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_CL_RPO_PRE_PROCESS dependencies on MSC_ST_SUPPLIES

Line 12: lv_batch_id msc_st_supplies.batch_id%TYPE;

8: lv_error_text VARCHAR2(250);
9: lv_where_str VARCHAR2(5000);
10: lv_sql_stmt VARCHAR2(5000);
11: lv_column_names VARCHAR2(5000);
12: lv_batch_id msc_st_supplies.batch_id%TYPE;
13: lv_message_text msc_errors.error_text%TYPE;
14:
15: ex_logging_err EXCEPTION;
16:

Line 19: FROM msc_st_supplies

15: ex_logging_err EXCEPTION;
16:
17: CURSOR c1(p_batch_id NUMBER) IS
18: SELECT rowid
19: FROM msc_st_supplies
20: WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS)
21: AND batch_id = p_batch_id
22: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
23:

Line 26: FROM msc_st_supplies

22: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
23:
24: CURSOR c2(p_batch_id NUMBER) IS
25: SELECT rowid
26: FROM msc_st_supplies
27: WHERE NVL(disposition_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
28: AND order_type =75
29: AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
30: AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 51: ' UPDATE msc_st_supplies mss1'

47:
48: --Duplicate records check for the records whose source is XML
49: MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
50: lv_sql_stmt :=
51: ' UPDATE msc_st_supplies mss1'
52: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
53: ||' error_text = '||''''||lv_message_text||''''
54: ||' WHERE message_id < (SELECT MAX(message_id)'
55: ||' FROM msc_st_supplies mss2'

Line 55: ||' FROM msc_st_supplies mss2'

51: ' UPDATE msc_st_supplies mss1'
52: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
53: ||' error_text = '||''''||lv_message_text||''''
54: ||' WHERE message_id < (SELECT MAX(message_id)'
55: ||' FROM msc_st_supplies mss2'
56: ||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
57: ||' AND mss2.repair_number = mss1.repair_number '
58: ||' AND mss2.order_type = mss1.order_type'
59: ||' AND mss2.organization_code = mss1.organization_code'

Line 92: 'UPDATE msc_st_supplies mss1 '

88: --Different SQL is used because in XML we can identify the latest records
89: --whereas in batch load we cannot.
90: MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
91: lv_sql_stmt :=
92: 'UPDATE msc_st_supplies mss1 '
93: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
94: ||' error_text = '||''''||lv_message_text||''''
95: ||' WHERE EXISTS( SELECT 1 '
96: ||' FROM msc_st_supplies mss2'

Line 96: ||' FROM msc_st_supplies mss2'

92: 'UPDATE msc_st_supplies mss1 '
93: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
94: ||' error_text = '||''''||lv_message_text||''''
95: ||' WHERE EXISTS( SELECT 1 '
96: ||' FROM msc_st_supplies mss2'
97: ||' WHERE mss2.sr_instance_code = mss1.sr_instance_code'
98: ||' AND mss2.repair_number = mss1.repair_number '
99: ||' AND mss2.order_type = mss1.order_type'
100: ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 146: ' UPDATE msc_st_supplies '

142: FROM dual;
143:
144: MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
145: lv_sql_stmt :=
146: ' UPDATE msc_st_supplies '
147: ||' SET batch_id = :lv_batch_id'
148: ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||')'
149: ||' AND order_type IN (75)'
150: ||' AND sr_instance_code = :v_instance_code'

Line 171: UPDATE msc_st_supplies

167: CLOSE c1;
168:
169: MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
170: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
171: UPDATE msc_st_supplies
172: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
173: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
174: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
175: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,

Line 172: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,

168:
169: MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
170: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
171: UPDATE msc_st_supplies
172: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
173: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
174: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
175: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
176: creation_date = MSC_CL_PRE_PROCESS.v_current_date,

Line 202: (p_table_name => 'MSC_ST_SUPPLIES',

198: ||' NOT IN(1,2)';
199: --Log a warning for those records where the deleted_flag has a value other
200: --than SYS_NO
201: lv_return := MSC_ST_UTIL.LOG_ERROR
202: (p_table_name => 'MSC_ST_SUPPLIES',
203: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
204: p_row => lv_column_names,
205: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
206: p_message_text => lv_message_text,

Line 232: (p_table_name => 'MSC_ST_SUPPLIES',

228: END IF;
229:
230: --Derive Organization_id
231: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
232: (p_table_name => 'MSC_ST_SUPPLIES',
233: p_org_partner_name => 'ORGANIZATION_CODE',
234: p_org_partner_id => 'ORGANIZATION_ID',
235: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
236: p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,

Line 262: (p_table_name => 'MSC_ST_SUPPLIES',

258: END IF;
259:
260: --Derive Inventory_item_id
261: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
262: (p_table_name => 'MSC_ST_SUPPLIES',
263: p_item_col_name => 'ITEM_NAME',
264: p_item_col_id => 'INVENTORY_ITEM_ID',
265: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
266: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,

Line 293: 'UPDATE msc_st_supplies '

289: END IF;
290:
291: MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
292: lv_sql_stmt :=
293: 'UPDATE msc_st_supplies '
294: ||' SET error_text = '||''''||lv_message_text||''''||','
295: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
296: ||' WHERE new_order_quantity is null '
297: ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO

Line 325: 'UPDATE msc_st_supplies '

321: END IF;
322:
323: MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
324: lv_sql_stmt :=
325: 'UPDATE msc_st_supplies '
326: ||' SET error_text = '||''''||lv_message_text||''''||','
327: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
328: ||' WHERE new_schedule_date is null'
329: ||' AND ro_creation_date is null'

Line 358: 'UPDATE msc_st_supplies '

354: END IF;
355:
356: MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
357: lv_sql_stmt :=
358: 'UPDATE msc_st_supplies '
359: ||' SET error_text = '||''''||lv_message_text||''''||','
360: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
361: ||' WHERE repair_number is NULL'
362: ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 378: 'UPDATE msc_st_supplies mss'

374: --- derive repair line id
375:
376: MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
377: lv_sql_stmt :=
378: 'UPDATE msc_st_supplies mss'
379: ||' SET disposition_id = (SELECT local_id'
380: ||' FROM msc_local_id_supply mls'
381: ||' WHERE mls.char4 = mss.repair_number'
382: ||' AND mls.char3 = mss.organization_code'

Line 412: 'UPDATE msc_st_supplies '

408: END IF;
409: MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
410:
411: lv_sql_stmt :=
412: 'UPDATE msc_st_supplies '
413: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
414: ||' error_text = '||''''||lv_message_text||''''
415: ||' WHERE disposition_id is null '
416: ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES

Line 440: p_token_value3 => 'MSC_ST_SUPPLIES');

436: p_token_value1 => 'SR_INSTANCE_CODE ,UOM_CODE',
437: p_token2 => 'MASTER_TABLE',
438: p_token_value2 => 'MSC_ST_UNITS_OF_MEASURE',
439: p_token3 => 'CHILD_TABLE',
440: p_token_value3 => 'MSC_ST_SUPPLIES');
441: IF lv_return <> 0 THEN
442: RAISE ex_logging_err;
443: END IF;
444:

Line 447: ' UPDATE MSC_ST_SUPPLIES mic'

443: END IF;
444:
445: MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
446: lv_sql_stmt :=
447: ' UPDATE MSC_ST_SUPPLIES mic'
448: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
449: ||' error_text = '||''''||lv_message_text||''''
450: ||' WHERE NOT EXISTS (SELECT 1 '
451: ||' FROM msc_units_of_measure muom'

Line 477: UPDATE msc_st_supplies

473: OPEN c2(lv_batch_id);
474: FETCH c2 BULK COLLECT INTO lb_rowid ;
475: if c2%ROWCOUNT >0 THEN
476: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
477: UPDATE msc_st_supplies
478: SET disposition_id = msc_st_iro_supply_s.NEXTVAL
479: WHERE rowid = lb_rowid(j);
480:
481: MSC_CL_PRE_PROCESS.v_sql_stmt := 13;

Line 521: FROM msc_st_supplies

517: MSC_CL_PRE_PROCESS.v_current_date,
518: MSC_CL_PRE_PROCESS.v_current_user,
519: MSC_CL_PRE_PROCESS.v_current_date,
520: MSC_CL_PRE_PROCESS.v_current_user
521: FROM msc_st_supplies
522: WHERE rowid = lb_rowid(j);
523: END IF ;
524: close c2;
525: ---- validating project and task :

Line 527: (p_table_name => 'MSC_ST_SUPPLIES',

523: END IF ;
524: close c2;
525: ---- validating project and task :
526: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
527: (p_table_name => 'MSC_ST_SUPPLIES',
528: p_proj_col_name => 'PROJECT_NUMBER',
529: p_proj_task_col_id => 'PROJECT_ID',
530: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
531: p_entity_name => 'PROJECT_ID',

Line 560: (p_table_name => 'MSC_ST_SUPPLIES',

556: END IF;
557:
558: --Derive Task Id.
559: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
560: (p_table_name => 'MSC_ST_SUPPLIES',
561: p_proj_col_name => 'PROJECT_NUMBER',
562: p_proj_task_col_id => 'TASK_ID',
563: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
564: p_entity_name => 'TASK_ID',

Line 584: pEntityName => 'MSC_ST_SUPPLIES',

580: (ERRBUF => lv_error_text,
581: RETCODE => lv_return,
582: pBatchID => lv_batch_id,
583: pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
584: pEntityName => 'MSC_ST_SUPPLIES',
585: pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
586:
587: IF NVL(lv_return,0) <> 0 THEN
588: RAISE ex_logging_err;

Line 592: (p_table_name => 'MSC_ST_SUPPLIES',

588: RAISE ex_logging_err;
589: END IF;
590:
591: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
592: (p_table_name => 'MSC_ST_SUPPLIES',
593: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
594: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
595: p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
596: p_error_text => lv_error_text,

Line 605: (p_table_name => 'MSC_ST_SUPPLIES',

601: RAISE ex_logging_err;
602: END IF;
603:
604: lv_return := MSC_ST_UTIL.LOG_ERROR
605: (p_table_name => 'MSC_ST_SUPPLIES',
606: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
607: p_row => lv_column_names,
608: p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
609: p_message_text => NULL,

Line 623: lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_SUPPLIES'||'('

619:
620: EXCEPTION
621:
622: WHEN too_many_rows THEN
623: lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_SUPPLIES'||'('
624: ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
625: ROLLBACK ;
626:
627: WHEN ex_logging_err THEN

Line 632: lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_SUPPLIES '||'('

628: msc_st_util.log_message(lv_error_text);
629: ROLLBACK;
630:
631: WHEN OTHERS THEN
632: lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_SUPPLIES '||'('
633: ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
634: msc_st_util.log_message(lv_error_text);
635: ROLLBACK;
636:

Line 1171: lv_batch_id msc_st_supplies.batch_id%TYPE;

1167: lv_where_str VARCHAR2(5000);
1168: lv_sql_stmt VARCHAR2(5000);
1169: lv_column_names VARCHAR2(5000); --stores concatenated column names
1170: lv_message_text msc_errors.error_text%TYPE;
1171: lv_batch_id msc_st_supplies.batch_id%TYPE;
1172: ex_logging_err EXCEPTION;
1173:
1174: CURSOR c1(p_batch_id NUMBER) IS
1175: SELECT rowid

Line 1176: FROM msc_st_supplies

1172: ex_logging_err EXCEPTION;
1173:
1174: CURSOR c1(p_batch_id NUMBER) IS
1175: SELECT rowid
1176: FROM msc_st_supplies
1177: WHERE order_type =86
1178: AND process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
1179: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE)=p_batch_id
1180: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 1184: FROM msc_st_supplies

1180: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1181:
1182: CURSOR c2(p_batch_id NUMBER) IS
1183: SELECT rowid
1184: FROM msc_st_supplies
1185: WHERE NVL(wip_entity_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1186: AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1187: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
1188: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 1192: FROM msc_st_supplies

1188: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1189:
1190: CURSOR c3(p_batch_id NUMBER) IS
1191: SELECT max(rowid)
1192: FROM msc_st_supplies
1193: WHERE NVL(schedule_group_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1194: AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
1195: AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1196: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = p_batch_id

Line 1202: FROM msc_st_supplies

1198: GROUP BY sr_instance_code,company_name,organization_code,schedule_group_name;
1199:
1200: CURSOR c4(p_batch_id NUMBER) IS
1201: SELECT rowid
1202: FROM msc_st_supplies
1203: WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1204: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
1205: AND batch_id = p_batch_id
1206: AND NVL(JOB_OP_SEQ_NUM, MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE

Line 1227: 'UPDATE msc_st_supplies mss1'

1223: --Duplicate records check for the records whose source is XML for
1224: --WO supplies
1225: MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
1226: lv_sql_stmt :=
1227: 'UPDATE msc_st_supplies mss1'
1228: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1229: ||' error_text = '||''''||lv_message_text||''''
1230: ||' WHERE message_id < (SELECT MAX(message_id)'
1231: ||' FROM msc_st_supplies mss2'

Line 1231: ||' FROM msc_st_supplies mss2'

1227: 'UPDATE msc_st_supplies mss1'
1228: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1229: ||' error_text = '||''''||lv_message_text||''''
1230: ||' WHERE message_id < (SELECT MAX(message_id)'
1231: ||' FROM msc_st_supplies mss2'
1232: ||' WHERE mss2.sr_instance_code'
1233: ||' = mss1.sr_instance_code'
1234: ||' AND NVL(mss2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1235: ||' NVL(mss1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'

Line 1269: 'UPDATE msc_st_supplies mss1 '

1265: --Different SQL is used because in XML we can identify the latest records
1266: --whereas in batch load we cannot.
1267: MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
1268: lv_sql_stmt :=
1269: 'UPDATE msc_st_supplies mss1 '
1270: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1271: ||' error_text = '||''''||lv_message_text||''''
1272: ||' WHERE EXISTS( SELECT 1 '
1273: ||' FROM msc_st_supplies mss2'

Line 1273: ||' FROM msc_st_supplies mss2'

1269: 'UPDATE msc_st_supplies mss1 '
1270: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1271: ||' error_text = '||''''||lv_message_text||''''
1272: ||' WHERE EXISTS( SELECT 1 '
1273: ||' FROM msc_st_supplies mss2'
1274: ||' WHERE mss2.sr_instance_code'
1275: ||' = mss1.sr_instance_code'
1276: ||' AND NVL(mss2.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')= '
1277: ||' NVL(mss1.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'

Line 1338: ' UPDATE msc_st_supplies '

1334: FROM dual;
1335:
1336: MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1337: lv_sql_stmt :=
1338: ' UPDATE msc_st_supplies '
1339: ||' SET batch_id = :lv_batch_id'
1340: ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
1341: ||' AND order_type =86'
1342: ||' AND sr_instance_code = :v_instance_code'

Line 1362: UPDATE msc_st_supplies

1358: CLOSE c1;
1359:
1360: MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1361: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1362: UPDATE msc_st_supplies
1363: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
1364: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
1365: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
1366: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,

Line 1363: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,

1359:
1360: MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1361: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1362: UPDATE msc_st_supplies
1363: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
1364: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
1365: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
1366: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
1367: creation_date = MSC_CL_PRE_PROCESS.v_current_date,

Line 1390: (p_table_name => 'MSC_ST_SUPPLIES',

1386: ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
1387: --Log a warning for those records where the deleted_flag has a value other
1388: --SYS_NO
1389: lv_return := MSC_ST_UTIL.LOG_ERROR
1390: (p_table_name => 'MSC_ST_SUPPLIES',
1391: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1392: p_row => lv_column_names,
1393: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1394: p_message_text => lv_message_text,

Line 1420: (p_table_name => 'MSC_ST_SUPPLIES',

1416: END IF;
1417:
1418: --Derive Organization_id
1419: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
1420: (p_table_name => 'MSC_ST_SUPPLIES',
1421: p_org_partner_name => 'ORGANIZATION_CODE',
1422: p_org_partner_id => 'ORGANIZATION_ID',
1423: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1424: p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,

Line 1450: (p_table_name => 'MSC_ST_SUPPLIES',

1446: END IF;
1447:
1448: --Derive Inventory_item_id
1449: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
1450: (p_table_name => 'MSC_ST_SUPPLIES',
1451: p_item_col_name => 'ITEM_NAME',
1452: p_item_col_id => 'INVENTORY_ITEM_ID',
1453: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
1454: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,

Line 1480: 'UPDATE msc_st_supplies '

1476: END IF;
1477:
1478: MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1479: lv_sql_stmt :=
1480: 'UPDATE msc_st_supplies '
1481: ||' SET error_text = '||''''||lv_message_text||''''||','
1482: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1483: ||' WHERE (NVL(new_schedule_date,sysdate-36500) = sysdate-36500'
1484: ||' OR NVL(new_order_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE|| ')= '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'

Line 1512: 'UPDATE msc_st_supplies '

1508: END IF;
1509:
1510: MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
1511: lv_sql_stmt :=
1512: 'UPDATE msc_st_supplies '
1513: ||' SET error_text = '||''''||lv_message_text||''''||','
1514: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1515: ||' WHERE NVL(wip_entity_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1516: ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''

Line 1551: (p_table_name => 'MSC_ST_SUPPLIES',

1547: --Log a warning for those records where the firm_planned_type has a value
1548: --other than 1 and 2
1549:
1550: lv_return := MSC_ST_UTIL.LOG_ERROR
1551: (p_table_name => 'MSC_ST_SUPPLIES',
1552: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1553: p_row => lv_column_names,
1554: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1555: p_message_text => lv_message_text,

Line 1589: (p_table_name => 'MSC_ST_SUPPLIES',

1585: --Log a warning for those records where the wip_status_code has a value other
1586: --than SYS_NO
1587:
1588: lv_return := MSC_ST_UTIL.LOG_ERROR
1589: (p_table_name => 'MSC_ST_SUPPLIES',
1590: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1591: p_row => lv_column_names,
1592: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1593: p_message_text => lv_message_text,

Line 1627: (p_table_name => 'MSC_ST_SUPPLIES',

1623: --Log a warning for those records where the firm_planned_type has a value other
1624: --than SYS_NO
1625:
1626: lv_return := MSC_ST_UTIL.LOG_ERROR
1627: (p_table_name => 'MSC_ST_SUPPLIES',
1628: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1629: p_row => lv_column_names,
1630: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1631: p_message_text => lv_message_text,

Line 1665: (p_table_name => 'MSC_ST_SUPPLIES',

1661: --Log a warning for those records where the wip_supply_type has a value other
1662: --than SYS_NO
1663:
1664: lv_return := MSC_ST_UTIL.LOG_ERROR
1665: (p_table_name => 'MSC_ST_SUPPLIES',
1666: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1667: p_row => lv_column_names,
1668: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1669: p_message_text => lv_message_text,

Line 1683: 'UPDATE msc_st_supplies'

1679: END IF;
1680:
1681: MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
1682: lv_sql_stmt :=
1683: 'UPDATE msc_st_supplies'
1684: ||' SET order_number = wip_entity_name'
1685: ||' WHERE NVL(order_number,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
1686: ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1687: ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO

Line 1705: 'UPDATE msc_st_supplies '

1701: -- ASSEMBLY NAME in BOM NAME column for all such records
1702:
1703: MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
1704: lv_sql_stmt :=
1705: 'UPDATE msc_st_supplies '
1706: ||' SET bill_name = item_name'
1707: ||' WHERE sr_instance_code = :v_instance_code'
1708: ||' AND order_type =86'
1709: ||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1728: 'UPDATE msc_st_supplies '

1724:
1725: MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
1726:
1727: lv_sql_stmt :=
1728: 'UPDATE msc_st_supplies '
1729: ||' SET routing_name = item_name'
1730: ||' WHERE sr_instance_code = :v_instance_code'
1731: ||' AND order_type =86'
1732: ||' AND process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1746: (p_table_name => 'MSC_ST_SUPPLIES',

1742:
1743: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
1744:
1745: lv_return := MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID
1746: (p_table_name => 'MSC_ST_SUPPLIES',
1747: p_bom_col_name => 'BILL_NAME',
1748: p_bom_col_id => 'BILL_SEQUENCE_ID',
1749: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1750: p_batch_id => lv_batch_id,

Line 1760: (p_table_name => 'MSC_ST_SUPPLIES',

1756: RAISE ex_logging_err;
1757: END IF;
1758:
1759: lv_return := MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID
1760: (p_table_name => 'MSC_ST_SUPPLIES',
1761: p_rtg_col_name => 'ROUTING_NAME',
1762: p_rtg_col_id => 'ROUTING_SEQUENCE_ID',
1763: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1764: p_batch_id => lv_batch_id,

Line 1777: 'UPDATE msc_st_supplies mss'

1773:
1774:
1775: MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
1776: lv_sql_stmt :=
1777: 'UPDATE msc_st_supplies mss'
1778: ||' SET schedule_group_id = (SELECT local_id'
1779: ||' FROM msc_local_id_supply mls'
1780: ||' WHERE mls.char4 = mss.schedule_group_name'
1781: ||' AND mls.char3 = mss.organization_code'

Line 1843: (p_table_name => 'MSC_ST_SUPPLIES',

1839: --Log a warning for those records where the bill_sequence_id or
1840: --routing_sequence_id has null values
1841:
1842: lv_return := MSC_ST_UTIL.LOG_ERROR
1843: (p_table_name => 'MSC_ST_SUPPLIES',
1844: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1845: p_row => lv_column_names,
1846: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1847: p_message_text => lv_message_text,

Line 1860: 'UPDATE msc_st_supplies mss'

1856:
1857: --Deriving wip_entity_id
1858: MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
1859: lv_sql_stmt :=
1860: 'UPDATE msc_st_supplies mss'
1861: ||' SET wip_entity_id = (SELECT local_id'
1862: ||' FROM msc_local_id_supply mls'
1863: ||' WHERE mls.char4 = mss.wip_entity_name'
1864: ||' AND mls.char3 = mss.organization_code'

Line 1895: 'UPDATE msc_st_supplies '

1891:
1892: MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
1893:
1894: lv_sql_stmt :=
1895: 'UPDATE msc_st_supplies '
1896: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1897: ||' error_text = '||''''||lv_message_text||''''
1898: ||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1899: ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES

Line 1916: 'UPDATE msc_st_supplies '

1912:
1913: -- update the jump_op_seq_num for lot based jobs for the operations jumped outside the network
1914:
1915: lv_sql_stmt :=
1916: 'UPDATE msc_st_supplies '
1917: ||' SET jump_op_seq_num = 50000'
1918: ||' WHERE NVL(jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1919: ||' = '||''''||50000||''''
1920: ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1935: 'UPDATE msc_st_supplies mss'

1931: MSC_CL_PRE_PROCESS.v_instance_code;
1932:
1933:
1934: lv_sql_stmt :=
1935: 'UPDATE msc_st_supplies mss'
1936: ||' SET jump_op_seq_num = (SELECT number1'
1937: ||' FROM msc_local_id_setup mls'
1938: ||' WHERE NVL(mls.char5,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1939: ||' NVL(mss.jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '

Line 1971: pEntityName => 'MSC_ST_SUPPLIES_ERO',

1967: (ERRBUF => lv_error_text,
1968: RETCODE => lv_return,
1969: pBatchID => lv_batch_id,
1970: pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
1971: pEntityName => 'MSC_ST_SUPPLIES_ERO',
1972: pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
1973:
1974: IF NVL(lv_return,0) <> 0 THEN
1975: RAISE ex_logging_err;

Line 1985: UPDATE msc_st_supplies

1981:
1982: IF c2%ROWCOUNT > 0 THEN
1983: MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
1984: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1985: UPDATE msc_st_supplies
1986: SET wip_entity_id = msc_st_wip_entity_id_s.NEXTVAL
1987: WHERE rowid = lb_rowid(j);
1988:
1989: MSC_CL_PRE_PROCESS.v_sql_stmt := 13;

Line 2031: FROM msc_st_supplies

2027: MSC_CL_PRE_PROCESS.v_current_date,
2028: MSC_CL_PRE_PROCESS.v_current_user,
2029: MSC_CL_PRE_PROCESS.v_current_date,
2030: MSC_CL_PRE_PROCESS.v_current_user
2031: FROM msc_st_supplies
2032: WHERE rowid = lb_rowid(j);
2033:
2034: END IF;
2035: CLOSE c2 ;

Line 2044: UPDATE msc_st_supplies

2040:
2041: IF c3%ROWCOUNT > 0 THEN
2042: MSC_CL_PRE_PROCESS.v_sql_stmt := 14;
2043: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2044: UPDATE msc_st_supplies
2045: SET schedule_group_id = msc_st_schedule_group_id_s.NEXTVAL
2046: WHERE rowid = lb_rowid(j);
2047:
2048: MSC_CL_PRE_PROCESS.v_sql_stmt := 15;

Line 2090: FROM msc_st_supplies

2086: MSC_CL_PRE_PROCESS.v_current_date,
2087: MSC_CL_PRE_PROCESS.v_current_user,
2088: MSC_CL_PRE_PROCESS.v_current_date,
2089: MSC_CL_PRE_PROCESS.v_current_user
2090: FROM msc_st_supplies
2091: WHERE rowid = lb_rowid(j);
2092:
2093: END IF;
2094: CLOSE c3;

Line 2098: UPDATE msc_st_supplies

2094: CLOSE c3;
2095:
2096: --Update disposition_id with the wip_entity_id.
2097: MSC_CL_PRE_PROCESS.v_sql_stmt := 16;
2098: UPDATE msc_st_supplies
2099: SET disposition_id = wip_entity_id
2100: WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
2101: AND batch_id = lv_batch_id
2102: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 2121: (p_table_name => 'MSC_ST_SUPPLIES',

2117: END IF;
2118:
2119: --Derive Project Id.
2120: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2121: (p_table_name => 'MSC_ST_SUPPLIES',
2122: p_proj_col_name => 'PROJECT_NUMBER',
2123: p_proj_task_col_id => 'PROJECT_ID',
2124: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2125: p_entity_name => 'PROJECT_ID',

Line 2154: (p_table_name => 'MSC_ST_SUPPLIES',

2150: END IF;
2151:
2152: --Derive Task Id.
2153: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2154: (p_table_name => 'MSC_ST_SUPPLIES',
2155: p_proj_col_name => 'PROJECT_NUMBER',
2156: p_proj_task_col_id => 'TASK_ID',
2157: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2158: p_entity_name => 'TASK_ID',

Line 2174: 'UPDATE msc_st_supplies mss '

2170:
2171:
2172: MSC_CL_PRE_PROCESS.v_sql_stmt := 17;
2173: lv_sql_stmt :=
2174: 'UPDATE msc_st_supplies mss '
2175: ||' SET schedule_group_id = (SELECT local_id'
2176: ||' FROM msc_local_id_supply mls'
2177: ||' WHERE mls.char4 = mss.schedule_group_name'
2178: ||' AND mls.char3 = mss.organization_code'

Line 2204: UPDATE msc_st_supplies

2200: IF c4%ROWCOUNT > 0 THEN
2201:
2202: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2203:
2204: UPDATE msc_st_supplies
2205: SET job_op_seq_num =
2206: to_number(decode(length(rtrim(job_op_seq_code,'0123456789')),
2207: NULL,job_op_seq_code,'1'))
2208: WHERE rowid = lb_rowid(j);

Line 2213: (p_table_name => 'MSC_ST_SUPPLIES',

2209: END IF;
2210: CLOSE c4;
2211:
2212: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
2213: (p_table_name => 'MSC_ST_SUPPLIES',
2214: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2215: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2216: p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
2217: p_error_text => lv_error_text,

Line 2225: (p_table_name => 'MSC_ST_SUPPLIES',

2221: RAISE ex_logging_err;
2222: END IF;
2223:
2224: lv_return := MSC_ST_UTIL.LOG_ERROR
2225: (p_table_name => 'MSC_ST_SUPPLIES',
2226: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2227: p_row => lv_column_names,
2228: p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2229: p_message_text => NULL,

Line 2635: p_token_value2 => 'MSC_ST_SUPPLIES',

2631: p_token1 => 'COLUMN_NAMES',
2632: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
2633: ||' ORGANIZATION_CODE,WIP_ENTITY_NAME',
2634: p_token2 => 'MASTER_TABLE',
2635: p_token_value2 => 'MSC_ST_SUPPLIES',
2636: p_token3 => 'CHILD_TABLE' ,
2637: p_token_value3 => 'MSC_ST_DEMANDS' );
2638:
2639: IF lv_return <> 0 THEN