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