eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POA_DBI_PC_PKG

Source


1 PACKAGE BODY poa_dbi_pc_pkg
2 /* $Header: poadbipcb.pls 120.5 2006/08/25 13:00:19 ankgoyal noship $ */
3 AS
4 
5 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
6 FUNCTION get_dtl_filter_where return VARCHAR2;
7   FUNCTION get_status_sel_clause(p_view_by_dim	IN VARCHAR2
8 				,p_url		IN VARCHAR2
9 				,p_view_by_col	IN VARCHAR2) RETURN VARCHAR2;
10 
11 
12   PROCEDURE status_sql(p_param		IN	    BIS_PMV_PAGE_PARAMETER_TBL
13 		      ,x_custom_sql	OUT NOCOPY  VARCHAR2,
14 		       x_custom_output	OUT NOCOPY  BIS_QUERY_ATTRIBUTES_TBL)
15   IS
16     l_query		VARCHAR2(20000);
17     l_view_by_col	VARCHAR2(120);
18     l_view_by_value	VARCHAR2(300);
19     l_view_by		VARCHAR2(120);
20     l_as_of_date	DATE;
21     l_prev_as_of_date	DATE;
22     l_xtd		VARCHAR2(10);
23     l_comparison_type	VARCHAR2(1) := 'Y';
24 
25     l_nested_pattern	NUMBER;
26     l_cur_suffix	VARCHAR2(2);
27     l_col_tbl		POA_DBI_UTIL_PKG.POA_DBI_COL_TBL;
28     l_url		VARCHAR2(300);
29     l_join_tbl		POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL;
30     l_in_join_tbl	POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
31     l_where_clause	VARCHAR2(2000);
32     l_mv		VARCHAR2(30);
33     ERR_MSG		VARCHAR2(100);
34     ERR_CDE		NUMBER;
35     l_context_code VARCHAR2(10);
36     l_to_date_type VARCHAR2(10);
37    BEGIN
38      l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
39      l_col_tbl	:= poa_dbi_util_pkg.POA_DBI_COL_TBL();
40      poa_dbi_sutil_pkg.process_parameters(p_param
41 					 ,l_view_by
42 					 ,l_view_by_col
43 					 ,l_view_by_value
44 					 ,l_comparison_type
45 					 ,l_xtd
46 					 ,l_as_of_date
47 					 ,l_prev_as_of_date
48 					 ,l_cur_suffix
49 					 ,l_nested_pattern
50 					 ,l_where_clause
51 					 ,l_mv
52 					 ,l_join_tbl
53 					 ,l_in_join_tbl
54 					, x_custom_output
55 					 ,'N'
56 					 ,'PO'
57 					 ,'6.0'
58 					 ,'COM'
59 					 ,'PQC');
60    l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
61    IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
62     l_to_date_type := 'RLX';
63    ELSE
64     l_to_date_type := 'XTD';
65    END IF;
66 
67 
68    poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_'
69 		|| l_cur_suffix, 'purchase_amt',p_to_date_type => l_to_date_type);
70 
71    poa_dbi_util_pkg.add_column(l_col_tbl,
72 		'pbpcqcs_amt_' || l_cur_suffix, 'pbpcqcs_amt',p_to_date_type => l_to_date_type);
73 
74 
75    if(l_view_by= 'ITEM+POA_ITEMS') then
76      poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity' , 'quantity',p_to_date_type => l_to_date_type);
77    end if;
78 
79    if((l_view_by='ITEM+ENI_ITEM_PO_CAT') and (l_view_by_value is not null)
80 	and (instr(l_view_by_value,',') =0)) then
81      	l_url := null;
82    else
83      if(l_view_by = 'ITEM+POA_ITEMS') then
84        l_url:='pFunctionName=POA_DBI_PC_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
85      else
86        l_url:='pFunctionName=POA_DBI_PC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y';
87      end if;
88     end if;
89 
90    l_query := get_status_sel_clause(l_view_by, l_url,l_view_by_col)
91 	|| ' from ' ||
92 	poa_dbi_template_pkg.status_sql(l_mv,l_where_clause,
93 					l_join_tbl,
94 					p_use_windowing => 'Y',
95 					p_col_name => l_col_tbl,
96 					p_use_grpid => 'N',
97 					p_filter_where => get_status_filter_where(l_view_by),
98 					p_in_join_tables => l_in_join_tbl);
99 
100 
101    x_custom_sql := l_query;
102 
103 END;
104 
105 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
106   IS
107     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
108   BEGIN
109     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
110     l_col_tbl.extend;
111     l_col_tbl(1) := 'POA_MEASURE1';
112     l_col_tbl.extend;
113     l_col_tbl(2) := 'POA_PERCENT2';
114     l_col_tbl.extend;
115     l_col_tbl(3) := 'POA_PERCENT1';
116     l_col_tbl.extend;
117     l_col_tbl(4) := 'POA_MEASURE2';
118 
119    if(p_view_by= 'ITEM+POA_ITEMS') then
120 	l_col_tbl.extend;
121 	l_col_tbl(5) := 'POA_MEASURE12';
122    end if;
123 
124 
125     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
126 
127   END;
128 
129 
130 FUNCTION get_status_sel_clause(p_view_by_dim	IN VARCHAR2
131 			      ,p_url		IN VARCHAR2
132 			      ,p_view_by_col	IN VARCHAR2) RETURN VARCHAR2
133 IS
134   l_sel_clause		VARCHAR2(8000);
135   l_view_by_col_name	VARCHAR2(40);
136 BEGIN
137   l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim,
138 	 'PO', '6.0');
139 
140   if(p_view_by_dim = 'ITEM+POA_ITEMS') then
141     l_sel_clause :=  l_sel_clause ||' v.description POA_ATTRIBUTE1,
142 	v2.description POA_ATTRIBUTE2,
143 	oset.POA_MEASURE12 POA_MEASURE12,';
144   else
145     l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1,
146 	null POA_ATTRIBUTE2,null POA_MEASURE12,';
147 
148   end if;
149 
150   l_sel_clause := l_sel_clause || '
151 	oset.POA_MEASURE1 POA_MEASURE1, 	--Price Change Amount
152 	oset.POA_PERCENT1 POA_PERCENT1,	--Price Change Rate
153 	oset.POA_MEASURE2 POA_MEASURE2,	--PO Purchases Amount
154 	oset.POA_PERCENT2 POA_PERCENT2,	--Change
155 	oset.POA_MEASURE3 POA_MEASURE3,	--Total Price Change Amount
156 	oset.POA_MEASURE4 POA_MEASURE4,	--Total Price Change Rate
157 	oset.POA_MEASURE5 POA_MEASURE5,	--Total PO Purchases Amount
158 	oset.POA_MEASURE6 POA_MEASURE6,	--Total Change
159 	''' || p_url || ''' POA_ATTRIBUTE4,
160 	oset.POA_MEASURE1 POA_MEASURE7,	--KPI Current Amount
161 	oset.POA_MEASURE8 POA_MEASURE8,	--KPI Prior Amount
162 	oset.POA_MEASURE3 POA_MEASURE9,	--Total KPI Current Amount
163 	oset.POA_MEASURE10 POA_MEASURE10	--Total KPI Prior Amount
164 from
165 		  (select (rank() over (&ORDER_BY_CLAUSE nulls last, '
166 			|| p_view_by_col;
167 
168 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
169 	l_sel_clause := l_sel_clause || ', base_uom';
170 end if;
171 
172 l_sel_clause := l_sel_clause ||
173 ')) - 1 rnk,'|| p_view_by_col
174 			||',' ;
175 
176 	 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
177 
178 	   l_sel_clause :=  l_sel_clause ||
179 		' base_uom,POA_MEASURE12, ';
180 
181 	 end if;
182 	l_sel_clause :=  l_sel_clause ||' POA_MEASURE1, POA_PERCENT1,
183 					  POA_MEASURE2, POA_PERCENT2,
184 					  POA_MEASURE3, POA_MEASURE4,
185 					  POA_MEASURE5, POA_MEASURE6,
186 					  POA_MEASURE8,
187 					  POA_MEASURE10
188 	from
189        (select ' || p_view_by_col || ',
190 	       ' || p_view_by_col || ' VIEWBY,'|| p_view_by_col || ' VIEWBYID,';
191 
192       if(p_view_by_dim = 'ITEM+POA_ITEMS') then
193 	l_sel_clause :=  l_sel_clause ||'base_uom,
194 		nvl(c_quantity,0) POA_MEASURE12, ';
195 
196       end if;
197 
198       l_sel_clause :=	l_sel_clause ||
199 		'nvl(c_purchase_amt,0) - nvl(c_pbpcqcs_amt,0) POA_MEASURE1,
200   ((c_purchase_amt - c_pbpcqcs_amt)/decode(c_purchase_amt,0,null,c_purchase_amt))*100 POA_PERCENT1,
201    nvl(c_purchase_amt,0) POA_MEASURE2,
202    ' ||  poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT2,
203    nvl(c_purchase_amt_total,0) - nvl(c_pbpcqcs_amt_total,0) POA_MEASURE3,
204    ((c_purchase_amt_total - c_pbpcqcs_amt_total)/decode(c_purchase_amt_total,0,null,c_purchase_amt_total))*100 POA_MEASURE4,
205    nvl(c_purchase_amt_total,0) POA_MEASURE5,
206    ' ||  poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_MEASURE6,
207    nvl(p_purchase_amt,0) - nvl(p_pbpcqcs_amt,0) POA_MEASURE8,
208    nvl(p_purchase_amt_total,0) - nvl(p_pbpcqcs_amt_total,0) POA_MEASURE10';
209 
210      RETURN l_sel_clause;
211 END;
212 
213 
214 FUNCTION get_dtl_filter_where return VARCHAR2
215   IS
216     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
217   BEGIN
218     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
219     l_col_tbl.extend;
220     l_col_tbl(1) := 'POA_MEASURE1';
221     l_col_tbl.extend;
222     l_col_tbl(2) := 'POA_MEASURE3';
223     l_col_tbl.extend;
224     l_col_tbl(3) := 'POA_MEASURE4';
225     l_col_tbl.extend;
226     l_col_tbl(4) := 'POA_MEASURE5';
227     l_col_tbl.extend;
228     l_col_tbl(5) := 'POA_MEASURE6';
229 
230     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
231 
232   END;
233 
234 PROCEDURE dtl_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
235                       x_custom_sql  OUT NOCOPY VARCHAR2,
236                       x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
237   IS
238         l_query varchar2(8000);
239         l_cur_suffix varchar2(2);
240         l_where_clause varchar2(2000);
241         l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
242         l_in_join_tables    VARCHAR2(240) := '';
243 	l_filter_where VARCHAR2(240);
244   BEGIN
245 
246   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
247 
248   poa_dbi_sutil_pkg.drill_process_parameters(p_param, l_cur_suffix, l_where_clause, l_in_join_tbl, 'PO', '6.0', 'COM','PQC');
249 
250   IF(l_in_join_tbl is not null) then
251 
252         FOR i in 1 .. l_in_join_tbl.COUNT
253         LOOP
254           l_in_join_tables := l_in_join_tables || ', ' ||  l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
255         END LOOP;
256   END IF;
257 
258 
259   l_filter_where := get_dtl_filter_where;
260 
261   l_query :=
262   'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, -- Po Number
263    pol.line_num POA_ATTRIBUTE2, 	--Line Number
264    poorg.name POA_ATTRIBUTE3,		--Operating Unit
265    supplier.value POA_ATTRIBUTE8,	--Supplier
266    item.value POA_ATTRIBUTE4,		--Item
267    uom.description POA_ATTRIBUTE5,	--UOM
268    POA_MEASURE1,			--Quantity
269    POA_MEASURE2,			--Supplier Benchmark Price
270    POA_MEASURE3,			--PO Price
271    POA_MEASURE4,			--Price Difference
272    POA_MEASURE5,			--Price Change Amount
273    POA_MEASURE6,			--PO Purchases Amount
274    POA_MEASURE7,			--Price Ch.Amt Total
275    POA_MEASURE8,			--PO Purch amt Total
276    i.po_header_id POA_ATTRIBUTE6,	--PO Header Id (hidden)
277    i.po_release_id POA_ATTRIBUTE7	--PO Release Id (hidden)
278   from
279    (select (rank() over
280             (&ORDER_BY_CLAUSE nulls last, po_header_id, po_line_id,
281 		po_item_id, base_uom, po_release_id, org_id,
282 		supplier_id, POA_MEASURE2, POA_MEASURE6)) - 1 rnk,
283             po_header_id,
284             po_line_id,
285             po_item_id,
286             org_id,
287             supplier_id,
288             base_uom,
289             po_release_id,
290 	    decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
291             POA_MEASURE2,
292             POA_MEASURE3,
293             POA_MEASURE4,
294             nvl(POA_MEASURE5,0) POA_MEASURE5,
295             nvl(POA_MEASURE6,0) POA_MEASURE6,
296             nvl(POA_MEASURE7,0) POA_MEASURE7,
297             nvl(POA_MEASURE8,0) POA_MEASURE8
298     from
299       (select f.po_header_id,
300         f.po_line_id,
301         f.po_item_id,
302         f.base_uom,
303         f.po_release_id,
304         f.org_id,
305         f.supplier_id,
306      sum(f.quantity) POA_MEASURE1,
307         nvl(f.pisp_amt_'
308 	|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
309 	|| '/f.pisp_quantity, cisp.purchase_amt_'
310 	|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
311 	|| '/cisp.quantity)'
312 	|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
313 	|| ' POA_MEASURE2,
314         f.purchase_amt_' || l_cur_suffix || '/f.quantity POA_MEASURE3,
315         ((f.purchase_amt_' || l_cur_suffix || '/f.quantity) - (
316         nvl(f.pisp_amt_'
317 	|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
318 	|| '/f.pisp_quantity, cisp.purchase_amt_'
319 	|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
320 	|| '/cisp.quantity)'
321 	|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
322 	|| '))  POA_MEASURE4 ,
323         sum(f.quantity * (f.purchase_amt_'
324  --Start fix for bug#5227377
325 	--|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
326 	|| l_cur_suffix
327  --End fix for bug#5227377
328 	|| '/f.quantity - nvl(f.pisp_amt_'
329 	||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
330 	     || '/f.pisp_quantity, cisp.purchase_amt_'
331 	||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
332 	|| '/cisp.quantity)'
333 	|| (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end) || ')) POA_MEASURE5,
334         sum(f.purchase_amt_' || l_cur_suffix || ') POA_MEASURE6,
335         sum(sum(f.quantity * (f.purchase_amt_'
336  --Start fix for bug#5353831
337 	--|| (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
338 	|| l_cur_suffix
339  --End fix for bug#5353831
340 	|| '/f.quantity - nvl(f.pisp_amt_'
341 	||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
342 	|| '/f.pisp_quantity, cisp.purchase_amt_'
343 	||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end)
344 	|| '/cisp.quantity)'
345            || (case l_cur_suffix when 'b' then '/decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate)' end)
346 	|| '))) over () POA_MEASURE7,
347         sum(sum(f.purchase_amt_' || l_cur_suffix || ')) over () POA_MEASURE8
348       from      poa_bm_item_s_mv cisp,
349 	(select /*+ NO_MERGE */ fact.po_header_id,
350         fact.po_line_id,
351         fact.po_item_id,
352         fact.base_uom,
353         fact.po_release_id,
354         fact.org_id,
355         fact.supplier_id,
356 	fact.global_cur_conv_rate,
357 	fact.ent_year_id,
358 	fact.purchase_amt_b,
359 	fact.purchase_amt_sg,
360 	fact.purchase_amt_g,
361 	fact.pisp_quantity,
362 	fact.pisp_amt_g,
363 	fact.pisp_amt_sg,
364 	fact.quantity
365  from poa_pqc_bs_j2_mv fact
366       ' || l_in_join_tables || '
367       where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
368          and &BIS_CURRENT_ASOF_DATE
369          and fact.consigned_code <> 1
370 	 and fact.order_type = ''QUANTITY''
371       and fact.complex_work_flag = ''N''
372       ' || l_where_clause  ||') f
373       where    f.ent_year_id = cisp.ent_year_id
374       and   f.supplier_id = cisp.supplier_id
375       and   f.po_item_id = cisp.po_item_id
376       and   f.base_uom = cisp.base_uom
377       group by f.po_header_id, f.po_line_id, f.po_item_id, f.base_uom, f.po_release_id, f.org_id, f.supplier_id, f.global_cur_conv_rate,
378           nvl(f.pisp_amt_' ||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.pisp_quantity, cisp.purchase_amt_' ||  (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/cisp.quantity),
379 		f.purchase_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.quantity,
380 	f.purchase_amt_b/f.quantity
381 )'
382 || ' where ' || l_filter_where ||
383 '     ) i,
384       po_headers_all poh,
385       po_lines_all pol,
386       po_releases_all rel,
387       poa_items_v item,
388       poa_suppliers_v supplier,
389       mtl_units_of_measure_vl uom,
390       hr_all_organization_units_vl poorg
391   where i.po_header_id = poh.po_header_id
392     and i.po_line_id = pol.po_line_id
393   and i.po_item_id = item.id
394   and i.base_uom = uom.unit_of_measure
395   and i.org_id = poorg.organization_id
396   and i.supplier_id = supplier.id
397   and i.po_release_id = rel.po_release_id (+)
398   and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
399 ORDER BY rnk';
400 
401   x_custom_sql := l_query;
402 
403   poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
404 
405 end;
406 
407 END POA_DBI_PC_PKG;