[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_ar_trx_info;
C_LINES_PER_INSERT CONSTANT NUMBER := 5000;
G_LAST_UPDATED_BY NUMBER(15);
G_LAST_UPDATE_DATE DATE;
G_LAST_UPDATE_LOGIN NUMBER(15);
| insert_tax_data |
| |
| DESCRIPTION |
| This procedure takes the input parameters from ZX_EXTRACT_PKG |
| and builds dynamic SQL statement clauses based on the parameters, |
| supplies them as output parameters. |
| |
| SCOPE - Public |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 04-Mar-04 Ling Zhang Created |
| |
+===========================================================================*/
PROCEDURE insert_tax_data (
P_MRC_SOB_TYPE IN VARCHAR2,
P_TRL_GLOBAL_VARIABLES_REC IN OUT NOCOPY ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
) IS
BEGIN
g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_tax_data.BEGIN',
'ZX_AR_EXTRACT_PKG: insert_tax_data(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_tax_data.END',
'ZX_AR_EXTRACT_PKG: insert_tax_data(-)');
FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_tax_data- '|| g_error_buffer);
'ZX.TRL.ZX_EXTRACT_PKG.insert_tax_data',
g_error_buffer);
END insert_tax_data;
| the fetch and insert procedures. |
+===========================================================================*/
PROCEDURE build_sql(
P_MRC_SOB_TYPE IN VARCHAR2
) IS
L_WHERE_REPORTING_CONTEXT_TRXH varchar2(2500);
SELECT CHART_OF_ACCOUNTS_ID
INTO L_CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
'SELECT count(*) '||
' FROM AR_VAT_TAX_ALL_B VAT '||
' WHERE VAT.INTERIM_TAX_CCID IS NOT NULL '||
' AND ROWNUM = :1 '
|| L_WHERE_TAX_CLASS
|| L_WHERE_TAX_CODE
|| L_WHERE_TAX_CODE_VAT_TRX_TYPE
|| L_WHERE_TAX_CODE_TYPE
|| L_WHERE_GDF_AR_TAX_CODES_ATT1
|| L_WHERE_REPORTING_CONTEXT_VAT;
'SELECT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID ,
ZX_DET.DOC_SEQ_ID ,
ZX_DET.DOC_SEQ_NAME ,
ZX_DET.RECEIVABLES_TRX_TYPE_ID,
ZX_DET.LINE_CLASS,
ZX_DET.EVENT_CLASS_CODE ,
TO_CHAR(NULL) ,'|| --APPLIED_FROM_ENTITY_CODE
'TO_NUMBER(NULL) ,'||--APPLIED_FROM_TRX_ID
'TO_CHAR(NULL) ,'||--APPLIED_FROM_EVENT_CLASS_CODE
'TO_CHAR(NULL) ,'|| -- ADJUSTED_DOC_ENTITY_CODE --APPLIED_TO_ENTITY_CODE
'TO_NUMBER(NULL) ,'|| --ADJSUTED_DOC_ID --APPLIED_TO_TRX_ID
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_EVENT_CLASS_CODE --APPLIED_TO_EVENT_CLASS_CODE
'ZX_DET.ADJUSTED_DOC_NUMBER ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID,NULL,''TAX'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL),'|| --TAXABLE_LINE_SOURCE_TABLE
'TO_NUMBER(NULL),'|| --TAXABLE_ITEM_SOURCE_ID
'ZX_LINE.TAX_EXEMPTION_ID,
ZX_LINE.TAX_EXCEPTION_ID,
TO_NUMBER(NULL),'|| --TAXABLE_LINE_TOTAL_AMOUNT
'TO_NUMBER(NULL),'|| --ZX_LINE.TAX_LINE_ID
'SUM(ZX_LINE.TAX_AMT),
sum(ZX_LINE.TAX_AMT_FUNCL_CURR) ,
TO_NUMBER(NULL),'|| --ZX_LINE.TAX_LINE_NUMBER
'sum(ZX_LINE.TAXABLE_AMT),
sum(ZX_LINE.TAXABLE_AMT_FUNCL_CURR) ,
TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_ID
'TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_QUANTITY
'ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL),'||--RECONCILIATION_FLAG
'ZX_DET.INTERNAL_ORGANIZATION_ID ,'||--ORG_ID
'TO_NUMBER(NULL),'||--BR_REF_CUSTOMER_TRX_ID
-- 'TO_CHAR(NULL), --reverse_date
'TO_NUMBER(NULL),'|| --AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| --TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE ,
ZX_DET.TRX_SHIPPING_DATE ,
ZX_DET.TRX_COMMUNICATED_DATE ,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
ZX_DET.TRX_SIC_CODE,
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,'||
'to_char(NULL),'|| --ZX_DET.TRX_LINE_DESCRIPTION,
'TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_NUMBER,
'TO_CHAR(NULL),'|| --ZX_DET.TRX_LINE_TYPE,
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'','|| --EXTRACT_SOURCE_LEDGER
'--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL),'|| --RECEIPT_CLASS_ID
'ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL),'|| --ZX_DET.REF_DOC_LINE_ID,
'TO_NUMBER(NULL),'|| --ZX_DET.REF_DOC_LINE_QUANTITY,
'ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
sum(ZX_DET.ASSESSABLE_VALUE),
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
ZX_LINE.EXCEPTION_RATE,
ZX_LINE.EXEMPT_RATE_MODIFIER,
sum(ZX_LINE.ORIG_TAX_AMT),
sum(ZX_LINE.ORIG_TAX_AMT_TAX_CURR) ,
sum(ZX_LINE.ORIG_TAXABLE_AMT),
sum(ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR),
TO_CHAR(NULL),'|| -- RECOVERY_RATE_CODE,
'sum(ZX_LINE.TAX_AMT_TAX_CURR) ,
ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
ZX_LINE.TAX_CURRENCY_CODE,
ZX_LINE.TAX_DATE,
ZX_LINE.TAX_DETERMINE_DATE,
ZX_DET.TAX_INVOICE_DATE,
ZX_DET.TAX_INVOICE_NUMBER,
ZX_LINE.TAX_JURISDICTION_CODE,
ZX_LINE.TAX_REGIME_CODE,
ZX_LINE.TAX_STATUS_CODE,
ZX_LINE.TAX_STATUS_ID,
ZX_LINE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
ZX_LINE.HQ_ESTB_REG_NUMBER ,
zx_line.tax_registration_number,
ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
ZX_LINE.REGISTRATION_PARTY_TYPE,
ZX_LINE.HISTORICAL_FLAG,
ZX_LINE.MRC_TAX_LINE_FLAG,
To_CHAR(NULL) POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
sum(ZX_DET.LINE_AMT),
ZX_LINE.TAX_PROVIDER_ID,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.TRX_ID ,
ZX_DET.ENTITY_CODE,
TO_DATE(NULL),
ZX_DET.TRX_LINE_GL_DATE,
to_number(NULL), --ZX_DET.UNIT_PRICE,
TO_NUMBER(NULL),
ZX_LINE.OFFSET_TAX_RATE_CODE,
ZX_LINE.OFFSET_FLAG';
'SELECT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID ,
ZX_DET.DOC_SEQ_ID ,
ZX_DET.DOC_SEQ_NAME ,
ZX_DET.RECEIVABLES_TRX_TYPE_ID,
ZX_DET.LINE_CLASS,
ZX_DET.EVENT_CLASS_CODE ,
TO_CHAR(NULL) ,'|| --APPLIED_FROM_ENTITY_CODE
'TO_NUMBER(NULL) ,'||--APPLIED_FROM_TRX_ID
'TO_CHAR(NULL) ,'||--APPLIED_FROM_EVENT_CLASS_CODE
'TO_CHAR(NULL) ,'|| -- ADJUSTED_DOC_ENTITY_CODE --APPLIED_TO_ENTITY_CODE
'TO_NUMBER(NULL) ,'|| --ADJSUTED_DOC_ID --APPLIED_TO_TRX_ID
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_EVENT_CLASS_CODE --APPLIED_TO_EVENT_CLASS_CODE
'ZX_DET.ADJUSTED_DOC_NUMBER ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID,NULL,''TAX'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL),'|| --TAXABLE_LINE_SOURCE_TABLE
'TO_NUMBER(NULL),'|| --TAXABLE_ITEM_SOURCE_ID
'ZX_LINE.TAX_EXEMPTION_ID,
ZX_LINE.TAX_EXCEPTION_ID,
TO_NUMBER(NULL),'|| --TAXABLE_LINE_TOTAL_AMOUNT
'TO_NUMBER(NULL),'|| --ZX_LINE.TAX_LINE_ID
'ZX_LINE.TAX_AMT,
ZX_LINE.TAX_AMT_FUNCL_CURR ,
TO_NUMBER(NULL),'|| --ZX_LINE.TAX_LINE_NUMBER
'ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_ID
'TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_QUANTITY
'ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL),'||--RECONCILIATION_FLAG
'ZX_DET.INTERNAL_ORGANIZATION_ID ,'||--ORG_ID
'TO_NUMBER(NULL),'||--BR_REF_CUSTOMER_TRX_ID
-- 'TO_CHAR(NULL), --reverse_date
'TO_NUMBER(NULL),'|| --AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| --TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE ,
ZX_DET.TRX_SHIPPING_DATE ,
ZX_DET.TRX_COMMUNICATED_DATE ,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
ZX_DET.TRX_SIC_CODE,
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
ZX_DET.TRX_LINE_DESCRIPTION,'||
'TO_NUMBER(NULL),'|| --ZX_LINE.TRX_LINE_NUMBER,
'TO_CHAR(NULL),'|| --ZX_DET.TRX_LINE_TYPE,
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'','|| --EXTRACT_SOURCE_LEDGER
'--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL),'|| --RECEIPT_CLASS_ID
'ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL),'|| --ZX_DET.REF_DOC_LINE_ID,
'TO_NUMBER(NULL),'|| --ZX_DET.REF_DOC_LINE_QUANTITY,
'ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
ZX_LINE.EXCEPTION_RATE,
ZX_LINE.EXEMPT_RATE_MODIFIER,
ZX_LINE.ORIG_TAX_AMT,
ZX_LINE.ORIG_TAX_AMT_TAX_CURR ,
ZX_LINE.ORIG_TAXABLE_AMT,
ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL),'|| -- RECOVERY_RATE_CODE,
'ZX_LINE.TAX_AMT_TAX_CURR ,
ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
ZX_LINE.TAX_CURRENCY_CODE,
ZX_LINE.TAX_DATE,
ZX_LINE.TAX_DETERMINE_DATE,
ZX_DET.TAX_INVOICE_DATE,
ZX_DET.TAX_INVOICE_NUMBER,
ZX_LINE.TAX_JURISDICTION_CODE,
ZX_LINE.TAX_REGIME_CODE,
ZX_LINE.TAX_STATUS_CODE,
ZX_LINE.TAX_STATUS_ID,
ZX_LINE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
ZX_LINE.HQ_ESTB_REG_NUMBER ,
zx_line.tax_registration_number,
ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
ZX_LINE.REGISTRATION_PARTY_TYPE,
ZX_LINE.HISTORICAL_FLAG,
ZX_LINE.MRC_TAX_LINE_FLAG,
DECODE(DIST.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.LINE_AMT,
ZX_LINE.TAX_PROVIDER_ID,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
DIST.ACCOUNT_CLASS,
TO_CHAR(NULL),
ZX_DET.TRX_ID ,
ZX_DET.ENTITY_CODE,
DIST.GL_POSTED_DATE,
DIST.GL_DATE,
ZX_DET.UNIT_PRICE,
TO_NUMBER(NULL),
ZX_LINE.OFFSET_TAX_RATE_CODE,
ZX_LINE.OFFSET_FLAG';
'SELECT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID ,
ZX_DET.DOC_SEQ_ID ,
ZX_DET.DOC_SEQ_NAME ,
ZX_DET.RECEIVABLES_TRX_TYPE_ID,
ZX_DET.LINE_CLASS,
ZX_DET.EVENT_CLASS_CODE ,
TO_CHAR(NULL) ,'|| --APPLIED_FROM_ENTITY_CODE
'TO_NUMBER(NULL) ,'||--APPLIED_FROM_TRX_ID
'TO_CHAR(NULL) ,'||--APPLIED_FROM_EVENT_CLASS_CODE
'TO_CHAR(NULL) ,'|| -- ADJUSTED_DOC_ENTITY_CODE --APPLIED_TO_ENTITY_CODE
'TO_NUMBER(NULL) ,'|| --ADJSUTED_DOC_ID --APPLIED_TO_TRX_ID
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_EVENT_CLASS_CODE --APPLIED_TO_EVENT_CLASS_CODE
'ZX_DET.ADJUSTED_DOC_NUMBER ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID,NULL,''TAX'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL), --TAXABLE_LINE_SOURCE_TABLE,
TO_NUMBER(NULL), --TAXABLE_ITEM_SOURCE_ID,
ZX_LINE.TAX_EXEMPTION_ID ,
ZX_LINE.TAX_EXCEPTION_ID ,
TO_NUMBER(NULL), --TAXABLE_LINE_TOTAL_AMOUNT ,
ZX_LINE.TAX_LINE_ID,
ZX_LINE.TAX_AMT,
ZX_LINE.TAX_AMT_FUNCL_CURR ,
ZX_LINE.TAX_LINE_NUMBER,
ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
ZX_LINE.TRX_LINE_ID,
ZX_LINE.TRX_LINE_QUANTITY ,
ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL) ,'||--RECONCILIATION_FLAG
'ZX_DET.INTERNAL_ORGANIZATION_ID ,'||--ORG_ID
'TO_NUMBER(NULL),'||--BR_REF_CUSTOMER_TRX_ID
-- 'TO_CHAR(NULL), --reverse_date
'TO_NUMBER(NULL) ,'||--AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| --TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
--ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE ,
ZX_DET.TRX_SHIPPING_DATE ,
ZX_DET.TRX_COMMUNICATED_DATE ,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
ZX_DET.TRX_SIC_CODE,
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
ZX_DET.TRX_LINE_DESCRIPTION,
ZX_LINE.TRX_LINE_NUMBER,
ZX_DET.TRX_LINE_TYPE,
ZX_LINE.ATTRIBUTE_CATEGORY,
ZX_LINE.ATTRIBUTE1,
ZX_LINE.ATTRIBUTE2,
ZX_LINE.ATTRIBUTE3,
ZX_LINE.ATTRIBUTE4,
ZX_LINE.ATTRIBUTE5,
ZX_LINE.ATTRIBUTE6,
ZX_LINE.ATTRIBUTE7,
ZX_LINE.ATTRIBUTE8,
ZX_LINE.ATTRIBUTE9,
ZX_LINE.ATTRIBUTE10,
ZX_LINE.ATTRIBUTE11,
ZX_LINE.ATTRIBUTE12,
ZX_LINE.ATTRIBUTE13,
ZX_LINE.ATTRIBUTE14,
ZX_LINE.ATTRIBUTE15,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'','|| --EXTRACT_SOURCE_LEDGER
'--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL),'|| --RECEIPT_CLASS_ID
'ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
ZX_LINE.EXCEPTION_RATE,
ZX_LINE.EXEMPT_RATE_MODIFIER,
ZX_LINE.ORIG_TAX_AMT,
ZX_LINE.ORIG_TAX_AMT_TAX_CURR ,
ZX_LINE.ORIG_TAXABLE_AMT,
ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL),'|| -- RECOVERY_RATE_CODE,
'ZX_LINE.TAX_AMT_TAX_CURR ,
ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
ZX_LINE.TAX_CURRENCY_CODE,
ZX_LINE.TAX_DATE,
ZX_LINE.TAX_DETERMINE_DATE,
ZX_DET.TAX_INVOICE_DATE,
ZX_DET.TAX_INVOICE_NUMBER,
ZX_LINE.TAX_JURISDICTION_CODE,
ZX_LINE.TAX_REGIME_CODE,
ZX_LINE.TAX_STATUS_CODE,
ZX_LINE.TAX_STATUS_ID,
ZX_LINE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
ZX_LINE.HQ_ESTB_REG_NUMBER ,
zx_line.tax_registration_number,
ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
ZX_LINE.REGISTRATION_PARTY_TYPE,
ZX_LINE.HISTORICAL_FLAG,
ZX_LINE.MRC_TAX_LINE_FLAG,
TO_CHAR(NULL)POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.LINE_AMT,
ZX_LINE.TAX_PROVIDER_ID,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.ENTITY_CODE,
TO_DATE(NULL),
ZX_DET.TRX_LINE_GL_DATE,
ZX_DET.UNIT_PRICE,
TO_NUMBER(NULL),
ZX_LINE.OFFSET_TAX_RATE_CODE,
ZX_LINE.OFFSET_FLAG';
'SELECT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID ,
ZX_DET.DOC_SEQ_ID ,
ZX_DET.DOC_SEQ_NAME ,
ZX_DET.RECEIVABLES_TRX_TYPE_ID,
ZX_DET.LINE_CLASS,
ZX_DET.EVENT_CLASS_CODE ,
TO_CHAR(NULL) ,'|| --APPLIED_FROM_ENTITY_CODE
'TO_NUMBER(NULL) ,'||--APPLIED_FROM_TRX_ID
'TO_CHAR(NULL) ,'||--APPLIED_FROM_EVENT_CLASS_CODE
'TO_CHAR(NULL) ,'|| -- ADJUSTED_DOC_ENTITY_CODE --APPLIED_TO_ENTITY_CODE
'TO_NUMBER(NULL) ,'|| --ADJSUTED_DOC_ID --APPLIED_TO_TRX_ID
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_EVENT_CLASS_CODE --APPLIED_TO_EVENT_CLASS_CODE
'ZX_DET.ADJUSTED_DOC_NUMBER ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID,NULL,''TAX'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL), --TAXABLE_LINE_SOURCE_TABLE,
TO_NUMBER(NULL), --TAXABLE_ITEM_SOURCE_ID,
ZX_LINE.TAX_EXEMPTION_ID ,
ZX_LINE.TAX_EXCEPTION_ID ,
TO_NUMBER(NULL), --TAXABLE_LINE_TOTAL_AMOUNT ,
ZX_LINE.TAX_LINE_ID,
ZX_LINE.TAX_AMT,
ZX_LINE.TAX_AMT_FUNCL_CURR ,
ZX_LINE.TAX_LINE_NUMBER,
ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
ZX_LINE.TRX_LINE_ID,
ZX_LINE.TRX_LINE_QUANTITY ,
ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL) ,'||--RECONCILIATION_FLAG
'ZX_DET.INTERNAL_ORGANIZATION_ID ,'||--ORG_ID
'TO_NUMBER(NULL),'||--BR_REF_CUSTOMER_TRX_ID
-- 'TO_CHAR(NULL), --reverse_date
'TO_NUMBER(NULL) ,'||--AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| --TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
--ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE ,
ZX_DET.TRX_SHIPPING_DATE ,
ZX_DET.TRX_COMMUNICATED_DATE ,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
ZX_DET.TRX_SIC_CODE,
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
ZX_DET.TRX_LINE_DESCRIPTION,
ZX_LINE.TRX_LINE_NUMBER,
ZX_DET.TRX_LINE_TYPE,
ZX_LINE.ATTRIBUTE_CATEGORY,
ZX_LINE.ATTRIBUTE1,
ZX_LINE.ATTRIBUTE2,
ZX_LINE.ATTRIBUTE3,
ZX_LINE.ATTRIBUTE4,
ZX_LINE.ATTRIBUTE5,
ZX_LINE.ATTRIBUTE6,
ZX_LINE.ATTRIBUTE7,
ZX_LINE.ATTRIBUTE8,
ZX_LINE.ATTRIBUTE9,
ZX_LINE.ATTRIBUTE10,
ZX_LINE.ATTRIBUTE11,
ZX_LINE.ATTRIBUTE12,
ZX_LINE.ATTRIBUTE13,
ZX_LINE.ATTRIBUTE14,
ZX_LINE.ATTRIBUTE15,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'','|| --EXTRACT_SOURCE_LEDGER
'--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL),'|| --RECEIPT_CLASS_ID
'ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
ZX_LINE.EXCEPTION_RATE,
ZX_LINE.EXEMPT_RATE_MODIFIER,
ZX_LINE.ORIG_TAX_AMT,
ZX_LINE.ORIG_TAX_AMT_TAX_CURR ,
ZX_LINE.ORIG_TAXABLE_AMT,
ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL),'|| -- RECOVERY_RATE_CODE,
'ZX_LINE.TAX_AMT_TAX_CURR ,
ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
ZX_LINE.TAX_CURRENCY_CODE,
ZX_LINE.TAX_DATE,
ZX_LINE.TAX_DETERMINE_DATE,
ZX_DET.TAX_INVOICE_DATE,
ZX_DET.TAX_INVOICE_NUMBER,
ZX_LINE.TAX_JURISDICTION_CODE,
ZX_LINE.TAX_REGIME_CODE,
ZX_LINE.TAX_STATUS_CODE,
ZX_LINE.TAX_STATUS_ID,
ZX_LINE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
ZX_LINE.HQ_ESTB_REG_NUMBER ,
zx_line.tax_registration_number,
ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
ZX_LINE.REGISTRATION_PARTY_TYPE,
ZX_LINE.HISTORICAL_FLAG,
ZX_LINE.MRC_TAX_LINE_FLAG,
DECODE(DIST.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.LINE_AMT,
ZX_LINE.TAX_PROVIDER_ID,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
DIST.ACCOUNT_CLASS,
TO_CHAR(NULL),
TO_CHAR(NULL) ,
ZX_DET.ENTITY_CODE,
DIST.GL_POSTED_DATE,
DIST.GL_DATE,
ZX_DET.UNIT_PRICE,
TO_NUMBER(NULL),
ZX_LINE.OFFSET_TAX_RATE_CODE,
ZX_LINE.OFFSET_FLAG';
'SELECT DISTINCT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID ,
ZX_DET.DOC_SEQ_ID ,
ZX_DET.DOC_SEQ_NAME ,
ZX_DET.RECEIVABLES_TRX_TYPE_ID,
ZX_DET.LINE_CLASS,
ZX_DET.EVENT_CLASS_CODE ,
TO_CHAR(NULL) ,'|| --APPLIED_FROM_ENTITY_CODE
'TO_NUMBER(NULL) ,'||--APPLIED_FROM_TRX_ID
'TO_CHAR(NULL) ,'||--APPLIED_FROM_EVENT_CLASS_CODE
'TO_CHAR(NULL) ,'|| -- ADJUSTED_DOC_ENTITY_CODE --APPLIED_TO_ENTITY_CODE
'TO_NUMBER(NULL) ,'|| --ADJSUTED_DOC_ID --APPLIED_TO_TRX_ID
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_EVENT_CLASS_CODE --APPLIED_TO_EVENT_CLASS_CODE
'ZX_DET.ADJUSTED_DOC_NUMBER ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_ACCOUNTS.INTERIM_TAX_CCID,NULL,''TAX'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL), --TAXABLE_LINE_SOURCE_TABLE,
TO_NUMBER(NULL), --TAXABLE_ITEM_SOURCE_ID,
ZX_LINE.TAX_EXEMPTION_ID ,
ZX_LINE.TAX_EXCEPTION_ID ,
TO_NUMBER(NULL), --TAXABLE_LINE_TOTAL_AMOUNT ,
ZX_LINE.TAX_LINE_ID,
DIST.AMOUNT, -- ZX_LINE.TAX_AMT,
DIST.ACCTD_AMOUNT, --ZX_LINE.TAX_AMT_FUNCL_CURR ,
ZX_LINE.TAX_LINE_NUMBER,
ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
ZX_LINE.TRX_LINE_ID,
ZX_LINE.TRX_LINE_QUANTITY ,
ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL) ,'||--RECONCILIATION_FLAG
'ZX_DET.INTERNAL_ORGANIZATION_ID ,'||--ORG_ID
'TO_NUMBER(NULL),'||--BR_REF_CUSTOMER_TRX_ID
--'TO_CHAR(NULL), --reverse_date
'TO_NUMBER(NULL) ,'||--AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| --TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
--ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE ,
ZX_DET.TRX_SHIPPING_DATE ,
ZX_DET.TRX_COMMUNICATED_DATE ,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
ZX_DET.TRX_SIC_CODE,
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
ZX_DET.TRX_LINE_DESCRIPTION,
ZX_LINE.TRX_LINE_NUMBER,
ZX_DET.TRX_LINE_TYPE,
ZX_LINE.ATTRIBUTE_CATEGORY,
ZX_LINE.ATTRIBUTE1,
ZX_LINE.ATTRIBUTE2,
ZX_LINE.ATTRIBUTE3,
ZX_LINE.ATTRIBUTE4,
ZX_LINE.ATTRIBUTE5,
ZX_LINE.ATTRIBUTE6,
ZX_LINE.ATTRIBUTE7,
ZX_LINE.ATTRIBUTE8,
ZX_LINE.ATTRIBUTE9,
ZX_LINE.ATTRIBUTE10,
ZX_LINE.ATTRIBUTE11,
ZX_LINE.ATTRIBUTE12,
ZX_LINE.ATTRIBUTE13,
ZX_LINE.ATTRIBUTE14,
ZX_LINE.ATTRIBUTE15,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'','|| --EXTRACT_SOURCE_LEDGER
'--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL) ,'|| --RECEIPT_CLASS_ID
'ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
ZX_LINE.EXCEPTION_RATE,
ZX_LINE.EXEMPT_RATE_MODIFIER,
ZX_LINE.ORIG_TAX_AMT,
ZX_LINE.ORIG_TAX_AMT_TAX_CURR ,
ZX_LINE.ORIG_TAXABLE_AMT,
ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL) ,'|| -- RECOVERY_RATE_CODE,
'ZX_LINE.TAX_AMT_TAX_CURR ,
ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
ZX_LINE.TAX_CURRENCY_CODE,
ZX_LINE.TAX_DATE,
ZX_LINE.TAX_DETERMINE_DATE,
ZX_DET.TAX_INVOICE_DATE,
ZX_DET.TAX_INVOICE_NUMBER,
ZX_LINE.TAX_JURISDICTION_CODE,
ZX_LINE.TAX_REGIME_CODE,
ZX_LINE.TAX_STATUS_CODE,
ZX_LINE.TAX_STATUS_ID,
ZX_LINE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID ,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID ,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
ZX_LINE.HQ_ESTB_REG_NUMBER ,
zx_line.tax_registration_number,
ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
ZX_LINE.REGISTRATION_PARTY_TYPE,
ZX_LINE.HISTORICAL_FLAG,
ZX_LINE.MRC_TAX_LINE_FLAG,
DECODE(DIST.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
ZX_LINE.PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
ZX_TAX.DEF_PLACE_OF_SUPPLY_TYPE_CODE,
ZX_DET.LINE_AMT,
ZX_LINE.TAX_PROVIDER_ID,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
DIST.ACCOUNT_CLASS,
DIST.LATEST_REC_FLAG,
DIST.CUST_TRX_LINE_GL_DIST_ID ,
ZX_DET.ENTITY_CODE,
DIST.GL_POSTED_DATE,
DIST.GL_DATE,
ZX_DET.UNIT_PRICE,
TO_NUMBER(NULL),
ZX_LINE.OFFSET_TAX_RATE_CODE,
ZX_LINE.OFFSET_FLAG';
'SELECT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID, '||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,
TO_NUMBER(NULL), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
' ''APP'',
APP.RECEIVABLE_APPLICATION_ID, '||
'''APP'','||
'TO_CHAR(NULL),'|| --ZX_DET.ADJUSTED_DOC_ENTITY_CODE, --INV
'TO_NUMBER(NULL) ,'|| --ZX_DET.ADJUSTED_DOC_TRX_ID--Applied_to_trx_id--adjusted_doc_id in eTax
'TO_CHAR(NULL),'|| --ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE -- INV
'ARCS.RECEIPT_NUMBER,'|| --ZX_DET.ADJUSTED_DOC_NUMBER --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''UNEDISC_NON_REC_TAX'',''NON-RECOVERABLE'',
''EDISC_NON_REC_TAX'',''NON-RECOVERABLE'',NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL),'||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0)-nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||-- Total Amount
'TO_NUMBER(NULL), ' || -- tax_line_id
'sum(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'sum(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)) ,'||
'TO_NUMBER(NULL), '||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'||
'sum(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 17
'TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'', ''Y'',''N''), ' ||
'ZX_DET.ORG_ID,
TO_NUMBER(NULL), ' ||
-- 'ARCS.REVERSAL_DATE, --Check this column
' APP.AMOUNT_APPLIED,
APP.TAX_APPLIED,
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
TO_CHAR(NULL), --ZX_DET.TRX_DESCRIPTION,
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
TO_DATE(NULL), --ZX_DET.TRX_DUE_DATE,
TO_DATE(NULL), --ZX_DET.TRX_SHIPPING_DATE,
TO_DATE(NULL), --ZX_DET.TRX_COMMUNICATED_DATE,
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.WAYBILL_NUMBER,
TO_CHAR(NULL),'|| --ZX_DET.BATCH_SOURCE_NAME,
'TO_CHAR(NULL),'|| -- TRX_TYPE_DESCRIPTION
-- 'ZX_RATE.RATE_TYPE_CODE,
'ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
NVL(ZX_DET.APPLICATION_ID,222),
TO_CHAR(NULL), --ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL), --ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL), --ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL), --ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL), --ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_LINE_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_TRX_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL), --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL), --ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL), --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL), --ZX_DET.DOCUMENT_SUB_TYPE,
TO_NUMBER(NULL), --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL), --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL), --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL), --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL), --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL), --ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_NUMBER(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_SITE_USE_ID, --ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_SITE_USE_ID, --ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_TO_CUSTOMER_ID, --ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_TO_CUSTOMER_ID, --ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER
'TO_CHAR(NULL), --ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL), --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(APP.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| -- POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'TO_NUMBER(NULL), -- ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL), --ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), --ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), --ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), --ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARDTAX.ACCTD_AMOUNT_CR),
sum(ARDTAX.ACCTD_AMOUNT_DR),
APP.STATUS,
TO_CHAR(NULL),
TO_CHAR(NULL),
ARD.LINE_ID ,
TO_CHAR(NULL), --ZX_DET.ENTITY_CODE,
APP.GL_POSTED_DATE,
APP.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID, '||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,
TO_NUMBER(NULL), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
' ''APP'',
APP.RECEIVABLE_APPLICATION_ID, '||
'''APP'','||
'ZX_DET.ADJUSTED_DOC_ENTITY_CODE,'|| --INV
'ZX_DET.ADJUSTED_DOC_TRX_ID,'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE,'|| -- INV
'ZX_DET.ADJUSTED_DOC_NUMBER, --Applied_doc_number
ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''UNEDISC_NON_REC_TAX'',''NON-RECOVERABLE'',
''EDISC_NON_REC_TAX'',''NON-RECOVERABLE'',NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL),'||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0)-nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||-- Total Amount
'TO_NUMBER(NULL), ' || -- tax_line_id
'sum(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'sum(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)) ,'||
'TO_NUMBER(NULL), '||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'||
'sum(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 17
'TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'', ''Y'',''N''), ' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,
TO_NUMBER(NULL), ' ||
-- 'ARCS.REVERSAL_DATE, --Check this column
' APP.AMOUNT_APPLIED,
APP.TAX_APPLIED,
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,'||
'TO_CHAR(NULL),'|| -- TRX_TYPE_DESCRIPTION
-- 'ZX_RATE.RATE_TYPE_CODE,
'ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
to_number(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_NUMBER(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,'||
'ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(APP.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| -- POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARDTAX.ACCTD_AMOUNT_CR),
sum(ARDTAX.ACCTD_AMOUNT_DR),
APP.STATUS,
TO_CHAR(NULL),
TO_CHAR(NULL),
ARD.LINE_ID ,
ZX_DET.ENTITY_CODE,
APP.GL_POSTED_DATE,
APP.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ARCS.SET_OF_BOOKS_ID,
ZX_DET.CUSTOMER_TRX_ID, '||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,
TO_NUMBER(NULL), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
' ''APP'',
APP.RECEIVABLE_APPLICATION_ID, '||
' ''APP'','||
'TO_CHAR(NULL),'|| --ZX_DET.ADJUSTED_DOC_ENTITY_CODE, --INV
'TO_NUMBER(NULL) ,'|| --ZX_DET.ADJUSTED_DOC_TRX_ID--Applied_to_trx_id--adjusted_doc_id in eTax
'TO_CHAR(NULL),'|| --ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE -- INV
'ARCS.RECEIPT_NUMBER,'|| --ZX_DET.ADJUSTED_DOC_NUMBER --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''UNEDISC_NON_REC_TAX'',''NON-RECOVERABLE'',
''EDISC_NON_REC_TAX'',''NON-RECOVERABLE'',NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'|| -- Total Amount
'TO_NUMBER(NULL),
(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),
TO_NUMBER(NULL), '||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 20
'ARD.SOURCE_ID,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N''),' ||
'ZX_DET.ORG_ID,
TO_NUMBER(NULL), ' ||
-- 'ARCS.REVERSAL_DATE,
'APP.AMOUNT_APPLIED,
APP.TAX_APPLIED,
TO_NUMBER(NULL), --ZX_RATE.PERCENTAGE_RATE,
TO_CHAR(NULL), --ZX_RATE.TAX_RATE_CODE,
TO_CHAR(NULL), --ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
TO_CHAR(NULL), --ZX_TAX.TAX_TYPE_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
TO_CHAR(NULL), --ZX_DET.TRX_DESCRIPTION,
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
TO_DATE(NULL), --ZX_DET.TRX_DUE_DATE,
TO_DATE(NULL), --ZX_DET.TRX_SHIPPING_DATE,
TO_DATE(NULL), --ZX_DET.TRX_COMMUNICATED_DATE,
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.WAYBILL_NUMBER,
BAT.NAME,
TO_CHAR(NULL),
-- ZX_RATE.RATE_TYPE_CODE,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
NVL(ZX_DET.APPLICATION_ID,222),
TO_CHAR(NULL), --ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL), --ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL), --ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL), --ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL), --ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_LINE_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_TRX_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL), --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL), --ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL), --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL), --ZX_DET.DOCUMENT_SUB_TYPE,
TO_NUMBER(NULL), --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL), --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL), --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL), --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL), --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL), --ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_RATE.TAX_JURISDICTION_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_REGIME_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
TO_CHAR(NULL), --ZX_RATE.TAX,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_SITE_USE_ID, --ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_SITE_USE_ID, --ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_TO_CUSTOMER_ID, --ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_TO_CUSTOMER_ID, --ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'TO_NUMBER(NULL), -- ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL), --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(APP.POSTING_CONTROL_ID,-3, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'TO_NUMBER(NULL), -- ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL), --ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), --ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), --ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), --ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
APP.STATUS,
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID ,
TO_CHAR(NULL), --zx_det.entity_code,
APP.GL_POSTED_DATE,
APP.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID, '||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,
TO_NUMBER(NULL), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
'DECODE(ARD.SOURCE_TYPE,''EDISC'',''EDISC'',''UNEDISC'',
''UNEDISC'',''REC'',''APP''), '||
' ''APP'',
APP.RECEIVABLE_APPLICATION_ID, '||
' ''APP'','||
'ZX_DET.ADJUSTED_DOC_ENTITY_CODE,'|| --INV
'ZX_DET.ADJUSTED_DOC_TRX_ID,'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE,'|| -- INV
'ZX_DET.ADJUSTED_DOC_NUMBER,'|| --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''UNEDISC_NON_REC_TAX'',''NON-RECOVERABLE'',
''EDISC_NON_REC_TAX'',''NON-RECOVERABLE'',NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'|| -- Total Amount
'TO_NUMBER(NULL),
(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),
TO_NUMBER(NULL), '||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 20
'TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N''),' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,
TO_NUMBER(NULL), ' ||
-- 'ARCS.REVERSAL_DATE,
'APP.AMOUNT_APPLIED,
APP.TAX_APPLIED,
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
TO_CHAR(NULL),
-- ZX_RATE.RATE_TYPE_CODE,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(APP.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
APP.STATUS,
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID ,
zx_det.entity_code,
APP.GL_POSTED_DATE,
APP.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ADJ.SET_OF_BOOKS_ID,
ADJ.ADJUSTMENT_ID, '||
'ADJ.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL), '|| -- Trx Type ID
'''ADJ'','||
'''ADJ'',
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ZX_DET.ADJUSTED_DOC_ENTITY_CODE,'|| --INV
'ZX_DET.ADJUSTED_DOC_TRX_ID,'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE,'|| -- INV
'ZX_DET.ADJUSTED_DOC_NUMBER,'|| --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''ADJ_NON_REC_TAX'',''NON-RECOVERABLE'',
''FINCHRG_NON_REC_TAX'',''NON-RECOVERABLE'',NULL),'|| --REG.TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARDTAX.SOURCE_TABLE,
ARDTAX.SOURCE_ID,
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0)-nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'|| --Total Amount
'TO_NUMBER(NULL),'||
'sum(nvl(ARDTAX.AMOUNT_CR,0)- nvl(ARDTAX.AMOUNT_DR,0)),'||
'sum(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),
TO_NUMBER(NULL),'||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'||
'sum(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 21
'TO_NUMBER(NULL), '||
'TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),'||
'TO_CHAR(NULL), ' ||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N'')
' || ' , ' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,'|| -- Org_id
'TO_NUMBER(NULL), ' ||
-- 'TO_DATE(NULL),'|| --reversal_date
'TO_NUMBER(NULL),'|| -- AMOUNT_APPLIED
'TO_NUMBER(NULL),'|| -- TAX_APPLIED
'ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ADJ.ADJUSTMENT_NUMBER,
ZX_DET.TRX_DESCRIPTION,'|| --COMMENTS,
'ADJ.DOC_SEQUENCE_VALUE,
ADJ.APPLY_DATE,
TO_DATE(NULL), -- Deposite date
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),'|| -- TRX_SIC_CODE
'ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.TRX_NUMBER, --ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE,
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG,
'DECODE(ADJ.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
sum(ZX_DET.LINE_AMT),
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
sum(ARDTAX.ACCTD_AMOUNT_CR),
sum(ARDTAX.ACCTD_AMOUNT_DR),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.TRX_ID ,
ZX_DET.ENTITY_CODE,
ADJ.GL_POSTED_DATE,
ADJ.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ADJ.SET_OF_BOOKS_ID,
ADJ.ADJUSTMENT_ID, '||
'ADJ.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL), '||
'''ADJ'','||
' ''ADJ'',
TO_CHAR(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
TO_CHAR(NULL),'|| --INV
'TO_NUMBER(NULL),'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'TO_CHAR(NULL),'|| -- INV
'ZX_DET.TRX_NUMBER,'|| --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''ADJ_NON_REC_TAX'',''NON-RECOVERABLE'',
''FINCHRG_NON_REC_TAX'',''NON-RECOVERABLE'',NULL),'|| -- REG. TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL), ' ||
'sum((nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0))),'||--Total Amount
'TO_NUMBER(NULL),
SUM((nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0))),'||
'sum((nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0))),'||
' TO_NUMBER(NULL), '||
'sum((nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0))),'||
'sum((nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0))),'|| -- ar matrix ++ 24
-- 'to_number(NULL),
'ard.line_id,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N''), ' ||
'ZX_DET.ORG_ID,'|| -- Org_id
'TO_NUMBER(NULL), ' ||
-- 'TO_DATE(NULL),
'TO_NUMBER(NULL), --Applied_amount
TO_NUMBER(NULL), --tax_amount
TO_NUMBER(NULL),
TO_CHAR(NULL),
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
to_char(NULL), --ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ADJ.ADJUSTMENT_NUMBER,
TO_CHAR(NULL), --ZX_DET.TRX_DESCRIPTION,
ADJ.DOC_SEQUENCE_VALUE,
ADJ.APPLY_DATE,
TO_DATE(NULL), --Deposit Date
TO_DATE(NULL), --ZX_DET.TRX_DUE_DATE,
TO_DATE(NULL), --ZX_DET.TRX_SHIPPING_DATE,
TO_DATE(NULL), --ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.INVOICE_CURRENCY_CODE ,
TO_CHAR(NULL), --ZX_DET.CURRENCY_CONVERSION_TYPE,
TO_DATE(NULL), --ZX_DET.CURRENCY_CONVERSION_DATE,
TO_NUMBER(NULL), --ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.WAYBILL_NUMBER,'||
'BAT.NAME,
TYPES.DESCRIPTION,
to_char(null),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
NVL(ZX_DET.APPLICATION_ID,222),
TO_CHAR(NULL), --ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL), --ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL), --ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL), --ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL), --ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_LINE_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_TRX_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL), --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL), --ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL), --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL), --ZX_DET.DOCUMENT_SUB_TYPE,
TO_NUMBER(NULL), --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL), --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL), --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL), --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL), --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL), --ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), -- ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_SITE_USE_ID, --ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_SITE_USE_ID, --ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_TO_CUSTOMER_ID, --ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_TO_CUSTOMER_ID, --ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'TO_NUMBER(NULL), --ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL), --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE,
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG,
'DECODE(ADJ.POSTING_CONTROL_ID,-3, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'TO_NUMBER(NULL), --ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL), --ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), --ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), --ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), --ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
SUM(ARDTAX.ACCTD_AMOUNT_CR),
sum(ARDTAX.ACCTD_AMOUNT_DR),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.CUSTOMER_TRX_ID, --ARDTAX.LINE_ID,
TO_CHAR(NULL), --ZX_DET.ENTITY_CODE,
ADJ.GL_POSTED_DATE,
ADJ.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ADJ.SET_OF_BOOKS_ID,
ADJ.ADJUSTMENT_ID, '||
'ADJ.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL), '||
'''ADJ'','||
' ''ADJ'',
TO_CHAR(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
TO_CHAR(NULL),'|| --INV
'TO_NUMBER(NULL),'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'TO_CHAR(NULL),'|| -- INV
'ZX_DET.TRX_NUMBER,'|| --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''ADJ_NON_REC_TAX'',''NON-RECOVERABLE'',
''FINCHRG_NON_REC_TAX'',''NON-RECOVERABLE'',NULL),'|| -- REG. TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||--Total Amount
'TO_NUMBER(NULL),
(nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),'||
' TO_NUMBER(NULL), '||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 24
'ARDTAX.LINE_ID,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N''), ' ||
'ZX_DET.ORG_ID,'|| -- Org_id
'TO_NUMBER(NULL), ' ||
-- 'TO_DATE(NULL),
'TO_NUMBER(NULL), --Applied_amount
TO_NUMBER(NULL), --tax_amount
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), --ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
TO_CHAR(NULL), --ZX_TAX.TAX_TYPE_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_RATE_NAME,
ADJ.ADJUSTMENT_NUMBER,
TO_CHAR(NULL), --ZX_DET.TRX_DESCRIPTION,
ADJ.DOC_SEQUENCE_VALUE,
ADJ.APPLY_DATE,
TO_DATE(NULL), --Deposit Date
TO_DATE(NULL), --ZX_DET.TRX_DUE_DATE,
TO_DATE(NULL), --ZX_DET.TRX_SHIPPING_DATE,
TO_DATE(NULL), --ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.INVOICE_CURRENCY_CODE ,
TO_CHAR(NULL), --ZX_DET.CURRENCY_CONVERSION_TYPE,
TO_DATE(NULL), --ZX_DET.CURRENCY_CONVERSION_DATE,
TO_NUMBER(NULL), --ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.WAYBILL_NUMBER,'||
'BAT.NAME,
TYPES.DESCRIPTION,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
NVL(ZX_DET.APPLICATION_ID,222),
TO_CHAR(NULL), --ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL), --ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL), --ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL), --ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL), --ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_LINE_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.APPLIED_TO_TRX_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL), --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
TO_DATE(NULL), --ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL), --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL), --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), --ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL), --ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL), --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL), --ZX_DET.DOCUMENT_SUB_TYPE,
TO_NUMBER(NULL), --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL), --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL), --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL), --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL), --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL), --ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), -- ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
TO_CHAR(NULL), --ZX_RATE.TAX_JURISDICTION_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_REGIME_CODE,
TO_CHAR(NULL), --ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
TO_CHAR(NULL), --ZX_RATE.TAX,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
TO_NUMBER(NULL), --ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_SITE_USE_ID, --ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_SITE_USE_ID, --ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_TO_CUSTOMER_ID, --ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_TO_CUSTOMER_ID, --ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'TO_NUMBER(NULL), --ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), --ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL), --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE,
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG,
'DECODE(ADJ.POSTING_CONTROL_ID,-3, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'TO_NUMBER(NULL), --ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL), --ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), --ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), --ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), --ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID,
TO_CHAR(NULL), --ZX_DET.ENTITY_CODE,
ADJ.GL_POSTED_DATE,
ADJ.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ADJ.SET_OF_BOOKS_ID,
ADJ.ADJUSTMENT_ID, '||
'ADJ.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL), '||
'''ADJ'','||
' ''ADJ'',
TO_CHAR(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
ZX_DET.ADJUSTED_DOC_ENTITY_CODE,'|| --INV
'ZX_DET.ADJUSTED_DOC_TRX_ID,'|| -- Applied_to_trx_id -- adjusted_doc_id in eTax
'ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE,'|| -- INV
'ZX_DET.ADJUSTED_DOC_NUMBER,'|| --Applied_doc_number
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM'',
''ADJ_NON_REC_TAX'',''NON-RECOVERABLE'',
''FINCHRG_NON_REC_TAX'',''NON-RECOVERABLE'',NULL),'|| -- REG. TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||--Total Amount
'TO_NUMBER(NULL),
(nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),'||
' TO_NUMBER(NULL), '||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 24
'ARD.LINE_ID,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL), '||
'DECODE(ARDTAX.SOURCE_TYPE_SECONDARY,''RECONCILE'',''Y'','||
' ''ASSIGNMENT_RECONCILE'',''Y'',''N''), ' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,'|| -- Org_id
'TO_NUMBER(NULL), ' ||
-- 'TO_DATE(NULL),
'TO_NUMBER(NULL), --Applied_amount
TO_NUMBER(NULL), --tax_amount
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ADJ.ADJUSTMENT_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ADJ.DOC_SEQUENCE_VALUE,
ADJ.APPLY_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,'||
'ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), -- ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE,
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG,
'DECODE(ADJ.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID,
ZX_DET.ENTITY_CODE,
ADJ.GL_POSTED_DATE,
ADJ.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID,'||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL),'||
'''MISC_CASH_RECEIPT'','||
'''MISC_CASH_RECEIPT'',
TO_CHAR(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'ARDTAX.TAX_CODE_ID,'||
'''TAX'','|| -- REGISTER TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0) ,'|| -- Total Amount
'TO_NUMBER(NULL),
nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0),'||
'nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0) ,'||
' TO_NUMBER(NULL), '||
'nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0) ,'||
'nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0),'|| -- ar matrix ++ 25
'ARD.LINE_ID,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),'||
'''N'', ' ||
'DIST.ORG_ID,
TO_NUMBER(NULL), ' ||
--'ARCS.REVERSAL_DATE,
'TO_NUMBER(NULL), --amount_applied
TO_NUMBER(NULL), --tax_applied
ZX_RATE.PERCENTAGE_RATE, --ZX_TAX.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
TO_CHAR(NULL),
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
TO_DATE(NULL), ' ||
'TO_DATE(NULL),
TO_DATE(NULL),
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARCS.TYPE,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
ARD.SOURCE_TYPE,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
222 ,--ZX_DET.APPLICATION_ID,
TO_CHAR(NULL) ,--ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL) ,--ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL) ,-- ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--TO_CHAR(NULL) ,--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--TO_CHAR(NULL) ,--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL) ,--ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL) ,-- ZX_DET.PRECISION,
ARM.RECEIPT_CLASS_ID ,-- RECEIPT_CLASS_ID, --Check this
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_TRX_LINE_ID
TO_CHAR(NULL) ,--ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL) ,--ZX_DET.APPLIED_TO_EVENT_CLASS_CODE
TO_CHAR(NULL), -- ZX_DET.APPLIED_TO_TRX_NUMBER
TO_NUMBER(NULL), -- ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL) , --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL) ,--ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL) , --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL) , --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), -- ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), -- ZX_DET.ADJUSTED_DOC_APPLICATION_ID
TO_DATE(NULL), -- ZX_DET.ADJUSTED_DOC_DATE,
--TO_CHAR(NULL) ,--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL) ,-- ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL) , --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL) , --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL) ,--ZX_DET.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL) , --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL) , --ZX_DET.DOCUMENT_SUB_TYPE,
TO_CHAR(NULL), --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL) , --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL) , --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL) , --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL) , --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL) ,-- ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL) , --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL) ,--ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL) , --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), -- ZX_DET.EXCEPTION_RATE,
TO_NUMBER(NULL) ,--ZX_DET.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL) , --TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_LINE.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'TO_NUMBER(NULL), -- ZX_DET.OWN_HQ_PARTY_SITE_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL) , --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(DIST.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_FLAG
'TO_NUMBER(NULL) ,-- ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL) ,--ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), -- ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), -- ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), -- ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
-- sum(ARDTAX.ACCTD_AMOUNT_CR),
-- sum(ARDTAX.ACCTD_AMOUNT_DR),
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARCS.CASH_RECEIPT_ID ,
''RECEIPTS'', --ZX_DET.ENTITY_CODE,
DIST.GL_POSTED_DATE,
DIST.GL_DATE,
TO_NUMBER(NULL),
ARCS.REMITTANCE_BANK_ACCOUNT_ID ,
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID,'||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL),'||
'''MISC_CASH_RECEIPT'','||
'''MISC_CASH_RECEIPT'',
TO_CHAR(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'ARDTAX.TAX_CODE_ID,'||
'''TAX'','|| -- REGISTER TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0) ,'|| -- Total Amount
'TO_NUMBER(NULL),
nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0),'||
'nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0) ,'||
' TO_NUMBER(NULL), '||
'nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0) ,'||
'nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0),'|| -- ar matrix ++ 25
'ARD.LINE_ID,
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),'||
'''N'', ' ||
'DIST.ORG_ID,
TO_NUMBER(NULL), ' ||
--'ARCS.REVERSAL_DATE,
'TO_NUMBER(NULL), --amount_applied
TO_NUMBER(NULL), --tax_applied
ZX_RATE.PERCENTAGE_RATE, --ZX_TAX.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
TO_CHAR(NULL),
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
TO_DATE(NULL), ' ||
'TO_DATE(NULL),
TO_DATE(NULL),
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARCS.TYPE,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
ARD.SOURCE_TYPE,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
222 ,--ZX_DET.APPLICATION_ID,
TO_CHAR(NULL) ,--ZX_DET.DOC_EVENT_STATUS,
TO_CHAR(NULL) ,--ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL) ,-- ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--TO_CHAR(NULL) ,--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--TO_CHAR(NULL) ,--ZX_DET.LEDGER_NAME,
TO_NUMBER(NULL) ,--ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
TO_NUMBER(NULL) ,-- ZX_DET.PRECISION,
ARM.RECEIPT_CLASS_ID ,-- RECEIPT_CLASS_ID, --Check this
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_APPLICATION_ID,
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_TRX_LINE_ID
TO_CHAR(NULL) ,--ZX_DET.APPLIED_TO_ENTITY_CODE,
TO_NUMBER(NULL) ,--ZX_DET.APPLIED_TO_TRX_ID,
TO_CHAR(NULL) ,--ZX_DET.APPLIED_TO_EVENT_CLASS_CODE
TO_CHAR(NULL), -- ZX_DET.APPLIED_TO_TRX_NUMBER
TO_NUMBER(NULL), -- ZX_DET.RELATED_DOC_APPLICATION_ID,
TO_DATE(NULL) , --ZX_DET.RELATED_DOC_DATE,
TO_CHAR(NULL) ,--ZX_DET.RELATED_DOC_ENTITY_CODE,
TO_CHAR(NULL) , --ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
TO_CHAR(NULL) , --ZX_DET.RELATED_DOC_NUMBER,
TO_NUMBER(NULL), -- ZX_DET.RELATED_DOC_TRX_ID,
TO_NUMBER(NULL), -- ZX_DET.ADJUSTED_DOC_APPLICATION_ID
TO_DATE(NULL), -- ZX_DET.ADJUSTED_DOC_DATE,
--TO_CHAR(NULL) ,--ZX_DET.BANKING_TP_TAXPAYER_ID,
TO_NUMBER(NULL) ,-- ZX_DET.REF_DOC_APPLICATION_ID,
TO_CHAR(NULL) , --ZX_DET.REF_DOC_ENTITY_CODE,
TO_CHAR(NULL) , --ZX_DET.REF_DOC_EVENT_CLASS_CODE,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_LINE_ID,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_LINE_QUANTITY,
TO_NUMBER(NULL), -- ZX_DET.REF_DOC_TRX_ID,
TO_CHAR(NULL) ,--ZX_DET.DEF_REC_SETTLEMENT_OPTION_CODE,
TO_CHAR(NULL) , --ZX_DET.DEFAULT_TAXATION_COUNTRY,
TO_CHAR(NULL) , --ZX_DET.DOCUMENT_SUB_TYPE,
TO_CHAR(NULL) , --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL) , --ZX_DET.TRX_BUSINESS_CATEGORY,
TO_CHAR(NULL) , --ZX_DET.LINE_INTENDED_USE,
TO_CHAR(NULL) , --ZX_DET.PRODUCT_CATEGORY,
TO_CHAR(NULL) , --ZX_DET.PRODUCT_FISC_CLASSIFICATION,
TO_NUMBER(NULL) ,-- ZX_DET.SUPPLIER_EXCHANGE_RATE,
TO_DATE(NULL) , --ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
TO_CHAR(NULL) ,--ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
TO_CHAR(NULL) , --ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), -- ZX_DET.EXCEPTION_RATE,
TO_NUMBER(NULL) ,--ZX_DET.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL) , --TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_LINE.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'TO_NUMBER(NULL), -- ZX_DET.OWN_HQ_PARTY_SITE_PROF_ID,
TO_NUMBER(NULL), -- ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL) , --ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(DIST.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_FLAG
'TO_NUMBER(NULL) ,-- ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL) ,--ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL), -- ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL), -- ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), -- ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
ARCS.REVERSAL_DATE,
ARCS.STATUS,
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID ' ||
',''RECEIPTS'','||
'DIST.GL_POSTED_DATE,
DIST.GL_DATE,
TO_NUMBER(NULL),
ARCS.REMITTANCE_BANK_ACCOUNT_ID ,
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT '||
'ARCS.SET_OF_BOOKS_ID,
ARCS.CASH_RECEIPT_ID,'||
'ARCS.DOC_SEQUENCE_ID,
SEQ.NAME,'||
'TO_NUMBER(NULL),'||
'''MISC_CASH_RECEIPT'','||
'''MISC_CASH_RECEIPT'',
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
'ARDTAX.TAX_CODE_ID,'||
'''TAX'','|| -- REGISTER TYPE CODE
'TO_NUMBER(NULL),'||
'TO_CHAR(NULL),
ARD.SOURCE_TABLE,
ARD.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'TO_NUMBER(NULL), --Tax Line ID
(nvl(ARDTAX.AMOUNT_CR,0) - nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)),
TO_NUMBER(NULL), '|| --Tax Line Number
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 26
'TO_NUMBER(NULL),'|| -- Trx_line_id
'TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
''N'', ' || --Reconcialiation Flag
'DIST.ORG_ID,
TO_NUMBER(NULL), ' ||
'ARCS.REVERSAL_DATE,
TO_NUMBER(NULL), -- Amount_applied
TO_NUMBER(NULL), -- tax_Amount
TO_NUMBER(NULL), --ZX_TAX.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
-- ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ARCS.RECEIPT_NUMBER,
TO_CHAR(NULL),
ARCS.DOC_SEQUENCE_VALUE,
ARCS.RECEIPT_DATE,
ARCS.DEPOSIT_DATE,
TO_DATE(NULL), ' ||
'TO_DATE(NULL),
TO_DATE(NULL),
ARCS.CURRENCY_CODE,
ARCS.EXCHANGE_RATE_TYPE,
ARCS.EXCHANGE_DATE,
ARCS.EXCHANGE_RATE,'||
'TO_CHAR(NULL), --trx_sic_code
TO_CHAR(NULL), --FOB
TO_CHAR(NULL), --Waybill Number
TO_CHAR(NULL), --batch source name
TO_CHAR(NULL),'|| --trx_type_description
'TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL), --ZX_DET.APPLICATION_ID
TO_CHAR(NULL) ,-- ZX_DET.DOC_EVENT_STATUS
TO_CHAR(NULL) ,-- ZX_DET.APPLICATION_DOC_STATUS,
TO_NUMBER(NULL) ,--ZX_DET.ESTABLISHMENT_ID
''AR'', --EXTRACT_SOURCE_LEDGER
TO_CHAR(NULL) ,-- ZX_DET.FUNCTIONAL_CURRECNY_CODE
TO_CHAR(NULL) ,-- ZX_DET.LEDGER_NAME
TO_NUMBER(NULL) , --ZX_DET.MINIMUM_ACCOUNTABLE_UNIT
TO_NUMBER(NULL), --ZX_DET.PRECISION
TO_NUMBER(NULL), --RECEIPT_CLASS_ID
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_APPLICATION_ID
TO_DATE(NULL), -- ZX_DET.ADJUSTED_DOC_DATE
TO_CHAR(NULL), -- ZX_DET.ADJUSTED_DOC_ENTITY_CODE
TO_CHAR(NULL), -- ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE
TO_CHAR(NULL), -- ZX_DET.ADJUSTED_DOC_NUMBER
TO_NUMBER(NULL), --ZX_DET.ADJUSTED_DOC_TRX_ID
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_APPLICATION_ID
TO_DATE(NULL), -- ZX_DET.RELATED_DOC_DATE
TO_CHAR(NULL) ,-- ZX_DET.RELATED_DOC_ENTITY_CODE
TO_CHAR(NULL), -- ZX_DET.RELATED_DOC_EVENT_CLASS_CODE
TO_CHAR(NULL), -- ZX_DET.RELATED_DOC_NUMBER
TO_NUMBER(NULL), --ZX_DET.RELATED_DOC_TRX_ID
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_APPLICATION_ID
TO_NUMBER(NULL), --ZX_DET.APPLIED_TO_TRX_LINE_ID
TO_CHAR(NULL), -- ZX_DET.BANKING_TP_TAXPAYER_ID
TO_NUMBER(NULL), --ZX_DET.REF_DOC_APPLICATION_ID
TO_CHAR(NULL), -- ZX_DET.REF_DOC_ENTITY_CODE
TO_CHAR(NULL), -- ZX_DET.REF_DOC_EVENT_CLASS_CODE
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_ID
TO_NUMBER(NULL), --ZX_DET.REF_DOC_LINE_QUANTITY
TO_NUMBER(NULL) --ZX_DET.REF_DOC_TRX_ID
TO_CHAR(NULL) -- ZX_DET.DEF_REC_SETTLEMENT_OPTION_CODE
TO_CHAR(NULL) -- ZX_DET.DEFAULT_TAXATION_COUNTRY
TO_CHAR(NULL) -- ZX_DET.DOCUMENT_SUB_TYPE
TO_CHAR(NULL) , --ZX_DET.ASSESSABLE_VALUE,
TO_CHAR(NULL) -- ZX_DET.TRX_BUSINESS_CATEGORY
TO_CHAR(NULL) -- ZX_DET.LINE_INTENDED_USE
TO_CHAR(NULL) -- ZX_DET.PRODUCT_CATEGORY
TO_CHAR(NULL) -- ZX_DET.PRODUCT_FISC_CLASSIFICATION
TO_NUMBER(NULL) --ZX_DET.SUPPLIER_EXCHANGE_RATE
TO_DATE(NULL), -- ZX_DET.SUPPLIER_TAX_INVOICE_DATE
TO_CHAR(NULL), -- ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER
TO_CHAR(NULL), -- ZX_DET.USER_DEFINED_FISC_CLASS
TO_NUMBER(NULL), --ZX_DET.EXCEPTION_RATE
TO_CHAR(NULL), -- ZX_DET.EXEMPT_RATE_MODIFIER
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR
to_char(NULL), --ZX_RATE.RECOVERY_RATE_CODE
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE
TO_DATE(NULL), --ZX_LINE.TAX_DATE
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE
TO_DATE(NULL), --ZX_LINE.TAX_INVOICE_DATE
TO_CHAR(NULL), --ZX_LINE.TAX_INVOICE_NUMBER
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
ZX_RATE.TAX,
TO_NUMBER(NULL) , --ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID
TO_NUMBER(NULL) , --ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID
TO_NUMBER(NULL) ,--ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID
TO_NUMBER(NULL) ,--ZX_DET.SHIP_TO_SITE_TAX_PROF_ID
TO_NUMBER(NULL) , --ZX_DET.BILL_TO_PARTY_TAX_PROF_ID
TO_NUMBER(NULL) ,--ZX_DET.BILL_TO_SITE_TAX_PROF_ID
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER
' TO_NUMBER(NULL) ,--ZX_DET.OWN_HQ_PARTY_SITE_PROF_ID,
TO_NUMBER(NULL) , --ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
TO_CHAR(NULL) ,--ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'TO_CHAR(NULL) POSTED_FLAG,'|| -- POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_FLAG
' TO_NUMBER(NULL) . --ZX_DET.EVENT_CLASS_MAPPING_ID,
TO_CHAR(NULL) --ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
TO_NUMBER(NULL) . --ZX_DET.POA_LOCATION_ID,
TO_NUMBER(NULL) . --ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
TO_NUMBER(NULL), --ZX_DET.LINE_AMT,
TO_NUMBER(NULL) ';
'SELECT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID, '||
'ZX_DET.DOC_SEQ_ID,
ZX_DET.DOC_SEQ_NAME,
TO_NUMBER(NULL),
''BR'', '||
'''BR'', '||
' TO_CHAR(NULL),
TO_NUMBER(NULL), '||
' TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
' TO_CHAR(NULL),
TO_CHAR(NULL), '||
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM''
,NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL) ,'||
'TO_NUMBER(NULL), ' ||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0)-nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||-- Total Amount
'TO_NUMBER(NULL),
sum(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'sum(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)) ,'||
'TO_NUMBER(NULL), '||
'sum(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'||
'sum(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 27
'TO_NUMBER(NULL),
TO_NUMBER(NULL), '||
'TO_CHAR(NULL),'||
'TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
''N'', ' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,
TO_NUMBER(NULL), ' ||
--'TO_DATE(NULL),
'TO_NUMBER(NULL), --amount_applied
TO_NUMBER(NULL), --tax_applied
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
--ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), --Deposit Date
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME, '||
'ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
LINE.ATTRIBUTE_CATEGORY,
LINE.ATTRIBUTE1,
LINE.ATTRIBUTE2,
LINE.ATTRIBUTE3,
LINE.ATTRIBUTE4,
LINE.ATTRIBUTE5,
LINE.ATTRIBUTE6,
LINE.ATTRIBUTE7,
LINE.ATTRIBUTE8,
LINE.ATTRIBUTE9,
LINE.ATTRIBUTE10,
LINE.ATTRIBUTE11,
LINE.ATTRIBUTE12,
LINE.ATTRIBUTE13,
LINE.ATTRIBUTE14,
LINE.ATTRIBUTE15,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
to_number(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE,
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG,
'DECODE(TH.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG,
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
sum(ARDTAX.ACCTD_AMOUNT_CR),
sum(ARDTAX.ACCTD_AMOUNT_DR),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ZX_DET.TRX_ID ,
ZX_DET.ENTITY_CODE,
TH.GL_POSTED_DATE,
TH.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
'SELECT DISTINCT '||
'ZX_DET.LEDGER_ID,
ZX_DET.TRX_ID, '||
'ZX_DET.DOC_SEQ_ID,
ZX_DET.DOC_SEQ_NAME,
TO_NUMBER(NULL),
''BR'', '||
'''BR'', '||
' TO_CHAR(NULL),
TO_NUMBER(NULL), '||
' TO_CHAR(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),'||
' TO_CHAR(NULL),
TO_CHAR(NULL), '||
'ZX_DET.BATCH_SOURCE_ID,'||
'ARDTAX.TAX_CODE_ID,'||
'DECODE(ARDTAX.SOURCE_TYPE,''TAX'',''TAX'',''DEFERRED_TAX'',''INTERIM''
,NULL), ' || -- REG. TYPE CODE
'TO_NUMBER(NULL),
TO_CHAR(NULL),
ARDTAX.SOURCE_TABLE,
ARDTAX.SOURCE_ID,
TO_NUMBER(NULL),'||
'TO_NUMBER(NULL), ' ||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)) ,'|| -- Total Amount
'TO_NUMBER(NULL),
(nvl(ARDTAX.AMOUNT_CR,0)-nvl(ARDTAX.AMOUNT_DR,0)),'||
'(nvl(ARDTAX.ACCTD_AMOUNT_CR,0) - nvl(ARDTAX.ACCTD_AMOUNT_DR,0)) ,
TO_NUMBER(NULL), '||
'(nvl(ARDTAX.TAXABLE_ENTERED_CR,0) - nvl(ARDTAX.TAXABLE_ENTERED_DR,0)),'||
'(nvl(ARDTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(ARDTAX.TAXABLE_ACCOUNTED_DR,0)),'|| -- ar matrix ++ 30
'TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),'||
'''N'', ' ||
'ZX_DET.INTERNAL_ORGANIZATION_ID,'|| -- Org_id
'LINE.BR_REF_CUSTOMER_TRX_ID, ' || --add in BR_REF_CUSTOMER_TRX_ID
--'TO_DATE(NULL),
'TO_NUMBER(NULL), --amount_applied
TO_NUMBER(NULL), --tax_applied
ZX_RATE.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
--ZX_RATE.RATE_TYPE_CODE,
ZX_TAX.TAX_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_DATE,
TO_DATE(NULL), -- Deposit Date
ZX_DET.TRX_DUE_DATE,
ZX_DET.TRX_SHIPPING_DATE,
ZX_DET.TRX_COMMUNICATED_DATE,
ZX_DET.TRX_CURRENCY_CODE,
ZX_DET.CURRENCY_CONVERSION_TYPE,
ZX_DET.CURRENCY_CONVERSION_DATE,
ZX_DET.CURRENCY_CONVERSION_RATE,
TO_CHAR(NULL),
ZX_DET.FOB_POINT,
ZX_DET.TRX_WAYBILL_NUMBER,
ZX_DET.BATCH_SOURCE_NAME,
ZX_DET.TRX_TYPE_DESCRIPTION,
ZX_RATE.DESCRIPTION,
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
LINE.ATTRIBUTE_CATEGORY,
LINE.ATTRIBUTE1,
LINE.ATTRIBUTE2,
LINE.ATTRIBUTE3,
LINE.ATTRIBUTE4,
LINE.ATTRIBUTE5,
LINE.ATTRIBUTE6,
LINE.ATTRIBUTE7,
LINE.ATTRIBUTE8,
LINE.ATTRIBUTE9,
LINE.ATTRIBUTE10,
LINE.ATTRIBUTE11,
LINE.ATTRIBUTE12,
LINE.ATTRIBUTE13,
LINE.ATTRIBUTE14,
LINE.ATTRIBUTE15,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
''AR'', --EXTRACT_SOURCE_LEDGER,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL), --RECEIPT_CLASS_ID,
ZX_DET.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
ZX_DET.APPLIED_TO_ENTITY_CODE,
ZX_DET.APPLIED_TO_TRX_ID,
ZX_DET.APPLIED_TO_EVENT_CLASS_CODE,
ZX_DET.APPLIED_TO_TRX_NUMBER,
ZX_DET.RELATED_DOC_APPLICATION_ID,
ZX_DET.RELATED_DOC_DATE,
ZX_DET.RELATED_DOC_ENTITY_CODE,
ZX_DET.RELATED_DOC_EVENT_CLASS_CODE,
ZX_DET.RELATED_DOC_NUMBER,
ZX_DET.RELATED_DOC_TRX_ID,
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
--ZX_DET.BANKING_TP_TAXPAYER_ID,
ZX_DET.REF_DOC_APPLICATION_ID,
ZX_DET.REF_DOC_ENTITY_CODE,
ZX_DET.REF_DOC_EVENT_CLASS_CODE,
ZX_DET.REF_DOC_LINE_ID,
ZX_DET.REF_DOC_LINE_QUANTITY,
ZX_DET.REF_DOC_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
ZX_DET.ASSESSABLE_VALUE,
ZX_DET.TRX_BUSINESS_CATEGORY,
ZX_DET.LINE_INTENDED_USE,
ZX_DET.PRODUCT_CATEGORY,
ZX_DET.PRODUCT_FISC_CLASSIFICATION,
ZX_DET.SUPPLIER_EXCHANGE_RATE,
ZX_DET.SUPPLIER_TAX_INVOICE_DATE,
ZX_DET.SUPPLIER_TAX_INVOICE_NUMBER,
ZX_DET.USER_DEFINED_FISC_CLASS,
TO_NUMBER(NULL), --ZX_LINE.EXCEPTION_RATE,
TO_NUMBER(NULL), --ZX_LINE.EXEMPT_RATE_MODIFIER,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT,
TO_NUMBER(NULL), --ZX_LINE.ORIG_TAXABLE_AMT_TAX_CURR,
TO_CHAR(NULL), --ZX_RATE.RECOVERY_RATE_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_AMT_TAX_CURR,
TO_NUMBER(NULL), --ZX_LINE.TAX_APPORTIONMENT_LINE_NUMBER,
TO_CHAR(NULL), --ZX_LINE.TAX_CURRENCY_CODE,
TO_DATE(NULL), --ZX_LINE.TAX_DATE,
TO_DATE(NULL), --ZX_LINE.TAX_DETERMINE_DATE,
TO_DATE(NULL), --ZX_DET.TAX_INVOICE_DATE,
TO_CHAR(NULL), --ZX_DET.TAX_INVOICE_NUMBER,
ZX_RATE.TAX_JURISDICTION_CODE,
ZX_RATE.TAX_REGIME_CODE,
ZX_RATE.TAX_STATUS_CODE,
TO_NUMBER(NULL), --ZX_LINE.TAX_STATUS_ID,
ZX_RATE.TAX,
ZX_DET.SHIP_FROM_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_FROM_SITE_TAX_PROF_ID,
ZX_DET.SHIP_TO_PARTY_TAX_PROF_ID,
ZX_DET.SHIP_TO_SITE_TAX_PROF_ID,
ZX_DET.BILL_TO_PARTY_TAX_PROF_ID,
ZX_DET.BILL_TO_SITE_TAX_PROF_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_SITE_ID,
ZX_DET.SHIP_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.BILL_TO_CUST_ACCT_SITE_USE_ID,
ZX_DET.SHIP_THIRD_PTY_ACCT_ID,
ZX_DET.BILL_THIRD_PTY_ACCT_ID,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --zx_line.tax_registration_number
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_SITE_TAX_PROF_ID,
ZX_DET.OWN_HQ_PARTY_TAX_PROF_ID,
ZX_DET.PORT_OF_ENTRY_CODE,
TO_CHAR(NULL),'|| --ZX_LINE.REGISTRATION_PARTY_TYPE
'TO_CHAR(NULL),'|| --ZX_LINE.HISTORICAL_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.MRC_TAX_LINE_FLAG
'DECODE(TH.GL_POSTED_DATE, NULL, ''N'', ''Y'') POSTED_FLAG,'|| --POSTED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.REPORTING_ONLY_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_AMT_INCLUDED_FLAG
'TO_CHAR(NULL),'|| --ZX_LINE.TAX_ONLY_LINE_FLAG
'ZX_DET.EVENT_CLASS_MAPPING_ID,
ZX_DET.TRX_LEVEL_TYPE,
TO_CHAR(NULL), -- Place of supply type code
ZX_DET.POA_LOCATION_ID,
ZX_DET.POO_LOCATION_ID,
TO_CHAR(NULL), -- Default Place of supply type code
ZX_DET.LINE_AMT,
TO_NUMBER(NULL),
TO_DATE(NULL),
TO_CHAR(NULL),
ARDTAX.ACCTD_AMOUNT_CR,
ARDTAX.ACCTD_AMOUNT_DR,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
ARDTAX.LINE_ID ,
ZX_DET.ENTITY_CODE,
TH.GL_POSTED_DATE,
TH.GL_DATE,
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)';
g_last_updated_by := fnd_global.user_id;
g_last_update_login := fnd_global.login_id;
g_last_update_date := sysdate;
IF (i >= C_LINES_PER_INSERT) THEN
total_rec := total_rec + i;
insert_ar_trx_info;
insert_ar_trx_info;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_ar_trx_info',
'No Data found for the product AR (OR) Verify Parameters');
| insert_ar_trx_info |
| name changed from INSERT_AR_SUB_ITF |
| (rewrite from INSERT_SQL_AR) |
| DESCRIPTION |
| This procedure inserts AR data into ZX_AR_EXTRACT_PKG_SUB_ITF table |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 04-JAN-03 S. korrapati Created |
| 30-JUN-04 Amol Pai Added Insert into ar and common extension |
| tables (moved code from populate to build_sql) |
| |
+===========================================================================*/
PROCEDURE insert_ar_trx_info
IS
l_count number;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_ar_trx_info.BEGIN',
'ZX_AR_EXTRACT_PKG: insert_ar_trx_info(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_ar_trx_info',
'Rows fetched : '||to_char(GT_LEDGER_ID.COUNT));
INSERT INTO ZX_REP_TRX_DETAIL_T(
DETAIL_TAX_LINE_ID,
LEDGER_ID,
TRX_ID ,
DOC_SEQ_ID,
DOC_SEQ_NAME ,
TRX_TYPE_ID ,
TRX_LINE_CLASS,
EVENT_CLASS_CODE ,
APPLIED_FROM_ENTITY_CODE,
APPLIED_FROM_TRX_ID,
APPLIED_FROM_EVENT_CLASS_CODE,
ADJUSTED_DOC_ENTITY_CODE ,
ADJUSTED_DOC_TRX_ID ,
ADJUSTED_DOC_EVENT_CLASS_CODE ,
ADJUSTED_DOC_NUMBER ,
TRX_BATCH_SOURCE_ID,
TAX_RATE_ID ,
TAX_RATE_REGISTER_TYPE_CODE,
PRODUCT_ID,
PRODUCT_DESCRIPTION,
TAXABLE_LINE_SOURCE_TABLE ,
TAXABLE_ITEM_SOURCE_ID,
TAX_EXEMPTION_ID ,
TAX_EXCEPTION_ID ,
TAXABLE_LINE_TOTAL_AMOUNT,
TAX_LINE_ID ,
TAX_AMT ,
TAX_AMT_FUNCL_CURR ,
TAX_LINE_NUMBER ,
TAXABLE_AMT ,
TAXABLE_AMT_FUNCL_CURR ,
TRX_LINE_ID ,
TRX_LINE_QUANTITY ,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE ,
TAX_EXCEPTION_REASON_CODE ,
UOM_CODE,
RECONCILIATION_FLAG ,
INTERNAL_ORGANIZATION_ID,
BR_REF_CUSTOMER_TRX_ID,
-- REVERSE_FLAG,
AMOUNT_APPLIED,
TAX_APPLIED,
TAX_RATE,
TAX_RATE_CODE,
TAX_RATE_VAT_TRX_TYPE_CODE,
TAX_TYPE_CODE,
TAX_RATE_CODE_NAME ,
TRX_NUMBER ,
TRX_DESCRIPTION ,
DOC_SEQ_VALUE,
TRX_DATE,
DEPOSIT_DATE,
TRX_DUE_DATE ,
TRX_SHIPPING_DATE ,
TRX_COMMUNICATED_DATE ,
TRX_CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
TRX_SIC_CODE,
FOB_POINT,
TRX_WAYBILL_NUMBER ,
TRX_BATCH_SOURCE_NAME,
TRX_TYPE_DESCRIPTION,
TRX_TYPE_MNG,
TAX_RATE_CODE_DESCRIPTION ,
TRX_LINE_DESCRIPTION,
TRX_LINE_NUMBER,
TRX_LINE_TYPE,
TAX_LINE_USER_CATEGORY ,
TAX_LINE_USER_ATTRIBUTE1 ,
TAX_LINE_USER_ATTRIBUTE2 ,
TAX_LINE_USER_ATTRIBUTE3 ,
TAX_LINE_USER_ATTRIBUTE4 ,
TAX_LINE_USER_ATTRIBUTE5 ,
TAX_LINE_USER_ATTRIBUTE6 ,
TAX_LINE_USER_ATTRIBUTE7 ,
TAX_LINE_USER_ATTRIBUTE8 ,
TAX_LINE_USER_ATTRIBUTE9,
TAX_LINE_USER_ATTRIBUTE10 ,
TAX_LINE_USER_ATTRIBUTE11,
TAX_LINE_USER_ATTRIBUTE12 ,
TAX_LINE_USER_ATTRIBUTE13 ,
TAX_LINE_USER_ATTRIBUTE14 ,
TAX_LINE_USER_ATTRIBUTE15 ,
APPLICATION_ID,
DOC_EVENT_STATUS,
APPLICATION_DOC_STATUS,
ESTABLISHMENT_ID,
EXTRACT_SOURCE_LEDGER ,
--FUNCTIONAL_CURRENCY_CODE,
--LEDGER_NAME,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
RECEIPT_CLASS_ID ,
APPLIED_TO_APPLICATION_ID ,
APPLIED_TO_TRX_LINE_ID ,
APPLIED_TO_ENTITY_CODE ,
APPLIED_TO_TRX_ID,
APPLIED_TO_EVENT_CLASS_CODE ,
APPLIED_TO_TRX_NUMBER ,
RELATED_DOC_APPLICATION_ID ,
RELATED_DOC_DATE ,
RELATED_DOC_ENTITY_CODE ,
RELATED_DOC_EVENT_CLASS_CODE ,
RELATED_DOC_NUMBER ,
RELATED_DOC_TRX_ID ,
ADJUSTED_DOC_APPLICATION_ID ,
ADJUSTED_DOC_DATE ,
REF_DOC_APPLICATION_ID ,
REF_DOC_ENTITY_CODE ,
REF_DOC_EVENT_CLASS_CODE ,
REF_DOC_LINE_ID ,
REF_DOC_LINE_QUANTITY ,
REF_DOC_TRX_ID ,
DEF_REC_SETTLEMENT_OPTION_CODE,
DEFAULT_TAXATION_COUNTRY,
DOCUMENT_SUB_TYPE,
ASSESSABLE_VALUE,
TRX_BUSINESS_CATEGORY,
LINE_INTENDED_USE,
PRODUCT_CATEGORY ,
PRODUCT_FISC_CLASSIFICATION,
SUPPLIER_EXCHANGE_RATE,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_TAX_INVOICE_NUMBER,
USER_DEFINED_FISC_CLASS,
EXCEPTION_RATE,
EXEMPT_RATE_MODIFIER,
ORIG_TAX_AMT,
ORIG_TAX_AMT_TAX_CURR ,
ORIG_TAXABLE_AMT,
ORIG_TAXABLE_AMT_TAX_CURR,
recovery_rate_code,
TAX_AMT_TAX_CURR ,
TAX_APPORTIONMENT_LINE_NUMBER,
TAX_CURRENCY_CODE,
TAX_DATE,
TAX_DETERMINE_DATE,
TAX_INVOICE_DATE,
TAX_INVOICE_NUMBER ,
TAX_JURISDICTION_CODE,
-- TAX_RATE_VAT_TRX_TYPE_DESC ,
TAX_REGIME_CODE,
TAX_STATUS_CODE,
TAX_STATUS_ID,
TAX,
SHIP_FROM_PARTY_TAX_PROF_ID,
SHIP_FROM_SITE_TAX_PROF_ID,
SHIP_TO_PARTY_TAX_PROF_ID ,
SHIP_TO_SITE_TAX_PROF_ID ,
BILL_TO_PARTY_TAX_PROF_ID,
BILL_TO_SITE_TAX_PROF_ID,
SHIPPING_TP_ADDRESS_ID, --SHIP_THIRD_PTY_ACCT_SITE_ID
BILLING_TP_ADDRESS_ID, --bill_third_pty_acct_site_id
SHIPPING_TP_SITE_ID, --ship_to_cust_acct_site_use_id
BILLING_TP_SITE_ID, --bill_to_cust_acct_site_use_id
SHIPPING_TRADING_PARTNER_ID, --ship_third_pty_acct_id
BILLING_TRADING_PARTNER_ID, -- bill_third_pty_acct_id
HQ_ESTB_REG_NUMBER ,
TAX_LINE_REGISTRATION_NUMBER,
LEGAL_ENTITY_TAX_REG_NUMBER,
OWN_HQ_PARTY_SITE_PROF_ID ,
OWN_HQ_PARTY_TAX_PROF_ID ,
PORT_OF_ENTRY_CODE,
REGISTRATION_PARTY_TYPE,
HISTORICAL_FLAG,
MRC_TAX_LINE_FLAG,
POSTED_FLAG,
REPORTING_ONLY_FLAG,
TAX_AMT_INCLUDED_FLAG,
TAX_ONLY_FLAG,
EVENT_CLASS_MAPPING_ID,
TRX_LEVEL_TYPE,
PLACE_OF_SUPPLY_TYPE_CODE,
POA_LOCATION_ID,
POO_LOCATION_ID,
DEF_PLACE_OF_SUPPLY_TYPE_CODE,
TRX_LINE_AMT,
TAX_PROVIDER_ID,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
AR_CASH_RECEIPT_REVERSE_DATE,
AR_CASH_RECEIPT_REVERSE_STATUS,
ACCTD_AMOUNT_CR,
ACCTD_AMOUNT_DR,
REC_APPLICATION_STATUS,
ACCOUNT_CLASS,
LATEST_REC_FLAG,
ACTG_SOURCE_ID,
ENTITY_CODE,
POSTED_DATE,
GL_DATE,
UNIT_PRICE,
BANK_ACCOUNT_ID,
REP_CONTEXT_ID,
OFFSET_TAX_RATE_CODE,
OFFSET_FLAG)
VALUES(
ZX_REP_TRX_DETAIL_T_S.NEXTVAL,
GT_LEDGER_ID(i),
GT_TRX_ID(i),
GT_DOC_SEQ_ID(i),
GT_DOC_SEQ_NAME(i),
GT_TRX_TYPE_ID(i),
GT_LINE_CLASS(i),
GT_EVENT_CLASS_CODE(i),
GT_APPLIED_FROM_ENTITY_CODE(i),
GT_APPLIED_FROM_TRX_ID(i),
GT_APPLIED_FROM_EVENT_CLS_CD(i),
GT_ADJUSTED_DOC_ENTITY_CODE(i),
GT_ADJUSTED_DOC_TRX_ID(i),
GT_ADJUSTED_DOC_EVENT_CLS_CD(i),
GT_ADJUSTED_DOC_NUMBER(i),
GT_BATCH_SOURCE_ID(i),
GT_TAX_RATE_ID(i),
-- g_register_type,
GT_TAX_RATE_REG_TYPE_CODE(i),
GT_PRODUCT_ID(i),
GT_PRODUCT_DESCRIPTION(i),
GT_TAXABLE_LINE_SOURCELE(i),
GT_TAXABLE_ITEM_SOURCE_ID(i),
GT_TAX_EXEMPTION_ID(i),
GT_TAX_EXCEPTION_ID(i),
GT_TAXABLE_LINE_TOTAL_AMOUNT(i),
GT_TAX_LINE_ID(i),
GT_TAX_AMT(i),
GT_TAX_AMT_FUNCL_CURR(i),
GT_TAX_LINE_NUMBER(i),
GT_TAXABLE_AMT(i),
GT_TAXABLE_AMT_FUNCL_CURR(i),
GT_TRX_LINE_ID(i),
GT_TRX_LINE_QUANTITY(i),
GT_EXEMPT_CERTIFICATE_NUMBER(i),
GT_EXEMPT_REASON_CODE(i),
GT_TAX_EXCEPTION_REASON_CODE(i),
GT_UOM_CODE(i),
GT_RECONCILIATION_FLAG(i),
GT_INTERNAL_ORGANIZATION_ID(i),
GT_BR_REF_CUSTOMER_TRX_ID(i),
-- GT_REVERSE_FLAG(i), -- REVERSAL_DATE Check this column
GT_AMOUNT_APPLIED(i),
GT_TAX_APPLIED(i),
GT_TAX_RATE(i),
GT_TAX_RATE_CODE(i),
GT_TAX_RATE_VAT_TRX_TYPE_CODE(i),
GT_TAX_TYPE_CODE(i),
GT_TAX_RATE_CODE_NAME(i), -- Do we need to rename tax_rate_name?
GT_TRX_NUMBER(i),
GT_TRX_DESCRIPTION(i),
GT_DOC_SEQ_VALUE(i),
GT_TRX_DATE(i),
GT_DEPOSIT_DATE(i),
GT_TRX_DUE_DATE(i),
GT_TRX_SHIPPING_DATE(i),
GT_TRX_COMMUNICATED_DATE(i),
GT_TRX_CURRENCY_CODE(i),
GT_CURRENCY_CONVERSION_TYPE(i),
GT_CURRENCY_CONVERSION_DATE(i),
GT_CURRENCY_CONVERSION_RATE(i),
GT_TRX_SIC_CODE(i),
GT_FOB_POINT(i),
GT_TRX_WAYBILL_NUMBER(i),
GT_TRX_BATCH_SOURCE_NAME(i),
GT_TRX_TYPE_DESCRIPTION(i),
GT_TRX_TYPE_DESCRIPTION(i),
GT_TAX_RATE_CODE_DESCRIPTION(i), --populate
GT_TRX_LINE_DESCRIPTION(i),
GT_TRX_LINE_NUMBER(i),
GT_TRX_LINE_TYPE(i),
GT_TAX_LINE_USER_CATEGORY(i),
GT_TAX_LINE_USER_ATTRIBUTE1(i),
GT_TAX_LINE_USER_ATTRIBUTE2(i),
GT_TAX_LINE_USER_ATTRIBUTE3(i),
GT_TAX_LINE_USER_ATTRIBUTE4(i),
GT_TAX_LINE_USER_ATTRIBUTE5(i),
GT_TAX_LINE_USER_ATTRIBUTE6(i),
GT_TAX_LINE_USER_ATTRIBUTE7(i),
GT_TAX_LINE_USER_ATTRIBUTE8(i),
GT_TAX_LINE_USER_ATTRIBUTE9(i),
GT_TAX_LINE_USER_ATTRIBUTE10(i),
GT_TAX_LINE_USER_ATTRIBUTE11(i),
GT_TAX_LINE_USER_ATTRIBUTE12(i),
GT_TAX_LINE_USER_ATTRIBUTE13(i),
GT_TAX_LINE_USER_ATTRIBUTE14(i),
GT_TAX_LINE_USER_ATTRIBUTE15(i),
GT_APPLICATION_ID(i),
GT_DOC_EVENT_STATUS(i),
GT_APPLICATION_DOC_STATUS(i),
GT_ESTABLISHMENT_ID(i),
GT_EXTRACT_SOURCE_LEDGER(i),
-- GT_FUNCTIONAL_CURRECNY_CODE(i),
-- GT_LEDGER_NAME(i),
GT_MINIMUM_ACCOUNTABLE_UNIT(i),
GT_PRECISION(i),
GT_RECEIPT_CLASS_ID(i),
GT_APPLIED_TO_APPLICATION_ID(i),
GT_APPLIED_TO_TRX_LINE_ID(i),
GT_APPLIED_TO_ENTITY_CODE(i),
GT_APPLIED_TO_TRX_ID(i),
GT_APPLIED_TO_EVENT_CLS_CODE(i),
GT_APPLIED_TO_TRX_NUMBER(i),
GT_RELATED_DOC_APPLICATION_ID(i),
GT_RELATED_DOC_DATE(i),
GT_RELATED_DOC_ENTITY_CODE(i),
GT_RELATED_DOC_EVENT_CLS_CODE(i),
GT_RELATED_DOC_NUMBER(i),
GT_RELATED_DOC_TRX_ID(i),
GT_ADJUSTED_DOC_APPL_ID(i),
GT_ADJUSTED_DOC_DATE(i),
GT_REF_DOC_APPLICATION_ID(i),
GT_REF_DOC_ENTITY_CODE(i),
GT_REF_DOC_EVENT_CLASS_CODE(i),
GT_REF_DOC_LINE_ID(i),
GT_REF_DOC_LINE_QUANTITY(i),
GT_REF_DOC_TRX_ID(i),
GT_DEF_REC_SETTLEMENT_OPT_CD(i),
GT_DEFAULT_TAXATION_COUNTRY(i),
GT_DOCUMENT_SUB_TYPE(i),
GT_ASSESSABLE_VALUE(i),
GT_TRX_BUSINESS_CATEGORY(i),
GT_LINE_INTENDED_USE(i),
GT_PRODUCT_CATEGORY(i),
GT_PROD_FISC_CLASSIFICATION(i),
GT_SUPPLIER_EXCHANGE_RATE(i),
GT_SUPPLIER_TAX_INVOICE_DATE(i),
GT_SUPPLIER_TAX_INVOICE_NUM(i),
GT_USER_DEFINED_FISC_CLASS(i),
GT_EXCEPTION_RATE(i),
GT_EXEMPT_RATE_MODIFIER(i),
GT_ORIG_TAX_AMT(i),
GT_ORIG_TAX_AMT_TAX_CURR(i),
GT_ORIG_TAXABLE_AMT(i),
GT_ORIG_TAXABLE_AMT_TAX_CURR(i),
GT_RECOVERY_RATE_CODE(i),
GT_TAX_AMT_TAX_CURR(i),
GT_TAX_APPORTIONMENT_LINE_NUM(i),
GT_TAX_CURRENCY_CODE(i),
GT_TAX_DATE(i),
GT_TAX_DETERMINE_DATE(i),
GT_TAX_INVOICE_DATE(i),
GT_TAX_INVOICE_NUMBER(i),
GT_TAX_JURISDICTION_CODE(i),
GT_TAX_REGIME_CODE(i),
GT_TAX_STATUS_CODE(i),
GT_TAX_STATUS_ID(i),
GT_TAX(i),
GT_SHIP_FROM_PTY_TAX_PROF_ID(i),
GT_SHIP_FROM_SITE_TAX_PROF_ID(i),
GT_SHIP_TO_PARTY_TAX_PROF_ID(i),
GT_SHIP_TO_SITE_TAX_PROF_ID(i),
GT_BILL_TO_PTY_TAX_PROF_ID(i),
GT_BILL_TO_SITE_TAX_PROF_ID(i),
GT_SHIPPING_TP_ADDRESS_ID(i), --SHIP_THIRD_PTY_ACCT_SITE_ID
GT_BILLING_TP_ADDRESS_ID(i),
GT_SHIPPING_TP_SITE_ID(i),
GT_BILLING_TP_SITE_ID(i),
GT_SHIPPING_TP_ID(i),
GT_BILLING_TRADING_PARTNER_ID(i),
GT_HQ_ESTB_REG_NUMBER(i),
gt_tax_line_registration_num(i),
GT_LEGAL_ENTITY_TAX_REG_NUM(i),
GT_OWN_HQ_PARTY_SITE_PROF_ID(i),
GT_OWN_HQ_PARTY_TAX_PROF_ID(i),
GT_PORT_OF_ENTRY_CODE(i),
GT_REGISTRATION_PARTY_TYPE(i),
GT_HISTORICAL_FLAG(i),
GT_MRC_TAX_LINE_FLAG(i),
GT_POSTED_FLAG(i),
GT_REPORTING_ONLY_FLAG(i),
GT_TAX_AMT_INCLUDED_FLAG(i),
GT_TAX_ONLY_LINE_FLAG(i),
GT_EVENT_CLASS_MAPPING_ID(i),
GT_TRX_LEVEL_TYPE(i),
GT_PLACE_OF_SUPPLY_TYPE_CODE(i),
GT_POA_LOCATION_ID(i),
GT_POO_LOCATION_ID(i),
GT_DEF_PLACE_OF_SUP_TYPE_CODE(i),
GT_LINE_AMT(i),
GT_TAX_PROVIDER_ID(i),
G_CREATED_BY ,
G_CREATION_DATE ,
G_LAST_UPDATED_BY,
G_LAST_UPDATE_DATE,
G_LAST_UPDATE_LOGIN,
-- 1,
-- SYSDATE,
-- 1,
-- SYSDATE,
-- 1,
g_request_id,
GT_AR_CASH_RCPT_RVRS_DATE(i),
GT_AR_CASH_RCPT_RVRS_STATUS(i),
GT_ACCTD_AMOUNT_CR(i),
GT_ACCTD_AMOUNT_DR(i),
GT_REC_APPLICATION_STATUS(i),
GT_ACCOUNT_CLASS(i),
GT_LATEST_REC_FLAG(i),
GT_ACTG_SOURCE_ID(i),
GT_ENTITY_CODE(i),
GT_POSTED_DATE(i),
GT_GL_DATE(i),
GT_UNIT_PRICE(i),
GT_BANKING_ACCOUNT_ID(i),
G_REP_CONTEXT_ID,
GT_OFFSET_TAX_RATE(i),
GT_OFFSET_FLAG(i));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_ar_trx_info',
'Number of AR rows successfully inserted = '||TO_CHAR(NVL(GT_LEDGER_ID.COUNT, 0)));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_EXTRACT_PKG.insert_ar_trx_info.END',
'ZX_AR_EXTRACT_PKG: insert_ar_trx_info(-)');
FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','insert_ar_trx_info- '|| g_error_buffer);
'ZX.TRL.ZX_EXTRACT_PKG.insert_ar_trx_info',
g_error_buffer);
END insert_ar_trx_info;
GT_LEDGER_ID.DELETE;
GT_TRX_ID.DELETE;
GT_DOC_SEQ_ID.DELETE;
GT_DOC_SEQ_NAME.DELETE;
GT_TRX_TYPE_ID.DELETE;
GT_LINE_CLASS.DELETE;
GT_EVENT_CLASS_CODE.DELETE;
GT_APPLIED_FROM_ENTITY_CODE.DELETE;
GT_APPLIED_FROM_TRX_ID.DELETE;
GT_APPLIED_FROM_EVENT_CLS_CD.DELETE;
GT_ADJUSTED_DOC_ENTITY_CODE.DELETE;
GT_ADJUSTED_DOC_TRX_ID.DELETE;
GT_ADJUSTED_DOC_EVENT_CLS_CD.DELETE;
GT_ADJUSTED_DOC_NUMBER.DELETE;
GT_BATCH_SOURCE_ID.DELETE;
GT_TAX_RATE_ID.DELETE;
GT_TAX_RATE_REG_TYPE_CODE.DELETE; --Check this
GT_PRODUCT_ID.DELETE;
GT_PRODUCT_DESCRIPTION.DELETE;
GT_TAXABLE_LINE_SOURCELE.DELETE;
GT_TAXABLE_ITEM_SOURCE_ID.DELETE;
GT_TAX_EXEMPTION_ID.DELETE;
GT_TAX_EXCEPTION_ID.DELETE;
GT_TAXABLE_LINE_TOTAL_AMOUNT.DELETE;
GT_TAX_LINE_ID.DELETE;
GT_TAX_AMT.DELETE;
GT_TAX_AMT_FUNCL_CURR.DELETE;
GT_TAX_LINE_NUMBER.DELETE;
GT_TAXABLE_AMT.DELETE;
GT_TAXABLE_AMT_FUNCL_CURR.DELETE;
GT_TRX_LINE_ID.DELETE;
GT_TRX_LINE_QUANTITY.DELETE;
GT_EXEMPT_CERTIFICATE_NUMBER.DELETE;
GT_EXEMPT_REASON_CODE.DELETE;
GT_TAX_EXCEPTION_REASON_CODE.DELETE;
GT_UOM_CODE.DELETE;
GT_RECONCILIATION_FLAG.DELETE;
GT_INTERNAL_ORGANIZATION_ID.DELETE;
GT_BR_REF_CUSTOMER_TRX_ID.DELETE;
GT_AMOUNT_APPLIED.DELETE;
GT_TAX_APPLIED.DELETE;
GT_TAX_RATE.DELETE;
GT_TAX_RATE_CODE.DELETE;
GT_TAX_RATE_VAT_TRX_TYPE_CODE.DELETE;
GT_TAX_TYPE_CODE.DELETE;
GT_TAX_RATE_CODE_NAME.DELETE; -- Do we need to rename tax_rate_name?
GT_TRX_NUMBER.DELETE;
GT_TRX_DESCRIPTION.DELETE;
GT_DOC_SEQ_VALUE.DELETE;
GT_TRX_DATE.DELETE;
GT_DEPOSIT_DATE.DELETE;
GT_TRX_DUE_DATE.DELETE;
GT_TRX_SHIPPING_DATE.DELETE;
GT_TRX_COMMUNICATED_DATE.DELETE;
GT_TRX_CURRENCY_CODE.DELETE;
GT_CURRENCY_CONVERSION_TYPE.DELETE;
GT_CURRENCY_CONVERSION_DATE.DELETE;
GT_CURRENCY_CONVERSION_RATE.DELETE;
GT_TRX_SIC_CODE.DELETE;
GT_FOB_POINT.DELETE;
GT_TRX_WAYBILL_NUMBER.DELETE;
GT_TRX_BATCH_SOURCE_NAME.DELETE;
GT_TRX_TYPE_DESCRIPTION.DELETE;
GT_TAX_RATE_CODE_DESCRIPTION.DELETE;
GT_TRX_LINE_DESCRIPTION.DELETE;
GT_TRX_LINE_NUMBER.DELETE;
GT_TRX_LINE_TYPE.DELETE;
GT_TAX_LINE_USER_CATEGORY.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE1.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE2.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE3.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE4.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE5.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE6.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE7.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE8.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE9.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE10.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE11.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE12.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE13.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE14.DELETE;
GT_TAX_LINE_USER_ATTRIBUTE15.DELETE;
GT_APPLICATION_ID.DELETE;
GT_DOC_EVENT_STATUS.DELETE;
GT_APPLICATION_DOC_STATUS.DELETE;
GT_ESTABLISHMENT_ID.DELETE;
GT_EXTRACT_SOURCE_LEDGER.DELETE;
GT_MINIMUM_ACCOUNTABLE_UNIT.DELETE;
GT_PRECISION.DELETE;
GT_RECEIPT_CLASS_ID.DELETE;
GT_APPLIED_TO_APPLICATION_ID.DELETE;
GT_APPLIED_TO_TRX_LINE_ID.DELETE;
GT_APPLIED_TO_ENTITY_CODE.DELETE;
GT_APPLIED_TO_TRX_ID.DELETE;
GT_APPLIED_TO_EVENT_CLS_CODE.DELETE;
GT_APPLIED_TO_TRX_NUMBER.DELETE;
GT_RELATED_DOC_APPLICATION_ID.DELETE;
GT_RELATED_DOC_DATE.DELETE;
GT_RELATED_DOC_ENTITY_CODE.DELETE;
GT_RELATED_DOC_EVENT_CLS_CODE.DELETE;
GT_RELATED_DOC_NUMBER.DELETE;
GT_RELATED_DOC_TRX_ID.DELETE;
GT_ADJUSTED_DOC_APPL_ID.DELETE;
GT_ADJUSTED_DOC_DATE.DELETE;
GT_REF_DOC_APPLICATION_ID.DELETE;
GT_REF_DOC_ENTITY_CODE.DELETE;
GT_REF_DOC_EVENT_CLASS_CODE.DELETE;
GT_REF_DOC_LINE_ID.DELETE;
GT_REF_DOC_LINE_QUANTITY.DELETE;
GT_REF_DOC_TRX_ID.DELETE;
GT_DEF_REC_SETTLEMENT_OPT_CD.DELETE;
GT_DEFAULT_TAXATION_COUNTRY.DELETE;
GT_DOCUMENT_SUB_TYPE.DELETE;
GT_ASSESSABLE_VALUE.DELETE;
GT_TRX_BUSINESS_CATEGORY.DELETE;
GT_LINE_INTENDED_USE.DELETE;
GT_PRODUCT_CATEGORY.DELETE;
GT_PROD_FISC_CLASSIFICATION.DELETE;
GT_SUPPLIER_EXCHANGE_RATE.DELETE;
GT_SUPPLIER_TAX_INVOICE_DATE.DELETE;
GT_SUPPLIER_TAX_INVOICE_NUM.DELETE;
GT_USER_DEFINED_FISC_CLASS.DELETE;
GT_EXCEPTION_RATE.DELETE;
GT_EXEMPT_RATE_MODIFIER.DELETE;
GT_ORIG_TAX_AMT.DELETE;
GT_ORIG_TAX_AMT_TAX_CURR.DELETE;
GT_ORIG_TAXABLE_AMT.DELETE;
GT_ORIG_TAXABLE_AMT_TAX_CURR.DELETE;
GT_RECOVERY_RATE_CODE.DELETE;
GT_TAX_AMT_TAX_CURR.DELETE;
GT_TAX_APPORTIONMENT_LINE_NUM.DELETE;
GT_TAX_CURRENCY_CODE.DELETE;
GT_TAX_DATE.DELETE;
GT_TAX_DETERMINE_DATE.DELETE;
GT_TAX_INVOICE_DATE.DELETE;
GT_TAX_INVOICE_NUMBER.DELETE;
GT_TAX_JURISDICTION_CODE.DELETE;
GT_TAX_REGIME_CODE.DELETE;
GT_TAX_STATUS_CODE.DELETE;
GT_TAX_STATUS_ID.DELETE;
GT_TAX.DELETE;
GT_SHIP_FROM_PTY_TAX_PROF_ID.DELETE;
GT_SHIP_FROM_SITE_TAX_PROF_ID.DELETE;
GT_SHIP_TO_PARTY_TAX_PROF_ID.DELETE;
GT_SHIP_TO_SITE_TAX_PROF_ID.DELETE;
GT_BILL_TO_PTY_TAX_PROF_ID.DELETE;
GT_BILL_TO_SITE_TAX_PROF_ID.DELETE;
GT_HQ_ESTB_REG_NUMBER.DELETE;
gt_tax_line_registration_num.delete;
GT_LEGAL_ENTITY_TAX_REG_NUM.DELETE;
GT_OWN_HQ_PARTY_SITE_PROF_ID.DELETE;
GT_OWN_HQ_PARTY_TAX_PROF_ID.DELETE;
GT_PORT_OF_ENTRY_CODE.DELETE;
GT_REGISTRATION_PARTY_TYPE.DELETE;
GT_HISTORICAL_FLAG.DELETE;
GT_MRC_TAX_LINE_FLAG.DELETE;
GT_POSTED_FLAG.DELETE;
GT_REPORTING_ONLY_FLAG.DELETE;
GT_TAX_AMT_INCLUDED_FLAG.DELETE;
GT_TAX_ONLY_LINE_FLAG.DELETE;
GT_EVENT_CLASS_MAPPING_ID.DELETE;
GT_TRX_LEVEL_TYPE.DELETE;
GT_PLACE_OF_SUPPLY_TYPE_CODE.DELETE;
GT_POA_LOCATION_ID.DELETE;
GT_POO_LOCATION_ID.DELETE;
GT_DEF_PLACE_OF_SUP_TYPE_CODE.DELETE;
GT_LINE_AMT.DELETE;
GT_TAX_PROVIDER_ID.DELETE;
GT_AR_CASH_RCPT_RVRS_DATE.DELETE;
GT_AR_CASH_RCPT_RVRS_STATUS.DELETE;
GT_ACCTD_AMOUNT_CR.DELETE;
GT_ACCTD_AMOUNT_DR.DELETE;
GT_REC_APPLICATION_STATUS.DELETE;
GT_ACCOUNT_CLASS.DELETE;
GT_LATEST_REC_FLAG.DELETE;
GT_ACTG_SOURCE_ID.DELETE;
GT_ENTITY_CODE.DELETE;
GT_POSTED_DATE.DELETE;
GT_GL_DATE.DELETE;
GT_UNIT_PRICE.DELETE;
GT_BANKING_ACCOUNT_ID.DELETE;