[Home] [Help]
401: cs.company_site_id,
402: c1.company_id,
403: cs1.company_site_id,
404: ln.inventory_item_id
405: FROM msc_companies c,
406: msc_company_sites cs,
407: msc_companies c1,
408: msc_company_sites cs1,
409: fnd_lookup_values flv,
403: cs1.company_site_id,
404: ln.inventory_item_id
405: FROM msc_companies c,
406: msc_company_sites cs,
407: msc_companies c1,
408: msc_company_sites cs1,
409: fnd_lookup_values flv,
410: msc_supdem_lines_interface ln
411: WHERE ln.parent_header_id = p_header_id
430: cs.company_site_id,
431: c1.company_id,
432: cs1.company_site_id,
433: ln.inventory_item_id
434: FROM msc_companies c,
435: msc_company_sites cs,
436: msc_companies c1,
437: msc_company_sites cs1,
438: fnd_lookup_values flv,
432: cs1.company_site_id,
433: ln.inventory_item_id
434: FROM msc_companies c,
435: msc_company_sites cs,
436: msc_companies c1,
437: msc_company_sites cs1,
438: fnd_lookup_values flv,
439: msc_supdem_lines_interface ln
440: WHERE ln.parent_header_id = p_header_id
584: WHERE ln.parent_header_id = p_header_id AND
585: ln.line_id = t_line_id(j) AND
586: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
587: NOT EXISTS (SELECT c.company_id
588: FROM msc_companies c
589: WHERE UPPER(c.company_name) = UPPER(NVL(ln.posting_party_name, G_NULL_STRING)));
590:
591: --=======================================================================
592: -- Validation: Check if the sync indicator is valid
678: ln.line_id = t_line_id(j) AND
679: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
680: ln.publisher_company IS NOT NULL AND
681: NOT EXISTS (SELECT c.company_id
682: FROM msc_companies c
683: WHERE UPPER(c.company_name) = UPPER(ln.publisher_company));
684:
685: --=============================================================================
686: -- Validate the company to be consistent if more than two feild has been given
1070: Upper(ln.sync_indicator) = 'D' AND
1071: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1072: NOT EXISTS (SELECT cs.company_site_id
1073: FROM msc_company_sites cs,
1074: msc_companies c
1075: WHERE UPPER(c.company_name) = UPPER(ln.publisher_company) AND
1076: c.company_id = cs.company_id AND
1077: UPPER(cs.company_site_name) = UPPER(ln.publisher_site));
1078:
1088: Upper(ln.sync_indicator) = 'D' AND
1089: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1090: ln.customer_company IS NOT NULL AND
1091: NOT EXISTS (SELECT c.company_id
1092: FROM msc_companies c,
1093: msc_companies c1,
1094: msc_company_relationships r
1095: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
1096: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1089: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1090: ln.customer_company IS NOT NULL AND
1091: NOT EXISTS (SELECT c.company_id
1092: FROM msc_companies c,
1093: msc_companies c1,
1094: msc_company_relationships r
1095: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
1096: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1097: r.subject_id = c1.company_id and
1111: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1112: ln.customer_company IS NOT NULL AND
1113: NOT EXISTS (SELECT cs.company_site_id
1114: FROM msc_company_sites cs,
1115: msc_companies c
1116: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) AND
1117: c.company_id = cs.company_id AND
1118: UPPER(cs.company_site_name) = UPPER(NVL(ln.customer_site,
1119: G_NULL_STRING)));
1130: Upper(ln.sync_indicator) = 'D' AND
1131: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1132: ln.supplier_company IS NOT NULL AND
1133: NOT EXISTS (SELECT c.company_id
1134: FROM msc_companies c,
1135: msc_companies c1,
1136: msc_company_relationships r
1137: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
1138: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1131: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1132: ln.supplier_company IS NOT NULL AND
1133: NOT EXISTS (SELECT c.company_id
1134: FROM msc_companies c,
1135: msc_companies c1,
1136: msc_company_relationships r
1137: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
1138: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1139: r.subject_id = c1.company_id and
1153: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1154: ln.supplier_company IS NOT NULL AND
1155: NOT EXISTS (SELECT cs.company_site_id
1156: FROM msc_company_sites cs,
1157: msc_companies c
1158: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) AND
1159: c.company_id = cs.company_id AND
1160: UPPER(cs.company_site_name) = UPPER(NVL(ln.supplier_site,
1161: G_NULL_STRING)));
1318: ln.line_id = t_line_id(j) AND
1319: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1320: ln.ship_from_party_name IS NOT NULL AND
1321: NOT EXISTS (SELECT c.company_id
1322: FROM msc_companies c
1323: WHERE UPPER(c.company_name) = UPPER(ln.ship_from_party_name));
1324:
1325: --========================================================
1326: -- Validation: Check if ship to company exists
1333: ln.line_id = t_line_id(j) AND
1334: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1335: ln.ship_to_party_name IS NOT NULL AND
1336: NOT EXISTS (SELECT c.company_id
1337: FROM msc_companies c
1338: WHERE UPPER(c.company_name) = UPPER(ln.ship_to_party_name));
1339:
1340: --========================================================
1341: -- Validation: Check if end order publisher exists
1348: ln.line_id = t_line_id(j) AND
1349: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1350: ln.end_order_publisher_name IS NOT NULL AND
1351: NOT EXISTS (SELECT c.company_id
1352: FROM msc_companies c
1353: WHERE UPPER(c.company_name) = UPPER(ln.end_order_publisher_name));
1354:
1355: --========================================================
1356: -- Validation: Check if end order type is valid
1680: ln.line_id = t_line_id1(j) AND
1681: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1682: ln.publisher_company IS NOT NULL AND
1683: NOT EXISTS (SELECT c.company_id
1684: FROM msc_companies c
1685: WHERE UPPER(c.company_name) = UPPER(ln.publisher_company));
1686:
1687: end if; -- if t_line_id1
1688: end if; -- if MOE
1698: ln.line_id = t_line_id(j) AND
1699: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1700: NOT EXISTS (SELECT cs.company_site_id
1701: FROM msc_company_sites cs,
1702: msc_companies c
1703: WHERE UPPER(c.company_name) = UPPER(ln.publisher_company) AND
1704: c.company_id = cs.company_id AND
1705: UPPER(cs.company_site_name) = UPPER(ln.publisher_site));
1706:
1768: ln.line_id = t_line_id(j) AND
1769: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1770: ln.customer_company IS NOT NULL AND
1771: NOT EXISTS (SELECT c.company_id
1772: FROM msc_companies c,
1773: msc_companies c1,
1774: msc_company_relationships r
1775: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
1776: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company,
1769: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1770: ln.customer_company IS NOT NULL AND
1771: NOT EXISTS (SELECT c.company_id
1772: FROM msc_companies c,
1773: msc_companies c1,
1774: msc_company_relationships r
1775: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) and
1776: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company,
1777: ln.posting_party_name)) and
1791: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1792: ln.customer_company IS NOT NULL AND
1793: NOT EXISTS (SELECT cs.company_site_id
1794: FROM msc_company_sites cs,
1795: msc_companies c
1796: WHERE UPPER(c.company_name) = UPPER(ln.customer_company) AND
1797: c.company_id = cs.company_id AND
1798: UPPER(cs.company_site_name) = UPPER(NVL(ln.customer_site,
1799: G_NULL_STRING)));
1809: ln.line_id = t_line_id(j) AND
1810: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1811: ln.supplier_company IS NOT NULL AND
1812: NOT EXISTS (SELECT c.company_id
1813: FROM msc_companies c,
1814: msc_companies c1,
1815: msc_company_relationships r
1816: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
1817: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1810: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1811: ln.supplier_company IS NOT NULL AND
1812: NOT EXISTS (SELECT c.company_id
1813: FROM msc_companies c,
1814: msc_companies c1,
1815: msc_company_relationships r
1816: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) and
1817: UPPER(c1.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) and
1818: r.subject_id = c1.company_id and
1831: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1832: ln.supplier_company IS NOT NULL AND
1833: NOT EXISTS (SELECT cs.company_site_id
1834: FROM msc_company_sites cs,
1835: msc_companies c
1836: WHERE UPPER(c.company_name) = UPPER(ln.supplier_company) AND
1837: c.company_id = cs.company_id AND
1838: UPPER(cs.company_site_name) = UPPER(NVL(ln.supplier_site,
1839: G_NULL_STRING)));
1850: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1851: ln.ship_from_party_name IS NOT NULL AND
1852: NOT EXISTS (SELECT cs.company_site_id
1853: FROM msc_company_sites cs,
1854: msc_companies c
1855: WHERE UPPER(c.company_name) = UPPER(ln.ship_from_party_name) AND
1856: c.company_id = cs.company_id AND
1857: UPPER(cs.company_site_name) = UPPER(NVL(ln.ship_from_party_site, G_NULL_STRING)));
1858:
1869: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1870: ln.ship_to_party_name IS NOT NULL AND
1871: NOT EXISTS (SELECT cs.company_site_id
1872: FROM msc_company_sites cs,
1873: msc_companies c
1874: WHERE UPPER(c.company_name) = UPPER(ln.ship_to_party_name) AND
1875: c.company_id = cs.company_id AND
1876: UPPER(cs.company_site_name) = UPPER(NVL(ln.ship_to_party_site, G_NULL_STRING)));
1877:
1887: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1888: ln.end_order_publisher_name IS NOT NULL AND
1889: NOT EXISTS (SELECT cs.company_site_id
1890: FROM msc_company_sites cs,
1891: msc_companies c
1892: WHERE UPPER(c.company_name) = UPPER(ln.end_order_publisher_name) AND
1893: c.company_id = cs.company_id AND
1894: UPPER(cs.company_site_name) = UPPER(NVL(ln.end_order_publisher_site, G_NULL_STRING)));
1895:
1912: from msc_system_items msi,
1913: msc_trading_partners part,
1914: msc_trading_partner_maps map,
1915: msc_company_sites cs,
1916: msc_companies c
1917: where msi.plan_id = -1 and
1918: msi.item_name = nvl(ln.owner_item_name,
1919: nvl(ln.customer_item_name,
1920: ln.supplier_item_name)) and
1953: msc_trading_partner_maps map1,
1954: msc_trading_partner_maps map2,
1955: msc_company_relationships r,
1956: msc_company_sites cs,
1957: msc_companies c,
1958: msc_company_sites cs1
1959: where mis.plan_id = -1 and
1960: mis.supplier_item_name = nvl(ln.owner_item_name,
1961: ln.supplier_item_name) and
1990: msc_trading_partner_maps map,
1991: msc_trading_partner_maps map1,
1992: msc_company_relationships r,
1993: msc_company_sites cs,
1994: msc_companies c
1995: where mic.plan_id = -1 and
1996: mic.customer_item_name = nvl(ln.owner_item_name,
1997: ln.customer_item_name) and
1998: mic.customer_id = map.tp_key and
2033: from msc_system_items msi,
2034: msc_trading_partners part,
2035: msc_trading_partner_maps map,
2036: msc_company_sites cs,
2037: msc_companies c
2038: where msi.plan_id = -1 and
2039: msi.item_name = ln.item_name and
2040: msi.organization_id = part.sr_tp_id and
2041: msi.sr_instance_id = part.sr_instance_id and
2048: UPPER(c.company_name) = upper(ln.publisher_company) and
2049: c.company_id = 1 and
2050: NVL(part.company_id,1) = c.company_id
2051: union select 1
2052: from msc_companies c
2053: where c.company_id <> 1 and G_CONS_ADVICE <> t_order_type(j) and
2054: UPPER(c.company_name) = upper(ln.publisher_company)
2055: /* For Consumption Advice, Item should be valid in the Org modelled as the customer (publisher) */
2056: union select 1
2057: from msc_system_items msi,
2058: msc_trading_partners part,
2059: msc_trading_partner_maps map,
2060: msc_company_sites cs,
2061: msc_companies c
2062: where
2063: msi.plan_id = -1 and
2064: msi.item_name = ln.item_name and
2065: msi.organization_id = part.sr_tp_id and
2093: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2094: UPPER(flv.meaning) = UPPER(ln.order_type) AND
2095: flv.language = p_language)) AND
2096: exists (SELECT 'exists'
2097: FROM msc_companies c0,
2098: msc_company_sites s0,
2099: msc_trading_partners mtp,
2100: msc_trading_partners mtp1,
2101: msc_trading_partner_maps maps,
2102: msc_trading_partner_maps maps1,
2103: msc_trading_partner_maps maps2,
2104: msc_trading_partner_sites mtps,
2105: msc_company_sites cs,
2106: msc_companies c,
2107: msc_company_relationships rel
2108: WHERE rel.relationship_type = 2
2109: AND rel.subject_id = 1
2110: AND Upper(c0.company_name) = Upper(ln.publisher_company)
2138: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2139: UPPER(flv.meaning) = UPPER(ln.order_type) AND
2140: flv.language = p_language)) AND
2141: exists( select 'exist'
2142: from msc_companies c,
2143: msc_company_sites s,
2144: msc_trading_partner_maps m,
2145: msc_trading_partners t
2146: where upper(c.company_name) = upper(ln.publisher_company)
2292: G_SALES_FORECAST,
2293: G_SALES_ORDER))
2294:
2295: AND EXISTS ( SELECT ln.customer_company
2296: FROM msc_companies c
2297: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
2298: AND c.company_id = 1
2299: )
2300: ;
2314: G_ASN,
2315: ---G_SALES_FORECAST,
2316: G_SALES_ORDER))
2317: AND EXISTS ( SELECT ln.customer_company
2318: FROM msc_companies c
2319: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
2320: AND c.company_id = 1
2321: );
2322:
2333: WHERE flv.language = p_language AND
2334: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2335: flv.lookup_code = G_SALES_FORECAST)
2336: AND EXISTS ( SELECT ln.customer_company
2337: FROM msc_companies c
2338: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
2339: AND c.company_id = 1
2340: );
2341: -- jguo: change ends here
2449: from msc_system_items msi,
2450: msc_trading_partners part,
2451: msc_trading_partner_maps map,
2452: msc_company_sites cs,
2453: msc_companies c,
2454: msc_supdem_lines_interface ln
2455: where msi.plan_id = -1 and
2456: ln.parent_header_id = p_header_id AND
2457: ln.line_id = t_line_id(j) AND
2492: msc_trading_partner_maps map,
2493: msc_trading_partner_maps map1,
2494: msc_company_relationships r,
2495: msc_company_sites cs,
2496: msc_companies c,
2497: msc_supdem_lines_interface ln
2498: where mis.plan_id = -1 and
2499: ln.parent_header_id = p_header_id AND
2500: ln.line_id = t_line_id(j) AND
2526: msc_trading_partner_maps map,
2527: msc_trading_partner_maps map1,
2528: msc_company_relationships r,
2529: msc_company_sites cs,
2530: msc_companies c,
2531: msc_supdem_lines_interface ln
2532: where mic.plan_id = -1 and
2533: ln.parent_header_id = p_header_id AND
2534: ln.line_id = t_line_id(j) AND
3208: p_date_format VARCHAR2
3209: ) IS
3210: select sd.transaction_id
3211: from msc_supdem_lines_interface ln,
3212: msc_companies c,
3213: msc_company_sites s ,
3214: fnd_lookup_values flv,
3215: msc_sup_dem_entries sd
3216: where ln.parent_header_id = p_header_id and
3268: AND Nvl(sd.last_update_login,-1) <> G_DELETED
3269: UNION
3270: select sd.transaction_id
3271: from msc_supdem_lines_interface ln,
3272: msc_companies c,
3273: msc_company_sites s ,
3274: fnd_lookup_values flv,
3275: msc_sup_dem_entries sd
3276: where ln.parent_header_id = p_header_id and
3315: AND Nvl(sd.last_update_login,-1) <> G_DELETED
3316: UNION
3317: select sd.transaction_id
3318: from msc_supdem_lines_interface ln,
3319: msc_companies c,
3320: msc_company_sites s ,
3321: fnd_lookup_values flv,
3322: msc_sup_dem_entries sd
3323: where ln.parent_header_id = p_header_id and
3563: ln.posting_party_name
3564: FROM msc_supdem_lines_interface ln,
3565: fnd_lookup_values flv,
3566: fnd_lookup_values flv1,
3567: msc_companies c,
3568: msc_company_sites s
3569: WHERE ln.parent_header_id = p_header_id AND
3570: ln.line_id between p_start_line and p_end_line AND
3571: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
3683: ln.posting_party_name
3684: FROM msc_supdem_lines_interface ln,
3685: fnd_lookup_values flv,
3686: fnd_lookup_values flv1,
3687: msc_companies c,
3688: msc_company_sites s,
3689: msc_companies c1,
3690: msc_company_sites s1,
3691: msc_company_relationships r
3685: fnd_lookup_values flv,
3686: fnd_lookup_values flv1,
3687: msc_companies c,
3688: msc_company_sites s,
3689: msc_companies c1,
3690: msc_company_sites s1,
3691: msc_company_relationships r
3692: WHERE ln.parent_header_id = p_header_id AND
3693: ln.line_id between p_start_line and p_end_line AND
3804: ln.attribute15,
3805: ln.posting_party_name
3806: FROM msc_supdem_lines_interface ln,
3807: fnd_lookup_values flv1,
3808: msc_companies c,
3809: msc_company_sites s
3810: WHERE ln.parent_header_id = p_header_id AND
3811: ln.line_id between p_start_line and p_end_line AND
3812: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
3919: ln.attribute15,
3920: ln.posting_party_name
3921: FROM msc_supdem_lines_interface ln,
3922: fnd_lookup_values flv1,
3923: msc_companies c,
3924: msc_company_sites s,
3925: msc_companies c1,
3926: msc_company_sites s1,
3927: msc_company_relationships r
3921: FROM msc_supdem_lines_interface ln,
3922: fnd_lookup_values flv1,
3923: msc_companies c,
3924: msc_company_sites s,
3925: msc_companies c1,
3926: msc_company_sites s1,
3927: msc_company_relationships r
3928: WHERE ln.parent_header_id = p_header_id AND
3929: ln.line_id between p_start_line and p_end_line AND
4037: ln.attribute15,
4038: ln.posting_party_name
4039: FROM msc_supdem_lines_interface ln,
4040: fnd_lookup_values flv1,
4041: msc_companies c,
4042: msc_company_sites s
4043: WHERE ln.parent_header_id = p_header_id AND
4044: ln.line_id between p_start_line and p_end_line AND
4045: ln.bucket_type IS NULL AND
4237: FROM msc_supdem_lines_interface ln,
4238: MSC_LOAD_BUCKETS_TEMP mlb,
4239: fnd_lookup_values flv,
4240: fnd_lookup_values flv1,
4241: msc_companies c,
4242: msc_company_sites s,
4243: msc_companies c1,
4244: msc_company_sites s1,
4245: msc_company_relationships r
4239: fnd_lookup_values flv,
4240: fnd_lookup_values flv1,
4241: msc_companies c,
4242: msc_company_sites s,
4243: msc_companies c1,
4244: msc_company_sites s1,
4245: msc_company_relationships r
4246: WHERE ln.parent_header_id = p_header_id AND
4247: ln.line_id between p_start_line and p_end_line AND
4379: FROM msc_supdem_lines_interface ln,
4380: MSC_LOAD_BUCKETS_TEMP mlb,
4381: fnd_lookup_values flv,
4382: fnd_lookup_values flv1,
4383: msc_companies c,
4384: msc_company_sites s
4385: WHERE ln.parent_header_id = p_header_id AND
4386: ln.line_id between p_start_line and p_end_line AND
4387: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
4581: FROM msc_supdem_lines_interface ln,
4582: MSC_LOAD_BUCKETS_TEMP mlb,
4583: fnd_lookup_values flv,
4584: fnd_lookup_values flv1,
4585: msc_companies c,
4586: msc_company_sites s,
4587: msc_companies c1,
4588: msc_company_sites s1,
4589: msc_company_relationships r
4583: fnd_lookup_values flv,
4584: fnd_lookup_values flv1,
4585: msc_companies c,
4586: msc_company_sites s,
4587: msc_companies c1,
4588: msc_company_sites s1,
4589: msc_company_relationships r
4590: WHERE ln.parent_header_id = p_header_id AND
4591: ln.line_id between p_start_line and p_end_line AND
4723: FROM msc_supdem_lines_interface ln,
4724: MSC_LOAD_BUCKETS_TEMP mlb,
4725: fnd_lookup_values flv,
4726: fnd_lookup_values flv1,
4727: msc_companies c,
4728: msc_company_sites s
4729: WHERE ln.parent_header_id = p_header_id AND
4730: ln.line_id between p_start_line and p_end_line AND
4731: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
4925: FROM msc_supdem_lines_interface ln,
4926: MSC_LOAD_BUCKETS_TEMP mlb,
4927: fnd_lookup_values flv,
4928: fnd_lookup_values flv1,
4929: msc_companies c,
4930: msc_company_sites s,
4931: msc_companies c1,
4932: msc_company_sites s1,
4933: msc_company_relationships r
4927: fnd_lookup_values flv,
4928: fnd_lookup_values flv1,
4929: msc_companies c,
4930: msc_company_sites s,
4931: msc_companies c1,
4932: msc_company_sites s1,
4933: msc_company_relationships r
4934: WHERE ln.parent_header_id = p_header_id AND
4935: ln.line_id between p_start_line and p_end_line AND
5066: FROM msc_supdem_lines_interface ln,
5067: MSC_LOAD_BUCKETS_TEMP mlb,
5068: fnd_lookup_values flv,
5069: fnd_lookup_values flv1,
5070: msc_companies c,
5071: msc_company_sites s
5072: WHERE ln.parent_header_id = p_header_id AND
5073: ln.line_id between p_start_line and p_end_line AND
5074: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
5111: FROM fnd_user u,
5112: fnd_user_resp_groups g,
5113: fnd_responsibility r,
5114: msc_company_users cu,
5115: msc_companies c,
5116: msc_supdem_lines_interface ln
5117: WHERE ln.parent_header_id = p_header_id
5118: AND ln.publisher_company = c.company_name
5119: AND cu.company_id = c.company_id
7394: msc_trading_partner_maps maps1,
7395: msc_trading_partner_maps maps2,
7396: msc_trading_partner_sites mtps,
7397: msc_company_sites cs,
7398: msc_companies c,
7399: msc_company_relationships rel
7400: WHERE rel.relationship_type = 2
7401: AND rel.subject_id = 1
7402: AND rel.object_id = t_supp_id(j)
7512: ELSE
7513: BEGIN
7514: SELECT c.company_id
7515: INTO t_end_ord_pub_id(j)
7516: FROM msc_companies c,
7517: msc_supdem_lines_interface l
7518: WHERE UPPER(c.company_name) = UPPER(l.end_order_publisher_name) AND
7519: l.parent_header_id = p_header_id and
7520: l.line_id = t_line_id(j);
7534: ELSE
7535: BEGIN
7536: SELECT c.company_id
7537: INTO t_shipfrom_id(j)
7538: FROM msc_companies c,
7539: msc_supdem_lines_interface l
7540: WHERE UPPER(c.company_name) = UPPER(l.ship_from_party_name) AND
7541: l.parent_header_id = p_header_id and
7542: l.line_id = t_line_id(j);
7556: ELSE
7557: BEGIN
7558: SELECT c.company_id
7559: INTO t_shipto_id(j)
7560: FROM msc_companies c,
7561: msc_supdem_lines_interface l
7562: WHERE UPPER(c.company_name) = UPPER(l.ship_to_party_name) AND
7563: l.parent_header_id = p_header_id and
7564: l.line_id = t_line_id(j);
7644: t_posting_party_id(j) := t_posting_party_id(j-1);
7645: ELSE
7646: SELECT c.company_id
7647: INTO t_posting_party_id(j)
7648: FROM msc_companies c
7649: WHERE c.company_name = t_posting_party_name(j);
7650: --Bug 5116681: there is no need no check the upper as posting party gets itself populated thru msc_companies table.
7651: END IF;
7652:
7646: SELECT c.company_id
7647: INTO t_posting_party_id(j)
7648: FROM msc_companies c
7649: WHERE c.company_name = t_posting_party_name(j);
7650: --Bug 5116681: there is no need no check the upper as posting party gets itself populated thru msc_companies table.
7651: END IF;
7652:
7653: END LOOP;
7654:
9943: UPDATE /*+ parallel(mssn,5) */
9944: msc_st_serial_numbers mssn
9945: SET mssn.publisher_id = (SELECT /*+ parallel(mc,5) */
9946: mc.company_id
9947: FROM msc_companies mc
9948: WHERE UPPER(mssn.publisher_company) = UPPER(mc.company_name)
9949: )
9950: WHERE mssn.parent_header_id = p_header_id AND
9951: mssn.line_id = t_line_id(j) AND
10021: FROM msc_system_items msi,
10022: msc_trading_partners mtp,
10023: msc_trading_partner_maps mtpm,
10024: msc_company_sites mcs,
10025: msc_companies mc
10026: WHERE msi.plan_id = -1 AND
10027: mssn.parent_header_id = p_header_id AND
10028: mssn.line_id = t_line_id(j) AND
10029: NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
10047: msc_trading_partner_maps map,
10048: msc_trading_partner_maps map1,
10049: msc_company_relationships r,
10050: msc_company_sites cs,
10051: msc_companies c
10052: WHERE mis.plan_id = -1 and
10053: mssn.parent_header_id = p_header_id AND
10054: mssn.line_id = t_line_id(j) AND
10055: NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
10076: msc_trading_partner_maps map,
10077: msc_trading_partner_maps map1,
10078: msc_company_relationships r,
10079: msc_company_sites cs,
10080: msc_companies c
10081: WHERE mic.plan_id = -1 and
10082: mssn.parent_header_id = p_header_id AND
10083: mssn.line_id = t_line_id(j) AND
10084: NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
10544: G_ORDER_FORECAST,
10545: G_REQUISITION,
10546: G_REPLENISHMENT)) -- bug #4070061
10547: AND EXISTS ( SELECT ln.customer_company
10548: FROM msc_companies c
10549: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10550: AND c.company_id = 1
10551: )
10552: ;
10567: G_ORDER_FORECAST,
10568: G_REQUISITION,
10569: G_REPLENISHMENT)) -- bug #4070061
10570: AND EXISTS ( SELECT ln.customer_company
10571: FROM msc_companies c
10572: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10573: AND c.company_id = 1
10574: )
10575: ;
10599: G_PROJ_SS,
10600: G_PROJ_ALLOC_AVAIL,
10601: G_PROJ_UNALLOC_AVAIL))
10602: AND EXISTS ( SELECT ln.customer_company
10603: FROM msc_companies c
10604: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10605: AND c.company_id = 1
10606: )
10607: ;
10622: G_PROJ_SS,
10623: G_PROJ_ALLOC_AVAIL,
10624: G_PROJ_UNALLOC_AVAIL))
10625: AND EXISTS ( SELECT ln.customer_company
10626: FROM msc_companies c
10627: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10628: AND c.company_id = 1
10629: )
10630: ;
10659: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10660: flv.language = p_language and
10661: flv.lookup_code = G_PO_ACKNOWLEDGEMENT)
10662: AND EXISTS ( SELECT ln.customer_company
10663: FROM msc_companies c
10664: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10665: AND c.company_id = 1
10666: );
10667:
10684: WHERE flv.language = p_language AND
10685: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10686: flv.lookup_code = G_PO_ACKNOWLEDGEMENT)
10687: AND EXISTS ( SELECT ln.customer_company
10688: FROM msc_companies c
10689: WHERE UPPER(c.company_name) = UPPER(NVL(ln.customer_company, ln.publisher_company))
10690: AND c.company_id = 1
10691: ) ;
10692:
10741: , NVL(ln.supplier_company, ln.publisher_company)
10742: , NVL(ln.supplier_site, ln.publisher_site)
10743: ) = 1 -- supplier
10744: AND EXISTS ( SELECT ln.customer_company
10745: FROM msc_companies c
10746: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10747: AND c.company_id = 1
10748: )
10749: ;
10765: flv.lookup_code IN ( G_SHIP_RECEIPT
10766: , G_ASN
10767: ))
10768: AND EXISTS ( SELECT ln.customer_company
10769: FROM msc_companies c
10770: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10771: AND c.company_id = 1
10772: )
10773: ;
10805: , NVL(ln.supplier_company, ln.publisher_company)
10806: , NVL(ln.supplier_site, ln.publisher_site)
10807: ) = 1 -- supplier
10808: AND EXISTS ( SELECT ln.customer_company
10809: FROM msc_companies c
10810: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10811: AND c.company_id = 1
10812: )
10813: ;
10832: flv.lookup_code IN ( G_SHIP_RECEIPT
10833: , G_ASN
10834: ))
10835: AND EXISTS ( SELECT ln.customer_company
10836: FROM msc_companies c
10837: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10838: AND c.company_id = 1
10839: )
10840: ;
10878: , NVL(ln.supplier_company, ln.publisher_company)
10879: , NVL(ln.supplier_site, ln.publisher_site)
10880: ) = 2 -- customer
10881: AND EXISTS ( SELECT ln.customer_company
10882: FROM msc_companies c
10883: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10884: AND c.company_id = 1
10885: )
10886: ;
10902: flv.lookup_code IN ( G_SALES_FORECAST
10903: , G_SELL_THRO_FCST
10904: ))
10905: AND EXISTS ( SELECT ln.customer_company
10906: FROM msc_companies c
10907: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10908: AND c.company_id = 1
10909: )
10910: ;
10945: , NVL(ln.supplier_company, ln.publisher_company)
10946: , NVL(ln.supplier_site, ln.publisher_site)
10947: ) = 2 -- customer
10948: AND EXISTS ( SELECT ln.customer_company
10949: FROM msc_companies c
10950: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10951: AND c.company_id = 1
10952: )
10953: ;
10972: flv.lookup_code IN ( G_SALES_FORECAST
10973: , G_SELL_THRO_FCST
10974: ))
10975: AND EXISTS ( SELECT ln.customer_company
10976: FROM msc_companies c
10977: WHERE UPPER(c.company_name) = UPPER(NVL(ln.supplier_company, ln.publisher_company))
10978: AND c.company_id = 1
10979: )
10980: ;