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