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_OSS_SOURCE_GT

Line 640: delete /+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) /

636: return;
637: end if;
638: end if; --if p_mode = 'ACC' Bugfix 8894392
639:
640: delete /*+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) */
641: from bom_cto_oss_source_gt
642: where ato_line_id = p_ato_line_id;
643:
644: delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */

Line 641: from bom_cto_oss_source_gt

637: end if;
638: end if; --if p_mode = 'ACC' Bugfix 8894392
639:
640: delete /*+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) */
641: from bom_cto_oss_source_gt
642: where ato_line_id = p_ato_line_id;
643:
644: delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */
645: from bom_cto_oss_orgslist_gt

Line 791: or ship_from_org_id in (select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

787: l_valid_ship_from_org
788: from bom_cto_order_lines_gt bcol
789: where line_id = p_ato_line_id
790: and (option_specific is null
791: or ship_from_org_id in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
792: rcv_org_id
793: from bom_cto_oss_source_gt oss_src
794: where line_id = p_ato_line_id
795: and valid_flag = 'Y'

Line 793: from bom_cto_oss_source_gt oss_src

789: where line_id = p_ato_line_id
790: and (option_specific is null
791: or ship_from_org_id in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
792: rcv_org_id
793: from bom_cto_oss_source_gt oss_src
794: where line_id = p_ato_line_id
795: and valid_flag = 'Y'
796: union
797: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 797: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

793: from bom_cto_oss_source_gt oss_src
794: where line_id = p_ato_line_id
795: and valid_flag = 'Y'
796: union
797: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
798: source_org_id
799: from bom_cto_oss_source_gt oss_src
800: where line_id = p_ato_line_id
801: and valid_flag = 'Y'));

Line 799: from bom_cto_oss_source_gt oss_src

795: and valid_flag = 'Y'
796: union
797: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
798: source_org_id
799: from bom_cto_oss_source_gt oss_src
800: where line_id = p_ato_line_id
801: and valid_flag = 'Y'));
802: Exception when no_data_found then
803: If PG_DEBUG <> 0 Then

Line 838: (Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

834: Begin
835: Select 'Y'
836: into l_valid_preconfig_org
837: from
838: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
839: distinct nvl(source_org_id,rcv_org_id) org_id
840: from bom_cto_oss_source_gt oss_src
841: where line_id = p_ato_line_id
842: and valid_flag in( 'P','Y')

Line 840: from bom_cto_oss_source_gt oss_src

836: into l_valid_preconfig_org
837: from
838: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
839: distinct nvl(source_org_id,rcv_org_id) org_id
840: from bom_cto_oss_source_gt oss_src
841: where line_id = p_ato_line_id
842: and valid_flag in( 'P','Y')
843: and source_type in (2,3)
844: union

Line 845: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

841: where line_id = p_ato_line_id
842: and valid_flag in( 'P','Y')
843: and source_type in (2,3)
844: union
845: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
846: distinct source_org_id org_id
847: from bom_cto_oss_source_gt oss_src
848: where line_id = p_ato_line_id
849: and valid_flag in ('P','Y')

Line 847: from bom_cto_oss_source_gt oss_src

843: and source_type in (2,3)
844: union
845: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
846: distinct source_org_id org_id
847: from bom_cto_oss_source_gt oss_src
848: where line_id = p_ato_line_id
849: and valid_flag in ('P','Y')
850: and source_org_id not in (
851: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 851: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

847: from bom_cto_oss_source_gt oss_src
848: where line_id = p_ato_line_id
849: and valid_flag in ('P','Y')
850: and source_org_id not in (
851: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
852: rcv_org_id
853: from bom_cto_oss_source_gt oss_src
854: where line_id = p_ato_line_id
855: and valid_flag in( 'P','Y')))

Line 853: from bom_cto_oss_source_gt oss_src

849: and valid_flag in ('P','Y')
850: and source_org_id not in (
851: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
852: rcv_org_id
853: from bom_cto_oss_source_gt oss_src
854: where line_id = p_ato_line_id
855: and valid_flag in( 'P','Y')))
856: Where org_id = l_ship_from_org_id
857: and rownum = 1;

