[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_CDE_PKG
Source
1 PACKAGE BODY ENI_DBI_CDE_PKG AS
2 /*$Header: ENICDEPB.pls 120.2 2006/02/14 04:26:25 lparihar noship $*/
3
4 PROCEDURE get_sql
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 , x_custom_sql OUT NOCOPY VARCHAR2
8 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10 l_err varchar2(3000);
11 l_custom_rec BIS_QUERY_ATTRIBUTES;
12 l_err_msg VARCHAR2(500);
13 l_period_type VARCHAR2(40);
14 l_period_bitand NUMBER;
15 l_view_by VARCHAR2(200);
16 l_as_of_date DATE;
17 l_prev_as_of_date DATE;
18 l_report_start DATE;
19 l_cur_period NUMBER;
20 l_days_into_period NUMBER;
21 l_comp_type VARCHAR2(100);
22 l_category VARCHAR2(100);
23 l_item VARCHAR2(100);
24 l_org VARCHAR2(100);
25 l_id_column VARCHAR2(30);
26 l_order_by VARCHAR2(100);
27 l_drill VARCHAR2(30);
28 l_status VARCHAR2(30);
29 l_priority VARCHAR2(30);
30 l_reason VARCHAR2(30);
31 l_lifecycle_phase VARCHAR2(30);
32 l_currency VARCHAR2(30);
33 l_bom_type VARCHAR2(30);
34 l_type VARCHAR2(30);
35 l_manager VARCHAR2(30);
36 l_comp_where VARCHAR2(1000);
37 l_org_where VARCHAR2(1000);
38 l_cat_where VARCHAR2(1000);
39 l_item_where VARCHAR2(1000);
40 l_temp VARCHAR2(1000);
41 l_lob VARCHAR2(1000);
42 l_for_cat VARCHAR2(1000);
43 l_from_clause VARCHAR2(1000);
44 l_where_clause VARCHAR2(1000);
45 l_group_by_clause VARCHAR2(500);
46 l_concat_var varchar2(1000);
47 l_item_org VARCHAR2(50);
48 l_item_temp NUMBER;
49 l_org_master NUMBER;
50 l_drill_to_item_page VARCHAR2(1000);
51 l_component_category VARCHAR2(200);
52 l_unassigned_desc VARCHAR2(240); -- FND_LOOKUP_VALUES.DESCRIPTION IS VARCHAR2(240)
53
54 BEGIN
55
56 ENI_DBI_UTIL_PKG.get_parameters( p_page_parameter_tbl
57 , l_period_type
58 , l_period_bitand
59 , l_view_by
60 , l_as_of_date
61 , l_prev_as_of_date
62 , l_report_start
63 , l_cur_period
64 , l_days_into_period
65 , l_comp_type
66 , l_category
67 , l_item_temp
68 , l_org_master
69 , l_id_column
70 , l_order_by
71 , l_drill
72 , l_status
73 , l_priority
74 , l_reason
75 , l_lifecycle_phase
76 , l_currency
77 , l_bom_type
78 , l_type
79 , l_manager
80 , l_lob
81 );
82
83 IF (p_page_parameter_tbl.count > 0) THEN
84 FOR i IN p_page_parameter_tbl.first .. p_page_parameter_tbl.last LOOP
85 IF ((p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_ITM_CAT+ENI_ITEM_ITM_CAT') OR
86 (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT+ENI_ITEM_ITM_CAT')) THEN
87 l_component_category := p_page_parameter_tbl(i).parameter_id;
88 ELSIF ((p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT+ENI_ITEM_ITM_CAT')
89 OR (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT+ENI_ITEM_ITM_CAT'))
90 THEN
91 l_category := p_page_parameter_tbl(i).parameter_value;
92 END IF;
93 END LOOP;
94 END IF;
95
96 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
97 x_custom_output := bis_query_attributes_tbl();
98
99 BEGIN
100 select
101 NVL(common_assembly_item_id,assembly_item_id),
102 NVL(common_organization_id ,organization_id)
103 INTO
104 l_item,l_org
105 from
106 bom_bill_of_materials
107 where
108 assembly_item_id = l_item_temp and
109 organization_id = l_org_master and
110 alternate_bom_designator IS NULL;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 l_item := NULL; l_org := NULL;
114 END;
115
116 IF l_item IS NULL
117 THEN
118 x_custom_sql :=' SELECT NULL AS VIEWBY,
119 1 AS ENI_MEASURE1,
120 2 AS ENI_MEASURE2,
121 3 AS ENI_MEASURE2,
122 4 AS ENI_MEASURE4,
123 6 AS ENI_MEASURE5 FROM DUAL where 1 = 2';
124 RETURN;
125 END IF;
126
127 SELECT DESCRIPTION
128 INTO
129 l_unassigned_desc
130 FROM
131 FND_LOOKUP_VALUES
132 WHERE
133 LOOKUP_TYPE = 'ITEM' AND LOOKUP_CODE = '-1'
134 AND LANGUAGE = USERENV( 'LANG' );
135
136 l_item_where := ' AND pco.assembly_item_id = :ITEM';
137 l_item_where := l_item_where || ' AND pco.organization_id = :ORG';
138 l_item_where := l_item_where || ' AND star.inventory_item_id = pco.component_item_id
139 AND star.organization_id = :ORG_MASTER'; --Modified to fix Bug# 3493983
140 l_cat_where := ' AND NVL( star.item_catalog_group_id, -1 ) = :COMPONENT_CATEGORY';
141
142 l_drill_to_item_page := ' ''pFunctionName=EGO_ITEM_OVERVIEW&inventoryItemId='' || TO_CHAR(item_id)' ;
143 l_drill_to_item_page := l_drill_to_item_page || ' || ''&organizationId='' || TO_CHAR(org_id)';
144 l_drill_to_item_page := l_drill_to_item_page || ' || ''&revisionCode='' || TO_CHAR(revision)';
145
146 x_custom_sql := '
147 SELECT
148 item_name AS ENI_MEASURE1
149 , DECODE(
150 item_id
151 , -1, :UNASSIGNED_DESC
152 , MTI_TL.DESCRIPTION )
153 AS ENI_MEASURE2
154 , revision AS ENI_MEASURE3
155 , status_code AS ENI_MEASURE4
156 , ' || l_drill_to_item_page || '
157 AS ENI_MEASURE5
158 FROM
159 ( SELECT
160 ( rank() over ( &ORDER_BY_CLAUSE nulls last, pco.rowid ) - 1 ) AS rnk,
161 star.value AS item_name,
162 pco.component_item_id AS item_id,
163 pco.organization_id AS org_id,
164 star.organization_id AS base_org,
165 NVL((select mir.revision from MTL_ITEM_REVISIONS_B mir
166 where mir.inventory_item_id = pco.component_item_id
167 and mir.organization_id = :ORG_MASTER
168 and mir.effectivity_date =
169 (select max(mir1.effectivity_date) from MTL_ITEM_REVISIONS_B mir1
170 where mir1.inventory_item_id = pco.component_item_id
171 and mir1.organization_id = :ORG_MASTER
172 and mir1.implementation_date IS NOT NULL
173 and trunc(mir1.effectivity_date) <= &BIS_CURRENT_ASOF_DATE )), -1) revision,
174 (select msi.inventory_item_status_code from MTL_SYSTEM_ITEMS_B msi
175 where inventory_item_id = pco.component_item_id
176 and organization_id = :ORG_MASTER ) status_code
177 FROM
178 ENI_DBI_PART_COUNT_F pco
179 , ( select value as ENI_MEASURE1, value, organization_id, inventory_item_id, item_catalog_group_id
180 FROM ENI_OLTP_ITEM_STAR ) star
181 WHERE
182 (&BIS_CURRENT_ASOF_DATE BETWEEN
183 pco.effectivity_date AND pco.disable_date)
184 ' || l_item_where || l_cat_where || '
185 ) pcofact
186 , MTL_SYSTEM_ITEMS_TL MTI_TL
187 WHERE
188 ( ( pcofact.rnk between &START_INDEX and &END_INDEX ) OR (&END_INDEX = -1) )
189 and pcofact.base_org = MTI_TL.ORGANIZATION_ID(+)
190 and pcofact.item_id = MTI_TL.INVENTORY_ITEM_ID(+)
191 and MTI_TL.LANGUAGE(+) = USERENV( ''LANG'' )
192 &ORDER_BY_CLAUSE NULLS LAST' ;
193 -- Dirty PMV HACK: when DisplayRows = 25 and displaying rows 26-50, for example,
194 -- sets START_INDEX = 25, END_INDEX = 51, so the above code returns 27 rows; 25 for display
195 -- and 2 to ensure PMV displays 'Next' and 'Previous' navigators
196
197 x_custom_output.extend;
198 l_custom_rec.attribute_name := ':UNASSIGNED_DESC';
199 l_custom_rec.attribute_value := l_unassigned_desc;
200 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
201 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
202 x_custom_output(1) := l_custom_rec;
203
204 x_custom_output.extend;
205 l_custom_rec.attribute_name := ':ORG_MASTER';
206 l_custom_rec.attribute_value := l_org_master;
207 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
208 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
209 x_custom_output(2) := l_custom_rec;
210
211 x_custom_output.extend;
212 l_custom_rec.attribute_name := ':ORG';
213 l_custom_rec.attribute_value := l_org;
214 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
215 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
216 x_custom_output(3) := l_custom_rec;
217
218 x_custom_output.extend;
219 l_custom_rec.attribute_name := ':ITEM';
220 l_custom_rec.attribute_value := l_item;
221 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
222 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
223 x_custom_output(4) := l_custom_rec;
224
225 x_custom_output.extend;
226 l_custom_rec.attribute_name := ':COMPONENT_CATEGORY';
227 l_custom_rec.attribute_value := l_component_category;
228 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
229 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
230 x_custom_output(5) := l_custom_rec;
231
232 END GET_SQL;
233
234 END ENI_DBI_CDE_PKG;