[Home] [Help]
PACKAGE BODY: APPS.PA_REVERSE_COSTED_LAB
Source
1 PACKAGE BODY PA_REVERSE_COSTED_LAB AS
2 /* $Header: PAXTREVB.pls 120.21 2011/08/26 08:47:45 jjgeorge noship $ */
3 -- ==================================================================================================================
4
5 -- Start of Comments
6 -- API Name : PAREVTXN
7 -- Type : Public
8 -- Pre-Reqs : None
9 -- Type : Procedure
10 -- Function : This procedure is used to pull Costed expenditure items to Reverse
11 -- based on selected parameters values through the Concurrent Program .
12 -- This is any entry point procedure for Prc:Reverse Costed Labor transactions Process.
13 --
14 --
15 -- Parameters:
16 --
17 -- IN
18 -- P_costing_method : Method used to calculate the cost .
19 -- Values are Standard Cost and Actual Cost
20 -- P_enable_accrue_flag_yn: Flag defined to enabling/Disabling Accrual Only Parameter
21 -- P_accrual_flag : Accrual only Paramter gets enabled when user selects
22 -- P_Enable_cost_set_flag : Flag Defined to Enable Costed set prameter
23 -- p_cost_set : Costed set
24 -- P_Enable_date : To Enable From and To date when user select method as Standard and actual with Yes
25 -- P_from_date : From date
26 -- P_to_date : To date
27 -- P_incur_by_org_id : Reverse the expenditures based on Organization
28 -- P_project_id : Reverse The Expenditures based on Project
29 -- P_person_id : Reverse The expenditures based on Employee
30 -- OUT
31 --=====================================================================================================================
32
33
34 PROCEDURE parevtxn (errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY VARCHAR2,
36 P_costing_method IN VARCHAR2,
37 P_enable_accrue_flag_yn IN VARCHAR2,
38 P_accrual_flag IN VARCHAR2,
39 P_Enable_cost_set_flag IN VARCHAR2,
40 p_cost_set IN NUMBER,
41 P_Enable_date IN VARCHAR2,
42 P_from_date IN VARCHAR2,
43 P_to_date IN VARCHAR2,
44 P_incur_by_org_id IN VARCHAR2,
45 P_project_id IN VARCHAR2,
46 P_person_id IN VARCHAR2
47 )
48 IS
49
50
51 l_to_date DATE;
52 l_from_date DATE;
53 xml_layout BOOLEAN;
54 req_id NUMBER;
55 l_status NUMBER;
56
57 BEGIN
58
59 G_DEBUG_MODE := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
60 G_err_stage := 'CHANGING VARCHAR2 TO DATE';
61 l_from_date := FND_CONC_DATE.STRING_TO_DATE(p_from_date); /*Changed fnd_date.canonical_to_date(p_from_date) to FND_CONC_DATE.STRING_TO_DATE(p_from_date)for bug:12611695 */
62 l_to_date := FND_CONC_DATE.STRING_TO_DATE(p_to_date); /*Changed fnd_date.canonical_to_date(p_to_date) to FND_CONC_DATE.STRING_TO_DATE(p_to_date) for bug:12611695 */
63 pa_debug.init_err_stack ('Reverse eis ');
64 pa_debug.set_process(
65 x_process => 'PLSQL',
66 x_debug_mode => G_DEBUG_MODE);
67
68 write_log (LOG,'---------------PARAMETERS----------- ');
69 write_log (LOG,'P_costing_method -> '||p_costing_method);
70 write_log (LOG,'P_accrual_flag -> '||p_accrual_flag);
71 write_log (LOG,'P_cost_set -> '||p_cost_set );
72 write_log (LOG,'P_from_date -> '||l_from_date);
73 write_log (LOG,'P_to_date -> '||l_to_date);
74 write_log (LOG,'P_incur_by_org_id-> '||p_incur_by_org_id);
75 write_log (LOG,'P_project_id -> '||p_project_id);
76 write_log (LOG,'P_person_id -> '||p_person_id );
77 write_log (LOG, '------------------------------------' );
78
79
80
81 If G_debug_mode = 'Y' Then
82 G_err_stage := 'Before calling Reverse_costed_trans';
83 write_log(LOG, G_err_stage);
84 End If;
85
86 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
87
88 Reverse_costed_trans (P_costing_method =>P_costing_method
89 ,P_Accrual_only_flag =>P_accrual_flag
90 ,P_cost_set =>p_cost_set
91 ,P_FROM_DATE =>l_from_date
92 ,P_TO_DATE =>l_to_date
93 ,P_inc_by_org_id =>P_incur_by_org_id
94 ,P_PROJECT_ID =>P_project_id
95 ,P_inc_by_person_id =>P_person_id
96 ,X_status =>l_status
97 );
98
99 write_log(LOG , 'Launching process to generate Audit Report');
100
101 /*Query the Org From Pa Implementation Options */
102 SELECT org_id
103 INTO G_ORG_ID
104 FROM pa_implementations;
105
106 FND_REQUEST.set_org_id(G_ORG_ID);
107
108 xml_layout := FND_REQUEST.ADD_LAYOUT('PA','PAXREVAUD','en','US','PDF');
109
110
111 req_id := FND_REQUEST.SUBMIT_REQUEST ( 'PA'
112 , 'PAXREVAUD'
113 , ''
114 , ''
115 , FALSE
116 ,P_costing_method
117 ,P_enable_accrue_flag_yn
118 ,P_accrual_flag
119 ,P_Enable_cost_set_flag
120 ,P_cost_set
121 ,P_Enable_date
122 ,P_from_date
123 ,P_to_date
124 ,P_incur_by_org_id
125 ,P_project_id
126 ,P_person_id
127 ,G_request_id
128 );
129
130 write_log (LOG,'Submitted Request Id' ||req_id );
131
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 RAISE;
136 END PAREVTXN;
137
138 -- ===========================================================================================
139
140 -- Start of Comments
141 -- API Name : REVERSE_COSTED_TRANS
142 -- Type : Public
143 -- Pre-Reqs : None
144 -- Type : Procedure
145 -- Function : This procedure is used to pull Costed expenditure items to Reverse
146 -- based on selected parameters values through the Concurrent Program .
147 --
148 --
149 --
150 -- Parameters:
151 --
152 -- IN
153 -- P_costing_method : Method used to calculate the cost .
154 -- Values are Standard Cost and Actual Cost
155 -- P_accrual_flag : Accrual only Paramter gets enabled when user selects
156 -- p_cost_set : Costed set
157 -- P_from_date : From date
158 -- P_to_date : To date
159 -- P_incur_by_org_id : Reverse the expenditures based on Organization
160 -- P_project_id : Reverse The Expenditures based on Project
161 -- P_person_id : Reverse The expenditures based on Employee
162 -- OUT
163 -- X_status
164 --=================================================================================================
165 PROCEDURE Reverse_costed_trans( P_costing_method IN VARCHAR2,
166 P_Accrual_only_flag IN VARCHAR2,
167 P_cost_set IN NUMBER,
168 P_from_date IN VARCHAR2,
169 P_to_date IN VARCHAR2,
170 P_inc_by_org_id IN VARCHAR2,
171 P_project_id IN VARCHAR2,
172 P_inc_by_person_id IN VARCHAR2,
173 X_status OUT NOCOPY NUMBER
174 ) IS
175
176 CURSOR c_expitems
177 IS
178 SELECT * FROM (
179 SELECT item.expenditure_item_id expenditure_item_id
180 ,item.cost_distributed_flag cost_distributed_flag
181 ,item.project_id project_id
182 ,item.system_linkage_function system_linkage_function
183 ,exp.incurred_by_person_id incurred_by_person_id
184 ,item.interface_run_id interface_run_id
185 ,'Y' reverse_eligibile_flag
186 ,NULL reverse_error_code
187 FROM pa_expenditure_items item,
188 pa_expenditures exp
189 WHERE exp.expenditure_id = item.expenditure_id
190 AND exp.expenditure_status_code = 'APPROVED'
191 AND (item.cost_distributed_flag = 'Y' OR (item.cost_distributed_flag = 'N'
192 AND item.costing_method IS NOT NULL
193 AND item.adjustment_type IN('RECALC_RAW', 'COST AND REV RECALC','RECALC_BURDEN'))
194 )
195 AND item.system_linkage_function IN ('OT','ST')
196 AND NVL(item.net_zero_adjustment_flag,'N') <> 'Y'
197 AND item.costing_method=P_costing_method
198 AND NVL(item.payroll_accrual_flag,'N')=NVL(P_accrual_only_flag,'N')
199 AND (P_project_id IS NULL OR item.project_id =P_project_id )
200 AND NVL(item.override_to_organization_id,exp.incurred_by_organization_id ) = NVL(P_inc_by_org_id,exp.incurred_by_organization_id)
201 AND exp.incurred_by_person_id = NVL(P_inc_by_person_id,exp.incurred_by_person_id )
202 AND item.expenditure_item_date BETWEEN P_from_date AND P_to_date
203 UNION
204 SELECT
205 item.expenditure_item_id expenditure_item_id
206 ,item.cost_distributed_flag cost_distributed_flag
207 ,item.project_id project_id
208 ,item.system_linkage_function system_linkage_function
209 ,exp.incurred_by_person_id incurred_by_person_id
210 ,item.interface_run_id interface_run_id
211 ,uncosted_eis_exist(NVL(P_cost_set,item.interface_run_id), NVL(P_inc_by_person_id,exp.incurred_by_person_id)) reverse_eligibile_flag -- returns X if exists
212 ,NULL reverse_error_code
213 FROM pa_expenditure_items item,
214 pa_expenditures exp
215 WHERE exp.expenditure_id = item.expenditure_id
216 AND exp.expenditure_status_code = 'APPROVED'
217 AND item.system_linkage_function IN ('ST','OT','PJ','BTC')
218 AND NVL(item.net_zero_adjustment_flag,'N') <> 'Y'
219 AND NVL(item.payroll_accrual_flag,'N')='N'
220 AND item.interface_run_id is not null
221 AND item.interface_run_id=P_cost_set
222 AND NVL(item.override_to_organization_id,exp.incurred_by_organization_id )=NVL(P_inc_by_org_id,exp.incurred_by_organization_id)
223 AND exp.incurred_by_person_id =NVL(P_inc_by_person_id,exp.incurred_by_person_id )
224 )
225 ORDER BY incurred_by_person_id asc;
226
227
228 CURSOR c_adjustedeis (X_expid NUMBER)
229 IS
230 SELECT ei1.expenditure_item_id ,
231 ei1.adjusted_expenditure_item_id
232 FROM pa_expenditure_items ei1
233 WHERE ei1.adjusted_expenditure_item_id= X_expid
234 AND NVL(ei1.net_zero_adjustment_flag,'N')='Y'
235 AND ei1.cost_distributed_flag='N';
236
237 CURSOR c_uncosted_costset (x_empid NUMBER, x_costset_id NUMBER)
238 IS
239 SELECT ei.expenditure_item_id
240 FROM pa_expenditure_items ei,
241 pa_expenditures exp
242 WHERE ei.expenditure_id = exp.expenditure_id
243 AND ei.interface_run_id = x_costset_id
244 AND exp.incurred_by_person_id = x_empid
245 AND ei.cost_distributed_flag='N';
246
247 CURSOR c_block_backout (x_empid NUMBER, x_costset_id NUMBER) IS
248 SELECT EI1.EXPENDITURE_ITEM_ID
249 FROM PA_EXPENDITURE_ITEMS EI1,
250 PA_EXPENDITURES EXP1
251 WHERE EXP1.INCURRED_BY_PERSON_ID = X_EMPID
252 AND EI1.EXPENDITURE_ID = EXP1.EXPENDITURE_ID
253 AND EI1.INTERFACE_RUN_ID = x_costset_id
254 AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
255 AND EXISTS
256 (
257 SELECT EI2.EXPENDITURE_ITEM_ID
258 FROM PA_EXPENDITURE_ITEMS EI3,
259 PA_EXPENDITURE_ITEMS EI2,
260 PA_EXPENDITURES EXP2
261 WHERE EI3.interface_run_id = EI1.INTERFACE_RUN_ID
262 AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) =
263 SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1)
264 AND EXP2.INCURRED_BY_PERSON_ID = X_EMPID
265 AND EXP2.EXPENDITURE_ID = EI2.EXPENDITURE_ID
266 AND EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
267 AND EI2.COSTING_METHOD = 'ACTUAL'
268 AND EI2.PAYROLL_ACCRUAL_FLAG = 'N'
269 AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
270 AND EI2.INTERFACE_RUN_ID > EI1.INTERFACE_RUN_ID
271 UNION
272 /* adjustment is not by PAXREVTXN reversal process but from OTL */
273 SELECT EI4.EXPENDITURE_ITEM_ID
274 FROM PA_EXPENDITURE_ITEMS EI5 ,
275 PA_EXPENDITURE_ITEMS EI4 ,
276 PA_EXPENDITURES EXP4
277 WHERE EI5.interface_run_id = EI1.INTERFACE_RUN_ID
278 AND EXP4.INCURRED_BY_PERSON_ID = X_EMPID
279 AND EXP4.EXPENDITURE_ID = EI4.EXPENDITURE_ID
280 AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
281 AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
282 AND SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1) =
283 SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1)
284 AND NVL(EI4.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
285 AND EI4.INTERFACE_RUN_ID IS NULL
286 AND NOT EXISTS
287 (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPEND_ITEM_ADJ_ACTIVITIES ADJ
288 WHERE ADJ.EXPENDITURE_ITEM_ID = EI4.EXPENDITURE_ITEM_ID
289 AND ADJ.MODULE_CODE = 'PAXREVTXN'
290 AND EXCEPTION_ACTIVITY_CODE = 'REINITIATE EI TO DIST' )
291 ) ; --Added for Bug 12779047
292
293 /*Declaring the Global Varible for the Rever*/
294 l_Project_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
295 l_Cost_Dist_Flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
296 l_Exp_item_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
297 l_adj_exp_item_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
298 l_NewExp_item_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
299 l_SysLinkTab PA_PLSQL_DATATYPES.Char30TabTyp;
300 l_PersonIdTab PA_PLSQL_DATATYPES.IdTabTyp;
301 l_Interface_run_idTab PA_PLSQL_DATATYPES.IdTabTyp;
302 l_Reverse_eligibile_flag_Tab PA_PLSQL_DATATYPES.Char1TabTyp;
303 l_Reverse_error_code_Tab PA_PLSQL_DATATYPES.Char30TabTyp;
304 l_row_count NUMBER := 0;
305 l_temp_num_processed NUMBER := 500;
306 temp_status NUMBER DEFAULT NULL;
307 l_status VARCHAR2(10) := NULL;
308 l_result NUMBER;
309 l_temp_emp_id pa_expenditures.incurred_by_person_id%TYPE := -1;
310 l_Exp_item_id_tb2 PA_PLSQL_DATATYPES.IdTabTyp;
311 l_Exp_item_id_tb3 PA_PLSQL_DATATYPES.IdTabTyp; --Bug12779047
312 l_temp_interface_run_id pa_expenditure_items.interface_run_id%TYPE :=-1;
313 -- =======================================================================
314 -- Start of Comments
315 -- API Name : CLEAR_PLSQL_TABLES
316 -- Type : Public
317 -- Pre-Reqs : None
318 -- Type : Procedure
319 -- Function : Procedure is used to clear the pl/sql tables
320 -- =======================================================================
321 PROCEDURE clear_plsql_tables
322 IS
323 BEGIN
324 l_Project_id_tbl.DELETE;
325 l_Cost_Dist_Flag_tbl.DELETE;
326 l_Exp_item_id_tbl.DELETE;
327 l_adj_exp_item_id_tbl.DELETE;
328 l_NewExp_item_id_tbl.DELETE;
329 l_SysLinkTab.DELETE;
330 l_PersonIdTab.DELETE;
331 l_Interface_run_idTab.DELETE;
332 l_Reverse_eligibile_flag_Tab.DELETE;
333 l_Reverse_error_code_Tab.DELETE;
334 END clear_plsql_tables;
335
336 BEGIN
337
338 G_err_stage := ' Inside Reverse_costed_trans';
339 write_log(LOG, G_err_stage);
340
341 g_user_id := FND_GLOBAL.USER_ID();
342 g_login_id := FND_GLOBAL.CONC_LOGIN_ID();
343
344 OPEN c_expitems;
345 LOOP
346 clear_plsql_tables;
347 FETCH c_expitems
348 BULK COLLECT INTO l_Exp_item_id_tbl
349 ,l_Cost_Dist_Flag_tbl
350 ,l_Project_id_tbl
351 ,l_SysLinkTab
352 ,l_PersonIdTab
353 ,l_Interface_run_idTab
354 ,l_Reverse_eligibile_flag_Tab
355 ,l_Reverse_error_code_Tab
356 LIMIT l_temp_num_processed;
357 l_temp_num_processed := l_temp_num_processed + 1;
358 write_log (LOG,'No.of Exp Items picked up for reversal:=>' ||l_Exp_item_id_tbl.COUNT);
359 FOR i IN 1 .. l_Exp_item_id_tbl.COUNT
360 LOOP
361
362 IF G_debug_mode = 'Y'
363 THEN
364 write_log(LOG, 'Reject all the items where the project is Closed');
365 END IF;
366 IF l_Reverse_eligibile_flag_Tab(i) = 'Y' AND pa_project_stus_utils.is_project_closed(l_project_id_tbl(i)) ='Y'
367 THEN
368
369 IF G_debug_mode = 'Y'
370 THEN
371 write_log(LOG,'Project:'|| l_project_id_tbl(i)||'is Closed ');
372 END IF;
373 l_Reverse_eligibile_flag_Tab(i) := 'N';
374 pa_adjustments.InsAuditRec
375 ( l_Exp_item_id_tbl(i)
376 , 'PA_REVERSAL'
377 , 'PAXREVTXN'
378 , g_user_id
379 , g_login_id
380 , temp_status
381 , G_REQUEST_ID
382 , G_PROGRAM_ID
383 , G_PROG_APPL_ID
384 , SYSDATE
385 , 'PA_SUM_PROJECT_CLOSED'
386 );
387
388 pa_adjustments.CheckStatus(temp_status);
389
390
391 END IF ;
392
393 IF l_Interface_run_idTab(i) IS NOT NULL AND l_Reverse_eligibile_flag_Tab(i) = 'X'
394 THEN
395 IF G_debug_mode = 'Y'
396 THEN
397 write_log(LOG, 'Reject all the items if there exist any item which is uncosted for the costset and employee');
398 write_log(LOG,'Expenditure item_id:=>'||l_Exp_item_id_tbl(i));
399 write_log(LOG,'Project_id:=>'||l_project_id_tbl(i));
400 END IF;
401
402 IF l_temp_emp_id <> l_PersonIdTab(i) AND l_temp_interface_run_id <> l_Interface_run_idTab(i)
403 THEN
404
405 l_temp_emp_id := l_PersonIdTab(i);
406 l_temp_interface_run_id := l_Interface_run_idTab(i);
407 /*Start BUG:12611695 Added logic to insert the record into audit table in case of uncosted transactions exists */
408 FOR j IN 1 .. l_Exp_item_id_tbl.COUNT
409 LOOP
410 IF ((l_PersonIdTab(i) = l_PersonIdTab(j)) AND (l_Interface_run_idTab(i) = l_Interface_run_idTab(j)) AND l_Cost_Dist_Flag_tbl(j) = 'Y' )
411 THEN
412 l_Reverse_eligibile_flag_Tab(j) := 'N';
413 pa_adjustments.InsAuditRec( l_Exp_item_id_tbl(j)
414 , 'PA_REVERSAL'
415 , 'PAXREVTXN'
416 , g_user_id
417 , g_login_id
418 , temp_status
419 , G_REQUEST_ID
420 , G_PROGRAM_ID
421 , G_PROG_APPL_ID
422 , SYSDATE
423 , 'PA_EMP_COSTSET_REJECT'
424 );
425 pa_adjustments.CheckStatus(temp_status);
426 END IF;
427 END LOOP;
428
429 OPEN c_uncosted_costset( l_PersonIdTab(i),l_Interface_run_idTab(i));
430 FETCH c_uncosted_costset BULK COLLECT INTO l_Exp_item_id_tb2;
431 FOR i in 1 .. l_Exp_item_id_tb2.COUNT
432 LOOP
433 l_Reverse_eligibile_flag_Tab(i) :='N';
434 pa_adjustments.InsAuditRec( l_Exp_item_id_tb2(i) /*Added for bug:12611695 */
435 , 'PA_REVERSAL'
436 , 'PAXREVTXN'
437 , g_user_id
438 , g_login_id
439 , temp_status
440 , G_REQUEST_ID
441 , G_PROGRAM_ID
442 , G_PROG_APPL_ID
443 , SYSDATE
444 , 'PA_EMP_COSTSET_UNCOSTED' /*Added for bug:12611695 */
445 );
446 pa_adjustments.CheckStatus(temp_status);
447 END LOOP;
448 CLOSE c_uncosted_costset;
449
450 --Added for Bug 12779047
451 write_log(LOG,'Checking if there are any adjustments on the Payroll batch being rolled back');
452 OPEN c_block_backout( l_PersonIdTab(i),l_Interface_run_idTab(i));
453 FETCH c_block_backout BULK COLLECT INTO l_Exp_item_id_tb3;
454 FOR i in 1 .. l_Exp_item_id_tb3.COUNT
455 LOOP
456
457 write_log(LOG,'cannot roll back ei - ' || l_Exp_item_id_tb3(i) );
458 l_Reverse_eligibile_flag_Tab(i) :='N';
459
460 pa_adjustments.InsAuditRec( l_Exp_item_id_tb3(i)
461 , 'PA_REVERSAL'
462 , 'PAXREVTXN'
463 , g_user_id
464 , g_login_id
465 , temp_status
466 , G_REQUEST_ID
467 , G_PROGRAM_ID
468 , G_PROG_APPL_ID
469 , SYSDATE
470 , 'PA_COSTSET_ADJUSTED'
471 );
472 pa_adjustments.CheckStatus(temp_status);
473
474 END LOOP;
475 CLOSE c_block_backout;
476
477
478 END IF;
479 END IF;
480 /*END BUG:12611695 */
481 IF G_debug_mode = 'Y'
482 THEN
483 write_log(LOG, 'Reject the items if the transaction source doesnot allow adjustments otherwise proceed further with the logic');
484 write_log(LOG,'Expenditure item_id:=>'||l_Exp_item_id_tbl(i));
485 write_log(LOG,'Project_id:=>'||l_project_id_tbl(i));
486 END IF;
487
488 IF ( is_ei_reversable(l_Exp_item_id_tbl(i))='Y' AND l_Reverse_eligibile_flag_Tab(i)='Y' )
489 THEN
490
491 IF G_debug_mode = 'Y'
492 THEN
493 write_log(LOG,'l_Cost_Dist_Flag_tbl(i)=>'||l_Cost_Dist_Flag_tbl(i));
494 END IF;
495
496 IF l_Cost_Dist_Flag_tbl(i)='N'
497 THEN
498 G_err_stage := ' Update expenditure cost columns with cdl amount if CDF =N and marked for recalculation ,etc';
499 write_log(LOG, G_err_stage);
500 UPDATE pa_expenditure_items ei
501 SET ei.cost_distributed_flag='Y',
502 (ei.raw_cost ,
503 ei.Raw_Cost_Rate,
504 ei.burden_cost,
505 ei.burden_cost_rate,
506 ei.ACCT_raw_cost,
507 ei.acct_burdened_cost,
508 ei.denom_raw_cost,
509 ei.denom_burdened_cost,
510 ei.project_raw_cost,
511 ei.project_burdened_cost)
512 =(
513 SELECT cdl.amount,
514 (cdl.denom_raw_cost/DECODE(cdl.Quantity, NULL, 1,0, 1,cdl.Quantity))
515 ,cdl.burdened_cost
516 ,(cdl.denom_burdened_cost/DECODE(cdl.Quantity, NULL,1,0,1,cdl.Quantity))
517 ,cdl.acct_raw_cost
518 ,cdl.acct_burdened_cost
519 ,cdl.denom_raw_cost
520 ,cdl.denom_burdened_cost
521 ,cdl.project_raw_cost
522 ,cdl.project_burdened_cost
523 FROM pa_cost_distribution_lines cdl
524 WHERE cdl.expenditure_item_id=ei.expenditure_item_id
525 AND cdl.line_type='R'
526 AND NVL(cdl.reversed_flag,'N')='N'
527 AND cdl.line_num_reversed IS NULL)
528 WHERE ei.expenditure_item_id = l_Exp_item_id_tbl(i)
529 AND ei.Cost_distributed_flag='N'
530 AND ei.adjustment_type is not null
531 AND ei.costing_method is not null
532 AND EXISTS (
533 SELECT 1
534 FROM pa_cost_distribution_lines cdl2
535 WHERE cdl2.expenditure_item_id =ei.expenditure_item_id
536 );
537
538 END IF ;
539
540 G_err_stage := ' Stage-1 :Calling backoutitem Procedure to reverse selected Costed expenditure Items';
541 write_log(LOG, G_err_stage);
542
543 pa_adjustments.backoutitem (
544 X_exp_item_id => l_Exp_item_id_tbl(i)
545 ,X_expenditure_id => NULL
546 ,X_adj_activity => 'BACKOUT_COSTED_ITEM'
547 ,X_module => 'PAXREVTXN'
548 ,X_user => g_user_id
549 ,X_login => g_login_id
550 ,X_status => l_status
551 );
552 IF G_debug_mode = 'Y' THEN
553 write_log(LOG,'Reversing the Expenditure Item:=>'||l_Exp_item_id_tbl(i));
554 END IF;
555
556 write_log(LOG, 'backoutitem:l_status=>'||l_status);
557
558 IF l_status =0 THEN
559
560 IF G_debug_mode = 'Y' THEN
561 G_err_stage := 'Calling mark_processed_costedset procedure to update transfered_to_prj Flag in Payroll';
562 write_log(LOG, G_err_stage);
563 END IF;
564
565 IF l_Interface_run_idTab(i) IS NOT NULL AND l_PersonIdTab(i) IS NOT NULL
566 THEN
567 mark_processed_costedset ( l_Interface_run_idTab(i),
568 l_PersonIdTab(i),
569 L_RESULT
570 );
571 IF G_debug_mode = 'Y'
572 THEN
573 write_log(LOG, 'Mark_processed_costedset Procedure:Interface_run_id:=>'||l_Interface_run_idTab(i));
574 write_log(LOG, 'Mark_processed_costedset Procedure:Person_id:=>'||l_PersonIdTab(i));
575 END IF;
576 IF l_result = 0
577 THEN
578 IF G_debug_mode = 'Y'
579 THEN
580 write_log(LOG, 'Update status on PA_PAY_PROJ_STATUS_ALL');
581 END IF;
582 UPDATE PA_PAY_PROJ_STATUS_ALL
583 SET PROJECT_ROLLBACK_STATUS_IND='Y',
584 LAST_UPDATED_BY=g_user_id
585 WHERE INTERFACE_RUN_ID=l_Interface_run_idTab(i)
586 AND PERSON_ID =l_PersonIdTab(i) ;
587 END IF;
588 END IF;
589
590 IF G_debug_mode = 'Y'
591 THEN
592 write_log(LOG, 'After mark_processed_costedset Procedure');
593 write_log(LOG,'CreateCDL For backed out expenditure items');
594
595 END IF;
596 OPEN c_adjustedeis(l_Exp_item_id_tbl(i));
597 LOOP
598 FETCH c_adjustedeis
599 BULK COLLECT INTO l_NewExp_item_id_tbl
600 ,l_adj_exp_item_id_tbl;
601
602 FOR J in 1..l_NewExp_item_id_tbl.COUNT
603 LOOP
604
605 G_err_stage := ' Stage-2 :Call CreateReverseCdl to create reverse cdl for backout items ';
606 write_log(LOG, G_err_stage);
607 CreateReverseCdl( X_exp_item_id => l_adj_exp_item_id_tbl(j),
608 X_backout_id => l_NewExp_item_id_tbl(j),
609 X_user => g_user_id,
610 X_status => temp_status
611 );
612 IF G_debug_mode = 'Y' THEN
613 write_log(LOG, 'Created Cdl for Expenditure Item:=>'||l_adj_exp_item_id_tbl(j));
614 write_log(LOG, 'Created Cdl for Reversal Expenditure Item:=>'||l_NewExp_item_id_tbl(j));
615 END IF ;
616 END LOOP;
617 EXIT WHEN c_adjustedeis%NOTFOUND;
618 END LOOP;
619 CLOSE c_adjustedeis;
620
621 IF temp_status = 0
622 THEN
623 G_err_stage := ' Stage-3 :Call createorigexpitem to reinstate Expenditure Transactions';
624 write_log(LOG, G_err_stage);
625 IF l_SysLinkTab(i) IN ('ST','OT') THEN
626 createorigexpitem(X_exp_item_id => l_Exp_item_id_tbl(i),
627 X_adj_activity => 'REINITIATE EI TO DIST',
628 X_module => 'PAXREVTXN',
629 X_user => G_USER_ID,
630 X_login => G_LOGIN_ID,
631 X_status => l_status
632 );
633 IF G_debug_mode = 'Y' THEN
634 write_log(LOG, 'Reinstated Expenditure Item :=>'||l_Exp_item_id_tbl(i));
635 END IF ;
636 END IF;
637
638 IF l_temp_num_processed >= 500 THEN
639 COMMIT;
640 l_temp_num_processed := 0;
641 END IF;
642 END IF; /*TEMP STATUS=0 */
643 END IF; /*L_STATUS=0 */
644 END IF ; /*Back out procedure Status end */
645 END LOOP;
646
647 EXIT
648 WHEN c_expitems%notfound;
649 END LOOP;
650 CLOSE c_expitems;
651 X_status:=0;
652 EXCEPTION
653 WHEN OTHERS THEN
654 X_status := SQLCODE;
655 RAISE;
656 END Reverse_costed_trans;
657
658 -- ========================================================================================
659 -- Start of Comments
660 -- API Name : IS_EI_REVERSABLE
661 -- Type : Public
662 -- Pre-Reqs : None
663 -- Type : Procedure
664 -- Function : function check whether selected expenditure item reversalble
665 --
666 --
667 --
668 -- Parameters:
669 --
670 -- IN
671 -- X_expenditure_item_id
672 --===========================================================================================
673 FUNCTION is_ei_reversable (X_expenditure_item_id IN NUMBER )
674 RETURN VARCHAR2 IS
675 l_is_ei_reversable VARCHAR2(1) :='N';
676 l_trx_source_costed VARCHAR2(1) :='N';
677 l_allow_adjustments_flag VARCHAR2(1) :='N';
678 temp_status NUMBER DEFAULT NULL;
679 BEGIN
680
681 write_log(LOG, 'Entering is_ei_reversable Function ');
682 SELECT
683 nvl(txs.costed_flag, 'N' ),
684 nvl(ALLOW_ADJUSTMENTS_FLAG,'Y')
685 INTO
686 l_trx_source_costed,
687 l_ALLOW_ADJUSTMENTS_FLAG
688 FROM
689 pa_expenditure_items ei ,
690 pa_transaction_sources txs
691 WHERE ei.transaction_source = txs.transaction_source(+)
692 AND ei.expenditure_item_id = X_expenditure_item_id;
693 IF l_trx_source_costed ='N' AND l_ALLOW_ADJUSTMENTS_FLAG='Y' THEN
694 l_is_ei_reversable :='Y';
695 ELSE
696 l_is_ei_reversable :='N';
697 END IF ;
698 IF l_is_ei_reversable ='N' THEN
699 --AUDITING
700 pa_adjustments.InsAuditRec( X_expenditure_item_id
701 , 'REVERSAL'
702 , 'PAXREVTXN'
703 , g_user_id
704 , g_login_id
705 , temp_status
706 , G_REQUEST_ID
707 , G_PROGRAM_ID
708 , G_PROG_APPL_ID
709 , sysdate
710 , 'PA_TR_APE_NO_ADJUST');
711 pa_adjustments.CheckStatus(temp_status);
712 IF g_debug_mode ='Y' THEN
713 write_log(LOG, 'Transaction Source Do Not Allow Adjustements for Expenditure Item:=>'||X_expenditure_item_id);
714 END IF;
715 END IF;
716 return l_is_ei_reversable;
717 EXCEPTION
718 WHEN NO_DATA_FOUND THEN
719 return 'N';
720 WHEN OTHERS THEN
721 return 'N';
722 END IS_EI_REVERSABLE;
723 -- ========================================================================================
724 -- Start of Comments
725 -- API Name : uncosted_eis_exist
726 -- Type : Public
727 -- Pre-Reqs : None
728 -- Type : Procedure
729 -- Function : uncosted_eis_exist function check whether uncosted expenditure item exists for that empid and interface run id
730 --
731 --
732 --
733 --
734 -- Parameters:
735 --
736 -- IN
737 -- P_interface_run_id
738 -- P_incurred_by_person_id
739 --===========================================================================================
740 FUNCTION uncosted_eis_exist(P_interface_run_id IN NUMBER , P_incurred_by_person_id IN VARCHAR2) RETURN VARCHAR2
741 IS
742 l_count NUMBER :=0;
743 l_count2 NUMBER :=0;
744 BEGIN
745
746 IF g_debug_mode ='Y' THEN
747 write_log (LOG,'Entering ncosted_eis_exist Function');
748 END IF;
749
750 SELECT COUNT(1)
751 INTO l_count
752 FROM pa_expenditure_items item,
753 pa_expenditures exp
754 WHERE exp.expenditure_id = item.expenditure_id
755 AND (exp.incurred_by_person_id =P_incurred_by_person_id
756 AND item.interface_run_id =P_interface_run_id)
757 AND item.cost_distributed_flag = 'N';
758
759
760 -- Added for bug 12779047
761 SELECT Count(EI1.EXPENDITURE_ITEM_ID)
762 INTO l_count2 /*Changed variable from l_count to l_count2 for bug:12915699 */
763 FROM PA_EXPENDITURE_ITEMS EI1,
764 PA_EXPENDITURES EXP1
765 WHERE EXP1.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
766 AND EI1.EXPENDITURE_ID = EXP1.EXPENDITURE_ID
767 AND EI1.INTERFACE_RUN_ID = P_interface_run_id
768 AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
769 AND EXISTS
770 (
771 SELECT EI2.EXPENDITURE_ITEM_ID
772 FROM PA_EXPENDITURE_ITEMS EI3,
773 PA_EXPENDITURE_ITEMS EI2,
774 PA_EXPENDITURES EXP2
775 WHERE EI3.interface_run_id = EI1.INTERFACE_RUN_ID
776 AND SUBSTR(EI2.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1) =
777 SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI2.ORIG_TRANSACTION_REFERENCE,':') - 1)
778 AND EXP2.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
779 AND EXP2.EXPENDITURE_ID = EI2.EXPENDITURE_ID
780 AND EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
781 AND EI2.COSTING_METHOD = 'ACTUAL'
782 AND EI2.PAYROLL_ACCRUAL_FLAG = 'N'
783 AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
784 AND EI2.INTERFACE_RUN_ID > EI1.INTERFACE_RUN_ID
785 UNION
786 /* adjustment is not by PAXREVTXN reversal process but from OTL */
787 SELECT EI4.EXPENDITURE_ITEM_ID
788 FROM PA_EXPENDITURE_ITEMS EI5 ,
789 PA_EXPENDITURE_ITEMS EI4 ,
790 PA_EXPENDITURES EXP4
791 WHERE EI5.interface_run_id = EI1.INTERFACE_RUN_ID
792 AND EXP4.INCURRED_BY_PERSON_ID = P_incurred_by_person_id
793 AND EXP4.EXPENDITURE_ID = EI4.EXPENDITURE_ID
794 AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
795 AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
796 AND SUBSTR(EI4.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI4.ORIG_TRANSACTION_REFERENCE,':') - 1) =
797 SUBSTR(EI5.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI5.ORIG_TRANSACTION_REFERENCE,':') - 1)
798 AND NVL(EI4.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
799 AND EI4.INTERFACE_RUN_ID IS NULL
800 AND NOT EXISTS
801 (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPEND_ITEM_ADJ_ACTIVITIES ADJ
802 WHERE ADJ.EXPENDITURE_ITEM_ID = EI4.EXPENDITURE_ITEM_ID
803 AND ADJ.MODULE_CODE = 'PAXREVTXN'
804 AND EXCEPTION_ACTIVITY_CODE = 'REINITIATE EI TO DIST' ) );
805
806
807 IF g_debug_mode ='Y' THEN
808 write_log (LOG,'uncosted_eis_exist:Total Number of Transactions are not Cost Distributed :=>'||l_count);
809 write_log (LOG,'uncosted_eis_exist:P_incurred_by_person_id:=>'||P_incurred_by_person_id);
810 write_log (LOG,'uncosted_eis_exist:P_interface_run_id:=>'||P_interface_run_id);
811 END IF;
812
813
814 IF ( (l_count > 0) OR (l_count2 > 0) )THEN
815 IF ((g_debug_mode ='Y') AND (l_count > 0) )THEN
816 write_log (LOG,'Uncosted Transactions Exist Return :X');
817 END IF;
818 IF ((g_debug_mode ='Y') AND (l_count2 > 0) )THEN
819 write_log (LOG,'Adjusted exists on the Payroll batch ');
820 END IF;
821
822 RETURN 'X';
823 END IF;
824
825
826
827
828 IF g_debug_mode ='Y' THEN
829 write_log (LOG,'Uncosted Transactions Do Not Exist Return :Y');
830 END IF;
831
832 RETURN 'Y';
833
834 END uncosted_eis_exist;
835 -- ========================================================================================
836 -- Start of Comments
837 -- API Name : mark_processed_costedset
838 -- Type : Public
839 -- Pre-Reqs : None
840 -- Type : Procedure
841 -- Function : Updates projects_flag in payroll once
842 -- successfully imported actuals to Projects
843 --
844 --
845 --
846 -- Parameters:
847 --
848 -- IN
849 -- P_interface_run_id
850 -- P_incurred_by_person_id
851 --OUT
852 -- x_status
853 --===========================================================================================
854 PROCEDURE mark_processed_costedset(
855 P_interface_run_id IN NUMBER,
856 P_person_id IN NUMBER,
857 X_status OUT NOCOPY NUMBER)
858 IS
859 l_status VARCHAR2(30);
860 BEGIN
861 IF g_debug_mode ='Y' THEN
862 write_log (LOG,'Entering Mark_processed_Costedset Procedure');
863 END IF;
864 FOR x IN
865 ( SELECT DISTINCT ps.assignment_action_id ,
866 src.element_type_id ,
867 'N' transfered_to_prj
868 FROM
869 pa_pay_audit_all aud ,
870 pay_costing_projects_v src,
871 pa_pay_proj_status_all ps
872 WHERE
873 aud.interface_run_id = p_interface_run_id
874 AND ps.interface_run_id = aud.interface_run_id
875 AND ps.person_id = p_person_id
876 AND src.time_period_id = aud.time_period_id
877 AND TRUNC(NVL(src.SOURCE_START_DATE,src.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
878 AND TRUNC(NVL(src.SOURCE_END_DATE,src.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) /*Added:12611695 */
879 AND src.payroll_id = aud.payroll_id
880 AND src.assignment_action_id = ps.assignment_action_id
881
882 )
883 LOOP
884 IF g_debug_mode ='Y' THEN
885 write_log (LOG,'Mark_processed_Costedset:p_element_type_id =>'||X.element_type_id);
886 write_log (LOG,'Mark_processed_Costedset:p_assignment_action_id =>'||X.assignment_action_id);
887 END IF;
888 pay_core_utils.update_prj_flag (p_element_type_id => x.element_type_id
889 ,p_assignment_action_id =>x.assignment_action_id
890 ,p_flag => x.transfered_to_prj
891 ,p_status => l_status );
892
893 IF g_debug_mode ='Y' THEN
894 write_log (LOG,'pay_core_utils.update_prj_flag:p_status=>'||l_status);
895 END IF;
896 -- Add code to chek the return status and handle the error raised
897 IF l_status='Success' THEN
898 X_status:=0;
899 END IF;
900 END LOOP;
901
902 EXCEPTION
903 WHEN OTHERS THEN
904 X_status := SQLCODE;
905 RAISE;
906 END mark_processed_costedset;
907 -- ========================================================================================
908 -- Start of Comments
909 -- API Name : CreateReverseCdl
910 -- Type : Public
911 -- Pre-Reqs : None
912 -- Type : Procedure
913 -- Function : Creates the reversal cdl for backout items
914 -- and mark the CDF on pa_expenditure_items_all
915
916 --
917 --
918 --
919 -- Parameters:
920 --
921 -- IN
922 -- X_exp_item_id
923 -- X_backout_id
924 -- X_user
925 -- OUT
926 -- X_status
927 --===========================================================================================
928 PROCEDURE CreateReverseCdl ( X_exp_item_id IN NUMBER,
929 X_backout_id IN NUMBER,
930 X_user IN NUMBER,
931 X_status OUT NOCOPY NUMBER)
932 IS
933 p_amount pa_cost_distribution_lines.amount%TYPE;
934 p_dr_ccid pa_cost_distribution_lines.dr_code_combination_id%TYPE;
935 p_cr_ccid pa_cost_distribution_lines.cr_code_combination_id%TYPE;
936 p_transfer_status_code pa_cost_distribution_lines.transfer_status_code%TYPE;
937 p_quantity pa_cost_distribution_lines.quantity%TYPE;
938 p_billable_flag pa_cost_distribution_lines.billable_flag%TYPE;
939 p_request_id pa_cost_distribution_lines.request_id%TYPE;
940 p_program_application_id pa_cost_distribution_lines.program_application_id%TYPE;
941 p_program_id pa_cost_distribution_lines.program_id%TYPE;
942 p_program_update_date pa_cost_distribution_lines.program_update_date%TYPE;
943 p_pa_date pa_cost_distribution_lines.pa_date%TYPE;
944 p_recvr_pa_date pa_cost_distribution_lines.pa_date%TYPE; /**CBGA**/
945 p_gl_date pa_cost_distribution_lines.gl_date%TYPE;
946 p_transferred_date pa_cost_distribution_lines.transferred_date%TYPE;
947 p_transfer_rejection_reason pa_cost_distribution_lines.transfer_rejection_reason%TYPE;
948 p_line_type pa_cost_distribution_lines.line_type%TYPE;
949 p_ind_complied_set_id pa_cost_distribution_lines.ind_compiled_set_id%TYPE;
950 p_burdened_cost pa_cost_distribution_lines.burdened_cost%TYPE;
951 p_line_num_reversed pa_cost_distribution_lines.line_num_reversed%TYPE;
952 p_reversed_flag pa_cost_distribution_lines.reversed_flag%TYPE;
953 p_cdlsr1 pa_cost_distribution_lines.system_reference1%TYPE;
954 p_cdlsr2 pa_cost_distribution_lines.system_reference2%TYPE;
955 p_cdlsr3 pa_cost_distribution_lines.system_reference3%TYPE;
956 p_denom_currency_code pa_cost_distribution_lines.denom_currency_code%TYPE;
957 p_denom_raw_cost pa_cost_distribution_lines.denom_raw_cost%TYPE;
958 p_denom_burdened_cost pa_cost_distribution_lines.denom_burdened_cost%TYPE;
959 p_acct_currency_code pa_cost_distribution_lines.acct_currency_code%TYPE;
960 p_acct_rate_date pa_cost_distribution_lines.acct_rate_date%TYPE;
961 p_acct_rate_type pa_cost_distribution_lines.acct_rate_type%TYPE;
962 p_acct_exchange_rate pa_cost_distribution_lines.acct_exchange_rate%TYPE;
963 p_acct_raw_cost pa_cost_distribution_lines.acct_raw_cost%TYPE;
964 p_acct_burdened_cost pa_cost_distribution_lines.acct_burdened_cost%TYPE;
965 p_project_currency_code pa_cost_distribution_lines.project_currency_code%TYPE;
966 p_project_rate_date pa_cost_distribution_lines.project_rate_date%TYPE;
967 p_project_rate_type pa_cost_distribution_lines.project_rate_type%TYPE;
968 p_project_exchange_rate pa_cost_distribution_lines.project_exchange_rate%TYPE;
969 p_project_id pa_cost_distribution_lines.project_id%TYPE;
970 p_task_id pa_cost_distribution_lines.task_id%TYPE;
971 p_parent_adjusted_id pa_expenditure_items.adjusted_expenditure_item_id%TYPE;
972 p_parent_transferred_id pa_expenditure_items.transferred_from_exp_item_id%TYPE;
973 p_gl_accounted_flag pa_transaction_sources.gl_accounted_flag%TYPE;
974 p_transaction_source pa_transaction_sources.transaction_source%TYPE;
975 l_si_assets_addition_flag pa_cost_distribution_lines.si_assets_addition_flag%TYPE ;
976 p_err_code NUMBER;
977 p_err_stage VARCHAR2(1000);
978 p_err_stack VARCHAR2(1000);
979 e_cdl_error EXCEPTION;
980 -- Start EPP Changes
981 p_pa_period_name VARCHAR2(15);
982 p_gl_period_name VARCHAR2(15);
983 p_recvr_gl_date DATE;
984 p_recvr_gl_period_name VARCHAR2(15);
985 p_recvr_pa_period_name VARCHAR2(15);
986 -- End EPP Changes
987 -- Start Project Currency/ EI Attribute Changes
988 p_projfunc_currency_code VARCHAR2(15);
989 p_projfunc_cost_rate_type VARCHAR2(30);
990 p_projfunc_cost_rate_date date;
991 p_projfunc_cost_exchange_rate NUMBER;
992 p_work_type_id NUMBER;
993 p_project_raw_cost NUMBER;
994 p_project_burdened_cost NUMBER;
995 -- End Project Currency/ EI Attribute Changes
996 -- AP Discounts
997 p_cdlsr4 pa_cost_distribution_lines.system_reference4%TYPE;
998 p_cdlsr5 pa_cost_distribution_lines.system_reference5%TYPE;
999 l_pa_date DATE ;
1000 l_recvr_pa_date DATE ;
1001 l_ei_date DATE ;
1002 l_org_id pa_expenditure_items.org_id%type;
1003 actual_cdl_line_num pa_cost_distribution_lines.line_num%TYPE;
1004 l_recvr_org_id pa_expenditure_items.org_id%type;
1005 l_gl_date DATE;
1006 l_recvr_gl_date DATE;
1007 l_exp_id NUMBER;
1008 l_sob_id NUMBER;
1009 l_recvr_sob_id NUMBER;
1010 l_sys_link_function VARCHAR2(3);
1011 l_err_stage NUMBER;
1012 l_status NUMBER;
1013 l_err_code VARCHAR2(100);
1014 l_rate_source_code varchar2(30);
1015 l_costing_method varchar2(150);
1016 BEGIN
1017 SELECT ITEMS.adjusted_expenditure_item_id,
1018 ITEMS.transferred_from_exp_item_id,
1019 TRN.gl_accounted_flag,
1020 TRN.transaction_source
1021 ,ITEMS.expenditure_item_date
1022 ,ITEMS.org_id
1023 ,NVL(ITEMS.recvr_org_id,ITEMS.ORG_ID)
1024 ,ITEMS.system_linkage_function
1025 ,ITEMS.expenditure_id
1026 INTO p_parent_adjusted_id,
1027 p_parent_transferred_id,
1028 p_gl_accounted_flag,
1029 p_transaction_source
1030 ,l_ei_date
1031 ,l_org_id
1032 ,l_recvr_org_id
1033 ,l_sys_link_function
1034 ,l_exp_id
1035 FROM pa_expenditure_items_All ITEMS,
1036 pa_transaction_sources TRN
1037 WHERE ITEMS.transaction_source = TRN.transaction_source (+)
1038 AND ITEMS.expenditure_item_id = X_exp_item_id;
1039 SELECT imp1.set_of_books_id, imp2.set_of_books_id
1040 INTO l_sob_id, l_recvr_sob_id
1041 FROM pa_implementations_all imp1, pa_implementations_all imp2
1042 WHERE imp1.org_id = l_org_id
1043 AND imp2.org_id = l_recvr_org_id;
1044
1045 SELECT max(cdl.line_num)
1046 INTO actual_cdl_line_num
1047 FROM pa_cost_distribution_lines cdl
1048 WHERE cdl.expenditure_item_id = X_exp_item_id and cdl.line_type = 'R';
1049 SELECT amount
1050 , dr_code_combination_id
1051 , cr_code_combination_id
1052 , transfer_status_code
1053 , quantity
1054 , billable_flag
1055 , request_id
1056 , program_application_id
1057 , program_id
1058 , program_update_date
1059 , pa_date
1060 , gl_date
1061 , transferred_date
1062 , transfer_rejection_reason
1063 , line_type
1064 , ind_compiled_set_id
1065 , nvl(burdened_cost,0) + nvl(projfunc_burdened_change,0)
1066 , line_num_reversed
1067 , reversed_flag
1068 , system_reference1
1069 , system_reference2
1070 , system_reference3
1071 , denom_currency_code
1072 , denom_raw_cost
1073 , NVL(denom_burdened_cost,0) + nvl(denom_burdened_change,0)
1074 , acct_currency_code
1075 , acct_rate_date
1076 , acct_rate_type
1077 , acct_exchange_rate
1078 , acct_raw_cost
1079 , NVL(acct_burdened_cost,0) + nvl(acct_burdened_change,0)
1080 , project_currency_code
1081 , project_rate_date
1082 , project_rate_type
1083 , project_exchange_rate
1084 , project_id
1085 , task_id
1086 , recvr_gl_date
1087 , Projfunc_currency_code
1088 , Projfunc_cost_rate_date
1089 , Projfunc_cost_rate_type
1090 , Projfunc_cost_exchange_rate
1091 , Project_raw_cost
1092 , NVL(Project_burdened_cost,0) + nvl(project_burdened_change,0)
1093 , Work_type_id
1094 , system_reference4
1095 , system_reference5
1096 , decode(si_assets_addition_flag, 'R','T', 'O', 'T', 'Y', 'T', 'N', 'T',si_assets_addition_flag )
1097 , rate_source_code
1098 , costing_method
1099 INTO p_amount,
1100 p_dr_ccid,
1101 p_cr_ccid,
1102 p_transfer_status_code,
1103 p_quantity,
1104 p_billable_flag,
1105 p_request_id,
1106 p_program_application_id,
1107 p_program_id,
1108 p_program_update_date,
1109 p_pa_date,
1110 p_gl_date,
1111 p_transferred_date,
1112 p_transfer_rejection_reason,
1113 p_line_type,
1114 p_ind_complied_set_id,
1115 p_burdened_cost,
1116 p_line_num_reversed,
1117 p_reversed_flag,
1118 p_cdlsr1,
1119 p_cdlsr2,
1120 p_cdlsr3,
1121 p_denom_currency_code,
1122 p_denom_raw_cost,
1123 p_denom_burdened_cost,
1124 p_acct_currency_code,
1125 p_acct_rate_date,
1126 p_acct_rate_type,
1127 p_acct_exchange_rate,
1128 p_acct_raw_cost,
1129 p_acct_burdened_cost,
1130 p_project_currency_code,
1131 p_project_rate_date,
1132 p_project_rate_type,
1133 p_project_exchange_rate,
1134 p_project_id,
1135 p_task_id
1136 , p_recvr_gl_date
1137 , p_Projfunc_currency_code
1138 , p_Projfunc_cost_rate_date
1139 , p_Projfunc_cost_rate_type
1140 , p_Projfunc_cost_exchange_rate
1141 , p_Project_raw_cost
1142 , p_Project_burdened_cost
1143 , p_Work_type_id
1144 , p_cdlsr4
1145 , p_cdlsr5
1146 , l_si_assets_addition_flag
1147 , l_rate_source_code
1148 , l_costing_method
1149 FROM pa_cost_distribution_lines_All
1150 WHERE expenditure_item_id = X_exp_item_id
1151 AND line_num = actual_cdl_line_num;
1152 PA_UTILS2.get_period_information(
1153 p_expenditure_item_date => l_ei_date
1154 ,p_expenditure_id => l_exp_id
1155 ,p_system_linkage_function => l_sys_link_function
1156 ,p_line_type => p_line_type
1157 ,p_prvdr_raw_pa_date => p_pa_date
1158 ,p_recvr_raw_pa_date => p_recvr_pa_date
1159 ,p_prvdr_raw_gl_date => p_gl_date
1160 ,p_recvr_raw_gl_date => p_recvr_gl_date
1161 ,p_prvdr_org_id => l_org_id
1162 ,p_recvr_org_id => l_recvr_org_id
1163 ,p_prvdr_sob_id => l_sob_id
1164 ,p_recvr_sob_id => l_recvr_sob_id
1165 ,p_calling_module => 'CDL'
1166 ,x_prvdr_pa_date => l_pa_date
1167 ,x_prvdr_pa_period_name => p_pa_period_name
1168 ,x_prvdr_gl_date => l_gl_date
1169 ,x_prvdr_gl_period_name => p_gl_period_name
1170 ,x_recvr_pa_date => l_recvr_pa_date
1171 ,x_recvr_pa_period_name => p_recvr_pa_period_name
1172 ,x_recvr_gl_date => l_recvr_gl_date
1173 ,x_recvr_gl_period_name => p_recvr_gl_period_name
1174 ,x_error_code => l_err_code
1175 ,x_return_status => l_status
1176 ,x_error_stage => l_err_stage );
1177 IF p_err_code IS NOT NULL THEN
1178 raise e_cdl_error;
1179 END IF;
1180 PA_COSTING.CREATENEWCDL(
1181 X_expenditure_item_id => X_backout_id
1182 , X_amount => -p_amount
1183 , X_dr_ccid => p_dr_ccid
1184 , X_cr_ccid => p_cr_ccid
1185 , X_transfer_status_code => 'P'
1186 , X_quantity => -p_quantity
1187 , X_billable_flag => p_billable_flag
1188 , X_request_id => p_request_id
1189 , X_program_application_id => p_program_application_id
1190 , x_program_id => p_program_id
1191 , x_program_update_date => p_program_update_date
1192 , X_pa_date => l_pa_date
1193 , X_recvr_pa_date => l_recvr_pa_date
1194 , X_gl_date => l_gl_date
1195 , X_transferred_date => NULL
1196 , X_transfer_rejection_reason => NULL
1197 , X_line_type => p_line_type
1198 , X_ind_compiled_set_id => p_ind_complied_set_id
1199 , X_burdened_cost => -p_burdened_cost
1200 , X_line_num_reversed => p_line_num_reversed
1201 , X_reverse_flag => p_reversed_flag
1202 , X_user => X_user
1203 , X_err_code => p_err_code
1204 , X_err_stage => p_err_stage
1205 , X_err_stack => p_err_stack
1206 , X_project_id => p_project_id
1207 , X_task_id => p_task_id
1208 , X_cdlsr1 => p_cdlsr1
1209 , X_cdlsr2 => p_cdlsr2
1210 , X_cdlsr3 => p_cdlsr3
1211 , X_denom_currency_code => p_denom_currency_code
1212 , X_denom_raw_cost => -p_denom_raw_cost
1213 , X_denom_burden_cost => -p_denom_burdened_cost
1214 , X_acct_currency_code => p_acct_currency_code
1215 , X_acct_rate_date => p_acct_rate_date
1216 , X_acct_rate_type => p_acct_rate_type
1217 , X_acct_exchange_rate => p_acct_exchange_rate
1218 , X_acct_raw_cost => -p_acct_raw_cost
1219 , X_acct_burdened_cost => -p_acct_burdened_cost
1220 , X_project_currency_code => p_project_currency_code
1221 , X_project_rate_date => p_project_rate_date
1222 , X_project_rate_type => p_project_rate_type
1223 , X_project_exchange_rate => p_project_exchange_rate
1224 , P_PaPeriodName => P_Pa_Period_Name
1225 , P_RecvrPaPeriodName => P_Recvr_Pa_Period_Name
1226 , P_GlPeriodName => P_Gl_Period_Name
1227 , P_RecvrGlDate => l_recvr_gl_date
1228 , P_RecvrGlPeriodName => P_Recvr_Gl_Period_Name
1229 , P_Projfunc_currency_code => P_Projfunc_currency_code
1230 , P_Projfunc_cost_rate_date => P_Projfunc_cost_rate_date
1231 , P_Projfunc_cost_rate_type => P_Projfunc_cost_rate_type
1232 , P_Projfunc_cost_exchange_rate => P_Projfunc_cost_exchange_rate
1233 , P_Project_Raw_Cost => -P_Project_Raw_Cost
1234 , P_Project_Burdened_Cost => -P_Project_Burdened_Cost
1235 , P_Work_Type_Id => P_Work_Type_Id
1236 , p_cdlsr4 => p_cdlsr4
1237 , p_si_assets_addition_flag => l_si_assets_addition_flag
1238 , p_cdlsr5 => p_cdlsr5
1239 , P_Parent_Line_Num => actual_cdl_line_num
1240 , p_rate_source_code => l_rate_source_code
1241 , p_costing_method => l_costing_method);
1242 IF p_err_code IS NOT NULL THEN
1243 raise e_cdl_error;
1244 END IF;
1245 UPDATE pa_expenditure_items
1246 SET cost_distributed_flag = 'Y'
1247 WHERE expenditure_item_id = X_backout_id;
1248 X_status := 0;
1249 EXCEPTION
1250 WHEN NO_DATA_FOUND THEN
1251 NULL;
1252 WHEN e_cdl_error THEN
1253 X_status := p_err_code;
1254 WHEN OTHERS THEN
1255 X_status := SQLCODE;
1256 RAISE;
1257 END CreateReverseCdl;
1258 -- ========================================================================================
1259 -- Start of Comments
1260 -- API Name : createorigexpitem
1261 -- Type : Public
1262 -- Pre-Reqs : None
1263 -- Type : Procedure
1264 -- Function : Procedure is used to reinstate the expenditure item
1265 --
1266 --
1267 --
1268 --
1269 -- Parameters:
1270 --
1271 -- IN
1272 -- X_exp_item_id
1273 -- X_adj_activity
1274 -- X_module
1275 -- X_login
1276 -- X_user
1277 -- OUT
1278 -- X_status
1279 --=========================================================================================
1280 PROCEDURE createorigexpitem( X_exp_item_id IN NUMBER
1281 , X_adj_activity IN VARCHAR2
1282 , X_module IN VARCHAR2
1283 , X_user IN NUMBER
1284 , X_login IN NUMBER
1285 , X_status OUT NOCOPY NUMBER )
1286 IS
1287 X_transfer_id NUMBER(15);
1288 temp_status NUMBER DEFAULT NULL;
1289 item_comment VARCHAR2(240);
1290 BEGIN
1291 write_log(LOG, 'Inside createorigexpitem');
1292 G_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1293 G_PROG_APPL_ID := FND_GLOBAL.PROG_APPL_ID;
1294 X_transfer_id := pa_utils.GetNextEiId;
1295 INSERT INTO pa_expenditure_items_all(
1296 expenditure_item_id
1297 , task_id
1298 , expenditure_type
1299 , system_linkage_function
1300 , expenditure_item_date
1301 , expenditure_id
1302 , override_to_organization_id
1303 , last_update_date
1304 , last_updated_by
1305 , creation_date
1306 , created_by
1307 , last_update_login
1308 , quantity
1309 , revenue_distributed_flag
1310 , bill_hold_flag
1311 , billable_flag
1312 , bill_rate_multiplier
1313 , cost_distributed_flag
1314 , raw_cost
1315 , raw_cost_rate
1316 , burden_cost
1317 , burden_cost_rate
1318 , cost_ind_compiled_set_id
1319 , non_labor_resource
1320 , organization_id
1321 , adjusted_expenditure_item_id
1322 , net_zero_adjustment_flag
1323 , attribute_category
1324 , attribute1
1325 , attribute2
1326 , attribute3
1327 , attribute4
1328 , attribute5
1329 , attribute6
1330 , attribute7
1331 , attribute8
1332 , attribute9
1333 , attribute10
1334 , transferred_from_exp_item_id
1335 , transaction_source
1336 , orig_transaction_reference
1337 , source_expenditure_item_id
1338 , job_id
1339 , org_id
1340 , labor_cost_multiplier_name
1341 , receipt_currency_amount
1342 , receipt_currency_code
1343 , receipt_exchange_rate
1344 , denom_currency_code
1345 , denom_raw_cost
1346 , denom_burdened_cost
1347 , acct_currency_code
1348 , acct_rate_date
1349 , acct_rate_type
1350 , acct_exchange_rate
1351 , acct_raw_cost
1352 , acct_burdened_cost
1353 , acct_exchange_rounding_limit
1354 , project_currency_code
1355 , project_rate_date
1356 , project_rate_type
1357 , project_exchange_rate
1358 , cc_cross_charge_code
1359 , cc_prvdr_organization_id
1360 , cc_recvr_organization_id
1361 , cc_rejection_code
1362 , denom_tp_currency_code
1363 , denom_transfer_price
1364 , acct_tp_rate_type
1365 , acct_tp_rate_date
1366 , acct_tp_exchange_rate
1367 , acct_transfer_price
1368 , projacct_transfer_price
1369 , cc_markup_base_code
1370 , tp_base_amount
1371 , cc_cross_charge_type
1372 , recvr_org_id
1373 , cc_bl_distributed_code
1374 , cc_ic_processed_code
1375 , tp_ind_compiled_set_id
1376 , tp_bill_rate
1377 , tp_bill_markup_percentage
1378 , tp_schedule_line_percentage
1379 , tp_rule_percentage
1380 , cost_job_id
1381 , tp_job_id
1382 , prov_proj_bill_job_id
1383 , assignment_id
1384 , work_type_id
1385 , projfunc_currency_code
1386 , projfunc_cost_rate_date
1387 , projfunc_cost_rate_type
1388 , projfunc_cost_exchange_rate
1389 , project_raw_cost
1390 , project_burdened_cost
1391 , project_id
1392 , project_tp_rate_date
1393 , project_tp_rate_type
1394 , project_tp_exchange_rate
1395 , project_transfer_price
1396 , tp_amt_type_code
1397 , cost_burden_distributed_flag
1398 , capital_event_id
1399 , wip_resource_id
1400 , inventory_item_id
1401 , unit_of_measure
1402 , document_header_id
1403 , document_distribution_id
1404 , document_line_number
1405 , document_payment_id
1406 , vendor_id
1407 , document_type
1408 , document_distribution_type
1409 , location_id
1410 , pay_element_type_id
1411 , rate_source_code
1412 , costing_method
1413 , PAYROLL_ACCRUAL_FLAG)
1414 SELECT
1415 X_transfer_id -- expenditure_item_id
1416 , ei.task_id -- task_id
1417 , ei.expenditure_type -- expenditure_type
1418 , ei.system_linkage_function -- system_linkage_function
1419 , ei.expenditure_item_date -- expenditure_item_date
1420 , ei.expenditure_id -- expenditure_id
1421 , ei.override_to_organization_id -- override exp organization
1422 , sysdate -- last_update_date
1423 , X_user -- last_updated_by
1424 , sysdate -- creation_date
1425 , X_user -- created_by
1426 , X_login -- last_update_login
1427 , ei.quantity -- quantity
1428 , 'N' -- revenue_distributed_flag
1429 , ei.bill_hold_flag -- bill_hold_flag
1430 , ei.billable_flag -- billable_flag
1431 , ei.bill_rate_multiplier -- bill_rate_multiplier
1432 , 'N' -- cost_distributed_flag
1433 , NULL -- raw_cost
1434 , NULL -- raw_cost_rate
1435 , NULL -- BURDEN_cost
1436 , NULL -- burden_cost_rate
1437 , ei.cost_ind_compiled_set_id -- cost_ind_compiled_set_id
1438 , ei.non_labor_resource -- non_labor_resource
1439 , ei.organization_id -- organization_id
1440 , ei.ADJUSTED_expenditure_item_id -- adjusted_expenditure_item_id
1441 , 'N' -- net_zero_adjustment_flag
1442 , ei.attribute_category -- attribute_category
1443 , ei.attribute1 -- attribute1
1444 , ei.attribute2 -- attribute2
1445 , ei.attribute3 -- attribute3
1446 , ei.attribute4 -- attribute4
1447 , ei.attribute5 -- attribute5
1448 , ei.attribute6 -- attribute6
1449 , ei.attribute7 -- attribute7
1450 , ei.attribute8 -- attribute8
1451 , ei.attribute9 -- attribute9
1452 , ei.attribute10 -- attribute10
1453 , ei.expenditure_item_id -- tfr from exp item id
1454 , ei.transaction_source -- transaction_source
1455 , ei.orig_transaction_reference -- orig_transaction_reference
1456 , ei.source_expenditure_item_id -- source_expenditure_item_id
1457 , ei.job_id -- job_id
1458 , ei.org_id -- org_id
1459 , ei.labor_cost_multiplier_name -- labor_cost_multiplier_name
1460 , NULL -- receipt_currency_amount
1461 , ei.receipt_currency_code -- receipt_currency_code
1462 , ei.receipt_exchange_rate -- receipt_exchange_rate
1463 , ei.denom_currency_code -- denom_currency_code
1464 , NULL -- denom_raw_cost
1465 , NULL -- denom_burdened_cost
1466 , ei.acct_currency_code -- acct_currency_code
1467 , ei.acct_rate_date -- acct_rate_date
1468 , ei.acct_rate_type -- acct_rate_type
1469 , ei.acct_exchange_rate -- acct_exchange_rate
1470 , NULL -- acct_raw_cost
1471 , NULL -- acct_burdened_cost
1472 , ei.acct_exchange_rounding_limit -- acct_exchange_rounding_limit
1473 , ei.project_currency_code -- project_currency_code
1474 , ei.project_rate_date -- project_rate_date
1475 , ei.project_rate_type -- project_rate_type
1476 , ei.project_exchange_rate -- project_exchange_rate
1477 , ei.cc_cross_charge_code -- cc_cross_charge_code
1478 , ei.cc_prvdr_organization_id -- cc_prvdr_organization_id
1479 , ei.cc_recvr_organization_id -- cc_recvr_organization_id
1480 , ei.cc_rejection_code -- cc_rejection_code
1481 , ei.denom_tp_currency_code -- denom_tp_currency_code
1482 , NULL -- denom_transfer_price
1483 , ei.acct_tp_rate_type -- acct_tp_rate_type
1484 , ei.acct_tp_rate_date -- acct_tp_rate_date
1485 , ei.acct_tp_exchange_rate -- acct_tp_exchange_rate
1486 , NULL -- acct_transfer_price
1487 , NULL -- projacct_transfer_price
1488 , ei.cc_markup_base_code -- cc_markup_base_code
1489 , NULL -- tp_base_amount
1490 , ei.cc_cross_charge_type -- cc_cross_charge_type
1491 , ei.recvr_org_id -- recvr_org_id
1492 , ei.cc_bl_distributed_code -- cc_bl_distributed_code
1493 , ei.cc_ic_processed_code -- cc_ic_processed_code
1494 , ei.tp_ind_compiled_set_id -- tp_ind_compiled_set_id
1495 , ei.tp_bill_rate -- tp_bill_rate
1496 , ei.tp_bill_markup_percentage -- tp_bill_markup_percentage
1497 , ei.tp_schedule_line_percentage -- tp_schedule_line_percentage
1498 , ei.tp_rule_percentage -- tp_rule_percentage
1499 , ei.cost_job_id -- cost_job_id
1500 , ei.tp_job_id -- tp_job_id
1501 , ei.prov_proj_bill_job_id -- prov_proj_bill_job_id
1502 , ei.assignment_id
1503 , ei.work_type_id
1504 , ei.projfunc_currency_code
1505 , ei.projfunc_cost_rate_date
1506 , ei.projfunc_cost_rate_type
1507 , ei.projfunc_cost_exchange_rate
1508 , NULL -- project raw cost
1509 , NULL -- project burended cost
1510 , ei.project_id
1511 , ei.project_tp_rate_date
1512 , ei.project_tp_rate_type
1513 , ei.project_tp_exchange_rate
1514 , NULL
1515 , ei.tp_amt_type_code
1516 /* inserting cost_burden_distributed_flag for 2661921 */
1517 , decode(ei.cost_ind_compiled_set_id,null,'X','N')
1518 , capital_event_id
1519 , wip_resource_id
1520 , inventory_item_id
1521 , unit_of_measure
1522 /* R12 Changes - Start */
1523 , ei.document_header_id
1524 , ei.document_distribution_id
1525 , ei.document_line_number
1526 , ei.document_payment_id
1527 , ei.vendor_id ei_vendor_id
1528 , ei.document_type
1529 , ei.document_distribution_type
1530 /* R12 Changes - End */
1531 /* 12.2 payroll intg enhancement */
1532 , ei.location_id /* Bug 12663113 location_id and pay_element_type_id to be copied from parent ei*/
1533 , ei.pay_element_type_id
1534 , null --ei.rate_source_code
1535 , null --ei.costing_method
1536 , NULL --PAYROLL_ACCRUAL_FLAG
1537 FROM
1538 pa_expenditure_items_all ei
1539 WHERE
1540 ei.expenditure_item_id = X_exp_item_id;
1541 /* Fix for bug 2211472 */
1542 /* Adding the comment of original expenditure_item to the TRANSFERED expenditure item
1543 and storing it in pa_expenditure_comments table */
1544 BEGIN
1545 SELECT
1546 ec.expenditure_comment
1547 INTO
1548 item_comment
1549 FROM
1550 pa_expenditure_comments ec
1551 WHERE
1552 ec.expenditure_item_id = X_exp_item_id;
1553 EXCEPTION
1554 WHEN NO_DATA_FOUND THEN
1555 NULL;
1556 END;
1557 IF ( item_comment IS NOT NULL ) THEN
1558 pa_transactions.InsItemComment( X_ei_id => X_transfer_id
1559 , X_ei_comment => item_comment
1560 , X_user => X_user
1561 , X_login => X_login
1562 , X_status => temp_status );
1563 PA_ADJUSTMENTS.CheckStatus( status_indicator => temp_status );
1564 END IF;
1565 /* End of Fix for bug 2211472 */
1566
1567 PA_ADJUSTMENTS.InsAuditRec( X_transfer_id
1568 , X_adj_activity
1569 , X_module
1570 , X_user
1571 , X_login
1572 , temp_status
1573 /* R12 Changes Start */
1574 , G_REQUEST_ID
1575 , G_PROGRAM_ID
1576 , G_PROG_APPL_ID
1577 , sysdate );
1578 /* R12 Changes End */
1579 PA_ADJUSTMENTS.CheckStatus( temp_status );
1580 X_status := 0;
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 X_status := SQLCODE;
1584 RAISE;
1585 END createorigexpitem;
1586 PROCEDURE write_log (
1587 p_message_type IN NUMBER,
1588 p_message IN VARCHAR2) IS
1589 buffer_overflow EXCEPTION;
1590 PRAGMA EXCEPTION_INIT(buffer_overflow, -20000);
1591 BEGIN
1592 --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
1593 pa_debug.write_file('LOG', 'pa.plsql.PA_REVERSE_COSTED_LAB : '|| p_message , 1);
1594 EXCEPTION /* When exception occurs, program needs to be aborted. */
1595 WHEN OTHERS THEN
1596 raise;
1597 END write_log;
1598 END PA_REVERSE_COSTED_LAB;