[Home] [Help]
PACKAGE BODY: APPS.PA_COSTING
Source
1 PACKAGE BODY PA_COSTING AS
2 /* $Header: PAXCOSTB.pls 120.9 2007/02/06 12:12:00 rshaik ship $ */
3
4 -- ========================================================================
5 -- PROCEDURE ReverseCdl
6 -- ========================================================================
7
8 PROCEDURE ReverseCdl( X_expenditure_item_id IN NUMBER
9 , X_billable_flag IN VARCHAR2
10 , X_amount IN NUMBER DEFAULT NULL
11 , X_quantity IN NUMBER DEFAULT NULL
12 , X_burdened_cost IN NUMBER DEFAULT NULL
13 , X_dr_ccid IN NUMBER DEFAULT NULL
14 , X_cr_ccid IN NUMBER DEFAULT NULL
15 , X_tr_source_accounted IN VARCHAR2 DEFAULT NULL
16 , X_line_type IN VARCHAR2
17 , X_user IN NUMBER
18 , X_denom_currency_code IN VARCHAR2
19 , X_denom_raw_cost IN NUMBER
20 , X_denom_burden_cost IN NUMBER
21 , X_acct_currency_code IN VARCHAR2
22 , X_acct_rate_date IN DATE
23 , X_acct_rate_type IN VARCHAR2
24 , X_acct_exchange_rate IN NUMBER
25 , X_acct_raw_cost IN NUMBER
26 , X_acct_burdened_cost IN NUMBER
27 , X_project_currency_code IN VARCHAR2
28 , X_project_rate_date IN DATE
29 , X_project_rate_type IN VARCHAR2
30 , X_project_exchange_rate IN NUMBER
31 , P_Projfunc_currency_code IN VARCHAR2 default null
32 , P_Projfunc_cost_rate_date IN DATE default null
33 , P_Projfunc_cost_rate_type IN VARCHAR2 default null
34 , P_Projfunc_cost_exchange_rate IN NUMBER default null
35 , P_project_raw_cost IN NUMBER default null
36 , P_project_burdened_cost IN NUMBER default null
37 , P_Work_Type_Id IN NUMBER default null
38 , X_err_code IN OUT NOCOPY NUMBER
39 , X_err_stage IN OUT NOCOPY VARCHAR2
40 , X_err_stack IN OUT NOCOPY VARCHAR2
41 , p_mode IN VARCHAR2 default 'COSTING'
42 , X_line_num IN NUMBER DEFAULT NULL ) -- Bug 4374769 : A new parameter X_line_num is added.
43 IS
44
45 -- This procedure can have X_amount, X_quantity, X_burdened_cost, X_dr_ccid, X_cr_ccid parameters
46 -- NULL. If it is NULL then the original values will be used while creating the new CDL.
47 -- X_billable_flag will be used when called from adjustments. X_tr_source_accounted says if it
48 -- is an accounted for transaction
49
50 trx_source VARCHAR2(30);
51 gl_acct_flag VARCHAR2(1) ;
52 cdl_line_num NUMBER ;
53 max_cdl_line_num NUMBER ;
54 p_expenditure_item_id NUMBER ;
55 p_line_num NUMBER ;
56 p_transfer_status_code VARCHAR2(30) ;
57 p_amount NUMBER;
58 p_quantity NUMBER;
59 p_billable_flag VARCHAR2(1);
60 p_request_id NUMBER ;
61 p_program_application_id NUMBER ;
62 p_program_id NUMBER ;
63 p_program_update_date DATE ;
64 p_expenditure_item_date DATE ;
65 p_pa_date DATE ;
66 p_recvr_pa_date DATE ; /**CBGA**/
67 p_dr_ccid NUMBER ;
68 p_gl_date DATE ;
69 p_transferred_date DATE ;
70 p_transfer_rejection_reason VARCHAR2(250);
71 p_batch_name VARCHAR2(30) ;
72 p_accumulated_flag VARCHAR2(1) ;
73 p_resource_accumulated_flag VARCHAR2(1) ;
74 p_reversed_flag VARCHAR2(1) ;
75 p_line_num_reversed NUMBER ;
76 p_sys_reference1 VARCHAR2(30) ;
77 p_sys_reference2 VARCHAR2(30) ;
78 p_sys_reference3 VARCHAR2(30) ;
79 p_cr_ccid NUMBER;
80 p_ind_compiled_set_id NUMBER;
81 p_line_type VARCHAR2(1) ;
82 p_burdened_cost NUMBER ;
83 p_denom_currency_code VARCHAR2(15);
84 p_denom_raw_cost NUMBER;
85 p_denom_burden_cost NUMBER;
86 p_acct_currency_code VARCHAR2(15);
87 p_acct_rate_date DATE;
88 p_acct_rate_type VARCHAR2(30);
89 p_acct_exchange_rate NUMBER;
90 p_acct_raw_cost NUMBER;
91 p_acct_burdened_cost NUMBER;
92 p_project_currency_code VARCHAR2(15);
93 p_project_rate_date DATE;
94 p_project_rate_type VARCHAR2(30);
95 p_project_exchange_rate NUMBER;
96 p_project_id NUMBER;
97 p_task_id NUMBER;
98 old_stack VARCHAR2(2000);
99
100 -- Start EPP Changes
101 p_pa_period_name VARCHAR2(15);
102 p_gl_period_name VARCHAR2(15);
103 p_recvr_gl_date DATE;
104 p_recvr_gl_period_name VARCHAR2(15);
105 p_recvr_pa_period_name VARCHAR2(15);
106 -- End EPP Changes
107
108 --Start PA-I Changes
109 l_Projfunc_currency_code VARCHAR2(15);
110 l_Projfunc_cost_rate_date DATE;
111 l_Projfunc_cost_rate_type VARCHAR2(30);
112 l_Projfunc_cost_exchange_rate NUMBER;
113 l_project_raw_cost NUMBER;
114 l_project_burdened_cost NUMBER;
115 l_Work_Type_Id NUMBER;
116 -- End PA-I Changes
117
118 -- AP Discounts
119 p_sys_reference4 VARCHAR2(30) ;
120 p_sys_reference5 NUMBER ;
121
122 /* Bug 4374769 : The l_transfer_status_code and l_line_type variables are used to set the transfer_status_code to 'G' and line_type to 'I'
123 for the reversing and new cdls created because of PJI Summarization. These variables have their default values as NULL.*/
124
125 l_transfer_status_code VARCHAR2(30) DEFAULT NULL ;
126 l_line_type VARCHAR2(1) DEFAULT NULL ;
127 l_si_assets_addition_flag varchar2(1) default NULL ;
128
129 BEGIN
130 /* Selects the row for which a negative CDL is to be created
131 Project Summarization changes : Pick up project_id, task_id and pass them to the createnewcdl procedure */
132
133 x_err_stage := 'Get the max CDL for the exp item id' ;
134
135 /* Bug 4374769 : The where condition in the following select statement that selects the row for which a negative CDL is to be created is modified
136 such that when the p_mode is 'INTERFACE' it selects the line_number, that is passed to the ReverseCdl procedure through
137 the parameter X_line_num, for reversing. */
138
139
140 SELECT max(cdl.line_num),
141 cdl.transfer_status_code,
142 cdl.amount,
143 cdl.quantity,
144 cdl.request_id,
145 cdl.billable_flag,
146 cdl.program_application_id,
147 cdl.program_id,
148 cdl.program_update_date,
149 cdl.pa_date,
150 cdl.recvr_pa_date, /**CBGA**/
151 cdl.dr_code_combination_id,
152 cdl.gl_date,
153 cdl.transferred_date,
154 cdl.transfer_rejection_reason,
155 cdl.accumulated_flag,
156 cdl.resource_accumulated_flag,
157 cdl.cr_code_combination_id,
158 cdl.ind_compiled_set_id,
159 cdl.line_type,
160 NVL(cdl.burdened_cost,0) + nvl(cdl.projfunc_burdened_change,0) burdened_cost,
161 cdl.system_reference1,
162 cdl.system_reference2,
163 cdl.system_reference3,
164 cdl.denom_currency_code,
165 cdl.denom_raw_cost,
166 nvl(cdl.denom_burdened_cost,0) + nvl(cdl.denom_burdened_change,0) denom_burdened_cost,
167 cdl.acct_currency_code,
168 cdl.acct_rate_date,
169 cdl.acct_rate_type,
170 cdl.acct_exchange_rate,
171 cdl.acct_raw_cost,
172 nvl(cdl.acct_burdened_cost,0) + Nvl(cdl.acct_burdened_change,0) acct_burdened_cost,
173 cdl.project_currency_code,
174 cdl.project_rate_date,
175 cdl.project_rate_type,
176 cdl.project_exchange_rate,
177 cdl.project_id,
178 cdl.task_id,
179 cdl.pa_period_name,
180 cdl.gl_period_name,
181 cdl.recvr_pa_period_name,
182 cdl.recvr_gl_period_name,
183 cdl.recvr_gl_date,
184 cdl.projfunc_currency_code,
185 cdl.projfunc_cost_rate_type,
186 cdl.projfunc_cost_rate_date,
187 cdl.projfunc_cost_exchange_rate,
188 cdl.project_raw_cost,
189 nvl(cdl.project_burdened_cost,0) + nvl(cdl.project_burdened_change,0) project_burdened_cost,
190 cdl.work_type_id,
191 cdl.system_reference4,
192 cdl.system_reference5,
193 decode(cdl.si_assets_addition_flag, 'Y','T', 'O','T', 'R', 'T',cdl.si_assets_addition_flag)
194 INTO
195 max_cdl_line_num ,
196 p_transfer_status_code ,
197 p_amount ,
198 p_quantity ,
199 p_request_id ,
200 p_billable_flag ,
201 p_program_application_id ,
202 p_program_id ,
203 p_program_update_date ,
204 p_pa_date ,
205 p_recvr_pa_date , /**CBGA**/
206 p_dr_ccid ,
207 p_gl_date ,
208 p_transferred_date ,
209 p_transfer_rejection_reason ,
210 p_accumulated_flag ,
211 p_resource_accumulated_flag ,
212 p_cr_ccid ,
213 p_ind_compiled_set_id ,
214 p_line_type ,
215 p_burdened_cost ,
216 p_sys_reference1 ,
217 p_sys_reference2 ,
218 p_sys_reference3 ,
219 p_denom_currency_code ,
220 p_denom_raw_cost ,
221 p_denom_burden_cost ,
222 p_acct_currency_code ,
223 p_acct_rate_date ,
224 p_acct_rate_type ,
225 p_acct_exchange_rate ,
226 p_acct_raw_cost ,
227 p_acct_burdened_cost ,
228 p_project_currency_code ,
229 p_project_rate_date ,
230 p_project_rate_type ,
231 p_project_exchange_rate ,
232 p_project_id ,
233 p_task_id
234 , p_pa_period_name
235 , p_gl_period_name
236 , p_recvr_pa_period_name
237 , p_recvr_gl_period_name
238 , p_recvr_gl_date
239 , l_projfunc_currency_code
240 , l_projfunc_cost_rate_type
241 , l_projfunc_cost_rate_date
242 , l_projfunc_cost_exchange_rate
243 , l_project_raw_cost
244 , l_project_burdened_cost
245 , l_work_type_id
246 , p_sys_reference4
247 , p_sys_reference5
248 , l_si_assets_addition_flag
249 FROM
250 -- pa_cost_distribution_lines cdl -- 12i MOAC changes
251 pa_cost_distribution_lines_All cdl
252 WHERE cdl.expenditure_item_id = X_expenditure_item_id
253 AND DECODE(p_mode,'INTERFACE',NULL,cdl.line_num_reversed) IS NULL -- Bug 4374769
254 AND DECODE(p_mode,'INTERFACE',NULL,cdl.reversed_flag) IS NULL -- Bug 4374769
255 AND cdl.line_type = X_line_type
256 AND cdl.line_num = DECODE ( p_mode, 'INTERFACE', X_line_num, cdl.line_num) -- Bug 4374769
257 /* Added this Not exists Clause for the bug 5509019 */
258 AND NOT EXISTS (select 1 from pa_cost_distribution_lines_all cdl1
259 where cdl1.expenditure_item_id = cdl.expenditure_item_id
260 and cdl1.line_num_reversed = cdl.line_num
261 and cdl1.transfer_status_code = 'G'
262 and cdl1.line_type = 'I'
263 and cdl1.pji_summarized_flag = 'N'
264 and p_mode = 'INTERFACE')
265 GROUP BY cdl.transfer_status_code,
266 cdl.amount,
267 cdl.quantity,
268 cdl.request_id,
269 cdl.billable_flag,
270 cdl.program_application_id,
271 cdl.program_id,
272 cdl.program_update_date,
273 cdl.pa_date,
274 cdl.recvr_pa_date, /**CBGA**/
275 cdl.dr_code_combination_id,
276 cdl.gl_date,
277 cdl.transferred_date,
278 cdl.transfer_rejection_reason,
279 cdl.accumulated_flag,
280 cdl.resource_accumulated_flag,
281 cdl.cr_code_combination_id,
282 cdl.ind_compiled_set_id,
283 cdl.line_type,
284 nvl(cdl.burdened_cost,0) + nvl(cdl.projfunc_burdened_change,0),
285 cdl.system_reference1,
286 cdl.system_reference2,
287 cdl.system_reference3,
288 cdl.denom_currency_code,
289 cdl.denom_raw_cost,
290 NVL(cdl.denom_burdened_cost,0) + nvl(cdl.denom_burdened_change,0),
291 cdl.acct_currency_code,
292 cdl.acct_rate_date,
293 cdl.acct_rate_type,
294 cdl.acct_exchange_rate,
295 cdl.acct_raw_cost,
296 NVL(cdl.acct_burdened_cost,0) + nvl(cdl.acct_burdened_change,0),
297 cdl.project_currency_code,
298 cdl.project_rate_date,
299 cdl.project_rate_type,
300 cdl.project_exchange_rate,
301 cdl.project_id,
302 cdl.task_id,
303 cdl.pa_period_name,
304 cdl.gl_period_name,
305 cdl.recvr_pa_period_name,
309 cdl.projfunc_cost_rate_type,
306 cdl.recvr_gl_period_name,
307 cdl.recvr_gl_date,
308 cdl.projfunc_currency_code,
310 cdl.projfunc_cost_rate_date,
311 cdl.projfunc_cost_exchange_rate,
312 cdl.project_raw_cost,
313 NVL(cdl.project_burdened_cost,0) + nvl(cdl.project_burdened_change,0),
314 cdl.work_type_id ,
315 cdl.system_reference4,
316 cdl.system_reference5,
317 decode(cdl.si_assets_addition_flag, 'Y','T', 'O','T', 'R', 'T',cdl.si_assets_addition_flag) ;
318
319 /* Update the reverse flag of the CDL */
320
321 x_err_stage := 'Updating the reverse flag' ;
322 x_err_stack := x_err_stack||'-> Update the reverse flag';
323
324 /* Bug 4374769 : If p_mode is 'INTERFACE' i.e if the reversing and new cdls are created because of PJI summarization then the reversed_flag is
325 kept as NULL on the original cdl. */
326
327 If p_mode <> 'INTERFACE' Then
328 UPDATE pa_cost_distribution_lines_all
329 SET reversed_flag = 'Y'
330 WHERE expenditure_item_id = X_expenditure_item_id
331 AND line_num = max_cdl_line_num ;
332 end if;
333
334 /* Create the new reversing CDL */
335
336 /* bug 2891527 */
337 If p_mode = 'VIADJUST' Then
338 p_transfer_status_code := 'G';
339 end if;
340 /* bug 2891527 */
341
342 /* Bug 5561542 - Start */
343 If p_mode = 'WORK_TYP_ADJ' Then
344 p_transfer_status_code := 'P';
345 end if;
346 /* Bug 5561542 - End */
347
348 x_err_stage := 'Create reverse CDL' ;
349 x_err_stack := x_err_stack||'-> Create reverse CDL';
350
351 /* Bug 4374769 : If p_mode is 'INTERFACE' i.e if the reversing and new cdls are created because of PJI summarization , then the
352 transfer_status_code is set to 'G' and line_type to 'I' for the reversing and new cdls. */
353
354 If P_mode = 'INTERFACE' Then
355 l_transfer_status_code := 'G';
356 l_line_type := 'I';
357 End If;
358
359 Pa_Costing.CreateNewCdl(
360 X_expenditure_item_id => X_expenditure_item_id
361 , X_amount => -p_amount
362 , X_dr_ccid => p_dr_ccid
363 , X_cr_ccid => p_cr_ccid
364 , X_transfer_status_code => NVL(l_transfer_status_code, p_transfer_status_code) -- Bug 4374769
365 , X_quantity => -p_quantity
366 , X_billable_flag => p_billable_flag
367 , X_request_id => p_request_id
368 , X_program_application_id => p_program_application_id
369 , x_program_id => p_program_id
370 , x_program_update_date => p_program_update_date
371 , X_pa_date => p_pa_date
372 , X_recvr_pa_date => p_recvr_pa_date /**CBGA**/
373 , X_gl_date => p_gl_date
374 , X_transferred_date => p_transferred_date
375 , X_transfer_rejection_reason => p_transfer_rejection_reason
376 , X_line_type => NVL(l_line_type,p_line_type) -- Bug 4374769
377 , X_ind_compiled_set_id => p_ind_compiled_set_id
378 , X_burdened_cost => -p_burdened_cost
379 , X_line_num_reversed => max_cdl_line_num
380 , X_reverse_flag => NULL /* Bug 3668005 :Modified from 'Y' to NULL */
381 , X_user => X_user
382 , X_err_code => X_err_code
383 , X_err_stage => X_err_stage
384 , X_err_stack => X_err_stack
385 , X_project_id => p_project_id
386 , X_task_id => p_task_id
387 , X_cdlsr1 => p_sys_reference1
388 , X_cdlsr2 => p_sys_reference2
389 , X_cdlsr3 => p_sys_reference3
390 , X_denom_currency_code => p_denom_currency_code
391 , X_denom_raw_cost => -p_denom_raw_cost
392 , X_denom_burden_cost => -p_denom_burden_cost
393 , X_acct_currency_code => p_acct_currency_code
394 , X_acct_rate_date => p_acct_rate_date
395 , X_acct_rate_type => p_acct_rate_type
396 , X_acct_exchange_rate => p_acct_exchange_rate
397 , X_acct_raw_cost => -p_acct_raw_cost
398 , X_acct_burdened_cost => -p_acct_burdened_cost
399 , X_project_currency_code => p_project_currency_code
400 , X_project_rate_date => p_project_rate_date
401 , X_project_rate_type => p_project_rate_type
402 , X_project_exchange_rate => p_project_exchange_rate
403 , P_PaPeriodName => P_Pa_Period_Name
404 , P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
405 , P_GlPeriodName => P_Gl_Period_Name
406 , P_RecvrGlDate => P_Recvr_Gl_Date
407 , P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
408 , P_Projfunc_currency_code => l_Projfunc_currency_code
409 , P_Projfunc_cost_rate_date => l_Projfunc_cost_rate_date
410 , P_Projfunc_cost_rate_type => l_Projfunc_cost_rate_type
411 , P_Projfunc_cost_exchange_rate => l_Projfunc_cost_exchange_rate
415 , p_mode => p_mode
412 , P_Project_Raw_Cost => -l_Project_Raw_Cost
413 , P_Project_Burdened_Cost => -l_Project_Burdened_Cost
414 , P_Work_Type_Id => l_Work_Type_Id
416 , p_cdlsr4 => p_sys_reference4
417 , p_si_assets_addition_flag => l_si_assets_addition_flag
418 , p_cdlsr5 => p_sys_reference5 );
419
420 /* Assign the new values */
421
422 if x_amount is not null then
423 p_amount := x_amount ;
424 end if;
425
426 if x_dr_ccid is not null then
427 p_dr_ccid := x_dr_ccid ;
428 end if;
429
430 if x_cr_ccid is not null then
431 p_cr_ccid := x_cr_ccid ;
432 end if;
433
434 if nvl(x_tr_source_accounted, 'N') = 'N' then
435 p_transfer_status_code := 'P' ;
436 end if ;
437
438 if x_quantity is not null then
439 p_quantity := x_quantity ;
440 end if;
441
442 if x_burdened_cost is not null then
443 p_burdened_cost := x_burdened_cost ;
444 end if;
445
446 if x_denom_raw_cost is not null then
447 p_denom_raw_cost := x_denom_raw_cost;
448 end if;
449
450 if x_denom_burden_cost is not null then
451 p_denom_burden_cost := x_denom_burden_cost;
452 end if;
453
454 if x_acct_raw_cost is not null then
455 p_acct_raw_cost := x_acct_raw_cost;
456 end if;
457
458 if x_acct_burdened_cost is not null then
459 p_acct_burdened_cost := x_acct_burdened_cost;
460 end if;
461
462 if p_project_raw_cost is not null then
463 l_project_raw_cost := p_project_raw_cost;
464 end if;
465
466 if p_project_burdened_cost is not null then
467 l_project_burdened_cost := p_project_burdened_cost;
468 end if;
469
470 if p_work_type_id is not null then
471 l_work_type_id := p_work_type_id;
472 end if;
473
474 /** added this condition as the transaction adjustment api passes null for
475 * for this column for the work type adjustments
476 **/
477 If X_billable_flag is NOT NULL then
478 p_billable_flag := X_billable_flag;
479 End if;
480
481 /* bug 2891527 */
482 If p_mode = 'VIADJUST' Then
483 select cost_ind_compiled_set_id
484 into p_ind_compiled_set_id
485 from pa_expenditure_items_all
486 where expenditure_item_id = X_expenditure_item_id;
487 end if;
488 /* bug 2891527 */
489
490 /* Create a new CDL */
491 x_err_stage := 'Creating a new CDL';
492 x_err_stack := x_err_stack||'-> Create new CDL';
493
494 IF l_si_assets_addition_flag = 'N' THEN
495 l_si_assets_addition_flag := 'T' ;
496 END IF ;
497
498 Pa_Costing.CreateNewCdl(
499 X_expenditure_item_id => X_expenditure_item_id
500 , X_amount => p_amount
501 , X_dr_ccid => p_dr_ccid
502 , X_cr_ccid => p_cr_ccid
503 , X_transfer_status_code => NVL(l_transfer_status_code,p_transfer_status_code) -- Bug 4374769
504 , X_quantity => p_quantity
505 , X_billable_flag => p_billable_flag /** changed x_bill to p_bil **/
506 , X_request_id => p_request_id
507 , X_program_application_id => p_program_application_id
508 , x_program_id => p_program_id
509 , x_program_update_date => p_program_update_date
510 , X_pa_date => p_pa_date
511 , X_recvr_pa_date => p_recvr_pa_date /**CBGA**/
512 , X_gl_date => p_gl_date
513 , X_transferred_date => p_transferred_date
514 , X_transfer_rejection_reason => p_transfer_rejection_reason
515 , X_line_type => NVL(l_line_type,p_line_type) -- Bug 4374769
516 , X_ind_compiled_set_id => p_ind_compiled_set_id
517 , X_burdened_cost => p_burdened_cost
518 , X_line_num_reversed => NULL
519 , X_reverse_flag => NULL
520 , X_user => X_user
521 , X_err_code => X_err_code
522 , X_err_stage => X_err_stage
523 , X_err_stack => X_err_stack
524 , X_project_id => p_project_id
525 , X_task_id => p_task_id
526 , X_cdlsr1 => p_sys_reference1
527 , X_cdlsr2 => p_sys_reference2
528 , X_cdlsr3 => p_sys_reference3
529 , X_denom_currency_code => p_denom_currency_code
530 , X_denom_raw_cost => p_denom_raw_cost
531 , X_denom_burden_cost => p_denom_burden_cost
532 , X_acct_currency_code => p_acct_currency_code
533 , X_acct_rate_date => p_acct_rate_date
534 , X_acct_rate_type => p_acct_rate_type
535 , X_acct_exchange_rate => p_acct_exchange_rate
536 , X_acct_raw_cost => p_acct_raw_cost
537 , X_acct_burdened_cost => p_acct_burdened_cost
538 , X_project_currency_code => p_project_currency_code
539 , X_project_rate_date => p_project_rate_date
543 , P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
540 , X_project_rate_type => p_project_rate_type
541 , X_project_exchange_rate => p_project_exchange_rate
542 , P_PaPeriodName => P_Pa_Period_Name
544 , P_GlPeriodName => P_Gl_Period_Name
545 , P_RecvrGlDate => P_Recvr_Gl_Date
546 , P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
547 , P_Projfunc_currency_code => l_Projfunc_currency_code
548 , P_Projfunc_cost_rate_date => l_Projfunc_cost_rate_date
549 , P_Projfunc_cost_rate_type => l_Projfunc_cost_rate_type
550 , P_Projfunc_cost_exchange_rate => l_Projfunc_cost_exchange_rate
551 , P_Project_Raw_Cost => l_Project_Raw_Cost
552 , P_Project_Burdened_Cost => l_Project_Burdened_Cost
553 , P_Work_Type_Id => l_Work_Type_Id
554 , p_mode => p_mode
555 , p_cdlsr4 => p_sys_reference4
556 , p_si_assets_addition_flag => l_si_assets_addition_flag
557 , p_cdlsr5 => p_sys_reference5 ) ; -- Bug 5561542
558
559 x_err_stack := old_stack ;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 X_err_code := SQLCODE;
564 RAISE;
565
566 END ReverseCdl;
567
568
569 -- ========================================================================
570 -- PROCEDURE CreateNewCdl due to bug 666884. this procedure handle 3 system refs.
571 -- ========================================================================
572
573 PROCEDURE CreateNewCdl( X_expenditure_item_id IN NUMBER
574 , X_amount IN NUMBER
575 , X_dr_ccid IN NUMBER
576 , X_cr_ccid IN NUMBER
577 , X_transfer_status_code IN VARCHAR2
578 , X_quantity IN NUMBER
579 , X_billable_flag IN VARCHAR2
580 , X_request_id IN NUMBER
581 , X_program_application_id IN NUMBER
582 , x_program_id IN NUMBER
583 , x_program_update_date IN DATE
584 , X_pa_date IN DATE
585 , X_recvr_pa_date IN DATE /**CBGA**/
586 , X_gl_date IN DATE
587 , X_transferred_date IN DATE
588 , X_transfer_rejection_reason IN VARCHAR2
589 , X_line_type IN VARCHAR2
590 , X_ind_compiled_set_id IN NUMBER
591 , X_burdened_cost IN NUMBER
592 , X_line_num_reversed IN NUMBER
593 , X_reverse_flag IN VARCHAR2
594 , X_user IN NUMBER
595 , X_err_code IN OUT NOCOPY NUMBER
596 , X_err_stage IN OUT NOCOPY VARCHAR2
597 , X_err_stack IN OUT NOCOPY VARCHAR2
598 , X_project_id IN NUMBER
599 , X_task_id IN NUMBER
600 , X_cdlsr1 IN VARCHAR2 default null
601 , X_cdlsr2 IN VARCHAR2 default null
602 , X_cdlsr3 IN VARCHAR2 default null
603 , X_denom_currency_code IN VARCHAR2 default null
604 , X_denom_raw_cost IN NUMBER default null
605 , X_denom_burden_cost IN NUMBER default null
606 , X_acct_currency_code IN VARCHAR2 default null
607 , X_acct_rate_date IN DATE default null
608 , X_acct_rate_type IN VARCHAR2 default null
609 , X_acct_exchange_rate IN NUMBER default null
610 , X_acct_raw_cost IN NUMBER default null
611 , X_acct_burdened_cost IN NUMBER default null
612 , X_project_currency_code IN VARCHAR2 default null
613 , X_project_rate_date IN DATE default null
614 , X_project_rate_type IN VARCHAR2 default null
615 , X_project_exchange_rate IN NUMBER default null
616 , P_PaPeriodName IN Varchar2 default null
617 , P_RecvrPaPeriodName IN Varchar2 default null
618 , P_GlPeriodName IN Varchar2 default null
619 , P_RecvrGlDate IN DATE default null
620 , P_RecvrGlPeriodName IN Varchar2 default null
621 , P_Projfunc_currency_code IN VARCHAR2 default null
622 , P_Projfunc_cost_rate_date IN DATE default null
623 , P_Projfunc_cost_rate_type IN VARCHAR2 default null
624 , P_Projfunc_cost_exchange_rate IN NUMBER default null
625 , P_project_raw_cost IN NUMBER default null
629 , p_cdlsr4 IN VARCHAR2 default null
626 , P_project_burdened_cost IN NUMBER default null
627 , P_Work_Type_Id IN NUMBER default null
628 , p_mode IN VARCHAR2 default 'COSTING'
630 , p_si_assets_addition_flag IN VARCHAR2 default NULL
631 , p_cdlsr5 IN NUMBER default NULL
632 , P_Parent_Line_Num IN NUMBER DEFAULT NULL)
633 IS
634
635 new_cdl_line_num NUMBER ;
636 old_stack VARCHAR2(2000);
637
638 /* bug 3149022 */
639 l_ei_date DATE := NULL;
640 l_exp_id NUMBER := NULL;
641 l_sys_link_function VARCHAR2(3) := NULL;
642 l_org_id pa_expenditure_items.org_id%type := NULL;
643 l_recvr_org_id pa_expenditure_items.org_id%type := NULL;
644 l_sob_id NUMBER := NULL;
645 l_recvr_sob_id NUMBER := NULL;
646 l_pa_date DATE := NULL;
647 l_pa_period_name VARCHAR2(15) := NULL;
648 l_gl_date DATE := NULL;
649 l_gl_period_name VARCHAR2(15) := NULL;
650 l_recvr_pa_date DATE := NULL;
651 l_recvr_pa_period_name VARCHAR2(15) := NULL;
652 l_recvr_gl_date DATE := NULL;
653 l_recvr_gl_period_name VARCHAR2(15) := NULL;
654 /* l_err_code NUMBER; bug fix: 3258043 */
655 l_err_code VARCHAR2(100) := NULL;
656 l_err_stage NUMBER := NULL;
657 l_status NUMBER := NULL;
658 /* bug 3149022 */
659
660 BEGIN
661 -- Initialize the error stack
662 pa_cc_utils.set_curr_function('PA_COSTING.CreateNewCdl');
663 /* Bug 3149022 */
664
665 SELECT ITEMS.expenditure_item_date
666 ,ITEMS.org_id
667 ,NVL(ITEMS.recvr_org_id , ITEMS.ORG_ID)
668 ,ITEMS.system_linkage_function
669 ,ITEMS.expenditure_id
670 INTO l_ei_date
671 ,l_org_id
672 ,l_recvr_org_id
673 ,l_sys_link_function
674 ,l_exp_id
675 -- FROM pa_expenditure_items ITEMS -- 12i MOAC changes
676 FROM pa_expenditure_items_All ITEMS
677 WHERE ITEMS.expenditure_item_id = X_expenditure_item_id;
678
679 SELECT imp1.set_of_books_id, imp2.set_of_books_id
680 INTO l_sob_id, l_recvr_sob_id
681 FROM pa_implementations_all imp1, pa_implementations_all imp2
682 -- start 12i MOAC changes
683 -- WHERE nvl(imp1.org_id,-99) = nvl(l_org_id,-99)
684 -- AND nvl(imp2.org_id,-99) = nvl(l_recvr_org_id,-99);
685 WHERE imp1.org_id = l_org_id
686 AND imp2.org_id = l_recvr_org_id;
687 -- end 12i MOAC changes
688
689
690 -- call get_period_information only for the following cases, bug 3357936
691 IF (p_mode = 'RECLASS' OR p_mode = 'WORK_TYP_ADJ' OR p_mode = 'TRXADJUST')
692 THEN
693 PA_UTILS2.get_period_information(
694 p_expenditure_item_date => l_ei_date
695 ,p_expenditure_id => l_exp_id
696 ,p_system_linkage_function => l_sys_link_function
697 ,p_line_type => x_line_type
698 ,p_prvdr_raw_pa_date => X_pa_date
699 ,p_recvr_raw_pa_date => X_recvr_pa_date
700 ,p_prvdr_raw_gl_date => X_gl_date
701 ,p_recvr_raw_gl_date => P_RecvrGlDate
702 ,p_prvdr_org_id => l_org_id
703 ,p_recvr_org_id => l_recvr_org_id
704 ,p_prvdr_sob_id => l_sob_id
705 ,p_recvr_sob_id => l_recvr_sob_id
706 ,p_calling_module => 'CDL'
707 ,x_prvdr_pa_date => l_pa_date
708 ,x_prvdr_pa_period_name => l_pa_period_name
709 ,x_prvdr_gl_date => l_gl_date
710 ,x_prvdr_gl_period_name => l_gl_period_name
711 ,x_recvr_pa_date => l_recvr_pa_date
712 ,x_recvr_pa_period_name => l_recvr_pa_period_name
713 ,x_recvr_gl_date => l_recvr_gl_date
714 ,x_recvr_gl_period_name => l_recvr_gl_period_name
715 ,x_error_code => l_err_code
716 ,x_return_status => l_status
717 ,x_error_stage => l_err_stage );
718
719 /****** Bug 3668005 : Will be executed only during interface for NEW CDLs. ******/
720 ElsIf (p_mode='INTERFACE' and X_line_num_reversed IS NULL and X_reverse_flag is NULL) Then
721 Select
722 pa_utils2.get_prvdr_gl_date(
723 X_gl_date
724 ,decode(EI.system_linkage_function
725 , 'VI', 200
726 , 101) -- GL Application ID = 101 and AP Application Id = 200 .
727 ,l_sob_id) gl_date,
728 pa_utils2.get_gl_period_name (
729 pa_utils2.get_prvdr_gl_date(
730 X_gl_date
731 ,decode(EI.system_linkage_function
732 , 'VI', 200
733 , 101)
734 ,l_sob_id)
735 ,EI.org_id) gl_period_name,
736 pa_utils2.get_recvr_gl_date(
737 P_RecvrGlDate
738 ,decode(EI.system_linkage_function
742 pa_utils2.get_gl_period_name (
739 , 'VI', 200
740 , 101)
741 ,l_recvr_sob_id) recvr_gl_date,
743 pa_utils2.get_recvr_gl_date(
744 P_RecvrGlDate
745 ,decode(EI.system_linkage_function
746 , 'VI', 200
747 , 101)
748 ,l_recvr_sob_id)
749 ,nvl(EI.recvr_org_id,EI.org_id)) recvr_gl_period_name
750 Into l_gl_date,
751 l_gl_period_name,
752 l_recvr_gl_date,
753 l_recvr_gl_period_name
754 -- From PA_Expenditure_items EI, -- 12i MOAC changes
755 From PA_Expenditure_items_All EI /* Bug 5353670 / PQE Bug 5248665
756 PA_Implementations_All IMP */
757 Where EI.expenditure_item_id = X_expenditure_item_id;
758 /* AND NVL(EI.recvr_org_id,EI.ORG_ID) = IMP.org_id; Bug 5353670 / PQE Bug 5248665 */
759
760 END IF;
761
762 /****** Bug 3668005 Ends ******/
763
764 /* Bug 3149022 */
765
766 old_stack := X_err_stack ;
767 /* Get the maximum line number for the CDL */
768
769 X_err_stack := X_err_stack ||'->Max_line_num' ;
770 X_err_stage := '-> Get new CDL line num';
771
772 SELECT max(cdl.line_num)
773 INTO new_cdl_line_num
774 FROM pa_cost_distribution_lines cdl
775 WHERE cdl.expenditure_item_id = X_expenditure_item_id ;
776
777 /* Create the new reversing CDL */
778
779 X_err_stack := X_err_stack ||'->Max_line_num' ;
780 X_err_stage := '-> Insert row in CDL table';
781 pa_cc_utils.log_message('Gl Accounted Flag['||PA_TRANSACTIONS.GL_ACCOUNTED_FLAG||
782 ']l_gl_date['||l_gl_date||']x_gl_date['||x_gl_date||']');
783
784 -- Insert into pa_cost_distribution_lines
785 Insert into pa_cost_distribution_lines_All
786 ( line_num
787 , parent_line_num
788 , expenditure_item_id
789 , amount
790 , dr_code_combination_id
791 , cr_code_combination_id
792 , line_type
793 , ind_compiled_set_id
794 , burdened_cost
795 , transfer_status_code
796 , quantity
797 , billable_flag
798 , creation_date
799 , created_by
800 , request_id
801 , program_application_id
802 , program_id
803 , program_update_date
804 , pa_date
805 , recvr_pa_date /**CBGA**/
806 , gl_date
807 , transferred_date
808 , transfer_rejection_reason
809 , accumulated_flag
810 , resource_accumulated_flag
811 , reversed_flag
812 , line_num_reversed
813 , burden_sum_source_run_id
814 , system_reference1
815 , system_reference2
816 , system_reference3
817 , denom_currency_code
818 , denom_raw_cost
819 , denom_burdened_cost
820 , acct_currency_code
821 , acct_rate_date
822 , acct_rate_type
823 , acct_exchange_rate
824 , acct_raw_cost
825 , acct_burdened_cost
826 , project_currency_code
827 , project_rate_date
828 , project_rate_type
829 , project_exchange_rate
830 , project_id
831 , task_id
832 , Pa_Period_Name
833 , Recvr_Pa_Period_Name
834 , Gl_Period_Name
835 , Recvr_Gl_Date
836 , Recvr_Gl_Period_Name
837 , Projfunc_currency_code
838 , Projfunc_cost_rate_date
839 , Projfunc_cost_rate_type
840 , Projfunc_cost_exchange_rate
841 , Project_raw_cost
842 , Project_burdened_cost
843 , Work_type_id
844 , util_summarized_flag
845 , system_reference4
846 , system_reference5
847 , pji_summarized_flag /* Bug 3668005 :Added */
848 , org_id -- 12i MOAC changes
849 , si_assets_addition_flag
850 )
851 Values
852 ( nvl(new_cdl_line_num, 0) + 1
853 , P_Parent_Line_Num
854 , X_expenditure_item_id
855 , x_amount
856 , x_dr_ccid
857 , x_cr_ccid
858 , x_line_type
859 , x_ind_compiled_set_id
860 , x_burdened_cost
861 , decode(p_mode,'TRXADJUST','G',x_transfer_status_code)
862 , x_quantity
863 , x_billable_flag
864 , sysdate
865 , X_user
866 , x_request_id
867 , x_program_application_id
868 , x_program_id
869 , x_program_update_date
873 , X_transferred_date
870 , NVL(l_pa_date, X_pa_date) /* X_pa_date ** bug 3357936 */
871 , NVL(l_recvr_pa_date, X_recvr_pa_date) /* X_recvr_pa_date ** bug 3357936 */ /**CBGA**/
872 , NVL(l_gl_date, X_gl_date) /* X_gl_date ** bug 3357936 */
874 , x_transfer_rejection_reason
875 , decode(p_mode,'TRXADJUST','Y','N') -- Accumalated flag
876 , decode(p_mode,'TRXADJUST','Y','N') -- Resource accumalated flag
877 , x_reverse_flag
878 , X_line_num_reversed
879 , -9999
880 , X_cdlsr1
881 , X_cdlsr2
882 , X_cdlsr3
883 , X_denom_currency_code
884 , X_denom_raw_cost
885 , X_denom_burden_cost
886 , X_acct_currency_code
887 , X_acct_rate_date
888 , X_acct_rate_type
889 , X_acct_exchange_rate
890 , X_acct_raw_cost
891 , X_acct_burdened_cost
892 , X_project_currency_code
893 , X_project_rate_date
894 , X_project_rate_type
895 , X_project_exchange_rate
896 , X_project_id
897 , X_task_id
898 , NVL(l_pa_period_name, P_PaPeriodName) /* P_PaPeriodName ** bug 3357936 */
899 , NVL(l_recvr_pa_period_name, P_RecvrPaPeriodName) /* P_RecvrPaPeriodName ** bug 3357936 */
900 /* Bug fix: 3258043 added decode to populate original GL info for accounted imported trans */
901 , NVL(l_gl_period_name, P_GlPeriodName)
902 /* P_GlPeriodName ** bug 3357936 */
903 , NVL(l_recvr_gl_date, P_RecvrGlDate)
904 /* P_RecvrGlDate ** bug 3357936 */
905 , NVL(l_recvr_gl_period_name, P_RecvrGlPeriodName)
906 /* P_RecvrGlPeriodName ** bug 3357936 */
907 , P_Projfunc_currency_code
908 , P_Projfunc_cost_rate_date
909 , P_Projfunc_cost_rate_type
910 , P_Projfunc_cost_exchange_rate
911 , P_Project_raw_cost
912 , P_Project_burdened_cost
913 , P_Work_type_id
914 , decode(p_mode,'TRXADJUST','N','N')
915 , p_cdlsr4
916 , p_cdlsr5
917 , 'N' /* Bug 3668005 :Added */
918 , l_org_id -- 12i MOAC changes
919 , p_si_assets_addition_flag
920 ) ;
921
922 X_err_stack := old_stack ;
923
924 pa_cc_utils.reset_curr_function;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 X_err_code := SQLCODE;
929 RAISE;
930 END CreateNewCdl ;
931
932 -- ========================================================================
933 -- PROCEDURE CreateExternalCdl
934 -- ========================================================================
935
936 PROCEDURE CreateExternalCdl( X_expenditure_item_id IN NUMBER
937 , X_ei_date IN DATE
938 , X_amount IN NUMBER
939 , X_dr_ccid IN NUMBER
940 , X_cr_ccid IN NUMBER
941 , X_transfer_status_code IN VARCHAR2
942 , X_quantity IN NUMBER
943 , X_billable_flag IN VARCHAR2
944 , X_request_id IN NUMBER
945 , X_program_application_id IN NUMBER
946 , x_program_id IN NUMBER
947 , x_program_update_date IN DATE
948 , X_pa_date IN DATE
949 , X_recvr_pa_date IN DATE /**CBGA**/
950 , X_gl_date IN DATE
951 , X_transferred_date IN DATE
952 , X_transfer_rejection_reason IN VARCHAR2
953 , X_line_type IN VARCHAR2
954 , X_ind_compiled_set_id IN NUMBER
955 , X_burdened_cost IN NUMBER
956 , X_user IN NUMBER
957 , X_project_id IN NUMBER
958 , X_task_id IN NUMBER
959 , X_cdlsr1 IN VARCHAR2 default null
960 , X_cdlsr2 IN VARCHAR2 default null
961 , X_cdlsr3 IN VARCHAR2 default null
962 , X_denom_currency_code IN VARCHAR2 default null
963 , X_denom_raw_cost IN NUMBER default null
964 , X_denom_burden_cost IN NUMBER default null
965 , X_acct_currency_code IN VARCHAR2 default null
966 , X_acct_rate_date IN DATE default null
967 , X_acct_rate_type IN VARCHAR2 default null
968 , X_acct_exchange_rate IN NUMBER default null
969 , X_acct_raw_cost IN NUMBER default null
970 , X_acct_burdened_cost IN NUMBER default null
971 , X_project_currency_code IN VARCHAR2 default null
972 , X_project_rate_date IN DATE default null
973 , X_project_rate_type IN VARCHAR2 default null
974 , X_project_exchange_rate IN NUMBER default null
978 , P_RecvrGlDate IN DATE default null
975 , P_PaPeriodName IN Varchar2 default null
976 , P_RecvrPaPeriodName IN Varchar2 default null
977 , P_GlPeriodName IN Varchar2 default null
979 , P_RecvrGlPeriodName IN Varchar2 default null
980 , P_Projfunc_currency_code IN VARCHAR2 default null
981 , P_Projfunc_cost_rate_date IN DATE default null
982 , P_Projfunc_cost_rate_type IN VARCHAR2 default null
983 , P_Projfunc_cost_exchange_rate IN NUMBER default null
984 , P_project_raw_cost IN NUMBER default null
985 , P_project_burdened_cost IN NUMBER default null
986 , P_Work_Type_Id IN NUMBER default null
987 , p_cdlsr4 IN VARCHAR2 default null
988 , p_si_assets_addition_flag in VARCHAR2 default null
989 , p_cdlsr5 IN NUMBER default null
990 , X_err_code IN OUT NOCOPY NUMBER
991 , X_err_stage IN OUT NOCOPY VARCHAR2
992 , X_err_stack IN OUT NOCOPY VARCHAR2 )
993 IS
994 BEGIN
995
996 /** CBGA **/
997 if (x_pa_date IS NULL) then
998 x_err_code := -20009 ;
999 raise_application_error( -20009, 'INVALID_PA_DATE') ;
1000 end if;
1001
1002 if (x_recvr_pa_date IS NULL) then
1003 x_err_code := -20010 ;
1004 raise_application_error( -20010, 'INVALID_RECVR_PA_DATE') ;
1005 end if;
1006 -- Insert into PA_Cost_Distribution_lines
1007
1008 x_err_stage := 'Insert into PA_Cost_Disctribution_Lines';
1009 Pa_Costing.CreateNewCdl (
1010 X_expenditure_item_id => x_expenditure_item_id
1011 , X_amount => x_amount
1012 , X_dr_ccid => x_dr_ccid
1013 , X_cr_ccid => x_cr_ccid
1014 , X_transfer_status_code => x_transfer_status_code
1015 , X_quantity => x_quantity
1016 , X_billable_flag => x_billable_flag
1017 , X_request_id => x_request_id
1018 , X_program_application_id => x_program_application_id
1019 , x_program_id => x_program_id
1020 , x_program_update_date => x_program_update_date
1021 , X_pa_date => x_pa_date
1022 , X_recvr_pa_date => x_recvr_pa_date /**CBGA**/
1023 , X_gl_date => x_gl_date
1024 , X_transferred_date => x_transferred_date
1025 , X_transfer_rejection_reason => x_transfer_rejection_reason
1026 , X_line_type => x_line_type
1027 , X_ind_compiled_set_id => x_ind_compiled_set_id
1028 , X_burdened_cost => x_burdened_cost
1029 , X_line_num_reversed => NULL
1030 , X_reverse_flag => NULL
1031 , X_user => x_user
1032 , X_err_code => x_err_code
1033 , X_err_stage => x_err_stage
1034 , X_err_stack => x_err_stack
1035 , X_project_id => x_project_id
1036 , X_task_id => x_task_id
1037 , X_cdlsr1 => X_cdlsr1
1038 , X_cdlsr2 => X_cdlsr2
1039 , X_cdlsr3 => X_cdlsr3
1040 , X_denom_currency_code => X_denom_currency_code
1041 , X_denom_raw_cost => X_denom_raw_cost
1042 , X_denom_burden_cost => X_denom_burden_cost
1043 , X_acct_currency_code => X_acct_currency_code
1044 , X_acct_rate_date => X_acct_rate_date
1045 , X_acct_rate_type => X_acct_rate_type
1046 , X_acct_exchange_rate => X_acct_exchange_rate
1047 , X_acct_raw_cost => X_acct_raw_cost
1048 , X_acct_burdened_cost => X_acct_burdened_cost
1049 , X_project_currency_code => X_project_currency_code
1050 , X_project_rate_date => X_project_rate_date
1051 , X_project_rate_type => X_project_rate_type
1052 , X_project_exchange_rate => X_project_exchange_rate
1053 , P_PaPeriodName => P_PaPeriodName
1054 , P_RecvrPaPeriodName => P_RecvrPaPeriodName
1055 , P_GlPeriodName => P_GlPeriodName
1056 , P_RecvrGlDate => P_RecvrGlDate
1057 , P_RecvrGlPeriodName => P_RecvrGlPeriodName
1058 , P_Projfunc_currency_code => P_Projfunc_currency_code
1059 , P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
1060 , P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
1061 , P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
1062 , P_Project_Raw_Cost => P_Project_Raw_Cost
1063 , P_Project_Burdened_Cost => P_Project_Burdened_Cost
1064 , P_Work_Type_Id => P_Work_Type_Id
1065 , p_cdlsr4 => p_cdlsr4
1066 , p_si_assets_addition_flag => p_si_assets_addition_flag
1067 , p_cdlsr5 => p_cdlsr5) ;
1068
1069 if (x_err_code <> 0) then
1070 return;
1071 end if;
1072
1073 EXCEPTION
1077
1074 When OTHERS then
1075 RAISE ;
1076 END CreateExternalCdl ;
1078 FUNCTION Is_Accounted(X_Transaction_Source IN VARCHAR2)
1079 RETURN VARCHAR2
1080 IS
1081 accounted_flag PA_TRANSACTION_SOURCES.Gl_Accounted_Flag%TYPE;
1082 BEGIN
1083 IF X_Transaction_Source IS NOT NULL THEN
1084 SELECT TS.Gl_Accounted_Flag
1085 INTO accounted_flag
1086 FROM PA_TRANSACTION_SOURCES TS
1087 WHERE TS.Transaction_Source = X_Transaction_source;
1088 ELSE
1089 accounted_flag := 'N';
1090 END IF;
1091 RETURN(accounted_flag);
1092 END Is_Accounted;
1093
1094 /*
1095 New Procedure added under Project Summarization changes.
1096 This procedure is used to create a reverse cdl for a reverse ei generated during the
1097 adjustment of an accounted and imported transaction.
1098 x_exp_item_id : id of the original transaction
1099 x_backout_id : id of the reverse transaction generated
1100 */
1101
1102 PROCEDURE CreateReverseCdl ( X_exp_item_id IN NUMBER,
1103 X_backout_id IN NUMBER,
1104 X_user IN NUMBER,
1105 X_status OUT NOCOPY NUMBER)
1106 IS
1107 p_amount pa_cost_distribution_lines.amount%TYPE;
1108 p_dr_ccid pa_cost_distribution_lines.dr_code_combination_id%TYPE;
1109 p_cr_ccid pa_cost_distribution_lines.cr_code_combination_id%TYPE;
1110 p_transfer_status_code pa_cost_distribution_lines.transfer_status_code%TYPE;
1111 p_quantity pa_cost_distribution_lines.quantity%TYPE;
1112 p_billable_flag pa_cost_distribution_lines.billable_flag%TYPE;
1113 p_request_id pa_cost_distribution_lines.request_id%TYPE;
1114 p_program_application_id pa_cost_distribution_lines.program_application_id%TYPE;
1115 p_program_id pa_cost_distribution_lines.program_id%TYPE;
1116 p_program_update_date pa_cost_distribution_lines.program_update_date%TYPE;
1117 p_pa_date pa_cost_distribution_lines.pa_date%TYPE;
1118 p_recvr_pa_date pa_cost_distribution_lines.pa_date%TYPE; /**CBGA**/
1119 p_gl_date pa_cost_distribution_lines.gl_date%TYPE;
1120 p_transferred_date pa_cost_distribution_lines.transferred_date%TYPE;
1121 p_transfer_rejection_reason pa_cost_distribution_lines.transfer_rejection_reason%TYPE;
1122 p_line_type pa_cost_distribution_lines.line_type%TYPE;
1123 p_ind_complied_set_id pa_cost_distribution_lines.ind_compiled_set_id%TYPE;
1124 p_burdened_cost pa_cost_distribution_lines.burdened_cost%TYPE;
1125 p_line_num_reversed pa_cost_distribution_lines.line_num_reversed%TYPE;
1126 p_reversed_flag pa_cost_distribution_lines.reversed_flag%TYPE;
1127 p_cdlsr1 pa_cost_distribution_lines.system_reference1%TYPE;
1128 p_cdlsr2 pa_cost_distribution_lines.system_reference2%TYPE;
1129 p_cdlsr3 pa_cost_distribution_lines.system_reference3%TYPE;
1130 p_denom_currency_code pa_cost_distribution_lines.denom_currency_code%TYPE;
1131 p_denom_raw_cost pa_cost_distribution_lines.denom_raw_cost%TYPE;
1132 p_denom_burdened_cost pa_cost_distribution_lines.denom_burdened_cost%TYPE;
1133 p_acct_currency_code pa_cost_distribution_lines.acct_currency_code%TYPE;
1134 p_acct_rate_date pa_cost_distribution_lines.acct_rate_date%TYPE;
1135 p_acct_rate_type pa_cost_distribution_lines.acct_rate_type%TYPE;
1136 p_acct_exchange_rate pa_cost_distribution_lines.acct_exchange_rate%TYPE;
1137 p_acct_raw_cost pa_cost_distribution_lines.acct_raw_cost%TYPE;
1138 p_acct_burdened_cost pa_cost_distribution_lines.acct_burdened_cost%TYPE;
1139 p_project_currency_code pa_cost_distribution_lines.project_currency_code%TYPE;
1140 p_project_rate_date pa_cost_distribution_lines.project_rate_date%TYPE;
1141 p_project_rate_type pa_cost_distribution_lines.project_rate_type%TYPE;
1142 p_project_exchange_rate pa_cost_distribution_lines.project_exchange_rate%TYPE;
1143 p_project_id pa_cost_distribution_lines.project_id%TYPE;
1144 p_task_id pa_cost_distribution_lines.task_id%TYPE;
1145 p_parent_adjusted_id pa_expenditure_items.adjusted_expenditure_item_id%TYPE;
1146 p_parent_transferred_id pa_expenditure_items.transferred_from_exp_item_id%TYPE;
1147 p_gl_accounted_flag pa_transaction_sources.gl_accounted_flag%TYPE;
1148 p_transaction_source pa_transaction_sources.transaction_source%TYPE;
1149
1150 l_si_assets_addition_flag pa_cost_distribution_lines.si_assets_addition_flag%TYPE ;
1151 p_err_code NUMBER;
1152 p_err_stage VARCHAR2(1000);
1153 p_err_stack VARCHAR2(1000);
1154 e_cdl_error EXCEPTION;
1155
1156 -- Start EPP Changes
1157 p_pa_period_name VARCHAR2(15);
1158 p_gl_period_name VARCHAR2(15);
1159 p_recvr_gl_date DATE;
1160 p_recvr_gl_period_name VARCHAR2(15);
1161 p_recvr_pa_period_name VARCHAR2(15);
1162 -- End EPP Changes
1163
1164 -- Start Project Currency/ EI Attribute Changes
1165 p_projfunc_currency_code VARCHAR2(15);
1166 p_projfunc_cost_rate_type VARCHAR2(30);
1167 p_projfunc_cost_rate_date date;
1171 p_project_burdened_cost NUMBER;
1168 p_projfunc_cost_exchange_rate NUMBER;
1169 p_work_type_id NUMBER;
1170 p_project_raw_cost NUMBER;
1172 -- End Project Currency/ EI Attribute Changes
1173
1174 -- AP Discounts
1175 p_cdlsr4 pa_cost_distribution_lines.system_reference4%TYPE;
1176 p_cdlsr5 pa_cost_distribution_lines.system_reference5%TYPE;
1177 /* bug#2361495 */
1178 l_pa_date DATE ;
1179 l_recvr_pa_date DATE ;
1180 l_ei_date DATE ;
1181 l_org_id pa_expenditure_items.org_id%type;
1182 /* bug#2361495 */
1183 /* bug2378505 */
1184 actual_cdl_line_num pa_cost_distribution_lines.line_num%TYPE;
1185 /* bug2378505 */
1186 /* bug 2661921*/
1187 l_recvr_org_id pa_expenditure_items.org_id%type;
1188 l_gl_date DATE;
1189 l_recvr_gl_date DATE;
1190 l_exp_id NUMBER;
1191 l_sob_id NUMBER;
1192 l_recvr_sob_id NUMBER;
1193 l_sys_link_function VARCHAR2(3);
1194 l_err_stage NUMBER;
1195 l_status NUMBER;
1196 l_err_code VARCHAR2(100);
1197 /* bug 2661921*/
1198
1199 BEGIN
1200
1201 SELECT ITEMS.adjusted_expenditure_item_id,
1202 ITEMS.transferred_from_exp_item_id,
1203 TRN.gl_accounted_flag,
1204 TRN.transaction_source
1205 ,ITEMS.expenditure_item_date
1206 ,ITEMS.org_id
1207 /* bug 2661921*/
1208 ,NVL(ITEMS.recvr_org_id,ITEMS.ORG_ID)
1209 ,ITEMS.system_linkage_function
1210 ,ITEMS.expenditure_id
1211 /* bug 2661921*/
1212
1213 INTO p_parent_adjusted_id,
1214 p_parent_transferred_id,
1215 p_gl_accounted_flag,
1216 p_transaction_source
1217 ,l_ei_date
1218 ,l_org_id
1219 /* bug 2661921*/
1220 ,l_recvr_org_id
1221 ,l_sys_link_function
1222 ,l_exp_id
1223 /* bug 2661921*/
1224 -- FROM pa_expenditure_items ITEMS, -- 12i MOAC changes
1225 FROM pa_expenditure_items_All ITEMS,
1226 pa_transaction_sources TRN
1227 WHERE ITEMS.transaction_source = TRN.transaction_source
1228 AND ITEMS.expenditure_item_id = X_exp_item_id;
1229
1230 /* selecting set of books id for bug 2661921*/
1231 SELECT imp1.set_of_books_id, imp2.set_of_books_id
1232 INTO l_sob_id, l_recvr_sob_id
1233 FROM pa_implementations_all imp1, pa_implementations_all imp2
1234 -- start 12i MOAC changes
1235 -- WHERE nvl(imp1.org_id,-99) = nvl(l_org_id,-99)
1236 -- AND nvl(imp2.org_id,-99) = nvl(l_recvr_org_id,-99);
1237 WHERE imp1.org_id = l_org_id
1238 AND imp2.org_id = l_recvr_org_id;
1239 -- end 12i MOAC changes
1240 /* selected set of books id bug 2661921*/
1241
1242
1243 IF p_parent_adjusted_id IS NULL AND
1244 p_parent_transferred_id IS NULL AND
1245 p_gl_accounted_flag = 'Y' AND
1246 /* Bug 4610677 - reversing line should not be created for all gl accounted VI and ER sources */
1247 l_sys_link_function not in ('VI','ER')
1248 /* Commenting the following for Bug 4610677
1249 p_transaction_source NOT IN ( 'AP EXPENSE','AP INVOICE',
1250 'INTERCOMPANY_AP_INVOICES',
1251 'INTERPROJECT_AP_INVOICES',
1252 /* Bug 4684328 - Added rest of the Purchasing and Payables transaction sources */
1253 /* Commenting the following for Bug 4610677
1254 'AP VARIANCE', 'AP NRTAX', 'AP DISCOUNTS',
1255 'PO RECEIPT', 'PO RECEIPT NRTAX',
1256 'PO RECEIPT NRTAX PRICE ADJ', 'PO RECEIPT PRICE ADJ')*/
1257 THEN
1258 /* Added for Bug 2378505 The reversing line should take the details from the latest CDL
1259 and not from line_num = 1 as there may be some attributes which could differ which in
1260 this case is the billable_flag */
1261
1262 SELECT max(cdl.line_num)
1263 INTO actual_cdl_line_num
1264 FROM pa_cost_distribution_lines cdl
1265 WHERE cdl.expenditure_item_id = X_exp_item_id and cdl.line_type = 'R';
1266
1267 /* Addition for bug 2378505 ends */
1268
1269 SELECT amount
1270 , dr_code_combination_id
1271 , cr_code_combination_id
1272 , transfer_status_code
1273 , quantity
1274 , billable_flag
1275 , request_id
1276 , program_application_id
1277 , program_id
1278 , program_update_date
1279 , pa_date
1280 , gl_date
1281 , transferred_date
1282 , transfer_rejection_reason
1283 , line_type
1284 , ind_compiled_set_id
1285 , nvl(burdened_cost,0) + nvl(projfunc_burdened_change,0)
1286 , line_num_reversed
1287 , reversed_flag
1288 , system_reference1
1289 , system_reference2
1290 , system_reference3
1291 , denom_currency_code
1292 , denom_raw_cost
1293 , NVL(denom_burdened_cost,0) + nvl(denom_burdened_change,0)
1294 , acct_currency_code
1295 , acct_rate_date
1299 , NVL(acct_burdened_cost,0) + nvl(acct_burdened_change,0)
1296 , acct_rate_type
1297 , acct_exchange_rate
1298 , acct_raw_cost
1300 , project_currency_code
1301 , project_rate_date
1302 , project_rate_type
1303 , project_exchange_rate
1304 , project_id
1305 , task_id,
1306 /* commenting out these columns as this information is obtained
1307 from pa_utils2.get_period_information for fix 2661921
1308 pa_period_name,
1309 gl_period_name,
1310 recvr_pa_period_name,
1311 recvr_gl_period_name, */
1312 recvr_gl_date
1313 , Projfunc_currency_code
1314 , Projfunc_cost_rate_date
1315 , Projfunc_cost_rate_type
1316 , Projfunc_cost_exchange_rate
1317 , Project_raw_cost
1318 , NVL(Project_burdened_cost,0) + nvl(project_burdened_change,0)
1319 , Work_type_id
1320 , system_reference4
1321 , system_reference5
1322 , decode(si_assets_addition_flag, 'R','T', 'O', 'T', 'Y', 'T', 'N', 'T',si_assets_addition_flag )
1323 INTO p_amount,
1324 p_dr_ccid,
1325 p_cr_ccid,
1326 p_transfer_status_code,
1327 p_quantity,
1328 p_billable_flag,
1329 p_request_id,
1330 p_program_application_id,
1331 p_program_id,
1332 p_program_update_date,
1333 p_pa_date,
1334 p_gl_date,
1335 p_transferred_date,
1336 p_transfer_rejection_reason,
1337 p_line_type,
1338 p_ind_complied_set_id,
1339 p_burdened_cost,
1340 p_line_num_reversed,
1341 p_reversed_flag,
1342 p_cdlsr1,
1343 p_cdlsr2,
1344 p_cdlsr3,
1345 p_denom_currency_code,
1346 p_denom_raw_cost,
1347 p_denom_burdened_cost,
1348 p_acct_currency_code,
1349 p_acct_rate_date,
1350 p_acct_rate_type,
1351 p_acct_exchange_rate,
1352 p_acct_raw_cost,
1353 p_acct_burdened_cost,
1354 p_project_currency_code,
1355 p_project_rate_date,
1356 p_project_rate_type,
1357 p_project_exchange_rate,
1358 p_project_id,
1359 p_task_id
1360 /* Commenting out the folliwng columns for 2661921
1361 , p_pa_period_name
1362 , p_gl_period_name
1363 , p_recvr_pa_period_name
1364 , p_recvr_gl_period_name */
1365 , p_recvr_gl_date
1366 , p_Projfunc_currency_code
1367 , p_Projfunc_cost_rate_date
1368 , p_Projfunc_cost_rate_type
1369 , p_Projfunc_cost_exchange_rate
1370 , p_Project_raw_cost
1371 , p_Project_burdened_cost
1372 , p_Work_type_id
1373 , p_cdlsr4
1374 , p_cdlsr5
1375 , l_si_assets_addition_flag
1376 -- FROM pa_cost_distribution_lines -- 12i MOAC changes
1377 FROM pa_cost_distribution_lines_All -- 12i MOAC changes
1378 WHERE expenditure_item_id = X_exp_item_id
1379 -- AND line_num = 1; -- commented for bug2378505
1380 AND line_num = actual_cdl_line_num; -- bug2378505
1381
1382 /* bug#2361495 */
1383 /* Commenting out the call to get_pa_date and get_recvr_pa_date
1384 for 2661921 as pa_date and recvr_pa_date will be obtained
1385 from PA_UTILS2.get_period_information
1386
1387 l_pa_date := PA_UTILS2.get_pa_date(p_ei_date => l_ei_date
1388 ,p_gl_date => NULL
1389 ,p_org_id => l_org_id );
1390
1391 l_recvr_pa_date := PA_UTILS2.get_recvr_pa_date(p_ei_date => l_ei_date
1392 ,p_gl_date => NULL
1393 ,p_org_id => l_org_id ); */
1394 /* bug#2361495 */
1395
1396 /* Getting pa and gl period information for 2661921 */
1397 PA_UTILS2.get_period_information(
1398 p_expenditure_item_date => l_ei_date
1399 ,p_expenditure_id => l_exp_id
1400 ,p_system_linkage_function => l_sys_link_function
1401 ,p_line_type => p_line_type
1402 ,p_prvdr_raw_pa_date => p_pa_date
1403 ,p_recvr_raw_pa_date => p_recvr_pa_date
1404 ,p_prvdr_raw_gl_date => p_gl_date
1405 ,p_recvr_raw_gl_date => p_recvr_gl_date
1406 ,p_prvdr_org_id => l_org_id
1407 ,p_recvr_org_id => l_recvr_org_id
1408 ,p_prvdr_sob_id => l_sob_id
1409 ,p_recvr_sob_id => l_recvr_sob_id
1410 ,p_calling_module => 'CDL'
1411 ,x_prvdr_pa_date => l_pa_date
1412 ,x_prvdr_pa_period_name => p_pa_period_name
1413 ,x_prvdr_gl_date => l_gl_date
1414 ,x_prvdr_gl_period_name => p_gl_period_name
1415 ,x_recvr_pa_date => l_recvr_pa_date
1416 ,x_recvr_pa_period_name => p_recvr_pa_period_name
1417 ,x_recvr_gl_date => l_recvr_gl_date
1418 ,x_recvr_gl_period_name => p_recvr_gl_period_name
1422 /* pa and gl period information fetched for 2661921*/
1419 ,x_error_code => l_err_code
1420 ,x_return_status => l_status
1421 ,x_error_stage => l_err_stage );
1423
1424 /* 2661921 */
1425 IF p_err_code IS NOT NULL THEN
1426 raise e_cdl_error;
1427 END IF;
1428 /* 2661921 */
1429
1430 PA_COSTING.CREATENEWCDL(
1431 X_expenditure_item_id => X_backout_id
1432 , X_amount => -p_amount
1433 , X_dr_ccid => p_dr_ccid
1434 , X_cr_ccid => p_cr_ccid
1435 , X_transfer_status_code => 'P' /* bug 2361495 p_transfer_status_code */
1436 , X_quantity => -p_quantity
1437 , X_billable_flag => p_billable_flag
1438 , X_request_id => p_request_id
1439 , X_program_application_id => p_program_application_id
1440 , x_program_id => p_program_id
1441 , x_program_update_date => p_program_update_date
1442 , X_pa_date => l_pa_date /* bug 2361495 p_pa_date */
1443 , X_recvr_pa_date => l_recvr_pa_date /** bug 2361495 p_recvr_pa_date CBGA **/
1444 , X_gl_date => l_gl_date /* bug 2661921 p_gl_date */
1445 , X_transferred_date => NULL /* bug 2361495 p_transferred_date */
1446 , X_transfer_rejection_reason => NULL /* bug 2361495 p_transfer_rejection_reason */
1447 , X_line_type => p_line_type
1448 , X_ind_compiled_set_id => p_ind_complied_set_id
1449 , X_burdened_cost => -p_burdened_cost
1450 , X_line_num_reversed => p_line_num_reversed
1451 , X_reverse_flag => p_reversed_flag
1452 , X_user => X_user
1453 , X_err_code => p_err_code
1454 , X_err_stage => p_err_stage
1455 , X_err_stack => p_err_stack
1456 , X_project_id => p_project_id
1457 , X_task_id => p_task_id
1458 , X_cdlsr1 => p_cdlsr1
1459 , X_cdlsr2 => p_cdlsr2
1460 , X_cdlsr3 => p_cdlsr3
1461 , X_denom_currency_code => p_denom_currency_code
1462 , X_denom_raw_cost => -p_denom_raw_cost
1463 , X_denom_burden_cost => -p_denom_burdened_cost
1464 , X_acct_currency_code => p_acct_currency_code
1465 , X_acct_rate_date => p_acct_rate_date
1466 , X_acct_rate_type => p_acct_rate_type
1467 , X_acct_exchange_rate => p_acct_exchange_rate
1468 , X_acct_raw_cost => -p_acct_raw_cost
1469 , X_acct_burdened_cost => -p_acct_burdened_cost
1470 , X_project_currency_code => p_project_currency_code
1471 , X_project_rate_date => p_project_rate_date
1472 , X_project_rate_type => p_project_rate_type
1473 , X_project_exchange_rate => p_project_exchange_rate
1474 , P_PaPeriodName => P_Pa_Period_Name
1475 , P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
1476 , P_GlPeriodName => P_Gl_Period_Name
1477 , P_RecvrGlDate => l_recvr_gl_date /* bug 2661921 P_Recvr_Gl_Date */
1478 , P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
1479 , P_Projfunc_currency_code => P_Projfunc_currency_code
1480 , P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
1481 , P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
1482 , P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
1483 , P_Project_Raw_Cost => -P_Project_Raw_Cost --Bug 3315099
1484 , P_Project_Burdened_Cost => -P_Project_Burdened_Cost --Bug 3315099
1485 , P_Work_Type_Id => P_Work_Type_Id
1486 , p_cdlsr4 => p_cdlsr4
1487 , p_si_assets_addition_flag => l_si_assets_addition_flag
1488 , p_cdlsr5 => p_cdlsr5
1489 , P_Parent_Line_Num => actual_cdl_line_num);
1490
1491 IF p_err_code IS NOT NULL THEN
1492 raise e_cdl_error;
1493 END IF;
1494
1495 UPDATE pa_expenditure_items
1496 SET cost_distributed_flag = 'Y'
1497 WHERE expenditure_item_id = X_backout_id;
1498
1499 END IF;
1500
1501 X_status := 0;
1502
1503 EXCEPTION
1504 WHEN NO_DATA_FOUND THEN
1505 NULL;
1506 WHEN e_cdl_error THEN
1507 X_status := p_err_code;
1508 WHEN OTHERS THEN
1509 X_status := SQLCODE;
1510 RAISE;
1511 END CreateReverseCdl;
1512
1513 END PA_COSTING ;