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