eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CTO_UPDATE_CONFIGS_PK dependencies on BOM_CTO_ORDER_LINES

Line 112: from bom_cto_order_lines_upg

108: Redundant cursor
109:
110: CURSOR c_seq(l_seq number) IS
111: select distinct sequence
112: from bom_cto_order_lines_upg
113: where sequence = l_seq;
114: */
115:
116: l_return_status varchar2(1);

Line 131: from bom_cto_order_lines_upg

127: l_cto_aset_id number;
128:
129: Cursor Attachment_cur is
130: select distinct ato_line_id
131: from bom_cto_order_lines_upg
132: where status = 'CTO_SRC'
133: and line_id = ato_line_id;
134:
135:

Line 185: from bom_cto_order_lines_upg bcolu

181: l_stmt_num := 10;
182: delete from bom_cto_src_orgs_b
183: where line_id in (
184: select bcolu.line_id
185: from bom_cto_order_lines_upg bcolu
186: where bcolu.config_item_id is not null
187: and not exists (
188: select 'exists'
189: from oe_order_lines_all oel

Line 194: delete from bom_cto_order_lines_upg;

190: where oel.line_id = bcolu.line_id));
191: WriteToLog('Rows deleted from bcso_b::'|| sql%rowcount, 1);
192:
193: l_stmt_num := 15;
194: delete from bom_cto_order_lines_upg;
195: WriteToLog('Rows deleted from bcol_upg::'|| sql%rowcount, 1);
196:
197: BEGIN
198: select assignment_set_id

Line 298: from bom_cto_order_lines_upg bcolu

