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 594: delete /+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) /

590:
591: return;
592: end if;
593:
594: delete /*+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) */
595: from bom_cto_oss_source_gt
596: where ato_line_id = p_ato_line_id;
597:
598: delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */

Line 595: from bom_cto_oss_source_gt

591: return;
592: end if;
593:
594: delete /*+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) */
595: from bom_cto_oss_source_gt
596: where ato_line_id = p_ato_line_id;
597:
598: delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */
599: from bom_cto_oss_orgslist_gt

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

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
707: from bom_cto_oss_source_gt oss_src
708: where line_id = p_ato_line_id
709: and valid_flag = 'Y'

Line 707: from bom_cto_oss_source_gt oss_src

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
707: from bom_cto_oss_source_gt oss_src
708: where line_id = p_ato_line_id
709: and valid_flag = 'Y'
710: union
711: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

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

707: from bom_cto_oss_source_gt oss_src
708: where line_id = p_ato_line_id
709: and valid_flag = 'Y'
710: union
711: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
712: source_org_id
713: from bom_cto_oss_source_gt oss_src
714: where line_id = p_ato_line_id
715: and valid_flag = 'Y'));

Line 713: from bom_cto_oss_source_gt oss_src

709: and valid_flag = 'Y'
710: union
711: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
712: source_org_id
713: from bom_cto_oss_source_gt oss_src
714: where line_id = p_ato_line_id
715: and valid_flag = 'Y'));
716: Exception when no_data_found then
717: If PG_DEBUG <> 0 Then

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

746: Begin
747: Select 'Y'
748: into l_valid_preconfig_org
749: from
750: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
751: distinct nvl(source_org_id,rcv_org_id) org_id
752: from bom_cto_oss_source_gt oss_src
753: where line_id = p_ato_line_id
754: and valid_flag in( 'P','Y')

Line 752: from bom_cto_oss_source_gt oss_src

748: into l_valid_preconfig_org
749: from
750: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
751: distinct nvl(source_org_id,rcv_org_id) org_id
752: from bom_cto_oss_source_gt oss_src
753: where line_id = p_ato_line_id
754: and valid_flag in( 'P','Y')
755: and source_type in (2,3)
756: union

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

753: where line_id = p_ato_line_id
754: and valid_flag in( 'P','Y')
755: and source_type in (2,3)
756: union
757: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
758: distinct source_org_id org_id
759: from bom_cto_oss_source_gt oss_src
760: where line_id = p_ato_line_id
761: and valid_flag in ('P','Y')

Line 759: from bom_cto_oss_source_gt oss_src

755: and source_type in (2,3)
756: union
757: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
758: distinct source_org_id org_id
759: from bom_cto_oss_source_gt oss_src
760: where line_id = p_ato_line_id
761: and valid_flag in ('P','Y')
762: and source_org_id not in (
763: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

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

759: from bom_cto_oss_source_gt oss_src
760: where line_id = p_ato_line_id
761: and valid_flag in ('P','Y')
762: and source_org_id not in (
763: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
764: rcv_org_id
765: from bom_cto_oss_source_gt oss_src
766: where line_id = p_ato_line_id
767: and valid_flag in( 'P','Y')))

Line 765: from bom_cto_oss_source_gt oss_src

761: and valid_flag in ('P','Y')
762: and source_org_id not in (
763: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
764: rcv_org_id
765: from bom_cto_oss_source_gt oss_src
766: where line_id = p_ato_line_id
767: and valid_flag in( 'P','Y')))
768: Where org_id = l_ship_from_org_id
769: and rownum = 1;

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

