JA_CN_VAT_INV_HEADERS

Details

Schema: FUSION
Object owner: JA
Object type: VIEW

Columns

Name
VAT_TRX_HEADER_ID CONSOLIDATION_FLAG INVOICE_NUMBER INVOICE_CODE INVOICE_TYPE CUSTOMER_NAME INVOICE_DATE INVOICE_NET_AMOUNT INVOICE_TAX_AMOUNT INVOICE_TOTAL_AMOUNT TP_TAX_REGISTRATION_NUMBER CUSTOMER_ADDRESS_PHONE BANK_ACCOUNT_NAME_NUMBER STATUS ORG_ID FP_TAX_REGISTRATION_NUMBER GTA_TRX_NUMBER INVOICE_TAX_RATE GTA_TRX_HEADER_ID GTA_TRX_NUMBER_GTA RA_TRX_NUMBER RA_TRX_ID TRANSACTION_DATE RA_TRX_SOURCE_SEQ_ID BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_NAME BILL_TO_CUSTOMER_NUMBER RA_TRX_CLASS GTA_INVOICE_TYPE GL_PERIOD GL_DATE GTA_TP_TAX_REG_NUM GTA_BANK_ACC_NAME_NUM GTA_TAX_RATE VAT_BATCH_NUMBER GTA_STATUS RA_TRX_BATCH_ID DESCRIPTION

Name

VAT_TRX_HEADER_ID

CONSOLIDATION_FLAG

INVOICE_NUMBER

INVOICE_CODE

INVOICE_TYPE

CUSTOMER_NAME

INVOICE_DATE

INVOICE_NET_AMOUNT

INVOICE_TAX_AMOUNT

INVOICE_TOTAL_AMOUNT

TP_TAX_REGISTRATION_NUMBER

CUSTOMER_ADDRESS_PHONE

BANK_ACCOUNT_NAME_NUMBER

STATUS

ORG_ID

FP_TAX_REGISTRATION_NUMBER

GTA_TRX_NUMBER

INVOICE_TAX_RATE

GTA_TRX_HEADER_ID

GTA_TRX_NUMBER_GTA

RA_TRX_NUMBER

RA_TRX_ID

TRANSACTION_DATE

RA_TRX_SOURCE_SEQ_ID

BILL_TO_CUSTOMER_ID

BILL_TO_CUSTOMER_NAME

BILL_TO_CUSTOMER_NUMBER

RA_TRX_CLASS

GTA_INVOICE_TYPE

GL_PERIOD

GL_DATE

GTA_TP_TAX_REG_NUM

GTA_BANK_ACC_NAME_NUM

GTA_TAX_RATE

VAT_BATCH_NUMBER

GTA_STATUS

RA_TRX_BATCH_ID

DESCRIPTION

Query

