eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.MSC_SCE_LOADS_PKG dependencies on MSC_SUPDEM_LINES_INTERFACE

Line 128: FROM msc_supdem_lines_interface l

124: p_end_line IN NUMBER,
125: p_date_format IN VARCHAR2
126: ) IS
127: SELECT l.line_id
128: FROM msc_supdem_lines_interface l
129: WHERE l.parent_header_id = p_header_id
130: AND l.line_id BETWEEN p_start_line AND p_end_line
131: AND NVL(l.row_status, G_PROCESS) = G_PROCESS
132: AND checkdates(p_header_id,l.line_id,p_date_format) = 1;

Line 146: from msc_supdem_lines_interface

142: nvl(customer_item_name,
143: supplier_item_name))),
144: substrb(order_type,1,80) ,
145: err_msg
146: from msc_supdem_lines_interface
147: where parent_header_id = p_header_id and
148: line_id between p_start_line and p_end_line and
149: row_status = 4;
150:

Line 170: from msc_supdem_lines_interface ln,

166: ln.line_number,
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

Line 198: from msc_supdem_lines_interface ln,

194: upper(ln.supplier_company),
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

Line 220: FROM msc_supdem_lines_interface ln,

216: Upper(ln.customer_site),
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

Line 246: from msc_supdem_lines_interface ln,

