SELECT
msib.segment1 "Item",
mp.organization_code,
msib.organization_id,
msib.purchasing_item_flag,
msib.shippable_item_flag,
msib.inventory_item_flag,
lot_control_code,
decode(serial_number_control_code, 1, 'Non Serialized', 2, 'Predefined',
5,
'At Receipt',
6,
'At Issue') "Serial Control",
mc.segment1 "PPL",
mc.segment2 "SPL",
mc.segment3 "ITEM CATEGORY",
(
SELECT
full_name
FROM
per_all_people_f
WHERE
person_id = fu.employee_id
AND ROWNUM = 1
) buyer
FROM
mtl_system_items_b msib,
mtl_item_categories mic,
mtl_categories mc,
mtl_parameters mp,
fnd_user fu
WHERE
msib.inventory_item_id = mic.inventory_item_id
AND msib.organization_id = mic.organization_id
AND msib.organization_id = mp.organization_id
AND mic.category_id = mc.category_id
AND mic.category_set_id = 1
AND mc.structure_id = 101
AND msib.enabled_flag = 'Y'
AND msib.lot_control_code = 1
AND nvl(msib.end_date_active, sysdate) >= sysdate
AND msib.buyer_id = fu.employee_id (+)
AND trunc(fu.end_date) < trunc(sysdate) --end dated buyer
AND mc.disable_date IS NULL
AND mp.organization_code IN ('MAS')--Add Organization Code
AND msib.item_type='P' --Component Item
ORDER BY
mc.segment1
No comments:
Post a Comment