1077: */
1078:
1079: l_stmt_num := 60;
1080:
1081: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1082: bom_cto_oss_source_gt oss_src
1083: set oss_src.valid_flag = 'Y'
1084: where oss_src.line_id = p_model_line_id
1085: and ((oss_src.source_org_id in

Line 1082: bom_cto_oss_source_gt oss_src

1078:
1079: l_stmt_num := 60;
1080:
1081: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1082: bom_cto_oss_source_gt oss_src
1083: set oss_src.valid_flag = 'Y'
1084: where oss_src.line_id = p_model_line_id
1085: and ((oss_src.source_org_id in
1086: (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */

Line 1123: insert into bom_cto_oss_source_gt

1119: even if it is not valid */
1120: l_stmt_num := 70;
1121:
1122:
1123: insert into bom_cto_oss_source_gt
1124: (
1125: inventory_item_id,
1126: line_id,
1127: config_item_id,

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

1156: 'Y' /* Leaf node */
1157: from bom_cto_oss_orgslist_gt oss_lis,
1158: mtl_system_items msi
1159: where oss_lis.organization_id not in
1160: (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1161: nvl(rcv_org_id,-1)
1162: from bom_cto_oss_source_gt oss_src
1163: where oss_src.line_id = p_model_line_id
1164: union

Line 1162: from bom_cto_oss_source_gt oss_src

1158: mtl_system_items msi
1159: where oss_lis.organization_id not in
1160: (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1161: nvl(rcv_org_id,-1)
1162: from bom_cto_oss_source_gt oss_src
1163: where oss_src.line_id = p_model_line_id
1164: union
1165: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1166: nvl(source_org_id,-1)

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

1161: nvl(rcv_org_id,-1)
1162: from bom_cto_oss_source_gt oss_src
1163: where oss_src.line_id = p_model_line_id
1164: union
1165: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1166: nvl(source_org_id,-1)
1167: from bom_cto_oss_source_gt oss_src
1168: where oss_src.line_id = p_model_line_id
1169: and valid_flag = 'Y'

Line 1167: from bom_cto_oss_source_gt oss_src

1163: where oss_src.line_id = p_model_line_id
1164: union
1165: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1166: nvl(source_org_id,-1)
1167: from bom_cto_oss_source_gt oss_src
1168: where oss_src.line_id = p_model_line_id
1169: and valid_flag = 'Y'
1170: )
1171: and oss_lis.line_id = p_model_line_id

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

1185: */
1186:
1187: l_stmt_num := 80;
1188:
1189: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1190: count(*)
1191: into l_valid_count
1192: from bom_cto_oss_source_gt oss_src
1193: where valid_flag = 'Y'

Line 1192: from bom_cto_oss_source_gt oss_src

1188:
1189: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1190: count(*)
1191: into l_valid_count
1192: from bom_cto_oss_source_gt oss_src
1193: where valid_flag = 'Y'
1194: and line_id = p_model_line_id;
1195:
1196: IF PG_DEBUG <> 0 Then

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

1217: l_stmt_num := 90;
1218:
1219: /* Identify and mark all the leaf nodes in the valid sourcing tree */
1220:
1221: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1222: bom_cto_oss_source_gt oss_src
1223: set leaf_node = 'Y'
1224: where leaf_node is null
1225: and line_id = p_model_line_id

Line 1222: bom_cto_oss_source_gt oss_src

1218:
1219: /* Identify and mark all the leaf nodes in the valid sourcing tree */
1220:
1221: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1222: bom_cto_oss_source_gt oss_src
1223: set leaf_node = 'Y'
1224: where leaf_node is null
1225: and line_id = p_model_line_id
1226: and valid_flag = 'Y'

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

1224: where leaf_node is null
1225: and line_id = p_model_line_id
1226: and valid_flag = 'Y'
1227: and source_org_id not in (
1228: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1229: rcv_org_id
1230: from bom_cto_oss_source_gt oss_src
1231: where line_id = p_model_line_id
1232: and valid_flag = 'Y');

Line 1230: from bom_cto_oss_source_gt oss_src

1226: and valid_flag = 'Y'
1227: and source_org_id not in (
1228: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1229: rcv_org_id
1230: from bom_cto_oss_source_gt oss_src
1231: where line_id = p_model_line_id
1232: and valid_flag = 'Y');
1233: /* Renga: Try converting this into a seperate procedure
1234: and re-use the code later

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

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) */
1330: bom_cto_oss_source_gt oss_src
1331: set valid_flag = 'N'
1332: where line_id = p_model_line_id
1333: and valid_flag is null;

Line 1330: bom_cto_oss_source_gt oss_src

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) */
1330: bom_cto_oss_source_gt oss_src
1331: set valid_flag = 'N'
1332: where line_id = p_model_line_id
1333: and valid_flag is null;
1334: If PG_DEBUG <> 0 then

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

1335: oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
1336: ||sql%rowcount,5);
1337: End if;
1338:
1339: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1340: bom_cto_oss_source_gt oss_src
1341: set valid_flag = null
1342: where line_id = p_model_line_id
1343: and valid_flag = 'Y';

Line 1340: bom_cto_oss_source_gt oss_src

1336: ||sql%rowcount,5);
1337: End if;
1338:
1339: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1340: bom_cto_oss_source_gt oss_src
1341: set valid_flag = null
1342: where line_id = p_model_line_id
1343: and valid_flag = 'Y';
1344:

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

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) */
1370: 'x'
1371: from bom_cto_oss_source_gt oss_src
1372: where line_id = bcol.line_id
1373: and rcv_org_id is null

Line 1371: from bom_cto_oss_source_gt oss_src

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) */
1370: 'x'
1371: from bom_cto_oss_source_gt oss_src
1372: where line_id = bcol.line_id
1373: and rcv_org_id is null
1374: and nvl(valid_flag,'N') = 'Y'
1375: and option_specific = '3');

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 1408: from bom_cto_oss_source_gt oss_src,

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
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')

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

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
1414: and oss_src.valid_flag = 'Y'
1415: and not exists ( Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1416: 'x'
1417: from bom_cto_oss_source_gt oss_src1
1418: where oss_src1.line_id = oss_src.line_id
1419: and bcol.option_specific = '3'

Line 1417: from bom_cto_oss_source_gt oss_src1

1413: and oss_src.line_id = bcol.line_id
1414: and oss_src.valid_flag = 'Y'
1415: and not exists ( Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1416: 'x'
1417: from bom_cto_oss_source_gt oss_src1
1418: where oss_src1.line_id = oss_src.line_id
1419: and bcol.option_specific = '3'
1420: and nvl(valid_flag,'N') = 'Y'
1421: and rcv_org_id is null)

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 1426: from bom_cto_oss_source_gt oss_src,

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
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')

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

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
1432: and oss_src.valid_flag = 'Y'
1433: and not exists ( Select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
1434: 'x'
1435: from bom_cto_oss_source_gt oss_src1
1436: where oss_src1.line_id = oss_src.line_id
1437: and bcol.option_specific = '3'

Line 1435: from bom_cto_oss_source_gt oss_src1

1431: and oss_src.line_id = bcol.line_id
1432: and oss_src.valid_flag = 'Y'
1433: and not exists ( Select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
1434: 'x'
1435: from bom_cto_oss_source_gt oss_src1
1436: where oss_src1.line_id = oss_src.line_id
1437: and bcol.option_specific = '3'
1438: and nvl(valid_flag,'N') = 'Y'
1439: and rcv_org_id is null)

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

1467:
1468: /* Check to see if there is a item level rule exists for the model
1469: */
1470: l_stmt_num := 60;
1471: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1472: count(*)
1473: into l_item_rule_count
1474: from bom_cto_oss_source_gt oss_src
1475: where line_id = p_model_line_id

Line 1474: from bom_cto_oss_source_gt oss_src

1470: l_stmt_num := 60;
1471: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1472: count(*)
1473: into l_item_rule_count
1474: from bom_cto_oss_source_gt oss_src
1475: where line_id = p_model_line_id
1476: and customer_id is null
1477: and rcv_org_id is null
1478: and nvl(valid_flag,'Y') <> 'N';

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

1530: l_stmt_num := 90;
1531: If l_rule_exists = 'N' Then
1532: l_stmt_num := 100;
1533:
1534: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1535: bom_cto_oss_source_gt oss_src
1536: set leaf_node = 'Y'
1537: where line_id = p_model_line_id
1538: and nvl(valid_flag,'Y') <> 'N'

Line 1535: bom_cto_oss_source_gt oss_src

1531: If l_rule_exists = 'N' Then
1532: l_stmt_num := 100;
1533:
1534: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1535: bom_cto_oss_source_gt oss_src
1536: set leaf_node = 'Y'
1537: where line_id = p_model_line_id
1538: and nvl(valid_flag,'Y') <> 'N'
1539: and ( source_type in (2,3)

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

1537: where line_id = p_model_line_id
1538: and nvl(valid_flag,'Y') <> 'N'
1539: and ( source_type in (2,3)
1540: or source_org_id not in
1541: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1542: rcv_org_id
1543: from bom_cto_oss_source_gt oss_src
1544: where line_id = p_model_line_id
1545: and nvl(valid_flag,'Y') <> 'N'

Line 1543: from bom_cto_oss_source_gt oss_src

1539: and ( source_type in (2,3)
1540: or source_org_id not in
1541: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1542: rcv_org_id
1543: from bom_cto_oss_source_gt oss_src
1544: where line_id = p_model_line_id
1545: and nvl(valid_flag,'Y') <> 'N'
1546: )
1547: );

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

1553: */
1554: l_stmt_num := 110;
1555:
1556:
1557: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1558: bom_cto_oss_source_gt
1559: set valid_flag = 'Y'
1560: where line_id = p_model_line_id
1561: and leaf_node = 'Y'

Line 1558: bom_cto_oss_source_gt

1554: l_stmt_num := 110;
1555:
1556:
1557: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1558: bom_cto_oss_source_gt
1559: set valid_flag = 'Y'
1560: where line_id = p_model_line_id
1561: and leaf_node = 'Y'
1562: and nvl(valid_flag,'Y') <> 'N'

Line 1632: Insert into bom_cto_oss_source_gt

1628: If PG_DEBUG <> 0 Then
1629: oe_debug_pub.add(lpad(' ',g_pg_level)||
1630: 'PRUNE_PARENT_OSS: Before inserting 100% make at rules',5);
1631: End if;
1632: Insert into bom_cto_oss_source_gt
1633: (
1634: inventory_item_id,
1635: line_id,
1636: rcv_org_id,

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

1668: where
1669: bcol.line_id = p_model_line_id
1670: and bcol.option_specific = '3'
1671: and oss_lis.organization_id not in (
1672: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1673: nvl(source_org_id, -1)
1674: from bom_cto_oss_source_gt oss_src
1675: where valid_flag = 'Y'
1676: and line_id = p_model_line_id

Line 1674: from bom_cto_oss_source_gt oss_src

1670: and bcol.option_specific = '3'
1671: and oss_lis.organization_id not in (
1672: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1673: nvl(source_org_id, -1)
1674: from bom_cto_oss_source_gt oss_src
1675: where valid_flag = 'Y'
1676: and line_id = p_model_line_id
1677: union
1678: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

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

1674: from bom_cto_oss_source_gt oss_src
1675: where valid_flag = 'Y'
1676: and line_id = p_model_line_id
1677: union
1678: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1679: nvl(rcv_org_id,-1)
1680: from bom_cto_oss_source_gt oss_src
1681: where valid_flag = 'Y'
1682: and line_id = p_model_line_id)

Line 1680: from bom_cto_oss_source_gt oss_src

1676: and line_id = p_model_line_id
1677: union
1678: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1679: nvl(rcv_org_id,-1)
1680: from bom_cto_oss_source_gt oss_src
1681: where valid_flag = 'Y'
1682: and line_id = p_model_line_id)
1683: and oss_lis.line_id = p_model_line_id
1684: and oss_lis.organization_id = msi.organization_id

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

1691: no valid nodes, CTO will fail with error message */
1692:
1693:
1694:
1695: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1696: count(*)
1697: into l_valid_source_count
1698: from bom_cto_oss_source_gt oss_src
1699: where line_id = p_model_line_id

Line 1698: from bom_cto_oss_source_gt oss_src

1694:
1695: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1696: count(*)
1697: into l_valid_source_count
1698: from bom_cto_oss_source_gt oss_src
1699: where line_id = p_model_line_id
1700: and valid_flag = 'Y';
1701:
1702: IF PG_DEBUG <> 0 Then

Line 1763: bom_cto_oss_source_gt and create new

1759:
1760:
1761:
1762: /* This procedure will look at the pruned tree from
1763: bom_cto_oss_source_gt and create new
1764: sourcing rules and assignments
1765: */
1766:
1767: Procedure Create_oss_sourcing_rules (

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

1800:
1801:
1802: Cursor source_tree_cur(p_line_id number,
1803: p_config_item_id number) is
1804: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1805: oss_src.inventory_item_id inventory_item_id,
1806: oss_src.line_id line_id,
1807: oss_src.rcv_org_id rcv_org_id,
1808: oss_src.source_org_id source_org_id,

Line 1847: from bom_cto_oss_source_gt oss_src,

1843: src_asg.secondary_inventory secondary_inventory,
1844: src_asg.ship_to_site_id ship_to_site_id,
1845: src_asg.sourcing_rule_type sourcing_rule_type,
1846: src_asg.sourcing_rule_id sourcing_rule_id
1847: from bom_cto_oss_source_gt oss_src,
1848: mrp_sr_assignments src_asg
1849: where oss_src.line_id = p_line_id
1850: and nvl(oss_src.reuse_flag,'Y') = 'N'
1851: and valid_flag = 'P'

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

1866:
1867: Cursor oss_make_orgs_cur(p_line_id Number,
1868: p_config_item_id Number
1869: ) is
1870: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1871: rcv_org_id,
1872: source_org_id,
1873: allocation,
1874: rank,

Line 1876: from bom_cto_oss_source_gt oss_src

1872: source_org_id,
1873: allocation,
1874: rank,
1875: config_item_id
1876: from bom_cto_oss_source_gt oss_src
1877: where line_id = p_line_id
1878: and valid_flag = 'P'
1879: and leaf_node = 'Y'
1880: and assignment_id is null

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

1885: /* This will pick up all rows that we introduced
1886: for 100% make at rule*/
1887:
1888: Cursor oss_reused_assg(p_line_id Number,p_config_item_id number) is
1889: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1890: distinct assignment_id
1891: from bom_cto_oss_source_gt oss_src
1892: where line_id = p_line_id
1893: and valid_flag = 'P'

Line 1891: from bom_cto_oss_source_gt oss_src

1887:
1888: Cursor oss_reused_assg(p_line_id Number,p_config_item_id number) is
1889: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
1890: distinct assignment_id
1891: from bom_cto_oss_source_gt oss_src
1892: where line_id = p_line_id
1893: and valid_flag = 'P'
1894: and nvl(reuse_flag,'Y') = 'Y'
1895: and nvl(rcv_org_id,-1) not in (select nvl(organization_id,-1)

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

2025: processed
2026: */
2027: l_stmt_num := 50;
2028:
2029: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2030: bom_cto_oss_source_gt oss_src
2031: set valid_flag = 'P'
2032: where line_id = oss_model_lines_rec.line_id
2033: and valid_flag = 'Y';

Line 2030: bom_cto_oss_source_gt oss_src

2026: */
2027: l_stmt_num := 50;
2028:
2029: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2030: bom_cto_oss_source_gt oss_src
2031: set valid_flag = 'P'
2032: where line_id = oss_model_lines_rec.line_id
2033: and valid_flag = 'Y';
2034:

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

2032: where line_id = oss_model_lines_rec.line_id
2033: and valid_flag = 'Y';
2034:
2035: IF PG_DEBUG <> 0 then
2036: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2037: ||sql%rowcount,5);
2038: End if;
2039:
2040: Else

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

2041: /* We need to create sourcing rule only for this order chain */
2042: /* We need to find the order chain from bcso */
2043:
2044: l_stmt_num := 60;
2045: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2046: bom_cto_oss_source_gt oss_src
2047: set valid_flag = 'P'
2048: where line_id = oss_model_lines_rec.line_id
2049: and valid_flag = 'Y'

Line 2046: bom_cto_oss_source_gt oss_src

2042: /* We need to find the order chain from bcso */
2043:
2044: l_stmt_num := 60;
2045: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2046: bom_cto_oss_source_gt oss_src
2047: set valid_flag = 'P'
2048: where line_id = oss_model_lines_rec.line_id
2049: and valid_flag = 'Y'
2050: and rcv_org_id in (select rcv_org_id

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

2051: from bom_cto_src_orgs
2052: where line_id = oss_model_lines_rec.line_id
2053: and organization_type is not null);
2054: IF PG_DEBUG <> 0 then
2055: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2056: ||sql%rowcount,5);
2057: End if;
2058:
2059: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

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

2055: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2056: ||sql%rowcount,5);
2057: End if;
2058:
2059: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2060: bom_cto_oss_source_gt oss_src
2061: set valid_flag = 'P'
2062: where line_id = oss_model_lines_rec.line_id
2063: and valid_flag = 'Y'

Line 2060: bom_cto_oss_source_gt oss_src

2056: ||sql%rowcount,5);
2057: End if;
2058:
2059: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2060: bom_cto_oss_source_gt oss_src
2061: set valid_flag = 'P'
2062: where line_id = oss_model_lines_rec.line_id
2063: and valid_flag = 'Y'
2064: and rcv_org_id is null

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

2065: and exists (select rcv_org_id
2066: from bom_cto_src_orgs
2067: where line_id = oss_model_lines_rec.line_id
2068: and organization_type is not null
2069: and rcv_org_id not in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2070: rcv_org_id
2071: from bom_cto_oss_source_gt oss_src
2072: where line_id = oss_model_lines_rec.line_id
2073: and valid_flag = 'P'));

Line 2071: from bom_cto_oss_source_gt oss_src

2067: where line_id = oss_model_lines_rec.line_id
2068: and organization_type is not null
2069: and rcv_org_id not in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2070: rcv_org_id
2071: from bom_cto_oss_source_gt oss_src
2072: where line_id = oss_model_lines_rec.line_id
2073: and valid_flag = 'P'));
2074:
2075: IF PG_DEBUG <> 0 then

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

