VIEW: APPS.GMS_STATUS_RESOURCES_BASE_V

Source

View Text - Preformatted

SELECT GB.award_id, GB.project_id, PPA.NAME, PPA.SEGMENT1, PAR.resource_id, PAR.name, GB.resource_list_member_id, PARLM.resource_list_id, sum(nvl(GB.revenue_period_to_date,0)* decode(GB.balance_type,'REV',1,0) ), sum(nvl(GB.budget_period_to_date,0)* decode(GB.balance_type,'BGT',1,0)), sum(nvl(GB.actual_period_to_date,0)* decode(GB.balance_type,'EXP',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'REQ',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'PO',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'AP',1,0)), sum(nvl(GB.encumb_period_to_date,0)* decode(GB.balance_type,'ENC',1,0)) , gb.start_date, gb.end_date FROM GMS_BALANCES GB, PA_RESOURCES PAR, GMS_BUDGET_VERSIONS GBV, PA_RESOURCE_LIST_MEMBERS PARLM, PA_RESOURCE_LISTS_ALL_BG PARLAB, PA_PROJECTS_ALL PPA WHERE GB.budget_version_id= GBV.budget_version_id and GBV.current_flag in ('Y','R') and GBV.budget_status_code = 'B' and GBV. resource_list_id = PARLM.resource_list_id and PARLM.resource_list_id = PARLAB.resource_list_id and PARLM.resource_list_member_id = GB.resource_list_member_id and PAR.resource_id = PARLM.resource_id and PPA.PROJECT_ID = GB.project_id GROUP BY GB.award_id, GB.project_id, PPA.NAME, PPA.SEGMENT1, PAR.resource_id, PAR.name, GB.resource_list_member_id, PARLM.resource_list_id,gb.start_date,gb.end_date union all select Gbc.award_id, Gbc.project_id, ppa.name, ppa.segment1, par.resource_id, par.name, gbc.resource_list_member_id, PARLM.resource_list_id, 0, 0, sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'EXP',1,0)), sum((nvl(gbc.entered_dr,0)-nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'REQ',1,0)), sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'PO',1,0)), sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'AP',1,0)), sum((nvl(gbc.entered_dr,0)- nvl(gbc.entered_cr,0)) * decode(gbc.document_type,'ENC',1,0)) , gbc.budget_period_start_date, gbc.budget_period_end_date from GMS_Bc_packets GBc, PA_RESOURCES PAR, GMS_BUDGET_VERSIONS GBV, PA_RESOURCE_LIST_MEMBERS PARLM, PA_RESOURCE_LISTS_ALL_BG PARLAB, PA_PROJECTS_ALL PPA where gbc.status_code = 'A' and GBc.budget_version_id = GBV.budget_version_id and GBV.current_flag in ('Y','R') and GBV.budget_status_code = 'B' and GBV. resource_list_id = PARLM.resource_list_id and PARLM.resource_list_id = PARLAB.resource_list_id and PARLM.resource_list_member_id = GBc.resource_list_member_id and PAR.resource_id = PARLM.resource_id and PPA.PROJECT_ID = GBc.project_id GROUP BY GBc.award_id, GBc.project_id, PPA.NAME, PPA.SEGMENT1, PAR.resource_id, PAR.name, GBc.resource_list_member_id, PARLM.resource_list_id,gbc.budget_period_start_date,gbc.budget_period_end_date

View Text - HTML Formatted

SELECT GB.AWARD_ID
, GB.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GB.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, SUM(NVL(GB.REVENUE_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REV'
, 1
, 0) )
, SUM(NVL(GB.BUDGET_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'BGT'
, 1
, 0))
, SUM(NVL(GB.ACTUAL_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'EXP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'REQ'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'PO'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'AP'
, 1
, 0))
, SUM(NVL(GB.ENCUMB_PERIOD_TO_DATE
, 0)* DECODE(GB.BALANCE_TYPE
, 'ENC'
, 1
, 0))
, GB.START_DATE
, GB.END_DATE
FROM GMS_BALANCES GB
, PA_RESOURCES PAR
, GMS_BUDGET_VERSIONS GBV
, PA_RESOURCE_LIST_MEMBERS PARLM
, PA_RESOURCE_LISTS_ALL_BG PARLAB
, PA_PROJECTS_ALL PPA
WHERE GB.BUDGET_VERSION_ID= GBV.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV. RESOURCE_LIST_ID = PARLM.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_ID = PARLAB.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_MEMBER_ID = GB.RESOURCE_LIST_MEMBER_ID
AND PAR.RESOURCE_ID = PARLM.RESOURCE_ID
AND PPA.PROJECT_ID = GB.PROJECT_ID GROUP BY GB.AWARD_ID
, GB.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GB.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, GB.START_DATE
, GB.END_DATE UNION ALL SELECT GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GBC.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, 0
, 0
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'EXP'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)-NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'REQ'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'PO'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'AP'
, 1
, 0))
, SUM((NVL(GBC.ENTERED_DR
, 0)- NVL(GBC.ENTERED_CR
, 0)) * DECODE(GBC.DOCUMENT_TYPE
, 'ENC'
, 1
, 0))
, GBC.BUDGET_PERIOD_START_DATE
, GBC.BUDGET_PERIOD_END_DATE
FROM GMS_BC_PACKETS GBC
, PA_RESOURCES PAR
, GMS_BUDGET_VERSIONS GBV
, PA_RESOURCE_LIST_MEMBERS PARLM
, PA_RESOURCE_LISTS_ALL_BG PARLAB
, PA_PROJECTS_ALL PPA
WHERE GBC.STATUS_CODE = 'A'
AND GBC.BUDGET_VERSION_ID = GBV.BUDGET_VERSION_ID
AND GBV.CURRENT_FLAG IN ('Y'
, 'R')
AND GBV.BUDGET_STATUS_CODE = 'B'
AND GBV. RESOURCE_LIST_ID = PARLM.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_ID = PARLAB.RESOURCE_LIST_ID
AND PARLM.RESOURCE_LIST_MEMBER_ID = GBC.RESOURCE_LIST_MEMBER_ID
AND PAR.RESOURCE_ID = PARLM.RESOURCE_ID
AND PPA.PROJECT_ID = GBC.PROJECT_ID GROUP BY GBC.AWARD_ID
, GBC.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PAR.RESOURCE_ID
, PAR.NAME
, GBC.RESOURCE_LIST_MEMBER_ID
, PARLM.RESOURCE_LIST_ID
, GBC.BUDGET_PERIOD_START_DATE
, GBC.BUDGET_PERIOD_END_DATE