1 PACKAGE BODY PA_PERIOD_PROCESS_PKG as
2 -- $Header: PAGLPKGB.pls 120.2 2006/07/14 06:18:17 anuagraw noship $
3
4 G_PrevOrgId NUMBER;
5 G_PrevRetValue VARCHAR2(30);
6
7 --
8 -- Function : Is_Enabled
9 -- Purpose : This functions returns true if the profile option
10 -- PA_EN_NEW_GLDATE_DERIVATION is set as 'Y' otherwise flase.
11 -- Parameters : None.
12 --
13
14 FUNCTION Is_Enabled RETURN VARCHAR2 IS
15
16 l_return_value VARCHAR(30);
17 BEGIN
18
19 IF PA_PERIOD_PROCESS_PKG.Enable_New_GL_Date_Der is NULL THEN
20 FND_PROFILE.GET('PA_EN_NEW_GLDATE_DERIVATION',l_return_value );
21
22 if ( l_return_value = 'Y' ) then
23 PA_PERIOD_PROCESS_PKG.Enable_New_GL_Date_Der := 'Y' ;
24 else
25 PA_PERIOD_PROCESS_PKG.Enable_New_GL_Date_Der := 'N' ;
26 end if;
27 END IF;
28 return (PA_PERIOD_PROCESS_PKG.Enable_New_GL_Date_Der) ;
29
30 END Is_enabled ;
31
32 --
33 -- Function : Application_Id
34 -- Purpose : This functions returns true if the profile option
35 -- PA_EN_NEW_GLDATE_DERIVATION is set as 'Y' otherwise flase.
36 -- Parameters : None.
37 --
38
39 FUNCTION Application_Id RETURN NUMBER IS
40
41 l_return_value VARCHAR(30);
42 BEGIN
43
44 IF PA_PERIOD_PROCESS_PKG.G_Application_Id is NULL THEN
45 FND_PROFILE.GET('PA_EN_NEW_GLDATE_DERIVATION',l_return_value );
46
47 if ( l_return_value = 'Y' ) then
48 PA_PERIOD_PROCESS_PKG.G_Application_Id := 8721 ;
49 else
50 PA_PERIOD_PROCESS_PKG.G_Application_Id := 101 ;
51 end if;
52 END IF;
53 return (PA_PERIOD_PROCESS_PKG.G_Application_Id) ;
54
55 END Application_Id ;
56
57 --
58 -- Function : Use_Same_PA_GL_Period
59 -- Purpose : This functions returns 'Y' if the implementation option
60 -- Maintain Common PA and GL Periods is set as 'Y' otherwise 'N'.
61 -- Parameters : None.
62 --
63 -- Bug#2103722
64 -- Added new parameter p_org_id and necessary join.
65 --
66
67 FUNCTION Use_Same_PA_GL_Period(p_org_id IN pa_implementations_all.org_id%TYPE) RETURN VARCHAR2 IS
68 l_return_value VARCHAR(30);
69 BEGIN
70
71 If G_PrevOrgId = nvl(p_org_id,-99) Then
72
73 l_return_value := G_PrevRetValue;
74
75 Else
76
77 select nvl(imp.same_pa_gl_period,'N')
78 into l_return_value
79 from pa_implementations_all imp
80 where imp.org_id = nvl(p_org_id, -99); /*5368274*/
81
82 G_PrevOrgId := nvl(p_org_id,-99);
83 G_PrevRetValue := l_return_value;
84
85 End If;
86
87 return (l_return_value) ;
88
89 EXCEPTION
90 when others then
91 G_PrevOrgId := nvl(p_org_id,-99);
92 G_PrevRetValue := 'N';
93 return 'N';
94 END Use_Same_PA_GL_Period ;
95
96 --
97 -- Procedure : Update_PA_Period_Status
98 -- Purpose : This procedure will update the PA period status when the
99 -- Implementation option - Maintain Common PA and GL Periods
100 -- is set to Yes and the Profile - Enable Enhanced Period
101 -- Processing is set to Yes.
102 -- This API is called from the GL Periods Form - PAXPAGLP.fmb
103 -- Parameters : None.
104 --
105 PROCEDURE Update_PA_Period_Status is
106
107 cursor c_periods is
108 select b.period_name, b.closing_status, b.set_of_books_id ,
109 b.last_update_date, b.last_updated_by, b.last_update_login /* added bug 3111150 */
110 from pa_periods a, gl_period_statuses b
111 where a.period_name = b.period_name
112 and b.application_id = 8721
113 and b.set_of_books_id = (select set_of_books_id from pa_implementations)
114 and a.start_date = b.start_date
115 and a.end_Date = b.end_date
116 and a.status <> b.closing_status;
117
118 l_PeriodTab PA_PLSQL_DATATYPES.Char15TabTyp;
119 l_StatusTab PA_PLSQL_DATATYPES.Char1TabTyp;
120 l_SobId PA_PLSQL_DATATYPES.IdTabTyp;
121 l_LastUpdateDate PA_PLSQL_DATATYPES.DateTabTyp; /* added bug 3111150 */
122 l_LastUpdatedBy PA_PLSQL_DATATYPES.IdTabTyp; /* added bug 3111150 */
123 l_LastUpdateLogin PA_PLSQL_DATATYPES.IdTabTyp; /* added bug 3111150 */
124
125 begin
126 open c_periods;
127 loop
128 l_PeriodTab.Delete;
129 l_StatusTab.Delete;
130 l_SobId.Delete;
131 l_LastUpdateDate.Delete; /* added bug 3111150 */
132 l_LastUpdatedBy.Delete; /* added bug 3111150 */
133 l_LastUpdateLogin.Delete; /* added bug 3111150 */
134
135 fetch c_periods bulk collect into
136 l_PeriodTab,
137 l_StatusTab,
138 l_SobId,
139 l_LastUpdateDate, /* added bug 3111150 */
140 l_LastUpdatedBy, /* added bug 3111150 */
141 l_LastUpdateLogin /* added bug 3111150 */
142 limit 200;
143
144 if l_PeriodTab.count = 0 then
145 exit;
146 end if;
147
148 forall i in l_PeriodTab.first..l_PeriodTab.last
149 update pa_periods_all pp
150 set pp.status = l_StatusTab(i),
151 pp.last_update_date = l_LastUpdateDate(i), /* added bug 3111150 */
152 pp.last_updated_by = l_LastUpdatedBy(i), /* added bug 3111150 */
153 pp.last_update_login = l_LastUpdateLogin(i) /* added bug 3111150 */
154 where pp.period_name = l_PeriodTab(i)
155 and pp.org_id in (select imp.org_id
156 from pa_implementations_all imp
157 where nvl(imp.same_pa_gl_period,'N') = 'Y'
158 and imp.set_of_books_id = l_SobId(i));
159
160 commit;
161 exit when c_periods%notfound;
162 end loop;
163 close c_periods;
164
165 exception
166 when others then
167 IF c_periods%ISOPEN THEN
168 close c_periods;
169 END IF;
170 raise;
171 end Update_PA_Period_Status;
172
173 --Procedure : Check_Imp_Option_Controls
174 --Purpose : This procedure is called from the implementation form
175 -- when the user changes the option - Maintain Common PA
176 -- and GL Periods from N to Y.
177 -- The checks that are performed are as follows:
178 -- 1. See if the calendar and period_type for GL and PA
179 -- period are the same
180 -- 2. Check if the profile PA: Enable Enhanced Period Processing
181 -- is enabled.
182 -- 3. Check if the PA period status is in sync with the GL period
183 -- status
184 PROCEDURE Check_Imp_Option_Controls(
185 p_period_set_name in varchar2,
186 p_pa_period_type in varchar2,
187 p_sob_id in number,
188 p_org_id in number,
189 x_return_status out nocopy varchar2,
190 x_error_message_code out nocopy varchar2) AS
191
192 lv_period_set_name VARCHAR2(30);
193 lv_pa_period_type VARCHAR2(30);
194 lv_sob_id NUMBER;
195 lv_org_id NUMBER;
196 lv_gl_period_type VARCHAR2(30);
197 lv_gl_period_set_name VARCHAR2(30);
198 lv_enabled_flag VARCHAR2(30);
199 lv_pa_period_name VARCHAR2(30);
200 lv_return_status VARCHAR2(30);
201 lv_error_message_code VARCHAR2(30);
202 lv_exception EXCEPTION;
203
204 --This cursor is used to check whether there are any periods
205 --where the the pa_period staus does not match the gl_period status
206 CURSOR c_Mismatch_Status IS
207 SELECT pa.period_name
208 FROM pa_periods pa,
209 gl_period_statuses gl
210 WHERE gl.set_of_books_id=p_sob_id
211 AND gl.application_id=8721
212 AND pa.period_name=gl.period_name
213 AND pa.status <>gl.closing_status;
214
215 BEGIN
216
217 SELECT Period_Set_Name,
218 Accounted_Period_Type
219 INTO lv_gl_period_set_name,
220 lv_gl_period_type
221 FROM gl_sets_of_books
222 WHERE Set_of_Books_ID=p_sob_id;
223
224 -- This SELECT statement is used to check whether the GL Period Name and
225 -- type match those on the PA side
226
227 IF lv_gl_period_set_name = p_period_set_name AND
228 lv_gl_period_type = p_pa_period_type THEN
229
230 --Calling the function to check whether the option in implementation is turned on
231
232 lv_enabled_flag := PA_PERIOD_PROCESS_PKG.IS_Enabled;
233
234 IF lv_enabled_flag='Y' THEN
235
236 OPEN c_Mismatch_Status;
237
238 LOOP
239
240 FETCH c_Mismatch_Status INTO lv_pa_period_name;
241
242 IF c_Mismatch_Status%NOTFOUND THEN
243
244 lv_return_status:='S';
245 EXIT;
246
247 ELSIF lv_pa_period_name IS NOT NULL THEN
248
249 lv_error_message_code:='PA_GL_PER_STS_MISMATCH';
250 lv_return_status:='F';
251 EXIT;
252 END IF;
253
254 END LOOP;
255
256 ELSE
257
258 lv_error_message_code:='PA_GL_PER_IMP_NOT_ENABLED';
259 lv_return_status:='F';
260
261 END IF;
262
263 ELSE
264
265 lv_error_message_code:='PA_GL_PER_TYP_MISMATCH';
266 lv_return_status:='F';
267
268 END IF;
269
270 x_return_status:=lv_return_status;
271 x_error_message_code:=lv_error_message_code;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 x_return_status := 'F';
276 x_error_message_code := Null;
277 RAISE;
278
279 END Check_Imp_Option_Controls;
280
281 END PA_PERIOD_PROCESS_PKG;
[Home] [Help]