[Home] [Help]
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);
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
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 := '';
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;
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:
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:
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 || ',';
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 || '%';
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:
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:
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);
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);
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;
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:
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:
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.
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:
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
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
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
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:
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);
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);
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:
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);
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);
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);
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;
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:
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';
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';
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
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);
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;
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:
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
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);
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:
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
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);
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;
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:
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
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);
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);
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);
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);
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);
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);
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);
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);
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);
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:
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;
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:
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);
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);
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
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);
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:
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);
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
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;
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);
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
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:
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);
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
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;
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';
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);
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
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:
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);
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:
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;
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);
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:
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
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);
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
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:
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);
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;
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:
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
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:
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:
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
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
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;
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;
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
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
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
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:
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);
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:
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
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);
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')
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:
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;
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';
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:
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;
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:
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
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;
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:
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();
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:
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',
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,
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:
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
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
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:
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;
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:
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
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);
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);
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);
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);
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;
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:
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;
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:
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:
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();
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
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);
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;
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:
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;
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:
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);
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);
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
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);
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:
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:
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
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:
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:
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);
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);
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');
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:
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;
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
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:
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';
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;
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
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
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
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
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
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';
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:
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
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
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
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
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
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');
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
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);
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);
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);
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;
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:
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);
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:
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:
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:
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;
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:
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:
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);
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:
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:
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:
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:
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);
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);
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);
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;
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:
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;
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:
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:
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,
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;
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:
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
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;
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
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);
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:
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');
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:
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:
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:
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:
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:
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);
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
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);
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
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';
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:
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:
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);
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;
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:
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);
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:
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:
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);
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;
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:
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(
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,
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);
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:
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);
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
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
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;
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:
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
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);
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: