[Home] [Help]
167: ln.pegging_order_identifier,
168: ln.ref_release_number,
169: ln.ref_line_number
170: from msc_supdem_lines_interface ln,
171: fnd_lookup_values flv
172: where ln.parent_header_id = p_header_id
173: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
174: and flv.language = p_language
175: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
195: upper(ln.supplier_site),
196: upper(ln.order_type),
197: ln.inventory_item_id
198: from msc_supdem_lines_interface ln,
199: fnd_lookup_values flv
200: where ln.parent_header_id = p_header_id
201: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
202: and flv.language = p_language
203: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
217: Upper(ln.order_type),
218: ln.inventory_item_id,
219: ln.order_identifier
220: FROM msc_supdem_lines_interface ln,
221: fnd_lookup_values flv
222: WHERE ln.parent_header_id = p_header_id
223: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
224: and flv.language = p_language
225: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
243: ln.inventory_item_id,
244: trunc(to_date(ln.key_date,p_date_format)),
245: upper(ln.bucket_type)
246: from msc_supdem_lines_interface ln,
247: fnd_lookup_values flv,
248: fnd_lookup_values flv1
249: where ln.parent_header_id = p_header_id
250: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
251: and flv.language = p_language
244: trunc(to_date(ln.key_date,p_date_format)),
245: upper(ln.bucket_type)
246: from msc_supdem_lines_interface ln,
247: fnd_lookup_values flv,
248: fnd_lookup_values flv1
249: where ln.parent_header_id = p_header_id
250: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
251: and flv.language = p_language
252: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
277: ln.inventory_item_id,
278: trunc(to_date(ln.key_date,p_date_format)),
279: upper(ln.bucket_type)
280: from msc_supdem_lines_interface ln,
281: fnd_lookup_values flv
282: where ln.parent_header_id = p_header_id
283: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
284: and flv.language = p_language
285: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
313: ln.inventory_item_id,
314: trunc(to_date(ln.key_date,p_date_format)),
315: upper(ln.bucket_type)
316: from msc_supdem_lines_interface ln,
317: fnd_lookup_values flv,
318: fnd_lookup_values flv1
319: where ln.parent_header_id = p_header_id
320: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
321: and flv.language = p_language
314: trunc(to_date(ln.key_date,p_date_format)),
315: upper(ln.bucket_type)
316: from msc_supdem_lines_interface ln,
317: fnd_lookup_values flv,
318: fnd_lookup_values flv1
319: where ln.parent_header_id = p_header_id
320: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
321: and flv.language = p_language
322: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
353: ln.inventory_item_id,
354: trunc(to_date(ln.key_date,p_date_format)),
355: upper(ln.bucket_type)
356: from msc_supdem_lines_interface ln,
357: fnd_lookup_values flv,
358: fnd_lookup_values flv1
359: where ln.parent_header_id = p_header_id
360: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
361: and flv.language = p_language
354: trunc(to_date(ln.key_date,p_date_format)),
355: upper(ln.bucket_type)
356: from msc_supdem_lines_interface ln,
357: fnd_lookup_values flv,
358: fnd_lookup_values flv1
359: where ln.parent_header_id = p_header_id
360: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
361: and flv.language = p_language
362: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
383: p_language IN varchar2)
384: IS
385: SELECT ln.line_id, flv.lookup_code
386: FROM msc_supdem_lines_interface ln
387: , fnd_lookup_values flv
388: WHERE ln.parent_header_id = p_header_id
389: AND ln.line_id BETWEEN p_start_line AND p_end_line
390: AND Nvl(ln.row_status, g_process) = g_process
391: AND flv.lookup_type = 'MSC_X_ORDER_TYPE'
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
413: AND Upper(c.company_name) = Upper(ln.publisher_company)
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
442: AND Upper(c.company_name) = Upper(ln.publisher_company)
542: WHERE ln.parent_header_id = p_header_id AND
543: ln.line_id = t_line_id(j) AND
544: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
545: NOT EXISTS (SELECT l.lookup_code
546: FROM fnd_lookup_values l
547: WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
548: UPPER(l.meaning) = NVL(UPPER(ln.order_type), G_NULL_STRING) AND
549: l.language = p_language);
550:
554:
555: SELECT flv.lookup_code,
556: ln.line_id
557: BULK COLLECT into t_order_type, t_line_id
558: FROM fnd_lookup_values flv,
559: msc_supdem_lines_interface ln
560: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
561: flv.language = p_language and
562: Upper(flv.meaning(+)) = Upper(ln.order_type) and
628: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
629: t_order_type(j) is NULL;
630:
631: -- NOT EXISTS (SELECT l.lookup_code
632: -- FROM fnd_lookup_values l
633: -- WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
634: -- UPPER(l.meaning) = NVL(UPPER(ln.order_type), G_NULL_STRING) AND
635: -- l.language = p_language);
636:
981: ln.line_id = t_line_id(j) AND
982: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
983: ln.bucket_type IS NOT NULL AND
984: NOT EXISTS (SELECT l.lookup_code
985: FROM fnd_lookup_values l
986: WHERE l.lookup_type = 'MSC_X_BUCKET_TYPE' AND
987: UPPER(l.meaning) = NVL(UPPER(ln.bucket_type),
988: G_NULL_STRING) AND
989: l.language = p_language);
1372: ln.line_id = t_line_id(j) AND
1373: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1374: ln.end_order_type IS NOT NULL AND
1375: NOT EXISTS (SELECT l.lookup_code
1376: FROM fnd_lookup_values l
1377: WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
1378: UPPER(l.meaning) = NVL(UPPER(ln.end_order_type), G_NULL_STRING) AND
1379: l.language = p_language);
1380:
1412: if p_build_err = 2 THEN
1413: SELECT flv.lookup_code,
1414: ln.line_id
1415: BULK COLLECT into t_order_type, t_line_id1
1416: FROM fnd_lookup_values flv,
1417: msc_supdem_lines_interface ln
1418: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
1419: flv.language = p_language and
1420: flv.meaning = ln.order_type and
1720: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1721: ln.customer_company IS NULL AND
1722: ln.supplier_company IS NULL AND
1723: not exists (SELECT 1
1724: FROM fnd_lookup_values flv
1725: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1726: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1727: flv.language = p_language AND
1728: flv.lookup_code in (G_UNALLOCATED_ONHAND,G_CONS_ADVICE,g_work_order,g_safety_stock,g_proj_avai_bal));
1736: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
1737: (ln.supplier_company IS NOT NULL or
1738: ln.customer_company IS NOT NULL) and
1739: (G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
1740: FROM fnd_lookup_values flv
1741: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1742: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1743: flv.language = p_language) OR
1744: G_CONS_ADVICE = (SELECT flv.lookup_code
1741: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1742: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1743: flv.language = p_language) OR
1744: G_CONS_ADVICE = (SELECT flv.lookup_code
1745: FROM fnd_lookup_values flv
1746: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1747: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1748: flv.language = p_language) OR
1749: g_safety_stock = (SELECT flv.lookup_code
1746: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1747: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1748: flv.language = p_language) OR
1749: g_safety_stock = (SELECT flv.lookup_code
1750: FROM fnd_lookup_values flv
1751: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1752: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1753: flv.language = p_language) OR
1754: g_proj_avai_bal = (SELECT flv.lookup_code
1751: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1752: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1753: flv.language = p_language) OR
1754: g_proj_avai_bal = (SELECT flv.lookup_code
1755: FROM fnd_lookup_values flv
1756: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
1757: UPPER(flv.meaning) = UPPER(ln.order_type) AND
1758: flv.language = p_language));
1759: /* sbala ADD CA */
2088: WHERE ln.parent_header_id = p_header_id AND
2089: ln.line_id = t_line_id(j) AND
2090: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2091: (G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
2092: FROM fnd_lookup_values flv
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'
2133: WHERE ln.parent_header_id = p_header_id AND
2134: ln.line_id = t_line_id(j) AND
2135: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2136: (G_UNALLOCATED_ONHAND = (SELECT flv.lookup_code
2137: FROM fnd_lookup_values flv
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'
2169: AND ln.line_id = t_line_id(j)
2170: AND Nvl(ln.row_status, g_process) = g_process
2171: AND g_null_string = (SELECT Nvl(ln1.wip_end_date, g_null_string)
2172: FROM msc_supdem_lines_interface ln1,
2173: fnd_lookup_values flv
2174: WHERE ln1.parent_header_id = ln.parent_header_id and
2175: ln1.line_id = ln.line_id and
2176: UPPER(flv.meaning) = UPPER(ln1.order_type) and
2177: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
2184: WHERE ln.parent_header_id = p_header_id AND
2185: ln.line_id = t_line_id(j) AND
2186: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2187: UPPER(ln.order_type) = (SELECT UPPER(flv.meaning)
2188: FROM fnd_lookup_values flv
2189: WHERE flv.language = p_language AND
2190: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2191: flv.lookup_code = g_work_order);
2192:
2227: ln.line_id = t_line_id(j) AND
2228: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2229: G_NULL_STRING = (SELECT NVL(ln1.new_schedule_date, G_NULL_STRING)
2230: FROM msc_supdem_lines_interface ln1,
2231: fnd_lookup_values flv
2232: WHERE ln1.parent_header_id = ln.parent_header_id and
2233: ln1.line_id = ln.line_id and
2234: UPPER(flv.meaning) = UPPER(ln1.order_type) and
2235: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
2245: WHERE ln.parent_header_id = p_header_id AND
2246: ln.line_id = t_line_id(j) AND
2247: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2248: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
2249: FROM fnd_lookup_values flv
2250: WHERE flv.language = p_language AND
2251: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2252: flv.lookup_code IN (G_CONS_ADVICE,
2253: G_HIST_SALES,
2268: ln.line_id = t_line_id(j) AND
2269: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2270: G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
2271: FROM msc_supdem_lines_interface ln1,
2272: fnd_lookup_values flv
2273: WHERE ln1.parent_header_id = ln.parent_header_id and
2274: ln1.line_id = ln.line_id and
2275: UPPER(flv.meaning) = UPPER(ln1.order_type) and
2276: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
2280: G_SALES_FORECAST,
2281: G_SALES_ORDER)) AND
2282: G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
2283: FROM msc_supdem_lines_interface ln1,
2284: fnd_lookup_values flv
2285: WHERE ln1.parent_header_id = ln.parent_header_id and
2286: ln1.line_id = ln.line_id and
2287: UPPER(flv.meaning) = UPPER(ln1.order_type) and
2288: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
2306: WHERE ln.parent_header_id = p_header_id AND
2307: ln.line_id = t_line_id(j) AND
2308: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2309: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
2310: FROM fnd_lookup_values flv
2311: WHERE flv.language = p_language AND
2312: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2313: flv.lookup_code IN (G_SUPPLY_COMMIT,
2314: G_ASN,
2328: WHERE ln.parent_header_id = p_header_id AND
2329: ln.line_id = t_line_id(j) AND
2330: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2331: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
2332: FROM fnd_lookup_values flv
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
2381: WHERE ln.parent_header_id = p_header_id AND
2382: ln.line_id = t_line_id(j) AND
2383: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
2384: UPPER(ln.order_type) = (SELECT UPPER(flv.meaning)
2385: FROM fnd_lookup_values flv
2386: WHERE flv.language = p_language AND
2387: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
2388: flv.lookup_code IN (G_SALES_ORDER)) AND
2389: exists (SELECT 'exists'
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
3218: NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
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
3278: NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
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
3325: NVL(ln.row_status, G_PROCESS) = G_SUCCESS and
3561: ln.attribute14,
3562: ln.attribute15,
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
3562: ln.attribute15,
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
3681: ln.attribute14,
3682: ln.attribute15,
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,
3682: ln.attribute15,
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,
3803: ln.attribute14,
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
3918: ln.attribute14,
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,
4036: ln.attribute14,
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
4235: ln.attribute15,
4236: ln.posting_party_name
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,
4236: ln.posting_party_name
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,
4377: ln.attribute15,
4378: ln.posting_party_name
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
4378: ln.posting_party_name
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
4579: ln.attribute15,
4580: ln.posting_party_name
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,
4580: ln.posting_party_name
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,
4721: ln.attribute15,
4722: ln.posting_party_name
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
4722: ln.posting_party_name
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
4923: ln.attribute15,
4924: ln.posting_party_name
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,
4924: ln.posting_party_name
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,
5064: ln.attribute15,
5065: ln.posting_party_name
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
5065: ln.posting_party_name
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
5131: ln.line_id,
5132: to_char(trunc(to_date(ln.key_date,l_date_format)),'J') from_date,
5133: to_char(trunc(to_date(nvl(ln.key_end_date,key_date),l_date_format)),'J') to_date
5134: FROM msc_supdem_lines_interface ln,
5135: fnd_lookup_values flv,
5136: fnd_lookup_values flv1
5137: WHERE ln.key_date is not null
5138: and ln.parent_header_id = p_header_id
5139: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
5132: to_char(trunc(to_date(ln.key_date,l_date_format)),'J') from_date,
5133: to_char(trunc(to_date(nvl(ln.key_end_date,key_date),l_date_format)),'J') to_date
5134: FROM msc_supdem_lines_interface ln,
5135: fnd_lookup_values flv,
5136: fnd_lookup_values flv1
5137: WHERE ln.key_date is not null
5138: and ln.parent_header_id = p_header_id
5139: and nvl(ln.row_status, G_PROCESS) = G_SUCCESS
5140: and flv.language = p_language
5342: l_language_code := 'US';
5343: END;
5344:
5345: execute immediate
5346: 'select meaning from FND_LOOKUP_VALUES '
5347: || ' where LOOKUP_TYPE = ''MSC_X_BUCKET_TYPE'' '
5348: || ' and LOOKUP_CODE = 1 '
5349: || ' and LANGUAGE = :l_language_code '
5350: into G_DAY_DESC
7221: from dual
7222: where exists (
7223: select 'exists'
7224: from msc_supdem_lines_interface ln,
7225: fnd_lookup_values flv
7226: where ln.parent_header_id = p_header_id
7227: and ln.row_status = G_SUCCESS
7228: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
7229: and flv.lookup_code = 2
7275: from dual
7276: where exists (
7277: select 'exists'
7278: from msc_supdem_lines_interface ln,
7279: fnd_lookup_values flv
7280: where ln.parent_header_id = p_header_id
7281: and ln.row_status = G_SUCCESS
7282: and flv.lookup_type = 'MSC_X_ORDER_TYPE'
7283: and flv.lookup_code IN (2, 3)
7492: t_end_order_type(j) := null;
7493: ELSE
7494: SELECT flv.lookup_code
7495: INTO t_end_order_type(j)
7496: FROM fnd_lookup_values flv,
7497: msc_supdem_lines_interface ln
7498: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' and
7499: flv.language = p_language_code and
7500: UPPER(flv.meaning) = UPPER(ln.end_order_type) and
7873: CURSOR c_shipping_control_meaning
7874: ( p_shipping_control_code IN NUMBER
7875: ) IS
7876: SELECT meaning,to_number(lookup_code)
7877: FROM fnd_lookup_values
7878: WHERE lookup_type = 'MSC_X_SHIPPING_CONTROL'
7879: AND language = p_language_code
7880: AND lookup_code = p_shipping_control_code
7881: ;
9894: mssn.line_id = t_line_id(j) AND
9895: NVL(mssn.row_status, G_PROCESS) = G_PROCESS AND
9896: NOT EXISTS (SELECT /*+ parallel(l,5) */
9897: l.lookup_code
9898: FROM fnd_lookup_values l
9899: WHERE l.lookup_type = 'MSC_X_ORDER_TYPE' AND
9900: UPPER(l.meaning) = NVL(UPPER(mssn.order_type), G_NULL_STRING) AND
9901: l.language = p_language AND
9902: l.lookup_code = G_UNALLOCATED_ONHAND);
10163: msde.publisher_site_id = mssn.publisher_site_id AND
10164: msde.inventory_item_id = mssn.inventory_item_id AND
10165: msde.publisher_order_type = (SELECT /*+ parallel(flv,5) */
10166: flv.lookup_code
10167: FROM fnd_lookup_values flv
10168: WHERE flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10169: UPPER(flv.meaning) = NVL(UPPER(mssn.order_type), G_NULL_STRING) AND
10170: flv.language = p_language)
10171: )
10532: ln.line_id = t_line_id(j) AND
10533: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10534: G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
10535: FROM msc_supdem_lines_interface ln1,
10536: fnd_lookup_values flv
10537: WHERE ln1.parent_header_id = ln.parent_header_id and
10538: ln1.line_id = ln.line_id and
10539: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10540: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10558: WHERE ln.parent_header_id = p_header_id AND
10559: ln.line_id = t_line_id(j) AND
10560: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10561: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10562: FROM fnd_lookup_values flv
10563: WHERE flv.language = p_language AND
10564: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10565: flv.lookup_code IN (G_PURCHASE_ORDER,
10566: G_SHIP_RECEIPT,
10587: ln.line_id = t_line_id(j) AND
10588: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10589: G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
10590: FROM msc_supdem_lines_interface ln1,
10591: fnd_lookup_values flv
10592: WHERE ln1.parent_header_id = ln.parent_header_id and
10593: ln1.line_id = ln.line_id and
10594: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10595: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10613: WHERE ln.parent_header_id = p_header_id AND
10614: ln.line_id = t_line_id(j) AND
10615: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10616: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10617: FROM fnd_lookup_values flv
10618: WHERE flv.language = p_language AND
10619: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10620: flv.lookup_code IN (G_SELL_THRO_FCST,
10621: G_SUPPLIER_CAP,
10642: ln.line_id = t_line_id(j) AND
10643: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10644: G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
10645: FROM msc_supdem_lines_interface ln1,
10646: fnd_lookup_values flv
10647: WHERE ln1.parent_header_id = ln.parent_header_id and
10648: ln1.line_id = ln.line_id and
10649: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10650: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10651: flv.language = p_language and
10652: flv.lookup_code = G_PO_ACKNOWLEDGEMENT) AND
10653: G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
10654: FROM msc_supdem_lines_interface ln1,
10655: fnd_lookup_values flv
10656: WHERE ln1.parent_header_id = ln.parent_header_id and
10657: ln1.line_id = ln.line_id and
10658: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10659: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10679: ln.line_id = t_line_id(j) AND
10680: ln.receipt_date is not null AND
10681: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10682: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10683: FROM fnd_lookup_values flv
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
10719: ln.line_id = t_line_id(j) AND
10720: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10721: G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
10722: FROM msc_supdem_lines_interface ln1,
10723: fnd_lookup_values flv
10724: WHERE ln1.parent_header_id = ln.parent_header_id and
10725: ln1.line_id = ln.line_id and
10726: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10727: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10755: ln.line_id = t_line_id(j) AND
10756: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10757: G_NULL_STRING = (SELECT NVL(ln1.receipt_date, G_NULL_STRING)
10758: FROM msc_supdem_lines_interface ln1,
10759: fnd_lookup_values flv
10760: WHERE ln1.parent_header_id = ln.parent_header_id and
10761: ln1.line_id = ln.line_id and
10762: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10763: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10786: WHERE ln.parent_header_id = p_header_id AND
10787: ln.line_id = t_line_id(j) AND
10788: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10789: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10790: FROM fnd_lookup_values flv
10791: WHERE flv.language = p_language AND
10792: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10793: flv.lookup_code IN (G_PURCHASE_ORDER,
10794: -- G_SHIP_RECEIPT,
10825: WHERE ln.parent_header_id = p_header_id AND
10826: ln.line_id = t_line_id(j) AND
10827: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10828: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10829: FROM fnd_lookup_values flv
10830: WHERE flv.language = p_language AND
10831: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10832: flv.lookup_code IN ( G_SHIP_RECEIPT
10833: , G_ASN
10852: ln.line_id = t_line_id(j) AND
10853: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10854: G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
10855: FROM msc_supdem_lines_interface ln1,
10856: fnd_lookup_values flv
10857: WHERE ln1.parent_header_id = ln.parent_header_id and
10858: ln1.line_id = ln.line_id and
10859: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10860: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10892: ln.line_id = t_line_id(j) AND
10893: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10894: G_NULL_STRING = (SELECT NVL(ln1.ship_date, G_NULL_STRING)
10895: FROM msc_supdem_lines_interface ln1,
10896: fnd_lookup_values flv
10897: WHERE ln1.parent_header_id = ln.parent_header_id and
10898: ln1.line_id = ln.line_id and
10899: UPPER(flv.meaning) = UPPER(ln1.order_type) and
10900: flv.lookup_type = 'MSC_X_ORDER_TYPE' and
10922: WHERE ln.parent_header_id = p_header_id AND
10923: ln.line_id = t_line_id(j) AND
10924: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10925: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10926: FROM fnd_lookup_values flv
10927: WHERE flv.language = p_language AND
10928: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10929: flv.lookup_code IN ( -- G_SELL_THRO_FCST,
10930: -- G_SUPPLIER_CAP,
10965: WHERE ln.parent_header_id = p_header_id AND
10966: ln.line_id = t_line_id(j) AND
10967: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND
10968: UPPER(ln.order_type) IN (SELECT UPPER(flv.meaning)
10969: FROM fnd_lookup_values flv
10970: WHERE flv.language = p_language AND
10971: flv.lookup_type = 'MSC_X_ORDER_TYPE' AND
10972: flv.lookup_code IN ( G_SALES_FORECAST
10973: , G_SELL_THRO_FCST