[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
date_name AS VIEWBY
, SUM(ENI_MEASURE1) AS ENI_MEASURE1
, SUM(ENI_MEASURE50) AS ENI_MEASURE50
, SUM(ENI_MEASURE2) AS ENI_MEASURE2
, SUM(ENI_MEASURE4) AS ENI_MEASURE4
, SUM(ENI_MEASURE5) AS ENI_MEASURE5
, SUM(ENI_MEASURE7) AS ENI_MEASURE7
, SUM(ENI_MEASURE8) AS ENI_MEASURE8
, SUM(ENI_MEASURE10) AS ENI_MEASURE10
, SUM(ENI_MEASURE11) AS ENI_MEASURE11
, SUM(ENI_MEASURE14) AS ENI_MEASURE14
, SUM(ENI_MEASURE15) AS ENI_MEASURE15
, SUM(ENI_MEASURE16) AS ENI_MEASURE16
FROM
(
(
SELECT
t.name AS date_name,
t.start_date AS start_date,
SUM
(
case when ftrs.report_date = t.c_end_date
THEN edismv.inv_total_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE1,
SUM
(
case when ftrs.report_date = t.c_end_date
THEN edismv.inv_total_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE50,
SUM
(
case when ftrs.report_date = t.p_end_date
THEN edismv.inv_total_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE2,
SUM
(
case when ftrs.report_date = t.c_end_date
THEN edismv.onhand_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE4,
SUM
(
case when ftrs.report_date = t.p_end_date
THEN edismv.onhand_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE5,
SUM
(
case when ftrs.report_date = t.c_end_date
THEN edismv.intransit_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE7,
SUM
(
case when ftrs.report_date = t.p_end_date
THEN edismv.intransit_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE8,
SUM
(
case when ftrs.report_date = t.c_end_date
THEN edismv.wip_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE10,
SUM
(
case when ftrs.report_date = t.p_end_date
THEN edismv.wip_value_'||l_curr_suffix||'
ELSE 0
END
) AS ENI_MEASURE11,
10 AS ENI_MEASURE14,
11 AS ENI_MEASURE15,
12 AS ENI_MEASURE16
FROM
' || l_from_clause ||' , fii_time_rpt_struct ftrs,
(
SELECT
c.name,
c.'||l_id_column||',
c.start_date AS start_date,
(case when '|| '&' || 'BIS_CURRENT_ASOF_DATE < c.end_date
then '|| '&' || 'BIS_CURRENT_ASOF_DATE else c.end_date end ) AS c_end_date,
(case when '|| '&' || 'BIS_PREVIOUS_ASOF_DATE < p.end_date
then '|| '&' || 'BIS_PREVIOUS_ASOF_DATE else p.end_date end ) AS p_end_date
FROM
' || l_period_type ||' c, ' || l_period_type || ' p
WHERE
c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
AND c.'||l_id_column||' <= :CUR_PERIOD_ID '|| --Bug 5083662
'AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
' || l_comp_where || '
) t
WHERE
' || l_where_clause || '
AND (
t.c_end_date = ftrs.report_date
OR t.p_end_date = ftrs.report_date
)
AND BITAND(ftrs.record_type_id, 1143) = ftrs.record_type_id
GROUP BY
t.name,t.start_date,t.c_end_date
)
UNION ALL
(
SELECT
c.name AS date_name
, c.start_date AS start_date
, NULL AS ENI_MEASURE1
, NULL AS ENI_MEASURE50
, NULL AS ENI_MEASURE2
, NULL AS ENI_MEASURE4
, NULL AS ENI_MEASURE5
, NULL AS ENI_MEASURE7
, NULL AS ENI_MEASURE8
, NULL AS ENI_MEASURE10
, NULL AS ENI_MEASURE11
, NULL AS ENI_MEASURE14
, NULL AS ENI_MEASURE15
, NULL AS ENI_MEASURE16
FROM
' || l_period_type ||' c, ' || l_period_type || ' p
WHERE
c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
AND c.'||l_id_column||' <= :CUR_PERIOD_ID '|| --Bug 5083662
'AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
' || l_comp_where || '
)
)
GROUP BY date_name,start_date
ORDER BY
' || l_order_by;