[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_RPO_PRE_PROCESS
Source
1 PACKAGE BODY MSC_CL_RPO_PRE_PROCESS AS
2 /* $Header: MSCRPOLB.pls 120.4 2008/01/26 09:34:26 abhikuma noship $ */
3
4 PROCEDURE LOAD_IRO_SUPPLY IS
5 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
6 lb_rowid RowidTab;
7 lv_return NUMBER;
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
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
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
31 AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
32 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
33
34 BEGIN
35
36
37 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
38 (p_app_short_name => 'MSC',
39 p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
40 p_message_text => lv_message_text,
41 p_error_text => lv_error_text);
42
43 IF lv_return <> 0 THEN
44 RAISE ex_logging_err;
45 END IF;
46
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'
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'
60 ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
61 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') ='||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
62 ||' AND mss1.order_type IN (75)'
63 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
64 ||' AND mss1.sr_instance_code = :v_instance_code'
65 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
66
67
68
69
70 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
71 msc_st_util.log_message(lv_sql_stmt);
72 END IF;
73
74 EXECUTE IMMEDIATE lv_sql_stmt
75 USING MSC_CL_PRE_PROCESS.v_instance_code;
76
77 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
78 (p_app_short_name => 'MSC',
79 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
80 p_message_text => lv_message_text,
81 p_error_text => lv_error_text);
82
83 IF lv_return <> 0 THEN
84 RAISE ex_logging_err;
85 END IF;
86
87 --Duplicate records check for the records whose source is other than XML
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'
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
101 ||' AND mss2.organization_code = mss1.organization_code'
102 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
103 ||' GROUP BY sr_instance_code,repair_number,organization_code, order_type '
104 ||' HAVING COUNT(*) > 1)'
105 ||' AND mss1.order_type IN (75)'
106 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
107 ||' AND mss1.sr_instance_code = :v_instance_code'
108 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
109
110 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
111 msc_st_util.log_message(lv_sql_stmt);
112 END IF;
113
114 EXECUTE IMMEDIATE lv_sql_stmt
115 USING MSC_CL_PRE_PROCESS.v_instance_code;
116
117 lv_column_names :=
118 'ITEM_NAME ||''~''||'
119 ||'ORGANIZATION_CODE ||''~''||'
120 ||'NEW_SCHEDULE_DATE ||''~''||'
121 ||'SR_INSTANCE_CODE ||''~''||'
122 ||'REVISION ||''~''||'
123 ||'NEW_ORDER_QUANTITY ||''~''||'
124 ||'PROJECT_NUMBER ||''~''||'
125 ||'TASK_NUMBER ||''~''||'
126 ||'DELETED_FLAG ||''~''||'
127 ||'UOM_CODE ||''~''||'
128 ||'CUSTOMER_PRODUCT_ID ||''~''||'
129 ||'SR_REPAIR_TYPE_ID ||''~''||'
130 ||'RO_STATUS_CODE ||''~''||'
131 ||'ASSET_SERIAL_NUMBER ||''~''||'
132 ||'SR_REPAIR_GROUP_ID ||''~''||'
133 ||'SCHEDULE_PRIORITY ||''~''||'
134 ||'RO_CREATION_DATE ||''~''||'
135 ||'REPAIR_LEAD_TIME';
136
137
138 LOOP
139 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
140 SELECT msc_st_batch_id_s.NEXTVAL
141 INTO lv_batch_id
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'
151 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
152 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
153
154
155 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
156 msc_st_util.log_message(lv_sql_stmt);
157 END IF;
158
159 EXECUTE IMMEDIATE lv_sql_stmt
160 USING lv_batch_id,
161 MSC_CL_PRE_PROCESS.v_instance_code;
162
163 EXIT WHEN SQL%NOTFOUND;
164
165 OPEN c1(lv_batch_id);
166 FETCH c1 BULK COLLECT INTO lb_rowid;
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,
176 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
177 created_by = MSC_CL_PRE_PROCESS.v_current_user
178 WHERE rowid = lb_rowid(j);
179
180 -- set the error message
181
182 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
183 (p_app_short_name => 'MSC',
184 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
185 p_message_text => lv_message_text,
186 p_error_text => lv_error_text,
187 p_token1 => 'COLUMN_NAME',
188 p_token_value1 => 'DELETED_FLAG',
189 p_token2 => 'DEFAULT_VALUE',
190 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
191
192 IF lv_return <> 0 THEN
193 RAISE ex_logging_err;
194 END IF;
195
196 lv_where_str :=
197 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') '
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,
207 p_error_text => lv_error_text,
208 p_batch_id => lv_batch_id,
209 p_where_str => lv_where_str,
210 p_col_name => 'DELETED_FLAG',
211 p_debug => MSC_CL_PRE_PROCESS.v_debug,
212 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
213
214 IF lv_return <> 0 THEN
215 RAISE ex_logging_err;
216 END IF;
217
218 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
219 (p_app_short_name => 'MSC',
220 p_error_code => 'MSC_PP_INVALID_VALUE',
221 p_message_text => lv_message_text,
222 p_error_text => lv_error_text,
223 p_token1 => 'COLUMN_NAME',
224 p_token_value1 => 'ORGANIZATION_CODE');
225
226 IF lv_return <> 0 THEN
227 RAISE ex_logging_err;
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,
237 p_error_text => lv_error_text,
238 p_batch_id => lv_batch_id,
239 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
240 p_message_text => lv_message_text,
241 p_debug => MSC_CL_PRE_PROCESS.v_debug,
242 p_row => lv_column_names);
243
244 IF lv_return <> 0 THEN
245 RAISE ex_logging_err;
246 END IF;
247
248 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
249 (p_app_short_name => 'MSC',
250 p_error_code => 'MSC_PP_INVALID_VALUE',
251 p_message_text => lv_message_text,
252 p_error_text => lv_error_text,
253 p_token1 => 'COLUMN_NAME',
254 p_token_value1 => 'ITEM_NAME');
255
256 IF lv_return <> 0 THEN
257 RAISE ex_logging_err;
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,
267 p_error_text => lv_error_text,
268 p_batch_id => lv_batch_id,
269 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
270 p_message_text => lv_message_text,
271 p_debug => MSC_CL_PRE_PROCESS.v_debug,
272 p_row => lv_column_names);
273
274 IF lv_return <> 0 THEN
275 RAISE ex_logging_err;
276 END IF;
277
278 --- error out the record if new_order_quantity is null
279 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
280 (p_app_short_name => 'MSC',
281 p_error_code => 'MSC_PP_COL_VAL_NULL',
282 p_message_text => lv_message_text,
283 p_error_text => lv_error_text,
284 p_token1 => 'COLUMN_NAME',
285 p_token_value1 => 'NEW_ORDER_QUANTITY');
286
287 IF lv_return <> 0 THEN
288 RAISE ex_logging_err;
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
298 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
299 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
300 ||' AND sr_instance_code = :v_instance_code';
301
302 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
303 msc_st_util.log_message(lv_sql_stmt);
304 END IF;
305
306 EXECUTE IMMEDIATE lv_sql_stmt
307 USING lv_batch_id,
308 MSC_CL_PRE_PROCESS.v_instance_code;
309
310 --- error out record which has new_schedule_date and ro_creation_date both as null
311 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
312 (p_app_short_name => 'MSC',
313 p_error_code => 'MSC_PP_COL_VAL_NULL',
314 p_message_text => lv_message_text,
315 p_error_text => lv_error_text,
316 p_token1 => 'COLUMN_NAME',
317 p_token_value1 => 'NEW_SCHEDULE_DATE AND RO_CREATION_DATE');
318
319 IF lv_return <> 0 THEN
320 RAISE ex_logging_err;
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'
330 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
331 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
332 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
333 ||' AND sr_instance_code = :v_instance_code';
334
335 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
336 msc_st_util.log_message(lv_sql_stmt);
337 END IF;
338
339 EXECUTE IMMEDIATE lv_sql_stmt
340 USING lv_batch_id,
341 MSC_CL_PRE_PROCESS.v_instance_code;
342
343 --- error out record if repair_number is null:
344 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
345 (p_app_short_name => 'MSC',
346 p_error_code => 'MSC_PP_COL_VAL_NULL',
347 p_message_text => lv_message_text,
348 p_error_text => lv_error_text,
349 p_token1 => 'COLUMN_NAME',
350 p_token_value1 => 'REPAIR_NUMBER');
351
352 IF lv_return <> 0 THEN
353 RAISE ex_logging_err;
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
363 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
364 ||' AND sr_instance_code = :v_instance_code';
365
366 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
367 msc_st_util.log_message(lv_sql_stmt);
368 END IF;
369
370 EXECUTE IMMEDIATE lv_sql_stmt
371 USING lv_batch_id,
372 MSC_CL_PRE_PROCESS.v_instance_code;
373
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'
383 ||' AND mls.char1 = mss.sr_instance_code'
384 ||' AND mls.entity_name = ''REPAIR_NUMBER'' )'
385 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
386 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
387 ||' AND sr_instance_code = :v_instance_code';
388
389 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
390 msc_st_util.log_message(lv_sql_stmt);
391 END IF;
392
393 EXECUTE IMMEDIATE lv_sql_stmt
394 USING lv_batch_id,
395 MSC_CL_PRE_PROCESS.v_instance_code;
396
397
398 ---error out the record where repair line is null and deleted flag is SYS_YES
399
400 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
401 (p_app_short_name => 'MSC',
402 p_error_code => 'MSC_PP_DELETE_FAIL',
403 p_message_text => lv_message_text,
404 p_error_text => lv_error_text);
405
406 IF lv_return <> 0 THEN
407 RAISE ex_logging_err;
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
417 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
418 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
419 ||' AND sr_instance_code =:v_instance_code';
420
421 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
422 msc_st_util.log_message(lv_sql_stmt);
423 END IF;
424
425 EXECUTE IMMEDIATE lv_sql_stmt
426 USING lv_batch_id,
427 MSC_CL_PRE_PROCESS.v_instance_code;
428
429 --- uom code validated
430 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
431 (p_app_short_name => 'MSC',
432 p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
433 p_message_text => lv_message_text,
434 p_error_text => lv_error_text,
435 p_token1 => 'COLUMN_NAMES',
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
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'
452 ||' WHERE muom.uom_code = mic.uom_code'
453 ||' UNION'
454 ||' SELECT 1 FROM msc_st_units_of_measure msuom'
455 ||' WHERE msuom.uom_code = mic.uom_code'
456 ||' AND msuom.sr_instance_id = :v_instance_id'
457 ||' AND msuom.process_flag = '||MSC_CL_PRE_PROCESS.G_VALID||')'
458 ||' AND mic.sr_instance_code = :v_instance_code'
459 ||' AND mic.batch_id = :lv_batch_id'
460 ||' AND mic.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS ;
461
462 IF MSC_CL_PRE_PROCESS.v_debug THEN
463 msc_st_util.log_message(lv_sql_stmt);
464 END IF;
465
466 EXECUTE IMMEDIATE lv_sql_stmt
467 USING MSC_CL_PRE_PROCESS.v_instance_id,
468 MSC_CL_PRE_PROCESS.v_instance_code,
469 lv_batch_id;
470
471 /* for all the row in given batch_id with process_flag =2 and repair_line_id as null populate repair line id
472 msc_st_iro_supply_s is the new sequence that needs to be created. */
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;
482 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
483 INSERT INTO msc_local_id_supply
484 (local_id,
485 st_transaction_id,
486 instance_id,
487 entity_name,
488 data_source_type,
489 char1,
490 char3,
491 char4,
492 SOURCE_ORG_ID,
493 SOURCE_INVENTORY_ITEM_ID,
494 SOURCE_BILL_SEQUENCE_ID,
495 SOURCE_ROUTING_SEQUENCE_ID,
496 SOURCE_SCHEDULE_GROUP_ID,
497 SOURCE_WIP_ENTITY_ID,
498 last_update_date,
499 last_updated_by,
500 creation_date,
501 created_by)
502 SELECT
503 disposition_id,
504 st_transaction_id,
505 MSC_CL_PRE_PROCESS.v_instance_id,
506 'REPAIR_NUMBER',
507 data_source_type,
508 MSC_CL_PRE_PROCESS.v_instance_code,
509 organization_code ,
510 REPAIR_NUMBER,
511 SOURCE_ORG_ID,
512 SOURCE_INVENTORY_ITEM_ID,
513 SOURCE_BILL_SEQUENCE_ID,
514 SOURCE_ROUTING_SEQUENCE_ID,
515 SOURCE_SCHEDULE_GROUP_ID,
516 SOURCE_WIP_ENTITY_ID,
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 :
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',
532 p_error_text => lv_error_text,
533 p_batch_id => lv_batch_id,
534 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
535 p_message_text => lv_message_text,
536 p_debug => MSC_CL_PRE_PROCESS.v_debug,
537 p_row => lv_column_names);
538 IF lv_return <> 0 THEN
539 RAISE ex_logging_err;
540 END IF;
541
542 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
543 (p_app_short_name => 'MSC',
544 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
545 p_message_text => lv_message_text,
546 p_error_text => lv_error_text,
547 p_token1 => 'COLUMN_NAMES',
548 p_token_value1 => ' SR_INSTANCE_CODE,'
549 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
550 ||' TASK_NUMBER',
551 p_token2 => 'MASTER_TABLE',
552 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
553
554 IF lv_return <> 0 THEN
555 RAISE ex_logging_err;
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',
565 p_error_text => lv_error_text,
566 p_task_col_name => 'TASK_NUMBER',
567 p_batch_id => lv_batch_id,
568 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
569 p_message_text => lv_message_text,
570 p_debug => MSC_CL_PRE_PROCESS.v_debug,
571 p_row => lv_column_names);
572
573 IF lv_return <> 0 THEN
574 RAISE ex_logging_err;
575 END IF;
576
577
578 --Call to customised validation.
579 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
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;
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,
597 p_debug => MSC_CL_PRE_PROCESS.v_debug,
598 p_batch_id => lv_batch_id);
599
600 IF lv_return <> 0 THEN
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,
610 p_error_text => lv_error_text,
611 p_debug => MSC_CL_PRE_PROCESS.v_debug,
612 p_batch_id => lv_batch_id);
613
614 IF lv_return <> 0 THEN
615 RAISE ex_logging_err;
616 END IF;
617 COMMIT;
618 END LOOP;
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
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
637 END LOAD_IRO_SUPPLY;
638
639 PROCEDURE LOAD_IRO_DEMAND IS
640 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
641 lb_rowid RowidTab;
642 lv_return NUMBER;
643 lv_error_text VARCHAR2(250);
644 lv_where_str VARCHAR2(5000);
645 lv_sql_stmt VARCHAR2(5000);
646 lv_column_names VARCHAR2(5000);
647 lv_batch_id msc_st_demands.batch_id%TYPE;
648 lv_message_text msc_errors.error_text%TYPE;
649
650 ex_logging_err EXCEPTION;
651
652 CURSOR c1(p_batch_id NUMBER) IS
653 SELECT rowid
654 FROM msc_st_demands
655 WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS)
656 AND batch_id = p_batch_id
657 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
658
659 BEGIN
660
661 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
662 (p_app_short_name => 'MSC',
663 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
664 p_message_text => lv_message_text,
665 p_error_text => lv_error_text);
666
667 IF lv_return <> 0 THEN
668 RAISE ex_logging_err;
669 END IF;
670
671 --Duplicate records check for the records whose source is other than XML
672 --Different SQL is used because in XML we can identify the latest records
673 --whereas in batch load we cannot.
674 MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
675 lv_sql_stmt :=
676 'UPDATE msc_st_demands msd1 '
677 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
678 ||' error_text = '||''''||lv_message_text||''''
679 ||' WHERE EXISTS( SELECT 1 '
680 ||' FROM msc_st_demands msd2'
681 ||' WHERE msd2.sr_instance_code = msd1.sr_instance_code'
682 ||' AND msd2.organization_code = msd1.organization_code'
683 ||' AND msd2.wip_entity_name = msd1.wip_entity_name'
684 ||' AND msd2.repair_number = msd1.repair_number'
685 ||' AND msd2.operation_seq_num = msd1.operation_seq_num '
686 ||' AND msd2.item_name = msd1.item_name '
687 ||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
688 ||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
689 ||' GROUP BY sr_instance_code,organization_code,wip_entity_name,'
690 ||' operation_seq_num,item_name,repair_number'
691 ||' HAVING COUNT(*) > 1)'
692 ||' AND msd1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
693 ||' AND msd1.origination_type =77'
694 ||' AND msd1.ENTITY =''IRO'''
695 ||' AND msd1.sr_instance_code = :v_instance_code'
696 ||' AND msd1.message_id IS NULL';
697
698
699 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
700 msc_st_util.log_message(lv_sql_stmt);
701 END IF;
702
703 EXECUTE IMMEDIATE lv_sql_stmt
704 USING MSC_CL_PRE_PROCESS.v_instance_code;
705
706 lv_column_names :=
707 'ITEM_NAME ||''~''||'
708 ||' ORGANIZATION_CODE ||''~''||'
709 ||' USING_REQUIREMENT_QUANTITY ||''~''||'
710 ||' REPAIR_NUMBER ||''~''||'
711 ||' WIP_ENTITY_NAME ||''~''||'
712 ||' OPERATION_SEQ_NUM ||''~''||'
713 ||' USING_ASSEMBLY_DEMAND_DATE ||''~''||'
714 ||' SR_INSTANCE_CODE ||''~''||'
715 ||' USING_ASSEMBLY_ITEM_NAME ||''~''||'
716 ||' PROJECT_NUMBER ||''~''||'
717 ||' TASK_NUMBER ||''~''||'
718 ||' DEMAND_CLASS ||''~''||'
719 ||' DELETED_FLAG ||''~''||'
720 ||' RO_STATUS_CODE ||''~''||'
721 ||' QUANTITY_ISSUED ||''~''||'
722 ||' COMPONENT_SCALING_TYPE ||''~''||'
723 ||' COMPONENT_YIELD_FACTOR' ;
724
725 LOOP
726 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
727 SELECT msc_st_batch_id_s.NEXTVAL
728 INTO lv_batch_id
729 FROM dual;
730
731 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
732 lv_sql_stmt :=
733 ' UPDATE msc_st_demands '
734 ||' SET batch_id = :lv_batch_id'
735 ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
736 ||' AND sr_instance_code = :v_instance_code'
737 ||' AND origination_type IN (77) '
738 ||' AND ENTITY = ''IRO'' '
739 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
740 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
741
742
743 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
744 msc_st_util.log_message(lv_sql_stmt);
745 END IF;
746
747 EXECUTE IMMEDIATE lv_sql_stmt
748 USING lv_batch_id,
749 MSC_CL_PRE_PROCESS.v_instance_code;
750
751 EXIT WHEN SQL%NOTFOUND;
752
753 OPEN c1(lv_batch_id);
754 FETCH c1 BULK COLLECT INTO lb_rowid;
755 CLOSE c1;
756
757 MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
758 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
759 UPDATE msc_st_demands
760 SET st_transaction_id = msc_st_demands_s.NEXTVAL,
761 refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
762 last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
763 last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
764 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
765 created_by = MSC_CL_PRE_PROCESS.v_current_user
766 WHERE rowid = lb_rowid(j);
767
768 -- set the error message
769
770 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
771 (p_app_short_name => 'MSC',
772 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
773 p_message_text => lv_message_text,
774 p_error_text => lv_error_text,
775 p_token1 => 'COLUMN_NAME',
776 p_token_value1 => 'DELETED_FLAG',
777 p_token2 => 'DEFAULT_VALUE',
778 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
779
780 IF lv_return <> 0 THEN
781 RAISE ex_logging_err;
782 END IF;
783
784 lv_where_str :=
785 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') '
786 ||' NOT IN(1,2)';
787 --Log a warning for those records where the deleted_flag has a value other
788 --than SYS_NO
789 lv_return := MSC_ST_UTIL.LOG_ERROR
790 (p_table_name => 'MSC_ST_DEMANDS',
791 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
792 p_row => lv_column_names,
793 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
794 p_message_text => lv_message_text,
795 p_error_text => lv_error_text,
796 p_batch_id => lv_batch_id,
797 p_where_str => lv_where_str,
798 p_col_name => 'DELETED_FLAG',
799 p_debug => MSC_CL_PRE_PROCESS.v_debug,
800 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
801
802 IF lv_return <> 0 THEN
803 RAISE ex_logging_err;
804 END IF;
805
806 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
807 (p_app_short_name => 'MSC',
808 p_error_code => 'MSC_PP_INVALID_VALUE',
809 p_message_text => lv_message_text,
810 p_error_text => lv_error_text,
811 p_token1 => 'COLUMN_NAME',
812 p_token_value1 => 'ORGANIZATION_CODE');
813
814 IF lv_return <> 0 THEN
815 RAISE ex_logging_err;
816 END IF;
817
818 --Derive Organization_id
819 lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
820 (p_table_name => 'MSC_ST_DEMANDS',
821 p_org_partner_name => 'ORGANIZATION_CODE',
822 p_org_partner_id => 'ORGANIZATION_ID',
823 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
824 p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,
825 p_error_text => lv_error_text,
826 p_batch_id => lv_batch_id,
827 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
828 p_message_text => lv_message_text,
829 p_debug => MSC_CL_PRE_PROCESS.v_debug,
830 p_row => lv_column_names);
831
832 IF lv_return <> 0 THEN
833 RAISE ex_logging_err;
834 END IF;
835
836 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
837 (p_app_short_name => 'MSC',
838 p_error_code => 'MSC_PP_INVALID_VALUE',
839 p_message_text => lv_message_text,
840 p_error_text => lv_error_text,
841 p_token1 => 'COLUMN_NAME',
842 p_token_value1 => 'ITEM_NAME');
843
844 IF lv_return <> 0 THEN
845 RAISE ex_logging_err;
846 END IF;
847
848 --Derive Inventory_item_id
849 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
850 (p_table_name => 'MSC_ST_DEMANDS',
851 p_item_col_name => 'ITEM_NAME',
852 p_item_col_id => 'INVENTORY_ITEM_ID',
853 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
854 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
855 p_error_text => lv_error_text,
856 p_batch_id => lv_batch_id,
857 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
858 p_message_text => lv_message_text,
859 p_debug => MSC_CL_PRE_PROCESS.v_debug,
860 p_row => lv_column_names);
861
862 IF lv_return <> 0 THEN
863 RAISE ex_logging_err;
864 END IF;
865
866 -- Set the message
867 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
868 (p_app_short_name => 'MSC',
869 p_error_code => 'MSC_PP_INVALID_VALUE',
870 p_message_text => lv_message_text,
871 p_error_text => lv_error_text,
872 p_token1 => 'COLUMN_NAME',
873 p_token_value1 => 'USING_ASSEMBLY_ITEM_NAME');
874
875 IF lv_return <> 0 THEN
876 RAISE ex_logging_err;
877 END IF;
878
879 --Derive Inventory_item_id
880 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
881 (p_table_name => 'MSC_ST_DEMANDS',
882 p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
883 p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
884 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
885 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
886 p_error_text => lv_error_text,
887 p_batch_id => lv_batch_id,
888 p_severity => MSC_CL_PRE_PROCESS.G_SEV3_ERROR,
889 p_message_text => lv_message_text,
890 p_debug => MSC_CL_PRE_PROCESS.v_debug,
891 p_row => lv_column_names);
892
893 IF lv_return <> 0 THEN
894 RAISE ex_logging_err;
895 END IF;
896
897 --- error out records where USING_REQUIREMENT_QUANTITY is NULL and using_assembly_demand_date is NULL
898
899 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
900 (p_app_short_name => 'MSC',
901 p_error_code => 'MSC_PP_COL_VAL_NULL',
902 p_message_text => lv_message_text,
903 p_error_text => lv_error_text,
904 p_token1 => 'COLUMN_NAME',
905 p_token_value1 => 'USING_REQUIREMENT_QUANTITY' || ' OR USING_ASSEMBLY_DEMAND_DATE');
906
907 IF lv_return <> 0 THEN
908 RAISE ex_logging_err;
909 END IF;
910
911 MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
912 lv_sql_stmt :=
913 'UPDATE msc_st_demands '
914 ||' SET error_text = '||''''||lv_message_text||''''||','
915 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
916 ||' WHERE (using_requirement_quantity IS NULL ' ||' OR using_assembly_demand_date IS NULL)'
917 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
918 ||' AND origination_type IN (77)'
919 ||' AND ENTITY = ''IRO'''
920 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
921 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
922 ||' AND sr_instance_code = :v_instance_code';
923
924 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
925 msc_st_util.log_message(lv_sql_stmt);
926 END IF;
927
928 EXECUTE IMMEDIATE lv_sql_stmt
929 USING lv_batch_id,
930 MSC_CL_PRE_PROCESS.v_instance_code;
931
932 --- error out record if repair_number is null:
933 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
934 (p_app_short_name => 'MSC',
935 p_error_code => 'MSC_PP_COL_VAL_NULL',
936 p_message_text => lv_message_text,
937 p_error_text => lv_error_text,
938 p_token1 => 'COLUMN_NAME',
939 p_token_value1 => 'REPAIR_NUMBER');
940
941 IF lv_return <> 0 THEN
942 RAISE ex_logging_err;
943 END IF;
944
945 MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
946 lv_sql_stmt :=
947 'UPDATE msc_st_demands '
948 ||' SET error_text = '||''''||lv_message_text||''''||','
949 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
950 ||' WHERE repair_number is NULL'
951 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
952 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
953 ||' AND sr_instance_code = :v_instance_code';
954
955 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
956 msc_st_util.log_message(lv_sql_stmt);
957 END IF;
958
959 EXECUTE IMMEDIATE lv_sql_stmt
960 USING lv_batch_id,
961 MSC_CL_PRE_PROCESS.v_instance_code;
962
963 --- derive repair line id
964
965 MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
966 lv_sql_stmt :=
967 'UPDATE msc_st_demands msd'
968 ||' SET repair_line_id = (SELECT local_id'
969 ||' FROM msc_local_id_supply mls'
970 ||' WHERE mls.char4 = msd.wip_entity_name'
971 ||' AND mls.char3 = msd.organization_code'
972 ||' AND mls.char1 = msd.sr_instance_code'
973 ||' AND mls.entity_name = ''REPAIR_NUMBER'' )'
974 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
975 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
976 ||' AND sr_instance_code = :v_instance_code';
977
978 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
979 msc_st_util.log_message(lv_sql_stmt);
980 END IF;
981
982 EXECUTE IMMEDIATE lv_sql_stmt
983 USING lv_batch_id,
984 MSC_CL_PRE_PROCESS.v_instance_code;
985
986
987 ---error out the record where repair line is null and deleted flag is SYS_YES
988
989 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
990 (p_app_short_name => 'MSC',
991 p_error_code => 'MSC_PP_DELETE_FAIL',
992 p_message_text => lv_message_text,
993 p_error_text => lv_error_text);
994
995 IF lv_return <> 0 THEN
996 RAISE ex_logging_err;
997 END IF;
998 MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
999
1000 lv_sql_stmt :=
1001 'UPDATE msc_st_demands '
1002 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1003 ||' error_text = '||''''||lv_message_text||''''
1004 ||' WHERE repair_line_id is null '
1005 ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
1006 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1007 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1008 ||' AND sr_instance_code =:v_instance_code';
1009
1010 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1011 msc_st_util.log_message(lv_sql_stmt);
1012 END IF;
1013
1014 EXECUTE IMMEDIATE lv_sql_stmt
1015 USING lv_batch_id,
1016 MSC_CL_PRE_PROCESS.v_instance_code;
1017
1018 ---- validating project and task :
1019 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
1020 (p_table_name => 'MSC_ST_DEMANDS',
1021 p_proj_col_name => 'PROJECT_NUMBER',
1022 p_proj_task_col_id => 'PROJECT_ID',
1023 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1024 p_entity_name => 'PROJECT_ID',
1025 p_error_text => lv_error_text,
1026 p_batch_id => lv_batch_id,
1027 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1028 p_message_text => lv_message_text,
1029 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1030 p_row => lv_column_names);
1031 IF lv_return <> 0 THEN
1032 RAISE ex_logging_err;
1033 END IF;
1034
1035 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1036 (p_app_short_name => 'MSC',
1037 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
1038 p_message_text => lv_message_text,
1039 p_error_text => lv_error_text,
1040 p_token1 => 'COLUMN_NAMES',
1041 p_token_value1 => ' SR_INSTANCE_CODE,'
1042 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
1043 ||' TASK_NUMBER',
1044 p_token2 => 'MASTER_TABLE',
1045 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
1046
1047 IF lv_return <> 0 THEN
1048 RAISE ex_logging_err;
1049 END IF;
1050
1051 --Derive Task Id.
1052 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
1053 (p_table_name => 'MSC_ST_DEMANDS',
1054 p_proj_col_name => 'PROJECT_NUMBER',
1055 p_proj_task_col_id => 'TASK_ID',
1056 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1057 p_entity_name => 'TASK_ID',
1058 p_error_text => lv_error_text,
1059 p_task_col_name => 'TASK_NUMBER',
1060 p_batch_id => lv_batch_id,
1061 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1062 p_message_text => lv_message_text,
1063 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1064 p_row => lv_column_names);
1065
1066 IF lv_return <> 0 THEN
1067 RAISE ex_logging_err;
1068 END IF;
1069
1070 --Call to customised validation.
1071 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
1072 (ERRBUF => lv_error_text,
1073 RETCODE => lv_return,
1074 pBatchID => lv_batch_id,
1075 pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
1076 pEntityName => 'MSC_ST_DEMANDS',
1077 pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
1078
1079 IF NVL(lv_return,0) <> 0 THEN
1080 RAISE ex_logging_err;
1081 END IF;
1082
1083 lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
1084 (p_table_name => 'MSC_ST_DEMANDS',
1085 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
1086 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1087 p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
1088 p_error_text => lv_error_text,
1089 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1090 p_batch_id => lv_batch_id);
1091
1092 IF lv_return <> 0 THEN
1093 RAISE ex_logging_err;
1094 END IF;
1095
1096 lv_return := MSC_ST_UTIL.LOG_ERROR
1097 (p_table_name => 'MSC_ST_DEMANDS',
1098 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1099 p_row => lv_column_names,
1100 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1101 p_message_text => NULL,
1102 p_error_text => lv_error_text,
1103 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1104 p_batch_id => lv_batch_id);
1105
1106 IF lv_return <> 0 THEN
1107 RAISE ex_logging_err;
1108 END IF;
1109 COMMIT;
1110 END LOOP;
1111
1112 EXCEPTION
1113
1114 WHEN too_many_rows THEN
1115 lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_DEMANDS'||'('
1116 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
1117 ROLLBACK ;
1118
1119 WHEN ex_logging_err THEN
1120 msc_st_util.log_message(lv_error_text);
1121 ROLLBACK;
1122
1123 WHEN OTHERS THEN
1124 lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_DEMANDS '||'('
1125 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
1126 msc_st_util.log_message(lv_error_text);
1127 ROLLBACK;
1128
1129 END LOAD_IRO_DEMAND;
1130
1131 PROCEDURE LOAD_ERO_SUPPLY IS
1132 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1133 lb_rowid RowidTab;
1134 lv_return NUMBER;
1135 lv_error_text VARCHAR2(250);
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
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;
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;
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
1166 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
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
1176 AND NVL(JOB_OP_SEQ_CODE, MSC_CL_PRE_PROCESS.NULL_CHAR) <> MSC_CL_PRE_PROCESS.NULL_CHAR
1177 AND order_type = 86
1178 AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO;
1179
1180 BEGIN
1181
1182 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1183 (p_app_short_name => 'MSC',
1184 p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
1185 p_message_text => lv_message_text,
1186 p_error_text => lv_error_text);
1187
1188 IF lv_return <> 0 THEN
1189 RAISE ex_logging_err;
1190 END IF;
1191
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'
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||''''||')'
1205 ||' AND mss2.wip_entity_name = mss1.wip_entity_name '
1206 ||' AND mss2.order_type = mss1.order_type'
1207 ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1208 ||' AND mss2.organization_code = mss1.organization_code'
1209 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<>'||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
1210 ||' AND mss1.order_type =86'
1211 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1212 ||' AND mss1.sr_instance_code = :v_instance_code'
1213 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') <> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
1214
1215 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1216 msc_st_util.log_message(lv_sql_stmt);
1217 END IF;
1218
1219 EXECUTE IMMEDIATE lv_sql_stmt
1220 USING MSC_CL_PRE_PROCESS.v_instance_code;
1221
1222
1223 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1224 (p_app_short_name => 'MSC',
1225 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
1226 p_message_text => lv_message_text,
1227 p_error_text => lv_error_text);
1228
1229 IF lv_return <> 0 THEN
1230 RAISE ex_logging_err;
1231 END IF;
1232
1233 --Duplicate records check for the records whose source is other than XML
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'
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||''''||')'
1247 ||' AND mss2.wip_entity_name = mss1.wip_entity_name '
1248 ||' AND mss2.order_type = mss1.order_type'
1249 ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1250 ||' AND mss2.organization_code = mss1.organization_code'
1251 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1252 ||' GROUP BY sr_instance_code,wip_entity_name,organization_code,company_name,'
1253 ||' order_type'
1254 ||' HAVING COUNT(*) > 1)'
1255 ||' AND mss1.order_type =86'
1256 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1257 ||' AND mss1.sr_instance_code = :v_instance_code'
1258 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
1259
1260 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1261 msc_st_util.log_message(lv_sql_stmt);
1262 END IF;
1263
1264 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
1265
1266 lv_column_names :=
1267 'ITEM_NAME ||''~''||'
1268 ||'ORGANIZATION_CODE ||''~''||'
1269 ||'NEW_SCHEDULE_DATE ||''~''||'
1270 ||'FIRM_PLANNED_TYPE ||''~''||'
1271 ||'WIP_ENTITY_NAME ||''~''||'
1272 ||'SR_INSTANCE_CODE ||''~''||'
1273 ||'REVISION ||''~''||'
1274 ||'UNIT_NUMBER ||''~''||'
1275 ||'NEW_WIP_START_DATE ||''~''||'
1276 ||'NEW_ORDER_QUANTITY ||''~''||'
1277 ||'ALTERNATE_BOM_DESIGNATOR ||''~''||'
1278 ||'ALTERNATE_ROUTING_DESIGNATOR ||''~''||'
1279 ||'LINE_CODE ||''~''||'
1280 ||'PROJECT_NUMBER ||''~''||'
1281 ||'TASK_NUMBER ||''~''||'
1282 ||'PLANNING_GROUP ||''~''||'
1283 ||'SCHEDULE_GROUP_NAME ||''~''||'
1284 ||'BUILD_SEQUENCE ||''~''||'
1285 ||'WO_LATENESS_COST ||''~''||'
1286 ||'IMPLEMENT_PROCESSING_DAYS ||''~''||'
1287 ||'LATE_SUPPLY_DATE ||''~''||'
1288 ||'LATE_SUPPLY_QTY ||''~''||'
1289 ||'QTY_SCRAPPED ||''~''||'
1290 ||'QTY_COMPLETED ||''~''||'
1291 ||'WIP_STATUS_CODE ||''~''||'
1292 ||'BILL_NAME ||''~''||'
1293 ||'ROUTING_NAME ||''~''||'
1294 ||'DELETED_FLAG ||''~''||'
1295 ||'COMPANY_NAME ||''~''||'
1296 ||'ORDER_TYPE ||''~''||'
1297 ||'ORDER_NUMBER';
1298
1299 LOOP
1300 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1301 SELECT msc_st_batch_id_s.NEXTVAL
1302 INTO lv_batch_id
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'
1312 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1313 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
1314
1315 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1316 msc_st_util.log_message(lv_sql_stmt);
1317 END IF;
1318
1319 EXECUTE IMMEDIATE lv_sql_stmt
1320 USING lv_batch_id,
1321 MSC_CL_PRE_PROCESS.v_instance_code;
1322
1323 EXIT WHEN SQL%NOTFOUND;
1324
1325 OPEN c1(lv_batch_id);
1326 FETCH c1 BULK COLLECT INTO lb_rowid;
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,
1336 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
1337 created_by = MSC_CL_PRE_PROCESS.v_current_user
1338 WHERE rowid = lb_rowid(j);
1339
1340 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1341 (p_app_short_name => 'MSC',
1342 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1343 p_message_text => lv_message_text,
1344 p_error_text => lv_error_text,
1345 p_token1 => 'COLUMN_NAME',
1346 p_token_value1 => 'DELETED_FLAG',
1347 p_token2 => 'DEFAULT_VALUE',
1348 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1349
1350 IF lv_return <> 0 THEN
1351 RAISE ex_logging_err;
1352 END IF;
1353
1354 lv_where_str :=
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,
1364 p_error_text => lv_error_text,
1365 p_batch_id => lv_batch_id,
1366 p_where_str => lv_where_str,
1367 p_col_name => 'DELETED_FLAG',
1368 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1369 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
1370
1371 IF lv_return <> 0 THEN
1372 RAISE ex_logging_err;
1373 END IF;
1374
1375 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1376 (p_app_short_name => 'MSC',
1377 p_error_code => 'MSC_PP_INVALID_VALUE',
1378 p_message_text => lv_message_text,
1379 p_error_text => lv_error_text,
1380 p_token1 => 'COLUMN_NAME',
1381 p_token_value1 => 'ORGANIZATION_CODE');
1382
1383 IF lv_return <> 0 THEN
1384 RAISE ex_logging_err;
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,
1394 p_error_text => lv_error_text,
1395 p_batch_id => lv_batch_id,
1396 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1397 p_message_text => lv_message_text,
1398 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1399 p_row => lv_column_names);
1400
1401 IF lv_return <> 0 THEN
1402 RAISE ex_logging_err;
1403 END IF;
1404
1405 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1406 (p_app_short_name => 'MSC',
1407 p_error_code => 'MSC_PP_INVALID_VALUE',
1408 p_message_text => lv_message_text,
1409 p_error_text => lv_error_text,
1410 p_token1 => 'COLUMN_NAME',
1411 p_token_value1 => 'ITEM_NAME');
1412
1413 IF lv_return <> 0 THEN
1414 RAISE ex_logging_err;
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,
1424 p_error_text => lv_error_text,
1425 p_batch_id => lv_batch_id,
1426 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1427 p_message_text => lv_message_text,
1428 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1429 p_row => lv_column_names);
1430
1431 IF lv_return <> 0 THEN
1432 RAISE ex_logging_err;
1433 END IF;
1434
1435 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1436 (p_app_short_name => 'MSC',
1437 p_error_code => 'MSC_PP_COL_VAL_NULL',
1438 p_message_text => lv_message_text,
1439 p_error_text => lv_error_text,
1440 p_token1 => 'COLUMN_NAME',
1441 p_token_value1 => 'NEW_SCHEDULE_DATE OR NEW_ORDER_QUANTITY');
1442
1443 IF lv_return <> 0 THEN
1444 RAISE ex_logging_err;
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||')'
1454 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1455 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1456 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
1457 ||' AND sr_instance_code = :v_instance_code';
1458
1459 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1460 msc_st_util.log_message(lv_sql_stmt);
1461 END IF;
1462
1463 EXECUTE IMMEDIATE lv_sql_stmt
1464 USING lv_batch_id,
1465 MSC_CL_PRE_PROCESS.v_instance_code;
1466
1467 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1468 (p_app_short_name => 'MSC',
1469 p_error_code => 'MSC_PP_COL_VAL_NULL',
1470 p_message_text => lv_message_text,
1471 p_error_text => lv_error_text,
1472 p_token1 => 'COLUMN_NAME',
1473 p_token_value1 => 'WIP_ENTITY_NAME');
1474
1475 IF lv_return <> 0 THEN
1476 RAISE ex_logging_err;
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||''''
1486 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1487 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1488 ||' AND sr_instance_code = :v_instance_code';
1489
1490 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1491 msc_st_util.log_message(lv_sql_stmt);
1492 END IF;
1493
1494 EXECUTE IMMEDIATE lv_sql_stmt
1495 USING lv_batch_id,
1496 MSC_CL_PRE_PROCESS.v_instance_code;
1497
1498 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1499 (p_app_short_name => 'MSC',
1500 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1501 p_message_text => lv_message_text,
1502 p_error_text => lv_error_text,
1503 p_token1 => 'COLUMN_NAME',
1504 p_token_value1 => 'FIRM_PLANNED_TYPE',
1505 p_token2 => 'DEFAULT_VALUE',
1506 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1507
1508 IF lv_return <> 0 THEN
1509 RAISE ex_logging_err;
1510 END IF;
1511
1512 lv_where_str :=
1513 ' AND NVL(firm_planned_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)'
1514 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1515
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,
1525 p_error_text => lv_error_text,
1526 p_batch_id => lv_batch_id,
1527 p_where_str => lv_where_str,
1528 p_col_name => 'FIRM_PLANNED_TYPE',
1529 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1530 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
1531
1532 IF lv_return <> 0 THEN
1533 RAISE ex_logging_err;
1534 END IF;
1535
1536 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1537 (p_app_short_name => 'MSC',
1538 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1539 p_message_text => lv_message_text,
1540 p_error_text => lv_error_text,
1541 p_token1 => 'COLUMN_NAME',
1542 p_token_value1 => 'WIP_STATUS_CODE',
1543 p_token2 => 'DEFAULT_VALUE',
1544 p_token_value2 => 1);
1545
1546 IF lv_return <> 0 THEN
1547 RAISE ex_logging_err;
1548 END IF;
1549
1550 lv_where_str := ' AND wip_status_code <= 1'
1551 ||' AND wip_status_code >= 15'
1552 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1553
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,
1563 p_error_text => lv_error_text,
1564 p_batch_id => lv_batch_id,
1565 p_where_str => lv_where_str,
1566 p_col_name => 'WIP_STATUS_CODE',
1567 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1568 p_default_value => 1);
1569
1570 IF lv_return <> 0 THEN
1571 RAISE ex_logging_err;
1572 END IF;
1573
1574 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1575 (p_app_short_name => 'MSC',
1576 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1577 p_message_text => lv_message_text,
1578 p_error_text => lv_error_text,
1579 p_token1 => 'COLUMN_NAME',
1580 p_token_value1 => 'DISPOSITION_STATUS_TYPE',
1581 p_token2 => 'DEFAULT_VALUE',
1582 p_token_value2 => 1);
1583
1584 IF lv_return <> 0 THEN
1585 RAISE ex_logging_err;
1586 END IF;
1587
1588 lv_where_str :=
1589 ' AND NVL(disposition_status_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)'
1590 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1591
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,
1601 p_error_text => lv_error_text,
1602 p_batch_id => lv_batch_id,
1603 p_where_str => lv_where_str,
1604 p_col_name => 'DISPOSITION_STATUS_TYPE',
1605 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1606 p_default_value => 1);
1607
1608 IF lv_return <> 0 THEN
1609 RAISE ex_logging_err;
1610 END IF;
1611
1612 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1613 (p_app_short_name => 'MSC',
1614 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1615 p_message_text => lv_message_text,
1616 p_error_text => lv_error_text,
1617 p_token1 => 'COLUMN_NAME',
1618 p_token_value1 => 'WIP_SUPPLY_TYPE',
1619 p_token2 => 'DEFAULT_VALUE',
1620 p_token_value2 => 1);
1621
1622 IF lv_return <> 0 THEN
1623 RAISE ex_logging_err;
1624 END IF;
1625
1626 lv_where_str := ' AND wip_supply_type <= 1'
1627 ||' AND wip_supply_type >= 7'
1628 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1629
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,
1639 p_error_text => lv_error_text,
1640 p_batch_id => lv_batch_id,
1641 p_where_str => lv_where_str,
1642 p_col_name => 'WIP_SUPPLY_TYPE',
1643 p_debug =>MSC_CL_PRE_PROCESS.v_debug,
1644 p_default_value => 1);
1645
1646 IF lv_return <> 0 THEN
1647 RAISE ex_logging_err;
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
1657 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1658 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
1659 ||' AND sr_instance_code = :v_instance_code';
1660
1661 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1662 msc_st_util.log_message(lv_sql_stmt);
1663 END IF;
1664
1665 EXECUTE IMMEDIATE lv_sql_stmt
1666 USING lv_batch_id,
1667 MSC_CL_PRE_PROCESS.v_instance_code;
1668
1669 -- Now we will check whether BOM Name is NULL , if it is NULL we will populate the
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
1679 ||' AND NVL(bill_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1680 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1681 ||' AND NVL(item_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1682 ||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1683 ||' AND batch_id = :lv_batch_id';
1684
1685 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1686 msc_st_util.log_message(lv_sql_stmt);
1687 END IF;
1688
1689 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
1690
1691 -- Now check whether Routing Name is NULL , if it is NULL we will populate
1692 -- Assembly Name in Routing Name column for all such records
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
1702 ||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1703 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1704 ||' AND NVL(item_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1705 ||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1706 ||' AND batch_id = :lv_batch_id ';
1707
1708 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1709 msc_st_util.log_message(lv_sql_stmt);
1710 END IF;
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,
1720 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1721 p_error_text => lv_error_text);
1722
1723
1724 IF (lv_return <> 0 ) THEN
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,
1734 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1735 p_error_text => lv_error_text);
1736
1737 if (lv_return <> 0 )then
1738 RAISE ex_logging_err;
1739 end if;
1740
1741
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'
1751 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1752 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1753 ||' AND mls.char1 = mss.sr_instance_code'
1754 ||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' ),'
1755 ||' line_id = (SELECT local_id'
1756 ||' FROM msc_local_id_setup mls'
1757 ||' WHERE mls.char4 = mss.line_code'
1758 ||' AND mls.char3 = mss.organization_code'
1759 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1760 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1761 ||' AND mls.char1 = mss.sr_instance_code'
1762 ||' AND mls.entity_name = ''LINE_ID''),'
1763 ||' operation_seq_num = (SELECT number1'
1764 ||' FROM msc_local_id_setup mls'
1765 ||' WHERE mls.char5 = mss.operation_seq_code'
1766 ||' AND mls.char4 = mss.routing_name'
1767 ||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1768 ||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1769 ||' AND mls.char3 = mss.organization_code'
1770 ||' AND mls.date1 = mss.effectivity_date'
1771 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1772 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1773 ||' AND mls.char1 = mss.sr_instance_code'
1774 ||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
1775 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1776 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1777 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1778 ||' AND sr_instance_code = :v_instance_code';
1779
1780 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1781 msc_st_util.log_message(lv_sql_stmt);
1782 END IF;
1783
1784 EXECUTE IMMEDIATE lv_sql_stmt
1785 USING lv_batch_id,
1786 MSC_CL_PRE_PROCESS.v_instance_code;
1787
1788
1789 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1790 (p_app_short_name => 'MSC',
1791 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1792 p_message_text => lv_message_text,
1793 p_error_text => lv_error_text,
1794 p_token1 => 'COLUMN_NAME',
1795 p_token_value1 => 'BILL_SEQUENCE_ID OR ROUTING_SEQUENCE_ID',
1796 p_token2 => 'DEFAULT_VALUE',
1797 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1798
1799 IF lv_return <> 0 THEN
1800 RAISE ex_logging_err;
1801 END IF;
1802
1803 lv_where_str :=
1804 ' AND (NVL(bill_sequence_id, '||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1805 ||' OR NVL(routing_sequence_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
1806 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1807
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,
1817 p_error_text => lv_error_text,
1818 p_batch_id => lv_batch_id,
1819 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1820 p_where_str => lv_where_str);
1821
1822 IF lv_return <> 0 THEN
1823 RAISE ex_logging_err;
1824 END IF;
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'
1834 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1835 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1836 ||' AND mls.char1 = mss.sr_instance_code'
1837 ||' AND mls.entity_name = ''WIP_ENTITY_ID'' )'
1838 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1839 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
1840 ||' AND sr_instance_code = :v_instance_code';
1841
1842 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1843 msc_st_util.log_message(lv_sql_stmt);
1844 END IF;
1845
1846 EXECUTE IMMEDIATE lv_sql_stmt
1847 USING lv_batch_id,
1848 MSC_CL_PRE_PROCESS.v_instance_code;
1849
1850
1851 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1852 (p_app_short_name => 'MSC',
1853 p_error_code => 'MSC_PP_DELETE_FAIL',
1854 p_message_text => lv_message_text,
1855 p_error_text => lv_error_text);
1856
1857 IF lv_return <> 0 THEN
1858 RAISE ex_logging_err;
1859 END IF;
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
1869 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1870 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1871 ||' AND sr_instance_code =:v_instance_code';
1872
1873 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1874 msc_st_util.log_message(lv_sql_stmt);
1875 END IF;
1876
1877 EXECUTE IMMEDIATE lv_sql_stmt
1878 USING lv_batch_id,
1879 MSC_CL_PRE_PROCESS.v_instance_code;
1880
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
1890 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1891 ||' AND order_type =86'
1892 ||' AND sr_instance_code =:v_instance_code';
1893
1894 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1895 msc_st_util.log_message(lv_sql_stmt);
1896 END IF;
1897
1898 EXECUTE IMMEDIATE lv_sql_stmt
1899 USING lv_batch_id,
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||''''||') '
1909 ||' AND mls.char4 = mss.routing_name'
1910 ||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1911 ||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1912 ||' AND mls.char3 = mss.organization_code'
1913 ||' AND mls.date1 = mss.jump_op_effectivity_date'
1914 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1915 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1916 ||' AND mls.char1 = mss.sr_instance_code'
1917 ||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
1918 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1919 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1920 ||' AND jump_op_seq_num <> 50000 '
1921 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1922 ||' AND sr_instance_code = :v_instance_code';
1923
1924
1925 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1926 msc_st_util.log_message(lv_sql_stmt);
1927 END IF;
1928
1929 EXECUTE IMMEDIATE lv_sql_stmt
1930 USING lv_batch_id,
1931 MSC_CL_PRE_PROCESS.v_instance_code;
1932
1933
1934 --Call to customised validation.
1935 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
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;
1945 END IF;
1946
1947 --Generation of wip_entity_id
1948 OPEN c2(lv_batch_id);
1949 FETCH c2 BULK COLLECT INTO lb_rowid ;
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;
1959 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1960 INSERT INTO msc_local_id_supply
1961 (local_id,
1962 st_transaction_id,
1963 instance_id,
1964 entity_name,
1965 data_source_type,
1966 char1,
1967 char2,
1968 char3,
1969 char4,
1970 SOURCE_ORG_ID,
1971 SOURCE_INVENTORY_ITEM_ID,
1972 SOURCE_BILL_SEQUENCE_ID,
1973 SOURCE_ROUTING_SEQUENCE_ID,
1974 SOURCE_SCHEDULE_GROUP_ID,
1975 SOURCE_WIP_ENTITY_ID,
1976 last_update_date,
1977 last_updated_by,
1978 creation_date,
1979 created_by)
1980 SELECT
1981 wip_entity_id,
1982 st_transaction_id,
1983 MSC_CL_PRE_PROCESS.v_instance_id,
1984 'WIP_ENTITY_ID',
1985 data_source_type,
1986 MSC_CL_PRE_PROCESS.v_instance_code,
1987 company_name,
1988 organization_code ,
1989 wip_entity_name,
1990 SOURCE_ORG_ID,
1991 SOURCE_INVENTORY_ITEM_ID,
1992 SOURCE_BILL_SEQUENCE_ID,
1993 SOURCE_ROUTING_SEQUENCE_ID,
1994 SOURCE_SCHEDULE_GROUP_ID,
1995 SOURCE_WIP_ENTITY_ID,
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 ;
2005
2006 --Generation of schedule_group_id
2007 OPEN c3(lv_batch_id);
2008 FETCH c3 BULK COLLECT INTO lb_rowid ;
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;
2018 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2019 INSERT INTO msc_local_id_supply
2020 (local_id,
2021 st_transaction_id,
2022 instance_id,
2023 entity_name,
2024 data_source_type,
2025 char1,
2026 char2,
2027 char3,
2028 char4,
2029 SOURCE_ORG_ID,
2030 SOURCE_INVENTORY_ITEM_ID,
2031 SOURCE_BILL_SEQUENCE_ID,
2032 SOURCE_ROUTING_SEQUENCE_ID,
2033 SOURCE_SCHEDULE_GROUP_ID,
2034 SOURCE_WIP_ENTITY_ID,
2035 last_update_date,
2036 last_updated_by,
2037 creation_date,
2038 created_by)
2039 SELECT
2040 schedule_group_id,
2041 st_transaction_id,
2042 MSC_CL_PRE_PROCESS.v_instance_id,
2043 'SCHEDULE_GROUP_ID',
2044 data_source_type,
2045 MSC_CL_PRE_PROCESS.v_instance_code,
2046 company_name,
2047 organization_code ,
2048 schedule_group_name,
2049 SOURCE_ORG_ID,
2050 SOURCE_INVENTORY_ITEM_ID,
2051 SOURCE_BILL_SEQUENCE_ID,
2052 SOURCE_ROUTING_SEQUENCE_ID,
2053 SOURCE_SCHEDULE_GROUP_ID,
2054 SOURCE_WIP_ENTITY_ID,
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;
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;
2072
2073 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2074 (p_app_short_name => 'MSC',
2075 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
2076 p_message_text => lv_message_text,
2077 p_error_text => lv_error_text,
2078 p_token1 => 'COLUMN_NAMES',
2079 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
2080 ||' ORGANIZATION_CODE AND PROJECT_NUMBER',
2081 p_token2 => 'MASTER_TABLE',
2082 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
2083
2084 IF lv_return <> 0 THEN
2085 RAISE ex_logging_err;
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',
2095 p_error_text => lv_error_text,
2096 p_batch_id => lv_batch_id,
2097 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2098 p_message_text => lv_message_text,
2099 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2100 p_row => lv_column_names);
2101 IF lv_return <> 0 THEN
2102 RAISE ex_logging_err;
2103 END IF;
2104
2105 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2106 (p_app_short_name => 'MSC',
2107 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
2108 p_message_text => lv_message_text,
2109 p_error_text => lv_error_text,
2110 p_token1 => 'COLUMN_NAMES',
2111 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
2112 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
2113 ||' TASK_NUMBER',
2114 p_token2 => 'MASTER_TABLE',
2115 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
2116
2117 IF lv_return <> 0 THEN
2118 RAISE ex_logging_err;
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',
2128 p_error_text => lv_error_text,
2129 p_task_col_name => 'TASK_NUMBER',
2130 p_batch_id => lv_batch_id,
2131 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2132 p_message_text => lv_message_text,
2133 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2134 p_row => lv_column_names);
2135
2136 IF lv_return <> 0 THEN
2137 RAISE ex_logging_err;
2138 END IF;
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'
2148 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
2149 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2150 ||' AND mls.char1 = mss.sr_instance_code'
2151 ||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' )'
2152 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2153 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2154 ||' AND NVL(schedule_group_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2155 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
2156 ||' AND sr_instance_code = :v_instance_code';
2157
2158 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2159 msc_st_util.log_message(lv_sql_stmt);
2160 END IF;
2161
2162 EXECUTE IMMEDIATE lv_sql_stmt
2163 USING lv_batch_id,
2164 MSC_CL_PRE_PROCESS.v_instance_code;
2165
2166 OPEN c4(lv_batch_id);
2167 FETCH c4 BULK COLLECT INTO lb_rowid ;
2168
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);
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,
2187 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2188 p_batch_id => lv_batch_id);
2189 IF lv_return <> 0 THEN
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,
2199 p_error_text => lv_error_text,
2200 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2201 p_batch_id => lv_batch_id);
2202
2203 IF lv_return <> 0 THEN
2204 RAISE ex_logging_err;
2205 END IF;
2206
2207 COMMIT;
2208 END LOOP;
2209 EXCEPTION
2210 WHEN too_many_rows THEN
2211 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_ERO_SUPPLY'||'('
2212 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
2213 msc_st_util.log_message(lv_error_text);
2214 ROLLBACK;
2215
2216 WHEN ex_logging_err THEN
2217 msc_st_util.log_message(lv_error_text);
2218 ROLLBACK;
2219
2220 WHEN OTHERS THEN
2221 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_WO_SUPPLY'||'('
2222 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
2223 msc_st_util.log_message(lv_error_text);
2224 ROLLBACK;
2225
2226 END LOAD_ERO_SUPPLY;
2227
2228 PROCEDURE LOAD_ERO_DEMAND IS
2229
2230 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2231
2232 lb_rowid RowidTab;
2233
2234 lv_local_id NUMBER;
2235 lv_sequence NUMBER;
2236 lv_column_names VARCHAR2(5000); -- Stores cocatenated column names
2237 lv_return NUMBER;
2238 lv_error_text VARCHAR2(250);
2239 lv_where_str VARCHAR2(5000);
2240 lv_sql_stmt VARCHAR2(5000);
2241 lv_cursor_stmt VARCHAR2(5000);
2242 lv_batch_id msc_st_demands.batch_id%TYPE;
2243 lv_message_text msc_errors.error_text%TYPE;
2244
2245 ex_logging_err EXCEPTION;
2246
2247 CURSOR c1(p_batch_id NUMBER) IS
2248 SELECT rowid
2249 FROM msc_st_demands
2250 WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
2251 AND origination_type =77
2252 AND batch_id = p_batch_id
2253 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
2254 AND ENTITY='ERO';
2255
2256 CURSOR c2(p_batch_id NUMBER) IS
2257 SELECT max(rowid)
2258 FROM msc_st_demands
2259 WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
2260 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
2261 AND batch_id = p_batch_id
2262 AND origination_type =77 -- Not for flow schedule
2263 AND NVL(operation_seq_num,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
2264 AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
2265 AND ENTITY='ERO'
2266 GROUP BY sr_instance_code,company_name,organization_code,routing_name,
2267 operation_seq_code,alternate_routing_designator,operation_effectivity_date;
2268
2269
2270 BEGIN
2271
2272 -- Before we start processing the record by group id( batch size ) we are going
2273 -- to check whether that there
2274 -- is any duplicates for user defined unique keys (UDKs,)
2275
2276 --For WIP component demand
2277
2278 --Duplicate records check for the records whose source is XML
2279 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2280 (p_app_short_name => 'MSC',
2281 p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
2282 p_message_text => lv_message_text,
2283 p_error_text => lv_error_text);
2284
2285 IF lv_return <> 0 THEN
2286 RAISE ex_logging_err;
2287 END IF;
2288
2289 MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
2290
2291 lv_sql_stmt :=
2292 'UPDATE msc_st_demands msd1'
2293 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2294 ||' error_text = '||''''||lv_message_text||''''
2295 ||' WHERE message_id < (SELECT MAX(message_id)'
2296 ||' FROM msc_st_demands msd2'
2297 ||' WHERE msd2.sr_instance_code '
2298 ||' = msd1.sr_instance_code '
2299 ||' AND msd2.organization_code '
2300 ||' = msd1.organization_code '
2301 ||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2302 ||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2303 ||' AND msd2.wip_entity_name = msd1.wip_entity_name'
2304 ||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2305 ||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2306 ||' AND msd2.item_name '
2307 ||' = msd1.item_name '
2308 ||' AND msd2.origination_type '
2309 ||' = msd1.origination_type'
2310 ||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2311 ||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
2312 ||' AND msd1.process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
2313 ||' AND msd1.origination_type =77 '
2314 ||' AND msd1.ENTITY =''ERO'''
2315 ||' AND msd1.sr_instance_code = :v_instance_code '
2316 ||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
2317
2318 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2319 msc_st_util.log_message(lv_sql_stmt);
2320 END IF;
2321
2322 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
2323
2324 --Duplicate records check for the records whose source is batch load
2325
2326 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2327 (p_app_short_name => 'MSC',
2328 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
2329 p_message_text => lv_message_text,
2330 p_error_text => lv_error_text);
2331
2332 IF lv_return <> 0 THEN
2333 RAISE ex_logging_err;
2334 END IF;
2335
2336 MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
2337
2338 lv_sql_stmt :=
2339 'UPDATE msc_st_demands msd1'
2340 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2341 ||' error_text = '||''''||lv_message_text||''''
2342 ||' WHERE EXISTS( SELECT 1 '
2343 ||' FROM msc_st_demands msd2'
2344 ||' WHERE msd2.sr_instance_code '
2345 ||' = msd1.sr_instance_code '
2346 ||' AND msd2.organization_code '
2347 ||' = msd1.organization_code '
2348 ||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2349 ||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2350 ||' AND msd2.wip_entity_name = msd1.wip_entity_name'
2351 ||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2352 ||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2353 ||' AND msd2.item_name '
2354 ||' = msd1.item_name '
2355 ||' AND msd2.origination_type '
2356 ||' = msd1.origination_type'
2357 ||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2358 ||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2359 ||' GROUP BY sr_instance_code,organization_code,wip_entity_name,'
2360 ||' company_name,operation_seq_code,item_name,origination_type'
2361 ||' HAVING COUNT(*) > 1)'
2362 ||' AND msd1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2363 ||' AND msd1.origination_type =77'
2364 ||' AND msd1.ENTITY =''ERO'''
2365 ||' AND msd1.sr_instance_code = :v_instance_code'
2366 ||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
2367
2368 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2369 msc_st_util.log_message(lv_sql_stmt);
2370 END IF;
2371
2372 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
2373
2374 lv_column_names :=
2375 'ITEM_NAME ||''~''||'
2376 ||' ORGANIZATION_CODE ||''~''||'
2377 ||' USING_REQUIREMENT_QUANTITY ||''~''||'
2378 ||' WIP_ENTITY_NAME ||''~''||'
2379 ||' USING_ASSEMBLY_DEMAND_DATE ||''~''||'
2380 ||' SR_INSTANCE_CODE ||''~''||'
2381 ||' USING_ASSEMBLY_ITEM_NAME ||''~''||'
2382 ||' OPERATION_SEQ_CODE ||''~''||'
2383 ||' ORIGINATION_TYPE ||''~''||'
2384 ||' PROJECT_NUMBER ||''~''||'
2385 ||' TASK_NUMBER ||''~''||'
2386 ||' PLANNING_GROUP ||''~''||'
2387 ||' END_ITEM_UNIT_NUMBER ||''~''||'
2388 ||' DEMAND_CLASS ||''~''||'
2389 ||' WIP_STATUS_CODE ||''~''||'
2390 ||' WIP_SUPPLY_TYPE ||''~''||'
2391 ||' DELETED_FLAG ||''~''||'
2392 ||' COMPANY_NAME ||''~''||'
2393 ||' DEMAND_TYPE' ;
2394
2395
2396 LOOP
2397 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
2398 SELECT msc_st_batch_id_s.NEXTVAL
2399 INTO lv_batch_id
2400 FROM DUAL;
2401
2402 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
2403 lv_sql_stmt :=
2404 ' UPDATE msc_st_demands '
2405 ||' SET batch_id = :lv_batch_id'
2406 ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
2407 ||' AND sr_instance_code = :v_instance_code'
2408 ||' AND origination_type =77'
2409 ||' AND ENTITY =''ERO'''
2410 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2411 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
2412
2413
2414 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2415 msc_st_util.log_message(lv_sql_stmt);
2416 END IF;
2417
2418 EXECUTE IMMEDIATE lv_sql_stmt
2419 USING lv_batch_id,
2420 MSC_CL_PRE_PROCESS.v_instance_code;
2421
2422 EXIT WHEN SQL%NOTFOUND ;
2423
2424 OPEN c1(lv_batch_id);
2425 FETCH c1 BULK COLLECT INTO lb_rowid;
2426 CLOSE c1;
2427
2428 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
2429 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2430 UPDATE msc_st_demands
2431 SET st_transaction_id = msc_st_demands_s.NEXTVAL,
2432 refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
2433 last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
2434 last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
2435 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
2436 created_by = MSC_CL_PRE_PROCESS.v_current_user
2437 WHERE rowid = lb_rowid(j);
2438
2439 -- Set the error message
2440 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2441 (p_app_short_name => 'MSC',
2442 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
2443 p_message_text => lv_message_text,
2444 p_error_text => lv_error_text,
2445 p_token1 => 'COLUMN_NAME',
2446 p_token_value1 => 'DELETED_FLAG',
2447 p_token2 => 'DEFAULT_VALUE',
2448 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO );
2449
2450 IF lv_return <> 0 THEN
2451 RAISE ex_logging_err;
2452 END IF;
2453 --Log a warning for those records where the deleted_flag has a value other
2454 --SYS_NO
2455
2456 lv_where_str :=
2457 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
2458
2459 lv_return := MSC_ST_UTIL.LOG_ERROR
2460 (p_table_name => 'MSC_ST_DEMANDS',
2461 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2462 p_row => lv_column_names,
2463 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2464 p_message_text => lv_message_text,
2465 p_error_text => lv_error_text,
2466 p_batch_id => lv_batch_id,
2467 p_where_str => lv_where_str,
2468 p_col_name => 'DELETED_FLAG',
2469 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2470 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
2471
2472 IF lv_return <> 0 THEN
2473 RAISE ex_logging_err;
2474 END IF;
2475
2476 -- Set the message
2477 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2478 (p_app_short_name => 'MSC',
2479 p_error_code => 'MSC_PP_INVALID_VALUE',
2480 p_message_text => lv_message_text,
2481 p_error_text => lv_error_text,
2482 p_token1 => 'COLUMN_NAME',
2483 p_token_value1 => 'ORGANIZATION_CODE');
2484
2485 IF lv_return <> 0 THEN
2486 RAISE ex_logging_err;
2487 END IF;
2488
2489 --Derive Organization_id
2490 lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
2491 (p_table_name => 'MSC_ST_DEMANDS',
2492 p_org_partner_name => 'ORGANIZATION_CODE',
2493 p_org_partner_id => 'ORGANIZATION_ID',
2494 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2495 p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,
2496 p_error_text => lv_error_text,
2497 p_batch_id => lv_batch_id,
2498 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2499 p_message_text => lv_message_text,
2500 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2501 p_row => lv_column_names);
2502
2503 IF lv_return <> 0 THEN
2504 RAISE ex_logging_err;
2505 END IF;
2506
2507 -- Set the message
2508 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2509 (p_app_short_name => 'MSC',
2510 p_error_code => 'MSC_PP_INVALID_VALUE',
2511 p_message_text => lv_message_text,
2512 p_error_text => lv_error_text,
2513 p_token1 => 'COLUMN_NAME',
2514 p_token_value1 => 'ITEM_NAME');
2515
2516 IF lv_return <> 0 THEN
2517 RAISE ex_logging_err;
2518 END IF;
2519
2520 --Derive Inventory_item_id
2521 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
2522 (p_table_name => 'MSC_ST_DEMANDS',
2523 p_item_col_name => 'ITEM_NAME',
2524 p_item_col_id => 'INVENTORY_ITEM_ID',
2525 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2526 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2527 p_message_text => lv_message_text,
2528 p_error_text => lv_error_text,
2529 p_batch_id => lv_batch_id,
2530 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2531 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2532 p_row => lv_column_names);
2533
2534 IF lv_return <> 0 THEN
2535 RAISE ex_logging_err;
2536 END IF;
2537
2538 -- Set the message
2539 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2540 (p_app_short_name => 'MSC',
2541 p_error_code => 'MSC_PP_INVALID_VALUE',
2542 p_message_text => lv_message_text,
2543 p_error_text => lv_error_text,
2544 p_token1 => 'COLUMN_NAME',
2545 p_token_value1 => 'USING_ASSEMBLY_ITEM_NAME');
2546
2547 IF lv_return <> 0 THEN
2548 RAISE ex_logging_err;
2549 END IF;
2550
2551 --Derive Using_assembly_item_id
2552 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
2553 (p_table_name => 'MSC_ST_DEMANDS',
2554 p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
2555 p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
2556 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2557 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2558 p_message_text => lv_message_text,
2559 p_error_text => lv_error_text,
2560 p_batch_id => lv_batch_id,
2561 p_severity => MSC_CL_PRE_PROCESS.G_SEV3_ERROR,
2562 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2563 p_row => lv_column_names);
2564
2565 IF lv_return <> 0 THEN
2566 RAISE ex_logging_err;
2567 END IF;
2568
2569 -- Derive WIP_ENTITY_ID
2570 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
2571
2572 lv_sql_stmt :=
2573 'UPDATE msc_st_demands msd'
2574 ||' SET wip_entity_id = ( SELECT local_id '
2575 ||' FROM msc_local_id_supply mlid'
2576 ||' WHERE mlid.char1 = msd.sr_instance_code'
2577 ||' AND NVL(mlid.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2578 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2579 ||' AND mlid.char3 = msd.organization_code'
2580 ||' AND mlid.char4 = msd.wip_entity_name'
2581 ||' AND mlid.entity_name = ''WIP_ENTITY_ID'' )'
2582 ||' WHERE origination_type =77 '
2583 ||' AND ENTITY=''ERO'''
2584 ||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2585 ||' AND batch_id = :lv_batch_id'
2586 ||' AND sr_instance_code =:v_instance_code';
2587
2588 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2589 msc_st_util.log_message(lv_sql_stmt);
2590 END IF;
2591
2592 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2593
2594 -- Set the error message
2595 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2596 (p_app_short_name => 'MSC',
2597 p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
2598 p_message_text => lv_message_text,
2599 p_error_text => lv_error_text,
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
2609 RAISE ex_logging_err;
2610 END IF;
2611
2612 -- Error out records where WIP_ENTITY_ID is NULL;
2613
2614 MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
2615 lv_sql_stmt :=
2616 'UPDATE msc_st_demands '
2617 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2618 ||' error_text = '||''''||lv_message_text||''''
2619 ||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') ='||MSC_CL_PRE_PROCESS.NULL_VALUE
2620 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2621 ||' AND origination_type =77'
2622 ||' AND ENTITY=''ERO'''
2623 ||' AND batch_id = :lv_batch_id'
2624 ||' AND sr_instance_code = :v_instance_code';
2625
2626 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2627 msc_st_util.log_message(lv_sql_stmt);
2628 END IF;
2629
2630 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2631
2632
2633 -- Set the message
2634 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2635 (p_app_short_name => 'MSC',
2636 p_error_code => 'MSC_PP_COL_VAL_NULL',
2637 p_message_text => lv_message_text,
2638 p_error_text => lv_error_text,
2639 p_token1 => 'COLUMN_NAME',
2640 p_token_value1 => 'USING_REQUIREMENT_QUANTITY'
2641 || ' OR USING_ASSEMBLY_DEMAND_DATE');
2642
2643 IF lv_return <> 0 THEN
2644 RAISE ex_logging_err;
2645 END IF;
2646
2647
2648 -- Error out records where USING_REQUIREMENT_QUANTITY is NULL;
2649 -- Error out records where using_assembly_demand_date is NULL
2650
2651 MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
2652 lv_sql_stmt :=
2653 'UPDATE msc_st_demands '
2654 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2655 ||' error_text = '||''''||lv_message_text||''''
2656 ||' WHERE (NVL(using_requirement_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')='||MSC_CL_PRE_PROCESS.NULL_VALUE
2657 ||' OR NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500 )'
2658 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2659 ||' AND origination_type =77'
2660 ||' AND ENTITY=''ERO'''
2661 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2662 ||' AND batch_id = :lv_batch_id'
2663 ||' AND sr_instance_code = :v_instance_code';
2664
2665 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2666 msc_st_util.log_message(lv_sql_stmt);
2667 END IF;
2668
2669 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2670
2671
2672 -- Update using_assembly_item_id = inventory_item_id
2673
2674 MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
2675 lv_sql_stmt :=
2676 ' UPDATE msc_st_demands'
2677 ||' SET using_assembly_item_id = inventory_item_id'
2678 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2679 ||' AND NVL(using_assembly_item_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2680 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2681 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2682 ||' AND origination_type =77'
2683 ||' AND ENTITY=''ERO'''
2684 ||' AND batch_id = :lv_batch_id'
2685 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2686 ||' AND sr_instance_code = :v_instance_code';
2687
2688 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2689 msc_st_util.log_message(lv_sql_stmt);
2690 END IF;
2691
2692 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2693
2694 -- Update order_number = wip_entity_name
2695
2696 MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
2697 lv_sql_stmt :=
2698 ' UPDATE msc_st_demands'
2699 ||' SET order_number = wip_entity_name'
2700 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2701 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2702 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2703 ||' AND origination_type =77'
2704 ||' AND ENTITY=''ERO'''
2705 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2706 ||' AND batch_id = :lv_batch_id'
2707 ||' AND sr_instance_code = :v_instance_code';
2708
2709 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2710 msc_st_util.log_message(lv_sql_stmt);
2711 END IF;
2712
2713 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2714
2715 -- Update disposition_id = wip_entity_id
2716
2717 MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
2718 lv_sql_stmt :=
2719 ' UPDATE msc_st_demands'
2720 ||' SET disposition_id = wip_entity_id'
2721 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2722 ||' AND NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2723 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2724 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2725 ||' AND origination_type =77'
2726 ||' AND ENTITY=''ERO'''
2727 ||' AND batch_id = :lv_batch_id'
2728 ||' AND sr_instance_code = :v_instance_code';
2729
2730 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2731 msc_st_util.log_message(lv_sql_stmt);
2732 END IF;
2733
2734 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2735
2736
2737
2738 -- UPdate MPS_DATE_REQUIRED as using_assembly_demand_date if NULL
2739 -- This is not reqd for flow schedule
2740
2741 MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
2742 lv_sql_stmt :=
2743 ' UPDATE msc_st_demands'
2744 ||' SET mps_date_required = using_assembly_demand_date'
2745 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2746 ||' AND NVL(mps_date_required,SYSDATE-36500) = SYSDATE-36500'
2747 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2748 ||' AND origination_type =77'
2749 ||' AND ENTITY=''ERO'''
2750 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2751 ||' AND batch_id = :lv_batch_id'
2752 ||' AND sr_instance_code = :v_instance_code';
2753
2754 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2755 msc_st_util.log_message(lv_sql_stmt);
2756 END IF;
2757
2758 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2759
2760
2761 -- Set the error message
2762 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2763 (p_app_short_name => 'MSC',
2764 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
2765 p_message_text => lv_message_text,
2766 p_error_text => lv_error_text,
2767 p_token1 => 'COLUMN_NAME',
2768 p_token_value1 => 'OPERATION_SEQ_CODE',
2769 p_token2 => 'DEFAULT_VALUE',
2770 p_token_value2 => MSC_CL_PRE_PROCESS.G_OPERATION_SEQ_CODE );
2771
2772 IF lv_return <> 0 THEN
2773 RAISE ex_logging_err;
2774 END IF;
2775
2776 -- Default operation_seq_code as 1 if NULL
2777 lv_where_str :=
2778 ' AND NVL(operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2779 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
2780 ||' AND origination_type =77'
2781 ||' AND ENTITY=''ERO''';
2782
2783 lv_return := MSC_ST_UTIL.LOG_ERROR
2784 (p_table_name => 'MSC_ST_DEMANDS',
2785 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2786 p_row => lv_column_names,
2787 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2788 p_message_text => lv_message_text,
2789 p_error_text => lv_error_text,
2790 p_batch_id => lv_batch_id,
2791 p_where_str => lv_where_str,
2792 p_col_name => 'OPERATION_SEQ_CODE',
2793 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2794 p_default_value => MSC_CL_PRE_PROCESS.G_OPERATION_SEQ_CODE);
2795
2796 IF lv_return <> 0 THEN
2797 RAISE ex_logging_err;
2798 END IF;
2799
2800 -- Set the error message
2801 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2802 (p_app_short_name => 'MSC',
2803 p_error_code => 'MSC_PP_COL_VAL_NULL_DEFAULT',
2804 p_message_text => lv_message_text,
2805 p_error_text => lv_error_text,
2806 p_token1 => 'COLUMN_NAME',
2807 p_token_value1 => 'OPERATION_EFFECTIVITY_DATE',
2808 p_token2 => 'DEFAULT_VALUE',
2809 p_token_value2 => SYSDATE );
2810
2811 IF lv_return <> 0 THEN
2812 RAISE ex_logging_err;
2813 END IF;
2814
2815 -- Default operation_effectivity date as SYSDATE if NULL
2816
2817 lv_where_str :=
2818 ' AND NVL(operation_effectivity_date,SYSDATE-36500 ) = SYSDATE-36500 '
2819 ||' AND origination_type =77'
2820 ||' AND ENTITY=''ERO''' ;
2821
2822 lv_return := MSC_ST_UTIL.LOG_ERROR
2823 (p_table_name => 'MSC_ST_DEMANDS',
2824 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2825 p_row => lv_column_names,
2826 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2827 p_message_text => lv_message_text,
2828 p_error_text => lv_error_text,
2829 p_batch_id => lv_batch_id,
2830 p_where_str => lv_where_str,
2831 p_col_name => 'OPERATION_EFFECTIVITY_DATE',
2832 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2833 p_default_value => 'SYSDATE');
2834
2835 IF lv_return <> 0 THEN
2836 RAISE ex_logging_err;
2837 END IF;
2838
2839 -- If routing_name is is NULL populate the item_name in routing name
2840
2841 MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
2842 lv_sql_stmt :=
2843 ' UPDATE msc_st_demands'
2844 ||' SET routing_name = nvl(USING_ASSEMBLY_ITEM_NAME,item_name)' /* bug 3768813 */
2845 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2846 ||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
2847 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
2848 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2849 ||' AND origination_type =77'
2850 ||' AND ENTITY=''ERO'''
2851 ||' AND batch_id = :lv_batch_id'
2852 ||' AND sr_instance_code = :v_instance_code';
2853
2854 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2855 msc_st_util.log_message(lv_sql_stmt);
2856 END IF;
2857
2858 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2859
2860 /* bug 3768813 */
2861 IF MSC_CL_PRE_PROCESS.v_instance_type <> MSC_CL_PRE_PROCESS.G_INS_OTHER THEN
2862
2863 -- Derive the ROUTING_SEQUENCE_ID from LOCAL ID table
2864
2865 lv_return :=msc_st_util.derive_routing_sequence_id
2866 (p_table_name => 'MSC_ST_DEMANDS',
2867 p_rtg_col_name => 'ROUTING_NAME',
2868 p_rtg_col_id =>'ROUTING_SEQUENCE_ID',
2869 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2870 p_batch_id => lv_batch_id,
2871 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2872 p_error_text => lv_error_text,
2873 p_item_id => 'using_assembly_item_id');
2874
2875 if (lv_return <> 0 )then
2876 msc_st_util.log_message(lv_error_text);
2877 end if;
2878
2879 MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
2880 lv_sql_stmt:=
2881 'update msc_st_demands msd'
2882 ||' set operation_seq_num = '
2883 ||' (select operation_seq_num '
2884 ||' from msc_routing_operations mro '
2885 ||' where mro.routing_sequence_id = msd.routing_sequence_id and '
2886 ||' mro.effectivity_date = msd.operation_effectivity_date and '
2887 ||' mro.SR_INSTANCE_ID = '||MSC_CL_PRE_PROCESS.v_instance_id||' and '
2888 ||' mro.operation_seq_num = to_number(decode(length(rtrim(msd.operation_seq_code,''0123456789'')),'
2889 ||' NULL,msd.operation_seq_code,''1'')) and'
2890 ||' mro.plan_id = -1 and '
2891 ||' mro.operation_type = 1)'
2892 ||' WHERE sr_instance_code = :v_instance_code'
2893 ||' AND process_flag = '|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
2894 ||' AND batch_id = :lv_batch_id ';
2895
2896 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2897 msc_st_util.log_message(lv_sql_stmt);
2898 END IF;
2899
2900 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
2901 END IF;
2902
2903 -- Derive operation seq num from local id table
2904
2905 MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
2906 lv_sql_stmt:=
2907 'UPDATE msc_st_demands msd'
2908 ||' SET operation_seq_num= (SELECT number1'
2909 ||' FROM msc_local_id_setup mlis'
2910 ||' WHERE mlis.char1 = msd.sr_instance_code'
2911 ||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2912 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2913 ||' AND mlis.char3 = msd.organization_code'
2914 ||' AND mlis.char4 = msd.routing_name'
2915 ||' AND mlis.char5 = msd.operation_seq_code'
2916 ||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2917 ||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2918 ||' AND mlis.date1 = msd.operation_effectivity_date'
2919 ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
2920 ||' WHERE sr_instance_code = :v_instance_code'
2921 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2922 ||' AND batch_id = :lv_batch_id'
2923 ||' AND operation_seq_num is null'; /* bug 3768813 */
2924
2925
2926 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2927 msc_st_util.log_message(lv_sql_stmt);
2928 END IF;
2929
2930 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
2931
2932 -- Set the message
2933
2934 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2935 (p_app_short_name => 'MSC',
2936 p_error_code => 'MSC_PP_DELETE_FAIL',
2937 p_message_text => lv_message_text,
2938 p_error_text => lv_error_text);
2939
2940 IF lv_return <> 0 THEN
2941 RAISE ex_logging_err;
2942 END IF;
2943
2944 -- Error out the records where operation_seq_num is NULL
2945 -- And deleted_flag = SYS_YES
2946
2947 MSC_CL_PRE_PROCESS.v_sql_stmt := 13;
2948
2949 lv_sql_stmt :=
2950 'UPDATE msc_st_demands '
2951 ||' SET process_flag ='||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2952 ||' error_text = '||''''||lv_message_text||''''
2953 ||' WHERE NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2954 ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
2955 ||' AND origination_type =77'
2956 ||' AND ENTITY=''ERO'''
2957 ||' AND batch_id = :lv_batch_id'
2958 ||' AND sr_instance_code =:v_instance_code';
2959
2960 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2961 msc_st_util.log_message(lv_sql_stmt);
2962 END IF;
2963
2964 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2965
2966 -- Set the error message
2967 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2968 (p_app_short_name => 'MSC',
2969 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
2970 p_message_text => lv_message_text,
2971 p_error_text => lv_error_text,
2972 p_token1 => 'COLUMN_NAMES',
2973 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
2974 ||' ORGANIZATION_CODE AND PROJECT_NUMBER',
2975 p_token2 => 'MASTER_TABLE',
2976 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
2977
2978 IF lv_return <> 0 THEN
2979 RAISE ex_logging_err;
2980 END IF;
2981
2982 --Derive Project Id.
2983 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2984 (p_table_name => 'MSC_ST_DEMANDS',
2985 p_proj_col_name => 'PROJECT_NUMBER',
2986 p_proj_task_col_id => 'PROJECT_ID',
2987 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2988 p_entity_name => 'PROJECT_ID',
2989 p_error_text => lv_error_text,
2990 p_batch_id => lv_batch_id,
2991 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2992 p_message_text => lv_message_text,
2993 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2994 p_row => lv_column_names);
2995 IF lv_return <> 0 THEN
2996 RAISE ex_logging_err;
2997 END IF;
2998
2999 -- Set the error message
3000 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3001 (p_app_short_name => 'MSC',
3002 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
3003 p_message_text => lv_message_text,
3004 p_error_text => lv_error_text,
3005 p_token1 => 'COLUMN_NAMES',
3006 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
3007 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
3008 ||' TASK_NUMBER',
3009 p_token2 => 'MASTER_TABLE',
3010 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
3011
3012 IF lv_return <> 0 THEN
3013 RAISE ex_logging_err;
3014 END IF;
3015
3016 --Derive Task Id.
3017 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
3018 (p_table_name => 'MSC_ST_DEMANDS',
3019 p_proj_col_name => 'PROJECT_NUMBER',
3020 p_proj_task_col_id => 'TASK_ID',
3021 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3022 p_entity_name => 'TASK_ID',
3023 p_error_text => lv_error_text,
3024 p_task_col_name => 'TASK_NUMBER',
3025 p_batch_id => lv_batch_id,
3026 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3027 p_message_text => lv_message_text,
3028 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3029 p_row => lv_column_names);
3030
3031 IF lv_return <> 0 THEN
3032 RAISE ex_logging_err;
3033 END IF;
3034
3035 -- Set the error message
3036 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3037 (p_app_short_name => 'MSC',
3038 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3039 p_message_text => lv_message_text,
3040 p_error_text => lv_error_text,
3041 p_token1 => 'COLUMN_NAME',
3042 p_token_value1 => 'DEMAND_TYPE',
3043 p_token2 => 'DEFAULT_VALUE',
3044 p_token_value2 => MSC_CL_PRE_PROCESS.G_DEMAND_TYPE );
3045
3046 IF lv_return <> 0 THEN
3047 RAISE ex_logging_err;
3048 END IF;
3049
3050 -- Default demand_type to 1 always
3051
3052 lv_where_str := ' AND NVL(demand_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') <> '||MSC_CL_PRE_PROCESS.G_DEMAND_TYPE
3053 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3054 ||' AND ENTITY=''ERO''' ;
3055
3056 lv_return := MSC_ST_UTIL.LOG_ERROR
3057 (p_table_name => 'MSC_ST_DEMANDS',
3058 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3059 p_row => lv_column_names,
3060 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3061 p_message_text => lv_message_text,
3062 p_error_text => lv_error_text,
3063 p_batch_id => lv_batch_id,
3064 p_where_str => lv_where_str,
3065 p_col_name => 'DEMAND_TYPE',
3066 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3067 p_default_value => MSC_CL_PRE_PROCESS.G_DEMAND_TYPE);
3068
3069 IF lv_return <> 0 THEN
3070 RAISE ex_logging_err;
3071 END IF;
3072
3073
3074 -- Set the error message
3075 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3076 (p_app_short_name => 'MSC',
3077 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3078 p_message_text => lv_message_text,
3079 p_error_text => lv_error_text,
3080 p_token1 => 'COLUMN_NAME',
3081 p_token_value1 => 'WIP_SUPPLY_TYPE',
3082 p_token2 => 'DEFAULT_VALUE',
3083 p_token_value2 => MSC_CL_PRE_PROCESS.G_WIP_SUPPLY_TYPE );
3084
3085 IF lv_return <> 0 THEN
3086 RAISE ex_logging_err;
3087 END IF;
3088
3089 -- Default wip_supply_type as 1
3090
3091 lv_where_str := ' AND NVL(wip_supply_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN (1,2,3,4,5,6,7)'
3092 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3093 ||' AND ENTITY=''ERO''' ;
3094
3095 lv_return := MSC_ST_UTIL.LOG_ERROR
3096 (p_table_name => 'MSC_ST_DEMANDS',
3097 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3098 p_row => lv_column_names,
3099 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3100 p_message_text => lv_message_text,
3101 p_error_text => lv_error_text,
3102 p_batch_id => lv_batch_id,
3103 p_where_str => lv_where_str,
3104 p_col_name => 'WIP_SUPPLY_TYPE',
3105 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3106 p_default_value => MSC_CL_PRE_PROCESS.G_WIP_SUPPLY_TYPE );
3107
3108 IF lv_return <> 0 THEN
3109 RAISE ex_logging_err;
3110 END IF;
3111
3112
3113 -- Set the error message
3114 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3115 (p_app_short_name => 'MSC',
3116 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3117 p_message_text => lv_message_text,
3118 p_error_text => lv_error_text,
3119 p_token1 => 'COLUMN_NAME',
3120 p_token_value1 => 'WIP_STATUS_CODE',
3121 p_token2 => 'DEFAULT_VALUE',
3122 p_token_value2 => MSC_CL_PRE_PROCESS.G_WIP_STATUS_CODE );
3123
3124 IF lv_return <> 0 THEN
3125 RAISE ex_logging_err;
3126 END IF;
3127
3128 -- Default wip_status_code as 1(unrelased)
3129
3130 lv_where_str := ' AND NVL(wip_status_code,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN (1,3,4,6,7,12)'
3131 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3132 ||' AND ENTITY=''ERO''' ;
3133
3134 lv_return := MSC_ST_UTIL.LOG_ERROR
3135 (p_table_name => 'MSC_ST_DEMANDS',
3136 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3137 p_row => lv_column_names,
3138 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3139 p_message_text => lv_message_text,
3140 p_error_text => lv_error_text,
3141 p_batch_id => lv_batch_id,
3142 p_where_str => lv_where_str,
3143 p_col_name => 'WIP_STATUS_CODE',
3144 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3145 p_default_value => MSC_CL_PRE_PROCESS.G_WIP_STATUS_CODE);
3146
3147 IF lv_return <> 0 THEN
3148 RAISE ex_logging_err;
3149 END IF;
3150
3151 --Call to customised validation.
3152 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
3153 (ERRBUF => lv_error_text,
3154 RETCODE => lv_return,
3155 pBatchID => lv_batch_id,
3156 pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
3157 pEntityName => 'MSC_ST_DEMANDS',
3158 pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
3159
3160 IF NVL(lv_return,0) <> 0 THEN
3161 RAISE ex_logging_err;
3162 END IF;
3163
3164 -- Generate the operation_seq_num and populate the LID table
3165
3166 OPEN c2(lv_batch_id);
3167 FETCH c2 BULK COLLECT INTO lb_rowid ;
3168
3169
3170 IF c2%ROWCOUNT > 0 THEN
3171 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
3172
3173 -- SELECT msc_st_operation_sequence_id_s.NEXTVAL
3174 -- INTO lv_local_id
3175 -- FROM DUAL;
3176
3177 UPDATE msc_st_demands
3178 SET operation_seq_num =
3179 to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
3180 NULL,operation_seq_code,'1'))
3181 WHERE rowid = lb_rowid(j);
3182
3183 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
3184
3185 -- Insert into the LID table
3186
3187 INSERT INTO msc_local_id_setup
3188 (local_id,
3189 st_transaction_id,
3190 instance_id,
3191 entity_name,
3192 data_source_type,
3193 char1,
3194 char2,
3195 char3,
3196 char4,
3197 char5,
3198 char6,
3199 number1,
3200 date1,
3201 SOURCE_ORGANIZATION_ID,
3202 SOURCE_INVENTORY_ITEM_ID,
3203 SOURCE_PROJECT_ID,
3204 SOURCE_TASK_ID,
3205 SOURCE_WIP_ENTITY_ID,
3206 SOURCE_OPERATION_SEQ_NUM,
3207 SOURCE_USING_ASSEMBLY_ID,
3208 last_update_date,
3209 last_updated_by,
3210 creation_date,
3211 created_by )
3212 SELECT
3213 msc_st_operation_sequence_id_s.NEXTVAL,
3214 st_transaction_id,
3215 MSC_CL_PRE_PROCESS.v_instance_id,
3216 'OPERATION_SEQUENCE_ID',
3217 data_source_type,
3218 MSC_CL_PRE_PROCESS.v_instance_code,
3219 company_name,
3220 organization_code,
3221 routing_name,
3222 operation_seq_code,
3223 alternate_routing_designator,
3224 operation_seq_num,
3225 operation_effectivity_date,
3226 SOURCE_ORGANIZATION_ID,
3227 SOURCE_INVENTORY_ITEM_ID,
3228 SOURCE_PROJECT_ID,
3229 SOURCE_TASK_ID,
3230 SOURCE_WIP_ENTITY_ID,
3231 SOURCE_OPERATION_SEQ_NUM,
3232 SOURCE_USING_ASSEMBLY_ITEM_ID,
3233 MSC_CL_PRE_PROCESS.v_current_date,
3234 MSC_CL_PRE_PROCESS.v_current_user,
3235 MSC_CL_PRE_PROCESS.v_current_date,
3236 MSC_CL_PRE_PROCESS.v_current_user
3237 FROM msc_st_demands
3238 WHERE rowid = lb_rowid(j) ;
3239
3240 END IF;
3241 CLOSE c2;
3242
3243 -- Update operation seq num from local id table
3244
3245 MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
3246 lv_sql_stmt:=
3247 'UPDATE msc_st_demands msd'
3248 ||' SET operation_seq_num= (SELECT number1'
3249 ||' FROM msc_local_id_setup mlis'
3250 ||' WHERE mlis.char1 = msd.sr_instance_code'
3251 ||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3252 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3253 ||' AND mlis.char3 = msd.organization_code'
3254 ||' AND mlis.char4 = msd.routing_name'
3255 ||' AND mlis.char5 = msd.operation_seq_code'
3256 ||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3257 ||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3258 ||' AND mlis.date1 = msd.operation_effectivity_date'
3259 ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
3260 ||' WHERE sr_instance_code = :v_instance_code'
3261 ||' AND NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= '||MSC_CL_PRE_PROCESS.NULL_VALUE
3262 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
3263 ||' AND batch_id = :lv_batch_id';
3264
3265
3266 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
3267 msc_st_util.log_message(lv_sql_stmt);
3268 END IF;
3269
3270 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
3271
3272
3273 lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
3274 (p_table_name => 'MSC_ST_DEMANDS',
3275 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
3276 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3277 p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
3278 p_error_text => lv_error_text,
3279 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3280 p_batch_id => lv_batch_id);
3281
3282 IF lv_return <> 0 THEN
3283 RAISE ex_logging_err;
3284 END IF;
3285
3286 lv_return := MSC_ST_UTIL.LOG_ERROR
3287 (p_table_name => 'MSC_ST_DEMANDS',
3288 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3289 p_row => lv_column_names,
3290 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
3291 p_message_text => NULL,
3292 p_error_text => lv_error_text,
3293 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3294 p_batch_id => lv_batch_id);
3295
3296 IF lv_return <> 0 THEN
3297 RAISE ex_logging_err;
3298 END IF;
3299
3300 COMMIT;
3301 END LOOP ;
3302
3303 EXCEPTION
3304 WHEN too_many_rows THEN
3305 lv_error_text := substr('MSC_CL__RPO_PRE_PROCESS.LOAD_ERO_DEMAND'||'('
3306 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
3307 msc_st_util.log_message(lv_error_text);
3308 ROLLBACK ;
3309
3310 WHEN ex_logging_err THEN
3311 msc_st_util.log_message(lv_error_text);
3312 ROLLBACK;
3313
3314 WHEN OTHERS THEN
3315 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_ERO_DEMAND'||'('
3316 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
3317 msc_st_util.log_message(lv_error_text);
3318 ROLLBACK;
3319
3320 END LOAD_ERO_DEMAND;
3321
3322
3323
3324
3325 END MSC_CL_RPO_PRE_PROCESS;