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