1 package body iby_pmtdates_pkg as
2 /*$Header: ibyschdb.pls 115.3 2002/11/19 21:17:02 jleybovi ship $*/
3 /*
4 ** This package is a wrapper kind of package to support the
5 ** Holidays. Each BEP need to define a package with its name prefixed to
6 ** "_pkg". That package should provide a s`procedure in it
7 ** which should be named as "getDates" and Parameters, int, and 4 date
8 ** parameters.
9 ** int parameter is the leadtime of the BEP.
10 ** first date parameter is the settlement date requested by user.
11 ** second date parameter is the current date, the date on which the payment
12 ** was requested.
13 ** next two dates are the ouput dates. First output date corresponds to the
14 ** schedule date of the payment and next out param is the earliest
15 ** settlement date possible.
16 */
17 procedure getPmtDates( i_bepid in iby_bepinfo.bepid%type,
18 i_settlement_date in date,
19 i_current_date in date,
20 io_schedule_date in out nocopy date,
21 io_earliest_sched_date in out nocopy date)
22 as
23 l_bepname iby_bepinfo.name%type;
24 l_leadtime iby_bepinfo.leadtime%type;
25 l_holidayfile iby_bepinfo.holidayfile%type;
26 l_blockstr varchar2(1000);
27 l_cursorId integer;
28 l_dummy integer;
29 /*
30 ** Cursor to get the BEP name and leadtime.
31 */
32 cursor bepinfo ( ci_bepid iby_bepinfo.bepid%type) is
33 select name, leadtime, upper(holidayfile)
34 from iby_bepinfo
35 where bepid = ci_bepid;
36 begin
37 if ( bepinfo%isopen ) then
38 close bepinfo;
39 end if;
40 open bepinfo(i_bepid);
41 /*
42 ** fetch the bep name and leadtime.
43 */
44 fetch bepinfo into l_bepname, l_leadtime, l_holidayfile;
45 if ( bepinfo%notfound ) then
46 close bepinfo;
47 raise_application_error(-20000, 'IBY_20521#', FALSE);
48 --raise_application_error(-20521, 'No BEP Objects matches', FALSE);
49 end if;
50 if ( l_holidayfile = 'Y' ) then
51 /*
52 ** Construct a dynamic PL/SQL call for calling the BEP's package.
53 */
54 l_cursorId := DBMS_SQL.OPEN_CURSOR;
55 l_blockstr := 'BEGIN
56 iby_' || l_bepname || '_pkg.getPmtDates( :leadtime, :reqsetdate, :curdate, :expschdate, :earliestschdate);
57 END; ';
58 /*
59 ** Bind parameters to the BEP call.
60 */
61 DBMS_SQL.PARSE(l_CursorId, l_blockstr, DBMS_SQL.V7);
62 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':leadtime', l_leadtime);
63 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':reqsetdate', i_settlement_date);
64 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':curdate', i_current_date);
65 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':expschdate', io_schedule_date);
66 DBMS_SQL.BIND_VARIABLE(l_cursorID, ':earliestschdate', io_earliest_sched_date);
67 /*
68 ** Execute the Dynamic plsql.
69 */
70 l_dummy := dbms_sql.execute(l_cursorid);
71 /*
72 ** Extract the values from the plsql procedure.
73 */
74 DBMS_SQL.VARIABLE_VALUE(l_cursorID, ':leadtime', l_leadtime);
75 DBMS_SQL.VARIABLE_VALUE(l_cursorID, ':expschdate', io_schedule_date);
76 DBMS_SQL.VARIABLE_VALUE(l_cursorID, ':earliestschdate', io_earliest_sched_date);
77 if ( io_earliest_sched_date is null ) then
78 io_earliest_sched_date := i_settlement_date;
79 end if;
80 dbms_sql.close_cursor(l_cursorid);
81 else
82 io_schedule_date := i_settlement_date - l_leadtime;
83 if ( trunc(io_schedule_date) < trunc(i_current_date) ) then
84 io_earliest_sched_date := i_current_date + l_leadtime;
85 io_schedule_date := i_current_date;
86 else
87 io_earliest_sched_date := i_settlement_date;
88 end if;
89 end if;
90 end getPmtDates;
91 end iby_pmtdates_pkg;
[Home] [Help]