2072: where line_id = oss_model_lines_rec.line_id
2073: and valid_flag = 'P'));
2074:
2075: IF PG_DEBUG <> 0 then
2076: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
2077: ||sql%rowcount,5);
2078: End if;
2079: End if;
2080:

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

2081: /* Mark all the rows that are not be re-used
2082: */
2083:
2084: l_stmt_num := 65;
2085: update /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
2086: bom_cto_oss_source_gt oss_src1
2087: set reuse_flag = 'N'
2088: where line_id = oss_model_lines_rec.line_id
2089: and valid_flag = 'P'

Line 2086: bom_cto_oss_source_gt oss_src1

2082: */
2083:
2084: l_stmt_num := 65;
2085: update /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
2086: bom_cto_oss_source_gt oss_src1
2087: set reuse_flag = 'N'
2088: where line_id = oss_model_lines_rec.line_id
2089: and valid_flag = 'P'
2090: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */

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

2086: bom_cto_oss_source_gt oss_src1
2087: set reuse_flag = 'N'
2088: where line_id = oss_model_lines_rec.line_id
2089: and valid_flag = 'P'
2090: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */
2091: 'x'
2092: from bom_cto_oss_source_gt oss_src2
2093: where oss_src2.line_id = oss_src1.line_id
2094: and oss_src2.source_rule_id = oss_src1.source_rule_id

