[Home] [Help]
PACKAGE BODY: APPS.GMS_COST_PLUS_EXTN
Source
1 package body GMS_COST_PLUS_EXTN as
2 /* $Header: gmscpexb.pls 120.20 2007/03/17 09:57:33 asubrama ship $ */
3
4
5 -- Global Variable : Used in create_burden_adjustments and function calc_exp_burden
6 g_set_of_books_id pa_implementations_all.set_of_books_id%type;
7 g_request_id gms_bc_packets.request_id%type;
8
9 -- Used in update_bc_pkt_burden_raw_cost
10 g_actual_flag varchar2(1);
11
12 -- Set in update_bc_pkt_burden_raw_cost
13 g_error_procedure_name varchar2(30);
14 g_error_program_name Varchar2 (30);
15 g_debug Varchar2(1);
16
17 -- Stores sqlerrm ..
18 g_dummy varchar2(2000);
19 --R12 Fundscheck Management uptake: Defining new global variables
20 g_update_status varchar2(13) ;
21 g_update_bvid varchar2(13) ;
22 g_update_brc varchar2(13) ;
23
24
25
26 -- variable used in update_bc_pkt_burden_raw_cost and UPDATE_BC_PACKET
27 l_calc_sequence gms_bc_packets.burden_calculation_seq%type;
28
29 -- This cursor and variable will be used to lock the summary table ...
30 -- Used in update_bc_pkt_burden_raw_cost and Maximize_burden
31 Cursor c_lock_burden_summary(p_award_id in number,p_exp_type in varchar2) is
32 select 1
33 from gms_award_exp_type_act_cost
34 where award_id = p_award_id
35 and expenditure_type = p_exp_type
36 for update;
37
38 l_lock_burden_summary number(1);
39
40 -- To retrive the burden schedule id of an award project from the award id
41 -- and expenditure item date. The Schedule type is always 'C'
42
43
44 RESOURCE_BUSY EXCEPTION;
45 PRAGMA EXCEPTION_INIT( RESOURCE_BUSY, -0054 );
46 X_ERR_STAGE VARCHAR2(10) ;
47
48 -- Bug 3465191
49 -- Performance Fix.
50 --
51 TYPE commitRecTyp IS RECORD
52 (
53 dist_id NUMBER,
54 header_id NUMBER,
55 amount NUMBER,
56 burden NUMBER,
57 award_set_id NUMBER,
58 adl_line_num NUMBER,
59 diff_amount NUMBER,
60 award_id NUMBER,
61 expenditure_type varchar2(30),
62 project_id NUMBER,
63 task_id NUMBER,
64 expenditure_item_date DATE,
65 expenditure_organization_id NUMBER,
66 resource_list_member_id NUMBER,
67 bud_task_id NUMBER,
68 ind_compiled_set_id NUMBER
69 );
70
71 -- Bug 3465191
72 -- Performance Fix.
73 --
74 TYPE c_adj_rec is REF CURSOR RETURN commitRecTyp;
75
76 CURSOR c_award_exp_total( p_award_id NUMBER, p_exp_type varchar2) is
77 SELECT nvl(act.req_raw_cost,0) req_raw_cost,
78 nvl(act.po_raw_cost,0) po_raw_cost,
79 nvl(act.enc_raw_cost,0) enc_raw_cost,
80 nvl(act.AP_raw_cost,0) AP_raw_cost,
81 nvl(act.exp_raw_cost,0) exp_raw_cost,
82 nvl(act.req_burdenable_cost,0) req_burdenable_cost,
83 nvl(act.po_burdenable_cost,0) po_burdenable_cost,
84 nvl(act.enc_burdenable_cost,0) enc_burdenable_cost,
85 nvl(act.ap_burdenable_cost,0) ap_burdenable_cost,
86 nvl(act.exp_burdenable_cost,0) exp_burdenable_cost
87 FROM gms_award_exp_type_act_cost act
88 WHERE act.award_id = P_award_id
89 AND act.expenditure_type = P_EXP_TYPE
90 FOR UPDATE OF REQ_RAW_COST NOWAIT;
91
92 cursor bc_packets( p_packet_id NUMBER, p_award_id NUMBER, p_expenditure_type varchar2 ) is
93 select packet_id,
94 bc_packet_id,
95 document_header_id,
96 document_distribution_id,
97 award_id,
98 expenditure_type,
99 document_type,
100 nvl(entered_cr,0) entered_cr,
101 nvl(entered_dr,0) entered_dr,
102 award_set_id,
103 transaction_source,
104 request_id
105 from gms_bc_packets
106 where packet_id = p_packet_id
107 and expenditure_type = p_expenditure_type
108 and award_id = p_award_id
109 and nvl(entered_cr,0) + nvl(entered_dr,0) <> 0
110 and Status_code in ('P','I') -- fix for bug : 2927485, To reject transactions that had already failed a setup step
111 and burdenable_raw_cost is null -- fix for bug 3810247
112 and document_type <> 'ENC' --Bug 5726575
113 order by decode(document_type,'REQ', 1, 'PO',2, 'ENC', 3, 'AP', 4, 'EXP', 5, 6 ) asc, ( nvl(entered_dr,0) - nvl( entered_cr,0) ) DESC ;
114 -- Above order by is to reduce over burdening ..
115
116 --Bug 5726575
117 cursor bc_packets_enc( p_packet_id NUMBER, p_award_id NUMBER, p_expenditure_type varchar2 ) is
118 select packet_id,
119 bc_packet_id,
120 document_header_id,
121 document_distribution_id,
122 gbp.award_id,
123 expenditure_type,
124 gbp.document_type,
125 nvl(entered_cr,0) entered_cr,
126 nvl(entered_dr,0) entered_dr,
127 gbp.award_set_id,
128 transaction_source,
129 gbp.request_id
130 from gms_bc_packets gbp,
131 gms_award_distributions adl
132 where gbp.packet_id = p_packet_id
133 and gbp.expenditure_type = p_expenditure_type
134 and gbp.award_id = p_award_id
135 and gbp.document_header_id = adl.expenditure_item_id
136 and gbp.document_distribution_id = adl.adl_line_num
137 and adl.document_type = 'ENC'
138 and adl.adl_status = 'A'
139 and nvl(gbp.entered_cr,0) + nvl(gbp.entered_dr,0) <> 0
140 and gbp.document_type = 'ENC'
141 and gbp.Status_code = 'P'
142 order by decode(adl.line_num_reversed, NULL, decode(adl.reversed_flag, NULL, 3, 2), 1) ASC,
143 (nvl(entered_dr,0) - nvl(entered_cr,0)) ASC;
144
145 -- --------------------------------
146 -- Change : status_code = 'I' ;
147 -- --------------------------------
148
149 X_rec_award_EXP_tot c_award_exp_total%ROWTYPE ;
150 X_tot_raw NUMBER ;
151 x_tot_burden NUMBER ;
152 x_cmt_tot_burden NUMBER ;
153 x_award_exp_limit NUMBER ;
154
155 -- ============================================================
156 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
157 -- : BURDENING.
158 -- ============================================================
159 x_calc_zero_limit BOOLEAN ;
160
161 -- ------------------------------------------------------------------------------------------------+
162 -- Function burden_allowed:
163
164 -- This will check if burden is allowed on the transaction ..
165 -- RETURN : Y -- burden cost should be calculated based on set up and limits.
166 -- N -- Burden cost should be zero.
167 -- ------------------------------------------------------------------------------------------------+
168 FUNCTION burden_allowed(p_transaction_source VARCHAR2) RETURN VARCHAR2
169 IS
170 l_allow_burden_flag pa_transaction_sources.allow_burden_flag%type;
171 BEGIN
172
173 If p_transaction_source is NULL then
174 RETURN 'Y';
175 End If;
176
177 Select DECODE( NVL(allow_burden_flag,'N'), 'N', 'Y', 'N')
178 into l_allow_burden_flag
179 from pa_transaction_sources
180 where transaction_source = p_transaction_source;
181
182 RETURN l_allow_burden_flag;
183 Exception
184 When no_data_found then
185 RETURN 'Y';
186 END burden_allowed;
187
188 -- BUG 3465191
189 -- performance issue with gms_commitment_encumbered_v
190 -- the cursors on gms_commitment_encumbered_v were using high shared memory.
191 -- open ref cursor open the selects based on the document type and selects
192 -- from the base tables directly.
193 --
194 PROCEDURE open_ref_cursor ( p_doc_adj IN OUT NOCOPY c_adj_rec,
195 p_doc_type IN VARCHAR2,
196 p_award_id IN NUMBER,
197 p_exp_type IN VARCHAR2,
198 p_dist_id IN NUMBER,
199 p_header_id IN NUMBER,
200 p_calling_seq IN VARCHAR2 ) is
201 l_choice NUMBER ;
202 BEGIN
203 g_error_procedure_name := 'open_ref_cursor';
204 IF g_debug = 'Y' THEN
205 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
206 END IF;
207
208 IF p_calling_seq = 'MAXIMIZE_BURDEN' THEN
209 l_choice := 1;
210 ELSIF p_calling_seq = 'CREATE_ADJPLUS_LOG' THEN
211 l_choice := 2;
212 ELSIF p_calling_seq = 'SELF_ADJUSTMENT' THEN
213 l_choice := 3;
214 ELSE
215 l_choice := 0;
216 END IF ;
217
218 IF l_choice = 0 THEN
219 return ;
220 END IF ;
221
222 IF p_doc_type = 'REQ' THEN
223 OPEN P_DOC_ADJ FOR
224 select vw.dist_id,
225 vw.header_id,
226 vw.amount,
227 vw.burden,
228 vw.award_set_id,
229 vw.adl_line_num,
230 (vw.amount - NVL(vw.burden,0) ) diff_amount,
231 vw.award_id,
232 vw.expenditure_type,
233 vw.project_id,
234 vw.task_id,
235 vw.expenditure_item_date,
236 vw.expenditure_organization_id,
237 vw.resource_list_member_id,
238 vw.bud_task_id,
239 vw.ind_compiled_set_id
240 from ( select rd.distribution_id dist_id,
241 rh.requisition_header_id header_id,
242 po_intg_document_funds_grp.get_active_encumbrance_func
243 ('REQUISITION',rd.distribution_id) amount,
244 adl.burdenable_raw_cost burden,
245 adl.award_set_id award_set_id,
246 adl.adl_line_num adl_line_num,
247 adl.award_id award_id,
248 rd.expenditure_type expenditure_type,
249 rd.project_id,
250 rd.task_id,
251 rd.expenditure_item_date,
252 rd.expenditure_organization_id,
253 adl.resource_list_member_id,
254 adl.bud_task_id,
255 adl.ind_compiled_set_id
256 from po_requisition_headers RH,
257 po_requisition_lines RL,
258 po_req_distributions RD,
259 gms_award_distributions ADL
260 where rh.type_lookup_code = 'PURCHASE'
261 and rh.requisition_header_id = rl.requisition_header_id
262 and nvl(rl.modified_by_agent_flag,'N') = 'N'
263 and rl.source_type_code = 'VENDOR'
264 and rd.requisition_line_id = rl.requisition_line_id
265 and nvl(rd.encumbered_flag,'N') = 'Y'
266 and adl.award_set_id = rd.award_id
267 and adl.distribution_id = rd.distribution_id
268 and adl.adl_status = 'A'
269 and adl.document_type = 'REQ'
270 and adl.adl_line_num = 1
271 and adl.award_id = p_award_id
272 and rd.expenditure_type = p_exp_type
273 --and nvl(rh.authorization_status,'NULL')= 'APPROVED' -- Commented as part of Bug 5037180
274 ) VW
275 where ( ( l_choice = 1 and ABS(vw.amount) > ABS(vw.burden) ) OR
276 ( l_choice = 2 and NVL(vw.burden,0) > 0 AND vw.dist_id <> p_dist_id ) OR
277 ( l_choice = 3 and vw.header_id <> p_header_id and vw.burden is not NULL )
278 )
279 order by vw.header_id desc , vw.dist_id desc ;
280
281 ELSIF p_doc_type = 'PO' THEN
282 -- BUG : 4908584
283 -- : R12.PJ:XB2:DEV:GMS: APPSPERF:GMS: PACKAGE: GMSBCPEXB.PLS . 1 SQL
284 -- : (Share Memory Size 2,009,186)
285 OPEN P_DOC_ADJ FOR
286 select vw.dist_id,
287 vw.header_id,
288 vw.amount,
289 vw.burden,
290 vw.award_set_id,
291 vw.adl_line_num,
292 (vw.amount - NVL(vw.burden,0) ) diff_amount,
293 vw.award_id,
294 vw.expenditure_type,
295 vw.project_id,
296 vw.task_id,
297 vw.expenditure_item_date,
298 vw.expenditure_organization_id,
299 vw.resource_list_member_id,
300 vw.bud_task_id,
301 vw.ind_compiled_set_id
302 from ( select pod.po_header_id header_id
303 , pod.po_distribution_id dist_ID
304 , po_intg_document_funds_grp.get_active_encumbrance_func('PO', pod.po_distribution_id)
305 amount
306 , adl.burdenable_raw_cost burden
307 , adl.award_set_id award_set_id
308 , adl.adl_line_num adl_line_num
309 , adl.award_id award_id
310 , pod.expenditure_type expenditure_type
311 , pod.project_id
312 , pod.task_id
313 , pod.expenditure_item_date
314 , pod.expenditure_organization_id
315 , adl.resource_list_member_id
316 , adl.bud_task_id
317 , adl.ind_compiled_set_id
318 from
319 po_distributions pod,
320 gms_award_distributions adl
321 where nvl(pod.encumbered_flag,'N')= 'Y'
322 and pod.award_id = adl.award_set_id
323 and adl.adl_line_num = 1
324 and adl.po_distribution_id = pod.po_distribution_id
325 --
326 -- 4004559 - PJ.M:B8:P13:OTH:PERF: FULL TABLE SCAN COST ON PO_DISTRIBUTIONS_ALL EXCEEDS 5
327 -- gms_budget_versions criteria was added so that index can be used.
328 -- and full table scan on po_distributions_all is gone.
329 --
330 and pod.project_id in ( select gbv.project_id
331 from gms_budget_versions gbv
332 where gbv.budget_type_code = 'AC'
333 and gbv.budget_status_code in ('S','W' )
334 and gbv.award_id = p_award_id )
335 and adl.adl_status = 'A'
336 and adl.fc_status = 'A'
337 and adl.document_type = 'PO'
338 and adl.award_id = p_award_id
339 and pod.expenditure_type = p_exp_type
340 ) VW
341 where (vw.amount - NVL(vw.burden,0) ) <> 0
342 AND ( ( l_choice = 1 and ABS(vw.amount) > ABS(vw.burden) ) OR
343 ( l_choice = 2 and NVL(vw.burden,0) > 0 AND vw.dist_id <> p_dist_id ) OR
344 ( l_choice = 3 and vw.header_id <> p_header_id and vw.burden is not NULL )
345 )
346 order by vw.header_id desc , vw.dist_id desc ;
347
348 ELSIF p_doc_type = 'AP' THEN
349 If l_choice in (2,3) then
350 OPEN P_DOC_ADJ FOR
351 select d.invoice_distribution_id dist_id -- AP Lines change
352 ,I.invoice_id header_id
353 , pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
354 I.invoice_id,
355 nvl(d.base_amount,d.amount),
356 'N',
357 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) amount
358 --, nvl(d.base_amount,d.amount) amount
359 , adl.burdenable_raw_cost burden
360 , adl.award_set_id award_set_id
361 , adl.adl_line_num adl_line_num
362
363 , ( pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
364 I.invoice_id,
365 nvl(d.base_amount,d.amount),
366 'N',
367 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
368 --, (nvl(d.base_amount,d.amount) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
369 , adl.award_id award_id
370 , d.expenditure_type expenditure_type
371 , d.project_id
372 , d.task_id
373 , d.expenditure_item_date
374 , d.expenditure_organization_id
375 , adl.resource_list_member_id
376 , adl.bud_task_id
377 , adl.ind_compiled_set_id
378 from ap_invoices I,
379 ap_invoice_distributions D,
380 gms_award_distributions ADL,
381 GL_LEDGERS G
382 where i.invoice_id = d.invoice_id
383 and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
384 I.invoice_id,
385 nvl(d.base_amount,d.amount),
386 'N',
387 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N')) <> 0
388 and decode(d.pa_addition_flag,'Z','Y','G', 'Y','T','Y','E','Y',null,'N', d.pa_addition_flag) <> 'Y'
389 and nvl(d.match_status_flag,'N') = 'A'
390 and d.award_id = adl.award_set_id
391 and G.LEDGER_ID = D.SET_OF_BOOKS_ID
392 and adl.invoice_id = i.invoice_id
393 and adl.invoice_distribution_id = d.invoice_distribution_id
394 and adl.adl_status = 'A'
395 and adl.adl_line_num = 1
396 and adl.document_type = 'AP'
397 and nvl(adl.fc_status,'N') = 'A'
398 and d.match_status_flag = 'A'
399 and adl.award_id = p_award_id
400 and d.expenditure_type = p_exp_type
401 and d.line_type_lookup_code <> 'PREPAY'
402 and I.invoice_type_lookup_code <> 'PREPAYMENT'
403 and ( --( l_choice = 1 and ABS(nvl(d.base_amount,d.amount ) ) > ABS(NVL(adl.burdenable_raw_cost,0) )
404 --) OR
405 ( l_choice = 2 and NVL(adl.burdenable_raw_cost,0) > 0
406 and d.invoice_distribution_id <> p_dist_id ) OR -- AP Lines change
407 ( l_choice = 3 and i.invoice_id <> p_header_id
408 and adl.burdenable_raw_cost is not NULL )
409 )
410 order by I.invoice_id desc , d.invoice_distribution_id desc ; -- AP Lines change
411 ElsIf l_choice = 1 then
412 OPEN P_DOC_ADJ FOR
413 select d.invoice_distribution_id dist_id -- AP Lines change
414 ,I.invoice_id header_id
415 , pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
416 I.invoice_id,
417 nvl(d.base_amount,d.amount),
418 'N',
419 'GMS' ) amount
420 --, nvl(d.base_amount,d.amount) amount
421 , adl.burdenable_raw_cost burden
422 , adl.award_set_id award_set_id
423 , adl.adl_line_num adl_line_num
424 , ( pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
425 I.invoice_id,
426 nvl(d.base_amount,d.amount),
427 'N',
428 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
429 --, (nvl(d.base_amount,d.amount) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
430 , adl.award_id award_id
431 , d.expenditure_type expenditure_type
432 , d.project_id
433 , d.task_id
434 , d.expenditure_item_date
435 , d.expenditure_organization_id
436 , adl.resource_list_member_id
437 , adl.bud_task_id
438 , adl.ind_compiled_set_id
439 from ap_invoices I,
440 ap_invoice_distributions D,
441 gms_award_distributions ADL,
442 gl_ledgers g
443 where i.invoice_id = d.invoice_id
444 and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
445 I.invoice_id,
446 nvl(d.base_amount,d.amount),
447 'N',
448 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) <> 0
449 and decode(d.pa_addition_flag,'Z','Y','G', 'Y','T','Y','E','Y',null,'N', d.pa_addition_flag) <> 'Y'
450 and nvl(d.match_status_flag,'N') = 'A'
451 and d.award_id = adl.award_set_id
452 and adl.invoice_id = i.invoice_id
453 and adl.invoice_distribution_id = d.invoice_distribution_id
454 and adl.adl_status = 'A'
455 and adl.adl_line_num = 1
456 and adl.document_type = 'AP'
457 and nvl(adl.fc_status,'N') = 'A'
458 and d.match_status_flag = 'A'
459 and adl.award_id = p_award_id
460 and d.expenditure_type = p_exp_type
461 and d.line_type_lookup_code <> 'PREPAY'
462 and I.invoice_type_lookup_code <> 'PREPAYMENT'
463 and nvl(d.base_amount,d.amount) > 0
464 and nvl(d.base_amount,d.amount) <> nvl(adl.burdenable_raw_cost,0)
465 AND G.LEDGER_ID = D.SET_OF_BOOKS_ID
466 order by (nvl(d.base_amount,d.amount) - nvl(adl.burdenable_raw_cost,0)) desc ;
467 End If;
468
469 ELSIF p_doc_type = 'ENC' THEN
470 OPEN P_DOC_ADJ FOR
471 SELECT 1 dist_id
472 , enc.encumbrance_item_id header_id
473 , enc.amount amount
474 , adl.burdenable_raw_cost burden
475 , adl.award_set_id award_set_id
476 , adl.adl_line_num adl_line_num
477 , (enc.amount - NVL(adl.burdenable_raw_cost,0) ) diff_amount
478 , adl.award_id award_id
479 , enc.encumbrance_type expenditure_type
480 , adl.project_id
481 , enc.task_id
482 , trunc(enc.encumbrance_item_date) expenditure_item_date
483 , nvl(enc.override_to_organization_id,ge.incurred_by_organization_id) expenditure_organization_id
484 , adl.resource_list_member_id
485 , adl.bud_task_id
486 , adl.ind_compiled_set_id
487 from gms_encumbrance_items enc,
488 gms_award_distributions adl,
489 gms_encumbrances_all ge
490 where enc.encumbrance_item_id = adl.expenditure_item_id
491 and nvl(enc.enc_distributed_flag,'N') = 'Y'
492 and adl.adl_status = 'A'
493 and adl.document_type = 'ENC'
494 AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
495 AND adl.line_num_reversed is null --Bug 5726575
496 and adl.award_id = p_award_id
497 and enc.encumbrance_type = p_exp_type
498 and ge.encumbrance_id = enc.encumbrance_id
499 and ( (l_choice = 1 and ABS(enc.amount ) > ABS(NVL(adl.burdenable_raw_cost,0)) ) OR
500 (l_choice = 2 and NVL(adl.burdenable_raw_cost,0) > 0
501 and 1 <> p_dist_id ) OR
502 (l_choice = 3 and enc.encumbrance_item_id <> p_header_id
503 and adl.burdenable_raw_cost is not NULL )
504 )
505 order by enc.encumbrance_item_id desc; -- Bug 3697483, changed order by
506 END IF ;
507
508 IF g_debug = 'Y' THEN
509 gms_error_pkg.gms_debug (g_error_procedure_name||':End','C');
510 END IF;
511 END open_ref_cursor ;
512
513 -- ----------------------------------------------
514 -- DOC: procedure get the totals raw and burden
515 -- cost for award and expenditure type.
516 -- ----------------------------------------------
517 PROCEDURE PROC_GET_AWARD_EXP_TOTAL( P_packet_id NUMBER,
518 P_award_id NUMBER,
519 P_EXP_TYPE VARCHAR2
520 ) IS
521
522 x_doc_type VARCHAR2(3) ;
523 x_act_raw NUMBER ;
524 x_burden_cost NUMBER ;
525 l_arrival_seq NUMBER ;
526 l_enc_raw NUMBER ;
527 l_exp_raw NUMBER ;
528 l_po_raw NUMBER ;
529 l_req_raw NUMBER ;
530 l_ap_raw NUMBER ;
531
532 l_enc_brc NUMBER ;
533 l_exp_brc NUMBER ;
534 l_po_brc NUMBER ;
535 l_req_brc NUMBER ;
536 l_ap_brc NUMBER ;
537
538 CURSOR get_burden_cost_limit is
539 SELECT gae.burden_cost_limit
540 FROM gms_allowable_expenditures gae,
541 gms_awards ga
542 where gae.allowability_schedule_id = ga.allowable_schedule_id
543 and gae.expenditure_type = P_EXP_TYPE
544 and ga.award_id = P_award_id;
545 --
546 -- Start of comment
547 -- bug : 3092603
548 -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
549 -- Change desc : l_arrival_seq code was added and decode was added
550 -- to avoid multiple selects for each documents.
551 -- Cursor changed was : C_ACT
552 -- End of comment.
553 --
554 CURSOR C_ACT is
555 SELECT SUM( decode(pkt.document_type, 'ENC',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) enc_raw,
556 SUM( decode(pkt.document_type, 'EXP',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) exp_raw,
557 SUM( decode(pkt.document_type, 'PO', (nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) po_raw,
558 SUM( decode(pkt.document_type, 'REQ',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) req_raw,
559 SUM( decode(pkt.document_type, 'AP', (nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) ap_raw,
560 SUM( decode(pkt.document_type, 'ENC',nvl(pkt.burdenable_raw_cost,0), 0 ) ) enc_brc,
561 SUM( decode(pkt.document_type, 'EXP',nvl(pkt.burdenable_raw_cost,0), 0 ) ) exp_brc,
562 SUM( decode(pkt.document_type, 'PO', nvl(pkt.burdenable_raw_cost,0), 0 ) ) po_brc,
563 SUM( decode(pkt.document_type, 'REQ',nvl(pkt.burdenable_raw_cost,0), 0 ) ) req_brc,
564 SUM( decode(pkt.document_type, 'AP', nvl(pkt.burdenable_raw_cost,0), 0 ) ) ap_brc
565 FROM GMS_BC_PACKETS PKT,
566 gms_budget_versions gbv
567 WHERE pkt.award_id = p_award_id
568 and pkt.expenditure_type = p_exp_type
569 and pkt.status_code in ('A', 'P','I' )
570 and burden_calculation_seq > 0
571 and gbv.budget_version_id = pkt.budget_version_id
572 and gbv.budget_status_code = 'B'
573 and substr(NVL(pkt.result_code,'P'),1,1) <> 'F'
574 and decode(pkt.status_code,
575 'A',1,
576 'P',decode(SIGN(NVL(entered_dr,0)-NVL(entered_cr,0)),
577 -1,decode(pkt.packet_id,P_packet_id,1,0),1),
578 'I', decode(SIGN(NVL(entered_dr,0)-NVL(entered_cr,0)),
579 -1,decode(pkt.packet_id,P_packet_id,1,0),1)
580 ) = 1;
581
582 -- burden_calculation_seq > 0 used instead of burden_calculation_seq IS NOT NULL
583 -- to use the index, value populated for LIMIT scenario
584 -- Filter criteria: pick all records that has completed burdenable raw cost calc.
585 -- i.e. burden_calculation_seq > 0 and that has not been posted to balance summary
586 -- Look across budget versions ...
587
588 -- Also, as burden_calculation_seq is zero, current packet will not be considered ..
589 -- Do not use parent_bc_packet_id is null as burden log has parent_bc_packet_id value
590
591 -- No need to check with budget version .. Note: budget_version_id is not populated
592 -- on records before cost plus, this can lead to the transactions being skipped.
593
594
595 BEGIN
596 g_error_procedure_name := 'proc_get_award_exp_total';
597 IF g_debug = 'Y' THEN
598 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
599 END IF;
600
601 x_calc_zero_limit := FALSE ;
602
603 open get_burden_cost_limit;
604 fetch get_burden_cost_limit into x_award_exp_limit;
605
606 if get_burden_cost_limit%NOTFOUND then
607 x_award_exp_limit:= 0 ;
608 ELSE
609 IF x_award_exp_limit = 0 THEN
610
611 x_calc_zero_limit := TRUE ;
612
613 END IF ;
614 end if;
615
616 close get_burden_cost_limit;
617
618 open c_award_exp_total(p_award_id, p_exp_type);
619 fetch c_award_exp_total into x_rec_award_exp_tot ;
620
621 IF c_award_exp_total%NOTFOUND THEN
622 x_rec_award_exp_tot.req_raw_cost := 0 ;
623 x_rec_award_exp_tot.PO_raw_cost := 0 ;
624 x_rec_award_exp_tot.enc_raw_cost := 0 ;
625 x_rec_award_exp_tot.ap_raw_cost := 0 ;
626 x_rec_award_exp_tot.exp_raw_cost := 0 ;
627 x_rec_award_exp_tot.req_burdenable_cost := 0 ;
628 x_rec_award_exp_tot.po_burdenable_cost := 0 ;
629 x_rec_award_exp_tot.enc_burdenable_cost := 0 ;
630 x_rec_award_exp_tot.AP_burdenable_cost := 0 ;
631 x_rec_award_exp_tot.exp_burdenable_cost := 0 ;
632 END IF;
633
634 close c_award_exp_total;
635 -- ---------------------------------------------------
636 -- Find out NOCOPY the unposted balances from GMS_BC_PACKETS
637 -- The gms_award_exp_type_act_cost is updated in
638 -- gms_gl_return_code process and burden_posted_flag
639 -- is updated to 'Y'. It is possible that there exists
640 -- some records for which funds_check has approved and
641 -- amounts are not posted.
642 -- ---------------------------------------------------
643 -- Start of comment
644 -- bug : 3092603
645 -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
646 -- Change desc : get the maximum arrival order sequence of the
647 -- bc packets. This is used to determine the
648 -- pending totals of raw and burdenable cost.
649 -- End of comment.
650 --
651 SELECT max(arrival_seq)
652 into l_arrival_seq
653 from gms_bc_packet_arrival_order ;
654
655 l_arrival_seq := NVL(l_arrival_seq,0) ;
656
657 -- ----------------------------------------
658 -- fetch pending cost from GMS_BC_PACKETS
659 -- ----------------------------------------
660 -- Start of comment
661 -- bug : 3092603
662 -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
663 -- Change desc : get the document level breakups of the raw and
664 -- burdenable raw cost for all the pending unposted
665 -- transactions in bc packets. use cursor c_act.
666 -- End of comment.
667 --
668 open c_act ;
669 fetch c_act into l_enc_raw,
670 l_exp_raw,
671 l_po_raw,
672 l_req_raw,
673 l_ap_raw,
674 l_enc_brc,
675 l_exp_brc,
676 l_po_brc,
677 l_req_brc,
678 l_ap_brc ;
679
680 close c_act ;
681
682 x_rec_award_exp_tot.req_raw_cost := x_rec_award_exp_tot.req_raw_cost + nvl(l_req_raw,0) ;
683 x_rec_award_exp_tot.req_burdenable_cost := x_rec_award_exp_tot.req_burdenable_cost + nvl(l_req_brc,0) ;
684
685 x_rec_award_exp_tot.po_raw_cost := x_rec_award_exp_tot.po_raw_cost + nvl(l_po_raw,0) ;
686 x_rec_award_exp_tot.po_burdenable_cost := x_rec_award_exp_tot.po_burdenable_cost + nvl(l_po_brc,0) ;
687
688 x_rec_award_exp_tot.AP_raw_cost := x_rec_award_exp_tot.AP_raw_cost + nvl(l_ap_raw,0) ;
689 x_rec_award_exp_tot.AP_burdenable_cost := x_rec_award_exp_tot.AP_burdenable_cost + nvl(l_ap_brc,0) ;
690
691 x_rec_award_exp_tot.ENC_raw_cost := x_rec_award_exp_tot.ENC_raw_cost + nvl(l_enc_raw,0) ;
692 x_rec_award_exp_tot.ENC_burdenable_cost := x_rec_award_exp_tot.ENC_burdenable_cost + nvl(l_enc_brc,0) ;
693
694 x_rec_award_exp_tot.EXP_raw_cost := x_rec_award_exp_tot.EXP_raw_cost + nvl(l_exp_raw,0) ;
695 x_rec_award_exp_tot.EXP_burdenable_cost := x_rec_award_exp_tot.EXP_burdenable_cost + nvl(l_exp_brc,0) ;
696
697 -- Start of comment
698 -- bug : 3092603
699 -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
700 -- End of bug fix.
701 --
702
703 x_tot_raw := x_rec_award_exp_tot.REQ_raw_cost +
704 x_rec_award_exp_tot.PO_raw_cost +
705 x_rec_award_exp_tot.ENC_raw_cost +
706 x_rec_award_exp_tot.AP_raw_cost +
707 x_rec_award_exp_tot.EXP_raw_cost ;
708
709 x_tot_burden := x_rec_award_exp_tot.REQ_burdenable_cost +
710 x_rec_award_exp_tot.PO_burdenable_cost +
711 x_rec_award_exp_tot.ENC_burdenable_cost +
712 x_rec_award_exp_tot.AP_burdenable_cost +
713 x_rec_award_exp_tot.EXP_burdenable_cost ;
714
715 x_cmt_tot_burden := x_rec_award_exp_tot.REQ_burdenable_cost +
716 x_rec_award_exp_tot.PO_burdenable_cost +
717 x_rec_award_exp_tot.ENC_burdenable_cost +
718 x_rec_award_exp_tot.AP_burdenable_cost ;
719
720
721 IF g_debug = 'Y' THEN
722 gms_error_pkg.gms_debug (g_error_procedure_name||':x_tot_raw:'||x_tot_raw||';'||'x_tot_burden:'||x_tot_burden||';'||'x_cmt_tot_burden:'||x_cmt_tot_burden,'C');
723 END IF;
724
725 EXCEPTION
726 WHEN RESOURCE_BUSY THEN
727 IF g_debug = 'Y' THEN
728 gms_error_pkg.gms_debug (g_error_procedure_name||':Resource Busy Exception','C');
729 END IF;
730 -- We couldn't acquire the locks at this time so
731 -- We need to abort the processing and have the
732 -- stataus Failed .
733 -- F40 - Unable to acquire Locks on GMS_AWARD_EXP_TYPE_ACT_COST
734 -- ------------------------------------------------------------
735 IF get_burden_cost_limit%ISOPEN THEN
736 CLOSE get_burden_cost_limit ;
737 END IF ;
738
739 IF C_ACT%ISOPEN THEN
740 CLOSE c_act ;
741 END IF ;
742
743 IF c_award_exp_total%ISOPEN THEN
744 close c_award_exp_total ;
745 END IF ;
746
747 g_dummy := SQLERRM;
748 IF g_debug = 'Y' THEN
749 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
750 END IF;
751
752 RAISE ;
753 WHEN others then
754 IF g_debug = 'Y' THEN
755 gms_error_pkg.gms_debug (g_error_procedure_name||':When Others Exception','C');
756 END IF;
757
758 IF get_burden_cost_limit%ISOPEN THEN
759 CLOSE get_burden_cost_limit ;
760 END IF ;
761
762 IF C_ACT%ISOPEN THEN
763 CLOSE c_act ;
764 END IF ;
765
766 IF c_award_exp_total%ISOPEN THEN
767 close c_award_exp_total ;
768 END IF ;
769
770 g_dummy := SQLERRM;
771 IF g_debug = 'Y' THEN
772 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
773 END IF;
774
775 RAISE ;
776 END PROC_GET_AWARD_EXP_TOTAL ;
777 -- ============= END OF PROC_GET_AWARD_EXP_TOTAL ==========================
778
779
780 -- ------------------------------------------------------------------------------------------------
781 -- Update expenditure_category and revenue category on gms_bc_packets because of change in RLMI API.
782 -- Update person_id,job_id,vendor_id columns on gms_bc_packets.
783 -- This is done before setup_rlmi is called Bug 2143160
784 -- ------------------------------------------------------------------------------------------------
785 PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
786 BEGIN
787 --
788 -- To update expenditure_category and revenue category
789 UPDATE gms_bc_packets pkt
790 SET (pkt.expenditure_category,pkt.revenue_category) =
791 (select pe.expenditure_category,pe.revenue_category_code
792 from pa_expenditure_types pe
793 where pe.expenditure_type = pkt.expenditure_type)
794 WHERE pkt.packet_id = x_packet_id;
795 END update_exp_rev_cat;
796 -- ------------------------------------------------------------------------------------------------
797 -- ------------------------------------------------------------------------------------------------
798 -- This procedure updates the gms_bc_packets top_task_id and parent_resource_id for a packet
799 -- This is mainly used during interface process.
800 -- Bug 2143160
801 -- ------------------------------------------------------------------------------------------------
802 PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
803 BEGIN
804 UPDATE gms_bc_packets pkt
805 SET pkt.top_task_id = (SELECT top_task_id
806 FROM pa_tasks
807 WHERE task_id = pkt.task_id)
808 WHERE pkt.packet_id = x_packet_id
809 AND pkt.top_task_id IS NULL;
810
811 UPDATE gms_bc_packets pkt
812 SET pkt.parent_resource_id = (SELECT parent_member_id
813 FROM pa_resource_list_members
814 WHERE resource_list_member_id = pkt.resource_list_member_id)
815 WHERE pkt.packet_id = x_packet_id
816 AND pkt.parent_resource_id IS NULL;
817
818 END update_top_tsk_par_res;
819
820 -- ----------------------------------------------------------------------------------------------------
821 -- -------------------------------------------------------------------------
822 -- create_burden_adjustments : Function creates burden adjusmtent entry in gms_bc_packets
823 -- -------------------------------------------------------------------------
824 PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
825 p_project_id pa_projects_all.project_id%type,
826 p_task_id pa_tasks.task_id%type,
827 p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828 p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829 p_rlmi gms_bc_packets.resource_list_member_id%type,
830 p_bud_task_id gms_bc_packets.bud_task_id%type,
831 p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
832 ) IS
833 PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
834
835 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
836 l_top_task_id gms_bc_packets.top_task_id%type;
837 l_parent_resource_id gms_bc_packets.parent_resource_id%type;
838 l_budget_version_id gms_bc_packets.budget_version_id%type;
839
840 l_stage varchar2(25);
841
842 BEGIN
843
844 g_error_procedure_name := 'create_burden_adjustments';
845 IF g_debug = 'Y' THEN
846 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
847 END IF;
848
849 x_rec_log := p_rec_log ;
850 x_rec_log.last_update_date := SYSDATE ;
851 x_rec_log.last_updated_by := nvl(fnd_global.user_id,0) ;
852 x_rec_log.created_by := nvl(fnd_global.user_id,0) ;
853 x_rec_log.creation_date := SYSDATE ;
854 x_rec_log.last_update_login := nvl(fnd_global.user_id,0) ;
855
856
857 -- Get set_of_books_id ..
858 l_stage := 'Derive Set of Books';
859
860 If g_set_of_books_id is null then
861 Select set_of_books_id
862 into g_set_of_books_id
863 from pa_implementations;
864 End If;
865
866 -- Get top_task_id
867 l_stage := 'Derive Top Task';
868
869 Select top_task_id
870 into l_top_task_id
871 from pa_tasks
872 where task_id = p_task_id;
873
874 -- Get parent_member_id
875 l_stage := 'Derive Parent Resource';
876
877 Select parent_member_id
878 into l_parent_resource_id
879 from pa_resource_list_members
880 where resource_list_member_id = p_rlmi;
881
882 -- Get budget_version_id
883 l_stage := 'Derive Budget Version';
884
885 Select budget_version_id
886 into l_budget_version_id
887 from gms_budget_versions
888 where award_id = x_rec_log.award_id
889 and project_id = p_project_id
890 and budget_status_code ='B'
891 and current_flag = 'Y';
892
893 -- Create burden adjustment entry ..
894 l_stage := 'Create Burden Entry';
895
896 insert into gms_bc_packets(
897 packet_id,
898 project_id,
899 award_id,
900 task_id,
901 budget_version_id,
902 expenditure_type,
903 expenditure_item_date,
904 actual_flag,
905 status_code,
906 last_update_date,
907 last_updated_by,
908 created_by,
909 creation_date,
910 last_update_login,
911 set_of_books_id,
912 je_category_name,
913 je_source_name,
914 transfered_flag,
915 document_type,
916 expenditure_organization_id,
917 document_header_id,
918 document_distribution_id,
919 entered_dr,
920 entered_cr,
921 bc_packet_id,
922 request_id,
923 burden_adj_bc_packet_id, -- burden_adj_bc_packet_id will store the bc_packet_id of txn. being FC'ed
924 parent_bc_packet_id, -- parent_bc_packet_id will store the bc_packet_id of txn. being FC'ed
925 burden_adjustment_flag,
926 burdenable_raw_cost,
927 resource_list_member_id,
928 bud_task_id,
929 ind_compiled_set_id,
930 top_task_id,
931 parent_resource_id,
932 burden_calculation_seq,
933 source_event_id) /* Added for Bug 5645290 */
934 values(x_rec_log.packet_id,
935 p_project_id,
936 x_rec_log.award_id,
937 p_task_id,
938 l_budget_version_id,
939 x_rec_log.expenditure_type,
940 p_expenditure_item_date,
941 decode(x_rec_log.document_type,'EXP','A','E'), -- Actual_flag
942 decode(x_rec_log.document_type,'EXP','P','I'), -- Bug 5037180 : Status_code is always 'P'
943 x_rec_log.last_update_date,
944 x_rec_log.last_updated_by,
945 x_rec_log.created_by,
946 x_rec_log.creation_date,
947 x_rec_log.last_update_login,
948 g_set_of_books_id,
949 decode(x_rec_log.document_type,'REQ','Requisitions',
950 'PO','Purchases',
951 'AP','Purchase Invoices',
952 'ENC','Project Accounting'), --Category
953 decode(x_rec_log.document_type,'REQ','Purchasing',
954 'PO','Purchasing',
955 'AP','Payables',
956 'ENC','Miscellaneous Transaction'), -- Source:Hard coding 'Misc Tran ..'
957 'N', --transferred_flag
958 x_rec_log.document_type,
959 p_expenditure_org_id,
960 x_rec_log.document_header_id,
961 x_rec_log.document_distribution_id,
962 0, --entered_dr,
963 0, --entered_cr
964 gms_bc_packets_s.nextval,
965 g_request_id,
966 x_rec_log.bc_packet_id,
967 x_rec_log.bc_packet_id,
968 'Y', -- burden_adjustment_flag,
969 x_rec_log.adj_burdenable_amount,
970 p_rlmi,
971 p_bud_task_id,
972 p_ind_compiled_set_id,
973 l_top_task_id,
974 l_parent_resource_id,
975 x_rec_log.adjustment_id,
976 (select source_event_id
977 from gms_bc_packets
978 where bc_packet_id = x_rec_log.bc_packet_id)); /* Added for Bug 5645290 */
979
980
981 -- ---------------------------------------------------------+
982 -- Update the running total of award and expenditure type.
983 -- ---------------------------------------------------------+
984 IF x_rec_log.source_flag = 'N' THEN
985 return ;
986 END IF ;
987
988 IF p_rec_log.document_type = 'REQ' THEN
989 x_rec_award_exp_tot.req_burdenable_cost := x_rec_award_exp_tot.req_burdenable_cost +
990 nvl(p_rec_log.adj_burdenable_amount,0) ;
991 ELSIF p_rec_log.document_type = 'PO' THEN
992 x_rec_award_exp_tot.PO_burdenable_cost := x_rec_award_exp_tot.PO_burdenable_cost +
993 nvl(p_rec_log.adj_burdenable_amount,0) ;
994 ELSIF p_rec_log.document_type = 'ENC' THEN
995 x_rec_award_exp_tot.ENC_burdenable_cost := x_rec_award_exp_tot.ENC_burdenable_cost +
996 nvl(p_rec_log.adj_burdenable_amount,0) ;
997 ELSIF p_rec_log.document_type = 'AP' THEN
998 x_rec_award_exp_tot.AP_burdenable_cost := x_rec_award_exp_tot.AP_burdenable_cost +
999 nvl(p_rec_log.adj_burdenable_amount,0) ;
1000 ELSIF p_rec_log.document_type = 'EXP' THEN
1001 x_rec_award_exp_tot.EXP_burdenable_cost := x_rec_award_exp_tot.EXP_burdenable_cost +
1002 nvl(p_rec_log.adj_burdenable_amount,0) ;
1003 END IF ;
1004
1005 x_tot_burden:= x_tot_burden + nvl(p_rec_log.adj_burdenable_amount,0) ;
1006
1007 IF g_debug = 'Y' THEN
1008 gms_error_pkg.gms_debug (g_error_procedure_name||':End','C');
1009 END IF;
1010
1011 COMMIT; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1012
1013 EXCEPTION
1014 When others then
1015
1016 g_dummy := SQLERRM;
1017 IF g_debug = 'Y' THEN
1018 gms_error_pkg.gms_debug (g_error_procedure_name||':'||l_stage||';'||g_dummy,'C');
1019 END IF;
1020
1021 RAISE ;
1022 END create_burden_adjustments;
1023 -- ====== END create_burden_adjustments ================================================
1024
1025 -- ----------------------------------------------------
1026 -- get_prev_unposted_adj : This gets from the log all
1027 -- the adjustments not applied to the documents.
1028 -- ----------------------------------------------------
1029 FUNCTION get_prev_unposted_adj( p_header_id NUMBER,
1030 p_dist_id NUMBER,
1031 p_doc_type varchar2 )
1032 return NUMBER IS
1033
1034 X_prev_adj NUMBER ;
1035
1036 Cursor get_prev_adj is
1037 SELECT SUM(burdenable_raw_cost)
1038 FROM gms_bc_packets
1039 WHERE document_header_id = p_header_id
1040 AND document_distribution_id = p_dist_id
1041 AND document_type = p_doc_type
1042 AND burden_adjustment_flag = 'Y'
1043 AND nvl(burden_posted_flag,'N') <> 'X'
1044 AND status_code IN ('P','A','I');
1045
1046 -- When the burdenable raw cost is posted on the source document, burden_posted_flag wil be updated to 'X'
1047 -- Also, status_code of 'X' means that the amount has been posted into Award-Exp burden balances ..
1048 BEGIN
1049 X_prev_adj := 0 ;
1050 OPEN get_prev_adj ;
1051 fetch get_prev_adj INTO X_prev_adj ;
1052 IF get_prev_adj%NOTFOUND THEN
1053 X_prev_adj := 0 ;
1054 END IF ;
1055 return NVL(X_prev_adj,0) ;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058
1059 g_dummy := SQLERRM;
1060 IF g_debug = 'Y' THEN
1061 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
1062 END IF;
1063
1064 RAISE ;
1065 END get_prev_unposted_adj ;
1066 -- ============== END OF get_prev_unposted_adj ==========
1067
1068 -- ---------------------------------------------------------------
1069 -- The following procedure gets idc_schedule_id for an award
1070 -- based on override schedules and using pa_cost_plus gets an rate
1071 -- schedule revision ID.
1072 -- ---------------------------------------------------------------
1073 PROCEDURE GET_AWARD_IND_RATE_SCH_REV_ID(x_award_id IN Number,
1074 x_task_id IN Number, -- Bug 2097676: Multiple IDC Build
1075 x_exp_item_date IN Date,
1076 x_rate_sch_rev_id IN OUT NOCOPY Number,
1077 x_status IN OUT NOCOPY Number,
1078 x_stage IN OUT NOCOPY Number)
1079 IS
1080 l_rate_sch_id NUMBER(15);
1081 l_sch_fixed_date DATE;
1082
1083 -- Start of code, Bug 2097676: Multiple IDC Build
1084
1085 l_project_id NUMBER;
1086 l_task_id NUMBER;
1087
1088 CURSOR prj_task IS
1089 SELECT project_id, top_task_id
1090 FROM pa_tasks
1091 WHERE task_id = x_task_id;
1092
1093 CURSOR override_schedule_task( l_task_id number) IS
1094 SELECT idc_schedule_id, cost_ind_sch_fixed_date
1095 FROM gms_override_schedules
1096 WHERE award_id = x_award_id
1097 AND task_id = l_task_id;
1098
1099 CURSOR override_schedule_project(l_project_id number) IS
1100 SELECT idc_schedule_id, cost_ind_sch_fixed_date
1101 FROM gms_override_schedules
1102 WHERE award_id = x_award_id
1103 AND project_id = l_project_id
1104 AND task_id is NULL;
1105
1106 -- End of code, Bug 2097676: Multiple IDC Build
1107
1108 BEGIN
1109
1110 -- initialize variables.
1111 x_stage := 250;
1112
1113 -- Start of code, Bug 2097676: Multiple IDC Build
1114
1115 OPEN prj_task;
1116 FETCH prj_task INTO l_project_id, l_task_id;
1117 CLOSE prj_task;
1118
1119 OPEN override_schedule_task(l_task_id);
1120 FETCH override_schedule_task INTO l_rate_sch_id, l_sch_fixed_date;
1121 CLOSE override_schedule_task;
1122
1123 IF l_rate_sch_id is NULL THEN
1124 OPEN override_schedule_project(l_project_id);
1125 FETCH override_schedule_project INTO l_rate_sch_id, l_sch_fixed_date;
1126 CLOSE override_schedule_project;
1127 END IF;
1128
1129 IF l_rate_sch_id is NULL THEN -- End of code, Bug 2097676: Multiple IDC Build
1130 SELECT idc_schedule_id,
1131 cost_ind_sch_fixed_date
1132 INTO l_rate_sch_id,
1133 l_sch_fixed_date
1134 FROM gms_awards_all -- bug 3117503. changed to _all.
1135 WHERE award_id = x_award_id;
1136 END IF; -- Bug 2097676: Multiple IDC Build
1137
1138 IF l_rate_sch_id is not null THEN
1139 pa_cost_plus.get_revision_by_date (l_rate_sch_id,
1140 l_sch_fixed_date,
1141 X_EXP_ITEM_DATE,
1142 X_RATE_SCH_REV_ID,
1143 X_STATUS,
1144 X_STAGE );
1145 ELSE
1146 x_status := 1; -- Award must have a burden schedule
1147 END IF;
1148
1149 RETURN;
1150
1151 EXCEPTION
1152 WHEN NO_DATA_FOUND THEN
1153 x_status := 1; -- Award must have a burden schedule
1154 WHEN OTHERS THEN
1155 x_status := SQLCODE; -- System error
1156 END get_award_ind_rate_sch_rev_id;
1157
1158 -- ----------------------------------------------------------------------------------
1159 -- BUG 1522671 - For REQ,PO,AP if there is any idc rate changes when REQ->PO->AP
1160 -- then the reversing committments should be based on the orginal
1161 -- idc rate. This is also applicable if the above committments are reversed.
1162 -- For this the orignal idc rate schedule (ind_compiled_set_id)
1163 -- is picked from gms_award_distributions for the committements
1164 -- ----------------------------------------------------------------------------------
1165
1166 -- ==================================================================================
1167 -- bug : 1698738 - IDC RATE CHANGES CAUSE DISCREPENCIES IN S.I. INTERFACE TO PROJECTS.
1168 -- Reorganized award_cmt_compiled_set_id
1169 -- added l_calc_new
1170 -- added condition for doc_type = 'EXP'
1171 -- ===================================================================================
1172 -- 2305048 ( DATA ERRORS MAKE IT IMPOSSIBLE TO BRING SUPPLIER INVOICES INTO OGA )
1173 FUNCTION award_cmt_compiled_set_id
1174 ( x_document_header_id IN NUMBER,
1175 x_document_distribution_id IN NUMBER,
1176 x_task_id IN NUMBER,
1177 x_document_type IN VARCHAR2,
1178 x_expenditure_item_date IN DATE,
1179 p_expenditure_type IN VARCHAR2, --Bug 3003584
1180 x_organization_id IN NUMBER,
1181 x_schedule_type IN VARCHAR2,
1182 x_award_id IN NUMBER) RETURN NUMBER IS
1183
1184 l_compiled_set_id number;
1185 l_calc_new BOOLEAN ;
1186
1187 BEGIN
1188
1189 l_calc_new := FALSE ;
1190
1191 IF x_document_type = 'REQ' THEN
1192
1193 BEGIN
1194 select adl.ind_compiled_set_id
1195 into l_compiled_set_id
1196 from gms_award_distributions adl
1197 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1198 --po_req_distributions_all por
1199 where adl.award_id = x_award_id
1200 and adl.task_id = x_task_id
1201 and adl.distribution_id = x_document_distribution_id
1202 and adl.document_type = 'REQ'
1203 and adl.adl_status = 'A'
1204 and adl.fc_status = 'A';
1205 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1206 --and adl.award_set_id = por.award_id
1207 --and por.distribution_id = x_document_distribution_id ;
1208
1209 EXCEPTION
1210 when no_data_found then
1211 l_calc_new := TRUE ;
1212 END;
1213 ELSIF x_document_type = 'PO' THEN
1214 BEGIN
1215 select adl.ind_compiled_set_id
1216 into l_compiled_set_id
1217 from gms_award_distributions adl
1218 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1219 -- po_distributions_all pod
1220 where adl.award_id = x_award_id
1221 and adl.task_id = x_task_id
1222 and adl.po_distribution_id = x_document_distribution_id
1223 and adl.document_type = 'PO'
1224 and adl.adl_status = 'A'
1225 and adl.fc_status = 'A'
1226 and adl.adl_line_num = 1 ;
1227 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1228 /*and pod.po_distribution_id = x_document_distribution_id
1229 and pod.award_id = adl.award_set_id ;*/
1230
1231 EXCEPTION
1232 when no_data_found then
1233 l_calc_new := TRUE ;
1234 END;
1235 ELSIF x_document_type = 'AP' THEN
1236 BEGIN
1237 select adl.ind_compiled_set_id
1238 into l_compiled_set_id
1239 from gms_award_distributions adl
1240 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1241 -- ap_invoice_distributions_all apd
1242 where adl.award_id = x_award_id
1243 and adl.task_id = x_task_id
1244 and adl.invoice_id = x_document_header_id
1245 and adl.invoice_distribution_id = x_document_distribution_id -- AP Lines change
1246 and adl.document_type = 'AP'
1247 and adl.adl_status = 'A'
1248 and adl.fc_status = 'A'
1249 -- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
1250 /*and apd.invoice_id = x_document_header_id
1251 and apd.invoice_distribution_id = x_document_distribution_id -- AP Lines change
1252 and apd.award_id = adl.award_set_id */
1253 and adl.adl_line_num = 1 ;
1254 EXCEPTION
1255 when no_data_found then
1256 l_calc_new := TRUE ;
1257 END;
1258 ELSIF x_document_type = 'EXP' THEN
1259 BEGIN
1260 select ind_compiled_set_id
1261 into l_compiled_set_id
1262 from gms_award_distributions
1263 where award_id = x_award_id
1264 and task_id = x_task_id
1265 and expenditure_item_id = x_document_header_id
1266 and cdl_line_num = x_document_distribution_id
1267 and document_type = 'EXP'
1268 and adl_status = 'A'
1269 and fc_status = 'A';
1270 EXCEPTION
1271 when no_data_found then
1272 l_calc_new := TRUE ;
1273 END ;
1274 ELSE
1275 l_calc_new := TRUE ;
1276 END IF;
1277
1278 IF l_calc_new THEN
1279 l_compiled_set_id := get_award_cmt_compiled_set_id
1280 ( x_task_id,
1281 x_expenditure_item_date,
1282 p_expenditure_type, --Bug 3003584
1283 x_organization_id,
1284 x_schedule_type,
1285 x_award_id);
1286 END IF ;
1287
1288 return l_compiled_set_id;
1289 EXCEPTION
1290 when others then
1291 raise ;
1292 END award_cmt_compiled_set_id;
1293
1294 FUNCTION get_award_cmt_compiled_set_id
1295 ( x_task_id IN NUMBER,
1296 x_expenditure_item_date IN DATE,
1297 p_expenditure_type IN VARCHAR2, --Bug 3003584
1298 x_organization_id IN NUMBER,
1299 x_schedule_type IN VARCHAR2,
1300 x_award_id IN NUMBER)
1301 RETURN NUMBER IS
1302 l_stage number ;
1303 l_status number ;
1304 l_rate_sch_rev_id number;
1305 l_compiled_set_id number;
1306 l_cp_structure pa_cost_plus_structures.cost_plus_structure%TYPE; --Bug 3003584
1307 l_cost_base pa_cost_bases.cost_base%TYPE; --Bug 3003584
1308
1309 BEGIN
1310
1311 l_stage := 275;
1312 l_status := 0;
1313
1314 -- For award level commitments, use award schedule
1315
1316 if (x_award_id is not null ) then
1317 get_award_ind_rate_sch_rev_id(x_award_id,
1318 x_task_id, -- Bug 2097676, Multiple IDC Schedule Build
1319 x_expenditure_item_date,
1320 l_rate_sch_rev_id,
1321 l_status,
1322 l_stage);
1323
1324 if (l_rate_sch_rev_id is not null ) then
1325 --Begin Bug 3003584
1326 pa_cost_plus.get_cost_plus_structure (
1327 rate_sch_rev_id=> l_rate_sch_rev_id,
1328 cp_structure=> l_cp_structure,
1329 status=> l_status,
1330 stage => l_stage
1331 );
1332
1333 IF (l_status <> 0)
1334 THEN
1335 RETURN NULL;
1336 END IF;
1337
1338 pa_cost_plus.get_cost_base (
1339 exp_type=> p_expenditure_type,
1340 cp_structure=> l_cp_structure,
1341 c_base=> l_cost_base,
1342 status=> l_status,
1343 stage => l_stage);
1344
1345 IF (l_status <> 0)
1346 THEN
1347 RETURN NULL;
1348 END IF;
1349
1350 --End Bug 3003584
1351 pa_cost_plus.get_compiled_set_id(l_rate_sch_rev_id,
1352 x_organization_id,
1353 l_cost_base, --Bug 3003584
1354 l_compiled_set_id,
1355 l_status, l_stage);
1356 else
1357 l_compiled_set_id := null;
1358
1359 end if;
1360 end if;
1361 return l_compiled_set_id;
1362
1363 EXCEPTION
1364 when others then
1365 raise ;
1366 END get_award_cmt_compiled_set_id;
1367 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1368
1369 FUNCTION create_adjplus_log( P_doc_type IN varchar2,
1370 p_adj_amount IN NUMBER,
1371 p_award_id IN NUMBER,
1372 p_exp_type IN varchar2,
1373 p_packet_id IN NUMBER,
1374 p_bc_packet_id IN NUMBER,
1375 p_header_id IN NUMBER,
1376 p_dist_id IN NUMBER,
1377 p_only_one IN BOOLEAN,
1378 p_adjustment_id IN OUT NOCOPY NUMBER,
1379 p_line_num IN OUT NOCOPY NUMBER )
1380 return NUMBER is
1381
1382 x_adjusted_amount NUMBER ;
1383 x_dummy NUMBER ;
1384 x_dummy1 NUMBER ;
1385 x_adj_logamt NUMBER ;
1386 x_adjustment_id NUMBER ;
1387 x_line_num NUMBER ;
1388 x_run_total NUMBER ;
1389 x_dist_id NUMBER ;
1390 x_doc_dist_id NUMBER ;
1391 x_hdr_id NUMBER ;
1392 x_we_are_done BOOLEAN ;
1393 x_doc_type varchar2(3) ;
1394 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
1395 x_adj_allowed NUMBER ;
1396 l_calling_seq varchar2(30) ;
1397 C_doc_ADJ c_adj_rec ;
1398 adj_rec commitRecTyp ;
1399
1400 --
1401 -- 4004559 - PJ.M:B8:P13:OTH:PERF: FULL TABLE SCAN COST
1402 -- document type criteria was added to remove the FTS.
1403 --
1404 cursor c1 is
1405 select sum(burdenable_raw_cost)
1406 from gms_bc_packets
1407 where document_header_id = x_hdr_id
1408 and document_distribution_id = x_doc_dist_id
1409 and burden_adjustment_flag = 'Y'
1410 and nvl(burden_posted_flag,'N') <> 'X'
1411 and status_code in ('A','P','I')
1412 and document_type in ( 'PO','REQ', 'AP', 'ENC', 'EXP' ) ;
1413
1414 -- Burden_posted_flag will be update to 'X' when the source document is updated with the
1415 -- Burdenable_raw_cost amount.This check will ensure that the cursor c1 does not pick these transactions.
1416 -- Also, only burden adjustment trasnaction, i.e. trasnactions created during burden adjustments needs to be
1417 -- considerde here .. earlier this select was on the burden log table ..
1418
1419 BEGIN
1420
1421 g_error_procedure_name := 'create_adjplus_log';
1422 IF g_debug = 'Y' THEN
1423 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
1424 END IF;
1425
1426 x_adjusted_amount := 0;
1427 x_dummy := 0 ;
1428 x_dummy1 := 0 ;
1429 x_adj_logamt := 0 ;
1430 l_calling_seq := 'CREATE_ADJPLUS_LOG' ;
1431 -- ----------------------------------------------
1432 -- P_doc_type = Document need to be adjusted.
1433 -- p_adj_amount = Amount needs to be adjusted.
1434 -- The pattern of adjustment used is last in
1435 -- 1st to be adjusted. LIFO.
1436 -- ----------------------------------------------
1437 IF g_debug = 'Y' THEN
1438 gms_error_pkg.gms_debug (g_error_procedure_name||':p_doc_type:'||p_doc_type||';'||'x_dist_id:'||x_dist_id||';'||'p_adj_amount:'||p_adj_amount,'C');
1439 END IF;
1440
1441
1442 -- ----------------------------------------------------
1443 -- BUG: 1362283 - ENC burdenable raw cost not adjusted
1444 -- when AP resereved and need to be adjusted from ENC.
1445 -- ENC view always has dist_id = 1 and for AP also 1st
1446 -- dist_id = 1 so 'and doc.distribution_id <> x_dist_id'
1447 -- returns false and no data found for adjustment.
1448 -- this is fixed by adding x_dist_id := 0 ;for ENC.
1449 -- ------------------------------------------------------
1450 x_dist_id := p_dist_id ;
1451 IF p_doc_type = 'ENC' THEN
1452 x_dist_id := 0 ;
1453 ELSE
1454 x_dist_id := p_dist_id ;
1455 END IF ;
1456
1457 x_adjusted_amount := 0 ;
1458 x_dummy := 0 ;
1459 x_dummy1 := 0 ;
1460 x_run_total := 0 ;
1461 x_adjustment_id := p_adjustment_id ;
1462 x_line_num := nvl(p_line_num,0) ;
1463 x_doc_type := p_doc_type ;
1464 x_adj_allowed := 0 ;
1465
1466 -- ==============================================================================
1467 -- BUG : 2982977 - GMS: COSTING AND FUNDSCHECK ON USAGES RUNNING FOR OVER TWO DAYS
1468 -- We don't want to adjust any transactions from the REQ,PO and ENC if the total
1469 -- burdenable raw cost for that doc is ZERO.
1470 -- This will avoid the loop of adjustments across the doc and save the processing
1471 -- time.
1472 -- Code chages starts here.
1473 -- New variables added : x_adj_allowed , x_dummy1 , x_doc_dist_id
1474 -- FOLLOW thru the CHANGE REQUEST FOR THE CODE CHANGES ->Change request : 2982977
1475 -- ================================================================================
1476
1477
1478 -- Change request : 2982977
1479 select
1480 decode( p_doc_type, 'REQ', x_rec_award_exp_tot.req_burdenable_cost,
1481 'PO', x_rec_award_exp_tot.po_burdenable_cost,
1482 'ENC', x_rec_award_exp_tot.enc_burdenable_cost, 1 )
1483 into x_adj_allowed
1484 from dual ;
1485
1486 IF g_debug = 'Y' THEN
1487 gms_error_pkg.gms_debug (g_error_procedure_name||':x_adj_allowed:'||x_adj_allowed,'C');
1488 END IF;
1489
1490 -- Change request : 2982977
1491 IF NVL( x_adj_allowed, 0) <> 0 THEN
1492
1493 -- 3465191
1494 -- open cursor using the base table
1495 --
1496 l_calling_seq := 'CREATE_ADJPLUS_LOG' ;
1497 open_ref_cursor( c_doc_adj,
1498 x_doc_type,
1499 p_award_id,
1500 p_exp_type,
1501 x_dist_id,
1502 NULL, --p_header_id
1503 l_calling_seq) ;
1504 LOOP
1505
1506 fetch c_doc_adj into adj_rec ;
1507
1508 IF c_doc_adj%NOTFOUND THEN
1509 close c_doc_adj ;
1510 EXIT ;
1511 END IF ;
1512
1513 x_dummy := adj_rec.burden ;
1514 x_hdr_id := adj_rec.header_id ;
1515 -- Change request : 2982977
1516 x_doc_dist_id := adj_rec.dist_id ;
1517
1518 -- Change request : 2982977
1519 open c1 ;
1520 fetch c1 into x_adj_logamt ;
1521 close c1 ;
1522
1523 -- ====================================================
1524 -- subtract the unposted adjusted amount from the
1525 -- amount of the doc. the adjusted log amount is -ve
1526 -- thats why PLUS will be okay.
1527 -- ===================================================
1528 -- Change request : 2982977
1529 x_dummy := x_dummy + NVL(x_adj_logamt,0) ;
1530
1531 IF p_doc_type = 'REQ' THEN
1532 IF (x_rec_award_exp_tot.req_burdenable_cost - x_dummy ) < 0 THEN
1533 x_dummy := x_rec_award_exp_tot.req_burdenable_cost ;
1534 END IF ;
1535 ELSIF p_doc_type = 'PO' THEN
1536 IF (x_rec_award_exp_tot.PO_burdenable_cost - x_dummy ) < 0 THEN
1537 x_dummy := x_rec_award_exp_tot.PO_burdenable_cost ;
1538 END IF ;
1539 ELSIF p_doc_type = 'ENC' THEN
1540 IF (x_rec_award_exp_tot.ENC_burdenable_cost - x_dummy ) < 0 THEN
1541 x_dummy := x_rec_award_exp_tot.ENC_burdenable_cost ;
1542 END IF ;
1543 END IF ;
1544
1545 x_run_total := x_dummy + x_run_total ;
1546
1547 IF p_adj_amount <= x_run_total THEN
1548
1549 -- Never Adjusted as of yet.
1550 x_dummy := p_adj_amount - ( x_run_total - x_dummy) ;
1551 x_run_total := p_adj_amount ;
1552 x_we_are_done := TRUE ;
1553 END IF ;
1554
1555 IF x_adjustment_id IS NULL THEN
1556 select gms_adjustments_id_s.NEXTVAL
1557 INTO x_adjustment_id
1558 FROM dual ;
1559 END IF ;
1560
1561 x_line_num := x_line_num + 1 ;
1562 x_rec_log.adjustment_id := x_adjustment_id ;
1563 x_rec_log.line_num := x_line_num ;
1564 x_rec_log.document_header_id:= adj_rec.header_id ;
1565 x_rec_log.document_type := p_doc_type ;
1566 x_rec_log.amount := 0 ;
1567 x_rec_log.source_flag := 'Y' ;
1568 x_rec_log.award_set_id := adj_rec.award_set_id ;
1569 x_rec_log.adl_line_num := adj_rec.adl_line_num ;
1570 x_rec_log.award_id := p_award_id ;
1571 x_rec_log.expenditure_type := p_exp_type ;
1572 x_rec_log.packet_id := p_packet_id ;
1573 x_rec_log.bc_packet_id := p_bc_packet_id ;
1574 x_rec_log.document_distribution_id := adj_rec.dist_id ;
1575 x_rec_log.adj_burdenable_amount := x_dummy * -1 ;
1576
1577 -- ------------------------------------------------------
1578 -- Create a adjustment log for this ITEM.
1579 -- ------------------------------------------------------
1580
1581 create_burden_adjustments(x_rec_log,
1582 adj_rec.project_id,
1583 adj_rec.task_id,
1584 adj_rec.expenditure_item_date,
1585 adj_rec.expenditure_organization_id,
1586 adj_rec.resource_list_member_id,
1587 adj_rec.bud_task_id,
1588 adj_rec.ind_compiled_set_id) ;
1589
1590 -- Change request : 2982977
1591 IF p_doc_type = 'REQ' THEN
1592 x_dummy1 := x_rec_award_exp_tot.req_burdenable_cost ;
1593 ELSIF p_doc_type = 'PO' THEN
1594 x_dummy1 := x_rec_award_exp_tot.po_burdenable_cost ;
1595 ELSIF p_doc_type = 'ENC' THEN
1596 x_dummy1 := x_rec_award_exp_tot.enc_burdenable_cost ;
1597 END IF ;
1598
1599
1600 IF x_we_are_done THEN
1601 IF c_doc_adj%ISOPEN THEN
1602 CLOSE c_doc_adj ;
1603 END IF ;
1604 EXIT ;
1605 END IF ;
1606
1607 -- Change request : 2982977
1608 IF x_dummy1 = 0 THEN
1609 -- nothing more to adjust for this document
1610 -- so exit out of this loop.
1611 IF c_doc_adj%ISOPEN THEN
1612 CLOSE c_doc_adj ;
1613 END IF ;
1614
1615 EXIT ;
1616 END IF ;
1617 END LOOP ;
1618 END IF ;
1619 IF c_doc_adj%ISOPEN THEN
1620 CLOSE c_doc_adj ;
1621 END IF ;
1622
1623 x_dummy := 0 ;
1624
1625 p_adjustment_id := x_adjustment_id ;
1626 p_line_num := nvl(x_line_num,0) ;
1627 IF x_we_are_done OR p_doc_type = 'ENC' OR p_only_one THEN
1628 return x_run_total ;
1629 END IF ;
1630
1631 IF x_run_total < p_adj_amount THEN
1632
1633 x_dummy := p_adj_amount - x_run_total ;
1634
1635 IF x_doc_type = 'REQ' THEN
1636 x_doc_type := 'PO' ;
1637 ELSIF x_doc_type = 'PO' THEN
1638 x_doc_type := 'ENC' ;
1639 END IF ;
1640
1641 x_dummy := create_adjplus_log( x_doc_type ,
1642 x_dummy,
1643 p_award_id,
1644 p_exp_type,
1645 p_packet_id,
1646 p_bc_packet_id,
1647 p_header_id,
1648 p_dist_id,
1649 p_only_one,
1650 x_adjustment_id ,
1651 x_line_num ) ;
1652
1653 END IF ;
1654
1655 x_run_total := x_run_total + x_dummy ;
1656
1657 p_adjustment_id := x_adjustment_id ;
1658 p_line_num := nvl(x_line_num,0) ;
1659 return ( x_run_total );
1660
1661
1662 EXCEPTION
1663 WHEN others then
1664
1665 g_dummy := SQLERRM;
1666 IF g_debug = 'Y' THEN
1667 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
1668 END IF;
1669
1670 RAISE ;
1671 END create_adjplus_log ;
1672
1673 FUNCTION SELF_ADJUSTMENT( p_doc_type varchar2 ,
1674 p_record bc_packets%ROWTYPE,
1675 p_adj_amount IN NUMBER,
1676 p_adjustment_id IN OUT NOCOPY NUMBER,
1677 p_line_num IN OUT NOCOPY NUMBER) return NUMBER is
1678 x_adjustment_id NUMBER ;
1679 x_line_num NUMBER ;
1680 x_run_total NUMBER ;
1681 x_dummy NUMBER ;
1682 x_diff NUMBER ;
1683 X_done BOOLEAN ;
1684 X_doc_ADJ c_adj_rec ;
1685 C_REC commitRecTyp ;
1686 X_rec_log gms_burden_adjustments_log%ROWTYPE ;
1687 x_adj_balance NUMBER ;
1688 l_calling_seq varchar2(30) ;
1689
1690 BEGIN
1691
1692 g_error_procedure_name := 'self_adjustment';
1693 IF g_debug = 'Y' THEN
1694 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
1695 END IF;
1696
1697 x_adj_balance := 0 ;
1698 l_calling_seq := 'SELF_ADJUSTMENT' ;
1699 x_adj_balance := p_adj_amount ;
1700 x_adjustment_id := p_adjustment_id ;
1701 x_line_num := p_line_num ;
1702 l_calling_seq := 'SELF_ADJUSTMENT' ;
1703
1704 -- bug 3465191
1705 -- performance fix.
1706 open_ref_cursor ( X_doc_ADJ ,
1707 p_doc_type,
1708 p_record.award_id,
1709 p_record.expenditure_type,
1710 p_record.document_distribution_id,
1711 p_record.document_header_id,
1712 l_calling_seq ) ;
1713 LOOP
1714 fetch x_doc_adj into c_rec ;
1715 IF x_doc_adj%notfound THEN
1716 close x_doc_adj ;
1717 EXIT ;
1718 END IF ;
1719
1720 --FOR C_REC IN X_DOC_ADJ LOOP
1721 -- Amount already posted during summarization;
1722 x_dummy := c_rec.burden; -- + get_prev_unposted_adj( C_REC.header_id,
1723 -- C_REC.dist_id,
1724 -- p_doc_type ) ;
1725
1726 -- --------------------------------------------------------
1727 -- BUG:1337438 PO_Burdenable_cost is off
1728 -- What was happening is that previously computed PO with
1729 -- 0 burden was adjusted with -Burden which is wrong.
1730 --
1731 -- ** July 25, 2001
1732 -- BUG 1833325 - IDC LIMIT SCENARIO: TWO PROJECTS FUNDING SAME AWARD,
1733 -- CANCELLED REQUISITION
1734 -- Req adjustments didn't happen correctly after Req cancellation.
1735 -- =========================================================================
1736 --IF p_doc_type in ('PO', 'REQ') and nvl(x_dummy,0) = 0 THEN
1737 IF p_doc_type in ('PO','REQ') THEN
1738 -- -----------------------------------------------
1739 -- BUG FIXED :UNIT TEST
1740 -- Following was added because of following error.
1741 -- PO1 = 30/30 , IDC=100
1742 -- PO2 = 80/60 , IDC=100
1743 -- Cancel PO1 -> 0/0
1744 -- ADJ PO2 -> 80/100.
1745 -- After the following changes it worked okay.
1746 -- -----------------------------------------------
1747 IF nvl(x_dummy,0) = 0 THEN
1748 x_diff := 0 ;
1749 ELSE
1750 x_diff := abs(x_dummy) - C_REC.amount ;
1751 END IF ;
1752 ELSE
1753 x_diff := C_REC.amount - abs(x_dummy) ;
1754 END IF ;
1755 -- ------------------------------------------------
1756 -- This is done for AP with MEMO type adjustments.
1757 -- ------------------------------------------------
1758 -- BUG FIXED :UNIT TEST
1759 -- ------------------------------------------------
1760 IF x_adj_balance < 0 and p_doc_type = 'AP' THEN
1761 IF x_dummy >= 0 THEN --Change from > to >= for bug 2311261
1762 x_diff := x_dummy + x_adj_balance ;
1763 IF x_diff <= 0 THEN
1764 x_diff := x_dummy ;
1765 ELSE
1766 x_diff := x_adj_balance; -- Added for bug 2311261
1767 END IF;
1768 ELSE
1769 x_diff := 0 ; -- Added for bug 2311261
1770 END IF ;
1771 END IF ;
1772
1773 x_dummy := ABS(x_adj_balance) - abs(x_diff) ;
1774
1775 IF x_dummy <= 0 THEN
1776 -- ------------------------------------------------------------------------------
1777 -- BUG: 1337294 REQ_burdenable_costvalue is off incase of PO less than REQ amount
1778 --x_dummy := ABS(x_adj_balance) ; Changed...
1779 -- ------------------------------------------------------------------------------
1780 IF p_doc_type = 'AP' THEN
1781 x_dummy := ABS(x_adj_balance) ;
1782 ELSE
1783 x_dummy := x_adj_balance ;
1784 END IF ;
1785 x_adj_balance := 0 ;
1786 x_done := TRUE ;
1787 ELSE
1788 x_dummy := x_diff ;
1789 -- ------------------------------------------------
1790 -- This is done for AP with MEMO type adjustments.
1791 -- ------------------------------------------------
1792 IF x_adj_balance < 0 THEN
1793 x_adj_balance := (ABS(x_adj_balance) - ABS(x_diff)) * -1 ;
1794 ELSE
1795 x_adj_balance := x_adj_balance - x_diff ;
1796 END IF ;
1797 END IF ;
1798
1799 IF x_dummy <> 0 THEN
1800
1801 IF x_adjustment_id IS NULL THEN
1802 select gms_adjustments_id_s.NEXTVAL
1803 INTO x_adjustment_id
1804 FROM dual ;
1805 END IF ;
1806
1807 x_line_num := NVL(x_line_num,0) + 1 ; -- BRC.1
1808 x_rec_log.adjustment_id := x_adjustment_id ;
1809 x_rec_log.line_num := x_line_num ;
1810 x_rec_log.document_header_id:= c_rec.header_id ;
1811 x_rec_log.document_type := p_doc_type ;
1812 x_rec_log.amount := 0 ;
1813 x_rec_log.source_flag := 'Y' ;
1814 x_rec_log.award_set_id := c_rec.award_set_id ;
1815 x_rec_log.adl_line_num := c_rec.adl_line_num ;
1816 x_rec_log.award_id := c_rec.award_id ;
1817 x_rec_log.expenditure_type := c_rec.expenditure_type ;
1818 x_rec_log.packet_id := p_record.packet_id ;
1819 x_rec_log.bc_packet_id := p_record.bc_packet_id ;
1820
1821 x_rec_log.document_distribution_id := c_rec.dist_id ;
1822 x_rec_log.adj_burdenable_amount := x_dummy * -1 ;
1823
1824 -- ------------------------------------------------------
1825 -- Create a adjustment log for this ITEM.
1826 -- ------------------------------------------------------
1827 create_burden_adjustments(x_rec_log,
1828 c_rec.project_id,
1829 c_rec.task_id,
1830 c_rec.expenditure_item_date,
1831 c_rec.expenditure_organization_id,
1832 c_rec.resource_list_member_id,
1833 c_rec.bud_task_id,
1834 c_rec.ind_compiled_set_id) ;
1835
1836 END IF ;
1837 IF x_done THEN
1838 EXIT ;
1839 END IF ;
1840 --<<NEXTREC>>
1841 NULL ;
1842 END LOOP ;
1843
1844 IF x_doc_adj%ISOPEN THEN
1845 CLOSE x_doc_adj ;
1846 END IF ;
1847
1848 p_adjustment_id := x_adjustment_id ;
1849 p_line_num := nvl(x_line_num,0) ;
1850
1851 return x_adj_balance ;
1852
1853 EXCEPTION
1854 WHEN others THEN
1855
1856 g_dummy := SQLERRM;
1857 IF g_debug = 'Y' THEN
1858 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
1859 END IF;
1860
1861 RAISE ;
1862 END SELF_ADJUSTMENT ;
1863
1864 FUNCTION DERIVE_ADJ_AMOUNT( p_old_burden IN NUMBER,
1865 p_new_burden IN OUT NOCOPY NUMBER ) return NUMBER IS
1866 x_adj_amount NUMBER ;
1867 x_diff NUMBER ;
1868 BEGIN
1869 x_adj_amount := 0 ;
1870 x_diff := 0 ;
1871
1872 IF p_old_burden < 0 and p_new_burden > 0 THEN
1873
1874 IF abs(p_old_burden) <> p_new_burden THEN
1875 x_adj_amount := p_new_burden - abs(p_old_burden) ;
1876 p_new_burden := abs(p_old_burden) ;
1877 ELSIF abs(p_old_burden) = p_new_burden THEN
1878 x_adj_amount := 0 ;
1879 END IF ;
1880
1881 ELSIF (p_old_burden < 0 and p_new_burden < 0 ) OR
1882 (p_old_burden > 0 and p_new_burden > 0 ) THEN
1883
1884 x_adj_amount := 0 ;
1885
1886 ELSIF p_old_burden > 0 and p_new_burden < 0 THEN
1887
1888 IF abs(p_new_burden) > p_old_burden THEN
1889 x_adj_amount := p_old_burden - abs(p_new_burden) ;
1890 p_new_burden := p_old_burden * -1 ;
1891 ELSIF abs(p_new_burden) = p_old_burden THEN
1892 x_adj_amount := 0 ;
1893 ELSIF abs(p_new_burden) < p_old_burden THEN
1894 x_adj_amount := abs(p_new_burden) - p_old_burden ;
1895 p_new_burden := 0 - p_old_burden ;
1896 END IF ;
1897
1898 ELSIF p_old_burden < 0 and p_new_burden = 0 THEN
1899
1900 p_new_burden := abs(p_old_burden) ;
1901 x_adj_amount := p_old_burden ;
1902
1903 ELSIF p_old_burden = 0 and p_new_burden = 0 THEN
1904
1905 x_adj_amount := 0 ;
1906 p_new_burden := p_old_burden * -1 ;
1907
1908 ELSIF p_old_burden > 0 and p_new_burden = 0 THEN
1909
1910 x_adj_amount := abs(p_new_burden) - p_old_burden ;
1911 p_new_burden := p_old_burden * -1 ;
1912
1913 END IF ;
1914
1915 return x_adj_amount ;
1916
1917 EXCEPTION
1918 WHEN others then
1919
1920 g_dummy := SQLERRM;
1921 IF g_debug = 'Y' THEN
1922 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
1923 END IF;
1924
1925 RAISE ;
1926 END DERIVE_ADJ_AMOUNT ;
1927
1928 PROCEDURE UPDATE_BC_PACKET( p_doc_type varchar2,
1929 p_burden NUMBER,
1930 p_amount NUMBER,
1931 p_record bc_packets%ROWTYPE )
1932 is
1933 PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1934 begin
1935 IF p_doc_type = 'REQ' THEN
1936 x_rec_award_exp_tot.req_raw_cost := x_rec_award_exp_tot.req_raw_cost +
1937 p_amount ;
1938 x_rec_award_exp_tot.req_burdenable_cost := x_rec_award_exp_tot.req_burdenable_cost +
1939 p_burden ;
1940 ELSIF p_doc_type = 'PO' THEN
1941 x_rec_award_exp_tot.po_raw_cost := x_rec_award_exp_tot.po_raw_cost +
1942 p_amount ;
1943 x_rec_award_exp_tot.po_burdenable_cost := x_rec_award_exp_tot.po_burdenable_cost +
1944 p_burden ;
1945 ELSIF p_doc_type = 'AP' THEN
1946 x_rec_award_exp_tot.ap_raw_cost := x_rec_award_exp_tot.ap_raw_cost +
1947 p_amount ;
1948 x_rec_award_exp_tot.ap_burdenable_cost := x_rec_award_exp_tot.ap_burdenable_cost +
1949 p_burden ;
1950 ELSIF p_doc_type = 'ENC' THEN
1951 x_rec_award_exp_tot.enc_raw_cost := x_rec_award_exp_tot.enc_raw_cost +
1952 p_amount ;
1953 x_rec_award_exp_tot.enc_burdenable_cost := x_rec_award_exp_tot.enc_burdenable_cost +
1954 p_burden ;
1955 ELSIF p_doc_type = 'EXP' THEN
1956 x_rec_award_exp_tot.exp_raw_cost := x_rec_award_exp_tot.exp_raw_cost +
1957 p_amount ;
1958 x_rec_award_exp_tot.exp_burdenable_cost := x_rec_award_exp_tot.exp_burdenable_cost +
1959 p_burden ;
1960 END IF ;
1961
1962 x_tot_raw := x_tot_raw + p_amount ;
1963 x_tot_burden:= x_tot_burden + p_burden ;
1964
1965 select gms_adjustments_id_s.NEXTVAL into l_calc_sequence from dual;
1966
1967 UPDATE gms_bc_packets
1968 set burdenable_raw_cost = p_burden,
1969 burden_calculation_seq = l_calc_sequence
1970 where packet_id = p_record.packet_id
1971 and bc_packet_id = p_record.bc_packet_id ;
1972
1973 COMMIT; --R12 Funds Management Uptake : Made this an autonomous procedure.
1974
1975 EXCEPTION
1976 when others then
1977
1978 g_dummy := SQLERRM;
1979 IF g_debug = 'Y' THEN
1980 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
1981 END IF;
1982
1983 raise ;
1984 END UPDATE_BC_PACKET ;
1985
1986 FUNCTION COMMON_LOGIC ( p_pkt_amount NUMBER,
1987 p_raw_cost NUMBER,
1988 P_burden NUMBER,
1989 p_adj_amount IN OUT NOCOPY NUMBER ) return NUMBER is
1990 x_burden NUMBER ;
1991 x_dummy NUMBER ;
1992 BEGIN
1993
1994 g_error_procedure_name := 'common_logic';
1995 IF g_debug = 'Y' THEN
1996 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
1997 END IF;
1998
1999 x_burden := 0 ;
2000 x_dummy := 0 ;
2001 p_adj_amount := 0 ;
2002
2003 IF g_debug = 'Y' THEN
2004 gms_error_pkg.gms_debug (g_error_procedure_name||':p_pkt_amount:'||p_pkt_amount||';'||'p_raw_cost:'||p_raw_cost,'C');
2005 END IF;
2006
2007 IF p_pkt_amount > 0 THEN
2008
2009 x_dummy := p_pkt_amount ;
2010
2011 -- ----------------------------------------------------------------
2012 -- INFORMATION :
2013 -- The following IF codition - PURPOSE
2014 -- Let say AP has credit memo of -50,000 for the same award
2015 -- and expenditures, so AP_RAW_COST = -50,000 and AP_BURDEN = 0
2016 -- now new standard AP gets in lets say +1000, The total is still
2017 -- -49,000. Since AP can becomes actuals and we don't want to get
2018 -- in a situation where Raw cost is -Ve and Burden is +Ve.
2019 -- Thats why we calculates Zero Burden for this scenario.
2020 -- This is true for any type of transaction entry. i.e. AP, PO, REQ
2021 -- ENC and EXP.
2022 -- -----------------------------------------------------------------
2023 IF p_raw_cost < 0 THEN
2024 x_dummy := p_raw_cost + p_pkt_amount ;
2025 IF x_dummy <= 0 THEN
2026 x_burden := 0 ;
2027 x_dummy := 0 ;
2028 END IF ;
2029 END IF ;
2030
2031 if (x_tot_burden + x_dummy) <= x_award_exp_limit THEN
2032 x_burden := x_dummy ;
2033 elsif (x_tot_burden + x_dummy) > x_award_exp_limit THEN
2034 x_burden := x_award_exp_limit - x_tot_burden ;
2035 p_adj_amount:= x_dummy - x_burden ;
2036 END IF ; -- Limit Check
2037
2038 -- ---------------------------------------------------------------
2039 -- Following code is replaced by the previous one.
2040 -- ---------------------------------------------------------------
2041 -- if (x_tot_burden + p_pkt_amount) <= x_award_exp_limit THEN
2042 -- x_burden := p_pkt_amount ;
2043 -- elsif (x_tot_burden + p_pkt_amount) > x_award_exp_limit THEN
2044 -- x_burden := x_award_exp_limit - x_tot_burden ;
2045 -- p_adj_amount:= p_pkt_amount - x_burden ;
2046 -- END IF ; -- Limit Check
2047 -- -----------------------------------------------------------------
2048
2049 ELSIF p_pkt_amount = 0 THEN
2050 x_burden := 0 ;
2051
2052 ELSIF p_pkt_amount < 0 THEN
2053 x_dummy := p_raw_cost + p_pkt_amount ;
2054
2055 IF x_dummy >= P_burden THEN
2056 x_burden := 0 ;
2057 ELSIF x_dummy <= 0 THEN
2058 x_burden := 0 - P_burden;
2059 ELSIF x_dummy < P_burden THEN
2060 x_burden := x_dummy - P_burden ;
2061 END IF ;
2062 END IF ; -- p_pkt_amount > 0
2063 return x_burden ;
2064 EXCEPTION
2065 when others then
2066
2067 g_dummy := SQLERRM;
2068 IF g_debug = 'Y' THEN
2069 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
2070 END IF;
2071
2072 raise ;
2073 END COMMON_LOGIC ;
2074
2075 FUNCTION CALC_REQ_BURDEN(p_record bc_packets%ROWTYPE,p_mode IN VARCHAR2 ) return boolean -- Bug : 2557041 - Added p_mode parameter
2076 IS
2077 pkt_amount number;
2078 burden_raw_cost number;
2079 l_award_set_id NUMBER ;
2080 l_adl_line_num NUMBER ;
2081 x_adjustment_id NUMBER ;
2082 x_line_num NUMBER ;
2083 req_adj_amount NUMBER ;
2084 bc_pkt_rec bc_packets%rowtype;
2085 req_burden NUMBER ;
2086 x_dummy NUMBER ;
2087 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
2088
2089 CURSOR C_req is
2090 SELECT adl.burdenable_raw_cost burden_amount,
2091 adl.adl_line_num,
2092 adl.award_set_id
2093 FROM gms_award_distributions adl ,
2094 po_req_distributions_all req
2095 WHERE adl.distribution_id = p_record.document_distribution_id
2096 AND req.distribution_id = p_record.document_distribution_id
2097 AND req.award_id = adl.award_set_id
2098 and adl.adl_status = 'A'
2099 and adl.distribution_id = req.distribution_id ;
2100 BEGIN
2101
2102 g_error_procedure_name := 'calc_req_burden';
2103 IF g_debug = 'Y' THEN
2104 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
2105 END IF;
2106
2107 x_line_num := 0;
2108 req_adj_amount := 0 ;
2109 bc_pkt_rec := p_record ;
2110 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
2111 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
2112
2113 if nvl(bc_pkt_rec.entered_cr,0) <> 0 then
2114 pkt_amount := 0 - bc_pkt_rec.entered_cr;
2115 elsif nvl(bc_pkt_rec.entered_dr,0) <> 0 then
2116 pkt_amount := bc_pkt_rec.entered_dr;
2117 end if;
2118
2119 -- -----------------------------------------
2120 -- We don't need and adjustments log so we
2121 -- calculate it direct.
2122 -- ------------------------------------------
2123 -- ============================================================
2124 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
2125 -- : BURDENING.
2126 -- ============================================================
2127 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
2128 IF x_calc_zero_limit THEN
2129 burden_raw_cost := 0 ;
2130 update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2131 return TRUE ;
2132 END IF ;
2133
2134 IF nvl(x_award_exp_limit,0) <= 0 THEN
2135 burden_raw_cost := pkt_amount ;
2136 update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2137 return TRUE ;
2138 END IF ;
2139 */
2140
2141 -- --------------------------------------
2142 -- We have a IDC limit.
2143 -- X_dummy : is adjustment amount, Req
2144 -- doesn't have any ADJ amount. So
2145 -- ignored.
2146 -- --------------------------------------
2147
2148 burden_raw_cost := common_logic(pkt_amount,
2149 x_rec_award_exp_tot.req_raw_cost,
2150 x_rec_award_exp_tot.req_burdenable_cost,
2151 x_dummy) ;
2152
2153
2154
2155 /* =================================================================
2156 -- Bug : 2557041 - Added for IP check funds Enhancement
2157
2158 As burden adjustment will not be carried out NOCOPY in check funds mode,
2159 reutrn after updating the burdenable_raw_cost on gms_bc_packets
2160 ================================================================== */
2161
2162 IF p_mode = 'C' THEN
2163 update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2164 RETURN TRUE ;
2165 END IF;
2166
2167
2168
2169 -- --------------------------------------------
2170 -- Get previous calculated burden on the same
2171 -- line.
2172 -- --------------------------------------------
2173 open c_req ;
2174 fetch c_req into req_burden,
2175 l_award_set_id,
2176 l_adl_line_num ;
2177 close c_req ;
2178
2179 -- =========================================================================
2180 -- BUG 1833325 - IDC LIMIT SCENARIO: TWO PROJECTS FUNDING SAME AWARD,
2181 -- CANCELLED REQUISITION
2182 -- Related PO Bug :
2183 -- Req adjustments didn't happen correctly after Req cancellation.
2184 -- =========================================================================
2185
2186 --IF req_burden is not NULL THEN
2187 -- req_adj_amount := derive_adj_amount( nvl(req_burden,0), burden_raw_cost ) ;
2188 --END IF ;
2189
2190 IF nvl(req_burden,0) > 0 and pkt_amount < 0 THEN
2191 burden_raw_cost := least( abs(pkt_amount), req_burden) * -1 ;
2192 END IF ;
2193
2194 IF burden_raw_cost < 0 THEN
2195 req_adj_amount := burden_raw_cost ;
2196 END IF ;
2197
2198 IF req_adj_amount <> 0 THEN
2199 x_dummy := 0 ;
2200 x_dummy := self_adjustment( 'REQ',
2201 p_record,
2202 req_adj_amount,
2203 x_adjustment_id,
2204 x_line_num) ;
2205
2206
2207
2208 req_adj_amount := req_adj_amount - x_dummy ;
2209 END IF ;
2210
2211 -- -------------------------------------------------------------------
2212 -- Update the Running Total.
2213 -- -------------------------------------------------------------------
2214
2215
2216 update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2217
2218 return TRUE ;
2219
2220 EXCEPTION
2221 when no_data_found then
2222 return TRUE ;
2223 when others then
2224
2225 g_dummy := SQLERRM;
2226 IF g_debug = 'Y' THEN
2227 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
2228 END IF;
2229
2230 Raise ;
2231 END CALC_REQ_BURDEN;
2232
2233 FUNCTION CALC_PO_BURDEN(p_record bc_packets%ROWTYPE,p_mode IN VARCHAR2 ) return boolean -- Bug : 2557041 - Added p_mode parameter
2234 IS
2235
2236 X_po_dist_id NUMBER ;
2237 x_line_num NUMBER ;
2238 pkt_amount number;
2239 burden_raw_cost number;
2240 x_award_set_id NUMBER ;
2241 x_adl_line_num NUMBER ;
2242 x_po_burden NUMBER ;
2243 x_dummy NUMBER ;
2244 po_adj_amount NUMBER ;
2245 x_adjustment_id NUMBER ;
2246
2247 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
2248 bc_pkt_rec bc_packets%rowtype;
2249
2250 cursor po_dist_records is
2251 select nvl(adl.burdenable_raw_cost,0) ,
2252 adl.award_set_id award_set_id,
2253 adl_line_num adl_line_num
2254 FROM po_distributions_all pod,
2255 gms_award_distributions adl
2256 where adl.award_set_id = pod.award_id
2257 AND adl.po_distribution_id = pod.po_distribution_id
2258 and adl.adl_status = 'A'
2259 and pod.po_distribution_id = X_po_dist_id ;
2260
2261 BEGIN
2262 g_error_procedure_name := 'calc_po_burden';
2263 IF g_debug = 'Y' THEN
2264 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
2265 END IF;
2266
2267 X_po_dist_id := 0 ;
2268 x_line_num := 0 ;
2269 bc_pkt_rec := p_record ;
2270 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
2271 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
2272
2273 x_po_dist_id := bc_pkt_rec.document_distribution_id ;
2274
2275 if bc_pkt_rec.entered_cr <> 0 then
2276 pkt_amount := 0 - bc_pkt_rec.entered_cr;
2277 elsif bc_pkt_rec.entered_dr <> 0 then
2278 pkt_amount := bc_pkt_rec.entered_dr;
2279 end if;
2280
2281
2282 -- ============================================================
2283 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
2284 -- : BURDENING.
2285 -- ============================================================
2286 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
2287 IF x_calc_zero_limit THEN
2288 burden_raw_cost := 0 ;
2289 update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
2290 return TRUE ;
2291 END IF ;
2292
2293 IF nvl(x_award_exp_limit,0) <= 0 THEN
2294 -- --------------------------
2295 -- IDC Limit is not enabled.
2296 -- -------------------------
2297 burden_raw_cost := pkt_amount ;
2298 update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
2299 return TRUE ;
2300 END IF ;
2301 */
2302
2303 burden_raw_cost := common_logic( pkt_amount,
2304 x_rec_award_exp_tot.po_raw_cost,
2305 x_rec_award_exp_tot.po_burdenable_cost,
2306 x_dummy) ;
2307
2308 /* =================================================================
2309 -- Bug : 2557041 - Added for IP check funds Enhancement
2310
2311 As burden adjustment will not be carried out NOCOPY in check funds mode,
2312 reutrn after updating the burdenable_raw_cost on gms_bc_packets
2313 ================================================================== */
2314
2315 IF p_mode = 'C' THEN
2316 update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
2317 RETURN TRUE ;
2318 END IF;
2319
2320
2321 x_po_dist_id := nvl(bc_pkt_rec.document_distribution_id,0) ;
2322
2323 OPEN po_dist_records ;
2324 FETCH po_dist_records into x_po_burden, x_award_set_id, x_adl_line_num ;
2325 CLOSE po_dist_records ;
2326 po_adj_amount := 0 ;
2327
2328 --IF x_po_burden is not NULL THEN
2329 -- po_adj_amount := derive_adj_amount( nvl(x_po_burden,0), burden_raw_cost ) ;
2330 --END IF ;
2331
2332 -- ----------------------------------------
2333 -- BUG: 1337438 - PO burdenable_cost is OFF
2334 -- ----------------------------------------
2335 IF x_po_burden > 0 and pkt_amount < 0 THEN
2336 burden_raw_cost := least( abs(pkt_amount), x_po_burden) * -1 ;
2337 END IF ;
2338
2339 IF burden_raw_cost < 0 THEN
2340 po_adj_amount := burden_raw_cost ;
2341 END IF ;
2342
2343 IF po_adj_amount <> 0 THEN
2344 x_dummy := self_adjustment( 'PO',
2345 p_record,
2346 PO_adj_amount,
2347 x_adjustment_id,
2348 x_line_num) ;
2349
2350 --burden_raw_cost := burden_raw_cost + x_dummy ;
2351 -- adjusted amount
2352 PO_adj_amount := PO_adj_amount - x_dummy ;
2353 IF po_adj_amount <> 0 THEN
2354 -- ------------------
2355 -- PO_ADJ_AMOUNT = 0
2356 -- -------------------
2357 PO_adj_amount := 0 ;
2358 END IF ;
2359 END IF ;
2360
2361 -- -------------------------------------------------------------------
2362 -- Update the Running Total.
2363 -- -------------------------------------------------------------------
2364 update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
2365
2366 return TRUE ;
2367 EXCEPTION
2368 when others then
2369
2370 g_dummy := SQLERRM;
2371 IF g_debug = 'Y' THEN
2372 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
2373 END IF;
2374
2375 Raise ;
2376 END CALC_PO_BURDEN ;
2377
2378 FUNCTION CALC_ENC_BURDEN(p_record bc_packets_enc%ROWTYPE) return boolean
2379 IS
2380 pkt_amount number;
2381 x_award_set_id NUMBER ;
2382 x_adl_line_num NUMBER ;
2383 x_adjustment_id NUMBER ;
2384 x_line_num NUMBER ;
2385 x_dummy NUMBER ;
2386 x_enc_burden NUMBER ;
2387 enc_adj_amount NUMBER ;
2388 burden_raw_cost number;
2389
2390 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
2391 bc_pkt_rec bc_packets%rowtype;
2392
2393 cursor ENC_record is
2394 select nvl(adl.burdenable_raw_cost,0) ,
2395 adl.award_set_id award_set_id,
2396 adl_line_num adl_line_num
2397 FROM gms_encumbrance_items_all enc,
2398 gms_award_distributions adl
2399 where adl.expenditure_item_id = ENC.encumbrance_item_id
2400 and adl.adl_status = 'A'
2401 and ENC.encumbrance_item_id = bc_pkt_rec.document_header_id
2402 and enc.enc_distributed_flag = 'Y' ;
2403
2404 BEGIN
2405 g_error_procedure_name := 'calc_enc_burden';
2406 IF g_debug = 'Y' THEN
2407 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
2408 END IF;
2409
2410 x_line_num := 0;
2411 bc_pkt_rec := p_record ;
2412 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
2413 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
2414
2415 if bc_pkt_rec.entered_cr <> 0 then
2416 pkt_amount := 0 - bc_pkt_rec.entered_cr;
2417 elsif bc_pkt_rec.entered_dr <> 0 then
2418 pkt_amount := bc_pkt_rec.entered_dr;
2419 end if;
2420
2421 --OPEN enc_record ;
2422 --FETCH enc_record into x_aenc_burden, x_award_set_id, x_adl_line_num ;
2423 --CLOSE enc_record ;
2424
2425 -- ============================================================
2426 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
2427 -- : BURDENING.
2428 -- ============================================================
2429 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
2430 IF x_calc_zero_limit THEN
2431 burden_raw_cost := 0 ;
2432 update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
2433 return TRUE ;
2434 END IF ;
2435 */
2436 enc_adj_amount := 0 ;
2437
2438 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
2439
2440 IF nvl(x_award_exp_limit,0) <= 0 THEN
2441 -- --------------------------
2442 -- IDC Limit is not enabled.
2443 -- -------------------------
2444 burden_raw_cost := pkt_amount ;
2445 update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
2446 return TRUE ;
2447 END IF ; -- nvl(x_award_exp_limit,0)
2448
2449 */
2450 burden_raw_cost := common_logic( pkt_amount,
2451 x_rec_award_exp_tot.ENC_raw_cost,
2452 x_rec_award_exp_tot.ENC_burdenable_cost,
2453 x_dummy) ;
2454
2455 -- -------------------------------------------------------------------
2456 -- Update the Running Total.
2457 -- -------------------------------------------------------------------
2458 update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
2459
2460 return TRUE ;
2461 EXCEPTION
2462 WHEN others THEN
2463
2464 g_dummy := SQLERRM;
2465 IF g_debug = 'Y' THEN
2466 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
2467 END IF;
2468
2469 RAISE ;
2470 END CALC_ENC_BURDEN ;
2471
2472 PROCEDURE NET_ZERO_AP(P_record bc_packets%ROWTYPE)
2473 IS
2474
2475 x_run_total NUMBER ;
2476 x_adj_amount NUMBER ;
2477 x_dummy NUMBER ;
2478 x_line_num NUMBER ;
2479 x_adjustment_id NUMBER ;
2480 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
2481
2482 cursor AP_ZERO_INVOICE is
2483 select nvl(apd.base_amount,apd.amount) amount , --Bug 2472802
2484 nvl(adl.burdenable_raw_cost,0) burden,
2485 apd.invoice_id header_id,
2486 apd.invoice_distribution_id DIST_ID, -- AP Lines change
2487 adl.award_set_id award_set_id ,
2488 adl.adl_line_num adl_line_num,
2489 apd.project_id,
2490 apd.task_id,
2491 apd.expenditure_item_date,
2492 apd.expenditure_organization_id,
2493 adl.resource_list_member_id,
2494 adl.bud_task_id,
2495 adl.ind_compiled_set_id
2496 FROM ap_invoice_distributions APD,
2497 gms_award_distributions ADL,
2498 gl_ledgers G
2499 where adl.invoice_distribution_id = APD.invoice_distribution_id
2500 and adl.adl_status = 'A'
2501 and adl.award_id = P_record.award_id
2502 and nvl(adl.burdenable_raw_cost,0) <> 0
2503 and apd.expenditure_type = P_record.expenditure_type
2504 and ADL.award_set_id = APD.award_id
2505 AND G.LEDGER_ID = APD.SET_OF_BOOKS_ID
2506 and pa_cmt_utils.get_apdist_amt( apd.invoice_distribution_id,
2507 apd.invoice_id,
2508 nvl(apd.base_amount,apd.amount),
2509 'N',
2510 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
2511 and apd.line_type_lookup_code <> 'PREPAY'
2512 and decode(apd.pa_addition_flag,'G', 'Y','Z','Y', 'T','Y', 'E','Y', NULL, 'N', apd.pa_addition_flag ) <> 'Y'
2513 -- Bug 2097676: Fixing GSCC Error File.sql.9
2514 and apd.invoice_id = P_record.document_header_id ;
2515
2516 BEGIN
2517 g_error_procedure_name := 'net_zero_ap';
2518 IF g_debug = 'Y' THEN
2519 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
2520 END IF;
2521
2522 x_run_total := 0 ;
2523 x_adj_amount := 0 ;
2524 x_dummy := 0 ;
2525 x_line_num := 0 ;
2526
2527 for c_rec in AP_ZERO_INVOICE LOOP
2528 x_dummy := NVL(c_rec.burden,0) ;
2529
2530 -- Amount already posted during summarization;
2531 --x_dummy := x_dummy + get_prev_unposted_adj( c_rec.header_id,
2532 -- c_rec.DIST_ID,
2533 -- 'AP' ) ;
2534
2535 IF x_dummy <> 0 THEN
2536
2537 x_run_total := x_run_total + x_dummy ;
2538
2539 IF x_adjustment_id IS NULL THEN
2540 select gms_adjustments_id_s.NEXTVAL
2541 INTO x_adjustment_id
2542 FROM dual ;
2543 END IF ;
2544
2545 x_line_num := x_line_num + 1 ;
2546 x_rec_log.adjustment_id := x_adjustment_id ;
2547 x_rec_log.line_num := x_line_num ;
2548 x_rec_log.document_header_id:= c_rec.header_id ;
2549 x_rec_log.document_type := 'AP' ;
2550 x_rec_log.amount := 0 ;
2551 x_rec_log.source_flag := 'Y' ;
2552 x_rec_log.award_set_id := c_rec.award_set_id ;
2553 x_rec_log.adl_line_num := c_rec.adl_line_num ;
2554 x_rec_log.award_id := p_record.award_id ;
2555 x_rec_log.expenditure_type := p_record.expenditure_type ;
2556 x_rec_log.packet_id := p_record.packet_id ;
2557 x_rec_log.bc_packet_id := p_record.bc_packet_id ;
2558
2559 x_rec_log.document_distribution_id := c_rec.dist_id ;
2560 x_rec_log.adj_burdenable_amount := x_dummy * -1 ;
2561
2562 -- ------------------------------------------------------
2563 -- Create a adjustment log for this ITEM.
2564 -- ------------------------------------------------------
2565 create_burden_adjustments(x_rec_log,
2566 c_rec.project_id,
2567 c_rec.task_id,
2568 c_rec.expenditure_item_date,
2569 c_rec.expenditure_organization_id,
2570 c_rec.resource_list_member_id,
2571 c_rec.bud_task_id,
2572 c_rec.ind_compiled_set_id) ;
2573
2574 END IF ;
2575 END LOOP ;
2576 EXCEPTION
2577 When others then
2578
2579 g_dummy := SQLERRM;
2580 IF g_debug = 'Y' THEN
2581 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
2582 END IF;
2583
2584 RAISE ;
2585 END NET_ZERO_AP ;
2586
2587 FUNCTION CALC_AP_BURDEN(p_record bc_packets%ROWTYPE,p_mode IN VARCHAR2 ) return boolean -- Bug : 2557041 - Added p_mode parameter
2588 IS
2589 pkt_amount number;
2590 x_award_set_id NUMBER ;
2591 x_adl_line_num NUMBER ;
2592 x_adjustment_id NUMBER ;
2593 x_line_num NUMBER ;
2594 x_dummy NUMBER ;
2595 burden_raw_cost number;
2596 AP_adj_amount NUMBER ;
2597 x_adj_amount NUMBER ;
2598 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
2599 bc_pkt_rec bc_packets%rowtype;
2600 AP_adjustment BOOLEAN ;
2601 x_AP_TYPE VARCHAR2(20) ;
2602 x_credit BOOLEAN ;
2603
2604 CURSOR C_MEMO is
2605 select invoice_id
2606 from ap_invoices inv
2607 where inv.invoice_type_lookup_code IN ('CREDIT','DEBIT')
2608 and inv.invoice_id = bc_pkt_rec.document_header_id ;
2609 --
2610 -- Bug 4737148
2611 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2612 -- Source prepayment ivoice distributions attributes.
2613 --
2614 cursor c_prepay is
2615 select apd1.line_type_lookup_code,
2616 apd1.prepay_distribution_id,
2617 nvl(adl.burdenable_raw_cost ,0),
2618 apd2.invoice_id,
2619 adl.award_set_id,
2620 adl.award_id,
2621 apd2.expenditure_type,
2622 apd2.project_id,
2623 apd2.task_id,
2624 apd2.expenditure_item_date,
2625 apd2.expenditure_organization_id,
2626 adl.resource_list_member_id,
2627 adl.bud_task_id,
2628 adl.ind_compiled_set_id
2629 from ap_invoice_distributions_all apd1,
2630 ap_invoice_distributions_all apd2,
2631 gms_award_distributions adl
2632 where apd1.invoice_distribution_id = bc_pkt_rec.document_distribution_id
2633 and apd2.invoice_distribution_id = apd1.prepay_distribution_id
2634 and apd2.award_id = adl.award_set_id
2635 and adl.invoice_distribution_id = apd2.invoice_distribution_id
2636 and adl.document_type = 'AP'
2637 and adl.fc_status = 'A'
2638 and adl.invoice_id = apd2.invoice_id ;
2639
2640
2641 l_sum_amount NUMBER ;
2642 l_sum_burden NUMBER ;
2643 l_sum_count NUMBER ;
2644 l_header_id NUMBER ;
2645 l_memo NUMBER ;
2646
2647 l_prepay_type ap_invoice_distributions_all.line_type_lookup_code%TYPE ;
2648 l_prepay_dist_id NUMBER ;
2649 l_prepayment_brc number ;
2650 l_prepay_header_id NUMBER ;
2651 l_prepay_award_set_id NUMBER ;
2652 l_prepay_award_id NUMBER ;
2653 l_prepay_exp_type ap_invoice_distributions.expenditure_type%TYPE ;
2654 l_prepay_project_id number ;
2655 l_prepay_task_id number ;
2656 l_prepay_ei_date date ;
2657 l_prepay_exp_org_id number ;
2658 l_prepay_rlmi_id number ;
2659 l_prepay_bud_task_id number ;
2660 l_prepay_ind_set_id number ;
2661
2662
2663 CURSOR C_GET_AWARD_SET_ID IS
2664 SELECT adl.award_set_id , adl.adl_line_num
2665 FROM ap_invoice_distributions AP,
2666 gms_award_distributions ADL
2667 where adl.invoice_distribution_id = AP.invoice_distribution_id
2668 and adl.adl_status = 'A'
2669 and adl.award_id = p_record.award_id
2670 and ap.expenditure_type = p_record.expenditure_type
2671 and ADL.award_set_id = AP.award_id
2672 and ap.invoice_id = P_RECORD.document_header_id
2673 and ap.invoice_distribution_id = P_RECORD.document_distribution_id ; -- AP Lines change
2674
2675 cursor AP_ZERO_INVOICE is
2676 select sum( pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
2677 ap.invoice_id,
2678 nvl(ap.base_amount,ap.amount),
2679 'N',
2680 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount ,
2681 sum(nvl(adl.burdenable_raw_cost,0)) sum_burden,
2682 count(*) sum_count
2683 FROM ap_invoice_distributions AP,
2684 gms_award_distributions ADL,
2685 gl_ledgers G
2686 where adl.invoice_distribution_id = AP.invoice_distribution_id
2687 and adl.adl_status = 'A'
2688 and adl.award_id = bc_pkt_rec.award_id
2689 and ap.expenditure_type = bc_pkt_rec.expenditure_type
2690 and ADL.award_set_id = AP.award_id
2691 and G.LEDGER_ID = AP.SET_OF_BOOKS_ID
2692 and pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
2693 ap.invoice_id,
2694 nvl(ap.base_amount,ap.amount),
2695 'N',
2696 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
2697 and ap.line_type_lookup_code <> 'PREPAY'
2698 and decode(ap.pa_addition_flag,'Z','Y', 'T','Y', 'E','Y', 'G', 'Y',NULL, 'N', ap.pa_addition_flag ) <> 'Y'
2699 -- Bug 2097676, Fixing GSCC error File.sql.9
2700 and ap.invoice_id = bc_pkt_rec.document_header_id ;
2701
2702 BEGIN
2703 g_error_procedure_name := 'calc_ap_burden';
2704 IF g_debug = 'Y' THEN
2705 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
2706 END IF;
2707
2708 bc_pkt_rec := p_record ;
2709 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
2710 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
2711 -- ------------------------------
2712 -- Defaulting as a standard AP.
2713 -- ------------------------------
2714 X_AP_TYPE := 'STD' ;
2715
2716 if bc_pkt_rec.entered_cr <> 0 then
2717 pkt_amount := 0 - bc_pkt_rec.entered_cr;
2718 x_credit := TRUE ;
2719 elsif bc_pkt_rec.entered_dr <> 0 then
2720 pkt_amount := bc_pkt_rec.entered_dr;
2721 end if;
2722
2723 -- -----------------------------------------
2724 -- What are the possible scenarios for AP.
2725 -- 1. No IDC Limit Scenarios.
2726 -- 2. IDC Limit Scenarios.
2727 -- 2.1 - Credit Memo.
2728 -- 2.2 - Debit Memo.
2729 -- 2.3 - Cancelled AP
2730 -- 2.4 - Standard AP.
2731 -- -----------------------------------------
2732 -- ----------------------------------------------
2733 -- CASE 2.3 - Cancelled AP or AP with ZERO
2734 -- Raw cost.
2735 -- ----------------------------------------------
2736 OPEN AP_ZERO_INVOICE ;
2737 FETCH AP_ZERO_INVOICE into l_sum_amount,
2738 l_sum_burden,
2739 l_sum_count ;
2740 IF l_sum_amount = 0 THEN
2741 -- ---------------------
2742 -- CALL net Zero AP.
2743 -- ---------------------
2744 NET_ZERO_AP(P_record) ;
2745 X_ap_type := 'NET-ZERO' ;
2746 burden_raw_cost := 0 ;
2747
2748 -- -------------------------------------------------------------------------------
2749 -- BUG : 1321744 IDC Limit scenario fails incase of cancelling DEBIT Memo.
2750 -- Scenarion - PO - 1000/1000
2751 -- AP - 2500/2000 , PO - 1000/0
2752 -- DM - -600/1900 , PO - 1000/100
2753 -- Cancel DM - 2000/1900 , PO - 1000/100 WRONG*****
2754 -- Fixes - By find the adjustment amounts in cancel of cancellations.
2755 -- -------------------------------------------------------------------------------
2756 ap_adj_amount := ( x_rec_award_exp_tot.ap_raw_cost + pkt_amount ) -
2757 ( x_rec_award_exp_tot.ap_burdenable_cost - burden_raw_cost ) ;
2758
2759 -- ---------------------------------------------------------------------------------
2760 -- Information : Don't adjust anything if you don't have anything to adjust.
2761 -- ---------------------------------------------------------------------------------
2762 OPEN C_MEMO ;
2763 Fetch c_memo into l_memo ;
2764
2765 IF C_MEMO%FOUND THEN
2766 IF ( x_rec_award_exp_tot.req_burdenable_cost + x_rec_award_exp_tot.po_burdenable_cost +
2767 x_rec_award_exp_tot.enc_burdenable_cost ) <=0 THEN
2768 ap_adj_amount := 0 ;
2769 END IF ;
2770 END IF ;
2771
2772 CLOSE C_MEMO ;
2773
2774 END IF ;
2775
2776 -- --------------------------------------
2777 -- We have a IDC limit.
2778 -- X_dummy : is adjustment amount .
2779 -- --------------------------------------
2780 --
2781 -- Bug 4737148
2782 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2783 -- Source prepayment ivoice distributions attributes.
2784 --
2785 open c_prepay ;
2786 fetch c_prepay into l_prepay_type,
2787 l_prepay_dist_id,
2788 l_prepayment_brc,
2789 l_prepay_header_id,
2790 l_prepay_award_set_id,
2791 l_prepay_award_id,
2792 l_prepay_exp_type,
2793 l_prepay_project_id ,
2794 l_prepay_task_id ,
2795 l_prepay_ei_date ,
2796 l_prepay_exp_org_id,
2797 l_prepay_rlmi_id ,
2798 l_prepay_bud_task_id,
2799 l_prepay_ind_set_id ;
2800 close c_prepay ;
2801
2802 IF X_ap_type <> 'NET-ZERO' THEN
2803
2804 burden_raw_cost := common_logic( pkt_amount,
2805 x_rec_award_exp_tot.ap_raw_cost,
2806 x_rec_award_exp_tot.ap_burdenable_cost,
2807 ap_adj_amount) ;
2808 END IF;
2809
2810 /* =================================================================
2811 -- Bug : 2557041 - Added for IP check funds Enhancement
2812
2813 As burden adjustment will not be carried out NOCOPY in check funds mode,
2814 reutrn after updating the burdenable_raw_cost on gms_bc_packets
2815 ================================================================== */
2816
2817 IF p_mode = 'C' THEN
2818 update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
2819 RETURN TRUE ;
2820 END IF;
2821
2822 OPEN C_GET_AWARD_SET_ID ;
2823 fetch C_GET_AWARD_SET_ID into x_award_set_id, x_adl_line_num ;
2824 CLOSE C_GET_AWARD_SET_ID ;
2825
2826 --
2827 -- Bug 4737148
2828 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2829 -- Logic :
2830 -- Determine the burdenable raw cost stored in source prepayment invoice distributions.
2831 -- APPLY action will reduce the burdenable raw cost in source prepayment invoice distributions.
2832 -- reduced amount is adjusted across another invoice that may be underburden.
2833 --
2834 /* Bug 5645290 - Checking for l_prepay_dist_id instead of l_prepay_type */
2835 -- IF l_prepay_type = 'PREPAY' and pkt_amount < 0 THEN
2836 IF l_prepay_dist_id IS NOT NULL and pkt_amount < 0 THEN
2837 burden_raw_cost := 0 ;
2838 IF abs( pkt_amount) > l_prepayment_brc then
2839 ap_adj_amount := -1 * l_prepayment_brc ;
2840 ELSE
2841 ap_adj_amount := pkt_amount ;
2842 END IF ;
2843
2844 IF ap_adj_amount <> 0 THEN
2845
2846 IF x_adjustment_id IS NULL THEN
2847 select gms_adjustments_id_s.NEXTVAL
2848 INTO x_adjustment_id
2849 FROM dual ;
2850 END IF ;
2851 x_line_num := NVL(x_line_num,0) + 1 ;
2852
2853 x_rec_log.adjustment_id := x_adjustment_id ;
2854 x_rec_log.line_num := x_line_num ;
2855 x_rec_log.document_header_id := l_prepay_header_id ;
2856 x_rec_log.document_type := 'AP' ;
2857 x_rec_log.amount := 0 ;
2858 x_rec_log.source_flag := 'Y' ;
2859 x_rec_log.award_set_id := l_prepay_award_set_id ;
2860 x_rec_log.adl_line_num := 1 ;
2861 x_rec_log.award_id := l_prepay_award_id ;
2862 x_rec_log.expenditure_type := l_prepay_exp_type ;
2863 x_rec_log.packet_id := p_record.packet_id ;
2864 x_rec_log.bc_packet_id := p_record.bc_packet_id ;
2865 x_rec_log.document_distribution_id:= l_prepay_dist_id ;
2866 x_rec_log.adj_burdenable_amount := ap_adj_amount ;
2867
2868 -- ------------------------------------------------------
2869 -- Create a adjustment log for this ITEM.
2870 -- ------------------------------------------------------
2871 --
2872 -- Bug 4737148
2873 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2874 -- Create adjusting entries.
2875 --
2876 create_burden_adjustments(x_rec_log,
2877 l_prepay_project_id,
2878 l_prepay_task_id,
2879 l_prepay_ei_date,
2880 l_prepay_exp_org_id,
2881 l_prepay_rlmi_id ,
2882 l_prepay_bud_task_id,
2883 l_prepay_ind_set_id ) ;
2884
2885
2886 x_dummy := self_adjustment ( 'AP', p_record, (-1*ap_adj_amount), x_adjustment_id, x_line_num ) ;
2887 END IF ;
2888
2889 END IF ;
2890
2891 IF burden_raw_cost < 0 THEN
2892 X_ap_type := 'MEMO-TYPE' ;
2893 ap_adj_amount := burden_raw_cost ;
2894 x_dummy := self_adjustment( 'AP',
2895 p_record,
2896 burden_raw_cost,
2897 x_adjustment_id,
2898 x_line_num) ;
2899 burden_raw_cost := nvl(x_dummy,0) ;
2900 -- adjusted amount
2901 ap_adj_amount := ap_adj_amount - ABS(x_dummy) ;
2902
2903 ap_adj_amount := 0 ;
2904 END IF ;
2905
2906 -- --------------------------------------------------
2907 -- ap_adj_amount will be computed here only for +ve
2908 -- amounts.
2909 -- --------------------------------------------------
2910 IF ap_adj_amount > 0 AND ( NVL(X_ap_type,'X') <> 'NET-ZERO' OR NVL(L_MEMO,0) > 0 ) THEN
2911
2912 -- ------------------------------------------------
2913 -- exp adj_amount is the amount need to be adjusted
2914 -- We take the amount from ENC and update EXP.
2915 -- The sequence of adjustment is REQ- PO-ENC.
2916 -- -------------------------------------------------
2917 x_dummy := 0 ;
2918 x_dummy := create_adjplus_log( 'REQ' ,
2919 ap_adj_amount,
2920 bc_pkt_rec.award_id,
2921 bc_pkt_rec.expenditure_type,
2922 bc_pkt_rec.packet_id ,
2923 bc_pkt_rec.bc_packet_id,
2924 bc_pkt_rec.document_header_id,
2925 bc_pkt_rec.document_distribution_id,
2926 FALSE,
2927 x_adjustment_id ,
2928 x_line_num ) ;
2929 ap_adj_amount := ap_adj_amount - x_dummy ;--bug 2311261
2930
2931 IF NVL(X_ap_type,'X') <> 'NET-ZERO' THEN
2932 -- --------------------------------------------------
2933 -- BUG 1321744 : IDC Limit scenario fails in case of
2934 -- cancelling debit memo.
2935 -- --------------------------------------------------
2936 burden_raw_cost := burden_raw_cost + x_dummy ;
2937 END IF ;
2938 END IF ;
2939
2940 --
2941 -- Bug 4737148
2942 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2943 -- Logic :
2944 -- Determine the burdenable raw cost stored in source prepayment invoice distributions.
2945 -- UNAPPLY action will increase the burdenable raw cost in source prepayment invoice distributions.
2946 -- unapply distributions will get the zero burdenable raw cost.
2947 --
2948 /* Bug 5645290 - Checking for l_prepay_dist_id instead of l_prepay_type */
2949 -- IF l_prepay_type = 'PREPAY' and pkt_amount > 0 THEN
2950 IF l_prepay_dist_id IS NOT NULL and pkt_amount > 0 THEN
2951
2952 IF burden_raw_cost <> 0 THEN
2953
2954 IF x_adjustment_id IS NULL THEN
2955 select gms_adjustments_id_s.NEXTVAL
2956 INTO x_adjustment_id
2957 FROM dual ;
2958 END IF ;
2959 x_line_num := NVL(x_line_num,0) + 1 ;
2960
2961 x_rec_log.adjustment_id := x_adjustment_id ;
2962 x_rec_log.line_num := x_line_num ;
2963 x_rec_log.document_header_id := l_prepay_header_id ;
2964 x_rec_log.document_type := 'AP' ;
2965 x_rec_log.amount := 0 ;
2966 x_rec_log.source_flag := 'Y' ;
2967 x_rec_log.award_set_id := l_prepay_award_set_id ;
2968 x_rec_log.adl_line_num := 1 ;
2969 x_rec_log.award_id := l_prepay_award_id ;
2970 x_rec_log.expenditure_type := l_prepay_exp_type ;
2971 x_rec_log.packet_id := p_record.packet_id ;
2972 x_rec_log.bc_packet_id := p_record.bc_packet_id ;
2973 x_rec_log.document_distribution_id:= l_prepay_dist_id ;
2974 x_rec_log.adj_burdenable_amount := burden_raw_cost ;
2975 burden_raw_cost := 0 ;
2976
2977 -- ------------------------------------------------------
2978 -- Create a adjustment log for this ITEM.
2979 -- ------------------------------------------------------
2980 --
2981 -- Bug 4737148
2982 -- burdenable raw cost calculations for apply/unapply prepayment distributions.
2983 -- Create the adjusting entries.
2984 --
2985 create_burden_adjustments(x_rec_log,
2986 l_prepay_project_id,
2987 l_prepay_task_id,
2988 l_prepay_ei_date,
2989 l_prepay_exp_org_id,
2990 l_prepay_rlmi_id ,
2991 l_prepay_bud_task_id,
2992 l_prepay_ind_set_id ) ;
2993 END IF ;
2994 END IF ;
2995
2996 -- -------------------------------------------------------------------
2997 -- Update the Running Total.
2998 -- -------------------------------------------------------------------
2999 update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
3000
3001 return TRUE ;
3002
3003 EXCEPTION
3004 WHEN others THEN
3005
3006 g_dummy := SQLERRM;
3007 IF g_debug = 'Y' THEN
3008 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
3009 END IF;
3010
3011 RAISE ;
3012 END CALC_AP_burden;
3013 -- ------------------AP ----------------------------------------------
3014
3015 FUNCTION CALC_FAB_burden(p_record bc_packets%ROWTYPE) return boolean
3016 IS
3017 bc_pkt_rec bc_packets%rowtype;
3018 burden_raw_cost number;
3019 pkt_amount number;
3020 fab_adj_amount NUMBER ;
3021
3022 BEGIN
3023 g_error_procedure_name := 'calc_fab_burden';
3024 IF g_debug = 'Y' THEN
3025 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
3026 END IF;
3027
3028 bc_pkt_rec := p_record ;
3029 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
3030 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
3031
3032 if bc_pkt_rec.entered_cr <> 0 then
3033 pkt_amount := 0 - bc_pkt_rec.entered_cr;
3034 elsif bc_pkt_rec.entered_dr <> 0 then
3035 pkt_amount := bc_pkt_rec.entered_dr;
3036 end if;
3037
3038 FAB_adj_amount := 0 ;
3039
3040 -- ============================================================
3041 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
3042 -- : BURDENING.
3043 -- ============================================================
3044 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
3045 IF x_calc_zero_limit THEN
3046 burden_raw_cost := 0 ;
3047 update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
3048 return TRUE ;
3049 END IF ;
3050
3051
3052 IF nvl(x_award_exp_limit,0) <= 0 THEN
3053 -- --------------------------
3054 -- IDC Limit is not enabled.
3055 -- -------------------------
3056 burden_raw_cost := pkt_amount ;
3057 update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
3058 return TRUE ;
3059 END IF ;
3060 */
3061 burden_raw_cost := common_logic( pkt_amount,
3062 0,
3063 0,
3064 fab_adj_amount ) ;
3065
3066 -- -------------------------------------------------------------------
3067 -- Update the Running Total.
3068 -- -------------------------------------------------------------------
3069 update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
3070
3071 return TRUE ;
3072 EXCEPTION
3073 WHEN others THEN
3074
3075 g_dummy := SQLERRM;
3076 IF g_debug = 'Y' THEN
3077 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
3078 END IF;
3079
3080 RAISE ;
3081 END CALC_FAB_burden ;
3082
3083 FUNCTION CALC_EXP_burden(p_record bc_packets%ROWTYPE,p_mode IN VARCHAR2 ) return boolean -- Bug : 2557041 - Added p_mode parameter
3084 IS
3085 pkt_amount number;
3086 x_adjustment_id NUMBER ;
3087 x_line_num NUMBER ;
3088 x_dummy NUMBER ;
3089 burden_raw_cost number;
3090 exp_adj_amount NUMBER ;
3091 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
3092 bc_pkt_rec bc_packets%rowtype;
3093 exp_adjustment BOOLEAN ;
3094
3095 -- =================================================================================
3096 -- ALLOW_BURDEN_FLAG - FLAG indicates that external system will provide burdened
3097 -- cost.
3098 -- =================================================================================
3099 x_allow_burden_flag pa_transaction_sources.allow_burden_flag%TYPE ;
3100 x_transaction_source pa_expenditure_items_all.transaction_source%TYPE ;
3101
3102 BEGIN
3103 g_error_procedure_name := 'calc_exp_burden';
3104 IF g_debug = 'Y' THEN
3105 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
3106 END IF;
3107
3108 bc_pkt_rec := p_record ;
3109 bc_pkt_rec.entered_cr := nvl(bc_pkt_rec.entered_cr,0) ;
3110 bc_pkt_rec.entered_dr := nvl(bc_pkt_rec.entered_dr,0) ;
3111 x_allow_burden_flag := 'N' ;
3112 x_transaction_source := bc_pkt_rec.transaction_source;
3113
3114 if bc_pkt_rec.entered_cr <> 0 then
3115 pkt_amount := 0 - bc_pkt_rec.entered_cr;
3116 elsif bc_pkt_rec.entered_dr <> 0 then
3117 pkt_amount := bc_pkt_rec.entered_dr;
3118 end if;
3119
3120 x_allow_burden_flag := burden_allowed(x_transaction_source);
3121
3122 exp_adj_amount := 0 ;
3123
3124 -- Set burdenable raw cost to zero if burden_allowed returns N.
3125
3126 IF x_allow_burden_flag = 'N' THEN
3127
3128 burden_raw_cost := 0 ;
3129 update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3130 return TRUE ;
3131
3132 END IF ;
3133
3134
3135 -- ============================================================
3136 -- Bug : 1776185 - IDC LIMITS OF $0 NOT BEING RECOGNIZED WHEN
3137 -- : BURDENING.
3138 -- ============================================================
3139 /* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
3140 IF x_calc_zero_limit THEN
3141 burden_raw_cost := 0 ;
3142 update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3143 return TRUE ;
3144 END IF ;
3145
3146 IF nvl(x_award_exp_limit,0) <= 0 THEN
3147 -- --------------------------
3148 -- IDC Limit is not enabled.
3149 -- -------------------------
3150 burden_raw_cost := pkt_amount ;
3151 update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3152 return TRUE ;
3153 END IF ;
3154 */
3155
3156 burden_raw_cost := common_logic( pkt_amount,
3157 x_rec_award_exp_tot.EXP_raw_cost,
3158 x_rec_award_exp_tot.EXP_burdenable_cost,
3159 exp_adj_amount ) ;
3160
3161 IF g_debug = 'Y' THEN
3162 gms_error_pkg.gms_debug (g_error_procedure_name||':common logic->burden_raw_cost:'||burden_raw_cost||';'||'exp_adj_amount:'||exp_adj_amount,'C');
3163 END IF;
3164
3165 /* =================================================================
3166 -- Bug : 2557041 - Added for IP check funds Enhancement
3167
3168 As burden adjustment will not be carried out NOCOPY in check funds mode,
3169 reutrn after updating the burdenable_raw_cost on gms_bc_packets
3170 ================================================================== */
3171
3172 IF p_mode = 'C' THEN
3173 update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3174 RETURN TRUE ;
3175 END IF;
3176
3177
3178 -- --------------------------------------------------
3179 -- exp_adj_amount will be computed here only for +ve
3180 -- amounts.
3181 -- --------------------------------------------------
3182 IF exp_adj_amount <> 0 THEN
3183
3184 g_request_id := bc_pkt_rec.request_id;
3185
3186 -- ------------------------------------------------
3187 -- exp adj_amount is the amount need to be adjusted
3188 -- We take the amount from ENC and update EXP.
3189 -- The sequence of adjustment is REQ- PO-ENC.
3190 -- -------------------------------------------------
3191 -- BUG:1349726 : ENC_BURDENABLE_COST is not
3192 -- released in acse of actuals with IDC scenario.
3193 -- --bc_pkt_rec.document_distribution_id COMMENTED
3194 -- because we always adjust from REQ<PO and ENC so
3195 -- we really don't care value of this here.
3196 -- In case of ENC bc_pkt_rec.document_distribution_id is
3197 -- 1 most of the time and ENC is always 1 so adjustment
3198 -- didn't happened.
3199 -- -------------------------------------------------
3200
3201 x_dummy := 0 ;
3202 x_dummy := create_adjplus_log( 'REQ' ,
3203 exp_adj_amount,
3204 bc_pkt_rec.award_id,
3205 bc_pkt_rec.expenditure_type,
3206 bc_pkt_rec.packet_id,
3207 bc_pkt_rec.bc_packet_id,
3208 bc_pkt_rec.document_header_id,
3209 --bc_pkt_rec.document_distribution_id,
3210 0,
3211 FALSE,
3212 x_adjustment_id ,
3213 x_line_num ) ;
3214
3215 burden_raw_cost := burden_raw_cost + x_dummy ;
3216
3217 END IF ;
3218
3219 -- -------------------------------------------------------------------
3220 -- Update the Running Total.
3221 -- -------------------------------------------------------------------
3222 update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3223
3224 return TRUE ;
3225
3226 EXCEPTION
3227 WHEN others THEN
3228
3229 g_dummy := SQLERRM;
3230 IF g_debug = 'Y' THEN
3231 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
3232 END IF;
3233
3234 RAISE ;
3235 END CALC_EXP_burden;
3236 -- ================== End of CALC_EXP_burden == ======================
3237
3238 -- ----------------------------------------------------------------------------- +
3239 -- New procedure, added 11i.GMS.M , Bug 3389292: burden log changes..
3240 -- Following procedure maximizes burden within and outside the packet
3241 -- First, it will check if there are any txns. that needs to be maximized
3242 -- within the packet.
3243 -- Then it will check if there are any AP txns. outside the packet (FC passed)
3244 -- txns. that needs to be maximized.
3245 -- ----------------------------------------------------------------------------- +
3246 PROCEDURE Maximize_burden(p_packet_id in number) is
3247 -- pick award and expenditure type that has
3248 -- * limit (non-zero)
3249 -- * Only check original transaction being FC'ed (parent_bc_packet_id is null)
3250 Cursor c_awd_exp is
3251 select distinct gbp.award_id,gbp.expenditure_type
3252 from gms_bc_packets gbp,
3253 gms_awards_all ga,
3254 gms_allowable_expenditures gae
3255 where gbp.packet_id = p_packet_id
3256 and ga.award_id = gbp.award_id
3257 and gae.allowability_schedule_id = ga.allowable_schedule_id
3258 and gae.expenditure_type = gbp.expenditure_type
3259 and nvl(gae.burden_cost_limit,0) > 0
3260 and gbp.parent_bc_packet_id is null;
3261
3262 -- Get bcpacket records (+ve txn) for an award/expenditure type that has
3263 -- records that are underburdened ...
3264 -- order by diff between raw and burden asc.
3265 -- ascending used as smaller txns. will get processed for FC/bill limits
3266 -- Only check original transaction being FC'ed (parent_bc_packet_id is null)
3267 Cursor c_bcpkts(x_award_id in number, x_expenditure_type in varchar2) is
3268 select rowid,
3269 entered_dr ,
3270 nvl(burdenable_raw_cost,0) burden
3271 from gms_bc_packets gbp
3272 where gbp.packet_id = p_packet_id
3273 and gbp.award_id = x_award_id
3274 and gbp.expenditure_type = x_expenditure_type
3275 and nvl(gbp.entered_dr,0) > 0
3276 and nvl(entered_cr,0) = 0
3277 and nvl(gbp.entered_dr,0) <> nvl(gbp.burdenable_raw_cost,0)
3278 and gbp.parent_bc_packet_id is null
3279 order by decode(gbp.document_type,'EXP',1,'AP',2,'ENC',3,'PO',4,'REQ',5,6) asc,
3280 nvl(gbp.entered_dr,0) desc;
3281
3282 -- Variable holds burden that can be maximized
3283 x_avail_burden_amt gms_bc_packets.burdenable_raw_cost%type;
3284
3285 -- Variable to hold stage
3286 x_stage number(2);
3287
3288 -- --------------------------------------------------------------+
3289 x_rec_log gms_burden_adjustments_log%ROWTYPE ;
3290 x_doc_adj c_adj_rec ;
3291 c_rec commitRecTyp ;
3292 x_temp NUMBER ;
3293 x_adjustment_id NUMBER ;
3294
3295 -- Variable stores burden amount that can be updated on AP txn.
3296 X_burden_amt_to_update_on_txn NUMBER;
3297
3298 -- Cursor that checks for Net Zero AP
3299 cursor AP_ZERO_INVOICE (p_invoice_id in number,
3300 p_award_id in number,
3301 p_expenditure_type in varchar2) is
3302 select sum( pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
3303 ap.invoice_id,
3304 nvl(ap.base_amount,ap.amount),
3305 'N',
3306 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount
3307 FROM ap_invoice_distributions AP,
3308 gms_award_distributions ADL,
3309 GL_LEDGERS G
3310 where adl.invoice_distribution_id = AP.invoice_distribution_id
3311 and adl.adl_status = 'A'
3312 and adl.award_id = p_award_id
3313 and ap.expenditure_type = p_expenditure_type
3314 and ADL.award_set_id = AP.award_id
3315 and ap.invoice_id = p_invoice_id
3316 and G.LEDGER_ID = AP.SET_OF_BOOKS_ID
3317 and pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
3318 ap.invoice_id,
3319 nvl(ap.base_amount,ap.amount),
3320 'N',
3321 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
3322 and decode(ap.pa_addition_flag,'Z','Y','G','Y','T','Y','E','Y',NULL,'N',ap.pa_addition_flag) <> 'Y' ;
3323
3324 -- Cursor to pick expenditure items that has lead to maximizing AP ..
3325 Cursor c_bcpkts_max(p_award_id in number, p_expenditure_type in varchar2) is
3326 select bc_packet_id,
3327 abs(nvl(burdenable_raw_cost,0)) burdenable_raw_cost
3328 from gms_bc_packets gbp
3329 where gbp.packet_id = p_packet_id
3330 and gbp.award_id = p_award_id
3331 and gbp.expenditure_type = p_expenditure_type
3332 and nvl(gbp.burdenable_raw_cost,0) < 0
3333 and gbp.parent_bc_packet_id is null
3334 order by nvl(gbp.burdenable_raw_cost,0) desc;
3335
3336 x_bc_packet_id gms_bc_packets.bc_packet_id%type;
3337 x_burdenable_raw_cost gms_bc_packets.burdenable_raw_cost%type;
3338
3339 Begin
3340 g_error_procedure_name := 'Maximize_burden';
3341 IF g_debug = 'Y' THEN
3342 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
3343 END IF;
3344 -- ------------------------------------------------------------------+
3345 -- Maximizing common logic
3346 -- ------------------------------------------------------------------+
3347 -- initialize variables ..
3348 x_cmt_tot_burden := 0;
3349 x_tot_raw := 0;
3350 x_tot_burden := 0;
3351 x_stage := 10;
3352
3353 -- Open cursor
3354 for x in c_awd_exp -- Award,Exp Type Loop
3355 loop
3356 -- Savepoint
3357 SAVEPOINT RECONCILE1;
3358 IF g_debug = 'Y' THEN
3359 gms_error_pkg.gms_debug (g_error_procedure_name||':Maximize:award/exp:'||x.award_id||';'||x.expenditure_type,'C');
3360 END IF;
3361 -- Cursor and variable defined at package level ..
3362 Open c_lock_burden_summary(x.award_id,x.expenditure_type);
3363 fetch c_lock_burden_summary into l_lock_burden_summary;
3364 close c_lock_burden_summary;
3365
3366 -- Get raw and burden total, idc limit
3367 proc_get_award_exp_total( p_packet_id, x.award_id, x.expenditure_type ) ;
3368 g_error_procedure_name := 'Maximize_burden';
3369 -- need to re-initalize g_error_procedure_name
3370
3371 IF g_debug = 'Y' THEN
3372 gms_error_pkg.gms_debug (g_error_procedure_name||':x_award_exp_limit:'||x_award_exp_limit||';'||'x_tot_burden:'||x_tot_burden||';'||'x_tot_raw:'||x_tot_raw,'C');
3373 END IF;
3374
3375 -- Check if there is any maximizing scope
3376 If (x_award_exp_limit <= x_tot_burden) -- burden.cost v/s idc limit
3377 OR
3378 (x_tot_raw <= x_tot_burden) -- burden.cost v/s raw.cost
3379 then
3380 IF g_debug = 'Y' THEN
3381 gms_error_pkg.gms_debug (g_error_procedure_name||':No maximization reqd. as burden already maximized','C');
3382 END IF;
3383 -- No maximizing required
3384
3385 GOTO END_OF_PROCESS;
3386
3387 End If;
3388
3389 -- Get burden available for maximizing
3390 -- case idc:100, raw:80 and burden:70 , x_avail_burden_amt = 10
3391 -- case idc:75, raw:80 and burden:70 , x_avail_burden_amt = 5
3392 x_avail_burden_amt := least(x_award_exp_limit,x_tot_raw) - x_tot_burden;
3393
3394 IF g_debug = 'Y' THEN
3395 gms_error_pkg.gms_debug (g_error_procedure_name||':x_avail_burden_amt:'||x_avail_burden_amt,'C');
3396 END IF;
3397
3398 -- If nothing to apply then no maximizing required.
3399 If x_avail_burden_amt <= 0 then
3400 -- No maximizing required
3401 IF g_debug = 'Y' THEN
3402 gms_error_pkg.gms_debug (g_error_procedure_name||':No maximization reqd. as no burden left to maximize','C');
3403 END IF;
3404
3405 GOTO END_OF_PROCESS;
3406 End If;
3407
3408 x_stage := 20;
3409 -- ------------------------------------------------------------------+
3410 -- Maximizing gms_bc_packet records
3411 -- ------------------------------------------------------------------+
3412 for y in c_bcpkts(x.award_id,x.expenditure_type)
3413 loop -- Bcpkt record loop
3414 If ((y.entered_dr - y.burden) >= x_avail_burden_amt) then
3415
3416 Update gms_bc_packets
3417 set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + x_avail_burden_amt
3418 where rowid = y.rowid;
3419
3420 x_avail_burden_amt := 0;
3421
3422 Else
3423 Update gms_bc_packets
3424 set burdenable_raw_cost = y.entered_dr
3425 where rowid = y.rowid;
3426
3427 x_avail_burden_amt := x_avail_burden_amt - (y.entered_dr - y.burden);
3428
3429 End If;
3430
3431 If x_avail_burden_amt = 0 then
3432 EXIT;
3433 End If;
3434 End loop; -- Bcpkt record loop
3435
3436 IF g_debug = 'Y' THEN
3437 gms_error_pkg.gms_debug (g_error_procedure_name||':After max. packet,x_avail_burden_amt:'||x_avail_burden_amt,'C');
3438 END IF;
3439
3440 If x_avail_burden_amt = 0 then
3441 -- No maximizing required
3442 IF g_debug = 'Y' THEN
3443 gms_error_pkg.gms_debug (g_error_procedure_name||':Packet maximized,No further maximization reqd. as x_avail_burden_amt:'||x_avail_burden_amt,'C');
3444 END IF;
3445
3446 GOTO END_OF_PROCESS;
3447 End If;
3448
3449 -- ------------------------------------------------------------------+
3450 -- Maximizing records that has already passed FC,outside current pkt.
3451 -- ------------------------------------------------------------------+
3452 x_stage := 30;
3453 SAVEPOINT RECONCILE2;
3454 -- A.0 open cursor using the base table
3455 -- Fetch AP records that are underburdened ...
3456 open_ref_cursor ( x_doc_adj,
3457 'AP',
3458 x.award_id,
3459 x.expenditure_type,
3460 NULL, -- p_dist_id
3461 NULL, -- p_header_id ,
3462 'MAXIMIZE_BURDEN'); -- p_calling_seq
3463 LOOP
3464 Fetch x_doc_adj into c_rec ;
3465 If x_doc_adj%notfound THEN
3466 -- A.1 if no AP records exist .. exit out for award/exp type ..
3467 IF g_debug = 'Y' THEN
3468 g_error_procedure_name := 'Maximize_Burden';
3469 gms_error_pkg.gms_debug (g_error_procedure_name||':No AP txns. to maximize - Exiting, Burden available to max:'||x_avail_burden_amt,'C');
3470 END IF;
3471
3472 Close x_doc_adj ;
3473 exit ;
3474 End If;
3475
3476 g_error_procedure_name := 'Maximize_Burden';
3477 IF g_debug = 'Y' THEN
3478 gms_error_pkg.gms_debug (g_error_procedure_name||':AP txns:header_id,raw.cost,burden.cost:'||c_rec.header_id||';'||c_rec.amount||';'||c_rec.burden,'C');
3479 END IF;
3480
3481 -- A.2 Check if its a NET zero AP .
3482 Open AP_ZERO_INVOICE(c_rec.header_id,x.award_id,x.expenditure_type);
3483 Fetch AP_ZERO_INVOICE into x_temp ;
3484 Close AP_ZERO_INVOICE ;
3485
3486 If x_temp = 0 THEN
3487 IF g_debug = 'Y' THEN
3488 gms_error_pkg.gms_debug (g_error_procedure_name||':Skipping AP txns:net zero AP','C');
3489 END IF;
3490
3491 GOTO SKIP_THIS;
3492 End If;
3493
3494 IF g_debug = 'Y' THEN
3495 gms_error_pkg.gms_debug (g_error_procedure_name||':After Net Zero Check','C');
3496 END IF;
3497
3498 -- A.3 What is the burden amount that can be updated on this transaction ..
3499 If (c_rec.amount - c_rec.burden) >= x_avail_burden_amt then
3500 X_burden_amt_to_update_on_txn := x_avail_burden_amt;
3501 x_avail_burden_amt := 0;
3502 -- All available burden can be updated on this txn.
3503 Else
3504 X_burden_amt_to_update_on_txn := c_rec.amount - c_rec.burden;
3505 x_avail_burden_amt := x_avail_burden_amt - X_burden_amt_to_update_on_txn;
3506 -- Only a portion of the available burden can be updated on this txn.
3507 End If;
3508
3509 IF g_debug = 'Y' THEN
3510 gms_error_pkg.gms_debug (g_error_procedure_name||':X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
3511 END IF;
3512
3513 -- A.4 Get burden_calculation_seq (adjustment_id) and set values (Common)
3514 select gms_adjustments_id_s.NEXTVAL
3515 INTO x_adjustment_id
3516 FROM dual ;
3517
3518 x_rec_log.packet_id := p_packet_id ;
3519 x_rec_log.award_id := x.award_id;
3520 x_rec_log.expenditure_type := x.expenditure_type ;
3521 x_rec_log.document_type := 'AP';
3522 x_rec_log.amount := 0 ;
3523 x_rec_log.source_flag := 'Y' ;
3524 x_rec_log.adjustment_id := x_adjustment_id ;
3525 x_rec_log.document_header_id := c_rec.header_id ;
3526 x_rec_log.document_distribution_id := c_rec.dist_id ;
3527
3528
3529 x_rec_log.last_update_date := sysdate;
3530 x_rec_log.last_updated_by := -1;
3531 x_rec_log.created_by := -1;
3532 x_rec_log.creation_date := sysdate;
3533 x_rec_log.last_update_login := -1;
3534
3535 IF g_debug = 'Y' THEN
3536 gms_error_pkg.gms_debug (g_error_procedure_name||':before loop:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
3537 END IF;
3538
3539 -- A.5 Get bcpkt transaction that caused this burden maximization
3540 For z in c_bcpkts_max(x.award_id,x.expenditure_type)
3541 Loop
3542
3543 -- A.6 Txn. found, check if this txn is responsible for the burden available
3544 If z.burdenable_raw_cost >= X_burden_amt_to_update_on_txn then
3545 x_rec_log.adj_burdenable_amount := X_burden_amt_to_update_on_txn;
3546 X_burden_amt_to_update_on_txn := 0;
3547 Else
3548 x_rec_log.adj_burdenable_amount := z.burdenable_raw_cost;
3549 X_burden_amt_to_update_on_txn := X_burden_amt_to_update_on_txn - z.burdenable_raw_cost;
3550 End If;
3551 x_rec_log.bc_packet_id := z.bc_packet_id ;
3552
3553 -- A.7 Create burden adjustments ..
3554 create_burden_adjustments(x_rec_log,
3555 c_rec.project_id,
3556 c_rec.task_id,
3557 c_rec.expenditure_item_date,
3558 c_rec.expenditure_organization_id,
3559 c_rec.resource_list_member_id,
3560 c_rec.bud_task_id,
3561 c_rec.ind_compiled_set_id) ;
3562
3563 If X_burden_amt_to_update_on_txn = 0 then
3564 exit;
3565 End if;
3566 End Loop; -- bcpkt loop
3567
3568 IF g_debug = 'Y' THEN
3569 gms_error_pkg.gms_debug (g_error_procedure_name||':After loop:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
3570 END IF;
3571
3572 If X_burden_amt_to_update_on_txn <> 0 then
3573 -- A.8 There are no records in bcpkts that can account for the available
3574 -- burden amount that can be maximized on this AP txn...
3575 -- Create record with dummy bc_packet_id
3576 x_rec_log.adj_burdenable_amount := X_burden_amt_to_update_on_txn;
3577 X_burden_amt_to_update_on_txn := 0;
3578 x_rec_log.bc_packet_id := -1;
3579
3580 -- A.9 Create burden adjustments ..
3581 create_burden_adjustments(x_rec_log,
3582 c_rec.project_id,
3583 c_rec.task_id,
3584 c_rec.expenditure_item_date,
3585 c_rec.expenditure_organization_id,
3586 c_rec.resource_list_member_id,
3587 c_rec.bud_task_id,
3588 c_rec.ind_compiled_set_id) ;
3589
3590 End If;
3591
3592 IF g_debug = 'Y' THEN
3593 gms_error_pkg.gms_debug (g_error_procedure_name||':After all bckpt:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
3594 END IF;
3595
3596 IF g_debug = 'Y' THEN
3597 gms_error_pkg.gms_debug (g_error_procedure_name||':After all bckpt:x_avail_burden_amt:'||x_avail_burden_amt,'C');
3598 END IF;
3599
3600 -- A.10 No more burden available to maximize, go to next award/exp type ..
3601 -- else continue with the next AP transaction
3602 If x_avail_burden_amt = 0 then
3603 exit;
3604 End If;
3605
3606 <<SKIP_THIS>>
3607 null;
3608 END LOOP; -- AP txn. loop
3609
3610 <<END_OF_PROCESS>>
3611 -- Initialize variables
3612 x_avail_burden_amt := 0;
3613 x_cmt_tot_burden := 0;
3614 x_tot_raw := 0;
3615 x_tot_burden := 0;
3616
3617 COMMIT;
3618 end loop; -- Award,Exp Type Loop
3619
3620 Exception
3621 When no_data_found then
3622 g_dummy := SQLERRM;
3623 IF g_debug = 'Y' THEN
3624 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
3625 END IF;
3626
3627 If x_stage < 30 then
3628 ROLLBACK TO RECONCILE1;
3629 Elsif x_stage = 30 then
3630 ROLLBACK TO RECONCILE2;
3631 End If;
3632 commit;
3633
3634 When others then
3635
3636 g_dummy := SQLERRM;
3637 IF g_debug = 'Y' THEN
3638 gms_error_pkg.gms_debug (g_error_procedure_name||':'||g_dummy,'C');
3639 END IF;
3640
3641 If x_stage < 30 then
3642 ROLLBACK TO RECONCILE1;
3643 Elsif x_stage = 30 then
3644 ROLLBACK TO RECONCILE2;
3645 End If;
3646 commit;
3647 End Maximize_burden;
3648
3649 -- ============================================================================
3650 -- R12 Funds Management Uptake : From R12 onwards Payables and Purchasings
3651 -- code will no longer save the transactions before calling grants, hence
3652 -- existing logic needs to be modified such that the part of code which needs
3653 -- access to AP/PO/REQ tables get fired from main session and the remaining code
3654 -- gets fired in autonomous mode as its existing currently.
3655 -- Introduced new Autonomous procedure to handle all the updates on gms_bc_packet
3656 -- which were in function update_bc_pkt_burden_raw_cost
3657
3658 -- --------------------------------------------------------------
3659 -- Function to update the burdenable raw cost,budget version Id and status
3660 -- in GMS_BC_PACKETS.All the records for a packet is updated.
3661 -- Parameters :
3662 -- ==============
3663 -- p_action : This parameter defines action to be performed on gms_bc_packets
3664 -- Values :
3665 -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666 -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667 -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668 -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669 -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670 -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3671 -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
3672 -- p_result_code : Failed result code
3673
3674 -- --------------------------------------------------------------
3675
3676 PROCEDURE update_bc_pkt_brc_bvid_status (p_action IN VARCHAR2,
3677 p_mode IN VARCHAR2 DEFAULT NULL,
3678 p_packet_id IN NUMBER DEFAULT NULL,
3679 p_award_id IN NUMBER DEFAULT NULL,
3680 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
3681 p_full_mode_failure IN VARCHAR2 DEFAULT NULL,
3682 x_result_code IN OUT NOCOPY VARCHAR2
3683 ) IS
3684 PRAGMA AUTONOMOUS_TRANSACTION;
3685 l_count NUMBER;
3686
3687 BEGIN
3688
3689 IF g_debug = 'Y' THEN
3690 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS' ||': Start'||l_count,'C');
3691 END IF;
3692
3693 IF p_action = g_update_brc THEN
3694
3695 IF g_debug = 'Y' THEN
3696 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Burdenable Raw cost update :','C');
3697 END IF;
3698
3699 /* --------------------------------------------------------------------
3700 -- This update will take care of 3 scenarios:
3701 -- A. Non-IDC limit
3702 -- B. Zero$ IDC Limit
3703 -- C. P82 scenarios
3704 -- Logic:
3705 -- i.If result code is 'P82' , update burdenable raw cost to zero (net zero)
3706 -- ii. If not, check transaction source of transaction
3707 -- iii. No txn. source, check limit,
3708 -- iv. if no limit, same as raw_cost,
3709 -- v. if limit=0, burdenable raw cost=0
3710 -- vi. if limit, do not update burdeanable raw cost
3711 -- vii. If txn. source, then check burden allowed?
3712 -- viii. No ..burden is zero
3713 -- ix. yes .. step iv - vi ..
3714 -------------------------------------------------------------------- */
3715
3716 /* Bug 5344693 : The following update is modified such that
3717 if burden_allowed function returns 'Y' (i.e burden is imported from external transaction source and do not calculate in Projects )
3718 then burdenable raw cost should be 0
3719 else if burden_allowed returns 'N' (i.e burden is calculated in projects )
3720 then calculate the burdenable raw cost. */
3721
3722 Update gms_bc_packets gbc
3723 Set gbc.burdenable_raw_cost =
3724 (select decode(gbc.result_code,'P82',0,
3725 decode(gbc.transaction_source,
3726 null,decode(gae.burden_cost_limit,
3727 null,(gbc.entered_dr-entered_cr),
3728 0,0,gbc.burdenable_raw_cost
3729 )
3730 ,decode(burden_allowed(gbc.transaction_source),
3731 'N',0,
3732 'Y',decode(gae.burden_cost_limit,
3733 null,(gbc.entered_dr-entered_cr),
3734 0,0,gbc.burdenable_raw_cost
3735 )
3736 )
3737 )
3738 )
3739 from gms_allowable_expenditures gae,
3740 gms_awards_all ga
3741 where ga.award_id = gbc.award_id
3742 and gae.allowability_schedule_id = ga.allowable_schedule_id
3743 and gae.expenditure_type = gbc.expenditure_type
3744 )
3745 where packet_id = p_packet_id
3746 and status_code in ('P','I')
3747 and burdenable_raw_cost is NULL;
3748
3749 l_count := SQL%ROWCOUNT;
3750
3751 IF g_debug = 'Y' THEN
3752 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Burdenable raw Cost Updated on :'||l_count||' records','C');
3753 END IF;
3754
3755 ELSIF p_action = g_update_status THEN
3756
3757 IF g_debug = 'Y' THEN
3758 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Result Code update :','C');
3759 END IF;
3760
3761 update gms_bc_packets
3762 set result_code = x_result_code
3763 where packet_id = p_packet_id
3764 and award_id = NVL(p_award_id,award_id)
3765 and expenditure_type = NVL(p_expenditure_type,expenditure_type) ;
3766
3767 l_count := SQL%ROWCOUNT;
3768
3769 IF g_debug = 'Y' THEN
3770 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':result code '||x_result_code||' Updated on :'||l_count||' records','C');
3771 END IF;
3772
3773
3774 If p_full_mode_failure = 'Y' then -- Encumbrance : PO/AP/REQ
3775 Update gms_bc_packets
3776 set status_code = 'R',
3777 result_code = decode(substr(result_code,1,1),'P','F65',result_code)
3778 where packet_id = p_packet_id;
3779
3780 l_count := SQL%ROWCOUNT;
3781
3782 IF g_debug = 'Y' THEN
3783 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':F65 (full mode failure) Updated on :'||l_count||' records','C');
3784 END IF;
3785
3786 END IF;
3787
3788
3789 ELSIF p_action = g_update_bvid THEN
3790
3791 -- Update budget_verison_id on gms_bc_packets. This is required as cursor c_Act
3792 -- checks for gms_bc_packet trasnactions that has a baselined budget only
3793 -- during summarization ...
3794
3795 IF g_debug = 'Y' THEN
3796 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Budget version Id update :','C');
3797 END IF;
3798
3799 Update gms_bc_packets bcp
3800 set bcp.budget_version_id = (select gbv.budget_version_id
3801 from gms_budget_versions gbv
3802 where gbv.award_id = bcp.award_id
3803 and gbv.project_id = bcp.project_id
3804 and gbv.budget_status_code ='B'
3805 and gbv.current_flag = 'Y'
3806 )
3807 where bcp.packet_id = p_packet_id
3808 and bcp.award_id = p_award_id
3809 and bcp.expenditure_type = p_expenditure_type;
3810
3811 Begin
3812
3813 Select 1 into l_count
3814 from dual where exists
3815 (select 1 from gms_bc_packets bcp
3816 where bcp.packet_id = p_packet_id
3817 and bcp.award_id = p_award_id
3818 and bcp.expenditure_type = p_expenditure_type
3819 and bcp.budget_version_id is null);
3820
3821 IF g_debug = 'Y' THEN
3822 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Award,Exp.type:'||p_award_id||';'||p_expenditure_type,'C');
3823 END IF;
3824
3825 x_result_code := 'F';
3826
3827 Update gms_bc_packets
3828 set status_code = decode(p_mode,'C','F','R'),
3829 result_code = 'F12',
3830 fc_error_message = 'Could not derive budget version during burden calculation'
3831 where packet_id = p_packet_id
3832 and award_id = p_award_id
3833 and expenditure_type = p_expenditure_type
3834 and budget_version_id is null;
3835
3836 If p_full_mode_failure = 'Y' then -- Encumbrance : PO/AP/REQ
3837
3838 IF g_debug = 'Y' THEN
3839 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Full mode failure','C');
3840 END IF;
3841
3842 Update gms_bc_packets
3843 set status_code = decode(p_mode,'C','F','R'),
3844 result_code = decode(substr(result_code,1,1),'P','F65',result_code)
3845 where packet_id = p_packet_id;
3846
3847 End If;
3848
3849 Exception
3850 When no_Data_found then
3851 null;
3852 End;
3853
3854 END IF;
3855
3856 COMMIT;
3857
3858 EXCEPTION
3859 when others then
3860 IF g_debug = 'Y' THEN
3861 gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':When Others Exception','C');
3862 END IF;
3863
3864 raise;
3865 END update_bc_pkt_brc_bvid_status;
3866
3867
3868 -- --------------------------------------------------------------
3869 -- Function to calculate and update the burdenable raw cost in GMS_BC_PACKETS
3870 -- All the records for a packet is updated.
3871 -- R12 Funds Managment Uptake : Modified below code to
3872 -- a. shift updates to new autonomous procedure update_bc_pkt_brc_bvid_status
3873 -- b. Added p_partial_flag parameter to fail records in bc packets based on FULL/PARTIAL MODE.
3874 -- --------------------------------------------------------------
3875
3876 FUNCTION update_bc_pkt_burden_raw_cost(x_packet_id IN NUMBER,
3877 p_mode IN VARCHAR2, -- Bug : 2557041 - Added p_mode parameter
3878 p_partial_flag IN VARCHAR2 DEFAULT 'N') return boolean
3879
3880 IS
3881
3882 stat boolean;
3883 X_total NUMBER ;
3884 l_expenditure_type gms_bc_packets.expenditure_type%TYPE ;
3885 l_award_id gms_bc_packets.award_id%TYPE ;
3886 l_header_id NUMBER ;
3887 X_result_code VARCHAR2(3) ;
3888
3889 cursor C_award_exp is
3890 select distinct bcp.award_id, bcp.expenditure_type
3891 from gms_bc_packets bcp,
3892 gms_awards_all ga,
3893 gms_allowable_expenditures gae
3894 where bcp.packet_id = x_packet_id
3895 and status_code IN ('P','I') -- fix for bug : 2927485 ,to reject the transactions that may have already failed a setup step
3896 and bcp.burdenable_raw_cost is NULL
3897 and ga.award_id = bcp.award_id
3898 and gae.allowability_schedule_id = ga.allowable_schedule_id
3899 and gae.burden_cost_limit is not null;
3900
3901 l_dummy number;
3902 l_full_mode_failure varchar2(1) := 'N';
3903 l_result_code gms_bc_packets.result_code%TYPE;
3904
3905 BEGIN
3906
3907 -------------------------------------------------------------------------------+
3908 -- 1. Initalize variables
3909 -------------------------------------------------------------------------------+
3910
3911 g_error_program_name := 'GMS_COST_PLUS_EXTN';
3912 g_error_procedure_name := 'UPDATE_BC_PKT_BURDEN_RAW_COST';
3913 g_debug := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
3914 -- R12 Funds Management Uptake : Intializing global variables
3915 g_update_status := 'UPDATE-STATUS';
3916 g_update_bvid := 'UPDATE-BVID';
3917 g_update_brc := 'UPDATE-BRC';
3918
3919 gms_error_pkg.set_debug_context;
3920
3921 IF g_debug = 'Y' THEN
3922 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
3923 END IF;
3924
3925 SAVEPOINT SAVE_CALC_PROCESS ;
3926 X_err_stage := 'STG_13_STR' ;
3927
3928 -- R12 Funds Management Uptake : Call autonomous procedure to update
3929 update_bc_pkt_brc_bvid_status (p_action => g_update_brc, --'UPDATE-BRC'
3930 p_packet_id => x_packet_id,
3931 x_result_code => l_result_code );
3932
3933 -- Derive g_actual_flag , g_actual_flag and p_mode will determine full mode
3934 -- If any txn. fails in full mode, exit out and fail all transactions
3935 If p_mode in ('U','C','E') then
3936 g_actual_flag := 'E';
3937 Elsif p_mode in ('R') then
3938 Begin
3939 select 'A' into g_actual_flag from dual where exists
3940 (select 1 from gms_bc_packets where packet_id = x_packet_id
3941 and document_type = 'EXP');
3942 Exception
3943 When no_data_found then
3944 g_actual_flag := 'E';
3945 End;
3946 End if;
3947
3948 -- R12 Funds Management Uptake : For AP/PO/REQ records derive l_full_mode_failure
3949 -- based on input parameter p_partial_flag
3950
3951 IF g_actual_flag ='E' AND p_mode <> 'E' AND p_partial_flag = 'N' THEN
3952 l_full_mode_failure := 'Y';
3953 END IF;
3954
3955 FOR C_INDEX in c_award_exp LOOP
3956
3957 l_expenditure_type := C_INDEX.expenditure_type ;
3958 l_award_id := C_INDEX.award_id ;
3959
3960 -- ------------------------------------------------------------------------
3961 -- We need to calculate the award and exptype balance at this point of time
3962 -- based on unposted records and available balance into
3963 -- gms_award_exp_type_act_cost table.
3964 -- -------------------------------------------------------------------------
3965 SAVEPOINT SAVE_CALC_AWARD_EXP ;
3966 BEGIN
3967
3968
3969 X_err_stage := 'STG_142_ST';
3970
3971 -- R12 Funds Management Uptake : Call autonomous procedure to stamp budget_verson_id on bc_packets.
3972 -- This code should be fired before locking summary table
3973 update_bc_pkt_brc_bvid_status ( p_action => g_update_bvid,
3974 p_mode => p_mode,
3975 p_packet_id => x_packet_id,
3976 p_award_id => l_award_id,
3977 p_expenditure_type => l_expenditure_type,
3978 p_full_mode_failure => l_full_mode_failure,
3979 x_result_code => l_result_code );
3980
3981
3982 IF SUBSTR(l_result_code,1,1) = 'F' AND l_full_mode_failure = 'Y' THEN -- Exit the loop if full mode and one of them has failed
3983 EXIT;
3984 ELSIF SUBSTR(l_result_code,1,1) = 'F' THEN -- Skip this record
3985 GOTO SKIP_AWD_EXP;
3986 END IF;
3987
3988 X_err_stage := 'STG_142_ED';
3989
3990 -- Cursor and variable defined at package level ..
3991 Open c_lock_burden_summary(l_award_id,l_expenditure_type);
3992 fetch c_lock_burden_summary into l_lock_burden_summary;
3993 close c_lock_burden_summary;
3994
3995 X_err_stage := 'STG_14_STR' ;
3996 proc_get_award_exp_total( x_packet_id, l_award_id, l_expenditure_type ) ;
3997
3998 -- R12 Funds Management Uptake : Shifted Budget version Id update code before locking
3999
4000 -- Bug : 2557041 - Added p_mode parameter , This parameter is used to
4001 -- restrict creation of burden adjustments in check funds mode
4002
4003 FOR C_rec in bc_packets( x_packet_id, l_award_id,l_expenditure_type ) LOOP
4004
4005 IF c_rec.document_type = 'REQ' THEN
4006 X_err_stage := 'STG_15_STR' ;
4007 stat := calc_req_burden(c_rec,p_mode) ;
4008 X_err_stage := 'STG_15_END' ;
4009 ELSIF c_rec.document_type = 'PO' THEN
4010 X_err_stage := 'STG_16_STR' ;
4011 stat := calc_PO_burden(c_rec,p_mode) ;
4012 X_err_stage := 'STG_16_END' ;
4013 ELSIF c_rec.document_type = 'AP' THEN
4014 X_err_stage := 'STG_17_STR' ;
4015 stat := calc_AP_burden(c_rec,p_mode) ;
4016 X_err_stage := 'STG_17_END' ;
4017 /* ELSIF c_rec.document_type = 'ENC' THEN Commented for bug 5726575
4018 X_err_stage := 'STG_18_STR' ;
4019 stat := calc_ENC_burden(c_rec) ;
4020 X_err_stage := 'STG_18_END' ;*/
4021 ELSIF c_rec.document_type = 'EXP' THEN
4022 X_err_stage := 'STG_19_STR' ;
4023 stat := calc_EXP_burden(c_rec,p_mode) ;
4024 X_err_stage := 'STG_19_END' ;
4025 ELSIF c_rec.document_type = 'FAB' THEN
4026 X_err_stage := 'STG_FB_STR' ;
4027 stat := calc_FAB_burden(c_rec) ;
4028 X_err_stage := 'STG_FB_END' ;
4029 ELSE
4030 X_err_stage := 'STG_20_NUL' ;
4031 NULL ;
4032 END IF ;
4033
4034 if (stat = FALSE) then
4035 EXIT ;
4036 end if;
4037
4038 END LOOP ;
4039
4040 --Bug 5726575
4041 FOR C_rec_enc in bc_packets_enc( x_packet_id, l_award_id,l_expenditure_type ) LOOP
4042 X_err_stage := 'STG_18_STR' ;
4043 stat := calc_ENC_burden(c_rec_enc) ;
4044 X_err_stage := 'STG_18_END' ;
4045 if (stat = FALSE) then
4046 EXIT ;
4047 end if;
4048 END LOOP ;
4049
4050 if (stat = FALSE) then
4051
4052 -- R12 Funds Management Uptake : Call autonomous procedure to update.
4053
4054 l_result_code := 'F49';
4055 update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
4056 p_packet_id => x_packet_id,
4057 p_award_id => l_award_id,
4058 p_expenditure_type => l_expenditure_type,
4059 p_full_mode_failure => 'N',
4060 x_result_code => l_result_code );
4061
4062 end if;
4063
4064 -- COMMIT; -- R12 Funds Management Uptake
4065 -- This commit will undo the lock that was applied on summary table.
4066
4067 EXCEPTION
4068 WHEN RESOURCE_BUSY THEN
4069 -- We couldn't acquire the locks at this time so
4070 -- We need to abort the processing and have the
4071 -- stataus Failed .
4072 -- F40 - Unable to acquire Locks on GMS_AWARD_EXP_TYPE_ACT_COST
4073 -- ------------------------------------------------------------
4074
4075 ROLLBACK to SAVE_CALC_AWARD_EXP ;
4076
4077 -- R12 Funds Management Uptake : Call autonomous procedure to update.
4078 l_result_code := 'F40';
4079 update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
4080 p_packet_id => x_packet_id,
4081 p_award_id => l_award_id,
4082 p_expenditure_type => l_expenditure_type,
4083 p_full_mode_failure => l_full_mode_failure,
4084 x_result_code => l_result_code );
4085
4086 If l_full_mode_failure ='Y' then
4087 EXIT;
4088 End If;
4089
4090 WHEN OTHERS THEN
4091
4092 -- -------------------------------------------
4093 -- Rollback the changes done till this point.
4094 -- -------------------------------------------
4095 ROLLBACK to SAVE_CALC_AWARD_EXP ;
4096
4097 IF X_err_stage = 'STG_14_STR' THEN
4098 -- ---------------------------------
4099 -- Award Exp Type get total failed.
4100 -- ---------------------------------
4101 x_result_code := 'F44' ;
4102 ELSIF X_err_stage = 'STG_15_STR' OR
4103 X_err_stage = 'STG_16_STR' OR
4104 X_err_stage = 'STG_17_STR' OR
4105 X_err_stage = 'STG_18_STR' OR
4106 X_err_stage = 'STG_FB_STR' OR
4107 X_err_stage = 'STG_19_STR' THEN
4108 -- -----------------------------------------------
4109 -- F45 : Burden Calculation failed at award and
4110 -- : expenditure level.
4111 -- -----------------------------------------------
4112 x_result_code := 'F45' ;
4113 ELSIF X_err_stage = 'STG_21_STR' THEN
4114 -- -----------------------------------------------
4115 -- F46 : BUMP UP calculations failed for IDC Limit
4116 -- : Scenarios at award and expenditure level.
4117 -- -----------------------------------------------
4118 x_result_code := 'F46' ;
4119 ELSE
4120 -- -----------------------------------------------
4121 -- F47 : Burden Calculation Failed,
4122 -- : Scenarios at award and expenditure level.
4123 -- -----------------------------------------------
4124 x_result_code := 'F47' ;
4125 END IF ;
4126
4127 -- R12 Funds Management Uptake : Call autonomous procedure to update.
4128 update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
4129 p_packet_id => x_packet_id,
4130 p_award_id => l_award_id,
4131 p_expenditure_type => l_expenditure_type,
4132 p_full_mode_failure => l_full_mode_failure,
4133 x_result_code => x_result_code );
4134
4135 If l_full_mode_failure ='Y' then
4136 EXIT;
4137 End If;
4138
4139 END ;
4140 -- -----------------------------------------------
4141 -- END of award and Expenditure Level Calculations
4142 -- -----------------------------------------------
4143
4144 -- initialize variables ..
4145 x_cmt_tot_burden := 0;
4146 x_tot_raw := 0;
4147 x_tot_burden := 0;
4148
4149 <<SKIP_AWD_EXP>>
4150 -- R12 Funds Management Uptake :Delete the below code as it is handled in new logic
4151 NULL;
4152
4153 END LOOP;
4154
4155 -- Call PROC_RECONCILE_DOCUMENT for maximizing burden
4156 X_err_stage := 'STG_21_STR' ;
4157
4158 IF NOT ( g_actual_flag ='E' AND p_mode <> 'E') THEN -- For AP/PO/REQ records
4159
4160 IF g_debug = 'Y' THEN
4161 gms_error_pkg.gms_debug (g_error_procedure_name||':Call Maximize_burden','C');
4162 END IF;
4163
4164 Maximize_burden(x_packet_id);
4165 --PROC_RECONCILE_DOCUMENT(x_packet_id);
4166
4167 End If;
4168
4169 X_err_stage := 'STG_21_END' ;
4170
4171 IF g_debug = 'Y' THEN
4172 gms_error_pkg.gms_debug (g_error_procedure_name||':End','C');
4173 END IF;
4174
4175 return TRUE;
4176
4177 EXCEPTION
4178 when others then
4179
4180 ROLLBACK TO SAVE_CALC_PROCESS ;
4181
4182 IF x_err_stage = 'STG_10_OTH' THEN
4183 -- --------------------------------------------
4184 -- F41 - AP_PO_RATE_DECREASED System Exception
4185 -- --------------------------------------------
4186 X_result_code := 'F41' ;
4187 ELSIF X_ERR_STAGE = 'STG_11_OTH' THEN
4188 -- --------------------------------------------
4189 -- F42 - Unable to Consolidate AP
4190 -- --------------------------------------------
4191 X_result_code := 'F42' ;
4192 ELSIF X_err_stage = 'STG_12_OTH' THEN
4193 -- --------------------------------------------
4194 -- F43 - Unable to Update PO Document TYPE.
4195 -- --------------------------------------------
4196 X_result_code := 'F43' ;
4197 ELSE
4198 X_result_code := 'F48' ;
4199 END IF ;
4200
4201 -- R12 Funds Management Uptake : Call autonomous procedure to update.
4202 update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
4203 p_packet_id => x_packet_id,
4204 p_award_id => NULL,
4205 p_expenditure_type => NULL,
4206 p_full_mode_failure => 'N',
4207 x_result_code => x_result_code );
4208
4209
4210 RETURN FALSE ;
4211
4212 END update_bc_pkt_burden_raw_cost;
4213 -- ============= update_bc_pkt_burden_raw_cost ====================
4214
4215 PROCEDURE PPAY_LOG_POSTING( x_packet_id in number, x_sob_id IN NUMBER, p_error_stat IN OUT NOCOPY varchar2) IS
4216 BEGIN
4217 null; -- All code remove as this will be taken care by Interface FC
4218 END PPAY_LOG_POSTING ;
4219
4220 PROCEDURE CALC_prepayment_burden( X_AP_REC ap_invoice_distributions_all%ROWTYPE , X_adl_rec gms_award_distributions%ROWTYPE ) is
4221 BEGIN
4222 null; -- All code remove as this will be taken care by Interface FC
4223 END CALC_prepayment_burden ;
4224
4225 -- -------------------------------------------------------------------------+
4226 -- FUNCTION UPDATE_SOURCE_BURDEN_RAW_COST
4227 -- Function updates burdenable raw cost on source document ..
4228 -- -------------------------------------------------------------------------+
4229 FUNCTION UPDATE_SOURCE_BURDEN_RAW_COST(x_packet_id in number, p_mode varchar2, p_partial_flag varchar2) return boolean
4230 IS
4231 l_error varchar2(1000);
4232
4233 Cursor c_packet is
4234 select rowid,document_header_id, document_distribution_id,
4235 expenditure_type,burdenable_raw_cost,document_type,
4236 burden_adjustment_flag,ind_compiled_set_id
4237 from gms_bc_packets
4238 where packet_id = x_packet_id
4239 and status_code IN ('P','I')
4240 and substr(result_code,1,1) = 'P'
4241 and ((nvl(burden_adjustment_flag,'N') = 'N' and parent_bc_packet_id is null)
4242 -- original raw line
4243 OR
4244 (nvl(burden_adjustment_flag,'N') = 'Y' and nvl(burdenable_raw_cost,0) <> 0)
4245 -- Burden adjustment line
4246 )
4247 and document_type <> 'EXP'; -- EXP adls are created during tieback..
4248 --and parent_bc_packet_id IS NULL;
4249 -- parent_bc_packet_id is not null on burden adjustment records
4250
4251 l_stage varchar2(30);
4252 BEGIN
4253 g_error_program_name := 'Gms_cost_plus_extn';
4254 g_error_procedure_name := 'Update_source_burden_raw_cost' ;
4255
4256 gms_error_pkg.gms_debug (g_error_procedure_name||':Start','C');
4257
4258 -- Kept this code in a loop operation as burden posted_flag needs to be updated
4259 -- on records that has been posted to ADL (critical update) ..
4260 -- If update fails, fail packet, packet failed as if any adjsuting line fails then
4261 -- corr. line being FC has to fail and vice versa .. so as to avoid confusion ..
4262 -- fail entire packet ..NOTE: before failing packet we do rollback ..
4263
4264 SAVEPOINT POST_BURDEN;
4265 l_stage := 'Retrieve records';
4266 FOR bc_records in c_packet
4267 LOOP
4268 If bc_records.document_type = 'REQ' then
4269 update gms_award_distributions
4270 set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4271 ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4272 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
4273 where distribution_id = bc_records.document_distribution_id
4274 and adl_status = 'A'
4275 and document_type = 'REQ';
4276
4277 Elsif bc_records. document_type = 'PO' then
4278 update gms_award_distributions
4279 set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4280 ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4281 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
4282 where po_distribution_id = bc_records.document_distribution_id
4283 and adl_status = 'A'
4284 and document_type = 'PO';
4285
4286 Elsif bc_records.document_type = 'AP' then
4287 update gms_award_distributions
4288 set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4289 ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4290 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
4291 where invoice_id = bc_records.document_header_id
4292 and invoice_distribution_id = bc_records.document_distribution_id -- AP Lines change
4293 and adl_status = 'A'
4294 and document_type = 'AP';
4295
4296 Elsif bc_records.document_type = 'ENC' then
4297 update gms_award_distributions
4298 set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
4299 ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
4300 'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
4301 where expenditure_item_id = bc_records.document_header_id
4302 and adl_line_num = bc_records.document_distribution_id --Bug 5726575
4303 and cdl_line_num = 1
4304 and adl_status = 'A'
4305 and document_type = 'ENC';
4306
4307 End If;
4308
4309 IF SQL%FOUND THEN
4310 Update gms_bc_packets
4311 set burden_posted_flag = 'X'
4312 where rowid = bc_records.rowid;
4313 ELSE
4314 ROLLBACK TO POST_BURDEN;
4315 l_stage := 'Failure';
4316 Update gms_bc_packets
4317 set status_code = 'R',
4318 result_code = 'F52'
4319 where rowid = bc_records.rowid;
4320
4321 -- R12 Funds Management Uptake : Update fail status on gms_bc_packets based on Partial/Full mode
4322 IF p_partial_flag = 'N' THEN
4323 EXIT;
4324 END IF;
4325 END IF ;
4326
4327 END LOOP;
4328
4329 gms_error_pkg.gms_debug (g_error_procedure_name||':End','C');
4330
4331 RETURN TRUE;
4332
4333 EXCEPTION
4334 When Others then
4335 l_error:= SUBSTR(SQLERRM,1,1000);
4336
4337 gms_error_pkg.gms_debug ('***********'||g_error_procedure_name||':FAILURE:'||l_error,'C');
4338
4339 ROLLBACK TO POST_BURDEN;
4340
4341 Update gms_bc_packets
4342 set status_code = 'T',
4343 result_code = 'F54',
4344 fc_error_message = l_stage||';'||l_error
4345 where packet_id = x_packet_id;
4346
4347 RETURN FALSE;
4348
4349 END UPDATE_SOURCE_BURDEN_RAW_COST;
4350 --------------------------------------------------------------------------
4351 -- Added for Bug: 1331903
4352 -- Start 3098797, 3103159
4353 -- Description : PAXACMPT: EXACT FETCH RETURNS MORE THAN REQUESTED NUMBER OF ROWS
4354 -- Resolution : Joins with the base tables were added.
4355 --
4356
4357 FUNCTION get_award_compiled_set_id( x_doc_type in VARCHAR2,
4358 x_distribution_id in NUMBER,
4359 x_distribution_line_number in NUMBER default NULL )
4360 RETURN number IS
4361
4362 x_ind_compiled_set_id NUMBER;
4363
4364 BEGIN
4365 if x_doc_type = 'REQ' then
4366
4367 -- 3098797, 3103159
4368 -- Resolution : Joins with the base tables were added.
4369 --
4370 select adl.ind_compiled_set_id
4371 into x_ind_compiled_set_id
4372 from gms_award_distributions adl,
4373 po_req_distributions_all req
4374 where req.distribution_id = x_distribution_id
4375 and req.award_id = adl.award_set_id
4376 and adl.adl_line_num = 1 ;
4377
4378 elsif x_doc_type = 'PO' then
4379
4380 -- 3098797, 3103159
4381 -- Resolution : Joins with the base tables were added.
4382 --
4383 select adl.ind_compiled_set_id
4384 into x_ind_compiled_set_id
4385 from gms_award_distributions adl,
4386 po_distributions_all po
4387 where po.po_distribution_id = x_distribution_id
4388 and po.award_id = adl.award_set_id
4389 and adl.adl_line_num = 1 ;
4390
4391 elsif x_doc_type = 'AP' then
4392
4393 -- 3098797, 3103159
4394 -- Resolution : Joins with the base tables were added.
4395 --
4396 select adl.ind_compiled_set_id
4397 into x_ind_compiled_set_id
4398 from gms_award_distributions adl,
4399 ap_invoice_distributions_all apd
4400 where apd.distribution_line_number = x_distribution_line_number
4401 and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4402 and apd.award_id = adl.award_set_id
4403 and adl.adl_line_num = 1 ;
4404
4405 elsif x_doc_type = 'ENC' then
4406 select ind_compiled_set_id
4407 into x_ind_compiled_set_id
4408 from gms_award_distributions
4409 where expenditure_item_id = x_distribution_id
4410 and adl_status = 'A'
4411 and fc_status = 'A'
4412 and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575
4413 and line_num_reversed is null
4414 and document_type = 'ENC';
4415 end if;
4416
4417 return x_ind_compiled_set_id;
4418
4419 exception
4420 when no_data_found then
4421 return NULL;
4422 end get_award_compiled_set_id;
4423
4424 ---------------------------------------------------------------------------
4425 FUNCTION get_burdenable_raw_cost( x_doc_type in VARCHAR2,
4426 x_distribution_id in NUMBER,
4427 x_distribution_line_number in NUMBER default NULL )
4428 RETURN number IS
4429
4430 x_burdenable_raw_cost NUMBER;
4431
4432 BEGIN
4433
4434 if x_doc_type = 'REQ' then
4435
4436 -- 3098797, 3103159
4437 -- Resolution : Joins with the base tables were added.
4438 --
4439 select adl.burdenable_raw_cost
4440 into x_burdenable_raw_cost
4441 from gms_award_distributions adl,
4442 po_req_distributions_all req
4443 where req.distribution_id = x_distribution_id
4444 and req.award_id = adl.award_set_id
4445 and adl.adl_line_num = 1 ;
4446
4447 elsif x_doc_type = 'PO' then
4448
4449 -- 3098797, 3103159
4450 -- Resolution : Joins with the base tables were added.
4451 --
4452 select adl.burdenable_raw_cost
4453 into x_burdenable_raw_cost
4454 from gms_award_distributions adl,
4455 po_distributions_all po
4456 where po.po_distribution_id = x_distribution_id
4457 and po.award_id = adl.award_set_id
4458 and adl.adl_line_num = 1 ;
4459
4460 elsif x_doc_type = 'AP' then
4461
4462 -- 3098797, 3103159
4463 -- Resolution : Joins with the base tables were added.
4464 --
4465 select adl.burdenable_raw_cost
4466 into x_burdenable_raw_cost
4467 from gms_award_distributions adl,
4468 ap_invoice_distributions_all apd
4469 where apd.distribution_line_number = x_distribution_line_number
4470 and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
4471 and apd.award_id = adl.award_set_id
4472 and adl.adl_line_num = 1 ;
4473
4474 elsif x_doc_type = 'ENC' then
4475 select burdenable_raw_cost
4476 into x_burdenable_raw_cost
4477 from gms_award_distributions
4478 where expenditure_item_id = x_distribution_id
4479 and adl_status = 'A'
4480 and fc_status = 'A'
4481 and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575
4482 and line_num_reversed is null --Bug 5726575
4483 and document_type = 'ENC';
4484
4485 end if;
4486
4487 return x_burdenable_raw_cost;
4488
4489 exception
4490 when no_data_found then
4491 return NULL;
4492 end;
4493
4494 -- Description : PAXACMPT: EXACT FETCH RETURNS MORE THAN REQUESTED NUMBER OF ROWS
4495 -- Resolution : Joins with the base tables were added.
4496 -- End 3098797, 3103159
4497 --
4498
4499 ---------------------------------------------------------------------------
4500 Function is_spon_project(x_project_id IN NUMBER ) RETURN number IS
4501
4502 CURSOR c_project IS
4503 SELECT 'X'
4504 FROM pa_projects p,
4505 gms_project_types gpt
4506 WHERE p.project_id = x_project_id
4507 AND p.project_type = gpt.project_type
4508 AND gpt.sponsored_flag= 'Y' ;
4509
4510 x_dummy varchar2(1) ;
4511 x_return number ;
4512
4513 BEGIN
4514 open c_project ;
4515 fetch c_project into x_dummy ;
4516
4517 IF c_project%FOUND then
4518 x_return := 0 ;
4519 ELSE
4520 x_return := 1 ;
4521 END IF ;
4522
4523 return (x_return ) ;
4524
4525 CLOSE c_project ;
4526 EXCEPTION
4527 WHEN OTHERS THEN
4528 return 1 ;
4529 END is_spon_project ;
4530 --------------------------------------------------------------------------
4531
4532 END GMS_COST_PLUS_EXTN;