[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BKLG_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_BKLG_DETAIL_PKG AS
2 /* $Header: ISCRGBLB.pls 120.2 2006/05/14 21:14:33 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_suffix VARCHAR2(120);
24 l_invoice_amt VARCHAR2(100);
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 = '''FII_GLOBAL1''')
57 THEN l_curr_suffix := 'g';
58 l_invoice_amt:= 'prim_amount_g';
59 ELSE l_curr_suffix := 'g1';
60 l_invoice_amt:= 'sec_amount_g';
61 END IF;
62
63 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
64 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
65
66 IF (l_sg_res IS NULL) -- when a sales group is chosen
67 THEN
68 l_sg_where := '
69 AND sc.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)';
70 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
71 l_sg_where := '
72 AND sc.sales_grp_id = :ISC_SG
73 AND sc.resource_id = :ISC_RES';
74 END IF;
75
76
77 IF (l_cust IS NULL)
78 THEN l_cust_where := '';
79 ELSE l_cust_where := '
80 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
81 END IF;
82
83 IF (l_class IS NULL)
84 THEN l_class_where:='';
85 ELSE l_class_where :='
86 AND class.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
87 END IF;
88
89 IF ((l_prod_cat IS NULL) AND (l_prod IS NULL)) -- Prod Cat=All, Product=All
90 THEN l_prod_cat_from := '';
91 l_prod_cat_where := '';
92 l_prod_where := '';
93 ELSIF (l_prod IS NULL) -- Prod Cat selected, Product=All
94 THEN l_prod_cat_from := ',
95 ENI_DENORM_HIERARCHIES eni_cat,
96 MTL_DEFAULT_CATEGORY_SETS mdcs';
97 l_prod_cat_where := '
98 AND star.vbh_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 l_prod_where := '';
105 ELSE -- Product selected, Prod Cat selected OR All
106 l_prod_cat_from := '';
107 l_prod_cat_where := '';
108 l_prod_where := '
109 AND star.master_id IN (&ITEM+ENI_ITEM)';
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 l_stmt := '
116 SELECT ISC_ATTRIBUTE_1, -- Order Number
117 ISC_ATTRIBUTE_2, -- Line Number
118 ou.value ISC_ATTRIBUTE_3, -- Operating Unit
119 ISC_ATTRIBUTE_4, -- Booked Date
120 cust.value ISC_ATTRIBUTE_5, -- Customer
121 cc.value ISC_ATTRIBUTE_10, -- Customer Classification
122 items.value ISC_ATTRIBUTE_6, -- Item
123 items.description ISC_ATTRIBUTE_7, -- Description
124 g.group_name ISC_ATTRIBUTE_8, -- Sales Group
125 r.resource_name ISC_ATTRIBUTE_9, -- Sales Representative
126 ISC_MEASURE_2, -- Backlog Sales Credit
127 ISC_MEASURE_3, -- Grand Total - Backlog Sales Credit
128 ISC_MEASURE_4 -- Header ID
129 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE, isc_attribute_1 desc, isc_attribute_2, org_ou_id))-1 RNK,
130 org_ou_id, customer_id, class_code, item_id, sales_grp_id, resource_id,
131 ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_4,
132 ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4
133 FROM (SELECT org_ou_id, customer_id, class_code, item_id, sales_grp_id, resource_id,
134 ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_4,
135 ISC_MEASURE_2, sum(isc_measure_2) over () ISC_MEASURE_3, ISC_MEASURE_4
136 FROM (
137 /* Get orders that have not been invoiced */
138 SELECT f.org_ou_id ORG_OU_ID,
139 f.customer_id CUSTOMER_ID,
140 class.class_code CLASS_CODE,
141 nvl(star.master_id, star.id) ITEM_ID,
142 sc.sales_grp_id SALES_GRP_ID,
143 sc.resource_id RESOURCE_ID,
144 f.order_number ISC_ATTRIBUTE_1,
145 f.line_number ISC_ATTRIBUTE_2,
146 f.time_booked_date_id ISC_ATTRIBUTE_4,
147 decode(f.line_category_code, ''RETURN'',-1,1)
148 * f.booked_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100
149 ISC_MEASURE_2,
150 f.header_id ISC_MEASURE_4
151 FROM ISC_BOOK_SUM2_F f,
152 ISC_SALES_CREDITS_F sc,
153 ENI_OLTP_ITEM_STAR star,
154 FII_PARTY_MKT_CLASS class'
155 ||l_prod_cat_from||'
156 WHERE f.time_booked_date_id <= &BIS_CURRENT_ASOF_DATE
157 AND NOT(f.time_fulfilled_date_id IS NULL AND f.open_flag = ''N'')
158 AND NOT EXISTS (SELECT 1 FROM fii_ar_revenue_b rev
159 WHERE to_char(f.line_id) = rev.child_order_line_id
160 AND rev.invoice_date <= &BIS_CURRENT_ASOF_DATE
161 AND rev.om_product_revenue_flag = ''Y'')
162 AND f.line_id = sc.line_id
163 AND f.inventory_item_id = star.inventory_item_id
164 AND f.item_inv_org_id = star.organization_id
165 AND f.customer_id = class.party_id
166 AND f.item_type_code <> ''SERVICE''
167 AND f.order_source_id <> 27
168 AND f.order_source_id <> 10
169 AND f.ordered_quantity <> 0
170 AND f.charge_periodicity_code is NULL'
171 ||l_sg_where
172 ||l_prod_cat_where
173 ||l_prod_where
174 ||l_cust_where
175 ||l_class_where||'
176 UNION ALL
177 /* Get orders that have partial invoices */
178 SELECT f.org_ou_id ORG_OU_ID,
179 f.customer_id CUSTOMER_ID,
180 class.class_code CLASS_CODE,
181 nvl(star.master_id, star.id) ITEM_ID,
182 sc.sales_grp_id SALES_GRP_ID,
183 sc.resource_id RESOURCE_ID,
184 f.order_number ISC_ATTRIBUTE_1,
185 f.line_number ISC_ATTRIBUTE_2,
186 f.time_booked_date_id ISC_ATTRIBUTE_4,
187 (decode(f.line_category_code, ''RETURN'',-1,1)
188 * f.booked_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100)
189 - sum(rev.'||l_invoice_amt||' * sr.revenue_percent_split / 100)
190 ISC_MEASURE_2,
191 f.header_id ISC_MEASURE_4
192 FROM ISC_BOOK_SUM2_F f,
193 ISC_SALES_CREDITS_F sc,
194 ENI_OLTP_ITEM_STAR star,
195 FII_PARTY_MKT_CLASS class,
196 FII_AR_REVENUE_B rev,
197 FII_AR_SALES_CREDITS sr,
198 JTF_RS_SRP_GROUPS g'
199 ||l_prod_cat_from||'
200 WHERE f.time_booked_date_id <= &BIS_CURRENT_ASOF_DATE
201 AND NOT(f.time_fulfilled_date_id IS NULL AND f.open_flag = ''N'')
202 AND to_char(f.line_id) = rev.child_order_line_id
203 AND rev.invoice_line_id = sr.invoice_line_id
204 AND rev.invoice_date <= &BIS_CURRENT_ASOF_DATE
205 AND rev.om_product_revenue_flag = ''Y''
206 AND f.line_id = sc.line_id
207 AND g.salesrep_id = sr.salesrep_id
208 AND g.org_id = rev.operating_unit_id
209 AND rev.invoice_date between g.start_date and g.end_date
210 AND g.resource_id = sc.resource_id
211 AND f.inventory_item_id = star.inventory_item_id
212 AND f.item_inv_org_id = star.organization_id
213 AND f.customer_id = class.party_id
214 AND f.item_type_code <> ''SERVICE''
215 AND f.order_source_id <> 27
216 AND f.order_source_id <> 10
217 AND f.ordered_quantity <> 0
218 AND f.charge_periodicity_code is NULL'
219 ||l_sg_where
220 ||l_prod_cat_where
221 ||l_prod_where
222 ||l_cust_where
223 ||l_class_where||'
224 GROUP BY f.order_number, f.line_number, f.org_ou_id, f.header_id, f.customer_id, class.class_code,
225 sc.sales_grp_id, sc.resource_id, f.time_booked_date_id, nvl(star.master_id, star.id),
226 f.booked_amt_'||l_curr_suffix||', sc.sales_credit_percent, f.line_category_code
227 HAVING round(decode(f.line_category_code, ''RETURN'',-1,1)
228 * f.booked_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100)
229 - sum(rev.'||l_invoice_amt||' * sr.revenue_percent_split / 100) <> 0
230 ))
231 ) a,
232 FII_CUSTOMERS_V cust,
233 FII_PARTNER_MKT_CLASS_V cc,
234 ENI_ITEM_V items,
235 FII_OPERATING_UNITS_V ou,
236 JTF_RS_GROUPS_VL g,
237 JTF_RS_RESOURCE_EXTNS_VL r
238 WHERE a.customer_id = cust.id
239 AND a.class_code = cc.id
240 AND a.item_id = items.id
241 AND a.org_ou_id = ou.id
242 AND a.sales_grp_id = g.group_id
243 AND a.resource_id = r.resource_id
244 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
245 ORDER BY rnk';
246
247 x_custom_sql := l_stmt;
248
249 l_custom_rec.attribute_name := ':ISC_SG';
250 l_custom_rec.attribute_value := to_char(l_sg_sg);
251 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
252 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
253 x_custom_output.extend;
254 x_custom_output(1) := l_custom_rec;
255
256 l_custom_rec.attribute_name := ':ISC_RES';
257 l_custom_rec.attribute_value := to_char(l_sg_res);
258 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
259 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
260 x_custom_output.extend;
261 x_custom_output(2) := l_custom_rec;
262
263 END get_sql;
264
265 END ISC_DBI_BKLG_DETAIL_PKG ;
266