Line 2092: from bom_cto_oss_source_gt oss_src2

2088: where line_id = oss_model_lines_rec.line_id
2089: and valid_flag = 'P'
2090: and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */
2091: 'x'
2092: from bom_cto_oss_source_gt oss_src2
2093: where oss_src2.line_id = oss_src1.line_id
2094: and oss_src2.source_rule_id = oss_src1.source_rule_id
2095: and nvl(oss_src2.valid_flag,'N') = 'N'
2096: )

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

2377: if nvl(l_old_rank,-1) <> source_tree_rec.rank then
2378:
2379: l_stmt_num := 160;
2380:
2381: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2382: sum(allocation)
2383: into l_rank_sum
2384: from bom_cto_oss_source_gt oss_src
2385: where line_id = source_tree_rec.line_id

Line 2384: from bom_cto_oss_source_gt oss_src

2380:
2381: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2382: sum(allocation)
2383: into l_rank_sum
2384: from bom_cto_oss_source_gt oss_src
2385: where line_id = source_tree_rec.line_id
2386: and source_rule_id = source_tree_rec.sourcing_rule_id
2387: and rank = source_tree_rec.rank
2388: and valid_flag = 'P';

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

2981: return;
2982: end if;
2983: l_stmt_num := 120;
2984:
2985: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2986: bom_cto_oss_source_gt oss_src
2987: set valid_flag = 'Y'
2988: where source_org_id = p_end_org
2989: and line_id = p_line_id

