[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_RVR_PKG
Source
1 PACKAGE BODY ENI_DBI_RVR_PKG AS
2 /* $Header: ENIRVRPB.pls 120.0 2005/05/26 19:38:06 appldev noship $ */
3
4 PROCEDURE GET_SQL( p_param IN BIS_PMV_PAGE_PARAMETER_TBL
5 , x_custom_sql OUT NOCOPY VARCHAR2
6 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_org VARCHAR2(32000);
9 l_prod VARCHAR2(32000);
10 l_prod_where VARCHAR2(32000);
11 l_prod_cat VARCHAR2(32000);
12 l_prod_cat_from VARCHAR2(32000);
13 l_prod_cat_where VARCHAR2(32000);
14 l_cust VARCHAR2(32000);
15 l_cust_where VARCHAR2(32000);
16 l_ret_reason VARCHAR2(32000);
17 l_ret_reason_where VARCHAR2(32000);
18 l_item_cat_flag NUMBER; -- 0 for product, 1 for product category, 3 for no grouping on item dimension
19 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
20
21 l_curr_suffix VARCHAR2(10);
22 -- l_curr VARCHAR2(15) := 'NOT PASSED IN';
23
24 l_custom_rec BIS_QUERY_ATTRIBUTES ;
25 -- l_open_url added for BUG 3730452
26 l_open_url VARCHAR2(2000);
27
28 l_all_prods BOOLEAN;
29 l_all_prod_cats BOOLEAN;
30 l_all_custs BOOLEAN;
31 l_all_reasons BOOLEAN;
32
33 BEGIN
34 l_open_url := '''pFunctionName=ENI_DBI_RVD_R&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
35
36 FOR i IN 1..p_param.COUNT LOOP
37 CASE p_param(i).parameter_name
38 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
39 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
40 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
41 WHEN 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON' THEN l_ret_reason := p_param(i).parameter_id;
42 WHEN 'CURRENCY+FII_CURRENCIES' THEN
43 -- l_curr := p_param(i).parameter_id;
44 l_curr_suffix :=
45 CASE p_param(i).parameter_id
46 WHEN eni_dbi_util_pkg.get_curr_prim THEN 'g' -- primary global currency
47 WHEN eni_dbi_util_pkg.get_curr_sec THEN 'g1' -- secondary global currency
48 ELSE 'f' -- functional currency
49 END;
50 ELSE null;
51 END CASE;
52 END LOOP;
53
54 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
55 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
56 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
57 l_all_reasons := (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All');
58
59 IF l_all_prods THEN
60 -- If item is not selected then set the prod where clause to null
61 l_prod_where := '';
62
63 -- Then check if prod category parameter is null as well
64 IF l_all_prod_cats THEN
65
66 -- If prod cat is null, set the prod_cat where clause to null
67 -- and the item cat flag = 3
68 l_prod_cat_from := '';
69 l_prod_cat_where := '';
70 l_item_cat_flag := 3; -- all categories
71
72 ELSE
73 -- If not null, then set the prod cat where clause
74 -- and the prod cat flag = 1
75 l_prod_cat_from := '
76 , ENI_DENORM_HIERARCHIES eni_cat
77 , MTL_DEFAULT_CATEGORY_SETS mdcs';
78 l_prod_cat_where := '
79 AND mv.item_category_id = eni_cat.child_id
80 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
81 AND eni_cat.dbi_flag = ''Y''
82 AND eni_cat.object_type = ''CATEGORY_SET''
83 AND eni_cat.object_id = mdcs.category_set_id
84 AND mdcs.functional_area_id = 11';
85
86 l_item_cat_flag := 1; -- a specific category
87 END IF;
88 ELSE -- When item is selected, set the where clause and the item cat flag
89 l_prod_where := '
90 AND mv.master_item_id IN (&ITEM+ENI_ITEM)';
91 l_item_cat_flag := 0; -- product
92 END IF;
93
94 -- Similarly when cust is not selected, set the where clause and the
95 -- cust flag to 1 else 0
96 IF l_all_custs
97 THEN
98 l_cust_where:= '';
99 l_cust_flag := 1; -- all customers and not viewed by customer
100 ELSE
101 l_cust_where :='
102 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
103 l_cust_flag := 0; -- customer selected
104 END IF;
105
106 IF l_all_reasons
107 THEN l_ret_reason_where := '';
108 ELSE l_ret_reason_where := '
109 AND mv.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
110 END IF;
111
112 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
113 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
114
115 x_custom_sql := '
116 SELECT ret.value VIEWBY -- return reason
117 , ret.id VIEWBYID
118 , c.prev_return ENI_MEASURE7 -- return value (prior)
119 , c.curr_return ENI_MEASURE1 -- return value
120 , (c.curr_return - c.prev_return)
121 / decode(c.prev_return, 0, NULL,
122 abs(c.prev_return)) * 100 ENI_MEASURE2 -- change (return value),
123 , c.curr_return
124 / decode(sum(c.curr_return) over(), 0, NULL,
125 sum(c.curr_return) over())
126 * 100 ENI_MEASURE3 -- percent of total
127 , c.lines_cnt ENI_MEASURE4 -- lines affected
128 , (CASE WHEN c.lines_cnt IS NULL
129 OR c.lines_cnt = 0
130 THEN NULL
131 ELSE '|| l_open_url ||' END) as ENI_ATTRIBUTE2 -- drill for lines affected
132 , sum(c.curr_return) over() ENI_MEASURE5 -- grand total for return value
133 , (sum(c.curr_return) over() - sum(c.prev_return) over())
134 / decode(sum(c.prev_return) over(), 0, NULL,
135 abs(sum(c.prev_return) over()))
136 * 100 ENI_MEASURE6 -- grand total for return value change
137 , sum(c.lines_cnt) over() ENI_MEASURE8 -- grand total for lines affected
138 FROM (SELECT mv.return_reason REASON
139 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
140 mv.returned_amt_' || l_curr_suffix || ', 0)) CURR_RETURN
141 , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
142 mv.returned_amt_' || l_curr_suffix || ', 0)) PREV_RETURN
143 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
144 mv.lines_cnt, 0)) LINES_CNT
145 FROM ISC_DBI_CFM_007_MV mv
146 , FII_TIME_RPT_STRUCT cal'
147 || l_prod_cat_from ||'
148 WHERE mv.time_id = cal.time_id
149 AND mv.period_type_id = cal.period_type_id
150 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
151 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
152 AND mv.customer_flag = :ENI_CUST_FLAG
153 AND mv.item_cat_flag = :ENI_ITEM_CAT_FLAG
154 AND mv.return_reason_flag = 0'
155 || l_prod_cat_where || l_prod_where || l_cust_where || l_ret_reason_where || '
156 GROUP BY mv.return_reason ) c
157 , BIS_ORDER_ITEM_RET_REASON_V ret
158 WHERE c.reason = ret.id
159 -- &ORDER_BY_CLAUSE NULLS LAST';
160 -- &ORDER_BY_CLAUSE NULLS LAST -- '||l_curr;
161
162 l_custom_rec.attribute_name := ':ENI_ITEM_CAT_FLAG';
163 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
164 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
165 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
166 x_custom_output.EXTEND;
167 x_custom_output(1) := l_custom_rec;
168
169 l_custom_rec.attribute_name := ':ENI_CUST_FLAG';
170 l_custom_rec.attribute_value := to_char(l_cust_flag);
171 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
172 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
173 x_custom_output.EXTEND;
174 x_custom_output(2) := l_custom_rec;
175
176 END get_sql;
177
178 END ENI_DBI_RVR_PKG;
179