[Home] [Help]
PACKAGE BODY: APPS.GMF_CBOM_REP_PKG
Source
1 PACKAGE BODY gmf_cbom_rep_pkg AS
2 /* $Header: GMFIBOMB.pls 120.4 2007/12/17 10:25:02 pmarada noship $ */
3
4 /*
5 REM+=========================================================================+
6 REM| PROCEDURE NAME |
7 REM| Get_Quantity |
8 REM| |
9 REM| TYPE |
10 REM| Private |
11 REM| |
12 REM| DESCRIPTION |
13 REM| This function returns the formula product quantity and ingradient |
14 REM| quantity in item's primary UOM |
15 REM| HISTORY |
16 REM| Created 01-NOV-2007 Rajesh Patangya (OPM Development) |
17 REM| 07-NOV-2007 WilliamJohn Harris
18 REM| 14-Dec-2007 WilliamJohn Harris -- added text to error returns
19 REM| |
20 REM+=========================================================================+
21 */
22
23 FUNCTION Get_Quantity (
24 p_invy_item_id IN NUMBER,
25 p_prod_invy_item_id IN NUMBER,
26 p_prod_ingr_ind IN VARCHAR2, -- values 'P', 'I', or 'B'
27 p_organization_id IN NUMBER ,
28 p_cost_type_id IN NUMBER ,
29 p_period_id IN NUMBER
30 ) RETURN VARCHAR2 IS
31
32 /* Local variables */
33 l_ret_val VARCHAR2(200) := ' ' ;
34 l_quantity NUMBER := 0 ;
35 l_qty_conv NUMBER := 0 ;
36 l_uom VARCHAR2(3) := ' ' ;
37 item_primary_uom VARCHAR2(3) := ' ' ;
38
39
40 BEGIN
41
42 -- FUNCTION Get_Quantity () -- gets quantity and Uom
43 -- 02-Nov-07 : if item-uom different from formula uom, scale the
44 -- item quantity to item-uom according to the uom conversion
45
46 SELECT primary_uom_code into item_primary_uom
47 FROM mtl_system_items_b
48 WHERE inventory_item_id = p_invy_item_id
49 AND organization_id = p_organization_id ;
50
51 IF (p_prod_ingr_ind = 'P') THEN
52 --SELECT std_qty, form_prod_uom into l_quantity, l_uom
53 SELECT product_qty, form_prod_uom into l_quantity, l_uom
54 FROM cm_scst_led
55 WHERE std_qty > 0
56 AND rownum < 2
57 AND cmpntcost_id in (SELECT cmpntcost_id
58 FROM cm_cmpt_dtl
59 WHERE inventory_item_id = p_prod_invy_item_id
60 AND period_id = p_period_id
61 AND cost_type_id = p_cost_type_id
62 AND organization_id = p_organization_id
63 ) ;
64
65 ELSIF ((p_prod_ingr_ind = 'I') OR (p_prod_ingr_ind = 'B')) THEN
66 SELECT item_fmqty, item_fmqty_uom INTO l_quantity, l_uom
67 FROM cm_scst_led
68 WHERE inventory_item_id = p_invy_item_id
69 AND rownum < 2
70 AND (line_type = -1 OR line_type = 2)
71 AND cmpntcost_id in (select cmpntcost_id from cm_cmpt_dtl
72 WHERE inventory_item_id = p_prod_invy_item_id
73 AND period_id = p_period_id
74 AND cost_type_id = p_cost_type_id
75 AND organization_id = p_organization_id
76 ) ;
77 ELSE
78 --RETURN -1 ;
79 l_ret_val := 'Error -1' ;
80 RETURN l_ret_val ;
81 END IF ;
82
83 IF (item_primary_uom <> l_uom) THEN
84 -- convert l_quantity to primary uom
85 l_qty_conv := inv_convert.inv_um_convert(p_invy_item_id, NULL, p_organization_id, 5, l_quantity, l_uom, item_primary_uom, NULL, NULL) ;
86 IF (l_qty_conv < 0) THEN
87 RETURN l_qty_conv ;
88 ELSE
89 l_quantity := l_qty_conv ;
90 END IF ;
91 END IF ;
92
93 l_ret_val := l_quantity || ' / ' || item_primary_uom ;
94 RETURN l_ret_val ;
95
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 --RETURN -2 ;
99 l_ret_val := 'Error -2' ;
100 RETURN l_ret_val ;
101 WHEN OTHERS THEN
102 --RETURN -3 ;
103 l_ret_val := 'Error -3' ;
104 RETURN l_ret_val ;
105 END Get_Quantity ;
106
107 /*
108 REM+=========================================================================+
109 REM| PROCEDURE NAME |
110 REM| get_le_name |
111 REM| |
112 REM| TYPE |
113 REM| Private |
114 REM| |
115 REM| DESCRIPTION |
116 REM| This function returns the Legal entity Name |
117 REM| HISTORY |
118 REM| Created 01-NOV-2007 Prasad Marada (OPM Development) |
119 REM| |
120 REM+=========================================================================+
121 */
122
123 FUNCTION get_le_name(p_le_id IN NUMBER) RETURN VARCHAR2 IS
124
125 l_le_name gmf_legal_entities.legal_entity_name%TYPE :='';
126
127 BEGIN
128 -- get Legal entity name
129 SELECT legal_entity_name INTO l_le_name FROM gmf_legal_entities
130 WHERE legal_entity_id = p_le_id;
131
132 RETURN l_le_name;
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 RETURN l_le_name;
137 WHEN OTHERS THEN
138 RETURN l_le_name;
139
140 END get_le_name;
141
142 /*
143 REM+=========================================================================+
144 REM| PROCEDURE NAME |
145 REM| get_currency |
146 REM| |
147 REM| TYPE |
148 REM| Private |
149 REM| |
150 REM| DESCRIPTION |
151 REM| This function returns the currency used by legal enity |
152 REM| HISTORY |
153 REM| Created 01-NOV-2007 Rajesh Patangya |
154 REM| |
155 REM+=========================================================================+
156 */
157 FUNCTION get_currency(p_le_id IN NUMBER) RETURN VARCHAR2 IS
158
159 l_currency gmf_legal_entities.base_currency_code%TYPE :='';
160
161 BEGIN
162 SELECT base_currency_code INTO l_currency FROM gmf_legal_entities
163 WHERE legal_entity_id = p_le_id;
164
165 RETURN l_currency;
166
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 RETURN l_currency;
170 WHEN OTHERS THEN
171 RETURN l_currency;
172
173 END get_currency;
174
175 /*
176 REM+=========================================================================+
177 REM| PROCEDURE NAME |
178 REM| get_cost_type |
179 REM| |
180 REM| TYPE |
181 REM| Private |
182 REM| |
183 REM| DESCRIPTION |
184 REM| This function returns the cost type based on cost_type_id |
185 REM| HISTORY |
186 REM| Created 01-NOV-2007 Rajesh Patangya |
187 REM| |
188 REM+=========================================================================+
189 */
190 FUNCTION get_cost_type(p_ct_id IN NUMBER) RETURN VARCHAR2 IS
191
192 l_cost_type cm_mthd_mst.cost_mthd_code%TYPE :='';
193
194 BEGIN
195 -- get Cost type name,
196 SELECT cost_mthd_code INTO l_cost_type FROM cm_mthd_mst
197 WHERE cost_type_id = p_ct_id;
198
199 RETURN l_cost_type;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 RETURN l_cost_type;
204 WHEN OTHERS THEN
205 RETURN l_cost_type;
206
207 END get_cost_type;
208
209 /*
210 REM+=========================================================================+
211 REM| PROCEDURE NAME |
212 REM| get_OrderBy |
213 REM| |
214 REM| TYPE |
215 REM| Private |
216 REM| |
217 REM| DESCRIPTION |
218 REM| This function returns the Order By cluase used by Query |
219 REM| HISTORY |
220 REM| Created 01-NOV-2007 Rajesh Patangya |
221 REM| |
222 REM+=========================================================================+
223 */
224 FUNCTION get_OrderBy (p_sort_by IN NUMBER) RETURN VARCHAR2 IS
225
226 l_org_where VARCHAR2(1000) := '';
227 BEGIN
228 -- Build the order by clause
229 IF NVL(p_sort_by,1) = 1 THEN
230 l_org_where := 'ORDER BY mp.organization_code, msi.CONCATENATED_SEGMENTS ' ;
231 ELSE
232 l_org_where := 'ORDER BY msi.CONCATENATED_SEGMENTS, mp.organization_code ' ;
233 END IF;
234 RETURN l_org_where;
235
236 END get_OrderBy;
237
238 /*
239 REM+=========================================================================+
240 REM| PROCEDURE NAME |
241 REM| BeforeReportTrigger |
242 REM| |
243 REM| TYPE |
244 REM| Private |
245 REM| |
246 REM| DESCRIPTION |
247 REM| This function returns the Where cluase to support Queries |
248 REM| HISTORY |
249 REM| Created 01-NOV-2007 Rajesh Patangya |
250 REM| |
251 REM+=========================================================================+
252 */
253 FUNCTION BeforeReportTrigger RETURN BOOLEAN IS
254 l_where_clause VARCHAR2(2000);
255 BEGIN
256 l_where_clause := '1=1';
257 p_where_clause := l_where_clause;
258 RETURN TRUE;
259 END BeforeReportTrigger ;
260
261 /*
262 REM+=========================================================================+
263 REM| PROCEDURE NAME |
264 REM| get_Period_Code |
265 REM| |
266 REM| TYPE |
267 REM| Private |
268 REM| |
269 REM| DESCRIPTION |
270 REM| This function returns the Period code based on period_id |
271 REM| HISTORY |
272 REM| Created 01-NOV-2007 Rajesh Patangya |
273 REM| 13-Dec-07 pmarada bug 6676681 changed the funtion to return period id |
274 REM| |
275 REM+=========================================================================+
276 */
277 FUNCTION get_Period_Id(p_legal_entity_id IN NUMBER, p_calendar_code IN VARCHAR2,
278 p_period_code IN VARCHAR2, p_cost_type_id IN NUMBER) RETURN NUMBER IS
279
280 l_period_id gmf_period_statuses.period_id%TYPE := -1;
281 BEGIN
282 SELECT period_id INTO l_period_id FROM gmf_period_statuses
283 WHERE legal_entity_id = p_legal_entity_id
284 AND calendar_code = p_calendar_code
285 AND period_code = p_period_code
286 AND cost_type_id = p_cost_type_id;
287 RETURN l_period_id ;
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 RETURN l_period_id ;
291 WHEN OTHERS THEN
292 RETURN l_period_id ;
293
294 END get_Period_Id;
295
296 /*
297 REM+=========================================================================+
298 REM| PROCEDURE NAME |
299 REM| get_item_name |
300 REM| |
301 REM| TYPE |
302 REM| Private |
303 REM| |
304 REM| DESCRIPTION |
305 REM| This function returns the item name |
306 REM| HISTORY |
307 REM| Created 01-NOV-2007 Rajesh Patangya |
308 REM| |
309 REM+=========================================================================+
310 */
311 FUNCTION get_item_name(v_item_id IN NUMBER) RETURN VARCHAR2 IS
312 l_item mtl_system_items_b_kfv.CONCATENATED_SEGMENTS%TYPE := '';
313 BEGIN
314 SELECT unique CONCATENATED_SEGMENTS INTO l_item
315 FROM mtl_system_items_b_kfv where inventory_item_id = v_item_id;
316 RETURN l_item ;
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 RETURN l_item ;
320 WHEN OTHERS THEN
321 RETURN l_item ;
322
323 END get_item_name;
324
325 /*
326 REM+=========================================================================+
330 REM| TYPE |
327 REM| PROCEDURE NAME |
328 REM| get_category_name |
329 REM| |
331 REM| Private |
332 REM| |
333 REM| DESCRIPTION |
334 REM| This function returns the category name |
335 REM| HISTORY |
336 REM| Created 01-NOV-2007 Rajesh Patangya |
337 REM| |
338 REM+=========================================================================+
339 */
340 FUNCTION get_category_name(v_cat_id IN NUMBER) RETURN VARCHAR2 IS
341
342 l_category mtl_categories_kfv.CONCATENATED_SEGMENTS%TYPE := '';
343
344 BEGIN
345
346 SELECT mc.concatenated_segments INTO l_category
347 FROM mtl_default_category_sets mdc,
348 mtl_category_sets mcs,
349 mtl_categories_kfv mc
350 WHERE mdc.functional_area_id = 19
351 AND mdc.category_set_id = mcs.category_set_id
352 AND mcs.structure_id = mc.structure_id
353 AND mc.category_id = v_cat_id ;
354
355 RETURN l_category ;
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 RETURN l_category ;
359 WHEN OTHERS THEN
360 RETURN l_category ;
361
362 END get_category_name;
363
364 /*
365 REM+=========================================================================+
366 REM| PROCEDURE NAME |
367 REM| get_OrderBy |
368 REM| |
369 REM| TYPE |
370 REM| Private |
371 REM| |
372 REM| DESCRIPTION |
373 REM| This function returns the Order By cluase used by Query |
374 REM| HISTORY |
375 REM| Created 01-NOV-2007 Rajesh Patangya |
376 REM| |
377 REM+=========================================================================+
378 */
379 FUNCTION get_Category (p_from_cat IN VARCHAR2,p_to_cat IN VARCHAR2) RETURN VARCHAR2 IS
380
381 l_cat_where VARCHAR2(5000) := '';
382 BEGIN
383 -- Build the order by clause
384 IF p_from_cat IS NOT NULL OR p_to_cat IS NOT NULL THEN
385 l_cat_where := ' AND EXISTS (SELECT mc.concatenated_segments ' ;
386 l_cat_where := l_cat_where || ' FROM mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories mic, ' ;
387 l_cat_where := l_cat_where || ' mtl_categories_kfv mc WHERE mdc.functional_area_id = 19 ' ;
388 l_cat_where := l_cat_where || ' AND mdc.category_set_id = mcs.category_set_id AND mcs.category_set_id = mic.category_set_id ' ;
389 l_cat_where := l_cat_where || ' AND mcs.structure_id = mc.structure_id AND mic.inventory_item_id = ccd.inventory_item_id ' ;
390 l_cat_where := l_cat_where || ' AND mic.organization_id = ccd.organization_id AND mic.category_id = mc.category_id ' ;
391 l_cat_where := l_cat_where || ' AND mc.concatenated_segments >= NVL(:P_FROM_COST_CATEGORY,mc.concatenated_segments) ' ;
392 l_cat_where := l_cat_where || ' AND mc.concatenated_segments <= NVL(:P_TO_COST_CATEGORY, mc.concatenated_segments) )' ;
393 ELSE
394 l_cat_where := ' AND 1=1';
395 END IF;
396 RETURN l_cat_where;
397 EXCEPTION
398 WHEN NO_DATA_FOUND THEN
399 RETURN l_cat_where ;
400 WHEN OTHERS THEN
401 RETURN l_cat_where ;
402 END get_Category;
403
404 END gmf_cbom_rep_pkg ;