PJB_XLA_CONTHEAD_REF

Details

Schema: FUSION
Object owner: PJB
Object type: VIEW

Columns

Name
CONTRACT_ID CONTRACT_NAME MAJOR_VERSION CONTRACT_NUMBER CONTRACT_LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_NAME CONTRACT_ORGANIZATION_ID CONTRACT_ORGANIZATION_NAME CONTRACT_ORG_COMPANY CONTRACT_ORG_COST_CENTER CONTRACT_TYPE_ID CONTRACT_TYPE CONTRACT_NET_INVOICE_FLAG CONTRACT_OWNING_ORG_ID CONTRACT_OWNING_ORG_NAME CONTRACT_OWN_ORG_COMPANY CONTRACT_OWN_ORG_COST_CENTER CONTRACT_PRIMARY_PARTY_ID SALESPERSON_REVENUE_ACCOUNT TRANSTYPE_REVENUE_ACCOUNT 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 AWARD_ID FEDERAL_SPONSOR AWARD_TYPE_NAME AWARD_PURPOSE_NAME AWARD_INSTITUTION AWARD_CONTEXT_CATEGORY AWARD_ATTRIBUTE_TEXT1 AWARD_ATTRIBUTE_TEXT2 AWARD_ATTRIBUTE_TEXT3 AWARD_ATTRIBUTE_TEXT4 AWARD_ATTRIBUTE_TEXT5 AWARD_ATTRIBUTE_TEXT6 AWARD_ATTRIBUTE_TEXT7 AWARD_ATTRIBUTE_TEXT8 AWARD_ATTRIBUTE_TEXT9 AWARD_ATTRIBUTE_TEXT10 AWARD_ATTRIBUTE_TEXT11 AWARD_ATTRIBUTE_TEXT12 AWARD_ATTRIBUTE_TEXT13 AWARD_ATTRIBUTE_TEXT14 AWARD_ATTRIBUTE_TEXT15 AWARD_ATTRIBUTE_TEXT16 AWARD_ATTRIBUTE_TEXT17 AWARD_ATTRIBUTE_TEXT18 AWARD_ATTRIBUTE_TEXT19 AWARD_ATTRIBUTE_TEXT20 AWARD_ATTRIBUTE_NUM1 AWARD_ATTRIBUTE_NUM2 AWARD_ATTRIBUTE_NUM3 AWARD_ATTRIBUTE_NUM4 AWARD_ATTRIBUTE_NUM5 AWARD_ATTRIBUTE_NUM6 AWARD_ATTRIBUTE_NUM7 AWARD_ATTRIBUTE_NUM8 AWARD_ATTRIBUTE_NUM9 AWARD_ATTRIBUTE_NUM10 AWARD_ATTRIBUTE_DATE1 AWARD_ATTRIBUTE_DATE2 AWARD_ATTRIBUTE_DATE3 AWARD_ATTRIBUTE_DATE4 AWARD_ATTRIBUTE_DATE5 AWARD_ATTRIBUTE_DATE6 AWARD_ATTRIBUTE_DATE7 AWARD_ATTRIBUTE_DATE8 AWARD_ATTRIBUTE_DATE9 AWARD_ATTRIBUTE_DATE10 CON_HEADER_CUST_PO_NUMBER MIGRATION_IDENTIFIER_FLAG SOLD_TO_CUSTOMER_ID

Name

CONTRACT_ID

CONTRACT_NAME

MAJOR_VERSION

CONTRACT_NUMBER

CONTRACT_LEGAL_ENTITY_ID

CONTRACT_LEGAL_ENTITY_NAME

CONTRACT_ORGANIZATION_ID

CONTRACT_ORGANIZATION_NAME

CONTRACT_ORG_COMPANY

CONTRACT_ORG_COST_CENTER

CONTRACT_TYPE_ID

CONTRACT_TYPE

CONTRACT_NET_INVOICE_FLAG

CONTRACT_OWNING_ORG_ID

CONTRACT_OWNING_ORG_NAME

CONTRACT_OWN_ORG_COMPANY

CONTRACT_OWN_ORG_COST_CENTER

CONTRACT_PRIMARY_PARTY_ID

SALESPERSON_REVENUE_ACCOUNT

TRANSTYPE_REVENUE_ACCOUNT

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

AWARD_ID

FEDERAL_SPONSOR

