[Home] [Help]
PACKAGE BODY: APPS.GMS_PA_API3
Source
1 PACKAGE BODY gms_pa_api3 AS
2 /* $Header: gmspax3b.pls 120.11 2010/08/27 14:11:57 prabsing ship $ */
3
4 /* added as part of bug 6761516 */
5 TYPE t_numb_tab is table of number index by binary_integer;
6 TYPE t_vch1_tab is table of varchar2(1) index by binary_integer;
7
8 g_task_id_tab t_numb_tab ;
9 g_test_tab t_vch1_tab ;
10 /* end added as part of bug 6761516 */
11
12 -- =====================
13 -- Start of the comment
14 -- API Name : grants_enabled
15 -- Type : Public
16 -- Pre_reqs : None
17 -- Description : Determine the grants implementations for a
18 -- operating unit. The value returned here is
19 -- from the cache.
20 -- Parameters : None
21 -- Return Value : 'Y' - Grants is implemented for a MO Org.
22 -- 'N'- Grants is not implemented.
23 --
24 -- End of comments
25 -- ===============
26
27 FUNCTION grants_enabled return VARCHAR is
28 l_enabled VARCHAR2(1) ;
29 BEGIN
30 l_enabled := 'N' ;
31
32 IF gms_install.enabled THEN
33 l_enabled := 'Y' ;
34 END IF ;
35
36 return l_enabled ;
37
38 END grants_enabled ;
39
40 -- =====================
41 -- Start of the comment
42 -- API Name : override_rate_rev_id
43 -- Type : Public
44 -- Pre_reqs : None
45 -- Description : The purpose of this API is to determine
46 -- the schedule based on the award.
47 -- Called from : PA_COST_PLUS.find_rate_sch_rev_id
48 -- Return Value : None
49 --
50 -- Parameters :
51 -- IN :
52 -- p_tran_item_id Expenditure item id
53 -- p_tran_type Transaction type
54 -- p_task_id Task ID
55 -- p_schedule_type Schedule Type
56 -- C - Costing Schedule
57 -- R - Revenue Schedule
58 -- I - Invoice Schedule
59 -- p_exp_item_date Expenditure item date
60 --OUT x_sch_fixed_date Schedule fixed date.
61 -- x_rate_sch_rev_id Revision ID
62 -- x_status Status
63 -- Note Do not add 'commit' or 'rollback' in your code, since Oracle
64 -- Project Accounting controls the transaction for you.
65 -- End of comments
66 -- ===============
67
68 PROCEDURE override_rate_rev_id(
69 p_tran_item_id IN number DEFAULT NULL,
70 p_tran_type IN Varchar2 DEFAULT NULL,
71 p_task_id IN number DEFAULT NULL,
72 p_schedule_type IN Varchar2 DEFAULT NULL,
73 p_exp_item_date IN Date DEFAULT NULL,
74 x_sch_fixed_date IN OUT nocopy Date,
75 x_rate_sch_rev_id OUT nocopy number,
76 x_status OUT nocopy number ) is
77
78 l_sponsored_flag varchar2(1) ;
79
80 BEGIN
81
82 x_rate_sch_rev_id:= NULL ;
83 x_status := NULL ;
84
85 IF p_tran_item_id is NULL THEN
86 return ;
87 END IF ;
88
89 gms_pa_api.Override_Rate_Rev_Id(
90 p_tran_item_id,
91 p_tran_type ,
92 p_task_id ,
93 p_schedule_type ,
94 p_exp_item_date ,
95 x_sch_fixed_date,
96 x_rate_sch_rev_id ,
97 x_status ) ;
98
99 END override_rate_rev_id ;
100
101 -- =====================
102 -- Start of the comment
103 -- API Name : commitments_changed
104 -- Type : Public
105 -- Pre_reqs : None
106 -- Description : The purpose of this API is to determine
107 -- the new manual encumbrances/REQ/PO/AP generated
108 -- since the last run of PSI process.
109 -- Called from : PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED
110 -- Return Value : Y
111 -- N
112 --
113 -- Parameters :
114 -- IN :
115 -- p_project_id Project ID value
116 -- End of comments
117 -- ===============
118 -- Code merged in pa_check_commitments (PAXCMTVB.pls)
119
120 -- =====================
121 -- Start of the comment
122 -- API Name : is_award_same
123 -- Type : Public
124 -- Pre_reqs : None
125 -- Description : The purpose of this API is to compare the award entered by the user
126 -- in expenditure
127 -- entry form and the award for the reversal item found is same or not.
128 -- Called from : exp_items_private.check_matching_reversal
129 -- Return Value : Y
130 -- N
131 --
132 -- Parameters :
133 -- IN :
134 -- expenditure_item_id Item id of matching reversal item
135 -- award_number Award Number entered in expenidture entry form.
136 -- End of comments
137 -- ===============
138 FUNCTION is_award_same (P_expenditure_item_id IN NUMBER,
139 P_award_number IN VARCHAR2 )
140 RETURN VARCHAR2 IS
141
142 l_award_same VARCHAR2(1) ;
143
144 Begin
145
146 l_award_same := 'N' ;
147
148 select 'Y'
149 into l_award_same
150 from gms_award_distributions adl,
151 gms_awards ga
152 where adl.expenditure_item_id = p_expenditure_item_id
153 and adl.document_type = 'EXP'
154 and adl.adl_status = 'A'
155 and adl.adl_line_num = 1
156 and adl.award_id = ga.award_id
157 and ga.award_number = p_award_number;
158
159 RETURN l_award_same ;
160
161 Exception
162 when no_data_found then
163 return 'N';
164 End is_award_same ;
165
166 -- =====================
167 -- Start of the comment
168 -- API Name : create_cmt_txns
169 -- Type : Public
170 -- Pre_reqs : None
171 -- Description : The purpose of this API is to create commitment transactions
172 -- using the GMS view and called from the PSI process .
173 -- Called from : PA_TXN_ACCUMS.create_cmt_txns
174 -- Return Value : None
175 --
176 -- Parameters :
177 -- IN :
178 -- p_start_project_id Starting project id in the range.
179 -- p_end_project_id Last project id in the range.
180 -- p_system_linkage_function System Linkage function
181 -- End of comments
182 -- ===============
183 PROCEDURE create_cmt_txns ( p_start_project_id IN NUMBER,
184 p_end_project_id IN NUMBER,
185 p_system_linkage_function IN VARCHAR2 ) is
186
187 l_last_updated_by NUMBER(15) ;
188 l_last_update_date NUMBER(15) ;
189 l_created_by NUMBER(15) ;
190 l_last_update_login NUMBER(15) ;
191 l_request_id NUMBER(15) ;
192 l_program_application_id NUMBER(15) ;
193 l_program_id NUMBER(15) ;
194 l_spon_project NUMBER(15) ;
195 l_cur_pa_period varchar2(20); /* Added for commitment change request */
196 l_cur_gl_period varchar2(15); /* Added for commitment change request */
197
198 TYPE t_num_tab is table of number ;
199 TYPE t_vc30_tab is table of varchar2(30) ;
200 TYPE t_vc50_tab is table of varchar2(50) ;
201 TYPE t_vc1_tab is table of varchar2(1) ;
202 TYPE t_vc255_tab is table of varchar2(255) ;
203 TYPE t_vc240_tab is table of varchar2(240) ;
204 TYPE t_vc15_tab is table of varchar2(15) ;
205 TYPE t_vc20_tab is table of varchar2(20) ;
206 TYPE t_vc80_tab is table of varchar2(80) ;
207 TYPE t_date_tab is table of date ;
208
209 l_project_id t_num_tab ;
210 l_task_id t_num_tab ;
211 l_transaction_source t_vc30_tab ;
212 l_line_type t_vc1_tab ;
213 l_cmt_number t_vc50_tab ;
214 l_cmt_distribution_id t_num_tab ;
215 l_description t_vc255_tab ;
216 l_expenditure_item_date t_date_tab ;
217 l_pa_period t_vc20_tab ;
218 l_gl_period t_vc15_tab ;
219 l_cmt_line_number t_num_tab ;
220 l_creation_date t_date_tab ;
221 l_approved_date t_date_tab ;
222 l_requestor_name t_vc240_tab ;
223 l_buyer_name t_vc240_tab ;
224 l_approved_flag t_vc1_tab ;
225 l_promised_date t_date_tab ;
226 l_need_by_date t_date_tab ;
227 l_header_id t_num_tab ;
228 l_burdenable_raw_cost t_num_tab ;
229 l_organization_id t_num_tab ;
230 l_vendor_id t_num_tab ;
231 l_vendor_name t_vc240_tab ;
232 l_expenditure_type t_vc30_tab ;
233 l_expenditure_category t_vc30_tab ;
234 l_revenue_category t_vc30_tab ;
235 l_system_linkage_function t_vc30_tab ;
236 l_unit_of_measure t_vc30_tab ;
237 l_unit_price t_num_tab ;
238 l_ind_compiled_set_id t_num_tab ;
239 l_tot_cmt_raw_cost t_num_tab ;
240 l_cmt_burdened_cost t_num_tab ;
241 l_cmt_quantity t_num_tab ;
242 l_quantity_ordered t_num_tab ;
243 l_amount_ordered t_num_tab ;
244 l_orig_quantity_ordered t_num_tab ;
245 l_orig_amount_ordered t_num_tab ;
246 l_quantity_cancelled t_num_tab ;
247 l_amount_cancelled t_num_tab ;
248 l_quantity_delivered t_num_tab ;
249 l_amount_delivered t_num_tab ;
250 l_quantity_invoiced t_num_tab ;
251 l_amount_invoiced t_num_tab ;
252 l_qty_out_delivery t_num_tab ;
253 l_amount_out_delivery t_num_tab ;
254 l_qty_out_invoiced t_num_tab ;
255 l_amount_out_invoiced t_num_tab ;
256 l_qty_overbilled t_num_tab ;
257 l_amount_overbilled t_num_tab ;
258 l_orig_txn_ref1 t_vc15_tab ;
259 l_orig_txn_ref2 t_vc15_tab ;
260 l_orig_txn_ref3 t_vc15_tab ;
261 l_updated_by t_num_tab ;
262 l_update_date t_date_tab ;
263 --l_created_by t_num_tab ;
264 l_update_login t_num_tab ;
265 l_receipt_currency_code t_vc15_tab ;
266 l_acct_currency_code t_vc15_tab ;
267 l_receipt_currency_amount t_num_tab ;
268 l_receipt_exchange_rate t_num_tab ;
269 l_acct_raw_cost t_num_tab ;
270 l_denom_currency_code t_vc15_tab ;
271 l_denom_raw_cost t_num_tab ;
272 l_denom_burdened_cost t_num_tab ;
273 l_acct_burdened_cost t_num_tab ;
274 l_acct_rate_date t_date_tab ;
275 l_acct_rate_type t_vc30_tab ;
276 l_acct_exchange_rate t_num_tab ;
277 l_cmt_rejection_code t_vc80_tab ;
278 l_cmt_header_id t_num_tab ;
279 BEGIN
280
281 l_last_updated_by := FND_GLOBAL.USER_ID;
282 l_last_update_date := FND_GLOBAL.USER_ID;
283 l_created_by := FND_GLOBAL.USER_ID;
284 l_last_update_login := FND_GLOBAL.LOGIN_ID;
285 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
286 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
287 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
288
289 IF p_start_project_id is NULL then
290 return ;
291 END IF ;
292
293 -- l_cur_pa_period := pa_accum_utils.Get_current_pa_period;
294 -- l_cur_gl_period := pa_accum_utils.Get_current_gl_period;
295
296 -- bug 3746527
297 select
298 per.PERIOD_NAME,
299 per.GL_PERIOD_NAME
300 into
301 l_cur_pa_period,
302 l_cur_gl_period
303 from
304 PA_PROJECTS_ALL prj,
305 PA_PERIODS_ALL per
306 where
307 prj.PROJECT_ID = p_start_project_id and
308 nvl(per.ORG_ID, -1) = nvl(prj.ORG_ID, -1) and
309 per.CURRENT_PA_PERIOD_FLAG = 'Y';
310
311 /* End of commitment change request*/
312 -- bug 4094814
313 -- psi will insert for non spon projects.
314 -- l_spon_project := gms_cost_plus_extn.is_spon_project(p_start_project_id) ;
315 --
316 -- Non Sponsored Project related changes...
317 --
318 --IF l_spon_project = 1 THEN
319 -- bug 4094814 code is changed in psi to allow insert for non spon projects.
320 --END IF ;
321
322 -- Continue sponsored project from here...
323 ---
324 --
325 -- BUG: 3614241
326 -- Performance issue with gms_commitment_txns_v
327 -- FTS and NMV causing performance issue.
328 -- Resolution
329 -- We are making a insert for Manual encumbrance (raw and Burden )
330 -- Commitment insert for the raw cost using the BULK array.
331 -- Indirect cost insert is done using the bulk arary and ind_compiled_set_id
332 -- joins.
333 -- -----
334
335 l_project_id := t_num_tab() ;
336 l_task_id := t_num_tab() ;
337 l_transaction_source := t_vc30_tab() ;
338 l_line_type := t_vc1_tab() ;
339 l_cmt_number := t_vc50_tab() ;
340 l_cmt_distribution_id := t_num_tab() ;
341 l_description := t_vc255_tab() ;
342 l_expenditure_item_date := t_date_tab() ;
343 l_pa_period := t_vc20_tab() ;
344 l_gl_period := t_vc15_tab() ;
345 l_cmt_line_number := t_num_tab() ;
346 l_creation_date := t_date_tab() ;
347 l_approved_date := t_date_tab() ;
348 l_requestor_name := t_vc240_tab() ;
349 l_buyer_name := t_vc240_tab() ;
350 l_approved_flag := t_vc1_tab() ;
351 l_promised_date := t_date_tab() ;
352 l_need_by_date := t_date_tab() ;
353 l_header_id := t_num_tab() ;
354 l_organization_id := t_num_tab() ;
355 l_vendor_id := t_num_tab() ;
356 l_vendor_name := t_vc240_tab() ;
357 l_expenditure_type := t_vc30_tab() ;
358 l_expenditure_category := t_vc30_tab() ;
359 l_revenue_category := t_vc30_tab() ;
360 l_system_linkage_function := t_vc30_tab() ;
361 l_unit_of_measure := t_vc30_tab() ;
362 l_unit_price := t_num_tab() ;
363 l_ind_compiled_set_id := t_num_tab() ;
364 l_tot_cmt_raw_cost := t_num_tab() ;
365 l_cmt_burdened_cost := t_num_tab() ;
366 l_cmt_quantity := t_num_tab() ;
367 l_quantity_ordered := t_num_tab() ;
368 l_amount_ordered := t_num_tab() ;
369 l_orig_quantity_ordered := t_num_tab() ;
370 l_orig_amount_ordered := t_num_tab() ;
371 l_quantity_cancelled := t_num_tab() ;
372 l_amount_cancelled := t_num_tab() ;
373 l_quantity_delivered := t_num_tab() ;
374 l_amount_delivered := t_num_tab() ;
375 l_quantity_invoiced := t_num_tab() ;
376 l_amount_invoiced := t_num_tab() ;
377 l_qty_out_delivery := t_num_tab() ;
378 l_amount_out_delivery := t_num_tab() ;
379 l_qty_out_invoiced := t_num_tab() ;
380 l_amount_out_invoiced := t_num_tab() ;
381 l_qty_overbilled := t_num_tab() ;
382 l_amount_overbilled := t_num_tab() ;
383 l_orig_txn_ref1 := t_vc15_tab() ;
384 l_orig_txn_ref2 := t_vc15_tab() ;
385 l_orig_txn_ref3 := t_vc15_tab() ;
386 l_updated_by := t_num_tab() ;
387 l_update_date := t_date_tab() ;
388 --l_created_by := t_num_tab() ;
389 l_update_login := t_num_tab() ;
390 l_burdenable_raw_cost := t_num_tab() ;
391 l_receipt_currency_code := t_vc15_tab() ;
392 l_receipt_currency_amount := t_num_tab() ;
393 l_receipt_exchange_rate := t_num_tab() ;
394 l_denom_currency_code := t_vc15_tab() ;
395 l_denom_raw_cost := t_num_tab() ;
396 l_acct_raw_cost := t_num_tab() ;
397 l_acct_burdened_cost := t_num_tab() ;
398 l_denom_burdened_cost := t_num_tab() ;
399 l_acct_rate_date := t_date_tab() ;
400 l_acct_rate_type := t_vc30_tab() ;
401 l_acct_exchange_rate := t_num_tab() ;
402 l_cmt_rejection_code := t_vc80_tab() ;
403 l_cmt_header_id := t_num_tab() ;
404
405 --
406 -- 3614241
407 -- Insert Raw and burden cost for Manual encumbrances using the gms_enc_psi_v
408 --
409 -- bug 4068681 PJ.M:B10:P11:QA: GCW: UPDATE PROJECT SUMMARY AMOUNTS PROCESS ERRORS OUT
410 -- gms_enc_psi_v view was fixed to use transaction source and project id column.
411 -- transaction source was null as part of this bug.
412 --
413
414 INSERT INTO pa_commitment_txns
415 ( CMT_LINE_ID,
416 PROJECT_ID,
417 TASK_ID,
418 TRANSACTION_SOURCE,
419 LINE_TYPE,
420 CMT_NUMBER,
421 CMT_DISTRIBUTION_ID,
422 CMT_HEADER_ID,
423 DESCRIPTION,
424 EXPENDITURE_ITEM_DATE,
425 PA_PERIOD,
426 GL_PERIOD,
427 CMT_LINE_NUMBER,
428 CMT_CREATION_DATE,
429 CMT_APPROVED_DATE,
430 CMT_REQUESTOR_NAME,
431 CMT_BUYER_NAME,
432 CMT_APPROVED_FLAG,
433 CMT_PROMISED_DATE,
434 CMT_NEED_BY_DATE,
435 ORGANIZATION_ID,
436 VENDOR_ID,
437 VENDOR_NAME,
438 EXPENDITURE_TYPE,
439 EXPENDITURE_CATEGORY,
440 REVENUE_CATEGORY,
441 SYSTEM_LINKAGE_FUNCTION,
442 UNIT_OF_MEASURE,
443 UNIT_PRICE,
444 CMT_IND_COMPILED_SET_ID,
445 TOT_CMT_RAW_COST,
446 TOT_CMT_BURDENED_COST,
447 TOT_CMT_QUANTITY,
448 QUANTITY_ORDERED,
449 AMOUNT_ORDERED,
450 ORIGINAL_QUANTITY_ORDERED,
451 ORIGINAL_AMOUNT_ORDERED,
452 QUANTITY_CANCELLED,
453 AMOUNT_CANCELLED,
454 QUANTITY_DELIVERED,
455 AMOUNT_DELIVERED,
456 QUANTITY_INVOICED,
457 AMOUNT_INVOICED,
458 QUANTITY_OUTSTANDING_DELIVERY,
459 AMOUNT_OUTSTANDING_DELIVERY,
460 QUANTITY_OUTSTANDING_INVOICE,
461 AMOUNT_OUTSTANDING_INVOICE,
462 QUANTITY_OVERBILLED,
463 AMOUNT_OVERBILLED,
464 ORIGINAL_TXN_REFERENCE1,
465 ORIGINAL_TXN_REFERENCE2,
466 ORIGINAL_TXN_REFERENCE3,
467 LAST_UPDATE_DATE,
468 LAST_UPDATED_BY,
469 CREATION_DATE,
470 CREATED_BY,
471 LAST_UPDATE_LOGIN,
472 REQUEST_ID,
473 PROGRAM_APPLICATION_ID,
474 PROGRAM_ID,
475 PROGRAM_UPDATE_DATE,
476 BURDEN_SUM_SOURCE_RUN_ID,
477 BURDEN_SUM_DEST_RUN_ID,
478 BURDEN_SUM_REJECTION_CODE,
479 acct_raw_cost,
480 acct_burdened_cost,
481 denom_currency_code,
482 denom_raw_cost,
483 denom_burdened_cost,
484 acct_currency_code,
485 acct_rate_date,
486 acct_rate_type,
487 acct_exchange_rate,
488 receipt_currency_code,
489 receipt_currency_amount,
490 receipt_exchange_rate,
491 project_currency_code,
492 project_rate_date,
493 project_rate_type,
494 project_exchange_rate,
495 generation_error_flag,
496 cmt_rejection_code
497 )
498 SELECT pa_txn_accums.cmt_line_id,
499 project_id,
500 task_id,
501 transaction_source,
502 line_type,
503 cmt_number,
504 cmt_distribution_id,
505 cmt_header_id,
506 description,
507 expenditure_item_date,
508 pa_period,
509 gl_period,
510 cmt_line_number,
511 cmt_creation_date,
512 cmt_approved_date,
513 cmt_requestor_name,
514 cmt_buyer_name,
515 cmt_approved_flag,
516 cmt_promised_date,
517 cmt_need_by_date,
518 organization_id,
519 vendor_id,
520 vendor_name,
521 expenditure_type,
522 expenditure_category,
523 revenue_category,
524 system_linkage_function,
525 unit_of_measure,
526 unit_price,
527 cmt_ind_compiled_set_id,
528 TO_NUMBER(NULL),
529 TO_NUMBER(NULL),
530 tot_cmt_quantity,
531 quantity_ordered,
532 amount_ordered,
533 original_quantity_ordered,
534 original_amount_ordered,
535 quantity_cancelled,
536 amount_cancelled,
537 quantity_delivered,
538 TO_NUMBER(NULL),
539 quantity_invoiced,
540 amount_invoiced,
541 quantity_outstanding_delivery,
542 amount_outstanding_delivery,
543 quantity_outstanding_invoice,
544 amount_outstanding_invoice,
545 quantity_overbilled,
546 amount_overbilled,
547 substr(original_txn_reference1,1,30), -- bug 8745595
548 substr(original_txn_reference2,1,15), -- bug 8745595
549 /*adding substr for original_txn_reference2 to make sure that the issue reported for attribute1 column does not occur for attribute6, which
550 is stored in original_txn_reference2 and presently the size of original_txn_reference2 is 15, but this should be investigated further*/
551 original_txn_reference3,
552 SYSDATE,
553 l_last_updated_by,
554 SYSDATE,
555 l_created_by,
556 l_last_update_login,
557 l_request_id,
558 l_program_application_id,
559 l_program_id,
560 NULL,
561 -9999,
562 NULL,
563 NULL,
564 acct_raw_cost,
565 acct_burdened_cost,
566 denom_currency_code,
567 denom_raw_cost,
568 denom_burdened_cost,
569 acct_currency_code,
570 acct_rate_date,
571 acct_rate_type,
572 acct_exchange_rate,
573 receipt_currency_code,
574 receipt_currency_amount,
575 receipt_exchange_rate,
576 NULL,
577 TO_DATE(NULL),
578 NULL,
579 TO_NUMBER(NULL),
580 'N',
581 NULL
582 FROM gms_enc_psi_v
583 WHERE project_id = p_start_project_id
584 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
585 -- bug fixes for 3755094 and 3736097
586 AND NVL(system_linkage_function,'X') =
587 NVL(NVL(p_system_linkage_function,system_linkage_function),'X');
588
589 --
590 -- 3614241
591 -- Fetch the commitment cost from gms_commitments_override_v into bulk araray elements.
592 -- Only Raw cost is fetched here.
593
594 -- Requisition Insert (Raw Cost)
595 --
596 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
597 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
598 --
599 SELECT
600 pprd.project_id,
601 pprd.task_id ,
602 'ORACLE_PURCHASING' ,
603 'R' ,
604 pprd.req_number ,
605 pprd.req_distribution_id ,
606 pprd.requisition_header_id ,
607 pprd.item_description ,
608 pprd.expenditure_item_date ,
609 l_cur_pa_period,
610 l_cur_gl_period,
611 pprd.req_line ,
612 pprd.creation_date ,
613 to_date(null) ,
614 pprd.requestor_name ,
615 to_char(null) ,
616 pprd.approved_flag ,
617 to_date(null) ,
618 pprd.need_by_date ,
619 pprd.expenditure_organization_id ,
620 pprd.vendor_id ,
621 pprd.vendor_name ,
622 pprd.expenditure_type ,
623 pprd.expenditure_category ,
624 pprd.revenue_category ,
625 'VI' ,
626 pprd.unit ,
627 pprd.unit_price ,
628 adl.ind_compiled_set_id,
629 pprd.quantity ,
630 to_number(null) ,
631 to_number(null) ,
632 to_number(null) ,
633 to_number(null) ,
634 to_number(null) ,
635 to_number(null) ,
636 to_number(null) ,
637 to_number(null) ,
638 to_number(null) ,
639 to_number(null) ,
640 to_number(null) ,
641 to_number(null) ,
642 to_number(null) ,
643 to_number(null) ,
644 to_number(null) ,
645 NULL ,
646 NULL ,
647 NULL ,
648 pprd.amount ,
649 pprd.amount ,
650 pprd.denom_currency_code ,
651 pprd.denom_amount ,
652 pprd.denom_amount ,
653 pprd.acct_currency_code ,
654 pprd.acct_rate_date ,
655 pprd.acct_rate_type ,
656 pprd.acct_exchange_rate ,
657 to_char(null) ,
658 to_number(null) ,
659 to_number(null),
660 adl.burdenable_raw_cost
661 BULK collect into
662 l_project_id,
663 l_task_id,
664 l_transaction_source,
665 l_line_type,
666 l_cmt_number,
667 l_cmt_distribution_id,
668 l_cmt_header_id,
669 l_description,
670 l_expenditure_item_date,
671 l_pa_period,
672 l_gl_period,
673 l_cmt_line_number,
674 l_creation_date,
675 l_approved_date,
676 l_requestor_name,
677 l_buyer_name,
678 l_approved_flag,
679 l_promised_date,
680 l_need_by_date,
681 l_organization_id,
682 l_vendor_id,
683 l_vendor_name,
684 l_expenditure_type,
685 l_expenditure_category,
686 l_revenue_category,
687 l_system_linkage_function,
688 l_unit_of_measure,
689 l_unit_price,
690 l_ind_compiled_set_id,
691 l_cmt_quantity,
692 l_quantity_ordered,
693 l_amount_ordered,
694 l_orig_quantity_ordered,
695 l_orig_amount_ordered,
696 l_quantity_cancelled,
697 l_amount_cancelled,
698 l_quantity_delivered,
699 l_quantity_invoiced,
700 l_amount_invoiced,
701 l_qty_out_delivery,
702 l_amount_out_delivery,
703 l_qty_out_invoiced,
704 l_amount_out_invoiced,
705 l_qty_overbilled,
706 l_amount_overbilled,
707 l_orig_txn_ref1,
708 l_orig_txn_ref2,
709 l_orig_txn_ref3,
710 l_acct_raw_cost,
711 l_acct_burdened_cost,
712 l_denom_currency_code,
713 l_denom_raw_cost,
714 l_denom_burdened_cost,
715 l_acct_currency_code,
716 l_acct_rate_date,
717 l_acct_rate_type,
718 l_acct_exchange_rate,
719 l_receipt_currency_code,
720 l_receipt_currency_amount,
721 l_receipt_exchange_rate,
722 l_burdenable_raw_cost
723 FROM PA_PROJ_REQ_DISTRIBUTIONS PPRD,
724 gms_award_distributions adl
725 WHERE PPRD.project_id = p_start_project_id
726 --
727 -- Bug : 4908630
728 -- R12.PJ:XB2:DEV:GMS: APPSPERF:GMS: PACKAGE: GMSPAX3B.PLS ( SHARE MEM:11MB) 5 SQL
729 --
730 and adl.distribution_id = pprd.req_distribution_id
731 and pprd.award_set_id = adl.award_set_id
732 and adl.adl_line_num= 1
733 -- bug fixes for 3755094 and 3736097
734 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
735 AND 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X');
736
737 if l_project_id.count <> 0 then
738
739 --
740 -- 3614241
741 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
742 --
743 FORALL indx in 1..l_project_id.count
744 INSERT INTO pa_commitment_txns
745 ( cmt_line_id,
746 project_id,
747 task_id,
748 transaction_source,
749 line_type,
750 cmt_number,
751 cmt_distribution_id,
752 cmt_header_id,
753 description,
754 expenditure_item_date,
755 pa_period,
756 gl_period,
757 cmt_line_number,
758 cmt_creation_date,
759 cmt_approved_date,
760 cmt_requestor_name,
761 cmt_buyer_name,
762 cmt_approved_flag,
763 cmt_promised_date,
764 cmt_need_by_date,
765 organization_id,
766 vendor_id,
767 vendor_name,
768 expenditure_type,
769 expenditure_category,
770 revenue_category,
771 system_linkage_function,
772 unit_of_measure,
773 unit_price,
774 cmt_ind_compiled_set_id,
775 tot_cmt_raw_cost,
776 tot_cmt_burdened_cost,
777 tot_cmt_quantity,
778 quantity_ordered,
779 amount_ordered,
780 original_quantity_ordered,
781 original_amount_ordered,
782 quantity_cancelled,
783 amount_cancelled,
784 quantity_delivered,
785 amount_delivered,
786 quantity_invoiced,
787 amount_invoiced,
788 quantity_outstanding_delivery,
789 amount_outstanding_delivery,
790 quantity_outstanding_invoice,
791 amount_outstanding_invoice,
792 quantity_overbilled,
793 amount_overbilled,
794 original_txn_reference1,
795 original_txn_reference2,
796 original_txn_reference3,
797 last_update_date,
798 last_updated_by,
799 creation_date,
800 created_by,
801 last_update_login,
802 request_id,
803 program_application_id,
804 program_id,
805 program_update_date,
806 burden_sum_source_run_id,
807 burden_sum_dest_run_id,
808 burden_sum_rejection_code,
809 acct_raw_cost,
810 acct_burdened_cost,
811 denom_currency_code,
812 denom_raw_cost,
813 denom_burdened_cost,
814 acct_currency_code,
815 acct_rate_date,
816 acct_rate_type,
817 acct_exchange_rate,
818 receipt_currency_code,
819 receipt_currency_amount,
820 receipt_exchange_rate,
821 project_currency_code,
822 project_rate_date,
823 project_rate_type,
824 project_exchange_rate,
825 generation_error_flag,
826 cmt_rejection_code
827 )
828 values (
829 pa_txn_accums.cmt_line_id,
830 l_project_id(indx),
831 l_task_id(indx),
832 l_transaction_source(indx),
833 l_line_type(indx),
834 l_cmt_number(indx),
835 l_cmt_distribution_id(indx),
836 l_cmt_header_id(indx),
837 l_description(indx),
838 l_expenditure_item_date(indx),
839 l_pa_period(indx),
840 l_gl_period(indx),
841 l_cmt_line_number(indx),
842 l_creation_date(indx),
843 l_approved_date(indx),
844 l_requestor_name(indx),
845 l_buyer_name(indx),
846 l_approved_flag(indx),
847 l_promised_date(indx),
848 l_need_by_date(indx),
849 l_organization_id(indx),
850 l_vendor_id(indx),
851 l_vendor_name(indx),
852 l_expenditure_type(indx),
853 l_expenditure_category(indx),
854 l_revenue_category(indx),
855 l_system_linkage_function(indx),
856 l_unit_of_measure(indx),
857 l_unit_price(indx),
858 to_number(null),
859 to_number(null),
860 to_number(null),
861 l_cmt_quantity(indx),
862 l_quantity_ordered(indx),
863 l_amount_ordered(indx),
864 l_orig_quantity_ordered(indx),
865 l_orig_amount_ordered(indx),
866 l_quantity_cancelled(indx),
867 l_amount_cancelled(indx),
868 l_quantity_delivered(indx),
869 to_number(null),
870 l_quantity_invoiced(indx),
871 l_amount_invoiced(indx),
872 l_qty_out_delivery(indx),
873 l_amount_out_delivery(indx),
874 l_qty_out_invoiced(indx),
875 l_amount_out_invoiced(indx),
876 l_qty_overbilled(indx),
877 l_amount_overbilled(indx),
878 l_orig_txn_ref1(indx),
879 l_orig_txn_ref2(indx),
880 l_orig_txn_ref3(indx),
881 sysdate,
882 l_last_updated_by,
883 sysdate,
884 l_created_by,
885 l_last_update_login,
886 l_request_id,
887 l_program_application_id,
888 l_program_id,
889 null,
890 -9999,
891 null,
892 null,
893 l_acct_raw_cost(indx),
894 l_acct_burdened_cost(indx),
895 l_denom_currency_code(indx),
896 l_denom_raw_cost(indx),
897 l_denom_burdened_cost(indx),
898 l_acct_currency_code(indx),
899 l_acct_rate_date(indx),
900 l_acct_rate_type(indx),
901 l_acct_exchange_rate(indx),
902 l_receipt_currency_code(indx),
903 l_receipt_currency_amount(indx),
904 l_receipt_exchange_rate(indx),
905 null,
906 to_date(null),
907 null,
908 to_number(null),
909 'N',
910 null ) ;
911
912 --
913 -- 3614241
914 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
915 --
916 FORALL indx in 1..l_project_id.count
917 INSERT INTO pa_commitment_txns
918 ( cmt_line_id,
919 project_id,
920 task_id,
921 transaction_source,
922 line_type,
923 cmt_number,
924 cmt_distribution_id,
925 cmt_header_id,
926 description,
927 expenditure_item_date,
928 pa_period,
929 gl_period,
930 cmt_line_number,
931 cmt_creation_date,
932 cmt_approved_date,
933 cmt_requestor_name,
934 cmt_buyer_name,
935 cmt_approved_flag,
936 cmt_promised_date,
937 cmt_need_by_date,
938 organization_id,
939 vendor_id,
940 vendor_name,
941 expenditure_type,
942 expenditure_category,
943 revenue_category,
944 system_linkage_function,
945 unit_of_measure,
946 unit_price,
947 cmt_ind_compiled_set_id,
948 tot_cmt_raw_cost,
949 tot_cmt_burdened_cost,
950 tot_cmt_quantity,
951 quantity_ordered,
952 amount_ordered,
953 original_quantity_ordered,
954 original_amount_ordered,
955 quantity_cancelled,
956 amount_cancelled,
957 quantity_delivered,
958 amount_delivered,
959 quantity_invoiced,
960 amount_invoiced,
961 quantity_outstanding_delivery,
962 amount_outstanding_delivery,
963 quantity_outstanding_invoice,
964 amount_outstanding_invoice,
965 quantity_overbilled,
966 amount_overbilled,
967 original_txn_reference1,
968 original_txn_reference2,
969 original_txn_reference3,
970 last_update_date,
971 last_updated_by,
972 creation_date,
973 created_by,
974 last_update_login,
975 request_id,
976 program_application_id,
977 program_id,
978 program_update_date,
979 burden_sum_source_run_id,
980 burden_sum_dest_run_id,
981 burden_sum_rejection_code,
982 acct_raw_cost,
983 acct_burdened_cost,
984 denom_currency_code,
985 denom_raw_cost,
986 denom_burdened_cost,
987 acct_currency_code,
988 acct_rate_date,
989 acct_rate_type,
990 acct_exchange_rate,
991 receipt_currency_code,
992 receipt_currency_amount,
993 receipt_exchange_rate,
994 project_currency_code,
995 project_rate_date,
996 project_rate_type,
997 project_exchange_rate,
998 generation_error_flag,
999 cmt_rejection_code
1000 )
1001 select pa_txn_accums.cmt_line_id,
1002 l_project_id(indx),
1003 l_task_id(indx),
1004 l_transaction_source(indx),
1005 l_line_type(indx),
1006 l_cmt_number(indx),
1007 l_cmt_distribution_id(indx),
1008 l_cmt_header_id(indx),
1009 l_description(indx),
1010 l_expenditure_item_date(indx),
1011 l_pa_period(indx),
1012 l_gl_period(indx),
1013 l_cmt_line_number(indx),
1014 l_creation_date(indx),
1015 l_approved_date(indx),
1016 l_requestor_name(indx),
1017 l_buyer_name(indx),
1018 l_approved_flag(indx),
1019 l_promised_date(indx),
1020 l_need_by_date(indx),
1021 l_organization_id(indx),
1022 l_vendor_id(indx),
1023 l_vendor_name(indx),
1024 icc.expenditure_type,
1025 pet.expenditure_category,
1026 l_revenue_category(indx),
1027 'BTC',
1028 l_unit_of_measure(indx),
1029 l_unit_price(indx),
1030 l_ind_compiled_set_id(indx),
1031 to_number(null),
1032 to_number(null),
1033 0,
1034 0,
1035 0,
1036 0,
1037 0,
1038 0,
1039 0,
1040 0,
1041 to_number(null),
1042 0,
1043 0,
1044 0,
1045 0,
1046 0,
1047 0,
1048 0,
1049 0,
1050 l_orig_txn_ref1(indx),
1051 l_orig_txn_ref2(indx),
1052 l_orig_txn_ref3(indx),
1053 sysdate,
1054 l_last_updated_by,
1055 sysdate,
1056 l_created_by,
1057 l_last_update_login,
1058 l_request_id,
1059 l_program_application_id,
1060 l_program_id,
1061 null,
1062 -9999,
1063 null,
1064 null,
1065 0,
1066 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
1067 l_denom_currency_code(indx),
1068 0,
1069 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
1070 nvl(l_acct_exchange_rate(indx),1)),
1071 l_acct_currency_code(indx),
1072 l_acct_rate_date(indx),
1073 l_acct_rate_type(indx),
1074 l_acct_exchange_rate(indx),
1075 l_receipt_currency_code(indx),
1076 l_receipt_currency_amount(indx),
1077 l_receipt_exchange_rate(indx),
1078 null,
1079 to_date(null),
1080 null,
1081 to_number(null),
1082 'N',
1083 null
1084 FROM pa_ind_rate_sch_revisions irsr,
1085 pa_cost_base_exp_types cbet,
1086 pa_compiled_multipliers cm,
1087 pa_ind_cost_codes icc,
1088 pa_ind_rate_schedules_all_bg irs,
1089 pa_ind_compiled_sets ics,
1090 pa_expenditure_types pet
1091 WHERE cbet.cost_base_type = 'INDIRECT COST'
1092 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
1093 -- and l_acct_raw_cost(indx) <> 0
1094 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
1095 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
1096 and icc.ind_cost_code = cm.ind_cost_code
1097 and cbet.cost_base = cm.cost_base
1098 and cbet.expenditure_type = l_expenditure_type(indx)
1099 and irsr.cost_plus_structure = cbet.cost_plus_structure
1100 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1101 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1102 and ics.organization_id = l_organization_id(indx)
1103 and ics.cost_base = cbet.cost_base
1104 and icc.expenditure_type = pet.expenditure_type ;
1105 end if ;
1106
1107 l_project_id.delete ;
1108 l_task_id.delete ;
1109 l_transaction_source.delete ;
1110 l_line_type.delete ;
1111 l_cmt_number.delete ;
1112 l_cmt_distribution_id.delete ;
1113 l_cmt_header_id.delete ;
1114 l_description.delete ;
1115 l_expenditure_item_date.delete ;
1116 l_pa_period.delete ;
1117 l_gl_period.delete ;
1118 l_cmt_line_number.delete ;
1119 l_creation_date.delete ;
1120 l_approved_date.delete ;
1121 l_requestor_name.delete ;
1122 l_buyer_name.delete ;
1123 l_approved_flag.delete ;
1124 l_promised_date.delete ;
1125 l_need_by_date.delete ;
1126 l_organization_id.delete ;
1127 l_vendor_id.delete ;
1128 l_vendor_name.delete ;
1129 l_expenditure_type.delete ;
1130 l_expenditure_category.delete ;
1131 l_revenue_category.delete ;
1132 l_system_linkage_function.delete ;
1133 l_unit_of_measure.delete ;
1134 l_unit_price.delete ;
1135 l_ind_compiled_set_id.delete ;
1136 l_cmt_quantity.delete ;
1137 l_quantity_ordered.delete ;
1138 l_amount_ordered.delete ;
1139 l_orig_quantity_ordered.delete ;
1140 l_orig_amount_ordered.delete ;
1141 l_quantity_cancelled.delete ;
1142 l_amount_cancelled.delete ;
1143 l_quantity_delivered.delete ;
1144 l_quantity_invoiced.delete ;
1145 l_amount_invoiced.delete ;
1146 l_qty_out_delivery.delete ;
1147 l_amount_out_delivery.delete ;
1148 l_qty_out_invoiced.delete ;
1149 l_amount_out_invoiced.delete ;
1150 l_qty_overbilled.delete ;
1151 l_amount_overbilled.delete ;
1152 l_orig_txn_ref1.delete ;
1153 l_orig_txn_ref2.delete ;
1154 l_orig_txn_ref3.delete ;
1155 l_acct_raw_cost.delete ;
1156 l_acct_burdened_cost.delete ;
1157 l_denom_currency_code.delete ;
1158 l_denom_raw_cost.delete ;
1159 l_denom_burdened_cost.delete ;
1160 l_acct_currency_code.delete ;
1161 l_acct_rate_date.delete ;
1162 l_acct_rate_type.delete ;
1163 l_acct_exchange_rate.delete ;
1164 l_receipt_currency_code.delete ;
1165 l_receipt_currency_amount.delete ;
1166 l_receipt_exchange_rate.delete ;
1167 l_burdenable_raw_cost.delete ;
1168
1169 --
1170 -- Purchase Order Inserts... (Raw)
1171 --
1172 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
1173 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
1174 --
1175
1176 SELECT
1177 pppd.project_id,
1178 pppd.task_id,
1179 'ORACLE_PURCHASING',
1180 'P',
1181 pppd.po_number,
1182 pppd.po_distribution_id,
1183 pppd.po_header_id,
1184 pppd.item_description,
1185 pppd.expenditure_item_date,
1186 l_cur_pa_period, /* Added for commitment change request*/
1187 l_cur_gl_period, /* Added for commitment change request*/
1188 pppd.po_line,
1189 pppd.creation_date,
1190 pppd.approved_date,
1191 pppd.requestor_name,
1192 pppd.buyer_name,
1193 pppd.approved_flag,
1194 pppd.promised_date,
1195 pppd.need_by_date ,
1196 pppd.expenditure_organization_id,
1197 pppd.vendor_id,
1198 pppd.vendor_name,
1199 pppd.expenditure_type,
1200 pppd.expenditure_category,
1201 pppd.revenue_category,
1202 'VI',
1203 pppd.unit,
1204 pppd.unit_price,
1205 adl.ind_compiled_set_id,
1206 pppd.quantity_outstanding_invoice,
1207 pppd.quantity_ordered,
1208 pppd.amount_ordered,
1209 pppd.original_quantity_ordered,
1210 pppd.original_amount_ordered,
1211 pppd.quantity_cancelled,
1212 pppd.amount_cancelled,
1213 pppd.quantity_delivered,
1214 pppd.quantity_invoiced,
1215 pppd.amount_invoiced,
1216 pppd.quantity_outstanding_delivery,
1217 pppd.amount_outstanding_delivery,
1218 pppd.quantity_outstanding_invoice,
1219 pppd.amount_outstanding_invoice,
1220 pppd.quantity_overbilled,
1221 pppd.amount_overbilled,
1222 NULL,
1223 NULL,
1224 NULL,
1225 pppd.amount_outstanding_invoice,
1226 PPPD.AMOUNT_OUTSTANDING_INVOICE ,
1227 pppd.denom_currency_code,
1228 pppd.denom_amt_outstanding_invoice,
1229 PPPD.denom_amt_outstanding_invoice ,
1230 pppd.acct_currency_code,
1231 pppd.acct_rate_date,
1232 pppd.acct_rate_type,
1233 pppd.acct_exchange_rate,
1234 TO_CHAR(NULL),
1235 TO_NUMBER(NULL),
1236 TO_NUMBER(NULL) ,
1237 adl.burdenable_raw_cost
1238 BULK collect into
1239 l_project_id,
1240 l_task_id,
1241 l_transaction_source,
1242 l_line_type,
1243 l_cmt_number,
1244 l_cmt_distribution_id,
1245 l_cmt_header_id,
1246 l_description,
1247 l_expenditure_item_date,
1248 l_pa_period,
1249 l_gl_period,
1250 l_cmt_line_number,
1251 l_creation_date,
1252 l_approved_date,
1253 l_requestor_name,
1254 l_buyer_name,
1255 l_approved_flag,
1256 l_promised_date,
1257 l_need_by_date,
1258 l_organization_id,
1259 l_vendor_id,
1260 l_vendor_name,
1261 l_expenditure_type,
1262 l_expenditure_category,
1263 l_revenue_category,
1264 l_system_linkage_function,
1265 l_unit_of_measure,
1266 l_unit_price,
1267 l_ind_compiled_set_id,
1268 l_cmt_quantity,
1269 l_quantity_ordered,
1270 l_amount_ordered,
1271 l_orig_quantity_ordered,
1272 l_orig_amount_ordered,
1273 l_quantity_cancelled,
1274 l_amount_cancelled,
1275 l_quantity_delivered,
1276 l_quantity_invoiced,
1277 l_amount_invoiced,
1278 l_qty_out_delivery,
1279 l_amount_out_delivery,
1280 l_qty_out_invoiced,
1281 l_amount_out_invoiced,
1282 l_qty_overbilled,
1283 l_amount_overbilled,
1284 l_orig_txn_ref1,
1285 l_orig_txn_ref2,
1286 l_orig_txn_ref3,
1287 l_acct_raw_cost,
1288 l_acct_burdened_cost,
1289 l_denom_currency_code,
1290 l_denom_raw_cost,
1291 l_denom_burdened_cost,
1292 l_acct_currency_code,
1293 l_acct_rate_date,
1294 l_acct_rate_type,
1295 l_acct_exchange_rate,
1296 l_receipt_currency_code,
1297 l_receipt_currency_amount,
1298 l_receipt_exchange_rate,
1299 l_burdenable_raw_cost
1300 FROM PA_PROJ_PO_DISTRIBUTIONS PPPD,
1301 GMS_AWARD_DISTRIBUTIONS ADL
1302 WHERE PPPD.project_id = p_start_project_id
1303 and pppd.po_distribution_id = adl.po_distribution_id
1304 and pppd.award_set_id = adl.award_set_id
1305 and adl.adl_line_num = 1
1306 -- bug fixes for 3755094 and 3736097
1307 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
1308 AND 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X');
1309
1310 if l_project_id.count <> 0 then
1311
1312 --
1313 -- 3614241
1314 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
1315 --
1316 FORALL indx in 1..l_project_id.count
1317
1318 INSERT INTO pa_commitment_txns
1319 ( cmt_line_id,
1320 project_id,
1321 task_id,
1322 transaction_source,
1323 line_type,
1324 cmt_number,
1325 cmt_distribution_id,
1326 cmt_header_id,
1327 description,
1328 expenditure_item_date,
1329 pa_period,
1330 gl_period,
1331 cmt_line_number,
1332 cmt_creation_date,
1333 cmt_approved_date,
1334 cmt_requestor_name,
1335 cmt_buyer_name,
1336 cmt_approved_flag,
1337 cmt_promised_date,
1338 cmt_need_by_date,
1339 organization_id,
1340 vendor_id,
1341 vendor_name,
1342 expenditure_type,
1343 expenditure_category,
1344 revenue_category,
1345 system_linkage_function,
1346 unit_of_measure,
1347 unit_price,
1348 cmt_ind_compiled_set_id,
1349 tot_cmt_raw_cost,
1350 tot_cmt_burdened_cost,
1351 tot_cmt_quantity,
1352 quantity_ordered,
1353 amount_ordered,
1354 original_quantity_ordered,
1355 original_amount_ordered,
1356 quantity_cancelled,
1357 amount_cancelled,
1358 quantity_delivered,
1359 amount_delivered,
1360 quantity_invoiced,
1361 amount_invoiced,
1362 quantity_outstanding_delivery,
1363 amount_outstanding_delivery,
1364 quantity_outstanding_invoice,
1365 amount_outstanding_invoice,
1366 quantity_overbilled,
1367 amount_overbilled,
1368 original_txn_reference1,
1369 original_txn_reference2,
1370 original_txn_reference3,
1371 last_update_date,
1372 last_updated_by,
1373 creation_date,
1374 created_by,
1375 last_update_login,
1376 request_id,
1377 program_application_id,
1378 program_id,
1379 program_update_date,
1380 burden_sum_source_run_id,
1381 burden_sum_dest_run_id,
1382 burden_sum_rejection_code,
1383 acct_raw_cost,
1384 acct_burdened_cost,
1385 denom_currency_code,
1386 denom_raw_cost,
1387 denom_burdened_cost,
1388 acct_currency_code,
1389 acct_rate_date,
1390 acct_rate_type,
1391 acct_exchange_rate,
1392 receipt_currency_code,
1393 receipt_currency_amount,
1394 receipt_exchange_rate,
1395 project_currency_code,
1396 project_rate_date,
1397 project_rate_type,
1398 project_exchange_rate,
1399 generation_error_flag,
1400 cmt_rejection_code
1401 )
1402 values (
1403 pa_txn_accums.cmt_line_id,
1404 l_project_id(indx),
1405 l_task_id(indx),
1406 l_transaction_source(indx),
1407 l_line_type(indx),
1408 l_cmt_number(indx),
1409 l_cmt_distribution_id(indx),
1410 l_cmt_header_id(indx),
1411 l_description(indx),
1412 l_expenditure_item_date(indx),
1413 l_pa_period(indx),
1414 l_gl_period(indx),
1415 l_cmt_line_number(indx),
1416 l_creation_date(indx),
1417 l_approved_date(indx),
1418 l_requestor_name(indx),
1419 l_buyer_name(indx),
1420 l_approved_flag(indx),
1421 l_promised_date(indx),
1422 l_need_by_date(indx),
1423 l_organization_id(indx),
1424 l_vendor_id(indx),
1425 l_vendor_name(indx),
1426 l_expenditure_type(indx),
1427 l_expenditure_category(indx),
1428 l_revenue_category(indx),
1429 l_system_linkage_function(indx),
1430 l_unit_of_measure(indx),
1431 l_unit_price(indx),
1432 to_number(null),
1433 to_number(null),
1434 to_number(null),
1435 l_cmt_quantity(indx),
1436 l_quantity_ordered(indx),
1437 l_amount_ordered(indx),
1438 l_orig_quantity_ordered(indx),
1439 l_orig_amount_ordered(indx),
1440 l_quantity_cancelled(indx),
1441 l_amount_cancelled(indx),
1442 l_quantity_delivered(indx),
1443 to_number(null),
1444 l_quantity_invoiced(indx),
1445 l_amount_invoiced(indx),
1446 l_qty_out_delivery(indx),
1447 l_amount_out_delivery(indx),
1448 l_qty_out_invoiced(indx),
1449 l_amount_out_invoiced(indx),
1450 l_qty_overbilled(indx),
1451 l_amount_overbilled(indx),
1452 l_orig_txn_ref1(indx),
1453 l_orig_txn_ref2(indx),
1454 l_orig_txn_ref3(indx),
1455 sysdate,
1456 l_last_updated_by,
1457 sysdate,
1458 l_created_by,
1459 l_last_update_login,
1460 l_request_id,
1461 l_program_application_id,
1462 l_program_id,
1463 null,
1464 -9999,
1465 null,
1466 null,
1467 l_acct_raw_cost(indx),
1468 l_acct_burdened_cost(indx),
1469 l_denom_currency_code(indx),
1470 l_denom_raw_cost(indx),
1471 l_denom_burdened_cost(indx),
1472 l_acct_currency_code(indx),
1473 l_acct_rate_date(indx),
1474 l_acct_rate_type(indx),
1475 l_acct_exchange_rate(indx),
1476 l_receipt_currency_code(indx),
1477 l_receipt_currency_amount(indx),
1478 l_receipt_exchange_rate(indx),
1479 null,
1480 to_date(null),
1481 null,
1482 to_number(null),
1483 'N',
1484 null ) ;
1485
1486 --
1487 -- 3614241
1488 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
1489 --
1490 FORALL indx in 1..l_project_id.count
1491 INSERT INTO pa_commitment_txns
1492 ( cmt_line_id,
1493 project_id,
1494 task_id,
1495 transaction_source,
1496 line_type,
1497 cmt_number,
1498 cmt_distribution_id,
1499 cmt_header_id,
1500 description,
1501 expenditure_item_date,
1502 pa_period,
1503 gl_period,
1504 cmt_line_number,
1505 cmt_creation_date,
1506 cmt_approved_date,
1507 cmt_requestor_name,
1508 cmt_buyer_name,
1509 cmt_approved_flag,
1510 cmt_promised_date,
1511 cmt_need_by_date,
1512 organization_id,
1513 vendor_id,
1514 vendor_name,
1515 expenditure_type,
1516 expenditure_category,
1517 revenue_category,
1518 system_linkage_function,
1519 unit_of_measure,
1520 unit_price,
1521 cmt_ind_compiled_set_id,
1522 tot_cmt_raw_cost,
1523 tot_cmt_burdened_cost,
1524 tot_cmt_quantity,
1525 quantity_ordered,
1526 amount_ordered,
1527 original_quantity_ordered,
1528 original_amount_ordered,
1529 quantity_cancelled,
1530 amount_cancelled,
1531 quantity_delivered,
1532 amount_delivered,
1533 quantity_invoiced,
1534 amount_invoiced,
1535 quantity_outstanding_delivery,
1536 amount_outstanding_delivery,
1537 quantity_outstanding_invoice,
1538 amount_outstanding_invoice,
1539 quantity_overbilled,
1540 amount_overbilled,
1541 original_txn_reference1,
1542 original_txn_reference2,
1543 original_txn_reference3,
1544 last_update_date,
1545 last_updated_by,
1546 creation_date,
1547 created_by,
1548 last_update_login,
1549 request_id,
1550 program_application_id,
1551 program_id,
1552 program_update_date,
1553 burden_sum_source_run_id,
1554 burden_sum_dest_run_id,
1555 burden_sum_rejection_code,
1556 acct_raw_cost,
1557 acct_burdened_cost,
1558 denom_currency_code,
1559 denom_raw_cost,
1560 denom_burdened_cost,
1561 acct_currency_code,
1562 acct_rate_date,
1563 acct_rate_type,
1564 acct_exchange_rate,
1565 receipt_currency_code,
1566 receipt_currency_amount,
1567 receipt_exchange_rate,
1568 project_currency_code,
1569 project_rate_date,
1570 project_rate_type,
1571 project_exchange_rate,
1572 generation_error_flag,
1573 cmt_rejection_code
1574 )
1575 select pa_txn_accums.cmt_line_id,
1576 l_project_id(indx),
1577 l_task_id(indx),
1578 l_transaction_source(indx),
1579 l_line_type(indx),
1580 l_cmt_number(indx),
1581 l_cmt_distribution_id(indx),
1582 l_cmt_header_id(indx),
1583 l_description(indx),
1584 l_expenditure_item_date(indx),
1585 l_pa_period(indx),
1586 l_gl_period(indx),
1587 l_cmt_line_number(indx),
1588 l_creation_date(indx),
1589 l_approved_date(indx),
1590 l_requestor_name(indx),
1591 l_buyer_name(indx),
1592 l_approved_flag(indx),
1593 l_promised_date(indx),
1594 l_need_by_date(indx),
1595 l_organization_id(indx),
1596 l_vendor_id(indx),
1597 l_vendor_name(indx),
1598 icc.expenditure_type,
1599 pet.expenditure_category,
1600 l_revenue_category(indx),
1601 'BTC',
1602 l_unit_of_measure(indx),
1603 l_unit_price(indx),
1604 l_ind_compiled_set_id(indx),
1605 to_number(null),
1606 to_number(null),
1607 0,
1608 0,
1609 0,
1610 0,
1611 0,
1612 0,
1613 0,
1614 0,
1615 to_number(null),
1616 0,
1617 0,
1618 0,
1619 0,
1620 0,
1621 0,
1622 0,
1623 0,
1624 l_orig_txn_ref1(indx),
1625 l_orig_txn_ref2(indx),
1626 l_orig_txn_ref3(indx),
1627 sysdate,
1628 l_last_updated_by,
1629 sysdate,
1630 l_created_by,
1631 l_last_update_login,
1632 l_request_id,
1633 l_program_application_id,
1634 l_program_id,
1635 null,
1636 -9999,
1637 null,
1638 null,
1639 0,
1640 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
1641 l_denom_currency_code(indx),
1642 0,
1643 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
1644 nvl(l_acct_exchange_rate(indx),1)),
1645 l_acct_currency_code(indx),
1646 l_acct_rate_date(indx),
1647 l_acct_rate_type(indx),
1648 l_acct_exchange_rate(indx),
1649 l_receipt_currency_code(indx),
1650 l_receipt_currency_amount(indx),
1651 l_receipt_exchange_rate(indx),
1652 null,
1653 to_date(null),
1654 null,
1655 to_number(null),
1656 'N',
1657 null
1658 FROM pa_ind_rate_sch_revisions irsr,
1659 pa_cost_base_exp_types cbet,
1660 pa_compiled_multipliers cm,
1661 pa_ind_cost_codes icc,
1662 pa_ind_rate_schedules_all_bg irs,
1663 pa_ind_compiled_sets ics,
1664 pa_expenditure_types pet
1665 WHERE cbet.cost_base_type = 'INDIRECT COST'
1666 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
1667 and l_acct_raw_cost(indx) <> 0
1668 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
1669 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
1670 and icc.ind_cost_code = cm.ind_cost_code
1671 and cbet.cost_base = cm.cost_base
1672 and cbet.expenditure_type = l_expenditure_type(indx)
1673 and irsr.cost_plus_structure = cbet.cost_plus_structure
1674 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1675 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1676 and ics.organization_id = l_organization_id(indx)
1677 and ics.cost_base = cbet.cost_base
1678 and icc.expenditure_type = pet.expenditure_type ;
1679 end if ;
1680 --
1681 -- AP Transactions starts here
1682 --
1683 --
1684 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
1685 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
1686 --
1687 l_project_id.delete ;
1688 l_task_id.delete ;
1689 l_transaction_source.delete ;
1690 l_line_type.delete ;
1691 l_cmt_number.delete ;
1692 l_cmt_distribution_id.delete ;
1693 l_cmt_header_id.delete ;
1694 l_description.delete ;
1695 l_expenditure_item_date.delete ;
1696 l_pa_period.delete ;
1697 l_gl_period.delete ;
1698 l_cmt_line_number.delete ;
1699 l_creation_date.delete ;
1700 l_approved_date.delete ;
1701 l_requestor_name.delete ;
1702 l_buyer_name.delete ;
1703 l_approved_flag.delete ;
1704 l_promised_date.delete ;
1705 l_need_by_date.delete ;
1706 l_organization_id.delete ;
1707 l_vendor_id.delete ;
1708 l_vendor_name.delete ;
1709 l_expenditure_type.delete ;
1710 l_expenditure_category.delete ;
1711 l_revenue_category.delete ;
1712 l_system_linkage_function.delete ;
1713 l_unit_of_measure.delete ;
1714 l_unit_price.delete ;
1715 l_ind_compiled_set_id.delete ;
1716 l_cmt_quantity.delete ;
1717 l_quantity_ordered.delete ;
1718 l_amount_ordered.delete ;
1719 l_orig_quantity_ordered.delete ;
1720 l_orig_amount_ordered.delete ;
1721 l_quantity_cancelled.delete ;
1722 l_amount_cancelled.delete ;
1723 l_quantity_delivered.delete ;
1724 l_quantity_invoiced.delete ;
1725 l_amount_invoiced.delete ;
1726 l_qty_out_delivery.delete ;
1727 l_amount_out_delivery.delete ;
1728 l_qty_out_invoiced.delete ;
1729 l_amount_out_invoiced.delete ;
1730 l_qty_overbilled.delete ;
1731 l_amount_overbilled.delete ;
1732 l_orig_txn_ref1.delete ;
1733 l_orig_txn_ref2.delete ;
1734 l_orig_txn_ref3.delete ;
1735 l_acct_raw_cost.delete ;
1736 l_acct_burdened_cost.delete ;
1737 l_denom_currency_code.delete ;
1738 l_denom_raw_cost.delete ;
1739 l_denom_burdened_cost.delete ;
1740 l_acct_currency_code.delete ;
1741 l_acct_rate_date.delete ;
1742 l_acct_rate_type.delete ;
1743 l_acct_exchange_rate.delete ;
1744 l_receipt_currency_code.delete ;
1745 l_receipt_currency_amount.delete ;
1746 l_receipt_exchange_rate.delete ;
1747 l_burdenable_raw_cost.delete ;
1748
1749 SELECT
1750 ppaid.project_id,
1751 ppaid.task_id,
1752 'ORACLE_PAYABLES',
1753 'I',
1754 ppaid.invoice_number,
1755 /* R12 AP Lines uptake:record invoice distribution ID and
1756 invoice line number instead of line number which is no
1757 longer unique.*/
1758 ppaid.invoice_distribution_id,
1759 ppaid.invoice_id,
1760 ppaid.description,
1761 ppaid.expenditure_item_date,
1762 l_cur_pa_period, /* Added for commitment change request*/
1763 l_cur_gl_period, /* Added for commitment change request*/
1764 /* R12 AP Lines uptake:record invoice distribution ID and
1765 invoice line number instead of line number which is no
1766 longer unique.*/
1767 ppaid.invoice_line_number,
1768 ppaid.invoice_date,
1769 to_date(NULL),
1770 to_char(NULL),
1771 to_char(NULL),
1772 ppaid.approved_flag,
1773 to_date(NULL),
1774 to_date(NULL),
1775 ppaid.expenditure_organization_id,
1776 vendor_id,
1777 ppaid.vendor_name,
1778 ppaid.expenditure_type,
1779 ppaid.expenditure_category,
1780 ppaid.revenue_category,
1781 'VI',
1782 to_char(NULL),
1783 to_number(NULL),
1784 adl.ind_compiled_set_id,
1785 ppaid.quantity,
1786 to_number(null),
1787 to_number(null),
1788 to_number(null),
1789 to_number(null),
1790 to_number(null),
1791 to_number(null),
1792 to_number(null),
1793 to_number(null),
1794 to_number(null),
1795 to_number(null),
1796 to_number(null),
1797 to_number(null),
1798 to_number(null),
1799 to_number(null),
1800 to_number(null),
1801 null,
1802 null,
1803 null,
1804 ppaid.amount,
1805 ppaid.amount ,
1806 ppaid.denom_currency_code,
1807 ppaid.denom_amount,
1808 ppaid.denom_amount ,
1809 ppaid.acct_currency_code,
1810 ppaid.acct_rate_date,
1811 ppaid.acct_rate_type,
1812 ppaid.acct_exchange_rate,
1813 ppaid.receipt_currency_code ,
1814 ppaid.receipt_currency_amount ,
1815 ppaid.receipt_exchange_rate ,
1816 /* Commented for Bug 5645290
1817 adl.burdenable_raw_cost */
1818 /* Added for Bug 5645290 */
1819 decode(gae.burden_cost_limit,NULL,ppaid.denom_amount,adl.burdenable_raw_cost)
1820 /* Bug 5645290 - End */
1821 BULK collect into
1822 l_project_id,
1823 l_task_id,
1824 l_transaction_source,
1825 l_line_type,
1826 l_cmt_number,
1827 l_cmt_distribution_id,
1828 l_cmt_header_id,
1829 l_description,
1830 l_expenditure_item_date,
1831 l_pa_period,
1832 l_gl_period,
1833 l_cmt_line_number,
1834 l_creation_date,
1835 l_approved_date,
1836 l_requestor_name,
1837 l_buyer_name,
1838 l_approved_flag,
1839 l_promised_date,
1840 l_need_by_date,
1841 l_organization_id,
1842 l_vendor_id,
1843 l_vendor_name,
1844 l_expenditure_type,
1845 l_expenditure_category,
1846 l_revenue_category,
1847 l_system_linkage_function,
1848 l_unit_of_measure,
1849 l_unit_price,
1850 l_ind_compiled_set_id,
1851 l_cmt_quantity,
1852 l_quantity_ordered,
1853 l_amount_ordered,
1854 l_orig_quantity_ordered,
1855 l_orig_amount_ordered,
1856 l_quantity_cancelled,
1857 l_amount_cancelled,
1858 l_quantity_delivered,
1859 l_quantity_invoiced,
1860 l_amount_invoiced,
1861 l_qty_out_delivery,
1862 l_amount_out_delivery,
1863 l_qty_out_invoiced,
1864 l_amount_out_invoiced,
1865 l_qty_overbilled,
1866 l_amount_overbilled,
1867 l_orig_txn_ref1,
1868 l_orig_txn_ref2,
1869 l_orig_txn_ref3,
1870 l_acct_raw_cost,
1871 l_acct_burdened_cost,
1872 l_denom_currency_code,
1873 l_denom_raw_cost,
1874 l_denom_burdened_cost,
1875 l_acct_currency_code,
1876 l_acct_rate_date,
1877 l_acct_rate_type,
1878 l_acct_exchange_rate,
1879 l_receipt_currency_code,
1880 l_receipt_currency_amount,
1881 l_receipt_exchange_rate,
1882 l_burdenable_raw_cost
1883 /* Commented for Bug 5645290
1884 FROM PA_PROJ_AP_INV_DISTRIBUTIONS PPAID,
1885 GMS_AWARD_DISTRIBUTIONS ADL
1886 WHERE PPAID.project_id = p_start_project_id */
1887 /* Added for Bug 5645290 */
1888 FROM PA_PROJ_AP_INV_DISTRIBUTIONS PPAID,
1889 GMS_AWARD_DISTRIBUTIONS ADL,
1890 gms_allowable_expenditures gae,
1891 gms_awards_all ga
1892 WHERE PPAID.project_id = p_start_project_id
1893 and ga.award_id = adl.award_id
1894 and gae.allowability_schedule_id = ga.allowable_schedule_id
1895 and gae.expenditure_type = PPAID.expenditure_type
1896 /* Bug 5645290 - End */
1897 /* R12 AP Lines uptake:record invoice distribution ID and
1898 invoice line number instead of line number which is no
1899 longer unique.*/
1900 --
1901 -- Bug : 4908630
1902 -- R12.PJ:XB2:DEV:GMS: APPSPERF:GMS: PACKAGE: GMSPAX3B.PLS ( SHARE MEM:11MB) 5 SQL
1903 --
1904 and adl.invoice_distribution_id = ppaid.invoice_distribution_id
1905 and ppaid.award_set_id = adl.award_set_id
1906 and adl.adl_line_num = 1
1907 and adl.invoice_id = ppaid.invoice_id
1908 and adl.distribution_line_number = ppaid.distribution_line_number
1909 and 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X')
1910 and NVL(adl.payment_status_flag , 'N') <> 'Y' ;
1911 -- bug fixes for 3755094 and 3736097
1912 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
1913
1914 if l_project_id.count <> 0 then
1915
1916 --
1917 -- 3614241
1918 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
1919 --
1920 FORALL indx in 1..l_project_id.count
1921
1922 INSERT INTO pa_commitment_txns
1923 ( cmt_line_id,
1924 project_id,
1925 task_id,
1926 transaction_source,
1927 line_type,
1928 cmt_number,
1929 cmt_distribution_id,
1930 cmt_header_id,
1931 description,
1932 expenditure_item_date,
1933 pa_period,
1934 gl_period,
1935 cmt_line_number,
1936 cmt_creation_date,
1937 cmt_approved_date,
1938 cmt_requestor_name,
1939 cmt_buyer_name,
1940 cmt_approved_flag,
1941 cmt_promised_date,
1942 cmt_need_by_date,
1943 organization_id,
1944 vendor_id,
1945 vendor_name,
1946 expenditure_type,
1947 expenditure_category,
1948 revenue_category,
1949 system_linkage_function,
1950 unit_of_measure,
1951 unit_price,
1952 cmt_ind_compiled_set_id,
1953 tot_cmt_raw_cost,
1954 tot_cmt_burdened_cost,
1955 tot_cmt_quantity,
1956 quantity_ordered,
1957 amount_ordered,
1958 original_quantity_ordered,
1959 original_amount_ordered,
1960 quantity_cancelled,
1961 amount_cancelled,
1962 quantity_delivered,
1963 amount_delivered,
1964 quantity_invoiced,
1965 amount_invoiced,
1966 quantity_outstanding_delivery,
1967 amount_outstanding_delivery,
1968 quantity_outstanding_invoice,
1969 amount_outstanding_invoice,
1970 quantity_overbilled,
1971 amount_overbilled,
1972 original_txn_reference1,
1973 original_txn_reference2,
1974 original_txn_reference3,
1975 last_update_date,
1976 last_updated_by,
1977 creation_date,
1978 created_by,
1979 last_update_login,
1980 request_id,
1981 program_application_id,
1982 program_id,
1983 program_update_date,
1984 burden_sum_source_run_id,
1985 burden_sum_dest_run_id,
1986 burden_sum_rejection_code,
1987 acct_raw_cost,
1988 acct_burdened_cost,
1989 denom_currency_code,
1990 denom_raw_cost,
1991 denom_burdened_cost,
1992 acct_currency_code,
1993 acct_rate_date,
1994 acct_rate_type,
1995 acct_exchange_rate,
1996 receipt_currency_code,
1997 receipt_currency_amount,
1998 receipt_exchange_rate,
1999 project_currency_code,
2000 project_rate_date,
2001 project_rate_type,
2002 project_exchange_rate,
2003 generation_error_flag,
2004 cmt_rejection_code
2005 )
2006 values (
2007 pa_txn_accums.cmt_line_id,
2008 l_project_id(indx),
2009 l_task_id(indx),
2010 l_transaction_source(indx),
2011 l_line_type(indx),
2012 l_cmt_number(indx),
2013 l_cmt_distribution_id(indx),
2014 l_cmt_header_id(indx),
2015 l_description(indx),
2016 l_expenditure_item_date(indx),
2017 l_pa_period(indx),
2018 l_gl_period(indx),
2019 l_cmt_line_number(indx),
2020 l_creation_date(indx),
2021 l_approved_date(indx),
2022 l_requestor_name(indx),
2023 l_buyer_name(indx),
2024 l_approved_flag(indx),
2025 l_promised_date(indx),
2026 l_need_by_date(indx),
2027 l_organization_id(indx),
2028 l_vendor_id(indx),
2029 l_vendor_name(indx),
2030 l_expenditure_type(indx),
2031 l_expenditure_category(indx),
2032 l_revenue_category(indx),
2033 l_system_linkage_function(indx),
2034 l_unit_of_measure(indx),
2035 l_unit_price(indx),
2036 to_number(null),
2037 to_number(null),
2038 to_number(null),
2039 l_cmt_quantity(indx),
2040 l_quantity_ordered(indx),
2041 l_amount_ordered(indx),
2042 l_orig_quantity_ordered(indx),
2043 l_orig_amount_ordered(indx),
2044 l_quantity_cancelled(indx),
2045 l_amount_cancelled(indx),
2046 l_quantity_delivered(indx),
2047 to_number(null),
2048 l_quantity_invoiced(indx),
2049 l_amount_invoiced(indx),
2050 l_qty_out_delivery(indx),
2051 l_amount_out_delivery(indx),
2052 l_qty_out_invoiced(indx),
2053 l_amount_out_invoiced(indx),
2054 l_qty_overbilled(indx),
2055 l_amount_overbilled(indx),
2056 l_orig_txn_ref1(indx),
2057 l_orig_txn_ref2(indx),
2058 l_orig_txn_ref3(indx),
2059 sysdate,
2060 l_last_updated_by,
2061 sysdate,
2062 l_created_by,
2063 l_last_update_login,
2064 l_request_id,
2065 l_program_application_id,
2066 l_program_id,
2067 null,
2068 -9999,
2069 null,
2070 null,
2071 l_acct_raw_cost(indx),
2072 l_acct_burdened_cost(indx),
2073 l_denom_currency_code(indx),
2074 l_denom_raw_cost(indx),
2075 l_denom_burdened_cost(indx),
2076 l_acct_currency_code(indx),
2077 l_acct_rate_date(indx),
2078 l_acct_rate_type(indx),
2079 l_acct_exchange_rate(indx),
2080 l_receipt_currency_code(indx),
2081 l_receipt_currency_amount(indx),
2082 l_receipt_exchange_rate(indx),
2083 null,
2084 to_date(null),
2085 null,
2086 to_number(null),
2087 'N',
2088 null ) ;
2089
2090 --
2091 -- 3614241
2092 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
2093 --
2094 FORALL indx in 1..l_project_id.count
2095 INSERT INTO pa_commitment_txns
2096 ( cmt_line_id,
2097 project_id,
2098 task_id,
2099 transaction_source,
2100 line_type,
2101 cmt_number,
2102 cmt_distribution_id,
2103 cmt_header_id,
2104 description,
2105 expenditure_item_date,
2106 pa_period,
2107 gl_period,
2108 cmt_line_number,
2109 cmt_creation_date,
2110 cmt_approved_date,
2111 cmt_requestor_name,
2112 cmt_buyer_name,
2113 cmt_approved_flag,
2114 cmt_promised_date,
2115 cmt_need_by_date,
2116 organization_id,
2117 vendor_id,
2118 vendor_name,
2119 expenditure_type,
2120 expenditure_category,
2121 revenue_category,
2122 system_linkage_function,
2123 unit_of_measure,
2124 unit_price,
2125 cmt_ind_compiled_set_id,
2126 tot_cmt_raw_cost,
2127 tot_cmt_burdened_cost,
2128 tot_cmt_quantity,
2129 quantity_ordered,
2130 amount_ordered,
2131 original_quantity_ordered,
2132 original_amount_ordered,
2133 quantity_cancelled,
2134 amount_cancelled,
2135 quantity_delivered,
2136 amount_delivered,
2137 quantity_invoiced,
2138 amount_invoiced,
2139 quantity_outstanding_delivery,
2140 amount_outstanding_delivery,
2141 quantity_outstanding_invoice,
2142 amount_outstanding_invoice,
2143 quantity_overbilled,
2144 amount_overbilled,
2145 original_txn_reference1,
2146 original_txn_reference2,
2147 original_txn_reference3,
2148 last_update_date,
2149 last_updated_by,
2150 creation_date,
2151 created_by,
2152 last_update_login,
2153 request_id,
2154 program_application_id,
2155 program_id,
2156 program_update_date,
2157 burden_sum_source_run_id,
2158 burden_sum_dest_run_id,
2159 burden_sum_rejection_code,
2160 acct_raw_cost,
2161 acct_burdened_cost,
2162 denom_currency_code,
2163 denom_raw_cost,
2164 denom_burdened_cost,
2165 acct_currency_code,
2166 acct_rate_date,
2167 acct_rate_type,
2168 acct_exchange_rate,
2169 receipt_currency_code,
2170 receipt_currency_amount,
2171 receipt_exchange_rate,
2172 project_currency_code,
2173 project_rate_date,
2174 project_rate_type,
2175 project_exchange_rate,
2176 generation_error_flag,
2177 cmt_rejection_code
2178 )
2179 select pa_txn_accums.cmt_line_id,
2180 l_project_id(indx),
2181 l_task_id(indx),
2182 l_transaction_source(indx),
2183 l_line_type(indx),
2184 l_cmt_number(indx),
2185 l_cmt_distribution_id(indx),
2186 l_cmt_header_id(indx),
2187 l_description(indx),
2188 l_expenditure_item_date(indx),
2189 l_pa_period(indx),
2190 l_gl_period(indx),
2191 l_cmt_line_number(indx),
2192 l_creation_date(indx),
2193 l_approved_date(indx),
2194 l_requestor_name(indx),
2195 l_buyer_name(indx),
2196 l_approved_flag(indx),
2197 l_promised_date(indx),
2198 l_need_by_date(indx),
2199 l_organization_id(indx),
2200 l_vendor_id(indx),
2201 l_vendor_name(indx),
2202 icc.expenditure_type,
2203 pet.expenditure_category,
2204 l_revenue_category(indx),
2205 'BTC',
2206 l_unit_of_measure(indx),
2207 l_unit_price(indx),
2208 l_ind_compiled_set_id(indx),
2209 to_number(null),
2210 to_number(null),
2211 0,
2212 0,
2213 0,
2214 0,
2215 0,
2216 0,
2217 0,
2218 0,
2219 to_number(null),
2220 0,
2221 0,
2222 0,
2223 0,
2224 0,
2225 0,
2226 0,
2227 0,
2228 l_orig_txn_ref1(indx),
2229 l_orig_txn_ref2(indx),
2230 l_orig_txn_ref3(indx),
2231 sysdate,
2232 l_last_updated_by,
2233 sysdate,
2234 l_created_by,
2235 l_last_update_login,
2236 l_request_id,
2237 l_program_application_id,
2238 l_program_id,
2239 null,
2240 -9999,
2241 null,
2242 null,
2243 0,
2244 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
2245 l_denom_currency_code(indx),
2246 0,
2247 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
2248 nvl(l_acct_exchange_rate(indx),1)),
2249 l_acct_currency_code(indx),
2250 l_acct_rate_date(indx),
2251 l_acct_rate_type(indx),
2252 l_acct_exchange_rate(indx),
2253 l_receipt_currency_code(indx),
2254 l_receipt_currency_amount(indx),
2255 l_receipt_exchange_rate(indx),
2256 null,
2257 to_date(null),
2258 null,
2259 to_number(null),
2260 'N',
2261 null
2262 FROM pa_ind_rate_sch_revisions irsr,
2263 pa_cost_base_exp_types cbet,
2264 pa_compiled_multipliers cm,
2265 pa_ind_cost_codes icc,
2266 pa_ind_rate_schedules_all_bg irs,
2267 pa_ind_compiled_sets ics,
2268 pa_expenditure_types pet
2269 WHERE cbet.cost_base_type = 'INDIRECT COST'
2270 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
2271 and l_acct_raw_cost(indx) <> 0
2272 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
2273 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
2274 and icc.ind_cost_code = cm.ind_cost_code
2275 and cbet.cost_base = cm.cost_base
2276 and cbet.expenditure_type = l_expenditure_type(indx)
2277 and irsr.cost_plus_structure = cbet.cost_plus_structure
2278 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
2279 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2280 and ics.organization_id = l_organization_id(indx)
2281 and ics.cost_base = cbet.cost_base
2282 and icc.expenditure_type = pet.expenditure_type ;
2283 end if ;
2284
2285 END create_cmt_txns ;
2286
2287 -- =====================
2288 -- Start of the comment
2289 -- Bug : 3599305
2290 -- API Name : is_project_type_sponsored
2291 -- Type : Public
2292 -- Pre_reqs : None
2293 -- Description : The purpose of this API is to check if project type is
2294 -- marked as sponsored in Grants Accounting.
2295 -- Called from : project type entry and project entry form
2296 -- Return Value : Y - Yes
2297 -- N - No
2298 --
2299 -- Parameters :
2300 -- IN :
2301 -- p_project_type varchar2
2302 -- End of comments
2303 -- ===============
2304 -- bug reference : 3596872
2305 -- problem : Please put the SELECT statement in the api :- GMS_PA_API3.IS_PROJECT_TYPE_SPONSORED in a cursor.
2306 -- If no record is found, the api should return 'N'.
2307 -- Otherwise, it is throwing a NO-DATA-FOUND error.
2308 -- Situation : When new project type is being created and not saved yet.
2309 --
2310 FUNCTION is_project_type_sponsored ( p_project_type IN VARCHAR2 )
2311 return varchar2 is
2312 l_spon_flag varchar2(1) ;
2313 begin
2314
2315 if p_project_type is NULL then
2316 return 'N' ;
2317 end if ;
2318
2319 l_spon_flag := 'N' ;
2320
2321 select sponsored_flag
2322 into l_spon_flag
2323 from gms_project_types
2324 where project_type = p_project_type ;
2325
2326 return NVL(l_spon_flag, 'N') ;
2327 -- bug 3596872
2328 EXCEPTION
2329 when no_data_found then
2330 return 'N' ;
2331 END is_project_type_sponsored ;
2332
2333 -- Bug 5726575
2334 -- =====================
2335 -- Start of the comment
2336 -- API Name : mark_impacted_enc_items
2337 -- Type : Public
2338 -- Pre_reqs : None
2339 -- Description : This procedure is called from
2340 -- pa_cost_plus.mark_impacted_exp_items (PAXCCPEB.pls).
2341 -- This procedure will mark all the burden impacted lines
2342 -- in gms_encumbrance_items_all.
2343 --
2344 -- Called from : pa_cost_plus.mark_impacted_exp_items
2345 -- Return Value : None
2346 --
2347 -- Parameters :
2348 -- IN :p_ind_compiled_set_id
2349 -- p_g_impacted_cost_bases
2350 -- p_g_cp_structure
2351 -- p_indirect_cost_code
2352 -- p_rate_sch_rev_id
2353 -- p_g_rate_sch_rev_id
2354 -- p_g_org_id
2355 -- p_g_org_override
2356 -- OUT :errbuf
2357 -- retcode
2358 -- End of comments
2359 -- ===============
2360 Procedure mark_impacted_enc_items (p_ind_compiled_set_id in number,
2361 p_g_impacted_cost_bases in varchar2,
2362 p_g_cp_structure in varchar2,
2363 p_indirect_cost_code in varchar2,
2364 p_rate_sch_rev_id in number,
2365 p_g_rate_sch_rev_id in number,
2366 p_g_org_id in number,
2367 p_g_org_override in number,
2368 errbuf OUT NOCOPY VARCHAR2,
2369 retcode OUT NOCOPY VARCHAR2)
2370 is
2371 x_last_updated_by number(15);
2372 x_last_update_login number(15);
2373 x_request_id number(15);
2374 begin
2375
2376 x_last_updated_by := FND_GLOBAL.USER_ID;
2377 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2378 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2379
2380 UPDATE gms_encumbrance_items_all ITEM
2381 SET ITEM.enc_distributed_flag =
2382 DECODE(ITEM.enc_distributed_flag,
2383 'Y', decode(ITEM.ind_compiled_set_id,
2384 p_ind_compiled_set_id, 'N',
2385 ITEM.enc_distributed_flag),
2386 ITEM.enc_distributed_flag),
2387 ITEM.adjustment_type =
2388 DECODE(ITEM.enc_distributed_flag,
2389 'Y', decode(ITEM.ind_compiled_set_id,
2390 p_ind_compiled_set_id, 'BURDEN_RECOMPILE',
2391 ITEM.adjustment_type),
2392 ITEM.adjustment_type),
2393 ITEM.ind_compiled_set_id = NULL,
2394 ITEM.last_update_date = SYSDATE,
2395 ITEM.last_updated_by = x_last_updated_by,
2396 ITEM.last_update_login = x_last_update_login,
2397 ITEM.request_id = x_request_id
2398 WHERE (ITEM.ind_compiled_set_id = p_ind_compiled_set_id
2399 AND ITEM.enc_distributed_flag = 'Y')
2400 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2401 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2402 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2403 AND exists (select /*+ NO_UNNEST */ null
2404 from pa_cost_base_exp_types cbet
2405 where cbet.cost_base = p_g_impacted_cost_bases
2406 AND cbet.cost_plus_structure = p_g_cp_structure
2407 AND cbet.cost_base_type = p_indirect_cost_code
2408 AND cbet.expenditure_type = ITEM.encumbrance_type)
2409 AND EXISTS (SELECT NULL
2410 FROM GMS_ENCUMBRANCES_ALL EXP,
2411 PA_IND_COMPILED_SETS ICS
2412 WHERE EXP.ENCUMBRANCE_ID = ITEM.ENCUMBRANCE_ID
2413 AND ICS.IND_COMPILED_SET_ID = ITEM.IND_COMPILED_SET_ID
2414 AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
2415 AND ICS.STATUS = 'H'
2416 AND ICS.IND_RATE_SCH_REVISION_ID = p_rate_sch_rev_id --Bug#5989869
2417 AND DECODE(p_rate_sch_rev_id ,p_g_rate_sch_rev_id ,DECODE(ICS.ORGANIZATION_ID,p_g_org_id ,p_g_org_override
2418 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(p_rate_sch_rev_id ,ICS.ORGANIZATION_ID))
2419 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(p_rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
2420 )
2421 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d');
2422 exception
2423 when others then
2424 errbuf := sqlcode;
2425 retcode := sqlerrm;
2426 end mark_impacted_enc_items;
2427
2428 -- Bug 5726575
2429 -- =====================
2430 -- Start of the comment
2431 -- API Name : mark_prev_rev_enc_items
2432 -- Type : Public
2433 -- Pre_reqs : None
2434 -- Description : This procedure is called from
2435 -- pa_cost_plus.mark_prev_rev_exp_items (PAXCCPEB.pls).
2436 -- This procedure will mark all the burden impacted lines
2437 -- in gms_encumbrance_items_all.
2438 --
2439 -- Called from : pa_cost_plus.mark_prev_rev_exp_items
2440 -- Return Value : None
2441 --
2442 -- Parameters :
2443 -- IN :p_compiled_set_id
2444 -- p_start_date
2445 -- p_end_date
2446 -- p_mode
2447 -- OUT :errbuf
2448 -- retcode
2449 -- End of comments
2450 -- ===============
2451 Procedure mark_prev_rev_enc_items (p_compiled_set_id in number,
2452 p_start_date in date,
2453 p_end_date in date,
2454 p_mode in varchar2,
2455 errbuf OUT NOCOPY VARCHAR2,
2456 retcode OUT NOCOPY VARCHAR2)
2457 is
2458 x_last_updated_by number(15);
2459 x_last_update_login number(15);
2460 x_request_id number(15);
2461 begin
2462 x_last_updated_by := FND_GLOBAL.USER_ID;
2463 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2464 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2465
2466 if p_mode = 'T' then --Update when task.cost_ind_sch_fixed_date is populated.
2467 --update commented for the Bug#5989869
2468 --Bug 9885825: Reverted the changes of bug 5989869
2469
2470 UPDATE gms_encumbrance_items_all ei
2471 SET enc_distributed_flag = 'N',
2472 adjustment_type ='BURDEN_RECOMPILE',
2473 last_update_date = SYSDATE,
2474 last_updated_by = x_last_updated_by,
2475 last_update_login = x_last_update_login,
2476 request_id = x_request_id
2477 WHERE ind_compiled_set_id = p_compiled_set_id
2478 AND EXISTS
2479 (SELECT task_id
2480 FROM pa_tasks task
2481 WHERE task.task_id = ei.task_id
2482 AND task.cost_ind_sch_fixed_date BETWEEN p_start_date AND
2483 NVL(p_end_date, cost_ind_sch_fixed_date))
2484 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
2485 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y'
2486 AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D'
2487 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N';
2488
2489
2490 elsif p_mode = 'N' then --Update based on task.cost_ind_sch_fixed_date IS NULL then go by enc_item_date
2491
2492 --update commented for the Bug#5989869
2493 --Bug 9885825: Reverted the changes of bug 5989869
2494
2495 UPDATE gms_encumbrance_items_all ei
2496 SET enc_distributed_flag = 'N' ,
2497 adjustment_type ='BURDEN_RECOMPILE',
2498 last_update_date = SYSDATE,
2499 last_updated_by = x_last_updated_by,
2500 last_update_login = x_last_update_login,
2501 request_id = x_request_id
2502 WHERE ind_compiled_set_id = p_compiled_set_id
2503 AND trunc(encumbrance_item_date) between trunc(p_start_date) and
2504 trunc(nvl(p_end_date, encumbrance_item_date))
2505 AND EXISTS
2506 (SELECT task_id
2507 FROM pa_tasks task
2508 WHERE task.task_id = ei.task_id
2509 AND task.cost_ind_sch_fixed_date IS NULL)
2510 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
2511 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
2512 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N'
2513 AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D';
2514
2515 elsif p_mode = 'O' then --Update based on enc_item_date
2516 UPDATE gms_encumbrance_items_all ei
2517 SET enc_distributed_flag = 'N' ,
2518 adjustment_type ='BURDEN_RECOMPILE',
2519 last_update_date = SYSDATE,
2520 last_updated_by = x_last_updated_by,
2521 last_update_login = x_last_update_login,
2522 request_id = x_request_id
2523 WHERE ei.ind_compiled_set_id = p_compiled_set_id
2524 AND ei.encumbrance_item_date between p_start_date and nvl(p_end_date, ei.encumbrance_item_date) --Bug#5989869; Removed TRUNC
2525 AND nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y'
2526 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
2527 --AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D' commented for bug#5989869
2528 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N';
2529 end if;
2530 exception
2531 when others then
2532 errbuf := sqlcode;
2533 retcode := sqlerrm;
2534 end mark_prev_rev_enc_items;
2535
2536 -- Bug 6761516
2537 -- =====================
2538 -- Start of the comment
2539 -- API Name : mark_enc_items_for_recalc
2540 -- Type : Public
2541 -- Pre_reqs : None
2542 -- Description : This procedure is called from
2543 -- GMSAWEAW.fmb and GMSICOVR.fmb.
2544 -- This procedure will mark all the associated encumbrance items for recalc
2545 -- on insertion, uodation or deletion in Award Management Compliance Screen
2546 -- or in Override Schedules Screen.
2547 --
2548 -- Called from : GMSAWEAW.fmb and GMSICOVR.fmb
2549 -- Return Value : None
2550 --
2551 -- Parameters :
2552 -- IN :p_ind_rate_sch_id
2553 -- p_award_id
2554 -- p_project_id
2555 -- p_task_id
2556 -- p_calling_form
2557 -- p_event
2558 -- p_idc_schedule_fixed_date
2559 -- OUT :errbuf
2560 -- retcode
2561 -- End of comments
2562 -- ===============
2563 Procedure mark_enc_items_for_recalc (p_ind_rate_sch_id in number,
2564 p_award_id in number,
2565 p_project_id in number,
2566 p_task_id in number,
2567 p_calling_form in varchar2,
2568 p_event in varchar2,
2569 p_idc_schedule_fixed_date in date,
2570 errbuf OUT NOCOPY VARCHAR2,
2571 retcode OUT NOCOPY VARCHAR2)
2572 is
2573 x_last_updated_by number(15);
2574 x_last_update_login number(15);
2575 x_request_id number(15);
2576 l_start_date_min date;
2577 l_end_date_max date;
2578 l_debug varchar2(1) ;
2579 l_test number(1);
2580 l_award_status gms_awards_all.status%TYPE ; --BUG 7225876
2581 l_close_date gms_awards_all.close_date%TYPE ; -- BUG 7225876
2582 l_project_closed varchar2(1); -- BUG 7225876
2583
2584 begin
2585 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N'); -- to generate debug messages
2586
2587 IF L_DEBUG = 'Y' THEN
2588 gms_error_pkg.gms_debug(' ARGUMENTS RECEIVED:: p_ind_rate_sch_id:' || p_ind_rate_sch_id || ',p_award_id:' || p_award_id, 'C');
2589 gms_error_pkg.gms_debug(',p_project_id:' || p_project_id || ',p_task_id:' || p_task_id || ',p_idc_schedule_fixed_date:' || p_idc_schedule_fixed_date, 'C');
2590 gms_error_pkg.gms_debug(',p_calling_form:' || p_calling_form || ',p_event:' || p_event, 'C');
2591 END IF;
2592
2593 x_last_updated_by := FND_GLOBAL.USER_ID;
2594 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2595 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2596
2597 begin /* added for bug 7225876 */
2598 select aw.status, aw.close_date
2599 into l_award_status,l_close_date
2600 from gms_awards_all aw
2601 where aw.award_id = p_award_id;
2602 exception
2603 when others then
2604 l_award_status := null;
2605 l_close_date := null;
2606 end;
2607
2608 if p_idc_schedule_fixed_date is null then -- mark only those enc items for recalc where enc date falls between any of the revisions of associated burden schedule
2609 IF L_DEBUG = 'Y' THEN
2610 gms_error_pkg.gms_debug('p_idc_schedule_fixed_date is null', 'C');
2611 END IF;
2612
2613 begin
2614 l_test := 0;
2615 select 1
2616 into l_test
2617 from pa_ind_rate_sch_revisions irsr
2618 where irsr.ind_rate_sch_id = p_ind_rate_sch_id
2619 and end_date_active is null;
2620 exception
2621 when no_data_found then
2622 l_test := 0;
2623 end;
2624
2625 if l_test = 0 then
2626 select min(start_date_active), max(end_date_active)
2627 into l_start_date_min, l_end_date_max
2628 from pa_ind_rate_sch_revisions irsr
2629 where irsr.ind_rate_sch_id = p_ind_rate_sch_id;
2630 else
2631 select min(start_date_active)
2632 into l_start_date_min
2633 from pa_ind_rate_sch_revisions irsr
2634 where irsr.ind_rate_sch_id = p_ind_rate_sch_id;
2635 end if;
2636
2637 if p_calling_form = 'GMSICOVR' then -- call from override schedule screen
2638 IF L_DEBUG = 'Y' THEN
2639 gms_error_pkg.gms_debug('Call is from OVERRIDE SCHEDULES SCREEN', 'C');
2640 END IF;
2641
2642 IF l_award_status <> 'CLOSED' AND l_close_date >= trunc (sysdate ) then /* bug 7225876 */
2643
2644 l_project_closed := pa_project_stus_utils.Is_Project_Closed(p_project_id);
2645
2646 UPDATE gms_encumbrance_items_all ITEM
2647 SET ITEM.enc_distributed_flag = 'N',
2648 ITEM.adjustment_type = 'BURDEN_RECALC',
2649 ITEM.ind_compiled_set_id = NULL,
2650 ITEM.last_update_date = SYSDATE,
2651 ITEM.last_updated_by = x_last_updated_by,
2652 ITEM.last_update_login = x_last_update_login,
2653 ITEM.request_id = x_request_id
2654 WHERE ITEM.enc_distributed_flag = 'Y'
2655 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2656 AND ITEM.project_id = p_project_id
2657 /* AND 1 = decode(p_task_id, NULL, 1,
2658 decode(ITEM.task_id ,p_task_id, 1, 2)) need to chech the top_task_id instead of task_id */
2659 /* in the following decode, if p_task_id is null, check if any other override for same award, project exists.
2660 if p_task id is not null, check if p_task_id = top_task_id for the enc. */
2661 /* calling new function item_task_validate */
2662 /* AND 1 = decode(p_task_id, NULL ,decode (1, (select 1
2663 from GMS_OVERRIDE_SCHEDULES GOS,
2664 pa_tasks TASK
2665 where GOS.award_id = p_award_id
2666 and GOS.project_id = p_project_id
2667 and nvl(ITEM.task_id,-99) = TASK.task_id
2668 and nvl(GOS.task_id,-99) = TASK.top_task_id
2669 and rownum = 1) ,2
2670 ,1)
2671 ,decode((select top_task_id
2672 from pa_tasks
2673 where task_id = ITEM.task_id), p_task_id ,1
2674 ,2))*/
2675 AND gms_pa_api3.item_task_validate(p_award_id, p_project_id, p_task_id, ITEM.task_id) = 'Y'
2676 AND l_project_closed <>'Y' --bug 7225876
2677 --bug 6967150 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2678 AND EXISTS ( select null
2679 from gms_award_distributions adl
2680 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2681 and adl.document_type = 'ENC' -- Added these three checks for bug 7225876
2682 and adl.adl_status = 'A'
2683 and adl.adl_line_num = 1
2684 and adl.award_id = p_award_id )
2685 AND ITEM.encumbrance_item_date between l_start_date_min
2686 and nvl(l_end_date_max,ITEM.encumbrance_item_date) -- dates corresponding to schedule revisions
2687 --bug 7225876 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2688 AND ITEM.ind_compiled_set_id > 0 -- bug 7225876
2689 AND 1 = decode(p_event, 'INSERT', 1
2690 , (select 1
2691 from pa_ind_compiled_sets ics,
2692 pa_ind_rate_sch_revisions irsr
2693 where ITEM.ind_compiled_set_id = ics.ind_compiled_set_id
2694 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2695 and irsr.ind_rate_sch_id = p_ind_rate_sch_id
2696 and rownum = 1));
2697 end if;
2698
2699 elsif p_calling_form = 'GMSAWEAW' then -- call from award management compliance tab
2700
2701 IF L_DEBUG = 'Y' THEN
2702 gms_error_pkg.gms_debug('Call is from AWARD MANAGEMENT SCREEN', 'C');
2703 END IF;
2704
2705 IF l_award_status <> 'CLOSED' AND l_close_date >= trunc (sysdate ) then -- bug 7225876
2706
2707 UPDATE gms_encumbrance_items_all ITEM
2708 SET ITEM.enc_distributed_flag = 'N',
2709 ITEM.adjustment_type = 'BURDEN_RECALC',
2710 ITEM.ind_compiled_set_id = NULL,
2711 ITEM.last_update_date = SYSDATE,
2712 ITEM.last_updated_by = x_last_updated_by,
2713 ITEM.last_update_login = x_last_update_login,
2714 ITEM.request_id = x_request_id
2715 WHERE ITEM.enc_distributed_flag = 'Y'
2716 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2717 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y' -- bug 7225876
2718 -- bug 7225876 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2719 AND EXISTS
2720 ( select null
2721 from gms_award_distributions adl
2722 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2723 and adl.award_id = p_award_id
2724 and adl.document_type = 'ENC' -- Added these three conditions for bug 7225876
2725 and adl.adl_status = 'A'
2726 and adl.adl_line_num = 1)
2727 AND ITEM.encumbrance_item_date between l_start_date_min and nvl(l_end_date_max,ITEM.encumbrance_item_date)
2728 -- bug 6967150 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2729 AND ITEM.ind_compiled_set_id > 0 -- bug 7225876
2730 AND NOT EXISTS
2731 ( select 1
2732 from GMS_OVERRIDE_SCHEDULES GOS,
2733 pa_tasks TASK
2734 where GOS.award_id = p_award_id
2735 and GOS.project_id = ITEM.project_id
2736 and ITEM.task_id = TASK.task_id
2737 and nvl(GOS.task_id, TASK.top_task_id) = TASK.top_task_id);
2738 end if;
2739 end if;
2740 else -- p_idc_schedule_fixed_date is not null, mark all associated enc items for recalc
2741 IF L_DEBUG = 'Y' THEN
2742 gms_error_pkg.gms_debug('p_idc_schedule_fixed_date is not null', 'C');
2743 END IF;
2744
2745 if p_calling_form = 'GMSICOVR' then -- call from override schedule screen
2746
2747 IF L_DEBUG = 'Y' THEN
2748 gms_error_pkg.gms_debug('call is from override schedule screen', 'C');
2749 END IF;
2750
2751 IF l_award_status <> 'CLOSED' AND l_close_date >= trunc (sysdate ) then -- bug 7225876
2752
2753 l_project_closed := pa_project_stus_utils.Is_Project_Closed(p_project_id);
2754
2755 UPDATE gms_encumbrance_items_all ITEM
2756 SET ITEM.enc_distributed_flag = 'N',
2757 ITEM.adjustment_type = 'BURDEN_RECALC',
2758 ITEM.ind_compiled_set_id = NULL,
2759 ITEM.last_update_date = SYSDATE,
2760 ITEM.last_updated_by = x_last_updated_by,
2761 ITEM.last_update_login = x_last_update_login,
2762 ITEM.request_id = x_request_id
2763 WHERE ITEM.enc_distributed_flag = 'Y'
2764 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2765 AND ITEM.project_id = p_project_id
2766 /* AND 1 = decode(p_task_id, NULL, 1,
2767 decode(ITEM.task_id ,p_task_id, 1, 2)) need to chech the top_task_id instead of task_id */
2768 /* in the following decode, if p_task_id is null, check if any other override for same award, project exists.
2769 if p_task id is not null, check if p_task_id = top_task_id for the enc. */
2770 /* calling new function item_task_validate */
2771 /* AND 1 = decode(p_task_id, NULL ,decode (1, (select 1
2772 from GMS_OVERRIDE_SCHEDULES GOS,
2773 pa_tasks TASK
2774 where GOS.award_id = p_award_id
2775 and GOS.project_id = p_project_id
2776 and nvl(ITEM.task_id,-99) = TASK.task_id
2777 and nvl(GOS.task_id,-99) = TASK.top_task_id
2778 and rownum = 1) ,2
2779 ,1)
2780 ,decode((select top_task_id
2781 from pa_tasks
2782 where task_id = ITEM.task_id), p_task_id ,1
2783 ,2))*/
2784 AND gms_pa_api3.item_task_validate(p_award_id, p_project_id, p_task_id, ITEM.task_id) = 'Y'
2785 AND l_project_closed <>'Y' --bug 7225876
2786 --bug6967150 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2787 AND EXISTS ( select null
2788 from gms_award_distributions adl
2789 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2790 and adl.document_type = 'ENC' -- Added the three conditions for bug 7225876
2791 and adl.adl_status = 'A'
2792 and adl.adl_line_num = 1
2793 and adl.award_id = p_award_id )
2794 --bug 7225876 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2795 AND ITEM.ind_compiled_set_id > 0 -- bug 7225876
2796 AND 1 = decode(p_event, 'INSERT', 1
2797 , (select 1
2798 from pa_ind_compiled_sets ics,
2799 pa_ind_rate_sch_revisions irsr
2800 where ITEM.ind_compiled_set_id = ics.ind_compiled_set_id
2801 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2802 and irsr.ind_rate_sch_id = p_ind_rate_sch_id
2803 and rownum = 1));
2804 End if;
2805
2806 elsif p_calling_form = 'GMSAWEAW' then -- call from award management compliance tab
2807
2808 IF L_DEBUG = 'Y' THEN
2809 gms_error_pkg.gms_debug('Call is from award management screen', 'C');
2810 END IF;
2811
2812 IF l_award_status <> 'CLOSED' AND l_close_date >= trunc (sysdate ) then
2813
2814 UPDATE gms_encumbrance_items_all ITEM
2815 SET ITEM.enc_distributed_flag = 'N',
2816 ITEM.adjustment_type = 'BURDEN_RECALC',
2817 ITEM.ind_compiled_set_id = NULL,
2818 ITEM.last_update_date = SYSDATE,
2819 ITEM.last_updated_by = x_last_updated_by,
2820 ITEM.last_update_login = x_last_update_login,
2821 ITEM.request_id = x_request_id
2822 WHERE ITEM.enc_distributed_flag = 'Y'
2823 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2824 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y' --BUG 7225876
2825 --BUG 6967150 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2826 AND EXISTS ( select null
2827 from gms_award_distributions adl
2828 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2829 and adl.document_type = 'ENC' -- Added following three condition for bug 7225876
2830 and adl.adl_status = 'A'
2831 and adl.adl_line_num = 1
2832 and adl.award_id = p_award_id )
2833 AND ITEM.ind_compiled_set_id >0 -- bug 7225876
2834 AND NOT EXISTS
2835 ( select 1
2836 from GMS_OVERRIDE_SCHEDULES GOS,
2837 pa_tasks TASK
2838 where GOS.award_id = p_award_id
2839 and GOS.project_id = ITEM.project_id
2840 and TASK.task_id = item.task_id
2841 and nvl(GOS.task_id, TASK.top_task_id) = TASK.top_task_id);
2842 END IF;
2843 --BUG 7225876 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2844
2845 end if;
2846 end if;
2847 exception
2848 when others then
2849 IF L_DEBUG = 'Y' THEN
2850 gms_error_pkg.gms_debug('IN EXCEPTION BLOCK', 'C');
2851 END IF;
2852 errbuf := sqlcode;
2853 retcode := sqlerrm;
2854 end mark_enc_items_for_recalc;
2855
2856 -- Bug 6761516
2857 -- =====================
2858 -- Start of the comment
2859 -- API Name : item_task_validate
2860 -- Type : Public
2861 -- Pre_reqs : None
2862 -- Description : This function is called from mark_enc_items_for_recalc
2863 -- to perform item validations.
2864 -- 1. If record being inserted/updated/deleted from overrides schedule
2865 -- screen does not have task details, then this function returns 'N'
2866 -- if any other override exists for same project, award ,top_task for
2867 -- the task of enc. in picture combination, else returns 'Y'
2868 -- 2. If record being inserted/updated/deleted from overrides schedule
2869 -- screen has task details, then just match the top task of the enc.
2870 -- in picture with the task on the record.
2871 -- Parameters : p_award_id number,
2872 -- p_project_id number,
2873 -- p_task_id number,
2874 -- p_item_task_id number
2875 -- Return Value : 'Y' - Encumbrance needs to be marked for recalc.
2876 -- 'N' - Encumbrance should not be marked for recalc.
2877 --
2878 -- End of comments
2879 -- ===============
2880
2881 function item_task_validate (p_award_id number,
2882 p_project_id number,
2883 p_task_id number,
2884 p_item_task_id number)
2885 return varchar2 is
2886
2887 l_test number ;
2888 l_max_indx number ;
2889 l_return varchar2(1);
2890
2891 begin
2892
2893 FOR indx in 1..g_task_id_tab.count loop
2894 if g_task_id_tab(indx) = p_item_task_id then
2895 gms_error_pkg.gms_debug('Results already available for task_id:' || g_task_id_tab(indx) || ',value:' || g_test_tab(indx), 'C');
2896 return g_test_tab(indx);
2897 end if;
2898 end loop;
2899
2900 gms_error_pkg.gms_debug('First Call to task_id:' || p_item_task_id, 'C');
2901 if p_task_id is NULL then
2902
2903 begin
2904 l_test := 0;
2905 select 1
2906 into l_test
2907 from GMS_OVERRIDE_SCHEDULES GOS,
2908 pa_tasks TASK
2909 where GOS.award_id = p_award_id
2910 and GOS.project_id = p_project_id
2911 and p_item_task_id = TASK.task_id
2912 and nvl(GOS.task_id,-99) = TASK.top_task_id
2913 and rownum = 1;
2914 exception
2915 when no_data_found then
2916 l_test := 0;
2917 end;
2918
2919 l_max_indx := g_task_id_tab.count;
2920 g_task_id_tab(l_max_indx+1) := p_item_task_id;
2921 if l_test = 1 then
2922 -- as override match is found, these transactions should not be marked for recalc
2923 -- hence returning 'N'
2924 l_return := 'N';
2925 else
2926 -- as override match is not found, these transactions should be marked for recalc
2927 -- hence returning 'Y'
2928 l_return := 'Y';
2929 end if;
2930 g_test_tab(l_max_indx+1) := l_return;
2931 return(l_return);
2932
2933 else -- p_task id is not null
2934
2935 begin
2936 l_test := 0;
2937 select 1
2938 into l_test
2939 from pa_tasks
2940 where task_id = p_item_task_id
2941 and top_task_id = p_task_id;
2942 exception
2943 when no_data_found then
2944 l_test := 0;
2945 end;
2946
2947 l_max_indx := g_task_id_tab.count;
2948 g_task_id_tab(l_max_indx+1) := p_item_task_id;
2949 if l_test = 1 then
2950 -- as enc task_id matches with p_task_id, these transactions should be marked for recalc
2951 -- hence returning 'Y'
2952 l_return := 'Y';
2953 else
2954 -- as enc task_id does not match with p_task_id, these transactions should not be marked for recalc
2955 -- hence returning 'N'
2956 l_return := 'N';
2957 end if;
2958 g_test_tab(l_max_indx+1) := l_return;
2959 return(l_return);
2960
2961 end if;
2962
2963 end item_task_validate;
2964 /* Bug 6761516 end */
2965
2966 END gms_pa_api3;