Line 2986: bom_cto_oss_source_gt oss_src

2982: end if;
2983: l_stmt_num := 120;
2984:
2985: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
2986: bom_cto_oss_source_gt oss_src
2987: set valid_flag = 'Y'
2988: where source_org_id = p_end_org
2989: and line_id = p_line_id
2990: and nvl(leaf_node,'N') <> 'Y'

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

3060: sourcing tree
3061: */
3062:
3063: Cursor global_orgs_cur is
3064: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3065: source_org_id
3066: from bom_cto_oss_source_gt oss_src
3067: where customer_id is null
3068: and rcv_org_id is null

Line 3066: from bom_cto_oss_source_gt oss_src

3062:
3063: Cursor global_orgs_cur is
3064: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3065: source_org_id
3066: from bom_cto_oss_source_gt oss_src
3067: where customer_id is null
3068: and rcv_org_id is null
3069: and line_id = p_model_line_id
3070: and nvl(valid_flag,'Y') <> 'N';

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

3087: Begin
3088: /* The following sql may not be needed as this will
3089: be part of find_leaf_node itself
3090: */
3091: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3092: source_org_id
3093: bulk collect into l_source_org_id
3094: from bom_cto_oss_source_gt oss_src
3095: where rcv_org_id = global_orgs_rec.source_org_id

