[Home] [Help]
1026: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1027: FROM
1028: msc_sourcing_rules msr,
1029: msc_sr_receipt_org receiptorg,
1030: msc_sr_source_org sourceorg,
1031: msc_sr_assignments msa,
1032: msc_system_items msi,
1033: msc_ship_set_temp msst
1034: WHERE
1091: x_atp_sources.Ship_Method,
1092: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1093: FROM msc_sourcing_rules msr,
1094: msc_sr_receipt_org receiptorg,
1095: msc_sr_source_org sourceorg,
1096: msc_sr_assignments msa,
1097: msc_item_categories cat,
1098: msc_ship_set_temp msst
1099: WHERE msa.assignment_type = 2 and
1165: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1166: FROM
1167: msc_sourcing_rules msr,
1168: msc_sr_receipt_org receiptorg,
1169: msc_sr_source_org sourceorg,
1170: msc_sr_assignments msa,
1171: msc_system_items msi,
1172: msc_ship_set_temp msst
1173: WHERE
1229: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1230: FROM
1231: msc_sourcing_rules msr,
1232: msc_sr_receipt_org receiptorg,
1233: msc_sr_source_org sourceorg,
1234: msc_sr_assignments msa,
1235: msc_system_items msi,
1236: msc_ship_set_temp msst
1237: WHERE
1287: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1288:
1289: FROM msc_sourcing_rules msr,
1290: msc_sr_receipt_org receipt_org,
1291: msc_sr_source_org source_org,
1292: msc_sr_assignments msa,
1293: msc_system_items msi
1294: WHERE msa.assignment_type = 3 and
1295: msa.assignment_set_id = p_assign_set_id and
1311: msa.inventory_item_id = msi.inventory_item_id and
1312: msi.plan_id = -1
1313: ORDER BY rank asc, allocation_percent desc;
1314: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1315: -- and msc_sr_source_org (src) for org, instance as in certain cases
1316: -- join with msc_sr_assignments (msa) will bring no rows.
1317: -- Filter on item in msc_system_items.
1318:
1319: IF PG_DEBUG in ('Y', 'C') THEN
1347: x_atp_sources.Ship_Method,
1348: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1349: FROM msc_sourcing_rules msr,
1350: msc_sr_receipt_org receipt_org,
1351: msc_sr_source_org source_org,
1352: msc_sr_assignments msa,
1353: msc_item_categories cat
1354: WHERE msa.assignment_type = 2 and
1355: msa.assignment_set_id = p_assign_set_id and
1371: --AND NVL(source_org.circular_src, 2) <> 1
1372: --Bug4567833
1373: NVL(source_org.circular_src, 'N') <> 'Y'
1374: ORDER BY rank asc, allocation_percent desc;
1375: -- BUG 2283260 Additional join between msc_sr_source_org snd
1376: -- msc_item_categories to ensure that the source is valid for item.
1377: END IF;
1378:
1379: IF (x_atp_sources.organization_id.count = 0) and (p_organization_id is not null) then
1410: x_atp_sources.Ship_Method,
1411: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1412: FROM msc_sourcing_rules msr,
1413: msc_sr_receipt_org receipt_org,
1414: msc_sr_source_org source_org,
1415: msc_sr_assignments msa,
1416: msc_system_items msi
1417: WHERE msa.assignment_type = 1 and
1418: msa.assignment_set_id = p_assign_set_id and
1436: msi.inventory_item_id = l_inv_item_id and
1437: msi.plan_id = -1
1438: ORDER BY rank asc, allocation_percent desc;
1439: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1440: -- and msc_sr_source_org (src) for org, instance as in certain cases
1441: -- join with msc_sr_assignments (msa) will bring no rows.
1442: -- Filter on item in msc_system_items.
1443: END IF;
1444:
1472: x_atp_sources.Ship_Method,
1473: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
1474: FROM msc_sourcing_rules msr,
1475: msc_sr_receipt_org receipt_org,
1476: msc_sr_source_org source_org,
1477: msc_sr_assignments msa,
1478: msc_system_items msi
1479: WHERE msa.assignment_type = 1 and
1480: msa.assignment_set_id = p_assign_set_id and
1496: msi.inventory_item_id = l_inv_item_id and
1497: msi.plan_id = -1
1498: ORDER BY rank asc, allocation_percent desc;
1499: -- BUG 2283260 Note that join happens between msc_system_items(msi)
1500: -- and msc_sr_source_org (src) for org, instance as in certain cases
1501: -- join with msc_sr_assignments (msa) will bring no rows.
1502: -- Filter on item in msc_system_items.
1503: END IF;
1504:
5500: FROM
5501: MSC_SOURCING_RULES MSR,
5502: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5503: MSC_SR_ASSIGNMENTS MSRA,
5504: MSC_SR_SOURCE_ORG SOURCE_ORG
5505: WHERE
5506: MSRA.ASSIGNMENT_SET_ID = p_assignment_set_id
5507: AND MSRA.SOURCING_RULE_ID = MSR.SOURCING_RULE_ID
5508: AND MSR.STATUS = 1
5574: x_atp_sources.Ship_Method,
5575: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5576: FROM
5577: MSC_SYSTEM_ITEMS ITEM,
5578: MSC_SR_SOURCE_ORG SOURCE_ORG,
5579: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5580: MSC_SOURCING_RULES MSR,
5581: MSC_SR_ASSIGNMENTS MSRA,
5582: MSC_TP_SITE_ID_LID MTSIL
5636: SELECT NVL(MIN(2000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
5637: INTO l_min_region_value
5638: FROM
5639: MSC_SYSTEM_ITEMS ITEM_INNER,
5640: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
5641: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
5642: MSC_SOURCING_RULES MSR_INNER,
5643: MSC_SR_ASSIGNMENTS MSRA_INNER,
5644: MSC_REGIONS_TEMP MRT_INNER
5698: x_atp_sources.Ship_Method,
5699: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5700: FROM
5701: MSC_SYSTEM_ITEMS ITEM,
5702: MSC_SR_SOURCE_ORG SOURCE_ORG,
5703: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5704: MSC_SOURCING_RULES MSR,
5705: MSC_SR_ASSIGNMENTS MSRA,
5706: MSC_REGIONS_TEMP MRT
5781: x_atp_sources.Ship_Method,
5782: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5783: FROM
5784: MSC_ITEM_CATEGORIES CAT,
5785: MSC_SR_SOURCE_ORG SOURCE_ORG,
5786: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5787: MSC_SOURCING_RULES MSR,
5788: MSC_SR_ASSIGNMENTS MSRA,
5789: MSC_TP_SITE_ID_LID MTSIL
5864: x_atp_sources.Ship_Method,
5865: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5866: FROM
5867: MSC_SYSTEM_ITEMS ITEM,
5868: MSC_SR_SOURCE_ORG SOURCE_ORG,
5869: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5870: MSC_SOURCING_RULES MSR,
5871: -- MSC_TRADING_PARTNER_SITES TP,
5872: -- MSC_TP_SITE_ID_LID MTSIL,
5925: SELECT NVL(MIN(5000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 10) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
5926: INTO l_min_region_value
5927: FROM
5928: MSC_ITEM_CATEGORIES CAT_INNER,
5929: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
5930: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
5931: MSC_SOURCING_RULES MSR_INNER,
5932: MSC_SR_ASSIGNMENTS MSRA_INNER,
5933: MSC_REGIONS_TEMP MRT_INNER
5987: x_atp_sources.Ship_Method,
5988: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
5989: FROM
5990: MSC_ITEM_CATEGORIES CAT,
5991: MSC_SR_SOURCE_ORG SOURCE_ORG,
5992: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
5993: MSC_SOURCING_RULES MSR,
5994: MSC_SR_ASSIGNMENTS MSRA,
5995: MSC_REGIONS_TEMP MRT
6071: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6072: FROM
6073: MSC_ITEM_CATEGORIES CAT,
6074: -- MSC_TRADING_PARTNER_SITES TP,
6075: MSC_SR_SOURCE_ORG SOURCE_ORG,
6076: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6077: MSC_SOURCING_RULES MSR,
6078: MSC_SR_ASSIGNMENTS MSRA
6079: -- MSC_TP_SITE_ID_LID MTSIL
6153: x_atp_sources.Ship_Method,
6154: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6155: FROM
6156: MSC_SYSTEM_ITEMS ITEM,
6157: MSC_SR_SOURCE_ORG SOURCE_ORG,
6158: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6159: MSC_SOURCING_RULES MSR,
6160: MSC_SR_ASSIGNMENTS MSRA,
6161: MSC_TP_SITE_ID_LID MTSIL
6213: SELECT NVL(MIN(8000 + ((10 - NVL(MRT_INNER.REGION_TYPE, 0)) * 100) + DECODE(MRT_INNER.ZONE_FLAG, 'Y', 1, 0)), 0)
6214: INTO l_min_region_value
6215: FROM
6216: MSC_SYSTEM_ITEMS ITEM_INNER,
6217: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
6218: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
6219: MSC_SOURCING_RULES MSR_INNER,
6220: MSC_SR_ASSIGNMENTS MSRA_INNER,
6221: MSC_REGIONS_TEMP MRT_INNER
6274: x_atp_sources.Ship_Method,
6275: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6276: FROM
6277: MSC_SYSTEM_ITEMS ITEM,
6278: MSC_SR_SOURCE_ORG SOURCE_ORG,
6279: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6280: MSC_SOURCING_RULES MSR,
6281: MSC_SR_ASSIGNMENTS MSRA,
6282: MSC_REGIONS_TEMP MRT
6357: x_atp_sources.Sup_Cap_Type -- For supplier intransit LT project
6358: FROM
6359: MSC_SYSTEM_ITEMS ITEM,
6360: -- MSC_TRADING_PARTNER_SITES TP,
6361: MSC_SR_SOURCE_ORG SOURCE_ORG,
6362: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6363: MSC_SOURCING_RULES MSR,
6364: MSC_SR_ASSIGNMENTS MSRA
6365: -- MSC_TP_SITE_ID_LID MTSIL
6447: -1,
6448: '@@@'
6449: FROM
6450: MSC_SYSTEM_ITEMS ITEM,
6451: MSC_SR_SOURCE_ORG SOURCE_ORG,
6452: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6453: MSC_SOURCING_RULES MSR,
6454: MSC_SR_ASSIGNMENTS MSRA,
6455: MSC_TP_SITE_ID_LID MTSIL,
6583: l_inventory_item_id_arr,
6584: l_min_region_value_arr
6585: FROM
6586: MSC_SYSTEM_ITEMS ITEM_INNER,
6587: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
6588: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
6589: MSC_SOURCING_RULES MSR_INNER,
6590: MSC_SR_ASSIGNMENTS MSRA_INNER,
6591: MSC_REGIONS_TEMP MRT_INNER,
6657: -1,
6658: '@@@'
6659: FROM
6660: MSC_SYSTEM_ITEMS ITEM,
6661: MSC_SR_SOURCE_ORG SOURCE_ORG,
6662: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6663: MSC_SOURCING_RULES MSR,
6664: MSC_SR_ASSIGNMENTS MSRA,
6665: MSC_REGIONS_TEMP MRT,
6796: -1,
6797: '@@@'
6798: FROM
6799: MSC_ITEM_CATEGORIES CAT,
6800: MSC_SR_SOURCE_ORG SOURCE_ORG,
6801: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6802: MSC_SOURCING_RULES MSR,
6803: MSC_SR_ASSIGNMENTS MSRA,
6804: MSC_TP_SITE_ID_LID MTSIL,
6940: '@@@'
6941: FROM
6942: MSC_SYSTEM_ITEMS ITEM,
6943: -- MSC_TRADING_PARTNER_SITES TP,
6944: MSC_SR_SOURCE_ORG SOURCE_ORG,
6945: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
6946: MSC_SOURCING_RULES MSR,
6947: MSC_SR_ASSIGNMENTS MSRA,
6948: -- MSC_TP_SITE_ID_LID MTSIL,
7073: l_inventory_item_id_arr,
7074: l_min_region_value_arr
7075: FROM
7076: MSC_ITEM_CATEGORIES CAT_INNER,
7077: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
7078: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
7079: MSC_SOURCING_RULES MSR_INNER,
7080: MSC_SR_ASSIGNMENTS MSRA_INNER,
7081: MSC_REGIONS_TEMP MRT_INNER,
7146: -1,
7147: '@@@'
7148: FROM
7149: MSC_ITEM_CATEGORIES CAT,
7150: MSC_SR_SOURCE_ORG SOURCE_ORG,
7151: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7152: MSC_SOURCING_RULES MSR,
7153: MSC_SR_ASSIGNMENTS MSRA,
7154: MSC_REGIONS_TEMP MRT,
7272: '@@@'
7273: FROM
7274: MSC_ITEM_CATEGORIES CAT,
7275: -- MSC_TRADING_PARTNER_SITES TP,
7276: MSC_SR_SOURCE_ORG SOURCE_ORG,
7277: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7278: MSC_SOURCING_RULES MSR,
7279: MSC_SR_ASSIGNMENTS MSRA,
7280: -- MSC_TP_SITE_ID_LID MTSIL,
7415: -1,
7416: '@@@'
7417: FROM
7418: MSC_SYSTEM_ITEMS ITEM,
7419: MSC_SR_SOURCE_ORG SOURCE_ORG,
7420: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7421: MSC_SOURCING_RULES MSR,
7422: MSC_SR_ASSIGNMENTS MSRA,
7423: MSC_TP_SITE_ID_LID MTSIL,
7548: l_inventory_item_id_arr,
7549: l_min_region_value_arr
7550: FROM
7551: MSC_SYSTEM_ITEMS ITEM_INNER,
7552: MSC_SR_SOURCE_ORG SOURCE_ORG_INNER,
7553: MSC_SR_RECEIPT_ORG RECEIPT_ORG_INNER,
7554: MSC_SOURCING_RULES MSR_INNER,
7555: MSC_SR_ASSIGNMENTS MSRA_INNER,
7556: MSC_REGIONS_TEMP MRT_INNER,
7619: -1,
7620: '@@@'
7621: FROM
7622: MSC_SYSTEM_ITEMS ITEM,
7623: MSC_SR_SOURCE_ORG SOURCE_ORG,
7624: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7625: MSC_SOURCING_RULES MSR,
7626: MSC_SR_ASSIGNMENTS MSRA,
7627: MSC_REGIONS_TEMP MRT,
7744: '@@@'
7745: FROM
7746: MSC_SYSTEM_ITEMS ITEM,
7747: -- MSC_TRADING_PARTNER_SITES TP,
7748: MSC_SR_SOURCE_ORG SOURCE_ORG,
7749: MSC_SR_RECEIPT_ORG RECEIPT_ORG,
7750: MSC_SOURCING_RULES MSR,
7751: MSC_SR_ASSIGNMENTS MSRA,
7752: -- MSC_TP_SITE_ID_LID MTSIL,