PJO_DIS_PD_PROF_GL

Details

Schema: FUSION
Object owner: PJO
Object type: VIEW

Columns

Name
PERIOD_PROFILE_ID CURRENT_PERIOD_NAME FROM_ANCHOR_START FROM_ANCHOR_END NUM_OF_PERIODS START_PERIOD_NAME END_PERIOD_NAME PERIOD_NAME START_DATE END_DATE SET_OF_BOOKS_ID PERIOD_YEAR QUARTER_NUM

Name

PERIOD_PROFILE_ID

CURRENT_PERIOD_NAME

FROM_ANCHOR_START

FROM_ANCHOR_END

NUM_OF_PERIODS

START_PERIOD_NAME

END_PERIOD_NAME

PERIOD_NAME

START_DATE

END_DATE

SET_OF_BOOKS_ID

PERIOD_YEAR

QUARTER_NUM

Query

SQL_Statement
SELECT pmd.period_profile_id, glcp.period_name current_period_name, pmd.from_anchor_start, pmd.from_anchor_end, pmd.num_of_periods, decode(glsd.period_name,gled.period_name,glsd.period_name,glsd.period_name) start_period_name, gled.period_name end_period_name, decode(glsd.period_name,gled.period_name,glsd.period_name,glsd.period_name\|\|'-'\|\|gled.period_name) period_name, glsd.start_date start_date, gled.end_date end_date, glsd.set_of_books_id, glsd.period_year, glsd.quarter_num FROM (SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type ORDER BY gl.start_date) rn, gl.start_date start_date, gl.end_Date end_date, gl.period_name period_name, gl.period_set_name period_set_name, gl.period_type period_type, gsb.set_of_books_id, gl.period_year, gl.quarter_num FROM gl_periods gl, PJF_BU_IMPL_ALL_V pim, gl_sets_of_books gsb WHERE gl.period_set_name = pim.period_set_name AND gl.period_type = gsb.accounted_period_type AND gl.adjustment_period_flag='N' AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID AND gsb.set_of_books_id = pim.primary_ledger_id) glsd, (SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type ORDER BY gl.start_date) rn, ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type ORDER BY gl.start_date desc ) rnreverse, gl.start_date start_date, gl.end_Date end_date, gl.period_name period_name, gl.period_set_name period_set_name, gl.period_type period_type, gsb.set_of_books_id, gl.period_year, gl.quarter_num FROM gl_periods gl, PJF_BU_IMPL_ALL_V pim, gl_sets_of_books gsb WHERE gl.period_set_name = pim.period_set_name AND gl.period_type = gsb.accounted_period_type AND gl.adjustment_period_flag='N' AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID AND gsb.set_of_books_id = pim.primary_ledger_id) gled, (SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type ORDER BY gl.start_date) rn, gl.start_date start_date, gl.end_Date end_date, gl.period_name period_name, gl.period_set_name period_set_name, gl.period_type period_type, gsb.set_of_books_id, gl.period_year, gl.quarter_num FROM gl_periods gl, PJF_BU_IMPL_ALL_V pim, gl_sets_of_books gsb WHERE gl.period_set_name = pim.period_set_name AND gl.period_type = gsb.accounted_period_type AND gl.adjustment_period_flag='N' AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID AND gsb.set_of_books_id = pim.primary_ledger_id)glcp, pjo_period_profile_dtls pmd WHERE glsd.rn = decode(pmd.from_anchor_start,-99999,1,pmd.from_anchor_start + glcp.rn) AND decode(pmd.from_anchor_end,99999,gled.rnreverse,gled.rn) = decode(pmd.from_anchor_end,99999,1,pmd.from_anchor_end+glcp.rn) AND pmd.period_profile_id = PJO_EDIT_PLAN_SETUP.GET_PERIOD_PROFILE_ID AND glcp.period_name = PJO_EDIT_PLAN_SETUP.GET_CURRENT_PLANNING_PERIOD

SQL_Statement

SELECT pmd.period_profile_id,

glcp.period_name current_period_name,

pmd.from_anchor_start,

pmd.from_anchor_end,

pmd.num_of_periods,

decode(glsd.period_name,gled.period_name,glsd.period_name,glsd.period_name) start_period_name,

gled.period_name end_period_name,

decode(glsd.period_name,gled.period_name,glsd.period_name,glsd.period_name||'-'||gled.period_name) period_name,

glsd.start_date start_date,

gled.end_date end_date,

glsd.set_of_books_id,

glsd.period_year,

glsd.quarter_num

FROM (SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type

ORDER BY gl.start_date) rn,

gl.start_date start_date,

gl.end_Date end_date,

gl.period_name period_name,

gl.period_set_name period_set_name,

gl.period_type period_type,

gsb.set_of_books_id,

gl.period_year,

gl.quarter_num

FROM gl_periods gl,

PJF_BU_IMPL_ALL_V pim,

gl_sets_of_books gsb

WHERE gl.period_set_name = pim.period_set_name

AND gl.period_type = gsb.accounted_period_type

AND gl.adjustment_period_flag='N'

AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID

AND gsb.set_of_books_id = pim.primary_ledger_id) glsd,

(SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type

ORDER BY gl.start_date) rn,

ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type

ORDER BY gl.start_date desc ) rnreverse,

gl.start_date start_date,

gl.end_Date end_date,

gl.period_name period_name,

gl.period_set_name period_set_name,

gl.period_type period_type,

gsb.set_of_books_id,

gl.period_year,

gl.quarter_num

FROM gl_periods gl,

PJF_BU_IMPL_ALL_V pim,

gl_sets_of_books gsb

WHERE gl.period_set_name = pim.period_set_name

AND gl.period_type = gsb.accounted_period_type

AND gl.adjustment_period_flag='N'

AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID

AND gsb.set_of_books_id = pim.primary_ledger_id) gled,

(SELECT ROW_NUMBER() OVER(PARTITION BY gl.period_set_name,gl.period_type

ORDER BY gl.start_date) rn,

gl.start_date start_date,

gl.end_Date end_date,

gl.period_name period_name,

gl.period_set_name period_set_name,

gl.period_type period_type,

gsb.set_of_books_id,

gl.period_year,

gl.quarter_num

FROM gl_periods gl,

PJF_BU_IMPL_ALL_V pim,

gl_sets_of_books gsb

WHERE gl.period_set_name = pim.period_set_name

AND gl.period_type = gsb.accounted_period_type

AND gl.adjustment_period_flag='N'

AND pim.org_id = PJO_EDIT_PLAN_SETUP.GET_PROJECT_ORG_ID

AND gsb.set_of_books_id = pim.primary_ledger_id)glcp,

pjo_period_profile_dtls pmd

WHERE glsd.rn = decode(pmd.from_anchor_start,-99999,1,pmd.from_anchor_start + glcp.rn)

AND decode(pmd.from_anchor_end,99999,gled.rnreverse,gled.rn) = decode(pmd.from_anchor_end,99999,1,pmd.from_anchor_end+glcp.rn)

AND pmd.period_profile_id = PJO_EDIT_PLAN_SETUP.GET_PERIOD_PROFILE_ID AND glcp.period_name = PJO_EDIT_PLAN_SETUP.GET_CURRENT_PLANNING_PERIOD