[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BK_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_BK_DETAIL_PKG AS
2 /* $Header: ISCRGBJB.pls 120.1 2006/05/14 21:12:57 abhdixi 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_custom_rec BIS_QUERY_ATTRIBUTES ;
22 l_curr VARCHAR2(10000);
23 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
24 l_curr_suffix VARCHAR2(120);
25
26 BEGIN
27 FOR i IN 1..p_param.COUNT
28 LOOP
29
30 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
31 l_sgid := p_param(i).parameter_id;
32 END IF;
33
34 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
35 l_prod_cat := p_param(i).parameter_id;
36 END IF;
37
38 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM') THEN
39 l_prod := p_param(i).parameter_id;
40 END IF;
41
42 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
43 l_cust := p_param(i).parameter_id;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
47 l_class := p_param(i).parameter_id;
48 END IF;
49
50 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
51 THEN l_curr := p_param(i).parameter_id;
52 END IF;
53
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 sc.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 sc.sales_grp_id = :ISC_SG
71 AND sc.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 f.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 class.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
85 END IF;
86
87 IF ((l_prod_cat IS NULL) AND (l_prod IS NULL)) -- Prod Cat=All, Product=All
88 THEN l_prod_cat_from := '';
89 l_prod_cat_where := '';
90 l_prod_where := '';
91 ELSIF (l_prod IS NULL) -- Prod Cat selected, Product=All
92 THEN l_prod_cat_from := ',
93 ENI_DENORM_HIERARCHIES eni_cat,
94 MTL_DEFAULT_CATEGORY_SETS mdcs';
95 l_prod_cat_where := '
96 AND star.vbh_category_id = eni_cat.child_id
97 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
98 AND eni_cat.dbi_flag = ''Y''
99 AND eni_cat.object_type = ''CATEGORY_SET''
100 AND eni_cat.object_id = mdcs.category_set_id
101 AND mdcs.functional_area_id = 11';
102 l_prod_where := '';
103 ELSE -- Product selected, Prod Cat selected OR All
104 l_prod_cat_from := '';
105 l_prod_cat_where := '';
106 l_prod_where := '
107 AND star.master_id IN (&ITEM+ENI_ITEM)';
108 END IF;
109
110 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
111 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
112
113 l_stmt := '
114 SELECT ISC_ATTRIBUTE_1, -- Order Number
115 ISC_ATTRIBUTE_2, -- Line Number
116 ou.value ISC_ATTRIBUTE_3, -- Operating Unit
117 ISC_ATTRIBUTE_4, -- Booked Date
118 cust.value ISC_ATTRIBUTE_5, -- Customer
119 cc.value ISC_ATTRIBUTE_10, -- Customer Classification
120 items.value ISC_ATTRIBUTE_6, -- Item
121 items.description ISC_ATTRIBUTE_7, -- Description
122 g.group_name ISC_ATTRIBUTE_8, -- Sales Group
123 r.resource_name ISC_ATTRIBUTE_9, -- Sales Representative
124 ISC_MEASURE_2, -- Booked Order Sales Credit
125 ISC_MEASURE_3, -- Grand Total - Booked Order Sales Credit
126 ISC_MEASURE_4 -- Header ID
127 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE, isc_attribute_1 desc, isc_attribute_2, org_ou_id))-1 RNK,
128 org_ou_id, customer_id, class_code, item_id, sales_grp_id, resource_id,
129 ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_4,
130 ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4
131 FROM (SELECT f.org_ou_id ORG_OU_ID,
132 f.customer_id CUSTOMER_ID,
133 class.class_code CLASS_CODE,
134 nvl(star.master_id, star.id) ITEM_ID,
135 sc.sales_grp_id SALES_GRP_ID,
136 sc.resource_id RESOURCE_ID,
137 f.order_number ISC_ATTRIBUTE_1,
138 f.line_number ISC_ATTRIBUTE_2,
139 f.time_booked_date_id ISC_ATTRIBUTE_4,
140 f.booked_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100
141 ISC_MEASURE_2,
142 sum(f.booked_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100) over ()
143 ISC_MEASURE_3,
144 f.header_id ISC_MEASURE_4
145 FROM ISC_BOOK_SUM2_F f,
146 ISC_SALES_CREDITS_F sc,
147 ENI_OLTP_ITEM_STAR star,
148 FII_PARTY_MKT_CLASS class'
149 ||l_prod_cat_from||'
150 WHERE f.time_booked_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
151 AND &BIS_CURRENT_ASOF_DATE
152 AND f.line_id = sc.line_id
153 AND f.inventory_item_id = star.inventory_item_id
154 AND f.item_inv_org_id = star.organization_id
155 AND f.customer_id = class.party_id
156 AND f.line_category_code <> ''RETURN''
157 AND f.item_type_code <> ''SERVICE''
158 AND f.order_source_id <> 27
159 AND f.order_source_id <> 10
160 AND f.ordered_quantity <> 0
161 AND f.charge_periodicity_code is NULL'
162 ||l_sg_where
163 ||l_prod_cat_where
164 ||l_prod_where
165 ||l_cust_where
166 ||l_class_where||')
167 ) a,
168 FII_CUSTOMERS_V cust,
169 FII_PARTNER_MKT_CLASS_V cc,
170 ENI_ITEM_V items,
171 FII_OPERATING_UNITS_V ou,
172 JTF_RS_GROUPS_VL g,
173 JTF_RS_RESOURCE_EXTNS_VL r
174 WHERE a.customer_id = cust.id
175 AND a.class_code = cc.id
176 AND a.item_id = items.id
177 AND a.org_ou_id = ou.id
178 AND a.sales_grp_id = g.group_id
179 AND a.resource_id = r.resource_id
180 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
181 ORDER BY rnk';
182
183 x_custom_sql := l_stmt;
184
185 l_custom_rec.attribute_name := ':ISC_SG';
186 l_custom_rec.attribute_value := to_char(l_sg_sg);
187 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
188 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
189 x_custom_output.extend;
190 x_custom_output(1) := l_custom_rec;
191
192 l_custom_rec.attribute_name := ':ISC_RES';
193 l_custom_rec.attribute_value := to_char(l_sg_res);
194 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
195 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
196 x_custom_output.extend;
197 x_custom_output(2) := l_custom_rec;
198
199 END get_sql;
200
201 END ISC_DBI_BK_DETAIL_PKG ;
202