OKC_VRM_SOURCE_DOCUMENT

Details

Schema: FUSION
Object owner: OKC
Object type: VIEW

Columns

Name
ID START_DATE CONTRACT_NUMBER DOCUMENT_CREATION_DATE DOCUMENT_UPDATE_DATE CURRENCY_CODE BILLING_SALESREP_ID BILL_TO_ACCT_ID BILL_TO_SITE_USE_ID SHIP_TO_ACCT_ID SHIP_TO_SITE_USE_ID ORG_ID LEGAL_ENTITY_ID PRIMARY_LEDGER_ID PRIMARY_PARTY_ID CUST_PO_NUMBER CONVERSION_DATE CONVERSION_RATE_TYPE REVENUE_IMPACT_DATE ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE4 ATTRIBUTE3 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_DATE6 ATTRIBUTE_DATE7 ATTRIBUTE_DATE8 ATTRIBUTE_DATE9 ATTRIBUTE_DATE10

Name

ID

START_DATE

CONTRACT_NUMBER

DOCUMENT_CREATION_DATE

DOCUMENT_UPDATE_DATE

CURRENCY_CODE

BILLING_SALESREP_ID

BILL_TO_ACCT_ID

BILL_TO_SITE_USE_ID

SHIP_TO_ACCT_ID

SHIP_TO_SITE_USE_ID

ORG_ID

LEGAL_ENTITY_ID

PRIMARY_LEDGER_ID

PRIMARY_PARTY_ID

CUST_PO_NUMBER

CONVERSION_DATE

CONVERSION_RATE_TYPE

REVENUE_IMPACT_DATE

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE4

ATTRIBUTE3

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE16

ATTRIBUTE17

ATTRIBUTE18

ATTRIBUTE19

ATTRIBUTE20

ATTRIBUTE21

ATTRIBUTE22

ATTRIBUTE23

ATTRIBUTE24

ATTRIBUTE25

ATTRIBUTE26

ATTRIBUTE27

ATTRIBUTE28

ATTRIBUTE29

ATTRIBUTE30

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_NUMBER6

ATTRIBUTE_NUMBER7

ATTRIBUTE_NUMBER8

ATTRIBUTE_NUMBER9

ATTRIBUTE_NUMBER10

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

ATTRIBUTE_DATE6

ATTRIBUTE_DATE7

ATTRIBUTE_DATE8

ATTRIBUTE_DATE9

ATTRIBUTE_DATE10

Query

SQL_Statement
SELECT cont.ID , cont.start_date, cont.contract_number, cont.creation_date document_creation_date, cont.last_update_date document_update_date, cont.currency_code, cont.billing_salesrep_id, cont.bill_to_acct_id, cont.bill_to_site_use_id, cont.ship_to_acct_id, cont.ship_to_site_use_id, cont.org_id, cont.legal_entity_id, bu.primary_ledger_id, r.OBJECT1_ID1 PRIMARY_PARTY_ID, cont.cust_po_number, cont.start_date conversion_date, bs.LGR_REV_RATE_TYPE conversion_rate_type, lines.REVENUE_IMPACT_DATE, cont.ATTRIBUTE_CATEGORY, cont.ATTRIBUTE1, cont.ATTRIBUTE2, cont.ATTRIBUTE4, cont.ATTRIBUTE3, cont.ATTRIBUTE5, cont.ATTRIBUTE6, cont.ATTRIBUTE7, cont.ATTRIBUTE8, cont.ATTRIBUTE9, cont.ATTRIBUTE10, cont.ATTRIBUTE11, cont.ATTRIBUTE12, cont.ATTRIBUTE13, cont.ATTRIBUTE14, cont.ATTRIBUTE15, cont.ATTRIBUTE16, cont.ATTRIBUTE17, cont.ATTRIBUTE18, cont.ATTRIBUTE19, cont.ATTRIBUTE20, cont.ATTRIBUTE21, cont.ATTRIBUTE22, cont.ATTRIBUTE23, cont.ATTRIBUTE24, cont.ATTRIBUTE25, cont.ATTRIBUTE26, cont.ATTRIBUTE27, cont.ATTRIBUTE28, cont.ATTRIBUTE29, cont.ATTRIBUTE30, cont.ATTRIBUTE_NUMBER1, cont.ATTRIBUTE_NUMBER2, cont.ATTRIBUTE_NUMBER3, cont.ATTRIBUTE_NUMBER4, cont.ATTRIBUTE_NUMBER5, cont.ATTRIBUTE_NUMBER6, cont.ATTRIBUTE_NUMBER7, cont.ATTRIBUTE_NUMBER8, cont.ATTRIBUTE_NUMBER9, cont.ATTRIBUTE_NUMBER10, cont.ATTRIBUTE_DATE1, cont.ATTRIBUTE_DATE2, cont.ATTRIBUTE_DATE3, cont.ATTRIBUTE_DATE4, cont.ATTRIBUTE_DATE5, cont.ATTRIBUTE_DATE6, cont.ATTRIBUTE_DATE7, cont.ATTRIBUTE_DATE8, cont.ATTRIBUTE_DATE9, cont.ATTRIBUTE_DATE10 FROM okc_k_headers_all_b cont, (SELECT dnz_chr_id, major_version, MAX(REVENUE_IMPACT_DATE) REVENUE_IMPACT_DATE FROM okc_k_lines_b WHERE sts_code in ('ACTIVE','CLOSED','EXPIRED','HOLD') AND source_code_class = 'PROJECT' GROUP BY dnz_chr_id, major_version) lines, fun_all_business_units_v bu, OKC_BU_IMPL_OPTIONS_ALL bs, OKC_K_PARTY_ROLES_B r, OKC_CONTRACT_TYPES_B t WHERE cont.version_type in ('C','A') AND cont.sts_code in ('ACTIVE','CLOSED','EXPIRED','HOLD') AND CONT.buy_or_sell = 'S' AND cont.CONTRACT_TYPE_ID=t.CONTRACT_TYPE_ID and (Nvl(t.INTER_COMPANY_FLAG,'N')<>'Y' and Nvl(t.INTER_PROJECT_FLAG,'N')<>'Y') AND t.LINE_CLASS='PROJECT' AND lines.dnz_chr_id(+) = cont.ID AND lines.major_version(+) = cont.major_version AND r.dnz_chr_id(+) = CONT.ID AND r.major_version(+) = CONT.major_version AND r.PRIMARY_YN(+) = 'Y' AND bu.bu_id = cont.org_id AND bs.org_id = cont.org_id

