[Home] [Help]
PACKAGE BODY: APPS.ZX_UTIL_PKG
Source
1 PACKAGE BODY ZX_UTIL_PKG AS
2 /* $Header: zxutilb.pls 120.2.12010000.2 2008/12/17 07:22:26 spasala ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | get_lookup_meaning |
7 | |
8 | DESCRIPTION |
9 | This function is used to get the FND lookup meaning value for the |
10 | given fnd lookup_type and lookup_code. |
11 | |
12 | SCOPE - PUBLIC |
13 | |
14 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
15 | |
16 | ARGUMENTS : IN: |
17 | p_lookup_type |
18 | p_lookup_code |
19 | OUT: |
20 | Meaning |
21 | |
22 | RETURNS : Returns the lookup meaning value. |
23 | |
24 | MODIFICATION HISTORY |
25 | 22-Mar-06 Venkatavaradhan Created. |
26 +===========================================================================*/
27
28 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
29 p_lookup_code IN VARCHAR2)
30 RETURN VARCHAR2 IS
31 l_meaning fnd_lookups.meaning%TYPE;
32 l_hash_value NUMBER;
33 BEGIN
34
35 IF p_lookup_code IS NOT NULL AND
36 p_lookup_type IS NOT NULL THEN
37
38 l_hash_value := DBMS_UTILITY.get_hash_value(
39 p_lookup_type||'@*?'||p_lookup_code,
40 1000,
41 25000);
42
43 IF pg_zx_lookups_rec.EXISTS(l_hash_value) THEN
44 l_meaning := pg_zx_lookups_rec(l_hash_value);
45 ELSE
46 SELECT meaning INTO l_meaning
47 FROM fnd_lookups
48 WHERE lookup_type = p_lookup_type
49 AND lookup_code = p_lookup_code
50 AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE, SYSDATE)
51 AND NVL(ENABLED_FLAG, 'N') = 'Y';
52
53 pg_zx_lookups_rec(l_hash_value) := l_meaning;
54
55 END IF;
56
57 END IF;
58
59 return(l_meaning);
60
61 EXCEPTION
62 WHEN no_data_found THEN
63 return(null);
64 WHEN OTHERS THEN
65 raise;
66
67 END;
68
69 PROCEDURE copy_accounts(p_tax_account_entity_code IN VARCHAR2,
70 p_tax_account_entity_id IN NUMBER)
71 IS
72 BEGIN
73
74 IF (p_tax_account_entity_code = 'TAXES')
75 THEN
76
77 INSERT INTO ZX_ACCOUNTS
78 (
79 TAX_ACCOUNT_ID,
80 OBJECT_VERSION_NUMBER,
81 TAX_ACCOUNT_ENTITY_CODE,
82 TAX_ACCOUNT_ENTITY_ID,
83 LEDGER_ID,
84 INTERNAL_ORGANIZATION_ID,
85 TAX_ACCOUNT_CCID,
86 INTERIM_TAX_CCID,
87 NON_REC_ACCOUNT_CCID,
88 ADJ_CCID,
89 EDISC_CCID,
90 UNEDISC_CCID,
91 FINCHRG_CCID,
92 ADJ_NON_REC_TAX_CCID,
93 EDISC_NON_REC_TAX_CCID,
94 UNEDISC_NON_REC_TAX_CCID,
95 FINCHRG_NON_REC_TAX_CCID,
96 RECORD_TYPE_CODE,
97 CREATED_BY,
98 CREATION_DATE,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_DATE,
101 LAST_UPDATE_LOGIN
102 )
103 SELECT
104 zx_accounts_s.nextval,
105 1,
106 'JURISDICTION',
107 zjb.TAX_JURISDICTION_ID,
108 za.LEDGER_ID,
109 za.INTERNAL_ORGANIZATION_ID,
110 za.TAX_ACCOUNT_CCID,
111 za.INTERIM_TAX_CCID,
112 za.NON_REC_ACCOUNT_CCID,
113 za.ADJ_CCID,
114 za.EDISC_CCID,
115 za.UNEDISC_CCID,
116 za.FINCHRG_CCID,
117 za.ADJ_NON_REC_TAX_CCID,
118 za.EDISC_NON_REC_TAX_CCID,
119 za.UNEDISC_NON_REC_TAX_CCID,
120 za.FINCHRG_NON_REC_TAX_CCID,
121 za.RECORD_TYPE_CODE,
122 za.CREATED_BY,
123 za.CREATION_DATE,
124 za.LAST_UPDATED_BY,
125 za.LAST_UPDATE_DATE,
126 za.LAST_UPDATE_LOGIN
127 FROM ZX_ACCOUNTS za,
128 ZX_TAXES_B ztb,
129 ZX_JURISDICTIONS_B zjb
130 WHERE za.TAX_ACCOUNT_ENTITY_CODE = p_tax_account_entity_code
131 AND za.TAX_ACCOUNT_ENTITY_ID = p_tax_account_entity_id
132 AND ztb.TAX_ID = za.TAX_ACCOUNT_ENTITY_ID
133 AND zjb.TAX_REGIME_CODE = ztb.TAX_REGIME_CODE
134 AND zjb.TAX = ztb.TAX
135 AND NOT EXISTS (SELECT NULL
136 FROM ZX_ACCOUNTS juris_za
137 WHERE juris_za.TAX_ACCOUNT_ENTITY_CODE = 'JURISDICTION'
138 AND juris_za.TAX_ACCOUNT_ENTITY_ID = zjb.TAX_JURISDICTION_ID
139 AND juris_za.LEDGER_ID = za.LEDGER_ID
140 AND juris_za.INTERNAL_ORGANIZATION_ID = za.INTERNAL_ORGANIZATION_ID);
141
142 INSERT INTO ZX_ACCOUNTS
143 (
144 TAX_ACCOUNT_ID,
145 OBJECT_VERSION_NUMBER,
146 TAX_ACCOUNT_ENTITY_CODE,
147 TAX_ACCOUNT_ENTITY_ID,
148 LEDGER_ID,
149 INTERNAL_ORGANIZATION_ID,
150 TAX_ACCOUNT_CCID,
151 INTERIM_TAX_CCID,
152 NON_REC_ACCOUNT_CCID,
153 ADJ_CCID,
154 EDISC_CCID,
155 UNEDISC_CCID,
156 FINCHRG_CCID,
157 ADJ_NON_REC_TAX_CCID,
158 EDISC_NON_REC_TAX_CCID,
159 UNEDISC_NON_REC_TAX_CCID,
160 FINCHRG_NON_REC_TAX_CCID,
161 RECORD_TYPE_CODE,
162 CREATED_BY,
163 CREATION_DATE,
164 LAST_UPDATED_BY,
165 LAST_UPDATE_DATE,
166 LAST_UPDATE_LOGIN
167 )
168 SELECT
169 zx_accounts_s.nextval,
170 1,
171 'RATES',
172 zrb.TAX_RATE_ID,
173 za.LEDGER_ID,
174 za.INTERNAL_ORGANIZATION_ID,
175 za.TAX_ACCOUNT_CCID,
176 za.INTERIM_TAX_CCID,
177 za.NON_REC_ACCOUNT_CCID,
178 za.ADJ_CCID,
179 za.EDISC_CCID,
180 za.UNEDISC_CCID,
181 za.FINCHRG_CCID,
182 za.ADJ_NON_REC_TAX_CCID,
183 za.EDISC_NON_REC_TAX_CCID,
184 za.UNEDISC_NON_REC_TAX_CCID,
185 za.FINCHRG_NON_REC_TAX_CCID,
186 za.RECORD_TYPE_CODE,
187 za.CREATED_BY,
188 za.CREATION_DATE,
189 za.LAST_UPDATED_BY,
190 za.LAST_UPDATE_DATE,
191 za.LAST_UPDATE_LOGIN
192 FROM ZX_ACCOUNTS za,
193 ZX_TAXES_B ztb,
194 ZX_RATES_B zrb
195 WHERE za.TAX_ACCOUNT_ENTITY_CODE = p_tax_account_entity_code
196 AND za.TAX_ACCOUNT_ENTITY_ID = p_tax_account_entity_id
197 AND ztb.TAX_ID = za.TAX_ACCOUNT_ENTITY_ID
198 AND zrb.TAX_REGIME_CODE = ztb.TAX_REGIME_CODE
199 AND zrb.TAX = ztb.TAX
200 AND NOT EXISTS (SELECT NULL
201 FROM ZX_ACCOUNTS rates_za
202 WHERE rates_za.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
203 AND rates_za.TAX_ACCOUNT_ENTITY_ID = zrb.TAX_RATE_ID
204 AND rates_za.LEDGER_ID = za.LEDGER_ID
205 AND rates_za.INTERNAL_ORGANIZATION_ID = za.INTERNAL_ORGANIZATION_ID);
206
207 END IF;
208
209 IF (p_tax_account_entity_code = 'JURISDICTION')
210 THEN
211
212 INSERT INTO ZX_ACCOUNTS
213 (
214 TAX_ACCOUNT_ID,
215 OBJECT_VERSION_NUMBER,
216 TAX_ACCOUNT_ENTITY_CODE,
217 TAX_ACCOUNT_ENTITY_ID,
218 LEDGER_ID,
219 INTERNAL_ORGANIZATION_ID,
220 TAX_ACCOUNT_CCID,
221 INTERIM_TAX_CCID,
222 NON_REC_ACCOUNT_CCID,
223 ADJ_CCID,
224 EDISC_CCID,
225 UNEDISC_CCID,
226 FINCHRG_CCID,
227 ADJ_NON_REC_TAX_CCID,
228 EDISC_NON_REC_TAX_CCID,
229 UNEDISC_NON_REC_TAX_CCID,
230 FINCHRG_NON_REC_TAX_CCID,
231 RECORD_TYPE_CODE,
232 CREATED_BY,
233 CREATION_DATE,
234 LAST_UPDATED_BY,
235 LAST_UPDATE_DATE,
236 LAST_UPDATE_LOGIN
237 )
238 SELECT
239 zx_accounts_s.nextval,
240 1,
241 'RATES',
242 zrb.TAX_RATE_ID,
243 za.LEDGER_ID,
244 za.INTERNAL_ORGANIZATION_ID,
245 za.TAX_ACCOUNT_CCID,
246 za.INTERIM_TAX_CCID,
247 za.NON_REC_ACCOUNT_CCID,
248 za.ADJ_CCID,
249 za.EDISC_CCID,
250 za.UNEDISC_CCID,
251 za.FINCHRG_CCID,
252 za.ADJ_NON_REC_TAX_CCID,
253 za.EDISC_NON_REC_TAX_CCID,
254 za.UNEDISC_NON_REC_TAX_CCID,
255 za.FINCHRG_NON_REC_TAX_CCID,
256 za.RECORD_TYPE_CODE,
257 za.CREATED_BY,
258 za.CREATION_DATE,
259 za.LAST_UPDATED_BY,
260 za.LAST_UPDATE_DATE,
261 za.LAST_UPDATE_LOGIN
262 FROM ZX_ACCOUNTS za,
263 ZX_JURISDICTIONS_B zjb,
264 ZX_RATES_B zrb
265 WHERE za.TAX_ACCOUNT_ENTITY_CODE = p_tax_account_entity_code
266 AND za.TAX_ACCOUNT_ENTITY_ID = p_tax_account_entity_id
267 AND zjb.TAX_JURISDICTION_CODE = zrb.TAX_JURISDICTION_CODE
268 AND zjb.TAX_JURISDICTION_ID = za.TAX_ACCOUNT_ENTITY_ID
269 AND zrb.TAX_REGIME_CODE = zjb.TAX_REGIME_CODE
270 AND zrb.TAX = zjb.TAX
271 AND NOT EXISTS (SELECT NULL
272 FROM ZX_ACCOUNTS rates_za
273 WHERE rates_za.TAX_ACCOUNT_ENTITY_CODE = 'RATES'
274 AND rates_za.TAX_ACCOUNT_ENTITY_ID = zrb.TAX_RATE_ID
275 AND rates_za.LEDGER_ID = za.LEDGER_ID
276 AND rates_za.INTERNAL_ORGANIZATION_ID = za.INTERNAL_ORGANIZATION_ID);
277
278 END IF;
279
280 EXCEPTION
281
282 WHEN OTHERS THEN
283 raise;
284
285 END copy_accounts;
286
287 END ZX_UTIL_PKG;