PJB_XLA_INV_HEAD_REF_V

Details

• Schema: FUSION

• Object owner: PJB

• Object type: VIEW

Columns

Name

INVOICE_ID CONTRACT_ID CREDITED_INVOICE_ID INVOICE_NUM INVOICE_TYPE_CODE BILLING_TYPE_CODE INVOICE_CREDIT_FLAG INVOICE_WRITE_OFF_FLAG INVOICE_CONCESSION_FLAG PROJECT_NULL_CCID INV_HEAD_ATTRIBUTE_CATEGORY INVOICE_HEADER_ATTRIBUTE1 INVOICE_HEADER_ATTRIBUTE2 INVOICE_HEADER_ATTRIBUTE3 INVOICE_HEADER_ATTRIBUTE4 INVOICE_HEADER_ATTRIBUTE5 INVOICE_HEADER_ATTRIBUTE6 INVOICE_HEADER_ATTRIBUTE7 INVOICE_HEADER_ATTRIBUTE8 INVOICE_HEADER_ATTRIBUTE9 INVOICE_HEADER_ATTRIBUTE10 INVOICE_HEADER_ATTRIBUTE11 INVOICE_HEADER_ATTRIBUTE12 INVOICE_HEADER_ATTRIBUTE13 INVOICE_HEADER_ATTRIBUTE14 INVOICE_HEADER_ATTRIBUTE15 INV_RA_CUST_TRX_ID PROJECT_ID PROJECT_ACCOUNTING_DATE PROJECT_ACCOUNTING_PERIOD

Query

SQL_Statement

