[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_RVD_PKG
Source
1 PACKAGE BODY ENI_DBI_RVD_PKG AS
2 /* $Header: ENIRVDPB.pls 120.0 2005/05/26 19:35:51 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_stmt VARCHAR2(32000);
9 l_measures VARCHAR2(32000);
10 l_org VARCHAR2(32000);
11 l_org_where VARCHAR2(32000);
12 l_prod VARCHAR2(32000);
13 l_prod_where VARCHAR2(32000);
14 l_prod_cat VARCHAR2(32000);
15 l_prod_cat_from VARCHAR2(32000);
16 l_prod_cat_where VARCHAR2(32000);
17 l_ret_reason VARCHAR2(32000);
18 l_ret_reason_where VARCHAR2(32000);
19 l_cust VARCHAR2(32000);
20 l_cust_where VARCHAR2(32000);
21 l_lang VARCHAR2(10);
22
23 -- l_curr VARCHAR2(15) := 'NOT PASSED IN';
24 l_curr_suffix VARCHAR2(10);
25
26 l_all_prods BOOLEAN;
27 l_all_prod_cats BOOLEAN;
28 l_all_custs BOOLEAN;
29 l_all_reasons BOOLEAN;
30
31 -- l_custom_rec BIS_QUERY_ATTRIBUTES;
32
33 BEGIN
34
35 l_lang := userenv('LANG');
36
37 FOR i IN 1..p_param.COUNT LOOP
38 CASE p_param(i).parameter_name
39 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
40 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
41 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
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 WHEN 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON' THEN l_ret_reason := p_param(i).parameter_id;
51 ELSE null;
52 END CASE;
53 END LOOP;
54
55 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
56 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
57 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
58 l_all_reasons := (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All');
59
60
61
62 IF l_all_prod_cats THEN
63 l_prod_cat_from := '';
64 l_prod_cat_where := '';
65 ELSE
66 l_prod_cat_from := ',
67 ENI_DENORM_HIERARCHIES eni_cat
68 , MTL_DEFAULT_CATEGORY_SETS mdcs';
69 l_prod_cat_where := '
70 AND mv.item_category_id = eni_cat.child_id
71 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
72 AND eni_cat.dbi_flag = ''Y''
73 AND eni_cat.object_type = ''CATEGORY_SET''
74 AND eni_cat.object_id = mdcs.category_set_id
75 AND mdcs.functional_area_id = 11';
76 END IF;
77
78 IF l_all_prods THEN
79 l_prod_where := '';
80 ELSE l_prod_where := '
81 AND mv.master_item_id IN (&ITEM+ENI_ITEM)';
82 END IF;
83
84 IF l_all_custs THEN
85 l_cust_where := '';
86 ELSE l_cust_where := '
87 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
88 END IF;
89
90 IF l_all_reasons THEN
91 l_ret_reason_where := '';
92 ELSE l_ret_reason_where := '
93 AND mv.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
94 END IF;
95
96 -- l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
97 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
98
99 l_stmt := '
100 SELECT ENI_ATTRIBUTE3
101 , ENI_ATTRIBUTE4
102 , cust.value ENI_ATTRIBUTE5
103 , ENI_ATTRIBUTE6
104 , ENI_MEASURE1
105 , ENI_MEASURE2
106 , ENI_MEASURE3
107 FROM
108 (SELECT (rank() over (&ORDER_BY_CLAUSE, ENI_ATTRIBUTE3, ENI_ATTRIBUTE4, inv_org_id)) - 1 rnk,
109 customer_id
110 , inv_org_id
111 , ENI_ATTRIBUTE3
112 , ENI_ATTRIBUTE4
113 , ENI_ATTRIBUTE6
114 , ENI_MEASURE1
115 , ENI_MEASURE2
116 , ENI_MEASURE3
117 FROM
118 (SELECT mv.customer_id CUSTOMER_ID
119 , mv.inv_org_id INV_ORG_ID
120 , mv.order_number ENI_ATTRIBUTE3
121 , mv.header_id ENI_MEASURE3
122 , mv.line_number ENI_ATTRIBUTE4
123 , mv.time_fulfilled_date_id ENI_ATTRIBUTE6
124 , mv.returned_amt_'||l_curr_suffix||' ENI_MEASURE1
125 , sum(mv.returned_amt_'||l_curr_suffix||') over() ENI_MEASURE2
126 FROM ISC_DBI_CFM_003_MV mv'
127 ||l_prod_cat_from||'
128 WHERE mv.time_fulfilled_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
129 AND &BIS_CURRENT_ASOF_DATE'
130 ||l_prod_cat_where
131 ||l_prod_where
132 ||l_cust_where
133 ||l_ret_reason_where||')) a
134 , FII_CUSTOMERS_V cust
135 WHERE a.customer_id = cust.id
136 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
137 &ORDER_BY_CLAUSE NULLS LAST';
138 -- || '-- CURR:' || l_curr;
139
140 x_custom_sql := l_stmt;
141
142 END get_sql;
143
144 END ENI_DBI_RVD_PKG ;
145