[Home] [Help]
PACKAGE BODY: APPS.GMF_GET_MAPPINGS
Source
1 PACKAGE BODY GMF_GET_MAPPINGS AS
2 /* $Header: gmfactmb.pls 115.27 2003/05/21 20:34:01 sschinch ship $ */
3
4 /* Declarations for AR merge */
5
6 TYPE A_seg_len IS TABLE OF gl_plcy_seg.length%TYPE INDEX BY BINARY_INTEGER;
7
8 GA_of_seg A_segment;
9 GA_of_seg_len A_seg_len;
10 GA_of_seg_pos A_seg_len;
11 sv_co_code sy_orgn_mst.co_code%TYPE := 'x';
12
13
14 Gn_of_seg NUMBER := -1;
15
16 /* Coming functions compare are special function
17 REM to help compare the column value with a given value.
18 REM Used for searching a right account.
19 REM */
20
21 FUNCTION IsEmpty(p_str IN VARCHAR2) RETURN BOOLEAN IS
22 BEGIN
23 /* B1043070: No change for "rtrim", we want char stripping from right */
24
25 IF ( p_str IS NULL OR RTRIM(p_str) IS NULL )
26 THEN
27 RETURN(TRUE);
28 ELSE
29 RETURN (FALSE);
30 END IF;
31 END;
32
33
34 FUNCTION fstrcmp(p_col IN VARCHAR2, p_val IN VARCHAR2) RETURN NUMBER IS
35 BEGIN
36 IF (p_col = p_val OR p_col IS NULL OR p_col = ' ')
37 THEN
38 RETURN (1);
39 ELSE
40 RETURN (0);
41 END IF;
42 END;
43
44 FUNCTION fnumcmp(p_col IN NUMBER, p_val IN NUMBER) RETURN NUMBER IS
45 BEGIN
46 IF (p_col = p_val OR p_col IS NULL OR p_col = 0 )
47 THEN
48 RETURN (1);
49 ELSE
50 RETURN (0);
51 END IF;
52 END;
53
54 /*##################################################
55 # NAME get_account_mappings
56 #
57 # SYNOPSIS
58 # Proc get_account_mappings
59 # Parms
60 # DESCRIPTION
61 # Fetches the account mapping for AR update Package.
62 # GMFARUPD
63 # HISTORY
64 # 11-Sep-2001 Uday Moogala Bug 2031374 - New Item Attributes
65 # Added two new item attributes - GL Business Class and GL Product Line
66 # as input parameters to get_account_mappings procedures.
67 # Also made other changes required to incorporate this feature at
68 # various places. Search with bug# for changes made
69 # 11-Oct-2001 Uday Moogala Bug 2468912 - New Attribute
70 # Added a new attribute Line Type as input parameters to
71 # get_account_mappings procedures. Search with bug# for changes made
72 # 22-Oct-2001 Uday Moogala Bug 2423983 - New Attribute
73 # Added a new attribute AR Trans Type as input parameters to
74 # get_account_mappings procedures. Search with bug# for changes made
75 ################################################### */
76
77 PROCEDURE get_account_mappings (
78 v_co_code IN OUT NOCOPY VARCHAR2,
79 v_orgn_code VARCHAR2,
80 v_whse_code VARCHAR2,
81 v_item_id NUMBER,
82 v_vendor_id NUMBER,
83 v_cust_id NUMBER,
84 v_reason_code VARCHAR2,
85 v_icgl_class VARCHAR2,
86 v_vendgl_class VARCHAR2,
87 v_custgl_class VARCHAR2,
88 v_currency_code VARCHAR2,
89 v_routing_id NUMBER,
90 v_charge_id NUMBER,
91 v_taxauth_id NUMBER,
92 v_aqui_cost_id NUMBER,
93 v_resources VARCHAR2,
94 v_cost_cmpntcls_id NUMBER,
95 v_cost_analysis_code VARCHAR2,
96 v_order_type NUMBER,
97 v_sub_event_type NUMBER,
98 v_acct_ttl_type NUMBER,
99 v_acct_id IN OUT NOCOPY NUMBER,
100 v_acctg_unit_id IN OUT NOCOPY NUMBER,
101 v_source NUMBER DEFAULT 0,
102 v_business_class_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
103 v_product_line_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
104 v_line_type NUMBER DEFAULT NULL, -- Bug 2468912 - umoogala
105 v_ar_trx_type_id NUMBER DEFAULT 0 -- Bug 2423983 - umoogala
106 )
107 IS
108 X_sqlstmt VARCHAR2(2000);
109 X_sqlwhere VARCHAR2(2000);
110 X_sqlwhere1 VARCHAR2(2000) DEFAULT '';
111 x_order_by gmf_get_mappings.my_order_by;
112 X_my_order_by VARCHAR2(200);
113 X_sqlwhere2 VARCHAR2(2000);
114 X_sqlwhere3 VARCHAR2(2000);
115 X_sqlwhere4 VARCHAR2(2000);
116 X_sqlwhere5 VARCHAR2(2000);
117 X_sqlwhere6 VARCHAR2(2000);
118 X_sqlcolumns VARCHAR2(2000);
119 X_sqlcolumns1 VARCHAR2(2000);
120 X_sqlcolumns2 VARCHAR2(2000);
121 X_acct_id gl_acct_mst.acct_id%TYPE;
122 X_sqlordby VARCHAR2(1000);
123 X_tmp1 NUMBER(10);
124 X_cursor_handle INTEGER;
125 i INTEGER DEFAULT 0;
126 X_sqlstmt1 VARCHAR2(2000); -- Bug 2031374 - umoogala 200 -> 2000
127 X_sqlstmt2 VARCHAR2(2000); -- Bug 2031374 - umoogala 200 -> 2000
128 X_num_col NUMBER(15);
129 X_rows_processed NUMBER(15);
130 X_whse_orgn VARCHAR2(4);
131 X_map_whse_co VARCHAR2(4);
132 X_map_orgn_co VARCHAR2(4);
133 X_map_orgn_code VARCHAR2(4);
134 X_space VARCHAR2(10) := ' ';
135
136
137 x_co_code sy_orgn_mst.co_code%TYPE;
138 CURSOR Cur_get_company(v_org_code VARCHAR2) IS
139 SELECT co_code
140 FROM sy_orgn_mst
141 WHERE orgn_code = v_org_code;
142
143 CURSOR Cur_get_whse_orgn(v_whs_code VARCHAR2) IS
144 SELECT orgn_code
145 FROM ic_whse_mst
146 WHERE whse_code = v_whs_code;
147 BEGIN
148
149 /* This cursor fetches the co_code of specified organizaton. */
150
151 IF (v_orgn_code IS NOT NULL) THEN
152 OPEN Cur_get_company(v_orgn_code);
153 FETCH Cur_get_company INTO X_map_orgn_co;
154 CLOSE Cur_get_company;
155 END IF;
156
157 IF (v_whse_code IS NOT NULL) THEN
158 OPEN Cur_get_whse_orgn(v_whse_code);
159 FETCH Cur_get_whse_orgn INTO X_whse_orgn;
160 CLOSE Cur_get_whse_orgn;
161 END IF;
162
163 IF (X_whse_orgn IS NOT NULL) THEN
164 OPEN Cur_get_company(X_whse_orgn);
165 FETCH Cur_get_company INTO X_map_whse_co;
166 CLOSE Cur_get_company;
167 END IF;
168
169 i := 0;
170 /* Get the priorities for the Account Title
171 REM ---------------------------------------- */
172
173 FOR Cur_subevtacct_ttl IN (
174 SELECT map_orgn_ind, acct_ttl_type
175 FROM gl_sevt_ttl
176 WHERE sub_event_type = v_sub_event_type
177 AND acct_ttl_type = v_acct_ttl_type)
178 LOOP
179
180 /* VC - Bug 1498503 */
181 IF (Cur_subevtacct_ttl.map_orgn_ind = 1) THEN
182 X_map_orgn_code := V_orgn_code;
183 X_co_code := X_map_orgn_co;
184 ELSE
185 X_co_code := X_map_whse_co;
186 X_map_orgn_code := X_whse_orgn;
187 END IF;
188
189 IF ( IsEmpty(X_co_code) )
190 THEN
191 X_co_code := v_co_code;
192 END IF;
193 v_co_code := X_co_code;
194
195 IF ( IsEmpty(X_map_orgn_code) )
196 THEN
197 X_map_orgn_code := v_orgn_code;
198 END IF;
199
200 --X_sqlstmt := 'SELECT NVL(co_code,'' ''),orgn_code_pri, whse_code_pri,icgl_class_pri, custgl_class_pri,vendgl_class_pri ,item_pri,customer_pri, vendor_pri,tax_auth_pri,';
201
202 X_sqlstmt := 'SELECT NVL(co_code,'' ''),orgn_code_pri, whse_code_pri,icgl_class_pri, custgl_class_pri,vendgl_class_pri ,item_pri,customer_pri, vendor_pri,tax_auth_pri,';
203
204 --
205 -- Bug 2031374 - umoogala : Added gl_business_class_pri and gl_product_line_pri
206 -- Bug 2468912 - umoogala : Added line_type_pri
207 -- Bug 2423983 - umoogala : Added ar_trx_type_pri
208 --
209 X_sqlstmt1 := 'charge_pri,currency_code_pri, reason_code_pri,routing_pri, aqui_cost_code_pri,resource_pri, ' ||
210 'cost_cmpntcls_pri,cost_analysis_pri, order_type_pri, gl_business_class_pri, gl_product_line_pri, line_type_pri, ar_trx_type_pri FROM gl_acct_hrc ';
211
212 --X_sqlstmt2 := ' WHERE acct_ttl_type = '||to_char(Cur_subevtacct_ttl.acct_ttl_type) || ' AND gmf_get_mappings.fstrcmp(co_code,'''||v_co_code||''')=1 AND delete_mark = 0 ORDER BY 1 desc';
213
214 X_sqlstmt2 := ' WHERE acct_ttl_type = :pacct_ttl_type AND (co_code IS NULL OR co_code = :pco_code) AND delete_mark = 0 ORDER BY 1 desc';
215
216
217 X_cursor_handle := DBMS_SQL.OPEN_CURSOR;
218
219 DBMS_SQL.PARSE(X_cursor_handle,X_sqlstmt||X_sqlstmt1||X_sqlstmt2,DBMS_SQL.V7);
220
221 --DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pspace',x_space);
222 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pacct_ttl_type',Cur_subevtacct_ttl.acct_ttl_type);
223 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pco_code',v_co_code);
224
225 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
226 -- Bug 2423983: 22 to 23
227 FOR k IN 2..23 LOOP
228 DBMS_SQL.DEFINE_COLUMN(X_cursor_handle,k,x_tmp1);
229 END LOOP;
230 X_rows_processed := DBMS_SQL.EXECUTE(X_cursor_handle);
231
232 /* selecting the acct_id in gl_acct_map
233 create the order_by based on the priority retrieved above.
234 company is always the first column selected */
235
236 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
237 -- Bug 2423983: 22 to 23
238 FOR z IN 1..23 LOOP
239 X_order_by(z) := 0;
240 END LOOP;
241
242 X_my_order_by := 'ORDER BY 1 desc';
243
244 IF (DBMS_SQL.FETCH_ROWS(X_cursor_handle) > 0) THEN
245 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
246 -- Bug 2423983: 22 to 23
247 FOR j IN 2..23 LOOP
248 DBMS_SQL.COLUMN_VALUE(X_cursor_handle,j,x_tmp1);
249 IF (X_tmp1 > 0) THEN
250 x_tmp1:=x_tmp1 + 1;
251 X_order_by(x_tmp1) := j;
252 END IF;
253 END LOOP;
254 END IF;
255
256 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
257 -- Bug 2423983: 22 to 23
258 FOR z IN 2..23 LOOP
259 IF (X_order_by(z) > 0) THEN
260 X_my_order_by := X_my_order_by||','||to_char(x_order_by(z))||' desc ';
261 END IF;
262 END LOOP;
263 DBMS_SQL.CLOSE_CURSOR(X_cursor_handle);
264
265 /* X_sqlwhere := 'WHERE acct_ttl_type = '||to_char(Cur_subevtacct_ttl.acct_ttl_type)||
266 ' AND gmf_get_mappings.fstrcmp(co_code, ''' || X_co_code || ''')=1' ||
267 ' AND gmf_get_mappings.fstrcmp(whse_code, ''' || v_whse_code || ''')=1'; */
268
269 X_sqlwhere := 'WHERE acct_ttl_type = :pacct_ttl_type AND (co_code IS NULL OR co_code = :pco_code) '||
270 ' AND (whse_code IS NULL OR whse_code = :pwhse_code) ';
271
272 /*X_sqlwhere1:= ' AND gmf_get_mappings.fstrcmp(orgn_code, ''' || X_map_orgn_code || ''')=1' ||
273 ' AND gmf_get_mappings.fnumcmp(item_id, ' || NVL(v_item_id, 0) || ')=1 ' ||
274 ' AND gmf_get_mappings.fnumcmp(vendor_id, ' || NVL(v_vendor_id,0) || ')=1 ' ; */
275
276 X_sqlwhere1:= ' AND (orgn_code IS NULL OR orgn_code = :pmap_orgn_code ) '||
277 ' AND (item_id IS NULL OR item_id = :pitem_id) AND (vendor_id IS NULL OR vendor_id = :pvendor_id) ' ;
278
279
280 /* X_sqlwhere2:= ' AND gmf_get_mappings.fnumcmp(cust_id, ' || NVL(v_cust_id,0) || ')=1' ||
281 ' AND gmf_get_mappings.fstrcmp(reason_code, ''' || v_reason_code || ''')=1' ||
282 ' AND gmf_get_mappings.fstrcmp(icgl_class, ''' || v_icgl_class || ''')=1'; */
283
284 X_sqlwhere2:= ' AND (cust_id IS NULL OR cust_id = :pcust_id) '||
285 ' AND (reason_code IS NULL OR reason_code = :preason_code) '||
286 ' AND (icgl_class IS NULL OR icgl_class = :picgl_class) ';
287
288
289 /* X_sqlwhere3:= ' AND gmf_get_mappings.fstrcmp(vendgl_class, ''' || v_vendgl_class || ''')=1' ||
290 ' AND gmf_get_mappings.fstrcmp(custgl_class, ''' || v_custgl_class || ''')=1'; */
291
292 X_sqlwhere3:= ' AND (vendgl_class IS NULL OR vendgl_class = :pvendgl_class) '||
293 ' AND (custgl_class IS NULL OR custgl_class = :pcustgl_class) ';
294
295
296 /* X_sqlwhere4:= ' AND gmf_get_mappings.fstrcmp(currency_code, '''||v_currency_code||''')=1' ||
297 ' AND gmf_get_mappings.fnumcmp(routing_id, '||nvl(v_routing_id,0)||')=1'; */
298
299 X_sqlwhere4:= ' AND (currency_code IS NULL OR currency_code = :pcurrency_code) '||
300 ' AND (routing_id IS NULL OR routing_id = :prouting_id) ';
301
302 /* X_sqlwhere5:= ' AND gmf_get_mappings.fnumcmp(charge_id, '||nvl(v_charge_id,0)||')=1' ||
303 ' AND gmf_get_mappings.fnumcmp(taxauth_id, '||nvl(v_taxauth_id,0)||')=1' ||
304 ' AND gmf_get_mappings.fnumcmp(aqui_cost_id, '||nvl(v_aqui_cost_id,0)||')=1' ; */
305
306 X_sqlwhere5:= ' AND (charge_id IS NULL OR charge_id = :pcharge_id) ' ||
307 ' AND (taxauth_id IS NULL OR taxauth_id = :ptaxauth_id) ' ||
308 ' AND (aqui_cost_id IS NULL OR aqui_cost_id = :paqui_cost_id) ' ;
309
310 /* X_sqlwhere6 := ' AND gmf_get_mappings.fstrcmp(resources, '''||v_resources||''')=1' ||
311 ' AND gmf_get_mappings.fnumcmp(cost_cmpntcls_id, '||nvl(v_cost_cmpntcls_id,0)||')=1' ||
312 ' AND gmf_get_mappings.fstrcmp(cost_analysis_code, '''||v_cost_analysis_code||''')=1' ||
313 ' AND delete_mark = 0 '; */
314
315 X_sqlwhere6 := ' AND (resources IS NULL OR resources = :presources) ' ||
316 ' AND (cost_cmpntcls_id IS NULL OR cost_cmpntcls_id = :pcost_cmpntcls_id) ' ||
317 ' AND (cost_analysis_code IS NULL OR cost_analysis_code = :pcost_analysis_code) ' ||
318 ' AND delete_mark = 0 ';
319
320 /**
321 * RS B1408077 Based on the source passed add condition on the source type column
322 */
323 IF( (v_order_type IS NOT NULL) AND (v_order_type <> 0 ) )
324 THEN
325
326 IF( v_source = 11 )
327 THEN
328 /* Bug 2431861
329 X_sqlwhere6 := X_sqlwhere6 || ' AND ( source_type = 11 ' ||
330 ' AND gmf_get_mappings.fnumcmp(order_type, '||nvl(v_order_type,0)||')=1 ) ';
331
332 X_sqlwhere6 := X_sqlwhere6 || ' AND (( source_type = 11 ' ||
333 ' AND order_type = '||to_char(nvl(v_order_type,0))||')'||' OR order_type IS NULL)'; */
334
335 X_sqlwhere6 := X_sqlwhere6 || ' AND ( source_type = 11 ' ||
336 ' AND (order_type = :porder_type OR order_type IS NULL))';
337
338 ELSE
339 /* X_sqlwhere6 := X_sqlwhere6 || ' AND ( source_type IS NULL ' ||
340 ' AND gmf_get_mappings.fnumcmp(order_type, '||nvl(v_order_type,0)||')=1 ) '; */
341
342 X_sqlwhere6 := X_sqlwhere6 || ' AND ( source_type IS NULL ' ||
343 ' AND (order_type IS NULL OR order_type = :porder_type)) ';
344
345
346 END IF;
347
348 ELSE
349 /* Do it as before */
350 -- X_sqlwhere6 := X_sqlwhere6 || ' AND gmf_get_mappings.fnumcmp(order_type, '||nvl(v_order_type,0)||')=1 ';
351 X_sqlwhere6 := X_sqlwhere6 || ' AND (order_type IS NULL OR order_type = :porder_type) ';
352
353 END IF;
354 /* End B1408077 */
355
356 /*
357 * Bug 2031374 - umoogala : Added gl_business_class_pri and gl_product_line_pri
358 * Bug 2468912 - umoogala : Added line_type
359 * Bug 2423983 - umoogala : Added ar_trx_type_id
360 */
361
362 /* X_sqlwhere6 := X_sqlwhere6 ||
363 ' AND gmf_get_mappings.fnumcmp(gl_business_class_cat_id, ' ||
364 nvl(v_business_class_cat_id,0)||')=1 ' ||
365 ' AND gmf_get_mappings.fnumcmp(gl_product_line_cat_id, ' ||
366 nvl(v_product_line_cat_id,0)||')=1 ' ||
367 ' AND gmf_get_mappings.fnumcmp(line_type, ' ||
368 nvl(v_line_type,0)||')=1 ' ||
369 ' AND gmf_get_mappings.fnumcmp(ar_trx_type_id, ' ||
370 nvl(v_ar_trx_type_id,0)||')=1 ' ; */
371
372 X_sqlwhere6 := X_sqlwhere6 ||
373 ' AND (gl_business_class_cat_id IS NULL OR gl_business_class_cat_id = :pbusiness_class_cat_id) ' ||
374 ' AND (gl_product_line_cat_id IS NULL OR gl_product_line_cat_id = :pproduct_line_cat_id) ' ||
375 ' AND (line_type IS NULL OR line_type = :pline_type) ' ||
376 ' AND (ar_trx_type_id IS NULL OR ar_trx_type_id = :par_trx_type_id) ' ;
377
378
379
380 /* End Bug 2031374 */
381
382 /* Changed the selection of acct_id to be the last column to be
383 selected. If not since the order by is by field position no the
384 correct acct_id doesnot get picked up if there are more than 1 record */
385
386 X_sqlcolumns:= ' SELECT co_code,'||
387 'nvl(orgn_code,'' ''),'||
388 'nvl(whse_code,'' ''),'||
389 'nvl(icgl_class,'' ''),'||
390 'nvl(custgl_class,'' ''),'||
391 'nvl(vendgl_class,'' ''),';
392
393 X_sqlcolumns1:= 'nvl(item_id,0),'||
394 'nvl(cust_id,0),'||
395 'nvl(vendor_id,0),'||
396 'nvl(taxauth_id,0),'||
397 'nvl(charge_id,0),'||
398 'nvl(currency_code,'' ''),'||
399 'nvl(reason_code,'' ''),';
400
401 --
402 -- Bug 2468912 - umoogala
403 -- here nvl(line_type,-99) is done to sort the values properly in case of
404 -- ingredients (line_type = -1).
405 -- In case of nvl..0, when we sort, 0 will come first then -1.
406 -- nvl..-99 will put null line type after -1. Other values of line type, 1 and 2,
407 -- will not have any issue as they are > 0
408 --
409 X_sqlcolumns2:= 'nvl(routing_id,0),'||
410 'nvl(aqui_cost_id,0),'||
411 'nvl(resources,'' ''),'||
412 'nvl(cost_cmpntcls_id,0),'||
413 'nvl(cost_analysis_code,'' ''),'||
414 'nvl(order_type,0),'||
415 'nvl(gl_business_class_cat_id,0), '|| -- Bug 2031374 - umoogala
416 'nvl(gl_product_line_cat_id,0), ' || -- Bug 2031374 - umoogala
417 'nvl(line_type,-99), ' || -- Bug 2468912 - umoogala
418 'nvl(ar_trx_type_id,0), ' || -- Bug 2423983 - umoogala
419 'acct_id ' ;
420
421 X_sqlordby:= X_my_order_by;
422
423 X_cursor_handle := DBMS_SQL.OPEN_CURSOR;
424
425 DBMS_SQL.PARSE(X_cursor_handle,X_sqlcolumns||X_sqlcolumns1||X_sqlcolumns2||' FROM gl_acct_map '||X_sqlwhere||X_sqlwhere1||X_sqlwhere2||X_sqlwhere3||X_sqlwhere4||X_sqlwhere5||X_sqlwhere6||X_sqlordby,DBMS_SQL.V7);
426 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pacct_ttl_type',Cur_subevtacct_ttl.acct_ttl_type);
427 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pco_code',x_co_code);
428 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pwhse_code',v_whse_code);
429 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pmap_orgn_code',x_map_orgn_code);
430 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pitem_id',v_item_id);
431 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pvendor_id',v_vendor_id);
432 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcust_id',v_cust_id);
433 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':preason_code',v_reason_code);
434 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':picgl_class',v_icgl_class);
435 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pvendgl_class',v_vendgl_class);
436 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcustgl_class',v_custgl_class);
437 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcurrency_code',v_currency_code);
438 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':prouting_id',v_routing_id);
439 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcharge_id',v_charge_id);
440 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':ptaxauth_id',v_taxauth_id);
441 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':paqui_cost_id',v_aqui_cost_id);
442 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':presources',v_resources);
443 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcost_cmpntcls_id',v_cost_cmpntcls_id);
444 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcost_analysis_code',v_cost_analysis_code);
445 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':porder_type',v_order_type);
446 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pbusiness_class_cat_id',v_business_class_cat_id);
447 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pproduct_line_cat_id',v_product_line_cat_id);
448 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pline_type',v_line_type);
449 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':par_trx_type_id',v_ar_trx_type_id);
450
451
452 -- Bug 2031374 - umoogala : 20 -> 22, Bug 2468912: 22 to 23
453 -- Bug 2423983: 23 to 24
454 DBMS_SQL.DEFINE_COLUMN(X_cursor_handle,24,x_num_col);
455 x_rows_processed := DBMS_SQL.EXECUTE(X_cursor_handle);
456 IF (DBMS_SQL.FETCH_ROWS(X_cursor_handle) > 0) THEN
457 i:= i + 1;
458 -- Bug 2031374 - umoogala : 20 -> 22, Bug 2468912: 22 to 23
459 -- Bug 2423983: 23 to 24
460 DBMS_SQL.COLUMN_VALUE(X_cursor_handle,24,gmf_get_mappings.my_accounts(i).acct_id);
461 gmf_get_mappings.my_accounts(i).acct_ttl_type := Cur_subevtacct_ttl.acct_ttl_type;
462 END IF;
463 DBMS_SQL.CLOSE_CURSOR(x_cursor_handle);
464 END LOOP;
465 no_of_rows := i;
466
467 /* No rows found */
468 v_acct_id := -1;
469
470 IF (no_of_rows > 0)
471 THEN
472 FOR i IN 1..no_of_rows LOOP
473 IF (my_accounts(i).acct_ttl_type = v_acct_ttl_type)
474 THEN
475 v_acct_id :=NVL(my_accounts(i).acct_id,0);
476 EXIT;
477 END IF;
478 END LOOP;
479 ELSE
480 v_acct_id := -2;
481 END IF;
482
483 v_acctg_unit_id := -1;
484 /* FOR crec IN (SELECT acctg_unit_id
485 FROM gl_accu_map
486 WHERE co_code = X_co_code
487 AND gmf_get_mappings.fstrcmp(orgn_code, X_map_orgn_code) = 1
488 AND gmf_get_mappings.fstrcmp(whse_code, v_whse_code) = 1
489 AND delete_mark = 0
490 ORDER BY nvl(orgn_code,' ') DESC, nvl(whse_code,' ') DESC
491 ) */
492
493 FOR crec IN (SELECT acctg_unit_id
494 FROM gl_accu_map
495 WHERE co_code = X_co_code
496 AND (orgn_code IS NULL OR orgn_code = X_map_orgn_code)
497 AND (whse_code IS NULL OR whse_code = v_whse_code)
498 AND delete_mark = 0
499 ORDER BY nvl(orgn_code,' ') DESC, nvl(whse_code,' ') DESC
500 )
501 LOOP
502 v_acctg_unit_id := crec.acctg_unit_id;
503 EXIT;
504 END LOOP;
505
506 sv_gl_acct_map.co_code := v_co_code;
507 sv_gl_acct_map.acct_id := v_acct_id;
508 sv_acctg_unit_id := v_acctg_unit_id;
509
510 END get_account_mappings;
511
512 /* This procedure parses to maintain the sequence by refernce no.
513 REM e.g. segment1, 10, 11, 12 will go to segment 1,2,3,4
514 */
515
516 PROCEDURE parse_account(p_co_code VARCHAR2, p_acct IN VARCHAR2, p_of_seg IN OUT NOCOPY A_segment)
517 IS
518 i NUMBER := 0;
519 n NUMBER := 0;
520 source_account my_opm_seg_values; /* Array of source accounts */
521 BEGIN
522 i := 1;
523 IF (p_acct IS NOT NULL) THEN
524 source_account := get_opm_segment_values(p_acct,p_co_code,2);
525 END IF;
526 IF ( sv_co_code <> p_co_code )
527 THEN
528 /* Get the GEMMS to OF account segment mapping information */
529
530 Gn_of_seg := 0;
531 FOR i IN 1..31
532 LOOP
533 GA_of_seg_pos(i) := 0;
534 GA_of_seg_len(i) := 0;
535 END LOOP;
536 n := 1;
537 /* Stores the current position */
538 FOR crec IN (SELECT
539 /* B1043070: Changed "substrb" to "substr", we want
540 REM everything from 8th character and not byte */
541 /* B2227050: select f.segment_num instead of f.application_column_name
542 REM order by f.segment_num instead of p.segment_no */
543 f.segment_num, p.segment_no, p.length
544 FROM
545 gl_plcy_seg p,
546 gl_plcy_mst pm,
547 fnd_id_flex_segments f,
548 gl_sets_of_books s
549 WHERE
550 p.co_code = p_co_code
551 AND p.delete_mark = 0
552 AND p.co_code = pm.co_code
553 AND pm.sob_id = s.set_of_books_id
554 AND s.chart_of_accounts_id = f.id_flex_num
555 AND f.application_id = 101
556 AND f.id_flex_code = 'GL#'
557 /* B1043070 Changed upper to lower */
558 AND LOWER(f.segment_name) = LOWER(p.short_name)
559 AND f.enabled_flag = 'Y'
560 ORDER BY f.segment_num)
561
562 LOOP
563 Gn_of_seg := Gn_of_seg + 1;
564 /* Bug 2227050
565 GA_of_seg_pos(crec.segment_ref) := n;
566 GA_of_seg_len(crec.segment_ref) := crec.length;
567 */
568 GA_of_seg_pos(Gn_of_seg) := crec.segment_no;
569 GA_of_seg_len(Gn_of_seg) := crec.length;
570 n := n + 1 ; /* + crec.length; */
571 END LOOP;
572 sv_co_code := p_co_code;
573 END IF;
574
575 FOR i IN 1..31
576 LOOP
577 IF ( GA_of_seg_len(i) > 0 )
578 THEN
579 /* B1043070: Changed "substrb" to "substr", we want 8th character
580 REM GA_of_seg(i) := SUBSTRB(p_acct, GA_of_seg_pos(i), GA_of_seg_len(i)); */
581 /* No need to check segment lengths */
582 -- GA_of_seg(i) := SUBSTR(p_acct, GA_of_seg_pos(i), GA_of_seg_len(i));
583 GA_of_seg(i) := source_account(GA_of_seg_pos(i));
584 ELSE
585 GA_of_seg(i) := NULL;
586 END IF;
587 p_of_seg(i) := GA_of_seg(i);
588 END LOOP;
589 END parse_account;
590
591 /* DESCRIPTION
592 REM Based on acctg_unit_id acct_id and co_code get the acctg_unit and
593 REM acct_no and separate the segments based on the segment delimiter info
594 REM PCR#9867 - Segments on gemms side and oracle financials need not
595 REM have one to one correspondence. i.e., the first segment in
596 REM gemms might be the 3rd segment in oracle financials. */
597
598 PROCEDURE get_of_seg(p_co_code IN VARCHAR2, p_acct_id NUMBER, p_acctg_unit_id IN NUMBER, p_of_seg IN OUT NOCOPY A_segment, rc IN OUT NOCOPY NUMBER)
599 IS
600 v_acctg_unit_no gl_accu_mst.acctg_unit_no%TYPE := NULL;
601 v_acct_no gl_acct_mst.acct_no%TYPE := NULL;
602 v_segment_delimiter gl_plcy_mst.segment_delimiter%TYPE;
603 BEGIN
604 rc := 0;
605 SELECT acctg_unit_no INTO v_acctg_unit_no
606 FROM gl_accu_mst WHERE acctg_unit_id = p_acctg_unit_id;
607
608 SELECT acct_no INTO v_acct_no
609 FROM gl_acct_mst
610 WHERE acct_id = p_acct_id;
611
612 SELECT segment_delimiter INTO v_segment_delimiter
613 FROM gl_plcy_mst
614 WHERE co_code = p_co_code
615 AND delete_mark = 0;
616
617 /* It is ok to use . as delimter, it will be ignored any way */
618 /* It is not ok to hard code . as delimiter. Fetch it from policy master. */
619
620 /* parse_account(p_co_code, v_acctg_unit_no || '.' || v_acct_no, p_of_seg); */
621 parse_account(p_co_code, v_acctg_unit_no || v_segment_delimiter || v_acct_no, p_of_seg);
622 EXCEPTION
623 WHEN OTHERS THEN
624 IF ( v_acctg_unit_no IS NULL )
625 THEN
626 rc := -1; /* error in acctg_unit_no */
627 ELSE
628 rc := -2; /* error in acct_no */
629 END IF;
630 END get_of_seg;
631
632
633 /*##################################################
634 # NAME get_account_mappings
635 #
636 # SYNOPSIS
637 # Proc get_account_mappings
638 # Parms
639 # DESCRIPTION
640 # Fetches the accounts defined in account mapping
641 # form.
642 # HISTORY
643 # 11-Sep-2001 Uday Moogala Bug 2031374 - New Item Attributes
644 # Added two new item attributes - GL Business Class and GL Product Line
645 # as input parameters to get_account_mappings procedures.
646 # Also made other changes required to incorporate this feature at
647 # various places. Search with bug# for changes made
648 # 11-Oct-2001 Uday Moogala Bug 2468912 - New Attribute
649 # Added a new attribute Line Type as input parameters to
650 # get_account_mappings procedures. Search with bug# for changes made
651 # 22-Oct-2001 Uday Moogala Bug 2423983 - New Attribute
652 # Added a new attribute AR Trans Type as input parameters to
653 # get_account_mappings procedures. Search with bug# for changes made
654 ################################################### */
655 PROCEDURE get_account_mappings (v_co_code VARCHAR2,
656 v_orgn_code VARCHAR2,
657 v_whse_code VARCHAR2,
658 v_item_id NUMBER,
659 v_vendor_id NUMBER,
660 v_cust_id NUMBER,
661 v_reason_code VARCHAR2,
662 v_icgl_class VARCHAR2,
663 v_vendgl_class VARCHAR2,
664 v_custgl_class VARCHAR2,
665 v_currency_code VARCHAR2,
666 v_routing_id NUMBER,
667 v_charge_id NUMBER,
668 v_taxauth_id NUMBER,
669 v_aqui_cost_id NUMBER,
670 v_resources VARCHAR2,
671 v_cost_cmpntcls_id NUMBER,
672 v_cost_analysis_code VARCHAR2,
673 v_order_type NUMBER,
674 v_sub_event_type NUMBER,
675 v_source NUMBER DEFAULT 0,
676 v_business_class_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
677 v_product_line_cat_id NUMBER DEFAULT 0, -- Bug 2031374 - umoogala
678 v_line_type NUMBER, -- Bug 2468912 - umoogala
679 v_ar_trx_type_id NUMBER DEFAULT 0 -- Bug 2423983 - umoogala
680 ) IS
681 X_sqlstmt VARCHAR2(2000);
682 X_sqlwhere VARCHAR2(2000);
683 X_sqlwhere1 VARCHAR2(2000) DEFAULT '';
684 x_order_by gmf_get_mappings.my_order_by;
685 X_my_order_by VARCHAR2(200);
686 X_sqlwhere2 VARCHAR2(2000);
687 X_sqlwhere3 VARCHAR2(2000);
688 X_sqlwhere4 VARCHAR2(2000);
689 X_sqlwhere5 VARCHAR2(2000);
690 X_sqlwhere6 VARCHAR2(2000);
691 X_sqlwhere7 VARCHAR2(2000);
692 X_sqlcolumns VARCHAR2(2000);
693 X_sqlcolumns1 VARCHAR2(2000);
694 X_sqlcolumns2 VARCHAR2(2000);
695 X_acct_id gl_acct_mst.acct_id%TYPE;
696 X_sqlordby VARCHAR2(1000);
697 X_tmp1 NUMBER(10);
698 X_cursor_handle INTEGER;
699 i INTEGER DEFAULT 0;
700 X_sqlstmt1 VARCHAR2(2000); -- Bug 2031374 - umoogala 200 -> 2000
701 X_sqlstmt2 VARCHAR2(2000); -- Bug 2031374 - umoogala 200 -> 2000
702 X_num_col NUMBER(15);
703 X_rows_processed NUMBER(15);
704 X_whse_orgn VARCHAR2(4);
705 X_map_whse_co VARCHAR2(4);
706 X_map_orgn_co VARCHAR2(4);
707 X_map_orgn_code VARCHAR2(4);
708 x_co_code sy_orgn_mst.co_code%TYPE;
709 CURSOR Cur_sevtacct_ttl IS
710 SELECT map_orgn_ind,
711 acct_ttl_type
712 FROM gl_sevt_ttl
713 WHERE sub_event_type = v_sub_event_type;
714
715 CURSOR Cur_get_company(v_org_code VARCHAR2) IS
716 SELECT co_code
717 FROM sy_orgn_mst
718 WHERE orgn_code = v_org_code;
719
720 CURSOR Cur_get_whse_orgn(v_whs_code VARCHAR2) IS
721 SELECT orgn_code
722 FROM ic_whse_mst
723 WHERE whse_code = v_whs_code;
724 BEGIN
725 /* This cursor fetches the co_code of specified organizaton.*/
726 IF (v_orgn_code IS NOT NULL) THEN
727 OPEN Cur_get_company(v_orgn_code);
728 FETCH Cur_get_company INTO X_map_orgn_co;
729 CLOSE Cur_get_company;
730 END IF;
731
732 IF (v_whse_code IS NOT NULL) THEN
733 OPEN Cur_get_whse_orgn(v_whse_code);
734 FETCH Cur_get_whse_orgn INTO X_whse_orgn;
735 CLOSE Cur_get_whse_orgn;
736 END IF;
737
738 IF (X_whse_orgn IS NOT NULL) THEN
739 OPEN Cur_get_company(X_whse_orgn);
740 FETCH Cur_get_company INTO X_map_whse_co;
741 CLOSE Cur_get_company;
742 END IF;
743
744
745 i := 0;
746 FOR Cur_subevtacct_ttl IN Cur_sevtacct_ttl LOOP
747 /*get the priorities for the account title*/
748 X_sqlstmt := 'SELECT '||'NVL(co_code,'||''''||' '||''''||')'||',orgn_code_pri,
749 whse_code_pri,icgl_class_pri,
750 custgl_class_pri,vendgl_class_pri
751 ,item_pri,customer_pri,
752 vendor_pri,tax_auth_pri,';
753
754 --
755 -- Bug 2031374 - umoogala : Added gl_business_class_pri and gl_product_line_pri
756 -- Bug 2468912 - umoogala : Added line_type
757 -- Bug 2423983 - umoogala : Added ar_trx_type_pri
758 --
759 X_sqlstmt1 := 'charge_pri,currency_code_pri,
760 reason_code_pri,routing_pri,
761 aqui_cost_code_pri,resource_pri,
762 cost_cmpntcls_pri,cost_analysis_pri,
763 order_type_pri, gl_business_class_pri,
764 gl_product_line_pri, line_type_pri, ar_trx_type_pri
765 FROM gl_acct_hrc ';
766
767 /*X_sqlstmt2 := ' WHERE acct_ttl_type = '||to_char(Cur_subevtacct_ttl.acct_ttl_type)||
768 ' AND (co_code = '||''''||v_co_code||''''||' OR co_code IS NULL)'||
769 ' AND delete_mark = 0 ORDER BY 1 desc'; */
770
771 X_sqlstmt2 := ' WHERE acct_ttl_type = :pacct_ttl_type'||
772 ' AND (co_code = :pco_code OR co_code IS NULL) AND delete_mark = 0 ORDER BY 1 desc';
773
774 X_cursor_handle := DBMS_SQL.OPEN_CURSOR;
775
776 DBMS_SQL.PARSE(X_cursor_handle,X_sqlstmt||X_sqlstmt1||X_sqlstmt2,DBMS_SQL.V7);
777
778 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pacct_ttl_type',Cur_subevtacct_ttl.acct_ttl_type);
779 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pco_code',v_co_code);
780
781 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
782 -- Bug 2423983: 22 to 23
783 FOR k IN 2..23 LOOP
784 DBMS_SQL.DEFINE_COLUMN(X_cursor_handle,k,x_tmp1);
785 END LOOP;
786 X_rows_processed := DBMS_SQL.EXECUTE(X_cursor_handle);
787 /*selecting the acct_id in gl_acct_map
788 create the order_by based on the priority retrieved above.
789 company is always the first column selected */
790
791 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
792 -- Bug 2423983: 22 to 23
793 FOR z IN 1..23 LOOP
794 X_order_by(z) := 0;
795 END LOOP;
796
797 X_my_order_by := 'ORDER BY 1 desc';
798
799 IF (dbms_sql.fetch_rows(X_cursor_handle) > 0) THEN
800 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
801 -- Bug 2423983: 22 to 23
802 FOR j IN 2..23 LOOP
803 DBMS_SQL.COLUMN_VALUE(X_cursor_handle,j,x_tmp1);
804 IF (X_tmp1 > 0) THEN
805 x_tmp1:=x_tmp1 + 1;
806 X_order_by(x_tmp1) := j;
807 END IF;
808 END LOOP;
809 END IF;
810
811 -- Bug 2031374 - umoogala : 19 -> 21 for 2 new attributes, Bug 2468912: 21 to 22,
812 -- Bug 2423983: 22 to 23
813 FOR z IN 2..23 LOOP
814 IF (X_order_by(z) > 0) THEN
815 X_my_order_by := X_my_order_by||','||to_char(x_order_by(z))||' desc ';
816 END IF;
817 END LOOP;
818 DBMS_SQL.CLOSE_CURSOR(X_cursor_handle);
819
820 IF (Cur_subevtacct_ttl.map_orgn_ind = 1) THEN
821 X_map_orgn_code := V_orgn_code;
822 X_co_code := X_map_orgn_co;
823 ELSE
824 X_co_code := X_map_whse_co;
825 X_map_orgn_code := X_whse_orgn;
826 END IF;
827
828 /* X_sqlwhere := 'WHERE acct_ttl_type = '||to_char(Cur_subevtacct_ttl.acct_ttl_type)||
829 ' AND co_code = '||''''||NVL(X_co_code,v_co_code)||''''||
830 ' AND (whse_code = '||''''||v_whse_code||''''||' OR whse_code IS NULL)'; */
831
832 X_sqlwhere := 'WHERE acct_ttl_type = :pacct_ttl_type '||
833 ' AND co_code = :pco_code '||
834 ' AND (whse_code = :pwhse_code OR whse_code IS NULL)';
835
836
837
838 /* X_sqlwhere1:= ' AND (orgn_code = '||''''||nvl(X_map_orgn_code,v_orgn_code)||''''||' OR orgn_code IS NULL )'||
839 ' AND (item_id = '||to_char(nvl(v_item_id,0))||' OR item_id IS NULL)'||
840 ' AND (vendor_id = '||to_char(nvl(v_vendor_id,0))||' OR vendor_id IS NULL )'; */
841
842 X_sqlwhere1:= ' AND (orgn_code = :pmap_orgn_code OR orgn_code IS NULL )'||
843 ' AND (item_id = :pitem_id OR item_id IS NULL)'||
844 ' AND (vendor_id = :pvendor_id OR vendor_id IS NULL )';
845
846
847 /* X_sqlwhere2:= ' AND (cust_id = '||to_char(nvl(v_cust_id,0))||' OR cust_id IS NULL)'||
848 ' AND (reason_code = '||''''||v_reason_code||''''||' OR reason_code IS NULL)'||
849 ' AND (icgl_class = '||''''||v_icgl_class||''''||' OR icgl_class IS NULL)'; */
850
851 X_sqlwhere2:= ' AND (cust_id = :pcust_id OR cust_id IS NULL)'||
852 ' AND (reason_code = :preason_code OR reason_code IS NULL)'||
853 ' AND (icgl_class = :picgl_class OR icgl_class IS NULL)';
854
855
856
857 /* X_sqlwhere3:= ' AND (vendgl_class = '||''''||v_vendgl_class||''''||' OR vendgl_class IS NULL)'||
858 ' AND (custgl_class = '||''''||v_custgl_class||''''||' OR custgl_class IS NULL) '; */
859
860
861
862
863 X_sqlwhere3:= ' AND (vendgl_class = :pvendgl_class OR vendgl_class IS NULL)'||
864 ' AND (custgl_class = :pcustgl_class OR custgl_class IS NULL) ';
865
866 /* X_sqlwhere4:= ' AND (currency_code = '||''''||v_currency_code||''''||' OR currency_code IS NULL )'||
867 ' AND (routing_id = '||to_char(nvl(v_routing_id,0))||' OR routing_id IS NULL)'; */
868
869 X_sqlwhere4:= ' AND (currency_code = :pcurrency_code OR currency_code IS NULL )'||
870 ' AND (routing_id = :prouting_id OR routing_id IS NULL)';
871
872
873 /* X_sqlwhere5:= ' AND (charge_id = '||to_char(nvl(v_charge_id,0))||' OR charge_id IS NULL)'||
874 ' AND (taxauth_id = '||to_char(nvl(v_taxauth_id,0))||' OR taxauth_id IS NULL)'||
875 ' AND (aqui_cost_id = '||to_char(nvl(v_aqui_cost_id,0))||' OR aqui_cost_id IS NULL)'; */
876
877
878 X_sqlwhere5:= ' AND (charge_id = :pcharge_id OR charge_id IS NULL)'||
879 ' AND (taxauth_id = :ptaxauth_id OR taxauth_id IS NULL)'||
880 ' AND (aqui_cost_id = :paqui_cost_id OR aqui_cost_id IS NULL)';
881
882
883 /* X_sqlwhere6:= ' AND (resources = '||''''||v_resources||''''||' OR resources IS NULL)'||
884 ' AND (cost_cmpntcls_id = '||to_char(nvl(v_cost_cmpntcls_id,0))||' OR Cost_cmpntcls_id IS NULL)'||
885 ' AND (cost_analysis_code = '||''''||v_cost_analysis_code||''''||' OR cost_analysis_code IS NULL)'; */
886
887
888 X_sqlwhere6:= ' AND (resources = :presources OR resources IS NULL)'||
889 ' AND (cost_cmpntcls_id = :pcost_cmpntcls_id OR cost_cmpntcls_id IS NULL)'||
890 ' AND (cost_analysis_code = :pcost_analysis_code OR cost_analysis_code IS NULL)';
891
892 X_sqlwhere7:= ' AND delete_mark = 0 ';
893
894 /**
895 * RS B1408077 Based on the source passed add condition on the source type column
896 */
897 IF( (v_order_type IS NOT NULL) AND (v_order_type <> 0 ) )
898 THEN
899 IF( v_source = 11 )
900 THEN
901 /* Bug 2431861
902 X_sqlwhere7:= X_sqlwhere7 || ' AND ( source_type = 11 AND (order_type = '||
903 to_char(nvl(v_order_type,0))||' OR order_type IS NULL) ) ';
904
905 X_sqlwhere7:= X_sqlwhere7 || ' AND (( source_type = 11 AND order_type = '||
906 to_char(nvl(v_order_type,0))||')'||' OR order_type IS NULL ) '; */
907
908 X_sqlwhere7:= X_sqlwhere7 || ' AND ((source_type = 11 AND order_type = :porder_type) OR order_type IS NULL ) ';
909
910 ELSE
911 /*X_sqlwhere7:= X_sqlwhere7 || ' AND ( source_type IS NULL AND (order_type = '||to_char(nvl(v_order_type,0))||' OR order_type IS NULL) ) '; */
912
913 X_sqlwhere7:= X_sqlwhere7 || ' AND ((source_type IS NULL AND order_type = :porder_type) OR order_type IS NULL) ';
914 END IF;
915
916 ELSE
917 /* Do it as before */
918 --X_sqlwhere7:= X_sqlwhere7 || ' AND (order_type = '||to_char(nvl(v_order_type,0))||' OR order_type IS NULL) ';
919 X_sqlwhere7:= X_sqlwhere7 || ' AND (order_type = :porder_type OR order_type IS NULL) ';
920 END IF;
921 /* End B1408077 */
922
923 /*
924 * Bug 2031374 - umoogala : Added gl_business_class_pri and gl_product_line_pri
925 * Bug 2468912 - umoogala : Added line_type
926 * Bug 2423983 - umoogala : Added ar_trx_type_id
927 */
928 /* X_sqlwhere7 := X_sqlwhere7 || ' AND (gl_business_class_cat_id = ' || to_char(nvl(v_business_class_cat_id,0)) ||
929 ' OR gl_business_class_cat_id IS NULL) ' ||
930 ' AND (gl_product_line_cat_id = ' || to_char(nvl(v_product_line_cat_id,0)) ||
931 ' OR gl_product_line_cat_id IS NULL) ' ||
932 ' AND (line_type = ' || to_char(nvl(v_line_type,0)) ||
933 ' OR line_type IS NULL) ' ||
934 ' AND (ar_trx_type_id = ' || to_char(nvl(v_ar_trx_type_id,0)) ||
935 ' OR ar_trx_type_id IS NULL) ' ; */
936
937
938 X_sqlwhere7 := X_sqlwhere7 || ' AND (gl_business_class_cat_id = :pbusiness_class_cat_id '||
939 ' OR gl_business_class_cat_id IS NULL) ' ||
940 ' AND (gl_product_line_cat_id = :pproduct_line_cat_id OR gl_product_line_cat_id IS NULL) ' ||
941 ' AND (line_type = :pline_type OR line_type IS NULL) ' ||
942 ' AND (ar_trx_type_id =:par_trx_type_id OR ar_trx_type_id IS NULL) ' ;
943
944
945 /* End Bug 2031374 */
946
947
948 /* Changed the selection of acct_id to be the last column to be
949 selected. If not since the order by is by field position no the
950 correct acct_id doesnot get picked up if there are more than 1 record */
951
952 X_sqlcolumns:= ' SELECT co_code,'||
953 'nvl(orgn_code,'||''''||' '||''''||'),'||
954 'nvl(whse_code,'||''''||' '||''''||'),'||
955 'nvl(icgl_class,'||''''||' '||''''||'),'||
956 'nvl(custgl_class,'||''''||' '||''''||'),'||
957 'nvl(vendgl_class,'||''''||' '||''''||'),';
958
959 X_sqlcolumns1:= 'nvl(item_id,0),'||
960 'nvl(cust_id,0),'||
961 'nvl(vendor_id,0),'||
962 'nvl(taxauth_id,0),'||
963 'nvl(charge_id,0),'||
964 'nvl(currency_code,'||''''||' '||''''||'),'||
965 'nvl(reason_code,'||''''||' '||''''||'),';
966
967 --
968 -- Bug 2468912 - umoogala
969 -- here nvl(line_type,-99) is done to sort the values properly in case of
970 -- ingredients (line_type = -1).
971 -- In case of nvl..0, when we sort, 0 will come first then -1.
972 -- nvl..-99 will put null line type after -1. Other values of line type, 1 and 2,
973 -- will not have any issue as they are > 0
974 --
975 X_sqlcolumns2:= 'nvl(routing_id,0),'||
976 'nvl(aqui_cost_id,0),'||
977 'nvl(resources,'||''''||' '||''''||'),'||
978 'nvl(cost_cmpntcls_id,0),'||
979 'nvl(cost_analysis_code,'||''''||' '||''''||'),'||
980 'nvl(order_type,0),'||
981 'nvl(gl_business_class_cat_id,0), '|| -- Bug 2031374 - umoogala
982 'nvl(gl_product_line_cat_id,0), ' || -- Bug 2031374 - umoogala
983 'nvl(line_type,-99), ' || -- Bug 2468912 - umoogala
984 'nvl(ar_trx_type_id,0), ' || -- Bug 2423983 - umoogala
985 'acct_id ' ;
986
987 X_sqlordby:= X_my_order_by;
988 X_cursor_handle := DBMS_SQL.OPEN_CURSOR;
989
990
991 DBMS_SQL.PARSE(X_cursor_handle,X_sqlcolumns||X_sqlcolumns1||X_sqlcolumns2||' FROM gl_acct_map '||X_sqlwhere||X_sqlwhere1||X_sqlwhere2||X_sqlwhere3||X_sqlwhere4||X_sqlwhere5||X_sqlwhere6||X_sqlwhere7||X_sqlordby,DBMS_SQL.V7);
992 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pacct_ttl_type',Cur_subevtacct_ttl.acct_ttl_type);
993 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pco_code',nvl(x_co_code,v_co_code));
994 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pwhse_code',v_whse_code);
995 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pmap_orgn_code',nvl(X_map_orgn_code,v_orgn_code));
996 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pitem_id',v_item_id);
997 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pvendor_id',v_vendor_id);
998 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcust_id',v_cust_id);
999 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':preason_code',v_reason_code);
1000 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':picgl_class',v_icgl_class);
1001 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pvendgl_class',v_vendgl_class);
1002 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcustgl_class',v_custgl_class);
1003 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcurrency_code',v_currency_code);
1004 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':prouting_id',v_routing_id);
1005 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcharge_id',v_charge_id);
1006 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':ptaxauth_id',v_taxauth_id);
1007 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':paqui_cost_id',v_aqui_cost_id);
1008 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':presources',v_resources);
1009 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcost_cmpntcls_id',v_cost_cmpntcls_id);
1010 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pcost_analysis_code',v_cost_analysis_code);
1011 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':porder_type',v_order_type);
1012 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pBusiness_class_cat_id',v_business_class_cat_id);
1013 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pproduct_line_cat_id',v_product_line_cat_id);
1014 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':pline_type',v_line_type);
1015 DBMS_SQL.BIND_VARIABLE(x_cursor_handle,':par_trx_type_id',v_ar_trx_type_id);
1016
1017 -- Bug 2031374 - umoogala: 20 -> 22 , Bug 2468912: 22 to 23
1018 -- Bug 2423983 - umoogala: 23 -> 24
1019 DBMS_SQL.DEFINE_COLUMN(X_cursor_handle,24,x_num_col);
1020
1021 x_rows_processed := DBMS_SQL.EXECUTE(X_cursor_handle);
1022 IF (dbms_sql.fetch_rows(X_cursor_handle) > 0) THEN
1023 i:= i + 1;
1024 -- Bug 2031374 - umoogala: 20 -> 22, Bug 2468912: 22 to 23
1025 -- Bug 2423983 - umoogala: 23 -> 24
1026 DBMS_SQL.COLUMN_VALUE(X_cursor_handle,24,gmf_get_mappings.my_accounts(i).acct_id);
1027 gmf_get_mappings.my_accounts(i).acct_ttl_type := Cur_subevtacct_ttl.acct_ttl_type;
1028 END IF;
1029 DBMS_SQL.CLOSE_CURSOR(x_cursor_handle);
1030 END LOOP;
1031 no_of_rows := i;
1032 END get_account_mappings;
1033
1034
1035 /* ##################################################
1036 # NAME get_account_value
1037 #
1038 # SYNOPSIS
1039 # Proc get_account_value
1040 # Parms
1041 # DESCRIPTION
1042 # This function returns acct_id value retrieved for
1043 # a particular acct_ttl_type.
1044 ################################################### */
1045
1046
1047 FUNCTION get_account_value(v_acct_ttl_type NUMBER) RETURN NUMBER IS
1048 BEGIN
1049 IF (gmf_get_mappings.no_of_rows > 0) THEN
1050 FOR i IN 1..no_of_rows LOOP
1051 IF (gmf_get_mappings.my_accounts(i).acct_ttl_type = v_acct_ttl_type) THEN
1052 RETURN(nvl(gmf_get_mappings.my_accounts(i).acct_id,0));
1053 END IF;
1054 END LOOP;
1055 ELSE
1056 RETURN(-1); /* No row retrieved.*/
1057 END IF;
1058 RETURN(-1);
1059 END get_account_value;
1060
1061 /*################################################################
1062 # NAME get_opm_segment_values
1063 #
1064 # SYNOPSIS
1065 # Proc get_opm_segment_values
1066 # Parms
1067 # AUTHOR
1068 # Sukarna Reddy Created Dt 25-Jan-2001
1069 # DESCRIPTION
1070 # This functions parses Account value by delimiter and stores
1071 # Individual value segments in to an array and returns the array.
1072 #################################################################### */
1073
1074
1075 FUNCTION get_opm_segment_values(p_account_value IN VARCHAR2,
1076 p_co_code IN VARCHAR2,
1077 p_type IN NUMBER) RETURN my_opm_seg_values IS
1078
1079 CURSOR Cur_get_seg_deli(pco_code VARCHAR2) IS
1080 SELECT segment_delimiter
1081 FROM gl_plcy_mst
1082 WHERE co_code = p_co_code
1083 AND delete_mark = 0;
1084
1085 CURSOR Cur_get_seg_cnttyp(pco_code VARCHAR2,
1086 ptype NUMBER) IS
1087 SELECT COUNT(*)
1088 FROM gl_plcy_seg
1089 WHERE co_code = pco_code
1090 AND type = ptype
1091 AND delete_mark = 0;
1092
1093 CURSOR Cur_get_segment_del(v_co_code VARCHAR2) IS
1094 SELECT segment_delimiter
1095 FROM gl_plcy_mst
1096 WHERE co_code = v_co_code
1097 AND delete_mark = 0;
1098
1099 CURSOR Cur_get_seg_cnt(pco_code VARCHAR2) IS
1100 SELECT COUNT(*)
1101 FROM gl_plcy_seg
1102 WHERE co_code = pco_code
1103 AND delete_mark = 0;
1104
1105 l_account_value gl_acct_mst.acct_no%TYPE;
1106 l_start NUMBER DEFAULT 1;
1107 l_end NUMBER DEFAULT 0;
1108 l_deli_process NUMBER DEFAULT 0;
1109 l_delimiter_cnt NUMBER DEFAULT 0;
1110 l_count NUMBER DEFAULT 0;
1111 l_opm_seg_values my_opm_seg_values;
1112 l_segment_delimiter gl_plcy_mst.segment_delimiter%TYPE;
1113 l_acct_no gl_acct_mst.acct_no%TYPE;
1114 BEGIN
1115
1116 -- Fetch Segment delimiter
1117 OPEN Cur_get_seg_deli(p_co_code);
1118 FETCH Cur_get_seg_deli INTO l_segment_delimiter;
1119 CLOSE Cur_get_seg_deli;
1120
1121 -- Get Count of no of segments
1122 OPEN Cur_get_seg_cnt(p_co_code);
1123 FETCH Cur_get_seg_cnt INTO l_count;
1124 CLOSE Cur_get_seg_cnt;
1125
1126 -- Initialize empty rows in an array.
1127 FOR i IN 1..l_count LOOP
1128 l_opm_seg_values(i) := NULL;
1129 END LOOP;
1130
1131 -- Get Count of segments based on type.
1132 IF (p_type IN (0,1)) THEN
1133 OPEN Cur_get_seg_cnttyp(p_co_code,p_type);
1134 FETCH Cur_get_seg_cnttyp INTO l_count;
1135 CLOSE Cur_get_seg_cnttyp;
1136 END IF;
1137
1138 -- Delimiter count should always be less by one then total count of segments.
1139 l_delimiter_cnt := l_count - 1;
1140
1141 /*The variable below is used only to identify last segment
1142 after the last delimiter is processed. */
1143
1144 l_deli_process := 1;
1145
1146 l_start := 1; /* Stores the starting position of the segment value in the string. */
1147 l_acct_no := p_account_value;
1148
1149 FOR i IN 1..l_count LOOP
1150 -- Condition to ensure there are no other delimiters to be considered.
1151 IF (l_deli_process <= l_delimiter_cnt) THEN
1152 l_end := instr(l_acct_no,l_segment_delimiter,1);
1153
1154 l_account_value := SUBSTR(l_acct_no,l_start,l_end - 1);
1155 l_acct_no := SUBSTR(l_acct_no,l_end+1);
1156 l_opm_seg_values(i) := l_account_value;
1157 l_deli_process := l_deli_process + 1;
1158 ELSE
1159 l_account_value := SUBSTR(l_acct_no,l_start);
1160 l_opm_seg_values(i) := l_account_value;
1161 END IF;
1162 END LOOP;
1163 RETURN (l_opm_seg_values);
1164 END get_opm_segment_values;
1165
1166 /* ################################################################
1167 # NAME parse_ccid
1168 #
1169 # DESCRIPTION
1170 # Function to return au and acct from ccid
1171 # HISTORY
1172 # 16-Mar-2001 Rajesh Seshadri Bug 1763233 - Common Receiving
1173 # 21-Sep-2001 Rajesh Seshadri Bug 1801417 - retrieve
1174 # segment description also for the accu/acct descriptions
1175 # 12-Aug-2002 Rajesh Seshadri Bug 2485772 - get the account
1176 # uom (non-null segment uom) and insert into account master
1177 # Update accu-desc, acct-desc, acct-uom if needed.
1178 ################################################################# */
1179 FUNCTION parse_ccid(
1180 pi_co_code IN gl_plcy_mst.co_code%TYPE,
1181 pi_code_combination_id IN NUMBER,
1182 pi_create_acct IN NUMBER DEFAULT 1)
1183 RETURN opm_account
1184 AS
1185 -- Dependencies:
1186 --
1187 -- segment_num order returned by FND_FLEX_EXT api
1188 -- is in ascending order of segment_num in GL
1189 --
1190 -- OPM does not allow account to be defined before accounting units
1191 -- segment_no and segment_ref are display only fields in the application
1192 -- and the segment_no in gl_plcy_seg is always put in ascending order
1193 --
1194 -- Can OPM have a subset of GL segments and others in GL might be
1195 -- disabled?
1196 --
1197
1198 l_code_combination_id NUMBER;
1199 l_n_gl_segs NUMBER(5);
1200 l_gl_segs fnd_flex_ext.SegmentArray;
1201 l_dummy BOOLEAN;
1202 l_flex_code VARCHAR2(32) := 'GL#';
1203 l_app_name VARCHAR2(32) := 'SQLGL';
1204 l_segment_num NUMBER;
1205
1206 l_user_id NUMBER;
1207
1208 l_chart_of_accounts_id number(15);
1209 l_opm_company sy_orgn_mst.orgn_code%TYPE;
1210 l_opm_accu_id gl_accu_mst.acctg_unit_id%TYPE;
1211 l_opm_acct_id gl_acct_mst.acct_id%TYPE;
1212
1213 l_opm_delimiter gl_plcy_mst.segment_delimiter%TYPE;
1214 l_n_accu NUMBER(5);
1215 l_n_acct NUMBER(5);
1216 l_opm_accu VARCHAR2(255);
1217 l_opm_accu_desc VARCHAR2(2000);
1218 l_opm_acct VARCHAR2(255);
1219 l_opm_acct_desc VARCHAR2(2000);
1220
1221 l_opm_account opm_account;
1222 l_opm_account_err opm_account;
1223
1224 TYPE rectype_opm_seg IS RECORD(
1225 segment_no NUMBER(5),
1226 short_name gl_plcy_seg.short_name%TYPE,
1227 type gl_plcy_seg.type%TYPE,
1228 segment_ref gl_plcy_seg.segment_ref%TYPE,
1229 gl_seg_val varchar2(255),
1230 segment_desc fnd_flex_values_vl.description%TYPE
1231 );
1232
1233 TYPE tabtype_gl_plcy_seg IS TABLE OF rectype_opm_seg
1234 INDEX BY BINARY_INTEGER;
1235
1236 lt_gl_plcy_seg tabtype_gl_plcy_seg;
1237
1238 CURSOR cur_opm_plcy_seg(p_opm_company gl_plcy_mst.co_code%TYPE) IS
1239 SELECT
1240 segment_no,
1241 short_name,
1242 type,
1243 nvl(segment_ref, 0) segment_ref
1244 FROM gl_plcy_seg
1245 WHERE
1246 co_code = p_opm_company
1247 ORDER BY segment_ref;
1248
1249 l_opm_seg_count NUMBER;
1250 l_seg_count2 NUMBER;
1251
1252 CURSOR cur_seg_num(
1253 p_app_id NUMBER, p_flex_code VARCHAR2,
1254 p_chart_of_accounts_id NUMBER, p_seg_name VARCHAR2)
1255 IS
1256 SELECT segment_num
1257 FROM fnd_id_flex_segments
1258 WHERE
1259 application_id = p_app_id
1260 AND id_flex_code = p_flex_code
1261 AND id_flex_num = p_chart_of_accounts_id
1262 AND segment_name = p_seg_name;
1263
1264 CURSOR cur_opm_accu(
1265 p_co_code sy_orgn_mst.orgn_code%TYPE,
1266 p_acctg_unit_no gl_accu_mst.acctg_unit_no%TYPE
1267 )
1268 IS
1269 SELECT
1270 acctg_unit_id, acctg_unit_desc
1271 FROM
1272 gl_accu_mst
1273 WHERE
1274 co_code = p_co_code
1275 AND acctg_unit_no = p_acctg_unit_no;
1276
1277 CURSOR cur_opm_acct(
1278 p_co_code sy_orgn_mst.orgn_code%TYPE,
1279 p_acct_no gl_acct_mst.acct_no%TYPE
1280 )
1281 IS
1282 SELECT
1283 acct_id, acct_desc, quantity_um
1284 FROM
1285 gl_acct_mst
1286 WHERE
1287 co_code = p_co_code
1288 AND acct_no = p_acct_no;
1289
1290 -- exceptions
1291 e_segment_not_found EXCEPTION;
1292 e_segment_setup_error EXCEPTION;
1293 e_incorrect_type EXCEPTION;
1294 e_accu_not_found EXCEPTION;
1295 e_acct_not_found EXCEPTION;
1296
1297 -- for Seg. Value descriptions
1298 l_startdate fnd_flex_values.start_date_active%TYPE;
1299 l_enddate fnd_flex_values.end_date_active%TYPE;
1300 l_sobname gl_plcy_mst.set_of_books_name%TYPE;
1301 l_segmentname fnd_id_flex_segments.segment_name%TYPE;
1302 l_segmentnum fnd_id_flex_segments.segment_num%TYPE;
1303 l_segmentval fnd_flex_values.flex_value%TYPE;
1304 l_segmentdesc fnd_flex_values_vl.description%TYPE;
1305 l_row_to_fetch NUMBER DEFAULT 1;
1306 l_statuscode NUMBER DEFAULT 0;
1307 l_segmentuom gl_stat_account_uom.unit_of_measure%TYPE;
1308
1309 -- RS B2485772
1310 l_acct_uom gl_acct_mst.quantity_um%TYPE := null;
1311 l_opm_db_acct_desc gl_acct_mst.acct_desc%TYPE;
1312 l_opm_db_acct_uom gl_acct_mst.quantity_um%TYPE;
1313 l_opm_db_accu_desc gl_accu_mst.acctg_unit_desc%TYPE;
1314
1315 BEGIN
1316
1317 l_opm_account_err.acctg_unit_id := -1;
1318 l_opm_account_err.acct_id := -1;
1319
1320 l_user_id := FND_GLOBAL.USER_ID;
1321
1322 -- Set the input values
1323 l_code_combination_id := pi_code_combination_id;
1324 l_opm_company := pi_co_code;
1325
1326 -- Get the chart of accounts id
1327 SELECT
1328 sob.chart_of_accounts_id
1329 INTO
1330 l_chart_of_accounts_id
1331 FROM
1332 gl_sets_of_books sob,
1333 gl_plcy_mst plc
1334 WHERE
1335 sob.set_of_books_id = plc.sob_id
1336 AND plc.co_code = l_opm_company;
1337
1338 -- Call get_segments()
1339 IF( fnd_flex_ext.get_segments(l_app_name, l_flex_code,
1340 l_chart_of_accounts_id, l_code_combination_id,
1341 l_n_gl_segs, l_gl_segs) = FALSE )
1342 THEN
1343 RAISE e_segment_not_found;
1344 ELSE
1345 FOR i IN 1..l_n_gl_segs LOOP
1346 null;
1347 END LOOP;
1348
1349 END IF;
1350
1351 -- get opm segment delimiter
1352 SELECT segment_delimiter, set_of_books_name
1353 INTO l_opm_delimiter, l_sobname
1354 FROM gl_plcy_mst
1355 WHERE co_code = l_opm_company;
1356
1357 SELECT count(*) INTO l_opm_seg_count
1358 FROM gl_plcy_seg
1359 WHERE co_code = l_opm_company;
1360
1361 IF( l_opm_seg_count <> l_n_gl_segs )
1362 THEN
1363 RAISE e_segment_setup_error;
1364 END IF;
1365
1366
1367 l_seg_count2 := 0;
1368 FOR r_seg IN cur_opm_plcy_seg(l_opm_company)
1369 LOOP
1370 IF( r_seg.segment_ref = 0 )
1371 THEN
1372 RAISE e_segment_setup_error;
1373 END IF;
1374
1375 l_seg_count2 := l_seg_count2 + 1;
1376
1377
1378 lt_gl_plcy_seg(r_seg.segment_no).segment_no := r_seg.segment_no;
1379 lt_gl_plcy_seg(r_seg.segment_no).short_name := r_seg.short_name;
1380 lt_gl_plcy_seg(r_seg.segment_no).type := r_seg.type;
1381 lt_gl_plcy_seg(r_seg.segment_no).segment_ref := r_seg.segment_ref;
1382
1383 lt_gl_plcy_seg(r_seg.segment_no).gl_seg_val := l_gl_segs(l_seg_count2);
1384
1385 -- Get the segment description
1386 l_startdate := NULL;
1387 l_enddate := NULL;
1388 l_segmentname := NULL;
1389 l_segmentnum := r_seg.segment_ref;
1390 l_segmentval := l_gl_segs(l_seg_count2);
1391 l_segmentdesc := NULL;
1392 l_row_to_fetch := 1;
1393 l_statuscode := NULL;
1394 l_segmentuom := NULL;
1395
1396 BEGIN
1397 gmf_fnd_get_segment_val.proc_get_segment_val(
1398 l_startdate,
1399 l_enddate,
1400 l_sobname,
1401 l_segmentname,
1402 l_segmentnum,
1403 l_segmentval,
1404 l_segmentdesc,
1405 l_row_to_fetch,
1406 l_statuscode,
1407 l_segmentuom
1408 );
1409
1410 lt_gl_plcy_seg(r_seg.segment_no).segment_desc := l_segmentdesc;
1411
1412 -- RS B2485772
1413 IF( l_segmentuom IS NOT NULL )
1414 THEN
1415 IF( LENGTHB(l_segmentuom) <= 4 )
1416 THEN
1417 l_acct_uom := l_segmentuom;
1418 END IF;
1419 END IF;
1420
1421 EXCEPTION
1422 WHEN others THEN
1423 lt_gl_plcy_seg(r_seg.segment_no).segment_desc := l_gl_segs(l_seg_count2);
1424 END;
1425
1426 END LOOP;
1427
1428 -- construct the OPM accounting unit and account
1429
1430 l_n_accu := 0;
1431 l_n_acct := 0;
1432 l_opm_accu := NULL;
1433 l_opm_accu_desc := NULL;
1434 l_opm_acct := NULL;
1435 l_opm_acct_desc := NULL;
1436
1437 FOR opm_seg_idx IN 1..l_n_gl_segs
1438 LOOP
1439
1440 IF( lt_gl_plcy_seg(opm_seg_idx).type = 0 )
1441 THEN
1442 -- it's an accu
1443 l_n_accu := l_n_accu + 1;
1444 IF( l_n_accu > 1 )
1445 THEN
1446 l_opm_accu := l_opm_accu || l_opm_delimiter || lt_gl_plcy_seg(opm_seg_idx).gl_seg_val;
1447 l_opm_accu_desc := l_opm_accu_desc || l_opm_delimiter || lt_gl_plcy_seg(opm_seg_idx).segment_desc;
1448 ELSE
1449 l_opm_accu := lt_gl_plcy_seg(opm_seg_idx).gl_seg_val;
1450 l_opm_accu_desc := lt_gl_plcy_seg(opm_seg_idx).segment_desc;
1451 END IF;
1452
1453 ELSE
1454 -- it's an acct
1455 l_n_acct := l_n_acct + 1;
1456 IF( l_n_acct > 1 )
1457 THEN
1458 l_opm_acct := l_opm_acct || l_opm_delimiter || lt_gl_plcy_seg(opm_seg_idx).gl_seg_val;
1459 l_opm_acct_desc := l_opm_acct_desc || l_opm_delimiter || lt_gl_plcy_seg(opm_seg_idx).segment_desc;
1460 ELSE
1461 l_opm_acct := lt_gl_plcy_seg(opm_seg_idx).gl_seg_val;
1462 l_opm_acct_desc := lt_gl_plcy_seg(opm_seg_idx).segment_desc;
1463 END IF;
1464 END IF;
1465
1466 END LOOP;
1467
1468 l_opm_accu_desc := SUBSTRB(l_opm_accu_desc, 1, 70);
1469 l_opm_acct_desc := SUBSTRB(l_opm_acct_desc, 1, 70);
1470
1471
1472 l_n_accu := 0;
1473 l_n_acct := 0;
1474
1475 OPEN cur_opm_accu(l_opm_company, l_opm_accu);
1476 FETCH cur_opm_accu INTO l_opm_accu_id, l_opm_db_accu_desc;
1477 IF( cur_opm_accu%NOTFOUND )
1478 THEN
1479 -- if accounting unit is not to be created then error out
1480 IF( pi_create_acct = 0 )
1481 THEN
1482 CLOSE cur_opm_accu;
1483 RAISE e_accu_not_found;
1484 END IF;
1485
1486 SELECT gem5_acctg_id_s.NEXTVAL
1487 INTO l_opm_accu_id
1488 FROM dual;
1489
1490 -- insert the accounting unit into OPM
1491 INSERT INTO gl_accu_mst(
1492 ACCTG_UNIT_ID,
1493 ACCTG_UNIT_NO,
1494 CO_CODE,
1495 ACCTG_UNIT_DESC,
1496 START_DATE,
1497 END_DATE,
1498 CREATION_DATE,
1499 CREATED_BY,
1500 LAST_UPDATE_DATE,
1501 LAST_UPDATED_BY,
1502 LAST_UPDATE_LOGIN,
1503 TRANS_CNT,
1504 TEXT_CODE,
1505 DELETE_MARK
1506 )
1507 VALUES
1508 (
1509 l_opm_accu_id,
1510 l_opm_accu,
1511 l_opm_company,
1512 l_opm_accu_desc,
1513 NULL, -- start_date
1514 NULL, -- end_date
1515 SYSDATE,
1516 l_user_id,
1517 SYSDATE,
1518 l_user_id,
1519 NULL, -- last_update_login
1520 0, -- trans_cnt
1521 NULL, -- text_code
1522 0 -- delete_mark
1523 );
1524 ELSE
1525 -- accu found, update desc if necessary
1526 IF( (l_opm_db_accu_desc <> l_opm_accu_desc) OR
1527 (l_opm_db_accu_desc IS NULL AND l_opm_accu_desc IS NOT NULL) OR
1528 (l_opm_db_accu_desc IS NOT NULL AND l_opm_accu_desc IS NULL) )
1529 THEN
1530 UPDATE gl_accu_mst
1531 SET
1532 acctg_unit_desc = l_opm_accu_desc
1533 WHERE
1534 co_code = l_opm_company AND
1535 acctg_unit_id = l_opm_accu_id
1536 ;
1537 END IF;
1538 END IF;
1539 CLOSE cur_opm_accu;
1540
1541 OPEN cur_opm_acct(l_opm_company, l_opm_acct);
1542 FETCH cur_opm_acct INTO l_opm_acct_id, l_opm_db_acct_desc, l_opm_db_acct_uom;
1543 IF( cur_opm_acct%NOTFOUND )
1544 THEN
1545
1546 -- if account is not to be created then error out
1547 IF( pi_create_acct = 0 )
1548 THEN
1549 CLOSE cur_opm_acct;
1550 RAISE e_acct_not_found;
1551 END IF;
1552
1553 SELECT gem5_acct_id_s.NEXTVAL
1554 INTO l_opm_acct_id
1555 FROM dual;
1556
1557 -- insert the account into OPM
1558 INSERT INTO gl_acct_mst(
1559 ACCT_ID,
1560 ACCT_NO,
1561 CO_CODE,
1562 ACCT_DESC,
1563 ACCT_TYPE_CODE,
1564 ACCT_CLASS_CODE,
1565 ACCT_USAGE_CODE,
1566 ACCT_BAL_TYPE,
1567 SUMMARY_IND,
1568 QTY_IND,
1569 QUANTITY_UM,
1570 START_DATE,
1571 END_DATE,
1572 CREATION_DATE,
1573 CREATED_BY,
1574 LAST_UPDATE_DATE,
1575 LAST_UPDATED_BY,
1576 LAST_UPDATE_LOGIN,
1577 TRANS_CNT,
1578 TEXT_CODE,
1579 DELETE_MARK
1580 )
1581 VALUES
1582 (
1583 l_opm_acct_id,
1584 l_opm_acct,
1585 l_opm_company,
1586 l_opm_acct_desc,
1587 NULL, -- type
1588 NULL, -- class
1589 NULL, -- usage
1590 0, -- acct_bal_type
1591 0, -- summary_ind
1592 0, -- qty_ind
1593 l_acct_uom, -- qty_um
1594 NULL, -- start_date
1595 NULL, -- end_date
1596 SYSDATE, -- creation date
1597 l_user_id, -- created by
1598 SYSDATE, -- last update date
1599 l_user_id, -- last updated by
1600 NULL, -- last update login
1601 0, -- trans cnt
1602 NULL, -- text code
1603 0 -- delete_mark
1604 );
1605 ELSE
1606 -- acct found. check desc and uom and update if necessary
1607 IF( (l_opm_db_acct_uom <> l_acct_uom) OR
1608 (l_opm_db_acct_uom IS NOT NULL AND l_acct_uom IS NULL) OR
1609 (l_opm_db_acct_uom IS NULL AND l_acct_uom IS NOT NULL) OR
1610 (l_opm_db_acct_desc <> l_opm_acct_desc) OR
1611 (l_opm_db_acct_desc IS NOT NULL AND l_opm_acct_desc IS NULL) OR
1612 (l_opm_db_acct_desc IS NULL AND l_opm_acct_desc IS NOT NULL) )
1613 THEN
1614 UPDATE gl_acct_mst
1615 SET
1616 acct_desc = l_opm_acct_desc,
1617 quantity_um = l_acct_uom
1618 WHERE
1619 co_code = l_opm_company AND
1620 acct_id = l_opm_acct_id
1621 ;
1622 END IF;
1623 END IF;
1624 CLOSE cur_opm_acct;
1625
1626 l_opm_account.acctg_unit_id := l_opm_accu_id;
1627 l_opm_account.acct_id := l_opm_acct_id;
1628
1629 RETURN l_opm_account;
1630
1631 EXCEPTION
1632 WHEN e_accu_not_found THEN
1633 RETURN l_opm_account_err;
1634 WHEN e_acct_not_found THEN
1635 RETURN l_opm_account_err;
1636 WHEN e_segment_not_found THEN
1637 RETURN l_opm_account_err;
1638 WHEN e_segment_setup_error THEN
1639 RETURN l_opm_account_err;
1640 WHEN e_incorrect_type THEN
1641 RETURN l_opm_account_err;
1642 WHEN others THEN
1643 RETURN l_opm_account_err;
1644
1645 END parse_ccid;
1646
1647 END GMF_GET_MAPPINGS;