[Home] [Help]
Skip to content
PACKAGE BODY: APPS.ICX_REQ_ACCT2
Source
1 PACKAGE BODY icx_req_acct2 AS
2 /* $Header: ICXRQA2B.pls 115.6 99/07/17 03:22:13 porting ship $ */
3
4 /* if passing in only cart id and cart line id, all distribution lines for
5 that cart line will be validated. Otherwise, if line number and account id
6 are passed in, it only validates that account, without searching through the
7 database table */
8 PROCEDURE validate_charge_account(v_cart_id IN NUMBER,
9 v_cart_line_id IN NUMBER,
10 v_line_number IN NUMBER default NULL,
11 v_account_id IN NUMBER default NULL) is
12
13 v_error_message varchar(1000);
14 v_structure number;
15 v_exist number;
16 v_return_code varchar2(200);
17 v_n_segments number;
18 l_line_number number;
19 l_cart_line_number number := 0;
20 l_account_id number;
21
22 cursor dist_acct is
23 select charge_account_id
24 from icx_cart_line_distributions
25 where cart_id = v_cart_id
26 and cart_line_id = v_cart_line_id
27 order by distribution_id;
28
29 cursor acct_exist(acct_id number) is
30 select count(*)
31 from gl_sets_of_books gsb,
32 financials_system_parameters fsp,
33 gl_code_combinations gl
34 where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
35 and gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
36 and gl.CODE_COMBINATION_ID = acct_id;
37
38 cursor get_cart_line_number(cartid number, cartline_id number) is
39 select cart_line_number
40 from icx_shopping_cart_lines
41 where cart_id = cartid
42 and cart_line_id = cartline_id;
43
44
45 BEGIN
46
47 if icx_sec.validatesession then
48 if v_line_number is not NULL then
49 l_line_number := v_line_number;
50 end if;
51
52 if v_cart_id is not NULL and
53 v_cart_line_id is not NULL then
54
55 open get_cart_line_number(v_cart_id,v_cart_line_id);
56 fetch get_cart_line_number into l_cart_line_number;
57 close get_cart_line_number;
58 end if;
59
60 -- account is null
61 if v_account_id is NULL and
62 v_cart_id is NULL and
63 v_cart_line_id is NULL then
64
65 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
66 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN',' ');
67 v_error_message := FND_MESSAGE.GET;
68 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
69 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
70
71 icx_util.add_error(v_error_message);
72 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number);
73
74 elsif v_account_id is not NULL then
75 l_account_id := v_account_id;
76
77 open acct_exist(l_account_id);
78 fetch acct_exist into v_exist;
79 close acct_exist;
80 if (v_exist = 0) then
81 --add error
82 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
83 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
84 v_error_message := FND_MESSAGE.GET;
85 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
86 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
87 icx_util.add_error(v_error_message);
88 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
89 end if;
90
91 else
92 l_line_number := 1;
93 for prec in dist_acct loop
94 if prec.charge_account_id is not NULL then
95 l_account_id := prec.charge_account_id;
96 open acct_exist(l_account_id);
97 fetch acct_exist into v_exist;
98 close acct_exist;
99 if (v_exist = 0) then
100 --add error
101 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
102 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
103 v_error_message := FND_MESSAGE.GET;
104 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
105 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
106 icx_util.add_error(v_error_message);
107 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
108 end if;
109 else
110 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
111 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
112 v_error_message := FND_MESSAGE.GET;
113 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
114 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
115 icx_util.add_error(v_error_message);
116 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
117 end if;
118 l_line_number := l_line_number + 1;
119 end loop;
120 end if;
121 end if;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
126 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
127 v_error_message := FND_MESSAGE.GET || ': ' || substr(SQLERRM,1,512);
128 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
129 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_Number || ') ' || v_error_message;
130 icx_util.add_error(v_error_message);
131 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
132 end;
133
134
135 PROCEDURE insert_row(v_cart_line_id IN NUMBER,
136 v_oo_id IN NUMBER,
137 v_cart_id IN NUMBER,
138 v_account_id IN NUMBER default NULL,
139 v_n_segments IN NUMBER default NULL,
140 v_segments IN fnd_flex_ext.SegmentArray,
141 v_account_num IN VARCHAR2 default NULL,
142 v_allocation_type IN VARCHAR2 default NULL,
143 v_allocation_value IN NUMBER default NULL) is
144
145
146 cursor get_ak_columns is
147 select ltrim(rtrim(d.COLUMN_NAME)) COL_NAME
148 from ak_region_items a,
149 ak_attributes b,
150 ak_regions c,
151 ak_object_attributes d
152 where a.NODE_DISPLAY_FLAG = 'Y'
153 and a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
154 and a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
155 and b.DATA_TYPE = 'VARCHAR2'
156 and c.REGION_APPLICATION_ID = 601
157 and a.REGION_CODE = c.REGION_CODE
158 and a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
159 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
160 and a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
161 and a.region_code = 'ICX_CART_LINE_DISTRIBUTIONS_R'
162 and d.COLUMN_NAME like 'CHARGE_ACCOUNT_SEGMENT%'
163 order by a.display_sequence;
164
165 v_col_name varchar2(100);
166 l_insert_sql varchar2(8000);
167 l_shopper_id number;
168 l_cur_seg number;
169 l number;
170 l_call INTEGER;
171 l_ret INTEGER;
172 l_err_pos NUMBER;
173 l_error_message VARCHAR2(2000);
174 l_err_num NUMBER;
175 l_err_mesg VARCHAR2(240);
176 l_alloc_type VARCHAR2(20) := 'PERCENT';
177 l_alloc_percent NUMBER := 100;
178 v_variance_acct_id NUMBER := NULL;
179 v_budget_acct_id NUMBER := NULL;
180 v_accrual_acct_id NUMBER := NULL;
181 v_return_code varchar2(200) := NULL;
182 l_dist_num number := NULL;
183 l_num_ak_cols number := 0;
184
185 cursor get_dist_num is
186 select max(distribution_num)
187 from icx_cart_line_distributions
188 where cart_id = v_cart_id
189 and cart_line_id = v_cart_line_id
190 and nvl(org_id,-9999) = nvl(v_oo_id,-9999);
191
192 cursor get_cart_line_number is
193 select cart_line_number
194 from icx_shopping_cart_lines
195 where cart_id = v_cart_id
196 and cart_line_id = v_cart_line_id;
197
198 l_cart_line_number NUMBER := 0;
199
200 /* New Vars added to take care of Binary Vars code ***/
201
202 v_cursor_id INTEGER;
203
204 v_distribution_id NUMBER;
205
206 v_segment_bind fnd_flex_ext.SegmentArray;
207
208 begin
209
210 if icx_sec.validatesession then
211
212
213 l_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
214
215 open get_cart_line_number;
216 fetch get_cart_line_number into l_cart_line_number;
217 close get_cart_line_number;
218
219 open get_dist_num;
220 fetch get_dist_num into l_dist_num;
221 close get_dist_num;
222
223 if l_dist_num is NULL then
224 l_dist_num := 1;
225 else
226 l_dist_num := l_dist_num + 1;
227 end if;
228
229 if v_allocation_type is not NULL and
230 v_allocation_value is not NULL then
231 l_alloc_type := v_allocation_type;
232 l_alloc_percent := v_allocation_value;
233 end if;
234
235 /* Making changes wrto Bind Vars ****/
236
237 /* Making changes wrto Bind Vars ****/
238
239 /* changed the code to take care of Bind vars + AK flexibility of DYnamic sql ***/
240
241
242 l_insert_sql := 'Insert into icx_cart_line_distributions(cart_line_id,
243 distribution_id,distribution_num,charge_account_id,charge_account_num,
244 allocation_type,allocation_value';
245
246 l_insert_sql := l_insert_sql || ' ,last_updated_by,last_update_date,
247 last_update_login, creation_date,created_by,org_id,cart_id';
248
249
250
251 select icx_cart_line_distributions_s.nextval into v_distribution_id from sys.dual;
252
253 /* code was commented out to take care of Bind vars logic ***/
254
255
256 if v_n_segments > 0 then
257 l := v_n_segments;
258 l_num_ak_cols := 0;
259 for prec in get_ak_columns loop
260 l_num_ak_cols := l_num_ak_cols + 1;
261 l_insert_sql := l_insert_sql || ',' || prec.COL_NAME;
262 l := l - 1;
263 if l = 0 then
264 exit;
265 end if;
266
267 end loop;
268 end if;
269
270 l_insert_sql := l_insert_sql || ')';
271
272
273
274 /*
275 l_insert_sql := l_insert_sql || ' VALUES (' || v_cart_line_id || ',icx_cart_line_distributions_s.nextval,' || l_dist_num || ','
276 || v_account_id || ',''' || v_account_num || ''',''' || l_alloc_type|| ''','
277 || l_alloc_percent || ',' || l_shopper_id || ',sysdate,' || l_shopper_id || ',sysdate,' || l_shopper_id || ',' || v_oo_id || ',' || v_cart_id;
278 ***/
279 /*sugupta breaking l_insert_sql into two to reduce line length for MRC conversion*/
280
281 l_insert_sql := l_insert_sql || 'values( :cart_line_id, :distribution_id, :distribution_num, :charge_account_id, :charge_account_num, :allocation_type, :allocation_value';
282
283 l_insert_sql := l_insert_sql || ' , :last_updated_by, :last_update_date, :last_update_login, :creation_date, :created_by, :org_id, :cart_id';
284
285
286 if v_n_segments >= 1 and l_num_ak_cols > 0 then
287 for l in 1..l_num_ak_cols loop
288 l_insert_sql := l_insert_sql || ',:a' || to_char(l);
289 v_segment_bind(l) := v_segments(l);
290 end loop;
291 end if;
292
293 l_insert_sql := l_insert_sql || ')';
294
295
296 v_cursor_id := dbms_sql.open_cursor;
297 dbms_sql.parse( v_cursor_id, l_insert_sql, DBMS_SQL.native);
298
299 l_err_pos := dbms_sql.LAST_ERROR_POSITION;
300
301 dbms_sql.bind_variable(v_cursor_id, ':cart_line_id', v_cart_line_id );
302 dbms_sql.bind_variable(v_cursor_id, ':distribution_id', v_distribution_id );
303 dbms_sql.bind_variable(v_cursor_id, ':distribution_num', l_dist_num );
304 dbms_sql.bind_variable(v_cursor_id, ':charge_account_id', v_account_id );
305 dbms_sql.bind_variable(v_cursor_id, ':charge_account_num', v_account_num );
306 dbms_sql.bind_variable(v_cursor_id, ':allocation_type', l_alloc_type );
307 dbms_sql.bind_variable(v_cursor_id, ':allocation_value', l_alloc_percent );
308 dbms_sql.bind_variable(v_cursor_id, ':last_updated_by', l_shopper_id );
309 dbms_sql.bind_variable(v_cursor_id, ':last_update_date', sysdate );
310 dbms_sql.bind_variable(v_cursor_id, ':last_update_login', l_shopper_id );
311 dbms_sql.bind_variable(v_cursor_id, ':creation_date', sysdate );
312 dbms_sql.bind_variable(v_cursor_id, ':created_by', l_shopper_id );
313 dbms_sql.bind_variable(v_cursor_id, ':org_id', v_oo_id );
314 dbms_sql.bind_variable(v_cursor_id, ':cart_id', v_cart_id );
315
316 for ix in 1..l_num_ak_cols loop
317 dbms_sql.bind_variable(v_cursor_id, ':a' || to_char(ix), v_segment_bind(ix) );
318
319 end loop;
320
321 -- l_call := dbms_sql.open_cursor;
322 -- dbms_sql.parse(l_call,l_insert_sql ,dbms_sql.native);
323 -- l_ret := dbms_sql.execute(l_call);
324 -- dbms_sql.close_cursor(l_call);
325 l_ret := dbms_sql.execute(v_cursor_id);
326 dbms_sql.close_cursor(v_cursor_id);
327
328
329
330
331 -- update the other account id based on charge account id
332 icx_req_custom.cart_custom_build_req_account2(v_cart_line_id,
333 v_variance_acct_id,
334 v_budget_acct_id,
335 v_accrual_acct_id,
336 v_return_code);
337
338 update icx_cart_line_distributions
339 set ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
340 VARIANCE_ACCOUNT_ID = v_variance_acct_id,
341 BUDGET_ACCOUNT_ID = v_budget_acct_id
342 where CART_LINE_ID = v_cart_line_id
343 and CART_ID = v_cart_id;
344
345 end if;
346 exception
347 WHEN OTHERS THEN
348 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
349 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',l_cart_line_number);
350 l_err_num := SQLCODE;
351 l_error_message := SQLERRM;
352 select substr(l_error_message,12,512) into l_err_mesg from dual;
353 l_err_mesg := FND_MESSAGE.GET || ': ' || l_err_mesg;
354 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
355 l_err_mesg := '(' || FND_MESSAGE.GET || ' ' || l_dist_num || ') ' || l_err_mesg;
356 icx_util.add_error(l_err_mesg);
357 ICX_REQ_SUBMIT.storeerror(v_cart_id, l_err_mesg,l_dist_num,v_cart_line_id);
358 if dbms_sql.IS_OPEN(v_cursor_id) then
359 dbms_sql.close_cursor(v_cursor_id);
360 end if;
361
362 end;
363
364
365
366 PROCEDURE update_row(v_cart_line_id IN NUMBER,
367 v_oo_id IN NUMBER,
368 v_cart_id IN NUMBER,
369 v_distribution_id IN NUMBER,
370 v_line_number IN NUMBER,
371 v_account_id IN NUMBER default NULL,
372 v_n_segments IN NUMBER default NULL,
373 v_segments IN fnd_flex_ext.SegmentArray,
374 v_account_num IN VARCHAR2 default NULL,
375 v_allocation_type IN VARCHAR2 default NULL,
376 v_allocation_value IN NUMBER default NULL) is
377
378
379 cursor get_ak_columns is
380 select ltrim(rtrim(d.COLUMN_NAME)) COL_NAME
381 from ak_region_items a,
382 ak_attributes b,
383 ak_regions c,
384 ak_object_attributes d
385 where a.NODE_DISPLAY_FLAG = 'Y'
386 and a.ATTRIBUTE_CODE = b.ATTRIBUTE_CODE
387 and a.ATTRIBUTE_APPLICATION_ID = b.ATTRIBUTE_APPLICATION_ID
388 and b.DATA_TYPE = 'VARCHAR2'
389 and c.REGION_APPLICATION_ID = 601
390 and a.REGION_CODE = c.REGION_CODE
391 and a.REGION_APPLICATION_ID = c.REGION_APPLICATION_ID
392 and c.DATABASE_OBJECT_NAME = d.DATABASE_OBJECT_NAME
393 and a.ATTRIBUTE_CODE = d.ATTRIBUTE_CODE
394 and a.region_code = 'ICX_CART_LINE_DISTRIBUTIONS_R'
395 and d.COLUMN_NAME like 'CHARGE_ACCOUNT_SEGMENT%'
396 order by a.display_sequence;
397
398 v_col_name varchar2(100);
399 l_insert_sql varchar2(8000);
400 l_shopper_id number;
401 l_cur_seg number;
402 l number;
403 l_call INTEGER;
404 l_ret INTEGER;
405 l_err_pos NUMBER;
406 l_error_message VARCHAR2(2000);
407 l_err_num NUMBER;
408 l_err_mesg VARCHAR2(240);
409 l_alloc_type VARCHAR2(20) := 'PERCENT';
410 l_alloc_percent NUMBER := 100;
411 v_variance_acct_id NUMBER := NULL;
412 v_budget_acct_id NUMBER := NULL;
413 v_accrual_acct_id NUMBER := NULL;
414 v_return_code varchar2(200) := NULL;
415 l_cart_line_number NUMBER := 0;
416
417 cursor get_cart_line_number is
418 select cart_line_number
419 from icx_shopping_cart_lines
420 where cart_id = v_cart_id
421 and cart_line_id = v_cart_line_id;
422
423 /* New vars added to take care of Bind Vars ***/
424
425 v_cursor_id INTEGER;
426
427 v_segment_bind fnd_flex_ext.SegmentArray;
428
429 begin
430
431 if icx_sec.validatesession then
432
433 l_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
434
435 open get_cart_line_number;
436 fetch get_cart_line_number into l_cart_line_number;
437 close get_cart_line_number;
438
439 /* Commented out the following code to implement Bind vars **/
440
441 -- l_insert_sql := 'Update icx_cart_line_distributions set
442 -- last_updated_by = ' || l_shopper_id
443 -- || ' ,last_update_login = ' || l_shopper_id
444 -- || ' ,last_update_date = sysdate';
445 /*sugupta breaking l_insert_sql into two to reduce line length for MRC conversion*/
446
447 l_insert_sql := 'Update icx_cart_line_distributions
448 set last_updated_by = :last_updated_by,
449 last_update_login = :last_update_login ,
450 last_update_date = :last_update_date,
451 allocation_type = decode( :allocation_type, null, allocation_type, :allocation_type),
452 allocation_value = decode( :allocation_value,null, allocation_value, :allocation_value)';
453 l_insert_sql := l_insert_sql || ' , charge_account_id = decode( :charge_account_id , null, charge_account_id, :charge_account_id), charge_account_num = decode( :charge_account_num, null, charge_account_num, :charge_account_num)';
454
455 /* The following code is commented out to take care of Bind vars **/
456
457 /*
458 if v_allocation_type is not NULL then
459 l_insert_sql := l_insert_sql || ', allocation_type = ''' || v_allocation_type || '''';
460 end if;
461 if v_allocation_value is not NULL then
462 l_insert_sql := l_insert_sql || ', allocation_value = ' || v_allocation_value;
463 end if;
464 if v_account_id is not NULL then
465 l_insert_sql := l_insert_sql || ', charge_account_id = ' || v_account_id;
466 end if;
467 if v_account_num is not NULL then
468 l_insert_sql := l_insert_sql || ', charge_account_num = ''' || v_account_num || '''';
469 end if;
470 **/
471
472 /*837732 bind variable 'a' was wrongly coded */
473
474 if v_n_segments > 0 then
475 l := 1;
476 for prec in get_ak_columns loop
477 l_insert_sql := l_insert_sql || ',' || prec.COL_NAME || ' = :a' || to_char(l) ;
478 v_segment_bind(l) := v_segments(l);
479
480 -- if l > v_n_segments then
481 if l = v_n_segments then
482 exit;
483 end if;
484
485 l := l + 1;
486
487 end loop;
488 end if;
489
490 /*
491 l_insert_sql := l_insert_sql || ' where cart_id = ' || v_cart_id ||
492 ' and cart_line_id = ' || v_cart_line_id || ' and distribution_id = ' || v_distribution_id;
493
494 **/
495
496 l_insert_sql := l_insert_sql || ' where cart_id = :cart_id and cart_line_id = :cart_line_id and distribution_id = :distribution_id ';
497
498
499
500 v_cursor_id := dbms_sql.open_cursor;
501 dbms_sql.parse( v_cursor_id, l_insert_sql, DBMS_SQL.native);
502
503 l_err_pos := dbms_sql.LAST_ERROR_POSITION;
504
505 dbms_sql.bind_variable(v_cursor_id, ':cart_line_id', v_cart_line_id );
506 dbms_sql.bind_variable(v_cursor_id, ':cart_id', v_cart_id );
507 dbms_sql.bind_variable(v_cursor_id, ':distribution_id', v_distribution_id );
508 dbms_sql.bind_variable(v_cursor_id, ':charge_account_id', v_account_id );
509 dbms_sql.bind_variable(v_cursor_id, ':charge_account_num', v_account_num );
510 dbms_sql.bind_variable(v_cursor_id, ':allocation_type', v_allocation_type );
511 dbms_sql.bind_variable(v_cursor_id, ':allocation_value', v_allocation_value );
512 dbms_sql.bind_variable(v_cursor_id, ':last_updated_by', l_shopper_id );
513 dbms_sql.bind_variable(v_cursor_id, ':last_update_date', sysdate );
514 dbms_sql.bind_variable(v_cursor_id, ':last_update_login', l_shopper_id );
515
516 for ix in 1..l loop
517 dbms_sql.bind_variable(v_cursor_id, ':a' || to_char(ix), v_segment_bind(ix) );
518 end loop;
519 /*
520 l_call := dbms_sql.open_cursor;
521 dbms_sql.parse(l_call,l_insert_sql ,dbms_sql.native);
522 l_err_pos := dbms_sql.LAST_ERROR_POSITION;
523 l_ret := dbms_sql.execute(l_call);
524 dbms_sql.close_cursor(l_call);
525 ***/
526 l_ret := dbms_sql.execute(v_cursor_id);
527 dbms_sql.close_cursor(v_cursor_id);
528
529 -- update the other account id based on charge account id
530 icx_req_custom.cart_custom_build_req_account2(v_cart_line_id,
531 v_variance_acct_id,
532 v_budget_acct_id,
533 v_accrual_acct_id,
534 v_return_code);
535
536 update icx_cart_line_distributions
537 set ACCRUAL_ACCOUNT_ID = v_accrual_acct_id,
538 VARIANCE_ACCOUNT_ID = v_variance_acct_id,
539 BUDGET_ACCOUNT_ID = v_budget_acct_id
540 where CART_LINE_ID = v_cart_line_id
541 and CART_ID = v_cart_id
542 and DISTRIBUTION_ID = v_distribution_id;
543
544 end if;
545
546 exception
547 WHEN OTHERS THEN
548 l_err_num := SQLCODE;
549 l_error_message := SQLERRM;
550 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
551 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',l_cart_line_number);
552 select substr(l_error_message,12,512) into l_err_mesg from dual;
553 l_err_mesg := FND_MESSAGE.GET || ': ' || l_err_mesg;
554 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
555 l_err_mesg := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || l_err_mesg;
556 icx_util.add_error(l_err_mesg);
557 ICX_REQ_SUBMIT.storeerror(v_cart_id, l_err_mesg,v_line_number,v_cart_line_id);
558 if dbms_sql.IS_OPEN(v_cursor_id) then
559 dbms_sql.close_cursor(v_cursor_id);
560 end if;
561 end;
562
563
564 /* get account id, segments by con-catenated segments pass in at v_account_num */
565 PROCEDURE get_acct_by_con(v_cart_id IN NUMBER,
566 v_line_number IN NUMBER,
567 v_account_num IN VARCHAR2,
568 v_structure IN NUMBER,
569 v_cart_line_id IN NUMBER,
570 v_cart_line_number IN NUMBER default NULL,
571 v_n_segments OUT NUMBER,
572 v_segments OUT fnd_flex_ext.SegmentArray,
573 v_account_id OUT NUMBER) is
574 v_delimiter varchar2(10);
575 l_n_segments NUMBER := NULL;
576 l_segments fnd_flex_ext.SegmentArray;
577 l_account_id NUMBER := NULL;
578 l_ret_cd BOOLEAN;
579 v_error_message varchar2(1000);
580
581 begin
582
583 -- get con-seg delimiter
584 v_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',v_structure);
585 l_account_id := fnd_flex_ext.get_ccid('SQLGL',
586 'GL#',
587 v_structure,
588 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
589 v_account_num);
590
591 if l_account_id is not NULL then
592
593 v_account_id := l_account_id;
594 l_ret_cd := fnd_flex_ext.get_segments('SQLGL',
595 'GL#',
596 v_structure,
597 l_account_id,
598 v_n_segments,
599 v_segments);
600 if l_ret_cd = FALSE then
601 v_error_message := FND_MESSAGE.GET;
602 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
603 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',v_cart_line_number);
604 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
605 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
606 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ' ) ' || v_error_message;
607 icx_util.add_error(v_error_message);
608 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
609 end if;
610
611
612 end if;
613
614 exception
615 when others then
616 v_error_message := substr(SQLERRM,1,512);
617 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
618 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',v_cart_line_number);
619 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
620 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
621 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
622 icx_util.add_error(v_error_message);
623 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
624 end;
625
626
627 /* get account id,con-catenated segments based on segments */
628 PROCEDURE get_acct_by_segs(v_cart_id IN NUMBER,
629 v_line_number IN NUMBER,
630 v_segments IN fnd_flex_ext.SegmentArray,
631 v_structure IN NUMBER,
632 v_cart_line_id IN NUMBER,
633 v_cart_line_number IN NUMBER default NULL,
634 v_n_segments OUT NUMBER,
635 v_account_num OUT VARCHAR2,
636 v_account_id OUT NUMBER) is
637
638 v_delimiter varchar2(10);
639 l_n_segments NUMBER := NULL;
640 l_account_num VARCHAR2(2000) := NULL;
641 l_account_id NUMBER := NULL;
642 l_ret_cd BOOLEAN;
643 v_error_message varchar2(1000);
644
645 begin
646
647 -- get con-seg delimiter
648 v_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',v_structure);
649 l_n_segments := v_segments.COUNT;
650 v_n_segments := l_n_segments;
651
652 l_account_num := fnd_flex_ext.concatenate_segments(l_n_segments,
653 v_segments,
654 v_delimiter);
655 v_account_num := l_account_num;
656
657 if l_account_num is not NULL then
658
659 l_account_id := fnd_flex_ext.get_ccid('SQLGL',
660 'GL#',
661 v_structure,
662 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
663 l_account_num);
664
665
666 v_account_id := l_account_id;
667 else
668 v_account_id := NULL;
669 end if;
670
671 exception
672 when others then
673 v_error_message := substr(SQLERRM,1,512);
674 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
675 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',v_cart_line_number);
676 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
677 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
678 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
679 icx_util.add_error(v_error_message);
680 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
681 end;
682
683
684 /* main procedure to call to get segments,concatenated segments based on an
685 * account id */
686 PROCEDURE get_account_segments(v_cart_id IN NUMBER,
687 v_line_number IN NUMBER,
688 v_account_id IN NUMBER,
689 v_structure IN NUMBER,
690 v_cart_line_id IN NUMBER,
691 v_cart_line_number IN NUMBER default NULL,
692 v_n_segments OUT NUMBER,
693 v_segments OUT fnd_flex_ext.SegmentArray,
694 v_account_num OUT VARCHAR2) is
695
696 v_delimiter varchar2(10);
697 l_n_segments NUMBER := NULL;
698 l_segments fnd_flex_ext.SegmentArray;
699 l_ret_cd BOOLEAN;
700 v_error_message varchar2(1000);
701
702 begin
703 -- get con-seg delimiter
704 v_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',v_structure);
705
706 -- get segments and put into the plsql table
707 l_ret_cd := fnd_flex_ext.get_segments('SQLGL',
708 'GL#',
709 v_structure,
710 v_account_id,
711 l_n_segments,
712 l_segments);
713
714 if l_ret_cd = FALSE then
715 v_error_message := FND_MESSAGE.GET;
716 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
717 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',v_cart_line_number);
718 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
719 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
720 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
721 icx_util.add_error(v_error_message);
722 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
723 end if;
724
725 -- if returns segments generate con-segs
726 if l_n_segments is not NULL and
727 l_n_segments <> 0 then
728
729 v_n_segments := l_n_segments;
730 v_segments := l_segments;
731
732 v_account_num := fnd_flex_ext.concatenate_segments(n_segments => l_n_segments,
733 segments => l_segments,
734 delimiter => v_delimiter);
735
736 end if;
737 exception
738 when others then
739 v_error_message := substr(SQLERRM,1,512);
740 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
741 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',v_cart_line_number);
742 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
743 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
744 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
745 icx_util.add_error(v_error_message);
746 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
747
748 end;
749
750
751 /* Find Account Id based on concatenated segments passin and update the account
752 * distribution tables based on account id found.
753 * Pass in distribution id to update existing account, and insert a new row
754 * if distiribution id is not passed.*/
755 /* NOTE: this is used when no segments are turned on in AK for display or
756 update, so only update the charge account id and charge account num */
757 PROCEDURE update_account_num(v_cart_id IN NUMBER,
758 v_cart_line_id IN NUMBER,
759 v_oo_id IN NUMBER,
760 v_account_num IN VARCHAR2,
761 v_distribution_id IN NUMBER default NULL,
762 v_line_number IN NUMBER default NULL,
763 v_allocation_type IN VARCHAR2 default NULL,
764 v_allocation_value IN NUMBER default NULL,
765 v_validate_flag IN VARCHAR2 default 'Y') is
766
767 v_error_message varchar(1000);
768 v_structure number;
769 v_expense_account number;
770 v_exist number;
771 v_return_code varchar2(200);
772 v_n_segments number;
773 v_segments fnd_flex_ext.SegmentArray;
774 v_account_id number := NULL;
775 l_line_number number := NULL;
776
777 cursor get_line_number(cartid number,cartline_id number,oo_id number) is
778 select max(distribution_num)
779 from icx_cart_line_distributions
780 where cart_id = cartid
781 and cart_line_id = cartline_id
782 and nvl(org_id, -9999) = nvl(oo_id,-9999);
783
784 CURSOR chart_account_id IS
785 SELECT CHART_OF_ACCOUNTS_ID
786 FROM gl_sets_of_books,
787 financials_system_parameters fsp
788 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
789
790 cursor get_cart_line_number(cartid number,cartline_id number) is
791 select cart_line_number
792 from icx_shopping_cart_lines
793 where cart_id = cartid
794 and cart_line_id = cartline_id;
795
796 l_cart_line_number NUMBER := 0;
797
798 BEGIN
799
800 if icx_sec.validatesession then
801
802 -- get structure number
803 open chart_account_id;
804 fetch chart_account_id into v_structure;
805 close chart_account_id;
806
807 open get_cart_line_number(v_cart_id,v_cart_line_id);
808 fetch get_cart_line_number into l_cart_line_number;
809 close get_cart_line_number;
810
811 -- if v_distribution_id is pass in as null then this is a new line
812 -- get a new dist line number
813 if v_distribution_id is NULL then
814 open get_line_number(v_cart_id,v_cart_line_id,v_oo_id);
815 fetch get_line_number into l_line_number;
816 close get_line_number;
817
818 if l_line_number is NULL then
819 l_line_number := 1;
820 else
821 l_line_number := l_line_number + 1;
822 end if;
823 else
824 l_line_number := v_line_number;
825 end if;
826
827 /* get the account id */
828 v_account_id := fnd_flex_ext.get_ccid('SQLGL',
829 'GL#',
830 v_structure,
831 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
832 v_account_num);
833
834 /* if the account number passing in does not generate a valid account id
835 error out immediately */
836 if v_account_id is NULL then
837 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
838 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
839 v_error_message := FND_MESSAGE.GET;
840 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
841 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
842 icx_util.add_error(v_error_message);
843 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
844 else
845
846 if v_validate_flag = 'Y' then
847 validate_charge_account(v_cart_id,v_cart_line_id,l_line_number,v_account_id);
848 end if;
849 end if;
850
851 /* No Segments are passed in , so set v_n_segments to 0 to avoid updating
852 any segment in the insert_row or update_row procedure */
853 v_n_segments := 0;
854 if v_distribution_id is NULL then
855 insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
856 else
857
858 update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
859 end if;
860
861 end if;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
866 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number || ': ' || substr(SQLERRM,1,512));
867 v_error_message := FND_MESSAGE.GET;
868 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
869 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
870 icx_util.add_error(v_error_message);
871 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
872 -- icx_util.add_error(substr(SQLERRM, 12, 512));
873 end;
874
875
876
877
878 /* Find Account Id based on table of segments passin and update the account
879 * distribution tables based on account id found
880 * Pass in distribution id to update existing account, and insert a new row
881 * if distiribution id is not passed.*/
882 PROCEDURE update_account(v_cart_id IN NUMBER,
883 v_cart_line_id IN NUMBER,
884 v_oo_id IN NUMBER,
885 v_segments IN fnd_flex_ext.SegmentArray,
886 v_distribution_id IN NUMBER default NULL,
887 v_line_number IN NUMBER default NULL,
888 v_allocation_type IN VARCHAR2 default NULL,
889 v_allocation_value IN NUMBER default NULL,
890 v_validate_flag IN VARCHAR2 default 'Y') is
891
892 v_error_message varchar(1000);
893 v_structure number;
894 v_expense_account number;
895 v_exist number;
896 v_return_code varchar2(200);
897 v_n_segments number;
898 v_account_num varchar2(2000) := NULL;
899 v_account_id number := NULL;
900 l_line_number number := NULL;
901
902 cursor get_line_number(cartid number,cartline_id number,oo_id number) is
903 select max(distribution_num)
904 from icx_cart_line_distributions
905 where cart_id = cartid
906 and cart_line_id = cartline_id
907 and nvl(org_id, -9999) = nvl(oo_id,-9999);
908
909 CURSOR chart_account_id IS
910 SELECT CHART_OF_ACCOUNTS_ID
911 FROM gl_sets_of_books,
912 financials_system_parameters fsp
913 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
914
915 cursor get_cart_line_number(cartid number,cartline_id number) is
916 select cart_line_number
917 from icx_shopping_cart_lines
918 where cart_id = cartid
919 and cart_line_id = cartline_id;
920
921 l_cart_line_number NUMBER := 0;
922
923 BEGIN
924
925 if icx_sec.validatesession then
926
927 open get_cart_line_number(v_cart_id,v_cart_line_id);
928 fetch get_cart_line_number into l_cart_line_number;
929 close get_cart_line_number;
930
931
932 -- get structure number
933 open chart_account_id;
934 fetch chart_account_id into v_structure;
935 close chart_account_id;
936
937 -- if v_distribution_id is pass in as null then this is a new line
938 -- get a new dist line number
939 if v_distribution_id is NULL then
940 open get_line_number(v_cart_id,v_cart_line_id,v_oo_id);
941 fetch get_line_number into l_line_number;
942 close get_line_number;
943
944 if l_line_number is NULL then
945 l_line_number := 1;
946 else
947 l_line_number := l_line_number + 1;
948 end if;
949 else
950 l_line_number := v_line_number;
951 end if;
952
953 get_acct_by_segs(v_cart_id,l_line_number,v_segments,v_structure,v_cart_line_id,l_cart_line_number,v_n_segments,v_account_num,v_account_id);
954
955 if v_n_segments > 0 then
956
957 if v_validate_flag = 'Y' then
958 validate_charge_account(v_cart_id,v_cart_line_id,l_line_number,v_account_id);
959 end if;
960
961 if v_distribution_id is NULL then
962
963 insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
964 else
965
966 update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,v_line_number,v_account_id,v_n_segments,v_segments,v_account_num,v_allocation_type,v_allocation_value);
967 end if;
968
969 else
970
971 --add error
972 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
973 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
974 v_error_message := FND_MESSAGE.GET;
975 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
976 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
977 icx_util.add_error(v_error_message);
978 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
979
980
981 end if;
982
983 end if;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
988 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number || ': ' || substr(SQLERRM,1,512));
989 v_error_message := FND_MESSAGE.GET;
990 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
991 v_error_message := '(' || FND_MESSAGE.GET || ' ' || l_line_number || ') ' || v_error_message;
992 icx_util.add_error(v_error_message);
993 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,l_line_number,v_cart_line_id);
994 -- icx_util.add_error(substr(SQLERRM, 12, 512));
995 end;
996
997
998
999 PROCEDURE get_default_account (v_cart_id IN NUMBER,
1000 v_cart_line_id IN NUMBER,
1001 v_emp_id IN NUMBER,
1002 v_oo_id IN NUMBER,
1003 v_account_id IN OUT NUMBER,
1004 v_account_num IN OUT VARCHAR2
1005
1006 ) IS
1007
1008 v_error_message varchar(1000);
1009 v_structure number;
1010 v_expense_account number;
1011 v_line_number number;
1012 v_exist number;
1013 v_return_code varchar2(200);
1014 v_n_segments number;
1015 v_segments fnd_flex_ext.SegmentArray;
1016
1017 CURSOR line_default_account is
1018 SELECT default_code_combination_id employee_default_account_id
1019 from hr_employees_current_v
1020 where employee_id = v_emp_id;
1021
1022 CURSOR item_expense_account is
1023 select msi.expense_account
1024 from mtl_system_items msi,
1025 icx_shopping_carts isc,
1026 icx_shopping_cart_lines iscl
1027 where msi.inventory_item_id(+) = iscl.item_id
1028 AND nvl(msi.ORGANIZATION_ID,
1029 nvl(isc.DESTINATION_ORGANIZATION_ID,
1030 iscl.DESTINATION_ORGANIZATION_ID)) =
1031 nvl(isc.DESTINATION_ORGANIZATION_ID,
1032 iscl.DESTINATION_ORGANIZATION_ID)
1033 and iscl.cart_id = isc.cart_id
1034 and iscl.cart_id = v_cart_id
1035 and iscl.cart_line_id = v_cart_line_id
1036 and nvl(isc.org_id,-9999) = nvl(v_oo_id,-9999)
1037 and nvl(iscl.org_id,-9999) = nvl(v_oo_id,-9999);
1038
1039 CURSOR chart_account_id IS
1040 SELECT CHART_OF_ACCOUNTS_ID
1041 FROM gl_sets_of_books,
1042 financials_system_parameters fsp
1043 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
1044
1045 cursor get_cart_line_number is
1046 select cart_line_number
1047 from icx_shopping_cart_lines
1048 where cart_id = v_cart_id
1049 and cart_line_id = v_cart_line_id
1050 and nvl(org_id, -9999) = nvl(v_oo_id,-9999);
1051
1052 l_cart_line_number NUMBER := 0;
1053
1054 BEGIN
1055
1056 if icx_sec.validatesession then
1057 open get_cart_line_number;
1058 fetch get_cart_line_number into l_cart_line_number;
1059 close get_cart_line_number;
1060
1061 v_line_number := 1;
1062
1063 -- get structure number
1064 open chart_account_id;
1065 fetch chart_account_id into v_structure;
1066 close chart_account_id;
1067
1068 -- get account from customer default
1069 v_account_id := NULL;
1070 v_account_num := NULL;
1071 icx_req_custom.cart_custom_build_req_account(v_cart_line_id,
1072 v_account_num,
1073 v_account_id,
1074 v_return_code);
1075
1076 -- if customer does not return any account id or con-seg of the account
1077 if (v_account_num is NULL) and (v_account_id is NULL) then
1078
1079 -- get the default account
1080 open line_default_account;
1081 fetch line_default_account into v_account_id;
1082 close line_default_account;
1083 if v_account_id is NULL then
1084 open item_expense_account;
1085 fetch item_expense_account into v_account_id;
1086 close item_expense_account;
1087 end if;
1088 if v_account_id is not NULL then
1089 select count(*) into v_exist
1090 from gl_sets_of_books gsb,
1091 financials_system_parameters fsp,
1092 gl_code_combinations gl
1093 where gsb.SET_OF_BOOKS_ID = fsp.set_of_books_id
1094 and gsb.CHART_OF_ACCOUNTS_ID = gl.CHART_OF_ACCOUNTS_ID
1095 and gl.CODE_COMBINATION_ID = v_account_id;
1096 if (v_exist = 0) then
1097 --add error
1098 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1099 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
1100 v_error_message := FND_MESSAGE.GET;
1101 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1102 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1103 icx_util.add_error(v_error_message);
1104 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number);
1105 else -- get con-seg based on account id
1106 get_account_segments(v_cart_id,v_line_number,v_account_id,v_structure,v_cart_line_id,l_cart_line_number,v_n_segments,v_segments,v_account_num);
1107 end if;
1108 else
1109 --add error
1110 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1111 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
1112 v_error_message := FND_MESSAGE.GET;
1113 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1114 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1115 icx_util.add_error(v_error_message);
1116 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1117
1118 end if;
1119
1120 elsif v_account_num is not NULL then
1121 get_acct_by_con(v_cart_id,v_line_number,v_account_num,v_structure,v_cart_line_id,l_cart_line_number,v_n_segments,v_segments,v_account_id);
1122
1123 if (v_account_id is null) or (v_account_id = 0) then
1124 --add error
1125 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1126 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number);
1127 v_error_message := FND_MESSAGE.GET;
1128 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1129 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1130 icx_util.add_error(v_error_message);
1131 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1132 v_account_id := null;
1133 end if;
1134
1135 end if;
1136
1137 insert_row(v_cart_line_id,v_oo_id,v_cart_id,v_account_id,v_n_segments,v_segments,v_account_num);
1138
1139 end if;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1144 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number || ': ' || substr(SQLERRM,1,512));
1145 v_error_message := FND_MESSAGE.GET;
1146 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1147 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1148 icx_util.add_error(v_error_message);
1149 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1150 -- icx_util.add_error(substr(SQLERRM, 12, 512));
1151
1152 END get_default_account;
1153
1154
1155 /* call this to get the default account in a segment table */
1156 PROCEDURE get_default_segs (v_cart_id IN NUMBER,
1157 v_cart_line_id IN NUMBER,
1158 v_emp_id IN NUMBER,
1159 v_oo_id IN NUMBER,
1160 v_segments OUT fnd_flex_ext.SegmentArray) IS
1161
1162 v_error_message varchar(1000);
1163 v_structure number;
1164 v_expense_account number;
1165 v_line_number number;
1166 v_exist number;
1167 v_return_code varchar2(200);
1168 v_n_segments number;
1169 v_account_id number;
1170 v_account_num varchar2(2000);
1171 l_ret_cd BOOLEAN;
1172 v_delimiter varchar2(100);
1173
1174 CURSOR line_default_account is
1175 SELECT default_code_combination_id employee_default_account_id
1176 from hr_employees_current_v
1177 where employee_id = v_emp_id;
1178
1179 CURSOR item_expense_account is
1180 select msi.expense_account
1181 from mtl_system_items msi,
1182 icx_shopping_carts isc,
1183 icx_shopping_cart_lines iscl
1184 where msi.inventory_item_id(+) = iscl.item_id
1185 AND nvl(msi.ORGANIZATION_ID,
1186 nvl(isc.DESTINATION_ORGANIZATION_ID,
1187 iscl.DESTINATION_ORGANIZATION_ID)) =
1188 nvl(isc.DESTINATION_ORGANIZATION_ID,
1189 iscl.DESTINATION_ORGANIZATION_ID)
1190 and iscl.cart_id = isc.cart_id
1191 and iscl.cart_id = v_cart_id
1192 and iscl.cart_line_id = v_cart_line_id
1193 and nvl(isc.org_id,-9999) = nvl(v_oo_id,-9999)
1194 and nvl(iscl.org_id,-9999) = nvl(v_oo_id,-9999);
1195
1196
1197 CURSOR chart_account_id IS
1198 SELECT CHART_OF_ACCOUNTS_ID
1199 FROM gl_sets_of_books,
1200 financials_system_parameters fsp
1201 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
1202
1203 cursor get_cart_line_number is
1204 select cart_line_number
1205 from icx_shopping_cart_lines
1206 where cart_id = v_cart_id
1207 and cart_line_id = v_cart_line_id;
1208
1209 l_cart_line_number NUMBER := 0;
1210
1211 BEGIN
1212
1213 if icx_sec.validatesession then
1214 open get_cart_line_number;
1215 fetch get_cart_line_number into l_cart_line_number;
1216 close get_cart_line_number;
1217
1218 -- get structure number
1219 open chart_account_id;
1220 fetch chart_account_id into v_structure;
1221 close chart_account_id;
1222
1223 -- get account from customer default
1224 v_account_id := NULL;
1225 v_account_num := NULL;
1226 icx_req_custom.cart_custom_build_req_account(v_cart_line_id,
1227 v_account_num,
1228 v_account_id,
1229 v_return_code);
1230
1231 -- if customer does not return any account id or con-seg of the account
1232 if (v_account_num is NULL) and (v_account_id is NULL) then
1233
1234 -- get the default account
1235 open line_default_account;
1236 fetch line_default_account into v_account_id;
1237 close line_default_account;
1238 if v_account_id is NULL then
1239 open item_expense_account;
1240 fetch item_expense_account into v_account_id;
1241 close item_expense_account;
1242 end if;
1243
1244 if v_account_id is not NULL then
1245 l_ret_cd := fnd_flex_ext.get_segments('SQLGL',
1246 'GL#',
1247 v_structure,
1248 v_account_id,
1249 v_n_segments,
1250 v_segments);
1251 end if;
1252
1253 elsif v_account_num is not NULL then
1254
1255 v_delimiter := fnd_flex_ext.get_delimiter('SQLGL','GL#',v_structure);
1256 v_account_id := fnd_flex_ext.get_ccid('SQLGL',
1257 'GL#',
1258 v_structure,
1259 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
1260 v_account_num);
1261 if v_account_id is not NULL then
1262
1263 l_ret_cd := fnd_flex_ext.get_segments('SQLGL',
1264 'GL#',
1265 v_structure,
1266 v_account_id,
1267 v_n_segments,
1268 v_segments);
1269 end if;
1270
1271 end if;
1272
1273 if l_ret_cd = FALSE then
1274 v_error_message := FND_MESSAGE.GET;
1275 FND_MESSAGE.SET_NAME('ICX','ICX_LINE_NUMBER');
1276 FND_MESSAGE.SET_TOKEN('LINE_NUM_TOKEN',l_cart_line_number);
1277 v_error_message := FND_MESSAGE.GET || ' ' || v_error_message;
1278 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1279 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1280 icx_util.add_error(v_error_message);
1281 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1282 end if;
1283
1284
1285 end if;
1286
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1290 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', l_cart_line_number || ': ' || substr(SQLERRM,1,512));
1291 v_error_message := FND_MESSAGE.GET;
1292 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1293 -- icx_util.add_error(substr(SQLERRM, 12, 512));
1294
1295 END get_default_segs;
1296
1297
1298 PROCEDURE update_account_by_id(v_cart_id IN NUMBER,
1299 v_cart_line_id IN NUMBER,
1300 v_oo_id IN NUMBER,
1301 v_distribution_id IN NUMBER,
1302 v_line_number IN NUMBER) is
1303
1304 v_segments fnd_flex_ext.SegmentArray;
1305 v_n_segments number;
1306 v_structure number;
1307 v_cart_line_number number;
1308 v_error_message varchar2(2000);
1309
1310 CURSOR chart_account_id IS
1311 SELECT CHART_OF_ACCOUNTS_ID
1312 FROM gl_sets_of_books,
1313 financials_system_parameters fsp
1314 WHERE gl_sets_of_books.SET_OF_BOOKS_ID = fsp.set_of_books_id;
1315
1316 v_account_id number;
1317 v_account_num varchar2(2000);
1318 l_ret_cd BOOLEAN;
1319
1320 cursor get_account_id(cartid number,cartline_id number,oo_id number,dist_id number) is
1321 select charge_account_id
1322 from icx_cart_line_distributions
1323 where cart_id = cartid
1324 and cart_line_id = cartline_id
1325 and distribution_id = dist_id
1326 and nvl(org_id,-9999) = nvl(oo_id,-9999);
1327
1328 cursor get_cart_line_number(cartid number, cartline_id number) is
1329 select cart_line_number
1330 from icx_shopping_cart_lines
1331 where cart_id = cartid
1332 and cart_line_id = cartline_id;
1333
1334
1335 begin
1336
1337 if icx_sec.validatesession then
1338
1339 open get_cart_line_number(v_cart_id,v_cart_line_id);
1340 fetch get_cart_line_number into v_cart_line_number;
1341 close get_cart_line_number;
1342
1343 -- get structure number
1344 open chart_account_id;
1345 fetch chart_account_id into v_structure;
1346 close chart_account_id;
1347
1348 -- get account id
1349 open get_account_id(v_cart_id,v_cart_line_id,v_oo_id,v_distribution_id);
1350 fetch get_account_id into v_account_id;
1351 close get_account_id;
1352
1353 if v_account_id is not NULL then
1354 l_ret_cd := fnd_flex_ext.get_segments('SQLGL',
1355 'GL#',
1356 v_structure,
1357 v_account_id,
1358 v_n_segments,
1359 v_segments);
1360 end if;
1361
1362 if l_ret_cd <> FALSE and v_n_segments > 0 then
1363
1364 icx_req_acct2.get_acct_by_segs(v_cart_id,v_line_number,v_segments,
1365 v_structure,v_cart_line_id,v_cart_line_number,
1366 v_n_segments,v_account_num,v_account_id);
1367
1368 update_row(v_cart_line_id,v_oo_id,v_cart_id,v_distribution_id,
1369 v_line_number,v_account_id,v_n_segments,v_segments,
1370 v_account_num);
1371
1372 else
1373
1374 FND_MESSAGE.SET_NAME('ICX', 'ICX_INVALID_ACCOUNT');
1375 FND_MESSAGE.SET_TOKEN('ITEM_TOKEN', v_cart_line_number);
1376 v_error_message := FND_MESSAGE.GET;
1377 FND_MESSAGE.SET_NAME('PO','PO_ZMVOR_DISTRIBUTION');
1378 v_error_message := '(' || FND_MESSAGE.GET || ' ' || v_line_number || ') ' || v_error_message;
1379 icx_util.add_error(v_error_message);
1380 ICX_REQ_SUBMIT.storeerror(v_cart_id, v_error_message,v_line_number,v_cart_line_id);
1381
1382
1383 end if;
1384
1385 end if;
1386 end;
1387
1388 END icx_req_acct2;