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_OSS_SOURCE_PK dependencies on BOM_CTO_ORDER_LINES

Line 294: l_perform_match bom_cto_order_lines.perform_match%type;

290: x_msg_data OUT NOCOPY Varchar) is
291:
292:
293:
294: l_perform_match bom_cto_order_lines.perform_match%type;
295: l_reuse_config bom_cto_order_lines.reuse_config%type;
296: l_oss_defined Varchar2(1);
297: i Number;
298: x_exp_error_code Number;

Line 295: l_reuse_config bom_cto_order_lines.reuse_config%type;

291:
292:
293:
294: l_perform_match bom_cto_order_lines.perform_match%type;
295: l_reuse_config bom_cto_order_lines.reuse_config%type;
296: l_oss_defined Varchar2(1);
297: i Number;
298: x_exp_error_code Number;
299: l_stmt_num Number;

Line 316: l_program_id bom_cto_order_lines.program_id%type;

312: -- Added by Renga Kannan on 11/30/04 for bug 3896824
313: -- Added the following variable declaration
314:
315: l_ship_from_org_id oe_order_lines.ship_from_org_id%type;
316: l_program_id bom_cto_order_lines.program_id%type;
317: l_valid_preconfig_org varchar2(1);
318:
319: -- End of change for bug 3896824 on 11/30/04
320:

Line 415: /* Changed the bom_cto_order_lines reference to bom_cto_order_lines_gt */

411: End if;
412:
413: l_stmt_num := 40;
414:
415: /* Changed the bom_cto_order_lines reference to bom_cto_order_lines_gt */
416:
417: -- Modified by Renga Kannan on 11/30/04 for bug 3896824
418: -- fetched two other columns ship_from_org_id and program_id into the variables
419: -- l_ship_from_org_id and l_program_id

Line 432: from bom_cto_order_lines_gt bcol

428: l_reuse_config,
429: l_config_creation,
430: l_ship_from_org_id,
431: l_program_id
432: from bom_cto_order_lines_gt bcol
433: where line_id = p_ato_line_id;
434:
435: -- End of change for bug 3896824 on 11/30/04
436:

Line 448: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

444:
445: /* Fixed the following sql to get the from orbitrary org istead of
446: going to specific ship from org */
447:
448: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
449: option_specific_sourced
450: into l_option_specific
451: from mtl_system_items msi,
452: bom_cto_order_lines_gt bcol

Line 452: bom_cto_order_lines_gt bcol

448: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
449: option_specific_sourced
450: into l_option_specific
451: from mtl_system_items msi,
452: bom_cto_order_lines_gt bcol
453: where msi.inventory_item_id = bcol.config_item_id
454: and line_id = p_ato_line_id
455: and rownum =1; /* Bugfix 3472654 */
456:

Line 458: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

454: and line_id = p_ato_line_id
455: and rownum =1; /* Bugfix 3472654 */
456:
457: if l_option_specific is not null then
458: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
459: 'Y'
460: into l_valid_ship_from_org
461: from bom_cto_order_lines_gt bcol,
462: mtl_system_items msi

Line 461: from bom_cto_order_lines_gt bcol,

457: if l_option_specific is not null then
458: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
459: 'Y'
460: into l_valid_ship_from_org
461: from bom_cto_order_lines_gt bcol,
462: mtl_system_items msi
463: where line_id = p_ato_line_id
464: and msi.inventory_item_id = bcol.config_item_id
465: and msi.organization_id = bcol.ship_from_org_id

Line 505: from bom_cto_order_lines_gt bcol,

501: l_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID Then
502: Begin
503: Select 'x'
504: into l_valid_preconfig_org
505: from bom_cto_order_lines_gt bcol,
506: mtl_system_items msi
507: where line_id = p_ato_line_id
508: and msi.inventory_item_id = bcol.config_item_id
509: and msi.organization_id = bcol.ship_from_org_id

Line 570: update bom_cto_order_lines_gt bcolgt

566: the matched config with its oss value from mtl_system_items to bcol so that
567: we won't have this issue.
568:
569: */
570: update bom_cto_order_lines_gt bcolgt
571: set option_specific = (select option_specific_sourced
572: from mtl_system_items
573: where inventory_item_id = bcolgt.config_item_id
574: and rownum = 1)

Line 683: update bom_cto_order_lines bcol

679:
680: /* Renga: Add Validation to ship from org check */
681:
682: If p_mode = 'ACC' then
683: update bom_cto_order_lines bcol
684: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
685: decode(option_specific,'4','3',option_specific)
686: from bom_cto_order_lines_gt bcol_gt
687: where bcol_gt.line_id = bcol.line_id)

Line 684: set option_specific = (select /+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) /

