[Home] [Help]
PACKAGE BODY: APPS.GL_DATE_PERIOD_MAP_PKG
Source
1 PACKAGE BODY gl_date_period_map_pkg AS
2 /* $Header: gliprmpb.pls 120.3.12010000.2 2010/04/30 13:36:47 sommukhe ship $ */
3 --
4 -- PRIVATE FUNCTIONS
5 --
6 PROCEDURE insert_new_year
7 (
8 x_period_set_name VARCHAR2,
9 x_period_type VARCHAR2,
10 x_entered_year VARCHAR2,
11 x_period_name VARCHAR2,
12 x_CREATION_DATE DATE,
13 x_CREATED_BY NUMBER,
14 x_LAST_UPDATE_DATE DATE,
15 x_LAST_UPDATED_BY NUMBER,
16 x_LAST_UPDATE_LOGIN NUMBER
17 ) IS
18 dummy VARCHAR2(100);
19 new_year_flag VARCHAR2(1);
20 new_entered_year VARCHAR2(30);
21 BEGIN
22 -- do we already have this year in GL_DATE_PERIOD_MAP?
23 SELECT '1' INTO dummy FROM sys.dual
24 WHERE EXISTS
25 (SELECT 'Existing Year'
26 FROM gl_date_period_map
27 WHERE
28 period_set_name = x_period_set_name
29 AND period_type = x_period_type
30 AND accounting_date BETWEEN
31 TO_DATE(x_entered_year || '/01/01', 'YYYY/MM/DD')
32 AND TO_DATE(x_entered_year || '/12/31', 'YYYY/MM/DD')
33 );
34
35
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 -- NO, this is a new year
39 -- Insert placeholder records (with period_name = NOT ASSIGNED) for the new
40 -- year into the GL_DATE_PERIOD_MAP table
41 new_entered_year := x_entered_year;
42 INSERT INTO gl_date_period_map
43 (
44 period_set_name,
45 period_type,
46 accounting_date,
47 period_name,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 LAST_UPDATE_LOGIN
53 )
54 SELECT
55 x_period_set_name,
56 x_period_type,
57 TO_DATE(new_entered_year || '/01/01',
58 'YYYY/MM/DD')+(cnt.multiplier-1),
59 'NOT ASSIGNED', -- placeholder
60 x_CREATION_DATE,
61 x_CREATED_BY,
62 x_LAST_UPDATE_DATE,
63 x_LAST_UPDATED_BY,
64 x_LAST_UPDATE_LOGIN
65 FROM gl_row_multipliers cnt
66 WHERE
67 cnt.multiplier <=
68 TO_DATE(new_entered_year || '/12/31', 'YYYY/MM/DD') -
69 TO_DATE(new_entered_year || '/01/01', 'YYYY/MM/DD')+1;
70
71 END;
72
73
74 PROCEDURE select_row( recinfo IN OUT NOCOPY gl_date_period_map%ROWTYPE) IS
75 BEGIN
76 SELECT *
77 INTO recinfo
78 FROM gl_date_period_map
79 WHERE period_set_name = recinfo.period_set_name
80 AND period_type = recinfo.period_type
81 AND accounting_date = recinfo.accounting_date;
82
83 END select_row;
84
85 --
86 -- PUBLIC FUNCTIONS
87 --
88 PROCEDURE maintain_date_period_map
89 (
90 x_period_set_name VARCHAR2,
91 x_period_type VARCHAR2,
92 x_adjust_period_flag VARCHAR2,
93 x_operation VARCHAR2,
94 x_start_date DATE,
95 x_end_date DATE,
96 x_period_name VARCHAR2,
97 x_CREATION_DATE DATE,
98 x_CREATED_BY NUMBER,
99 x_LAST_UPDATE_DATE DATE,
100 x_LAST_UPDATED_BY NUMBER,
101 x_LAST_UPDATE_LOGIN NUMBER
102 ) IS
103 dummy VARCHAR2(100);
104 start_entered_year VARCHAR2(30);
105 end_entered_year VARCHAR2(30);
106 BEGIN
107
108 start_entered_year := TO_CHAR(x_start_date, 'YYYY');
109 -- insert new year for start_date if needed
110 insert_new_year (
111 x_period_set_name,
112 x_period_type,
113 start_entered_year,
114 x_period_name,
115 x_CREATION_DATE,
116 x_CREATED_BY,
117 x_LAST_UPDATE_DATE,
118 x_LAST_UPDATED_BY,
119 x_LAST_UPDATE_LOGIN
120 );
121
122 -- if start_entered_year and end_entered_year are different and
123 -- the end_date is a new year, insert a new year for this one too
124 end_entered_year := TO_CHAR(x_end_date, 'YYYY');
125 IF(start_entered_year <> end_entered_year) THEN
126 -- insert new year for end_date if needed
127 insert_new_year (
128 x_period_set_name,
129 x_period_type,
130 end_entered_year,
131 x_period_name,
132 x_CREATION_DATE,
133 x_CREATED_BY,
134 x_LAST_UPDATE_DATE,
135 x_LAST_UPDATED_BY,
136 x_LAST_UPDATE_LOGIN
137 );
138 END IF;
139
140
141 -- Update period_name column in GL_DATE_PERIOD_MAP table
142 -- (only non-adjusting periods)
143 IF (x_adjust_period_flag = 'N') THEN
144 IF (x_operation = 'INSERT') THEN
145 -- For new periods being inserted:
146 UPDATE gl_date_period_map
147 SET period_name = x_period_name
148 WHERE
149 accounting_date between
150 x_start_date
151 AND x_end_date
152 AND period_set_name = x_period_set_name
153 AND period_type = x_period_type;
154
155 ELSIF(x_operation = 'DELETE') THEN
156
157 -- For existing periods being updated:
158 UPDATE gl_date_period_map
159 SET period_name = 'NOT ASSIGNED'
160 WHERE
161 ( accounting_date between x_start_date AND x_end_date
162 OR period_name = x_period_name)
163 AND period_set_name = x_period_set_name
164 AND period_type = x_period_type;
165 ELSE
166
167 -- For existing periods being updated:
168 UPDATE gl_date_period_map
169 SET period_name = DECODE(LEAST(accounting_date, x_start_date-1),
170 accounting_date, 'NOT ASSIGNED',
171 DECODE(GREATEST(accounting_date, x_end_date+1),
172 accounting_date, 'NOT ASSIGNED',
173 x_period_name))
174 WHERE
175 ( accounting_date between x_start_date AND x_end_date
176 OR period_name = x_period_name)
177 AND period_set_name = x_period_set_name
178 AND period_type = x_period_type;
179 END IF;
180 END IF; -- from IF (x_adjust_period_flag =
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
185 fnd_message.set_token('PROCEDURE',
186 'gl_date_period_map_pkg.maintain_date_period_map');
187 RAISE;
188
189 END maintain_date_period_map;
190
191
192 PROCEDURE select_columns
193 (
194 x_period_set_name VARCHAR2,
195 x_period_type VARCHAR2,
196 x_accounting_date DATE,
197 x_period_name IN OUT NOCOPY VARCHAR2) IS
198
199 recinfo gl_date_period_map%ROWTYPE;
200
201 BEGIN
202 recinfo.period_set_name := x_period_set_name;
203 recinfo.period_type := x_period_type;
204 recinfo.accounting_date := x_accounting_date;
205
206 select_row(recinfo);
207
208 x_period_name := recinfo.period_name;
209 END select_columns;
210
211 END;