Introduction
This Query will help to Fetch the Item Master Extract Report Data in Oracle Apps.
SQL Query:
SELECT DISTINCT (SELECT organization_code
FROM mtl_parameters
WHERE organization_id = msi.organization_id
AND ROWNUM = 1) org,
msi.inventory_item_id, msi.segment1 item,
(SELECT mic_brand.segment8
FROM mtl_item_categories_v mic_brand
WHERE msi.organization_id = mic_brand.organization_id
AND msi.inventory_item_id = mic_brand.inventory_item_id
AND mic_brand.category_set_name = ‘APP Brand’
AND ROWNUM = 1) brand,
msi.description, msi.item_type,
msi.creation_date born_on_date,
DECODE (msi.planning_make_buy_code,
‘1’, ‘Make’,
‘Buy’
) make_buy,
msi.attribute1 “ITEM_DIMENSIONS”,
msi.attribute2 “CASE_PACK_QUANTITY”,
msi.attribute3 “CASE_PACK_DIMENSIONS”,
msi.attribute15 “CASE_PACK_LENGTH”,
msi.attribute16 “CASE_PACK_WIDTH”,
msi.attribute17 “CASE_PACK_HEIGHT”,
msi.attribute4 “CASE_PACK_CUBE”,
msi.attribute5 “CASE_PACK_WEIGHT”,
msi.attribute6 “CASE_PACK_SCS_CODE”,
msi.attribute20 “OTM_PALLET_DIMENSIONS”,
msi.attribute7 “PALLET_DIMENSIONS”,
msi.attribute8 “PALLET_CUBE”, msi.attribute9 “PALLET_WEIGHT”,
msi.attribute10 “PALLET_QUANTITY”,
msi.attribute18 “QTY_PER_LAYER”,
msi.attribute19 “NUMBER_OF_LAYERS”,
msi.attribute11 “STACKABLE”, msi.attribute12 “MOLD_TOOL”,
msi.attribute13 “ROYALTY_PERCENT”,
msi.attribute14 “COUNTRY_OF_ORGIN”,
msi.attribute21 “ASSEMBLED_LENGTH”,
msi.attribute22 “ASSEMBLED_WIDTH”,
msi.attribute23 “ASSEMBLED_HEIGHT”,
(SELECT element_value
FROM mtl_descr_element_values_v
WHERE inventory_item_id = msi.inventory_item_id
AND item_catalog_group_id = 2
AND element_name = ‘Color Family’) color,
(SELECT item_cost
FROM apps.cst_item_cost_type_v
WHERE inventory_item_id = msi.inventory_item_id
AND organization_id = msi.organization_id
AND cost_type_id = 1
AND ROWNUM = 1) unit_cost,
xxdmc_logility_generic.item_volume
(msi.inventory_item_id,
msi.organization_id
)
* .000016387 unit_cube,
msi.unit_weight unit_weight,
(SELECT segment1
|| ‘.’
|| segment2
|| ‘.’
|| segment3
FROM mtl_item_categories_v mic_pg
WHERE msi.inventory_item_id = mic_pg.inventory_item_id
AND msi.organization_id = mic_pg.organization_id
AND mic_pg.category_set_name = ‘Inventory’
AND ROWNUM = 1) inventory_category,
(SELECT segment1
|| ‘.’
|| segment2
|| ‘.’
|| segment3
FROM mtl_item_categories_v mic_pg
WHERE msi.inventory_item_id = mic_pg.inventory_item_id
AND msi.organization_id = mic_pg.organization_id
AND mic_pg.category_set_name = ‘Purchasing’
AND ROWNUM = 1) purchasing_category,
(SELECT REPLACE (agent_name, ‘,’)
FROM po_agents_v
WHERE agent_id =
(SELECT buyer_id
FROM mtl_system_items_b m
WHERE m.inventory_item_id =
msi.inventory_item_id
AND m.organization_id = msi.organization_id)
AND ROWNUM = 1) buyer_planner, –User Data 10
msi.planner_code forecast_planner, –User Data 11
xxdmc_logility_generic.item_status
(msi.inventory_item_id,
msi.organization_id
) status, –User Data 12
(SELECT resource_code
FROM bom_operation_resources_v bor,
bom_operation_sequences_v bos,
bom_operational_routings_v bou
WHERE bor.operation_sequence_id = bos.operation_sequence_id
AND bou.routing_sequence_id = bos.routing_sequence_id
AND bou.assembly_item_id = msi.inventory_item_id
AND bou.organization_id = msi.organization_id
AND resource_seq_num = ’30’
AND ROWNUM = 1) machine_group, –User Data 13
(SELECT resource_code
FROM bom_operation_resources_v bor,
bom_operation_sequences_v bos,
bom_operational_routings_v bou
WHERE bor.operation_sequence_id = bos.operation_sequence_id
AND bou.routing_sequence_id = bos.routing_sequence_id
AND bou.assembly_item_id = msi.inventory_item_id
AND bou.organization_id = msi.organization_id
AND resource_seq_num = ’35’
AND ROWNUM = 1) machine, –User Data 14 machine
(SELECT resource_code
FROM bom_operation_resources_v bor,
bom_operation_sequences_v bos,
bom_operational_routings_v bou
WHERE bor.operation_sequence_id = bos.operation_sequence_id
AND bou.routing_sequence_id = bos.routing_sequence_id
AND bou.assembly_item_id = msi.inventory_item_id
AND bou.organization_id = msi.organization_id
AND resource_seq_num = ’40’
AND ROWNUM = 1) mold, –User Data 15 tool
(SELECT msr.sourcing_rule_name
FROM mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_assignments s
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
— and ood.organization_id = msso.source_organization_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
AND s.organization_id = msi.organization_id
AND s.assignment_set_id = 21
AND ROWNUM = 1) sourcing,
(SELECT segment2
FROM mtl_item_categories_v mic_pg
WHERE msi.inventory_item_id =
mic_pg.inventory_item_id
AND msi.organization_id = mic_pg.organization_id
AND mic_pg.category_set_name = ‘Inventory’
AND ROWNUM = 1) super_group,
(SELECT vendor_name
FROM mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
— ,org_organization_definitions ood
,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_assignments s
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
— and ood.organization_id = msso.source_organization_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
AND s.organization_id = msi.organization_id
AND s.assignment_set_id = 21
AND ROWNUM = 1) vendor,
(SELECT vendor_site_code
FROM mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
— ,org_organization_definitions ood
,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_assignments s
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
— and ood.organization_id = msso.source_organization_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
AND s.organization_id = msi.organization_id
AND s.assignment_set_id = 21
AND ROWNUM = 1) vendor_site_code,
full_lead_time, fixed_days_supply, fixed_lot_multiplier,
minimum_order_quantity,
(SELECT country
FROM mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
— ,org_organization_definitions ood
,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_assignments s
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
— and ood.organization_id = msso.source_organization_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
AND s.organization_id = msi.organization_id
AND s.assignment_set_id = 21
AND ROWNUM = 1) vendor_country, –User Data 18
(SUBSTR (msi.attribute1, 1, INSTR (msi.attribute1, ‘X’) – 1)
) LENGTH, –User Data 22
(SUBSTR (msi.attribute1,
INSTR (msi.attribute1, ‘X’) + 1,
INSTR (msi.attribute1, ‘X’, 2) – 1
)
) width, –User Data 23
(SUBSTR (msi.attribute1,
INSTR (msi.attribute1, ‘X’, 1, 2) + 1,
6
)
) height, –User Data 24
(SELECT pv.segment1
FROM mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr
— ,org_organization_definitions ood
,
po_vendors pv,
po_vendor_sites_all pvs,
mrp_sr_assignments s
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
— and ood.organization_id = msso.source_organization_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
AND s.organization_id = msi.organization_id
AND s.assignment_set_id = 21
AND ROWNUM = 1) vendor_number,
msi.primary_unit_of_measure primary_uom, –Unit of measure
(SELECT DISTINCT cross_reference
FROM mtl_cross_references
WHERE 1 = 1
AND inventory_item_id = msi.inventory_item_id
AND cross_reference_type = ‘UPC CODE’
AND ROWNUM = 1) upc, –Universal product code
(SELECT DISTINCT cross_reference
FROM mtl_cross_references
WHERE 1 = 1
AND inventory_item_id = msi.inventory_item_id
AND cross_reference_type = ‘WMS_GTIN_HRCHY’
AND NVL (uom_code, ‘XX’) = ‘EA’
AND ROWNUM = 1) gtin,
(SELECT DISTINCT uom_code || ‘:’
|| cross_reference
FROM mtl_cross_references
WHERE 1 = 1
AND inventory_item_id = msi.inventory_item_id
AND cross_reference_type = ‘HS TARIFF NUMBER’
AND ROWNUM = 1) “HS_TARIFF”,
(SELECT segment1 || ‘.’ || segment2 || ‘.’
|| segment3
FROM mtl_item_categories_v mic_pg
WHERE msi.inventory_item_id =
mic_pg.inventory_item_id
AND msi.organization_id = mic_pg.organization_id
AND mic_pg.category_set_name = ‘APP Royalty’
AND ROWNUM = 1) royalty,
— Start point Added By Venkatesh on 12-Jan-2021
msi.attribute15 “INNER_PACK_LENGTH”,
msi.attribute16 “INNER_PACK_WIDTH”,
msi.attribute17 “INNER_PACK_HEIGHT”,
msi.attribute5 “INNER_PACK_WEIGHT”,
(SELECT pvs.fob_lookup_code
FROM apps.mrp_sr_source_org msso,
apps.mrp_sr_receipt_org msro,
apps.mrp_sourcing_rules msr,
org_organization_definitions ood,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.mrp_sr_assignments s,
mtl_parameters mp,
hz_locations hl
WHERE 1 = 1
AND msro.sr_receipt_id = msso.sr_receipt_id
AND msr.sourcing_rule_id = msro.sourcing_rule_id
AND pv.vendor_id = msso.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND s.sourcing_rule_id = msro.sourcing_rule_id
AND s.inventory_item_id = msi.inventory_item_id
–3060285
AND s.organization_id = msi.organization_id –124
AND mp.organization_id = msi.organization_id –124
AND s.assignment_set_id = 21
AND hl.location_id = pvs.location_id
AND ROWNUM < 2) “FOB”,
(SELECT DISTINCT cross_reference
FROM mtl_cross_references_v a
WHERE 1 = 1
AND a.inventory_item_id = msi.inventory_item_id
AND a.cross_reference_type = ‘WMS_GTIN_HRCHY’
AND NVL (a.uom_code, ‘XX’) = ‘IP’
AND ROWNUM = 1) “GTIN_IP”,
(SELECT DISTINCT cross_reference
FROM mtl_cross_references_v a
WHERE a.inventory_item_id =
msi.inventory_item_id
AND a.cross_reference_type = ‘WMS_GTIN_HRCHY’
AND a.uom_code = ‘CS’
AND ROWNUM = 1) “GTIN_CS”
FROM apps.mtl_system_items_b msi, mtl_parameters mp
WHERE 1 = 1
AND msi.organization_id = mp.organization_id
AND msi.organization_id IN (124, 125, 182, 183, 202);
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your question
