[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* selected from the parameter portlet *
* x_custom_sql varchar2 OUT This is used to send the portlet query *
* x_cusom_output varchar2 OUT This is used to send the bind variables *
* *
**********************************************************************************************/
PROCEDURE GET_CUST_ACQUIS_TREND_SQL(
p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
x_custom_sql OUT NOCOPY VARCHAR2,
x_custom_output OUT NOCOPY bis_query_attributes_TBL )
IS
-- Generic Variables
l_custom_sql VARCHAR2(15000) ; --Final Sql.
l_outer_select VARCHAR2(3200);
l_outer_select := '';
l_outer_select := ' time_dim.NAME VIEWBY,
NVL(WEB_REG_P,0) IBW_VAL8, --Bug#4727078 Issue#:21
NVL(WEB_REG_C,0) IBW_VAL1,
NVL(WEB_CUST_P,0) IBW_VAL9, --Bug#4727078 Issue#:21
NVL(WEB_CUST_C,0) IBW_VAL3,
(DECODE(NVL(WEB_CUST_ALL,0),0,null,
NVL(WEB_CUST_C,0)/WEB_CUST_ALL)*100) IBW_VAL5,
NVL(BOOKED_AMOUNT_C,0) IBW_VAL6,
DECODE(NVL(BOOKED_ORDERS_C,0),0,null,
NVL(BOOKED_AMOUNT_C,0)/BOOKED_ORDERS_C) IBW_VAL7
FROM (
SELECT
start_date START_DATE,
SUM(WEB_REG_C) WEB_REG_C,
SUM(WEB_REG_P) WEB_REG_P,
SUM(WEB_CUST_C) WEB_CUST_C,
SUM(WEB_CUST_P) WEB_CUST_P,
SUM(WEB_CUST_ALL) WEB_CUST_ALL,
SUM(BOOKED_AMOUNT_C) BOOKED_AMOUNT_C,
SUM(BOOKED_ORDERS_C) BOOKED_ORDERS_C ';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select );
/* select appropriate record_type_id */
/* based on the period selected */
/*************************************************************************/
-- The inner most select clause has two UNION ALLs
-- The first UNION ALL fetches Web Registrations,Change,New Web Customers,Change,Booked Web Orders Amount, Average Web Order Value
-- The second UNION ALL fetches new web customers through all channels which is used to calculate Percent New Web Customers
l_custom_sql := ' SELECT '|| l_outer_select ||
' FROM ' ||
'(
SELECT
dates.start_date START_DATE,
decode(dates.period, ''C'',web_registrations,0) WEB_REG_C,
decode(dates.period, ''P'',web_registrations,0) WEB_REG_P,
decode(dates.period, ''C'',new_web_customers,0) WEB_CUST_C,
decode(dates.period, ''P'',new_web_customers,0) WEB_CUST_P,
NULL WEB_CUST_ALL,
decode(dates.period, ''C'',
decode(:l_currency,:l_gp_currency,BOOKED_AMT_G,:l_gs_currency,BOOKED_AMT_G1,CURRENCY_CD_F,BOOKED_AMT_F),0) BOOKED_AMOUNT_C,
decode(dates.period, ''C'',booked_web_orders,0) BOOKED_ORDERS_C
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC) p1,
(SELECT time_dim.start_date START_DATE,
rownum ID
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates, '||l_from || l_site_from ||'
WHERE ' || l_where || l_site_where ||
' UNION ALL
SELECT
dates.start_date START_DATE,
null WEB_REG_C,
null WEB_REG_P,
null WEB_CUST_C,
null WEB_CUST_P,
DECODE(dates.period, ''C'',new_web_customers_all,null) WEB_CUST_ALL,
null BOOKED_AMOUNT_C,
null BOOKED_ORDERS_C
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(
SELECT
REPORT_DATE,
rownum id
FROM
(
SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM
' ||l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p1,
(
SELECT
START_DATE,
rownum id
FROM
(
SELECT time_dim.start_date START_DATE
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p2
WHERE p1.id(+) = p2.id) dates, '|| l_from ||'
WHERE '|| l_where ||
' AND CUSTACQUIS_MV.site_id = -9999 )
GROUP BY start_date
) s,'|| l_period_type||' time_dim
WHERE '|| l_outer_where ||
'ORDER BY time_dim.start_date';
* selected from the parameter portlet *
* x_custom_sql varchar2 OUT This is used to send the portlet query *
* x_cusom_output varchar2 OUT This is used to send the bind variables *
* *
**********************************************************************************************/
PROCEDURE GET_CUST_ACTY_TREND_SQL(
p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
x_custom_sql OUT NOCOPY VARCHAR2,
x_custom_output OUT NOCOPY bis_query_attributes_TBL)
IS
-- Generic Variables
l_custom_sql VARCHAR2(15000) ; --Final Sql.
l_outer_select VARCHAR2(3200);
l_outer_select := '';
l_outer_select := ' time_dim.NAME VIEWBY,
NVL(VISITS,0) IBW_VAL1,
NVL(CARTS,0) IBW_VAL2,
NVL(A_LEADS,0) IBW_VAL3,
DECODE(NVL(CARTS,0)
,0,null,
(NVL(ORDERS,0)/
NVL(CARTS,0))*100) IBW_VAL4,
NVL(BOOKED_ORDERS,0) IBW_VAL5,
DECODE(NVL(P_BOOKED_ORDERS_ALL,0),0,null,
(NVL(P_BOOKED_ORDERS,0)/
NVL(P_BOOKED_ORDERS_ALL,0))*100) IBW_VAL17, --4727078 Issue#21
DECODE(NVL(BOOKED_ORDERS_ALL,0),0,null,
(NVL(BOOKED_ORDERS,0)/
NVL(BOOKED_ORDERS_ALL,0))*100) IBW_VAL6,
DECODE(NVL(BOOKED_ORDERS,0),0,null,
(NVL(ASSISTED_ORDERS,0)/
NVL(BOOKED_ORDERS,0))*100) IBW_VAL7,
NVL(P_BOOKED_AMOUNT,0) IBW_VAL18, --4727078 Issue#21
NVL(BOOKED_AMOUNT,0) IBW_VAL8,
NVL(BOOKED_AMOUNT_ALL,0) IBW_VAL9,
NVL(TOTAL_ORDER_INQUIRIES,0) IBW_VAL10,
NVL(TOTAL_INVOICE_INQUIRIES,0) IBW_VAL11,
NVL(TOTAL_PAYMENT_INQUIRIES,0) IBW_VAL12
FROM (
SELECT
start_date START_DATE,
SUM(VISITS) VISITS,
SUM(CARTS) CARTS,
SUM(A_LEADS) A_LEADS,
SUM(ORDERS) ORDERS,
SUM(BOOKED_ORDERS) BOOKED_ORDERS,
SUM(BOOKED_ORDERS_ALL) BOOKED_ORDERS_ALL,
SUM(ASSISTED_ORDERS) ASSISTED_ORDERS,
SUM(BOOKED_AMOUNT) BOOKED_AMOUNT,
SUM(BOOKED_AMOUNT_ALL) BOOKED_AMOUNT_ALL,
SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES,
SUM(P_BOOKED_AMOUNT) P_BOOKED_AMOUNT, --4727078 Issue#21
SUM(P_BOOKED_ORDERS) P_BOOKED_ORDERS, --4727078 Issue#21
SUM(P_BOOKED_ORDERS_ALL) P_BOOKED_ORDERS_ALL --4727078 Issue#21
';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select);
/* select appropriate record_type_id */
/* based on the period selected */
/*************************************************************************/
-- The inner most select clause has two UNION ALLs
-- The first UNION ALL fetches Visits,Carts,A Leads,Cart Conversion,Booked Orders,Assisted Orders,Booked Orders Amount,Order Status,Invoice,Payment
-- The second UNION ALL fetches Total Booked Orders which is used to calculate Percent Web Orders and Total Booked Orders Amount
l_custom_sql := ' SELECT '|| l_outer_select ||
' FROM ' ||
'(
SELECT
dates.start_date START_DATE,
decode(dates.period, ''C'',visits,0) VISITS,
decode(dates.period, ''C'',carts,0) CARTS,
decode(dates.period, ''C'',a_leads,0) A_LEADS,
decode(dates.period, ''C'',orders,0) ORDERS,
decode(dates.period, ''C'',booked_web_orders,0) BOOKED_ORDERS,
NULL BOOKED_ORDERS_ALL,
decode(dates.period, ''C'',assisted_web_orders,0) ASSISTED_ORDERS,
decode(dates.period, ''C'',
decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f),0) BOOKED_AMOUNT,
NULL BOOKED_AMOUNT_ALL,
decode(dates.period, ''C'',total_order_inquiries,0) TOTAL_ORDER_INQUIRIES,
decode(dates.period, ''C'',total_invoice_inquiries,0) TOTAL_INVOICE_INQUIRIES,
decode(dates.period, ''C'',total_payment_inquiries,0) TOTAL_PAYMENT_INQUIRIES ,
decode(dates.period, ''P'',
decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f),0) P_BOOKED_AMOUNT, --4727078 Issue#21
decode(dates.period, ''P'',booked_web_orders,0) P_BOOKED_ORDERS, --4727078 Issue#21
NULL P_BOOKED_ORDERS_ALL --4727078 Issue#21
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(
SELECT
REPORT_DATE,
rownum id
FROM
(
SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM
' ||l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p1,
(
SELECT
START_DATE,
rownum id
FROM
(
SELECT time_dim.start_date START_DATE
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p2
WHERE p1.id(+) = p2.id) dates, '||l_from || l_site_from ||'
WHERE ' || l_where || l_site_where ||
' UNION ALL
SELECT
dates.start_date START_DATE,
NULL VISITS,
NULL CARTS,
NULL A_LEADS,
NULL ORDERS,
NULL BOOKED_ORDERS,
decode(dates.period, ''C'',total_booked_orders,0) BOOKED_ORDERS_ALL,
NULL ASSISTED_ORDERS,
NULL BOOKED_AMOUNT,
decode(dates.period, ''C'',
decode(:l_currency,:l_gp_currency,total_booked_amt_g,:l_gs_currency,total_booked_amt_g1,currency_cd_f,total_booked_amt_f),0) BOOKED_AMOUNT_ALL,
NULL TOTAL_ORDER_INQUIRIES,
NULL TOTAL_INVOICE_INQUIRIES,
NULL TOTAL_PAYMENT_INQUIRIES,
NULL P_BOOKED_AMOUNT, --4727078 Issue#21
NULL P_BOOKED_ORDERS, --4727078 Issue#21
decode(dates.period, ''P'',total_booked_orders,0) P_BOOKED_ORDERS_ALL --4727078 Issue#21
FROM
(
SELECT
time_dim.start_date START_DATE,
''C'' PERIOD,
least(time_dim.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT
p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM
(
SELECT
REPORT_DATE,
rownum id
FROM
(
SELECT
least(time_dim.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM
' ||l_period_type||' time_dim
WHERE
time_dim.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p1,
(
SELECT
START_DATE,
rownum id
FROM
(
SELECT time_dim.start_date START_DATE
FROM ' ||l_period_type||' time_dim
WHERE time_dim.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
ORDER BY time_dim.start_date DESC
)
) p2
WHERE p1.id(+) = p2.id) dates, '|| l_from ||'
WHERE '|| l_where ||
' AND CUSTACQUIS_MV.site_id = -9999 )
GROUP BY start_date
) s,'|| l_period_type||' time_dim
WHERE '|| l_outer_where ||
'ORDER BY time_dim.start_date';
* selected from the parameter portlet *
* x_custom_sql varchar2 OUT This is used to send the portlet query *
* x_cusom_output varchar2 OUT This is used to send the bind variables *
* *
**********************************************************************************************/
PROCEDURE GET_CUST_ACTY_SQL(
p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
x_custom_sql OUT NOCOPY VARCHAR2,
x_custom_output OUT NOCOPY bis_query_attributes_TBL)
IS
-- Generic Variables
l_custom_sql VARCHAR2(15000) ; --Final Sql.
l_outer_select VARCHAR2(32000);
l_inner_select VARCHAR2(3200);
l_inner_select_all VARCHAR2(3200);
l_middle_select VARCHAR2(3200);
l_outer_select := '';
l_inner_select := '';
l_inner_select_all := '';
l_middle_select := '';
l_outer_select := ' VIEW_BY VIEWBY,
VIEW_BY_ID VIEWBYID,
NVL(VISITS,0) IBW_VAL1,
NVL(CARTS,0) IBW_VAL2,
NVL(A_LEADS,0) IBW_VAL3,
DECODE(NVL(CARTS,0)
,0,null,
(NVL(ORDERS,0)/
NVL(CARTS,0))*100) IBW_VAL4,
NVL(BOOKED_ORDERS,0) IBW_VAL5,
DECODE(NVL(BOOKED_ORDERS_ALL,0),0,null,
(NVL(BOOKED_ORDERS,0)/
NVL(BOOKED_ORDERS_ALL,0))*100) IBW_VAL6,
DECODE(NVL(BOOKED_ORDERS,0),0,null,
(NVL(ASSISTED_ORDERS,0)/
NVL(BOOKED_ORDERS,0))*100) IBW_VAL7,
NVL(BOOKED_AMOUNT,0) IBW_VAL8,
NVL(BOOKED_AMOUNT_ALL,0) IBW_VAL9,
NVL(TOTAL_ORDER_INQUIRIES,0) IBW_VAL10,
NVL(TOTAL_INVOICE_INQUIRIES,0) IBW_VAL11,
NVL(TOTAL_PAYMENT_INQUIRIES,0) IBW_VAL12,
SUM(NVL(VISITS,0)) over () IBW_G_TOT1,
SUM(NVL(CARTS,0)) over () IBW_G_TOT2,
SUM(NVL(A_LEADS,0)) over () IBW_G_TOT3,
DECODE(SUM(NVL(CARTS,0)) over(),0,null,
(SUM(NVL(ORDERS,0)) over()/
SUM(NVL(CARTS,0)) over())*100) IBW_G_TOT4,
SUM(NVL(BOOKED_ORDERS,0)) over () IBW_G_TOT5,
DECODE(DECODE('''|| l_view_by ||''',''SITE+SITE'',
NVL(BOOKED_ORDERS_ALL,0),
SUM(NVL(BOOKED_ORDERS_ALL,0)) over ())
,0,null,
(SUM(NVL(BOOKED_ORDERS,0)) over()/
DECODE('''|| l_view_by ||''',''SITE+SITE'',
NVL(BOOKED_ORDERS_ALL,0),
SUM(NVL(BOOKED_ORDERS_ALL,0)) over ()))*100) IBW_G_TOT6, -- Changed grandtotal so that BOOKED_ORDERS_ALL is not summed up when view by is Site
DECODE(SUM(NVL(BOOKED_ORDERS,0)) over()
,0,null,
(SUM(NVL(ASSISTED_ORDERS,0)) over()/
SUM(NVL(BOOKED_ORDERS,0)) over())*100) IBW_G_TOT7,
SUM(NVL(BOOKED_AMOUNT,0)) over () IBW_G_TOT8,
DECODE('''|| l_view_by ||''',''SITE+SITE'',
NVL(BOOKED_AMOUNT_ALL,0),
SUM(NVL(BOOKED_AMOUNT_ALL,0)) over ()) IBW_G_TOT9,
SUM(NVL(TOTAL_ORDER_INQUIRIES,0)) over () IBW_G_TOT10,
SUM(NVL(TOTAL_INVOICE_INQUIRIES,0)) over () IBW_G_TOT11,
SUM(NVL(TOTAL_PAYMENT_INQUIRIES,0)) over () IBW_G_TOT12 ';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_outer_select : ' || l_outer_select );
l_middle_select := ' VIEW_BY VIEW_BY,
VIEW_BY_ID VIEW_BY_ID,
SUM(VISITS) VISITS,
SUM(CARTS) CARTS,
SUM(A_LEADS) A_LEADS,
SUM(ORDERS) ORDERS,
SUM(BOOKED_ORDERS) BOOKED_ORDERS,
SUM(BOOKED_ORDERS_ALL) BOOKED_ORDERS_ALL,
SUM(ASSISTED_ORDERS) ASSISTED_ORDERS,
SUM(BOOKED_AMOUNT) BOOKED_AMOUNT,
SUM(BOOKED_AMOUNT_ALL) BOOKED_AMOUNT_ALL,
SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES ';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_middle_select : ' || l_middle_select );
l_inner_select := ' SUM(VISITS) VISITS,
SUM(CARTS) CARTS,
SUM(A_LEADS) A_LEADS,
SUM(ORDERS) ORDERS,
SUM(BOOKED_WEB_ORDERS) BOOKED_ORDERS,
NULL BOOKED_ORDERS_ALL,
SUM(ASSISTED_WEB_ORDERS) ASSISTED_ORDERS,
SUM(decode(:l_currency,:l_gp_currency,booked_amt_g,:l_gs_currency,booked_amt_g1,currency_cd_f,booked_amt_f)) BOOKED_AMOUNT,
NULL BOOKED_AMOUNT_ALL,
SUM(TOTAL_ORDER_INQUIRIES) TOTAL_ORDER_INQUIRIES,
SUM(TOTAL_INVOICE_INQUIRIES) TOTAL_INVOICE_INQUIRIES,
SUM(TOTAL_PAYMENT_INQUIRIES) TOTAL_PAYMENT_INQUIRIES ';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select : ' || l_inner_select );
l_inner_select_all := ' NULL VISITS,
NULL CARTS,
NULL A_LEADS,
NULL ORDERS,
NULL BOOKED_ORDERS,
SUM(TOTAL_BOOKED_ORDERS) BOOKED_ORDERS_ALL,
NULL ASSISTED_ORDERS,
NULL BOOKED_AMOUNT,
SUM(decode(:l_currency,:l_gp_currency,total_booked_amt_g,:l_gs_currency,total_booked_amt_g1,currency_cd_f,total_booked_amt_f)) BOOKED_AMOUNT_ALL,
NULL TOTAL_ORDER_INQUIRIES,
NULL TOTAL_INVOICE_INQUIRIES,
NULL TOTAL_PAYMENT_INQUIRIES ';
fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select_all : ' || l_inner_select_all );
l_inner_select := ' SITE.VALUE VIEW_BY,SITE.ID VIEW_BY_ID, ' || l_inner_select;
l_inner_select_all:= ' SITE.VALUE VIEW_BY,SITE.ID VIEW_BY_ID, ' || l_inner_select_all;
l_inner_select := ' CUSTCLASS.VALUE VIEW_BY,CUSTCLASS.ID VIEW_BY_ID, ' || l_inner_select;
l_inner_select_all:= ' CUSTCLASS.VALUE VIEW_BY,CUSTCLASS.ID VIEW_BY_ID, ' || l_inner_select_all;
l_inner_select := ' CUST.VALUE VIEW_BY,CUST.ID VIEW_BY_ID, ' || l_inner_select;
l_inner_select_all:= ' CUST.VALUE VIEW_BY,CUST.ID VIEW_BY_ID, ' || l_inner_select_all;
fnd_log.string(fnd_log.level_statement,l_full_path,'l_inner_select : ' || l_inner_select ||
'l_inner_select_all : ' || l_inner_select_all || 'l_from : ' || l_from || 'l_where : ' ||
l_where || 'l_from_all : ' || l_from_all || 'l_where_all : ' || l_where_all || 'l_inner_group_by : '
|| l_inner_select_all || 'l_middle_group_by : ' || l_middle_group_by );
/* select appropriate record_type_id */
/* based on the period selected */
/*************************************************************************/
l_custom_sql := 'SELECT ' || l_outer_select ||
' FROM ' ||
' (SELECT ' || l_middle_select ||
' FROM ' ||
' (SELECT ' || l_inner_select ||
' FROM ' || l_from ||
' WHERE ' || l_where ||
' GROUP BY ' || l_inner_group_by ||
' UNION ALL ' ||
' SELECT ' || l_inner_select_all ||
' FROM ' || l_from_all ||
' WHERE ' || l_where_all ||
' GROUP BY ' || l_inner_group_by ||
' ) ' ||
' GROUP BY ' || l_middle_group_by ||
' HAVING ' || l_having ||
' ) ' ||
' &ORDER_BY_CLAUSE ';