242: upper(ln.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

Line 280: from msc_supdem_lines_interface ln,

276: upper(ln.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

Line 316: from msc_supdem_lines_interface ln,

312: upper(ln.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

Line 356: from msc_supdem_lines_interface ln,

352: upper(ln.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

Line 386: FROM msc_supdem_lines_interface ln

382: p_end_line IN number,
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

Line 410: msc_supdem_lines_interface ln

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)
414: AND c.company_id = cs.company_id

Line 439: msc_supdem_lines_interface ln

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)
443: AND c.company_id = cs.company_id

Line 472: from msc_supdem_lines_interface

468:
469: if calendar_is_not_seeded = 0 then
470: begin
471: select min(line_id) into l_min
472: from msc_supdem_lines_interface
473: where parent_header_id = p_header_id;
474: exception
475: when others then
476: return;

Line 481: update msc_supdem_lines_interface

477: end;
478:
479: l_err_msg := get_message('MSC', 'MSC_X_CALENDAR_NOT_SEEDED', p_language);
480:
481: update msc_supdem_lines_interface
482: set row_status = 4,
483: err_msg = substrb(l_err_msg,1,1000)
484: where parent_header_id = p_header_id
485: and line_id = l_min;

Line 487: update msc_supdem_lines_interface

483: err_msg = substrb(l_err_msg,1,1000)
484: where parent_header_id = p_header_id
485: and line_id = l_min;
486:
487: update msc_supdem_lines_interface
488: set row_status = 1
489: where parent_header_id = p_header_id
490: and line_id <> l_min;
491:

Line 499: FROM msc_supdem_lines_interface

495:
496: BEGIN
497: SELECT min(line_id), max(line_id)
498: INTO l_min, l_max
499: FROM msc_supdem_lines_interface
500: WHERE parent_header_id = p_header_id;
501: EXCEPTION
502: WHEN NO_DATA_FOUND THEN
503: return;

Line 527: FROM msc_supdem_lines_interface

523: --=========================================================================
524:
525: SELECT line_id
526: BULK COLLECT INTO t_line_id
527: FROM msc_supdem_lines_interface
528: WHERE parent_header_id = p_header_id AND
529: line_id BETWEEN l_start_line and l_end_line;
530:
531:

Line 540: UPDATE msc_supdem_lines_interface ln

536:
537:
538: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ORDER_TYPE', p_language);
539: FORALL j IN t_line_id.FIRST..t_line_id.LAST
540: UPDATE msc_supdem_lines_interface ln
541: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 559: msc_supdem_lines_interface ln

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
563: nvl(ln.row_status, G_PROCESS) = G_PROCESS and

Line 582: UPDATE msc_supdem_lines_interface ln

578: -- Validation: Check if the posting party exists
579: --=======================================================================
580: l_err_msg := get_message('MSC', 'MSC_X_INVALID_POSTING_PARTY', p_language);
581: FORALL j IN t_line_id.FIRST..t_line_id.LAST
582: UPDATE msc_supdem_lines_interface ln
583: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 596: UPDATE msc_supdem_lines_interface ln

592: -- Validation: Check if the sync indicator is valid
593: --=======================================================================
594: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SYNC_INDICATOR', p_language);
595: FORALL j IN t_line_id.FIRST..t_line_id.LAST
596: UPDATE msc_supdem_lines_interface ln
597: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
598: WHERE ln.parent_header_id = p_header_id AND
599: ln.line_id = t_line_id(j) AND
600: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 609: update msc_supdem_lines_interface ln

605: -- file or XML document.
606: --======================================================================
607: l_err_msg := get_message('MSC', 'MSC_X_INVALID_NO_ITEM', p_language);
608: FORALL j IN t_line_id.FIRST..t_line_id.LAST
609: update msc_supdem_lines_interface ln
610: set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
611: where ln.parent_header_id = p_header_id and
612: ln.line_id = t_line_id(j) and
613: NVL(ln.row_status, G_PROCESS) = G_PROCESS and

Line 624: UPDATE msc_supdem_lines_interface ln

620: -- Validation: Check if order type is valid
621: --======================================================================
622: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ORDER_TYPE', p_language);
623: FORALL j IN t_line_id.FIRST..t_line_id.LAST
624: UPDATE msc_supdem_lines_interface ln
625: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
626: WHERE ln.parent_header_id = p_header_id AND
627: ln.line_id = t_line_id(j) AND
628: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 658: UPDATE msc_supdem_lines_interface ln

654: log_debug('Order number is Mandatory for Consumption Advice');
655: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ORDER_NUMBER', p_language);
656:
657: FORALL j IN t_line_id.FIRST..t_line_id.LAST
658: UPDATE msc_supdem_lines_interface ln
659: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
660: WHERE ln.parent_header_id = p_header_id AND
661: ln.line_id = t_line_id(j) AND
662: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 675: UPDATE msc_supdem_lines_interface ln

671: --=======================================================================
672: IF p_build_err = 1 THEN
673: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER', p_language);
674: FORALL j IN t_line_id.FIRST..t_line_id.LAST
675: UPDATE msc_supdem_lines_interface ln
676: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
677: WHERE ln.parent_header_id = p_header_id AND
678: ln.line_id = t_line_id(j) AND
679: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 690: UPDATE msc_supdem_lines_interface ln

686: -- Validate the company to be consistent if more than two feild has been given
687: --=============================================================================
688: l_err_msg := get_message('MSC', 'MSC_X_PUBLISH_UNMATCH', p_language); --change for export start
689: FORALL j IN t_line_id.FIRST..t_line_id.LAST
690: UPDATE msc_supdem_lines_interface ln
691: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
692: WHERE ln.parent_header_id = p_header_id AND
693: ln.line_id = t_line_id(j) AND
694: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 722: UPDATE msc_supdem_lines_interface ln

718: -- Validate the company sites to be consistent if more than two feild has been given
719: --=============================================================================
720: l_err_msg := get_message('MSC', 'MSC_X_PUBLISH_SITE_UNMATCH', p_language);
721: FORALL j IN t_line_id.FIRST..t_line_id.LAST
722: UPDATE msc_supdem_lines_interface ln
723: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
724: WHERE ln.parent_header_id = p_header_id AND
725: ln.line_id = t_line_id(j) AND
726: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 752: UPDATE msc_supdem_lines_interface ln

748: --==============================================================================
749: -- Populate the Publisher Information
750: --============================================================================
751: FORALL j IN t_line_id.FIRST..t_line_id.LAST
752: UPDATE msc_supdem_lines_interface ln
753: SET ln.publisher_company = NVL(decode(t_order_type(j),
754: G_SALES_FORECAST, ln.posting_party_name,
755: G_ORDER_FORECAST, ln.customer_company,
756: G_SUPPLY_COMMIT, ln.supplier_company,

Line 827: UPDATE msc_supdem_lines_interface ln

823: if (G_USER_IS_ADMIN <> SYS_YES) then
824: /* only for users who do not have SC Admin Responsibility */
825: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER', p_language);
826: FORALL j IN t_line_id.FIRST..t_line_id.LAST
827: UPDATE msc_supdem_lines_interface ln
828: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
829: ln.row_status = G_FAILURE
830: WHERE ln.parent_header_id = p_header_id AND
831: ln.line_id = t_line_id(j) AND

Line 854: UPDATE msc_supdem_lines_interface ln

850: --=============================================================================
851: -- Remove the extra Columns . Added for export/import
852: --=============================================================================
853: FORALL j IN t_line_id.FIRST..t_line_id.LAST
854: UPDATE msc_supdem_lines_interface ln
855: SET ln.customer_company = DECODE(t_order_type(j),
856: G_SALES_FORECAST,
857: DECODE(ln.posting_party_name,
858: ln.customer_company,

Line 978: UPDATE msc_supdem_lines_interface ln

974: -- Validation: Check if bucket type is valid
975: --======================================================================
976: l_err_msg := get_message('MSC', 'MSC_X_INVALID_BUCKET_TYPE', p_language);
977: FORALL j IN t_line_id.FIRST..t_line_id.LAST
978: UPDATE msc_supdem_lines_interface ln
979: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
980: WHERE ln.parent_header_id = p_header_id AND
981: ln.line_id = t_line_id(j) AND
982: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1002: UPDATE msc_supdem_lines_interface ln

998: CLOSE c_dates;
999:
1000: IF t_err_line_id IS NOT NULL AND t_err_line_id.COUNT > 0 THEN
1001: FORALL j in t_err_line_id.FIRST..t_err_line_id.LAST
1002: UPDATE msc_supdem_lines_interface ln
1003: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1004: WHERE ln.parent_header_id = p_header_id AND
1005: ln.line_id = t_err_line_id(j) AND
1006: NVL(ln.row_status, G_PROCESS) = G_PROCESS;

Line 1016: UPDATE msc_supdem_lines_interface ln

1012: -- Set row status = G_FAILURE for records with sync indicator 'D'
1013: -- where err_msg is not null
1014: --=======================================================================
1015: forall j IN t_line_id.first..t_line_id.last
1016: UPDATE msc_supdem_lines_interface ln
1017: SET ln.row_status = g_failure
1018: WHERE ln.err_msg IS NOT NULL
1019: AND Upper(ln.sync_indicator) = 'D'
1020: AND ln.parent_header_id = p_header_id

Line 1030: update msc_supdem_lines_interface ln

1026: --Validation: Item check for records with sync indicator 'D'
1027: --========================================================================
1028: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ITEM', p_language);
1029: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1030: update msc_supdem_lines_interface ln
1031: set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1032: where ln.parent_header_id = p_header_id and
1033: ln.line_id = t_line_id(j) and
1034: NVL(ln.row_status, G_PROCESS) = G_PROCESS and

Line 1066: UPDATE msc_supdem_lines_interface ln

1062: -- Validation: Check if publisher site exists
1063: --======================================================================
1064: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER_SITE', p_language);
1065: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1066: UPDATE msc_supdem_lines_interface ln
1067: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1068: WHERE ln.parent_header_id = p_header_id AND
1069: ln.line_id = t_line_id(j) AND
1070: Upper(ln.sync_indicator) = 'D' AND

Line 1084: UPDATE msc_supdem_lines_interface ln

1080: -- Validation: Check if customer company exists
1081: --========================================================
1082: l_err_msg := get_message('MSC', 'MSC_X_INVALID_CUSTOMER', p_language);
1083: FORALL j in t_line_id.FIRST..t_line_id.LAST
1084: UPDATE msc_supdem_lines_interface ln
1085: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1086: WHERE ln.parent_header_id = p_header_id AND
1087: ln.line_id = t_line_id(j) AND
1088: Upper(ln.sync_indicator) = 'D' AND

Line 1106: UPDATE msc_supdem_lines_interface ln

1102: -- Validation: Check if customer site exists
1103: --========================================================
1104: l_err_msg := get_message('MSC', 'MSC_X_INVALID_CUST_SITE', p_language);
1105: FORALL j in t_line_id.FIRST..t_line_id.LAST
1106: UPDATE msc_supdem_lines_interface ln
1107: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1108: WHERE ln.parent_header_id = p_header_id AND
1109: ln.line_id = t_line_id(j) AND
1110: Upper(ln.sync_indicator) = 'D' AND

Line 1126: UPDATE msc_supdem_lines_interface ln

1122: -- Validation: Check if supplier company exists
1123: --========================================================
1124: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SUPPLIER', p_language);
1125: FORALL j in t_line_id.FIRST..t_line_id.LAST
1126: UPDATE msc_supdem_lines_interface ln
1127: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1128: WHERE ln.parent_header_id = p_header_id AND
1129: ln.line_id = t_line_id(j) AND
1130: Upper(ln.sync_indicator) = 'D' AND

Line 1148: UPDATE msc_supdem_lines_interface ln

1144: -- Validation: Check if supplier site exists
1145: --========================================================
1146: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SUPP_SITE', p_language);
1147: FORALL j in t_line_id.FIRST..t_line_id.LAST
1148: UPDATE msc_supdem_lines_interface ln
1149: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1150: WHERE ln.parent_header_id = p_header_id AND
1151: ln.line_id = t_line_id(j) AND
1152: Upper(ln.sync_indicator) = 'D' AND

Line 1174: UPDATE msc_supdem_lines_interface ln

1170:
1171: IF t_tmp_line_id IS NOT NULL AND t_tmp_line_id.COUNT > 0 THEN
1172: forall j IN t_tmp_line_id.first..t_tmp_line_id.last
1173:
1174: UPDATE msc_supdem_lines_interface ln
1175: SET ln.key_date = Decode(t_tmp_ot(j),
1176: G_WORK_ORDER,ln.wip_end_date,
1177: G_PURCHASE_ORDER, -- jguo
1178: DECODE(ln.shipping_control,

Line 1238: UPDATE msc_supdem_lines_interface ln

1234: -- Validation: Check if key date <= new schedule end date
1235: --======================================================================
1236: l_err_msg := get_message('MSC', 'MSC_X_INVALID_END_DATE', p_language);
1237: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1238: UPDATE msc_supdem_lines_interface ln
1239: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1240: WHERE ln.parent_header_id = p_header_id AND
1241: ln.line_id = t_line_id(j) AND
1242: Upper(ln.sync_indicator) = 'D' AND

Line 1254: UPDATE msc_supdem_lines_interface ln

1250: --=======================================================================
1251: -- End of Validations for sync indicator 'D'
1252: --=======================================================================
1253: forall j IN t_line_id.first..t_line_id.last
1254: UPDATE msc_supdem_lines_interface ln
1255: SET ln.row_status = g_failure
1256: WHERE ln.err_msg IS NOT NULL
1257: AND Upper(ln.sync_indicator) = 'D'
1258: AND ln.parent_header_id = p_header_id

Line 1262: UPDATE msc_supdem_lines_interface ln

1258: AND ln.parent_header_id = p_header_id
1259: AND ln.line_id = t_line_id(j);
1260:
1261: forall j IN t_line_id.first..t_line_id.last
1262: UPDATE msc_supdem_lines_interface ln
1263: SET ln.row_status = g_success
1264: WHERE ln.err_msg IS NULL
1265: AND Upper(ln.sync_indicator) = 'D'
1266: AND ln.parent_header_id = p_header_id

Line 1277: FROM msc_supdem_lines_interface

1273: --=======================================================================
1274: --Perform remaining validations for records with sync indicator equal to 'R'
1275: SELECT line_id
1276: BULK COLLECT INTO t_line_id
1277: FROM msc_supdem_lines_interface
1278: WHERE sync_indicator = 'R' AND
1279: NVL(row_status, G_PROCESS) = G_PROCESS AND
1280: parent_header_id = p_header_id AND
1281: line_id BETWEEN l_start_line and l_end_line;

Line 1289: UPDATE msc_supdem_lines_interface ln

1285: -- Validation: Check that exactly two parties are involved in the transaction
1286: --=======================================================================
1287: l_err_msg := get_message('MSC', 'MSC_X_INVALID_MULTIPLE_PARTIES', p_language);
1288: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1289: UPDATE msc_supdem_lines_interface ln
1290: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1291: WHERE ln.parent_header_id = p_header_id AND
1292: ln.line_id = t_line_id(j) AND
1293: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1303: UPDATE msc_supdem_lines_interface ln

1299: -- Validation: Check if posted quantity is positive
1300: --========================================================
1301: l_err_msg := get_message('MSC', 'MSC_X_INVALID_QUANTITY', p_language);
1302: FORALL j in t_line_id.FIRST..t_line_id.LAST
1303: UPDATE msc_supdem_lines_interface ln
1304: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1305: WHERE ln.parent_header_id = p_header_id AND
1306: ln.line_id = t_line_id(j) AND
1307: nVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1315: UPDATE msc_supdem_lines_interface ln

1311: -- Validation: Check if ship from company exists
1312: --========================================================
1313: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_FROM_PARTY', p_language);
1314: FORALL j in t_line_id.FIRST..t_line_id.LAST
1315: UPDATE msc_supdem_lines_interface ln
1316: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1317: WHERE ln.parent_header_id = p_header_id AND
1318: ln.line_id = t_line_id(j) AND
1319: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1330: UPDATE msc_supdem_lines_interface ln

1326: -- Validation: Check if ship to company exists
1327: --========================================================
1328: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_TO_PARTY', p_language);
1329: FORALL j in t_line_id.FIRST..t_line_id.LAST
1330: UPDATE msc_supdem_lines_interface ln
1331: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1332: WHERE ln.parent_header_id = p_header_id AND
1333: ln.line_id = t_line_id(j) AND
1334: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1345: UPDATE msc_supdem_lines_interface ln

1341: -- Validation: Check if end order publisher exists
1342: --========================================================
1343: l_err_msg := get_message('MSC', 'MSC_X_INVALID_END_ORDER_PUB', p_language);
1344: FORALL j in t_line_id.FIRST..t_line_id.LAST
1345: UPDATE msc_supdem_lines_interface ln
1346: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1347: WHERE ln.parent_header_id = p_header_id AND
1348: ln.line_id = t_line_id(j) AND
1349: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1360: UPDATE msc_supdem_lines_interface ln

1356: -- Validation: Check if end order type is valid
1357: --========================================================
1358: l_err_msg := get_message('MSC', 'MSC_X_INVALID_END_ORDER_TYPE', p_language);
1359: FORALL j in t_line_id.FIRST..t_line_id.LAST
1360: UPDATE msc_supdem_lines_interface ln
1361: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1362: WHERE ln.parent_header_id = p_header_id AND
1363: ln.line_id = t_line_id(j) AND
1364: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1369: UPDATE msc_supdem_lines_interface ln

1365: ln.pegging_order_identifier IS NOT NULL AND
1366: ln.end_order_type IS NULL;
1367:
1368: FORALL j in t_line_id.FIRST..t_line_id.LAST
1369: UPDATE msc_supdem_lines_interface ln
1370: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1371: WHERE ln.parent_header_id = p_header_id AND
1372: ln.line_id = t_line_id(j) AND
1373: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1388: UPDATE msc_supdem_lines_interface ln

1384: -- specified the default uom 'Ea' is used.
1385: --======================================================================
1386: l_err_msg := get_message('MSC', 'MSC_X_INVALID_UOM_CODE', p_language);
1387: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1388: UPDATE msc_supdem_lines_interface ln
1389: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1390: WHERE ln.parent_header_id = p_header_id AND
1391: ln.line_id = t_line_id(j) AND
1392: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1401: UPDATE msc_supdem_lines_interface ln

1397: --=======================================================================
1398: --End of independent errors for sync indicator R
1399: --=======================================================================
1400: forall j IN t_line_id.first..t_line_id.last
1401: UPDATE msc_supdem_lines_interface ln
1402: SET ln.row_status = g_failure
1403: WHERE ln.err_msg IS NOT NULL
1404: AND Upper(ln.sync_indicator) = 'R'
1405: AND ln.parent_header_id = p_header_id

Line 1417: msc_supdem_lines_interface ln

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
1421: nvl(ln.row_status, G_PROCESS) = G_PROCESS and

Line 1428: UPDATE msc_supdem_lines_interface ln

1424:
1425: IF t_line_id1 IS NOT NULL AND t_line_id1.COUNT > 0 THEN
1426: l_err_msg := get_message('MSC', 'MSC_X_INVALID_FEW_PARTIES', p_language);
1427: FORALL j in t_line_id1.FIRST..t_line_id1.LAST
1428: UPDATE msc_supdem_lines_interface ln
1429: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1430: WHERE ln.parent_header_id = p_header_id AND
1431: ln.line_id = t_line_id1(j) AND
1432: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1438: UPDATE msc_supdem_lines_interface ln

1434: ln.supplier_company IS NULL) AND
1435: t_order_type(j) NOT IN (g_unallocated_onhand, g_work_order, g_safety_stock, g_proj_avai_bal, G_CONS_ADVICE);
1436:
1437: FORALL j in t_line_id1.FIRST..t_line_id1.LAST
1438: UPDATE msc_supdem_lines_interface ln
1439: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1440: WHERE ln.parent_header_id = p_header_id AND
1441: ln.line_id = t_line_id1(j) AND
1442: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1449: UPDATE msc_supdem_lines_interface ln

1445: t_order_type(j) IN (g_unallocated_onhand, g_work_order, g_safety_stock, g_proj_avai_bal, G_CONS_ADVICE);
1446:
1447: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PROXY_SF', p_language);
1448: FORALL j in t_line_id1.FIRST..t_line_id1.LAST
1449: UPDATE msc_supdem_lines_interface ln
1450: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1451: WHERE ln.parent_header_id = p_header_id AND
1452: ln.line_id = t_line_id1(j) AND
1453: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1459: UPDATE msc_supdem_lines_interface ln

1455: ln.supplier_company <> ln.posting_party_name AND
1456: G_SALES_FORECAST = t_order_type(j);
1457:
1458: FORALL j in t_line_id1.FIRST..t_line_id1.LAST
1459: UPDATE msc_supdem_lines_interface ln
1460: SET ln.publisher_company = decode(t_order_type(j),
1461: G_SALES_FORECAST, ln.posting_party_name,
1462: G_ORDER_FORECAST, ln.customer_company,
1463: G_SUPPLY_COMMIT, ln.supplier_company,

Line 1537: UPDATE msc_supdem_lines_interface ln

1533: if (G_USER_IS_ADMIN <> SYS_YES) then
1534: /* only for users who do not have SC Admin Responsibility */
1535: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER', p_language);
1536: FORALL j IN t_line_id1.FIRST..t_line_id1.LAST
1537: UPDATE msc_supdem_lines_interface ln
1538: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
1539: ln.row_status = G_FAILURE
1540: WHERE ln.parent_header_id = p_header_id AND
1541: ln.line_id = t_line_id1(j) AND

Line 1560: UPDATE msc_supdem_lines_interface ln

1556: end if;
1557:
1558: /* sbala ADD CA */
1559: FORALL j in t_line_id1.FIRST..t_line_id1.LAST
1560: UPDATE msc_supdem_lines_interface ln
1561: SET ln.customer_company = decode(t_order_type(j),
1562: G_SALES_FORECAST,
1563: decode(ln.posting_party_name,
1564: ln.customer_company,

Line 1676: UPDATE msc_supdem_lines_interface ln

1672: /* sbala ADD CA */
1673:
1674: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER', p_language);
1675: FORALL j IN t_line_id1.FIRST..t_line_id1.LAST
1676: UPDATE msc_supdem_lines_interface ln
1677: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000),
1678: ln.row_status = G_FAILURE
1679: WHERE ln.parent_header_id = p_header_id AND
1680: ln.line_id = t_line_id1(j) AND

Line 1695: UPDATE msc_supdem_lines_interface ln

1691: -- Validation: Check if publisher site exists
1692: --======================================================================
1693: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUBLISHER_SITE', p_language);
1694: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1695: UPDATE msc_supdem_lines_interface ln
1696: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 1716: UPDATE msc_supdem_lines_interface ln

1712: -- is null for unallocated onhand records.
1713: --========================================================
1714: l_err_msg := get_message('MSC', 'MSC_X_NULL_SUBSCRIBER', p_language);
1715: FORALL j in t_line_id.FIRST..t_line_id.LAST
1716: UPDATE msc_supdem_lines_interface ln
1717: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1718: WHERE ln.parent_header_id = p_header_id AND
1719: ln.line_id = t_line_id(j) AND
1720: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1732: UPDATE msc_supdem_lines_interface ln

1728: flv.lookup_code in (G_UNALLOCATED_ONHAND,G_CONS_ADVICE,g_work_order,g_safety_stock,g_proj_avai_bal));
1729: /* sbala ADD CA */
1730: l_err_msg := get_message('MSC', 'MSC_X_INVALID_MULT_PARTIES_UO', p_language);
1731: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1732: UPDATE msc_supdem_lines_interface ln
1733: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1734: WHERE ln.parent_header_id = p_header_id AND
1735: ln.line_id = t_line_id(j) AND
1736: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1765: UPDATE msc_supdem_lines_interface ln

1761: -- Validation: Check if customer company exists
1762: --========================================================
1763: l_err_msg := get_message('MSC', 'MSC_X_INVALID_CUSTOMER', p_language);
1764: FORALL j in t_line_id.FIRST..t_line_id.LAST
1765: UPDATE msc_supdem_lines_interface ln
1766: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1767: WHERE ln.parent_header_id = p_header_id AND
1768: ln.line_id = t_line_id(j) AND
1769: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1787: UPDATE msc_supdem_lines_interface ln

1783: -- Validation: Check if customer site exists
1784: --========================================================
1785: l_err_msg := get_message('MSC', 'MSC_X_INVALID_CUST_SITE', p_language);
1786: FORALL j in t_line_id.FIRST..t_line_id.LAST
1787: UPDATE msc_supdem_lines_interface ln
1788: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1789: WHERE ln.parent_header_id = p_header_id AND
1790: ln.line_id = t_line_id(j) AND
1791: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1806: UPDATE msc_supdem_lines_interface ln

1802: -- Validation: Check if supplier company exists
1803: --========================================================
1804: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SUPPLIER', p_language);
1805: FORALL j in t_line_id.FIRST..t_line_id.LAST
1806: UPDATE msc_supdem_lines_interface ln
1807: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1808: WHERE ln.parent_header_id = p_header_id AND
1809: ln.line_id = t_line_id(j) AND
1810: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1827: UPDATE msc_supdem_lines_interface ln

1823: -- Validation: Check if supplier site exists
1824: --========================================================
1825: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SUPP_SITE', p_language);
1826: FORALL j in t_line_id.FIRST..t_line_id.LAST
1827: UPDATE msc_supdem_lines_interface ln
1828: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1829: WHERE ln.parent_header_id = p_header_id AND
1830: ln.line_id = t_line_id(j) AND
1831: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1846: UPDATE msc_supdem_lines_interface ln

1842: -- Validation: Check if ship from site exists
1843: --========================================================
1844: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_FROM_SITE', p_language);
1845: FORALL j in t_line_id.FIRST..t_line_id.LAST
1846: UPDATE msc_supdem_lines_interface ln
1847: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1848: WHERE ln.parent_header_id = p_header_id AND
1849: ln.line_id = t_line_id(j) AND
1850: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1865: UPDATE msc_supdem_lines_interface ln

1861: -- Validation: Check if ship to site exists
1862: --========================================================
1863: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_TO_SITE', p_language);
1864: FORALL j in t_line_id.FIRST..t_line_id.LAST
1865: UPDATE msc_supdem_lines_interface ln
1866: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1867: WHERE ln.parent_header_id = p_header_id AND
1868: ln.line_id = t_line_id(j) AND
1869: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1883: UPDATE msc_supdem_lines_interface ln

1879: -- Validation: Check if end order publisher site exists
1880: --========================================================
1881: l_err_msg := get_message('MSC', 'MSC_X_INV_END_ORD_PUB_SITE', p_language);
1882: FORALL j in t_line_id.FIRST..t_line_id.LAST
1883: UPDATE msc_supdem_lines_interface ln
1884: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1885: WHERE ln.parent_header_id = p_header_id AND
1886: ln.line_id = t_line_id(j) AND
1887: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 1901: update msc_supdem_lines_interface ln

1897: --Validation: Item check for records with sync indicator 'R'
1898: --========================================================================
1899: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ITEM', p_language);
1900: FORALL j IN t_line_id.FIRST..t_line_id.LAST
1901: update msc_supdem_lines_interface ln
1902: set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
1903: where ln.parent_header_id = p_header_id and
1904: ln.line_id = t_line_id(j) and
1905: NVL(ln.row_status, G_PROCESS) = G_PROCESS and

Line 2027: update msc_supdem_lines_interface ln

2023:
2024: IF t_order_type IS NOT NULL AND t_order_type.COUNT > 0 THEN
2025: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ITEM', p_language);
2026: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2027: update msc_supdem_lines_interface ln
2028: set ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2029: where ln.parent_header_id = p_header_id and
2030: ln.line_id = t_line_id(j) and
2031: NVL(ln.row_status, G_PROCESS) = G_PROCESS and

Line 2086: UPDATE msc_supdem_lines_interface ln

2082: -- should not be allowed to publish unallocated onhand
2083: --=========================================================
2084: l_err_msg := get_message('MSC', 'MSC_X_INVALID_PUB_UO', p_language);
2085: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2086: UPDATE msc_supdem_lines_interface ln
2087: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 2131: UPDATE msc_supdem_lines_interface ln

2127: AND cs.company_id = c.company_id
2128: AND c.company_id = 1);
2129:
2130: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2131: UPDATE msc_supdem_lines_interface ln
2132: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 2166: UPDATE msc_supdem_lines_interface ln

2162: -- Work order
2163: --======================================================================
2164: l_err_msg := get_message('MSC','MSC_X_INVALID_WIP_DATE', p_language);
2165: forall j IN t_line_id.first..t_line_id.last
2166: UPDATE msc_supdem_lines_interface ln
2167: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2168: WHERE ln.parent_header_id = p_header_id
2169: AND ln.line_id = t_line_id(j)
2170: AND Nvl(ln.row_status, g_process) = g_process

Line 2172: FROM msc_supdem_lines_interface ln1,

2168: WHERE ln.parent_header_id = p_header_id
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

Line 2182: UPDATE msc_supdem_lines_interface ln

2178: flv.language = p_language and
2179: flv.lookup_code = g_work_order);
2180:
2181: FORALL j in t_line_id.FIRST..t_line_id.LAST
2182: UPDATE msc_supdem_lines_interface ln
2183: SET ln.key_date = ln.wip_end_date
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

Line 2195: UPDATE msc_supdem_lines_interface ln

2191: flv.lookup_code = g_work_order);
2192:
2193: l_err_msg := get_message('MSC','MSC_X_INVALID_WIP_ST_DATE', p_language);
2194: forall j IN t_line_id.first..t_line_id.last
2195: UPDATE msc_supdem_lines_interface ln
2196: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2197: WHERE ln.parent_header_id = p_header_id
2198: AND ln.line_id = t_line_id(j)
2199: AND Nvl(ln.row_status, g_process) = g_process

Line 2224: UPDATE msc_supdem_lines_interface ln

2220: -- order type is historical sales, safety stock, allocated onhand
2221: --=================================================================================
2222: l_err_msg := get_message('MSC', 'MSC_X_INVALID_ACT_DATE', p_language);
2223: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2224: UPDATE msc_supdem_lines_interface ln
2225: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2226: WHERE ln.parent_header_id = p_header_id AND
2227: ln.line_id = t_line_id(j) AND
2228: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 2230: FROM msc_supdem_lines_interface ln1,

2226: WHERE ln.parent_header_id = p_header_id AND
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

Line 2242: UPDATE msc_supdem_lines_interface ln

2238: G_SAFETY_STOCK,
2239: G_ALLOC_ONHAND,
2240: G_PROJ_AVAI_BAL));
2241: FORALL j in t_line_id.FIRST..t_line_id.LAST
2242: UPDATE msc_supdem_lines_interface ln
2243: SET ln.key_date = ln.new_schedule_date,
2244: ln.key_end_date = ln.new_schedule_end_date
2245: WHERE ln.parent_header_id = p_header_id AND
2246: ln.line_id = t_line_id(j) AND

Line 2265: UPDATE msc_supdem_lines_interface ln

2261: -- sales order, ASN
2262: --==============================================================================
2263: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_RCPT_DATE', p_language);
2264: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2265: UPDATE msc_supdem_lines_interface ln
2266: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2267: WHERE ln.parent_header_id = p_header_id AND
2268: ln.line_id = t_line_id(j) AND
2269: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 2271: FROM msc_supdem_lines_interface ln1,

2267: WHERE ln.parent_header_id = p_header_id AND
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

Line 2283: FROM msc_supdem_lines_interface ln1,

2279: G_ASN,
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

Line 2303: UPDATE msc_supdem_lines_interface ln

2299: )
2300: ;
2301:
2302: FORALL j in t_line_id.FIRST..t_line_id.LAST
2303: UPDATE msc_supdem_lines_interface ln
2304: SET ln.key_date = NVL(ln.receipt_date, ln.ship_date), -- jguo NVL(ln.ship_date, ln.receipt_date),
2305: ln.key_end_date = ln.new_schedule_end_date
2306: WHERE ln.parent_header_id = p_header_id AND
2307: ln.line_id = t_line_id(j) AND

Line 2325: UPDATE msc_supdem_lines_interface ln

2321: );
2322:
2323:
2324: FORALL j in t_line_id.FIRST..t_line_id.LAST
2325: UPDATE msc_supdem_lines_interface ln
2326: SET ln.key_date = NVL(ln.ship_date, ln.receipt_date),
2327: ln.key_end_date = ln.new_schedule_end_date
2328: WHERE ln.parent_header_id = p_header_id AND
2329: ln.line_id = t_line_id(j) AND

Line 2348: UPDATE msc_supdem_lines_interface ln

2344: -- Validation: Check if key date <= new schedule end date
2345: --======================================================================
2346: l_err_msg := get_message('MSC', 'MSC_X_INVALID_END_DATE', p_language);
2347: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2348: UPDATE msc_supdem_lines_interface ln
2349: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2350: WHERE ln.parent_header_id = p_header_id AND
2351: ln.line_id = t_line_id(j) AND
2352: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 2363: UPDATE msc_supdem_lines_interface ln

2359: -- Check if the ship date > the receipt date
2360: --=========================================================================
2361: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_DATE1', p_language);
2362: FORALL j IN t_line_id.FIRST..t_line_id.LAST
2363: UPDATE msc_supdem_lines_interface ln
2364: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2365: WHERE ln.parent_header_id = p_header_id AND
2366: ln.line_id = t_line_id(j) AND
2367: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 2379: UPDATE msc_supdem_lines_interface ln

2375: -- Check if the SO has already been entered via iSP (PO-Ack validation)
2376: --=========================================================================
2377: l_err_msg := get_message('MSC', 'MSC_X_SO_EXISTS', p_language);
2378: FORALL j in t_line_id.FIRST..t_line_id.LAST
2379: UPDATE msc_supdem_lines_interface ln
2380: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
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

Line 2410: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_FAILURE (4) if

2406: AND nvl(sd.ack_flag, 'N') = 'Y');
2407:
2408: --=========================================================================
2409: -- End of Dependent Validations
2410: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_FAILURE (4) if
2411: -- the err_msg is not null
2412: --=========================================================================
2413: FORALL j in t_line_id.FIRST..t_line_id.LAST
2414: UPDATE msc_supdem_lines_interface ln

