SELECT /*+ hint index(element.print_order) */ distinct xpd.drop_id, xpd.drop_number, xpdcr.element_name, xpdcr.actual_value, nvl(MLN.PARENT_LOT_NUMBER, 0) PARENT_LOT, nvl(WDD.LOT_NUMBER, 0) CHILD_LOT1, nvl(WDD.SHIPPED_QUANTITY2,0) SEC_UOM_PC, nvl(WDD.NET_WEIGHT, 0) NET_WEIGHT, nvl(WND.GROSS_WEIGHT, 0) GROSS_WEIGHT, WDD.WEIGHT_UOM_CODE NET_GROSS_UOM, mdev.element_value Alloy_no, ooha.order_number, ooha.header_id, ooha.sold_to_org_id, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, ship_cas.party_site_id, xpd.plant_code, element.print_order FROM MTL_SYSTEM_ITEMS_B MSIB, WSH_NEW_DELIVERIES WND, WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA, OE_ORDER_LINES_ALL OOLA, OE_ORDER_HEADERS_ALL OOHA, hz_cust_accounts cust_acct, hz_cust_site_uses_all ship_su, hz_cust_acct_sites_all ship_cas, MTL_LOT_NUMBERS MLN, MTL_DESCR_ELEMENT_VALUES MDEV, -- HR_ORGANIZATION_UNITS hou, -- hr_locations_all hla, org_organization_definitions ood, xxgmd_pm_drops xpd, xxgmd_pm_drop_chem_results xpdcr, xxgmd_pm_cspecs cspec, xxgmd_pm_cspec_elements cspec_el, xxgmd_pm_cspec_element_rules cspec_rule, xxgmd_pm_elements element WHERE 1 = 1 AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND WDD.SOURCE_LINE_ID = OOLA.LINE_ID AND OOLA.HEADER_ID = OOHA.HEADER_ID AND MSIB.INVENTORY_ITEM_ID = OOLA.INVENTORY_ITEM_ID AND MSIB.ORGANIZATION_ID(+) = WDD.ORGANIZATION_ID and msib.inventory_item_id = mdev.inventory_item_id -- AND HOU.LOCATION_ID = HLA.LOCATION_ID(+) AND OOHA.SHIP_FROM_ORG_ID = OOD.ORGANIZATION_ID -- AND OOD.OPERATING_UNIT = HOU.ORGANIZATION_ID AND OOHA.sold_to_org_id = cust_acct.cust_account_id(+) AND MLN.LOT_NUMBER(+) = WDD.LOT_NUMBER AND mln.inventory_item_id(+) = wdd.inventory_item_id --Added on 26-Nov-12 from offshore and mdev.element_name = 'C-ALLOY' and xpd.drop_number = MLN.PARENT_LOT_NUMBER AND xpd.plant_code = ood.organization_code AND xpd.drop_id = xpdcr.drop_id AND wnd.DELIVERY_ID =:P_DELIVERY_ID -- AND ship_cas.cust_account_id = ooha.sold_to_org_id AND ship_cas.cust_acct_site_id = ship_su.cust_acct_site_id AND ooha.deliver_to_org_id = ship_su.site_use_id AND cspec.cspec_name = mdev.element_value AND cspec.cspec_id = cspec_el.cspec_id AND cspec_rule.cspec_element_id = cspec_el.cspec_element_id and cspec_rule.party_site_id = ship_cas.party_site_id and cspec_rule.print_element = 'Y' and cspec_el.element_id = element.element_id and upper(element.element_name) = upper(xpdcr.element_name) and upper(xpdcr.element_name) <> 'AL' AND SYSDATE BETWEEN cspec.effective_start_date AND NVL (cspec.effective_end_date,SYSDATE + 500 ) order by element.print_order ======================== PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 612 | 112 (3)| | 1 | TABLE ACCESS BY INDEX ROWID | MTL_UNITS_OF_MEASURE_TL | 1 | 14 | 1 (0)| |* 2 | INDEX UNIQUE SCAN | MTL_UNITS_OF_MEASURE_TL_U2 | 1 | | 0 (0)| | 3 | SORT ORDER BY | | 1 | 612 | 112 (3)| |* 4 | FILTER | | | | | |* 5 | HASH JOIN | | 1 | 612 | 104 (2)| | 6 | NESTED LOOPS | | | | | | 7 | NESTED LOOPS | | 1 | 594 | 86 (2)| | 8 | NESTED LOOPS | | 1 | 565 | 82 (2)| | 9 | NESTED LOOPS | | 1 | 554 | 79 (2)| | 10 | NESTED LOOPS | | 1 | 503 | 73 (2)| | 11 | NESTED LOOPS | | 1 | 495 | 73 (2)| | 12 | NESTED LOOPS | | 1 | 474 | 72 (2)| | 13 | NESTED LOOPS | | 1 | 449 | 71 (2)| | 14 | NESTED LOOPS | | 1 | 438 | 70 (2)| | 15 | NESTED LOOPS | | 1 | 420 | 68 (2)| | 16 | NESTED LOOPS | | 2 | 798 | 67 (2)| | 17 | NESTED LOOPS | | 1 | 274 | 63 (2)| |* 18 | HASH JOIN | | 1 | 240 | 59 (2)| | 19 | NESTED LOOPS OUTER | | 1 | 225 | 33 (4)| | 20 | NESTED LOOPS | | 1 | 146 | 32 (4)| | 21 | NESTED LOOPS | | 1 | 113 | 31 (4)| | 22 | NESTED LOOPS | | 1 | 101 | 30 (4)| | 23 | NESTED LOOPS | | 1 | 93 | 29 (4)| | 24 | NESTED LOOPS | | 1 | 64 | 27 (4)| |* 25 | HASH JOIN | | 2 | 120 | 27 (4)| | 26 | NESTED LOOPS | | | | | | 27 | NESTED LOOPS | | 8 | 256 | 16 (0)| | 28 | NESTED LOOPS | | 8 | 192 | 8 (0)| | 29 | TABLE ACCESS BY INDEX ROWID| WSH_NEW_DELIVERIES | 1 | 17 | 2 (0)| |* 30 | INDEX UNIQUE SCAN | WSH_NEW_DELIVERIES_U1 | 1 | | 1 (0)| |* 31 | INDEX SKIP SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 8 | 56 | 6 (0)| |* 32 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| |* 33 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 8 | 1 (0)| |* 34 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 18 | 504 | 10 (0)| |* 35 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_IX1 | 49 | | 1 (0)| |* 36 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | 4 | 0 (0)| | 37 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 29 | 2 (0)| |* 38 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)| |* 39 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 8 | 1 (0)| |* 40 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)| |* 41 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 12 | 1 (0)| |* 42 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| | 43 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 33 | 1 (0)| |* 44 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| | 45 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL | 1 | 79 | 1 (0)| |* 46 | INDEX UNIQUE SCAN | HR_LOCATIONS_PK | 1 | | 0 (0)| | 47 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 1747 | 26205 | 26 (0)| | 48 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 6 | 204 | 4 (0)| |* 49 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 7 | | 1 (0)| | 50 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 2 | 250 | 4 (0)| |* 51 | INDEX RANGE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 2 | | 2 (0)| | 52 | TABLE ACCESS BY INDEX ROWID | MTL_DESCR_ELEMENT_VALUES | 1 | 21 | 1 (0)| |* 53 | INDEX UNIQUE SCAN | MTL_DESCR_ELEMENT_VALUES_U1 | 1 | | 0 (0)| |* 54 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 1 | 18 | 2 (0)| | 55 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B | 1 | 11 | 1 (0)| |* 56 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_B_U1 | 1 | | 0 (0)| |* 57 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_TL | 1 | 25 | 1 (0)| |* 58 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | | 0 (0)| |* 59 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 1 | 21 | 1 (0)| |* 60 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_U1 | 1 | | 0 (0)| |* 61 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 8 | 0 (0)| |* 62 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS | 1 | 51 | 6 (0)| |* 63 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 40 | | 2 (0)| |* 64 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS | 1 | 11 | 3 (0)| |* 65 | INDEX RANGE SCAN | WSH_DELIVERY_ASSIGNMENTS_N3 | 1 | | 2 (0)| |* 66 | INDEX RANGE SCAN | MTL_LOT_NUMBERS_N1 | 2 | | 2 (0)| |* 67 | TABLE ACCESS BY INDEX ROWID | MTL_LOT_NUMBERS | 1 | 29 | 4 (0)| | 68 | TABLE ACCESS FULL | XXGMD_PM_DROPS | 3509 | 63162 | 17 (0)| | 69 | MERGE JOIN CARTESIAN | | 1 | 83 | 7 (0)| | 70 | NESTED LOOPS | | | | | | 71 | NESTED LOOPS | | 1 | 55 | 5 (0)| | 72 | NESTED LOOPS | | 1 | 47 | 4 (0)| | 73 | NESTED LOOPS | | 1 | 23 | 2 (0)| | 74 | NESTED LOOPS | | 1 | 15 | 1 (0)| |* 75 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 8 | 1 (0)| |* 76 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)| |* 77 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 7 | 0 (0)| |* 78 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 8 | 1 (0)| |* 79 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| | 80 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 24 | 2 (0)| |* 81 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)| |* 82 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)| |* 83 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 8 | 1 (0)| | 84 | BUFFER SORT | | 1 | 28 | 6 (0)| |* 85 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 28 | 2 (0)| |* 86 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)| ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("UOM_CODE"=:B1 AND "LANGUAGE"=USERENV('LANG')) 4 - filter( EXISTS (SELECT 0 FROM "GL"."GL_LEDGERS" "LGR","INV"."MTL_PARAMETERS" "MP","HR"."HR_ORGANIZATION_INFORMATION" "HOI2","HR"."HR_ORGANIZATION_INFORMATION" "HOI1","HR"."HR_ALL_ORGANIZATION_UNITS_TL" "HAOTL","HR"."HR_ALL_ORGANIZATION_UNITS" "HAO" WHERE "HAO"."ORGANIZATION_ID"=:B1 AND "HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS _GROUP_ID","HR_GENERAL"."GET_BUSINESS_GROUP_ID"()) AND "HAOTL"."LANGUAGE"=USERENV('LANG') AND "HAOTL"."ORGANIZATION_ID"=:B2 AND DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_OR GANIZATION_UNITS',"HAOTL"."ORGANIZATION_ID"))='TRUE' AND "HOI1"."ORGANIZATION_ID"=:B3 AND "HOI1"."ORG_INFORMATION1"='INV' AND "HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS' AND "HOI2"."ORGANIZATION_ID"=:B4 AND "HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information' AND "MP"."ORGANIZATION_ID"=:B5 AND "MP"."ORGANIZATION_CODE"=:B6 AND "LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'0123456789',' ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999')) AND "LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y')) 5 - access("XPD"."DROP_NUMBER"="MLN"."PARENT_LOT_NUMBER") 18 - access("OOHA"."SHIP_FROM_ORG_ID"="HAO"."ORGANIZATION_ID") 25 - access("HAO"."ORGANIZATION_ID"="HOI1"."ORGANIZATION_ID") 30 - access("WND"."DELIVERY_ID"=TO_NUMBER(:P_DELIVERY_ID)) 31 - access("HAOTL"."LANGUAGE"=USERENV('LANG')) filter("HAOTL"."LANGUAGE"=USERENV('LANG') AND DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW _RECORD"('HR_ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANIZATION_ID"))='TRUE') 32 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID") 33 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GEN ERAL"."GET_BUSINESS_GROUP_ID"())) 34 - filter("HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS') 35 - access("HOI1"."ORG_INFORMATION1"='INV') 36 - access("HAO"."ORGANIZATION_ID"="MP"."ORGANIZATION_ID") 38 - access("HAO"."ORGANIZATION_ID"="HOI2"."ORGANIZATION_ID") filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information') 39 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y') 40 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'0123456789',' ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999'))) 41 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GEN ERAL"."GET_BUSINESS_GROUP_ID"())) 42 - access("HAO"."ORGANIZATION_ID"=DECODE("HOI2"."ORG_INFORMATION_CONTEXT",'Accounting Information',TO_NUMBER("HOI2"."ORG_INFORMATION3"),TO_NUMBER(NULL))) 44 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID" AND "HAOTL"."LANGUAGE"=USERENV('LANG')) filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"H AOTL"."ORGANIZATION_ID"))='TRUE') 46 - access("HAO"."LOCATION_ID"="HLA"."LOCATION_ID"(+)) 49 - access("OOLA"."HEADER_ID"="OOHA"."HEADER_ID") 51 - access("MSIB"."INVENTORY_ITEM_ID"="OOLA"."INVENTORY_ITEM_ID") 53 - access("MSIB"."INVENTORY_ITEM_ID"="MDEV"."INVENTORY_ITEM_ID" AND "MDEV"."ELEMENT_NAME"='C-ALLOY') 54 - access("MSIB"."ORGANIZATION_ID"="MIC"."ORGANIZATION_ID" AND "MSIB"."INVENTORY_ITEM_ID"="MIC"."INVENTORY_ITEM_ID") 56 - access("MIC"."CATEGORY_SET_ID"="B"."CATEGORY_SET_ID") 57 - filter("T"."CATEGORY_SET_NAME"='Inventory') 58 - access("B"."CATEGORY_SET_ID"="T"."CATEGORY_SET_ID" AND "T"."LANGUAGE"=USERENV('LANG')) 59 - filter("B"."STRUCTURE_ID"="B"."STRUCTURE_ID") 60 - access("MIC"."CATEGORY_ID"="B"."CATEGORY_ID") 61 - access("B"."CATEGORY_ID"="T"."CATEGORY_ID" AND "T"."LANGUAGE"=USERENV('LANG')) 62 - filter("MSIB"."ORGANIZATION_ID"="WDD"."ORGANIZATION_ID") 63 - access("WDD"."SOURCE_LINE_ID"="OOLA"."LINE_ID") 64 - filter("WDA"."DELIVERY_ID"=TO_NUMBER(:P_DELIVERY_ID)) 65 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID") 66 - access("MLN"."LOT_NUMBER"="WDD"."LOT_NUMBER") 67 - filter("MLN"."INVENTORY_ITEM_ID"="WDD"."INVENTORY_ITEM_ID") 75 - filter("MP"."ORGANIZATION_CODE"=:B1) 76 - access("MP"."ORGANIZATION_ID"=:B1) 77 - access("HAOTL"."ORGANIZATION_ID"=:B1 AND "HAOTL"."LANGUAGE"=USERENV('LANG')) filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"H AOTL"."ORGANIZATION_ID"))='TRUE') 78 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GEN ERAL"."GET_BUSINESS_GROUP_ID"())) 79 - access("HAO"."ORGANIZATION_ID"=:B1) 81 - access("HOI2"."ORGANIZATION_ID"=:B1) filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information') 82 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'0123456789',' ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999'))) 83 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y') 85 - filter("HOI1"."ORG_INFORMATION1"='INV' AND "HOI1"."ORG_INFORMATION2"='Y') 86 - access("HOI1"."ORGANIZATION_ID"=:B1) filter("HOI1"."ORG_INFORMATION_CONTEXT"||''='CLASS') Note ----- - 'PLAN_TABLE' is old version OOHA.SHIP_FROM_ORG_ID