[Home] [Help]
PACKAGE BODY: APPS.GMS_AWARDS_DIST_PKG
Source
1 package BODY GMS_AWARDS_DIST_PKG as
2 -- $Header: gmsadlsb.pls 120.5.12020000.2 2012/10/16 13:41:46 navemish ship $
3
4 -- -----------------------------------------
5 -- get_award_set_id returns the next
6 -- award set id in sequence.
7 -- -----------------------------------------
8 FUNCTION get_award_set_id return NUMBER is
9 x_award_set_id NUMBER ;
10 p_err_code NUMBER ;
11 p_err_buf varchar2(2000) ;
12 BEGIN
13
14 SELECT gms_adls_award_set_id_s.NEXTVAL
15 INTO x_award_set_id
16 FROM dual ;
17 return nvl(x_award_set_id,0) ;
18 EXCEPTION
19 WHEN OTHERS THEN
20 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
21 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG : get_award_set_id ',
22 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
23 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
24 x_err_code => p_err_code, x_err_buff => p_err_buf
25 ) ;
26 RAISE ;
27 END get_award_set_id ;
28 -- ================== END OF get_award_set_id ===================
29
30 -- Bug 5344693 : Added a new parameter p_called_from
31 PROCEDURE copy_adls( p_award_set_id IN NUMBER ,
32 P_NEW_AWARD_SET_ID OUT NOCOPY NUMBER,
33 p_doc_type IN varchar2,
34 p_dist_id IN NUMBER,
35 P_INVOICE_ID IN NUMBER DEFAULT NULL,
36 p_dist_line_num IN NUMBER DEFAULT NULL,
37 p_raw_cost IN NUMBER DEFAULT NULL,
38 p_called_from IN varchar2 DEFAULT 'NOT_MISC_SYNCH_ADLS') IS
39
40 x_adls_rec gms_award_distributions%ROWTYPE ;
41 g_rec_index Number ;
42 x_new_award_set_id NUMBER ;
43 x_line_num_reversed NUMBER ;
44 x_reversed_flag varchar2(1) ;
45 x_adl_line_num NUMBER ;
46 x_amount NUMBER ;
47 X_billable_flag varchar2(1) ;
48 x_ind_compiled_set_id NUMBER ;
49 p_err_code NUMBER ;
50 p_err_buf varchar2(2000) ;
51
52 CDL_NOT_FOUND EXCEPTION;
53 PRAGMA EXCEPTION_INIT(CDL_NOT_FOUND, -20000 ) ;
54
55 -- ------------------------------------------------------
56 -- CURSOR : C_CDL is declared to fetch values which are
57 -- required to create adls.
58 -- p_dist_line_num = CDL LINE NUMBER
59 -- p_dist_id = Expenditure Item ID.
60 -- -------------------------------------------------------
61 CURSOR C_CDL IS
62 SELECT line_num_reversed,
63 REVERSED_FLAG,
64 IND_COMPILED_SET_ID,
65 BILLABLE_FLAG,
66 AMOUNT
67 FROM PA_COST_DISTRIBUTION_LINES_ALL
68 WHERE LINE_NUM = p_dist_line_num
72 g_rec_index := 1 ;
69 and expenditure_item_id = p_dist_id ;
70 BEGIN
71
73 --
74 -- 3478028
75 -- 11.5.10 Patch for grants accounting.
76 -- BUG: 3517362 forward port funds check related changes.
77
78 --IF p_doc_type = 'AP' and NVL(PSA_FUNDS_CHECKER_PKG.g_fc_autonomous_flag,'N') = 'Y'then
79 /* Bug 5344693 : The procedure copy_adls is called from the function gms_funds_control_pkg.misc_synch_adls
80 to create ADLS for the Price Variance record in the scenario when we are trying to funds
81 check for an invoice matched to a PO with price variance. */
82 IF ((p_doc_type = 'AP') and (p_called_from <> 'MISC_SYNCH_ADLS')) then
83 return ;
84 end if ;
85
86
87 -- -----------------------------------------------
88 -- Special requirements for expenditure items.
89 -- We need line_num_reversed, REVERSED_FLAG,
90 -- IND_COMPILED_SET_ID, AMOUNT, fetched
91 -- using C_CDL cursor.
92 -- ADL_LINE_NUM should be the max(line_num) + 1.
93 -- -----------------------------------------------
94 IF p_doc_type = 'EXP' THEN
95
96 OPEN C_CDL ;
97
98 FETCH C_CDL
99 INTO x_line_num_reversed,
100 x_reversed_flag ,
101 x_ind_compiled_set_id,
102 X_billable_flag,
103 x_amount ;
104
105 IF C_CDL%NOTFOUND THEN
106 -- CDL NOT found so we can not create ADL.
107 -- --------------------------------------
108 close C_CDL ;
109 return ;
110 ELSE
111 update gms_award_distributions
112 set reversed_flag = 'Y'
113 where award_set_id = p_award_set_id
114 and document_type = 'EXP'
115 and adl_status = 'A'
116 and cdl_line_num = nvl(x_line_num_reversed, -9 )
117 and expenditure_item_id = p_dist_id ;
118 END IF ;
119 close C_CDL ;
120 -- ---------------------------------
121 -- Get the next adl line num.
122 -- --------------------------------
123 SELECT max(adl_line_num ) + 1
124 INTO x_adl_line_num
125 FROM gms_award_distributions
126 where award_set_id = p_award_set_id ;
127
128 x_new_award_set_id := p_award_set_id ;
129 ELSE
130 -- -------------------------
131 -- Get the award set id .
132 -- -------------------------
133 x_new_award_set_id := get_award_set_id ;
134
135 END IF ;
136
137
138 -- -----------------------------------
139 -- Create new record into ADL.
140 -- -----------------------------------
141 INSERT INTO gms_award_distributions ( award_set_id ,
142 adl_line_num,
143 funding_pattern_id,
144 distribution_value ,
145 raw_cost,
146 document_type,
147 project_id ,
148 task_id ,
149 award_id ,
150 --expenditure_type ,
151 expenditure_item_id ,
152 cdl_line_num ,
153 ind_compiled_set_id ,
154 gl_date ,
155 request_id ,
156 line_num_reversed ,
157 resource_list_member_id ,
158 --output_vat_tax_id ,--ETax Change:Replace the tax_id with classificationcode
159 output_tax_classification_code,
160 output_tax_exempt_flag ,
161 output_tax_exempt_reason_code ,
162 output_tax_exempt_number ,
163 adl_status ,
164 fc_status ,
165 line_type ,
166 capitalized_flag ,
167 capitalizable_flag ,
168 reversed_flag ,
169 revenue_distributed_flag ,
170 billed_flag ,
171 bill_hold_flag ,
172 distribution_id ,
173 po_distribution_id ,
174 invoice_distribution_id ,
175 parent_award_set_id ,
176 invoice_id ,
177 parent_adl_line_num ,
178 distribution_line_number ,
179 burdenable_raw_cost ,
180 cost_distributed_flag ,
181 last_update_date ,
185 last_update_login ,
182 last_updated_by ,
183 created_by ,
184 creation_date ,
186 billable_flag )
187 select
188 x_new_award_set_id ,
189 decode(p_doc_type, 'EXP', x_adl_line_num, adl_line_num ) , -- ADL_LINE_NUM
190 funding_pattern_id,
191 distribution_value ,
192 decode(p_doc_type, 'EXP', x_amount, p_raw_cost ) ,-- p_raw_cost
193 p_doc_type,
194 project_id ,
195 task_id ,
196 award_id ,
197 --expenditure_type ,
198 decode(p_doc_type, 'EXP', p_dist_id, expenditure_item_id ),-- expenditure_item_id
199 decode(p_doc_type, 'EXP', p_dist_line_num, CDL_line_num ) ,-- cdl_line_num
200 decode(p_doc_type, 'EXP', x_ind_compiled_set_id, ind_compiled_set_id ) ,-- ind_compiled_set_id
201 gl_date ,
202 request_id ,
203 decode(p_doc_type, 'EXP', x_line_num_reversed, line_num_reversed ) ,-- line_num_reversed
204 resource_list_member_id ,
205 --output_vat_tax_id , --ETax Changes
206 output_tax_classification_code,
207 output_tax_exempt_flag ,
208 output_tax_exempt_reason_code ,
209 output_tax_exempt_number ,
210 adl_status ,
211 'N' , -- FC_STATUS
212 line_type ,
213 NVL(capitalized_flag,'N') ,
214 capitalizable_flag ,
215 decode(p_doc_type, 'EXP', x_reversed_flag, reversed_flag ) ,-- reversed_flag
216 'N' , --revenue_distributed_flag ,
217 'N' , --billed_flag
218 NULL , --bill_hold_flag
219 decode(p_doc_type, 'REQ', p_dist_id, NULL), -- distribution_id ,
220 decode(p_doc_type, 'PO', p_dist_id, NULL), -- po_distribution_id ,
221 decode(p_doc_type, 'AP', p_dist_id, NULL), -- invoice_distribution_id ,
222 parent_award_set_id ,
223 P_invoice_id ,
224 parent_adl_line_num ,
225 decode(p_doc_type, 'AP',p_dist_line_num,NULL) ,
226 null , -- burdenable_raw_cost
227 'N' , -- cost_distributed_flag ,
228 sysdate , -- last_update_date
229 nvl(fnd_global.user_id,0) , -- last_updated_by ,
230 nvl(fnd_global.user_id,0) , -- created_by ,
231 sysdate , -- creation_date ,
232 last_update_login ,
233 nvl( x_billable_flag, NVL(billable_flag,'Y') )
234 from GMS_AWARD_DISTRIBUTIONS
235 where AWARD_SET_ID = P_AWARD_SET_ID
236 AND ADL_STATUS = 'A'
237 AND rownum < 2 ;
238
239
240 -- ----------------------------------------------
241 -- Need to update the Distribution line with the
242 -- new award_set_id
243 -- ----------------------------------------------
244 IF p_doc_type = 'PO' THEN
245 update po_distributions_all
246 set award_id = x_new_award_set_id
247 where po_distribution_id = p_dist_id
248 and award_id = p_award_set_id
249 and exists ( select 'X'
250 from gms_award_distributions
251 where award_set_id = x_new_award_set_id
252 ) ;
253 p_new_award_set_id := x_new_award_set_id ;
254
255 /* Bug 5344693 : The procedure copy_adls is called from the function gms_funds_control_pkg.misc_synch_adls
256 to create ADLS for the Price Variance record in the scenario when we are trying to funds
257 check for an invoice matched to a PO with price variance. After the ADL is created for the
258 IPV distribution the new award_set_id is stamped on the IPV distribution. */
259 ELSIF ((p_doc_type = 'AP') and (p_called_from = 'MISC_SYNCH_ADLS')) THEN
260 -- 3478028 11.5.10 grants accounting patch.
261 -- --------------------------------------------------
262 -- Create a link between ap_invoice_distributions_all
263 -- and ADL.
264 -- --------------------------------------------------
265 -- BUG: 3517362 forward port funds check related changes.
266
267 -- In r12, FC mode is always autonomous ...
268
269 -- IF NVL(PSA_FUNDS_CHECKER_PKG.g_fc_autonomous_flag,'N') <> 'Y' THEN
270
271 UPDATE ap_invoice_distributions_all
272 set award_id = x_new_award_set_id
273 WHERE invoice_id = p_invoice_id
274 and invoice_distribution_id = p_dist_id
275 and exists ( select 'X'
276 from gms_award_distributions
277 where award_set_id = x_new_award_set_id
278 ) ;
279 /* BUG 14216205 : Added the following Code for SAT : Starts */
280 UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL
281 set award_id = x_new_award_set_id
282 WHERE invoice_id = p_invoice_id
283 and invoice_distribution_id = p_dist_id
287 ) ;
284 and exists ( select 'X'
285 from gms_award_distributions
286 where AWARD_SET_ID = X_NEW_AWARD_SET_ID
288 /* BUG 14216205 : Added the following Code for SAT : Ends */
289 /* Bug 5344693 : The following update is added to stamp the distribution_line_number correctly
290 on gms_award_distributions. */
291 UPDATE gms_award_distributions
292 set distribution_line_number = (select distribution_line_number
293 from ap_invoice_distributions_all
294 where invoice_id = p_invoice_id
295 and invoice_distribution_id = p_dist_id
296 and award_id = x_new_award_set_id )
297 where award_set_id = x_new_award_set_id
298 and invoice_id = p_invoice_id
299 and INVOICE_DISTRIBUTION_ID = P_DIST_ID;
300
301 /* BUG 14216205 : Added the following Code for SAT : Starts */
302 UPDATE gms_award_distributions
303 set distribution_line_number = (select distribution_line_number
304 from AP_SELF_ASSESSED_TAX_DIST_ALL
305 where invoice_id = p_invoice_id
306 and invoice_distribution_id = p_dist_id
307 and award_id = x_new_award_set_id )
308 where award_set_id = x_new_award_set_id
309 and INVOICE_ID = P_INVOICE_ID
310 and INVOICE_DISTRIBUTION_ID = P_DIST_ID;
311 /* BUG 14216205 : Added the following Code for SAT : Ends */
312 p_new_award_set_id := x_new_award_set_id ;
313 -- END IF ;
314
315
316 NULL ;
317 ELSIF p_doc_type = 'REQ' THEN -- Bug 2155774
318 update po_req_distributions
319 set award_id = x_new_award_set_id
320 where distribution_id = p_dist_id
321 and award_id = p_award_set_id
322 and exists ( select 'X'
323 from gms_award_distributions
324 where award_set_id = x_new_award_set_id
325 ) ;
326 p_new_award_set_id := x_new_award_set_id ;
327
328 END IF ;
329
330
331 EXCEPTION
332 WHEN CDL_NOT_FOUND THEN
333 CLOSE C_CDL ;
334
335 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
336 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :COPY ADLS',
337 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
338 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
339 x_err_code => p_err_code, x_err_buff => p_err_buf
340 ) ;
341 RAISE ;
342
343 WHEN OTHERS THEN
344
345 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
346 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :Create_adls',
347 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
348 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
349 x_err_code => p_err_code, x_err_buff => p_err_buf
350 ) ;
351 RAISE ;
352 END copy_adls ;
353 -- ================= END OF copy_adls ================================
354
355 -- ----------------------------------------------------------------
356 -- CREATE_ADLS
357 -- The following function allows to create ADL and shared all across.
358 -- -----------------------------------------------------------------
359 PROCEDURE create_adls( p_adls_rec gms_award_distributions%ROWTYPE ) IS
360 x_adls_rec gms_award_distributions%ROWTYPE ;
361 g_rec_index number ;
362 p_err_code NUMBER ;
363 p_err_buf varchar2(2000) ;
364 BEGIN
365 g_rec_index := 1 ;
366 x_adls_rec := p_adls_rec ;
367
368 IF x_adls_rec.last_update_date is NULL then
369 x_adls_rec.last_update_date := sysdate ;
370 END IF ;
371 IF x_adls_rec.last_updated_by is NULL THEN
372 x_adls_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
373 END IF ;
374 IF x_adls_rec.created_by is NULL THEN
375 x_adls_rec.created_by := nvl(fnd_global.user_id,0) ;
376 END IF ;
377
378 IF x_adls_rec.creation_date is NULL THEN
379 x_adls_rec.creation_date := SYSDATE ;
380 END IF ;
381
382 IF x_adls_rec.last_update_login is NULL THEN
383 x_adls_rec.last_update_login := 0 ;
384 END IF ;
385
386 /* Bug 4301049, 4610217 starts here */
387 -- 22-NOV-2005
388
389 IF x_adls_rec.distribution_value is NULL THEN
390 x_adls_rec.distribution_value:=100;
391 END IF;
392 /* Bug 4301049, 4610217 ends here */
393
394 INSERT into gms_award_distributions ( award_set_id ,
395 adl_line_num,
396 funding_pattern_id,
397 distribution_value ,
398 raw_cost,
399 document_type,
400 project_id ,
401 task_id ,
402 award_id ,
403 --expenditure_type ,
404 expenditure_item_id ,
405 cdl_line_num ,
409 line_num_reversed ,
406 ind_compiled_set_id ,
407 gl_date ,
408 request_id ,
410 resource_list_member_id ,
411 --output_vat_tax_id ,--ETax Changes Replacing the tax id changes with tax_classification code
412 output_tax_classification_code,
413 output_tax_exempt_flag ,
414 output_tax_exempt_reason_code ,
415 output_tax_exempt_number ,
416 adl_status ,
417 fc_status ,
418 line_type ,
419 capitalized_flag ,
420 capitalizable_flag ,
421 reversed_flag ,
422 revenue_distributed_flag ,
423 billed_flag ,
424 bill_hold_flag ,
425 distribution_id ,
426 po_distribution_id ,
427 invoice_distribution_id ,
428 parent_award_set_id ,
429 invoice_id ,
430 parent_adl_line_num ,
431 distribution_line_number ,
432 burdenable_raw_cost ,
433 cost_distributed_flag ,
434 last_update_date ,
435 last_updated_by ,
436 created_by ,
437 creation_date ,
438 last_update_login ,
439 billable_flag )
440 Values ( x_adls_rec.award_set_id ,
441 x_adls_rec.adl_line_num,
442 x_adls_rec.funding_pattern_id,
443 x_adls_rec.distribution_value ,
444 x_adls_rec.raw_cost,
445 x_adls_rec.document_type,
446 x_adls_rec.project_id ,
447 x_adls_rec.task_id ,
448 x_adls_rec.award_id ,
449 --x_adls_rec.expenditure_type ,
450 x_adls_rec.expenditure_item_id ,
451 x_adls_rec.cdl_line_num ,
452 x_adls_rec.ind_compiled_set_id ,
453 x_adls_rec.gl_date ,
454 x_adls_rec.request_id ,
455 x_adls_rec.line_num_reversed ,
456 x_adls_rec.resource_list_member_id ,
457 --x_adls_rec.output_vat_tax_id , --Etax Changes
458 x_adls_rec.output_tax_classification_code,
459 x_adls_rec.output_tax_exempt_flag ,
460 x_adls_rec.output_tax_exempt_reason_code ,
461 x_adls_rec.output_tax_exempt_number ,
462 x_adls_rec.adl_status ,
463 nvl(x_adls_rec.fc_status,'N') ,
464 x_adls_rec.line_type ,
465 NVL(x_adls_rec.capitalized_flag,'N') ,
466 x_adls_rec.capitalizable_flag ,
467 x_adls_rec.reversed_flag ,
468 NVL(x_adls_rec.revenue_distributed_flag,'N') ,
469 NVL(x_adls_rec.billed_flag,'N') ,
470 x_adls_rec.bill_hold_flag ,
471 x_adls_rec.distribution_id ,
472 x_adls_rec.po_distribution_id ,
473 x_adls_rec.invoice_distribution_id ,
474 x_adls_rec.parent_award_set_id ,
475 x_adls_rec.invoice_id ,
476 x_adls_rec.parent_adl_line_num ,
477 x_adls_rec.distribution_line_number ,
478 x_adls_rec.burdenable_raw_cost ,
479 NVL(x_adls_rec.cost_distributed_flag,'N') ,
480 x_adls_rec.last_update_date ,
481 x_adls_rec.last_updated_by ,
482 x_adls_rec.created_by ,
483 x_adls_rec.creation_date ,
487 WHEN OTHERS THEN
484 x_adls_rec.last_update_login ,
485 NVL(x_adls_rec.billable_flag, 'Y') ) ;
486 EXCEPTION
488 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
489 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :CREATE ADLS',
490 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
491 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
492 x_err_code => p_err_code, x_err_buff => p_err_buf
493 ) ;
494 RAISE ;
495 END create_adls ;
496 -- ================= END OF create_adls ================================
497
498
499 PROCEDURE update_adls( p_adls_rec gms_award_distributions%ROWTYPE ) IS
500 x_adls_rec gms_award_distributions%ROWTYPE ;
501 g_rec_index number ;
502 p_err_code NUMBER ;
503 p_err_buf varchar2(2000) ;
504 BEGIN
505 g_rec_index := 1 ;
506 x_adls_rec := p_adls_rec ;
507
508 IF x_adls_rec.last_update_date is NULL then
509 x_adls_rec.last_update_date := sysdate ;
510 END IF ;
511 IF x_adls_rec.last_updated_by is NULL THEN
512 x_adls_rec.last_updated_by := fnd_global.user_id ;
513 END IF ;
514 IF x_adls_rec.created_by is NULL THEN
515 x_adls_rec.created_by := fnd_global.user_id ;
516 END IF ;
517 IF x_adls_rec.creation_date is NULL THEN
518 x_adls_rec.creation_date := SYSDATE ;
519 END IF ;
520
521 UPDATE gms_award_distributions
522 SET funding_pattern_id = x_adls_rec.funding_pattern_id,
523 distribution_value = x_adls_rec.distribution_value,
524 document_type = x_adls_rec.document_type,
525 project_id = x_adls_rec.project_id,
526 task_id = x_adls_rec.task_id,
527 award_id = x_adls_rec.award_id ,
528 --expenditure_type = x_adls_rec.expenditure_type ,
529 expenditure_item_id = x_adls_rec.expenditure_item_id ,
530 cdl_line_num = x_adls_rec.cdl_line_num ,
531 ind_compiled_set_id = x_adls_rec.ind_compiled_set_id ,
532 gl_date = x_adls_rec.gl_date ,
533 request_id = x_adls_rec.request_id ,
534 line_num_reversed = x_adls_rec.line_num_reversed ,
535 resource_list_member_id = x_adls_rec.resource_list_member_id ,
536 --output_vat_tax_id = x_adls_rec.output_vat_tax_id ,
537 output_tax_classification_code = x_adls_rec.output_tax_classification_code,
538 output_tax_exempt_flag = x_adls_rec.output_tax_exempt_flag ,
539 output_tax_exempt_reason_code = x_adls_rec.output_tax_exempt_reason_code,
540 output_tax_exempt_number = x_adls_rec.output_tax_exempt_number ,
541 adl_status = x_adls_rec.adl_status ,
542 fc_status = x_adls_rec.fc_status ,
543 line_type = x_adls_rec.line_type ,
544 capitalized_flag = x_adls_rec.capitalized_flag ,
545 capitalizable_flag = x_adls_rec.capitalizable_flag ,
546 reversed_flag = x_adls_rec.reversed_flag,
547 revenue_distributed_flag = x_adls_rec.revenue_distributed_flag,
548 billed_flag = x_adls_rec.billed_flag,
549 bill_hold_flag = x_adls_rec.bill_hold_flag,
550 distribution_id = x_adls_rec.distribution_id,
551 po_distribution_id = x_adls_rec.po_distribution_id,
552 invoice_distribution_id = x_adls_rec.invoice_distribution_id,
553 parent_award_set_id = x_adls_rec.parent_award_set_id,
554 invoice_id = x_adls_rec.invoice_id,
555 parent_adl_line_num = x_adls_rec.parent_adl_line_num,
556 distribution_line_number = x_adls_rec.distribution_line_number,
557 burdenable_raw_cost = x_adls_rec.burdenable_raw_cost,
558 cost_distributed_flag = x_adls_rec.cost_distributed_flag,
559 last_update_date = x_adls_rec.last_update_date,
560 last_updated_by = x_adls_rec.last_updated_by,
561 created_by = x_adls_rec.created_by,
562 creation_date = x_adls_rec.creation_date,
563 last_update_login = x_adls_rec.last_update_login,
564 billable_flag = X_adls_rec.billable_flag
565 WHERE award_set_id = x_adls_rec.award_set_id and
566 adl_line_num = x_adls_rec.adl_line_num ;
567 EXCEPTION
568 WHEN OTHERS THEN
569 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
570 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :UPDATE ADLS',
571 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
572 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
573 x_err_code => p_err_code, x_err_buff => p_err_buf
574 ) ;
575 RAISE ;
576 END update_adls ;
577 -- ================ END OF update_adls ====================
578
579 -- ----------------------------------------------------------------
580 -- DELETE_ADLS
581 -- The following function allows to delete ADLS.
582 -- -----------------------------------------------------------------
583 PROCEDURE delete_adls( p_distribution_set_id NUMBER ) is
584 p_err_code NUMBER ;
585 p_err_buf varchar2(2000) ;
586 BEGIN
587 -- DELETE gms_award_distributions
588 -- WHERE distribution_set_id = p_distribution_set_id ;
589 NULL ;
590 EXCEPTION
591 WHEN OTHERS THEN
592 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
596 x_err_code => p_err_code, x_err_buff => p_err_buf
593 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :DELETE ADLS',
594 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
595 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
597 ) ;
598 RAISE ;
599 END delete_adls ;
600 -- ================ END OF delete_adls ====================
601
602 -- ---------------------------------------------------------------------
603 -- API to delete gms_award_distribution record.
604 -- 3733123 - PJ.M:B5: QA:P11:OTH: MANUAL ENC/EXP FORM CREATING ORPHAN ADLS
605 -- ---------------------------------------------------------------------
606 PROCEDURE delete_adls( p_doc_header_id IN NUMBER,
607 p_doc_distribution_id IN NUMBER,
608 p_doc_type IN VARCHAR2 ) is
609 BEGIN
610
611 IF p_doc_header_id is NULL and
612 p_doc_distribution_id is NULL THEN
613
614 return ;
615 END IF ;
616
617 IF p_doc_type = 'EXP' THEN
618 IF p_doc_distribution_id is NOT NULL THEN
619
620 -- =====
621 -- Delete award distribution line for a given expenditure item.
622 -- =====
623 delete from gms_award_distributions adls
624 where document_type = 'EXP'
625 and expenditure_item_id in ( select expenditure_item_id
626 from pa_expenditure_items_all ei
627 where expenditure_item_id = p_doc_distribution_id ) ;
628 ELSE
629
630 -- =====
631 -- Delete award distribution line for a given expenditure.
632 -- =====
633 delete from gms_award_distributions adls
634 where document_type = 'EXP'
635 and expenditure_item_id in ( select expenditure_item_id
636 from pa_expenditure_items_all ei
637 where expenditure_id = p_doc_header_id ) ;
638 END IF ;
639
640 ELSIF p_doc_type = 'ENC' THEN
641
642 IF p_doc_distribution_id is NOT NULL THEN
643 -- =====
644 -- Delete award distribution line for a given encumbrance item.
645 -- =====
646 delete from gms_award_distributions adls
647 where document_type = 'ENC'
648 and expenditure_item_id in ( select encumbrance_item_id
649 from gms_encumbrance_items_all ei
650 where encumbrance_item_id = p_doc_distribution_id ) ;
651 ELSE
652 -- =====
653 -- Delete award distribution line for a given encumbrance.
654 -- =====
655 delete from gms_award_distributions adls
656 where document_type = 'ENC'
657 and expenditure_item_id in ( select encumbrance_item_id
658 from gms_encumbrance_items_all ei
659 where encumbrance_id = p_doc_header_id ) ;
660
661 END IF ;
662 END IF ;
663 END delete_adls ;
664 --
665 -- 3733123 - PJ.M:B5: QA:P11:OTH: MANUAL ENC/EXP FORM CREATING ORPHAN ADLS
666 -- End of code change.
667 -- ---------------------------------------------------------------------
668 PROCEDURE clean_dangling_adls is
669 p_err_code NUMBER ;
670 p_err_buf varchar2(2000) ;
671 BEGIN
672 NULL ;
673
674 EXCEPTION
675 WHEN OTHERS THEN
676 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
677 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG : clean_dangling_adls',
678 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
679 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
680 x_err_code => p_err_code, x_err_buff => p_err_buf
681 ) ;
682 RAISE ;
683
684 END clean_dangling_adls ;
685
686 -- ------------------------------------------------------------------------------
687 -- Supplier Invoice Interface logic of creating ADLS.
688 -- LD PA Interface logic of creating ADLS.
689 -- InsSi_items - Creates ADLS for the new expenditure items
690 -- created for PA Interface from payables/LD.
691 -- This is called from PA_TRX_IMPORT.NEWexpend.
692 -- ------------------------------------------------------------------------------
693 /***************************************************************
694 * This call is removed because it is called now from GMS_PA_API
695 * gmspax1b.pls
696 ****************************************************************
697 PROCEDURE InsSI_Items( X_user IN NUMBER
698 , X_login IN NUMBER
699 , X_module IN VARCHAR2
700 , X_calling_process IN VARCHAR2
701 , Rows IN BINARY_INTEGER
702 , X_status IN OUT NOCOPY NUMBER )
703 IS
704 END InsSI_Items ;
705 ***************************************************/
706 -- --------------------------------------------------------------
707 -- PROCEDURE update_billable_flag (p_expenditure_item_id in number)
708 -- This procedure will initialize the billable flag
709 -- in PA_EXPENDITURE_ITEMS_ALL
710 -- Called from trigger GMS_UPDATE_EI_BILLABLE_FLAG
711 -- on GMS_AWARD_DISTRIBUTIONS
712 -- this Procedure is created as direct update of
713 -- other products tables directly from trigger leads to warning in
714 -- adpatch
715 -- --------------------------------------------------------------
716 PROCEDURE update_billable_flag (p_expenditure_item_id in number)
717 IS
718 BEGIN
719 -- Commenting below code, Bug 1756179
720 /* Update pa_expenditure_items_all
721 set billable_flag = 'Y'
725 EXCEPTION
722 where expenditure_item_id = p_expenditure_item_id
723 and nvl(billable_flag ,'N') = 'N'; */
724 NULL; -- Added bug 1756179
726 WHEN OTHERS THEN
727 RAISE;
728 END update_billable_flag ;
729
730 -- --------------------------------------------------------------
731
732 -- Start of comments
733 -- -----------------
734 -- API Name : check_award_funding
735 -- Type : private
736 -- Pre Reqs : None
737 --
738 -- Function : check award funding identifies the award funding the project.
739 -- Calling API : verify_create_adl
740 -- End of comments
741 -- ----------------
742
743 PROCEDURE check_award_funding ( p_project_id IN NUMBER,
744 p_award_id IN OUT NOCOPY NUMBER,
745 p_status out NOCOPY NUMBER ) IS
746
747 l_award_id NUMBER ;
748 l_status NUMBER ;
749
750 -- =====================================================
751 -- Cursor : c_validate_award
752 -- Cursor verifies that award is funded by the
753 -- project.
754 -- =====================================================
755 CURSOR C_validate_award IS
756 SELECT ins.award_id
757 FROM gms_installments ins,
758 gms_summary_project_fundings pf
759 WHERE ins.installment_id = pf.installment_id
760 AND pf.project_id = p_project_id
761 AND ins.award_id = p_award_id ;
762
763 -- =====================================================
764 -- Cursor : c_get_award
765 -- Cursor finds out if there is a award funding the
766 -- project charged to a transaction.
767 -- =====================================================
768 CURSOR c_get_award IS
769 SELECT ins.award_id
770 FROM gms_installments ins,
771 gms_summary_project_fundings pf
772 WHERE ins.installment_id = pf.installment_id
773 AND pf.project_id = p_project_id
774 AND NOT EXISTS ( SELECT 1
775 FROM gms_installments ins2,
776 gms_summary_project_fundings pf2
777 WHERE ins2.installment_id = pf2.installment_id
778 AND pf2.project_id = pf.project_id
779 AND ins2.award_id <> ins.award_id ) ;
780 BEGIN
781 l_award_id := p_award_id ;
782 l_status := 0 ;
783
784 -- =================================
785 -- Validate award.
786 -- =================================
787 IF p_award_id is not NULL THEN
788 OPEN c_validate_award ;
789 FETCH c_validate_award into l_award_id ;
790 CLOSE c_validate_award ;
791 END IF ;
792
793 -- There is no valid award yet.
794 -- checking to see if there
795
796 IF l_award_id is NULL THEN
797 OPEN c_get_award ;
798 FETCH c_get_award into l_award_id ;
799 CLOSE c_get_award ;
800 END IF ;
801
802 IF l_award_id is NULL THEN
803 l_status:= -1 ;
804 ELSE
805 p_award_id := l_award_id ;
806 END IF ;
807
808 p_status := l_status ;
809
810 END check_award_funding ;
811 -- End of check_award_funding
812 -- ----------------------------
813
814 -- Start of comments
815 -- -----------------
816 -- API Name : verify_create_adl
817 -- Type : public
818 -- Pre Reqs : None
819 -- Function : This is used to create award distribution lines
820 -- using the bulk processing.
821 -- Logic : Identify the newly created PO/REQ/REL distribution
822 -- lines and create award distribution lines for
823 -- sponsored project.
824 -- Parameters :
825 -- IN : p_header_id IN NUMBER
826 -- The PO/REQ/REL id created and that may
827 -- have distributions associated with
828 -- an award.
829 -- : p_doc_type IN varchar2
830 -- It should be PO/REQ/REL.
831 --
832 -- : p_doc_num IN varchar2
833 -- This will have PO/REQ/REL Number.
834 --
835 -- Calling place : POST-FORMS-COMMIT event in PO/REQ/REL
836 --
837 -- End of comments
838 -- ----------------
839
840 PROCEDURE VERIFY_CREATE_ADLS( p_header_id IN NUMBER ,
841 p_doc_type IN VARCHAR2,
842 p_doc_num IN VARCHAR2
843 ) IS
844
845
846 type gms_po_req_type_number is table of number index by binary_integer;
847 type gms_po_req_type_date is table of date index by binary_integer;
848
849 l_distribution_num gms_po_req_type_number;
850 l_distribution_id gms_po_req_type_number;
851 l_project_id gms_po_req_type_number;
852 l_task_id gms_po_req_type_number;
853 l_award_id gms_po_req_type_number;
854 l_new_award_set_id gms_po_req_type_number;
855 l_last_update_date gms_po_req_type_date;
856 l_creation_date gms_po_req_type_date;
857 l_last_updated_by gms_po_req_type_number;
858 l_created_by gms_po_req_type_number;
859 l_last_update_login gms_po_req_type_number;
860 l_dummy_tab gms_po_req_type_number;
861
862 l_award_set_id gms_po_req_type_number ;
863 l_award_set_idX NUMBER ;
864 l_count NUMBER ;
868 AWARD_NOT_FOUND EXCEPTION ;
865 l_project_idX NUMBER ;
866 l_award_idX NUMBER ;
867 l_status NUMBER ;
869
870 CURSOR c_adl IS
871 SELECT *
872 FROM gms_award_distributions
873 WHERE award_set_id = l_award_set_idX
874 AND adl_line_num = 1 ;
875 l_adl_rec c_adl%ROWTYPE ;
876
877
878 PROCEDURE VERIFY_REQUISITIONS IS
879
880 CURSOR c_req IS
881 SELECT rl.requisition_header_id header_id,
882 rd.distribution_num distribution_num,
883 rd.distribution_id distribution_id,
884 rd.project_id project_id,
885 rd.task_id task_id,
886 rd.award_id award_set_id,
887 rd.last_update_date last_update_date,
888 rd.creation_date creation_date,
889 rd.last_updated_by last_updated_by,
890 rd.created_by created_by,
891 nvl(rd.last_update_login,0) last_update_login
892 FROM po_req_distributions_all rd,
893 po_requisition_lines_all rl,
894 pa_projects_all pp,
895 gms_project_types gpt
896 WHERE rl.requisition_header_id = p_header_id
897 AND rd.requisition_line_id = rl.requisition_line_id
898 AND rd.project_id = pp.project_id
899 AND pp.project_type = gpt.project_type
900 AND gpt.sponsored_flag = 'Y'
901 --
902 -- BUG : 3603758
903 -- Award Distribution is failing in PO and Req.
904 -- We need to skip records associated with the dummy award fo adls
905 -- creation.
906 AND NVL(rd.award_id,0) >= 0 ;
907
908
909 BEGIN
910
911 l_count :=0;
912 -- Activate ADLS that belongs to the REQ distribution line
913
914 l_dummy_tab.DELETE ;
915 --
916 -- BUG : 4953765
917 -- : R12.PJ:XB2:DEV:GMS:APPSPERF: PERFORMANCE ISSUE IN GMSADLSB.PLS 1 SQL
918 -- : Update statements were changed to bulk updates.
919 --
920
921 SELECT adl2.award_set_id
922 bulk collect into l_dummy_tab
923 FROM po_req_distributions_all rd,
924 po_requisition_lines_all rl,
925 gms_award_distributions adl2
926 WHERE rl.requisition_header_id = p_header_id
927 AND rd.requisition_line_id = rl.requisition_line_id
928 AND adl2.distribution_id = rd.distribution_id
929 AND adl2.document_type = 'REQ'
930 AND adl2.award_set_id = rd.award_id
931 AND adl2.adl_status = 'I' ;
932
933
934 IF l_dummy_tab.count > 0 THEN
935
936 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
937
938 UPDATE gms_award_distributions
939 set adl_status = 'A'
940 where award_set_id = l_dummy_tab(i) ;
941
942 END IF ;
943
944 l_dummy_tab.DELETE ;
945
946 -- Inactivate ADLS that belongs to the REQ distribution line but
947 -- not tied up with award_id in distribution line.
948 -- Inactivate dangling active adls.
949 -- ----
950 --
951 -- BUG : 4953765
952 -- : R12.PJ:XB2:DEV:GMS:APPSPERF: PERFORMANCE ISSUE IN GMSADLSB.PLS 1 SQL
953 -- : Update statements were changed to bulk updates.
954 --
955 SELECT adl2.award_set_id
956 bulk collect into l_dummy_tab
957 FROM po_req_distributions_all rd,
958 po_requisition_lines_all rl,
959 gms_award_distributions adl2
960 WHERE rl.requisition_header_id = p_header_id
961 AND rd.requisition_line_id = rl.requisition_line_id
962 AND adl2.distribution_id = rd.distribution_id
963 AND adl2.document_type = 'REQ'
964 AND adl2.award_set_id <> rd.award_id
965 AND adl2.adl_status = 'A' ;
966
967
968 IF l_dummy_tab.count > 0 THEN
969
970 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
971
972 UPDATE gms_award_distributions
973 set adl_status = 'I'
974 where award_set_id = l_dummy_tab(i) ;
975
976 END IF ;
977
978 l_dummy_tab.DELETE ;
979
980 -- ==================================================
981 -- Update award_id to NULL for non sponsored
982 -- projects.
983 -- =================================================
984 --
985 -- BUG : 4953765
986 -- : R12.PJ:XB2:DEV:GMS:APPSPERF: PERFORMANCE ISSUE IN GMSADLSB.PLS 1 SQL
987 -- : Update statements were changed to bulk updates.
988 --
989
990 SELECT rd2.distribution_id
991 bulk collect into l_dummy_tab
992 FROM po_req_distributions_all rd2,
993 po_requisition_lines_all rl,
994 pa_projects_all pp,
995 gms_project_types gpt
996 WHERE rl.requisition_header_id = p_header_id
997 AND rd2.requisition_line_id = rl.requisition_line_id
998 AND rd2.project_id = pp.project_id
999 and rd2.award_id is NOT NULL
1000 AND pp.project_type = gpt.project_type
1001 AND gpt.sponsored_flag = 'N' ;
1002
1003 IF l_dummy_tab.count > 0 THEN
1004
1005 FORALL i in l_dummy_tab.FIRST..l_dummy_tab.LAST
1006 UPDATE po_req_distributions_all rd
1007 SET award_id = NULL
1008 WHERE rd.distribution_id = l_dummy_tab(i) ;
1009
1010 END IF ;
1011
1012 l_dummy_tab.DELETE ;
1016 FOR req_rec in c_req LOOP
1013
1014 l_count :=0;
1015
1017
1018 l_award_set_idX := NVL(req_rec.award_set_id,0) ;
1019 l_adl_rec := NULL ;
1020
1021 OPEN c_adl ;
1022 FETCH c_adl into l_adl_rec ;
1023 CLOSE c_adl ;
1024
1025 IF NOT (( NVL(l_adl_rec.adl_status,'I') = 'A' ) AND
1026 ( NVL(l_adl_rec.document_type,'X') = 'REQ' ) AND
1027 ( NVL(l_adl_rec.distribution_id,0) = NVL( req_rec.distribution_id,0) )) THEN
1028
1029 l_count := l_count + 1 ;
1030 l_distribution_id(l_count) := req_rec.distribution_id;
1031 l_distribution_num(l_count) := req_rec.distribution_num;
1032 l_project_id(l_count) := req_rec.project_id;
1033 l_task_id(l_count) := req_rec.task_id;
1034 l_project_idX := req_rec.project_id;
1035 l_award_idX := l_adl_rec.award_id ;
1036
1037 check_award_funding( l_project_idX, l_award_idX, l_status ) ;
1038
1039 IF l_status = -1 THEN
1040 raise AWARD_NOT_FOUND ;
1041 ELSE
1042 l_award_id(l_count) := l_award_idX ;
1043 END IF ;
1044
1045
1046 l_last_update_date(l_count) := req_rec.last_update_date;
1047 l_creation_date(l_count) := req_rec.creation_date;
1048 l_last_updated_by(l_count) := req_rec.last_updated_by;
1049 l_created_by(l_count) := NVL(req_rec.created_by,0);
1050 l_last_update_login(l_count) := req_rec.last_update_login;
1051 l_new_award_set_id(l_count) := gms_awards_dist_pkg.get_award_set_id ;
1052
1053 END IF ;
1054
1055 END LOOP ;
1056
1057 -- Start of comments
1058 -- Check if need to proceed.
1059 -- End of comment.
1060
1061 IF l_distribution_id.count = 0 then
1062 return ;
1063 END IF ;
1064
1065
1066 -- Start of comment.
1067 -- Loop through all the collection and insert into the ADL table.
1068 -- Update the ap inv dist record with the newly created ADLs award set id.
1069 -- End of comment
1070
1071
1072 FORALL i in l_distribution_id.FIRST..l_distribution_id.LAST
1073 INSERT INTO gms_award_distributions ( award_set_id ,
1074 adl_line_num ,
1075 document_type ,
1076 distribution_value ,
1077 project_id ,
1078 task_id ,
1079 award_id ,
1080 adl_status ,
1081 fc_status ,
1082 line_type ,
1083 capitalized_flag ,
1084 revenue_distributed_flag ,
1085 billed_flag ,
1086 distribution_id ,
1087 burdenable_raw_cost ,
1088 cost_distributed_flag ,
1089 last_update_date ,
1090 last_updated_by ,
1091 created_by ,
1092 creation_date ,
1093 last_update_login ,
1094 billable_flag )
1095 VALUES ( l_new_award_set_id(i) ,
1096 1, --adl_line_num ,
1097 'REQ' , --document_type ,
1098 100 ,
1099 l_project_id(i) ,
1100 l_task_id(i) ,
1101 l_award_id(i) ,
1102 'A', --adl_status ,
1103 'N', --fc_status ,
1104 'R', --line_type ,
1105 'N' ,
1106 'N' ,
1107 'N' ,
1108 l_distribution_id(i),
1109 NULL, --burdenable_raw_cost ,
1110 'N' ,
1111 l_last_update_date(i) ,
1112 l_last_updated_by(i) ,
1113 l_created_by(i) ,
1114 l_creation_date(i) ,
1115 l_last_update_login(i) ,
1116 'Y') ;
1117
1118 -- Start of comment.
1119 -- Update REQ distribution with the award set id.
1120 -- End of comment.
1121
1122 FORALL k in l_distribution_id.FIRST..l_distribution_id.LAST
1123 UPDATE po_req_distributions_all
1124 SET award_id = l_new_award_set_id(k)
1125 WHERE distribution_id = l_distribution_id(k);
1126
1127
1128
1129 END VERIFY_REQUISITIONS;
1130
1131
1132 PROCEDURE VERIFY_PO IS
1136 pod.po_distribution_id distribution_id,
1133 CURSOR c_po is
1134 SELECT pod.po_header_id header_id,
1135 pod.distribution_num distribution_num,
1137 pod.project_id project_id,
1138 pod.task_id task_id,
1139 pod.award_id award_set_id,
1140 pod.last_update_date last_update_date,
1141 pod.creation_date creation_date,
1142 pod.last_updated_by last_updated_by,
1143 pod.created_by created_by,
1144 nvl(pod.last_update_login,0) last_update_login
1145 FROM po_distributions_all pod,
1146 pa_projects_all p,
1147 gms_project_types gpt
1148 WHERE pod.po_header_id = p_header_id
1149 AND pod.project_id = p.project_id
1150 AND p.project_type = gpt.project_type
1151 AND gpt.sponsored_flag = 'Y'
1152 --
1153 -- BUG : 3603758
1154 -- Award Distribution is failing in PO and Req.
1155 -- We need to skip records associated with the dummy award fo adls
1156 -- creation.
1157 AND NVL(pod.award_id,0) >= 0 ;
1158 BEGIN
1159 -- Activate ADLS that belongs to the po distribution line but
1160 UPDATE gms_award_distributions adl
1161 set adl.adl_status = 'A'
1162 WHERE adl.document_type = 'PO'
1163 AND adl.adl_status = 'I'
1164 AND adl.award_set_id in ( SELECT adl2.award_set_id
1165 FROM gms_award_distributions adl2,
1166 po_distributions_all pod
1167 WHERE pod.po_header_Id = p_header_id
1168 AND pod.award_id is not null
1169 AND adl2.award_set_id = pod.award_id
1170 AND adl2.po_distribution_id =pod.po_distribution_id
1171 AND adl2.document_type = 'PO'
1172 AND adl2.adl_status = 'I' ) ;
1173
1174 -- Inactivate ADLS that belongs to the po distribution line but
1175 -- not tied up with award_id in distribution line.
1176 -- Inactivate dangling active adls.
1177 -- ----
1178 UPDATE gms_award_distributions adl
1179 set adl.adl_status = 'I'
1180 WHERE adl.document_type = 'PO'
1181 AND adl.adl_status = 'A'
1182 AND adl.award_set_id in (SELECT adl2.award_set_id
1183 FROM gms_award_distributions adl2,
1184 po_distributions_all pod
1185 WHERE pod.po_header_id = p_header_id
1186 AND pod.award_id is not null
1187 AND adl2.award_set_id <> pod.award_id
1188 AND adl2.po_distribution_id =pod.po_distribution_id
1189 AND adl2.document_type = 'PO'
1190 AND adl2.adl_status = 'A' ) ;
1191
1192
1193 -- ==================================================
1194 -- Update award_id to NULL for non sponsored
1195 -- projects.
1196 -- =================================================
1197 UPDATE po_distributions_all pod
1198 SET award_id = NULL
1199 WHERE pod.po_header_id = p_header_id
1200 AND pod.award_id is not NULL
1201 AND pod.po_distribution_id in ( SELECT pod2.po_distribution_id
1202 FROM po_distributions_all pod2,
1203 pa_projects_all p,
1204 gms_project_types gpt
1205 WHERE pod2.po_header_id = p_header_id
1206 AND pod2.project_id = p.project_id
1207 AND p.project_type = gpt.project_type
1208 AND gpt.sponsored_flag = 'N' ) ;
1209
1210 l_count :=0;
1211 FOR po_rec in c_po LOOP
1212
1213 l_award_set_idX := NVL(po_rec.award_set_id,0) ;
1214 l_adl_rec := NULL ;
1215
1216 OPEN c_adl ;
1217 FETCH c_adl into l_adl_rec ;
1218 CLOSE c_adl ;
1219
1220 IF NOT (( NVL(l_adl_rec.adl_status,'I') = 'A' ) and
1221 ( NVL(l_adl_rec.document_type,'X') = 'PO' ) and
1222 ( NVL(l_adl_rec.po_distribution_id,0) = NVL( po_rec.distribution_id,0) )) THEN
1223
1224 l_count := l_count + 1 ;
1225 l_distribution_id(l_count) := po_rec.distribution_id;
1226 l_distribution_num(l_count) := po_rec.distribution_num;
1227 l_project_id(l_count) := po_rec.project_id;
1228 l_task_id(l_count) := po_rec.task_id;
1229 l_project_idX := po_rec.project_id;
1230 l_award_idX := l_adl_rec.award_id ;
1231
1232 check_award_funding( l_project_idX, l_award_idX, l_status ) ;
1233
1234 IF l_status = -1 THEN
1235 raise AWARD_NOT_FOUND ;
1236 ELSE
1237 l_award_id(l_count) := l_award_idX ;
1238 END IF ;
1239
1240 l_last_update_date(l_count) := po_rec.last_update_date;
1241 l_creation_date(l_count) := po_rec.creation_date;
1242 l_last_updated_by(l_count) := po_rec.last_updated_by;
1243 l_created_by(l_count) := NVL(po_rec.created_by,0);
1244 l_last_update_login(l_count) := po_rec.last_update_login;
1245 l_new_award_set_id(l_count) := gms_awards_dist_pkg.get_award_set_id ;
1246
1247 END IF ;
1248
1249 END LOOP ;
1250
1251
1252
1253 -- Start of comments
1254 -- Check if need to proceed.
1255 -- End of comment.
1256
1257 IF l_distribution_id.count = 0 then
1258 return ;
1259 END IF ;
1260
1261 -- Start of comment.
1262 -- Loop through all the collection and insert into the ADL table.
1266
1263 -- Update the ap inv dist record with the newly created ADLs award set id.
1264 -- End of comment
1265
1267 FORALL i in l_distribution_id.FIRST..l_distribution_id.LAST
1268 INSERT INTO gms_award_distributions ( award_set_id ,
1269 adl_line_num,
1270 document_type,
1271 distribution_value,
1272 project_id ,
1273 task_id ,
1274 award_id ,
1275 adl_status ,
1276 fc_status ,
1277 line_type ,
1278 capitalized_flag ,
1279 revenue_distributed_flag ,
1280 billed_flag ,
1281 po_distribution_id ,
1282 burdenable_raw_cost ,
1283 cost_distributed_flag ,
1284 last_update_date ,
1285 last_updated_by ,
1286 created_by ,
1287 creation_date ,
1288 last_update_login ,
1289 billable_flag )
1290 VALUES ( l_new_award_set_id(i) ,
1291 1, --adl_line_num,
1292 'PO' , --document_type,
1293 100,
1294 l_project_id(i) ,
1295 l_task_id(i) ,
1296 l_award_id(i) ,
1297 'A', --adl_status ,
1298 'N', --fc_status ,
1299 'R', --line_type ,
1300 'N' ,
1301 'N' ,
1302 'N' ,
1303 l_distribution_id(i),
1304 NULL, --burdenable_raw_cost ,
1305 'N' ,
1306 l_last_update_date(i) ,
1307 l_last_updated_by(i) ,
1308 l_created_by(i) ,
1309 l_creation_date(i) ,
1310 l_last_update_login(i) ,
1311 'Y') ;
1312
1313 -- Start of comment.
1314 -- Update po distribution with the award set id.
1315 -- End of comment.
1316
1317 FORALL k in l_distribution_id.FIRST..l_distribution_id.LAST
1318 UPDATE po_distributions_all
1319 SET award_id = l_new_award_set_id(k)
1320 WHERE po_header_id = p_header_id
1321 AND po_distribution_id = l_distribution_id(k) ;
1322
1323
1324 END VERIFY_PO;
1325 BEGIN
1326
1327 -- Start of comment
1328 -- Verify that grants is enabled.
1329 -- End of comments.
1330 --
1331 IF NOT gms_install.enabled THEN
1332 return ;
1333 END IF ;
1334
1335 IF p_doc_type ='REQ' THEN
1336 VERIFY_REQUISITIONS;
1337 ELSIF p_doc_type IN ('PO','REL') THEN
1338 VERIFY_PO;
1339 END IF;
1340 EXCEPTION
1341 WHEN AWARD_NOT_FOUND THEN
1342
1343
1344 --
1345 -- Message : Incorrect award is associated with the PO/REQ/REL : ??? and
1346 -- distribution line number : ??????. Please change award information
1347 -- on the distribution line.
1348
1349 IF p_doc_type ='REQ' THEN
1350 fnd_message.set_name('GMS','GMS_INVALID_REQ_AWARD_FOUND');
1351 fnd_message.set_token('REQNUM',p_doc_num );
1352 fnd_message.set_token('DISTLNO', l_distribution_num(l_count));
1353
1354 ELSIF p_doc_type ='PO' THEN
1355 fnd_message.set_name('GMS','GMS_INVALID_PO_AWARD_FOUND');
1356 fnd_message.set_token('PONUM',p_doc_num );
1357 fnd_message.set_token('DISTLNO', l_distribution_num(l_count));
1358
1359 ELSIF p_doc_type ='REL' THEN
1360 fnd_message.set_name('GMS','GMS_INVALID_REL_AWARD_FOUND');
1361 fnd_message.set_token('RELNUM',p_doc_num );
1362 fnd_message.set_token('DISTLNO', l_distribution_num(l_count));
1363 END IF;
1364 app_exception.raise_exception;
1365 WHEN OTHERS THEN
1366 fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
1367 fnd_message.set_token('PROGRAM_NAME','gms_awards_dist_pkg.verify_create_adls');
1368 fnd_message.set_token('OERRNO',to_char(sqlcode));
1369 fnd_message.set_token('OERRM',sqlerrm);
1370 app_exception.raise_exception;
1371 END VERIFY_CREATE_ADLS;
1372
1373 -- Start of comments
1374 -- -----------------
1375 -- API Name : copy_exp_adls
1376 -- Bug : 3684711
1377 -- Type : Private
1378 -- Pre Reqs : None
1379 -- Function : This is used to create award distribution lines for a reversed expenditure item.
1383 -- IN : P_exp_item_id IN NUMBER
1380 -- Logic : Copy the award distribution from the original expenditure item.
1381 -- This is required to support entry of automatically reversal exp item.
1382 -- Parameters :
1384 -- The original expenditure item.
1385 -- : p_backout_item_id IN NUMBER
1386 -- Reversed expenditure item ID.
1387 -- p_adj_activity IN VARCHAR2
1388 -- adjustment activity
1389 -- P_module, P_user, P_login
1390 -- Calling module, user and login details
1391 -- X_status out number
1392 -- API status
1393 -- This will have PO/REQ/REL Number.
1394 -- Calling Place : PA_ADJUSTMENTS.backout item (PAXTADJB.pls )
1395 --
1396 -- End of comments
1397 -- ----------------
1398
1399 PROCEDURE copy_exp_adls( P_exp_item_id IN NUMBER
1400 , p_backout_item_id IN NUMBER
1401 , p_adj_activity IN VARCHAR2
1402 , P_module IN VARCHAR2
1403 , P_user IN NUMBER
1404 , P_login IN NUMBER
1405 , X_status OUT nocopy NUMBER ) is
1406 l_err_code NUMBER ;
1407 l_err_buf varchar2(2000) ;
1408 BEGIN
1409 INSERT INTO gms_award_distributions
1410 ( award_set_id ,
1411 adl_line_num,
1412 funding_pattern_id,
1413 distribution_value ,
1414 raw_cost,
1415 document_type,
1416 project_id ,
1417 task_id ,
1418 award_id ,
1419 expenditure_item_id ,
1420 cdl_line_num ,
1421 ind_compiled_set_id ,
1422 gl_date ,
1423 request_id ,
1424 line_num_reversed ,
1425 resource_list_member_id ,
1426 --output_vat_tax_id , --ETax Changes
1427 output_tax_classification_code,
1428 output_tax_exempt_flag ,
1429 output_tax_exempt_reason_code ,
1430 output_tax_exempt_number ,
1431 adl_status ,
1432 fc_status ,
1433 line_type ,
1434 capitalized_flag ,
1435 capitalizable_flag ,
1436 reversed_flag ,
1437 revenue_distributed_flag ,
1438 billed_flag ,
1439 bill_hold_flag ,
1440 distribution_id ,
1441 po_distribution_id ,
1442 invoice_distribution_id ,
1443 parent_award_set_id ,
1444 invoice_id ,
1445 parent_adl_line_num ,
1446 distribution_line_number ,
1447 burdenable_raw_cost ,
1448 cost_distributed_flag ,
1449 last_update_date ,
1450 last_updated_by ,
1451 created_by ,
1452 creation_date ,
1453 last_update_login ,
1454 billable_flag )
1455 select get_award_set_id ,
1456 1,
1457 funding_pattern_id,
1458 distribution_value ,
1459 raw_cost* -1 ,
1460 'EXP',
1461 project_id ,
1462 task_id ,
1463 award_id ,
1464 p_backout_item_id,
1465 cdl_line_num,
1466 ind_compiled_set_id ,-- ind_compiled_set_id
1467 NULL ,
1468 request_id ,
1469 NULL,
1470 resource_list_member_id ,
1471 --output_vat_tax_id ,--ETax Changes
1472 output_tax_classification_code,
1473 output_tax_exempt_flag ,
1474 output_tax_exempt_reason_code ,
1475 output_tax_exempt_number ,
1476 adl_status ,
1477 'N' , -- FC_STATUS
1478 line_type ,
1479 NVL(capitalized_flag,'N') ,
1480 capitalizable_flag ,
1481 NULL,
1482 revenue_distributed_flag ,
1483 billed_flag,
1484 bill_hold_flag,
1485 NULL, -- distribution_id ,
1486 NULL, -- po_distribution_id ,
1487 NULL, -- invoice_distribution_id ,
1488 parent_award_set_id ,
1489 NULL ,
1490 parent_adl_line_num ,
1491 NULL ,
1492 null , -- burdenable_raw_cost,
1493 'N' , -- cost_distributed_flag ,
1494 sysdate , -- SYSDATE
1495 p_user , -- last_updated_by ,
1496 P_user , -- created_by ,
1497 sysdate , -- creation_date ,
1498 p_login ,
1499 billable_flag
1500 from GMS_AWARD_DISTRIBUTIONS
1501 where expenditure_item_id = p_exp_item_id
1502 and document_type = 'EXP'
1503 and adl_status = 'A'
1504 and adl_line_num = 1 ;
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 -- The following procedure call is added for Bug 4290147
1508 GMS_ERROR_PKG.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
1509 x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_AWARDS_DIST_PKG :copy_exp_adls :Exp Item Id :'||p_backout_item_id,
1510 x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
1511 x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
1512 x_err_code => l_err_code, x_err_buff => l_err_buf ) ;
1513 x_status := SQLCODE ;
1514 END copy_exp_adls ;
1515
1516 end GMS_AWARDS_DIST_PKG; -- ================== END OF GMS_AWARDS_DIST_PKG ======================