[Home] [Help]
PACKAGE: APPS.RLM_MESSAGE_SV
Source
1 PACKAGE RLM_MESSAGE_SV AUTHID CURRENT_USER as
2 /* $Header: RLMCOMSS.pls 120.2.12000000.2 2007/09/03 13:50:07 sunilku ship $ */
3 /*===========================================================================
4 PACKAGE NAME: rlm_message_sv
5
6 DESCRIPTION: Contains the exception handling apis required for
7 Oracle Release Management.
8
9 CLIENT/SERVER: Server
10
11 LIBRARY NAME: None
12
13 OWNER: amitra
14
15 PROCEDURE/FUNCTIONS: app_error
16 get_msg_text
17 insert_row
18 sql_error
19 processing_error
20 get
21
22 GLOBALS: g_warn
23 g_error
24
25 ===========================================================================*/
26
27 g_fatal_error_flag VARCHAR2(30) := 'N';
28
29 TYPE t_message_rec IS RECORD (
30 exception_level VARCHAR2(100),
31 message_name VARCHAR2(30000),
32 child_message_name VARCHAR2(30),
33 error_text VARCHAR2(30000),
34 interface_header_id NUMBER,
35 interface_line_id NUMBER,
36 schedule_header_id NUMBER,
37 schedule_line_id NUMBER ,
38 order_header_id NUMBER,
39 order_line_id NUMBER,
40 group_Info BOOLEAN,
41 ship_from_org_id NUMBER,
42 ship_to_address_id NUMBER,
43 customer_item_id NUMBER,
44 inventory_item_id NUMBER, /* bug 4091219 */
45 schedule_line_number NUMBER); --bugfix 6319027
46
47 TYPE t_PurExp_rec IS RECORD (
48
49 ECE_TP_TRANSLATOR_CODE VARCHAR2(35),
50 SCHEDULE_REFERENCE_NUM VARCHAR2(35),
51 SCHEDULE_TYPE VARCHAR2(30),
52 SCHED_GENERATION_DATE DATE,
53 ORIGIN_TABLE VARCHAR2(10) /*2261812*/
54 );
55
56
57 TYPE t_exception_rec IS RECORD (
58 CUST_NAME_EXT VARCHAR2(360),
59 --CUST_SHIP_TO_EXT VARCHAR2(35),
60 --CUST_BILL_TO_EXT VARCHAR2(35),
61 --CUST_INTERMD_SHIPTO_EXT VARCHAR2(35),
62 ECE_TP_TRANSLATOR_CODE VARCHAR2(35),
63 ECE_TP_LOCATION_CODE_EXT VARCHAR2(35),
64 EDI_CONTROL_NUM_3 VARCHAR2(15),
65 EDI_TEST_INDICATOR VARCHAR2(1),
66 SCHED_GENERATION_DATE DATE,
67 SCHEDULE_REFERENCE_NUM VARCHAR2(35),
68 SCHEDULE_SOURCE VARCHAR2(30),
69 SCHEDULE_TYPE VARCHAR2(30),
70 SCHEDULE_PURPOSE VARCHAR2(30),
71 HORIZON_START_DATE DATE,
72 HORIZON_END_DATE DATE,
73 CUST_SHIP_FROM_ORG_EXT VARCHAR2(80),
74 SCHEDULE_LINE_NUMBER NUMBER,
75 SCHEDULE_ITEM_NUM NUMBER,
76 CUSTOMER_ITEM_EXT VARCHAR2(50),
77 CUST_ITEM_DESCRIPTION VARCHAR2(80),
78 CUST_UOM_EXT VARCHAR2(10),
79 INVENTORY_ITEM VARCHAR2(50),
80 ITEM_DETAIL_TYPE VARCHAR2(30),
81 ITEM_DETAIL_SUBTYPE VARCHAR2(30),
82 ITEM_DETAIL_QUANTITY NUMBER,
83 START_DATE_TIME DATE,
84 CUST_JOB_NUMBER VARCHAR2(50),
85 CUST_MODEL_SERIAL_NUM VARCHAR2(35),
86 CUSTOMER_PROD_SEQ_NUM VARCHAR2(35),
87 DATE_TYPE_CODE VARCHAR2(30),
88 QTY_TYPE_CODE VARCHAR2(30),
89 LINE_NUMBER NUMBER,
90 REQUEST_DATE DATE,
91 SCHEDULE_DATE DATE,
92 CUST_PO_NUMBER VARCHAR2(50),
93 INDUSTRY_ATTRIBUTE1 VARCHAR2(150),
94 CUST_PRODUCTION_LINE VARCHAR2(50),
95 CUSTOMER_DOCK_CODE VARCHAR2(50),
96 SCHEDULE_LINE_ID NUMBER
97 );
98
99
100 g_info VARCHAR2(2) := 'I';
101 g_warn VARCHAR2(2) := 'W';
102 g_error VARCHAR2(2) := 'E';
103 g_routine VARCHAR2(2000) := NULL;
104 g_location VARCHAR2(3) := NULL;
105
106 -- Constants to be passed in to the app_error
107 k_error_level VARCHAR2(10) := 'E';
108 k_warn_level VARCHAR2(10) := 'W';
109 k_info_level VARCHAR2(10) := 'I';
110 /*===========================================================================
111 PROCEDURE NAME: app_error
112
113 DESCRIPTION: This procedure is called by server side apis to
114 process error conditions. It extracts the error
115 message, replaces tokens if any and inserts the
116 error into the rlm_demand_exceptions table.
117
118 PARAMETERS: x_ExceptionLevel IN VARCHAR2 DEFAULT 'E'
119 x_MessageName IN VARCHAR2 DEFAULT NULL
120 x_ChildMessageName IN VARCHAR2 DEFAULT NULL
121 x_InterfaceHeaderId IN NUMBER DEFAULT NULL
122 x_InterfaceLineId IN NUMBER DEFAULT NULL
123 x_ScheduleHeaderId IN NUMBER DEFAULT NULL
124 x_ScheduleLineId IN NUMBER DEFAULT NULL
125 x_OrderHeaderId IN NUMBER DEFAULT NULL
126 x_OrderLineId IN NUMBER DEFAULT NULL
127 x_ErrorText IN VARCHAR2 DEFAULT NULL
128 x_ValidationType IN VARCHAR2 DEFAULT NULL
129 x_GroupInfo IN BOOLEAN DEFAULT FALSE
130
131 DESIGN REFERENCES:
132
133 ALGORITHM:
134
135 NOTES:
136
137 OPEN ISSUES:
138
139 CLOSED ISSUES:
140
141 CHANGE HISTORY: Abhijit Mitra Created 8/11/98
142 Mohana Narayan Modified 10/14/99
143 ===========================================================================*/
144
145 PROCEDURE app_error (x_ExceptionLevel IN VARCHAR2 DEFAULT 'E',
146 x_MessageName IN VARCHAR2 DEFAULT NULL,
147 x_ChildMessageName IN VARCHAR2 DEFAULT NULL,
148 x_InterfaceHeaderId IN NUMBER DEFAULT NULL,
149 x_InterfaceLineId IN NUMBER DEFAULT NULL,
150 x_ScheduleHeaderId IN NUMBER DEFAULT NULL,
151 x_ScheduleLineId IN NUMBER DEFAULT NULL,
152 x_OrderHeaderId IN NUMBER DEFAULT NULL,
153 x_OrderLineId IN NUMBER DEFAULT NULL,
154 x_ErrorText IN VARCHAR2 DEFAULT NULL,
155 x_ValidationType IN VARCHAR2 DEFAULT NULL,
156 x_GroupInfo IN BOOLEAN DEFAULT FALSE,
157 -- bug 4198330
158 x_ShipfromOrgId IN NUMBER DEFAULT NULL,
159 x_ShipToAddressId IN NUMBER DEFAULT NULL,
160 x_CustomerItemId IN NUMBER DEFAULT NULL,
161 x_InventoryItemId IN NUMBER DEFAULT NULL,
162 x_token1 IN VARCHAR2 DEFAULT NULL,
163 x_value1 IN VARCHAR2 DEFAULT NULL,
164 x_token2 IN VARCHAR2 DEFAULT NULL,
165 x_value2 IN VARCHAR2 DEFAULT NULL,
166 x_token3 IN VARCHAR2 DEFAULT NULL,
167 x_value3 IN VARCHAR2 DEFAULT NULL,
168 x_token4 IN VARCHAR2 DEFAULT NULL,
169 x_value4 IN VARCHAR2 DEFAULT NULL,
170 x_token5 IN VARCHAR2 DEFAULT NULL,
171 x_value5 IN VARCHAR2 DEFAULT NULL,
172 x_token6 IN VARCHAR2 DEFAULT NULL,
173 x_value6 IN VARCHAR2 DEFAULT NULL,
174 x_token7 IN VARCHAR2 DEFAULT NULL, -- Bug 4297984
175 x_value7 IN VARCHAR2 DEFAULT NULL,
176 x_token8 IN VARCHAR2 DEFAULT NULL,
177 x_value8 IN VARCHAR2 DEFAULT NULL,
178 x_token9 IN VARCHAR2 DEFAULT NULL,
179 x_value9 IN VARCHAR2 DEFAULT NULL,
180 x_token10 IN VARCHAR2 DEFAULT NULL,
181 x_value10 IN VARCHAR2 DEFAULT NULL);
182
183 /*===========================================================================
184 PROCEDURE NAME: app_purge_error
185
186 DESCRIPTION: This procedure is called by Purge Schedule
187 Concurrrent Program to process error conditions.
188 It extracts the error
189 message, replaces tokens if any and inserts the
190 error into the rlm_demand_exceptions table.
191
192 PARAMETERS:
193
194 DESIGN REFERENCES:
195
196 ALGORITHM:
197
198 NOTES:
199
200 OPEN ISSUES:
201
202 CLOSED ISSUES:
203
204 CHANGE HISTORY: Ajit Sutar 11/15/2000
205 ===========================================================================*/
206
207
208
209 PROCEDURE app_purge_error (x_ExceptionLevel IN VARCHAR2 DEFAULT 'E',
210 x_MessageName IN VARCHAR2 DEFAULT NULL,
211 x_ErrorText IN VARCHAR2 DEFAULT NULL,
212 x_ChildMessageName IN VARCHAR2 DEFAULT NULL,
213 x_InterfaceHeaderId IN NUMBER DEFAULT NULL,
214 x_InterfaceLineId IN NUMBER DEFAULT NULL,
215 x_ScheduleHeaderId IN NUMBER DEFAULT NULL,
216 x_ScheduleLineId IN NUMBER DEFAULT NULL,
217 x_OrderHeaderId IN NUMBER DEFAULT NULL,
218 x_OrderLineId IN NUMBER DEFAULT NULL,
219 x_ScheduleLineNum IN NUMBER DEFAULT NULL, --bugfix 6319027
220 x_ValidationType IN VARCHAR2 DEFAULT NULL,
221 x_token1 IN VARCHAR2 DEFAULT NULL,
222 x_value1 IN VARCHAR2 DEFAULT NULL,
223 x_token2 IN VARCHAR2 DEFAULT NULL,
224 x_value2 IN VARCHAR2 DEFAULT NULL,
225 x_token3 IN VARCHAR2 DEFAULT NULL,
226 x_value3 IN VARCHAR2 DEFAULT NULL,
227 x_token4 IN VARCHAR2 DEFAULT NULL,
228 x_value4 IN VARCHAR2 DEFAULT NULL,
229 x_token5 IN VARCHAR2 DEFAULT NULL,
230 x_value5 IN VARCHAR2 DEFAULT NULL,
231 x_token6 IN VARCHAR2 DEFAULT NULL,
232 x_value6 IN VARCHAR2 DEFAULT NULL,
233 x_token7 IN VARCHAR2 DEFAULT NULL, -- Bug 4297984
234 x_value7 IN VARCHAR2 DEFAULT NULL,
235 x_token8 IN VARCHAR2 DEFAULT NULL,
236 x_value8 IN VARCHAR2 DEFAULT NULL,
237 x_token9 IN VARCHAR2 DEFAULT NULL,
238 x_value9 IN VARCHAR2 DEFAULT NULL,
239 x_token10 IN VARCHAR2 DEFAULT NULL,
240 x_value10 IN VARCHAR2 DEFAULT NULL,
241 x_user_id IN NUMBER DEFAULT NULL,
242 x_conc_req_id IN NUMBER DEFAULT NULL,
243 x_prog_appl_id IN NUMBER DEFAULT NULL,
244 x_conc_program_id IN NUMBER DEFAULT NULL,
245 x_PurgeStatus IN VARCHAR2 DEFAULT NULL,
246 x_PurgeExp_rec IN t_PurExp_rec DEFAULT NULL);
247
248
249
250
251
252 /*===========================================================================
253 PROCEDURE NAME: get_msg_text
254
255 DESCRIPTION: This procedure provides the message text after
256 performing token substitution. It can process upto
257 4 tokens.
258
259 PARAMETERS: x_message_name IN VARCHAR2
260 x_text IN OUT NOCOPY VARCHAR2
261 x_token1 IN VARCHAR2 DEFAULT NULL
262 x_value1 IN VARCHAR2 DEFAULT NULL
263 x_token2 IN VARCHAR2 DEFAULT NULL
264 x_value2 IN VARCHAR2 DEFAULT NULL
265 x_token3 IN VARCHAR2 DEFAULT NULL
266 x_value3 IN VARCHAR2 DEFAULT NULL
267 x_token4 IN VARCHAR2 DEFAULT NULL
268 x_value4 IN VARCHAR2 DEFAULT NULL
269 x_token5 IN VARCHAR2 DEFAULT NULL
270 x_value5 IN VARCHAR2 DEFAULT NULL
271 x_token6 IN VARCHAR2 DEFAULT NULL
272 x_value6 IN VARCHAR2 DEFAULT NULL
273 x_token7 IN VARCHAR2 DEFAULT NULL
274 x_value7 IN VARCHAR2 DEFAULT NULL
275 x_token8 IN VARCHAR2 DEFAULT NULL
276 x_value8 IN VARCHAR2 DEFAULT NULL
277 x_token9 IN VARCHAR2 DEFAULT NULL
278 x_value9 IN VARCHAR2 DEFAULT NULL
279 x_token10 IN VARCHAR2 DEFAULT NULL
280 x_value10 IN VARCHAR2 DEFAULT NULL
281
282 DESIGN REFERENCES:
283
284 ALGORITHM:
285
286 NOTES:
287
288 OPEN ISSUES:
289
290 CLOSED ISSUES:
291
292 CHANGE HISTORY: Ramana Mulpury Created 9/26/96
293 ===========================================================================*/
294
295 PROCEDURE get_msg_text (x_message_name IN VARCHAR2,
296 x_text IN OUT NOCOPY VARCHAR2,
297 x_token1 IN VARCHAR2 DEFAULT NULL,
298 x_value1 IN VARCHAR2 DEFAULT NULL,
299 x_token2 IN VARCHAR2 DEFAULT NULL,
300 x_value2 IN VARCHAR2 DEFAULT NULL,
301 x_token3 IN VARCHAR2 DEFAULT NULL,
302 x_value3 IN VARCHAR2 DEFAULT NULL,
303 x_token4 IN VARCHAR2 DEFAULT NULL,
304 x_value4 IN VARCHAR2 DEFAULT NULL,
305 x_token5 IN VARCHAR2 DEFAULT NULL,
306 x_value5 IN VARCHAR2 DEFAULT NULL,
307 x_token6 IN VARCHAR2 DEFAULT NULL,
308 x_value6 IN VARCHAR2 DEFAULT NULL,
309 x_token7 IN VARCHAR2 DEFAULT NULL, -- Bug 4297984
310 x_value7 IN VARCHAR2 DEFAULT NULL,
311 x_token8 IN VARCHAR2 DEFAULT NULL,
312 x_value8 IN VARCHAR2 DEFAULT NULL,
313 x_token9 IN VARCHAR2 DEFAULT NULL,
314 x_value9 IN VARCHAR2 DEFAULT NULL,
315 x_token10 IN VARCHAR2 DEFAULT NULL,
316 x_value10 IN VARCHAR2 DEFAULT NULL);
317
318 /*===========================================================================
319 PROCEDURE NAME: insert_row
320
321 DESCRIPTION: Inserts a record into rlm_demand_exceptions
322 table.
323
324 PARAMETERS: x_ExceptionLevel IN VARCHAR2
325 x_MessageName IN VARCHAR2 DEFAULT NULL
326 x_ErrorText IN VARCHAR2 DEFAULT NULL
327 x_InterfaceHeaderId IN NUMBER DEFAULT NULL
328 x_InterfaceLineId IN NUMBER DEFAULT NULL
329 x_ScheduleHeaderId IN NUMBER DEFAULT NULL
330 x_ScheduleLineId IN NUMBER DEFAULT NULL
331 x_Order_Header_Id IN NUMBER DEFAULT NULL
332 x_Order_Line_Id IN NUMBER DEFAULT NULL
333
334 DESIGN REFERENCES:
335
336 ALGORITHM:
337
338 NOTES:
339
340 OPEN ISSUES:
341
342 CLOSED ISSUES:
343
344 CHANGE HISTORY: Abhijit Mitra Created 8/11/98
345 ===========================================================================*/
346
347 PROCEDURE insert_row (x_ExceptionLevel IN VARCHAR2,
348 x_MessageName IN VARCHAR2 DEFAULT NULL,
349 x_ErrorText IN VARCHAR2 DEFAULT NULL,
350 x_InterfaceHeaderId IN NUMBER DEFAULT NULL,
351 x_InterfaceLineId IN NUMBER DEFAULT NULL,
352 x_ScheduleHeaderId IN NUMBER DEFAULT NULL,
353 x_ScheduleLineId IN NUMBER DEFAULT NULL,
354 x_OrderHeaderId IN NUMBER DEFAULT NULL,
355 x_OrderLineId IN NUMBER DEFAULT NULL,
356 x_GroupInfo IN BOOLEAN DEFAULT FALSE,
357 x_user_id IN NUMBER DEFAULT NULL,
358 x_conc_req_id IN NUMBER DEFAULT NULL,
359 x_prog_appl_id IN NUMBER DEFAULT NULL,
363
360 x_conc_program_id IN NUMBER DEFAULT NULL,
361 x_PurgeStatus IN VARCHAR2 DEFAULT NULL);
362
364 PROCEDURE insert_purge_row (x_ExceptionLevel IN VARCHAR2,
365 x_MessageName IN VARCHAR2 DEFAULT NULL,
366 x_ErrorText IN VARCHAR2 DEFAULT NULL,
367 x_InterfaceHeaderId IN NUMBER DEFAULT NULL,
368 x_InterfaceLineId IN NUMBER DEFAULT NULL,
369 x_ScheduleHeaderId IN NUMBER DEFAULT NULL,
370 x_ScheduleLineId IN NUMBER DEFAULT NULL,
371 x_OrderHeaderId IN NUMBER DEFAULT NULL,
372 x_OrderLineId IN NUMBER DEFAULT NULL,
373 x_ScheduleLineNum IN NUMBER DEFAULT NULL, --bugfix 6319027
374 x_user_id IN NUMBER DEFAULT NULL,
375 x_conc_req_id IN NUMBER DEFAULT NULL,
376 x_prog_appl_id IN NUMBER DEFAULT NULL,
377 x_conc_program_id IN NUMBER DEFAULT NULL,
378 x_PurgeStatus IN VARCHAR2 DEFAULT NULL,
379 x_PurgeExp_rec IN t_PurExp_rec DEFAULT NULL);
380
381
382 /*===========================================================================
383
384 /*===========================================================================
385 PROCEDURE NAME: sql_error
386
387 DESCRIPTION: This procedure puts a message on the stack when
388 there is a sql error. It is useful to trace the error
389 to the sql statement causing the exception.
390
391
392 PARAMETERS: x_routine IN VARCHAR2
393 x_location IN VARCHAR2
394
395 DESIGN REFERENCES:
396
397 ALGORITHM:
398
399 NOTES:
400
401 OPEN ISSUES:
402
403 CLOSED ISSUES:
404
405 CHANGE HISTORY: Ramana Mulpury Created 9/26/96
406 ===========================================================================*/
407
408 PROCEDURE sql_error (x_routine IN VARCHAR2,
409 x_location IN VARCHAR2);
410
411 /*===========================================================================
412 PROCEDURE NAME: processing_error
413
414 DESCRIPTION: This procedure puts a message on the stack when
415 there is a fatal processing error. It is useful
416 to trace the error to the procedure/function causing
417 the failure.
418
419
420 PARAMETERS: x_routine IN VARCHAR2
421 x_location IN VARCHAR2
422
423 DESIGN REFERENCES:
424
425 ALGORITHM:
426
427 NOTES:
428
429 OPEN ISSUES:
430
431 CLOSED ISSUES:
432
433 CHANGE HISTORY: Ramana Mulpury Created 9/26/96
434 ===========================================================================*/
435
436 PROCEDURE processing_error (x_routine IN VARCHAR2,
437 x_location IN VARCHAR2);
438
439
440 /*===========================================================================
441 FUNCTION NAME: get
442
443 DESCRIPTION: This function retrieves the message from the
444 stack. It should be used only if the message on
445 the stack is required to be passed to another
446 calling procedure which does not have access to
447 the AOL provided message stack.
448
449 PARAMETERS:
450
451 DESIGN REFERENCES:
452
453 ALGORITHM:
454
455 NOTES:
456
457 OPEN ISSUES:
458
459 CLOSED ISSUES:
460
461 CHANGE HISTORY: Ramana Mulpury Created 9/26/96
462 ===========================================================================*/
463
464 FUNCTION get RETURN VARCHAR2;
465 --
466 fatal_error_flag VARCHAR2(1);
467 --
468 g_info_flag VARCHAR2(1);
469 --
470 g_warn_flag VARCHAR2(1);
471 --
472 g_error_flag VARCHAR2(1);
473 --
474 ---------------------------------------------------------------------------
475 -- DEPENDENCY CHECKS
476 ---------------------------------------------------------------------------
477 g_message_rec t_message_rec;
478
479 TYPE message_tab_type IS TABLE OF g_message_rec%TYPE
480 INDEX BY BINARY_INTEGER;
481
482 g_message_tab message_tab_type;
483 TYPE dep_rec_type IS RECORD
484 (val_name VARCHAR2(30),
485 dep_name VARCHAR2(30),
486 error_flag VARCHAR2(1));
487
488 TYPE dep_tab_type IS TABLE of dep_rec_type INDEX BY BINARY_INTEGER;
489
490 -- The g_dependency_table will store the dependency array of all the validations
491 -- dependencies on which it depends and also the error flag which is
492 -- stored for the validation. If the validation fails then the error flag will
493 -- be set to Y so that any further validations with other objects need not be
494 -- performed
495
496 g_dependency_tab dep_tab_type;
497
498 PROCEDURE set_Dependent_error( x_name VARCHAR2);
499 --
500 PROCEDURE set_fatal_error;
501 --
502 PROCEDURE dump_messages;
503 --
504 PROCEDURE dump_messages(x_header_id IN NUMBER);
505 --
506 PROCEDURE initialize_messages;
507 --
508 FUNCTION fatal_error_found
509 RETURN BOOLEAN;
510 --
511 FUNCTION check_dependency(x_name VARCHAR2)
515
512 RETURN BOOLEAN ;
513
514 PROCEDURE initialize_dependency (x_module VARCHAR2);
516 PROCEDURE reset_dependency( x_val_name IN VARCHAR2 DEFAULT NULL);
517
518 --
519 g_conc_req_id NUMBER;
520 --
521 PROCEDURE populate_req_id;
522 --
523 FUNCTION get_conc_req_id
524 RETURN NUMBER;
525
526 -- Bug#: 2771756 : Added the new procedure RemoveMessages.
527 -- Bug: 4198330 : Added grouping criteria to the removeMessages
528
529 PROCEDURE removeMessages (p_header_id IN NUMBER,
530 p_message IN VARCHAR2,
531 p_message_type IN VARCHAR2,
532 p_ship_from_org_id IN NUMBER DEFAULT NULL,
533 p_ship_to_address_id IN NUMBER DEFAULT NULL,
534 p_customer_item_id IN NUMBER DEFAULT NULL,
535 p_inventory_item_id IN NUMBER DEFAULT NULL);
536
537 END RLM_MESSAGE_SV;