AWARD_TYPE_NAME

AWARD_PURPOSE_NAME

AWARD_INSTITUTION

AWARD_CONTEXT_CATEGORY

AWARD_ATTRIBUTE_TEXT1

AWARD_ATTRIBUTE_TEXT2

AWARD_ATTRIBUTE_TEXT3

AWARD_ATTRIBUTE_TEXT4

AWARD_ATTRIBUTE_TEXT5

AWARD_ATTRIBUTE_TEXT6

AWARD_ATTRIBUTE_TEXT7

AWARD_ATTRIBUTE_TEXT8

AWARD_ATTRIBUTE_TEXT9

AWARD_ATTRIBUTE_TEXT10

AWARD_ATTRIBUTE_TEXT11

AWARD_ATTRIBUTE_TEXT12

AWARD_ATTRIBUTE_TEXT13

AWARD_ATTRIBUTE_TEXT14

AWARD_ATTRIBUTE_TEXT15

AWARD_ATTRIBUTE_TEXT16

AWARD_ATTRIBUTE_TEXT17

AWARD_ATTRIBUTE_TEXT18

AWARD_ATTRIBUTE_TEXT19

AWARD_ATTRIBUTE_TEXT20

AWARD_ATTRIBUTE_NUM1

AWARD_ATTRIBUTE_NUM2

AWARD_ATTRIBUTE_NUM3

AWARD_ATTRIBUTE_NUM4

AWARD_ATTRIBUTE_NUM5

AWARD_ATTRIBUTE_NUM6

AWARD_ATTRIBUTE_NUM7

AWARD_ATTRIBUTE_NUM8

AWARD_ATTRIBUTE_NUM9

AWARD_ATTRIBUTE_NUM10

AWARD_ATTRIBUTE_DATE1

AWARD_ATTRIBUTE_DATE2

AWARD_ATTRIBUTE_DATE3

AWARD_ATTRIBUTE_DATE4

AWARD_ATTRIBUTE_DATE5

AWARD_ATTRIBUTE_DATE6

AWARD_ATTRIBUTE_DATE7

AWARD_ATTRIBUTE_DATE8

AWARD_ATTRIBUTE_DATE9

AWARD_ATTRIBUTE_DATE10

CON_HEADER_CUST_PO_NUMBER

MIGRATION_IDENTIFIER_FLAG

