PJB_XLA_AR_CONT_REF_V
Details
-
Schema: FUSION
-
Object owner: PJB
-
Object type: VIEW
Columns
Name |
---|
CONTRACT_ID CONTRACT_NAME MAJOR_VERSION CONTRACT_NUMBER CONTRACT_LEGAL_ENTITY_ID CONTRACT_ORGANIZATION_ID CONTRACT_ORGANIZATION_NAME CONTRACT_ORG_COMPANY CONTRACT_ORG_COST_CENTER CONTRACT_TYPE_ID CONTRACT_TYPE CONTRACT_TYPE_IP_FLAG CONTRACT_TYPE_IC_FLAG CONTRACT_NET_INVOICE_FLAG CONTRACT_OWNING_ORG_ID CONTRACT_OWN_ORG_COMPANY CONTRACT_OWN_ORG_COST_CENTER CONTRACT_PRIMARY_PARTY_ID CONTRACT_ATTRIBUTE_CATEGORY CONTRACT_HEADER_ATTRIBUTE1 CONTRACT_HEADER_ATTRIBUTE2 CONTRACT_HEADER_ATTRIBUTE3 CONTRACT_HEADER_ATTRIBUTE4 CONTRACT_HEADER_ATTRIBUTE5 CONTRACT_HEADER_ATTRIBUTE6 CONTRACT_HEADER_ATTRIBUTE7 CONTRACT_HEADER_ATTRIBUTE8 CONTRACT_HEADER_ATTRIBUTE9 CONTRACT_HEADER_ATTRIBUTE10 CONTRACT_HEADER_ATTRIBUTE11 CONTRACT_HEADER_ATTRIBUTE12 CONTRACT_HEADER_ATTRIBUTE13 CONTRACT_HEADER_ATTRIBUTE14 CONTRACT_HEADER_ATTRIBUTE15 CONTRACT_HEADER_ATTRIBUTE16 CONTRACT_HEADER_ATTRIBUTE17 CONTRACT_HEADER_ATTRIBUTE18 CONTRACT_HEADER_ATTRIBUTE19 CONTRACT_HEADER_ATTRIBUTE20 CONTRACT_HEADER_ATTRIBUTE21 CONTRACT_HEADER_ATTRIBUTE22 CONTRACT_HEADER_ATTRIBUTE23 CONTRACT_HEADER_ATTRIBUTE24 CONTRACT_HEADER_ATTRIBUTE25 CONTRACT_HEADER_ATTRIBUTE26 CONTRACT_HEADER_ATTRIBUTE27 CONTRACT_HEADER_ATTRIBUTE28 CONTRACT_HEADER_ATTRIBUTE29 CONTRACT_HEADER_ATTRIBUTE30 CONT_RA_CUST_TRX_ID |
Query
SQL_Statement |
---|
SELECT ContHead.ID CONTRACT_ID, ContHead.ID CONTRACT_NAME, ContHead.MAJOR_VERSION MAJOR_VERSION, ContHead.CONTRACT_NUMBER CONTRACT_NUMBER, ContHead.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID, ContHead.ORG_ID CONTRACT_ORGANIZATION_ID, ContHead.ORG_ID CONTRACT_ORGANIZATION_NAME, pjc_accounting_pvt.get_company_info(ContHead.ORG_ID) CONTRACT_ORG_COMPANY, pjc_accounting_pvt.get_costcenter_info(ContHead.ORG_ID) CONTRACT_ORG_COST_CENTER, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE, ContType.INTER_PROJECT_FLAG CONTRACT_TYPE_IP_FLAG, ContType.INTER_COMPANY_FLAG CONTRACT_TYPE_IC_FLAG, contHead.NET_INVOICE_FLAG CONTRACT_NET_INVOICE_FLAG, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_ID, pjc_accounting_pvt.get_company_info(ContHead.OWNING_ORG_ID) CONTRACT_OWN_ORG_COMPANY, pjc_accounting_pvt.get_costcenter_info(ContHead.OWNING_ORG_ID) CONTRACT_OWN_ORG_COST_CENTER, ContHead.PRIMARY_ENT_PARTY_ID CONTRACT_PRIMARY_PARTY_ID, ContHead.ATTRIBUTE_CATEGORY CONTRACT_ATTRIBUTE_CATEGORY, ContHead.ATTRIBUTE1 CONTRACT_HEADER_ATTRIBUTE1, ContHead.ATTRIBUTE2 CONTRACT_HEADER_ATTRIBUTE2, ContHead.ATTRIBUTE3 CONTRACT_HEADER_ATTRIBUTE3, ContHead.ATTRIBUTE4 CONTRACT_HEADER_ATTRIBUTE4, ContHead.ATTRIBUTE5 CONTRACT_HEADER_ATTRIBUTE5, ContHead.ATTRIBUTE6 CONTRACT_HEADER_ATTRIBUTE6, ContHead.ATTRIBUTE7 CONTRACT_HEADER_ATTRIBUTE7, ContHead.ATTRIBUTE8 CONTRACT_HEADER_ATTRIBUTE8, ContHead.ATTRIBUTE9 CONTRACT_HEADER_ATTRIBUTE9, ContHead.ATTRIBUTE10 CONTRACT_HEADER_ATTRIBUTE10, ContHead.ATTRIBUTE11 CONTRACT_HEADER_ATTRIBUTE11, ContHead.ATTRIBUTE12 CONTRACT_HEADER_ATTRIBUTE12, ContHead.ATTRIBUTE13 CONTRACT_HEADER_ATTRIBUTE13, ContHead.ATTRIBUTE14 CONTRACT_HEADER_ATTRIBUTE14, ContHead.ATTRIBUTE15 CONTRACT_HEADER_ATTRIBUTE15, ContHead.ATTRIBUTE16 CONTRACT_HEADER_ATTRIBUTE16, ContHead.ATTRIBUTE17 CONTRACT_HEADER_ATTRIBUTE17, ContHead.ATTRIBUTE18 CONTRACT_HEADER_ATTRIBUTE18, ContHead.ATTRIBUTE19 CONTRACT_HEADER_ATTRIBUTE19, ContHead.ATTRIBUTE20 CONTRACT_HEADER_ATTRIBUTE20, ContHead.ATTRIBUTE21 CONTRACT_HEADER_ATTRIBUTE21, ContHead.ATTRIBUTE22 CONTRACT_HEADER_ATTRIBUTE22, ContHead.ATTRIBUTE23 CONTRACT_HEADER_ATTRIBUTE23, ContHead.ATTRIBUTE24 CONTRACT_HEADER_ATTRIBUTE24, ContHead.ATTRIBUTE25 CONTRACT_HEADER_ATTRIBUTE25, ContHead.ATTRIBUTE26 CONTRACT_HEADER_ATTRIBUTE26, ContHead.ATTRIBUTE27 CONTRACT_HEADER_ATTRIBUTE27, ContHead.ATTRIBUTE28 CONTRACT_HEADER_ATTRIBUTE28, ContHead.ATTRIBUTE29 CONTRACT_HEADER_ATTRIBUTE29, ContHead.ATTRIBUTE30 CONTRACT_HEADER_ATTRIBUTE30, TrxHead.CUSTOMER_TRX_ID CONT_RA_CUST_TRX_ID 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 ContHead.ID CONTRACT_ID, ContHead.ID CONTRACT_NAME, ContHead.MAJOR_VERSION MAJOR_VERSION, ContHead.CONTRACT_NUMBER CONTRACT_NUMBER, ContHead.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID, ContHead.ORG_ID CONTRACT_ORGANIZATION_ID, ContHead.ORG_ID CONTRACT_ORGANIZATION_NAME, pjc_accounting_pvt.get_company_info(ContHead.ORG_ID) CONTRACT_ORG_COMPANY, pjc_accounting_pvt.get_costcenter_info(ContHead.ORG_ID) CONTRACT_ORG_COST_CENTER, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE_ID, ContHead.CONTRACT_TYPE_ID CONTRACT_TYPE, ContType.INTER_PROJECT_FLAG CONTRACT_TYPE_IP_FLAG, ContType.INTER_COMPANY_FLAG CONTRACT_TYPE_IC_FLAG, contHead.NET_INVOICE_FLAG CONTRACT_NET_INVOICE_FLAG, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_ID, pjc_accounting_pvt.get_company_info(ContHead.OWNING_ORG_ID) CONTRACT_OWN_ORG_COMPANY, pjc_accounting_pvt.get_costcenter_info(ContHead.OWNING_ORG_ID) CONTRACT_OWN_ORG_COST_CENTER, ContHead.PRIMARY_ENT_PARTY_ID CONTRACT_PRIMARY_PARTY_ID, ContHead.ATTRIBUTE_CATEGORY CONTRACT_ATTRIBUTE_CATEGORY, ContHead.ATTRIBUTE1 CONTRACT_HEADER_ATTRIBUTE1, ContHead.ATTRIBUTE2 CONTRACT_HEADER_ATTRIBUTE2, ContHead.ATTRIBUTE3 CONTRACT_HEADER_ATTRIBUTE3, ContHead.ATTRIBUTE4 CONTRACT_HEADER_ATTRIBUTE4, ContHead.ATTRIBUTE5 CONTRACT_HEADER_ATTRIBUTE5, ContHead.ATTRIBUTE6 CONTRACT_HEADER_ATTRIBUTE6, ContHead.ATTRIBUTE7 CONTRACT_HEADER_ATTRIBUTE7, ContHead.ATTRIBUTE8 CONTRACT_HEADER_ATTRIBUTE8, ContHead.ATTRIBUTE9 CONTRACT_HEADER_ATTRIBUTE9, ContHead.ATTRIBUTE10 CONTRACT_HEADER_ATTRIBUTE10, ContHead.ATTRIBUTE11 CONTRACT_HEADER_ATTRIBUTE11, ContHead.ATTRIBUTE12 CONTRACT_HEADER_ATTRIBUTE12, ContHead.ATTRIBUTE13 CONTRACT_HEADER_ATTRIBUTE13, ContHead.ATTRIBUTE14 CONTRACT_HEADER_ATTRIBUTE14, ContHead.ATTRIBUTE15 CONTRACT_HEADER_ATTRIBUTE15, ContHead.ATTRIBUTE16 CONTRACT_HEADER_ATTRIBUTE16, ContHead.ATTRIBUTE17 CONTRACT_HEADER_ATTRIBUTE17, ContHead.ATTRIBUTE18 CONTRACT_HEADER_ATTRIBUTE18, ContHead.ATTRIBUTE19 CONTRACT_HEADER_ATTRIBUTE19, ContHead.ATTRIBUTE20 CONTRACT_HEADER_ATTRIBUTE20, ContHead.ATTRIBUTE21 CONTRACT_HEADER_ATTRIBUTE21, ContHead.ATTRIBUTE22 CONTRACT_HEADER_ATTRIBUTE22, ContHead.ATTRIBUTE23 CONTRACT_HEADER_ATTRIBUTE23, ContHead.ATTRIBUTE24 CONTRACT_HEADER_ATTRIBUTE24, ContHead.ATTRIBUTE25 CONTRACT_HEADER_ATTRIBUTE25, ContHead.ATTRIBUTE26 CONTRACT_HEADER_ATTRIBUTE26, ContHead.ATTRIBUTE27 CONTRACT_HEADER_ATTRIBUTE27, ContHead.ATTRIBUTE28 CONTRACT_HEADER_ATTRIBUTE28, ContHead.ATTRIBUTE29 CONTRACT_HEADER_ATTRIBUTE29, ContHead.ATTRIBUTE30 CONTRACT_HEADER_ATTRIBUTE30, TrxHead.CUSTOMER_TRX_ID CONT_RA_CUST_TRX_ID 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)) |