294: (select config_item_id
295: from bom_ato_configurations bac
296: where not exists
297: (select 'exists'
298: from bom_cto_order_lines_upg bcolu
299: where bcolu.config_item_id = bac.config_item_id)
300: -- and not on open order lines
301: and not exists
302: (select 'exists'

Line 304: bom_cto_order_lines bcol

300: -- and not on open order lines
301: and not exists
302: (select 'exists'
303: from oe_order_lines_all oel,
304: bom_cto_order_lines bcol
305: where bcol.config_item_id = bac.config_item_id
306: and bcol.ato_line_id = oel.ato_line_id
307: and nvl(oel.open_flag, 'N') = 'Y'
308: and oel.item_type_code='CONFIG')

Line 365: from bom_cto_order_lines_upg bcolu

361: where bac.base_model_id = mcat.inventory_item_id
362: and mcat.category_id = p_cat_id
363: and not exists
364: (select 'exists'
365: from bom_cto_order_lines_upg bcolu
366: where bcolu.config_item_id = bac.config_item_id
367: )
368: and bac.config_item_id not in
369: (select nvl( bcol.config_item_id,-1)

Line 371: bom_cto_order_lines bcol

367: )
368: and bac.config_item_id not in
369: (select nvl( bcol.config_item_id,-1)
370: from oe_order_lines_all oel,
371: bom_cto_order_lines bcol
372: where bcol.ato_line_id = oel.ato_line_id
373: and oel.item_type_code = 'CONFIG'
374: and open_flag = 'Y'
375: )

Line 426: from bom_cto_order_lines_upg bcolu

422: and inventory_item_id = p_config_id
423: -- not being upgraded
424: and not exists
425: (select 'exists'
426: from bom_cto_order_lines_upg bcolu
427: where bcolu.config_item_id = p_config_id)
428: -- and not on open order lines
429: and not exists /* bug 3399310 sushant changed the query to identify config item exists */
430: (select 'exists'

Line 432: bom_cto_order_lines bcol

428: -- and not on open order lines
429: and not exists /* bug 3399310 sushant changed the query to identify config item exists */
430: (select 'exists'
431: from oe_order_lines_all oel,
432: bom_cto_order_lines bcol
433: where bcol.config_item_id = p_config_id
434: and bcol.line_id = oel.ato_line_id
435: and oel.item_type_code = 'CONFIG'
436: and nvl(oel.open_flag, 'N') = 'Y')

Line 453: from bom_cto_order_lines_upg;

449: -- if no rows populated into bcol, return
450: --
451: select count(*)
452: into l_bcolu_count
453: from bom_cto_order_lines_upg;
454: WriteToLog('Rows populated in bcol_upg::'||l_bcolu_count, 1);
455:
456: IF l_bcolu_count = 0 THEN
457: WriteToLog('+++++++++++++++++++++++++++++++++++++++', 1);

Line 482: from bom_cto_order_lines_upg

478: l_stmt_num := 51;
479:
480: select count(*)
481: into l_bcolu_count
482: from bom_cto_order_lines_upg
483: where status <>'ERROR';
484:
485: WriteToLog('Rows populated in bcol_upg and NOT in error status::'||l_bcolu_count, 1);
486:

Line 517: update bom_cto_order_lines_upg bcolu

513: WHILE (TRUE) LOOP
514:
515: l_seq := l_seq + 1;
516:
517: update bom_cto_order_lines_upg bcolu
518: set bcolu.sequence = l_seq
519: where bcolu.ato_line_id in
520: (select ato_line_id
521: from bom_cto_order_lines_upg bcolu2

Line 521: from bom_cto_order_lines_upg bcolu2

517: update bom_cto_order_lines_upg bcolu
518: set bcolu.sequence = l_seq
519: where bcolu.ato_line_id in
520: (select ato_line_id
521: from bom_cto_order_lines_upg bcolu2
522: where bcolu2.ato_line_id = bcolu2.line_id
523: and bcolu2.status IN ('UPG')
524: and rownum < G_BATCH_SIZE + 1
525: and bcolu2.sequence is null);

Line 600: from bom_cto_order_lines_upg

596: l_seq := l_seq + 1;
597: BEGIN
598: select 'exists'
599: into l_exists
600: from bom_cto_order_lines_upg
601: where sequence = l_seq
602: and rownum = 1;
603:
604: EXCEPTION

Line 731: from bom_cto_order_lines_upg bcolu

727: from bom_ato_configurations bac,
728: mtl_system_items msi
729: where NOT EXISTS
730: (select 'exists'
731: from bom_cto_order_lines_upg bcolu
732: where bcolu.config_item_id = bac.config_item_id)
733: and bac.base_model_id = msi.inventory_item_id
734: and bac.organization_id = msi.organization_id
735: and nvl(msi.config_orgs, '1') = '3';

Line 763: from bom_cto_order_lines_upg bcolu

759: and nvl(msi2.config_orgs, '1') = '3')
760: -- and not already in bcol_upg
761: and NOT EXISTS
762: (select 'exists'
763: from bom_cto_order_lines_upg bcolu
764: where bcolu.config_item_id = bac.config_item_id);
765:
766: l_match NUMBER;
767: l_exists varchar2(1);

Line 808: from bom_cto_order_lines_upg;

804: WriteToLog('sql 1', 3);
805:
806: select count(line_id)
807: into l_count
808: from bom_cto_order_lines_upg;
809: WriteToLog('kiran cont in bcol_upgs is =>'||l_count);
810:
811: l_stmt_num := 60;
812: insert into bom_cto_order_lines_upg

Line 812: insert into bom_cto_order_lines_upg

808: from bom_cto_order_lines_upg;
809: WriteToLog('kiran cont in bcol_upgs is =>'||l_count);
810:
811: l_stmt_num := 60;
812: insert into bom_cto_order_lines_upg
813: (
814: ATO_LINE_ID
815: , BATCH_ID
816: , BOM_ITEM_TYPE

Line 887: from bom_cto_order_lines bcol2

883: , 'UPG' --STATUS
884: , nvl(mtl.config_orgs, '1')
885: --changed the where clause to use a subquery
886: --bugfix 3841575
887: from bom_cto_order_lines bcol2
888: , mtl_system_items mtl
889: -- select entire configuration
890: where mtl.inventory_item_id = bcol2.inventory_item_id
891: and mtl.organization_id = bcol2.ship_from_org_id

Line 894: from bom_cto_order_lines bcol1

890: where mtl.inventory_item_id = bcol2.inventory_item_id
891: and mtl.organization_id = bcol2.ship_from_org_id
892: and bcol2.ato_line_id in
893: (select distinct bcol1.ato_line_id
894: from bom_cto_order_lines bcol1
895: , oe_order_lines_all oel
896: , mtl_system_items msi
897: -- for configs whose models have attr=2,3
898: where bcol1.config_item_id is not null

Line 917: insert into bom_cto_order_lines_upg

913: -- mark as UPG
914: --
915: WriteToLog('sql 2', 3);
916: l_stmt_num := 70;
917: insert into bom_cto_order_lines_upg
918: (
919: ATO_LINE_ID
920: , BATCH_ID
921: , BOM_ITEM_TYPE

Line 990: from bom_cto_order_lines bcol1

986: , 'N' --REUSE_CONFIG
987: , bcol2.QTY_PER_PARENT_MODEL
988: , 'UPG' --STATUS
989: , nvl(msi.config_orgs, '1')
990: from bom_cto_order_lines bcol1
991: , bom_cto_order_lines bcol2
992: , bom_ato_configurations bac
993: , oe_order_lines_all oel
994: , mtl_system_items msi

Line 991: , bom_cto_order_lines bcol2

987: , bcol2.QTY_PER_PARENT_MODEL
988: , 'UPG' --STATUS
989: , nvl(msi.config_orgs, '1')
990: from bom_cto_order_lines bcol1
991: , bom_cto_order_lines bcol2
992: , bom_ato_configurations bac
993: , oe_order_lines_all oel
994: , mtl_system_items msi
995: -- base model has item attr = 3

Line 1017: insert into bom_cto_order_lines_upg

1013: -- mark as UPG
1014: --
1015: WriteToLog('sql 3', 3);
1016: l_stmt_num := 80;
1017: insert into bom_cto_order_lines_upg
1018: (
1019: ATO_LINE_ID
1020: , BATCH_ID
1021: , BOM_ITEM_TYPE

Line 1091: , bom_cto_order_lines bcol

1087: , bcol.QTY_PER_PARENT_MODEL
1088: , 'UPG' --STATUS
1089: , nvl(msi.config_orgs, '1')
1090: from bom_ato_configurations bac
1091: , bom_cto_order_lines bcol
1092: , mtl_system_items msi
1093: -- base model has item attr = 3
1094: where bac.base_model_id = msi.inventory_item_id
1095: and bac.organization_id = msi.organization_id

Line 1100: from bom_cto_order_lines_upg bcolu

1096: and nvl(msi.config_orgs, '1') = '3'
1097: -- and not already in bcol_upg
1098: and NOT EXISTS
1099: (select 'exists'
1100: from bom_cto_order_lines_upg bcolu
1101: where bcolu.config_item_id = bac.config_item_id)
1102: -- select first ato_line_id in bcol
1103: and bcol.ato_line_id =
1104: (select bcol1.ato_line_id

Line 1105: from bom_cto_order_lines bcol1

1101: where bcolu.config_item_id = bac.config_item_id)
1102: -- select first ato_line_id in bcol
1103: and bcol.ato_line_id =
1104: (select bcol1.ato_line_id
1105: from bom_cto_order_lines bcol1
1106: where bcol1.config_item_id = bac.config_item_id
1107: -- pick up only if config is at top level
1108: and bcol1.line_id = bcol1.ato_line_id
1109: and rownum = 1)

Line 1129: from bom_cto_order_lines_upg

1125: WriteToLog('Item being populated from bac::'|| to_char(v_bac.config_id), 4);
1126: BEGIN
1127: select 'Y'
1128: into l_exists
1129: from bom_cto_order_lines_upg
1130: where config_item_id = v_bac.config_id
1131: and rownum = 1;
1132: WriteToLog('Item::'|| to_char(v_bac.config_id)||' already exists in bcolu', 4);
1133:

Line 1167: insert into bom_cto_order_lines_upg

1163: -- TEST THIS!!
1164: --
1165: WriteToLog('sql 5', 3);
1166: l_stmt_num := 110;
1167: insert into bom_cto_order_lines_upg
1168: (
1169: ATO_LINE_ID
1170: , BATCH_ID
1171: , BOM_ITEM_TYPE

Line 1240: from bom_cto_order_lines bcol

1236: , 'N' --REUSE_CONFIG
1237: , bcol.QTY_PER_PARENT_MODEL
1238: , 'UPG' --STATUS
1239: , nvl(msi.CONFIG_ORGS, '1')
1240: from bom_cto_order_lines bcol
1241: , oe_order_lines_all oel
1242: , mtl_system_items msi
1243: -- select all configs on open order lines
1244: where bcol.ato_line_id = oel.ato_line_id

Line 1260: insert into bom_cto_order_lines_upg

1256: -- mark as UPG
1257: --
1258: WriteToLog('sql 6', 3);
1259: l_stmt_num := 120;
1260: insert into bom_cto_order_lines_upg
1261: (
1262: ATO_LINE_ID
1263: , BATCH_ID
1264: , BOM_ITEM_TYPE

Line 1333: from bom_cto_order_lines bcol1

1329: , 'N' --REUSE_CONFIG
1330: , bcol2.QTY_PER_PARENT_MODEL
1331: , 'UPG' --STATUS
1332: , nvl(msi.CONFIG_ORGS, '1')
1333: from bom_cto_order_lines bcol1
1334: , bom_cto_order_lines bcol2
1335: , bom_ato_configurations bac
1336: , oe_order_lines_all oel
1337: , mtl_system_items msi

Line 1334: , bom_cto_order_lines bcol2

1330: , bcol2.QTY_PER_PARENT_MODEL
1331: , 'UPG' --STATUS
1332: , nvl(msi.CONFIG_ORGS, '1')
1333: from bom_cto_order_lines bcol1
1334: , bom_cto_order_lines bcol2
1335: , bom_ato_configurations bac
1336: , oe_order_lines_all oel
1337: , mtl_system_items msi
1338: -- base model has item attr = 3

Line 1362: insert into bom_cto_order_lines_upg

1358: -- mark as UPG
1359: --
1360: WriteToLog('sql 7', 3);
1361: l_stmt_num := 130;
1362: insert into bom_cto_order_lines_upg
1363: (
1364: ATO_LINE_ID
1365: , BATCH_ID
1366: , BOM_ITEM_TYPE

Line 1436: , bom_cto_order_lines bcol

1432: , bcol.QTY_PER_PARENT_MODEL
1433: , 'UPG' --STATUS
1434: , nvl(msi.CONFIG_ORGS, '1')
1435: from bom_ato_configurations bac
1436: , bom_cto_order_lines bcol
1437: , mtl_system_items msi
1438: -- base model has item attr = 3
1439: where bac.base_model_id = msi.inventory_item_id
1440: and bac.organization_id = msi.organization_id

Line 1454: from bom_cto_order_lines_upg bcolu

1450: and nvl(msi2.config_orgs, '1') = '3')
1451: -- and not already in bcol_upg
1452: and NOT EXISTS
1453: (select 'exists'
1454: from bom_cto_order_lines_upg bcolu
1455: where bcolu.config_item_id = bac.config_item_id)
1456: -- select first ato_line_id in bcol
1457: and bcol.ato_line_id =
1458: (select bcol1.ato_line_id

Line 1459: from bom_cto_order_lines bcol1

1455: where bcolu.config_item_id = bac.config_item_id)
1456: -- select first ato_line_id in bcol
1457: and bcol.ato_line_id =
1458: (select bcol1.ato_line_id
1459: from bom_cto_order_lines bcol1
1460: where bcol1.config_item_id = bac.config_item_id
1461: -- pick up only if config is at top level
1462: and bcol1.line_id = bcol1.ato_line_id
1463: and rownum = 1)

Line 1481: from bom_cto_order_lines_upg bcolu

1477: WriteToLog('Item being populated from bac::'|| to_char(v_bac_top.config_id), 4);
1478: BEGIN
1479: select 'exists'
1480: into l_exists
1481: from bom_cto_order_lines_upg bcolu
1482: where bcolu.config_item_id = v_bac_top.config_id
1483: and rownum = 1;
1484: WriteToLog('Item::'|| to_char(v_bac_top.config_id)||' already exists in bcolu', 4);
1485: EXCEPTION

Line 1560: from bom_cto_order_lines_upg bcolu

1556: mtl_system_items msi,
1557: bom_ato_configurations bac
1558: where NOT EXISTS
1559: (select 'exists'
1560: from bom_cto_order_lines_upg bcolu
1561: where bcolu.config_item_id = bac.config_item_id)
1562: and bac.base_model_id = msi.inventory_item_id
1563: and bac.organization_id = msi.organization_id
1564: and nvl(msi.config_orgs, '1') = '3'

Line 1602: from bom_cto_order_lines_upg bcolu

1598: and nvl(msi2.config_orgs, '1') = '3')
1599: -- and not already in bcol_upg
1600: and NOT EXISTS
1601: (select 'exists'
1602: from bom_cto_order_lines_upg bcolu
1603: where bcolu.config_item_id = bac.config_item_id);
1604:
1605:
1606: l_match NUMBER;

Line 1645: insert into bom_cto_order_lines_upg

1641: -- mark as UPG
1642: --
1643: WriteToLog('sql 1', 3);
1644: l_stmt_num := 60;
1645: insert into bom_cto_order_lines_upg
1646: (
1647: ATO_LINE_ID
1648: , BATCH_ID
1649: , BOM_ITEM_TYPE

Line 1720: from bom_cto_order_lines bcol1

1716: , bcol2.QTY_PER_PARENT_MODEL
1717: , 'UPG' --STATUS
1718: --, bcol2.SEQUENCE
1719: , nvl(msi.config_orgs, '1')
1720: from bom_cto_order_lines bcol1
1721: , bom_cto_order_lines bcol2
1722: , oe_order_lines_all oel
1723: , mtl_system_items msi
1724: , mtl_item_categories mcat

Line 1721: , bom_cto_order_lines bcol2

1717: , 'UPG' --STATUS
1718: --, bcol2.SEQUENCE
1719: , nvl(msi.config_orgs, '1')
1720: from bom_cto_order_lines bcol1
1721: , bom_cto_order_lines bcol2
1722: , oe_order_lines_all oel
1723: , mtl_system_items msi
1724: , mtl_item_categories mcat
1725: -- select entire configuration

Line 1751: insert into bom_cto_order_lines_upg

1747: -- mark as UPG
1748: --
1749: WriteToLog('sql 2', 3);
1750: l_stmt_num := 70;
1751: insert into bom_cto_order_lines_upg
1752: (
1753: ATO_LINE_ID
1754: , BATCH_ID
1755: , BOM_ITEM_TYPE

Line 1826: from bom_cto_order_lines bcol1

1822: , bcol2.QTY_PER_PARENT_MODEL
1823: , 'UPG' --STATUS
1824: --, bcol2.SEQUENCE
1825: , nvl(msi.config_orgs, '1')
1826: from bom_cto_order_lines bcol1
1827: , bom_cto_order_lines bcol2
1828: , bom_ato_configurations bac
1829: , oe_order_lines_all oel
1830: , mtl_system_items msi

Line 1827: , bom_cto_order_lines bcol2

1823: , 'UPG' --STATUS
1824: --, bcol2.SEQUENCE
1825: , nvl(msi.config_orgs, '1')
1826: from bom_cto_order_lines bcol1
1827: , bom_cto_order_lines bcol2
1828: , bom_ato_configurations bac
1829: , oe_order_lines_all oel
1830: , mtl_system_items msi
1831: , mtl_item_categories mcat

Line 1859: insert into bom_cto_order_lines_upg

1855: -- mark as UPG
1856: --
1857: WriteToLog('sql 3', 3);
1858: l_stmt_num := 80;
1859: insert into bom_cto_order_lines_upg
1860: (
1861: ATO_LINE_ID
1862: , BATCH_ID
1863: , BOM_ITEM_TYPE

Line 1937: , bom_cto_order_lines bcol

1933: , nvl(msi.config_orgs, '1')
1934: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
1935: , mtl_system_items msi
1936: , bom_ato_configurations bac
1937: , bom_cto_order_lines bcol
1938: -- base model has item attr = 3
1939: where bac.base_model_id = msi.inventory_item_id
1940: and bac.organization_id = msi.organization_id
1941: and nvl(msi.config_orgs, '1') = '3'

Line 1949: from bom_cto_order_lines_upg bcolu

1945: and mcat.category_id = p_cat_id
1946: -- and not already in bcol_upg
1947: and NOT EXISTS
1948: (select 'exists'
1949: from bom_cto_order_lines_upg bcolu
1950: where bcolu.config_item_id = bac.config_item_id)
1951: -- select first ato_line_id in bcol
1952: and bcol.ato_line_id =
1953: (select bcol1.ato_line_id

Line 1954: from bom_cto_order_lines bcol1

1950: where bcolu.config_item_id = bac.config_item_id)
1951: -- select first ato_line_id in bcol
1952: and bcol.ato_line_id =
1953: (select bcol1.ato_line_id
1954: from bom_cto_order_lines bcol1
1955: where bcol1.config_item_id = bac.config_item_id
1956: -- pick up only if config is at top level
1957: and bcol1.line_id = bcol1.ato_line_id
1958: and rownum = 1)

Line 1978: from bom_cto_order_lines_upg

1974: WriteToLog('Item being populated from bac::'|| to_char(v_bac.config_id), 4);
1975: BEGIN
1976: select 'Y'
1977: into l_exists
1978: from bom_cto_order_lines_upg
1979: where config_item_id = v_bac.config_id
1980: and rownum = 1;
1981: WriteToLog('Item::'|| to_char(v_bac.config_id)||' already exists in bcolu', 4);
1982:

Line 2014: insert into bom_cto_order_lines_upg

2010: -- mark as UPG
2011: --
2012: WriteToLog('sql 5', 3);
2013: l_stmt_num := 100;
2014: insert into bom_cto_order_lines_upg
2015: (
2016: ATO_LINE_ID
2017: , BATCH_ID
2018: , BOM_ITEM_TYPE

Line 2089: from bom_cto_order_lines bcol

2085: , bcol.QTY_PER_PARENT_MODEL
2086: , 'UPG' --STATUS
2087: --, bcol.SEQUENCE
2088: , nvl(msi2.CONFIG_ORGS, '1')
2089: from bom_cto_order_lines bcol
2090: , oe_order_lines_all oel
2091: , mtl_system_items msi2
2092: -- select all configs on open order lines
2093: where bcol.ato_line_id = oel.ato_line_id

Line 2101: , bom_cto_order_lines bcol2

2097: and oel.ato_line_id in -- bug 6617686 connect using oel rather than bcol to get better filtering
2098: (select /*+ leading(MCAT) */ distinct bcol2.ato_line_id --Bugfix 6617686 Added a hint
2099: from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
2100: , mtl_system_items msi
2101: , bom_cto_order_lines bcol2
2102: where bcol2.config_item_id is not null
2103: and bcol2.inventory_item_id = msi.inventory_item_id
2104: and bcol2.ship_from_org_id = msi.organization_id
2105: -- and base model is in CTO category

Line 2122: insert into bom_cto_order_lines_upg

2118: -- TEST THIS!!
2119: --
2120: WriteToLog('sql 6', 3);
2121: l_stmt_num := 110;
2122: insert into bom_cto_order_lines_upg
2123: (
2124: ATO_LINE_ID
2125: , BATCH_ID
2126: , BOM_ITEM_TYPE

Line 2197: from bom_cto_order_lines bcol1

2193: , bcol2.QTY_PER_PARENT_MODEL
2194: , 'UPG' --STATUS
2195: --, bcol2.SEQUENCE
2196: , nvl(msi.CONFIG_ORGS, '1')
2197: from bom_cto_order_lines bcol1
2198: , bom_cto_order_lines bcol2
2199: , bom_ato_configurations bac
2200: , oe_order_lines_all oel
2201: , mtl_system_items msi

Line 2198: , bom_cto_order_lines bcol2

2194: , 'UPG' --STATUS
2195: --, bcol2.SEQUENCE
2196: , nvl(msi.CONFIG_ORGS, '1')
2197: from bom_cto_order_lines bcol1
2198: , bom_cto_order_lines bcol2
2199: , bom_ato_configurations bac
2200: , oe_order_lines_all oel
2201: , mtl_system_items msi
2202: , mtl_item_categories mcat

Line 2230: insert into bom_cto_order_lines_upg

2226: -- mark as UPG
2227: --
2228: WriteToLog('sql 7', 3);
2229: l_stmt_num := 120;
2230: insert into bom_cto_order_lines_upg
2231: (
2232: ATO_LINE_ID
2233: , BATCH_ID
2234: , BOM_ITEM_TYPE

Line 2306: , bom_cto_order_lines bcol

2302: , 'UPG' --STATUS
2303: --, bcol.SEQUENCE
2304: , nvl(msi.CONFIG_ORGS, '1')
2305: from bom_ato_configurations bac
2306: , bom_cto_order_lines bcol
2307: , mtl_system_items msi
2308: , mtl_item_categories mcat
2309: -- base model has item attr = 3
2310: where bac.base_model_id = msi.inventory_item_id

Line 2329: from bom_cto_order_lines_upg bcolu

2325: and nvl(msi2.config_orgs, '1') = '3')
2326: -- and not already in bcol_upg
2327: and NOT EXISTS
2328: (select 'exists'
2329: from bom_cto_order_lines_upg bcolu
2330: where bcolu.config_item_id = bac.config_item_id)
2331: -- select first ato_line_id in bcol
2332: and bcol.ato_line_id =
2333: (select bcol1.ato_line_id

Line 2334: from bom_cto_order_lines bcol1

2330: where bcolu.config_item_id = bac.config_item_id)
2331: -- select first ato_line_id in bcol
2332: and bcol.ato_line_id =
2333: (select bcol1.ato_line_id
2334: from bom_cto_order_lines bcol1
2335: where bcol1.config_item_id = bac.config_item_id
2336: -- pick up only if config is at top level
2337: and bcol1.line_id = bcol1.ato_line_id
2338: and rownum = 1)

Line 2356: from bom_cto_order_lines_upg bcolu

2352: WriteToLog('Item being populated from bac::'|| to_char(v_bac_top.config_id), 4);
2353: BEGIN
2354: select 'exists'
2355: into l_exists
2356: from bom_cto_order_lines_upg bcolu
2357: where bcolu.config_item_id = v_bac_top.config_id
2358: and rownum = 1;
2359: WriteToLog('Item::'|| to_char(v_bac_top.config_id)||' already exists in bcolu', 4);
2360: EXCEPTION

Line 2478: insert into bom_cto_order_lines_upg

2474: -- mark as UPG
2475: --
2476: WriteToLog('sql 1', 3);
2477: l_stmt_num := 50;
2478: insert into bom_cto_order_lines_upg
2479: (
2480: ATO_LINE_ID
2481: , BATCH_ID
2482: , BOM_ITEM_TYPE

Line 2552: from bom_cto_order_lines bcol1

2548: , bcol2.QTY_PER_PARENT_MODEL
2549: , 'UPG' --STATUS
2550: --, bcol2.SEQUENCE
2551: , nvl(msi.config_orgs, '1')
2552: from bom_cto_order_lines bcol1
2553: , bom_cto_order_lines bcol2
2554: , oe_order_lines_all oel
2555: , mtl_system_items msi
2556: , oe_order_lines_all oel2 --bugfix 3371155

Line 2553: , bom_cto_order_lines bcol2

2549: , 'UPG' --STATUS
2550: --, bcol2.SEQUENCE
2551: , nvl(msi.config_orgs, '1')
2552: from bom_cto_order_lines bcol1
2553: , bom_cto_order_lines bcol2
2554: , oe_order_lines_all oel
2555: , mtl_system_items msi
2556: , oe_order_lines_all oel2 --bugfix 3371155
2557: -- select entire configuration

Line 2585: insert into bom_cto_order_lines_upg

2581: --
2582: IF (l_attribute = 3) THEN
2583: WriteToLog('sql 2', 3);
2584: l_stmt_num := 60;
2585: insert into bom_cto_order_lines_upg
2586: (
2587: ATO_LINE_ID
2588: , BATCH_ID
2589: , BOM_ITEM_TYPE

Line 2660: from bom_cto_order_lines bcol1

2656: , bcol2.QTY_PER_PARENT_MODEL
2657: , 'UPG' --STATUS
2658: --, bcol2.SEQUENCE
2659: , nvl(msi.config_orgs, '1')
2660: from bom_cto_order_lines bcol1
2661: , bom_cto_order_lines bcol2
2662: , bom_ato_configurations bac
2663: , oe_order_lines_all oel
2664: , mtl_system_items msi

Line 2661: , bom_cto_order_lines bcol2

2657: , 'UPG' --STATUS
2658: --, bcol2.SEQUENCE
2659: , nvl(msi.config_orgs, '1')
2660: from bom_cto_order_lines bcol1
2661: , bom_cto_order_lines bcol2
2662: , bom_ato_configurations bac
2663: , oe_order_lines_all oel
2664: , mtl_system_items msi
2665: where bac.config_item_id = p_config_id

Line 2690: insert into bom_cto_order_lines_upg

2686: --
2687: IF (l_attribute = 3) THEN
2688: WriteToLog('sql 3', 3);
2689: l_stmt_num := 70;
2690: insert into bom_cto_order_lines_upg
2691: (
2692: ATO_LINE_ID
2693: , BATCH_ID
2694: , BOM_ITEM_TYPE

Line 2764: , bom_cto_order_lines bcol

2760: , bcol.QTY_PER_PARENT_MODEL
2761: , 'UPG' --STATUS
2762: , nvl(msi.config_orgs, '1')
2763: from bom_ato_configurations bac
2764: , bom_cto_order_lines bcol
2765: , mtl_system_items msi
2766: where bac.config_item_id = p_config_id
2767: and NOT EXISTS
2768: (select 'exists'

Line 2769: from bom_cto_order_lines_upg bcolu

2765: , mtl_system_items msi
2766: where bac.config_item_id = p_config_id
2767: and NOT EXISTS
2768: (select 'exists'
2769: from bom_cto_order_lines_upg bcolu
2770: where bcolu.config_item_id = bac.config_item_id)
2771: -- select first ato_line_id in bcol
2772: and bcol.ato_line_id =
2773: (select bcol1.ato_line_id

Line 2774: from bom_cto_order_lines bcol1

2770: where bcolu.config_item_id = bac.config_item_id)
2771: -- select first ato_line_id in bcol
2772: and bcol.ato_line_id =
2773: (select bcol1.ato_line_id
2774: from bom_cto_order_lines bcol1
2775: where bcol1.config_item_id = bac.config_item_id
2776: -- pick up only if config is at top level
2777: and bcol1.line_id = bcol1.ato_line_id
2778: and rownum = 1)

Line 2805: from bom_cto_order_lines_upg

2801: l_stmt_num := 80;
2802: BEGIN
2803: select 'Y'
2804: into l_exists
2805: from bom_cto_order_lines_upg
2806: where config_item_id = p_config_id
2807: and rownum = 1;
2808: WriteToLog('Item::'|| to_char(p_config_id)||' already exists in bcolu', 4);
2809:

Line 2871: bom_cto_order_lines_s1.nextval, -- line_id

2867: t_bcol BCOL_TAB;
2868:
2869: CURSOR c_bac_data IS
2870: select
2871: bom_cto_order_lines_s1.nextval, -- line_id
2872: substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
2873: bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
2874: bac.component_code, -- component_code
2875: msi.bom_item_type, -- bom_item_type

Line 3001: select bom_cto_order_lines_s1.nextval

2997: END IF;
2998: END LOOP;
2999:
3000: l_stmt_num := 50;
3001: select bom_cto_order_lines_s1.nextval
3002: into l_header_id
3003: from dual;
3004:
3005: -- populate top_model_line_id, ato_line_id and header_id

Line 3071: INSERT INTO bom_cto_order_lines_upg(

3067: i := t_bcol.first;
3068:
3069: WHILE i IS NOT NULL LOOP
3070: l_stmt_num := 130;
3071: INSERT INTO bom_cto_order_lines_upg(
3072: HEADER_ID ,
3073: LINE_ID ,
3074: LINK_TO_LINE_ID ,
3075: ATO_LINE_ID ,

Line 3181: bom_cto_order_lines_s1.nextval, -- line_id

3177:
3178:
3179: CURSOR c_bac_child_data(l_curr_config_id NUMBER) IS
3180: select
3181: bom_cto_order_lines_s1.nextval, -- line_id
3182: substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
3183: bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
3184: substr(bac.component_code, (instr(bac.component_code, '-', 1)+1)), -- component_code
3185: --bac.config_item_id, -- config_item_id

Line 3511: p_bcol_line_id in bom_cto_order_lines.line_id%type,

3507:
3508:
3509: PROCEDURE populate_parent_ato(
3510: p_t_bcol in out NOCOPY bcol_tab,
3511: p_bcol_line_id in bom_cto_order_lines.line_id%type,
3512: x_return_status OUT NOCOPY varchar2)
3513: IS
3514:
3515: TYPE TABNUM IS TABLE of NUMBER index by binary_integer;

Line 3623: from bom_cto_order_lines_upg bcolu,

3619: --
3620: CURSOR c_success IS
3621: select distinct substrb(concatenated_segments,1,50) name,msi.inventory_item_id
3622: item_id
3623: from bom_cto_order_lines_upg bcolu,
3624: mtl_system_items_kfv msi
3625: where bcolu.config_item_id is not null
3626: and bcolu.config_item_id = msi.inventory_item_id
3627: and bcolu.ship_from_org_id = msi.organization_id

Line 3630: from bom_cto_order_lines_upg bcolu1

3626: and bcolu.config_item_id = msi.inventory_item_id
3627: and bcolu.ship_from_org_id = msi.organization_id
3628: and ((bcolu.config_creation = '3'
3629: and exists (select 'exists'
3630: from bom_cto_order_lines_upg bcolu1
3631: where bcolu1.config_item_id = bcolu.config_item_id
3632: and bcolu1.status = 'MRP_SRC'
3633: and rownum = 1))
3634: or (bcolu.config_creation <> '3'

Line 3636: from bom_cto_order_lines_upg bcolu1

3632: and bcolu1.status = 'MRP_SRC'
3633: and rownum = 1))
3634: or (bcolu.config_creation <> '3'
3635: and not exists (select 'exists'
3636: from bom_cto_order_lines_upg bcolu1
3637: where bcolu1.config_item_id = bcolu.config_item_id
3638: and bcolu1.status <> 'MRP_SRC')))
3639: order by 1; -- Modified by Renga for bug 3930047
3640:

Line 3648: from bom_cto_order_lines_upg bcolu,

3644: --
3645: CURSOR c_error IS
3646: select distinct substrb(concatenated_segments,1,50) name,
3647: msi.inventory_item_id item_id
3648: from bom_cto_order_lines_upg bcolu,
3649: mtl_system_items_kfv msi
3650: where bcolu.config_item_id is not null
3651: and bcolu.config_item_id = msi.inventory_item_id
3652: and bcolu.ship_from_org_id = msi.organization_id

Line 3654: from bom_cto_order_lines_upg bcolu1

3650: where bcolu.config_item_id is not null
3651: and bcolu.config_item_id = msi.inventory_item_id
3652: and bcolu.ship_from_org_id = msi.organization_id
3653: and not exists (select 'exists'
3654: from bom_cto_order_lines_upg bcolu1
3655: where bcolu1.config_item_id = bcolu.config_item_id
3656: and bcolu1.status = 'MRP_SRC')
3657: order by 1; -- Modified by Renga for bug 3930047
3658:

Line 3669: from bom_cto_order_lines_upg bcolu,

3665: select distinct substrb(concatenated_segments,1,50) name,
3666: msi.inventory_item_id item_id,
3667: oeh.order_number,
3668: decode(bcolu.status, 'MRP_SRC', 'was successfully processed', 'errored out') status
3669: from bom_cto_order_lines_upg bcolu,
3670: mtl_system_items_kfv msi,
3671: oe_order_lines_all oel,
3672: oe_order_headers_all oeh
3673: where bcolu.config_item_id is not null

Line 3678: from bom_cto_order_lines_upg bcolu1

3674: and bcolu.config_item_id = msi.inventory_item_id
3675: and bcolu.ship_from_org_id = msi.organization_id
3676: and config_creation <> '3'
3677: and exists (select 'exists'
3678: from bom_cto_order_lines_upg bcolu1
3679: where bcolu1.config_item_id = bcolu.config_item_id
3680: and bcolu1.status = 'MRP_SRC')
3681: and exists (select 'exists'
3682: from bom_cto_order_lines_upg bcolu1

Line 3682: from bom_cto_order_lines_upg bcolu1

3678: from bom_cto_order_lines_upg bcolu1
3679: where bcolu1.config_item_id = bcolu.config_item_id
3680: and bcolu1.status = 'MRP_SRC')
3681: and exists (select 'exists'
3682: from bom_cto_order_lines_upg bcolu1
3683: where bcolu1.config_item_id = bcolu.config_item_id
3684: and bcolu1.status <> 'MRP_SRC')
3685: and oel.line_id = bcolu.ato_line_id
3686: and oel.header_id = oeh.header_id

Line 3761: FROM bom_cto_order_lines_upg

3757: parent_ato_line_id,
3758: ato_line_id,
3759: line_id,
3760: ship_from_org_id
3761: FROM bom_cto_order_lines_upg
3762: WHERE ato_line_id in ( SELECT DISTINCT bupg1.ato_line_id
3763: FROM bom_cto_order_lines_upg bupg1
3764: WHERE bupg1.config_creation = 3);
3765:

Line 3763: FROM bom_cto_order_lines_upg bupg1

3759: line_id,
3760: ship_from_org_id
3761: FROM bom_cto_order_lines_upg
3762: WHERE ato_line_id in ( SELECT DISTINCT bupg1.ato_line_id
3763: FROM bom_cto_order_lines_upg bupg1
3764: WHERE bupg1.config_creation = 3);
3765:
3766: TYPE r_bcolupg IS RECORD
3767: (

Line 3877: UPDATE bom_cto_order_lines_upg

3873: oe_debug_pub.add('There are ' || t_ato_line_id.count ||'top ato models with invalid configurations');
3874:
3875: l_stmt_num := 80;
3876: FORALL j IN t_ato_line_id.first..t_ato_line_id.last
3877: UPDATE bom_cto_order_lines_upg
3878: SET status = 'ERROR'
3879: WHERE ato_line_id = t_ato_line_id(j);
3880:
3881: l_stmt_num:= 90;