[Home] [Help]
PACKAGE BODY: APPS.GMS_AWARD_DIST_ENG
Source
1 package body GMS_AWARD_DIST_ENG as
2 -- $Header: gmsawdeb.pls 120.11.12020000.2 2012/10/16 13:54:40 navemish ship $
3
4 -- =================================================================================
5 -- BUG: 3358176 Award Distribution not recognized for sub tasks when funding
6 -- pattern is defined at top task level.
7 -- top_task_id and pa_tasks join was added.
8 -- =================================================================================
9 cursor get_funding_pattern (p_project_id in number,
10 p_task_id in number,
11 p_exp_item_dt in date,
12 p_org_id in number) is
13 select fp.funding_sequence,
14 fp.funding_pattern_id
15 from gms_funding_patterns_all fp,
16 pa_tasks t
17 where nvl(fp.retroactive_flag, 'N') = 'N'
18 and NVL(fp.status, 'N') = 'A'
19 -- and org_id = nvl(p_org_id, org_id )
20 and ((fp.org_id = p_org_id) or (fp.org_id is null and p_org_id is null)) -- bug 2362489
21 and fp.project_id = p_project_id
22 and t.task_id = p_task_id
23 and fp.task_id = t.top_task_id
24 and p_exp_item_dt between fp.start_date and nvl(fp.end_date, p_exp_item_dt)
25 union
26 select funding_sequence,
27 funding_pattern_id
28 from gms_funding_patterns_all gfpa
29 where nvl(retroactive_flag, 'N') = 'N'
30 and NVL(status, 'N') = 'A'
31 -- and org_id = NVL(p_org_id, org_id)
32 and ((org_id = p_org_id) or (org_id is null and p_org_id is null)) -- bug 2362489
33 and project_id = p_project_id
34 and task_id is null
35 and p_exp_item_dt between start_date and nvl(end_date, p_exp_item_dt)
36 and not exists (select '1'
37 from gms_funding_patterns_all b,
38 pa_tasks t1
39 where gfpa.project_id = b.project_id
40 and b.status = 'A'
41 and t1.task_id = p_task_id
42 and b.task_id = t1.top_task_id)
43 order by 1;
44
45 cursor apply_fp( x_doc_header_id NUMBER,
46 x_doc_type VARCHAR2 ) is
47 select expenditure_item_date,
48 document_header_id,
49 document_distribution_id,
50 expenditure_type,
51 expenditure_organization_id,
52 project_id,
53 task_id,
54 gl_date,
55 quantity,
56 amount,
57 rowid,
58 burdened_cost,
59 denom_burdened_cost,
60 denom_raw_cost,
61 acct_raw_cost,
62 acct_burdened_cost,
63 receipt_currency_amount
64 from gms_distributions
65 where NVL(dist_status,'X') <> 'FABA'
66 and document_header_id = x_doc_header_id
67 and document_type = x_doc_type;
68
69
70 TYPE proj_awd_rec IS RECORD ( document_header_id number,
71 document_distribution_id NUMBER,
72 project_id number,
73 task_id number,
74 award_id number,
75 dist_value number,
76 expenditure_type varchar2(30),
77 expenditure_item_date date,
78 expenditure_organization_id number,
79 funding_pattern_id number,
80 gl_date date,
81 quantity number,
82 award_amount number,
83 old_award_amount number, /*BUG 13697251 added new field */
84 row_id rowid,
85 burdened_cost NUMBER,
86 denom_burdened_cost NUMBER,
87 denom_raw_cost NUMBER,
88 acct_raw_cost NUMBER,
89 acct_burdened_cost NUMBER,
90 receipt_currency_amount NUMBER,
91 funds_check_ok boolean);
92
93 /* Start changes for Bug 10353561 */
94
95 TYPE proj_cum_rec IS RECORD ( funding_pattern_id number,
96 award_id number,
97 award_amount number,
98 pindex number);
99 /*End changes for Bug 10353561 */
100
101 TYPE fp_table is table of proj_awd_rec;
102 TYPE t_num_tab is table of NUMBER ;
103 TYPE t_date_tab is table of DATE ;
104 TYPE t_varchar_tab is table of varchar2(40) ;
105
106 /* Start changes for Bug 10353561 */
107 TYPE fcum_table is table of proj_cum_rec;
108 /* End changes for Bug 10353561 */
109
110 RESOURCE_BUSY EXCEPTION;
111 PRAGMA EXCEPTION_INIT( RESOURCE_BUSY, -0054 );
112
113
114
115 recs apply_fp%ROWTYPE;
116 valid_fp_tab fp_table ;
117
118 /* Start changes for Bug 10353561 */
119 cum_fp_tab fcum_table;
120 /* End changes for Bug 10353561 */
121
122 p_SOB_ID number ;
123 X_error varchar2(200);
124
125 FUNCTION FUNC_BUFF_RECORDS( p_header_id NUMBER,
126 p_line_id NUMBER,
127 p_document_type VARCHAR2,
128 p_dist_award_id NUMBER ) return NUMBER IS
129 l_count_rec NUMBER ;
130 BEGIN
131 IF p_document_type = 'ENC' THEN
132 DELETE from gms_distribution_details A
133 WHERE document_type = p_document_type
134 and exists ( select 'X' from gms_distributions B
135 where A.document_header_id = b.document_distribution_id
136 and B.document_header_id = p_header_id
137 and b.document_type = p_document_type ) ;
138 ELSE
139 DELETE from gms_distribution_details
140 WHERE document_header_id = p_header_id
141 AND document_type = p_document_type ;
142 END IF ;
143
144 DELETE from gms_distributions
145 WHERE document_header_id = p_header_id
146 AND document_type = p_document_type ;
147
148 IF p_document_type = 'REQ' THEN
149 INSERT INTO gms_distributions ( document_header_id,
150 document_distribution_id,
151 document_type,
152 gl_date,
153 project_id,
154 task_id,
155 expenditure_type,
156 expenditure_organization_id,
157 expenditure_item_date,
158 quantity,
159 unit_price,
160 amount,
161 dist_status,
162 creation_date
163 )
164 SELECT p_header_id,
165 dst.distribution_id,
166 p_document_type,
167 dst.gl_encumbered_date,
168 dst.project_id,
169 dst.task_id,
170 dst.expenditure_type,
171 dst.expenditure_organization_id,
172 dst.expenditure_item_date,
173 dst.req_line_quantity,
174 lne.unit_price,
175 -- 3362016 Grants integrations with CWK and PO services.
176 --dst.req_line_quantity * lne.unit_price,
177 decode( plt.matching_basis, 'AMOUNT', dst.req_line_amount,
178 dst.req_line_quantity * lne.unit_price) ,
179 NULL,
180 SYSDATE
181 FROM po_requisition_lines_all lne,
182 po_req_distributions_all dst,
183 po_line_types plt
184 -- bug 3576717
185 WHERE lne.requisition_header_id = p_header_id
186 AND lne.requisition_line_id = p_line_id
187 AND dst.requisition_line_id = lne.requisition_line_id
188 AND plt.line_type_id = lne.line_type_id
189 AND NVL(dst.award_id,0) = p_dist_award_id ;
190
191 l_count_rec := SQL%ROWCOUNT ;
192 ELSIF p_document_type = 'PO' THEN
193
194 INSERT INTO gms_distributions ( document_header_id,
195 document_distribution_id,
196 document_type,
197 gl_date,
198 project_id,
199 task_id,
200 expenditure_type,
201 expenditure_organization_id,
202 expenditure_item_date,
203 quantity,
204 unit_price,
205 amount,
206 dist_status,
207 creation_date
208 )
209 SELECT dst.po_header_id,
210 dst.po_distribution_id,
211 p_document_type,
212 dst.gl_encumbered_date,
213 dst.project_id,
214 dst.task_id,
215 dst.expenditure_type,
216 dst.expenditure_organization_id,
217 dst.expenditure_item_date,
218 dst.quantity_ordered,
219 lne.unit_price,
220 -- 3362016 Grants integrations with CWK and PO services.
221 -- dst.quantity_ordered * lne.unit_price,
222 decode( plt.matching_basis, 'AMOUNT', dst.amount_ordered,
223 dst.quantity_ordered * lne.unit_price),
224 NULL,
225 SYSDATE
226 FROM po_lines_all lne,
227 po_distributions_all dst,
228 po_line_types plt
229 -- bug 3576717
230 WHERE lne.po_header_id = p_header_id
231 AND lne.po_line_id = p_line_id
232 AND plt.line_type_id = lne.line_type_id
233 AND dst.po_line_id = lne.po_line_id
234 AND NVL(dst.award_id,0) = p_dist_award_id ;
235
236 l_count_rec := SQL%ROWCOUNT ;
237 ELSIF p_document_type = 'AP' THEN
238
239 INSERT INTO gms_distributions ( document_header_id,
240 document_distribution_id,
241 document_type,
242 gl_date,
243 project_id,
244 task_id,
245 expenditure_type,
246 expenditure_organization_id,
247 expenditure_item_date,
248 quantity,
249 unit_price,
250 amount,
251 dist_status,
252 creation_date
253 )
254 -- ==========================================
255 -- R12 AP Lines Uptake: Insert into gms_distributions
256 -- got changed from picking distribution_line_number
257 -- to invoice_distribution_id for document type AP.
258 -- ==========================================
259 SELECT dst.invoice_id,
260 dst.invoice_distribution_id,
261 p_document_type,
262 dst.accounting_date,
263 dst.project_id,
264 dst.task_id,
265 dst.expenditure_type,
266 dst.expenditure_organization_id,
267 dst.expenditure_item_date,
268 dst.pa_quantity,
269 1,
270 dst.amount,
271 NULL,
272 SYSDATE
273 FROM ap_invoice_distributions_all dst
274 WHERE dst.invoice_id = p_header_id
275 AND NVL(dst.award_id,0) = p_dist_award_id
276 union /* BUG 14216205 : Added the union for SAT */
277 SELECT APSAT.invoice_id,
278 APSAT.INVOICE_DISTRIBUTION_ID,
279 p_document_type,
280 APSAT.accounting_date,
281 APSAT.project_id,
282 APSAT.task_id,
283 APSAT.expenditure_type,
284 APSAT.expenditure_organization_id,
285 APSAT.expenditure_item_date,
286 APSAT.pa_quantity,
287 1,
288 APSAT.amount,
289 NULL,
290 SYSDATE
291 FROM AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
292 where APSAT.INVOICE_ID = p_header_id
293 and LINE_TYPE_LOOKUP_CODE='NONREC_TAX'
294 AND NVL(APSAT.award_id,0) = p_dist_award_id ;
295
296 L_COUNT_REC := SQL%ROWCOUNT ;
297 ELSIF p_document_type = 'ENC' THEN
298
299 INSERT INTO gms_distributions ( document_header_id,
300 document_distribution_id,
301 document_type,
302 gl_date,
303 project_id,
304 task_id,
305 expenditure_type,
306 expenditure_organization_id,
307 expenditure_item_date,
308 quantity,
309 unit_price,
310 amount,
311 dist_status,
312 creation_date
313 )
314 SELECT hdr.encumbrance_id,
315 dst.encumbrance_item_id,
316 p_document_type,
317 NVL(dst.gl_date,SYSDATE),
318 adl.project_id,
319 dst.task_id,
320 dst.encumbrance_type,
321 hdr.incurred_by_organization_id,
322 dst.encumbrance_item_date,
323 0,
324 0,
325 dst.amount,
326 NULL,
327 SYSDATE
328 FROM gms_encumbrances_all hdr,
329 gms_encumbrance_items_all dst,
330 gms_award_distributions adl
331 WHERE dst.encumbrance_id = p_header_id
332 AND hdr.encumbrance_id = p_header_id
333 AND hdr.encumbrance_id = dst.encumbrance_id
334 AND adl.expenditure_item_id = dst.encumbrance_item_id
335 AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
336 AND adl.line_num_reversed is null --Bug 5726575
337 and adl.adl_status = 'A'
338 AND NVL(adl.award_id,0) = p_dist_award_id ;
339 ----- Fix for bug : 2017155
340 L_COUNT_REC := SQL%ROWCOUNT ;
341
342 ---- ********************* Fix for bug number : 1939601 start ****************----------
343
344 ELSIF p_document_type = 'EXP' THEN
345
346 INSERT INTO gms_distributions ( document_header_id,
347 document_distribution_id,
348 document_type,
349 gl_date,
350 project_id,
351 task_id,
352 expenditure_type,
353 expenditure_organization_id,
354 expenditure_item_date,
355 quantity,
356 unit_price,
357 amount,
358 dist_status,
359 creation_date
360 )
361 SELECT hdr.expenditure_id,
362 dst.expenditure_item_id,
363 p_document_type,
364 -- ??????????? NVL(dst.gl_date,SYSDATE),
365 SYSDATE,
366 adl.project_id,
367 dst.task_id,
368 dst.expenditure_type,
369 hdr.incurred_by_organization_id,
370 dst.expenditure_item_date,
371 dst.quantity,
372 0,
373 0,
374 NULL,
375 SYSDATE
376 FROM pa_expenditures_all hdr,
377 pa_expenditure_items_all dst,
378 gms_award_distributions adl
379 WHERE dst.expenditure_id = p_header_id
380 AND hdr.expenditure_id = p_header_id
381 AND hdr.expenditure_id = dst.expenditure_id
382 AND adl.expenditure_item_id = dst.expenditure_item_id
383 and adl.adl_status = 'A'
384 AND NVL(adl.award_id,0) = p_dist_award_id ;
385
386 ---- ********************* Fix for bug number : 1939601 end ****************----------
387 L_COUNT_REC := SQL%ROWCOUNT ;
388 END IF ;
389
390 return L_COUNT_REC ;
391
392 END FUNC_BUFF_RECORDS ;
393 -- ======================================================
394 -- Function FUNC_add_to_table begins
395 -- =======================================================
396 FUNCTION FUNC_add_to_table( P_funding_pattern_id NUMBER )
397 return NUMBER is
398 l_tab_ndx NUMBER ;
399
400 cursor fetch_awards is
401 select award_id,
402 distribution_value
403 from gms_fp_distributions
404 where funding_pattern_id = p_funding_pattern_id
405 order by distribution_number ;
406
407 get_awards_rec fetch_awards%ROWTYPE;
408
409 x_dist_percent NUMBER ;
410 x_tot_amount NUMBER ;
411 X_diff_amount NUMBER ;
412 x_acc_amount NUMBER ;
413
414 X_diff_qty NUMBER ;
415 X_acc_qty NUMBER ;
416 X_tot_qty NUMBER ;
417 X_tot_bc NUMBER ;
418
419 X_tot_denom_bc NUMBER ;
420 x_tot_acct_bc NUMBER ;
421 x_tot_denom_rc NUMBER ;
422 x_tot_acct_rc NUMBER ;
423 x_tot_curr_amount NUMBER ;
424
425 X_diff_bc NUMBER ;
426 X_diff_denom_bc NUMBER ;
427 x_diff_acct_bc NUMBER ;
428 x_diff_denom_rc NUMBER ;
429 x_diff_acct_rc NUMBER ;
430 x_diff_curr_amount NUMBER ;
431
432 X_bc NUMBER ;
433 X_denom_bc NUMBER ;
434 x_acct_bc NUMBER ;
435 x_denom_rc NUMBER ;
436 x_acct_rc NUMBER ;
437 x_curr_amount NUMBER ;
438 /* Start changes for Bug 10353561 */
439 loc_index NUMBER ;
440 /* End changes for Bug 10353561 */
441
442 BEGIN
443
444 /* Start changes for Bug 10353561 */
445 loc_index :=0;
446 /* End changes for Bug 10353561 */
447 x_dist_percent := 0 ;
448 x_tot_amount := 0;
449 X_diff_amount := 0;
450 x_acc_amount := 0;
451 X_diff_qty := 0;
452 X_acc_qty := 0;
453 X_tot_qty := 0;
454 X_tot_bc := 0 ;
455 X_tot_denom_bc := 0 ;
456 x_tot_acct_bc := 0 ;
457 x_tot_denom_rc := 0 ;
458 x_tot_acct_rc := 0 ;
459 x_tot_curr_amount := 0 ;
460 X_diff_bc := 0 ;
461 X_diff_denom_bc := 0 ;
462 x_diff_acct_bc := 0 ;
463 x_diff_denom_rc := 0 ;
464 x_diff_acct_rc := 0 ;
465 x_diff_curr_amount := 0 ;
466 X_bc := 0 ;
467 X_denom_bc := 0 ;
468 x_acct_bc := 0 ;
469 x_denom_rc := 0 ;
470 x_acct_rc := 0 ;
471 x_curr_amount := 0 ;
472
473
474 -- Bug 1980810 : Added to set currency related global variables
475 -- Call to pa_currency.round_currency_amt function will use
476 -- global variables and thus improves performance
477
478 pa_currency.set_currency_info;
479
480
481 l_tab_ndx := 0 ;
482 valid_fp_tab.delete;
483
484 open fetch_awards ;
485 x_tot_qty := recs.quantity ;
486 x_tot_amount := recs.amount ;
487
488 x_tot_bc := NVL(recs.burdened_cost,0) ;
489 x_tot_denom_bc := NVL(recs.denom_burdened_cost,0) ;
490 x_tot_denom_rc := NVL(recs.denom_raw_cost,0) ;
491
492 x_tot_acct_rc := NVL(recs.acct_raw_cost,0) ;
493 x_tot_acct_bc := NVL(recs.acct_burdened_cost,0) ;
494
495 x_tot_curr_amount := NVL(recs.receipt_currency_amount,0) ;
496
497 loop
498 fetch fetch_awards into get_awards_rec;
499 if fetch_awards%NOTFOUND then
500 close fetch_awards;
501 exit;
502 end if;
503
504 l_tab_ndx := l_tab_ndx + 1;
505 x_dist_percent := get_awards_rec.distribution_value ;
506
507 valid_fp_tab.extend;
508 valid_fp_tab(l_tab_ndx).document_header_id := recs.document_header_id;
509 valid_fp_tab(l_tab_ndx).document_distribution_id := recs.document_distribution_id;
510 valid_fp_tab(l_tab_ndx).project_id := recs.project_id;
511 valid_fp_tab(l_tab_ndx).task_id := recs.task_id;
512 valid_fp_tab(l_tab_ndx).award_id := get_awards_rec.award_id;
513 valid_fp_tab(l_tab_ndx).dist_value := get_awards_rec.distribution_value;
514 valid_fp_tab(l_tab_ndx).expenditure_type := recs.expenditure_type;
515 valid_fp_tab(l_tab_ndx).expenditure_item_date := recs.expenditure_item_date;
516 --valid_fp_tab(l_tab_ndx).destination_type := recs.destination_type;
517 valid_fp_tab(l_tab_ndx).expenditure_organization_id := recs.expenditure_organization_id;
518 valid_fp_tab(l_tab_ndx).funding_pattern_id := P_funding_pattern_id;
519 valid_fp_tab(l_tab_ndx).gl_date := recs.gl_date;
520 --valid_fp_tab(l_tab_ndx).instance_id := recs.instance_id;
521 valid_fp_tab(l_tab_ndx).row_id := recs.rowid;
522
523 --
524 -- bug:4451781
525 -- PQE:R12:EXPAND FUNDING PATTERN DECIMAL ENTRY TO THREE
526 --
527 IF nvl(recs.quantity, 0) = 0 THEN --Bug 6754773 /* Added NVL for bug 6822240 */
528 valid_fp_tab(l_tab_ndx).quantity := ROUND( ( recs.quantity * x_dist_percent)/100, 3 ) ;
529
530 -- Bug 1980810 PA Rounding function added
531
532 valid_fp_tab(l_tab_ndx).award_amount := pa_currency.round_currency_amt( ( recs.amount * x_dist_percent)/100);
533 valid_fp_tab(l_tab_ndx).old_award_amount := pa_currency.round_currency_amt( ( recs.amount * x_dist_percent)/100); /*BUG 13697251 added new field */
534 valid_fp_tab(l_tab_ndx).burdened_cost := pa_currency.round_currency_amt( (recs.burdened_cost * x_dist_percent)/100);
535 valid_fp_tab(l_tab_ndx).denom_burdened_cost := pa_currency.round_currency_amt( (recs.denom_burdened_cost * x_dist_percent)/100 );
536 valid_fp_tab(l_tab_ndx).acct_burdened_cost := pa_currency.round_currency_amt( (recs.acct_burdened_cost * x_dist_percent)/100);
537 valid_fp_tab(l_tab_ndx).acct_raw_cost := pa_currency.round_currency_amt( (recs.acct_raw_cost * x_dist_percent)/100);
538 valid_fp_tab(l_tab_ndx).denom_raw_cost := pa_currency.round_currency_amt( (recs.denom_raw_cost * x_dist_percent)/100);
539 valid_fp_tab(l_tab_ndx).receipt_currency_amount := pa_currency.round_currency_amt( (recs.receipt_currency_amount * x_dist_percent)/100) ;
540 ELSE --Bug 6754773
541 valid_fp_tab(l_tab_ndx).quantity := recs.quantity * x_dist_percent/100;
542 /* Starts - Modified following columns derivation for bug#6822240 */
543 valid_fp_tab(l_tab_ndx).award_amount :=
544 pa_currency.round_currency_amt(recs.amount * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
545 valid_fp_tab(l_tab_ndx).old_award_amount :=
546 pa_currency.round_currency_amt(recs.amount * valid_fp_tab(l_tab_ndx).quantity/recs.quantity); /*BUG 13697251 added new field */
547 valid_fp_tab(l_tab_ndx).burdened_cost :=
548 pa_currency.round_currency_amt(recs.burdened_cost * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
549 valid_fp_tab(l_tab_ndx).denom_burdened_cost :=
550 pa_currency.round_currency_amt( recs.denom_burdened_cost * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
551 valid_fp_tab(l_tab_ndx).acct_burdened_cost :=
552 pa_currency.round_currency_amt( recs.acct_burdened_cost * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
553 valid_fp_tab(l_tab_ndx).acct_raw_cost :=
554 pa_currency.round_currency_amt( recs.acct_raw_cost * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
555 valid_fp_tab(l_tab_ndx).denom_raw_cost :=
556 pa_currency.round_currency_amt( recs.denom_raw_cost * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
557 valid_fp_tab(l_tab_ndx).receipt_currency_amount :=
558 pa_currency.round_currency_amt( recs.receipt_currency_amount * valid_fp_tab(l_tab_ndx).quantity/recs.quantity);
559 /* ENds - Modified following columns derivation for bug#6809323 */
560 END if;
561
562 x_acc_qty := x_acc_qty + valid_fp_tab(l_tab_ndx).quantity ;
563 X_acc_amount := X_acc_amount + valid_fp_tab(l_tab_ndx).award_amount ;
564
565 x_bc := NVL(x_bc,0) + valid_fp_tab(l_tab_ndx).burdened_cost ;
566 x_denom_bc := NVL(x_denom_bc,0) + valid_fp_tab(l_tab_ndx).denom_burdened_cost ;
567 x_denom_rc := NVL(x_denom_rc,0) + valid_fp_tab(l_tab_ndx).denom_raw_cost ;
568 x_acct_bc := NVL(x_acct_bc,0) + valid_fp_tab(l_tab_ndx).acct_burdened_cost ;
569 x_acct_rc := NVL(x_acct_rc,0) + valid_fp_tab(l_tab_ndx).acct_raw_cost ;
570 x_curr_amount := NVL(x_curr_amount,0) + valid_fp_tab(l_tab_ndx).receipt_currency_amount ;
571
572 END LOOP ;
573
574 IF l_tab_ndx > 0 THEN
575
576 x_diff_amount := X_tot_amount - X_acc_amount ;
577 x_diff_qty := X_tot_qty - X_acc_qty ;
578
579 x_diff_bc := X_tot_bc - X_bc ;
580 x_diff_denom_bc := X_tot_denom_bc - X_denom_bc ;
581 x_diff_acct_bc := X_tot_acct_bc - X_acct_bc ;
582 x_diff_denom_rc := X_tot_denom_rc - X_denom_rc ;
583 x_diff_acct_rc := X_tot_acct_rc - X_acct_rc ;
584 x_diff_curr_amount := x_tot_curr_amount - X_curr_amount ;
585
586 valid_fp_tab(l_tab_ndx).quantity := valid_fp_tab(l_tab_ndx).quantity + X_diff_qty ;
587 valid_fp_tab(l_tab_ndx).award_amount := valid_fp_tab(l_tab_ndx).award_amount + X_diff_amount ;
588 valid_fp_tab(l_tab_ndx).old_award_amount := valid_fp_tab(l_tab_ndx).old_award_amount + X_diff_amount ; /*BUG 13697251 added new field */
589 valid_fp_tab(l_tab_ndx).burdened_cost := valid_fp_tab(l_tab_ndx).burdened_cost + x_diff_bc ;
590 valid_fp_tab(l_tab_ndx).denom_burdened_cost := valid_fp_tab(l_tab_ndx).denom_burdened_cost + x_diff_denom_bc ;
591 valid_fp_tab(l_tab_ndx).acct_burdened_cost := valid_fp_tab(l_tab_ndx).acct_burdened_cost + x_diff_acct_bc ;
592 valid_fp_tab(l_tab_ndx).acct_raw_cost := valid_fp_tab(l_tab_ndx).acct_raw_cost + x_diff_acct_rc ;
593 valid_fp_tab(l_tab_ndx).denom_raw_cost := valid_fp_tab(l_tab_ndx).denom_raw_cost + x_diff_denom_rc ;
594 valid_fp_tab(l_tab_ndx).receipt_currency_amount := valid_fp_tab(l_tab_ndx).receipt_currency_amount + x_diff_curr_amount ;
595
596 END IF ;
597 return l_tab_ndx ;
598 EXCEPTION
599 when others then
600 IF fetch_awards%ISOPEN THEN
601 CLOSE fetch_awards ;
602 END IF ;
603
604 RAISE ;
605 END FUNC_add_to_table;
606 -- ======================================================
607 -- Function FUNC_add_to_table Ends
608 -- =======================================================
609
610
611 -- ======================================================
612 -- Function valid_transaction
613 -- =======================================================
614 FUNCTION valid_transaction( p_project_id IN NUMBER,
615 p_task_id IN NUMBER,
616 p_award_id IN NUMBER,
617 p_exp_type IN VARCHAR2,
618 P_EXP_ITEM_DATE IN DATE ) return boolean is
619
620 l_return_stat varchar2(2000) ;
621
622 begin
623 gms_transactions_pub.validate_transaction(recs.project_id,
624 recs.task_id,
625 P_award_id,
626 recs.expenditure_type,
627 recs.expenditure_item_date,
628 'GMSFABE',
629 l_return_stat);
630 --dbms_output.put_line(l_return_stat) ;
631 if (l_return_stat is null) then
632 return TRUE;
633 else
634 return FALSE;
635 end if;
636
637 END valid_transaction;
638 -- ======================================================
639 -- Function valid_transaction End.
640 -- =======================================================
641
642
643
644
645
646 -- ======================================================
647 -- Function FUNC_CHECK_FUNDS End.
648 -- =======================================================
649
650 FUNCTION FUNC_CHECK_FUNDS( p_ndx in number, p_document_type in varchar2) return BOOLEAN is
651
652 x_period_name varchar2(30);
653 x_period_year varchar2(4);
654 x_period_num number;
655 X_packet_id NUMBER ;
656 X_RETURN BOOLEAN ;
657 x_return_code varchar2(10);
658 x_e_code varchar2(100);
659 x_e_mesg varchar2(240);
660 l_budget_version_id NUMBER ;
661 x_doc_header_id NUMBER ;
662 x_doc_dist_id NUMBER ;
663
664 FUNCTION GET_RESULT_CODE RETURN BOOLEAN IS
665 x_result_code varchar2(1) ;
666 BEGIN
667 -- Debashis. Added exists and removed rownum.
668 select 1 into x_result_code from dual where exists (
669 select substr(NVL(result_code,'X'),1,1)
670 -- into x_result_code
671 from gms_bc_packets
672 where packet_id = X_packet_id
673 and substr(NVL(result_code,'X'),1,1) = 'F' );
674 -- and rownum < 2 ;
675
676 Return FALSE ;
677
678 EXCEPTION
679 when no_data_found then
680 return TRUE ;
681 when TOO_MANY_ROWS then
682 return FALSE ;
683 END GET_RESULT_CODE ;
684
685 begin
686
687 l_budget_version_id := 0 ;
688 X_RETURN := FALSE ;
689
690 SELECT GL_BC_PACKETS_S.nextval
691 INTO X_packet_id
692 FROM DUAL ;
693
694 select glst.period_name,
695 glst.period_year,
696 glst.period_num
697 into x_period_name,
698 x_period_year,
699 x_period_num
700 from gl_period_statuses glst
701 where glst.set_of_books_id = P_sob_id
702 and glst.application_id = 101
703 and glst.adjustment_period_flag = 'N'
704 and valid_fp_tab(1).expenditure_item_date between glst.start_date and glst.end_date;
705
706 for tab_index in 1..p_ndx loop
707
708 select bv.budget_version_id
709 into l_budget_version_id
710 from gms_budget_versions bv
711 where bv.project_id = valid_fp_tab(tab_index).project_id
712 and bv.award_id = valid_fp_tab(tab_index).award_id
713 and bv.budget_status_code = 'B'
714 and bv.current_flag = 'Y';
715
716 ------ ==============ERROR PROCESSING IF NO BUDGETS ==========
717 IF p_document_type = 'ENC' THEN
718 x_doc_header_id := valid_fp_tab(tab_index).document_distribution_id ;
719 x_doc_dist_id := 1 ;
720 ELSE
721 x_doc_header_id := valid_fp_tab(tab_index).document_header_id ;
722 x_doc_dist_id := valid_fp_tab(tab_index).document_distribution_id ;
723 END IF ;
724
725 /* Start changes for Bug 10353561 */
726
727 FOR i in cum_fp_tab.FIRST..cum_fp_tab.LAST LOOP
728
729 IF(valid_fp_tab(tab_index).award_id = cum_fp_tab(i).award_id and cum_fp_tab(i).award_id is not null) THEN
730
731 valid_fp_tab(tab_index).award_amount := valid_fp_tab(tab_index).award_amount + cum_fp_tab(i).award_amount;
732 END IF;
733
734 END LOOP;
735
736
737 /* End changes for Bug 10353561 */
738
739 insert into gms_bc_packets ( packet_id,
740 set_of_books_id,
741 je_source_name,
742 je_category_name,
743 actual_flag,
744 period_name,
745 period_year,
746 period_num,
747 project_id,
748 task_id,
749 award_id,
750 result_code,
751 funding_pattern_id,
752 funding_sequence,
753 fp_status,
754 status_code,
755 last_update_date,
756 last_updated_by,
757 created_by,
758 creation_date,
759 last_update_login,
760 entered_dr,
761 entered_cr,
762 expenditure_type,
763 expenditure_organization_id,
764 expenditure_item_date,
765 document_type,
766 document_header_id,
767 document_distribution_id,
768 transfered_flag,
769 budget_version_id,
770 account_type,
771 bc_packet_id)
772 values (x_packet_id,
773 P_sob_id,
774 'FAB Source Name',
775 'FAB Category Name',
776 'E',
777 x_period_name,
778 x_period_year,
779 x_period_num,
780 valid_fp_tab(tab_index).project_id,
781 valid_fp_tab(tab_index).task_id,
782 valid_fp_tab(tab_index).award_id,
783 NULL, --result code
784 null,
785 null,
786 null,
787 'P', --Bug Fix 2273188
788 sysdate,
789 FND_GLOBAL.USER_ID,
790 FND_GLOBAL.USER_ID,
791 sysdate,
792 FND_GLOBAL.LOGIN_ID,
793 valid_fp_tab(tab_index).award_amount,
794 0,
795 valid_fp_tab(tab_index).expenditure_type,
796 valid_fp_tab(tab_index).expenditure_organization_id,
797 trunc(valid_fp_tab(tab_index).expenditure_item_date),
798 'FAB',
799 x_doc_header_id,
800 x_doc_dist_id,
801 --tab_index,
802 'N',
803 l_budget_version_id, ---bv.budget_version_id,
804 'E',
805 gms_bc_packets_s.nextval
806 );
807
808
809 END loop;
810
811 -- ==========================================
812 -- Set the packet Arrival Order
813 -- ==========================================
814
815 -- REL12 : Deleted the insert into gl_bc_packet_arrival_order as this table
816 -- was replaced with gms_bc_packet_arrival_order and no more used in fundscheck code.
817 -- Insert into gms_bc_packet_arrival_order is handles by gms_fck.
818
819 -- ========================================
820 -- End of packet arrival order.
821 -- ========================================
822 X_return := GMS_FUNDS_CONTROL_PKG.GMS_FCK( P_sob_id,
823 X_packet_id,
824 'C', -- For Check Funds Mode..DEFAULT 'R'
825 'N', --x_override DEFAULT 'N'**ignore
826 'N', --x_partial DEFAULT 'N'
827 fnd_global.user_id, --x_user_id,
828 fnd_global.resp_id,
829 'Y', --x_execute***ignore
830 x_return_code, --F-failure,S-success
831 x_e_code,
832 x_e_mesg);
833
834
835 if x_return_code = 'F' then
836 X_return := FALSE ;
837 -- Fix for bug : 1782568
838 elsif x_return_code IS NULL then
839 X_return := FALSE ;
840 else
841 X_return := GET_RESULT_CODE ;
842 end if;
843
844 delete from gms_bc_packets
845 where packet_id = X_packet_id;
846
847 return X_return ;
848
849 EXCEPTION
850 when others then
851 -->>>>>>>> MESSAGE HERE..SYSTEM ERROR OCCURED <<<<<<<<<<<------
852 X_return := FALSE ;
853 RAISE ;
854 -- return X_return ;
855 END FUNC_CHECK_FUNDS ;
856 -- ======================================================
857 -- Function FUNC_CHECK_FUNDS End.
858 -- =======================================================
859
860
861
862 -- ======================================================
863 -- Function populate_dist_details
864 -- =======================================================
865
866 PROCEDURE populate_dist_details(cntr in number, p_document_type in varchar2) is
867 x_doc_header_id NUMBER ;
868 x_doc_dist_id NUMBER ;
869 begin
870
871 for Tab_index in 1..cntr loop
872
873 IF p_document_type = 'ENC' THEN
874 x_doc_header_id := valid_fp_tab(tab_index).document_distribution_id ;
875 x_doc_dist_id := 1 ;
876 ELSE
877 x_doc_header_id := valid_fp_tab(tab_index).document_header_id ;
878 x_doc_dist_id := valid_fp_tab(tab_index).document_distribution_id ;
879 END IF ;
880
881 insert into gms_distribution_details (
882 document_header_id,
883 document_distribution_id,
884 document_type,
885 funding_pattern_id,
886 distribution_number,
887 award_id,
888 project_id,
889 task_id,
890 expenditure_type,
891 expenditure_organization_id,
892 expenditure_item_date,
893 gl_date,
894 quantity_distributed,
895 amount_distributed,
896 fc_status,
897 line_status,
898 remarks,
899 burdened_cost,
900 denom_burdened_cost,
901 acct_burdened_cost,
902 denom_raw_cost,
903 acct_raw_cost,
904 receipt_currency_amount,
905 creation_date)
906 values
907 (
908 x_doc_header_id,
909 x_doc_dist_id,
910 p_document_type,
911 valid_fp_tab(tab_index).funding_pattern_id,
912 tab_index,
913 valid_fp_tab(tab_index).award_id,
914 valid_fp_tab(tab_index).project_id,
915 valid_fp_tab(tab_index).task_id,
916 valid_fp_tab(tab_index).expenditure_type,
917 valid_fp_tab(tab_index).expenditure_organization_id,
918 valid_fp_tab(tab_index).expenditure_item_date,
919 valid_fp_tab(tab_index).gl_date,
920 valid_fp_tab(tab_index).quantity,
921 valid_fp_tab(tab_index).old_award_amount, /*BUG 13697251 Changed the field to old_award_amount*/
922 'A',
923 'N',
924 NULL,
925 valid_fp_tab(tab_index).burdened_cost,
926 valid_fp_tab(tab_index).denom_burdened_cost,
927 valid_fp_tab(tab_index).acct_burdened_cost,
928 valid_fp_tab(tab_index).denom_raw_cost,
929 valid_fp_tab(tab_index).acct_raw_cost,
930 valid_fp_tab(tab_index).receipt_currency_amount,
931 sysdate
932 );
933 end loop;
934 END populate_dist_details;
935 -- ======================================================
936 -- Function populate_dist_details ends.
937 -- =======================================================
938
939
940 -- ======================================================
941 -- Function FUNC_FIND_PATTERN
942 -- =======================================================
943 FUNCTION FUNC_FIND_PATTERN( p_project_id IN NUMBER,
944 p_task_id IN NUMBER,
945 p_exp_type IN VARCHAR2,
946 p_exp_item_dt IN date,
947 p_org_id IN number,
948 p_funding_pattern_id IN NUMBER)
949 return NUMBER IS
950
951 l_pattern_id NUMBER ;
952
953 cursor fetch_awards1 (x_funding_pattern_id in number) is
954 select award_id,
955 distribution_value
956 from gms_fp_distributions
957 where funding_pattern_id = x_funding_pattern_id
958 order by distribution_number ;
959
960 get_awards_rec fetch_awards1%ROWTYPE;
961
962 l_return BOOLEAN;
963 l_valid_trans BOOLEAN;
964 BEGIN
965
966 l_return := TRUE;
967
968
969 X_error := 'Opened fetch awards1';
970 open fetch_awards1( p_funding_pattern_id );
971
972 loop
973 fetch fetch_awards1 into get_awards_rec;
974 if fetch_awards1%NOTFOUND then
975 close fetch_awards1;
976 exit;
977 end if;
978
979 l_valid_trans := valid_transaction( p_project_id,
980 p_task_id ,
981 get_awards_rec.award_id,
982 p_exp_type,
983 P_EXP_ITEM_DT ) ;
984 if (l_valid_trans) then
985 l_pattern_id := p_funding_pattern_id ;
986 else
987
988 -- ======================================================
989 -- l_pattern_id := -1 indicates POETA validation failed.
990 -- ======================================================
991 l_pattern_id := -1 ;
992
993 close fetch_awards1;
994 exit;
995 end if;
996 end loop; -- get_awards
997
998 return l_pattern_id ;
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 if fetch_awards1%ISOPEN then
1003 close fetch_awards1;
1004 END IF ;
1005
1006 RAISE ;
1007 END FUNC_FIND_PATTERN ;
1008 -- ======================================================
1009 -- Function FUNC_FIND_PATTERN Ends.
1010 -- =======================================================
1011
1012
1013 -- ======================================================
1014 -- PROCEDURE PROC_DISTRIBUTE_RECORDS *** Main process.
1015 -- =======================================================
1016 PROCEDURE PROC_DISTRIBUTE_RECORDS( p_doc_header_id in number,
1017 p_doc_type in varchar2,
1018 p_recs_processed out NOCOPY number,
1019 p_recs_rejected out NOCOPY number) is
1020
1021
1022
1023
1024 l_do_funds_check varchar2(10);
1025 l_org_id varchar2(10);
1026 found_fp BOOLEAN ;
1027 l_check_funds BOOLEAN ;
1028
1029 l_return_stat varchar2(10);
1030 l_tab_index number ; --- index for the table
1031 l_pattern_id number ;
1032 l_processed NUMBER ;
1033 l_rejected NUMBER ;
1034 /* Start changes for Bug 10353561 */
1035 l_count NUMBER ;
1036 g_count NUMBER ;
1037 p_count NUMBER;
1038 /* End changes for Bug 10353561 */
1039
1040 get_funding_pattern_rec get_funding_pattern%ROWTYPE;
1041 ---------------------------- Begin Main ---------------------------------------------------------
1042 BEGIN
1043 found_fp := FALSE;
1044 l_check_funds := FALSE ;
1045 l_tab_index := 0; --- index for the table
1046 l_pattern_id := 0;
1047 l_processed := 0 ;
1048 l_rejected := 0 ;
1049 valid_fp_tab := fp_table();
1050 cum_fp_tab := fcum_table(null,null,null,null);
1051 /* Start changes for Bug 10353561 */
1052 l_count :=1;
1053 g_count :=0;
1054 p_count :=0;
1055 /* End changes for Bug 10353561 */
1056
1057 l_org_id := PA_MOAC_UTILS.GET_CURRENT_ORG_ID;
1058
1059 -- FND_PROFILE.GET('ORG_ID', l_org_id);
1060
1061 FND_PROFILE.GET('GMS_DO_FUNDS_CHECK', l_do_funds_check); --- Define a profile name for funds check YES/NO
1062
1063 -- =======================================
1064 -- We don't do funds check for ACTUALS.
1065 -- =======================================
1066 IF p_doc_type = 'EXP' THEN
1067 l_do_funds_check := 'N' ;
1068 END IF ;
1069
1070 l_do_funds_check := NVL(l_do_funds_check,'N') ;
1071
1072
1073
1074 -- Get the Set of Books ID
1075
1076 select set_of_books_id
1077 into P_sob_id
1078 from pa_implementations;
1079
1080 -- ERR99 ( System Exception was captured for NO_data_found
1081 -- -------------------------------------------------------
1082
1083
1084 open apply_fp ( P_doc_header_id, p_doc_type );
1085 LOOP
1086
1087 fetch apply_fp into recs;
1088
1089 IF apply_fp%NOTFOUND then
1090 close apply_fp;
1091 exit;
1092 END IF ;
1093
1094 -- ================== Pattern Processing =====================
1095 open get_funding_pattern( recs.project_id,
1096 recs.task_id,
1097 recs.expenditure_item_date,
1098 l_org_id);
1099 l_pattern_id := 0 ;
1100 -- ***** Pattern LOOP ***************
1101 LOOP
1102
1103 fetch get_funding_pattern into get_funding_pattern_rec;
1104
1105 -- ==========================================================
1106 -- BUG : 3222459
1107 -- Transaction import process is erroring in pre_import step.
1108 -- l_tab_index didn't initialize before funding pattern.
1109 -- So if funding pattern was not found then l_tab_index had
1110 -- retained the previous value.
1111 -- l_tab_index initialization had fixed the issue.
1112 -- ==========================================================
1113 l_tab_index := 0 ;
1114 if get_funding_pattern%NOTFOUND then
1115 close get_funding_pattern;
1116 exit;
1117 end if;
1118
1119 -- BEGIN OF Pattern Stage 01 =========================================================
1120 l_pattern_id := FUNC_FIND_PATTERN ( recs.project_id,
1121 recs.task_id,
1122 recs.expenditure_type,
1123 recs.expenditure_item_date,
1124 l_org_id,
1125 get_funding_pattern_rec.funding_pattern_id) ;
1126
1127 l_tab_index := 0 ;
1128
1129 IF l_pattern_id > 0 THEN
1130 l_tab_index := FUNC_add_to_table( l_pattern_id ) ;
1131 END IF ;
1132
1133 -- END OF Pattern Stage 01 =========================================================
1134
1135 l_check_funds := TRUE ;
1136
1137 -- ============= Funds Check Processing ============
1138 if (upper(l_do_funds_check) = 'Y' and l_tab_index > 0 ) then
1139 l_check_funds := FUNC_CHECK_FUNDS(l_tab_index, p_doc_type);
1140 /* Start changes for Bug 10353561 */
1141 IF l_check_funds THEN
1142
1143 l_count :=1;
1144 /*Getting the Count of elements in temp table */
1145 FOR k in cum_fp_tab.FIRST..cum_fp_tab.LAST LOOP
1146
1147 IF(cum_fp_tab(k).award_id is not null ) THEN
1148 g_count := g_count + 1;
1149 l_count := l_count + 1;
1150 END IF;
1151
1152
1153 END LOOP;
1154
1155 FOR i in valid_fp_tab.FIRST..valid_fp_tab.LAST LOOP
1156
1157 --cum_fp_tab.extend;
1158
1159 /*Adding the values of an award into a temp table after successfully passing funds check for first distribution line
1160 If award is existing already in the table updating the latest amount after successful passing funds check
1161 for the subsequent distribution lines*/
1162 FOR m in cum_fp_tab.FIRST..cum_fp_tab.LAST LOOP
1163
1164 /* 13636959 IF (cum_fp_tab(m).award_id = valid_fp_tab(i).award_id or (cum_fp_tab(i).award_id is null and g_count = 0)) THEN */
1165 IF (cum_fp_tab(m).award_id = valid_fp_tab(i).award_id or (g_count = 0 and cum_fp_tab.exists(i) and cum_fp_tab(i).award_id is null )) THEN -- 13636959
1166 --{
1167 /*BUG 13697251 changed the index to i */
1168 cum_fp_tab(i).funding_pattern_id := valid_fp_tab(i).funding_pattern_id;
1169 cum_fp_tab(i).award_amount := valid_fp_tab(i).award_amount;
1170 cum_fp_tab(i).award_id := valid_fp_tab(i).award_id;
1171 cum_fp_tab(i).pindex := i;
1172 --}
1173 ELSE
1174 --{
1175 p_count :=0;
1176 FOR j in cum_fp_tab.FIRST..cum_fp_tab.LAST LOOP
1177 --{
1178 IF(cum_fp_tab(j).award_id = valid_fp_tab(i).award_id ) THEN
1179 p_count := p_count + 1;
1180 END IF;
1181 --}
1182 END LOOP;
1183
1184 /*Adding the values of an award into a temp table at the end after successfully passing funds check
1185 If award is not existing in the table for the subsequent distribution lines*/
1186
1187 IF(l_count > 1 and p_count = 0) THEN
1188 --{
1189 cum_fp_tab.extend;
1190 cum_fp_tab(l_count).funding_pattern_id := valid_fp_tab(i).funding_pattern_id;
1191 cum_fp_tab(l_count).award_amount := valid_fp_tab(i).award_amount;
1192 cum_fp_tab(l_count).award_id := valid_fp_tab(i).award_id;
1193 cum_fp_tab(l_count).pindex := l_count;
1194 --}
1195 END IF;
1196 --}
1197 END IF;
1198
1199 END LOOP;
1200
1201 END LOOP;
1202
1203 END IF;
1204
1205 /* End changes for Bug 10353561 */
1206 END IF ;
1207 -- END OF Funds Check Processing ====================
1208
1209 IF l_check_funds THEN
1210 EXIT ;
1211 END IF ;
1212
1213 END LOOP ;
1214 if get_funding_pattern%ISOPEN then
1215 close get_funding_pattern;
1216 END IF ;
1217 -- ***** End of Pattern LOOP ***************
1218
1219 -- ================== End of Pattern Processing =====================
1220
1221
1222
1223 -- ==== Create Distributions ========
1224 IF l_check_funds and l_tab_index > 0 then
1225 populate_dist_details(l_tab_index, p_doc_type);
1226
1227 update gms_distributions
1228 set dist_status = 'FABA'
1229 where rowid = recs.rowid ;
1230
1231 l_processed := l_processed + 1 ;
1232 ELSE
1233 -- ============== ERROR PROCESSING ============
1234 -- FUNDS CHECK FAILED
1235 -- ============================================
1236 -- ERR02 ( Pattern not found. )
1237 -- ERR01 ( POETA Failed. )
1238
1239 IF l_pattern_id <= 0 THEN
1240
1241 update gms_distributions
1242 set dist_status = DECODE(l_pattern_id, -1, 'ERR01', 'ERR02' )
1243 where rowid = recs.rowid
1244 and document_header_id = p_doc_header_id
1245 and document_type = p_doc_type ;
1246
1247 ELSIF NOT ( l_check_funds ) THEN
1248
1249 -- ERR03 ( Check funds failed. )
1250 update gms_distributions
1251 set dist_status = 'ERR03'
1252 where rowid = recs.rowid
1253 and document_header_id = p_doc_header_id
1254 and document_type = p_doc_type ;
1255 END IF ;
1256
1257 l_rejected := l_rejected + 1 ;
1258 NULL ;
1259 END IF ;
1260 -- === End of create Distributions ==============
1261 END LOOP ;
1262
1263 if apply_fp%ISOPEN then
1264 close apply_fp;
1265 end if;
1266
1267 p_recs_processed := l_processed ;
1268 p_recs_rejected := l_rejected ;
1269 delete from gms_distributions
1270 where creation_date <= ( TRUNC(sysdate) -1 ) ;
1271
1272 delete from gms_distribution_details
1273 where creation_date <= ( TRUNC(sysdate) -1 ) ;
1274 COMMIT ;
1275 EXCEPTION
1276 when others then
1277 --dbms_output.put_line('Error : ' || sqlerrm || X_error);
1278
1279 if get_funding_pattern%ISOPEN then
1280 close get_funding_pattern;
1281 END IF ;
1282
1283 if apply_fp%ISOPEN then
1284 close apply_fp;
1285 end if;
1286
1287 RAISE ;
1288
1289 end PROC_DISTRIBUTE_RECORDS ;
1290 -- ==========================================================
1291 -- PROCEDURE PROC_DISTRIBUTE_RECORDS *** Main process ends.
1292 -- ===========================================================
1293
1294 PROCEDURE PROC_INSERT_TRANS( P_transaction_source varchar2,
1295 p_batch varchar2,
1296 p_user_id NUMBER,
1297 p_xface_id NUMBER ) IS
1298 count_new_rec NUMBER ;
1299 BEGIN
1300 pa_cc_utils.set_curr_function('PROC_INSERT_TRANS');
1301
1302 UPDATE GMS_DISTRIBUTION_DETAILS
1303 SET remarks = to_char(PA_TXN_INTERFACE_S.nextval)
1304 WHERE document_header_id = p_xface_id
1305 AND document_type = 'EXP'
1306 AND distribution_number > 1 ;
1307
1308 pa_cc_utils.log_message(' Generated new txn_interface_id :'||to_char(SQL%ROWCOUNT));
1309 -- 3466152
1310 -- import process award distributions doesn't work when batch name is not supplied.
1311 -- addaed transaction source in the where clause to have better performance.
1312 --
1313 -- Bug 3221039 : Modified the following insert to populate award number and not
1314 -- to populate obsolete columns.
1315
1316 INSERT into GMS_TRANSACTION_INTERFACE_ALL
1317 (
1318 --TASK_NUMBER ,
1319 AWARD_ID ,
1320 AWARD_NUMBER ,
1321 --EXPENDITURE_TYPE ,
1322 --TRANSACTION_STATUS_CODE ,
1323 --ORIG_TRANSACTION_REFERENCE ,
1324 --ORG_ID ,
1325 --SYSTEM_LINKAGE ,
1326 --USER_TRANSACTION_SOURCE ,
1327 TRANSACTION_TYPE ,
1328 BURDENABLE_RAW_COST ,
1329 FUNDING_PATTERN_ID ,
1330 CREATED_BY ,
1331 CREATION_DATE ,
1332 LAST_UPDATED_BY ,
1333 LAST_UPDATE_DATE ,
1334 TXN_INTERFACE_ID
1335 --BATCH_NAME ,
1336 --TRANSACTION_SOURCE ,
1337 --EXPENDITURE_ENDING_DATE ,
1338 --EXPENDITURE_ITEM_DATE,
1339 --PROJECT_NUMBER
1340 )
1341 SELECT
1342 --TXN.TASK_NUMBER ,
1343 GTN.AWARD_ID ,
1344 GA.AWARD_NUMBER ,
1345 --TXN.EXPENDITURE_TYPE ,
1346 --TXN.TRANSACTION_STATUS_CODE ,
1347 --TXN.ORIG_TRANSACTION_REFERENCE ,
1348 --TXN.ORG_ID ,
1349 --TXN.SYSTEM_LINKAGE ,
1350 --TXN.USER_TRANSACTION_SOURCE ,
1351 NULL ,
1352 NULL ,
1353 GTN.funding_pattern_id ,
1354 TXN.CREATED_BY ,
1355 TXN.CREATION_DATE ,
1356 TXN.LAST_UPDATED_BY ,
1357 TXN.LAST_UPDATE_DATE ,
1358 TO_NUMBER(GTN.REMARKS)
1359 --TXN.BATCH_NAME ,
1360 --TXN.TRANSACTION_SOURCE ,
1361 --TXN.EXPENDITURE_ENDING_DATE ,
1362 --TXN.EXPENDITURE_ITEM_DATE ,
1363 --TXN.PROJECT_NUMBER
1364 FROM PA_TRANSACTION_INTERFACE_ALL TXN,
1365 GMS_DISTRIBUTION_DETAILS GTN,
1366 GMS_AWARDS_ALL GA -- Bug 3221039
1367 WHERE GTN.document_header_id = p_xface_id
1368 AND GA.award_id = GTN.award_id -- Bug 3221039
1369 AND TXN.transaction_source = P_transaction_source
1370 AND GTN.document_type = 'EXP'
1371 AND GTN.distribution_number > 1
1372 AND GTN.document_distribution_id = TXN.TXN_INTERFACE_ID ;
1373
1374 count_new_rec := SQL%ROWCOUNT ;
1375 pa_cc_utils.log_message(' GMS Transactions inserted :'||to_char(SQL%ROWCOUNT));
1376
1377 -- 3466152
1378 -- import process award distributions doesn't work when batch name is not supplied.
1379 -- addaed transaction source in the where clause to have better performance.
1380 --
1381 INSERT into PA_TRANSACTION_INTERFACE_ALL
1382 ( RECEIPT_CURRENCY_AMOUNT ,
1383 RECEIPT_CURRENCY_CODE ,
1384 RECEIPT_EXCHANGE_RATE ,
1385 DENOM_CURRENCY_CODE ,
1386 DENOM_RAW_COST ,
1387 DENOM_BURDENED_COST ,
1388 ACCT_RATE_DATE ,
1389 ACCT_RATE_TYPE ,
1390 ACCT_EXCHANGE_RATE ,
1391 ACCT_RAW_COST ,
1392 ACCT_BURDENED_COST ,
1393 ACCT_EXCHANGE_ROUNDING_LIMIT ,
1394 PROJECT_CURRENCY_CODE ,
1395 PROJECT_RATE_DATE ,
1396 PROJECT_RATE_TYPE ,
1397 PROJECT_EXCHANGE_RATE ,
1398 ORIG_EXP_TXN_REFERENCE1 ,
1399 ORIG_EXP_TXN_REFERENCE2 ,
1400 ORIG_EXP_TXN_REFERENCE3 ,
1401 ORIG_USER_EXP_TXN_REFERENCE ,
1402 VENDOR_NUMBER ,
1403 OVERRIDE_TO_ORGANIZATION_NAME ,
1404 REVERSED_ORIG_TXN_REFERENCE ,
1405 BILLABLE_FLAG ,
1406 PERSON_BUSINESS_GROUP_NAME ,
1407 TRANSACTION_SOURCE ,
1408 BATCH_NAME ,
1409 EXPENDITURE_ENDING_DATE ,
1410 EMPLOYEE_NUMBER ,
1411 ORGANIZATION_NAME ,
1412 EXPENDITURE_ITEM_DATE ,
1413 PROJECT_NUMBER ,
1414 TASK_NUMBER ,
1415 EXPENDITURE_TYPE ,
1416 NON_LABOR_RESOURCE ,
1417 NON_LABOR_RESOURCE_ORG_NAME ,
1418 QUANTITY ,
1419 RAW_COST ,
1420 EXPENDITURE_COMMENT ,
1421 TRANSACTION_STATUS_CODE ,
1422 TRANSACTION_REJECTION_CODE ,
1423 EXPENDITURE_ID ,
1424 ORIG_TRANSACTION_REFERENCE ,
1425 ATTRIBUTE_CATEGORY ,
1426 ATTRIBUTE1 ,
1427 ATTRIBUTE2 ,
1428 ATTRIBUTE3 ,
1429 ATTRIBUTE4 ,
1430 ATTRIBUTE5 ,
1431 ATTRIBUTE6 ,
1432 ATTRIBUTE7 ,
1433 ATTRIBUTE8 ,
1434 ATTRIBUTE9 ,
1435 ATTRIBUTE10 ,
1436 RAW_COST_RATE ,
1437 INTERFACE_ID ,
1438 UNMATCHED_NEGATIVE_TXN_FLAG ,
1439 EXPENDITURE_ITEM_ID ,
1440 ORG_ID ,
1441 DR_CODE_COMBINATION_ID ,
1442 CR_CODE_COMBINATION_ID ,
1443 CDL_SYSTEM_REFERENCE1 ,
1444 CDL_SYSTEM_REFERENCE2 ,
1445 CDL_SYSTEM_REFERENCE3 ,
1446 GL_DATE ,
1447 BURDENED_COST ,
1448 BURDENED_COST_RATE ,
1449 SYSTEM_LINKAGE ,
1450 TXN_INTERFACE_ID ,
1451 USER_TRANSACTION_SOURCE ,
1452 CREATED_BY ,
1453 CREATION_DATE ,
1454 LAST_UPDATED_BY ,
1455 LAST_UPDATE_DATE ,
1456 PROJFUNC_CURRENCY_CODE ,
1457 PROJFUNC_COST_RATE_TYPE ,
1458 PROJFUNC_COST_RATE_DATE ,
1459 PROJFUNC_COST_EXCHANGE_RATE ,
1460 PROJECT_RAW_COST ,
1461 PROJECT_BURDENED_COST ,
1462 ASSIGNMENT_NAME ,
1463 WORK_TYPE_NAME ,
1464 CDL_SYSTEM_REFERENCE4 ,
1465 ACCRUAL_FLAG ,
1466 PROJECT_ID ,
1467 TASK_ID ,
1468 PERSON_ID ,
1469 ORGANIZATION_ID ,
1470 NON_LABOR_RESOURCE_ORG_ID ,
1471 VENDOR_ID ,
1472 OVERRIDE_TO_ORGANIZATION_ID ,
1473 ASSIGNMENT_ID ,
1474 WORK_TYPE_ID ,
1475 PERSON_BUSINESS_GROUP_ID ,
1476 INVENTORY_ITEM_ID ,
1477 WIP_RESOURCE_ID ,
1478 UNIT_OF_MEASURE ,
1479 PO_NUMBER , /* CWK Changes */
1480 PO_HEADER_ID ,
1481 PO_LINE_NUM ,
1482 PO_LINE_ID ,
1483 PERSON_TYPE ,
1484 PO_PRICE_TYPE
1485 )
1486 SELECT
1487 GTN.RECEIPT_CURRENCY_AMOUNT ,
1488 TXN.RECEIPT_CURRENCY_CODE ,
1489 TXN.RECEIPT_EXCHANGE_RATE ,
1490 TXN.DENOM_CURRENCY_CODE ,
1491 GTN.DENOM_RAW_COST ,
1492 GTN.DENOM_BURDENED_COST ,
1493 TXN.ACCT_RATE_DATE ,
1494 TXN.ACCT_RATE_TYPE ,
1495 TXN.ACCT_EXCHANGE_RATE ,
1496 GTN.ACCT_RAW_COST ,
1497 GTN.ACCT_BURDENED_COST ,
1498 TXN.ACCT_EXCHANGE_ROUNDING_LIMIT ,
1499 TXN.PROJECT_CURRENCY_CODE ,
1500 TXN.PROJECT_RATE_DATE ,
1501 TXN.PROJECT_RATE_TYPE ,
1502 TXN.PROJECT_EXCHANGE_RATE ,
1503 TXN.ORIG_EXP_TXN_REFERENCE1 ,
1504 TXN.ORIG_EXP_TXN_REFERENCE2 ,
1505 TXN.ORIG_EXP_TXN_REFERENCE3 ,
1506 TXN.ORIG_USER_EXP_TXN_REFERENCE ,
1507 TXN.VENDOR_NUMBER ,
1508 TXN.OVERRIDE_TO_ORGANIZATION_NAME ,
1509 TXN.REVERSED_ORIG_TXN_REFERENCE ,
1510 TXN.BILLABLE_FLAG ,
1511 TXN.PERSON_BUSINESS_GROUP_NAME ,
1512 TXN.TRANSACTION_SOURCE ,
1513 TXN.BATCH_NAME ,
1514 TXN.EXPENDITURE_ENDING_DATE ,
1515 TXN.EMPLOYEE_NUMBER ,
1516 TXN.ORGANIZATION_NAME ,
1517 TXN.EXPENDITURE_ITEM_DATE ,
1518 TXN.PROJECT_NUMBER ,
1519 TXN.TASK_NUMBER ,
1520 TXN.EXPENDITURE_TYPE ,
1521 TXN.NON_LABOR_RESOURCE ,
1522 TXN.NON_LABOR_RESOURCE_ORG_NAME ,
1523 GTN.QUANTITY_DISTRIBUTED ,
1524 GTN.AMOUNT_DISTRIBUTED ,
1525 TXN.EXPENDITURE_COMMENT ,
1526 TXN.TRANSACTION_STATUS_CODE ,
1527 TXN.TRANSACTION_REJECTION_CODE ,
1528 TXN.EXPENDITURE_ID ,
1529 TXN.ORIG_TRANSACTION_REFERENCE ,
1530 TXN.ATTRIBUTE_CATEGORY ,
1531 TXN.ATTRIBUTE1 ,
1532 TXN.ATTRIBUTE2 ,
1533 TXN.ATTRIBUTE3 ,
1534 TXN.ATTRIBUTE4 ,
1535 TXN.ATTRIBUTE5 ,
1536 TXN.ATTRIBUTE6 ,
1537 TXN.ATTRIBUTE7 ,
1538 TXN.ATTRIBUTE8 ,
1539 TXN.ATTRIBUTE9 ,
1540 TXN.ATTRIBUTE10 ,
1541 TXN.RAW_COST_RATE ,
1542 TXN.INTERFACE_ID ,
1543 TXN.UNMATCHED_NEGATIVE_TXN_FLAG ,
1544 TXN.EXPENDITURE_ITEM_ID ,
1545 TXN.ORG_ID ,
1546 TXN.DR_CODE_COMBINATION_ID ,
1547 TXN.CR_CODE_COMBINATION_ID ,
1548 TXN.CDL_SYSTEM_REFERENCE1 ,
1549 TXN.CDL_SYSTEM_REFERENCE2 ,
1550 TXN.CDL_SYSTEM_REFERENCE3 ,
1551 TXN.GL_DATE ,
1552 GTN.BURDENED_COST ,
1553 TXN.BURDENED_COST_RATE ,
1554 TXN.SYSTEM_LINKAGE ,
1555 TO_NUMBER(GTN.REMARKS) ,
1556 TXN.USER_TRANSACTION_SOURCE ,
1557 TXN.CREATED_BY ,
1558 TXN.CREATION_DATE ,
1559 TXN.LAST_UPDATED_BY ,
1560 TXN.LAST_UPDATE_DATE ,
1561 TXN.PROJFUNC_CURRENCY_CODE ,
1562 TXN.PROJFUNC_COST_RATE_TYPE ,
1563 TXN.PROJFUNC_COST_RATE_DATE ,
1564 TXN.PROJFUNC_COST_EXCHANGE_RATE ,
1565 TXN.PROJECT_RAW_COST ,
1566 TXN.PROJECT_BURDENED_COST ,
1567 TXN.ASSIGNMENT_NAME ,
1568 TXN.WORK_TYPE_NAME ,
1569 TXN.CDL_SYSTEM_REFERENCE4 ,
1570 TXN.ACCRUAL_FLAG ,
1571 TXN.PROJECT_ID ,
1572 TXN.TASK_ID ,
1573 TXN.PERSON_ID ,
1574 TXN.ORGANIZATION_ID ,
1575 TXN.NON_LABOR_RESOURCE_ORG_ID ,
1576 TXN.VENDOR_ID ,
1577 TXN.OVERRIDE_TO_ORGANIZATION_ID ,
1578 TXN.ASSIGNMENT_ID ,
1579 TXN.WORK_TYPE_ID ,
1580 TXN.PERSON_BUSINESS_GROUP_ID ,
1581 TXN.INVENTORY_ITEM_ID ,
1582 TXN.WIP_RESOURCE_ID ,
1583 TXN.UNIT_OF_MEASURE ,
1584 TXN.PO_NUMBER ,
1585 TXN.PO_HEADER_ID ,
1586 TXN.PO_LINE_NUM ,
1587 TXN.PO_LINE_ID ,
1588 TXN.PERSON_TYPE ,
1589 TXN.PO_PRICE_TYPE
1590 FROM PA_TRANSACTION_INTERFACE_ALL TXN,
1591 GMS_DISTRIBUTION_DETAILS GTN
1592 WHERE GTN.document_header_id = p_xface_id
1593 AND GTN.document_type = 'EXP'
1594 AND TXN.transaction_source = P_transaction_source
1595 AND GTN.distribution_number > 1
1596 AND GTN.document_distribution_id = TXN.TXN_INTERFACE_ID ;
1597
1598 count_new_rec := SQL%ROWCOUNT ;
1599 pa_cc_utils.log_message(' PA Transactions inserted :'||to_char(SQL%ROWCOUNT));
1600 DELETE from GMS_DISTRIBUTION_DETAILS
1601 where document_header_id = p_xface_id ;
1602
1603 pa_cc_utils.log_message(' No of GMS_DISTRIBUTION_DETAILS records deleted :'||to_char(SQL%ROWCOUNT));
1604 DELETE from GMS_DISTRIBUTIONS
1605 where document_header_id = p_xface_id ;
1606 pa_cc_utils.log_message(' No of GMS_DISTRIBUTIONS records deleted :'||to_char(SQL%ROWCOUNT));
1607 pa_cc_utils.reset_curr_function;
1608 EXCEPTION
1609 When others then
1610 pa_cc_utils.log_message(' ERROR :'||SQLERRM);
1611 pa_cc_utils.reset_curr_function;
1612 RAISE ;
1613 END PROC_INSERT_TRANS ;
1614
1615 -- ============
1616 FUNCTION lockCntrlRec ( trx_source VARCHAR2
1617 , batch VARCHAR2
1618 , etypeclasscode VARCHAR2 ) RETURN NUMBER IS
1619 dummy NUMBER ;
1620 BEGIN
1621 pa_cc_utils.set_curr_function('Award Distribution lockCntrlRec');
1622
1623 pa_cc_utils.log_message('Trying to get lock for record in xface ctrl:'||
1624 ' transaction source ='||trx_source||
1625 ' batch = '||batch||
1626 ' sys link = '||etypeclasscode, 1);
1627 SELECT 1
1628 INTO dummy
1629 FROM pa_transaction_xface_control
1630 WHERE transaction_source = trx_source
1631 AND batch_name = batch
1632 AND system_linkage_function = etypeclasscode
1633 AND status = 'PENDING'
1634 FOR UPDATE OF status NOWAIT;
1635
1636 pa_cc_utils.log_message('Got lock for record',1);
1637 pa_cc_utils.log_message('Updated interface id/status on pa_transaction_xface_control',1);
1638 pa_cc_utils.reset_curr_function;
1639
1640 RETURN 0;
1641 EXCEPTION
1642 WHEN RESOURCE_BUSY THEN
1643 pa_cc_utils.log_message('Cannot get lock',1);
1644 pa_cc_utils.reset_curr_function;
1645 RETURN -1;
1646 END lockCntrlRec;
1647
1648
1649 -- ===============================================================
1650 -- GET_DIST_AWARD_ID returns default distribution award_id
1651 -- defined in gms implementation setup form.
1652 -- ===============================================================
1653 -- Bug 3221039 : Modified the below function to procedure inorder to
1654 -- fetch even the default award distribution number.
1655
1656 --FUNCTION GET_DIST_AWARD_ID(p_status out NOCOPY varchar2) return NUMBER is
1657 -- x_dummy NUMBER ;
1658
1659 PROCEDURE GET_DIST_AWARD_ID( p_default_dist_award_id out NOCOPY NUMBER,
1660 p_default_dist_award_number out NOCOPY varchar2,
1661 p_status out NOCOPY varchar2) IS
1662 BEGIN
1663 pa_cc_utils.set_curr_function('Award Distribution GET_DIST_AWARD_ID');
1664
1665 SELECT default_dist_award_id,
1666 default_dist_award_number -- Bug 3221039
1667 INTO p_default_dist_award_id,
1668 p_default_dist_award_number -- Bug 3221039
1669 FROM GMS_IMPLEMENTATIONS
1670 WHERE AWARD_DISTRIBUTION_OPTION = 'Y' ;
1671
1672 pa_cc_utils.log_message('Award Distribution default_dist_award_id='||to_char(p_default_dist_award_id));
1673 pa_cc_utils.reset_curr_function;
1674
1675 --return x_dummy ;
1676 EXCEPTION
1677 when no_data_found then
1678 p_status := 'NO_DATA_FOUND' ;
1679 pa_cc_utils.log_message('Award Distribution default_dist_award_id not enabled.****');
1680 pa_cc_utils.reset_curr_function;
1681 --return -1 ;
1682 END GET_DIST_AWARD_ID ;
1683 -- ==== End of GET_DIST_AWARD_ID ==================================
1684
1685 PROCEDURE PRE_IMPORT( P_transaction_source IN VARCHAR2,
1686 p_batch IN varchar2,
1687 p_user_id IN NUMBER,
1688 p_xface_id IN NUMBER ) IS
1689 FIRST_RECORD BOOLEAN ;
1690
1691 v_doc_header_id T_NUM_TAB ;
1692 v_doc_dist_id T_NUM_TAB ;
1693 V_project_id T_NUM_TAB ;
1694 V_task_id T_NUM_TAB ;
1695 V_exp_org_id T_NUM_TAB ;
1696 V_quantity T_NUM_TAB ;
1697 V_unit_price T_NUM_TAB ;
1698 V_amount T_NUM_TAB ;
1699 V_burdened_cost T_NUM_TAB ;
1700 v_denom_raw_cost T_NUM_TAB ;
1701 v_denom_burdened_cost T_NUM_TAB ;
1702 v_acct_raw_cost T_NUM_TAB ;
1703 v_acct_burdened_cost T_NUM_TAB ;
1704 v_receipt_currency_amount T_NUM_TAB ;
1705
1706 V_exp_type T_varchar_tab ;
1707 V_dist_status T_varchar_tab ;
1708 V_exp_item_date T_DATE_TAB ;
1709 V_creation_date T_DATE_TAB ;
1710 v_gl_date T_DATE_TAB ;
1711
1712 x_default_dist_award_id NUMBER ;
1713 count_rec NUMBER ;
1714 dummy NUMBER ;
1715 x_accepted NUMBER ;
1716 x_rejected NUMBER ;
1717 x_record_found NUMBER ;
1718
1719 x_org_id NUMBER ;
1720 x_override_to_org_id NUMBER ;
1721 x_incurred_by_org_id NUMBER ;
1722 x_dummy NUMBER ;
1723
1724
1725 x_status VARCHAR2(30) ;
1726 x_org_status VARCHAR2(30) ;
1727 X_billable_flag varchar2(1) ;
1728 l_default_dist_award_number VARCHAR2(15); -- Bug 3221039
1729 l_emp_org_oride varchar2(1) ;
1730 l_emporg_id NUMBER ;
1731 l_empJob_id NUMBER ;
1732 l_project_id pa_projects_all.project_id%TYPE ;
1733 l_project_id_last pa_projects_all.project_id%TYPE ;
1734 l_project_number pa_projects_all.segment1%TYPE ;
1735 l_project_number_last pa_projects_all.segment1%TYPE ;
1736 l_task_id pa_tasks.task_id%TYPE ;
1737 l_task_id_last pa_tasks.task_id%TYPE ;
1738 l_task_number pa_tasks.task_number%TYPE ;
1739 l_task_number_last pa_tasks.task_number%TYPE ;
1740 --l_sponsored_flag gms_project_types_all.sponsored_flag%TYPE ;
1741 l_sponsored_flag pa_project_types.sponsored_flag%TYPE ; --bug 4712763
1742
1743 cursor c_project_id is
1744 select project_id
1745 from pa_projects_all
1746 where segment1 = l_project_number ;
1747
1748 --
1749 -- bug : 3628820 perf issue in gmsawdeb.pls
1750 -- FTS on pa_projects and pa_tasks table.
1751 --
1752 cursor c_project_number is
1753 select segment1
1754 from pa_projects_all
1755 where project_id = l_project_id ;
1756 --
1757 -- bug : 3628820 perf issue in gmsawdeb.pls
1758 -- FTS on pa_projects and pa_tasks table.
1759 --
1760 cursor c_task_id is
1761 select task_id
1762 from pa_tasks
1763 where task_number = l_task_number
1764 and project_id = l_project_id ;
1765
1766 --
1767 -- bug : 3628820 perf issue in gmsawdeb.pls
1768 -- FTS on pa_projects and pa_tasks table.
1769 --
1770 cursor c_task_number is
1771 select task_number
1772 from pa_tasks
1773 where task_id = l_task_id ;
1774
1775 --
1776 -- bug : 3628820 perf issue in gmsawdeb.pls
1777 -- FTS on pa_projects and pa_tasks table.
1778 --
1779 cursor c_sponsored_flag is
1780 select sponsored_flag
1781 from pa_projects_all p,
1782 gms_project_types gpt
1783 where p.project_id = l_project_id
1784 and p.project_type = gpt.project_type ;
1785
1786 -- PA.L Changes
1787 CURSOR c_trans_source is
1788 SELECT allow_emp_org_override_flag
1789 from pa_transaction_sources
1790 where transaction_source = P_TRANSACTION_SOURCE ;
1791 -- PA.L Changes.
1792
1793 CURSOR TrxBatches IS
1794 SELECT xc.transaction_source
1795 , xc.batch_name
1796 , xc.system_linkage_function
1797 , xc.batch_name ||xc.system_linkage_function|| to_char(P_xface_id) exp_group_name
1798 FROM pa_transaction_xface_control xc
1799 WHERE xc.transaction_source = P_transaction_source
1800 AND xc.batch_name = nvl(P_batch, xc.batch_name)
1801 AND xc.status = 'PENDING';
1802
1803 CURSOR TrxRecs ( X_transaction_source VARCHAR2
1804 , current_batch VARCHAR2
1805 , curr_etype_class_code VARCHAR2 ) IS
1806 SELECT TXN.system_linkage
1807 , TXN.expenditure_ending_date expenditure_ending_date
1808 , TXN.employee_number
1809 , decode( TXN.employee_number, NULL, TXN.organization_name,
1810 decode(allow_emp_org_override_flag,'Y',TXN.organization_name,NULL)) organization_name
1811 , TXN.expenditure_item_date expenditure_item_date
1812 , TXN.project_number
1813 , TXN.project_id
1814 , TXN.task_id
1815 , TXN.task_number
1816 , TXN.expenditure_type
1817 , TXN.non_labor_resource
1818 , TXN.non_labor_resource_org_name
1819 , TXN.quantity
1820 , TXN.raw_cost
1821 , TXN.raw_cost_rate
1822 , TXN.orig_transaction_reference
1823 , TXN.attribute_category
1824 , TXN.attribute1
1825 , TXN.attribute2
1826 , TXN.attribute3
1827 , TXN.attribute4
1828 , TXN.attribute5
1829 , TXN.attribute6
1830 , TXN.attribute7
1831 , TXN.attribute8
1832 , TXN.attribute9
1833 , TXN.attribute10
1834 , TXN.expenditure_comment
1835 , TXN.interface_id
1836 , TXN.expenditure_id
1837 , TXN.unmatched_negative_txn_flag unmatched_negative_txn_flag
1838 , to_number( NULL ) expenditure_item_id
1839 , to_number( NULL ) job_id
1840 , TXN.org_id org_id
1841 , TXN.dr_code_combination_id
1842 , TXN.cr_code_combination_id
1843 , TXN.cdl_system_reference1
1844 , TXN.cdl_system_reference2
1845 , TXN.cdl_system_reference3
1846 , TXN.gl_date
1847 , TXN.burdened_cost
1848 , TXN.burdened_cost_rate
1849 , TXN.receipt_currency_amount
1850 , TXN.receipt_currency_code
1851 , TXN.receipt_exchange_rate
1852 , TXN.denom_currency_code
1853 , TXN.denom_raw_cost
1854 , TXN.denom_burdened_cost
1855 , TXN.acct_rate_date
1856 , TXN.acct_rate_type
1857 , TXN.acct_exchange_rate
1858 , TXN.acct_raw_cost
1859 , TXN.acct_burdened_cost
1860 , TXN.acct_exchange_rounding_limit
1861 , TXN.project_currency_code
1862 , TXN.project_rate_date
1863 , TXN.project_rate_type
1864 , TXN.project_exchange_rate
1865 , TXN.orig_exp_txn_reference1
1866 , TXN.orig_user_exp_txn_reference
1867 , TXN.vendor_number
1868 , TXN.orig_exp_txn_reference2
1869 , TXN.orig_exp_txn_reference3
1870 , TXN.override_to_organization_name
1871 , TXN.reversed_orig_txn_reference
1872 , TXN.billable_flag
1873 , TXN.txn_interface_id
1874 , TXN.person_business_group_name
1875 -- Bug 2464841 : Added parameters for 11.5 PA-J certification.
1876 , TXN.projfunc_currency_code
1877 , TXN.projfunc_cost_rate_type
1878 , TXN.projfunc_cost_rate_date
1879 , TXN.projfunc_cost_exchange_rate
1880 , TXN.project_raw_cost
1881 , TXN.project_burdened_cost
1882 , TXN.assignment_name
1883 , TXN.work_type_name
1884 , TXN.accrual_flag
1885 , TXN.person_id -- PA.L Changes
1886 , TXN.organization_id
1887 , TXN.non_labor_resource_org_id
1888 , TXN.vendor_id
1889 , TXN.override_to_organization_id
1890 , TXN.assignment_id
1891 , TXN.work_type_id
1892 , TXN.person_business_group_id -- PA.L Changes end.
1893 , TXN.po_number /* cwk */
1894 , TXN.po_header_id
1895 , TXN.po_line_num
1896 , TXN.po_line_id
1897 , TXN.person_type
1898 , TXN.po_price_type
1899 , TXN.wip_resource_id
1900 , TXN.inventory_item_id
1901 , TXN.unit_of_measure
1902 FROM pa_transaction_interface TXN,
1903 pa_transaction_sources TS,
1904 GMS_transaction_interface_all GMS1
1905 WHERE TXN.transaction_source = X_transaction_source
1906 and ts.transaction_source = TXN.transaction_source
1907 AND TXN.batch_name = current_batch
1908 AND TXN.transaction_status_code = 'P'
1909 and gms1.TXN_INTERFACE_ID = TXN.TXN_INTERFACE_ID
1910 and ( (gms1.award_number IS NULL AND NVL(gms1.award_id,0) = x_default_dist_award_id)
1911 OR
1912 (gms1.award_number = l_default_dist_award_number))
1913 -- Bug 3221039 : To fetch based on Award Number and Award Id
1914 AND decode(TXN.system_linkage,'OT','ST',txn.system_linkage) = curr_etype_class_code
1915 FOR UPDATE OF TXN.transaction_status_code;
1916
1917 TrxRec TrxRecs%ROWTYPE;
1918
1919 BEGIN
1920 v_doc_header_id := T_NUM_TAB();
1921 v_doc_dist_id := T_NUM_TAB();
1922 V_project_id := T_NUM_TAB();
1923 V_task_id := T_NUM_TAB();
1924 V_exp_org_id := T_NUM_TAB();
1925 V_quantity := T_NUM_TAB();
1926 V_unit_price := T_NUM_TAB();
1927 V_amount := T_NUM_TAB();
1928 V_burdened_cost := T_NUM_TAB();
1929 v_denom_raw_cost := T_NUM_TAB();
1930 v_denom_burdened_cost := T_NUM_TAB();
1931 v_acct_raw_cost := T_NUM_TAB();
1932 v_acct_burdened_cost := T_NUM_TAB();
1933 v_receipt_currency_amount := T_NUM_TAB() ;
1934 V_exp_type := T_varchar_tab() ;
1935 V_dist_status := T_varchar_tab() ;
1936 V_exp_item_date := T_DATE_TAB() ;
1937 V_creation_date := T_DATE_TAB() ;
1938 v_gl_date := T_DATE_TAB() ;
1939
1940
1941 x_record_found := 0 ;
1942 l_project_id_last := 0 ;
1943 l_task_id_last := 0 ;
1944
1945 pa_cc_utils.set_curr_function('PRE_IMPORT');
1946 pa_cc_utils.log_message('Start Grants Accounting Pre Import for award Distributions.'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
1947
1948 -- Bug 3221039 : Modified the call to procedure from function
1949 --x_default_dist_award_id := get_dist_award_id(x_status) ;
1950 get_dist_award_id(x_default_dist_award_id,l_default_dist_award_number,x_status);
1951
1952 --dbms_output.put_line('x_default_dist_award_id = '||to_char(x_default_dist_award_id)) ;
1953
1954 -- =====================================================
1955 -- award distribution is not enabled so we don't need
1956 -- to continue further.
1957 -- =====================================================
1958
1959 IF x_status is not NULL THEN
1960 pa_cc_utils.log_message('Award Distribution not enabled, pre-import Exit.');
1961 pa_cc_utils.reset_curr_function;
1962 return ;
1963 END IF ;
1964
1965 x_record_found := 0 ;
1966
1967 open c_trans_source ;
1968 fetch c_trans_source into l_emp_org_oride;
1969 close c_trans_source ;
1970
1971 -- ===================================
1972 -- Fetch batch
1973 -- ===================================
1974 FOR eachGroup IN TrxBatches LOOP
1975
1976 dummy := lockCntrlRec( eachGroup.transaction_source , eachGroup.batch_name
1977 , eachGroup.system_linkage_function );
1978 IF ( dummy <> 0 ) THEN
1979 pa_cc_utils.log_message(eachGroup.batch_name||' : All records rejected because of :'||'LOCK_'||eachGroup.batch_name||'_FAILED');
1980
1981 UPDATE pa_transaction_interface
1982 SET transaction_rejection_code = 'LOCK_'||eachGroup.batch_name||'_FAILED'
1983 , transaction_status_code = 'PR'
1984 WHERE transaction_source = eachGroup.transaction_source
1985 AND batch_name = eachGroup.batch_name
1986 AND transaction_status_code = 'P';
1987
1988 pa_cc_utils.log_message(eachGroup.batch_name||' : Rejected Count :'||to_char(SQL%ROWCOUNT));
1989
1990 END IF ;
1991 -- =======End of LOCK CHECK ********
1992
1993
1994 OPEN TrxRecs( eachGroup.transaction_source , eachGroup.batch_name , eachGroup.system_linkage_function );
1995
1996 pa_cc_utils.log_message('Fetch Records for :'||eachGroup.transaction_source||','||eachGroup.batch_name||','
1997 ||eachGroup.system_linkage_function ) ;
1998
1999 FIRST_RECORD := TRUE ;
2000
2001 -- ======================================
2002 -- Truncate the table of data.
2003 -- ======================================
2004 count_rec := 0 ;
2005
2006 -- =====================================
2007 -- Clear the tab buffer.
2008 -- =====================================
2009 v_doc_header_id.DELETE ;
2010 v_doc_dist_id.DELETE ;
2011 v_gl_date.DELETE ;
2012 V_project_id.DELETE ;
2013 V_task_id.DELETE ;
2014 V_exp_org_id.DELETE ;
2015 V_quantity.DELETE ;
2016 V_unit_price.DELETE ;
2017 V_amount.DELETE ;
2018 V_exp_type.DELETE ;
2019 V_dist_status.DELETE ;
2020 V_exp_item_date.DELETE ;
2021 V_creation_date.DELETE ;
2022
2023 V_burdened_cost.DELETE ;
2024 v_denom_raw_cost.DELETE ;
2025 v_denom_burdened_cost.DELETE ;
2026 v_acct_raw_cost.DELETE ;
2027 v_acct_burdened_cost.DELETE ;
2028 v_receipt_currency_amount.DELETE ;
2029 -- ===========End of BUF clearance **************
2030
2031 <<expenditures>>
2032 LOOP
2033 FETCH TrxRecs INTO TrxRec;
2034
2035 l_task_id := TrxRec.task_id ;
2036 l_project_id := TrxRec.project_id ;
2037 l_task_number := TrxRec.task_number ;
2038 l_project_number := TrxRec.Project_number ;
2039
2040 IF ( TrxRecs%ROWCOUNT = 0 ) THEN
2041 pa_cc_utils.log_message('Zero Records Fetched',1);
2042 EXIT expenditures ;
2043 ELSIF ( TrxRecs%NOTFOUND ) THEN
2044 pa_cc_utils.log_message('Last Record fetched',1);
2045 EXIT expenditures ;
2046 END IF;
2047 --
2048 -- bug : 3628820 perf issue in gmsawdeb.pls
2049 -- FTS on pa_projects and pa_tasks table.
2050 --
2051 IF l_project_number is NULL and l_project_id is NOT NULL THEN
2052
2053 IF l_project_id_last <> l_project_id THEN
2054 pa_cc_utils.log_message('GMS: Pre_import open c_project_number.');
2055 open c_project_number ;
2056 fetch c_project_number into l_project_number ;
2057 close c_project_number ;
2058
2059 l_project_id_last := l_project_id ;
2060 l_project_number_last := l_project_number ;
2061
2062 l_sponsored_flag := 'N' ;
2063 open c_sponsored_flag ;
2064 fetch c_sponsored_flag into l_sponsored_flag ;
2065 close c_sponsored_flag ;
2066
2067 l_project_id_last := l_project_id ;
2068
2069 ELSE
2070 l_project_number := l_project_number_last ;
2071 END IF ;
2072
2073 TrxRec.Project_number := l_project_number ;
2074 END IF ;
2075
2076 --
2077 -- bug : 3628820 perf issue in gmsawdeb.pls
2078 -- FTS on pa_projects and pa_tasks table.
2079 --
2080 IF l_project_number is not NULL and l_project_id is NULL THEN
2081 IF l_project_number_last <> l_project_number OR
2082 l_project_number_last is NULL THEN
2083
2084 pa_cc_utils.log_message('GMS: Pre_import open c_project_id.');
2085 open c_project_id ;
2086 fetch c_project_id into l_project_id ;
2087 close c_project_id ;
2088
2089 l_project_number_last := l_project_number ;
2090 l_project_id_last := l_project_id ;
2091 -- bug 5169675
2092 -- bug:5131439 TRANSACTION IMORT FAILED WITH PA_EXP_INV_PJTK FOR LEGITIMATE
2093 -- TRANSACTIONS
2094 -- last tasknumber and taskid not valid when project changed...
2095 --
2096 l_task_number_last := NULL ;
2097 l_task_id_last := NULL ;
2098 -- bug:5131439 end
2099
2100 l_sponsored_flag := 'N' ;
2101 open c_sponsored_flag ;
2102 fetch c_sponsored_flag into l_sponsored_flag ;
2103 close c_sponsored_flag ;
2104
2105 ELSE
2106 l_project_id := l_project_id_last ;
2107 END IF ;
2108
2109 TrxRec.Project_id := l_project_id ;
2110 END IF ;
2111
2112 --
2113 -- bug : 3628820 perf issue in gmsawdeb.pls
2114 -- FTS on pa_projects and pa_tasks table.
2115 --
2116 IF l_task_number is NULL and l_task_id is NOT NULL THEN
2117 IF l_task_id_last <> l_task_id OR
2118 l_task_id_last is NULL THEN
2119
2120 pa_cc_utils.log_message('GMS: Pre_import open c_task_number.');
2121 open c_task_number ;
2122 fetch c_task_number into l_task_number ;
2123 close c_task_number ;
2124
2125 l_task_number_last := l_task_number ;
2126 l_task_id_last := l_task_id ;
2127 ELSE
2128 l_task_number := l_task_number_last ;
2129 END IF ;
2130
2131 TrxRec.task_number := l_task_number ;
2132 END IF ;
2133
2134 --
2135 -- bug : 3628820 perf issue in gmsawdeb.pls
2136 -- FTS on pa_projects and pa_tasks table.
2137 --
2138 IF l_task_number is not NULL and l_task_id is NULL THEN
2139 IF l_task_number_last <> l_task_number OR
2140 l_task_number_last is NULL THEN
2141
2142 pa_cc_utils.log_message('GMS: Pre_import open c_task_id.');
2143 open c_task_id ;
2144 fetch c_task_id into l_task_id ;
2145 close c_task_id ;
2146 -- 5169675
2147 -- bug:5131439 TRANSACTION IMORT FAILED WITH PA_EXP_INV_PJTK FOR LEGITIMATE
2148 -- TRANSACTIONS
2149 -- Task is Invalid...
2150 IF l_task_id is not NULL THEN
2151 l_task_id_last := l_task_id ;
2152 l_task_number_last := l_task_number ;
2153 END IF ;
2154
2155 ELSE
2156 l_task_id := l_task_id_last ;
2157 END IF ;
2158
2159 TrxRec.task_id := l_task_id ;
2160 END IF ;
2161 -- 5169675
2162 -- bug:5131439 TRANSACTION IMORT FAILED WITH PA_EXP_INV_PJTK FOR LEGITIMATE
2163 -- TRANSACTIONS
2164 --
2165 x_status := NULL ;
2166
2167 IF l_task_id is NULL THEN
2168 X_status := 'INVALID_TASK' ;
2169 END IF ;
2170 -- bug:5131439 end...
2171 --
2172 --
2173 -- bug : 3628820 perf issue in gmsawdeb.pls
2174 -- FTS on pa_projects and pa_tasks table.
2175 --
2176
2177 -- Following sql will be executed if both project id and project number
2178 -- are populated in the transaction interface table.
2179 --
2180 IF l_project_id_last <> l_project_id THEN
2181 l_sponsored_flag := 'N' ;
2182 open c_sponsored_flag ;
2183 fetch c_sponsored_flag into l_sponsored_flag ;
2184 close c_sponsored_flag ;
2185
2186 l_project_id_last := l_project_id ;
2187 l_project_number_last := l_project_number ;
2188 END IF ;
2189
2190 -- 5169675
2191 -- bug:5131439 TRANSACTION IMORT FAILED WITH PA_EXP_INV_PJTK FOR LEGITIMATE
2192 --x_status := NULL ;
2193 --
2194
2195 pa_cc_utils.log_message('GMS: Pre_import l_project_id :'|| l_project_id);
2196 pa_cc_utils.log_message('GMS: Pre_import l_project_id_last :'|| l_project_id_last);
2197 pa_cc_utils.log_message('GMS: Pre_import l_project_number :'|| l_project_number);
2198 pa_cc_utils.log_message('GMS: Pre_import l_project_id_last :'|| l_project_number_last);
2199
2200 pa_cc_utils.log_message('GMS: Pre_import l_task_id :'|| l_task_id);
2201 pa_cc_utils.log_message('GMS: Pre_import l_task_id_last :'|| l_task_id_last);
2202 pa_cc_utils.log_message('GMS: Pre_import l_task_number :'|| l_task_number);
2203 pa_cc_utils.log_message('GMS: Pre_import l_task_number_last :'|| l_task_number_last);
2204 --
2205 -- bug : 3628820 perf issue in gmsawdeb.pls
2206 -- FTS on pa_projects and pa_tasks table.
2207 --
2208 IF NVL(l_sponsored_flag,'N') = 'N' THEN
2209 X_status := 'GMS_NOT_A_SPONSORED_PROJECT' ;
2210 x_org_status := x_status ;
2211 END IF ;
2212
2213 IF X_status is NULL THEN
2214 pa_debug.G_err_stage := 'CAlling ValidateOrgId';
2215
2216 pa_cc_utils.log_message(pa_debug.G_err_stage);
2217
2218 PA_TRX_IMPORT.ValidateOrgId(TrxRec.org_id,X_org_status );
2219 END IF ;
2220 IF ( X_org_status IS NOT NULL ) THEN
2221 -- Org id is null. Update status.
2222 X_status := X_org_status;
2223 ELSE
2224 -- org id is not null. continue with other validations
2225 pa_debug.G_err_stage := 'CAlling ValidateItem';
2226 pa_cc_utils.log_message(pa_debug.G_err_stage);
2227 l_emporg_id := NULL ;
2228 l_empjob_id := NULL ;
2229
2230 IF NVL(l_emp_org_oride, 'N') = 'N' AND
2231 TrxRec.person_id is NOT NULL THEN
2232
2233 pa_utils.GetEmpOrgJobID( trxRec.person_id,
2234 trxRec.expenditure_item_date,
2235 l_emporg_id ,
2236 l_empJob_id ) ;
2237 END IF ;
2238
2239 PA_TRX_IMPORT.ValidateItem( P_transaction_source
2240 , TrxRec.employee_number
2241 , TrxRec.organization_name
2242 , TrxRec.expenditure_ending_date
2243 , TrxRec.expenditure_item_date
2244 , TrxRec.expenditure_type
2245 , TrxRec.project_number
2246 , TrxRec.task_number
2247 , TrxRec.non_labor_resource
2248 , TrxRec.non_labor_resource_org_name
2249 , TrxRec.quantity
2250 , TrxRec.denom_raw_cost
2251 , 'PAXTRTRX' --v_calling_module
2252 , TrxRec.orig_transaction_reference
2253 , TrxRec.unmatched_negative_txn_flag
2254 , P_user_id
2255 , TrxRec.attribute_category
2256 , TrxRec.attribute1
2257 , TrxRec.attribute2
2258 , TrxRec.attribute3
2259 , TrxRec.attribute4
2260 , TrxRec.attribute5
2261 , TrxRec.attribute6
2262 , TrxRec.attribute7
2263 , TrxRec.attribute8
2264 , TrxRec.attribute9
2265 , TrxRec.attribute10
2266 , TrxRec.dr_code_combination_id
2267 , TrxRec.cr_code_combination_id
2268 , TrxRec.gl_date
2269 , TrxRec.denom_burdened_cost
2270 , TrxRec.system_linkage
2271 , X_status
2272 , X_billable_flag
2273 , TrxRec.receipt_currency_amount
2274 , TrxRec.receipt_currency_code
2275 , TrxRec.receipt_exchange_rate
2276 , TrxRec.denom_currency_code
2277 , TrxRec.acct_rate_date
2278 , TrxRec.acct_rate_type
2279 , TrxRec.acct_exchange_rate
2280 , TrxRec.acct_raw_cost
2281 , TrxRec.acct_burdened_cost
2282 , TrxRec.acct_exchange_rounding_limit
2283 , TrxRec.project_currency_code
2284 , TrxRec.project_rate_date
2285 , TrxRec.project_rate_type
2286 , TrxRec.project_exchange_rate
2287 , TrxRec.raw_cost
2288 , TrxRec.burdened_cost
2289 , TrxRec.override_to_organization_name
2290 , TrxRec.vendor_number
2291 , TrxRec.org_id
2292 , TrxRec.person_business_group_name
2293 -- Bug 2464841 : Added parameters for 11.5 PA-J certification.
2294 , TrxRec.projfunc_currency_code
2295 , TrxRec.projfunc_cost_rate_type
2296 , TrxRec.projfunc_cost_rate_date
2297 , TrxRec.projfunc_cost_exchange_rate
2298 , TrxRec.project_raw_cost
2299 , TrxRec.project_burdened_cost
2300 , TrxRec.assignment_name
2301 , TrxRec.work_type_name
2302 , TrxRec.accrual_flag
2303 , TrxRec.project_id
2304 , TrxRec.Task_id
2305 , TrxRec.person_id
2306 , TrxRec.Organization_id
2307 , TrxRec.non_labor_resource_org_id
2308 , TrxRec.vendor_id
2309 , TrxRec.override_to_organization_id
2310 , TrxRec.person_business_group_id
2311 , TrxRec.assignment_id
2312 , TrxRec.work_type_id
2313 , l_emporg_id
2314 , l_empjob_id
2315 , TrxRec.txn_interface_id
2316 , TrxRec.po_number /* CWK */
2317 , TrxRec.po_header_id
2318 , TrxRec.po_line_num
2319 , TrxRec.po_line_id
2320 , TrxRec.person_type
2321 , TrxRec.po_price_type
2322 );
2323
2324 pa_cc_utils.reset_curr_function;
2325
2326 END IF ;
2327
2328 IF ( X_status IS NOT NULL ) THEN
2329
2330 pa_debug.G_err_stage := 'Updating txn interface table for txn'||
2331 ' rejected by validateitem';
2332 pa_cc_utils.log_message(pa_debug.G_err_stage);
2333
2334 UPDATE pa_transaction_interface
2335 SET transaction_rejection_code = X_status ,
2336 interface_id = P_xface_id ,
2337 transaction_status_code = 'PR'
2338 WHERE CURRENT OF TrxRecs;
2339
2340 pa_cc_utils.reset_curr_function;
2341 ELSE
2342 -- =================================================
2343 -- Identify the record for distribution.
2344 -- ================================================
2345 count_rec := count_rec + 1 ;
2346 v_doc_dist_id.extend ;
2347 v_gl_date.extend ;
2348 V_project_id.extend ;
2349 V_task_id.extend ;
2350 V_exp_org_id.extend ;
2351 V_quantity.extend ;
2352 V_amount.extend ;
2353 V_exp_type.extend ;
2354 V_dist_status.extend ;
2355 V_exp_item_date.extend ;
2356 v_receipt_currency_amount.extend ;
2357
2358 V_burdened_cost.EXTEND ;
2359 v_denom_raw_cost.EXTEND ;
2360 v_denom_burdened_cost.EXTEND ;
2361 v_acct_raw_cost.EXTEND ;
2362 v_acct_burdened_cost.EXTEND ;
2363
2364 x_org_id := pa_utils.getorgid(TrxRec.organization_name) ;
2365 x_override_to_org_id := pa_utils.getorgid(TrxRec.override_to_organization_name) ;
2366 x_incurred_by_org_id := NVL(x_override_to_org_id, x_org_id ) ;
2367 v_doc_dist_id(count_rec) := TrxRec.txn_interface_id ;
2368 v_gl_date(count_rec) := TrxRec.gl_date ;
2369 V_project_id(count_rec) := TrxRec.project_id ;
2370 V_task_id(count_rec) := TrxRec.task_id ;
2371 x_dummy := NVL(x_override_to_org_id, x_org_id ) ;
2372
2373 -- ========================================================================
2374 -- BUG: 1963556 ( ORA-1400 WHEN RUNNING PAXTRTRX FOR AWARD RELATED LABOR
2375 -- TRANSACTIONS ).
2376 -- Expenditure_organization_id is not null column in gms_distributions.
2377 -- This is required only for funds Check. For expenditures we don't have
2378 -- Funds check and afford to have it ZERO.
2379 -- ========================================================================
2380 V_exp_org_id(count_rec) := NVL(x_dummy,0) ;
2381 V_quantity(count_rec) := TrxRec.quantity ;
2382 -- = =================================================================
2383 -- = BUG: 3228565
2384 -- = Transaction import process is erroring out in pre import step.
2385 -- = gms_distributions.amount column is not null. Null value in
2386 -- = TrxRec.raw_cost is raising a ORA exception when inserting into
2387 -- = gms_distributions table.
2388 -- = Error is fixed by using NVL(TrxRec.raw_cost,0)
2389 -- = =================================================================
2390 V_amount(count_rec) := NVL(TrxRec.raw_cost ,0);
2391 V_exp_type(count_rec) := TrxRec.Expenditure_type ;
2392 V_dist_status(count_rec) := NULL ;
2393 V_exp_item_date(count_rec) := TrxRec.Expenditure_item_date ;
2394 V_burdened_cost(count_rec) := TrxRec.burdened_cost;
2395 v_denom_raw_cost(count_rec) := TrxRec.denom_raw_cost;
2396 v_denom_burdened_cost(count_rec) := TrxRec.denom_burdened_cost;
2397 v_acct_raw_cost(count_rec) := TrxRec.acct_raw_cost ;
2398 v_receipt_currency_amount(count_rec) := TrxRec.receipt_currency_amount ;
2399
2400 v_acct_burdened_cost(count_rec) := TrxRec.acct_burdened_cost;
2401 x_record_found := x_record_found + 1 ;
2402
2403 END IF ;
2404 END LOOP expenditures;
2405
2406 IF TrxRecs%ISOPEN THEN
2407 CLOSE TrxRecs ;
2408 END IF ;
2409
2410 -- ==================================================
2411 -- Insert Records into Distribution Table.
2412 -- PLSQL Bulk operation
2413 -- =================================================
2414 FORALL indx in 1..count_rec
2415 INSERT INTO gms_distributions ( document_header_id,
2416 document_distribution_id,
2417 document_type,
2418 gl_date,
2419 project_id,
2420 task_id,
2421 expenditure_type,
2422 expenditure_organization_id,
2423 expenditure_item_date,
2424 quantity,
2425 unit_price,
2426 amount,
2427 burdened_cost,
2428 denom_raw_cost,
2429 denom_burdened_cost,
2430 acct_raw_cost,
2431 receipt_currency_amount,
2432 acct_burdened_cost,
2433 dist_status,
2434 creation_date
2435 )
2436 VALUES ( P_xface_id,
2437 v_doc_dist_id(indx),
2438 'EXP',
2439 nvl(v_gl_date(indx),SYSDATE),
2440 v_project_id(indx),
2441 v_task_id(indx),
2442 v_exp_type(indx),
2443 v_exp_org_id(indx),
2444 v_exp_item_date(indx),
2445 v_quantity(indx),
2446 1,
2447 v_amount(indx),
2448 v_burdened_cost(indx),
2449 v_denom_raw_cost(indx),
2450 v_denom_burdened_cost(indx),
2451 v_acct_raw_cost(indx),
2452 v_receipt_currency_amount(indx),
2453 v_acct_burdened_cost(indx),
2454 v_dist_status(indx),
2455 SYSDATE
2456 ) ;
2457 END LOOP ;
2458
2459 pa_cc_utils.log_message('Insert record into gms_distributions :'||to_char(count_rec));
2460 -- =====================================
2461 -- There is nothing to distribute.
2462 -- =====================================
2463 IF x_record_found = 0 THEN
2464 pa_cc_utils.reset_curr_function;
2465 pa_cc_utils.log_message('Nothing found for distributions -PRE_IMPORT EXIT');
2466 return ;
2467 END IF ;
2468 -- =================================================
2469 -- Distribute records using FAB engine.
2470 -- =================================================
2471 pa_cc_utils.log_message('Start gms_award_dist_eng.proc_distribute_records '||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2472
2473 gms_award_dist_eng.proc_distribute_records(p_xface_id,
2474 'EXP',
2475 x_accepted,
2476 x_rejected ) ;
2477 pa_cc_utils.log_message('End gms_award_dist_eng.proc_distribute_records '||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2478 -- ==================================================
2479 -- Update 1st distribution into IMPORT tables.
2480 -- ==================================================
2481 -- 3466152
2482 -- import process award distributions doesn't work when batch name is not supplied.
2483 -- removed batch name criteria . Its not needed since txn_interface_id is available.
2484 --
2485 -- Bug 3221039 : Modified the below code to distribute based on default Award id/
2486 -- Award number and to populate both award id and award number .
2487
2488 update gms_transaction_interface_all A
2489 set (a.award_id,a.award_number) = ( select B.award_id,GA.award_number -- Bug 3221039
2490 from gms_distribution_details B,
2491 gms_awards_all GA
2492 where a.txn_interface_id = b.document_distribution_id
2493 and B.document_header_id = P_xface_id
2494 and B.distribution_number= 1
2495 and B.document_type = 'EXP'
2496 and GA.award_id = B.award_id)
2497 where --A.transaction_source = P_transaction_source -- Bug 3221039 : obsolete column
2498 --and A.batch_name = p_batch
2499 ( (award_number IS NULL AND nvl(award_id,0)= x_default_dist_award_id )
2500 OR
2501 (award_number = l_default_dist_award_number)) -- Bug 3221039
2502 and A.txn_interface_id in ( select C.document_distribution_id
2503 from gms_distribution_details C
2504 where C.document_header_id = P_xface_id
2505 and C.distribution_number= 1
2506 and C.document_type = 'EXP' );
2507
2508 pa_cc_utils.log_message('Update award_id in gms_transaction_interface_all count :'||to_char(SQL%ROWCOUNT));
2509
2510 -- 3466152
2511 -- import process award distributions doesn't work when batch name is not supplied.
2512 -- removed batch name criteria . Its not needed since txn_interface_id is available.
2513 --
2514 update PA_transaction_interface_all A
2515 set ( quantity, raw_cost, burdened_cost, denom_raw_cost, denom_burdened_cost, acct_raw_cost, acct_burdened_cost, receipt_currency_amount ) =
2516 ( select B.quantity_distributed,
2517 B.amount_distributed,
2518 B.burdened_cost,
2519 B.denom_raw_cost,
2520 B.denom_burdened_cost,
2521 B.acct_raw_cost,
2522 B.acct_burdened_cost,
2523 B.receipt_currency_amount
2524 from gms_distribution_details B
2525 where a.txn_interface_id = b.document_distribution_id
2526 and B.document_header_id = P_xface_id
2527 and B.distribution_number= 1
2528 and B.document_type = 'EXP' )
2529 where A.transaction_source = P_transaction_source
2530 --and A.batch_name = p_batch
2531 and A.txn_interface_id IN ( SELECT C.document_distribution_id
2532 from gms_distribution_details C
2533 WHERE C.document_header_id = P_xface_id
2534 and C.distribution_number= 1
2535 and C.document_type = 'EXP' );
2536
2537 pa_cc_utils.log_message('Update ( quantity, raw_cost ) in pa_transaction_interface_all count :'||
2538 to_char(SQL%ROWCOUNT));
2539 -- 3466152
2540 -- import process award distributions doesn't work when batch name is not supplied.
2541 -- removed batch name criteria . Its not needed since txn_interface_id is available.
2542 --
2543
2544 -- 3466152
2545 -- import process award distributions doesn't work when batch name is not supplied.
2546 -- removed batch name criteria . Its not needed since txn_interface_id is available.
2547 --
2548 UPDATE pa_transaction_interface A
2549 SET transaction_rejection_code = 'AWARD_DISTRIBUTION_FAILED' ,
2550 interface_id = P_xface_id ,
2551 transaction_status_code = 'PR'
2552 WHERE A.transaction_source = P_transaction_source
2553 --AND A.batch_name = p_batch
2554 AND A.TXN_INTERFACE_ID IN ( SELECT B.document_distribution_id
2555 FROM GMS_DISTRIBUTIONS B
2556 WHERE B.document_header_id = P_Xface_id
2557 and B.document_type = 'EXP'
2558 and NVL(B.dist_status,'X') <> 'FABA' ) ;
2559
2560 pa_cc_utils.log_message('Update distribution recject in pa_transaction_interface_all count :'||to_char(SQL%ROWCOUNT));
2561 -- ===================================================================
2562 -- Insert distributed records into PA_transaction_interface_all and
2563 -- gms_transaction_interface_all. Update the count in
2564 -- pa_transaction_xface_ctrl_all
2565 -- ==================================================================
2566 PROC_INSERT_TRANS( P_transaction_source,
2567 p_batch ,
2568 p_user_id ,
2569 p_xface_id ) ;
2570 pa_cc_utils.log_message('END Grants Accounting Pre Import for award Distributions.'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2571 pa_cc_utils.reset_curr_function;
2572 EXCEPTION
2573 When OTHERS THEN
2574
2575 IF TrxRecs%Isopen THEN
2576 Close TrxRecs ;
2577 END IF ;
2578
2579 pa_cc_utils.log_message('ERROR Grants Accounting Pre Import for award Distributions.'||to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
2580 pa_cc_utils.log_message('PLSQL ERROR Occured :'||SQLERRM);
2581 pa_cc_utils.reset_curr_function;
2582 ROLLBACK ;
2583 raise_application_error( -20000, SQLERRM ) ;
2584 RAISE ;
2585 END PRE_IMPORT ;
2586
2587
2588 END GMS_AWARD_DIST_ENG ;