ZX_ACCT_TRANSACTIONS_V

Details

• Schema: FUSION

• Object owner: ZX

• Object type: VIEW

Columns

Name

APPLICATION_ID ENTITY_CODE EVENT_CLASS_CODE EVENT_TYPE_CODE TRX_ID TRX_DATE TRX_NUMBER TAX_EVENT_CLASS_CODE ACCOUNTING_DATE BATCH_SOURCE_ID BATCH_SOURCE_NAME BATCH_SOURCE_SEQ_ID DOC_SEQ_ID DOC_SEQ_NAME DOC_SEQ_VALUE DOCUMENT_TYPE FIRST_PTY_ORG_ID FIRST_PTY_REG_ID INTERNAL_ORGANIZATION_ID LEDGER_ID LEGAL_ENTITY_ID RECEIVABLES_TRX_TYPE_ID RECEIVABLES_TRX_TYPE_SEQ_ID THIRD_PTY_REG_ID BILL_THIRD_PTY_ACCT_ID SHIP_THIRD_PTY_ACCT_ID BILL_THIRD_PTY_ACCT_SITE_ID SHIP_THIRD_PTY_ACCT_SITE_ID PARTY_TYPE TRX_CURRENCY_CODE TRX_DESCRIPTION TRX_DOC_REVISION TRX_DUE_DATE TRX_SOURCE TRX_TYPE_DESCRIPTION HDR_TRX_USER_KEY1 HDR_TRX_USER_KEY2 HDR_TRX_USER_KEY3 HDR_TRX_USER_KEY4 HDR_TRX_USER_KEY5 HDR_TRX_USER_KEY6

Query

SQL_Statement

