Query to get Contract Details

 SELECT okha.CONTRACT_NUMBER,

  hca.ACCOUNT_NUMBER,

hca.ACCOUNT_Name,

hp.PARTY_NUMBER,

octt.NAME "Contract Type",

hou.name "BU",

xep.name "Legal Entity",

okha.START_DATE,

okha.END_DATE,

okha.STS_CODE "Header status",

okha.ESTIMATED_AMOUNT,

oklb.LINE_NUMBER,

oklt.LINE_NAME,

oklt.LINE_DESCRIPTION,

oklb.LINE_AMOUNT,

oklb.STS_CODE "Line status",

oltt.NAME "Line Type",

pbptb.BILL_PLAN_NAME "Bill Plan",

pbptr.BILL_PLAN_NAME "Revenue Plan",

ppat.NAME "Proect Name",

ppab.SEGMENT1 "Project Number",

ppab.PROJECT_STATUS_CODE,

pbc.ITD_INVOICE_AMOUNT,

pbc.ITD_REVENUE_AMOUNT

,ppev.ELEMENT_NUMBER

,ppev.name "Task Name" 

FROM OKC_K_HEADERS_ALL_B okha,

HZ_CUST_ACCOUNTS hca,

HZ_PARTIES HP,

OKC_CONTRACT_TYPES_TL octt,

HR_ORGANIZATION_UNITS Hou,

XLE_ENTITY_PROFILES xep,

OKC_K_LINES_B oklb,

OKC_K_LINES_TL oklt,

OKC_LINE_TYPES_TL oltt,

PJB_BILL_PLANS_TL pbptb,

PJB_BILL_PLANS_TL pbptr,

PJB_CNTRCT_PROJ_LINKS pcpl,

PJF_PROJECTS_ALL_B ppab,

pjf_projects_all_tl ppat,

PJB_BILLING_CONTROLS pbc,

PJF_PROJ_ELEMENTS_VL ppev

WHERE contract_number = :P_CONTRACT_NUM

AND oklb.LINE_NUMBER = 1

AND okha.BILL_TO_ACCT_ID = hca.CUST_ACCOUNT_ID

AND okha.MAJOR_VERSION = (

SELECT max(MAJOR_VERSION)

FROM OKC_K_HEADERS_ALL_B

WHERE ID = okha.ID

)

AND hp.party_id = hca.party_id

AND octt.CONTRACT_TYPE_ID = okha.CONTRACT_TYPE_ID

AND octt.LANGUAGE = userenv('LANG')

AND okha.ORG_ID = hou.ORGANIZATION_ID

AND okha.LEGAL_ENTITY_ID = xep.LEGAL_ENTITY_ID

AND oklb.CHR_ID = okha.ID

AND oklb.MAJOR_VERSION = okha.MAJOR_VERSION

AND oklb.ID = oklt.ID

AND oklb.MAJOR_VERSION = oklt.MAJOR_VERSION

AND oklt.LANGUAGE = userenv('LANG')

AND oltt.LINE_TYPE_ID = oklb.LINE_TYPE_ID

AND oltt.LANGUAGE = userenv('LANG')

AND oklb.BILL_PLAN_ID = pbptb.BILL_PLAN_ID

AND oklb.MAJOR_VERSION = pbptb.MAJOR_VERSION

AND pbptb.LANGUAGE = userenv('LANG')

AND oklb.REVENUE_PLAN_ID = pbptr.BILL_PLAN_ID

AND oklb.MAJOR_VERSION = pbptr.MAJOR_VERSION

AND pbptr.LANGUAGE = userenv('LANG')

AND oklb.ID = pcpl.CONTRACT_LINE_ID

AND oklb.MAJOR_VERSION = pcpl.MAJOR_VERSION

AND ppab.PROJECT_ID = pcpl.PROJECT_ID

AND ppat.PROJECT_ID = pcpl.PROJECT_ID

AND ppat.LANGUAGE = userenv('LANG')

AND pbc.CONTRACT_LINE_ID = oklb.ID

AND pbc.MAJOR_VERSION = oklb.MAJOR_VERSION

and pcpl.PROJ_ELEMENT_ID = ppev.PROJ_ELEMENT_ID