[Home] [Help]
Skip to content
PACKAGE BODY: APPS.MSC_CL_RPO_PRE_PROCESS
Source
1 PACKAGE BODY MSC_CL_RPO_PRE_PROCESS AS
2 /* $Header: MSCRPOLB.pls 120.5 2010/04/15 10:21:37 vsiyer ship $ */
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 ||' ITEM_TYPE_VALUE ' ;
725
726 LOOP
727 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
728 SELECT msc_st_batch_id_s.NEXTVAL
729 INTO lv_batch_id
730 FROM dual;
731
732 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
733 lv_sql_stmt :=
734 ' UPDATE msc_st_demands '
735 ||' SET batch_id = :lv_batch_id'
736 ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
737 ||' AND sr_instance_code = :v_instance_code'
738 ||' AND origination_type IN (77) '
739 ||' AND ENTITY = ''IRO'' '
740 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
741 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
742
743
744 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
745 msc_st_util.log_message(lv_sql_stmt);
746 END IF;
747
748 EXECUTE IMMEDIATE lv_sql_stmt
749 USING lv_batch_id,
750 MSC_CL_PRE_PROCESS.v_instance_code;
751
752 EXIT WHEN SQL%NOTFOUND;
753
754 OPEN c1(lv_batch_id);
755 FETCH c1 BULK COLLECT INTO lb_rowid;
756 CLOSE c1;
757
758 MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
759 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
760 UPDATE msc_st_demands
761 SET st_transaction_id = msc_st_demands_s.NEXTVAL,
762 refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
763 last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
764 last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
765 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
766 created_by = MSC_CL_PRE_PROCESS.v_current_user
767 WHERE rowid = lb_rowid(j);
768
769 -- set the error message
770
771 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
772 (p_app_short_name => 'MSC',
773 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
774 p_message_text => lv_message_text,
775 p_error_text => lv_error_text,
776 p_token1 => 'COLUMN_NAME',
777 p_token_value1 => 'DELETED_FLAG',
778 p_token2 => 'DEFAULT_VALUE',
779 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
780
781 IF lv_return <> 0 THEN
782 RAISE ex_logging_err;
783 END IF;
784
785 lv_where_str :=
786 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') '
787 ||' NOT IN(1,2)';
788 --Log a warning for those records where the deleted_flag has a value other
789 --than SYS_NO
790 lv_return := MSC_ST_UTIL.LOG_ERROR
791 (p_table_name => 'MSC_ST_DEMANDS',
792 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
793 p_row => lv_column_names,
794 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
795 p_message_text => lv_message_text,
796 p_error_text => lv_error_text,
797 p_batch_id => lv_batch_id,
798 p_where_str => lv_where_str,
799 p_col_name => 'DELETED_FLAG',
800 p_debug => MSC_CL_PRE_PROCESS.v_debug,
801 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
802
803 IF lv_return <> 0 THEN
804 RAISE ex_logging_err;
805 END IF;
806
807 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
808 (p_app_short_name => 'MSC',
809 p_error_code => 'MSC_PP_INVALID_VALUE',
810 p_message_text => lv_message_text,
811 p_error_text => lv_error_text,
812 p_token1 => 'COLUMN_NAME',
813 p_token_value1 => 'ORGANIZATION_CODE');
814
815 IF lv_return <> 0 THEN
816 RAISE ex_logging_err;
817 END IF;
818
819 --Derive Organization_id
820 lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
821 (p_table_name => 'MSC_ST_DEMANDS',
822 p_org_partner_name => 'ORGANIZATION_CODE',
823 p_org_partner_id => 'ORGANIZATION_ID',
824 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
825 p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,
826 p_error_text => lv_error_text,
827 p_batch_id => lv_batch_id,
828 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
829 p_message_text => lv_message_text,
830 p_debug => MSC_CL_PRE_PROCESS.v_debug,
831 p_row => lv_column_names);
832
833 IF lv_return <> 0 THEN
834 RAISE ex_logging_err;
835 END IF;
836
837 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
838 (p_app_short_name => 'MSC',
839 p_error_code => 'MSC_PP_INVALID_VALUE',
840 p_message_text => lv_message_text,
841 p_error_text => lv_error_text,
842 p_token1 => 'COLUMN_NAME',
843 p_token_value1 => 'ITEM_NAME');
844
845 IF lv_return <> 0 THEN
846 RAISE ex_logging_err;
847 END IF;
848
849 --Derive Inventory_item_id
850 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
851 (p_table_name => 'MSC_ST_DEMANDS',
852 p_item_col_name => 'ITEM_NAME',
853 p_item_col_id => 'INVENTORY_ITEM_ID',
854 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
855 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
856 p_error_text => lv_error_text,
857 p_batch_id => lv_batch_id,
858 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
859 p_message_text => lv_message_text,
860 p_debug => MSC_CL_PRE_PROCESS.v_debug,
861 p_row => lv_column_names);
862
863 IF lv_return <> 0 THEN
864 RAISE ex_logging_err;
865 END IF;
866
867 -- Set the message
868 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
869 (p_app_short_name => 'MSC',
870 p_error_code => 'MSC_PP_INVALID_VALUE',
871 p_message_text => lv_message_text,
872 p_error_text => lv_error_text,
873 p_token1 => 'COLUMN_NAME',
874 p_token_value1 => 'USING_ASSEMBLY_ITEM_NAME');
875
876 IF lv_return <> 0 THEN
877 RAISE ex_logging_err;
878 END IF;
879
880 --Derive Inventory_item_id
881 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
882 (p_table_name => 'MSC_ST_DEMANDS',
883 p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
884 p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
885 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
886 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
887 p_error_text => lv_error_text,
888 p_batch_id => lv_batch_id,
889 p_severity => MSC_CL_PRE_PROCESS.G_SEV3_ERROR,
890 p_message_text => lv_message_text,
891 p_debug => MSC_CL_PRE_PROCESS.v_debug,
892 p_row => lv_column_names);
893
894 IF lv_return <> 0 THEN
895 RAISE ex_logging_err;
896 END IF;
897
898 --- error out records where USING_REQUIREMENT_QUANTITY is NULL and using_assembly_demand_date is NULL
899
900 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
901 (p_app_short_name => 'MSC',
902 p_error_code => 'MSC_PP_COL_VAL_NULL',
903 p_message_text => lv_message_text,
904 p_error_text => lv_error_text,
905 p_token1 => 'COLUMN_NAME',
906 p_token_value1 => 'USING_REQUIREMENT_QUANTITY' || ' OR USING_ASSEMBLY_DEMAND_DATE');
907
908 IF lv_return <> 0 THEN
909 RAISE ex_logging_err;
910 END IF;
911
912 MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
913 lv_sql_stmt :=
914 'UPDATE msc_st_demands '
915 ||' SET error_text = '||''''||lv_message_text||''''||','
916 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
917 ||' WHERE (using_requirement_quantity IS NULL ' ||' OR using_assembly_demand_date IS NULL)'
918 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
919 ||' AND origination_type IN (77)'
920 ||' AND ENTITY = ''IRO'''
921 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
922 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
923 ||' AND sr_instance_code = :v_instance_code';
924
925 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
926 msc_st_util.log_message(lv_sql_stmt);
927 END IF;
928
929 EXECUTE IMMEDIATE lv_sql_stmt
930 USING lv_batch_id,
931 MSC_CL_PRE_PROCESS.v_instance_code;
932
933 --- error out record if repair_number is null:
934 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
935 (p_app_short_name => 'MSC',
936 p_error_code => 'MSC_PP_COL_VAL_NULL',
937 p_message_text => lv_message_text,
938 p_error_text => lv_error_text,
939 p_token1 => 'COLUMN_NAME',
940 p_token_value1 => 'REPAIR_NUMBER');
941
942 IF lv_return <> 0 THEN
943 RAISE ex_logging_err;
944 END IF;
945
946 MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
947 lv_sql_stmt :=
948 'UPDATE msc_st_demands '
949 ||' SET error_text = '||''''||lv_message_text||''''||','
950 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
951 ||' WHERE repair_number is NULL'
952 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
953 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
954 ||' AND sr_instance_code = :v_instance_code';
955
956 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
957 msc_st_util.log_message(lv_sql_stmt);
958 END IF;
959
960 EXECUTE IMMEDIATE lv_sql_stmt
961 USING lv_batch_id,
962 MSC_CL_PRE_PROCESS.v_instance_code;
963
964 --- derive repair line id
965
966 MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
967 lv_sql_stmt :=
968 'UPDATE msc_st_demands msd'
969 ||' SET repair_line_id = (SELECT local_id'
970 ||' FROM msc_local_id_supply mls'
971 ||' WHERE mls.char4 = msd.wip_entity_name'
972 ||' AND mls.char3 = msd.organization_code'
973 ||' AND mls.char1 = msd.sr_instance_code'
974 ||' AND mls.entity_name = ''REPAIR_NUMBER'' )'
975 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
976 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
977 ||' AND sr_instance_code = :v_instance_code';
978
979 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
980 msc_st_util.log_message(lv_sql_stmt);
981 END IF;
982
983 EXECUTE IMMEDIATE lv_sql_stmt
984 USING lv_batch_id,
985 MSC_CL_PRE_PROCESS.v_instance_code;
986
987
988 ---error out the record where repair line is null and deleted flag is SYS_YES
989
990 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
991 (p_app_short_name => 'MSC',
992 p_error_code => 'MSC_PP_DELETE_FAIL',
993 p_message_text => lv_message_text,
994 p_error_text => lv_error_text);
995
996 IF lv_return <> 0 THEN
997 RAISE ex_logging_err;
998 END IF;
999 MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
1000
1001 lv_sql_stmt :=
1002 'UPDATE msc_st_demands '
1003 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1004 ||' error_text = '||''''||lv_message_text||''''
1005 ||' WHERE repair_line_id is null '
1006 ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
1007 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1008 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1009 ||' AND sr_instance_code =:v_instance_code';
1010
1011 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1012 msc_st_util.log_message(lv_sql_stmt);
1013 END IF;
1014
1015 EXECUTE IMMEDIATE lv_sql_stmt
1016 USING lv_batch_id,
1017 MSC_CL_PRE_PROCESS.v_instance_code;
1018 --- error out record if ITEM_TYPE_VALUE is null:
1019 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1020 (p_app_short_name => 'MSC',
1021 p_error_code => 'MSC_PP_INVALID_VALUE',
1022 p_message_text => lv_message_text,
1023 p_error_text => lv_error_text,
1024 p_token1 => 'COLUMN_NAME',
1025 p_token_value1 => 'ITEM_TYPE_VALUE');
1026
1027 IF lv_return <> 0 THEN
1028 RAISE ex_logging_err;
1029 END IF;
1030
1031 MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
1032 lv_sql_stmt :=
1033 'UPDATE msc_st_demands '
1034 ||' SET error_text = '||''''||lv_message_text||''''||','
1035 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1036 ||' WHERE nvl(ITEM_TYPE_VALUE,-1) not in (1,2)'
1037 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1038 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1039 ||' AND sr_instance_code = :v_instance_code';
1040
1041 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1042 msc_st_util.log_message(lv_sql_stmt);
1043 END IF;
1044
1045 EXECUTE IMMEDIATE lv_sql_stmt
1046 USING lv_batch_id,
1047 MSC_CL_PRE_PROCESS.v_instance_code;
1048
1049 ---- validating project and task :
1050 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
1051 (p_table_name => 'MSC_ST_DEMANDS',
1052 p_proj_col_name => 'PROJECT_NUMBER',
1053 p_proj_task_col_id => 'PROJECT_ID',
1054 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1055 p_entity_name => 'PROJECT_ID',
1056 p_error_text => lv_error_text,
1057 p_batch_id => lv_batch_id,
1058 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1059 p_message_text => lv_message_text,
1060 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1061 p_row => lv_column_names);
1062 IF lv_return <> 0 THEN
1063 RAISE ex_logging_err;
1064 END IF;
1065
1066 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1067 (p_app_short_name => 'MSC',
1068 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
1069 p_message_text => lv_message_text,
1070 p_error_text => lv_error_text,
1071 p_token1 => 'COLUMN_NAMES',
1072 p_token_value1 => ' SR_INSTANCE_CODE,'
1073 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
1074 ||' TASK_NUMBER',
1075 p_token2 => 'MASTER_TABLE',
1076 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
1077
1078 IF lv_return <> 0 THEN
1079 RAISE ex_logging_err;
1080 END IF;
1081
1082 --Derive Task Id.
1083 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
1084 (p_table_name => 'MSC_ST_DEMANDS',
1085 p_proj_col_name => 'PROJECT_NUMBER',
1086 p_proj_task_col_id => 'TASK_ID',
1087 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1088 p_entity_name => 'TASK_ID',
1089 p_error_text => lv_error_text,
1090 p_task_col_name => 'TASK_NUMBER',
1091 p_batch_id => lv_batch_id,
1092 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1093 p_message_text => lv_message_text,
1094 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1095 p_row => lv_column_names);
1096
1097 IF lv_return <> 0 THEN
1098 RAISE ex_logging_err;
1099 END IF;
1100
1101 --Call to customised validation.
1102 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
1103 (ERRBUF => lv_error_text,
1104 RETCODE => lv_return,
1105 pBatchID => lv_batch_id,
1106 pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
1107 pEntityName => 'MSC_ST_DEMANDS',
1108 pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
1109
1110 IF NVL(lv_return,0) <> 0 THEN
1111 RAISE ex_logging_err;
1112 END IF;
1113
1114 lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
1115 (p_table_name => 'MSC_ST_DEMANDS',
1116 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
1117 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1118 p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
1119 p_error_text => lv_error_text,
1120 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1121 p_batch_id => lv_batch_id);
1122
1123 IF lv_return <> 0 THEN
1124 RAISE ex_logging_err;
1125 END IF;
1126
1127 lv_return := MSC_ST_UTIL.LOG_ERROR
1128 (p_table_name => 'MSC_ST_DEMANDS',
1129 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1130 p_row => lv_column_names,
1131 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1132 p_message_text => NULL,
1133 p_error_text => lv_error_text,
1134 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1135 p_batch_id => lv_batch_id);
1136
1137 IF lv_return <> 0 THEN
1138 RAISE ex_logging_err;
1139 END IF;
1140 COMMIT;
1141 END LOOP;
1142
1143 EXCEPTION
1144
1145 WHEN too_many_rows THEN
1146 lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_DEMANDS'||'('
1147 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
1148 ROLLBACK ;
1149
1150 WHEN ex_logging_err THEN
1151 msc_st_util.log_message(lv_error_text);
1152 ROLLBACK;
1153
1154 WHEN OTHERS THEN
1155 lv_error_text := substr('MSC_CL_PRE_PROCESS.MSC_ST_DEMANDS '||'('
1156 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
1157 msc_st_util.log_message(lv_error_text);
1158 ROLLBACK;
1159
1160 END LOAD_IRO_DEMAND;
1161
1162 PROCEDURE LOAD_ERO_SUPPLY IS
1163 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1164 lb_rowid RowidTab;
1165 lv_return NUMBER;
1166 lv_error_text VARCHAR2(250);
1167 lv_where_str VARCHAR2(5000);
1168 lv_sql_stmt VARCHAR2(5000);
1169 lv_column_names VARCHAR2(5000); --stores concatenated column names
1170 lv_message_text msc_errors.error_text%TYPE;
1171 lv_batch_id msc_st_supplies.batch_id%TYPE;
1172 ex_logging_err EXCEPTION;
1173
1174 CURSOR c1(p_batch_id NUMBER) IS
1175 SELECT rowid
1176 FROM msc_st_supplies
1177 WHERE order_type =86
1178 AND process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
1179 AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE)=p_batch_id
1180 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1181
1182 CURSOR c2(p_batch_id NUMBER) IS
1183 SELECT rowid
1184 FROM msc_st_supplies
1185 WHERE NVL(wip_entity_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1186 AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1187 AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) =p_batch_id
1188 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
1189
1190 CURSOR c3(p_batch_id NUMBER) IS
1191 SELECT max(rowid)
1192 FROM msc_st_supplies
1193 WHERE NVL(schedule_group_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1194 AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
1195 AND process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1196 AND NVL(batch_id,MSC_CL_PRE_PROCESS.NULL_VALUE) = p_batch_id
1197 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
1198 GROUP BY sr_instance_code,company_name,organization_code,schedule_group_name;
1199
1200 CURSOR c4(p_batch_id NUMBER) IS
1201 SELECT rowid
1202 FROM msc_st_supplies
1203 WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
1204 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
1205 AND batch_id = p_batch_id
1206 AND NVL(JOB_OP_SEQ_NUM, MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
1207 AND NVL(JOB_OP_SEQ_CODE, MSC_CL_PRE_PROCESS.NULL_CHAR) <> MSC_CL_PRE_PROCESS.NULL_CHAR
1208 AND order_type = 86
1209 AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO;
1210
1211 BEGIN
1212
1213 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1214 (p_app_short_name => 'MSC',
1215 p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
1216 p_message_text => lv_message_text,
1217 p_error_text => lv_error_text);
1218
1219 IF lv_return <> 0 THEN
1220 RAISE ex_logging_err;
1221 END IF;
1222
1223 --Duplicate records check for the records whose source is XML for
1224 --WO supplies
1225 MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
1226 lv_sql_stmt :=
1227 'UPDATE msc_st_supplies mss1'
1228 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1229 ||' error_text = '||''''||lv_message_text||''''
1230 ||' WHERE message_id < (SELECT MAX(message_id)'
1231 ||' FROM msc_st_supplies mss2'
1232 ||' WHERE mss2.sr_instance_code'
1233 ||' = mss1.sr_instance_code'
1234 ||' AND NVL(mss2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1235 ||' NVL(mss1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
1236 ||' AND mss2.wip_entity_name = mss1.wip_entity_name '
1237 ||' AND mss2.order_type = mss1.order_type'
1238 ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1239 ||' AND mss2.organization_code = mss1.organization_code'
1240 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<>'||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
1241 ||' AND mss1.order_type =86'
1242 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1243 ||' AND mss1.sr_instance_code = :v_instance_code'
1244 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') <> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
1245
1246 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1247 msc_st_util.log_message(lv_sql_stmt);
1248 END IF;
1249
1250 EXECUTE IMMEDIATE lv_sql_stmt
1251 USING MSC_CL_PRE_PROCESS.v_instance_code;
1252
1253
1254 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1255 (p_app_short_name => 'MSC',
1256 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
1257 p_message_text => lv_message_text,
1258 p_error_text => lv_error_text);
1259
1260 IF lv_return <> 0 THEN
1261 RAISE ex_logging_err;
1262 END IF;
1263
1264 --Duplicate records check for the records whose source is other than XML
1265 --Different SQL is used because in XML we can identify the latest records
1266 --whereas in batch load we cannot.
1267 MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
1268 lv_sql_stmt :=
1269 'UPDATE msc_st_supplies mss1 '
1270 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1271 ||' error_text = '||''''||lv_message_text||''''
1272 ||' WHERE EXISTS( SELECT 1 '
1273 ||' FROM msc_st_supplies mss2'
1274 ||' WHERE mss2.sr_instance_code'
1275 ||' = mss1.sr_instance_code'
1276 ||' AND NVL(mss2.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')= '
1277 ||' NVL(mss1.company_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
1278 ||' AND mss2.wip_entity_name = mss1.wip_entity_name '
1279 ||' AND mss2.order_type = mss1.order_type'
1280 ||' AND mss2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1281 ||' AND mss2.organization_code = mss1.organization_code'
1282 ||' AND NVL(mss2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1283 ||' GROUP BY sr_instance_code,wip_entity_name,organization_code,company_name,'
1284 ||' order_type'
1285 ||' HAVING COUNT(*) > 1)'
1286 ||' AND mss1.order_type =86'
1287 ||' AND mss1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1288 ||' AND mss1.sr_instance_code = :v_instance_code'
1289 ||' AND NVL(mss1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
1290
1291 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1292 msc_st_util.log_message(lv_sql_stmt);
1293 END IF;
1294
1295 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
1296
1297 lv_column_names :=
1298 'ITEM_NAME ||''~''||'
1299 ||'ORGANIZATION_CODE ||''~''||'
1300 ||'NEW_SCHEDULE_DATE ||''~''||'
1301 ||'FIRM_PLANNED_TYPE ||''~''||'
1302 ||'WIP_ENTITY_NAME ||''~''||'
1303 ||'SR_INSTANCE_CODE ||''~''||'
1304 ||'REVISION ||''~''||'
1305 ||'UNIT_NUMBER ||''~''||'
1306 ||'NEW_WIP_START_DATE ||''~''||'
1307 ||'NEW_ORDER_QUANTITY ||''~''||'
1308 ||'ALTERNATE_BOM_DESIGNATOR ||''~''||'
1309 ||'ALTERNATE_ROUTING_DESIGNATOR ||''~''||'
1310 ||'LINE_CODE ||''~''||'
1311 ||'PROJECT_NUMBER ||''~''||'
1312 ||'TASK_NUMBER ||''~''||'
1313 ||'PLANNING_GROUP ||''~''||'
1314 ||'SCHEDULE_GROUP_NAME ||''~''||'
1315 ||'BUILD_SEQUENCE ||''~''||'
1316 ||'WO_LATENESS_COST ||''~''||'
1317 ||'IMPLEMENT_PROCESSING_DAYS ||''~''||'
1318 ||'LATE_SUPPLY_DATE ||''~''||'
1319 ||'LATE_SUPPLY_QTY ||''~''||'
1320 ||'QTY_SCRAPPED ||''~''||'
1321 ||'QTY_COMPLETED ||''~''||'
1322 ||'WIP_STATUS_CODE ||''~''||'
1323 ||'BILL_NAME ||''~''||'
1324 ||'ROUTING_NAME ||''~''||'
1325 ||'DELETED_FLAG ||''~''||'
1326 ||'COMPANY_NAME ||''~''||'
1327 ||'ORDER_TYPE ||''~''||'
1328 ||'ORDER_NUMBER';
1329
1330 LOOP
1331 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1332 SELECT msc_st_batch_id_s.NEXTVAL
1333 INTO lv_batch_id
1334 FROM dual;
1335
1336 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1337 lv_sql_stmt :=
1338 ' UPDATE msc_st_supplies '
1339 ||' SET batch_id = :lv_batch_id'
1340 ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
1341 ||' AND order_type =86'
1342 ||' AND sr_instance_code = :v_instance_code'
1343 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1344 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
1345
1346 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1347 msc_st_util.log_message(lv_sql_stmt);
1348 END IF;
1349
1350 EXECUTE IMMEDIATE lv_sql_stmt
1351 USING lv_batch_id,
1352 MSC_CL_PRE_PROCESS.v_instance_code;
1353
1354 EXIT WHEN SQL%NOTFOUND;
1355
1356 OPEN c1(lv_batch_id);
1357 FETCH c1 BULK COLLECT INTO lb_rowid;
1358 CLOSE c1;
1359
1360 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
1361 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1362 UPDATE msc_st_supplies
1363 SET st_transaction_id = msc_st_supplies_s.NEXTVAL,
1364 refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
1365 last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
1366 last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
1367 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
1368 created_by = MSC_CL_PRE_PROCESS.v_current_user
1369 WHERE rowid = lb_rowid(j);
1370
1371 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1372 (p_app_short_name => 'MSC',
1373 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1374 p_message_text => lv_message_text,
1375 p_error_text => lv_error_text,
1376 p_token1 => 'COLUMN_NAME',
1377 p_token_value1 => 'DELETED_FLAG',
1378 p_token2 => 'DEFAULT_VALUE',
1379 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1380
1381 IF lv_return <> 0 THEN
1382 RAISE ex_logging_err;
1383 END IF;
1384
1385 lv_where_str :=
1386 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
1387 --Log a warning for those records where the deleted_flag has a value other
1388 --SYS_NO
1389 lv_return := MSC_ST_UTIL.LOG_ERROR
1390 (p_table_name => 'MSC_ST_SUPPLIES',
1391 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1392 p_row => lv_column_names,
1393 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1394 p_message_text => lv_message_text,
1395 p_error_text => lv_error_text,
1396 p_batch_id => lv_batch_id,
1397 p_where_str => lv_where_str,
1398 p_col_name => 'DELETED_FLAG',
1399 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1400 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
1401
1402 IF lv_return <> 0 THEN
1403 RAISE ex_logging_err;
1404 END IF;
1405
1406 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1407 (p_app_short_name => 'MSC',
1408 p_error_code => 'MSC_PP_INVALID_VALUE',
1409 p_message_text => lv_message_text,
1410 p_error_text => lv_error_text,
1411 p_token1 => 'COLUMN_NAME',
1412 p_token_value1 => 'ORGANIZATION_CODE');
1413
1414 IF lv_return <> 0 THEN
1415 RAISE ex_logging_err;
1416 END IF;
1417
1418 --Derive Organization_id
1419 lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
1420 (p_table_name => 'MSC_ST_SUPPLIES',
1421 p_org_partner_name => 'ORGANIZATION_CODE',
1422 p_org_partner_id => 'ORGANIZATION_ID',
1423 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1424 p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,
1425 p_error_text => lv_error_text,
1426 p_batch_id => lv_batch_id,
1427 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1428 p_message_text => lv_message_text,
1429 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1430 p_row => lv_column_names);
1431
1432 IF lv_return <> 0 THEN
1433 RAISE ex_logging_err;
1434 END IF;
1435
1436 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1437 (p_app_short_name => 'MSC',
1438 p_error_code => 'MSC_PP_INVALID_VALUE',
1439 p_message_text => lv_message_text,
1440 p_error_text => lv_error_text,
1441 p_token1 => 'COLUMN_NAME',
1442 p_token_value1 => 'ITEM_NAME');
1443
1444 IF lv_return <> 0 THEN
1445 RAISE ex_logging_err;
1446 END IF;
1447
1448 --Derive Inventory_item_id
1449 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
1450 (p_table_name => 'MSC_ST_SUPPLIES',
1451 p_item_col_name => 'ITEM_NAME',
1452 p_item_col_id => 'INVENTORY_ITEM_ID',
1453 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
1454 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1455 p_error_text => lv_error_text,
1456 p_batch_id => lv_batch_id,
1457 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
1458 p_message_text => lv_message_text,
1459 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1460 p_row => lv_column_names);
1461
1462 IF lv_return <> 0 THEN
1463 RAISE ex_logging_err;
1464 END IF;
1465
1466 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1467 (p_app_short_name => 'MSC',
1468 p_error_code => 'MSC_PP_COL_VAL_NULL',
1469 p_message_text => lv_message_text,
1470 p_error_text => lv_error_text,
1471 p_token1 => 'COLUMN_NAME',
1472 p_token_value1 => 'NEW_SCHEDULE_DATE OR NEW_ORDER_QUANTITY');
1473
1474 IF lv_return <> 0 THEN
1475 RAISE ex_logging_err;
1476 END IF;
1477
1478 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
1479 lv_sql_stmt :=
1480 'UPDATE msc_st_supplies '
1481 ||' SET error_text = '||''''||lv_message_text||''''||','
1482 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1483 ||' WHERE (NVL(new_schedule_date,sysdate-36500) = sysdate-36500'
1484 ||' OR NVL(new_order_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE|| ')= '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
1485 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
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_COL_VAL_NULL',
1501 p_message_text => lv_message_text,
1502 p_error_text => lv_error_text,
1503 p_token1 => 'COLUMN_NAME',
1504 p_token_value1 => 'WIP_ENTITY_NAME');
1505
1506 IF lv_return <> 0 THEN
1507 RAISE ex_logging_err;
1508 END IF;
1509
1510 MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
1511 lv_sql_stmt :=
1512 'UPDATE msc_st_supplies '
1513 ||' SET error_text = '||''''||lv_message_text||''''||','
1514 ||' process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG
1515 ||' WHERE NVL(wip_entity_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1516 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1517 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1518 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1519 ||' AND sr_instance_code = :v_instance_code';
1520
1521 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1522 msc_st_util.log_message(lv_sql_stmt);
1523 END IF;
1524
1525 EXECUTE IMMEDIATE lv_sql_stmt
1526 USING lv_batch_id,
1527 MSC_CL_PRE_PROCESS.v_instance_code;
1528
1529 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1530 (p_app_short_name => 'MSC',
1531 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1532 p_message_text => lv_message_text,
1533 p_error_text => lv_error_text,
1534 p_token1 => 'COLUMN_NAME',
1535 p_token_value1 => 'FIRM_PLANNED_TYPE',
1536 p_token2 => 'DEFAULT_VALUE',
1537 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1538
1539 IF lv_return <> 0 THEN
1540 RAISE ex_logging_err;
1541 END IF;
1542
1543 lv_where_str :=
1544 ' AND NVL(firm_planned_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)'
1545 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1546
1547 --Log a warning for those records where the firm_planned_type has a value
1548 --other than 1 and 2
1549
1550 lv_return := MSC_ST_UTIL.LOG_ERROR
1551 (p_table_name => 'MSC_ST_SUPPLIES',
1552 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1553 p_row => lv_column_names,
1554 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1555 p_message_text => lv_message_text,
1556 p_error_text => lv_error_text,
1557 p_batch_id => lv_batch_id,
1558 p_where_str => lv_where_str,
1559 p_col_name => 'FIRM_PLANNED_TYPE',
1560 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1561 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
1562
1563 IF lv_return <> 0 THEN
1564 RAISE ex_logging_err;
1565 END IF;
1566
1567 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1568 (p_app_short_name => 'MSC',
1569 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1570 p_message_text => lv_message_text,
1571 p_error_text => lv_error_text,
1572 p_token1 => 'COLUMN_NAME',
1573 p_token_value1 => 'WIP_STATUS_CODE',
1574 p_token2 => 'DEFAULT_VALUE',
1575 p_token_value2 => 1);
1576
1577 IF lv_return <> 0 THEN
1578 RAISE ex_logging_err;
1579 END IF;
1580
1581 lv_where_str := ' AND wip_status_code <= 1'
1582 ||' AND wip_status_code >= 15'
1583 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1584
1585 --Log a warning for those records where the wip_status_code has a value other
1586 --than SYS_NO
1587
1588 lv_return := MSC_ST_UTIL.LOG_ERROR
1589 (p_table_name => 'MSC_ST_SUPPLIES',
1590 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1591 p_row => lv_column_names,
1592 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1593 p_message_text => lv_message_text,
1594 p_error_text => lv_error_text,
1595 p_batch_id => lv_batch_id,
1596 p_where_str => lv_where_str,
1597 p_col_name => 'WIP_STATUS_CODE',
1598 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1599 p_default_value => 1);
1600
1601 IF lv_return <> 0 THEN
1602 RAISE ex_logging_err;
1603 END IF;
1604
1605 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1606 (p_app_short_name => 'MSC',
1607 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1608 p_message_text => lv_message_text,
1609 p_error_text => lv_error_text,
1610 p_token1 => 'COLUMN_NAME',
1611 p_token_value1 => 'DISPOSITION_STATUS_TYPE',
1612 p_token2 => 'DEFAULT_VALUE',
1613 p_token_value2 => 1);
1614
1615 IF lv_return <> 0 THEN
1616 RAISE ex_logging_err;
1617 END IF;
1618
1619 lv_where_str :=
1620 ' AND NVL(disposition_status_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)'
1621 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1622
1623 --Log a warning for those records where the firm_planned_type has a value other
1624 --than SYS_NO
1625
1626 lv_return := MSC_ST_UTIL.LOG_ERROR
1627 (p_table_name => 'MSC_ST_SUPPLIES',
1628 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1629 p_row => lv_column_names,
1630 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1631 p_message_text => lv_message_text,
1632 p_error_text => lv_error_text,
1633 p_batch_id => lv_batch_id,
1634 p_where_str => lv_where_str,
1635 p_col_name => 'DISPOSITION_STATUS_TYPE',
1636 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1637 p_default_value => 1);
1638
1639 IF lv_return <> 0 THEN
1640 RAISE ex_logging_err;
1641 END IF;
1642
1643 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1644 (p_app_short_name => 'MSC',
1645 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1646 p_message_text => lv_message_text,
1647 p_error_text => lv_error_text,
1648 p_token1 => 'COLUMN_NAME',
1649 p_token_value1 => 'WIP_SUPPLY_TYPE',
1650 p_token2 => 'DEFAULT_VALUE',
1651 p_token_value2 => 1);
1652
1653 IF lv_return <> 0 THEN
1654 RAISE ex_logging_err;
1655 END IF;
1656
1657 lv_where_str := ' AND wip_supply_type <= 1'
1658 ||' AND wip_supply_type >= 7'
1659 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1660
1661 --Log a warning for those records where the wip_supply_type has a value other
1662 --than SYS_NO
1663
1664 lv_return := MSC_ST_UTIL.LOG_ERROR
1665 (p_table_name => 'MSC_ST_SUPPLIES',
1666 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1667 p_row => lv_column_names,
1668 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1669 p_message_text => lv_message_text,
1670 p_error_text => lv_error_text,
1671 p_batch_id => lv_batch_id,
1672 p_where_str => lv_where_str,
1673 p_col_name => 'WIP_SUPPLY_TYPE',
1674 p_debug =>MSC_CL_PRE_PROCESS.v_debug,
1675 p_default_value => 1);
1676
1677 IF lv_return <> 0 THEN
1678 RAISE ex_logging_err;
1679 END IF;
1680
1681 MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
1682 lv_sql_stmt :=
1683 'UPDATE msc_st_supplies'
1684 ||' SET order_number = wip_entity_name'
1685 ||' WHERE NVL(order_number,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
1686 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1687 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1688 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1689 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = :lv_batch_id'
1690 ||' AND sr_instance_code = :v_instance_code';
1691
1692 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1693 msc_st_util.log_message(lv_sql_stmt);
1694 END IF;
1695
1696 EXECUTE IMMEDIATE lv_sql_stmt
1697 USING lv_batch_id,
1698 MSC_CL_PRE_PROCESS.v_instance_code;
1699
1700 -- Now we will check whether BOM Name is NULL , if it is NULL we will populate the
1701 -- ASSEMBLY NAME in BOM NAME column for all such records
1702
1703 MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
1704 lv_sql_stmt :=
1705 'UPDATE msc_st_supplies '
1706 ||' SET bill_name = item_name'
1707 ||' WHERE sr_instance_code = :v_instance_code'
1708 ||' AND order_type =86'
1709 ||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1710 ||' AND NVL(bill_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1711 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1712 ||' AND NVL(item_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1713 ||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1714 ||' AND batch_id = :lv_batch_id';
1715
1716 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1717 msc_st_util.log_message(lv_sql_stmt);
1718 END IF;
1719
1720 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
1721
1722 -- Now check whether Routing Name is NULL , if it is NULL we will populate
1723 -- Assembly Name in Routing Name column for all such records
1724
1725 MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
1726
1727 lv_sql_stmt :=
1728 'UPDATE msc_st_supplies '
1729 ||' SET routing_name = item_name'
1730 ||' WHERE sr_instance_code = :v_instance_code'
1731 ||' AND order_type =86'
1732 ||' AND process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
1733 ||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1734 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1735 ||' AND NVL(item_name, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1736 ||' <> '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
1737 ||' AND batch_id = :lv_batch_id ';
1738
1739 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1740 msc_st_util.log_message(lv_sql_stmt);
1741 END IF;
1742
1743 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
1744
1745 lv_return := MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID
1746 (p_table_name => 'MSC_ST_SUPPLIES',
1747 p_bom_col_name => 'BILL_NAME',
1748 p_bom_col_id => 'BILL_SEQUENCE_ID',
1749 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1750 p_batch_id => lv_batch_id,
1751 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1752 p_error_text => lv_error_text);
1753
1754
1755 IF (lv_return <> 0 ) THEN
1756 RAISE ex_logging_err;
1757 END IF;
1758
1759 lv_return := MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID
1760 (p_table_name => 'MSC_ST_SUPPLIES',
1761 p_rtg_col_name => 'ROUTING_NAME',
1762 p_rtg_col_id => 'ROUTING_SEQUENCE_ID',
1763 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1764 p_batch_id => lv_batch_id,
1765 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1766 p_error_text => lv_error_text);
1767
1768 if (lv_return <> 0 )then
1769 RAISE ex_logging_err;
1770 end if;
1771
1772
1773
1774
1775 MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
1776 lv_sql_stmt :=
1777 'UPDATE msc_st_supplies mss'
1778 ||' SET schedule_group_id = (SELECT local_id'
1779 ||' FROM msc_local_id_supply mls'
1780 ||' WHERE mls.char4 = mss.schedule_group_name'
1781 ||' AND mls.char3 = mss.organization_code'
1782 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1783 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1784 ||' AND mls.char1 = mss.sr_instance_code'
1785 ||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' ),'
1786 ||' line_id = (SELECT local_id'
1787 ||' FROM msc_local_id_setup mls'
1788 ||' WHERE mls.char4 = mss.line_code'
1789 ||' AND mls.char3 = mss.organization_code'
1790 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1791 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1792 ||' AND mls.char1 = mss.sr_instance_code'
1793 ||' AND mls.entity_name = ''LINE_ID''),'
1794 ||' operation_seq_num = (SELECT number1'
1795 ||' FROM msc_local_id_setup mls'
1796 ||' WHERE mls.char5 = mss.operation_seq_code'
1797 ||' AND mls.char4 = mss.routing_name'
1798 ||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1799 ||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1800 ||' AND mls.char3 = mss.organization_code'
1801 ||' AND mls.date1 = mss.effectivity_date'
1802 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1803 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1804 ||' AND mls.char1 = mss.sr_instance_code'
1805 ||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
1806 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1807 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1808 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1809 ||' AND sr_instance_code = :v_instance_code';
1810
1811 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1812 msc_st_util.log_message(lv_sql_stmt);
1813 END IF;
1814
1815 EXECUTE IMMEDIATE lv_sql_stmt
1816 USING lv_batch_id,
1817 MSC_CL_PRE_PROCESS.v_instance_code;
1818
1819
1820 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1821 (p_app_short_name => 'MSC',
1822 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
1823 p_message_text => lv_message_text,
1824 p_error_text => lv_error_text,
1825 p_token1 => 'COLUMN_NAME',
1826 p_token_value1 => 'BILL_SEQUENCE_ID OR ROUTING_SEQUENCE_ID',
1827 p_token2 => 'DEFAULT_VALUE',
1828 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO);
1829
1830 IF lv_return <> 0 THEN
1831 RAISE ex_logging_err;
1832 END IF;
1833
1834 lv_where_str :=
1835 ' AND (NVL(bill_sequence_id, '||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1836 ||' OR NVL(routing_sequence_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
1837 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO;
1838
1839 --Log a warning for those records where the bill_sequence_id or
1840 --routing_sequence_id has null values
1841
1842 lv_return := MSC_ST_UTIL.LOG_ERROR
1843 (p_table_name => 'MSC_ST_SUPPLIES',
1844 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
1845 p_row => lv_column_names,
1846 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
1847 p_message_text => lv_message_text,
1848 p_error_text => lv_error_text,
1849 p_batch_id => lv_batch_id,
1850 p_debug => MSC_CL_PRE_PROCESS.v_debug,
1851 p_where_str => lv_where_str);
1852
1853 IF lv_return <> 0 THEN
1854 RAISE ex_logging_err;
1855 END IF;
1856
1857 --Deriving wip_entity_id
1858 MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
1859 lv_sql_stmt :=
1860 'UPDATE msc_st_supplies mss'
1861 ||' SET wip_entity_id = (SELECT local_id'
1862 ||' FROM msc_local_id_supply mls'
1863 ||' WHERE mls.char4 = mss.wip_entity_name'
1864 ||' AND mls.char3 = mss.organization_code'
1865 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1866 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1867 ||' AND mls.char1 = mss.sr_instance_code'
1868 ||' AND mls.entity_name = ''WIP_ENTITY_ID'' )'
1869 ||' WHERE 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 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
1883 (p_app_short_name => 'MSC',
1884 p_error_code => 'MSC_PP_DELETE_FAIL',
1885 p_message_text => lv_message_text,
1886 p_error_text => lv_error_text);
1887
1888 IF lv_return <> 0 THEN
1889 RAISE ex_logging_err;
1890 END IF;
1891
1892 MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
1893
1894 lv_sql_stmt :=
1895 'UPDATE msc_st_supplies '
1896 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
1897 ||' error_text = '||''''||lv_message_text||''''
1898 ||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
1899 ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
1900 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1901 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1902 ||' AND sr_instance_code =:v_instance_code';
1903
1904 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1905 msc_st_util.log_message(lv_sql_stmt);
1906 END IF;
1907
1908 EXECUTE IMMEDIATE lv_sql_stmt
1909 USING lv_batch_id,
1910 MSC_CL_PRE_PROCESS.v_instance_code;
1911
1912
1913 -- update the jump_op_seq_num for lot based jobs for the operations jumped outside the network
1914
1915 lv_sql_stmt :=
1916 'UPDATE msc_st_supplies '
1917 ||' SET jump_op_seq_num = 50000'
1918 ||' WHERE NVL(jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1919 ||' = '||''''||50000||''''
1920 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1921 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id '
1922 ||' AND order_type =86'
1923 ||' AND sr_instance_code =:v_instance_code';
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 lv_sql_stmt :=
1935 'UPDATE msc_st_supplies mss'
1936 ||' SET jump_op_seq_num = (SELECT number1'
1937 ||' FROM msc_local_id_setup mls'
1938 ||' WHERE NVL(mls.char5,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1939 ||' NVL(mss.jump_op_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1940 ||' AND mls.char4 = mss.routing_name'
1941 ||' AND NVL(mls.char6, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1942 ||' NVL(mss.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1943 ||' AND mls.char3 = mss.organization_code'
1944 ||' AND mls.date1 = mss.jump_op_effectivity_date'
1945 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
1946 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
1947 ||' AND mls.char1 = mss.sr_instance_code'
1948 ||' AND mls.entity_name = ''OPERATION_SEQUENCE_ID'' )'
1949 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
1950 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
1951 ||' AND jump_op_seq_num <> 50000 '
1952 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
1953 ||' AND sr_instance_code = :v_instance_code';
1954
1955
1956 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
1957 msc_st_util.log_message(lv_sql_stmt);
1958 END IF;
1959
1960 EXECUTE IMMEDIATE lv_sql_stmt
1961 USING lv_batch_id,
1962 MSC_CL_PRE_PROCESS.v_instance_code;
1963
1964
1965 --Call to customised validation.
1966 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
1967 (ERRBUF => lv_error_text,
1968 RETCODE => lv_return,
1969 pBatchID => lv_batch_id,
1970 pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
1971 pEntityName => 'MSC_ST_SUPPLIES_ERO',
1972 pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
1973
1974 IF NVL(lv_return,0) <> 0 THEN
1975 RAISE ex_logging_err;
1976 END IF;
1977
1978 --Generation of wip_entity_id
1979 OPEN c2(lv_batch_id);
1980 FETCH c2 BULK COLLECT INTO lb_rowid ;
1981
1982 IF c2%ROWCOUNT > 0 THEN
1983 MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
1984 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1985 UPDATE msc_st_supplies
1986 SET wip_entity_id = msc_st_wip_entity_id_s.NEXTVAL
1987 WHERE rowid = lb_rowid(j);
1988
1989 MSC_CL_PRE_PROCESS.v_sql_stmt := 13;
1990 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
1991 INSERT INTO msc_local_id_supply
1992 (local_id,
1993 st_transaction_id,
1994 instance_id,
1995 entity_name,
1996 data_source_type,
1997 char1,
1998 char2,
1999 char3,
2000 char4,
2001 SOURCE_ORG_ID,
2002 SOURCE_INVENTORY_ITEM_ID,
2003 SOURCE_BILL_SEQUENCE_ID,
2004 SOURCE_ROUTING_SEQUENCE_ID,
2005 SOURCE_SCHEDULE_GROUP_ID,
2006 SOURCE_WIP_ENTITY_ID,
2007 last_update_date,
2008 last_updated_by,
2009 creation_date,
2010 created_by)
2011 SELECT
2012 wip_entity_id,
2013 st_transaction_id,
2014 MSC_CL_PRE_PROCESS.v_instance_id,
2015 'WIP_ENTITY_ID',
2016 data_source_type,
2017 MSC_CL_PRE_PROCESS.v_instance_code,
2018 company_name,
2019 organization_code ,
2020 wip_entity_name,
2021 SOURCE_ORG_ID,
2022 SOURCE_INVENTORY_ITEM_ID,
2023 SOURCE_BILL_SEQUENCE_ID,
2024 SOURCE_ROUTING_SEQUENCE_ID,
2025 SOURCE_SCHEDULE_GROUP_ID,
2026 SOURCE_WIP_ENTITY_ID,
2027 MSC_CL_PRE_PROCESS.v_current_date,
2028 MSC_CL_PRE_PROCESS.v_current_user,
2029 MSC_CL_PRE_PROCESS.v_current_date,
2030 MSC_CL_PRE_PROCESS.v_current_user
2031 FROM msc_st_supplies
2032 WHERE rowid = lb_rowid(j);
2033
2034 END IF;
2035 CLOSE c2 ;
2036
2037 --Generation of schedule_group_id
2038 OPEN c3(lv_batch_id);
2039 FETCH c3 BULK COLLECT INTO lb_rowid ;
2040
2041 IF c3%ROWCOUNT > 0 THEN
2042 MSC_CL_PRE_PROCESS.v_sql_stmt := 14;
2043 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2044 UPDATE msc_st_supplies
2045 SET schedule_group_id = msc_st_schedule_group_id_s.NEXTVAL
2046 WHERE rowid = lb_rowid(j);
2047
2048 MSC_CL_PRE_PROCESS.v_sql_stmt := 15;
2049 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2050 INSERT INTO msc_local_id_supply
2051 (local_id,
2052 st_transaction_id,
2053 instance_id,
2054 entity_name,
2055 data_source_type,
2056 char1,
2057 char2,
2058 char3,
2059 char4,
2060 SOURCE_ORG_ID,
2061 SOURCE_INVENTORY_ITEM_ID,
2062 SOURCE_BILL_SEQUENCE_ID,
2063 SOURCE_ROUTING_SEQUENCE_ID,
2064 SOURCE_SCHEDULE_GROUP_ID,
2065 SOURCE_WIP_ENTITY_ID,
2066 last_update_date,
2067 last_updated_by,
2068 creation_date,
2069 created_by)
2070 SELECT
2071 schedule_group_id,
2072 st_transaction_id,
2073 MSC_CL_PRE_PROCESS.v_instance_id,
2074 'SCHEDULE_GROUP_ID',
2075 data_source_type,
2076 MSC_CL_PRE_PROCESS.v_instance_code,
2077 company_name,
2078 organization_code ,
2079 schedule_group_name,
2080 SOURCE_ORG_ID,
2081 SOURCE_INVENTORY_ITEM_ID,
2082 SOURCE_BILL_SEQUENCE_ID,
2083 SOURCE_ROUTING_SEQUENCE_ID,
2084 SOURCE_SCHEDULE_GROUP_ID,
2085 SOURCE_WIP_ENTITY_ID,
2086 MSC_CL_PRE_PROCESS.v_current_date,
2087 MSC_CL_PRE_PROCESS.v_current_user,
2088 MSC_CL_PRE_PROCESS.v_current_date,
2089 MSC_CL_PRE_PROCESS.v_current_user
2090 FROM msc_st_supplies
2091 WHERE rowid = lb_rowid(j);
2092
2093 END IF;
2094 CLOSE c3;
2095
2096 --Update disposition_id with the wip_entity_id.
2097 MSC_CL_PRE_PROCESS.v_sql_stmt := 16;
2098 UPDATE msc_st_supplies
2099 SET disposition_id = wip_entity_id
2100 WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
2101 AND batch_id = lv_batch_id
2102 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code;
2103
2104 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2105 (p_app_short_name => 'MSC',
2106 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
2107 p_message_text => lv_message_text,
2108 p_error_text => lv_error_text,
2109 p_token1 => 'COLUMN_NAMES',
2110 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
2111 ||' ORGANIZATION_CODE AND PROJECT_NUMBER',
2112 p_token2 => 'MASTER_TABLE',
2113 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
2114
2115 IF lv_return <> 0 THEN
2116 RAISE ex_logging_err;
2117 END IF;
2118
2119 --Derive Project Id.
2120 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2121 (p_table_name => 'MSC_ST_SUPPLIES',
2122 p_proj_col_name => 'PROJECT_NUMBER',
2123 p_proj_task_col_id => 'PROJECT_ID',
2124 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2125 p_entity_name => 'PROJECT_ID',
2126 p_error_text => lv_error_text,
2127 p_batch_id => lv_batch_id,
2128 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2129 p_message_text => lv_message_text,
2130 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2131 p_row => lv_column_names);
2132 IF lv_return <> 0 THEN
2133 RAISE ex_logging_err;
2134 END IF;
2135
2136 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2137 (p_app_short_name => 'MSC',
2138 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
2139 p_message_text => lv_message_text,
2140 p_error_text => lv_error_text,
2141 p_token1 => 'COLUMN_NAMES',
2142 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
2143 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
2144 ||' TASK_NUMBER',
2145 p_token2 => 'MASTER_TABLE',
2146 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
2147
2148 IF lv_return <> 0 THEN
2149 RAISE ex_logging_err;
2150 END IF;
2151
2152 --Derive Task Id.
2153 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
2154 (p_table_name => 'MSC_ST_SUPPLIES',
2155 p_proj_col_name => 'PROJECT_NUMBER',
2156 p_proj_task_col_id => 'TASK_ID',
2157 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2158 p_entity_name => 'TASK_ID',
2159 p_error_text => lv_error_text,
2160 p_task_col_name => 'TASK_NUMBER',
2161 p_batch_id => lv_batch_id,
2162 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2163 p_message_text => lv_message_text,
2164 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2165 p_row => lv_column_names);
2166
2167 IF lv_return <> 0 THEN
2168 RAISE ex_logging_err;
2169 END IF;
2170
2171
2172 MSC_CL_PRE_PROCESS.v_sql_stmt := 17;
2173 lv_sql_stmt :=
2174 'UPDATE msc_st_supplies mss '
2175 ||' SET schedule_group_id = (SELECT local_id'
2176 ||' FROM msc_local_id_supply mls'
2177 ||' WHERE mls.char4 = mss.schedule_group_name'
2178 ||' AND mls.char3 = mss.organization_code'
2179 ||' AND NVL(mls.char2, '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') = '
2180 ||' NVL(mss.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2181 ||' AND mls.char1 = mss.sr_instance_code'
2182 ||' AND mls.entity_name = ''SCHEDULE_GROUP_ID'' )'
2183 ||' WHERE deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2184 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2185 ||' AND NVL(schedule_group_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2186 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= :lv_batch_id'
2187 ||' AND sr_instance_code = :v_instance_code';
2188
2189 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2190 msc_st_util.log_message(lv_sql_stmt);
2191 END IF;
2192
2193 EXECUTE IMMEDIATE lv_sql_stmt
2194 USING lv_batch_id,
2195 MSC_CL_PRE_PROCESS.v_instance_code;
2196
2197 OPEN c4(lv_batch_id);
2198 FETCH c4 BULK COLLECT INTO lb_rowid ;
2199
2200 IF c4%ROWCOUNT > 0 THEN
2201
2202 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2203
2204 UPDATE msc_st_supplies
2205 SET job_op_seq_num =
2206 to_number(decode(length(rtrim(job_op_seq_code,'0123456789')),
2207 NULL,job_op_seq_code,'1'))
2208 WHERE rowid = lb_rowid(j);
2209 END IF;
2210 CLOSE c4;
2211
2212 lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
2213 (p_table_name => 'MSC_ST_SUPPLIES',
2214 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2215 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2216 p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
2217 p_error_text => lv_error_text,
2218 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2219 p_batch_id => lv_batch_id);
2220 IF lv_return <> 0 THEN
2221 RAISE ex_logging_err;
2222 END IF;
2223
2224 lv_return := MSC_ST_UTIL.LOG_ERROR
2225 (p_table_name => 'MSC_ST_SUPPLIES',
2226 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2227 p_row => lv_column_names,
2228 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2229 p_message_text => NULL,
2230 p_error_text => lv_error_text,
2231 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2232 p_batch_id => lv_batch_id);
2233
2234 IF lv_return <> 0 THEN
2235 RAISE ex_logging_err;
2236 END IF;
2237
2238 COMMIT;
2239 END LOOP;
2240 EXCEPTION
2241 WHEN too_many_rows THEN
2242 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_ERO_SUPPLY'||'('
2243 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
2244 msc_st_util.log_message(lv_error_text);
2245 ROLLBACK;
2246
2247 WHEN ex_logging_err THEN
2248 msc_st_util.log_message(lv_error_text);
2249 ROLLBACK;
2250
2251 WHEN OTHERS THEN
2252 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_WO_SUPPLY'||'('
2253 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
2254 msc_st_util.log_message(lv_error_text);
2255 ROLLBACK;
2256
2257 END LOAD_ERO_SUPPLY;
2258
2259 PROCEDURE LOAD_ERO_DEMAND IS
2260
2261 TYPE RowidTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2262
2263 lb_rowid RowidTab;
2264
2265 lv_local_id NUMBER;
2266 lv_sequence NUMBER;
2267 lv_column_names VARCHAR2(5000); -- Stores cocatenated column names
2268 lv_return NUMBER;
2269 lv_error_text VARCHAR2(250);
2270 lv_where_str VARCHAR2(5000);
2271 lv_sql_stmt VARCHAR2(5000);
2272 lv_cursor_stmt VARCHAR2(5000);
2273 lv_batch_id msc_st_demands.batch_id%TYPE;
2274 lv_message_text msc_errors.error_text%TYPE;
2275
2276 ex_logging_err EXCEPTION;
2277
2278 CURSOR c1(p_batch_id NUMBER) IS
2279 SELECT rowid
2280 FROM msc_st_demands
2281 WHERE process_flag IN (MSC_CL_PRE_PROCESS.G_IN_PROCESS,MSC_CL_PRE_PROCESS.G_ERROR_FLG)
2282 AND origination_type =77
2283 AND batch_id = p_batch_id
2284 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
2285 AND ENTITY='ERO';
2286
2287 CURSOR c2(p_batch_id NUMBER) IS
2288 SELECT max(rowid)
2289 FROM msc_st_demands
2290 WHERE process_flag = MSC_CL_PRE_PROCESS.G_IN_PROCESS
2291 AND sr_instance_code = MSC_CL_PRE_PROCESS.v_instance_code
2292 AND batch_id = p_batch_id
2293 AND origination_type =77 -- Not for flow schedule
2294 AND NVL(operation_seq_num,MSC_CL_PRE_PROCESS.NULL_VALUE) = MSC_CL_PRE_PROCESS.NULL_VALUE
2295 AND deleted_flag = MSC_CL_PRE_PROCESS.SYS_NO
2296 AND ENTITY='ERO'
2297 GROUP BY sr_instance_code,company_name,organization_code,routing_name,
2298 operation_seq_code,alternate_routing_designator,operation_effectivity_date;
2299
2300
2301 BEGIN
2302
2303 -- Before we start processing the record by group id( batch size ) we are going
2304 -- to check whether that there
2305 -- is any duplicates for user defined unique keys (UDKs,)
2306
2307 --For WIP component demand
2308
2309 --Duplicate records check for the records whose source is XML
2310 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2311 (p_app_short_name => 'MSC',
2312 p_error_code => 'MSC_PP_DUP_REC_FOR_XML',
2313 p_message_text => lv_message_text,
2314 p_error_text => lv_error_text);
2315
2316 IF lv_return <> 0 THEN
2317 RAISE ex_logging_err;
2318 END IF;
2319
2320 MSC_CL_PRE_PROCESS.v_sql_stmt := 01;
2321
2322 lv_sql_stmt :=
2323 'UPDATE msc_st_demands msd1'
2324 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2325 ||' error_text = '||''''||lv_message_text||''''
2326 ||' WHERE message_id < (SELECT MAX(message_id)'
2327 ||' FROM msc_st_demands msd2'
2328 ||' WHERE msd2.sr_instance_code '
2329 ||' = msd1.sr_instance_code '
2330 ||' AND msd2.organization_code '
2331 ||' = msd1.organization_code '
2332 ||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2333 ||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2334 ||' AND msd2.wip_entity_name = msd1.wip_entity_name'
2335 ||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2336 ||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2337 ||' AND msd2.item_name '
2338 ||' = msd1.item_name '
2339 ||' AND msd2.origination_type '
2340 ||' = msd1.origination_type'
2341 ||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2342 ||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE||')'
2343 ||' AND msd1.process_flag ='|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
2344 ||' AND msd1.origination_type =77 '
2345 ||' AND msd1.ENTITY =''ERO'''
2346 ||' AND msd1.sr_instance_code = :v_instance_code '
2347 ||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')<> '||MSC_CL_PRE_PROCESS.NULL_VALUE;
2348
2349 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2350 msc_st_util.log_message(lv_sql_stmt);
2351 END IF;
2352
2353 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
2354
2355 --Duplicate records check for the records whose source is batch load
2356
2357 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2358 (p_app_short_name => 'MSC',
2359 p_error_code => 'MSC_PP_DUP_REC_FOR_BATCH_LOAD',
2360 p_message_text => lv_message_text,
2361 p_error_text => lv_error_text);
2362
2363 IF lv_return <> 0 THEN
2364 RAISE ex_logging_err;
2365 END IF;
2366
2367 MSC_CL_PRE_PROCESS.v_sql_stmt := 02;
2368
2369 lv_sql_stmt :=
2370 'UPDATE msc_st_demands msd1'
2371 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2372 ||' error_text = '||''''||lv_message_text||''''
2373 ||' WHERE EXISTS( SELECT 1 '
2374 ||' FROM msc_st_demands msd2'
2375 ||' WHERE msd2.sr_instance_code '
2376 ||' = msd1.sr_instance_code '
2377 ||' AND msd2.organization_code '
2378 ||' = msd1.organization_code '
2379 ||' AND NVL(msd2.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2380 ||' = NVL(msd1.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2381 ||' AND msd2.wip_entity_name = msd1.wip_entity_name'
2382 ||' AND NVL(msd2.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2383 ||' = NVL(msd1.operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2384 ||' AND msd2.item_name '
2385 ||' = msd1.item_name '
2386 ||' AND msd2.origination_type '
2387 ||' = msd1.origination_type'
2388 ||' AND msd2.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2389 ||' AND NVL(msd2.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2390 ||' GROUP BY sr_instance_code,organization_code,wip_entity_name,'
2391 ||' company_name,operation_seq_code,item_name,origination_type'
2392 ||' HAVING COUNT(*) > 1)'
2393 ||' AND msd1.process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2394 ||' AND msd1.origination_type =77'
2395 ||' AND msd1.ENTITY =''ERO'''
2396 ||' AND msd1.sr_instance_code = :v_instance_code'
2397 ||' AND NVL(msd1.message_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE;
2398
2399 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2400 msc_st_util.log_message(lv_sql_stmt);
2401 END IF;
2402
2403 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code;
2404
2405 lv_column_names :=
2406 'ITEM_NAME ||''~''||'
2407 ||' ORGANIZATION_CODE ||''~''||'
2408 ||' USING_REQUIREMENT_QUANTITY ||''~''||'
2409 ||' WIP_ENTITY_NAME ||''~''||'
2410 ||' USING_ASSEMBLY_DEMAND_DATE ||''~''||'
2411 ||' SR_INSTANCE_CODE ||''~''||'
2412 ||' USING_ASSEMBLY_ITEM_NAME ||''~''||'
2413 ||' OPERATION_SEQ_CODE ||''~''||'
2414 ||' ORIGINATION_TYPE ||''~''||'
2415 ||' PROJECT_NUMBER ||''~''||'
2416 ||' TASK_NUMBER ||''~''||'
2417 ||' PLANNING_GROUP ||''~''||'
2418 ||' END_ITEM_UNIT_NUMBER ||''~''||'
2419 ||' DEMAND_CLASS ||''~''||'
2420 ||' WIP_STATUS_CODE ||''~''||'
2421 ||' WIP_SUPPLY_TYPE ||''~''||'
2422 ||' DELETED_FLAG ||''~''||'
2423 ||' COMPANY_NAME ||''~''||'
2424 ||' DEMAND_TYPE' ;
2425
2426
2427 LOOP
2428 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
2429 SELECT msc_st_batch_id_s.NEXTVAL
2430 INTO lv_batch_id
2431 FROM DUAL;
2432
2433 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
2434 lv_sql_stmt :=
2435 ' UPDATE msc_st_demands '
2436 ||' SET batch_id = :lv_batch_id'
2437 ||' WHERE process_flag IN ('||MSC_CL_PRE_PROCESS.G_IN_PROCESS||','||MSC_CL_PRE_PROCESS.G_ERROR_FLG||')'
2438 ||' AND sr_instance_code = :v_instance_code'
2439 ||' AND origination_type =77'
2440 ||' AND ENTITY =''ERO'''
2441 ||' AND NVL(batch_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2442 ||' AND rownum <= '||MSC_CL_PRE_PROCESS.v_batch_size;
2443
2444
2445 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2446 msc_st_util.log_message(lv_sql_stmt);
2447 END IF;
2448
2449 EXECUTE IMMEDIATE lv_sql_stmt
2450 USING lv_batch_id,
2451 MSC_CL_PRE_PROCESS.v_instance_code;
2452
2453 EXIT WHEN SQL%NOTFOUND ;
2454
2455 OPEN c1(lv_batch_id);
2456 FETCH c1 BULK COLLECT INTO lb_rowid;
2457 CLOSE c1;
2458
2459 MSC_CL_PRE_PROCESS.v_sql_stmt := 03;
2460 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
2461 UPDATE msc_st_demands
2462 SET st_transaction_id = msc_st_demands_s.NEXTVAL,
2463 refresh_id = MSC_CL_PRE_PROCESS.v_refresh_id,
2464 last_update_date = MSC_CL_PRE_PROCESS.v_current_date,
2465 last_updated_by = MSC_CL_PRE_PROCESS.v_current_user,
2466 creation_date = MSC_CL_PRE_PROCESS.v_current_date,
2467 created_by = MSC_CL_PRE_PROCESS.v_current_user
2468 WHERE rowid = lb_rowid(j);
2469
2470 -- Set the error message
2471 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2472 (p_app_short_name => 'MSC',
2473 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
2474 p_message_text => lv_message_text,
2475 p_error_text => lv_error_text,
2476 p_token1 => 'COLUMN_NAME',
2477 p_token_value1 => 'DELETED_FLAG',
2478 p_token2 => 'DEFAULT_VALUE',
2479 p_token_value2 => MSC_CL_PRE_PROCESS.SYS_NO );
2480
2481 IF lv_return <> 0 THEN
2482 RAISE ex_logging_err;
2483 END IF;
2484 --Log a warning for those records where the deleted_flag has a value other
2485 --SYS_NO
2486
2487 lv_where_str :=
2488 ' AND NVL(deleted_flag,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN(1,2)';
2489
2490 lv_return := MSC_ST_UTIL.LOG_ERROR
2491 (p_table_name => 'MSC_ST_DEMANDS',
2492 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2493 p_row => lv_column_names,
2494 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2495 p_message_text => lv_message_text,
2496 p_error_text => lv_error_text,
2497 p_batch_id => lv_batch_id,
2498 p_where_str => lv_where_str,
2499 p_col_name => 'DELETED_FLAG',
2500 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2501 p_default_value => MSC_CL_PRE_PROCESS.SYS_NO);
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 => 'ORGANIZATION_CODE');
2515
2516 IF lv_return <> 0 THEN
2517 RAISE ex_logging_err;
2518 END IF;
2519
2520 --Derive Organization_id
2521 lv_return := MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID
2522 (p_table_name => 'MSC_ST_DEMANDS',
2523 p_org_partner_name => 'ORGANIZATION_CODE',
2524 p_org_partner_id => 'ORGANIZATION_ID',
2525 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2526 p_partner_type => MSC_CL_PRE_PROCESS.G_ORGANIZATION,
2527 p_error_text => lv_error_text,
2528 p_batch_id => lv_batch_id,
2529 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
2530 p_message_text => lv_message_text,
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 => 'ITEM_NAME');
2546
2547 IF lv_return <> 0 THEN
2548 RAISE ex_logging_err;
2549 END IF;
2550
2551 --Derive Inventory_item_id
2552 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
2553 (p_table_name => 'MSC_ST_DEMANDS',
2554 p_item_col_name => 'ITEM_NAME',
2555 p_item_col_id => 'INVENTORY_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_SEV_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 -- Set the message
2570 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2571 (p_app_short_name => 'MSC',
2572 p_error_code => 'MSC_PP_INVALID_VALUE',
2573 p_message_text => lv_message_text,
2574 p_error_text => lv_error_text,
2575 p_token1 => 'COLUMN_NAME',
2576 p_token_value1 => 'USING_ASSEMBLY_ITEM_NAME');
2577
2578 IF lv_return <> 0 THEN
2579 RAISE ex_logging_err;
2580 END IF;
2581
2582 --Derive Using_assembly_item_id
2583 lv_return := MSC_ST_UTIL.DERIVE_ITEM_ID
2584 (p_table_name => 'MSC_ST_DEMANDS',
2585 p_item_col_name => 'USING_ASSEMBLY_ITEM_NAME',
2586 p_item_col_id => 'USING_ASSEMBLY_ITEM_ID',
2587 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
2588 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2589 p_message_text => lv_message_text,
2590 p_error_text => lv_error_text,
2591 p_batch_id => lv_batch_id,
2592 p_severity => MSC_CL_PRE_PROCESS.G_SEV3_ERROR,
2593 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2594 p_row => lv_column_names);
2595
2596 IF lv_return <> 0 THEN
2597 RAISE ex_logging_err;
2598 END IF;
2599
2600 -- Derive WIP_ENTITY_ID
2601 MSC_CL_PRE_PROCESS.v_sql_stmt := 04;
2602
2603 lv_sql_stmt :=
2604 'UPDATE msc_st_demands msd'
2605 ||' SET wip_entity_id = ( SELECT local_id '
2606 ||' FROM msc_local_id_supply mlid'
2607 ||' WHERE mlid.char1 = msd.sr_instance_code'
2608 ||' AND NVL(mlid.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2609 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2610 ||' AND mlid.char3 = msd.organization_code'
2611 ||' AND mlid.char4 = msd.wip_entity_name'
2612 ||' AND mlid.entity_name = ''WIP_ENTITY_ID'' )'
2613 ||' WHERE origination_type =77 '
2614 ||' AND ENTITY=''ERO'''
2615 ||' AND process_flag ='||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2616 ||' AND batch_id = :lv_batch_id'
2617 ||' AND sr_instance_code =:v_instance_code';
2618
2619 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2620 msc_st_util.log_message(lv_sql_stmt);
2621 END IF;
2622
2623 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2624
2625 -- Set the error message
2626 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2627 (p_app_short_name => 'MSC',
2628 p_error_code => 'MSC_PP_COL_REF_NOT_EXIST',
2629 p_message_text => lv_message_text,
2630 p_error_text => lv_error_text,
2631 p_token1 => 'COLUMN_NAMES',
2632 p_token_value1 => 'SR_INSTANCE_CODE,COMPANY_NAME,'
2633 ||' ORGANIZATION_CODE,WIP_ENTITY_NAME',
2634 p_token2 => 'MASTER_TABLE',
2635 p_token_value2 => 'MSC_ST_SUPPLIES',
2636 p_token3 => 'CHILD_TABLE' ,
2637 p_token_value3 => 'MSC_ST_DEMANDS' );
2638
2639 IF lv_return <> 0 THEN
2640 RAISE ex_logging_err;
2641 END IF;
2642
2643 -- Error out records where WIP_ENTITY_ID is NULL;
2644
2645 MSC_CL_PRE_PROCESS.v_sql_stmt := 05;
2646 lv_sql_stmt :=
2647 'UPDATE msc_st_demands '
2648 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2649 ||' error_text = '||''''||lv_message_text||''''
2650 ||' WHERE NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') ='||MSC_CL_PRE_PROCESS.NULL_VALUE
2651 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2652 ||' AND origination_type =77'
2653 ||' AND ENTITY=''ERO'''
2654 ||' AND batch_id = :lv_batch_id'
2655 ||' AND sr_instance_code = :v_instance_code';
2656
2657 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2658 msc_st_util.log_message(lv_sql_stmt);
2659 END IF;
2660
2661 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2662
2663
2664 -- Set the message
2665 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2666 (p_app_short_name => 'MSC',
2667 p_error_code => 'MSC_PP_COL_VAL_NULL',
2668 p_message_text => lv_message_text,
2669 p_error_text => lv_error_text,
2670 p_token1 => 'COLUMN_NAME',
2671 p_token_value1 => 'USING_REQUIREMENT_QUANTITY'
2672 || ' OR USING_ASSEMBLY_DEMAND_DATE');
2673
2674 IF lv_return <> 0 THEN
2675 RAISE ex_logging_err;
2676 END IF;
2677
2678
2679 -- Error out records where USING_REQUIREMENT_QUANTITY is NULL;
2680 -- Error out records where using_assembly_demand_date is NULL
2681
2682 MSC_CL_PRE_PROCESS.v_sql_stmt := 06;
2683 lv_sql_stmt :=
2684 'UPDATE msc_st_demands '
2685 ||' SET process_flag = '||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2686 ||' error_text = '||''''||lv_message_text||''''
2687 ||' WHERE (NVL(using_requirement_quantity,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')='||MSC_CL_PRE_PROCESS.NULL_VALUE
2688 ||' OR NVL(using_assembly_demand_date,SYSDATE-36500) = SYSDATE-36500 )'
2689 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2690 ||' AND origination_type =77'
2691 ||' AND ENTITY=''ERO'''
2692 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2693 ||' AND batch_id = :lv_batch_id'
2694 ||' AND sr_instance_code = :v_instance_code';
2695
2696 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2697 msc_st_util.log_message(lv_sql_stmt);
2698 END IF;
2699
2700 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2701
2702
2703 -- Update using_assembly_item_id = inventory_item_id
2704
2705 MSC_CL_PRE_PROCESS.v_sql_stmt := 07;
2706 lv_sql_stmt :=
2707 ' UPDATE msc_st_demands'
2708 ||' SET using_assembly_item_id = inventory_item_id'
2709 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2710 ||' AND NVL(using_assembly_item_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2711 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2712 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2713 ||' AND origination_type =77'
2714 ||' AND ENTITY=''ERO'''
2715 ||' AND batch_id = :lv_batch_id'
2716 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2717 ||' AND sr_instance_code = :v_instance_code';
2718
2719 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2720 msc_st_util.log_message(lv_sql_stmt);
2721 END IF;
2722
2723 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2724
2725 -- Update order_number = wip_entity_name
2726
2727 MSC_CL_PRE_PROCESS.v_sql_stmt := 08;
2728 lv_sql_stmt :=
2729 ' UPDATE msc_st_demands'
2730 ||' SET order_number = wip_entity_name'
2731 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2732 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2733 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2734 ||' AND origination_type =77'
2735 ||' AND ENTITY=''ERO'''
2736 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2737 ||' AND batch_id = :lv_batch_id'
2738 ||' AND sr_instance_code = :v_instance_code';
2739
2740 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2741 msc_st_util.log_message(lv_sql_stmt);
2742 END IF;
2743
2744 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2745
2746 -- Update disposition_id = wip_entity_id
2747
2748 MSC_CL_PRE_PROCESS.v_sql_stmt := 09;
2749 lv_sql_stmt :=
2750 ' UPDATE msc_st_demands'
2751 ||' SET disposition_id = wip_entity_id'
2752 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2753 ||' AND NVL(wip_entity_id,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2754 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2755 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2756 ||' AND origination_type =77'
2757 ||' AND ENTITY=''ERO'''
2758 ||' AND batch_id = :lv_batch_id'
2759 ||' AND sr_instance_code = :v_instance_code';
2760
2761 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2762 msc_st_util.log_message(lv_sql_stmt);
2763 END IF;
2764
2765 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2766
2767
2768
2769 -- UPdate MPS_DATE_REQUIRED as using_assembly_demand_date if NULL
2770 -- This is not reqd for flow schedule
2771
2772 MSC_CL_PRE_PROCESS.v_sql_stmt := 10;
2773 lv_sql_stmt :=
2774 ' UPDATE msc_st_demands'
2775 ||' SET mps_date_required = using_assembly_demand_date'
2776 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2777 ||' AND NVL(mps_date_required,SYSDATE-36500) = SYSDATE-36500'
2778 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2779 ||' AND origination_type =77'
2780 ||' AND ENTITY=''ERO'''
2781 ||' AND deleted_flag = '||MSC_CL_PRE_PROCESS.SYS_NO
2782 ||' AND batch_id = :lv_batch_id'
2783 ||' AND sr_instance_code = :v_instance_code';
2784
2785 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2786 msc_st_util.log_message(lv_sql_stmt);
2787 END IF;
2788
2789 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2790
2791
2792 -- Set the error message
2793 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2794 (p_app_short_name => 'MSC',
2795 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
2796 p_message_text => lv_message_text,
2797 p_error_text => lv_error_text,
2798 p_token1 => 'COLUMN_NAME',
2799 p_token_value1 => 'OPERATION_SEQ_CODE',
2800 p_token2 => 'DEFAULT_VALUE',
2801 p_token_value2 => MSC_CL_PRE_PROCESS.G_OPERATION_SEQ_CODE );
2802
2803 IF lv_return <> 0 THEN
2804 RAISE ex_logging_err;
2805 END IF;
2806
2807 -- Default operation_seq_code as 1 if NULL
2808 lv_where_str :=
2809 ' AND NVL(operation_seq_code,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2810 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
2811 ||' AND origination_type =77'
2812 ||' AND ENTITY=''ERO''';
2813
2814 lv_return := MSC_ST_UTIL.LOG_ERROR
2815 (p_table_name => 'MSC_ST_DEMANDS',
2816 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2817 p_row => lv_column_names,
2818 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2819 p_message_text => lv_message_text,
2820 p_error_text => lv_error_text,
2821 p_batch_id => lv_batch_id,
2822 p_where_str => lv_where_str,
2823 p_col_name => 'OPERATION_SEQ_CODE',
2824 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2825 p_default_value => MSC_CL_PRE_PROCESS.G_OPERATION_SEQ_CODE);
2826
2827 IF lv_return <> 0 THEN
2828 RAISE ex_logging_err;
2829 END IF;
2830
2831 -- Set the error message
2832 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2833 (p_app_short_name => 'MSC',
2834 p_error_code => 'MSC_PP_COL_VAL_NULL_DEFAULT',
2835 p_message_text => lv_message_text,
2836 p_error_text => lv_error_text,
2837 p_token1 => 'COLUMN_NAME',
2838 p_token_value1 => 'OPERATION_EFFECTIVITY_DATE',
2839 p_token2 => 'DEFAULT_VALUE',
2840 p_token_value2 => SYSDATE );
2841
2842 IF lv_return <> 0 THEN
2843 RAISE ex_logging_err;
2844 END IF;
2845
2846 -- Default operation_effectivity date as SYSDATE if NULL
2847
2848 lv_where_str :=
2849 ' AND NVL(operation_effectivity_date,SYSDATE-36500 ) = SYSDATE-36500 '
2850 ||' AND origination_type =77'
2851 ||' AND ENTITY=''ERO''' ;
2852
2853 lv_return := MSC_ST_UTIL.LOG_ERROR
2854 (p_table_name => 'MSC_ST_DEMANDS',
2855 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2856 p_row => lv_column_names,
2857 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
2858 p_message_text => lv_message_text,
2859 p_error_text => lv_error_text,
2860 p_batch_id => lv_batch_id,
2861 p_where_str => lv_where_str,
2862 p_col_name => 'OPERATION_EFFECTIVITY_DATE',
2863 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2864 p_default_value => 'SYSDATE');
2865
2866 IF lv_return <> 0 THEN
2867 RAISE ex_logging_err;
2868 END IF;
2869
2870 -- If routing_name is is NULL populate the item_name in routing name
2871
2872 MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
2873 lv_sql_stmt :=
2874 ' UPDATE msc_st_demands'
2875 ||' SET routing_name = nvl(USING_ASSEMBLY_ITEM_NAME,item_name)' /* bug 3768813 */
2876 ||' WHERE process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2877 ||' AND NVL(routing_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||')'
2878 ||' = '||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''
2879 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2880 ||' AND origination_type =77'
2881 ||' AND ENTITY=''ERO'''
2882 ||' AND batch_id = :lv_batch_id'
2883 ||' AND sr_instance_code = :v_instance_code';
2884
2885 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2886 msc_st_util.log_message(lv_sql_stmt);
2887 END IF;
2888
2889 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2890
2891 /* bug 3768813 */
2892 IF MSC_CL_PRE_PROCESS.v_instance_type <> MSC_CL_PRE_PROCESS.G_INS_OTHER THEN
2893
2894 -- Derive the ROUTING_SEQUENCE_ID from LOCAL ID table
2895
2896 lv_return :=msc_st_util.derive_routing_sequence_id
2897 (p_table_name => 'MSC_ST_DEMANDS',
2898 p_rtg_col_name => 'ROUTING_NAME',
2899 p_rtg_col_id =>'ROUTING_SEQUENCE_ID',
2900 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
2901 p_batch_id => lv_batch_id,
2902 p_debug => MSC_CL_PRE_PROCESS.v_debug,
2903 p_error_text => lv_error_text,
2904 p_item_id => 'using_assembly_item_id');
2905
2906 if (lv_return <> 0 )then
2907 msc_st_util.log_message(lv_error_text);
2908 end if;
2909
2910 MSC_CL_PRE_PROCESS.v_sql_stmt := 11;
2911 lv_sql_stmt:=
2912 'update msc_st_demands msd'
2913 ||' set operation_seq_num = '
2914 ||' (select operation_seq_num '
2915 ||' from msc_routing_operations mro '
2916 ||' where mro.routing_sequence_id = msd.routing_sequence_id and '
2917 ||' mro.effectivity_date = msd.operation_effectivity_date and '
2918 ||' mro.SR_INSTANCE_ID = '||MSC_CL_PRE_PROCESS.v_instance_id||' and '
2919 ||' mro.operation_seq_num = to_number(decode(length(rtrim(msd.operation_seq_code,''0123456789'')),'
2920 ||' NULL,msd.operation_seq_code,''1'')) and'
2921 ||' mro.plan_id = -1 and '
2922 ||' mro.operation_type = 1)'
2923 ||' WHERE sr_instance_code = :v_instance_code'
2924 ||' AND process_flag = '|| MSC_CL_PRE_PROCESS.G_IN_PROCESS
2925 ||' AND batch_id = :lv_batch_id ';
2926
2927 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2928 msc_st_util.log_message(lv_sql_stmt);
2929 END IF;
2930
2931 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
2932 END IF;
2933
2934 -- Derive operation seq num from local id table
2935
2936 MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
2937 lv_sql_stmt:=
2938 'UPDATE msc_st_demands msd'
2939 ||' SET operation_seq_num= (SELECT number1'
2940 ||' FROM msc_local_id_setup mlis'
2941 ||' WHERE mlis.char1 = msd.sr_instance_code'
2942 ||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2943 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2944 ||' AND mlis.char3 = msd.organization_code'
2945 ||' AND mlis.char4 = msd.routing_name'
2946 ||' AND mlis.char5 = msd.operation_seq_code'
2947 ||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2948 ||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
2949 ||' AND mlis.date1 = msd.operation_effectivity_date'
2950 ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
2951 ||' WHERE sr_instance_code = :v_instance_code'
2952 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
2953 ||' AND batch_id = :lv_batch_id'
2954 ||' AND operation_seq_num is null'; /* bug 3768813 */
2955
2956
2957 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2958 msc_st_util.log_message(lv_sql_stmt);
2959 END IF;
2960
2961 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
2962
2963 -- Set the message
2964
2965 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2966 (p_app_short_name => 'MSC',
2967 p_error_code => 'MSC_PP_DELETE_FAIL',
2968 p_message_text => lv_message_text,
2969 p_error_text => lv_error_text);
2970
2971 IF lv_return <> 0 THEN
2972 RAISE ex_logging_err;
2973 END IF;
2974
2975 -- Error out the records where operation_seq_num is NULL
2976 -- And deleted_flag = SYS_YES
2977
2978 MSC_CL_PRE_PROCESS.v_sql_stmt := 13;
2979
2980 lv_sql_stmt :=
2981 'UPDATE msc_st_demands '
2982 ||' SET process_flag ='||MSC_CL_PRE_PROCESS.G_ERROR_FLG||','
2983 ||' error_text = '||''''||lv_message_text||''''
2984 ||' WHERE NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') = '||MSC_CL_PRE_PROCESS.NULL_VALUE
2985 ||' AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_YES
2986 ||' AND origination_type =77'
2987 ||' AND ENTITY=''ERO'''
2988 ||' AND batch_id = :lv_batch_id'
2989 ||' AND sr_instance_code =:v_instance_code';
2990
2991 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
2992 msc_st_util.log_message(lv_sql_stmt);
2993 END IF;
2994
2995 EXECUTE IMMEDIATE lv_sql_stmt USING lv_batch_id,MSC_CL_PRE_PROCESS.v_instance_code;
2996
2997 -- Set the error message
2998 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
2999 (p_app_short_name => 'MSC',
3000 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
3001 p_message_text => lv_message_text,
3002 p_error_text => lv_error_text,
3003 p_token1 => 'COLUMN_NAMES',
3004 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
3005 ||' ORGANIZATION_CODE AND PROJECT_NUMBER',
3006 p_token2 => 'MASTER_TABLE',
3007 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
3008
3009 IF lv_return <> 0 THEN
3010 RAISE ex_logging_err;
3011 END IF;
3012
3013 --Derive Project Id.
3014 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
3015 (p_table_name => 'MSC_ST_DEMANDS',
3016 p_proj_col_name => 'PROJECT_NUMBER',
3017 p_proj_task_col_id => 'PROJECT_ID',
3018 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3019 p_entity_name => 'PROJECT_ID',
3020 p_error_text => lv_error_text,
3021 p_batch_id => lv_batch_id,
3022 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3023 p_message_text => lv_message_text,
3024 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3025 p_row => lv_column_names);
3026 IF lv_return <> 0 THEN
3027 RAISE ex_logging_err;
3028 END IF;
3029
3030 -- Set the error message
3031 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3032 (p_app_short_name => 'MSC',
3033 p_error_code => 'MSC_PP_FK_REF_NOT_EXIST',
3034 p_message_text => lv_message_text,
3035 p_error_text => lv_error_text,
3036 p_token1 => 'COLUMN_NAMES',
3037 p_token_value1 => ' SR_INSTANCE_CODE, COMPANY_NAME,'
3038 ||' ORGANIZATION_CODE, PROJECT_NUMBER,'
3039 ||' TASK_NUMBER',
3040 p_token2 => 'MASTER_TABLE',
3041 p_token_value2 => 'MSC_ST_PROJECT_TASKS');
3042
3043 IF lv_return <> 0 THEN
3044 RAISE ex_logging_err;
3045 END IF;
3046
3047 --Derive Task Id.
3048 lv_return := MSC_ST_UTIL.DERIVE_PROJ_TASK_ID
3049 (p_table_name => 'MSC_ST_DEMANDS',
3050 p_proj_col_name => 'PROJECT_NUMBER',
3051 p_proj_task_col_id => 'TASK_ID',
3052 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3053 p_entity_name => 'TASK_ID',
3054 p_error_text => lv_error_text,
3055 p_task_col_name => 'TASK_NUMBER',
3056 p_batch_id => lv_batch_id,
3057 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3058 p_message_text => lv_message_text,
3059 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3060 p_row => lv_column_names);
3061
3062 IF lv_return <> 0 THEN
3063 RAISE ex_logging_err;
3064 END IF;
3065
3066 -- Set the error message
3067 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3068 (p_app_short_name => 'MSC',
3069 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3070 p_message_text => lv_message_text,
3071 p_error_text => lv_error_text,
3072 p_token1 => 'COLUMN_NAME',
3073 p_token_value1 => 'DEMAND_TYPE',
3074 p_token2 => 'DEFAULT_VALUE',
3075 p_token_value2 => MSC_CL_PRE_PROCESS.G_DEMAND_TYPE );
3076
3077 IF lv_return <> 0 THEN
3078 RAISE ex_logging_err;
3079 END IF;
3080
3081 -- Default demand_type to 1 always
3082
3083 lv_where_str := ' AND NVL(demand_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') <> '||MSC_CL_PRE_PROCESS.G_DEMAND_TYPE
3084 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3085 ||' AND ENTITY=''ERO''' ;
3086
3087 lv_return := MSC_ST_UTIL.LOG_ERROR
3088 (p_table_name => 'MSC_ST_DEMANDS',
3089 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3090 p_row => lv_column_names,
3091 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3092 p_message_text => lv_message_text,
3093 p_error_text => lv_error_text,
3094 p_batch_id => lv_batch_id,
3095 p_where_str => lv_where_str,
3096 p_col_name => 'DEMAND_TYPE',
3097 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3098 p_default_value => MSC_CL_PRE_PROCESS.G_DEMAND_TYPE);
3099
3100 IF lv_return <> 0 THEN
3101 RAISE ex_logging_err;
3102 END IF;
3103
3104
3105 -- Set the error message
3106 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3107 (p_app_short_name => 'MSC',
3108 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3109 p_message_text => lv_message_text,
3110 p_error_text => lv_error_text,
3111 p_token1 => 'COLUMN_NAME',
3112 p_token_value1 => 'WIP_SUPPLY_TYPE',
3113 p_token2 => 'DEFAULT_VALUE',
3114 p_token_value2 => MSC_CL_PRE_PROCESS.G_WIP_SUPPLY_TYPE );
3115
3116 IF lv_return <> 0 THEN
3117 RAISE ex_logging_err;
3118 END IF;
3119
3120 -- Default wip_supply_type as 1
3121
3122 lv_where_str := ' AND NVL(wip_supply_type,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN (1,2,3,4,5,6,7)'
3123 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3124 ||' AND ENTITY=''ERO''' ;
3125
3126 lv_return := MSC_ST_UTIL.LOG_ERROR
3127 (p_table_name => 'MSC_ST_DEMANDS',
3128 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3129 p_row => lv_column_names,
3130 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3131 p_message_text => lv_message_text,
3132 p_error_text => lv_error_text,
3133 p_batch_id => lv_batch_id,
3134 p_where_str => lv_where_str,
3135 p_col_name => 'WIP_SUPPLY_TYPE',
3136 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3137 p_default_value => MSC_CL_PRE_PROCESS.G_WIP_SUPPLY_TYPE );
3138
3139 IF lv_return <> 0 THEN
3140 RAISE ex_logging_err;
3141 END IF;
3142
3143
3144 -- Set the error message
3145 lv_return := MSC_ST_UTIL.GET_ERROR_MESSAGE
3146 (p_app_short_name => 'MSC',
3147 p_error_code => 'MSC_PP_INVALID_COL_VALUE',
3148 p_message_text => lv_message_text,
3149 p_error_text => lv_error_text,
3150 p_token1 => 'COLUMN_NAME',
3151 p_token_value1 => 'WIP_STATUS_CODE',
3152 p_token2 => 'DEFAULT_VALUE',
3153 p_token_value2 => MSC_CL_PRE_PROCESS.G_WIP_STATUS_CODE );
3154
3155 IF lv_return <> 0 THEN
3156 RAISE ex_logging_err;
3157 END IF;
3158
3159 -- Default wip_status_code as 1(unrelased)
3160
3161 lv_where_str := ' AND NVL(wip_status_code,'||MSC_CL_PRE_PROCESS.NULL_VALUE||') NOT IN (1,3,4,6,7,12)'
3162 ||' AND origination_type =77 AND deleted_flag ='||MSC_CL_PRE_PROCESS.SYS_NO
3163 ||' AND ENTITY=''ERO''' ;
3164
3165 lv_return := MSC_ST_UTIL.LOG_ERROR
3166 (p_table_name => 'MSC_ST_DEMANDS',
3167 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3168 p_row => lv_column_names,
3169 p_severity => MSC_CL_PRE_PROCESS.G_SEV_WARNING,
3170 p_message_text => lv_message_text,
3171 p_error_text => lv_error_text,
3172 p_batch_id => lv_batch_id,
3173 p_where_str => lv_where_str,
3174 p_col_name => 'WIP_STATUS_CODE',
3175 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3176 p_default_value => MSC_CL_PRE_PROCESS.G_WIP_STATUS_CODE);
3177
3178 IF lv_return <> 0 THEN
3179 RAISE ex_logging_err;
3180 END IF;
3181
3182 --Call to customised validation.
3183 MSC_CL_PRE_PROCESS_HOOK.ENTITY_VALIDATION
3184 (ERRBUF => lv_error_text,
3185 RETCODE => lv_return,
3186 pBatchID => lv_batch_id,
3187 pInstanceCode => MSC_CL_PRE_PROCESS.v_instance_code,
3188 pEntityName => 'MSC_ST_DEMANDS',
3189 pInstanceID => MSC_CL_PRE_PROCESS.v_instance_id);
3190
3191 IF NVL(lv_return,0) <> 0 THEN
3192 RAISE ex_logging_err;
3193 END IF;
3194
3195 -- Generate the operation_seq_num and populate the LID table
3196
3197 OPEN c2(lv_batch_id);
3198 FETCH c2 BULK COLLECT INTO lb_rowid ;
3199
3200
3201 IF c2%ROWCOUNT > 0 THEN
3202 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
3203
3204 -- SELECT msc_st_operation_sequence_id_s.NEXTVAL
3205 -- INTO lv_local_id
3206 -- FROM DUAL;
3207
3208 UPDATE msc_st_demands
3209 SET operation_seq_num =
3210 to_number(decode(length(rtrim(operation_seq_code,'0123456789')),
3211 NULL,operation_seq_code,'1'))
3212 WHERE rowid = lb_rowid(j);
3213
3214 FORALL j IN lb_rowid.FIRST..lb_rowid.LAST
3215
3216 -- Insert into the LID table
3217
3218 INSERT INTO msc_local_id_setup
3219 (local_id,
3220 st_transaction_id,
3221 instance_id,
3222 entity_name,
3223 data_source_type,
3224 char1,
3225 char2,
3226 char3,
3227 char4,
3228 char5,
3229 char6,
3230 number1,
3231 date1,
3232 SOURCE_ORGANIZATION_ID,
3233 SOURCE_INVENTORY_ITEM_ID,
3234 SOURCE_PROJECT_ID,
3235 SOURCE_TASK_ID,
3236 SOURCE_WIP_ENTITY_ID,
3237 SOURCE_OPERATION_SEQ_NUM,
3238 SOURCE_USING_ASSEMBLY_ID,
3239 last_update_date,
3240 last_updated_by,
3241 creation_date,
3242 created_by )
3243 SELECT
3244 msc_st_operation_sequence_id_s.NEXTVAL,
3245 st_transaction_id,
3246 MSC_CL_PRE_PROCESS.v_instance_id,
3247 'OPERATION_SEQUENCE_ID',
3248 data_source_type,
3249 MSC_CL_PRE_PROCESS.v_instance_code,
3250 company_name,
3251 organization_code,
3252 routing_name,
3253 operation_seq_code,
3254 alternate_routing_designator,
3255 operation_seq_num,
3256 operation_effectivity_date,
3257 SOURCE_ORGANIZATION_ID,
3258 SOURCE_INVENTORY_ITEM_ID,
3259 SOURCE_PROJECT_ID,
3260 SOURCE_TASK_ID,
3261 SOURCE_WIP_ENTITY_ID,
3262 SOURCE_OPERATION_SEQ_NUM,
3263 SOURCE_USING_ASSEMBLY_ITEM_ID,
3264 MSC_CL_PRE_PROCESS.v_current_date,
3265 MSC_CL_PRE_PROCESS.v_current_user,
3266 MSC_CL_PRE_PROCESS.v_current_date,
3267 MSC_CL_PRE_PROCESS.v_current_user
3268 FROM msc_st_demands
3269 WHERE rowid = lb_rowid(j) ;
3270
3271 END IF;
3272 CLOSE c2;
3273
3274 -- Update operation seq num from local id table
3275
3276 MSC_CL_PRE_PROCESS.v_sql_stmt := 12;
3277 lv_sql_stmt:=
3278 'UPDATE msc_st_demands msd'
3279 ||' SET operation_seq_num= (SELECT number1'
3280 ||' FROM msc_local_id_setup mlis'
3281 ||' WHERE mlis.char1 = msd.sr_instance_code'
3282 ||' AND NVL(mlis.char2,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3283 ||' = NVL(msd.company_name,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3284 ||' AND mlis.char3 = msd.organization_code'
3285 ||' AND mlis.char4 = msd.routing_name'
3286 ||' AND mlis.char5 = msd.operation_seq_code'
3287 ||' AND NVL(mlis.char6,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3288 ||' = NVL(msd.alternate_routing_designator,'||''''||MSC_CL_PRE_PROCESS.NULL_CHAR||''''||') '
3289 ||' AND mlis.date1 = msd.operation_effectivity_date'
3290 ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'') '
3291 ||' WHERE sr_instance_code = :v_instance_code'
3292 ||' AND NVL(operation_seq_num,'||MSC_CL_PRE_PROCESS.NULL_VALUE||')= '||MSC_CL_PRE_PROCESS.NULL_VALUE
3293 ||' AND process_flag = '||MSC_CL_PRE_PROCESS.G_IN_PROCESS
3294 ||' AND batch_id = :lv_batch_id';
3295
3296
3297 IF MSC_CL_PRE_PROCESS.V_DEBUG THEN
3298 msc_st_util.log_message(lv_sql_stmt);
3299 END IF;
3300
3301 EXECUTE IMMEDIATE lv_sql_stmt USING MSC_CL_PRE_PROCESS.v_instance_code,lv_batch_id;
3302
3303
3304 lv_return := MSC_ST_UTIL.SET_PROCESS_FLAG
3305 (p_table_name => 'MSC_ST_DEMANDS',
3306 p_instance_id => MSC_CL_PRE_PROCESS.v_instance_id,
3307 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3308 p_process_flag => MSC_CL_PRE_PROCESS.G_VALID,
3309 p_error_text => lv_error_text,
3310 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3311 p_batch_id => lv_batch_id);
3312
3313 IF lv_return <> 0 THEN
3314 RAISE ex_logging_err;
3315 END IF;
3316
3317 lv_return := MSC_ST_UTIL.LOG_ERROR
3318 (p_table_name => 'MSC_ST_DEMANDS',
3319 p_instance_code => MSC_CL_PRE_PROCESS.v_instance_code,
3320 p_row => lv_column_names,
3321 p_severity => MSC_CL_PRE_PROCESS.G_SEV_ERROR,
3322 p_message_text => NULL,
3323 p_error_text => lv_error_text,
3324 p_debug => MSC_CL_PRE_PROCESS.v_debug,
3325 p_batch_id => lv_batch_id);
3326
3327 IF lv_return <> 0 THEN
3328 RAISE ex_logging_err;
3329 END IF;
3330
3331 COMMIT;
3332 END LOOP ;
3333
3334 EXCEPTION
3335 WHEN too_many_rows THEN
3336 lv_error_text := substr('MSC_CL__RPO_PRE_PROCESS.LOAD_ERO_DEMAND'||'('
3337 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
3338 msc_st_util.log_message(lv_error_text);
3339 ROLLBACK ;
3340
3341 WHEN ex_logging_err THEN
3342 msc_st_util.log_message(lv_error_text);
3343 ROLLBACK;
3344
3345 WHEN OTHERS THEN
3346 lv_error_text := substr('MSC_CL_PRE_PROCESS.LOAD_ERO_DEMAND'||'('
3347 ||MSC_CL_PRE_PROCESS.v_sql_stmt||')'|| SQLERRM, 1, 240);
3348 msc_st_util.log_message(lv_error_text);
3349 ROLLBACK;
3350
3351 END LOAD_ERO_DEMAND;
3352
3353
3354
3355
3356 END MSC_CL_RPO_PRE_PROCESS;