[Home] [Help]
PACKAGE BODY: APPS.GL_AUTO_ALLOC_BATCH_PKG
Source
1 PACKAGE BODY gl_auto_alloc_batch_pkg AS
2 /* $Header: glatalbb.pls 120.3 2005/05/05 02:02:08 kvora ship $ */
3 SUCCESS CONSTANT VARCHAR2(1) := 'S';
4 FAILURE CONSTANT VARCHAR2(1) := 'F';
5
6 Procedure Insert_Allocation_Batch(
7 l_Row_Id IN OUT NOCOPY VARCHAR2
8 , l_ALLOCATION_SET_ID IN NUMBER
9 , l_BATCH_ID IN NUMBER
10 , l_BATCH_TYPE_CODE IN VARCHAR2
11 , l_LAST_UPDATE_DATE IN DATE
12 , l_LAST_UPDATED_BY IN NUMBER
13 , l_LAST_UPDATE_LOGIN IN NUMBER
14 , l_CREATION_DATE IN DATE
15 , l_CREATED_BY IN NUMBER
16 , l_STEP_NUMBER IN NUMBER
17 , l_OWNER IN VARCHAR2
18 , l_ALLOCATION_METHOD_CODE IN VARCHAR2
19 , l_ATTRIBUTE1 IN VARCHAR2
20 , l_ATTRIBUTE2 IN VARCHAR2
21 , l_ATTRIBUTE3 IN VARCHAR2
22 , l_ATTRIBUTE4 IN VARCHAR2
23 , l_ATTRIBUTE5 IN VARCHAR2
24 , l_ATTRIBUTE6 IN VARCHAR2
25 , l_ATTRIBUTE7 IN VARCHAR2
26 , l_ATTRIBUTE8 IN VARCHAR2
27 , l_ATTRIBUTE9 IN VARCHAR2
28 , l_ATTRIBUTE10 IN VARCHAR2
29 , l_ATTRIBUTE11 IN VARCHAR2
30 , l_ATTRIBUTE12 IN VARCHAR2
31 , l_ATTRIBUTE13 IN VARCHAR2
32 , l_ATTRIBUTE14 IN VARCHAR2
33 , l_ATTRIBUTE15 IN VARCHAR2
34 , l_CONTEXT IN VARCHAR2
35 ) IS
36
37 CURSOR C IS
38 SELECT rowid
39 FROM GL_AUTO_ALLOC_BATCHES
40 WHERE ALLOCATION_SET_ID = l_ALLOCATION_SET_ID
41 AND BATCH_ID = l_BATCH_ID
42 And BATCH_TYPE_CODE = l_BATCH_TYPE_CODE;
43
44 Begin
45 Insert Into GL_AUTO_ALLOC_BATCHES
46 (
47 ALLOCATION_SET_ID
48 , BATCH_ID
49 , BATCH_TYPE_CODE
50 , LAST_UPDATE_DATE
51 , LAST_UPDATED_BY
52 , LAST_UPDATE_LOGIN
53 , CREATION_DATE
54 , CREATED_BY
55 , STEP_NUMBER
56 , OWNER
57 , ALLOCATION_METHOD_CODE
58 , ATTRIBUTE1
59 , ATTRIBUTE2
60 , ATTRIBUTE3
61 , ATTRIBUTE4
62 , ATTRIBUTE5
63 , ATTRIBUTE6
64 , ATTRIBUTE7
65 , ATTRIBUTE8
66 , ATTRIBUTE9
67 , ATTRIBUTE10
68 , ATTRIBUTE11
69 , ATTRIBUTE12
70 , ATTRIBUTE13
71 , ATTRIBUTE14
72 , ATTRIBUTE15
73 , CONTEXT
74 )
75 Values(
76 l_ALLOCATION_SET_ID
77 , l_BATCH_ID
78 , l_BATCH_TYPE_CODE
79 , l_LAST_UPDATE_DATE
80 , l_LAST_UPDATED_BY
81 , l_LAST_UPDATE_LOGIN
82 , l_CREATION_DATE
83 , l_CREATED_BY
84 , l_STEP_NUMBER
85 , l_OWNER
86 , l_ALLOCATION_METHOD_CODE
87 , l_ATTRIBUTE1
88 , l_ATTRIBUTE2
89 , l_ATTRIBUTE3
90 , l_ATTRIBUTE4
91 , l_ATTRIBUTE5
92 , l_ATTRIBUTE6
93 , l_ATTRIBUTE7
94 , l_ATTRIBUTE8
95 , l_ATTRIBUTE9
96 , l_ATTRIBUTE10
97 , l_ATTRIBUTE11
98 , l_ATTRIBUTE12
99 , l_ATTRIBUTE13
100 , l_ATTRIBUTE14
101 , l_ATTRIBUTE15
102 , l_CONTEXT
103 );
104 OPEN C;
105 FETCH C INTO l_Row_id ;
106 If (C%NOTFOUND) then
107 CLOSE C;
108 Raise NO_DATA_FOUND;
109 End If;
110 CLOSE C;
111 End Insert_Allocation_Batch;
112
113 Procedure Update_Allocation_Batch(
114 l_Row_Id IN VARCHAR2
115 , l_BATCH_ID IN NUMBER
116 , l_BATCH_TYPE_CODE IN VARCHAR2
117 , l_LAST_UPDATE_DATE IN DATE
118 , l_LAST_UPDATED_BY IN NUMBER
119 , l_LAST_UPDATE_LOGIN IN NUMBER
120 , l_STEP_NUMBER IN NUMBER
121 , l_OWNER IN VARCHAR2
122 , l_ALLOCATION_METHOD_CODE IN VARCHAR2
123 , l_ATTRIBUTE1 IN VARCHAR2
124 , l_ATTRIBUTE2 IN VARCHAR2
125 , l_ATTRIBUTE3 IN VARCHAR2
126 , l_ATTRIBUTE4 IN VARCHAR2
127 , l_ATTRIBUTE5 IN VARCHAR2
128 , l_ATTRIBUTE6 IN VARCHAR2
129 , l_ATTRIBUTE7 IN VARCHAR2
130 , l_ATTRIBUTE8 IN VARCHAR2
131 , l_ATTRIBUTE9 IN VARCHAR2
132 , l_ATTRIBUTE10 IN VARCHAR2
133 , l_ATTRIBUTE11 IN VARCHAR2
134 , l_ATTRIBUTE12 IN VARCHAR2
135 , l_ATTRIBUTE13 IN VARCHAR2
136 , l_ATTRIBUTE14 IN VARCHAR2
137 , l_ATTRIBUTE15 IN VARCHAR2
138 , l_CONTEXT IN VARCHAR2
139 ) IS
140 Begin
141 Update GL_AUTO_ALLOC_BATCHES
142 Set
143 Batch_id = l_BATCH_ID
144 , BATCH_TYPE_CODE = l_BATCH_TYPE_CODE
145 , STEP_NUMBER = l_STEP_NUMBER
146 , OWNER = l_OWNER
147 , ALLOCATION_METHOD_CODE = l_ALLOCATION_METHOD_CODE
148 , LAST_UPDATE_DATE = l_LAST_UPDATE_DATE
149 , LAST_UPDATED_BY = l_LAST_UPDATED_BY
150 , LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN
151 , ATTRIBUTE1 = l_ATTRIBUTE1
152 , ATTRIBUTE2 = l_ATTRIBUTE2
153 , ATTRIBUTE3 = l_ATTRIBUTE3
154 , ATTRIBUTE4 = l_ATTRIBUTE4
155 , ATTRIBUTE5 = l_ATTRIBUTE5
156 , ATTRIBUTE6 = l_ATTRIBUTE6
157 , ATTRIBUTE7 = l_ATTRIBUTE7
158 , ATTRIBUTE8 = l_ATTRIBUTE8
159 , ATTRIBUTE9 = l_ATTRIBUTE9
160 , ATTRIBUTE10 = l_ATTRIBUTE10
161 , ATTRIBUTE11 = l_ATTRIBUTE11
162 , ATTRIBUTE12 = l_ATTRIBUTE12
163 , ATTRIBUTE13 = l_ATTRIBUTE13
164 , ATTRIBUTE14 = l_ATTRIBUTE14
165 , ATTRIBUTE15 = l_ATTRIBUTE15
166 , CONTEXT = l_CONTEXT
167 Where rowid = l_row_id;
168
169 End Update_Allocation_batch;
170
171
172 Procedure Delete_Allocation_batch(
173 l_Row_id IN VARCHAR2
174 ) Is
175 Begin
176 Delete From GL_AUTO_ALLOC_BATCHES
177 Where RowId = l_Row_id;
178 If (SQL%NOTFOUND) then
179 Raise NO_DATA_FOUND;
180 End If;
181 End Delete_Allocation_batch;
182
183
184 Procedure Lock_allocation_batch
185 (
186 l_Row_Id IN VARCHAR2
187 , l_ALLOCATION_SET_ID IN NUMBER
188 , l_BATCH_ID IN NUMBER
189 , l_BATCH_TYPE_CODE IN VARCHAR2
190 , l_LAST_UPDATED_BY IN NUMBER
191 , l_LAST_UPDATE_LOGIN IN NUMBER
192 , l_STEP_NUMBER IN NUMBER
193 , l_OWNER IN VARCHAR2
194 , l_ALLOCATION_METHOD_CODE IN VARCHAR2
195 , l_ATTRIBUTE1 IN VARCHAR2
196 , l_ATTRIBUTE2 IN VARCHAR2
197 , l_ATTRIBUTE3 IN VARCHAR2
198 , l_ATTRIBUTE4 IN VARCHAR2
199 , l_ATTRIBUTE5 IN VARCHAR2
200 , l_ATTRIBUTE6 IN VARCHAR2
201 , l_ATTRIBUTE7 IN VARCHAR2
202 , l_ATTRIBUTE8 IN VARCHAR2
203 , l_ATTRIBUTE9 IN VARCHAR2
204 , l_ATTRIBUTE10 IN VARCHAR2
205 , l_ATTRIBUTE11 IN VARCHAR2
206 , l_ATTRIBUTE12 IN VARCHAR2
207 , l_ATTRIBUTE13 IN VARCHAR2
208 , l_ATTRIBUTE14 IN VARCHAR2
209 , l_ATTRIBUTE15 IN VARCHAR2
210 , l_CONTEXT IN VARCHAR2
211 ) IS
212 CURSOR C IS
213 SELECT *
214 FROM GL_AUTO_ALLOC_BATCHES
215 WHERE rowid = l_ROW_ID
216 FOR UPDATE NOWAIT;
217 Recinfo C%ROWTYPE;
218
219 BEGIN
220 OPEN C;
221 FETCH C INTO Recinfo;
222
223 If (C%NOTFOUND) then
224 CLOSE C;
225 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
226 APP_EXCEPTION.Raise_Exception;
227 End If;
228
229 CLOSE C;
230
231 If (
232 Recinfo.ALLOCATION_SET_ID = l_ALLOCATION_SET_ID
233 And Recinfo.BATCH_ID = l_BATCH_ID
234 And Recinfo.BATCH_TYPE_CODE = l_BATCH_TYPE_CODE
235 And Recinfo.STEP_NUMBER = l_STEP_NUMBER
236 And ( Recinfo.OWNER = l_OWNER
237 OR ( Recinfo.OWNER IS NULL AND
238 l_OWNER IS NULL )
239 )
240 And ( Recinfo.ALLOCATION_METHOD_CODE = l_ALLOCATION_METHOD_CODE
241 OR (Recinfo.ALLOCATION_METHOD_CODE IS NULL AND
242 l_ALLOCATION_METHOD_CODE IS NULL )
243 )
244 And ( Recinfo.ATTRIBUTE1 = l_ATTRIBUTE1
245 OR ( Recinfo.ATTRIBUTE1 IS NULL AND
246 l_ATTRIBUTE1 IS NULL ))
247 And ( Recinfo.ATTRIBUTE2 = l_ATTRIBUTE2
248 OR ( Recinfo.ATTRIBUTE2 IS NULL AND
249 l_ATTRIBUTE2 IS NULL ))
250 And ( Recinfo.ATTRIBUTE3 = l_ATTRIBUTE3
251 OR ( Recinfo.ATTRIBUTE3 IS NULL AND
252 l_ATTRIBUTE3 IS NULL ))
253 And ( Recinfo.ATTRIBUTE4 = l_ATTRIBUTE4
254 OR ( Recinfo.ATTRIBUTE4 IS NULL AND
255 l_ATTRIBUTE4 IS NULL ))
256 And ( Recinfo.ATTRIBUTE5 = l_ATTRIBUTE5
257 OR ( Recinfo.ATTRIBUTE5 IS NULL AND
258 l_ATTRIBUTE5 IS NULL ))
259 And ( Recinfo.ATTRIBUTE6 = l_ATTRIBUTE6
260 OR ( Recinfo.ATTRIBUTE6 IS NULL AND
261 l_ATTRIBUTE6 IS NULL ))
262 And ( Recinfo.ATTRIBUTE7 = l_ATTRIBUTE7
263 OR ( Recinfo.ATTRIBUTE7 IS NULL AND
264 l_ATTRIBUTE7 IS NULL ))
265 And ( Recinfo.ATTRIBUTE8 = l_ATTRIBUTE8
266 OR ( Recinfo.ATTRIBUTE8 IS NULL AND
267 l_ATTRIBUTE8 IS NULL ))
268 And ( Recinfo.ATTRIBUTE9 = l_ATTRIBUTE9
269 OR ( Recinfo.ATTRIBUTE9 IS NULL AND
270 l_ATTRIBUTE9 IS NULL ))
271 And ( Recinfo.ATTRIBUTE10 = l_ATTRIBUTE10
272 OR ( Recinfo.ATTRIBUTE10 IS NULL AND
273 l_ATTRIBUTE10 IS NULL ))
274 And ( Recinfo.ATTRIBUTE11 = l_ATTRIBUTE11
275 OR ( Recinfo.ATTRIBUTE11 IS NULL AND
276 l_ATTRIBUTE11 IS NULL ))
277 And ( Recinfo.ATTRIBUTE12 = l_ATTRIBUTE12
278 OR ( Recinfo.ATTRIBUTE12 IS NULL AND
279 l_ATTRIBUTE12 IS NULL ))
280 And ( Recinfo.ATTRIBUTE13 = l_ATTRIBUTE13
281 OR ( Recinfo.ATTRIBUTE13 IS NULL AND
282 l_ATTRIBUTE13 IS NULL ))
283 And ( Recinfo.ATTRIBUTE14 = l_ATTRIBUTE14
284 OR ( Recinfo.ATTRIBUTE14 IS NULL AND
285 l_ATTRIBUTE14 IS NULL ))
286 And ( Recinfo.ATTRIBUTE15 = l_ATTRIBUTE15
287 OR ( Recinfo.ATTRIBUTE15 IS NULL AND
288 l_ATTRIBUTE15 IS NULL ))
289 And ( Recinfo.Context = l_Context
290 OR ( Recinfo.Context IS NULL AND
291 l_Context IS NULL ))
292
293 ) Then
294 Return;
295 Else
296 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
297 APP_EXCEPTION.RAISE_EXCEPTION;
298 End If;
299 End Lock_allocation_batch;
300
301 PROCEDURE Check_Unique_Step( l_rowid IN VARCHAR2
302 ,l_step_number IN NUMBER
303 ,l_allocation_set_id IN NUMBER
304 ,l_step_label IN VARCHAR2 ) IS
305 Cursor c_dup IS
306 Select 'Duplicate'
307 From gl_auto_alloc_batches r
308 Where r.step_number = l_step_number
309 And r.allocation_set_id = l_allocation_set_id
310 And ( l_rowid is NULL
311 OR
312 r.rowid <> l_rowid );
313
314 dummy VARCHAR2(100);
315
316 Begin
317 Open c_dup;
318 Fetch c_dup Into dummy;
319
320 If c_dup%FOUND THEN
321 Close c_dup;
322 fnd_message.set_name( 'SQLGL', 'GL_ALLOC_DUP_STEP' );
323 fnd_message.set_token('STEP_LABEL',
324 l_step_label);
325 app_exception.raise_exception;
326 End If;
327
328 Close c_dup;
329
330 Exception
331 When app_exceptions.application_exception THEN
332 Raise;
333 When Others THEN
334 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
335 fnd_message.set_token('PROCEDURE',
336 'gl_auto_alloc_batch_pkg.Check_Unique_Step');
337 Raise;
338
339 End Check_Unique_Step;
340
341 PROCEDURE Check_Unique_Batch( l_rowid IN VARCHAR2
342 ,l_allocation_set_id IN NUMBER
343 ,l_Batch_Id IN NUMBER
344 ,l_Batch_Type_Code IN VARCHAR2
345 ,l_Return_Code IN OUT NOCOPY VARCHAR2 ) Is
346 Cursor c_dup IS
347 Select 'Duplicate'
348 From gl_auto_alloc_batches r
349 Where r.allocation_set_id = l_allocation_set_id
350 And r.Batch_Id = l_Batch_Id
351 And r.Batch_Type_Code = l_Batch_Type_Code
352 And ( l_rowid is NULL
353 OR
354 r.rowid <> l_rowid );
355
356 dummy VARCHAR2(100);
357
358 Begin
359 Open c_dup;
360 Fetch c_dup Into dummy;
361
362 If c_dup%FOUND THEN
363 l_Return_Code := FAILURE;
364 Else
365 l_Return_Code := SUCCESS;
366 End If;
367
368 Close c_dup;
369
370 Exception
371 When app_exceptions.application_exception THEN
372 Raise;
373 When Others THEN
374 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
375 fnd_message.set_token('PROCEDURE',
376 'gl_auto_alloc_batch_pkg.Check_Unique_Batch');
377 Raise;
378
379 End Check_Unique_Batch;
380
381 Procedure get_step_status (
382 p_request_Id IN NUMBER
383 ,p_step_number IN NUMBER
384 ,p_mode IN VARCHAR2
385 ,p_status OUT NOCOPY VARCHAR2
386 ) Is
387
388 l_batch_type_code Varchar2(2);
389
390 Cursor Get_Batch_Type_C IS
391 Select Batch_Type_Code
392 From GL_AUTO_ALLOC_BATCH_HISTORY
393 Where REQUEST_ID = p_request_Id
394 AND STEP_NUMBER = p_step_number;
395
396 Begin
397 If p_mode = 'SD' Then
398 Open Get_Batch_Type_C;
399 Fetch Get_Batch_Type_C
400 into l_batch_type_code;
401
402 If Get_Batch_Type_C%NOTFOUND Then
403 p_status := NULL;
404 Close Get_Batch_Type_C;
405 return;
406 End If;
407
408 Close Get_Batch_Type_C;
409 Else
410 --doesn't matter for parallel or for request detail
414 If l_batch_type_code in ('A','B','E','R') Then
411 l_batch_type_code := 'A';
412 End If;
413
415 get_gl_step_status(
416 p_request_Id
417 ,p_step_number
418 ,p_mode
419 ,p_status);
420 ElsIf l_batch_type_code = 'P' Then
421 gl_pa_autoalloc_pkg.get_pa_step_status (
422 p_request_Id
423 ,p_step_number
424 ,p_mode
425 ,p_status);
426 End If;
427 End get_step_status;
428
429
430 Procedure get_gl_step_status(
431 p_request_id In Number
432 ,p_step_number In Number
433 ,p_mode In Varchar2
434 ,p_status Out NOCOPY Varchar2) Is
435
436 l_meaning Varchar2(80);
437 l_description Varchar2(240);
438 l_status_code Varchar2(30);
439 l_lookup_code Varchar2(30);
440 l_request_id Number := p_request_id;
441
442 l_phase Varchar2(30);
443 l_status Varchar2(30);
444 l_dev_phase Varchar2(30);
445 l_dev_status Varchar2(30);
446 l_message Varchar2(240);
447 call_status Boolean;
448
449 Cursor Get_status_Code_C IS
450 Select Status_Code
451 From GL_AUTO_ALLOC_BATCH_HISTORY
452 Where REQUEST_ID = p_request_Id
453 AND STEP_NUMBER = p_step_number;
454
455 Cursor Get_Status_Meaning_C IS
456 Select
457 Meaning
458 ,Description
459 From gl_lookups
460 Where LOOKUP_TYPE = 'AUTOALLOCATION_STATUS'
461 And LOOKUP_CODE = l_lookup_code;
462
463 Cursor get_request_id_C IS
464 Select request_id
465 From GL_AUTO_ALLOC_BAT_HIST_DET
466 Where PARENT_REQUEST_ID = p_request_Id
467 And STEP_NUMBER = p_step_number
468 order by request_id desc;
469 Begin
470
471 If p_mode = 'SD' Then
472 -- Mode is step-down
473 If p_request_id is Null Or
474 p_step_number is Null Then
475 p_status := NULL;
476 return;
477 End If;
478
479 Open Get_status_Code_C;
480 Fetch Get_status_Code_C into l_status_code;
481 If Get_status_Code_C%NOTFOUND Then
482 p_status := NULL;
483 Close Get_status_Code_C;
484 return;
485 End If;
486 Close Get_status_Code_C;
487
488 If l_status_code in ('VP','GP','PP','RPP') Then
489 -- Find whether pending request is presently running or completed
490 Open get_request_id_C;
491 Fetch get_request_id_C into l_request_id;
492 Close get_request_id_C;
493
494 call_status :=
495 fnd_concurrent.get_request_status(
496 l_request_Id
497 ,'SQLGL'
498 ,NULL
499 ,l_phase
500 ,l_status
501 ,l_dev_phase
502 ,l_dev_status
503 ,l_message
504 );
505
506 If l_dev_phase = 'COMPLETE' AND
507 l_dev_status In ('ERROR','CANCELLED','TERMINATED') Then
508 If l_status_code = 'VP' Then
509 l_status_code := 'VF';
510 ElsIf l_status_code = 'GP' Then
511 l_status_code := 'GF' ;
512 ElsIf l_status_code = 'PP' Then
513 l_status_code := 'PF' ;
514 ElsIf l_status_code = 'RPP' Then
515 l_status_code := 'RPF' ;
516 End If;
517 ElsIf l_dev_phase = 'COMPLETE' AND
518 l_dev_status = 'NORMAL' Then
519 If l_status_code = 'VP' Then
520 l_status_code := 'VPC';
521 ElsIf l_status_code = 'GP' Then
522 l_status_code := 'GPC' ;
523 ElsIf l_status_code = 'PP' Then
524 l_status_code := 'PPC' ;
525 ElsIf l_status_code = 'RPP' Then
526 l_status_code := 'RPPC' ;
527 End If;
528 ElsIf l_dev_phase = 'RUNNING' AND
529 l_dev_status in ('NORMAL') Then
530 If l_status_code = 'VP' Then
531 l_status_code := 'VR';
532 ElsIf l_status_code = 'GP' Then
533 l_status_code := 'GR' ;
534 ElsIf l_status_code = 'PP' Then
535 l_status_code := 'PR' ;
536 ElsIf l_status_code = 'RPP' Then
537 l_status_code := 'RPR' ;
538 End If;
539
540 End If;
541 End If;
542 l_lookup_code := l_status_code;
543
544 Open Get_Status_Meaning_C;
545 Fetch Get_Status_Meaning_C into
546 l_Meaning, l_description;
547
548 If Get_Status_Meaning_C%NOTFOUND Then
549 FND_MESSAGE.Set_Name('SQLGL', 'GL_AUTO_ALLOC_STATUS_ERR');
550 p_status := FND_MESSAGE.Get;
551 Close Get_Status_Meaning_C;
552 return;
553 Else
554 Close Get_Status_Meaning_C;
555 p_status := l_description;
556 End If;
557 Else
558 -- if not step down
559 call_status :=
560 fnd_concurrent.get_request_status(
561 l_request_Id
562 ,'SQLGL'
563 ,NULL
564 ,l_phase
565 ,l_status
566 ,l_dev_phase
567 ,l_dev_status
571 If l_dev_phase = 'COMPLETE' AND
568 ,l_message
569 );
570
572 l_dev_status = 'NORMAL' Then
573 p_status := l_dev_phase;
574 ElsIf l_dev_phase = 'COMPLETE' AND
575 l_dev_status <> 'NORMAL' Then
576 p_status := l_dev_status;
577 Else
578 p_status := l_dev_phase;
579 End If;
580 End If;
581 End get_gl_step_status;
582
583 END gl_auto_alloc_batch_pkg;