6 If the rate for the passed date is not defined then the rquirement is to get the rate of max(date) defined before the passed_date */
1 PACKAGE pa_multi_currency_txn AUTHID CURRENT_USER AS
2 --$Header: PAXMCTXS.pls 120.1.12000000.2 2007/06/26 11:38:31 sugupta ship $
3
4
5 /* This global variable is introduced to handle specific method of returning exchange rate for 'WORKPLAN' model
7
8 G_calling_module varchar2(30) := NULL;
9
10 /*----------------------------------------------------------------------------*/
11 -- Start of Comments
12 -- API Name : get_projfunc_cost_rate_type
13 -- Type : Public
14 -- Pre-Reqs : None
15 -- Type : Procedure
16 -- Function : returns the project functional rate type
17 -- Parameters :
18 -- IN
19 -- P_task_id NUMBER
20 -- IN/OUT
21 -- P_projfunc_currency_code VARCHAR2
22 -- P_projfunc_cost_rate_type VARCHAR2
23 --
24 --------------
25 -- EPP Changes
26 --------------
27 -- This procedure was previously called as get_project_rate_type.
28
29 /*----------------------------------------------------------------------------*/
30
31 /*
32 * PROCEDURE get_project_rate_type (
33 * P_task_id IN NUMBER ,
34 * P_project_currency_code IN OUT VARCHAR2 ,
35 * P_project_rate_type IN OUT VARCHAR2 ) ;
36 *
37 * pragma RESTRICT_REFERENCES(get_project_rate_type,WNDS,WNPS);
38 */
39 PROCEDURE get_projfunc_cost_rate_type (
40 P_task_id IN NUMBER ,
41 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL,
42 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS',
43 p_structure_version_id IN NUMBER DEFAULT NULL,
44 P_projfunc_currency_code IN OUT NOCOPY VARCHAR2 ,
45 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 ) ;
46
47 --pragma RESTRICT_REFERENCES(get_projfunc_cost_rate_type,WNDS,WNPS);
48
49 /*----------------------------------------------------------------------------*/
50 -- Start of Comments
51 -- API Name : get_def_projfunc_cst_rate_type
52 -- Type : Public
53 -- Pre-Reqs : None
54 -- Type : Procedure
55 -- Function : returns the project functional rate type
56 -- Parameters :
57 -- IN
58 -- P_task_id NUMBER
59 -- IN/OUT
60 -- P_projfunc_currency_code VARCHAR2
61 -- P_projfunc_cost_rate_type VARCHAR2
62 --------------
63 -- EPP Changes
64 --------------
65 -- This procedure was previously called as get_default_project_rate_type.
66
67 /*----------------------------------------------------------------------------*/
68
69 /*
70 * PROCEDURE get_default_project_rate_type ( P_task_id IN NUMBER ,
71 * P_project_currency_code IN OUT VARCHAR2 ,
72 * P_project_rate_type IN OUT VARCHAR2 ) ;
73 * pragma RESTRICT_REFERENCES(get_default_project_rate_type, WNDS, WNPS);
74 */
75 PROCEDURE get_def_projfunc_cst_rate_type (
76 P_task_id IN NUMBER ,
77 P_projfunc_currency_code IN OUT NOCOPY VARCHAR2 ,
78 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 ) ;
79 --pragma RESTRICT_REFERENCES(get_def_projfunc_cst_rate_type, WNDS, WNPS);
80 /*----------------------------------------------------------------------------*/
81 -- Start of Comments
82 -- API Name : get_proj_curr_code_sql
83 -- Type : Public
84 -- Pre-Reqs : None
85 -- Type : Function
86 -- Function : returns the project Currency Code
87 -- Purity : WNDS, WNPS
88 -- Parameters :
89 -- IN
90 -- P_project_id NUMBER
91 -- RETURNS
92 -- P_project_currency_code VARCHAR2
93
94 /*----------------------------------------------------------------------------*/
95
96 FUNCTION get_proj_curr_code_sql( P_project_id NUMBER )
97 RETURN VARCHAR2 ;
98 --pragma RESTRICT_REFERENCES(get_proj_curr_code_sql,WNDS,WNPS);
99
100 /*----------------------------------------------------------------------------*/
101 -- Start of Comments
102 -- API Name : get_projfunc_cost_rate_date
103 -- Type : Public
104 -- Pre-Reqs : None
105 -- Type : Procedure
106 -- Function : returns the project functional rate date
107 -- Parameters :
108 -- IN
109 -- P_task_id NUMBER
110 -- P_EI_date DATE
111 -- IN/OUT
112 -- P_projfunc_cost_rate_date VARCHAR2
113 --------------
114 -- EPP Changes
115 --------------
116 -- This procedure was previously called as get_project_rate_date.
117
118 /*----------------------------------------------------------------------------*/
119
120 /*
121 * PROCEDURE get_project_rate_date ( P_task_id IN NUMBER ,
122 * P_EI_date IN DATE ,
123 * P_project_rate_date IN OUT DATE );
124 *
128 P_task_id IN NUMBER ,
125 * pragma RESTRICT_REFERENCES(get_project_rate_date,WNDS);
126 */
127 PROCEDURE get_projfunc_cost_rate_date (
129 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
130 P_EI_date IN DATE ,
131 P_structure_version_id IN NUMBER DEFAULT NULL,
132 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS' ,
133 P_projfunc_cost_rate_date IN OUT NOCOPY DATE );
134
135 --pragma RESTRICT_REFERENCES(get_projfunc_cost_rate_date,WNDS); /**CBGA - removed WNPS**/
136
137
138 /*----------------------------------------------------------------------------*/
139 -- Start of Comments
140 -- API Name : get_def_projfunc_cst_rate_date
141 -- Type : Public
142 -- Pre-Reqs : None
143 -- Type : Procedure
144 -- Function : returns the project functional rate date
145 -- Parameters :
146 -- IN
147 -- P_task_id NUMBER
148 -- P_EI_date DATE
149 -- IN/OUT
150 -- P_projfunc_cost_rate_date VARCHAR2
151 --------------
152 -- EPP Changes
153 --------------
154 -- This procedure was previously called as get_default_project_rate_date.
155
156 /*----------------------------------------------------------------------------*/
157
158 /*
159 * PROCEDURE get_default_project_rate_date ( P_task_id IN NUMBER ,
163 */
160 * P_EI_date IN DATE ,
161 * P_project_rate_date IN OUT DATE );
162 * pragma RESTRICT_REFERENCES(get_default_project_rate_date, WNDS, WNPS);
164 PROCEDURE get_def_projfunc_cst_rate_date (
165 P_task_id IN NUMBER ,
166 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
167 P_EI_date IN DATE ,
168 p_structure_version_id IN NUMBER DEFAULT NULL,
169 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS' ,
170 P_projfunc_cost_rate_date IN OUT NOCOPY DATE );
171 --pragma RESTRICT_REFERENCES(get_def_projfunc_cst_rate_date, WNDS, WNPS);
172 /*----------------------------------------------------------------------------*/
173 -- Start of Comments
174 -- API Name : get_acct_rate_date
175 -- Type : Public
176 -- Pre-Reqs : None
177 -- Type : Procedure
178 -- Function : returns the acct rate date
179 -- Parameters :
180 -- IN
181 -- P_EI_date DATE
182 -- IN/OUT
183 -- P_acct_rate_date VARCHAR2
184
185 /*----------------------------------------------------------------------------*/
186
187 PROCEDURE get_acct_rate_date (
188 P_EI_date IN DATE ,
189 P_acct_rate_date IN OUT NOCOPY DATE );
190
191 --pragma RESTRICT_REFERENCES(get_acct_rate_date,WNDS); /**CBGA - removed WNPS**/
192
193 /*----------------------------------------------------------------------------*/
194 -- Start of Comments
195 -- API Name : get_default_acct_rate_date
196 -- Type : Public
197 -- Pre-Reqs : None
198 -- Type : Procedure
199 -- Function : returns the acct rate date
200 -- Parameters :
201 -- IN
202 -- P_EI_date DATE
203 -- IN/OUT
204 -- P_acct_rate_date VARCHAR2
205
206 /*----------------------------------------------------------------------------*/
207 PROCEDURE get_default_acct_rate_date ( P_EI_date IN DATE ,
208 P_acct_rate_date IN OUT NOCOPY DATE );
209 --pragma RESTRICT_REFERENCES(get_default_acct_rate_date, WNDS, WNPS);
210
211 /*----------------------------------------------------------------------------*/
212 -- Start of Comments
213 -- API Name : get_currency_amounts
214 -- Type : Public
215 -- Pre-Reqs : None
216 -- Type : Procedure
217 -- Function : returns the project and functional raw costs.
218 -- If the conversion attributes are not passed in then
219 -- this procedure will derive the conversion attributes
220 -- from the project/task/implementation option setup tables.
221 -- When functional and project currencies are same, this
222 -- procedure will make sure the respective conversion attributes
223 -- are identical.
224 -- Exceptions : Conversion Type 'User'Not allowed
225 -- Conversion rate not available for conversion type 'User'
226 -- Cannot find conversion rate in gl.
227 -- Invalid currency code
231 -- P_EI_date DATE
228 -- Parameters :
229 -- IN
230 -- P_task_id NUMBER
232 -- P_denom_raw_cost NUMBER
233 -- P_denom_curr_code VARCHAR2
234 -- P_acct_curr_code VARCHAR2
235 -- P_project_curr_code VARCHAR2
236 -- P_accounted_flag VARCHAR2
237 -- IN/OUT
238 -- P_project_rate_type VARCHAR2
239 -- P_project_rate_date DATE
240 -- P_project_exchange_rate NUMBER
241 -- P_acct_rate_date DATE
242 -- P_acct_rate_type VARCHAR2
243 -- P_acct_exchange_rate NUMBER
244 -- P_raw_cost NUMBER
245 -- P_status VARCHAR2
246 -- P_stage NUMBER
247 --------------
248 -- EPP Changes
249 --------------
250 -- Added 6 more parameters.
251 -- P_project_raw_cost
252 -- P_projfunc_curr_code
253 -- P_projfunc_cost_rate_type
254 -- P_projfunc_cost_rate_date
255 -- P_projfunc_exch_rate
256 -- P_raw_cost
257 -- P_system_linkage
258 /** The same API is called from Transactions Adjustments and Forecast Items
259 * so new parameters are added to handle the same API when it is called from
260 * Forecast module
261 * The P_calling_module = 'GET_CURR_AMOUNTS' for Transactions
262 * P_calling_module = 'FORECAST' for FIs
263 * P_calling_module = 'WORKPLAN' for Workplan
264 * Defaulting System_Linkage_Function to 'NER' meaning Not-ER. Special
265 * handling is required only for ER transactions. Hence the above.
266 **/
267 PROCEDURE get_currency_amounts (
268 /** Added the following new params for the FI calls **/
269 P_project_id IN NUMBER DEFAULT NULL,
270 P_exp_org_id IN NUMBER DEFAULT NULL,
271 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS',
272 /** End of FI changes **/
273 P_task_id IN NUMBER,
274 P_EI_date IN DATE,
275 P_denom_raw_cost IN NUMBER,
276 P_denom_curr_code IN VARCHAR2,
277 P_acct_curr_code IN VARCHAR2,
278 P_accounted_flag IN VARCHAR2 DEFAULT 'N',
279 P_acct_rate_date IN OUT NOCOPY DATE,
280 P_acct_rate_type IN OUT NOCOPY VARCHAR2,
281 P_acct_exch_rate IN OUT NOCOPY NUMBER,
282 P_acct_raw_cost IN OUT NOCOPY NUMBER,
283 P_project_curr_code IN VARCHAR2,
284 P_project_rate_type IN OUT NOCOPY VARCHAR2 ,
285 P_project_rate_date IN OUT NOCOPY DATE,
286 P_project_exch_rate IN OUT NOCOPY NUMBER,
287 P_project_raw_cost IN OUT NOCOPY NUMBER,
288 P_projfunc_curr_code IN VARCHAR2,
289 P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 ,
290 P_projfunc_cost_rate_date IN OUT NOCOPY DATE,
291 P_projfunc_cost_exch_rate IN OUT NOCOPY NUMBER,
292 P_projfunc_raw_cost IN OUT NOCOPY NUMBER,
293 P_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE DEFAULT 'NER',
294 P_structure_version_id IN NUMBER DEFAULT NULL,
295 P_status OUT NOCOPY VARCHAR2,
296 P_stage OUT NOCOPY NUMBER,
297 P_Po_Line_ID IN NUMBER DEFAULT NULL /* Bug : 3535935 */
298 ) ;
299
300 --pragma RESTRICT_REFERENCES(get_currency_amounts,WNDS); /**CBGA removed WNPS**/
301 /*----------------------------------------------------------------------------*/
302 /*
303 * IC related changes:
307 * Labor program.
304 * New procedure added to perform MC and IC processings necessary
305 * in the cost distribution programs. In addition to that, this new
306 * procedure will take care of calling the client extension for
308 */
309 PROCEDURE Perform_MC_and_IC_processing(
310 P_Sys_Link IN VARCHAR2,
311 P_Request_Id IN NUMBER,
312 P_Source OUT NOCOPY VARCHAR2,
313 P_MC_IC_status OUT NOCOPY NUMBER,
314 P_Update_Count OUT NOCOPY NUMBER);
315 /*----------------------------------------------------------------------------*/
316 -- Start of Comments
317 -- API Name : get_proj_rate_type
318 -- Type : Public
319 -- Pre-Reqs : None
320 -- Type : Procedure
321 -- Function : returns the project rate type
322 -- Parameters :
323 -- IN
324 -- P_task_id NUMBER
325 -- IN/OUT
326 -- P_project_currency_code VARCHAR2
327 -- P_project_rate_type VARCHAR2
328 --
329 /*----------------------------------------------------------------------------*/
330
331 PROCEDURE get_proj_rate_type ( P_task_id IN NUMBER ,
332 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL,
333 P_structure_version_id IN NUMBER DEFAULT NULL,
334 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS',
335 P_project_currency_code IN OUT NOCOPY VARCHAR2 ,
336 P_project_rate_type IN OUT NOCOPY VARCHAR2 ) ;
337
338 --pragma RESTRICT_REFERENCES(get_proj_rate_type,WNDS,WNPS);
339 /*----------------------------------------------------------------------------*/
340 -- Start of Comments
341 -- API Name : get_proj_rate_date
342 -- Type : Public
343 -- Pre-Reqs : None
344 -- Type : Procedure
345 -- Function : returns the project rate date
346 -- Parameters :
347 -- IN
348 -- P_task_id NUMBER
349 -- P_EI_date DATE
350 -- IN/OUT
351 -- P_project_rate_date VARCHAR2
352 /*----------------------------------------------------------------------------*/
353
354 PROCEDURE get_proj_rate_date ( P_task_id IN NUMBER ,
355 P_project_id IN pa_projects_all.project_id%TYPE DEFAULT NULL ,
356 P_EI_date IN DATE ,
357 p_structure_version_id IN NUMBER DEFAULT NULL,
358 P_calling_module IN VARCHAR2 DEFAULT 'GET_CURR_AMOUNTS' ,
359 P_project_rate_date IN OUT NOCOPY DATE );
360
361 --pragma RESTRICT_REFERENCES(get_proj_rate_date,WNDS);
362 /*----------------------------------------------------------------------------*/
363 /** Added new params p_project_id, p_exp_org_id to call this api from FORECAST modules
364 * when p_calling_module = 'FORECAST' p_task_id will be null and p_ei_date = FI date
365 **/
366 PROCEDURE get_currency_attributes (
367 P_project_id IN pa_projects_all.project_id%type default NULL,
368 P_exp_org_id IN pa_projects_all.org_id%type default NULL,
369 P_task_id IN pa_expenditure_items_all.task_id%TYPE,
370 P_ei_date IN pa_expenditure_items_all.expenditure_item_date%TYPE,
371 P_calling_module IN VARCHAR2,
372 P_denom_curr_code IN pa_expenditure_items_all.denom_currency_code%TYPE,
373 P_accounted_flag IN VARCHAR2 DEFAULT 'N',
377 X_acct_exch_rate IN OUT NOCOPY pa_expenditure_items_all.acct_exchange_rate%TYPE,
374 P_acct_curr_code IN pa_expenditure_items_all.acct_currency_code%TYPE,
375 X_acct_rate_date IN OUT NOCOPY pa_expenditure_items_all.acct_rate_date%TYPE,
376 X_acct_rate_type IN OUT NOCOPY pa_expenditure_items_all.acct_rate_type%TYPE,
378 P_project_curr_code IN pa_expenditure_items_all.project_currency_code%TYPE,
379 X_project_rate_date IN OUT NOCOPY pa_expenditure_items_all.project_rate_date%TYPE,
380 X_project_rate_type IN OUT NOCOPY pa_expenditure_items_all.project_rate_type%TYPE ,
381 X_project_exch_rate IN OUT NOCOPY pa_expenditure_items_all.project_exchange_rate%TYPE,
382 P_projfunc_curr_code IN pa_expenditure_items_all.projfunc_currency_code%TYPE,
383 X_projfunc_cost_rate_date IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_date%TYPE,
384 X_projfunc_cost_rate_type IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_rate_type%TYPE ,
385 X_projfunc_cost_exch_rate IN OUT NOCOPY pa_expenditure_items_all.projfunc_cost_exchange_rate%TYPE,
386 P_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE,
387 P_structure_version_id IN NUMBER DEFAULT NULL,
388 X_status OUT NOCOPY VARCHAR2,
389 X_stage OUT NOCOPY NUMBER) ;
390
391 --pragma RESTRICT_REFERENCES(get_currency_attributes,WNDS); /**CBGA removed WNPS**/
392
393 /*----------------------------------------------------------------------------*/
394
395 END pa_multi_currency_txn ;