In this article, we will talk about Oracle Fusion contract expenditure item Tables. The oracle fusion contract expenditure items specifics can be projected using project and contract tables. These oracle fusion tables will provide us with all project information. we will see invoiced and un-invoiced project table details In Oracle Fusion, we use these data to create BIP oracle fusion project reports. Please see below for further information on Oracle Fusion Projects Tables.
PJF_PROJECTS_ALL_VL: This is a view and this stores basic information about Projects in Oracle Fusion.
PJF_TASKS_V: This is a view and this stores project task-related information in Oracle Fusion.
PJC_EXP_ITEMS_ALL: This table stores the Project Expenditure Item information in Oracle Fusion.
PJF_EXP_TYPES_TL: This table stores Project Expenditure type Information in translated form in Oracle Fusion.
OKC_K_HEADERS_ALL_B: A contract’s top level component, the contract header, is used to collect contract information such as the number, kind, parties, business unit, start date, currency, and so on. In addition, certain header-level characteristics can be set to contract lines by default.
PJB_INVOICE_HEADERS: This table stores the draft invoices generated for a project.
PJB_INVOICE_LINES: This table stores the lines of a draft invoice.
PJB_INV_LINE_DISTS: This table stores the invoice lines distributions.
Query to get billable expenditure Items details against Project and Contracts in Oracle Fusion
SELECT NVL(SUM(QTY),0) ITD_BILL_TPROJ,
NVL(SUM(PROJFUNC_RAW_COST),0) ITD_RAW_COST
FROM(
select NVL(sum(a.quantity),0) qty,
NVL(SUM(a.PROJFUNC_RAW_COST),0) PROJFUNC_RAW_COST
from PJC_EXP_ITEMS_ALL a,
PJF_EXP_TYPES_VL b
where a.expenditure_type_id = b.expenditure_type_id
and a.project_id = ( Select project_id
From PJF_PROJECTS_ALL_VL PPA
Where Segment1= :P_PRJ_NUM)
and a.BILLABLE_FLAG = ‘Y’
and b.expenditure_type_name = ‘Hours’
and a.REVENUE_RECOGNIZED_FLAG = ‘F’
and a.expenditure_item_id not in
(SELECT PEIA.EXPENDITURE_ITEM_ID
FROM OKC_K_HEADERS_ALL_B OKH,
PJB_INVOICE_HEADERS PIH,
PJB_INVOICE_LINES PIL,
PJB_INV_LINE_DISTS PILD,
PJC_EXP_ITEMS_ALL PEIA,
PJF_EXP_TYPES_VL PET
WHERE OKH.contract_number=:P_CONTRACT_NUM
–AND OKH.MAJOR_VERSION = (select max(MAJOR_VERSION)MAJOR_VERSION from OKC_K_HEADERS_ALL_B where contract_number= :P_CONTRACT_NUM)
AND OKH.ID = PIH.CONTRACT_ID
–AND PIH.INVOICE_NUM = (select max(INVOICE_NUM) from PJB_INVOICE_HEADERS where contract_id = OKH.CONTRACT_ID)
AND PIH.INVOICE_ID = PIL.INVOICE_ID
AND PIL.INVOICE_ID = PILD.INVOICE_ID
AND PIL.INVOICE_LINE_ID = PILD.INVOICE_LINE_ID
AND PILD.TRANSACTION_ID = PEIA.EXPENDITURE_ITEM_ID
AND PEIA.EXPENDITURE_TYPE_ID = PET.EXPENDITURE_TYPE_ID
and pet.expenditure_type_name = ‘Hours’)
union
SELECT sum(PEIA.QUANTITY) qty,
sum(PEIA.PROJFUNC_RAW_COST) PROJFUNC_RAW_COST
FROM OKC_K_HEADERS_ALL_B OKH,
PJB_INVOICE_HEADERS PIH,
PJB_INVOICE_LINES PIL,
PJB_INV_LINE_DISTS PILD,
PJC_EXP_ITEMS_ALL PEIA,
PJF_EXP_TYPES_VL PET
WHERE OKH.contract_number=:P_CONTRACT_NUM
AND OKH.MAJOR_VERSION = (select max(MAJOR_VERSION)MAJOR_VERSION from OKC_K_HEADERS_ALL_B where contract_number= :P_CONTRACT_NUM)
AND OKH.ID = PIH.CONTRACT_ID
–AND PIH.INVOICE_NUM = (select max(INVOICE_NUM) from PJB_INVOICE_HEADERS where contract_id = OKH.CONTRACT_ID)
AND PIH.INVOICE_ID = PIL.INVOICE_ID
AND PIL.INVOICE_ID = PILD.INVOICE_ID
AND PIL.INVOICE_LINE_ID = PILD.INVOICE_LINE_ID
AND PILD.TRANSACTION_ID = PEIA.EXPENDITURE_ITEM_ID
AND PEIA.EXPENDITURE_TYPE_ID = PET.EXPENDITURE_TYPE_ID
and pet.expenditure_type_name = ‘Hours’
)