The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_AR_TRX_INFO;
C_LINES_PER_INSERT CONSTANT NUMBER := 5000;
GT_LAST_UPDATED_BY ZX_EXTRACT_PKG.LAST_UPDATED_BY_TBL;
GT_LAST_UPDATE_DATE ZX_EXTRACT_PKG.LAST_UPDATE_DATE_TBL;
GT_LAST_UPDATE_LOGIN ZX_EXTRACT_PKG.LAST_UPDATE_LOGIN_TBL;
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_ACTG_EXTRACT_PKG.INSERT_TAX_DATA.BEGIN',
'ZX_AR_ACTG_EXTRACT_PKG: INSERT_TAX_DATA(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_EXTRACT_PKG.INSERT_TAX_DATA.END',
'ZX_AR_ACTG_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(500);
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
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,''IMMEDIATE'',''TAX'',''DEFERRED'',''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_LINE.TAX_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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_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.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_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.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,
-- ZX_LINE.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,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
to_number(NULL), --xla_line.ae_line_num,
to_char(NULL), --xla_dist.accounting_class_code,
to_char(NULL), --xla_line.description,
to_number(NULL), --xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
to_number(NULL), --xla_line.party_id,
to_number(NULL), --xla_line.party_site_id,
to_char(NULL), --xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
to_number(NULL), --xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_id,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL) ';
'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
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,''IMMEDIATE'',''TAX'',''DEFERRED'',''INTERIM''), --ZX_LINE.TAX_RATE_REGISTER_TYPE_CODE,
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_LINE.TAX_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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_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.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_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.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,
-- ZX_LINE.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,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_line_id,
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL),
TO_NUMBER(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL) ';
'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
'TO_CHAR(NULL) ,'|| --ADJUSTED_DOC_NUMBER --APPLIED_TO_TRX_NUMBER
'ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,''IMMEDIATE'',''TAX'',''DEFERRED'',''INTERIM''), --ZX_LINE.TAX_RATE_REGISTER_TYPE_CODE,
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_LINE.TAX_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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_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.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_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.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,
-- ZX_LINE.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,
xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
dist.cust_trx_line_gl_dist_id,
TO_DATE ( NULL ),
TO_CHAR ( NULL ),
TO_NUMBER ( NULL ),
TO_NUMBER ( NULL ),
TO_CHAR ( NULL ),
DIST.ACCOUNT_CLASS,
DIST.LATEST_REC_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'','||
'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_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,
'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.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,
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.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
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
ard.line_id,
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARD.ACCTD_AMOUNT_CR),
sum(ARD.ACCTD_AMOUNT_DR),
APP.STATUS,
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_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,
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.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,
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.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
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
ardtax.line_id,
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARD.ACCTD_AMOUNT_CR),
sum(ARD.ACCTD_AMOUNT_DR),
APP.STATUS,
TO_CHAR(NULL),
TO_CHAR(NULL) ';
'SELECT '||
'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_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,
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.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,
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.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,
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_id,
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) ';
'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
'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),
'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_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,
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.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,
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.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,
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
ardtax.line_id,
TO_DATE(NULL),
TO_CHAR(NULL),
sum(ARD.ACCTD_AMOUNT_CR),
sum(ARD.ACCTD_AMOUNT_DR),
TO_CHAR(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
'TO_NUMBER(NULL),
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_TAX.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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),
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),
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
EXTRACT_SOURCE_LEDGER,
ZX_DET.FUNCTIONAL_CURRECNY_CODE,
ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
RECEIPT_CLASS_ID, --Check this
ZX_DET.ADJUSTED_DOC_APPLICATION_ID,
ZX_DET.ADJUSTED_DOC_DATE,
ZX_DET.ADJUSTED_DOC_ENTITY_CODE,
ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE,
ZX_DET.ADJUSTED_DOC_NUMBER,
ZX_DET.ADJUSTED_DOC_TRX_ID,
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.APPLIED_TO_APPLICATION_ID,
ZX_DET.APPLIED_TO_TRX_LINE_ID,
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_DET.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
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_DET.EXCEPTION_RATE,
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,
ZX_RATE.RECOVERY_RATE_CODE,
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_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,
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER,
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER,
'ZX_DET.OWN_HQ_PARTY_SITE_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
'TO_CHAR(NULL),'|| --ZX_LINE.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
'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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
arcs.cash_receipt_id,
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARD.ACCTD_AMOUNT_CR),
sum(ARD.ACCTD_AMOOUNT_DR),
TO_CHAR(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)),'||
'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
ZX_TAX.PERCENTAGE_RATE,
ZX_RATE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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),
ZX_DET.APPLICATION_ID
ZX_DET.DOC_EVENT_STATUS
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID
EXTRACT_SOURCE_LEDGER
ZX_DET.FUNCTIONAL_CURRECNY_CODE
ZX_DET.LEDGER_NAME
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT
ZX_DET.PRECISION
RECEIPT_CLASS_ID
ZX_DET.ADJUSTED_DOC_APPLICATION_ID
ZX_DET.ADJUSTED_DOC_DATE
ZX_DET.ADJUSTED_DOC_ENTITY_CODE
ZX_DET.ADJUSTED_DOC_EVENT_CLASS_CODE
ZX_DET.ADJUSTED_DOC_NUMBER
ZX_DET.ADJUSTED_DOC_TRX_ID
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.APPLIED_TO_APPLICATION_ID
ZX_DET.APPLIED_TO_TRX_LINE_ID
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_DET.DEF_REC_SETTLEMENT_OPTION_CODE
ZX_DET.DEFAULT_TAXATION_COUNTRY
ZX_DET.DOCUMENT_SUB_TYPE
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_DET.EXCEPTION_RATE
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
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_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
TO_CHAR(NULL),'|| --ZX_LINE.HQ_ESTB_REG_NUMBER
'TO_CHAR(NULL),'|| --ZX_LINE.LEGAL_ENTITY_TAX_REG_NUMBER
'ZX_DET.OWN_HQ_PARTY_SITE_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
'TO_CHAR(NULL),'|| --ZX_LINE.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
'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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
ardtax.line_id,
ARCS.REVERSAL_DATE,
ARCS.STATUS,
sum(ARD.ACCTD_AMOUNT_CR),
sum(ARD.ACCTD_AMOOUNT_DR),
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(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_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,
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.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,
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.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,
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
zx_det.trx_id,
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)';
'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_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,
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.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,
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.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
-- 'TO_CHAR(NULL),'|| --ZX_LINE.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),'||
'xla_event.event_type_code, -- Accounting Columns
xla_event.event_number,
xla_event.event_status_code,
xla_head.je_category_name,
xla_head.accounting_date,
xla_head.gl_transfer_status_code,
xla_head.description,
xla_line.ae_line_num,
xla_line.accounting_class_code,
xla_line.description,
xla_line.statistical_amount,
xla_event.process_status_code,
xla_head.gl_transfer_status_code,
xla_head.doc_sequence_id,
xla_head.doc_sequence_value,
xla_line.party_id,
xla_line.party_site_id,
xla_line.party_type_code,
xla_event.event_id,
xla_head.ae_header_id,
xla_line.code_combination_id,
xla_head.period_name,
ardtax.line_id,
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) ';
st1 := ' 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) ,TO_NUMBER(NULL) ,TO_CHAR(NULL) ,TO_CHAR(NULL) ,TO_NUMBER(NULL)
,TO_CHAR(NULL) ,TO_CHAR(NULL) ,ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
DECODE(ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,''IMMEDIATE'',''TAX'',''DEFERRED'',''INTERIM''),
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
TO_CHAR(NULL),TO_NUMBER(NULL),ZX_LINE.TAX_EXEMPTION_ID,
ZX_LINE.TAX_EXCEPTION_ID,
TO_NUMBER(NULL),TO_NUMBER(NULL),ZX_LINE.TAX_AMT,
ZX_LINE.TAX_AMT_FUNCL_CURR ,
TO_NUMBER(NULL),ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
TO_NUMBER(NULL),TO_NUMBER(NULL),ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
TO_CHAR(NULL),ZX_DET.INTERNAL_ORGANIZATION_ID ,TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),ZX_LINE.TAX_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_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_DET.TRX_LINE_DESCRIPTION,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'',--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
TO_NUMBER(NULL),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),TO_NUMBER(NULL),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.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),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_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.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,
-- ZX_LINE.POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG,
to_char(NULL), --xla_event.event_type_code, -- Accounting Column
to_char(NULL), --xla_event.event_number,
to_char(NULL), --xla_event.event_status_code,
to_char(NULL), --xla_head.je_category_name,
to_date(NULL), --xla_head.accounting_date,
to_char(NULL), --xla_head.gl_transfer_status_code,
to_char(NULL), --xla_head.description,
to_number(NULL), --xla_line.ae_line_num,
to_char(NULL), --xla_dist.accounting_line_code,
to_char(NULL), --xla_line.description,
to_number(NULL), --xla_line.statistical_amount,
to_char(NULL), --xla_event.process_status_code,
to_char(NULL), --xla_head.gl_transfer_status_code,
to_number(NULL),--xla_head.doc_sequence_id,
to_char(NULL), --xla_head.doc_sequence_value,
to_number(NULL), --xla_line.party_id,
to_number(NULL), --xla_line.party_site_id,
to_char(NULL), --xla_line.party_type_code,
to_number(NULL),--xla_event.event_id,
to_number(NULL),--xla_head.ae_header_id,
to_number(NULL), --xla_line.code_combination_id,
to_char(NULL), --xla_head.period_name,
zx_det.trx_id
FROM ZX_LINES ZX_LINE,
ZX_LINES_DET_FACTORS ZX_DET,
ZX_TAXES_B ZX_TAX,
ZX_RATES_VL ZX_RATE
WHERE ZX_DET.INTERNAL_ORGANIZATION_ID = ZX_LINE.INTERNAL_ORGANIZATION_ID
AND ZX_DET.APPLICATION_ID = ZX_LINE.APPLICATION_ID
AND ZX_DET.ENTITY_CODE = ZX_LINE.ENTITY_CODE
AND ZX_DET.EVENT_CLASS_CODE = ZX_LINE.EVENT_CLASS_CODE
AND ZX_DET.TRX_ID = ZX_LINE.TRX_ID
AND ZX_DET.TRX_LINE_ID = ZX_LINE.TRX_LINE_ID
AND zx_det.tax_reporting_flag = ''Y''
AND ZX_LINE.TAX_ID = ZX_TAX.TAX_ID(+)
AND ZX_LINE.TAX_RATE_ID = NVL(ZX_RATE.SOURCE_ID,ZX_RATE.TAX_RATE_ID)
AND ((ZX_RATE.RECORD_TYPE_CODE <> ''MIGRATED'')
OR (ZX_RATE.SOURCE_ID IS NULL))
-- AND ZX_DET.trx_id = 83546
AND DECODE(:G_LEDGER_ID,NULL,NULL) IS NULL
--AND ZX_DET.LEGAL_ENTITY_ID = :G_LEGAL_ENTITY_ID
AND ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE = ''IMMEDIATE''
AND ZX_DET.LINE_CLASS in (''INVOICE'',''CREDIT_MEMO'',''DEBIT_MEMO'')
AND DECODE(:G_BATCH_NAME,NULL,NULL) IS NULL
--AND ZX_DET.TRX_NUMBER BETWEEN :G_TRX_NUMBER_LOW AND :G_TRX_NUMBER_HIGH
AND decode(:G_TRX_DATE_LOW,NULL,NULL) is null and decode(:G_TRX_DATE_HIGH,NULL,NULL) is null
AND decode(:G_GL_DATE_LOW,NULL,NULL) is null and decode(:G_GL_DATE_HIGH,NULL,NULL) is null
AND DECODE(:G_DOCUMENT_SUB_TYPE,NULL,NULL) IS NULL AND DECODE(:G_TRX_BUSINESS_CATEGORY,NULL,NULL) IS NULL
AND DECODE(:G_TAX_INVOICE_DATE_LOW,NULL,NULL) IS NULL AND DECODE(:G_TAX_INVOICE_DATE_HIGH,NULL,NULL) IS NULL
AND DECODE(:G_TAX_REGIME_CODE,NULL,NULL) IS NULL AND DECODE(:G_TAX,NULL,NULL) IS NULL
AND DECODE(:G_TAX_STATUS_CODE,NULL,NULL) IS NULL
AND DECODE(:G_TAX_RATE_CODE_LOW,NULL,NULL) IS NULL
AND DECODE(:G_TAX_RATE_CODE_HIGH,NULL,NULL) IS NULL
AND DECODE(:G_TAX_TYPE_CODE_LOW,NULL,NULL) IS NULL
AND DECODE(:G_TAX_TYPE_CODE_HIGH,NULL,NULL) IS NULL
AND DECODE(:G_CURRENCY_CODE_LOW,NULL,NULL) IS NULL
AND DECODE(:G_CURRENCY_CODE_HIGH,NULL,NULL) IS NULL
AND DECODE(:G_AR_EXEMPTION_STATUS,NULL,NULL) is NULL
AND DECODE(:G_VAT_TRANSACTION_TYPE_CODE,NULL,NULL) IS NULL
AND decode(:G_VAT_TAX,NULL,NULL) is null
AND decode(:G_VAT_ADDITIONAL_TAX,NULL,NULL) is null
AND decode(:G_VAT_NON_TAXABLE_TAX,NULL,NULL) is null
AND decode(:G_VAT_NOT_TAX,NULL,NULL) is null
AND decode(:G_VAT_PERCEPTION_TAX,NULL,NULL) is null
AND decode(:G_EXCISE_TAX,NULL,NULL) is null
GROUP BY 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 ,
ZX_DET.BATCH_SOURCE_ID,
ZX_LINE.TAX_RATE_ID ,
-- ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
--ZX_LINE.TAX_RATE_REGISTER_TYPE_CODE,
ZX_DET.PRODUCT_ID,
ZX_DET.PRODUCT_DESCRIPTION,
ZX_LINE.TAX_EXEMPTION_ID,
ZX_LINE.TAX_EXCEPTION_ID,
ZX_LINE.TAX_AMT,
ZX_LINE.TAX_AMT_FUNCL_CURR ,
ZX_LINE.TAXABLE_AMT,
ZX_LINE.TAXABLE_AMT_FUNCL_CURR ,
ZX_DET.EXEMPT_CERTIFICATE_NUMBER,
ZX_LINE.EXEMPT_REASON_CODE ,
TO_CHAR(NULL), --exception_reason_code
ZX_DET.UOM_CODE,
ZX_DET.INTERNAL_ORGANIZATION_ID ,--ORG_ID
TO_CHAR(NULL), --reverse_date
ZX_LINE.TAX_RATE,
ZX_LINE.TAX_RATE_CODE,
ZX_RATE.VAT_TRANSACTION_TYPE_CODE,
ZX_RATE.RATE_TYPE_CODE,
ZX_RATE.TAX_RATE_NAME,
ZX_DET.TRX_NUMBER,
ZX_DET.TRX_DESCRIPTION,
ZX_DET.DOC_SEQ_VALUE,
ZX_DET.TRX_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_DET.TRX_LINE_DESCRIPTION,
ZX_DET.APPLICATION_ID,
ZX_DET.DOC_EVENT_STATUS,
ZX_DET.APPLICATION_DOC_STATUS,
ZX_DET.ESTABLISHMENT_ID,
--ZX_DET.FUNCTIONAL_CURRECNY_CODE,
--ZX_DET.LEDGER_NAME,
ZX_DET.MINIMUM_ACCOUNTABLE_UNIT,
ZX_DET.PRECISION,
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_TRX_ID,
ZX_RATE.DEF_REC_SETTLEMENT_OPTION_CODE,
ZX_DET.DEFAULT_TAXATION_COUNTRY,
ZX_DET.DOCUMENT_SUB_TYPE,
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,
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_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.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,
-- ZX_LINE.POSTED_FLAG,
ZX_LINE.REPORTING_ONLY_FLAG,
ZX_LINE.TAX_AMT_INCLUDED_FLAG,
ZX_LINE.TAX_ONLY_LINE_FLAG ';
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
--INSERT_AR_SUB_ITF;
INSERT_AR_TRX_INFO;
INSERT_AR_TRX_INFO;
| 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_ACTG_EXTRACT_PKG_SUB_ITF table |
| |
| SCOPE - Private |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 4-AUG-03 Phong La Created |
| 07-OCT-03 Phong La Bugfix 3175265 added ORG_ID, EXCHANGE_RATE, |
| GL_POSTED_DATE, PRECISION, |
| MINIMUM_ACCOUNTABLE_UNIT, |
| BALANCING_SEGMENT_VALUE to insert |
| 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_ACTG_EXTRACT_PKG.INSERT_AR_TRX_INFO.BEGIN',
'ZX_AR_ACTG_EXTRACT_PKG: INSERT_AR_TRX_INFO(+)');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_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,
-- 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,
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,
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,
SHIPPING_TP_SITE_ID,
BILLING_TP_SITE_ID,
SHIPPING_TRADING_PARTNER_ID,
BILLING_TRADING_PARTNER_ID,
HQ_ESTB_REG_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)
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_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_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_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_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,
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));
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_AR_ACTG_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_ACTG_EXTRACT_PKG.INSERT_AR_TRX_INFO.END',
'ZX_AR_ACTG_EXTRACT_PKG: INSERT_AR_TRX_INFO(-)');
INSERT INTO ZX_REP_ACTG_EXT_T(
actg_ext_line_id,
detail_tax_line_id,
actg_event_type_code,
actg_event_number,
actg_event_status_flag,
actg_category_code,
accounting_date,
gl_transfer_flag,
-- gl_transfer_run_id,
actg_header_description,
actg_line_num,
actg_line_type_code,
actg_line_description,
actg_stat_amt,
actg_error_code,
gl_transfer_code,
actg_doc_sequence_id,
-- actg_doc_sequence_name,
actg_doc_sequence_value,
actg_party_id,
actg_party_site_id,
actg_party_type,
actg_event_id,
actg_header_id,
actg_source_id,
-- actg_source_table,
actg_line_ccid,
period_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_login_id)
VALUES (zx_rep_actg_ext_t_s.nextval,
gt_detail_tax_line_id(i),
gt_actg_event_type_code(i),
gt_actg_event_number(i),
gt_actg_event_status_flag(i),
gt_actg_category_code(i),
gt_accounting_date(i),
gt_gl_transfer_flag(i),
-- gt_gl_transfer_run_id(i),
gt_actg_header_description(i),
gt_actg_line_num(i),
gt_actg_line_type_code(i),
gt_actg_line_description(i),
gt_actg_stat_amt(i),
gt_actg_error_code(i),
gt_gl_transfer_code(i),
gt_actg_doc_sequence_id(i),
-- gt_actg_doc_sequence_name(i),
gt_actg_doc_sequence_value(i),
gt_actg_party_id(i),
gt_actg_party_site_id(i),
gt_actg_party_type(i),
gt_actg_event_id(i),
gt_actg_header_id(i),
gt_actg_source_id(i),
-- gt_actg_source_table(i),
gt_actg_line_ccid(i),
gt_period_name(i),
g_created_by,
g_creation_date,
g_last_updated_by,
g_last_update_date,
g_last_update_login,
g_program_application_id,
g_program_id,
g_program_login_id);
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_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_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_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_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_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_actg_ext_line_id.delete;
gt_detail_tax_line_id.delete;
gt_actg_event_type_code.delete;
gt_actg_event_number.delete;
gt_actg_event_status_flag.delete;
gt_actg_category_code.delete;
gt_accounting_date.delete;
gt_gl_transfer_flag.delete;
gt_gl_transfer_run_id.delete;
gt_actg_header_description.delete;
gt_actg_line_num.delete;
gt_actg_line_type_code.delete;
gt_actg_line_description.delete;
gt_actg_stat_amt.delete;
gt_actg_error_code.delete;
gt_gl_transfer_code.delete;
gt_actg_doc_sequence_id.delete;
gt_actg_doc_sequence_name.delete;
gt_actg_doc_sequence_value.delete;
gt_actg_party_id.delete;
gt_actg_party_site_id.delete;
gt_actg_party_type.delete;
gt_actg_event_id.delete;
gt_actg_header_id.delete;
gt_actg_source_id.delete;
gt_actg_source_table.delete;
gt_actg_line_ccid.delete;
gt_period_name.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_created_by.delete;
gt_creation_date.delete;
gt_last_updated_by.delete;
gt_last_update_date.delete;
gt_last_update_login.delete;
gt_program_application_id.delete;
gt_program_id.delete;
gt_program_login_id.delete;*/
/* PG_EXTRACT_REPORT_LINE_NUM_TAB.DELETE;
PG_LEDGER_ID_TAB.DELETE;
PG_MATRIX_REPORT_FLAG_TAB.DELETE;
PG_TRX_ID_TAB.DELETE;
PG_TRX_DOC_SEQUENCE_ID_TAB.DELETE;
PG_TRX_DOC_SEQUENCE_NAME_TAB.DELETE;
PG_TRX_TYPE_ID_TAB.DELETE;
PG_TRX_CLASS_CODE_TAB.DELETE;
PG_TRX_APPLIED_FRM_HDR_TBL_TAB.DELETE;
PG_TRX_FRM_TRX_HDR_ID_TAB.DELETE;
PG_TRX_APPLD_FRM_CLS_CD_TAB.DELETE;
PG_TRX_APPLIED_TO_HDR_TBL_TAB.DELETE;
PG_TRX_APPLD_TO_TRX_HDR_ID_TAB.DELETE;
PG_TRX_APPLIED_TO_CLS_CD_TAB.DELETE;
PG_TRX_APPLIED_TO_TRX_NUM_TAB.DELETE;
PG_BILLING_TRDG_PTNR_ID_TAB.DELETE;
PG_BILLING_TP_SITE_ID_TAB.DELETE;
PG_BILLING_TP_ADDRESS_ID_TAB.DELETE;
PG_SHIPPING_TRDG_PTNR_ID_TAB.DELETE;
PG_SHIPPING_TP_SITE_ID_TAB.DELETE;
PG_SHIPPING_TP_ADDRESS_ID_TAB.DELETE;
PG_AR_TRX_BATCH_SOURCE_ID_TAB.DELETE;
PG_TAX_CODE_ID_TAB.DELETE;
PG_TAX_CODE_RGSTR_TYP_CD_TAB.DELETE;
PG_TAX_OFFSET_TAX_CODE_ID_TAB.DELETE;
PG_TAXABLE_LINE_ITEM_ID_TAB.DELETE;
PG_TAXABLE_LINE_ITEM_DESC_TAB.DELETE;
PG_TAXABLE_LINE_SOURCE_TBL_TAB.DELETE;
PG_TAXABLE_ITEM_SOURCE_ID_TAB.DELETE;
PG_TAX_EXEMPTION_ID_TAB.DELETE;
PG_TAX_EXCEPTION_ID_TAB.DELETE;
PG_TAXABLE_LINE_TOTAL_AMT_TAB.DELETE;
PG_TAX_LINE_ID_TAB.DELETE;
PG_TAX_ENTERED_DR_TAB.DELETE;
PG_TAX_ENTERED_CR_TAB.DELETE;
PG_TAX_ENTERED_AMOUNT_TAB.DELETE;
PG_EXEMPT_ENTERED_AMOUNT_TAB.DELETE;
PG_TAX_ACCOUNTED_DR_TAB.DELETE;
PG_TAX_ACCOUNTED_CR_TAB.DELETE;
PG_TAX_ACCOUNTED_AMOUNT_TAB.DELETE;
PG_TAX_LINE_NUMBER_TAB.DELETE;
PG_TAXABLE_ENTERED_DR_TAB.DELETE;
PG_TAXABLE_ENTERED_CR_TAB.DELETE;
PG_TAXABLE_AMOUNT_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_DR_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_CR_TAB.DELETE;
PG_TAXABLE_ACCOUNTED_AMT_TAB.DELETE;
PG_ACCTG_DIST_ID_TAB.DELETE;
PG_AE_SOURCE_TABLE_TAB.DELETE;
PG_AE_SOURCE_ID_TAB.DELETE;
PG_AL_ACCOUNT_CCID_TAB.DELETE;
PG_TRX_LINE_ID_TAB.DELETE;
PG_TAXABLE_LINE_QUANTITY_TAB.DELETE;
PG_BILLING_TP_SITE_NAME_TAB.DELETE;
PG_BLNG_TP_SITE_TX_REG_NUM_TAB.DELETE;
PG_SHIPPING_TP_SITE_NAME_TAB.DELETE;
PG_SHP_TP_SITE_TAX_REG_NUM_TAB.DELETE;
PG_TAX_EXEMPT_NUMBER_TAB.DELETE;
PG_TAX_EXEMPT_REASON_CODE_TAB.DELETE;
PG_TAX_EXCEPTN_REASN_CODE_TAB.DELETE;
PG_TXBL_QUANTITY_UOM_CODE_TAB.DELETE;
PG_RECONCILIATION_FLAG_TAB.DELETE;
PG_ORG_ID_TAB.DELETE;
PG_EXCHANGE_RATE_TAB.DELETE;
PG_CURRENCY_CODE_TAB.DELETE;
PG_ACCOUNTING_DATE_TAB.DELETE;
PG_GL_POSTED_DATE_TAB.DELETE;
PG_TAX_CLASS_TAB.DELETE;
PG_TXBL_XMPT_TOT_ENTRD_AMT_TAB.DELETE;
PG_BR_REF_CUSTOMER_TRX_ID_TAB.DELETE;
PG_REVERSAL_DATE_TAB.DELETE;
PG_AMOUNT_APPLIED_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT_CAT_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT1_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT4_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT6_TAB.DELETE;
PG_TAX_RATE_TAB.DELETE;
PG_ENABLED_FLAG_TAB.DELETE;
PG_GDF_RA_BCH_SRC_ATT_CAT_TAB.DELETE;
PG_GDF_RA_BATCH_SRC_ATT1_TAB.DELETE;
PG_GDF_RA_BATCH_SRC_ATT3_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT_CAT_TAB.DELETE;
PG_GDF_RA_TRX_TYPES_ATT1_TAB.DELETE;
PG_GDF_RA_TRX_TYPES_ATT6_TAB.DELETE;
PG_GDF_RA_TRX_TYPES_ATT7_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT_CAT_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT19_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT5_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT9_TAB.DELETE;
PG_TAX1_CODE_TAB.DELETE;
PG_TAX1_CD_VAT_TRX_TYPE_CD_TAB.DELETE;
PG_TAX1_CODE_TYPE_MEANING_TAB.DELETE;
PG_TAX1_CODE_TYPE_CODE_TAB.DELETE;
PG_TAX1_CODE_NAME_TAB.DELETE;
PG_TAX1_CODE_DESCRIPTION_TAB.DELETE;
PG_TRX_NUMBER_TAB.DELETE;
PG_TRX_DESCRIPTION_TAB.DELETE;
PG_TRX_DOC_SEQUENCE_VALUE_TAB.DELETE;
PG_TRX_DATE_TAB.DELETE;
PG_TRX_HEADER_CATEGORY_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE1_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE2_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE3_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE4_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE5_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE6_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE7_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE8_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE9_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE10_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE11_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE12_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE13_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE14_TAB.DELETE;
PG_TRX_HEADER_ATTRIBUTE15_TAB.DELETE;
PG_TRX_DUE_DATE_TAB.DELETE;
PG_TRX_SHIPPING_DATE_TAB.DELETE;
PG_TRX_COMMUNICATED_DATE_TAB.DELETE;
PG_TRX_CURRENCY_CODE_TAB.DELETE;
PG_TRX_CURRENCY_CNVRSN_TYP_TAB.DELETE;
PG_TRX_CURRENCY_CNVRSN_DT_TAB.DELETE;
PG_TRX_CURRENCY_CNVRSN_RT_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT1_TAB.DELETE;
PG_GDF_RA_CST_TRX_ATT1_DT_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT2_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT3_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT4_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT6_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT7_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT8_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT10_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT11_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT12_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT13_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT14_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT15_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT16_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT17_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT18_TAB.DELETE;
PG_GDF_RA_CUST_TRX_ATT20_TAB.DELETE;
PG_AR_TRX_SIC_CODE_TAB.DELETE;
PG_AR_TRX_FOB_POINT_TAB.DELETE;
PG_AR_TRX_WAYBILL_NUMBER_TAB.DELETE;
PG_AR_TRX_POSTING_STATUS_TAB.DELETE;
PG_AR_TRX_PURCHASE_ORDER_TAB.DELETE;
PG_AR_TRX_PRCHS_ODR_RVS_TAB.DELETE;
PG_AR_TRX_SALES_ORDER_TAB.DELETE;
PG_AR_TRX_SALES_ODR_SRC_TAB.DELETE;
PG_AR_TRX_SLS_ODR_RVS_TAB.DELETE;
PG_AR_TRX_SALES_ORDER_DATE_TAB.DELETE;
PG_AR_TRX_BATCH_SRC_NAME_TAB.DELETE;
PG_GDF_RA_BTCH_SRC_ATT2_TAB.DELETE;
PG_GDF_RA_BTCH_SRC_ATT4_TAB.DELETE;
PG_GDF_RA_BTCH_SRC_ATT5_TAB.DELETE;
PG_GDF_RA_BTCH_SRC_ATT6_TAB.DELETE;
PG_TRX_TYPE_MEANING_TAB.DELETE;
PG_TRX_TYPE_DESCRIPTION_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT2_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT3_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT4_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT5_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT10_TAB.DELETE;
PG_GDF_RA_TRX_TYP_ATT20_TAB.DELETE;
PG_TAX_CODE_DESCRIPTION_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT2_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT3_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT5_TAB.DELETE;
PG_GDF_AR_VAT_TAX_ATT7_TAB.DELETE;
PG_TAX_CODE_TAX_CLASS_CODE_TAB.DELETE;
PG_TAX_CODE_TAB.DELETE;
PG_TAX_CODE_RATE_TAB.DELETE;
PG_TAX_CODE_TYPE_CODE_TAB.DELETE;
PG_TAX_CD_VAT_TRX_TYP_CD_TAB.DELETE;
PG_TAX_CODE_NAME_TAB.DELETE;
PG_TAXABLE_LINE_DESC_TAB.DELETE;
PG_TAXABLE_LN_ENTERED_AMT_TAB.DELETE;
PG_TAXABLE_LINE_ACCTED_AMT_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT_CAT_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT1_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT2_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT3_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT4_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT5_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT6_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT7_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT8_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT9_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT10_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT11_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT12_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT13_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT14_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT15_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT16_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT17_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT18_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT19_TAB.DELETE;
PG_GDF_RA_TRX_LN_ATT20_TAB.DELETE;
PG_TAXABLE_LINE_NUMBER_TAB.DELETE;
PG_TAXABLE_LINE_TYPE_CODE_TAB.DELETE;
PG_TAXABLE_LN_TYPE_MNING_TAB.DELETE;
PG_TAX_LINE_USER_CATEGORY_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB1_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB2_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB3_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB4_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB5_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB6_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB7_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB8_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB9_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB10_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB11_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB12_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB13_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB14_TAB.DELETE;
PG_TAX_LN_USR_ATTRIB15_TAB.DELETE;
PG_TAX_LN_EFCTV_TAX_RATE_TAB.DELETE;
PG_AH_ACCOUNTING_DATE_TAB.DELETE;