[Home] [Help]
PACKAGE BODY: APPS.GMS_FUNDS_POSTING_PKG
Source
1 PACKAGE BODY gms_funds_posting_pkg AS
2 /* $Header: gmsglfcb.pls 120.6 2006/08/09 12:04:38 cmishra noship $ */
3
4 -- =================================
5 -- Declare Global package Variables.
6 -- =================================
7 g_error_program_name CONSTANT VARCHAR2 (30) := 'GMS_FUNDS_POSTING_PKG';
8 g_error_procedure_name VARCHAR2 (30);
9 g_error_stage VARCHAR2 (30);
10 g_non_gms_txn BOOLEAN;
11 TYPE g_type_number is table of NUMBER index by binary_integer ;
12
13
14 -- ==================================
15 -- Declare Local program routines.
16 -- ==================================
17
18 -- R12 Funds Management uptake : Deleted specification of FUNCTION misc_non_gms_txn
19
20 FUNCTION get_gl_return_code ( p_packet_id IN NUMBER,
21 p_gms_partial_flag IN VARCHAR2) RETURN VARCHAR2 ;
22
23 PROCEDURE gl_result_code_update ( p_packet_id IN NUMBER) ;
24
25 -- bug 3478028 11.5.10 grants accounting patch.
26 -- BUG: 3517362 forward port funds check related changes.
27 -- R12 Funds Management uptake : Deleted specification of obsolete procedure adjust_burden_ref14
28
29 PROCEDURE gms_result_code_update ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
30 p_packet_id IN NUMBER,
31 p_mode IN VARCHAR2) ;
32
33 PROCEDURE budget_ver_check ( x_return OUT NOCOPY VARCHAR2,
34 p_packet_id IN NUMBER,
35 p_gms_partial_flag IN VARCHAR2 ) ;
36
37
38 PROCEDURE gms_posting ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
39 p_packet_id IN NUMBER,
40 p_mode IN VARCHAR2,
41 p_gms_partial_flag IN VARCHAR2) ;
42
43 PROCEDURE status_code_update ( p_packet_id IN NUMBER,
44 p_mode IN VARCHAR2,
45 p_partial IN VARCHAR2 DEFAULT 'N') ;
46 -- ===============================================================================
47 -- Main API.
48 -- GMS_GL_RETURN_CODE : Code change is done to integrate bug fixes for GL bug
49 -- 1751696. As part of GL code change GMS_GL_RETURN_CODE may be called three
50 -- times now.
51 -- Phase 1: Update the status code on GL/GMS bc_packets based on
52 -- GL failure/GMS failure. (gms_result_code_update)
53 -- Phase 2: POSTING MODE. This is to update the burden posting to ADL's
54 -- and summary tables. It is called after bc_packet update and after
55 -- GL updates for gms burden posting.
56 -- Phase 3: PANIC MODE. This call occurs only if POSTING failed for unhandled
57 -- exceptions.
58 -- ==============================================================================
59 PROCEDURE gms_gl_return_code (x_er_code IN OUT NOCOPY VARCHAR2,
60 x_er_stage IN OUT NOCOPY VARCHAR2,
61 x_gl_return_code IN OUT NOCOPY VARCHAR2,
62 p_packet_id IN NUMBER,
63 p_mode IN VARCHAR2,
64 p_gms_return_code IN VARCHAR2,
65 p_gms_partial_flag IN VARCHAR2) IS
66
67 t_stage VARCHAR2 (40);
68 v_error_code NUMBER;
69 l_new_partial_flag VARCHAR2 (2) := 'N' ;
70 l_error_message VARCHAR2(2000);
71 l_gl_update VARCHAR2(1);
72 l_bc_packet_id G_TYPE_NUMBER ;
73 l_packet_id G_TYPE_NUMBER ;
74 l_burdenable_cost G_TYPE_NUMBER ;
75 l_count NUMBER; -- R12 Funds Management uptake :
76
77 -- R12 Funds Management uptake : Cursor to check if gms txns exists for processing
78 CURSOR C_gms_pkt_rec_exist IS
79 SELECT count(*)
80 FROM gms_bc_packets
81 WHERE packet_id = p_packet_id;
82
83 BEGIN
84 g_error_procedure_name := 'gms_gl_return_code';
85
86 -- R12 Funds Management uptake : Logic to check if any gms txns exists for processing
87 -- Removed call to obsolete misc_non_gms_txn procedure
88 OPEN C_gms_pkt_rec_exist;
89 FETCH C_gms_pkt_rec_exist INTO l_count;
90 CLOSE C_gms_pkt_rec_exist;
91
92 IF l_count = 0 THEN
93 return ;
94 END IF ;
95
96 -- R12 Funds Management uptake : Code to derive partial/full mode based on p_gms_partial_flag
97 l_new_partial_flag := p_gms_partial_flag ;
98 --
99 -- When others exception was returning success funds checking status.
100 -- t_stage STATUS will allow x_gl_return_code to have failure value set.
101 t_stage := 'STATUS' ;
102
103 -- =========================================
104 -- Determine the gl_return_code value
105 -- =========================================
106 IF l_new_partial_flag = 'N' AND
107 NVL(x_gl_return_code,'S') NOT IN ('F','T') AND
108 get_gl_return_code(p_packet_id, l_new_partial_flag) = 'F' THEN
109
110 x_gl_return_code := 'F';
111
112 END IF;
113
114 budget_ver_check (l_gl_update, p_packet_id, l_new_partial_flag);
115
116 -- R12 Funds Management uptake : Return GL status as Failed only if FULL mode
117 -- If all packets have failed then above call to get_gl_return_code will
118 -- assign x_gl_return_code to 'F'.
119
120 IF (p_gms_return_code IN ('F','T') AND p_gms_partial_flag = 'N') OR
121 NVL (l_gl_update,'N') = 'Y' THEN
122
123 -- Bug : 2557041 - Added for IP check funds Enhancement
124 -- Set the value of gl return code, in case of grants failing
125 -- in full mode comment out NOCOPY call to update_gl_packet
126 --
127 x_gl_return_code := p_gms_return_code;
128 END IF;
129
130 -- =====================================================
131 -- We want to make sure that GL failure has not happened
132 -- after the last call to GMS GL return code changes.
133 -- So updating the gms packet just in case GL panic
134 -- occured.
135 -- We are expecting only GMS BC packet update at this
136 -- Point and X_gl_return_code doesn't change here.
137 -- =====================================================
138 gms_result_code_update(x_gl_return_code, p_packet_id, p_mode);
139
140 IF p_gms_return_code = 'X' THEN
141 g_error_stage := 'POSTING';
142 t_stage := 'POSTING';
143
144 -- POSTING MODE and return
145 -- x_gl_return_code = 'Z' during failure.
146 --
147 -- bug: 3523583
148 -- allocate the burden from the summarized lines when reference14 is populated for the tax lines.
149 -- Burden is allocated to the tax line.
150 -- bug 3478028 11.5.10 grants accounting patch.
151 -- BUG: 3517362 forward port funds check related changes.
152 -- R12 Funds Management uptake : Removed call to obsolete procedure adjust_burden_ref14
153
154 gms_posting(x_gl_return_code,
155 p_packet_id,
156 p_mode,
157 l_new_partial_flag);
158
159 -- bug: 3523583
160 -- De allocate the burden from the summarized lines when reference14 is populated for the tax lines.
161 -- Burden is de allocated to the tax line.
162 -- This is to keep the burdenable raw cost in synch with PO match and results to match in
163 -- funds check results form.
164 -- bug 3478028 11.5.10 grants accounting patch.
165 -- BUG: 3517362 forward port funds check related changes.
166 IF l_bc_packet_id.count > 0 THEN
167 forall l_index in l_bc_packet_id.FIRST..l_bc_packet_id.LAST
168 update gms_bc_packets
169 set burdenable_raw_cost = l_burdenable_cost(l_index)
170 where bc_packet_id = l_bc_packet_id(l_index)
171 and packet_id = p_packet_id ;
172 END IF ;
173 END IF;
174
175 IF p_mode IN ('U', 'C', 'R') THEN
176 -- ===============================================================
177 -- status_code_update should be called in p_gms_return_code='X'
178 -- filter condition was added. without filter condition burdenable
179 -- raw cost was not getting updated and burden posting expects
180 -- packet in the pending status.
181 -- ===============================================================
182 IF ( p_mode = 'C' OR p_gms_return_code = 'X' ) THEN
183 -- Bug 5039545 : status_code_update is called with p_partial parameter as p_gms_partial_flag.
184 status_code_update (p_packet_id, p_mode,p_gms_partial_flag);
185 END IF ;
186 END IF;
187
188 gl_result_code_update(p_packet_id);
189 x_gl_return_code := get_gl_return_code(p_packet_id, l_new_partial_flag) ;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 x_er_code := 'U';
194 x_er_stage := SQLCODE||' '||SQLERRM;
195
196 IF t_stage = 'STATUS' AND
197 p_gms_return_code <> 'Z' THEN
198 x_gl_return_code := 'F';
199 ELSIF t_stage = 'POSTING' THEN
200 x_gl_return_code := 'Z';
201 END IF; -- Bug 2337897 : Added End If
202
203 UPDATE gl_bc_packets gl
204 SET gl.result_code = DECODE (
205 NVL (SUBSTR (result_code, 1, 1), 'P'),
206 'P', 'F71',
207 result_code)
208 WHERE gl.packet_id = p_packet_id;
209
210 l_error_message := SUBSTR((g_error_program_name || '.'
211 ||g_error_procedure_name || '.' || g_error_stage
212 ||' SQLCODE :'||SQLCODE||' SQLERRM :'||SQLERRM),1,2000);
213
214 UPDATE gms_bc_packets gms
215 SET gms.status_code = 'T',
216 gms.fc_error_message = l_error_message,
217 gms.result_code = DECODE (
218 NVL (SUBSTR (result_code, 1, 1), 'P'),
219 'P', 'F68',
220 result_code)
221 WHERE gms.packet_id = p_packet_id;
222
223 END gms_gl_return_code;
224
225 -- ================================================================
226 -- STATUS_CODE_UPDATE
227 -- Following procedure will update funds check ststus on BC packets
228 -- and gms award distributions table records.
229 -- ================================================================
230 PROCEDURE status_code_update (p_packet_id NUMBER,
231 p_mode VARCHAR2,
232 p_partial VARCHAR2 DEFAULT 'N') IS
233 x_err_code NUMBER;
234 x_dummy NUMBER; -- Bug 2181546, Added
235 x_err_buff VARCHAR2 (2000);
236
237 CURSOR c_failed_packet IS -- Bug 2181546, Added
238 SELECT 1
239 FROM gms_bc_packets
240 WHERE packet_id = p_packet_id
241 AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
242
243 CURSOR update_status IS
244 SELECT document_header_id,
245 document_type,
246 result_code,
247 status_code,
248 entered_dr,
249 entered_cr,
250 bud_task_id,
251 project_id,
252 resource_list_member_id,
253 document_distribution_id,
254 task_id,
255 expenditure_item_date,
256 expenditure_type , -- Bug 3003584
257 award_id,
258 expenditure_organization_id,
259 packet_id,
260 bc_packet_id -- Added for bug : 2927485
261 FROM gms_bc_packets
262 WHERE packet_id = p_packet_id
263 AND parent_bc_packet_id IS NULL
264 AND nvl(burden_adjustment_flag,'N') = 'N'
265 AND status_code in ('A','B') ; --Added to fix bug 2138376 from 'B'
266
267 BEGIN
268
269 g_error_procedure_name := 'status_code_update';
270 g_error_stage := 'SCU : START';
271
272 --R12 Funds Management Uptake : Whole packet should be updated only in full mode
273 IF NVL(p_mode,'R') in ('U','S','B','C', 'R' ) AND p_partial = 'N' THEN
274
275 g_error_stage := 'SCU : PARTIAL NO RES';
276 --Bug 2181546, Added the cursor and failing packet if atleast one failed record exists in packet
277
278 OPEN c_failed_packet;
279 FETCH c_failed_packet INTO x_dummy;
280
281 IF c_failed_packet%FOUND THEN
282 UPDATE gms_bc_packets
283 SET status_code = decode(p_mode,'S','E','C','F','R'),
284 result_code =
285 DECODE (SUBSTR (NVL (result_code, 'F65'), 1, 1),
286 'P','F65',
287 NVL(result_code,'F65'))
288 WHERE packet_id = p_packet_id;
289
290 --IF SQL%NOTFOUND THEN
291 -- Bug 2181546, Replaced with ELSE clause
292 ELSE
293
294 UPDATE gms_bc_packets
295 SET status_code = decode(p_mode,'S','S','B','B','C','C','A')
296 WHERE packet_id = p_packet_id;
297 END IF;
298 CLOSE c_failed_packet;
299
300
301 /* Bug 5039545 : When p_partial is 'Y' , the status_code of all the records in gms_bc_packets for the current packet id
302 is updated correctly. */
303 /* Bug 5217281 : Modified the code such that when the GL funds check fails but the GMS fundscheck passes then
304 the status_code is updated correctly on gms_bc_packets. */
305 ELSIF NVL(p_mode,'R') in ('U','S','B','C', 'R') AND p_partial = 'Y' THEN
306
307 UPDATE gms_bc_packets
308 SET status_code =
309 DECODE(status_code,'P',decode(p_mode,'S',DECODE (SUBSTR (result_code, 1, 1), 'P', 'S', 'E')
310 ,'B',DECODE (SUBSTR (result_code, 1, 1), 'P', 'B', 'R')
311 ,'C',DECODE (SUBSTR (result_code, 1, 1), 'P', 'C', 'F')
312 ,DECODE (SUBSTR (result_code, 1, 1), 'P', 'A', 'R')) -- This will cover p_mode 'U' and 'R'
313 ,status_code)
314 WHERE packet_id = p_packet_id;
315
316
317 ELSIF ( NVL(p_mode,'R') in ('E') ) THEN
318
319 UPDATE gms_bc_packets
320 SET status_code = DECODE (SUBSTR (nvl(result_code,'F65'), 1, 1), 'P', 'A', 'R')
321 WHERE packet_id = p_packet_id;
322
323 g_error_stage := 'SCU : PARTIAL YES RES';
324 END IF ;
325
326 IF p_mode not IN ('R','U','B','E') THEN
327 return ;
328 END IF ;
329
330 FOR bc_records IN update_status LOOP
331
332 IF bc_records.document_type = 'REQ' THEN
333 g_error_stage := 'UPDATE_ADL:REQ';
334
335 UPDATE gms_award_distributions
336 SET resource_list_member_id = bc_records.resource_list_member_id,
337 bud_task_id = bc_records.bud_task_id,
338 fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
339 WHERE distribution_id = bc_records.document_distribution_id
340 AND adl_status = 'A'
341 AND document_type = 'REQ'
342 AND project_id = bc_records.project_id
343 AND task_id = bc_records.task_id
344 AND award_id = bc_records.award_id ;
345
346 ELSIF bc_records.document_type = 'PO' THEN
347 g_error_stage := 'UPDATE_ADL:PO';
348
349 UPDATE gms_award_distributions
350 SET resource_list_member_id = bc_records.resource_list_member_id,
351 bud_task_id = bc_records.bud_task_id,
352 fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
353 WHERE po_distribution_id = bc_records.document_distribution_id
354 AND adl_status = 'A'
355 AND document_type = 'PO'
356 AND project_id = bc_records.project_id
357 AND task_id = bc_records.task_id
358 AND award_id = bc_records.award_id;
359
360 ELSIF bc_records.document_type = 'AP' THEN
361 g_error_stage := 'UPDATE_ADL:AP';
362
363 UPDATE gms_award_distributions
364 SET resource_list_member_id = bc_records.resource_list_member_id,
365 bud_task_id = bc_records.bud_task_id,
366 fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
367 WHERE invoice_id = bc_records.document_header_id
368 /* Bug 5453662 : bc_records.document_distribution_id stores the invoice_distribution_id for an AP invoice.
369 So for an AP invoice , bc_records.document_distribution_id should be compared with invoice_distribution_id. */
370 AND invoice_distribution_id = bc_records.document_distribution_id
371 AND adl_status = 'A'
372 AND document_type = 'AP'
373 AND project_id = bc_records.project_id
374 AND task_id = bc_records.task_id
375 AND award_id = bc_records.award_id;
376 END IF;
377 END LOOP;
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 IF update_status%ISOPEN THEN
382 CLOSE update_status;
383 END IF;
384 RAISE; -- Bug 2181546, Added
385 END status_code_update;
386
387 -- ==========================================================
388 -- MISC_NON_GMS_TXN
389 -- Following function returns TRUE if there are non sponsored
390 -- project related transactions.
391 -- ==========================================================
392
393 FUNCTION misc_non_gms_txn (p_packet_id IN NUMBER ) RETURN BOOLEAN IS
394
395 l_temp NUMBER := 0;
396 l_record NUMBER;
397 l_return BOOLEAN;
398 l_document_type VARCHAR2 (30); --Bug 2069079
399 l_source_name VARCHAR2 (30);
400 l_category_name VARCHAR2 (30);
401
402 CURSOR c_non_gms_ap_trans IS
403 SELECT gl.packet_id
404 FROM ap_invoice_distributions_all ap,
405 gms_award_distributions adl,
406 pa_projects_all pp,
407 gms_project_types gpt,
408 gl_bc_packets gl
409 WHERE gl.packet_id = p_packet_id
410 AND gl.je_source_name = 'Payables'
411 AND gl.template_id IS NULL
412 AND gl.je_category_name = 'Purchase Invoices'
413 AND gl.reference2 = ap.invoice_id
414 AND gl.reference3 = ap.distribution_line_number
415 AND ap.project_id IS NOT NULL
416 AND (NVL (ap.pa_addition_flag, 'X') = 'T')
417 AND ap.project_id = pp.project_id
418 AND pp.project_type = gpt.project_type
419 AND gpt.sponsored_flag = 'Y'
420 AND ap.award_id = adl.award_set_id
421 AND ap.invoice_id = NVL (adl.invoice_id, ap.invoice_id)
422 AND ap.distribution_line_number =
423 NVL (adl.distribution_line_number, ap.distribution_line_number)
424 AND ap.invoice_distribution_id =
425 NVL (adl.invoice_distribution_id, ap.invoice_distribution_id)
426 AND ap.project_id = NVL (adl.project_id, ap.project_id)
427 AND ap.task_id = NVL (adl.task_id, ap.task_id)
428 AND NVL (adl.adl_status, 'I') = 'A' -- Bug 2092791
429 AND NVL (adl.document_type, 'AP') IN ('AP', 'DST')
430 AND NVL (adl.fc_status, 'X') <> 'A';
431
432 CURSOR c_non_gms_ap IS
433 SELECT gl.packet_id
434 FROM ap_invoice_distributions_all ap,
435 pa_projects_all pp,
436 gms_project_types gpt,
437 gl_bc_packets gl
438 WHERE gl.packet_id = p_packet_id
439 AND gl.je_source_name = 'Payables'
440 AND gl.template_id IS NULL
441 AND gl.je_category_name = 'Purchase Invoices'
442 AND gl.reference2 = ap.invoice_id
443 AND gl.reference3 = ap.distribution_line_number
444 AND ap.project_id IS NOT NULL
445 AND (NVL (ap.pa_addition_flag, 'X') <> 'T')
446 AND ap.project_id = pp.project_id
447 AND pp.project_type = gpt.project_type
448 AND gpt.sponsored_flag = 'Y';
449
450 CURSOR c_non_gms_req IS
451 SELECT gl.packet_id
452 FROM pa_projects_all pp,
453 gms_project_types gpt,
454 po_req_distributions_all pord,
455 gl_bc_packets gl
456 WHERE gl.packet_id = p_packet_id
457 AND gl.reference1 = 'REQ'
458 AND gl.template_id IS NULL
459 AND gl.reference3 = pord.distribution_id
460 AND pord.project_id IS NOT NULL
461 AND pord.project_id = pp.project_id
462 AND pp.project_type = gpt.project_type
463 AND gpt.sponsored_flag = 'Y';
464
465 CURSOR c_non_gms_ip is
466 SELECT gl.packet_id
467 FROM gl_bc_packets gl,
468 pa_projects pp,
469 gms_project_types gpt
470 WHERE gl.packet_id = p_packet_id
471 AND pp.project_id = TO_NUMBER (gl.reference7)
472 AND pp.project_type = gpt.project_type
473 AND gpt.sponsored_flag = 'Y'
474 AND NVL (gl.reference6, 'XXXXX') = 'GMSIP' ;
475
476 CURSOR c_non_gms_po IS
477 SELECT gl.packet_id
478 FROM po_distributions_all pod, pa_projects_all pp, gms_project_types gpt, gl_bc_packets gl
479 WHERE gl.packet_id = p_packet_id
480 AND gl.reference1 = 'PO'
481 AND gl.template_id IS NULL
482 AND gl.reference3 = pod.po_distribution_id
483 AND pod.project_id IS NOT NULL
484 AND pod.project_id = pp.project_id
485 AND pod.distribution_type <> 'PREPAYMENT' -- subcontractor/complex work uptake
486 AND pp.project_type = gpt.project_type
487 AND gpt.sponsored_flag = 'Y';
488
489
490 CURSOR c_document_type IS
491 SELECT DISTINCT NVL (reference1, 'X'),
492 je_source_name,
493 je_category_name
494 FROM gl_bc_packets
495 WHERE packet_id = p_packet_id
496 AND template_id IS NULL
497 AND ( ( reference1 in ('PO', 'REQ') ) OR
498 ( je_source_name = 'Payables' AND je_category_name = 'Purchase Invoices' ) OR
499 ( reference6 = 'GMSIP' )
500 ) ;
501
502 BEGIN
503
504 g_error_procedure_name := 'misc_non_gms_txn' ;
505
506 l_return := TRUE;
507
508 g_error_stage := 'MISC_NON_GMS : START';
509
510 LOOP
511 OPEN c_document_type;
512 FETCH c_document_type INTO l_document_type, l_source_name, l_category_name;
513 EXIT WHEN c_document_type%NOTFOUND;
514
515 IF l_source_name = 'Payables' AND
516 l_category_name = 'Purchase Invoices' THEN
517
518 g_error_stage := 'MISC_NON_GMS : AP';
519
520 OPEN c_non_gms_ap_trans;
521 FETCH c_non_gms_ap_trans INTO l_record;
522
523 IF c_non_gms_ap_trans%FOUND THEN
524 l_return := TRUE;
525 END IF;
526
527 CLOSE c_non_gms_ap_trans;
528
529 OPEN c_non_gms_ap;
530 FETCH c_non_gms_ap INTO l_record;
531
532 IF c_non_gms_ap%FOUND THEN
533 l_return := FALSE;
534 END IF;
535
536 CLOSE c_non_gms_ap;
537 ELSIF l_document_type = 'REQ' THEN
538 g_error_stage := 'MISC_NON_GMS : REQ';
539
540 OPEN c_non_gms_req;
541 FETCH c_non_gms_req INTO l_record;
542
543 IF c_non_gms_req%FOUND THEN
544 l_return := FALSE;
545 END IF;
546 CLOSE c_non_gms_req;
547
548 OPEN c_non_gms_ip;
549 FETCH c_non_gms_ip INTO l_record;
550
551 IF c_non_gms_ip%FOUND THEN
552 l_return := FALSE;
553 END IF;
554 CLOSE c_non_gms_ip;
555 ELSIF l_document_type = 'PO' THEN
556 g_error_stage := 'MISC_NON_GMS : PO';
557 OPEN c_non_gms_po;
558 FETCH c_non_gms_po INTO l_record;
559
560 IF c_non_gms_po%FOUND THEN
561 l_return := FALSE;
562 END IF;
563
564 CLOSE c_non_gms_po;
565 END IF;
566
567 CLOSE c_document_type;
568 EXIT;
569 END LOOP;
570
571 g_non_gms_txn := l_return ;
572
573 RETURN l_return;
574
575 END misc_non_gms_txn;
576
577 -- ===============================================================
578 -- BUDGET_VER_CHECK
579 -- Check if award budget baseline process is not in progress.
580 -- fail funds check if award budget baseline process is in
581 -- progress.
582 -- ===============================================================
583 PROCEDURE budget_ver_check (x_return OUT NOCOPY VARCHAR2,
584 p_packet_id IN NUMBER,
585 p_gms_partial_flag IN VARCHAR2 ) IS
586
587 l_budget_version_id NUMBER (15);
588
589 CURSOR cur_valid_bvid IS
590 SELECT DISTINCT budget_version_id
591 FROM gms_bc_packets
592 WHERE packet_id = p_packet_id
593 AND SUBSTR (result_code, 1, 1) = 'P';
594
595 CURSOR c_budget_rec IS
596 SELECT budget_version_id
597 FROM gms_budget_versions
598 WHERE budget_version_id = l_budget_version_id
599 AND current_flag = 'Y'
600 AND budget_status_code = 'B';
601
602 BEGIN
603 g_error_procedure_name := 'budget_ver_check';
604 ----------------------------------------------------------------
605 -- CHECK IF BASELINED BUDGET EXITS FOR THE BUDGET VERSION ID.
606 ----------------------------------------------------------------
607 x_return := 'N';
608
609 FOR records IN cur_valid_bvid LOOP
610
611 l_budget_version_id := records.budget_version_id;
612
613 OPEN c_budget_rec;
614 FETCH c_budget_rec INTO l_budget_version_id;
615
616 IF c_budget_rec%NOTFOUND THEN
617 -- R12 Funds management uptake : Fail the full packet only if FULL mode
618 --x_return := 'Y';
619
620 UPDATE gms_bc_packets
621 SET budget_version_id = NULL,
622 result_code = 'F10'
623 WHERE packet_id = p_packet_id
624 AND budget_version_id = records.budget_version_id
625 AND SUBSTR (result_code, 1, 1) = 'P';
626
627 -- R12 Funds management uptake : Fail the full packet only if FULL mode
628 IF p_gms_partial_flag = 'N' THEN
629
630 x_return := 'Y';
631
632 UPDATE gms_bc_packets
633 SET result_code = 'F11'
634 WHERE packet_id = p_packet_id
635 AND substr(result_code,1,1) = 'P'; -- Bug 2181546, Added
636 END IF;
637 END IF;
638
639 -- 3688308
640 -- 3684986 IPST:MFGST11I: CHECK FUNDS FAILS FOR MULTIPLE AWARD DISTRIBUTIONS/LINES
641 -- GMS_FUNDS_POSTING_PKG.budget_ver_check.MISC_NON_GMS : PO SQLCODE :-6511
642 -- SQLERRM :ORA-06511: PL/SQL: cursor already open c_budget_rec was not closed
643 -- in the loop which has resulted into PL/SQL: cursor already open exception.
644 -- ===============================================================================
645
646 CLOSE c_budget_rec;
647 IF x_return = 'Y' THEN
648 EXIT ;
649 END IF ;
650
651 END LOOP;
652
653 END budget_ver_check;
654
655 -- ==================================================================
656 -- Bug : 2557041 - Added for IP check funds Enhancement
657 -- This procedure is called from procedure gms_gl_return_code .
658 -- Procedure will update the result codes on gl_bc_packets record,
659 -- if the records have:
660 -- A. Failed grants funds check process.
661 -- B. Passed grants funds check process in advisory mode.
662 -- ==================================================================
663 PROCEDURE gl_result_code_update (p_packet_id IN NUMBER) IS
664
665 -- =================================================================
666 -- This cursor return records in following scenario's
667 -- A. In gms_bc_packets there exists Funds check failed records
668 -- for the current packet.
669 -- B. In gms_bc_packets there exists records which passed Funds
670 -- check in advisory mode for the current packet.
671 -- =================================================================
672 CURSOR c_gl_update_required IS
673 SELECT 1
674 FROM DUAL
675 WHERE EXISTS ( SELECT 1
676 FROM gms_bc_packets
677 WHERE packet_id = p_packet_id
678 AND ( result_code IN ('P61', 'P65', 'P69', 'P73', 'P80')
679 OR NVL (SUBSTR (result_code, 1, 1), 'P') = 'F'
680 ));
681 l_dummy NUMBER;
682 BEGIN
683
684 g_error_procedure_name := 'gl_result_code_update';
685 g_error_stage := 'GL_RESULT_CODE UPD :START';
686 -- If cursor c_gl_update_required returns any records then only execute
687 -- the update statement. This will impove performance.
688 OPEN c_gl_update_required;
689 FETCH c_gl_update_required INTO l_dummy;
690 IF c_gl_update_required%FOUND THEN
691
692 g_error_stage := 'GL_RESULT_CODE UPD :REC_FOUND';
693 UPDATE gl_bc_packets glc
694 SET glc.result_code = (SELECT DECODE (
695 SUBSTR (bp.result_code, 1, 1),
696 'P', DECODE (
697 bp.result_code,
698 'P61', 'P39', -- advisory result code
699 'P65', 'P39', -- advisory result code
700 'P69', 'P39', -- advisory result code
701 'P73', 'P39', -- advisory result code
702 'P80', 'P39', -- advisory result code
703 glc.result_code
704 ),
705 'F', DECODE (
706 bp.result_code,
707 'F21', 'F68', --Invalid award number
708 'F60', 'F69', --Top Task Failure
709 'F90', 'F71', --Award Failure
710 'F91', 'F72', --Task Failure
711 'F92', 'F73', --Resource Failure
712 'F93', 'F74', --Resource Group Failure
713 'F65', 'F70', --Full Mode
714 'F68', 'F67', --Funds Check processing error
715 'F89', 'F67', --Funds Check processing error
716 'F09', 'F67', --Funds Check processing error
717 'F10', 'F67', --Funds Check processing error
718 'F11', 'F67', --Funds Check processing error
719 'F12', 'F67', --Funds Check processing error
720 'F13', 'F67', --Funds Check processing error
721 'F14', 'F67', --Funds Check processing error
722 'F15', 'F67', --Funds Check processing error
723 'F16', 'F67', --Funds Check processing error
724 'F17', 'F67', --Funds Check processing error
725 'F18', 'F67', --Funds Check processing error
726 'F19', 'F67', --Funds Check processing error
727 'F40', 'F67', --Funds Check processing error
728 'F41', 'F67', --Funds Check processing error
729 'F42', 'F67', --Funds Check processing error
730 'F43', 'F67', --Funds Check processing error
731 'F44', 'F67', --Funds Check processing error
732 'F45', 'F67', --Funds Check processing error
733 'F46', 'F67', --Funds Check processing error
734 'F47', 'F67', --Funds Check processing error
735 'F48', 'F67', --Funds Check processing error
736 'F49', 'F67', --Funds Check processing error
737 'F50', 'F67', --Funds Check processing error
738 'F51', 'F67', --Funds Check processing error
739 'F52', 'F67', --Funds Check processing error
740 'F53', 'F67', --Funds Check processing error
741 'F54', 'F67', --Funds Check processing error
742 'F62', 'F67', --Funds Check processing error
743 'F64', 'F67', --Funds Check processing error
744 'F73', 'F67', --Funds Check processing error
745 'F76', 'F67', --Funds Check processing error
746 'F78', 'F67', --Funds Check processing error
747 'F79', 'F67', --Funds Check processing error
748 'F82', 'F67', --Funds Check processing error
749 'F94', 'F67', --Funds Check processing error
750 'F95', 'F67', --Funds Check processing error
751 -- Update gl_bc_packets with Failure status if gl.result_code
752 -- is Pxx and gms.result_code is Fxx but the result_code is
753 -- not there in the above List
754 DECODE(NVL(SUBSTR(glc.result_code,1,1),'P'),'P','F67',glc.result_code)
755 )
756 )
757 FROM gms_bc_packets bp
758 WHERE bp.gl_bc_packets_rowid = ROWIDTOCHAR(glc.ROWID)
759 AND bp.result_code NOT IN ('F63', 'F75')
760 AND bp.packet_id = p_packet_id
761 AND ROWNUM = 1)
762 WHERE glc.packet_id = p_packet_id
763 AND glc.template_id IS NULL
764 AND substr(nvl(glc.result_code,'P'),1,1) = 'P'
765 -- Bug 2896476 : We should only override if GL Funds check passed
766 -- Bug 3277370 : Added following exists statement to filter out non-GMS transactions , we shouldn't
767 -- update result_code on Non-GMS Transactions.
768 AND EXISTS (SELECT 1
769 FROM gms_bc_packets gms1
770 WHERE gms1.packet_id = glc.packet_id
771 AND gms1.gl_bc_packets_rowid = ROWIDTOCHAR(glc.ROWID)
772 );
773
774 END IF;
775
776 CLOSE c_gl_update_required;
777
778 g_error_stage := 'GL_RESULT_CODE UPD :END';
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 IF c_gl_update_required%ISOPEN THEN
783 CLOSE c_gl_update_required;
784 END IF;
785 RAISE;
786 END gl_result_code_update;
787
788 -- =============================================================================
789 -- GMS_RESULT_CODE_UPDATE
790 -- Purpose: This is called from gms_gl_return_code to have gms_bc_packet
791 -- in synch with gl_bc_Packets and updates the result_code in
792 -- gms_bc_packets with 'F30', 'F31', 'F32' values.
793 -- =============================================================================
794 PROCEDURE gms_result_code_update ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
795 p_packet_id IN NUMBER,
796 p_mode IN VARCHAR2) IS
797
798
799 l_result_code varchar2(3) ;
800 BEGIN
801 g_error_procedure_name := 'gms_result_code_update';
802
803 IF x_gl_return_code IN ('F', 'T') THEN
804 g_error_procedure_name := 'update_gms_packet';
805
806 l_result_code := 'F30' ;
807
808 IF p_mode = 'C' and x_gl_return_code ='T' THEN
809 l_result_code := 'F31' ;
810 END IF ;
811
812 IF p_mode IN ('R', 'U') THEN
813 l_result_code := 'F32' ;
814 END IF;
815
816 UPDATE gms_bc_packets
817 SET result_code = l_result_code
818 WHERE packet_id = p_packet_id
819 AND SUBSTR (result_code, 1, 1) = 'P';
820 END IF;
821 EXCEPTION
822 WHEN OTHERS THEN
823 x_gl_return_code := 'T';
824 RAISE;
825 END gms_result_code_update;
826
827 -- =======================================================================
828 -- GMS_POSTING: This procedure will be called during the 2nd phase of
829 -- gms_gl_return_code i.e., after the GMS/GL packet updates.
830 -- This will post the burdenable raw cost to ADL's and update
831 -- the summary table.
832 -- =======================================================================
833 PROCEDURE gms_posting ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
834 p_packet_id IN NUMBER,
835 p_mode IN VARCHAR2,
836 p_gms_partial_flag IN VARCHAR2) IS
837
838 l_gms_return_code NUMBER;
839 l_gl_update VARCHAR2 (1);
840 l_posting_return BOOLEAN;
841 l_dummy NUMBER;
842
843 l_cursor_name INTEGER;
844 l_string_execute INTEGER;
845 l_sql_string VARCHAR2(1000);
846
847 BEGIN
848 g_error_procedure_name := 'gms_posting';
849 -- =======================================================================
850 -- Bug 2337897 : commented out NOCOPY following code as this procedure is
851 -- getting called in gms_result_code_update which is called before
852 -- gms_posting in gms_gl_retunrn_code procedure, there is no
853 -- need to call this procedure again for budget_ver_check
854 -- =======================================================================
855
856 savepoint SAVE_GMSGL_POSTING;
857
858 -- Update burdenable raw cost in source REQ, PO, AP, EXP if fundscheck is
859 -- successful
860
861 IF p_mode IN ('R', 'U') THEN
862 -- =================================================================
863 -- The following code was commented and Dynamic call was added ,This
864 -- is to provide 11.5.3 base level compatibility.
865 --
866 --l_posting_return := gms_cost_plus_extn.update_source_burden_raw_cost
867 -- ( p_packet_id,
868 -- p_mode,
869 -- p_gms_partial_flag
870 -- );
871 --
872 -- =================================================================
873 l_sql_string :=
874 ' DECLARE
875 l_post_ret BOOLEAN ;
876 begin
877 l_post_ret := gms_cost_plus_extn.update_source_burden_raw_cost
878 ( :l_packet_id,
879 :l_mode,
880 :l_gms_partial_flag
881 );
882 end ;' ;
883 l_cursor_name := dbms_sql.open_cursor;
884 dbms_sql.parse(l_cursor_name,l_sql_string,dbms_sql.native);
885 DBMS_SQL.BIND_VARIABLE(l_cursor_name,':l_packet_id', p_packet_id, 20);
886 DBMS_SQL.BIND_VARIABLE(l_cursor_name,':l_mode', p_mode, 2);
887 DBMS_SQL.BIND_VARIABLE(l_cursor_name,':l_gms_partial_flag', p_gms_partial_flag, 2);
888 l_string_execute := dbms_sql.execute(l_cursor_name);
889 dbms_sql.close_cursor(l_cursor_name);
890 END IF;
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 -- Bug 2181546, Added
895 rollback to SAVE_GMSGL_POSTING;
896 x_gl_return_code := 'Z';
897 RAISE;
898 END gms_posting;
899
900 -- ====================================================================
901 -- GET_GL_RETURN_CODE
902 -- Following function determines the gl return code based on the result
903 -- code value assigned to gl bc packets record.
904 -- ====================================================================
905 FUNCTION get_gl_return_code ( p_packet_id IN NUMBER,
906 p_gms_partial_flag IN VARCHAR2)
907 RETURN VARCHAR2 IS
908
909 t_return_code VARCHAR2 (1);
910 BEGIN
911 g_error_procedure_name := 'get_return_code';
912 SELECT DECODE (
913 COUNT (*),
914 COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'P', 1)),
915 DECODE (SIGN(COUNT (DECODE (bp.result_code,
916 'P20', 1,
917 'P22', 1,
918 'P25', 1,
919 'P27', 1,
920 'P39', 1))), -- Bug 2469309 : Added P39
921 0, 'S',
922 1, 'A'),
923 COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'F', 1)), 'F',
924 DECODE (p_gms_partial_flag, 'Y', 'P', 'F'))
925 INTO t_return_code
926 FROM gl_bc_packets bp
927 WHERE bp.packet_id = p_packet_id
928 AND bp.template_id IS NULL; /* detail transactions only */
929
930 RETURN t_return_code;
931
932 END get_gl_return_code;
933
934 -- R12 Funds Management uptake : Logic of procedure adjust_burden_ref14 is obsolete as
935 -- reference3 and reference14 columns are not used anymore with new architecture.
936
937 END GMS_FUNDS_POSTING_PKG ;