Line 1169: Update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1165: */
1166:
1167: l_stmt_num := 60;
1168:
1169: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1170: bom_cto_oss_source_gt oss_src
1171: set oss_src.valid_flag = 'Y'
1172: where oss_src.line_id = p_model_line_id
1173: and ((oss_src.source_org_id in

Line 1170: bom_cto_oss_source_gt oss_src

1166:
1167: l_stmt_num := 60;
1168:
1169: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1170: bom_cto_oss_source_gt oss_src
1171: set oss_src.valid_flag = 'Y'
1172: where oss_src.line_id = p_model_line_id
1173: and ((oss_src.source_org_id in
1174: (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */

Line 1211: insert into bom_cto_oss_source_gt

1207: even if it is not valid */
1208: l_stmt_num := 70;
1209:
1210:
1211: insert into bom_cto_oss_source_gt
1212: (
1213: inventory_item_id,
1214: line_id,
1215: config_item_id,

Line 1248: (select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1244: 'Y' /* Leaf node */
1245: from bom_cto_oss_orgslist_gt oss_lis,
1246: mtl_system_items msi
1247: where oss_lis.organization_id not in
1248: (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1249: nvl(rcv_org_id,-1)
1250: from bom_cto_oss_source_gt oss_src
1251: where oss_src.line_id = p_model_line_id
1252: union

Line 1250: from bom_cto_oss_source_gt oss_src

1246: mtl_system_items msi
1247: where oss_lis.organization_id not in
1248: (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1249: nvl(rcv_org_id,-1)
1250: from bom_cto_oss_source_gt oss_src
1251: where oss_src.line_id = p_model_line_id
1252: union
1253: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1254: nvl(source_org_id,-1)

Line 1253: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1249: nvl(rcv_org_id,-1)
1250: from bom_cto_oss_source_gt oss_src
1251: where oss_src.line_id = p_model_line_id
1252: union
1253: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1254: nvl(source_org_id,-1)
1255: from bom_cto_oss_source_gt oss_src
1256: where oss_src.line_id = p_model_line_id
1257: and valid_flag = 'Y'

Line 1255: from bom_cto_oss_source_gt oss_src

1251: where oss_src.line_id = p_model_line_id
1252: union
1253: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1254: nvl(source_org_id,-1)
1255: from bom_cto_oss_source_gt oss_src
1256: where oss_src.line_id = p_model_line_id
1257: and valid_flag = 'Y'
1258: )
1259: and oss_lis.line_id = p_model_line_id

Line 1277: Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1273: */
1274:
1275: l_stmt_num := 80;
1276:
1277: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1278: count(*)
1279: into l_valid_count
1280: from bom_cto_oss_source_gt oss_src
1281: where valid_flag = 'Y'

Line 1280: from bom_cto_oss_source_gt oss_src

1276:
1277: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1278: count(*)
1279: into l_valid_count
1280: from bom_cto_oss_source_gt oss_src
1281: where valid_flag = 'Y'
1282: and line_id = p_model_line_id;
1283:
1284: IF PG_DEBUG <> 0 Then

Line 1316: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1312: l_stmt_num := 90;
1313:
1314: /* Identify and mark all the leaf nodes in the valid sourcing tree */
1315:
1316: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1317: bom_cto_oss_source_gt oss_src
1318: set leaf_node = 'Y'
1319: where leaf_node is null
1320: and line_id = p_model_line_id

Line 1317: bom_cto_oss_source_gt oss_src

1313:
1314: /* Identify and mark all the leaf nodes in the valid sourcing tree */
1315:
1316: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1317: bom_cto_oss_source_gt oss_src
1318: set leaf_node = 'Y'
1319: where leaf_node is null
1320: and line_id = p_model_line_id
1321: and valid_flag = 'Y'

Line 1323: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1319: where leaf_node is null
1320: and line_id = p_model_line_id
1321: and valid_flag = 'Y'
1322: and source_org_id not in (
1323: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1324: rcv_org_id
1325: from bom_cto_oss_source_gt oss_src
1326: where line_id = p_model_line_id
1327: and valid_flag = 'Y');

Line 1325: from bom_cto_oss_source_gt oss_src

1321: and valid_flag = 'Y'
1322: and source_org_id not in (
1323: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1324: rcv_org_id
1325: from bom_cto_oss_source_gt oss_src
1326: where line_id = p_model_line_id
1327: and valid_flag = 'Y');
1328: /* Renga: Try converting this into a seperate procedure
1329: and re-use the code later

Line 1432: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1428: from bom_cto_order_lines_gt
1429: where line_id = p_model_line_id;
1430:
1431: If l_option_specific = '2' then
1432: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1433: bom_cto_oss_source_gt oss_src
1434: set valid_flag = 'N'
1435: where line_id = p_model_line_id
1436: and valid_flag is null;

Line 1433: bom_cto_oss_source_gt oss_src

1429: where line_id = p_model_line_id;
1430:
1431: If l_option_specific = '2' then
1432: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1433: bom_cto_oss_source_gt oss_src
1434: set valid_flag = 'N'
1435: where line_id = p_model_line_id
1436: and valid_flag is null;
1437: If PG_DEBUG <> 0 then

Line 1442: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1438: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
1439: ||sql%rowcount,5);
1440: End if;
1441:
1442: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1443: bom_cto_oss_source_gt oss_src
1444: set valid_flag = null
1445: where line_id = p_model_line_id
1446: and valid_flag = 'Y';

Line 1443: bom_cto_oss_source_gt oss_src

1439: ||sql%rowcount,5);
1440: End if;
1441:
1442: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1443: bom_cto_oss_source_gt oss_src
1444: set valid_flag = null
1445: where line_id = p_model_line_id
1446: and valid_flag = 'Y';
1447:

Line 1474: --and not exists(select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1470: where parent_ato_line_id = p_model_line_id
1471: and line_id <> p_model_line_id /* We should igonre the current row */
1472: and option_specific in ('1','2','3')
1473: --Bugfix 13540153-FP(13360098)
1474: --and not exists(select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1475: /* 'x'
1476: from bom_cto_oss_source_gt oss_src
1477: where line_id = bcol.line_id
1478: and rcv_org_id is null

Line 1476: from bom_cto_oss_source_gt oss_src

1472: and option_specific in ('1','2','3')
1473: --Bugfix 13540153-FP(13360098)
1474: --and not exists(select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1475: /* 'x'
1476: from bom_cto_oss_source_gt oss_src
1477: where line_id = bcol.line_id
1478: and rcv_org_id is null
1479: and nvl(valid_flag,'N') = 'Y'
1480: and option_specific = '3')*/;

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

1521: p_model_item_id,
1522: p_model_line_id,
1523: organization_id,
1524: p_ato_line_id
1525: from (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1526: oss_src.line_id line_id,
1527: oss_src.rcv_org_id organization_id
1528: from bom_cto_oss_source_gt oss_src,
1529: bom_cto_order_lines_gt bcol

Line 1528: from bom_cto_oss_source_gt oss_src,

1524: p_ato_line_id
1525: from (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1526: oss_src.line_id line_id,
1527: oss_src.rcv_org_id organization_id
1528: from bom_cto_oss_source_gt oss_src,
1529: bom_cto_order_lines_gt bcol
1530: where bcol.parent_ato_line_id = p_model_line_id
1531: and bcol.parent_ato_line_id <> bcol.line_id
1532: and bcol.option_specific in ('1','2','3')

Line 1536: --and not exists ( Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1532: and bcol.option_specific in ('1','2','3')
1533: and oss_src.line_id = bcol.line_id
1534: and oss_src.valid_flag = 'Y'
1535: --Bugfix 13540153-FP(13360098): Refer to .
1536: --and not exists ( Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1537: /* 'x'
1538: from bom_cto_oss_source_gt oss_src1
1539: where oss_src1.line_id = oss_src.line_id
1540: and bcol.option_specific = '3'

Line 1538: from bom_cto_oss_source_gt oss_src1

1534: and oss_src.valid_flag = 'Y'
1535: --Bugfix 13540153-FP(13360098): Refer to .
1536: --and not exists ( Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1537: /* 'x'
1538: from bom_cto_oss_source_gt oss_src1
1539: where oss_src1.line_id = oss_src.line_id
1540: and bcol.option_specific = '3'
1541: and nvl(valid_flag,'N') = 'Y'
1542: and rcv_org_id is null)*/

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

1540: and bcol.option_specific = '3'
1541: and nvl(valid_flag,'N') = 'Y'
1542: and rcv_org_id is null)*/
1543: Union
1544: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1545: oss_src.line_id line_id,
1546: oss_src.source_org_id organization_id
1547: from bom_cto_oss_source_gt oss_src,
1548: bom_cto_order_lines_gt bcol

Line 1547: from bom_cto_oss_source_gt oss_src,

1543: Union
1544: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
1545: oss_src.line_id line_id,
1546: oss_src.source_org_id organization_id
1547: from bom_cto_oss_source_gt oss_src,
1548: bom_cto_order_lines_gt bcol
1549: where bcol.parent_ato_line_id = p_model_line_id
1550: and bcol.parent_ato_line_id <> bcol.line_id
1551: and bcol.option_specific in ('1','2','3')

Line 1555: --and not exists ( Select /+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) /

1551: and bcol.option_specific in ('1','2','3')
1552: and oss_src.line_id = bcol.line_id
1553: and oss_src.valid_flag = 'Y'
1554: --Bugfix 13540153-FP(13360098): Refer to .
1555: --and not exists ( Select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
1556: /* 'x'
1557: from bom_cto_oss_source_gt oss_src1
1558: where oss_src1.line_id = oss_src.line_id
1559: and bcol.option_specific = '3'

Line 1557: from bom_cto_oss_source_gt oss_src1

1553: and oss_src.valid_flag = 'Y'
1554: --Bugfix 13540153-FP(13360098): Refer to .
1555: --and not exists ( Select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
1556: /* 'x'
1557: from bom_cto_oss_source_gt oss_src1
1558: where oss_src1.line_id = oss_src.line_id
1559: and bcol.option_specific = '3'
1560: and nvl(valid_flag,'N') = 'Y'
1561: and rcv_org_id is null)*/

Line 1612: Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1608:
1609: /* Check to see if there is a item level rule exists for the model
1610: */
1611: l_stmt_num := 60;
1612: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1613: count(*)
1614: into l_item_rule_count
1615: from bom_cto_oss_source_gt oss_src
1616: where line_id = p_model_line_id

Line 1615: from bom_cto_oss_source_gt oss_src

1611: l_stmt_num := 60;
1612: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1613: count(*)
1614: into l_item_rule_count
1615: from bom_cto_oss_source_gt oss_src
1616: where line_id = p_model_line_id
1617: and customer_id is null
1618: and rcv_org_id is null
1619: and nvl(valid_flag,'Y') <> 'N';

Line 1675: Update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1671: l_stmt_num := 90;
1672: If l_rule_exists = 'N' Then
1673: l_stmt_num := 100;
1674:
1675: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1676: bom_cto_oss_source_gt oss_src
1677: set leaf_node = 'Y'
1678: where line_id = p_model_line_id
1679: and nvl(valid_flag,'Y') <> 'N'

Line 1676: bom_cto_oss_source_gt oss_src

1672: If l_rule_exists = 'N' Then
1673: l_stmt_num := 100;
1674:
1675: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1676: bom_cto_oss_source_gt oss_src
1677: set leaf_node = 'Y'
1678: where line_id = p_model_line_id
1679: and nvl(valid_flag,'Y') <> 'N'
1680: and ( source_type in (2,3)

Line 1682: (Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1678: where line_id = p_model_line_id
1679: and nvl(valid_flag,'Y') <> 'N'
1680: and ( source_type in (2,3)
1681: or source_org_id not in
1682: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1683: rcv_org_id
1684: from bom_cto_oss_source_gt oss_src
1685: where line_id = p_model_line_id
1686: and nvl(valid_flag,'Y') <> 'N'

Line 1684: from bom_cto_oss_source_gt oss_src

1680: and ( source_type in (2,3)
1681: or source_org_id not in
1682: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1683: rcv_org_id
1684: from bom_cto_oss_source_gt oss_src
1685: where line_id = p_model_line_id
1686: and nvl(valid_flag,'Y') <> 'N'
1687: )
1688: );

Line 1698: Update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1694: */
1695: l_stmt_num := 110;
1696:
1697:
1698: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1699: bom_cto_oss_source_gt
1700: set valid_flag = 'Y'
1701: where line_id = p_model_line_id
1702: and leaf_node = 'Y'

Line 1699: bom_cto_oss_source_gt

1695: l_stmt_num := 110;
1696:
1697:
1698: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1699: bom_cto_oss_source_gt
1700: set valid_flag = 'Y'
1701: where line_id = p_model_line_id
1702: and leaf_node = 'Y'
1703: and nvl(valid_flag,'Y') <> 'N'

Line 1773: Insert into bom_cto_oss_source_gt

1769: If PG_DEBUG <> 0 Then
1770: oe_debug_pub.add(lpad(' ',g_pg_level)||
1771: 'PRUNE_PARENT_OSS: Before inserting 100% make at rules',5);
1772: End if;
1773: Insert into bom_cto_oss_source_gt
1774: (
1775: inventory_item_id,
1776: line_id,
1777: rcv_org_id,

Line 1813: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1809: where
1810: bcol.line_id = p_model_line_id
1811: and bcol.option_specific = '3'
1812: and oss_lis.organization_id not in (
1813: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1814: nvl(source_org_id, -1)
1815: from bom_cto_oss_source_gt oss_src
1816: where valid_flag = 'Y'
1817: and line_id = p_model_line_id

Line 1815: from bom_cto_oss_source_gt oss_src

1811: and bcol.option_specific = '3'
1812: and oss_lis.organization_id not in (
1813: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1814: nvl(source_org_id, -1)
1815: from bom_cto_oss_source_gt oss_src
1816: where valid_flag = 'Y'
1817: and line_id = p_model_line_id
1818: union
1819: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 1819: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1815: from bom_cto_oss_source_gt oss_src
1816: where valid_flag = 'Y'
1817: and line_id = p_model_line_id
1818: union
1819: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1820: nvl(rcv_org_id,-1)
1821: from bom_cto_oss_source_gt oss_src
1822: where valid_flag = 'Y'
1823: and line_id = p_model_line_id)

Line 1821: from bom_cto_oss_source_gt oss_src

1817: and line_id = p_model_line_id
1818: union
1819: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1820: nvl(rcv_org_id,-1)
1821: from bom_cto_oss_source_gt oss_src
1822: where valid_flag = 'Y'
1823: and line_id = p_model_line_id)
1824: and oss_lis.line_id = p_model_line_id
1825: and oss_lis.organization_id = msi.organization_id

Line 1836: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1832: no valid nodes, CTO will fail with error message */
1833:
1834:
1835:
1836: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1837: count(*)
1838: into l_valid_source_count
1839: from bom_cto_oss_source_gt oss_src
1840: where line_id = p_model_line_id

Line 1839: from bom_cto_oss_source_gt oss_src

1835:
1836: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1837: count(*)
1838: into l_valid_source_count
1839: from bom_cto_oss_source_gt oss_src
1840: where line_id = p_model_line_id
1841: and valid_flag = 'Y';
1842:
1843: IF PG_DEBUG <> 0 Then

Line 1910: bom_cto_oss_source_gt and create new

1906:
1907:
1908:
1909: /* This procedure will look at the pruned tree from
1910: bom_cto_oss_source_gt and create new
1911: sourcing rules and assignments
1912: */
1913:
1914: Procedure Create_oss_sourcing_rules (

Line 1951: Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

1947:
1948:
1949: Cursor source_tree_cur(p_line_id number,
1950: p_config_item_id number) is
1951: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1952: oss_src.inventory_item_id inventory_item_id,
1953: oss_src.line_id line_id,
1954: oss_src.rcv_org_id rcv_org_id,
1955: oss_src.source_org_id source_org_id,

Line 1994: from bom_cto_oss_source_gt oss_src,

1990: src_asg.secondary_inventory secondary_inventory,
1991: src_asg.ship_to_site_id ship_to_site_id,
1992: src_asg.sourcing_rule_type sourcing_rule_type,
1993: src_asg.sourcing_rule_id sourcing_rule_id
1994: from bom_cto_oss_source_gt oss_src,
1995: mrp_sr_assignments src_asg
1996: where oss_src.line_id = p_line_id
1997: and nvl(oss_src.reuse_flag,'Y') = 'N'
1998: and valid_flag = 'P'

Line 2020: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2016: --
2017: Cursor oss_make_orgs_cur(p_line_id Number,
2018: p_config_item_id Number
2019: ) is
2020: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2021: rcv_org_id,
2022: source_org_id,
2023: allocation,
2024: rank,

Line 2026: from bom_cto_oss_source_gt oss_src

2022: source_org_id,
2023: allocation,
2024: rank,
2025: config_item_id
2026: from bom_cto_oss_source_gt oss_src
2027: where line_id = p_line_id
2028: and valid_flag = 'P'
2029: and leaf_node = 'Y'
2030: and assignment_id is null

Line 2033: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2029: and leaf_node = 'Y'
2030: and assignment_id is null
2031: and rcv_org_id IS NULL
2032: UNION
2033: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2034: rcv_org_id,
2035: source_org_id,
2036: allocation,
2037: rank,

Line 2039: from bom_cto_oss_source_gt oss_src

2035: source_org_id,
2036: allocation,
2037: rank,
2038: config_item_id
2039: from bom_cto_oss_source_gt oss_src
2040: WHERE line_id = p_line_id
2041: AND valid_flag = 'P'
2042: AND leaf_node = 'Y'
2043: AND assignment_id IS NULL

Line 2059: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2055: -- Bug 13362916
2056: -- Performance changes
2057: --
2058: Cursor oss_reused_assg(p_line_id Number,p_config_item_id number) is
2059: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2060: distinct assignment_id
2061: from bom_cto_oss_source_gt oss_src
2062: where line_id = p_line_id
2063: and valid_flag = 'P'

Line 2061: from bom_cto_oss_source_gt oss_src

2057: --
2058: Cursor oss_reused_assg(p_line_id Number,p_config_item_id number) is
2059: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2060: distinct assignment_id
2061: from bom_cto_oss_source_gt oss_src
2062: where line_id = p_line_id
2063: and valid_flag = 'P'
2064: and nvl(reuse_flag,'Y') = 'Y'
2065: and rcv_org_id is null

Line 2067: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2063: and valid_flag = 'P'
2064: and nvl(reuse_flag,'Y') = 'Y'
2065: and rcv_org_id is null
2066: UNION
2067: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2068: distinct assignment_id
2069: FROM BOM_CTO_OSS_SOURCE_GT OSS_SRC
2070: WHERE LINE_ID = p_line_id
2071: and VALID_FLAG = 'P'

Line 2069: FROM BOM_CTO_OSS_SOURCE_GT OSS_SRC

2065: and rcv_org_id is null
2066: UNION
2067: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2068: distinct assignment_id
2069: FROM BOM_CTO_OSS_SOURCE_GT OSS_SRC
2070: WHERE LINE_ID = p_line_id
2071: and VALID_FLAG = 'P'
2072: and NVL(REUSE_FLAG,'Y') = 'Y'
2073: and RCV_ORG_ID IS NOT NULL

Line 2240: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2236: processed
2237: */
2238: l_stmt_num := 50;
2239:
2240: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2241: bom_cto_oss_source_gt oss_src
2242: set valid_flag = 'P'
2243: where line_id = oss_model_lines_rec.line_id
2244: and valid_flag = 'Y';

Line 2241: bom_cto_oss_source_gt oss_src

2237: */
2238: l_stmt_num := 50;
2239:
2240: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2241: bom_cto_oss_source_gt oss_src
2242: set valid_flag = 'P'
2243: where line_id = oss_model_lines_rec.line_id
2244: and valid_flag = 'Y';
2245:

Line 2247: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='

2243: where line_id = oss_model_lines_rec.line_id
2244: and valid_flag = 'Y';
2245:
2246: IF PG_DEBUG <> 0 then
2247: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2248: ||sql%rowcount,5);
2249: End if;
2250:
2251: Else

Line 2256: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2252: /* We need to create sourcing rule only for this order chain */
2253: /* We need to find the order chain from bcso */
2254:
2255: l_stmt_num := 60;
2256: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2257: bom_cto_oss_source_gt oss_src
2258: set valid_flag = 'P'
2259: where line_id = oss_model_lines_rec.line_id
2260: and valid_flag = 'Y'

Line 2257: bom_cto_oss_source_gt oss_src

2253: /* We need to find the order chain from bcso */
2254:
2255: l_stmt_num := 60;
2256: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2257: bom_cto_oss_source_gt oss_src
2258: set valid_flag = 'P'
2259: where line_id = oss_model_lines_rec.line_id
2260: and valid_flag = 'Y'
2261: and rcv_org_id in (select rcv_org_id

Line 2266: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='

2262: from bom_cto_src_orgs
2263: where line_id = oss_model_lines_rec.line_id
2264: and organization_type is not null);
2265: IF PG_DEBUG <> 0 then
2266: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2267: ||sql%rowcount,5);
2268: End if;
2269:
2270: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 2270: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2266: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2267: ||sql%rowcount,5);
2268: End if;
2269:
2270: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2271: bom_cto_oss_source_gt oss_src
2272: set valid_flag = 'P'
2273: where line_id = oss_model_lines_rec.line_id
2274: and valid_flag = 'Y'

Line 2271: bom_cto_oss_source_gt oss_src

2267: ||sql%rowcount,5);
2268: End if;
2269:
2270: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2271: bom_cto_oss_source_gt oss_src
2272: set valid_flag = 'P'
2273: where line_id = oss_model_lines_rec.line_id
2274: and valid_flag = 'Y'
2275: and rcv_org_id is null

Line 2280: and rcv_org_id not in (select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2276: and exists (select rcv_org_id
2277: from bom_cto_src_orgs
2278: where line_id = oss_model_lines_rec.line_id
2279: and organization_type is not null
2280: and rcv_org_id not in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2281: rcv_org_id
2282: from bom_cto_oss_source_gt oss_src
2283: where line_id = oss_model_lines_rec.line_id
2284: and valid_flag = 'P'));

Line 2282: from bom_cto_oss_source_gt oss_src

2278: where line_id = oss_model_lines_rec.line_id
2279: and organization_type is not null
2280: and rcv_org_id not in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2281: rcv_org_id
2282: from bom_cto_oss_source_gt oss_src
2283: where line_id = oss_model_lines_rec.line_id
2284: and valid_flag = 'P'));
2285:
2286: IF PG_DEBUG <> 0 then

Line 2287: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='

2283: where line_id = oss_model_lines_rec.line_id
2284: and valid_flag = 'P'));
2285:
2286: IF PG_DEBUG <> 0 then
2287: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2288: ||sql%rowcount,5);
2289: End if;
2290: End if;
2291:

Line 2296: update /+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) /

2292: /* Mark all the rows that are not be re-used
2293: */
2294:
2295: l_stmt_num := 65;
2296: update /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
2297: bom_cto_oss_source_gt oss_src1
2298: set reuse_flag = 'N'
2299: where line_id = oss_model_lines_rec.line_id
2300: and valid_flag = 'P'

Line 2297: bom_cto_oss_source_gt oss_src1

2293: */
2294:
2295: l_stmt_num := 65;
2296: update /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
2297: bom_cto_oss_source_gt oss_src1
2298: set reuse_flag = 'N'
2299: where line_id = oss_model_lines_rec.line_id
2300: and valid_flag = 'P'
2301: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */

Line 2301: and (oss_src1.assignment_id is null or exists (select/+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) /

2297: bom_cto_oss_source_gt oss_src1
2298: set reuse_flag = 'N'
2299: where line_id = oss_model_lines_rec.line_id
2300: and valid_flag = 'P'
2301: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */
2302: 'x'
2303: from bom_cto_oss_source_gt oss_src2
2304: where oss_src2.line_id = oss_src1.line_id
2305: and oss_src2.source_rule_id = oss_src1.source_rule_id

Line 2303: from bom_cto_oss_source_gt oss_src2

2299: where line_id = oss_model_lines_rec.line_id
2300: and valid_flag = 'P'
2301: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */
2302: 'x'
2303: from bom_cto_oss_source_gt oss_src2
2304: where oss_src2.line_id = oss_src1.line_id
2305: and oss_src2.source_rule_id = oss_src1.source_rule_id
2306: and nvl(oss_src2.valid_flag,'N') = 'N'
2307: )

Line 2650: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

2646: if nvl(l_old_rank,-1) <> source_tree_rec.rank then
2647:
2648: l_stmt_num := 160;
2649:
2650: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2651: sum(allocation)
2652: into l_rank_sum
2653: from bom_cto_oss_source_gt oss_src
2654: where line_id = source_tree_rec.line_id

Line 2653: from bom_cto_oss_source_gt oss_src

2649:
2650: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2651: sum(allocation)
2652: into l_rank_sum
2653: from bom_cto_oss_source_gt oss_src
2654: where line_id = source_tree_rec.line_id
2655: and source_rule_id = source_tree_rec.sourcing_rule_id
2656: and rank = source_tree_rec.rank
2657: and valid_flag = 'P';

Line 3423: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3419: return;
3420: end if;
3421: l_stmt_num := 120;
3422:
3423: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3424: bom_cto_oss_source_gt oss_src
3425: set valid_flag = 'Y'
3426: where source_org_id = p_end_org
3427: and line_id = p_line_id

Line 3424: bom_cto_oss_source_gt oss_src

3420: end if;
3421: l_stmt_num := 120;
3422:
3423: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3424: bom_cto_oss_source_gt oss_src
3425: set valid_flag = 'Y'
3426: where source_org_id = p_end_org
3427: and line_id = p_line_id
3428: and nvl(leaf_node,'N') <> 'Y'

Line 3502: Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3498: sourcing tree
3499: */
3500:
3501: Cursor global_orgs_cur is
3502: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3503: source_org_id
3504: from bom_cto_oss_source_gt oss_src
3505: where customer_id is null
3506: and rcv_org_id is null

Line 3504: from bom_cto_oss_source_gt oss_src

3500:
3501: Cursor global_orgs_cur is
3502: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3503: source_org_id
3504: from bom_cto_oss_source_gt oss_src
3505: where customer_id is null
3506: and rcv_org_id is null
3507: and line_id = p_model_line_id
3508: and nvl(valid_flag,'Y') <> 'N';

Line 3529: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3525: Begin
3526: /* The following sql may not be needed as this will
3527: be part of find_leaf_node itself
3528: */
3529: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3530: source_org_id
3531: bulk collect into l_source_org_id
3532: from bom_cto_oss_source_gt oss_src
3533: where rcv_org_id = global_orgs_rec.source_org_id

Line 3532: from bom_cto_oss_source_gt oss_src

3528: */
3529: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3530: source_org_id
3531: bulk collect into l_source_org_id
3532: from bom_cto_oss_source_gt oss_src
3533: where rcv_org_id = global_orgs_rec.source_org_id
3534: and line_id = p_model_line_id
3535: and nvl(valid_flag,'Y') <> 'N';
3536:

Line 3567: Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3563: End;
3564:
3565: End Loop;
3566:
3567: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3568: count(*)
3569: into l_valid_count
3570: from bom_cto_oss_source_gt oss_src
3571: where line_id = p_model_line_id

Line 3570: from bom_cto_oss_source_gt oss_src

3566:
3567: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3568: count(*)
3569: into l_valid_count
3570: from bom_cto_oss_source_gt oss_src
3571: where line_id = p_model_line_id
3572: and valid_flag ='Y';
3573:
3574: If l_valid_count > 0 then

Line 3590: Update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3586: 3. Mark all the make at nodes for whihc the org is part of intersection
3587: list as valid nodes.
3588: */
3589:
3590: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3591: bom_cto_oss_source_gt oss_src
3592: set leaf_node = 'Y',
3593: valid_flag = 'Y'
3594: where

Line 3591: bom_cto_oss_source_gt oss_src

3587: list as valid nodes.
3588: */
3589:
3590: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3591: bom_cto_oss_source_gt oss_src
3592: set leaf_node = 'Y',
3593: valid_flag = 'Y'
3594: where
3595: line_id = p_model_line_id

Line 3607: (Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3603: where line_id = p_model_line_id)
3604: )
3605: or ( source_type = 1
3606: and source_org_id not in
3607: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3608: nvl(rcv_org_id,-1)
3609: from bom_cto_oss_source_gt oss_src
3610: where line_id = p_model_line_id
3611: and nvl(valid_flag,'Y') <> 'N')

Line 3609: from bom_cto_oss_source_gt oss_src

3605: or ( source_type = 1
3606: and source_org_id not in
3607: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3608: nvl(rcv_org_id,-1)
3609: from bom_cto_oss_source_gt oss_src
3610: where line_id = p_model_line_id
3611: and nvl(valid_flag,'Y') <> 'N')
3612: )
3613: )

Line 3703: -- select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3699: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: p_model_line_id = '||p_model_line_id,5);
3700: End if;
3701:
3702: -- bug 13362916
3703: -- select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3704: -- source_org_id,
3705: -- source_type
3706: --bulk collect into
3707: -- l_source_org_id,

Line 3709: --from bom_cto_oss_source_gt oss_src

3705: -- source_type
3706: --bulk collect into
3707: -- l_source_org_id,
3708: -- l_source_type
3709: --from bom_cto_oss_source_gt oss_src
3710: --where rcv_org_id = p_source_org_id
3711: --and line_id = p_model_line_id
3712: --and nvl(valid_flag,'Y') <> 'N';
3713:

Line 3714: SELECT /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3710: --where rcv_org_id = p_source_org_id
3711: --and line_id = p_model_line_id
3712: --and nvl(valid_flag,'Y') <> 'N';
3713:
3714: SELECT /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3715: source_org_id,
3716: source_type
3717: BULK COLLECT INTO source_details_tab
3718: FROM bom_cto_oss_source_gt oss_src

Line 3718: FROM bom_cto_oss_source_gt oss_src

3714: SELECT /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3715: source_org_id,
3716: source_type
3717: BULK COLLECT INTO source_details_tab
3718: FROM bom_cto_oss_source_gt oss_src
3719: WHERE rcv_org_id = p_source_org_id
3720: AND line_id = p_model_line_id
3721: AND nvl(valid_flag,'Y') <> 'N';
3722:

Line 3767: ================PRINTING BOM_CTO_OSS_SOURCE_GT==================

3763: End if;
3764:
3765: /*Bugfix 13540153-FP(13360098): Changing rcv_org_id to source_org_id and adding an nvl.
3766: 1. Changing rcv_org_id to source_org_id: Consider the sourcing data for parent non OSS model as:
3767: ================PRINTING BOM_CTO_OSS_SOURCE_GT==================
3768: Line_id --- Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf --- sr_receipt_id ---
3769: -99326 --- 3087074 --- --- 122 --- --- --- --- 1 --- 40 --- 1 --- --- --- --- 213011
3770: -99326 --- 3087074 --- --- 164 --- --- --- --- 1 --- 35 --- 1 --- --- --- --- 213011
3771: -99326 --- 3087074 --- --- 304 --- --- --- --- 1 --- 25 --- 1 --- --- --- --- 213011

Line 3796: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3792: This was very strange. Without nvl, the record 'rcv_org = 304, src_org = 122' was still being marked
3793: as valid. Added an nvl to get around this problem.
3794: */
3795:
3796: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3797: bom_cto_oss_source_gt oss_src
3798: set leaf_node = 'Y',
3799: valid_flag = 'Y'
3800: where line_id = p_model_line_id

Line 3797: bom_cto_oss_source_gt oss_src

3793: as valid. Added an nvl to get around this problem.
3794: */
3795:
3796: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3797: bom_cto_oss_source_gt oss_src
3798: set leaf_node = 'Y',
3799: valid_flag = 'Y'
3800: where line_id = p_model_line_id
3801: and source_org_id = p_source_org_id

Line 3844: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3840: IF PG_DEBUG <> 0 THEN
3841: oe_debug_pub.add('Inside else.. Find_leaf_node');
3842: END IF;
3843:
3844: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3845: bom_cto_oss_source_gt oss_src
3846: set leaf_node = 'Y',
3847: valid_flag = 'Y'
3848: where line_id = p_model_line_id

Line 3845: bom_cto_oss_source_gt oss_src

3841: oe_debug_pub.add('Inside else.. Find_leaf_node');
3842: END IF;
3843:
3844: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3845: bom_cto_oss_source_gt oss_src
3846: set leaf_node = 'Y',
3847: valid_flag = 'Y'
3848: where line_id = p_model_line_id
3849: and source_org_id = p_source_org_id

Line 3916: update /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

3912: '||p_source_org_id,1);
3913:
3914: End if;
3915:
3916: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3917: bom_cto_oss_source_gt oss_src
3918: set valid_flag = 'Y'
3919: where line_id = p_model_line_id
3920: and source_org_id = p_source_org_id

Line 3917: bom_cto_oss_source_gt oss_src

3913:
3914: End if;
3915:
3916: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3917: bom_cto_oss_source_gt oss_src
3918: set valid_flag = 'Y'
3919: where line_id = p_model_line_id
3920: and source_org_id = p_source_org_id
3921: and nvl(valid_flag,'Y') <> 'N'

Line 4019: delete from bom_cto_oss_source_gt ;

4015: If PG_DEBUG <> 0 Then
4016: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Begin GET_OSS_ORGS_LIST API',5);
4017: End if;
4018:
4019: delete from bom_cto_oss_source_gt ;
4020: If PG_DEBUG <> 0 Then
4021: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
4022: ||sql%rowcount,5);
4023: end if;

Line 4406: update bom_cto_oss_source_gt ossgt1

4402:
4403: delete from bom_cto_oss_orgslist_gt;
4404:
4405: l_stmt_no := 30;
4406: update bom_cto_oss_source_gt ossgt1
4407: set reuse_flag = 'N'
4408: where rcv_org_id is not null
4409: and valid_flag = 'Y'
4410: and not exists (select 'x'

Line 4411: from bom_cto_oss_source_gt ossgt2

4407: set reuse_flag = 'N'
4408: where rcv_org_id is not null
4409: and valid_flag = 'Y'
4410: and not exists (select 'x'
4411: from bom_cto_oss_source_gt ossgt2
4412: where ossgt1.line_id = ossgt2.line_id
4413: and ossgt2.rcv_org_id = ossgt1.rcv_org_id
4414: and ossgt2.source_type = 2
4415: and ossgt2.valid_flag = 'Y');

Line 4419: update bom_cto_oss_source_gt ossgt1

4415: and ossgt2.valid_flag = 'Y');
4416:
4417:
4418: l_stmt_no := 40;
4419: update bom_cto_oss_source_gt ossgt1
4420: set reuse_flag = 'Y'
4421: where rcv_org_id is not null
4422: and valid_flag = 'Y'
4423: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */

Line 4423: and exists (select/+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) /

4419: update bom_cto_oss_source_gt ossgt1
4420: set reuse_flag = 'Y'
4421: where rcv_org_id is not null
4422: and valid_flag = 'Y'
4423: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */
4424: 'x'
4425: from bom_cto_oss_source_gt ossgt2
4426: where ossgt1.line_id = ossgt2.line_id
4427: and ossgt2.rcv_org_id = ossgt1.rcv_org_id

Line 4425: from bom_cto_oss_source_gt ossgt2

4421: where rcv_org_id is not null
4422: and valid_flag = 'Y'
4423: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */
4424: 'x'
4425: from bom_cto_oss_source_gt ossgt2
4426: where ossgt1.line_id = ossgt2.line_id
4427: and ossgt2.rcv_org_id = ossgt1.rcv_org_id
4428: and ossgt2.source_type = 2
4429: and ossgt2.valid_flag = 'Y');

Line 4451: from bom_cto_oss_source_gt oss_src,

4447: -- oss_src.vendor_site_code,
4448: to_number(null), --3894241
4449: null,
4450: reuse_flag
4451: from bom_cto_oss_source_gt oss_src,
4452: bom_cto_order_lines_gt bcol
4453: where bcol.line_id = oss_src.line_id
4454: and oss_error_code is null
4455: and oss_src.valid_flag = 'Y'

Line 4469: from bom_cto_oss_source_gt oss_src,

4465: -- oss_src.vendor_site_code,
4466: to_number(null), --3894241
4467: null,
4468: null
4469: from bom_cto_oss_source_gt oss_src,
4470: bom_cto_order_lines_gt bcol
4471: where bcol.line_id = oss_src.line_id
4472: and bcol.option_specific is not null
4473: and oss_error_code is null

Line 4476: and oss_src.source_org_id not in (select /+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) /

4472: and bcol.option_specific is not null
4473: and oss_error_code is null
4474: and oss_src.valid_flag = 'Y'
4475: and oss_src.source_org_id is not null
4476: and oss_src.source_org_id not in (select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
4477: rcv_org_id
4478: from bom_cto_oss_source_gt oss_src1
4479: where oss_src1.line_id = oss_src.line_id
4480: and valid_flag = 'Y'

Line 4478: from bom_cto_oss_source_gt oss_src1

4474: and oss_src.valid_flag = 'Y'
4475: and oss_src.source_org_id is not null
4476: and oss_src.source_org_id not in (select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
4477: rcv_org_id
4478: from bom_cto_oss_source_gt oss_src1
4479: where oss_src1.line_id = oss_src.line_id
4480: and valid_flag = 'Y'
4481: )
4482: and nvl(bcol.option_specific,'4') <> '4'

Line 4492: from bom_cto_oss_source_gt oss_src,

4488: to_number(null),--3894241
4489: oss_src.vendor_id,
4490: oss_src.vendor_site_code,
4491: null
4492: from bom_cto_oss_source_gt oss_src,
4493: bom_cto_order_lines_gt bcol
4494: where bcol.line_id = oss_src.line_id
4495: and bcol.option_specific is not null
4496: and oss_error_code is null

Line 5192: Insert into bom_cto_oss_source_gt

5188: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Inside GET_SOURCING_DATA API',5);
5189: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Assignment set id ='||g_def_assg_set,5);
5190: End if;
5191:
5192: Insert into bom_cto_oss_source_gt
5193: (
5194: Inventory_item_id,
5195: Line_id,
5196: ato_line_id,

Line 5259: Insert into bom_cto_oss_source_gt

5255: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 1st sql ='||sql%rowcount,5);
5256: -- 13362916
5257: Print_source_gt;
5258: End if;
5259: Insert into bom_cto_oss_source_gt
5260: (
5261: Inventory_item_id,
5262: Line_id,
5263: ato_line_id,

Line 5332: Insert into bom_cto_oss_source_gt

5328: /*
5329:
5330: If p_config_creation = '3' Then
5331:
5332: Insert into bom_cto_oss_source_gt
5333: (
5334: Inventory_item_id,
5335: Line_id,
5336: config_item_id,

Line 5882: delete /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) /

5878: l_stmt_num := 10;
5879: g_pg_level := g_pg_level + 3;
5880: x_return_status := FND_API.G_RET_STS_SUCCESS;
5881:
5882: delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
5883: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
5884:
5885: If PG_DEBUG <> 0 Then
5886: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Inside GET_ORDER_SOURCING_DATA API',5);

Line 5883: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;

5879: g_pg_level := g_pg_level + 3;
5880: x_return_status := FND_API.G_RET_STS_SUCCESS;
5881:
5882: delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
5883: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
5884:
5885: If PG_DEBUG <> 0 Then
5886: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Inside GET_ORDER_SOURCING_DATA API',5);
5887: End if;

Line 5951: Insert into bom_cto_oss_source_gt

5947: end if;
5948:
5949: l_stmt_num := 50;
5950: FORALL i in x_assg_list.assignment_id.first..x_assg_list.assignment_id.last
5951: Insert into bom_cto_oss_source_gt
5952: (
5953: Inventory_item_id,
5954: Line_id,
5955: ato_line_id,

Line 6016: Insert into bom_cto_oss_source_gt

6012: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting Model attribute 3 lines',5);
6013: End if;
6014:
6015: l_stmt_num := 60;
6016: Insert into bom_cto_oss_source_gt
6017: (
6018: Inventory_item_id,
6019: Line_id,
6020: ato_line_id,

Line 6101: Insert into bom_cto_oss_source_gt

6097: -- we need the data from model and not config.
6098: /*
6099:
6100: l_stmt_num := 70;
6101: Insert into bom_cto_oss_source_gt
6102: (
6103: Inventory_item_id,
6104: Line_id,
6105: ato_line_id,

Line 6369: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6365: i Number;
6366: l_buy_type Varchar2(1) := 'N';
6367:
6368: Cursor source_org_rule_cur is
6369: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6370: source_org_id,
6371: source_type
6372: from bom_cto_oss_source_gt oss_src
6373: where line_id = p_line_id

Line 6372: from bom_cto_oss_source_gt oss_src

6368: Cursor source_org_rule_cur is
6369: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6370: source_org_id,
6371: source_type
6372: from bom_cto_oss_source_gt oss_src
6373: where line_id = p_line_id
6374: and valid_flag = 'Y'
6375: and rcv_org_id = p_organization_id;
6376:

Line 6378: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6374: and valid_flag = 'Y'
6375: and rcv_org_id = p_organization_id;
6376:
6377: Cursor source_item_rule_cur is
6378: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6379: source_org_id,
6380: source_type
6381: from bom_cto_oss_source_gt oss_src
6382: where line_id = p_line_id

Line 6381: from bom_cto_oss_source_gt oss_src

6377: Cursor source_item_rule_cur is
6378: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6379: source_org_id,
6380: source_type
6381: from bom_cto_oss_source_gt oss_src
6382: where line_id = p_line_id
6383: and valid_flag = 'Y'
6384: and rcv_org_id is null;
6385:

Line 6557: (Select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6553:
6554: Select org_id
6555: bulk collect into x_orgs_list
6556: from
6557: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6558: distinct nvl(source_org_id,rcv_org_id) org_id
6559: from bom_cto_oss_source_gt oss_src
6560: where line_id = p_line_id
6561: and valid_flag in( 'P','Y')

Line 6559: from bom_cto_oss_source_gt oss_src

6555: bulk collect into x_orgs_list
6556: from
6557: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6558: distinct nvl(source_org_id,rcv_org_id) org_id
6559: from bom_cto_oss_source_gt oss_src
6560: where line_id = p_line_id
6561: and valid_flag in( 'P','Y')
6562: and source_type in (2,3)
6563: union

Line 6564: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6560: where line_id = p_line_id
6561: and valid_flag in( 'P','Y')
6562: and source_type in (2,3)
6563: union
6564: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6565: distinct source_org_id org_id
6566: from bom_cto_oss_source_gt oss_src
6567: where line_id = p_line_id
6568: and valid_flag in ('P','Y')

Line 6566: from bom_cto_oss_source_gt oss_src

6562: and source_type in (2,3)
6563: union
6564: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6565: distinct source_org_id org_id
6566: from bom_cto_oss_source_gt oss_src
6567: where line_id = p_line_id
6568: and valid_flag in ('P','Y')
6569: and source_org_id not in (
6570: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

Line 6570: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6566: from bom_cto_oss_source_gt oss_src
6567: where line_id = p_line_id
6568: and valid_flag in ('P','Y')
6569: and source_org_id not in (
6570: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6571: rcv_org_id
6572: from bom_cto_oss_source_gt oss_src
6573: where line_id = p_line_id
6574: and valid_flag in( 'P','Y')));

Line 6572: from bom_cto_oss_source_gt oss_src

6568: and valid_flag in ('P','Y')
6569: and source_org_id not in (
6570: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6571: rcv_org_id
6572: from bom_cto_oss_source_gt oss_src
6573: where line_id = p_line_id
6574: and valid_flag in( 'P','Y')));
6575: If PG_DEBUG <> 0 Then
6576: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: Number of orgs where bom should be created = '

Line 6588: select /+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) /

6584:
6585: -- Bugfix 13362916
6586: Procedure Print_source_gt is
6587: cursor source_cur is
6588: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
6589: line_id,
6590: inventory_item_id,
6591: rcv_org_id,
6592: source_org_id,

Line 6603: from bom_cto_oss_source_gt oss_src;

6599: reuse_flag,
6600: valid_flag,
6601: leaf_node,
6602: SR_RECEIPT_ID -- bug 13362916
6603: from bom_cto_oss_source_gt oss_src;
6604: --where line_id = p_line_id;
6605:
6606: Begin
6607: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);

Line 6607: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);

6603: from bom_cto_oss_source_gt oss_src;
6604: --where line_id = p_line_id;
6605:
6606: Begin
6607: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);
6608: --oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
6609: oe_debug_pub.add('Line_id --- Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf --- sr_receipt_id --- ',5);
6610: for source_rec in source_cur
6611: loop