1 PACKAGE BODY gms_pa_costing_pkg AS
2 -- $Header: gmspafcb.pls 120.23.12010000.2 2008/08/25 09:22:56 mumohan ship $
3
4 -- Declare variables.
5 -- Bug 5236418 : The collection variables are initialized before they are referenced.
6 t_project_id tt_project_id := tt_project_id();
7 t_award_id tt_award_id := tt_award_id();
8 t_task_id tt_task_id := tt_task_id();
9 t_expenditure_type tt_expenditure_type := tt_expenditure_type();
10 t_expenditure_item_date tt_expenditure_item_date := tt_expenditure_item_date();
11 t_actual_flag tt_actual_flag;
12 t_status_code tt_status_code;
13 t_last_update_date tt_last_update_date;
14 t_last_updated_by tt_last_updated_by;
15 t_created_by tt_created_by;
16 t_creation_date tt_creation_date;
17 t_last_update_login tt_last_update_login;
18 t_je_category_name tt_je_category_name;
19 t_je_source_name tt_je_source_name;
20 t_transfered_flag tt_transfered_flag;
21 t_document_type tt_document_type;
22 t_expenditure_organization_id tt_expenditure_organization_id := tt_expenditure_organization_id();
23 t_document_header_id tt_document_header_id;
24 t_document_distribution_id tt_document_distribution_id;
25 t_entered_dr tt_entered_dr;
26 t_entered_cr tt_entered_cr;
27 t_status_flag tt_status_flag;
28 t_bc_packet_id tt_bc_packet_id;
29 t_request_id tt_request_id;
30 t_ind_compiled_set_id tt_ind_compiled_set_id := tt_ind_compiled_set_id();
31 t_person_id tt_person_id;
32 t_job_id tt_job_id;
33 t_expenditure_category tt_expenditure_category := tt_expenditure_category();
34 t_revenue_category tt_revenue_category := tt_revenue_category();
35 t_adjusted_document_header_id tt_adjusted_document_header_id;
36 t_award_set_id tt_award_set_id;
37 t_transaction_source tt_transaction_source := tt_transaction_source();
38 t_system_linkage_function tt_transaction_source;
39 t_burdenable_raw_cost tt_burdenable_raw_cost := tt_burdenable_raw_cost();
40 t_acct_raw_cost tt_acct_raw_cost := tt_acct_raw_cost();
41 t_line_type_lookup tt_line_type_lookup;
42 t_invoice_type_lookup tt_invoice_type_lookup;
43
44 --REL12 : AP lines uptake enhancement : Added below plsql tables
45 t_invoice_id tt_invoice_id := tt_invoice_id();
46 t_invoice_distribution_id tt_invoice_distribution_id := tt_invoice_distribution_id();
47 t_sys_ref4 tt_sys_ref4 := tt_sys_ref4();
48 t_bud_task_id tt_bud_task_id := tt_bud_task_id();
49 t_adjusted_expenditure_item_id tt_document_header_id := tt_document_header_id();
50 t_txn_interface_id tt_txn_interface_id := tt_txn_interface_id();
51 t_nz_adj_flag tt_nz_adj_flag := tt_nz_adj_flag();
52
53 l_last_update date := trunc(sysdate);
54
55 -- This variable is used by all the AP interface procedures defined in this package
56 g_xface_rec get_xface_cur%ROWTYPE;
57
58 g_process varchar2(10);
59
60 -- Declare Local Procedures and Functions here.
61
62 -- Set the global debug context if either PA or GMS debug is enabled.
63 Procedure Set_Debug_Context;
64
65 -- Check if there are any CDLs to be processed for the current request_id.
66 Function More_CDLs_to_Process return varchar2;
67
68 -- This procedure fetches the set_of_books_id and packet_id from a sequence.
69 Procedure INIT;
70
71 --
72 -- Procedure to mark expenditure items as failed fundscheck for costing
73 --
74 Procedure Mark_ExpItem_As_Failed;
75
76 --
77 Procedure Delete_Concurrency_Records;
78
79
80 -- Populate GMS_BC_PACKETS with Costed Expenditure Items for fundscheck.
81 Procedure Populate_BC_Packets;
82
83 -- Populate GMS_BC_PACKETS with AP Interface txns
84 Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE);
85
86 -- Populate GMS Concurrency table. This is to control concurrent running of
87 -- costing processes.
88 Procedure Populate_Concurrency_Table(p_system_linkage in VARCHAR2);
89
90 -- Procedure execute fundscheck for both Costed Expenditures and Interface SI.
91 -- Return code is checked to see if any error occured.
92 Procedure Execute_FundsCheck(p_fck_return_code OUT NOCOPY NUMBER,
93 p_fck_err_code OUT NOCOPY VARCHAR2,
94 p_fck_err_stage OUT NOCOPY VARCHAR2);
95
96 -- Procedure to handle net zero items and adjusting items coming in
97 -- for costing and fundscheck.
98 Procedure Handle_net_zero_txn(p_packetid IN number, p_mode IN varchar2 );
99
100 -- Procedure to summarize the amounts for Award + Exp Type and update the
101 -- summary table.
102 --Procedure Summarize_Costs;
103
104 -- Procedure to update status of packet entries.
105 Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2, p_request_id IN NUMBER);
106
107 -- Procedure to create ADLs for successfully fundschecked expenditure items.
108 Procedure Create_ADLs(p_process IN VARCHAR2, p_request_id IN NUMBER);
109
110 -- Procedure to populate the indirect cost data into gms_bc_packets table.
111 -- Used for interface process.
112 Procedure Populate_Indirect_Cost(p_packet_id IN NUMBER);
113
114 -- Procedure to mark interfacing item as failed. This is used for
115 -- Interface process.
116 Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER, p_status out nocopy varchar2);
117
118 -- Procedure to post the burden costs to adjustment logs.
119 --Procedure Post_Burden_Records;
120
121 -- Procedure to initialize the pl/sql arrays.
122 Procedure Initialize_Tabs;
123
124 -- Procedure to populate arrival order sequence table.
125 -- In the current code, x_mode is not used.
126 Procedure Insert_Arrival_Order_Seq (x_packetid IN NUMBER,
127 x_mode IN VARCHAR2);
128
129 --=============================================================================
130 -- Procedure FundsCheck_CDLs does the following :
131 -- 1. Check the debug context : set debug context if either Projects or
132 -- GMS debug options are set to 'Y'.
133 -- 2. Query CDLs based on the request id passed and see if there are any
134 -- to be processed. If no CDLs are found then return to calling point.
135 -- Else process them.
136 -- 3. If there are CDLs to be processed populate gms_bc_packets table and
137 -- call gms_funds_control_pkg.gms_fck in 'X' mode for fundschecking.
138 -- 4. If packet fails fundscheck, mark the expenditures with rejection code
139 -- and delete the corresponding CDLs.
140 --
141 -- Parameters and meaning.
142 -- -----------------------
143 -- p_request_id : Request_id of the costing process being run.
144 -- p_return_status : Return status: 0 if success and 1 if failure.
145 -- p_error_code : Error Code for the failure.
146 -- p_error_stage : Stage where the failure occured.
147 --=============================================================================
148
149 Procedure FundsCheck_CDL (p_request_id IN NUMBER,
150 p_return_status OUT NOCOPY NUMBER,
151 p_error_code OUT NOCOPY VARCHAR2,
152 p_error_stage OUT NOCOPY NUMBER) IS
153
154 l_fck_return_code NUMBER;
155 l_fck_error_code varchar2(1) := NULL;
156 l_fck_error_stage varchar2(10) := NULL;
157
158 begin
159
160 g_packet_id := NULL;
161 g_debug_context := NULL;
162 g_set_of_books_id := NULL;
163 g_request_id := p_request_id;
164
165 g_error_stage := 'FundsCheck_CDL: Start';
166
167 Set_Debug_Context;
168
169 IF g_debug_context = 'Y' THEN
170 gms_error_pkg.gms_debug (g_error_stage,'C');
171 gms_error_pkg.gms_debug ('Debug profile set to : ' || g_debug_context,'C');
172 END IF;
173
174 g_error_stage := 'Checking if there are any CDLs to be processed';
175
176 if More_CDLs_to_Process = 'N' then
177 IF g_debug_context = 'Y' THEN
178 gms_error_pkg.gms_debug ('Did not find any CDLs to be processed. Exit','C');
179 END IF;
180 p_return_status := 0;
181 return;
182 end if;
183
184 -- Expenditure items need to be processed. Get packetid and set of books info.
185 g_error_stage := 'Execute INIT. Get set of books id and packet id';
186
187 IF g_debug_context = 'Y' THEN
188 gms_error_pkg.gms_debug (g_error_stage ,'C');
189 END IF;
190
191 INIT;
192
193 IF g_debug_context = 'Y' THEN
194 gms_error_pkg.gms_debug ('Set of Books : '|| g_set_of_books_id ||
195 'Packet ID : ' || g_packet_id ,'C');
196 END IF;
197
198 g_process := 'Costing';
199
200 -- Perform fundscheck for the items.
201 g_error_stage := 'Calling Execute_FundsCheck procedure';
202
203 Execute_FundsCheck(l_fck_return_code,
204 l_fck_error_code,
205 l_fck_error_stage);
206
207 if l_fck_return_code < 0 then
208
209 IF g_debug_context = 'Y' THEN
210 gms_error_pkg.gms_debug ('Execute Funds Check returned: ' || l_fck_return_code, 'C');
211 gms_error_pkg.gms_debug ('Error Stage : ' || l_fck_error_stage,'C');
212 END IF;
213 end if;
214
215 -- Mark failure result code in cost_dist_rejection_code for expenditure items that failed
216 -- funds check and delete corresponding cdl's.
217 Mark_ExpItem_As_Failed;
218
219 -- Initalize pl/sql table ..
220 Initialize_Tabs;
221
222 p_return_status := 0;
223
224 exception
225 when others then
226
227 IF g_debug_context = 'Y' THEN
228 gms_error_pkg.gms_debug ('In when others of FundsCheck_CDL. Stage :' || g_error_stage , 'C');
229 END IF;
230
231 p_return_status := -1;
232 return;
233
234 end FundsCheck_CDL;
235
236 ---
237 -------------------------------------------------------------------------------
238 -- Procedure to set the global variable g_debug_context.
239 -- Set the variable to 'Y' if either of Grants or Projects debug profile
240 -- option is set to 'Yes'.
241 -------------------------------------------------------------------------------
242 procedure Set_Debug_Context is
243
244 begin
245 if (NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N') = 'Y' or
246 NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N') = 'Y') then
247 g_debug_context := 'Y';
248 gms_error_pkg.set_debug_context;
249 end if;
250 end Set_Debug_Context;
251
252 -------------------------------------------------------------------------------
253 -- Procedure to fetch the set of books id and fetch packet_id from sequence.
254 -------------------------------------------------------------------------------
255
256 Procedure INIT is
257 l_sob_id NUMBER;
258 l_packet_id NUMBER;
259 Begin
260 select gl_bc_packets_s.nextval, set_of_books_id
261 into l_packet_id, l_sob_id
262 from pa_implementations;
263
264 g_set_of_books_id := l_sob_id;
265 g_packet_id := l_packet_id;
266
267 g_error_stage := 'Fetched Set of Books and Packet IDs';
268
269 -- Initialize currency context.
270 pa_currency.set_currency_info;
271
272 End INIT;
273
274 ---
275 -- Procedure to initialize the pl/sql arrays.
276
277 Procedure Initialize_Tabs is
278 Begin
279
280 t_project_id.delete;
281 t_award_id.delete;
282 t_task_id.delete;
283 t_expenditure_type.delete;
284 t_expenditure_item_date.delete;
285 t_expenditure_organization_id.delete;
286 t_document_header_id.delete;
287 t_document_distribution_id.delete;
288 t_entered_dr.delete;
289 t_entered_cr.delete;
290 t_burdenable_raw_cost.delete; --R12 AP lines uptake :Forward port bug 4217161
291 t_ind_compiled_set_id.delete;
292 t_person_id.delete;
293 t_job_id.delete;
294 t_expenditure_category.delete;
295 t_revenue_category.delete;
296 t_adjusted_document_header_id.delete;
297 t_award_set_id.delete;
298 t_transaction_source.delete;
299 t_system_linkage_function.delete;
300
301 End Initialize_Tabs;
302
303 ---
304 -------------------------------------------------------------------------------
305 -- Function to check if there are any transactions to be processed.
306 -- If there are any fetch them into pl/sql arrays and return 'Y' else
307 -- return 'N'.
308 -------------------------------------------------------------------------------
309
310 Function More_CDLs_to_Process return varchar2 is
311
312 cursor get_cdls_cur is
313 select cdl.expenditure_item_id, -- document_header_id
314 cdl.line_num, -- document_distribution_id
315 decode(exp.net_zero_adjustment_flag,
316 'Y', nvl(exp.adjusted_expenditure_item_id, cdl.expenditure_item_id),
317 NULL), -- adjusted_document_header_id
318 exp.transaction_source, -- transaction_source
319 adl.award_set_id, -- award_set_id
320 adl.award_id, -- award_id
321 cdl.project_id, -- project_id
322 exp.task_id, -- task_id
323 exp.expenditure_type, -- expenditure_type
324 exp.expenditure_item_date, -- expenditure_item_date
325 exp.system_linkage_function,
326 cdl.ind_compiled_set_id, -- ind_compiled_set_id
327 exp.job_id, -- job_id
328 nvl(exp.override_to_organization_id, pae.incurred_by_organization_id) expenditure_org, -- Expenditure org
329 pae.incurred_by_person_id, -- incurred by person_id
330 et.expenditure_category, -- expenditure catg
331 et.revenue_category_code, -- revenue catg
332 decode(sign(cdl.amount), 1, cdl.amount, 0), -- entered_dr
333 decode(sign(cdl.amount), -1, -1 * cdl.amount, 0), -- entered_cr
334 -- R12 AP Lines Uptake: Reversing CDL lines should have same BRC as that of original line.
335 -- This forward port of fix 4217161.
336 (SELECT NVL(adl.burdenable_raw_cost,0) * -1
337 FROM gms_award_distributions adl
338 WHERE adl.cdl_line_num = cdl.line_num_reversed
339 AND adl.expenditure_item_id = cdl.expenditure_item_id
340 AND adl.document_type = 'EXP'
341 AND adl.adl_status = 'A'
342 AND cdl.line_num_reversed IS NOT NULL ) burdenable_raw_cost
343 from pa_cost_distribution_lines cdl,
344 pa_expenditure_items exp,
345 gms_award_distributions adl,
346 pa_expenditures_all pae,
347 pa_expenditure_types et
348 where cdl.request_id = g_request_id
349 and cdl.line_type = 'R'
350 and nvl(cdl.reversed_flag,'N') <> 'Y'
351 and cdl.expenditure_item_id = exp.expenditure_item_id
352 and exp.cost_distributed_flag = 'S'
353 and cdl.expenditure_item_id = adl.expenditure_item_id
354 and nvl(adl.cdl_line_num, 1) = 1
355 and adl.adl_status = 'A'
356 and adl.document_type = 'EXP'
357 and exp.expenditure_id = pae.expenditure_id
358 and exp.expenditure_type = et.expenditure_type
359 and exp.cost_dist_rejection_code is null;
360
361
362 begin
363
364 g_error_stage := 'Opening get_cdls_cur cursor';
365
366 open get_cdls_cur;
367 fetch get_cdls_cur bulk collect
368 into t_document_header_id,
369 t_document_distribution_id,
370 t_adjusted_document_header_id,
371 t_transaction_source,
372 t_award_set_id,
373 t_award_id,
374 t_project_id,
375 t_task_id,
376 t_expenditure_type,
377 t_expenditure_item_date,
378 t_system_linkage_function,
379 t_ind_compiled_set_id,
380 t_job_id,
381 t_expenditure_organization_id,
382 t_person_id,
383 t_expenditure_category,
384 t_revenue_category,
385 t_entered_dr,
386 t_entered_cr,
387 t_burdenable_raw_cost;
388
389 close get_cdls_cur;
390
391 g_error_stage := 'Bulk count check.';
392
393 if t_document_header_id.count = 0 then
394
395 IF g_debug_context = 'Y' THEN
396 gms_error_pkg.gms_debug ('Did not find any CDLs to be processed','C');
397 END IF;
398
399 return 'N';
400 else
401 IF g_debug_context = 'Y' THEN
402 gms_error_pkg.gms_debug ('Found CDLs to be processed','C');
403 END IF;
404
405 return 'Y';
406 end if;
407
408 end More_CDLs_to_Process;
409
410 ---
411 /*------------------------------------------------------------------------
412 This procedure is called for both Costing and Interface processes.
413 Processes are identified by a global variable.
414 Do fundscheck where required.
415 Procedure returns 0 for success and -1 if procedure encounters any
416 exception.
417 --------------------------------------------------------------------------*/
418 Procedure Execute_FundsCheck(p_fck_return_code OUT NOCOPY NUMBER,
419 p_fck_err_code OUT NOCOPY VARCHAR2,
420 p_fck_err_stage OUT NOCOPY VARCHAR2) is
421 Pragma autonomous_transaction;
422
423 l_err_code varchar2(1);
424 l_err_buf varchar2(2000);
425 l_return_code varchar2(3);
426
427 l_system_linkage varchar2(3);
428 l_fc_mode varchar2(1);
429 l_partial_mode varchar2(1);
430
431 l_bc_pkt gms_bc_packets%ROWTYPE;
432 l_packet_id number;
433
434 l_fc_required varchar2(1) := 'N';
435 l_je_source_name varchar2(30);
436 l_je_category_name varchar2(30);
437 l_doc_type varchar2(3);
438 l_actual_flag varchar2(1);
439 l_status_code varchar2(1);
440 l_budget_version_id number;
441 l_entry_level_code varchar2(1);
442 l_entered_dr number;
443 l_entered_cr number;
444 l_effect_on_funds_code varchar2(1);
445
446 l_new_compiled_set_id number;
447 l_invoice_id number ;
448 l_po_matched_flag VARCHAR2(1); -- R12 AP lines uptake
449 l_comm_fc_req VARCHAR2(1); -- R12 AP lines uptake
450 l_top_task_id number;
451 l_cmt_releived VARCHAR2(1) := 'N';
452
453 --REL12 : AP lines uptake enhancement : Added below variables
454 l_old_award_id gms_awards_all.award_id%TYPE;
455 l_old_project_id pa_projects_all.project_id%TYPE;
456 l_old_task_id pa_tasks.task_id%TYPE;
457 l_bud_task_id gms_bc_packets.bud_task_id%TYPE;
458 l_adj_ei_populated VARCHAR2(1);
459
460 -- Cash based accounting variables
461 l_adl_fully_paid VARCHAR2(1); -- Flag indicating if invoice is flly paid
462 l_apdist_brc_to_relieve NUMBER; -- Variable storing the BRC to be relieved for AP
463 l_apdist_amt_to_relieve NUMBER; -- Variable storing the Invoice dist amount to be relieved for AP
464 l_cash_fc_required VARCHAR2(1); -- Flag indicating if Fundscheck is required during payment interface
465 l_erv_found VARCHAR2(1); -- Flag indicates if payment has ERV
466 l_ap_bc_pkt_id NUMBER; -- Variable to store bc_packet_id of the AP RAW record created during FC
467
468 CURSOR C_ap_bc_pkt_id (p_packet_id NUMBER) IS
469 SELECT bc_packet_id
470 FROM gms_bc_packets gbc
471 WHERE packet_id = p_packet_id
472 AND document_type = 'AP'
473 AND parent_bc_packet_id IS NULL
474 AND entered_dr <> 0 OR entered_cr <> 0 ;
475
476 -- R12 AP LINES UPTAKE:Procedure to calculate amount of burden to be releived for partial or full payments for
477 -- cash basis accounting.
478
479 PROCEDURE CALCULATE_PAYTMENT_BRC (p_adl_fully_paid OUT NOCOPY VARCHAR2 ,
480 p_erv_found OUT NOCOPY VARCHAR2 ,
481 p_cash_fc_required OUT NOCOPY VARCHAR2 ,
482 p_apdist_amt_to_relieve IN OUT NOCOPY VARCHAR2,
483 p_apdist_brc_to_relieve OUT NOCOPY NUMBER) IS
484
485 -- Cursor to check if there exists any exchange rate variance for payment
486 CURSOR c_erv_exits IS
487 SELECT 'Y'
488 FROM DUAL
489 WHERE EXISTS (SELECT 1
490 FROM ap_invoice_payments Pay,
491 ap_invoices_all inv
492 WHERE pay.invoice_payment_id = g_xface_rec.cdl_system_reference4
493 AND pay.invoice_id = inv.invoice_id
494 AND NVL(pay.exchange_rate,0) <> NVL(inv.exchange_rate,0));
495
496 -- AP Invoice payment amount to be relieved
497 CURSOR C_apdist_amount IS
498 SELECT NVL(paydist.invoice_dist_base_amount,paydist.invoice_dist_amount)
499 FROM ap_payment_hist_dists Paydist
500 WHERE paydist.pay_dist_lookup_code = 'CASH'
501 AND Paydist.invoice_distribution_id = g_xface_rec.invoice_distribution_id
502 AND PayDIST.invoice_payment_id = g_xface_rec.cdl_system_reference4;
503
504 -- Cursor to fetch burdenable raw cost and payment status on AP invoice distribution corresponding
505 -- to which payment is being interfaced
506
507 CURSOR C_ap_amt_brc IS
508 SELECT nvl(adl.burdenable_raw_cost,0),NVL(adl.payment_status_flag,'N')
509 FROM ap_invoice_distributions APD,
510 gms_award_distributions ADL
511 where adl.invoice_distribution_id = APD.invoice_distribution_id
512 and adl.adl_status = 'A'
513 and adl.document_type = 'AP'
514 and ADL.award_set_id = APD.award_id
515 and apd.invoice_id = g_xface_rec.invoice_id
516 and apd.invoice_distribution_id = g_xface_rec.invoice_distribution_id;
517
518 --BRC already consumed in current run
519 CURSOR c_pkt_brc IS
520 SELECT sum(NVL(gbc.burdenable_raw_cost,0))
521 FROM gms_bc_packets gbc
522 WHERE gbc.packet_id = g_packet_id
523 AND gbc.request_id = g_request_id
524 AND gbc.status_code = 'P'
525 AND gbc.document_header_id = g_xface_rec.invoice_id
526 AND gbc.document_distribution_id = g_xface_rec.invoice_distribution_id
527 AND gbc.document_type = 'AP';
528
529 -- Cursor to fetch amount on invoice distribution
530 CURSOR c_ap_amt IS
531 SELECT NVL(apdist.base_amount,apdist.amount)
532 FROM ap_invoice_distributions_all apdist
533 WHERE apdist.invoice_distribution_id = g_xface_rec.invoice_distribution_id
534 AND apdist.invoice_id = g_xface_rec.invoice_id ;
535
536 -- Bug : 5414183
537 -- Cursor to fetch interfaced payment amount for an invoice distribution
538 CURSOR c_ap_interfaced_pay_amt IS
539 SELECT SUM(NVL(paydist1.invoice_dist_base_amount,paydist1.invoice_dist_amount))
540 + SUM(NVL(paydist2.invoice_dist_base_amount,paydist2.invoice_dist_amount))
541 FROM ap_payment_hist_dists Paydist1,
542 ap_payment_hist_dists Paydist2
543 WHERE paydist1.pay_dist_lookup_code = 'CASH'
544 AND Paydist1.invoice_distribution_id = g_xface_rec.invoice_distribution_id
545 and Paydist2.invoice_distribution_id = g_xface_rec.invoice_distribution_id
546 AND ( paydist1.pa_addition_flag = 'Y' OR --interfaced payments
547 PayDIST1.invoice_payment_id IN ( SELECT xface.cdl_system_reference4 -- Payments marked for interface in current run
548 FROM pa_transaction_interface_all xface
549 WHERE xface.transaction_source = G_txn_source
550 and xface.cdl_system_reference2 = g_xface_rec.invoice_id
551 and xface.cdl_system_reference5 = g_xface_rec.invoice_distribution_id
552 and xface.cdl_system_reference4 is not NULL
553 and xface.TRANSACTION_STATUS_CODE ='P'))
554 AND paydist2.invoice_distribution_id = paydist1.invoice_distribution_id
555 and paydist2.payment_history_id = paydist1.payment_history_id
556 and paydist2.invoice_payment_id = paydist1.invoice_payment_id
557 and paydist2.pay_dist_lookup_code = 'DISCOUNT' ;
558
559
560
561 -- ====================================================================================
562 --
563 -- Bug : 5414183
564 -- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO GRANTS IN CASH BASED ACC
565 -- For payments, payment amount includes discount amount. So we are interfacing
566 -- only payments. But we need to relieve corresponding invoice amount for that
567 -- payment.
568 -- Invoice dist amount :100
569 -- Payment : 80
570 -- Disc : 20
571 -- Actual interface : 80
572 -- AP Relieve : 80 + 20 = 100
573 -- Functionality :
574 -- Discount is applicable when discount method is EXPENSE
575 -- Discount is applicable for tax distributions when discount method is TAX
576 -- Discount is not applicable when discount method is 'SYSTEM'
577 -- Discount is also based on the discount profile start date
578 -- ap payment record includes the discount amount and we do not need to interface
579 -- discount record because we are interfacing the payments.
580 -- But we need to relieve corresponding inv dist amount paid to relieve the ap commitment amount.
581 -- ap amount to relieve := payment amunt + discount amount (when applicable).
582 -- ====================================================================================
583 CURSOR c_get_disc_amount is
584 SELECT NVL(b.invoice_dist_base_amount , b.invoice_dist_amount) amount
585 from ap_payment_hist_dists b,
586 ap_invoice_distributions_all apd
587 where b.invoice_payment_id = g_xface_rec.cdl_system_reference4
588 and b.invoice_distribution_id = g_xface_rec.invoice_distribution_id
589 and b.pay_dist_lookup_code = 'DISCOUNT'
590 and apd.invoice_distribution_id = b.invoice_distribution_id
591 and NVL(apd.historical_flag,'N') <> 'Y'
592 and apd.expenditure_item_date >= PA_TRX_IMPORT.G_Profile_Discount_Start_date
593 and apd.line_type_lookup_code = decode ( PA_TRX_IMPORT.G_discount_Method,
594 'TAX', decode (apd.line_type_lookup_code,
595 'TIPV', 'TIPV',
596 'TERV','TERV',
597 'TRV', 'TRV',
598 'NONREC_TAX') ,
599 'SYSTEM', 'NOT APPLICABLE',
600 apd.line_type_lookup_code ) ;
601
602
603 l_ap_outstanding_brc NUMBER;
604 l_ap_amt NUMBER;
605 l_ap_interfaced_pay_amt NUMBER;
606 l_apdist_pkt_brc NUMBER;
607 l_apdist_brc_to_relieve NUMBER;
608 l_disc_amount NUMBER := 0;
609
610 BEGIN
611
612 -- Fetching applicable discount amount absed on the discount method and profile discount start date.
613 --
614 -- Bug : 5414183
615 -- : R12.PJ:XB1:QA:BC:INCORRECT AMOUNTS INTERFACED TO GRANTS IN CASH BASED ACC
616 --
617 OPEN c_get_disc_amount ;
618 fetch c_get_disc_amount into l_disc_amount ;
619 close c_get_disc_amount ;
620 l_disc_amount := NVL(l_disc_amount,0) ;
621
622 --Fetch BRC on ap invoice distribution
623 OPEN C_ap_amt_brc;
624 FETCH C_ap_amt_brc INTO l_ap_outstanding_brc,p_adl_fully_paid;
625 CLOSE C_ap_amt_brc;
626
627 --Fetch BRC from pa_bc_packets which is consumed during current run
628 OPEN c_pkt_brc;
629 FETCH c_pkt_brc INTO l_apdist_pkt_brc;
630 CLOSE c_pkt_brc;
631
632 l_ap_outstanding_brc := l_ap_outstanding_brc + l_apdist_pkt_brc;
633
634 -- Calculating AP amount to be relieved.
635 -- Only for payment with ERV or for final payment , p_apdist_amt_to_relieve <> g_xface_rec.acct_raw_cost
636
637 -- Intializing the amount
638 p_apdist_amt_to_relieve := g_xface_rec.acct_raw_cost + l_disc_amount;
639
640 -- If final payment relieve total outstanding amount
641 IF (pa_trx_import.g_finalPaymentId = g_xface_rec.cdl_system_reference4) THEN
642
643 OPEN c_ap_amt;
644 FETCH c_ap_amt INTO l_ap_amt ;
645 CLOSE c_ap_amt;
646
647 OPEN c_ap_interfaced_pay_amt;
648 FETCH c_ap_interfaced_pay_amt INTO l_ap_interfaced_pay_amt ;
649 CLOSE c_ap_interfaced_pay_amt;
650
651 p_apdist_amt_to_relieve := l_ap_amt +l_disc_amount - l_ap_interfaced_pay_amt;
652
653 ELSE
654 -- Check if there exists ERV ,IF yes then AP amount to be relieved should be calculated
655 -- and based on which BRC has to be dervied .In this case FC is required as AP and EXP amounts will not match.
656 OPEN c_erv_exits;
657 FETCH c_erv_exits INTO p_erv_found;
658 CLOSE c_erv_exits;
659
660 IF l_erv_found = 'Y' THEN
661 OPEN C_apdist_amount;
662 FETCH C_apdist_amount INTO p_apdist_amt_to_relieve;
663 CLOSE C_apdist_amount;
664 p_apdist_amt_to_relieve := p_apdist_amt_to_relieve + l_disc_amount;
665 END IF;
666
667 END IF;
668
669 -- Fundscheck required for below scenarios :
670 -- 1. IF AP distriution adl.payment_status_flag = 'Y' then this distribution has been
671 -- fully paid and no more adjustments will be allowed on this distribution.i.e.
672 -- donot relieve any ap commitment.IN this case EXP should go thru fundscheck.
673 -- 2. IF payment has ERV then perform FC as there will be difference in amounts
674 -- 3. IF payment is VOIDED/refund then perform FC as +ve amount resrved against AP with
675 -- new calculated BRC
676
677 IF (l_adl_fully_paid = 'Y')
678 OR (g_xface_rec.acct_raw_cost < 0 )
679 OR (l_erv_found = 'Y' )
680 OR (pa_trx_import.g_finalPaymentId = g_xface_rec.cdl_system_reference4) THEN
681
682 p_cash_fc_required := 'Y' ;
683
684 END IF;
685
686 -- R12 AP Lines Uptake enhancement : Cash basie accounting Flow
687 -- BRC calculation logic :
688 --------------------------
689 -- 1. IF final payment then relieve leftover BRC.
690 -- 2. Calculate BRC for VOIDED payment,refund payments
691 -- 3. IF BRC on AP distribution is greater than or equal to payment amount then BRC to
692 -- relieve will be equal to that of payment amount
693 -- 3. IF BRC on AP distribution is less than payment amount then relieve left over BRC
694 -- 5. Update AP distribution ADL with current available BRC and final payment status in tieback
695 --
696 IF (pa_trx_import.g_finalPaymentId = g_xface_rec.cdl_system_reference4) THEN
697 p_apdist_brc_to_relieve := l_ap_outstanding_brc;
698 ELSIF p_apdist_amt_to_relieve < 0 THEN
699 p_apdist_brc_to_relieve := NULL;
700 ELSIF p_apdist_amt_to_relieve >= l_ap_outstanding_brc THEN
701 p_apdist_brc_to_relieve := l_ap_outstanding_brc;
702 ELSIF p_apdist_amt_to_relieve < l_ap_outstanding_brc THEN
703 p_apdist_brc_to_relieve := p_apdist_amt_to_relieve;
704 END IF;
705
706 END CALCULATE_PAYTMENT_BRC;
707
708 PROCEDURE GET_BUD_TASK_DETAILS (p_award_id NUMBER ,
709 p_project_id NUMBER,
710 p_task_id NUMBER ) IS
711
712 BEGIN
713
714 g_error_stage := 'GET_BUD_TASK_DETAILS: Checking for current baselined budget';
715 IF g_debug_context = 'Y' THEN
716 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
717 END IF;
718
719 -- REL12 : AP lines uptake enhancement :
720 -- Code introduced to fetch budget_version_id and bud_task_id only if
721 -- award/project/task changes.
722
723 IF l_old_award_id <> p_award_id OR
724 l_old_project_id <> p_project_id OR
725 l_old_task_id <> p_task_id THEN
726
727 begin
728 select gbv.budget_version_id, pb.entry_level_code
729 into l_budget_version_id, l_entry_level_code
730 from gms_budget_versions gbv, pa_budget_entry_methods pb
731 where award_id = p_award_id
732 and project_id = p_project_id
733 and budget_status_code = 'B'
734 and current_flag = 'Y'
735 and gbv.budget_entry_method_code = pb.budget_entry_method_code;
736 exception
737 when no_data_found then
738
739 IF g_debug_context = 'Y' THEN
740 gms_error_pkg.gms_debug ('No current baselined budget found. Fail the current packet.','C');
741 gms_error_pkg.gms_debug ('Award: ' || p_award_id || ' Project: ' || p_project_id, 'C');
742 END IF;
743
744 p_fck_return_code := -1;
745 p_fck_err_stage := 'F10';
746 return;
747 end;
748
749 -- bug 3674107 start
750 l_bud_task_id := null;
751
752 select top_task_id
753 into l_top_task_id
754 from pa_tasks
755 where task_id = p_task_id;
756
757 if l_entry_level_code = 'P' then
758 l_bud_task_id := 0;
759 l_top_task_id := 0;
760 elsif l_entry_level_code = 'L' then
761 l_bud_task_id := p_task_id;
762 elsif l_entry_level_code = 'T' then
763 l_bud_task_id := l_top_task_id;
764 else -- entry_level_code = 'M'
765 begin
766 select task_id
767 into g_xface_rec.bud_task_id
768 from gms_balances
769 where budget_version_id = l_budget_version_id
770 and task_id = p_task_id
771 and balance_type = 'BGT'
772 --Added the following conditions for Bug 4859071
773 and project_id = g_xface_rec.project_id
774 and award_id = g_xface_rec.award_id
775 and rownum =1;
776 exception
777 when no_data_found then
778 begin
779 select task_id
780 into l_bud_task_id
781 from gms_balances
782 where budget_version_id = l_budget_version_id
783 and task_id = (select top_task_id
784 from pa_tasks
785 where task_id = p_task_id)
786 and balance_type = 'BGT'
787 --Added the following conditions for Bug 4859071
788 and project_id = g_xface_rec.project_id
789 and award_id = g_xface_rec.award_id
790 and rownum =1;
791 exception
792 when no_data_found then
793 l_bud_task_id := p_task_id;
794 end;
795 end;
796 end if;
797
798 if l_bud_task_id is null then
799 l_bud_task_id := p_task_id;
800 end if;
801 -- bug 3674107 end.
802
803 l_old_award_id := p_award_id ;
804 l_old_project_id := p_project_id;
805 l_old_task_id := p_task_id;
806
807 END If;
808 EXCEPTION
809 WHEN OTHERS THEN
810 IF g_debug_context = 'Y' THEN
811 gms_error_pkg.gms_debug ('GET_BUD_TASK_DETAILS : when others exception .\','C');
812 END IF;
813 RAISE;
814 END GET_BUD_TASK_DETAILS;
815
816 Begin
817
818 -- If there was any abnormal termination previously, we want to clean up
819 -- that data before proceding.
820 g_error_stage := 'Calling delete pending transactions';
821
822 gms_funds_control_pkg.delete_pending_txns(l_err_code, l_err_buf);
823
824 if g_process = 'Costing' then
825
826 g_error_stage := 'Calling Populate BC Packets for Costing Fundscheck';
827
828 populate_bc_packets;
829
830 g_error_stage := 'Calling Handle Net Zero Txn for Net Zero';
831 -- If adjusting and adjusted items are in the same packet, mark them as passed.
832
833 Handle_Net_Zero_Txn(g_packet_id, 'Net_Zero');
834
835 g_error_stage := 'Calling Handle Net Zero Txn for Check_Adjusted';
836 -- If the adjusted item is not already fundschecked or not in the same packet
837 -- mark the adjusting item as failed.
838
839 Handle_Net_Zero_Txn(g_packet_id, 'Check_Adjusted');
840
841 select decode(t_system_linkage_function(1), 'ST', 'ST',
842 'OT', 'ST',
843 'USG', 'USG',
844 'PJ', 'USG',
845 'ER', 'ER',
846 'INV', 'USG',
847 'WIP', 'USG',
848 'VI', 'VI')
849 into l_system_linkage
850 from dual;
851
852 -- Call fundscheck in 'X' mode. Internally this is treated same as
853 -- 'R' by fundscheck with some flow changes.
854
855 l_fc_mode := 'X';
856 l_partial_mode := 'Y';
857
858 g_error_stage := 'Calling populate concurrency table';
859 populate_concurrency_table(l_system_linkage);
860
861 commit;
862
863 p_fck_return_code := 0;
864
865 g_error_stage := 'Calling gms_fck';
866
867 -- Call Fundscheck process.
868 if NOT gms_funds_control_pkg.gms_fck (x_sobid => g_set_of_books_id,
869 x_packetid => g_packet_id,
870 x_mode => l_fc_mode,
871 x_override => 'N',
872 x_partial => l_partial_mode,
873 x_user_id => fnd_global.user_id,
874 x_user_resp_id => fnd_global.resp_id,
875 x_execute => 'Y',
876 x_return_code => l_return_code,
877 x_e_code => l_err_code,
878 x_e_stage => l_err_buf) THEN
879
880 g_error_stage := 'Execute FundsCheck : ' || l_err_buf ;
881
882 IF g_debug_context = 'Y' THEN
883 gms_error_pkg.gms_debug ('Fundscheck errored at: ' || l_err_buf, 'C');
884 END IF;
885
886 -- Set the error codes and related information.
887 p_fck_return_code := -1;
888 p_fck_err_code := l_err_code;
889 p_fck_err_stage := l_err_buf;
890
891 end if;
892
893 commit;
894
895 return;
896
897 end if; -- g_process = 'Costing'
898
899 --- Interface Processing starts.
900
901 /*--------------------------------------------------------------------------
902
903 For : Standard Invoices, Expense Reports and Tax lines
904
905 Check if ind_compiled_set_id changed.
906 if ind_compiled_set_id changed then
907 create switching entries for AP and EXP
908 fundscheck them
909 else
910 create bucket switching entries for AP and EXP
911 end if;
912 --
913 AP Discounts are not fundschecked.
914 Prepayment applied distribution line is not fundschecked.
915
916 For : Discounts and Prepayment applied distribution lines
917 If amount for either of these is +ve, then do fundscheck
918 else create EXP entries.
919 For both these entries, no credit is given back to AP.
920
921 --------------------------------------------------------------------------*/
922
923 if g_process = 'Interface' then
924
925 g_error_stage := 'In Execute_FundsCheck for Interface items';
926 IF g_debug_context = 'Y' THEN
927 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
928 END IF;
929
930 --REL12 : AP lines uptake enhancement : Intializing variables
931 l_old_award_id := 0;
932 l_old_project_id := 0;
933 l_old_task_id := 0;
934 l_new_compiled_set_id := 0;
935 l_adj_ei_populated := 'N';
936
937 -- Intializing cash based accounting related variables
938 l_adl_fully_paid := 'N';
939 l_apdist_brc_to_relieve := NULL;
940 l_apdist_amt_to_relieve := g_xface_rec.acct_raw_cost;
941 l_cash_fc_required := 'N';
942 l_erv_found := 'N';
943 l_ap_bc_pkt_id := 0;
944
945 -- REL12 : AP lines uptake enhancement :
946 -- Compiled_set_id should be queried only for the commitment transaction.
947 -- For expenditures, latest compiled set id will always match with the compiled set id
948 -- on the adjusted/non adjusted expenditure items. This is controlled by interface process
949 -- which rejects interfacing of payables adjustments against uncosted expenditure Items.
950
951 g_error_stage := 'Getting new ind compiled set id';
952 IF g_debug_context = 'Y' THEN
953 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
954 END IF;
955
956 l_new_compiled_set_id := gms_cost_plus_extn.get_award_cmt_compiled_set_id(g_xface_rec.task_id,
957 g_xface_rec.expenditure_item_date,
958 g_xface_rec.expenditure_type,
959 g_xface_rec.expenditure_organization_id,
960 'C',
961 g_xface_rec.award_id);
962
963 IF g_debug_context = 'Y' THEN
964 gms_error_pkg.gms_debug ('New compiled set id is : ' || l_new_compiled_set_id, 'C');
965 END IF;
966
967 IF g_xface_rec.adjusted_expenditure_item_id IS NOT NULL THEN
968 l_adj_ei_populated := 'Y' ;
969 END IF;
970
971 --REL12 : AP lines uptake enhancement :
972 --Loop for all pa_transaction_interface_all records having same invoice_id,
973 --invoice distribution id and Invoice payment id
974
975 FOR i in t_txn_interface_id.FIRST..t_txn_interface_id.LAST LOOP --REL12 :Ap lines Uptake enhancement
976
977 l_system_linkage := 'VI';
978 l_fc_mode := 'I';
979 l_partial_mode := 'N';
980 l_fc_required := 'N';
981
982 -- Create a bc packets record with default values. Update as required.
983
984 l_bc_pkt.packet_id := g_packet_id;
985 l_bc_pkt.project_id := t_project_id(i);
986 l_bc_pkt.award_id := t_award_id(i);
987 l_bc_pkt.task_id := t_task_id(i);
988 l_bc_pkt.expenditure_type := t_expenditure_type(i);
989 l_bc_pkt.expenditure_item_date := t_expenditure_item_date(i);
990 l_bc_pkt.actual_flag := 'E';
991 l_bc_pkt.status_code := 'P';
992 l_bc_pkt.set_of_books_id := g_set_of_books_id;
993 l_bc_pkt.je_category_name := 'Purchase Invoices';
994 l_bc_pkt.je_source_name := 'Payables';
995 l_bc_pkt.transfered_flag := 'N';
996 l_bc_pkt.document_type := 'AP';
997 l_bc_pkt.expenditure_organization_id := t_expenditure_organization_id(i);
998 l_bc_pkt.document_header_id := t_invoice_id(i);
999 l_bc_pkt.document_distribution_id := t_invoice_distribution_id(i);
1000 l_bc_pkt.entered_dr := 0;
1001 l_bc_pkt.entered_cr := 0;
1002 l_bc_pkt.effect_on_funds_code := 'I';
1003 l_bc_pkt.result_code := NULL;
1004 l_bc_pkt.burdenable_raw_cost := t_burdenable_raw_cost(i);
1005 l_bc_pkt.request_id := g_request_id;
1006 -- For transactions latest compiled set id will be same as the one stamped on EI table.
1007 l_bc_pkt.ind_compiled_set_id := t_ind_compiled_set_id(i);
1008 l_bc_pkt.vendor_id := g_xface_rec.vendor_id;
1009 l_bc_pkt.expenditure_category := t_expenditure_category(i);
1010 l_bc_pkt.revenue_category := t_revenue_category(i);
1011 l_bc_pkt.transaction_source := g_txn_source;
1012 l_bc_pkt.gl_bc_packets_rowid := t_txn_interface_id(i);
1013 l_bc_pkt.txn_interface_id := t_txn_interface_id(i);
1014
1015
1016 IF g_debug_context = 'Y' THEN
1017 gms_error_pkg.gms_debug ('Transaction source : ' || g_txn_source, 'C');
1018 END IF;
1019
1020 -- R12 AP lines uptake : Logic for handling transactions where only actuals need to be reserved
1021 -- and no commitment relieving is required.
1022 -- Data processed : AP DISCOUNTS , PREPAYMENT Matched to PO and 11i PREPAYMENT APPLICATIONS
1023
1024 -- R12 AP lines uptake : Intialize variable holding whether fundscheck is required or NOT
1025 l_comm_fc_req := 'Y';
1026
1027 IF (g_txn_source = 'AP DISCOUNTS' ) THEN
1028
1029 l_comm_fc_req := 'N' ;
1030
1031 -- PREAPYMENT matched to PO
1032 ELSIF g_xface_rec.invoice_type_lookup_code = 'PREPAYMENT' THEN
1033
1034 SELECT DECODE(po_distribution_id,NULL,'N','Y')
1035 INTO l_po_matched_flag
1036 FROM ap_invoice_distributions
1037 WHERE invoice_id = g_xface_rec.invoice_id
1038 AND invoice_distribution_id = g_xface_rec.invoice_distribution_id
1039 AND line_type_lookup_code = 'ITEM';
1040
1041 IF l_po_matched_flag = 'Y' THEN
1042 l_comm_fc_req := 'N' ;
1043 END IF;
1044
1045 -- 11i PREAPYMENT applications (PREPAY) : which are not fundschecked
1046 -- In R12 prepayment applications are fundschecked
1047 ELSIF g_xface_rec.line_type_lookup_code = 'PREPAY' THEN
1048
1049 IF NVL(g_xface_rec.fc_status,'N') = 'N' THEN
1050 l_comm_fc_req := 'N' ;
1051 END IF;
1052
1053 END IF;
1054
1055
1056 -- R12 AP lines uptake : Below commitment costs are NOT fundscheck hence reserve just the actual cost
1057 -- AP DISCOUNTS : To support R12 and 1ii AP DISCOUNTS
1058 -- PO matched PREPAYMENT : In R12 prepayments are no longer interfaced to projects .The below code
1059 -- is to support 11i historical transactions which are interfaced to projects
1060 -- PREPAY applications :
1061 -- Accrual based acccounting : In R12 applications of prepayments are no longer
1062 -- interfaced to projects.Below code is to support 11i historical transactions.
1063 -- Cash based acccounting : In R12 applications of prepayments are interfaced
1064 -- from new ap_prepay_apps_dist (cdl_system_refernce4 is not null).as payments .
1065 -- 11i transactions are interfaced as invoice dists (cdl_system_refernce4 is null).
1066
1067 IF ( (g_txn_source = 'AP DISCOUNTS' OR
1068 g_xface_rec.line_type_lookup_code = 'PREPAYMENT' OR
1069 g_xface_rec.line_type_lookup_code = 'PREPAY' ) AND
1070 l_comm_fc_req = 'N'
1071 ) THEN
1072
1073 GET_BUD_TASK_DETAILS (l_bc_pkt.award_id,l_bc_pkt.project_id,l_bc_pkt.task_id);
1074
1075 l_bc_pkt.bud_task_id := l_bud_task_id;
1076 l_bc_pkt.top_task_id := l_top_task_id;
1077 l_bc_pkt.budget_version_id := l_budget_version_id;
1078
1079 if nvl(t_acct_raw_cost(i), 0) > 0 THEN
1080
1081 l_bc_pkt.entered_dr := pa_currency.round_currency_amt(t_acct_raw_cost(i));
1082 l_bc_pkt.entered_cr := 0;
1083 l_bc_pkt.effect_on_funds_code := 'D';
1084 --
1085 -- REL12 AP Lines Uptake
1086 -- Source and reversal transaction is getting interfaced together. So we do not need to
1087 -- funds check becoz it is a net zero transaction.
1088 --
1089 IF NVL( t_nz_adj_flag(i) , 'N') = 'Y' then
1090 l_fc_required := 'N';
1091 ELSE
1092 l_fc_required := 'Y';
1093 l_bc_pkt.burdenable_raw_cost := NULL; --> this will be calculated
1094 END IF ;
1095
1096 else
1097
1098 l_bc_pkt.entered_cr := ABS(pa_currency.round_currency_amt(t_acct_raw_cost(i))) ;
1099 l_bc_pkt.entered_dr := 0;
1100
1101 -- R12 AP lines uptake : For reversing expneditures BRC and ind_compiled_set_id should be
1102 -- copied from original expenditure
1103
1104 IF t_adjusted_expenditure_item_id(i) IS NOT NULL THEN
1105 l_bc_pkt.burdenable_raw_cost := -1 * t_burdenable_raw_cost(i) ;
1106 ELSE
1107 l_bc_pkt.burdenable_raw_cost := NULL ;
1108 END IF;
1109
1110 l_bc_pkt.effect_on_funds_code := 'I';
1111 l_fc_required := 'N';
1112 l_bc_pkt.document_type := 'EXP';
1113 l_bc_pkt.document_header_id := t_txn_interface_id(i);
1114 l_bc_pkt.document_distribution_id := 1;
1115 l_bc_pkt.actual_flag := 'A';
1116
1117 end if;
1118
1119 populate_bc_packets(l_bc_pkt);
1120
1121 -- calculate the burdenable raw cost and update bc_packet entries.
1122
1123 IF NVL( t_nz_adj_flag(i) , 'N') = 'N' then
1124 if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
1125 g_error_stage := 'Could not get burdenable raw cost..fail';
1126 IF g_debug_context = 'Y' THEN
1127 gms_error_pkg.gms_debug ('Could not get burdenable raw cost..1', 'C');
1128 END IF;
1129 p_fck_return_code := -1;
1130 p_fck_err_stage := 'F06';
1131 commit;
1132 return;
1133 end if;
1134 END IF ;
1135
1136 END IF;
1137
1138 -- this is for Std, Exp Report and Tax lines. check if the ind compiled set
1139 -- id is same if same, creating bucket switching bc packet entries.
1140 -- else create bc packet entries for fundschecking.
1141
1142 -- Bug 3681990
1143 -- Prepayment invoice interfaced to grants without an ADLs.
1144 -- Prepayments not matching to PO and not discount and not prepayment application
1145 -- should fall under standard invoice category.
1146 --
1147
1148 IF ( g_txn_source<> 'AP DISCOUNTS' AND
1149 g_xface_rec.line_type_lookup_code <> 'PREPAYMENT' AND
1150 g_xface_rec.line_type_lookup_code <> 'PREPAY' AND
1151 l_comm_fc_req <> 'N'
1152 ) THEN
1153
1154 g_error_stage := 'Processing Std, ER, Tax lines..';
1155 -- if burden changed, we want to fundscheck the distributions. otherwise
1156 -- simply do a AP to EXP switch.
1157
1158 -- R12 AP lines Uptake enhancement :
1159 --
1160 -- FC required only if :
1161 -- a. Burden multiplier has been modified for both cash based and accrual based
1162 -- accounting
1163 -- b. Payment mapped to an invoice has exchange rate variance
1164 -- c. For Cash based accounting if Invoice has been fully paid then no more
1165 -- adjustments will be performed against AP ,IN this case EXP should go thru
1166 -- Fundscheck.
1167 --
1168 -- If no FC required then switch amounts between AP and EXP buckets i.e.
1169 -- Put negative amount in AP bucket and put positive amounts in EXP bucket
1170 -- where ABS(+ve amt) = ABS(-ve amt).
1171 -- a. For cash based accounting payments are not FC'ed in payables ,only
1172 -- the corresponding invoice was FC'ed .Hence during payment interface and
1173 -- when there is no change in burden multipler
1174 -- relieve amount = payment amount against AP and create EXP's for payment amt.
1175 -- b. For accrual based accounting, when there is no change in burden multipler
1176 -- c. if the currenct txn record is a adjusting exp item i.e. adjusted_expenditure_item_id
1177 -- is not null
1178 -- d. If the txn records are net zero items i.e. both parent and reversal getting interfaced
1179 -- in same run.
1180
1181
1182 -- R12 AP lines uptake: IF its cash based accounting and payments are being interfaced
1183 -- then call local procedure to populate amount and BRC variables for paymenmts .
1184 -- IN R12,for cash based accounting historical invoices will be interfaced as
1185 -- invoice distributions itself and NOT the payments.
1186
1187 IF (NVL(PA_TRX_IMPORT.G_cash_based_accounting,'N') = 'Y' AND
1188 g_xface_rec.cdl_system_reference4 IS NOT NULL) THEN
1189
1190 CALCULATE_PAYTMENT_BRC (p_adl_fully_paid => l_adl_fully_paid ,
1191 p_erv_found => l_erv_found ,
1192 p_cash_fc_required => l_cash_fc_required,
1193 p_apdist_amt_to_relieve => l_apdist_amt_to_relieve,
1194 p_apdist_brc_to_relieve => l_apdist_brc_to_relieve);
1195 END IF;
1196
1197 -- Bug 4017468 : Added nvl function as there was no burden on AP lines.
1198 if nvl(l_new_compiled_set_id,0) = nvl(g_xface_rec.ind_compiled_set_id,0)
1199 OR t_adjusted_expenditure_item_id(i) IS NOT NULL
1200 OR NVL( t_nz_adj_flag(i) , 'N') = 'Y'
1201 OR ( NVL(l_cash_fc_required,'N') = 'N' AND g_xface_rec.cdl_system_reference4 IS NOT NULL) then
1202
1203 g_error_stage := 'Indirect rate did not change..switch buckets';
1204 IF g_debug_context = 'Y' THEN
1205 gms_error_pkg.gms_debug (g_error_stage, 'C');
1206 END IF;
1207
1208 IF NVL( t_nz_adj_flag(i) , 'N') = 'Y' then
1209 l_cmt_releived := 'N';
1210 END IF ;
1211
1212 IF l_cmt_releived = 'N' THEN
1213
1214 l_bc_pkt.ind_compiled_set_id := g_xface_rec.ind_compiled_set_id;
1215 l_bc_pkt.project_id := g_xface_rec.project_id;
1216 l_bc_pkt.award_id := g_xface_rec.award_id;
1217 l_bc_pkt.task_id := g_xface_rec.task_id;
1218
1219 GET_BUD_TASK_DETAILS (l_bc_pkt.award_id,l_bc_pkt.project_id,l_bc_pkt.task_id);
1220 l_bc_pkt.bud_task_id := l_bud_task_id;
1221 l_bc_pkt.top_task_id := l_top_task_id;
1222 l_bc_pkt.budget_version_id := l_budget_version_id;
1223 -- giving credit to AP... Which is okay.
1224 l_bc_pkt.entered_cr := pa_currency.round_currency_amt(g_xface_rec.acct_raw_cost);
1225 l_bc_pkt.entered_dr := 0;
1226 IF (NVL(PA_TRX_IMPORT.G_cash_based_accounting,'N') = 'Y' AND
1227 g_xface_rec.cdl_system_reference4 IS NOT NULL) THEN
1228 l_bc_pkt.entered_cr := l_apdist_amt_to_relieve;
1229 l_bc_pkt.burdenable_raw_cost := -1 * l_apdist_brc_to_relieve;
1230 ELSE
1231 l_bc_pkt.burdenable_raw_cost := -1 * g_xface_rec.burdenable_raw_cost;
1232 END IF;
1233 l_bc_pkt.effect_on_funds_code := 'I';
1234 -- Irrespective of amount sign fundscheck is not required as AP document has been
1235 -- Fundschecked in Payables. --??
1236 l_fc_required := 'N';
1237 l_bc_pkt.document_type := 'AP';
1238
1239 populate_bc_packets(l_bc_pkt);
1240 IF NVL( t_nz_adj_flag(i) , 'N') = 'Y' then
1241 l_cmt_releived := 'N';
1242 ELSE
1243 l_cmt_releived := 'Y';
1244 END IF ;
1245
1246 END IF;
1247
1248 l_bc_pkt.project_id := t_project_id(i);
1249 l_bc_pkt.award_id := t_award_id(i);
1250 l_bc_pkt.task_id := t_task_id(i);
1251
1252 GET_BUD_TASK_DETAILS (l_bc_pkt.award_id,l_bc_pkt.project_id,l_bc_pkt.task_id);
1253 l_bc_pkt.bud_task_id := l_bud_task_id;
1254 l_bc_pkt.top_task_id := l_top_task_id;
1255 l_bc_pkt.budget_version_id := l_budget_version_id;
1256 l_bc_pkt.ind_compiled_set_id := t_ind_compiled_set_id(i);
1257 l_bc_pkt.entered_dr := pa_currency.round_currency_amt(t_acct_raw_cost(i));
1258 l_bc_pkt.entered_cr := 0;
1259
1260 -- R12 AP lines uptake : For reversing expneditures BRC and ind_compiled_set_id should be
1261 -- copied from original expenditure
1262
1263 IF (NVL(PA_TRX_IMPORT.G_cash_based_accounting,'N') = 'Y' AND
1264 g_xface_rec.cdl_system_reference4 IS NOT NULL) THEN
1265 l_bc_pkt.burdenable_raw_cost := l_apdist_brc_to_relieve;
1266 ELSE
1267 -- Bug 5389130
1268 IF t_adjusted_expenditure_item_id(i) IS NOT NULL THEN
1269 l_bc_pkt.burdenable_raw_cost := -1 * t_burdenable_raw_cost(i);
1270 ELSE
1271 l_bc_pkt.burdenable_raw_cost := t_burdenable_raw_cost(i);
1272 END IF;
1273
1274 END IF;
1275
1276 IF t_adjusted_expenditure_item_id(i) IS NOT NULL THEN
1277 IF t_acct_raw_cost(i) >0 THEN
1278 IF NVL( t_nz_adj_flag(i) , 'N') = 'Y' then
1279 l_fc_required := 'N';
1280 ELSE
1281 l_fc_required := 'Y';
1282 END IF ;
1283 l_bc_pkt.effect_on_funds_code := 'D';
1284 ELSE
1285 l_bc_pkt.document_type := 'EXP';
1286 l_bc_pkt.document_header_id := t_txn_interface_id(i);
1287 l_bc_pkt.document_distribution_id := 1;
1288 l_bc_pkt.effect_on_funds_code := 'I';
1289 l_bc_pkt.actual_flag := 'A';
1290 /* Bug 5487306 : When we interface a reversing AP distribution , the original distribution of which is already interfaced
1291 to Grants , then the burdenable raw cost for the reversing AP distribution is 0 but the burdenable raw cost for the
1292 expenditure (reversing the expenditure created for the original AP distribution) will be the negative of the
1293 burdenable raw cost for the expenditure created for the original AP distribution. So fundscheck is required in this
1294 scenario. */
1295 IF nvl(g_xface_rec.acct_raw_cost,0) < 0 AND
1296 nvl(g_xface_rec.burdenable_raw_cost,0) = 0 AND
1297 nvl(t_burdenable_raw_cost(i),0) <> 0 then
1298 l_fc_required := 'Y';
1299 ELSE
1300 l_fc_required := 'N';
1301 END IF;
1302 END IF;
1303
1304 populate_bc_packets(l_bc_pkt);
1305
1306 -- calculate the burdenable raw cost and update bc_packet entries.
1307 IF NVL( t_nz_adj_flag(i) , 'N') = 'N' then
1308 if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
1309 g_error_stage := 'Could not get burdenable raw cost..fail';
1310 IF g_debug_context = 'Y' THEN
1311 gms_error_pkg.gms_debug ('Could not get burdenable raw cost..2', 'C');
1312 END IF;
1313
1314 p_fck_return_code := -1;
1315 p_fck_err_stage := 'F06';
1316 commit;
1317 return;
1318 end if;
1319 END IF ;
1320
1321 ELSE
1322 l_bc_pkt.document_type := 'EXP';
1323 l_bc_pkt.document_header_id := t_txn_interface_id(i);
1324 l_bc_pkt.document_distribution_id := 1;
1325 l_bc_pkt.effect_on_funds_code := 'D';
1326 l_bc_pkt.actual_flag := 'A';
1327 l_fc_required := 'N';
1328
1329 populate_bc_packets(l_bc_pkt);
1330
1331 END IF;
1332
1333 -- populate burden for both the raw lines.
1334 --populate_indirect_cost(g_packet_id); -- shifted outside the loop
1335 IF g_debug_context = 'Y' THEN
1336 gms_error_pkg.gms_debug ('Populated Invoice switching entries', 'C');
1337 END IF;
1338 g_error_stage := 'Done with invoice switching entries';
1339
1340 else -- ind compiled set id changed OR FC required for cash based accounting scenarios
1341
1342 g_error_stage := 'Indirect Rate Changed..processing';
1343 if g_debug_context = 'Y' THEN
1344 gms_error_pkg.gms_debug (g_error_stage, 'C');
1345 end if;
1346
1347 -- Start of code for Commitment line record inserting in gms_bc_packets
1348 IF l_adl_fully_paid <> 'Y' THEN -- Will be YES for fully paid cash based accounting invoice
1349
1350 l_bc_pkt.project_id := g_xface_rec.project_id;
1351 l_bc_pkt.award_id := g_xface_rec.award_id;
1352 l_bc_pkt.task_id := g_xface_rec.task_id;
1353
1354 GET_BUD_TASK_DETAILS (l_bc_pkt.award_id,l_bc_pkt.project_id,l_bc_pkt.task_id);
1355 l_bc_pkt.bud_task_id := l_bud_task_id;
1356 l_bc_pkt.top_task_id := l_top_task_id;
1357 l_bc_pkt.budget_version_id := l_budget_version_id;
1358
1359 -- For exchange rate Variance scenario,amount to be relieved for AP is different from acct_raw_cost
1360 IF l_erv_found = 'Y' THEN
1361 IF l_apdist_amt_to_relieve >=0 THEN
1362 l_bc_pkt.entered_cr := pa_currency.round_currency_amt(l_apdist_amt_to_relieve);
1363 l_bc_pkt.entered_dr := 0;
1364 l_bc_pkt.effect_on_funds_code := 'I';
1365 ELSE -- IF +ve amount being reserved against AP then calculate BRC.
1366 l_bc_pkt.entered_dr := pa_currency.round_currency_amt(ABS(l_apdist_amt_to_relieve));
1367 l_bc_pkt.entered_cr := 0;
1368 l_bc_pkt.effect_on_funds_code := 'D';
1369 END IF;
1370 ELSE
1371 l_bc_pkt.entered_cr := 0;
1372 l_bc_pkt.entered_dr := 0;
1373 l_bc_pkt.effect_on_funds_code := 'I';
1374 END IF;
1375
1376 IF (NVL(PA_TRX_IMPORT.G_cash_based_accounting,'N') = 'Y' AND
1377 g_xface_rec.cdl_system_reference4 IS NOT NULL) THEN
1378 l_bc_pkt.burdenable_raw_cost := -1 * l_apdist_brc_to_relieve;
1379 ELSE
1380 l_bc_pkt.burdenable_raw_cost := -1 * g_xface_rec.burdenable_raw_cost;
1381 END IF;
1382
1383 l_bc_pkt.actual_flag := 'E';
1384 l_bc_pkt.document_type := 'AP';
1385
1386 populate_bc_packets(l_bc_pkt);
1387
1388 -- calculate the burdenable raw cost if null
1389 if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
1390 g_error_stage := 'Could not get burdenable raw cost..fail';
1391 IF g_debug_context = 'Y' THEN
1392 gms_error_pkg.gms_debug ('Could not get burdenable raw cost..2', 'C');
1393 END IF;
1394
1395 p_fck_return_code := -1;
1396 p_fck_err_stage := 'F06';
1397 COMMIT;
1398 RETURN;
1399 end if;
1400
1401 -- Fetching BC packet Id associated with AP raw record inserted.
1402 -- THis is used at later point to flip document_type ='EXP' for actual records
1403 OPEN C_ap_bc_pkt_id(g_packet_id);
1404 FETCH C_ap_bc_pkt_id INTO l_ap_bc_pkt_id;
1405 CLOSE C_ap_bc_pkt_id;
1406
1407 END IF ;
1408
1409 -- End of code for Commitment line record insertion in gms_bc_packets
1410
1411 -- Start of code for actual line record insertion in gms_bc_packets
1412
1413 l_bc_pkt.project_id := t_project_id(i);
1414 l_bc_pkt.award_id := t_award_id(i);
1415 l_bc_pkt.task_id := t_task_id(i);
1416
1417 GET_BUD_TASK_DETAILS (l_bc_pkt.award_id,l_bc_pkt.project_id,l_bc_pkt.task_id);
1418 l_bc_pkt.bud_task_id := l_bud_task_id;
1419 l_bc_pkt.top_task_id := l_top_task_id;
1420 l_bc_pkt.budget_version_id := l_budget_version_id;
1421 -- populate the reversing entry. rest all entries are same as above.
1422 l_bc_pkt.ind_compiled_set_id := l_new_compiled_set_id;
1423 l_bc_pkt.actual_flag := 'A';
1424
1425 -- For exchange rate Variance scenario,amount to be relieved for AP is different from acct_raw_cost
1426 IF l_erv_found = 'Y' THEN
1427 IF g_xface_rec.acct_raw_cost >=0 THEN
1428 l_bc_pkt.entered_cr := pa_currency.round_currency_amt(g_xface_rec.acct_raw_cost);
1429 l_bc_pkt.entered_dr := 0;
1430 l_bc_pkt.effect_on_funds_code := 'I';
1431 ELSE -- IF +ve amount being reserved against AP then calculate BRC.
1432 l_bc_pkt.entered_dr := pa_currency.round_currency_amt(ABS(g_xface_rec.acct_raw_cost));
1433 l_bc_pkt.entered_cr := 0;
1434 l_bc_pkt.effect_on_funds_code := 'D';
1435 END IF;
1436 ELSE
1437 l_bc_pkt.entered_cr := 0;
1438 l_bc_pkt.entered_dr := 0;
1439 l_bc_pkt.effect_on_funds_code := 'D';
1440 END IF;
1441
1442 IF (NVL(PA_TRX_IMPORT.G_cash_based_accounting,'N') = 'Y' AND
1443 g_xface_rec.cdl_system_reference4 IS NOT NULL) THEN
1444 l_bc_pkt.burdenable_raw_cost := l_apdist_brc_to_relieve;
1445 ELSE
1446 l_bc_pkt.burdenable_raw_cost := t_burdenable_raw_cost(i);
1447 END IF;
1448
1449 populate_bc_packets(l_bc_pkt);
1450
1451 l_fc_required := 'Y';
1452 -- call FC.
1453 IF g_debug_context = 'Y' THEN
1454 gms_error_pkg.gms_debug ('Populated Invoice data for fundscheck', 'C');
1455 END IF;
1456 g_error_stage := 'Populated invoice data for fundscheck';
1457
1458 -- calculate the burdenable raw cost and update bc_packet entries.
1459 if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
1460 g_error_stage := 'Could not get burdenable raw cost..fail';
1461 IF g_debug_context = 'Y' THEN
1462 gms_error_pkg.gms_debug ('Could not get burdenable raw cost..2', 'C');
1463 END IF;
1464
1465 p_fck_return_code := -1;
1466 p_fck_err_stage := 'F06';
1467 COMMIT;
1468 RETURN;
1469 end if;
1470 -- End of code for actual line record insertion in gms_bc_packets
1471 end if;
1472
1473 end if; -- source and line type check
1474
1475 END LOOP;
1476
1477 if l_fc_required = 'N' then
1478
1479 populate_indirect_cost(g_packet_id);
1480
1481 g_error_stage := 'Calling setup_rlmi for non-fundschecked txns.';
1482 IF g_debug_context = 'Y' THEN
1483 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1484 END IF;
1485 gms_funds_control_pkg.setup_rlmi(g_packet_id, 'R', l_err_code, l_err_buf);
1486
1487 g_error_stage := 'Calling update top task and parent resource for non-FCd txns';
1488 IF g_debug_context = 'Y' THEN
1489 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1490 END IF;
1491 gms_cost_plus_extn.update_top_tsk_par_res(g_packet_id);
1492
1493 g_error_stage := 'Populate Arrival Order Sequence Order table';
1494 IF g_debug_context = 'Y' THEN
1495 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1496 END IF;
1497 Insert_Arrival_Order_Seq(g_packet_id, 'R');
1498
1499 g_error_stage := 'Update the status and results codes for non-fcd txns';
1500 IF g_debug_context = 'Y' THEN
1501 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1502 END IF;
1503 update gms_bc_packets gbc
1504 set gbc.result_code = 'P76',
1505 gbc.award_result_code = 'P76',
1506 gbc.top_task_result_code = 'P76',
1507 gbc.task_result_code = 'P76',
1508 gbc.res_grp_result_code = 'P76',
1509 gbc.res_result_code = 'P76'
1510 where gbc.packet_id = g_packet_id
1511 and nvl(result_code, 'P76') like 'P%';
1512
1513
1514 p_fck_return_code := 0;
1515
1516 commit;
1517
1518 return;
1519
1520 else
1521
1522 populate_concurrency_table(l_system_linkage);
1523
1524 commit;
1525
1526 p_fck_return_code := 0;
1527
1528 if NOT gms_funds_control_pkg.gms_fck (x_sobid => g_set_of_books_id,
1529 x_packetid => g_packet_id,
1530 x_mode => l_fc_mode,
1531 x_override => 'N',
1532 x_partial => l_partial_mode,
1533 x_user_id => fnd_global.user_id,
1534 x_user_resp_id => fnd_global.resp_id,
1535 x_execute => 'Y',
1536 x_return_code => l_return_code,
1537 x_e_code => l_err_code,
1538 x_e_stage => l_err_buf) THEN
1539
1540 g_error_stage := 'Execute FundsCheck : ' || p_fck_err_stage ;
1541
1542 IF g_debug_context = 'Y' THEN
1543 gms_error_pkg.gms_debug ('Fundscheck returned error..2', 'C');
1544 gms_error_pkg.gms_debug ('Error : ' || l_err_code || ' Stage : ' || l_err_buf, 'C');
1545 END IF;
1546 -- Set the error codes and related information exit.
1547
1548 p_fck_return_code := -1;
1549 p_fck_err_code := l_err_code;
1550 p_fck_err_stage := l_err_buf;
1551
1552 commit;
1553
1554 return;
1555
1556 end if;
1557
1558 -- if fundscheck succeeds then switch the document.
1559 -- For lines getting switched to EXP, we populate with
1560 -- transaction interface id.
1561
1562 g_error_stage := 'Updating Fundschecked txns with EXP doc type';
1563 IF g_debug_context = 'Y' THEN
1564 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1565 END IF;
1566
1567 --
1568 -- Bug : 3603121
1569 -- PO matched PREPAYMENT should gets funds check during the interface to
1570 -- grants accounting.
1571 --
1572 -- Psuedo-code for update below :
1573 -- if distribution is never fundschecked in AP then there will be
1574 -- a single set of records which can be switched to 'EXP'. Those identified are
1575 -- 'AP DISCOUNTS', 'PREPAY' (distribution for applied prepayment)
1576 -- and PREPAYMENT matched to PO.
1577 -- else
1578 -- there will be reversing pairs for AP and EXP. update
1579 -- them using new ind_compiled_set_id.
1580 -- end if;
1581
1582 IF ( (g_txn_source = 'AP DISCOUNTS' OR
1583 g_xface_rec.line_type_lookup_code = 'PREPAYMENT' OR
1584 g_xface_rec.line_type_lookup_code = 'PREPAY' ) AND
1585 l_comm_fc_req = 'N'
1586 ) THEN
1587
1588 update gms_bc_packets
1589 set document_header_id = txn_interface_id,
1590 document_distribution_id = 1,
1591 document_type = 'EXP',
1592 actual_flag = 'A'
1593 where packet_id = g_packet_id;
1594
1595 --else -- for all other fc'd txns
1596 ELSIF l_adj_ei_populated ='Y' THEN
1597
1598 update gms_bc_packets
1599 set document_header_id = txn_interface_id,
1600 document_distribution_id = 1,
1601 document_type = 'EXP',
1602 actual_flag = 'A'
1603 where packet_id = g_packet_id
1604 AND effect_on_funds_code = 'D';
1605
1606 else -- ind compiled set id donot match
1607
1608 -- Update bc records where entered_dr <>0 OR entered_cr <> 0
1609 -- This update is for payment with exchange rate variance in cash based accounting ,
1610 update gms_bc_packets
1611 set document_header_id = txn_interface_id,
1612 document_distribution_id = 1,
1613 document_type = 'EXP',
1614 actual_flag = 'A'
1615 where packet_id = g_packet_id
1616 AND bc_packet_id NOT IN (SELECT l_ap_bc_pkt_id
1617 FROM DUAL
1618 UNION ALL
1619 SELECT bc_packet_id
1620 FROM gms_bc_packets
1621 WHERE parent_bc_packet_id = l_ap_bc_pkt_id
1622 AND packet_id = g_packet_id )
1623 AND (entered_dr <> 0 OR entered_cr <>0 ) ;
1624
1625 -- update switching raw record to correct doc type and raw cost.
1626 update gms_bc_packets
1627 set document_header_id = txn_interface_id,
1628 document_distribution_id = 1,
1629 document_type = 'EXP',
1630 entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1631 1, g_xface_rec.acct_raw_cost, 0),
1632 entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
1633 -1, abs(g_xface_rec.acct_raw_cost), 0)
1634 where packet_id = g_packet_id
1635 and ind_compiled_set_id = l_new_compiled_set_id
1636 and parent_bc_packet_id is null
1637 and document_type = 'AP'
1638 and nvl(burden_adjustment_flag, 'N') = 'N'
1639 AND entered_dr = 0
1640 AND entered_cr = 0 ;
1641
1642 --
1643 -- update switching burden record to correct doc type.
1644 g_error_stage := 'Updating burden record to EXP';
1645 IF g_debug_context = 'Y' THEN
1646 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1647 END IF;
1648
1649 -- bug : 3612707 incorrect actuals in funds check burdenable cost.
1650 -- bug : 3607250 burdenable cost -ve
1651 update gms_bc_packets
1652 set document_type = 'EXP',
1653 document_header_id = txn_interface_id,
1654 document_distribution_id = 1
1655 where packet_id = g_packet_id
1656 and document_type = 'AP'
1657 and parent_bc_packet_id in ( select a.bc_packet_id
1658 from gms_bc_packets a
1659 where a.ind_compiled_set_id = l_new_compiled_set_id
1660 and a.document_type = 'EXP'
1661 and a.packet_id = g_packet_id )
1662
1663 AND entered_dr = 0
1664 AND entered_cr = 0 ;
1665
1666 --
1667 -- update the reversing AP line with raw cost
1668 g_error_stage := 'Updating raw cost on reversing AP line';
1669 IF g_debug_context = 'Y' THEN
1670 gms_error_pkg.gms_debug(g_process||':' || g_error_stage, 'C');
1671 END IF;
1672
1673 update gms_bc_packets
1674 set entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1675 -1, abs(g_xface_rec.acct_raw_cost), 0),
1676 entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
1677 1, g_xface_rec.acct_raw_cost, 0)
1678 where packet_id = g_packet_id
1679 and ind_compiled_set_id = g_xface_rec.ind_compiled_set_id
1680 and document_type = 'AP'
1681 and parent_bc_packet_id is null
1682 and nvl(burden_adjustment_flag, 'N') = 'N'
1683 AND entered_dr = 0
1684 AND entered_cr = 0 ;
1685
1686 end if;
1687
1688 end if; -- l_fc_required = 'Y'
1689
1690 commit;
1691
1692 return;
1693
1694 end if; -- g_process := 'Interface'
1695
1696 End Execute_FundsCheck;
1697
1698 -------------------------------------------------------------------------------
1699 -- Procedure to populate gms_bc_packets table. This procedure is called from
1700 -- Costing fundscheck process.
1701 -------------------------------------------------------------------------------
1702
1703 Procedure Populate_BC_Packets is
1704
1705 l_je_source varchar2(30);
1706 l_je_category varchar2(30);
1707 l_doc_type varchar2(3);
1708 l_actual_flag varchar2(1);
1709 l_status_code varchar2(1);
1710
1711 begin
1712 g_error_stage := 'In Populate_BC_Packets for Exp Items';
1713
1714 l_je_source := 'Projects Accounting';
1715 l_doc_type := 'EXP';
1716 l_actual_flag := 'A';
1717 l_status_code := 'P';
1718
1719 g_error_stage := 'Populating BC packets with costing data for FC';
1720 forall i in t_document_header_id.FIRST..t_document_header_id.LAST
1721 insert into gms_bc_packets(
1722 packet_id,
1723 project_id,
1724 award_id,
1725 task_id,
1726 expenditure_type,
1727 expenditure_item_date,
1728 actual_flag,
1729 status_code,
1730 transfered_flag,
1731 last_update_date,
1732 last_updated_by,
1733 created_by,
1734 creation_date,
1735 last_update_login,
1736 set_of_books_id,
1737 je_category_name,
1738 je_source_name,
1739 document_type,
1740 ind_compiled_set_id,
1741 expenditure_organization_id,
1742 document_header_id,
1743 document_distribution_id,
1744 entered_dr,
1745 entered_cr,
1746 bc_packet_id,
1747 request_id,
1748 person_id,
1749 job_id,
1750 expenditure_category,
1751 revenue_category,
1752 adjusted_document_header_id,
1753 award_set_id,
1754 transaction_source,
1755 burdenable_raw_cost --R12 AP lines uptake :Forward port bug 4217161
1756 ) values
1757 (
1758 g_packet_id,
1759 t_project_id(i),
1760 t_award_id(i),
1761 t_task_id(i),
1762 t_expenditure_type(i),
1763 t_expenditure_item_date(i),
1764 l_actual_flag,
1765 l_status_code,
1766 'N',
1767 l_last_update,
1768 fnd_global.user_id,
1769 fnd_global.user_id,
1770 l_last_update,
1771 fnd_global.login_id,
1772 g_set_of_books_id,
1773 decode(t_system_linkage_function(i),
1774 'OT', 'Labor Cost',
1775 'ST', 'Labor Cost',
1776 'ER', 'Purchase Invoices',
1777 'VI', 'Purchase Invoices',
1778 'USG', 'Usage Cost',
1779 'INV', 'Inventory',
1780 'PJ', 'Miscellaneous Transaction',
1781 'WIP', 'WIP'),
1782 l_je_source,
1783 l_doc_type,
1784 t_ind_compiled_set_id(i),
1785 t_expenditure_organization_id(i),
1786 t_document_header_id(i),
1787 t_document_distribution_id(i),
1788 t_entered_dr(i),
1789 t_entered_cr(i),
1790 gms_bc_packets_s.nextval,
1791 g_request_id,
1792 t_person_id(i),
1793 t_job_id(i),
1794 t_expenditure_category(i),
1795 t_revenue_category(i),
1796 t_adjusted_document_header_id(i),
1797 t_award_set_id(i),
1798 t_transaction_source(i),
1799 t_burdenable_raw_cost(i) ); --R12 AP lines uptake :Forward port bug 4217161);
1800
1801 IF g_debug_context = 'Y' THEN
1802 gms_error_pkg.gms_debug ('Populated gms_bc_packets with costing data for FC', 'C');
1803 END IF;
1804
1805 end Populate_BC_Packets;
1806
1807 -------------------------------------------------------------------------------
1808 -- Procedure to populate gms_bc_packets table for the given packet.
1809 -- This procedure is called for Supplier interface process.
1810 -------------------------------------------------------------------------------
1811 Procedure Populate_BC_Packets(p_bc_pkt IN gms_bc_packets%ROWTYPE) is
1812
1813 PRAGMA AUTONOMOUS_TRANSACTION; -- Bug 5474308
1814
1815 begin
1816 g_error_stage := 'In Populate_BC_Packets for Interface data';
1817
1818 INSERT into gms_bc_packets ( packet_id,
1819 bc_packet_id,
1820 document_header_id,
1821 document_distribution_id,
1822 Document_type,
1823 project_id,
1824 task_id,
1825 award_id,
1826 expenditure_type,
1827 expenditure_item_date,
1828 expenditure_organization_id,
1829 bud_task_id,
1830 top_task_id,
1831 entered_dr,
1832 entered_cr,
1833 budget_version_id,
1834 burdenable_raw_cost,
1835 actual_flag,
1836 status_code,
1837 set_of_books_id,
1838 je_category_name,
1839 je_source_name,
1840 transfered_flag,
1841 status_flag,
1842 result_code,
1843 request_id ,
1844 ind_compiled_set_id,
1845 effect_on_funds_code,
1846 last_update_date,
1847 last_updated_by,
1848 created_by,
1849 creation_date,
1850 last_update_login,
1851 gl_bc_packets_rowid,
1852 vendor_id,
1853 expenditure_category, --Bug: 5003642
1854 revenue_category) values --Bug: 5003642
1855 (p_bc_pkt.packet_id,
1856 gms_bc_packets_s.nextval,
1857 p_bc_pkt.document_header_id,
1858 p_bc_pkt.document_distribution_id,
1859 p_bc_pkt.Document_type,
1860 p_bc_pkt.project_id,
1861 p_bc_pkt.task_id,
1862 p_bc_pkt.award_id,
1863 p_bc_pkt.expenditure_type,
1864 p_bc_pkt.expenditure_item_date,
1865 p_bc_pkt.expenditure_organization_id,
1866 p_bc_pkt.bud_task_id,
1867 p_bc_pkt.top_task_id,
1868 p_bc_pkt.entered_dr,
1869 p_bc_pkt.entered_cr,
1870 p_bc_pkt.budget_version_id,
1871 p_bc_pkt.burdenable_raw_cost,
1872 p_bc_pkt.actual_flag,
1873 p_bc_pkt.status_code,
1874 p_bc_pkt.set_of_books_id,
1875 p_bc_pkt.je_category_name,
1876 p_bc_pkt.je_source_name,
1877 p_bc_pkt.transfered_flag,
1878 p_bc_pkt.status_flag,
1879 p_bc_pkt.result_code,
1880 p_bc_pkt.request_id,
1881 p_bc_pkt.ind_compiled_set_id,
1882 p_bc_pkt.effect_on_funds_code,
1883 l_last_update,
1884 fnd_global.user_id,
1885 fnd_global.user_id,
1886 l_last_update,
1887 fnd_global.login_id,
1888 p_bc_pkt.gl_bc_packets_rowid,
1889 p_bc_pkt.vendor_id,
1890 p_bc_pkt.expenditure_category, --Bug: 5003642
1891 p_bc_pkt.revenue_category); --Bug: 5003642
1892
1893 IF g_debug_context = 'Y' THEN
1894 gms_error_pkg.gms_debug ('Populated BC packets with interface data for FC', 'C');
1895 END IF;
1896
1897 COMMIT;
1898
1899 end Populate_BC_Packets;
1900
1901 -------------------------------------------------------------------------------
1902 -- This procedure checks for adjusting and adjusted expenditure items.
1903 -- If they are balanced, then they are marked as successful.
1904 -- If they are not mark them as failed.
1905 -------------------------------------------------------------------------------
1906 Procedure Handle_net_zero_txn(p_packetid IN number, p_mode IN varchar2 ) is
1907
1908 Cursor c_txn is
1909 select adjusted_document_header_id,
1910 nvl(ind_compiled_set_id,-1) ind_compiled_set_id
1911 from gms_bc_packets
1912 where packet_id = p_packetid
1913 having sum(entered_dr-entered_cr) = 0
1914 group by adjusted_document_header_id,
1915 nvl(ind_compiled_set_id,-1);
1916 Begin
1917
1918 g_error_stage := 'In Handle Net Zero Procedure';
1919 if g_debug_context = 'Y' then
1920 gms_error_pkg.gms_debug ( 'Handle_net_zero_txn : Start','C');
1921 gms_error_pkg.gms_debug ( 'p_mode : '||p_mode,'C');
1922 end if;
1923
1924 If p_mode = 'Check_Adjusted' then
1925
1926 -- Fail adjusting txn. If adjusted has not been funds checked -F08
1927 IF g_debug_context = 'Y' THEN
1928 gms_error_pkg.gms_debug ('Fail adjusting txn if adjusted is not FCd', 'C');
1929 END IF;
1930
1931 update gms_bc_packets gbc
1932 set gbc.result_code = 'F08',
1933 gbc.award_result_code = 'F08',
1934 gbc.top_task_result_code = 'F08',
1935 gbc.task_result_code = 'F08',
1936 gbc.res_grp_result_code = 'F08',
1937 gbc.res_result_code = 'F08',
1938 gbc.status_code = 'R'
1939 where gbc.packet_id = p_packetid
1940 and nvl(gbc.result_code,'XX') <> 'P82'
1941 and gbc.adjusted_document_header_id is NOT NULL
1942 and gbc. adjusted_document_header_id <> gbc.document_header_id
1943 and exists
1944 (select 1
1945 from gms_award_distributions adl
1946 where adl.expenditure_item_id = gbc.adjusted_document_header_id
1947 and nvl(adl.fc_status, 'N') = 'N'
1948 and adl.adl_status = 'A'
1949 and nvl(adl.request_id,-1) <> gbc.request_id
1950 ) ;
1951
1952 Elsif p_mode = 'Net_Zero' then
1953
1954 -- Adjusted and adjusting in same packet
1955
1956 g_error_stage := 'In Handle_Net_Zero...Net_Zero mode';
1957 for recs in c_txn
1958 loop
1959
1960 update gms_bc_packets gbc
1961 set gbc.result_code = 'P82',
1962 gbc.award_result_code = 'P82',
1963 gbc.top_task_result_code = 'P82',
1964 gbc.task_result_code = 'P82',
1965 gbc.res_grp_result_code = 'P82',
1966 gbc.res_result_code = 'P82',
1967 gbc.effect_on_funds_code = 'I'
1968 where gbc.packet_id = p_packetid
1969 and gbc.adjusted_document_header_id = recs.adjusted_document_header_id /* bug 3604195 */
1970 and nvl(gbc.ind_compiled_set_id,-1) = recs.ind_compiled_set_id;
1971
1972
1973 end loop;
1974
1975 if g_debug_context = 'Y' then
1976 gms_error_pkg.gms_debug ( 'Handle_net_zero_txn : End','C');
1977 end if;
1978
1979 End If;
1980
1981 End Handle_net_zero_txn;
1982
1983 -------------------------------------------------------------------------------
1984 -- Procedure to insert record into concurrency table.
1985 -------------------------------------------------------------------------------
1986 Procedure Populate_Concurrency_Table (p_system_linkage IN VARCHAR2) IS
1987 l_exists varchar2(1);
1988 Begin
1989
1990 g_error_stage := 'Populate concurrency table...';
1991 begin
1992 select '1'
1993 into l_exists
1994 from dual
1995 where exists (select '1' from gms_concurrency_control
1996 where process_name = 'GMSFCSYS'
1997 and request_id = g_request_id);
1998 exception
1999 when no_data_found then
2000
2001 insert into gms_concurrency_control
2002 (PROCESS_NAME,
2003 PROCESS_KEY ,
2004 REQUEST_ID,
2005 LAST_UPDATE_DATE,
2006 LAST_UPDATED_BY,
2007 CREATED_BY ,
2008 CREATION_DATE,
2009 LAST_UPDATE_LOGIN )
2010 values('GMSFCSYS',
2011 DECODE(p_system_linkage,
2012 'ST', 1,
2013 'USG', 2,
2014 'ER', 3,
2015 'VI', 4),
2016 g_request_id,
2017 sysdate,
2018 fnd_global.user_id,
2019 fnd_global.user_id,
2020 sysdate,
2021 fnd_global.login_id
2022 );
2023
2024 end;
2025
2026 g_error_stage := 'Populate concurrency table..end';
2027 IF g_debug_context = 'Y' THEN
2028 gms_error_pkg.gms_debug ('Populated concurrency table..end', 'C');
2029 END IF;
2030 Exception
2031 When others then
2032 RAISE;
2033 End Populate_Concurrency_Table;
2034
2035 -------------------------------------------------------------------------------
2036 -- Procedure to mark expenditure items as failed.
2037 -------------------------------------------------------------------------------
2038 Procedure Mark_ExpItem_As_Failed is
2039
2040 cursor get_failed_exps is
2041 select distinct document_header_id,
2042 result_code
2043 from gms_bc_packets gbc
2044 where packet_id = g_packet_id
2045 and substr(nvl(result_code, 'P75'), 1, 1) = 'F'
2046 and result_code not in ('F75', 'F63')
2047 and document_type = 'EXP';
2048
2049 fc_expenditure_item_id tt_document_header_id;
2050
2051 TYPE tt_result_code is table of gms_bc_packets.result_code%TYPE;
2052 fc_result_code tt_result_code;
2053
2054 Begin
2055 g_error_stage := 'In Mark_ExpItem_As_Failed';
2056 IF g_debug_context = 'Y' THEN
2057 gms_error_pkg.gms_debug (g_error_stage, 'C');
2058 END IF;
2059
2060 open get_failed_exps;
2061 fetch get_failed_exps bulk collect into fc_expenditure_item_id,
2062 fc_result_code;
2063
2064 close get_failed_exps;
2065
2066 if fc_expenditure_item_id.count = 0 then
2067 return;
2068 end if;
2069
2070 forall i in fc_expenditure_item_id.FIRST..fc_expenditure_item_id.LAST
2071 update pa_expenditure_items
2072 set cost_distributed_flag = decode(cost_distributed_flag,'Y','N',cost_distributed_flag),
2073 cost_dist_rejection_code = fc_result_code(i) /*Added for bug 7047986 */
2074 /* decode(fc_result_code(i), -- Commented for bug 7047986
2075 'F10','F143',
2076 'F90','F10',
2077 'F91','F110',
2078 'F92','F108',
2079 'F93','F109',
2080 'F60','F111',
2081 'F12','F118',
2082 'F89','F142',
2083 'F15','F01',
2084 'F16','F122',
2085 'F17','F122',
2086 'F18','F02',
2087 'F19','F03',
2088 'F21','F04',
2089 'F13','F128',
2090 'F14','F128',
2091 'F94','F128',
2092 'F67','F05',
2093 'F73','F05',
2094 'F78','F05',
2095 'F79','F05',
2096 'F95','F05',
2097 'F40','F06',
2098 'F44','F06',
2099 'F45','F06',
2100 'F46','F06',
2101 'F47','F06',
2102 'F48','F06',
2103 'F49','F06',
2104 'F76','F06',
2105 'F50','F07',
2106 'F51','F07',
2107 'F52','F07',
2108 'F53','F07',
2109 'F54','F07',
2110 fc_result_code(i)) */ -- Mapping GMS to PA code
2111 where expenditure_item_id = fc_expenditure_item_id(i)
2112 and request_id = g_request_id;
2113
2114 forall i in fc_expenditure_item_id.FIRST..fc_expenditure_item_id.LAST
2115 delete from pa_cost_distribution_lines
2116 where expenditure_item_id = fc_expenditure_item_id(i)
2117 and request_id = g_request_id
2118 and line_num in (select document_distribution_id
2119 from gms_bc_packets
2120 where document_header_id = fc_expenditure_item_id(i)
2121 and packet_id = g_packet_id
2122 and parent_bc_packet_id is null
2123 and document_type = 'EXP');
2124
2125 IF g_debug_context = 'Y' THEN
2126 gms_error_pkg.gms_debug ('Updating reversed flag on CDLs..in mark exp item as failed', 'C');
2127 END IF;
2128
2129 forall i in fc_expenditure_item_id.FIRST..fc_expenditure_item_id.LAST
2130 update pa_cost_distribution_lines
2131 set reversed_flag = NULL
2132 where expenditure_item_id = fc_expenditure_item_id(i)
2133 and nvl(reversed_flag, 'N') = 'Y'
2134 and request_id = g_request_id;
2135
2136 -- Initialize pl/sql table ..
2137 fc_expenditure_item_id.delete;
2138 fc_result_code.delete;
2139
2140
2141 IF g_debug_context = 'Y' THEN
2142 gms_error_pkg.gms_debug ('End Mark_ExpItem_As_Failed', 'C');
2143 END IF;
2144
2145 END Mark_ExpItem_As_Failed;
2146
2147 -------------------------------------------------------------------------------
2148 ----------------------------------------------------------------------------------------------------------
2149 -- This Procedure inserts record in gms_bc_packet_arrival_order, once record is
2150 -- inserted lock is released.
2151 -- from gms_concurrency_control using COMMIT;
2152
2153 -- gms_bc_packet_arrival_order Table will store the order in which packets
2154 -- have completed there setup and are ready for funds check.
2155 -- This is required becase packets arrived later can pass fundscheck as setup
2156 -- has not been completed for large packets arrived before.
2157 ----------------------------------------------------------------------------------------------------------
2158
2159
2160 PROCEDURE insert_arrival_order_seq (x_packetid IN NUMBER,
2161 x_mode IN VARCHAR2) IS
2162
2163 x_err_code NUMBER;
2164 x_arrival_order_seq NUMBER;
2165
2166 BEGIN
2167
2168 g_error_stage := 'insert_arrival_order_seq';
2169
2170 SELECT 0
2171 INTO x_err_code
2172 FROM gms_concurrency_control
2173 WHERE process_name = 'GMSFCTRL'
2174 FOR UPDATE;
2175
2176 SELECT gms_bc_packet_arrival_order_s.NEXTVAL
2177 INTO x_arrival_order_seq
2178 FROM DUAL;
2179
2180 g_error_stage := 'IN ARRIVAL ORD: INSRT';
2181
2182 INSERT INTO gms_bc_packet_arrival_order
2183 (packet_id,
2184 arrival_seq,
2185 last_update_date,
2186 last_updated_by)
2187 VALUES (x_packetid,
2188 x_arrival_order_seq,
2189 SYSDATE,
2190 fnd_global.user_id);
2191
2192 END insert_arrival_order_seq;
2193
2194 -------------------------------------------------------------------------------
2195 -- This procedure is called from Projects Costing processes. It is called
2196 -- after Projects is done with their TieBack process.
2197 -- Parameters :
2198 -- p_request_id : Request ID of the calling process
2199 -- p_return_status : 0 if successful, -1 if any exception occurs.
2200 -- p_error_code : error code sent back if an exception occurs
2201 -- p_error_stage : Stage where the error/exception occured.
2202 -------------------------------------------------------------------------------
2203 ---
2204 Procedure FundsCheck_TieBack (p_request_id IN NUMBER,
2205 p_return_status OUT NOCOPY NUMBER,
2206 p_error_code OUT NOCOPY VARCHAR2,
2207 p_error_stage OUT NOCOPY NUMBER) is
2208 begin
2209
2210 g_error_stage := 'FundsCheck_TieBack: ';
2211
2212 if not gms_cost_plus_extn.update_source_burden_raw_cost(g_packet_id, 'R', 'Y') then
2213 p_return_status := -1;
2214
2215 g_error_stage := 'Update Source Burden Raw Cost failed..rollback';
2216 IF g_debug_context = 'Y' THEN
2217 gms_error_pkg.gms_debug (g_error_stage, 'C');
2218 END IF;
2219
2220 return;
2221 end if;
2222
2223 g_error_stage := 'Calling update_gms_bc_packets from FundsCheck_TieBack';
2224 IF g_debug_context = 'Y' THEN
2225 gms_error_pkg.gms_debug (g_error_stage, 'C');
2226 END IF;
2227
2228 update_gms_bc_packets(g_process, g_request_id);
2229
2230 g_error_stage := 'Calling Create_ADLs from FundsCheck_TieBack';
2231 IF g_debug_context = 'Y' THEN
2232 gms_error_pkg.gms_debug (g_error_stage, 'C');
2233 END IF;
2234
2235 -- Note: Do not change the order of create_adls and update_gms_bc_packets ...
2236 create_adls(g_process, g_request_id);
2237
2238 g_error_stage := 'Calling Delete_concurrency_records from FundsCheck_TieBack';
2239 IF g_debug_context = 'Y' THEN
2240 gms_error_pkg.gms_debug (g_error_stage, 'C');
2241 END IF;
2242
2243 delete_concurrency_records;
2244
2245 p_return_status := 0;
2246 return;
2247
2248 end FundsCheck_TieBack;
2249
2250 -------------------------------------------------------------------------------
2251 -- Procedure to update the status of transactions in gms_bc_packets.
2252 -- Parameters :
2253 -- p_process : 'Costing' or 'Interface'.
2254 -- p_request_id : Request ID of the calling process.
2255 --
2256 -- Notes:
2257 -- For Costing related transactions, we do the following :
2258 -- Update status_code in gms_bc_packets to Accepted or Rejected.
2259 --
2260 -- For Interface related transactions, we do the following :
2261 -- Txn_Interface_ID is updated on gms_bc_packets.document_header_id
2262 -- after Fundscheck is done. This is done so that we know which
2263 -- transactions are created for 'EXP' document type. Now, we update
2264 -- document_header_id with expenditure_item_id by joining with
2265 -- txn_interface_id.
2266 -------------------------------------------------------------------------------
2267 ---
2268 Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
2269 p_request_id IN NUMBER) is
2270 Begin
2271 g_error_stage := 'Update_GMS_BC_Packets...start';
2272 IF g_debug_context = 'Y' THEN
2273 gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
2274 END IF;
2275
2276 -- If this procedure is called from interface then we need to use
2277 -- request_id for update ...
2278 If p_process = 'Interface' then
2279 update gms_bc_packets
2280 set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2281 'P', 'A',
2282 'R')
2283 where request_id = p_request_id
2284 and status_code = 'P';
2285 Else
2286 -- costing, use g_packet_id for update ..
2287 update gms_bc_packets
2288 set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
2289 'P', 'A',
2290 'R')
2291 where packet_id = g_packet_id
2292 and status_code = 'P';
2293 End If;
2294
2295 IF g_debug_context = 'Y' THEN
2296 gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
2297 END IF;
2298
2299 End Update_GMS_BC_Packets;
2300
2301 -------------------------------------------------------------------------------
2302 -- Procedure to create ADLs for the transactions that passed fundscheck.
2303 -- Parameters :
2304 -- p_process : 'Costing' or 'Interface'.
2305 -- p_request_id : Request ID of the calling process.
2306 -------------------------------------------------------------------------------
2307 Procedure Create_ADLs(p_process IN VARCHAR2,
2308 p_request_id IN NUMBER) is
2309
2310 cursor reversed_cur is
2311 select cdl.expenditure_item_id, cdl.line_num
2312 from pa_cost_distribution_lines cdl,
2313 --pa_expenditure_items_all exp,
2314 gms_bc_packets gbc
2315 where gbc.packet_id = g_packet_id
2316 and gbc.parent_bc_packet_id is null
2317 and gbc.status_code = 'A'
2318 --and exp.expenditure_item_id = gbc.document_header_id
2319 and cdl.expenditure_item_id = gbc.document_header_id
2320 and cdl.request_id + 0 = p_request_id
2321 and mod(gbc.document_distribution_id,2) = 0
2322 --and exp.cost_distributed_flag = 'Y'
2323 and cdl.reversed_flag = 'Y';
2324
2325 Type tab_billable_flag is table of pa_cost_distribution_lines_all.billable_flag%TYPE;
2326 Type tab_line_num is table of pa_cost_distribution_lines_all.line_num%TYPE;
2327 Type tab_rlmi is table of gms_bc_packets.resource_list_member_id%TYPE;
2328 Type tab_bud_task_id is table of gms_bc_packets.bud_task_id%TYPE;
2329 Type tab_status_code is table of gms_bc_packets.status_code%TYPE;
2330 Type tab_row_id is table of varchar2(30);
2331
2332 v_ind_compiled_set_id tt_ind_compiled_set_id;
2333 v_billable_flag tab_billable_flag;
2334 v_line_num tab_line_num;
2335 v_expenditure_item_id tt_document_header_id;
2336 v_rlmi tab_rlmi;
2337 v_bud_task_id tab_bud_task_id;
2338 v_raw_cost tt_entered_dr;
2339 v_status_code tab_status_code;
2340 v_burdenable_raw_cost tt_burdenable_raw_cost; -- defined in package spec
2341 v_rowid tab_row_id;
2342 v_exp_item_id tt_document_header_id;
2343 v_cdl_line_num tab_line_num;
2344
2345 cursor get_xface_exp is
2346 select gbc.rowid, txn.expenditure_item_id
2347 from pa_transaction_interface_all txn, gms_bc_packets gbc
2348 where gbc.request_id = p_request_id
2349 and txn.txn_interface_id = gbc.document_header_id
2350 and nvl(txn.transaction_status_code, 'Z') <> 'R'
2351 and gbc.status_code = 'P'
2352 and substr(nvl(result_code, 'F'), 1, 1) = 'P'
2353 and gbc.document_type = 'EXP';
2354
2355 cursor first_adls is
2356 select gbc.document_header_id,
2357 gbc.document_distribution_id,
2358 cdl.billable_flag,
2359 gbc.resource_list_member_id,
2360 gbc.bud_task_id,
2361 nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0) raw_cost,
2362 gbc.status_code,
2363 gbc.ind_compiled_set_id,
2364 gbc.burdenable_raw_cost
2365 from gms_bc_packets gbc,
2366 pa_cost_distribution_lines cdl
2367 where gbc.packet_id = g_packet_id
2368 and gbc.document_header_id = cdl.expenditure_item_id
2369 and gbc.document_distribution_id = cdl.line_num
2370 and gbc.document_distribution_id = 1
2371 and gbc.parent_bc_packet_id is null
2372 and gbc.status_code = 'A';
2373
2374 --Variables used in insert ..
2375 v_login number;
2376 v_userid number;
2377 v_date date;
2378
2379 Begin
2380 g_error_stage := 'Create_ADLs...start';
2381 IF g_debug_context = 'Y' THEN
2382 gms_error_pkg.gms_debug (g_error_stage, 'C');
2383 END IF;
2384 --Variables used in insert ..
2385 v_login := fnd_global.login_id;
2386 v_date := sysdate;
2387 v_userid := fnd_global.user_id;
2388
2389 if (p_process = 'Costing') then
2390
2391 open first_adls;
2392 fetch first_adls bulk collect into v_expenditure_item_id, v_line_num,
2393 v_billable_flag, v_rlmi, v_bud_task_id, v_raw_cost,
2394 v_status_code, v_ind_compiled_set_id,v_burdenable_raw_cost;
2395 close first_adls;
2396
2397 if v_expenditure_item_id.count > 0 then
2398
2399 forall i in v_expenditure_item_id.FIRST..v_expenditure_item_id.LAST
2400
2401 update gms_award_distributions
2402 set ind_compiled_set_id = v_ind_compiled_set_id(i),
2403 billable_flag = v_billable_flag(i),
2404 cdl_line_num = v_line_num(i),
2405 cost_distributed_flag = 'Y',
2406 resource_list_member_id = v_rlmi(i),
2407 bud_task_id = v_bud_task_id(i),
2408 raw_cost = v_raw_cost(i),
2409 fc_status = v_status_code(i),
2410 burdenable_raw_cost = v_burdenable_raw_cost(i)
2411 where expenditure_item_id = v_expenditure_item_id(i)
2412 and adl_status = 'A'
2413 and fc_status = 'N'
2414 and nvl(cdl_line_num,1) = 1;
2415
2416 end if;
2417
2418 insert into gms_award_distributions(
2419 AWARD_SET_ID,
2420 ADL_LINE_NUM,
2421 DISTRIBUTION_VALUE,
2422 RAW_COST,
2423 DOCUMENT_TYPE,
2424 PROJECT_ID,
2425 TASK_ID,
2426 AWARD_ID,
2427 EXPENDITURE_ITEM_ID,
2428 CDL_LINE_NUM,
2429 IND_COMPILED_SET_ID,
2430 REQUEST_ID,
2431 LINE_NUM_REVERSED,
2432 RESOURCE_LIST_MEMBER_ID,
2433 ADL_STATUS,
2434 FC_STATUS,
2435 LINE_TYPE,
2436 CAPITALIZED_FLAG,
2437 REVERSED_FLAG,
2438 REVENUE_DISTRIBUTED_FLAG,
2439 BILLED_FLAG,
2440 BILL_HOLD_FLAG,
2441 BURDENABLE_RAW_COST,
2442 COST_DISTRIBUTED_FLAG,
2443 BUD_TASK_ID,
2444 BILLABLE_FLAG,
2445 LAST_UPDATE_DATE,
2446 LAST_UPDATED_BY,
2447 CREATED_BY,
2448 CREATION_DATE,
2449 LAST_UPDATE_LOGIN)
2450 select gbc.award_set_id,
2451 gbc.document_distribution_id,
2452 100,
2453 nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0),
2454 'EXP',
2455 gbc.project_id,
2456 gbc.task_id,
2457 gbc.award_id,
2458 gbc.document_header_id,
2459 cdl.line_num,
2460 gbc.ind_compiled_set_id,
2461 cdl.request_id,
2462 cdl.line_num_reversed,
2463 gbc.resource_list_member_id,
2464 'A', -- adl_status
2465 'A', -- fc_status
2466 'R', -- line_type
2467 'N', -- capitalized_flag
2468 NULL, -- reversed_flag
2469 'N', -- revenue_distributed_flag
2470 'N', -- billed_flag
2471 exp.bill_hold_flag,
2472 gbc.burdenable_raw_cost,
2473 'Y', -- cost_distributed_flag
2474 gbc.bud_task_id,
2475 cdl.billable_flag,
2476 v_date,
2477 v_userid,
2478 v_userid,
2479 v_date,
2480 v_login
2481 from pa_cost_distribution_lines cdl,
2482 pa_expenditure_items_all exp,
2483 gms_bc_packets gbc
2484 where gbc.packet_id = g_packet_id
2485 and exp.expenditure_item_id = cdl.expenditure_item_id
2486 and cdl.expenditure_item_id = gbc.document_header_id
2487 and cdl.line_num = gbc.document_distribution_id
2488 and exp.cost_distributed_flag = 'Y'
2489 and gbc.document_distribution_id > 1
2490 and gbc.parent_bc_packet_id is null
2491 and gbc.status_code = 'A';
2492
2493 -- update the reversed flag on adls.
2494 g_error_stage := 'Created costing ADLs..update reversed flag';
2495 IF g_debug_context = 'Y' THEN
2496 gms_error_pkg.gms_debug (g_error_stage, 'C');
2497 END IF;
2498
2499 open reversed_cur;
2500 fetch reversed_cur bulk collect into v_exp_item_id,v_cdl_line_num;
2501 close reversed_cur;
2502
2503 if v_exp_item_id.count > 0 then
2504
2505 forall i in v_exp_item_id.first..v_exp_item_id.last
2506 update gms_award_distributions adl
2507 set adl.reversed_flag = 'Y'
2508 where expenditure_item_id = v_exp_item_id(i)
2509 and cdl_line_num = v_cdl_line_num(i)
2510 and document_type = 'EXP'; --added for bug 6622800
2511
2512 end if;
2513
2514 elsif (p_process = 'Interface') then
2515
2516 -- update expenditure_item_id from pa_transaction_interface to
2517 -- gms_bc_packets' document_header_id.
2518 -- re-sequenced code for bug :3690812
2519
2520 open get_xface_exp;
2521 fetch get_xface_exp bulk collect into v_rowid, v_expenditure_item_id;
2522 close get_xface_exp;
2523
2524 if v_rowid.count > 0 then
2525
2526 forall i in v_rowid.first..v_rowid.last
2527 update gms_bc_packets
2528 set document_header_id = v_expenditure_item_id(i)
2529 where rowid = v_rowid(i);
2530
2531 g_error_stage := 'Creating ADLs for interface..';
2532 IF g_debug_context = 'Y' THEN
2533 gms_error_pkg.gms_debug (g_error_stage, 'C');
2534 END IF;
2535
2536 forall i in v_rowid.first..v_rowid.last
2537 insert into gms_award_distributions(
2538 AWARD_SET_ID,
2539 ADL_LINE_NUM,
2540 DISTRIBUTION_VALUE,
2541 RAW_COST,
2542 DOCUMENT_TYPE,
2543 PROJECT_ID,
2544 TASK_ID,
2545 AWARD_ID,
2546 EXPENDITURE_ITEM_ID,
2547 CDL_LINE_NUM,
2548 IND_COMPILED_SET_ID,
2549 REQUEST_ID,
2550 LINE_NUM_REVERSED,
2551 RESOURCE_LIST_MEMBER_ID,
2552 ADL_STATUS,
2553 FC_STATUS,
2554 LINE_TYPE,
2555 CAPITALIZED_FLAG,
2556 REVERSED_FLAG,
2557 REVENUE_DISTRIBUTED_FLAG,
2558 BILLED_FLAG,
2559 BILL_HOLD_FLAG,
2560 BURDENABLE_RAW_COST,
2561 COST_DISTRIBUTED_FLAG,
2562 BUD_TASK_ID,
2563 BILLABLE_FLAG,
2564 LAST_UPDATE_DATE,
2565 LAST_UPDATED_BY,
2566 CREATED_BY,
2567 CREATION_DATE,
2568 LAST_UPDATE_LOGIN)
2569 select gms_adls_award_set_id_s.NEXTVAL,
2570 gbc.document_distribution_id,
2571 100,
2572 nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0),
2573 'EXP',
2574 gbc.project_id,
2575 gbc.task_id,
2576 gbc.award_id,
2577 gbc.document_header_id,
2578 cdl.line_num,
2579 gbc.ind_compiled_set_id,
2580 cdl.request_id,
2581 cdl.line_num_reversed,
2582 gbc.resource_list_member_id,
2583 'A', -- adl_status
2584 'A', -- fc_status
2585 'R', -- line_type
2586 'N', -- capitalized_flag
2587 NULL, -- reversed_flag
2588 'N', -- revenue_distributed_flag
2589 'N', -- billed_flag
2590 exp.bill_hold_flag,
2591 gbc.burdenable_raw_cost,
2592 'Y', -- cost_distributed_flag
2593 gbc.bud_task_id,
2594 cdl.billable_flag,
2595 v_date,
2596 v_userid,
2597 v_userid,
2598 v_date,
2599 v_login
2600 from pa_cost_distribution_lines cdl,
2601 pa_expenditure_items_all exp,
2602 gms_bc_packets gbc
2603 where gbc.rowid = v_rowid(i)
2604 and exp.expenditure_item_id = cdl.expenditure_item_id
2605 and cdl.expenditure_item_id = gbc.document_header_id
2606 and cdl.line_num = gbc.document_distribution_id
2607 and gbc.document_type = 'EXP'
2608 and gbc.parent_bc_packet_id is null
2609 and gbc.status_code = 'P'
2610 and substr(nvl(result_code, 'F'), 1, 1) = 'P';
2611
2612 v_rowid.delete;
2613 v_expenditure_item_id.delete;
2614
2615 end if; /* v_rowid.count > 0 */
2616
2617 end if; /* process = 'Costing' */
2618
2619 g_error_stage := 'Create_ADLs...end';
2620 IF g_debug_context = 'Y' THEN
2621 gms_error_pkg.gms_debug (g_error_stage, 'C');
2622 END IF;
2623 End Create_ADLs;
2624
2625 -------------------------------------------------------------------------------
2626 -- Procedure to delete the concurrency record.
2627 -- This is the last step done for both Fundscheck for Costing and Interface.
2628 -------------------------------------------------------------------------------
2629 Procedure Delete_Concurrency_Records is
2630 Begin
2631 g_error_stage := 'Delete_Concurrency_Records..starts';
2632 IF g_debug_context = 'Y' THEN
2633 gms_error_pkg.gms_debug (g_error_stage, 'C');
2634 END IF;
2635
2636 delete from gms_concurrency_control
2637 where process_name = 'GMSFCSYS'
2638 and request_id = g_request_id;
2639
2640 g_error_stage := 'Delete_Concurrency_Records..end';
2641 IF g_debug_context = 'Y' THEN
2642 gms_error_pkg.gms_debug (g_error_stage, 'C');
2643 END IF;
2644
2645 End Delete_Concurrency_Records;
2646
2647 --
2648 ------------------------------------------------------------------------------
2649 -- By the time this procedure is called all GMS validations are done by
2650 -- vert_app_validate if there are any sponsored project related transactions.
2651 -- Here check if transaction_source is one of the supported ones and process
2652 -- them.
2653 --
2654 -- This procedure is called for each distribution line being interfaced
2655 -- from AP. Call is from pa_trx_import.
2656 -- p_status = NULL if successful, else error is populated into p_status.
2657 ------------------------------------------------------------------------------
2658
2659 PROCEDURE Fundscheck_Supplier_Cost( p_transaction_source IN VARCHAR2,
2660 p_txn_interface_id IN NUMBER ,
2661 p_request_id IN NUMBER,
2662 p_status IN OUT NOCOPY Varchar2 ) IS
2663
2664 l_fck_return_code varchar2(3) := NULL;
2665 l_fck_error_code varchar2(1) := NULL;
2666 l_fck_error_stage varchar2(10) := NULL;
2667 l_fck_required varchar2(1) := 'N';
2668
2669 l_status varchar2(1);
2670 i NUMBER;
2671
2672 -- Bug 5389130 : Cursor to fetch AP amount for adjustment scenario
2673 CURSOR C_fetch_ap_amount ( p_sys_ref2 VARCHAR2,
2674 p_sys_ref5 VARCHAR2,
2675 p_sys_ref4 VARCHAR2,
2676 p_interface_id NUMBER ) IS
2677 select xface.acct_raw_cost
2678 FROM pa_transaction_interface xface
2679 WHERE xface.transaction_source = G_txn_source
2680 and xface.cdl_system_reference2 = p_sys_ref2
2681 and xface.cdl_system_reference5 = p_sys_ref5
2682 and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
2683 and xface.SC_XFER_CODE = 'V'
2684 and xface.interface_id = p_interface_id
2685 and xface.TRANSACTION_STATUS_CODE = 'P';
2686
2687 -- REL12 : AP Lines uptake Enhancement
2688 -- Procedure to intialise temporary plsql tables.
2689
2690 Procedure Initialize_Tabs is
2691 Begin
2692
2693 t_txn_interface_id.delete;
2694 t_transaction_source.delete;
2695 t_invoice_id.delete;
2696 t_invoice_distribution_id.delete;
2697 t_sys_ref4.delete;
2698 t_project_id.delete;
2699 t_task_id.delete;
2700 t_award_id.delete;
2701 t_ind_compiled_set_id.delete;
2702 t_burdenable_raw_cost.delete;
2703 t_bud_task_id.delete;
2704 t_expenditure_type.delete;
2705 t_expenditure_item_date.delete;
2706 t_expenditure_organization_id.delete;
2707 t_acct_raw_cost.delete;
2708 t_expenditure_category.delete;
2709 t_revenue_category.delete;
2710 t_adjusted_expenditure_item_id.delete;
2711 t_nz_adj_flag.delete ;
2712
2713 End Initialize_Tabs;
2714
2715
2716 Begin
2717
2718 g_debug_context := NULL;
2719
2720 g_set_of_books_id := NULL;
2721
2722 p_status := NULL;
2723
2724 Set_Debug_Context;
2725
2726 g_error_stage := 'In FundsCheck_Supplier_Cost..start';
2727 IF g_debug_context = 'Y' THEN
2728 gms_error_pkg.gms_debug ('x---- Start of GMS interface process ----x', 'C');
2729 gms_error_pkg.gms_debug (g_error_stage, 'C');
2730 END IF;
2731
2732 -- Bug 5344693 : Modified the following condition so that records with transaction source as 'AP VARIANCE' are also funds checked.
2733 if p_transaction_source not in ('AP INVOICE', 'AP DISCOUNTS',
2734 'AP NRTAX', 'AP EXPENSE', 'AP ERV' , 'AP VARIANCE' ) then /* Bug 5284323 */
2735 g_error_stage := 'Not a supported txn source..return';
2736 IF g_debug_context = 'Y' THEN
2737 gms_error_pkg.gms_debug (g_error_stage, 'C');
2738 END IF;
2739 return;
2740 end if;
2741
2742 g_process := 'Interface';
2743
2744 g_txn_source := p_transaction_source;
2745
2746 g_txn_xface_id := p_txn_interface_id;
2747
2748 g_request_id := p_request_id;
2749
2750 INIT;
2751
2752 IF g_debug_context = 'Y' THEN
2753 gms_error_pkg.gms_debug ('Txn Src: ' || g_txn_source ||', xface id: ' || g_txn_xface_id
2754 ||', Request ID: ' || g_request_id, 'C');
2755 END IF;
2756
2757 g_error_stage := 'Call execute fundscheck from fundscheck_supplier_cost';
2758 IF g_debug_context = 'Y' THEN
2759 gms_error_pkg.gms_debug (g_error_stage, 'C');
2760 END IF;
2761
2762 open get_xface_cur;
2763 fetch get_xface_cur into g_xface_rec;
2764
2765 if get_xface_cur%NOTFOUND then
2766 close get_xface_cur;
2767
2768 g_error_stage := 'No interface records found to process';
2769
2770 IF g_debug_context = 'Y' THEN
2771 gms_error_pkg.gms_debug ('No Interface records to process', 'C');
2772 END IF;
2773
2774 return;
2775 else
2776 close get_xface_cur;
2777 g_error_stage := 'Interface records found to process';
2778
2779 IF g_debug_context = 'Y' THEN
2780 gms_error_pkg.gms_debug ('Interface records to process', 'C');
2781 END IF;
2782 end if;
2783
2784 -- Bug 5236418 : All the collection variables should be cleared before they are used.
2785 Initialize_Tabs;
2786
2787 -- REL12 : AP Lines uptake Enhancement
2788 -- If adjusted_expenditure_item_id is populated then the txn records stores
2789 -- adjusted/non adjusted expenditures data associated with the original invoice whose reversal is being
2790 -- interfaced.In this case fetch all interface records having same invoice_id,invoice_distribution id
2791 -- and invoice payment id.
2792
2793 -- Note :
2794 -- Global variable g_xface_rec stores Invoice data which is being interfaced
2795 -- Plsql tables starting with t_% stores either
2796 -- -> Stores data of adjusted/non adjusted expenditures associated with original invoice
2797 -- , if the invoice being interfaced is a reversal distribution.
2798 -- -> Invoice data , if the invoice being interfaced is a non reversal distribution
2799
2800
2801 IF NVL(g_xface_rec.adjusted_expenditure_item_id,0) <> 0 THEN
2802
2803 OPEN C_fetch_ap_amount (g_xface_rec.invoice_id,
2804 g_xface_rec.invoice_distribution_id,
2805 g_xface_rec.cdl_system_reference4,
2806 g_xface_rec.interface_id);
2807 fetch C_fetch_ap_amount INTO g_xface_rec.acct_raw_cost;
2808 CLOSE C_fetch_ap_amount;
2809
2810 OPEN c_txn_details(g_xface_rec.invoice_id,
2811 g_xface_rec.invoice_distribution_id,
2812 g_xface_rec.cdl_system_reference4,
2813 g_xface_rec.interface_id);
2814
2815
2816
2817 FETCH c_txn_details BULK COLLECT INTO
2818 t_txn_interface_id,
2819 t_transaction_source,
2820 t_invoice_id,
2821 t_invoice_distribution_id,
2822 t_sys_ref4,
2823 t_project_id,
2824 t_task_id,
2825 t_award_id,
2826 t_ind_compiled_set_id,
2827 t_burdenable_raw_cost,
2828 t_bud_task_id,
2829 t_expenditure_type,
2830 t_expenditure_item_date,
2831 t_expenditure_organization_id,
2832 t_acct_raw_cost,
2833 t_expenditure_category,
2834 t_revenue_category,
2835 t_adjusted_expenditure_item_id,
2836 t_nz_adj_flag ;
2837 CLOSE c_txn_details;
2838
2839 if t_txn_interface_id.count = 0 then
2840 g_error_stage := 'No interface records found to process';
2841 IF g_debug_context = 'Y' THEN
2842 gms_error_pkg.gms_debug ('No Interface records to process', 'C');
2843 END IF;
2844 return;
2845 end if;
2846 ELSE
2847
2848 -- Bug 5236418 : Used BULK COLLECT so that the collection variables need not be extended before assigning value to them.
2849
2850 SELECT
2851 g_xface_rec.txn_interface_id,
2852 g_xface_rec.transaction_source,
2853 g_xface_rec.invoice_id,
2854 g_xface_rec.invoice_distribution_id,
2855 g_xface_rec.project_id,
2856 g_xface_rec.task_id,
2857 g_xface_rec.award_id,
2858 g_xface_rec.ind_compiled_set_id,
2859 g_xface_rec.burdenable_raw_cost,
2860 g_xface_rec.bud_task_id,
2861 g_xface_rec.expenditure_type,
2862 g_xface_rec.expenditure_item_date,
2863 g_xface_rec.expenditure_organization_id,
2864 g_xface_rec.acct_raw_cost,
2865 g_xface_rec.expenditure_category,
2866 g_xface_rec.revenue_category_code,
2867 g_xface_rec.adjusted_expenditure_item_id,
2868 g_xface_rec.net_zero_adjustment_flag
2869 BULK COLLECT INTO
2870 t_txn_interface_id,
2871 t_transaction_source,
2872 t_invoice_id,
2873 t_invoice_distribution_id,
2874 t_project_id,
2875 t_task_id,
2876 t_award_id,
2877 t_ind_compiled_set_id,
2878 t_burdenable_raw_cost,
2879 t_bud_task_id,
2880 t_expenditure_type,
2881 t_expenditure_item_date,
2882 t_expenditure_organization_id,
2883 t_acct_raw_cost,
2884 t_expenditure_category,
2885 t_revenue_category,
2886 t_adjusted_expenditure_item_id,
2887 t_nz_adj_flag
2888 FROM DUAL;
2889
2890 END IF;
2891
2892
2893 Execute_FundsCheck(l_fck_return_code,
2894 l_fck_error_code,
2895 l_fck_error_stage);
2896
2897 Mark_Xface_Item_AS_Failed(g_packet_id, l_status);
2898
2899 if l_fck_return_code < 0 then
2900 p_status := substr(l_fck_error_stage, 1, 30);
2901 IF g_debug_context = 'Y' THEN
2902 gms_error_pkg.gms_debug ('Error returned by Execute Fundscheck: ' || p_status, 'C');
2903 END IF;
2904 elsif (l_status = 'F') then
2905 p_status := 'GMS_FC_ERROR';
2906 IF g_debug_context = 'Y' THEN
2907 gms_error_pkg.gms_debug ('Error returned by Mark_Xface_Item_AS_Failed', 'C');
2908 END IF;
2909 end if;
2910
2911 g_error_stage := ' -- Done with FundsCheck_Supplier_Cost..end -- ';
2912 IF g_debug_context = 'Y' THEN
2913 gms_error_pkg.gms_debug (g_error_stage, 'C');
2914 END IF;
2915
2916 return;
2917
2918 Exception
2919 when others then
2920 p_status := 'GMS_FC_ERROR';
2921 g_error_stage := 'Exception in FundsCheck_Supplier_Costs..return error';
2922 IF g_debug_context = 'Y' THEN
2923 gms_error_pkg.gms_debug (g_error_stage, 'C');
2924 END IF;
2925 return;
2926
2927 End FundsCheck_Supplier_Cost;
2928
2929 -------------------------------------------------------------------------------
2930 -- Procedure to create the indirect cost entries in gms_bc_packets.
2931 -- This is called from Interface process.
2932 -------------------------------------------------------------------------------
2933
2934 Procedure Populate_Indirect_Cost(p_packet_id IN NUMBER) IS
2935
2936 BEGIN
2937 g_error_stage := 'In Populate_Indirect_Cost..start';
2938 IF g_debug_context = 'Y' THEN
2939 gms_error_pkg.gms_debug (g_error_stage, 'C');
2940 END IF;
2941
2942 Insert into gms_bc_packets
2943 ( PACKET_ID,
2944 PROJECT_ID,
2945 AWARD_ID,
2946 TASK_ID,
2947 EXPENDITURE_TYPE,
2948 EXPENDITURE_ITEM_DATE,
2949 ACTUAL_FLAG,
2950 STATUS_CODE,
2951 LAST_UPDATE_DATE,
2952 LAST_UPDATED_BY,
2953 CREATED_BY,
2954 CREATION_DATE,
2955 LAST_UPDATE_LOGIN,
2956 SET_OF_BOOKS_ID,
2957 JE_CATEGORY_NAME,
2958 JE_SOURCE_NAME,
2959 TRANSFERED_FLAG,
2960 DOCUMENT_TYPE,
2961 EXPENDITURE_ORGANIZATION_ID,
2962 PERIOD_NAME,
2963 PERIOD_YEAR,
2964 PERIOD_NUM,
2965 DOCUMENT_HEADER_ID ,
2966 DOCUMENT_DISTRIBUTION_ID,
2967 TOP_TASK_ID,
2968 BUDGET_VERSION_ID,
2969 BUD_TASK_ID,
2970 RESOURCE_LIST_MEMBER_ID,
2971 ACCOUNT_TYPE,
2972 ENTERED_DR,
2973 ENTERED_CR ,
2974 TOLERANCE_AMOUNT,
2975 TOLERANCE_PERCENTAGE,
2976 OVERRIDE_AMOUNT,
2977 EFFECT_ON_FUNDS_CODE ,
2978 RESULT_CODE,
2979 GL_BC_PACKETS_ROWID,
2980 BC_PACKET_ID,
2981 PARENT_BC_PACKET_ID,
2982 VENDOR_ID,
2983 REQUEST_ID,
2984 IND_COMPILED_SET_ID,
2985 AWARD_SET_ID,
2986 TRANSACTION_SOURCE,
2987 EXPENDITURE_CATEGORY, --Bug: 5003642
2988 REVENUE_CATEGORY) --Bug: 5003642
2989 select
2990 gbc.PACKET_ID,
2991 gbc.PROJECT_ID,
2992 gbc.AWARD_ID,
2993 gbc.TASK_ID,
2994 icc.EXPENDITURE_TYPE,
2995 trunc(gbc.EXPENDITURE_ITEM_DATE),
2996 gbc.ACTUAL_FLAG,
2997 gbc.STATUS_CODE,
2998 gbc.LAST_UPDATE_DATE,
2999 gbc.LAST_UPDATED_BY,
3000 gbc.CREATED_BY,
3001 gbc.CREATION_DATE,
3002 gbc.LAST_UPDATE_LOGIN,
3003 gbc.SET_OF_BOOKS_ID,
3004 gbc.JE_CATEGORY_NAME,
3005 gbc.JE_SOURCE_NAME,
3006 gbc.TRANSFERED_FLAG,
3007 gbc.DOCUMENT_TYPE,
3008 gbc.EXPENDITURE_ORGANIZATION_ID,
3009 gbc.PERIOD_NAME,
3010 gbc.PERIOD_YEAR,
3011 gbc.PERIOD_NUM,
3012 gbc.DOCUMENT_HEADER_ID ,
3013 gbc.DOCUMENT_DISTRIBUTION_ID,
3014 gbc.TOP_TASK_ID,
3015 gbc.BUDGET_VERSION_ID,
3016 gbc.BUD_TASK_ID,
3017 NULL,
3018 gbc.ACCOUNT_TYPE,
3019 pa_currency.round_currency_amt(decode(sign(gbc.BURDENABLE_RAW_COST * nvl(cm.compiled_multiplier,0)), 1, gbc.burdenable_raw_cost * nvl(cm.compiled_multiplier, 0), 0)),
3020 pa_currency.round_currency_amt(decode(sign(gbc.BURDENABLE_RAW_COST * nvl(cm.compiled_multiplier,0)), -1, abs(gbc.burdenable_raw_cost * nvl(cm.compiled_multiplier, 0)), 0)), --> bug 3637934
3021 gbc.TOLERANCE_AMOUNT,
3022 gbc.TOLERANCE_PERCENTAGE,
3023 gbc.OVERRIDE_AMOUNT,
3024 gbc.EFFECT_ON_FUNDS_CODE ,
3025 gbc.RESULT_CODE,
3026 gbc.gl_bc_packets_rowid,
3027 gms_bc_packets_s.nextval,
3028 gbc.BC_PACKET_ID,
3029 gbc.vendor_id,
3030 gbc.request_id,
3031 gbc.ind_compiled_set_id,
3032 gbc.award_set_id,
3033 gbc.transaction_source,
3034 et.expenditure_category, --Bug: 5003642
3035 et.revenue_category_code --Bug: 5003642
3036 from --pa_ind_rate_sch_revisions irsr, /*6054504*/
3037 --pa_cost_bases cb, /*6054504*/
3038 pa_expenditure_types et,
3039 pa_ind_cost_codes icc,
3040 pa_cost_base_exp_types cbet,
3041 PA_COST_BASE_COST_CODES CBCC, /*6054504*/
3042 --pa_ind_rate_schedules_all_bg irs, /*6054504*/
3043 --pa_ind_compiled_sets ics, /*6054504*/
3044 pa_compiled_multipliers cm,
3045 gms_bc_packets gbc
3046 where et.expenditure_type = icc.expenditure_type
3047 and icc.ind_cost_code = cm.ind_cost_code
3048 and cbet.cost_base = cm.cost_base
3049 and cbet.cost_base_type = 'INDIRECT COST'
3050 and cbet.expenditure_type = gbc.expenditure_type
3051 and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
3052 and cm.compiled_multiplier <> 0
3053 and cbcc.cost_plus_structure = cbet.cost_plus_structure
3054 and cbcc.cost_base = cbet.cost_base
3055 and cbcc.cost_base_type = cbet.cost_base_type
3056 and cm.cost_base_cost_code_Id = cbcc.cost_base_cost_code_Id
3057 and cm.ind_cost_code = cbcc.ind_cost_code
3058 and gbc.burdenable_raw_cost <> 0
3059 and gbc.packet_id = p_packet_id;
3060 -- and gbc.document_type = 'AP' ;
3061
3062 g_error_stage := 'Done populating indirect cost';
3063 IF g_debug_context = 'Y' THEN
3064 gms_error_pkg.gms_debug (g_error_stage, 'C');
3065 END IF;
3066 END Populate_Indirect_Cost;
3067
3068 -------------------------------------------------------------------------------
3069 -- Tieback after interface process is done. This is called from pa_trx_import
3070 -- after Projects is done with their tieback. This processes data for the
3071 -- current request id.
3072 -- Parameters :
3073 -- p_request_id : Request ID of the calling process.
3074 -- p_status : 0 if successful, -1 if any exception occurs.
3075 -------------------------------------------------------------------------------
3076
3077 PROCEDURE Tieback_Interface(p_request_id IN NUMBER,
3078 p_status IN OUT NOCOPY VARCHAR2
3079 ) IS
3080
3081 cursor pkt_for_summary_update is
3082 select distinct packet_id
3083 from gms_bc_packets
3084 where request_id = p_request_id
3085 and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3086 and status_code = 'P';
3087
3088 -- Get all rejected transactions from pa_transaction_interface.
3089 -- Use this to fail gms_bc_packets records.
3090
3091 cursor get_failed_txns is
3092 select distinct gbp.packet_id,
3093 xface.transaction_rejection_code
3094 from pa_transaction_interface_all xface,
3095 gms_bc_packets gbp
3096 where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
3097 and gbp.request_id = p_request_id
3098 and gbp.parent_bc_packet_id is null
3099 and xface.transaction_status_code = 'R'
3100 and substr(nvl(gbp.result_code, 'Z'), 1, 1) <> 'F'
3101 and gbp.status_code = 'P';
3102
3103 x_packet_id number;
3104 x_error_occured varchar2(1);
3105
3106 v_all_pkts_failed varchar2(1);
3107
3108 v_packet_id tt_bc_packet_id;
3109 type tt_reject_code is table of
3110 pa_transaction_interface.transaction_rejection_code%TYPE;
3111 v_reject_code tt_reject_code;
3112 v_status varchar2(1);
3113
3114 Begin
3115
3116 g_error_stage := 'TieBack_Xface: Start';
3117 IF g_debug_context = 'Y' THEN
3118 gms_error_pkg.gms_debug (g_error_stage, 'C');
3119 END IF;
3120
3121 -- check the transactions which are rejected and fail gms_bc_packets
3122 -- entries
3123 open get_failed_txns;
3124 fetch get_failed_txns bulk collect into v_packet_id,
3125 v_reject_code;
3126
3127 close get_failed_txns;
3128
3129 if v_packet_id.count > 0 then
3130
3131 IF g_debug_context = 'Y' THEN
3132 gms_error_pkg.gms_debug('Following packets are marked as failed :', 'C');
3133 END IF;
3134 IF g_debug_context = 'Y' THEN
3135 for i in v_packet_id.first..v_packet_id.last loop
3136 gms_error_pkg.gms_debug('Failed packet : ' || v_packet_id(i), 'C');
3137 end loop;
3138 END IF;
3139
3140 forall i in v_packet_id.FIRST..v_packet_id.LAST
3141 update gms_bc_packets
3142 set result_code = 'F89',
3143 status_code = 'T',
3144 fc_error_message = 'PA_FC_ERROR: ' ||
3145 v_reject_code(i)
3146 where packet_id = v_packet_id(i);
3147
3148 end if;
3149 -- update summary and post adjustment logs.
3150
3151 v_all_pkts_failed := 'N';
3152
3153 open pkt_for_summary_update;
3154 loop
3155 fetch pkt_for_summary_update into x_packet_id;
3156
3157 if pkt_for_summary_update%ROWCOUNT = 0 then
3158 v_all_pkts_failed := 'Y';
3159 close pkt_for_summary_update;
3160 exit;
3161 end if;
3162
3163 if pkt_for_summary_update%NOTFOUND then
3164 close pkt_for_summary_update;
3165 exit;
3166 end if;
3167
3168 if not
3169 gms_cost_plus_extn.update_source_burden_raw_cost(x_packet_id, 'R', 'Y') then
3170
3171 g_error_stage := 'TieBack_Xface: Error returned from update_source_burden_raw_cost';
3172 IF g_debug_context = 'Y' THEN
3173 gms_error_pkg.gms_debug (g_error_stage || ' for Packet ID : ' || x_packet_id, 'C');
3174 END IF;
3175
3176 x_error_occured := 'Y';
3177 Mark_Xface_Item_As_Failed(x_packet_id, v_status);
3178 exit;
3179
3180 end if;
3181
3182 end loop;
3183
3184 -- If error occurs, retrurn with failure status. Rollback everything
3185 if x_error_occured = 'Y' then
3186 p_status := FND_API.G_RET_STS_UNEXP_ERROR;
3187 IF g_debug_context = 'Y' THEN
3188 gms_error_pkg.gms_debug('Call to Source Burden Raw Cost failed..return failure', 'C');
3189 END IF;
3190 return;
3191 end if;
3192
3193 -- Update final payment status for finally paid invoices
3194 UPDATE gms_award_distributions adl
3195 SET adl.payment_status_flag = 'Y'
3196 WHERE (adl.invoice_id ,adl.invoice_distribution_id) IN
3197 (SELECT trx.cdl_system_reference2,trx.cdl_system_reference5
3198 FROM gms_bc_packets pkt,
3199 pa_transaction_interface_all trx
3200 WHERE pkt.request_id = p_request_id
3201 AND pkt.txn_interface_id = trx.txn_interface_id
3202 AND substr(nvl(pkt.result_code, 'P65'), 1, 1) = 'P'
3203 AND pkt.status_code = 'P'
3204 AND pkt.document_type = 'AP'
3205 AND pkt.parent_bc_packet_id IS NULL
3206 AND pa_trx_import.g_finalPaymentId = trx.cdl_system_reference4) -- Final payment
3207 AND adl.document_type ='AP'
3208 AND adl.adl_status = 'A' ;
3209
3210 -- update the status of gms_bc_packets. update expenditure_item_id
3211 -- from pa_transaction_interface to gms_bc_packets.
3212
3213 if v_all_pkts_failed = 'N' then
3214
3215 g_error_stage := 'TieBack_Xface: Calling Create ADLs';
3216 IF g_debug_context = 'Y' THEN
3217 gms_error_pkg.gms_debug (g_error_stage, 'C');
3218 END IF;
3219
3220 -- Note:
3221 -- ADLs should be created before Updating status_code on gms_bc_packets to 'A'.
3222 -- create_adl package looks at records with status_code 'P'
3223
3224 create_adls('Interface', p_request_id);
3225
3226 g_error_stage := 'TieBack_Xface: Calling update_gms_bc_packets';
3227 IF g_debug_context = 'Y' THEN
3228 gms_error_pkg.gms_debug (g_error_stage, 'C');
3229 END IF;
3230
3231 update_gms_bc_packets('Interface', p_request_id);
3232
3233 end if;
3234
3235 p_status := FND_API.G_RET_STS_SUCCESS;
3236
3237 Exception
3238 when others then
3239 p_status := 'GMS_FC_ERROR';
3240 g_error_stage := 'Exception in TieBack_Interface..return error';
3241 IF g_debug_context = 'Y' THEN
3242 gms_error_pkg.gms_debug (g_error_stage, 'C');
3243 END IF;
3244
3245 return;
3246
3247 End Tieback_Interface;
3248
3249 --=============================================================================
3250 -- Bug : 5389130
3251 -- R12.PJ:XB7:DEV:BC: TO TRACK GRANTS INTERFACE ISSUES
3252 -- Procedure : Net_zero_adls
3253 -- Purpose : Adls creation logic for the dummy additional exp created
3254 -- to correct the accounting adjustments.
3255 -- These are new zero transactions.
3256 --
3257 -- Parameters and meaning.
3258 -- -----------------------
3259 -- p_transaction_source : Transaction source for supplier cost interface.
3260 -- p_batch : Batch name for transaction source.
3261 -- p_status : return status .
3262 -- P_xface_id : Transaction interface ID.
3263 --=============================================================================
3264 procedure Net_zero_adls( p_transaction_source IN VARCHAR2,
3265 p_batch IN VARCHAR2,
3266 P_xface_id IN NUMBER,
3267 p_status IN OUT NOCOPY VARCHAR2 ) is
3268 v_login number;
3269 v_userid number;
3270 v_date date;
3271 begin
3272 --Variables used in insert ..
3273 v_login := fnd_global.login_id;
3274 v_date := sysdate;
3275 v_userid := fnd_global.user_id;
3276
3277 g_error_stage := 'TieBack_Xface: Net_zero_adls starts here';
3278 IF g_debug_context = 'Y' THEN
3279 gms_error_pkg.gms_debug (g_error_stage, 'C');
3280 END IF;
3281
3282 insert into gms_award_distributions(
3283 AWARD_SET_ID,
3284 ADL_LINE_NUM,
3285 DISTRIBUTION_VALUE,
3286 RAW_COST,
3287 DOCUMENT_TYPE,
3288 PROJECT_ID,
3289 TASK_ID,
3290 AWARD_ID,
3291 EXPENDITURE_ITEM_ID,
3292 CDL_LINE_NUM,
3293 IND_COMPILED_SET_ID,
3294 REQUEST_ID,
3295 LINE_NUM_REVERSED,
3296 RESOURCE_LIST_MEMBER_ID,
3297 ADL_STATUS,
3298 FC_STATUS,
3299 LINE_TYPE,
3300 CAPITALIZED_FLAG,
3301 REVERSED_FLAG,
3302 REVENUE_DISTRIBUTED_FLAG,
3303 BILLED_FLAG,
3304 BILL_HOLD_FLAG,
3305 BURDENABLE_RAW_COST,
3306 COST_DISTRIBUTED_FLAG,
3307 BUD_TASK_ID,
3308 BILLABLE_FLAG,
3309 LAST_UPDATE_DATE,
3310 LAST_UPDATED_BY,
3311 CREATED_BY,
3312 CREATION_DATE,
3313 LAST_UPDATE_LOGIN)
3314 select gms_adls_award_set_id_s.NEXTVAL,
3315 1,
3316 100,
3317 cdl.amount,
3318 'EXP',
3319 txn.project_id,
3320 txn.task_id,
3321 adl.award_id,
3322 cdl.expenditure_item_id,
3323 cdl.line_num,
3324 cdl.ind_compiled_set_id,
3325 cdl.request_id,
3326 cdl.line_num_reversed,
3327 adl.resource_list_member_id,
3328 'A', -- adl_status
3329 'A', -- fc_status
3330 'R', -- line_type
3331 'N', -- capitalized_flag
3332 NULL, -- reversed_flag
3333 'N', -- revenue_distributed_flag
3334 'N', -- billed_flag
3335 ei.bill_hold_flag,
3336 cdl.amount,
3337 'Y', -- cost_distributed_flag
3338 adl.bud_task_id,
3339 cdl.billable_flag,
3340 v_date,
3341 v_userid,
3342 v_userid,
3343 v_date,
3344 v_login
3345 from pa_transaction_interface_all txn,
3346 pa_expenditure_items_all ei,
3347 pa_cost_distribution_lines_all cdl,
3348 ap_invoice_distributions_all apd,
3349 gms_award_distributions adl
3350 where txn.interface_id = p_xface_id
3351 and nvl(txn.transaction_status_code, 'Z') <> 'R'
3352 and txn.batch_name = p_batch
3353 and txn.adjusted_expenditure_item_id = 0
3354 and txn.expenditure_item_id = cdl.expenditure_item_id
3355 and txn.expenditure_item_id = ei.expenditure_item_id
3356 and txn.cdl_system_reference5 = apd.invoice_distribution_id
3357 and apd.award_id = adl.award_set_id
3358 and adl.adl_line_num = 1
3359 and not exists ( select 1 from gms_award_distributions adl2
3360 where adl2.expenditure_item_id = ei.expenditure_item_id
3361 and adl2.document_type = 'EXP'
3362 and adl2.adl_status = 'A'
3363 and adl2.adl_line_num = 1 );
3364
3365 g_error_stage := 'TieBack_Xface: Net_zero_adls ends here';
3366 IF g_debug_context = 'Y' THEN
3367 gms_error_pkg.gms_debug (g_error_stage, 'C');
3368 END IF;
3369
3370 end Net_zero_adls ;
3371
3372
3373 -------------------------------------------------------------------------------
3374 -- Procedure marks the current packet's interface data as failed.
3375 -- Updates gms_bc_packets and pa_transaction_interface tables.
3376 -------------------------------------------------------------------------------
3377
3378 Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER,
3379 p_status OUT NOCOPY VARCHAR2) is
3380
3381 cursor c1 is -- changed for performance.
3382 select distinct txn_interface_id
3383 from gms_bc_packets
3384 where packet_id = p_packet_id
3385 and substr(result_code, 1, 1) = 'F';
3386
3387 v_txn_interface_id number;
3388 Begin
3389
3390 g_error_stage := 'Mark_Xface_Item_As_Failed: Start';
3391 IF g_debug_context = 'Y' THEN
3392 gms_error_pkg.gms_debug (g_error_stage, 'C');
3393 END IF;
3394
3395 p_status := 'S';
3396
3397 open c1;
3398 fetch c1 into v_txn_interface_id;
3399
3400 if c1%FOUND then
3401 p_status := 'F';
3402
3403 update pa_transaction_interface
3404 set transaction_rejection_code = 'GMS_FC_ERROR',
3405 transaction_status_code = 'R'
3406 where txn_interface_id = v_txn_interface_id;
3407
3408 end if;
3409
3410 close c1;
3411
3412 g_error_stage := 'Mark_Xface_Item_As_Failed: End';
3413 IF g_debug_context = 'Y' THEN
3414 gms_error_pkg.gms_debug (g_error_stage, 'C');
3415 END IF;
3416
3417 End Mark_Xface_Item_AS_Failed;
3418
3419 -------------------------------------------------------------------------------
3420 -- Function : grants_implemented
3421 -- Purpose : Function checks if grants is implemented for the OU.
3422 -- If so, return 'Y' else return 'N'.
3423 -------------------------------------------------------------------------------
3424 FUNCTION grants_implemented return VARCHAR2 IS
3425 BEGIN
3426
3427 if gms_pa_api.vert_install then
3428 return 'Y';
3429 else
3430 return 'N';
3431 end if;
3432
3433 END grants_implemented;
3434
3435 end gms_pa_costing_pkg;
[Home] [Help]