1 PACKAGE BODY CLN_INV_REJECT_NOTIF_PVT AS
2 /* $Header: CLN3C4B.pls 120.2 2006/05/10 23:56:24 smuthuav noship $ */
3
4 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
5
6 -- Start of comments
7 -- API name : SEPARATE_REASON_CODE
8 -- Type : Private.
9 -- Pre-reqs : None.
10 -- Function : It separates the line number from the given reasoncode
11 -- Version : Current version 1.0
12 -- Initial version 1.0
13 -- Notes :
14 -- End of comments
15
16 PROCEDURE SEPARATE_REASON_CODE (p_reason_code IN VARCHAR2,
17 x_err_string OUT NOCOPY VARCHAR2,
18 x_line_num OUT NOCOPY NUMBER) AS
19
20 l_pos NUMBER;
21 l_error_code VARCHAR(30);
22 l_errmsg VARCHAR2(1000);
23
24 BEGIN
25
26 IF (l_debug_level <= 2) THEN
27 cln_debug_pub.Add('Entering the procedure SEPARATE_REASON_CODE with the parameter '|| p_reason_code ,2);
28 END IF;
29
30 l_pos := instr(p_reason_code, ':');
31 x_err_string := substr(p_reason_code,l_pos+1);
32 x_line_num := substr(p_reason_code,0,l_pos-1);
33 x_line_num := to_number(x_line_num);
34
35 IF (l_debug_level <= 2) THEN
36 cln_debug_pub.Add('Exiting the procedure SEPARATE_REASON_CODE with parameters.... ', 2);
37 END IF;
38
39 IF (l_debug_level <= 1) THEN
40 cln_debug_pub.Add('LINE NUMBER:'|| x_line_num ,1);
41 cln_debug_pub.Add('ERROR STRING:'|| x_err_string ,1);
42 END IF;
43
44 EXCEPTION
45
46 WHEN OTHERS THEN
47 l_error_code := SQLCODE;
48 l_errmsg := SQLERRM;
49
50 IF (l_debug_level <= 5) THEN
51 cln_debug_pub.Add('Exception in SEPARATE_REASON_CODE' || ':' || l_error_code || ':' ||l_errmsg,5);
52 END IF;
53
54 END SEPARATE_REASON_CODE;
55
56
57 -- Start of comments
58 -- API name : ADD_MESSAGES_TO_COLL_HISTORY
59 -- Type : Private.
60 -- Pre-reqs : None.
61 -- Function : Adds the messages to the CH.
62 -- Version : Current version 1.0
63 -- Initial version 1.0
64 -- Notes :
65 -- End of comments
66
67 PROCEDURE ADD_MESSAGES_TO_COLL_HISTORY( p_internal_control_number IN NUMBER,
68 p_line_num IN NUMBER,
69 p_err_string IN VARCHAR2,
70 p_id IN VARCHAR2 ) AS
71
72 l_error_code VARCHAR(30);
73 l_errmsg VARCHAR2(1000);
74 l_parameter_list wf_parameter_list_t;
75
76 BEGIN
77
78 IF (l_debug_level <= 2) THEN
79 cln_debug_pub.Add('...Entering the procedure ADD_MESSAGES_TO_COLL_HISTORY with parameters...', 2);
80 END IF;
81
82 IF (l_debug_level <= 1) THEN
83 cln_debug_pub.Add('INTERNAL CONTROL NUMBER:'|| p_internal_control_number ,1);
84 cln_debug_pub.Add('P_ID:'|| p_id ,1);
85 cln_debug_pub.Add('REFERENCE_ID1:'|| p_line_num ,1);
86 cln_debug_pub.Add('DETAIL_MESSAGE:'|| p_err_string ,1);
87 END IF;
88
89 l_parameter_list := wf_parameter_list_t();
90 wf_event.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_parameter_list);
91 wf_event.AddParameterToList('REFERENCE_ID1',p_line_num,l_parameter_list);
92 wf_event.AddParameterToList('DETAIL_MESSAGE',p_err_string,l_parameter_list);
93
94 -- Add the error string to the Collaboration History
95 IF (l_debug_level <= 2) THEN
96 cln_debug_pub.Add('Raising the ----oracle.apps.cln.ch.collaboration.addmessage----- event',2);
97 END IF;
98
99 BEGIN
100 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.addmessage',
101 p_event_key => p_id,
102 p_parameters => l_parameter_list);
103
104 IF (l_debug_level <= 1) THEN
105 cln_debug_pub.Add('Add Message event raised',1);
106 END IF;
107
108 IF (l_debug_level <= 2) THEN
109 cln_debug_pub.Add('Exiting the procedure CLN_INV_REJECT_NOTIF_PVT.ADD_MESSAGES_TO_COLL_HISTORY', 2);
110 END IF;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 l_error_code := SQLCODE;
115 l_errmsg := SQLERRM;
116
117 IF (l_debug_level <= 5) THEN
118 cln_debug_pub.Add('Exception in raising the ----oracle.apps.cln.ch.collaboration.addmessage-----
119 event' || ':' || l_error_code || ':' || l_errmsg,5);
120 END IF;
121 END;
122 END ADD_MESSAGES_TO_COLL_HISTORY;
123
124
125 -- Start of comments
126 -- API name : CALL_AR_API
127 -- Type : Private.
128 -- Pre-reqs : None.
129 -- Function : Calls 'ar_confirmation.initiate_confirmation_process' to send the notification
130 -- Version : Current version 1.0
131 -- Initial version 1.0
132 -- Notes :
133 -- End of comments
134
135 PROCEDURE CALL_AR_API(p_reason_code IN VARCHAR2,
136 p_id IN VARCHAR2,
137 p_description IN VARCHAR2,
138 p_internal_control_number IN NUMBER) AS
139
140 l_error_code VARCHAR(30);
141 l_errmsg VARCHAR2(1000);
142
143 BEGIN
144 IF (l_debug_level <= 2) THEN
145 cln_debug_pub.Add('Entering the procedure CLN_INV_REJECT_NOTIF_PVT.CALL_AR_API', 2);
146 END IF;
147
148 IF (l_debug_level <= 2) THEN
149 cln_debug_pub.Add('Calling the -----ar_confirmation.initiate_confirmation_process----API with the parameter' ||
150 p_reason_code ,2);
151 END IF;
152
153 BEGIN
154 ar_confirmation.initiate_confirmation_process(p_status =>'10',
155 p_id => p_id,
156 p_reason_code => p_reason_code,
157 p_description => p_description,
158 p_int_ctr_num => p_internal_control_number);
159
160 IF (l_debug_level <= 2) THEN
161 cln_debug_pub.Add('Exting the --ar_confirmation.initiate_confirmation_process----API',2);
162 cln_debug_pub.Add('Exiting the procedure CLN_INV_REJECT_NOTIF_PVT.CALL_AR_API', 2);
163 END IF;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 l_error_code := SQLCODE;
168 l_errmsg := SQLERRM;
169
170 IF (l_debug_level <= 5) THEN
171 cln_debug_pub.Add('Exception in calling the procedure----
172 ar_confirmation.initiate_confirmation_process--:'||l_error_code || ':' || l_errmsg,5);
173 END IF;
174 END;
175 END CALL_AR_API;
176
177
178 -- Start of comments
179 -- API name : PROCESS_INBOUND_3C4
180 -- Type : Private.
181 -- Pre-reqs : None.
182 -- Function : This procedure
183 -- (1). Separates individual reason codes from the value of the parameter
184 -- 'p_reason_code' and calls the ar_confirmation.initiate_confirmation_process'
185 -- API the number of times as the number of times the reason codes are.
186 -- (2). Updates the Collaboration History
187 -- Version : Current version 1.0
188 -- Initial version 1.0
189 -- Notes : This procedure is called from the XML map(3C4 Inbound)
190 -- End of comments
191
192 PROCEDURE PROCESS_INBOUND_3C4 (p_internal_control_number IN NUMBER,
193 p_reason_code IN VARCHAR2,
194 p_invoice_num IN VARCHAR2,
195 p_description IN VARCHAR2,
196 p_tp_id IN NUMBER) AS
197
198 -- declare the local variables
199 l_all_reason_code VARCHAR2(3000);
200 l_header_reason_code VARCHAR2(1000);
201 l_line_reason_code VARCHAR2(3000);
202 l_errmsg VARCHAR2(2000);
203 l_error_code VARCHAR2(30);
204 l_internal_control_number NUMBER;
205 l_comma_position NUMBER;
206 l_party_id NUMBER;
207 l_party_site_id NUMBER;
208 l_err_string VARCHAR2(2000);
209 l_line_num NUMBER;
210 l_invoice_ref_id VARCHAR2(255); -- l_invoice_ref_id is the document_transfer_id : customer_trx_id
211 l_trx_number VARCHAR2(255);
212 l_org_id NUMBER;
213 l_doc_transfer_id VARCHAR2(200);
214 l_cust_trx_id VARCHAR2(200);
215 l_parameter_list wf_parameter_list_t;
216
217 BEGIN
218 IF (l_debug_level <= 2) THEN
219 cln_debug_pub.Add('...Entering the procedure CLN_INV_REJECT_NOTIF_PVT.PROCESS_INBOUND_3C4 with parameters ...', 2);
220 END IF;
221
222 IF (l_debug_level <= 1) THEN
223 cln_debug_pub.Add('Invoice number:'|| p_invoice_num ,1);
224 cln_debug_pub.Add('Internal control number:'|| p_internal_control_number ,1);
225 cln_debug_pub.Add('Reason Code:'|| p_reason_code ,1);
226 cln_debug_pub.Add('Description'|| p_description ,1);
227 cln_debug_pub.Add('Trading Partner Code:'|| p_tp_id ,1);
228 END IF;
229
230 SELECT party_id, party_site_id
231 INTO l_party_id, l_party_site_id
232 FROM ecx_tp_headers
233 WHERE tp_header_id = p_tp_id;
234
235 IF (l_debug_level <= 1) THEN
236 cln_debug_pub.Add('Party ID : '|| l_party_id, 1);
237 cln_debug_pub.Add('Party Site ID : '|| l_party_site_id, 1);
238 END IF;
239
240 -- get the customer_trx_id and doc_trnsfr_id,org_id
241 BEGIN
242
243 SELECT DOCUMENT_TRANSFER_ID,CUSTOMER_TRX_ID,RCT.ORG_ID
244 INTO l_doc_transfer_id,l_cust_trx_id,l_org_id
245 FROM AR_DOCUMENT_TRANSFERS ADT,RA_CUSTOMER_TRX_ALL RCT, HZ_CUST_SITE_USES_ALL CSU, HZ_CUST_ACCT_SITES_ALL CAS, HZ_CUST_ACCOUNTS_ALL CA
246 WHERE RCT.TRX_NUMBER = p_invoice_num
247 AND ADT.SOURCE_ID = RCT.CUSTOMER_TRX_ID
248 AND ADT.SOURCE_TABLE ='RA_CUSTOMER_TRX' -- Bug #4938901.
249 AND RCT.BILL_TO_CUSTOMER_ID = CA.CUST_ACCOUNT_ID
250 AND RCT.BILL_TO_SITE_USE_ID = CSU.SITE_USE_ID
251 AND RCT.COMPLETE_FLAG = 'Y'
252 AND CAS.PARTY_SITE_ID = l_party_site_id;
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 l_error_code := SQLCODE;
257 l_errmsg := SQLERRM;
258
259 IF (l_debug_level <= 5) THEN
260 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_errmsg,5);
261 END IF;
262 END;
263
264
265 l_invoice_ref_id := l_doc_transfer_id ||':'||l_cust_trx_id;
266
267 IF (l_debug_level <= 1) THEN
268 cln_debug_pub.Add('DOCUMENT_TRANSFER_ID: '|| l_doc_transfer_id, 1);
269 cln_debug_pub.Add('CUSTOMER_TRX_ID: '|| l_cust_trx_id, 1);
270 cln_debug_pub.Add('ORG_ID: '|| l_org_id, 1);
271 cln_debug_pub.Add('l_invoice_ref_id: '|| l_invoice_ref_id, 1);
272
273 END IF;
274
275 IF (l_debug_level <= 2) THEN
276 cln_debug_pub.Add('Raising the ----oracle.apps.cln.ch.collaboration.update----- event,with parameters ',2);
277 END IF;
278
279 IF (l_debug_level <= 1) THEN
280 cln_debug_pub.Add('DOCUMENT_NO: '|| p_invoice_num, 1);
281 cln_debug_pub.Add('XMLG_INTERNAL_CONTROL_NUMBER: '|| p_internal_control_number, 1);
282 cln_debug_pub.Add('ORG_ID: '|| l_org_id, 1);
283 END IF;
284
285 l_parameter_list:= wf_parameter_list_t();
286
287 -- add the parameters to the parameter list
288 wf_event.AddParameterToList('DOCUMENT_NO',p_invoice_num,l_parameter_list);
289 wf_event.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_parameter_list);
290 wf_event.AddParameterToList('MESSAGE_TEXT','CLN_CH_XML_CONSUMED_SUCCESS',l_parameter_list);
291 wf_event.AddParameterToList('ORG_ID',l_org_id,l_parameter_list);
292
293 -- raise the 'oracle.apps.cln.ch.collaboration.update' event for Collaboration History Updation
294 BEGIN
295 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update', -- Bug #5219307
296 p_event_key => l_invoice_ref_id,
297 p_parameters => l_parameter_list);
298
299 IF (l_debug_level <= 1) THEN
300 cln_debug_pub.Add('----Collaboration History update event raised---',1);
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 l_error_code := SQLCODE;
306 l_errmsg := SQLERRM;
307
308 IF (l_debug_level <= 5) THEN
309 cln_debug_pub.Add('Exception in calling the ----oracle.apps.cln.ch.collaboration.update----- API' ||
310 ':' || l_error_code || ':' || l_errmsg,5);
311 END IF;
312 END;
313
314 -- separate the error reason strings from the original reason code
315 l_all_reason_code:=rtrim(ltrim(p_reason_code));
316
317 IF (l_debug_level <= 1) THEN
318 cln_debug_pub.Add('Original complete reason code:'|| l_all_reason_code ,1);
319 END IF;
320
321 --Check if the string is of format %HeaderCode,linenum:linecode,linenum:linecode%
322 IF (substr(l_all_reason_code,1,1) <> '%') and (substr(l_all_reason_code,length(l_all_reason_code),1) <> '%') THEN
323 --This is just a message description and is not in the expected %HeaderCode,linenum:linecode,linenum:linecode% format
324
325 IF (l_debug_level <= 1) THEN
326 cln_debug_pub.Add('This is just a message description and is not in the expected %HeaderCode,
327 linenum:linecode,linenum:linecode% format',1);
328 END IF;
329
330 IF (l_debug_level <= 2) THEN
331 cln_debug_pub.Add('Calling ---- CALL_AR_API----' ,2);
332 END IF;
333
334 CALL_AR_API(p_reason_code => l_all_reason_code,
335 p_id => l_invoice_ref_id,
336 p_description => l_all_reason_code,
337 p_internal_control_number => p_internal_control_number);
338
339 IF (l_debug_level <= 2) THEN
340 cln_debug_pub.Add('Calling ---- ADD_MESSAGES_TO_COLL_HISTORY---- ',2);
341 END IF;
342
343 -- Add the error description to the CH
344 ADD_MESSAGES_TO_COLL_HISTORY( p_internal_control_number => p_internal_control_number,
345 p_line_num => null,
346 p_err_string => l_all_reason_code,
347 p_id => p_invoice_num);
348
349 ELSE
350 -- The error string obtained may be in %HeaderCode,linenum:linecode,linenum:linecode% format
351 -- remove % at start and end
352 l_all_reason_code := substr(l_all_reason_code,2,length(l_all_reason_code)-2);
353
354 --Get the header reason code
355 l_comma_position := instr(l_all_reason_code,',');
356
357 IF (l_debug_level <= 1) THEN
358 cln_debug_pub.Add('The String has a comma at '|| l_comma_position,1);
359 END IF;
360
361 IF (l_comma_position=0) or (l_comma_position is NULL) THEN
362 -- The string should be %% or %header%
363 IF l_all_reason_code IS NULL THEN
364 -- The string should be %%
365 l_header_reason_code := nvl(l_header_reason_code,'UNEXPECTED ERROR');
366 l_all_reason_code := NULL;
367
368 IF (l_debug_level <= 1) THEN
369 cln_debug_pub.Add('Sending -- UNEXPECTED ERROR: String may be of form %%-- for l_header_reason_code',1);
370 END IF;
371 ELSE
372 IF (l_debug_level <= 1) THEN
373 cln_debug_pub.Add('Only Header',1);
374 END IF;
375
376 l_header_reason_code := l_all_reason_code;
377 l_all_reason_code := NULL;
378 END IF;
379
380 ELSE
381 l_header_reason_code := substr(l_all_reason_code,1,l_comma_position-1);
382
383 IF (l_debug_level <= 1) THEN
384 cln_debug_pub.Add('l_header_reason_code :'||l_header_reason_code ,1);
385 END IF;
386
387 l_all_reason_code := substr(l_all_reason_code,l_comma_position+1);
388
389 IF (l_debug_level <= 1) THEN
390 cln_debug_pub.Add('l_all_reason_code :'||l_all_reason_code ,1);
391 END IF;
392
393 --When the string may be %,linenum:linecode...% or %,%
394 IF (not (length(l_header_reason_code) > 0)) or TO_CHAR((length(l_header_reason_code))) is NULL THEN
395 --When the string is %,%
396 IF (not ( length(l_all_reason_code)> 0 )) or TO_CHAR((length(l_all_reason_code))) is NULL THEN
397
398 l_header_reason_code := nvl(l_header_reason_code,'UNEXPECTED ERROR');
399 l_all_reason_code := NULL;
400
401 IF (l_debug_level <= 1) THEN
402 cln_debug_pub.Add('l_header_reason_code is set as UNEXPECTED ERROR: String may be of form %,%',1);
403 END IF;
404
405 ELSE
406
407 -- When the string is %,linenum:linecode%
408 l_header_reason_code := NULL;
409
410 IF (l_debug_level <= 1) THEN
411 cln_debug_pub.Add('The header reason code is set to NULL and l_all_reason_code is '|| l_all_reason_code,1);
412 END IF;
413
414 END IF;
415 END IF;
416
417 END IF;
418
419 -- Send Notification and update Collaboration History for Header
420 IF l_header_reason_code is not null and length(l_header_reason_code) > 0 THEN
421
422 IF (l_debug_level <= 2) THEN
423 cln_debug_pub.Add('Calling ---- CALL_AR_API----' ,2);
424 END IF;
425
426 IF (l_debug_level <= 1) THEN
427 cln_debug_pub.Add('p_reason_code:' || l_header_reason_code ,1);
428 cln_debug_pub.Add('p_description:' || p_description ,1);
429 END IF;
430
431 CALL_AR_API(p_reason_code => l_header_reason_code,
432 p_id => l_invoice_ref_id,
433 p_description => p_description,
434 p_internal_control_number => p_internal_control_number);
435
436 IF (l_debug_level <= 2) THEN
437 cln_debug_pub.Add('Calling ---- ADD_MESSAGES_TO_COLL_HISTORY----' ,2);
438 END IF;
439
440 IF (l_debug_level <= 1) THEN
441 cln_debug_pub.Add('p_err_string:' || l_header_reason_code ,1);
442 END IF;
443
444 -- add the reason code to the collaboration history
445 ADD_MESSAGES_TO_COLL_HISTORY( p_internal_control_number => p_internal_control_number,
446 p_line_num => NULL,
447 p_err_string => l_header_reason_code,
448 p_id => p_invoice_num);
449 END IF;
450
451 --take action for eachline
452 WHILE l_all_reason_code is not null and length(l_all_reason_code) > 0 LOOP
453
454 l_comma_position := instr(l_all_reason_code,',');
455
456 IF (l_comma_position=0) THEN
457 l_line_reason_code := l_all_reason_code;
458 l_all_reason_code := NULL;
459
460 IF (l_debug_level <= 1) THEN
461 cln_debug_pub.Add('Comma position is zero',1);
462 cln_debug_pub.Add('l_line_reason_code:' || l_all_reason_code ,1);
463 END IF;
464
465 ELSE
466 l_line_reason_code := substr(l_all_reason_code,1,l_comma_position-1);
467 l_all_reason_code := substr(l_all_reason_code,l_comma_position+1);
468
469 IF (l_debug_level <= 1) THEN
470 cln_debug_pub.Add('Comma position is NOT zero',1);
471 cln_debug_pub.Add('l_line_reason_code:' || l_line_reason_code ,1);
472 cln_debug_pub.Add('l_all_reason_code:' || l_all_reason_code ,1);
473 END IF;
474
475 END IF;
476
477 IF l_line_reason_code is not null and length(l_line_reason_code) > 0 THEN
478
479 -- take action for line
480 IF (l_debug_level <= 2) THEN
481 cln_debug_pub.Add('Calling ----SEPARATE_REASON_CODE----API with the following parameters',2);
482 END IF;
483
484 IF (l_debug_level <= 1) THEN
485 cln_debug_pub.Add('l_line_reason_code'|| l_line_reason_code,1);
486 END IF;
487
488 SEPARATE_REASON_CODE(p_reason_code => l_line_reason_code,
489 x_err_string => l_err_string,
490 x_line_num => l_line_num);
491
492 IF (l_debug_level <= 2) THEN
493 cln_debug_pub.Add('Calling the ---- CALL_AR_API-----with parameters ',2);
494 END IF;
495
496 IF (l_debug_level <= 1) THEN
497 cln_debug_pub.Add('p_reason_code:'|| l_err_string ,1);
498 cln_debug_pub.Add('p_description:'|| p_description ,1);
499 END IF;
500
501 -- Call the AR procedure to send Notification to System Administrator.
502 CALL_AR_API(p_reason_code => l_err_string,
503 p_id => l_invoice_ref_id,
504 p_description => p_description,
505 p_internal_control_number => p_internal_control_number);
506
507 IF (l_debug_level <= 2) THEN
508 cln_debug_pub.Add('Calling the ---- ADD_MESSAGES_TO_COLL_HISTORY-----with parameters ',2);
509 END IF;
510
511 IF (l_debug_level <= 1) THEN
512 cln_debug_pub.Add('p_line_num:'|| l_line_num ,1);
513 cln_debug_pub.Add('p_err_string:'|| l_err_string ,1);
514 END IF;
515
516 -- add the reason code to the collaboration history
517 ADD_MESSAGES_TO_COLL_HISTORY( p_internal_control_number => p_internal_control_number,
518 p_line_num => l_line_num,
519 p_err_string => l_err_string,
520 p_id => p_invoice_num);
521 END IF;
522 END LOOP;
523
524 END IF;
525
526 IF (l_debug_level <= 2) THEN
527 cln_debug_pub.Add('Exiting the procedure CLN_INV_REJECT_NOTIF_PVT.PROCESS_INBOUND_3C4', 2);
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 l_error_code := SQLCODE;
533 l_errmsg := SQLERRM;
534
535 IF (l_debug_level <= 5) THEN
536 cln_debug_pub.Add('---Exception in PROCESS_INBOUND_3C4 as ---' || l_error_code || ':' || l_errmsg,5);
537 END IF;
538
539 END PROCESS_INBOUND_3C4;
540
541
542 -- Start of comments
543 -- API name : NOTIFICATION_PROCESS_3C4_IN
544 -- Type : Private
545 -- Pre-reqs : None.
546 -- Function : This procedure does the notification processing for the 3C4 Inbound. It performs all the
547 -- pre-defined actions defined in the notification code '3C4_01'.
548 -- Version : Current version 1.0
549 -- Initial version 1.0
550 -- Notes : This procedure is called from the XML map(3C4 Inbound)
551 -- End of comments
552
553 PROCEDURE NOTIFICATION_PROCESS_3C4_IN (p_itemtype IN VARCHAR2,
554 p_itemkey IN VARCHAR2,
555 p_actid IN NUMBER,
556 p_funcmode IN VARCHAR2,
557 x_resultout IN OUT NOCOPY VARCHAR2) AS
558
559 -- declare local variables
560 l_notif_code VARCHAR2(100);
561 l_notif_desc VARCHAR2(100);
562 l_status VARCHAR2(100);
563 l_app_ref_id VARCHAR2(255);
564 l_return_code VARCHAR2(10);
565 l_return_desc VARCHAR2(2000);
566 l_coll_pt VARCHAR2(100);
567 l_intrl_cntrl_num VARCHAR2(100);
568 l_errmsg VARCHAR2(2000);
569 l_error_code VARCHAR2(30);
570 l_tp_id NUMBER;
571
572 BEGIN
573 IF (l_debug_level <= 2) THEN
574 cln_debug_pub.Add('Entering the procedure CLN_INV_REJECT_NOTIF_PVT.notification_process_3c4_in', 2);
575 END IF;
576
577 -- get the workflow activity attributes.
578 l_notif_code:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_CODE');
579
580 IF (l_debug_level <= 1) THEN
581 cln_debug_pub.Add('Notification_code:'|| l_notif_code , 1);
582 END IF;
583
584 l_notif_desc:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_DESC');
585
586 IF (l_debug_level <= 1) THEN
587 cln_debug_pub.Add('Notification_description:'|| l_notif_desc , 1);
588 END IF;
589
590 l_status:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'STATUS');
591
592 IF (l_debug_level <= 1) THEN
593 cln_debug_pub.Add('Status:'|| l_status , 1);
594 END IF;
595
596 l_tp_id:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'TPID');
597
598 IF (l_debug_level <= 1) THEN
599 cln_debug_pub.Add('Trading Partner ID:'|| l_tp_id , 1);
600 END IF;
601
602 l_app_ref_id :=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'REFERENCE');
603
604 IF (l_debug_level <= 1) THEN
605 cln_debug_pub.Add('Application Reference ID:'|| l_app_ref_id , 1);
606 END IF;
607
608 l_coll_pt:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'COLL_POINT');
609
610 IF (l_debug_level <= 1) THEN
611 cln_debug_pub.Add('Collaboration Point:'|| l_coll_pt, 1);
612 END IF;
613
614 l_intrl_cntrl_num:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'XMLG_INTERNAL_CONTROL_NUMBER');
615
616 IF (l_debug_level <= 1) THEN
617 cln_debug_pub.Add('Internal Control Number:'|| l_intrl_cntrl_num, 1);
618 END IF;
619
620 IF (l_debug_level <= 2) THEN
621 cln_debug_pub.Add('Calling the ----CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS----- API with the above parameters...',2);
622 END IF;
623
624 IF (l_debug_level <= 1) THEN
625 cln_debug_pub.Add('.....Validating the Error String format......... :',1);
626 END IF;
627
628 --Check if the string is of format %HeaderCode,linenum:linecode,linenum:linecode%
629
630 IF (substr(l_notif_desc,1,1) <> '%') and (substr(l_notif_desc,length(l_notif_desc),1) <> '%') THEN
631 IF (l_debug_level <= 1) THEN
632 cln_debug_pub.Add('The error code is just a description',1);
633 END IF;
634
635 l_notif_desc := NULL;
636 ELSE
637
638 l_notif_desc := substr(l_notif_desc,2,length(l_notif_desc)-2);
639
640 IF length(l_notif_desc) = 0 or length(l_notif_desc) = 1 or (length(l_notif_desc) is NULL) THEN
641 l_notif_desc := 'UNEXPECTED ERROR OCCURED';
642
643 IF (l_debug_level <= 1) THEN
644 cln_debug_pub.Add('l_notif_desc' || l_notif_desc,1);
645 END IF;
646
647 ELSE
648 l_notif_desc := NULL;
649 END IF;
650 END IF;
651
652 -- Calls the CLN Notification Processing API to perform the pre-defined actions
653 BEGIN
654 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS( x_ret_code => l_return_code ,
655 x_ret_desc => l_return_desc,
656 p_notification_code => l_notif_code,
657 p_notification_desc => l_notif_desc,
658 p_status => l_status,
659 p_tp_id => l_tp_id,
660 p_reference => l_app_ref_id,
661 p_coll_point => l_coll_pt,
662 p_int_con_no => l_intrl_cntrl_num);
663
664 IF (l_debug_level <= 2) THEN
665 cln_debug_pub.Add('Exiting the ----CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS----- API with the below parameters...',2);
666 END IF;
667
668 IF (l_debug_level <= 1) THEN
669 cln_debug_pub.Add('Return Code:'|| l_return_code, 1);
670 cln_debug_pub.Add('Return Description:'|| l_return_desc, 1);
671 END IF;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 l_error_code := SQLCODE;
676 l_errmsg := SQLERRM;
677
678 IF (l_debug_level <= 5) THEN
679 cln_debug_pub.Add('Exception in CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS:' || l_error_code || ' : '||l_errmsg,5);
680 END IF;
681 END;
682
683 x_resultout := 'SUCCESS';
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 l_error_code := SQLCODE;
688 l_errmsg := SQLERRM;
689
690 IF (l_debug_level <= 5) THEN
691 cln_debug_pub.Add('Exception in NOTIFICATION_PROCESS_3C4_IN:' || l_error_code || ':' || l_errmsg,5);
692 END IF;
693
694 x_resultout := 'ERROR';
695
696 IF (l_debug_level <= 2) THEN
697 cln_debug_pub.Add('Exiting the ----NOTIFICATION_PROCESS_3C4_IN----- API with Resultout as ...'||x_resultout,2);
698 END IF;
699
700 END NOTIFICATION_PROCESS_3C4_IN;
701
702 BEGIN
703 l_debug_level:= to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
704
705 END CLN_INV_REJECT_NOTIF_PVT;