SQL_Statement

SELECT

cont.ID ,

cont.start_date,

cont.contract_number,

cont.creation_date document_creation_date,

cont.last_update_date document_update_date,

cont.currency_code,

cont.billing_salesrep_id,

cont.bill_to_acct_id,

cont.bill_to_site_use_id,

cont.ship_to_acct_id,

cont.ship_to_site_use_id,

cont.org_id,

cont.legal_entity_id,

bu.primary_ledger_id,

r.OBJECT1_ID1 PRIMARY_PARTY_ID,

cont.cust_po_number,

cont.start_date conversion_date,

bs.LGR_REV_RATE_TYPE conversion_rate_type,

lines.REVENUE_IMPACT_DATE,

cont.ATTRIBUTE_CATEGORY,

cont.ATTRIBUTE1,

cont.ATTRIBUTE2,

cont.ATTRIBUTE4,

cont.ATTRIBUTE3,

cont.ATTRIBUTE5,

cont.ATTRIBUTE6,

cont.ATTRIBUTE7,

cont.ATTRIBUTE8,

cont.ATTRIBUTE9,

cont.ATTRIBUTE10,

cont.ATTRIBUTE11,

cont.ATTRIBUTE12,

cont.ATTRIBUTE13,

cont.ATTRIBUTE14,

cont.ATTRIBUTE15,

cont.ATTRIBUTE16,

cont.ATTRIBUTE17,

cont.ATTRIBUTE18,

cont.ATTRIBUTE19,

cont.ATTRIBUTE20,

cont.ATTRIBUTE21,

cont.ATTRIBUTE22,

cont.ATTRIBUTE23,

cont.ATTRIBUTE24,

cont.ATTRIBUTE25,

cont.ATTRIBUTE26,

cont.ATTRIBUTE27,

cont.ATTRIBUTE28,

cont.ATTRIBUTE29,

cont.ATTRIBUTE30,

cont.ATTRIBUTE_NUMBER1,

cont.ATTRIBUTE_NUMBER2,

cont.ATTRIBUTE_NUMBER3,

cont.ATTRIBUTE_NUMBER4,

cont.ATTRIBUTE_NUMBER5,

cont.ATTRIBUTE_NUMBER6,

cont.ATTRIBUTE_NUMBER7,

cont.ATTRIBUTE_NUMBER8,

cont.ATTRIBUTE_NUMBER9,

cont.ATTRIBUTE_NUMBER10,

cont.ATTRIBUTE_DATE1,

cont.ATTRIBUTE_DATE2,

cont.ATTRIBUTE_DATE3,

cont.ATTRIBUTE_DATE4,

cont.ATTRIBUTE_DATE5,

cont.ATTRIBUTE_DATE6,

cont.ATTRIBUTE_DATE7,

cont.ATTRIBUTE_DATE8,

cont.ATTRIBUTE_DATE9,

cont.ATTRIBUTE_DATE10

FROM okc_k_headers_all_b cont,

(SELECT dnz_chr_id, major_version, MAX(REVENUE_IMPACT_DATE) REVENUE_IMPACT_DATE

FROM okc_k_lines_b

WHERE sts_code in ('ACTIVE','CLOSED','EXPIRED','HOLD')

AND source_code_class = 'PROJECT'

GROUP BY dnz_chr_id, major_version) lines,

fun_all_business_units_v bu, OKC_BU_IMPL_OPTIONS_ALL bs, OKC_K_PARTY_ROLES_B r, OKC_CONTRACT_TYPES_B t

WHERE cont.version_type in ('C','A')

AND cont.sts_code in ('ACTIVE','CLOSED','EXPIRED','HOLD')

AND CONT.buy_or_sell = 'S'

AND cont.CONTRACT_TYPE_ID=t.CONTRACT_TYPE_ID and (Nvl(t.INTER_COMPANY_FLAG,'N')<>'Y' and Nvl(t.INTER_PROJECT_FLAG,'N')<>'Y')

AND t.LINE_CLASS='PROJECT'

AND lines.dnz_chr_id(+) = cont.ID

AND lines.major_version(+) = cont.major_version

AND r.dnz_chr_id(+) = CONT.ID

AND r.major_version(+) = CONT.major_version

AND r.PRIMARY_YN(+) = 'Y'

AND bu.bu_id = cont.org_id

AND bs.org_id = cont.org_id