680: /* Renga: Add Validation to ship from org check */
681:
682: If p_mode = 'ACC' then
683: update bom_cto_order_lines bcol
684: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
685: decode(option_specific,'4','3',option_specific)
686: from bom_cto_order_lines_gt bcol_gt
687: where bcol_gt.line_id = bcol.line_id)
688: where bcol.ato_line_id = p_ato_line_id;

Line 686: from bom_cto_order_lines_gt bcol_gt

682: If p_mode = 'ACC' then
683: update bom_cto_order_lines bcol
684: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
685: decode(option_specific,'4','3',option_specific)
686: from bom_cto_order_lines_gt bcol_gt
687: where bcol_gt.line_id = bcol.line_id)
688: where bcol.ato_line_id = p_ato_line_id;
689: elsif p_mode = 'UPG' then
690: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) */ bom_cto_order_lines_upg bcol

Line 690: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) / bom_cto_order_lines_upg bcol

686: from bom_cto_order_lines_gt bcol_gt
687: where bcol_gt.line_id = bcol.line_id)
688: where bcol.ato_line_id = p_ato_line_id;
689: elsif p_mode = 'UPG' then
690: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) */ bom_cto_order_lines_upg bcol
691: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
692: decode(option_specific,'4','3',option_specific)
693: from bom_cto_order_lines_gt bcol_gt
694: where bcol_gt.line_id = bcol.line_id)

Line 691: set option_specific = (select /+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) /

687: where bcol_gt.line_id = bcol.line_id)
688: where bcol.ato_line_id = p_ato_line_id;
689: elsif p_mode = 'UPG' then
690: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) */ bom_cto_order_lines_upg bcol
691: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
692: decode(option_specific,'4','3',option_specific)
693: from bom_cto_order_lines_gt bcol_gt
694: where bcol_gt.line_id = bcol.line_id)
695: where bcol.ato_line_id = p_ato_line_id;

Line 693: from bom_cto_order_lines_gt bcol_gt

689: elsif p_mode = 'UPG' then
690: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) */ bom_cto_order_lines_upg bcol
691: set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
692: decode(option_specific,'4','3',option_specific)
693: from bom_cto_order_lines_gt bcol_gt
694: where bcol_gt.line_id = bcol.line_id)
695: where bcol.ato_line_id = p_ato_line_id;
696: end if;
697:

Line 700: into /+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1) /

696: end if;
697:
698: Begin
699: select 'Y'
700: into /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1) */
701: l_valid_ship_from_org
702: from bom_cto_order_lines_gt bcol
703: where line_id = p_ato_line_id
704: and (option_specific is null

Line 702: from bom_cto_order_lines_gt bcol

698: Begin
699: select 'Y'
700: into /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1) */
701: l_valid_ship_from_org
702: from bom_cto_order_lines_gt bcol
703: where line_id = p_ato_line_id
704: and (option_specific is null
705: or ship_from_org_id in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
706: rcv_org_id

Line 897: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) /

893:
894:
895: l_stmt_num := 10;
896:
897: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
898: count(*)
899: into l_comp_count
900: from bom_cto_oss_components ossc,
901: bom_cto_order_lines_gt bcol

Line 901: bom_cto_order_lines_gt bcol

897: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
898: count(*)
899: into l_comp_count
900: from bom_cto_oss_components ossc,
901: bom_cto_order_lines_gt bcol
902: where ossc.model_item_id = p_model_item_id
903: and ossc.option_item_id = bcol.inventory_item_id
904: and bcol.parent_ato_line_id = p_model_line_id
905: and exists (select 'x' from bom_cto_oss_orgs_list ossl

Line 931: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) /

927: organization_id, /* Organization Id */
928: ato_line_id /* Ato line id */
929: )
930:
931: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
932: p_model_line_id Line_id,
933: p_model_item_id inventory_item_id,
934: ossl.organization_id organization_id,
935: p_ato_line_id

Line 939: bom_cto_order_lines_gt bcol

935: p_ato_line_id
936:
937: from bom_cto_oss_components ossc,
938: bom_cto_oss_orgs_list ossl,
939: bom_cto_order_lines_gt bcol
940:
941: where
942: ossc.model_item_id = p_model_item_id
943: and ossc.option_item_id = bcol.inventory_item_id

Line 976: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) /

972: vendor_site_code,
973: ato_line_id )
974:
975:
976: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
977: p_model_line_id line_id,
978: p_model_item_id inventory_item_id,
979: ossl.vendor_id vendor_id,
980: decode(ossl.vendor_site_code,null,'-1',

Line 986: bom_cto_order_lines_gt bcol

982: p_ato_line_id ato_line_id
983:
984: from bom_cto_oss_components ossc,
985: bom_cto_oss_orgs_list ossl,
986: bom_cto_order_lines_gt bcol
987:
988: where
989: bcol.parent_ato_line_id = p_model_line_id
990: and ossc.model_item_id = p_model_item_id

Line 1322: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

1318: l_option_specific Varchar2(1);
1319:
1320: Begin
1321:
1322: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1323: option_specific
1324: into l_option_specific
1325: from bom_cto_order_lines_gt
1326: where line_id = p_model_line_id;

Line 1325: from bom_cto_order_lines_gt

1321:
1322: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1323: option_specific
1324: into l_option_specific
1325: from bom_cto_order_lines_gt
1326: where line_id = p_model_line_id;
1327:
1328: If l_option_specific = '2' then
1329: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 1362: select /+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_N3) /

