[Home] [Help]
PACKAGE BODY: APPS.GMS_PA_API3
Source
1 PACKAGE BODY gms_pa_api3 AS
2 /* $Header: gmspax3b.pls 120.8 2008/01/24 09:00:32 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 original_txn_reference1,
548 original_txn_reference2,
549 original_txn_reference3,
550 SYSDATE,
551 l_last_updated_by,
552 SYSDATE,
553 l_created_by,
554 l_last_update_login,
555 l_request_id,
556 l_program_application_id,
557 l_program_id,
558 NULL,
559 -9999,
560 NULL,
561 NULL,
562 acct_raw_cost,
563 acct_burdened_cost,
564 denom_currency_code,
565 denom_raw_cost,
566 denom_burdened_cost,
567 acct_currency_code,
568 acct_rate_date,
569 acct_rate_type,
570 acct_exchange_rate,
571 receipt_currency_code,
572 receipt_currency_amount,
573 receipt_exchange_rate,
574 NULL,
575 TO_DATE(NULL),
576 NULL,
577 TO_NUMBER(NULL),
578 'N',
579 NULL
580 FROM gms_enc_psi_v
581 WHERE project_id = p_start_project_id
582 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
583 -- bug fixes for 3755094 and 3736097
584 AND NVL(system_linkage_function,'X') =
585 NVL(NVL(p_system_linkage_function,system_linkage_function),'X');
586
587 --
588 -- 3614241
589 -- Fetch the commitment cost from gms_commitments_override_v into bulk araray elements.
590 -- Only Raw cost is fetched here.
591
592 -- Requisition Insert (Raw Cost)
593 --
594 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
595 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
596 --
597 SELECT
598 pprd.project_id,
599 pprd.task_id ,
600 'ORACLE_PURCHASING' ,
601 'R' ,
602 pprd.req_number ,
603 pprd.req_distribution_id ,
604 pprd.requisition_header_id ,
605 pprd.item_description ,
606 pprd.expenditure_item_date ,
607 l_cur_pa_period,
608 l_cur_gl_period,
609 pprd.req_line ,
610 pprd.creation_date ,
611 to_date(null) ,
612 pprd.requestor_name ,
613 to_char(null) ,
614 pprd.approved_flag ,
615 to_date(null) ,
616 pprd.need_by_date ,
617 pprd.expenditure_organization_id ,
618 pprd.vendor_id ,
619 pprd.vendor_name ,
620 pprd.expenditure_type ,
621 pprd.expenditure_category ,
622 pprd.revenue_category ,
623 'VI' ,
624 pprd.unit ,
625 pprd.unit_price ,
626 adl.ind_compiled_set_id,
627 pprd.quantity ,
628 to_number(null) ,
629 to_number(null) ,
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 NULL ,
644 NULL ,
645 NULL ,
646 pprd.amount ,
647 pprd.amount ,
648 pprd.denom_currency_code ,
649 pprd.denom_amount ,
650 pprd.denom_amount ,
651 pprd.acct_currency_code ,
652 pprd.acct_rate_date ,
653 pprd.acct_rate_type ,
654 pprd.acct_exchange_rate ,
655 to_char(null) ,
656 to_number(null) ,
657 to_number(null),
658 adl.burdenable_raw_cost
659 BULK collect into
660 l_project_id,
661 l_task_id,
662 l_transaction_source,
663 l_line_type,
664 l_cmt_number,
665 l_cmt_distribution_id,
666 l_cmt_header_id,
667 l_description,
668 l_expenditure_item_date,
669 l_pa_period,
670 l_gl_period,
671 l_cmt_line_number,
672 l_creation_date,
673 l_approved_date,
674 l_requestor_name,
675 l_buyer_name,
676 l_approved_flag,
677 l_promised_date,
678 l_need_by_date,
679 l_organization_id,
680 l_vendor_id,
681 l_vendor_name,
682 l_expenditure_type,
683 l_expenditure_category,
684 l_revenue_category,
685 l_system_linkage_function,
686 l_unit_of_measure,
687 l_unit_price,
688 l_ind_compiled_set_id,
689 l_cmt_quantity,
690 l_quantity_ordered,
691 l_amount_ordered,
692 l_orig_quantity_ordered,
693 l_orig_amount_ordered,
694 l_quantity_cancelled,
695 l_amount_cancelled,
696 l_quantity_delivered,
697 l_quantity_invoiced,
698 l_amount_invoiced,
699 l_qty_out_delivery,
700 l_amount_out_delivery,
701 l_qty_out_invoiced,
702 l_amount_out_invoiced,
703 l_qty_overbilled,
704 l_amount_overbilled,
705 l_orig_txn_ref1,
706 l_orig_txn_ref2,
707 l_orig_txn_ref3,
708 l_acct_raw_cost,
709 l_acct_burdened_cost,
710 l_denom_currency_code,
711 l_denom_raw_cost,
712 l_denom_burdened_cost,
713 l_acct_currency_code,
714 l_acct_rate_date,
715 l_acct_rate_type,
716 l_acct_exchange_rate,
717 l_receipt_currency_code,
718 l_receipt_currency_amount,
719 l_receipt_exchange_rate,
720 l_burdenable_raw_cost
721 FROM PA_PROJ_REQ_DISTRIBUTIONS PPRD,
722 gms_award_distributions adl
723 WHERE PPRD.project_id = p_start_project_id
724 --
725 -- Bug : 4908630
726 -- R12.PJ:XB2:DEV:GMS: APPSPERF:GMS: PACKAGE: GMSPAX3B.PLS ( SHARE MEM:11MB) 5 SQL
727 --
728 and adl.distribution_id = pprd.req_distribution_id
729 and pprd.award_set_id = adl.award_set_id
730 and adl.adl_line_num= 1
731 -- bug fixes for 3755094 and 3736097
732 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
733 AND 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X');
734
735 if l_project_id.count <> 0 then
736
737 --
738 -- 3614241
739 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
740 --
741 FORALL indx in 1..l_project_id.count
742 INSERT INTO pa_commitment_txns
743 ( cmt_line_id,
744 project_id,
745 task_id,
746 transaction_source,
747 line_type,
748 cmt_number,
749 cmt_distribution_id,
750 cmt_header_id,
751 description,
752 expenditure_item_date,
753 pa_period,
754 gl_period,
755 cmt_line_number,
756 cmt_creation_date,
757 cmt_approved_date,
758 cmt_requestor_name,
759 cmt_buyer_name,
760 cmt_approved_flag,
761 cmt_promised_date,
762 cmt_need_by_date,
763 organization_id,
764 vendor_id,
765 vendor_name,
766 expenditure_type,
767 expenditure_category,
768 revenue_category,
769 system_linkage_function,
770 unit_of_measure,
771 unit_price,
772 cmt_ind_compiled_set_id,
773 tot_cmt_raw_cost,
774 tot_cmt_burdened_cost,
775 tot_cmt_quantity,
776 quantity_ordered,
777 amount_ordered,
778 original_quantity_ordered,
779 original_amount_ordered,
780 quantity_cancelled,
781 amount_cancelled,
782 quantity_delivered,
783 amount_delivered,
784 quantity_invoiced,
785 amount_invoiced,
786 quantity_outstanding_delivery,
787 amount_outstanding_delivery,
788 quantity_outstanding_invoice,
789 amount_outstanding_invoice,
790 quantity_overbilled,
791 amount_overbilled,
792 original_txn_reference1,
793 original_txn_reference2,
794 original_txn_reference3,
795 last_update_date,
796 last_updated_by,
797 creation_date,
798 created_by,
799 last_update_login,
800 request_id,
801 program_application_id,
802 program_id,
803 program_update_date,
804 burden_sum_source_run_id,
805 burden_sum_dest_run_id,
806 burden_sum_rejection_code,
807 acct_raw_cost,
808 acct_burdened_cost,
809 denom_currency_code,
810 denom_raw_cost,
811 denom_burdened_cost,
812 acct_currency_code,
813 acct_rate_date,
814 acct_rate_type,
815 acct_exchange_rate,
816 receipt_currency_code,
817 receipt_currency_amount,
818 receipt_exchange_rate,
819 project_currency_code,
820 project_rate_date,
821 project_rate_type,
822 project_exchange_rate,
823 generation_error_flag,
824 cmt_rejection_code
825 )
826 values (
827 pa_txn_accums.cmt_line_id,
828 l_project_id(indx),
829 l_task_id(indx),
830 l_transaction_source(indx),
831 l_line_type(indx),
832 l_cmt_number(indx),
833 l_cmt_distribution_id(indx),
834 l_cmt_header_id(indx),
835 l_description(indx),
836 l_expenditure_item_date(indx),
837 l_pa_period(indx),
838 l_gl_period(indx),
839 l_cmt_line_number(indx),
840 l_creation_date(indx),
841 l_approved_date(indx),
842 l_requestor_name(indx),
843 l_buyer_name(indx),
844 l_approved_flag(indx),
845 l_promised_date(indx),
846 l_need_by_date(indx),
847 l_organization_id(indx),
848 l_vendor_id(indx),
849 l_vendor_name(indx),
850 l_expenditure_type(indx),
851 l_expenditure_category(indx),
852 l_revenue_category(indx),
853 l_system_linkage_function(indx),
854 l_unit_of_measure(indx),
855 l_unit_price(indx),
856 to_number(null),
857 to_number(null),
858 to_number(null),
859 l_cmt_quantity(indx),
860 l_quantity_ordered(indx),
861 l_amount_ordered(indx),
862 l_orig_quantity_ordered(indx),
863 l_orig_amount_ordered(indx),
864 l_quantity_cancelled(indx),
865 l_amount_cancelled(indx),
866 l_quantity_delivered(indx),
867 to_number(null),
868 l_quantity_invoiced(indx),
869 l_amount_invoiced(indx),
870 l_qty_out_delivery(indx),
871 l_amount_out_delivery(indx),
872 l_qty_out_invoiced(indx),
873 l_amount_out_invoiced(indx),
874 l_qty_overbilled(indx),
875 l_amount_overbilled(indx),
876 l_orig_txn_ref1(indx),
877 l_orig_txn_ref2(indx),
878 l_orig_txn_ref3(indx),
879 sysdate,
880 l_last_updated_by,
881 sysdate,
882 l_created_by,
883 l_last_update_login,
884 l_request_id,
885 l_program_application_id,
886 l_program_id,
887 null,
888 -9999,
889 null,
890 null,
891 l_acct_raw_cost(indx),
892 l_acct_burdened_cost(indx),
893 l_denom_currency_code(indx),
894 l_denom_raw_cost(indx),
895 l_denom_burdened_cost(indx),
896 l_acct_currency_code(indx),
897 l_acct_rate_date(indx),
898 l_acct_rate_type(indx),
899 l_acct_exchange_rate(indx),
900 l_receipt_currency_code(indx),
901 l_receipt_currency_amount(indx),
902 l_receipt_exchange_rate(indx),
903 null,
904 to_date(null),
905 null,
906 to_number(null),
907 'N',
908 null ) ;
909
910 --
911 -- 3614241
912 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
913 --
914 FORALL indx in 1..l_project_id.count
915 INSERT INTO pa_commitment_txns
916 ( cmt_line_id,
917 project_id,
918 task_id,
919 transaction_source,
920 line_type,
921 cmt_number,
922 cmt_distribution_id,
923 cmt_header_id,
924 description,
925 expenditure_item_date,
926 pa_period,
927 gl_period,
928 cmt_line_number,
929 cmt_creation_date,
930 cmt_approved_date,
931 cmt_requestor_name,
932 cmt_buyer_name,
933 cmt_approved_flag,
934 cmt_promised_date,
935 cmt_need_by_date,
936 organization_id,
937 vendor_id,
938 vendor_name,
939 expenditure_type,
940 expenditure_category,
941 revenue_category,
942 system_linkage_function,
943 unit_of_measure,
944 unit_price,
945 cmt_ind_compiled_set_id,
946 tot_cmt_raw_cost,
947 tot_cmt_burdened_cost,
948 tot_cmt_quantity,
949 quantity_ordered,
950 amount_ordered,
951 original_quantity_ordered,
952 original_amount_ordered,
953 quantity_cancelled,
954 amount_cancelled,
955 quantity_delivered,
956 amount_delivered,
957 quantity_invoiced,
958 amount_invoiced,
959 quantity_outstanding_delivery,
960 amount_outstanding_delivery,
961 quantity_outstanding_invoice,
962 amount_outstanding_invoice,
963 quantity_overbilled,
964 amount_overbilled,
965 original_txn_reference1,
966 original_txn_reference2,
967 original_txn_reference3,
968 last_update_date,
969 last_updated_by,
970 creation_date,
971 created_by,
972 last_update_login,
973 request_id,
974 program_application_id,
975 program_id,
976 program_update_date,
977 burden_sum_source_run_id,
978 burden_sum_dest_run_id,
979 burden_sum_rejection_code,
980 acct_raw_cost,
981 acct_burdened_cost,
982 denom_currency_code,
983 denom_raw_cost,
984 denom_burdened_cost,
985 acct_currency_code,
986 acct_rate_date,
987 acct_rate_type,
988 acct_exchange_rate,
989 receipt_currency_code,
990 receipt_currency_amount,
991 receipt_exchange_rate,
992 project_currency_code,
993 project_rate_date,
994 project_rate_type,
995 project_exchange_rate,
996 generation_error_flag,
997 cmt_rejection_code
998 )
999 select pa_txn_accums.cmt_line_id,
1000 l_project_id(indx),
1001 l_task_id(indx),
1002 l_transaction_source(indx),
1003 l_line_type(indx),
1004 l_cmt_number(indx),
1005 l_cmt_distribution_id(indx),
1006 l_cmt_header_id(indx),
1007 l_description(indx),
1008 l_expenditure_item_date(indx),
1009 l_pa_period(indx),
1010 l_gl_period(indx),
1011 l_cmt_line_number(indx),
1012 l_creation_date(indx),
1013 l_approved_date(indx),
1014 l_requestor_name(indx),
1015 l_buyer_name(indx),
1016 l_approved_flag(indx),
1017 l_promised_date(indx),
1018 l_need_by_date(indx),
1019 l_organization_id(indx),
1020 l_vendor_id(indx),
1021 l_vendor_name(indx),
1022 icc.expenditure_type,
1023 pet.expenditure_category,
1024 l_revenue_category(indx),
1025 'BTC',
1026 l_unit_of_measure(indx),
1027 l_unit_price(indx),
1028 l_ind_compiled_set_id(indx),
1029 to_number(null),
1030 to_number(null),
1031 0,
1032 0,
1033 0,
1034 0,
1035 0,
1036 0,
1037 0,
1038 0,
1039 to_number(null),
1040 0,
1041 0,
1042 0,
1043 0,
1044 0,
1045 0,
1046 0,
1047 0,
1048 l_orig_txn_ref1(indx),
1049 l_orig_txn_ref2(indx),
1050 l_orig_txn_ref3(indx),
1051 sysdate,
1052 l_last_updated_by,
1053 sysdate,
1054 l_created_by,
1055 l_last_update_login,
1056 l_request_id,
1057 l_program_application_id,
1058 l_program_id,
1059 null,
1060 -9999,
1061 null,
1062 null,
1063 0,
1064 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
1065 l_denom_currency_code(indx),
1066 0,
1067 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
1068 nvl(l_acct_exchange_rate(indx),1)),
1069 l_acct_currency_code(indx),
1070 l_acct_rate_date(indx),
1071 l_acct_rate_type(indx),
1072 l_acct_exchange_rate(indx),
1073 l_receipt_currency_code(indx),
1074 l_receipt_currency_amount(indx),
1075 l_receipt_exchange_rate(indx),
1076 null,
1077 to_date(null),
1078 null,
1079 to_number(null),
1080 'N',
1081 null
1082 FROM pa_ind_rate_sch_revisions irsr,
1083 pa_cost_base_exp_types cbet,
1084 pa_compiled_multipliers cm,
1085 pa_ind_cost_codes icc,
1086 pa_ind_rate_schedules_all_bg irs,
1087 pa_ind_compiled_sets ics,
1088 pa_expenditure_types pet
1089 WHERE cbet.cost_base_type = 'INDIRECT COST'
1090 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
1091 -- and l_acct_raw_cost(indx) <> 0
1092 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
1093 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
1094 and icc.ind_cost_code = cm.ind_cost_code
1095 and cbet.cost_base = cm.cost_base
1096 and cbet.expenditure_type = l_expenditure_type(indx)
1097 and irsr.cost_plus_structure = cbet.cost_plus_structure
1098 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1099 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1100 and ics.organization_id = l_organization_id(indx)
1101 and ics.cost_base = cbet.cost_base
1102 and icc.expenditure_type = pet.expenditure_type ;
1103 end if ;
1104
1105 l_project_id.delete ;
1106 l_task_id.delete ;
1107 l_transaction_source.delete ;
1108 l_line_type.delete ;
1109 l_cmt_number.delete ;
1110 l_cmt_distribution_id.delete ;
1111 l_cmt_header_id.delete ;
1112 l_description.delete ;
1113 l_expenditure_item_date.delete ;
1114 l_pa_period.delete ;
1115 l_gl_period.delete ;
1116 l_cmt_line_number.delete ;
1117 l_creation_date.delete ;
1118 l_approved_date.delete ;
1119 l_requestor_name.delete ;
1120 l_buyer_name.delete ;
1121 l_approved_flag.delete ;
1122 l_promised_date.delete ;
1123 l_need_by_date.delete ;
1124 l_organization_id.delete ;
1125 l_vendor_id.delete ;
1126 l_vendor_name.delete ;
1127 l_expenditure_type.delete ;
1128 l_expenditure_category.delete ;
1129 l_revenue_category.delete ;
1130 l_system_linkage_function.delete ;
1131 l_unit_of_measure.delete ;
1132 l_unit_price.delete ;
1133 l_ind_compiled_set_id.delete ;
1134 l_cmt_quantity.delete ;
1135 l_quantity_ordered.delete ;
1136 l_amount_ordered.delete ;
1137 l_orig_quantity_ordered.delete ;
1138 l_orig_amount_ordered.delete ;
1139 l_quantity_cancelled.delete ;
1140 l_amount_cancelled.delete ;
1141 l_quantity_delivered.delete ;
1142 l_quantity_invoiced.delete ;
1143 l_amount_invoiced.delete ;
1144 l_qty_out_delivery.delete ;
1145 l_amount_out_delivery.delete ;
1146 l_qty_out_invoiced.delete ;
1147 l_amount_out_invoiced.delete ;
1148 l_qty_overbilled.delete ;
1149 l_amount_overbilled.delete ;
1150 l_orig_txn_ref1.delete ;
1151 l_orig_txn_ref2.delete ;
1152 l_orig_txn_ref3.delete ;
1153 l_acct_raw_cost.delete ;
1154 l_acct_burdened_cost.delete ;
1155 l_denom_currency_code.delete ;
1156 l_denom_raw_cost.delete ;
1157 l_denom_burdened_cost.delete ;
1158 l_acct_currency_code.delete ;
1159 l_acct_rate_date.delete ;
1160 l_acct_rate_type.delete ;
1161 l_acct_exchange_rate.delete ;
1162 l_receipt_currency_code.delete ;
1163 l_receipt_currency_amount.delete ;
1164 l_receipt_exchange_rate.delete ;
1165 l_burdenable_raw_cost.delete ;
1166
1167 --
1168 -- Purchase Order Inserts... (Raw)
1169 --
1170 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
1171 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
1172 --
1173
1174 SELECT
1175 pppd.project_id,
1176 pppd.task_id,
1177 'ORACLE_PURCHASING',
1178 'P',
1179 pppd.po_number,
1180 pppd.po_distribution_id,
1181 pppd.po_header_id,
1182 pppd.item_description,
1183 pppd.expenditure_item_date,
1184 l_cur_pa_period, /* Added for commitment change request*/
1185 l_cur_gl_period, /* Added for commitment change request*/
1186 pppd.po_line,
1187 pppd.creation_date,
1188 pppd.approved_date,
1189 pppd.requestor_name,
1190 pppd.buyer_name,
1191 pppd.approved_flag,
1192 pppd.promised_date,
1193 pppd.need_by_date ,
1194 pppd.expenditure_organization_id,
1195 pppd.vendor_id,
1196 pppd.vendor_name,
1197 pppd.expenditure_type,
1198 pppd.expenditure_category,
1199 pppd.revenue_category,
1200 'VI',
1201 pppd.unit,
1202 pppd.unit_price,
1203 adl.ind_compiled_set_id,
1204 pppd.quantity_outstanding_invoice,
1205 pppd.quantity_ordered,
1206 pppd.amount_ordered,
1207 pppd.original_quantity_ordered,
1208 pppd.original_amount_ordered,
1209 pppd.quantity_cancelled,
1210 pppd.amount_cancelled,
1211 pppd.quantity_delivered,
1212 pppd.quantity_invoiced,
1213 pppd.amount_invoiced,
1214 pppd.quantity_outstanding_delivery,
1215 pppd.amount_outstanding_delivery,
1216 pppd.quantity_outstanding_invoice,
1217 pppd.amount_outstanding_invoice,
1218 pppd.quantity_overbilled,
1219 pppd.amount_overbilled,
1220 NULL,
1221 NULL,
1222 NULL,
1223 pppd.amount_outstanding_invoice,
1224 PPPD.AMOUNT_OUTSTANDING_INVOICE ,
1225 pppd.denom_currency_code,
1226 pppd.denom_amt_outstanding_invoice,
1227 PPPD.denom_amt_outstanding_invoice ,
1228 pppd.acct_currency_code,
1229 pppd.acct_rate_date,
1230 pppd.acct_rate_type,
1231 pppd.acct_exchange_rate,
1232 TO_CHAR(NULL),
1233 TO_NUMBER(NULL),
1234 TO_NUMBER(NULL) ,
1235 adl.burdenable_raw_cost
1236 BULK collect into
1237 l_project_id,
1238 l_task_id,
1239 l_transaction_source,
1240 l_line_type,
1241 l_cmt_number,
1242 l_cmt_distribution_id,
1243 l_cmt_header_id,
1244 l_description,
1245 l_expenditure_item_date,
1246 l_pa_period,
1247 l_gl_period,
1248 l_cmt_line_number,
1249 l_creation_date,
1250 l_approved_date,
1251 l_requestor_name,
1252 l_buyer_name,
1253 l_approved_flag,
1254 l_promised_date,
1255 l_need_by_date,
1256 l_organization_id,
1257 l_vendor_id,
1258 l_vendor_name,
1259 l_expenditure_type,
1260 l_expenditure_category,
1261 l_revenue_category,
1262 l_system_linkage_function,
1263 l_unit_of_measure,
1264 l_unit_price,
1265 l_ind_compiled_set_id,
1266 l_cmt_quantity,
1267 l_quantity_ordered,
1268 l_amount_ordered,
1269 l_orig_quantity_ordered,
1270 l_orig_amount_ordered,
1271 l_quantity_cancelled,
1272 l_amount_cancelled,
1273 l_quantity_delivered,
1274 l_quantity_invoiced,
1275 l_amount_invoiced,
1276 l_qty_out_delivery,
1277 l_amount_out_delivery,
1278 l_qty_out_invoiced,
1279 l_amount_out_invoiced,
1280 l_qty_overbilled,
1281 l_amount_overbilled,
1282 l_orig_txn_ref1,
1283 l_orig_txn_ref2,
1284 l_orig_txn_ref3,
1285 l_acct_raw_cost,
1286 l_acct_burdened_cost,
1287 l_denom_currency_code,
1288 l_denom_raw_cost,
1289 l_denom_burdened_cost,
1290 l_acct_currency_code,
1291 l_acct_rate_date,
1292 l_acct_rate_type,
1293 l_acct_exchange_rate,
1294 l_receipt_currency_code,
1295 l_receipt_currency_amount,
1296 l_receipt_exchange_rate,
1297 l_burdenable_raw_cost
1298 FROM PA_PROJ_PO_DISTRIBUTIONS PPPD,
1299 GMS_AWARD_DISTRIBUTIONS ADL
1300 WHERE PPPD.project_id = p_start_project_id
1301 and pppd.po_distribution_id = adl.po_distribution_id
1302 and pppd.award_set_id = adl.award_set_id
1303 and adl.adl_line_num = 1
1304 -- bug fixes for 3755094 and 3736097
1305 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
1306 AND 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X');
1307
1308 if l_project_id.count <> 0 then
1309
1310 --
1311 -- 3614241
1312 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
1313 --
1314 FORALL indx in 1..l_project_id.count
1315
1316 INSERT INTO pa_commitment_txns
1317 ( cmt_line_id,
1318 project_id,
1319 task_id,
1320 transaction_source,
1321 line_type,
1322 cmt_number,
1323 cmt_distribution_id,
1324 cmt_header_id,
1325 description,
1326 expenditure_item_date,
1327 pa_period,
1328 gl_period,
1329 cmt_line_number,
1330 cmt_creation_date,
1331 cmt_approved_date,
1332 cmt_requestor_name,
1333 cmt_buyer_name,
1334 cmt_approved_flag,
1335 cmt_promised_date,
1336 cmt_need_by_date,
1337 organization_id,
1338 vendor_id,
1339 vendor_name,
1340 expenditure_type,
1341 expenditure_category,
1342 revenue_category,
1343 system_linkage_function,
1344 unit_of_measure,
1345 unit_price,
1346 cmt_ind_compiled_set_id,
1347 tot_cmt_raw_cost,
1348 tot_cmt_burdened_cost,
1349 tot_cmt_quantity,
1350 quantity_ordered,
1351 amount_ordered,
1352 original_quantity_ordered,
1353 original_amount_ordered,
1354 quantity_cancelled,
1355 amount_cancelled,
1356 quantity_delivered,
1357 amount_delivered,
1358 quantity_invoiced,
1359 amount_invoiced,
1360 quantity_outstanding_delivery,
1361 amount_outstanding_delivery,
1362 quantity_outstanding_invoice,
1363 amount_outstanding_invoice,
1364 quantity_overbilled,
1365 amount_overbilled,
1366 original_txn_reference1,
1367 original_txn_reference2,
1368 original_txn_reference3,
1369 last_update_date,
1370 last_updated_by,
1371 creation_date,
1372 created_by,
1373 last_update_login,
1374 request_id,
1375 program_application_id,
1376 program_id,
1377 program_update_date,
1378 burden_sum_source_run_id,
1379 burden_sum_dest_run_id,
1380 burden_sum_rejection_code,
1381 acct_raw_cost,
1382 acct_burdened_cost,
1383 denom_currency_code,
1384 denom_raw_cost,
1385 denom_burdened_cost,
1386 acct_currency_code,
1387 acct_rate_date,
1388 acct_rate_type,
1389 acct_exchange_rate,
1390 receipt_currency_code,
1391 receipt_currency_amount,
1392 receipt_exchange_rate,
1393 project_currency_code,
1394 project_rate_date,
1395 project_rate_type,
1396 project_exchange_rate,
1397 generation_error_flag,
1398 cmt_rejection_code
1399 )
1400 values (
1401 pa_txn_accums.cmt_line_id,
1402 l_project_id(indx),
1403 l_task_id(indx),
1404 l_transaction_source(indx),
1405 l_line_type(indx),
1406 l_cmt_number(indx),
1407 l_cmt_distribution_id(indx),
1408 l_cmt_header_id(indx),
1409 l_description(indx),
1410 l_expenditure_item_date(indx),
1411 l_pa_period(indx),
1412 l_gl_period(indx),
1413 l_cmt_line_number(indx),
1414 l_creation_date(indx),
1415 l_approved_date(indx),
1416 l_requestor_name(indx),
1417 l_buyer_name(indx),
1418 l_approved_flag(indx),
1419 l_promised_date(indx),
1420 l_need_by_date(indx),
1421 l_organization_id(indx),
1422 l_vendor_id(indx),
1423 l_vendor_name(indx),
1424 l_expenditure_type(indx),
1425 l_expenditure_category(indx),
1426 l_revenue_category(indx),
1427 l_system_linkage_function(indx),
1428 l_unit_of_measure(indx),
1429 l_unit_price(indx),
1430 to_number(null),
1431 to_number(null),
1432 to_number(null),
1433 l_cmt_quantity(indx),
1434 l_quantity_ordered(indx),
1435 l_amount_ordered(indx),
1436 l_orig_quantity_ordered(indx),
1437 l_orig_amount_ordered(indx),
1438 l_quantity_cancelled(indx),
1439 l_amount_cancelled(indx),
1440 l_quantity_delivered(indx),
1441 to_number(null),
1442 l_quantity_invoiced(indx),
1443 l_amount_invoiced(indx),
1444 l_qty_out_delivery(indx),
1445 l_amount_out_delivery(indx),
1446 l_qty_out_invoiced(indx),
1447 l_amount_out_invoiced(indx),
1448 l_qty_overbilled(indx),
1449 l_amount_overbilled(indx),
1450 l_orig_txn_ref1(indx),
1451 l_orig_txn_ref2(indx),
1452 l_orig_txn_ref3(indx),
1453 sysdate,
1454 l_last_updated_by,
1455 sysdate,
1456 l_created_by,
1457 l_last_update_login,
1458 l_request_id,
1459 l_program_application_id,
1460 l_program_id,
1461 null,
1462 -9999,
1463 null,
1464 null,
1465 l_acct_raw_cost(indx),
1466 l_acct_burdened_cost(indx),
1467 l_denom_currency_code(indx),
1468 l_denom_raw_cost(indx),
1469 l_denom_burdened_cost(indx),
1470 l_acct_currency_code(indx),
1471 l_acct_rate_date(indx),
1472 l_acct_rate_type(indx),
1473 l_acct_exchange_rate(indx),
1474 l_receipt_currency_code(indx),
1475 l_receipt_currency_amount(indx),
1476 l_receipt_exchange_rate(indx),
1477 null,
1478 to_date(null),
1479 null,
1480 to_number(null),
1481 'N',
1482 null ) ;
1483
1484 --
1485 -- 3614241
1486 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
1487 --
1488 FORALL indx in 1..l_project_id.count
1489 INSERT INTO pa_commitment_txns
1490 ( cmt_line_id,
1491 project_id,
1492 task_id,
1493 transaction_source,
1494 line_type,
1495 cmt_number,
1496 cmt_distribution_id,
1497 cmt_header_id,
1498 description,
1499 expenditure_item_date,
1500 pa_period,
1501 gl_period,
1502 cmt_line_number,
1503 cmt_creation_date,
1504 cmt_approved_date,
1505 cmt_requestor_name,
1506 cmt_buyer_name,
1507 cmt_approved_flag,
1508 cmt_promised_date,
1509 cmt_need_by_date,
1510 organization_id,
1511 vendor_id,
1512 vendor_name,
1513 expenditure_type,
1514 expenditure_category,
1515 revenue_category,
1516 system_linkage_function,
1517 unit_of_measure,
1518 unit_price,
1519 cmt_ind_compiled_set_id,
1520 tot_cmt_raw_cost,
1521 tot_cmt_burdened_cost,
1522 tot_cmt_quantity,
1523 quantity_ordered,
1524 amount_ordered,
1525 original_quantity_ordered,
1526 original_amount_ordered,
1527 quantity_cancelled,
1528 amount_cancelled,
1529 quantity_delivered,
1530 amount_delivered,
1531 quantity_invoiced,
1532 amount_invoiced,
1533 quantity_outstanding_delivery,
1534 amount_outstanding_delivery,
1535 quantity_outstanding_invoice,
1536 amount_outstanding_invoice,
1537 quantity_overbilled,
1538 amount_overbilled,
1539 original_txn_reference1,
1540 original_txn_reference2,
1541 original_txn_reference3,
1542 last_update_date,
1543 last_updated_by,
1544 creation_date,
1545 created_by,
1546 last_update_login,
1547 request_id,
1548 program_application_id,
1549 program_id,
1550 program_update_date,
1551 burden_sum_source_run_id,
1552 burden_sum_dest_run_id,
1553 burden_sum_rejection_code,
1554 acct_raw_cost,
1555 acct_burdened_cost,
1556 denom_currency_code,
1557 denom_raw_cost,
1558 denom_burdened_cost,
1559 acct_currency_code,
1560 acct_rate_date,
1561 acct_rate_type,
1562 acct_exchange_rate,
1563 receipt_currency_code,
1564 receipt_currency_amount,
1565 receipt_exchange_rate,
1566 project_currency_code,
1567 project_rate_date,
1568 project_rate_type,
1569 project_exchange_rate,
1570 generation_error_flag,
1571 cmt_rejection_code
1572 )
1573 select pa_txn_accums.cmt_line_id,
1574 l_project_id(indx),
1575 l_task_id(indx),
1576 l_transaction_source(indx),
1577 l_line_type(indx),
1578 l_cmt_number(indx),
1579 l_cmt_distribution_id(indx),
1580 l_cmt_header_id(indx),
1581 l_description(indx),
1582 l_expenditure_item_date(indx),
1583 l_pa_period(indx),
1584 l_gl_period(indx),
1585 l_cmt_line_number(indx),
1586 l_creation_date(indx),
1587 l_approved_date(indx),
1588 l_requestor_name(indx),
1589 l_buyer_name(indx),
1590 l_approved_flag(indx),
1591 l_promised_date(indx),
1592 l_need_by_date(indx),
1593 l_organization_id(indx),
1594 l_vendor_id(indx),
1595 l_vendor_name(indx),
1596 icc.expenditure_type,
1597 pet.expenditure_category,
1598 l_revenue_category(indx),
1599 'BTC',
1600 l_unit_of_measure(indx),
1601 l_unit_price(indx),
1602 l_ind_compiled_set_id(indx),
1603 to_number(null),
1604 to_number(null),
1605 0,
1606 0,
1607 0,
1608 0,
1609 0,
1610 0,
1611 0,
1612 0,
1613 to_number(null),
1614 0,
1615 0,
1616 0,
1617 0,
1618 0,
1619 0,
1620 0,
1621 0,
1622 l_orig_txn_ref1(indx),
1623 l_orig_txn_ref2(indx),
1624 l_orig_txn_ref3(indx),
1625 sysdate,
1626 l_last_updated_by,
1627 sysdate,
1628 l_created_by,
1629 l_last_update_login,
1630 l_request_id,
1631 l_program_application_id,
1632 l_program_id,
1633 null,
1634 -9999,
1635 null,
1636 null,
1637 0,
1638 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
1639 l_denom_currency_code(indx),
1640 0,
1641 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
1642 nvl(l_acct_exchange_rate(indx),1)),
1643 l_acct_currency_code(indx),
1644 l_acct_rate_date(indx),
1645 l_acct_rate_type(indx),
1646 l_acct_exchange_rate(indx),
1647 l_receipt_currency_code(indx),
1648 l_receipt_currency_amount(indx),
1649 l_receipt_exchange_rate(indx),
1650 null,
1651 to_date(null),
1652 null,
1653 to_number(null),
1654 'N',
1655 null
1656 FROM pa_ind_rate_sch_revisions irsr,
1657 pa_cost_base_exp_types cbet,
1658 pa_compiled_multipliers cm,
1659 pa_ind_cost_codes icc,
1660 pa_ind_rate_schedules_all_bg irs,
1661 pa_ind_compiled_sets ics,
1662 pa_expenditure_types pet
1663 WHERE cbet.cost_base_type = 'INDIRECT COST'
1664 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
1665 and l_acct_raw_cost(indx) <> 0
1666 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
1667 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
1668 and icc.ind_cost_code = cm.ind_cost_code
1669 and cbet.cost_base = cm.cost_base
1670 and cbet.expenditure_type = l_expenditure_type(indx)
1671 and irsr.cost_plus_structure = cbet.cost_plus_structure
1672 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1673 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1674 and ics.organization_id = l_organization_id(indx)
1675 and ics.cost_base = cbet.cost_base
1676 and icc.expenditure_type = pet.expenditure_type ;
1677 end if ;
1678 --
1679 -- AP Transactions starts here
1680 --
1681 --
1682 -- bug 4007039 PJ.M:B8:P13:OTH:PERF: INDEX FULL SCAN, NON-MERGABLE VIEW and SHARABLE MEMORY>600K
1683 -- gms_commitment_override v was removed and select from the REQ, PO and AP was used directly.
1684 --
1685 l_project_id.delete ;
1686 l_task_id.delete ;
1687 l_transaction_source.delete ;
1688 l_line_type.delete ;
1689 l_cmt_number.delete ;
1690 l_cmt_distribution_id.delete ;
1691 l_cmt_header_id.delete ;
1692 l_description.delete ;
1693 l_expenditure_item_date.delete ;
1694 l_pa_period.delete ;
1695 l_gl_period.delete ;
1696 l_cmt_line_number.delete ;
1697 l_creation_date.delete ;
1698 l_approved_date.delete ;
1699 l_requestor_name.delete ;
1700 l_buyer_name.delete ;
1701 l_approved_flag.delete ;
1702 l_promised_date.delete ;
1703 l_need_by_date.delete ;
1704 l_organization_id.delete ;
1705 l_vendor_id.delete ;
1706 l_vendor_name.delete ;
1707 l_expenditure_type.delete ;
1708 l_expenditure_category.delete ;
1709 l_revenue_category.delete ;
1710 l_system_linkage_function.delete ;
1711 l_unit_of_measure.delete ;
1712 l_unit_price.delete ;
1713 l_ind_compiled_set_id.delete ;
1714 l_cmt_quantity.delete ;
1715 l_quantity_ordered.delete ;
1716 l_amount_ordered.delete ;
1717 l_orig_quantity_ordered.delete ;
1718 l_orig_amount_ordered.delete ;
1719 l_quantity_cancelled.delete ;
1720 l_amount_cancelled.delete ;
1721 l_quantity_delivered.delete ;
1722 l_quantity_invoiced.delete ;
1723 l_amount_invoiced.delete ;
1724 l_qty_out_delivery.delete ;
1725 l_amount_out_delivery.delete ;
1726 l_qty_out_invoiced.delete ;
1727 l_amount_out_invoiced.delete ;
1728 l_qty_overbilled.delete ;
1729 l_amount_overbilled.delete ;
1730 l_orig_txn_ref1.delete ;
1731 l_orig_txn_ref2.delete ;
1732 l_orig_txn_ref3.delete ;
1733 l_acct_raw_cost.delete ;
1734 l_acct_burdened_cost.delete ;
1735 l_denom_currency_code.delete ;
1736 l_denom_raw_cost.delete ;
1737 l_denom_burdened_cost.delete ;
1738 l_acct_currency_code.delete ;
1739 l_acct_rate_date.delete ;
1740 l_acct_rate_type.delete ;
1741 l_acct_exchange_rate.delete ;
1742 l_receipt_currency_code.delete ;
1743 l_receipt_currency_amount.delete ;
1744 l_receipt_exchange_rate.delete ;
1745 l_burdenable_raw_cost.delete ;
1746
1747 SELECT
1748 ppaid.project_id,
1749 ppaid.task_id,
1750 'ORACLE_PAYABLES',
1751 'I',
1752 ppaid.invoice_number,
1753 /* R12 AP Lines uptake:record invoice distribution ID and
1754 invoice line number instead of line number which is no
1755 longer unique.*/
1756 ppaid.invoice_distribution_id,
1757 ppaid.invoice_id,
1758 ppaid.description,
1759 ppaid.expenditure_item_date,
1760 l_cur_pa_period, /* Added for commitment change request*/
1761 l_cur_gl_period, /* Added for commitment change request*/
1762 /* R12 AP Lines uptake:record invoice distribution ID and
1763 invoice line number instead of line number which is no
1764 longer unique.*/
1765 ppaid.invoice_line_number,
1766 ppaid.invoice_date,
1767 to_date(NULL),
1768 to_char(NULL),
1769 to_char(NULL),
1770 ppaid.approved_flag,
1771 to_date(NULL),
1772 to_date(NULL),
1773 ppaid.expenditure_organization_id,
1774 vendor_id,
1775 ppaid.vendor_name,
1776 ppaid.expenditure_type,
1777 ppaid.expenditure_category,
1778 ppaid.revenue_category,
1779 'VI',
1780 to_char(NULL),
1781 to_number(NULL),
1782 adl.ind_compiled_set_id,
1783 ppaid.quantity,
1784 to_number(null),
1785 to_number(null),
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 null,
1800 null,
1801 null,
1802 ppaid.amount,
1803 ppaid.amount ,
1804 ppaid.denom_currency_code,
1805 ppaid.denom_amount,
1806 ppaid.denom_amount ,
1807 ppaid.acct_currency_code,
1808 ppaid.acct_rate_date,
1809 ppaid.acct_rate_type,
1810 ppaid.acct_exchange_rate,
1811 ppaid.receipt_currency_code ,
1812 ppaid.receipt_currency_amount ,
1813 ppaid.receipt_exchange_rate ,
1814 /* Commented for Bug 5645290
1815 adl.burdenable_raw_cost */
1816 /* Added for Bug 5645290 */
1817 decode(gae.burden_cost_limit,NULL,ppaid.denom_amount,adl.burdenable_raw_cost)
1818 /* Bug 5645290 - End */
1819 BULK collect into
1820 l_project_id,
1821 l_task_id,
1822 l_transaction_source,
1823 l_line_type,
1824 l_cmt_number,
1825 l_cmt_distribution_id,
1826 l_cmt_header_id,
1827 l_description,
1828 l_expenditure_item_date,
1829 l_pa_period,
1830 l_gl_period,
1831 l_cmt_line_number,
1832 l_creation_date,
1833 l_approved_date,
1834 l_requestor_name,
1835 l_buyer_name,
1836 l_approved_flag,
1837 l_promised_date,
1838 l_need_by_date,
1839 l_organization_id,
1840 l_vendor_id,
1841 l_vendor_name,
1842 l_expenditure_type,
1843 l_expenditure_category,
1844 l_revenue_category,
1845 l_system_linkage_function,
1846 l_unit_of_measure,
1847 l_unit_price,
1848 l_ind_compiled_set_id,
1849 l_cmt_quantity,
1850 l_quantity_ordered,
1851 l_amount_ordered,
1852 l_orig_quantity_ordered,
1853 l_orig_amount_ordered,
1854 l_quantity_cancelled,
1855 l_amount_cancelled,
1856 l_quantity_delivered,
1857 l_quantity_invoiced,
1858 l_amount_invoiced,
1859 l_qty_out_delivery,
1860 l_amount_out_delivery,
1861 l_qty_out_invoiced,
1862 l_amount_out_invoiced,
1863 l_qty_overbilled,
1864 l_amount_overbilled,
1865 l_orig_txn_ref1,
1866 l_orig_txn_ref2,
1867 l_orig_txn_ref3,
1868 l_acct_raw_cost,
1869 l_acct_burdened_cost,
1870 l_denom_currency_code,
1871 l_denom_raw_cost,
1872 l_denom_burdened_cost,
1873 l_acct_currency_code,
1874 l_acct_rate_date,
1875 l_acct_rate_type,
1876 l_acct_exchange_rate,
1877 l_receipt_currency_code,
1878 l_receipt_currency_amount,
1879 l_receipt_exchange_rate,
1880 l_burdenable_raw_cost
1881 /* Commented for Bug 5645290
1882 FROM PA_PROJ_AP_INV_DISTRIBUTIONS PPAID,
1883 GMS_AWARD_DISTRIBUTIONS ADL
1884 WHERE PPAID.project_id = p_start_project_id */
1885 /* Added for Bug 5645290 */
1886 FROM PA_PROJ_AP_INV_DISTRIBUTIONS PPAID,
1887 GMS_AWARD_DISTRIBUTIONS ADL,
1888 gms_allowable_expenditures gae,
1889 gms_awards_all ga
1890 WHERE PPAID.project_id = p_start_project_id
1891 and ga.award_id = adl.award_id
1892 and gae.allowability_schedule_id = ga.allowable_schedule_id
1893 and gae.expenditure_type = PPAID.expenditure_type
1894 /* Bug 5645290 - End */
1895 /* R12 AP Lines uptake:record invoice distribution ID and
1896 invoice line number instead of line number which is no
1897 longer unique.*/
1898 --
1899 -- Bug : 4908630
1900 -- R12.PJ:XB2:DEV:GMS: APPSPERF:GMS: PACKAGE: GMSPAX3B.PLS ( SHARE MEM:11MB) 5 SQL
1901 --
1902 and adl.invoice_distribution_id = ppaid.invoice_distribution_id
1903 and ppaid.award_set_id = adl.award_set_id
1904 and adl.adl_line_num = 1
1905 and adl.invoice_id = ppaid.invoice_id
1906 and adl.distribution_line_number = ppaid.distribution_line_number
1907 and 'VI' = NVL(NVL(p_system_linkage_function,'VI'),'X')
1908 and NVL(adl.payment_status_flag , 'N') <> 'Y' ;
1909 -- bug fixes for 3755094 and 3736097
1910 --WHERE project_id BETWEEN p_start_project_id AND p_end_project_id
1911
1912 if l_project_id.count <> 0 then
1913
1914 --
1915 -- 3614241
1916 -- Insert Commitment raw Cost using the PL/SQL bulk array elements.
1917 --
1918 FORALL indx in 1..l_project_id.count
1919
1920 INSERT INTO pa_commitment_txns
1921 ( cmt_line_id,
1922 project_id,
1923 task_id,
1924 transaction_source,
1925 line_type,
1926 cmt_number,
1927 cmt_distribution_id,
1928 cmt_header_id,
1929 description,
1930 expenditure_item_date,
1931 pa_period,
1932 gl_period,
1933 cmt_line_number,
1934 cmt_creation_date,
1935 cmt_approved_date,
1936 cmt_requestor_name,
1937 cmt_buyer_name,
1938 cmt_approved_flag,
1939 cmt_promised_date,
1940 cmt_need_by_date,
1941 organization_id,
1942 vendor_id,
1943 vendor_name,
1944 expenditure_type,
1945 expenditure_category,
1946 revenue_category,
1947 system_linkage_function,
1948 unit_of_measure,
1949 unit_price,
1950 cmt_ind_compiled_set_id,
1951 tot_cmt_raw_cost,
1952 tot_cmt_burdened_cost,
1953 tot_cmt_quantity,
1954 quantity_ordered,
1955 amount_ordered,
1956 original_quantity_ordered,
1957 original_amount_ordered,
1958 quantity_cancelled,
1959 amount_cancelled,
1960 quantity_delivered,
1961 amount_delivered,
1962 quantity_invoiced,
1963 amount_invoiced,
1964 quantity_outstanding_delivery,
1965 amount_outstanding_delivery,
1966 quantity_outstanding_invoice,
1967 amount_outstanding_invoice,
1968 quantity_overbilled,
1969 amount_overbilled,
1970 original_txn_reference1,
1971 original_txn_reference2,
1972 original_txn_reference3,
1973 last_update_date,
1974 last_updated_by,
1975 creation_date,
1976 created_by,
1977 last_update_login,
1978 request_id,
1979 program_application_id,
1980 program_id,
1981 program_update_date,
1982 burden_sum_source_run_id,
1983 burden_sum_dest_run_id,
1984 burden_sum_rejection_code,
1985 acct_raw_cost,
1986 acct_burdened_cost,
1987 denom_currency_code,
1988 denom_raw_cost,
1989 denom_burdened_cost,
1990 acct_currency_code,
1991 acct_rate_date,
1992 acct_rate_type,
1993 acct_exchange_rate,
1994 receipt_currency_code,
1995 receipt_currency_amount,
1996 receipt_exchange_rate,
1997 project_currency_code,
1998 project_rate_date,
1999 project_rate_type,
2000 project_exchange_rate,
2001 generation_error_flag,
2002 cmt_rejection_code
2003 )
2004 values (
2005 pa_txn_accums.cmt_line_id,
2006 l_project_id(indx),
2007 l_task_id(indx),
2008 l_transaction_source(indx),
2009 l_line_type(indx),
2010 l_cmt_number(indx),
2011 l_cmt_distribution_id(indx),
2012 l_cmt_header_id(indx),
2013 l_description(indx),
2014 l_expenditure_item_date(indx),
2015 l_pa_period(indx),
2016 l_gl_period(indx),
2017 l_cmt_line_number(indx),
2018 l_creation_date(indx),
2019 l_approved_date(indx),
2020 l_requestor_name(indx),
2021 l_buyer_name(indx),
2022 l_approved_flag(indx),
2023 l_promised_date(indx),
2024 l_need_by_date(indx),
2025 l_organization_id(indx),
2026 l_vendor_id(indx),
2027 l_vendor_name(indx),
2028 l_expenditure_type(indx),
2029 l_expenditure_category(indx),
2030 l_revenue_category(indx),
2031 l_system_linkage_function(indx),
2032 l_unit_of_measure(indx),
2033 l_unit_price(indx),
2034 to_number(null),
2035 to_number(null),
2036 to_number(null),
2037 l_cmt_quantity(indx),
2038 l_quantity_ordered(indx),
2039 l_amount_ordered(indx),
2040 l_orig_quantity_ordered(indx),
2041 l_orig_amount_ordered(indx),
2042 l_quantity_cancelled(indx),
2043 l_amount_cancelled(indx),
2044 l_quantity_delivered(indx),
2045 to_number(null),
2046 l_quantity_invoiced(indx),
2047 l_amount_invoiced(indx),
2048 l_qty_out_delivery(indx),
2049 l_amount_out_delivery(indx),
2050 l_qty_out_invoiced(indx),
2051 l_amount_out_invoiced(indx),
2052 l_qty_overbilled(indx),
2053 l_amount_overbilled(indx),
2054 l_orig_txn_ref1(indx),
2055 l_orig_txn_ref2(indx),
2056 l_orig_txn_ref3(indx),
2057 sysdate,
2058 l_last_updated_by,
2059 sysdate,
2060 l_created_by,
2061 l_last_update_login,
2062 l_request_id,
2063 l_program_application_id,
2064 l_program_id,
2065 null,
2066 -9999,
2067 null,
2068 null,
2069 l_acct_raw_cost(indx),
2070 l_acct_burdened_cost(indx),
2071 l_denom_currency_code(indx),
2072 l_denom_raw_cost(indx),
2073 l_denom_burdened_cost(indx),
2074 l_acct_currency_code(indx),
2075 l_acct_rate_date(indx),
2076 l_acct_rate_type(indx),
2077 l_acct_exchange_rate(indx),
2078 l_receipt_currency_code(indx),
2079 l_receipt_currency_amount(indx),
2080 l_receipt_exchange_rate(indx),
2081 null,
2082 to_date(null),
2083 null,
2084 to_number(null),
2085 'N',
2086 null ) ;
2087
2088 --
2089 -- 3614241
2090 -- Insert Commitment idc Cost using the PL/SQL bulk array elements.
2091 --
2092 FORALL indx in 1..l_project_id.count
2093 INSERT INTO pa_commitment_txns
2094 ( cmt_line_id,
2095 project_id,
2096 task_id,
2097 transaction_source,
2098 line_type,
2099 cmt_number,
2100 cmt_distribution_id,
2101 cmt_header_id,
2102 description,
2103 expenditure_item_date,
2104 pa_period,
2105 gl_period,
2106 cmt_line_number,
2107 cmt_creation_date,
2108 cmt_approved_date,
2109 cmt_requestor_name,
2110 cmt_buyer_name,
2111 cmt_approved_flag,
2112 cmt_promised_date,
2113 cmt_need_by_date,
2114 organization_id,
2115 vendor_id,
2116 vendor_name,
2117 expenditure_type,
2118 expenditure_category,
2119 revenue_category,
2120 system_linkage_function,
2121 unit_of_measure,
2122 unit_price,
2123 cmt_ind_compiled_set_id,
2124 tot_cmt_raw_cost,
2125 tot_cmt_burdened_cost,
2126 tot_cmt_quantity,
2127 quantity_ordered,
2128 amount_ordered,
2129 original_quantity_ordered,
2130 original_amount_ordered,
2131 quantity_cancelled,
2132 amount_cancelled,
2133 quantity_delivered,
2134 amount_delivered,
2135 quantity_invoiced,
2136 amount_invoiced,
2137 quantity_outstanding_delivery,
2138 amount_outstanding_delivery,
2139 quantity_outstanding_invoice,
2140 amount_outstanding_invoice,
2141 quantity_overbilled,
2142 amount_overbilled,
2143 original_txn_reference1,
2144 original_txn_reference2,
2145 original_txn_reference3,
2146 last_update_date,
2147 last_updated_by,
2148 creation_date,
2149 created_by,
2150 last_update_login,
2151 request_id,
2152 program_application_id,
2153 program_id,
2154 program_update_date,
2155 burden_sum_source_run_id,
2156 burden_sum_dest_run_id,
2157 burden_sum_rejection_code,
2158 acct_raw_cost,
2159 acct_burdened_cost,
2160 denom_currency_code,
2161 denom_raw_cost,
2162 denom_burdened_cost,
2163 acct_currency_code,
2164 acct_rate_date,
2165 acct_rate_type,
2166 acct_exchange_rate,
2167 receipt_currency_code,
2168 receipt_currency_amount,
2169 receipt_exchange_rate,
2170 project_currency_code,
2171 project_rate_date,
2172 project_rate_type,
2173 project_exchange_rate,
2174 generation_error_flag,
2175 cmt_rejection_code
2176 )
2177 select pa_txn_accums.cmt_line_id,
2178 l_project_id(indx),
2179 l_task_id(indx),
2180 l_transaction_source(indx),
2181 l_line_type(indx),
2182 l_cmt_number(indx),
2183 l_cmt_distribution_id(indx),
2184 l_cmt_header_id(indx),
2185 l_description(indx),
2186 l_expenditure_item_date(indx),
2187 l_pa_period(indx),
2188 l_gl_period(indx),
2189 l_cmt_line_number(indx),
2190 l_creation_date(indx),
2191 l_approved_date(indx),
2192 l_requestor_name(indx),
2193 l_buyer_name(indx),
2194 l_approved_flag(indx),
2195 l_promised_date(indx),
2196 l_need_by_date(indx),
2197 l_organization_id(indx),
2198 l_vendor_id(indx),
2199 l_vendor_name(indx),
2200 icc.expenditure_type,
2201 pet.expenditure_category,
2202 l_revenue_category(indx),
2203 'BTC',
2204 l_unit_of_measure(indx),
2205 l_unit_price(indx),
2206 l_ind_compiled_set_id(indx),
2207 to_number(null),
2208 to_number(null),
2209 0,
2210 0,
2211 0,
2212 0,
2213 0,
2214 0,
2215 0,
2216 0,
2217 to_number(null),
2218 0,
2219 0,
2220 0,
2221 0,
2222 0,
2223 0,
2224 0,
2225 0,
2226 l_orig_txn_ref1(indx),
2227 l_orig_txn_ref2(indx),
2228 l_orig_txn_ref3(indx),
2229 sysdate,
2230 l_last_updated_by,
2231 sysdate,
2232 l_created_by,
2233 l_last_update_login,
2234 l_request_id,
2235 l_program_application_id,
2236 l_program_id,
2237 null,
2238 -9999,
2239 null,
2240 null,
2241 0,
2242 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier),
2243 l_denom_currency_code(indx),
2244 0,
2245 pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier /
2246 nvl(l_acct_exchange_rate(indx),1)),
2247 l_acct_currency_code(indx),
2248 l_acct_rate_date(indx),
2249 l_acct_rate_type(indx),
2250 l_acct_exchange_rate(indx),
2251 l_receipt_currency_code(indx),
2252 l_receipt_currency_amount(indx),
2253 l_receipt_exchange_rate(indx),
2254 null,
2255 to_date(null),
2256 null,
2257 to_number(null),
2258 'N',
2259 null
2260 FROM pa_ind_rate_sch_revisions irsr,
2261 pa_cost_base_exp_types cbet,
2262 pa_compiled_multipliers cm,
2263 pa_ind_cost_codes icc,
2264 pa_ind_rate_schedules_all_bg irs,
2265 pa_ind_compiled_sets ics,
2266 pa_expenditure_types pet
2267 WHERE cbet.cost_base_type = 'INDIRECT COST'
2268 and pa_currency.round_currency_amt(nvl(l_burdenable_raw_cost(indx),0) * cm.compiled_multiplier) <> 0 --added for bug 6271366
2269 and l_acct_raw_cost(indx) <> 0
2270 and ics.ind_compiled_set_id = l_ind_compiled_set_id(indx)
2271 and cm.ind_compiled_set_id = DECODE( CBET.COST_BASE, NULL, NULL, ics.ind_compiled_set_id )
2272 and icc.ind_cost_code = cm.ind_cost_code
2273 and cbet.cost_base = cm.cost_base
2274 and cbet.expenditure_type = l_expenditure_type(indx)
2275 and irsr.cost_plus_structure = cbet.cost_plus_structure
2276 and irs.ind_rate_sch_id = irsr.ind_rate_sch_id
2277 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2278 and ics.organization_id = l_organization_id(indx)
2279 and ics.cost_base = cbet.cost_base
2280 and icc.expenditure_type = pet.expenditure_type ;
2281 end if ;
2282
2283 END create_cmt_txns ;
2284
2285 -- =====================
2286 -- Start of the comment
2287 -- Bug : 3599305
2288 -- API Name : is_project_type_sponsored
2289 -- Type : Public
2290 -- Pre_reqs : None
2291 -- Description : The purpose of this API is to check if project type is
2292 -- marked as sponsored in Grants Accounting.
2293 -- Called from : project type entry and project entry form
2294 -- Return Value : Y - Yes
2295 -- N - No
2296 --
2297 -- Parameters :
2298 -- IN :
2299 -- p_project_type varchar2
2300 -- End of comments
2301 -- ===============
2302 -- bug reference : 3596872
2303 -- problem : Please put the SELECT statement in the api :- GMS_PA_API3.IS_PROJECT_TYPE_SPONSORED in a cursor.
2304 -- If no record is found, the api should return 'N'.
2305 -- Otherwise, it is throwing a NO-DATA-FOUND error.
2306 -- Situation : When new project type is being created and not saved yet.
2307 --
2308 FUNCTION is_project_type_sponsored ( p_project_type IN VARCHAR2 )
2309 return varchar2 is
2310 l_spon_flag varchar2(1) ;
2311 begin
2312
2313 if p_project_type is NULL then
2314 return 'N' ;
2315 end if ;
2316
2317 l_spon_flag := 'N' ;
2318
2319 select sponsored_flag
2320 into l_spon_flag
2321 from gms_project_types
2322 where project_type = p_project_type ;
2323
2324 return NVL(l_spon_flag, 'N') ;
2325 -- bug 3596872
2326 EXCEPTION
2327 when no_data_found then
2328 return 'N' ;
2329 END is_project_type_sponsored ;
2330
2331 -- Bug 5726575
2332 -- =====================
2333 -- Start of the comment
2334 -- API Name : mark_impacted_enc_items
2335 -- Type : Public
2336 -- Pre_reqs : None
2337 -- Description : This procedure is called from
2338 -- pa_cost_plus.mark_impacted_exp_items (PAXCCPEB.pls).
2339 -- This procedure will mark all the burden impacted lines
2340 -- in gms_encumbrance_items_all.
2341 --
2342 -- Called from : pa_cost_plus.mark_impacted_exp_items
2343 -- Return Value : None
2344 --
2345 -- Parameters :
2346 -- IN :p_ind_compiled_set_id
2347 -- p_g_impacted_cost_bases
2348 -- p_g_cp_structure
2349 -- p_indirect_cost_code
2350 -- p_rate_sch_rev_id
2351 -- p_g_rate_sch_rev_id
2352 -- p_g_org_id
2353 -- p_g_org_override
2354 -- OUT :errbuf
2355 -- retcode
2356 -- End of comments
2357 -- ===============
2358 Procedure mark_impacted_enc_items (p_ind_compiled_set_id in number,
2359 p_g_impacted_cost_bases in varchar2,
2360 p_g_cp_structure in varchar2,
2361 p_indirect_cost_code in varchar2,
2362 p_rate_sch_rev_id in number,
2363 p_g_rate_sch_rev_id in number,
2364 p_g_org_id in number,
2365 p_g_org_override in number,
2366 errbuf OUT NOCOPY VARCHAR2,
2367 retcode OUT NOCOPY VARCHAR2)
2368 is
2369 x_last_updated_by number(15);
2370 x_last_update_login number(15);
2371 x_request_id number(15);
2372 begin
2373
2374 x_last_updated_by := FND_GLOBAL.USER_ID;
2375 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2376 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2377
2378 UPDATE gms_encumbrance_items_all ITEM
2379 SET ITEM.enc_distributed_flag =
2380 DECODE(ITEM.enc_distributed_flag,
2381 'Y', decode(ITEM.ind_compiled_set_id,
2382 p_ind_compiled_set_id, 'N',
2383 ITEM.enc_distributed_flag),
2384 ITEM.enc_distributed_flag),
2385 ITEM.adjustment_type =
2386 DECODE(ITEM.enc_distributed_flag,
2387 'Y', decode(ITEM.ind_compiled_set_id,
2388 p_ind_compiled_set_id, 'BURDEN_RECOMPILE',
2389 ITEM.adjustment_type),
2390 ITEM.adjustment_type),
2391 ITEM.ind_compiled_set_id = NULL,
2392 ITEM.last_update_date = SYSDATE,
2393 ITEM.last_updated_by = x_last_updated_by,
2394 ITEM.last_update_login = x_last_update_login,
2395 ITEM.request_id = x_request_id
2396 WHERE (ITEM.ind_compiled_set_id = p_ind_compiled_set_id
2397 AND ITEM.enc_distributed_flag = 'Y')
2398 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2399 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2400 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2401 AND exists (select /*+ NO_UNNEST */ null
2402 from pa_cost_base_exp_types cbet
2403 where cbet.cost_base = p_g_impacted_cost_bases
2404 AND cbet.cost_plus_structure = p_g_cp_structure
2405 AND cbet.cost_base_type = p_indirect_cost_code
2406 AND cbet.expenditure_type = ITEM.encumbrance_type)
2407 AND EXISTS (SELECT NULL
2408 FROM GMS_ENCUMBRANCES_ALL EXP,
2409 PA_IND_COMPILED_SETS ICS
2410 WHERE EXP.ENCUMBRANCE_ID = ITEM.ENCUMBRANCE_ID
2411 AND ICS.IND_COMPILED_SET_ID = ITEM.IND_COMPILED_SET_ID
2412 AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
2413 AND ICS.STATUS = 'H'
2414 AND ICS.IND_RATE_SCH_REVISION_ID = p_rate_sch_rev_id --Bug#5989869
2415 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
2416 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(p_rate_sch_rev_id ,ICS.ORGANIZATION_ID))
2417 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(p_rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
2418 )
2419 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d');
2420 exception
2421 when others then
2422 errbuf := sqlcode;
2423 retcode := sqlerrm;
2424 end mark_impacted_enc_items;
2425
2426 -- Bug 5726575
2427 -- =====================
2428 -- Start of the comment
2429 -- API Name : mark_prev_rev_enc_items
2430 -- Type : Public
2431 -- Pre_reqs : None
2432 -- Description : This procedure is called from
2433 -- pa_cost_plus.mark_prev_rev_exp_items (PAXCCPEB.pls).
2434 -- This procedure will mark all the burden impacted lines
2435 -- in gms_encumbrance_items_all.
2436 --
2437 -- Called from : pa_cost_plus.mark_prev_rev_exp_items
2438 -- Return Value : None
2439 --
2440 -- Parameters :
2441 -- IN :p_compiled_set_id
2442 -- p_start_date
2443 -- p_end_date
2444 -- p_mode
2445 -- OUT :errbuf
2446 -- retcode
2447 -- End of comments
2448 -- ===============
2449 Procedure mark_prev_rev_enc_items (p_compiled_set_id in number,
2450 p_start_date in date,
2451 p_end_date in date,
2452 p_mode in varchar2,
2453 errbuf OUT NOCOPY VARCHAR2,
2454 retcode OUT NOCOPY VARCHAR2)
2455 is
2456 x_last_updated_by number(15);
2457 x_last_update_login number(15);
2458 x_request_id number(15);
2459 begin
2460 x_last_updated_by := FND_GLOBAL.USER_ID;
2461 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2462 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2463
2464 if p_mode = 'T' then --Update when task.cost_ind_sch_fixed_date is populated.
2465 NULL;
2466 --update commented for the Bug#5989869
2467 /*UPDATE gms_encumbrance_items_all ei
2468 SET enc_distributed_flag = 'N',
2469 adjustment_type ='BURDEN_RECOMPILE',
2470 last_update_date = SYSDATE,
2471 last_updated_by = x_last_updated_by,
2472 last_update_login = x_last_update_login,
2473 request_id = x_request_id
2474 WHERE ind_compiled_set_id = p_compiled_set_id
2475 AND EXISTS
2476 (SELECT task_id
2477 FROM pa_tasks task
2478 WHERE task.task_id = ei.task_id
2479 AND task.cost_ind_sch_fixed_date BETWEEN p_start_date AND
2480 NVL(p_end_date, cost_ind_sch_fixed_date))
2481 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
2482 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y'
2483 AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D'
2484 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N';*/
2485 elsif p_mode = 'N' then --Update based on task.cost_ind_sch_fixed_date IS NULL then go by enc_item_date
2486 NULL;
2487 --update commented for the Bug#5989869
2488 /*UPDATE gms_encumbrance_items_all ei
2489 SET enc_distributed_flag = 'N' ,
2490 adjustment_type ='BURDEN_RECOMPILE',
2491 last_update_date = SYSDATE,
2492 last_updated_by = x_last_updated_by,
2493 last_update_login = x_last_update_login,
2494 request_id = x_request_id
2495 WHERE ind_compiled_set_id = p_compiled_set_id
2496 AND trunc(encumbrance_item_date) between trunc(p_start_date) and
2497 trunc(nvl(p_end_date, encumbrance_item_date))
2498 AND EXISTS
2499 (SELECT task_id
2500 FROM pa_tasks task
2501 WHERE task.task_id = ei.task_id
2502 AND task.cost_ind_sch_fixed_date IS NULL)
2503 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
2504 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
2505 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N'
2506 AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D';*/
2507 elsif p_mode = 'O' then --Update based on enc_item_date
2508 UPDATE gms_encumbrance_items_all ei
2509 SET enc_distributed_flag = 'N' ,
2510 adjustment_type ='BURDEN_RECOMPILE',
2511 last_update_date = SYSDATE,
2512 last_updated_by = x_last_updated_by,
2513 last_update_login = x_last_update_login,
2514 request_id = x_request_id
2515 WHERE ei.ind_compiled_set_id = p_compiled_set_id
2516 AND ei.encumbrance_item_date between p_start_date and nvl(p_end_date, ei.encumbrance_item_date) --Bug#5989869; Removed TRUNC
2517 AND nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y'
2518 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
2519 --AND pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) = 'D' commented for bug#5989869
2520 AND gms_pa_api2.is_award_closed(ei.encumbrance_item_id,ei.task_id, 'ENC') = 'N';
2521 end if;
2522 exception
2523 when others then
2524 errbuf := sqlcode;
2525 retcode := sqlerrm;
2526 end mark_prev_rev_enc_items;
2527
2528 -- Bug 6761516
2529 -- =====================
2530 -- Start of the comment
2531 -- API Name : mark_enc_items_for_recalc
2532 -- Type : Public
2533 -- Pre_reqs : None
2534 -- Description : This procedure is called from
2535 -- GMSAWEAW.fmb and GMSICOVR.fmb.
2536 -- This procedure will mark all the associated encumbrance items for recalc
2537 -- on insertion, uodation or deletion in Award Management Compliance Screen
2538 -- or in Override Schedules Screen.
2539 --
2540 -- Called from : GMSAWEAW.fmb and GMSICOVR.fmb
2541 -- Return Value : None
2542 --
2543 -- Parameters :
2544 -- IN :p_ind_rate_sch_id
2545 -- p_award_id
2546 -- p_project_id
2547 -- p_task_id
2548 -- p_calling_form
2549 -- p_event
2550 -- p_idc_schedule_fixed_date
2551 -- OUT :errbuf
2552 -- retcode
2553 -- End of comments
2554 -- ===============
2555 Procedure mark_enc_items_for_recalc (p_ind_rate_sch_id in number,
2556 p_award_id in number,
2557 p_project_id in number,
2558 p_task_id in number,
2559 p_calling_form in varchar2,
2560 p_event in varchar2,
2561 p_idc_schedule_fixed_date in date,
2562 errbuf OUT NOCOPY VARCHAR2,
2563 retcode OUT NOCOPY VARCHAR2)
2564 is
2565 x_last_updated_by number(15);
2566 x_last_update_login number(15);
2567 x_request_id number(15);
2568 l_start_date_min date;
2569 l_end_date_max date;
2570 l_debug varchar2(1) ;
2571 l_test number(1);
2572 begin
2573 L_DEBUG := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N'); -- to generate debug messages
2574
2575 IF L_DEBUG = 'Y' THEN
2576 gms_error_pkg.gms_debug(' ARGUMENTS RECEIVED:: p_ind_rate_sch_id:' || p_ind_rate_sch_id || ',p_award_id:' || p_award_id, 'C');
2577 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');
2578 gms_error_pkg.gms_debug(',p_calling_form:' || p_calling_form || ',p_event:' || p_event, 'C');
2579 END IF;
2580
2581 x_last_updated_by := FND_GLOBAL.USER_ID;
2582 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2583 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2584
2585 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
2586 IF L_DEBUG = 'Y' THEN
2587 gms_error_pkg.gms_debug('p_idc_schedule_fixed_date is null', 'C');
2588 END IF;
2589
2590 begin
2591 l_test := 0;
2592 select 1
2593 into l_test
2594 from pa_ind_rate_sch_revisions irsr
2595 where irsr.ind_rate_sch_id = p_ind_rate_sch_id
2596 and end_date_active is null;
2597 exception
2598 when no_data_found then
2599 l_test := 0;
2600 end;
2601
2602 if l_test = 0 then
2603 select min(start_date_active), max(end_date_active)
2604 into l_start_date_min, l_end_date_max
2605 from pa_ind_rate_sch_revisions irsr
2606 where irsr.ind_rate_sch_id = p_ind_rate_sch_id;
2607 else
2608 select min(start_date_active)
2609 into l_start_date_min
2610 from pa_ind_rate_sch_revisions irsr
2611 where irsr.ind_rate_sch_id = p_ind_rate_sch_id;
2612 end if;
2613
2614 if p_calling_form = 'GMSICOVR' then -- call from override schedule screen
2615 IF L_DEBUG = 'Y' THEN
2616 gms_error_pkg.gms_debug('Call is from OVERRIDE SCHEDULES SCREEN', 'C');
2617 END IF;
2618
2619 UPDATE gms_encumbrance_items_all ITEM
2620 SET ITEM.enc_distributed_flag = 'N',
2621 ITEM.adjustment_type = 'BURDEN_RECALC',
2622 ITEM.ind_compiled_set_id = NULL,
2623 ITEM.last_update_date = SYSDATE,
2624 ITEM.last_updated_by = x_last_updated_by,
2625 ITEM.last_update_login = x_last_update_login,
2626 ITEM.request_id = x_request_id
2627 WHERE ITEM.enc_distributed_flag = 'Y'
2628 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2629 AND ITEM.project_id = p_project_id
2630 /* AND 1 = decode(p_task_id, NULL, 1,
2631 decode(ITEM.task_id ,p_task_id, 1, 2)) need to chech the top_task_id instead of task_id */
2632 /* in the following decode, if p_task_id is null, check if any other override for same award, project exists.
2633 if p_task id is not null, check if p_task_id = top_task_id for the enc. */
2634 /* calling new function item_task_validate */
2635 /* AND 1 = decode(p_task_id, NULL ,decode (1, (select 1
2636 from GMS_OVERRIDE_SCHEDULES GOS,
2637 pa_tasks TASK
2638 where GOS.award_id = p_award_id
2639 and GOS.project_id = p_project_id
2640 and nvl(ITEM.task_id,-99) = TASK.task_id
2641 and nvl(GOS.task_id,-99) = TASK.top_task_id
2642 and rownum = 1) ,2
2643 ,1)
2644 ,decode((select top_task_id
2645 from pa_tasks
2646 where task_id = ITEM.task_id), p_task_id ,1
2647 ,2))*/
2648 AND gms_pa_api3.item_task_validate(p_award_id, p_project_id, p_task_id, ITEM.task_id) = 'Y'
2649 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2650 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2651 AND EXISTS ( select null
2652 from gms_award_distributions adl
2653 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2654 and adl.award_id = p_award_id )
2655 AND ITEM.encumbrance_item_date between l_start_date_min
2656 and nvl(l_end_date_max,ITEM.encumbrance_item_date) -- dates corresponding to schedule revisions
2657 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2658 AND ITEM.ind_compiled_set_id is not null
2659 AND 1 = decode(p_event, 'INSERT', 1
2660 , (select 1
2661 from pa_ind_compiled_sets ics,
2662 pa_ind_rate_sch_revisions irsr
2663 where ITEM.ind_compiled_set_id = ics.ind_compiled_set_id
2664 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2665 and irsr.ind_rate_sch_id = p_ind_rate_sch_id
2666 and rownum = 1));
2667
2668 elsif p_calling_form = 'GMSAWEAW' then -- call from award management compliance tab
2669
2670 IF L_DEBUG = 'Y' THEN
2671 gms_error_pkg.gms_debug('Call is from AWARD MANAGEMENT SCREEN', 'C');
2672 END IF;
2673
2674 UPDATE gms_encumbrance_items_all ITEM
2675 SET ITEM.enc_distributed_flag = 'N',
2676 ITEM.adjustment_type = 'BURDEN_RECALC',
2677 ITEM.ind_compiled_set_id = NULL,
2678 ITEM.last_update_date = SYSDATE,
2679 ITEM.last_updated_by = x_last_updated_by,
2680 ITEM.last_update_login = x_last_update_login,
2681 ITEM.request_id = x_request_id
2682 WHERE ITEM.enc_distributed_flag = 'Y'
2683 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2684 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2685 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2686 AND EXISTS
2687 ( select null
2688 from gms_award_distributions adl
2689 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2690 and adl.award_id = p_award_id )
2691 AND ITEM.encumbrance_item_date between l_start_date_min and nvl(l_end_date_max,ITEM.encumbrance_item_date)
2692 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2693 AND ITEM.ind_compiled_set_id is not null
2694 AND NOT EXISTS
2695 ( select 1
2696 from GMS_OVERRIDE_SCHEDULES GOS,
2697 pa_tasks TASK
2698 where GOS.award_id = p_award_id
2699 and GOS.project_id = ITEM.project_id
2700 and ITEM.task_id = TASK.task_id
2701 and nvl(GOS.task_id, TASK.top_task_id) = TASK.top_task_id);
2702
2703 end if;
2704 else -- p_idc_schedule_fixed_date is not null, mark all associated enc items for recalc
2705 IF L_DEBUG = 'Y' THEN
2706 gms_error_pkg.gms_debug('p_idc_schedule_fixed_date is not null', 'C');
2707 END IF;
2708
2709 if p_calling_form = 'GMSICOVR' then -- call from override schedule screen
2710
2711 IF L_DEBUG = 'Y' THEN
2712 gms_error_pkg.gms_debug('call is from override schedule screen', 'C');
2713 END IF;
2714
2715 UPDATE gms_encumbrance_items_all ITEM
2716 SET ITEM.enc_distributed_flag = 'N',
2717 ITEM.adjustment_type = 'BURDEN_RECALC',
2718 ITEM.ind_compiled_set_id = NULL,
2719 ITEM.last_update_date = SYSDATE,
2720 ITEM.last_updated_by = x_last_updated_by,
2721 ITEM.last_update_login = x_last_update_login,
2722 ITEM.request_id = x_request_id
2723 WHERE ITEM.enc_distributed_flag = 'Y'
2724 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2725 AND ITEM.project_id = p_project_id
2726 /* AND 1 = decode(p_task_id, NULL, 1,
2727 decode(ITEM.task_id ,p_task_id, 1, 2)) need to chech the top_task_id instead of task_id */
2728 /* in the following decode, if p_task_id is null, check if any other override for same award, project exists.
2729 if p_task id is not null, check if p_task_id = top_task_id for the enc. */
2730 /* calling new function item_task_validate */
2731 /* AND 1 = decode(p_task_id, NULL ,decode (1, (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 = p_project_id
2736 and nvl(ITEM.task_id,-99) = TASK.task_id
2737 and nvl(GOS.task_id,-99) = TASK.top_task_id
2738 and rownum = 1) ,2
2739 ,1)
2740 ,decode((select top_task_id
2741 from pa_tasks
2742 where task_id = ITEM.task_id), p_task_id ,1
2743 ,2))*/
2744 AND gms_pa_api3.item_task_validate(p_award_id, p_project_id, p_task_id, ITEM.task_id) = 'Y'
2745 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2746 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2747 AND EXISTS ( select null
2748 from gms_award_distributions adl
2749 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2750 and adl.award_id = p_award_id )
2751 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d')
2752 AND ITEM.ind_compiled_set_id is not null
2753 AND 1 = decode(p_event, 'INSERT', 1
2754 , (select 1
2755 from pa_ind_compiled_sets ics,
2756 pa_ind_rate_sch_revisions irsr
2757 where ITEM.ind_compiled_set_id = ics.ind_compiled_set_id
2758 and ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
2759 and irsr.ind_rate_sch_id = p_ind_rate_sch_id
2760 and rownum = 1));
2761
2762 elsif p_calling_form = 'GMSAWEAW' then -- call from award management compliance tab
2763
2764 IF L_DEBUG = 'Y' THEN
2765 gms_error_pkg.gms_debug('Call is from award management screen', 'C');
2766 END IF;
2767
2768 UPDATE gms_encumbrance_items_all ITEM
2769 SET ITEM.enc_distributed_flag = 'N',
2770 ITEM.adjustment_type = 'BURDEN_RECALC',
2771 ITEM.ind_compiled_set_id = NULL,
2772 ITEM.last_update_date = SYSDATE,
2773 ITEM.last_updated_by = x_last_updated_by,
2774 ITEM.last_update_login = x_last_update_login,
2775 ITEM.request_id = x_request_id
2776 WHERE ITEM.enc_distributed_flag = 'Y'
2777 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
2778 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
2779 AND gms_pa_api2.is_award_closed(ITEM.encumbrance_item_id,ITEM.task_id, 'ENC') = 'N'
2780 AND EXISTS ( select null
2781 from gms_award_distributions adl
2782 where adl.expenditure_item_id = ITEM.encumbrance_item_id
2783 and adl.award_id = p_award_id )
2784 AND ITEM.ind_compiled_set_id is not null
2785 AND NOT EXISTS
2786 ( select 1
2787 from GMS_OVERRIDE_SCHEDULES GOS,
2788 pa_tasks TASK
2789 where GOS.award_id = p_award_id
2790 and GOS.project_id = ITEM.project_id
2791 and ITEM.task_id = TASK.task_id
2792 and nvl(GOS.task_id, TASK.top_task_id) = TASK.top_task_id)
2793 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d');
2794
2795 end if;
2796 end if;
2797 exception
2798 when others then
2799 IF L_DEBUG = 'Y' THEN
2800 gms_error_pkg.gms_debug('IN EXCEPTION BLOCK', 'C');
2801 END IF;
2802 errbuf := sqlcode;
2803 retcode := sqlerrm;
2804 end mark_enc_items_for_recalc;
2805
2806 -- Bug 6761516
2807 -- =====================
2808 -- Start of the comment
2809 -- API Name : item_task_validate
2810 -- Type : Public
2811 -- Pre_reqs : None
2812 -- Description : This function is called from mark_enc_items_for_recalc
2813 -- to perform item validations.
2814 -- 1. If record being inserted/updated/deleted from overrides schedule
2815 -- screen does not have task details, then this function returns 'N'
2816 -- if any other override exists for same project, award ,top_task for
2817 -- the task of enc. in picture combination, else returns 'Y'
2818 -- 2. If record being inserted/updated/deleted from overrides schedule
2819 -- screen has task details, then just match the top task of the enc.
2820 -- in picture with the task on the record.
2821 -- Parameters : p_award_id number,
2822 -- p_project_id number,
2823 -- p_task_id number,
2824 -- p_item_task_id number
2825 -- Return Value : 'Y' - Encumbrance needs to be marked for recalc.
2826 -- 'N' - Encumbrance should not be marked for recalc.
2827 --
2828 -- End of comments
2829 -- ===============
2830
2831 function item_task_validate (p_award_id number,
2832 p_project_id number,
2833 p_task_id number,
2834 p_item_task_id number)
2835 return varchar2 is
2836
2837 l_test number ;
2838 l_max_indx number ;
2839 l_return varchar2(1);
2840
2841 begin
2842
2843 FOR indx in 1..g_task_id_tab.count loop
2844 if g_task_id_tab(indx) = p_item_task_id then
2845 gms_error_pkg.gms_debug('Results already available for task_id:' || g_task_id_tab(indx) || ',value:' || g_test_tab(indx), 'C');
2846 return g_test_tab(indx);
2847 end if;
2848 end loop;
2849
2850 gms_error_pkg.gms_debug('First Call to task_id:' || p_item_task_id, 'C');
2851 if p_task_id is NULL then
2852
2853 begin
2854 l_test := 0;
2855 select 1
2856 into l_test
2857 from GMS_OVERRIDE_SCHEDULES GOS,
2858 pa_tasks TASK
2859 where GOS.award_id = p_award_id
2860 and GOS.project_id = p_project_id
2861 and p_item_task_id = TASK.task_id
2862 and nvl(GOS.task_id,-99) = TASK.top_task_id
2863 and rownum = 1;
2864 exception
2865 when no_data_found then
2866 l_test := 0;
2867 end;
2868
2869 l_max_indx := g_task_id_tab.count;
2870 g_task_id_tab(l_max_indx+1) := p_item_task_id;
2871 if l_test = 1 then
2872 -- as override match is found, these transactions should not be marked for recalc
2873 -- hence returning 'N'
2874 l_return := 'N';
2875 else
2876 -- as override match is not found, these transactions should be marked for recalc
2877 -- hence returning 'Y'
2878 l_return := 'Y';
2879 end if;
2880 g_test_tab(l_max_indx+1) := l_return;
2881 return(l_return);
2882
2883 else -- p_task id is not null
2884
2885 begin
2886 l_test := 0;
2887 select 1
2888 into l_test
2889 from pa_tasks
2890 where task_id = p_item_task_id
2891 and top_task_id = p_task_id;
2892 exception
2893 when no_data_found then
2894 l_test := 0;
2895 end;
2896
2897 l_max_indx := g_task_id_tab.count;
2898 g_task_id_tab(l_max_indx+1) := p_item_task_id;
2899 if l_test = 1 then
2900 -- as enc task_id matches with p_task_id, these transactions should be marked for recalc
2901 -- hence returning 'Y'
2902 l_return := 'Y';
2903 else
2904 -- as enc task_id does not match with p_task_id, these transactions should not be marked for recalc
2905 -- hence returning 'N'
2906 l_return := 'N';
2907 end if;
2908 g_test_tab(l_max_indx+1) := l_return;
2909 return(l_return);
2910
2911 end if;
2912
2913 end item_task_validate;
2914 /* Bug 6761516 end */
2915
2916 END gms_pa_api3;