SQL_Statement
SELECT VAT_TRX_HEADER_ID, CONSOLIDATION_FLAG, INVOICE_NUMBER, INVOICE_CODE, INVOICE_TYPE, CUSTOMER_NAME, INVOICE_DATE, INVOICE_NET_AMOUNT, INVOICE_TAX_AMOUNT, INVOICE_TOTAL_AMOUNT, TP_TAX_REGISTRATION_NUMBER, CUSTOMER_ADDRESS_PHONE, BANK_ACCOUNT_NAME_NUMBER, STATUS, ORG_ID, FP_TAX_REGISTRATION_NUMBER, GTA_TRX_NUMBER, INVOICE_TAX_RATE, GTA_TRX_HEADER_ID, GTA_TRX_NUMBER_GTA, RA_TRX_NUMBER, RA_TRX_ID, TRANSACTION_DATE, RA_TRX_SOURCE_SEQ_ID, BILL_TO_CUSTOMER_ID, BILL_TO_CUSTOMER_NAME, BILL_TO_CUSTOMER_NUMBER, RA_TRX_CLASS, GTA_INVOICE_TYPE, Gl_Period, Gl_Date, GTA_TP_TAX_REG_NUM, GTA_BANK_ACC_NAME_NUM, GTA_TAX_RATE, VAT_BATCH_NUMBER, GTA_STATUS, RA_TRX_BATCH_ID, DESCRIPTION FROM (SELECT vat_header.VAT_TRX_HEADER_ID, trx_header.CONSOLIDATION_FLAG, vat_header.INVOICE_NUMBER, vat_header.INVOICE_CODE, vat_header.INVOICE_TYPE, vat_header.CUSTOMER_NAME, vat_header.INVOICE_DATE, vat_header.INVOICE_NET_AMOUNT, vat_header.INVOICE_TAX_AMOUNT, (decode(vat_header.INVOICE_NET_AMOUNT, NULL, 0, vat_header.INVOICE_NET_AMOUNT) + decode(vat_header.INVOICE_TAX_AMOUNT, NULL, 0, vat_header.INVOICE_TAX_AMOUNT)) INVOICE_TOTAL_AMOUNT, vat_header.TP_TAX_REGISTRATION_NUMBER, vat_header.CUSTOMER_ADDRESS_PHONE, vat_header.BANK_ACCOUNT_NAME_NUMBER, vat_header.STATUS, vat_header.ORG_ID, vat_header.FP_TAX_REGISTRATION_NUMBER, vat_header.GTA_TRX_NUMBER, vat_header.TAX_RATE INVOICE_TAX_RATE, trx_header.gta_trx_header_id, trx_header.GTA_TRX_NUMBER GTA_TRX_NUMBER_GTA, trx_header.RA_TRX_NUMBER, trx_header.RA_TRX_ID, trx_header.TRANSACTION_DATE, trx_header.RA_TRX_SOURCE_SEQ_ID, trx_header.BILL_TO_CUSTOMER_ID, trx_header.BILL_TO_CUSTOMER_NAME, trx_header.BILL_TO_CUSTOMER_NUMBER, trx_header.RA_TRX_CLASS, trx_header.INVOICE_TYPE GTA_INVOICE_TYPE, trx_header.gl_period, trx_header.gl_date, trx_header.TP_TAX_REGISTRATION_NUMBER GTA_TP_TAX_REG_NUM, trx_header.BANK_ACCOUNT_NAME \|\| decode(trx_header.BANK_ACCOUNT_NUMBER, null,'', ' ' \|\| trx_header.BANK_ACCOUNT_NUMBER) GTA_BANK_ACC_NAME_NUM, trx_header.TAX_RATE GTA_TAX_RATE, trx_header.VAT_BATCH_NUMBER, trx_header.STATUS GTA_STATUS, trx_header.ra_trx_batch_id, vat_header.DESCRIPTION FROM JA_CN_TRX_HEADERS_ALL trx_header, JA_CN_vat_headers_all vat_header WHERE trx_header.GTA_TRX_NUMBER = vat_header.GTA_TRX_NUMBER UNION SELECT vat_header.VAT_TRX_HEADER_ID, trx_child_header.CONSOLIDATION_FLAG, vat_header.INVOICE_NUMBER, vat_header.INVOICE_CODE, vat_header.INVOICE_TYPE, vat_header.CUSTOMER_NAME, vat_header.INVOICE_DATE, vat_header.INVOICE_NET_AMOUNT, vat_header.INVOICE_TAX_AMOUNT, (decode(vat_header.INVOICE_NET_AMOUNT, NULL, 0, vat_header.INVOICE_NET_AMOUNT) + decode(vat_header.INVOICE_TAX_AMOUNT, NULL, 0, vat_header.INVOICE_TAX_AMOUNT)) INVOICE_TOTAL_AMOUNT, vat_header.TP_TAX_REGISTRATION_NUMBER, vat_header.CUSTOMER_ADDRESS_PHONE, vat_header.BANK_ACCOUNT_NAME_NUMBER, vat_header.STATUS, vat_header.ORG_ID, vat_header.FP_TAX_REGISTRATION_NUMBER, vat_header.GTA_TRX_NUMBER, vat_header.TAX_RATE INVOICE_TAX_RATE, trx_child_header.gta_trx_header_id, trx_child_header.GTA_TRX_NUMBER GTA_TRX_NUMBER_GTA, trx_child_header.RA_TRX_NUMBER, trx_child_header.RA_TRX_ID, trx_child_header.TRANSACTION_DATE, trx_child_header.RA_TRX_SOURCE_SEQ_ID, trx_child_header.BILL_TO_CUSTOMER_ID, trx_child_header.BILL_TO_CUSTOMER_NAME, trx_child_header.BILL_TO_CUSTOMER_NUMBER, trx_child_header.RA_TRX_CLASS, trx_child_header.INVOICE_TYPE GTA_INVOICE_TYPE, trx_child_header.gl_period, trx_child_header.gl_date, trx_child_header.TP_TAX_REGISTRATION_NUMBER GTA_TP_TAX_REG_NUM, trx_child_header.BANK_ACCOUNT_NAME \|\| decode(trx_child_header.BANK_ACCOUNT_NUMBER, null,'', ' ' \|\| trx_child_header.BANK_ACCOUNT_NUMBER) GTA_BANK_ACC_NAME_NUM, trx_child_header.TAX_RATE GTA_TAX_RATE, trx_child_header.VAT_BATCH_NUMBER, trx_child_header.STATUS GTA_STATUS, trx_child_header.ra_trx_batch_id, vat_header.DESCRIPTION FROM JA_CN_TRX_HEADERS_ALL trx_parent_header, JA_CN_TRX_HEADERS_ALL trx_child_header, JA_CN_vat_headers_all vat_header WHERE trx_parent_header.GTA_TRX_NUMBER = vat_header.GTA_TRX_NUMBER AND trx_parent_header.GTA_TRX_HEADER_ID = trx_child_header.CONSOLI_PARENT_TRX_HDR_ID)

