[Home] [Help]
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,
410: msc_supdem_lines_interface ln
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
412: AND ln.row_status = G_SUCCESS
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,
439: msc_supdem_lines_interface ln
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
441: AND ln.row_status = G_SUCCESS
1069: ln.line_id = t_line_id(j) AND
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));
1110: Upper(ln.sync_indicator) = 'D' 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,
1152: Upper(ln.sync_indicator) = 'D' 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,
1697: WHERE ln.parent_header_id = p_header_id AND
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));
1790: ln.line_id = t_line_id(j) 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,
1830: ln.line_id = t_line_id(j) 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,
1849: ln.line_id = t_line_id(j) AND
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)));
1868: ln.line_id = t_line_id(j) AND
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)));
1886: ln.line_id = t_line_id(j) AND
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)));
1911: select msi.inventory_item_id
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,
1952: msc_trading_partner_maps map,
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,
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
1962: mis.organization_id = mtp.sr_tp_id and
1989: from msc_item_customers mic,
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
2032: not exists ( select msi.inventory_item_id
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
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
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,
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
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)
2147: and c.company_id = s.company_id
2448: select msi.inventory_item_id
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
2491: from msc_item_suppliers mis,
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
2525: from msc_item_customers mic,
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
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
3217: ln.line_id between p_start_line and p_end_line and
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
3277: ln.line_id between p_start_line and p_end_line and
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
3324: ln.line_id between p_start_line and p_end_line and
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
3572: UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
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
3692: WHERE ln.parent_header_id = p_header_id AND
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
3694: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
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
3813: UPPER(c.company_name) = UPPER(NVL(ln.publisher_company, ln.posting_party_name)) AND
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
3928: WHERE ln.parent_header_id = p_header_id AND
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
3930: ln.bucket_type IS NULL AND
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
4046: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND
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
4246: WHERE ln.parent_header_id = p_header_id AND
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
4248: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
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
4388: UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
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
4590: WHERE ln.parent_header_id = p_header_id AND
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
4592: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
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
4732: UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
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
4934: WHERE ln.parent_header_id = p_header_id AND
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
4936: flv.lookup_type = 'MSC_X_BUCKET_TYPE' AND
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
5075: UPPER(flv.meaning) = NVL(UPPER(ln.bucket_type), G_NULL_STRING) AND
7393: msc_trading_partner_maps maps,
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
7577: ELSE
7578: BEGIN
7579: SELECT s.company_site_id
7580: INTO t_end_ord_pub_site_id(j)
7581: FROM msc_company_sites s,
7582: msc_supdem_lines_interface l
7583: WHERE s.company_id = t_end_ord_pub_id(j) AND
7584: UPPER(s.company_site_name) = UPPER(l.end_order_publisher_site) AND
7585: l.parent_header_id = p_header_id and
7601: ELSE
7602: BEGIN
7603: SELECT s.company_site_id
7604: INTO t_shipfrom_site_id(j)
7605: FROM msc_company_sites s,
7606: msc_supdem_lines_interface l
7607: WHERE s.company_id = t_shipfrom_id(j) AND
7608: UPPER(s.company_site_name) = UPPER(l.ship_from_party_site) AND
7609: l.parent_header_id = p_header_id and
7625: ELSE
7626: BEGIN
7627: SELECT s.company_site_id
7628: INTO t_shipto_site_id(j)
7629: FROM msc_company_sites s,
7630: msc_supdem_lines_interface l
7631: WHERE s.company_id = t_shipto_id(j) AND
7632: UPPER(s.company_site_name) = UPPER(l.ship_to_party_site) AND
7633: l.parent_header_id = p_header_id and
9976: UPDATE /*+ parallel(mssn,5) */
9977: msc_st_serial_numbers mssn
9978: SET mssn.publisher_site_id = (SELECT /*+ parallel(mcs,5) */
9979: mcs.company_site_id
9980: FROM msc_company_sites mcs
9981: WHERE mssn.publisher_id=mcs.company_id AND
9982: UPPER(mssn.publisher_site) = UPPER(mcs.company_site_name)
9983: )
9984: WHERE mssn.parent_header_id = p_header_id AND
10020: SELECT msi.inventory_item_id
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
10046: FROM msc_item_suppliers mis,
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
10075: FROM msc_item_customers mic,
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