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))