SQL_Statement

SELECT VAT_TRX_HEADER_ID,

CONSOLIDATION_FLAG,

INVOICE_NUMBER,

INVOICE_CODE,

INVOICE_TYPE,

CUSTOMER_NAME,

INVOICE_DATE,

INVOICE_NET_AMOUNT,

INVOICE_TAX_AMOUNT,

INVOICE_TOTAL_AMOUNT,

TP_TAX_REGISTRATION_NUMBER,

CUSTOMER_ADDRESS_PHONE,

BANK_ACCOUNT_NAME_NUMBER,

STATUS,

ORG_ID,

FP_TAX_REGISTRATION_NUMBER,

GTA_TRX_NUMBER,

INVOICE_TAX_RATE,

GTA_TRX_HEADER_ID,

GTA_TRX_NUMBER_GTA,

RA_TRX_NUMBER,

RA_TRX_ID,

TRANSACTION_DATE,

RA_TRX_SOURCE_SEQ_ID,

BILL_TO_CUSTOMER_ID,

BILL_TO_CUSTOMER_NAME,

BILL_TO_CUSTOMER_NUMBER,

RA_TRX_CLASS,

GTA_INVOICE_TYPE,

Gl_Period,

Gl_Date,

GTA_TP_TAX_REG_NUM,

GTA_BANK_ACC_NAME_NUM,

GTA_TAX_RATE,

VAT_BATCH_NUMBER,

GTA_STATUS,

RA_TRX_BATCH_ID,

DESCRIPTION