SOLD_TO_CUSTOMER_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.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_NAME, 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, contHead.NET_INVOICE_FLAG CONTRACT_NET_INVOICE_FLAG, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_ID, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_NAME, 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, SalesRep.REV_CCID SALESPERSON_REVENUE_ACCOUNT, TransType.REV_CCID TRANSTYPE_REVENUE_ACCOUNT, 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, GmsView.ID AWARD_ID, (Select GmsSponsor.FEDERAL_FLAG from GMS_SPONSORS_B GmsSponsor where GmsSponsor.SPONSOR_ID = GmsView.SPONSOR_ID) FEDERAL_SPONSOR, GmsView.AWARD_TYPE AWARD_TYPE_NAME, GmsView.AWARD_PURPOSE_CODE AWARD_PURPOSE_NAME, GmsView.INSTITUTION_ID AWARD_INSTITUTION, GmsView.ATTRIBUTE_CATEGORY AWARD_CONTEXT_CATEGORY, GmsView.ATTRIBUTE1 AWARD_ATTRIBUTE_TEXT1, GmsView.ATTRIBUTE2 AWARD_ATTRIBUTE_TEXT2, GmsView.ATTRIBUTE3 AWARD_ATTRIBUTE_TEXT3, GmsView.ATTRIBUTE4 AWARD_ATTRIBUTE_TEXT4, GmsView.ATTRIBUTE5 AWARD_ATTRIBUTE_TEXT5, GmsView.ATTRIBUTE6 AWARD_ATTRIBUTE_TEXT6, GmsView.ATTRIBUTE7 AWARD_ATTRIBUTE_TEXT7, GmsView.ATTRIBUTE8 AWARD_ATTRIBUTE_TEXT8, GmsView.ATTRIBUTE9 AWARD_ATTRIBUTE_TEXT9, GmsView.ATTRIBUTE10 AWARD_ATTRIBUTE_TEXT10, GmsView.ATTRIBUTE11 AWARD_ATTRIBUTE_TEXT11, GmsView.ATTRIBUTE12 AWARD_ATTRIBUTE_TEXT12, GmsView.ATTRIBUTE13 AWARD_ATTRIBUTE_TEXT13, GmsView.ATTRIBUTE14 AWARD_ATTRIBUTE_TEXT14, GmsView.ATTRIBUTE15 AWARD_ATTRIBUTE_TEXT15, GmsView.ATTRIBUTE16 AWARD_ATTRIBUTE_TEXT16, GmsView.ATTRIBUTE17 AWARD_ATTRIBUTE_TEXT17, GmsView.ATTRIBUTE18 AWARD_ATTRIBUTE_TEXT18, GmsView.ATTRIBUTE19 AWARD_ATTRIBUTE_TEXT19, GmsView.ATTRIBUTE20 AWARD_ATTRIBUTE_TEXT20, GmsView.ATTRIBUTE_NUMBER1 AWARD_ATTRIBUTE_NUM1, GmsView.ATTRIBUTE_NUMBER2 AWARD_ATTRIBUTE_NUM2, GmsView.ATTRIBUTE_NUMBER3 AWARD_ATTRIBUTE_NUM3, GmsView.ATTRIBUTE_NUMBER4 AWARD_ATTRIBUTE_NUM4, GmsView.ATTRIBUTE_NUMBER5 AWARD_ATTRIBUTE_NUM5, GmsView.ATTRIBUTE_NUMBER6 AWARD_ATTRIBUTE_NUM6, GmsView.ATTRIBUTE_NUMBER7 AWARD_ATTRIBUTE_NUM7, GmsView.ATTRIBUTE_NUMBER8 AWARD_ATTRIBUTE_NUM8, GmsView.ATTRIBUTE_NUMBER9 AWARD_ATTRIBUTE_NUM9, GmsView.ATTRIBUTE_NUMBER10 AWARD_ATTRIBUTE_NUM10, GmsView.ATTRIBUTE_DATE1 AWARD_ATTRIBUTE_DATE1, GmsView.ATTRIBUTE_DATE2 AWARD_ATTRIBUTE_DATE2, GmsView.ATTRIBUTE_DATE3 AWARD_ATTRIBUTE_DATE3, GmsView.ATTRIBUTE_DATE4 AWARD_ATTRIBUTE_DATE4, GmsView.ATTRIBUTE_DATE5 AWARD_ATTRIBUTE_DATE5, GmsView.ATTRIBUTE_DATE6 AWARD_ATTRIBUTE_DATE6, GmsView.ATTRIBUTE_DATE7 AWARD_ATTRIBUTE_DATE7, GmsView.ATTRIBUTE_DATE8 AWARD_ATTRIBUTE_DATE8, GmsView.ATTRIBUTE_DATE9 AWARD_ATTRIBUTE_DATE9, GmsView.ATTRIBUTE_DATE10 AWARD_ATTRIBUTE_DATE10, ContHead.CUST_PO_NUMBER CON_HEADER_CUST_PO_NUMBER, ContHead.UNDER_MIGRATION_FLAG MIGRATION_IDENTIFIER_FLAG, ContHead.SOLD_TO_ACCT_ID SOLD_TO_CUSTOMER_ID FROM OKC_K_HEADERS_ALL_B ContHead, GMS_AWARD_HEADERS_B GmsView, AR_REF_ACCOUNTS_ALL SalesRep, AR_REF_ACCOUNTS_ALL TransType WHERE ContHead.VERSION_TYPE IN ('A','C') AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD') AND ContHead.TEMPLATE_YN = 'N' AND ContHead.ID = GmsView.ID(+) AND ContHead.BILLING_SALESREP_ID = SalesRep.SOURCE_REF_ACCOUNT_ID(+) AND NVL( SalesRep.SOURCE_REF_TABLE, 'RA_SALESREPS') = 'RA_SALESREPS' AND ContHead.ORG_ID = NVL(SalesRep.BU_ID,ContHead.ORG_ID) AND ContHead.INV_TRX_TYPE_ID = TransType.SOURCE_REF_ACCOUNT_ID(+) AND NVL(TransType.SOURCE_REF_TABLE,'RA_CUST_TRX_TYPES_ALL') = 'RA_CUST_TRX_TYPES_ALL' AND ContHead.ORG_ID = NVL(TransType.BU_ID,ContHead.ORG_ID) 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.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_NAME, 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, contHead.NET_INVOICE_FLAG CONTRACT_NET_INVOICE_FLAG, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_ID, ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_NAME, 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, SalesRep.REV_CCID SALESPERSON_REVENUE_ACCOUNT, TransType.REV_CCID TRANSTYPE_REVENUE_ACCOUNT, 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, GmsView.ID AWARD_ID, (Select GmsSponsor.FEDERAL_FLAG from GMS_SPONSORS_B GmsSponsor where GmsSponsor.SPONSOR_ID = GmsView.SPONSOR_ID) FEDERAL_SPONSOR, GmsView.AWARD_TYPE AWARD_TYPE_NAME, GmsView.AWARD_PURPOSE_CODE AWARD_PURPOSE_NAME, GmsView.INSTITUTION_ID AWARD_INSTITUTION, GmsView.ATTRIBUTE_CATEGORY AWARD_CONTEXT_CATEGORY, GmsView.ATTRIBUTE1 AWARD_ATTRIBUTE_TEXT1, GmsView.ATTRIBUTE2 AWARD_ATTRIBUTE_TEXT2, GmsView.ATTRIBUTE3 AWARD_ATTRIBUTE_TEXT3, GmsView.ATTRIBUTE4 AWARD_ATTRIBUTE_TEXT4, GmsView.ATTRIBUTE5 AWARD_ATTRIBUTE_TEXT5, GmsView.ATTRIBUTE6 AWARD_ATTRIBUTE_TEXT6, GmsView.ATTRIBUTE7 AWARD_ATTRIBUTE_TEXT7, GmsView.ATTRIBUTE8 AWARD_ATTRIBUTE_TEXT8, GmsView.ATTRIBUTE9 AWARD_ATTRIBUTE_TEXT9, GmsView.ATTRIBUTE10 AWARD_ATTRIBUTE_TEXT10, GmsView.ATTRIBUTE11 AWARD_ATTRIBUTE_TEXT11, GmsView.ATTRIBUTE12 AWARD_ATTRIBUTE_TEXT12, GmsView.ATTRIBUTE13 AWARD_ATTRIBUTE_TEXT13, GmsView.ATTRIBUTE14 AWARD_ATTRIBUTE_TEXT14, GmsView.ATTRIBUTE15 AWARD_ATTRIBUTE_TEXT15, GmsView.ATTRIBUTE16 AWARD_ATTRIBUTE_TEXT16, GmsView.ATTRIBUTE17 AWARD_ATTRIBUTE_TEXT17, GmsView.ATTRIBUTE18 AWARD_ATTRIBUTE_TEXT18, GmsView.ATTRIBUTE19 AWARD_ATTRIBUTE_TEXT19, GmsView.ATTRIBUTE20 AWARD_ATTRIBUTE_TEXT20, GmsView.ATTRIBUTE_NUMBER1 AWARD_ATTRIBUTE_NUM1, GmsView.ATTRIBUTE_NUMBER2 AWARD_ATTRIBUTE_NUM2, GmsView.ATTRIBUTE_NUMBER3 AWARD_ATTRIBUTE_NUM3, GmsView.ATTRIBUTE_NUMBER4 AWARD_ATTRIBUTE_NUM4, GmsView.ATTRIBUTE_NUMBER5 AWARD_ATTRIBUTE_NUM5, GmsView.ATTRIBUTE_NUMBER6 AWARD_ATTRIBUTE_NUM6, GmsView.ATTRIBUTE_NUMBER7 AWARD_ATTRIBUTE_NUM7, GmsView.ATTRIBUTE_NUMBER8 AWARD_ATTRIBUTE_NUM8, GmsView.ATTRIBUTE_NUMBER9 AWARD_ATTRIBUTE_NUM9, GmsView.ATTRIBUTE_NUMBER10 AWARD_ATTRIBUTE_NUM10, GmsView.ATTRIBUTE_DATE1 AWARD_ATTRIBUTE_DATE1, GmsView.ATTRIBUTE_DATE2 AWARD_ATTRIBUTE_DATE2, GmsView.ATTRIBUTE_DATE3 AWARD_ATTRIBUTE_DATE3, GmsView.ATTRIBUTE_DATE4 AWARD_ATTRIBUTE_DATE4, GmsView.ATTRIBUTE_DATE5 AWARD_ATTRIBUTE_DATE5, GmsView.ATTRIBUTE_DATE6 AWARD_ATTRIBUTE_DATE6, GmsView.ATTRIBUTE_DATE7 AWARD_ATTRIBUTE_DATE7, GmsView.ATTRIBUTE_DATE8 AWARD_ATTRIBUTE_DATE8, GmsView.ATTRIBUTE_DATE9 AWARD_ATTRIBUTE_DATE9, GmsView.ATTRIBUTE_DATE10 AWARD_ATTRIBUTE_DATE10, ContHead.CUST_PO_NUMBER CON_HEADER_CUST_PO_NUMBER, ContHead.UNDER_MIGRATION_FLAG MIGRATION_IDENTIFIER_FLAG, ContHead.SOLD_TO_ACCT_ID SOLD_TO_CUSTOMER_ID FROM OKC_K_HEADERS_ALL_B ContHead, GMS_AWARD_HEADERS_B GmsView, AR_REF_ACCOUNTS_ALL SalesRep, AR_REF_ACCOUNTS_ALL TransType, 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.TEMPLATE_YN = 'N' AND ContHead.ID = GmsView.ID(+) AND ContHead.BILLING_SALESREP_ID = SalesRep.SOURCE_REF_ACCOUNT_ID(+) AND NVL( SalesRep.SOURCE_REF_TABLE, 'RA_SALESREPS') = 'RA_SALESREPS' AND ContHead.ORG_ID = NVL(SalesRep.BU_ID,ContHead.ORG_ID) AND ContHead.INV_TRX_TYPE_ID = TransType.SOURCE_REF_ACCOUNT_ID(+) AND NVL(TransType.SOURCE_REF_TABLE,'RA_CUST_TRX_TYPES_ALL') = 'RA_CUST_TRX_TYPES_ALL' AND ContHead.ORG_ID = NVL(TransType.BU_ID,ContHead.ORG_ID) AND BU.ORG_ID = ContHead.Org_id AND BU.attribute15 = 'INCLUDE_UNDER_AMENDMENT'

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.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_NAME,

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,