1358: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Inside PRUNE_PARENT_OSS_CONFIG API',5);
1359: End if;
1360:
1361:
1362: select /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_N3) */
1363: count(*)
1364: into l_oss_child_count
1365: from bom_cto_order_lines_gt bcol
1366: where parent_ato_line_id = p_model_line_id

Line 1365: from bom_cto_order_lines_gt bcol

1361:
1362: select /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_N3) */
1363: count(*)
1364: into l_oss_child_count
1365: from bom_cto_order_lines_gt bcol
1366: where parent_ato_line_id = p_model_line_id
1367: and line_id <> p_model_line_id /* We should igonre the current row */
1368: and option_specific in ('1','2','3')
1369: and not exists(select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 1405: from (select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) /

1401: p_model_item_id,
1402: p_model_line_id,
1403: organization_id,
1404: p_ato_line_id
1405: from (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1406: oss_src.line_id line_id,
1407: oss_src.rcv_org_id organization_id
1408: from bom_cto_oss_source_gt oss_src,
1409: bom_cto_order_lines_gt bcol

Line 1409: bom_cto_order_lines_gt bcol

1405: from (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1406: oss_src.line_id line_id,
1407: oss_src.rcv_org_id organization_id
1408: from bom_cto_oss_source_gt oss_src,
1409: bom_cto_order_lines_gt bcol
1410: where bcol.parent_ato_line_id = p_model_line_id
1411: and bcol.parent_ato_line_id <> bcol.line_id
1412: and bcol.option_specific in ('1','2','3')
1413: and oss_src.line_id = bcol.line_id

Line 1423: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) /

1419: and bcol.option_specific = '3'
1420: and nvl(valid_flag,'N') = 'Y'
1421: and rcv_org_id is null)
1422: Union
1423: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1424: oss_src.line_id line_id,
1425: oss_src.source_org_id organization_id
1426: from bom_cto_oss_source_gt oss_src,
1427: bom_cto_order_lines_gt bcol

Line 1427: bom_cto_order_lines_gt bcol

1423: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1424: oss_src.line_id line_id,
1425: oss_src.source_org_id organization_id
1426: from bom_cto_oss_source_gt oss_src,
1427: bom_cto_order_lines_gt bcol
1428: where bcol.parent_ato_line_id = p_model_line_id
1429: and bcol.parent_ato_line_id <> bcol.line_id
1430: and bcol.option_specific in ('1','2','3')
1431: and oss_src.line_id = bcol.line_id

Line 1446: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

1442: group by organization_id
1443: having count(*) = l_oss_child_count;
1444: Else
1445: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: No oss child found...Updating in bcol',1);
1446: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1447: bom_cto_order_lines_gt bcol
1448: set option_specific = null
1449: where line_id = p_model_line_id;
1450: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Rows updated = '||sql%rowcount,1);

Line 1447: bom_cto_order_lines_gt bcol

1443: having count(*) = l_oss_child_count;
1444: Else
1445: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: No oss child found...Updating in bcol',1);
1446: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
1447: bom_cto_order_lines_gt bcol
1448: set option_specific = null
1449: where line_id = p_model_line_id;
1450: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Rows updated = '||sql%rowcount,1);
1451: return;

Line 1650: select /+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1/

1646: valid_flag,
1647: leaf_node
1648: )
1649:
1650: select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1*/
1651: p_model_item_id,
1652: p_model_line_id,
1653: oss_lis.organization_id,
1654: oss_lis.organization_id,

Line 1667: bom_cto_order_lines_gt bcol

