eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_VRM_SOURCE_DOC_LINES

Source

View Text - Preformatted


SELECT pbv.PROJECT_ID AS PROJECT_ID, 'Revenue' AS BUDGET_TYPE, 'Revision' AS TYPE, pbv.BUDGET_VERSION_ID AS BUDGET_VERSION_ID, pbv.VERSION_NAME AS BUDGET_VERSION_NAME, pbv.VERSION_NUMBER AS BUDGET_VERSION_NUMBER, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK',11,10) AS REVENUE_ACCRUAL_METHOD, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK','Time and Material Project','Fixed Bid Project') AS MEMO_LINE_NAME, 1 AS QUANTITY, pbv.REVENUE AS REVENUE_AMOUNT, NULL AS COST_AMOUNT, pbv.CREATION_DATE AS PROJECT_CREATION_DATE, pbv.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE, pbv.ATTRIBUTE_CATEGORY AS REV_ATTRIBUTE_CATEGORY, pbv.ATTRIBUTE1 AS REV_ATTRIBUTE1, pbv.ATTRIBUTE2 AS REV_ATTRIBUTE2, pbv.ATTRIBUTE3 AS REV_ATTRIBUTE3, pbv.ATTRIBUTE4 AS REV_ATTRIBUTE4, pbv.ATTRIBUTE5 AS REV_ATTRIBUTE5, pbv.ATTRIBUTE6 AS REV_ATTRIBUTE6, pbv.ATTRIBUTE7 AS REV_ATTRIBUTE7, pbv.ATTRIBUTE8 AS REV_ATTRIBUTE8, pbv.ATTRIBUTE9 AS REV_ATTRIBUTE9, pbv.ATTRIBUTE10 AS REV_ATTRIBUTE10, pbv.ATTRIBUTE11 AS REV_ATTRIBUTE11, pbv.ATTRIBUTE12 AS REV_ATTRIBUTE12, pbv.ATTRIBUTE13 AS REV_ATTRIBUTE13, pbv.ATTRIBUTE14 AS REV_ATTRIBUTE14, pbv.ATTRIBUTE15 AS REV_ATTRIBUTE15, NULL AS COST_ATTRIBUTE_CATEGORY, NULL AS COST_ATTRIBUTE1, NULL AS COST_ATTRIBUTE2, NULL AS COST_ATTRIBUTE3, NULL AS COST_ATTRIBUTE4, NULL AS COST_ATTRIBUTE5, NULL AS COST_ATTRIBUTE6, NULL AS COST_ATTRIBUTE7, NULL AS COST_ATTRIBUTE8, NULL AS COST_ATTRIBUTE9, NULL AS COST_ATTRIBUTE10, NULL AS COST_ATTRIBUTE11, NULL AS COST_ATTRIBUTE12, NULL AS COST_ATTRIBUTE13, NULL AS COST_ATTRIBUTE14, NULL AS COST_ATTRIBUTE15, PA_VRM_UTILS.GET_REVENUE_DIFF_AMOUNT(pbv.PROJECT_ID,pbv.version_number ) AS REV_REVISION_AMOUNT, 0 AS COST_REVISION_AMOUNT, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(pbv.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID, 'Revenue' AS INITIAL_BUDGET_TYPE, 1 AS LINE_NUMBER, NULL AS DELIVERED_FLAG, NULL AS DELIVERED_DATE, pr.ORG_ID AS ORG_ID FROM PA_BUDGET_VERSIONS pbv, PA_PROJECTS_ALL pr WHERE pr.PROJECT_ID = pbv.PROJECT_ID AND pbv.BUDGET_STATUS_CODE = 'B' AND pbv.ci_id IS NULL AND (NVL(pbv.APPROVED_REV_PLAN_TYPE_FLAG,'N') = 'Y') AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(pbv.PROJECT_ID,pbv.VERSION_NUMBER,'R') = 'N' -- C for Cost and R for Revenue AND PA_VRM_UTILS.GET_REVENUE_DIFF_AMOUNT(pbv.PROJECT_ID,pbv.version_number ) <> 0 AND EXISTS (SELECT * FROM PA_BUDGET_VERSIONS pbv2 WHERE pbv2.PROJECT_ID = pr.PROJECT_ID AND NVL(pbv2.APPROVED_COST_PLAN_TYPE_FLAG,'N') = 'Y' AND pbv2.BUDGET_STATUS_CODE = 'B' ) UNION SELECT pbv.PROJECT_ID AS PROJECT_ID, 'Cost' AS BUDGET_TYPE, 'Revision' AS TYPE, pbv.BUDGET_VERSION_ID AS BUDGET_VERSION_ID, pbv.VERSION_NAME AS BUDGET_VERSION_NAME, pbv.VERSION_NUMBER AS BUDGET_VERSION_NUMBER, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK',11,10) AS REVENUE_ACCRUAL_METHOD, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK','Time and Material Project','Fixed Bid Project') AS MEMO_LINE_NAME, 1 AS QUANTITY, pbv.REVENUE AS REVENUE_AMOUNT, pbv.BURDENED_COST AS COST_AMOUNT, pr.CREATION_DATE AS PROJECT_CREATION_DATE, pr.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE, NULL AS REV_ATTRIBUTE_CATEGORY, NULL AS REV_ATTRIBUTE1, NULL AS REV_ATTRIBUTE2, NULL AS REV_ATTRIBUTE3, NULL AS REV_ATTRIBUTE4, NULL AS REV_ATTRIBUTE5, NULL AS REV_ATTRIBUTE6, NULL AS REV_ATTRIBUTE7, NULL AS REV_ATTRIBUTE8, NULL AS REV_ATTRIBUTE9, NULL AS REV_ATTRIBUTE10, NULL AS REV_ATTRIBUTE11, NULL AS REV_ATTRIBUTE12, NULL AS REV_ATTRIBUTE13, NULL AS REV_ATTRIBUTE14, NULL AS REV_ATTRIBUTE15, pbv.ATTRIBUTE_CATEGORY AS COST_ATTRIBUTE_CATEGORY, pbv.ATTRIBUTE1 AS COST_ATTRIBUTE1, pbv.ATTRIBUTE2 AS COST_ATTRIBUTE2, pbv.ATTRIBUTE3 AS COST_ATTRIBUTE3, pbv.ATTRIBUTE4 AS COST_ATTRIBUTE4, pbv.ATTRIBUTE5 AS COST_ATTRIBUTE5, pbv.ATTRIBUTE6 AS COST_ATTRIBUTE6, pbv.ATTRIBUTE7 AS COST_ATTRIBUTE7, pbv.ATTRIBUTE8 AS COST_ATTRIBUTE8, pbv.ATTRIBUTE9 AS COST_ATTRIBUTE9, pbv.ATTRIBUTE10 AS COST_ATTRIBUTE10, pbv.ATTRIBUTE11 AS COST_ATTRIBUTE11, pbv.ATTRIBUTE12 AS COST_ATTRIBUTE12, pbv.ATTRIBUTE13 AS COST_ATTRIBUTE13, pbv.ATTRIBUTE14 AS COST_ATTRIBUTE14, pbv.ATTRIBUTE15 AS COST_ATTRIBUTE15, 0 AS REV_REVISION_AMOUNT, PA_VRM_UTILS.GET_COST_DIFF_AMOUNT(pbv.PROJECT_ID,pbv.version_number ) AS COST_REVISION_AMOUNT, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(pbv.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID, 'Revenue' AS INITIAL_BUDGET_TYPE, 1 AS LINE_NUMBER, NULL AS DELIVERED_FLAG, NULL AS DELIVERED_DATE, pr.ORG_ID AS ORG_ID FROM PA_BUDGET_VERSIONS pbv, PA_PROJECTS_ALL pr WHERE pr.PROJECT_ID = pbv.PROJECT_ID AND pbv.BUDGET_STATUS_CODE = 'B' AND pbv.ci_id IS NULL AND (NVL(pbv.APPROVED_COST_PLAN_TYPE_FLAG,'N') = 'Y') AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(pbv.PROJECT_ID,pbv.VERSION_NUMBER,'C') = 'N' -- C for Cost and R for Revenue AND PA_VRM_UTILS.GET_COST_DIFF_AMOUNT(pbv.PROJECT_ID,pbv.version_number ) <> 0 AND EXISTS (SELECT * FROM PA_BUDGET_VERSIONS pbv2 WHERE pbv2.PROJECT_ID = pr.PROJECT_ID AND NVL(pbv2.APPROVED_REV_PLAN_TYPE_FLAG,'N') = 'Y' AND pbv2.BUDGET_STATUS_CODE = 'B' ) UNION SELECT pbv.PROJECT_ID AS PROJECT_ID, 'Revenue' AS BUDGET_TYPE, 'Original' AS TYPE, pbv.BUDGET_VERSION_ID AS BUDGET_VERSION_ID, pbv.VERSION_NAME AS BUDGET_VERSION_NAME, pbv.VERSION_NUMBER AS BUDGET_VERSION_NUMBER, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK',11,10) AS REVENUE_ACCRUAL_METHOD, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK','Time and Material Project','Fixed Bid Project') AS MEMO_LINE_NAME, 1 AS QUANTITY, pbv.REVENUE AS REVENUE_AMOUNT, costPbv.BURDENED_COST AS COST_AMOUNT, pr.CREATION_DATE AS PROJECT_CREATION_DATE, pr.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE, pbv.ATTRIBUTE_CATEGORY AS REV_ATTRIBUTE_CATEGORY, pbv.ATTRIBUTE1 AS REV_ATTRIBUTE1, pbv.ATTRIBUTE2 AS REV_ATTRIBUTE2, pbv.ATTRIBUTE3 AS REV_ATTRIBUTE3, pbv.ATTRIBUTE4 AS REV_ATTRIBUTE4, pbv.ATTRIBUTE5 AS REV_ATTRIBUTE5, pbv.ATTRIBUTE6 AS REV_ATTRIBUTE6, pbv.ATTRIBUTE7 AS REV_ATTRIBUTE7, pbv.ATTRIBUTE8 AS REV_ATTRIBUTE8, pbv.ATTRIBUTE9 AS REV_ATTRIBUTE9, pbv.ATTRIBUTE10 AS REV_ATTRIBUTE10, pbv.ATTRIBUTE11 AS REV_ATTRIBUTE11, pbv.ATTRIBUTE12 AS REV_ATTRIBUTE12, pbv.ATTRIBUTE13 AS REV_ATTRIBUTE13, pbv.ATTRIBUTE14 AS REV_ATTRIBUTE14, pbv.ATTRIBUTE15 AS REV_ATTRIBUTE15, costPbv.ATTRIBUTE_CATEGORY AS COST_ATTRIBUTE_CATEGORY, costPbv.ATTRIBUTE1 AS COST_ATTRIBUTE1, costPbv.ATTRIBUTE2 AS COST_ATTRIBUTE2, costPbv.ATTRIBUTE3 AS COST_ATTRIBUTE3, costPbv.ATTRIBUTE4 AS COST_ATTRIBUTE4, costPbv.ATTRIBUTE5 AS COST_ATTRIBUTE5, costPbv.ATTRIBUTE6 AS COST_ATTRIBUTE6, costPbv.ATTRIBUTE7 AS COST_ATTRIBUTE7, costPbv.ATTRIBUTE8 AS COST_ATTRIBUTE8, costPbv.ATTRIBUTE9 AS COST_ATTRIBUTE9, costPbv.ATTRIBUTE10 AS COST_ATTRIBUTE10, costPbv.ATTRIBUTE11 AS COST_ATTRIBUTE11, costPbv.ATTRIBUTE12 AS COST_ATTRIBUTE12, costPbv.ATTRIBUTE13 AS COST_ATTRIBUTE13, costPbv.ATTRIBUTE14 AS COST_ATTRIBUTE14, costPbv.ATTRIBUTE15 AS COST_ATTRIBUTE15, pbv.REVENUE AS REV_REVISION_AMOUNT, costPbv.BURDENED_COST AS COST_REVISION_AMOUNT, NULL AS INITIAL_BASELINED_VERSION_ID, NULL AS INITIAL_BUDGET_TYPE, 1 AS LINE_NUMBER, NULL AS DELIVERED_FLAG, NULL AS DELIVERED_DATE, pr.ORG_ID AS ORG_ID FROM PA_BUDGET_VERSIONS pbv, PA_PROJECTS_ALL pr, PA_BUDGET_VERSIONS costPbv WHERE pr.PROJECT_ID = pbv.PROJECT_ID AND pbv.BUDGET_STATUS_CODE = 'B' AND pbv.ci_id IS NULL AND (NVL(pbv.APPROVED_REV_PLAN_TYPE_FLAG,'N') = 'Y') AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(pbv.PROJECT_ID,pbv.VERSION_NUMBER,'R') = 'Y' AND pr.PROJECT_ID = costPbv.PROJECT_ID AND costPbv.BUDGET_STATUS_CODE = 'B' AND costPbv.ci_id IS NULL AND (NVL(costPbv.APPROVED_COST_PLAN_TYPE_FLAG,'N') = 'Y') AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(costPbv.PROJECT_ID,costPbv.VERSION_NUMBER,'C') = 'Y' UNION SELECT pbv.PROJECT_ID AS PROJECT_ID, 'Close' AS BUDGET_TYPE, 'Revision' AS TYPE, pbv.PROJECT_ID * -1 AS BUDGET_VERSION_ID, NULL AS BUDGET_VERSION_NAME, NULL AS BUDGET_VERSION_NUMBER, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK',11,10) AS REVENUE_ACCRUAL_METHOD, DECODE(pr.REVENUE_ACCRUAL_METHOD,'WORK','Time and Material Project','Fixed Bid Project') AS MEMO_LINE_NAME, 1 AS QUANTITY, 0 AS REVENUE_AMOUNT, 0 AS COST_AMOUNT, pr.CREATION_DATE AS PROJECT_CREATION_DATE, pr.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE, NULL AS REV_ATTRIBUTE_CATEGORY, NULL AS REV_ATTRIBUTE1, NULL AS REV_ATTRIBUTE2, NULL AS REV_ATTRIBUTE3, NULL AS REV_ATTRIBUTE4, NULL AS REV_ATTRIBUTE5, NULL AS REV_ATTRIBUTE6, NULL AS REV_ATTRIBUTE7, NULL AS REV_ATTRIBUTE8, NULL AS REV_ATTRIBUTE9, NULL AS REV_ATTRIBUTE10, NULL AS REV_ATTRIBUTE11, NULL AS REV_ATTRIBUTE12, NULL AS REV_ATTRIBUTE13, NULL AS REV_ATTRIBUTE14, NULL AS REV_ATTRIBUTE15, NULL AS COST_ATTRIBUTE_CATEGORY, NULL AS COST_ATTRIBUTE1, NULL AS COST_ATTRIBUTE2, NULL AS COST_ATTRIBUTE3, NULL AS COST_ATTRIBUTE4, NULL AS COST_ATTRIBUTE5, NULL AS COST_ATTRIBUTE6, NULL AS COST_ATTRIBUTE7, NULL AS COST_ATTRIBUTE8, NULL AS COST_ATTRIBUTE9, NULL AS COST_ATTRIBUTE10, NULL AS COST_ATTRIBUTE11, NULL AS COST_ATTRIBUTE12, NULL AS COST_ATTRIBUTE13, NULL AS COST_ATTRIBUTE14, NULL AS COST_ATTRIBUTE15, 0 AS REV_REVISION_AMOUNT, 0 AS COST_REVISION_AMOUNT, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(pbv.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID, 'Revenue' AS INITIAL_BUDGET_TYPE, 1 AS LINE_NUMBER, 'Y' AS DELIVERED_FLAG, closed_date AS DELIVERED_DATE, pr.ORG_ID AS ORG_ID FROM PA_BUDGET_VERSIONS pbv, PA_PROJECTS_ALL pr, PA_PROJECT_STATUSES pps WHERE pr.PROJECT_ID = pbv.PROJECT_ID AND pbv.BUDGET_STATUS_CODE = 'B' AND pbv.ci_id IS NULL AND (NVL(pbv.APPROVED_REV_PLAN_TYPE_FLAG,'N') = 'Y') AND pr.project_status_code = pps.project_status_code AND pps.status_type = 'PROJECT' AND pps.project_system_status_code = 'CLOSED' AND pr.closed_date IS NOT NULL AND EXISTS (SELECT 1 FROM PA_BUDGET_VERSIONS pbv2 WHERE pbv2.PROJECT_ID = pr.PROJECT_ID AND NVL(pbv2.APPROVED_REV_PLAN_TYPE_FLAG,'N') = 'Y' AND pbv2.BUDGET_STATUS_CODE = 'B' AND pbv2.ci_id IS NULL )

