[Home] [Help]
PACKAGE BODY: APPS.PA_PERIODS_PKG
Source
1 PACKAGE BODY pa_periods_pkg AS
2 /* $Header: PASUCPSB.pls 120.4 2011/09/20 12:26:32 arbandyo ship $ */
3
4 PROCEDURE copy_periods ( P_Org_Id IN NUMBER DEFAULT NULL -- 12i MOAC changes
5 , x_rec_count OUT NOCOPY NUMBER
6 , x_err_text OUT NOCOPY VARCHAR2 )
7 IS
8
9 l_rec_count NUMBER := 0;
10 X_user_id NUMBER(15);
11 X_login_id NUMBER(15);
12 X_sob_id NUMBER(15);
13 x_stage VARCHAR2(100);
14 l_org_Id NUMBER := nvl(P_Org_Id, pa_moac_utils.get_current_org_id); -- 12i MOAC changes
15
16
17 --Bug 3065754
18 l_imp_period_set_name pa_implementations_all.period_set_name%type;
19
20 CURSOR NewPeriods
21 IS
22 SELECT
23 period_name
24 , start_date
25 , end_date
26 , gl_period_name
27 FROM
28 pa_periods_copy_v;
29
30 BEGIN
31
32 x_stage := 'SELECT FROM PA_IMPLEMENTATIONS';
33 --Bug 3065754
34 SELECT set_of_books_id, period_set_name
35 INTO X_sob_id , l_imp_period_set_name
36 FROM pa_implementations;
37
38 X_user_id := fnd_global.user_id;
39 X_login_id := fnd_global.login_id;
40
41 FOR xperiod IN NewPeriods LOOP
42
43 l_rec_count := l_rec_count + 1;
44
45 x_stage := 'INSERT INTO PA_PERIODS';
46 INSERT INTO pa_periods (
47 period_name
48 , last_update_date
49 , last_updated_by
50 , creation_date
51 , created_by
52 , last_update_login
53 , start_date
54 , end_date
55 , status
56 , gl_period_name
57 , current_pa_period_flag
58 , org_id ) -- 12i MOAC changes
59 VALUES (
60 xperiod.period_name
61 , TRUNC(sysdate)
62 , X_user_id
63 , TRUNC(sysdate)
64 , X_user_id
65 , X_login_id
66 , xperiod.start_date
67 , xperiod.end_date
68 , 'N' /* Never Opened */
69 , xperiod.gl_period_name
70 , NULL
71 , l_org_id ); -- 12i MOAC changes
72
73 x_stage := 'INSERT INTO GL_PERIOD_STATUSES';
74 gl_period_statuses_pkg.insert_ps_api(
75 275
76 , X_sob_id
77 , xperiod.period_name
78 , 'O'
79 --Bug 3065754
80 --, NULL
81 , l_imp_period_set_name
82 , X_user_id
83 , X_login_id );
84
85 /* Bug 2579245: Inserting record in gl_period_statuses*/
86 /* gl_period_statuses_pkg.insert_ps_api(
87 275
88 , X_sob_id
89 , xperiod.gl_period_name
90 , 'O'
91 , NULL
92 , X_user_id
93 , X_login_id ); Bug# 3271356:reverting fix of 2579245*/
94
95
96 END LOOP;
97
98 IF ( l_rec_count > 0 ) THEN
99 COMMIT;
100 END IF;
101
102 x_rec_count := l_rec_count;
103
104 EXCEPTION
105 WHEN NO_DATA_FOUND THEN
106 x_rec_count := 0;
107 x_err_text := NULL;
108 WHEN OTHERS THEN
109 x_rec_count := SQLCODE;
110 x_err_text := x_stage || ' - ' || SQLERRM(SQLCODE);
111
112 END copy_periods;
113
114 PROCEDURE copy_from_glperiods ( P_Org_ID IN NUMBER DEFAULT NULL -- 12i MOAC changes
115 , x_rec_count OUT NOCOPY NUMBER
116 , x_err_text OUT NOCOPY VARCHAR2 )
117 IS
118
119 l_rec_count NUMBER := 0;
120 X_user_id NUMBER(15);
121 X_login_id NUMBER(15);
122 X_sob_id NUMBER(15);
123 x_stage VARCHAR2(100);
124 l_org_Id NUMBER := nvl(P_Org_Id, pa_moac_utils.get_current_org_id); -- 12i MOAC changes
125
126 CURSOR C_GlPeriods(p_sob_id in number)
127 IS
128 SELECT
129 period_name
130 , start_date
131 , end_date
132 , closing_status
133 FROM
134 gl_period_statuses gps
135 WHERE gps.application_id = 8721
136 and gps.adjustment_period_flag = 'N'
137 and gps.set_of_books_id = p_sob_id
138 and not exists (
139 SELECT NULL
140 FROM PA_PERIODS pp
141 WHERE PP.PERIOD_NAME = GPS.PERIOD_NAME );
142
143 BEGIN
144
145 x_stage := 'SELECT FROM PA_IMPLEMENTATIONS';
146 SELECT set_of_books_id
147 INTO X_sob_id
148 FROM pa_implementations;
149
150 X_user_id := fnd_global.user_id;
151 X_login_id := fnd_global.login_id;
152
153 FOR xperiod IN C_GlPeriods(X_sob_id) LOOP
154
155 l_rec_count := l_rec_count + 1;
156
157 x_stage := 'INSERT INTO PA_PERIODS';
158 INSERT INTO pa_periods (
159 period_name
160 , last_update_date
161 , last_updated_by
162 , creation_date
163 , created_by
164 , last_update_login
165 , start_date
166 , end_date
167 , status
168 , gl_period_name
169 , current_pa_period_flag
170 , org_id) -- 12i MOAC changes
171 VALUES (
172 xperiod.period_name
173 , TRUNC(sysdate)
174 , X_user_id
175 , TRUNC(sysdate)
176 , X_user_id
177 , X_login_id
178 , xperiod.start_date
179 , xperiod.end_date
180 , xperiod.closing_status
181 , xperiod.period_name
182 , NULL
183 , l_org_id); -- 12i MOAC changes
184
185 /*Bug# 3271356 :Reverted the fix of 2579245*/
186 /* Bug 2579245 : updating the status of gl_period to open */
187 /* x_stage := 'UPDATE GL_PERIOD_STATUSES';
188 UPDATE gl_period_statuses
189 SET closing_status = 'O'
190 WHERE application_id = 8721
191 AND set_of_books_id = X_sob_id
192 AND period_name = xperiod.period_name; Commented for Bug 3271356*/
193
194 END LOOP;
195
196 IF ( l_rec_count > 0 ) THEN
197 COMMIT;
198 END IF;
199
200 x_rec_count := l_rec_count;
201
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 x_rec_count := 0;
205 x_err_text := NULL;
206 WHEN OTHERS THEN
207 x_rec_count := SQLCODE;
208 x_err_text := x_stage || ' - ' || SQLERRM(SQLCODE);
209
210 END copy_from_glperiods;
211
212 /*****************************************************************************
213 Bug# 3271356 :
214 FUNCTION check_gl_period_used_in_pa :This is used by GL to prevent the user
215 trying to manipulate the GL period name into which the PA periods falls.
216 *****************************************************************************/
217
218 FUNCTION check_gl_period_used_in_pa ( p_period_name IN VARCHAR2,
219 p_period_set_name IN VARCHAR2)
220 RETURN VARCHAR2 is
221
222 l_temp varchar2(1);
223 l_period_name pa_periods_all.period_name%TYPE;
224
225 l_pji_name pji_time_cal_period.NAME%TYPE;
226
227 l_rec_count NUMBER;
228
229
230 CURSOR period_cur IS
231 SELECT period_name
232 FROM PA_PERIODS_ALL prd,
233 PA_IMPLEMENTATIONS_ALL imp,
234 GL_SETS_OF_BOOKS sob
235 WHERE (period_name = p_period_name
236 OR gl_period_name = p_period_name)
237 AND prd.org_id = imp.org_id /* Removed the NVL check for bug 6811046*/
238 AND imp.set_of_books_id = sob.set_of_books_id
239 AND sob.period_set_name = p_period_set_name
240 AND rownum =1;
241
242 BEGIN
243
244 l_rec_count := 1;
245
246 Open period_cur;
247 Fetch period_cur into l_period_name;
248
249 If period_cur%NOTFOUND Then
250
251 SELECT COUNT(*)
252 into l_rec_count
253 FROM PA_PERIODS_ALL prd,
254 PA_IMPLEMENTATIONS_ALL imp
255 WHERE ( period_name = p_period_name
256 OR gl_period_name =p_period_name)
257 AND prd.org_id = imp.org_id /* Removed the NVL check for bug 6811046*/
258 AND period_set_name = p_period_set_name --added this condition for the bug 4119508
259 AND rownum =1;
260
261 End if ;
262
263 Close period_cur;
264
265
266 /* Adding the condition to check GL period already extracted in PJI */
267
268
269 IF (l_rec_count = 0 ) THEN
270
271 SELECT COUNT(*)
272 INTO l_rec_count
273 FROM pa_time_cal_name cal, /* Modified for bug 12979524 */
274 pji_time_cal_period prd
275 WHERE cal.period_set_name = p_period_set_name
276 AND prd.name = p_period_name
277 AND prd.calendar_id = cal.calendar_id
278 AND ROWNUM =1;
279
280 END IF;
281
282
283 IF (l_rec_count = 0) THEN
284
285 RETURN ('N');
286
287 else
288
289 RETURN ('Y');
290
291 END IF;
292
293 EXCEPTION
294 WHEN no_data_found THEN
295 RETURN ('N');
296 WHEN others THEN
297 Raise;
298 END check_gl_period_used_in_pa;
299 /*End of changes for Bug# 3271356 */
300
301 END pa_periods_pkg;