[Home] [Help]
PACKAGE BODY: APPS.PA_GL_AUTOALLOC_PKG
Source
1 PACKAGE BODY PA_GL_AUTOALLOC_PKG AS
2 /* $Header: PAXGLAAB.pls 120.3 2005/09/29 14:54:08 dlanka noship $ */
3 ----------------------------------------------------------------------------
4 FUNCTION GET_PERIOD_TYPE ( p_allocation_set_id Number )
5 RETURN CHAR
6 IS
7
8 v_pa_period CHAR(1) := 'N';
9 v_gl_period CHAR(1) := 'N';
10 v_period_type VARCHAR2(2);
11
12 CURSOR C_Period
13 IS
14 SELECT DISTINCT a.period_type
15 FROM pa_alloc_rules_all a,
16 gl_auto_alloc_batches b
17 WHERE b.allocation_set_id = p_allocation_set_id
18 AND b.batch_type_code = 'P'
19 AND a.rule_id = b.batch_id;
20
21 BEGIN
22
23 OPEN C_Period;
24 LOOP
25
26 FETCH C_Period
27 INTO v_period_type ;
28
29 EXIT WHEN C_Period%NOTFOUND;
30
31 IF v_Period_Type = 'PA' THEN
32 v_pa_period := 'Y';
33 ELSIF v_Period_Type = 'GL' THEN
34 v_gl_period := 'Y';
35 END IF;
36
37 END LOOP;
38
39 CLOSE C_Period;
40
41 IF v_pa_period = 'N' AND v_gl_period = 'N' THEN
42 RETURN 'N';
43 ELSIF v_pa_period = 'Y' AND v_gl_period = 'Y' THEN
44 RETURN 'B';
45 ELSIF v_pa_period = 'N' AND v_gl_period = 'Y' THEN
46 RETURN 'G';
47 ELSIF v_pa_period = 'Y' AND v_gl_period = 'N' THEN
48 RETURN 'P';
49 END IF;
50
51 END GET_PERIOD_TYPE;
52 ----------------------------------------------------------------------------
53 FUNCTION Valid_Run_Period ( p_allocation_set_id IN Number,
54 p_pa_period IN Varchar2
55 default Null,
56 p_gl_period IN Varchar2
57 default Null)
58
59 RETURN BOOLEAN
60 IS
61
62 v_period_type CHAR(1);
63
64 BEGIN
65
66 v_period_type := Get_Period_Type (p_allocation_set_id);
67
68 if v_period_type = 'N' then
69 return TRUE;
70 elsif v_period_type = 'P' then
71 if p_pa_period is not null then
72 return TRUE;
73 else
74 return FALSE;
75 end if;
76 elsif v_period_type = 'G' then
77 if p_gl_period is not null then
78 return TRUE;
79 else
80 return FALSE;
81 end if;
82 elsif v_period_type = 'B' then
83 if ((p_pa_period is not null) and (p_gl_period is not null)) then
84 return TRUE;
85 else
86 return FALSE;
87 end if;
88 end if;
89
90 END Valid_Run_Period;
91 ------------------------------------------------------------------------------
92 Function Submit_Alloc_Request( p_rule_id IN Number,
93 p_expnd_item_date IN Date,
94 p_pa_period IN Varchar2,
95 p_gl_period IN Varchar2
96 )
97 Return Number
98 IS
99
100 v_run_period Varchar2(15);
101 v_period_type Varchar2(2);
102 v_request_id Number;
103 v_expnd_item_date Varchar2(20);
104 l_org_id Number;
105
106 BEGIN
107
108 /** Find out the run_period (pa/gl period) to be passed **/
109 select period_type, org_id -- Fix for bug : 4640479
110 into v_period_type , l_org_id
111 from pa_alloc_rules_all
112 where rule_id = p_rule_id;
113
114 /* dbms_output.put_line('Period Type = '||v_period_type); */
115
116 IF v_period_type = 'GL' THEN
117 v_run_period := p_gl_period;
118 ELSIF v_period_type = 'PA' THEN
119 v_run_period := p_pa_period;
120 END IF;
121
122 /** Convert the date parameter to varchar2 to make use of FND_REQUEST.SUBMIT
123 _REQUEST **/
124 v_expnd_item_date := fnd_date.date_to_canonical (p_expnd_item_date);
125
126 fnd_request.set_org_id (l_org_id);
127 v_request_id :=
128 FND_REQUEST.SUBMIT_REQUEST(
129 'PA',
130 'PAXALGAT',
131 '',
132 '',
133 FALSE,
134 p_rule_id,
135 v_run_period,
136 v_expnd_item_date
137 ,'G'
138 ,'','','','','','','','','','','','','','',''
139 ,'','','','','','','','','','','','','','',''
140 ,'','','','','','','','','','','','','','',''
141 ,'','','','','','','','','','','','','','',''
142 ,'','','','','','','','','','','','','','',''
143 ,'','','','','','','','','','','','','','',''
144 ,'','','','','','');
145
146 Return v_request_id;
147
148 END Submit_Alloc_Request;
149 ------------------------------------------------------------------------------
150 Procedure get_pa_step_status (
151 p_request_Id In Number
152 ,p_step_number In Number
153 ,p_mode In Varchar2
154 ,l_status Out NOCOPY Varchar2) IS
155
156 v_meaning Varchar2(80);
157 v_description Varchar2(240);
158 v_status_code Varchar2(30);
159 v_lookup_code Varchar2(30);
160 v_request_id Number := p_request_id;
161
162 v_phase Varchar2(30);
163 v_status Varchar2(30);
164 v_dev_phase Varchar2(30);
165 v_dev_status Varchar2(30);
166 v_message Varchar2(240);
167 v_call_status Boolean;
168
169 Cursor Get_status_Code_C IS
170 Select Status_Code
171 From GL_AUTO_ALLOC_BATCH_HISTORY
172 Where REQUEST_ID = p_request_Id
173 AND STEP_NUMBER = p_step_number;
174
175 Cursor Get_Status_Meaning_C IS
176 Select
177 Meaning
178 ,Description
179 From gl_lookups
180 Where LOOKUP_TYPE = 'AUTOALLOCATION_STATUS'
181 And LOOKUP_CODE = v_lookup_code;
182 Cursor get_request_id_C IS
183 Select request_id
184 From GL_AUTO_ALLOC_BAT_HIST_DET
185 Where PARENT_REQUEST_ID = p_request_Id
186 And STEP_NUMBER = p_step_number
187 order by request_id desc;
188
189 Begin
190
191 If p_mode = 'SD' Then
192 -- Mode is step-down
193 If p_request_id is Null Or
194 p_step_number is Null Then
195 l_status := NULL;
196 return;
197 End If;
198
199 Open Get_status_Code_C;
200 Fetch Get_status_Code_C into v_status_code;
201 If Get_status_Code_C%NOTFOUND Then
202 l_status := NULL;
203 Close Get_status_Code_C;
204 return;
205 End If;
206 Close Get_status_Code_C;
207
208 If v_status_code in ('ALPP','DCPP','UPPP','RLAPP','RALPP',
209 'RDCPP','RUPPP' ) Then
210 -- Find whether pending request is presently running or completed
211 Open get_request_id_C;
212 Fetch get_request_id_C into v_request_id;
213 Close get_request_id_C;
214
215 v_call_status :=
216 fnd_concurrent.get_request_status(
217 v_request_Id
218 ,'PA'
219 ,NULL
220 ,v_phase
221 ,v_status
222 ,v_dev_phase
223 ,v_dev_status
224 ,v_message
225 );
226
227 If v_dev_phase = 'COMPLETE' AND
228 v_dev_status In ('ERROR','CANCELLED','TERMINATED') Then
229
230 if v_status_code = 'ALPP' Then
231 v_status_code := 'ALPF';
232 Elsif v_status_code = 'DCPP' Then
233 v_status_code := 'DCPF';
234 Elsif v_status_code = 'RLALPP' Then
235 v_status_code := 'RLALPF';
236 ELsif v_status_code = 'UPPP' Then
237 v_status_code := 'UPPF';
238 Elsif v_status_code = 'RALPP' Then
239 v_status_code := 'RALPF';
240 Elsif v_status_code = 'RDCPP' Then
241 v_status_code := 'RDCPF';
242 Elsif v_status_code = 'RUPPP' Then
243 v_status_code := 'RUPPP';
244 End If;
245
246 ElsIf v_dev_phase = 'COMPLETE' AND
247 v_dev_status = 'NORMAL' Then
248
249 If v_status_code = 'ALPP' Then
250 v_status_code := 'ALPC' ;
251 ElsIf v_status_code = 'RLALPP' Then
252 v_status_code := 'RLALPC' ;
253 ElsIf v_status_code = 'DCPP' Then
254 v_status_code := 'DCPC' ;
255
256 ElsIf v_status_code = 'UPPP' Then
257 v_status_code := 'UPPC' ;
258 ElsIf v_status_code = 'RALPP' Then
259 v_status_code := 'RALPC' ;
260 ElsIf v_status_code = 'RDCPP' Then
261 v_status_code := 'RDCPC' ;
262 ElsIf v_status_code = 'RUPPP' Then
263 v_status_code := 'RUPPC' ;
264 End If;
265
266 ElsIf v_dev_phase = 'RUNNING' AND
267 v_dev_status in ('NORMAL') Then
268
269 If v_status_code = 'ALPP' Then
270 v_status_code := 'ALPR' ;
271 ElsIf v_status_code = 'RLALPP' Then
272 v_status_code := 'RLALPR' ;
273 ElsIf v_status_code = 'DCPP' Then
274 v_status_code := 'DCPR' ;
275 ElsIf v_status_code = 'UPPP' Then
276 v_status_code := 'UPPR' ;
277 ElsIf v_status_code = 'RALPP' Then
278 v_status_code := 'RALPR' ;
279 ElsIf v_status_code = 'RDCPP' Then
280 v_status_code := 'RDCPR' ;
281 ElsIf v_status_code = 'RUPPP' Then
282
283 v_status_code := 'RUPPR' ;
284 End If;
285
286 End If;
287 End If;
288 v_lookup_code := v_status_code;
289
290 Open Get_Status_Meaning_C;
291 Fetch Get_Status_Meaning_C into
292 v_Meaning, v_description;
293
294 If Get_Status_Meaning_C%NOTFOUND Then
295 FND_MESSAGE.Set_Name('SQLGL', 'GL_AUTO_ALLOC_STATUS_ERR');
296 l_status := FND_MESSAGE.Get;
297 Close Get_Status_Meaning_C;
298 return;
299 Else
300 Close Get_Status_Meaning_C;
301 l_status := v_description;
302 End If;
303 Else
304 -- if not step down
305 v_call_status :=
306 fnd_concurrent.get_request_status(
307 v_request_Id
308 ,'PA'
309 ,NULL
310 ,v_phase
311 ,v_status
312 ,v_dev_phase
313 ,v_dev_status
314 ,v_message
315 );
316
317 If v_dev_phase = 'COMPLETE' AND
318 v_dev_status = 'NORMAL' Then
319 l_status := v_dev_phase;
320
321 ElsIf v_dev_phase = 'COMPLETE' AND
322 v_dev_status <> 'NORMAL' Then
323 l_status := v_dev_status;
324 Else
325 l_status := v_dev_phase;
326 End If;
327 End If;
328 End get_pa_step_status;
329
330 ------------------------------------------------------------------------------
331 END PA_GL_AUTOALLOC_PKG;