1 PACKAGE BODY GL_TRANS_DATES_PKG as
2 /* $Header: gligctdb.pls 120.3 2005/05/05 01:08:26 kvora ship $ */
3
4
5 --
6 --
7 -- Package
8 -- gl_trans_date_pkg
9 --
10 -- Purpose
11 -- Obtains business days pattern from
12 -- transaction calendar.
13 --
14 --
15 -- Procedure
16 -- get_business_days_pattern
17 -- Purpose
18 -- Uses transaction_calendar_id and start and end dates
19 -- to obtain which days are business days in the form of a
20 -- binary VARCHAR pattern.
21 -- A typical pattern could be '1121221212221211212'
22 -- Each character represents whether the day is a business day or not.
23 -- '1' represents a business day and
24 -- '2' represents a nonbusiness day.
25 --
26 -- Arguments
27 -- X_transaction_cal_id
28 -- X_start_date
29 -- X_end_date
30 -- X_bus_days_pattern
31 --
32 PROCEDURE get_business_days_pattern(X_transaction_cal_id IN NUMBER,
33 X_start_date IN DATE,
34 X_end_date IN DATE,
35 X_bus_days_pattern IN OUT NOCOPY VARCHAR2) IS
36
37 CURSOR GETPAT IS
38 SELECT
39 to_char(sum(decode(business_day_flag, 'Y', 1, 'N', 2) *
40 power(10, transaction_date - X_start_date)))
41 FROM
42 GL_TRANSACTION_DATES
43 WHERE
44 transaction_calendar_id = X_transaction_cal_id
45 AND transaction_date between X_start_date and X_end_date;
46
47
48 BEGIN
49
50 OPEN GETPAT;
51 FETCH GETPAT INTO X_bus_days_pattern;
52 CLOSE GETPAT;
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 app_exception.raise_exception;
57
58 END get_business_days_pattern;
59
60 --
61 -- Procedure
62 -- get_big_bus_days_pattern
63 -- Purpose
64 -- Uses transaction_calendar_id and start and end dates
65 -- to obtain which days are business days in the form of a
66 -- binary VARCHAR pattern.
67 -- A typical pattern could be '1121221212221211212'
68 -- Each character represents whether the day is a business day or not.
69 -- '1' represents a business day and
70 -- '2' represents a nonbusiness day.
71 --
72 -- Arguments
73 -- X_transaction_cal_id
74 -- X_start_date
75 -- X_end_date
76 -- X_bus_days_pattern
77 --
78 PROCEDURE get_big_bus_days_pattern(X_transaction_cal_id IN NUMBER,
79 X_start_date IN DATE,
80 X_end_date IN DATE,
81 X_bus_days_pattern IN OUT NOCOPY VARCHAR2
82 ) IS
83
84 current_date DATE;
85 next_pattern VARCHAR2(100);
86 BEGIN
87
88 current_date := X_end_date;
89 X_bus_days_pattern := '';
90 next_pattern := '';
91
92 WHILE (current_date - 34 > X_start_date) LOOP
93
94 get_business_days_pattern(X_transaction_cal_id,
95 current_date - 34,
96 current_date,
97 next_pattern);
98
99 X_bus_days_pattern := X_bus_days_pattern || next_pattern;
100
101 current_date := current_date - 35;
102
103 END LOOP;
104
105 get_business_days_pattern(X_transaction_cal_id,
106 X_start_date,
107 current_date,
108 next_pattern);
109
110 X_bus_days_pattern := X_bus_days_pattern || next_pattern;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 app_exception.raise_exception;
114
115 END get_big_bus_days_pattern;
116
117 END GL_TRANS_DATES_PKG;
118
[Home] [Help]