[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_RET_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_RET_DETAIL_PKG AS
2 /* $Header: ISCRGBKB.pls 120.0 2005/05/25 17:34:16 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_stmt VARCHAR2(32000);
8 l_sgid VARCHAR2(32000);
9 l_sg_where VARCHAR2(32000);
10 l_prod VARCHAR2(32000);
11 l_prod_where VARCHAR2(32000);
12 l_prod_cat VARCHAR2(32000);
13 l_prod_cat_from VARCHAR2(32000);
14 l_prod_cat_where VARCHAR2(32000);
15 l_cust VARCHAR2(32000);
16 l_cust_where VARCHAR2(32000);
17 l_class VARCHAR2(32000);
18 l_class_where VARCHAR2(32000);
19 l_sg_sg NUMBER;
20 l_sg_res NUMBER;
21 l_curr VARCHAR2(10000);
22 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
23 l_curr_suffix VARCHAR2(120);
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25
26
27 BEGIN
28 FOR i IN 1..p_param.COUNT
29 LOOP
30
31 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
32 l_sgid := p_param(i).parameter_id;
33 END IF;
34
35 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
36 l_prod_cat := p_param(i).parameter_id;
37 END IF;
38
39 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM') THEN
40 l_prod := p_param(i).parameter_id;
41 END IF;
42
43 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
44 l_cust := p_param(i).parameter_id;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
48 l_class := p_param(i).parameter_id;
49 END IF;
50
51 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
52 THEN l_curr := p_param(i).parameter_id;
53 END IF;
54 END LOOP;
55
56 IF (l_curr = l_curr_g)
57 THEN l_curr_suffix := 'g';
58 ELSE l_curr_suffix := 'g1';
59 END IF;
60
61 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
62 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
63
64 IF (l_sg_res IS NULL) -- when a sales group is chosen
65 THEN
66 l_sg_where := '
67 AND mv.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)';
68 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
69 l_sg_where := '
70 AND mv.sales_grp_id = :ISC_SG
71 AND mv.resource_id = :ISC_RES';
72 END IF;
73
74
75 IF (l_cust IS NULL)
76 THEN l_cust_where := '';
77 ELSE l_cust_where := '
78 AND mv.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
79 END IF;
80
81 IF (l_class IS NULL)
82 THEN l_class_where:='';
83 ELSE l_class_where :='
84 AND mv.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
85 END IF;
86
87 IF (l_prod_cat IS NULL)
88 THEN
89 l_prod_cat_from := '';
90 l_prod_cat_where := '';
91 ELSE -- a prod cat has been selected
92 IF (l_prod IS NULL)
93 THEN
94 l_prod_cat_from := ',
95 ENI_DENORM_HIERARCHIES eni_cat,
96 MTL_DEFAULT_CATEGORY_SETS mdcs';
97 l_prod_cat_where := '
98 AND mv.item_category_id = eni_cat.child_id
99 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
100 AND eni_cat.dbi_flag = ''Y''
101 AND eni_cat.object_type = ''CATEGORY_SET''
102 AND eni_cat.object_id = mdcs.category_set_id
103 AND mdcs.functional_area_id = 11';
104 ELSE
105 l_prod_cat_from := '';
106 l_prod_cat_where := '';
107 END IF;
108 END IF;
109
110 IF (l_prod IS NULL)
111 THEN l_prod_where := '';
112 ELSE l_prod_where := '
113 AND mv.item_id IN (&ITEM+ENI_ITEM)';
114 END IF;
115
116 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
117 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
118
119 l_stmt := '
120 SELECT ISC_ATTRIBUTE_1, -- Order Number
121 ISC_ATTRIBUTE_2, -- Line Number
122 ou.value ISC_ATTRIBUTE_3, -- Operating Unit
123 ISC_ATTRIBUTE_4, -- Booked Date
124 cust.value ISC_ATTRIBUTE_5, -- Customer
125 cc.value ISC_ATTRIBUTE_10, -- Customer Classification
126 items.value ISC_ATTRIBUTE_6, -- Item
127 items.description ISC_ATTRIBUTE_7, -- Description
128 g.group_name ISC_ATTRIBUTE_8, -- Sales Group
129 r.resource_name ISC_ATTRIBUTE_9, -- Sales Representative
130 ISC_MEASURE_2, -- Booked Return Sales Credit
131 ISC_MEASURE_3, -- Grand Total - Booked Return Sales Credit
132 ISC_MEASURE_4 -- Header ID
133 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE, isc_attribute_1 desc, isc_attribute_2, org_ou_id))-1 RNK,
134 org_ou_id, customer_id, class_code, item_id, sales_grp_id, resource_id,
135 ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_4,
136 ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4
137 FROM (SELECT mv.org_ou_id ORG_OU_ID,
138 mv.customer_id CUSTOMER_ID,
139 mv.class_code CLASS_CODE,
140 mv.item_id ITEM_ID,
141 mv.sales_grp_id SALES_GRP_ID,
142 mv.resource_id RESOURCE_ID,
143 mv.order_number ISC_ATTRIBUTE_1,
144 mv.line_number ISC_ATTRIBUTE_2,
145 mv.time_booked_date_id ISC_ATTRIBUTE_4,
146 mv.returned_amt_'||l_curr_suffix||' ISC_MEASURE_2,
147 sum(mv.returned_amt_'||l_curr_suffix||') over () ISC_MEASURE_3,
148 mv.header_id ISC_MEASURE_4
149 FROM ISC_DBI_SCR_003_MV mv'
150 ||l_prod_cat_from||'
151 WHERE mv.time_booked_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
152 AND &BIS_CURRENT_ASOF_DATE'
153 ||l_sg_where
154 ||l_prod_cat_where
155 ||l_prod_where
156 ||l_cust_where
157 ||l_class_where||')
158 ) a,
159 FII_CUSTOMERS_V cust,
160 FII_PARTNER_MKT_CLASS_V cc,
161 ENI_ITEM_V items,
162 FII_OPERATING_UNITS_V ou,
163 JTF_RS_GROUPS_VL g,
164 JTF_RS_RESOURCE_EXTNS_VL r
165 WHERE a.customer_id = cust.id
166 AND a.class_code = cc.id
167 AND a.item_id = items.id
168 AND a.org_ou_id = ou.id
169 AND a.sales_grp_id = g.group_id
170 AND a.resource_id = r.resource_id
171 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
172 ORDER BY rnk';
173
174 x_custom_sql := l_stmt;
175
176 l_custom_rec.attribute_name := ':ISC_SG';
177 l_custom_rec.attribute_value := to_char(l_sg_sg);
178 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
179 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
180 x_custom_output.extend;
181 x_custom_output(1) := l_custom_rec;
182
183 l_custom_rec.attribute_name := ':ISC_RES';
184 l_custom_rec.attribute_value := to_char(l_sg_res);
185 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
186 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
187 x_custom_output.extend;
188 x_custom_output(2) := l_custom_rec;
189
190 END get_sql;
191
192 END ISC_DBI_RET_DETAIL_PKG ;
193