[Home] [Help]
PACKAGE BODY: APPS.ZX_PRD_OPTIONS_MIGRATE_PKG
Source
1 PACKAGE BODY ZX_PRD_OPTIONS_MIGRATE_PKG AS
2 /* $Header: zxprdoptmigpkgb.pls 120.19.12010000.1 2008/07/28 13:35:36 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'ZX_PRD_OPTIONS_MIGRATE_PKG';
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(250):= 'ZX.PLSQL.ZX_PRD_OPTIONS_MIGRATE_PKG.';
13
14 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
15 L_ORG_ID NUMBER(15);
16
17 TYPE NUMBER_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
18 TYPE DATE_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
19 TYPE VARCHAR2_1_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
20 TYPE VARCHAR2_2_tbl_type IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
21 TYPE VARCHAR2_15_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
22 TYPE VARCHAR2_20_tbl_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
23 TYPE VARCHAR2_30_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
24 TYPE VARCHAR2_50_tbl_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
25 TYPE VARCHAR2_80_tbl_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
26 TYPE VARCHAR2_150_tbl_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
27 TYPE VARCHAR2_240_tbl_type IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
28 TYPE VARCHAR2_250_tbl_type IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
29 TYPE VARCHAR2_2000_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
30
31 TYPE system_options_rec IS RECORD
32 (
33 org_id NUMBER_tbl_type,
34 tax_hier_site_exc_rate NUMBER_tbl_type,
35 tax_hier_cust_exc_rate NUMBER_tbl_type,
36 tax_hier_prod_exc_rate NUMBER_tbl_type,
37 tax_hier_account_exc_rate NUMBER_tbl_type,
38 tax_hier_system_exc_rate NUMBER_tbl_type,
39 tax_hier_po_shipment NUMBER_tbl_type,
40 tax_hier_vendor_site NUMBER_tbl_type,
41 tax_hier_vendor NUMBER_tbl_type,
42 tax_hier_account NUMBER_tbl_type,
43 tax_hier_system NUMBER_tbl_type,
44 tax_hier_invoice NUMBER_tbl_type,
45 tax_hier_template NUMBER_tbl_type,
46 tax_hier_ship_to_loc NUMBER_tbl_type,
47 tax_hier_item NUMBER_tbl_type,
48 output_tax_hier_site NUMBER_tbl_type,
49 output_tax_hier_cust NUMBER_tbl_type,
50 output_tax_hier_project NUMBER_tbl_type,
51 output_tax_hier_exp_ev NUMBER_tbl_type,
52 output_tax_hier_extn NUMBER_tbl_type,
53 output_tax_hier_ar_param NUMBER_tbl_type,
54 max_value NUMBER_tbl_type,
55 min_value NUMBER_tbl_type,
56 default_hierarchy1 VARCHAR2_30_tbl_type,
57 default_hierarchy2 VARCHAR2_30_tbl_type,
58 default_hierarchy3 VARCHAR2_30_tbl_type,
59 default_hierarchy4 VARCHAR2_30_tbl_type,
60 default_hierarchy5 VARCHAR2_30_tbl_type,
61 default_hierarchy6 VARCHAR2_30_tbl_type,
62 default_hierarchy7 VARCHAR2_30_tbl_type,
63 tax_code VARCHAR2_30_tbl_type,
64 tax_method_code VARCHAR2_30_tbl_type,
65 --inclusive_tax_used_flag VARCHAR2_1_tbl_type,
66 tax_use_customer_exempt_flag VARCHAR2_1_tbl_type,
67 tax_use_loc_exc_rate_flag VARCHAR2_1_tbl_type,
68 --tax_allow_compound_flag VARCHAR2_1_tbl_type,
69 tax_use_product_exempt_flag VARCHAR2_1_tbl_type,
70 tax_rounding_rule VARCHAR2_30_tbl_type,
71 tax_precision NUMBER_tbl_type,
72 tax_minimum_accountable_unit NUMBER_tbl_type,
73 use_tax_classification_flag VARCHAR2_1_tbl_type,
74 home_country_default_flag VARCHAR2_1_tbl_type,
75 sales_tax_geocode VARCHAR2_30_tbl_type
76 );
77
78 FUNCTION get_location_tax (
79 p_org_id IN NUMBER,
80 p_set_of_books_id IN NUMBER
81 ) RETURN VARCHAR2
82 IS
83 l_tax_code VARCHAR2(30);
84
85 BEGIN
86
87 SELECT vat.tax_code
88 INTO l_tax_code
89 FROM AR_VAT_TAX_ALL vat
90 WHERE vat.tax_type = 'LOCATION'
91 AND vat.set_of_books_id = p_set_of_books_id
92 AND sysdate between vat.start_date and nvl(vat.end_date, sysdate)
93 AND nvl(vat.enabled_flag, 'Y') = 'Y'
94 AND nvl(vat.tax_class, 'O') = 'O'
95 AND decode(l_multi_org_flag,'N',l_org_id,vat.org_id) = p_org_id;
96
97 RETURN l_tax_code;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 RETURN null;
101 END get_location_tax;
102
103
104 PROCEDURE AP_PRD_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
105 ap_system_options system_options_rec;
106 l_api_name VARCHAR2(30) := 'AP_TAX_OPTIONS_MIGRATE';
107
108 BEGIN
109 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
110 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
111 END IF;
112
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 SELECT decode(l_multi_org_flag,'N',l_org_id, sys.org_id), --org_id
116 tax_hier_po_shipment,
117 tax_hier_vendor_site,
118 tax_hier_vendor,
119 tax_hier_account,
120 tax_hier_system,
121 tax_hier_invoice,
122 tax_hier_template,
123 greatest(nvl(tax_hier_po_shipment,-1),nvl(tax_hier_vendor_site,-1),
124 nvl(tax_hier_vendor,-1),nvl(tax_hier_account,-1),
125 nvl(tax_hier_system,-1),nvl(tax_hier_invoice,-1),
126 nvl(tax_hier_template,-1)),
127 least(nvl(tax_hier_po_shipment,999),nvl(tax_hier_vendor_site,999),
128 nvl(tax_hier_vendor,999),nvl(tax_hier_account,999),
129 nvl(tax_hier_system,999),nvl(tax_hier_invoice,999),
130 nvl(tax_hier_template,999)),
131 null, --default_hierarchy1
132 null, --default_hierarchy2
133 null, --default_hierarchy3
134 null, --default_hierarchy4
135 null, --default_hierarchy5
136 null, --default_hierarchy6
140 --null, --inclusive_tax_used_flag
137 null, --default_hierarchy7
138 fin.VAT_CODE, --tax_classification_code
139 null, --tax_method_code
141 null, --tax_use_customer_exempt_flag,
142 null, --tax_use_product_exempt_flag
143 null, --tax_use_loc_exc_rate_flag
144 --null, --tax_allow_compound_flag
145 null, --tax_rounding_rule
146 null, --tax_precision
147 null, --tax_minimum_accountable_unit
148 decode (sys.tax_hier_template||sys.tax_hier_vendor_site||
149 sys.tax_hier_vendor||sys.tax_hier_account||
150 sys.tax_hier_system||sys.tax_hier_invoice,null, 'N','Y'), --use_tax_classification_flag
151 null --home_country_default_flag
152 BULK COLLECT INTO
153 ap_system_options.org_id,
154 ap_system_options.tax_hier_po_shipment,
155 ap_system_options.tax_hier_vendor_site,
156 ap_system_options.tax_hier_vendor,
157 ap_system_options.tax_hier_account,
158 ap_system_options.tax_hier_system,
159 ap_system_options.tax_hier_invoice,
160 ap_system_options.tax_hier_template,
161 ap_system_options.max_value,
162 ap_system_options.min_value ,
163 ap_system_options.default_hierarchy1,
164 ap_system_options.default_hierarchy2,
165 ap_system_options.default_hierarchy3,
166 ap_system_options.default_hierarchy4,
167 ap_system_options.default_hierarchy5,
168 ap_system_options.default_hierarchy6,
169 ap_system_options.default_hierarchy7,
170 ap_system_options.tax_code,
171 ap_system_options.tax_method_code,
172 --ap_system_options.inclusive_tax_used_flag,
173 ap_system_options.tax_use_customer_exempt_flag,
174 ap_system_options.tax_use_product_exempt_flag,
175 ap_system_options.tax_use_loc_exc_rate_flag,
176 --ap_system_options.tax_allow_compound_flag,
177 ap_system_options.tax_rounding_rule,
178 ap_system_options.tax_precision,
179 ap_system_options.tax_minimum_accountable_unit,
180 ap_system_options.use_tax_classification_flag,
181 ap_system_options.home_country_default_flag
182 FROM AP_SYSTEM_PARAMETERS_ALL sys,
183 FINANCIALS_SYSTEM_PARAMS_ALL fin
184 WHERE decode(l_multi_org_flag,'N',l_org_id,fin.org_id(+)) = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
185 AND NOT EXISTS (SELECT 1
186 FROM ZX_PRODUCT_OPTIONS_ALL prd
187 WHERE prd.ORG_ID = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
188 AND prd.APPLICATION_ID = 200
189 );
190
191 FOR i in 1..nvl(ap_system_options.org_id.LAST,0) LOOP
192 FOR j in ap_system_options.min_value(i)..ap_system_options.max_value(i) LOOP
193 IF ap_system_options.DEFAULT_HIERARCHY1(i) is null THEN
194 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
195 ap_system_options.DEFAULT_HIERARCHY1(i) := 'REFERENCE_DOCUMENT';
196 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
197 ap_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_FROM_PARTY_SITE';
198 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
199 ap_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_FROM_PARTY';
200 ELSIF ap_system_options.tax_hier_account(i) = j THEN
201 ap_system_options.DEFAULT_HIERARCHY1(i) := 'NATURAL_ACCOUNT';
202 ELSIF ap_system_options.tax_hier_system(i) = j THEN
203 ap_system_options.DEFAULT_HIERARCHY1(i) := 'FINANCIAL_OPTIONS';
204 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
205 ap_system_options.DEFAULT_HIERARCHY1(i) := 'INVOICE_HEADER';
206 ELSIF ap_system_options.tax_hier_template(i) = j THEN
207 ap_system_options.DEFAULT_HIERARCHY1(i) := 'TEMPLATE';
208 END IF;
209 ELSIF ap_system_options.DEFAULT_HIERARCHY2(i) is null THEN
210 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
211 ap_system_options.DEFAULT_HIERARCHY2(i) := 'REFERENCE_DOCUMENT';
212 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
213 ap_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_FROM_PARTY_SITE';
214 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
215 ap_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_FROM_PARTY';
216 ELSIF ap_system_options.tax_hier_account(i) = j THEN
217 ap_system_options.DEFAULT_HIERARCHY2(i) := 'NATURAL_ACCOUNT';
218 ELSIF ap_system_options.tax_hier_system(i) = j THEN
219 ap_system_options.DEFAULT_HIERARCHY2(i) := 'FINANCIAL_OPTIONS';
220 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
221 ap_system_options.DEFAULT_HIERARCHY2(i) := 'INVOICE_HEADER';
222 ELSIF ap_system_options.tax_hier_template(i) = j THEN
223 ap_system_options.DEFAULT_HIERARCHY2(i) := 'TEMPLATE';
224 END IF;
225 ELSIF ap_system_options.DEFAULT_HIERARCHY3(i) is null THEN
226 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
227 ap_system_options.DEFAULT_HIERARCHY3(i) := 'REFERENCE_DOCUMENT';
228 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
229 ap_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_FROM_PARTY_SITE';
230 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
231 ap_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_FROM_PARTY';
232 ELSIF ap_system_options.tax_hier_account(i) = j THEN
233 ap_system_options.DEFAULT_HIERARCHY3(i) := 'NATURAL_ACCOUNT';
234 ELSIF ap_system_options.tax_hier_system(i) = j THEN
235 ap_system_options.DEFAULT_HIERARCHY3(i) := 'FINANCIAL_OPTIONS';
236 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
237 ap_system_options.DEFAULT_HIERARCHY3(i) := 'INVOICE_HEADER';
238 ELSIF ap_system_options.tax_hier_template(i) = j THEN
239 ap_system_options.DEFAULT_HIERARCHY3(i) := 'TEMPLATE';
240 END IF;
241 ELSIF ap_system_options.DEFAULT_HIERARCHY4(i) is null THEN
242 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
243 ap_system_options.DEFAULT_HIERARCHY4(i) := 'REFERENCE_DOCUMENT';
244 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
245 ap_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_FROM_PARTY_SITE';
246 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
247 ap_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_FROM_PARTY';
248 ELSIF ap_system_options.tax_hier_account(i) = j THEN
249 ap_system_options.DEFAULT_HIERARCHY4(i) := 'NATURAL_ACCOUNT';
250 ELSIF ap_system_options.tax_hier_system(i) = j THEN
251 ap_system_options.DEFAULT_HIERARCHY4(i) := 'FINANCIAL_OPTIONS';
252 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
253 ap_system_options.DEFAULT_HIERARCHY4(i) := 'INVOICE_HEADER';
254 ELSIF ap_system_options.tax_hier_template(i) = j THEN
255 ap_system_options.DEFAULT_HIERARCHY4(i) := 'TEMPLATE';
256 END IF;
257 ELSIF ap_system_options.DEFAULT_HIERARCHY5(i) is null THEN
258 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
259 ap_system_options.DEFAULT_HIERARCHY5(i) := 'REFERENCE_DOCUMENT';
260 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
261 ap_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_FROM_PARTY_SITE';
262 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
263 ap_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_FROM_PARTY';
264 ELSIF ap_system_options.tax_hier_account(i) = j THEN
265 ap_system_options.DEFAULT_HIERARCHY5(i) := 'NATURAL_ACCOUNT';
266 ELSIF ap_system_options.tax_hier_system(i) = j THEN
267 ap_system_options.DEFAULT_HIERARCHY5(i) := 'FINANCIAL_OPTIONS';
268 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
269 ap_system_options.DEFAULT_HIERARCHY5(i) := 'INVOICE_HEADER';
270 ELSIF ap_system_options.tax_hier_template(i) = j THEN
271 ap_system_options.DEFAULT_HIERARCHY5(i) := 'TEMPLATE';
272 END IF;
273 ELSIF ap_system_options.DEFAULT_HIERARCHY6(i) is null THEN
274 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
275 ap_system_options.DEFAULT_HIERARCHY6(i) := 'REFERENCE_DOCUMENT';
276 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
277 ap_system_options.DEFAULT_HIERARCHY6(i) := 'SHIP_FROM_PARTY_SITE';
278 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
279 ap_system_options.DEFAULT_HIERARCHY6(i) := 'SHIP_FROM_PARTY';
280 ELSIF ap_system_options.tax_hier_account(i) = j THEN
281 ap_system_options.DEFAULT_HIERARCHY6(i) := 'NATURAL_ACCOUNT';
282 ELSIF ap_system_options.tax_hier_system(i) = j THEN
283 ap_system_options.DEFAULT_HIERARCHY6(i) := 'FINANCIAL_OPTIONS';
284 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
285 ap_system_options.DEFAULT_HIERARCHY6(i) := 'INVOICE_HEADER';
286 ELSIF ap_system_options.tax_hier_template(i) = j THEN
287 ap_system_options.DEFAULT_HIERARCHY6(i) := 'TEMPLATE';
288 END IF;
289 ELSIF ap_system_options.DEFAULT_HIERARCHY7(i) is null THEN
290 IF ap_system_options.tax_hier_po_shipment(i) = j THEN
294 ELSIF ap_system_options.tax_hier_vendor(i) = j THEN
291 ap_system_options.DEFAULT_HIERARCHY7(i) := 'REFERENCE_DOCUMENT';
292 ELSIF ap_system_options.tax_hier_vendor_site(i) = j THEN
293 ap_system_options.DEFAULT_HIERARCHY7(i) := 'SHIP_FROM_PARTY_SITE';
295 ap_system_options.DEFAULT_HIERARCHY7(i) := 'SHIP_FROM_PARTY';
296 ELSIF ap_system_options.tax_hier_account(i) = j THEN
297 ap_system_options.DEFAULT_HIERARCHY7(i) := 'NATURAL_ACCOUNT';
298 ELSIF ap_system_options.tax_hier_system(i) = j THEN
299 ap_system_options.DEFAULT_HIERARCHY7(i) := 'FINANCIAL_OPTIONS';
300 ELSIF ap_system_options.tax_hier_invoice(i) = j THEN
301 ap_system_options.DEFAULT_HIERARCHY7(i) := 'INVOICE_HEADER';
302 ELSIF ap_system_options.tax_hier_template(i) = j THEN
303 ap_system_options.DEFAULT_HIERARCHY7(i) := 'TEMPLATE';
304 END IF;
305 END IF;
306 END LOOP;
307 END LOOP;
308
309 FORALL i in 1..nvl(ap_system_options.org_id.LAST,0)
310 INSERT INTO ZX_PRODUCT_OPTIONS_ALL (
311 PRODUCT_OPTIONS_ID,
312 ORG_ID,
313 APPLICATION_ID,
314 DEF_OPTION_HIER_1_CODE,
315 DEF_OPTION_HIER_2_CODE,
316 DEF_OPTION_HIER_3_CODE,
317 DEF_OPTION_HIER_4_CODE,
318 DEF_OPTION_HIER_5_CODE,
319 DEF_OPTION_HIER_6_CODE,
320 DEF_OPTION_HIER_7_CODE,
321 TAX_CLASSIFICATION_CODE,
322 TAX_METHOD_CODE,
323 --INCLUSIVE_TAX_USED_FLAG,
324 TAX_USE_CUSTOMER_EXEMPT_FLAG,
325 TAX_USE_PRODUCT_EXEMPT_FLAG,
326 TAX_USE_LOC_EXC_RATE_FLAG,
327 --TAX_ALLOW_COMPOUND_FLAG,
328 TAX_ROUNDING_RULE,
329 TAX_PRECISION,
330 TAX_MINIMUM_ACCOUNTABLE_UNIT,
331 USE_TAX_CLASSIFICATION_FLAG,
332 HOME_COUNTRY_DEFAULT_FLAG,
333 OBJECT_VERSION_NUMBER,
334 RECORD_TYPE_CODE,
335 CREATION_DATE,
336 CREATED_BY,
337 LAST_UPDATE_DATE,
338 LAST_UPDATED_BY,
339 LAST_UPDATE_LOGIN
340 )
341 values (ZX_PRODUCT_OPTIONS_ALL_S.nextval,
342 ap_system_options.ORG_ID(i),
343 200, --application_id
344 ap_system_options.DEFAULT_HIERARCHY1(i),
345 ap_system_options.DEFAULT_HIERARCHY2(i),
346 ap_system_options.DEFAULT_HIERARCHY3(i),
347 ap_system_options.DEFAULT_HIERARCHY4(i),
348 ap_system_options.DEFAULT_HIERARCHY5(i),
349 ap_system_options.DEFAULT_HIERARCHY6(i),
350 ap_system_options.DEFAULT_HIERARCHY7(i),
351 ap_system_options.TAX_CODE(i),
352 ap_system_options.TAX_METHOD_CODE(i),
353 --ap_system_options.INCLUSIVE_TAX_USED_FLAG(i),
354 ap_system_options.TAX_USE_CUSTOMER_EXEMPT_FLAG(i), --tax_use_customer_exempt_flag
355 ap_system_options.TAX_USE_PRODUCT_EXEMPT_FLAG(i), --tax_use_product_exempt_flag
356 ap_system_options.TAX_USE_LOC_EXC_RATE_FLAG(i), --tax_use_loc_exc_rate_flag
357 --ap_system_options.TAX_ALLOW_COMPOUND_FLAG(i), --tax_allow_compound_flag
358 ap_system_options.TAX_ROUNDING_RULE(i), --tax_rounding_rule
359 ap_system_options.TAX_PRECISION(i), --tax_precision
360 ap_system_options.TAX_MINIMUM_ACCOUNTABLE_UNIT(i), --tax_minimum_accountable_unit
361 ap_system_options.USE_TAX_CLASSIFICATION_FLAG(i),
362 ap_system_options.HOME_COUNTRY_DEFAULT_FLAG(i),
363 1, --object_version_number
364 'MIGRATED', --record_type_code
365 sysdate, --creation_date
366 fnd_global.user_Id, --created_by
367 sysdate, --last_updated_by
368 fnd_global.user_id, --last_updated_by
369 fnd_global.conc_login_id --last_update_login
370 );
371
372
373 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
375 END IF;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 x_return_status := FND_API.G_RET_STS_ERROR ;
380 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
381 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT',' AP Tax Options Migration : '||SQLERRM);
382 FND_MSG_PUB.Add;
383 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
384 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
385 END IF;
386 END AP_PRD_OPTIONS_MIGRATE;
387
388
389 /* For AR/PA System Options: when 'System Options' is enabled and there is no Tax
390 Code available in AR/PA System Parameters, consider Tax of 'LOCATION'
391 tax type from AR_VAT_TAX for migrating as Tax Classification Code. This needs
392 to be considered only if the Tax Method is 'SALES_TAX'. */
393 PROCEDURE AR_PRD_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
394 ar_system_options system_options_rec;
395 l_api_name VARCHAR2(30) := 'AR_TAX_OPTIONS_MIGRATE';
396
397 BEGIN
398 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
400 END IF;
401
402 x_return_status := FND_API.G_RET_STS_SUCCESS;
403
404 SELECT decode(l_multi_org_flag,'N',l_org_id, sys.org_id), --org_id
405 tax_hier_site_exc_rate,
406 tax_hier_cust_exc_rate,
407 tax_hier_prod_exc_rate,
408 tax_hier_account_exc_rate,
409 tax_hier_system_exc_rate,
410 greatest(nvl(tax_hier_site_exc_rate,-1),nvl(tax_hier_cust_exc_rate,-1),
411 nvl(tax_hier_prod_exc_rate,-1),nvl(tax_hier_account_exc_rate,-1),
412 nvl(tax_hier_system_exc_rate,-1)),
413 least(nvl(tax_hier_site_exc_rate,999),nvl(tax_hier_cust_exc_rate,999),
414 nvl(tax_hier_prod_exc_rate,999),nvl(tax_hier_account_exc_rate,999),
415 nvl(tax_hier_system_exc_rate,999)),
416 null, --default_hierarchy1
417 null, --default_hierarchy2
418 null, --default_hierarchy3
419 null, --default_hierarchy4
420 null, --default_hieararchy5
421 null, --default_hierarchy6
422 null, --default_hierarchy7
423 decode(sys.tax_use_system_exc_rate_flag,'Y', --tax_classification
424 decode(sys.TAX_CODE, null, decode(sys.TAX_METHOD,'SALES_TAX',
425 get_location_tax(decode(l_multi_org_flag,'N',l_org_id,sys.org_id),
426 sys.set_of_books_id),null),sys.tax_code),null),
427 decode(sys.TAX_METHOD,'LATIN', 'LTE', 'EBTAX'), --tax_method_code
428 -- decode(sys.TAX_METHOD,'LATIN',sys.INCLUSIVE_TAX_USED,null), --inclusive_tax_used_flag
429 decode(sys.TAX_METHOD,'LATIN',null,sys.TAX_USE_CUSTOMER_EXEMPT_FLAG),--tax_use_customer_exempt_flag
430 decode(sys.TAX_METHOD,'LATIN',null,sys.TAX_USE_PRODUCT_EXEMPT_FLAG), --tax_use_product_exempt_flag
431 decode(sys.TAX_METHOD,'LATIN',null,sys.TAX_USE_LOC_EXC_RATE_FLAG), --tax_use_loc_exc_rate_flag
432 -- decode(sys.TAX_METHOD,'LATIN',sys.TAX_ALLOW_COMPOUND_FLAG,null), --tax_allow_compound_flag
433 decode(sys.TAX_METHOD,'LATIN', --tax_rounding_rule
434 decode(sys.TAX_ROUNDING_RULE, null, fin.TAX_ROUNDING_RULE,sys.TAX_ROUNDING_RULE),null),
435 decode(sys.TAX_METHOD,'LATIN', --tax_precision
436 decode(sys.TAX_PRECISION, null, fin.PRECISION,sys.TAX_PRECISION),null),
437 decode(sys.TAX_METHOD,'LATIN', --tax_minimum_accountable_unit
438 decode(sys.TAX_MINIMUM_ACCOUNTABLE_UNIT,null,fin.MINIMUM_ACCOUNTABLE_UNIT,sys.TAX_MINIMUM_ACCOUNTABLE_UNIT),null),
439 decode (sys.tax_use_site_exc_rate_flag||sys.tax_use_cust_exc_rate_flag|| --use_tax_classification_flag
440 sys.tax_use_prod_exc_rate_flag||sys.tax_use_account_exc_rate_flag||
441 sys.tax_use_system_exc_rate_flag, null, 'N', 'Y'),
442 decode(sys.TAX_CODE, null,
443 decode(sys.TAX_METHOD,'SALES_TAX', 'Y', 'N'),'N'),--home_country_default_flag
444 sys.SALES_TAX_GEOCODE
445 BULK COLLECT INTO
446 ar_system_options.org_id,
447 ar_system_options.tax_hier_site_exc_rate,
448 ar_system_options.tax_hier_cust_exc_rate,
449 ar_system_options.tax_hier_prod_exc_rate,
450 ar_system_options.tax_hier_account_exc_rate,
451 ar_system_options.tax_hier_system_exc_rate,
452 ar_system_options.max_value,
453 ar_system_options.min_value ,
454 ar_system_options.default_hierarchy1,
455 ar_system_options.default_hierarchy2,
456 ar_system_options.default_hierarchy3,
457 ar_system_options.default_hierarchy4,
458 ar_system_options.default_hierarchy5,
459 ar_system_options.default_hierarchy6,
460 ar_system_options.default_hierarchy7,
461 ar_system_options.tax_code,
462 ar_system_options.tax_method_code,
463 --ar_system_options.inclusive_tax_used_flag,
464 ar_system_options.tax_use_customer_exempt_flag,
465 ar_system_options.tax_use_product_exempt_flag,
469 ar_system_options.tax_precision,
466 ar_system_options.tax_use_loc_exc_rate_flag,
467 --ar_system_options.tax_allow_compound_flag,
468 ar_system_options.tax_rounding_rule,
470 ar_system_options.tax_minimum_accountable_unit,
471 ar_system_options.use_tax_classification_flag,
472 ar_system_options.home_country_default_flag,
473 ar_system_options.sales_tax_geocode
474 FROM AR_SYSTEM_PARAMETERS_ALL sys,
475 FINANCIALS_SYSTEM_PARAMS_ALL fin
476 WHERE decode(l_multi_org_flag,'N',l_org_id,fin.org_id(+)) = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
477 AND NOT EXISTS (SELECT 1
478 FROM ZX_PRODUCT_OPTIONS_ALL prd
479 WHERE prd.ORG_ID = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
480 AND prd.APPLICATION_ID = 222
481 );
482
483 FOR i in 1..nvl(ar_system_options.org_id.LAST,0) LOOP
484 FOR j in ar_system_options.min_value(i)..ar_system_options.max_value(i) LOOP
485 IF ar_system_options.DEFAULT_HIERARCHY1(i) is null THEN
486 IF ar_system_options.tax_hier_site_exc_rate(i) = j THEN
487 ar_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_TO_PARTY_SITE';
488 ELSIF ar_system_options.tax_hier_cust_exc_rate(i) = j THEN
489 ar_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_TO_PARTY';
490 ELSIF ar_system_options.tax_hier_prod_exc_rate(i) = j THEN
491 ar_system_options.DEFAULT_HIERARCHY1(i) := 'PRODUCT';
492 ELSIF ar_system_options.tax_hier_account_exc_rate(i) = j THEN
493 ar_system_options.DEFAULT_HIERARCHY1(i) := 'REVENUE_ACCOUNT';
494 ELSIF ar_system_options.tax_hier_system_exc_rate(i) = j THEN
495 ar_system_options.DEFAULT_HIERARCHY1(i) := 'SYSTEM_OPTIONS';
496 END IF;
497 ELSIF ar_system_options.DEFAULT_HIERARCHY2(i) is null THEN
498 IF ar_system_options.tax_hier_site_exc_rate(i) = j THEN
499 ar_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_TO_PARTY_SITE';
500 ELSIF ar_system_options.tax_hier_cust_exc_rate(i) = j THEN
501 ar_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_TO_PARTY';
502 ELSIF ar_system_options.tax_hier_prod_exc_rate(i) = j THEN
503 ar_system_options.DEFAULT_HIERARCHY2(i) := 'PRODUCT';
504 ELSIF ar_system_options.tax_hier_account_exc_rate(i) = j THEN
505 ar_system_options.DEFAULT_HIERARCHY2(i) := 'REVENUE_ACCOUNT';
506 ELSIF ar_system_options.tax_hier_system_exc_rate(i) = j THEN
507 ar_system_options.DEFAULT_HIERARCHY2(i) := 'SYSTEM_OPTIONS';
508 END IF;
509 ELSIF ar_system_options.DEFAULT_HIERARCHY3(i) is null THEN
510 IF ar_system_options.tax_hier_site_exc_rate(i) = j THEN
511 ar_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_TO_PARTY_SITE';
512 ELSIF ar_system_options.tax_hier_cust_exc_rate(i) = j THEN
513 ar_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_TO_PARTY';
514 ELSIF ar_system_options.tax_hier_prod_exc_rate(i) = j THEN
515 ar_system_options.DEFAULT_HIERARCHY3(i) := 'PRODUCT';
516 ELSIF ar_system_options.tax_hier_account_exc_rate(i) = j THEN
517 ar_system_options.DEFAULT_HIERARCHY3(i) := 'REVENUE_ACCOUNT';
518 ELSIF ar_system_options.tax_hier_system_exc_rate(i) = j THEN
519 ar_system_options.DEFAULT_HIERARCHY3(i) := 'SYSTEM_OPTIONS';
520 END IF;
521 ELSIF ar_system_options.DEFAULT_HIERARCHY4(i) is null THEN
522 IF ar_system_options.tax_hier_site_exc_rate(i) = j THEN
523 ar_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_TO_PARTY_SITE';
524 ELSIF ar_system_options.tax_hier_cust_exc_rate(i) = j THEN
525 ar_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_TO_PARTY';
526 ELSIF ar_system_options.tax_hier_prod_exc_rate(i) = j THEN
527 ar_system_options.DEFAULT_HIERARCHY4(i) := 'PRODUCT';
528 ELSIF ar_system_options.tax_hier_account_exc_rate(i) = j THEN
529 ar_system_options.DEFAULT_HIERARCHY4(i) := 'REVENUE_ACCOUNT';
530 ELSIF ar_system_options.tax_hier_system_exc_rate(i) = j THEN
531 ar_system_options.DEFAULT_HIERARCHY4(i) := 'SYSTEM_OPTIONS';
532 END IF;
533 ELSIF ar_system_options.DEFAULT_HIERARCHY5(i) is null THEN
534 IF ar_system_options.tax_hier_site_exc_rate(i) = j THEN
535 ar_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_TO_PARTY_SITE';
536 ELSIF ar_system_options.tax_hier_cust_exc_rate(i) = j THEN
537 ar_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_TO_PARTY';
538 ELSIF ar_system_options.tax_hier_prod_exc_rate(i) = j THEN
539 ar_system_options.DEFAULT_HIERARCHY5(i) := 'PRODUCT';
540 ELSIF ar_system_options.tax_hier_account_exc_rate(i) = j THEN
541 ar_system_options.DEFAULT_HIERARCHY5(i) := 'REVENUE_ACCOUNT';
542 ELSIF ar_system_options.tax_hier_system_exc_rate(i) = j THEN
543 ar_system_options.DEFAULT_HIERARCHY5(i) := 'SYSTEM_OPTIONS';
544 END IF;
545 END IF;
546 END LOOP;
547 END LOOP;
548
549 FORALL i in 1..nvl(ar_system_options.org_id.LAST,0)
550 INSERT INTO ZX_PRODUCT_OPTIONS_ALL (
551 PRODUCT_OPTIONS_ID,
552 ORG_ID,
553 APPLICATION_ID,
554 DEF_OPTION_HIER_1_CODE,
555 DEF_OPTION_HIER_2_CODE,
556 DEF_OPTION_HIER_3_CODE,
557 DEF_OPTION_HIER_4_CODE,
558 DEF_OPTION_HIER_5_CODE,
559 DEF_OPTION_HIER_6_CODE,
560 DEF_OPTION_HIER_7_CODE,
561 TAX_CLASSIFICATION_CODE,
562 TAX_METHOD_CODE,
563 --INCLUSIVE_TAX_USED_FLAG,
564 TAX_USE_CUSTOMER_EXEMPT_FLAG,
565 TAX_USE_PRODUCT_EXEMPT_FLAG,
566 TAX_USE_LOC_EXC_RATE_FLAG,
567 --TAX_ALLOW_COMPOUND_FLAG,
568 TAX_ROUNDING_RULE,
569 TAX_PRECISION,
570 TAX_MINIMUM_ACCOUNTABLE_UNIT,
571 USE_TAX_CLASSIFICATION_FLAG,
572 HOME_COUNTRY_DEFAULT_FLAG,
573 SALES_TAX_GEOCODE,
574 OBJECT_VERSION_NUMBER,
575 RECORD_TYPE_CODE,
576 CREATION_DATE,
577 CREATED_BY,
578 LAST_UPDATE_DATE,
579 LAST_UPDATED_BY,
580 LAST_UPDATE_LOGIN
581 )
582 values (ZX_PRODUCT_OPTIONS_ALL_S.nextval,
583 ar_system_options.ORG_ID(i),
584 222, --application_id
585 ar_system_options.DEFAULT_HIERARCHY1(i),
586 ar_system_options.DEFAULT_HIERARCHY2(i),
587 ar_system_options.DEFAULT_HIERARCHY3(i),
588 ar_system_options.DEFAULT_HIERARCHY4(i),
589 ar_system_options.DEFAULT_HIERARCHY5(i),
590 ar_system_options.DEFAULT_HIERARCHY6(i),
591 ar_system_options.DEFAULT_HIERARCHY7(i),
592 ar_system_options.TAX_CODE(i),
593 ar_system_options.TAX_METHOD_CODE(i),
594 --ar_system_options.INCLUSIVE_TAX_USED_FLAG(i),
595 ar_system_options.TAX_USE_CUSTOMER_EXEMPT_FLAG(i), --tax_use_customer_exempt_flag
596 ar_system_options.TAX_USE_PRODUCT_EXEMPT_FLAG(i), --tax_use_product_exempt_flag
597 ar_system_options.TAX_USE_LOC_EXC_RATE_FLAG(i), --tax_use_loc_exc_rate_flag
598 --ar_system_options.TAX_ALLOW_COMPOUND_FLAG(i), --tax_allow_compound_flag
599 ar_system_options.TAX_ROUNDING_RULE(i), --tax_rounding_rule
600 ar_system_options.TAX_PRECISION(i), --tax_precision
601 ar_system_options.TAX_MINIMUM_ACCOUNTABLE_UNIT(i), --tax_minimum_accountable_unit
602 ar_system_options.USE_TAX_CLASSIFICATION_FLAG(i),
603 ar_system_options.HOME_COUNTRY_DEFAULT_FLAG(i),
604 ar_system_options.SALES_TAX_GEOCODE(i),
605 1, --object_version_number
606 'MIGRATED', --record_type_code
607 sysdate, --creation_date
608 fnd_global.user_Id, --created_by
609 sysdate, --last_updated_by
610 fnd_global.user_id, --last_updated_by
611 fnd_global.conc_login_id --last_update_login
612 );
613
614 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
615 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
616 END IF;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 x_return_status := FND_API.G_RET_STS_ERROR ;
621 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
622 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','AR Tax Options Migration : '||SQLERRM);
623 FND_MSG_PUB.Add;
624 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
626 END IF;
627 END AR_PRD_OPTIONS_MIGRATE;
628
629
630 PROCEDURE PO_PRD_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
631 po_system_options system_options_rec;
632 l_api_name VARCHAR2(30) := 'PO_TAX_OPTIONS_MIGRATE';
633
634 BEGIN
635 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
636 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
637 END IF;
638
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640
641 SELECT decode(l_multi_org_flag,'N',l_org_id, sys.org_id), --org_id
642 sys.tax_hier_ship_to_loc,
643 sys.tax_hier_item,
644 sys.tax_hier_vendor_site,
645 sys.tax_hier_vendor,
646 sys.tax_hier_system,
647 greatest(nvl(sys.tax_hier_ship_to_loc,-1),nvl(sys.tax_hier_item,-1),
648 nvl(sys.tax_hier_vendor_site,-1),nvl(sys.tax_hier_vendor,-1),
649 nvl(sys.tax_hier_system,-1)),
650 least(nvl(sys.tax_hier_ship_to_loc,999),nvl(sys.tax_hier_item,999),
651 nvl(sys.tax_hier_vendor_site,999),nvl(sys.tax_hier_vendor,999),
652 nvl(sys.tax_hier_system,999)),
653 null, --default_hierarchy1
654 null, --default_hierarchy2
655 null, --default_hierarchy3
656 null, --default_hierarchy4
657 null, --default_hierarchy5
658 null, --default_hierarchy6
659 null, --default_hierarchy7
660 fin.VAT_CODE, --tax_classification_code
661 null, --tax_method_code
662 --null, --inclusive_tax_used_flag
663 null, --tax_use_customer_exempt_flag,
664 null, --tax_use_product_exempt_flag
665 null, --tax_use_loc_exc_rate_flag
666 --null, --tax_allow_compound_flag
667 null, --tax_rounding_rule
668 null, --tax_precision
669 null, --tax_minimum_accountable_unit
670 decode (sys.tax_hier_ship_to_loc||sys.tax_hier_item||
671 sys.tax_hier_vendor_site||sys.tax_hier_vendor||
672 sys.tax_hier_system,null, 'N','Y'), --use_tax_classification_flag
673 null --home_country_default_flag
674 BULK COLLECT INTO
675 po_system_options.org_id,
676 po_system_options.tax_hier_ship_to_loc,
677 po_system_options.tax_hier_item,
678 po_system_options.tax_hier_vendor_site,
679 po_system_options.tax_hier_vendor,
680 po_system_options.tax_hier_system,
681 po_system_options.max_value,
682 po_system_options.min_value ,
683 po_system_options.default_hierarchy1,
684 po_system_options.default_hierarchy2,
685 po_system_options.default_hierarchy3,
686 po_system_options.default_hierarchy4,
687 po_system_options.default_hierarchy5,
688 po_system_options.default_hierarchy6,
689 po_system_options.default_hierarchy7,
690 po_system_options.tax_code,
691 po_system_options.tax_method_code,
692 --po_system_options.inclusive_tax_used_flag,
696 --po_system_options.tax_allow_compound_flag,
693 po_system_options.tax_use_customer_exempt_flag,
694 po_system_options.tax_use_product_exempt_flag,
695 po_system_options.tax_use_loc_exc_rate_flag,
697 po_system_options.tax_rounding_rule,
698 po_system_options.tax_precision,
699 po_system_options.tax_minimum_accountable_unit,
700 po_system_options.use_tax_classification_flag,
701 po_system_options.home_country_default_flag
702 FROM PO_SYSTEM_PARAMETERS_ALL sys,
703 FINANCIALS_SYSTEM_PARAMS_ALL fin
704 WHERE decode(l_multi_org_flag,'N',l_org_id,fin.org_id(+))= decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
705 AND NOT EXISTS (SELECT 1
706 FROM ZX_PRODUCT_OPTIONS_ALL prd
707 WHERE prd.ORG_ID = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
708 AND prd.APPLICATION_ID = 201
709 );
710
711 FOR i in 1..nvl(po_system_options.org_id.LAST,0) LOOP
712 FOR j in po_system_options.min_value(i)..po_system_options.max_value(i) LOOP
713 IF po_system_options.DEFAULT_HIERARCHY1(i) is null THEN
714 IF po_system_options.tax_hier_ship_to_loc(i) = j THEN
715 po_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_TO_LOCATION';
716 ELSIF po_system_options.tax_hier_item(i) = j THEN
717 po_system_options.DEFAULT_HIERARCHY1(i) := 'ITEM';
718 ELSIF po_system_options.tax_hier_vendor_site(i) = j THEN
719 po_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_FROM_PARTY_SITE';
720 ELSIF po_system_options.tax_hier_vendor(i) = j THEN
721 po_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_FROM_PARTY';
722 ELSIF po_system_options.tax_hier_system(i) = j THEN
723 po_system_options.DEFAULT_HIERARCHY1(i) := 'FINANCIAL_OPTIONS';
724 END IF;
725 ELSIF po_system_options.DEFAULT_HIERARCHY2(i) is null THEN
726 IF po_system_options.tax_hier_ship_to_loc(i) = j THEN
727 po_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_TO_LOCATION';
728 ELSIF po_system_options.tax_hier_item(i) = j THEN
729 po_system_options.DEFAULT_HIERARCHY2(i) := 'ITEM';
730 ELSIF po_system_options.tax_hier_vendor_site(i) = j THEN
731 po_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_FROM_PARTY_SITE';
732 ELSIF po_system_options.tax_hier_vendor(i) = j THEN
733 po_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_FROM_PARTY';
734 ELSIF po_system_options.tax_hier_system(i) = j THEN
735 po_system_options.DEFAULT_HIERARCHY2(i) := 'FINANCIAL_OPTIONS';
736 END IF;
737 ELSIF po_system_options.DEFAULT_HIERARCHY3(i) is null THEN
738 IF po_system_options.tax_hier_ship_to_loc(i) = j THEN
739 po_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_TO_LOCATION';
740 ELSIF po_system_options.tax_hier_item(i) = j THEN
741 po_system_options.DEFAULT_HIERARCHY3(i) := 'ITEM';
742 ELSIF po_system_options.tax_hier_vendor_site(i) = j THEN
743 po_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_FROM_PARTY_SITE';
744 ELSIF po_system_options.tax_hier_vendor(i) = j THEN
745 po_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_FROM_PARTY';
746 ELSIF po_system_options.tax_hier_system(i) = j THEN
747 po_system_options.DEFAULT_HIERARCHY3(i) := 'FINANCIAL_OPTIONS';
748 END IF;
749 ELSIF po_system_options.DEFAULT_HIERARCHY4(i) is null THEN
750 IF po_system_options.tax_hier_ship_to_loc(i) = j THEN
751 po_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_TO_LOCATION';
752 ELSIF po_system_options.tax_hier_item(i) = j THEN
753 po_system_options.DEFAULT_HIERARCHY4(i) := 'ITEM';
754 ELSIF po_system_options.tax_hier_vendor_site(i) = j THEN
755 po_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_FROM_PARTY_SITE';
756 ELSIF po_system_options.tax_hier_vendor(i) = j THEN
757 po_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_FROM_PARTY';
758 ELSIF po_system_options.tax_hier_system(i) = j THEN
759 po_system_options.DEFAULT_HIERARCHY4(i) := 'FINANCIAL_OPTIONS';
760 END IF;
761 ELSIF po_system_options.DEFAULT_HIERARCHY5(i) is null THEN
762 IF po_system_options.tax_hier_ship_to_loc(i) = j THEN
763 po_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_TO_LOCATION';
764 ELSIF po_system_options.tax_hier_item(i) = j THEN
765 po_system_options.DEFAULT_HIERARCHY5(i) := 'ITEM';
766 ELSIF po_system_options.tax_hier_vendor_site(i) = j THEN
767 po_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_FROM_PARTY_SITE';
768 ELSIF po_system_options.tax_hier_vendor(i) = j THEN
769 po_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_FROM_PARTY';
770 ELSIF po_system_options.tax_hier_system(i) = j THEN
771 po_system_options.DEFAULT_HIERARCHY5(i) := 'FINANCIAL_OPTIONS';
772 END IF;
773 END IF;
774 END LOOP;
775 END LOOP;
776
777 FORALL i in 1..nvl(po_system_options.org_id.LAST,0)
778 INSERT INTO ZX_PRODUCT_OPTIONS_ALL (
779 PRODUCT_OPTIONS_ID,
780 ORG_ID,
781 APPLICATION_ID,
782 DEF_OPTION_HIER_1_CODE,
783 DEF_OPTION_HIER_2_CODE,
784 DEF_OPTION_HIER_3_CODE,
785 DEF_OPTION_HIER_4_CODE,
786 DEF_OPTION_HIER_5_CODE,
787 TAX_CLASSIFICATION_CODE,
788 TAX_METHOD_CODE,
789 --INCLUSIVE_TAX_USED_FLAG,
790 TAX_USE_CUSTOMER_EXEMPT_FLAG,
791 TAX_USE_PRODUCT_EXEMPT_FLAG,
792 TAX_USE_LOC_EXC_RATE_FLAG,
793 --TAX_ALLOW_COMPOUND_FLAG,
794 TAX_ROUNDING_RULE,
795 TAX_PRECISION,
796 TAX_MINIMUM_ACCOUNTABLE_UNIT,
797 USE_TAX_CLASSIFICATION_FLAG,
798 HOME_COUNTRY_DEFAULT_FLAG,
799 OBJECT_VERSION_NUMBER,
800 RECORD_TYPE_CODE,
801 CREATION_DATE,
802 CREATED_BY,
803 LAST_UPDATE_DATE,
804 LAST_UPDATED_BY,
805 LAST_UPDATE_LOGIN
806 )
807 values (ZX_PRODUCT_OPTIONS_ALL_S.nextval,
808 po_system_options.ORG_ID(i),
809 201, --application_id
810 po_system_options.DEFAULT_HIERARCHY1(i),
811 po_system_options.DEFAULT_HIERARCHY2(i),
812 po_system_options.DEFAULT_HIERARCHY3(i),
813 po_system_options.DEFAULT_HIERARCHY4(i),
814 po_system_options.DEFAULT_HIERARCHY5(i),
815 po_system_options.TAX_CODE(i),
816 po_system_options.TAX_METHOD_CODE(i),
817 --po_system_options.INCLUSIVE_TAX_USED_FLAG(i),
818 po_system_options.TAX_USE_CUSTOMER_EXEMPT_FLAG(i), --tax_use_customer_exempt_flag
819 po_system_options.TAX_USE_PRODUCT_EXEMPT_FLAG(i), --tax_use_product_exempt_flag
820 po_system_options.TAX_USE_LOC_EXC_RATE_FLAG(i), --tax_use_loc_exc_rate_flag
821 --po_system_options.TAX_ALLOW_COMPOUND_FLAG(i), --tax_allow_compound_flag
822 po_system_options.TAX_ROUNDING_RULE(i), --tax_rounding_rule
823 po_system_options.TAX_PRECISION(i), --tax_precision
824 po_system_options.TAX_MINIMUM_ACCOUNTABLE_UNIT(i), --tax_minimum_accountable_unit
825 po_system_options.USE_TAX_CLASSIFICATION_FLAG(i),
826 po_system_options.HOME_COUNTRY_DEFAULT_FLAG(i),
827 1, --object_version_number
828 'MIGRATED', --record_type_code
829 sysdate, --creation_date
830 fnd_global.user_Id, --created_by
831 sysdate, --last_updated_by
832 fnd_global.user_id, --last_updated_by
833 fnd_global.conc_login_id --last_update_login
834 );
835
836 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
838 END IF;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 x_return_status := FND_API.G_RET_STS_ERROR ;
843 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
844 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','PO Tax Options Migration : '||SQLERRM);
845 FND_MSG_PUB.Add;
846 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
847 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
848 END IF;
849 END PO_PRD_OPTIONS_MIGRATE;
850
851 PROCEDURE PA_PRD_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
852 pa_system_options system_options_rec;
853 l_api_name VARCHAR2(30) := 'PA_TAX_OPTIONS_MIGRATE';
854
855 BEGIN
856 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
858 END IF;
859
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861
862 SELECT decode(l_multi_org_flag,'N',l_org_id, sys.org_id), --org_id
863 output_tax_hier_site,
864 output_tax_hier_cust,
865 output_tax_hier_project,
866 output_tax_hier_exp_ev,
867 output_tax_hier_extn,
868 output_tax_hier_ar_param,
869 greatest(nvl(output_tax_hier_site,-1),nvl(output_tax_hier_cust,-1),
870 nvl(output_tax_hier_project,-1),nvl(output_tax_hier_exp_ev,-1),
871 nvl(output_tax_hier_extn,-1),nvl(output_tax_hier_ar_param,-1)),
872 least(nvl(output_tax_hier_site,999),nvl(output_tax_hier_cust,999),
873 nvl(output_tax_hier_project,999),nvl(output_tax_hier_exp_ev,999),
874 nvl(output_tax_hier_extn,999),nvl(output_tax_hier_ar_param,999)),
875 null, --default_hierarchy1
876 null, --default_hierarchy2
877 null, --default_hierarchy3
878 null, --default_hierarchy4
879 null, --default_hierarchy5
880 null, --default_hierarchy6
881 null, --default_hierarchy7
882 /*If AR system options enabled, get the tax code from AR.
883 If AR tax code is null and tax method is 'SALES_TAX, retrieve tax from location*/
884 decode(sys.output_tax_use_ar_param_flag,'Y', --tax_code Bug 5753907
885 decode(receivable.tax_code,null,
886 decode (receivable.TAX_METHOD,'SALES_TAX',
887 get_location_tax(decode(l_multi_org_flag,'N',l_org_id,receivable.org_id), receivable.set_of_books_id),null),receivable.tax_code),null),
888 null, --tax_method_code
889 --null, --inclusive_tax_used_flag
890 null, --tax_use_customer_exempt_flag
891 null, --tax_use_product_exempt_flag
892 null, --tax_use_loc_exc_rate_flag
893 --null, --tax_allow_compound_flag
894 null, --tax_rounding_rule
895 null, --tax_precision
896 null, --tax_minimum_accountable_unit
897 decode (sys.output_tax_hier_site||sys.output_tax_hier_cust|| --use_tax_classification_flag
898 sys.output_tax_hier_project||sys.output_tax_hier_exp_ev||
899 sys.output_tax_hier_extn||sys.output_tax_hier_ar_param,null, 'N','Y'),
900 decode(sys.output_tax_hier_ar_param,'Y', --home_country_default_flag
901 decode(receivable.tax_code,null,
902 decode (receivable.TAX_METHOD,'SALES_TAX','Y','N'),'N'),'N')
903 BULK COLLECT INTO
904 pa_system_options.org_id,
905 pa_system_options.output_tax_hier_site,
906 pa_system_options.output_tax_hier_cust,
907 pa_system_options.output_tax_hier_project,
908 pa_system_options.output_tax_hier_exp_ev,
909 pa_system_options.output_tax_hier_extn,
910 pa_system_options.output_tax_hier_ar_param,
911 pa_system_options.max_value,
912 pa_system_options.min_value ,
913 pa_system_options.default_hierarchy1,
914 pa_system_options.default_hierarchy2,
915 pa_system_options.default_hierarchy3,
919 pa_system_options.default_hierarchy7,
916 pa_system_options.default_hierarchy4,
917 pa_system_options.default_hierarchy5,
918 pa_system_options.default_hierarchy6,
920 pa_system_options.tax_code,
921 pa_system_options.tax_method_code,
922 --pa_system_options.inclusive_tax_used_flag,
923 pa_system_options.tax_use_customer_exempt_flag,
924 pa_system_options.tax_use_product_exempt_flag,
925 pa_system_options.tax_use_loc_exc_rate_flag,
926 --pa_system_options.tax_allow_compound_flag,
927 pa_system_options.tax_rounding_rule,
928 pa_system_options.tax_precision,
929 pa_system_options.tax_minimum_accountable_unit,
930 pa_system_options.use_tax_classification_flag,
931 pa_system_options.home_country_default_flag
932 FROM PA_IMPLEMENTATIONS_ALL sys,
933 AR_SYSTEM_PARAMETERS_ALL receivable
934 WHERE decode(l_multi_org_flag,'N',l_org_id,receivable.ORG_ID(+)) = decode(l_multi_org_flag,'N',l_org_id,sys.ORG_ID)
935 AND NOT EXISTS (SELECT 1
936 FROM ZX_PRODUCT_OPTIONS_ALL prd
937 WHERE prd.ORG_ID = decode(l_multi_org_flag,'N',l_org_id,sys.org_id)
938 AND prd.APPLICATION_ID = 275
939 );
940
941 FOR i in 1..nvl(pa_system_options.org_id.LAST,0) LOOP
942 FOR j in pa_system_options.min_value(i)..pa_system_options.max_value(i) LOOP
943 IF pa_system_options.DEFAULT_HIERARCHY1(i) is null THEN
944 IF pa_system_options.output_tax_hier_site(i) = j THEN
945 pa_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_TO_PARTY_SITE';
946 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
947 pa_system_options.DEFAULT_HIERARCHY1(i) := 'SHIP_TO_PARTY';
948 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
949 pa_system_options.DEFAULT_HIERARCHY1(i) := 'PROJECT';
950 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
951 pa_system_options.DEFAULT_HIERARCHY1(i) := 'TYPE';
952 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
953 pa_system_options.DEFAULT_HIERARCHY1(i) := 'CLIENT_EXTENSION';
954 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
955 pa_system_options.DEFAULT_HIERARCHY1(i) := 'SYSTEM_OPTIONS';
956 END IF;
957 ELSIF pa_system_options.DEFAULT_HIERARCHY2(i) is null THEN
958 IF pa_system_options.output_tax_hier_site(i) = j THEN
959 pa_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_TO_PARTY_SITE';
960 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
961 pa_system_options.DEFAULT_HIERARCHY2(i) := 'SHIP_TO_PARTY';
962 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
963 pa_system_options.DEFAULT_HIERARCHY2(i) := 'PROJECT';
964 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
965 pa_system_options.DEFAULT_HIERARCHY2(i) := 'TYPE';
966 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
967 pa_system_options.DEFAULT_HIERARCHY2(i) := 'CLIENT_EXTENSION';
968 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
969 pa_system_options.DEFAULT_HIERARCHY2(i) := 'SYSTEM_OPTIONS';
970 END IF;
971 ELSIF pa_system_options.DEFAULT_HIERARCHY3(i) is null THEN
972 IF pa_system_options.output_tax_hier_site(i) = j THEN
973 pa_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_TO_PARTY_SITE';
974 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
975 pa_system_options.DEFAULT_HIERARCHY3(i) := 'SHIP_TO_PARTY';
976 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
977 pa_system_options.DEFAULT_HIERARCHY3(i) := 'PROJECT';
978 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
979 pa_system_options.DEFAULT_HIERARCHY3(i) := 'TYPE';
980 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
981 pa_system_options.DEFAULT_HIERARCHY3(i) := 'CLIENT_EXTENSION';
982 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
983 pa_system_options.DEFAULT_HIERARCHY3(i) := 'SYSTEM_OPTIONS';
984 END IF;
985 ELSIF pa_system_options.DEFAULT_HIERARCHY4(i) is null THEN
986 IF pa_system_options.output_tax_hier_site(i) = j THEN
987 pa_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_TO_PARTY_SITE';
988 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
989 pa_system_options.DEFAULT_HIERARCHY4(i) := 'SHIP_TO_PARTY';
990 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
991 pa_system_options.DEFAULT_HIERARCHY4(i) := 'PROJECT';
992 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
993 pa_system_options.DEFAULT_HIERARCHY4(i) := 'TYPE';
994 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
995 pa_system_options.DEFAULT_HIERARCHY4(i) := 'CLIENT_EXTENSION';
996 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
997 pa_system_options.DEFAULT_HIERARCHY4(i) := 'SYSTEM_OPTIONS';
998 END IF;
999 ELSIF pa_system_options.DEFAULT_HIERARCHY5(i) is null THEN
1000 IF pa_system_options.output_tax_hier_site(i) = j THEN
1001 pa_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_TO_PARTY_SITE';
1002 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
1003 pa_system_options.DEFAULT_HIERARCHY5(i) := 'SHIP_TO_PARTY';
1004 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
1005 pa_system_options.DEFAULT_HIERARCHY5(i) := 'PROJECT';
1006 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
1007 pa_system_options.DEFAULT_HIERARCHY5(i) := 'TYPE';
1008 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
1009 pa_system_options.DEFAULT_HIERARCHY5(i) := 'CLIENT_EXTENSION';
1010 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
1011 pa_system_options.DEFAULT_HIERARCHY5(i) := 'SYSTEM_OPTIONS';
1012 END IF;
1013 ELSIF pa_system_options.DEFAULT_HIERARCHY6(i) is null THEN
1014 IF pa_system_options.output_tax_hier_site(i) = j THEN
1015 pa_system_options.DEFAULT_HIERARCHY6(i) := 'SHIP_TO_PARTY_SITE';
1016 ELSIF pa_system_options.output_tax_hier_cust(i) = j THEN
1017 pa_system_options.DEFAULT_HIERARCHY6(i) := 'SHIP_TO_PARTY';
1018 ELSIF pa_system_options.output_tax_hier_project(i) = j THEN
1019 pa_system_options.DEFAULT_HIERARCHY6(i) := 'PROJECT';
1020 ELSIF pa_system_options.output_tax_hier_exp_ev(i) = j THEN
1021 pa_system_options.DEFAULT_HIERARCHY6(i) := 'TYPE';
1022 ELSIF pa_system_options.output_tax_hier_extn(i) = j THEN
1023 pa_system_options.DEFAULT_HIERARCHY6(i) := 'CLIENT_EXTENSION';
1024 ELSIF pa_system_options.output_tax_hier_ar_param(i) = j THEN
1025 pa_system_options.DEFAULT_HIERARCHY6(i) := 'SYSTEM_OPTIONS';
1026 END IF;
1027 END IF;
1028 END LOOP;
1029 END LOOP;
1030
1031 FORALL i in 1..nvl(pa_system_options.org_id.LAST,0)
1032 INSERT INTO ZX_PRODUCT_OPTIONS_ALL (
1033 PRODUCT_OPTIONS_ID,
1034 ORG_ID,
1035 APPLICATION_ID,
1036 DEF_OPTION_HIER_1_CODE,
1037 DEF_OPTION_HIER_2_CODE,
1038 DEF_OPTION_HIER_3_CODE,
1039 DEF_OPTION_HIER_4_CODE,
1040 DEF_OPTION_HIER_5_CODE,
1041 DEF_OPTION_HIER_6_CODE,
1042 TAX_CLASSIFICATION_CODE,
1043 TAX_METHOD_CODE,
1044 --INCLUSIVE_TAX_USED_FLAG,
1045 TAX_USE_CUSTOMER_EXEMPT_FLAG,
1046 TAX_USE_PRODUCT_EXEMPT_FLAG,
1047 TAX_USE_LOC_EXC_RATE_FLAG,
1048 --TAX_ALLOW_COMPOUND_FLAG,
1049 TAX_ROUNDING_RULE,
1050 TAX_PRECISION,
1051 TAX_MINIMUM_ACCOUNTABLE_UNIT,
1052 USE_TAX_CLASSIFICATION_FLAG,
1053 HOME_COUNTRY_DEFAULT_FLAG,
1054 OBJECT_VERSION_NUMBER,
1055 RECORD_TYPE_CODE,
1056 CREATION_DATE,
1057 CREATED_BY,
1058 LAST_UPDATE_DATE,
1059 LAST_UPDATED_BY,
1060 LAST_UPDATE_LOGIN
1061 )
1062 values (ZX_PRODUCT_OPTIONS_ALL_S.nextval,
1063 pa_system_options.org_id(i),
1064 275, --application_id
1065 pa_system_options.DEFAULT_HIERARCHY1(i),
1066 pa_system_options.DEFAULT_HIERARCHY2(i),
1067 pa_system_options.DEFAULT_HIERARCHY3(i),
1068 pa_system_options.DEFAULT_HIERARCHY4(i),
1069 pa_system_options.DEFAULT_HIERARCHY5(i),
1070 pa_system_options.DEFAULT_HIERARCHY6(i),
1071 pa_system_options.TAX_CODE(i),
1072 pa_system_options.TAX_METHOD_CODE(i),
1073 --pa_system_options.INCLUSIVE_TAX_USED_FLAG(i),
1074 pa_system_options.TAX_USE_CUSTOMER_EXEMPT_FLAG(i), --tax_use_customer_exempt_flag
1075 pa_system_options.TAX_USE_PRODUCT_EXEMPT_FLAG(i), --tax_use_product_exempt_flag
1076 pa_system_options.TAX_USE_LOC_EXC_RATE_FLAG(i), --tax_use_loc_exc_rate_flag
1077 --pa_system_options.TAX_ALLOW_COMPOUND_FLAG(i), --tax_allow_compound_flag
1078 pa_system_options.TAX_ROUNDING_RULE(i), --tax_rounding_rule
1079 pa_system_options.TAX_PRECISION(i), --tax_precision
1080 pa_system_options.TAX_MINIMUM_ACCOUNTABLE_UNIT(i), --tax_minimum_accountable_unit
1081 pa_system_options.USE_TAX_CLASSIFICATION_FLAG(i),
1082 pa_system_options.HOME_COUNTRY_DEFAULT_FLAG(i),
1083 1, --object_version_number
1084 'MIGRATED', --record_type_code
1085 sysdate, --creation_date
1086 fnd_global.user_Id, --created_by
1087 sysdate, --last_updated_by
1088 fnd_global.user_id, --last_updated_by
1089 fnd_global.conc_login_id --last_update_login
1090 );
1091
1092
1093
1094 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1095 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
1096 END IF;
1097
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 x_return_status := FND_API.G_RET_STS_ERROR ;
1101 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1102 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','PA Tax Options Migration : '||SQLERRM);
1103 FND_MSG_PUB.Add;
1104 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1105 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1106 END IF;
1107 END PA_PRD_OPTIONS_MIGRATE;
1108
1109
1110 PROCEDURE OIE_PRD_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
1111
1112 l_api_name VARCHAR2(30) := 'OIE_TAX_OPTIONS_MIGRATE';
1113
1114 BEGIN
1115 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1117 END IF;
1118
1119 x_return_status := FND_API.G_RET_STS_SUCCESS;
1120
1121 INSERT INTO ZX_PRODUCT_OPTIONS_ALL (
1122 PRODUCT_OPTIONS_ID,
1123 ORG_ID,
1124 APPLICATION_ID,
1125 EVENT_CLASS_MAPPING_ID,
1126 DEF_OPTION_HIER_1_CODE,
1127 DEF_OPTION_HIER_2_CODE,
1128 DEF_OPTION_HIER_3_CODE,
1129 DEF_OPTION_HIER_4_CODE,
1130 DEF_OPTION_HIER_5_CODE,
1131 DEF_OPTION_HIER_6_CODE,
1132 DEF_OPTION_HIER_7_CODE,
1133 TAX_CLASSIFICATION_CODE,
1134 TAX_METHOD_CODE,
1135 --INCLUSIVE_TAX_USED_FLAG,
1136 TAX_USE_CUSTOMER_EXEMPT_FLAG,
1137 TAX_USE_PRODUCT_EXEMPT_FLAG,
1138 TAX_USE_LOC_EXC_RATE_FLAG,
1139 --TAX_ALLOW_COMPOUND_FLAG,
1140 TAX_ROUNDING_RULE,
1141 TAX_PRECISION,
1142 TAX_MINIMUM_ACCOUNTABLE_UNIT,
1143 USE_TAX_CLASSIFICATION_FLAG,
1144 HOME_COUNTRY_DEFAULT_FLAG,
1145 OBJECT_VERSION_NUMBER,
1146 RECORD_TYPE_CODE,
1147 CREATION_DATE,
1148 CREATED_BY,
1149 LAST_UPDATE_DATE,
1150 LAST_UPDATED_BY,
1151 LAST_UPDATE_LOGIN
1152 )
1153 SELECT ZX_PRODUCT_OPTIONS_ALL_S.nextval,
1154 decode(l_multi_org_flag,'N',l_org_id,prd.org_id),
1155 prd.application_id,
1156 mapp.event_class_mapping_id,
1157 prd.def_option_hier_1_code,
1158 prd.def_option_hier_2_code,
1159 prd.def_option_hier_3_code,
1160 prd.def_option_hier_4_code,
1161 prd.def_option_hier_5_code,
1162 prd.def_option_hier_6_code,
1163 prd.def_option_hier_7_code,
1164 prd.tax_classification_code,
1165 prd.tax_method_code,
1166 --prd.inclusive_tax_used_flag,
1167 prd.tax_use_customer_exempt_flag,
1168 prd.tax_use_product_exempt_flag,
1169 prd.tax_use_loc_exc_rate_flag,
1170 --prd.tax_allow_compound_flag,
1171 prd.tax_rounding_rule,
1172 prd.tax_precision,
1173 prd.tax_minimum_accountable_unit,
1174 prd.use_tax_classification_flag,
1175 prd.home_country_default_flag,
1176 prd.object_version_number,
1177 prd.record_type_code,
1178 prd.creation_date,
1179 prd.created_by,
1180 prd.last_update_date,
1181 prd.last_updated_by,
1182 prd.last_update_login
1183 FROM ZX_PRODUCT_OPTIONS_ALL prd,
1184 ZX_EVNT_CLS_MAPPINGS mapp
1185 WHERE prd.application_id = 200
1186 AND prd.event_class_mapping_id is null
1187 AND mapp.application_id = prd.application_id
1188 AND mapp.entity_code = 'AP_INVOICES'
1189 AND mapp.event_class_code = 'EXPENSE REPORTS'
1190 AND NOT EXISTS (SELECT 1
1191 FROM ZX_PRODUCT_OPTIONS_ALL prd1
1192 WHERE prd1.ORG_ID = decode(l_multi_org_flag,'N',l_org_id,prd.org_id)
1193 AND prd1.APPLICATION_ID = 200
1194 AND prd1.event_class_mapping_id = (SELECT event_class_mapping_id
1195 FROM zx_evnt_cls_mappings
1196 WHERE application_id = 200
1197 AND entity_code = 'AP_INVOICES'
1198 AND event_class_code = 'EXPENSE REPORTS')
1199 );
1200
1201
1202 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1203 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
1204 END IF;
1205
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 x_return_status := FND_API.G_RET_STS_ERROR ;
1209 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1210 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','PA Tax Options Migration : '||SQLERRM);
1211 FND_MSG_PUB.Add;
1212 IF ( G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL) THEN
1213 FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,SQLERRM);
1214 END IF;
1215 END OIE_PRD_OPTIONS_MIGRATE;
1216
1217
1218 PROCEDURE MIGRATE_PRODUCT_OPTIONS(x_return_status OUT NOCOPY VARCHAR2)
1219 IS
1220 l_api_name VARCHAR2(30) := 'MIGRATE_PRODUCT_OPTIONS';
1221 l_return_status VARCHAR2(1);
1222
1223 BEGIN
1224 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1225 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
1226 END IF;
1227
1228 x_return_status := FND_API.G_RET_STS_SUCCESS;
1229
1230 /*-----------------------------------------------------+
1231 | Process records from AR |
1232 +-----------------------------------------------------*/
1233 AR_PRD_OPTIONS_MIGRATE(l_return_status);
1234
1235
1236 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1237 x_return_status := FND_API.G_RET_STS_ERROR ;
1238 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1239 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Product Tax Options Migration : '||SQLERRM);
1240 FND_MSG_PUB.Add;
1241 RETURN;
1242 END IF;
1243
1244
1245 /*-----------------------------------------------------+
1246 | Process records from AP |
1247 +-----------------------------------------------------*/
1248 AP_PRD_OPTIONS_MIGRATE (l_return_status);
1249
1250 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1251 x_return_status := FND_API.G_RET_STS_ERROR ;
1252 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1253 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Product Tax Options Migration : '||SQLERRM);
1254 FND_MSG_PUB.Add;
1255 RETURN;
1256 END IF;
1257
1258
1259 /*-----------------------------------------------------+
1260 | Process records from PO |
1261 +-----------------------------------------------------*/
1262 PO_PRD_OPTIONS_MIGRATE (l_return_status);
1263
1264 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1265 x_return_status := FND_API.G_RET_STS_ERROR ;
1266 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1267 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Product Tax Options Migration : '||SQLERRM);
1268 FND_MSG_PUB.Add;
1269 RETURN;
1270 END IF;
1271
1272
1273 /*-----------------------------------------------------+
1274 | Process records from PA |
1275 +-----------------------------------------------------*/
1276 PA_PRD_OPTIONS_MIGRATE (l_return_status);
1277
1278 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1279 x_return_status := FND_API.G_RET_STS_ERROR ;
1280 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1281 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Product Tax Options Migration : '||SQLERRM);
1282 FND_MSG_PUB.Add;
1283 RETURN;
1284 END IF;
1285
1286 /*-----------------------------------------------------+
1287 | Process records from OIE |
1288 +-----------------------------------------------------*/
1289 OIE_PRD_OPTIONS_MIGRATE (l_return_status);
1290
1291 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1292 x_return_status := FND_API.G_RET_STS_ERROR ;
1293 FND_MESSAGE.SET_NAME ('ZX','GENERIC_MESSAGE');
1294 FND_MESSAGE.SET_TOKEN ('GENERIC_TEXT','Product Tax Options Migration : '||SQLERRM);
1295 FND_MSG_PUB.Add;
1296 RETURN;
1297 END IF;
1298
1299 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1300 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
1301 END IF;
1302
1303 END MIGRATE_PRODUCT_OPTIONS;
1304
1305 BEGIN
1306
1307 SELECT NVL(MULTI_ORG_FLAG,'N')
1308 INTO L_MULTI_ORG_FLAG
1309 FROM FND_PRODUCT_GROUPS;
1310
1311 IF L_MULTI_ORG_FLAG = 'N' THEN
1312 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
1313 IF L_ORG_ID IS NULL THEN
1314 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
1315 END IF;
1316 ELSE
1317 L_ORG_ID := NULL;
1318 END IF;
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 arp_util_tax.debug('Exception in Migrate Product Options Constructor : '||sqlerrm);
1322
1323 END ZX_PRD_OPTIONS_MIGRATE_PKG;