FROM (SELECT vat_header.VAT_TRX_HEADER_ID,

trx_header.CONSOLIDATION_FLAG,

vat_header.INVOICE_NUMBER,

vat_header.INVOICE_CODE,

vat_header.INVOICE_TYPE,

vat_header.CUSTOMER_NAME,

vat_header.INVOICE_DATE,

vat_header.INVOICE_NET_AMOUNT,

vat_header.INVOICE_TAX_AMOUNT,

(decode(vat_header.INVOICE_NET_AMOUNT,

NULL,

0,

vat_header.INVOICE_NET_AMOUNT) +

decode(vat_header.INVOICE_TAX_AMOUNT,

NULL,

0,

vat_header.INVOICE_TAX_AMOUNT)) INVOICE_TOTAL_AMOUNT,

vat_header.TP_TAX_REGISTRATION_NUMBER,

vat_header.CUSTOMER_ADDRESS_PHONE,

vat_header.BANK_ACCOUNT_NAME_NUMBER,

vat_header.STATUS,

vat_header.ORG_ID,

vat_header.FP_TAX_REGISTRATION_NUMBER,

vat_header.GTA_TRX_NUMBER,

vat_header.TAX_RATE INVOICE_TAX_RATE,

trx_header.gta_trx_header_id,

trx_header.GTA_TRX_NUMBER GTA_TRX_NUMBER_GTA,

trx_header.RA_TRX_NUMBER,

trx_header.RA_TRX_ID,

trx_header.TRANSACTION_DATE,

trx_header.RA_TRX_SOURCE_SEQ_ID,

trx_header.BILL_TO_CUSTOMER_ID,

trx_header.BILL_TO_CUSTOMER_NAME,

trx_header.BILL_TO_CUSTOMER_NUMBER,

trx_header.RA_TRX_CLASS,

trx_header.INVOICE_TYPE GTA_INVOICE_TYPE,

trx_header.gl_period,

trx_header.gl_date,

trx_header.TP_TAX_REGISTRATION_NUMBER GTA_TP_TAX_REG_NUM,

trx_header.BANK_ACCOUNT_NAME || decode(trx_header.BANK_ACCOUNT_NUMBER, null,'', ' ' || trx_header.BANK_ACCOUNT_NUMBER) GTA_BANK_ACC_NAME_NUM,

trx_header.TAX_RATE GTA_TAX_RATE,

trx_header.VAT_BATCH_NUMBER,

trx_header.STATUS GTA_STATUS,

trx_header.ra_trx_batch_id,

vat_header.DESCRIPTION

FROM JA_CN_TRX_HEADERS_ALL trx_header,

JA_CN_vat_headers_all vat_header

WHERE trx_header.GTA_TRX_NUMBER = vat_header.GTA_TRX_NUMBER

UNION

SELECT vat_header.VAT_TRX_HEADER_ID,

trx_child_header.CONSOLIDATION_FLAG,

vat_header.INVOICE_NUMBER,

vat_header.INVOICE_CODE,

vat_header.INVOICE_TYPE,

vat_header.CUSTOMER_NAME,

vat_header.INVOICE_DATE,

vat_header.INVOICE_NET_AMOUNT,

vat_header.INVOICE_TAX_AMOUNT,

(decode(vat_header.INVOICE_NET_AMOUNT,

NULL,

0,

vat_header.INVOICE_NET_AMOUNT) +

decode(vat_header.INVOICE_TAX_AMOUNT,

NULL,

0,

vat_header.INVOICE_TAX_AMOUNT)) INVOICE_TOTAL_AMOUNT,

vat_header.TP_TAX_REGISTRATION_NUMBER,

vat_header.CUSTOMER_ADDRESS_PHONE,

vat_header.BANK_ACCOUNT_NAME_NUMBER,

vat_header.STATUS,

vat_header.ORG_ID,

vat_header.FP_TAX_REGISTRATION_NUMBER,

vat_header.GTA_TRX_NUMBER,

vat_header.TAX_RATE INVOICE_TAX_RATE,

trx_child_header.gta_trx_header_id,

trx_child_header.GTA_TRX_NUMBER GTA_TRX_NUMBER_GTA,

trx_child_header.RA_TRX_NUMBER,

trx_child_header.RA_TRX_ID,

trx_child_header.TRANSACTION_DATE,

trx_child_header.RA_TRX_SOURCE_SEQ_ID,

trx_child_header.BILL_TO_CUSTOMER_ID,

trx_child_header.BILL_TO_CUSTOMER_NAME,

trx_child_header.BILL_TO_CUSTOMER_NUMBER,

trx_child_header.RA_TRX_CLASS,

trx_child_header.INVOICE_TYPE GTA_INVOICE_TYPE,

trx_child_header.gl_period,

trx_child_header.gl_date,

trx_child_header.TP_TAX_REGISTRATION_NUMBER GTA_TP_TAX_REG_NUM,

trx_child_header.BANK_ACCOUNT_NAME || decode(trx_child_header.BANK_ACCOUNT_NUMBER, null,'', ' ' || trx_child_header.BANK_ACCOUNT_NUMBER) GTA_BANK_ACC_NAME_NUM,

trx_child_header.TAX_RATE GTA_TAX_RATE,

trx_child_header.VAT_BATCH_NUMBER,

trx_child_header.STATUS GTA_STATUS,

trx_child_header.ra_trx_batch_id,

vat_header.DESCRIPTION

FROM JA_CN_TRX_HEADERS_ALL trx_parent_header,

JA_CN_TRX_HEADERS_ALL trx_child_header,

JA_CN_vat_headers_all vat_header

WHERE trx_parent_header.GTA_TRX_NUMBER = vat_header.GTA_TRX_NUMBER

AND trx_parent_header.GTA_TRX_HEADER_ID =

trx_child_header.CONSOLI_PARENT_TRX_HDR_ID)