1663: 'Y',
1664: 'Y'
1665: from bom_cto_oss_orgslist_gt oss_lis,
1666: mtl_system_items msi,
1667: bom_cto_order_lines_gt bcol
1668: where
1669: bcol.line_id = p_model_line_id
1670: and bcol.option_specific = '3'
1671: and oss_lis.organization_id not in (

Line 1783: from bom_cto_order_lines

1779: option_specific,
1780: config_creation,
1781: perform_match,
1782: reuse_config
1783: from bom_cto_order_lines
1784: where ato_line_id = p_Ato_line_id
1785: and option_specific in ('1','2','3')
1786: and p_mode = 'ACC'
1787: union

Line 1795: from bom_cto_order_lines_upg

1791: option_specific,
1792: config_creation,
1793: perform_match,
1794: reuse_config
1795: from bom_cto_order_lines_upg
1796: where ato_line_id = p_Ato_line_id
1797: and option_specific in ('1','2','3')
1798: and p_mode = 'UPG'
1799: and (p_changed_src = 'Y' or config_creation=3);

Line 2889: from bom_cto_order_lines_upg bcol

2885: If p_mode = 'UPG' then
2886:
2887: update mtl_system_items msi
2888: set msi.option_specific_sourced = (select bcol.option_specific
2889: from bom_cto_order_lines_upg bcol
2890: where bcol.ato_line_id= p_ato_line_id
2891: and bcol.config_item_id = msi.inventory_item_id
2892: )
2893: where msi.inventory_item_id in (select config_item_id

Line 2894: from bom_cto_order_lines_upg

2890: where bcol.ato_line_id= p_ato_line_id
2891: and bcol.config_item_id = msi.inventory_item_id
2892: )
2893: where msi.inventory_item_id in (select config_item_id
2894: from bom_cto_order_lines_upg
2895: where ato_line_id = p_ato_line_id
2896: and bom_item_type = 1
2897: and option_specific in ('1','2','3')
2898: );

Line 2902: from bom_cto_order_lines bcol

2898: );
2899: elsif p_mode = 'ACC' Then
2900: update mtl_system_items msi
2901: set msi.option_specific_sourced = (select bcol.option_specific
2902: from bom_cto_order_lines bcol
2903: where bcol.ato_line_id=p_ato_line_id
2904: and bcol.config_item_id =msi.inventory_item_id
2905: )
2906: where msi.inventory_item_id in (select config_item_id

Line 2907: from bom_cto_order_lines

2903: where bcol.ato_line_id=p_ato_line_id
2904: and bcol.config_item_id =msi.inventory_item_id
2905: )
2906: where msi.inventory_item_id in (select config_item_id
2907: from bom_cto_order_lines
2908: where ato_line_id = p_ato_line_id
2909: and bom_item_type = 1
2910: and option_specific in ('1','2','3')
2911: );

Line 3214: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

3210: End loop;
3211: Else /* X_rule_exists */
3212: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_ITEM_LEVEL_RULE: Updating bcol with option specific = 4',1);
3213:
3214: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3215: bom_cto_order_lines_gt bcol
3216: set option_specific = 4
3217: where line_id = p_model_line_id;
3218:

Line 3215: bom_cto_order_lines_gt bcol

3211: Else /* X_rule_exists */
3212: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_ITEM_LEVEL_RULE: Updating bcol with option specific = 4',1);
3213:
3214: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3215: bom_cto_order_lines_gt bcol
3216: set option_specific = 4
3217: where line_id = p_model_line_id;
3218:
3219: End if; /* x_rule_exists = 'Y' */

Line 3467: any input parameter. This looks at the data from bom_cto_order_lines_gt temp table

3463: */
3464:
3465: /*
3466: This procedure is called from match API during ATP. This will not have
3467: any input parameter. This looks at the data from bom_cto_order_lines_gt temp table
3468: and process all the OSS configurations to get the list of valid orgs and
3469: vendors.
3470: */
3471:

Line 3736: Select /+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N5) /

3732: x_msg_count OUT NOCOPY Number,
3733: x_msg_data OUT NOCOPY Varchar2) is
3734:
3735: Cursor Oss_top_models is
3736: Select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N5) */
3737: distinct bcol1.ato_line_id
3738: from bom_cto_order_lines_gt bcol1
3739: where exists (select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) */
3740: 'X'

Line 3738: from bom_cto_order_lines_gt bcol1

3734:
3735: Cursor Oss_top_models is
3736: Select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N5) */
3737: distinct bcol1.ato_line_id
3738: from bom_cto_order_lines_gt bcol1
3739: where exists (select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) */
3740: 'X'
3741: from bom_cto_oss_components ossc,
3742: bom_cto_order_lines_gt bcol2

Line 3739: where exists (select /+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) /

3735: Cursor Oss_top_models is
3736: Select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N5) */
3737: distinct bcol1.ato_line_id
3738: from bom_cto_order_lines_gt bcol1
3739: where exists (select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) */
3740: 'X'
3741: from bom_cto_oss_components ossc,
3742: bom_cto_order_lines_gt bcol2
3743: where bcol2.parent_ato_line_id = bcol1.line_id

Line 3742: bom_cto_order_lines_gt bcol2

3738: from bom_cto_order_lines_gt bcol1
3739: where exists (select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) */
3740: 'X'
3741: from bom_cto_oss_components ossc,
3742: bom_cto_order_lines_gt bcol2
3743: where bcol2.parent_ato_line_id = bcol1.line_id
3744: and ossc.model_item_id = bcol1.inventory_item_id
3745: and ossc.option_item_id = bcol2.inventory_item_id)
3746: and bcol1.bom_item_type = '1'

