[Home] [Help]
PACKAGE BODY: APPS.GML_ACCT_GENERATE
Source
1 PACKAGE BODY GML_ACCT_GENERATE AS
2 /* $Header: GMLACTGB.pls 115.6 2003/12/09 18:01:10 uphadtar noship $ */
3
4 -- global variables
5 g_location VARCHAR2(255) := NULL;
6 g_debug VARCHAR2(10) := NVL(fnd_profile.value('GML_PO_LOG'),'0');
7
8 -- /*************************************************************************
9 -- # PROC
10 -- # PrintLine
11 -- #
12 -- # INPUT PARAMETERS
13 -- # string
14 -- # DESCRIPTION
15 -- # Procedure to write the debug log.
16 -- #
17 -- #
18 -- #**************************************************************************/
19 PROCEDURE PrintLn( p_msg IN VARCHAR2 ) IS
20
21 CURSOR get_log_file_location IS
22 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
23 FROM v$parameter
24 WHERE name = 'utl_file_dir';
25
26
27 l_log UTL_FILE.file_type;
28 l_file_name VARCHAR2(80);
29
30 BEGIN
31
32 /* always write to GMLPOLOG */
33 l_file_name := 'GMLPOLOG';
34
35 IF (g_debug <> '1') THEN
36 RETURN;
37 ELSE
38 -- file name is GMLPOLOG.userid
39 l_file_name := l_file_name||'.'||FND_GLOBAL.user_id;
40
41 IF g_location is NULL THEN
42 OPEN get_log_file_location;
43 FETCH get_log_file_location into g_location;
44 CLOSE get_log_file_location;
45 END IF;
46
47 l_log := UTL_FILE.fopen(g_location, l_file_name, 'a');
48 IF UTL_FILE.IS_OPEN(l_log) THEN
49 UTL_FILE.put_line(l_log, p_msg);
50 UTL_FILE.fflush(l_log);
51 UTL_FILE.fclose(l_log);
52 END IF;
53 END IF;
54
55 EXCEPTION
56
57 WHEN OTHERS THEN
58 NULL;
59
60 END PrintLn;
61
62 -- /*************************************************************************
63 -- # PROC
64 -- # initialize_variables
65 -- #
66 -- # INPUT PARAMETERS
67 -- #
68 -- # DESCRIPTION
69 -- # Initialize Package variables
70 -- #
71 -- #
72 -- #**************************************************************************/
73 PROCEDURE initialize_variables as
74 BEGIN
75
76 /* Initialize Package Variables.*/
77 P_itemglclass := NULL;
78 P_acctg_unit_id := NULL;
79 P_base_currency := NULL;
80 P_vend_gl_class := NULL;
81 P_whse_co_code := NULL;
82 P_whse_orgn_code := NULL;
83 P_cust_id := NULL;
84 P_reason_code := NULL;
85 P_cust_gl_class := NULL;
86 P_routing_id := NULL;
87 P_charge_id := NULL;
88 P_taxauth_id := NULL;
89 P_aqui_cost_id := NULL;
90 P_resources := NULL;
91 P_order_type := NULL;
92 P_shipvend_id := NULL;
93 P_to_whse := NULL;
94 P_item_no := NULL;
95 P_gl_item_id := NULL;
96 P_acct_id := NULL;
97 P_acctg_unit_no := NULL;
98 P_acct_no := NULL;
99 P_acct_desc := NULL;
100 P_acct_ttl_num := NULL;
101 P_cc_id := NULL;
102 P_gl_business_class_cat_id := NULL; /* B2312653 RVK */
103 P_gl_product_line_cat_id := NULL; /* B2312653 RVK */
104
105 END;
106
107 --/*************************************************************************
108 --# PROC
109 --# generate_opm_acct
110 --#
111 --# INPUT PARAMETERS
112 --# v_destination_type VARCHAR2 . It can be 'INVENTORY', 'EXPENSE', 'ACCRUAL'
113 --# v_inv_item_type VARCHAR2. For Inventory destination type values can be
114 --# either 'ASSET' or 'EXPENSE'. NULL for other destination types.
115 --# v_subinv_type VARCHAR2. For Inventory destination type values can be
116 --# either 'ASSET' or 'EXPENSE'. NULL for other destination types.
117 --# v_dest_org_id VARCHAR2. Apps organization id which is shipment org. id.
118 --# v_apps_item_id NUMBER. Application Item Id.
119 --# v_vendor_site_id NUMBER. Vendor site id on the Purchase order
120 --#
121 --# IN OUT PARAMETERS
122 --# v_cc_id NUMBER. Code combination id returned for OPM account.
123 --#
124 --# DESCRIPTION
125 --# This procedure is called from the workflow functions to retrive the code
126 --# combination id for the OPM account. This is a wrapper procedure for main
127 --# procedure get_opm_account.
128 --#
129 --#**************************************************************************/
130 PROCEDURE generate_opm_acct(v_destination_type VARCHAR2,
131 v_inv_item_type VARCHAR2, v_subinv_type VARCHAR2
132 ,
133 v_dest_org_id NUMBER, v_apps_item_id NUMBER,
134 v_vendor_site_id NUMBER,
135 v_cc_id IN OUT NOCOPY NUMBER) AS
136 x_opm_account_type VARCHAR2(10) := NULL;
137 x_retcode NUMBER := 0;
138 BEGIN
139
140 PrintLn('/**************************************/');
141 PrintLn('begin of generate_opm_acct');
142 PrintLn('v_destination_type = '||v_destination_type);
143 PrintLn('v_inv_item_type = '||v_inv_item_type);
144 PrintLn('v_subinv_type = '||v_subinv_type);
145 PrintLn('v_dest_org_id = '||v_dest_org_id);
146 PrintLn('v_apps_item_id = '||v_apps_item_id);
147 PrintLn('v_vendor_site_id = '||v_vendor_site_id);
148
149 PrintLn('calling get_opm_account_type');
150
151 /* Get OPM Account type. 'INVENTORY','EXPENSE' or 'ACCRUAL' */
152 GML_ACCT_GENERATE.get_opm_account_type(v_destination_type, v_inv_item_type,v_subinv_type,
153 x_opm_account_type);
154
155 PrintLn('finished calling get_opm_account_type, x_opm_account_type = '||x_opm_account_type);
156
157 If (x_opm_account_type is null ) then
158 v_cc_id := NULL;
159 return;
160 end if;
161
162 /* Initialize global variables */
163 initialize_variables;
164
165 PrintLn('calling get_opm_account');
166
167 /* Get code combination id for OPM account */
168 GML_ACCT_GENERATE.get_opm_account(v_dest_org_id, v_apps_item_id, v_vendor_site_id,
169 x_opm_account_type, x_retcode);
170
171 PrintLn('finished calling get_opm_account');
172 PrintLn('x_retcode = '||to_char(x_retcode));
173
174 If x_retcode = 0
175 then
176 v_cc_id := P_cc_id;
177 else
178 v_cc_id := NULL;
179 end if;
180
181 PrintLn('v_cc_id = '||to_char(v_cc_id));
182 PrintLn('end of generate_opm_acct');
183
184 END generate_opm_acct;
185
186
187 --/*************************************************************************
188 --# PROC
189 --# get_opm_account
190 --#
191 --# INPUT PARAMETERS
192 --# v_dest_org_id VARCHAR2. Apps organization id which is shipment org. id.
193 --# v_apps_item_id NUMBER. Application Item Id.
194 --# v_vendor_site_id NUMBER. Vendor site id on the Purchase order
195 --# v_opm_account_type VARCHAR2 . It can be 'INVENTORY', 'EXPENSE', 'ACCRUAL'
196 --#
197 --# IN OUT PARAMETERS
198 --# retcode NUMBER. SUCCESS = 0 ; ERROR otherwise.
199 --#
200 --# DESCRIPTION
201 --# Main routine for getting code combination id. Called from generate_opm_acct
202 --#
203 --#**************************************************************************/
204 PROCEDURE get_opm_account(v_dest_org_id NUMBER, v_apps_item_id NUMBER,
205 v_vendor_site_id NUMBER, v_opm_account_type VARCHAR2,
206 retcode IN OUT NOCOPY NUMBER) AS
207 x_base_currency gl_curr_mst.CURRENCY_CODE%TYPE;
208 x_item_gl_class ic_item_mst.GL_CLASS%TYPE;
209 x_vend_gl_class po_vend_mst.VENDGL_CLASS%TYPE;
210 x_whse_co_code sy_orgn_mst.co_code%TYPE;
211 x_whse_orgn_code sy_orgn_mst.orgn_code%TYPE;
212 x_shipvend_id NUMBER;
213 x_item_no ic_item_mst.item_no%TYPE;
214 x_gl_item_id ic_item_mst.item_id%TYPE;
215 x_to_whse ic_whse_mst.whse_code%TYPE;
216
217 Cursor cur_opm_item IS
218 Select mst.item_no , mst.item_id, mst.gl_class
219 from ic_item_mst mst, mtl_system_items mtl
220 where mtl.inventory_item_id = v_apps_item_id and
221 mtl.organization_id = v_dest_org_id and
222 mtl.segment1 = mst.item_no;
223
224 Cursor cur_opm_orgn IS
225 Select mst.co_code, whse.orgn_code, whse.whse_code
226 from sy_orgn_mst mst, ic_whse_mst whse
227 where whse.MTL_ORGANIZATION_ID = v_dest_org_id and
228 whse.orgn_code = mst.orgn_code ;
229
230 Cursor cur_base_curr is
231 Select plcy.base_currency_code
232 from sy_orgn_mst orgn, gl_plcy_mst plcy
233 where orgn.orgn_code = x_whse_orgn_code and orgn.co_code = plcy.co_code;
234
235 Cursor cur_opm_vend is
236 Select vendor_id, vendgl_class from po_vend_mst
237 where of_vendor_site_id = v_vendor_site_id;
238
239 /* Bug 2312653 RVK */
240 Cursor category_ids(p_item_id ic_item_mst.item_id%TYPE)
241 IS
242 SELECT gic.item_id, gcs.opm_class, gic.category_id
243 FROM gmi_category_sets gcs, gmi_item_categories gic
244 WHERE gic.item_id = p_item_id
245 AND gic.category_set_id = gcs.category_set_id
246 AND gcs.category_set_id IS NOT NULL
247 AND gcs.opm_class in ('GL_BUSINESS_CLASS', 'GL_PRODUCT_LINE');
248
249 x_gltitles number ;
250
251 BEGIN
252
253 PrintLn('begin of get_opm_account');
254
255 Open cur_opm_item;
256 Fetch cur_opm_item into x_item_no, x_gl_item_id, x_item_gl_class;
257 Close cur_opm_item;
258
259 Open cur_opm_orgn;
260 Fetch cur_opm_orgn into x_whse_co_code, x_whse_orgn_code, x_to_whse;
261 Close cur_opm_orgn;
262
263 Open cur_base_curr;
264 Fetch cur_base_curr into x_base_currency;
265 Close cur_base_curr;
266
267 Open cur_opm_vend;
268 Fetch cur_opm_vend into x_shipvend_id, x_vend_gl_class;
269 Close cur_opm_vend;
270
271 P_item_no := x_item_no;
272 P_gl_item_id := x_gl_item_id;
273 P_itemglclass := x_item_gl_class;
274 P_whse_co_code := x_whse_co_code;
275 P_whse_orgn_code := x_whse_orgn_code;
276 P_to_whse := x_to_whse;
277 P_base_currency := x_base_currency;
278 P_shipvend_id := x_shipvend_id;
279 P_vend_gl_class := x_vend_gl_class;
280
281 /* Bug 2312653 RVK */
282 IF x_gl_item_id > 0 THEN
283 FOR cur_category_ids in category_ids(x_gl_item_id)
284 LOOP
285 IF cur_category_ids.opm_class = 'GL_BUSINESS_CLASS' THEN
286 P_gl_business_class_cat_id := cur_category_ids.category_id;
287 ELSIF cur_category_ids.opm_class = 'GL_PRODUCT_LINE' THEN
288 P_gl_product_line_cat_id := cur_category_ids.category_id;
289 END IF;
290 END LOOP;
291 ELSE
292 P_gl_business_class_cat_id := NULL;
293 P_gl_product_line_cat_id := NULL;
294 END IF;
295 /* End Bug 2312653 */
296
297 PrintLn('calling get_acct_title');
298
299 /* Get account title number for the OPM account type */
300 GML_ACCT_GENERATE.get_acct_title (v_opm_account_type,
301 x_gltitles);
302
303 PrintLn('finished calling get_acct_title');
304 PrintLn('account title number(X_gltitles) = '||to_char(X_gltitles));
305
306 P_acct_ttl_num := X_gltitles;
307 P_cost_cmpntcls_id := NULL;
308 P_cost_analysis_code := NULL;
309
310 PrintLn('calling process_trans');
311
312 /* Process for the OPM account */
313 GML_ACCT_GENERATE.process_trans (retcode);
314
315 PrintLn('finished calling process_trans');
316 PrintLn('end of get_opm_account');
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 retcode := 1;
321 END get_opm_account;
322
323
324 --/*************************************************************************
325 --# PROC
326 --# get_opm_account_type
327 --#
328 --# INPUT PARAMETERS
329 --# v_destination_type VARCHAR2 . It can be 'INVENTORY', 'EXPENSE', 'ACCRUAL'
330 --# v_inv_item_type VARCHAR2. For Inventory destination type values can be
331 --# either 'ASSET' or 'EXPENSE'. NULL for other destination types.
332 --# v_subinv_type VARCHAR2. For Inventory destination type values can be
333 --# either 'ASSET' or 'EXPENSE'. NULL for other destination types.
334 --#
335 --# OUT PARAMETERS
336 --# v_opm_account_type VARCHAR2 . It can be 'INVENTORY', 'EXPENSE', 'ACCRUAL'
337 --#
338 --# DESCRIPTION
339 --# Determines OPM account type for the inputs from the workflow destination types.
340 --#
341 --#**************************************************************************/
342 PROCEDURE get_opm_account_type(v_destination_type VARCHAR2,
343 v_inv_item_type VARCHAR2, v_subinv_type VARCHAR2,
344 v_opm_account_type OUT NOCOPY VARCHAR2) AS
345 BEGIN
346 if (v_destination_type = 'INVENTORY')
347 then
348 if (v_inv_item_type = 'EXPENSE') then
349 v_opm_account_type := 'EXPENSE';
350 elsif (v_inv_item_type = 'ASSET') then
351 if (v_subinv_type = 'ASSET' or v_subinv_type is null)
352 then
353 v_opm_account_type := 'INVENTORY';
354 elsif (v_subinv_type = 'EXPENSE') then
355 v_opm_account_type := 'EXPENSE';
356 end if;
357 end if;
358 elsif (v_destination_type = 'EXPENSE') then
359 v_opm_account_type := 'EXPENSE';
360 elsif (v_destination_type = 'ACCRUAL') then
361 v_opm_account_type := 'ACCRUAL';
362 end if;
363
364 END get_opm_account_type;
365
366 -- /*************************************************************************
367 -- # PROC
368 -- # process_trans
369 -- #
370 -- # INPUT PARAMETERS
371 -- #
372 -- # IN OUT PARAMETERS
373 -- # retcode NUMBER. SUCCESS = 0 , ERROR otherwise
374 -- # DESCRIPTION
375 -- # Calls the functions to get Account and Accounting Unit. Also calls set_data
376 -- # procedure which calls a routine to get a code combination id fro this account.
377 -- #
378 -- #**************************************************************************/
379
380 PROCEDURE process_trans (retcode IN OUT NOCOPY NUMBER) AS
381 BEGIN
382 P_acct_id := GML_ACCT_GENERATE.default_mapping ;
383 P_acctg_unit_no := GML_ACCT_GENERATE.get_acctg_unit_no ;
384 GML_ACCT_GENERATE.get_acct_no (P_acct_no, P_acct_desc );
385
386 PrintLn('P_acct_id = '||to_char(P_acct_id));
387 PrintLn('P_acctg_unit_no = '||P_acctg_unit_no);
388 PrintLn('P_acct_no = '||P_acct_no);
389 PrintLn('P_acct_desc = '||P_acct_desc);
390 PrintLn('calling set_data');
391
392 GML_ACCT_GENERATE.set_data (retcode);
393
394 PrintLn('finished calling set_data');
395 END process_trans;
396
397 -- /*##########################################################################
398 -- # PROC
399 -- # default_mapping
400 -- #
401 -- # INPUT PARAMETERS
402 -- # Package Variables are passed to the fuction
403 -- # RETURNS
404 -- # < 0 - Mapping failed
405 -- # > 0 - Mapping Successful. Account id.
406 -- # DESCRIPTION
407 -- # This function calls central account mapping routine to determine the account
408 -- # based on the Account mapping for Company, Account Title and other set of
409 -- # attributes.
410 -- #
411 -- #########################################################################*/
412
413 FUNCTION default_mapping RETURN NUMBER AS
414 BEGIN
415 gmf_get_mappings.get_account_mappings (
416 V_CO_CODE => P_whse_co_code,
417 V_ORGN_CODE => P_whse_orgn_code,
418 V_WHSE_CODE => P_to_whse,
419 V_ITEM_ID => P_gl_item_id,
420 V_VENDOR_ID => P_shipvend_id,
421 V_CUST_ID => P_cust_id,
422 V_REASON_CODE => P_reason_code,
423 V_ICGL_CLASS => P_itemglclass,
424 V_VENDGL_CLASS => P_vend_gl_class,
425 V_CUSTGL_CLASS => P_cust_gl_class,
426 V_CURRENCY_CODE => P_base_currency,
427 V_ROUTING_ID => P_routing_id,
428 V_CHARGE_ID => P_charge_id,
429 V_TAXAUTH_ID => P_taxauth_id,
430 V_AQUI_COST_ID => P_aqui_cost_id,
431 V_RESOURCES => P_resources,
432 V_COST_CMPNTCLS_ID => P_cost_cmpntcls_id,
433 V_COST_ANALYSIS_CODE => P_cost_analysis_code,
434 V_ORDER_TYPE => P_order_type,
435 V_SUB_EVENT_TYPE => P_sub_event_type,
436 V_SOURCE => 0,
437 V_BUSINESS_CLASS_CAT_ID => P_gl_business_class_cat_id,
438 V_PRODUCT_LINE_CAT_ID => P_gl_product_line_cat_id );
439 P_acct_id := gmf_get_mappings.get_account_value (P_acct_ttl_num );
440 RETURN (P_acct_id );
441
442 END default_mapping;
443
444 -- /*##########################################################################
445 -- # PROC
446 -- # get_acctg_unit_no
447 -- #
448 -- # INPUT PARAMETERS
449 -- # Package Variables are passed to the Function
450 -- # RETURNS
451 -- # If success, returns acctg_unit_no ELSE null.
452 -- # DESCRIPTION
453 -- # This function determines the accounting unit
454 -- # based on the Accounting Unit mapping for Company, Organization , Warehouse
455 -- # attributes.
456 -- ############################################################################*/
457 --
458 FUNCTION get_acctg_unit_no RETURN VARCHAR2 AS
459
460 CURSOR Cur_acctg_unit_id (vc_orgn_code VARCHAR2) IS
461 SELECT acctg_unit_id
462 FROM gl_accu_map
463 WHERE co_code = P_whse_co_code and
464 (orgn_code = vc_orgn_code or orgn_code IS NULL) and
465 (whse_code = P_to_whse or whse_code IS NULL) and
466 delete_mark = 0
467 order by nvl(orgn_code, ' ') desc, nvl(whse_code, ' ') desc;
468
469 CURSOR Cur_acctg_unit_no IS
470 SELECT acctg_unit_no
471 FROM gl_accu_mst
472 WHERE acctg_unit_id = P_acctg_unit_id;
473
474 BEGIN
475
476 OPEN Cur_acctg_unit_id (P_whse_orgn_code);
477 FETCH Cur_acctg_unit_id INTO P_acctg_unit_id;
478 CLOSE Cur_acctg_unit_id;
479
480 OPEN Cur_acctg_unit_no;
481 FETCH Cur_acctg_unit_no INTO P_acctg_unit_no;
482 CLOSE Cur_acctg_unit_no;
483 RETURN ( P_acctg_unit_no );
484
485 END get_acctg_unit_no;
486
487 -- /*##########################################################################
488 -- # PROC
489 -- # get_acct_no
490 -- #
491 -- # INPUT PARAMETERS
492 -- # Package variables are passed to the procedure
493 -- # DESCRIPTION
494 -- # This procedure returns the corresponding Account no. and Account desc
495 -- # based on the P_acct_id
496 -- ##########################################################################*/
497
498 PROCEDURE get_acct_no(V_acct_no OUT NOCOPY VARCHAR2, V_acct_desc OUT NOCOPY VARCHAR2) AS
499
500 CURSOR Cur_acct_no IS
501 SELECT acct_no, acct_desc
502 FROM gl_acct_mst
503 WHERE acct_id= P_acct_id;
504
505 BEGIN
506 OPEN Cur_acct_no;
507 FETCH Cur_acct_no INTO V_acct_no, V_acct_desc;
508 CLOSE Cur_acct_no;
509
510 END get_acct_no;
511
512 -- /*############################################################################
513 -- #
514 -- # PROC
515 -- # set_data
516 -- # IN OUT PARAMETERS
517 -- # retcode NUMBER. 0 for SUCCESS, ERROR otherwiese
518 -- #
519 -- # DESCRIPTION
520 -- # This procedure would set data for getting the code combination id. It returns
521 -- # to process trans procedure if P_acct_id or P_acctg_unit_id is null with
522 -- # retcode other than 0. Calls gen_combination_id routine.
523 -- ##############################################################################*/
524
525 PROCEDURE set_data(retcode IN OUT NOCOPY NUMBER) AS
526
527 X_combination_id NUMBER;
528
529 BEGIN
530 PrintLn('begin of set_data');
531
532 IF (GML_ACCT_GENERATE.P_acct_id IS NULL OR GML_ACCT_GENERATE.P_acct_id = -1) THEN
533 retcode := 1;
534 ELSIF GML_ACCT_GENERATE.P_acctg_unit_id IS NULL THEN
535 retcode := 2;
536 END IF;
537
538 IF retcode >0 THEN
539 RETURN;
540 END IF;
541
542 PrintLn('calling gen_combination_id');
543
544 GML_ACCT_GENERATE.gen_combination_id( GML_ACCT_GENERATE.P_whse_co_code,
545 GML_ACCT_GENERATE.P_acct_id,
546 GML_ACCT_GENERATE.P_acctg_unit_id,
547 X_combination_id);
548
549 PrintLn('finished calling gen_combination_id');
550 PrintLn('X_combination_id = '||to_char(X_combination_id));
551
552 P_cc_id := X_combination_id;
553
554 PrintLn('end of set_data');
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 retcode := 1;
559
560 END set_data;
561
562 -- /*############################################################################
563 -- #
564 -- # PROC
565 -- # get_acct_title
566 -- #
567 -- # INPUT PARAMETERS
568 -- # v_opm_account_type VARCHAR2
569 -- # OUTPUT PARAMETERS
570 -- # v_gltitle
571 -- #
572 -- # DESCRIPTION: Determine which acct_ttl to be derived based on the
573 -- # v_opm_account_type passed to it.
574 -- ############################################################################## */
575 PROCEDURE get_acct_title(
576 v_opm_account_type VARCHAR2,
577 v_gltitles OUT NOCOPY NUMBER
578 ) AS
579
580 x_at_inv NUMBER := 1500;
581 x_at_aap NUMBER := 3100;
582 x_at_ppv NUMBER := 6100;
583 x_at_exp NUMBER := 5100;
584
585 BEGIN
586
587 IF v_opm_account_type = 'EXPENSE' THEN
588 v_gltitles := x_at_exp;
589 ELSIF (v_opm_account_type = 'INVENTORY') THEN
590 v_gltitles := x_at_inv;
591 ELSIF (v_opm_account_type = 'ACCRUAL') THEN
592 v_gltitles := x_at_aap;
593 END IF;
594
595 END get_acct_title;
596
597
598
599 PROCEDURE gen_combination_id( v_co_code IN VARCHAR2,
600 v_acct_id IN NUMBER,
601 v_acctg_unit_id IN NUMBER,
602 v_combination_id IN OUT NOCOPY NUMBER) AS
603
604 x_acctg_unit_no gl_accu_mst.acctg_unit_no%TYPE := NULL;
605 x_acct_no gl_acct_mst.acct_no%TYPE := NULL;
606 x_application_short_name VARCHAR2(50);
607 x_key_flex_code VARCHAR2(50);
608 x_chart_of_account_id NUMBER;
609 x_validation_date DATE;
610 x_segment_count NUMBER;
611 x_of_seg fnd_flex_ext.SegmentArray;
612 x_ret BOOLEAN;
613 x_segment_delimiter gl_plcy_mst.segment_delimiter%TYPE;
614
615
616 Cursor get_chart_id is
617 select chart_of_accounts_id
618 from gl_plcy_mst,gl_sets_of_books
619 where co_code = v_co_code
620 and name like set_of_books_name
621 and set_of_books_id = sob_id;
622
623 BEGIN
624
625 PrintLn('begin of gen_combination_id');
626
627 SELECT acctg_unit_no INTO x_acctg_unit_no
628 FROM gl_accu_mst WHERE acctg_unit_id = p_acctg_unit_id;
629
630 SELECT acct_no INTO x_acct_no
631 FROM gl_acct_mst
632 WHERE acct_id = p_acct_id;
633
634 SELECT segment_delimiter INTO x_segment_delimiter
635 FROM gl_plcy_mst
636 WHERE co_code = v_co_code
637 AND delete_mark = 0;
638
639 PrintLn('calling parse_account');
640
641 /* Parse the OPM account to set the of segments based on the segment
642 mapping of OPM and Oracle Financials */
643 GML_ACCT_GENERATE.parse_account( v_co_code ,
644 x_acctg_unit_no ||x_segment_delimiter|| x_acct_no,
645 2,0, x_of_seg, x_segment_count ) ;
646
647 PrintLn('finished calling parse_account');
648
649 /* structure_no */
650 Open get_chart_id;
651 Fetch get_chart_id into x_chart_of_account_id;
652 Close get_chart_id;
653
654 x_application_short_name := 'SQLGL';
655 x_key_flex_code := 'GL#';
656 x_validation_date := SYSDATE;
657
658 PrintLn('calling get_combination_id');
659
660 /* Call the apps routine to get the CC id */
661 x_ret := fnd_flex_ext.get_combination_id(x_application_short_name,
662 x_key_flex_code,
663 x_chart_of_account_id,
664 x_validation_date,
665 x_segment_count,
666 x_of_seg,
667 v_combination_id );
668
669 PrintLn('finished calling get_combination_id');
670 IF x_ret = TRUE THEN
671 PrintLn('fnd_flex_ext.get_combination_id returns TRUE');
672 ELSE
673 PrintLn('fnd_flex_ext.get_combination_id returns FALSE');
674 END IF;
675
676 PrintLn('v_combination_id = '||to_char(v_combination_id));
677 PrintLn('end of gen_combination_id');
678
679 END gen_combination_id;
680
681
682
683 -- /*############################################################################
684 -- # FUNCTION
685 -- # parse_account
686 -- # DESCRIPTION
687 -- # Parses the gemms account string and sorts the segment according
688 -- # to the order defined in Oracle financials.
689 -- #
690 -- # INPUT PARAMETERS
691 -- # v_co_code OPM Company
692 -- # v_account = Account string to be parsed
693 -- # v_type = 0 Parses Account unit segments
694 -- # = 1 Parses Account Segments
695 -- # = 2 Parses both Account unit and Account segments
696 -- # v_offset = Offset value.
697 -- # v_segment Segment array of type fnd_flex_ext.SegmentArray.
698 -- # V_no_of_seg Total no. of segments.
699 -- #
700 -- # OUTPUT PARAMETERS
701 -- # GLOBAL
702 -- #
703 -- # RETURNS
704 -- #
705 -- #
706 -- # Uday Phadtare 12/08/2003 Bug 3299321. Cursor cur_plcy_seg modified.
707 -- ############################################################################ */
708
709 PROCEDURE parse_account( v_co_code IN VARCHAR2,
710 v_account IN VARCHAR2,
711 v_type IN NUMBER,
712 v_offset IN NUMBER,
713 v_segment IN OUT NOCOPY fnd_flex_ext.SegmentArray,
714 V_no_of_seg IN OUT NOCOPY NUMBER )
715 AS
716 /** MC BUG# 2395971**/
717 -- change the select column segment_ref from
718 -- nvl(substrb(f.application_column_name,8),0) to f.segment_num
719
720 CURSOR cur_plcy_seg IS
721 SELECT p.type, p.length,
722 --nvl(substrb(f.application_column_name,8),0) segment_ref,
723 --f.segment_num segment_ref, Bug 3299321
724 p.segment_no segment_ref,
725 pm.segment_delimiter
726 FROM gl_plcy_seg p,
727 gl_plcy_mst pm,
728 fnd_id_flex_segments f,
729 gl_sets_of_books s
730 WHERE p.co_code = v_co_code
731 AND p.delete_mark = 0
732 AND p.co_code = pm.co_code
733 AND pm.sob_id = s.set_of_books_id
734 AND s.chart_of_accounts_id = f.id_flex_num
735 AND f.application_id = 101
736 AND f.id_flex_code = 'GL#'
737 AND LOWER(f.segment_name) = LOWER(p.short_name)
738 AND f.enabled_flag = 'Y'
739 --ORDER BY p.segment_no ; Bug 3299321
740 ORDER BY f.segment_num;
741
742 x_segment_index NUMBER(10) DEFAULT 0;
743 x_value NUMBER(10);
744 x_index NUMBER(10);
745 x_position NUMBER(10) DEFAULT 1;
746 x_length NUMBER(10);
747 x_result VARCHAR2(255);
748 x_gemms_acct VARCHAR2(255);
749 x_description VARCHAR2(1000) default '';
750 source_accounts gmf_get_mappings.my_opm_seg_values;
751 BEGIN
752
753 PrintLn('begin of parse_account');
754
755 source_accounts := gmf_get_mappings.get_opm_segment_values(v_account,v_co_code,2);
756
757 FOR cur_plcy_seg_tmp IN cur_plcy_seg LOOP
758 x_segment_index := x_segment_index + 1;
759 IF (cur_plcy_seg_tmp.type = v_type or v_type = 2) THEN
760 IF (cur_plcy_seg_tmp.segment_ref = 0) THEN
761 x_value := x_segment_index;
762 ELSE
763 x_value := cur_plcy_seg_tmp.segment_ref;
764 END IF;
765 x_index := x_value + v_offset;
766 -- v_segment(x_index) := source_accounts(x_position); Bug 3299321
767 v_segment(x_position) := source_accounts(x_index);
768
769 PrintLn('v_segment['||to_char(x_position)||']' ||v_segment(x_position));
770
771 x_position := x_position + 1;
772 END IF;
773 END LOOP;
774
775 v_no_of_seg := x_segment_index;
776
777 PrintLn('end of parse_account');
778
779 END parse_account;
780
781 END GML_ACCT_GENERATE;