Line 3094: from bom_cto_oss_source_gt oss_src

3090: */
3091: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3092: source_org_id
3093: bulk collect into l_source_org_id
3094: from bom_cto_oss_source_gt oss_src
3095: where rcv_org_id = global_orgs_rec.source_org_id
3096: and line_id = p_model_line_id
3097: and nvl(valid_flag,'Y') <> 'N';
3098:

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

3125: End;
3126:
3127: End Loop;
3128:
3129: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3130: count(*)
3131: into l_valid_count
3132: from bom_cto_oss_source_gt oss_src
3133: where line_id = p_model_line_id

Line 3132: from bom_cto_oss_source_gt oss_src

3128:
3129: Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3130: count(*)
3131: into l_valid_count
3132: from bom_cto_oss_source_gt oss_src
3133: where line_id = p_model_line_id
3134: and valid_flag ='Y';
3135:
3136: If l_valid_count > 0 then

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

3148: 3. Mark all the make at nodes for whihc the org is part of intersection
3149: list as valid nodes.
3150: */
3151:
3152: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3153: bom_cto_oss_source_gt oss_src
3154: set leaf_node = 'Y',
3155: valid_flag = 'Y'
3156: where

Line 3153: bom_cto_oss_source_gt oss_src

3149: list as valid nodes.
3150: */
3151:
3152: Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3153: bom_cto_oss_source_gt oss_src
3154: set leaf_node = 'Y',
3155: valid_flag = 'Y'
3156: where
3157: line_id = p_model_line_id

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

3165: where line_id = p_model_line_id)
3166: )
3167: or ( source_type = 1
3168: and source_org_id not in
3169: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3170: nvl(rcv_org_id,-1)
3171: from bom_cto_oss_source_gt oss_src
3172: where line_id = p_model_line_id
3173: and nvl(valid_flag,'Y') <> 'N')

Line 3171: from bom_cto_oss_source_gt oss_src

3167: or ( source_type = 1
3168: and source_org_id not in
3169: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3170: nvl(rcv_org_id,-1)
3171: from bom_cto_oss_source_gt oss_src
3172: where line_id = p_model_line_id
3173: and nvl(valid_flag,'Y') <> 'N')
3174: )
3175: )

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

3248: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_source_org_id = '||p_source_org_id,5);
3249: oe_debug_pub.add(lpad(' ',g_pg_level)||'FIND_LEAF_NODE: P_rcv_org_id = '||p_rcv_org_id,5);
3250: End if;
3251:
3252: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3253: source_org_id,
3254: source_type
3255: bulk collect into
3256: l_source_org_id,

Line 3258: from bom_cto_oss_source_gt oss_src

3254: source_type
3255: bulk collect into
3256: l_source_org_id,
3257: l_source_type
3258: from bom_cto_oss_source_gt oss_src
3259: where rcv_org_id = p_source_org_id
3260: and line_id = p_model_line_id
3261: and nvl(valid_flag,'Y') <> 'N';
3262:

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

3293: End if;
3294:
3295:
3296:
3297: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3298: bom_cto_oss_source_gt oss_src
3299: set leaf_node = 'Y',
3300: valid_flag = 'Y'
3301: where line_id = p_model_line_id

Line 3298: bom_cto_oss_source_gt oss_src

3294:
3295:
3296:
3297: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3298: bom_cto_oss_source_gt oss_src
3299: set leaf_node = 'Y',
3300: valid_flag = 'Y'
3301: where line_id = p_model_line_id
3302: and source_org_id = p_source_org_id

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

3330:
3331: End if;/* Sql%rowcount <> 0 then */
3332: End Loop;
3333: else
3334: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3335: bom_cto_oss_source_gt oss_src
3336: set leaf_node = 'Y',
3337: valid_flag = 'Y'
3338: where line_id = p_model_line_id

Line 3335: bom_cto_oss_source_gt oss_src

3331: End if;/* Sql%rowcount <> 0 then */
3332: End Loop;
3333: else
3334: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3335: bom_cto_oss_source_gt oss_src
3336: set leaf_node = 'Y',
3337: valid_flag = 'Y'
3338: where line_id = p_model_line_id
3339: and source_org_id = p_source_org_id

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

3393: '||p_source_org_id,1);
3394:
3395: End if;
3396:
3397: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3398: bom_cto_oss_source_gt oss_src
3399: set valid_flag = 'Y'
3400: where line_id = p_model_line_id
3401: and source_org_id = p_source_org_id

Line 3398: bom_cto_oss_source_gt oss_src

3394:
3395: End if;
3396:
3397: update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
3398: bom_cto_oss_source_gt oss_src
3399: set valid_flag = 'Y'
3400: where line_id = p_model_line_id
3401: and source_org_id = p_source_org_id
3402: and nvl(valid_flag,'Y') <> 'N'

Line 3500: delete from bom_cto_oss_source_gt ;

3496: If PG_DEBUG <> 0 Then
3497: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Begin GET_OSS_ORGS_LIST API',5);
3498: End if;
3499:
3500: delete from bom_cto_oss_source_gt ;
3501: If PG_DEBUG <> 0 Then
3502: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
3503: ||sql%rowcount,5);
3504: end if;

Line 3887: update bom_cto_oss_source_gt ossgt1

3883:
3884: delete from bom_cto_oss_orgslist_gt;
3885:
3886: l_stmt_no := 30;
3887: update bom_cto_oss_source_gt ossgt1
3888: set reuse_flag = 'N'
3889: where rcv_org_id is not null
3890: and valid_flag = 'Y'
3891: and not exists (select 'x'

Line 3892: from bom_cto_oss_source_gt ossgt2

3888: set reuse_flag = 'N'
3889: where rcv_org_id is not null
3890: and valid_flag = 'Y'
3891: and not exists (select 'x'
3892: from bom_cto_oss_source_gt ossgt2
3893: where ossgt1.line_id = ossgt2.line_id
3894: and ossgt2.rcv_org_id = ossgt1.rcv_org_id
3895: and ossgt2.source_type = 2
3896: and ossgt2.valid_flag = 'Y');

Line 3900: update bom_cto_oss_source_gt ossgt1

3896: and ossgt2.valid_flag = 'Y');
3897:
3898:
3899: l_stmt_no := 40;
3900: update bom_cto_oss_source_gt ossgt1
3901: set reuse_flag = 'Y'
3902: where rcv_org_id is not null
3903: and valid_flag = 'Y'
3904: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */

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

3900: update bom_cto_oss_source_gt ossgt1
3901: set reuse_flag = 'Y'
3902: where rcv_org_id is not null
3903: and valid_flag = 'Y'
3904: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */
3905: 'x'
3906: from bom_cto_oss_source_gt ossgt2
3907: where ossgt1.line_id = ossgt2.line_id
3908: and ossgt2.rcv_org_id = ossgt1.rcv_org_id

Line 3906: from bom_cto_oss_source_gt ossgt2

3902: where rcv_org_id is not null
3903: and valid_flag = 'Y'
3904: and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */
3905: 'x'
3906: from bom_cto_oss_source_gt ossgt2
3907: where ossgt1.line_id = ossgt2.line_id
3908: and ossgt2.rcv_org_id = ossgt1.rcv_org_id
3909: and ossgt2.source_type = 2
3910: and ossgt2.valid_flag = 'Y');

Line 3932: from bom_cto_oss_source_gt oss_src,

3928: -- oss_src.vendor_site_code,
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'

Line 3950: from bom_cto_oss_source_gt oss_src,

3946: -- oss_src.vendor_site_code,
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

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

