[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PCO_PKG
Source
1 PACKAGE BODY ENI_DBI_PCO_PKG AS
2 /*$Header: ENIPCOPB.pls 120.3 2006/03/19 22:58:37 sdebroy noship $*/
3 PROCEDURE GET_SQL
4 (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
6 , x_custom_sql OUT NOCOPY VARCHAR2
7 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
8 ) IS
9 l_err varchar2(3000);
10 l_custom_rec BIS_QUERY_ATTRIBUTES;
11 l_err_msg VARCHAR2(500);
12 l_period_type VARCHAR2(40);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(200);
15 l_as_of_date DATE;
16 l_prev_as_of_date DATE;
17 l_report_start DATE;
18 l_cur_period NUMBER;
19 l_days_into_period NUMBER;
20 l_comp_type VARCHAR2(100);
21 l_category VARCHAR2(100);
22 l_item VARCHAR2(100);
23 l_org VARCHAR2(100);
24 l_id_column VARCHAR2(30);
25 l_order_by VARCHAR2(100);
26 l_drill VARCHAR2(30);
27 l_status VARCHAR2(30);
28 l_priority VARCHAR2(30);
29 l_reason VARCHAR2(30);
30 l_lifecycle_phase VARCHAR2(30);
31 l_currency VARCHAR2(30);
32 l_bom_type VARCHAR2(30);
33 l_type VARCHAR2(30);
34 l_manager VARCHAR2(30);
35 l_comp_where VARCHAR2(1000);
36 l_temp VARCHAR2(1000);
37 l_lob VARCHAR2(1000);
38 l_for_cat VARCHAR2(1000);
39 l_where_clause VARCHAR2(1000);
40 l_group_by_clause VARCHAR2(500);
41 l_concat_var VARCHAR2(1000);
42 l_item_1 NUMBER;
43 l_org_1 NUMBER;
44 l_drill_down_part_count VARCHAR2(2000);
45 l_component_cat_cond VARCHAR2(100);
46 l_cat_cond VARCHAR2(100);
47 l_org_exists NUMBER;
48 l_org_temp NUMBER;
49
50 BEGIN
51
52 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
53 , l_period_type
54 , l_period_bitand
55 , l_view_by
56 , l_as_of_date
57 , l_prev_as_of_date
58 , l_report_start
59 , l_cur_period
60 , l_days_into_period
61 , l_comp_type
62 , l_category
63 , l_item
64 , l_org
65 , l_id_column
66 , l_order_by
67 , l_drill
68 , l_status
69 , l_priority
70 , l_reason
71 , l_lifecycle_phase
72 , l_currency
73 , l_bom_type
74 , l_type
75 , l_manager
76 , l_lob
77 );
78 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
79 x_custom_output := bis_query_attributes_tbl();
80 l_org_temp := l_org;
81
82 Begin
83 select
84 NVL(common_assembly_item_id,assembly_item_id),
85 NVL(common_organization_id,organization_id)
86 INTO
87 l_item,l_org
88 from
89 bom_bill_of_materials
90 where
91 assembly_item_id = l_item and
92 organization_id = l_org and
93 alternate_bom_designator IS NULL;
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 l_item := null; l_org := null;
97 end;
98
99 IF (l_item IS NOT NULL) THEN
100 -- Display the data only when the organization of the item exists
101 -- in the org_temp table. Added for bug # 3669751
102
103 SELECT count(*)
104 INTO l_org_exists
105 FROM eni_dbi_part_count_org_temp
106 WHERE organization_id = l_org_temp;
107
108 IF (l_org_exists = 0) THEN
109 l_item := NULL;
110 l_org := NULL;
111 END IF;
112 END IF;
113
114 IF (l_item IS NULL) THEN -- When no Item is selected, a single row of N/A is displayed
115 x_custom_sql := ' SELECT NULL AS VIEWBY,
116 NULL AS ENI_MEASURE1,
117 NULL AS ENI_MEASURE2,
118 NULL AS ENI_MEASURE3,
119 NULL AS ENI_MEASURE4,
120 NULL AS ENI_MEASURE5,
121 NULL AS ENI_MEASURE6,
122 NULL AS ENI_MEASURE7,
123 NULL AS ENI_MEASURE9
124 FROM DUAL ';
125 RETURN;
126 END IF;
127
128 l_drill_down_part_count := 'pFunctionName=ENI_DBI_CDE_R&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
129 l_component_cat_cond := '&ITEM+ENI_ITEM_ITM_CAT+ENI_ITEM_ITM_CAT=';
130 l_cat_cond := '&ITEM+ENI_ITEM_ITM_CAT+ENI_ITEM_VBH_CAT=' || l_category;
131
132 l_where_clause := 'pco.organization_id = :ORGANIZATION_ID '|| --Bug#5083708 || l_org ||
133 ' AND pco.assembly_item_id = :ASSEMBLY_ITEM_ID' ; --Bug#5083708 || l_item;
134
135 l_where_clause := l_where_clause ||
136 ' AND eiv.organization_id = :ORGANIZATION_ID '|| --Bug#5083708 || l_org ||
137 ' AND eiv.inventory_item_id = pco.component_item_id';
138
139 x_custom_sql :=
140 ' SELECT
141 eic.VALUE as VIEWBY,
142 eic.ID as VIEWBYID,
143 b.current_pco as ENI_MEASURE1,
144 b.prior_pco as ENI_MEASURE2,
145 b.change_percent as ENI_MEASURE3,
146 b.percent_of_total as ENI_MEASURE4,
147 sum(b.current_pco) over() as ENI_MEASURE5,
148 sum(b.change_percent) over() AS ENI_MEASURE6,
149 sum(b.percent_of_total) over() as ENI_MEASURE7,
150 :PCO_LIST_DRILL || :PCO_CAT_DRILL || :PCO_COMP_DRILL || TO_CHAR(eic.ID) AS ENI_MEASURE9
151 FROM (SELECT
152 a.ITEM_CATALOG_GROUP_ID,
153 a.current_pco ,
154 a.prior_pco ,
155 round(DECODE(a.prior_pco,0 , NULL, (((a.current_pco - a.prior_pco)/a.prior_pco)*100)),2)
156 AS change_percent,
157 sum(a.current_pco) over() as grand_current,
158 round(DECODE((sum(a.current_pco) over()),0 , NULL, ((a.current_pco/(sum(a.current_pco) over()))*100)),2)
159 AS percent_of_total
160 FROM
161 (SELECT NVL(eiv.ITEM_CATALOG_GROUP_ID,-1) as item_catalog_group_id,
162 SUM(CASE WHEN ('||'&'|| 'BIS_CURRENT_ASOF_DATE -- Condition modified to fix the Bug 3151377
163 BETWEEN pco.effectivity_date AND pco.disable_date)
164 THEN 1 ELSE 0 END) AS current_pco,
165 SUM(CASE WHEN ('||'&'|| 'BIS_PREVIOUS_ASOF_DATE -- Condition modified to fix the Bug 3151377
166 BETWEEN pco.effectivity_date AND pco.disable_date)
167 THEN 1 ELSE 0 END) AS prior_pco
168 FROM ENI_DBI_PART_COUNT_F pco, ENI_ITEM_ORG_V eiv WHERE
169 '||l_where_clause||'
170 GROUP BY NVL(eiv.item_catalog_group_id,-1)) a) b, ENI_ITEM_ITM_CAT_V eic
171 WHERE b.ITEM_CATALOG_GROUP_ID = eic.ID and
172 (NOT(b.current_pco = 0)) and -- Condition added to to fix the Bug 3131408
173 eic.ID = NVL(TO_NUMBER(eic.NODE),-1)
174 GROUP BY VALUE, ID, current_pco, prior_pco, change_percent, percent_of_total
175 ORDER BY
176 ' ||l_order_by;
177
178 x_custom_output.extend;
179 l_custom_rec.attribute_name := ':PCO_LIST_DRILL';
180 l_custom_rec.attribute_value := l_drill_down_part_count;
181 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
182 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
183 x_custom_output(1) := l_custom_rec;
184
185 x_custom_output.extend;
186 l_custom_rec.attribute_name := ':PCO_COMP_DRILL';
187 l_custom_rec.attribute_value := l_component_cat_cond;
188 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
189 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
190 x_custom_output(2) := l_custom_rec;
191
192 x_custom_output.extend;
193 l_custom_rec.attribute_name := ':PCO_CAT_DRILL';
194 l_custom_rec.attribute_value := l_cat_cond;
195 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
196 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
197 x_custom_output(3) := l_custom_rec;
198
199 --Bug#5083708
200 x_custom_output.extend;
201 l_custom_rec.attribute_name := ':ORGANIZATION_ID';
202 l_custom_rec.attribute_value := replace(l_org,'''');
203 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
204 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
205 x_custom_output(4) := l_custom_rec;
206
207 x_custom_output.extend;
208 l_custom_rec.attribute_name := ':ASSEMBLY_ITEM_ID';
209 l_custom_rec.attribute_value := replace(l_item,'''');
210 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
211 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
212 x_custom_output(5) := l_custom_rec;
213 --Bug#5083708
214
215 END GET_SQL;
216 END ENI_DBI_PCO_PKG;