Line 2414: UPDATE msc_supdem_lines_interface ln

2410: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_FAILURE (4) if
2411: -- the err_msg is not null
2412: --=========================================================================
2413: FORALL j in t_line_id.FIRST..t_line_id.LAST
2414: UPDATE msc_supdem_lines_interface ln
2415: SET ln.row_status = G_FAILURE
2416: WHERE ln.parent_header_id = p_header_id AND
2417: ln.line_id = t_line_id(j) AND
2418: NVL(ln.row_status, G_PROCESS) = g_process AND

Line 2422: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_SUCCESS (3) if

2418: NVL(ln.row_status, G_PROCESS) = g_process AND
2419: ln.err_msg IS NOT NULL;
2420:
2421: --=========================================================================
2422: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_SUCCESS (3) if
2423: -- the err_msg is null
2424: --=========================================================================
2425: FORALL j in t_line_id.FIRST..t_line_id.LAST
2426: UPDATE msc_supdem_lines_interface ln

Line 2426: UPDATE msc_supdem_lines_interface ln

2422: -- Update row status in MSC_SUPDEM_LINES_INTERFACE to G_SUCCESS (3) if
2423: -- the err_msg is null
2424: --=========================================================================
2425: FORALL j in t_line_id.FIRST..t_line_id.LAST
2426: UPDATE msc_supdem_lines_interface ln
2427: SET ln.row_status = G_SUCCESS
2428: WHERE ln.parent_header_id = p_header_id AND
2429: ln.line_id = t_line_id(j) AND
2430: NVL(ln.row_status, G_PROCESS) = g_process AND