contHead.NET_INVOICE_FLAG CONTRACT_NET_INVOICE_FLAG,

ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_ID,

ContHead.OWNING_ORG_ID CONTRACT_OWNING_ORG_NAME,

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,

SalesRep.REV_CCID SALESPERSON_REVENUE_ACCOUNT,

TransType.REV_CCID TRANSTYPE_REVENUE_ACCOUNT,

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,

GmsView.ID AWARD_ID,

(Select GmsSponsor.FEDERAL_FLAG from GMS_SPONSORS_B GmsSponsor where GmsSponsor.SPONSOR_ID = GmsView.SPONSOR_ID) FEDERAL_SPONSOR,

GmsView.AWARD_TYPE AWARD_TYPE_NAME,

GmsView.AWARD_PURPOSE_CODE AWARD_PURPOSE_NAME,

GmsView.INSTITUTION_ID AWARD_INSTITUTION,

GmsView.ATTRIBUTE_CATEGORY AWARD_CONTEXT_CATEGORY,

GmsView.ATTRIBUTE1 AWARD_ATTRIBUTE_TEXT1,

GmsView.ATTRIBUTE2 AWARD_ATTRIBUTE_TEXT2,

GmsView.ATTRIBUTE3 AWARD_ATTRIBUTE_TEXT3,

