1 PACKAGE BODY GMF_UTILITIES_GRP AS
2 /* $Header: gmfputlb.pls 120.2 2005/10/27 09:51:21 pmarada noship $ */
3
4 --****************************************************************************************************
5 --* *
6 --* Oracle Process Manufacturing *
7 --* ============================ *
8 --* *
9 --* Package GMF_UTILITIES_GRP *
10 --* --------------------------- *
11 --* This package contains the common utility functions *
12 --* For individual procedures' descriptions, see the *
13 --* description in front of each one. *
14 --* *
15 --* *
16 --* HISTORY *
17 --* ======= *
18 --* 8-Sep -2005 Jahnavi Boppana created *
19 --* 01-Oct-2005 Prasad Marada Added get organization, get item methods *
20 --* *
21 --****************************************************************************************************
22
23 /*=========================================================
24 FUNCTION : GET_ACCOUNT_DESC
25
26 DESCRIPTION
27 This function will return the account description for the
28 account_id and legal_entity_id passed
29 AUTHOR : Jahnavi Boppana INVCONV August 2005
30 ==========================================================*/
31
32
33 FUNCTION GET_ACCOUNT_DESC(P_ACCOUNT IN VARCHAR2,
34 P_LEGAL_ENTITY_ID IN NUMBER,
35 P_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
36 ACCOUNT_DESC VARCHAR2(2000);
37 ACCOUNT_KEY VARCHAR2(1000);
38 BEGIN
39 IF P_FLAG = 'I' THEN
40 ACCOUNT_KEY := GET_ACCOUNT_CODE(TO_NUMBER(p_account), p_legal_entity_id);
41 ELSIF P_FLAG = 'C' THEN
42 account_key := P_ACCOUNT;
43 END IF;
44 ACCOUNT_DESC := GET_ACCOUNT_DESC(P_ACCOUNT_KEY => account_key, p_legal_entity_id => p_legal_entity_id);
45 RETURN (ACCOUNT_DESC);
46 END GET_ACCOUNT_DESC;
47
48
49 /*=========================================================
50 FUNCTION : GET_ACCOUNT_DESC
51
52 DESCRIPTION
53 This function will return the account description for the
54 account_id and legal_entity_id passed
55 AUTHOR : Jahnavi Boppana INVCONV August 2005
56 ==========================================================*/
57
58
59 FUNCTION GET_ACCOUNT_DESC(P_ACCOUNT_ID IN NUMBER, P_LEGAL_ENTITY_ID IN NUMBER) RETURN VARCHAR2 IS
60 ACCOUNT_DESC VARCHAR2(2000);
61 ACCOUNT_KEY VARCHAR2(1000);
62 BEGIN
63 ACCOUNT_KEY := GET_ACCOUNT_CODE(p_account_id, p_legal_entity_id);
64 ACCOUNT_DESC := GET_ACCOUNT_DESC(account_key, p_legal_entity_id);
65 RETURN (ACCOUNT_DESC);
66 END GET_ACCOUNT_DESC;
67
68 /*=========================================================
69 FUNCTION : GET_ACCOUNT_CODE
70
71 DESCRIPTION
72 This function will return the account code for the
73 account_id and legal_entity_id passed
74 AUTHOR : Jahnavi Boppana INVCONV August 2005
75 ==========================================================*/
76
77 FUNCTION GET_ACCOUNT_CODE(P_ACCOUNT_ID IN NUMBER, P_LEGAL_ENTITY_ID IN NUMBER) RETURN VARCHAR2 IS
78 account_code VARCHAR2(1000);
79 l_chart_of_accounts_id NUMBER(15);
80 l_delimiter varchar2(5);
81 l_sql_stmt VARCHAR2(2000);
82
83 TYPE t_seg_num IS TABLE OF FND_ID_FLEX_SEGMENTS.APPLICATION_COLUMN_NAME%TYPE INDEX BY BINARY_INTEGER;
84
85 CURSOR segments(chart_of_accounts_id NUMBER) IS SELECT
86 application_column_name
87 FROM
88 fnd_id_flex_segments f
89 WHERE
90 f.id_flex_num = chart_of_accounts_id and
91 f.application_id = 101 AND
92 f.id_flex_code = 'GL#' AND
93 f.enabled_flag = 'Y'
94 ORDER BY f.segment_num;
95 l_seg_num t_seg_num;
96
97 BEGIN
98 SELECT chart_of_accounts_id INTO l_chart_of_accounts_id FROM gmf_legal_entities
99 WHERE legal_entity_id = p_legal_entity_id;
100 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#', l_chart_of_accounts_id);
101 OPEN segments(l_chart_of_accounts_id);
102 FETCH segments bulk collect INTO l_seg_num;
103 CLOSE segments;
104
105 l_sql_stmt := 'SELECT ';
106 FOR i IN l_seg_num.first .. l_seg_num.last LOOP
107 IF i = l_seg_num.first THEN
108 l_sql_stmt := l_sql_stmt||l_seg_num(i);
109 ELSE
110 l_sql_stmt := l_sql_stmt||'||'''||l_delimiter||'''||'||l_seg_num(i);
111 END IF;
112 END LOOP;
113
114 l_sql_stmt := l_sql_stmt||' from gl_code_combinations where code_combination_id = '||
115 p_account_id||' and chart_of_accounts_id = '||l_chart_of_accounts_id;
116 dbms_output.put_line(l_sql_stmt);
117
118 EXECUTE IMMEDIATE l_sql_stmt INTO account_code;
119 RETURN(ACCOUNT_CODE);
120 END GET_ACCOUNT_CODE;
121
122
123 /*=========================================================
124 FUNCTION : GET_LEGAL_ENTITY
125
126 DESCRIPTION
127 This function will return the legal entity name for the
128 legal_entity_id passed
129 AUTHOR : Jahnavi Boppana INVCONV August 2005
130 ==========================================================*/
131
132 FUNCTION get_legal_entity(p_legal_entity_id IN NUMBER)
133 RETURN VARCHAR2 IS
134
135 CURSOR cp_legal_entity (cp_legal_entity_id gmf_legal_entities.legal_entity_id%TYPE) IS
136 SELECT legal_entity_name FROM gmf_legal_entities
137 WHERE legal_entity_id = cp_legal_entity_id;
138
139 l_legal_entity_name gmf_legal_entities.legal_entity_name%TYPE := NULL;
140
141 BEGIN
142 OPEN cp_legal_entity(p_legal_entity_id);
143 FETCH cp_legal_entity INTO l_legal_entity_name;
144 CLOSE cp_legal_entity;
145
146 RETURN(l_legal_entity_name);
147
148 END get_legal_entity;
149
150 /*=========================================================
151 FUNCTION : GET_ORGANIZATION_NAME
152
153 DESCRIPTION
154 This function will return the organization name for the
155 organization id passed
156 AUTHOR : Jahnavi Boppana INVCONV August 2005
157 ==========================================================*/
158
159
160 FUNCTION get_organization_name (p_organization_id IN NUMBER)
161 RETURN VARCHAR2 IS
162
163 CURSOR cur_organization (cp_organization_id hr_organization_units.organization_id%TYPE) IS
164 SELECT name FROM hr_organization_units
165 WHERE organization_id = cp_organization_id;
166
167 l_organization_name hr_organization_units.name%TYPE;
168 BEGIN
169 OPEN cur_organization(p_organization_id);
170 FETCH cur_organization INTO l_organization_name;
171 CLOSE cur_organization;
172
173 RETURN(l_organization_name);
174 END get_organization_name;
175
176
177 /*=========================================================
178 FUNCTION : GET_ORGANIZATION_CODE
179
180 DESCRIPTION
181 This function will return the organization code for the
182 organization id passed
183 AUTHOR : Jahnavi Boppana INVCONV August 2005
184 ==========================================================*/
185 FUNCTION get_organization_code(p_organization_id IN NUMBER)
186 RETURN VARCHAR2 IS
187 CURSOR cur_org_code (cp_org_id mtl_parameters.organization_id%TYPE) IS
188 SELECT organization_code FROM mtl_parameters
189 WHERE organization_id = cp_org_id;
190
191 l_organization_code mtl_parameters.organization_code%TYPE;
192 BEGIN
193
194 OPEN cur_org_code (p_organization_id);
195 FETCH cur_org_code INTO l_organization_code;
196 CLOSE cur_org_code;
197
198 RETURN(l_organization_code);
199 END get_organization_code;
200
201 /*=========================================================
202 FUNCTION : GET_ACCOUNT_DESC
203
204 DESCRIPTION
205 This function will return the account description for the
206 account code and legal entity id passed
207 AUTHOR : Jahnavi Boppana INVCONV August 2005
208 ==========================================================*/
209
210
211 FUNCTION GET_ACCOUNT_DESC(P_ACCOUNT_KEY IN VARCHAR2, P_LEGAL_ENTITY_ID IN NUMBER)RETURN VARCHAR2 IS
212 TYPE t_segments is table of varchar2(240) INDEX BY BINARY_INTEGER;
213 source_accounts t_segments;
214
215 x_description VARCHAR2(4000) default '';
216 l_account_desc VARCHAR2(2000) default '';
217 l_account_key VARCHAR2(1000) default '';
218 l_delimiter varchar2(5);
219 l_startdate DATE;
220 l_enddate DATE;
221 l_sobname VARCHAR2(100);
222 l_segmentname VARCHAR2(100);
223 l_segmentnum NUMBER;
224 l_segmentval VARCHAR2(100);
225 l_row_to_fetch NUMBER;
226 l_statuscode NUMBER;
227 l_segmentuom VARCHAR2(100);
228 l_start NUMBER DEFAULT 1;
229 l_end NUMBER DEFAULT 0;
230 l_deli_process NUMBER DEFAULT 0;
231 l_acct_no VARCHAR2(32767);
232 l_account_value VARCHAR2(32767);
233 l_chart_of_accounts_id NUMBER(15);
234 n number;
235
236 function description
237 (
238 p_segment_num number,
239 p_segment_value varchar2,
240 p_chart_of_accounts_id NUMBER
241 ) return varchar2
242 is
243 cursor cur_description
244 (
245 p_segment_num number,
246 p_segment_value varchar2,
247 p_chart_of_accounts_id NUMBER
248 )
249 is
250 SELECT VAL.description
251 FROM fnd_id_flex_segments FND,
252 fnd_flex_values_vl VAL
253 WHERE FND.id_flex_num = p_chart_of_accounts_id
254 AND FND.segment_num = NVL(p_segment_num, FND.segment_num)
255 AND FND.enabled_flag = 'Y'
256 AND FND.flex_value_set_id = VAL.flex_value_set_id
257 AND VAL.enabled_flag = 'Y'
258 AND VAL.flex_value = NVL(p_segment_value, VAL.flex_value)
259 AND NVL(VAL.description,' ') = NVL(null, NVL(VAL.description,' '))
260 AND VAL.summary_flag = 'N'
261 AND fnd.id_flex_code = 'GL#';
262
263 l_description fnd_flex_values_vl.description%TYPE;
264 begin
265 open cur_description(p_segment_num, p_segment_value, p_chart_of_accounts_id);
266 fetch cur_description into l_description;
267 close cur_description;
268 return l_description;
269 end description;
270 BEGIN
271 SELECT chart_of_accounts_id INTO l_chart_of_accounts_id FROM gmf_legal_entities
272 WHERE legal_entity_id = p_legal_entity_id;
273 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#', l_chart_of_accounts_id);
274 n := lengthb(p_account_key) - lengthb(replace(p_account_key, l_delimiter,null));
275 for i in 1..n+1 loop
276 source_accounts(i) := null;
277 end loop;
278 l_deli_process := 1;
279
280 l_start := 1;
281 l_acct_no := p_account_KEY;
282 FOR i IN 1..n+1 LOOP
283 IF (l_deli_process <= n) THEN
284 l_end := instr(l_acct_no,l_delimiter,1);
285 l_account_value := SUBSTR(l_acct_no,l_start,l_end - 1);
286 l_acct_no := SUBSTR(l_acct_no,l_end+1);
287 source_accounts(i) := l_account_value;
288 l_deli_process := l_deli_process + 1;
289 ELSE
290 l_account_value := SUBSTR(l_acct_no,l_start);
291 source_accounts(i) := l_account_value;
292 END IF;
293 END LOOP;
294
295 FOR i in 1..SOURCE_ACCOUNTS.COUNT LOOP
296 l_segmentval := source_accounts(i);
297 x_description := description(i, l_segmentval,l_chart_of_accounts_id);
298
299 IF i = 1 THEN
300 l_account_desc := x_description;
301 ELSE
302 l_account_desc := l_account_desc || l_delimiter || x_description;
303 END IF;
304
305 END LOOP;
306 RETURN l_account_desc;
307
308 END GET_ACCOUNT_DESC;
309
310 /*=========================================================
311 FUNCTION : get_item_number
312
313 DESCRIPTION: function will return the item number for the
314 Item id and organization id
315 History
316 Creted by : Prasad Marada 28-sep-2005
317 Who Date Comment
318 ==========================================================*/
319 FUNCTION get_item_number (p_inventory_item_id IN NUMBER,
320 p_organization_id IN NUMBER) RETURN VARCHAR2 IS
321
322 CURSOR cur_item_number (cp_inventory_item_id mtl_item_flexfields.inventory_item_id%TYPE,
323 cp_organization_id mtl_item_flexfields.organization_id%TYPE) IS
324 SELECT item_number FROM mtl_item_flexfields
325 WHERE inventory_item_id = cp_inventory_item_id
326 AND organization_id = cp_organization_id;
327
328 l_item_number VARCHAR2(2000);
329
330 BEGIN
331
332 OPEN cur_item_number (p_inventory_item_id, p_organization_id );
333 FETCH cur_item_number INTO l_item_number;
334 CLOSE cur_item_number;
335
336 RETURN(l_item_number);
337
338 END get_item_number;
339
340 /*=========================================================
341 FUNCTION : get_cost_category
342
343 DESCRIPTION: function will return the cost category for the
344 cost category id and organization id
345 History
346 Creted by : Prasad Marada 28-sep-2005
347 Who Date Comment
348 ==========================================================*/
349 FUNCTION get_cost_category(p_category_id IN NUMBER ) RETURN VARCHAR2 IS
350
351 CURSOR cur_cost_category (cp_cost_category_id mtl_categories_kfv.category_id%TYPE) IS
352 SELECT concatenated_segments FROM mtl_categories_kfv
353 WHERE category_id = cp_cost_category_id;
354
355 l_cost_category VARCHAR2(2000);
356
357 BEGIN
358 OPEN cur_cost_category (p_category_id);
359 FETCH cur_cost_category INTO l_cost_category;
360 CLOSE cur_cost_category;
361
362 RETURN (l_cost_category);
363
364 END get_cost_category;
365
366
367 END GMF_UTILITIES_GRP;
[Home] [Help]