Line 2438: update msc_supdem_lines_interface l

2434: -- Post Validation steps
2435: -- Populate the item id column for validated records
2436: --=========================================================================
2437: FORALL j in t_line_id.FIRST..t_line_id.LAST
2438: update msc_supdem_lines_interface l
2439: set l.inventory_item_id =
2440: (select i.inventory_item_id
2441: from msc_items i,
2442: msc_supdem_lines_interface ln

Line 2442: msc_supdem_lines_interface ln

2438: update msc_supdem_lines_interface l
2439: set l.inventory_item_id =
2440: (select i.inventory_item_id
2441: from msc_items i,
2442: msc_supdem_lines_interface ln
2443: where i.item_name = ln.item_name and
2444: ln.parent_header_id = p_header_id AND
2445: ln.line_id = t_line_id(j) AND
2446: NVL(ln.row_status, G_PROCESS) = G_SUCCESS

Line 2454: msc_supdem_lines_interface ln

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
2458: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND

Line 2497: msc_supdem_lines_interface ln

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
2501: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND

Line 2531: msc_supdem_lines_interface ln

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
2535: NVL(ln.row_status, G_PROCESS) = G_SUCCESS AND

Line 2589: UPDATE msc_supdem_lines_interface ln

2585:
2586: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEYS_EXEC', p_language);
2587: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 then
2588: forall j IN t_pub.first..t_pub.LAST
2589: UPDATE msc_supdem_lines_interface ln
2590: SET ln.row_status = g_failure,
2591: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2592: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2593: FROM msc_supdem_lines_interface ln1

Line 2593: FROM msc_supdem_lines_interface ln1

2589: UPDATE msc_supdem_lines_interface ln
2590: SET ln.row_status = g_failure,
2591: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2592: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2593: FROM msc_supdem_lines_interface ln1
2594: WHERE ln1.parent_header_id = p_header_id
2595: AND Upper(ln1.publisher_company) = t_pub(j)
2596: AND Upper(ln1.publisher_site) = t_pub_site(j)
2597: AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2610: FROM msc_supdem_lines_interface ln2

2606: AND Nvl(Upper(ln1.pegging_order_identifier),-99) = Nvl(t_end_order_number(j),-99)
2607: AND Nvl(Upper(ln1.ref_release_number),-99) = Nvl(t_end_order_rel_number(j),-99)
2608: AND Nvl(Upper(ln1.ref_line_number),-99) = Nvl(t_end_order_line_number(j),-99)
2609: AND 1 < (SELECT COUNT(*)
2610: FROM msc_supdem_lines_interface ln2
2611: WHERE ln2.parent_header_id = p_header_id
2612: AND Upper(ln2.publisher_company) = t_pub(j)
2613: AND Upper(ln2.publisher_site) = t_pub_site(j)
2614: AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2642: UPDATE msc_supdem_lines_interface ln

2638:
2639: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEYS_WIP', p_language);
2640: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 THEN
2641: forall j IN t_pub.first..t_pub.last
2642: UPDATE msc_supdem_lines_interface ln
2643: SET ln.row_status = g_failure,
2644: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2645: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2646: FROM msc_supdem_lines_interface ln1

Line 2646: FROM msc_supdem_lines_interface ln1

2642: UPDATE msc_supdem_lines_interface ln
2643: SET ln.row_status = g_failure,
2644: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2645: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2646: FROM msc_supdem_lines_interface ln1
2647: WHERE ln1.parent_header_id = p_header_id
2648: AND Upper(ln1.publisher_company) = t_pub(j)
2649: AND Upper(ln1.publisher_site) = t_pub_site(j)
2650: AND Nvl(Upper(ln1.customer_company),-99) = Nvl(t_cust(j),-99)

Line 2656: FROM msc_supdem_lines_interface ln2

2652: AND Upper(ln1.order_type) = t_order_type_desc(j)
2653: AND ln1.inventory_item_id = t_item_id(j)
2654: AND Nvl(ln1.order_identifier, '-99') = Nvl(t_order_number(j), '-99')
2655: AND 1 < (SELECT COUNT(*)
2656: FROM msc_supdem_lines_interface ln2
2657: WHERE ln2.parent_header_id = p_header_id
2658: AND Upper(ln2.publisher_company) = t_pub(j)
2659: AND Upper(ln2.publisher_site) = t_pub_site(j)
2660: AND Nvl(Upper(ln2.customer_company),-99) = Nvl(t_cust(j),-99)

Line 2682: UPDATE msc_supdem_lines_interface ln

2678:
2679: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEY_PLANNING', p_language);
2680: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 then
2681: forall j IN t_pub.first..t_pub.LAST
2682: UPDATE msc_supdem_lines_interface ln
2683: SET ln.row_status = g_failure,
2684: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2685: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2686: FROM msc_supdem_lines_interface ln1

Line 2686: FROM msc_supdem_lines_interface ln1

2682: UPDATE msc_supdem_lines_interface ln
2683: SET ln.row_status = g_failure,
2684: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2685: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2686: FROM msc_supdem_lines_interface ln1
2687: WHERE ln1.parent_header_id = p_header_id
2688: AND Upper(ln1.publisher_company) = t_pub(j)
2689: AND Upper(ln1.publisher_site) = t_pub_site(j)
2690: AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2697: FROM msc_supdem_lines_interface ln2

2693: AND Nvl(Upper(ln1.customer_site),-99) = Nvl(t_cust_site(j),-99)
2694: AND Upper(ln1.order_type) = t_order_type_desc(j)
2695: AND ln1.inventory_item_id = t_item_id(j)
2696: AND 1 < (SELECT COUNT(*)
2697: FROM msc_supdem_lines_interface ln2
2698: WHERE ln2.parent_header_id = p_header_id
2699: AND Upper(ln2.publisher_company) = t_pub(j)
2700: AND Upper(ln2.publisher_site) = t_pub_site(j)
2701: AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2726: UPDATE msc_supdem_lines_interface ln

2722:
2723: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEY_PLANNING', p_language);
2724: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 then
2725: forall j IN t_pub.first..t_pub.LAST
2726: UPDATE msc_supdem_lines_interface ln
2727: SET ln.row_status = g_failure,
2728: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2729: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2730: FROM msc_supdem_lines_interface ln1

Line 2730: FROM msc_supdem_lines_interface ln1

2726: UPDATE msc_supdem_lines_interface ln
2727: SET ln.row_status = g_failure,
2728: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2729: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2730: FROM msc_supdem_lines_interface ln1
2731: WHERE ln1.parent_header_id = p_header_id
2732: AND Upper(ln1.publisher_company) = t_pub(j)
2733: AND Upper(ln1.publisher_site) = t_pub_site(j)
2734: AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2743: FROM msc_supdem_lines_interface ln2

2739: AND ln1.inventory_item_id = t_item_id(j)
2740: AND Nvl(Upper(ln1.bucket_type),1) = Nvl(t_bucket_type(j),1)
2741: AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
2742: AND 1 < (SELECT COUNT(*)
2743: FROM msc_supdem_lines_interface ln2
2744: WHERE ln2.parent_header_id = p_header_id
2745: AND Upper(ln2.publisher_company) = t_pub(j)
2746: AND Upper(ln2.publisher_site) = t_pub_site(j)
2747: AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2775: UPDATE msc_supdem_lines_interface ln

2771:
2772: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEY_PLANNING', p_language);
2773: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 then
2774: forall j IN t_pub.first..t_pub.LAST
2775: UPDATE msc_supdem_lines_interface ln
2776: SET ln.row_status = g_failure,
2777: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2778: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2779: FROM msc_supdem_lines_interface ln1

Line 2779: FROM msc_supdem_lines_interface ln1

2775: UPDATE msc_supdem_lines_interface ln
2776: SET ln.row_status = g_failure,
2777: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2778: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2779: FROM msc_supdem_lines_interface ln1
2780: WHERE ln1.parent_header_id = p_header_id
2781: AND Upper(ln1.publisher_company) = t_pub(j)
2782: AND Upper(ln1.publisher_site) = t_pub_site(j)
2783: AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2792: FROM msc_supdem_lines_interface ln2

2788: AND ln1.inventory_item_id = t_item_id(j)
2789: AND Upper(ln1.bucket_type) = t_bucket_type(j)
2790: AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
2791: AND 1 < (SELECT COUNT(*)
2792: FROM msc_supdem_lines_interface ln2
2793: WHERE ln2.parent_header_id = p_header_id
2794: AND Upper(ln2.publisher_company) = t_pub(j)
2795: AND Upper(ln2.publisher_site) = t_pub_site(j)
2796: AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2824: UPDATE msc_supdem_lines_interface ln

2820:
2821: l_err_msg := get_message('MSC', 'MSC_X_DUPLICATE_KEY_PLANNING', p_language);
2822: IF t_pub IS NOT NULL AND t_pub.COUNT > 0 then
2823: forall j IN t_pub.first..t_pub.LAST
2824: UPDATE msc_supdem_lines_interface ln
2825: SET ln.row_status = g_failure,
2826: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2827: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2828: FROM msc_supdem_lines_interface ln1

Line 2828: FROM msc_supdem_lines_interface ln1

2824: UPDATE msc_supdem_lines_interface ln
2825: SET ln.row_status = g_failure,
2826: ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
2827: WHERE ln.line_id IN (SELECT DISTINCT ln1.line_id
2828: FROM msc_supdem_lines_interface ln1
2829: WHERE ln1.parent_header_id = p_header_id
2830: AND Upper(ln1.publisher_company) = t_pub(j)
2831: AND Upper(ln1.publisher_site) = t_pub_site(j)
2832: AND Nvl(Upper(ln1.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2841: FROM msc_supdem_lines_interface ln2

2837: AND ln1.inventory_item_id = t_item_id(j)
2838: AND Upper(ln1.bucket_type) = t_bucket_type(j)
2839: AND t_key_date(j) = trunc(to_date(ln1.key_date,p_date_format))
2840: AND 1 < (SELECT COUNT(*)
2841: FROM msc_supdem_lines_interface ln2
2842: WHERE ln2.parent_header_id = p_header_id
2843: AND Upper(ln2.publisher_company) = t_pub(j)
2844: AND Upper(ln2.publisher_site) = t_pub_site(j)
2845: AND Nvl(Upper(ln2.supplier_company),-99) = Nvl(t_supp(j),-99)

Line 2994: FROM msc_supdem_lines_interface ln

2990: x_date6,
2991: x_date7,
2992: x_date8,
2993: x_date9
2994: FROM msc_supdem_lines_interface ln
2995: WHERE ln.parent_header_id = p_header_id and
2996: ln.line_id = p_line_id;
2997:
2998: return 0;

Line 3123: FROM msc_supdem_lines_interface

3119: substrb(order_type,1,240),
3120: order_identifier,
3121: release_number,
3122: line_number
3123: FROM msc_supdem_lines_interface
3124: WHERE parent_header_id = header_id AND
3125: row_status = G_FAILURE;
3126:
3127: err_buf VARCHAR2(2000) := NULL;

Line 3139: FROM msc_supdem_lines_interface

3135: line_number VARCHAR2(20);
3136: BEGIN
3137: SELECT min(line_id)
3138: INTO min_line_id
3139: FROM msc_supdem_lines_interface
3140: WHERE parent_header_id = p_header_id;
3141:
3142: OPEN errors(p_header_id);
3143: LOOP

Line 3211: from msc_supdem_lines_interface ln,

3207: p_end_line NUMBER,
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

Line 3271: from msc_supdem_lines_interface ln,

3267: and decode(ln.new_schedule_end_date, NULL, sd.key_date,trunc(to_date(ln.new_schedule_end_date, p_date_format)))
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

Line 3318: from msc_supdem_lines_interface ln,

3314: and NVL(sd.order_number, G_NULL_STRING) = NVL(ln.order_identifier,Nvl(sd.order_number,G_NULL_STRING))
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

Line 3564: FROM msc_supdem_lines_interface ln,

3560: ln.attribute13,
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

Line 3684: FROM msc_supdem_lines_interface ln,

3680: ln.attribute13,
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,

Line 3806: FROM msc_supdem_lines_interface ln,

3802: ln.attribute13,
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

Line 3921: FROM msc_supdem_lines_interface ln,

3917: ln.attribute13,
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,

Line 4039: FROM msc_supdem_lines_interface ln,

4035: ln.attribute13,
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

Line 4237: FROM msc_supdem_lines_interface ln,

4233: ln.attribute13,
4234: ln.attribute14,
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,

Line 4379: FROM msc_supdem_lines_interface ln,

4375: ln.attribute13,
4376: ln.attribute14,
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,

Line 4581: FROM msc_supdem_lines_interface ln,

4577: ln.attribute13,
4578: ln.attribute14,
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,

Line 4723: FROM msc_supdem_lines_interface ln,

4719: ln.attribute13,
4720: ln.attribute14,
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,

Line 4925: FROM msc_supdem_lines_interface ln,

4921: ln.attribute13,
4922: ln.attribute14,
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,

Line 5066: FROM msc_supdem_lines_interface ln,

5062: ln.attribute13,
5063: ln.attribute14,
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,

Line 5116: msc_supdem_lines_interface ln

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
5120: AND cu.user_id = u.user_id

Line 5134: FROM msc_supdem_lines_interface ln,

5130: SELECT ln.parent_header_id,
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

Line 5318: msc_supdem_lines_interface l

5314: l.created_by
5315: INTO l_user_name,
5316: l_user_id
5317: FROM fnd_user u,
5318: msc_supdem_lines_interface l
5319: WHERE u.user_id = l.created_by and
5320: l.parent_header_id = p_header_id;
5321:
5322: /* BUG #3845796 :Using Applications Session Language in preference to ICX_LANGUAGE profile value */

Line 5378: FROM msc_supdem_lines_interface

5374:
5375: BEGIN
5376: SELECT min(line_id), max(line_id)
5377: INTO l_min, l_max
5378: FROM msc_supdem_lines_interface
5379: WHERE parent_header_id = p_header_id;
5380: EXCEPTION
5381: WHEN NO_DATA_FOUND THEN
5382: p_status := 0;

Line 7107: FROM msc_supdem_lines_interface

7103: SELECT 1 INTO l_error_count
7104: FROM dual
7105: WHERE exists(
7106: SELECT 'exists'
7107: FROM msc_supdem_lines_interface
7108: WHERE parent_header_id = p_header_id AND
7109: row_status IN (G_PROCESS,G_FAILURE)
7110: );
7111: EXCEPTION

Line 7125: FROM msc_supdem_lines_interface

7121: if l_error_count > 0 THEN
7122: p_status := 1;
7123: if p_build_err = 2 then
7124: SELECT err_msg INTO p_err_msg
7125: FROM msc_supdem_lines_interface
7126: WHERE parent_header_id = p_header_id;
7127: else
7128: send_ntf(p_header_id, l_file_name, G_FAILURE, l_user_name, l_event_key);
7129: end if;

Line 7224: from msc_supdem_lines_interface ln,

7220: into l_comp_avg_dmd
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'

Line 7278: from msc_supdem_lines_interface ln,

7274: into l_order_type_flag
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'

Line 7497: msc_supdem_lines_interface ln

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
7501: ln.parent_header_id = p_header_id and

Line 7517: msc_supdem_lines_interface l

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);
7521: EXCEPTION

Line 7539: msc_supdem_lines_interface l

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);
7543: EXCEPTION

Line 7561: msc_supdem_lines_interface l

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);
7565: EXCEPTION

Line 7582: msc_supdem_lines_interface l

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
7586: l.line_id = t_line_id(j);

Line 7606: msc_supdem_lines_interface l

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
7610: l.line_id = t_line_id(j);

Line 7630: msc_supdem_lines_interface l

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
7634: l.line_id = t_line_id(j);

Line 9111: msc_supdem_lines_interface ln

9107: sd1.last_updated_by,
9108: ln.sync_indicator, --Fix for bug 6147298
9109: ln.quantity
9110: from msc_sup_dem_entries sd1,
9111: msc_supdem_lines_interface ln
9112: where sd1.ref_header_id = p_header_id and
9113: sd1.plan_id = -1 and
9114: sd1.publisher_order_type = 15 and --ASN
9115: sd1.end_order_type = 13 and --PO

Line 10529: UPDATE msc_supdem_lines_interface ln

10525: --======================================================================
10526: l_err_msg := get_message('MSC', 'MSC_X_INVALID_RCPT_DATE', p_language);
10527: --dbms_output.put_line( 'In ' || l_err_msg);
10528: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10529: UPDATE msc_supdem_lines_interface ln
10530: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10531: WHERE ln.parent_header_id = p_header_id AND
10532: ln.line_id = t_line_id(j) AND
10533: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10535: FROM msc_supdem_lines_interface ln1,

10531: WHERE ln.parent_header_id = p_header_id AND
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

Line 10555: UPDATE msc_supdem_lines_interface ln

10551: )
10552: ;
10553:
10554: FORALL j in t_line_id.FIRST..t_line_id.LAST
10555: UPDATE msc_supdem_lines_interface ln
10556: SET ln.key_date = ln.receipt_date,
10557: ln.key_end_date = ln.new_schedule_end_date
10558: WHERE ln.parent_header_id = p_header_id AND
10559: ln.line_id = t_line_id(j) AND

Line 10584: UPDATE msc_supdem_lines_interface ln

10580: -- projected safety stock and projected available balances.
10581: --======================================================================
10582: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_DATE', p_language);
10583: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10584: UPDATE msc_supdem_lines_interface ln
10585: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10586: WHERE ln.parent_header_id = p_header_id AND
10587: ln.line_id = t_line_id(j) AND
10588: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10590: FROM msc_supdem_lines_interface ln1,

10586: WHERE ln.parent_header_id = p_header_id AND
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

Line 10610: UPDATE msc_supdem_lines_interface ln

10606: )
10607: ;
10608:
10609: FORALL j in t_line_id.FIRST..t_line_id.LAST
10610: UPDATE msc_supdem_lines_interface ln
10611: SET ln.key_date = ln.ship_date,
10612: ln.key_end_date = ln.new_schedule_end_date
10613: WHERE ln.parent_header_id = p_header_id AND
10614: ln.line_id = t_line_id(j) AND

Line 10639: UPDATE msc_supdem_lines_interface ln

10635: -- populated if the order type = POA
10636: --==============================================================================
10637: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_RCPT_DATE', p_language);
10638: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10639: UPDATE msc_supdem_lines_interface ln
10640: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10641: WHERE ln.parent_header_id = p_header_id AND
10642: ln.line_id = t_line_id(j) AND
10643: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10645: FROM msc_supdem_lines_interface ln1,

10641: WHERE ln.parent_header_id = p_header_id AND
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

Line 10654: FROM msc_supdem_lines_interface ln1,

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

Line 10675: UPDATE msc_supdem_lines_interface ln

10671: ---and key_date is populated there
10672: -----------------------------------------------------------------------------------------------------------
10673:
10674: FORALL j in t_line_id.FIRST..t_line_id.LAST
10675: UPDATE msc_supdem_lines_interface ln
10676: SET ln.key_date = ln.receipt_date,
10677: ln.key_end_date = ln.new_schedule_end_date
10678: WHERE ln.parent_header_id = p_header_id AND
10679: ln.line_id = t_line_id(j) AND

Line 10716: UPDATE msc_supdem_lines_interface ln

10712: --======================================================================
10713: l_err_msg := get_message('MSC', 'MSC_X_INVALID_RCPT_DATE', p_language);
10714: --dbms_output.put_line( 'In ' || l_err_msg);
10715: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10716: UPDATE msc_supdem_lines_interface ln
10717: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10718: WHERE ln.parent_header_id = p_header_id AND
10719: ln.line_id = t_line_id(j) AND
10720: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10722: FROM msc_supdem_lines_interface ln1,

10718: WHERE ln.parent_header_id = p_header_id AND
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

Line 10752: UPDATE msc_supdem_lines_interface ln

10748: )
10749: ;
10750:
10751: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10752: UPDATE msc_supdem_lines_interface ln
10753: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10754: WHERE ln.parent_header_id = p_header_id AND
10755: ln.line_id = t_line_id(j) AND
10756: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10758: FROM msc_supdem_lines_interface ln1,

10754: WHERE ln.parent_header_id = p_header_id 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

Line 10777: UPDATE msc_supdem_lines_interface ln

10773: ;
10774:
10775:
10776: FORALL j in t_line_id.FIRST..t_line_id.LAST
10777: UPDATE msc_supdem_lines_interface ln
10778: SET ln.key_date = ln.receipt_date,
10779: ln.key_end_date = ln.new_schedule_end_date
10780: , ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
10781: ( NVL(ln.customer_company, ln.publisher_company)

Line 10816: UPDATE msc_supdem_lines_interface ln

10812: )
10813: ;
10814:
10815: FORALL j in t_line_id.FIRST..t_line_id.LAST
10816: UPDATE msc_supdem_lines_interface ln
10817: SET ln.key_date = ln.receipt_date,
10818: ln.key_end_date = ln.new_schedule_end_date
10819: , ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
10820: ( NVL(ln.customer_company, ln.publisher_company)

Line 10849: UPDATE msc_supdem_lines_interface ln

10845: -- projected safety stock and projected available balances.
10846: --======================================================================
10847: l_err_msg := get_message('MSC', 'MSC_X_INVALID_SHIP_DATE', p_language);
10848: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10849: UPDATE msc_supdem_lines_interface ln
10850: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10851: WHERE ln.parent_header_id = p_header_id AND
10852: ln.line_id = t_line_id(j) AND
10853: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10855: FROM msc_supdem_lines_interface ln1,

10851: WHERE ln.parent_header_id = p_header_id AND
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

Line 10889: UPDATE msc_supdem_lines_interface ln

10885: )
10886: ;
10887:
10888: FORALL j IN t_line_id.FIRST..t_line_id.LAST
10889: UPDATE msc_supdem_lines_interface ln
10890: SET ln.err_msg = substrb(ln.err_msg || ' ' || l_err_msg,1,1000)
10891: WHERE ln.parent_header_id = p_header_id AND
10892: ln.line_id = t_line_id(j) AND
10893: NVL(ln.row_status, G_PROCESS) = G_PROCESS AND

Line 10895: FROM msc_supdem_lines_interface ln1,

10891: WHERE ln.parent_header_id = p_header_id 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

Line 10913: UPDATE msc_supdem_lines_interface ln

10909: )
10910: ;
10911:
10912: FORALL j in t_line_id.FIRST..t_line_id.LAST
10913: UPDATE msc_supdem_lines_interface ln
10914: SET ln.key_date = ln.ship_date,
10915: ln.key_end_date = ln.new_schedule_end_date
10916: , ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
10917: ( NVL(ln.customer_company, ln.publisher_company)

Line 10956: UPDATE msc_supdem_lines_interface ln

10952: )
10953: ;
10954:
10955: FORALL j in t_line_id.FIRST..t_line_id.LAST
10956: UPDATE msc_supdem_lines_interface ln
10957: SET ln.key_date = ln.ship_date,
10958: ln.key_end_date = ln.new_schedule_end_date
10959: , ln.shipping_control = MSC_X_UTIL.GET_SHIPPING_CONTROL
10960: ( NVL(ln.customer_company, ln.publisher_company)