[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT INV_TYPE.CUST_TRX_TYPE_ID,
INV_TYPE.TAX_CALCULATION_FLAG
INTO l_cust_trx_type_id,
l_tax_calculation_flag
FROM OE_LINE_TYPES_V LINE_TYPE, --OE_TRANSACTION_TYPES_VL LINE_TYPE,
RA_CUST_TRX_TYPES INV_TYPE
WHERE LINE_TYPE.TRANSACTION_TYPE_ID = p_line_type_id
--AND LINE_TYPE.TRANSACTION_TYPE_CODE = 'LINE'
AND LINE_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID
AND nvl(inv_type.org_id,-99) = nvl(line_type.org_id,-99) --Bugfix 2367111
AND nvl(inv_type.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT INV_TYPE.CUST_TRX_TYPE_ID,
INV_TYPE.TAX_CALCULATION_FLAG
INTO l_cust_trx_type_id,
l_tax_calculation_flag
FROM OE_ORDER_TYPES_V ORD_TYPE,
OE_ORDER_HEADERS ORD_HEADER,
RA_CUST_TRX_TYPES INV_TYPE
WHERE ORD_TYPE.ORDER_TYPE_ID = ORD_HEADER.ORDER_TYPE_ID
AND ORD_HEADER.HEADER_ID = p_header_id
AND ORD_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID;
SELECT INV_TYPE.CUST_TRX_TYPE_ID,
INV_TYPE.TAX_CALCULATION_FLAG
INTO l_cust_trx_type_id,
l_tax_calculation_flag
FROM OE_ORDER_TYPES_V ORD_TYPE,
RA_CUST_TRX_TYPES INV_TYPE
WHERE ORD_TYPE.ORDER_TYPE_ID = p_line_type_id
AND ORD_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID;
SELECT INV_TYPE.CUST_TRX_TYPE_ID,
INV_TYPE.TAX_CALCULATION_FLAG
INTO l_cust_trx_type_id,
l_tax_calculation_flag
FROM RA_CUST_TRX_TYPES INV_TYPE
WHERE INV_TYPE.CUST_TRX_TYPE_ID =
FND_PROFILE.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID');
SELECT SYS.LOCATION_STRUCTURE_ID,
TO_NUMBER(SYS.GLOBAL_ATTRIBUTE10),
SYS.SET_OF_BOOKS_ID,
SYS.TAX_ROUNDING_ALLOW_OVERRIDE,
SYS.TAX_HEADER_LEVEL_FLAG,
TAX_ROUNDING_RULE,
SYS.GLOBAL_ATTRIBUTE13
INTO l_location_structure_id,
l_location_segment_num,
l_set_of_books_id,
l_tax_rounding_allow_override,
l_tax_header_level_flag,
l_tax_rounding_rule,
l_tax_rule_set
FROM ar_system_parameters_all sys --Bugfix 2367111
WHERE nvl(sys.org_id,-99) = nvl(p_org_id,-99); --Bugfix 2367111;
SELECT HRL.LOCATION_ID,
NVL(HRL.GLOBAL_ATTRIBUTE1, 'DEFAULT')
INTO l_location_id,
l_org_class
FROM HR_LOCATIONS_ALL HRL,
HR_ORGANIZATION_UNITS ORG
WHERE ORG.LOCATION_ID = HRL.LOCATION_ID
AND ORG.ORGANIZATION_ID = NVL(p_ship_from_org_id,
NVL(p_invoice_to_org_id,
OE_PROFILE.VALUE('SO_ORGANIZATION_ID')));
SELECT vat.TAXABLE_BASIS,
vat.TAX_CALCULATION_PLSQL_BLOCK,
vat.TAX_TYPE,
decode(vat.tax_type,'TAX_GROUP',vat.vat_tax_id,null),
decode(vat.tax_type,'TAX_GROUP',NULL,
decode (length(translate(vat.global_attribute1,
'0123456789 ', '0123456789')),
length(translate(vat.global_attribute1, '0123456789
ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_-,:.',
'0123456789')), vat.global_attribute1, -99))
INTO l_taxable_basis,
l_tax_calculation_plsql_block,
l_tax_type,
l_vat_tax_id,
l_tax_category_id
FROM ar_vat_tax_all_b vat --Bugfix 2367111
WHERE vat.set_of_books_id = l_set_of_books_id
AND vat.tax_code = p_tax_code
AND p_tax_date BETWEEN vat.start_date
AND NVL(vat.end_date, TO_DATE( '31122199', 'DDMMYYYY'))
AND NVL(vat.enabled_flag,'Y') = 'Y'
AND NVL(vat.tax_class,'O') = 'O'
AND NVL(vat.org_id,-99) = nvl(l_org_id,99); --Bugfix 2367111
SELECT MINIMUM_ACCOUNTABLE_UNIT,
PRECISION
INTO l_minimum_accountable_unit,
l_precision
FROM FND_CURRENCIES_VL
WHERE currency_code = p_currency_code;
SELECT ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID,
LOC.POSTAL_CODE POSTAL_CODE,
CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
PARTY.PARTY_NAME CUSTOMER_NAME,
ACCT_SITE.CUST_ACCT_SITE_ID NUMERIC_ATTRIBUTE8,
NVL(acct_site.global_attribute8, 'DEFAULT') ATTRIBUTE6,
NVL(acct_site.global_attribute9, 'N') use_site_prof,
DECODE(NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE, 'N'),
'Y', NVL(SITE.TAX_HEADER_LEVEL_FLAG,
NVL(CUST_ACCT.TAX_HEADER_LEVEL_FLAG,
NVL(SYS.TAX_HEADER_LEVEL_FLAG, 'N' ))),
NVL(SYS.TAX_HEADER_LEVEL_FLAG, 'N')) TAX_HEADER_LEVEL_FLAG,
DECODE(NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE, 'N'),
'Y', NVL(SITE.TAX_ROUNDING_RULE,
NVL(CUST_ACCT.TAX_ROUNDING_RULE,
NVL(SYS.TAX_ROUNDING_RULE, 'NEAREST'))),
NVL(SYS.TAX_ROUNDING_RULE, 'NEAREST')) TAX_ROUNDING_RULE
FROM HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_SITE_USES SITE,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
AR_SYSTEM_PARAMETERS SYS
WHERE SITE.SITE_USE_ID = l_site_use_id
AND SITE.SITE_USE_CODE = l_site_use_code
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = NVL(l_cust_id,ACCT_SITE.CUST_ACCOUNT_ID);
SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3,--TTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE3,
TGR.ESTABLISHMENT_TYPE ATTRIBUTE5,--OTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE5,
TGR.TAX_CATEGORY_ID NUMERIC_ATTRIBUTE1,
TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,--CTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE8,
TGR.CALCULATE_IN_OE,
TXC.TAX_CATEGORY
FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
JL_ZZ_AR_TX_CATEG TXC
WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
AND ATC.ENABLED_FLAG = 'Y'
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111)
AND EXISTS (SELECT CTA.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_CUS_CLS CTA
WHERE CTA.ADDRESS_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND CTA.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND CTA.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
AND CTA.ENABLED_FLAG = 'Y'
AND TGR.CONTRIBUTOR_TYPE = CTA.TAX_ATTRIBUTE_VALUE
AND nvl(tgr.org_id,-99) = nvl(cta.org_id,-99)) --Bugfix 2367111
AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
AND ATC.ENABLED_FLAG = 'Y'
AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
AND nvl(tgr.org_id,-99) = nvl(p_org_id,-99) --Bugfix 2367111
AND nvl(tgr.org_id,-99) = nvl(txc.org_id,-99); --Bugfix 2367111
SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3 ,
TGR.ESTABLISHMENT_TYPE ATTRIBUTE5 ,
TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,
TGR.CALCULATE_IN_OE
FROM JL_ZZ_AR_TX_GROUPS TGR ,
JL_ZZ_AR_TX_CATEG TXC
WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >=
NVL(TGR.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
AND NVL(TXC.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
AND ATC.ENABLED_FLAG = 'Y'
AND ATC.TAX_ATTR_CLASS_CODE =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10)
AND EXISTS (SELECT CTA.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_CUS_CLS CTA
WHERE CTA.ADDRESS_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
AND CTA.TAX_ATTR_CLASS_CODE =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND CTA.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND CTA.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
AND CTA.ENABLED_FLAG = 'Y'
AND TGR.CONTRIBUTOR_TYPE = CTA.TAX_ATTRIBUTE_VALUE )
AND EXISTS (SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
AND ATC.ENABLED_FLAG = 'Y');
SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3,--TTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE3,
TGR.ESTABLISHMENT_TYPE ATTRIBUTE5,--OTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE5,
TGR.TAX_CATEGORY_ID NUMERIC_ATTRIBUTE1,
TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,--CTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE8,
TGR.CALCULATE_IN_OE,
TXC.TAX_CATEGORY
FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
JL_ZZ_AR_TX_CATEG TXC
WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
AND ATC.ENABLED_FLAG = 'Y'
AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
AND TGR.CONTRIBUTOR_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND ATC.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
AND ATC.ENABLED_FLAG = 'Y'
AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
AND ATC.ENABLED_FLAG = 'Y'
AND nvl(tgr.org_id,-99) = nvl(atc.org_id,-99)) --Bugfix 2367111
AND nvl(tgr.org_id,-99) = nvl(p_org_id,-99) --Bugfix 2367111
AND nvl(tgr.org_id,-99) = nvl(txc.org_id,-99); --Bugfix 2367111
SELECT TGR.TRANSACTION_NATURE ATTRIBUTE3 ,
TGR.ESTABLISHMENT_TYPE ATTRIBUTE5 ,
TGR.CONTRIBUTOR_TYPE ATTRIBUTE8,
TGR.CALCULATE_IN_OE
FROM JL_ZZ_AR_TX_GROUPS TGR ,
JL_ZZ_AR_TX_CATEG TXC
WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >=
NVL(TGR.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND NVL(TXC.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND TGR.ESTABLISHMENT_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND ATC.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
AND ATC.ENABLED_FLAG = 'Y')
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
AND TGR.CONTRIBUTOR_TYPE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
AND ATC.TAX_ATTR_CLASS_CODE =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND ATC.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
AND ATC.ENABLED_FLAG = 'Y' )
AND EXISTS ( SELECT ATC.TAX_ATTRIBUTE_VALUE
FROM JL_ZZ_AR_TX_ATT_CLS ATC
WHERE ATC.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND TGR.TRANSACTION_NATURE = ATC.TAX_ATTRIBUTE_VALUE
AND ATC.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND TXC.TAX_CATEGORY_ID =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1
AND ATC.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND ATC.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
AND ATC.TAX_ATTR_CLASS_CODE =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
AND ATC.ENABLED_FLAG = 'Y');
SELECT vat.taxable_basis,
vat.tax_calculation_plsql_block,
vat.tax_type,
vat.tax_regime_code,
vat.tax,
vat.tax_status_code,
vat.tax_code
FROM ar_vat_tax_all_b vat --Bugfix 2367111
WHERE vat.set_of_books_id = ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id
AND vat.tax_code = l_tax_code
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date BETWEEN vat.start_date AND
NVL(vat.end_date, TO_DATE('31122199', 'DDMMYYYY'))
AND NVL(vat.enabled_flag,'Y') = 'Y'
AND NVL(vat.tax_class, 'O') = 'O'
AND nvl(vat.org_id,-99) = nvl(p_org_id,-99); --Bugfix 2367111
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='PRODUCT_CATEGORY';
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec_tbl.delete;
SELECT ml.line_type
INTO l_line_type
FROM ar_memo_lines_all_b ml --Bugfix 2367111
WHERE ml.memo_line_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id
AND nvl(ml.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT TGR.TAX_CATEGORY_ID,
TGR.CALCULATE_IN_OE,
TXC.TXN_TAX_ATTRIBUTE,
TXC.ORG_TAX_ATTRIBUTE,
TXC.CUS_TAX_ATTRIBUTE,
TGR.TRANSACTION_NATURE,
TGR.ESTABLISHMENT_TYPE,
TGR.CONTRIBUTOR_TYPE,
TXC.TAX_CATEGORY
FROM JL_ZZ_AR_TX_GROUPS_ALL TGR , --Bugfix 2367111
JL_ZZ_AR_TX_CATEG TXC
WHERE TGR.GROUP_TAX_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND NVL(TXC.START_DATE_ACTIVE, ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE) <=
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.END_DATE_ACTIVE >= ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE
AND TXC.TAX_RULE_SET = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf1
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE <= TGR.END_DATE_ACTIVE
AND ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE >= NVL(TGR.START_DATE_ACTIVE,
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.TRX_DATE)
AND nvl(txc.org_id,-99) = nvl(l_org_id,-99) --Bugfix 2367111
AND nvl(txc.org_id,-99) = nvl(tgr.org_id,-99); --Bugfix 2367111
SELECT TTA.TAX_ATTRIBUTE_VALUE
INTO l_tta_attrib_value
FROM JL_ZZ_AR_TX_ATT_CLS_ALL TTA --Bugfix 2367111
WHERE TTA.TAX_ATTRIBUTE_VALUE
= c_tax_categ_rec.transaction_nature
AND TTA.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND TTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
AND TTA.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND TTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.txn_tax_attribute
AND TTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9
AND TTA.ENABLED_FLAG = 'Y'
AND nvl(tta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT OTA.TAX_ATTRIBUTE_VALUE
INTO l_ota_attrib_value
FROM JL_ZZ_AR_TX_ATT_CLS_ALL OTA --Bugfix 2367111
WHERE OTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.establishment_type
AND OTA.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND OTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
AND OTA.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND OTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.org_tax_attribute
AND OTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf10
AND OTA.ENABLED_FLAG = 'Y'
AND NVL(ota.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT CTA.TAX_ATTRIBUTE_VALUE
INTO l_cta_attrib_value
FROM JL_ZZ_AR_TX_CUS_CLS_ALL CTA --Bugfix 2367111
WHERE CTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.contributor_type
AND CTA.ADDRESS_ID = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern8
AND CTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
AND CTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.cus_tax_attribute
AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND CTA.ENABLED_FLAG = 'Y'
AND nvl(cta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT CTA.TAX_ATTRIBUTE_VALUE
INTO l_cta_attrib_value
FROM JL_ZZ_AR_TX_ATT_CLS_ALL CTA --Bugfix 2367111
WHERE CTA.TAX_ATTRIBUTE_VALUE = c_tax_categ_rec.contributor_type
AND CTA.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS'
AND CTA.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE'
AND CTA.TAX_CATEGORY_ID = c_tax_categ_rec.tax_category_id
AND CTA.TAX_ATTRIBUTE_NAME = c_tax_categ_rec.cus_tax_attribute
AND CTA.TAX_ATTR_CLASS_CODE = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6
AND CTA.ENABLED_FLAG = 'Y'
AND nvl(cta.org_id,-99) = nvl(l_org_id,-99); --Bugfix 2367111
SELECT V.vat_tax_id
INTO l_group_tax_id
FROM RA_CUSTOMER_TRX_LINES_ALL TL,
AR_VAT_TAX_ALL V
WHERE TL.customer_trx_line_id =
ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_line_id
AND TL.TAX_CLASSIFICATION_CODE = V.tax_code --TL.vat_tax_id = V.vat_tax_id
AND TL.org_id = V.org_id
AND TL.org_id = l_org_id
AND V.tax_type = 'TAX_GROUP';