SELECT msi.organization_id || msi.inventory_item_id AS itm_id,
msi.inventory_item_id,
--mpv.organization_code,
msi.segment1 AS Item_Number,
SUBSTR (msi.description, 1, 75) AS Item_Description,
msi.PRIMARY_UOM_CODE AS ItemUOM,
mc.segment1 product_group,
mc.segment2 minor_product_group,
mc.segment3 bull_semen_ownership,
mc.segment4 country_of_residence,
mc.segment5 lifecycle_status,
mc.segment6 marketing_status,
mc.segment7 semen_sorting_status,
mc.segment8 bull_identifier,
mc.segment11 excluded_item,
DECODE (mc.segment1, 'SEMEN-DAIRY', 'S', 'SEMEN-BEEF', 'S', 'P')
AS Item_Type,
CASE
WHEN NVL (G02.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN G02.inventory_item_status_code IN ('GB Inactiv', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS G02,
CASE
WHEN NVL (U01.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN U01.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS U01,
CASE
WHEN NVL (U06.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN U06.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS U06,
CASE
WHEN NVL (C01.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN C01.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS C01,
CASE
WHEN NVL (C06.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN C06.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS C06,
CASE
WHEN NVL (G03.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN G03.inventory_item_status_code IN ('GB Inactiv', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS G03,
CASE
WHEN NVL (G06.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN G06.inventory_item_status_code IN ('GB Inactiv', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS G06,
CASE
WHEN NVL (A01.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN A01.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS A01,
CASE
WHEN NVL (A06.inventory_item_id, -99999) = -99999
THEN
'N'
WHEN A06.inventory_item_status_code IN ('ABS No Ord', 'Inactive')
THEN
'N'
ELSE
'Y'
END
AS A06,
DECODE (mc.segment1,
'SEMEN-DAIRY', SUBSTR (mcr.cross_reference, 1, 10),
'SEMEN-BEEF', SUBSTR (mcr.cross_reference, 1, 10),
SUBSTR (mcr2.cross_reference, 1, 10))
bull_fh_code,
msi.inventory_item_status_code,
SYSDATE AS sql_updated_date
FROM mtl_system_items msi,
mtl_item_categories mic,
mtl_category_sets_tl mcst,
mtl_categories_b_kfv mc,
(SELECT cross_reference,
cross_reference_type,
inventory_item_id,
last_update_date
FROM mtl_cross_references
WHERE organization_id = '121') mcr,
(SELECT cross_reference,
cross_reference_type,
inventory_item_id,
last_update_date
FROM mtl_cross_references
WHERE organization_id = '121') mcr2,
(SELECT organization_id, organization_code, last_update_date
FROM MTL_PARAMETERS
WHERE organization_id = 141) mpv, --item master --useed to get distinct list of oracle items
(SELECT msi.inventory_item_id,
inventory_item_status_code,
msi.last_update_date
FROM mtl_system_items msi
WHERE organization_id = 143) G02,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 263) U01,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 306) U06,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 307) C01,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 323) C06,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 144) G03,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 146) G06,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 324) A01,
(SELECT inventory_item_id,
inventory_item_status_code,
last_update_date
FROM mtl_system_items
WHERE organization_id = 327) A06
WHERE msi.inventory_item_id = mic.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = mic.ORGANIZATION_ID
AND mic.category_set_id = mcst.category_set_id
AND mcst.LANGUAGE = USERENV ('LANG')
AND mic.category_id = mc.category_id
AND mic.CATEGORY_SET_ID = 1
AND msi.organization_id = mpv.organization_id
AND msi.inventory_item_id = G02.inventory_item_id(+)
AND msi.inventory_item_id = U01.inventory_item_id(+)
AND msi.inventory_item_id = U06.inventory_item_id(+)
AND msi.inventory_item_id = C01.inventory_item_id(+)
AND msi.inventory_item_id = C06.inventory_item_id(+)
AND msi.inventory_item_id = G03.inventory_item_id(+)
AND msi.inventory_item_id = G06.inventory_item_id(+)
AND msi.inventory_item_id = A01.inventory_item_id(+)
AND msi.inventory_item_id = A06.inventory_item_id(+)
AND msi.inventory_item_id = mcr.inventory_item_id(+)
AND mcr.cross_reference_type(+) = 'GB-B'
AND msi.inventory_item_id = mcr2.inventory_item_id(+)
AND mcr2.cross_reference_type(+) = 'GB-G'
AND GREATEST (
NVL (
mpv.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
G02.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
U01.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
U06.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
C01.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
C06.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
G03.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
G06.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
A01.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
A06.last_update_Date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
msi.last_update_date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
mc.last_update_date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
mcr.last_update_date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss')),
NVL (
mcr2.last_update_date,
TO_DATE ('04/01/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss'))) >=
(SELECT last_extract_date - 5000
FROM xxgns_obi_last_runs
WHERE object = 'ITEM');
I'm wondering how to avoid the multiple times join of table mtl_system_items ? Perhaps with decode or case but need to ensure the rows returned will be the same as they are now.