[Home] [Help]
PACKAGE BODY: APPS.MSC_ST_UTIL
Source
1 PACKAGE BODY MSC_ST_UTIL AS
2 /* $Header: MSCSUTLB.pls 120.14 2012/03/07 19:12:22 pstrnad ship $ */
3
4 v_sql_stmt PLS_INTEGER;--Holds the DML statement no used for error logging.
5 v_debug BOOLEAN;
6 v_my_company VARCHAR(1000) := NULL;
7 v_seq_num NUMBER := 0 ;
8
9 /*=================================================================================+
10 | DESCRIPTION : This function returns the debug mode set for the profile |
11 | 'MRP: Debug Mode'. |
12 +==================================================================================*/
13 Function retn_debug_mode
14 RETURN BOOLEAN IS
15 BEGIN
16 IF v_debug IS NULL THEN
17 v_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
18 END IF;
19 RETURN v_debug;
20 END retn_debug_mode;
21
22 /*==========================================================================+
23 | DESCRIPTION : This function is called for inserting the errored records |
24 | into the error table for the severities warning and error. |
25 +==========================================================================*/
26 FUNCTION LOG_ERROR
27 (p_table_name VARCHAR2,
28 p_instance_code VARCHAR2,
29 p_row CLOB,
30 p_severity NUMBER DEFAULT G_SEV_ERROR,
31 p_error_text OUT NOCOPY VARCHAR2,
32 p_message_text VARCHAR2 DEFAULT NULL,
33 p_batch_id NUMBER DEFAULT NULL,
34 p_where_str VARCHAR2 DEFAULT NULL,
35 p_col_name VARCHAR2 DEFAULT NULL,
36 p_default_value VARCHAR2 DEFAULT NULL,
37 p_debug BOOLEAN DEFAULT FALSE,
38 p_propagated VARCHAR2 DEFAULT 'N')
39 RETURN NUMBER IS
40 lv_sql_stmt VARCHAR2(5000);
41 lv_where_str VARCHAR2(5000);
42 lv_message_text msc_errors.error_text%TYPE;
43 BEGIN
44
45 --For the severity warning, the error text will be taken from the
46 --parameter and for the severity error, error text will be picked from
47 --the corresponding BO tables.
48
49 IF p_severity = G_WARNING THEN
50 lv_message_text := ''''||p_message_text||'''';
51 ELSE
52 lv_message_text := 'error_text';
53 END IF;
54
55 IF p_batch_id IS NOT NULL THEN
56 lv_where_str :=
57 ' AND batch_id = :p_batch_id '||p_where_str;
58 ELSE
59 lv_where_str :=p_where_str;
63 lv_sql_stmt :=
60 END IF;
61
62 v_sql_stmt := 01;
64 'INSERT INTO msc_errors'
65 ||'( error_id,'
66 ||' transaction_id,'
67 ||' message_id,'
68 ||' instance_code,'
69 ||' table_name,'
70 ||' propagated,'
71 ||' source,'
72 ||' rrow,'
73 ||' severity,'
74 ||' message_sent,'
75 ||' last_update_date,'
76 ||' last_updated_by,'
77 ||' creation_date,'
78 ||' created_by,'
79 ||' last_update_login,'
80 ||' request_id,'
81 ||' program_application_id,'
82 ||' program_id,'
83 ||' program_update_date,'
84 ||' error_text)'
85 ||' SELECT'
86 ||' msc_errors_s.NEXTVAL,'
87 ||' st_transaction_id,'
88 ||' message_id,'
89 || ''''||p_instance_code||''''||','
90 || ''''||p_table_name||''''||','
91 || ''''||p_propagated||''''||','
92 ||' data_source_type,'
93 || p_row||','
94 || p_severity||','
95 || SYS_NO||','
96 ||' last_update_date,'
97 ||' last_updated_by,'
98 ||' creation_date,'
99 ||' created_by,'
100 ||' last_update_login,'
101 ||' request_id,'
102 ||' program_application_id,'
103 ||' program_id,'
104 ||' program_update_date,'
105 || lv_message_text
106 ||' FROM '
107 || p_table_name
108 ||' WHERE sr_instance_code = :p_instance_code'
109 ||' AND (('||p_severity||'='||G_WARNING
110 ||' AND process_flag = '||G_IN_PROCESS||')'
111 ||' OR ('||p_severity ||'='||G_SEV_ERROR
112 ||' AND process_flag = '||G_ERROR||'))'
113 || lv_where_str;
114
115
116 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
117
118 IF p_batch_id IS NOT NULL THEN
119 EXECUTE IMMEDIATE lv_sql_stmt
120 USING p_instance_code,
121 p_batch_id;
122 ELSE
123 EXECUTE IMMEDIATE lv_sql_stmt
124 USING p_instance_code;
125 END IF;
126
127 IF p_default_value IS NOT NULL THEN
128 v_sql_stmt := 02;
129 lv_sql_stmt :=
130 'UPDATE '||p_table_name
131 ||' SET '||p_col_name|| ' = '||p_default_value
132 ||' WHERE sr_instance_code = :p_instance_code'
133 ||' AND process_flag = '||G_IN_PROCESS
134 || lv_where_str;
135
136
137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
138
139 IF p_batch_id IS NOT NULL THEN
140 EXECUTE IMMEDIATE lv_sql_stmt
141 USING p_instance_code,
142 p_batch_id;
143 ELSE
144 EXECUTE IMMEDIATE lv_sql_stmt
145 USING p_instance_code;
146 END IF;
147 END IF;
148 RETURN(0);
149 EXCEPTION
150 WHEN OTHERS THEN
151 p_error_text := substr('MSC_ST_UTIL.LOG_ERROR'||'('||v_sql_stmt||')'
152 || SQLERRM, 1, 240);
153 return(SQLCODE);
154 END LOG_ERROR;
155
156 /*===========================================================================+
157 | DESCRIPTION : This function is called for deriving the company_id |
158 | and it is based on the instance_type. If the Instance type |
159 | is 4, then, the company_id is derived from the hz_parties |
160 | else the company_id is updated with -1. |
161 | |
162 | p_table_name - Name of the table for whose column the id derived. |
163 | (eg., msc_st_supplies). |
164 | p_company_name - Name of the column whose id is derived. |
165 | (eg., company_name) |
166 | p_company_id - Name of the column which stores the id. |
167 | (eg., company_id) |
168 | p_instance_code - Instance_code column name |
169 | p_default_value - default value(-1) will be used if the instance_type |
170 | is other than 5(ie.,SCE) |
171 | p_instance_type - Type of the instance |
172 | (eg., 4 - SCE ) |
173 | p_message_text - Pre-defined message text. |
174 | p_severity - Severity fo the error(1.Warning and 2. Error |
175 | p_error_text - This communicates the error message to the calling |
176 | function, if any. |
177 | p_row - Concatenated column names of the table. This is |
178 | used for error logging in case of child tables. |
179 +==========================================================================*/
180 FUNCTION DERIVE_COMPANY_ID
181 (p_table_name VARCHAR2,
182 p_company_name VARCHAR2,
183 p_company_id VARCHAR2,
184 p_instance_code VARCHAR2,
185 p_error_text OUT NOCOPY VARCHAR2,
186 p_default_value NUMBER DEFAULT -1,
187 p_instance_type NUMBER DEFAULT 3 ,
188 p_batch_id NUMBER DEFAULT NULL_VALUE,
189 p_severity NUMBER DEFAULT 0 ,
190 p_message_text VARCHAR2 DEFAULT NULL,
191 p_debug BOOLEAN DEFAULT FALSE,
192 p_row LONG DEFAULT NULL)
193 RETURN NUMBER IS
194 lv_sql_stmt VARCHAR2(5000);
195 lv_where_str VARCHAR2(100);
196 lv_status NUMBER := 0;
197 lv_my_company VARCHAR2(1000);
198 BEGIN
199 lv_my_company := GET_MY_COMPANY;
200 v_sql_stmt := 03;
201 lv_sql_stmt :=
202 'UPDATE '|| p_table_name ||' t1 '
203 ||' SET '||p_company_id ||'= -1 '
204 ||' WHERE exists( SELECT 1 '
205 ||' FROM msc_companies mc '
206 ||' WHERE mc.company_name = nvl(t1.'||p_company_name ||' ,:lv_my_company) '
207 ||' AND mc.company_id = 1 '
208 ||' AND NVL(mc.disable_date,sysdate+1) > sysdate) '
209 ||' AND t1.process_flag ='|| G_IN_PROCESS
210 ||' AND t1.sr_instance_code = :p_instance_code'
211 ||' AND NVL(t1.batch_id,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_VALUE||''''||')';
212
213
214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
215
216
217 EXECUTE IMMEDIATE lv_sql_stmt
218 USING lv_my_company,
219 p_instance_code,
220 p_batch_id;
221
222 IF G_MULTI_TIER_ENABLE OR TRIM(UPPER(p_table_name)) ='MSC_ST_COMPANY_USERS' THEN
223 v_sql_stmt := 04;
224 lv_sql_stmt :=
225 'UPDATE '|| p_table_name ||' t1 '
226 ||' SET '||p_company_id ||'= (SELECT local_id from MSC_LOCAL_ID_SETUP'
227 ||' WHERE char1 = t1.sr_instance_code'
228 ||' and NVL(char3,'||''''||NULL_CHAR||''''||') = NVL(t1.'||p_company_name||','||''''||NULL_CHAR||''''||')'
229 ||' and entity_name = '||''''||'SR_TP_ID'||''''
230 ||' and number1 in (1,2) '
231 ||' and rownum = 1) ' -- we need the first occurence of sr_tp_id
232 ||' WHERE t1.'||p_company_id ||' IS NULL'
233 ||' AND t1.process_flag ='|| G_IN_PROCESS
234 ||' AND t1.sr_instance_code = :p_instance_code'
235 ||' AND NVL(t1.batch_id ,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_CHAR||''''||')';
236
237
238
239 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
240
241
242 EXECUTE IMMEDIATE lv_sql_stmt
243 USING p_instance_code,
244 p_batch_id;
245
246 ELSIF TRIM(UPPER(p_table_name))='MSC_ST_TRADING_PARTNERS' THEN
247
248 v_sql_stmt := 04;
249 lv_sql_stmt :=
250 'UPDATE '|| p_table_name ||' t1 '
251 ||' SET '||p_company_id ||'= (SELECT local_id from MSC_LOCAL_ID_SETUP'
252 ||' WHERE char1 = t1.sr_instance_code'
253 ||' and NVL(char3,'||''''||NULL_CHAR||''''||') = NVL(t1.'||p_company_name||','||''''||NULL_CHAR||''''||')'
254 ||' and entity_name = '||''''||'SR_TP_ID'||''''
255 ||' and number1 in (1,2) '
256 ||' and rownum = 1) ' -- we need the first occurence of sr_tp_id
257 ||' WHERE t1.'||p_company_id ||' IS NULL'
258 ||' AND t1.process_flag ='|| G_IN_PROCESS
259 ||' AND t1.sr_instance_code = :p_instance_code'
260 ||' AND NVL(t1.batch_id ,'||''''||NULL_CHAR||''''||') = NVL(:p_batch_id,'||''''||NULL_CHAR||''''||')'
261 ||' AND t1.partner_type =3';
262
263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
264
265
266 EXECUTE IMMEDIATE lv_sql_stmt
267 USING p_instance_code,
268 p_batch_id;
269
270 END IF;
271
272 v_sql_stmt := 05;
273 lv_sql_stmt :=
274 'UPDATE '||p_table_name ||' t1 '
275 ||' SET error_text ='||''''||p_message_text||''''||','
276 ||' process_flag = '||g_error
277 ||' WHERE NVL(t1.'||p_company_id||','||NULL_VALUE||') = '||NULL_VALUE
278 ||' AND sr_instance_code = :p_instance_code'
279 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
280 ||' AND process_flag = ' ||G_IN_PROCESS;
281
282
283 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
284
285
286 EXECUTE IMMEDIATE lv_sql_stmt
287 USING p_instance_code,
288
289 p_batch_id;
290
291
292 RETURN(lv_status);
293
294 EXCEPTION
295 WHEN too_many_rows THEN
296 p_error_text := substr('MSC_ST_UTIL.DERIVE_COMPANY_ID'||'('
297 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
298 return(SQLCODE);
299
300 WHEN others THEN
301 p_error_text := substr('MSC_ST_UTIL.DERIVE_COMPANY_ID'||'('
302 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
303 return(SQLCODE);
304
305 END DERIVE_COMPANY_ID;
306
307
308 /*==========================================================================+
309 | DESCRIPTION : This function returns the Operator's name |
310 | Default is 'My Company' |
311 +==========================================================================*/
312 FUNCTION GET_MY_COMPANY return VARCHAR2 IS
313 p_my_company VARCHAR2(1000);
314 BEGIN
315
316 /* Get the name of the own Company */
317 /* This name is seeded with company_is = 1 in msc_companies */
318 BEGIN
319 IF v_my_company IS NULL THEN
320 select company_name into p_my_company
321 from msc_companies
322 where company_id = 1;
323 v_my_company := p_my_company;
324 ELSE
325 p_my_company := v_my_company;
326 END IF;
327 EXCEPTION
328 WHEN OTHERS THEN
329 return 'My Company';
330 END;
331
332 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'The name in GET_MY_COMPANY :'||p_my_company);
333 return p_my_company;
334
335 END GET_MY_COMPANY;
336
337
338
339 /*==========================================================================+
340 | DESCRIPTION : This function is called for deriving the item_id's based on|
341 | the values in msc_local_id_item. For the tables other |
342 | than msc_st_system_items, if the derivation fails then the |
343 | record will be errored out. |
344 | Severity - 1 - Error |
345 | 2 - Warning |
346 | 3 - Error if value for item name exists |
347 +==========================================================================*/
348 FUNCTION DERIVE_ITEM_ID
349 (p_table_name VARCHAR2,
350 p_item_col_name VARCHAR2, --item_name
351 p_item_col_id VARCHAR2, --inventory_item_id
352 p_instance_id NUMBER,
353 p_instance_code VARCHAR2,
354 p_error_text OUT NOCOPY VARCHAR2,
355 p_batch_id NUMBER DEFAULT NULL_VALUE,
356 p_severity NUMBER DEFAULT 0,
357 p_message_text VARCHAR2 DEFAULT NULL,
358 p_debug BOOLEAN DEFAULT FALSE,
359 p_row LONG DEFAULT NULL,
360 p_check_org BOOLEAN DEFAULT TRUE)
361 RETURN NUMBER IS
362 lv_sql_stmt VARCHAR2(5000);
363 lv_where_str VARCHAR2(100);
364 lv_status NUMBER := 0;
365 BEGIN
366 v_sql_stmt := 06;
367
368 IF v_instance_type = G_INS_OTHER THEN
369 IF p_check_org THEN
370 lv_sql_stmt :=
371 'UPDATE '||p_table_name ||' t1'
372 ||' SET '||p_item_col_id
373 ||' = (SELECT distinct local_id'
374 ||' FROM msc_local_id_item t2'
375 ||' WHERE t2.char1 = t1.sr_instance_code '
376 ||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
377 ||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
378 ||' AND t2.char3 = t1.organization_code'
379 ||' AND t2.char4 = t1.'||p_item_col_name
380 ||' AND t2.entity_name = ''SR_INVENTORY_ITEM_ID'' '
381 ||' AND t2.instance_id = :p_instance_id)'
382 ||' WHERE sr_instance_code = :p_instance_code'
383 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
384 ||' AND process_flag = '||G_IN_PROCESS;
385 ELSE
386 lv_sql_stmt :=
387 'UPDATE '||p_table_name ||' t1'
388 ||' SET '||p_item_col_id
389 ||' = (SELECT distinct local_id'
390 ||' FROM msc_local_id_item t2'
391 ||' WHERE t2.char1 = t1.sr_instance_code '
392 ||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
393 ||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
394 ||' AND t2.char4 = t1.'||p_item_col_name
395 ||' AND t2.entity_name = ''SR_INVENTORY_ITEM_ID'' '
396 ||' AND t2.instance_id = :p_instance_id)'
397 ||' WHERE sr_instance_code = :p_instance_code'
398 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
399 ||' AND process_flag = '||G_IN_PROCESS;
400 END IF;
401
402 ELSE
403
404 lv_sql_stmt :=
405 'UPDATE '||p_table_name ||' t1'
406 ||' SET '||p_item_col_id
407 ||' = (SELECT distinct mil.sr_inventory_item_id'
408 ||' FROM msc_item_id_lid mil, msc_system_items t2'
409 ||' WHERE mil.sr_instance_id = t2.sr_instance_id'
410 ||' AND mil.inventory_item_id = t2.inventory_item_id'
411 ||' AND t2.item_name = t1.'||p_item_col_name
412 ||' AND t2.sr_instance_id = :p_instance_id'
413 ||' AND t2.organization_id = t1.organization_id'
414 ||' AND t2.plan_id = -1)'
415 ||' WHERE sr_instance_code = :p_instance_code'
416 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
417 ||' AND process_flag = '||G_IN_PROCESS;
418
419 END IF;
420
421
422 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
423
424 EXECUTE IMMEDIATE lv_sql_stmt
425 USING p_instance_id,
426 p_instance_code,
427 p_batch_id;
428
429 IF p_message_text IS NOT NULL and p_severity = 1 THEN
430 v_sql_stmt := 07;
431 lv_sql_stmt :=
432 'UPDATE '||p_table_name ||' t1'
433 ||' SET error_text = '||''''||p_message_text||''''||','
434 ||' process_flag = '||g_error
435 ||' WHERE NVL(t1.'||p_item_col_id||','||NULL_VALUE||') = '||NULL_VALUE
436 ||' AND sr_instance_code = :p_instance_code'
437 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
438 ||' AND process_flag = ' ||G_IN_PROCESS;
439
440
441 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
442
443 EXECUTE IMMEDIATE lv_sql_stmt
444 USING p_instance_code,
445 p_batch_id;
446
447 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
448 lv_where_str :=
449 ' AND NVL('||p_item_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
450
451 lv_status := LOG_ERROR(p_table_name => p_table_name,
452 p_instance_code => p_instance_code,
453 p_row => p_row,
454 p_severity => p_severity,
455 p_propagated => 'N',
456 p_where_str => lv_where_str,
457 p_message_text => p_message_text,
458 p_error_text => p_error_text,
459 p_batch_id => p_batch_id);
460
461 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
462 v_sql_stmt := 08;
463 lv_sql_stmt :=
464 'UPDATE '||p_table_name ||' t1'
465 ||' SET error_text = '||''''||p_message_text||''''||','
466 ||' process_flag = '||g_error
467 ||' WHERE NVL(t1.'||p_item_col_id||','||NULL_VALUE||') = '||NULL_VALUE
468 ||' AND NVL(t1.'||p_item_col_name||','||''''||NULL_CHAR||''''||') '
469 ||' <> '||''''||NULL_CHAR||''''
470 ||' AND sr_instance_code = :p_instance_code'
471 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
472 ||' AND process_flag = ' ||G_IN_PROCESS;
473
474
475 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
476
477 EXECUTE IMMEDIATE lv_sql_stmt
478 USING p_instance_code,
479 p_batch_id;
480
481 END IF;
482 RETURN(lv_status);
483
484 EXCEPTION
485 WHEN too_many_rows THEN
486 p_error_text := substr('MSC_ST_UTIL.DERIVE_ITEM_ID'||'('
487 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
488 return(SQLCODE);
489
490 WHEN OTHERS THEN
491 p_error_text := substr('MSC_ST_UTIL.DERIVE_ITEM_ID'||'('
492 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
493 return(SQLCODE);
494 END DERIVE_ITEM_ID;
495
496
500 | partner name from the trading partner's table. |
497 /*===========================================================================+
498 | DESCRIPTION : This function is called for deriving the organization/ |
499 | supplier/customer id's based on the organization code/ |
501 | sr_instance_code (and Task number for task_id's)from the |
502 | local_id table. |
503 | p_table_name - Name of the table for whose column the id derived. |
504 | (eg., msc_st_supplies). |
505 | p_org_partner_name - Name of the column whose id is derived. |
506 | (eg., supplier_name/organization_code) |
507 | p_org_partner_id - Name of the column which stores the id. |
508 | (eg., supplier_id/organization_id) |
509 | p_instance_code - Instance_code column name |
510 | p_partner_type - Partner Type as stored in the msc_trading_partners |
511 | 1-customer, 2-Supplier and 3-Organization |
512 | p_message_text - Pre-defined error text. |
513 | p_severity - Severity fo the error(1.Warning and 2. Error |
514 | p_error_text - This communicates the error message to the calling |
515 | function, if any. |
516 | p_row - Concatenated column names of the table. This is |
517 | used for error logging in case of child tables. |
518 +==========================================================================*/
519 FUNCTION DERIVE_PARTNER_ORG_ID
520 (p_table_name VARCHAR2,
521 p_org_partner_name VARCHAR2,
522 p_cust_account_number VARCHAR2 DEFAULT 0,
523 p_org_partner_id VARCHAR2,
524 p_instance_code VARCHAR2,
525 p_partner_type NUMBER,
526 p_error_text OUT NOCOPY VARCHAR2,
527 p_batch_id NUMBER DEFAULT NULL_VALUE,
528 p_severity NUMBER DEFAULT 0,
529 p_message_text VARCHAR2 DEFAULT NULL,
530 p_debug BOOLEAN DEFAULT FALSE,
531 p_row LONG DEFAULT NULL,
532 p_where_str VARCHAR2 DEFAULT NULL,
533 p_company_name_col BOOLEAN DEFAULT TRUE )
534 RETURN NUMBER IS
535
536 lv_sql_stmt VARCHAR2(5000);
537 lv_where_str VARCHAR2(100) ;
538 lv_where_str1 VARCHAR2(100) := NULL;
539 lv_where_str2 VARCHAR2(100) := NULL;
540 lv_status NUMBER := 0;
541 BEGIN
542 v_sql_stmt := 09;
543 IF (p_table_name IN ('MSC_ST_TRADING_PARTNERS','MSC_ST_TRADING_PARTNER_SITES',
544 'MSC_ST_LOCATION_ASSOCIATIONS','MSC_ST_PARTNER_CONTACTS')) THEN
545 lv_where_str1 := ' AND PARTNER_TYPE = '||p_partner_type || p_where_str;
546 ELSIF (p_table_name = 'MSC_ST_CALENDAR_ASSIGNMENTS' and p_partner_type in (G_VENDOR, G_CUSTOMER)) THEN
547 lv_where_str1 := ' AND PARTNER_TYPE = '||p_partner_type || p_where_str;
548 END IF ;
549
550 IF (p_table_name IN ('MSC_ST_ITEM_SUBSTITUTES')) THEN
551 lv_where_str1 := lv_where_str1 || p_where_str;
552 END IF;
553
554 IF p_partner_type = 2 THEN -- customer
555 IF p_table_name IN ('MSC_ST_TRADING_PARTNERS','MSC_ST_TRADING_PARTNER_SITES') then
556 lv_where_str2:= ' AND char4 = '||p_cust_account_number;
557 ELSE
558 lv_where_str2:= ' AND ROWNUM = 1 ';
559 END IF;
560 END IF;
561
562 IF v_instance_type = G_INS_OTHER THEN
563 IF p_company_name_col THEN
564 lv_sql_stmt :=
565 'UPDATE '||p_table_name
566 ||' SET '||p_org_partner_id
567 ||' = (SELECT local_id'
568 ||' FROM msc_local_id_setup '
569 ||' WHERE char1 = sr_instance_code'
570 ||' AND NVL(char2, '||''''||NULL_CHAR||''''||')='
571 ||' NVL(company_name,'||''''||NULL_CHAR||''''||')'
572 ||' AND char3 = '||p_org_partner_name
573 ||' AND number1 = '||p_partner_type
574 ||lv_where_str2
575 ||' AND entity_name = ''SR_TP_ID'' )'
576 ||' WHERE sr_instance_code = :p_instance_code'
577 ||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
578 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
579 ||' AND process_flag = '||G_IN_PROCESS
580 || lv_where_str1;
581 ELSE
582 lv_sql_stmt :=
583 'UPDATE '||p_table_name
584 ||' SET '||p_org_partner_id
585 ||' = (SELECT local_id'
586 ||' FROM msc_local_id_setup '
587 ||' WHERE char1 = sr_instance_code'
588 ||' AND char3 = '||p_org_partner_name
589 ||' AND number1 = '||p_partner_type
590 ||lv_where_str2
591 ||' AND entity_name = ''SR_TP_ID'' )'
592 ||' WHERE sr_instance_code = :p_instance_code'
593 ||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
594 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
595 ||' AND process_flag = '||G_IN_PROCESS
596 || lv_where_str1;
597 END IF;
598
599 ELSE
600
601 IF p_partner_type = G_ORGANIZATION THEN
602 lv_sql_stmt :=
603 'UPDATE '||p_table_name ||' t1'
604 ||' SET '||p_org_partner_id
605 ||' = (SELECT sr_tp_id'
606 ||' FROM msc_trading_partners mtp'
607 ||' WHERE mtp.partner_type = '||p_partner_type
608 ||' AND mtp.organization_code = '||''''||p_instance_code||''''||'||'':''||'||'t1.'||p_org_partner_name
609 ||' AND mtp.sr_instance_id = '||v_instance_id||')'
613 ||' AND process_flag = '||G_IN_PROCESS
610 ||' WHERE sr_instance_code = :p_instance_code'
611 ||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
612 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
614 || lv_where_str1;
615
616 ELSE
617 lv_sql_stmt :=
618 'UPDATE '||p_table_name ||' t1'
619 ||' SET '||p_org_partner_id
620 ||' = (SELECT max(mtil.sr_tp_id)'
621 ||' FROM msc_tp_id_lid mtil, msc_trading_partners mtp'
622 ||' WHERE mtil.partner_type = '||p_partner_type
623 ||' AND mtil.sr_instance_id = '||v_instance_id
624 ||' AND mtil.tp_id = mtp.partner_id'
625 ||' AND mtp.partner_name = t1.'||p_org_partner_name
626 ||' AND mtp.partner_type = '||p_partner_type||')'
627 ||' WHERE sr_instance_code = :p_instance_code'
628 ||' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
629 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
630 ||' AND process_flag = '||G_IN_PROCESS
631 || lv_where_str1;
632
633 END IF;
634 END IF;
635
636
637 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
638
639 EXECUTE IMMEDIATE lv_sql_stmt
640 USING p_instance_code,
641 p_batch_id;
642
643
644 IF p_message_text IS NOT NULL and p_severity = 1 THEN
645 v_sql_stmt := 10;
646 lv_sql_stmt :=
647 'UPDATE '||p_table_name
648 ||' SET error_text = '||''''||p_message_text||''''||','
649 ||' process_flag = '||g_error
650 ||' WHERE NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
651 ||' AND sr_instance_code = :p_instance_code'
652 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
653 ||' AND process_flag = '||G_IN_PROCESS
654 || lv_where_str1;
655
656
657 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
658
659 EXECUTE IMMEDIATE lv_sql_stmt
660 USING p_instance_code,
661 p_batch_id;
662
663 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
664
665 lv_where_str :=
666 ' AND NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
667 || lv_where_str1;
668
669 lv_status :=
670 LOG_ERROR(p_table_name => p_table_name,
671 p_instance_code => p_instance_code,
672 p_row => p_row,
673 p_severity => p_severity,
674 p_propagated => 'N',
675 p_where_str => lv_where_str,
676 p_message_text => p_message_text,
677 p_error_text => p_error_text,
678 p_batch_id => p_batch_id);
679
680 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
681
682 v_sql_stmt := 11;
683 lv_sql_stmt :=
684 'UPDATE '||p_table_name
685 ||' SET error_text = '||''''||p_message_text||''''||','
686 ||' process_flag = '||G_ERROR
687 ||' WHERE NVL('||p_org_partner_id||','||NULL_VALUE||') = '||NULL_VALUE
688 ||' AND NVL('||p_org_partner_name||','||''''||NULL_CHAR||''''||')'
689 ||' <> '||''''||NULL_CHAR||''''
690 ||' AND sr_instance_code = :p_instance_code'
691 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
692 ||' AND process_flag = '||G_IN_PROCESS
693 || lv_where_str1;
694
695
696 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
697
698 EXECUTE IMMEDIATE lv_sql_stmt
699 USING p_instance_code,
700 p_batch_id;
701
702 END IF;
703 RETURN(lv_status);
704 EXCEPTION
705 WHEN too_many_rows THEN
706 p_error_text := substr('MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID'||'('
707 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
708 return(SQLCODE);
709
710 WHEN OTHERS THEN
711 p_error_text := substr('MSC_ST_UTIL.DERIVE_PARTNER_ORG_ID'||'('
712 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
713 return(SQLCODE);
714
715 END DERIVE_PARTNER_ORG_ID;
716
717 /*==========================================================================+
718 | DESCRIPTION : This function derives the value of the partner location id |
719 | from the trading partner sites table. |
720 +==========================================================================*/
721 FUNCTION DERIVE_PARTNER_SITE_ID
722 (p_table_name VARCHAR2,
723 p_partner_name VARCHAR2,
724 p_partner_site_code VARCHAR2,
725 p_CUST_ACCOUNT_NUMBER VARCHAR2 DEFAULT '0',
726 p_partner_site_id VARCHAR2,
727 p_instance_code VARCHAR2,
728 p_partner_type VARCHAR2,
729 p_error_text OUT NOCOPY VARCHAR2,
730 p_batch_id NUMBER DEFAULT NULL_VALUE,
731 p_severity NUMBER DEFAULT 0,
732 p_message_text VARCHAR2 DEFAULT NULL,
733 p_debug BOOLEAN DEFAULT FALSE,
734 p_row LONG DEFAULT NULL,
735 p_where_str VARCHAR2 DEFAULT NULL)
736 RETURN NUMBER IS
737 lv_sql_stmt VARCHAR2(5000);
738 lv_where_str VARCHAR2(100);
739 lv_where_str1 VARCHAR2(100) := NULL ;
740 lv_where_str2 VARCHAR2(100) := NULL ;
741 lv_where_str3 VARCHAR2(100) := NULL ;
742 lv_status NUMBER := 0;
743 BEGIN
744
748 END IF ;
745 IF (p_table_name IN ('MSC_ST_TRADING_PARTNER_SITES','MSC_ST_LOCATION_ASSOCIATIONS',
746 'MSC_ST_PARTNER_CONTACTS', 'MSC_ST_CALENDAR_ASSIGNMENTS')) THEN
747 lv_where_str1 := ' AND PARTNER_TYPE = '||p_partner_type;
749 IF (p_table_name IN ('MSC_ST_ITEM_SUBSTITUTES')) THEN
750 lv_where_str1 := lv_where_str1 ||p_where_str;
751 END IF ;
752
753 IF p_table_name in ('MSC_ST_SALES_ORDERS', 'MSC_ST_DESIGNATORS','MSC_ST_DEMANDS') THEN
754 IF (p_partner_site_code = 'SHIP_TO_SITE_CODE' AND p_partner_type = 2) THEN
755 lv_where_str2 := ' AND mtps.tp_site_code = ''SHIP_TO'' AND rownum =1)' ;
756 ELSIF (p_partner_site_code IN ('BILL_TO_SITE_CODE', 'BILL_CODE') AND p_partner_type = 2) THEN
757 lv_where_str2 := ' AND mtps.tp_site_code = ''BILL_TO'' AND rownum =1)';
758 ELSE
759 lv_where_str2 := ' AND rownum =1)';
760 END IF;
761 ELSE
762 lv_where_str2 := ' AND rownum =1)';
763 END IF;
764
765 v_sql_stmt := 12;
766
767 IF p_partner_type = 2 THEN -- customer
768 IF p_table_name IN ('MSC_ST_TRADING_PARTNER_SITES') THEN
769 lv_where_str3:= ' AND char5 = '||p_CUST_ACCOUNT_NUMBER;
770 ELSE
771 lv_where_str3:= ' AND ROWNUM =1 '; -- location is not considered.
772 END IF;
773 END IF;
774
775 IF v_instance_type = G_INS_OTHER THEN
776 lv_sql_stmt :=
777 'UPDATE '||p_table_name
778 ||' SET '||p_partner_site_id
779 ||' = (SELECT local_id'
780 ||' FROM msc_local_id_setup '
781 ||' WHERE char1 = sr_instance_code'
782 ||' AND NVL(char2, '||''''||NULL_CHAR||''''||')='
783 ||' NVL(company_name,'||''''||NULL_CHAR||''''||')'
784 ||' AND char3 = '||p_partner_name
785 ||' AND char4 = '||p_partner_site_code
786 ||' AND number1 = '||p_partner_type
787 ||lv_where_str3
788 ||' AND entity_name = ''SR_TP_SITE_ID'' )'
789 ||' WHERE sr_instance_code = :p_instance_code'
790 ||' AND NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
791 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
792 ||' AND process_flag = '||G_IN_PROCESS
793 || lv_where_str1;
794
795 ELSE
796 lv_sql_stmt :=
797 'UPDATE '||p_table_name ||' t1'
798 ||' SET '||p_partner_site_id
799 ||' = (SELECT mtsil.sr_tp_site_id'
800 ||' FROM msc_tp_site_id_lid mtsil, msc_trading_partner_sites mtps, msc_trading_partners mtp'
801 ||' WHERE mtsil.partner_type = '||p_partner_type
802 ||' AND mtsil.sr_instance_id = '||v_instance_id
803 ||' AND mtsil.tp_site_id = mtps.partner_site_id'
804 ||' AND mtps.partner_id = mtp.partner_id'
805 ||' AND mtp.partner_type = '||p_partner_type
806 ||' AND mtp.partner_name = t1.'||p_partner_name
807 ||' AND mtps.partner_type = '||p_partner_type;
808
809 IF p_partner_type = 2 THEN
810 lv_sql_stmt := lv_sql_stmt
811 ||' AND mtps.location = t1.'||p_partner_site_code
812 || lv_where_str2
813 ||' WHERE sr_instance_code = :p_instance_code'
814 ||' AND NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
815 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
816 ||' AND process_flag = '||G_IN_PROCESS
817 || lv_where_str1;
818
819 ELSE
820 lv_sql_stmt := lv_sql_stmt
821 ||' AND mtps.tp_site_code = t1.'||p_partner_site_code
822 ||' AND rownum =1)'
823 ||' WHERE sr_instance_code = :p_instance_code'
824 ||' AND NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
825 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
826 ||' AND process_flag = '||G_IN_PROCESS
827 || lv_where_str1;
828 END IF;
829
830 END IF;
831
832 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
833 EXECUTE IMMEDIATE lv_sql_stmt
834 USING p_instance_code,
835 p_batch_id;
836
837
838 IF p_message_text IS NOT NULL and p_severity = 1 THEN
839 v_sql_stmt := 13;
840 lv_sql_stmt :=
841 'UPDATE '||p_table_name
842 ||' SET error_text = '||''''||p_message_text||''''||','
843 ||' process_flag = '||g_error
844 ||' WHERE NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
845 ||' AND sr_instance_code = :p_instance_code'
846 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
847 ||' AND process_flag = '||G_IN_PROCESS
848 || lv_where_str1 ;
849
850 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
851 EXECUTE IMMEDIATE lv_sql_stmt
852 USING p_instance_code,
853 p_batch_id;
854
855 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
856
857 lv_where_str :=
858 ' AND NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
859 || lv_where_str1 ;
860
861 lv_status :=
862 LOG_ERROR(p_table_name => p_table_name,
863 p_instance_code => p_instance_code,
864 p_row => p_row,
865 p_severity => p_severity,
866 p_propagated => 'N',
867 p_where_str => lv_where_str,
868 p_message_text => p_message_text,
869 p_error_text => p_error_text,
870 p_batch_id => p_batch_id);
871
872 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
873
874 v_sql_stmt := 14;
875 lv_sql_stmt :=
876 'UPDATE '||p_table_name
880 ||' AND NVL('||p_partner_site_code||','||''''||NULL_CHAR||''''||') '
877 ||' SET error_text = '||''''||p_message_text||''''||','
878 ||' process_flag = '||G_ERROR
879 ||' WHERE NVL('||p_partner_site_id||','||NULL_VALUE||') = '||NULL_VALUE
881 ||' <> '||''''||NULL_CHAR||''''
882 ||' AND sr_instance_code = :p_instance_code'
883 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
884 ||' AND process_flag = '||G_IN_PROCESS
885 || lv_where_str1;
886
887 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
888 EXECUTE IMMEDIATE lv_sql_stmt
889 USING p_instance_code,
890 p_batch_id;
891 END IF;
892 RETURN(lv_status);
893
894 EXCEPTION
895 WHEN too_many_rows THEN
896 p_error_text := substr('MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID'||'('
897 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
898 return(SQLCODE);
899
900 WHEN OTHERS THEN
901 p_error_text := substr('MSC_ST_UTIL.DERIVE_PARTNER_SITE_ID'||'('
902 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
903 return(SQLCODE);
904
905 END DERIVE_PARTNER_SITE_ID;
906
907 /*==========================================================================+
908 | DESCRIPTION : This function is called for deriving the project/Task ids |
909 | based on the organization name, project number/name, |
910 | sr_instance_code (and Task number for task_id's)from the |
911 | local_id table. |
912 | p_table_name - Name of the table for whose column the id derived. |
913 | (eg., msc_st_supplies). |
914 | p_proj_col_name - Name of the column whose id is derived. |
915 | (eg., project name) |
916 | p_task_col_name - Name of the column whose id is derived. |
917 | (eg., task number) |
918 | p_proj_task_col_id - Name of the column which stores the id. |
919 | (eg., project_id/task_id) |
920 | p_instance_code - Current instance_code |
921 | p_entity_name - Name of the entity as stored in the msc_local_id |
922 | table.(eg., "PROJECT_ID") |
923 | p_message_text - Pre-defined error text. |
924 | p_error_text - This communicates the error message to the calling |
925 | function, if any. |
926 | p_row - Concatenated column names of the table. This is |
927 | used for error logging in case of child tables. |
928 +==========================================================================*/
929 FUNCTION DERIVE_PROJ_TASK_ID
930 (p_table_name VARCHAR2,
931 p_proj_col_name VARCHAR2,
932 p_proj_task_col_id VARCHAR2,
933 p_instance_code VARCHAR2,
934 p_entity_name VARCHAR2,
935 p_error_text OUT NOCOPY VARCHAR2,
936 p_task_col_name VARCHAR2 DEFAULT NULL,
937 p_batch_id NUMBER DEFAULT NULL_VALUE,
938 p_severity NUMBER DEFAULT 0,
939 p_message_text VARCHAR2 DEFAULT NULL,
940 p_debug BOOLEAN DEFAULT FALSE,
941 p_row LONG DEFAULT NULL)
942 RETURN NUMBER IS
943
944 lv_sql_stmt VARCHAR2(5000);
945 lv_where_str VARCHAR2(100);
946 lv_status NUMBER:=0;
947 BEGIN
948 v_sql_stmt := 15;
949
950 IF v_instance_type = G_INS_OTHER THEN
951 lv_sql_stmt :=
952 'UPDATE '||p_table_name ||' t1'
953 ||' SET '||p_proj_task_col_id
954 ||' = (SELECT local_id'
955 ||' FROM msc_local_id_misc t2'
956 ||' WHERE t2.char1 = t1.sr_instance_code '
957 ||' AND NVL(t2.char2, '||''''||NULL_CHAR||''''||')='
958 ||' NVL(t1.company_name,'||''''||NULL_CHAR||''''||')'
959 /* ||' AND t2.char3 = t1.organization_code '*/
960 ||' AND t2.char4 = t1.'||p_proj_col_name;
961
962 IF p_task_col_name IS NULL THEN
963 lv_sql_stmt := lv_sql_stmt
964 ||' AND t2.entity_name = '||''''||p_entity_name||''''||')'
965 ||' WHERE sr_instance_code = :p_instance_code'
966 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
967 ||' AND process_flag = '||G_IN_PROCESS;
968 ELSE
969 lv_sql_stmt := lv_sql_stmt
970 ||' AND t2.char5 = t1.'||p_task_col_name
971 ||' AND t2.entity_name = '||''''||p_entity_name||''''||')'
972 ||' WHERE sr_instance_code = :p_instance_code'
973 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
974 ||' AND process_flag = '||G_IN_PROCESS;
975 END IF;
976
977 ELSE
978
979 IF p_task_col_name IS NULL THEN
980 lv_sql_stmt :=
981 'UPDATE '||p_table_name ||' t1'
982 ||' SET '||p_proj_task_col_id
983 ||' = (SELECT project_id'
984 ||' FROM msc_projects t2'
985 ||' WHERE t2.project_number = t1.'||p_proj_col_name
986 ||' AND t2.sr_instance_id ='||v_instance_id
987 ||' AND t2.plan_id = -1 '
988 ||' AND t2.organization_id = t1.organization_id)'
989 ||' WHERE sr_instance_code = :p_instance_code'
990 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
991 ||' AND process_flag = '||G_IN_PROCESS;
992
993 ELSE
994 lv_sql_stmt :=
995 'UPDATE '||p_table_name ||' t1'
996 ||' SET '||p_proj_task_col_id
1000 ||' AND t2.sr_instance_id = '||v_instance_id
997 ||' = (SELECT task_id'
998 ||' FROM msc_project_tasks t2'
999 ||' WHERE t2.project_id = t1.project_id '
1001 ||' AND t2.plan_id = -1 '
1002 ||' AND t2.task_number = t1.'||p_task_col_name
1003 ||' AND t2.organization_id = t1.organization_id)'
1004 ||' WHERE sr_instance_code = :p_instance_code'
1005 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
1006 ||' AND process_flag = '||G_IN_PROCESS;
1007
1008 END IF;
1009
1010 END IF;
1011
1012 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1013 EXECUTE IMMEDIATE lv_sql_stmt
1014 USING p_instance_code,
1015 p_batch_id;
1016
1017 IF p_message_text IS NOT NULL AND p_severity = 1 THEN
1018 v_sql_stmt := 16;
1019 lv_sql_stmt :=
1020 'UPDATE '||p_table_name
1021 ||' SET error_text = '||''''||p_message_text||''''||','
1022 ||' process_flag = '||G_ERROR
1023 ||' WHERE NVL('||p_proj_task_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1024 ||' AND sr_instance_code = :p_instance_code'
1025 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1026 ||' AND process_flag = '||G_IN_PROCESS;
1027
1028 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1029 EXECUTE IMMEDIATE lv_sql_stmt
1030 USING p_instance_code,
1031 p_batch_id;
1032
1033 ELSIF p_message_text IS NOT NULL AND p_severity = 2 THEN
1034 --Log a warning for those records where the project name/task name
1035 --exists and the derivation has failed.
1036
1037 IF p_proj_col_name IS NULL THEN
1038 lv_where_str :=
1039 ' AND NVL('||p_proj_task_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1040 ||' AND NVL('||p_task_col_name||','||''''||NULL_CHAR||''''||')'
1041 ||' <>'||''''||NULL_CHAR||'''';
1042 ELSE
1043 lv_where_str :=
1044 ' AND NVL('||p_proj_task_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1045 ||' AND NVL('||p_proj_col_name||','||''''||NULL_CHAR||''''||')'
1046 ||' <>'||''''||NULL_CHAR||'''';
1047 END IF;
1048
1049 lv_status := LOG_ERROR(p_table_name => p_table_name,
1050 p_instance_code => p_instance_code,
1051 p_row => p_row,
1052 p_severity => p_severity,
1053 p_propagated => 'N',
1054 p_where_str => lv_where_str,
1055 p_message_text => p_message_text,
1056 p_error_text => p_error_text,
1057 p_batch_id => p_batch_id);
1058
1059 END IF;
1060 RETURN(lv_status);
1061 EXCEPTION
1062 WHEN too_many_rows THEN
1063 p_error_text := substr('MSC_ST_UTIL.DERIVE_PROJ_TASK_ID'||'('
1064 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1065 return(SQLCODE);
1066
1067 WHEN OTHERS THEN
1068 p_error_text := substr('MSC_ST_UTIL.DERIVE_PROJ_TASK_ID'||'('
1069 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1070 return(SQLCODE);
1071
1072 END DERIVE_PROJ_TASK_ID;
1073
1074 /*==========================================================================+
1075 | DESCRIPTION : This function is called for validating UOMs based on |
1076 | the values in ODS/Staging |
1077 | Severity - 1 - Error |
1078 | 3 - Error if value for UOM code exists |
1079 +==========================================================================*/
1080 FUNCTION VALIDATE_UOM
1081 (p_table_name VARCHAR2,
1082 p_uom_col_name VARCHAR2, --uom_code
1083 p_instance_id NUMBER,
1084 p_instance_code VARCHAR2,
1085 p_error_text OUT NOCOPY VARCHAR2,
1086 p_batch_id NUMBER DEFAULT NULL_VALUE,
1087 p_severity NUMBER DEFAULT 0,
1088 p_message_text VARCHAR2 DEFAULT NULL,
1089 p_debug BOOLEAN DEFAULT FALSE,
1090 p_row LONG DEFAULT NULL)
1091 RETURN NUMBER IS
1092 lv_sql_stmt VARCHAR2(5000);
1093 lv_where_str VARCHAR2(100);
1094 lv_status NUMBER:=0;
1095
1096 BEGIN
1097
1098 IF p_message_text IS NOT NULL and p_severity = 1 THEN
1099
1100 NULL;
1101
1102 v_sql_stmt := 17;
1103 lv_sql_stmt :=
1104 'UPDATE '||p_table_name ||' t1'
1105 ||' SET error_text = '||''''||p_message_text||''''||','
1106 ||' process_flag = '||g_error
1107 ||' WHERE NOT EXISTS (SELECT 1'
1108 ||' FROM msc_units_of_measure muom'
1109 ||' WHERE muom.uom_code = t1.'||p_uom_col_name
1110 ||' UNION'
1111 ||' SELECT 1 FROM msc_st_units_of_measure msuom'
1112 ||' WHERE msuom.uom_code = t1.'||p_uom_col_name
1113 ||' AND msuom.sr_instance_id = :v_instance_id'
1114 ||' AND msuom.process_flag = '||G_VALID||' )'
1115 ||' AND sr_instance_code = :p_instance_code'
1116 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1117 ||' AND process_flag = ' ||G_IN_PROCESS;
1118
1119 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1120 EXECUTE IMMEDIATE lv_sql_stmt
1121 USING p_instance_id,
1122 p_instance_code,
1123 p_batch_id;
1124
1125
1126 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
1127
1128 v_sql_stmt := 18;
1132 ||' process_flag = '||g_error
1129 lv_sql_stmt :=
1130 'UPDATE '||p_table_name ||' t1'
1131 ||' SET error_text = '||''''||p_message_text||''''||','
1133 ||' WHERE NOT EXISTS (SELECT 1'
1134 ||' FROM msc_units_of_measure muom'
1135 ||' WHERE muom.uom_code = t1.'||p_uom_col_name
1136 ||' UNION'
1137 ||' SELECT 1 FROM msc_st_units_of_measure msuom'
1138 ||' WHERE msuom.uom_code = t1.'||p_uom_col_name
1139 ||' AND msuom.sr_instance_id = :v_instance_id'
1140 ||' AND msuom.process_flag = '||G_VALID||' )'
1141 ||' AND NVL(t1.'||p_uom_col_name||','||''''||NULL_CHAR||''''||') '
1142 ||' <> '||''''||NULL_CHAR||''''
1143 ||' AND sr_instance_code = :p_instance_code'
1144 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1145 ||' AND process_flag = ' ||G_IN_PROCESS;
1146
1147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1148 EXECUTE IMMEDIATE lv_sql_stmt
1149 USING p_instance_id,
1150 p_instance_code,
1151 p_batch_id;
1152
1153 END IF;
1154 RETURN(lv_status);
1155
1156 EXCEPTION
1157 WHEN too_many_rows THEN
1158 p_error_text := substr('MSC_ST_UTIL.VALIDATE_UOM'||'('
1159 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1160 return(SQLCODE);
1161
1162 WHEN OTHERS THEN
1163 p_error_text := substr('MSC_ST_UTIL.VALIDATE_UOM'||'('
1164 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1165 return(SQLCODE);
1166 END VALIDATE_UOM ;
1167
1168 /*==========================================================================+
1169 | DESCRIPTION : This function is called for validating Demand classbased on|
1170 | the values in ODS/Staging |
1171 | Severity - 1 - Error |
1172 | 3 - Error if value for demand class exists |
1173 +==========================================================================*/
1174 FUNCTION VALIDATE_DMD_CLASS
1175 (p_table_name VARCHAR2,
1176 p_dmd_class_column VARCHAR2, -- demand class column name
1177 p_instance_id NUMBER,
1178 p_instance_code VARCHAR2,
1179 p_error_text OUT NOCOPY VARCHAR2,
1180 p_batch_id NUMBER DEFAULT NULL_VALUE,
1181 p_severity NUMBER DEFAULT 0,
1182 p_message_text VARCHAR2 DEFAULT NULL,
1183 p_debug BOOLEAN DEFAULT FALSE,
1184 p_row LONG DEFAULT NULL)
1185 RETURN NUMBER IS
1186 lv_sql_stmt VARCHAR2(5000);
1187 lv_where_str VARCHAR2(100);
1188 lv_status NUMBER:=0;
1189
1190 BEGIN
1191
1192 IF p_message_text IS NOT NULL and p_severity = 1 THEN
1193
1194 v_sql_stmt :=01;
1195 lv_sql_stmt :=
1196 'UPDATE '||p_table_name ||' t1'
1197 ||' SET error_text = '||''''||p_message_text||''''||','
1198 ||' process_flag = '||g_error
1199 ||' WHERE NOT EXISTS (SELECT 1'
1200 ||' FROM msc_demand_classes mdc'
1201 ||' WHERE mdc.demand_class = t1.'||p_dmd_class_column
1202 ||' AND mdc.sr_instance_id = :v_instance_id'
1203 ||' UNION'
1204 ||' SELECT 1 FROM msc_st_demand_classes mstd'
1205 ||' WHERE mstd.demand_class = t1.'||p_dmd_class_column
1206 ||' AND mstd.sr_instance_id = :v_instance_id'
1207 ||' AND mstd.process_flag = '||G_VALID||' )'
1208 ||' AND sr_instance_code = :p_instance_code'
1209 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1210 ||' AND process_flag = ' ||G_IN_PROCESS;
1211
1212 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1213 EXECUTE IMMEDIATE lv_sql_stmt
1214 USING p_instance_id,
1215 p_instance_id,
1216 p_instance_code,
1217 p_batch_id;
1218
1219
1220 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
1221
1222 v_sql_stmt := 02;
1223 lv_sql_stmt :=
1224 'UPDATE '||p_table_name ||' t1'
1225 ||' SET error_text = '||''''||p_message_text||''''||','
1226 ||' process_flag = '||g_error
1227 ||' WHERE NOT EXISTS (SELECT 1'
1228 ||' FROM msc_demand_classes mdc'
1229 ||' WHERE mdc.demand_class = t1.'||p_dmd_class_column
1230 ||' AND mdc.sr_instance_id = :v_instance_id'
1231 ||' UNION'
1232 ||' SELECT 1 FROM msc_st_demand_classes mstd'
1233 ||' WHERE mstd.demand_class = t1.'||p_dmd_class_column
1234 ||' AND mstd.sr_instance_id = :v_instance_id'
1235 ||' AND mstd.process_flag = '||G_VALID||' )'
1236 ||' AND NVL(t1.'||p_dmd_class_column||','||''''||NULL_CHAR||''''||') '
1237 ||' <> '||''''||NULL_CHAR||''''
1238 ||' AND sr_instance_code = :p_instance_code'
1239 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1240 ||' AND process_flag = ' ||G_IN_PROCESS;
1241
1242 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1243 EXECUTE IMMEDIATE lv_sql_stmt
1244 USING p_instance_id,
1245 p_instance_id,
1246 p_instance_code,
1250 EXCEPTION
1247 p_batch_id;
1248 END IF;
1249 RETURN(lv_status);
1251 WHEN OTHERS THEN
1252 p_error_text := substr('MSC_ST_UTIL.VALIDATE_DMD_CLASS'||'('
1253 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1254 return(SQLCODE);
1255 END VALIDATE_DMD_CLASS ;
1256
1257
1258 /*==========================================================================+
1259 | DESCRIPTION : This function sets the token value and returns the error |
1260 | message text. |
1261 +==========================================================================*/
1262 FUNCTION GET_ERROR_MESSAGE
1263 (p_app_short_name VARCHAR2,
1264 p_error_code VARCHAR2,
1265 p_message_text OUT NOCOPY VARCHAR2,
1266 p_error_text OUT NOCOPY VARCHAR2,
1267 p_token1 VARCHAR2 DEFAULT NULL,
1268 p_token_value1 VARCHAR2 DEFAULT NULL,
1269 p_token2 VARCHAR2 DEFAULT NULL,
1270 p_token_value2 VARCHAR2 DEFAULT NULL,
1271 p_token3 VARCHAR2 DEFAULT NULL,
1272 p_token_value3 VARCHAR2 DEFAULT NULL,
1273 p_token4 VARCHAR2 DEFAULT NULL,
1274 p_token_value4 VARCHAR2 DEFAULT NULL,
1275 p_token5 VARCHAR2 DEFAULT NULL,
1276 p_token_value5 VARCHAR2 DEFAULT NULL)
1277 RETURN NUMBER IS
1278 BEGIN
1279
1280 FND_MESSAGE.SET_NAME(p_app_short_name,p_error_code);
1281
1282 IF p_token1 IS NOT NULL THEN
1283 FND_MESSAGE.SET_TOKEN(p_token1,p_token_value1);
1284 END IF;
1285
1286 IF p_token2 IS NOT NULL THEN
1287 FND_MESSAGE.SET_TOKEN(p_token2,p_token_value2);
1288 END IF;
1289
1290 IF p_token3 IS NOT NULL THEN
1291 FND_MESSAGE.SET_TOKEN(p_token3,p_token_value3);
1292 END IF;
1293
1294 IF p_token4 IS NOT NULL THEN
1295 FND_MESSAGE.SET_TOKEN(p_token4,p_token_value4);
1296 END IF;
1297
1298 IF p_token5 IS NOT NULL THEN
1299 FND_MESSAGE.SET_TOKEN(p_token5,p_token_value5);
1300 END IF;
1301
1302 p_message_text := FND_MESSAGE.GET;
1303
1304 RETURN(0);
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 p_error_text := substr('MSC_ST_UTIL.GET_ERROR_MESSAGE'
1308 || SQLERRM, 1, 240);
1309 return(SQLCODE);
1310
1311 END GET_ERROR_MESSAGE;
1312
1313 /*==========================================================================+
1314 | DESCRIPTION : This function is called for deriving the bill_sequence_id |
1315 | based on the values in msc_local_id_setup. For the tables other |
1316 | than msc_st_boms, if the derivation fails then the |
1317 | record will be errored out. |
1318 | Severity - 1 - Error |
1319 | 2 - Warning |
1320 | 3 - Error if value for bom name exists |
1321 +==========================================================================*/
1322 FUNCTION DERIVE_BILL_SEQUENCE_ID
1323 (p_table_name VARCHAR2,
1324 p_bom_col_name VARCHAR2, --bom_name
1325 p_bom_col_id VARCHAR2, --bill_sequence_id
1326 p_instance_code VARCHAR2,
1327 p_error_text OUT NOCOPY VARCHAR2,
1328 p_batch_id NUMBER DEFAULT NULL_VALUE,
1329 p_severity NUMBER DEFAULT 0,
1330 p_message_text VARCHAR2 DEFAULT NULL,
1331 p_debug BOOLEAN DEFAULT FALSE,
1332 p_row LONG DEFAULT NULL)
1333 RETURN NUMBER IS
1334 lv_sql_stmt VARCHAR2(5000);
1335 lv_where_str VARCHAR2(100);
1336 lv_status NUMBER := 0;
1337 BEGIN
1338 v_sql_stmt := 01;
1339
1340 IF v_instance_type = G_INS_OTHER THEN
1341 lv_sql_stmt :=
1342 'UPDATE '||p_table_name ||' t1'
1343 ||' SET '||p_bom_col_id
1344 ||' = (SELECT local_id'
1345 ||' FROM msc_local_id_setup t2'
1346 ||' WHERE t2.char1 = t1.sr_instance_code'
1347 ||' AND NVL(t2.char2,'||''''||NULL_CHAR||''''||') '
1348 ||' = NVL(t1.company_name,'||''''||NULL_CHAR||''''||') '
1349 ||' AND t2.char3 = t1.organization_code'
1350 ||' AND NVL(t2.char6,'||''''||NULL_CHAR||''''||') '
1351 ||' = NVL( t1.alternate_bom_designator,'||''''||NULL_CHAR||''''||') '
1352 ||' AND t2.char4 = t1.'||p_bom_col_name
1353 ||' AND t2.entity_name = ''BILL_SEQUENCE_ID'')'
1354 ||' WHERE sr_instance_code = :p_instance_code'
1355 ||' AND batch_id = :p_batch_id'
1356 ||' AND process_flag = '||G_IN_PROCESS;
1357
1358 ELSE
1359
1360 lv_sql_stmt :=
1361 'UPDATE '||p_table_name ||' t1'
1362 ||' SET '||p_bom_col_id
1363 ||' = (SELECT bill_sequence_id'
1364 ||' FROM msc_boms t2, msc_item_id_lid mil'
1365 ||' WHERE t2.plan_id = -1'
1366 ||' AND t2.organization_id = t1.organization_id'
1367 ||' AND t2.sr_instance_id = '||v_instance_id
1368 ||' AND t2.assembly_item_id = mil.inventory_item_id'
1369 ||' AND mil.sr_inventory_item_id = t1.inventory_item_id'
1370 ||' AND mil.sr_instance_id = '||v_instance_id
1371 ||' AND NVL(t2.alternate_bom_designator,'||''''||NULL_CHAR||''''||') '
1372 ||' = NVL( t1.alternate_bom_designator,'||''''||NULL_CHAR||''''||')) '
1373 ||' WHERE sr_instance_code = :p_instance_code'
1374 ||' AND batch_id = :p_batch_id'
1378
1375 ||' AND process_flag = '||G_IN_PROCESS;
1376
1377 END IF;
1379 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1380 EXECUTE IMMEDIATE lv_sql_stmt
1381 USING p_instance_code,
1382 p_batch_id;
1383
1384
1385 IF p_message_text IS NOT NULL and p_severity = 1 THEN
1386 v_sql_stmt := 02;
1387 lv_sql_stmt :=
1388 'UPDATE '||p_table_name ||' t1'
1389 ||' SET error_text = '||''''||p_message_text||''''||','
1390 ||' process_flag = '||G_ERROR
1391 ||' WHERE NVL(t1.'||p_bom_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1392 ||' AND sr_instance_code = :p_instance_code'
1393 ||' AND batch_id = :p_batch_id'
1394 ||' AND process_flag = ' ||G_IN_PROCESS;
1395
1396 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1397 EXECUTE IMMEDIATE lv_sql_stmt
1398 USING p_instance_code,
1399 p_batch_id;
1400
1401 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
1402
1403 lv_where_str :=
1404 ' AND NVL('||p_bom_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
1405
1406 lv_status := LOG_ERROR(p_table_name => p_table_name,
1407 p_instance_code => p_instance_code,
1408 p_row => p_row,
1409 p_severity => p_severity,
1410 p_propagated => 'N',
1411 p_where_str => lv_where_str,
1412 p_error_text => p_error_text,
1413 p_message_text => p_message_text,
1414 p_batch_id => p_batch_id);
1415
1416 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
1417 v_sql_stmt := 03;
1418 lv_sql_stmt :=
1419 'UPDATE '||p_table_name ||' t1'
1420 ||' SET error_text = '||''''||p_message_text||''''||','
1421 ||' process_flag = '||G_ERROR
1422 ||' WHERE NVL(t1.'||p_bom_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1423 ||' AND NVL(t1.'||p_bom_col_name||','||''''||NULL_CHAR||''''||')'
1424 ||' <> '||''''||NULL_CHAR||''''
1425 ||' AND sr_instance_code = :p_instance_code'
1426 ||' AND batch_id = :p_batch_id'
1427 ||' AND process_flag = ' ||G_IN_PROCESS;
1428
1429 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1430 EXECUTE IMMEDIATE lv_sql_stmt
1431 USING p_instance_code,
1432 p_batch_id;
1433
1434 END IF;
1435 RETURN(lv_status);
1436
1437 EXCEPTION
1438 WHEN too_many_rows THEN
1439 p_error_text := substr('MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID'||'('
1440 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1441 return(SQLCODE);
1442
1443 WHEN OTHERS THEN
1444 p_error_text := substr('MSC_ST_UTIL.DERIVE_BILL_SEQUENCE_ID'||'('
1445 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1446 return(SQLCODE);
1447
1448 END DERIVE_BILL_SEQUENCE_ID;
1449
1450 /*==========================================================================+
1451 | DESCRIPTION : This function is called for deriving therouting_sequence_id|
1452 | the values in msc_local_id_setup. For the tables otherthan|
1453 | than msc_st_routings, if the derivation fails then the |
1454 | record will be errored out. |
1455 | Severity - 1 - Error |
1456 | 2 - Warning |
1457 | 3 - Error if value for bom name exists |
1458 +==========================================================================*/
1459 FUNCTION DERIVE_ROUTING_SEQUENCE_ID
1460 (p_table_name VARCHAR2,
1461 p_rtg_col_name VARCHAR2, --routing_name
1462 p_rtg_col_id VARCHAR2, --routing_sequence_id
1463 p_instance_code VARCHAR2,
1464 p_error_text OUT NOCOPY VARCHAR2,
1465 p_batch_id NUMBER DEFAULT NULL_VALUE,
1466 p_severity NUMBER DEFAULT 0,
1467 p_message_text VARCHAR2 DEFAULT NULL,
1468 p_debug BOOLEAN DEFAULT FALSE,
1469 p_row LONG DEFAULT NULL,
1470 p_where_str VARCHAR2 DEFAULT NULL,
1471 p_item_id VARCHAR2 DEFAULT 'inventory_item_id')
1472 RETURN NUMBER IS
1473
1474 lv_sql_stmt VARCHAR2(5000);
1475 lv_where_str VARCHAR2(100);
1476 lv_status NUMBER := 0;
1477 BEGIN
1478 v_sql_stmt := 01;
1479
1480 IF v_instance_type = G_INS_OTHER THEN
1481 lv_sql_stmt :=
1482 'UPDATE '||p_table_name ||' t1'
1483 ||' SET '||p_rtg_col_id
1484 ||' = (SELECT local_id'
1485 ||' FROM msc_local_id_setup t2'
1486 ||' WHERE t2.char1 = t1.sr_instance_code'
1487 ||' AND NVL(t2.char2,'||''''||NULL_CHAR||''''||') '
1488 ||' = NVL(t1.company_name,'||''''||NULL_CHAR||''''||') '
1489 ||' AND t2.char3 = t1.organization_code'
1490 ||' AND NVL(t2.char6,'||''''||NULL_CHAR||''''||') '
1491 ||' = NVL( t1.alternate_routing_designator,'||''''||NULL_CHAR||''''||') '
1492 ||' AND t2.char4 = t1.'||p_rtg_col_name
1493 ||' AND t2.entity_name = ''ROUTING_SEQUENCE_ID'')'
1494 ||' WHERE sr_instance_code = :p_instance_code'
1495 ||' AND batch_id = :p_batch_id'
1499 ELSE
1496 ||' AND process_flag = '||G_IN_PROCESS
1497 || p_where_str;
1498
1500
1501 lv_sql_stmt :=
1502 'UPDATE '||p_table_name ||' t1'
1503 ||' SET '||p_rtg_col_id
1504 ||' = (SELECT routing_sequence_id'
1505 ||' FROM msc_routings t2, msc_item_id_lid mil'
1506 ||' WHERE t2.plan_id = -1'
1507 ||' AND t2.organization_id = t1.organization_id'
1508 ||' AND t2.sr_instance_id = '||v_instance_id
1509 ||' AND t2.assembly_item_id = mil.inventory_item_id'
1510 ||' AND mil.sr_inventory_item_id = t1.'||p_item_id
1511 ||' AND mil.sr_instance_id = '||v_instance_id
1512 ||' AND NVL(t2.alternate_routing_designator,'||''''||NULL_CHAR||''''||') '
1513 ||' = NVL( t1.alternate_routing_designator,'||''''||NULL_CHAR||''''||')) '
1514 ||' WHERE sr_instance_code = :p_instance_code'
1515 ||' AND batch_id = :p_batch_id'
1516 ||' AND process_flag = '||G_IN_PROCESS
1517 || p_where_str;
1518
1519 END IF;
1520
1521 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1522 EXECUTE IMMEDIATE lv_sql_stmt
1523 USING p_instance_code,
1524 p_batch_id;
1525
1526
1527 IF p_message_text IS NOT NULL and p_severity = 1 THEN
1528 v_sql_stmt := 02;
1529 lv_sql_stmt :=
1530 'UPDATE '||p_table_name ||' t1'
1531 ||' SET error_text = '||''''||p_message_text||''''||','
1532 ||' process_flag = '||G_ERROR
1533 ||' WHERE NVL(t1.'||p_rtg_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1534 ||' AND sr_instance_code = :p_instance_code'
1535 ||' AND batch_id = :p_batch_id'
1536 ||' AND process_flag = ' ||G_IN_PROCESS;
1537
1538 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1539 EXECUTE IMMEDIATE lv_sql_stmt
1540 USING p_instance_code,
1541 p_batch_id;
1542
1543 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
1544
1545 lv_where_str :=
1546 ' AND NVL('||p_rtg_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
1547
1548 lv_status := LOG_ERROR(p_table_name => p_table_name,
1549 p_instance_code => p_instance_code,
1550 p_row => p_row,
1551 p_severity => p_severity,
1552 p_propagated => 'N',
1553 p_where_str => lv_where_str,
1554 p_message_text => p_message_text,
1555 p_error_text => p_error_text,
1556 p_batch_id => p_batch_id);
1557
1558 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
1559 v_sql_stmt := 03;
1560 lv_sql_stmt :=
1561 'UPDATE '||p_table_name ||' t1'
1562 ||' SET p_message_text = '||''''||p_message_text||''''||','
1563 ||' process_flag = '||g_error
1564 ||' WHERE NVL(t1.'||p_rtg_col_id||','||NULL_VALUE||') = '||NULL_VALUE
1565 ||' AND NVL(t1.'||p_rtg_col_name||', '||''''||NULL_CHAR||''''||')'
1566 ||' <> '||''''||NULL_CHAR||''''
1567 ||' AND sr_instance_code = :p_instance_code'
1568 ||' AND batch_id = :p_batch_id'
1569 ||' AND process_flag = ' ||G_IN_PROCESS;
1570
1571 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1572 EXECUTE IMMEDIATE lv_sql_stmt
1573 USING p_instance_code,
1574 p_batch_id;
1575 END IF;
1576 RETURN(lv_status);
1577
1578 EXCEPTION
1579 WHEN too_many_rows THEN
1580 p_error_text := substr('MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID'||'('
1581 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1582 return(SQLCODE);
1583
1584
1585 WHEN OTHERS THEN
1586 p_error_text := substr('MSC_ST_UTIL.DERIVE_ROUTING_SEQUENCE_ID'||'('
1587 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1588 return(SQLCODE);
1589
1590 END DERIVE_ROUTING_SEQUENCE_ID;
1591
1592 /*==========================================================================+
1593 | DESCRIPTION : This function sets the process flag vlaue to the valid/ |
1594 | In process status and the instnce_id value for these |
1595 | records. |
1596 +==========================================================================*/
1597 FUNCTION SET_PROCESS_FLAG
1598 (p_table_name VARCHAR2,
1599 p_instance_id NUMBER,
1600 p_instance_code VARCHAR2,
1601 p_process_flag NUMBER,
1602 p_error_text OUT NOCOPY VARCHAR2,
1603 p_where_str VARCHAR2 DEFAULT NULL,
1604 p_debug BOOLEAN DEFAULT FALSE,
1605 p_batch_id NUMBER DEFAULT NULL_VALUE,
1606 p_instance_id_col VARCHAR2 DEFAULT 'SR_INSTANCE_ID')
1607 RETURN NUMBER IS
1608 lv_sql_stmt VARCHAR2(5000);
1609 lv_where_str VARCHAR2(5000):=' ';
1610
1611 BEGIN
1612 --To handle calendar which does'nt have the batch_id column.
1613 IF p_batch_id <> NULL_VALUE THEN
1614 lv_where_str :=
1615 ' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'||p_where_str;
1616 ELSE
1617 lv_where_str :=p_where_str;
1618 END IF;
1619
1620 IF p_process_flag = G_VALID THEN
1621 v_sql_stmt := 17;
1622 lv_sql_stmt :=
1623 'UPDATE '||p_table_name
1624 ||' SET process_flag = '||G_VALID||','
1628 || lv_where_str;
1625 || p_instance_id_col||' = :p_instance_id'
1626 ||' WHERE process_flag = '||G_IN_PROCESS
1627 ||' AND sr_instance_code = :p_instance_code'
1629
1630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1631 IF p_batch_id = NULL_VALUE THEN
1632 EXECUTE IMMEDIATE lv_sql_stmt
1633 USING p_instance_id,
1634 p_instance_code;
1635 ELSE
1636 EXECUTE IMMEDIATE lv_sql_stmt
1637 USING p_instance_id,
1638 p_instance_code,
1639 p_batch_id;
1640 END IF;
1641
1642 ELSE
1643 v_sql_stmt := 18;
1644 lv_sql_stmt :=
1645 'UPDATE '||p_table_name
1646 ||' SET process_flag = ' ||G_IN_PROCESS ||','
1647 || p_instance_id_col||' = 0' ||','
1648 ||' WHERE nvl(process_flag,'||G_NEW ||') = '||G_NEW
1649 ||' AND sr_instance_code = :p_instance_code'
1650 || lv_where_str;
1651
1652 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1653 IF p_batch_id = NULL_VALUE THEN
1654 EXECUTE IMMEDIATE lv_sql_stmt
1655 USING p_instance_id,
1656 p_instance_code;
1657 ELSE
1658 EXECUTE IMMEDIATE lv_sql_stmt
1659 USING p_instance_id,
1660 p_instance_code,
1661 p_batch_id;
1662 END IF;
1663
1664 END IF;
1665
1666 RETURN(0);
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 p_error_text := substr('MSC_ST_UTIL.SET_PROCESS_FLAG'||'('||v_sql_stmt||')'
1670 || SQLERRM, 1, 240);
1671 return(SQLCODE);
1672
1673 END SET_PROCESS_FLAG;
1674
1675 -- DP specific Function and Procedure
1676 /*==========================================================================+
1677 | DESCRIPTION : This function is called for deriving level_id |
1678 | from the msd_levels table. |
1679 +==========================================================================*/
1680 FUNCTION DERIVE_LEVEL_ID
1681 (p_table_name VARCHAR2,
1682 p_level_name_col VARCHAR2, --level_name
1683 p_level_id_col VARCHAR2, --level_id
1684 p_severity NUMBER DEFAULT G_SEV_ERROR,
1685 p_message_text VARCHAR2 DEFAULT NULL,
1686 p_instance_code VARCHAR2,
1687 p_batch_id NUMBER DEFAULT NULL_VALUE,
1688 p_error_text OUT NOCOPY VARCHAR2)
1689 RETURN NUMBER IS
1690 lv_sql_stmt VARCHAR2(5000);
1691 lv_where_str VARCHAR2(100);
1692 lv_status NUMBER := 0;
1693 --lv_debug BOOLEAN := retn_debug_mode;
1694
1695 BEGIN
1696 v_sql_stmt := 01;
1697
1698 lv_sql_stmt :=
1699 ' UPDATE '||p_table_name||' t1'
1700 ||' SET '||p_level_id_col
1701 ||' = NVL((SELECT level_id'
1702 ||' FROM msd_levels t2'
1703 ||' WHERE t2.level_name = t1.'||p_level_name_col
1704 ||' AND t2.PLAN_TYPE IS NULL ),' --bug 4443782
1705 || NULL_VALUE ||')'
1706 ||' WHERE sr_instance_code = :p_instance_code'
1707 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1708 ||' AND NVL(t1.'||p_level_name_col||','||''''||NULL_CHAR||''''||') '
1709 ||' <> '||''''||NULL_CHAR||''''
1710 ||' AND process_flag = '||G_IN_PROCESS ;
1711
1712 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1713
1714 EXECUTE IMMEDIATE lv_sql_stmt
1715 USING p_instance_code,
1716 p_batch_id;
1717
1718 IF p_severity = 1 THEN
1719
1720 v_sql_stmt := 02;
1721 lv_sql_stmt :=
1722 'UPDATE '||p_table_name ||' t1'
1723 ||' SET error_text = '||''''||p_message_text||''''||','
1724 ||' process_flag = '||G_ERROR
1725 ||' WHERE NVL(t1.'||p_level_id_col||','||''''||NULL_CHAR||''''||') '
1726 ||' = '||''''||NULL_CHAR||''''
1727 ||' AND sr_instance_code = :p_instance_code'
1728 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1729 ||' AND process_flag = ' ||G_IN_PROCESS;
1730
1731 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1732 EXECUTE IMMEDIATE lv_sql_stmt
1733 USING p_instance_code,
1734 p_batch_id;
1735
1736 ELSIF p_severity = 3 THEN
1737 v_sql_stmt := 03;
1738
1739 lv_sql_stmt :=
1740 'UPDATE '||p_table_name ||' t1'
1741 ||' SET error_text = '||''''||p_message_text||''''||','
1742 ||' process_flag = '||g_error
1743 ||' WHERE NVL(t1.'||p_level_id_col||','||''''||NULL_CHAR||''''||') '
1744 ||' = '||''''||NULL_CHAR||''''
1745 ||' AND NVL(t1.'||p_level_name_col||','||''''||NULL_CHAR||''''||') '
1746 ||' <> '||''''||NULL_CHAR||''''
1747 ||' AND sr_instance_code = :p_instance_code'
1748 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
1749 ||' AND process_flag = ' ||G_IN_PROCESS;
1750
1751 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1752 EXECUTE IMMEDIATE lv_sql_stmt
1753 USING p_instance_code,
1754 p_batch_id;
1755 END IF;
1756 RETURN(lv_status);
1757
1758 EXCEPTION
1759
1760 WHEN too_many_rows THEN
1761 p_error_text := substr('MSC_ST_UTIL.DERIVE_LEVEL_ID'||'('
1762 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1763 return(SQLCODE);
1764
1765 WHEN OTHERS THEN
1769
1766 p_error_text := substr('MSC_ST_UTIL.DERIVE_LEVEL_ID'||'('
1767 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1768 return(SQLCODE);
1770 END DERIVE_LEVEL_ID;
1771
1772 /*==========================================================================+
1773 | DESCRIPTION : This function is called for deriving sr_level_pk |
1774 | from the msd_local_id_setup table. |
1775 +==========================================================================*/
1776 FUNCTION DERIVE_SR_LEVEL_PK
1777 (p_table_name VARCHAR2,
1778 p_level_val_col VARCHAR2, --level value col name
1779 p_level_pk_col VARCHAR2, --level_pk column name
1780 p_level_id_col VARCHAR2, --level_id col name
1781 p_instance_code VARCHAR2,
1782 p_message_text VARCHAR2 DEFAULT NULL,
1783 p_batch_id NUMBER DEFAULT NULL_VALUE,
1784 p_error_text OUT NOCOPY VARCHAR2)
1785 RETURN NUMBER IS
1786 lv_sql_stmt VARCHAR2(5000);
1787 lv_where_str VARCHAR2(100);
1788 lv_status NUMBER := 0;
1789 --lv_debug BOOLEAN := retn_debug_mode;
1790
1791 BEGIN
1792 v_sql_stmt := 01;
1793 lv_sql_stmt :=
1794 'UPDATE '||p_table_name ||' t1'
1795 ||' SET '||p_level_pk_col
1796 ||' = (SELECT local_id'
1797 ||' FROM msd_local_id_setup t2'
1798 ||' WHERE t2.char1 = t1.sr_instance_code'
1799 ||' AND t2.char2 = t1.'||p_level_val_col
1800 ||' AND t2.level_id = t1.'||p_level_id_col||')'
1801 ||' WHERE sr_instance_code = :p_instance_code'
1802 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1803 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
1804 ||' = '||''''||NULL_CHAR||''''
1805 ||' AND process_flag = '||G_IN_PROCESS ;
1806
1807 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1808
1809 EXECUTE IMMEDIATE lv_sql_stmt
1810 USING p_instance_code,
1811 p_batch_id;
1812 RETURN(lv_status);
1813
1814 EXCEPTION
1815 WHEN too_many_rows THEN
1816 p_error_text := substr('MSC_ST_UTIL.DERIVE_SR_LEVEL_PK'||'('
1817 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1818 return(SQLCODE);
1819
1820 WHEN OTHERS THEN
1821 p_error_text := substr('MSC_ST_UTIL.DERIVE_SR_LEVEL_PK'||'('
1822 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1823 return(SQLCODE);
1824
1825 END DERIVE_SR_LEVEL_PK;
1826
1827 /*==========================================================================+
1828 | DESCRIPTION : This function is called for deriving sr_xxx_pk |
1829 | from the msd_st_level_values and msd_level_values |
1830 | Should be used when level_id value to be passed as |
1831 | a parameter and same is not stored in any column. |
1832 +==========================================================================*/
1833
1834 FUNCTION DERIVE_SR_PK
1835 (p_table_name VARCHAR2,
1836 p_column_name VARCHAR2, --level value col name
1837 p_pk_col_name VARCHAR2, --level_pk column name
1838 p_level_id VARCHAR2, --level_id
1839 p_severity VARCHAR2 DEFAULT G_SEV3_ERROR,
1840 p_instance_id NUMBER,
1841 p_instance_code VARCHAR2,
1842 p_message_text VARCHAR2 DEFAULT NULL,
1843 p_batch_id NUMBER DEFAULT NULL_VALUE,
1844 p_error_text OUT NOCOPY VARCHAR2)
1845 RETURN NUMBER
1846 IS
1847 lv_sql_stmt VARCHAR2(5000);
1848 lv_where_str VARCHAR2(100);
1849 lv_status NUMBER := 0;
1850 --lv_debug BOOLEAN := retn_debug_mode;
1851
1852
1853 BEGIN
1854
1855 v_sql_stmt := 01;
1856 lv_sql_stmt :=
1857 'UPDATE '||p_table_name ||' t1'
1858 ||' SET '||p_pk_col_name
1859 ||' = NVL((SELECT sr_level_pk '
1860 ||' FROM msd_level_values t2'
1861 ||' WHERE t2.level_value '
1862 ||' = t1.'||p_column_name
1863 ||' AND t2.level_id = :p_level_id '
1864 ||' AND t2.instance = :p_instance_id'
1865 ||' AND rownum = 1'
1866 ||' UNION'
1867 ||' SELECT sr_level_pk'
1868 ||' FROM msd_st_level_values t3'
1869 ||' WHERE t3.level_value '
1870 ||' = t1.'||p_column_name
1871 ||' AND t3.level_id = :p_level_id '
1872 ||' AND t3.instance = :p_instance_id '
1873 ||' AND NOT EXISTS (SELECT sr_level_pk '
1874 ||' FROM msd_level_values t4'
1875 ||' WHERE t4.level_value '
1876 ||' = t1.'||p_column_name
1877 ||' AND t4.level_id = :p_level_id '
1878 ||' AND t4.instance = :p_instance_id)'
1879 ||' AND rownum = 1),'
1880 || p_pk_col_name||')' --This change is made to ensure that - not updating the sr_level_pk with any dummy value (like '-23453') when the value for the level value is not provided.
1881 ||' WHERE sr_instance_code = :p_instance_code'
1882 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1883 ||' AND process_flag = '||G_IN_PROCESS ;
1884
1885 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1886
1887 EXECUTE IMMEDIATE lv_sql_stmt
1888 USING p_level_id,
1889 p_instance_id,
1890 p_level_id,
1891 p_instance_id,
1892 p_level_id,
1893 p_instance_id,
1894 p_instance_code,
1895 p_batch_id;
1896
1897
1898 IF p_severity = 1 THEN
1899
1900 v_sql_stmt := 03;
1904 ||' process_flag = '||G_ERROR
1901 lv_sql_stmt :=
1902 'UPDATE '||p_table_name ||' t1'
1903 ||' SET error_text = '||''''||p_message_text||''''||','
1905 ||' WHERE NVL(t1.'||p_pk_col_name||','||''''||NULL_CHAR||''''||') '
1906 ||' = '||''''||NULL_CHAR||''''
1907 ||' AND sr_instance_code = :p_instance_code'
1908 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1909 ||' AND process_flag = ' ||G_IN_PROCESS;
1910
1911 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1912 EXECUTE IMMEDIATE lv_sql_stmt
1913 USING p_instance_code,
1914 p_batch_id;
1915
1916 ELSIF p_severity = 3 THEN
1917
1918 v_sql_stmt := 04;
1919 lv_sql_stmt :=
1920 'UPDATE '||p_table_name ||' t1'
1921 ||' SET error_text = '||''''||p_message_text||''''||','
1922 ||' process_flag = '||G_ERROR
1923 ||' WHERE NVL(t1.'||p_pk_col_name||','||''''||NULL_CHAR||''''||') '
1924 ||' = '||''''||NULL_CHAR||''''
1925 ||' AND NVL(t1.'||p_column_name||','||''''||NULL_CHAR||''''||') '
1926 ||' <> '||''''||NULL_CHAR||''''
1927 ||' AND sr_instance_code = :p_instance_code'
1928 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
1929 ||' AND process_flag = ' ||G_IN_PROCESS;
1930
1931 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
1932 EXECUTE IMMEDIATE lv_sql_stmt
1933 USING p_instance_code,
1934 p_batch_id;
1935
1936 END IF;
1937 RETURN(lv_status);
1938
1939 EXCEPTION
1940
1941 WHEN too_many_rows THEN
1942 p_error_text := substr('MSC_ST_UTIL.DERIVE_SR_PK'||'('
1943 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1944 return(SQLCODE);
1945
1946 WHEN OTHERS THEN
1947 p_error_text := substr('MSC_ST_UTIL.DERIVE_SR_PK'||'('
1948 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
1949 return(SQLCODE);
1950
1951 END DERIVE_SR_PK ;
1952
1953 /*==========================================================================+
1954 | DESCRIPTION : This function is called for deriving level_pk |
1955 | from the msd_st_level_values and msd_level_values |
1956 | Should be used when level_name is stored in one of column |
1957 | pass column as a parameter |
1958 +==========================================================================*/
1959
1960 FUNCTION DERIVE_LEVEL_PK
1961 (p_table_name VARCHAR2,
1962 p_level_val_col VARCHAR2, --level value col name
1963 p_level_name_col VARCHAR2, --level_name column
1964 p_level_pk_col VARCHAR2, --level_val col name
1965 p_severity VARCHAR2 DEFAULT G_SEV3_ERROR,
1966 p_instance_code VARCHAR2,
1967 p_instance_id NUMBER,
1968 p_message_text VARCHAR2,
1969 p_batch_id NUMBER DEFAULT NULL_VALUE,
1970 p_error_text OUT NOCOPY VARCHAR2)
1971 RETURN NUMBER
1972 IS
1973 lv_sql_stmt VARCHAR2(5000);
1974 lv_where_str VARCHAR2(100);
1975 lv_status NUMBER := 0;
1976 --lv_debug BOOLEAN := retn_debug_mode;
1977
1978 BEGIN
1979 v_sql_stmt := 01;
1980 lv_sql_stmt :=
1981 'UPDATE '||p_table_name ||' t1'
1982 ||' SET '||p_level_pk_col
1983 ||' = NVL((SELECT sr_level_pk '
1984 ||' FROM msd_level_values t2'
1985 ||' WHERE t2.level_value '
1986 ||' = t1.'||p_level_val_col
1987 ||' AND t2.level_id = (SELECT level_id'
1988 ||' FROM msd_levels t5 '
1989 ||' WHERE t5.level_name '
1990 ||' = t1.'||p_level_name_col
1991 ||' AND t5.PLAN_TYPE IS NULL )'--bug 4443782
1992 ||' AND t2.instance = :p_instance_id'
1993 ||' AND rownum = 1'
1994 ||' UNION'
1995 ||' SELECT sr_level_pk'
1996 ||' FROM msd_st_level_values t3'
1997 ||' WHERE t3.level_value '
1998 ||' = t1.'||p_level_val_col
1999 ||' AND t3.level_id = (SELECT level_id'
2000 ||' FROM msd_levels t4'
2001 ||' WHERE t4.level_name '
2002 ||' = t1.'||p_level_name_col
2003 ||' AND t4.PLAN_TYPE IS NULL )'--bug 4443782
2004 ||' AND t3.instance = :p_instance_id'
2005 ||' AND NOT EXISTS ( SELECT sr_level_pk'
2006 ||' FROM msd_level_values t2'
2007 ||' WHERE t2.level_value '
2008 ||' = t1.'||p_level_val_col
2009 ||' AND t2.level_id = (SELECT level_id'
2010 ||' FROM msd_levels t5 '
2011 ||' WHERE t5.level_name '
2012 ||' = t1.'||p_level_name_col
2013 ||' AND t5.PLAN_TYPE IS NULL )'--bug 4443782
2014 ||' AND t2.instance = :p_instance_id )'
2015 ||' AND rownum=1 ),'
2016 || NULL_VALUE||')'
2017 ||' WHERE sr_instance_code = :p_instance_code'
2018 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
2019 ||' AND NVL(t1.'||p_level_val_col||','||''''||NULL_CHAR||''''||') '
2020 ||' <> '||''''||NULL_CHAR||''''
2021 ||' AND process_flag = '||G_IN_PROCESS ;
2022
2023 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2024
2025 EXECUTE IMMEDIATE lv_sql_stmt
2026 USING p_instance_id,
2027 p_instance_id,
2028 p_instance_id,
2029 p_instance_code,
2030 p_batch_id;
2031
2032
2033 IF p_severity = 1 THEN
2034
2035 v_sql_stmt := 03;
2036 lv_sql_stmt :=
2037 'UPDATE '||p_table_name ||' t1'
2041 ||' = '||''''||NULL_CHAR||''''
2038 ||' SET error_text = '||''''||p_message_text||''''||','
2039 ||' process_flag = '||G_ERROR
2040 ||' WHERE NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
2042 ||' AND sr_instance_code = :p_instance_code'
2043 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
2044 ||' AND process_flag = ' ||G_IN_PROCESS;
2045
2046 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2047 EXECUTE IMMEDIATE lv_sql_stmt
2048 USING p_instance_code,
2049 p_batch_id;
2050
2051 ELSIF p_severity = 3 THEN
2052
2053 v_sql_stmt := 04;
2054 lv_sql_stmt :=
2055 'UPDATE '||p_table_name ||' t1'
2056 ||' SET error_text = '||''''||p_message_text||''''||','
2057 ||' process_flag = '||G_ERROR
2058 ||' WHERE NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
2059 ||' = '||''''||NULL_CHAR||''''
2060 ||' AND NVL(t1.'||p_level_val_col||','||''''||NULL_CHAR||''''||') '
2061 ||' <> '||''''||NULL_CHAR||''''
2062 ||' AND sr_instance_code = :p_instance_code'
2063 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
2064 ||' AND process_flag = ' ||G_IN_PROCESS;
2065
2066 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2067 EXECUTE IMMEDIATE lv_sql_stmt
2068 USING p_instance_code,
2069 p_batch_id;
2070
2071 END IF;
2072 RETURN(lv_status);
2073
2074 EXCEPTION
2075
2076 WHEN too_many_rows THEN
2077 p_error_text := substr('MSC_ST_UTIL.DERIVE_LEVEL_PK'||'('
2078 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2079 return(SQLCODE);
2080
2081 WHEN OTHERS THEN
2082 p_error_text := substr('MSC_ST_UTIL.DERIVE_LEVEL_PK'||'('
2083 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2084 return(SQLCODE);
2085
2086 END DERIVE_LEVEL_PK ;
2087
2088 PROCEDURE LOG_MESSAGE(p_error_text IN VARCHAR2)
2089 IS
2090 BEGIN
2091 IF fnd_global.conc_request_id > 0 THEN
2092 FND_FILE.PUT_LINE( FND_FILE.LOG, p_error_text);
2093 END IF;
2094
2095 EXCEPTION
2096 WHEN OTHERS THEN
2097 RETURN;
2098
2099 END LOG_MESSAGE;
2100
2101 /*==========================================================================+
2102 | Returns the default instance code based on the trading partner code. |
2103 | This function would be called from XML Gateway to get the default instance|
2104 | code in case the XML message does not contain the instance code |
2105 +==========================================================================*/
2106 FUNCTION GET_INSTANCE_CODE (p_trading_partner_id number) RETURN VARCHAR2
2107 IS
2108 lv_instance_code msc_apps_instances.instance_code%TYPE;
2109 /*
2110 CURSOR c_inst(p_trading_partner_id number) IS
2111 SELECT instance_code
2112 FROM msc_apps_instances mai
2113 WHERE mai.default_flag = 'Y'
2114 AND company_id = (SELECT company_id
2115 FROM msc_trading_partners mtp
2116 WHERE mtp.partner_id = p_trading_partner_id);
2117 */
2118
2119 BEGIN
2120 /*
2121 OPEN c_inst(p_trading_partner_id);
2122 FETCH c_inst INTO lv_instance_code ;
2123 CLOSE c_inst;
2124
2125 RETURN lv_instance_code;
2126 */
2127 RETURN null;
2128
2129 EXCEPTION
2130 WHEN no_data_found THEN
2131 lv_instance_code := 'ERROR';
2132 RETURN lv_instance_code;
2133
2134 WHEN too_many_rows THEN
2135 lv_instance_code := 'ERROR';
2136 RETURN lv_instance_code;
2137
2138 WHEN OTHERS THEN
2139 lv_instance_code := 'ERROR';
2140 RETURN lv_instance_code;
2141
2142 END GET_INSTANCE_CODE;
2143
2144 /*==========================================================================+
2145 | DESCRIPTION : This procedure takes the SYNCIND value from the XML |
2146 | message in character and convert it into numeric records. |
2147 +==========================================================================*/
2148 PROCEDURE retn_delete_flag
2149 (p_syncind IN VARCHAR2,
2150 p_return OUT NOCOPY NUMBER) IS
2151 lv_return number;
2152 BEGIN
2153 SELECT DECODE(NVL(UPPER(p_syncind),'A'),'D',1,2)
2154 INTO p_return
2155 FROM dual;
2156 EXCEPTION
2157 WHEN others THEN
2158 p_return := -1;
2159 END retn_delete_flag ;
2160
2161 /*==========================================================================+
2162 | DESCRIPTION : This procedure takes the Schedule Type value from the XML |
2163 | message in character and convert it into numeric records. |
2164 +==========================================================================*/
2165 PROCEDURE retn_schedule_id
2166 (p_schedule_type IN VARCHAR2,
2167 p_schid OUT NOCOPY NUMBER) IS
2168 BEGIN
2169 SELECT DECODE(NVL(UPPER(p_schedule_type),'MDS'),'MDS',1,'MPS',2,'FORECAST',6,
2170 'SUPPLY FORECAST',2,'DEMAND_FORECAST',6,1)
2171 INTO p_schid
2172 FROM dual;
2173 EXCEPTION
2174 WHEN others THEN
2175 p_schid := -1;
2176 END retn_schedule_id;
2177
2178 /*=====================================================================+
2179 | DESCRIPTION : This function provides the user friendly name of the |
2180 | entity whose local_id is provided, by looking into the|
2181 | corresponding MSC_LOCAL_ID_XXX table |
2182 +======================================================================*/
2183 FUNCTION ret_code
2184 (p_entity_name IN VARCHAR2,
2185 p_id IN NUMBER)
2186 RETURN VARCHAR2 IS
2187 TYPE c_cur_type IS REF CURSOR;
2188 c_cur c_cur_type;
2189 lv_return_val VARCHAR2(240):='';
2190 lv_sql_stmt VARCHAR2(1000):='';
2191 BEGIN
2192 -- LINE_CODE
2193 IF p_entity_name = 'LINE_ID' THEN
2194 lv_sql_stmt:=
2195 'SELECT CHAR4 '
2196 ||' FROM MSC_LOCAL_ID_SETUP '
2197 ||' WHERE entity_name = :entity_id'
2198 ||' AND local_id = :local_id';
2199
2200 --ITEM_NAME
2201 ELSIF p_entity_name = 'SR_INVENTORY_ITEM_ID' THEN
2202 lv_sql_stmt:=
2203 'SELECT CHAR4 '
2204 ||' FROM MSC_LOCAL_ID_ITEM '
2205 ||' WHERE entity_name = :entity_id'
2206 ||' AND local_id = :local_id';
2207
2208 -- PROJECT_NUMBER
2209 ELSIF p_entity_name = 'PROJECT_ID' THEN
2210 lv_sql_stmt:=
2211 'SELECT CHAR4 '
2212 ||' FROM MSC_LOCAL_ID_MISC'
2213 ||' WHERE entity_name = :entity_id'
2214 ||' AND local_id = :local_id';
2215
2216 -- TASK_NUMBER
2217 ELSIF p_entity_name = 'TASK_ID' THEN
2218 lv_sql_stmt:=
2219 'SELECT CHAR5 '
2220 ||' FROM MSC_LOCAL_ID_MISC'
2221 ||' WHERE entity_name = :entity_id'
2222 ||' AND local_id = :local_id';
2223
2224 -- BOM_NAME
2225 ELSIF p_entity_name = 'BILL_SEQUENCE_ID' THEN
2226 lv_sql_stmt:=
2227 'SELECT CHAR4 '
2228 ||' FROM MSC_LOCAL_ID_SETUP '
2229 ||' WHERE entity_name = :entity_id'
2230 ||' AND local_id = :local_id';
2231
2232 -- ROUTING_NAME
2233 ELSIF p_entity_name = 'ROUTING_SEQUENCE_ID' THEN
2234 lv_sql_stmt:=
2235 'SELECT CHAR4 '
2236 ||' FROM MSC_LOCAL_ID_SETUP '
2237 ||' WHERE entity_name = :entity_id'
2238 ||' AND local_id = :local_id';
2239
2240 -- DEPARTMENT_CODE
2241 ELSIF p_entity_name = 'DEPARTMENT_ID' THEN
2242 lv_sql_stmt:=
2243 'SELECT CHAR4 '
2244 ||' FROM MSC_LOCAL_ID_SETUP '
2245 ||' WHERE entity_name = :entity_id'
2246 ||' AND local_id = :local_id';
2247
2248 -- OPERATION_SEQ_CODE
2249 ELSIF p_entity_name = 'OPERATION_SEQUENCE_ID' THEN
2250 lv_sql_stmt:=
2251 'SELECT CHAR5 '
2252 ||' FROM MSC_LOCAL_ID_SETUP '
2253 ||' WHERE entity_name = :entity_id'
2254 ||' AND local_id = :local_id';
2255
2256 -- RESOURCE_SEQ_CODE
2257 ELSIF p_entity_name = 'RESOURCE_SEQ_NUM' THEN
2258 lv_sql_stmt:=
2259 'SELECT CHAR6 '
2260 ||' FROM MSC_LOCAL_ID_SETUP '
2261 ||' WHERE entity_name = :entity_id'
2262 ||' AND local_id = :local_id';
2263
2264 -- RESOURCE_CODE
2265 ELSIF p_entity_name = 'RESOURCE_ID' THEN
2266 lv_sql_stmt:=
2267 'SELECT CHAR4 '
2268 ||' FROM MSC_LOCAL_ID_SETUP '
2269 ||' WHERE entity_name = :entity_id'
2270 ||' AND local_id = :local_id';
2271
2272 -- PURCH_LINE_NUM
2273 ELSIF p_entity_name = 'PO_LINE_ID' THEN
2274 lv_sql_stmt:=
2275 'SELECT NUMBER1 '
2276 ||'FROM MSC_LOCAL_ID_SUPPLY '
2277 ||'WHERE entity_name = :entity_id '
2278 ||'AND local_id = :local_id ';
2279
2280 -- SCHEDULE_LINE_NUM
2281 ELSIF p_entity_name = 'DISPOSITION_ID_MPS' THEN
2282 lv_sql_stmt:=
2283 'SELECT CHAR5 '
2284 ||'FROM MSC_LOCAL_ID_SUPPLY '
2285 ||'WHERE entity_name = :entity_id '
2286 ||'AND local_id = :local_id ';
2287
2288 -- WIP_ENTITY_NAME
2289 ELSIF p_entity_name = 'WIP_ENTITY_ID' THEN
2290 lv_sql_stmt:=
2291 'SELECT CHAR4 '
2292 ||'FROM MSC_LOCAL_ID_SUPPLY '
2293 ||'WHERE entity_name = :entity_id '
2294 ||'AND local_id = :local_id ';
2295
2296 END IF;
2297
2298 OPEN c_cur FOR lv_sql_stmt USING p_entity_name,p_id;
2299 FETCH c_cur into lv_return_val;
2300 CLOSE c_cur;
2301 RETURN (lv_return_val );
2302 END ret_code ;
2303
2304 /*=====================================================================+
2305 | DESCRIPTION : This function provides the organization code |
2306 +======================================================================*/
2307 FUNCTION ret_org_code
2308 (p_sr_instance_id IN NUMBER,
2309 p_organization_id IN NUMBER)
2310 RETURN VARCHAR2 IS
2311 TYPE c_cur_type IS REF CURSOR;
2312 c_cur c_cur_type;
2313 lv_return_val VARCHAR2(240):='';
2314 lv_sql_stmt VARCHAR2(1000):='';
2315 BEGIN
2316
2317 lv_sql_stmt:=
2318 'SELECT organization_code '
2319 ||'FROM msc_trading_partners mtp '
2320 ||'WHERE mtp.sr_instance_id = :instance_id '
2321 ||'AND mtp.sr_tp_id = :org_id '
2322 ||'AND mtp.partner_type = 3 ';
2323
2324 OPEN c_cur FOR lv_sql_stmt
2325 USING p_sr_instance_id ,
2326 p_organization_id;
2327
2328 FETCH c_cur INTO lv_return_val;
2329 CLOSE c_cur;
2330
2331 lv_return_val := substr(lv_return_val, instr(lv_return_val,':') + 1);
2332
2333 RETURN (lv_return_val );
2334 END ret_org_code;
2335
2336 /*=============================================================================+
2337 | DESCRIPTION : This function provides the partner name given the partner_id |
2338 +=============================================================================*/
2339 FUNCTION ret_partner_name
2340 (p_partner_id IN NUMBER)
2341 RETURN VARCHAR2 IS
2342 TYPE c_cur_type IS REF CURSOR;
2343 c_cur c_cur_type;
2344 lv_return_val VARCHAR2(240):='';
2345 lv_sql_stmt VARCHAR2(1000):='';
2346 BEGIN
2347
2348 lv_sql_stmt:=
2349 'SELECT partner_name '
2350 ||' FROM msc_trading_partners mtp '
2351 ||' WHERE mtp.partner_id = :partner_id ';
2352
2353 OPEN c_cur FOR lv_sql_stmt
2354 USING p_partner_id;
2355 FETCH c_cur INTO lv_return_val;
2356 CLOSE c_cur;
2357
2358 RETURN (lv_return_val );
2359 END ret_partner_name ;
2360
2361
2362 /*=============================================================================+
2363 | DESCRIPTION : This function provides the partner name given the sr_tp_id |
2364 +=============================================================================*/
2365 FUNCTION ret_partner_name
2366 (p_instance_id IN NUMBER,
2367 p_sr_tp_id IN NUMBER,
2368 p_partner_type IN NUMBER)
2369 RETURN VARCHAR2 IS
2370 TYPE c_cur_type IS REF CURSOR;
2371 c_cur c_cur_type;
2372 lv_return_val VARCHAR2(240):='';
2373 lv_sql_stmt VARCHAR2(1000):='';
2374
2375 BEGIN
2376 lv_sql_stmt:=
2377 'SELECT mtp.partner_name '
2378 ||' FROM msc_trading_partners mtp, '
2379 ||' msc_tp_id_lid mtp_lid '
2380 ||' WHERE mtp.partner_id = mtp_lid.tp_id'
2381 ||' AND mtp_lid.sr_instance_id = :instance_id'
2382 ||' AND mtp_lid.sr_tp_id = :sr_tp_id '
2383 ||' AND mtp_lid.partner_type = :partner_type ';
2384
2385 OPEN c_cur FOR lv_sql_stmt
2386 USING p_instance_id,
2387 p_sr_tp_id,
2388 p_partner_type;
2389
2390 FETCH c_cur INTO lv_return_val;
2391 CLOSE c_cur;
2392 RETURN (lv_return_val );
2393
2394 END ret_partner_name ;
2395
2396 /*=================================================================================+
2397 | DESCRIPTION : This function provides the partner site given the partner_site_id |
2398 +==================================================================================*/
2399 FUNCTION ret_partner_site
2400 (p_partner_site_id IN NUMBER)
2401 RETURN VARCHAR2 IS
2402 TYPE c_cur_type IS REF CURSOR;
2403 c_cur c_cur_type;
2404 lv_return_val VARCHAR2(240):='';
2405 lv_sql_stmt VARCHAR2(1000):='';
2406
2407 BEGIN
2408 lv_sql_stmt:=
2409 'SELECT tp_site_code '
2410 ||'FROM MSC_TRADING_PARTNER_SITES mtps '
2411 ||'WHERE mtps.partner_site_id = :partner_site ';
2412
2413 OPEN c_cur FOR lv_sql_stmt
2414 USING p_partner_site_id;
2415 FETCH c_cur INTO lv_return_val;
2416 CLOSE c_cur;
2417
2418 RETURN (lv_return_val );
2419
2420 END ret_partner_site ;
2421
2422
2423 /*=================================================================================+
2424 | DESCRIPTION : This function provides the partner site given the sr_tp_site_id |
2425 +==================================================================================*/
2426 FUNCTION ret_partner_site
2427 (p_instance_id IN NUMBER,
2428 p_sr_tp_site_id IN NUMBER,
2429 p_partner_type IN NUMBER)
2430 RETURN VARCHAR2 IS
2431 TYPE c_cur_type IS REF CURSOR;
2432 c_cur c_cur_type;
2433 lv_return_val VARCHAR2(240):='';
2434 lv_sql_stmt VARCHAR2(1000):='';
2435
2436 BEGIN
2437 lv_sql_stmt:=
2438 ' SELECT mtps.tp_site_code '
2439 ||' FROM MSC_TRADING_PARTNER_SITES mtps,'
2440 ||' MSC_TP_SITE_ID_LID mtps_lid'
2441 ||' WHERE mtps.partner_site_id = mtps_lid.sr_tp_site_id'
2442 ||' AND mtps_lid.sr_instance_id = :instance_id '
2443 ||' AND mtps_lid.sr_tp_site_id = :partner_site '
2444 ||' AND mtps_lid.partner_type = :partner_type ';
2445
2446 OPEN c_cur FOR lv_sql_stmt
2447 USING p_instance_id,
2448 p_sr_tp_site_id,
2449 p_partner_type;
2450 FETCH c_cur INTO lv_return_val;
2451 CLOSE c_cur;
2452
2453 RETURN (lv_return_val );
2454
2455 END ret_partner_site ;
2456
2457 /*=================================================================================+
2458 | DESCRIPTION : This function provides the designator name given the designator_id|
2459 +==================================================================================*/
2460 FUNCTION ret_desig
2461 (p_designator_id IN NUMBER)
2462 RETURN VARCHAR2 IS
2463 TYPE c_cur_type IS REF CURSOR;
2464 c_cur c_cur_type;
2465 lv_return_val VARCHAR2(240):='';
2466 lv_sql_stmt VARCHAR2(1000):='';
2467
2468 BEGIN
2469 lv_sql_stmt:=
2470 'SELECT designator '
2471 ||'FROM msc_designators '
2472 ||'WHERE designator_id = :designator_id ';
2473
2474 OPEN c_cur FOR lv_sql_stmt
2475 USING p_designator_id;
2476 FETCH c_cur INTO lv_return_val;
2477 CLOSE c_cur;
2478
2479 RETURN (lv_return_val );
2480
2481 END ret_desig ;
2482
2483 /*=================================================================================+
2484 | DESCRIPTION : This function provides the instance code given the instance_id |
2485 +==================================================================================*/
2486 FUNCTION ret_sr_instance_code
2487 (p_sr_instance_id IN NUMBER)
2488 RETURN VARCHAR2 IS
2489 TYPE c_cur_type IS REF CURSOR;
2490 c_cur c_cur_type;
2491 lv_return_val VARCHAR2(240):='';
2492 lv_sql_stmt VARCHAR2(1000):='';
2493 BEGIN
2494
2495 lv_sql_stmt:=
2496 'SELECT instance_code '
2497 ||'FROM msc_apps_instances '
2498 ||'WHERE instance_id = :instance_id ';
2499
2500 OPEN c_cur FOR lv_sql_stmt
2501 USING p_sr_instance_id;
2502 FETCH c_cur INTO lv_return_val;
2503 CLOSE c_cur;
2504
2505 RETURN (lv_return_val );
2506
2507 END ret_sr_instance_code ;
2508
2509 /*=================================================================================+
2510 | DESCRIPTION : This function provides the project number |
2511 +==================================================================================*/
2512 FUNCTION ret_project_number
2513 (p_project_id IN NUMBER ,
2514 p_sr_instance_id IN NUMBER ,
2515 p_organization_id IN NUMBER,
2516 p_plan_id IN NUMBER)
2517 RETURN VARCHAR2 IS
2518 TYPE c_cur_type IS REF CURSOR;
2519 c_cur c_cur_type;
2520 lv_return_val VARCHAR2(240):='';
2521 lv_sql_stmt VARCHAR2(1000):='';
2522
2523 BEGIN
2524 lv_sql_stmt:=
2525 'SELECT project_number '
2526 ||'FROM msc_projects '
2527 ||'WHERE sr_instance_id = :instance_id '
2528 ||'AND organization_id = :org_id '
2529 ||'AND project_id = :project_id '
2530 ||'AND plan_id = :plan_id ';
2531
2532 OPEN c_cur FOR lv_sql_stmt
2533 USING p_sr_instance_id,
2534 p_organization_id,
2535 p_project_id,
2536 p_plan_id ;
2537 FETCH c_cur INTO lv_return_val;
2538 CLOSE c_cur;
2539
2540 RETURN (lv_return_val );
2541 END ret_project_number ;
2542
2543 /*=================================================================================+
2544 | DESCRIPTION : This function provides the task number |
2545 +==================================================================================*/
2546 FUNCTION ret_task_number
2547 (p_project_id IN NUMBER,
2548 p_task_id IN NUMBER,
2549 p_sr_instance_id IN NUMBER,
2550 p_organization_id IN NUMBER,
2551 p_plan_id IN NUMBER)
2552 RETURN VARCHAR2 IS
2553 TYPE c_cur_type IS REF CURSOR;
2554 c_cur c_cur_type;
2555 lv_return_val VARCHAR2(240):='';
2556 lv_sql_stmt VARCHAR2(1000):='';
2557
2558 BEGIN
2559 lv_sql_stmt:=
2560 'SELECT task_number '
2561 ||' FROM msc_project_tasks '
2562 ||' WHERE sr_instance_id = :instance_id '
2563 ||' AND organization_id = :org_id '
2564 ||' AND project_id = :project_id '
2565 ||' AND task_id = :task_id '
2566 ||' AND plan_id = :plan_id ';
2567
2568 OPEN c_cur FOR lv_sql_stmt
2569 USING p_sr_instance_id,
2570 p_organization_id,
2571 p_project_id,
2572 p_task_id,
2573 p_plan_id ;
2574 FETCH c_cur INTO lv_return_val;
2575 CLOSE c_cur;
2576 RETURN (lv_return_val );
2577
2578 END ret_task_number ;
2579
2580 /*=================================================================================+
2581 | DESCRIPTION : This function provides the wip entity name |
2582 +==================================================================================*/
2583 FUNCTION ret_wip_entity_name
2584 (p_wip_entity_id IN NUMBER,
2585 p_sr_instance_id IN NUMBER,
2586 p_organization_id IN NUMBER,
2587 p_plan_id IN NUMBER)
2588 RETURN VARCHAR2 IS
2589 TYPE c_cur_type IS REF CURSOR;
2590 c_cur c_cur_type;
2591 lv_return_val VARCHAR2(240):='';
2592 lv_sql_stmt VARCHAR2(1000):='';
2593 BEGIN
2594
2595 lv_sql_stmt:=
2596 'SELECT wip_entity_name '
2597 ||'FROM msc_supplies '
2598 ||'WHERE sr_instance_id = :instance_id '
2599 ||'AND organization_id = :org_id '
2600 ||'AND disposition_id = :wip_entity_id '
2601 ||'AND order_type IN (3,7) '
2602 ||'AND plan_id = :plan_id ';
2603
2604 OPEN c_cur FOR lv_sql_stmt
2605 USING p_sr_instance_id,
2606 p_organization_id,
2607 p_wip_entity_id ,
2608 p_plan_id ;
2609
2610 FETCH c_cur INTO lv_return_val;
2611 CLOSE c_cur;
2612 RETURN (lv_return_val );
2613
2614 END ret_wip_entity_name ;
2615
2616 /*=======================================================================================================+
2617 | DESCRIPTION : This function provides the group_id for MSC_FROUPS and MSC_GROUP_COMPANIES aeroexhange |
2618 +========================================================================================================*/
2619 FUNCTION DERIVE_GROUP_ID
2620 (p_table_name VARCHAR2,
2621 p_grp_col_name VARCHAR2,
2622 p_grp_col_id VARCHAR2,
2623 p_instance_id NUMBER,
2624 p_instance_code VARCHAR2,
2625 p_error_text OUT NOCOPY VARCHAR2,
2626 p_batch_id NUMBER DEFAULT NULL_VALUE,
2627 p_severity NUMBER DEFAULT 0,
2628 p_message_text VARCHAR2 DEFAULT NULL,
2629 p_debug BOOLEAN DEFAULT FALSE,
2630 p_row LONG DEFAULT NULL)
2631 RETURN NUMBER IS
2632 lv_sql_stmt VARCHAR2(5000);
2633 lv_where_str VARCHAR2(100);
2634 lv_status NUMBER := 0;
2635 BEGIN
2636
2637 v_sql_stmt := 01;
2638 lv_sql_stmt :=
2639 'UPDATE '||p_table_name ||' t1'
2640 ||' SET '||p_grp_col_id
2641 ||' = (SELECT local_id'
2642 ||' FROM msc_local_id_setup t2'
2643 ||' WHERE t2.char1 = t1.'||p_grp_col_name
2644 ||' AND t2.entity_name = ''GROUP_ID'' )'
2645 ||' WHERE sr_instance_code = :p_instance_code'
2646 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
2647 ||' AND process_flag = '||G_IN_PROCESS;
2648
2649 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2650
2651 EXECUTE IMMEDIATE lv_sql_stmt
2652 USING p_instance_code,
2653 p_batch_id;
2654
2655 IF p_message_text IS NOT NULL and p_severity = 1 THEN
2656 v_sql_stmt := 07;
2657 lv_sql_stmt :=
2658 'UPDATE '||p_table_name ||' t1'
2659 ||' SET error_text = '||''''||p_message_text||''''||','
2660 ||' process_flag = '||g_error
2661 ||' WHERE NVL(t1.'||p_grp_col_id||','||NULL_VALUE||') = '||NULL_VALUE
2662 ||' AND sr_instance_code = :p_instance_code'
2663 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
2664 ||' AND process_flag = ' ||G_IN_PROCESS;
2665
2669 p_batch_id;
2666 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2667 EXECUTE IMMEDIATE lv_sql_stmt
2668 USING p_instance_code,
2670
2671 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
2672 lv_where_str :=
2673 ' AND NVL('||p_grp_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
2674
2675 lv_status := LOG_ERROR(p_table_name => p_table_name,
2676 p_instance_code => p_instance_code,
2677 p_row => p_row,
2678 p_severity => p_severity,
2679 p_propagated => 'N',
2680 p_where_str => lv_where_str,
2681 p_message_text => p_message_text,
2682 p_error_text => p_error_text,
2683 p_batch_id => p_batch_id);
2684
2685 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
2686 v_sql_stmt := 08;
2687 lv_sql_stmt :=
2688 'UPDATE '||p_table_name ||' t1'
2689 ||' SET error_text = '||''''||p_message_text||''''||','
2690 ||' process_flag = '||g_error
2691 ||' WHERE NVL(t1.'||p_grp_col_id||','||NULL_VALUE||') = '||NULL_VALUE
2692 ||' AND NVL(t1.'||p_grp_col_name||','||''''||NULL_CHAR||''''||') '
2693 ||' <> '||''''||NULL_CHAR||''''
2694 ||' AND sr_instance_code = :p_instance_code'
2695 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
2696 ||' AND process_flag = ' ||G_IN_PROCESS;
2697 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
2698 EXECUTE IMMEDIATE lv_sql_stmt
2699 USING p_instance_code,
2700 p_batch_id;
2701
2702 END IF;
2703 RETURN(lv_status);
2704
2705 EXCEPTION
2706 WHEN too_many_rows THEN
2707 p_error_text := substr('MSC_ST_UTIL.DERIVE_GROUP_ID'||'('
2708 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2709 return(SQLCODE);
2710
2711 WHEN OTHERS THEN
2712 p_error_text := substr('MSC_ST_UTIL.DERIVE_GROUP_ID'||'('
2713 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
2714 return(SQLCODE);
2715
2716 END DERIVE_GROUP_ID;
2717
2718
2719 /*===================================================================================+
2720 | DESCRIPTION : This function provides the item name given the sr_inventory_item_id |
2721 +====================================================================================*/
2722 FUNCTION ret_item_name
2723 (p_item_id IN NUMBER,
2724 p_sr_instance_id IN NUMBER,
2725 p_organization_id IN NUMBER,
2726 p_plan_id IN NUMBER)
2727 RETURN VARCHAR2 IS
2728 TYPE c_cur_type IS REF CURSOR;
2729 c_cur c_cur_type;
2730 lv_return_val VARCHAR2(240):='';
2731 lv_sql_stmt VARCHAR2(1000):='';
2732
2733 BEGIN
2734 lv_sql_stmt:=
2735 'SELECT ITEM_NAME '
2736 ||'FROM MSC_SYSTEM_ITEMS '
2737 ||'WHERE sr_instance_id = :instance_id '
2738 ||'AND organization_id = :org_id '
2739 ||'AND sr_inventory_item_id = :item_id '
2740 ||'AND plan_id = :plan_id ';
2741
2742 OPEN c_cur FOR lv_sql_stmt
2743 USING p_sr_instance_id,
2744 p_organization_id,
2745 p_item_id ,
2746 p_plan_id ;
2747 FETCH c_cur INTO lv_return_val;
2748 CLOSE c_cur;
2749
2750 RETURN (lv_return_val );
2751
2752 END ret_item_name ;
2753
2754 /*=================================================================================+
2755 | DESCRIPTION : This function accepts Yes/No and returns the corresponding numeric|
2756 | value. |
2757 | 1-Yes |
2758 | 2-No |
2759 +==================================================================================*/
2760 PROCEDURE retn_yes_no_value
2761 (p_yes_no_code IN VARCHAR2,
2762 p_yes_no_value OUT NOCOPY NUMBER) IS
2763 TYPE c_cur_type IS REF CURSOR;
2764 c_cur c_cur_type;
2765 lv_sql_stmt VARCHAR2(1000):='';
2766 BEGIN
2767
2768 lv_sql_stmt:=
2769 'SELECT DECODE(SUBSTR(UPPER(NVL(:p_yes_no_code,''N'')),1,1),''Y'',1,2)'
2770 ||' FROM dual';
2771
2772 OPEN c_cur FOR lv_sql_stmt
2773 USING p_yes_no_code;
2774 FETCH c_cur INTO p_yes_no_value;
2775 CLOSE c_cur;
2776
2777 END retn_yes_no_value;
2778
2779
2780 /*=================================================================================+
2781 | DESCRIPTION : This function accepts user_id and returns the corresponding boolean |
2782 | value, stating that whether the user is an operator or not. |
2783 +===================================================================================*/
2784 FUNCTION IS_OPERATOR (p_user_id IN NUMBER)
2785 RETURN BOOLEAN IS
2786 lv_cnt NUMBER;
2787
2788 BEGIN
2789
2790 select COUNT(*) into lv_cnt
2791 FROM FND_USER_RESP_GROUPS
2792 WHERE user_id IN (select USER_ID from MSC_COMPANY_USERS where user_id = p_user_id AND company_id = 1)
2793 AND responsibility_id = (SELECT responsibility_id FROM FND_RESPONSIBILITY WHERE responsibility_key = 'MSCX_SC_ADMIN_FULL')
2794 AND SYSDATE between start_date and decode(end_date,NULL,SYSDATE) ;
2795
2796 IF lv_cnt > 0 THEN
2797 RETURN TRUE;
2798 ELSE
2799 RETURN FALSE;
2800 END IF;
2801
2802 END IS_OPERATOR;
2803
2804 /*=================================================================================+
2805 | DESCRIPTION : This procedure is used to explode the composite calendar from week |
2806 | level to the day level. |
2807 +===================================================================================*/
2808
2809 procedure Explode_Composite_Dates(
2810 errbuf OUT NOCOPY VARCHAR2,
2811 retcode OUT NOCOPY VARCHAR2,
2812 p_dest_table IN VARCHAR2,
2813 p_instance_id IN NUMBER,
2814 p_calendar_type_id IN NUMBER,
2815 p_calendar_code IN VARCHAR2,
2816 p_seq_num IN NUMBER,
2817 p_year IN VARCHAR2,
2818 p_year_description IN VARCHAR2,
2819 p_year_start_date IN DATE,
2820 p_year_end_date IN DATE,
2821 p_quarter IN VARCHAR2,
2822 p_quarter_description IN VARCHAR2,
2823 p_quarter_start_date IN DATE,
2824 p_quarter_end_date IN DATE,
2825 p_month IN VARCHAR2,
2826 p_month_description IN VARCHAR2,
2827 p_month_start_date IN DATE,
2828 p_month_end_date IN DATE,
2829 p_week IN VARCHAR2,
2830 p_week_description IN VARCHAR2,
2831 p_week_start_date IN DATE,
2832 p_week_end_date IN DATE ) IS
2833
2834 lv_num_of_days NUMBER;
2835 lv_current_date DATE ;
2836 lv_count NUMBER;
2837
2838 Begin
2839
2840 lv_count := p_week_end_date - p_week_start_date ;
2841
2842 if (p_dest_table = MSD_COMMON_UTILITIES.TIME_FACT_TABLE) then
2843
2844 For lv_num_of_days in 0..(p_week_end_date - p_week_start_date) LOOP
2845
2846 v_seq_num := v_seq_num + 1 ;
2847
2848 insert into msd_time (
2849 instance,
2850 calendar_type,
2851 calendar_code,
2852 seq_num,
2853 YEAR,
2854 YEAR_DESCRIPTION,
2855 YEAR_START_DATE,
2856 YEAR_END_DATE,
2857 QUARTER,
2858 QUARTER_DESCRIPTION,
2859 QUARTER_START_DATE,
2860 QUARTER_END_DATE,
2861 MONTH,
2862 MONTH_DESCRIPTION,
2863 MONTH_START_DATE,
2864 MONTH_END_DATE,
2865 WEEK,
2866 WEEK_DESCRIPTION,
2867 WEEK_START_DATE,
2868 WEEK_END_DATE,
2869 DAY,
2870 DAY_DESCRIPTION,
2871 LAST_UPDATE_DATE,
2872 last_updated_by,
2873 creation_date,
2874 created_by,
2875 LAST_UPDATE_LOGIN )
2876 values(
2877 p_instance_id,
2878 p_calendar_type_id,
2879 p_calendar_code,
2880 v_seq_num,
2881 p_year,
2882 p_year_description,
2883 p_year_start_date,
2884 p_year_end_date,
2885 p_quarter,
2886 p_quarter_description,
2887 p_quarter_start_date,
2888 p_quarter_end_date,
2889 p_month,
2890 p_month_description,
2891 p_month_start_date,
2892 p_month_end_date,
2893 p_week,
2894 p_week_description,
2895 p_week_start_date,
2896 p_week_end_date,
2897 p_week_start_date + lv_num_of_days,
2898 p_week_start_date + lv_num_of_days,
2899 sysdate,
2900 FND_GLOBAL.USER_ID ,
2901 sysdate,
2902 FND_GLOBAL.USER_ID ,
2903 FND_GLOBAL.USER_ID
2904 ) ;
2905
2906
2907 End Loop ;
2908
2909 elsif (p_dest_table = MSD_COMMON_UTILITIES.TIME_STAGING_TABLE) then
2910
2911 For lv_num_of_days in 0..(p_week_end_date - p_week_start_date) LOOP
2912
2913 v_seq_num := v_seq_num + 1 ;
2914
2915 insert into msd_st_time (
2916 instance,
2917 calendar_type,
2918 calendar_code,
2919 seq_num,
2920 YEAR,
2921 YEAR_DESCRIPTION,
2922 YEAR_START_DATE,
2923 YEAR_END_DATE,
2924 QUARTER,
2925 QUARTER_DESCRIPTION,
2926 QUARTER_START_DATE,
2927 QUARTER_END_DATE,
2928 MONTH,
2929 MONTH_DESCRIPTION,
2930 MONTH_START_DATE,
2931 MONTH_END_DATE,
2932 WEEK,
2933 WEEK_DESCRIPTION,
2934 WEEK_START_DATE,
2935 WEEK_END_DATE,
2936 DAY,
2937 DAY_DESCRIPTION,
2938 LAST_UPDATE_DATE,
2939 last_updated_by,
2940 creation_date,
2941 created_by,
2942 LAST_UPDATE_LOGIN )
2943 values(
2944 p_instance_id,
2945 p_calendar_type_id,
2946 p_calendar_code,
2947 v_seq_num,
2948 p_year,
2949 p_year_description,
2950 p_year_start_date,
2951 p_year_end_date,
2952 p_quarter,
2953 p_quarter_description,
2954 p_quarter_start_date,
2955 p_quarter_end_date,
2956 p_month,
2957 p_month_description,
2958 p_month_start_date,
2959 p_month_end_date,
2960 p_week,
2961 p_week_description,
2962 p_week_start_date,
2963 p_week_end_date,
2964 p_week_start_date + lv_num_of_days,
2965 p_week_start_date + lv_num_of_days,
2966 sysdate,
2967 FND_GLOBAL.USER_ID ,
2968 sysdate,
2969 FND_GLOBAL.USER_ID ,
2970 FND_GLOBAL.USER_ID
2971 ) ;
2972
2973 End Loop ;
2974
2975 End if ;
2976
2977
2978 exception
2979
2980 when others then
2981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_WARNING,substr(SQLERRM,1,1000));
2982 errbuf := substr(SQLERRM,1,150);
2983 retcode := -1 ;
2984
2985
2986 End Explode_Composite_Dates ;
2987
2988 FUNCTION DERIVE_SETUP_SR_LEVEL_PK
2989 (p_table_name VARCHAR2,
2990 p_level_val_col VARCHAR2, --level value col name
2991 p_level_pk_col VARCHAR2, --level_pk column name
2992 p_level_id_col VARCHAR2, --level_id col name
2993 p_instance_code VARCHAR2,
2994 p_instance_id NUMBER,
2995 p_message_text VARCHAR2 DEFAULT NULL,
2996 p_batch_id NUMBER DEFAULT NULL_VALUE,
2997 p_error_text OUT NOCOPY VARCHAR2)
2998 RETURN NUMBER IS
2999 lv_sql_stmt VARCHAR2(5000);
3000 lv_where_str VARCHAR2(100);
3001 lv_status NUMBER := 0;
3002 --lv_debug BOOLEAN := retn_debug_mode;
3003
3004 BEGIN
3005 v_sql_stmt := 01; --Items and Product Family
3006
3007 lv_sql_stmt :=
3008 'UPDATE '||p_table_name ||' t1'
3009 ||' SET '||p_level_pk_col
3010 ||' = (SELECT sr_inventory_item_id '
3011 ||' FROM msc_system_items t2'
3012 ||' WHERE t2.sr_instance_id = :p_instance_id'
3013 ||' AND t2.item_name = t1.'||p_level_val_col
3014 ||' AND t2.plan_id = -1 '
3015 ||' AND t2.bom_item_type <> 5 '
3016 ||' AND rownum = 1 )'
3017 ||' WHERE sr_instance_code = :p_instance_code'
3018 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3019 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3020 ||' = '||''''||NULL_CHAR||''''
3021 ||' AND level_id = 1 '
3022 ||' AND process_flag = '||G_IN_PROCESS;
3023
3024
3025 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3026
3027 EXECUTE IMMEDIATE lv_sql_stmt
3028 USING p_instance_id,
3029 p_instance_code,
3030 p_batch_id;
3031
3032 v_sql_stmt := 02; --Product Family
3033
3034 lv_sql_stmt :=
3035 'UPDATE '||p_table_name ||' t1'
3036 ||' SET '||p_level_pk_col
3037 ||' = (SELECT sr_inventory_item_id '
3038 ||' FROM msc_system_items t2'
3039 ||' WHERE t2.sr_instance_id = :p_instance_id'
3040 ||' AND t2.item_name = t1.'||p_level_val_col
3041 ||' AND t2.plan_id = -1 '
3042 ||' AND t2.bom_item_type = 5 '
3043 ||' AND rownum = 1 )'
3044 ||' WHERE sr_instance_code = :p_instance_code'
3045 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3046 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3047 ||' = '||''''||NULL_CHAR||''''
3048 ||' AND level_id = 3 '
3049 ||' AND process_flag = '||G_IN_PROCESS ;
3050
3051
3052 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3053
3054 EXECUTE IMMEDIATE lv_sql_stmt
3055 USING p_instance_id,
3056 p_instance_code,
3057 p_batch_id;
3058
3059 v_sql_stmt := 03; --Product Category
3060
3061 lv_sql_stmt :=
3062 'UPDATE '||p_table_name ||' t1'
3063 ||' SET '||p_level_pk_col
3064 ||' = (SELECT sr_category_id '
3065 ||' FROM msc_item_categories t2'
3066 ||' WHERE t2.sr_instance_id = :p_instance_id'
3067 ||' AND t2.category_name = t1.'||p_level_val_col
3068 ||' AND t2.category_set_id = ( select mcs.category_set_id '
3069 ||' from msc_category_sets mcs, msd_setup_parameters msp '
3070 ||' where msp.parameter_name = ''MSD_CATEGORY_SET_NAME_LEGACY'' '
3071 ||' and msp.instance_id = :p_instance_id '
3072 ||' and msp.parameter_value = mcs.category_set_name ) '
3073 /* ||' AND t2.category_set_id = ( select parameter_value '
3074 ||' from msd_setup_parameters '
3075 ||' where instance_id = :p_instance_id '
3076 ||' and parameter_name = ''MSD_CATEGORY_SET_NAME'' )' */
3077 ||' AND rownum = 1 )'
3078 ||' WHERE sr_instance_code = :p_instance_code'
3079 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3080 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3081 ||' = '||''''||NULL_CHAR||''''
3082 ||' AND level_id = 2 '
3083 ||' AND process_flag = '||G_IN_PROCESS ;
3084
3085 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3086
3087 EXECUTE IMMEDIATE lv_sql_stmt
3088 USING p_instance_id,
3089 p_instance_id,
3090 p_instance_code,
3091 p_batch_id;
3092
3093 v_sql_stmt := 04; --Organization
3094
3095 lv_sql_stmt :=
3096 'UPDATE '||p_table_name ||' t1'
3097 ||' SET '||p_level_pk_col
3098 ||' = (SELECT sr_tp_id '
3099 ||' FROM msc_trading_partners t2 '
3100 ||' WHERE t2.sr_instance_id = :p_instance_id '
3101 ||' AND substr(t2.partner_name,instr(t2.partner_name,'':'')+1,length(t2.partner_name)) = t1.'||p_level_val_col
3102 ||' AND t2.partner_type = 3 '
3103 ||' AND nvl(t2.company_id,-1) = -1 '
3104 ||' AND rownum = 1 ) '
3105 ||' WHERE sr_instance_code = :p_instance_code '
3106 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3107 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3108 ||' = '||''''||NULL_CHAR||''''
3109 ||' AND level_id = 7 '
3110 ||' AND process_flag = '||G_IN_PROCESS ;
3111
3112 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3113
3114 EXECUTE IMMEDIATE lv_sql_stmt
3115 USING p_instance_id,
3116 p_instance_code,
3117 p_batch_id;
3118
3119 v_sql_stmt := 05; --Ship To Location
3120
3121 lv_sql_stmt :=
3122 'UPDATE '||p_table_name ||' t1'
3123 ||' SET '||p_level_pk_col
3124 ||' = (SELECT t2.sr_tp_site_id '
3125 ||' FROM msc_tp_site_id_lid t2,msc_trading_partner_sites t3,msc_trading_partners t4 '
3126 ||' WHERE t4.partner_type = 2 '
3127 ||' AND t4.partner_name = substr(t1.'||p_level_val_col||',1,instr(t1.'||p_level_val_col||','':'')-1) '
3128 ||' AND nvl(t4.company_id,-1) = -1 '
3129 ||' AND t3.partner_id = t4.partner_id '
3130 ||' AND t3.location = substr(t1.'||p_level_val_col||',instr(t1.'||p_level_val_col||','':'')+1,length(t1.'||p_level_val_col||')) '
3131 ||' AND t3.tp_site_code = ''SHIP_TO'' '
3132 ||' AND t2.tp_site_id = t3.partner_site_id '
3133 ||' AND t2.partner_type = 2 '
3134 ||' AND t2.sr_instance_id = :p_instance_id'
3135 ||' AND nvl(t2.sr_company_id,-1) = -1 '
3136 ||' AND rownum = 1 )'
3137 ||' WHERE sr_instance_code = :p_instance_code'
3138 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3139 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3140 ||' = '||''''||NULL_CHAR||''''
3141 ||' AND level_id = 11 '
3142 ||' AND process_flag = '||G_IN_PROCESS;
3143
3144
3145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3146
3147 EXECUTE IMMEDIATE lv_sql_stmt
3148 USING p_instance_id,
3149 p_instance_code,
3150 p_batch_id;
3151
3152 v_sql_stmt := 06; --Customer
3153
3154 lv_sql_stmt :=
3155 'UPDATE '||p_table_name ||' t1'
3156 ||' SET '||p_level_pk_col
3157 ||' = (SELECT t2.sr_tp_id '
3158 ||' FROM msc_tp_id_lid t2,msc_trading_partners t3'
3159 ||' WHERE t2.sr_instance_id = :p_instance_id'
3160 ||' AND t3.partner_name = t1.'||p_level_val_col
3161 ||' AND t3.partner_type = 2 '
3162 ||' AND nvl(t3.company_id,-1) = -1 '
3163 ||' AND t2.partner_type = 2 '
3164 ||' AND t2.tp_id = t3.partner_id '
3165 ||' AND nvl(t2.sr_company_id,-1) = -1 '
3166 ||' AND rownum = 1 )'
3167 ||' WHERE sr_instance_code = :p_instance_code'
3171 ||' AND level_id = 15 '
3168 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3169 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3170 ||' = '||''''||NULL_CHAR||''''
3172 ||' AND process_flag = '||G_IN_PROCESS ;
3173
3174
3175
3176 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3177
3178 EXECUTE IMMEDIATE lv_sql_stmt
3179 USING p_instance_id,
3180 p_instance_code,
3181 p_batch_id;
3182
3183 v_sql_stmt := 07; --Demand Class
3184
3185 lv_sql_stmt :=
3186 'UPDATE '||p_table_name ||' t1'
3187 ||' SET '||p_level_pk_col
3188 ||' = (SELECT demand_class '
3189 ||' FROM msc_demand_classes t2'
3190 ||' WHERE t2.sr_instance_id = :p_instance_id'
3191 ||' AND t2.meaning = t1.'||p_level_val_col
3192 ||' AND rownum = 1 )'
3193 ||' WHERE sr_instance_code = :p_instance_code'
3194 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3195 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3196 ||' = '||''''||NULL_CHAR||''''
3197 ||' AND level_id = 34 '
3198 ||' AND process_flag = '||G_IN_PROCESS ;
3199
3200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3201
3202 EXECUTE IMMEDIATE lv_sql_stmt
3203 USING p_instance_id,
3204 p_instance_code,
3205 p_batch_id;
3206
3207 v_sql_stmt := 08; --Zone
3208
3209 lv_sql_stmt :=
3210 'UPDATE '||p_table_name ||' t1'
3211 ||' SET '||p_level_pk_col
3212 ||' = (SELECT t2.region_id '
3213 ||' FROM msc_regions t2'
3214 ||' WHERE t2.sr_instance_id = :p_instance_id'
3215 ||' AND t2.zone = t1.'||p_level_val_col
3216 ||' AND t2.region_type = 10'
3217 ||' AND rownum = 1 )'
3218 ||' WHERE sr_instance_code = :p_instance_code'
3219 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3220 ||' AND NVL(t1.'||p_level_pk_col||','||''''||NULL_CHAR||''''||') '
3221 ||' = '||''''||NULL_CHAR||''''
3222 ||' AND level_id = 42 '
3223 ||' AND process_flag = '||G_IN_PROCESS ;
3224
3225 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3226
3227 EXECUTE IMMEDIATE lv_sql_stmt
3228 USING p_instance_id,
3229 p_instance_code,
3230 p_batch_id;
3231
3232
3233
3234 RETURN(lv_status);
3235
3236 EXCEPTION
3237 WHEN too_many_rows THEN
3238 p_error_text := substr('MSC_ST_UTIL.DERIVE_SETUP_SR_LEVEL_PK'||'('
3239 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3240 return(SQLCODE);
3241
3242 WHEN OTHERS THEN
3243 p_error_text := substr('MSC_ST_UTIL.DERIVE_SETUP_SR_LEVEL_PK'||'('
3244 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3245 return(SQLCODE);
3246
3247 END DERIVE_SETUP_SR_LEVEL_PK;
3248
3249 /*==========================================================================+
3250 | DESCRIPTION : This function is called for deriving the resource id's |
3251 | based on |
3252 | the values in msc_local_id_setup. For the tables other |
3253 | than msc_st_resource_requirements, |
3254 | if the derivation fails then the |
3255 | record will be errored out. |
3256 | Severity - 1 - Error |
3257 | 2 - Warning |
3258 | 3 - Error if value for item name exists |
3259 +==========================================================================*/
3260 FUNCTION DERIVE_RESOURCE_ID
3261 (p_table_name VARCHAR2,
3262 p_resource_col_name VARCHAR2, --resource code
3263 p_department_col_name VARCHAR2, --department code
3264 p_resource_col_id VARCHAR2, --resource id
3265 p_instance_code VARCHAR2,
3266 p_error_text OUT NOCOPY VARCHAR2,
3267 p_batch_id NUMBER DEFAULT NULL_VALUE,
3268 p_severity NUMBER DEFAULT 0,
3269 p_message_text VARCHAR2 DEFAULT NULL,
3270 p_debug BOOLEAN DEFAULT FALSE,
3271 p_row LONG DEFAULT NULL,
3272 p_where_str VARCHAR2 DEFAULT NULL)
3273 RETURN NUMBER IS
3274 lv_sql_stmt VARCHAR2(5000);
3275 lv_where_str VARCHAR2(100);
3276 lv_status NUMBER := 0;
3277 BEGIN
3278 v_sql_stmt := 10;
3279
3280 IF v_instance_type = G_INS_OTHER THEN
3281
3282 lv_sql_stmt :=
3283 'UPDATE '|| p_table_name ||' msrr'
3284 ||' SET '||p_resource_col_id || '= (SELECT local_id '
3285 ||' FROM msc_local_id_setup mlis'
3286 ||' WHERE mlis.char1 = msrr.sr_instance_code'
3287 ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
3288 ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
3289 ||' AND mlis.char3 = msrr.organization_code'
3290 ||' AND mlis.char4 = msrr.'||p_resource_col_name
3291 ||' AND mlis.entity_name = ''RESOURCE_ID'''
3292 ||' AND mlis.instance_id ='||v_instance_id||' )'
3293 ||' WHERE msrr.sr_instance_code = :p_instance_code'
3294 ||' AND msrr.deleted_flag = '||SYS_NO
3298
3295 ||' AND msrr.process_flag ='|| G_IN_PROCESS
3296 ||' AND msrr.batch_id = :p_batch_id'
3297 || p_where_str;
3299 ELSE
3300
3301 lv_sql_stmt :=
3302 'update '|| p_table_name ||' msrr'
3303 ||' set '||p_resource_col_id || '= (select RESOURCE_ID '
3304 ||' from msc_department_resources mdr '
3305 ||' where mdr.ORGANIZATION_ID = msrr.ORGANIZATION_ID and '
3306 ||' mdr.SR_INSTANCE_ID = '||v_instance_id||' and '
3307 ||' mdr.department_code = msrr.'||p_department_col_name||' and '
3308 ||' mdr.RESOURCE_CODE = msrr.'||p_resource_col_name||' and mdr.plan_id = -1 and rownum = 1 )'
3309 ||' WHERE msrr.sr_instance_code = :p_instance_code'
3310 ||' AND msrr.deleted_flag = '||SYS_NO
3311 ||' AND msrr.process_flag ='|| G_IN_PROCESS
3312 ||' AND msrr.batch_id = :p_batch_id'
3313 || p_where_str;
3314 END IF;
3315
3316 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3317 EXECUTE IMMEDIATE lv_sql_stmt
3318 USING p_instance_code,
3319 p_batch_id;
3320
3321 IF p_message_text IS NOT NULL and p_severity = 1 THEN
3322 v_sql_stmt := 07;
3323 lv_sql_stmt :=
3324 'UPDATE '||p_table_name ||' t1'
3325 ||' SET error_text = '||''''||p_message_text||''''||','
3326 ||' process_flag = '||g_error
3327 ||' WHERE NVL(t1.'||p_resource_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3328 ||' AND sr_instance_code = :p_instance_code'
3329 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3330 ||' AND process_flag = ' ||G_IN_PROCESS;
3331
3332 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3333 EXECUTE IMMEDIATE lv_sql_stmt
3334 USING p_instance_code,
3335 p_batch_id;
3336
3337 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
3338 lv_where_str :=
3339 ' AND NVL('||p_resource_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
3340
3341 lv_status := LOG_ERROR(p_table_name => p_table_name,
3342 p_instance_code => p_instance_code,
3343 p_row => p_row,
3344 p_severity => p_severity,
3345 p_propagated => 'N',
3346 p_where_str => lv_where_str,
3347 p_message_text => p_message_text,
3348 p_error_text => p_error_text,
3349 p_batch_id => p_batch_id);
3350
3351 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
3352 v_sql_stmt := 08;
3353 lv_sql_stmt :=
3354 'UPDATE '||p_table_name ||' t1'
3355 ||' SET error_text = '||''''||p_message_text||''''||','
3356 ||' process_flag = '||g_error
3357 ||' WHERE NVL(t1.'||p_resource_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3358 ||' AND NVL(t1.'||p_resource_col_name||','||''''||NULL_CHAR||''''||') '
3359 ||' <> '||''''||NULL_CHAR||''''
3360 ||' AND sr_instance_code = :p_instance_code'
3361 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
3362 ||' AND process_flag = ' ||G_IN_PROCESS;
3363
3364 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3365 EXECUTE IMMEDIATE lv_sql_stmt
3366 USING p_instance_code,
3367 p_batch_id;
3368
3369 END IF;
3370 RETURN(lv_status);
3371
3372 EXCEPTION
3373 WHEN too_many_rows THEN
3374 p_error_text := substr('MSC_ST_UTIL.DERIVE_RESOURCE_ID'||'('
3375 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3376 return(SQLCODE);
3377
3378 WHEN OTHERS THEN
3379 p_error_text := substr('MSC_ST_UTIL.DERIVE_RESOURCE_ID'||'('
3380 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3381 return(SQLCODE);
3382 END DERIVE_RESOURCE_ID;
3383
3384 /*==========================================================================+
3385 | DESCRIPTION : This function is called for deriving the departmet id's |
3386 | based on |
3387 | the values in msc_local_id_setup. For the tables other |
3388 | than msc_st_resource_requirements, |
3389 | if the derivation fails then the |
3390 | record will be errored out. |
3391 | Severity - 1 - Error |
3392 | 2 - Warning |
3393 | 3 - Error if value for item name exists |
3394 +==========================================================================*/
3395 FUNCTION DERIVE_DEPARTMENT_ID
3396 (p_table_name VARCHAR2,
3397 p_resource_col_name VARCHAR2, --resource code
3398 p_department_col_name VARCHAR2, --department code
3399 p_department_col_id VARCHAR2, --department id
3400 p_instance_code VARCHAR2,
3401 p_error_text OUT NOCOPY VARCHAR2,
3402 p_batch_id NUMBER DEFAULT NULL_VALUE,
3403 p_severity NUMBER DEFAULT 0,
3404 p_message_text VARCHAR2 DEFAULT NULL,
3405 p_debug BOOLEAN DEFAULT FALSE,
3406 p_row LONG DEFAULT NULL)
3407 RETURN NUMBER IS
3408 lv_sql_stmt VARCHAR2(5000);
3409 lv_where_str VARCHAR2(100);
3410 lv_status NUMBER := 0;
3411 BEGIN
3415
3412 v_sql_stmt := 12;
3413
3414 IF v_instance_type = G_INS_OTHER THEN
3416 lv_sql_stmt :=
3417 'UPDATE '|| p_table_name ||' msrr'
3418 ||' SET '||p_department_col_id || '= (SELECT local_id '
3419 ||' FROM msc_local_id_setup mlis'
3420 ||' WHERE mlis.char1 = msrr.sr_instance_code'
3421 ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
3422 ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
3423 ||' AND mlis.char3 = msrr.organization_code'
3424 ||' AND mlis.char4 = msrr.'||p_department_col_name
3425 ||' AND mlis.entity_name = ''DEPARTMENT_ID'''
3426 ||' AND mlis.instance_id ='||v_instance_id||' )'
3427 ||' WHERE msrr.sr_instance_code = :p_instance_code'
3428 ||' AND msrr.process_flag ='|| G_IN_PROCESS
3429 ||' AND NVL(msrr.'||p_department_col_id||','||NULL_VALUE||') <> -1'
3433
3430 ||' AND msrr.batch_id = :p_batch_id';
3431
3432 ELSE
3434 lv_sql_stmt :=
3435 'update '|| p_table_name ||' msrr'
3436 ||' set '||p_department_col_id || '= (select department_ID '
3437 ||' from msc_department_resources mdr '
3438 ||' where mdr.ORGANIZATION_ID = msrr.ORGANIZATION_ID and '
3439 ||' mdr.SR_INSTANCE_ID = '||v_instance_id||' and '
3440 ||' mdr.department_code = msrr.'||p_department_col_name||' and '
3441 ||' mdr.RESOURCE_CODE = msrr.'||p_resource_col_name||' and mdr.plan_id = -1 and rownum = 1 )'
3442 ||' WHERE msrr.sr_instance_code = :p_instance_code'
3443 ||' AND msrr.process_flag ='|| G_IN_PROCESS
3444 ||' AND NVL(msrr.'||p_department_col_id||','||NULL_VALUE||') <> -1'
3445 ||' AND msrr.batch_id = :p_batch_id';
3446 END IF;
3447 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3448 EXECUTE IMMEDIATE lv_sql_stmt
3449 USING p_instance_code,
3450 p_batch_id;
3451
3452 IF p_message_text IS NOT NULL and p_severity = 1 THEN
3453 v_sql_stmt := 07;
3454 lv_sql_stmt :=
3455 'UPDATE '||p_table_name ||' t1'
3456 ||' SET error_text = '||''''||p_message_text||''''||','
3457 ||' process_flag = '||g_error
3458 ||' WHERE NVL(t1.'||p_department_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3459 ||' AND sr_instance_code = :p_instance_code'
3460 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3461 ||' AND process_flag = ' ||G_IN_PROCESS;
3462
3463 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3464 EXECUTE IMMEDIATE lv_sql_stmt
3465 USING p_instance_code,
3466 p_batch_id;
3467
3468 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
3469 lv_where_str :=
3470 ' AND NVL('||p_department_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
3471
3472 lv_status := LOG_ERROR(p_table_name => p_table_name,
3473 p_instance_code => p_instance_code,
3474 p_row => p_row,
3475 p_severity => p_severity,
3476 p_propagated => 'N',
3477 p_where_str => lv_where_str,
3478 p_message_text => p_message_text,
3479 p_error_text => p_error_text,
3480 p_batch_id => p_batch_id);
3481
3482 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
3483 v_sql_stmt := 08;
3484 lv_sql_stmt :=
3485 'UPDATE '||p_table_name ||' t1'
3486 ||' SET error_text = '||''''||p_message_text||''''||','
3487 ||' process_flag = '||g_error
3488 ||' WHERE NVL(t1.'||p_department_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3489 ||' AND NVL(t1.'||p_department_col_name||','||''''||NULL_CHAR||''''||') '
3490 ||' <> '||''''||NULL_CHAR||''''
3491 ||' AND sr_instance_code = :p_instance_code'
3492 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
3493 ||' AND process_flag = ' ||G_IN_PROCESS;
3494
3495 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3496 EXECUTE IMMEDIATE lv_sql_stmt
3497 USING p_instance_code,
3498 p_batch_id;
3499
3500 END IF;
3501 RETURN(lv_status);
3502
3503 EXCEPTION
3504 WHEN too_many_rows THEN
3505 p_error_text := substr('MSC_ST_UTIL.DERIVE_DEPARTMENT_ID'||'('
3506 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3507 return(SQLCODE);
3508
3509 WHEN OTHERS THEN
3513 END DERIVE_DEPARTMENT_ID;
3510 p_error_text := substr('MSC_ST_UTIL.DERIVE_DEPARTMENT_ID'||'('
3511 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3512 return(SQLCODE);
3514
3515 /*==========================================================================+
3516 | DESCRIPTION : This function is called for deriving the |
3517 | operatin sequence id's |
3518 | based on |
3519 | the values in msc_local_id_setup. For the tables other |
3520 | than msc_st_resource_requirements, |
3521 | if the derivation fails then the |
3522 | record will be errored out. |
3523 | Severity - 1 - Error |
3524 | 2 - Warning |
3525 | 3 - Error if value for item name exists |
3526 +==========================================================================*/
3527 FUNCTION DERIVE_OPERATION_SEQUENCE_ID
3528 (p_table_name VARCHAR2,
3529 p_operation_seq_num VARCHAR2, --OPERATION_SEQ_NUM
3530 p_routing_col_name VARCHAR2, --ROUTING_NAME
3531 p_sequence_col_id VARCHAR2, --OPERATION_SEQUENCE_ID
3532 p_op_effectivity_date VARCHAR2, --operation_effectivity_date
3533 p_operation_seq_code VARCHAR2, --operation_seq_code
3534 p_routing_sequence_id VARCHAR2, --routing_sequence_id
3535 p_instance_code VARCHAR2,
3536 p_error_text OUT NOCOPY VARCHAR2,
3537 p_batch_id NUMBER DEFAULT NULL_VALUE,
3538 p_severity NUMBER DEFAULT 0,
3539 p_message_text VARCHAR2 DEFAULT NULL,
3540 p_debug BOOLEAN DEFAULT FALSE,
3541 p_row LONG DEFAULT NULL)
3542 RETURN NUMBER IS
3543 lv_sql_stmt VARCHAR2(5000);
3544 lv_where_str VARCHAR2(100);
3545 lv_status NUMBER := 0;
3546 BEGIN
3547
3548 IF v_instance_type <> G_INS_OTHER THEN
3549 v_sql_stmt := 15;
3550 lv_sql_Stmt :=
3551 'update '||p_table_name||' msrr'
3552 ||' set ' ||p_operation_seq_num||' = to_number(decode(length(rtrim('||p_operation_seq_code||',''0123456789'')),'
3553 ||' NULL,'||p_operation_seq_code||',''1'')),'
3554 ||p_sequence_col_id||' = (select operation_sequence_id '
3555 ||' from msc_routing_operations mro '
3556 ||' where mro.routing_sequence_id = msrr.'||p_routing_sequence_id||' and '
3557 ||' mro.effectivity_date = msrr.'||p_op_effectivity_date||' and '
3558 ||' mro.operation_seq_num = to_number(decode(length(rtrim(msrr.'||p_operation_seq_code||',''0123456789'')),'
3559 ||' NULL,msrr.'||p_operation_seq_code||',''1'')) and'
3563 ||' AND batch_id = :p_batch_id'
3560 ||' mro.SR_INSTANCE_ID = '||v_instance_id ||' and mro.plan_id = -1 and mro.operation_type = 1)'
3561 ||' WHERE sr_instance_code = :p_instance_code'
3562 ||' AND process_flag ='||G_IN_PROCESS
3564 ||' AND msrr.'||p_sequence_col_id||' IS NULL';
3565
3566 ELSE
3567
3568 v_sql_stmt := 16;
3569 lv_sql_Stmt :=
3570 'UPDATE '||p_table_name||' msrr'
3571 ||' SET '||p_sequence_col_id||'= (SELECT local_id'
3572 ||' FROM msc_local_id_setup mlis'
3573 ||' WHERE mlis.char1 = msrr.sr_instance_code'
3574 ||' AND NVL(mlis.char2,'||''''||NULL_CHAR||''''||') '
3575 ||' = NVL(msrr.company_name,'||''''||NULL_CHAR||''''||') '
3576 ||' AND mlis.char3 = msrr.organization_code'
3577 ||' AND mlis.char4 = msrr.'||p_routing_col_name
3578 ||' AND mlis.char5 = msrr.'||p_operation_seq_code
3579 ||' AND NVL(mlis.char6,'||''''||NULL_CHAR||''''||') '
3580 ||' = NVL(msrr.alternate_routing_designator,'||''''||NULL_CHAR||''''||')'
3581 ||' AND mlis.date1 = msrr.'||p_op_effectivity_date
3582 ||' AND mlis.entity_name = ''OPERATION_SEQUENCE_ID'' '
3583 ||' AND mlis.instance_id = '||v_instance_id||')'
3584 ||' WHERE sr_instance_code = :p_instance_code'
3585 ||' AND process_flag ='||G_IN_PROCESS
3586 ||' AND batch_id = :lp_batch_id';
3587
3588 END IF;
3589
3590 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3591
3592
3593 EXECUTE IMMEDIATE lv_sql_stmt USING p_instance_code,p_batch_id;
3594
3595
3596 IF p_message_text IS NOT NULL and p_severity = 1 THEN
3597 v_sql_stmt := 07;
3598 lv_sql_stmt :=
3599 'UPDATE '||p_table_name ||' t1'
3600 ||' SET error_text = '||''''||p_message_text||''''||','
3601 ||' process_flag = '||g_error
3602 ||' WHERE NVL(t1.'||p_sequence_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3603 ||' AND sr_instance_code = :p_instance_code'
3604 ||' AND NVL(batch_id,'||NULL_VALUE||') = :p_batch_id'
3605 ||' AND process_flag = ' ||G_IN_PROCESS;
3606
3607 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3608 EXECUTE IMMEDIATE lv_sql_stmt
3609 USING p_instance_code,
3610 p_batch_id;
3611
3612 ELSIF p_message_text IS NOT NULL and p_severity = 2 THEN
3613 lv_where_str :=
3614 ' AND NVL('||p_sequence_col_id||','||NULL_VALUE||') = '||NULL_VALUE;
3615
3616 lv_status := LOG_ERROR(p_table_name => p_table_name,
3617 p_instance_code => p_instance_code,
3618 p_row => p_row,
3619 p_severity => p_severity,
3620 p_propagated => 'N',
3621 p_where_str => lv_where_str,
3622 p_message_text => p_message_text,
3623 p_error_text => p_error_text,
3624 p_batch_id => p_batch_id);
3625
3626 ELSIF p_message_text IS NOT NULL and p_severity = 3 THEN
3627 v_sql_stmt := 08;
3628 lv_sql_stmt :=
3629 'UPDATE '||p_table_name ||' t1'
3630 ||' SET error_text = '||''''||p_message_text||''''||','
3631 ||' process_flag = '||g_error
3632 ||' WHERE NVL(t1.'||p_sequence_col_id||','||NULL_VALUE||') = '||NULL_VALUE
3633 ||' AND NVL(t1.'||p_operation_seq_code||','||''''||NULL_CHAR||''''||') '
3634 ||' <> '||''''||NULL_CHAR||''''
3635 ||' AND sr_instance_code = :p_instance_code'
3636 ||' AND NVL(batch_id,'||NULL_VALUE||')= :p_batch_id'
3637 ||' AND process_flag = ' ||G_IN_PROCESS;
3638
3639 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_DEBUG_1,lv_sql_stmt);
3640 EXECUTE IMMEDIATE lv_sql_stmt
3641 USING p_instance_code,
3642 p_batch_id;
3643
3644 END IF;
3645 RETURN(lv_status);
3646
3647 EXCEPTION
3648 WHEN too_many_rows THEN
3649 p_error_text := substr('MSC_ST_UTIL.DERIVE_OPERATION_SEQUENCE_ID'||'('
3650 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3651 return(SQLCODE);
3652
3653 WHEN OTHERS THEN
3654 p_error_text := substr('MSC_ST_UTIL.DERIVE_OPERATION_SEQUENCE_ID'||'('
3655 ||v_sql_stmt||')'|| SQLERRM, 1, 240);
3656 return(SQLCODE);
3657 END DERIVE_OPERATION_SEQUENCE_ID;
3658
3659 /*S_OP */
3660 FUNCTION CHECK_DP_ENABLED_FLAG
3661 (
3662 p_MRP_PLANNING_CODE NUMBER,
3663 p_PICK_COMPONENT_FLAG VARCHAR2,
3664 p_MSD_PLANING_PERCENTAGE NUMBER,
3665 p_ATO_FORECAST_CONTROL NUMBER
3666 )
3667 RETURN NUMBER
3668 IS
3669 lv_dp_enabled_flag NUMBER ;
3670 BEGIN
3671
3672 If ((nvl(p_MRP_PLANNING_CODE,0)<> 6 OR nvl(p_PICK_COMPONENT_FLAG,'N') ='Y') AND (nvl(p_MSD_PLANING_PERCENTAGE,0)= 4 OR nvl(p_ATO_FORECAST_CONTROL,0) <> 3))
3673 Then
3674 lv_dp_enabled_flag:=1;
3675 Else
3676 lv_dp_enabled_flag:=2;
3677 End IF;
3678
3679 return lv_dp_enabled_flag;
3680 END CHECK_DP_ENABLED_FLAG;
3681 /*S_OP */
3682
3683 FUNCTION get_stream_name (x varchar2) return varchar2
3684 IS
3685 stream_name varchar2(100);
3686 begin
3687 select decode(upper(x),'TOTAL_BACKLOG','MSD_TOTAL_BACKLOG','PASTDUE_BACKLOG','MSD_PASTDUE_BACKLOG'
3688 ,'PRODUCTION_PLAN','MSD_PRODUCTION_PLAN','ACTUAL_PRODUCTION','MSD_ACTUAL_PRODUCTION','ONHAND_INVENTORY','MSD_ONHAND_INVENTORY'
3692
3689 ,'SUPPLY_PLANS','MSD_SUPPLY_PLANS','CONSTRAINED_FORECAST','MSD_CONSTRAINED_FORECAST','SAFETY_STOCKS','MSD_SAFETY_STOCKS'
3690 ,'AVAIL_RESOURCE_CAPACITY','MSD_AVAIL_RESOURCE_CAPACITY','AVAIL_SUPPLIER_CAPACITY','MSD_AVAIL_SUPPLIER_CAPACITY'
3691 ,'WORK_IN_PROCESS','MSD_WORK_IN_PROCESS',x) into stream_name from dual;
3693 return stream_name;
3694 end get_stream_name;
3695
3696
3697 END MSC_ST_UTIL;