Thursday, 13 July 2017

Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module



WITH PARAMETER AS (SELECT :PERIOD_NAME AS PERIOD_NAME FROM DUAL)
SELECT distinct prj.project_id,prj.segment1,
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period   =PARAMETER.PERIOD_NAME
    -- and organization_id=TXN.organization_id
  ) "MTD Cost",
  (SELECT ROUND(SUM(unbilled_receivable_dr),2)--round(SUM(tot_revenue),2)
  FROM apps.pa_draft_revenues_all
  WHERE project_id              =prj.project_id
  AND TO_CHAR(gl_date,'MON-YY') =PARAMETER.PERIOD_NAME
  ) "MTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
    -- and organization_id=TXN.organization_id
  ) "YTD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
     ) "YTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  ) "ITD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  )"ITD Revenue",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                   =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY')=PARAMETER.PERIOD_NAME
  )"MTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                     =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY') IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
  )"YTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id=prj.project_id
  )"ITD Fee",
  (SELECT SUM(allocated_amount)
  FROM pa_project_fundings
  WHERE project_id=prj.project_id
  )"Funded Revenue",
  (SELECT SUM(a.amount) "Revenue To Date"
  FROM pa.PA_DRAFT_REVENUE_ITEMS# a,
    pa.PA_DRAFT_REVENUES_ALL# b,
    pa.pa_projects_all ppa
  WHERE a.project_id      = ppa.project_id
  AND a.DRAFT_REVENUE_NUM = b.DRAFT_REVENUE_NUM
  AND a.project_id        = b.project_id
  AND b.GL_DATE          <=
    (SELECT END_DATE
    FROM gl.gl_period_statuses
    WHERE end_date =
      (SELECT MAX(end_date)
      FROM gl.gl_period_statuses
      WHERE application_id       = 101
      AND set_of_books_id        = 2022
      AND closing_status         = 'C'
      AND adjustment_period_flag = 'N'
      )
    AND application_id         = 101
    AND set_of_books_id        = 2022
    AND adjustment_period_flag = 'N'
    )
  AND ppa.project_id=prj.project_id
  )"GL Close Revenue"
FROM
    PA.PA_PROJECTS_ALL# PRJ,
  PA.PA_PROJECT_TYPES_ALL# PRJT,
  PA.PA_TXN_ACCUM# TXN,PARAMETER
WHERE prj.project_id                              = txn.project_id
AND prj.project_status_code                     ='APPROVED'
AND prj.PROJECT_TYPE                            = PRJT.PROJECT_TYPE(+)
AND prjt.project_type_class_code                ='CONTRACT'
AND TO_CHAR(week_ending_date , 'MON-RR') = PARAMETER.PERIOD_NAME

2 comments:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete
  2. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete