VIEW: APPS.QPR_TIME_ALLHIER_V

Source

View Text - Preformatted

SELECT qpr_sr_util.get_all_year_pk AS all_sr_level_pk, qpr_sr_util.get_all_year_desc AS all_desc, qpr_maintain_aw.get_end_date - (SELECT start_date FROM qpr_price_plans_vl WHERE price_plan_id = qpr_maintain_aw.get_price_plan_id) + 1 AS all_year_span, least((nvl((SELECT end_date FROM qpr_price_plans_vl WHERE price_plan_id = qpr_maintain_aw.get_price_plan_id),(SELECT MAX(year_end_date) FROM qpr_time))),(SELECT MAX(year_end_date) FROM qpr_time),qpr_maintain_aw.get_end_date) AS all_year_end_date, a.year as YEAR, a.year_end_date as YEAR_END_DATE, a.year_end_date - a.year_start_date + 1 as year_span, a.year_description as YEAR_DESCRIPTION, b.year as FISCAL_YEAR, b.year_end_date as FISCAL_YEAR_END_DATE, b.year_end_date - b.year_start_date + 1 as FISCAL_YEAR_SPAN, b.year_description as FISCAL_YEAR_DESCRIPTION, a.quarter as QUARTER, a.quarter_end_date as QUARTER_END_DATE, a.quarter_end_date - a.quarter_start_date + 1 as QUARTER_SPAN, a.quarter_description as QUARTER_DESCRIPTION, b.quarter as FISCAL_QUARTER, b.quarter_end_date as FISCAL_QUARTER_END_DATE, b.quarter_end_date - b.quarter_start_date + 1 as FISCAL_QUARTER_SPAN, b.quarter_description as FISCAL_QUARTER_DESCRIPTION, a.month as MONTH, a.month_end_date as MONTH_END_DATE, a.month_end_date - a.month_start_date + 1 as MONTH_SPAN, a.month_description as MONTH_DESCRIPTION, b.month as FISCAL_MONTH, b.month_end_date as FISCAL_MONTH_END_DATE, b.month_end_date - b.month_start_date + 1 as FISCAL_MONTH_SPAN, b.month_description as FISCAL_MONTH_DESCRIPTION, a.week as WEEK, a.week_end_date as WEEK_END_DATE, a.week_end_date - a.week_start_date + 1 as week_span, a.week_description as WEEK_DESCRIPTION, b.week as FISCAL_WEEK, b.week_end_date as FISCAL_WEEK_END_DATE, b.week_end_date - a.week_start_date + 1 as FISCAL_WEEK_SPAN, b.week_description as FISCAL_WEEK_DESCRIPTION, a.day as DAY, 1 as day_span, a.day_description as DAY_DESCRIPTION, b.instance_id as INSTANCE_ID from qpr_time a, qpr_time b WHERE a.calendar_type=1 and a.calendar_code='Gregorian' and b.calendar_type(+)=2 and a.instance_id=-1 and a.day=b.day(+)

View Text - HTML Formatted

SELECT QPR_SR_UTIL.GET_ALL_YEAR_PK AS ALL_SR_LEVEL_PK
, QPR_SR_UTIL.GET_ALL_YEAR_DESC AS ALL_DESC
, QPR_MAINTAIN_AW.GET_END_DATE - (SELECT START_DATE
FROM QPR_PRICE_PLANS_VL
WHERE PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID) + 1 AS ALL_YEAR_SPAN
, LEAST((NVL((SELECT END_DATE
FROM QPR_PRICE_PLANS_VL
WHERE PRICE_PLAN_ID = QPR_MAINTAIN_AW.GET_PRICE_PLAN_ID)
, (SELECT MAX(YEAR_END_DATE)
FROM QPR_TIME)))
, (SELECT MAX(YEAR_END_DATE)
FROM QPR_TIME)
, QPR_MAINTAIN_AW.GET_END_DATE) AS ALL_YEAR_END_DATE
, A.YEAR AS YEAR
, A.YEAR_END_DATE AS YEAR_END_DATE
, A.YEAR_END_DATE - A.YEAR_START_DATE + 1 AS YEAR_SPAN
, A.YEAR_DESCRIPTION AS YEAR_DESCRIPTION
, B.YEAR AS FISCAL_YEAR
, B.YEAR_END_DATE AS FISCAL_YEAR_END_DATE
, B.YEAR_END_DATE - B.YEAR_START_DATE + 1 AS FISCAL_YEAR_SPAN
, B.YEAR_DESCRIPTION AS FISCAL_YEAR_DESCRIPTION
, A.QUARTER AS QUARTER
, A.QUARTER_END_DATE AS QUARTER_END_DATE
, A.QUARTER_END_DATE - A.QUARTER_START_DATE + 1 AS QUARTER_SPAN
, A.QUARTER_DESCRIPTION AS QUARTER_DESCRIPTION
, B.QUARTER AS FISCAL_QUARTER
, B.QUARTER_END_DATE AS FISCAL_QUARTER_END_DATE
, B.QUARTER_END_DATE - B.QUARTER_START_DATE + 1 AS FISCAL_QUARTER_SPAN
, B.QUARTER_DESCRIPTION AS FISCAL_QUARTER_DESCRIPTION
, A.MONTH AS MONTH
, A.MONTH_END_DATE AS MONTH_END_DATE
, A.MONTH_END_DATE - A.MONTH_START_DATE + 1 AS MONTH_SPAN
, A.MONTH_DESCRIPTION AS MONTH_DESCRIPTION
, B.MONTH AS FISCAL_MONTH
, B.MONTH_END_DATE AS FISCAL_MONTH_END_DATE
, B.MONTH_END_DATE - B.MONTH_START_DATE + 1 AS FISCAL_MONTH_SPAN
, B.MONTH_DESCRIPTION AS FISCAL_MONTH_DESCRIPTION
, A.WEEK AS WEEK
, A.WEEK_END_DATE AS WEEK_END_DATE
, A.WEEK_END_DATE - A.WEEK_START_DATE + 1 AS WEEK_SPAN
, A.WEEK_DESCRIPTION AS WEEK_DESCRIPTION
, B.WEEK AS FISCAL_WEEK
, B.WEEK_END_DATE AS FISCAL_WEEK_END_DATE
, B.WEEK_END_DATE - A.WEEK_START_DATE + 1 AS FISCAL_WEEK_SPAN
, B.WEEK_DESCRIPTION AS FISCAL_WEEK_DESCRIPTION
, A.DAY AS DAY
, 1 AS DAY_SPAN
, A.DAY_DESCRIPTION AS DAY_DESCRIPTION
, B.INSTANCE_ID AS INSTANCE_ID
FROM QPR_TIME A
, QPR_TIME B
WHERE A.CALENDAR_TYPE=1
AND A.CALENDAR_CODE='GREGORIAN'
AND B.CALENDAR_TYPE(+)=2
AND A.INSTANCE_ID=-1
AND A.DAY=B.DAY(+)