SELECT InvHead.INVOICE_ID INVOICE_ID, InvHead.CONTRACT_ID CONTRACT_ID, InvHead.CREDITED_INVOICE_ID CREDITED_INVOICE_ID, InvHead.INVOICE_NUM INVOICE_NUM, InvHead.INVOICE_TYPE_CODE INVOICE_TYPE_CODE, InvHead.BILLING_TYPE_CODE BILLING_TYPE_CODE , decode(InvHead.CREDITED_INVOICE_ID ,null,'N','Y') INVOICE_CREDIT_FLAG, decode(InvHead.INVOICE_TYPE_CODE ,'WRITE_OFF','Y','N') INVOICE_WRITE_OFF_FLAG, decode(InvHead.INVOICE_TYPE_CODE ,'CONCESSION','Y','N') INVOICE_CONCESSION_FLAG, to_number(NULL) PROJECT_NULL_CCID, InvHead.ATTRIBUTE_CATEGORY INV_HEAD_ATTRIBUTE_CATEGORY, InvHead.ATTRIBUTE1 INVOICE_HEADER_ATTRIBUTE1, InvHead.ATTRIBUTE2 INVOICE_HEADER_ATTRIBUTE2, InvHead.ATTRIBUTE3 INVOICE_HEADER_ATTRIBUTE3, InvHead.ATTRIBUTE4 INVOICE_HEADER_ATTRIBUTE4, InvHead.ATTRIBUTE5 INVOICE_HEADER_ATTRIBUTE5, InvHead.ATTRIBUTE6 INVOICE_HEADER_ATTRIBUTE6, InvHead.ATTRIBUTE7 INVOICE_HEADER_ATTRIBUTE7, InvHead.ATTRIBUTE8 INVOICE_HEADER_ATTRIBUTE8, InvHead.ATTRIBUTE9 INVOICE_HEADER_ATTRIBUTE9, InvHead.ATTRIBUTE10 INVOICE_HEADER_ATTRIBUTE10, InvHead.ATTRIBUTE11 INVOICE_HEADER_ATTRIBUTE11, InvHead.ATTRIBUTE12 INVOICE_HEADER_ATTRIBUTE12, InvHead.ATTRIBUTE13 INVOICE_HEADER_ATTRIBUTE13, InvHead.ATTRIBUTE14 INVOICE_HEADER_ATTRIBUTE14, InvHead.ATTRIBUTE15 INVOICE_HEADER_ATTRIBUTE15, TrxHead.CUSTOMER_TRX_ID INV_RA_CUST_TRX_ID, InvHead.PROJECT_ID PROJECT_ID, InvHead.PA_DATE PROJECT_ACCOUNTING_DATE, InvHead.PA_PERIOD_NAME PROJECT_ACCOUNTING_PERIOD FROM OKC_K_HEADERS_ALL_B ContHead, OKC_CONTRACT_TYPES_B ContType, PJB_INVOICE_HEADERS InvHead, RA_CUSTOMER_TRX_ALL TrxHead WHERE ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND InvHead.CONTRACT_ID = ContHead.ID AND ContHead.TEMPLATE_YN = 'N' AND InvHead.CONTRACT_ID = TO_NUMBER(DECODE(RTRIM(TRANSLATE(TrxHead.INTERFACE_HEADER_ATTRIBUTE2,'0123456789',' ')), NULL,TrxHead.INTERFACE_HEADER_ATTRIBUTE2,-99999) ) AND InvHead.INVOICE_NUM = TO_NUMBER(DECODE(RTRIM(TRANSLATE(TrxHead.INTERFACE_HEADER_ATTRIBUTE3,'0123456789',' ')), NULL, TrxHead.INTERFACE_HEADER_ATTRIBUTE3,-99999)) AND InvHead.RA_INVOICE_NUMBER = DECODE(InvHead.invoice_status_code,'ACCEPTED',TrxHead.TRX_NUMBER,NVL(TrxHead.OLD_TRX_NUMBER, TrxHead.TRX_NUMBER)) UNION ALL SELECT InvHead.INVOICE_ID INVOICE_ID, InvHead.CONTRACT_ID CONTRACT_ID, InvHead.CREDITED_INVOICE_ID CREDITED_INVOICE_ID, InvHead.INVOICE_NUM INVOICE_NUM, InvHead.INVOICE_TYPE_CODE INVOICE_TYPE_CODE, InvHead.BILLING_TYPE_CODE BILLING_TYPE_CODE , decode(InvHead.CREDITED_INVOICE_ID ,null,'N','Y') INVOICE_CREDIT_FLAG, decode(InvHead.INVOICE_TYPE_CODE ,'WRITE_OFF','Y','N') INVOICE_WRITE_OFF_FLAG, decode(InvHead.INVOICE_TYPE_CODE ,'CONCESSION','Y','N') INVOICE_CONCESSION_FLAG, to_number(NULL) PROJECT_NULL_CCID, InvHead.ATTRIBUTE_CATEGORY INV_HEAD_ATTRIBUTE_CATEGORY, InvHead.ATTRIBUTE1 INVOICE_HEADER_ATTRIBUTE1, InvHead.ATTRIBUTE2 INVOICE_HEADER_ATTRIBUTE2, InvHead.ATTRIBUTE3 INVOICE_HEADER_ATTRIBUTE3, InvHead.ATTRIBUTE4 INVOICE_HEADER_ATTRIBUTE4, InvHead.ATTRIBUTE5 INVOICE_HEADER_ATTRIBUTE5, InvHead.ATTRIBUTE6 INVOICE_HEADER_ATTRIBUTE6, InvHead.ATTRIBUTE7 INVOICE_HEADER_ATTRIBUTE7, InvHead.ATTRIBUTE8 INVOICE_HEADER_ATTRIBUTE8, InvHead.ATTRIBUTE9 INVOICE_HEADER_ATTRIBUTE9, InvHead.ATTRIBUTE10 INVOICE_HEADER_ATTRIBUTE10, InvHead.ATTRIBUTE11 INVOICE_HEADER_ATTRIBUTE11, InvHead.ATTRIBUTE12 INVOICE_HEADER_ATTRIBUTE12, InvHead.ATTRIBUTE13 INVOICE_HEADER_ATTRIBUTE13, InvHead.ATTRIBUTE14 INVOICE_HEADER_ATTRIBUTE14, InvHead.ATTRIBUTE15 INVOICE_HEADER_ATTRIBUTE15, TrxHead.CUSTOMER_TRX_ID INV_RA_CUST_TRX_ID, InvHead.PROJECT_ID PROJECT_ID, InvHead.PA_DATE PROJECT_ACCOUNTING_DATE, InvHead.PA_PERIOD_NAME PROJECT_ACCOUNTING_PERIOD FROM OKC_K_HEADERS_ALL_B ContHead, OKC_CONTRACT_TYPES_B ContType, PJB_INVOICE_HEADERS InvHead, RA_CUSTOMER_TRX_ALL TrxHead, OKC_BU_IMPL_OPTIONS_ALL BU WHERE ContHead.VERSION_TYPE= 'C' AND ContHead.STS_CODE ='UNDER_AMENDMENT' AND ContHead.under_amend_version_flag ='N' AND ContHead.org_id = BU.org_id AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT' AND ContHead.CONTRACT_TYPE_ID = ContType.CONTRACT_TYPE_ID AND InvHead.CONTRACT_ID = ContHead.ID AND ContHead.TEMPLATE_YN = 'N' AND InvHead.CONTRACT_ID = TO_NUMBER(DECODE(RTRIM(TRANSLATE(TrxHead.INTERFACE_HEADER_ATTRIBUTE2,'0123456789',' ')), NULL,TrxHead.INTERFACE_HEADER_ATTRIBUTE2,-99999) ) AND InvHead.INVOICE_NUM = TO_NUMBER(DECODE(RTRIM(TRANSLATE(TrxHead.INTERFACE_HEADER_ATTRIBUTE3,'0123456789',' ')), NULL, TrxHead.INTERFACE_HEADER_ATTRIBUTE3,-99999)) AND InvHead.RA_INVOICE_NUMBER = DECODE(InvHead.invoice_status_code,'ACCEPTED',TrxHead.TRX_NUMBER,NVL(TrxHead.OLD_TRX_NUMBER, TrxHead.TRX_NUMBER))