This PA labor expenditures report fetches expenditure and billing data related to Oracle Projects by joining relevant tables that store financial transactions and project billing information. It provides a combined view of project costs incurred and the corresponding billing details for analysis and reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves expenditure and billing details for Oracle Projects
-- Operating Unit Info
PAEI.OPERATING_UNIT OPERATING_UNIT_NAME,
PAEI.PROJECT_CARRYING_OUT_ORG PROJECT_ORGANIZATION,
PAEI.PROVIDER_ORGANIZATION,
PAEI.RECEIVER_ORGANIZATION,
-- Project Info
PAEI.PROJECT_NAME,
PAEI.PROJECT_NUMBER,
PAEI.PROJECT_STATUS_CODE PROJECT_STATUS,
--Task Info
PAEI.TASK_NAME,
PAEI.TASK_NUMBER,
PAEI.TASK_LEVEL,
PAEI.TASK_CARRYING_OUT_ORG,
-- Job Info
PAEI.COSTING_JOB_NAME,
PAEI.EMPLOYEE_JOB_NAME,
PAEI.LENT_JOB_NAME,
PAEI.INCURRED_ORG,
-- Expenditure Info
PAEI.EXPENDITURE_ITEM_DATE,
PAEI.UNIT_OF_MEASURE,
PAEI.EXPENDITURE_COMMENT,
PAEI.ADJUSTED_EXP_FLAG,
-- Supplier Info
PAEI.SUPPLIER_NAME,
PAEI.SUPPLIER_NUMBER,
--Billing Info
PAEI.BILL_HOLD_FLAG,
PAEI.BILL_RATE,
PAEI.BILL_RATE_MULTIPLIER,
PAEI.BILLABLE_FLAG,
PAEI.BILLED_FLAG,
-- Cost Distribution Info
PAEI.COST_BURDEN_DIST_FLAG,
PAEI.COST_DISTRIBUTED_FLAG,
PAEI.CROSS_CHARGE_FLAG,
PAEI.RECEIVER_GL_DATE,
PAEI.RECEIVER_GL_PERIOD_NAME,
-- PAEI.REVENUE_DIST_REJECTION,
PAEI.REVENUE_DISTRIBUTED_FLAG,
PAEI.SYSTEM_LINKAGE,
PAEI.PROJECT_CURRENCY_CODE CURRENCY,
-- Financials Info
PAEI.BURDEN_COST,
PAEI.BURDEN_COST_RATE,
PAEI.BURDENED_COST,
PAEI.QUANTITY,
PAEI.RAW_COST,
PAEI.RAW_COST_RATE,
NVL(PAEI.QUANTITY, 0) HOURS,
DECODE(PAEI.BILLABLE_FLAG, 'N', 0, NVL (PAEI.QUANTITY, 0)) BILLABLE_HOURS,
PAEI.PROJECT_RAW_COST
FROM
(SELECT
HOUSEC.NAME OPERATING_UNIT,
HOUSEC.ORGANIZATION_ID ORG_ID,
PEIA.ACCT_TP_RATE_TYPE ACCRUAL_RATE,
PEIA.BILL_TRANS_CURR_REV_AMT ACCRUED_REVENUE,
DECODE(PEIA.ADJUSTED_EXPENDITURE_ITEM_ID, NULL, 'N', 'Y') ADJUSTED_EXP_FLAG,
PEIA.BILL_HOLD_FLAG BILL_HOLD_FLAG,
PEIA.TP_BILL_RATE BILL_RATE,
PEIA.EXT_OVRD_BILL_RATE BILL_RATE_MULTIPLIER,
PEIA.BILLABLE_FLAG ,
DECODE(PEIA.IC_BILLABLE_FLAG, 'Y', DECODE(NVL(PEIA.PROJFUNC_RAW_COST, 0), 0, 'N', 'Y'), 'N') BILLED_FLAG,
PEIA.PROJFUNC_RAW_COST PROJECT_RAW_COST,
PEIA.BURDEN_COST_RATE,
PEIA.PROJECT_BURDENED_COST BURDENED_COST,
PEIA.DENOM_BURDENED_COST BURDEN_COST,
PEIA.CAPITALIZABLE_FLAG COST_BURDEN_DIST_FLAG,
PEIA.CAPITALIZATION_DIST_FLAG COST_DISTRIBUTED_FLAG,
PJ.NAME COSTING_JOB_NAME,
DECODE(PEIA.CC_CROSS_CHARGE_CODE, 'X', 'N', 'P', 'N', 'Y') CROSS_CHARGE_FLAG,
PTF.CREATION_DATE DATE_RECORD_CREATED,
PBE.EVENT_DESC EVENT,
PEIA.ATTRIBUTE_CATEGORY,
DECODE(PEIA.ATTRIBUTE_CATEGORY, 'Transportation - Allocation', PEIA.ATTRIBUTE4, NULL) ALLOCATION_PERCENT,
PEIA.ATTRIBUTE5 CORRECTED_EXPENDITURE_ITE,
DECODE(PEIA.ATTRIBUTE_CATEGORY, 'Transportation - Allocation', PEIA.ATTRIBUTE3, NULL) TOTAL_AREA_DOLLARS,
PTF.ORIG_TRANSACTION_REFERENCE EXP_ITEM_EXTERNAL_SYS_REF,
PTF.LD_ORIG_TRANSACTION_REFERENCE EXPEND_EXTERNAL_SYS_USER_REF,
PEC.EXPENDITURE_COMMENT,
PTF.ORIG_EXP_TXN_REFERENCE1 EXPENDITURE_EXTERNAL_SYS_REF1,
PTF.ORIG_EXP_TXN_REFERENCE2 EXPENDITURE_EXTERNAL_SYS_REF2,
PTF.ORIG_EXP_TXN_REFERENCE3 EXPENDITURE_EXTERNAL_SYS_REF3,
PEIA.EXPENDITURE_ITEM_DATE,
HAOUTL1.NAME INCURRED_ORG,
PJ2.NAME LENT_JOB_NAME,
PJ3.NAME EMPLOYEE_JOB_NAME,
PEIA.LABOR_COST_MULTIPLIER_NAME LABOR_COST_MULTIPLIER,
PSL.LABOR_NON_LABOR_FLAG LABOR_OR_NONLABOR_FLAG,
PEIA.NET_ZERO_ADJUSTMENT_FLAG NET_ZERO_FLAG,
NLRTL.NON_LABOR_RESOURCE_ID || '-' || NLRTL.DESCRIPTION NON_LABOR_RESOURCE,
PJAB.ATTRIBUTE_CATEGORY PROJ_ATTRIBUTE_CATEGORY,
HAOUTL2.NAME PROJECT_CARRYING_OUT_ORG,
NVL(PSL.PROJECT_MANUFACTURING_FLAG, 'N') PROJECT_MANUFACTURING_FLAG,
PJTL.NAME PROJECT_NAME,
PJAB.SEGMENT1 PROJECT_NUMBER,
HAOUTL3.NAME PROVIDER_ORGANIZATION,
PEIA.QUANTITY QUANTITY,
PEIA.ACCT_RAW_COST RAW_COST,
PEIA.RAW_COST_RATE,
PCDLA.RECVR_GL_DATE RECEIVER_GL_DATE,
PCDLA.RECVR_GL_PERIOD_NAME RECEIVER_GL_PERIOD_NAME,
HAOUTL4.NAME RECEIVER_ORGANIZATION,
PEIA.REVENUE_RECOGNIZED_FLAG REVENUE_DISTRIBUTED_FLAG,
PSL.MEANING SYSTEM_LINKAGE,
PTV.ATTRIBUTE_CATEGORY TASK_ATTRIBUTE_CATEGORY,
PTV.ATTRIBUTE2 TASK_ACTIVITY,
HAOUTL5.NAME TASK_CARRYING_OUT_ORG,
PTV.WBS_LEVEL TASK_LEVEL,
PTV.TASK_NAME TASK_NAME,
PTV.TASK_NUMBER TASK_NUMBER,
EXT.UNIT_OF_MEASURE UNIT_OF_MEASURE,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
PEIA.PROJECT_CURRENCY_CODE,
PEIA.PROJFUNC_CURRENCY_CODE,
PJAB.PROJECT_STATUS_CODE,
PEIA.ACCT_CURRENCY_CODE
FROM
PJC_COST_DIST_LINES_ALL PCDLA,
HR_OPERATING_UNITS HOUSEC,
PJC_EXP_ITEMS_ALL PEIA,
PJF_EXP_TYPES_B EXT,
PJF_EXP_CATEGORIES_TL PECT,
PJC_EXP_COMMENTS PEC,
PJC_TXN_XFACE_ALL PTF,
PJF_PROJECTS_ALL_B PJAB,
PJF_PROJECTS_ALL_TL PJTL,
PJF_PROJECT_TYPES_B PJT,
PJF_TASKS_V PTV,
POZ_SUPPLIERS_V PSV,
PJB_BILLING_EVENTS PBE,
PJF_NON_LABOR_RES_TL NLRTL,
PJF_SYSTEM_LINKAGES PSL,
PER_ALL_PEOPLE_F PAF,
PER_EMPLOYEES_X PEX,
HR_ALL_ORGANIZATION_UNITS USER_SEC,
HR_ALL_ORGANIZATION_UNITS HAU1,
HR_ALL_ORGANIZATION_UNITS HAU2,
HR_ALL_ORGANIZATION_UNITS HAU4,
HR_ALL_ORGANIZATION_UNITS HAU5,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL1,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL2,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL3,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL4,
HR_ALL_ORGANIZATION_UNITS_TL HAOUTL5,
PER_JOBS PJ,
PER_JOBS PJ2,
PER_JOBS PJ3
WHERE
1 = 1
AND PCDLA.ORG_ID = HOUSEC.ORGANIZATION_ID
AND PCDLA.EXPENDITURE_ITEM_ID = PEIA.EXPENDITURE_ITEM_ID
AND PEIA.EXPENDITURE_TYPE_ID = EXT.EXPENDITURE_TYPE_ID
AND PEC.EXPENDITURE_ITEM_ID(+) = PEIA.EXPENDITURE_ITEM_ID
AND (
PEC.EXPENDITURE_ITEM_ID IS NULL
OR PEC.LINE_NUMBER = (
SELECT
MIN(PEC1.LINE_NUMBER)
FROM
PJC_EXP_COMMENTS PEC1
WHERE
PEC1.EXPENDITURE_ITEM_ID = PEIA.EXPENDITURE_ITEM_ID
)
)
AND PECT.EXPENDITURE_CATEGORY_ID = EXT.EXPENDITURE_CATEGORY_ID
AND PECT.LANGUAGE = USERENV('LANG')
AND PECT.EXPENDITURE_CATEGORY_NAME = 'Labor'
AND PTF.EXPENDITURE_TYPE_ID(+) = EXT.EXPENDITURE_TYPE_ID
AND PTF.CONTEXT_CATEGORY(+) = 'PJC_Expenditure_Batch'
AND PJAB.PROJECT_ID = PCDLA.PROJECT_ID
AND PJAB.PROJECT_ID = PJTL.PROJECT_ID
AND PJTL.LANGUAGE(+) = USERENV('LANG')
AND PJAB.PROJECT_TYPE_ID = PJT.PROJECT_TYPE_ID
AND PCDLA.TASK_ID = PTV.TASK_ID
AND PEIA.VENDOR_ID = PSV.VENDOR_ID(+)
AND PEIA.PROJECT_ID = PBE.PROJECT_ID(+)
AND PEIA.TASK_ID = PBE.TASK_ID(+)
AND PEIA.NON_LABOR_RESOURCE_ID = NLRTL.NON_LABOR_RESOURCE_ID(+)
AND NLRTL.LANGUAGE(+) = USERENV('LANG')
AND PEIA.SYSTEM_LINKAGE_FUNCTION = PSL.FUNCTION(+)
AND PEIA.INCURRED_BY_PERSON_ID = PAF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND PEX.PERSON_ID = PAF.PERSON_ID
AND NVL(PJAB.ORG_ID, -9999) = USER_SEC.ORGANIZATION_ID
AND NVL(
PEIA.OVERRIDE_TO_ORGANIZATION_ID,
PEIA.INCURRED_BY_ORGANIZATION_ID
) = HAU1.ORGANIZATION_ID
AND HAU2.ORGANIZATION_ID(+) = PJAB.CARRYING_OUT_ORGANIZATION_ID
AND HAU4.ORGANIZATION_ID(+) = PEIA.CC_RECVR_ORGANIZATION_ID
AND HAU5.ORGANIZATION_ID = PTV.CARRYING_OUT_ORGANIZATION_ID
AND HAOUTL1.ORGANIZATION_ID(+) = HAU1.ORGANIZATION_ID
AND HAOUTL1.LANGUAGE(+) = USERENV('LANG')
AND HAOUTL2.ORGANIZATION_ID(+) = HAU2.ORGANIZATION_ID
AND HAOUTL2.LANGUAGE(+) = USERENV('LANG')
AND HAOUTL3.ORGANIZATION_ID(+) = PEIA.CC_PRVDR_ORGANIZATION_ID
AND HAOUTL3.LANGUAGE(+) = USERENV('LANG')
AND HAOUTL4.ORGANIZATION_ID(+) = HAU4.ORGANIZATION_ID
AND HAOUTL4.LANGUAGE(+) = USERENV('LANG')
AND HAOUTL5.ORGANIZATION_ID(+) = HAU5.ORGANIZATION_ID
AND HAOUTL5.LANGUAGE(+) = USERENV('LANG')
AND PEIA.COST_JOB_ID = PJ.JOB_ID(+)
AND PEIA.TP_JOB_ID = PJ2.JOB_ID(+)
AND PEIA.PERSON_JOB_ID = PJ3.JOB_ID(+)) PAEI,
PJF_PROJECT_STATUSES_B PPSBM,
HR_ALL_ORGANIZATION_UNITS HAOUM
WHERE 1=1
AND NVL (PAEI.ORG_ID, -9999) = HAOUM.ORGANIZATION_ID
AND PAEI.PROJECT_STATUS_CODE = PPSBM.PROJECT_STATUS_CODE
-- AND PAEI.PROJECT_NUMBER = '00009813'
ORDER BY PAEI.OPERATING_UNIT ,
PAEI.PROJECT_NUMBER,
PAEI.TASK_NUMBER,
PAEI.TASK_LEVEL;