[Home] [Help]
PACKAGE BODY: APPS.CLN_NTFYINVC_PKG
Source
1 PACKAGE BODY CLN_NTFYINVC_PKG AS
2 /* $Header: CLN3C3B.pls 120.2 2006/10/04 11:28:07 bsaratna noship $ */
3
4 l_debug_level NUMBER;
5
6 FUNCTION GET_AR_REJECTIONS_STRING(p_invoice_id IN NUMBER)
7 RETURN VARCHAR2 IS
8
9 l_parent_id AP_INTERFACE_REJECTIONS.PARENT_ID%TYPE;
10 l_reject_code AP_INTERFACE_REJECTIONS.REJECT_LOOKUP_CODE%TYPE;
11 l_error_reject_string VARCHAR2(2000);
12 l_count_failed_rows VARCHAR2(10);
13
14 CURSOR c_header_errors IS
15 SELECT PARENT_ID, REJECT_LOOKUP_CODE
16 FROM AP_INTERFACE_REJECTIONS
17 WHERE PARENT_ID = p_invoice_id
18 AND PARENT_TABLE = 'AP_INVOICES_INTERFACE';
19
20 CURSOR c_line_errors IS
21 SELECT PARENT_ID, REJECT_LOOKUP_CODE
22 FROM AP_INTERFACE_REJECTIONS
23 WHERE PARENT_ID in (SELECT INVOICE_LINE_ID FROM AP_INVOICE_LINES_INTERFACE WHERE INVOICE_ID = p_invoice_id)
24 AND PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE';
25
26 BEGIN
27 IF (l_Debug_Level <= 2) THEN
28 cln_debug_pub.Add('------ Entering CLN_NTFYINVC_PKG.GET_AR_REJECTIONS_STRING API ------ ', 2);
29 END IF;
30
31 IF (l_Debug_Level <= 1) THEN
32 cln_debug_pub.Add('p_invoice_id : ' || p_invoice_id, 1);
33 END IF;
34
35 -- check for failed rows..
36 BEGIN
37 SELECT 'x'
38 INTO l_count_failed_rows
39 FROM DUAL
40 WHERE EXISTS (
41 SELECT 'x'
42 FROM ap_interface_rejections air
43 WHERE parent_table = 'AP_INVOICES_INTERFACE'
44 AND parent_id = p_invoice_id
45 UNION ALL
46 SELECT 'x'
47 FROM ap_interface_rejections air
48 WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
49 AND parent_id in (select invoice_line_id from ap_invoice_lines_interface aili
50 where aili.invoice_id = p_invoice_id)
51 );
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 cln_debug_pub.Add('No errors found in the interface rejections table', 1);
55 END;
56
57 IF (l_count_failed_rows = 'x') THEN
58 l_error_reject_string := '';
59
60 OPEN c_header_errors;
61 FETCH c_header_errors INTO l_parent_id, l_reject_code;
62
63 IF (l_Debug_Level <= 1) THEN
64 cln_debug_pub.Add('Entered Cursor to find error message at header', 1);
65 cln_debug_pub.Add('Reject Lookup Code '||l_reject_code, 1);
66 END IF;
67
68 CLOSE c_header_errors;
69
70 l_error_reject_string := l_reject_code || ',';
71
72 OPEN c_line_errors;
73 LOOP
74 FETCH c_line_errors INTO l_parent_id, l_reject_code;
75 EXIT WHEN c_line_errors%NOTFOUND;
76 -- process row here
77 IF (l_Debug_Level <= 1) THEN
78 cln_debug_pub.Add('Entered Cursor to find error message at line level', 1);
79 cln_debug_pub.Add('Parent ID '||l_parent_id, 1);
80 cln_debug_pub.Add('Reject Lookup Code '||l_reject_code, 1);
81 END IF;
82
83 l_error_reject_string := l_error_reject_string || l_parent_id || ':' || l_reject_code || ',';
84 END LOOP;
85
86 CLOSE c_line_errors;
87
88 IF (l_Debug_Level <= 1) THEN
89 cln_debug_pub.Add('Moving out of Cursor .....', 1);
90 END IF;
91
92 l_error_reject_string := substr(l_error_reject_string,1,length(l_error_reject_string)-1);
93 l_error_reject_string := '%' || l_error_reject_string || '%';
94 ELSE
95
96 l_error_reject_string := NULL;
97 END IF;
98
99 IF (l_Debug_Level <= 1) THEN
100 cln_debug_pub.Add('l_error_reject_string : '|| l_error_reject_string, 1);
101 END IF;
102
103 RETURN l_error_reject_string;
104
105 IF (l_Debug_Level <= 2) THEN
106 cln_debug_pub.Add('------ Exiting CLN_NTFYINVC_PKG.GET_AR_REJECTIONS_STRING API ------ ', 2);
107 END IF;
108 END GET_AR_REJECTIONS_STRING;
109
110
111 PROCEDURE RAISE_3C4_EVENT(
112 p_invoice_id IN NUMBER,
113 p_internal_control_number IN NUMBER,
114 p_reference_id IN VARCHAR2,
115 p_tp_header_id IN NUMBER,
116 p_error_reject_string IN VARCHAR2) IS
117
118 l_party_id NUMBER;
119 l_party_site_id NUMBER;
120 l_party_type VARCHAR2(30);
121 l_org_id NUMBER;
122
123 l_errmsg VARCHAR2(2000);
124 l_error_code NUMBER;
125 l_error_msg VARCHAR2(1000);
126 l_raise_3c4_parameters wf_parameter_list_t;
127 l_doc_id VARCHAR2(30);
128 l_invoice_num VARCHAR2(100);
129 l_po_num VARCHAR2(100);
130 l_invoice_amt NUMBER;
131 l_invoice_date DATE;
132 BEGIN
133
134 IF (l_debug_level <= 2) THEN
135 cln_debug_pub.Add('ENTERING CLN_NTFYINVC_PKG.RAISE_3C4_EVENT', 2);
136 END IF;
137
138 IF (l_debug_level <= 1) THEN
139 cln_debug_pub.Add('p_invoice_id:' || p_invoice_id, 1);
140 cln_debug_pub.Add('p_internal_control_number:' || p_internal_control_number, 1);
141 cln_debug_pub.Add('p_reference_id:' || p_reference_id, 1);
142 cln_debug_pub.Add('p_error_reject_string:' || p_error_reject_string, 1);
143 cln_debug_pub.Add('p_tp_header_id:' || p_tp_header_id, 1);
144 END IF;
145
146
147 --this query return the corresponding Trading Partner values for an Internal Control Number.
148 -- If it raises any exception then its Unexpected error, caught in the caller Procedure
149 SELECT party_id, party_site_id, party_type
150 INTO l_party_id,l_party_site_id,l_party_type
151 FROM ecx_tp_headers
152 WHERE tp_header_id = p_tp_header_id;
153
154 IF (l_debug_level <= 1) THEN
155 cln_debug_pub.Add('Obtained party type:'|| l_party_type ,1);
156 cln_debug_pub.Add('Obtained party id:'|| l_party_id ,1);
157 cln_debug_pub.Add('Obtained party site id:'|| l_party_site_id ,1);
158 END IF;
159
160 -- this query returns the corresponding Organisation ID for the above obtained Trading Partner values
161 -- If it raises any exception then its Unexpected error, caught in the caller Procedure
162 SELECT org_id
163 INTO l_org_id
164 FROM po_vendor_sites_all
165 WHERE vendor_id = l_party_id and vendor_site_id = l_party_site_id;
166
167 IF (l_debug_level <= 1) THEN
168 cln_debug_pub.Add('Obtained Org ID:'|| l_org_id ,1);
169 END IF;
170
171
172 -- Assigns the next value of 'CLN.CLN_3C4_DOCUMENT_NUM_S' sequence
173 SELECT M4R_3C4_DOCUMENT_NUM_S.NEXTVAL
174 INTO l_doc_id
175 FROM dual;
176
177 l_doc_id := 'CLN3C4OT-' || l_doc_id;
178
179 IF (l_debug_level <= 1) THEN
180 cln_debug_pub.Add('l_doc_id:'|| l_doc_id ,1);
181 END IF;
182
183 get_rejected_invoice_details(p_invoice_id, l_invoice_num, l_po_num, l_invoice_amt, l_invoice_date);
184
185 IF (l_debug_level <= 1) THEN
186 cln_debug_pub.Add('l_invoice_num :'|| l_invoice_num ,1);
187 END IF;
188
189 -- raise event to start 3c4 invoice error processing
190 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'CLN', l_raise_3c4_parameters);
191 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'INV_REJECT_NOTIF_OUT', l_raise_3c4_parameters);
192 WF_EVENT.AddParameterToList('ECX_PARTY_TYPE', l_party_type, l_raise_3c4_parameters);
193 WF_EVENT.AddParameterToList('ECX_PARTY_ID', l_party_id, l_raise_3c4_parameters);
194 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', l_party_site_id, l_raise_3c4_parameters);
195 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', l_doc_id, l_raise_3c4_parameters);
196 WF_EVENT.AddParameterToList('ORG_ID', l_org_id, l_raise_3c4_parameters);
197 WF_EVENT.AddParameterToList('VALIDATION_REQUIRED_YN', 'N', l_raise_3c4_parameters);
198 WF_EVENT.AddParameterToList('CH_MESSAGE_BEFORE_GENERATE_XML', 'CLN_CH_COLLABORATION_CREATED', l_raise_3c4_parameters);
199 WF_EVENT.AddParameterToList('CH_MESSAGE_AFTER_XML_SENT', 'CLN_3C4_XML_SENT', l_raise_3c4_parameters);
200 WF_EVENT.AddParameterToList('ECX_DELIVERY_CHECK_REQUIRED', 'Y', l_raise_3c4_parameters);
201 WF_EVENT.AddParameterToList('CH_MESSAGE_NO_TP_SETUP', 'CLN_CH_TP_SETUP_NOTFOUND', l_raise_3c4_parameters);
202 WF_EVENT.AddParameterToList('ECX_PARAMETER1', P_invoice_id, l_raise_3c4_parameters);
203 WF_EVENT.AddParameterToList('ECX_PARAMETER2', p_reference_id, l_raise_3c4_parameters);
204 WF_EVENT.AddParameterToList('ECX_PARAMETER3', p_error_reject_string, l_raise_3c4_parameters);
205 WF_EVENT.AddParameterToList('ECX_PARAMETER4', l_org_id, l_raise_3c4_parameters);
206 WF_EVENT.AddParameterToList('ECX_PARAMETER5', l_invoice_amt, l_raise_3c4_parameters);
207 WF_EVENT.AddParameterToList('DOCUMENT_NO', l_invoice_num, l_raise_3c4_parameters);
208
209 --WF_EVENT.AddParameterToList('CLN_DOCUMENT_NUMBER', l_invoice_num, l_raise_3c4_parameters);
210 --REFERENCE_ID?
211 --@@@@ - Add these params
212 --WF_EVENT.AddParameterToList('INVOICE_DATE', l_invoice_id, l_raise_3c4_parameters);
213 --WF_EVENT.AddParameterToList('ECX_PARAMETER4', l_po_num, l_raise_3c4_parameters);
214 --WF_EVENT.Raise('oracle.apps.cln.error.ntfyinvi',l_event_key, NULL, l_raise_3c4_parameters, NULL);
215 WF_EVENT.Raise('oracle.apps.cln.common.xml.out',l_doc_id, NULL, l_raise_3c4_parameters, NULL);
216
217 IF (l_Debug_Level <= 1) THEN
218 cln_debug_pub.Add('---------3c4 event raised ---------', 1);
219 END IF;
220
221 IF (l_debug_level <= 1) THEN
222 cln_debug_pub.Add('Exiting CLN_NTFYINVC_PKG.RAISE_3C4_EVENT', 1);
223 END IF;
224
225 END RAISE_3C4_EVENT;
226
227
228
229 -- Start of comments
230 -- API name : Get_NotifyInvoice_Params
231 -- Type : Private
232 -- Pre-reqs : None.
233 -- Function : Gets the necessary parameters for the outbound Notify Invoice transaction.
234 -- Version : Current version 1.0
235 -- Initial version 1.0
236 -- Notes : This procedure is called from workflow(3C3 Outbound).
237 -- End of comments
238
239 PROCEDURE Get_NotifyInvoice_Params(p_itemtype IN VARCHAR2,
240 p_itemkey IN VARCHAR2,
241 p_actid IN NUMBER,
242 p_funcmode IN VARCHAR2,
243 x_resultout IN OUT NOCOPY VARCHAR2) IS
244
245 l_transaction_type VARCHAR2(240);
246 l_transaction_subtype VARCHAR2(240);
247 l_document_direction VARCHAR2(240);
248 l_party_id NUMBER;
249 l_party_site_id NUMBER;
250 l_party_type VARCHAR2(30);
251 l_return_code PLS_INTEGER;
252 l_errmsg VARCHAR2(2000);
253 l_result BOOLEAN;
254 l_error_code NUMBER;
255 l_error_msg VARCHAR2(1000);
256 l_customer_trx_id NUMBER;
257 l_inv_date DATE;
258 l_canonical_date VARCHAR2(100);
259 l_doc_transfer_id NUMBER;
260 l_document_id VARCHAR2(100);
261 l_ntfyinvc_seq NUMBER;
262 l_organization_id NUMBER;
263 l_trx_number VARCHAR2(100);
264
265 BEGIN
266
267 IF (l_debug_level <= 1) THEN
268 cln_debug_pub.Add('ENTERING CLN_NTFYINVC_PKG.Get_NotifyInvoice_Params', 1);
269 cln_debug_pub.Add('With the following parameters:', 1);
270 cln_debug_pub.Add('itemtype:' || p_itemtype, 1);
271 cln_debug_pub.Add('itemkey:' || p_itemkey, 1);
272 cln_debug_pub.Add('actid:' || p_actid, 1);
273 cln_debug_pub.Add('funcmode:' || p_funcmode, 1);
274 cln_debug_pub.Add('resultout:' || x_resultout, 1);
275 END IF;
276
277 l_transaction_type := 'CLN';
278 l_transaction_subtype := 'NTFYINVCO';
279 l_document_direction := 'OUT';
280 l_party_type := 'C';
281 l_result := FALSE;
282
283 -- Do nothing in cancel or timeout mode
284 IF (p_funcmode <> wf_engine.eng_run) THEN
285 x_resultout := wf_engine.eng_null;
286 return; -- do not raise the exception as it would end the workflow
287 END IF;
288
289 -- Retrieve Activity Attributes
290 l_party_site_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_PARTY_SITE_ID');
291 l_doc_transfer_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_DOCUMENT_ID');
292
293
294 -- Get party id, party site id, org_id
295 BEGIN
296
297 SELECT party_id
298 INTO l_party_id
299 FROM hz_party_sites
300 WHERE party_site_id = l_party_site_id;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304
305 IF (l_debug_level <= 1) THEN
306 cln_debug_pub.Add('Exception - Query for Party ID failed', 1);
307 END IF;
308 END;
309
310 IF (l_debug_level <= 1) THEN
311 cln_debug_pub.Add('CLN_NTFYINVC_PKG.Get_NotifyInvoice_Params: Parameter Lookups Completed', 1);
312 cln_debug_pub.Add('With the following parameters:', 1);
313 cln_debug_pub.Add('party_id:' || l_party_id, 1);
314 cln_debug_pub.Add('party_site_id:' || l_party_site_id, 1);
315 cln_debug_pub.Add('doc_transfer_id:' || l_doc_transfer_id, 1);
316 END IF;
317
318 IF (l_debug_level <= 1) THEN
319 cln_debug_pub.Add('XML Trading Partner Setup Check Succeeded', 1);
320 END IF;
321
322 BEGIN
323
324 SELECT customer_trx_id, org_id, trx_number,trx_date
325 INTO l_customer_trx_id, l_organization_id, l_trx_number,l_inv_date
326 FROM CLN_3C3_INVOICE_V
327 WHERE document_transfer_id = l_doc_transfer_id AND ROWNUM < 2;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331
332 IF (l_debug_level <= 1) THEN
333 cln_debug_pub.Add('Exception - Querying the CLN_3C3_INVOICE_V failed', 1);
334 END IF;
335 END;
336
337 -- generate Document Creation Date
338 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_inv_date);
339
340 IF (l_debug_level <= 1) THEN
341 cln_debug_pub.Add('l_canonical_date'||l_canonical_date, 1);
342 cln_debug_pub.Add('customer_trx_id'||l_customer_trx_id, 1);
343 cln_debug_pub.Add('org_id'||l_organization_id, 1);
344 cln_debug_pub.Add('trx_number'||l_trx_number, 1);
345 cln_debug_pub.Add('trx_date'||l_inv_date, 1);
346 END IF;
347
348 IF (l_debug_level <= 1) THEN
349 cln_debug_pub.Add('Workflow Item Attributes are set as below:',1);
350 cln_debug_pub.Add('Document Number'||l_trx_number, 1);
351 cln_debug_pub.Add('ORG_ID'|| l_organization_id, 1);
352 cln_debug_pub.Add('ECX_TRANSACTION_TYPE'|| l_transaction_type, 1);
353 cln_debug_pub.Add('ECX_TRANSACTION_SUBTYPE'|| l_transaction_subtype, 1);
354 cln_debug_pub.Add('ECX_PARTY_ID'|| l_party_id, 1);
355 cln_debug_pub.Add('ECX_PARTY_SITE_ID'|| l_party_site_id, 1);
356 cln_debug_pub.Add('ECX_PARTY_TYPE'|| l_party_type, 1);
357 cln_debug_pub.Add('DOCUMENT_CREATION_DATE'|| l_canonical_date, 1);
358 cln_debug_pub.Add('PROPRIETARY_DOCUMENT_ID'|| l_trx_number, 1);
359 cln_debug_pub.Add('ECX_DOCUMENT_ID'|| l_doc_transfer_id, 1);
360 cln_debug_pub.Add('EVENT_KEY'|| p_itemkey, 1);
361
362 END IF;
363
364 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_NO', l_trx_number);
365 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ORG_ID', l_organization_id);
366 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_TYPE', l_transaction_type);
367 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_SUBTYPE', l_transaction_subtype);
368 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_ID', l_party_id);
369 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_SITE_ID', l_party_site_id);
370 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_TYPE', l_party_type);
371 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_DOCUMENT_ID', l_doc_transfer_id);
372 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_CREATION_DATE', l_canonical_date);
373 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PROPRIETARY_DOCUMENT_ID', l_trx_number);
374 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'REFERENCE_ID', p_itemkey);
375 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', p_itemkey);
376
377 -- Reached Here. Successful execution.
378 x_resultout := 'SUCCESS';
379 IF (l_debug_level <= 1) THEN
380 cln_debug_pub.Add('Result out '|| x_resultout, 1);
381 END IF;
382
383 IF (l_debug_level <= 2) THEN
384 cln_debug_pub.Add('EXITING CLN_NTFYINVC_PKG.Get_NotifyInvoice_Params Successfully', 2);
385 END IF;
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 l_error_code := SQLCODE;
390 l_error_msg := SQLERRM;
391
392 IF (l_debug_level <= 1) THEN
393 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
394 END IF;
395
396 x_resultout := 'ERROR';
397 IF (l_debug_level <= 1) THEN
398 cln_debug_pub.Add('Result out '|| x_resultout, 1);
399 END IF;
400
401 IF (l_debug_level <= 1) THEN
402 cln_debug_pub.Add('Exiting CLN_NTFYINVC_PKG.Get_NotifyInvoice_Params with Error', 1);
403 END IF;
404
405 END Get_NotifyInvoice_Params;
406
407
408 -- Start of comments
409 -- API name : GET_PAYMENT_TERM_CODE
410 -- Type : Private
411 -- Pre-reqs : None.
412 -- Function : Gets the Payment Term Code.
413 -- Version : Current version 1.0
414 -- Previous version 1.0
415 -- Initial version 1.0
416 -- Notes :
417 -- End of comments
418
419
420 PROCEDURE GET_PAYMENT_TERM_CODE(p_customer_trx_id IN NUMBER,
421 x_pay_t_code OUT NOCOPY VARCHAR2 ) IS
422
423 l_error_code NUMBER;
424 l_error_msg VARCHAR2(2000);
425
426 BEGIN
427
428 IF (l_debug_level <= 2) THEN
429 cln_debug_pub.Add('Entering GET_PAYMENT_TERM_CODE',2);
430 END IF;
431
432 IF (l_debug_level <= 1) THEN
433 cln_debug_pub.Add('p_customer_trx_id : '|| p_customer_trx_id, 1);
434 END IF;
435
436
437 -- Get the Payment Terms Code
438 BEGIN
439
440 SELECT name --This is equivalent to TERM_NAME column of AR_XML_PAYMENT_TERMS_V
441 INTO x_pay_t_code
442 FROM ra_terms t, ar_payment_schedules_all ps
443 WHERE t.term_id = ps.term_id
444 AND ps.customer_trx_id = p_customer_trx_id
445 AND rownum < 2;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449
450 IF (l_debug_level <= 1) THEN
451 cln_debug_pub.Add('Inside when others while fetching payment term', 1);
452 END IF;
453 x_pay_t_code := null;
454
455 END;
456
457 IF (l_debug_level <= 1) THEN
458 cln_debug_pub.Add('Payment term code : '|| x_pay_t_code, 1);
459 END IF;
460
461 IF (l_debug_level <= 2) THEN
462 cln_debug_pub.Add('Exiting GET_PAYMENT_TERM_CODE',2);
463 END IF;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 l_error_code := SQLCODE;
468 l_error_msg := SQLERRM;
469
470 IF (l_Debug_Level <= 6) THEN
471 cln_debug_pub.Add('Exception in GET_PAYMENT_TERM_CODE with Error code' || l_error_code ||
472 'and Errror Message' || l_error_msg,6);
473 END IF;
474
475 IF (l_Debug_Level <= 2) THEN
476 cln_debug_pub.Add('----------- ERROR:EXITING GET_PAYMENT_TERM_CODE ------------', 2);
477 END IF;
478
479 END GET_PAYMENT_TERM_CODE;
480
481
482 -- Start of comments
483 -- API name : GET_TAX_AMOUNT_AND_CODE
484 -- Type : Private
485 -- Pre-reqs : None.
486 -- Function : Gets the Tax Amount and the Tax Code.
487 -- Version : Current version 1.0
488 -- Previous version 1.0
489 -- Initial version 1.0
490 -- Notes :
491 -- End of comments
492
493
494 PROCEDURE GET_TAX_AMOUNT_AND_CODE (p_customer_trx_line_id IN NUMBER,
495 x_tax_amount OUT NOCOPY NUMBER,
496 x_tax_code OUT NOCOPY VARCHAR2) IS
497 l_cust_trx_id NUMBER;
498 l_cust_trx_line_id NUMBER;
499 l_error_code NUMBER;
500 l_error_msg VARCHAR2(2000);
501
502 BEGIN
503
504 IF (l_debug_level <= 2) THEN
505 cln_debug_pub.Add('Entering GET_TAX_AMOUNT_AND_CODE',2);
506 END IF;
507
508 IF (l_debug_level <= 1) THEN
509 cln_debug_pub.Add('p_customer_trx_line_id : '|| p_customer_trx_line_id, 1);
510 END IF;
511
512 -- Sum all the Tax amounts pertaining to the Trx Line Id
513 BEGIN
514
515 SELECT sum(tax_amount)
516 INTO x_tax_amount
517 FROM AR_XML_INVOICE_TAX_V
518 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 l_error_code := SQLCODE;
523 l_error_msg := SQLERRM;
524
525 IF (l_Debug_Level <= 6) THEN
526 cln_debug_pub.Add('Exception in calculating the Tax amount : ' || l_error_code ||
527 'and Errror Message' || l_error_msg,6);
528 END IF;
529
530 END;
531
532 IF (l_debug_level <= 1) THEN
533 cln_debug_pub.Add('Tax Amount : '|| x_tax_amount, 1);
534 END IF;
535
536 IF x_tax_amount > 0 THEN
537 x_tax_code := 'DEBIT';
538 ELSE IF x_tax_amount <0 THEN
539 x_tax_code := 'CREDIT';
540 ELSE
541 x_tax_code := 'ZERO REMIT';
542 END IF;
543 END IF;
544
545 IF (l_debug_level <= 1) THEN
546 cln_debug_pub.Add('Tax Code : '|| x_tax_code, 1);
547 END IF;
548
549 IF (l_debug_level <= 2) THEN
550 cln_debug_pub.Add('Exiting GET_TAX_AMOUNT_AND_CODE',2);
551 END IF;
552
553 EXCEPTION
554 WHEN OTHERS THEN
555 l_error_code := SQLCODE;
556 l_error_msg := SQLERRM;
557
558 IF (l_Debug_Level <= 6) THEN
559 cln_debug_pub.Add('Exception in GET_TAX_AMOUNT_AND_CODE with Error code' || l_error_code ||
560 'and Errror Message' || l_error_msg,6);
561 END IF;
562
563 IF (l_Debug_Level <= 2) THEN
564 cln_debug_pub.Add('----------- ERROR:EXITING GET_TAX_AMOUNT_AND_CODE ------------', 2);
565 END IF;
566
567 END GET_TAX_AMOUNT_AND_CODE;
568
569
570 -- Start of comments
571 -- API name : GET_DOC_GENERATION_DATETIME
572 -- Type : Private
573 -- Pre-reqs : None.
574 -- Function : Gets the Document Generation Date and Time.
575 -- Version : Current version 1.0
576 -- Previous version 1.0
577 -- Initial version 1.0
578 -- Notes :
579 -- End of comments
580
581
582 PROCEDURE GET_DOC_GENERATION_DATETIME(p_doc_trnsfr_id IN NUMBER,
583 x_doc_gen_dt OUT NOCOPY VARCHAR2 ) IS
584
585 l_trx_date DATE;
586 l_error_code NUMBER;
587 l_error_msg VARCHAR2(2000);
588
589 BEGIN
590
591 IF (l_debug_level <= 2) THEN
592 cln_debug_pub.Add('Entering GET_DOC_GENERATION_DATETIME',2);
593 END IF;
594
595 IF (l_debug_level <= 1) THEN
596 cln_debug_pub.Add('Document Transfer ID : '|| p_doc_trnsfr_id, 1);
597 END IF;
598
599 BEGIN
600
601 SELECT trx_date
602 INTO l_trx_date
603 FROM CLN_3C3_INVOICE_V
604 WHERE document_transfer_id = p_doc_trnsfr_id and rownum < 2;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_error_code := SQLCODE;
609 l_error_msg := SQLERRM;
610
611 IF (l_Debug_Level <= 6) THEN
612 cln_debug_pub.Add('Exception while querying for the trx_date : ' || l_error_code ||
613 'and Errror Message' || l_error_msg,6);
614 END IF;
615
616 END;
617
618 IF (l_debug_level <= 1) THEN
619 cln_debug_pub.Add('Transaction Date before conversion : '|| l_trx_date, 1);
620 END IF;
621
622 IF (l_debug_level <= 2) THEN
623 cln_debug_pub.Add('Calling ------- cln_rn_utils.convert_to_rn_datetime ----', 2);
624 END IF;
625
626 cln_rn_utils.convert_to_rn_datetime(l_trx_date,x_doc_gen_dt);
627
628 IF (l_debug_level <= 1) THEN
629 cln_debug_pub.Add('Out of ------- cln_rn_utils.convert_to_rn_datetime ---- ', 1);
630 cln_debug_pub.Add('Transaction Date after conversion : '|| x_doc_gen_dt, 1);
631 END IF;
632
633 IF (l_debug_level <= 2) THEN
634 cln_debug_pub.Add('Exiting GET_DOC_GENERATION_DATETIME',2);
635 END IF;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 l_error_code := SQLCODE;
640 l_error_msg := SQLERRM;
641
642 IF (l_Debug_Level <= 6) THEN
643 cln_debug_pub.Add('Exception in GET_DOC_GENERATION_DATETIME with Error code' || l_error_code ||
644 'and Errror Message' || l_error_msg,6);
645 END IF;
646
647 IF (l_Debug_Level <= 2) THEN
648 cln_debug_pub.Add('----------- ERROR:EXITING GET_DOC_GENERATION_DATETIME ------------', 2);
649 END IF;
650
651 END GET_DOC_GENERATION_DATETIME;
652
653
654 -- Start of comments
655 -- API name : GET_PO_SHIPMENT_INFO
656 -- Type : Private
657 -- Pre-reqs : None.
658 -- Function : Gets the PO, Line and Shipment info. of a PO
659 -- Version : Current version 1.0
660 -- Previous version 1.0
661 -- Initial version 1.0
662 -- Notes : We may need to modify this to support get PO details
663 -- : for SO auto created from delivery
664 -- End of comments
665 PROCEDURE GET_PO_SHIPMENT_INFO(
666 p_org_id IN VARCHAR2,
667 p_so_num IN VARCHAR2,
668 p_so_rev_num IN VARCHAR2,
669 p_so_lin_num IN VARCHAR2,
670 x_po_num IN OUT NOCOPY VARCHAR2,
671 x_po_line_num IN OUT NOCOPY VARCHAR2,
672 x_po_ship_num IN OUT NOCOPY VARCHAR2) AS
673 BEGIN
674 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
675 IF (l_debug_level <= 2) THEN
676 cln_debug_pub.Add('Entering CLN_NTFYINVC_PKG.GET_PO_SHIPMENT_INFO',2);
677 cln_debug_pub.Add('p_org_id - '||p_org_id ,2);
678 cln_debug_pub.Add('p_so_num - '||p_so_num ,2);
679 cln_debug_pub.Add('p_so_rev_num - '||p_so_rev_num,2);
680 cln_debug_pub.Add('p_so_lin_num - '||p_so_lin_num,2);
681 END IF;
682
683 BEGIN
684
685 SELECT ol.orig_sys_document_ref,ol.orig_sys_line_ref, ol.orig_sys_shipment_ref
686 INTO x_po_num, x_po_line_num,x_po_ship_num
687 FROM oe_order_headers oh, oe_order_lines ol
688 WHERE oh.org_id = ol.org_id
689 AND oh.header_id = ol.header_id
690 AND oh.org_id = trim(p_org_id)
691 AND oh.order_number = to_number(trim(p_so_num))
692 AND oh.version_number = nvl(to_number(trim(p_so_rev_num)),0)
693 AND ol.line_number = to_number(trim(p_so_lin_num))
694 AND rownum < 2;
695
696 IF (l_debug_level <= 1) THEN
697 cln_debug_pub.add('PO num - ' || x_po_num,1);
698 cln_debug_pub.add('PO line - ' || x_po_line_num,1);
699 cln_debug_pub.add('PO ship - ' || x_po_ship_num,1);
700 END IF;
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 IF (l_debug_level <= 5) THEN
705 cln_debug_pub.Add('Error in CLN_NTFYINVC_PKG.GET_PO_SHIPMENT_INFO',5);
706 cln_debug_pub.Add('Error - ' || SQLCODE || SQLERRM,5);
707 END IF;
708 x_po_num := null;
709 x_po_line_num := null;
710 x_po_ship_num := null;
711 END;
712
713 END GET_PO_SHIPMENT_INFO;
714
715
716
717 -- Start of comments
718 -- API name : CLN_UPDATE_DOCUMENT_STATUS
719 -- Type : Private
720 -- Pre-reqs : None.
721 -- Function : Updates the status for the transaction in the 'ar_document_transfers' table.
722 -- Version : Current version 1.0
723 -- Previous version 1.0
724 -- Initial version 1.0
725 -- Notes :
726 -- End of comments
727
728 PROCEDURE CLN_UPDATE_DOC_STATUS(p_itemtype IN VARCHAR2,
729 p_itemkey IN VARCHAR2,
730 p_actid IN NUMBER,
731 p_funcmode IN VARCHAR2,
732 x_resultout IN OUT NOCOPY VARCHAR2) AS
733 l_status VARCHAR2(10);
734 l_doc_id VARCHAR2(1000);
735 l_error_code NUMBER;
736 l_error_msg VARCHAR2(2000);
737 l_transaction_type VARCHAR2(100);
738 l_ext_trx_type VARCHAR2(100);
739 l_ext_trx_subtype VARCHAR2(100);
740 l_transaction_subtype VARCHAR2(100);
741
742 BEGIN
743
744 -- set debug level
745 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
746
747 IF (l_debug_level <= 2) THEN
748 cln_debug_pub.Add('Entering CLN_UPDATE_DOC_STATUS',2);
749 END IF;
750
751 l_doc_id := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_DOCUMENT_ID');
752 IF (l_debug_level <= 1) THEN
753 cln_debug_pub.Add('Document Transfer ID : '|| l_doc_id, 1);
754 END IF;
755
756 l_transaction_type := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_TRANSACTION_TYPE');
757 IF (l_debug_level <= 1) THEN
758 cln_debug_pub.Add('l_transaction_type: '|| l_transaction_type, 1);
759 END IF;
760
761 l_transaction_subtype := Wf_Engine.GetActivityAttrText(p_itemtype, p_itemkey, p_actid, 'ECX_TRANSACTION_SUBTYPE');
762 IF (l_debug_level <= 1) THEN
763 cln_debug_pub.Add('l_transaction_subtype : '|| l_transaction_subtype, 1);
764 END IF;
765
766 BEGIN
767
768 SELECT EXT_TYPE, EXT_SUBTYPE
769 INTO l_ext_trx_type,l_ext_trx_subtype
770 FROM ecx_ext_processes
771 WHERE transaction_id = (SELECT transaction_id
772 FROM ecx_transactions
773 WHERE transaction_type = l_transaction_type
774 AND transaction_subtype = l_transaction_subtype);
775
776 EXCEPTION
777 WHEN NO_DATA_FOUND THEN
778 cln_debug_pub.Add('No Data found for External Transaction Type and External Transaction Type', 1);
779 END;
780
781 IF (l_debug_level <= 1) THEN
782 cln_debug_pub.Add('l_ext_trx_type: '|| l_ext_trx_type, 1);
783 cln_debug_pub.Add('l_ext_trx_subtype : '|| l_ext_trx_subtype, 1);
784 END IF;
785
786
787 BEGIN
788 SELECT 'x'
789 INTO l_status
790 FROM DUAL
791 WHERE EXISTS (
792 SELECT 'x'
793 FROM ecx_doclogs
794 WHERE document_number = l_doc_id
795 AND item_type = p_itemtype -- Changed to fix Bug #5031346
796 AND item_key = p_itemkey -- Changed to fix Bug #5031346
797 AND direction = 'OUT');
798 EXCEPTION
799
800 WHEN NO_DATA_FOUND THEN
801 cln_debug_pub.Add('No Data found in ecx_doclogs', 1);
802 END;
803
804 IF (l_debug_level <= 1) THEN
805 cln_debug_pub.Add('l_status: '|| l_status , 1);
806 END IF;
807
808 UPDATE ar_document_transfers
809 SET status = decode(l_status,'x','TRANSMITTED','FAILED')
810 WHERE document_transfer_id = l_doc_id;
811
812 IF (l_debug_level <= 1) THEN
813 cln_debug_pub.Add('ar_document_transfers row updated', 1);
814 END IF;
815
816 x_resultout := 'YES';
817
818 IF (l_debug_level <= 2) THEN
819 cln_debug_pub.Add('Exiting CLN_UPDATE_DOC_STATUS',2);
820 END IF;
821 EXCEPTION
822 WHEN OTHERS THEN
823 l_error_code := SQLCODE;
824 l_error_msg := SQLERRM;
825
826 IF (l_Debug_Level <= 6) THEN
827 cln_debug_pub.Add('Exception in CLN_UPDATE_DOC_STATUS with Error code' || l_error_code ||
828 'and Errror Message' || l_error_msg,6);
829 END IF;
830
831 x_resultout := 'ERRROR';
832 IF (l_Debug_Level <= 2) THEN
833 cln_debug_pub.Add('----------- ERROR:EXITING CLN_UPDATE_DOC_STATUS ------------', 2);
834 END IF;
835
836 END CLN_UPDATE_DOC_STATUS;
837
838
839 -- Start of comments
840 -- API name : RAISE_UPDATE
841 -- Type : Private
842 -- Pre-reqs : None.
843 -- Function : This is the public procedure which raises an event to update collaboration passing the
844 -- parameters so obtained.
845 -- Version : Current version 1.0
846 -- Initial version 1.0
847 -- Notes : This procedure is called from the root of XGM map.
848 -- End of comments
849
850
851 PROCEDURE RAISE_UPDATE (p_document_id IN VARCHAR2,
852 p_int_cnt_num IN NUMBER,
853 p_org_id IN NUMBER,
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_data OUT NOCOPY VARCHAR2) IS
856
857 l_msg_data VARCHAR2(100);
858 l_error_code NUMBER;
859 l_error_msg VARCHAR2(2000);
860
861
862 -- parameters for document creation date
863 l_date DATE;
864 l_canonical_date VARCHAR2(100);
865
866 -- parameters for raising event
867 l_update_cln_parameter_list wf_parameter_list_t;
868
869 BEGIN
870
871 l_msg_data := '000';
872
873 IF (l_Debug_Level <= 1) THEN
874 cln_debug_pub.Add('Entering RAISE_UPDATE_EVENT procedure with parameters----', 1);
875 cln_debug_pub.Add('Internal Control Number :'||p_int_cnt_num, 1);
876 cln_debug_pub.Add('Invoice Number : '||p_document_id, 1);
877 cln_debug_pub.Add('Organization ID : '||p_org_id, 1);
878 END IF;
879
880 -- Standard Start of API savepoint
881 SAVEPOINT CHECK_COLLABORATION_PUB;
882
883 SELECT sysdate
884 INTO l_date
885 FROM dual;
886
887 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
888
889 IF (l_Debug_Level <= 1) THEN
890 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update called with the following parameters',1);
891 cln_debug_pub.Add('DOCUMENT_NO(Invoice Number): '|| p_document_id,1);
892 cln_debug_pub.Add('DOCUMENT_CREATION_DATE : ' || l_canonical_date,1);
893 END IF;
894
895 l_update_cln_parameter_list := wf_parameter_list_t();
896
897 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'SUCCESS', l_update_cln_parameter_list);
898 WF_EVENT.AddParameterToList('MESSAGE_TEXT', 'CLN_3C3_INVOICE_RCVD', l_update_cln_parameter_list);
899 WF_EVENT.AddParameterToList('DOCUMENT_NO',p_document_id,l_update_cln_parameter_list);
900 WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE',l_canonical_date,l_update_cln_parameter_list);--sysdate
901 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_cnt_num,l_update_cln_parameter_list);
902 WF_EVENT.AddParameterToList('ORG_ID',p_org_id,l_update_cln_parameter_list);
903
904 IF (l_Debug_Level <= 1) THEN
905 cln_debug_pub.Add('-------- EVENT PARAMETERS SET-----------', 1);
906 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
907 END IF;
908
909 wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
910 p_event_key => p_document_id ||'.'|| p_int_cnt_num,
911 p_parameters => l_update_cln_parameter_list);
912
913
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915 x_msg_data:= 'SUCCESS';
916
917 IF (l_Debug_Level <= 2) THEN
918 cln_debug_pub.Add('x_return_status: '|| x_return_status, 2);
919 cln_debug_pub.Add('x_msg_data: '|| x_msg_data, 2);
920 cln_debug_pub.Add('----------- EXITING RAISE_UPDATE_EVENT ------------', 2);
921 END IF;
922
923 EXCEPTION
924 WHEN OTHERS THEN
925 l_error_code := SQLCODE;
926 l_error_msg := SQLERRM;
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
928 l_msg_data := l_error_code||' : '||l_error_msg;
929 x_msg_data := l_msg_data;
930
931 IF (l_Debug_Level <= 6) THEN
932 cln_debug_pub.Add('Error: '|| l_msg_data,6);
933 cln_debug_pub.Add('x_return_status: '|| x_return_status, 2);
934 cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 6);
935 END IF;
936
937 END RAISE_UPDATE;
938
939 -- Start of comments
940 -- API name : ERROR_HANDLER
941 -- Type : Private
942 -- Pre-reqs : None.
943 -- Function :
944 -- Version : Current version 1.0
945 -- Previous version 1.0
946 -- Initial version 1.0
947 -- Notes :
948 -- End of comments
949
950
951 PROCEDURE ERROR_HANDLER(p_internal_control_number IN NUMBER,
952 p_document_id IN NUMBER,
953 p_org_id IN NUMBER,
954 x_notification_code OUT NOCOPY VARCHAR2,
955 x_notification_status OUT NOCOPY VARCHAR2,
956 x_return_status_tp OUT NOCOPY VARCHAR2,
957 x_return_desc_tp OUT NOCOPY VARCHAR2,
958 x_return_status IN OUT NOCOPY VARCHAR2,
959 x_msg_data IN OUT NOCOPY VARCHAR2) IS
960
961 l_cln_ch_parameters wf_parameter_list_t;
962
963 l_error_code NUMBER;
964 l_error_msg VARCHAR2(2000);
965 l_msg_data VARCHAR2(255);
966 l_event_key NUMBER;
967
968 -- parameters for document creation date
969 l_date DATE;
970 l_canonical_date VARCHAR2(100);
971 --l_ntfyinvc_seq NUMBER;
972
973 BEGIN
974
975 IF (l_Debug_Level <= 2) THEN
976 cln_debug_pub.Add('------ Entering ERROR_HANDLER API ------ ', 2);
977 END IF;
978
979 -- generate doc creation date
980 SELECT sysdate
981 INTO l_date
982 FROM dual;
983
984 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
985
986 -- here we do not initialize x_msg_data so as to account for the actual message coming from
987 -- previous API calls.
988 -- Parameters received
989
990 IF (l_Debug_Level <= 1) THEN
991 cln_debug_pub.Add('------------- Parameters Received ------------ ', 1);
992 cln_debug_pub.Add('Return Status - '||x_return_status,1);
993 cln_debug_pub.Add('Message Data - '||x_msg_data,1);
994 cln_debug_pub.Add('Internal Control Number - '||p_internal_control_number,1);
995 cln_debug_pub.Add('Document ID - '||p_document_id,1);
996 cln_debug_pub.Add('Organization ID - '||p_org_id,1);
997 cln_debug_pub.Add('------------------------------------------------- ', 1);
998 cln_debug_pub.Add('Rollback all previous changes....',1);
999 END IF;
1000
1001 ROLLBACK TO CHECK_COLLABORATION_PUB;
1002
1003
1004 IF (l_Debug_Level <= 1) THEN
1005 cln_debug_pub.Add('--------ERROR status -------------',1);
1006 END IF;
1007
1008 x_notification_code := '3C3_IN02';
1009 x_notification_status := 'ERROR';
1010 x_return_status_tp := '99';
1011 x_return_desc_tp := x_msg_data;
1012
1013 IF (l_Debug_Level <= 1) THEN
1014 cln_debug_pub.Add('Msg for collaboration detail - '||x_msg_data,1);
1015 cln_debug_pub.Add('-------------------------------------',1);
1016 cln_debug_pub.Add('------Calling RAISE_UPDATE_EVENT with ERROR status------',1);
1017 END IF;
1018
1019 l_cln_ch_parameters := wf_parameter_list_t();
1020 IF (l_Debug_Level <= 1) THEN
1021 cln_debug_pub.Add('---- SETTING EVENT PARAMETERS FOR UPDATE COLLABORATION ----', 1);
1022 END IF;
1023
1024 IF (l_Debug_Level <= 1) THEN
1025 cln_debug_pub.Add('DOCUMENT_CREATION_DATE:' || l_canonical_date, 1);
1026 cln_debug_pub.Add('DOCUMENT_NO:' || p_document_id, 1);
1027 cln_debug_pub.Add('XMLG_INTERNAL_CONTROL_NUMBER:' || p_internal_control_number, 1);
1028 cln_debug_pub.Add('ORG_ID:' || p_org_id, 1);
1029 END IF;
1030
1031 l_event_key := p_internal_control_number;
1032
1033 WF_EVENT.AddParameterToList('DOCUMENT_STATUS', 'ERROR', l_cln_ch_parameters);
1034 WF_EVENT.AddParameterToList('MESSAGE_TEXT', x_msg_data, l_cln_ch_parameters);
1035 WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_internal_control_number,l_cln_ch_parameters);
1036 WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE',l_canonical_date,l_cln_ch_parameters);
1037 WF_EVENT.AddParameterToList('DOCUMENT_NO',p_document_id,l_cln_ch_parameters);
1038 WF_EVENT.AddParameterToList('ORG_ID',p_org_id,l_cln_ch_parameters);
1039
1040 IF (l_Debug_Level <= 1) THEN
1041 cln_debug_pub.Add('------------------- EVENT PARAMETERS SET -------------------', 1);
1042 cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
1043 END IF;
1044
1045 WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
1046
1047 -- this is required for the proper processing in workflow.
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049
1050 IF (l_Debug_Level <= 1) THEN
1051 cln_debug_pub.Add('the return status is :'||x_return_status,1);
1052 END IF;
1053
1054 IF (l_Debug_Level <= 2) THEN
1055 cln_debug_pub.Add('------- Exiting ERROR_HANDLER API --------- ',2);
1056 END IF;
1057
1058 -- Exception Handling
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 l_error_code :=SQLCODE;
1062 l_error_msg :=SQLERRM;
1063
1064 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR ;
1065
1066 FND_MESSAGE.SET_NAME('CLN','CLN_CH_UNEXPECTED_ERROR');
1067 FND_MESSAGE.SET_TOKEN('ERRORCODE',l_error_code);
1068 FND_MESSAGE.SET_TOKEN('ERRORMSG',l_error_msg);
1069
1070 x_msg_data :=FND_MESSAGE.GET;
1071 IF (l_Debug_Level <= 6) THEN
1072 cln_debug_pub.Add('Unexpected Error in ERROR_HANDLER - '|| x_msg_data,6);
1073 END IF;
1074
1075 IF (l_Debug_Level <= 6) THEN
1076 cln_debug_pub.Add('------- ERROR:Exiting ERROR_HANDLER API --------- ',6);
1077 END IF;
1078
1079 END ERROR_HANDLER;
1080
1081
1082
1083 -- Start of comments
1084 -- API name : XGM_CHECK_STATUS
1085 -- Type : Private
1086 -- Pre-reqs : None.
1087 -- Function : This procedure returns 'True' in case the status inputted is 'S' and returns 'False'
1088 -- in case the status inputted is other than 'S'.
1089 -- Version : Current version 1.0
1090 -- Previous version 1.0
1091 -- Initial version 1.0
1092 -- Notes :
1093 -- End of comments
1094
1095 PROCEDURE XGM_CHECK_STATUS ( p_itemtype IN VARCHAR2,
1096 p_itemkey IN VARCHAR2,
1097 p_actid IN NUMBER,
1098 p_funcmode IN VARCHAR2,
1099 x_resultout OUT NOCOPY VARCHAR2 ) IS
1100
1101 l_sender_header_id NUMBER;
1102 l_party_id NUMBER;
1103 l_party_site_id NUMBER;
1104 l_internal_control_number NUMBER;
1105 l_return_status_tp VARCHAR2(10);
1106 l_notification_code VARCHAR2(10);
1107 l_party_type VARCHAR2(20);
1108 l_notification_status VARCHAR2(100);
1109 l_msg_data VARCHAR2(255);
1110 l_return_desc_tp VARCHAR2(1000);
1111 l_error_code NUMBER;
1112 l_error_msg VARCHAR2(2000);
1113
1114 BEGIN
1115
1116 IF (l_Debug_Level <= 2) THEN
1117 cln_debug_pub.Add('------ Entering XGM_CHECK_STATUS API ------ ', 2);
1118 END IF;
1119
1120 l_msg_data :='Status returned from XGM checked for further processing';
1121
1122 -- Do nothing in cancel or timeout mode
1123 IF (p_funcmode <> wf_engine.eng_run) THEN
1124 x_resultout := wf_engine.eng_null;
1125
1126 IF (l_Debug_Level <= 1) THEN
1127 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
1128 END IF;
1129
1130 RETURN;
1131 END IF;
1132
1133 -- Should be 00 for success
1134 l_return_status_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER7', TRUE);
1135 IF (l_Debug_Level <= 1) THEN
1136 cln_debug_pub.Add('Return Status as obtained from workflow : '||l_return_status_tp,1);
1137 END IF;
1138
1139 l_sender_header_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'PARAMETER9', TRUE));
1140 IF (l_Debug_Level <= 1) THEN
1141 cln_debug_pub.Add('Trading Partner Header ID : '||l_sender_header_id, 1);
1142 END IF;
1143
1144 IF (l_sender_header_id IS NOT NULL) THEN
1145
1146 BEGIN
1147
1148 SELECT PARTY_ID, PARTY_SITE_ID,PARTY_TYPE
1149 INTO l_party_id, l_party_site_id, l_party_type
1150 FROM ECX_TP_HEADERS
1151 WHERE TP_HEADER_ID = l_sender_header_id ;
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 l_error_code := SQLCODE;
1156 l_error_msg := SQLERRM;
1157
1158 IF (l_Debug_Level <= 6) THEN
1159 cln_debug_pub.Add('Exception while querying for Party values in XGM_CHECK_STATUS: ' || l_error_code ||
1160 'and Errror Message' || l_error_msg,6);
1161 END IF;
1162
1163 x_resultout := 'ERROR';
1164
1165 END;
1166
1167 IF (l_Debug_Level <= 1) THEN
1168 cln_debug_pub.Add('Party ID : '||l_party_id,1);
1169 cln_debug_pub.Add('Party Site ID : '||l_party_site_id,1);
1170 cln_debug_pub.Add('Party Type : '||l_party_type,1);
1171 END IF;
1172
1173 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_ID', l_party_id);
1174 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_SITE_ID', l_party_site_id);
1175 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARTY_TYPE', l_party_type);
1176 END IF;
1177
1178 IF (l_return_status_tp = '00') THEN
1179
1180 IF (l_Debug_Level <= 1) THEN
1181 cln_debug_pub.Add('Return Status is Success',1);
1182 END IF;
1183
1184 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '00');
1185 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', 'SUCCESS');
1186 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'SUCCESS');
1187 x_resultout := 'COMPLETE:'||'TRUE';
1188
1189 ELSIF(l_return_status_tp = '99') THEN
1190
1191 IF (l_Debug_Level <= 1) THEN
1192 cln_debug_pub.Add('Return Status is Error',1);
1193 END IF;
1194
1195 l_return_desc_tp := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER8', TRUE);
1196
1197 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', '99');
1198 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1199 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'ERROR');
1200 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', '3C3_IN02');
1201
1202 IF (l_Debug_Level <= 1) THEN
1203 cln_debug_pub.Add('Message for the trading partner : '||l_return_desc_tp, 1);
1204 END IF;
1205
1206 x_resultout := 'COMPLETE:'||'FALSE';
1207 END IF;
1208
1209 IF (l_Debug_Level <= 2) THEN
1210 cln_debug_pub.Add('------- Exiting XGM_CHECK_STATUS API --------- ',2);
1211 END IF;
1212
1213 -- Exception Handling
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216
1217 FND_MESSAGE.SET_NAME('CLN','CLN_CH_ACTIVITY_ERROR');
1218 FND_MESSAGE.SET_TOKEN('ITMTYPE',p_itemtype);
1219 FND_MESSAGE.SET_TOKEN('ITMKEY',p_itemkey);
1220 FND_MESSAGE.SET_TOKEN('ACTIVITY','CHECK_STATUS');
1221
1222 -- we are not stopping the process becoz of this error,
1223 -- negative confirm bod is sent out with error occured here
1224
1225 l_return_status_tp := '99';
1226 l_return_desc_tp := FND_MESSAGE.GET;
1227
1228 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1229 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1230 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS', 'ERROR');
1231 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', '3C3_IN02');
1232
1233 x_resultout := 'ERROR';
1234 CLN_NP_PROCESSOR_PKG.NOTIFY_ADMINISTRATOR(l_return_desc_tp);
1235
1236 IF (l_Debug_Level <= 6) THEN
1237 cln_debug_pub.Add('------- ERROR:Exiting XGM_CHECK_STATUS API --------- ',6);
1238 END IF;
1239
1240 END XGM_CHECK_STATUS;
1241
1242
1243 -- Start of comments
1244 -- API name : INVOICE_IMPORT_STATUS_HANDLER
1245 -- Type : Private
1246 -- Pre-reqs : None.
1247 -- Function : This API checks for the status and accordingly updates the collaboration. Also, on the basis
1248 -- of Input parameters, notifications are sent out to Buyer for his necessary actions.
1249 -- Version : Current version 1.0
1250 -- Previous version 1.0
1251 -- Initial version 1.0
1252 -- Notes :
1253 -- End of comments
1254
1255
1256 PROCEDURE INVOICE_IMPORT_STATUS_HANDLER (p_itemtype IN VARCHAR2,
1257 p_itemkey IN VARCHAR2,
1258 p_actid IN NUMBER,
1259 p_funcmode IN VARCHAR2,
1260 x_resultout OUT NOCOPY VARCHAR2 ) IS
1261
1262 l_error_code NUMBER;
1263 l_event_key NUMBER;
1264 l_request_id NUMBER;
1265 l_internal_control_number NUMBER;
1266 l_invoice_id NUMBER;
1267 l_reference_id VARCHAR2(100);
1268 l_parent_table VARCHAR2(30);
1269 l_parent_id NUMBER(15);
1270 l_reject_code VARCHAR2(30);
1271 l_status_code VARCHAR2(2);
1272 l_count_failed_rows VARCHAR2(2);
1273 l_notification_code VARCHAR2(10);
1274 l_return_status_tp VARCHAR2(10);
1275 l_process_each_row_for_errors VARCHAR2(20);
1276 l_doc_status VARCHAR2(25);
1277 l_phase_code VARCHAR2(25);
1278 l_concurrent_msg VARCHAR2(250);
1279 l_return_desc_tp VARCHAR2(1000);
1280 l_error_reject_string VARCHAR2(2000);
1281 l_update_coll_msg VARCHAR2(2000);
1282 l_msg_data VARCHAR2(2000);
1283 l_error_msg VARCHAR2(2000);
1284 l_tp_header_id NUMBER;
1285
1286 BEGIN
1287
1288
1289 IF (l_Debug_Level <= 2) THEN
1290 cln_debug_pub.Add('------ Entering INVOICE_IMPORT_STATUS_HANDLER API ------ ', 2);
1291 END IF;
1292
1293 l_msg_data :='Parameters defaulted to proper values based on the status obtained after running the Invoice Import concurrent program.';
1294 l_process_each_row_for_errors := 'FALSE';
1295 x_resultout := 'COMPLETE:'||'TRUE';
1296
1297 -- Do nothing in cancel or timeout mode
1298
1299 IF (p_funcmode <> wf_engine.eng_run) THEN
1300 x_resultout := wf_engine.eng_null;
1301
1302 IF (l_Debug_Level <= 1) THEN
1303 cln_debug_pub.Add('Not in Running Mode...........Return Here',1);
1304 END IF;
1305
1306 RETURN;
1307 END IF;
1308
1309 -- Getting the values from the workflow.
1310 l_internal_control_number := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'EVENT_KEY', TRUE));
1311
1312 IF (l_Debug_Level <= 1) THEN
1313 cln_debug_pub.Add('Internal Control Number : '||l_internal_control_number, 1);
1314 END IF;
1315
1316 l_request_id := TO_NUMBER(wf_engine.GetItemAttrText(p_itemtype, p_itemkey,'REQIDNAME', TRUE));
1317 IF (l_Debug_Level <= 1) THEN
1318 cln_debug_pub.Add('Concurrent Program Request ID : '||l_request_id, 1);
1319 END IF;
1320
1321 l_notification_code := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', TRUE);
1322 IF (l_Debug_Level <= 1) THEN
1323 cln_debug_pub.Add('Notification Code : '||l_notification_code, 1);
1324 END IF;
1325
1326 l_invoice_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER6', TRUE);
1327 IF (l_Debug_Level <= 1) THEN
1328 cln_debug_pub.Add('Invoice ID : '||l_invoice_id, 1);
1329 END IF;
1330
1331 l_reference_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER10', TRUE);
1332 IF (l_Debug_Level <= 1) THEN
1333 cln_debug_pub.Add('Reference ID : '||l_reference_id, 1);
1334 END IF;
1335
1336 l_tp_header_id := wf_engine.GetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER9', TRUE);
1337 IF (l_Debug_Level <= 1) THEN
1338 cln_debug_pub.Add('Trading Partner HeaderID : '||l_tp_header_id, 1);
1339 END IF;
1340
1341 l_doc_status := 'SUCCESS';
1342 l_return_status_tp := '00';
1343 l_return_desc_tp := '3C3 Consumed Succesfully';
1344 l_update_coll_msg := 'CLN_CH_XML_CONSUMED_SUCCESS';
1345 l_notification_code := '3C3_IN02';
1346
1347 BEGIN
1348 SELECT status_code,completion_text,phase_code
1349 INTO l_status_code, l_concurrent_msg,l_phase_code
1350 FROM fnd_concurrent_requests
1351 WHERE request_id = l_request_id;
1352
1353 IF (l_Debug_Level <= 1) THEN
1354 cln_debug_pub.Add('Status Code returned from concurrent Request : '||l_status_code, 1);
1355 cln_debug_pub.Add('Phase Code returned from concurrent Request : '||l_phase_code, 1);
1356 cln_debug_pub.Add('Message From concurrent Request : '||l_concurrent_msg, 1);
1357 END IF;
1358
1359 EXCEPTION
1360 WHEN NO_DATA_FOUND THEN
1361 cln_debug_pub.Add('ERROR : Could not find the details for the Concurrent Request'||l_request_id, 1);
1362 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_RQST');
1363 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1364 l_msg_data := FND_MESSAGE.GET;
1365 -- default the status code so as to account for it in the collaboration hstry
1366 l_status_code := 'E';
1367 l_concurrent_msg := l_msg_data;
1368 x_resultout := 'ERROR:Could not find the status for the Concurrent Request';
1369 RETURN;
1370 END;
1371
1372
1373 IF (l_status_code NOT IN ('I','C','R')) THEN
1374
1375 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_FAILED');
1376 FND_MESSAGE.SET_TOKEN('REQNAME','Invoice Import');
1377 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1378 l_update_coll_msg := FND_MESSAGE.GET;
1379 l_return_status_tp := '99';
1380 l_return_desc_tp := l_update_coll_msg;
1381 l_notification_code := '3C3_IN02';
1382 l_doc_status := 'ERROR';
1383 x_resultout := 'COMPLETE:'||'Concurrent Program Request failed';
1384
1385 ELSE
1386
1387 IF (l_Debug_Level <= 1) THEN
1388 cln_debug_pub.Add('Processing for Concurrent Program Completed Normal ', 1);
1389 END IF;
1390
1391 BEGIN
1392 SELECT REJECT_REASON_STRING
1393 INTO l_error_reject_string
1394 FROM CLN_AP_INVOICE_REJECTION_ARCH
1395 WHERE invoice_id = l_invoice_id;
1396 EXCEPTION
1397 WHEN NO_DATA_FOUND then
1398 --NO ERRORS stored in acrival table
1399 l_error_reject_string := null;
1400 END;
1401
1402 IF (l_Debug_Level <= 1) THEN
1403 cln_debug_pub.Add(' l_error_reject_string from CLN_AP_INVOICE_REJECTION_ARCH : ' || l_error_reject_string, 1);
1404 END IF;
1405
1406 IF l_error_reject_string is null THEN
1407 --If no errors found in CLN_AP_INVOICE_REJECTION_ARCH try to scan the interface table again
1408 l_error_reject_string := GET_AR_REJECTIONS_STRING(l_invoice_id);
1409 END IF;
1410
1411 IF (l_Debug_Level <= 1) THEN
1412 cln_debug_pub.Add('l_error_reject_string from interface rejections : ' || l_error_reject_string, 1);
1413 END IF;
1414
1415 IF (l_error_reject_string is not null) THEN
1416 IF (l_Debug_Level <= 1) THEN
1417 cln_debug_pub.Add('Some items failed import ', 1);
1418 END IF;
1419
1420 -- Setting values for few rows falied import.
1421 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_1');
1422 FND_MESSAGE.SET_TOKEN('REQNAME','Invoice Import');
1423 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1424 l_update_coll_msg := FND_MESSAGE.GET;
1425 l_return_desc_tp := l_update_coll_msg;
1426 l_notification_code := '3C3_IN02';
1427 l_doc_status := 'ERROR';
1428
1429 x_resultout := 'COMPLETE:'||'Concurrent Program Request Success, import failed for few items';
1430
1431 IF (l_Debug_Level <= 1) THEN
1432 cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
1433 --cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
1434 END IF;
1435
1436 IF (l_Debug_Level <= 2) THEN
1437 cln_debug_pub.Add('Calling -----RAISE_3C4_EVENT------API', 2);
1438 cln_debug_pub.Add('Invoice ID: '||l_invoice_id, 2);
1439 cln_debug_pub.Add('l_internal_control_number: '||l_internal_control_number, 2);
1440 cln_debug_pub.Add('l_reference_id: '||l_reference_id, 2);
1441 cln_debug_pub.Add('l_tp_header_id: '||l_tp_header_id, 2);
1442 cln_debug_pub.Add('l_error_reject_string: '||l_error_reject_string, 2);
1443 END IF;
1444
1445 RAISE_3C4_EVENT(l_invoice_id,l_internal_control_number,l_reference_id,l_tp_header_id,l_error_reject_string);
1446
1447 IF (l_Debug_Level <= 2) THEN
1448 cln_debug_pub.Add('Out of -----RAISE_3C4_EVENT------API', 2);
1449 END IF;
1450
1451 ELSE
1452
1453 IF (l_Debug_Level <= 1) THEN
1454 cln_debug_pub.Add('l_error_reject_string is NULL', 1);
1455 END IF;
1456
1457 -- Setting values for Normal Completion Of the Concurrent Program
1458
1459 FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_2');
1460 FND_MESSAGE.SET_TOKEN('REQNAME','Invoice Import');
1461 FND_MESSAGE.SET_TOKEN('REQID',l_request_id);
1462
1463 l_update_coll_msg := FND_MESSAGE.GET;
1464 l_return_desc_tp := l_update_coll_msg;
1465 l_process_each_row_for_errors := 'FALSE';
1466 x_resultout := 'COMPLETE:'||'Concurrent Program Request Success';
1467
1468 END IF;
1469
1470 END IF;
1471
1472 IF (l_Debug_Level <= 1) THEN
1473 cln_debug_pub.Add('l_return_status_tp: '||l_return_status_tp, 1);
1474 cln_debug_pub.Add('RETURN_MSG_TP: '||l_return_desc_tp, 1);
1475 cln_debug_pub.Add('l_doc_status: '||l_doc_status, 1);
1476 cln_debug_pub.Add('PARAMETER4: '||l_notification_code, 1);
1477 END IF;
1478
1479
1480 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_STATUS_TP', l_return_status_tp);
1481 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_return_desc_tp);
1482 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'DOCUMENT_STATUS',l_doc_status );
1483 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PARAMETER4', l_notification_code);
1484 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'COLL_UPDATE_MSG', l_return_desc_tp);
1485
1486 IF (l_Debug_Level <= 2) THEN
1487 cln_debug_pub.Add('------- Exiting INVOICE_IMPORT_STATUS_HANDLER API --------- ',2);
1488 END IF;
1489
1490 EXCEPTION
1491
1492 WHEN OTHERS THEN
1493 l_error_code := SQLCODE;
1494 l_error_msg := SQLERRM;
1495 l_msg_data := l_error_code||' : '||l_error_msg;
1496
1497 IF (l_Debug_Level <= 6) THEN
1498 cln_debug_pub.Add(l_msg_data,6);
1499 END IF;
1500
1501 x_resultout := 'ERROR' || l_msg_data;
1502
1503 IF (l_Debug_Level <= 6) THEN
1504 cln_debug_pub.Add('------- Exiting INVOICE_IMPORT_STATUS_HANDLER API with errors --------- ',6);
1505 END IF;
1506
1507 END INVOICE_IMPORT_STATUS_HANDLER;
1508
1509
1510 -- Start of comments
1511 -- API name : UPDATE_INV_HEADER_INTERFACE
1512 -- Type : Private
1513 -- Pre-reqs : None.
1514 -- Function : This API checks for the status and accordingly updates the collaboration. Also, on the basis
1515 -- of Input parameters, notifications are sent out to Buyer for his necessary actions.
1516 -- Version : Current version 1.0
1517 -- Previous version 1.0
1518 -- Initial version 1.0
1519 -- Notes :
1520 -- End of comments
1521
1522
1523 PROCEDURE UPDATE_INV_HEADER_INTERFACE( p_invoice_id IN NUMBER,
1524 p_proprietary_doc_Identifier IN VARCHAR2,
1525 p_inv_curr_code IN VARCHAR2,
1526 p_inv_amount IN NUMBER,
1527 p_inv_date IN VARCHAR2,
1528 p_inv_type_lookup_code IN VARCHAR2,
1529 x_invoice_num IN OUT NOCOPY VARCHAR2,
1530 x_return_status IN OUT NOCOPY VARCHAR2,
1531 x_msg_data IN OUT NOCOPY VARCHAR2 ) IS
1532
1533 l_msg_data VARCHAR2(100);
1534 l_error_code NUMBER;
1535 l_error_msg VARCHAR2(2000);
1536 l_position NUMBER;
1537 l_db_inv_date DATE;
1538 BEGIN
1539
1540 l_msg_data := '000';
1541
1542
1543 IF (l_Debug_Level <= 2) THEN
1544 cln_debug_pub.Add('---------------- ENTERING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE -----------------', 2);
1545 END IF;
1546
1547 x_return_status := FND_API.G_RET_STS_SUCCESS;
1548
1549 IF (l_debug_level <= 1) THEN
1550 cln_debug_pub.Add('With the following parameters:', 1);
1551 cln_debug_pub.Add('p_invoice_id:' || p_invoice_id, 1);
1552 cln_debug_pub.Add('p_proprietary_doc_Identifier:' || p_proprietary_doc_Identifier, 1);
1553 cln_debug_pub.Add('P_inv_curr_code:' || P_inv_curr_code, 1);
1554 cln_debug_pub.Add('P_inv_amount:' || P_inv_amount, 1);
1555 cln_debug_pub.Add('p_inv_date:' || p_inv_date, 1);
1556 cln_debug_pub.Add('p_inv_type_lookup_code:' || p_inv_type_lookup_code, 1);
1557 END IF;
1558
1559 x_invoice_num := p_proprietary_doc_identifier;
1560
1561 IF (l_debug_level <= 1) THEN
1562 cln_debug_pub.Add('x_invoice_num:' || x_invoice_num, 1);
1563 END IF;
1564
1565 CLN_RN_UTILS.convert_to_db_date(p_inv_date,l_db_inv_date);
1566
1567 IF (l_debug_level <= 1) THEN
1568 cln_debug_pub.Add('l_db_inv_date:' || l_db_inv_date, 1);
1569 END IF;
1570
1571 UPDATE ap_invoices_interface
1572 SET INVOICE_CURRENCY_CODE = p_inv_curr_code,
1573 INVOICE_AMOUNT = P_inv_amount,
1574 INVOICE_DATE = l_db_inv_date,
1575 INVOICE_NUM = p_proprietary_doc_identifier,
1576 vendor_email_address = '3C4',
1577 source = 'XML GATEWAY'
1578 --INVOICE_TYPE_LOOKUP_CODE = p_inv_type_lookup_code
1579 WHERE invoice_id = p_invoice_id;
1580
1581 x_msg_data :='SUCCESS';
1582
1583 IF (l_debug_level <= 1) THEN
1584 cln_debug_pub.Add('Update ap_invoices_interface, is successful' , 1);
1585 cln_debug_pub.Add('x_return_status: '|| x_return_status , 1);
1586 cln_debug_pub.Add('x_msg_data: '|| x_msg_data , 1);
1587
1588 END IF;
1589
1590 IF (l_Debug_Level <= 2) THEN
1591 cln_debug_pub.Add('---------------- EXITING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE -----------------', 2);
1592 END IF;
1593 EXCEPTION
1594 WHEN OTHERS THEN
1595 l_error_code := SQLCODE;
1596 l_error_msg := SQLERRM;
1597
1598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1599 l_msg_data := l_error_code||' : '||l_error_msg;
1600 x_msg_data := l_msg_data;
1601
1602 IF (l_Debug_Level <= 6) THEN
1603 cln_debug_pub.Add('x_return_status: '|| x_return_status , 1);
1604 cln_debug_pub.Add(l_msg_data,6);
1605 END IF;
1606
1607 IF (l_Debug_Level <= 2) THEN
1608 cln_debug_pub.Add('---------------- EXITING CLN_NTFYINVC_PKG.UPDATE_INV_HEADER_INTERFACE with ERROR-----------------', 2);
1609 END IF;
1610 END UPDATE_INV_HEADER_INTERFACE;
1611
1612
1613 -- Start of comments
1614 -- API name : NOTIFY_INVOICE_TO_SYSADMIN
1615 -- Type : Private
1616 -- Pre-reqs : None.
1617 -- Function : This procedure notifies 3C3 Inbound to Sysadmin.
1618 -- Version : Current version 1.0
1619 -- Initial version 1.0
1620 -- Notes : This procedure is called from the XML map(3C4 Inbound)
1621 -- End of comments
1622
1623 PROCEDURE NOTIFY_INVOICE_TO_SYSADMIN (p_itemtype IN VARCHAR2,
1624 p_itemkey IN VARCHAR2,
1625 p_actid IN NUMBER,
1626 p_funcmode IN VARCHAR2,
1627 x_resultout IN OUT NOCOPY VARCHAR2) AS
1628
1629 -- declare local variables
1630 l_notif_code VARCHAR2(100);
1631 l_notif_desc VARCHAR2(2000);
1632 l_status VARCHAR2(100);
1633 l_app_ref_id VARCHAR2(100);
1634 l_return_code VARCHAR2(10);
1635 l_return_desc VARCHAR2(2000);
1636 l_coll_pt VARCHAR2(100);
1637 l_intrl_cntrl_num VARCHAR2(100);
1638 l_errmsg VARCHAR2(2000);
1639 l_error_code VARCHAR2(100);
1640 l_tp_id VARCHAR2(255);
1641
1642 BEGIN
1643
1644
1645 IF (l_debug_level <= 2) THEN
1646 cln_debug_pub.Add('Entering the procedure NOTIFY_INVOICE_TO_SYSADMIN', 2);
1647 END IF;
1648
1649 -- get the workflow activity attributes.
1650 l_notif_code:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_CODE');
1651
1652 IF (l_debug_level <= 1) THEN
1653 cln_debug_pub.Add('Notification_code:'|| l_notif_code , 1);
1654 END IF;
1655
1656 l_notif_desc:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'NOTIF_DESC');
1657
1658 IF (l_debug_level <= 1) THEN
1659 cln_debug_pub.Add('Notification_description:'|| l_notif_desc , 1);
1660 END IF;
1661
1662 l_status:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'STATUS');
1663
1664 IF (l_debug_level <= 1) THEN
1665 cln_debug_pub.Add('Status:'|| l_status , 1);
1666 END IF;
1667
1668 l_tp_id:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'TPID');
1669
1670 IF (l_debug_level <= 1) THEN
1671 cln_debug_pub.Add('Trading Partner ID:'|| l_tp_id , 1);
1672 END IF;
1673
1674 l_app_ref_id := '';
1675
1676 l_coll_pt:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'COLL_POINT');
1677
1678 IF (l_debug_level <= 1) THEN
1679 cln_debug_pub.Add('Collaboration Point:'|| l_coll_pt, 1);
1680 END IF;
1681
1682 l_intrl_cntrl_num:=wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'XMLG_INTERNAL_CONTROL_NUMBER');
1683
1684 IF (l_debug_level <= 1) THEN
1685 cln_debug_pub.Add('Internal Control Number:'|| l_intrl_cntrl_num, 1);
1686 END IF;
1687
1688 IF (l_debug_level <= 2) THEN
1689 cln_debug_pub.Add('Calling the ----CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS----- API with the above parameters...',2);
1690 END IF;
1691 -- Calls the CLN Notification Processing API to perform the pre-defined actions
1692
1693 BEGIN
1694 CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS( x_ret_code => l_return_code ,
1695 x_ret_desc => l_return_desc,
1696 p_notification_code => l_notif_code,
1697 p_notification_desc => l_notif_desc,
1698 p_status => l_status,
1699 p_tp_id => l_tp_id,
1700 p_reference => l_app_ref_id,
1701 p_coll_point => l_coll_pt,
1702 p_int_con_no => l_intrl_cntrl_num);
1703
1704 IF (l_debug_level <= 2) THEN
1705 cln_debug_pub.Add('Exiting the ----CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS----- API with the below parameters...',2);
1706 END IF;
1707
1708 IF (l_debug_level <= 1) THEN
1709 cln_debug_pub.Add('Return Code:'|| l_return_code, 1);
1710 cln_debug_pub.Add('Return Description:'|| l_return_desc, 1);
1711 END IF;
1712
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 l_error_code := SQLCODE;
1716 l_errmsg := SQLERRM;
1717
1718 IF (l_debug_level <= 5) THEN
1719 cln_debug_pub.Add('Exception in CLN_NP_PROCESSOR_PKG.TAKE_ACTIONS:' || l_error_code || ' : '||l_errmsg,5);
1720 END IF;
1721 END;
1722
1723 x_resultout := 'SUCCESS';
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 l_error_code := SQLCODE;
1728 l_errmsg := SQLERRM;
1729
1730 IF (l_debug_level <= 5) THEN
1731 cln_debug_pub.Add('Exception in NOTIFY_INVOICE_TO_SYSADMIN:' || l_error_code || ':' || l_errmsg,5);
1732 END IF;
1733
1734 x_resultout := 'ERROR';
1735
1736 IF (l_debug_level <= 2) THEN
1737 cln_debug_pub.Add('Exiting the ----NOTIFY_INVOICE_TO_SYSADMIN----- API with Resultout as ...'||x_resultout,2);
1738 END IF;
1739
1740 END NOTIFY_INVOICE_TO_SYSADMIN;
1741
1742 PROCEDURE TRIGGER_REJECTION(
1743 p_invoice_id IN NUMBER,
1744 p_group_id IN NUMBER,
1745 p_request_id IN NUMBER,
1746 p_external_doc_ref IN VARCHAR2) IS
1747 l_internal_control_number NUMBER;
1748 l_error_reject_string VARCHAR2(2000);
1749 l_invoice_number VARCHAR2(100);
1750 l_invoice_date DATE;
1751 l_invoice_amount NUMBER;
1752 l_po_number VARCHAR2(100);
1753
1754 CURSOR c_header_errors IS
1755 SELECT PARENT_ID, REJECT_LOOKUP_CODE
1756 FROM AP_INTERFACE_REJECTIONS
1757 WHERE PARENT_ID = p_invoice_id
1758 AND PARENT_TABLE = 'AP_INVOICES_INTERFACE';
1759
1760 CURSOR c_line_errors IS
1761 SELECT PARENT_ID, REJECT_LOOKUP_CODE
1762 FROM AP_INTERFACE_REJECTIONS
1763 WHERE PARENT_ID in (SELECT INVOICE_LINE_ID FROM AP_INVOICE_LINES_INTERFACE WHERE INVOICE_ID = p_invoice_id)
1764 AND PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE';
1765
1766 BEGIN
1767
1768 IF (l_Debug_Level <= 2) THEN
1769 cln_debug_pub.Add('------ Entering CLN_NTFYINVC_PKG.TRIGGER_REJECTION API ------ ', 2);
1770 END IF;
1771
1772 IF (l_Debug_Level <= 1) THEN
1773 cln_debug_pub.Add('p_invoice_id : ' || p_invoice_id, 1);
1774 cln_debug_pub.Add('p_group_id : ' || p_group_id, 1);
1775 cln_debug_pub.Add('p_request_id : ' || p_request_id, 1);
1776 cln_debug_pub.Add('p_external_doc_ref : ' || p_external_doc_ref, 1);
1777 END IF;
1778
1779 l_error_reject_string := GET_AR_REJECTIONS_STRING(p_invoice_id);
1780
1781 IF (l_Debug_Level <= 1) THEN
1782 cln_debug_pub.Add('l_error_reject_string : '|| l_error_reject_string, 1);
1783 END IF;
1784
1785 l_internal_control_number := p_group_id;
1786
1787 SELECT invoice_num, invoice_date, invoice_amount, po_number
1788 INTO l_invoice_number, l_invoice_date, l_invoice_amount, l_po_number
1789 FROM ap_invoices_interface
1790 WHERE invoice_id = p_invoice_id;
1791
1792 IF (l_Debug_Level <= 1) THEN
1793 cln_debug_pub.Add('Before inserting data into CLN_AP_INVOICE_REJECTION_ARCH', 1);
1794 END IF;
1795
1796 IF (l_Debug_Level <= 1) THEN
1797 cln_debug_pub.Add('l_invoice_number : ' || l_invoice_number, 1);
1798 cln_debug_pub.Add('l_invoice_date : ' || to_char(l_invoice_date,'yyyy-mm-dd hh24:mi:ss'), 1);
1799 cln_debug_pub.Add('l_invoice_amount : ' || l_invoice_amount, 1);
1800 cln_debug_pub.Add('l_po_number : ' || l_po_number, 1);
1801 END IF;
1802
1803 INSERT INTO CLN_AP_INVOICE_REJECTION_ARCH(
1804 invoice_id,
1805 xmlg_internal_control_number,
1806 invoice_number,
1807 reference_id,
1808 po_number,
1809 invoice_amount,
1810 invoice_date,
1811 reject_reason_string,
1812 creation_date,
1813 created_by,
1814 last_update_date,
1815 last_updated_by,
1816 last_update_login)
1817 VALUES(
1818 p_invoice_id,
1819 p_group_id,
1820 l_invoice_number,
1821 p_external_doc_ref,
1822 l_po_number,
1823 l_invoice_amount,
1824 l_invoice_date,
1825 l_error_reject_string,
1826 sysdate,
1827 fnd_global.user_id,
1828 sysdate,
1829 fnd_global.user_id,
1830 fnd_global.login_id
1831 );
1832
1833 IF (l_Debug_Level <= 1) THEN
1834 cln_debug_pub.Add('Before inserting data', 1);
1835 END IF;
1836
1837 IF (l_Debug_Level <= 2) THEN
1838 cln_debug_pub.Add('------ Exiting CLN_NTFYINVC_PKG.TRIGGER_REJECTION API ------ ', 2);
1839 END IF;
1840 END TRIGGER_REJECTION;
1841
1842
1843 PROCEDURE GET_REJECTED_INVOICE_DETAILS(
1844 p_invoice_id IN NUMBER,
1845 x_invoice_num IN OUT NOCOPY VARCHAR2,
1846 x_po_num IN OUT NOCOPY VARCHAR2,
1847 x_invoice_amt IN OUT NOCOPY NUMBER,
1848 x_invoice_date IN OUT NOCOPY DATE) IS
1849 BEGIN
1850 IF (l_Debug_Level <= 2) THEN
1851 cln_debug_pub.Add('------ Entering CLN_NTFYINVC_PKG.GET_REJECTED_INVOICE_DETAILS API ------ ', 2);
1852 END IF;
1853
1854 IF (l_Debug_Level <= 1) THEN
1855 cln_debug_pub.Add('p_invoice_id : ' || p_invoice_id, 1);
1856 END IF;
1857
1858
1859 BEGIN
1860 SELECT invoice_num, po_number, invoice_amount, invoice_date
1861 INTO x_invoice_num, x_po_num, x_invoice_amt, x_invoice_date
1862 FROM ap_invoices_interface
1863 WHERE invoice_id = p_invoice_id;
1864 EXCEPTION
1865 WHEN NO_DATA_FOUND THEN
1866 IF (l_debug_level <= 1) THEN
1867 cln_debug_pub.Add('rows not available in ap_invoices_interface. Querying CLN_AP_INVOICE_REJECTION_ARCH',1);
1868 END IF;
1869 --The following query should not error out... If it errors out, Its an exception condition
1870 SELECT invoice_number, po_number, invoice_amount, invoice_date
1871 INTO x_invoice_num, x_po_num, x_invoice_amt, x_invoice_date
1872 FROM CLN_AP_INVOICE_REJECTION_ARCH
1873 WHERE invoice_id = p_invoice_id;
1874 END;
1875
1876
1877 IF (l_Debug_Level <= 1) THEN
1878 cln_debug_pub.Add('x_invoice_num : ' || x_invoice_num, 1);
1879 cln_debug_pub.Add('x_po_num : ' || x_po_num, 1);
1880 cln_debug_pub.Add('x_invoice_amt : ' || x_invoice_amt, 1);
1881 cln_debug_pub.Add('x_invoice_date : ' || to_char(x_invoice_date,'yyyy-mm-dd hh24:mi:ss'), 1);
1882 END IF;
1883
1884 IF (l_Debug_Level <= 2) THEN
1885 cln_debug_pub.Add('------ Exiting CLN_NTFYINVC_PKG.GET_REJECTED_INVOICE_DETAILS API ------ ', 2);
1886 END IF;
1887
1888 END GET_REJECTED_INVOICE_DETAILS;
1889
1890 BEGIN
1891
1892 l_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
1893
1894 END CLN_NTFYINVC_PKG;