SELECT DISTINCT zd.application_id , zd.entity_code, zd.event_class_code, zd.EVENT_TYPE_CODE, zd.trx_id, zd.trx_date, zd.trx_number, zd.tax_event_class_code, zd.HDR_GL_DATE ACCOUNTING_DATE, zd.BATCH_SOURCE_ID, zd.BATCH_SOURCE_NAME, zd.BATCH_SOURCE_SEQ_ID, zd.DOC_SEQ_ID, zd.DOC_SEQ_NAME, zd.DOC_SEQ_VALUE, zd.DOCUMENT_TYPE, zd.FIRST_PTY_ORG_ID, zd.FIRST_PTY_REG_ID, zd.INTERNAL_ORGANIZATION_ID, zd.LEDGER_ID, zd.LEGAL_ENTITY_ID, zd.RECEIVABLES_TRX_TYPE_ID, zd.RECEIVABLES_TRX_TYPE_SEQ_ID, zd.THIRD_PTY_REG_ID, zd.BILL_THIRD_PTY_ACCT_ID, zd.SHIP_THIRD_PTY_ACCT_ID, zd.BILL_THIRD_PTY_ACCT_SITE_ID, zd.SHIP_THIRD_PTY_ACCT_SITE_ID, (select CASE WHEN supplier_flag = 'Y' AND customer_flag = 'Y' AND zd.TAX_EVENT_CLASS_CODE ='PURCHASE_TRANSACTION' THEN 'S' WHEN supplier_flag = 'Y' AND customer_flag = 'Y' AND zd.TAX_EVENT_CLASS_CODE ='SALES_TRANSACTION' THEN 'C' WHEN supplier_flag = 'Y' THEN 'S' WHEN customer_flag = 'Y' THEN 'C' ELSE '' END from zx_party_tax_profile where ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID = coalesce (zd.BILL_FROM_SITE_TAX_PROF_ID,zd.BILL_TO_SITE_TAX_PROF_ID,zd.SHIP_FROM_SITE_TAX_PROF_ID,zd.SHIP_TO_SITE_TAX_PROF_ID,zd.BILL_FROM_PARTY_TAX_PROF_ID,zd.BILL_TO_PARTY_TAX_PROF_ID,zd.SHIP_FROM_PARTY_TAX_PROF_ID,zd.SHIP_TO_PARTY_TAX_PROF_ID)) as PARTY_TYPE, zd.TRX_CURRENCY_CODE, zd.TRX_DESCRIPTION, zd.TRX_DOC_REVISION, zd.TRX_DUE_DATE, zd.TRX_SOURCE, zd.TRX_TYPE_DESCRIPTION, zd.hdr_trx_user_key1, zd.HDR_TRX_USER_KEY2, zd.HDR_TRX_USER_KEY3, zd.HDR_TRX_USER_KEY4, zd.HDR_TRX_USER_KEY5, zd.HDR_TRX_USER_KEY6 FROM zx_lines_det_factors zd where zd.tax_event_class_code IN ('SALES_TRANSACTION','PURCHASE_TRANSACTION') AND (zd.application_id = 235) UNION SELECT DISTINCT zd.application_id , zd.entity_code, zd.event_class_code, zd.EVENT_TYPE_CODE, zd.trx_id, zd.trx_date, zd.trx_number, zd.tax_event_class_code, zd.HDR_GL_DATE ACCOUNTING_DATE, zd.BATCH_SOURCE_ID, zd.BATCH_SOURCE_NAME, zd.BATCH_SOURCE_SEQ_ID, zd.DOC_SEQ_ID, zd.DOC_SEQ_NAME, zd.DOC_SEQ_VALUE, zd.DOCUMENT_TYPE, zd.FIRST_PTY_ORG_ID, zd.FIRST_PTY_REG_ID, zd.INTERNAL_ORGANIZATION_ID, zd.LEDGER_ID, zd.LEGAL_ENTITY_ID, zd.RECEIVABLES_TRX_TYPE_ID, zd.RECEIVABLES_TRX_TYPE_SEQ_ID, zd.THIRD_PTY_REG_ID, zd.BILL_THIRD_PTY_ACCT_ID, zd.SHIP_THIRD_PTY_ACCT_ID, zd.BILL_THIRD_PTY_ACCT_SITE_ID, zd.SHIP_THIRD_PTY_ACCT_SITE_ID, (select CASE WHEN supplier_flag = 'Y' AND customer_flag = 'Y' AND zd.TAX_EVENT_CLASS_CODE ='PURCHASE_TRANSACTION' THEN 'S' WHEN supplier_flag = 'Y' AND customer_flag = 'Y' AND zd.TAX_EVENT_CLASS_CODE ='SALES_TRANSACTION' THEN 'C' WHEN supplier_flag = 'Y' THEN 'S' WHEN customer_flag = 'Y' THEN 'C' ELSE '' END from zx_party_tax_profile where ZX_PARTY_TAX_PROFILE.PARTY_TAX_PROFILE_ID = coalesce (zd.BILL_FROM_SITE_TAX_PROF_ID,zd.BILL_TO_SITE_TAX_PROF_ID,zd.SHIP_FROM_SITE_TAX_PROF_ID,zd.SHIP_TO_SITE_TAX_PROF_ID,zd.BILL_FROM_PARTY_TAX_PROF_ID,zd.BILL_TO_PARTY_TAX_PROF_ID,zd.SHIP_FROM_PARTY_TAX_PROF_ID,zd.SHIP_TO_PARTY_TAX_PROF_ID)) as PARTY_TYPE, zd.TRX_CURRENCY_CODE, zd.TRX_DESCRIPTION, zd.TRX_DOC_REVISION, zd.TRX_DUE_DATE, zd.TRX_SOURCE, zd.TRX_TYPE_DESCRIPTION, zd.hdr_trx_user_key1, zd.HDR_TRX_USER_KEY2, zd.HDR_TRX_USER_KEY3, zd.HDR_TRX_USER_KEY4, zd.HDR_TRX_USER_KEY5, zd.HDR_TRX_USER_KEY6 FROM zx_lines_det_factors zd where zd.tax_event_class_code IN ('PURCHASE_TRANSACTION') AND zd.ENTITY_CODE = 'AP_INVOICES' AND zd.application_id = 200 AND EXISTS (SELECT 1 FROM ZX_REC_NREC_DIST ZxRecNrecDist WHERE zd.APPLICATION_ID = ZxRecNrecDist.APPLICATION_ID AND zd.ENTITY_CODE = ZxRecNrecDist.ENTITY_CODE AND zd.EVENT_CLASS_CODE = ZxRecNrecDist.EVENT_CLASS_CODE AND zd.TRX_ID = ZxRecNrecDist.TRX_ID AND zd.TRX_LINE_ID = ZxRecNrecDist.TRX_LINE_ID AND ZxRecNrecDist.ACCOUNTING_EVENT_ID IS NOT NULL AND ZxRecNrecDist.RECOVERABLE_FLAG = 'Y' AND zxRecNrecDist.PROCESS_FINAL_RECOVERY_CODE = 'A' )