1 PACKAGE BODY IBY_ASSIGN_PUB AS
2 /*$Header: ibyasgnb.pls 120.12.12010000.1 2008/07/28 05:39:15 appldev ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_ASSIGN_PUB';
8
9 --
10 -- List of document statuses that are set in this
11 -- module (assignment flow).
12 --
13 DOC_STATUS_MISSING_ACC CONSTANT VARCHAR2(100) := 'MISSING_ACCOUNT';
14 DOC_STATUS_MISSING_PROF CONSTANT VARCHAR2(100) := 'MISSING_PROFILE';
15 DOC_STATUS_MISSING_BOTH CONSTANT VARCHAR2(100) :=
16 'MISSING_ACCOUNT_AND_PROFILE';
17 DOC_STATUS_FULL_ASSIGNED CONSTANT VARCHAR2(100) := 'READY_FOR_VALIDATION';
18
19 --
20 -- List of payment request statuses that are set in this
21 -- module (assignment flow).
22 --
23 REQ_STATUS_FULL_ASSIGNED CONSTANT VARCHAR2(100) := 'ASSIGNMENT_COMPLETE';
24 REQ_STATUS_INFO_REQD CONSTANT VARCHAR2(100) := 'INFORMATION_REQUIRED';
25
26 --
27 -- Forward declarations
28 --
29 PROCEDURE print_debuginfo(
30 p_module IN VARCHAR2,
31 p_debug_text IN VARCHAR2,
32 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
33 );
34
35 /*--------------------------------------------------------------------
36 | NAME:
37 | performAssignments
38 |
39 | PURPOSE:
40 | The entry point for the account/profile assigments flow (F4).
41 | This method picks up all documents for the payment request
42 | and checks if any of them have a missing iinternal bank account
43 | or payment profile.
44 |
45 | If yes, then it tries to default the missing account/profile
46 | from (a) the request, (b) from CE API for bank accounts, (c)
47 | from payment method for profile
48 |
49 | If it is not possible to default account/profile, the document
50 | status is updated to 'missing account/profile'.
51 |
52 | PARAMETERS:
53 | IN
54 |
55 |
56 | OUT
57 |
58 |
59 | RETURNS:
60 |
61 | NOTES:
62 |
63 *---------------------------------------------------------------------*/
64 PROCEDURE performAssignments(
65 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
66 payment_service_request_id%type,
67 x_return_status IN OUT NOCOPY VARCHAR2)
68 IS
69
70 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
71 '.performAssignments';
72 l_profile_id iby_docs_payable_all.payment_profile_id%TYPE := -1;
73
74 dummybankAccountsArray IBY_ASSIGN_PUB.bankAccounts;
75 bankAccountsArray CE_BANK_AND_ACCOUNT_UTIL.BankAcctIdTable;
76
77 l_assignCriTab IBY_ASSIGN_PUB.assignCriteriaTabType;
78 l_updateDocsRec IBY_ASSIGN_PUB.updateDocAttributesRec;
79 l_setDocsRec IBY_ASSIGN_PUB.setDocAttributesRec;
80 l_setDocsTab IBY_ASSIGN_PUB.setDocAttribsTabType;
81
82 l_acct_default_flag BOOLEAN := false;
83 l_prof_default_flag BOOLEAN := false;
84
85 G_LINES_PER_FETCH CONSTANT NUMBER:= 1000;
86 l_trx_line_index BINARY_INTEGER;
87 l_no_rec_in_ppr BOOLEAN;
88
89 /* variables for fields from document */
90 doc_id iby_docs_payable_all.document_payable_id%TYPE;
91 ca_doc_id1 iby_docs_payable_all.
92 calling_app_doc_unique_ref1%TYPE;
93 ca_doc_id2 iby_docs_payable_all.
94 calling_app_doc_unique_ref2%TYPE;
95 ca_doc_id3 iby_docs_payable_all.
96 calling_app_doc_unique_ref3%TYPE;
97 ca_doc_id4 iby_docs_payable_all.
98 calling_app_doc_unique_ref4%TYPE;
99 ca_doc_id5 iby_docs_payable_all.
100 calling_app_doc_unique_ref5%TYPE;
101 ca_id iby_docs_payable_all.calling_app_id%TYPE;
102 pp_tt_cd iby_docs_payable_all.pay_proc_trxn_type_code%TYPE;
103 doc_int_bank_acct_id iby_docs_payable_all.internal_bank_account_id%TYPE;
104 doc_profile_id iby_docs_payable_all.payment_profile_id%TYPE;
105 doc_pay_currency iby_docs_payable_all.payment_currency_code%TYPE;
106 doc_pmt_method iby_docs_payable_all.payment_method_code%TYPE;
107 doc_pmt_format iby_docs_payable_all.payment_format_code%TYPE;
108 doc_org_id iby_docs_payable_all.org_id%TYPE;
109 doc_org_type iby_docs_payable_all.org_type%TYPE;
110 doc_pmt_date iby_docs_payable_all.payment_date%TYPE;
111 doc_payee_id iby_docs_payable_all.ext_payee_id%TYPE;
112
113 /* variables for fields from payment request */
114 req_ca_payreq_id iby_pay_service_requests.
115 call_app_pay_service_req_code%TYPE;
116 req_ca_id iby_pay_service_requests.calling_app_id%TYPE;
117 req_int_bank_acct_id iby_pay_service_requests.internal_bank_account_id%TYPE;
118 req_profile_id iby_pay_service_requests.payment_profile_id%TYPE;
119
120 /*
121 * Cursor to pick up the documents of a given payment request.
122 */
123 CURSOR c_document_attribs(p_payment_request_id VARCHAR2)
124 IS
125 SELECT document_payable_id,
126 calling_app_id, --| These seven
127 calling_app_doc_unique_ref1, --| are used
128 calling_app_doc_unique_ref2, --| by the
129 calling_app_doc_unique_ref3, --| calling app
130 calling_app_doc_unique_ref4, --| to uniquely
131 calling_app_doc_unique_ref5, --| id a
132 pay_proc_trxn_type_code, --| document
133 NVL(internal_bank_account_id, -1), -- Internal bank account id
134 NVL(payment_profile_id, -1), -- Payment profile id
135 payment_currency_code,
136 payment_method_code,
137 payment_format_code,
138 org_id,
139 org_type,
140 payment_date,
141 NVL(ext_payee_id, -1) -- payee id
142 FROM IBY_DOCS_PAYABLE_ALL
143 WHERE payment_service_request_id = p_payment_request_id
144 AND (internal_bank_account_id IS NULL OR
145 payment_profile_id IS NULL)
146 ORDER BY document_payable_id;
147
148 BEGIN
149 print_debuginfo(l_module_name, 'ENTER');
150
151 print_debuginfo(l_module_name, 'Payment Request Id : '||
152 p_payment_request_id);
153
154 /*
155 * Get attributes from the payment request like
156 * calling app payred id, internal bank account etc.
157 */
158 getRequestAttributes(p_payment_request_id, req_ca_payreq_id,
159 req_ca_id, req_int_bank_acct_id, req_profile_id);
160
161 print_debuginfo(l_module_name,
162 'Fetched assignment attributes from request -'
163 || ' Payment request id: ' || p_payment_request_id
164 || ', CA payment request id: ' || req_ca_payreq_id
165 || ', internal bank account: ' || req_int_bank_acct_id
166 || ', payment profile: ' || req_profile_id
167 );
168
169 /*-- REQUEST LEVEL DEFAULTING STARTS HERE --*/
170 /*
171 * Populate the updateDocAttributesRec record. This information
172 * will be used to update all documents in IBY_DOCS_PAYABLE_ALL
173 * that have this payment request id.
174 */
175
176 IF (req_int_bank_acct_id IS NOT NULL) THEN
177 l_updateDocsRec.payment_request_id := p_payment_request_id;
178 l_updateDocsRec.int_bank_acct_id := req_int_bank_acct_id;
179 l_updateDocsRec.bank_acct_flag := true;
180 END IF;
181
182 IF (req_profile_id IS NOT NULL) THEN
183 l_updateDocsRec.payment_request_id := p_payment_request_id;
184 l_updateDocsRec.pay_profile_id := req_profile_id;
185 l_updateDocsRec.pay_profile_flag := true;
186 END IF;
187
188 /*
189 * If both bank account and profile were available from the request
190 * then we have all the information we need to update the documents.
191 * Update the documents and exit.
192 */
193 IF (req_int_bank_acct_id IS NOT NULL AND req_profile_id IS NOT NULL) THEN
194 /*
195 * Update IBY_DOCS_PAYABLE_ALL table with the account id and
196 * profile id from request.
197 */
198 updateDocumentAssignments(l_updateDocsRec);
199
200 /*
201 * Update document and request statuses.
202 */
203 finalizeStatuses(p_payment_request_id, x_return_status);
204
205 /*
206 * All documents now have account and profile assigned. No
207 * need to continue further, we can exit at this point.
208 */
209 RETURN;
210
211 ELSIF (req_int_bank_acct_id IS NOT NULL OR req_profile_id IS NOT NULL) THEN
212 /*
213 * Update IBY_DOCS_PAYABLE_ALL table with either the account
214 * id or the profile id (whichever was available).
215 *
216 * Then continue down the process to individually assign
217 * profile/account to the documents on a case-by-case basis.
218 */
219 updateDocumentAssignments(l_updateDocsRec);
220
221 END IF;
222
223 /*-- DOCUMENT LEVEL DEFAULTING STARTS HERE --*/
224 /*
225 * Request level attributes were not populated, or only
226 * partially populated to the documents. We have to individually
227 * determine what attributes to assign to each document.
228 */
229
230 l_no_rec_in_ppr := TRUE;
231
232 /*
233 * Step 1:
234 * Pull up all documents for this payment request where
235 * the document does not have the bank account or profile
236 * assigned.
237 */
238 OPEN c_document_attribs(p_payment_request_id);
239 LOOP
240
241 iby_disburse_submit_pub_pkg.delete_docspayTab;
242
243 FETCH c_document_attribs BULK COLLECT INTO
244 iby_disburse_submit_pub_pkg.docspayTab.document_payable_id,
245 iby_disburse_submit_pub_pkg.docspayTab.calling_app_id,
246 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1,
247 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2,
248 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3,
249 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4,
250 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5,
251 iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code,
252 iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id,
253 iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id,
254 iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code,
255 iby_disburse_submit_pub_pkg.docspayTab.payment_method_code,
256 iby_disburse_submit_pub_pkg.docspayTab.payment_format_code,
257 iby_disburse_submit_pub_pkg.docspayTab.org_id,
258 iby_disburse_submit_pub_pkg.docspayTab.org_type,
259 iby_disburse_submit_pub_pkg.docspayTab.payment_date,
260 iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id
261 LIMIT G_LINES_PER_FETCH;
262
263 FOR l_trx_line_index IN nvl(iby_disburse_submit_pub_pkg.docspayTab.document_payable_id.FIRST,0) .. nvl(iby_disburse_submit_pub_pkg.docspayTab.document_payable_id.LAST,-99)
264 LOOP
265 l_no_rec_in_ppr := FALSE;
266
267 /*
268 * Step 2:
269 * Loop through the fetched documents assigning default
270 * internal bank account and payment profile whenever
271 * necessary (and whenever possible).
272 */
273
274 doc_id := iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index);
275 ca_id := iby_disburse_submit_pub_pkg.docspayTab.calling_app_id(l_trx_line_index);
276 ca_doc_id1 := iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1(l_trx_line_index);
277 ca_doc_id2 := iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(l_trx_line_index);
278 ca_doc_id3 := iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3(l_trx_line_index);
279 ca_doc_id4 := iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4(l_trx_line_index);
280 ca_doc_id5 := iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5(l_trx_line_index);
281 pp_tt_cd := iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code(l_trx_line_index);
282 doc_int_bank_acct_id := iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id(l_trx_line_index);
283 doc_profile_id := iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index);
284 doc_pay_currency := iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code(l_trx_line_index);
285 doc_pmt_method := iby_disburse_submit_pub_pkg.docspayTab.payment_method_code(l_trx_line_index);
286 doc_pmt_format := iby_disburse_submit_pub_pkg.docspayTab.payment_format_code(l_trx_line_index);
287 doc_org_id := iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index);
288 doc_org_type := iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index);
289 doc_pmt_date := iby_disburse_submit_pub_pkg.docspayTab.payment_date(l_trx_line_index);
290 doc_payee_id := iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index);
291
292 /*
293 * Log the fields for the fetched document
294 */
295 print_debuginfo(l_module_name, 'Fetched attributes for document: '
296 || doc_id
297 || ', calling app id: ' || ca_id
298 || ', calling app doc id1: ' || ca_doc_id1
299 || ', calling app doc id2: ' || ca_doc_id2
300 || ', calling app doc id3: ' || ca_doc_id3
301 || ', calling app doc id4: ' || ca_doc_id4
302 || ', calling app doc id5: ' || ca_doc_id5
303 || ', ca pay proc ttype cd: ' || pp_tt_cd
304 );
305
306 print_debuginfo(l_module_name, 'Other attributes for document: '
307 || doc_id
308 || ', internal bank account: ' || doc_int_bank_acct_id
309 || ', profile: ' || doc_profile_id
310 || ', payment method: ' || doc_pmt_method
311 || ', payment format: ' || doc_pmt_format
312 || ', payment currency: ' || doc_pay_currency
313 || ', org id: ' || doc_org_id
314 || ', org type: ' || doc_org_type
315 || ', payment date: ' || doc_pmt_date
316 || ', payee id: ' || doc_payee_id
317 );
318
319 /*
320 * Reset the temporary record after each iteration
321 * so that old field values are overwritten.
322 */
323 l_setDocsRec.doc_id := NULL;
324 l_setDocsRec.ca_id := NULL;
325 l_setDocsRec.ca_doc_id1 := NULL;
326 l_setDocsRec.ca_doc_id2 := NULL;
327 l_setDocsRec.ca_doc_id3 := NULL;
328 l_setDocsRec.ca_doc_id4 := NULL;
329 l_setDocsRec.ca_doc_id5 := NULL;
330 l_setDocsRec.pp_tt_cd := NULL;
331 l_setDocsRec.pay_profile_id := NULL;
332 l_setDocsRec.int_bank_acct_id := NULL;
333 l_setDocsRec.status := NULL;
334
335 /*
336 * Step 2A:
337 * Try to default the internal bank account for
338 * the document.
339 */
340 IF (doc_int_bank_acct_id = -1) THEN
341 /*
342 * Call Cash Management API
343 *
344 * Input : Payment currency
345 * Organization id
346 * Output: List of internal bank accounts
347 * that match this criteria
348 *
349 * If there is only one bank account that matches
350 * the given criteria, then the bank account can be
351 * defaulted, not otherwise.
352 */
353 CE_BANK_AND_ACCOUNT_UTIL.get_internal_bank_accts(
354 doc_pay_currency,
355 doc_org_type,
356 doc_org_id,
357 doc_pmt_date,
358 bankAccountsArray
359 );
360
361 --
362 -- Uncomment if you want to test with a dummy API
363 --
364 --dummyCEAPI(doc_pay_currency, doc_pmt_date, doc_org_id,
365 -- dummyBankAccountsArray);
366
367 print_debuginfo(l_module_name, 'Number of bank accounts '
368 || ' returned by CE for currency '
369 || doc_pay_currency
370 || ', pmt date '
371 || doc_pmt_date
372 || ' and org '
373 || doc_org_id
374 || ' is '
375 || bankAccountsArray.COUNT
376 );
377
378 /*
379 * If only bank account returned, then assign this as
380 * default bank account to this document.
381 */
382 IF (bankAccountsArray.COUNT = 1) THEN
383
384 print_debuginfo(l_module_name, 'Setting default bank account'
385 || ' to '
386 || bankAccountsArray(bankAccountsArray.COUNT)
387 || ' for document: '
388 || doc_id
389 );
390
391 l_setDocsRec.doc_id := doc_id;
392 l_setDocsRec.ca_id := ca_id;
393 l_setDocsRec.ca_doc_id1 := ca_doc_id1;
394 l_setDocsRec.ca_doc_id2 := ca_doc_id2;
395 l_setDocsRec.ca_doc_id3 := ca_doc_id3;
396 l_setDocsRec.ca_doc_id4 := ca_doc_id4;
397 l_setDocsRec.ca_doc_id5 := ca_doc_id5;
398 l_setDocsRec.pp_tt_cd := pp_tt_cd;
399 l_setDocsRec.int_bank_acct_id
400 := bankAccountsArray(bankAccountsArray.COUNT);
401 l_acct_default_flag := true;
402
403 ELSE
404
405 /*
406 * CE returned multiple bank accounts for the given
407 * document attributes. We cannot default the bank
408 * account.
409 */
410 l_setDocsRec.doc_id := doc_id;
411 l_setDocsRec.ca_id := ca_id;
412 l_setDocsRec.ca_doc_id1 := ca_doc_id1;
413 l_setDocsRec.ca_doc_id2 := ca_doc_id2;
414 l_setDocsRec.ca_doc_id3 := ca_doc_id3;
415 l_setDocsRec.ca_doc_id4 := ca_doc_id4;
416 l_setDocsRec.ca_doc_id5 := ca_doc_id5;
417 l_setDocsRec.pp_tt_cd := pp_tt_cd;
418 l_setDocsRec.status := DOC_STATUS_MISSING_ACC;
419
420 print_debuginfo(l_module_name, 'Internal bank account '
421 || 'could not be defaulted for document: '
422 || doc_id
423 || '. This is because the Cash Management API did '
424 || 'not return a unique bank account for org '
428 || '.'
425 || doc_org_id
426 || ' and currency '
427 || doc_pay_currency
429 );
430
431 END IF;
432
433 END IF;
434
435 /*
436 * Step 2B:
437 * Try to default the payment profile for
438 * the document.
439 */
440 IF (doc_profile_id = -1) THEN
441
442 /* Initialize */
443 l_profile_id := -1;
444
445 /*
446 * ATTEMPT I:
447 * Attempt to get the default payment format from the
448 * payee on the document.
449 *
450 * If there is only one profile linked to this format,
451 * then default this profile for the document.
452 */
453 IF (doc_payee_id <> -1) THEN
454
455 l_profile_id := getProfileFromPayeeFormat(doc_payee_id);
456
457 print_debuginfo(l_module_name, 'Derived profile id: '
458 || l_profile_id
459 || ' from payee format '
460 || ' for document '
461 || doc_id
462 );
463
464 END IF;
465
466 IF (l_profile_id = -1) THEN
467
468 /*
469 * ATTEMPT II:
470 * Attempt to derive the payment profile from the
471 * document's payment method and other attributes.
472 * If only one payment profile exists for the given
473 * set of attributes, then we can default
474 * this profile to the document.
475 */
476 l_profile_id := getProfileFromProfileDrivers(doc_pmt_method,
477 doc_org_id, doc_org_type,
478 doc_pay_currency, doc_int_bank_acct_id
479 );
480
481 print_debuginfo(l_module_name, 'Derived profile id: '
482 || l_profile_id
483 || ' from payment method id '
484 || doc_pmt_method
485 || ' and payment format id '
486 || doc_pmt_format
487 || ' for document '
488 || doc_id
489 );
490
491 END IF;
492
493 /*
494 * Profile id of '-1' means that either no payment
495 * profile was mapped to this document's (method,
496 * org, currency, int bank account), or more than
497 * one payment profile was mapped to this payment method.
498 *
499 * If that is the case, we cannot default.
500 */
501 IF (l_profile_id <> -1) THEN
502
503 l_setDocsRec.doc_id := doc_id;
504 l_setDocsRec.ca_id := ca_id;
505 l_setDocsRec.ca_doc_id1 := ca_doc_id1;
506 l_setDocsRec.ca_doc_id2 := ca_doc_id2;
507 l_setDocsRec.ca_doc_id3 := ca_doc_id3;
508 l_setDocsRec.ca_doc_id4 := ca_doc_id4;
509 l_setDocsRec.ca_doc_id5 := ca_doc_id5;
510 l_setDocsRec.pp_tt_cd := pp_tt_cd;
511 l_setDocsRec.pay_profile_id := l_profile_id;
512 l_prof_default_flag := true;
513
514 ELSE
515
516 l_setDocsRec.doc_id := doc_id;
517 l_setDocsRec.ca_id := ca_id;
518 l_setDocsRec.ca_doc_id1 := ca_doc_id1;
519 l_setDocsRec.ca_doc_id2 := ca_doc_id2;
520 l_setDocsRec.ca_doc_id3 := ca_doc_id3;
521 l_setDocsRec.ca_doc_id4 := ca_doc_id4;
522 l_setDocsRec.ca_doc_id5 := ca_doc_id5;
523 l_setDocsRec.pp_tt_cd := pp_tt_cd;
524
525 /*
526 * If we come here, it means that the document is
527 * missing a profile.
528 *
529 * If the document is already missing an internal
530 * bank account as well, set the status of the document
531 * to reflect that it is missing both account and profile.
532 */
533 IF (l_setDocsRec.status = DOC_STATUS_MISSING_ACC) THEN
534
535 l_setDocsRec.status := DOC_STATUS_MISSING_BOTH;
536
537 ELSE
538
539 l_setDocsRec.status := DOC_STATUS_MISSING_PROF;
540
541 END IF;
542
543 print_debuginfo(l_module_name, 'Payment profile could not '
544 || 'be derived for document id: '
545 || doc_id
546 );
547
548 END IF;
549
550 END IF;
551
552 /*
553 * 'READY_FOR_VALIDATION' status means the document has
554 * both bank account and profile. This can happen in
555 * 3 situations:
556 *
557 * 1. We were able to default both the bank account and
558 * payment profile for this document.
559 *
563 * 3. We were able to default profile and bank account was
560 * 2. We were able to default bank account and profile was
561 * already available.
562 *
564 * already available.
565 *
566 * If either of these is missing, then the document status
567 * would have been set to 'MISSING_ACCOUNT' | 'MISSING_PROFILE'
568 * earlier.
569 *
570 * [The situation of both account and profile being already
571 * available would not occur because our query would not
572 * have picked those documents up.]
573 */
574 IF ((l_acct_default_flag = true AND l_prof_default_flag = true) OR
575 (l_acct_default_flag = true AND doc_profile_id <> -1) OR
576 (l_prof_default_flag = true AND doc_int_bank_acct_id <> -1)) THEN
577
578 l_setDocsRec.status := DOC_STATUS_FULL_ASSIGNED;
579
580 END IF;
581
582 /*
583 * Add this record to the PLSQL table. We will update the
584 * PLSQL table outside this loop when all documents have
585 * been processed.
586 */
587 l_setDocsTab(l_setDocsTab.COUNT + 1) := l_setDocsRec;
588
589 /*
590 * Reset flags before going into next iteration
591 */
592 l_acct_default_flag := false;
593 l_prof_default_flag := false;
594
595 END LOOP; -- for documents cursor
596
597 EXIT WHEN c_document_attribs%NOTFOUND;
598
599 END LOOP; -- for documents cursor
600
601 /*
602 * If no documents were provided with the payment request,
603 * there is no point in going further.
604 *
605 * A pament request with no documents payable is an invalid
606 * request. Return error response.
607 */
608 IF (l_no_rec_in_ppr) THEN
609
610 print_debuginfo(l_module_name, 'Found no documents with incomplete '
611 || 'assignments for payment request: '
612 || p_payment_request_id
613 || '. Finalizing status of docs and request and exiting.. ');
614
615 /*
616 * Update document and request statuses.
617 */
618 finalizeStatuses(p_payment_request_id, x_return_status);
619
620 print_debuginfo(l_module_name, 'EXIT');
621 RETURN;
622
623 END IF;
624
625 iby_disburse_submit_pub_pkg.delete_docspayTab;
626
627 CLOSE c_document_attribs;
628
629
630 /*
631 * Update the bank account and/or profile for the
632 * documents for which we were able to come up with
633 * defaults.
634 */
635 setDocumentAssignments(l_setDocsTab);
636
637 /*
638 * Update the payment request status. This depends upon whether
639 * all documents in the request have their bank account and profile
640 * assigned or not.
641 *
642 * Internally, this function will call a hook to access an external
643 * application if all documents have not been completely assigned
644 * their bank account / profile.
645 */
646 updateRequestStatus(p_payment_request_id, x_return_status);
647
648 /*
649 * Finally, raise a business event to inform the calling
650 * app of any documents with missing account/profile.
651 */
652 raiseBizEvents(p_payment_request_id, req_ca_payreq_id, req_ca_id);
653
654 print_debuginfo(l_module_name, 'EXIT');
655
656 END performAssignments;
657
658 /*--------------------------------------------------------------------
659 | NAME:
660 | getRequestAttributes
661 |
662 | PURPOSE:
663 | Returns the calling app pay service request id, the internal
664 | bank account associated with the request (if any), the payment
665 | profile associated with the request (if any) and other
666 | identifying information for the given payment request.
667 |
668 | PARAMETERS:
669 | IN
670 |
671 | OUT
672 |
673 |
674 | RETURNS:
675 |
676 | NOTES:
677 |
678 *---------------------------------------------------------------------*/
679 PROCEDURE getRequestAttributes(
680 p_payReqId IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
681 x_caPayReqCd IN OUT NOCOPY
682 IBY_PAY_SERVICE_REQUESTS.call_app_pay_service_req_code%TYPE,
683 x_caId IN OUT NOCOPY
684 IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
685 x_bankAcctId IN OUT NOCOPY
686 IBY_PAY_SERVICE_REQUESTS.internal_bank_account_id%TYPE,
687 x_profileId IN OUT NOCOPY
688 IBY_PAY_SERVICE_REQUESTS.payment_profile_id%TYPE
689 )
690 IS
691 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getRequestAttributes';
692 BEGIN
693
694 SELECT call_app_pay_service_req_code,
695 calling_app_id,
696 internal_bank_account_id, -- Internal bank account ID
697 payment_profile_id -- Payment profile ID
698 INTO x_caPayReqCd,
699 x_caId,
700 x_bankAcctId,
704
701 x_profileId
702 FROM IBY_PAY_SERVICE_REQUESTS
703 WHERE payment_service_request_id = p_payReqId;
705 END getRequestAttributes;
706
707 /*--------------------------------------------------------------------
708 | NAME:
709 | updateDocumentAssignments
710 |
711 | PURPOSE:
712 | Updates the account/profile attributes of documents in the
713 | payment request using information from the given PLSQL table.
714 |
715 | PARAMETERS:
716 | IN
717 |
718 | OUT
719 |
720 |
721 | RETURNS:
722 |
723 | NOTES:
724 |
725 *---------------------------------------------------------------------*/
726 PROCEDURE updateDocumentAssignments(
727 p_updateDocsRec IN IBY_ASSIGN_PUB.updateDocAttributesRec
728 )
729 IS
730 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
731 '.updateDocumentAssignments';
732
733 BEGIN
734
735 print_debuginfo(l_module_name, 'ENTER');
736
737 /* Check if account needs to be updated */
738 IF (p_updateDocsRec.bank_acct_flag = true) THEN
739
740 print_debuginfo(l_module_name, 'Internal bank account '
741 || 'will be set to '
742 || p_updateDocsRec.int_bank_acct_id
743 || ' for all documents of payment request '
744 || p_updateDocsRec.payment_request_id
745 );
746
747 END IF;
748
749 /* Check if profile needs to be updated */
750 IF (p_updateDocsRec.pay_profile_flag = true) THEN
751
752 print_debuginfo(l_module_name, 'Payment profile '
753 || 'will be set to '
754 || p_updateDocsRec.pay_profile_id
755 || ' for all documents of payment request '
756 || p_updateDocsRec.payment_request_id
757 );
758
759 END IF;
760
761 /*
762 * There are three possible situations:
763 * 1. Both the account and profile need to be updated
764 * 2. Only the account needs to be updated
765 * 3. Only the profile needs to be updated
766 *
767 * All of these situations will be handled by the SQL
768 * string below.
769 */
770
771 UPDATE
772 IBY_DOCS_PAYABLE_ALL
773 SET
774 internal_bank_account_id =
775 NVL(
776 p_updateDocsRec.int_bank_acct_id,
777 internal_bank_account_id
778 ),
779 payment_profile_id =
780 NVL(
781 p_updateDocsRec.pay_profile_id,
782 payment_profile_id
783 )
784 WHERE
785 payment_service_request_id = p_updateDocsRec.payment_request_id
786 ;
787
788 print_debuginfo(l_module_name, 'EXIT');
789
790 END updateDocumentAssignments;
791
792
793 /*--------------------------------------------------------------------
794 | NAME:
795 | finalizeStatuses
796 |
797 | PURPOSE:
798 | Updates the statuses of the documents and the payment request.
799 |
800 |
801 | PARAMETERS:
802 | IN
803 |
804 | OUT
805 |
806 |
807 | RETURNS:
808 |
809 | NOTES:
810 |
811 *---------------------------------------------------------------------*/
812 PROCEDURE finalizeStatuses(
813 p_payReqID IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
814 x_req_status IN OUT NOCOPY VARCHAR2
815 )
816 IS
817
818 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.finalizeStatuses';
819
820 BEGIN
821
822 print_debuginfo(l_module_name, 'ENTER');
823
824 /* Update document statuses */
825 UPDATE
826 IBY_DOCS_PAYABLE_ALL
827 SET
828 document_status = DOC_STATUS_FULL_ASSIGNED
829 WHERE
830 payment_service_request_id = p_payReqID
831 ;
832
833 /* Update payment request statuse */
834 UPDATE
835 IBY_PAY_SERVICE_REQUESTS
836 SET
837 payment_service_request_status = REQ_STATUS_FULL_ASSIGNED
838 WHERE
839 payment_service_request_id = p_payReqID
840 ;
841
842 /* Pass back the request status to the caller */
843 x_req_status := REQ_STATUS_FULL_ASSIGNED;
844
845 print_debuginfo(l_module_name, 'EXIT');
846
847 END finalizeStatuses;
848
849
850 /*--------------------------------------------------------------------
851 | NAME:
852 | dummyCEAPI
853 |
854 | PURPOSE:
855 | Returns a dummy bank account as default for the given
856 | (Org, Pmt Date, Currency) combination. This method simulates
857 | the API provided by CE and is meant for testing purposes.
858 |
859 | PARAMETERS:
860 | IN
861 |
862 | OUT
863 |
864 |
865 | RETURNS:
866 |
867 | NOTES:
868 |
872 p_pmtDate IN IBY_DOCS_PAYABLE_ALL.payment_date%TYPE,
869 *---------------------------------------------------------------------*/
870 PROCEDURE dummyCEAPI(
871 p_payCurrency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
873 p_OrgID IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
874 x_bankAccounts IN OUT NOCOPY IBY_ASSIGN_PUB.bankAccounts
875 )
876 IS
877
878 BEGIN
879
880 x_bankAccounts(x_bankAccounts.COUNT+1) := 9831508;
881
882 END dummyCEAPI;
883
884
885 /*--------------------------------------------------------------------
886 | NAME:
887 | setDocumentAssignments
888 |
889 | PURPOSE:
890 | Updates the account/profile attributes of individual documents
891 | using information from the given PLSQL table.
892 |
893 | PARAMETERS:
894 | IN
895 |
896 | OUT
897 |
898 |
899 | RETURNS:
900 |
901 | NOTES:
902 |
903 *---------------------------------------------------------------------*/
904 PROCEDURE setDocumentAssignments(
905 p_setDocAttribsTab IN IBY_ASSIGN_PUB.setDocAttribsTabType
906 )
907 IS
908
909 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
910 '.setDocumentAssignments';
911
912 l_update_acct VARCHAR2(1000);
913 l_update_prof VARCHAR2(1000);
914 l_acct_flag BOOLEAN := false;
915 l_prof_flag BOOLEAN := false;
916
917 l_update_str VARCHAR2(2000);
918 l_status_str VARCHAR2(1000);
919 l_sql_str VARCHAR2(4000);
920
921 BEGIN
922
923 print_debuginfo(l_module_name, 'ENTER');
924
925 /*
926 * Exit if no records were provided to update.
927 *
928 */
929 IF (p_setDocAttribsTab.COUNT = 0) THEN
930 print_debuginfo(l_module_name, 'No records were provided. Exiting ..');
931 RETURN;
932 END IF;
933
934 /*
935 * Loop through all the records, updating one document
936 * per iteration.
937 */
938 FOR i in p_setDocAttribsTab.FIRST..p_setDocAttribsTab.LAST LOOP
939
940 print_debuginfo(l_module_name, 'Setting attributes for document: '
941 || p_setDocAttribsTab(i).doc_id
942 );
943
944 /* Reset flags before going into next iteration */
945 l_acct_flag := false;
946 l_prof_flag := false;
947 l_update_acct := '';
948 l_update_prof := '';
949
950 /* bank account */
951 IF (p_setDocAttribsTab(i).int_bank_acct_id IS NOT NULL) THEN
952
953 l_acct_flag := true;
954 l_update_acct := 'internal_bank_account_id = '
955 || p_setDocAttribsTab(i).int_bank_acct_id;
956
957 END IF;
958
959 /* payment profile */
960 IF (p_setDocAttribsTab(i).pay_profile_id IS NOT NULL) THEN
961
962 l_prof_flag := true;
963 l_update_prof := 'payment_profile_id = '
964 || p_setDocAttribsTab(i).pay_profile_id;
965
966 END IF;
967
968 /* status */
969 /*
970 * Note: Using binding instead of concatenation for status
971 * value because the adding quotes to status string is ugly.
972 */
973
974 /*
975 * Fix for bug 4405981:
976 *
977 * Update the straight through flag whenever a
978 * document is missing internal bank account / profile
979 * or both.
980 */
981 l_status_str := 'document_status = :status, '
982 || 'straight_through_flag = :flag';
983
984 /*
985 * There are four possible situations:
986 * 1. Both the account and profile need to be updated
987 * 2. Only the account needs to be updated
988 * 3. Only the profile needs to be updated
989 * 4. Neither account nor profile needs to be updated
990 *
991 * Depending upon the situation, form the appropriate
992 * SQL string.
993 */
994 IF (l_acct_flag = true AND l_prof_flag = true) THEN
995
996 print_debuginfo(l_module_name, 'Updating both account and '
997 || 'profile');
998 /*
999 * Status will be READY_FOR_VALIDATION as
1000 * both account and profile are available.
1001 */
1002 l_update_str := l_update_acct
1003 || ', '
1004 || l_update_prof
1005 || ', '
1006 || l_status_str
1007 ;
1008
1009 ELSIF (l_acct_flag = true) THEN
1010
1011 print_debuginfo(l_module_name, 'Only updating account');
1012 l_update_str := l_update_acct || ', ' || l_status_str;
1013
1014 ELSIF (l_prof_flag = true) THEN
1015
1016 print_debuginfo(l_module_name, 'Only updating profile');
1020
1017 l_update_str := l_update_prof || ', ' || l_status_str;
1018
1019 ELSE
1021 /*
1022 * If we reached here it means that either internal
1023 * bank account, or profile or both could not be
1024 * defaulted for the document.
1025 *
1026 * Therefore, update the document status appropriately.
1027 */
1028
1029 print_debuginfo(l_module_name, 'Updating status of doc '
1030 || p_setDocAttribsTab(i).doc_id
1031 || ' to '
1032 || p_setDocAttribsTab(i).status
1033 );
1034
1035 UPDATE
1036 IBY_DOCS_PAYABLE_ALL
1037 SET
1038 document_status = p_setDocAttribsTab(i).status,
1039 straight_through_flag = 'N'
1040 WHERE
1041 document_payable_id = p_setDocAttribsTab(i).doc_id
1042 ;
1043
1044
1045 GOTO label_finish_iteration;
1046
1047 END IF;
1048
1049 /*
1050 * Form the complete SQL statement.
1051 */
1052 l_sql_str := 'UPDATE IBY_DOCS_PAYABLE_ALL SET '
1053 || l_update_str
1054 || ' WHERE document_payable_id = '
1055 || p_setDocAttribsTab(i).doc_id;
1056
1057 print_debuginfo(l_module_name, 'SQL str: ' || l_sql_str);
1058 print_debuginfo(l_module_name, 'Setting status to: '
1059 || p_setDocAttribsTab(i).status);
1060
1061 /*
1062 * Dynamic SQL update
1063 */
1064 EXECUTE IMMEDIATE
1065 l_sql_str
1066 USING
1067 p_setDocAttribsTab(i).status, /* document status */
1068 'N' /* straight through flag */
1069 ;
1070
1071
1072 <<label_finish_iteration>>
1073
1074 /*
1075 * This null is needed because the PLSQL compiler needs
1076 * a statement after the GOTO label.
1077 */
1078 NULL;
1079
1080 END LOOP;
1081
1082 print_debuginfo(l_module_name, 'EXIT');
1083
1084 END setDocumentAssignments;
1085
1086 /*--------------------------------------------------------------------
1087 | NAME:
1088 | getProfileFromProfileDrivers
1089 |
1090 | PURPOSE:
1091 | Derives the payment profile for the given (payment method,
1092 | org, payment currency, internal bank acct)
1093 | combination.
1094 |
1095 | This method will only be able to derive the profile from
1096 | this combination if there is only one payment profile
1097 | uniquely associated with the given (payment method,
1098 | org, currency, bank acct). If multiple profiles match, or no
1099 | profiles matches -1 will be returned.
1100 |
1101 | PARAMETERS:
1102 | IN
1103 |
1104 | OUT
1105 |
1106 |
1107 | RETURNS:
1108 |
1109 | -1 signifies that no profile could be derived from the given
1110 | (method, org, currency, bank acct) combination.
1111 |
1112 | NOTES:
1113 |
1114 *---------------------------------------------------------------------*/
1115 FUNCTION getProfileFromProfileDrivers(
1116 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
1117 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
1118 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
1119 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
1120 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE
1121 ) RETURN NUMBER
1122 IS
1123 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1124 '.getProfileFromProfileDrivers';
1125
1126 l_profile_id NUMBER;
1127 l_profiles_tab IBY_BUILD_UTILS_PKG.pmtProfTabType;
1128
1129 BEGIN
1130
1131 print_debuginfo(l_module_name, 'ENTER');
1132
1133 print_debuginfo(l_module_name, 'Checking for profiles '
1134 || 'applicable for given org id '
1135 || p_org_id
1136 || ' and org type '
1137 || p_org_type
1138 || ' and payment method '
1139 || p_pmt_method_cd
1140 || ' and payment currency '
1141 || p_pmt_currency
1142 || ' and internal bank account '
1143 || p_int_bank_acct_id
1144 || ' combination ...'
1145 );
1146
1147 /*
1148 * Get the list of all payment profiles that
1149 * match the given list of profile drivers.
1150 */
1151 IBY_BUILD_UTILS_PKG.getProfListFromProfileDrivers(
1152 p_pmt_method_cd,
1153 p_org_id,
1154 p_org_type,
1155 p_pmt_currency,
1156 p_int_bank_acct_id,
1157 l_profiles_tab);
1158
1159 /*
1160 * If count is exactly one, it means that there is
1161 * a profile that uniquely matches the given set of
1165 * If count is 0 or more than 1, it means that we
1162 * drivers. This profile can be defaulted to the
1163 * document. Return this profile to the caller.
1164 *
1166 * cannot default a profile from the given set of
1167 * drivers. In this case, return -1.
1168 */
1169 IF (l_profiles_tab.COUNT = 1) THEN
1170
1171 l_profile_id := l_profiles_tab(1).profile_id;
1172
1173 ELSE
1174
1175 l_profile_id := -1;
1176
1177 END IF;
1178
1179 RETURN l_profile_id;
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183
1184 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1185 || 'attempting to get payment profile for given (payment '
1186 || 'method, org, currency , bank acct) '
1187 || 'combination. Profile id '
1188 || 'will be returned as -1.'
1189 );
1190
1191 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1192 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1193
1194 l_profile_id := -1;
1195 print_debuginfo(l_module_name, 'ENTER');
1196
1197 RETURN l_profile_id;
1198
1199 END getProfileFromProfileDrivers;
1200
1201 /*--------------------------------------------------------------------
1202 | NAME:
1203 | updateRequestStatus
1204 |
1205 | PURPOSE:
1206 | Updates the payment request status. If all documents have an
1207 | account and a profile (either from the start, or after defaulting)
1208 | then the request status will be 'fully assigned', otherwise
1209 | the request status will be set to 'information required'.
1210 |
1211 | PARAMETERS:
1212 | IN
1213 |
1214 | OUT
1215 |
1216 |
1217 | RETURNS:
1218 |
1219 | NOTES:
1220 |
1221 *---------------------------------------------------------------------*/
1222 PROCEDURE updateRequestStatus(
1223 p_payReqID IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
1224 x_req_status IN OUT NOCOPY VARCHAR2
1225 )
1226 IS
1227
1228 l_unassgnDocsTab IBY_ASSIGN_PUB.unassignedDocsTabType;
1229 l_preHookDocsTab IBY_ASSIGN_PUB.unassignedDocsTabType;
1230 l_setDocAttribsTab IBY_ASSIGN_PUB.setDocAttribsTabType;
1231 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1232 '.updateRequestStatus';
1233 l_request_status VARCHAR2(200);
1234 l_str VARCHAR2(5000);
1235
1236 /*
1237 * Cursor to pick up all documents that do not have
1238 * a payment profile or an internal bank account
1239 * assigned.
1240 */
1241 CURSOR c_unassigned_docs(p_payment_request_id VARCHAR2)
1242 IS
1243 SELECT document_payable_id,
1244 calling_app_id, --| These seven
1245 calling_app_doc_unique_ref1, --| are used
1246 calling_app_doc_unique_ref2, --| by the
1247 calling_app_doc_unique_ref3, --| calling app
1248 calling_app_doc_unique_ref4, --| to uniquely
1249 calling_app_doc_unique_ref5, --| id a
1250 pay_proc_trxn_type_code, --| document
1251 NVL(internal_bank_account_id, -1),
1252 NVL(payment_profile_id, -1)
1253 FROM IBY_DOCS_PAYABLE_ALL
1254 WHERE payment_service_request_id = p_payment_request_id
1255 AND (internal_bank_account_id IS NULL OR
1256 payment_profile_id IS NULL)
1257 ORDER BY document_payable_id;
1258
1259 BEGIN
1260
1261 print_debuginfo(l_module_name, 'ENTER');
1262
1263 OPEN c_unassigned_docs(p_payReqID);
1264 FETCH c_unassigned_docs BULK COLLECT INTO l_unassgnDocsTab;
1265 CLOSE c_unassigned_docs;
1266
1267 print_debuginfo(l_module_name, 'Found ' || l_unassgnDocsTab.COUNT
1268 || ' documents that were not assigned payment profile'
1269 || ' and/or bank account.');
1270
1271 /*
1272 * If there are no unassigned documents, update the
1273 * status of the payment request to 'ASSIGNMENT_COMPLETE'.
1274 */
1275 IF (l_unassgnDocsTab.COUNT = 0) THEN
1276
1277 x_req_status := REQ_STATUS_FULL_ASSIGNED;
1278
1279 UPDATE
1280 IBY_PAY_SERVICE_REQUESTS
1281 SET
1282 payment_service_request_status = REQ_STATUS_FULL_ASSIGNED
1283 WHERE
1284 payment_service_request_id = p_payreqID
1285 ;
1286
1287 print_debuginfo(l_module_name, 'Payment request status updated to '
1288 || 'ASSIGNMENT_COMPLETE status.');
1289
1290 print_debuginfo(l_module_name, 'EXIT');
1291
1292 RETURN;
1293
1294 END IF;
1295
1296 /*
1297 * If we reached here, it means that there is at least
1298 * one unassigned document in the payment request.
1299 *
1300 * We need to call the hook to see if it can default
1301 * any doc assignments.
1302 */
1303
1304 /*
1305 * Copy all the data that we pass to the hook into a
1306 * backup data structure. We will diff this backup
1310 */
1307 * structure with the data struct that returns from
1308 * the hook and log all the differences as 'assignments
1309 * made by hook'.
1311 FOR i in l_unassgnDocsTab.FIRST .. l_unassgnDocsTab.LAST LOOP
1312 l_preHookDocsTab(l_preHookDocsTab.COUNT + 1) := l_unassgnDocsTab(i);
1313 END LOOP;
1314
1315 /*
1316 * CALL HOOK:
1317 *
1318 * Call a hook to access custom assignment logic. The
1319 * hook may be able supply the missing internal bank
1320 * account and/or payment profile.
1321 *
1322 * Input:
1323 * List of documents with missing assignments
1324 * Output:
1325 * List of documents with assignments
1326 */
1327 BEGIN
1328 print_debuginfo(l_module_name, 'Calling hook for custom assignments');
1329
1330 hookForAssignments(l_unassgnDocsTab);
1331
1332 --
1333 -- Uncomment for testing
1334 --
1335 --dummyAsgnHook(l_unassgnDocsTab);
1336
1337 print_debuginfo(l_module_name, 'Finished calling hook for custom '
1338 || 'assignments.');
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 print_debuginfo(l_module_name, 'Fatal: Exception occured '
1343 || 'when calling assignment hook.', FND_LOG.LEVEL_UNEXPECTED);
1344 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1345 FND_LOG.LEVEL_UNEXPECTED);
1346 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1347 FND_LOG.LEVEL_UNEXPECTED);
1348
1349 /*
1350 * Propogate exception to caller.
1351 */
1352 RAISE;
1353 END;
1354
1355 /*
1356 * Log all the hook assigned documents. We do a diff between
1357 * the data structures passed to the hook and the data
1358 * structures returning from the hook and log the
1359 * differences.
1360 */
1361 print_debuginfo(l_module_name,'List of hook assigned docs: ');
1362 FOR i in l_unassgnDocsTab.FIRST .. l_unassgnDocsTab.LAST LOOP
1363
1364 l_str := NULL;
1365
1366 IF (l_unassgnDocsTab(i).int_bank_acct_id <>
1367 l_preHookDocsTab(i).int_bank_acct_id) THEN
1368 l_str := 'Bank Acc: ' || l_unassgnDocsTab(i).int_bank_acct_id;
1369 END IF;
1370
1371 IF (l_unassgnDocsTab(i).pay_profile_id <>
1372 l_preHookDocsTab(i).pay_profile_id) THEN
1373 l_str := l_str || ' Profile: '
1374 || l_unassgnDocsTab(i).pay_profile_id;
1375 END IF;
1376
1377 IF (l_str IS NOT NULL) THEN
1378 l_str := 'Doc Id: ' || l_unassgnDocsTab(i).document_id
1379 || ' with calling app id '
1380 || l_unassgnDocsTab(i).calling_app_id
1381 || ' calling app doc id1 '
1382 || l_unassgnDocsTab(i).calling_app_doc_id1
1383 || ' calling app doc id2 '
1384 || l_unassgnDocsTab(i).calling_app_doc_id2
1385 || ' calling app doc id3 '
1386 || l_unassgnDocsTab(i).calling_app_doc_id3
1387 || ' calling app doc id4 '
1388 || l_unassgnDocsTab(i).calling_app_doc_id4
1389 || ' calling app doc id5 '
1390 || l_unassgnDocsTab(i).calling_app_doc_id5
1391 || ' pay proc txn type id '
1392 || l_unassgnDocsTab(i).pay_proc_ttype_cd
1393 || ' ' || l_str;
1394 print_debuginfo(l_module_name, l_str);
1395 END IF;
1396
1397 END LOOP;
1398
1399 /*
1400 * Copy the records from the hook data structure to
1401 * our internal data structure used for updating tables.
1402 */
1403 populateDocAttributes(l_unassgnDocsTab, l_setDocAttribsTab);
1404
1405 /*
1406 * Update the documents table with the hook provided
1407 * data.
1408 */
1409 setDocumentAssignments(l_setDocAttribsTab);
1410
1411 /*
1412 * Update the status of the payment request.
1413 */
1414 FOR i in l_unassgnDocsTab.FIRST .. l_unassgnDocsTab.LAST LOOP
1415
1416 IF (l_unassgnDocsTab(i).int_bank_acct_id = -1 OR
1417 l_unassgnDocsTab(i).pay_profile_id = -1) THEN
1418
1419 /*
1420 * At least one document in the request does not
1421 * have an assigned bank account / profile.
1422 */
1423 print_debuginfo(l_module_name, 'Unassigned documents '
1424 || 'exist for this payment request.');
1425
1426 l_request_status := REQ_STATUS_INFO_REQD;
1427 GOTO label_update_request_status;
1428
1429 END IF;
1430
1431 END LOOP;
1432
1433 /*
1434 * Reaching here means all the documents have been
1435 * assigned a status by the external application.
1436 */
1437 l_request_status := REQ_STATUS_FULL_ASSIGNED;
1438
1439 <<label_update_request_status>>
1440
1441 /*
1442 * Update the payment request status appropriately.
1443 */
1444 UPDATE
1445 IBY_PAY_SERVICE_REQUESTS
1446 SET
1450 ;
1447 payment_service_request_status = l_request_status
1448 WHERE
1449 payment_service_request_id = p_payreqID
1451
1452 print_debuginfo(l_module_name, 'Payment request status updated to '
1453 || l_request_status || ' status.');
1454
1455 /* Pass back the request status to the caller */
1456 x_req_status := l_request_status;
1457
1458 print_debuginfo(l_module_name, 'EXIT');
1459
1460 EXCEPTION
1461 WHEN OTHERS THEN
1462 print_debuginfo(l_module_name, 'Fatal: Exception when '
1463 || 'attempting to update request status.',
1464 FND_LOG.LEVEL_UNEXPECTED);
1465 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1466 FND_LOG.LEVEL_UNEXPECTED);
1467 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1468 FND_LOG.LEVEL_UNEXPECTED);
1469
1470 /*
1471 * Propogate exception to caller.
1472 */
1473 RAISE;
1474
1475 END updateRequestStatus;
1476
1477 /*--------------------------------------------------------------------
1478 | NAME:
1479 | hookForAssignments
1480 |
1481 | PURPOSE:
1482 | The assignment flow will call this hook with a list of
1483 | unassigned documents (documents that do not have account/
1484 | profile even after the assignment flow attempted to default
1485 | them).
1486 |
1487 | The customer can implement custom assignment logic here to
1488 | assign defaults to the unassigned documents. The assignment
1489 | flow will use these custom assignments to update the individual
1490 | documents.
1491 |
1492 | This hook will ship with an empty body from Oracle. The customer
1493 | may implement this hook on their environment if they wish.
1494 |
1495 | PARAMETERS:
1496 | IN
1497 |
1498 | OUT
1499 |
1500 |
1501 | RETURNS:
1502 |
1503 | NOTES:
1504 |
1505 *---------------------------------------------------------------------*/
1506 PROCEDURE hookForAssignments(
1507 x_unassgnDocsTab IN OUT NOCOPY IBY_ASSIGN_PUB.unassignedDocsTabType
1508 )
1509 IS
1510 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.hookForAssignments';
1511 BEGIN
1512
1513 print_debuginfo(l_module_name, 'ENTER');
1514
1515 /*
1516 * This hook will ship out-of-the-box with an empty
1517 * body.
1518 *
1519 * The customer can add custom assignment logic this
1520 * hook to perform. The implementation and maintainence
1521 * of this hook has to be performed on site by the
1522 * customer.
1523 */
1524 NULL;
1525
1526 print_debuginfo(l_module_name, 'EXIT');
1527
1528 END hookForAssignments;
1529
1530 /*--------------------------------------------------------------------
1531 | NAME:
1532 | dummyAsgnHook
1533 |
1534 | PURPOSE:
1535 | Assigns a dummy internal bank account and dummy payment profile
1536 | to the given documents. To be used for testing purposes.
1537 |
1538 | PARAMETERS:
1539 | IN
1540 |
1541 | OUT
1542 |
1543 |
1544 | RETURNS:
1545 |
1546 | NOTES:
1547 |
1548 *---------------------------------------------------------------------*/
1549 PROCEDURE dummyAsgnHook(
1550 x_unassgnDocsTab IN OUT NOCOPY IBY_ASSIGN_PUB.unassignedDocsTabType
1551 )
1552 IS
1553 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.dummyAsgnHook';
1554 BEGIN
1555
1556 print_debuginfo(l_module_name, 'ENTER');
1557
1558 FOR i in x_unassgnDocsTab.FIRST .. x_unassgnDocsTab.LAST LOOP
1559 x_unassgnDocsTab(i).int_bank_acct_id := 746051;
1560 x_unassgnDocsTab(i).pay_profile_id := 10;
1561 END LOOP;
1562
1563 print_debuginfo(l_module_name, 'EXIT');
1564
1565 END dummyAsgnHook;
1566
1567 /*--------------------------------------------------------------------
1568 | NAME:
1569 | populateDocAttributes
1570 |
1571 | PURPOSE:
1572 | Reads the account/profile assignments provided by the hook
1573 | and assign them to the documents PLSQL table. This documents
1574 | PLSQL table will later be used in updating the documents
1575 | in the database.
1576 |
1577 | PARAMETERS:
1578 | IN
1579 |
1580 | OUT
1581 |
1582 |
1583 | RETURNS:
1584 |
1585 | NOTES:
1586 |
1587 *---------------------------------------------------------------------*/
1588 PROCEDURE populateDocAttributes(
1589 p_hookAsgnDocsTab IN IBY_ASSIGN_PUB.unassignedDocsTabType,
1590 x_setDocAttribsTab IN OUT NOCOPY IBY_ASSIGN_PUB.setDocAttribsTabType
1591 )
1592 IS
1593 l_docAttrsRec IBY_ASSIGN_PUB.setDocAttributesRec;
1594 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.populateDocAttributes';
1595 BEGIN
1596
1597 print_debuginfo(l_module_name, 'ENTER');
1598
1599 /*
1600 * Loop through the hook assigned docs, and populate the
1601 * internal bank account / payment profile, whenever provided,
1602 * into the document attributes table.
1603 *
1604 * This document attributes table will be used to update
1608
1605 * the DB with the hook provided assignments.
1606 */
1607 FOR i in p_hookAsgnDocsTab.FIRST .. p_hookAsgnDocsTab.LAST LOOP
1609 /* document id */
1610 l_docAttrsRec.doc_id := p_hookAsgnDocsTab(i).document_id;
1611
1612 /* caller assigned doc id */
1613 l_docAttrsRec.ca_id := p_hookAsgnDocsTab(i).calling_app_id;
1614 l_docAttrsRec.ca_doc_id1 := p_hookAsgnDocsTab(i).calling_app_doc_id1;
1615 l_docAttrsRec.ca_doc_id2 := p_hookAsgnDocsTab(i).calling_app_doc_id2;
1616 l_docAttrsRec.ca_doc_id3 := p_hookAsgnDocsTab(i).calling_app_doc_id3;
1617 l_docAttrsRec.ca_doc_id4 := p_hookAsgnDocsTab(i).calling_app_doc_id4;
1618 l_docAttrsRec.ca_doc_id5 := p_hookAsgnDocsTab(i).calling_app_doc_id5;
1619 l_docAttrsRec.pp_tt_cd := p_hookAsgnDocsTab(i).pay_proc_ttype_cd;
1620
1621 /* internal bank account */
1622 IF (p_hookAsgnDocsTab(i).int_bank_acct_id <> -1) THEN
1623 l_docAttrsRec.int_bank_acct_id :=
1624 p_hookAsgnDocsTab(i).int_bank_acct_id;
1625 ELSE
1626 l_docAttrsRec.status := DOC_STATUS_MISSING_ACC;
1627 END IF;
1628
1629 /* payment profile */
1630 IF (p_hookAsgnDocsTab(i).pay_profile_id <> -1) THEN
1631
1632 l_docAttrsRec.pay_profile_id :=
1633 p_hookAsgnDocsTab(i).pay_profile_id;
1634
1635 ELSE
1636
1637 /*
1638 * If internal bank account is already missing for
1639 * this document, then both int bank account and
1640 * profile are missing.
1641 *
1642 * Set document status to reflect this.
1643 */
1644 IF (l_docAttrsRec.status = DOC_STATUS_MISSING_ACC) THEN
1645
1646 l_docAttrsRec.status := DOC_STATUS_MISSING_BOTH;
1647
1648 ELSE
1649
1650 l_docAttrsRec.status := DOC_STATUS_MISSING_PROF;
1651
1652 END IF;
1653
1654 END IF;
1655
1656 /* if both attributes are available, update the status */
1657 IF (l_docAttrsRec.int_bank_acct_id IS NOT NULL AND
1658 l_docAttrsRec.pay_profile_id IS NOT NULL) THEN
1659
1660 l_docAttrsRec.status := DOC_STATUS_FULL_ASSIGNED;
1661
1662 END IF;
1663
1664 /* add record with doc attributes to table */
1665 x_setDocAttribsTab(x_setDocAttribsTab.COUNT + 1) := l_docAttrsRec;
1666
1667 END LOOP;
1668
1669 print_debuginfo(l_module_name, 'EXIT');
1670
1671 END populateDocAttributes;
1672
1673 /*--------------------------------------------------------------------
1674 | NAME:
1675 | raiseBizEvents
1676 |
1677 | PURPOSE:
1678 | Raises business events (when necessary) to signal to the
1679 | external application that some documents in the payment
1680 | request contain documents with missing account/profile.
1681 |
1682 | PARAMETERS:
1683 | IN
1684 |
1685 | OUT
1686 |
1687 |
1688 | RETURNS:
1689 |
1690 | NOTES:
1691 |
1692 *---------------------------------------------------------------------*/
1693 PROCEDURE raiseBizEvents(
1694 p_payreq_id IN VARCHAR2,
1695 p_cap_payreq_id IN VARCHAR2,
1696 p_cap_id IN NUMBER
1697 )
1698 IS
1699 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.raiseBizEvents';
1700 l_xml_clob CLOB;
1701 l_event_name VARCHAR2(200);
1702 l_event_key NUMBER;
1703 l_param_names JTF_VARCHAR2_TABLE_300;
1704 l_param_vals JTF_VARCHAR2_TABLE_300;
1705
1706 BEGIN
1707
1708 print_debuginfo(l_module_name, 'ENTER');
1709 print_debuginfo(l_module_name, 'Payreq id: '
1710 || p_payreq_id);
1711
1712 /*
1713 * These tables are used to pass event keys
1714 * to the business event.
1715 */
1716 l_param_names := JTF_VARCHAR2_TABLE_300();
1717 l_param_vals := JTF_VARCHAR2_TABLE_300();
1718
1719 /*
1720 * The event key uniquely identifies a specific
1721 * occurance of an event. Therefore, it should be
1722 * a sequence number.
1723 */
1724 SELECT IBY_EVENT_KEY_S.nextval INTO l_event_key
1725 FROM DUAL;
1726
1727 /*
1728 * Raise a business event with the list of documents
1729 * that have either internal bank account, or payment
1730 * profile, or both, missing. This business event should
1731 * trigger the calling app to launch a worfkflow
1732 * to enable the user to assign the missing information
1733 * to these docs.
1734 */
1735 l_event_name :=
1736 'oracle.apps.iby.buildprogram.validation.notify_incomplete_docs';
1737
1738 print_debuginfo(l_module_name, 'Going to raise biz event '
1739 || l_event_name);
1740
1741 /*
1742 * Select all docs that:
1743 * 1. Have the given pay req id
1744 * 2. Are missing either account or profile (or both)
1745 *
1746 * And create an XML fragment with these documents.
1747 */
1748
1749 l_xml_clob := getXMLClob(p_payreq_id);
1750
1751 IF (l_xml_clob IS NULL) THEN
1752
1753 print_debuginfo(l_module_name, 'Not raising biz event '
1757 ELSE
1754 || l_event_name || ' because all documents were '
1755 || ' fully assigned. So no documents to notify.'
1756 );
1758
1759 print_debuginfo(l_module_name, 'Going to raise biz event '
1760 || l_event_name);
1761
1762 IBY_BUILD_UTILS_PKG.printXMLClob(l_xml_clob);
1763
1764 l_param_names.EXTEND;
1765 l_param_vals.EXTEND;
1766 l_param_names(1) := 'calling_app_id';
1767 l_param_vals(1) := p_cap_id;
1768
1769 l_param_names.EXTEND;
1770 l_param_vals.EXTEND;
1771 l_param_names(1) := 'pay_service_request_id';
1772 l_param_vals(1) := p_cap_payreq_id;
1773
1774 iby_workflow_pvt.raise_biz_event(l_event_name, l_event_key,
1775 l_param_names, l_param_vals);
1776
1777 print_debuginfo(l_module_name, 'Raised biz event '
1778 || l_event_name || ' with key '
1779 || l_event_key || '.');
1780
1781 END IF;
1782
1783 print_debuginfo(l_module_name, 'EXIT');
1784
1785 END raiseBizEvents;
1786
1787 /*--------------------------------------------------------------------
1788 | NAME:
1789 | getXMLClob
1790 |
1791 | PURPOSE:
1792 | Returns an XML clob that contains a list of documents that
1793 | are missing account/profile.
1794 |
1795 | PARAMETERS:
1796 | IN
1797 |
1798 | OUT
1799 |
1800 |
1801 | RETURNS:
1802 |
1803 | NOTES:
1804 |
1805 *---------------------------------------------------------------------*/
1806 FUNCTION getXMLClob(
1807 p_payreq_id IN VARCHAR2
1808 )
1809 RETURN CLOB
1810 IS
1811 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getXMLClob';
1812 l_xml_clob CLOB := NULL;
1813
1814 l_ctx DBMS_XMLQuery.ctxType;
1815 l_sql VARCHAR2(2000);
1816 l_sqlcode NUMBER;
1817 l_sqlerrm VARCHAR2(300);
1818
1819 BEGIN
1820
1821 print_debuginfo(l_module_name, 'ENTER');
1822
1823 /*
1824 * Select all docs that:
1825 * 1. Have the given pay req id
1826 * 2. Are missing either account or profile (or both)
1827 */
1828 l_sql := 'SELECT calling_app_id, '
1829 || 'calling_app_doc_unique_ref1, '
1830 || 'calling_app_doc_unique_ref2, '
1831 || 'calling_app_doc_unique_ref3, '
1832 || 'calling_app_doc_unique_ref4, '
1833 || 'calling_app_doc_unique_ref5, '
1834 || 'pay_proc_trxn_type_code, '
1835 || 'internal_bank_account_id, '
1836 || 'payment_profile_id '
1837 || 'FROM IBY_DOCS_PAYABLE_ALL '
1838 || 'WHERE payment_service_request_id = :payreq_id '
1839 || 'AND (internal_bank_account_id IS NULL '
1840 || 'OR payment_profile_id IS NULL)';
1841
1842 l_ctx := DBMS_XMLQuery.newContext(l_sql);
1843 DBMS_XMLQuery.setBindValue(l_ctx, 'payreq_id', p_payreq_id);
1844 DBMS_XMLQuery.useNullAttributeIndicator(l_ctx, TRUE);
1845
1846 /* raise an exception if no rows were found */
1847 DBMS_XMLQuery.setRaiseException(l_ctx, TRUE);
1848 DBMS_XMLQuery.setRaiseNoRowsException(l_ctx, TRUE);
1849 DBMS_XMLQuery.propagateOriginalException(l_ctx, TRUE);
1850
1851 l_xml_clob := DBMS_XMLQuery.getXML(l_ctx);
1852 DBMS_XMLQuery.closeContext(l_ctx);
1853
1854 print_debuginfo(l_module_name, 'EXIT');
1855
1856 RETURN l_xml_clob;
1857
1858 EXCEPTION
1859
1860 WHEN OTHERS THEN
1861
1862 DBMS_XMLQuery.getExceptionContent(l_ctx, l_sqlcode, l_sqlerrm);
1863 print_debuginfo(l_module_name, 'SQL code: ' || l_sqlcode);
1864 print_debuginfo(l_module_name, 'SQL err msg: '|| l_sqlerrm);
1865
1866 /*
1867 * Do not raise exception if no rows found.
1868 * It means all documents have assignments.
1869 * Return NULL clob to caller.
1870 *
1871 * 1403 = NO_DATA_FOUND
1872 *
1873 * Note: We are unable to explicitly catch the
1874 * NO_DATA_FOUND exception here because the caller
1875 * raises some other exception. So we have to check
1876 * value of the original error code instead.
1877 */
1878 IF (l_sqlcode = 1403) THEN
1879 print_debuginfo(l_module_name, 'No rows were returned for query;'
1880 || ' Returning null xml clob.');
1881 RETURN NULL;
1882 END IF;
1883
1884 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
1885 || 'to raise business event.', FND_LOG.LEVEL_UNEXPECTED);
1886 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1887 FND_LOG.LEVEL_UNEXPECTED);
1888 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1889 FND_LOG.LEVEL_UNEXPECTED);
1890
1891 /*
1892 * Propogate exception to caller.
1893 */
1894 RAISE;
1895
1896 END getXMLClob;
1897
1898 /*--------------------------------------------------------------------
1899 | NAME:
1903 |
1900 | getProfileFromPayeeFormat
1901 |
1902 | PURPOSE:
1904 |
1905 |
1906 | PARAMETERS:
1907 | IN
1908 |
1909 |
1910 | OUT
1911 |
1912 |
1913 | RETURNS:
1914 |
1915 | NOTES:
1916 |
1917 *---------------------------------------------------------------------*/
1918 FUNCTION getProfileFromPayeeFormat(
1919 p_payee_id IN IBY_DOCS_PAYABLE_ALL.ext_payee_id%TYPE
1920 ) RETURN NUMBER
1921 IS
1922 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1923 '.getProfileFromPayeeFormat';
1924
1925 l_profile_id IBY_PAYMENT_PROFILES.payment_profile_id%TYPE := -1;
1926
1927 BEGIN
1928
1929 print_debuginfo(l_module_name, 'ENTER');
1930
1931 /*
1932 * This select will fail if is more than one row
1933 * or no rows. That's perfect because we want to find
1934 * a profile that is linked to exactly one format.
1935 */
1936 SELECT
1937 NVL(prof.payment_profile_id, -1)
1938 INTO
1939 l_profile_id
1940 FROM
1941 IBY_PAYMENT_PROFILES prof,
1942 IBY_EXTERNAL_PAYEES_ALL payee
1943 WHERE
1944 payee.ext_payee_id = ext_payee_id AND
1945 prof.payment_format_code = payee.payment_format_code
1946 ;
1947
1948 print_debuginfo(l_module_name, 'EXIT');
1949
1950 RETURN l_profile_id;
1951
1952 EXCEPTION
1953
1954 WHEN OTHERS THEN
1955
1956 /*
1957 * In case of an exception, return -1
1958 */
1959 print_debuginfo(l_module_name, 'Non-fatal: Exception thrown '
1960 || 'when attempting to get payment profile '
1961 || 'linked to format. Returning -1.'
1962 );
1963
1964 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1965 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1966
1967 l_profile_id := -1;
1968
1969 print_debuginfo(l_module_name, 'EXIT');
1970
1971 RETURN l_profile_id;
1972
1973 END getProfileFromPayeeFormat;
1974
1975 /*--------------------------------------------------------------------
1976 | NAME:
1977 | print_debuginfo
1978 |
1979 | PURPOSE:
1980 | This procedure prints the debug message to the concurrent manager
1981 | log file.
1982 |
1983 | PARAMETERS:
1984 | IN
1985 | p_debug_text - The debug message to be printed
1986 |
1987 | OUT
1988 |
1989 |
1990 | RETURNS:
1991 |
1992 | NOTES:
1993 |
1994 *---------------------------------------------------------------------*/
1995 PROCEDURE print_debuginfo(
1996 p_module IN VARCHAR2,
1997 p_debug_text IN VARCHAR2,
1998 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
1999 )
2000 IS
2001 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
2002 BEGIN
2003
2004 /*
2005 * Set the debug level to the value passed in
2006 * (provided this value is not null).
2007 */
2008 IF (p_debug_level IS NOT NULL) THEN
2009 l_default_debug_level := p_debug_level;
2010 END IF;
2011
2012 /*
2013 * Write the debug message to the concurrent manager log file.
2014 */
2015 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2016 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
2017 p_debug_level);
2018 END IF;
2019
2020 END print_debuginfo;
2021
2022
2023 END IBY_ASSIGN_PUB;
[Home] [Help]