3953: and bcol.option_specific is not null
3954: and oss_error_code is null
3955: and oss_src.valid_flag = 'Y'
3956: and oss_src.source_org_id is not null
3957: and oss_src.source_org_id not in (select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
3958: rcv_org_id
3959: from bom_cto_oss_source_gt oss_src1
3960: where oss_src1.line_id = oss_src.line_id
3961: and valid_flag = 'Y'

Line 3959: from bom_cto_oss_source_gt oss_src1

3955: and oss_src.valid_flag = 'Y'
3956: and oss_src.source_org_id is not null
3957: and oss_src.source_org_id not in (select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
3958: rcv_org_id
3959: from bom_cto_oss_source_gt oss_src1
3960: where oss_src1.line_id = oss_src.line_id
3961: and valid_flag = 'Y'
3962: )
3963: and nvl(bcol.option_specific,'4') <> '4'

Line 3973: from bom_cto_oss_source_gt oss_src,

3969: to_number(null),--3894241
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

Line 4639: Insert into bom_cto_oss_source_gt

4635: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Inside GET_SOURCING_DATA API',5);
4636: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Assignment set id ='||g_def_assg_set,5);
4637: End if;
4638:
4639: Insert into bom_cto_oss_source_gt
4640: (
4641: Inventory_item_id,
4642: Line_id,
4643: ato_line_id,

Line 4704: Insert into bom_cto_oss_source_gt

4700:
4701: If PG_DEBUG <> 0 Then
4702: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 1st sql ='||sql%rowcount,5);
4703: End if;
4704: Insert into bom_cto_oss_source_gt
4705: (
4706: Inventory_item_id,
4707: Line_id,
4708: ato_line_id,

Line 4775: Insert into bom_cto_oss_source_gt

4771: /*
4772:
4773: If p_config_creation = '3' Then
4774:
4775: Insert into bom_cto_oss_source_gt
4776: (
4777: Inventory_item_id,
4778: Line_id,
4779: config_item_id,

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

5213: l_stmt_num := 10;
5214: g_pg_level := g_pg_level + 3;
5215: x_return_status := FND_API.G_RET_STS_SUCCESS;
5216:
5217: delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
5218: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
5219:
5220: If PG_DEBUG <> 0 Then
5221: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Inside GET_ORDER_SOURCING_DATA API',5);

Line 5218: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;

5214: g_pg_level := g_pg_level + 3;
5215: x_return_status := FND_API.G_RET_STS_SUCCESS;
5216:
5217: delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
5218: from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
5219:
5220: If PG_DEBUG <> 0 Then
5221: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCNIG_DATA: Inside GET_ORDER_SOURCING_DATA API',5);
5222: End if;

Line 5286: Insert into bom_cto_oss_source_gt

5282: end if;
5283:
5284: l_stmt_num := 50;
5285: FORALL i in x_assg_list.assignment_id.first..x_assg_list.assignment_id.last
5286: Insert into bom_cto_oss_source_gt
5287: (
5288: Inventory_item_id,
5289: Line_id,
5290: ato_line_id,

Line 5351: Insert into bom_cto_oss_source_gt

5347: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting Model attribute 3 lines',5);
5348: End if;
5349:
5350: l_stmt_num := 60;
5351: Insert into bom_cto_oss_source_gt
5352: (
5353: Inventory_item_id,
5354: Line_id,
5355: ato_line_id,

Line 5420: Insert into bom_cto_oss_source_gt

5416: -- legs in the sourcing as valid. The valid_flag column in added
5417: -- and passed 'Y' value for all rows.
5418:
5419: l_stmt_num := 70;
5420: Insert into bom_cto_oss_source_gt
5421: (
5422: Inventory_item_id,
5423: Line_id,
5424: ato_line_id,

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

5684: i Number;
5685: l_buy_type Varchar2(1) := 'N';
5686:
5687: Cursor source_org_rule_cur is
5688: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5689: source_org_id,
5690: source_type
5691: from bom_cto_oss_source_gt oss_src
5692: where line_id = p_line_id

Line 5691: from bom_cto_oss_source_gt oss_src

5687: Cursor source_org_rule_cur is
5688: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5689: source_org_id,
5690: source_type
5691: from bom_cto_oss_source_gt oss_src
5692: where line_id = p_line_id
5693: and valid_flag = 'Y'
5694: and rcv_org_id = p_organization_id;
5695:

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

5693: and valid_flag = 'Y'
5694: and rcv_org_id = p_organization_id;
5695:
5696: Cursor source_item_rule_cur is
5697: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5698: source_org_id,
5699: source_type
5700: from bom_cto_oss_source_gt oss_src
5701: where line_id = p_line_id

Line 5700: from bom_cto_oss_source_gt oss_src

5696: Cursor source_item_rule_cur is
5697: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5698: source_org_id,
5699: source_type
5700: from bom_cto_oss_source_gt oss_src
5701: where line_id = p_line_id
5702: and valid_flag = 'Y'
5703: and rcv_org_id is null;
5704:

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

5872:
5873: Select org_id
5874: bulk collect into x_orgs_list
5875: from
5876: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5877: distinct nvl(source_org_id,rcv_org_id) org_id
5878: from bom_cto_oss_source_gt oss_src
5879: where line_id = p_line_id
5880: and valid_flag in( 'P','Y')

Line 5878: from bom_cto_oss_source_gt oss_src

5874: bulk collect into x_orgs_list
5875: from
5876: (Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5877: distinct nvl(source_org_id,rcv_org_id) org_id
5878: from bom_cto_oss_source_gt oss_src
5879: where line_id = p_line_id
5880: and valid_flag in( 'P','Y')
5881: and source_type in (2,3)
5882: union

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

5879: where line_id = p_line_id
5880: and valid_flag in( 'P','Y')
5881: and source_type in (2,3)
5882: union
5883: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5884: distinct source_org_id org_id
5885: from bom_cto_oss_source_gt oss_src
5886: where line_id = p_line_id
5887: and valid_flag in ('P','Y')

Line 5885: from bom_cto_oss_source_gt oss_src

5881: and source_type in (2,3)
5882: union
5883: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5884: distinct source_org_id org_id
5885: from bom_cto_oss_source_gt oss_src
5886: where line_id = p_line_id
5887: and valid_flag in ('P','Y')
5888: and source_org_id not in (
5889: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */

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

5885: from bom_cto_oss_source_gt oss_src
5886: where line_id = p_line_id
5887: and valid_flag in ('P','Y')
5888: and source_org_id not in (
5889: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5890: rcv_org_id
5891: from bom_cto_oss_source_gt oss_src
5892: where line_id = p_line_id
5893: and valid_flag in( 'P','Y')));

Line 5891: from bom_cto_oss_source_gt oss_src

5887: and valid_flag in ('P','Y')
5888: and source_org_id not in (
5889: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5890: rcv_org_id
5891: from bom_cto_oss_source_gt oss_src
5892: where line_id = p_line_id
5893: and valid_flag in( 'P','Y')));
5894: If PG_DEBUG <> 0 Then
5895: oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_BOM_ORGS: Number of orgs where bom should be created = '

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

5904:
5905: Procedure Print_source_gt(
5906: p_line_id IN Number) is
5907: cursor source_cur is
5908: select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
5909: inventory_item_id,
5910: rcv_org_id,
5911: source_org_id,
5912: customer_id,

Line 5921: from bom_cto_oss_source_gt oss_src

5917: source_type,
5918: reuse_flag,
5919: valid_flag,
5920: leaf_node
5921: from bom_cto_oss_source_gt oss_src
5922: where line_id = p_line_id;
5923:
5924: Begin
5925: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);

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

5921: from bom_cto_oss_source_gt oss_src
5922: where line_id = p_line_id;
5923:
5924: Begin
5925: oe_debug_pub.add('================PRINTING BOM_CTO_OSS_SOURCE_GT==================',5);
5926: oe_debug_pub.add('================ Line id = '||p_line_id||'======================',5);
5927: oe_debug_pub.add('Item id --- Rcv org --- src org --- customer --- vendor --- vend site --- rank --- alloc% --- src type --- reuse -- valid --- leaf ---',5);
5928: for source_rec in source_cur
5929: loop