1 PACKAGE BODY ar_obalrev_pkg AS
2 -- $Header: AROBRRPB.pls 120.9 2011/07/19 00:18:57 dgaurab ship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- AROBRRPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package specification of ar_obalrev_pkg.
13 -- This package is used to generate AR Open balance Revaluation Report for Slovakia.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 --FUNCTION DESCRIPTION
20 -- beforereport It is a public function used to intialize global variables
21 -- which will be used to build the queries in the Data Template Dynamically
22 --
23 --
24 -- exch_rate_calc It is a public function which returns exchange rate, by taking P_AS_OF_DATE,
25 -- p_exchange_rate_type,gc_func_currency , and currency as parameter
26 --
27 --
28 -- amtduefilter It is a public function which returns boolean value
29 -- which will be used to fetch the data in Data Template Dynamically
30 --
31 -- DEPENDENCIES
32 -- None.
33 --
34 --
35 -- LAST UPDATE DATE 13-MAR-2007
36 -- Date the program has been modified for the last time
37 --
38 -- HISTORY
39 -- =======
40 --
41 -- VERSION DATE AUTHOR(S) DESCRIPTION
42 -- ------- ----------- ------------------- ---------------------------
43 -- 1.0 11-MAR-2007 Mallikarjun Gupta Creation
44 --- 1.1 24-DEC-2007 Ravi Kiran G Modified to pick CM Details
45 --****************************************************************************************
46
47 FUNCTION beforereport RETURN BOOLEAN IS
48 lc_exchange_rate_type varchar2(2000);
49 EX_EXCHANGE_RATE_TYPE EXCEPTION;
50 BEGIN
51 IF P_CUSTOMER IS NOT NULL
52 THEN
53 SELECT hp.party_name
54 INTO gc_customer_name
55 FROM hz_parties hp
56 ,hz_cust_accounts hca
57 WHERE hca.party_id = hp.party_id
58 AND hca.account_number = P_CUSTOMER;
59 ELSE
60 gc_customer_name:=NULL;
61 END IF;
62
63 BEGIN
64 SELECT GL1.ledger_id
65 ,GL1.currency_code
66 INTO gc_ledger_id
67 ,gc_func_currency
68 FROM gl_ledgers GL1
69 ,gl_access_set_norm_assign GASNA
70 WHERE GASNA.access_set_id = FND_PROFILE.VALUE('GL_ACCESS_SET_ID')
71 AND GL1.ledger_id = GASNA.ledger_id
72 AND GL1.ledger_category_code = 'PRIMARY';
73
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 FND_FILE.PUT_LINE(FND_FILE.LOG,'1)gc_func_currency = "' || gc_func_currency || '"');
77 END;
78
79 BEGIN
80 SELECT name
81 INTO gc_ou_name
82 FROM hr_operating_units
83 WHERE organization_id = p_org_id;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 gc_ou_name := NULL;
87 END;
88
89 BEGIN
90 SELECT gdct.user_conversion_type
91 INTO gc_exchange_rate_type
92 FROM gl_daily_conversion_types gdct
93 WHERE gdct.conversion_type = P_EXCHANGE_RATE_TYPE;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 FND_FILE.PUT_LINE(FND_FILE.LOG,'2)gc_exchange_rate_type = "' || gc_exchange_rate_type || '"');
97 END;
98
99 --***********************************************************************
100 ----build the where clause for gc_incl_domestic_inv_where
101 --***********************************************************************
102 IF p_incl_domestic_inv = 'N' THEN
103 gc_incl_domestic_inv_where := 'acr.currency_code <> '''||gc_func_currency||'''';
104 ELSE
105 gc_incl_domestic_inv_where := '1=1';
106 END IF;
107
108 IF p_incl_domestic_inv = 'N' THEN
109 gc_incl_domestic_inv_where1 := 'RCTA.invoice_currency_code <> '''||gc_func_currency||'''';
110 ELSE
111 gc_incl_domestic_inv_where1 := '1=1';
112 END IF;
113
114 --***********************************************************************
115 ----build the where clause for gc_currency_where
116 --***********************************************************************
117 IF p_currency IS NOT NULL THEN
118 gc_currency_where := 'acr.currency_code = :p_currency';
119 ELSE
120 IF P_EXCHANGE_RATE_TYPE = 'User' THEN
121 fnd_message.SET_name('AR', 'AR_EXCHANGE_RATE_TYPE');
122 lc_exchange_rate_type := FND_MESSAGE.GET;
123 RAISE EX_EXCHANGE_RATE_TYPE;
124 END IF;
125 gc_currency_where := '1=1';
126
127 END IF;
128
129 IF p_currency IS NOT NULL THEN
130 gc_currency_where1 := 'RCTA.invoice_currency_code = :p_currency';
131 ELSE
132 IF P_EXCHANGE_RATE_TYPE = 'User' THEN
133 fnd_message.SET_name('AR', 'AR_EXCHANGE_RATE_TYPE');
134 lc_exchange_rate_type := FND_MESSAGE.GET;
135 RAISE EX_EXCHANGE_RATE_TYPE;
136 END IF;
137 gc_currency_where1 := '1=1';
138 END IF;
139
140 --************************************************************************
141 ----build the where clause for gc_customer_where
142 --***********************************************************************
143 IF p_customer IS NOT NULL THEN
144 gc_customer_where := 'HCA.account_number = :P_CUSTOMER';
145 gc_customer_where1 := 'HCA.account_number = :P_CUSTOMER';
146 ELSE
147 gc_customer_where := '1=1';
148 gc_customer_where1 := '1=1';
149 END IF;
150
151 --****************************************************************************
152 ----build where clause for gc_trx_date_where
153 --****************************************************************************
154
155 --gd_date_to :=TO_DATE(TO_CHAR(TO_DATE(P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY'));
156 gd_date_to1:=fnd_date.canonical_to_date(P_AS_OF_DATE);
157 gc_trx_date_where := 'ara.gl_date<='||''''||gd_date_to1||'''';
158 gc_trx_date_where1 := 'RCTA.trx_date<='||''''||gd_date_to1||'''';
159 gd_date_to := ''''||gd_date_to1||'''';
160 --****************************************************************************
161 --build where clause for gc_ou_where
162 --****************************************************************************
163
164 IF p_org_id IS NULL THEN
165 gc_ou_where :='1=1';
166 gc_ou_where1 :='1=1';
167 ELSE
168 gc_ou_where :='acr.org_id=:p_org_id';
169 gc_ou_where1 :='rcta.org_id=:p_org_id';
170 END IF;
171
172 RETURN TRUE;
173 EXCEPTION
174 WHEN EX_EXCHANGE_RATE_TYPE THEN
175 FND_FILE.PUT_LINE(FND_FILE.LOG,lc_exchange_rate_type);
176 RETURN FALSE;
177 END beforereport;
178
179
180 --*************************************************************************
181 -----function to calculate exchange rate for a given currency
182 --*************************************************************************
183 FUNCTION get_rate(p_currency IN VARCHAR2) RETURN NUMBER AS
184 ln_exch_rate NUMBER;
185 lc_exchange_rate_type varchar2(2000);
186 BEGIN
187 IF p_currency <> gc_func_currency THEN
188 ln_exch_rate := gl_currency_api.get_rate_sql(p_currency
189 ,gc_func_currency
190 ,gd_date_to1
191 ,p_exchange_rate_type);
192 IF ln_exch_rate = -1 THEN
193
194 fnd_message.SET_name('AR', 'AR_EXCHANGE_RATE');
195 fnd_message.SET_TOKEN('P_EXCHANGE_RATE_TYPE',P_EXCHANGE_RATE_TYPE);
196 fnd_message.SET_TOKEN('CURRENCY',P_CURRENCY);
197 --Int'l Calendar Project
198 fnd_message.SET_TOKEN('P_AS_OF_DATE',fnd_date.date_to_chardate(gd_date_to1, calendar_aware=> FND_DATE.calendar_aware_alt));
199 lc_exchange_rate_type := FND_MESSAGE.GET;
200 FND_FILE.PUT_LINE(FND_FILE.LOG,lc_exchange_rate_type);
201 raise_application_error (-20101,lc_exchange_rate_type);
202 ln_exch_rate:=NULL;
203 ELSIF ln_exch_rate = -2 THEN
204 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invalid Currency exception raised in GL_CURRENCY_API');
205 ln_exch_rate:=NULL;
206 END IF;
207 ELSE
208 ln_exch_rate:=1;
209 END IF;
210 RETURN (ln_exch_rate);
211 END get_rate;
212
213 FUNCTION amtduefilter (p_amt_due IN NUMBER) RETURN BOOLEAN AS
214 BEGIN
215 IF p_amt_due = 0 OR p_amt_due IS NULL THEN
216 RETURN (FALSE);
217 ELSE
218 RETURN (TRUE);
219 END IF;
220 END;
221
222 FUNCTION test(inv_num VARCHAR2 , amount NUMBER) RETURN NUMBER IS
223 var1 VARCHAR2(100) := 0;
224 BEGIN
225 var1 := inv_num;
226 IF var1 = var2 THEN
227 var2:=var1;
228 RETURN (0);
229 ELSE
230 var2 := var1;
231 RETURN(AMOUNT);
232 END IF;
233 END test;
234
235 END ar_obalrev_pkg;
[Home] [Help]