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 1140: lv_batch_id msc_st_supplies.batch_id%TYPE;

1136: lv_where_str VARCHAR2(5000);
1137: lv_sql_stmt VARCHAR2(5000);
1138: lv_column_names VARCHAR2(5000); --stores concatenated column names
1139: lv_message_text msc_errors.error_text%TYPE;
1140: lv_batch_id msc_st_supplies.batch_id%TYPE;
1141: ex_logging_err EXCEPTION;
1142:
1143: CURSOR c1(p_batch_id NUMBER) IS
1144: SELECT rowid

Line 1145: FROM msc_st_supplies

1141: ex_logging_err EXCEPTION;
1142:
1143: CURSOR c1(p_batch_id NUMBER) IS
1144: SELECT rowid
1145: FROM msc_st_supplies
1146: WHERE order_type =86
1147: AND process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
1148: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE)=p_batch_id
1149: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 1153: FROM msc_st_supplies

1149: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1150:
1151: CURSOR c2(p_batch_id NUMBER) IS
1152: SELECT rowid
1153: FROM msc_st_supplies
1154: WHERE NVL(wip_entity_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1155: AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1156: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
1157: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 1161: FROM msc_st_supplies

1157: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1158:
1159: CURSOR c3(p_batch_id NUMBER) IS
1160: SELECT max(rowid)
1161: FROM msc_st_supplies
1162: WHERE NVL(schedule_group_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1163: AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
1164: AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1165: AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = p_batch_id

Line 1171: FROM msc_st_supplies

1167: GROUP BY sr_instance_code,company_name,organization_code,schedule_group_name;
1168:
1169: CURSOR c4(p_batch_id NUMBER) IS
1170: SELECT rowid
1171: FROM msc_st_supplies
1172: WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1173: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
1174: AND batch_id = p_batch_id
1175: AND NVL(JOB_OP_SEQ_NUM, MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE

Line 1196: 'UPDATE msc_st_supplies mss1'

1192: --Duplicate records check for the records whose source is XML for
1193: --WO supplies
1194: MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
1195: lv_sql_stmt :=
1196: 'UPDATE msc_st_supplies mss1'
1197: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1198: ||' error_text = '||''''||lv_message_text||''''
1199: ||' WHERE message_id < (SELECT MAX(message_id)'
1200: ||' FROM msc_st_supplies mss2'

Line 1200: ||' FROM msc_st_supplies mss2'

1196: 'UPDATE msc_st_supplies mss1'
1197: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1198: ||' error_text = '||''''||lv_message_text||''''
1199: ||' WHERE message_id < (SELECT MAX(message_id)'
1200: ||' FROM msc_st_supplies mss2'
1201: ||' WHERE mss2.sr_instance_code'
1202: ||' = mss1.sr_instance_code'
1203: ||' AND NVL(mss2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1204: ||' NVL(mss1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'

Line 1238: 'UPDATE msc_st_supplies mss1 '

1234: --Different SQL is used because in XML we can identify the latest records
1235: --whereas in batch load we cannot.
1236: MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
1237: lv_sql_stmt :=
1238: 'UPDATE msc_st_supplies mss1 '
1239: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1240: ||' error_text = '||''''||lv_message_text||''''
1241: ||' WHERE EXISTS( SELECT 1 '
1242: ||' FROM msc_st_supplies mss2'

Line 1242: ||' FROM msc_st_supplies mss2'

1238: 'UPDATE msc_st_supplies mss1 '
1239: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1240: ||' error_text = '||''''||lv_message_text||''''
1241: ||' WHERE EXISTS( SELECT 1 '
1242: ||' FROM msc_st_supplies mss2'
1243: ||' WHERE mss2.sr_instance_code'
1244: ||' = mss1.sr_instance_code'
1245: ||' AND NVL(mss2.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')= '
1246: ||' NVL(mss1.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'

Line 1307: ' UPDATE msc_st_supplies '

1303: FROM dual;
1304:
1305: MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1306: lv_sql_stmt :=
1307: ' UPDATE msc_st_supplies '
1308: ||' SET batch_id = :lv_batch_id'
1309: ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
1310: ||' AND order_type =86'
1311: ||' AND sr_instance_code = :v_instance_code'

Line 1331: UPDATE msc_st_supplies

1327: CLOSE c1;
1328:
1329: MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1330: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1331: UPDATE msc_st_supplies
1332: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
1333: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
1334: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
1335: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,

Line 1332: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,

1328:
1329: MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1330: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1331: UPDATE msc_st_supplies
1332: SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
1333: refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
1334: last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
1335: last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
1336: creation_date = MSC_CL_PRE_PROCESS.v_current_date,

Line 1359: (p_table_name => 'MSC_ST_SUPPLIES',

1355: ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
1356: --Log a warning for those records where the deleted_flag has a value other
1357: --SYS_NO
1358: lv_return := MSC_ST_UTIL.LOG_ERROR
1359: (p_table_name => 'MSC_ST_SUPPLIES',
1360: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1361: p_row => lv_column_names,
1362: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1363: p_message_text => lv_message_text,

Line 1389: (p_table_name => 'MSC_ST_SUPPLIES',

1385: END IF;
1386:
1387: --Derive Organization_id
1388: lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
1389: (p_table_name => 'MSC_ST_SUPPLIES',
1390: p_org_partner_name => 'ORGANIZATION_CODE',
1391: p_org_partner_id => 'ORGANIZATION_ID',
1392: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1393: p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,

Line 1419: (p_table_name => 'MSC_ST_SUPPLIES',

1415: END IF;
1416:
1417: --Derive Inventory_item_id
1418: lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
1419: (p_table_name => 'MSC_ST_SUPPLIES',
1420: p_item_col_name => 'ITEM_NAME',
1421: p_item_col_id => 'INVENTORY_ITEM_ID',
1422: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
1423: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,

Line 1449: 'UPDATE msc_st_supplies '

1445: END IF;
1446:
1447: MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1448: lv_sql_stmt :=
1449: 'UPDATE msc_st_supplies '
1450: ||' SET error_text = '||''''||lv_message_text||''''||','
1451: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1452: ||' WHERE (NVL(new_schedule_date,sysdate-36500) = sysdate-36500'
1453: ||' OR NVL(new_order_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE|| ')= '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'

Line 1481: 'UPDATE msc_st_supplies '

1477: END IF;
1478:
1479: MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
1480: lv_sql_stmt :=
1481: 'UPDATE msc_st_supplies '
1482: ||' SET error_text = '||''''||lv_message_text||''''||','
1483: ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1484: ||' WHERE NVL(wip_entity_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1485: ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''

Line 1520: (p_table_name => 'MSC_ST_SUPPLIES',

1516: --Log a warning for those records where the firm_planned_type has a value
1517: --other than 1 and 2
1518:
1519: lv_return := MSC_ST_UTIL.LOG_ERROR
1520: (p_table_name => 'MSC_ST_SUPPLIES',
1521: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1522: p_row => lv_column_names,
1523: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1524: p_message_text => lv_message_text,

Line 1558: (p_table_name => 'MSC_ST_SUPPLIES',

1554: --Log a warning for those records where the wip_status_code has a value other
1555: --than SYS_NO
1556:
1557: lv_return := MSC_ST_UTIL.LOG_ERROR
1558: (p_table_name => 'MSC_ST_SUPPLIES',
1559: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1560: p_row => lv_column_names,
1561: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1562: p_message_text => lv_message_text,

Line 1596: (p_table_name => 'MSC_ST_SUPPLIES',

1592: --Log a warning for those records where the firm_planned_type has a value other
1593: --than SYS_NO
1594:
1595: lv_return := MSC_ST_UTIL.LOG_ERROR
1596: (p_table_name => 'MSC_ST_SUPPLIES',
1597: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1598: p_row => lv_column_names,
1599: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1600: p_message_text => lv_message_text,

Line 1634: (p_table_name => 'MSC_ST_SUPPLIES',

1630: --Log a warning for those records where the wip_supply_type has a value other
1631: --than SYS_NO
1632:
1633: lv_return := MSC_ST_UTIL.LOG_ERROR
1634: (p_table_name => 'MSC_ST_SUPPLIES',
1635: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1636: p_row => lv_column_names,
1637: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1638: p_message_text => lv_message_text,

Line 1652: 'UPDATE msc_st_supplies'

1648: END IF;
1649:
1650: MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
1651: lv_sql_stmt :=
1652: 'UPDATE msc_st_supplies'
1653: ||' SET order_number = wip_entity_name'
1654: ||' WHERE NVL(order_number,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
1655: ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1656: ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO

Line 1674: 'UPDATE msc_st_supplies '

1670: -- ASSEMBLY NAME in BOM NAME column for all such records
1671:
1672: MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
1673: lv_sql_stmt :=
1674: 'UPDATE msc_st_supplies '
1675: ||' SET bill_name = item_name'
1676: ||' WHERE sr_instance_code = :v_instance_code'
1677: ||' AND order_type =86'
1678: ||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1697: 'UPDATE msc_st_supplies '

1693:
1694: MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
1695:
1696: lv_sql_stmt :=
1697: 'UPDATE msc_st_supplies '
1698: ||' SET routing_name = item_name'
1699: ||' WHERE sr_instance_code = :v_instance_code'
1700: ||' AND order_type =86'
1701: ||' AND process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1715: (p_table_name => 'MSC_ST_SUPPLIES',

1711:
1712: EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
1713:
1714: lv_return := MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID
1715: (p_table_name => 'MSC_ST_SUPPLIES',
1716: p_bom_col_name => 'BILL_NAME',
1717: p_bom_col_id => 'BILL_SEQUENCE_ID',
1718: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1719: p_batch_id => lv_batch_id,

Line 1729: (p_table_name => 'MSC_ST_SUPPLIES',

1725: RAISE ex_logging_err;
1726: END IF;
1727:
1728: lv_return := MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID
1729: (p_table_name => 'MSC_ST_SUPPLIES',
1730: p_rtg_col_name => 'ROUTING_NAME',
1731: p_rtg_col_id => 'ROUTING_SEQUENCE_ID',
1732: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1733: p_batch_id => lv_batch_id,

Line 1746: 'UPDATE msc_st_supplies mss'

1742:
1743:
1744: MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
1745: lv_sql_stmt :=
1746: 'UPDATE msc_st_supplies mss'
1747: ||' SET schedule_group_id = (SELECT local_id'
1748: ||' FROM msc_local_id_supply mls'
1749: ||' WHERE mls.char4 = mss.schedule_group_name'
1750: ||' AND mls.char3 = mss.organization_code'

Line 1812: (p_table_name => 'MSC_ST_SUPPLIES',

1808: --Log a warning for those records where the bill_sequence_id or
1809: --routing_sequence_id has null values
1810:
1811: lv_return := MSC_ST_UTIL.LOG_ERROR
1812: (p_table_name => 'MSC_ST_SUPPLIES',
1813: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1814: p_row => lv_column_names,
1815: p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1816: p_message_text => lv_message_text,

Line 1829: 'UPDATE msc_st_supplies mss'

1825:
1826: --Deriving wip_entity_id
1827: MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
1828: lv_sql_stmt :=
1829: 'UPDATE msc_st_supplies mss'
1830: ||' SET wip_entity_id = (SELECT local_id'
1831: ||' FROM msc_local_id_supply mls'
1832: ||' WHERE mls.char4 = mss.wip_entity_name'
1833: ||' AND mls.char3 = mss.organization_code'

Line 1864: 'UPDATE msc_st_supplies '

1860:
1861: MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
1862:
1863: lv_sql_stmt :=
1864: 'UPDATE msc_st_supplies '
1865: ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1866: ||' error_text = '||''''||lv_message_text||''''
1867: ||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1868: ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES

Line 1885: 'UPDATE msc_st_supplies '

1881:
1882: -- update the jump_op_seq_num for lot based jobs for the operations jumped outside the network
1883:
1884: lv_sql_stmt :=
1885: 'UPDATE msc_st_supplies '
1886: ||' SET jump_op_seq_num = 50000'
1887: ||' WHERE NVL(jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1888: ||' = '||''''||50000||''''
1889: ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS

Line 1904: 'UPDATE msc_st_supplies mss'

1900: MSC_CL_PRE_PROCESS.v_instance_code;
1901:
1902:
1903: lv_sql_stmt :=
1904: 'UPDATE msc_st_supplies mss'
1905: ||' SET jump_op_seq_num = (SELECT number1'
1906: ||' FROM msc_local_id_setup mls'
1907: ||' WHERE NVL(mls.char5,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1908: ||' NVL(mss.jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '

Line 1940: pEntityName => 'MSC_ST_SUPPLIES_ERO',

1936: (ERRBUF => lv_error_text,
1937: RETCODE => lv_return,
1938: pBatchID => lv_batch_id,
1939: pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
1940: pEntityName => 'MSC_ST_SUPPLIES_ERO',
1941: pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
1942:
1943: IF NVL(lv_return,0) <> 0 THEN
1944: RAISE ex_logging_err;

Line 1954: UPDATE msc_st_supplies

1950:
1951: IF c2%ROWCOUNT > 0 THEN
1952: MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
1953: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1954: UPDATE msc_st_supplies
1955: SET wip_entity_id = msc_st_wip_entity_id_s.NEXTVAL
1956: WHERE rowid = lb_rowid(j);
1957:
1958: MSC_CL_PRE_PROCESS.v_sql_stmt := 13;

Line 2000: FROM msc_st_supplies

1996: MSC_CL_PRE_PROCESS.v_current_date,
1997: MSC_CL_PRE_PROCESS.v_current_user,
1998: MSC_CL_PRE_PROCESS.v_current_date,
1999: MSC_CL_PRE_PROCESS.v_current_user
2000: FROM msc_st_supplies
2001: WHERE rowid = lb_rowid(j);
2002:
2003: END IF;
2004: CLOSE c2 ;

Line 2013: UPDATE msc_st_supplies

2009:
2010: IF c3%ROWCOUNT > 0 THEN
2011: MSC_CL_PRE_PROCESS.v_sql_stmt := 14;
2012: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2013: UPDATE msc_st_supplies
2014: SET schedule_group_id = msc_st_schedule_group_id_s.NEXTVAL
2015: WHERE rowid = lb_rowid(j);
2016:
2017: MSC_CL_PRE_PROCESS.v_sql_stmt := 15;

Line 2059: FROM msc_st_supplies

2055: MSC_CL_PRE_PROCESS.v_current_date,
2056: MSC_CL_PRE_PROCESS.v_current_user,
2057: MSC_CL_PRE_PROCESS.v_current_date,
2058: MSC_CL_PRE_PROCESS.v_current_user
2059: FROM msc_st_supplies
2060: WHERE rowid = lb_rowid(j);
2061:
2062: END IF;
2063: CLOSE c3;

Line 2067: UPDATE msc_st_supplies

2063: CLOSE c3;
2064:
2065: --Update disposition_id with the wip_entity_id.
2066: MSC_CL_PRE_PROCESS.v_sql_stmt := 16;
2067: UPDATE msc_st_supplies
2068: SET disposition_id = wip_entity_id
2069: WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
2070: AND batch_id = lv_batch_id
2071: AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;

Line 2090: (p_table_name => 'MSC_ST_SUPPLIES',

2086: END IF;
2087:
2088: --Derive Project Id.
2089: lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2090: (p_table_name => 'MSC_ST_SUPPLIES',
2091: p_proj_col_name => 'PROJECT_NUMBER',
2092: p_proj_task_col_id => 'PROJECT_ID',
2093: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2094: p_entity_name => 'PROJECT_ID',

Line 2123: (p_table_name => 'MSC_ST_SUPPLIES',

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

Line 2143: 'UPDATE msc_st_supplies mss '

2139:
2140:
2141: MSC_CL_PRE_PROCESS.v_sql_stmt := 17;
2142: lv_sql_stmt :=
2143: 'UPDATE msc_st_supplies mss '
2144: ||' SET schedule_group_id = (SELECT local_id'
2145: ||' FROM msc_local_id_supply mls'
2146: ||' WHERE mls.char4 = mss.schedule_group_name'
2147: ||' AND mls.char3 = mss.organization_code'

Line 2173: UPDATE msc_st_supplies

2169: IF c4%ROWCOUNT > 0 THEN
2170:
2171: FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2172:
2173: UPDATE msc_st_supplies
2174: SET job_op_seq_num =
2175: to_number(decode(length(rtrim(job_op_seq_code,'0123456789')),
2176: NULL,job_op_seq_code,'1'))
2177: WHERE rowid = lb_rowid(j);

Line 2182: (p_table_name => 'MSC_ST_SUPPLIES',

2178: END IF;
2179: CLOSE c4;
2180:
2181: lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
2182: (p_table_name => 'MSC_ST_SUPPLIES',
2183: p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2184: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2185: p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
2186: p_error_text => lv_error_text,

Line 2194: (p_table_name => 'MSC_ST_SUPPLIES',

2190: RAISE ex_logging_err;
2191: END IF;
2192:
2193: lv_return := MSC_ST_UTIL.LOG_ERROR
2194: (p_table_name => 'MSC_ST_SUPPLIES',
2195: p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2196: p_row => lv_column_names,
2197: p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2198: p_message_text => NULL,

Line 2604: p_token_value2 => 'MSC_ST_SUPPLIES',

2600: p_token1 => 'COLUMN_NAMES',
2601: p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
2602: ||' ORGANIZATION_CODE,WIP_ENTITY_NAME',
2603: p_token2 => 'MASTER_TABLE',
2604: p_token_value2 => 'MSC_ST_SUPPLIES',
2605: p_token3 => 'CHILD_TABLE' ,
2606: p_token_value3 => 'MSC_ST_DEMANDS' );
2607:
2608: IF lv_return <> 0 THEN