[Home] [Help]
107: xMsgData OUT NOCOPY varchar2)
108: IS
109:
110: CURSOR c_lines(l_seq number) IS
111: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
112: distinct ato_line_id, program_id -- , perform_match
113: from bom_cto_order_lines_upg
114: where sequence = l_seq
115: and status = 'UPG';
109:
110: CURSOR c_lines(l_seq number) IS
111: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
112: distinct ato_line_id, program_id -- , perform_match
113: from bom_cto_order_lines_upg
114: where sequence = l_seq
115: and status = 'UPG';
116:
117: CURSOR c_configs(l_ato_line_id number) IS
115: and status = 'UPG';
116:
117: CURSOR c_configs(l_ato_line_id number) IS
118: select substrb(concatenated_segments,1,50) config_name
119: from bom_cto_order_lines_upg bcolu,
120: mtl_system_items_kfv msi
121: where bcolu.ato_line_id = l_ato_line_id
122: and bcolu.config_item_id is not null
123: and bcolu.config_item_id = msi.inventory_item_id
123: and bcolu.config_item_id = msi.inventory_item_id
124: and bcolu.ship_from_org_id = msi.organization_id;
125:
126: CURSOR c_src_lines(l_seq number) IS
127: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
128: distinct ato_line_id, program_id
129: from bom_cto_order_lines_upg
130: where sequence = l_seq
131: and status = 'ITEM';
125:
126: CURSOR c_src_lines(l_seq number) IS
127: select /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
128: distinct ato_line_id, program_id
129: from bom_cto_order_lines_upg
130: where sequence = l_seq
131: and status = 'ITEM';
132:
133: CURSOR c_copy_src_rules(p_ato_line_id number) IS
136: bcolu.config_creation,
137: bcso.create_src_rules,
138: bcso.model_item_id,
139: bcso.config_item_id
140: from bom_cto_order_lines_upg bcolu,
141: bom_cto_src_orgs bcso
142: where bcolu.ato_line_id = p_ato_line_id
143: and bcolu.bom_item_type = '1'
144: and nvl(bcolu.wip_supply_type, 1) <> 6
190:
191: BEGIN
192: select 1
193: into l_exists
194: from bom_cto_order_lines_upg
195: where sequence = l_seq
196: and rownum = 1;
197:
198: EXCEPTION
258: END LOOP;
259: lstmtnum := 40;
260: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
261:
262: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
263: bom_cto_order_lines_upg
264: set status = 'ERROR'
265: where ato_line_id = v_lines.ato_line_id;
266:
259: lstmtnum := 40;
260: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
261:
262: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
263: bom_cto_order_lines_upg
264: set status = 'ERROR'
265: where ato_line_id = v_lines.ato_line_id;
266:
267: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
288: -- Delete from bcso_b
289: --
290: delete from bom_cto_src_orgs_b
291: where line_id in
292: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
293: line_id
294: from bom_cto_order_lines_upg
295: where ato_line_id = v_lines.ato_line_id
296: and config_item_id is not null
290: delete from bom_cto_src_orgs_b
291: where line_id in
292: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
293: line_id
294: from bom_cto_order_lines_upg
295: where ato_line_id = v_lines.ato_line_id
296: and config_item_id is not null
297: and status = 'UPG');
298: WriteToLog('Lines deleted from bcso_b::'||sql%rowcount, 2);
301: -- delete from bcmo
302: --
303: delete from bom_cto_model_orgs
304: where config_item_id in
305: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
306: distinct config_item_id
307: from bom_cto_order_lines_upg
308: where ato_line_id = v_lines.ato_line_id
309: and status = 'UPG');
303: delete from bom_cto_model_orgs
304: where config_item_id in
305: (select /* INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 BOM_CTO_ORDER_LINES_UPG_N2 ) */
306: distinct config_item_id
307: from bom_cto_order_lines_upg
308: where ato_line_id = v_lines.ato_line_id
309: and status = 'UPG');
310:
311: WriteToLog('Lines deleted from bcmo::'||sql%rowcount, 2);
352: END LOOP;
353: lstmtnum := 40;
354: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
355:
356: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
357: bom_cto_order_lines_upg
358: set status = 'ERROR'
359: where ato_line_id = v_lines.ato_line_id;
360:
353: lstmtnum := 40;
354: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
355:
356: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
357: bom_cto_order_lines_upg
358: set status = 'ERROR'
359: where ato_line_id = v_lines.ato_line_id;
360:
361: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
374:
375: /*
376: begin
377: select perform_match into v_lines_perform_match
378: from bom_cto_order_lines_upg
379: where line_id = v_lines.ato_line_id ;
380:
381: exception
382: when others then
425: <
426: EXIT WHEN c_lines%NOTFOUND;
427: END LOOP; /* c_lines */
428:
429: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
430: bom_cto_order_lines_upg
431: set status = 'BCSO'
432: where sequence = l_seq
433: and status = 'UPG';
426: EXIT WHEN c_lines%NOTFOUND;
427: END LOOP; /* c_lines */
428:
429: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
430: bom_cto_order_lines_upg
431: set status = 'BCSO'
432: where sequence = l_seq
433: and status = 'UPG';
434:
456: and msim.organization_id = msic.organization_id)
457: where msic.inventory_item_id in
458: (
459: select distinct config_item_id
460: from bom_cto_order_lines_upg
461: where status = 'BCSO'
462: and config_item_id is not null
463: )
464: and exists (select 'x'
527: -- The hint is not needed since this update would
528: -- impact all the records in the table and so an
529: -- index scan is not efficient
530: --
531: --update /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
532: update
533: bom_cto_order_lines_upg
534: set status = 'ITEM'
535: where status = 'BCSO';
529: -- index scan is not efficient
530: --
531: --update /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
532: update
533: bom_cto_order_lines_upg
534: set status = 'ITEM'
535: where status = 'BCSO';
536:
537: WriteToLog('Items created. Status updated to ITEM for rows::'||sql%rowcount, 1);
553:
554: BEGIN
555: select 1
556: into l_exists
557: from bom_cto_order_lines_upg
558: where sequence = l_seq
559: and rownum = 1;
560:
561: EXCEPTION
617: END LOOP;
618: lstmtnum := 40;
619: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
620:
621: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
622: bom_cto_order_lines_upg
623: set status = 'ERROR'
624: where ato_line_id = v_lines.ato_line_id;
625:
618: lstmtnum := 40;
619: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
620:
621: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
622: bom_cto_order_lines_upg
623: set status = 'ERROR'
624: where ato_line_id = v_lines.ato_line_id;
625:
626: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
735: WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
736: END LOOP;
737: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
738:
739: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
740: bom_cto_order_lines_upg
741: set status = 'ERROR'
742: where ato_line_id = v_lines.ato_line_id;
743:
736: END LOOP;
737: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
738:
739: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
740: bom_cto_order_lines_upg
741: set status = 'ERROR'
742: where ato_line_id = v_lines.ato_line_id;
743:
744: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
799: WriteToLog('Error : '||substr(l_msg_data,1,250), 1);
800: END LOOP;
801: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
802:
803: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
804: bom_cto_order_lines_upg
805: set status = 'ERROR'
806: where ato_line_id = v_lines.ato_line_id;
807:
800: END LOOP;
801: WriteToLog('++++++++++++++++++++++++++++++++++++++++++', 1);
802:
803: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
804: bom_cto_order_lines_upg
805: set status = 'ERROR'
806: where ato_line_id = v_lines.ato_line_id;
807:
808: WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
820: <
821: EXIT WHEN c_src_lines%NOTFOUND;
822: END LOOP; /* c_src_lines */
823:
824: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
825: bom_cto_order_lines_upg
826: set status = 'CTO_SRC'
827: where sequence = l_seq
828: and status = 'ITEM';
821: EXIT WHEN c_src_lines%NOTFOUND;
822: END LOOP; /* c_src_lines */
823:
824: update /*+ INDEX( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
825: bom_cto_order_lines_upg
826: set status = 'CTO_SRC'
827: where sequence = l_seq
828: and status = 'ITEM';
829:
893: bcso.config_item_id
894: from bom_cto_model_orgs bcmo,
895: bom_cto_src_orgs_b bcso
896: where bcso.config_item_id = bcmo.config_item_id and
897: bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
898: distinct config_item_id
899: from bom_cto_order_lines_upg
900: where ato_line_id = p_ato_line_id and
901: nvl(config_creation, 1) = 3 and
895: bom_cto_src_orgs_b bcso
896: where bcso.config_item_id = bcmo.config_item_id and
897: bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
898: distinct config_item_id
899: from bom_cto_order_lines_upg
900: where ato_line_id = p_ato_line_id and
901: nvl(config_creation, 1) = 3 and
902: config_item_id is not null and
903: status = 'UPG' and
942:
943: DELETE from bom_cto_src_orgs_b
944: where (line_id) IN (select distinct bcso.line_id
945: from bom_cto_src_orgs_b bcso
946: where bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
947: distinct config_item_id
948: from bom_cto_order_lines_upg
949: where ato_line_id = p_ato_line_id
950: and nvl(config_creation, 1) = 3
944: where (line_id) IN (select distinct bcso.line_id
945: from bom_cto_src_orgs_b bcso
946: where bcso.config_item_id in (select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
947: distinct config_item_id
948: from bom_cto_order_lines_upg
949: where ato_line_id = p_ato_line_id
950: and nvl(config_creation, 1) = 3
951: and config_item_id is not null
952: and status = 'UPG'
1004:
1005: WriteToLog('After insert into bom_cto_src_orgs_b count is :'||sql%rowcount, 3);
1006:
1007: lStmtNum := 40;
1008: update /* INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1009: bom_cto_order_lines_upg
1010: set status = 'BCSO'
1011: where line_id in ( select distinct line_id
1012: from bom_cto_src_orgs_b
1005: WriteToLog('After insert into bom_cto_src_orgs_b count is :'||sql%rowcount, 3);
1006:
1007: lStmtNum := 40;
1008: update /* INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1009: bom_cto_order_lines_upg
1010: set status = 'BCSO'
1011: where line_id in ( select distinct line_id
1012: from bom_cto_src_orgs_b
1013: where config_item_id in( select /*+INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
1009: bom_cto_order_lines_upg
1010: set status = 'BCSO'
1011: where line_id in ( select distinct line_id
1012: from bom_cto_src_orgs_b
1013: where config_item_id in( select /*+INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
1014: distinct config_item_id
1015: from bom_cto_order_lines_upg
1016: where ato_line_id = p_ato_line_id
1017: and nvl(config_creation, 1) = 3
1011: where line_id in ( select distinct line_id
1012: from bom_cto_src_orgs_b
1013: where config_item_id in( select /*+INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4 ) */
1014: distinct config_item_id
1015: from bom_cto_order_lines_upg
1016: where ato_line_id = p_ato_line_id
1017: and nvl(config_creation, 1) = 3
1018: and config_item_id is not null
1019: and status = 'UPG'));
1062: bcolu.config_item_id config_item_id
1063: from
1064: cst_item_costs c,
1065: bom_cto_src_orgs bcso,
1066: bom_cto_order_lines_upg bcolu,
1067: mtl_parameters mp1
1068: where bcolu.config_item_id is not null
1069: and bcolu.status = 'BCSO'
1070: and c.organization_id = bcso.organization_id
1080: where inventory_item_id = bcolu.config_item_id
1081: and organization_id = bcso.organization_id);
1082:
1083: CURSOR c_bcolu_cfg IS
1084: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
1085: distinct
1086: config_item_id,
1087: inventory_item_id,
1088: line_id
1085: distinct
1086: config_item_id,
1087: inventory_item_id,
1088: line_id
1089: from bom_cto_order_lines_upg bcolu
1090: where bcolu.config_item_id is not null
1091: and bcolu.status = 'BCSO';
1092:
1093: CURSOR c_get_org_id IS
1090: where bcolu.config_item_id is not null
1091: and bcolu.status = 'BCSO';
1092:
1093: CURSOR c_get_org_id IS
1094: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N2 ) */
1095: bcolu.config_item_id cfg_item_id,
1096: bcolu.inventory_item_id model_item_id,
1097: msi.organization_id src_org_id
1098: from mtl_system_items msi,
1095: bcolu.config_item_id cfg_item_id,
1096: bcolu.inventory_item_id model_item_id,
1097: msi.organization_id src_org_id
1098: from mtl_system_items msi,
1099: bom_cto_order_lines_upg bcolu
1100: where bcolu.config_item_id is not null
1101: and bcolu.status = 'BCSO'
1102: and msi.inventory_item_id = bcolu.config_item_id
1103: and not exists
1134: --kkonada R12
1135: --for mtl_cross_references_b
1136: --bug# 4539578
1137:
1138: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
1139: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
1140: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
1141: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
1142: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1164: creation_date,
1165: created_by,
1166: last_update_login
1167: )
1168: select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1169: distinct
1170: bcolu.config_item_id,
1171: m.organization_id,
1172: l.language_code,
1179: gLoginId --last_update_login
1180: from
1181: mtl_system_items_tl m,
1182: bom_cto_src_orgs bcso,
1183: bom_cto_order_lines_upg bcolu,
1184: fnd_languages l
1185: where bcolu.config_item_id is not null
1186: and bcolu.status = 'BCSO'
1187: and m.inventory_item_id = bcolu.inventory_item_id
1214: program_application_id,
1215: program_id,
1216: program_update_date,
1217: request_id)
1218: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1219: distinct
1220: bcolu.config_item_id,
1221: m.organization_id,
1222: m.inventory_item_status_code,
1232: sysdate,
1233: null -- req_id
1234: from mtl_system_items m,
1235: bom_cto_src_orgs bcso,
1236: bom_cto_order_lines_upg bcolu
1237: where bcolu.config_item_id is not null
1238: and bcolu.status = 'BCSO'
1239: and m.inventory_item_id = bcolu.inventory_item_id
1240: and bcso.model_item_id = bcolu.inventory_item_id
1399: from
1400: mtl_parameters MP1,
1401: cst_item_costs C,
1402: cst_item_cost_details CICD,
1403: bom_cto_order_lines_upg bcolu
1404: where
1405: C.organization_id = MP1.organization_id
1406: and C.inventory_item_id = bcolu.inventory_item_id -- pModelId
1407: and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
1417: MP2.cost_organization_id
1418: from mtl_parameters mp2,
1419: mtl_parameters mp3,
1420: bom_cto_src_orgs bcso,
1421: bom_cto_order_lines_upg bcolu
1422: where bcolu.config_item_id is not null
1423: and bcolu.status = 'BCSO'
1424: and bcso.model_item_id = bcolu.inventory_item_id
1425: and bcso.model_item_id = bcolu.inventory_item_id
1533: attribute13,
1534: attribute14,
1535: attribute15
1536: )
1537: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1538: distinct
1539: bcolu.config_item_id, -- INVENTORY_ITEM_ID
1540: mp1.cost_organization_id,
1541: c.cost_type_id,
1590: mtl_parameters MP1,
1591: cst_item_costs C,
1592: mtl_system_items S,
1593: bom_cto_src_orgs bcso,
1594: bom_cto_order_lines_upg bcolu
1595: where bcolu.config_item_id is not null
1596: and bcolu.status = 'BCSO'
1597: and S.organization_id = C.organization_id
1598: and S.inventory_item_id = C.inventory_item_id
1756: attribute13,
1757: attribute14,
1758: attribute15
1759: )
1760: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1761: distinct
1762: bcolu.config_item_id, -- INVENTORY_ITEM_ID
1763: mp1.cost_organization_id,
1764: l_cto_cost_type_id, -- CTO cost_type_id,
1813: mtl_parameters MP1,
1814: cst_item_costs C,
1815: mtl_system_items S,
1816: bom_cto_src_orgs bcso,
1817: bom_cto_order_lines_upg bcolu
1818: where bcolu.config_item_id is not null
1819: and bcolu.status = 'BCSO'
1820: and S.organization_id = C.organization_id
1821: and S.inventory_item_id = C.inventory_item_id
1926: attribute13,
1927: attribute14,
1928: attribute15
1929: )
1930: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
1931: distinct
1932: bcolu.config_item_id, -- inventory_item_id
1933: c.cost_type_id,
1934: sysdate, -- last_update_date
1976: mtl_parameters MP1,
1977: cst_item_cost_details C,
1978: mtl_system_items S,
1979: bom_cto_src_orgs bcso,
1980: bom_cto_order_lines_upg bcolu
1981: where bcolu.config_item_id is not null
1982: and bcolu.status = 'BCSO'
1983: and S.organization_id = C.organization_id
1984: and S.inventory_item_id = C.inventory_item_id
2048: attribute13,
2049: attribute14,
2050: attribute15
2051: )
2052: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2053: distinct
2054: bcolu.config_item_id, -- inventory_item_id
2055: l_cto_cost_type_id, -- CTO cost_type_id,
2056: sysdate, -- last_update_date
2098: mtl_parameters MP1,
2099: cst_item_cost_details C,
2100: mtl_system_items S,
2101: bom_cto_src_orgs bcso,
2102: bom_cto_order_lines_upg bcolu
2103: where bcolu.config_item_id is not null
2104: and bcolu.status = 'BCSO'
2105: and S.organization_id = C.organization_id
2106: and S.inventory_item_id = C.inventory_item_id
2182: program_id,
2183: program_update_date,
2184: request_id
2185: )
2186: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2187: distinct
2188: bcolu.config_item_id, -- Inventory_item_id
2189: E.element_name, -- element_name
2190: sysdate, -- last_update_date
2201: NULL -- request_id
2202: from mtl_system_items s,
2203: mtl_descr_element_values D,
2204: mtl_descriptive_elements E,
2205: bom_cto_order_lines_upg bcolu
2206: where bcolu.config_item_id is not null
2207: and bcolu.status = 'BCSO'
2208: and D.inventory_item_id = S.inventory_item_id
2209: and s.inventory_item_id = bcolu.inventory_item_id
2251: program_id,
2252: program_update_date,
2253: organization_id
2254: )
2255: -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2256: select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2257: distinct
2258: bcolu.config_item_id,
2259: ic.category_set_id,
2270: ic.organization_id
2271: from
2272: mtl_item_categories ic,
2273: bom_cto_src_orgs bcso,
2274: bom_cto_order_lines_upg bcolu
2275: where bcolu.config_item_id is not null
2276: and bcolu.status = 'BCSO'
2277: and ic.inventory_item_id = bcolu.inventory_item_id
2278: and ic.organization_id = bcso.organization_id
2309: program_id,
2310: program_update_date,
2311: organization_id
2312: )
2313: -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2314: select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2315: distinct
2316: bcolu.config_item_id,
2317: mcsb.category_set_id,
2330: mtl_item_categories ic,
2331: mtl_category_sets_b mcsb,
2332: mtl_default_category_sets mdcs,
2333: bom_cto_src_orgs bcso,
2334: bom_cto_order_lines_upg bcolu
2335: where bcolu.config_item_id is not null
2336: and bcolu.status = 'BCSO'
2337: and bcolu.inventory_item_id = ic.inventory_item_id
2338: and ic.organization_id = bcso.organization_id
2376: program_id,
2377: program_update_date,
2378: organization_id
2379: )
2380: -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2381: select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2382: distinct
2383: bcolu.config_item_id,
2384: ic.category_set_id,
2395: ic.organization_id
2396: from
2397: mtl_item_categories ic,
2398: bom_cto_src_orgs bcso,
2399: bom_cto_order_lines_upg bcolu
2400: where bcolu.config_item_id is not null
2401: and bcolu.status = 'BCSO'
2402: and ic.inventory_item_id = bcolu.inventory_item_id
2403: and ic.organization_id = bcso.organization_id
2435: program_id,
2436: program_update_date,
2437: organization_id
2438: )
2439: -- select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2440: select /*+ leading( bcolu, ic1, mp1, bcso, mp2, ic) */
2441: distinct
2442: bcolu.config_item_id,
2443: mcsb.category_set_id,
2456: mtl_item_categories ic,
2457: mtl_category_sets_b mcsb,
2458: mtl_default_category_sets mdcs,
2459: bom_cto_src_orgs bcso,
2460: bom_cto_order_lines_upg bcolu
2461: where bcolu.config_item_id is not null
2462: and bcolu.status = 'BCSO'
2463: and bcolu.inventory_item_id = ic.inventory_item_id
2464: and ic.organization_id = bcso.organization_id
2503: program_id,
2504: program_update_date,
2505: organization_id
2506: )
2507: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2508: distinct
2509: bcolu.config_item_id,
2510: ri.related_item_id,
2511: ri.relationship_type_id,
2521: SYSDATE, -- program_update_date
2522: ri.organization_id
2523: from mtl_related_items ri,
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 ri.inventory_item_id = bcolu.inventory_item_id
2529: and bcso.model_item_id = bcolu.inventory_item_id
2585: program_id,
2586: program_update_date,
2587: encumbrance_account
2588: )
2589: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2590: distinct
2591: bcolu.config_item_id,
2592: isi.ORGANIZATION_ID,
2593: isi.SECONDARY_INVENTORY,
2630: isi.ENCUMBRANCE_ACCOUNT
2631: from
2632: mtl_item_sub_inventories isi,
2633: bom_cto_src_orgs bcso,
2634: bom_cto_order_lines_upg bcolu
2635: where bcolu.config_item_id is not null
2636: and bcolu.status = 'BCSO'
2637: and isi.organization_id = bcso.organization_id
2638: and isi.inventory_item_id = bcolu.inventory_item_id
2670: program_application_id,
2671: program_id,
2672: program_update_date
2673: )
2674: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2675: distinct
2676: bcolu.config_item_id,
2677: sl.organization_id,
2678: sl.secondary_locator,
2690: SYSDATE -- program_update_date
2691: from
2692: mtl_secondary_locators sl,
2693: bom_cto_src_orgs bcso,
2694: bom_cto_order_lines_upg bcolu
2695: where bcolu.config_item_id is not null
2696: and bcolu.status = 'BCSO'
2697: and sl.organization_id = bcso.organization_id
2698: and bcolu.inventory_item_id = sl.inventory_item_id
2712:
2713: lStmtNumber := 170;
2714:
2715: --start bugfix 4539578
2716: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2717: distinct
2718: bcolu.config_item_id,
2719: cr_b.organization_id,
2720: cr_b.cross_reference_type,
2728: t_org_independent_flag
2729: from
2730: mtl_cross_references_b cr_b,
2731: bom_cto_src_orgs bcso,
2732: bom_cto_order_lines_upg bcolu
2733: where bcolu.config_item_id is not null
2734: and bcolu.status = 'BCSO'
2735: and (cr_b.organization_id = bcso.organization_id or
2736: cr_b.organization_id is NULL)
2893: program_application_id,
2894: program_id,
2895: program_update_date
2896: )
2897: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2898: distinct
2899: bcolu.config_item_id,
2900: sd.organization_id,
2901: sd.subinventory_code,
2911: SYSDATE -- program_update_date
2912: from
2913: mtl_item_sub_defaults sd,
2914: bom_cto_src_orgs bcso,
2915: bom_cto_order_lines_upg bcolu
2916: where bcolu.config_item_id is not null
2917: and bcolu.status = 'BCSO'
2918: and sd.organization_id = bcso.organization_id
2919: and sd.inventory_item_id = bcolu.inventory_item_id
2949: program_application_id,
2950: program_id,
2951: program_update_date
2952: )
2953: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
2954: distinct
2955: bcolu.config_item_id,
2956: ld.organization_id,
2957: ld.locator_id,
2968: SYSDATE -- program_update_date
2969: from
2970: mtl_item_loc_defaults ld,
2971: bom_cto_src_orgs bcso,
2972: bom_cto_order_lines_upg bcolu
2973: where bcolu.config_item_id is not null
2974: and bcolu.status = 'BCSO'
2975: and ld.organization_id = bcso.organization_id
2976: and ld.inventory_item_id = bcso.model_item_id
3806: mtl_parameters mp1,
3807: mtl_system_items_b m, -- model
3808: mtl_system_items_b c, -- config
3809: bom_cto_src_orgs bcso,
3810: bom_cto_order_lines_upg bcolu
3811: where bcolu.config_item_id is not null
3812: and bcolu.status = 'BCSO'
3813: and m.inventory_item_id = bcolu.inventory_item_id
3814: -- get config item row for any one org
4237: outsourced_assembly
4238:
4239:
4240: )
4241: select /*+ INDEX ( BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1 ) */
4242: distinct
4243: bcolu.config_item_id,
4244: m.organization_id,
4245: sysdate,
4590: from
4591: mtl_system_items_b m, -- model
4592: mtl_system_items_b config, -- config
4593: bom_cto_src_orgs bcso,
4594: bom_cto_order_lines_upg bcolu
4595: where bcolu.config_item_id is not null
4596: and bcolu.status = 'BCSO'
4597: and m.inventory_item_id = bcolu.inventory_item_id
4598: -- get config item row for any one org