[Home] [Help]
1025: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1026: FROM
1027: msc_sourcing_rules msr,
1028: msc_sr_receipt_org receiptorg,
1029: msc_sr_source_org sourceorg,
1030: msc_sr_assignments msa,
1031: msc_system_items msi,
1032: msc_ship_set_temp msst
1033: WHERE
1090: x_atp_sources.Ship_Method,
1091: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1092: FROM msc_sourcing_rules msr,
1093: msc_sr_receipt_org receiptorg,
1094: msc_sr_source_org sourceorg,
1095: msc_sr_assignments msa,
1096: msc_item_categories cat,
1097: msc_ship_set_temp msst
1098: WHERE msa.assignment_type = 2 and
1164: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1165: FROM
1166: msc_sourcing_rules msr,
1167: msc_sr_receipt_org receiptorg,
1168: msc_sr_source_org sourceorg,
1169: msc_sr_assignments msa,
1170: msc_system_items msi,
1171: msc_ship_set_temp msst
1172: WHERE
1228: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1229: FROM
1230: msc_sourcing_rules msr,
1231: msc_sr_receipt_org receiptorg,
1232: msc_sr_source_org sourceorg,
1233: msc_sr_assignments msa,
1234: msc_system_items msi,
1235: msc_ship_set_temp msst
1236: WHERE
1286: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1287:
1288: FROM msc_sourcing_rules msr,
1289: msc_sr_receipt_org receipt_org,
1290: msc_sr_source_org source_org,
1291: msc_sr_assignments msa,
1292: msc_system_items msi
1293: WHERE msa.assignment_type = 3 and
1294: msa.assignment_set_id = p_assign_set_id and
1310: msa.inventory_item_id = msi.inventory_item_id and
1311: msi.plan_id = -1
1312: ORDER BY rank asc, allocation_percent desc;
1313: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1314: -- and msc_sr_source_org (src) for org, instance as in certain cases
1315: -- join with msc_sr_assignments (msa) will bring no rows.
1316: -- Filter on item in msc_system_items.
1317:
1318: IF PG_DEBUG in ('Y', 'C') THEN
1346: x_atp_sources.Ship_Method,
1347: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1348: FROM msc_sourcing_rules msr,
1349: msc_sr_receipt_org receipt_org,
1350: msc_sr_source_org source_org,
1351: msc_sr_assignments msa,
1352: msc_item_categories cat
1353: WHERE msa.assignment_type = 2 and
1354: msa.assignment_set_id = p_assign_set_id and
1370: --AND NVL(source_org.circular_src, 2) <> 1
1371: --Bug4567833
1372: NVL(source_org.circular_src, 'N') <> 'Y'
1373: ORDER BY rank asc, allocation_percent desc;
1374: -- BUG 2283260 Additional join between msc_sr_source_org snd
1375: -- msc_item_categories to ensure that the source is valid for item.
1376: END IF;
1377:
1378: IF (x_atp_sources.organization_id.count = 0) and (p_organization_id is not null) then
1409: x_atp_sources.Ship_Method,
1410: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1411: FROM msc_sourcing_rules msr,
1412: msc_sr_receipt_org receipt_org,
1413: msc_sr_source_org source_org,
1414: msc_sr_assignments msa,
1415: msc_system_items msi
1416: WHERE msa.assignment_type = 1 and
1417: msa.assignment_set_id = p_assign_set_id and
1435: msi.inventory_item_id = l_inv_item_id and
1436: msi.plan_id = -1
1437: ORDER BY rank asc, allocation_percent desc;
1438: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1439: -- and msc_sr_source_org (src) for org, instance as in certain cases
1440: -- join with msc_sr_assignments (msa) will bring no rows.
1441: -- Filter on item in msc_system_items.
1442: END IF;
1443:
1471: x_atp_sources.Ship_Method,
1472: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1473: FROM msc_sourcing_rules msr,
1474: msc_sr_receipt_org receipt_org,
1475: msc_sr_source_org source_org,
1476: msc_sr_assignments msa,
1477: msc_system_items msi
1478: WHERE msa.assignment_type = 1 and
1479: msa.assignment_set_id = p_assign_set_id and
1495: msi.inventory_item_id = l_inv_item_id and
1496: msi.plan_id = -1
1497: ORDER BY rank asc, allocation_percent desc;
1498: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1499: -- and msc_sr_source_org (src) for org, instance as in certain cases
1500: -- join with msc_sr_assignments (msa) will bring no rows.
1501: -- Filter on item in msc_system_items.
1502: END IF;
1503:
5489: FROM
5490: MSC_SOURCING_RULES MSR,
5491: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5492: MSC_SR_ASSIGNMENTS MSRA,
5493: MSC_SR_SOURCE_ORG SOURCE_ORG
5494: WHERE
5495: MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
5496: AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
5497: AND MSR.STATUS = 1
5563: x_atp_sources.Ship_Method,
5564: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5565: FROM
5566: MSC_SYSTEM_ITEMS ITEM,
5567: MSC_SR_SOURCE_ORG SOURCE_ORG,
5568: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5569: MSC_SOURCING_RULES MSR,
5570: MSC_SR_ASSIGNMENTS MSRA,
5571: MSC_TP_SITE_ID_LID MTSIL
5625: SELECT NVL(MIN(2000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
5626: INTO l_min_region_value
5627: FROM
5628: MSC_SYSTEM_ITEMS ITEM_INNER,
5629: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
5630: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
5631: MSC_SOURCING_RULES MSR_INNER,
5632: MSC_SR_ASSIGNMENTS MSRA_INNER,
5633: MSC_REGIONS_TEMP MRT_INNER
5687: x_atp_sources.Ship_Method,
5688: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5689: FROM
5690: MSC_SYSTEM_ITEMS ITEM,
5691: MSC_SR_SOURCE_ORG SOURCE_ORG,
5692: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5693: MSC_SOURCING_RULES MSR,
5694: MSC_SR_ASSIGNMENTS MSRA,
5695: MSC_REGIONS_TEMP MRT
5770: x_atp_sources.Ship_Method,
5771: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5772: FROM
5773: MSC_ITEM_CATEGORIES CAT,
5774: MSC_SR_SOURCE_ORG SOURCE_ORG,
5775: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5776: MSC_SOURCING_RULES MSR,
5777: MSC_SR_ASSIGNMENTS MSRA,
5778: MSC_TP_SITE_ID_LID MTSIL
5853: x_atp_sources.Ship_Method,
5854: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5855: FROM
5856: MSC_SYSTEM_ITEMS ITEM,
5857: MSC_SR_SOURCE_ORG SOURCE_ORG,
5858: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5859: MSC_SOURCING_RULES MSR,
5860: -- MSC_TRADING_PARTNER_SITES TP,
5861: -- MSC_TP_SITE_ID_LID MTSIL,
5914: SELECT NVL(MIN(5000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
5915: INTO l_min_region_value
5916: FROM
5917: MSC_ITEM_CATEGORIES CAT_INNER,
5918: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
5919: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
5920: MSC_SOURCING_RULES MSR_INNER,
5921: MSC_SR_ASSIGNMENTS MSRA_INNER,
5922: MSC_REGIONS_TEMP MRT_INNER
5976: x_atp_sources.Ship_Method,
5977: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5978: FROM
5979: MSC_ITEM_CATEGORIES CAT,
5980: MSC_SR_SOURCE_ORG SOURCE_ORG,
5981: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5982: MSC_SOURCING_RULES MSR,
5983: MSC_SR_ASSIGNMENTS MSRA,
5984: MSC_REGIONS_TEMP MRT
6060: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6061: FROM
6062: MSC_ITEM_CATEGORIES CAT,
6063: -- MSC_TRADING_PARTNER_SITES TP,
6064: MSC_SR_SOURCE_ORG SOURCE_ORG,
6065: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6066: MSC_SOURCING_RULES MSR,
6067: MSC_SR_ASSIGNMENTS MSRA
6068: -- MSC_TP_SITE_ID_LID MTSIL
6142: x_atp_sources.Ship_Method,
6143: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6144: FROM
6145: MSC_SYSTEM_ITEMS ITEM,
6146: MSC_SR_SOURCE_ORG SOURCE_ORG,
6147: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6148: MSC_SOURCING_RULES MSR,
6149: MSC_SR_ASSIGNMENTS MSRA,
6150: MSC_TP_SITE_ID_LID MTSIL
6202: SELECT NVL(MIN(8000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 100) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
6203: INTO l_min_region_value
6204: FROM
6205: MSC_SYSTEM_ITEMS ITEM_INNER,
6206: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
6207: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
6208: MSC_SOURCING_RULES MSR_INNER,
6209: MSC_SR_ASSIGNMENTS MSRA_INNER,
6210: MSC_REGIONS_TEMP MRT_INNER
6263: x_atp_sources.Ship_Method,
6264: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6265: FROM
6266: MSC_SYSTEM_ITEMS ITEM,
6267: MSC_SR_SOURCE_ORG SOURCE_ORG,
6268: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6269: MSC_SOURCING_RULES MSR,
6270: MSC_SR_ASSIGNMENTS MSRA,
6271: MSC_REGIONS_TEMP MRT
6346: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6347: FROM
6348: MSC_SYSTEM_ITEMS ITEM,
6349: -- MSC_TRADING_PARTNER_SITES TP,
6350: MSC_SR_SOURCE_ORG SOURCE_ORG,
6351: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6352: MSC_SOURCING_RULES MSR,
6353: MSC_SR_ASSIGNMENTS MSRA
6354: -- MSC_TP_SITE_ID_LID MTSIL
6436: -1,
6437: '@@@'
6438: FROM
6439: MSC_SYSTEM_ITEMS ITEM,
6440: MSC_SR_SOURCE_ORG SOURCE_ORG,
6441: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6442: MSC_SOURCING_RULES MSR,
6443: MSC_SR_ASSIGNMENTS MSRA,
6444: MSC_TP_SITE_ID_LID MTSIL,
6572: l_inventory_item_id_arr,
6573: l_min_region_value_arr
6574: FROM
6575: MSC_SYSTEM_ITEMS ITEM_INNER,
6576: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
6577: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
6578: MSC_SOURCING_RULES MSR_INNER,
6579: MSC_SR_ASSIGNMENTS MSRA_INNER,
6580: MSC_REGIONS_TEMP MRT_INNER,
6646: -1,
6647: '@@@'
6648: FROM
6649: MSC_SYSTEM_ITEMS ITEM,
6650: MSC_SR_SOURCE_ORG SOURCE_ORG,
6651: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6652: MSC_SOURCING_RULES MSR,
6653: MSC_SR_ASSIGNMENTS MSRA,
6654: MSC_REGIONS_TEMP MRT,
6785: -1,
6786: '@@@'
6787: FROM
6788: MSC_ITEM_CATEGORIES CAT,
6789: MSC_SR_SOURCE_ORG SOURCE_ORG,
6790: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6791: MSC_SOURCING_RULES MSR,
6792: MSC_SR_ASSIGNMENTS MSRA,
6793: MSC_TP_SITE_ID_LID MTSIL,
6929: '@@@'
6930: FROM
6931: MSC_SYSTEM_ITEMS ITEM,
6932: -- MSC_TRADING_PARTNER_SITES TP,
6933: MSC_SR_SOURCE_ORG SOURCE_ORG,
6934: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6935: MSC_SOURCING_RULES MSR,
6936: MSC_SR_ASSIGNMENTS MSRA,
6937: -- MSC_TP_SITE_ID_LID MTSIL,
7062: l_inventory_item_id_arr,
7063: l_min_region_value_arr
7064: FROM
7065: MSC_ITEM_CATEGORIES CAT_INNER,
7066: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
7067: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
7068: MSC_SOURCING_RULES MSR_INNER,
7069: MSC_SR_ASSIGNMENTS MSRA_INNER,
7070: MSC_REGIONS_TEMP MRT_INNER,
7135: -1,
7136: '@@@'
7137: FROM
7138: MSC_ITEM_CATEGORIES CAT,
7139: MSC_SR_SOURCE_ORG SOURCE_ORG,
7140: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7141: MSC_SOURCING_RULES MSR,
7142: MSC_SR_ASSIGNMENTS MSRA,
7143: MSC_REGIONS_TEMP MRT,
7261: '@@@'
7262: FROM
7263: MSC_ITEM_CATEGORIES CAT,
7264: -- MSC_TRADING_PARTNER_SITES TP,
7265: MSC_SR_SOURCE_ORG SOURCE_ORG,
7266: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7267: MSC_SOURCING_RULES MSR,
7268: MSC_SR_ASSIGNMENTS MSRA,
7269: -- MSC_TP_SITE_ID_LID MTSIL,
7404: -1,
7405: '@@@'
7406: FROM
7407: MSC_SYSTEM_ITEMS ITEM,
7408: MSC_SR_SOURCE_ORG SOURCE_ORG,
7409: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7410: MSC_SOURCING_RULES MSR,
7411: MSC_SR_ASSIGNMENTS MSRA,
7412: MSC_TP_SITE_ID_LID MTSIL,
7537: l_inventory_item_id_arr,
7538: l_min_region_value_arr
7539: FROM
7540: MSC_SYSTEM_ITEMS ITEM_INNER,
7541: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
7542: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
7543: MSC_SOURCING_RULES MSR_INNER,
7544: MSC_SR_ASSIGNMENTS MSRA_INNER,
7545: MSC_REGIONS_TEMP MRT_INNER,
7608: -1,
7609: '@@@'
7610: FROM
7611: MSC_SYSTEM_ITEMS ITEM,
7612: MSC_SR_SOURCE_ORG SOURCE_ORG,
7613: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7614: MSC_SOURCING_RULES MSR,
7615: MSC_SR_ASSIGNMENTS MSRA,
7616: MSC_REGIONS_TEMP MRT,
7733: '@@@'
7734: FROM
7735: MSC_SYSTEM_ITEMS ITEM,
7736: -- MSC_TRADING_PARTNER_SITES TP,
7737: MSC_SR_SOURCE_ORG SOURCE_ORG,
7738: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7739: MSC_SOURCING_RULES MSR,
7740: MSC_SR_ASSIGNMENTS MSRA,
7741: -- MSC_TP_SITE_ID_LID MTSIL,