Tuesday, 30 January 2018

Item Master Sql Query in Oracle Apps R12



Item Master Sql Query in Oracle Apps R12


SELECT msi.segment1, msi.segment2, msi.segment3, msi.segment4, msi.segment5,
msi.segment6, msi.segment7, msi.description, msi.primary_uom_code,
msi.cycle_count_enabled_flag, msi.inspection_required_flag,
msi.attribute14 legacy_code, msi.attribute13 legacy_description,
msi.stock_enabled_flag stockable,
msi.mtl_transactions_enabled_flag transactable, msi.lot_control_code,
msi.location_control_code, msi.purchasing_enabled_flag,
msi.must_use_approved_vendor_flag, msi.buyer_id,pap.full_name buyer_name,
msi.list_price_per_unit,
(msi.expense_account) expense_account,
msi.receiving_routing_id, misi.secondary_inventory subinventory,
mil.segment1 loc_seg1, mil.segment11 loc_seg11,
mil.segment2 loc_seg2, mil.segment3 loc_seg3, mil.segment4 loc_seg4,
mil.segment5 loc_seg5, mil.segment6 loc_seg6, mil.segment7 loc_seg7,
mil.segment8 loc_seg8, mil.segment9 loc_seg9,
mil.segment10 loc_seg10, ood.organization_name,
mcv.segment1 cat_seg1, mcv.segment2 cat_seg2, mcv.segment3 cat_seg3,
mcv.segment4 cat_seg4, mcv.segment5 cat_seg5, mcv.segment6 cat_seg6,
mcv.segment7 cat_seg7, mcv.segment8 cat_seg8, mcv.segment9 cat_seg9,
mcv.segment10 cat_seg10, mcv.segment11 cat_seg11,
mcv.segment12 cat_seg12, mcv.segment13 cat_seg13
FROM mtl_system_items msi,
mtl_secondary_locators msl,
mtl_item_locations mil,
per_all_people_f pap,
mtl_item_sub_inventories misi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE msi.inventory_item_id = msl.inventory_item_id(+)
AND msi.organization_id = msl.organization_id(+)
AND msl.secondary_locator = mil.inventory_location_id(+)
AND msl.organization_id = mil.organization_id(+)
AND pap.person_id(+) = msi.buyer_id
AND msi.inventory_item_id = misi.inventory_item_id(+)
AND msi.organization_id = misi.organization_id(+)
AND ood.organization_id = msi.organization_id
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mcs.category_set_id = mic.category_set_id
AND mcs.structure_id = mcv.structure_id
AND mcv.category_id = mic.category_id
AND msi.inventory_item_status_code = 'Active'

0 comments:

Post a Comment