GmsView.ATTRIBUTE4 AWARD_ATTRIBUTE_TEXT4,

GmsView.ATTRIBUTE5 AWARD_ATTRIBUTE_TEXT5,

GmsView.ATTRIBUTE6 AWARD_ATTRIBUTE_TEXT6,

GmsView.ATTRIBUTE7 AWARD_ATTRIBUTE_TEXT7,

GmsView.ATTRIBUTE8 AWARD_ATTRIBUTE_TEXT8,

GmsView.ATTRIBUTE9 AWARD_ATTRIBUTE_TEXT9,

GmsView.ATTRIBUTE10 AWARD_ATTRIBUTE_TEXT10,

GmsView.ATTRIBUTE11 AWARD_ATTRIBUTE_TEXT11,

GmsView.ATTRIBUTE12 AWARD_ATTRIBUTE_TEXT12,

GmsView.ATTRIBUTE13 AWARD_ATTRIBUTE_TEXT13,

GmsView.ATTRIBUTE14 AWARD_ATTRIBUTE_TEXT14,

GmsView.ATTRIBUTE15 AWARD_ATTRIBUTE_TEXT15,

GmsView.ATTRIBUTE16 AWARD_ATTRIBUTE_TEXT16,

GmsView.ATTRIBUTE17 AWARD_ATTRIBUTE_TEXT17,

