1 PACKAGE BODY XTR_UPDATE_SETTLEMENT_ACCOUNTS as
2 /* $Header: xtrupacb.pls 120.3 2006/11/03 12:27:20 kbabu noship $ */
3
4 --------------------------------------
5 -- declaration of public procedures and functions
6 --------------------------------------
7
8 /**
9 * PROCEDURE process_settlement_accounts
10 *
11 * DESCRIPTION
12 * This procedure updates XTR_DEAL_DATE_AMOUNTS table and sets the
13 * company account or counterparty account specified as the
14 * account_number_from to the account specified as account_number_to.
15 *
16 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
17 *
18 * ARGUMENTS
19 * IN:
20 * p_party_code Party Code of the party for whom the
21 * accounts are to be updated.
22 * p_current_bank_account Bank Account Number from
23 * XTR_BANK_ACCOUNTS for the above party.
24 * All cashflow records that use this
25 * account are to be updated.
26 * p_new_bank_account Bank Account Number from
27 * XTR_BANK_ACCOUNTS for the above party.
28 * All cashflow records that use the
29 * above account will be updated with this
30 * account.
31 * p_start_date All records with an amount_date greater
32 * than or equal to this date will be
33 * considered for update.
34 * p_end_date All records with an amount_date lesser
35 * than or equal to this date will be
36 * considered for update.
37 * p_deal_type Only those deals with the specified
38 * Deal Type will be updated.
39 * p_deal_number_from Only those deals with a deal number
40 * greater than or equal to this
41 * parameter will be updated.
42 * p_deal_number_to Only those deals with a deal number
43 * lesser than or equal to this
44 * parameter will be updated.
45 * p_include_journalized Flag to indicate whether cashflows
46 * that have already been journalized
47 * should be updated.
48 * IN/OUT:
49 * OUT:
50 *
51 * NOTES
52 *
53 * MODIFICATION HISTORY
54 *
55 * 06-Jun-2005 Rajesh Jose o Created.
56 *
57 */
58 PROCEDURE Process_Settlement_Accounts(
59 p_error_buf OUT NOCOPY VARCHAR2,
60 p_retcode OUT NOCOPY NUMBER,
61 p_party_code IN XTR_PARTY_INFO.PARTY_CODE%TYPE,
62 p_current_bank_account IN XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
63 p_new_bank_account IN XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
64 p_start_date IN VARCHAR2,
65 p_end_date IN VARCHAR2,
66 p_deal_type IN XTR_DEALS.DEAL_TYPE%TYPE,
67 p_deal_number_from IN XTR_DEALS.DEAL_NO%TYPE,
68 p_deal_number_to IN XTR_DEALS.DEAL_NO%TYPE,
69 p_include_journalized IN
70 XTR_CFLOW_REQUEST_DETAILS.INCLUDE_JOURNALIZED_FLAG%TYPE)
71 IS
72
73 CURSOR company_list IS
74 select party_code
75 from xtr_parties_v where party_type = 'C';
76
77 CURSOR max_deal_no IS
78 select max (deal_number) from XTR_DEAL_DATE_AMOUNTS;
79
80 CURSOR comp_accts_with_deal_type (
81 p_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE,
82 p_int_deal_no_from XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
83 p_int_deal_no_to XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
84 p_date DATE,
85 p_default_end_date DATE) IS
86 SELECT deal_date_amount_id, deal_number,
87 amount_date, amount_type, cashflow_amount,
88 settle, batch_id, transaction_number, deal_type
89 FROM xtr_deal_date_amounts
90 WHERE account_no = p_current_bank_account
91 AND amount_date >= p_date
92 AND amount_date <= p_default_end_date
93 AND deal_type = p_deal_type
94 AND company_code = p_company_code
95 AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
96
97 CURSOR comp_accts_with_deal_no (
98 p_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE,
99 p_int_deal_no_from XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
100 p_int_deal_no_to XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
101 p_date DATE,
102 p_default_end_date DATE) IS
103 SELECT deal_date_amount_id, deal_number,
104 amount_date, amount_type, cashflow_amount,
105 settle, batch_id, transaction_number, deal_type
106 FROM xtr_deal_date_amounts
107 WHERE account_no = p_current_bank_account
108 AND amount_date >= p_date
109 AND amount_date <= p_default_end_date
110 AND company_code = p_company_code
111 AND deal_type NOT IN ('CA', 'IAC')
112 AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
113
114 Cursor cparty_accts_with_deal_no(
115 p_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE,
116 p_int_deal_no_from XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
117 p_int_deal_no_to XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
118 p_date DATE,
119 p_default_end_date DATE) IS
120 SELECT deal_date_amount_id, deal_number,
121 amount_date, amount_type, cashflow_amount,
122 settle, batch_id, transaction_number, deal_type
123 FROM xtr_deal_date_amounts
124 WHERE cparty_account_no = p_current_bank_account
125 AND amount_date >= p_date
126 AND amount_date <= p_default_end_date
127 AND company_code = p_company_code
128 AND deal_type NOT IN ('CA', 'IAC')
129 AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
130
131 CURSOR cparty_accts_with_deal_type (
132 p_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE,
133 p_int_deal_no_from XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
134 p_int_deal_no_to XTR_DEAL_DATE_AMOUNTS.DEAL_NUMBER%TYPE,
135 p_date DATE,
136 p_default_end_date DATE) IS
137 SELECT deal_date_amount_id, deal_number,
138 amount_date, amount_type, cashflow_amount,
139 settle, batch_id, transaction_number, deal_type
140 FROM xtr_deal_date_amounts
141 WHERE cparty_account_no = p_current_bank_account
142 AND amount_date >= p_date
143 AND amount_date <= p_default_end_date
144 AND deal_type = p_deal_type
145 AND company_code = p_company_code
146 AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
147
148 CURSOR check_journal( p_batch_id XTR_DEAL_DATE_AMOUNTS.BATCH_ID%TYPE) IS
149 SELECT 'Y'
150 FROM xtr_batches b, xtr_batch_events e
151 WHERE b.batch_id = e.batch_id
152 AND e.event_code = 'JRNLGN'
153 AND b.batch_id = p_batch_id;
154
155 l_commit_counter NUMBER;
156 l_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE;
157 l_current_currency XTR_BANK_ACCOUNTS.currency%TYPE;
158 l_current_party_code XTR_BANK_ACCOUNTS.PARTY_CODE%TYPE;
159 l_new_currency XTR_BANK_ACCOUNTS.currency%TYPE;
160 l_new_authorized XTR_BANK_ACCOUNTS.AUTHORISED%TYPE;
161 l_reqid VARCHAR2(30);
162 l_request_id NUMBER;
163 l_amount_type XTR_DEAL_DATE_AMOUNTS.AMOUNT_TYPE%TYPE;
164 l_amount_date XTR_DEAL_DATE_AMOUNTS.AMOUNT_DATE%TYPE;
165 l_settle XTR_DEAL_DATE_AMOUNTS.SETTLE%TYPE;
166 l_batch_id XTR_DEAL_DATE_AMOUNTS.BATCH_ID%TYPE;
167 l_party_type XTR_PARTY_INFO.PARTY_TYPE%TYPE;
168 l_deal_number XTR_DEALS.DEAL_NO%TYPE;
169 l_deal_date_amount_id XTR_DEAL_DATE_AMOUNTS.DEAL_DATE_AMOUNT_ID%TYPE;
170 l_cf_req_details_id XTR_CFLOW_REQUEST_DETAILS.CASHFLOW_REQUEST_DETAILS_ID%TYPE;
171 l_cashflow_amount XTR_DEAL_DATE_AMOUNTS.CASHFLOW_AMOUNT%TYPE;
172 l_transaction_number XTR_DEAL_DATE_AMOUNTS.TRANSACTION_NUMBER%TYPE;
173 l_deal_type XTR_DEAL_DATE_AMOUNTS.DEAL_TYPE%TYPE;
174 l_updated_flag VARCHAR2(1);
175 l_journalized VARCHAR2(1);
176 l_message_name VARCHAR2(30);
177
178 p_int_start_date DATE;
179 p_int_end_date DATE;
180 p_int_deal_number_from NUMBER;
181 p_int_deal_number_to NUMBER;
182 p_company_code XTR_PARTY_INFO.PARTY_CODE%TYPE;
183
184 BEGIN
185
186 G_user_id := FND_GLOBAL.USER_ID;
187 G_create_date := trunc(sysdate);
188 p_int_start_date := to_date(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
189 p_int_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
190 --Verify Bank Account Currencies are the same
191 fnd_profile.get('CONC_REQUEST_ID', l_reqid);
192 l_request_id := to_number(l_reqid);
193
194 /* for bug 5634804*/
195 IF p_int_end_date IS NULL THEN
196 p_int_end_date := to_date('31-12-4712', 'DD-MM-RRRR');
197 END IF;
198 /* for bug 5634804*/
199
200 SELECT xban.currency, xpv.party_type
201 INTO l_current_currency, l_party_type
202 FROM xtr_bank_accounts xban, xtr_parties_v xpv
203 WHERE xban.account_number = p_current_bank_account
204 AND xban.party_code = p_party_code
205 AND xpv.party_code = xban.party_code;
206
207 SELECT currency, authorised
208 INTO l_new_currency, l_new_authorized
209 FROM xtr_bank_accounts
210 WHERE account_number = p_new_bank_account
211 AND party_code = p_party_code;
212
213 IF l_new_authorized <> 'Y' THEN
214 SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
215 INTO l_cf_req_details_id
216 FROM DUAL;
217 insert_request_details( l_cf_req_details_id,
218 l_request_id, p_party_code,
219 p_current_bank_account, p_new_bank_account,
220 p_deal_type, p_deal_number_from,
221 p_deal_number_to, p_int_start_date,
222 p_int_end_date, p_include_journalized);
223 l_message_name := 'XTR_CFLOW_ACCT_UNAUTHORIZED';
224 insert_transaction_details(l_cf_req_details_id, sysdate,
225 '', 0, '', 0, 0, 'N',
226 l_message_name);
227 l_request_id := FND_REQUEST.SUBMIT_REQUEST('XTR', 'XTRUPREP',
228 '','', FALSE, l_cf_req_details_id);
229 IF l_request_id = 0 THEN
230 RAISE APP_EXCEPTION.application_exception;
231 END IF;
232 END IF;
233
234 IF (l_message_name IS NULL) THEN
235 IF l_current_currency <> l_new_currency THEN
236 SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
237 INTO l_cf_req_details_id
238 FROM DUAL;
239 insert_request_details( l_cf_req_details_id,
240 l_request_id, p_party_code,
241 p_current_bank_account, p_new_bank_account,
242 p_deal_type, p_deal_number_from,
243 p_deal_number_to, p_int_start_date,
244 p_int_end_date, p_include_journalized);
245 l_message_name := 'XTR_CFLOW_CURRENCY_MISMATCH';
246 insert_transaction_details(l_cf_req_details_id, sysdate,
247 '', 0, '', 0, 0, 'N',
248 l_message_name);
249 l_request_id := FND_REQUEST.SUBMIT_REQUEST('XTR', 'XTRUPREP',
250 '','', FALSE, l_cf_req_details_id);
251 IF l_request_id = 0 THEN
252 RAISE APP_EXCEPTION.application_exception;
253 END IF;
254 END IF;
255 END IF;
256
257 IF l_message_name IS NULL THEN
258 SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
259 INTO l_cf_req_details_id
260 FROM DUAL;
261 insert_request_details(l_cf_req_details_id, l_request_id,
262 p_party_code, p_current_bank_account,
263 p_new_bank_account, p_deal_type,
264 p_deal_number_from, p_deal_number_to,
265 p_int_start_date, p_int_end_date,
266 p_include_journalized);
267 IF p_deal_number_from IS NULL THEN
268 p_int_deal_number_from := 0;
269 ELSE
270 p_int_deal_number_from := p_deal_number_from;
271 END IF;
272 IF p_deal_number_to IS NULL THEN
273 OPEN max_deal_no;
274 FETCH max_deal_no INTO p_int_deal_number_to;
275 CLOSE max_deal_no;
276 ELSE
277 p_int_deal_number_to := p_deal_number_to;
278 END IF;
279 l_commit_counter := 0;
280 IF l_party_type = 'C' THEN
281 IF p_deal_type IS NULL THEN
282 OPEN comp_accts_with_deal_no (
283 p_party_code, p_int_deal_number_from,
284 p_int_deal_number_to,
285 p_int_start_date, p_int_end_date);
286 LOOP
287 FETCH comp_accts_with_deal_no INTO
288 l_deal_date_amount_id,
289 l_deal_number,
290 l_amount_date, l_amount_type,
291 l_cashflow_amount, l_settle,
292 l_batch_id,
293 l_transaction_number, l_deal_type;
294 IF comp_accts_with_deal_no%NOTFOUND THEN
295 CLOSE comp_accts_with_deal_no;
296 EXIT;
297 END IF;
298 l_updated_flag := 'Y';
299 l_message_name := '';
300 IF l_settle = 'Y' THEN
301 l_updated_flag := 'N';
302 l_message_name :=
303 'XTR_CFLOW_NOT_UPDATED';
304 END IF;
305 IF (l_batch_id IS NOT NULL AND
306 l_updated_flag = 'Y') THEN
307 OPEN check_journal(l_batch_id);
308 FETCH check_journal
309 INTO l_journalized;
310 CLOSE check_journal;
311 IF l_journalized = 'Y' THEN
312 IF p_include_journalized = 'Y'
313 THEN
314 l_updated_flag := 'Y';
315 l_message_name :=
316 'XTR_ACCNT_CHNG_AFTER_JRNL';
317 ELSE
318 l_updated_flag := 'N';
319 l_message_name :=
320 'XTR_CFLOW_NOT_UPDATED';
321 END IF;
322 END IF;
323 END IF;
324 IF l_updated_flag = 'Y' THEN
325 UPDATE xtr_deal_date_amounts
326 set account_no = p_new_bank_account
327 where deal_date_amount_id =
328 l_deal_date_amount_id;
329 END IF;
330 l_commit_counter := l_commit_counter + 1;
331 insert_transaction_details(
332 l_cf_req_details_id,
333 l_amount_date,
334 l_amount_type,
335 l_cashflow_amount,
336 l_deal_type,
337 l_deal_number,
338 l_transaction_number,
339 l_updated_flag,
340 l_message_name);
341 IF l_commit_counter = 1000 THEN
342 COMMIT;
343 l_commit_counter := 0;
344 END IF;
345 END LOOP;
346 COMMIT;
347 IF comp_accts_with_deal_no%ISOPEN THEN
348 CLOSE comp_accts_with_deal_no;
349 END IF;
350 ELSE -- p_deal_type is not null
351 OPEN comp_accts_with_deal_type(
352 p_party_code, p_int_deal_number_from,
353 p_int_deal_number_to,
354 p_int_start_date, p_int_end_date);
355 LOOP
356 FETCH comp_accts_with_deal_type
357 INTO
361 l_cashflow_amount, l_settle,
358 l_deal_date_amount_id,
359 l_deal_number,
360 l_amount_date, l_amount_type,
362 l_batch_id,
363 l_transaction_number, l_deal_type;
364 IF comp_accts_with_deal_type%NOTFOUND THEN
365 CLOSE comp_accts_with_deal_type;
366 EXIT;
367 END IF;
368 l_updated_flag := 'Y';
369 l_message_name := '';
370 IF l_settle = 'Y' THEN
371 l_updated_flag := 'N';
372 l_message_name :=
373 'XTR_CFLOW_NOT_UPDATED';
374 END IF;
375 IF (l_batch_id IS NOT NULL AND
376 l_updated_flag = 'Y') THEN
377 OPEN check_journal(l_batch_id);
378 FETCH check_journal
379 INTO l_journalized;
380 CLOSE check_journal;
381 IF l_journalized = 'Y' THEN
382 IF p_include_journalized = 'Y'
383 THEN
384 l_updated_flag := 'Y';
385 l_message_name :=
386 'XTR_ACCNT_CHNG_AFTER_JRNL';
387 ELSE
388 l_updated_flag := 'N';
389 l_message_name :=
390 'XTR_CFLOW_NOT_UPDATED';
391 END IF;
392 END IF;
393 END IF;
394 IF l_updated_flag = 'Y' THEN
395 UPDATE xtr_deal_date_amounts
396 set account_no = p_new_bank_account
397 where deal_date_amount_id =
398 l_deal_date_amount_id;
399 END IF;
400 l_commit_counter := l_commit_counter + 1;
401 insert_transaction_details(
402 l_cf_req_details_id,
403 l_amount_date,
404 l_amount_type,
405 l_cashflow_amount,
406 l_deal_type,
407 l_deal_number,
408 l_transaction_number,
409 l_updated_flag,
410 l_message_name);
411 IF l_commit_counter = 1000 THEN
412 COMMIT;
413 l_commit_counter := 0;
414 END IF;
415 END LOOP;
416 COMMIT;
417 IF comp_accts_with_deal_type%ISOPEN THEN
418 CLOSE comp_accts_with_deal_type;
419 END IF;
420 END IF; -- p_deal_type is null
421 ELSE -- p_party_type = Counterparty
422 IF p_deal_type IS NULL THEN
423 OPEN company_list;
424 LOOP
425 FETCH company_list INTO l_company_code;
426 IF company_list%NOTFOUND THEN
427 CLOSE company_list;
428 EXIT;
429 END IF;
430 l_commit_counter := 0;
431 OPEN cparty_accts_with_deal_no
432 (l_company_code,
433 p_int_deal_number_from,
434 p_int_deal_number_to,
435 p_int_start_date,
436 p_int_end_date);
437 LOOP
438 FETCH cparty_accts_with_deal_no
439 INTO
440 l_deal_date_amount_id,
441 l_deal_number,
442 l_amount_date, l_amount_type,
443 l_cashflow_amount, l_settle,
444 l_batch_id,
445 l_transaction_number, l_deal_type;
446 IF cparty_accts_with_deal_no%NOTFOUND
447 THEN
448 CLOSE cparty_accts_with_deal_no;
449 EXIT;
450 END IF;
451 l_updated_flag := 'Y';
452 l_message_name := '';
453 IF l_settle = 'Y' THEN
454 l_updated_flag := 'N';
455 l_message_name :=
456 'XTR_CFLOW_NOT_UPDATED';
457 END IF;
458 IF (l_batch_id IS NOT NULL AND
459 l_updated_flag = 'Y') THEN
460 OPEN check_journal(l_batch_id);
461 FETCH check_journal
462 INTO l_journalized;
463 CLOSE check_journal;
464 IF l_journalized = 'Y' THEN
465 IF p_include_journalized = 'Y'
466 THEN
467 l_updated_flag := 'Y';
468 l_message_name :=
469 'XTR_ACCNT_CHNG_AFTER_JRNL';
470 ELSE
471 l_updated_flag := 'N';
472 l_message_name :=
473 'XTR_CFLOW_NOT_UPDATED';
474 END IF;
475 END IF;
476 END IF;
477 IF l_updated_flag = 'Y' THEN
478 Update XTR_DEAL_DATE_AMOUNTS
479 Set cparty_account_no =
480 p_new_bank_account
481 Where deal_date_amount_id =
482 l_deal_date_amount_id;
483 END IF;
484 l_commit_counter :=
485 l_commit_counter + 1;
486 insert_transaction_details(
487 l_cf_req_details_id,
488 l_amount_date,
489 l_amount_type,
490 l_cashflow_amount,
491 l_deal_type,
492 l_deal_number,
493 l_transaction_number,
494 l_updated_flag,
495 l_message_name);
496 IF l_commit_counter = 1000 THEN
497 COMMIT;
498 l_commit_counter := 0;
499 END IF;
500 END LOOP; -- unauth_counterparty
501 COMMIT;
502 IF cparty_accts_with_deal_no%ISOPEN THEN
503 CLOSE cparty_accts_with_deal_no;
504 END IF;
505 END LOOP; -- company_list
506 IF company_list%ISOPEN THEN
507 CLOSE company_list;
508 END IF;
509 ELSE -- p_deal_type is not null
510 OPEN company_list;
511 LOOP
512 FETCH company_list INTO l_company_code;
513 IF company_list%NOTFOUND THEN
514 CLOSE company_list;
515 EXIT;
516 END IF;
517 l_commit_counter := 0;
518 OPEN cparty_accts_with_deal_type
519 (l_company_code,
520 p_int_deal_number_from,
521 p_int_deal_number_to,
522 p_int_start_date,
523 p_int_end_date);
524 LOOP
525 FETCH cparty_accts_with_deal_type
526 INTO
527 l_deal_date_amount_id,
528 l_deal_number,
529 l_amount_date, l_amount_type,
530 l_cashflow_amount, l_settle,
531 l_batch_id,
532 l_transaction_number, l_deal_type;
533 IF cparty_accts_with_deal_type%NOTFOUND
534 THEN
538 l_updated_flag := 'Y';
535 CLOSE cparty_accts_with_deal_type;
536 EXIT;
537 END IF;
539 l_message_name := '';
540 IF l_settle = 'Y' THEN
541 l_updated_flag := 'N';
542 l_message_name :=
543 'XTR_CFLOW_NOT_UPDATED';
544 END IF;
545 IF (l_batch_id IS NOT NULL AND
546 l_updated_flag = 'Y') THEN
547 OPEN check_journal(l_batch_id);
548 FETCH check_journal
549 INTO l_journalized;
550 CLOSE check_journal;
551 IF l_journalized = 'Y' THEN
552 IF p_include_journalized = 'Y'
553 THEN
554 l_updated_flag := 'Y';
555 l_message_name :=
556 'XTR_ACCNT_CHNG_AFTER_JRNL';
557 ELSE
558 l_updated_flag := 'N';
559 l_message_name :=
560 'XTR_CFLOW_NOT_UPDATED';
561 END IF;
562 END IF;
563 END IF;
564 IF l_updated_flag = 'Y' THEN
565 Update XTR_DEAL_DATE_AMOUNTS
566 Set cparty_account_no =
567 p_new_bank_account
568 Where deal_date_amount_id =
569 l_deal_date_amount_id;
570 END IF;
571 l_commit_counter :=
572 l_commit_counter + 1;
573 insert_transaction_details(
574 l_cf_req_details_id,
575 l_amount_date,
576 l_amount_type,
577 l_cashflow_amount,
578 l_deal_type,
579 l_deal_number,
580 l_transaction_number,
581 l_updated_flag,
582 l_message_name);
583 IF l_commit_counter = 1000 THEN
584 COMMIT;
585 l_commit_counter := 0;
586 END IF;
587 END LOOP; -- unauth_counterparty
588 COMMIT;
589 IF cparty_accts_with_deal_type%ISOPEN THEN
590 CLOSE cparty_accts_with_deal_type;
591 END IF;
592 END LOOP; -- company_list
593 IF company_list%ISOPEN THEN
594 CLOSE company_list;
595 END IF;
596 END IF;
597 END IF;
598
599 l_request_id := FND_REQUEST.SUBMIT_REQUEST('XTR', 'XTRUPREP',
600 '','', FALSE, l_cf_req_details_id);
601 IF l_request_id = 0 THEN
602 RAISE APP_EXCEPTION.application_exception;
603 END IF;
604 END IF;
605 END;
606
607
608
609 /**
610 * PROCEDURE insert_request_details
611 *
612 * DESCRIPTION
613 * Inserts request details into XTR_CFLOW_REQUEST_DETAILS table so that
614 * the execution report can be run at any time.
615 *
616 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
617 *
618 * ARGUMENTS
619 * IN:
620 * p_cashflow_request_details_id The identifier for the record.
621 * p_request_id The Request ID of the Concurrent
622 * Request that was run to update the
623 * Settlement Accounts.
624 * p_party_code Party Code Parameter from the request.
625 * p_current_bank_account Current Bank Account Number Parameter
626 * from the request.
627 * p_new_bank_account New Bank Account Number Parameter
628 * from request.
629 * p_start_date Start Date Parameter from the request.
630 * p_end_date End Date Parameter from the request.
631 * p_deal_type Deal Type Parameter from the request.
632 * p_deal_number_from Deal Number From Parameter from the
633 * request.
634 * p_deal_number_to Deal Number To Parameter from the
635 * request.
636 * p_inc_journalized Include Journalized Transactions
637 * Parameter from the request.
638 * IN/OUT:
639 * OUT:
640 *
641 * NOTES
642 *
643 * MODIFICATION HISTORY
644 *
645 * 06-Jun-2005 Rajesh Jose o Created.
646 */
647 PROCEDURE Insert_Request_Details(
648 p_cashflow_request_details_id IN
649 XTR_CFLOW_REQUEST_DETAILS.CASHFLOW_REQUEST_DETAILS_ID%TYPE,
650 p_request_id IN NUMBER,
651 p_party_code IN XTR_PARTY_INFO.PARTY_CODE%TYPE,
652 p_current_bank_account IN XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
653 p_new_bank_account IN XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
654 p_deal_type IN XTR_DEALS.DEAL_TYPE%TYPE,
655 p_deal_number_from IN XTR_DEALS.DEAL_NO%TYPE,
656 p_deal_number_to IN XTR_DEALS.DEAL_NO%TYPE,
657 p_start_date IN DATE,
658 p_end_date IN DATE,
659 p_inc_journalized IN
660 XTR_CFLOW_REQUEST_DETAILS.INCLUDE_JOURNALIZED_FLAG%TYPE)
661 IS
662 BEGIN
663 INSERT INTO XTR_CFLOW_REQUEST_DETAILS
664 (cashflow_request_details_id, request_id, party_code,
665 account_no_from, account_no_to, deal_type,
666 deal_number_from, deal_number_to, starting_cflow_date,
667 ending_cflow_date, include_journalized_flag,
668 created_by, creation_date, last_updated_by, last_update_date,
669 last_update_login)
670 VALUES
671 (p_cashflow_request_details_id, p_request_id, p_party_code,
672 p_current_bank_account, p_new_bank_account, p_deal_type,
673 p_deal_number_from, p_deal_number_to, p_start_date,
674 p_end_date, p_inc_journalized,
675 G_user_id, G_create_date, G_user_id, G_create_date,
676 G_user_id);
677 END;
678
679
680 /**
681 * PROCEDURE insert_transaction_details
682 *
683 * DESCRIPTION
684 * Inserts details of transactions updated by the concurrent request
685 * so that the Execution Report shows accurate data at any point of time.
686 *
687 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
688 *
689 * ARGUMENTS
690 * IN:
691 * p_cashflow_request_details_id Foreign key reference from
692 * XTR_CFLOW_REQUEST_DETAILS.
693 * p_amount_date The amount date of the cashflow.
694 * p_amount_type The type of cashflow.
695 * p_cashflow_amount The amount of cash flow.
696 * p_deal_type The Deal Type for the record.
697 * p_deal_number The Deal Number for the record.
698 * p_transaction_number The Transaction Number for the record.
699 * p_updated_flag Flag indicating whether the record was
700 * updated or not.
701 * p_message_name Message to be shown if record was not
702 * updated. Conveys the reason why the
703 * record was not updated.
704 * IN/OUT:
705 * OUT:
706 *
707 * NOTES
708 *
709 * MODIFICATION HISTORY
710 * 06-Jun-2005 Rajesh Jose o Created.
711 *
712 */
713
714 PROCEDURE Insert_Transaction_Details(
715 p_cashflow_request_details_id IN
716 XTR_CFLOW_REQUEST_DETAILS.CASHFLOW_REQUEST_DETAILS_ID%TYPE,
717 p_amount_date IN DATE,
718 p_amount_type IN XTR_DEAL_DATE_AMOUNTS.AMOUNT_TYPE%TYPE,
719 p_cashflow_amount IN
720 XTR_DEAL_DATE_AMOUNTS.CASHFLOW_AMOUNT%TYPE,
721 p_deal_type IN XTR_DEAL_DATE_AMOUNTS.DEAL_TYPE%TYPE,
722 p_deal_number IN XTR_DEALS.DEAL_NO%TYPE,
723 p_transaction_number IN
724 XTR_DEAL_DATE_AMOUNTS.TRANSACTION_NUMBER%TYPE,
725 p_updated_flag IN VARCHAR2,
726 p_message_name IN VARCHAR2)
727 IS
728 BEGIN
729 INSERT INTO XTR_CFLOW_UPDATED_RECORDS
730 (cashflow_request_details_id, amount_date, amount_type,
731 cashflow_amount, deal_type, deal_number,
732 transaction_number, updated_flag, message_name,
733 created_by, creation_date, last_updated_by, last_update_date,
734 last_update_login)
735 VALUES
736 (p_cashflow_request_details_id, p_amount_date, p_amount_type,
737 p_cashflow_amount, p_deal_type, p_deal_number,
738 p_transaction_number, p_updated_flag, p_message_name,
739 G_user_id, G_create_date, G_user_id, G_create_date,
740 G_user_id);
741 END;
742
743
744 END XTR_UPDATE_SETTLEMENT_ACCOUNTS;
745
[Home] [Help]