1 package body icx_req_custom as
2 /* $Header: ICXRQCUB.pls 115.1 99/07/17 03:23:09 porting ship $ */
3
4
5 /* The comments given below are simply my quick hints for writting user
6 defaults and validation. The sql once written should be VALIDATED and
7 TUNED!!! Remeber any overhead you add will result in slower performance.
8 Try to keep the defaulting to the defaulting routines, and the validation
9 to the validation routines. These routines give you a lot of power over
10 the system, use it wisely........
11
12
13 Thanks,
14 KW
15
16 */
17
18
19 /****************************************************************************/
20 /************************* CART TABLES ************************************/
21 /****************************************************************************
22
23
24
25 ICX_SHOPPING_CARTS -- Header info for the Requisition
26
27 CART_ID NOT NULL NUMBER
28 LAST_UPDATE_DATE NOT NULL DATE
29 LAST_UPDATED_BY NOT NULL NUMBER
30 CREATION_DATE NOT NULL DATE
31 CREATED_BY NUMBER
32 SHOPPER_ID NOT NULL NUMBER
33 SAVED_FLAG NOT NULL NUMBER
34 APPROVER_ID NUMBER
35 APPROVER_NAME VARCHAR2(240)
36 NOTE_TO_APPROVER VARCHAR2(240)
37 REQ_NUMBER_SEGMENT1 VARCHAR2(30)
38 HEADER_DESCRIPTION VARCHAR2(240)
39 HEADER_ATTRIBUTE_CATEGORY VARCHAR2(30)
40 HEADER_ATTRIBUTE1 VARCHAR2(150)
41 HEADER_ATTRIBUTE2 VARCHAR2(150)
42 HEADER_ATTRIBUTE3 VARCHAR2(150)
43 HEADER_ATTRIBUTE4 VARCHAR2(150)
44 HEADER_ATTRIBUTE5 VARCHAR2(150)
45 HEADER_ATTRIBUTE6 VARCHAR2(150)
46 HEADER_ATTRIBUTE7 VARCHAR2(150)
47 HEADER_ATTRIBUTE8 VARCHAR2(150)
48 HEADER_ATTRIBUTE9 VARCHAR2(150)
49 HEADER_ATTRIBUTE10 VARCHAR2(150)
50 HEADER_ATTRIBUTE11 VARCHAR2(150)
51 HEADER_ATTRIBUTE12 VARCHAR2(150)
52 HEADER_ATTRIBUTE13 VARCHAR2(150)
53 HEADER_ATTRIBUTE14 VARCHAR2(150)
54 HEADER_ATTRIBUTE15 VARCHAR2(150)
55 NOTE_TO_BUYER VARCHAR2(240)
56 RESERVED_PO_NUM VARCHAR2(220)
57 DESTINATION_TYPE_CODE VARCHAR2(25)
58 DESTINATION_ORGANIZATION_ID NUMBER
59 DELIVER_TO_LOCATION_ID NUMBER
60 DELIVER_TO_REQUESTOR_ID NUMBER
61 NEED_BY_DATE DATE
62 ORG_ID NUMBER
63 DELIVER_TO_LOCATION VARCHAR2(20)
64 DELIVER_TO_REQUESTOR VARCHAR2(240)
65 EMERGENCY_FLAG VARCHAR2(1)
66
67
68 ICX_SHOPPING_CART_LINES --Line level information for the Requisition
69
70 CART_LINE_ID NOT NULL NUMBER
71 LAST_UPDATE_DATE NOT NULL DATE
72 LAST_UPDATED_BY NOT NULL NUMBER
73 CREATION_DATE NOT NULL DATE
74 CREATED_BY NOT NULL NUMBER
75 LAST_UPDATE_LOGIN NUMBER
76 CART_ID NOT NULL NUMBER
77 LINE_ID VARCHAR2(80)
78 ITEM_DESCRIPTION VARCHAR2(240)
79 QUANTITY NUMBER
80 UNIT_PRICE NUMBER
81 ITEM_ID NUMBER
82 ITEM_REVISION VARCHAR2(3)
83 CATEGORY_ID NUMBER
84 UNIT_OF_MEASURE VARCHAR2(25)
85 LINE_TYPE_ID NUMBER
86 EXPENDITURE_TYPE VARCHAR2(30)
87 DESTINATION_ORGANIZATION_ID NUMBER
88 DELIVER_TO_LOCATION_ID NUMBER
89 SUGGESTED_BUYER_ID NUMBER
90 SUGGESTED_VENDOR_NAME VARCHAR2(80)
91 SUGGESTED_VENDOR_SITE VARCHAR2(15)
92 LINE_ATTRIBUTE_CATEGORY VARCHAR2(30)
93 LINE_ATTRIBUTE1 VARCHAR2(150)
94 LINE_ATTRIBUTE2 VARCHAR2(150)
95 LINE_ATTRIBUTE3 VARCHAR2(150)
96 LINE_ATTRIBUTE4 VARCHAR2(150)
97 LINE_ATTRIBUTE5 VARCHAR2(150)
98 LINE_ATTRIBUTE6 VARCHAR2(150)
99 LINE_ATTRIBUTE7 VARCHAR2(150)
100 LINE_ATTRIBUTE8 VARCHAR2(150)
101 LINE_ATTRIBUTE9 VARCHAR2(150)
102 LINE_ATTRIBUTE10 VARCHAR2(150)
103 LINE_ATTRIBUTE11 VARCHAR2(150)
104 LINE_ATTRIBUTE12 VARCHAR2(150)
105 LINE_ATTRIBUTE13 VARCHAR2(150)
106 LINE_ATTRIBUTE14 VARCHAR2(150)
107 LINE_ATTRIBUTE15 VARCHAR2(150)
108 NEED_BY_DATE DATE
109 AUTOSOURCE_DOC_HEADER_ID NUMBER
110 AUTOSOURCE_DOC_LINE_NUM NUMBER
111 PROJECT_ID NUMBER
112 TASK_ID NUMBER
113 EXPENDITURE_ITEM_DATE DATE
114 SUGGESTED_VENDOR_CONTACT VARCHAR2(80)
115 SUGGESTED_VENDOR_PHONE VARCHAR2(20)
116 SUGGESTED_VENDOR_ITEM_NUM VARCHAR2(25)
117 EXPENDITURE_ORGANIZATION_ID NUMBER
118 SUPPLIER_ITEM_NUM VARCHAR2(25)
119 ORG_ID NUMBER
120 EXPRESS_NAME VARCHAR2(25)
121 ITEM_NUMBER VARCHAR2(40)
122 DELIVER_TO_LOCATION VARCHAR2(20)
123 CUSTOM_DEFAULTED CHAR(1)
124 CART_LINE_NUMBER NOT NULL NUMBER
125
126
127 ICX_CART_DISTRIBUTIONS -- Account information turned on at the
128 -- Header level
129
130 CART_ID NOT NULL NUMBER
131 DISTRIBUTION_ID NOT NULL NUMBER
132 LAST_UPDATED_BY NOT NULL NUMBER
133 LAST_UPDATE_DATE NOT NULL DATE
134 LAST_UPDATE_LOGIN NOT NULL NUMBER
135 CREATION_DATE NOT NULL DATE
136 CREATED_BY NOT NULL NUMBER
137 CHARGE_ACCOUNT_SEGMENT1 VARCHAR2(240)
138 CHARGE_ACCOUNT_SEGMENT2 VARCHAR2(25)
139 CHARGE_ACCOUNT_SEGMENT3 VARCHAR2(25)
140 CHARGE_ACCOUNT_SEGMENT4 VARCHAR2(25)
141 CHARGE_ACCOUNT_SEGMENT5 VARCHAR2(25)
142 CHARGE_ACCOUNT_SEGMENT6 VARCHAR2(25)
143 CHARGE_ACCOUNT_SEGMENT7 VARCHAR2(25)
144 CHARGE_ACCOUNT_SEGMENT8 VARCHAR2(25)
145 CHARGE_ACCOUNT_SEGMENT9 VARCHAR2(25)
146 CHARGE_ACCOUNT_SEGMENT10 VARCHAR2(25)
147 CHARGE_ACCOUNT_SEGMENT11 VARCHAR2(25)
148 CHARGE_ACCOUNT_SEGMENT12 VARCHAR2(25)
149 CHARGE_ACCOUNT_SEGMENT13 VARCHAR2(25)
150 CHARGE_ACCOUNT_SEGMENT14 VARCHAR2(25)
151 CHARGE_ACCOUNT_SEGMENT15 VARCHAR2(25)
152 CHARGE_ACCOUNT_SEGMENT16 VARCHAR2(25)
153 CHARGE_ACCOUNT_SEGMENT17 VARCHAR2(25)
154 CHARGE_ACCOUNT_SEGMENT18 VARCHAR2(25)
155 CHARGE_ACCOUNT_SEGMENT19 VARCHAR2(25)
156 CHARGE_ACCOUNT_SEGMENT20 VARCHAR2(25)
157 CHARGE_ACCOUNT_SEGMENT21 VARCHAR2(25)
158 CHARGE_ACCOUNT_SEGMENT22 VARCHAR2(25)
159 CHARGE_ACCOUNT_SEGMENT23 VARCHAR2(25)
160 CHARGE_ACCOUNT_SEGMENT24 VARCHAR2(25)
161 CHARGE_ACCOUNT_SEGMENT25 VARCHAR2(25)
162 CHARGE_ACCOUNT_SEGMENT26 VARCHAR2(25)
163 CHARGE_ACCOUNT_SEGMENT27 VARCHAR2(25)
164 CHARGE_ACCOUNT_SEGMENT28 VARCHAR2(25)
165 CHARGE_ACCOUNT_SEGMENT29 VARCHAR2(25)
166 CHARGE_ACCOUNT_SEGMENT30 VARCHAR2(25)
167 ORG_ID NUMBER
168
169 ICX_CART_LINE_DISTRIBUTIONS -- Account information turned on at the
170 -- Line level
171
172 CART_LINE_ID NOT NULL NUMBER
173 DISTRIBUTION_ID NOT NULL NUMBER
174 LAST_UPDATED_BY NOT NULL NUMBER
175 LAST_UPDATE_DATE NOT NULL DATE
176 LAST_UPDATE_LOGIN NOT NULL NUMBER
177 CREATION_DATE NOT NULL DATE
178 CREATED_BY NOT NULL NUMBER
179 CHARGE_ACCOUNT_ID NUMBER
180 CHARGE_ACCOUNT_SEGMENT1 VARCHAR2(240)
181 CHARGE_ACCOUNT_SEGMENT2 VARCHAR2(25)
182 CHARGE_ACCOUNT_SEGMENT3 VARCHAR2(25)
183 CHARGE_ACCOUNT_SEGMENT4 VARCHAR2(25)
184 CHARGE_ACCOUNT_SEGMENT5 VARCHAR2(25)
185 CHARGE_ACCOUNT_SEGMENT6 VARCHAR2(25)
186 CHARGE_ACCOUNT_SEGMENT7 VARCHAR2(25)
187 CHARGE_ACCOUNT_SEGMENT8 VARCHAR2(25)
188 CHARGE_ACCOUNT_SEGMENT9 VARCHAR2(25)
189 CHARGE_ACCOUNT_SEGMENT10 VARCHAR2(25)
190 CHARGE_ACCOUNT_SEGMENT11 VARCHAR2(25)
191 CHARGE_ACCOUNT_SEGMENT12 VARCHAR2(25)
192 CHARGE_ACCOUNT_SEGMENT13 VARCHAR2(25)
193 CHARGE_ACCOUNT_SEGMENT14 VARCHAR2(25)
194 CHARGE_ACCOUNT_SEGMENT15 VARCHAR2(25)
195 CHARGE_ACCOUNT_SEGMENT16 VARCHAR2(25)
196 CHARGE_ACCOUNT_SEGMENT17 VARCHAR2(25)
197 CHARGE_ACCOUNT_SEGMENT18 VARCHAR2(25)
198 CHARGE_ACCOUNT_SEGMENT19 VARCHAR2(25)
199 CHARGE_ACCOUNT_SEGMENT20 VARCHAR2(25)
200 CHARGE_ACCOUNT_SEGMENT21 VARCHAR2(25)
201 CHARGE_ACCOUNT_SEGMENT22 VARCHAR2(25)
202 CHARGE_ACCOUNT_SEGMENT23 VARCHAR2(25)
203 CHARGE_ACCOUNT_SEGMENT24 VARCHAR2(25)
204 CHARGE_ACCOUNT_SEGMENT25 VARCHAR2(25)
205 CHARGE_ACCOUNT_SEGMENT26 VARCHAR2(25)
206 CHARGE_ACCOUNT_SEGMENT27 VARCHAR2(25)
207 CHARGE_ACCOUNT_SEGMENT28 VARCHAR2(25)
208 CHARGE_ACCOUNT_SEGMENT29 VARCHAR2(25)
209 CHARGE_ACCOUNT_SEGMENT30 VARCHAR2(25)
210 DIST_ATTRIBUTE_CATEGORY VARCHAR2(30)
211 DISTRIBUTION_ATTRIBUTE1 VARCHAR2(150)
212 DISTRIBUTION_ATTRIBUTE2 VARCHAR2(150)
213 DISTRIBUTION_ATTRIBUTE3 VARCHAR2(150)
214 DISTRIBUTION_ATTRIBUTE4 VARCHAR2(150)
215 DISTRIBUTION_ATTRIBUTE5 VARCHAR2(150)
216 DISTRIBUTION_ATTRIBUTE6 VARCHAR2(150)
217 DISTRIBUTION_ATTRIBUTE7 VARCHAR2(150)
218 DISTRIBUTION_ATTRIBUTE8 VARCHAR2(150)
219 DISTRIBUTION_ATTRIBUTE9 VARCHAR2(150)
220 DISTRIBUTION_ATTRIBUTE10 VARCHAR2(150)
221 DISTRIBUTION_ATTRIBUTE11 VARCHAR2(150)
222 DISTRIBUTION_ATTRIBUTE12 VARCHAR2(150)
223 DISTRIBUTION_ATTRIBUTE13 VARCHAR2(150)
224 DISTRIBUTION_ATTRIBUTE14 VARCHAR2(150)
225 DISTRIBUTION_ATTRIBUTE15 VARCHAR2(150)
226 ACCRUAL_ACCOUNT_ID NUMBER
227 VARIANCE_ACCOUNT_ID NUMBER
228 BUDGET_ACCOUNT_ID NUMBER
229 ORG_ID NUMBER
230 CART_ID NOT NULL NUMBER
231
232
233
234 */
235
236
237 -------------------------------------------------------------------------
238 procedure add_user_error(v_cart_id number, error_message varchar2) is
239 -------------------------------------------------------------------------
240
241
242 begin
243 icx_util.add_error(error_message);
244 ICX_REQ_SUBMIT.storeerror(v_cart_id, error_message);
245
246 end;
247
248
249
250
251 -------------------------------------------------------------------------
252 procedure cart_custom_build_req_account(v_cart_line_id IN NUMBER,
253 V_ACCOUNT_NUM OUT VARCHAR2,
254 V_ACCOUNT_ID OUT NUMBER,
255 RETURN_CODE OUT VARCHAR2) IS
256 -------------------------------------------------------------------------
257
258
259 /* The following is a cursor you can use to get the infomation
260 available about the requisition line
261
262
263 CURSOR get_info is
264 SELECT isc.ANY_COLUMN_OF_ICX_SHOPPING_CARTS
265 iscl.ANY_COLUMN_OF_ICX_SHOPPING_CART_LINES
266 iscd.ANY_COLUMN_OF_ICX_CART_DISTRIBUTIONS
267 iscld.ANY_COLUMN_OF_ICX_CART_LINE_DISTRIBUTIONS
268 hecv.default_code_combination_id employee_default_account_id,
269 hecv.organization_id employee_org_id,
270 hecv.business_group_id employee_bus_group_id,
271 fsp.org_id po_org_id,
272 fsp.set_of_books_id,
273 msi.expense_account
274 FROM financials_system_parameters fsp,
275 hr_employees_current_v hecv,
276 mtl_system_items msi,
277 icx_cart_distributions iscd,
278 icx_cart_line_distributions iscld,
279 icx_shopping_carts isc,
280 icx_shopping_cart_lines iscl
281 WHERE isc.cart_id = iscl.cart_id
282 AND iscl.cart_line_id = v_cart_line_id
283 AND iscd.cart_id = iscl.cart_id
284 AND iscld.cart_line_id = v_cart_line_id
285 AND msi.INVENTORY_ITEM_ID (+) = iscl.ITEM_ID
286 AND nvl(msi.ORGANIZATION_ID, isc.DESTINATION_ORG_ID) = isc.DESTINATION_ORG_ID
287 AND hecv.EMPLOYEE_ID = isc.shopper_id;
288
289
290 If you decide to not use some of the above information (such as the
291 info from ht_employees_current_v) it is faster to remove it from the
292 join and the select.
293
294 You can then OPEN the cursor, FETCH the info, and figure out a
295 ACCOUNT. Remember to CLOSE the CURSOR.
296
297 You can add any table join to the above or do your own SQL to build the
298 account
299
300 */
301
302
303
304
305 BEGIN
306 -- PLACE CUSTOM CODE HERE!!!!!
307
308 -- generate new charge account id
309
310 V_ACCOUNT_NUM := NULL;
311 V_ACCOUNT_ID := NULL;
312 RETURN_CODE := null;
313
314 END;
315
316
317
318 -------------------------------------------------------------------------
319 procedure cart_custom_build_req_account2(v_cart_line_id IN NUMBER,
320 VARIANCE_ACCOUNT_ID OUT NUMBER,
321 BUDGET_ACCOUNT_ID OUT NUMBER,
322 ACCRUAL_ACCOUNT_ID OUT NUMBER,
323 RETURN_CODE OUT VARCHAR2) is
324 -------------------------------------------------------------------------
325
326 /* To get at the information you need you can use the following cursor
327
328 CURSOR get_info is
329 SELECT isc.ANY_COLUMN_YOU_WANT_FROM_ICX_SHOPPING_CARTS
330 iscl.ANY_COLUMN_OF_ICX_SHOPPING_CART_LINES
331 iscd.ANY_COLUMN_OF_ICX_CART_DISTRIBUTIONS
332 iscld.ANY_COLUMN_OF_ICX_CART_LINE_DISTRIBUTIONS
333 FROM icx_cart_distributions iscd,
334 icx_cart_line_distributions iscd,
335 icx_shopping_cart_lines iscl,
336 icx_shopping_carts isc
337 WHERE isc.cart_id = iscl.cart_id
338 AND isc.cart_id = iscd.cart_id
339 AND iscl.cart_line_id = v_cart_line_id
340 AND iscl.cart_line_id = iscld.cart_line_id;
341
342
343 You can then build the correct Accounts in the same way you did above
344 */
345 BEGIN
346 VARIANCE_ACCOUNT_ID := NULL;
347 BUDGET_ACCOUNT_ID := NULL;
348 ACCRUAL_ACCOUNT_ID := NULL;
349 RETURN_CODE := NULL;
350 end;
351
352
353 -------------------------------------------------------------------------
354 procedure po_custom_build_req_account(EMPLOYEE_ID IN NUMBER,
355 employee_default_account_id IN NUMBER,
356 employee_org_id IN NUMBER,
357 employee_bus_group_id NUMBER,
358 po_org_id IN NUMBER,
359 NEED_BY_DATE IN DATE,
360 DESTINATION_TYPE IN VARCHAR2,
361 DESTINATION_ORG_ID IN NUMBER,
362 SITE_ID IN NUMBER,
363 set_of_books_id IN NUMBER,
364 ITEM_ID IN NUMBER,
365 ITEM_REVISION IN VARCHAR2,
366 ITEM_DESCRIPTION IN VARCHAR2,
367 item_default_account_id IN NUMBER,
368 UNIT_OF_MEASURE IN VARCHAR2,
369 QUANTITY IN NUMBER,
370 PRICE IN NUMBER,
371 SUPPLIER_ITEM_NUM IN VARCHAR2,
372 CATEGORY_ID IN NUMBER,
373 LINE_TYPE IN NUMBER,
374 SUPPLIER IN VARCHAR2,
375 SUPPLIER_SITE IN VARCHAR2,
376 SOURCE_DOC_NUM IN VARCHAR2,
377 SOURCE_LINE_NUM IN NUMBER,
378 CHARGE_ACCT_LINE_SEGMENTS IN VARCHAR2,
379 ACCOUNT_NUM OUT VARCHAR2,
380 CHARGE_ACCOUNT_ID OUT NUMBER,
381 RETURN_CODE OUT VARCHAR2) IS
382 -------------------------------------------------------------------------
383
384 l_new_charge_account_id NUMBER;
385 l_new_seg VARCHAR2(1000);
386 pos NUMBER;
387
388 cursor getChargeAccount(new_seg varchar2) is
389 select code_combination_id
390 from gl_code_combinations_kfv
391 where concatenated_segments = rtrim(l_new_seg);
392
393
394 BEGIN
395 -- PLACE CUSTOM CODE HERE!!!!!
396
397 -- generate new charge account id
398
399 ACCOUNT_NUM := NULL;
400 CHARGE_ACCOUNT_ID := NULL;
401 l_new_charge_account_id := NULL;
402 l_new_seg := CHARGE_ACCT_LINE_SEGMENTS;
403
404 if l_new_seg is NOT NULL then
405
406 open getChargeAccount(l_new_seg);
407 fetch getChargeAccount into l_new_charge_account_id;
408 close getChargeAccount;
409
410 -- send back the new account id
411 -- otherwise pass in the old charge account id
412 -- and pass that back out
413 if l_new_charge_account_id is not NULL then
414 CHARGE_ACCOUNT_ID := l_new_charge_account_id;
415 end if;
416
417 end if;
418
419 RETURN_CODE := null;
420
421 exception
422 when NO_DATA_FOUND then
423 CHARGE_ACCOUNT_ID := NULL;
424 ACCOUNT_NUM := NULL;
425 RETURN_CODE := NULL;
426 END;
427
428
429
430 -------------------------------------------------------------------------
431 procedure po_custom_build_req_account2(EMPLOYEE_ID IN NUMBER,
432 employee_default_account_id IN NUMBER,
433 employee_org_id IN NUMBER,
434 employee_bus_group_id NUMBER,
435 po_org_id IN NUMBER,
436 NEED_BY_DATE IN DATE,
437 DESTINATION_TYPE IN VARCHAR2,
438 DESTINATION_ORG_ID IN NUMBER,
439 SITE_ID IN NUMBER,
440 set_of_books_id IN NUMBER,
441 ITEM_ID IN NUMBER,
442 ITEM_REVISION IN VARCHAR2,
443 ITEM_DESCRIPTION IN VARCHAR2,
444 item_default_account_id IN NUMBER,
445 UNIT_OF_MEASURE IN VARCHAR2,
446 QUANTITY IN NUMBER,
447 PRICE IN NUMBER,
448 SUPPLIER_ITEM_NUM IN VARCHAR2,
449 CATEGORY_ID IN NUMBER,
450 LINE_TYPE IN NUMBER,
451 SUPPLIER IN VARCHAR2,
452 SUPPLIER_SITE IN VARCHAR2,
453 SOURCE_DOC_NUM IN VARCHAR2,
454 SOURCE_LINE_NUM IN NUMBER,
455 CHARGE_ACCT_LINE_SEGMENTS IN VARCHAR2,
456 CHARGE_ACCOUNT_ID IN NUMBER,
457 ACCOUNT_NUM OUT VARCHAR2,
458 VARIANCE_ACCOUNT_ID OUT NUMBER,
459 BUDGET_ACCOUNT_ID OUT NUMBER,
460 ACCRUAL_ACCOUNT_ID OUT NUMBER,
461 RETURN_CODE OUT VARCHAR2) is
462 -------------------------------------------------------------------------
463 BEGIN
464 ACCOUNT_NUM := NULL;
465 VARIANCE_ACCOUNT_ID := NULL;
466 BUDGET_ACCOUNT_ID := NULL;
467 ACCRUAL_ACCOUNT_ID := NULL;
468 RETURN_CODE := NULL;
469 end;
470
471 -------------------------------------------------------------------------
472 procedure reqs_default_lines( p_emergency IN VARCHAR2,
473 cartId IN number) is
474 -------------------------------------------------------------------------
475
476 /* You can default any informaion into the lines. Please be carefull......
477
478 You can do this in one of two ways. Line by line, or with set processing.
479 Line by line will allow you to do specific defaults at a line level
480 while set processing will update all the columns at the same time. It
481 should be noted that set processing is FASTER...
482
483 Please note that this procedure will be run ONCE per ADD. This means that
484 if a user adds 6 different items from a Template, this procedure will only
485 be called ONCE.
486
487 You will use the CUSTOM_DEFAULTED flag in icx_shopping_cart_lines to
488 determine which records you have already done the defaulting for.
489 When we create the record, we set CUSTUM_DEFAULTED to 'N'. When you do
490 your defaulting, set the CUSTUM_DEFAULTED to 'Y'. I am not going to
491 force this, its your defaulting mechanism, it is just a suggestion.
492
493
494 LINE BY LINE -- Use a Cursor and loop through
495
496
497 CURSOR get_info is
498 SELECT isc.ANY_SHOPPING_CART_COLUMN
499 iscl.ANY_SHOPPING_CART_LINE_COLUMN
500 iscld.ANY_CART_LINE_DISTRIBUTIONS_COLUMN
501 FROM icx_shopping_carts isc,
502 icx_cart_line_distributions iscld,
503 icx_shopping_cart_lines iscl
504 WHERE isc.cart_id = cartId
505 AND iscl.cart_id = cartId
506 AND iscld.cart_line_id = iscl.cart_line_id
507 AND isc.CUSTOM_DEFAULTED = 'N';
508
509
510 Then simply LOOP through the record and do any updates you want
511
512
513 FOR prec in get_info LOOP
514
515 UPDATE icx_shopping_cart_lines
516 set WHATEVER = WHATEVER,
517 CUSTOM_DEFAULTED = 'Y'
521 set WHATEVER = WHATEVER
518 where cart_line_id = CART_LINE_ID FROM YOUR CURSOR;
519
520 UPDATE icx_cart_line_distributions
522 where cart_line_id = CART_LINE_ID FROM YOUR CURSOR;
523
524 end LOOP;
525
526
527 SET PROCESSING -- simply update the tables
528
529 -- do the distributions first
530 update icx_cart_line_distributions
531 set WHATEVER = WHATEVER
532 where cart_line_id in
533 (SELECT cart_line_id
534 from icx_shopping_cart_lines
535 where cart_id = cartId
536 and CUSTOM_DEFAULTED = 'N');
537
538
539 update icx_shopping_cart_lines
540 set (WHATEVER, CUSTOM_DEFAULTED) =
541 (SELECT WHATEVER, 'Y'
542 FROM WHEREVER)
543 where cart_id = cartId
544 and CUSTOM_DEFAULTED = 'N';
545
546
547 */
548
549 begin
550 -- Custom default code will come here
551 -- do nothing;
552 null;
553 end reqs_default_lines;
554
555
556 -------------------------------------------------------------------------
557 procedure reqs_default_head( p_emergency IN VARCHAR2,
558 v_cart_id IN NUMBER) is
559
560 -------------------------------------------------------------------------
561
562 /* The default head is run once when the Header record is created. This
563 occurs when the user enters the Req program. Here you can default in
564 any values you wish. Again BE CAREFUL......
565
566
567 update icx_shopping_carts
568 set WHATEVER = WHATEVER
569 where cart_id = v_cart_id;
570
571 update icx_cart_distributions
572 set WHATEVER = WHATEVER
573 where cart_id = v_cart_id;
574
575
576 */
577
578
579 begin
580 -- Custom default code will come here
581 -- do nothing;
582 null;
583 end reqs_default_head;
584
585
586 -------------------------------------------------------------------------
587 procedure reqs_validate_line(p_emergency IN VARCHAR2,
588 v_cart_id number) is
589 -------------------------------------------------------------------------
590
591 /* Validation of the line
592
593 Please do validation of the lines here. Remember this routine is run
594 ONCE for all lines. It is run during the submit of the requisition.
595 If an error occurs, you should put an error on the error stack. This
596 will stop the submission. Please make ALL your error checks here. IF
597 you find an error, put it on the error stack and continue checking.
598 Each error you report, plus any we find, will then be reproted to the
599 user at the same time, and in the same way.
600
601 To add a message to the error stack use
602 add_user_error(v_cart_id, 'YOUR ERROR MESSAGE');
603
604 To get at the lines you can use line processing or set processing.
605
606 LINE
607
608 CURSOR get_info is
609 SELECT isc.ANY_COLUMN_YOU_WANT_FROM_ICX_SHOPPING_CARTS
610 iscl.ANY_COLUMN_OF_ICX_SHOPPING_CART_LINES
611 iscd.ANY_COLUMN_OF_ICX_CART_DISTRIBUTIONS
612 iscld.ANY_COLUMN_OF_ICX_CART_LINE_DISTRIBUTIONS
613 FROM icx_cart_distributions iscd,
614 icx_cart_line_distributions iscd,
615 icx_shopping_cart_lines iscl,
616 icx_shopping_carts isc
617 WHERE isc.cart_id = iscl.cart_id
618 AND isc.cart_id = iscd.cart_id
619 AND iscl.cart_line_id = v_cart_line_id
620 AND iscl.cart_line_id = iscld.cart_line_id;
621
622
623
624 SET -- Completely depends on your logic
625
626 Please only do Line logic, and please TRAP ALL YOUR ERRORS!!!!!
627
628
629 */
630
631 begin
632 -- Custom validation code will come here
633 -- do nothing;
634 null;
635 end reqs_validate_line;
636
637
638 -------------------------------------------------------------------------
639 procedure reqs_validate_head(p_emergency IN VARCHAR2,
640 v_cart_id IN NUMBER) is
641 -------------------------------------------------------------------------
642
643 /* You can do your own header validation here. As in lines, you can
644 put your errors directly to our error stack. In this way, your errors
645 look exactly like errors raise by Oracle. If you find an error, please
646 put it on the stack and continue. In this way, all errors will be reported
647 to the user.
648
649 Please do only Header logic, and please TRAP ALL YOUR ERRORS!!!!!
650
651
652 To add a message to the error stack use
653 add_user_error(v_cart_id, 'YOUR ERROR MESSAGE');
654
655 CURSOR get_info is
656 SELECT isc.ANY_COLUMN_YOU_WANT_FROM_ICX_SHOPPING_CARTS
657 iscd.ANY_COLUMN_OF_ICX_CART_DISTRIBUTIONS
658 FROM icx_cart_distributions iscd,
659 icx_shopping_carts isc
660 WHERE isc.cart_id = iscd.cart_id
661 AND isc.cart_id = v_cart_id;
662
663
664 */
665
666 begin
667 -- Custom validation code will come here
668 -- do nothing;
669 null;
670 end reqs_validate_head;
671
672 end icx_req_custom;
[Home] [Help]