GmsView.ATTRIBUTE18 AWARD_ATTRIBUTE_TEXT18,

GmsView.ATTRIBUTE19 AWARD_ATTRIBUTE_TEXT19,

GmsView.ATTRIBUTE20 AWARD_ATTRIBUTE_TEXT20,

GmsView.ATTRIBUTE_NUMBER1 AWARD_ATTRIBUTE_NUM1,

GmsView.ATTRIBUTE_NUMBER2 AWARD_ATTRIBUTE_NUM2,

GmsView.ATTRIBUTE_NUMBER3 AWARD_ATTRIBUTE_NUM3,

GmsView.ATTRIBUTE_NUMBER4 AWARD_ATTRIBUTE_NUM4,

GmsView.ATTRIBUTE_NUMBER5 AWARD_ATTRIBUTE_NUM5,

GmsView.ATTRIBUTE_NUMBER6 AWARD_ATTRIBUTE_NUM6,

GmsView.ATTRIBUTE_NUMBER7 AWARD_ATTRIBUTE_NUM7,

GmsView.ATTRIBUTE_NUMBER8 AWARD_ATTRIBUTE_NUM8,

GmsView.ATTRIBUTE_NUMBER9 AWARD_ATTRIBUTE_NUM9,

GmsView.ATTRIBUTE_NUMBER10 AWARD_ATTRIBUTE_NUM10,

GmsView.ATTRIBUTE_DATE1 AWARD_ATTRIBUTE_DATE1,

GmsView.ATTRIBUTE_DATE2 AWARD_ATTRIBUTE_DATE2,

GmsView.ATTRIBUTE_DATE3 AWARD_ATTRIBUTE_DATE3,

GmsView.ATTRIBUTE_DATE4 AWARD_ATTRIBUTE_DATE4,

GmsView.ATTRIBUTE_DATE5 AWARD_ATTRIBUTE_DATE5,

GmsView.ATTRIBUTE_DATE6 AWARD_ATTRIBUTE_DATE6,

GmsView.ATTRIBUTE_DATE7 AWARD_ATTRIBUTE_DATE7,

GmsView.ATTRIBUTE_DATE8 AWARD_ATTRIBUTE_DATE8,

GmsView.ATTRIBUTE_DATE9 AWARD_ATTRIBUTE_DATE9,

GmsView.ATTRIBUTE_DATE10 AWARD_ATTRIBUTE_DATE10,

ContHead.CUST_PO_NUMBER CON_HEADER_CUST_PO_NUMBER,

ContHead.UNDER_MIGRATION_FLAG MIGRATION_IDENTIFIER_FLAG,

ContHead.SOLD_TO_ACCT_ID SOLD_TO_CUSTOMER_ID

FROM

OKC_K_HEADERS_ALL_B ContHead,

GMS_AWARD_HEADERS_B GmsView,

AR_REF_ACCOUNTS_ALL SalesRep,

AR_REF_ACCOUNTS_ALL TransType

WHERE

ContHead.VERSION_TYPE IN ('A','C')

AND ContHead.STS_CODE IN ('ACTIVE','CLOSED','EXPIRED','HOLD')

AND ContHead.TEMPLATE_YN = 'N'

AND ContHead.ID = GmsView.ID(+) AND ContHead.BILLING_SALESREP_ID = SalesRep.SOURCE_REF_ACCOUNT_ID(+)

AND NVL( SalesRep.SOURCE_REF_TABLE, 'RA_SALESREPS') = 'RA_SALESREPS'

AND ContHead.ORG_ID = NVL(SalesRep.BU_ID,ContHead.ORG_ID)

AND ContHead.INV_TRX_TYPE_ID = TransType.SOURCE_REF_ACCOUNT_ID(+)

AND NVL(TransType.SOURCE_REF_TABLE,'RA_CUST_TRX_TYPES_ALL') = 'RA_CUST_TRX_TYPES_ALL'

AND ContHead.ORG_ID = NVL(TransType.BU_ID,ContHead.ORG_ID)

UNION ALL