PA Revise Project Budget To Consumption
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Revise Project Budget To Consumption Report (XML)
Application: Projects
Source: AUD: Revise Project Budget To Consumption
Short Name: PAXREVBU
DB package:
Description: Revise Project Budget To Consumption Report (XML)
Application: Projects
Source: AUD: Revise Project Budget To Consumption
Short Name: PAXREVBU
DB package:
Run
PA Revise Project Budget To Consumption and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT HRO.NAME Project_Organization, PROJ.Segment1 Project_Number, BT.Budget_Type Budget_Type, (SELECT SUM(BAL.BUDGET_PERIOD_TO_DATE) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) Original_Budget_Amount, (SELECT SUM(DECODE(BAL.BALANCE_TYPE,'REQ',BAL.ENCUMB_PERIOD_TO_DATE,0)) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) Commitments, (SELECT SUM(DECODE(BAL.BALANCE_TYPE,'PO',BAL.ENCUMB_PERIOD_TO_DATE,0)) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) Obligations, (SELECT SUM(DECODE(BAL.BALANCE_TYPE,'AP',BAL.ENCUMB_PERIOD_TO_DATE,0)) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) Invoices, (SELECT SUM(DECODE(BAL.BALANCE_TYPE,'EXP',BAL.ACTUAL_PERIOD_TO_DATE,0)) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) Expenditures, NVL(BCO.REVISE_BUDGET_AMOUNT,0) Unconsumed_Funds, ((SELECT SUM(BAL.BUDGET_PERIOD_TO_DATE) FROM PA_BC_BALANCES BAL, PA_Implementations IMP WHERE BAL.budget_version_id = PBV2.budget_version_id AND BAL.Set_Of_Books_ID = IMP.Set_Of_Books_ID AND IMP.org_id = PROJ.org_id AND BAL.Start_Date IN (SELECT GPS.Start_Date FROM GL_Period_Statuses GPS, GL_Sets_Of_Books SOB WHERE GPS.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND SOB.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS.APPLICATION_ID = 101 AND GPS.PERIOD_YEAR = (SELECT GPS2.PERIOD_YEAR FROM GL_Period_Statuses GPS2 WHERE GPS2.SET_OF_BOOKS_ID = IMP.Set_Of_Books_ID AND GPS2.APPLICATION_ID = 101 AND GPS2.PERIOD_NAME = BCO.REVISE_BUDGET_PERIOD AND NVL(GPS2.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) AND GPS.Period_Type = SOB.Accounted_Period_Type AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG,'N') <> 'Y' ) GROUP BY BAL.budget_version_id) - NVL(BCO.REVISE_BUDGET_AMOUNT,0)) Revised_Budget_Amount, CASE WHEN PBV1.WF_STATUS_CODE IS NOT NULL THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='WF_STATUS_CODE' AND LOOKUP_CODE = PBV1.WF_STATUS_CODE) WHEN PBV1.WF_STATUS_CODE IS NULL AND PBV1.LAST_UPDATE_DATE > PBV2.CREATION_DATE THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV1.BUDGET_STATUS_CODE) WHEN PBV1.WF_STATUS_CODE IS NULL AND PBV1.LAST_UPDATE_DATE <= PBV2.CREATION_DATE THEN (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV2.BUDGET_STATUS_CODE) ELSE (SELECT MEANING FROM PA_LOOKUPS WHERE LOOKUP_TYPE ='BUDGET STATUS' AND LOOKUP_CODE = PBV1.BUDGET_STATUS_CODE) END Budget_Status_Post_Revision FROM PA_Projects_All PROJ, PA_Budget_Types BT, PA_Budgetary_Control_Options BCO, PA_BUDGET_VERSIONS PBV1, PA_BUDGET_VERSIONS PBV2, HR_ALL_ORGANIZATION_UNITS HRO WHERE BCO.REVISE_BUDGET_FLAG = 'S' AND BCO.REVISE_BUDGET_REQUEST_ID = :P_Conc_Request_ID AND PROJ.Carrying_Out_Organization_ID = NVL(:P_Organization_ID, PROJ.Carrying_Out_Organization_ID) AND PROJ.Carrying_Out_Organization_ID = HRO.ORGANIZATION_ID AND BCO.Project_ID = PROJ.Project_ID AND BCO.Budget_Type_Code = BT.Budget_Type_Code AND PBV1.PROJECT_ID = PROJ.Project_ID AND PBV1.Budget_Type_Code = BCO.Budget_Type_Code AND PBV1.BUDGET_STATUS_CODE IN ('S', 'W') AND PBV2.PROJECT_ID = PROJ.Project_ID AND PBV2.Budget_Type_Code = BCO.Budget_Type_Code AND PBV2.BUDGET_STATUS_CODE = 'B' AND PBV2.CURRENT_FLAG = 'Y' order by 1,2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |