[Home] [Help]
PACKAGE BODY: APPS.AP_PMT_VALIDATIONS_PKG
Source
1 PACKAGE BODY AP_PMT_VALIDATIONS_PKG AS
2 /* $Header: appyvalb.pls 120.2 2011/06/01 22:20:26 jaanders noship $ */
3
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PMT_VALIDATIONS_PKG';
7
8 PROCEDURE performValidations(
9 p_instruction_id IN NUMBER,
10 p_completed_pmts_group_id IN NUMBER,
11 x_valResult OUT NOCOPY BOOLEAN,
12 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE )
13 IS
14 l_err_count NUMBER;
15 l_ap_pmt_count NUMBER;
16 l_iby_pmt_count NUMBER;
17 l_ap_pmtsch_count NUMBER;
18 l_int_inv_expected_count NUMBER;
19 l_int_inv_actual_count NUMBER;
20 l_int_line_actual_count NUMBER;
21 l_iby_doc_count NUMBER;
22 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.performValidations';
23
24 l_payment_id NUMBER;
25 l_payment_document_id NUMBER;
26 l_payment_method_code VARCHAR2(30);
27 l_bank_acct_use_id NUMBER;
28 l_seq_num_profile VARCHAR2(80);
29 l_doc_category_code VARCHAR2(30);
30 l_sse_cc_payment_notify VARCHAR2(1);
31 l_process_type IBY_PAYMENTS_ALL.PROCESS_TYPE%TYPE;
32
33 CURSOR check_sequences
34 IS
35 SELECT pmt.payment_id,
36 ins.payment_document_id,
37 pmt.payment_method_code,
38 ce.bank_acct_use_id
39 FROM iby_fd_payments_v pmt,
40 iby_pay_instructions_all ins,
41 iby_payment_profiles ipp,
42 ce_bank_acct_uses_all ce
43 WHERE pmt.completed_pmts_group_id = p_completed_pmts_group_id
44 AND ipp.payment_profile_id = ins.payment_profile_id
45 AND ins.payment_instruction_id = pmt.payment_instruction_id
46 AND ce.org_id = pmt.org_id
47 AND ce.bank_account_id = pmt.internal_bank_account_id;
48
49 BEGIN
50
51 --===== INITIALIZE =====--
52 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Parameters Passed ...');
53 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,' Payment Instruction ID : ' || p_instruction_id);
54 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,' Completed Payments Group ID: ' || p_completed_pmts_group_id);
55
56 /* The processing type of the documents will determine the validations to be performed */
57 SELECT process_type
58 INTO l_process_type
59 FROM iby_payments_all
60 WHERE completed_pmts_group_id = p_completed_pmts_group_id
61 AND rownum =1;
62
63 /* Process_type IMMEDIATE is used for quick checks.
64 These will have a payment id , but no completed pmts group id
65 Process_type MANUAL is used for Manual payments.
66 This will have a payment id, but no completed pmts group id.
67 They all have a payment_id, except refunds.
68 IBY should not be called for refunds.
69 Process_type STANDARD is used for PPRs.
70 */
71
72 IF l_process_type = 'STANDARD' THEN
73
74 x_valResult := TRUE;
75
76 --===== VALIDATION-A = System Parameters and Currency Validation =====--
77 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation1.txt
78
79 SELECT COUNT(*) validation1
80 INTO l_err_count
81 FROM iby_fd_payments_v pmts
82 WHERE pmts.completed_pmts_group_id = p_completed_pmts_group_id
83 AND NOT EXISTS
84 (SELECT 1
85 FROM ap_system_parameters_all asp,
86 gl_code_combinations gc,
87 gl_sets_of_books gsob,
88 fnd_currencies fndc
89 WHERE gc.code_combination_id(+) = asp.interest_code_combination_id
90 AND gsob.set_of_books_id = asp.set_of_books_id
91 AND asp.org_id = pmts.org_id
92 AND base_currency_code = fndc.currency_code
93 );
94 IF ( l_err_count > 0 ) THEN
95 x_valResult := FALSE;
96 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-A failed - Error Count:' || l_err_count);
97 ELSE
98 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-A passed');
99 END IF;
100
101 --===== VALIDATION-B = Interest Invoice Insert Validation =====--
102 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation2.txt
103
104 /* Number of selected invoices that should result in interest invoices: */
105
106 select count(*)
107 into l_int_inv_expected_count
108 from ap_selected_invoices_all asi
109 , iby_fd_docs_payable_v ibydocs
110 where asi.original_invoice_id is not null
111 and ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(asi.checkrun_id)
112 and ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(asi.invoice_id)
113 and ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(asi.payment_num)
114 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id;
115
116 /* Number of interest invoices that will actually be created */
117
118 SELECT COUNT(*) validation2
119 INTO l_int_inv_actual_count
120 FROM iby_fd_payments_v ibypmts,
121 iby_fd_docs_payable_v ibydocs,
122 ap_supplier_sites_all aps,
123 ap_selected_invoices_all apsi,
124 ap_invoices_all orig
125 WHERE ibypmts.payment_id = ibydocs.payment_id
126 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
127 --AND ibypmts.org_id = :l_current_org_id
128 AND ibypmts.org_type = 'OPERATING_UNIT'
129 AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
130 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
131 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
132 AND aps.vendor_site_id(+) = ibypmts.supplier_site_id
133 AND apsi.original_invoice_id = orig.invoice_id;
134
135 IF ( l_int_inv_expected_count <> l_int_inv_actual_count ) THEN
136 x_valResult := FALSE;
137 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-B failed - Expected Interest Count:'
138 || l_int_inv_expected_count || ' Actual Interest Count: '|| l_int_inv_actual_count);
139 ELSE
140 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-B passed');
141 END IF;
142
143 --===== VALIDATION-C = Interest Invoice Lines Insert Validation =====--
144 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation2.txt
145 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation3.txt
146
147 SELECT COUNT(*) validation3
148 INTO l_int_line_actual_count
149 FROM iby_fd_payments_v ibypmts,
150 iby_fd_docs_payable_v ibydocs,
151 po_vendors pv,
152 ap_selected_invoices_all apsi,
153 ap_invoices_all orig
154 WHERE ibypmts.payment_id = ibydocs.payment_id
155 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
156 --AND ibypmts.org_id = :l_current_org_id
157 AND ibypmts.org_type = 'OPERATING_UNIT'
158 AND ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
159 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
160 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
161 AND apsi.vendor_id = pv.vendor_id
162 AND apsi.original_invoice_id = orig.invoice_id;
163
164 IF ( l_int_line_actual_count > l_int_inv_expected_count ) THEN
165 x_valResult := FALSE;
166 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-C failed - Expected Interest Count:'
167 || l_int_inv_expected_count || ' Actual Interest Line Count: '|| l_int_line_actual_count);
168 ELSE
169 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-C passed');
170 END IF;
171
172 --===== VALIDATION-D = Batches Insert Validation =====--
173 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation4.txt
174 SELECT (
175 CASE
176 WHEN COUNT(*) > 0
177 THEN 0
178 ELSE 1
179 END) validation4
180 INTO l_err_count
181 FROM ap_lookup_codes lc
182 WHERE lookup_type = 'NLS TRANSLATION'
183 AND lookup_code = 'INTEREST ON PAYMENTBATCH';
184 IF ( l_err_count > 0 ) THEN
185 x_valResult := FALSE;
186 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-D failed');
187 ELSE
188 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-D passed');
189 END IF;
190
191 --===== VALIDATION-E = PAYMENTS TALLY = Validation5 + Validation6 = Validation7 =====--
192 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation5.txt
193 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation6.txt
194 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation7.txt
195
196 --IBY_CHECK_COUNT
197 SELECT COUNT(*) iby_check_count
198 INTO l_iby_pmt_count
199 FROM iby_fd_payments_v
200 WHERE completed_pmts_group_id = p_completed_pmts_group_id;
201 --AP_CHECK_COUNT
202 --(almost near to the original)
203 SELECT COUNT(*) ap_check_count
204 INTO l_ap_pmt_count
205 FROM iby_fd_payments_v iby,
206 po_vendors pv,
207 ce_bank_acct_uses_all ce,
208 ce_gl_accounts_ccid cegl,
209 ce_bank_accounts ceb,
210 ap_supplier_sites_all aps
211 WHERE iby.inv_payee_party_id = pv.party_id(+)
212 AND aps.vendor_site_id(+) = iby.supplier_site_id
213 AND NVL(pv.vendor_id,-99) =
214 (SELECT
215 CASE
216 WHEN inv.invoice_type_lookup_code = 'PAYMENT REQUEST'
217 AND SIGN(inv.vendor_id) = -1
218 THEN NVL(pv.vendor_id, -99)
219 ELSE NVL(vendor_id, -99)
220 END
221 FROM ap_invoices_all inv,
222 iby_docs_payable_all idp
223 WHERE inv.invoice_id =idp.calling_app_doc_unique_ref2
224 AND idp.payment_id =iby.payment_id
225 AND idp.payment_service_request_id = iby.payment_service_request_id
226 AND rownum =1
227 )
228 AND ce.bank_account_id = iby.internal_bank_account_id
229 AND ceb.bank_account_id = ce.bank_account_id
230 AND iby.org_type = 'OPERATING_UNIT'
231 AND ce.org_id = iby.org_id
232 AND ce.bank_acct_use_id = cegl.bank_acct_use_id
233 AND iby.completed_pmts_group_id = p_completed_pmts_group_id;
234 IF ( l_iby_pmt_count <> l_ap_pmt_count ) THEN
235 x_valResult := FALSE;
236 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-E (Payments tally) failed - IBY:' ||l_iby_pmt_count ||'; AP:'|| l_ap_pmt_count);
237 ELSE
238 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-E (Payments tally) passed - IBY:' ||l_iby_pmt_count ||'; AP:'|| l_ap_pmt_count);
239 END IF;
240
241 --===== VALIDATION-F = DOCUMENTS PAYABLE TALLY = Validation5 + Validation6 = Validation7 =====--
242 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation5.txt
243 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation6.txt
244 --http://ebizapdev.idc.oracle.com/iby/archive/Bugs/ap-iby-completion/validation7.txt
245
246 --IBY_DOC_COUNT
247 SELECT COUNT(*) iby_doc_count
248 INTO l_iby_doc_count
249 FROM iby_fd_docs_payable_v
250 WHERE payment_id IN
251 (SELECT payment_id
252 FROM iby_payments_all
253 WHERE completed_pmts_group_id = p_completed_pmts_group_id
254 );
255
256 --PAYMENT_SCHEDULE and SELECTED INVOICE COUNT
257
258 SELECT COUNT(*) ap_pmt_sch_count
259 INTO l_ap_pmtsch_count
260 FROM iby_fd_payments_v ibypmts,
261 iby_fd_docs_payable_v ibydocs,
262 ap_selected_invoices_all SI,
263 fnd_currencies FORE,
264 ap_payment_schedules_all PS,
265 ap_invoices_all AI
266 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(si.checkrun_id)
267 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(si.invoice_id)
268 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(si.payment_num)
269 AND ibypmts.payment_id = ibydocs.payment_id
270 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
271 AND ibypmts.payment_currency_code = FORE.currency_code
272 AND PS.invoice_id(+) = SI.invoice_id
273 AND PS.payment_num(+) = SI.payment_num
274 AND AI.invoice_id = SI.invoice_id;
275
276 /* Because the payment schedules for interest invoices are only created
277 at runtime of the payments_completed procedure, the interest invoices
278 have to be added in separately.
279 */
280
281 -- Check ( ( IBY_DOC_COUNT - AP_INTEREST_INVOICE_COUNT ) = PAYMENT_SCHEDULE_COUNT )
282 IF ( l_iby_doc_count <> (l_ap_pmtsch_count + l_int_inv_actual_count) ) THEN
283 x_valResult := FALSE;
284 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-F (Documents Payable tally) failed - IBY:' ||l_iby_doc_count ||'; AP:'|| l_ap_pmtsch_count || ',' || l_int_inv_actual_count);
285 ELSE
286 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-F (Documents Payable tally) passed - IBY:' ||l_iby_doc_count ||'; AP:'|| l_ap_pmtsch_count || ',' || l_int_inv_actual_count);
287 END IF;
288
289 --===== VALIDATION-G = Assign Sequences =====--
290 l_err_count:= 0;
291 fnd_profile.get('UNIQUE:SEQ_NUMBERS',l_seq_num_profile);
292 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,' Sequence number profile : '||l_seq_num_profile);
293
294 IF ( l_seq_num_profile IN ('A', 'P') ) THEN
295 OPEN check_sequences;
296 LOOP
297 FETCH check_sequences
298 INTO l_payment_id,
299 l_payment_document_id,
300 l_payment_method_code,
301 l_bank_acct_use_id;
302
303 CE_BANK_AND_ACCOUNT_VALIDATION.get_pay_doc_cat(l_payment_document_id, l_payment_method_code, l_bank_acct_use_id, l_doc_category_code);
304
305 IF( l_doc_category_code = -1) THEN
306 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,' Assign sequences validation failed for Payment ID : '||l_payment_id);
307 l_err_count := l_err_count + 1;
308 END IF;
309 END LOOP;
310
311 IF ( l_err_count > 0 ) THEN
312 x_valResult := FALSE;
313 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-G failed');
314 ELSE
315 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-G passed');
316 END IF;
317 END IF;
318
319 --===== VALIDATION-H = Subscribe to Payment Event ====--
320 /* This results in a call to any functions listed under
321 payment_created in the ap_product_registrations table.
322 Validations for potential problems in those functions
323 are performed here.
324 */
325
326 /* 1 -- AP_WEB_UTILITIES_PKG.NotifyEmployeeOnPayment
327 duplicates in ap_expense_report_headers_all
328 */
329
330 /* This validation is unnecessary after the error handling changes
331 for procedures registered in ap_product_registrations
332 fnd_profile.get('SSE_CC_PAYMENT_NOTIFY', l_sse_cc_payment_notify);
333 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'SSE_CC_PAYMENT_NOTIFY'
334 || l_sse_cc_payment_notify);
335 IF (nvl(l_sse_cc_payment_notify,'N') = 'Y') then
336
337 select count(*)
338 into l_err_count
339 from ap_invoices_all ai
340 , ap_selected_invoices_all apsi
341 , iby_fd_docs_payable_v ibydocs
342 where ai.source in ( 'Both Pay')
343 and (nvl(ai.paid_on_behalf_employee_id, -1) <> -1
344 or ai.invoice_type_lookup_code = 'EXPENSE REPORT')
345 and ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(apsi.checkrun_id)
346 and ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(apsi.invoice_id)
347 and ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(apsi.payment_num)
348 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
349 and apsi.invoice_id = ai.invoice_id
350 and exists
351 (SELECT 1
352 FROM ap_expense_report_headers_all aerh1,
353 ap_expense_report_headers_all aerh2
354 WHERE aerh1.bothpay_parent_id = aerh2.report_header_id
355 AND aerh1.invoice_num = ai.invoice_num
356 AND aerh1.org_id = ai.org_id
357 AND aerh1.source = 'Both Pay'
358 group by aerh1.invoice_num
359 having count(*) > 1);
360
361 END IF;
362 IF ( l_err_count > 0 ) THEN
363 x_valResult := FALSE;
364 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-H-1 failed');
365 ELSE
366 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-H-1 passed');
367 END IF;
368 */
369
370 /* 2 AP_WEB_UTILITIES_PKG.NotifyEmployeeOnPayment
371 ORA-01403 Getting Bank Information for the Invoice and Vendor
372 */
373
374 /* This validation is unnecessary after the error handling changes
375 for procedures registered in ap_product_registrations
376 select
377 (CASE
378 WHEN COUNT(*) > 0
379 THEN 0
380 ELSE 1
381 END) validationG2
382 into l_err_count
383 from iby_ext_bank_accounts_v eba
384 , iby_fd_payments_v ibypmts
385 where eba.ext_bank_account_id = ibypmts.external_bank_account_id
386 and ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
387 and ibypmts.external_bank_account_id is not null;
388
389 IF ( l_err_count > 0 ) THEN
390 x_valResult := FALSE;
391 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-H-2 failed');
392 ELSE
393 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-H-2 passed');
394 END IF;
395
396 */
397
398 --= VALIDATION-I Orphan AWT Temp Distributions ====--
399
400 l_err_count:= 0;
401 SELECT COUNT(*)
402 INTO l_err_count
403 FROM iby_fd_payments_v ibypmts,
404 iby_fd_docs_payable_v ibydocs,
405 ap_selected_invoices_all SI,
406 ap_awt_temp_distributions_all atd
407 WHERE ibydocs.calling_app_doc_unique_ref1 = TO_CHAR(si.checkrun_id)
408 AND ibydocs.calling_app_doc_unique_ref2 = TO_CHAR(si.invoice_id)
409 AND ibydocs.calling_app_doc_unique_ref3 = TO_CHAR(si.payment_num)
410 AND ibypmts.payment_id = ibydocs.payment_id
411 AND ibypmts.completed_pmts_group_id = p_completed_pmts_group_id
412 AND atd.invoice_id = si.invoice_id
413 and not exists
414 (SELECT 1
415 FROM gl_Period_Statuses gps,
416 ap_System_Parameters_All Asp
417 WHERE gps.Application_Id = 200
418 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
419 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
420 AND atd.accounting_date BETWEEN Trunc(gps.Start_Date)
421 AND Trunc(gps.End_Date)
422 AND Nvl(Asp.Org_Id,- 99) = Nvl(atd.org_id,- 99)
423 AND gps.closing_Status in ('O', 'F'));
424
425 IF ( l_err_count > 0 ) THEN
426 x_valResult := FALSE;
427 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-I failed');
428 ELSE
429 IBY_VALIDATIONSETS_CALLS_PUB.print_debuginfo(l_module_name,'Validation-I passed');
430 END IF;
431
432
433 --===== Error Reporting Logic =====--
434 IF ( x_valResult = FALSE ) THEN
435 x_docErrorRec.error_code := 'IBY_AP_PPR_VALIDATION_FAIL';
436 FND_MESSAGE.SET_NAME('IBY', x_docErrorRec.error_code);
437 x_docErrorRec.error_message := fnd_message.get;
438 END IF;
439
440 END IF; /* l_process_type = 'STANDARD' */
441
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 FND_MESSAGE.SET_NAME('IBY', '');
446 FND_MESSAGE.SET_TOKEN('SQLERR','evaluateCondition : '||SUBSTR(SQLERRM, 1, 300));
447 FND_MSG_PUB.Add;
448 RAISE;
449 END performValidations;
450 END AP_PMT_VALIDATIONS_PKG;