[Home] [Help]
277: FROM mtl_system_items si
278: WHERE si.inventory_item_id = p_inventory_item_id
279: AND si.organization_id = p_inventory_organization_id
280: AND exists (select 1
281: from ar_vat_tax vt
282: WHERE vt.tax_code = si.tax_code
283: AND vt.tax_type = 'VAT'
284: AND decode(ltrim(vt.global_attribute1, '0123456789'),
285: null, to_number(vt.global_attribute1), null) =
292: SELECT tg.tax_code,
293: NULL,
294: si.inventory_item_id
295: FROM mtl_system_items si
296: ,ar_vat_tax vt
297: ,jl_zz_ar_tx_groups tg
298: ,ar_vat_tax vt1
299: WHERE si.inventory_item_id = p_inventory_item_id
300: AND si.organization_id = p_inventory_organization_id
294: si.inventory_item_id
295: FROM mtl_system_items si
296: ,ar_vat_tax vt
297: ,jl_zz_ar_tx_groups tg
298: ,ar_vat_tax vt1
299: WHERE si.inventory_item_id = p_inventory_item_id
300: AND si.organization_id = p_inventory_organization_id
301: AND si.tax_code = vt.tax_code
302: AND vt.tax_type = 'TAX_GROUP'
322: FROM hz_cust_accounts c
323: WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
324: p_bill_to_customer_id)
325: AND exists (select 1
326: from ar_vat_tax vt
327: WHERE c.tax_code = vt.tax_code
328: AND vt.tax_type = 'VAT'
329: AND decode(ltrim(vt.global_attribute1,
330: '0123456789'),
340: SELECT tg.tax_code,
341: NULL,
342: c.cust_account_id
343: FROM hz_cust_accounts c
344: ,ar_vat_tax vt
345: ,jl_zz_ar_tx_groups tg
346: ,ar_vat_tax vt1
347: WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
348: p_bill_to_customer_id)
342: c.cust_account_id
343: FROM hz_cust_accounts c
344: ,ar_vat_tax vt
345: ,jl_zz_ar_tx_groups tg
346: ,ar_vat_tax vt1
347: WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
348: p_bill_to_customer_id)
349: AND c.tax_code = vt.tax_code
350: AND vt.tax_type = 'TAX_GROUP'
368: su.site_use_id
369: FROM hz_cust_site_uses su
370: WHERE su.site_use_id = p_bill_to_site_use_id
371: AND exists (select 1
372: from ar_vat_tax vt
373: WHERE su.tax_code = vt.tax_code
374: AND vt.tax_type = 'VAT'
375: AND decode(ltrim(vt.global_attribute1,
376: '0123456789'),
385: SELECT tg.tax_code,
386: NULL,
387: su.site_use_id
388: FROM hz_cust_site_uses su
389: ,ar_vat_tax vt
390: ,jl_zz_ar_tx_groups tg
391: ,ar_vat_tax vt1
392: WHERE su.site_use_id = p_bill_to_site_use_id
393: AND su.tax_code = vt.tax_code
387: su.site_use_id
388: FROM hz_cust_site_uses su
389: ,ar_vat_tax vt
390: ,jl_zz_ar_tx_groups tg
391: ,ar_vat_tax vt1
392: WHERE su.site_use_id = p_bill_to_site_use_id
393: AND su.tax_code = vt.tax_code
394: AND vt.tax_type = 'TAX_GROUP'
395: AND tg.group_tax_id = vt.vat_tax_id
412: su.site_use_id
413: FROM hz_cust_site_uses su
414: WHERE su.site_use_id = p_ship_to_site_use_id
415: AND exists (select 1
416: from ar_vat_tax vt
417: WHERE su.tax_code = vt.tax_code
418: AND vt.tax_type = 'VAT'
419: AND decode(ltrim(vt.global_attribute1,
420: '0123456789'),null,
429: SELECT tg.tax_code,
430: NULL,
431: su.site_use_id
432: FROM hz_cust_site_uses su
433: ,ar_vat_tax vt
434: ,jl_zz_ar_tx_groups tg
435: ,ar_vat_tax vt1
436: WHERE su.site_use_id = p_ship_to_site_use_id
437: AND su.tax_code = vt.tax_code
431: su.site_use_id
432: FROM hz_cust_site_uses su
433: ,ar_vat_tax vt
434: ,jl_zz_ar_tx_groups tg
435: ,ar_vat_tax vt1
436: WHERE su.site_use_id = p_ship_to_site_use_id
437: AND su.tax_code = vt.tax_code
438: AND vt.tax_type = 'TAX_GROUP'
439: AND tg.group_tax_id = vt.vat_tax_id
456: p_ship_from_warehouse_id
457: FROM hr_locations_all hrl
458: WHERE hrl.location_id = p_warehouse_location_id
459: AND exists (select 1
460: from ar_vat_tax vt
461: WHERE hrl.global_attribute6 = vt.tax_code
462: AND vt.tax_type = 'VAT'
463: AND decode(ltrim(vt.global_attribute1,'0123456789'),
464: null, to_number(vt.global_attribute1),
479: AND tg.establishment_type = p_establishment_type
480: AND p_trx_date <= tg.end_date_active
481: AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
482: AND exists (select 1
483: from ar_vat_tax vt1,
484: ar_vat_tax vt,
485: hr_locations_all hrl
486: WHERE hrl.location_id = p_warehouse_location_id
487: AND hrl.global_attribute6 = vt.tax_code
480: AND p_trx_date <= tg.end_date_active
481: AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
482: AND exists (select 1
483: from ar_vat_tax vt1,
484: ar_vat_tax vt,
485: hr_locations_all hrl
486: WHERE hrl.location_id = p_warehouse_location_id
487: AND hrl.global_attribute6 = vt.tax_code
488: AND vt.tax_type = 'TAX_GROUP'
499: SELECT sp.tax_classification_code,
500: NULL,
501: NULL
502: FROM zx_product_options sp
503: ,ar_vat_tax vt
504: WHERE sp.application_id = 222
505: AND sp.org_id = vt.org_id
506: AND sp.tax_classification_code = vt.tax_code
507: AND vt.tax_type = 'VAT'
522: AND tg.establishment_type = p_establishment_type
523: AND p_trx_date <= tg.end_date_active
524: AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
525: AND exists (select 1
526: from ar_vat_tax vt1
527: ,ar_vat_tax vt
528: ,zx_product_options sp
529: WHERE sp.tax_classification_code = vt.tax_code
530: AND sp.application_id = 222
523: AND p_trx_date <= tg.end_date_active
524: AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
525: AND exists (select 1
526: from ar_vat_tax vt1
527: ,ar_vat_tax vt
528: ,zx_product_options sp
529: WHERE sp.tax_classification_code = vt.tax_code
530: AND sp.application_id = 222
531: AND sp.org_id = vt.org_id
545: ml.memo_line_id
546: FROM ar_memo_lines ml
547: WHERE ml.memo_line_id = p_memo_line_id
548: AND exists (select 1
549: from ar_vat_tax vt
550: WHERE ml.tax_code = vt.tax_code
551: AND vt.tax_type = 'VAT'
552: AND decode(ltrim(vt.global_attribute1,
553: '0123456789'), null,
562: SELECT tg.tax_code,
563: NULL,
564: ml.memo_line_id
565: FROM ar_memo_lines ml
566: ,ar_vat_tax vt
567: ,jl_zz_ar_tx_groups tg
568: ,ar_vat_tax vt1
569: WHERE memo_line_id = p_memo_line_id
570: AND ml.tax_code = vt.tax_code
564: ml.memo_line_id
565: FROM ar_memo_lines ml
566: ,ar_vat_tax vt
567: ,jl_zz_ar_tx_groups tg
568: ,ar_vat_tax vt1
569: WHERE memo_line_id = p_memo_line_id
570: AND ml.tax_code = vt.tax_code
571: AND vt.tax_type = 'TAX_GROUP'
572: AND tg.group_tax_id = vt.vat_tax_id
1438: tr.rule;
1439:
1440: c_rule_default_rec c_rule_default%ROWTYPE;
1441:
1442: l_tax_code ar_vat_tax.tax_code%type;
1443: l_rule_data_id NUMBER;
1444:
1445:
1446: BEGIN
2242: ,r1.customer_trx_id header_trx_id
2243: FROM ra_customer_trx r1
2244: ,ra_customer_trx_lines rl1
2245: ,ra_customer_trx_lines rlt1
2246: ,ar_vat_tax v1
2247: ,jl_zz_ar_tx_att_cls tcl1
2248: ,jl_zz_ar_tx_categ tc1
2249: WHERE r1.customer_trx_id = rl1.customer_trx_id
2250: AND r1.customer_trx_id = p_prev_cust_trx_id
2275: ,r1.customer_trx_id header_trx_id
2276: FROM ra_customer_trx r1
2277: ,ra_customer_trx_lines rl1
2278: ,ra_customer_trx_lines rlt1
2279: ,ar_vat_tax v1
2280: ,jl_zz_ar_tx_att_cls tcl1
2281: ,jl_zz_ar_tx_categ tc1
2282: WHERE r1.customer_trx_id = rl1.customer_trx_id
2283: AND r1.related_customer_trx_id = p_prev_cust_trx_id
2308: ,r1.customer_trx_id header_trx_id
2309: FROM ra_customer_trx r1
2310: ,ra_customer_trx_lines rl1
2311: ,ra_customer_trx_lines rlt1
2312: ,ar_vat_tax v1
2313: ,jl_zz_ar_tx_att_cls tcl1
2314: ,jl_zz_ar_tx_categ tc1
2315: WHERE r1.customer_trx_id = rl1.customer_trx_id
2316: AND r1.previous_customer_trx_id = p_prev_cust_trx_id
2342: FROM ra_customer_trx r1
2343: ,ra_customer_trx r2
2344: ,ra_customer_trx_lines rl1
2345: ,ra_customer_trx_lines rlt1
2346: ,ar_vat_tax v1
2347: ,jl_zz_ar_tx_att_cls tcl1
2348: ,jl_zz_ar_tx_categ tc1
2349: WHERE r2.related_customer_trx_id = p_prev_cust_trx_id
2350: AND r2.customer_trx_id = r1.previous_customer_trx_id
2381: ,r1.customer_trx_id header_trx_id
2382: FROM ra_customer_trx r1
2383: ,ra_customer_trx_lines rl1
2384: ,ra_customer_trx_lines rlt1
2385: ,ar_vat_tax v1
2386: ,jl_zz_ar_tx_att_cls tcl1
2387: ,jl_zz_ar_tx_categ tc1
2388: WHERE r1.customer_trx_id = rl1.customer_trx_id
2389: AND r1.customer_trx_id = p_rel_cust_trx_id
2414: ,r1.customer_trx_id header_trx_id
2415: FROM ra_customer_trx r1
2416: ,ra_customer_trx_lines rl1
2417: ,ra_customer_trx_lines rlt1
2418: ,ar_vat_tax v1
2419: ,jl_zz_ar_tx_att_cls tcl1
2420: ,jl_zz_ar_tx_categ tc1
2421: WHERE r1.customer_trx_id = rl1.customer_trx_id
2422: AND r1.related_customer_trx_id = p_rel_cust_trx_id
2447: ,r1.customer_trx_id header_trx_id
2448: FROM ra_customer_trx r1
2449: ,ra_customer_trx_lines rl1
2450: ,ra_customer_trx_lines rlt1
2451: ,ar_vat_tax v1
2452: ,jl_zz_ar_tx_att_cls tcl1
2453: ,jl_zz_ar_tx_categ tc1
2454: WHERE r1.customer_trx_id = rl1.customer_trx_id
2455: AND r1.previous_customer_trx_id = p_rel_cust_trx_id
2481: FROM ra_customer_trx r1
2482: ,ra_customer_trx r2
2483: ,ra_customer_trx_lines rl1
2484: ,ra_customer_trx_lines rlt1
2485: ,ar_vat_tax v1
2486: ,jl_zz_ar_tx_att_cls tcl1
2487: ,jl_zz_ar_tx_categ tc1
2488: WHERE r2.related_customer_trx_id = p_rel_cust_trx_id
2489: AND r2.customer_trx_id = r1.previous_customer_trx_id
2515: ,r1.customer_trx_id header_trx_id
2516: FROM ra_customer_trx r1
2517: ,ra_customer_trx_lines rl1
2518: ,ra_customer_trx_lines rlt1
2519: ,ar_vat_tax v1
2520: ,jl_zz_ar_tx_att_cls tcl1
2521: ,jl_zz_ar_tx_categ tc1
2522: WHERE r1.customer_trx_id = rl1.customer_trx_id
2523: AND r1.customer_trx_id = p_curr_cust_trx_id
2540:
2541: cursor rel_trx_categ is
2542: SELECT tc1.tax_category_id tax_category_id
2543: FROM ra_customer_trx_lines rlt1
2544: ,ar_vat_tax v1
2545: ,jl_zz_ar_tx_categ tc1
2546: WHERE rlt1.customer_trx_id = p_rel_cust_trx_id
2547: AND rlt1.line_type= 'TAX'
2548: AND rlt1.vat_tax_id = v1.vat_tax_id
2552:
2553: cursor cur_trx_categ is
2554: SELECT tc1.tax_category_id tax_category_id
2555: FROM ra_customer_trx_lines rlt1
2556: ,ar_vat_tax v1
2557: ,jl_zz_ar_tx_categ tc1
2558: WHERE rlt1.customer_trx_id = p_curr_cust_trx_id
2559: AND rlt1.line_type= 'TAX'
2560: AND rlt1.vat_tax_id = v1.vat_tax_id
2796:
2797: l_rel_tax_line_amt_ctr BINARY_INTEGER;
2798: l_rel_tax_lines_ctr BINARY_INTEGER;
2799: l_rule_code jl_zz_ar_tx_rules.rule%type;
2800: l_tax_code ar_vat_tax.tax_code%type;
2801: l_grp_attr_name VARCHAR2(30);
2802: l_grp_attr_val VARCHAR2(30);
2803: l_operation_level VARCHAR2(30);
2804: l_func_curr_taxable_base NUMBER;
2960: IF NVL(l_operation_level,'LINE') <> 'LINE' AND p_taxable_basis = 'QUANTITY' THEN
2961: BEGIN
2962: select taxable_basis
2963: into p_taxable_basis
2964: from ar_vat_tax vt
2965: where decode(ltrim(vt.global_attribute1, '0123456789')
2966: , null, to_number(vt.global_attribute1)
2967: , null) = p_tax_category_id
2968: AND nvl(vt.enabled_flag,'Y') = 'Y'
3116: l_sch_taxable_basis := NULL;
3117: BEGIN
3118: SELECT tax_rate,taxable_basis
3119: INTO l_sch_tax_rate,l_sch_taxable_basis
3120: FROM ar_vat_tax
3121: WHERE tax_code = tax_schedule_rec.tax_code
3122: AND set_of_books_id = p_set_of_books_id
3123: AND start_date <= p_trx_date
3124: AND NVL(end_date,p_trx_date) >= p_trx_date
3654: RAISE ERROR_FROM_FUNCTION;
3655: ELSE
3656: SELECT vt.tax_rate,vt.taxable_basis
3657: INTO v_aux_tax_rate,v_aux_taxable_basis
3658: FROM ar_vat_tax vt
3659: WHERE vt.tax_code = v_aux_tax_code
3660: AND vt.set_of_books_id = v_set_of_books_id
3661: AND vt.start_date <= trunc(p_trx_date)
3662: AND nvl(vt.end_date, trunc(p_trx_date)) >= trunc(p_trx_date)
4100: INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag,
4101: v_use_legal_message,
4102: v_legal_message_exception,
4103: l_exchange_rate
4104: FROM ar_vat_tax vt,
4105: ar_system_parameters_all sp
4106: WHERE vt.vat_tax_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id
4107: AND nvl(vt.org_id,-99) = nvl(sp.org_id,-99);
4108:
4353: l_vat_tax_id := NULL;
4354: BEGIN
4355: SELECT vat_tax_id
4356: INTO l_vat_tax_id
4357: FROM ar_vat_tax
4358: WHERE tax_code = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code
4359: AND set_of_books_id = ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id
4360: AND tax_type = 'VAT'
4361: AND to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD') >= start_date