1 PACKAGE PA_MC_UPG AS
2 --$Header: PAXMCUPS.pls 120.1 2005/06/08 16:21:40 vgade noship $
3
4 PROCEDURE Upgrade_MRC ( x_errbuf OUT NOCOPY VARCHAR2,
5 x_retcode OUT NOCOPY VARCHAR2,
6 x_Pri_SOB IN NUMBER,
7 x_Rep_SOB IN NUMBER,
8 x_From_Prj_Num IN VARCHAR2,
9 x_To_Prj_Num IN VARCHAR2,
10 x_Rounding IN VARCHAR2 DEFAULT 'N',
11 x_Use_Curr_Rate IN VARCHAR2 DEFAULT 'N',
12 x_Debug_Flag IN VARCHAR2 DEFAULT 'N',
13 x_include_closed_prj IN VARCHAR2 DEFAULT 'N',
14 x_Process IN VARCHAR2 DEFAULT 'PLSQL',
15 x_Validation_Check IN VARCHAR2 DEFAULT 'Y'
16 );
17
18 /** Upgrade_MRC : Main procedure for MRC upgrade.
19 Parameters are passed from the script.
20 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
21 G_Err_Stage contains the error msg.
22 Ora Errors will be raised as exceptions.
23 **/
24
25
26 PROCEDURE Validate_Params;
27
28 /** Validate_Params : Procedure to validate parameters.
29 Parameters should be available as global vars.
30 While validating certain variables are set.
31 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
32 G_Err_Stage contains the error msg.
33 Ora Errors will be raised as exceptions.
34 **/
35
36
37 PROCEDURE Init_Upgrade;
38
39 /** Init_Upgrade : Procedure to initialize variables.
40 Works on global vars. Sets variables as needed.
41 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
42 G_Err_Stage contains the error msg.
43 Ora Errors will be raised as exceptions.
44 **/
45
46 PROCEDURE cache_exchange_rates;
47
48 /** Cache_Exchange_Rates :
49 This procedure will cache the exchange rates for various transaction
50 Currencies and the Reporting currency before doing the MRC Upgrade. If
51 It is unable to find rates for any currency, It will list out those
52 currencies, so that the rates for them can be populated and this procedure
53 is rerun
54 **/
55
56 PROCEDURE insert_temp_rates ( x_currency_code IN VARCHAR2);
57
58 /** insert_temp_rates : Procedure to Insert a transaction currency along with its
59 Fixed rate on the Initial MRC Date, into the cache table.
60 **/
61
62 PROCEDURE Validate_SOB_Assign ( x_Pri_SOB_ID IN NUMBER,
63 x_Rep_SOB_ID IN NUMBER);
64
65 /** Validate_SOB_Assign : Procedure to validate if the Primary and Reporting
66 Set of books assignment is valid. If yes, then set the variables,
67 else return with -1.
68 G_Err_Code = 0 - Success, -1 - Error ( Abort ).
69 G_Return_Msg contains the error msg.
70 Ora Errors will be raised as exceptions.
71 **/
72
73 Function Check_Future_Record
74 Return BOOLEAN;
75
76 /** Check_Future_Record checks availability of future record Table_Name = 'FUTURE' for the set of books and returns TRUE if available, FALSE otherwise **/
77
78 Function Validate_First_MRC_Period
79 RETURN BOOLEAN;
80 /** First GL_Period should be equal to First_MRC_Period. Return TRUE on success else FALSE **/
81
82 PROCEDURE Insert_History_Rec ( x_Table_Name IN VARCHAR2,
83 x_Project_ID IN NUMBER,
84 x_Status IN VARCHAR2,
85 x_Status_Value IN VARCHAR2);
86
87 /** Insert_History_Rec : Procedure to insert a history rec.
88 x_table_name : Table to insert rec for.
89 x_Project_ID : Project to insert recoed for.
90 x_Status : 'CONVERSION' or 'ROUNDING' status
91 x_Status_Value : 'C' Converted, 'S' In Process or NULL.
92 G_Return_Code = 0 - Success, -1 - Error ( Abort ).
93 G_Err_Stage contains the error msg.
94 Ora Errors will be raised as exceptions.
95 **/
96
97
98
99 FUNCTION Get_Project_Number ( x_Project_Range IN VARCHAR2 )
100 RETURN VARCHAR2;
101
102 /** Get_Project_Number : Function to get the project number MIN or MAX.
103 x_Project_Range can be MIN or MAX. Accordingly, a minimum or
104 a maximum project number is returned. Project Number is Unique
105 across Orgs. Hence PA_Projects_ALL used.
106 Ora Errors will be raised as exceptions.
107 **/
108
109
110 FUNCTION Validate_SOB ( x_SOB_ID IN NUMBER,
111 l_Currency_Code OUT NOCOPY VARCHAR2)
112 RETURN VARCHAR2;
113
114 /** Validate_SOB : Function to validate the set of books name passed.
115 Returns Set of Books ID, if found, Else -1. ( ABORT )
116 Ora Errors will be raised as exceptions.
117 **/
118
119
120 FUNCTION Get_User_Lock ( x_Lock_Name IN VARCHAR2,
121 x_Lock_Mode IN NUMBER default 6,
122 x_Commit_Mode IN BOOLEAN default FALSE )
123 RETURN VARCHAR2;
124
125 /** Get_User_Lock : Function to acquire a user lock.
126 x_lock_name : name of the lock.
127 x_lock_mode : Mode of the lock ( Exclusive,..)
128 x_commit_mode : Rls with commit or not
129 Returns : lock handle if successful in acquiring lock
130 else NULL - Cannot acquire lock.
131 Ora Errors will be raised as exceptions.
132 **/
133
134
135 FUNCTION Rls_User_Lock ( x_Lock_Hndl IN VARCHAR2 )
136 RETURN NUMBER;
137
138 /** Rls_User_Lock : Function to release user lock.
139 x_Lock_Hndl : The lock handle obtained earlier.
140 Returns 0 - success, -1 - Error. ( Abort ).
141 **/
142
143
144
145 FUNCTION Get_Table_Status ( x_Table_Name IN VARCHAR2,
146 x_Project_ID IN NUMBER,
147 x_Status IN VARCHAR2 )
148 RETURN VARCHAR2;
149
150 /** Get_Table_Status : Function to get the table status for the project from
151 the MRC upgrade history table.
152 x_table_name : table to be checked for.
153 x_Project_ID : Project to be checked for.
154 x_Status : Status to check for - Conversion or Rounding.
155 Can have values - CONVERSION or ROUNDING.
156 Returns : 'C' = Converted, NULL = Not done yet.
157 **/
158
159
160 PROCEDURE Convert_Table ( x_Table_Name IN VARCHAR2);
161
162 /** Convert_Table : Procedure to convert table.
163 x_Table_Name: Table to be converted.
164 **/
165
166 PROCEDURE Insert_Recs ( x_Table_Name IN VARCHAR2);
167
168 PROCEDURE Update_Recs ( x_Table_Name IN VARCHAR2);
169
170 PROCEDURE Insert_CDL;
171
172 PROCEDURE Insert_CRDL;
173
174 PROCEDURE Insert_ERDL;
175
176 PROCEDURE Insert_DR;
177
178 PROCEDURE Insert_Event;
179
180 PROCEDURE Insert_AL;
181
182 PROCEDURE Insert_ALD;
183
184 Procedure Insert_DINV( x_Project_ID IN NUMBER,
185 x_Rep_SOB_ID IN NUMBER);
186
187 PROCEDURE Insert_exp_items( x_Project_ID IN Number,
188 x_Rep_SOB_ID IN Number);
189
190 PROCEDURE Update_CDL;
191
192 PROCEDURE Update_CRDL;
193
194 PROCEDURE Update_ERDL;
195
196 PROCEDURE Update_DR;
197
198 PROCEDURE Update_EVENT;
199
200 PROCEDURE Update_AL;
201
202 PROCEDURE Update_ALD;
203
204 PROCEDURE Update_DINV;
205
206 PROCEDURE Insert_CCDL;
207
208 PROCEDURE Insert_DINVDTLS;
209
210 PROCEDURE Update_CCDL;
211
212 PROCEDURE Update_DINVDTLS;
213
214 PROCEDURE Update_exp_items( x_Project_ID IN Number,
215 x_Rep_SOB_ID IN Number);
216 -------------------------------------------------------------------------------
217 /** Important: Dependencies: Get_Rate_Type,Get_Rate_Date. If you modify any
218 logic in get_converted_amount check if the logic has any
219 impact on those two dependent functions **/
220 FUNCTION Get_Converted_Amount ( x_Denom_Cur_Code IN Varchar2,
221 x_Acct_Rate_Type IN Varchar2,
222 x_Conversion_Date IN Date,
223 x_Amount IN Number,
224 x_Acct_Amt IN Number,
225 x_Rate IN Varchar2 DEFAULT 'N'
226 )
227 RETURN NUMBER;
228
229 PRAGMA RESTRICT_REFERENCES (Get_Converted_Amount,WNDS);
230
231 PROCEDURE Get_Cached_Rate ( x_curr_code IN VARCHAR2,
232 x_denom_rate OUT NOCOPY NUMBER,
233 x_num_rate OUT NOCOPY NUMBER);
234
235 PROCEDURE Write_Log ( x_Msg IN VARCHAR2);
236
237 PROCEDURE Write_Out ( x_Msg IN VARCHAR2);
238
239 PROCEDURE Submit_Report;
240
241 FUNCTION Check_Intercompany_Project (p_project_id IN Number )
242 RETURN BOOLEAN;
243
244 FUNCTION Different_SOB (p_prvdr_org_id IN Number,
245 p_recvr_org_id IN Number)
246
247 Return Varchar2;
248
249 PRAGMA RESTRICT_REFERENCES (Different_SOB,WNDS);
250 ------------------------------------------------------------------------------
251 FUNCTION Prvdr_Proj_Converted
252 Return Boolean;
253 -------------------------------------------------------------------------
254 Procedure Insert_CC_CDL;
255 -------------------------------------------------------------------------
256 PROCEDURE Update_CC_CDL;
257 -------------------------------------------------------------------------
258 PROCEDURE Insert_CC_exp_items( x_Project_ID IN Number,
259 x_Rep_SOB_ID IN Number);
260 -------------------------------------------------------------------------
261 PROCEDURE update_cc_exp_items ( x_Project_ID IN Number,
262 x_Rep_SOB_ID IN Number) ;
263 -------------------------------------------------------------------------
264 Procedure Insert_CC_CCDL;
265 ------------------------------------------------------------------------
266 Function Get_Rate_Type (x_rate_type IN Varchar2,
267 x_conversion_date IN Date)
268 return Varchar2;
269 ------------------------------------------------------------------------
270 Function Get_Rate_Date (x_conversion_date IN Date)
271 return Date;
272 ------------------------------------------------------------------------
273
274 END PA_MC_UPG;