[Home] [Help]
PACKAGE BODY: APPS.PA_MC_UPG
Source
1 PACKAGE BODY PA_MC_UPG AS
2 --$Header: PAXMCUPB.pls 120.5 2005/08/26 11:07:42 skannoji noship $
3
4 /* Declare variables */
5
6 G_Pri_SOB_NAME VARCHAR2(30);
7 G_Pri_SOB_ID NUMBER(15);
8 G_Rep_SOB_NAME VARCHAR2(30);
9 g_Rep_SOB_ID NUMBER(15);
10 G_From_Prj_Num VARCHAR2(30);
11 G_To_Prj_Num VARCHAR2(30);
12 G_Project_ID NUMBER (15);
13 G_Rounding VARCHAR2(2);
14 G_Use_Curr_Rate VARCHAR2(2);
15 G_Use_Debug_Flag VARCHAR2(2);
16 G_User_ID VARCHAR2(30);
17 G_MRC_LED DATE;
18 G_MRC_Period VARCHAR2(30);
19 G_Fixed_Type VARCHAR2(30);
20 G_Fixed_Date DATE;
21 G_Pri_Curr_Code VARCHAR2(5);
22 G_Rep_Curr_Code VARCHAR2(5);
23 G_Daily_Type VARCHAR2(30);
24 G_Lock_Hndl VARCHAR2(128);
25 G_Num_Rate NUMBER;
26 G_Denom_Rate NUMBER;
27 G_Fixed_Rate NUMBER;
28 G_Application_ID NUMBER(15);
29 G_Org_ID NUMBER(15);
30 G_Upgrade_Run_ID NUMBER(15);
31 G_Program_ID NUMBER(15);
32 G_MIN_Exp_Item_ID NUMBER(15);
33 G_MAX_Exp_Item_ID NUMBER(15);
34 G_MIN_CDL_Exp_Item_ID NUMBER(15);
35 G_MAX_CDL_Exp_Item_ID NUMBER(15);
36 G_MIN_CCDL_Exp_Item_ID NUMBER;
37 G_MAX_CCDL_Exp_Item_ID NUMBER;
38 G_MIN_DRAFT_INV_DTL_ID NUMBER;
39 G_MAX_DRAFT_INV_DTL_ID NUMBER;
40 G_MIN_Dr_Rev_Num NUMBER(15);
41 G_MAX_Dr_Rev_Num NUMBER(15);
42 G_MIN_Dr_Inv_Num NUMBER(15);
43 G_MAX_Dr_Inv_Num NUMBER(15);
44 G_MIN_Event_Num NUMBER(15);
45 G_MAX_Event_Num NUMBER(15);
46 G_MIN_Ast_Line_ID NUMBER(15);
47 G_MAX_Ast_Line_ID NUMBER(15);
48 G_MIN_Ast_Line_Dtl_ID NUMBER(15);
49 G_MAX_Ast_Line_Dtl_ID NUMBER(15);
50 G_Err_Stack VARCHAR2(650);
51 G_Err_Stage VARCHAR2 (2000);
52 G_Err_Code NUMBER(15);
53 G_Process VARCHAR2(5);
54 G_Validation_Check VARCHAR2(1);
55 G_First_MRC_Period_Flag VARCHAR2(1);
56 G_Token2 VARCHAR2(30);
57 G_Project_Loop_Count NUMBER := 0;
58 G_Org_Loop_Count NUMBER := 0;
59 G_Login_ID Number;
60 G_InterCompany_Project Boolean;
61 G_Prvdr_Recvr_Flag Varchar2(1) := 'R';
62 j Integer;
63 G_EI_Array PA_PLSQL_DATATYPES.IdTabTyp ;
64 Null_G_EI_Array PA_PLSQL_DATATYPES.IdTabTyp ;/* Added for bug 1683582 */
65 G_Future_Record_Found Boolean;
66 --*
67 G_Conversion_Opt Varchar2(1);
68 --*
69
70 PROCEDURE Upgrade_MRC ( x_errbuf OUT NOCOPY VARCHAR2,
71 x_retcode OUT NOCOPY VARCHAR2,
72 x_Pri_SOB IN NUMBER,
73 x_Rep_SOB IN NUMBER,
74 x_From_Prj_Num IN VARCHAR2,
75 x_To_Prj_Num IN VARCHAR2,
76 x_Rounding IN VARCHAR2 DEFAULT 'N',
77 x_Use_Curr_Rate IN VARCHAR2 DEFAULT 'N',
78 x_Debug_Flag IN VARCHAR2 DEFAULT 'N',
79 x_include_closed_prj IN VARCHAR2 DEFAULT 'N',
80 x_Process IN VARCHAR2 DEFAULT 'PLSQL',
81 x_Validation_Check IN VARCHAR2 DEFAULT 'Y'
82 )
83
84
85 /** Upgrade_MRC : Main procedure for MRC upgrade.
86 Parameters are passed from the script.
87 G_Err_Code 0 - Success, -1 - Error ( Abort ).
88 G_Err_Stage contains the error msg.
89 Ora Errors will be raised as exceptions.
90 **/
91 IS
92
93 CURSOR C_Org IS
94 SELECT org_id
95 FROM PA_IMPLEMENTATIONS_ALL
96 WHERE set_of_books_id = G_Pri_SOB_ID;
97
98 CURSOR C_Projects IS
99 SELECT Proj.Project_ID
100 FROM PA_PROJECT_STATUSES ST,PA_PROJECTS PROJ
101 WHERE PROJ.segment1 between G_From_Prj_Num and G_To_Prj_Num
102 AND (G_First_MRC_Period_Flag = 'N'
103 OR
104 (G_First_MRC_Period_Flag = 'Y'
105 AND
106 (x_include_closed_prj = 'Y'
107 OR
108 (x_include_closed_prj = 'N'
109 AND ST.project_system_status_code <> 'CLOSED'))))
110 AND ST.Project_Status_Code = Proj.Project_status_code
111 AND ST.status_type = 'PROJECT'
112 Order BY Proj.Project_ID;
113 Cursor C_CC_EI
114 IS
115 SELECT EI.expenditure_item_id
116 FROM PA_EXPENDITURE_ITEMS_ALL EI,
117 PA_IMPLEMENTATIONS_ALL IMP1,
118 PA_IMPLEMENTATIONS_ALL IMP2
119 WHERE EI.ORG_ID = G_Org_ID
120 AND EI.ORG_ID <> EI.RECVR_ORG_ID
121 AND IMP1.ORG_ID = EI.ORG_ID
122 AND IMP1.set_of_books_id = G_Pri_SOB_ID
123 AND IMP2.ORG_ID = EI.RECVR_ORG_ID
124 AND IMP1.SET_OF_BOOKS_ID <> IMP2.SET_OF_BOOKS_ID;
125
126 v_Old_Stack VARCHAR2(650) := NULL;
127 v_Project_Count NUMBER(15) := 0;
128 v_org_Count NUMBER(15) := 0;
129 l_current_org_id NUMBER ;
130
131 BEGIN
132 NULL;
133
134 EXCEPTION
135
136 WHEN NO_DATA_FOUND THEN
137 Write_Log('*******Exception encountered (NO DATA FOUND): ********');
138 Write_Log (G_Err_Stack);
139 Write_Log (G_Err_Stage);
140 Submit_Report;
141 Write_Log('End Run Date and Time: '||to_char(sysdate,'MM/DD/YYYY,HH24:MI:SS'));
142
143 WHEN DUP_VAL_ON_INDEX THEN
144 Write_Log('*******Exception encountered (DUP VAL ON INDEX): ********');
145 Write_Log (G_Err_Stack);
146 Write_Log (G_Err_Stage);
147 Submit_Report;
148 Write_Log('End Run Date and Time: '||to_char(sysdate,'MM/DD/YYYY,HH24:MI:SS'));
149
150 WHEN OTHERS THEN
151 Write_Log('*******Exception encountered (OTHERS): **********');
152 Write_Log(SQLERRM);
153 Write_Log (G_Err_Stack);
154 Write_Log (G_Err_Stage);
155 Submit_Report;
156 Write_Log('End Run Date and Time: '||to_char(sysdate,'MM/DD/YYYY,HH24:MI:SS'));
157
158 END Upgrade_MRC;
159
160 -----------------------------------------------------------------
161
162 PROCEDURE Validate_Params
163 IS
164 /** Validate_Params : Procedure to validate parameters.
165 Parameters should be available as global vars.
166 While validating certain variables are set.
167 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
168 G_Err_Stage contains the error msg.
169 Ora Errors will be raised as exceptions.
170 **/
171 v_Old_Stack VARCHAR2(650);
172 l_Currency_Code VARCHAR2(5);
173
174 BEGIN
175
176 v_Old_Stack := G_Err_Stack;
177 G_Err_Stack := G_Err_Stack||'->Validate_Params';
178 G_Err_Code := 0;
179 G_Err_Stage := 'Starting Validate_Params';
180 Write_Log(G_Err_Stack);
181
182 END Validate_Params;
183 --------------------------------------------------------------------
184 /** Init_Upgrade : Procedure to initialize variables.
185 Works on global vars. Sets variables as needed.
186 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
187 G_Err_Stage contains the error msg.
188 Ora Errors will be raised as exceptions.
189 **/
190
191 PROCEDURE Init_Upgrade
192 IS
193 --l_Result_Code VARCHAR2(30) ;
194 v_old_stack VARCHAR2(650);
195
196 BEGIN
197
198 v_old_stack := G_Err_Stack;
199 G_Err_Code := 0;
200 G_Err_Stack := G_Err_Stack || '->Init_Upgrade';
201 G_Err_Stage := 'Entering Init_Upgrade';
202
203 Write_Log (G_Err_Stack);
204
205 /* Set Application ID */
206 G_Application_ID := 275;
207
208 END Init_Upgrade;
209
210 -------------------------------------------------------------------------
211 /** Cache_Exchange_Rates :
212 This procedure will cache the exchange rates for various transaction
213 Currencies and the Reporting currency before doing the MRC Upgrade. If
214 It is unable to find rates for any currency, It will list out those
215 currencies, so that the rates for them can be populated and this procedure
216 is rerun
217 **/
218
219 PROCEDURE cache_exchange_rates
220 IS
221
222 /* Added UNION for fix of 3159188 */
223 CURSOR C_currency IS
224 (SELECT distinct denom_currency_code
225 from pa_expenditure_items_all pei
226 where not exists (select null
227 from PA_MC_UPGRADE_RATES
228 where From_currency = pei.denom_currency_code
229 and To_Currency = G_Rep_Curr_Code
230 and Exchange_date = G_Fixed_Date
231 and Rate_Type = G_Fixed_Type)
232 and denom_currency_code <> G_Rep_Curr_Code
233 UNION
234 SELECT distinct bill_trans_currency_code
235 from pa_events pevt
236 where not exists (select null
237 from PA_MC_UPGRADE_RATES
238 where From_currency = pevt.bill_trans_currency_code
239 and To_Currency = G_Rep_Curr_Code
240 and Exchange_date = G_Fixed_Date
241 and Rate_Type = G_Fixed_Type)
242 and bill_trans_currency_code <> G_Rep_Curr_Code)
243 order by 1;
244
245 v_Curr_rec C_currency%rowtype;
246 v_old_stack VARCHAR2(650);
247 v_currency_code VARCHAR2(2000);
248 v_separator VARCHAR2(1);
249 v_err_count NUMBER := 0;
250 BEGIN
251
252 v_old_stack := G_Err_Stack;
253 G_Err_Code := 0;
254 G_Err_Stack := G_Err_Stack || '->Cache_Exchange_Rates';
255 G_Err_Stage := 'Entering Cache_Exchange_Rates';
256
257 EXCEPTION WHEN OTHERS THEN
258 RAISE;
259
260 END cache_exchange_rates;
261
262 -------------------------------------------------------------------------
263 /** insert_temp_rates : Procedure to Insert a transaction currency along with its
264 Fixed rate on the Initial MRC Date, into the cache table.
265 Package modified for Different Conversion Options msundare on 27-06-00
266 **/
267
268 PROCEDURE insert_temp_rates ( x_currency_code IN VARCHAR2 )
269 IS
270
271 v_denominator_rate NUMBER;
272 v_numerator_rate NUMBER;
273 v_exchange_rate NUMBER;
274 v_old_stack VARCHAR2(650);
275
276 BEGIN
277
278 v_old_stack := G_Err_Stack;
279 G_Err_Code := 0;
280 G_Err_Stack := G_Err_Stack || '->insert_temp_rates';
281 G_Err_Stage := 'Entering insert_temp_rates';
282
283 G_Err_Stage := 'Before Get Triangulation Rate ';
284
285 EXCEPTION WHEN gl_currency_api.NO_RATE THEN
286 G_Err_code := -1;
287
288 WHEN gl_currency_api.INVALID_CURRENCY THEN
289 pa_mc_currency_pkg.raise_error('PA_MRC_INV_CUR','PAMRCUPG',x_currency_code);
290
291 WHEN OTHERS THEN
292 RAISE;
293
294 END insert_temp_rates;
295
296 -------------------------------------------------------------------------
297 /** Validate_SOB_Assign : Procedure to validate if the Primary and Reporting
298 Set of books assignment is valid. If yes, then set the variables,
299 else return with -1.
300 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
301 G_Err_Stage contains the error msg.
302 Ora Errors will be raised as exceptions.
303
304 **/
305
306 PROCEDURE Validate_SOB_Assign ( x_Pri_SOB_ID IN NUMBER,
307 x_Rep_SOB_ID IN NUMBER)
308
309 IS
310 v_Old_Stack VARCHAR2(650);
311 BEGIN
312
313 G_Err_Code := 0;
314 v_Old_Stack := G_Err_Stack;
315 G_Err_Stack := G_Err_Stack ||'->Validate_SOB_Assign';
316 G_Err_Stage := 'Starting Validate_SOB_Assign';
317
318
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 G_Err_Code := -1;
322 G_Err_Stage := 'PA_MRC_SOB_ASSIGN';
323 G_Token2 := '.';
324
325 END Validate_SOB_Assign;
326 ------------------------------------------------------------------------
327
328 Function Check_Future_Record
329 Return BOOLEAN
330 IS
331 v_table_name VARCHAR2(30);
332 v_Old_Stack VARCHAR2(650);
333
334 BEGIN
335
336 G_Err_Code := 0;
337 v_Old_Stack := G_Err_Stack;
338 G_Err_Stack := G_Err_Stack ||'->Check_Future_Record';
339 G_Err_Stage := 'Checking Future Record';
340
341 write_log (G_Err_Stage);
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 G_Err_Stage := 'No Future Record Found';
346 IF G_Use_Debug_Flag = 'Y' THEN
347 write_log (G_Err_Stage);
348 END IF;
349 G_Err_Stack := v_Old_Stack;
350 RETURN FALSE;
351
352 END;
353 ------------------------------------------------------------------------
354 Function Validate_First_MRC_Period
355 RETURN BOOLEAN
356 IS
357 v_GL_Period Number;
358 v_Old_Stack VARCHAR2(650);
359 v_Period_Name VARCHAR2(15);
360
361 BEGIN
362
363 G_Err_Code := 0;
364 v_Old_Stack := G_Err_Stack;
365 G_Err_Stack := G_Err_Stack ||'->Validate_First_MRC_Period';
366 G_Err_Stage := 'Inside Validate_First_MRC_Period';
367
368 IF G_Use_Debug_Flag = 'Y' THEN
369 write_log (G_Err_Stage);
370 END IF;
371
372 Return TRUE;
373
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 IF G_Use_Debug_Flag = 'Y' THEN
377 write_log ('Validate First MRC Period returning False');
378 END IF;
379 WHEN OTHERS THEN
380 IF G_Use_Debug_Flag = 'Y' THEN
381 write_log ('sqlerrm');
382 END IF;
383 G_Err_Stack := v_Old_Stack;
384 Return FALSE;
385
386 END;
387 ------------------------------------------------------------------------
388 /** Insert_History_Rec : Procedure to insert a history rec.
389 x_table_name : Table to insert rec for.
390 x_Project_ID : Project to insert recoed for.
391 x_Status : 'CONVERSION' or 'ROUNDING' status
392 x_Status_Value : 'C' Converted, 'S' In Process or NULL.
393 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
394 G_Err_Stage contains the error msg.
395
396 Ora Errors will be raised as exceptions.
397 **/
398
399 PROCEDURE Insert_History_Rec ( x_Table_Name IN VARCHAR2,
400 x_Project_ID IN NUMBER,
401 x_Status IN VARCHAR2,
402 x_Status_Value IN VARCHAR2)
403 IS
404
405 v_Old_Stack VARCHAR2(650);
406
407 BEGIN
408
409 v_Old_Stack := G_Err_Stack;
410 G_Err_Stack := G_Err_Stack || '->Insert_History_Rec';
411 G_Err_Code := 0;
412 G_Err_Stage:= 'Inside Insert_History_Rec ' ||x_Table_Name ;
413
414 EXCEPTION
415 WHEN DUP_VAL_ON_INDEX Then
416 G_Err_Code := -1;
417 G_Err_Stage := 'PA_MRC_HIST_DUP';
418 G_Token2 := x_Table_Name;
419
420 END Insert_History_Rec;
421
422
423 -----------------------------------------------------------------------
424 /** Get_Project_Number : Function to get the project number MIN or MAX.
425 x_Project_Range can be MIN or MAX. Accordingly, a minimum or
426 a maximum project number is returned. Project Number is Unique
427 across Orgs. Hence PA_Projects_ALL used.
428 Returns Project number if successful.
429 Returns -1 if Project number cannot be obtained.
430
431 Returns -2 if MIN or MAX not specified.
432 Ora Errors will be raised as exceptions.
433 **/
434
435 FUNCTION Get_Project_Number ( x_Project_Range IN VARCHAR2 )
436 RETURN VARCHAR2 IS
437
438 v_project_number varchar2(30);
439 v_Old_Stack VARCHAR2(650);
440
441 BEGIN
442 G_Err_Code := 0;
443 v_Old_Stack := G_Err_Stack;
444 G_Err_Stack := G_Err_Stack||'->Get_Project_Number';
445 G_Err_Stage := 'Starting Get_Project_Number: '||x_Project_Range;
446
447 RETURN v_project_number;
448
449
450 END Get_Project_Number;
451
452 ----------------------------------------------------------------------------
453 /** Validate_SOB : Function to validate the set of books name passed.
454 Returns Set of Books ID, if found, Else -1. ( ABORT )
455 Ora Errors will be raised as exceptions.
456 **/
457 FUNCTION Validate_SOB ( x_SOB_ID IN NUMBER,
458 l_Currency_Code OUT NOCOPY VARCHAR2)
459 RETURN VARCHAR2 IS
460 v_SOB_Name VARCHAR2(30);
461 v_Old_Stack VARCHAR2(650);
462
463 BEGIN
464 v_Old_Stack := G_Err_Stack;
465 G_Err_Stack := G_Err_Stack||'->Validate_SOB';
466 G_Err_Code := 0;
467 G_Err_Stage := 'Starting Validate_SOB' || x_SOB_ID;
468
469 RETURN v_SOB_Name;
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 RETURN NULL;
474
475 END Validate_SOB;
476 ----------------------------------------------------------------
477 /** Get_User_Lock : Function to acquire a user lock.
478 x_lock_name : name of the lock.
479 x_lock_mode : Mode of the lock ( Exclusive,..)
480 x_commit_mode : Rls with commit or not
481 Returns : lock handle if successful in acquiring lock
482 else -1 - Cannot acquire lock.
483 Ora Errors will be raised as exceptions.
484 **/
485
486
487 FUNCTION Get_User_Lock ( x_Lock_Name IN VARCHAR2,
488 x_Lock_Mode IN NUMBER default 6,
489 x_Commit_Mode IN BOOLEAN default FALSE )
490 RETURN VARCHAR2
491 IS
492 timeout number := 100;
493 lstatus number;
494 lockhndl varchar2(128);
495 v_Old_Stack VARCHAR2(650);
496
497 BEGIN
498 v_Old_Stack := G_Err_Stack;
499 G_Err_Stack := G_Err_Stack || '->Get_User_Lock';
500 G_Err_Code := 0;
501 G_Err_Stage := 'Entered Get_User_Lock';
502
503 IF G_use_Debug_Flag = 'Y' THEN
504 Write_Log (G_Err_Stage);
505 END IF;
506
507 Return NULL; /* Failed to allocate lock */
508
509 END Get_User_Lock;
510
511 ------------------------------------------------------------------
512 /** Rls_User_Lock : Function to release user lock.
513 x_Lock_Hndl : The lock handle obtained earlier.
514 Returns 0 - success, -1 - Error. ( Abort ).
515 **/
516
517 FUNCTION Rls_User_Lock ( x_Lock_Hndl IN VARCHAR2 )
518 RETURN NUMBER
519 IS
520 status number;
521 v_Old_Stack VARCHAR2(650);
522
523 BEGIN
524 v_Old_Stack := G_Err_Stack;
525 G_Err_Stack := G_Err_Stack || '->Rls_User_Lock ';
526 G_Err_Code := 0;
527 G_Err_Stage:= 'Starting Rls_User_Lock';
528
529 status := dbms_lock.release(x_lock_hndl);
530 G_Err_Stage := 'Release Lock Status: '||to_char(status);
531 IF G_Use_Debug_Flag = 'Y' THEN
532 Write_Log (G_Err_Stage);
533 END IF;
534
535 Return 0;
536
537 END Rls_User_Lock;
538
539 ------------------------------------------------------------------
540 /** Get_Table_Status : Function to get the table status for the project from
541 the MRC upgrade history table.
542 x_table_name : table to be checked for.
543 x_Project_ID : Project to be checked for.
544 x_Status : Status to check for - Conversion or Rounding.
545
546 Can have values - CONVERSION or ROUNDING.
547 Returns : 'C' = Converted, NULL = Not done yet. '-1' if status
548 specified is wrong.
549 **/
550
551 FUNCTION Get_Table_Status ( x_Table_Name IN VARCHAR2,
552 x_Project_ID IN NUMBER,
553 x_Status IN VARCHAR2 )
554 RETURN VARCHAR2
555 IS
556
557 v_status VARCHAR2(25);
558 v_Old_Stack VARCHAR2(650);
559
560
561 BEGIN
562 v_Old_Stack := G_Err_Stack;
563 G_Err_Stack := G_Err_Stack || '->Get_Table_Status';
564 G_Err_Code := 0;
565 G_Err_Stage := 'Entered Get_Table_Status'||x_Table_Name;
566
567 Return v_status;
568
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571 G_Err_Stack := v_Old_Stack;
572 RETURN NULL;
573
574 END Get_Table_Status;
575
576 -----------------------------------------------------------------
577
578 PROCEDURE Convert_Table ( x_Table_Name IN VARCHAR2 )
579 IS
580 v_status VARCHAR2(25);
581 v_Old_Stack VARCHAR2(650);
582
583 BEGIN
584 v_Old_Stack := G_Err_Stack;
585 G_Err_Stack := G_Err_Stack || '->Convert_Table';
586 G_Err_Code := 0;
587 G_Err_Stage:= 'Inside Convert_Table '||x_Table_Name ;
588
589 END Convert_Table;
590
591 -----------------------------------------------------------------
592
593 PROCEDURE Insert_Recs ( x_Table_Name IN VARCHAR2)
594 IS
595 v_Old_Stack VARCHAR2(650);
596 BEGIN
597 v_Old_Stack := G_Err_Stack;
598 G_Err_Stack := G_Err_Stack || '->Insert_Recs ';
599 G_Err_Code := 0;
600 G_Err_Stage:= 'Inserting Records: ' ||x_Table_Name;
601
602 END Insert_Recs;
603
604 -----------------------------------------------------------------
605
606 PROCEDURE Update_Recs ( x_Table_Name IN VARCHAR2)
607 IS
608 v_Old_Stack VARCHAR2(650);
609 BEGIN
610 v_Old_Stack := G_Err_Stack;
611 G_Err_Stack := G_Err_Stack || '->Update_Recs ';
612 G_Err_Code := 0;
613 G_Err_Stage:= 'Starting Update_Recs: ' ||x_Table_Name;
614
615 END Update_Recs;
616 -----------------------------------------------------------------
617
618 PROCEDURE Insert_CDL
619 IS
620 v_Old_Stack VARCHAR2(650);
621 BEGIN
622 v_Old_Stack := G_Err_Stack;
623 G_Err_Stack := G_Err_Stack || '->Insert_CDL';
624 G_Err_Code := 0;
625 G_Err_Stage:= 'Inside Insert_CDL ';
626
627 IF G_Use_Debug_Flag = 'Y' THEN
628 Write_Log (G_Err_Stage);
629 END IF;
630
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 -- This Exception will be raised from get_converted_amount,
634 -- If the rate is not found for the Denom Currency in the cache
635 Rollback;
636
637 G_Err_code := -10;
638 cache_exchange_rates;
639 Commit;
640 WHEN DUP_VAL_ON_INDEX THEN
641 G_Err_Code := -1;
642 G_Err_Stage := 'PA_MRC_INS_DUP';
643
644 END Insert_CDL;
645 -----------------------------------------------------------------
646
647 PROCEDURE Insert_CRDL
648 IS
649 v_Old_Stack VARCHAR2(650);
650 BEGIN
651
652 v_Old_Stack := G_Err_Stack;
653 G_Err_Stack := G_Err_Stack || '->Insert_CRDL';
654 G_Err_Code := 0;
655 G_Err_Stage:= 'Inside Insert_CRDL ';
656
657
658 EXCEPTION
659 WHEN DUP_VAL_ON_INDEX THEN
660 G_Err_Code := -1;
661 G_Err_Stage := 'PA_MRC_INS_DUP';
662
663 END Insert_CRDL;
664 -----------------------------------------------------------------
665
666 PROCEDURE Insert_ERDL
667 IS
668 v_Old_Stack VARCHAR2(650);
669 BEGIN
670 v_Old_Stack := G_Err_Stack;
671 G_Err_Stack := G_Err_Stack || '->Insert_ERDL';
672 G_Err_Code := 0;
673 G_Err_Stage:= 'Inside Insert_ERDL ';
674
675 EXCEPTION
676 WHEN DUP_VAL_ON_INDEX THEN
677 G_Err_Code := -1;
678 G_Err_Stage := 'PA_MRC_INS_DUP';
679
680 END Insert_ERDL;
681 -----------------------------------------------------------------
682
683 PROCEDURE Insert_DR
684 IS
685 v_Old_Stack VARCHAR2(650);
686 BEGIN
687
688 v_Old_Stack := G_Err_Stack;
689 G_Err_Stack := G_Err_Stack || '->Insert_DR';
690 G_Err_Code := 0;
691 G_Err_Stage:= 'Inside Insert_DR ';
692
693 EXCEPTION
694 WHEN DUP_VAL_ON_INDEX THEN
695 G_Err_Code := -1;
696 G_Err_Stage := 'PA_MRC_INS_DUP';
697
698 END Insert_DR;
699 -----------------------------------------------------------------
700
701 PROCEDURE Insert_Event
702 IS
703 v_Old_Stack VARCHAR2(650);
704 BEGIN
705 v_Old_Stack := G_Err_Stack;
706 G_Err_Stack := G_Err_Stack || '->Insert_Event';
707 G_Err_Code := 0;
708 G_Err_Stage:= 'Inside Insert_Event ';
709
710 EXCEPTION
711 WHEN DUP_VAL_ON_INDEX THEN
712
713 G_Err_Code := -1;
714 G_Err_Stage := 'PA_MRC_INS_DUP';
715
716 END Insert_Event;
717 -----------------------------------------------------------------
718
719 PROCEDURE Insert_AL
720 IS
721 v_Old_Stack VARCHAR2(650);
722 BEGIN
723 v_Old_Stack := G_Err_Stack;
724 G_Err_Stack := G_Err_Stack || '->Insert_AL';
725 G_Err_Code := 0;
726 G_Err_Stage:= 'Inside Insert_AL ';
727
728 EXCEPTION
729 WHEN DUP_VAL_ON_INDEX THEN
730 G_Err_Code := -1;
731 G_Err_Stage := 'PA_MRC_INS_DUP';
732
733 END Insert_AL;
734 -----------------------------------------------------------------
735
736 PROCEDURE Insert_ALD
737 IS
738 v_Old_Stack VARCHAR2(650);
739 BEGIN
740 v_Old_Stack := G_Err_Stack;
741 G_Err_Stack := G_Err_Stack || '->Insert_ALD';
742 G_Err_Code := 0;
743 G_Err_Stage:= 'Inside Insert_ALD';
744
745 EXCEPTION
746
747 WHEN DUP_VAL_ON_INDEX THEN
748 G_Err_Code := -1;
749 G_Err_Stage := 'PA_MRC_INS_DUP';
750
751 END Insert_ALD;
752 -----------------------------------------------------------------
753
754 Procedure Insert_DINV (x_Project_ID IN NUMBER,
755 x_Rep_SOB_ID IN NUMBER)
756 IS
757 /* added di.canceled_flag orig_canceled_flag, di.invoice_date invoice_date
758 for bug fix 1924362 */
759
760 CURSOR c_dinv ( rprojectid IN Number ) IS
761 SELECT di.draft_invoice_num draft_invoice_num,
762 di.draft_invoice_num_credited draft_invoice_num_credited,
763 NVL(di.unbilled_receivable_dr,0) unbilled_receivable_dr,
764 NVL(di.unearned_revenue_cr,0) unearned_revenue_cr,
765 di.write_off_flag write_off_flag ,
766 di.customer_bill_split customer_bill_split,
767 NVL(di.retention_percentage,0) retention_percentage,
768 NVL(di.retention_invoice_flag,'N') retention_invoice_flag, /* added bug2966251 */
769 dic.canceled_flag canceled_flag,
770 di.canceled_flag orig_canceled_flag,
771 di.invoice_date invoice_date
772 FROM PA_Draft_Invoices dic,
773 PA_Draft_Invoices di
774 WHERE di.project_id = rprojectid
775 AND dic.project_id(+) = di.project_id
776 AND dic.draft_invoice_num(+) = di.draft_Invoice_num_credited
777 ORDER BY 1;
778
779 /* added amount for bug fix 1924362 */
780 CURSOR c_inv_items ( rprojectid IN NUMBER, rdinvnum IN NUMBER ) IS
781 SELECT decode(invoice_line_type,'RETENTION',2,1) l_type, line_num,
782 event_task_id, event_num, invoice_line_type,amount /* added for bug 1946624 */
783 , bill_trans_currency_code, bill_trans_bill_amount /* MCB2 */
784 , projfunc_bill_amount, retention_rule_id,invproc_currency_code/* added bug 2966251 */
785 FROM pa_draft_invoice_items
786 WHERE project_id = rprojectid
787 AND draft_invoice_num = rdinvnum
788 ORDER BY 1,2;
789
790 /* decode used and then the order by so as to get the RETENTION lines
791 as the last line */
792
793
794
795 v_dinv c_dinv%ROWTYPE;
796 rec_inv_items c_inv_items%ROWTYPE;
797 --currency VARCHAR2(30);
798
799 --sob NUMBER;
800 l_transfer_status_code VARCHAR2(1);
801 v_Old_Stack VARCHAR2(650);
802
803 BEGIN
804 v_Old_Stack := G_Err_Stack;
805 G_Err_Stack := G_Err_Stack || '->Insert_DINV';
806 G_Err_Code := 0;
807 G_Err_Stage:= 'Inside Insert_DINV ';
808
809 IF G_Use_Debug_Flag = 'Y' THEN
810 Write_Log (G_Err_Stage);
811 END IF;
812
813 --currency := G_PRI_CURR_CODE;
814
815 --sob := G_PRI_SOB_ID;
816
817 /* Loop for all Draft invoices for this project */
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 RAISE;
822 IF G_Use_Debug_Flag = 'Y' THEN
823 Write_Log ('Exitting Insert_DINV');
824 END IF;
825
826 END Insert_DINV;
827 -----------------------------------------------------------------
828
829 PROCEDURE Insert_exp_items( x_Project_ID IN Number,
830 x_Rep_SOB_ID IN Number)
831 IS
832 l_raw_cost Number := 0;
833 l_burdened_cost Number := 0;
834 l_revenue Number := 0;
835 l_accrued_revenue Number :=0;
836 l_bill_amount Number := 0;
837 l_raw_revenue Number := 0;
838 l_adjusted_revenue Number := 0;
839 l_forecast_revenue Number := 0;
840 l_exchange_rate_cost Number :=0;
841 l_exchange_rate_rev Number :=0;
842 l_exchange_rate_type_rev VARCHAR2(30);
843 l_exchange_date_rev DATE;
844
845 l_tp_exchange_rate Number;
846 l_tp_exchange_date Date;
847 l_tp_rate_type Varchar2(30);
848
849 l_transfer_price Number;
850 l_exchange_date_cost Date;
851 l_exchange_rate_type_cost Varchar2(30) ;
852 v_Old_Stack VARCHAR2(650);
853
854 l_denominator_rate NUMBER;
855 l_numerator_rate NUMBER;
856
857 l_fcst_exchange_date DATE ;
858 l_fcst_exchange_rate_type VARCHAR2(30) ;
859 l_fcst_exchange_rate NUMBER := 0 ;
860
861 l_inv_exchange_date DATE ;
862 l_inv_exchange_rate_type VARCHAR2(30) ;
863 l_inv_exchange_rate NUMBER := 0 ;
864
865 l_result_code VARCHAR2(15);
866
867
868 Cursor C_exp_items IS
869 Select expenditure_item_id, Denom_raw_cost,
870 quantity,
871 acct_currency_code,
872 project_currency_code,
873 expenditure_item_date,
874 Denom_burdened_cost, raw_revenue, accrued_revenue,
875 adjusted_revenue, forecast_revenue,
876 bill_amount, net_zero_adjustment_flag,
877 bill_trans_bill_amount,
878 bill_trans_raw_revenue,
879 projfunc_inv_rate_date,
880 projfunc_inv_rate_type,
881 projfunc_inv_exchange_rate,
882 projfunc_rev_rate_type,
883 projfunc_rev_exchange_rate,
884 projfunc_rev_rate_date,
885 bill_trans_forecast_revenue,
886 projfunc_fcst_rate_date,
887 projfunc_fcst_rate_type,
888 projfunc_fcst_exchange_rate,
889 bill_trans_currency_code,
890 transferred_from_exp_item_id,
891 denom_transfer_price,
892 cc_cross_charge_code,
893 project_exchange_rate,
894 recvr_org_id,
895 acct_raw_cost,
896 acct_burdened_cost,
897 acct_rate_type,
898 acct_rate_date,
899 acct_exchange_rate,
900 acct_transfer_price,
901 acct_tp_rate_type,
902 acct_tp_rate_date,
903 acct_tp_exchange_rate,
904 denom_currency_code -- added for MRC enhancement
905 ,org_id
906 From PA_Expenditure_Items_ALL
907 Where expenditure_item_id < NVL(G_MIN_exp_item_id,G_MAX_exp_item_id)
908 And Project_Id = x_Project_ID;
909 /* Bug 3961113 : Removing unnecessary join with PA_TASKS
910 and Task_id IN ( Select task_id
911 From PA_Tasks
912 Where Project_ID = x_Project_ID );
913 */
914
915 Begin
916 v_Old_Stack := G_Err_Stack;
917 G_Err_Stack := G_Err_Stack || '->Insert_exp_items';
918 G_Err_Code := 0;
919 G_Err_Stage:= 'Inside Insert_exp_items: Max EI['||G_MAX_exp_item_id||']Min EI['||G_MIN_exp_item_id||
920 ']ProjectId['||x_Project_ID||']';
921 IF G_Use_Debug_Flag = 'Y' THEN
922 Write_Log(G_Err_Stage);
923 END IF;
924
925 EXCEPTION
926
927 WHEN DUP_VAL_ON_INDEX THEN
928 G_Err_Code := -1;
929 G_Err_Stage := 'PA_MRC_INS_DUP';
930
931 END Insert_exp_items;
932 -----------------------------------------------------------------
933
934 PROCEDURE Update_CDL
935 IS
936 v_Old_Stack VARCHAR2(650);
937 BEGIN
938 v_Old_Stack := G_Err_Stack;
939 G_Err_Stack := G_Err_Stack || '->Update_CDL';
940 G_Err_Code := 0;
941 G_Err_Stage:= 'Inside Update_CDL ';
942 /** This part is not needed because both original transactions
943 and reversed transactions use either expenditure item date
944 or G_Fixed_Date for conversion so absolute values of both
945 converted amounts should be same **/
946
947 END Update_CDL;
948 -----------------------------------------------------------------
949
950 PROCEDURE Update_CRDL
951 IS
952 v_Old_Stack VARCHAR2(650);
953 BEGIN
954 v_Old_Stack := G_Err_Stack;
955 G_Err_Stack := G_Err_Stack || '->Update_CRDL';
956 G_Err_Code := 0;
957 G_Err_Stage:= 'Inside Update_CRDL ';
958
959 END Update_CRDL;
960 -----------------------------------------------------------------
961 PROCEDURE Update_ERDL
962 IS
963 v_Old_Stack VARCHAR2(650);
964 BEGIN
965 v_Old_Stack := G_Err_Stack;
966 G_Err_Stack := G_Err_Stack || '->Update_ERDL';
967 G_Err_Code := 0;
968 G_Err_Stage:= 'Inside Update_ERDL ';
969
970 END Update_ERDL;
971 -----------------------------------------------------------------
972
973 PROCEDURE Update_DR
974 IS
975 BEGIN
976 G_Err_Code := 0;
977 END Update_DR;
978 -----------------------------------------------------------------
979
980 PROCEDURE Update_Event
981 IS
982 BEGIN
983 G_Err_Code := 0;
984 END Update_Event;
985 -----------------------------------------------------------------
986
987 PROCEDURE Update_AL
988 IS
989 BEGIN
990 G_Err_Code := 0;
991 END Update_AL;
992 -----------------------------------------------------------------
993
994 PROCEDURE Update_ALD
995 IS
996 BEGIN
997
998 G_Err_Code := 0;
999
1000 END Update_ALD;
1001 -----------------------------------------------------------------
1002
1003 PROCEDURE Update_DINV
1004 IS
1005 BEGIN
1006
1007 G_Err_Code := 0;
1008
1009 END Update_DINV;
1010 -----------------------------------------------------------------
1011 PROCEDURE update_exp_items ( x_Project_ID IN Number,
1012 x_Rep_SOB_ID IN Number)
1013 IS
1014
1015 prev_orig_ei Number := 0;
1016 split_flag Varchar2(1) := 'N';
1017 split_amt Number := 0;
1018 split_raw_cost Number := 0;
1019 split_burden_cost Number := 0;
1020
1021 /* Cursor modified for bug 1534858. Explicit hints are given so that even if statisctics
1022 are not available on mc tables during upgrade it takes this plan only which is the best plan
1023 */
1024
1025
1026 v_Old_Stack VARCHAR2(650);
1027
1028 Begin
1029 v_Old_Stack := G_Err_Stack;
1030 G_Err_Stack := G_Err_Stack || '->Update_exp_items';
1031 G_Err_Code := 0;
1032 G_Err_Stage:= 'Inside Update_exp_items ';
1033 END Update_exp_items ;
1034 -----------------------------------------------------------------
1035 /** Important: Dependencies: Get_Rate_Type,Get_Rate_Date. If you modify any
1036 logic in get_converted_amount check if the logic has any
1037 impact on those two dependent functions **/
1038
1039 FUNCTION Get_Converted_Amount ( x_Denom_Cur_Code IN Varchar2,
1040 x_Acct_Rate_Type IN Varchar2,
1041 x_Conversion_Date IN Date,
1042 x_Amount IN Number,
1043 x_Acct_Amt IN Number,
1044 x_Rate IN Varchar2
1045 DEFAULT 'N'
1046 )
1047 RETURN NUMBER
1048 IS
1049 Converted_Amount Number;
1050 Sign_Factor Number (1);
1051
1052 /* 08-20-1998 : Earlier GL_Date was compared against MRC Date to determine if
1053 the txn is future dated or not. Now it is the txn date, that is compared
1054 against the MRC date to determine this. Thus the GL Date parameter
1055 is not used, instead conversion date parameter is used.
1056 Tsaifee. */
1057
1058 v_curr_code Varchar2(15):= NULL;
1059 v_denom_rate NUMBER;
1060 v_num_rate NUMBER;
1061 v_amount NUMBER;
1062
1063 BEGIN
1064
1065 Return (Converted_Amount);
1066
1067 END Get_Converted_Amount;
1068 -------------------------------------------------------------
1069 PROCEDURE Get_Cached_Rate ( x_curr_code IN VARCHAR2,
1070 x_denom_rate OUT NOCOPY NUMBER,
1071 x_num_rate OUT NOCOPY NUMBER)
1072 IS
1073 BEGIN
1074
1075 NULL;
1076 EXCEPTION WHEN OTHERS THEN
1077 RAISE;
1078 END Get_Cached_Rate;
1079 -------------------------------------------------------------
1080 PROCEDURE Write_Log ( x_Msg IN VARCHAR2)
1081 IS
1082 BEGIN
1083
1084 --r_debug.r_msg('Log:'||x_Msg);
1085
1086 IF G_Process = 'PLSQL' THEN
1087 FND_FILE.PUT(FND_FILE.LOG,x_Msg);
1088 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1089 ELSIF G_Process = 'SQL' THEN
1090 --dbms_output.put_line (x_Msg);
1091 NULL;
1092 END IF;
1093
1094 EXCEPTION
1095
1096 WHEN UTL_FILE.INVALID_PATH THEN
1097 raise_application_error(-20010,'INVALID PATH exception from UTL_FILE !!'
1098 || G_Err_Stack ||' - '||G_Err_Stage);
1099 WHEN UTL_FILE.INVALID_MODE THEN
1100 raise_application_error(-20010,'INVALID MODE exception from UTL_FILE !!'
1101 || G_Err_Stack ||' - '||G_Err_Stage);
1102 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1103 raise_application_error(-20010,'INVALID FILEHANDLE exception from UTL_FILE !!'
1104 || G_Err_Stack ||' - '||G_Err_Stage);
1105 WHEN UTL_FILE.INVALID_OPERATION THEN
1106 raise_application_error(-20010,'INVALID OPERATION exception from UTL_FILE !!'
1107 || G_Err_Stack ||' - '||G_Err_Stage);
1108 WHEN UTL_FILE.READ_ERROR THEN
1109 raise_application_error(-20010,'READ ERROR exception from UTL_FILE !!'
1110 || G_Err_Stack ||' - '||G_Err_Stage);
1111 WHEN UTL_FILE.WRITE_ERROR THEN
1112 raise_application_error(-20010,'WRITE ERROR exception from UTL_FILE !!'
1113 || G_Err_Stack ||' - '||G_Err_Stage);
1114 WHEN UTL_FILE.INTERNAL_ERROR THEN
1115 raise_application_error(-20010,'INTERNAL ERROR exception from UTL_FILE !!'
1116 || G_Err_Stack ||' - '||G_Err_Stage);
1117
1118 END Write_Log;
1119 -------------------------------------------------------------
1120 PROCEDURE Write_Out ( x_Msg IN VARCHAR2)
1121 IS
1122 BEGIN
1123
1124 IF G_Process = 'PLSQL' THEN
1125 FND_FILE.PUT(FND_FILE.OUTPUT,x_Msg);
1126 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1127 ELSIF G_Process = 'SQL' THEN
1128 /* dbms_output.put_line (x_Msg); */
1129 NULL;
1130 END IF;
1131
1132 END Write_Out;
1133 -------------------------------------------------------------
1134 PROCEDURE Submit_Report
1135 IS
1136 v_request number;
1137
1138 BEGIN
1139 IF G_Project_Loop_Count >0 THEN
1140 Write_Log(' ');
1141 Write_Log('Submitting Request for Report');
1142 v_request := FND_REQUEST.Submit_Request('PA','PAMRCUPGRPT',NULL,NULL,NULL,
1143 G_PRI_SOB_ID, G_REP_SOB_ID, G_UPGRADE_RUN_ID );
1144 if v_request = 0 then
1145 Write_Log('Error encountered while submitting request for report');
1146 else
1147 G_Err_Stage := 'PA_MRC_UPG_REP';
1148 fnd_message.set_name('PA', G_Err_Stage);
1149 fnd_message.set_token('MODULE', 'PAXMCUPB');
1150 fnd_message.set_token('CURRENCY', to_char(v_request));
1151 G_Err_Stage := fnd_message.get;
1152
1153 Write_Log (G_Err_Stage);
1154 Write_Out (' ');
1155 Write_Out (G_Err_Stage);
1156
1157 end if;
1158 END IF;
1159 END Submit_Report;
1160 --------------------------------------------------------------
1161 PROCEDURE Insert_CCDL
1162 IS
1163 l_rep_rsob_id PA_PLSQL_DATATYPES.IDTabTyp;
1164 l_org_id PA_PLSQL_DATATYPES.IDTabTyp;
1165 l_rcurrency_code PA_PLSQL_DATATYPES.Char15TabTyp;
1166 l_cc_dist_line_id PA_PLSQL_DATATYPES.IDTabTyp;
1167 l_expenditure_item_id PA_PLSQL_DATATYPES.IDTabTyp;
1168 l_line_num PA_PLSQL_DATATYPES.IDTabTyp;
1169 l_cdl_line_num PA_PLSQL_DATATYPES.NumTabTyp;
1170 l_acct_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
1171 l_prvdr_cost_reclass_code PA_PLSQL_DATATYPES.Char240TabTyp;
1172 l_expenditure_item_date PA_PLSQL_DATATYPES.DateTabTyp;
1173 l_acct_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
1174 l_denom_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
1175 l_denom_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
1176 l_dist_line_id_reversed PA_PLSQL_DATATYPES.IDTabTyp;
1177 l_line_type PA_PLSQL_DATATYPES.Char2TabTyp;
1178 v_old_stack Varchar2(650);
1179 i Integer;
1180 l_use_debug_flag Boolean;
1181
1182
1183 BEGIN
1184
1185 v_Old_Stack := G_Err_Stack;
1186 G_Err_Stack := G_Err_Stack || '->Insert_CCDL';
1187 G_Err_Code := 0;
1188 G_Err_Stage:= 'Inside Insert_CCDL ';
1189 IF G_Use_Debug_Flag = 'Y' THEN
1190 Write_Log (G_Err_Stage);
1191 END IF;
1192
1193 i := 1;
1194
1195 END Insert_CCDL;
1196
1197 PROCEDURE Update_CCDL
1198 IS
1199 BEGIN
1200 G_Err_Code := 0;
1201 END Update_CCDL;
1202 ---------------------------------------------------------------------------------
1203 FUNCTION Check_Intercompany_Project (p_project_id IN Number )
1204 RETURN BOOLEAN
1205 IS
1206
1207 Cursor C_Project_Type
1208 IS
1209 SELECT TYP.CC_PRVDR_FLAG
1210 FROM PA_PROJECTS PROJ,
1211 PA_PROJECT_TYPES TYP
1212 WHERE TYP.PROJECT_TYPE = PROJ.PROJECT_TYPE
1213 AND PROJ.PROJECT_ID = p_PROJECT_ID;
1214
1215 l_prvdr_flag Varchar2(1);
1216
1217 BEGIN
1218 open C_Project_Type;
1219
1220 fetch C_Project_Type
1221 into l_prvdr_flag;
1222
1223 close c_project_type;
1224
1225 IF NVL(l_prvdr_flag,'N') = 'Y' THEN
1226 return True;
1227 ELSE
1228 return FALSE;
1229 END IF;
1230
1231 END Check_Intercompany_Project;
1232 ---------------------------------------------------------------------------------
1233
1234 PROCEDURE Insert_DINVDTLS
1235
1236 IS
1237
1238 I Integer;
1239 v_old_stack Varchar2(650);
1240 l_EI_Date PA_PLSQL_DATATYPES.DateTabTyp;
1241
1242
1243 INV_REC PA_INVOICE_DETAIL_PKG.inv_rec_tab;
1244
1245 Cursor C_INV_DTLS
1246 IS
1247 SELECT DINVDTLS.DRAFT_INVOICE_DETAIL_ID INVOICE_DETAIL_ID,
1248 DINVDTLS.EXPENDITURE_ITEM_ID EI_ID,
1249 DINVDTLS.LINE_NUM LINE_NUM,
1250 DINVDTLS.PROJECT_ID PROJECT_ID,
1251 DINVDTLS.DENOM_CURRENCY_CODE DENOM_CURRENCY_CODE,
1252 DINVDTLS.DENOM_BILL_AMOUNT DENOM_BILL_AMOUNT,
1253 DINVDTLS.INVOICED_FLAG INVOICED_FLAG,
1254 DINVDTLS.ACCT_CURRENCY_CODE ACCT_CURRENCY_CODE,
1255 DINVDTLS.BILL_AMOUNT BILL_AMOUNT,
1256 DINVDTLS.ACCT_RATE_TYPE ACCT_RATE_TYPE,
1257 DINVDTLS.ACCT_RATE_DATE ACCT_RATE_DATE,
1258 DINVDTLS.ACCT_EXCHANGE_RATE ACCT_EXCHANGE_RATE,
1259 DINVDTLS.CC_PROJECT_ID CC_PROJECT_ID,
1260 DINVDTLS.CC_TAX_TASK_ID CC_TAX_TASK_ID,
1261 EI.Expenditure_item_date EI_DATE
1262
1263 FROM PA_DRAFT_INVOICE_DETAILS_ALL DINVDTLS,
1264 PA_EXPENDITURE_ITEMS_ALL EI
1265 WHERE DINVDTLS.project_id = G_Project_ID
1266 AND DINVDTLS.draft_invoice_detail_id <
1267 NVL(G_MIN_DRAFT_INV_DTL_ID,G_MAX_DRAFT_INV_DTL_ID)
1268 AND EI.expenditure_item_id = DINVDTLS.EXPENDITURE_ITEM_ID;
1269
1270 BEGIN
1271
1272 v_Old_Stack := G_Err_Stack;
1273 G_Err_Stack := G_Err_Stack || '->Insert_DINVDTLS';
1274 G_Err_Code := 0;
1275 G_Err_Stage:= 'Inside Insert_DINVDTLS';
1276 IF G_Use_Debug_Flag = 'Y' THEN
1277 Write_Log (G_Err_Stage);
1278 END IF;
1279
1280
1281 END Insert_DINVDTLS;
1282
1283 ---------------------------------------------------------------------------------
1284
1285 PROCEDURE Update_DINVDTLS
1286
1287 IS
1288
1289 BEGIN
1290
1291 G_Err_Code := 0;
1292 END Update_DINVDTLS;
1293 --------------------------------------------------------------------------------
1294
1295 FUNCTION Different_SOB (p_prvdr_org_id IN Number,
1296 p_recvr_org_id IN Number)
1297 Return Varchar2
1298 IS
1299
1300 l_prvdr_sob Number;
1301 l_recvr_sob Number;
1302 v_old_stack Varchar2(2000);
1303
1304 Cursor C_SOB
1305 IS
1306 SELECT IMP1.set_of_books_id,
1307 IMP2.set_of_books_id
1308 FROM PA_IMPLEMENTATIONS_ALL IMP1,
1309 PA_IMPLEMENTATIONS_ALL IMP2
1310 WHERE IMP1.org_id = p_prvdr_org_id
1311 AND IMP2.org_id = p_recvr_org_id;
1312
1313 BEGIN
1314
1315 open c_sob;
1316 fetch c_sob
1317 into l_prvdr_sob,l_recvr_sob;
1318 close c_sob;
1319
1320 IF NVL(l_prvdr_sob,-99) = NVL(l_recvr_sob,-99)THEN
1321 return 'N';
1322 ELSE
1323 return 'Y';
1324 END IF;
1325
1326
1327 END Different_SOB;
1328 ---------------------------------------------------------------------------------
1329 FUNCTION Prvdr_Proj_Converted
1330 Return Boolean
1331 IS
1332 v_table_name Varchar2(30);
1333 v_old_stack Varchar2(2000);
1334
1335 BEGIN
1336 v_Old_Stack := G_Err_Stack;
1337 G_Err_Stack := G_Err_Stack || '->Prvdr_Proj_Converted';
1338 G_Err_Code := 0;
1339 G_Err_Stage:= 'Inside Prvdr_Proj_Converted';
1340
1341 return TRUE;
1342
1343 END;
1344 ------------------------------------------------------------------------------
1345 Procedure Insert_CC_CDL
1346 IS
1347
1348 v_old_stack Varchar2(2000);
1349
1350 /** Create the array element to store the CDL records to be inserted **/
1351
1352 l_expenditure_item_id PA_PLSQL_DATATYPES.IdTabTyp;
1353 l_line_num PA_PLSQL_DATATYPES.IdTabTyp;
1354 l_line_type PA_PLSQL_DATATYPES.Char1TabTyp;
1355 l_transfer_status_code PA_PLSQL_DATATYPES.Char1TabTyp;
1356 l_quantity PA_PLSQL_DATATYPES.NumTabTyp;
1357 l_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
1358 l_transferred_date PA_PLSQL_DATATYPES.DateTabTyp;
1359 l_transfer_rejection_reason PA_PLSQL_DATATYPES.Char250TabTyp;
1360 l_amount PA_PLSQL_DATATYPES.NumTabTyp;
1361 l_batch_name PA_PLSQL_DATATYPES.Char30TabTyp;
1362 l_burdened_cost PA_PLSQL_DATATYPES.NumTabTyp;
1363 l_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
1364 l_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
1365 l_conversion_date PA_PLSQL_DATATYPES.DateTabTyp;
1366 k Integer;
1367
1368 Cursor C_CC_CDL
1369 IS
1370 SELECT CDL.expenditure_item_id EI_ID,
1371 CDL.line_num line_num,
1372 CDL.line_type line_type,
1373 decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))-G_MRC_LED),
1374 -1,
1375 CDL.transfer_status_code,'P') transfer_status_code,
1376 Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
1377 EI.expenditure_item_date,
1378 CDL.Denom_Raw_Cost, CDL.Acct_Raw_Cost,'N') Amount,
1379 CDL.quantity quantity,
1380 decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))
1381 -G_MRC_LED),-1,CDL.transferred_date,null) transferred_date,
1382 CDL.transfer_rejection_reason rejection_reason,
1383 decode(sign(NVL(CDL.gl_date,to_date('12/31/4000','MM/DD/YYYY'))
1384 -G_MRC_LED),-1,
1385 NULL,'CONVERTED') Batch_name,
1386 /*burdening enhancements*/
1387 --Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
1388 -- EI.expenditure_item_date,
1389 -- CDL.Denom_burdened_cost,CDL.Acct_Burdened_Cost,'N')
1390 Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
1391 EI.expenditure_item_date,
1392 CDL.Denom_burdened_cost+NVL(CDL.Denom_burdened_change,0)
1393 ,CDL.Acct_Burdened_Cost+NVL(CDL.Acct_Burdened_Change,0),'N')
1394 burdened_cost,
1395 Get_Converted_Amount(CDL.Denom_currency_code,CDL.Acct_Rate_Type,
1396 EI.expenditure_item_date,1,1,'Y') exchange_rate,
1397 decode (G_Use_Curr_rate,'N',G_Fixed_Date,
1398 'Y', decode(sign(EI.expenditure_item_date - G_MRC_LED),-1 ,
1399 G_Fixed_Date, EI.expenditure_item_date)
1400 ) conversion_date
1401 FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
1402 PA_EXPENDITURE_ITEMS_ALL EI
1403 WHERE CDL.expenditure_item_id = G_EI_Array(k)
1404 AND CDL.line_type <> 'I' -- burdening enhancements
1405 AND EI.expenditure_item_id = CDL.expenditure_item_id;
1406
1407
1408 Begin
1409 v_Old_Stack := G_Err_Stack;
1410 G_Err_Stack := G_Err_Stack || '->Insert_CC_CDL';
1411 G_Err_Code := 0;
1412 G_Err_Stage:= 'Inside Insert_CC_CDL';
1413 j := 1;
1414
1415 Exception
1416 WHEN DUP_VAL_ON_INDEX THEN
1417 G_Err_Code := -1;
1418 G_Err_Stage := 'PA_MRC_INS_DUP';
1419 END Insert_CC_CDL;
1420 ------------------------------------------------------------------------------
1421 PROCEDURE Update_CC_CDL
1422 IS
1423 v_Old_Stack VARCHAR2(650);
1424 BEGIN
1425 v_Old_Stack := G_Err_Stack;
1426 G_Err_Stack := G_Err_Stack || '->Update_CC_CDL';
1427 G_Err_Code := 0;
1428 G_Err_Stage:= 'Inside Update_CC_CDL ';
1429 /** This part is not needed because both original transactions
1430 and reversed transactions use either expenditure item date
1431 or G_Fixed_Date for conversion so absolute values of both
1432 converted amounts should be same **/
1433 END Update_CC_CDL;
1434 -------------------------------------------------------------------------------
1435 PROCEDURE insert_CC_exp_items( x_Project_ID IN Number,
1436 x_Rep_SOB_ID IN Number)
1437 IS
1438 l_raw_cost PA_PLSQL_DATATYPES.NumTabtyp;
1439 l_Quantity PA_PLSQL_DATATYPES.NumTabTyp;
1440 l_burdened_cost PA_PLSQL_DATATYPES.NumTabtyp;
1441 l_raw_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1442 l_accrued_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1443 l_adjusted_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1444 l_forecast_revenue PA_PLSQL_DATATYPES.NumTabTyp;
1445 l_bill_amount PA_PLSQL_DATATYPES.NumTabTyp;
1446 l_net_zero_adjustment_flag PA_PLSQL_DATATYPES.Char1TabTyp;
1447 l_transferred_from_exp_item_id PA_PLSQL_DATATYPES.IdTabTyp;
1448 l_transfer_price PA_PLSQL_DATATYPES.NumTabtyp;
1449 l_cost_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
1450 l_rev_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
1451 l_cost_conversion_date PA_PLSQL_DATATYPES.DateTabTyp;
1452 l_cost_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
1453 l_raw_cost_rate PA_PLSQL_DATATYPES.NumTabtyp;
1454 l_burdened_cost_rate PA_PLSQL_DATATYPES.NumTabtyp;
1455 l_bill_rate PA_PLSQL_DATATYPES.NumTabtyp;
1456 l_accrual_rate PA_PLSQL_DATATYPES.NumTabtyp;
1457 l_adjusted_rate PA_PLSQL_DATATYPES.NumTabtyp;
1458 l_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabtyp;
1459 l_tp_exchange_date PA_PLSQL_DATATYPES.DateTabTyp;
1460 l_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
1461
1462 v_Old_Stack VARCHAR2(650);
1463 k Integer;
1464
1465 Cursor C_exp_items IS
1466 Select expenditure_item_id,
1467 Denom_raw_cost,
1468 quantity,
1469 Denom_burdened_cost,
1470 raw_revenue,
1471 accrued_revenue,
1472 adjusted_revenue,
1473 forecast_revenue,
1474 bill_amount,
1475 net_zero_adjustment_flag,
1476 transferred_from_exp_item_id,
1477 denom_transfer_price,
1478 cc_cross_charge_code,
1479 project_exchange_rate,
1480 recvr_org_id
1481
1482 From PA_Expenditure_Items_ALL
1483 Where expenditure_item_id = G_EI_Array(k);
1484
1485 Begin
1486 v_Old_Stack := G_Err_Stack;
1487 G_Err_Stack := G_Err_Stack || '->Insert_cc_exp_items';
1488 G_Err_Code := 0;
1489 G_Err_Stage:= 'Inside Insert_CC_exp_items ';
1490 IF G_Use_Debug_Flag = 'Y' THEN
1491 Write_Log(G_Err_Stage);
1492 END IF;
1493
1494 EXCEPTION
1495
1496 WHEN DUP_VAL_ON_INDEX THEN
1497 G_Err_Code := -1;
1498 G_Err_Stage := 'PA_MRC_INS_DUP';
1499 Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');
1500
1501 WHEN OTHERS THEN
1502 G_Err_Code := -1;
1503 G_Err_Stage := 'Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']';
1504 Write_Log ('Exception in Insert_CC_exp_items['||SQLCODE||SQLERRM||']');
1505
1506
1507 END Insert_CC_exp_items;
1508 -------------------------------------------------------------------------------
1509 PROCEDURE update_cc_exp_items ( x_Project_ID IN Number,
1510 x_Rep_SOB_ID IN Number)
1511 IS
1512
1513 prev_orig_ei Number := 0;
1514 split_flag Varchar2(1) := 'N';
1515 split_amt Number := 0;
1516 split_raw_cost Number := 0;
1517 k integer;
1518 split_burden_cost Number := 0;
1519
1520 v_Old_Stack VARCHAR2(650);
1521
1522 Begin
1523 v_Old_Stack := G_Err_Stack;
1524 G_Err_Stack := G_Err_Stack || '->Update_cc_exp_items';
1525 G_Err_Code := 0;
1526 G_Err_Stage:= 'Inside Update_cc_exp_items ';
1527 END Update_cc_exp_items ;
1528 -------------------------------------------------------------------------------
1529 PROCEDURE Insert_CC_CCDL
1530 IS
1531 l_rep_rsob_id PA_PLSQL_DATATYPES.IDTabTyp;
1532 l_org_id PA_PLSQL_DATATYPES.IDTabTyp;
1533 l_rcurrency_code PA_PLSQL_DATATYPES.Char15TabTyp;
1534 l_cc_dist_line_id PA_PLSQL_DATATYPES.IDTabTyp;
1535 l_expenditure_item_id PA_PLSQL_DATATYPES.IDTabTyp;
1536 l_line_num PA_PLSQL_DATATYPES.IDTabTyp;
1537 l_cdl_line_num PA_PLSQL_DATATYPES.NumTabTyp;
1538 l_acct_tp_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
1539 l_prvdr_cost_reclass_code PA_PLSQL_DATATYPES.Char240TabTyp;
1540 l_expenditure_item_date PA_PLSQL_DATATYPES.DateTabTyp;
1541 l_acct_tp_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
1542 l_denom_transfer_price PA_PLSQL_DATATYPES.NumTabTyp;
1543 l_denom_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
1544 l_dist_line_id_reversed PA_PLSQL_DATATYPES.IDTabTyp;
1545 l_line_type PA_PLSQL_DATATYPES.Char2TabTyp;
1546 v_old_stack Varchar2(650);
1547 i Integer;
1548 l_use_debug_flag Boolean;
1549 k Integer;
1550
1551
1552
1553 BEGIN
1554
1555 v_Old_Stack := G_Err_Stack;
1556 G_Err_Stack := G_Err_Stack || '->Insert_CC_CCDL';
1557 G_Err_Code := 0;
1558 G_Err_Stage:= 'Inside Insert_CC_CCDL ';
1559 IF G_Use_Debug_Flag = 'Y' THEN
1560 Write_Log (G_Err_Stage);
1561 END IF;
1562
1563 i := 1;
1564
1565
1566 END Insert_CC_CCDL;
1567 ------------------------------------------------------------------------
1568 Function Get_Rate_Type (x_rate_type IN Varchar2,
1569 x_conversion_date IN Date)
1570
1571 return Varchar2
1572 IS
1573
1574 v_rate_type VARCHAR2(30);
1575
1576 BEGIN
1577
1578 IF x_rate_type = 'USER' THEN
1579 v_rate_type := x_rate_type;
1580 ELSE
1581 IF G_Use_Curr_Rate = 'N' THEN
1582 v_rate_type := G_Fixed_Type;
1583 ELSE
1584 IF (sign(NVL(x_conversion_date,to_date('12/31/4000',
1585 'MM/DD/YYYY'))
1586 - G_MRC_LED )) = -1 THEN
1587 /** Past transactions **/
1588 v_rate_type := G_Fixed_Type;
1589 ELSE
1590 /** Future Transactions **/
1591 v_rate_type := G_Daily_Type;
1592 END IF;
1593 END IF;
1594 END IF;
1595 return v_rate_type;
1596 END Get_Rate_Type;
1597 ------------------------------------------------------------------------
1598 Function Get_Rate_Date (x_conversion_date IN Date)
1599 return Date
1600 IS
1601 v_rate_date Date;
1602 BEGIN
1603 IF G_Use_Curr_Rate = 'N' THEN
1604 v_rate_date := G_Fixed_Date;
1605 ELSE
1606 IF (sign(NVL(x_conversion_date,to_date('12/31/4000',
1607 'MM/DD/YYYY'))
1608 - G_MRC_LED )) = -1 THEN
1609 /** Past transactions **/
1610 v_rate_date := G_Fixed_Date;
1611 ELSE
1612 /** Future Transactions **/
1613 v_rate_date := x_conversion_date;
1614 END IF;
1615 END IF;
1616 return v_rate_date;
1617 END Get_Rate_Date;
1618 ------------------------------------------------------------------------
1619
1620 END PA_MC_UPG;