[Home] [Help]
PACKAGE BODY: APPS.GMS_BUDGET_BALANCE
Source
1 PACKAGE BODY gms_budget_balance AS
2 -- $Header: gmsfcupb.pls 120.11 2007/06/01 06:21:20 jravisha ship $
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 G_PO_QUANTITY_BILLED NUMBER; -- Bug 2721095
8 G_PO_DISTRIBUTION_ID NUMBER; -- Bug 2721095
9
10 Procedure update_gms_balance (x_project_id IN number,
11 x_award_id IN number,
12 x_mode IN varchar2,
13 ERRBUF OUT NOCOPY varchar2,
14 RETCODE OUT NOCOPY varchar2) is
15 -- x_baseline_flag = 'Y' process called to base line a budget
16 -- x_baseline_flag = 'N' process called to update balance
17 x_sob_id number;
18 x_packet_id number;
19 x_budget_version_id number;
20 x_e_code varchar2(1) := null;
21 x_e_stage varchar2(10) := null;
22 x_e_mesg varchar2(2000) := null;
23 x_user_id number;
24 x_user_resp_id number;
25 x_execute varchar2(1) := 'Y';
26 x_partial varchar2(1) := 'N';
27 x_return_code varchar2(3);
28 x_run number := 0;
29 x_fcmode varchar2(1) := 'R';
30 x_over varchar2(1) := 'N';
31 x_budget_total number := 0;
32 x_award_total number := 0;
33 x_temp number;
34 x_err_code number;
35 x_err_buff varchar2(2000);
36 x_fc_required_flag varchar2(1);
37 x_base_bud_version_id number;
38 x_dummy number;
39
40 -- Variables for error handling .
41 g_error_program_name VARCHAR2 ( 30 ) := 'GMS_BUDGET_BALANCE';
42 g_error_procedure_name VARCHAR2 ( 30 );
43 g_error_stage VARCHAR2 ( 30 );
44
45 resource_busy exception;
46 pragma exception_init(resource_busy,-00054);
47 cursor gms_bal_lock is
48 select end_date from gms_balances
49 where budget_version_id = x_budget_version_id
50 for update nowait;
51 ---------------------------------------------------------------------------------------------
52 -- procedure to re-create the gms_balance records from GMS_BUDGET_LINES
53 -- and to clean up GMS_BC_PACKETs
54 -- Added x_project_id and x_award_id parameter : ported Bug 1703510
55
56 procedure create_gms_balance( x_budget_version_id in number, x_set_of_books_id in number,
57 x_project_id in number, x_award_id in number) is
58 begin
59 delete from gms_balances where budget_version_id = x_budget_version_id;
60 --Commented the above line for history purposes 11i change.
61
62 -- Added x_project_id and x_award_id to delete so that it hits N2 index : ported Bug 1703510
63
64 delete from gms_bc_packets
65 where project_id = x_project_id
66 and award_id = x_award_id
67 and budget_version_id = x_budget_version_id;
68
69 -- insert into gms_balances table from gms_budget_lines
70 insert into gms_balances (
71 PROJECT_ID,
72 AWARD_ID,
73 TASK_ID,
74 TOP_TASK_ID,
75 RESOURCE_LIST_MEMBER_ID,
76 BALANCE_TYPE,
77 SET_OF_BOOKS_ID,
78 BUDGET_VERSION_ID,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATED_BY,
82 CREATION_DATE,
83 LAST_UPDATE_LOGIN,
84 PERIOD_NAME,
85 START_DATE,
86 END_DATE,
87 PARENT_MEMBER_ID,
88 BUDGET_PERIOD_TO_DATE)
89 select
90 ga.project_id,
91 gv.award_id,
92 ga.task_id,
93 pt.top_task_id,
94 ga.resource_list_member_id,
95 'BGT',
96 x_set_of_books_id,
97 gv.budget_version_id,
98 sysdate,
99 FND_GLOBAL.USER_ID,
100 FND_GLOBAL.USER_ID,
101 sysdate,
102 FND_GLOBAL.LOGIN_ID,
103 gb.PERIOD_NAME,
104 gb.START_DATE,
105 gb.END_DATE,
106 rm.PARENT_MEMBER_ID,
107 gb.burdened_cost --gb.raw_cost
108 from
109 gms_budget_lines gb,
110 gms_resource_assignments ga,
111 pa_tasks pt,
112 pa_resource_list_members rm,
113 gms_budget_versions gv
114 where gv.budget_version_id = x_budget_version_id
115 and ga.resource_assignment_id = gb.resource_assignment_id
116 and ga.task_id = pt.task_id (+)
117 and ga.budget_version_id = gv.budget_version_id
118 and rm.resource_list_member_id = ga.resource_list_member_id;
119 exception
120 when no_data_found then
121 RETCODE := 'E';
122 ERRBUF := 'NO_DRAFT_BUDGET_BUDGET';
123 end create_gms_balance;
124 -----------------------------------------------------------------------------
125 --Procedure to load all the raw transactions in GMS_BC_PACKETS for funds check
126 procedure create_direct_cost(x_packet_id IN number,
127 x_sob_id in number,
128 x_project_id in number,
129 x_award_id in number,
130 x_budget_version_id in number) IS
131 x_err_code number;
132 x_err_buff varchar2(2000);
133 BEGIN
134 begin
135 -- ---------------------------------------------------------------
136 -- TO INSERT Commitments (Requisitions) Bug 2009836
137 -- ---------------------------------------------------------------
138 --
139 -- Bug : 3362016 Grants integrations with CWK and PO Services.
140 -- sub select added in the from clause to use PO encumbered amount api.
141 insert into gms_bc_packets (
142 packet_id,
143 set_of_books_id,
144 je_source_name,
145 je_category_name,
146 actual_flag,
147 period_name,
148 period_year,
149 period_num,
150 project_id,
151 task_id,
152 award_id,
153 status_code,
154 last_update_date,
155 last_updated_by,
156 created_by,
157 creation_date,
158 last_update_login,
159 entered_dr,
160 entered_cr,
161 expenditure_type,
162 expenditure_organization_id,
163 expenditure_item_date,
164 document_type,
165 document_header_id,
166 document_distribution_id,
167 transfered_flag,
168 account_type,
169 budget_version_id,
170 bc_packet_id,
171 burdenable_raw_cost,
172 vendor_id, -- Bug 2069132 ( RLMI Change)
173 expenditure_category, -- Bug 2069132 ( RLMI Change)
174 revenue_category, -- Bug 2069132 ( RLMI Change)
175 ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
176 )
177 select
178 x_packet_id,
179 x_sob_id,
180 'Purchasing',
181 'Requisitions',
182 'E',
183 vw.period_name,
184 vw.period_year,
185 vw.period_num,
186 vw.project_id,
187 vw.task_id,
188 vw.award_id ,
189 'P',
190 sysdate,
191 fnd_global.user_id,
192 fnd_global.user_id,
193 sysdate,
194 fnd_global.login_id,
195 decode(sign(vw.amount), 1, vw.amount, 0),
196 decode(sign(vw.amount), -1,vw.amount, 0),
197 vw.expenditure_type,
198 vw.expenditure_organization_id,
199 vw.expenditure_item_date,
200 'REQ',
201 vw.requisition_header_id,
202 vw.distribution_id,
203 'Y',
204 'E',
205 x_budget_version_id,
206 gms_bc_packets_s.nextval,
207 vw.burdenable_raw_cost,
208 vw.vendor_id,
209 vw.expenditure_category,
210 vw.revenue_category_code,
211 vw.ind_compiled_set_id
212 FROM
213 ( select gps.period_name,
214 gps.period_year,
215 gps.period_num,
216 adl.project_id,
217 adl.task_id,
218 adl.award_id ,
219 PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func
220 ('REQUISITION', RD.DISTRIBUTION_ID) amount,
221 rd.expenditure_type,
222 rd.expenditure_organization_id,
223 trunc(rd.expenditure_item_date) expenditure_item_date,
224 rd.distribution_id,
225 adl.burdenable_raw_cost,
226 pet.expenditure_category,
227 pet.revenue_category_code,
228 adl.ind_compiled_set_id,
229 rd.requisition_line_id,
230 rh.requisition_header_id,
231 rl.vendor_id
232 from po_req_distributions_all rd,
233 gms_award_distributions adl,
234 po_requisition_lines_all rl,
235 po_requisition_headers_all rh,
236 gl_period_statuses gps,
237 pa_expenditure_types pet
238 WHERE RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
239 AND RH.TYPE_LOOKUP_CODE = 'PURCHASE'
240 AND NVL(RL.MODIFIED_BY_AGENT_FLAG,'N') = 'N'
241 AND RL.SOURCE_TYPE_CODE = 'VENDOR'
242 AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
243 AND RD.ENCUMBERED_FLAG = 'Y'
244 AND ADL.PROJECT_ID = X_PROJECT_ID
245 AND ADL.AWARD_ID = X_AWARD_Id
246 AND ADL.DISTRIBUTION_ID = RD.DISTRIBUTION_ID
247 AND ADL.ADL_STATUS = 'A'
248 AND ADL.DOCUMENT_TYPE = 'REQ'
249 AND NVL(ADL.FC_STATUS,'N') = 'A'
250 AND RD.PROJECT_ID = ADL.PROJECT_ID
251 AND RD.TASK_ID = ADL.TASK_ID
252 AND RD.AWARD_ID = ADL.AWARD_SET_ID
253 AND RD.EXPENDITURE_ITEM_DATE BETWEEN GPS.START_DATE
254 AND GPS.END_DATE
255 AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
256 AND GPS.APPLICATION_ID = 101
257 AND GPS.SET_OF_BOOKS_ID = X_SOB_ID
258 AND pet.expenditure_type = rd.expenditure_type
259 ) VW
260 WHERE nvl(VW.amount,0) <> 0 ;
261 -- ---------------------------------------------------------------
262 -- TO INSERT Commitments (Purchase Order) Bug 2009836
263 -- ---------------------------------------------------------------
264 --
265 -- Bug : 3362016 Grants integrations with CWK and PO Services.
266 -- sub select added in the from clause to use PO encumbered amount api.
267 insert into gms_bc_packets (
268 packet_id,
269 set_of_books_id,
270 je_source_name,
271 je_category_name,
272 actual_flag,
273 period_name,
274 period_year,
275 period_num,
276 project_id,
277 task_id,
278 award_id,
279 status_code,
280 last_update_date,
281 last_updated_by,
282 created_by,
283 creation_date,
284 last_update_login,
285 entered_dr,
286 entered_cr,
287 expenditure_type,
288 expenditure_organization_id,
289 expenditure_item_date,
290 document_type,
291 document_header_id,
292 document_distribution_id,
293 transfered_flag,
294 account_type,
295 budget_version_id,
296 bc_packet_id,
297 burdenable_raw_cost,
298 vendor_id, -- Bug 2069132 ( RLMI Change)
299 expenditure_category, -- Bug 2069132 ( RLMI Change)
300 revenue_category, -- Bug 2069132 ( RLMI Change)
301 ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
302 )
303 select
304 x_packet_id,
305 x_sob_id,
306 'Purchasing',
307 'Purchases',
308 'E',
309 vw.period_name,
310 vw.period_year,
311 vw.period_num,
312 vw.project_id,
313 vw.task_id,
314 vw.award_id,
315 'P',
316 sysdate,
317 fnd_global.user_id,
318 fnd_global.user_id,
319 sysdate,
320 fnd_global.login_id,
321 vw.amount,
322 0, -- Entered_Cr
323 vw.expenditure_type,
324 vw.expenditure_organization_id,
325 vw.expenditure_item_date,
326 'PO',
327 vw.po_header_id,
328 vw.po_distribution_id,
329 'Y',
330 'E',
331 x_budget_version_id,
332 gms_bc_packets_s.nextval,
333 vw.burdenable_raw_cost,
334 vw.vendor_id, -- Bug 2069132 ( RLMI Change)
335 vw.expenditure_category, -- Bug 2069132 ( RLMI Change)
336 vw.revenue_category_code, -- Bug 2069132 ( RLMI Change)
337 vw.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
338 FROM (select
339 gps.period_name,
340 gps.period_year,
341 gps.period_num,
342 adl.project_id,
343 adl.task_id,
344 adl.award_id,
345 PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func
346 ('PO', pod.po_DISTRIBUTION_ID) amount,
347 pod.expenditure_type,
348 pod.expenditure_organization_id,
349 trunc(pod.expenditure_item_date) expenditure_item_date,
350 pod.po_header_id,
351 pod.po_distribution_id,
352 adl.burdenable_raw_cost,
353 poh.vendor_id,
354 pet.expenditure_category,
355 pet.revenue_category_code,
356 adl.ind_compiled_set_id
357 FROM
358 po_headers_all poh,
359 po_lines_all pol,
360 po_line_locations_all pll,
361 po_releases_all por,
362 po_distributions_all pod,
363 gms_award_distributions adl,
364 gl_period_statuses gps,
365 pa_expenditure_types pet
366 WHERE POH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','PLANNED')
367 AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
368 AND POL.PO_LINE_ID = PLL.PO_LINE_ID
369 AND PLL.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED','PLANNED')
370 AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
371 AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
372 AND PO_INTG_DOCUMENT_FUNDS_GRP.get_active_encumbrance_func /*Bug 6085276 */
373 ('PO', pod.po_DISTRIBUTION_ID) <> 0
374 AND NVL(POH.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' /* 6085276 */
375 AND NVL(pll.closed_code,'OPEN') <> 'FINALLY CLOSED' /* 6085276 */
376 /*AND POD.ENCUMBERED_FLAG = 'Y' Commented for bug 6085276 */
377 AND POD.PROJECT_ID = X_PROJECT_ID
378 AND POD.DISTRIBUTION_TYPE <> 'PREPAYMENT' -- Complex work/subcontractor uptake
379 AND ADL.AWARD_ID = X_AWARD_Id
380 AND ADL.PROJECT_ID = POD.PROJECT_ID
381 AND ADL.PO_DISTRIBUTION_ID= POD.PO_DISTRIBUTION_ID
382 AND ADL.TASK_ID = POD.TASK_ID
383 AND POD.AWARD_ID = ADL.AWARD_SET_ID
384 AND ADL.ADL_STATUS = 'A'
385 AND ADL.DOCUMENT_TYPE = 'PO'
386 AND NVL(ADL.FC_STATUS,'N')= 'A'
387 AND POD.EXPENDITURE_ITEM_DATE BETWEEN GPS.START_DATE
388 AND GPS.END_DATE
389 AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
390 AND GPS.APPLICATION_ID = 101
391 AND GPS.SET_OF_BOOKS_ID = X_SOB_ID
392 AND pet.expenditure_type = pod.expenditure_type ) VW
393 WHERE NVL(VW.amount,0) <> 0 ;
394
395 -- ---------------------------------------------------------------
396 -- TO INSERT Commitments (AP)
397 -- ---------------------------------------------------------------
398 -- ---------------------------------------------------------------
399 -- Bug Fix 2170878. Removed invoice_distribution_id join.In some
400 -- scenarios id is null on ad
401 -- ---------------------------------------------------------------
402 insert into gms_bc_packets (
403 packet_id,
404 set_of_books_id,
405 je_source_name,
406 je_category_name,
407 actual_flag,
408 period_name,
409 period_year,
410 period_num,
411 project_id,
412 task_id,
413 award_id,
414 status_code,
415 last_update_date,
416 last_updated_by,
417 created_by,
418 creation_date,
419 last_update_login,
420 entered_dr,
421 entered_cr,
422 expenditure_type,
423 expenditure_organization_id,
424 expenditure_item_date,
425 document_type,
426 document_header_id,
427 document_distribution_id,
428 TRANSFERED_FLAG,
429 account_type,
430 budget_version_id,
431 bc_packet_id,
432 burdenable_raw_cost,
433 vendor_id, -- Bug 2069132 ( RLMI Change)
434 expenditure_category, -- Bug 2069132 ( RLMI Change)
435 revenue_category, -- Bug 2069132 ( RLMI Change)
436 ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
437 )
438 select
439 x_packet_id,
440 x_sob_id,
441 'Payables', -- Bug 2603943
442 'Purchase Invoices',
443 'E',
444 gps.period_name,
445 gps.period_year,
446 gps.period_num,
447 adl.project_id,
448 adl.task_id,
449 adl.award_id,
450 'P',
451 sysdate,
452 FND_GLOBAL.USER_ID,
453 FND_GLOBAL.USER_ID,
454 sysdate,
455 FND_GLOBAL.LOGIN_ID,
456 -- Added below NVL clause as the base_amount stores correct amount in multi currency scenario
457 -- Bug 1980810 PA Rounding function added
458 pa_currency.round_currency_amt(decode(sign(pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
459 aid.invoice_id,
460 nvl(aid.base_amount,aid.amount),
461 'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N'))),
462 1, pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
463 aid.invoice_id,
464 nvl(aid.base_amount,aid.amount),
465 'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) ,
466 0)), -- Bug 2386531
467 pa_currency.round_currency_amt(decode(sign(pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
468 aid.invoice_id,
469 nvl(aid.base_amount,aid.amount),
470 'N', 'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') )),
471 -1,abs( pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
472 aid.invoice_id,
473 nvl(aid.base_amount,aid.amount),
474 'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') )),
475 0)),--Bug 2386531
476 aid.expenditure_type,
477 aid.expenditure_organization_id,
478 aid.expenditure_item_date,
479 'AP',
480 aid.invoice_id,
481 aid.invoice_distribution_id, -- AP Lines change
482 'Y',
483 'E',
484 x_budget_version_id,
485 gms_bc_packets_s.nextval,
486 adl.burdenable_raw_cost,
487 ap.vendor_id,
488 pet.expenditure_category,
489 pet.revenue_category_code,
490 adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
491 from ap_invoices_all ap,
492 gms_award_distributions adl,
493 ap_invoice_distributions aid,
494 gl_period_statuses gps,
495 pa_expenditure_types pet,
496 gl_ledgers g
497 where ap.invoice_id = aid.invoice_id
498 and aid.invoice_distribution_id = adl.invoice_distribution_id -- AP Lines change
499 and aid.invoice_id = adl.invoice_id
500 and adl.document_type = 'AP'
501 and adl.award_set_id = aid.award_id
502 and adl.adl_status = 'A'
503 and nvl(adl.fc_status,'N') = 'A'
504 and nvl(aid.pa_addition_flag,'N') = 'N'
505 and aid.expenditure_item_date
506 between gps.start_date and gps.end_date
507 and gps.adjustment_period_flag = 'N'
508 and gps.application_id = 101
509 and gps.set_of_books_id = x_sob_id
510 and adl.project_id = x_project_id
511 and adl.award_id = x_award_id
512 and pa_cmt_utils.get_apdist_amt( aid.invoice_distribution_id,
513 aid.invoice_id,
514 nvl(aid.base_amount,aid.amount),
515 'N', 'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
516 and nvl(aid.match_status_flag, 'X') = 'A'
517 and pet.expenditure_type = aid.expenditure_type
518 and g.ledger_id = aid.set_of_books_id ;
519
520 /* Commented out for bug 3661740. pa_addition_flag check should preclude items
521 that are interfaced to PA.
522 and not exists (select 'X'
523 from pa_cost_distribution_lines_all cdl
524 where cdl.system_reference2 = to_char(aid.invoice_id)
525 and cdl.system_reference3 = to_char(aid.distribution_line_number))
526 */
527
528 exception
529 when no_data_found then
530 null;
531 when others then
532 raise;
533 end;
534
535 -- -------------------------------------------------------------------------------------------------------
536 -- Bug 3283448 : Removed the earlier code from here, The following statement will handle all the scenarios
537 -- The following insert staement should pick up following Scenarios :
538 -- Transactions interfaced from AP (these expenditures will have fc_status = 'A'
539 -- Expenditures having Funds check passed CDL which failed funds checking during Re-costing
540 -- As we are checking for fc_status on ADL , these lines will be picked up
541 -- -------------------------------------------------------------------------------------------------------
542
543 begin
544 -- ---------------------------------------------------------------
545 -- TO INSERT Expenditures and Encumberances
546 -- ---------------------------------------------------------------
547 insert into gms_bc_packets (
548 PACKET_ID,
549 PROJECT_ID,
550 AWARD_ID,
551 TASK_ID,
552 EXPENDITURE_TYPE,
553 EXPENDITURE_ITEM_DATE,
554 ACTUAL_FLAG,
555 STATUS_CODE,
556 LAST_UPDATE_DATE,
557 LAST_UPDATED_BY,
558 CREATED_BY,
559 CREATION_DATE,
560 LAST_UPDATE_LOGIN,
561 SET_OF_BOOKS_ID,
562 JE_CATEGORY_NAME,
563 JE_SOURCE_NAME,
564 TRANSFERED_FLAG,
565 DOCUMENT_TYPE,
566 EXPENDITURE_ORGANIZATION_ID,
567 PERIOD_NAME,
568 PERIOD_YEAR,
569 PERIOD_NUM,
570 DOCUMENT_HEADER_ID,
571 DOCUMENT_DISTRIBUTION_ID,
572 ACCOUNT_TYPE,
573 ENTERED_DR,
574 ENTERED_CR,
575 BUDGET_VERSION_ID,
576 bc_packet_id,
577 burdenable_raw_cost,
578 person_id, -- Bug 2069132 ( RLMI Change)
579 job_id, -- Bug 2069132 ( RLMI Change)
580 vendor_id, -- Bug 2069132 ( RLMI Change)
581 expenditure_category, -- Bug 2069132 ( RLMI Change)
582 revenue_category, -- Bug 2069132 ( RLMI Change)
583 ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
584 )
585 select
586 x_packet_id,
587 --task.project_id, -- commented for porting Bug:1703510
588 pc.project_id, -- added for above bug
589 adl.award_id,
590 pe.task_id,
591 pe.EXPENDITURE_TYPE,
592 trunc(pe.EXPENDITURE_ITEM_DATE),
593 'A',
594 'P',
595 sysdate,
596 FND_GLOBAL.USER_ID,
597 FND_GLOBAL.USER_ID,
598 sysdate,
599 FND_GLOBAL.LOGIN_ID,
600 x_sob_id,
601 DECODE(pe.system_linkage_function,'OT','Labor Cost',
602 'ST','Labor Cost',
603 'ER','Purchase Invoices',
604 'VI','Purchase Invoices',
605 'USG','Usage Cost',
606 'PJ','Miscellaneous Transaction',
607 'INV','Inventory',
608 'WIP','WIP'), -- Bug 2461450 : Replaced 'Expenditures' with DECODE statement
609 'Project Accounting',
610 'Y',
611 'EXP', -- for document_type
612 nvl(pe.override_to_organization_id,pa.incurred_by_organization_id),
613 gl.PERIOD_NAME,
614 gl.PERIOD_YEAR,
615 gl.PERIOD_NUM,
616 pc.expenditure_item_id,
617 pc.line_num,
618 'E',
619 decode(sign(pc.amount),1,pc.amount,0),
620 decode(sign(pc.amount),-1,ABS(pc.amount),0),
621 x_budget_version_id,
622 gms_bc_packets_s.nextval,
623 adl.burdenable_raw_cost,
624 pa.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
625 pe.job_id, -- Bug 2069132 ( RLMI Change)
626 pc.system_reference1, -- Bug 2069132 ( RLMI Change)
627 pet.expenditure_category, -- Bug 2069132 ( RLMI Change)
628 pet.revenue_category_code, -- Bug 2069132 ( RLMI Change)
629 adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
630 /* Changed the order of queries and
631 removed join with tables
632 gl_date_period_map map,
633 gl_sets_of_books glsob and pa_implementations imp for bug# 6043224 */
634 FROM GMS_AWARD_DISTRIBUTIONS ADL,
635 PA_COST_DISTRIBUTION_LINES_ALL PC,
636 GL_PERIOD_STATUSES GL,
637 PA_EXPENDITURE_ITEMS_ALL PE,
638 PA_EXPENDITURES_ALL PA,
639 PA_EXPENDITURE_TYPES PET
640 WHERE ADL.PROJECT_ID = x_project_id
641 AND ADL.AWARD_ID = x_award_id
642 AND ADL.ADL_STATUS = 'A'
643 AND NVL(ADL.FC_STATUS,'N') = 'A'
644 AND ADL.DOCUMENT_TYPE = 'EXP'
645 AND pc.expenditure_item_id = adl.expenditure_item_id
646 and pc.line_num = adl.cdl_line_num
647 AND PC.LINE_TYPE = 'R'
648 AND NVL(PC.AMOUNT,0) <> 0
649 AND GL.APPLICATION_ID = 101
650 AND GL.SET_OF_BOOKS_ID = x_sob_id
651 AND GL.ADJUSTMENT_PERIOD_FLAG = 'N'
652 AND PC.GL_DATE BETWEEN GL.START_DATE AND GL.END_DATE
653 AND PE.EXPENDITURE_ITEM_ID = PC.EXPENDITURE_ITEM_ID
654 AND PE.EXPENDITURE_ITEM_ID = ADL.EXPENDITURE_ITEM_ID
655 AND PA.EXPENDITURE_ID = PE.EXPENDITURE_ID
656 AND PET.EXPENDITURE_TYPE = PE.EXPENDITURE_TYPE;
657 /* Commented for Bug 6043224
658 from pa_expenditure_items_all pe,
659 pa_expenditures_all pa,
660 pa_cost_distribution_lines_all pc,
661 gl_period_STATUSES gl,
662 -- pa_tasks task, -- commented for Bug:1703510
663 gms_award_distributions adl,
664 --pa_periods pp, -- Bug 2887849, EPP changes
665 pa_expenditure_types pet, -- Bug 2069132 (RLMI Change)
666 -- Added the joins with the tables for Bug 5569067
667 gl_date_period_map map,
668 gl_sets_of_books glsob,
669 pa_implementations imp
670 where adl.project_id = x_project_id -- Bug 2387678
671 -- where pc.project_id = x_project_id -- added for porting Bug:1703510
672 --where task.project_id = x_project_id -- commented for above bug
673 --and pe.task_id = task.task_id -- commented for above bug
674 and adl.award_id = x_award_id
675 -- and pe.task_id = adl.task_id -- Bug 2387678
676 and adl.adl_status = 'A'
677 and nvl(adl.fc_status,'N') = 'A'
678 and adl.document_type = 'EXP'
679 and pa.expenditure_id = pe.expenditure_id
680 -- and pp.end_date = pc.pa_date + 0
681 --and pp.end_date = pc.pa_date -- Bug 2887849, EPP changes
682 --and pp.gl_period_name = gl.period_name -- Bug 2887849, EPP changes
683 and pc.gl_date between gl.start_date and gl.end_date -- Bug 2887849, EPP changes
684 and gl.application_id = 101
685 and gl.set_of_books_id = x_sob_id
686 and gl.adjustment_period_flag = 'N' ---> bug 3201867
687 and nvl(pc.amount,0) <> 0 -- filter burden transactions
688 and pe.expenditure_item_id = pc.expenditure_item_id
689 and pe.expenditure_item_id = adl.expenditure_item_id
690 and pc.line_num = adl.cdl_line_num
691 and pc.line_type = 'R'
692 --and pc.reversed_flag is null
693 --and pc.line_num_reversed is null
694 -- 2337127 ( Budget Baseline should insert all the cdls.
695 -- that has passed fundscheck previously..
696 -- and pe.cost_distributed_flag = 'Y' -- Bug 3283448 : Only check for fc_status , if fc_status = 'A' we should pick the record
697 and pet.expenditure_type = pe.expenditure_type -- Bug 2069132 (RLMI Change)
698 -- Added the following conditions for bug 5569067
699 AND map.period_set_name = glsob.Period_set_name
700 AND map.period_type = glsob.accounted_period_type
701 AND imp.org_id = pe.org_id
702 AND glsob.set_of_books_id = imp.set_of_books_id
703 AND map.accounting_date = trunc(pc.gl_date) -- Modified from pe.expenditure_item_date to pc.gl_date for the bug 5725787
704 Added trunc in the above condition for bug5960821
705 AND gl.period_name= map.period_name; Commented for Bug 6043224 */
706
707 exception
708 when no_data_found then
709 null;
710 when others then
711 raise;
712 end;
713
714 -- ---------------------------------------------------------------------------------------------
715 -- Encumbrance Insert for Baseline
716 -- ---------------------------------------------------------------------------------------------
717
718 -- ---------------------------------------------------------------------------------------------
719 -- Bug Fix 2170878. Encumbrance insert should have condition of adl.document_type = 'ENC' as
720 -- both encumbrance_item_id and expenditure_item_id is stored in expenditure_item_id in adl table
721 -- ---------------------------------------------------------------------------------------------
722
723 begin
724
725 insert into gms_bc_packets (
726 PACKET_ID,
727 PROJECT_ID,
728 AWARD_ID,
729 TASK_ID,
730 EXPENDITURE_TYPE,
731 EXPENDITURE_ITEM_DATE,
732 ACTUAL_FLAG,
733 STATUS_CODE,
734 LAST_UPDATE_DATE,
735 LAST_UPDATED_BY,
736 CREATED_BY,
737 CREATION_DATE,
738 LAST_UPDATE_LOGIN,
739 SET_OF_BOOKS_ID,
740 JE_CATEGORY_NAME,
741 JE_SOURCE_NAME,
742 TRANSFERED_FLAG,
743 DOCUMENT_TYPE,
744 EXPENDITURE_ORGANIZATION_ID,
745 PERIOD_NAME,
746 PERIOD_YEAR,
747 PERIOD_NUM,
748 DOCUMENT_HEADER_ID,
749 DOCUMENT_DISTRIBUTION_ID,
750 ACCOUNT_TYPE,
751 ENTERED_DR,
752 ENTERED_CR,
753 BUDGET_VERSION_ID,
754 bc_packet_id,
755 burdenable_raw_cost,
756 person_id, -- Bug 2069132 ( RLMI Change)
757 job_id, -- Bug 2069132 ( RLMI Change)
758 expenditure_category, -- Bug 2069132 ( RLMI Change)
759 revenue_category, -- Bug 2069132 ( RLMI Change)
760 ind_compiled_set_id -- Bug 2387678 ( Performance Tuning )
761 )
762 select
763 x_packet_id,
764 --task.project_id, -- commented out NOCOPY for porting bug:1703510
765 adl.project_id, -- added for the above bug
766 adl.award_id,
767 gei.task_id,
768 gei.encumbrance_type,
769 trunc(gei.encumbrance_item_date),
770 'E',
771 'P',
772 sysdate,
773 FND_GLOBAL.USER_ID,
774 FND_GLOBAL.USER_ID,
775 sysdate,
776 FND_GLOBAL.LOGIN_ID,
777 x_sob_id,
778 'Encumbrances', -- Bug 2461450
779 'Project Accounting',
780 'Y',
781 'ENC', -- for document_type
782 nvl(gei.override_to_organization_id,ge.incurred_by_organization_id),
783 gl.PERIOD_NAME,
784 gl.PERIOD_YEAR,
785 gl.PERIOD_NUM,
786 gei.encumbrance_item_id,
787 adl.adl_line_num, --Bug 5726575 1,
788 'E',
789 -- Bug 1980810 PA Rounding function added
790 pa_currency.round_currency_amt(decode(sign(gei.amount),1,gei.amount,0)),
791 pa_currency.round_currency_amt(decode(sign(gei.amount),-1,-1*gei.amount,0)),
792 x_budget_version_id,
793 gms_bc_packets_s.nextval,
794 adl.burdenable_raw_cost,
795 ge.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
796 gei.job_id, -- Bug 2069132 ( RLMI Change)
797 pet.expenditure_category, -- Bug 2069132 ( RLMI Change)
798 pet.revenue_category_code, -- Bug 2069132 ( RLMI Change)
799 adl.ind_compiled_set_id -- Bug 2387678 (Performance Tuning)
800 from gms_encumbrance_items_all gei,
801 gms_encumbrances_all ge,
802 gl_period_STATUSES gl,
803 --pa_tasks task, -- commented out NOCOPY for porting bug:1703510
804 gms_award_distributions adl,
805 pa_expenditure_types pet -- Bug 2069132 (RLMI Change)
806 -- where task.project_id = x_project_id -- commented out NOCOPY for porting bug:1703510
807 -- and gei.task_id = task.task_id -- commented out NOCOPY for porting bug:1703510
808 where adl.project_id = x_project_id -- added for the above bug
809 and adl.award_id = x_award_id
810 -- and adl.project_id = gei.project_id -- commented out NOCOPY for Bug: 1666853
811 and adl.task_id = gei.task_id
812 and adl.adl_status = 'A'
813 and nvl(adl.fc_status,'N') = 'A'
814 and adl.document_type = 'ENC'
815 and ge.encumbrance_id = gei.encumbrance_id
816 -- and pp.end_date = gei.pa_date -- commented out NOCOPY for Bug: 1666853
817 and gei.encumbrance_item_date between gl.start_date and gl.end_date -- added for Bug: 1666853
818 -- and pp.gl_period_name = gl.period_name -- commented out NOCOPY for Bug: 1666853
819 and gl.application_id = 101
820 and gl.set_of_books_id = x_sob_id
821 and gl.adjustment_period_flag = 'N' ---> bug 3201867
822 and gei.encumbrance_item_id = adl.expenditure_item_id
823 and gei.enc_distributed_flag = 'Y'
824 and nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
825 and adl.line_num_reversed is null --Bug 5726575
826 and pet.expenditure_type = gei.encumbrance_type; -- Bug 2069132 (RLMI Change)
827
828 exception
829 when no_data_found then
830 null;
831 when others then
832 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
833 'SQLCODE',
834 SQLCODE,
835 'SQLERRM',
836 SQLERRM,
837 X_Exec_Type => 'C',
838 X_Err_Code => X_Err_Code,
839 X_Err_Buff => X_Err_Buff);
840 raise;
841 end;
842 END create_direct_cost;
843 ---------------------------------------------------------------------------------------------
844 -- Procedure to create indirect cost lines in GMS_BC_PACKETS from the raw cost lines
845 -- in GMS_BC_PACKETS for a given packet;
846 procedure create_indirect_cost(x_packet_id IN number) IS
847 x_err_code number;
848 x_err_buff varchar2(2000);
849 begin
850 begin
851 Insert into gms_bc_packets
852 ( PACKET_ID,
853 PROJECT_ID,
854 AWARD_ID,
855 TASK_ID,
856 EXPENDITURE_TYPE,
857 EXPENDITURE_ITEM_DATE,
858 ACTUAL_FLAG,
859 STATUS_CODE,
860 LAST_UPDATE_DATE,
861 LAST_UPDATED_BY,
862 CREATED_BY,
863 CREATION_DATE,
864 LAST_UPDATE_LOGIN,
865 SET_OF_BOOKS_ID,
866 JE_CATEGORY_NAME,
867 JE_SOURCE_NAME,
868 TRANSFERED_FLAG,
869 DOCUMENT_TYPE,
870 EXPENDITURE_ORGANIZATION_ID,
871 PERIOD_NAME,
872 PERIOD_YEAR,
873 PERIOD_NUM,
874 DOCUMENT_HEADER_ID ,
875 DOCUMENT_DISTRIBUTION_ID,
876 TOP_TASK_ID,
877 BUDGET_VERSION_ID,
878 RESOURCE_LIST_MEMBER_ID,
879 ACCOUNT_TYPE,
880 ENTERED_DR,
881 ENTERED_CR ,
882 TOLERANCE_AMOUNT,
883 TOLERANCE_PERCENTAGE,
884 OVERRIDE_AMOUNT,
885 EFFECT_ON_FUNDS_CODE ,
886 RESULT_CODE,
887 GL_BC_PACKETS_ROWID,
888 BC_PACKET_ID,
889 PARENT_BC_PACKET_ID,
890 person_id, -- Bug 2069132 ( RLMI Change)
891 job_id, -- Bug 2069132 ( RLMI Change)
892 vendor_id, -- Bug 2069132 ( RLMI Change)
893 expenditure_category, -- Bug 2069132 ( RLMI Change)
894 revenue_category -- Bug 2069132 ( RLMI Change)
895 )
896 select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
897 gbc.PACKET_ID,
898 gbc.PROJECT_ID,
899 gbc.AWARD_ID,
900 gbc.TASK_ID,
901 icc.EXPENDITURE_TYPE, /* for performance fix bug 5569067 */ /* Bug 5676410 */
902 trunc(gbc.EXPENDITURE_ITEM_DATE),
903 gbc.ACTUAL_FLAG,
904 gbc.STATUS_CODE,
905 gbc.LAST_UPDATE_DATE,
906 gbc.LAST_UPDATED_BY,
907 gbc.CREATED_BY,
908 gbc.CREATION_DATE,
909 gbc.LAST_UPDATE_LOGIN,
910 gbc.SET_OF_BOOKS_ID,
911 gbc.JE_CATEGORY_NAME,
912 gbc.JE_SOURCE_NAME,
913 gbc.TRANSFERED_FLAG,
914 gbc.DOCUMENT_TYPE,
915 gbc.EXPENDITURE_ORGANIZATION_ID,
916 gbc.PERIOD_NAME,
917 gbc.PERIOD_YEAR,
918 gbc.PERIOD_NUM,
919 gbc.DOCUMENT_HEADER_ID ,
920 gbc.DOCUMENT_DISTRIBUTION_ID,
921 gbc.TOP_TASK_ID,
922 gbc.BUDGET_VERSION_ID,
923 gbc.RESOURCE_LIST_MEMBER_ID,
924 gbc.ACCOUNT_TYPE,
925 -- Bug 1980810 PA Rounding function added
926 pa_currency.round_currency_amt(sign(nvl(entered_dr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
927 pa_currency.round_currency_amt(sign(nvl(entered_cr,0)) * abs(nvl(gbc.BURDENABLE_RAW_COST ,0) * nvl(cm.compiled_multiplier,0))),
928 gbc.TOLERANCE_AMOUNT,
929 gbc.TOLERANCE_PERCENTAGE,
930 gbc.OVERRIDE_AMOUNT,
931 gbc.EFFECT_ON_FUNDS_CODE ,
932 gbc.RESULT_CODE,
933 gbc.GL_BC_PACKETS_ROWID,
934 gms_bc_packets_s.nextval,
935 gbc.BC_PACKET_ID,
936 gbc.person_id, -- Bug 2069132 ( RLMI Change)
937 gbc.job_id, -- Bug 2069132 ( RLMI Change)
938 gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
939 et.expenditure_category, -- Bug 2069132 ( RLMI Change)
940 et.revenue_category_code -- Bug 2069132 ( RLMI Change)
941 from /*pa_ind_rate_sch_revisions irsr, --for performance fix bug 5569067 */
942 --pa_cost_bases cb, --Bug 3630704 : Performance fix
943 pa_expenditure_types et,
944 pa_ind_cost_codes icc, /* Bug 5676410 */
945 pa_cost_base_exp_types cbet,
946 --pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
947 pa_cost_base_cost_codes cbcc, /*for performance fix bug 5569067 */
948 /*pa_ind_compiled_sets ics, --for performance fix bug 5569067 */
949 pa_compiled_multipliers cm,
950 gms_bc_packets gbc
951 where gbc.document_type in ('REQ','PO','AP', 'ENC') -- perf bug 4005086. included 'ENC' here
952 and cbcc.cost_plus_structure = cbet.cost_plus_structure
953 /*and irsr.cost_plus_structure = cbet.cost_plus_structure bug 5569067 */
954 --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
955 --and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
956 and et.expenditure_type = icc.expenditure_type /* Bug 5676410 */
957 and cbcc.cost_base = cbet.cost_base /*for performance fix bug 5569067 */
958 /*and ics.cost_base = cbet.cost_base -- Bug 3003584 */
959 and icc.ind_cost_code = cm.ind_cost_code /* Bug 5676410 */
960 and cbet.cost_base = cm.cost_base
961 and cm.cost_base_cost_code_id = cbcc.cost_base_cost_code_id /*--for performance fix bug 5569067*/
962 and cm.ind_cost_code = cbcc.ind_cost_code /*--for performance fix bug 5569067*/
963 and cbet.cost_base_type = 'INDIRECT COST'
964 and cbet.expenditure_type = gbc.expenditure_type
965 --and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
966 /*and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id --for performance fix bug 5569067
967 and ics.ind_compiled_set_id = gbc.ind_compiled_set_id -- Replaced the above clause with this for Bug:2387678 --for performance fix bug 5569067
968 and ics.organization_id = gbc.expenditure_organization_id */
969 and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
970 and cm.compiled_multiplier <> 0
971 and gbc.packet_id = x_packet_id;
972
973 exception
974 when no_data_found then
975 null;
976 when others then
977 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
978 'SQLCODE',
979 SQLCODE,
980 'SQLERRM',
981 SQLERRM,
982 X_Exec_Type => 'C',
983 X_Err_Code => X_Err_Code,
984 X_Err_Buff => X_Err_Buff);
985 raise;
986 end;
987 Begin
988 Insert into gms_bc_packets
989 ( PACKET_ID,
990 PROJECT_ID,
991 AWARD_ID,
992 TASK_ID,
993 EXPENDITURE_TYPE,
994 EXPENDITURE_ITEM_DATE,
995 ACTUAL_FLAG,
996 STATUS_CODE,
997 LAST_UPDATE_DATE,
998 LAST_UPDATED_BY,
999 CREATED_BY,
1000 CREATION_DATE,
1001 LAST_UPDATE_LOGIN,
1002 SET_OF_BOOKS_ID,
1003 JE_CATEGORY_NAME,
1004 JE_SOURCE_NAME,
1005 TRANSFERED_FLAG,
1006 DOCUMENT_TYPE,
1007 EXPENDITURE_ORGANIZATION_ID,
1008 PERIOD_NAME,
1009 PERIOD_YEAR,
1010 PERIOD_NUM,
1011 DOCUMENT_HEADER_ID ,
1012 DOCUMENT_DISTRIBUTION_ID,
1013 TOP_TASK_ID,
1014 BUDGET_VERSION_ID,
1015 RESOURCE_LIST_MEMBER_ID,
1016 ACCOUNT_TYPE,
1017 ENTERED_DR,
1018 ENTERED_CR ,
1019 TOLERANCE_AMOUNT,
1020 TOLERANCE_PERCENTAGE,
1021 OVERRIDE_AMOUNT,
1022 EFFECT_ON_FUNDS_CODE ,
1023 RESULT_CODE,
1024 GL_BC_PACKETS_ROWID,
1025 BC_PACKET_ID,
1026 PARENT_BC_PACKET_ID,
1027 person_id, -- Bug 2069132 ( RLMI Change)
1028 job_id, -- Bug 2069132 ( RLMI Change)
1029 vendor_id, -- Bug 2069132 ( RLMI Change)
1030 expenditure_category, -- Bug 2069132 ( RLMI Change)
1031 revenue_category -- Bug 2069132 ( RLMI Change)
1032 )
1033 select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
1034 gbc.PACKET_ID,
1035 gbc.PROJECT_ID,
1036 gbc.AWARD_ID,
1037 gbc.TASK_ID,
1038 icc.EXPENDITURE_TYPE,
1039 trunc(gbc.EXPENDITURE_ITEM_DATE),
1040 gbc.ACTUAL_FLAG,
1041 gbc.STATUS_CODE,
1042 gbc.LAST_UPDATE_DATE,
1043 gbc.LAST_UPDATED_BY,
1044 gbc.CREATED_BY,
1045 gbc.CREATION_DATE,
1046 gbc.LAST_UPDATE_LOGIN,
1047 gbc.SET_OF_BOOKS_ID,
1048 gbc.JE_CATEGORY_NAME,
1049 gbc.JE_SOURCE_NAME,
1050 gbc.TRANSFERED_FLAG,
1051 gbc.DOCUMENT_TYPE,
1052 gbc.EXPENDITURE_ORGANIZATION_ID,
1053 gbc.PERIOD_NAME,
1054 gbc.PERIOD_YEAR,
1055 gbc.PERIOD_NUM,
1056 gbc.DOCUMENT_HEADER_ID ,
1057 gbc.DOCUMENT_DISTRIBUTION_ID,
1058 gbc.TOP_TASK_ID,
1059 gbc.BUDGET_VERSION_ID,
1060 gbc.RESOURCE_LIST_MEMBER_ID,
1061 gbc.ACCOUNT_TYPE,
1062 -- Bug 1980810 PA Rounding function added
1063 pa_currency.round_currency_amt(decode(nvl(entered_dr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1064 pa_currency.round_currency_amt(decode(nvl(entered_cr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1065 gbc.TOLERANCE_AMOUNT,
1066 gbc.TOLERANCE_PERCENTAGE,
1067 gbc.OVERRIDE_AMOUNT,
1068 gbc.EFFECT_ON_FUNDS_CODE ,
1069 gbc.RESULT_CODE,
1070 gbc.GL_BC_PACKETS_ROWID,
1071 gms_bc_packets_s.nextval,
1072 gbc.BC_PACKET_ID,
1073 gbc.person_id, -- Bug 2069132 ( RLMI Change)
1074 gbc.job_id, -- Bug 2069132 ( RLMI Change)
1075 gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
1076 et.expenditure_category, -- Bug 2069132 ( RLMI Change)
1077 et.revenue_category_code -- Bug 2069132 ( RLMI Change)
1078 from --pa_ind_rate_sch_revisions irsr, /* Commented for bug 5689194 */
1079 --pa_cost_bases cb, --Bug 3630704 : Performance fix
1080 pa_expenditure_types et,
1081 pa_ind_cost_codes icc,
1082 pa_cost_base_exp_types cbet,
1083 --pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
1084 pa_cost_base_cost_codes cbcc , /* added for bug 5689194 */
1085 --pa_ind_compiled_sets ics, /* commented for bug 5689194 */
1086 pa_compiled_multipliers cm,
1087 pa_expenditure_items_all ei, --Bug Fix 1482377
1088 pa_transaction_sources pts, --Bug Fix 1482377
1089 gms_bc_packets gbc
1090 where gbc.document_type = 'EXP'
1091 and cbcc.cost_plus_structure = cbet.cost_plus_structure -- Bug 5689194
1092 --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
1093 --and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
1094 --and ics.cost_base = cbet.cost_base -- 3003584 Bug 5689194
1095 and cbcc.cost_base = cbet.cost_base -- Bug 5689194
1096 and et.expenditure_type = icc.expenditure_type
1097 and icc.ind_cost_code = cm.ind_cost_code
1098 and cbet.cost_base = cm.cost_base
1099 and cbet.cost_base_type = 'INDIRECT COST'
1100 and cm.cost_base_cost_code_id = cbcc.cost_base_cost_code_id --Bug 5689194
1101 and cm.ind_cost_code = cbcc.ind_cost_code --Bug 5689194
1102 and cbet.expenditure_type = gbc.expenditure_type
1103 --and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
1104 --and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id Bug 5689194
1105 and gbc.document_type = 'EXP'
1106 --and ics.ind_compiled_set_id = gbc.ind_compiled_set_id Bug 5689194
1107 and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
1108 --and ics.organization_id = gbc.expenditure_organization_id Bug 5689194
1109 and cm.compiled_multiplier <> 0
1110 and ei.expenditure_item_id = gbc.document_header_id --Bug Fix 1482377
1111 and (ei.transaction_source = pts.transaction_source (+) --Bug Fix 1482377
1112 and nvl(pts.allow_burden_flag,'N') = 'N') --Bug Fix 1815635
1113 and gbc.packet_id = x_packet_id;
1114
1115 exception
1116 when no_data_found then
1117 null;
1118 when others then
1119 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1120 'SQLCODE',
1121 SQLCODE,
1122 'SQLERRM',
1123 SQLERRM,
1124 X_Exec_Type => 'C',
1125 X_Err_Code => X_Err_Code,
1126 X_Err_Buff => X_Err_Buff);
1127 raise;
1128 end ;
1129
1130 /***** Perf bug 4005086 .. included 'ENC' along with 'PO', 'AP', 'REQ'...
1131
1132 -- ------------
1133 -- Encumbrances
1134 -- ------------
1135 begin
1136 Insert into gms_bc_packets
1137 ( PACKET_ID,
1138 PROJECT_ID,
1139 AWARD_ID,
1140 TASK_ID,
1141 EXPENDITURE_TYPE,
1142 EXPENDITURE_ITEM_DATE,
1143 ACTUAL_FLAG,
1144 STATUS_CODE,
1145 LAST_UPDATE_DATE,
1146 LAST_UPDATED_BY,
1147 CREATED_BY,
1148 CREATION_DATE,
1149 LAST_UPDATE_LOGIN,
1150 SET_OF_BOOKS_ID,
1151 JE_CATEGORY_NAME,
1152 JE_SOURCE_NAME,
1153 TRANSFERED_FLAG,
1154 DOCUMENT_TYPE,
1155 EXPENDITURE_ORGANIZATION_ID,
1156 PERIOD_NAME,
1157 PERIOD_YEAR,
1158 PERIOD_NUM,
1159 DOCUMENT_HEADER_ID ,
1160 DOCUMENT_DISTRIBUTION_ID,
1161 TOP_TASK_ID,
1162 BUDGET_VERSION_ID,
1163 RESOURCE_LIST_MEMBER_ID,
1164 ACCOUNT_TYPE,
1165 ENTERED_DR,
1166 ENTERED_CR ,
1167 TOLERANCE_AMOUNT,
1168 TOLERANCE_PERCENTAGE,
1169 OVERRIDE_AMOUNT,
1170 EFFECT_ON_FUNDS_CODE ,
1171 RESULT_CODE,
1172 GL_BC_PACKETS_ROWID,
1173 BC_PACKET_ID,
1174 PARENT_BC_PACKET_ID,
1175 person_id, -- Bug 2069132 ( RLMI Change)
1176 job_id, -- Bug 2069132 ( RLMI Change)
1177 vendor_id, -- Bug 2069132 ( RLMI Change)
1178 expenditure_category, -- Bug 2069132 ( RLMI Change)
1179 revenue_category -- Bug 2069132 ( RLMI Change)
1180 )
1181 select
1182 gbc.PACKET_ID,
1183 gbc.PROJECT_ID,
1184 gbc.AWARD_ID,
1185 gbc.TASK_ID,
1186 icc.EXPENDITURE_TYPE,
1187 trunc(gbc.EXPENDITURE_ITEM_DATE),
1188 gbc.ACTUAL_FLAG,
1189 gbc.STATUS_CODE,
1190 gbc.LAST_UPDATE_DATE,
1191 gbc.LAST_UPDATED_BY,
1192 gbc.CREATED_BY,
1193 gbc.CREATION_DATE,
1194 gbc.LAST_UPDATE_LOGIN,
1195 gbc.SET_OF_BOOKS_ID,
1196 gbc.JE_CATEGORY_NAME,
1197 gbc.JE_SOURCE_NAME,
1198 gbc.TRANSFERED_FLAG,
1199 gbc.DOCUMENT_TYPE,
1200 gbc.EXPENDITURE_ORGANIZATION_ID,
1201 gbc.PERIOD_NAME,
1202 gbc.PERIOD_YEAR,
1203 gbc.PERIOD_NUM,
1204 gbc.DOCUMENT_HEADER_ID ,
1205 gbc.DOCUMENT_DISTRIBUTION_ID,
1206 gbc.TOP_TASK_ID,
1207 gbc.BUDGET_VERSION_ID,
1208 gbc.RESOURCE_LIST_MEMBER_ID,
1209 gbc.ACCOUNT_TYPE,
1210 -- Bug 1980810 PA Rounding function added
1211 pa_currency.round_currency_amt(decode(nvl(entered_dr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1212 pa_currency.round_currency_amt(decode(nvl(entered_cr,0),0,0,(abs(nvl(gbc.BURDENABLE_RAW_COST ,0)) * nvl(cm.compiled_multiplier,0)))),
1213 gbc.TOLERANCE_AMOUNT,
1214 gbc.TOLERANCE_PERCENTAGE,
1215 gbc.OVERRIDE_AMOUNT,
1216 gbc.EFFECT_ON_FUNDS_CODE ,
1217 gbc.RESULT_CODE,
1218 gbc.GL_BC_PACKETS_ROWID,
1219 gms_bc_packets_s.nextval,
1220 gbc.BC_PACKET_ID,
1221 gbc.person_id, -- Bug 2069132 ( RLMI Change)
1222 gbc.job_id, -- Bug 2069132 ( RLMI Change)
1223 gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
1224 et.expenditure_category, -- Bug 2069132 ( RLMI Change)
1225 et.revenue_category_code -- Bug 2069132 ( RLMI Change)
1226 from pa_ind_rate_sch_revisions irsr,
1227 --pa_cost_bases cb, --Bug 3630704 : Performance fix
1228 pa_expenditure_types et,
1229 pa_ind_cost_codes icc,
1230 pa_cost_base_exp_types cbet,
1231 --pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
1232 pa_ind_compiled_sets ics,
1233 pa_compiled_multipliers cm,
1234 gms_bc_packets gbc
1235 where irsr.cost_plus_structure = cbet.cost_plus_structure
1236 --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
1237 --and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
1238 and ics.cost_base = cbet.cost_base --Bug 3003584
1239 and et.expenditure_type = icc.expenditure_type
1240 and icc.ind_cost_code = cm.ind_cost_code
1241 and cbet.cost_base = cm.cost_base
1242 and cbet.cost_base_type = 'INDIRECT COST'
1243 and cbet.expenditure_type = gbc.expenditure_type
1244 --and irs.ind_rate_sch_id = irsr.ind_rate_sch_id --Bug 3630704 : Performance fix
1245 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1246 and gbc.document_type = 'ENC'
1247 and ics.ind_compiled_set_id = gbc.ind_compiled_set_id -- Replaced the above clause with this for Bug:2387678
1248 and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
1249 and ics.organization_id = gbc.expenditure_organization_id
1250 and cm.compiled_multiplier <> 0 -- Fix for Bug 806481
1251 and gbc.packet_id = x_packet_id;
1252
1253 exception
1254 when no_data_found then
1255 null;
1256 when others then
1257 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1258 'SQLCODE',
1259 SQLCODE,
1260 'SQLERRM',
1261 SQLERRM,
1262 X_Exec_Type => 'C',
1263 X_Err_Code => X_Err_Code,
1264 X_Err_Buff => X_Err_Buff);
1265 raise;
1266 end ;
1267 *********************************/
1268 end create_indirect_cost;
1269
1270 --------------------------------------------------------------------------------------------------------------------------------------------------
1271 -- This Module sets up the denormalized columns in the queue such as
1272 -- Budgetary Control Options, Funds Check Level, Account Type, Transaction
1273 -- effect on Funds Available, etc. Generating resource_list_member_id for the packet based on the resource list
1274 -- and expenditure_type
1275 --------------------------------------------------------------------------------------------------------------------------------------------------
1276
1277 Procedure re_base_setup_rlmi( x_packet_id IN number,
1278 x_budget_version_id IN number,
1279 x_err_code OUT NOCOPY number,
1280 x_err_buff OUT NOCOPY varchar2) IS
1281
1282 x_resource_list_member_id number;
1283 x_project_id number ;
1284 x_award_id number;
1285 x_task_id number;
1286 --x_budget_version_id number;
1287 x_gms_rowid varchar2(30);
1288 x_res_list_id number;
1289 x_organization_id number ;
1290 x_job_id number;
1291 x_vendor_id number;
1292 x_expenditure_type varchar2(30);
1293 x_exptype varchar2(30); -- Additional parameter for map trans
1294 x_non_labor_resource varchar2(20);
1295 x_expenditure_category varchar2(30);
1296 x_revenue_category varchar2(30);
1297 x_non_labor_resource_org_id number;
1298 x_system_linkage varchar2(30);
1299 x_document_type varchar2(3);
1300 x_person_id number;
1301 x_awd_id number ;
1302 x_parent_id number; -- Additional parameter for map trans
1303 x_err_stack varchar2(2000);
1304 x_er_code varchar2(1) := null;
1305 x_er_stage varchar2(2000) := null;
1306 x_e_code number;
1307 x_bc_packet_id number;
1308 x_fcl varchar2(1);
1309 x_bc_option_id number(15);
1310 x_categorization_code varchar2(1);
1311 l_budget_version_id gms_bc_packets.budget_version_id%TYPE;
1312 l_effect_on_funds_code varchar2(1);
1313 x_group_by_none varchar2(60) ;
1314
1315 -- ------------------------------------------------------------------------
1316 -- If resource list is setup without resource groups and the
1317 -- resources are setup as expenditure categories, Funds check
1318 -- fail due to a resource mapping error.
1319 -- ------------------------------------------------------------------------
1320
1321
1322 CURSOR C_group_by_none_cat is
1323 SELECT 'X'
1324 FROM pa_child_resources_v a,
1325 pa_expenditure_types b,
1326 pa_resource_lists c
1327 WHERE a.resource_list_id = x_res_list_id
1328 AND a.resource_type_name = 'Expenditure Category'
1329 AND a.resource_list_id = c.resource_list_id
1330 AND c.group_resource_type_id = 0
1331 AND a.resource_name = b.expenditure_category
1332 AND b.expenditure_type = x_expenditure_type
1333 AND NVL(a.migration_code,'M') ='M'; -- Bug 3626671;
1334
1335
1336 Cursor cur_update_col is
1337 select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
1338 gms.project_id,
1339 gms.award_id,
1340 gms.task_id,
1341 gms.expenditure_organization_id,
1342 gms.expenditure_type,
1343 gms.document_type,
1344 nvl(ei.system_linkage_function,'VI'),
1345 TYPE.expenditure_category,
1346 TYPE.revenue_category_code,
1347 gms.award_id,
1348 gms.parent_bc_packet_id,
1349 pm.categorization_code, -- to calculate the correct rlmi if budget without resource
1350 decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
1351 from gms_bc_packets gms,
1352 gms_budget_versions bv,
1353 pa_budget_entry_methods pm,
1354 pa_expenditure_types TYPE,
1355 gms_encumbrance_items_all ei
1356 where gms.packet_id = x_packet_id
1357 and gms.budget_version_id = bv.budget_version_id
1358 and gms.document_type = 'ENC'
1359 and bv.budget_entry_method_code = pm.budget_entry_method_code
1360 and gms.expenditure_type = TYPE.expenditure_type
1361 and gms.document_header_id = ei.encumbrance_item_id
1362 union all
1363 select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
1364 gms.project_id,
1365 gms.award_id,
1366 gms.task_id,
1367 gms.expenditure_organization_id,
1368 gms.expenditure_type,
1369 gms.document_type,
1370 nvl(ei.system_linkage_function,'VI'),
1371 TYPE.expenditure_category,
1372 TYPE.revenue_category_code,
1373 gms.award_id,
1374 gms.parent_bc_packet_id,
1375 pm.categorization_code, -- to calculate the correct rlmi if budget without resource
1376 decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
1377 from gms_bc_packets gms,
1378 gms_budget_versions bv,
1379 pa_budget_entry_methods pm,
1380 pa_expenditure_types TYPE,
1381 pa_expenditure_items_all ei
1382 where gms.packet_id = x_packet_id
1383 and gms.budget_version_id = bv.budget_version_id
1384 and gms.document_type = 'EXP'
1385 and bv.budget_entry_method_code = pm.budget_entry_method_code
1386 and gms.expenditure_type = TYPE.expenditure_type
1387 and gms.document_header_id = ei.expenditure_item_id(+);
1388
1389 BEGIN
1390 IF L_DEBUG = 'Y' THEN
1391 gms_error_pkg.gms_debug('BEGIN SETUP RLMI','C');
1392 END IF;
1393 --('*********** IN GMS_FST PROCESS **************************************');
1394 if x_mode <> 'U' then
1395
1396 -- Update denormalized columns for all detail transactions in the packet
1397 -- open cur_update_col;
1398
1399 /* --- Procedure opens the cursor --- */
1400 OPEN cur_update_col;
1401 LOOP
1402 fetch cur_update_col into x_bc_packet_id,
1403 x_project_id,
1404 x_award_id,
1405 x_task_id,
1406 x_organization_id,
1407 x_expenditure_type,
1408 x_document_type,
1409 x_system_linkage,
1410 x_expenditure_category,
1411 x_revenue_category,
1412 x_awd_id,
1413 x_parent_id,
1414 x_categorization_code,
1415 l_effect_on_funds_code;
1416 exit when cur_update_col%notfound;
1417 Begin
1418
1419 if x_categorization_code = 'R' then -- if not categorized by resources then no call to map trans to generate rlmi
1420
1421 Begin
1422 select bv.resource_list_id into x_res_list_id
1423 from gms_budget_versions bv
1424 where bv.budget_version_id = x_budget_version_id;
1425 Exception
1426 when others then
1427 raise;
1428 End;
1429
1430
1431 -- INPUT PARAMETERS FOR RESOURCE MAPPING
1432 IF ( x_system_linkage = 'VI'
1433 AND x_document_type = 'REQ' ) THEN
1434 g_error_stage := 'VI-REQ';
1435 SELECT DISTINCT line.vendor_id
1436 INTO x_vendor_id
1437 FROM po_requisition_lines line,
1438 po_requisition_headers req,
1439 gms_bc_packets bc
1440 WHERE bc.packet_id = x_packet_id
1441 AND line.requisition_header_id = req.requisition_header_id
1442 AND bc.document_header_id = req.requisition_header_id
1443 AND bc.bc_packet_id = x_bc_packet_id;
1444 ELSIF x_system_linkage = 'VI'
1445 AND x_document_type = 'PO' THEN
1446 g_error_stage := 'VI-PO';
1447 SELECT DISTINCT head.vendor_id
1448 INTO x_vendor_id
1449 FROM po_headers_all head,
1450 gms_bc_packets bc
1451 WHERE bc.packet_id = x_packet_id
1452 AND bc.document_header_id = head.po_header_id
1453 AND bc.bc_packet_id = x_bc_packet_id;
1454 ELSIF x_system_linkage = 'VI'
1455 AND x_document_type = 'AP' THEN
1456 g_error_stage := 'VI-AP';
1457 SELECT DISTINCT head.vendor_id
1458 INTO x_vendor_id
1459 FROM ap_invoices_all head,
1460 gms_bc_packets bc
1461 WHERE bc.packet_id = x_packet_id
1462 AND bc.document_header_id = head.invoice_id
1463 AND bc.bc_packet_id = x_bc_packet_id;
1464 ELSIF ( x_system_linkage = 'ER'
1465 OR x_system_linkage = 'ST'
1466 OR x_system_linkage = 'OT' ) THEN
1467 IF x_document_type = 'EXP' THEN
1468 g_error_stage := 'ER/ST/OT-EXP';
1469 SELECT DISTINCT EXP.incurred_by_person_id,
1470 item.job_id
1471 INTO x_person_id,
1472 x_job_id
1473 FROM pa_expenditures_all exp,
1474 pa_expenditure_items_all item,
1475 gms_bc_packets bc
1476 WHERE bc.packet_id = x_packet_id
1477 AND bc.bc_packet_id = x_bc_packet_id
1478 AND bc.document_header_id = item.expenditure_item_id
1479 AND item.expenditure_id = EXP.expenditure_id;
1480 ELSIF x_document_type = 'ENC' THEN
1481 g_error_stage := 'ER/ST/OT-ENC';
1482 SELECT DISTINCT enc.incurred_by_person_id,
1483 item.job_id
1484 INTO x_person_id,
1485 x_job_id
1486 FROM gms_encumbrances_all enc,
1487 gms_encumbrance_items_all item,
1488 gms_bc_packets bc
1489 WHERE bc.packet_id = x_packet_id
1490 AND bc.bc_packet_id = x_bc_packet_id
1491 AND bc.document_header_id = item.encumbrance_item_id
1492 AND item.encumbrance_id = enc.encumbrance_id;
1493 END IF;
1494 ELSIF x_system_linkage = 'USG' THEN
1495 IF x_document_type IN ( 'AP', 'PO', 'REQ' ) THEN
1496 g_error_stage := 'USG-REQ/PO/AP';
1497 SELECT DISTINCT tp.attribute2,
1498 tp.attribute3
1499 INTO x_non_labor_resource,
1500 x_non_labor_resource_org_id
1501 FROM pa_expenditure_types tp,
1502 gms_bc_packets bc
1503 WHERE bc.packet_id = x_packet_id
1504 AND bc.bc_packet_id = x_bc_packet_id
1505 AND tp.expenditure_type = bc.expenditure_type;
1506 ELSIF x_document_type = 'EXP' THEN
1507 g_error_stage := 'USG-EXP';
1508 SELECT DISTINCT EXP.incurred_by_person_id,
1509 item.job_id,
1510 item.non_labor_resource,
1511 item.organization_id
1512 INTO x_person_id,
1513 x_job_id,
1514 x_non_labor_resource,
1515 x_non_labor_resource_org_id
1516 FROM pa_expenditures_all exp,
1517 pa_expenditure_items_all item,
1518 gms_bc_packets bc
1519 WHERE bc.packet_id = x_packet_id
1520 AND bc.bc_packet_id = x_bc_packet_id
1521 AND bc.document_header_id = item.expenditure_item_id
1522 AND item.expenditure_id = EXP.expenditure_id;
1523 END IF;
1524 END IF;
1525
1526 -- ------------------------------------------------------------------------
1527 -- BUG:1370475 - If resource list is setup without resource groups and the
1528 -- resources are setup as expenditure categories, Funds check
1529 -- fail due to a resource mapping error.
1530 -- ------------------------------------------------------------------------
1531
1532 g_error_stage := 'Resource-Exp Category';
1533 OPEN c_group_by_none_cat;
1534 FETCH c_group_by_none_cat INTO x_group_by_none;
1535
1536 IF c_group_by_none_cat%FOUND THEN
1537 x_expenditure_type := NULL;
1538 x_exptype := NULL;
1539 ELSE
1540 IF x_parent_id is not null then
1541 x_exptype := NULL;
1542 ELSE
1543 x_exptype := x_expenditure_type;
1544 END IF;
1545 CLOSE c_group_by_none_cat;
1546 END IF;
1547
1548 --('PARAMETERS FOR RESOURCE MAPPING PROCESS **************************************');
1549 --gms_error_pkg.gms_debug('map trans :x_project_id >>>>>>'||to_char(x_project_id),'C');
1550 --gms_error_pkg.gms_debug('map trans :x_res_list_id>>>>>>'||to_char(x_res_list_id),'C');
1551 --gms_error_pkg.gms_debug('map trans :x_person_id>>>>>>'||to_char(x_person_id),'C');
1552 --gms_error_pkg.gms_debug('map trans :x_organization_id>>>>>>'||to_char(x_organization_id),'C');
1553 --gms_error_pkg.gms_debug('map trans :x_expenditure_type>>>>>>'||x_expenditure_type,'C');
1554 --gms_error_pkg.gms_debug('map trans :x_non_labor_resource>>>>>>'||x_non_labor_resource,'C');
1555 --gms_error_pkg.gms_debug('map trans :x_expenditure_category>>>>>>'||x_expenditure_category,'C');
1556 --gms_error_pkg.gms_debug('map trans :x_non_labor_resource_org_id>>>>>>'||to_char(x_non_labor_resource_org_id),'C');
1557 --gms_error_pkg.gms_debug('map trans :x_system_linkage>>>>>>'||x_system_linkage,'C');
1558 --gms_error_pkg.gms_debug('map trans :x_resource_list_member_id>>>Before resource mapping is >>>
1559 -- '|| to_char(x_resource_list_member_id),'C');
1560 g_error_stage := 'Resource Map';
1561 gms_res_map.map_trans ( x_project_id,
1562 x_res_list_id,
1563 x_person_id,
1564 x_job_id,
1565 x_organization_id,
1566 x_vendor_id,
1567 x_expenditure_type,
1568 NULL,
1569 x_non_labor_resource,
1570 x_expenditure_category,
1571 x_revenue_category,
1572 x_non_labor_resource_org_id,
1573 NULL, -- x_event_type_classification
1574 x_system_linkage,
1575 x_exptype,
1576 x_resource_list_member_id,
1577 x_er_stage,
1578 x_e_code);
1579 IF L_DEBUG = 'Y' THEN
1580 gms_error_pkg.gms_debug ( 'map trans :x_resource_list_member_id >>>>>>' || x_resource_list_member_id,
1581 'C' );
1582 END IF;
1583
1584 IF x_e_code > 0
1585 OR x_resource_list_member_id IS NULL THEN
1586 gms_error_pkg.gms_message ( 'GMS_MAP_TRANS',
1587 'BC_PACKET_ID',
1588 x_bc_packet_id,
1589 'PACKET_ID',
1590 x_packet_id,
1591 x_exec_type => 'C',
1592 x_err_code => x_err_code,
1593 x_err_buff => x_err_buff );
1594
1595 UPDATE gms_bc_packets
1596 SET status_code = 'R',
1597 result_code = 'F94',
1598 res_result_code = 'F94',
1599 res_grp_result_code = 'F94',
1600 task_result_code = 'F94',
1601 top_task_result_code = 'F94',
1602 award_result_code = 'F94'
1603 WHERE packet_id = x_packet_id
1604 AND bc_packet_id = x_bc_packet_id;
1605 ELSE
1606 ----------------------------------------------------------
1607 --To update effect on funds code,resource list member id
1608 -- for each record in a packet, if categorized by resource.
1609 ----------------------------------------------------------
1610
1611 UPDATE gms_bc_packets
1612 SET resource_list_member_id = x_resource_list_member_id,
1613 effect_on_funds_code = l_effect_on_funds_code
1614 WHERE packet_id = x_packet_id
1615 AND bc_packet_id = x_bc_packet_id
1616 AND budget_version_id = x_budget_version_id;
1617 END IF;
1618
1619 x_job_id := NULL;
1620 x_vendor_id := NULL;
1621 x_non_labor_resource := NULL;
1622 x_non_labor_resource_org_id := NULL;
1623 x_person_id := NULL;
1624 ELSE
1625 g_error_stage := 'Categorized<>R';
1626 SELECT resource_list_member_id
1627 INTO x_resource_list_member_id
1628 FROM gms_balances gb
1629 WHERE gb.budget_version_id = x_budget_version_id
1630 AND balance_type = 'BGT'
1631 AND ROWNUM = 1;
1632 IF L_DEBUG = 'Y' THEN
1633 gms_error_pkg.gms_debug ( 'Not Categorized by Resource :x_resource_list_member_id >>>>>>' || x_resource_list_member_id,
1634 'C' );
1635 END IF;
1636
1637 ----------------------------------------------------------
1638 --To update effect on funds code,resource list member id
1639 -- for each record in a packet, if not categorized by resource.
1640 ----------------------------------------------------------
1641 IF x_resource_list_member_id IS NULL THEN
1642 UPDATE gms_bc_packets
1643 SET status_code = 'R',
1644 result_code = 'F94',
1645 res_result_code = 'F94',
1646 res_grp_result_code = 'F94',
1647 task_result_code = 'F94',
1648 top_task_result_code = 'F94',
1649 award_result_code = 'F94'
1650 WHERE packet_id = x_packet_id
1651 AND bc_packet_id = x_bc_packet_id;
1652
1653 x_err_buff := x_er_stage;
1654 --('After Resource Mapping Process');
1655 ELSE
1656 UPDATE gms_bc_packets
1657 SET resource_list_member_id = x_resource_list_member_id,
1658 effect_on_funds_code = l_effect_on_funds_code
1659 WHERE packet_id = x_packet_id
1660 AND bc_packet_id = x_bc_packet_id
1661 AND budget_version_id = x_budget_version_id;
1662 END IF;
1663 END IF;
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 gms_error_pkg.gms_message ( x_err_name=> 'GMS_UNEXPECTED_ERROR',
1667 x_token_name1 => 'PROGRAM_NAME',
1668 x_token_val1 => g_error_program_name || '.' || g_error_procedure_name || '.' || g_error_stage,
1669 x_token_name2 => 'SQLCODE',
1670 x_token_val2 => SQLCODE,
1671 x_token_name3 => 'SQLERRM',
1672 x_token_val3 => SQLERRM,
1673 x_exec_type => 'C',
1674 x_err_code => x_err_code,
1675 x_err_buff => x_err_buff );
1676
1677 Update gms_bc_packets
1678 set status_code = 'T',
1679 result_code = 'F82',
1680 res_result_code = 'F82',
1681 res_grp_result_code = 'F82',
1682 task_result_code = 'F82',
1683 top_task_result_code = 'F82',
1684 award_result_code = 'F82'
1685 where packet_id= x_packet_id
1686 and bc_packet_id = x_bc_packet_id;
1687 END;
1688 END LOOP;
1689
1690 COMMIT;
1691 CLOSE cur_update_col;
1692 END IF;
1693
1694 -- x_mode <> 'U' then
1695 x_err_code := 0;
1696 EXCEPTION
1697 WHEN OTHERS THEN
1698 gms_error_pkg.gms_message ( x_err_name=> 'GMS_UNEXPECTED_ERROR',
1699 x_token_name1 => 'PROGRAM_NAME',
1700 x_token_val1 => g_error_program_name || '.' || g_error_procedure_name || '.' || g_error_stage,
1701 x_token_name2 => 'SQLCODE',
1702 x_token_val2 => SQLCODE,
1703 x_token_name3 => 'SQLERRM',
1704 x_token_val3 => SQLERRM,
1705 x_exec_type => 'C',
1706 x_err_code => x_err_code,
1707 x_err_buff => x_err_buff );
1708
1709 Update gms_bc_packets
1710 set status_code = 'T',
1711 result_code = 'F100',
1712 res_result_code = 'F100',
1713 res_grp_result_code = 'F100',
1714 task_result_code = 'F100',
1715 top_task_result_code = 'F100',
1716 award_result_code = 'F100'
1717 where packet_id= x_packet_id;
1718 if cur_update_col%ISOPEN then
1719 close cur_update_col;
1720 end if;
1721 if c_group_by_none_cat%ISOPEN then
1722 close c_group_by_none_cat;
1723 end if;
1724 COMMIT;
1725 RAISE;
1726
1727 END re_base_setup_rlmi;
1728 ---------------------------------------------------------------------------------------------
1729 procedure update_bc_packet_status(x_packet_id in number) is
1730 begin
1731 update gms_bc_packets
1732 set status_code = 'A'
1733 where packet_id = x_packet_id
1734 and status_code = 'P';
1735 exception
1736 when others then
1737 raise;
1738 end;
1739 ---------------------------------------------------------------------------------------------
1740
1741 /***************************************************************************+
1742 |** Procedure to create actual and encumbrance lines in GMS_BALANCES when **|
1743 |** Funds check is not required ********************************************|
1744 +***************************************************************************/
1745
1746 PROCEDURE create_act_enc_gms_balances(x_budget_version_id number,
1747 x_base_budget_version_id number) IS
1748 BEGIN
1749 DELETE FROM gms_balances WHERE budget_version_id = x_budget_version_id;
1750 DELETE FROM gms_bc_packets WHERE budget_version_id = x_budget_version_id;
1751 --
1752 -- Insert Actuals and Encumbrance rows into GMS_BALANCES copied
1753 -- from the previous budget version in GMS_BALANCES
1754
1755 INSERT INTO gms_balances (
1756 PROJECT_ID,
1757 AWARD_ID,
1758 TASK_ID,
1759 RESOURCE_LIST_MEMBER_ID,
1760 SET_OF_BOOKS_ID,
1761 BUDGET_VERSION_ID,
1762 LAST_UPDATE_DATE,
1763 LAST_UPDATED_BY,
1764 CREATED_BY,
1765 CREATION_DATE,
1766 LAST_UPDATE_LOGIN,
1767 PERIOD_NAME,
1768 START_DATE,
1769 END_DATE,
1770 PARENT_MEMBER_ID,
1771 BUDGET_PERIOD_TO_DATE,
1772 ACTUAL_PERIOD_TO_DATE,
1773 ENCUMB_PERIOD_TO_DATE)
1774 SELECT
1775 gms.project_id,
1776 gms.award_id,
1777 gms.task_id,
1778 gms.resource_list_member_id,
1779 gms.set_of_books_id,
1780 x_budget_version_id,
1781 sysdate,
1782 FND_GLOBAL.USER_ID,
1783 FND_GLOBAL.USER_ID,
1784 sysdate,
1785 FND_GLOBAL.LOGIN_ID,
1786 gms.PERIOD_NAME,
1787 gms.START_DATE,
1788 gms.END_DATE,
1789 gms.PARENT_MEMBER_ID,
1790 0,
1791 gms.actual_period_to_date,
1792 gms.encumb_period_to_date
1793 FROM gms_balances gms
1794 WHERE gms.budget_version_id = x_base_budget_version_id
1795 AND ( NVL(gms.actual_period_to_date,0) <> 0 OR NVL(gms.encumb_period_to_date,0) <> 0 );
1796 EXCEPTION WHEN no_data_found THEN
1797 NULL;
1798 END create_act_enc_gms_balances;
1799
1800 /***********************************************************************+
1801 |** Procedure to get packet ids of the baselined budget and run the ****|
1802 |** Sweeper Process for the packet ids *********************************|
1803 +***********************************************************************/
1804 PROCEDURE sweep_baselined_budget(x_base_budget_version_id number) IS
1805 --
1806 CURSOR get_pacid_cur(p_budget_version_id number) IS
1807 SELECT packet_id
1808 FROM gms_bc_packets
1809 WHERE budget_version_id = p_budget_version_id;
1810 --
1811 BEGIN
1812 FOR get_pacid_cur_var in get_pacid_cur(x_base_budget_version_id)
1813 LOOP
1814 EXIT WHEN get_pacid_cur%NOTFOUND;
1815 --
1816 update_bc_packet_status(get_pacid_cur_var.packet_id);
1817 --
1818 gms_sweeper.upd_act_enc_bal(ERRBUF, x_e_code, get_pacid_cur_var.packet_id,'B');
1819 END LOOP;
1820 END sweep_baselined_budget;
1821
1822 /******************************************************************************+
1823 |******** Procedure to update GMS_BALANCES table when funds check not reqd. ***|
1824 +******************************************************************************/
1825
1826 PROCEDURE update_gms_fck_nr(x_budget_version_id number,
1827 x_base_bud_version_id number,
1828 x_sob_id number) IS
1829 bud_amount number;
1830 CURSOR sel_base_bud_lines(p_budget_version_id number) IS
1831 SELECT
1832 ra.project_id,
1833 gbv.award_id,
1834 ra.task_id,
1835 ra.resource_list_member_id,
1836 gbv.budget_version_id,
1837 FND_GLOBAL.USER_ID,
1838 sysdate,
1839 FND_GLOBAL.LOGIN_ID,
1840 gbl.PERIOD_NAME,
1841 gbl.START_DATE,
1842 gbl.END_DATE,
1843 rm.PARENT_MEMBER_ID,
1844 gbl.burdened_cost --pb.raw_cost
1845 FROM
1846 gms_budget_lines gbl,
1847 pa_resource_assignments ra,
1848 gms_budget_versions gbv,
1849 pa_resource_list_members rm
1850 WHERE
1851 gbv.budget_version_id = p_budget_version_id
1852 and ra.resource_assignment_id = gbl.resource_assignment_id
1853 and ra.budget_version_id = gbv.budget_version_id
1854 and rm.resource_list_member_id = ra.resource_list_member_id;
1855 --
1856 BEGIN
1857 FOR sel_rec in sel_base_bud_lines(x_budget_version_id)
1858 LOOP
1859 EXIT WHEN sel_base_bud_lines%notfound;
1860 --
1861 BEGIN
1862 SELECT budget_period_to_date
1863 INTO bud_amount
1864 FROM gms_balances
1865 WHERE project_id = sel_rec.project_id
1866 AND award_id = sel_rec.award_id
1867 AND task_id = sel_rec.task_id
1868 AND resource_list_member_id = sel_rec.resource_list_member_id
1869 AND set_of_books_id = x_sob_id
1870 AND budget_version_id = x_budget_version_id
1871 AND start_date = sel_rec.start_date;
1872 EXCEPTION WHEN no_data_found THEN
1873
1874 INSERT INTO gms_balances (project_id
1875 ,award_id
1876 ,task_id
1877 ,resource_list_member_id
1878 ,set_of_books_id
1879 ,budget_Version_id
1880 ,last_update_date
1881 ,last_updated_by
1882 ,created_by
1883 ,creation_date
1884 ,last_update_login
1885 ,period_name
1886 ,start_date
1887 ,end_date
1888 ,balance_type
1889 ,parent_member_id
1890 ,budget_period_to_date
1891 )
1892 VALUES
1893 (sel_rec.project_id
1894 ,sel_rec.award_id
1895 ,sel_rec.task_id
1896 ,sel_rec.resource_list_member_id
1897 ,x_sob_id
1898 ,x_budget_version_id
1899 ,sysdate
1900 ,FND_GLOBAL.USER_ID
1901 ,FND_GLOBAL.USER_ID
1902 ,sysdate
1903 ,FND_GLOBAL.LOGIN_ID
1904 ,sel_rec.period_name
1905 ,sel_rec.start_date
1906 ,sel_rec.end_date
1907 ,'BGT'
1908 ,sel_rec.parent_member_id
1909 ,sel_rec.burdened_cost
1910 );
1911 END;
1912 IF bud_amount <> sel_rec.burdened_cost THEN
1913 UPDATE gms_balances
1914 SET budget_period_to_date = sel_rec.burdened_cost
1915 WHERE Project_id = sel_rec.project_id
1916 AND award_id = sel_rec.award_id
1917 AND task_id = sel_rec.task_id
1918 AND resource_list_member_id = sel_rec.resource_list_member_id
1919 AND set_of_books_id = x_sob_id
1920 AND budget_version_id = x_budget_version_id
1921 AND start_date = sel_rec.start_date;
1922 END IF;
1923 END LOOP;
1924
1925
1926 END update_gms_fck_nr;
1927
1928 -------------------------------------------------------------------------------
1929
1930
1931 BEGIN
1932
1933
1934 -- Bug 1980810 : Added to set currency related global variables
1935 -- Call to pa_currency.round_currency_amt function will use
1936 -- global variables and thus improves performance
1937
1938 pa_currency.set_currency_info;
1939
1940
1941 x_e_stage := '100';
1942 -- TO SELECT SET OF BOOKS ID
1943 select set_of_books_id into x_sob_id
1944 from pa_implementations;
1945 x_e_stage := '200';
1946 IF L_DEBUG = 'Y' THEN
1947 gms_error_pkg.gms_debug('gms_budget_balance -1','C');
1948 END IF;
1949 -- TO SELECT DRAFT_BUDGET_VERSION_ID FROM PA_BUDGET_VERSION TABLE.
1950 select max(budget_version_id) into x_budget_version_id
1951 from gms_budget_versions
1952 where project_id = x_project_id
1953 and award_id = to_char(x_award_id)
1954 and ((budget_status_code ='W' and x_mode='S')
1955 or (budget_status_code = 'B' and x_mode = 'B'));
1956 IF L_DEBUG = 'Y' THEN
1957 gms_error_pkg.gms_debug('gms_budget_balance -2','C');
1958 END IF;
1959
1960 /* -- Commented out NOCOPY for Bug: 1666853 - will be included in 11.5F
1961
1962 --Logic for Conditional FC : Start -----------------------------------------------------------------
1963
1964 BEGIN
1965 x_e_stage := '210';
1966
1967 select fc_required_flag
1968 into x_fc_required_flag
1969 from gms_budget_versions
1970 where project_id = x_project_id
1971 and award_id = x_award_id
1972 and budget_status_code in ('W','S');
1973
1974 if nvl(x_fc_required_flag,'Y') = 'N' then
1975
1976 BEGIN
1977
1978 SELECT budget_version_id
1979 INTO x_base_bud_version_id
1980 FROM gms_budget_versions
1981 WHERE project_id = x_project_id
1982 AND award_id = x_award_id
1983 AND budget_status_code = 'B'
1984 AND (current_flag = 'Y'
1985 OR current_flag = 'R');
1986
1987
1988 -- If GMS:Update Actual and Encumbrance balance conc. process is
1989 -- not run then the actuals and encumbrances should be picked
1990 -- from GMS_BC_PACKETS otherwise the actuals and encumbrances should
1991 -- be picked from GMS_BALANCES since it will no longer be in
1992 -- GMS_BC_PACKETS.
1993
1994 begin
1995 SELECT 1 into x_dummy from dual
1996 where exists (select 'x'
1997 FROM gms_bc_packets
1998 WHERE project_id = x_project_id
1999 AND award_id = x_award_id
2000 AND budget_version_id = x_base_bud_version_id
2001 AND status_code ='A');
2002 exception
2003 when no_data_found then
2004 SELECT count(1)
2005 INTO x_dummy
2006 FROM gms_balances
2007 WHERE project_id = x_project_id
2008 AND award_id = x_award_id
2009 AND budget_version_id = x_base_bud_version_id
2010 AND actual_period_to_date is NOT NULL
2011 AND encumb_period_to_date is NOT NULL;
2012 end;
2013
2014
2015 IF x_dummy = 0 THEN
2016 create_act_enc_gms_balances(x_budget_version_id,x_base_bud_version_id);
2017 update_gms_fck_nr(x_budget_version_id,x_base_bud_version_id,x_sob_id);
2018 RETCODE := 'S';
2019 RETURN;
2020 ELSE
2021 sweep_baselined_budget(x_base_bud_version_id);
2022 create_act_enc_gms_balances(x_budget_version_id,x_base_bud_version_id);
2023 update_gms_fck_nr(x_budget_version_id,x_base_bud_version_id,x_sob_id);
2024 RETCODE := 'S';
2025 RETURN;
2026 END IF;
2027 EXCEPTION when no_data_found THEN
2028 NULL;
2029 END;
2030 END IF;
2031 END;
2032
2033 --Logic for Conditional FC : End -----------------------------------------------------------------
2034 for bug: 1666853 */
2035
2036 -- re-create GMS_BALANCES record
2037 x_e_stage := '300';
2038 open gms_bal_lock; -- lock gms_balances records for the budget version
2039 IF L_DEBUG = 'Y' THEN
2040 gms_error_pkg.gms_debug('gms_budget_balance -3','C');
2041 END IF;
2042 x_e_stage := '400';
2043 IF L_DEBUG = 'Y' THEN
2044 gms_error_pkg.gms_debug('gms_budget_balance -4','C');
2045 END IF;
2046 create_gms_balance( x_budget_version_id, x_sob_id, x_project_id, x_award_id );
2047 -- Get a new packet id
2048 x_e_stage := '500';
2049 IF L_DEBUG = 'Y' THEN
2050 gms_error_pkg.gms_debug('gms_budget_balance -5','C');
2051 END IF;
2052 select gl_bc_packets_s.nextval into x_packet_id from dual;
2053 -- Create raw transactions in GMS_BC_PACKETS
2054 x_e_stage := '600';
2055 IF L_DEBUG = 'Y' THEN
2056 gms_error_pkg.gms_debug('gms_budget_balance -6','C');
2057 END IF;
2058 create_direct_cost(x_packet_id ,
2059 x_sob_id ,
2060 x_project_id,
2061 x_award_id,
2062 x_budget_version_id);
2063 -- Create burden transactions in GMS_BC_PACKETS
2064 x_e_stage := '700';
2065 create_indirect_cost(x_packet_id);
2066 -------------------------------------------------------------
2067 Begin
2068 IF L_DEBUG = 'Y' THEN
2069 gms_error_pkg.gms_debug('gms_budget_balance -7','C');
2070 END IF;
2071 x_e_stage := '800';
2072 begin
2073 select count(packet_id) into x_run
2074 from gms_bc_packets
2075 where packet_id = x_packet_id
2076 and rownum < 2;
2077 exception
2078 when no_data_found then
2079 null;
2080 end;
2081 x_e_stage := '900';
2082 IF L_DEBUG = 'Y' THEN
2083 gms_error_pkg.gms_debug('gms_budget_balance -8','C');
2084 END IF;
2085 if x_run > 0 then
2086 if x_mode in ('S','B') then
2087 if NOT GMS_FUNDS_CONTROL_PKG.GMS_FCK( x_sob_id,
2088 x_packet_id,
2089 x_mode, -- DEFAULT 'R'
2090 x_over, -- DEFAULT 'N'
2091 x_partial, -- DEFAULT 'N'
2092 x_user_id, -- DEFAULT NULL
2093 x_user_resp_id, -- DEFAULT NULL
2094 x_execute, -- DEFAULT 'Y',
2095 x_return_code,
2096 x_e_code,
2097 x_e_mesg) then
2098 ERRBUF := x_e_stage||': '||x_e_mesg;
2099 end if;
2100
2101 if x_e_code = 'S' then
2102 retcode := 'S';
2103 else
2104 retcode := 'F';
2105 end if;
2106 else
2107 --Recreating gms_balances without going thro' FC
2108 x_e_stage := '910';
2109 IF L_DEBUG = 'Y' THEN
2110 gms_error_pkg.gms_debug('gms_budget_balance -9','C');
2111 END IF;
2112 -- Update resource list
2113 RETCODE := 'S';
2114 re_base_setup_rlmi(x_packet_id, x_budget_version_id,x_err_code,x_err_buff);
2115 if x_err_code <> 0 then
2116 gms_error_pkg.gms_message('GMS_RE_BASE_RLMI_FAILED',
2117 X_Exec_Type => 'C',
2118 X_Err_Code => X_Err_Code,
2119 X_Err_Buff => X_Err_Buff);
2120
2121 end if;
2122 x_e_stage := '920';
2123 IF L_DEBUG = 'Y' THEN
2124 gms_error_pkg.gms_debug('gms_budget_balance -10','C');
2125 END IF;
2126 update_bc_packet_status(x_packet_id);
2127 -- Update gms_balances using sweeper process
2128 end if;
2129 --Recreating gms_balances without going thro' FC
2130 else
2131 RETCODE := 'S';
2132 end if;
2133 x_e_stage := '930';
2134 IF L_DEBUG = 'Y' THEN
2135 gms_error_pkg.gms_debug('gms_budget_balance -11','C');
2136 END IF;
2137
2138 /* -- Commented out NOCOPY for Bug: 1666853 ...
2139 -- Sweeper is now being called from GMS_BUDGET_PUB.BASELINE_BUDGET()
2140 -- after the budget status flags have been updated.
2141
2142 if RETCODE = 'S' then
2143
2144
2145 -- Added if endif so that sweeper called only for baselining
2146
2147 if x_mode ='B' then
2148
2149 IF L_DEBUG = 'Y' THEN
2150 gms_error_pkg.gms_debug('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%','C');
2151 gms_error_pkg.gms_debug('--------- Calling balance Sweeper ------','C');
2152 gms_error_pkg.gms_debug('RETCODE'||RETCODE,'C');
2153 gms_error_pkg.gms_debug('x_mode'||x_mode,'C');
2154 gms_error_pkg.gms_debug('x_packet_id'||x_packet_id,'C');
2155 gms_error_pkg.gms_debug('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%','C');
2156 END IF;
2157
2158
2159 gms_sweeper.upd_act_enc_bal(ERRBUF, x_e_code, x_packet_id,x_mode,x_project_id,x_award_id);
2160 IF L_DEBUG = 'Y' THEN
2161 gms_error_pkg.gms_debug('gms_budget_balance -12','C');
2162 END IF;
2163 end if;
2164
2165
2166 if x_e_code <> 'S' then
2167 RETCODE := 'H';
2168 else
2169 RETCODE := 'S';
2170 end if;
2171 end if;
2172 .... for Bug: 1666853 */
2173
2174 Exception
2175 when resource_busy then
2176 if gms_bal_lock%isopen then
2177 close gms_bal_lock ;
2178 end if;
2179 RETCODE := 'L';
2180 ERRBUF := (SQLCODE||SQLERRM);
2181 return;
2182 when OTHERS then
2183 if gms_bal_lock%isopen then
2184 close gms_bal_lock;
2185 end if;
2186 RETCODE := 'H';
2187 ERRBUF := (X_E_STAGE||': '||SQLERRM);
2188 return;
2189 End;
2190 Exception
2191 when OTHERS then
2192 RETCODE := 'H';
2193 IF L_DEBUG = 'Y' THEN
2194 gms_error_pkg.gms_debug('gms_budget_balance -14','C');
2195 END IF;
2196 ERRBUF := (X_E_STAGE||': '||SQLERRM);
2197 end update_gms_balance;
2198
2199 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2200 --Bug 2721095 : The following function is introduced to calculate PO's quantity billed
2201 -- based on following logic :
2202 -- IF its not a new PO distribution Line then return the global variable G_PO_QUANTITY_BILLED
2203 -- ELSE Re calculate the value of PO's quantity Billed
2204 --
2205 -- PO's quantity Billed = sum (quantity_invoiced on approved AP that
2206 -- is matched to the PO)
2207 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2208 FUNCTION get_po_qty_invoiced (p_po_distribution_id NUMBER,
2209 p_po_quantity_billed NUMBER ,
2210 p_recalc VARCHAR2 ) RETURN NUMBER
2211 IS
2212
2213 CURSOR c_get_quantity_invoiced IS
2214 SELECT nvl(sum(aid.quantity_invoiced),0)
2215 FROM ap_invoice_distributions aid,
2216 gms_award_distributions adl
2217 WHERE aid.po_distribution_id = p_po_distribution_id
2218 AND aid.distribution_line_number = adl.distribution_line_number
2219 AND aid.invoice_distribution_id = adl.invoice_distribution_id -- AP Lines change
2220 AND aid.invoice_id = adl.invoice_id
2221 AND adl.document_type = 'AP'
2222 AND adl.award_set_id = aid.award_id
2223 AND aid.line_type_lookup_code = 'ITEM'
2224 AND adl.adl_status = 'A'
2225 AND nvl(adl.fc_status,'N') = 'A'
2226 AND nvl(aid.match_status_flag,'N') = 'A';
2227
2228 BEGIN
2229
2230 IF p_recalc = 'N' and nvl(G_PO_DISTRIBUTION_ID,-999) = p_po_distribution_id THEN
2231
2232 Return G_PO_QUANTITY_BILLED ;
2233
2234 ELSE
2235
2236 G_PO_DISTRIBUTION_ID := p_po_distribution_id;
2237
2238 OPEN c_get_quantity_invoiced;
2239 FETCH c_get_quantity_invoiced into G_PO_QUANTITY_BILLED;
2240 CLOSE c_get_quantity_invoiced;
2241
2242 RETURN G_PO_QUANTITY_BILLED ;
2243
2244 END IF;
2245
2246 EXCEPTION
2247 WHEN OTHERS THEN
2248
2249 IF c_get_quantity_invoiced%ISOPEN THEN
2250 CLOSE c_get_quantity_invoiced;
2251 END IF;
2252
2253 IF p_recalc = 'N' and nvl(G_PO_DISTRIBUTION_ID,-999) = p_po_distribution_id THEN
2254 RETURN G_PO_QUANTITY_BILLED;
2255 ELSE
2256 G_PO_DISTRIBUTION_ID := p_po_distribution_id;
2257 G_PO_QUANTITY_BILLED := p_po_quantity_billed;
2258 RETURN G_PO_QUANTITY_BILLED;
2259 END IF;
2260
2261 END get_po_qty_invoiced ;
2262
2263 end gms_budget_balance;