[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_CUD_PKG
Source
1 PACKAGE BODY poa_dbi_cud_pkg
2 /* $Header: poadbicudb.pls 120.7 2006/08/08 11:00:06 nchava noship $*/
3 AS
4 FUNCTION get_con_dtl_filter_where return VARCHAR2;
5 FUNCTION get_ncp_dtl_filter_where return VARCHAR2;
6 FUNCTION get_pcl_dtl_filter_where return VARCHAR2;
7
8 PROCEDURE con_drill_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
9 x_custom_sql OUT NOCOPY VARCHAR2,
10 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12 l_query varchar2(8000);
13 l_cur_suffix varchar2(2);
14 l_where_clause varchar2(2000);
15 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
16 l_in_join_tables VARCHAR2(1000) ;
17 l_sec_context varchar2(10);
18 BEGIN
19 l_in_join_tables := '';
20 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
21
22 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
23 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
24 poa_dbi_sutil_pkg.drill_process_parameters(
25 p_param => p_param,
26 p_cur_suffix => l_cur_suffix,
27 p_where_clause => l_where_clause,
28 p_in_join_tbl => l_in_join_tbl,
29 p_func_area => 'PO',
30 p_version => '6.0',
31 p_role => 'COM',
32 p_mv_set => 'PODCUT');
33 elsif(l_sec_context = 'COMP') then
34 poa_dbi_sutil_pkg.drill_process_parameters(
35 p_param => p_param,
36 p_cur_suffix => l_cur_suffix,
37 p_where_clause => l_where_clause,
38 p_in_join_tbl => l_in_join_tbl,
39 p_func_area => 'PO',
40 p_version => '8.0',
41 p_role => 'COM',
42 p_mv_set => 'PODCUTB');
43 end if;
44
45 IF(l_in_join_tbl is not null) then
46 FOR i in 1 .. l_in_join_tbl.COUNT LOOP
47 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
48 END LOOP;
49 END IF;
50
51 l_query :=
52 'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, -- PO Number
53 (case when (i.shipment_type in (''BLANKET'',''SCHEDULED'')) then poh.segment1
54 when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'') then bl.segment1
55 when (i.POA_MEASURE2 > 0) then ''Catalog''
56 else '''' end) POA_ATTRIBUTE2, -- Contract Number
57 item.value POA_ATTRIBUTE3, -- Item
58 item.description POA_ATTRIBUTE4, -- Description
59 uom.description POA_ATTRIBUTE5, -- UOM
60 POA_MEASURE1, -- Quantity
61 POA_MEASURE2, -- Contract Purchases Amt
62 POA_MEASURE3, -- Total Contract Purchases
63 i.po_header_id POA_ATTRIBUTE6, -- PO Header ID
64 i.po_release_id POA_ATTRIBUTE7, -- PO Release ID
65 poorg.name POA_ATTRIBUTE8, -- Operating Unit
66 (case when (i.shipment_type in (''BLANKET'',''SCHEDULED'')) then poorg.name
67 when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'') then blorg.name
68 when (i.POA_MEASURE2 > 0) then '' ''
69 else '''' end) POA_ATTRIBUTE9, -- Operating Unit
70 (case when (i.shipment_type in (''BLANKET'',''SCHEDULED''))
71 then ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id||''&addBreadCrumb=Y&retainAM=Y''
72 when (i.shipment_type = ''STANDARD'' and bl.type_lookup_code = ''BLANKET'')
73 then ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||bl.po_header_id||''&addBreadCrumb=Y&retainAM=Y''
74 when (i.POA_MEASURE2 > 0) then NULL
75 else NULL end) POA_ATTRIBUTE10 -- Contract Number Drill
76 from
77 ( select (rank() over
78 (&ORDER_BY_CLAUSE nulls last, po_header_id, po_item_id, base_uom,
79 shipment_type, from_document_id, po_release_id, org_id)) - 1 rnk,
80 po_header_id,
81 po_item_id,
82 org_id,
83 base_uom,
84 shipment_type,
85 from_document_id,
86 po_release_id,
87 decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
88 nvl(POA_MEASURE2,0) POA_MEASURE2,
89 nvl(POA_MEASURE3,0) POA_MEASURE3
90 from
91 ( select fact.po_header_id,
92 fact.po_item_id,
93 fact.base_uom,
94 fact.shipment_type,
95 fact.from_document_id,
96 fact.po_release_id,
97 fact.org_id,
98 sum(quantity) POA_MEASURE1,
99 sum(contract_amt_' || l_cur_suffix || ') POA_MEASURE2,
100 sum(sum(contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE3
101 from poa_dbi_pod_f_v fact
102 ' || l_in_join_tables || '
103 where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
104 and &BIS_CURRENT_ASOF_DATE
105 ' || l_where_clause || '
106 and fact.consigned_code <> 1
107 and fact.contract_type is not null ';
108 if (l_sec_context = 'OU/COM') then
109 l_query := l_query || fnd_global.newline||
110 'and fact.commodity_id <> -1 ';
111 elsif (l_sec_context = 'COMP') then
112 l_query := l_query ||
113 'and fact.company_id = com.child_company_id
114 and fact.cost_center_id = cc.child_cc_id'||fnd_global.newline;
115 end if;
116 l_query := l_query ||
117 'group by fact.po_header_id,
118 fact.po_item_id,
119 fact.base_uom,
120 fact.shipment_type,
121 fact.from_document_id,
122 fact.po_release_id,
123 fact.org_id
124 )
125 where ' || get_con_dtl_filter_where || '
126 ) i,
127 po_headers_all bl,
128 po_releases_all rel,
129 po_headers_all poh,
130 poa_items_v item,
131 mtl_units_of_measure_vl uom,
132 hr_all_organization_units_vl poorg,
133 hr_all_organization_units_vl blorg
134 where i.po_header_id = poh.po_header_id
135 and i.po_item_id = item.id
136 and i.base_uom = uom.unit_of_measure(+)
137 and i.org_id = poorg.organization_id
138 and bl.org_id = blorg.organization_id(+)
139 and i.from_document_id = bl.po_header_id (+)
140 and i.po_release_id = rel.po_release_id (+)
141 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
142 ORDER BY rnk ';
143
144 x_custom_sql := l_query;
145 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
146 if(l_sec_context = 'COMP')then
147 poa_dbi_sutil_pkg.bind_com_cc_values(x_custom_output, p_param);
148 end if;
149 end;
150
151
152 PROCEDURE ncp_drill_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
153 x_custom_sql OUT NOCOPY VARCHAR2,
154 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
155 IS
156 l_query varchar2(10000);
157 l_cur_suffix varchar2(2);
158 l_where_clause varchar2(2000);
159 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
160 l_in_join_tables VARCHAR2(1000);
161 l_sec_context varchar2(10);
162 BEGIN
163 l_in_join_tables := '';
164 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
165
166 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
167 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
168 poa_dbi_sutil_pkg.drill_process_parameters(
169 p_param => p_param,
170 p_cur_suffix => l_cur_suffix,
171 p_where_clause => l_where_clause,
172 p_in_join_tbl => l_in_join_tbl,
173 p_func_area => 'PO',
174 p_version => '6.0',
175 p_role => 'COM',
176 p_mv_set => 'PODCUT');
177 elsif (l_sec_context = 'COMP') then
178 poa_dbi_sutil_pkg.drill_process_parameters(
179 p_param => p_param,
180 p_cur_suffix => l_cur_suffix,
181 p_where_clause => l_where_clause,
182 p_in_join_tbl => l_in_join_tbl,
183 p_func_area => 'PO',
184 p_version => '8.0',
185 p_role => 'COM',
186 p_mv_set => 'PODCUTB');
187 end if;
188
189 IF(l_in_join_tbl is not null) then
190 FOR i in 1 .. l_in_join_tbl.COUNT LOOP
191 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
192 END LOOP;
193 END IF;
194
195 l_query :=
196 'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, -- PO Number-release number
197 item.value POA_ATTRIBUTE2, -- Item
198 item.description POA_ATTRIBUTE3, -- Description
199 uom.description POA_ATTRIBUTE4, -- UOM
200 POA_MEASURE1, -- Quantity
201 POA_MEASURE2, -- NC Purchases Amount
202 POA_MEASURE3, -- Total NC Purchases
203 i.po_header_id POA_ATTRIBUTE5, -- PO Header ID
204 org.name POA_ATTRIBUTE6 -- OU
205 from
206 ( select (rank() over (&ORDER_BY_CLAUSE nulls last,po_header_id, po_item_id, base_uom, org_id)) - 1 rnk,
207 po_header_id,
208 po_release_id,
209 po_item_id,
210 base_uom,
211 org_id,
212 decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
213 nvl(POA_MEASURE2,0) POA_MEASURE2,
214 nvl(POA_MEASURE3,0) POA_MEASURE3
215 from
216 ( select fact.po_header_id,
217 fact.po_release_id,
218 fact.po_item_id,
219 fact.base_uom,
220 fact.org_id,
221 sum(quantity) POA_MEASURE1,
222 sum(n_contract_amt_' || l_cur_suffix || ') POA_MEASURE2,
223 sum(sum(n_contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE3
224 from poa_dbi_pod_f_v fact
225 ' || l_in_join_tables || '
226 where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
227 ' || l_where_clause || '
228 and fact.consigned_code <> 1
229 and fact.contract_type is null ';
230 if (l_sec_context = 'OU/COM') then
231 l_query := l_query || fnd_global.newline||
232 'and fact.commodity_id <> -1 ';
233 elsif (l_sec_context = 'COMP') then
234 l_query := l_query ||
235 'and fact.company_id = com.child_company_id
236 and fact.cost_center_id = cc.child_cc_id'||fnd_global.newline;
237 end if;
238 l_query := l_query ||
239 'group by fact.po_header_id, fact.po_release_id,fact.po_item_id, fact.base_uom, fact.org_id
240 )
241 where ' || get_ncp_dtl_filter_where || '
242 ) i,
243 po_headers_all poh,
244 poa_items_v item,
245 po_releases_all rel,
246 mtl_units_of_measure_vl uom,
247 hr_all_organization_units_vl org
248 where i.po_header_id = poh.po_header_id
249 and i.po_release_id = rel.po_release_id (+)
250 and i.po_item_id = item.id
251 and i.org_id = org.organization_id
252 and i.base_uom = uom.unit_of_measure(+)
253 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
254 ORDER BY rnk';
255
256 x_custom_sql := l_query;
257 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
258 if(l_sec_context = 'COMP')then
259 poa_dbi_sutil_pkg.bind_com_cc_values(x_custom_output, p_param);
260 end if;
261 end;
262
263 PROCEDURE pcl_drill_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
264 x_custom_sql OUT NOCOPY VARCHAR2,
265 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
266 IS
267 l_query varchar2(10000);
268 l_cur_suffix varchar2(2);
269 l_where_clause varchar2(2000);
270 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
271 l_in_join_tables VARCHAR2(1000) ;
272 l_sec_context varchar2(10);
273 BEGIN
274 l_in_join_tables := '';
275 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
276
277 l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
278 if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
279 poa_dbi_sutil_pkg.drill_process_parameters(
280 p_param => p_param,
281 p_cur_suffix => l_cur_suffix,
282 p_where_clause => l_where_clause,
283 p_in_join_tbl => l_in_join_tbl,
284 p_func_area => 'PO',
285 p_version => '6.0',
286 p_role => 'COM',
287 p_mv_set => 'PODCUT');
288 elsif(l_sec_context = 'COMP') then
289 poa_dbi_sutil_pkg.drill_process_parameters(
290 p_param => p_param,
291 p_cur_suffix => l_cur_suffix,
292 p_where_clause => l_where_clause,
293 p_in_join_tbl => l_in_join_tbl,
294 p_func_area => 'PO',
295 p_version => '8.0',
296 p_role => 'COM',
297 p_mv_set => 'PODCUTB');
298 end if;
299
300 IF(l_in_join_tbl is not null) then
301 FOR i in 1 .. l_in_join_tbl.COUNT LOOP
302 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
303 END LOOP;
304 END IF;
305
306 l_query :=
307 'select poh.segment1 POA_ATTRIBUTE1, -- PO Number
308 poorg.name POA_ATTRIBUTE10, -- Unused Contract URL
309 sup.value POA_ATTRIBUTE2, -- Supplier
310 item.value POA_ATTRIBUTE3, -- Item
311 item.description POA_ATTRIBUTE4, -- Description
312 uom.description POA_ATTRIBUTE5, -- UOM
313 POA_MEASURE1, -- Quantity
314 POA_MEASURE3, -- Contract Leakage Amount
315 POA_MEASURE2, -- Leakage Impact Amount
316 bl.segment1 POA_ATTRIBUTE6, -- Unused Contract Number
317 blorg.name POA_ATTRIBUTE12, -- Operating Unit
318 bl_sup.value POA_ATTRIBUTE7, -- Unused Supplier
319 POA_MEASURE4, -- Total Leakage Impact Amount
320 POA_MEASURE5, -- Total Contract Leakage Amt
321 i.po_header_id POA_ATTRIBUTE8, -- PO Header ID
322 i.potential_contract_id POA_ATTRIBUTE9, -- PO Release ID
323 decode(bl.segment1,null,null,''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||i.potential_contract_id||''&PoReleaseId=&addBreadCrumb=Y&retainAM=Y'') POA_ATTRIBUTE11
324 from
325 ( select (rank() over (&ORDER_BY_CLAUSE nulls last,po_header_id,po_item_id,
326 base_uom, potential_contract_id,
327 supplier_id, org_id)) - 1 rnk,
328 po_header_id,
329 po_item_id,
330 base_uom,
331 potential_contract_id,
332 supplier_id,
333 org_id,
334 decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
335 nvl(POA_MEASURE2,0) POA_MEASURE2,
336 nvl(POA_MEASURE3,0) POA_MEASURE3,
337 nvl(POA_MEASURE4,0) POA_MEASURE4,
338 nvl(POA_MEASURE5,0) POA_MEASURE5
339 from
340 ( select fact.po_header_id,
341 fact.po_item_id,
342 fact.base_uom,
343 fact.potential_contract_id,
344 fact.supplier_id,
345 fact.org_id,
346 sum(quantity) POA_MEASURE1,
347 sum(p_savings_amt_' || l_cur_suffix || ') POA_MEASURE2,
348 sum(p_contract_amt_' || l_cur_suffix || ') POA_MEASURE3,
349 sum(sum(p_savings_amt_' || l_cur_suffix || ')) over () POA_MEASURE4,
350 sum(sum(p_contract_amt_' || l_cur_suffix || ')) over () POA_MEASURE5
351 from poa_dbi_pod_f_v fact
352 ' || l_in_join_tables || '
353 where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
354 and &BIS_CURRENT_ASOF_DATE
355 ' || l_where_clause || '
356 and fact.consigned_code <> 1
357 and fact.contract_type is null
358 and ((fact.p_contract_amt_b is not null and fact.p_contract_amt_b != 0) or (fact.p_savings_amt_b is not null and fact.p_savings_amt_b != 0)) ';
359 if (l_sec_context = 'OU/COM') then
360 l_query := l_query || fnd_global.newline||
361 'and fact.commodity_id <> -1 ';
362 elsif (l_sec_context = 'COMP') then
363 l_query := l_query ||
364 'and fact.company_id = com.child_company_id
365 and fact.cost_center_id = cc.child_cc_id'||fnd_global.newline;
366 end if;
367 l_query := l_query ||
368 'group by fact.po_header_id,
369 fact.po_item_id,
370 fact.base_uom,
371 fact.potential_contract_id,
372 fact.supplier_id, fact.org_id
373 )
374 where ' || get_pcl_dtl_filter_where || '
375 )i,
376 po_headers_all poh,
377 poa_items_v item,
378 mtl_units_of_measure_vl uom,
379 po_headers_all bl,
380 poa_suppliers_v sup,
381 poa_suppliers_v bl_sup,
382 hr_all_organization_units_vl poorg,
383 hr_all_organization_units_vl blorg
384 where i.po_header_id = poh.po_header_id
385 and i.po_item_id = item.id
386 and i.org_id = poorg.organization_id
387 and bl.org_id = blorg.organization_id(+)
388 and i.base_uom = uom.unit_of_measure
389 and i.potential_contract_id = bl.po_header_id (+)
390 and i.supplier_id = sup.id
391 and bl.vendor_id = bl_sup.id (+)
392 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
393 ORDER BY rnk';
394
395 x_custom_sql := l_query;
399 end if;
396 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
397 if(l_sec_context = 'COMP')then
398 poa_dbi_sutil_pkg.bind_com_cc_values(x_custom_output, p_param);
400 end;
401
402 FUNCTION get_con_dtl_filter_where return VARCHAR2
403 IS
404 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
405 BEGIN
406 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
407 l_col_tbl.extend;
408 l_col_tbl(1) := 'POA_MEASURE1';
409 l_col_tbl.extend;
410 l_col_tbl(2) := 'POA_MEASURE2';
411 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
412
413 END;
414
415 FUNCTION get_ncp_dtl_filter_where return VARCHAR2
416 IS
417 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
418 BEGIN
419 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
420 l_col_tbl.extend;
421 l_col_tbl(1) := 'POA_MEASURE1';
422 l_col_tbl.extend;
423 l_col_tbl(2) := 'POA_MEASURE2';
424 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
425
426 END;
427
428 FUNCTION get_pcl_dtl_filter_where return VARCHAR2
429 IS
430 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
431 BEGIN
432 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
433 l_col_tbl.extend;
434 l_col_tbl(1) := 'POA_MEASURE1';
435 l_col_tbl.extend;
436 l_col_tbl(2) := 'POA_MEASURE2';
437 l_col_tbl.extend;
438 l_col_tbl(3) := 'POA_MEASURE3';
439 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
440
441 END;
442
443
444 end poa_dbi_cud_pkg;