Introduction
This Post illustrates the steps required to finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module Oracle EBS R12.
Script to 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
What we expect in the script
This script helps us to comprehend finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module.
Summary
This Post described the script Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