Line 3755: select /+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) /

3751: will bring all those lines for processing
3752: */
3753:
3754: Cursor oss_models(p_ato_line_id Number) is
3755: select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) */
3756: line_id,
3757: ato_line_id,
3758: option_specific,
3759: inventory_item_id,

Line 3763: from bom_cto_order_lines_gt

3759: inventory_item_id,
3760: config_item_id,
3761: perform_match,
3762: config_creation
3763: from bom_cto_order_lines_gt
3764: where ato_line_id = p_ato_line_id
3765: and option_specific in ('1','2','3')
3766: order by plan_level desc;
3767:

Line 3922: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

3918: organization_id,
3919: vendor_id,
3920: vendor_site_code,
3921: make_flag)
3922: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3923: oss_src.inventory_item_id,
3924: oss_src.line_id,
3925: bcol.ato_line_id,
3926: oss_src.rcv_org_id,

Line 3933: bom_cto_order_lines_gt bcol

3929: to_number(null), --3894241
3930: null,
3931: reuse_flag
3932: from bom_cto_oss_source_gt oss_src,
3933: bom_cto_order_lines_gt bcol
3934: where bcol.line_id = oss_src.line_id
3935: and oss_error_code is null
3936: and oss_src.valid_flag = 'Y'
3937: and oss_src.rcv_org_id is not null

Line 3940: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

3936: and oss_src.valid_flag = 'Y'
3937: and oss_src.rcv_org_id is not null
3938: and nvl(bcol.option_specific,'4') <> '4'
3939: union
3940: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3941: oss_src.inventory_item_id,
3942: oss_src.line_id,
3943: bcol.ato_line_id,
3944: oss_src.source_org_id,

Line 3951: bom_cto_order_lines_gt bcol

3947: to_number(null), --3894241
3948: null,
3949: null
3950: from bom_cto_oss_source_gt oss_src,
3951: bom_cto_order_lines_gt bcol
3952: where bcol.line_id = oss_src.line_id
3953: and bcol.option_specific is not null
3954: and oss_error_code is null
3955: and oss_src.valid_flag = 'Y'

Line 3965: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

3961: and valid_flag = 'Y'
3962: )
3963: and nvl(bcol.option_specific,'4') <> '4'
3964: union
3965: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
3966: oss_src.inventory_item_id,
3967: oss_src.line_id,
3968: bcol.ato_line_id,
3969: to_number(null),--3894241

Line 3974: bom_cto_order_lines_gt bcol

3970: oss_src.vendor_id,
3971: oss_src.vendor_site_code,
3972: null
3973: from bom_cto_oss_source_gt oss_src,
3974: bom_cto_order_lines_gt bcol
3975: where bcol.line_id = oss_src.line_id
3976: and bcol.option_specific is not null
3977: and oss_error_code is null
3978: and oss_src.valid_flag = 'Y'

Line 4087: bom_cto_order_lines_gt bcol,

4083:
4084: from mrp_sr_receipt_org rcv,
4085: mrp_sr_source_org src,
4086: mrp_sr_assignments assg,
4087: bom_cto_order_lines_gt bcol,
4088: mtl_system_items msi,
4089: ap_supplier_sites_all vend
4090:
4091: where

Line 4121: bom_cto_order_lines_gt bcol,

4117: to_number(null), --3894241
4118: null
4119:
4120: from mrp_sr_assignments assg,
4121: bom_cto_order_lines_gt bcol,
4122: mtl_system_items msi
4123:
4124: where
4125: bcol.ato_line_id = bcol.line_id

Line 4152: bom_cto_order_lines_gt bcol,

4148:
4149: from mrp_sr_receipt_org rcv,
4150: mrp_sr_source_org src,
4151: mrp_sr_assignments assg,
4152: bom_cto_order_lines_gt bcol,
4153: mtl_system_items msi,
4154: ap_supplier_sites_all vend
4155:
4156: where

Line 4189: bom_cto_order_lines_gt bcol,

4185: to_number(null),--bugfix3894241
4186: null
4187:
4188: from mrp_sr_assignments assg,
4189: bom_cto_order_lines_gt bcol,
4190: mtl_system_items msi
4191:
4192: where
4193: bcol.config_item_id is not null

Line 4273: /* The following cursor will get all the line from bom_cto_order_lines table.

4269: i Number:=0;
4270: l_stmt_num Number ;
4271:
4272:
4273: /* The following cursor will get all the line from bom_cto_order_lines table.
4274: This curosr is used for creating bcol cache.
4275: */
4276:
4277: Cursor bcol_cur is

Line 4278: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4274: This curosr is used for creating bcol cache.
4275: */
4276:
4277: Cursor bcol_cur is
4278: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4279: line_id,
4280: ato_line_id,
4281: parent_ato_line_id,
4282: option_specific,

Line 4284: from bom_cto_order_lines_gt bcol

4280: ato_line_id,
4281: parent_ato_line_id,
4282: option_specific,
4283: perform_match
4284: from bom_cto_order_lines_gt bcol
4285: where ato_line_id = p_ato_line_id;
4286:
4287:
4288: Cursor oss_line_cur is

Line 4289: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4285: where ato_line_id = p_ato_line_id;
4286:
4287:
4288: Cursor oss_line_cur is
4289: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4290: line_id,
4291: ato_line_id,
4292: parent_ato_line_id,
4293: option_specific

Line 4294: from bom_cto_order_lines_gt bcol

4290: line_id,
4291: ato_line_id,
4292: parent_ato_line_id,
4293: option_specific
4294: from bom_cto_order_lines_gt bcol
4295: where ato_line_id = p_ato_line_id
4296: and option_specific = '1'
4297: order by plan_level desc;
4298:

Line 4312: update /+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) /

4308: x_oss_exists :='Y';
4309:
4310: l_stmt_num := 10;
4311:
4312: update /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) */
4313: bom_cto_order_lines_gt bcol1
4314: set option_specific = '1'
4315: where
4316: ato_line_id = p_ato_line_id

Line 4313: bom_cto_order_lines_gt bcol1

4309:
4310: l_stmt_num := 10;
4311:
4312: update /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) */
4313: bom_cto_order_lines_gt bcol1
4314: set option_specific = '1'
4315: where
4316: ato_line_id = p_ato_line_id
4317: and exists (select /*+ INDEX (bcol2 BOM_CTO_ORDER_LINES_GT_N3) */

Line 4317: and exists (select /+ INDEX (bcol2 BOM_CTO_ORDER_LINES_GT_N3) /

4313: bom_cto_order_lines_gt bcol1
4314: set option_specific = '1'
4315: where
4316: ato_line_id = p_ato_line_id
4317: and exists (select /*+ INDEX (bcol2 BOM_CTO_ORDER_LINES_GT_N3) */
4318: 'X'
4319: from bom_cto_oss_components ossc,
4320: bom_cto_order_lines_gt bcol2,
4321: bom_cto_oss_orgs_list ossl

Line 4320: bom_cto_order_lines_gt bcol2,

4316: ato_line_id = p_ato_line_id
4317: and exists (select /*+ INDEX (bcol2 BOM_CTO_ORDER_LINES_GT_N3) */
4318: 'X'
4319: from bom_cto_oss_components ossc,
4320: bom_cto_order_lines_gt bcol2,
4321: bom_cto_oss_orgs_list ossl
4322: where bcol2.parent_ato_line_id = bcol1.line_id
4323: and ossc.model_item_id = bcol1.inventory_item_id
4324: and ossc.option_item_id = bcol2.inventory_item_id

Line 4397: Update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

4393:
4394: l_stmt_num := 40;
4395: If g_parent_rec.line_id.count > 0 then
4396: FORALL i in g_parent_rec.line_id.first..g_parent_rec.line_id.last
4397: Update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4398: bom_cto_order_lines_gt bcol
4399: set option_specific = g_parent_rec.option_specific(i)
4400: where line_id = g_parent_rec.line_id(i);
4401: end if;

Line 4398: bom_cto_order_lines_gt bcol

4394: l_stmt_num := 40;
4395: If g_parent_rec.line_id.count > 0 then
4396: FORALL i in g_parent_rec.line_id.first..g_parent_rec.line_id.last
4397: Update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4398: bom_cto_order_lines_gt bcol
4399: set option_specific = g_parent_rec.option_specific(i)
4400: where line_id = g_parent_rec.line_id(i);
4401: end if;
4402:

Line 4416: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4412: the flag determined earlier.
4413: */
4414:
4415: l_stmt_num := 50;
4416: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4417: bom_cto_order_lines_gt bcol
4418: set bcol.option_specific = (select msi.option_specific_sourced
4419: from mtl_system_items msi
4420: where msi.inventory_item_id = bcol.config_item_id

Line 4417: bom_cto_order_lines_gt bcol

4413: */
4414:
4415: l_stmt_num := 50;
4416: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4417: bom_cto_order_lines_gt bcol
4418: set bcol.option_specific = (select msi.option_specific_sourced
4419: from mtl_system_items msi
4420: where msi.inventory_item_id = bcol.config_item_id
4421: and rownum =1)

Line 4660: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4656: sr_source_id,
4657: assignment_id
4658: )
4659:
4660: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4661: bcol.inventory_item_id,
4662: bcol.line_id,
4663: p_ato_line_id,
4664: null,

Line 4685: bom_cto_order_lines_gt bcol

4681: mrp_sr_source_org src,
4682: mrp_sr_assignments assg,
4683: mrp_sourcing_rules rule,
4684: po_vendor_sites_all vend,
4685: bom_cto_order_lines_gt bcol
4686: where
4687: assg.assignment_set_id = g_def_assg_set
4688: and bcol.ato_line_id = p_ato_line_id
4689: and bcol.config_item_id is null

Line 4726: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4722: assignment_id,
4723: valid_flag
4724: )
4725:
4726: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4727: bcol.inventory_item_id,
4728: bcol.line_id,
4729: p_ato_line_id,
4730: bcol.config_item_id,

Line 4752: bom_cto_order_lines_gt bcol

4748: mrp_sr_source_org src,
4749: mrp_sr_assignments assg,
4750: mrp_sourcing_rules rule,
4751: po_vendor_sites_all vend,
4752: bom_cto_order_lines_gt bcol
4753: where
4754: assg.assignment_set_id = g_def_assg_set
4755: and bcol.ato_line_id = p_ato_line_id
4756: and bcol.config_creation = 3

Line 4862: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

4858: -- the sourcing rule already pruned and the sourcing data is gathered from
4859: -- config item.
4860:
4861: cursor oss_models is
4862: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
4863: line_id,
4864: ato_line_id,
4865: option_specific,
4866: inventory_item_id,

Line 4870: from bom_cto_order_lines_gt bcol

4866: inventory_item_id,
4867: config_item_id,
4868: perform_match,
4869: config_creation
4870: from bom_cto_order_lines_gt bcol
4871: where ato_line_id = p_ato_line_id
4872: and option_specific in ('1','2','3')
4873: and not (perform_match = 'Y' and config_creation = '3') -- 4093235
4874: order by plan_level desc;

Line 4952: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

4948:
4949: If x_exp_error_code <> 0 Then
4950: l_stmt_num :=80;
4951:
4952: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4953: bom_cto_order_lines_gt bcol
4954: set oss_error_code = x_exp_error_code
4955: where line_id = oss_model_rec.line_id;
4956:

Line 4953: bom_cto_order_lines_gt bcol

4949: If x_exp_error_code <> 0 Then
4950: l_stmt_num :=80;
4951:
4952: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4953: bom_cto_order_lines_gt bcol
4954: set oss_error_code = x_exp_error_code
4955: where line_id = oss_model_rec.line_id;
4956:
4957: If PG_DEBUG <> 0 Then

Line 4965: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

4961: If oss_model_rec.line_id <> oss_model_rec.ato_line_id then
4962:
4963: l_stmt_num := 90;
4964:
4965: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4966: bom_cto_order_lines_gt bcol
4967: Set oss_error_code = 360
4968: where line_id = oss_model_rec.ato_line_id;
4969: exit;

Line 4966: bom_cto_order_lines_gt bcol

4962:
4963: l_stmt_num := 90;
4964:
4965: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
4966: bom_cto_order_lines_gt bcol
4967: Set oss_error_code = 360
4968: where line_id = oss_model_rec.ato_line_id;
4969: exit;
4970:

Line 5027: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

5023: End if; /* x_return_status = FND_API.G_RET_STS_ERROR */
5024:
5025: If x_exp_error_code <> 0 Then
5026: l_stmt_num := 120;
5027: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5028: bom_cto_order_lines_gt bcol
5029: set oss_error_code = x_exp_error_code
5030: where line_id = oss_model_rec.line_id;
5031:

Line 5028: bom_cto_order_lines_gt bcol

5024:
5025: If x_exp_error_code <> 0 Then
5026: l_stmt_num := 120;
5027: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5028: bom_cto_order_lines_gt bcol
5029: set oss_error_code = x_exp_error_code
5030: where line_id = oss_model_rec.line_id;
5031:
5032: If PG_DEBUG <> 0 Then

Line 5038: update /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

5034: End if;
5035:
5036: If oss_model_rec.line_id <> oss_model_rec.ato_line_id then
5037: l_stmt_num := 130;
5038: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5039: bom_cto_order_lines_gt bcol
5040: set oss_error_code = 360
5041: where line_id = oss_model_rec.ato_line_id;
5042: If PG_DEBUG <> 0 Then

Line 5039: bom_cto_order_lines_gt bcol

5035:
5036: If oss_model_rec.line_id <> oss_model_rec.ato_line_id then
5037: l_stmt_num := 130;
5038: update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5039: bom_cto_order_lines_gt bcol
5040: set oss_error_code = 360
5041: where line_id = oss_model_rec.ato_line_id;
5042: If PG_DEBUG <> 0 Then
5043: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_ORDER_FOR_OSS: setting parent model error code to 360',5);

Line 5109: delete /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

5105: If pg_debug <> 0 Then
5106: oe_debug_pub.add(lpad(' ',g_pg_level)||'COPY_TO_BCOL_TEMP: Inside Copy to Bcol Temp API',5);
5107: end if;
5108:
5109: delete /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5110: from bom_cto_order_lines_gt bcol
5111: where ato_line_id = p_ato_line_id;
5112:
5113: INSERT into bom_cto_order_lines_gt(

Line 5110: from bom_cto_order_lines_gt bcol

5106: oe_debug_pub.add(lpad(' ',g_pg_level)||'COPY_TO_BCOL_TEMP: Inside Copy to Bcol Temp API',5);
5107: end if;
5108:
5109: delete /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5110: from bom_cto_order_lines_gt bcol
5111: where ato_line_id = p_ato_line_id;
5112:
5113: INSERT into bom_cto_order_lines_gt(
5114: ATO_LINE_ID,

Line 5113: INSERT into bom_cto_order_lines_gt(

5109: delete /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5110: from bom_cto_order_lines_gt bcol
5111: where ato_line_id = p_ato_line_id;
5112:
5113: INSERT into bom_cto_order_lines_gt(
5114: ATO_LINE_ID,
5115: BATCH_ID,
5116: BOM_ITEM_TYPE,
5117: COMPONENT_CODE,

Line 5139: Select /+ INDEX (bcol_upg BOM_CTO_ORDER_LINES_UPG_N4) /

5135: REUSE_CONFIG,
5136: QTY_PER_PARENT_MODEL,
5137: CONFIG_CREATION
5138: )
5139: Select /*+ INDEX (bcol_upg BOM_CTO_ORDER_LINES_UPG_N4) */
5140: ATO_LINE_ID,
5141: BATCH_ID,
5142: BOM_ITEM_TYPE,
5143: COMPONENT_CODE,

Line 5165: from bom_cto_order_lines_upg bcol_upg

5161: REUSE_CONFIG,
5162: QTY_PER_PARENT_MODEL,
5163: CONFIG_CREATION
5164:
5165: from bom_cto_order_lines_upg bcol_upg
5166: where ato_line_id = p_ato_line_id;
5167:
5168:
5169: If PG_DEBUG <> 0 Then

Line 5188: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

5184: x_return_status OUT NOCOPY Varchar2,
5185: x_msg_count OUT NOCOPY Number,
5186: x_msg_data OUT NOCOPY Varchar2) is
5187: cursor model_lines_cur is
5188: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5189: line_id,
5190: inventory_item_id,
5191: option_specific,
5192: parent_ato_line_id,

Line 5194: from bom_cto_order_lines_gt bcol

5190: inventory_item_id,
5191: option_specific,
5192: parent_ato_line_id,
5193: ato_line_id
5194: from bom_cto_order_lines_gt bcol
5195: where ato_line_id = p_ato_line_id
5196: and nvl(wip_supply_type,-1) <> '6'
5197: and bom_item_type = '1'
5198: and option_specific in ('1','2','3')

Line 5230: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) /

5226: line_id,
5227: organization_id,
5228: ato_line_id
5229: )
5230: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5231: -1,
5232: ship_from_org_id,
5233: p_ato_line_id
5234: from bom_cto_order_lines_gt bcol

Line 5234: from bom_cto_order_lines_gt bcol

5230: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
5231: -1,
5232: ship_from_org_id,
5233: p_ato_line_id
5234: from bom_cto_order_lines_gt bcol
5235: where line_id = p_ato_line_id;
5236:
5237: l_stmt_num := 20;
5238:

Line 5372: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

5368: sr_source_id,
5369: assignment_id
5370: )
5371:
5372: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5373: bcol.inventory_item_id,
5374: bcol.line_id,
5375: p_ato_line_id,
5376: null,

Line 5397: bom_cto_order_lines_gt bcol

5393: mrp_sr_source_org src,
5394: mrp_sr_assignments assg,
5395: mrp_sourcing_rules rule,
5396: po_vendor_sites_all vend,
5397: bom_cto_order_lines_gt bcol
5398: where
5399: assg.assignment_set_id = g_def_assg_set
5400: and bcol.ato_line_id = p_ato_line_id
5401: and bcol.config_creation = 3

Line 5442: select /+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) /

5438: assignment_id,
5439: Valid_flag /* 4093235 */
5440: )
5441:
5442: select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
5443: bcol.inventory_item_id,
5444: bcol.line_id,
5445: p_ato_line_id,
5446: bcol.config_item_id,

Line 5468: bom_cto_order_lines_gt bcol

5464: mrp_sr_source_org src,
5465: mrp_sr_assignments assg,
5466: mrp_sourcing_rules rule,
5467: po_vendor_sites_all vend,
5468: bom_cto_order_lines_gt bcol
5469: where
5470: assg.assignment_set_id = g_def_assg_set
5471: and bcol.ato_line_id = p_ato_line_id
5472: and bcol.config_creation = 3