[Home] [Help]
105: xMsgData OUT NOCOPY varchar2)
106: IS
107:
108: CURSOR c_lines(l_seq number) IS
109: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
110: distinct ato_line_id, program_id -- , perform_match
111: from bom_cto_order_lines_upg
112: where sequence = l_seq
113: and status = 'UPG';
107:
108: CURSOR c_lines(l_seq number) IS
109: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
110: distinct ato_line_id, program_id -- , perform_match
111: from bom_cto_order_lines_upg
112: where sequence = l_seq
113: and status = 'UPG';
114:
115: CURSOR c_configs(l_ato_line_id number) IS
113: and status = 'UPG';
114:
115: CURSOR c_configs(l_ato_line_id number) IS
116: select substrb(concatenated_segments,1,50) config_name
117: from bom_cto_order_lines_upg bcolu,
118: mtl_system_items_kfv msi
119: where bcolu.ato_line_id = l_ato_line_id
120: and bcolu.config_item_id is not null
121: and bcolu.config_item_id = msi.inventory_item_id
121: and bcolu.config_item_id = msi.inventory_item_id
122: and bcolu.ship_from_org_id = msi.organization_id;
123:
124: CURSOR c_src_lines(l_seq number) IS
125: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
126: distinct ato_line_id, program_id
127: from bom_cto_order_lines_upg
128: where sequence = l_seq
129: and status = 'ITEM';
123:
124: CURSOR c_src_lines(l_seq number) IS
125: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
126: distinct ato_line_id, program_id
127: from bom_cto_order_lines_upg
128: where sequence = l_seq
129: and status = 'ITEM';
130:
131: CURSOR c_copy_src_rules(p_ato_line_id number) IS
134: bcolu.config_creation,
135: bcso.create_src_rules,
136: bcso.model_item_id,
137: bcso.config_item_id
138: from bom_cto_order_lines_upg bcolu,
139: bom_cto_src_orgs bcso
140: where bcolu.ato_line_id = p_ato_line_id
141: and bcolu.bom_item_type = '1'
142: and nvl(bcolu.wip_supply_type, 1) <> 6
175:
176: BEGIN
177: select 1
178: into l_exists
179: from bom_cto_order_lines_upg
180: where sequence = l_seq
181: and rownum = 1;
182:
183: EXCEPTION
238: END LOOP;
239: lstmtnum := 40;
240: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
241:
242: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
243: bom_cto_order_lines_upg
244: set status = 'ERROR'
245: where ato_line_id = v_lines.ato_line_id;
246:
239: lstmtnum := 40;
240: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
241:
242: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
243: bom_cto_order_lines_upg
244: set status = 'ERROR'
245: where ato_line_id = v_lines.ato_line_id;
246:
247: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
266: -- Delete from bcso_b
267: --
268: delete from bom_cto_src_orgs_b
269: where line_id in
270: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
271: line_id
272: from bom_cto_order_lines_upg
273: where ato_line_id = v_lines.ato_line_id
274: and config_item_id is not null
268: delete from bom_cto_src_orgs_b
269: where line_id in
270: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
271: line_id
272: from bom_cto_order_lines_upg
273: where ato_line_id = v_lines.ato_line_id
274: and config_item_id is not null
275: and status = 'UPG');
276: WriteToLog('Lines deleted from bcso_b::'||sql%rowcount, 2);
279: -- delete from bcmo
280: --
281: delete from bom_cto_model_orgs
282: where config_item_id in
283: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
284: distinct config_item_id
285: from bom_cto_order_lines_upg
286: where ato_line_id = v_lines.ato_line_id
287: and status = 'UPG');
281: delete from bom_cto_model_orgs
282: where config_item_id in
283: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
284: distinct config_item_id
285: from bom_cto_order_lines_upg
286: where ato_line_id = v_lines.ato_line_id
287: and status = 'UPG');
288:
289: WriteToLog('Lines deleted from bcmo::'||sql%rowcount, 2);
321: END LOOP;
322: lstmtnum := 40;
323: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
324:
325: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
326: bom_cto_order_lines_upg
327: set status = 'ERROR'
328: where ato_line_id = v_lines.ato_line_id;
329:
322: lstmtnum := 40;
323: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
324:
325: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
326: bom_cto_order_lines_upg
327: set status = 'ERROR'
328: where ato_line_id = v_lines.ato_line_id;
329:
330: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
340:
341: /*
342: begin
343: select perform_match into v_lines_perform_match
344: from bom_cto_order_lines_upg
345: where line_id = v_lines.ato_line_id ;
346:
347: exception
348: when others then
388: <
389: EXIT WHEN c_lines%NOTFOUND;
390: END LOOP; /* c_lines */
391:
392: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
393: bom_cto_order_lines_upg
394: set status = 'BCSO'
395: where sequence = l_seq
396: and status = 'UPG';
389: EXIT WHEN c_lines%NOTFOUND;
390: END LOOP; /* c_lines */
391:
392: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
393: bom_cto_order_lines_upg
394: set status = 'BCSO'
395: where sequence = l_seq
396: and status = 'UPG';
397:
416: and msim.organization_id = msic.organization_id)
417: where msic.inventory_item_id in
418: (
419: select distinct config_item_id
420: from bom_cto_order_lines_upg
421: where status = 'BCSO'
422: and config_item_id is not null
423: )
424: and exists (select 'x'
482:
483: --
484: -- Update status to 'ITEM'
485: --
486: update /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
487: bom_cto_order_lines_upg
488: set status = 'ITEM'
489: where status = 'BCSO';
490:
483: --
484: -- Update status to 'ITEM'
485: --
486: update /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
487: bom_cto_order_lines_upg
488: set status = 'ITEM'
489: where status = 'BCSO';
490:
491: WriteToLog('Items created. Status updated to ITEM for rows::'||sql%rowcount, 1);
501:
502: BEGIN
503: select 1
504: into l_exists
505: from bom_cto_order_lines_upg
506: where sequence = l_seq
507: and rownum = 1;
508:
509: EXCEPTION
552: END LOOP;
553: lstmtnum := 40;
554: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
555:
556: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
557: bom_cto_order_lines_upg
558: set status = 'ERROR'
559: where ato_line_id = v_lines.ato_line_id;
560:
553: lstmtnum := 40;
554: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
555:
556: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
557: bom_cto_order_lines_upg
558: set status = 'ERROR'
559: where ato_line_id = v_lines.ato_line_id;
560:
561: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
609: WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
610: END LOOP;
611: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
612:
613: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
614: bom_cto_order_lines_upg
615: set status = 'ERROR'
616: where ato_line_id = v_lines.ato_line_id;
617:
610: END LOOP;
611: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
612:
613: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
614: bom_cto_order_lines_upg
615: set status = 'ERROR'
616: where ato_line_id = v_lines.ato_line_id;
617:
618: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
662: WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
663: END LOOP;
664: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
665:
666: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
667: bom_cto_order_lines_upg
668: set status = 'ERROR'
669: where ato_line_id = v_lines.ato_line_id;
670:
663: END LOOP;
664: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
665:
666: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
667: bom_cto_order_lines_upg
668: set status = 'ERROR'
669: where ato_line_id = v_lines.ato_line_id;
670:
671: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
679: <
680: EXIT WHEN c_src_lines%NOTFOUND;
681: END LOOP; /* c_src_lines */
682:
683: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
684: bom_cto_order_lines_upg
685: set status = 'CTO_SRC'
686: where sequence = l_seq
687: and status = 'ITEM';
680: EXIT WHEN c_src_lines%NOTFOUND;
681: END LOOP; /* c_src_lines */
682:
683: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
684: bom_cto_order_lines_upg
685: set status = 'CTO_SRC'
686: where sequence = l_seq
687: and status = 'ITEM';
688:
739: l_msg_data OUT NOCOPY varchar2)
740: IS
741:
742: CURSOR c_configs IS
743: select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
744: distinct config_item_id, nvl(perform_match, 'N') match
745: from bom_cto_order_lines_upg
746: where ato_line_id = p_ato_line_id
747: and nvl(config_creation, 1) = 3
741:
742: CURSOR c_configs IS
743: select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
744: distinct config_item_id, nvl(perform_match, 'N') match
745: from bom_cto_order_lines_upg
746: where ato_line_id = p_ato_line_id
747: and nvl(config_creation, 1) = 3
748: and config_item_id is not null
749: and status = 'UPG';
868: --
869: -- Updating rows for the same config item to status BCSO to
870: -- avoid processing them again with new group reference id
871: --
872: update /* INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
873: bom_cto_order_lines_upg
874: set status = 'BCSO'
875: where line_id in (
876: select distinct line_id
869: -- Updating rows for the same config item to status BCSO to
870: -- avoid processing them again with new group reference id
871: --
872: update /* INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
873: bom_cto_order_lines_upg
874: set status = 'BCSO'
875: where line_id in (
876: select distinct line_id
877: from bom_cto_src_orgs_b
919: bcolu.config_item_id config_item_id
920: from
921: cst_item_costs c,
922: bom_cto_src_orgs bcso,
923: bom_cto_order_lines_upg bcolu,
924: mtl_parameters mp1
925: where bcolu.config_item_id is not null
926: and bcolu.status = 'BCSO'
927: and c.organization_id = bcso.organization_id
937: where inventory_item_id = bcolu.config_item_id
938: and organization_id = bcso.organization_id);
939:
940: CURSOR c_bcolu_cfg IS
941: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
942: distinct
943: config_item_id,
944: inventory_item_id,
945: line_id
942: distinct
943: config_item_id,
944: inventory_item_id,
945: line_id
946: from bom_cto_order_lines_upg bcolu
947: where bcolu.config_item_id is not null
948: and bcolu.status = 'BCSO';
949:
950: CURSOR c_get_org_id IS
947: where bcolu.config_item_id is not null
948: and bcolu.status = 'BCSO';
949:
950: CURSOR c_get_org_id IS
951: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
952: bcolu.config_item_id cfg_item_id,
953: bcolu.inventory_item_id model_item_id,
954: msi.organization_id src_org_id
955: from mtl_system_items msi,
952: bcolu.config_item_id cfg_item_id,
953: bcolu.inventory_item_id model_item_id,
954: msi.organization_id src_org_id
955: from mtl_system_items msi,
956: bom_cto_order_lines_upg bcolu
957: where bcolu.config_item_id is not null
958: and bcolu.status = 'BCSO'
959: and msi.inventory_item_id = bcolu.config_item_id
960: and not exists
991: --kkonada R12
992: --for mtl_cross_references_b
993: --bug# 4539578
994:
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
997: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
998: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
999: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1021: creation_date,
1022: created_by,
1023: last_update_login
1024: )
1025: select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1026: distinct
1027: bcolu.config_item_id,
1028: m.organization_id,
1029: l.language_code,
1036: gLoginId --last_update_login
1037: from
1038: mtl_system_items_tl m,
1039: bom_cto_src_orgs bcso,
1040: bom_cto_order_lines_upg bcolu,
1041: fnd_languages l
1042: where bcolu.config_item_id is not null
1043: and bcolu.status = 'BCSO'
1044: and m.inventory_item_id = bcolu.inventory_item_id
1071: program_application_id,
1072: program_id,
1073: program_update_date,
1074: request_id)
1075: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1076: distinct
1077: bcolu.config_item_id,
1078: m.organization_id,
1079: m.inventory_item_status_code,
1089: sysdate,
1090: null -- req_id
1091: from mtl_system_items m,
1092: bom_cto_src_orgs bcso,
1093: bom_cto_order_lines_upg bcolu
1094: where bcolu.config_item_id is not null
1095: and bcolu.status = 'BCSO'
1096: and m.inventory_item_id = bcolu.inventory_item_id
1097: and bcso.model_item_id = bcolu.inventory_item_id
1256: from
1257: mtl_parameters MP1,
1258: cst_item_costs C,
1259: cst_item_cost_details CICD,
1260: bom_cto_order_lines_upg bcolu
1261: where
1262: C.organization_id = MP1.organization_id
1263: and C.inventory_item_id = bcolu.inventory_item_id -- pModelId
1264: and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
1274: MP2.cost_organization_id
1275: from mtl_parameters mp2,
1276: mtl_parameters mp3,
1277: bom_cto_src_orgs bcso,
1278: bom_cto_order_lines_upg bcolu
1279: where bcolu.config_item_id is not null
1280: and bcolu.status = 'BCSO'
1281: and bcso.model_item_id = bcolu.inventory_item_id
1282: and bcso.model_item_id = bcolu.inventory_item_id
1390: attribute13,
1391: attribute14,
1392: attribute15
1393: )
1394: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1395: distinct
1396: bcolu.config_item_id, -- INVENTORY_ITEM_ID
1397: mp1.cost_organization_id,
1398: c.cost_type_id,
1447: mtl_parameters MP1,
1448: cst_item_costs C,
1449: mtl_system_items S,
1450: bom_cto_src_orgs bcso,
1451: bom_cto_order_lines_upg bcolu
1452: where bcolu.config_item_id is not null
1453: and bcolu.status = 'BCSO'
1454: and S.organization_id = C.organization_id
1455: and S.inventory_item_id = C.inventory_item_id
1613: attribute13,
1614: attribute14,
1615: attribute15
1616: )
1617: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1618: distinct
1619: bcolu.config_item_id, -- INVENTORY_ITEM_ID
1620: mp1.cost_organization_id,
1621: l_cto_cost_type_id, -- CTO cost_type_id,
1670: mtl_parameters MP1,
1671: cst_item_costs C,
1672: mtl_system_items S,
1673: bom_cto_src_orgs bcso,
1674: bom_cto_order_lines_upg bcolu
1675: where bcolu.config_item_id is not null
1676: and bcolu.status = 'BCSO'
1677: and S.organization_id = C.organization_id
1678: and S.inventory_item_id = C.inventory_item_id
1783: attribute13,
1784: attribute14,
1785: attribute15
1786: )
1787: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1788: distinct
1789: bcolu.config_item_id, -- inventory_item_id
1790: c.cost_type_id,
1791: sysdate, -- last_update_date
1833: mtl_parameters MP1,
1834: cst_item_cost_details C,
1835: mtl_system_items S,
1836: bom_cto_src_orgs bcso,
1837: bom_cto_order_lines_upg bcolu
1838: where bcolu.config_item_id is not null
1839: and bcolu.status = 'BCSO'
1840: and S.organization_id = C.organization_id
1841: and S.inventory_item_id = C.inventory_item_id
1905: attribute13,
1906: attribute14,
1907: attribute15
1908: )
1909: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1910: distinct
1911: bcolu.config_item_id, -- inventory_item_id
1912: l_cto_cost_type_id, -- CTO cost_type_id,
1913: sysdate, -- last_update_date
1955: mtl_parameters MP1,
1956: cst_item_cost_details C,
1957: mtl_system_items S,
1958: bom_cto_src_orgs bcso,
1959: bom_cto_order_lines_upg bcolu
1960: where bcolu.config_item_id is not null
1961: and bcolu.status = 'BCSO'
1962: and S.organization_id = C.organization_id
1963: and S.inventory_item_id = C.inventory_item_id
2039: program_id,
2040: program_update_date,
2041: request_id
2042: )
2043: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2044: distinct
2045: bcolu.config_item_id, -- Inventory_item_id
2046: E.element_name, -- element_name
2047: sysdate, -- last_update_date
2058: NULL -- request_id
2059: from mtl_system_items s,
2060: mtl_descr_element_values D,
2061: mtl_descriptive_elements E,
2062: bom_cto_order_lines_upg bcolu
2063: where bcolu.config_item_id is not null
2064: and bcolu.status = 'BCSO'
2065: and D.inventory_item_id = S.inventory_item_id
2066: and s.inventory_item_id = bcolu.inventory_item_id
2105: program_id,
2106: program_update_date,
2107: organization_id
2108: )
2109: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2110: distinct
2111: bcolu.config_item_id,
2112: ic.category_set_id,
2113: ic.category_id,
2123: ic.organization_id
2124: from
2125: mtl_item_categories ic,
2126: bom_cto_src_orgs bcso,
2127: bom_cto_order_lines_upg bcolu
2128: where bcolu.config_item_id is not null
2129: and bcolu.status = 'BCSO'
2130: and ic.inventory_item_id = bcolu.inventory_item_id
2131: and ic.organization_id = bcso.organization_id
2156: program_id,
2157: program_update_date,
2158: organization_id
2159: )
2160: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2161: distinct
2162: bcolu.config_item_id,
2163: mcsb.category_set_id,
2164: mcsb.default_category_id,
2176: mtl_item_categories ic,
2177: mtl_category_sets_b mcsb,
2178: mtl_default_category_sets mdcs,
2179: bom_cto_src_orgs bcso,
2180: bom_cto_order_lines_upg bcolu
2181: where bcolu.config_item_id is not null
2182: and bcolu.status = 'BCSO'
2183: and bcolu.inventory_item_id = ic.inventory_item_id
2184: and ic.organization_id = bcso.organization_id
2216: program_id,
2217: program_update_date,
2218: organization_id
2219: )
2220: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2221: distinct
2222: bcolu.config_item_id,
2223: ic.category_set_id,
2224: ic.category_id,
2234: ic.organization_id
2235: from
2236: mtl_item_categories ic,
2237: bom_cto_src_orgs bcso,
2238: bom_cto_order_lines_upg bcolu
2239: where bcolu.config_item_id is not null
2240: and bcolu.status = 'BCSO'
2241: and ic.inventory_item_id = bcolu.inventory_item_id
2242: and ic.organization_id = bcso.organization_id
2268: program_id,
2269: program_update_date,
2270: organization_id
2271: )
2272: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2273: distinct
2274: bcolu.config_item_id,
2275: mcsb.category_set_id,
2276: mcsb.default_category_id,
2288: mtl_item_categories ic,
2289: mtl_category_sets_b mcsb,
2290: mtl_default_category_sets mdcs,
2291: bom_cto_src_orgs bcso,
2292: bom_cto_order_lines_upg bcolu
2293: where bcolu.config_item_id is not null
2294: and bcolu.status = 'BCSO'
2295: and bcolu.inventory_item_id = ic.inventory_item_id
2296: and ic.organization_id = bcso.organization_id
2334: program_id,
2335: program_update_date,
2336: organization_id
2337: )
2338: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2339: distinct
2340: bcolu.config_item_id,
2341: ri.related_item_id,
2342: ri.relationship_type_id,
2352: SYSDATE, -- program_update_date
2353: ri.organization_id
2354: from mtl_related_items ri,
2355: bom_cto_src_orgs bcso,
2356: bom_cto_order_lines_upg bcolu
2357: where bcolu.config_item_id is not null
2358: and bcolu.status = 'BCSO'
2359: and ri.inventory_item_id = bcolu.inventory_item_id
2360: and bcso.model_item_id = bcolu.inventory_item_id
2416: program_id,
2417: program_update_date,
2418: encumbrance_account
2419: )
2420: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2421: distinct
2422: bcolu.config_item_id,
2423: isi.ORGANIZATION_ID,
2424: isi.SECONDARY_INVENTORY,
2461: isi.ENCUMBRANCE_ACCOUNT
2462: from
2463: mtl_item_sub_inventories isi,
2464: bom_cto_src_orgs bcso,
2465: bom_cto_order_lines_upg bcolu
2466: where bcolu.config_item_id is not null
2467: and bcolu.status = 'BCSO'
2468: and isi.organization_id = bcso.organization_id
2469: and isi.inventory_item_id = bcolu.inventory_item_id
2501: program_application_id,
2502: program_id,
2503: program_update_date
2504: )
2505: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2506: distinct
2507: bcolu.config_item_id,
2508: sl.organization_id,
2509: sl.secondary_locator,
2521: SYSDATE -- program_update_date
2522: from
2523: mtl_secondary_locators sl,
2524: bom_cto_src_orgs bcso,
2525: bom_cto_order_lines_upg bcolu
2526: where bcolu.config_item_id is not null
2527: and bcolu.status = 'BCSO'
2528: and sl.organization_id = bcso.organization_id
2529: and bcolu.inventory_item_id = sl.inventory_item_id
2543:
2544: lStmtNumber := 170;
2545:
2546: --start bugfix 4539578
2547: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2548: distinct
2549: bcolu.config_item_id,
2550: cr_b.organization_id,
2551: cr_b.cross_reference_type,
2559: t_org_independent_flag
2560: from
2561: mtl_cross_references_b cr_b,
2562: bom_cto_src_orgs bcso,
2563: bom_cto_order_lines_upg bcolu
2564: where bcolu.config_item_id is not null
2565: and bcolu.status = 'BCSO'
2566: and (cr_b.organization_id = bcso.organization_id or
2567: cr_b.organization_id is NULL)
2724: program_application_id,
2725: program_id,
2726: program_update_date
2727: )
2728: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2729: distinct
2730: bcolu.config_item_id,
2731: sd.organization_id,
2732: sd.subinventory_code,
2742: SYSDATE -- program_update_date
2743: from
2744: mtl_item_sub_defaults sd,
2745: bom_cto_src_orgs bcso,
2746: bom_cto_order_lines_upg bcolu
2747: where bcolu.config_item_id is not null
2748: and bcolu.status = 'BCSO'
2749: and sd.organization_id = bcso.organization_id
2750: and sd.inventory_item_id = bcolu.inventory_item_id
2780: program_application_id,
2781: program_id,
2782: program_update_date
2783: )
2784: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2785: distinct
2786: bcolu.config_item_id,
2787: ld.organization_id,
2788: ld.locator_id,
2799: SYSDATE -- program_update_date
2800: from
2801: mtl_item_loc_defaults ld,
2802: bom_cto_src_orgs bcso,
2803: bom_cto_order_lines_upg bcolu
2804: where bcolu.config_item_id is not null
2805: and bcolu.status = 'BCSO'
2806: and ld.organization_id = bcso.organization_id
2807: and ld.inventory_item_id = bcso.model_item_id
3594: mtl_parameters mp1,
3595: mtl_system_items_b m, -- model
3596: mtl_system_items_b c, -- config
3597: bom_cto_src_orgs bcso,
3598: bom_cto_order_lines_upg bcolu
3599: where bcolu.config_item_id is not null
3600: and bcolu.status = 'BCSO'
3601: and m.inventory_item_id = bcolu.inventory_item_id
3602: -- get config item row for any one org
4008: outsourced_assembly
4009:
4010:
4011: )
4012: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
4013: distinct
4014: bcolu.config_item_id,
4015: m.organization_id,
4016: sysdate,
4338: from
4339: mtl_system_items_b m, -- model
4340: mtl_system_items_b config, -- config
4341: bom_cto_src_orgs bcso,
4342: bom_cto_order_lines_upg bcolu
4343: where bcolu.config_item_id is not null
4344: and bcolu.status = 'BCSO'
4345: and m.inventory_item_id = bcolu.inventory_item_id
4346: -- get config item row for any one org