[Home] [Help]
PACKAGE BODY: APPS.AR_ARXFXGL_XMLP_PKG
Source
1 PACKAGE BODY AR_ARXFXGL_XMLP_PKG AS
2 /* $Header: ARXFXGLB.pls 120.0 2007/12/27 13:51:15 abraghun noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 /*SRW.USER_EXIT('FND SRWINIT');*/null;
8
9
10 /*srw.message ('100', 'DEBUG: BeforeReport +');*/null;
11
12
13
14
15 /*srw.message ('100', 'DEBUG: Call Build_Customer_Details');*/null;
16
17 Build_Customer_Details;
18
19
20
21
22 /*srw.message ('100', 'DEBUG: Call Build_Location_Details');*/null;
23
24 Build_Location_Details;
25
26
27
28
29 /*srw.message ('100', 'DEBUG: Call Build_Rate_Type_Details');*/null;
30
31 Build_Rate_Type_Details;
32
33
34
35
36 /*srw.message ('100', 'DEBUG: Call Build_Receipt_Date_Details');*/null;
37
38
39 Build_Receipt_Date_Details;
40
41
42
43
44 /*srw.message ('100', 'DEBUG: Call Build_Currency_Details');*/null;
45
46
47 Build_Currency_Details;
48
49
50
51
52 /*srw.message ('100', 'DEBUG: Call Get_SOB_Details');*/null;
53
54
55 Get_SOB_Details;
56
57
58
59
60 /*srw.message ('100', 'DEBUG: Call Get_Report_Name');*/null;
61
62
63 Get_Report_Name;
64
65
66 /*srw.message ('100', 'DEBUG: BeforeReport -');*/null;
67
68
69 return (TRUE);
70 end;
71
72 PROCEDURE Build_Customer_Details IS
73 Customer_Id_Char VARCHAR2(15);
74 BEGIN
75
76 /*srw.message ('100', 'DEBUG: Build_Customer_Details +');*/null;
77
78
79 IF P_Customer_Id IS NOT NULL THEN
80
81 Customer_Id_Char := P_Customer_Id;
82 Where_Customer := 'AND cash.customer_id = ' || Customer_Id_Char;
83
84 SELECT CUST.ACCOUNT_NUMBER,
85 SUBSTRB(PARTY.PARTY_NAME,1,50)
86 INTO P_Customer_Number,
87 P_Customer_Name
88 FROM HZ_CUST_ACCOUNTS CUST,
89 HZ_PARTIES PARTY
90 WHERE CUST.CUST_ACCOUNT_ID = P_Customer_Id
91 AND CUST.PARTY_ID = PARTY.PARTY_ID;
92 END IF;
93
94 /*srw.message ('100', 'DEBUG: Build_Customer_Details -');*/null;
95
96
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 /*srw.message ('100', 'DEBUG: Customer Number/Name not found');*/null;
101
102 RAISE;
103
104 END;
105
106 PROCEDURE Build_Location_Details IS
107 Site_Use_Id_Char VARCHAR2(15);
108 BEGIN
109
110 /*srw.message ('100', 'DEBUG: Build_Location_Details +');*/null;
111
112
113 IF P_Site_Use_Id IS NOT NULL THEN
114
115 Site_Use_Id_Char := P_Site_Use_Id;
116 Where_Location := 'AND cash.customer_site_use_id = ' || Site_Use_Id_Char;
117
118 SELECT location
119 INTO P_Location
120 FROM HZ_CUST_site_uses_all
121 WHERE site_use_id = P_site_use_id;
122
123 END IF;
124
125 /*srw.message ('100', 'DEBUG: Build_Location_Details -');*/null;
126
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 /*srw.message ('100', 'DEBUG: Location not found');*/null;
131
132 RAISE;
133
134 END;
135
136 PROCEDURE Build_Rate_Type_Details IS
137 BEGIN
138
139 /*srw.message ('100', 'DEBUG: Build_Rate_Type_Details +');*/null;
140
141
142 IF P_Rate_Type IS NOT NULL THEN
143
144 SELECT user_conversion_type
145 INTO P_Exchange_Rate_Type
146 FROM gl_daily_conversion_types
147 WHERE conversion_type = P_Rate_Type;
148
149 END IF;
150
151 /*srw.message ('100', 'DEBUG: Build_Rate_Type_Details -');*/null;
152
153
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 /*srw.message ('100', 'DEBUG: Exchange Rate Type Not Found');*/null;
157
158 RAISE;
159
160 END;
161
162 PROCEDURE BUILD_RECEIPT_DATE_DETAILS IS
163 From_Date_Char VARCHAR2(11);
164 To_Date_Char VARCHAR2(11);
165 BEGIN
166
167
168
169 /*srw.message ('100', 'DEBUG: Build_Receipt_Dates +');*/null;
170
171
172 From_Date_Char := P_From_Receipt_Date;
173 To_Date_Char := P_To_Receipt_Date;
174
175
176 IF P_From_Receipt_Date IS NOT NULL THEN
177 IF P_To_Receipt_Date IS NOT NULL THEN
178 Where_Date := 'AND cash.receipt_date BETWEEN ''' || From_Date_Char || '''' ||
179 ' AND ''' || To_Date_Char || '''';
180 ELSE
181 Where_Date := 'AND cash.receipt_date >= ''' || From_Date_Char || '''';
182 END IF;
183 ELSE
184 IF P_To_Receipt_Date IS NOT NULL THEN
185 Where_Date := 'AND cash.receipt_date <= ''' || To_Date_Char || '''';
186 END IF;
187 END IF;
188
189 /*srw.message ('100', 'DEBUG: Build_Receipt_Dates -');*/null;
190
191
192 END;
193
194 PROCEDURE BUILD_CURRENCY_DETAILS IS
195 BEGIN
196
197 /*srw.message ('100', 'DEBUG: Build_Currency_Details +');*/null;
198
199
200 IF P_Receipt_Currency IS NOT NULL THEN
201 Where_Currency := 'AND cash.currency_code = ''' || P_Receipt_Currency || '''';
202 END IF;
203
204 /*srw.message ('100', 'DEBUG: Build_Currency_Details -');*/null;
205
206
207 END;
208
209 function cf_gain_loss_actualfo(Actual_Alloc_Receipt_Amt_Base in number, Trx_Amt_Applied_Base in number) return number is
210 Exchange_Gain_Loss NUMBER;
211 begin
212
213 Exchange_Gain_Loss := ROUND(Actual_Alloc_Receipt_Amt_Base - Trx_Amt_Applied_Base, 6);
214
215 RETURN (Exchange_Gain_Loss);
216
217 end;
218
219 function allocated_amount_rateformula(Trx_Amt_Applied IN NUMBER,Receipt_Precision IN NUMBER,Rate_Sys_Curr_Rate in number) return number is
220 Allocated_Amount_Rate NUMBER;
221 begin
222
223 IF Rate_Sys_Curr_Rate IS NULL THEN
224 Allocated_Amount_Rate := '';
225 ELSE
226 Allocated_Amount_Rate := ROUND (Trx_Amt_Applied * Rate_Sys_Curr_Rate, Receipt_Precision);
227 END IF;
228
229 RETURN (Allocated_Amount_Rate);
230
231 end;
232
233 function Sys_Cross_CurrencyFormula(Trx_Currency IN VARCHAR2,Receipt_Currency IN VARCHAR2,Receipt_Date IN DATE) return Number is
234 Sys_Cross_Currency NUMBER;
235 Tmp_Sys_Cross_Currency NUMBER;
236 begin
237
238 IF P_Rate_Type IS NULL THEN
239 Sys_Cross_Currency := '';
240 ELSE
241 BEGIN
242
243
244
245 Sys_Cross_Currency := GL_CURRENCY_API.get_rate (Trx_Currency,
246 Receipt_Currency,
247 Receipt_Date,
248 P_Rate_Type);
249
250
251
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 Sys_Cross_Currency := '';
256 END;
257
258 END IF;
259
260 RETURN (Sys_Cross_Currency);
261
262 end;
263
264 function rate_alloc_receipt_amt_basefor(Rate_Sys_Curr_Rate in number,Rate_Alloc_Receipt_Amt IN NUMBER,Receipt_Exchange_Rate IN NUMBER) return number is
265 Alloc_Receipt_Amt_Base NUMBER;
266 begin
267
268 IF Rate_Sys_Curr_Rate IS NULL THEN
269 Alloc_Receipt_Amt_Base := '';
270 ELSE
271 Alloc_Receipt_Amt_Base := ROUND ((Rate_Alloc_Receipt_Amt * Receipt_Exchange_Rate), Functional_Precision);
272 END IF;
273
274 RETURN (Alloc_Receipt_Amt_Base);
275
276 end;
277
278 function rate_gain_lossformula(Rate_Alloc_Receipt_Amt_Base in number, Trx_Amt_Applied_Base in number) return number is
279 Gain_Loss NUMBER;
280 begin
281
282 Gain_Loss := ROUND (Rate_Alloc_Receipt_Amt_Base - Trx_Amt_Applied_Base, Functional_Precision);
283
284 RETURN (Gain_Loss);
285
286 end;
287
288 function absolute_differenceformula(Actual_Gain_Loss in number, Rate_Gain_Loss in number) return number is
289 L_Absolute_Difference NUMBER;
290 begin
291
292 L_Absolute_Difference := ABS( ROUND ((Actual_Gain_Loss - Rate_Gain_Loss), Functional_Precision));
293
294 RETURN (L_Absolute_Difference);
295
296 end;
297
298 function actual_gainformula(Actual_Gain_Loss in number) return number is
299 Actual_Gain NUMBER;
300 begin
301
302 IF Actual_Gain_Loss > 0 THEN
303 Actual_Gain := Actual_Gain_Loss;
304 END IF;
305
306 RETURN (Actual_Gain);
307 end;
308
309 function actual_rate_lossformula(Actual_Gain_Loss in number) return number is
310 Actual_Loss NUMBER;
311 begin
312
313 IF Actual_Gain_Loss < 0 THEN
314 Actual_Loss := Actual_Gain_Loss;
315 END IF;
316
317 RETURN (Actual_Loss);
318
319 end;
320
321 function rate_gainformula(Rate_Gain_Loss in number) return number is
322 Rate_Gain NUMBER;
323 begin
324
325 IF Rate_Gain_Loss > 0 THEN
326 Rate_Gain := Rate_Gain_Loss;
327 END IF;
328
329 RETURN (Rate_Gain);
330
331 end;
332
333 function rate_lossformula(Rate_Gain_Loss in number) return number is
334 Rate_Loss NUMBER;
335 begin
336
337 IF Rate_Gain_Loss < 0 THEN
338 Rate_Loss := Rate_Gain_Loss;
339 END IF;
340
341 RETURN (Rate_Loss);
342
343 end;
344
345 PROCEDURE Get_Report_Name IS
346 BEGIN
347
348 /*srw.message ('100', 'DEBUG: Get_Report_Name +');*/null;
349
350
351 SELECT cp.user_concurrent_program_name
352 INTO Report_Name
353 FROM FND_CONCURRENT_PROGRAMS_VL cp,
354 FND_CONCURRENT_REQUESTS cr
355 WHERE cr.request_id = P_CONC_REQUEST_ID
356 AND cp.application_id = cr.program_application_id
357 AND cp.concurrent_program_id = cr.concurrent_program_id;
358
359 /*srw.message ('100', 'DEBUG: Get_Report_Name -');*/null;
360
361
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 /*srw.message ('100', 'DEBUG: Concurrent Program Title not found');*/null;
365
366 Report_Name := '';
367
368 END;
369
370 PROCEDURE Get_SOB_Details IS
371 BEGIN
372
373 /*srw.message ('100', 'DEBUG: Get_SOB_Details +');*/null;
374
375
376 SELECT cur.currency_code,
377 cur.precision,
378 sob.name
379 INTO Functional_Currency,
380 Functional_Precision,
381 Set_Of_Books_Name
382 FROM fnd_currencies cur,
383 gl_sets_of_books sob
384 WHERE cur.currency_code = sob.currency_code
385 AND sob.set_of_books_id = P_Set_Of_Books_Id;
386
387 /*srw.message ('100', 'DEBUG: Get_SOB_Details -');*/null;
388
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 /*srw.message ('100', 'DEBUG: Set of Books Details not found');*/null;
393
394 RAISE;
395
396 END;
397
398 function rate_sys_curr_rate_dformula(Rate_Sys_Curr_Rate in number) return number is
399 Exchange_Rate NUMBER;
400 begin
401
402 Exchange_Rate := ROUND (Rate_Sys_Curr_Rate, 3);
403
404 RETURN(Exchange_Rate);
405
406 end;
407
408 function actual_cross_curr_rate_dformul(Actual_Cross_Curr_Rate in number) return number is
409 Exchange_Rate NUMBER;
410 begin
411
412 Exchange_Rate := ROUND (Actual_Cross_Curr_Rate, 3);
413
414 RETURN(Exchange_Rate);
415
416 end;
417
418 function AfterReport return boolean is
419 begin
420 /*srw.user_exit('FND SRWEXIT');*/null;
421
422
423 return (TRUE);
424 end;
425
426 --Functions to refer Oracle report placeholders--
427
428 Function P_Customer_Number_p return varchar2 is
429 Begin
430 return P_Customer_Number;
431 END;
432 Function P_Customer_Name_p return varchar2 is
433 Begin
434 return P_Customer_Name;
435 END;
436 Function Where_Customer_p return varchar2 is
437 Begin
438 return Where_Customer;
439 END;
440 Function P_Location_p return varchar2 is
441 Begin
442 return P_Location;
443 END;
444 Function Where_Location_p return varchar2 is
445 Begin
446 return Where_Location;
447 END;
448 Function Where_Date_p return varchar2 is
449 Begin
450 return Where_Date;
451 END;
452 Function Where_Currency_p return varchar2 is
453 Begin
454 return Where_Currency;
455 END;
456 Function P_Exchange_Rate_Type_p return varchar2 is
457 Begin
458 return P_Exchange_Rate_Type;
459 END;
460 Function Report_Name_p return varchar2 is
461 Begin
462 return Report_Name;
463 END;
464 Function Functional_Currency_p return varchar2 is
465 Begin
466 return Functional_Currency;
467 END;
468 Function Set_Of_Books_Name_p return varchar2 is
469 Begin
470 return Set_Of_Books_Name;
471 END;
472 Function Functional_Precision_p return number is
473 Begin
474 return Functional_Precision;
475 END;
476 END AR_ARXFXGL_XMLP_PKG ;
477