[Home] [Help]
PACKAGE BODY: APPS.RLM_MESSAGE_SV
Source
1 PACKAGE BODY RLM_MESSAGE_SV as
2 /* $Header: RLMCOMSB.pls 120.2.12000000.2 2007/09/03 13:52:41 sunilku ship $ */
3 /*========================== rlm_message_sv ============================*/
4
5 /*===========================================================================
6
7 PROCEDURE NAME: app_error
8 app_error will just put in the details in the g_message_tab
9 all the values will be written to the table at one shot when the dump messages
10 is called after a group of records is processed
11 --
12 The validation type variable which is accepted in this proc will be used to
13 update the dependency table with an error for that validationType
14 a null could be passed in to the validation type and then no dependency check
15 will be done
16 ===========================================================================*/
17 -- added grouping info for bug 4198330
18
19 PROCEDURE app_error (x_ExceptionLevel IN VARCHAR2,
20 x_MessageName IN VARCHAR2,
21 x_ChildMessageName IN VARCHAR2,
22 x_InterfaceHeaderId IN NUMBER,
23 x_InterfaceLineId IN NUMBER,
24 x_ScheduleHeaderId IN NUMBER,
25 x_ScheduleLineId IN NUMBER,
26 x_OrderHeaderId IN NUMBER,
27 x_OrderLineId IN NUMBER,
28 x_ErrorText IN VARCHAR2,
29 x_ValidationType IN VARCHAR2,
30 x_GroupInfo IN BOOLEAN,
31 x_ShipfromOrgId IN NUMBER,
32 x_ShipToAddressId IN NUMBER,
33 x_CustomerItemId IN NUMBER,
34 x_InventoryItemId IN NUMBER,
35 x_token1 IN VARCHAR2,
36 x_value1 IN VARCHAR2,
37 x_token2 IN VARCHAR2,
38 x_value2 IN VARCHAR2,
39 x_token3 IN VARCHAR2,
40 x_value3 IN VARCHAR2,
41 x_token4 IN VARCHAR2,
42 x_value4 IN VARCHAR2,
43 x_token5 IN VARCHAR2,
44 x_value5 IN VARCHAR2,
45 x_token6 IN VARCHAR2,
46 x_value6 IN VARCHAR2,
47 x_token7 IN VARCHAR2, -- Bug 4297984
48 x_value7 IN VARCHAR2,
49 x_token8 IN VARCHAR2,
50 x_value8 IN VARCHAR2,
51 x_token9 IN VARCHAR2,
52 x_value9 IN VARCHAR2,
53 x_token10 IN VARCHAR2,
54 x_value10 IN VARCHAR2)
55
56 IS
57
58 x_text VARCHAR2(2000) := NULL;
59 v_progress VARCHAR2(3) := '010';
60 v_message_rec t_message_rec;
61 v_incr NUMBER;
62 --
63 BEGIN
64
65 v_message_rec.exception_level := x_ExceptionLevel;
66 v_message_rec.message_name := x_MessageName;
67 v_message_rec.child_message_name := x_ChildMessageName;
68 v_message_rec.error_text := x_ErrorText;
69 v_message_rec.interface_header_id := x_InterfaceHeaderId;
70 v_message_rec.interface_line_id := x_InterfaceLineId;
71 v_message_rec.schedule_header_id := x_ScheduleHeaderId;
72 v_message_rec.schedule_line_id := x_ScheduleLineId;
73 v_message_rec.order_header_id := x_OrderHeaderId;
74 v_message_rec.order_line_id := x_OrderLineId;
75 v_message_rec.group_Info := x_GroupInfo;
76 /* Bug 4198330 */
77 v_message_rec.ship_from_org_id := x_ShipFromOrgId;
78 v_message_rec.ship_to_address_id := x_ShipToAddressId;
79 v_message_rec.customer_item_id := x_CustomerItemId;
80 v_message_rec.inventory_item_id := x_InventoryItemId;
81 --
82 -- This is for conditions when the user will not have any error codes
83 -- defined and the app error is called directly with the text in the
84 -- message
85 --
86 IF x_ErrorText is null THEN
87 --
88 get_msg_text(x_MessageName,
89 v_message_rec.error_text,
90 x_token1,
91 x_value1,
92 x_token2,
93 x_value2,
94 x_token3,
95 x_value3,
96 x_token4,
97 x_value4,
98 x_token5,
99 x_value5,
100 x_token6,
101 x_value6,
102 x_token7, -- Bug 4297984
103 x_value7,
104 x_token8,
105 x_value8,
106 x_token9,
107 x_value9,
108 x_token10,
109 x_value10);
110
111 -- v_message_rec.error_text := x_MessageName || ': ' || v_message_rec.error_text;
112 --
113 ELSE
114 v_message_rec.error_text := x_ErrorText;
115 END IF;
116 --
117 v_incr := g_message_tab.COUNT + 1;
118 --
119 -- set the message in the table
120 --
121 g_message_tab(v_incr) := v_message_rec;
122 --
123 IF x_ValidationType IS NOT NULL THEN
124 --
125 -- set the dependent error for this val type
126 set_dependent_error(x_ValidationType);
127 --
128 END IF;
129 --
130 EXCEPTION
131 WHEN OTHERS THEN
132 sql_error('rlm_message_sv.app_error', v_progress);
133 RAISE;
134
135 END app_error;
136
137
138
139
140 /*===========================================================================
141
142 PROCEDURE NAME: app_purge_error
143
144 ===========================================================================*/
145
146
147 PROCEDURE app_purge_error (x_ExceptionLevel IN VARCHAR2,
148 x_MessageName IN VARCHAR2,
149 x_ErrorText IN VARCHAR2,
150 x_ChildMessageName IN VARCHAR2,
151 x_InterfaceHeaderId IN NUMBER,
152 x_InterfaceLineId IN NUMBER,
153 x_ScheduleHeaderId IN NUMBER,
154 x_ScheduleLineId IN NUMBER,
155 x_OrderHeaderId IN NUMBER,
156 x_OrderLineId IN NUMBER,
157 x_ScheduleLineNum IN NUMBER, --bugfix 6319027
158 x_ValidationType IN VARCHAR2,
159 x_token1 IN VARCHAR2,
160 x_value1 IN VARCHAR2,
161 x_token2 IN VARCHAR2,
162 x_value2 IN VARCHAR2,
163 x_token3 IN VARCHAR2,
164 x_value3 IN VARCHAR2,
165 x_token4 IN VARCHAR2,
166 x_value4 IN VARCHAR2,
167 x_token5 IN VARCHAR2,
168 x_value5 IN VARCHAR2,
169 x_token6 IN VARCHAR2,
170 x_value6 IN VARCHAR2,
171 x_token7 IN VARCHAR2, -- Bug 4297984
172 x_value7 IN VARCHAR2,
173 x_token8 IN VARCHAR2,
174 x_value8 IN VARCHAR2,
175 x_token9 IN VARCHAR2,
176 x_value9 IN VARCHAR2,
177 x_token10 IN VARCHAR2,
178 x_value10 IN VARCHAR2,
179 x_user_id IN NUMBER,
180 x_conc_req_id IN NUMBER,
181 x_prog_appl_id IN NUMBER,
182 x_conc_program_id IN NUMBER,
183 x_PurgeStatus IN VARCHAR2,
184 x_PurgeExp_rec IN t_PurExp_rec)
185
186 IS
187
188 x_text VARCHAR2(2000) := NULL;
189 v_message_rec t_message_rec;
190 v_conc_req NUMBER;
191 v_purge VARCHAR2(255);
192 v_purge_rec t_PurExp_rec;
193
194 --
195 BEGIN
196
197 v_message_rec.exception_level := x_ExceptionLevel;
198 v_message_rec.message_name := x_MessageName;
199 v_message_rec.child_message_name := x_ChildMessageName;
200 v_message_rec.error_text := x_ErrorText;
201 v_message_rec.interface_header_id := x_InterfaceHeaderId;
202 v_message_rec.interface_line_id := x_InterfaceLineId;
203 v_message_rec.schedule_header_id := x_ScheduleHeaderId;
204 v_message_rec.schedule_line_id := x_ScheduleLineId;
205 v_message_rec.order_header_id := x_OrderHeaderId;
206 v_message_rec.order_line_id := x_OrderLineId;
207 v_message_rec.Schedule_line_number := x_ScheduleLineNum; --bugfix 6319027
208 v_conc_req := x_conc_req_id;
209 v_purge := x_PurgeStatus;
210 v_purge_rec := x_PurgeExp_rec;
211
212 IF x_MessageName is not null THEN
213 --
214 get_msg_text(x_MessageName,
215 v_message_rec.error_text,
216 x_token1,
217 x_value1,
218 x_token2,
219 x_value2,
220 x_token3,
221 x_value3,
222 x_token4,
223 x_value4,
224 x_token5,
225 x_value5,
226 x_token6,
227 x_value6,
228 x_token7, -- Bug 4297984
229 x_value7,
230 x_token8,
231 x_value8,
232 x_token9,
233 x_value9,
234 x_token10,
235 x_value10);
236
237 rlm_message_sv.insert_purge_row (x_ExceptionLevel =>v_message_rec.exception_level,
238 x_MessageName =>v_message_rec.message_name,
239 x_ErrorText =>v_message_rec.error_text,
240 x_ScheduleHeaderId =>v_message_rec.schedule_header_id,
241 x_ScheduleLineId =>v_message_rec.schedule_line_id,
242 x_OrderHeaderId =>v_message_rec.order_header_id,
243 x_OrderLineId => v_message_rec.order_line_id,
244 x_ScheduleLineNum => v_message_rec.Schedule_line_number, --bugfix 6319027
245 x_conc_req_id =>v_conc_req ,
246 x_PurgeStatus =>v_purge ,
247 x_PurgeExp_rec=>v_purge_rec );
248
249
250
251
252 END IF;
253 --
254 EXCEPTION
255 WHEN OTHERS THEN
256 RAISE;
257
258 END app_purge_error;
259
260
261
262 /*===========================================================================
263
264 PROCEDURE NAME: get_msg_text
265
266 ===========================================================================*/
267
268 PROCEDURE get_msg_text (x_message_name IN VARCHAR2,
269 x_text IN OUT NOCOPY VARCHAR2,
270 x_token1 IN VARCHAR2,
271 x_value1 IN VARCHAR2,
272 x_token2 IN VARCHAR2,
273 x_value2 IN VARCHAR2,
274 x_token3 IN VARCHAR2,
275 x_value3 IN VARCHAR2,
276 x_token4 IN VARCHAR2,
277 x_value4 IN VARCHAR2,
278 x_token5 IN VARCHAR2,
279 x_value5 IN VARCHAR2,
280 x_token6 IN VARCHAR2,
281 x_value6 IN VARCHAR2,
282 x_token7 IN VARCHAR2, -- Bug 4297984
283 x_value7 IN VARCHAR2,
284 x_token8 IN VARCHAR2,
285 x_value8 IN VARCHAR2,
286 x_token9 IN VARCHAR2,
287 x_value9 IN VARCHAR2,
288 x_token10 IN VARCHAR2,
289 x_value10 IN VARCHAR2
290 )
291
292 IS
293 BEGIN
294
295 /*
296 ** Build the message string.
297 */
298
299 fnd_message.set_name ('RLM', x_message_name);
300
301 /*
302 ** Replace the tokens.
303 */
304
305 IF (x_token1 is NULL) THEN
306 null;
307
308 ELSIF (x_token2 is NULL) THEN
309 fnd_message.set_token (x_token1, SUBSTR(x_value1,1,2000));
310 null;
311
312 ELSIF (x_token3 is NULL) THEN
313 fnd_message.set_token (x_token1, x_value1);
314 null;
315 fnd_message.set_token (x_token2, x_value2);
316
317 ELSIF (x_token4 is NULL) THEN
318 fnd_message.set_token (x_token1, x_value1);
319 fnd_message.set_token (x_token2, x_value2);
320 fnd_message.set_token (x_token3, x_value3);
321 null;
322
323 ELSIF (x_token5 is NULL) THEN
324 fnd_message.set_token (x_token1, x_value1);
325 fnd_message.set_token (x_token2, x_value2);
326 fnd_message.set_token (x_token3, x_value3);
327 fnd_message.set_token (x_token4, x_value4);
328 null;
329
330 ELSIF (x_token6 is NULL) THEN
331 fnd_message.set_token (x_token1, x_value1);
332 fnd_message.set_token (x_token2, x_value2);
333 fnd_message.set_token (x_token3, x_value3);
334 fnd_message.set_token (x_token4, x_value4);
335 fnd_message.set_token (x_token5, x_value5);
336 null;
337 -- Bug 4297984
338 ELSIF (x_token7 is NULL) THEN
339 fnd_message.set_token (x_token1, x_value1);
340 fnd_message.set_token (x_token2, x_value2);
341 fnd_message.set_token (x_token3, x_value3);
342 fnd_message.set_token (x_token4, x_value4);
343 fnd_message.set_token (x_token5, x_value5);
344 fnd_message.set_token (x_token6, x_value6);
345 null;
346
347 ELSIF (x_token8 is NULL) THEN
348 fnd_message.set_token (x_token1, x_value1);
349 fnd_message.set_token (x_token2, x_value2);
350 fnd_message.set_token (x_token3, x_value3);
351 fnd_message.set_token (x_token4, x_value4);
352 fnd_message.set_token (x_token5, x_value5);
353 fnd_message.set_token (x_token6, x_value6);
354 fnd_message.set_token (x_token7, x_value7);
355 null;
356
357 ELSIF (x_token9 is NULL) THEN
358 fnd_message.set_token (x_token1, x_value1);
359 fnd_message.set_token (x_token2, x_value2);
360 fnd_message.set_token (x_token3, x_value3);
361 fnd_message.set_token (x_token4, x_value4);
362 fnd_message.set_token (x_token5, x_value5);
363 fnd_message.set_token (x_token6, x_value6);
364 fnd_message.set_token (x_token7, x_value7);
365 fnd_message.set_token (x_token8, x_value8);
366 null;
367
368 ELSIF (x_token10 is NULL) THEN
369 fnd_message.set_token (x_token1, x_value1);
370 fnd_message.set_token (x_token2, x_value2);
371 fnd_message.set_token (x_token3, x_value3);
372 fnd_message.set_token (x_token4, x_value4);
373 fnd_message.set_token (x_token5, x_value5);
374 fnd_message.set_token (x_token6, x_value6);
375 fnd_message.set_token (x_token7, x_value7);
376 fnd_message.set_token (x_token8, x_value8);
377 fnd_message.set_token (x_token9, x_value9);
378 null;
379
380 ELSE
381 fnd_message.set_token (x_token1, x_value1);
382 fnd_message.set_token (x_token2, x_value2);
383 fnd_message.set_token (x_token3, x_value3);
384 fnd_message.set_token (x_token4, x_value4);
385 fnd_message.set_token (x_token5, x_value5);
386 fnd_message.set_token (x_token6, x_value6);
387 fnd_message.set_token (x_token7, x_value7);
388 fnd_message.set_token (x_token8, x_value8);
389 fnd_message.set_token (x_token9, x_value9);
390 fnd_message.set_token (x_token10, x_value10);
391 null;
392
393 END IF;
394
395 /*
396 ** Retrieve the error message.
397 */
398
399 x_text := fnd_message.get;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 RAISE;
404
405 END get_msg_text;
406
407 /*===========================================================================
408
409 PROCEDURE NAME: insert_row
410
411 ===========================================================================*/
412
413 PROCEDURE insert_row (
414 x_ExceptionLevel IN VARCHAR2,
415 x_MessageName IN VARCHAR2,
416 x_ErrorText IN VARCHAR2,
417 x_InterfaceHeaderId IN NUMBER,
418 x_InterfaceLineId IN NUMBER,
419 x_ScheduleHeaderId IN NUMBER,
420 x_ScheduleLineId IN NUMBER,
421 x_OrderHeaderId IN NUMBER,
422 x_OrderLineId IN NUMBER,
423 x_GroupInfo IN BOOLEAN,
424 x_user_id IN NUMBER,
425 x_conc_req_id IN NUMBER,
426 x_prog_appl_id IN NUMBER,
427 x_conc_program_id IN NUMBER,
428 x_PurgeStatus IN VARCHAR2
429 )
430
431 IS
432 --
433 v_MessageText VARCHAR2(5000) := NULL;
434 v_ExceptionId NUMBER := NULL;
435 v_progress VARCHAR2(3) := '010';
436 v_info_txt VARCHAR2(2000) := NULL;
437 v_warn_txt VARCHAR2(2000) := NULL;
438 v_error_txt VARCHAR2(2000) := NULL;
439 v_ProgramDate DATE := NULL;
440 v_LoginId NUMBER := fnd_global.login_id;
441 v_inv_item VARCHAR2(50);
442 v_Exception_rec t_exception_rec;
443 v_Exception_rec1 t_exception_rec;
444 v_shipTo hz_cust_acct_sites.ece_tp_location_code%TYPE;
445 v_BillTo hz_cust_acct_sites.ece_tp_location_code%TYPE;
446 v_IntrmdShipTo hz_cust_acct_sites.ece_tp_location_code%TYPE;
447 --
448 --C_SDEBUG NUMBER :=rlm_core_sv.C_LEVEL5;
449 --C_DEBUG NUMBER :=rlm_core_sv.C_LEVEL6;
450 --
451 CURSOR c_GetShipTo IS
452 SELECT ece_tp_location_code
453 FROM hz_cust_acct_sites_all acct_site,
454 rlm_interface_lines_all lines
455 WHERE lines.ship_to_address_id = acct_site.cust_acct_site_id
456 AND lines.line_id = x_InterfaceLineId;
457 --
458 CURSOR c_GetBillTo IS
459 SELECT ece_tp_location_code
460 FROM hz_cust_acct_sites_all acct_site,
461 rlm_interface_lines_all lines
462 WHERE lines.bill_to_address_id = acct_site.cust_acct_site_id
463 AND lines.line_id = x_InterfaceLineId;
464 --
465 CURSOR c_GetIntrmdShipTo IS
466 SELECT ece_tp_location_code
467 FROM hz_cust_acct_sites_all acct_site,
468 rlm_interface_lines_all lines
469 WHERE lines.intrmd_ship_to_id = acct_site.cust_acct_site_id
470 AND lines.line_id = x_InterfaceLineId;
471 --
472 -- Following cursor is changed as per TCA obsolescence project.
473 CURSOR c_Excep_Int IS
474 SELECT PARTY.PARTY_NAME customer_name,
475 rih.ECE_TP_TRANSLATOR_CODE ,
476 rih.ECE_TP_LOCATION_CODE_EXT ,
477 rih.EDI_CONTROL_NUM_3 ,
478 rih.EDI_TEST_INDICATOR ,
479 rih.SCHED_GENERATION_DATE ,
480 rih.SCHEDULE_REFERENCE_NUM ,
481 rih.SCHEDULE_SOURCE ,
482 rih.SCHEDULE_TYPE ,
483 rih.SCHEDULE_PURPOSE ,
484 rih.SCHED_HORIZON_START_DATE ,
485 rih.SCHED_HORIZON_END_DATE ,
486 ril.CUST_SHIP_FROM_ORG_EXT ,
487 ril.LINE_NUMBER ,
488 ril.SCHEDULE_ITEM_NUM ,
489 mtl.customer_item_number ,
490 ril.ITEM_DESCRIPTION_EXT ,
491 ril.CUST_UOM_EXT ,
492 ril.SUPPLIER_ITEM_EXT ,
493 ril.ITEM_DETAIL_TYPE ,
494 ril.ITEM_DETAIL_SUBTYPE ,
495 ril.ITEM_DETAIL_QUANTITY ,
496 ril.START_DATE_TIME ,
497 ril.CUSTOMER_JOB ,
498 ril.CUST_MODEL_SERIAL_NUMBER ,
499 ril.CUST_PRODUCTION_SEQ_NUM ,
500 ril.DATE_TYPE_CODE ,
501 ril.QTY_TYPE_CODE ,
502 ril.LINE_NUMBER ,
503 ril.REQUEST_DATE ,
504 ril.SCHEDULE_DATE ,
505 ril.CUST_PO_NUMBER ,
506 ril.INDUSTRY_ATTRIBUTE1 ,
507 ril.CUST_PRODUCTION_LINE ,
508 ril.CUSTOMER_DOCK_CODE ,
509 ril.SCHEDULE_LINE_ID
510 FROM rlm_interface_headers rih,
511 rlm_interface_lines_all ril,
512 HZ_PARTIES PARTY,
513 HZ_CUST_ACCOUNTS CUST_ACCT,
514 mtl_customer_items mtl
515 WHERE rih.ORG_ID = ril.ORG_ID
516 AND rih.header_id = x_InterfaceHeaderId
517 AND ril.line_id = x_InterfaceLineId
518 AND rih.header_id = ril.header_id
519 And CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
520 AND rih.customer_id = CUST_ACCT.PARTY_ID (+)
521 AND ril.customer_item_id = mtl.customer_item_id (+);
522 --
523 -- when no line_id is passed this cursor becomes active
524 -- Following cursor is changed as per TCA obsolescence project.
525 --
526 CURSOR c_Excep_Int1 IS
527 SELECT PARTY.PARTY_NAME customer_name,
528 rih.ECE_TP_TRANSLATOR_CODE ,
529 rih.ECE_TP_LOCATION_CODE_EXT ,
530 rih.EDI_CONTROL_NUM_3 ,
531 rih.EDI_TEST_INDICATOR ,
532 rih.SCHED_GENERATION_DATE ,
533 rih.SCHEDULE_REFERENCE_NUM ,
534 rih.SCHEDULE_SOURCE ,
535 rih.SCHEDULE_TYPE ,
536 rih.SCHEDULE_PURPOSE ,
537 rih.SCHED_HORIZON_START_DATE ,
538 rih.SCHED_HORIZON_END_DATE ,
539 NULL,--ril.CUST_SHIP_FROM_ORG_EXT ,
540 NULL,--ril.LINE_NUMBER ,
541 NULL,--ril.SCHEDULE_ITEM_NUM ,
542 NULL,--mtl.customer_item_number ,
543 NULL,--ril.ITEM_DESCRIPTION_EXT ,
544 NULL,--ril.CUST_UOM_EXT ,
545 NULL,--ril.SUPPLIER_ITEM_EXT ,
546 NULL,--ril.ITEM_DETAIL_TYPE ,
547 NULL,--ril.ITEM_DETAIL_SUBTYPE ,
548 NULL,--ril.ITEM_DETAIL_QUANTITY ,
549 NULL,--ril.START_DATE_TIME ,
550 NULL,--ril.CUSTOMER_JOB ,
551 NULL,--ril.CUST_MODEL_SERIAL_NUMBER ,
552 NULL,--ril.CUST_PRODUCTION_SEQ_NUM ,
553 NULL,--ril.DATE_TYPE_CODE ,
554 NULL,--ril.QTY_TYPE_CODE ,
555 NULL,--ril.LINE_NUMBER ,
556 NULL,--ril.REQUEST_DATE ,
557 NULL,--ril.SCHEDULE_DATE ,
558 NULL,--ril.CUST_PO_NUMBER ,
559 NULL,--ril.INDUSTRY_ATTRIBUTE1 ,
560 NULL,--ril.CUST_PRODUCTION_LINE ,
561 NULL,--ril.CUSTOMER_DOCK_CODE ,
562 NULL --ril.SCHEDULE_LINE_ID
563 FROM rlm_interface_headers rih,
564 HZ_PARTIES PARTY,
565 HZ_CUST_ACCOUNTS CUST_ACCT
566 WHERE rih.header_id = x_InterfaceHeaderId
567 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
568 AND rih.customer_id = cust_acct.cust_account_id (+);
569 --
570 --
571 -- Bug 2778186 : When group Info is required, this cursor will be used.
572 --
573 -- Following cursor is changed as per TCA obsolescence project.
574 CURSOR c_Excep_Int2 IS
575 SELECT PARTY.PARTY_NAME customer_name,
576 rih.ECE_TP_TRANSLATOR_CODE ,
577 rih.ECE_TP_LOCATION_CODE_EXT ,
578 rih.EDI_CONTROL_NUM_3 ,
579 rih.EDI_TEST_INDICATOR ,
580 rih.SCHED_GENERATION_DATE ,
581 rih.SCHEDULE_REFERENCE_NUM ,
582 rih.SCHEDULE_SOURCE ,
586 rih.SCHED_HORIZON_END_DATE ,
583 rih.SCHEDULE_TYPE ,
584 rih.SCHEDULE_PURPOSE ,
585 rih.SCHED_HORIZON_START_DATE ,
587 ril.CUST_SHIP_FROM_ORG_EXT ,
588 NULL, --ril.LINE_NUMBER ,
589 ril.SCHEDULE_ITEM_NUM ,
590 mtl.customer_item_number ,
591 ril.ITEM_DESCRIPTION_EXT ,
592 NULL, --ril.CUST_UOM_EXT ,
593 ril.SUPPLIER_ITEM_EXT ,
594 NULL, --ril.ITEM_DETAIL_TYPE ,
595 NULL, --ril.ITEM_DETAIL_SUBTYPE ,
596 NULL, --ril.ITEM_DETAIL_QUANTITY ,
597 NULL, --ril.START_DATE_TIME ,
598 NULL, --ril.CUSTOMER_JOB ,
599 NULL, --ril.CUST_MODEL_SERIAL_NUMBER ,
600 NULL, --ril.CUST_PRODUCTION_SEQ_NUM ,
601 NULL, --ril.DATE_TYPE_CODE ,
602 NULL, --ril.QTY_TYPE_CODE ,
603 NULL, --ril.LINE_NUMBER ,
604 NULL, --ril.REQUEST_DATE ,
605 NULL, --ril.SCHEDULE_DATE ,
606 NULL, --ril.CUST_PO_NUMBER ,
607 NULL, --ril.INDUSTRY_ATTRIBUTE1 ,
608 NULL, --ril.CUST_PRODUCTION_LINE ,
609 NULL, --ril.CUSTOMER_DOCK_CODE ,
610 NULL --ril.SCHEDULE_LINE_ID
611 FROM rlm_interface_headers rih,
612 rlm_interface_lines_all ril,
613 HZ_PARTIES PARTY,
614 HZ_CUST_ACCOUNTS CUST_ACCT,
615 mtl_customer_items mtl
616 WHERE rih.ORG_ID = ril.ORG_ID
617 AND rih.header_id = x_InterfaceHeaderId
618 AND ril.line_id = x_InterfaceLineId
619 AND rih.header_id = ril.header_id
620 AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
621 AND rih.customer_id = CUST_ACCT.PARTY_ID (+)
622 AND ril.customer_item_id = mtl.customer_item_id (+);
623 --
624 BEGIN
625 --
626 /*
627 ** Retrieve translated message for 'Error'
628 ** and 'Warning' if required.
629 */
630
631 /*
632 ** Complete the message string by substituting
633 ** tokens.
634 */
635 --
636 IF (x_ExceptionLevel = g_error) THEN
637 --
638 fnd_message.set_name ('RLM', 'RLM_ERROR');
639 v_error_txt := fnd_message.get;
640 v_MessageText := v_error_txt ||': ' || x_ErrorText;
641 g_error_flag := 'Y';
642 --
643 ELSIF (x_ExceptionLevel = g_warn) THEN
644 --
645 fnd_message.set_name ('RLM', 'RLM_WARNING');
646 v_warn_txt := fnd_message.get;
647 v_MessageText := v_warn_txt ||': '|| x_ErrorText;
648 g_warn_flag := 'Y';
649 --
650 ELSIF (x_ExceptionLevel = g_info) THEN
651 --
652 fnd_message.set_name ('RLM', 'RLM_INFORMATION');
653 v_info_txt := fnd_message.get;
654 v_MessageText := v_info_txt ||': ' || x_ErrorText;
655 g_info_flag := 'Y';
656 --
657 END IF;
658 --
659 /*
660 ** Obtain the exception id from the
661 ** sequence rlm_demand_exceptions_s .
662 */
663 --
664 SELECT rlm_demand_exceptions_s.nextval
665 INTO v_ExceptionId
666 FROM sys.dual;
667 --
668 /*
669 ** Program update date should be populated
670 ** if called from a concurrent program.
671 */
672 --
673 IF (fnd_global.conc_request_id IS NOT NULL) THEN
674 v_ProgramDate := sysdate;
675 v_LoginId := fnd_global.conc_login_id;
676 END IF;
677 --
678 /*
679 ** Select record.
680 */
681 v_progress := '015';
682 --
683 IF (x_InterfaceLineId IS NOT NULL) THEN
684 --
685 OPEN c_GetShipTo;
686 FETCH c_GetShipTo INTO v_ShipTo;
687 CLOSE c_GetShipTo;
688 --
689 OPEN c_GetBillTo;
690 FETCH c_GetBillTo INTO v_BillTo;
691 CLOSE c_GetBillTo;
692 --
693 OPEN c_GetIntrmdShipTo;
694 FETCH c_GetIntrmdShipTo INTO v_IntrmdShipTo;
695 CLOSE c_GetIntrmdShipTo;
696 --
697 END IF;
698 --
699 IF x_GroupInfo THEN
700 --
701 OPEN c_Excep_Int2;
702 FETCH c_Excep_Int2 INTO v_Exception_rec;
703 CLOSE c_Excep_Int2;
704 --
705 ELSIF (x_InterfaceLineId is NOT NULL) THEN
706 --
707 OPEN c_Excep_Int;
708 FETCH c_Excep_int INTO v_Exception_rec;
709 CLOSE c_Excep_Int;
710 --
711 ELSE
712 --
713 OPEN c_Excep_Int1;
714 FETCH c_Excep_Int1 INTO v_Exception_rec;
715 CLOSE c_Excep_Int1;
716 --
717 END IF;
718 --
719 /*
720 ** Insert record.
721 */
722 --
723 v_progress := '020';
724 --
725 -- get the inventory item for the exception report
726 --
727 BEGIN
728 --
729 SELECT a.item_number
730 INTO v_inv_item
731 FROM mtl_item_flexfields a,
732 mtl_customer_item_xrefs b
733 WHERE a.inventory_item_id = b.inventory_item_id
734 AND a.organization_id = b.master_organization_id
735 AND b.preference_number =1
736 AND b.customer_item_id IN (
737 SELECT customer_item_id
738 FROM rlm_interface_lines
739 WHERE line_id = x_InterfaceLineId
740 );
741 --
742 EXCEPTION
743 --
744 WHEN OTHERS THEN
745 NULL;
746 END;
747 --
748 --rlm_core_sv.dlog(C_DEBUG, 'ST', v_ShipTo);
749 --rlm_core_sv.dlog(C_DEBUG, 'BT', v_BillTo);
750 --rlm_core_sv.dlog(C_DEBUG, 'IST', v_IntrmdShipTo);
751 --
752 INSERT INTO RLM_DEMAND_EXCEPTIONS
753 (
754 exception_id,
755 exception_level,
756 message_name,
757 message_text,
761 schedule_line_id,
758 interface_header_id,
759 interface_line_id,
760 schedule_header_id,
762 order_header_id,
763 order_line_id,
764 last_update_date,
765 last_updated_by,
766 creation_date,
767 created_by,
768 last_update_login,
769 request_id,
770 program_application_id,
771 program_id,
772 program_update_date,
773 CUST_NAME_EXT,
774 CUST_SHIP_TO_EXT,
775 CUST_BILL_TO_EXT,
776 CUST_INTERMD_SHIPTO_EXT,
777 ECE_TP_TRANSLATOR_CODE,
778 ECE_TP_LOCATION_CODE_EXT,
779 EDI_CONTROL_NUM_3,
780 EDI_TEST_INDICATOR,
781 SCHED_GENERATION_DATE,
782 SCHEDULE_REFERENCE_NUM,
783 SCHEDULE_SOURCE,
784 SCHEDULE_TYPE,
785 SCHEDULE_PURPOSE,
786 HORIZON_START_DATE,
787 HORIZON_END_DATE,
788 CUST_SHIP_FROM_ORG_EXT,
789 SCHEDULE_LINE_NUMBER,
790 SCHEDULE_ITEM_NUM,
791 CUSTOMER_ITEM_EXT,
792 CUST_ITEM_DESCRIPTION,
793 CUST_UOM_EXT,
794 INVENTORY_ITEM,
795 ITEM_DETAIL_TYPE,
796 ITEM_DETAIL_SUBTYPE,
797 ITEM_DETAIL_QUANTITY,
798 START_DATE_TIME,
799 CUST_JOB_NUMBER,
800 CUST_MODEL_SERIAL_NUM,
801 CUSTOMER_PROD_SEQ_NUM,
802 DATE_TYPE_CODE,
803 QTY_TYPE_CODE,
804 REQUEST_DATE,
805 SCHEDULE_DATE,
806 CUST_PO_NUMBER,
807 INDUSTRY_ATTRIBUTE1,
808 CUST_PRODUCTION_LINE,
809 CUSTOMER_DOCK_CODE,
810 PURGE_STATUS
811 )
812 VALUES
813 (
814 v_ExceptionId,
815 x_ExceptionLevel,
816 x_MessageName,
817 SUBSTR(v_MessageText,1,2000),
818 x_InterfaceHeaderId,
819 x_InterfaceLineId,
820 x_ScheduleHeaderId,
821 x_ScheduleLineId,
822 x_OrderHeaderId,
823 x_OrderLineId,
824 sysdate,
825 nvl(x_user_id,fnd_global.user_id),
826 sysdate,
827 fnd_global.user_id,
828 v_LoginId,
829 x_conc_req_id,
830 x_prog_appl_id,
831 x_conc_program_id,
832 v_ProgramDate,
833 v_Exception_rec.CUST_NAME_EXT,
834 v_shipTo,
835 v_BillTo,
836 v_IntrmdShipTo,
837 v_Exception_rec.ECE_TP_TRANSLATOR_CODE,
838 v_Exception_rec.ECE_TP_LOCATION_CODE_EXT,
839 v_Exception_rec.EDI_CONTROL_NUM_3,
840 v_Exception_rec.EDI_TEST_INDICATOR,
841 v_Exception_rec.SCHED_GENERATION_DATE,
842 v_Exception_rec.SCHEDULE_REFERENCE_NUM,
843 v_Exception_rec.SCHEDULE_SOURCE,
844 v_Exception_rec.SCHEDULE_TYPE,
845 v_Exception_rec.SCHEDULE_PURPOSE,
846 v_Exception_rec.HORIZON_START_DATE,
847 v_Exception_rec.HORIZON_END_DATE,
848 v_Exception_rec.CUST_SHIP_FROM_ORG_EXT,
849 v_Exception_rec.SCHEDULE_LINE_NUMBER,
850 v_Exception_rec.SCHEDULE_ITEM_NUM,
851 v_Exception_rec.CUSTOMER_ITEM_EXT,
852 v_Exception_rec.CUST_ITEM_DESCRIPTION,
853 v_Exception_rec.CUST_UOM_EXT,
854 v_inv_item,
855 v_Exception_rec.ITEM_DETAIL_TYPE,
856 v_Exception_rec.ITEM_DETAIL_SUBTYPE,
857 v_Exception_rec.ITEM_DETAIL_QUANTITY,
858 v_Exception_rec.START_DATE_TIME,
859 v_Exception_rec.CUST_JOB_NUMBER,
860 v_Exception_rec.CUST_MODEL_SERIAL_NUM,
861 v_Exception_rec.CUSTOMER_PROD_SEQ_NUM,
862 v_Exception_rec.DATE_TYPE_CODE,
863 v_Exception_rec.QTY_TYPE_CODE,
864 v_Exception_rec.REQUEST_DATE,
865 v_Exception_rec.SCHEDULE_DATE,
866 v_Exception_rec.CUST_PO_NUMBER,
867 v_Exception_rec.INDUSTRY_ATTRIBUTE1,
868 v_Exception_rec.CUST_PRODUCTION_LINE,
869 v_Exception_rec.CUSTOMER_DOCK_CODE,
870 x_PurgeStatus
871 );
872 --
873 --rlm_core_sv.dpop(C_SDEBUG);
874 --
875 EXCEPTION
876 --
877 WHEN OTHERS THEN
878 --
879 sql_error ('rlm_message_sv.insert_row', v_progress);
880 --
881 -- close cursors
882 --
883 if (c_Excep_Int%ISOPEN) Then
884 CLOSE c_Excep_Int;
885 end if;
886 --
887 if (c_Excep_Int1%ISOPEN) Then
888 CLOSE c_Excep_Int1;
889 end if;
890 --
891 if (c_Excep_Int2%ISOPEN) Then
892 CLOSE c_Excep_Int2;
893 end if;
894 --
895 IF (c_GetShipTo%ISOPEN) THEN
896 CLOSE c_GetShipTo;
897 END IF;
898 --
899 IF (c_GetBillTo%ISOPEN) THEN
900 CLOSE c_GetBillTo;
901 END IF;
902 --
903 IF (c_GetIntrmdShipTo%ISOPEN) THEN
904 CLOSE c_GetIntrmdShipTo;
905 END IF;
906 --
907 --rlm_core_sv.dpop(C_SDEBUG);
908 RAISE;
909 --
910 END insert_row;
911
912
913
914 /*===========================================================================
915
916 PROCEDURE NAME: insert_purge_row
917
918 ===========================================================================*/
919
920 PROCEDURE insert_purge_row (
921 x_ExceptionLevel IN VARCHAR2,
922 x_MessageName IN VARCHAR2,
923 x_ErrorText IN VARCHAR2,
924 x_InterfaceHeaderId IN NUMBER,
928 x_OrderHeaderId IN NUMBER,
925 x_InterfaceLineId IN NUMBER,
926 x_ScheduleHeaderId IN NUMBER,
927 x_ScheduleLineId IN NUMBER,
929 x_OrderLineId IN NUMBER,
930 x_ScheduleLineNum IN NUMBER, --bugfix 6319027
931 x_user_id IN NUMBER,
932 x_conc_req_id IN NUMBER,
933 x_prog_appl_id IN NUMBER,
934 x_conc_program_id IN NUMBER,
935 x_PurgeStatus IN VARCHAR2,
936 x_PurgeExp_rec IN t_PurExp_rec
937 )
938
939 IS
940
941 v_MessageText VARCHAR2(5000) := NULL;
942 v_ExceptionId NUMBER := NULL;
943 v_progress VARCHAR2(3) := '010';
944 v_info_txt VARCHAR2(2000) := NULL;
945 v_warn_txt VARCHAR2(2000) := NULL;
946 v_error_txt VARCHAR2(2000) := NULL;
947 v_ProgramDate DATE := NULL;
948 v_LoginId NUMBER := fnd_global.login_id;
949 v_count NUMBER := 0;
950
951 BEGIN
952
953 /*
954 ** Retrieve translated message for 'Error'
955 ** and 'Warning' if required.
956 */
957
958 /*
959 ** Complete the message string by substituting
960 ** tokens.
961 */
962
963
964 IF (x_ExceptionLevel = g_error) THEN
965 fnd_message.set_name ('RLM', 'RLM_ERROR');
966 v_error_txt := fnd_message.get;
967 v_MessageText := v_error_txt ||': ' || x_ErrorText;
968 g_error_flag := 'Y';
969
970
971 ELSIF (x_ExceptionLevel = g_warn) THEN
972 fnd_message.set_name ('RLM', 'RLM_WARNING');
973 v_warn_txt := fnd_message.get;
974 v_MessageText := v_warn_txt ||': '|| x_ErrorText;
975 g_warn_flag := 'Y';
976
977
978 ELSIF (x_ExceptionLevel = g_info) THEN
979 fnd_message.set_name ('RLM', 'RLM_INFORMATION');
980 v_info_txt := fnd_message.get;
981 v_MessageText := v_info_txt ||': ' || x_ErrorText;
982 g_info_flag := 'Y';
983
984 END IF;
985
986
987
988 /*
989 ** Obtain the exception id from the
990 ** sequence rlm_demand_exceptions_s .
991 */
992
993 SELECT rlm_demand_exceptions_s.nextval
994 INTO v_ExceptionId
995 FROM sys.dual;
996
997
998
999 /*
1000 ** Program update date should be populated
1001 ** if called from a concurrent program.
1002 */
1003
1004
1005
1006 IF (fnd_global.conc_request_id IS NOT NULL) THEN
1007 v_ProgramDate := sysdate;
1008 v_LoginId := fnd_global.conc_login_id;
1009 END IF;
1010
1011
1012
1013 /*
1014 ** Select record.
1015 */
1016
1017
1018
1019 v_progress := '015';
1020
1021
1022 /*
1023 ** Insert record.
1024 */
1025
1026
1027
1028 v_progress := '020';
1029
1030 INSERT INTO RLM_DEMAND_EXCEPTIONS(
1031 exception_id,
1032 exception_level,
1033 message_name,
1034 message_text,
1035 interface_header_id,
1036 interface_line_id,
1037 schedule_header_id,
1038 schedule_line_id,
1039 order_header_id,
1040 order_line_id,
1041 schedule_line_number, --bugfix 6319027
1042 last_update_date,
1043 last_updated_by,
1044 creation_date,
1045 created_by,
1046 last_update_login,
1047 request_id,
1048 program_application_id,
1049 program_id,
1050 program_update_date,
1051 ECE_TP_TRANSLATOR_CODE,
1052 SCHEDULE_REFERENCE_NUM,
1053 SCHEDULE_TYPE,
1054 SCHED_GENERATION_DATE,
1055 ORIGIN_TABLE, /*2261812*/
1056 PURGE_STATUS
1057 )
1058 VALUES (
1059 v_ExceptionId,
1060 x_ExceptionLevel,
1061 x_MessageName,
1062 substr(v_MessageText,1,2000),
1063 x_InterfaceHeaderId,
1064 x_InterfaceLineId,
1065 x_ScheduleHeaderId,
1066 x_ScheduleLineId,
1067 x_OrderHeaderId,
1068 x_OrderLineId,
1069 x_ScheduleLineNum, --bugfix 6319027
1070 sysdate,
1071 nvl(x_user_id,fnd_global.user_id),
1072 sysdate,
1073 fnd_global.user_id,
1074 v_LoginId,
1075 x_conc_req_id,
1076 x_prog_appl_id,
1077 x_conc_program_id,
1078 v_ProgramDate,
1079 x_PurgeExp_rec.ECE_TP_TRANSLATOR_CODE,
1080 x_PurgeExp_rec.SCHEDULE_REFERENCE_NUM,
1081 x_PurgeExp_rec.SCHEDULE_TYPE,
1082 x_PurgeExp_rec.SCHED_GENERATION_DATE,
1083 x_PurgeExp_rec.ORIGIN_TABLE, /* 2261812*/
1084 x_PurgeStatus);
1085
1086
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 sql_error ('rlm_message_sv.insert_purge_row', v_progress);
1090 RAISE;
1091
1092 END insert_purge_row;
1093
1094
1095
1096 /*===========================================================================
1097
1098 PROCEDURE NAME: sql_error
1099
1103 x_location IN VARCHAR2)
1100 ===========================================================================*/
1101
1102 PROCEDURE sql_error (x_routine IN VARCHAR2,
1104 IS
1105
1106 --
1107 x_text VARCHAR2(255) := NULL;
1108 --
1109 v_message_rec t_message_rec;
1110 v_incr NUMBER;
1111 --
1112 BEGIN
1113 --
1114 IF (g_routine is NULL) THEN
1115 g_routine := x_routine;
1116 g_location := x_location;
1117 ELSE
1118 g_routine := x_routine ||'-'|| x_location ||': '|| g_routine;
1119 END IF;
1120 /*
1121 ** Build the message string.
1122 */
1123 fnd_message.set_name ('RLM', 'RLM_ALL_SQL_ERROR');
1124 fnd_message.set_token ('ROUTINE', g_routine);
1125 fnd_message.set_token ('ERR_NUMBER', g_location);
1126 fnd_message.set_token ('SQL_ERR', substr(sqlerrm,1,300));
1127 --
1128 v_message_rec.exception_level := rlm_message_sv.k_error_level;
1129 v_message_rec.message_name := 'RLM_ALL_SQL_ERROR';
1130 v_message_rec.error_text := fnd_message.get;
1131 --
1132 v_incr := g_message_tab.COUNT + 1;
1133 --
1134 -- set the message in the table
1135 --
1136 g_message_tab(v_incr) := v_message_rec;
1137 --
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 RAISE;
1141
1142 END sql_error;
1143
1144 /*===========================================================================
1145
1146 PROCEDURE NAME: processing_error
1147
1148 ===========================================================================*/
1149
1150 PROCEDURE processing_error (x_routine IN VARCHAR2,
1151 x_location IN VARCHAR2)
1152 IS
1153
1154 x_text VARCHAR2(255) := NULL;
1155
1156 BEGIN
1157
1158 IF (g_routine is NULL) THEN
1159 g_routine := x_routine;
1160 g_location := x_location;
1161
1162 ELSE
1163 g_routine := x_routine ||'-'|| x_location ||': '|| g_routine;
1164
1165 END IF;
1166
1167 /*
1168 ** Build the message string.
1169 */
1170
1171 fnd_message.set_name ('RLM', 'RLM_ALL_PROC_ERROR');
1172 fnd_message.set_token ('ROUTINE', g_routine);
1173 fnd_message.set_token ('ERR_NUMBER', g_location);
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 RAISE;
1178
1179 END processing_error;
1180
1181
1182
1183 /*===========================================================================
1184
1185 FUNCTION NAME: get
1186
1187 ===========================================================================*/
1188
1189 FUNCTION get
1190 RETURN VARCHAR2
1191 IS
1192
1193 BEGIN
1194
1195 /*
1196 ** Retrieve the message from the stack.
1197 */
1198 --return (fnd_message.get);
1199 return ('ERROR');
1200
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 RAISE;
1204
1205 END get;
1206
1207 /*===========================================================================
1208
1209 FUNCTION NAME: fatal_error_found
1210
1211 ===========================================================================*/
1212
1213 FUNCTION fatal_error_found
1214 RETURN BOOLEAN
1215 IS
1216
1217 BEGIN
1218 --
1219 RETURN FALSE;
1220 --
1221 /*
1222 IF fatal_error_flag = 'Y' THEN
1223 return TRUE;
1224 ELSE
1225 return FALSE;
1226 END IF;
1227 */
1228 --
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 RAISE;
1232
1233 END fatal_error_found;
1234
1235 /*===========================================================================
1236
1237 FUNCTION NAME: initialize_messages
1238 This function will initialize the flags
1239 fatal_error_flag
1240 error flag and
1241 warn flag to N
1242 Also it will reset all records in the dependency table
1243 ===========================================================================*/
1244
1245 PROCEDURE initialize_messages
1246 IS
1247
1248 BEGIN
1249 --
1250 g_fatal_error_flag := 'N';
1251 g_error_flag := 'N';
1252 g_warn_flag := 'N';
1253 g_info_flag := 'N';
1254 reset_dependency;
1255 g_message_tab.DELETE;
1256 --
1257 --
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 RAISE;
1261
1262 END initialize_messages;
1263
1264 /*===========================================================================
1265
1266 PROCEDURE NAME: reset_dependency
1267 IF no parameter is passed then the reset dependency will reset all the error
1268 falg in the dep table to N
1269 else
1270 it will only reset the error flag for the valitaion type specified
1271
1272 ===========================================================================*/
1273
1274 PROCEDURE reset_dependency( x_val_name IN VARCHAR2)
1275 IS
1276
1277 BEGIN
1278 --
1279 FOR i IN 1..g_dependency_tab.COUNT LOOP
1280 --
1281 IF x_val_name IS NULL THEN
1282 --
1283 g_dependency_tab(i).error_flag := 'N';
1284 --
1285 ELSIF (g_dependency_tab(i).val_name = x_val_name OR
1286 g_dependency_tab(i).dep_name = x_val_name )
1287 THEN
1291 END IF;
1288 --
1289 g_dependency_tab(i).error_flag := 'N';
1290 --
1292 --
1293 END LOOP;
1294 --
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 RAISE;
1298
1299 END reset_dependency;
1300
1301
1302 /*===========================================================================
1303
1304 FUNCTION NAME: dump_messages
1305
1306 ===========================================================================*/
1307
1308 PROCEDURE dump_messages
1309 IS
1310 v_user_id NUMBER := fnd_global.user_id;
1311 v_prog_appl_id NUMBER := fnd_global.prog_appl_id;
1312 v_conc_program_id NUMBER := fnd_global.conc_program_id;
1313
1314 BEGIN
1315 --
1316 IF g_message_tab.COUNT > 0 THEN
1317 --
1318 FOR i in 1..g_message_tab.COUNT LOOP
1319 --
1320 insert_row (g_message_tab(i).exception_level,
1321 NVL(g_message_tab(i).child_message_name,g_message_tab(i).message_name),
1322 g_message_tab(i).error_text,
1323 g_message_tab(i).interface_header_id,
1324 g_message_tab(i).interface_line_id,
1325 g_message_tab(i).schedule_header_id,
1326 g_message_tab(i).schedule_line_id,
1327 g_message_tab(i).order_header_id,
1328 g_message_tab(i).order_line_id,
1329 g_message_tab(i).group_Info,
1330 v_user_id,
1331 get_conc_req_id,
1332 v_prog_appl_id,
1333 v_conc_program_id);
1334 --
1335 END LOOP;
1336 --
1337 g_message_tab.delete; -- BugFix #4147550
1338 --
1339 END IF;
1340 --
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 RAISE;
1344
1345 END dump_messages;
1346
1347
1348
1349 /*===========================================================================
1350
1351 FUNCTION NAME: dump_messages
1352
1353 ===========================================================================*/
1354
1355 PROCEDURE dump_messages(x_header_id IN NUMBER)
1356 IS
1357 v_user_id NUMBER := fnd_global.user_id;
1358 v_prog_appl_id NUMBER := fnd_global.prog_appl_id;
1359 v_conc_program_id NUMBER := fnd_global.conc_program_id;
1360 -- Bug 2771756
1361 i NUMBER;
1362 BEGIN
1363 --
1364 IF g_message_tab.COUNT > 0 THEN
1365 --
1366 -- Bug 2771756 : Using while loop instead of FOR loop.
1367
1368 i := g_message_tab.FIRST;
1369 WHILE i IS NOT NULL LOOP
1370 --
1371 insert_row (g_message_tab(i).exception_level,
1372 NVL(g_message_tab(i).child_message_name,g_message_tab(i).message_name),
1373 g_message_tab(i).error_text,
1374 NVL(g_message_tab(i).interface_header_id,x_header_id),
1375 g_message_tab(i).interface_line_id,
1376 g_message_tab(i).schedule_header_id,
1377 g_message_tab(i).schedule_line_id,
1378 g_message_tab(i).order_header_id,
1379 g_message_tab(i).order_line_id,
1380 g_message_tab(i).group_Info,
1381 v_user_id,
1382 get_conc_req_id,
1383 v_prog_appl_id,
1384 v_conc_program_id);
1385 --
1386
1387 i := g_message_tab.NEXT(i);
1388
1389 END LOOP;
1390 --
1391 END IF;
1392 --
1393 g_message_tab.delete; -- BugFix #4147550
1394 --
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 RAISE;
1398
1399 END dump_messages;
1400
1401
1402 /*===========================================================================
1403
1404 FUNCTION NAME: set_fatal_error
1405
1406 ===========================================================================*/
1407
1408 PROCEDURE set_fatal_error
1409 IS
1410
1411 BEGIN
1412
1413 g_fatal_error_flag := 'Y';
1414
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 RAISE;
1418
1419 END set_fatal_error;
1420
1421 /*===========================================================================
1422
1423 FUNCTION NAME: are_there_errors
1424
1425 ===========================================================================*/
1426
1427 FUNCTION are_there_errors
1428 RETURN BOOLEAN
1429 IS
1430
1431 BEGIN
1432
1433 IF g_error_flag = 'Y' THEN
1434 RETURN TRUE;
1435 END IF;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 RAISE;
1440
1441 END are_there_errors;
1442
1443
1444 /*===========================================================================
1445
1446 PROCEDURE NAME: set_Dependent_error
1447 This proc will set the error flag for all val types which are
1448 dependent on the x_type which is passed in
1449 ===========================================================================*/
1450
1451 PROCEDURE set_Dependent_error(x_name VARCHAR2)
1452 IS
1453
1454 BEGIN
1455 -- rlm_core_sv.dpush(C_SDEBUG,'set_Dependent_Error');
1456
1457 FOR i IN 1..g_dependency_tab.COUNT LOOP
1458 --
1459 IF (g_dependency_tab(i).dep_name = x_name OR
1460 g_dependency_tab(i).val_name = x_name )
1461 THEN
1462 --
1463 g_dependency_tab(i).error_flag := 'Y';
1464 --
1465 END IF;
1466 --
1467 END LOOP;
1471 WHEN OTHERS THEN
1468 -- rlm_core_sv.dpop(C_SDEBUG);
1469
1470 EXCEPTION
1472 RAISE;
1473 END set_Dependent_Error;
1474
1475 /*===========================================================================
1476
1477 FUNCTION NAME: check_dependency
1478 This function will check in the dependency table for val_name
1479 if there was a previous error then the flag will be Y so we need to just
1480 set the error for all other vals where dep_name = name to Y so that
1481 those values will not be validated in future.
1482 ===========================================================================*/
1483
1484 FUNCTION check_dependency(x_name VARCHAR2)
1485 RETURN BOOLEAN
1486 IS
1487 dependent_error_found EXCEPTION;
1488 BEGIN
1489 --
1490 -- rlm_core_sv.dpush(C_SDEBUG, 'check_dependency');
1491 --
1492 FOR i IN g_dependency_tab.FIRST..g_dependency_tab.LAST LOOP
1493 --
1494 IF g_dependency_tab(i).val_name = x_name AND
1495 g_dependency_tab(i).error_flag = 'Y'
1496 THEN
1497 --
1498 RAISE dependent_error_found;
1499 --
1500 END IF;
1501 --
1502 END LOOP;
1503 --
1504 -- rlm_core_sv.dpop(C_SDEBUG);
1505 RETURN TRUE;
1506 --
1507 EXCEPTION
1508 WHEN dependent_error_found THEN
1509 set_dependent_error(x_name);
1510 -- rlm_core_sv.dpop(C_SDEBUG);
1511 RETURN FALSE;
1512 --
1513 WHEN OTHERS THEN
1514 RAISE;
1515
1516 END check_dependency;
1517
1518 FUNCTION get_dep_rec(x_val_name VARCHAR2,
1519 x_dep_name VARCHAR2,
1520 x_error_flag VARCHAR2)
1521 RETURN dep_rec_type
1522 IS
1523 v_dep_rec dep_rec_type;
1524 BEGIN
1525 --
1526 v_dep_rec.val_name := x_val_name;
1527 v_dep_rec.dep_name := x_dep_name;
1528 v_dep_rec.error_flag := x_error_flag;
1529 RETURN v_dep_rec;
1530 --
1531 END get_dep_rec;
1532
1533 /*
1534 This procedure will add the dependencies to be checked at the time of running
1535 the validations. Any dependencies can be added by the users at any time which
1536 will be accessed by the is_dependent_error which should be called at the start
1537 of each procedure. This will check whether an error has been detected with any
1538 validation type.
1539 */
1540
1541 PROCEDURE add_dependency (p_val_name VARCHAR2,
1542 p_dep_name VARCHAR2)
1543
1544 IS
1545 depIndex number;
1546 -- v_dep_rec dep_rec_type;
1547 BEGIN
1548
1549 depIndex := g_dependency_tab.COUNT;
1550 --
1551 g_dependency_tab(depIndex +1) := get_dep_rec(p_val_name,p_dep_name,'N');
1552 --
1553
1554 END add_dependency;
1555
1556 PROCEDURE initialize_dependency(x_module VARCHAR2)
1557 IS
1558
1559 BEGIN
1560 --
1561 IF x_module = 'VALIDATE_DEMAND' THEN
1562 --
1563 add_dependency('CUSTOMER',null);
1564 add_dependency('SHIPFROM',null);
1565 add_dependency('SHIPTO','CUSTOMER');
1566 add_dependency('BILLTO','CUSTOMER');
1567 add_dependency('BILLTO','SHIPTO');
1568 add_dependency('CITEM','CUSTOMER');
1569 add_dependency('CITEM','SHIPTO');
1570 add_dependency('INVITEM','CITEM');
1571 add_dependency('ITEM_DETAIL_SUBTYPE','ITEM_DETAIL_TYPE');
1572 add_dependency('QUANTITY_TYPE_CODE','ITEM_DETAIL_TYPE');
1573 add_dependency('UOM_CODE','INVITEM');
1574 add_dependency('LINE_SCHEDULE_TYPE','SCHEDULE_TYPE');
1575 add_dependency('FOREASTDESIGNATOR','CUSTOMER');
1576 add_dependency('FOREASTDESIGNATOR','SHIPFROM');
1577 add_dependency('CUM_KEY_PO','SHIP_FROM_ORG');
1578 add_dependency('CUM_KEY_PO','INVENTORY_ITEM');
1579 add_dependency('CUM_KEY_PO','SHIP_TO');
1580 add_dependency('CUM_KEY_PO','CUSTOMER_ITEM');
1581 --
1582 ELSIF x_module = 'MANAGE_DEMAND' THEN
1583 --
1584 add_dependency('INVITEM','CITEM');
1585 --
1586 END IF;
1587 --
1588 EXCEPTION
1589 WHEN OTHERS THEN
1590 RAISE;
1591
1592 END initialize_dependency;
1593
1594
1595 FUNCTION get_conc_req_id
1596 RETURN NUMBER
1597 IS
1598 BEGIN
1599 --
1600 IF g_conc_req_id IS NOT NULL THEN
1601 RETURN g_conc_req_id;
1602 ELSE
1603 RETURN fnd_global.conc_request_id;
1604 END IF;
1605 --
1606 END get_conc_req_id;
1607
1608
1609 PROCEDURE populate_req_id
1610 IS
1611 BEGIN
1612 --
1613 g_conc_req_id := fnd_global.conc_request_id;
1614 --
1615 END populate_req_id;
1616
1617 -- Bug#: 2771756 - Start
1618 /*===========================================================================
1619
1620 PROCEDURE NAME: removeMessages
1621
1622 ===========================================================================*/
1623
1624 /* Bug 4198330 added grouping parameters to removeMessages*/
1625
1626 PROCEDURE removeMessages (p_header_id IN NUMBER,
1627 p_message IN VARCHAR2,
1628 p_message_type IN VARCHAR2,
1629 p_ship_from_org_id IN NUMBER,
1630 p_ship_to_address_id IN NUMBER,
1631 p_customer_item_id IN NUMBER,
1632 p_inventory_item_id IN NUMBER)
1633 IS
1634 --
1635 i NUMBER;
1636 --
1637 BEGIN
1638 --
1639 IF g_message_tab.COUNT > 0 THEN
1640 --
1641 i := g_message_tab.FIRST;
1642 --
1643 WHILE i IS NOT NULL LOOP
1644 --
1645 IF g_message_tab(i).interface_header_id = p_header_id AND
1646 g_message_tab(i).exception_level = p_message_type AND
1647 g_message_tab(i).message_name = p_message THEN
1648 -- Bug 4198330
1649 IF g_message_tab(i).ship_from_org_id IS NOT NULL AND
1650 g_message_tab(i).ship_to_address_id IS NOT NULL AND
1651 g_message_tab(i).inventory_item_id IS NOT NULL AND
1652 g_message_tab(i).customer_item_id IS NOT NULL THEN
1653 --
1654 IF g_message_tab(i).ship_from_org_id =
1655 nvl(p_ship_from_org_id, g_message_tab(i).ship_from_org_id) AND
1656 g_message_tab(i).ship_to_address_id =
1657 nvl(p_ship_to_address_id,g_message_tab(i).ship_to_address_id) AND
1658 g_message_tab(i).inventory_item_id =
1659 nvl(p_inventory_item_id,g_message_tab(i).inventory_item_id) AND
1660 g_message_tab(i).customer_item_id =
1661 nvl(p_customer_item_id,g_message_tab(i).customer_item_id) THEN
1662 --
1663 g_message_tab.DELETE(i);
1664 --
1665 END IF;
1666 --
1667 ELSE
1668 -- Remove the line from the tab
1669 g_message_tab.DELETE(i);
1670 --
1671 END IF;
1672 --
1673 END IF;
1674 --
1675 i := g_message_tab.NEXT(i);
1676 --
1677 END LOOP;
1678 --
1679 END IF;
1680 --
1681 EXCEPTION
1682 When others then
1683 raise;
1684
1685 END removeMessages;
1686 -- Bug#: 2771756 - End
1687
1688 END rlm_message_sv;