View Text - HTML Formatted

SELECT PBV.PROJECT_ID AS PROJECT_ID
, 'REVENUE' AS BUDGET_TYPE
, 'REVISION' AS TYPE
, PBV.BUDGET_VERSION_ID AS BUDGET_VERSION_ID
, PBV.VERSION_NAME AS BUDGET_VERSION_NAME
, PBV.VERSION_NUMBER AS BUDGET_VERSION_NUMBER
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 11
, 10) AS REVENUE_ACCRUAL_METHOD
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 'TIME
AND MATERIAL PROJECT'
, 'FIXED BID PROJECT') AS MEMO_LINE_NAME
, 1 AS QUANTITY
, PBV.REVENUE AS REVENUE_AMOUNT
, NULL AS COST_AMOUNT
, PBV.CREATION_DATE AS PROJECT_CREATION_DATE
, PBV.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE
, PBV.ATTRIBUTE_CATEGORY AS REV_ATTRIBUTE_CATEGORY
, PBV.ATTRIBUTE1 AS REV_ATTRIBUTE1
, PBV.ATTRIBUTE2 AS REV_ATTRIBUTE2
, PBV.ATTRIBUTE3 AS REV_ATTRIBUTE3
, PBV.ATTRIBUTE4 AS REV_ATTRIBUTE4
, PBV.ATTRIBUTE5 AS REV_ATTRIBUTE5
, PBV.ATTRIBUTE6 AS REV_ATTRIBUTE6
, PBV.ATTRIBUTE7 AS REV_ATTRIBUTE7
, PBV.ATTRIBUTE8 AS REV_ATTRIBUTE8
, PBV.ATTRIBUTE9 AS REV_ATTRIBUTE9
, PBV.ATTRIBUTE10 AS REV_ATTRIBUTE10
, PBV.ATTRIBUTE11 AS REV_ATTRIBUTE11
, PBV.ATTRIBUTE12 AS REV_ATTRIBUTE12
, PBV.ATTRIBUTE13 AS REV_ATTRIBUTE13
, PBV.ATTRIBUTE14 AS REV_ATTRIBUTE14
, PBV.ATTRIBUTE15 AS REV_ATTRIBUTE15
, NULL AS COST_ATTRIBUTE_CATEGORY
, NULL AS COST_ATTRIBUTE1
, NULL AS COST_ATTRIBUTE2
, NULL AS COST_ATTRIBUTE3
, NULL AS COST_ATTRIBUTE4
, NULL AS COST_ATTRIBUTE5
, NULL AS COST_ATTRIBUTE6
, NULL AS COST_ATTRIBUTE7
, NULL AS COST_ATTRIBUTE8
, NULL AS COST_ATTRIBUTE9
, NULL AS COST_ATTRIBUTE10
, NULL AS COST_ATTRIBUTE11
, NULL AS COST_ATTRIBUTE12
, NULL AS COST_ATTRIBUTE13
, NULL AS COST_ATTRIBUTE14
, NULL AS COST_ATTRIBUTE15
, PA_VRM_UTILS.GET_REVENUE_DIFF_AMOUNT(PBV.PROJECT_ID
, PBV.VERSION_NUMBER ) AS REV_REVISION_AMOUNT
, 0 AS COST_REVISION_AMOUNT
, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(PBV.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID
, 'REVENUE' AS INITIAL_BUDGET_TYPE
, 1 AS LINE_NUMBER
, NULL AS DELIVERED_FLAG
, NULL AS DELIVERED_DATE
, PR.ORG_ID AS ORG_ID
FROM PA_BUDGET_VERSIONS PBV
, PA_PROJECTS_ALL PR
WHERE PR.PROJECT_ID = PBV.PROJECT_ID
AND PBV.BUDGET_STATUS_CODE = 'B'
AND PBV.CI_ID IS NULL
AND (NVL(PBV.APPROVED_REV_PLAN_TYPE_FLAG
, 'N') = 'Y')
AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(PBV.PROJECT_ID
, PBV.VERSION_NUMBER
, 'R') = 'N' -- C FOR COST
AND R FOR REVENUE
AND PA_VRM_UTILS.GET_REVENUE_DIFF_AMOUNT(PBV.PROJECT_ID
, PBV.VERSION_NUMBER ) <> 0
AND EXISTS (SELECT *
FROM PA_BUDGET_VERSIONS PBV2
WHERE PBV2.PROJECT_ID = PR.PROJECT_ID
AND NVL(PBV2.APPROVED_COST_PLAN_TYPE_FLAG
, 'N') = 'Y'
AND PBV2.BUDGET_STATUS_CODE = 'B' ) UNION SELECT PBV.PROJECT_ID AS PROJECT_ID
, 'COST' AS BUDGET_TYPE
, 'REVISION' AS TYPE
, PBV.BUDGET_VERSION_ID AS BUDGET_VERSION_ID
, PBV.VERSION_NAME AS BUDGET_VERSION_NAME
, PBV.VERSION_NUMBER AS BUDGET_VERSION_NUMBER
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 11
, 10) AS REVENUE_ACCRUAL_METHOD
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 'TIME
AND MATERIAL PROJECT'
, 'FIXED BID PROJECT') AS MEMO_LINE_NAME
, 1 AS QUANTITY
, PBV.REVENUE AS REVENUE_AMOUNT
, PBV.BURDENED_COST AS COST_AMOUNT
, PR.CREATION_DATE AS PROJECT_CREATION_DATE
, PR.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE
, NULL AS REV_ATTRIBUTE_CATEGORY
, NULL AS REV_ATTRIBUTE1
, NULL AS REV_ATTRIBUTE2
, NULL AS REV_ATTRIBUTE3
, NULL AS REV_ATTRIBUTE4
, NULL AS REV_ATTRIBUTE5
, NULL AS REV_ATTRIBUTE6
, NULL AS REV_ATTRIBUTE7
, NULL AS REV_ATTRIBUTE8
, NULL AS REV_ATTRIBUTE9
, NULL AS REV_ATTRIBUTE10
, NULL AS REV_ATTRIBUTE11
, NULL AS REV_ATTRIBUTE12
, NULL AS REV_ATTRIBUTE13
, NULL AS REV_ATTRIBUTE14
, NULL AS REV_ATTRIBUTE15
, PBV.ATTRIBUTE_CATEGORY AS COST_ATTRIBUTE_CATEGORY
, PBV.ATTRIBUTE1 AS COST_ATTRIBUTE1
, PBV.ATTRIBUTE2 AS COST_ATTRIBUTE2
, PBV.ATTRIBUTE3 AS COST_ATTRIBUTE3
, PBV.ATTRIBUTE4 AS COST_ATTRIBUTE4
, PBV.ATTRIBUTE5 AS COST_ATTRIBUTE5
, PBV.ATTRIBUTE6 AS COST_ATTRIBUTE6
, PBV.ATTRIBUTE7 AS COST_ATTRIBUTE7
, PBV.ATTRIBUTE8 AS COST_ATTRIBUTE8
, PBV.ATTRIBUTE9 AS COST_ATTRIBUTE9
, PBV.ATTRIBUTE10 AS COST_ATTRIBUTE10
, PBV.ATTRIBUTE11 AS COST_ATTRIBUTE11
, PBV.ATTRIBUTE12 AS COST_ATTRIBUTE12
, PBV.ATTRIBUTE13 AS COST_ATTRIBUTE13
, PBV.ATTRIBUTE14 AS COST_ATTRIBUTE14
, PBV.ATTRIBUTE15 AS COST_ATTRIBUTE15
, 0 AS REV_REVISION_AMOUNT
, PA_VRM_UTILS.GET_COST_DIFF_AMOUNT(PBV.PROJECT_ID
, PBV.VERSION_NUMBER ) AS COST_REVISION_AMOUNT
, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(PBV.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID
, 'REVENUE' AS INITIAL_BUDGET_TYPE
, 1 AS LINE_NUMBER
, NULL AS DELIVERED_FLAG
, NULL AS DELIVERED_DATE
, PR.ORG_ID AS ORG_ID
FROM PA_BUDGET_VERSIONS PBV
, PA_PROJECTS_ALL PR
WHERE PR.PROJECT_ID = PBV.PROJECT_ID
AND PBV.BUDGET_STATUS_CODE = 'B'
AND PBV.CI_ID IS NULL
AND (NVL(PBV.APPROVED_COST_PLAN_TYPE_FLAG
, 'N') = 'Y')
AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(PBV.PROJECT_ID
, PBV.VERSION_NUMBER
, 'C') = 'N' -- C FOR COST
AND R FOR REVENUE
AND PA_VRM_UTILS.GET_COST_DIFF_AMOUNT(PBV.PROJECT_ID
, PBV.VERSION_NUMBER ) <> 0
AND EXISTS (SELECT *
FROM PA_BUDGET_VERSIONS PBV2
WHERE PBV2.PROJECT_ID = PR.PROJECT_ID
AND NVL(PBV2.APPROVED_REV_PLAN_TYPE_FLAG
, 'N') = 'Y'
AND PBV2.BUDGET_STATUS_CODE = 'B' ) UNION SELECT PBV.PROJECT_ID AS PROJECT_ID
, 'REVENUE' AS BUDGET_TYPE
, 'ORIGINAL' AS TYPE
, PBV.BUDGET_VERSION_ID AS BUDGET_VERSION_ID
, PBV.VERSION_NAME AS BUDGET_VERSION_NAME
, PBV.VERSION_NUMBER AS BUDGET_VERSION_NUMBER
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 11
, 10) AS REVENUE_ACCRUAL_METHOD
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 'TIME
AND MATERIAL PROJECT'
, 'FIXED BID PROJECT') AS MEMO_LINE_NAME
, 1 AS QUANTITY
, PBV.REVENUE AS REVENUE_AMOUNT
, COSTPBV.BURDENED_COST AS COST_AMOUNT
, PR.CREATION_DATE AS PROJECT_CREATION_DATE
, PR.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE
, PBV.ATTRIBUTE_CATEGORY AS REV_ATTRIBUTE_CATEGORY
, PBV.ATTRIBUTE1 AS REV_ATTRIBUTE1
, PBV.ATTRIBUTE2 AS REV_ATTRIBUTE2
, PBV.ATTRIBUTE3 AS REV_ATTRIBUTE3
, PBV.ATTRIBUTE4 AS REV_ATTRIBUTE4
, PBV.ATTRIBUTE5 AS REV_ATTRIBUTE5
, PBV.ATTRIBUTE6 AS REV_ATTRIBUTE6
, PBV.ATTRIBUTE7 AS REV_ATTRIBUTE7
, PBV.ATTRIBUTE8 AS REV_ATTRIBUTE8
, PBV.ATTRIBUTE9 AS REV_ATTRIBUTE9
, PBV.ATTRIBUTE10 AS REV_ATTRIBUTE10
, PBV.ATTRIBUTE11 AS REV_ATTRIBUTE11
, PBV.ATTRIBUTE12 AS REV_ATTRIBUTE12
, PBV.ATTRIBUTE13 AS REV_ATTRIBUTE13
, PBV.ATTRIBUTE14 AS REV_ATTRIBUTE14
, PBV.ATTRIBUTE15 AS REV_ATTRIBUTE15
, COSTPBV.ATTRIBUTE_CATEGORY AS COST_ATTRIBUTE_CATEGORY
, COSTPBV.ATTRIBUTE1 AS COST_ATTRIBUTE1
, COSTPBV.ATTRIBUTE2 AS COST_ATTRIBUTE2
, COSTPBV.ATTRIBUTE3 AS COST_ATTRIBUTE3
, COSTPBV.ATTRIBUTE4 AS COST_ATTRIBUTE4
, COSTPBV.ATTRIBUTE5 AS COST_ATTRIBUTE5
, COSTPBV.ATTRIBUTE6 AS COST_ATTRIBUTE6
, COSTPBV.ATTRIBUTE7 AS COST_ATTRIBUTE7
, COSTPBV.ATTRIBUTE8 AS COST_ATTRIBUTE8
, COSTPBV.ATTRIBUTE9 AS COST_ATTRIBUTE9
, COSTPBV.ATTRIBUTE10 AS COST_ATTRIBUTE10
, COSTPBV.ATTRIBUTE11 AS COST_ATTRIBUTE11
, COSTPBV.ATTRIBUTE12 AS COST_ATTRIBUTE12
, COSTPBV.ATTRIBUTE13 AS COST_ATTRIBUTE13
, COSTPBV.ATTRIBUTE14 AS COST_ATTRIBUTE14
, COSTPBV.ATTRIBUTE15 AS COST_ATTRIBUTE15
, PBV.REVENUE AS REV_REVISION_AMOUNT
, COSTPBV.BURDENED_COST AS COST_REVISION_AMOUNT
, NULL AS INITIAL_BASELINED_VERSION_ID
, NULL AS INITIAL_BUDGET_TYPE
, 1 AS LINE_NUMBER
, NULL AS DELIVERED_FLAG
, NULL AS DELIVERED_DATE
, PR.ORG_ID AS ORG_ID
FROM PA_BUDGET_VERSIONS PBV
, PA_PROJECTS_ALL PR
, PA_BUDGET_VERSIONS COSTPBV
WHERE PR.PROJECT_ID = PBV.PROJECT_ID
AND PBV.BUDGET_STATUS_CODE = 'B'
AND PBV.CI_ID IS NULL
AND (NVL(PBV.APPROVED_REV_PLAN_TYPE_FLAG
, 'N') = 'Y')
AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(PBV.PROJECT_ID
, PBV.VERSION_NUMBER
, 'R') = 'Y'
AND PR.PROJECT_ID = COSTPBV.PROJECT_ID
AND COSTPBV.BUDGET_STATUS_CODE = 'B'
AND COSTPBV.CI_ID IS NULL
AND (NVL(COSTPBV.APPROVED_COST_PLAN_TYPE_FLAG
, 'N') = 'Y')
AND PA_VRM_UTILS.IS_INITIAL_BASELINED_VER(COSTPBV.PROJECT_ID
, COSTPBV.VERSION_NUMBER
, 'C') = 'Y' UNION SELECT PBV.PROJECT_ID AS PROJECT_ID
, 'CLOSE' AS BUDGET_TYPE
, 'REVISION' AS TYPE
, PBV.PROJECT_ID * -1 AS BUDGET_VERSION_ID
, NULL AS BUDGET_VERSION_NAME
, NULL AS BUDGET_VERSION_NUMBER
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 11
, 10) AS REVENUE_ACCRUAL_METHOD
, DECODE(PR.REVENUE_ACCRUAL_METHOD
, 'WORK'
, 'TIME
AND MATERIAL PROJECT'
, 'FIXED BID PROJECT') AS MEMO_LINE_NAME
, 1 AS QUANTITY
, 0 AS REVENUE_AMOUNT
, 0 AS COST_AMOUNT
, PR.CREATION_DATE AS PROJECT_CREATION_DATE
, PR.LAST_UPDATE_DATE AS PROJECT_LAST_UPDATE_DATE
, NULL AS REV_ATTRIBUTE_CATEGORY
, NULL AS REV_ATTRIBUTE1
, NULL AS REV_ATTRIBUTE2
, NULL AS REV_ATTRIBUTE3
, NULL AS REV_ATTRIBUTE4
, NULL AS REV_ATTRIBUTE5
, NULL AS REV_ATTRIBUTE6
, NULL AS REV_ATTRIBUTE7
, NULL AS REV_ATTRIBUTE8
, NULL AS REV_ATTRIBUTE9
, NULL AS REV_ATTRIBUTE10
, NULL AS REV_ATTRIBUTE11
, NULL AS REV_ATTRIBUTE12
, NULL AS REV_ATTRIBUTE13
, NULL AS REV_ATTRIBUTE14
, NULL AS REV_ATTRIBUTE15
, NULL AS COST_ATTRIBUTE_CATEGORY
, NULL AS COST_ATTRIBUTE1
, NULL AS COST_ATTRIBUTE2
, NULL AS COST_ATTRIBUTE3
, NULL AS COST_ATTRIBUTE4
, NULL AS COST_ATTRIBUTE5
, NULL AS COST_ATTRIBUTE6
, NULL AS COST_ATTRIBUTE7
, NULL AS COST_ATTRIBUTE8
, NULL AS COST_ATTRIBUTE9
, NULL AS COST_ATTRIBUTE10
, NULL AS COST_ATTRIBUTE11
, NULL AS COST_ATTRIBUTE12
, NULL AS COST_ATTRIBUTE13
, NULL AS COST_ATTRIBUTE14
, NULL AS COST_ATTRIBUTE15
, 0 AS REV_REVISION_AMOUNT
, 0 AS COST_REVISION_AMOUNT
, PA_VRM_UTILS.GET_INITIAL_BASELINED_REV_VER(PBV.PROJECT_ID) AS INITIAL_BASELINED_VERSION_ID
, 'REVENUE' AS INITIAL_BUDGET_TYPE
, 1 AS LINE_NUMBER
, 'Y' AS DELIVERED_FLAG
, CLOSED_DATE AS DELIVERED_DATE
, PR.ORG_ID AS ORG_ID
FROM PA_BUDGET_VERSIONS PBV
, PA_PROJECTS_ALL PR
, PA_PROJECT_STATUSES PPS
WHERE PR.PROJECT_ID = PBV.PROJECT_ID
AND PBV.BUDGET_STATUS_CODE = 'B'
AND PBV.CI_ID IS NULL
AND (NVL(PBV.APPROVED_REV_PLAN_TYPE_FLAG
, 'N') = 'Y')
AND PR.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PPS.STATUS_TYPE = 'PROJECT'
AND PPS.PROJECT_SYSTEM_STATUS_CODE = 'CLOSED'
AND PR.CLOSED_DATE IS NOT NULL
AND EXISTS (SELECT 1
FROM PA_BUDGET_VERSIONS PBV2
WHERE PBV2.PROJECT_ID = PR.PROJECT_ID
AND NVL(PBV2.APPROVED_REV_PLAN_TYPE_FLAG
, 'N') = 'Y'
AND PBV2.BUDGET_STATUS_CODE = 'B'
AND PBV2.CI_ID IS NULL )