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