Tuesday, 17 October 2017

Query to get the Project Expenditures Defined for an Employee for a Specific Period

SELECT
  EXPT.EXPENDITURE_TYPE ,
  EXPI.Quantity ,
  CASE
    WHEN (EXPT.unit_of_measure='HOURS')
    THEN NVL(CDL.PROJECT_BURDENED_COST,cdl.project_raw_cost)
  END Raw_cost ,
  --EXPI.DENOM_RAW_COST
  EXPI.ACCRUED_REVENUE ,
  EXPI.expenditure_item_id ,
  EXPI.expenditure_id ,
  EXPI.EXPENDITURE_ITEM_DATE,
  PAP.GL_PERIOD_NAME GL_PERIOD,
  PAP.PERIOD_NAME PA_PERIOD,
  PAP.START_DATE Period_Start_Date,
  PAP.END_DATE Period_End_Date,
  --  (SELECT
  --DECODE(EXPT.Expenditure_type,'Straight Time',EXPI.Quantity,'Overtime 0.0X',EXPI.Quantity, 'Overtime 1.0X', EXPI.Quantity,'Overtime 1.5X',EXPI.Quantity,'Overtime 2.0X',EXPI.Quantity, 'Overtime 2.5X',EXPI.Quantity,'Premium',EXPI.quantity,0)Total_Hours,
  CASE
    WHEN EXPT.Expenditure_category='Labor'
    AND EXPT.unit_of_measure      ='HOURS'
    THEN EXPI.Quantity
    ELSE 0
  END Total_Hours,
  --  (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN EXPI.QUANTITY
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN EXPI.QUANTITY
        ELSE 0
      END
    ELSE 0
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --      PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --  AND EXPI.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  --  AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  OT_DT_Hours,
  -- (SELECT SUM (
  CASE
    WHEN EXPT.EXPENDITURE_CATEGORY = 'Labor'
    AND EXPT.UNIT_OF_MEASURE       = 'HOURS'
    THEN
      CASE
        WHEN EXPT.EXPENDITURE_TYPE LIKE 'Overtime%'
        THEN 0
        WHEN EXPT.EXPENDITURE_TYPE IN ('Premium','Weekend Premium-Saturday','Weekend Premium-Sunday','Unpaid OT')
        THEN 0
        WHEN EXPT.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 EXPI.QUANTITY
      END
  END
  --)
  --  FROM PA.PA_EXPENDITURE_TYPES EXPT,
  --     PA.PA_EXPENDITURE_ITEMS_ALL EXPI1
  -- WHERE     1 = 1
  --    AND EXP.EXPENDITURE_ID = EXPI1.EXPENDITURE_ID
  -- AND EXPI1.EXPENDITURE_TYPE = EXPT.EXPENDITURE_TYPE)
  Regular_Hours,
  CDL.acct_raw_cost,
  EXP.INCURRED_BY_PERSON_ID
FROM apps.PA_PERIODS_ALL pap,
  apps.PA_COST_DISTRIBUTION_LINES_ALL CDL,
  apps.PA_EXPENDITURE_ITEMS_ALL EXPI,
  apps.PA_EXPENDITURE_TYPES EXPT,
  apps.PA_EXPENDITURES_ALL EXP
WHERE CDL.PA_DATE BETWEEN PAP.START_DATE(+) AND PAP.END_DATE(+)
AND CDL.ORG_ID                 = PAP.ORG_ID(+)
AND PAP.GL_PERIOD_NAME         ='SEP-17'
AND CDL.LINE_NUM(+)            = 1
AND CDL.EXPENDITURE_ITEM_ID(+) = EXPI.EXPENDITURE_ITEM_ID
AND EXPI.EXPENDITURE_TYPE      = EXPT.EXPENDITURE_TYPE
AND EXP.EXPENDITURE_ID         = EXPI.EXPENDITURE_ID
AND EXP.INCURRED_BY_PERSON_ID IS NOT NULL

No comments:

Post a Comment