VIEW: APPS.QPR_TIME_ALLHIER_L_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 FROM qpr_time a, qpr_time b WHERE a.calendar_type=1 and a.calendar_code='Gregorian' and b.calendar_type(+)=2 and b.calendar_code(+)=qpr_maintain_aw.get_calendar_code and b.instance_id(+)=qpr_maintain_aw.get_instance and a.instance_id=-1 and a.day=b.day(+) and a.day between nvl(qpr_maintain_aw.get_start_date, a.day) and nvl(qpr_maintain_aw.get_end_date, a.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
FROM QPR_TIME A
, QPR_TIME B
WHERE A.CALENDAR_TYPE=1
AND A.CALENDAR_CODE='GREGORIAN'
AND B.CALENDAR_TYPE(+)=2
AND B.CALENDAR_CODE(+)=QPR_MAINTAIN_AW.GET_CALENDAR_CODE
AND B.INSTANCE_ID(+)=QPR_MAINTAIN_AW.GET_INSTANCE
AND A.INSTANCE_ID=-1
AND A.DAY=B.DAY(+)
AND A.DAY BETWEEN NVL(QPR_MAINTAIN_AW.GET_START_DATE
, A.DAY)
AND NVL(QPR_MAINTAIN_AW.GET_END_DATE
, A.DAY)