Oracle Apps project expenditure query

Oracle Apps project expenditure query

we are going to discuss about oracle apps project expenditure query. This is the complete SQL query to extract the details level of project expenditure details in oracle apps. This is one of the most important sql query to fetch the Project expense details in oracle. You can refer this sql query to develop the custom Oracle report related to Oracle project. We will also share the important tables which helps to store the project expenditure details in oracle apps. This below sql query is developed using these tables. We have developed this sql query as per our business requirement. You can alter sql query as per your business needs to fetch the Project Expenditure details in oracle apps. Please find below the detail about Oracle Apps project expenditure query.

Oracle Apps project expenditure query

5 Important Tables used by Oracle Apps project expenditure query

1.PA_PERIODS_ALL
2.PA_COST_DISTRIBUTION_LINES_ALL
3.PA_EXPENDITURE_ITEMS_ALL
4.PA_EXPENDITURE_TYPES
5.PA_EXPENDITURES_ALL

Detail Level Oracle Apps project expenditure query

Here below is the Oracle apps query which helps to extract the oracle apps project expense details.

SELECT
  P_EXPN_TYPE.EXPENDITURE_TYPE  Expense_Type,
  P_EXPN_ITEM.ACCRUED_REVENUE ,
  P_EXPN_ITEM.expenditure_item_id ,
  P_EXPN_ITEM.expenditure_id ,
  P_EXPN_ITEM.EXPENDITURE_ITEM_DATE,
  P_EXPN_ITEM.Quantity "Qty" ,
  PAP.GL_PERIOD_NAME GL_PERIOD,
  PAP.PERIOD_NAME PA_PERIOD,
  PAP.START_DATE Period_Start_Date,
  PAP.END_DATE Period_End_Date,
   CASE
    WHEN (P_EXPN_TYPE.unit_of_measure='HOURS')
    THEN NVL(P_COST_DIST.PROJECT_BURDENED_COST,P_COST_DIST.project_raw_cost)
  END Raw_cost ,
 CASE
    WHEN P_EXPN_TYPE.Expenditure_category='Labor'
    AND P_EXPN_TYPE.unit_of_measure      ='HOURS'
    THEN P_EXPN_ITEM.Quantity
    ELSE 0
  END Total_Hours,
      CASE
        WHEN P_EXPN_TYPE.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN P_EXPN_ITEM.QUANTITY
        WHEN P_EXPN_TYPE.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN P_EXPN_ITEM.QUANTITY
        ELSE 0
      END OT_DT_Hours,
      CASE
        WHEN P_EXPN_TYPE.EXPENDITURE_TYPE NOT IN('Overtime 0.0','Overtime 1.5X','Overtime 2.0X','Overtime 1.0X','Overtime 2.5X','Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT','Union Vac/Supp Dues')
        THEN P_EXPN_ITEM.QUANTITY
      END Regular_Hours,
  P_COST_DIST.acct_raw_cost
FROM apps.PA_PERIODS_ALL pap,
  apps.PA_COST_DISTRIBUTION_LINES_ALL P_COST_DIST,
  apps.PA_EXPENDITURE_ITEMS_ALL P_EXPN_ITEM,
  apps.PA_EXPENDITURE_TYPES P_EXPN_TYPE,
  apps.PA_EXPENDITURES_ALL EXP
WHERE 1=1
AND P_EXPN_ITEM.EXPENDITURE_TYPE      = P_EXPN_TYPE.EXPENDITURE_TYPE
AND P_EXPNS.EXPENDITURE_ID         = P_EXPN_ITEM.EXPENDITURE_ID
AND P_COST_DIST.ORG_ID  = PAP.ORG_ID(+)
AND PAP.GL_PERIOD_NAME =:P_PERIOD_NAME
AND P_COST_DIST.LINE_NUM(+) = 1
AND P_COST_DIST.EXPENDITURE_ITEM_ID(+) = P_EXPN_ITEM.EXPENDITURE_ITEM_ID
AND P_COST_DIST.PA_DATE BETWEEN PAP.START_DATE(+) AND PAP.END_DATE(+)

Oracle Apps project expenditure query