[Home] [Help]
PACKAGE BODY: APPS.PO_REQIMP_PKG
Source
1 PACKAGE BODY PO_REQIMP_PKG AS
2 /* $Header: jaaupirb.pls 120.5.12010000.2 2008/08/04 14:32:28 vgadde ship $ */
3
4 -- ** Declare global variables
5 l_error_msg po_interface_errors.error_message%TYPE ;
6 l_error_flag number;
7 l_index NATURAL;
8
9 g_count NUMBER := 0; -- Bug 3032472 : Counts number of rec in PO_APPROVED_SUPPLIER_LIST
10 g_autosource_flag VARCHAR2(1); -- Bug 3032472 : To handle Min-Max set it to global variable. When Min-Max, 'P' will be populated.
11
12 -- ** Declare exceptions
13 l_incorrect_table EXCEPTION;
14 l_null_segment EXCEPTION;
15 l_invalid_segment EXCEPTION;
16 l_no_rows_updated EXCEPTION;
17 l_null_exp_acct EXCEPTION;
18 l_update_failure EXCEPTION;
19 l_no_data_found EXCEPTION;
20
21
22
23 -- *
24 -- ** Create JA_AU_GET_CONV_RATES procedure
25 -- *
26
27 PROCEDURE JA_AU_GET_CONV_RATES
28 (x_item_id IN
29 po_requisitions_interface.item_id%TYPE,
30 x_unit_of_purchase IN
31 po_requisitions_interface.unit_of_measure%TYPE,
32 x_unit_of_measure IN
33 po_requisitions_interface.unit_of_measure%TYPE,
34 x_to_rate OUT NOCOPY
35 mtl_uom_conversions.conversion_rate%TYPE,
36 x_item_uom_class OUT NOCOPY
37 mtl_uom_conversions.uom_class%TYPE,
38 x_from_rate OUT NOCOPY
39 mtl_uom_conversions.conversion_rate%TYPE,
40 x_from_class OUT NOCOPY
41 mtl_uom_conversions.uom_class%TYPE)
42 IS
43 CURSOR l_conversion_rates
44 IS SELECT NVL(t.conversion_rate,0),
45 t.uom_class,
46 NVL(f.conversion_rate,0),
47 f.uom_class
48 FROM mtl_uom_conversions t,
49 mtl_uom_conversions f
50 WHERE t.inventory_item_id IN (x_item_id,0)
51 AND t.unit_of_measure = x_unit_of_purchase
52 AND f.inventory_item_id IN (x_item_id,0)
53 AND f.unit_of_measure = x_unit_of_measure
54 ORDER BY t.inventory_item_id desc, f.inventory_item_id desc ;
55
56 BEGIN
57
58 OPEN l_conversion_rates ;
59
60 FETCH l_conversion_rates
61 INTO x_to_rate,
62 x_item_uom_class,
63 x_from_rate,
64 x_from_class ;
65
66 CLOSE l_conversion_rates ;
67
68 EXCEPTION
69 WHEN OTHERS THEN NULL;
70 END JA_AU_GET_CONV_RATES ;
71
72
73 -- *
74 -- ** Create JA_AU_CONV_PRICE procedure
75 -- *
76
77 PROCEDURE JA_AU_CONV_PRICE
78 (
79 x_from_rate IN
80 mtl_uom_conversions.conversion_rate%TYPE,
81 x_to_rate IN
82 mtl_uom_conversions.conversion_rate%TYPE,
83 x_class_rate IN
84 mtl_uom_class_conversions.conversion_rate%TYPE,
85 x_unit_price IN OUT NOCOPY
86 po_requisitions_interface.unit_price%TYPE
87 )
88 IS
89 BEGIN
90
91 x_unit_price := x_unit_price / x_from_rate ;
92
93 x_unit_price := x_unit_price / x_class_rate ;
94
95 if x_to_rate = 0 THEN
96 x_unit_price := 0 ;
97 ELSE
98 x_unit_price := x_unit_price * x_to_rate ;
99 END IF ;
100
101 SELECT round(x_unit_price,2)
102 INTO x_unit_price
103 FROM dual ;
104
105 EXCEPTION
106 WHEN OTHERS THEN NULL;
107 END JA_AU_CONV_PRICE;
108
109
110
111 -- *
112 -- ** Create JA_AU_GET_CLASS_RATE procedure
113 -- *
114
115 PROCEDURE JA_AU_GET_CLASS_RATE
116 (x_item_id IN
117 po_requisitions_interface.item_id%TYPE,
118 x_item_uom_class IN
119 mtl_uom_conversions.uom_class%TYPE,
120 x_from_class IN
121 mtl_uom_conversions.uom_class%TYPE,
122 x_class_rate OUT NOCOPY
123 mtl_uom_class_conversions.conversion_rate%TYPE)
124 IS
125 BEGIN
126
127 SELECT nvl(conversion_rate,0)
128 INTO x_class_rate
129 FROM mtl_uom_class_conversions
130 WHERE inventory_item_id = x_item_id
131 AND to_uom_class = x_item_uom_class
132 AND from_uom_class = x_from_class ;
133
134 EXCEPTION
135 WHEN OTHERS THEN NULL;
136 END JA_AU_GET_CLASS_RATE ;
137
138
139 -- *
140 -- ** Create JA_AU_REORDER_UOM_CONVERT procedure
141 -- *
142
143 PROCEDURE JA_AU_REORDER_UOM_CONVERT
144 (x_item_id IN number,
145 x_from_unit IN po_requisitions_interface.unit_of_measure%TYPE,
146 x_to_unit IN po_requisitions_interface.unit_of_measure%TYPE,
147 x_quantity IN OUT NOCOPY po_requisitions_interface.quantity%TYPE,
148 x_unit_price IN OUT NOCOPY po_requisitions_interface.unit_price%TYPE
149 )
150 IS
151
152 l_quantity po_requisitions_interface.quantity%TYPE;
153 l_unit_price po_requisitions_interface.unit_price%TYPE;
154 l_from_rate mtl_uom_conversions.conversion_rate%TYPE;
155 l_to_rate mtl_uom_conversions.conversion_rate%TYPE;
156 l_class_rate mtl_uom_class_conversions.conversion_rate%TYPE;
157 l_from_class mtl_uom_conversions.uom_class%TYPE;
158 l_item_uom_class mtl_uom_conversions.uom_class%TYPE;
159 l_dummy char(1);
160
161 BEGIN
162
163 IF x_quantity is null THEN
164 x_quantity := 0 ;
165 END IF ;
166
167 IF x_unit_price is null THEN
168 x_unit_price := 0 ;
169 END IF ;
170
171 l_quantity := x_quantity ;
172 l_unit_price := x_unit_price ;
173 l_from_rate := 1;
174 l_to_rate := 1;
175 l_class_rate := 1 ;
176
177
178 IF x_to_unit = x_from_unit THEN
179 -- Bug 3032472 : Min-Max is treated differently.
180 -- need rounding when from/to uom are same
181 IF g_autosource_flag = 'P' THEN
182 GOTO round_qty;
183 ELSE
184 -- this is the original logic from 11
185 GOTO end_proc;
186 END IF;
187
188 END IF;
189
190
191 JA_AU_get_conv_rates(x_item_id,
192 x_to_unit,
193 x_from_unit,
194 l_to_rate,
195 l_item_uom_class,
196 l_from_rate,
197 l_from_class) ;
198
199
200 IF l_from_class = l_item_uom_class THEN
201 GOTO calculate ;
202 ELSE
203 JA_AU_get_class_rate(x_item_id,
204 l_item_uom_class,
205 l_from_class,
206 l_class_rate) ;
207 END IF ;
208
209
210 <<calculate>>
211
212 l_quantity := l_from_rate * x_quantity ;
213
214
215 l_quantity := l_quantity / l_class_rate ;
216
217 if l_to_rate = 0 THEN
218 GOTO set_qty_zero ;
219 ELSE
220 l_quantity := l_quantity / l_to_rate ;
221
222
223 GOTO calculate_unit_price ;
224 END IF ;
225
226 <<set_qty_zero>>
227
228 l_quantity := 0 ;
229
230 <<calculate_unit_price>>
231
232 -- *
233 -- ** Convert price
234 -- *
235
236 JA_AU_conv_price(l_from_rate,
237 l_to_rate,
238 l_class_rate,
239 l_unit_price );
240
241 <<round_qty>>
242
243
244 SELECT ceil(l_quantity)
245 INTO l_quantity
246 FROM dual ;
247
248 <<end_proc>>
249
250 -- Bug 3032472
251 IF g_count <> 0 THEN
252 -- ASL is used
253 x_quantity := l_quantity * l_to_rate / l_from_rate;
254
255 ELSE
256 x_quantity := l_quantity ;
257 END IF;
258
259 x_unit_price := l_unit_price ;
260
261 EXCEPTION
262 WHEN OTHERS THEN NULL;
263 END JA_AU_REORDER_UOM_CONVERT ;
264
265
266 -- +=======================================================================+
267 -- | Procedure Name: JA_AU_PRICE_BREAK
268 -- | Description: Gets best price break from quotation for a qty
269 -- | Arguments: IN : x_po_line_id
270 -- | x_unit_of_purchase
271 -- | x_quantity
272 -- | OUT : x_unit_price
273 -- +=======================================================================+
274
275 PROCEDURE JA_AU_PRICE_BREAK
276 (x_po_line_id IN po_lines.po_line_id%TYPE,
277 x_unit_of_purchase IN po_line_locations.unit_meas_lookup_code%TYPE,
278 x_quantity IN po_line_locations.quantity%TYPE,
279 x_unit_price OUT NOCOPY po_line_locations.price_override%TYPE)
280 IS
281
282 CURSOR l_price_break
283 IS SELECT poll.price_override
284 FROM po_line_locations poll
285 WHERE price_override is not null
286 AND sysdate between nvl(start_date,sysdate-1)
287 and nvl(end_date,sysdate+1)
288 AND (poll.quantity is null
289 OR
290 poll.quantity <= x_quantity)
291 AND unit_meas_lookup_code = x_unit_of_purchase
292 AND po_line_id = x_po_line_id
293 -- Bug : 4236157
294 AND poll.shipment_type <> 'PREPAYMENT'
295 ORDER BY price_override asc ;
296
297 BEGIN
298
299 OPEN l_price_break ;
300
301 FETCH l_price_break INTO x_unit_price ;
302
303 CLOSE l_price_break ;
304
305
306 EXCEPTION
307 WHEN OTHERS THEN NULL;
308 END JA_AU_PRICE_BREAK ;
309
310
311 -- *
312 -- ** Create JA_AU_SUGGESTED_DOCUMENT procedure
313 -- *
314
315 PROCEDURE JA_AU_SUGGESTED_DOCUMENT
316 (x_item_id IN po_requisitions_interface.item_id%TYPE,
317 x_document_header_id OUT NOCOPY po_requisitions_interface.autosource_doc_header_id%TYPE,
318 x_document_line_num OUT NOCOPY po_requisitions_interface.autosource_doc_line_num%TYPE,
319 x_po_line_id OUT NOCOPY po_lines.po_line_id%TYPE,
320 x_vendor_product_number OUT NOCOPY po_requisitions_interface.suggested_vendor_item_num%TYPE ,
321 x_vendor_id OUT NOCOPY po_requisitions_interface.suggested_vendor_id%TYPE ,
322 x_vendor_site_id OUT NOCOPY po_requisitions_interface.suggested_vendor_site_id%TYPE ,
323 x_vendor_contact_id OUT NOCOPY po_requisitions_interface.suggested_vendor_contact_id%TYPE ,
324 x_vendor_name OUT NOCOPY po_requisitions_interface.suggested_vendor_name%TYPE ,
325 x_vendor_site_code OUT NOCOPY po_requisitions_interface.suggested_vendor_site%TYPE ,
326 x_unit_price OUT NOCOPY po_requisitions_interface.unit_price%TYPE ,
327 x_unit_of_purchase OUT NOCOPY po_lines.unit_meas_lookup_code%TYPE ,
328 x_suggested_vendor_id IN po_requisitions_interface.suggested_vendor_id%TYPE ,
329 x_suggested_vendor_site_id IN po_requisitions_interface.suggested_vendor_site_id%TYPE
330 )
331 IS
332
333 CURSOR l_suggested_document IS
334 SELECT pad.document_header_id,
335 pol.line_num,
336 pol.po_line_id,
337 pol.vendor_product_num,
338 poh.vendor_id,
339 poh.vendor_site_id ,
340 poh.vendor_contact_id,
341 pov.vendor_name,
342 povs.vendor_site_code,
343 nvl(pol.unit_price,0),
344 pol.unit_meas_lookup_code
345 FROM po_autosource_documents pad,
346 po_headers poh,
347 po_lines pol,
348 po_vendors pov,
349 po_vendor_sites povs,
350 po_autosource_vendors ven,
351 po_autosource_rules rul
352 WHERE pad.autosource_rule_id = rul.autosource_rule_id
353 AND pad.vendor_id = ven.vendor_id
354 AND ven.autosource_rule_id = rul.autosource_rule_id
355 AND ven.autosource_rule_id = pad.autosource_rule_id
356 AND pad.document_header_id = poh.po_header_id
357 AND pad.document_line_id = pol.po_line_id
358 AND (( poh.type_lookup_code = 'BLANKET'
359 AND poh.approved_flag = 'Y'
360 AND nvl(poh.frozen_flag,'N') = 'N'
361 AND nvl(poh.cancel_flag,'N') = 'N'
362 AND nvl(pol.cancel_flag,'N') = 'N')
363 OR
364 ( poh.type_lookup_code = 'QUOTATION'
365 AND poh.status_lookup_code = 'A'))
366 AND poh.vendor_id = pov.vendor_id
367 AND poh.vendor_site_id = povs.vendor_site_id(+)
368 AND poh.vendor_id = povs.vendor_id(+)
369 AND SYSDATE between nvl(poh.start_date, SYSDATE)
370 AND nvl(poh.end_date, SYSDATE+1)
371 AND rul.item_id = x_item_id
372 AND sysdate between nvl(rul.start_date, sysdate)
373 AND nvl(rul.end_date, sysdate+1)
374 ORDER BY vendor_rank asc, sequence_num ;
375
376
377 -- Bug 3032472 : To support ASL
378 CURSOR l_asl_document IS
379 SELECT paa.purchasing_unit_of_measure,
380 pl.UNIT_MEAS_LOOKUP_CODE,
381 pl.unit_price,
382 ph.po_header_id,
383 pl.line_num,
384 pl.po_line_id,
385 pl.unit_price
386 FROM po_approved_supplier_list pasl,
387 po_vendors pv,
388 po_vendor_sites_all pvs,
389 po_asl_attributes paa,
390 po_asl_documents pad,
391 po_headers_all ph,
392 po_lines_all pl,
393 mrp_sr_source_org msso
394 WHERE pvs.vendor_site_id = pasl.vendor_site_id
395 AND pv.vendor_id = pasl.vendor_id
396 AND pasl.item_id = x_item_id
397 AND pasl.asl_id = paa.asl_id
398 AND pasl.asl_id = pad.asl_id
399 AND ph.po_header_id = pl.po_header_id
400 AND (( ph.type_lookup_code = 'BLANKET'
401 AND ph.approved_flag = 'Y'
402 AND nvl(ph.frozen_flag,'N') = 'N'
403 AND nvl(ph.cancel_flag,'N') = 'N'
404 AND nvl(pl.cancel_flag,'N') = 'N')
405 OR
406 ( ph.type_lookup_code = 'QUOTATION'
407 AND ph.status_lookup_code = 'A'))
408 AND ph.po_header_id = pad.document_header_id
409 AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
410 AND pl.item_id = x_item_id
411 AND msso.vendor_id = pv.vendor_id
412 AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
413 ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num; -- Added allocation_percent
414
415 -- Bug 3032472
416 l_unit_of_purchase_asl po_asl_attributes.purchasing_unit_of_measure%TYPE;
417
418 -- Bug 5576820
419 CURSOR l_asl_document_new IS
420 SELECT paa.purchasing_unit_of_measure,
421 pl.UNIT_MEAS_LOOKUP_CODE,
422 pl.unit_price,
423 ph.po_header_id,
424 pl.line_num,
425 pl.po_line_id,
426 pl.unit_price
427 FROM po_approved_supplier_list pasl,
428 po_vendors pv,
429 po_vendor_sites_all pvs,
430 po_asl_attributes paa,
431 po_asl_documents pad,
432 po_headers_all ph,
433 po_lines_all pl,
434 mrp_sr_source_org msso
435 WHERE pvs.vendor_site_id = pasl.vendor_site_id
436 AND pv.vendor_id = pasl.vendor_id
437 AND pasl.item_id = x_item_id
438 AND pasl.asl_id = paa.asl_id
439 AND pasl.asl_id = pad.asl_id
440 AND ph.po_header_id = pl.po_header_id
441 AND (( ph.type_lookup_code = 'BLANKET'
442 AND ph.approved_flag = 'Y'
443 AND nvl(ph.frozen_flag,'N') = 'N'
444 AND nvl(ph.cancel_flag,'N') = 'N'
445 AND nvl(pl.cancel_flag,'N') = 'N')
446 OR
447 ( ph.type_lookup_code = 'QUOTATION'
448 AND ph.status_lookup_code = 'A'))
449 AND ph.po_header_id = pad.document_header_id
450 AND SYSDATE between nvl(ph.start_date, SYSDATE) AND nvl(ph.end_date, SYSDATE+1)
451 AND pl.item_id = x_item_id
452 AND msso.vendor_id = pv.vendor_id
453 AND (pasl.disable_flag IS NULL OR pasl.disable_flag = 'N')
454 AND pv.vendor_id = x_suggested_vendor_id
455 AND pvs.vendor_site_id = x_suggested_vendor_site_id
456 ORDER BY msso.allocation_percent desc, msso.rank, pad.sequence_num; -- Added allocation_percent
457
458
459
460 BEGIN
461 IF g_count <> 0 THEN
462 if x_suggested_vendor_id is not null and
463 x_suggested_vendor_site_id is not null then
464 OPEN l_asl_document_new;
465 FETCH l_asl_document_new into l_unit_of_purchase_asl, -- unit of purchase at asl
466 x_unit_of_purchase, -- unit of purchase at line
467 x_unit_price, -- unit price
468 x_document_header_id, -- po_header_id
469 x_document_line_num, -- po_line_num
470 x_po_line_id, -- po_line_id
471 x_unit_price; -- po_line.unit_price
472
473 CLOSE l_asl_document_new;
474 else
475 OPEN l_asl_document;
476 FETCH l_asl_document into l_unit_of_purchase_asl, -- unit of purchase at asl
477 x_unit_of_purchase, -- unit of purchase at line
478 x_unit_price, -- unit price
479 x_document_header_id, -- po_header_id
480 x_document_line_num, -- po_line_num
481 x_po_line_id, -- po_line_id
482 x_unit_price; -- po_line.unit_price
483
484 CLOSE l_asl_document;
485 end if;
486
487 -- x_document_line_num := 1; -- Put dummy number so that ja_au_autosource handles well.
488
489 ELSE
490 OPEN l_suggested_document ;
491
492 FETCH l_suggested_document
493 INTO x_document_header_id,
494 x_document_line_num,
495 x_po_line_id,
496 x_vendor_product_number,
497 x_vendor_id,
498 x_vendor_site_id ,
499 x_vendor_contact_id,
500 x_vendor_name,
501 x_vendor_site_code,
502 x_unit_price,
503 x_unit_of_purchase ;
504
505 CLOSE l_suggested_document ;
506 END IF;
507
508 EXCEPTION
509 WHEN OTHERS THEN NULL;
510 END JA_AU_SUGGESTED_DOCUMENT ;
511
512
513
514
515 -- *
516 -- ** Create JA_AU_AUTOSOURCE procedure
517 -- *
518
519 PROCEDURE JA_AU_AUTOSOURCE
520 (
521 x_item_id IN po_requisitions_interface.item_id%TYPE,
522 x_organization_id IN po_requisitions_interface.source_organization_id%TYPE,
523 x_quantity IN OUT NOCOPY po_requisitions_interface.quantity%TYPE,
524 x_uom_code IN OUT NOCOPY po_requisitions_interface.uom_code%TYPE,
525 x_unit_of_measure IN OUT NOCOPY po_requisitions_interface.unit_of_measure%TYPE,
526 x_rowid IN po_requisitions_interface.unit_of_measure%TYPE,
527 x_autosource_doc_header_id IN po_requisitions_interface.autosource_doc_header_id%TYPE,
528 x_autosource_doc_line_num IN po_requisitions_interface.autosource_doc_line_num%TYPE,
529 x_suggested_vendor_id IN po_requisitions_interface.suggested_vendor_id%TYPE,
530 x_suggested_vendor_site_id IN po_requisitions_interface.suggested_vendor_site_id%TYPE
531 )
532 IS
533 l_document_header_id po_requisitions_interface.autosource_doc_header_id%TYPE;
534 l_document_line_num po_requisitions_interface.autosource_doc_line_num%TYPE;
535 l_vendor_id po_requisitions_interface.suggested_vendor_id%TYPE ;
536 l_vendor_site_id po_requisitions_interface.suggested_vendor_site_id%TYPE ;
537 l_vendor_contact_id po_requisitions_interface.suggested_vendor_contact_id%TYPE ;
538 l_vendor_product_number po_requisitions_interface.suggested_vendor_item_num%TYPE ;
539 l_unit_of_purchase po_requisitions_interface.unit_of_measure%TYPE ;
540 l_unit_of_measure po_requisitions_interface.unit_of_measure%TYPE ;
541 l_uom_code po_requisitions_interface.uom_code%TYPE ;
542 l_unit_price po_requisitions_interface.unit_price%TYPE ;
543 l_quote_line_price po_requisitions_interface.unit_price%TYPE ;
544 l_vendor_name po_requisitions_interface.suggested_vendor_name%TYPE ;
545 l_vendor_site_code po_requisitions_interface.suggested_vendor_site%TYPE ;
546 l_po_line_id po_lines.po_line_id%TYPE;
547 l_dummy char(1);
548 l_dummy2 po_requisitions_interface.unit_price%TYPE ;
549 l_uom_direct varchar2(30);
550
551 BEGIN
552
553 l_document_header_id := 0;
554 l_unit_price := 0;
555 l_quote_line_price := 0;
556 l_document_line_num := 0;
557 l_vendor_id := 0;
558 l_vendor_site_id := 0;
559 l_vendor_site_code := null;
560 l_vendor_name := null;
561 l_vendor_product_number := null;
562 l_unit_of_purchase := null;
563
564 if x_unit_of_measure is null THEN
565
566 SELECT unit_of_measure
567 INTO l_unit_of_measure
568 FROM mtl_units_of_measure
569 WHERE uom_code = x_uom_code ;
570
571 ELSE
572
573 l_unit_of_measure := x_unit_of_measure ;
574 l_uom_code := x_uom_code ;
575
576 END IF ;
577
578 SELECT count(*) INTO g_count FROM PO_APPROVED_SUPPLIER_LIST;
579
580
581 if x_autosource_doc_header_id is not null and
582 x_autosource_doc_line_num is not null then
583 select unit_meas_lookup_code
584 into l_uom_direct
585 from po_lines_all
586 where po_header_id = x_autosource_doc_header_id
587 and line_num = x_autosource_doc_line_num;
588 end if;
589
590
591 JA_AU_suggested_document(x_item_id,
592 l_document_header_id,
593 l_document_line_num,
594 l_po_line_id,
595 l_vendor_product_number,
596 l_vendor_id,
597 l_vendor_site_id,
598 l_vendor_contact_id,
599 l_vendor_name,
600 l_vendor_site_code,
601 l_unit_price,
602 l_unit_of_purchase,
603 x_suggested_vendor_id,
604 x_suggested_vendor_site_id);
605
606
607 if l_uom_direct is not null then
608 l_unit_of_purchase := l_uom_direct;
609 end if;
610
611 if l_unit_of_purchase is not null THEN
612
613 SELECT uom_code
614 INTO l_uom_code
615 FROM mtl_units_of_measure
616 WHERE unit_of_measure = l_unit_of_purchase ;
617
618 ELSE
619
620 l_unit_of_purchase := l_unit_of_measure ;
621
622 END IF ;
623 if x_autosource_doc_header_id is not null and
624 x_autosource_doc_line_num is not null then
625 l_document_line_num := x_autosource_doc_line_num;
626 end if;
627
628 IF l_document_line_num is null or l_document_line_num = 0 THEN
629 SELECT list_price_per_unit
630 INTO l_unit_price
631 FROM mtl_system_items
632 WHERE inventory_item_id = x_item_id
633 AND organization_id = x_organization_id ;
634
635 UPDATE po_requisitions_interface
636 SET unit_price = l_unit_price
637 WHERE rowid = x_rowid ;
638
639 COMMIT WORK ;
640 ELSE
641 l_dummy2 := 0 ;
642
643 JA_AU_reorder_uom_convert(x_item_id,
644 l_unit_of_measure,
645 l_unit_of_purchase,
646 x_quantity,
647 l_dummy2 ) ;
648
649 l_quote_line_price := l_unit_price ;
650
651 JA_AU_price_break(l_po_line_id,
652 l_unit_of_purchase,
653 x_quantity,
654 l_unit_price) ;
655
656 IF l_unit_price is null THEN
657 l_unit_price := l_quote_line_price ;
658 END IF;
659
660 -- Bug 3032472
661 IF g_count <> 0 THEN
662 UPDATE po_requisitions_interface
663 SET quantity = x_quantity
664 -- uom_code = l_uom_code,
665 -- unit_of_measure = l_unit_of_purchase
666 WHERE rowid = x_rowid;
667 ELSE
668 UPDATE po_requisitions_interface
669 SET unit_of_measure = l_unit_of_purchase,
670 uom_code = l_uom_code,
671 unit_price = l_unit_price,
672 quantity = x_quantity,
673 autosource_doc_header_id = l_document_header_id,
674 autosource_doc_line_num = l_document_line_num,
675 suggested_vendor_name = l_vendor_name,
676 suggested_vendor_id = l_vendor_id,
677 suggested_vendor_site = l_vendor_site_code,
678 suggested_vendor_site_id = l_vendor_site_id,
679 suggested_vendor_contact_id = l_vendor_contact_id,
680 suggested_vendor_contact = null,
681 suggested_vendor_phone = null,
682 suggested_vendor_item_num = l_vendor_product_number
683 WHERE rowid = x_rowid ;
684 END IF;
685
686 COMMIT WORK ;
687
688 END IF ;
689
690
691 EXCEPTION
692 WHEN OTHERS THEN NULL;
693 END JA_AU_AUTOSOURCE ;
694
695
696 -- *
697 -- ** Create JA_AU_UOI_CONVERSION procedure
698 -- *
699
700 PROCEDURE JA_AU_UOI_CONVERSION
701 (x_item_id IN
702 po_requisitions_interface.item_id%TYPE,
703 x_source_organization_id IN
704 po_requisitions_interface.source_organization_id%TYPE,
705 x_quantity IN
706 po_requisitions_interface.quantity%TYPE,
707 x_unit_price IN
708 po_requisitions_interface.unit_price%TYPE,
709 x_unit_of_measure IN
710 po_requisitions_interface.unit_of_measure%TYPE,
711 x_rowid IN
712 po_requisitions_interface.unit_of_measure%TYPE
713 )
714 IS
715
716 l_quantity
717 po_requisitions_interface.quantity%TYPE;
718 l_unit_price
719 po_requisitions_interface.unit_price%TYPE;
720 l_unit_of_issue
721 po_requisitions_interface.unit_of_measure%TYPE;
722 l_uoi_code
723 po_requisitions_interface.uom_code%TYPE;
724
725 BEGIN
726
727 SELECT nvl(unit_of_issue,primary_unit_of_measure)
728 INTO l_unit_of_issue
729 FROM mtl_system_items
730 WHERE inventory_item_id = x_item_id
731 AND organization_id = x_source_organization_id ;
732
733
734 IF x_unit_of_measure <> l_unit_of_issue THEN
735
736
737 l_quantity := x_quantity ;
738 l_unit_price := x_unit_price ;
739
740 JA_AU_reorder_uom_convert(x_item_id,
741 x_unit_of_measure,
742 l_unit_of_issue,
743 l_quantity,
744 l_unit_price );
745
746
747 SELECT uom_code
748 INTO l_uoi_code
749 FROM mtl_units_of_measure
750 WHERE unit_of_measure = l_unit_of_issue ;
751
752 UPDATE po_requisitions_interface
753 SET unit_of_measure = l_unit_of_issue,
754 uom_code = l_uoi_code,
755 quantity = l_quantity,
756 unit_price = l_unit_price
757 WHERE rowid = x_rowid ;
758
759 COMMIT WORK ;
760
761 END IF ;
762
763
764 EXCEPTION
765 WHEN OTHERS THEN NULL;
766 END JA_AU_UOI_CONVERSION;
767
768
769
770 -- *
771 -- ** Create JA_AU_UPDATE_ERRORS procedure
772 -- *
773
774 PROCEDURE JA_AU_UPDATE_ERRORS
775 (x_rowid IN
776 varchar2,
777 x_transaction_id IN
778 po_requisitions_interface.transaction_id%TYPE)
779 IS
780 BEGIN
781
782 UPDATE po_requisitions_interface
783 SET charge_account_id = 0,
784 request_id = NULL,
785 process_flag = 'ERROR'
786 WHERE rowid = x_rowid;
787
788 IF SQL%NOTFOUND THEN
789 RAISE l_update_failure;
790 END IF;
791
792 INSERT INTO po_interface_errors
793 (
794 interface_type,
795 interface_transaction_id,
796 error_message,
797 processing_date,
798 creation_date,
799 created_by,
800 last_update_date,
801 last_updated_by
802 )
803 VALUES
804 (
805 'REQIMPORT',
806 x_transaction_id,
807 l_error_msg,
808 sysdate,
809 sysdate,
810 -1,
811 sysdate,
812 -1
813 );
814
815 EXCEPTION
816 WHEN l_update_failure THEN
817 -- DBMS_OUTPUT.NEW_LINE;
818 -- DBMS_OUTPUT.PUT('*** UPDATE FAILURE in JA_AU_UPDATE_ERRORS ***');
819 -- DBMS_OUTPUT.NEW_LINE;
820 -- DBMS_OUTPUT.PUT(SQLERRM);
821 -- DBMS_OUTPUT.NEW_LINE;
822 null;
823 WHEN OTHERS THEN
824 -- DBMS_OUTPUT.NEW_LINE;
825 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_UPDATE_ERRORS ***');
826 -- DBMS_OUTPUT.NEW_LINE;
827 -- DBMS_OUTPUT.PUT(SQLERRM);
828 -- DBMS_OUTPUT.NEW_LINE;
829 null;
830 END JA_AU_UPDATE_ERRORS;
831
832
833 -- *
834 -- ** Create JA_AU_GET_COA_SOB procedure
835 -- *
836
837 PROCEDURE JA_AU_GET_COA_SOB
838 (x_rowid IN
839 varchar2,
840 x_transaction_id IN
841 po_requisitions_interface.transaction_id%TYPE,
842 x_org_id IN
843 org_organization_definitions.organization_id%TYPE,
844 x_chart_of_accts_id OUT NOCOPY
845 org_organization_definitions.chart_of_accounts_id%TYPE,
846 x_set_of_books_id OUT NOCOPY
847 org_organization_definitions.set_of_books_id%TYPE)
848 IS
849 BEGIN
850
851 SELECT chart_of_accounts_id, set_of_books_id
852 INTO x_chart_of_accts_id, x_set_of_books_id
853 FROM org_organization_definitions
854 WHERE organization_id = x_org_id
855 AND nvl(disable_date, sysdate+1) > sysdate ;
856
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 l_error_msg := 'AUTOGL ERROR - Could not retrieve chart_of_accounts_id and/or set_of_books_id.';
860 l_error_flag := -1;
861 JA_AU_update_errors(x_rowid,x_transaction_id);
862 WHEN OTHERS THEN
863 l_error_flag := -1;
864 -- DBMS_OUTPUT.NEW_LINE;
865 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_GET_COA_SOB ***');
866 -- DBMS_OUTPUT.NEW_LINE;
867 -- DBMS_OUTPUT.PUT(SQLERRM);
868 -- DBMS_OUTPUT.NEW_LINE;
869
870 END JA_AU_GET_COA_SOB;
871
872
873 -- *
874 -- ** Create JA_AU_GET_REPLN_EXP_ACCTS procedure
875 -- *
876
877 PROCEDURE JA_AU_GET_REPLN_EXP_ACCTS
878 (x_rowid IN
879 varchar2,
880 x_transaction_id IN
881 po_requisitions_interface.transaction_id%TYPE,
882 x_org_id IN
883 org_organization_definitions.organization_id%TYPE,
884 x_subinv IN
885 mtl_secondary_inventories.secondary_inventory_name%TYPE,
886 x_item_id IN
887 mtl_system_items.inventory_item_id%TYPE,
888 x_subinv_ccid IN OUT NOCOPY
889 mtl_secondary_inventories.expense_account%TYPE,
890 x_item_ccid IN OUT NOCOPY
891 mtl_system_items.expense_account%TYPE)
892 IS
893 BEGIN
894
895 l_error_msg := 'AUTOGL ERROR - Could not retrieve subinventory expense_account';
896
897 SELECT nvl(expense_account, -1)
898 INTO x_subinv_ccid
899 FROM mtl_secondary_inventories
900 WHERE organization_id = x_org_id
901 AND secondary_inventory_name = x_subinv ;
902
903 IF x_subinv_ccid = -1 THEN
904 l_error_msg := 'AUTOGL ERROR - Subinventory expense_account was NULL';
905 RAISE l_null_exp_acct;
906 END IF;
907
908 l_error_msg := 'AUTOGL ERROR - Could not retrieve item expense_account';
909
910 SELECT nvl(expense_account, -1)
911 INTO x_item_ccid
912 FROM mtl_system_items
913 WHERE organization_id = x_org_id
914 AND inventory_item_id = x_item_id ;
915
916 IF x_item_ccid = -1 THEN
917 l_error_msg := 'AUTOGL ERROR - Item expense_account was NULL';
918 RAISE l_null_exp_acct;
919 END IF;
920
921 l_error_msg := null;
922
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 JA_AU_update_errors(x_rowid,x_transaction_id);
926 l_error_flag := -1;
927 WHEN l_null_exp_acct THEN
928 JA_AU_update_errors(x_rowid,x_transaction_id);
929 l_error_flag := -1;
930 WHEN OTHERS THEN
931 l_error_flag := -1;
932 -- DBMS_OUTPUT.NEW_LINE;
933 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_GET_REPLN_EXP_ACCTS ***');
934 -- DBMS_OUTPUT.NEW_LINE;
935 -- DBMS_OUTPUT.PUT(SQLERRM);
936 -- DBMS_OUTPUT.NEW_LINE;
937
938 END JA_AU_GET_REPLN_EXP_ACCTS;
939
940
941 -- *
942 -- ** Create JA_AU_GET_VALUE function
943 -- *
944
945 FUNCTION JA_AU_GET_VALUE
946 (x_rowid IN
947 varchar2,
948 x_transaction_id IN
949 po_requisitions_interface.transaction_id%TYPE,
950 x_ccid IN
951 gl_code_combinations.code_combination_id%TYPE,
952 x_segment IN
953 gl_code_combinations.segment1%TYPE)
954 RETURN gl_code_combinations.segment1%TYPE IS
955
956 l_value gl_code_combinations.segment1%TYPE;
957
958 BEGIN
959
960 IF SUBSTR(x_segment,1,9) = 'SEGMENT30' THEN
961 SELECT nvl(segment30,'!@')
962 INTO l_value
963 FROM gl_code_combinations
964 WHERE code_combination_id = x_ccid ;
965 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT29' THEN
966 SELECT nvl(segment29,'!@')
967 INTO l_value
968 FROM gl_code_combinations
969 WHERE code_combination_id = x_ccid ;
970 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT28' THEN
971 SELECT nvl(segment28,'!@')
972 INTO l_value
973 FROM gl_code_combinations
974 WHERE code_combination_id = x_ccid ;
975 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT27' THEN
976 SELECT nvl(segment27,'!@')
977 INTO l_value
978 FROM gl_code_combinations
979 WHERE code_combination_id = x_ccid ;
980 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT26' THEN
981 SELECT nvl(segment26,'!@')
982 INTO l_value
983 FROM gl_code_combinations
984 WHERE code_combination_id = x_ccid ;
985 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT25' THEN
986 SELECT nvl(segment25,'!@')
987 INTO l_value
988 FROM gl_code_combinations
989 WHERE code_combination_id = x_ccid ;
990 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT24' THEN
991 SELECT nvl(segment24,'!@')
992 INTO l_value
993 FROM gl_code_combinations
994 WHERE code_combination_id = x_ccid ;
995 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT23' THEN
996 SELECT nvl(segment23,'!@')
997 INTO l_value
998 FROM gl_code_combinations
999 WHERE code_combination_id = x_ccid ;
1000 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT22' THEN
1001 SELECT nvl(segment22,'!@')
1002 INTO l_value
1003 FROM gl_code_combinations
1004 WHERE code_combination_id = x_ccid ;
1005 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT21' THEN
1006 SELECT nvl(segment21,'!@')
1007 INTO l_value
1008 FROM gl_code_combinations
1009 WHERE code_combination_id = x_ccid ;
1010 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT20' THEN
1011 SELECT nvl(segment20,'!@')
1012 INTO l_value
1013 FROM gl_code_combinations
1014 WHERE code_combination_id = x_ccid ;
1015 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT19' THEN
1016 SELECT nvl(segment19,'!@')
1017 INTO l_value
1018 FROM gl_code_combinations
1019 WHERE code_combination_id = x_ccid ;
1020 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT18' THEN
1021 SELECT nvl(segment18,'!@')
1022 INTO l_value
1023 FROM gl_code_combinations
1024 WHERE code_combination_id = x_ccid ;
1025 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT17' THEN
1026 SELECT nvl(segment17,'!@')
1027 INTO l_value
1028 FROM gl_code_combinations
1029 WHERE code_combination_id = x_ccid ;
1030 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT16' THEN
1031 SELECT nvl(segment16,'!@')
1032 INTO l_value
1033 FROM gl_code_combinations
1034 WHERE code_combination_id = x_ccid ;
1035 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT15' THEN
1036 SELECT nvl(segment15,'!@')
1037 INTO l_value
1038 FROM gl_code_combinations
1039 WHERE code_combination_id = x_ccid ;
1040 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT14' THEN
1041 SELECT nvl(segment14,'!@')
1042 INTO l_value
1043 FROM gl_code_combinations
1044 WHERE code_combination_id = x_ccid ;
1045 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT13' THEN
1046 SELECT nvl(segment13,'!@')
1047 INTO l_value
1048 FROM gl_code_combinations
1049 WHERE code_combination_id = x_ccid ;
1050 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT12' THEN
1051 SELECT nvl(segment12,'!@')
1052 INTO l_value
1053 FROM gl_code_combinations
1054 WHERE code_combination_id = x_ccid ;
1055 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT11' THEN
1056 SELECT nvl(segment11,'!@')
1057 INTO l_value
1058 FROM gl_code_combinations
1059 WHERE code_combination_id = x_ccid ;
1060 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT10' THEN
1061 SELECT nvl(segment10,'!@')
1062 INTO l_value
1063 FROM gl_code_combinations
1064 WHERE code_combination_id = x_ccid ;
1065 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT9' THEN
1066 SELECT nvl(segment9,'!@')
1067 INTO l_value
1068 FROM gl_code_combinations
1069 WHERE code_combination_id = x_ccid ;
1070 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT8' THEN
1071 SELECT nvl(segment8,'!@')
1072 INTO l_value
1073 FROM gl_code_combinations
1074 WHERE code_combination_id = x_ccid ;
1075 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT7' THEN
1076 SELECT nvl(segment7,'!@')
1077 INTO l_value
1078 FROM gl_code_combinations
1079 WHERE code_combination_id = x_ccid ;
1080 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT6' THEN
1081 SELECT nvl(segment6,'!@')
1082 INTO l_value
1083 FROM gl_code_combinations
1084 WHERE code_combination_id = x_ccid ;
1085 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT5' THEN
1086 SELECT nvl(segment5,'!@')
1087 INTO l_value
1088 FROM gl_code_combinations
1089 WHERE code_combination_id = x_ccid ;
1090 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT4' THEN
1091 SELECT nvl(segment4,'!@')
1092 INTO l_value
1093 FROM gl_code_combinations
1094 WHERE code_combination_id = x_ccid ;
1095 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT3' THEN
1096 SELECT nvl(segment3,'!@')
1097 INTO l_value
1098 FROM gl_code_combinations
1099 WHERE code_combination_id = x_ccid ;
1100 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT2' THEN
1101 SELECT nvl(segment2,'!@')
1102 INTO l_value
1103 FROM gl_code_combinations
1104 WHERE code_combination_id = x_ccid ;
1105 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT1' THEN
1106 SELECT nvl(segment1,'!@')
1107 INTO l_value
1108 FROM gl_code_combinations
1109 WHERE code_combination_id = x_ccid ;
1110 ELSE
1111 RAISE l_invalid_segment;
1112 END IF;
1113
1114 IF l_value = '!@' THEN
1115 RAISE l_null_segment;
1116 END IF;
1117
1118 return(l_value);
1119
1120 EXCEPTION
1121 WHEN l_invalid_segment THEN
1122 l_error_msg := 'AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Segment';
1123 JA_AU_update_errors(x_rowid,x_transaction_id);
1124 WHEN l_null_segment THEN
1125 l_error_msg := 'AUTOGL ERROR - Segment value in GL_CODE_COMBINATIONS is null';
1126 JA_AU_update_errors(x_rowid,x_transaction_id);
1127 WHEN NO_DATA_FOUND THEN
1128 l_error_msg := 'AUTOGL ERROR - Could not retrieve segment value from GL_CODE_COMBINATIONS.';
1129 JA_AU_update_errors(x_rowid,x_transaction_id);
1130 WHEN OTHERS THEN
1131 -- DBMS_OUTPUT.NEW_LINE;
1132 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_GET_VALUE ***');
1133 -- DBMS_OUTPUT.NEW_LINE;
1134 -- DBMS_OUTPUT.PUT(SQLERRM);
1135 -- DBMS_OUTPUT.NEW_LINE;
1136 null;
1137
1138 END JA_AU_GET_VALUE;
1139
1140
1141 -- *
1142 -- ** Create JA_AU_GET_SEGMENT_VALUE function
1143 -- *
1144
1145 FUNCTION JA_AU_GET_SEGMENT_VALUE
1146 (x_rowid IN
1147 varchar2,
1148 x_transaction_id IN
1149 po_requisitions_interface.transaction_id%TYPE,
1150 x_table_name IN
1151 JA_AU_ACCT_DEFAULT_SEGS.table_name%TYPE,
1152 x_constant IN
1153 JA_AU_ACCT_DEFAULT_SEGS.constant%TYPE,
1154 x_segment IN
1155 JA_AU_ACCT_DEFAULT_SEGS.segment%TYPE,
1156 x_subinv_ccid IN
1157 mtl_secondary_inventories.expense_account%TYPE,
1158 x_item_ccid IN
1159 mtl_system_items.expense_account%TYPE)
1160 RETURN gl_code_combinations.segment1%TYPE IS
1161
1162 l_value gl_code_combinations.segment1%TYPE;
1163
1164 BEGIN
1165
1166 IF SUBSTR(x_constant,1,2) = '!~' THEN /* Not a constant */
1167 IF x_table_name = 'MTL_SECONDARY_INVENTORIES' THEN
1168 l_value := JA_AU_get_value(x_rowid,
1169 x_transaction_id,
1170 x_subinv_ccid,
1171 x_segment);
1172 ELSIF x_table_name = 'MTL_SYSTEM_ITEMS' THEN
1173 l_value := JA_AU_get_value(x_rowid,
1174 x_transaction_id,
1175 x_item_ccid,
1176 x_segment);
1177 ELSE
1178 l_value := '0';
1179 RAISE l_incorrect_table;
1180 END IF;
1181 ELSE
1182 l_value := RTRIM(SUBSTR(x_constant,1,25));
1183 END IF;
1184
1185 return(l_value);
1186
1187 EXCEPTION
1188 WHEN l_incorrect_table THEN
1189 l_error_msg := 'AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Table.';
1190 JA_AU_update_errors(x_rowid,x_transaction_id);
1191 l_error_flag := -1;
1192 return(l_value);
1193 WHEN OTHERS THEN
1194 l_error_flag := -1;
1195 -- DBMS_OUTPUT.NEW_LINE;
1196 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_GET_SEGMENT_VALUE ***');
1197 -- DBMS_OUTPUT.NEW_LINE;
1198 -- DBMS_OUTPUT.PUT(SQLERRM);
1199 -- DBMS_OUTPUT.NEW_LINE;
1200 return(l_value);
1201
1202 END JA_AU_GET_SEGMENT_VALUE;
1203
1204 -- *
1205 -- ** Create JA_AU_UPDATE_REQINTERFACE procedure
1206 -- *
1207
1208 PROCEDURE JA_AU_UPDATE_REQINTERFACE
1209 (x_rowid IN
1210 varchar2,
1211 x_transaction_id IN
1212 po_requisitions_interface.transaction_id%TYPE,
1213 x_ccid IN
1214 gl_code_combinations.code_combination_id%TYPE)
1215 IS
1216 BEGIN
1217
1218 UPDATE po_requisitions_interface
1219 SET charge_account_id = x_ccid
1220 WHERE rowid = x_rowid;
1221
1222 IF SQL%NOTFOUND THEN
1223 RAISE l_no_rows_updated;
1224 END IF;
1225
1226 COMMIT WORK;
1227
1228 EXCEPTION
1229 WHEN l_no_rows_updated THEN
1230 -- DBMS_OUTPUT.NEW_LINE;
1231 -- DBMS_OUTPUT.PUT('*** Failed to update PO_REQUISITIONS_INTERFACE ***');
1232 -- DBMS_OUTPUT.NEW_LINE;
1233 -- DBMS_OUTPUT.PUT(SQLERRM);
1234 -- DBMS_OUTPUT.NEW_LINE;
1235 l_error_msg := 'AUTOGL ERROR - Update of charge_account_id in po_requisitions_interface failed.';
1236 JA_AU_update_errors(x_rowid,x_transaction_id);
1237 WHEN OTHERS THEN
1238 -- DBMS_OUTPUT.NEW_LINE;
1239 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_UPDATE_REQINTERFACE ***');
1240 -- DBMS_OUTPUT.NEW_LINE;
1241 -- DBMS_OUTPUT.PUT(SQLERRM);
1242 -- DBMS_OUTPUT.NEW_LINE;
1243 null;
1244 END JA_AU_UPDATE_REQINTERFACE;
1245
1246
1247
1248 -- *
1249 -- ** Create JA_AU_AUTOACCOUNTING procedure
1250 -- *
1251 -- +=======================================================================+
1252 -- | This procedure will obtain the code_combination_id from the table
1253 -- | GL_CODE_COMBINATIONS for the given item and subinventory. It will store
1254 -- | the code_combination_id in the charge_account_id column of the table
1255 -- | PO_REQUISITIONS_INTERFACE.
1256 -- | Any errors will be flagged by inserting a record in PO_INTERFACE_ERRORS
1257 -- | Possible errors are :
1258 -- | AUTOGL ERROR - Could not retrieve chart_of_accounts_id and/or
1259 -- | set_of_books_id.
1260 -- | AUTOGL ERROR - Could not retrieve subinventory expense_account.
1261 -- | AUTOGL ERROR - Subinventory expense_account was NULL.
1262 -- | AUTOGL ERROR - Could not retrieve item expense_account.
1263 -- | AUTOGL ERROR - Item expense_account was NULL.
1264 -- | AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Table.
1265 -- | AUTOGL ERROR - Segment value in GL_CODE_COMBINATIONS is null.
1266 -- | AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Segment
1267 -- | AUTOGL ERROR - Could not retrieve segment value from GL_CODE_COMBINATIONS.
1268 -- | AUTOGL ERROR - Update of charge_account_id in po_requisitions_interface
1269 -- | failed.
1270 -- | AUTOGL ERROR - Could not obtain or create CODE_COMBINATION_ID
1271 -- +=======================================================================+
1272
1273 PROCEDURE JA_AU_AUTOACCOUNTING
1274 (x_rowid IN
1275 varchar2,
1276 x_org_id IN
1277 po_requisitions_interface.destination_organization_id%TYPE,
1278 x_subinv IN
1279 po_requisitions_interface.destination_subinventory%TYPE,
1280 x_item_id IN
1281 po_requisitions_interface.item_id%TYPE,
1282 l_transaction_id IN
1283 po_requisitions_interface.transaction_id%TYPE)
1284
1285 IS
1286
1287 l_chart_of_accts_id org_organization_definitions.organization_id%TYPE;
1288 l_set_of_books_id org_organization_definitions.set_of_books_id%TYPE;
1289 l_subinv_ccid mtl_secondary_inventories.expense_account%TYPE;
1290 l_item_ccid mtl_system_items.expense_account%TYPE;
1291 l_table_name JA_AU_ACCT_DEFAULT_SEGS.table_name%TYPE;
1292 l_constant JA_AU_ACCT_DEFAULT_SEGS.constant%TYPE;
1293 l_segment JA_AU_ACCT_DEFAULT_SEGS.segment%TYPE;
1294 l_segvalues fnd_flex_ext.segmentarray;
1295 l_seglength natural := 0;
1296 l_segnumber natural := 0;
1297 l_ccid gl_code_combinations.code_combination_id%TYPE;
1298 l_num_segs number;
1299 l_test_ccid boolean;
1300
1301
1302 CURSOR l_autoaccount_defns IS
1303 SELECT nvl(upper(s.table_name), '!~') TABLE_NAME,
1304 nvl(s.constant, '!~') CONSTANT,
1305 s.segment
1306 FROM JA_AU_ACCT_DEFAULT_SEGS s, ja_au_account_defaults d
1307 WHERE s.gl_default_id = d.gl_default_id
1308 AND d.set_of_books_id = l_set_of_books_id
1309 ORDER BY d.type,s.segment_num ;
1310
1311 BEGIN
1312
1313 l_error_flag := 0;
1314 l_num_segs := 0;
1315
1316 /* Initialise l_segvalues and l_ccid */
1317 FOR l_index IN 1..30 LOOP
1318 l_segvalues(l_index) := '%';
1319 END LOOP;
1320 l_ccid := 0;
1321 l_chart_of_accts_id := 0;
1322 l_set_of_books_id := 0;
1323 l_subinv_ccid := 0;
1324 l_item_ccid := 0;
1325
1326
1327 /* Obtain chart_of_accounts_id and set_of_books from
1328 org_organization_definitions*/
1329 JA_AU_get_coa_sob(x_rowid,
1330 l_transaction_id,
1331 x_org_id,
1332 l_chart_of_accts_id,
1333 l_set_of_books_id);
1334
1335 IF l_error_flag = -1 THEN
1336 GOTO end_processing;
1337 END IF;
1338
1339 /* Get the subinventory and item expense accounts */
1340 JA_AU_get_repln_exp_accts(x_rowid,
1341 l_transaction_id,
1342 x_org_id,
1343 x_subinv,
1344 x_item_id,
1345 l_subinv_ccid,
1346 l_item_ccid);
1347
1348 IF l_error_flag = -1 THEN
1349 GOTO end_processing;
1350 END IF;
1351
1352
1353 /* Fetch the AutoAccounting definitions a row at a time and retrieve
1354 the segment value from GL_CODE_COMBINATIONS for the specified
1355 segment */
1356 OPEN l_autoaccount_defns;
1357
1358 LOOP
1359 FETCH l_autoaccount_defns
1360 INTO l_table_name,
1361 l_constant,
1362 l_segment ;
1363
1364
1365 EXIT WHEN l_autoaccount_defns%NOTFOUND;
1366
1367 l_num_segs := l_num_segs + 1;
1368
1369 l_seglength := LENGTH(l_segment);
1370 l_segnumber := TO_NUMBER(SUBSTR(l_segment,8,l_seglength-7));
1371
1372 l_segvalues(l_segnumber) := JA_AU_get_segment_value(x_rowid,
1373 l_transaction_id,
1374 l_table_name,
1375 l_constant,
1376 l_segment,
1377 l_subinv_ccid,
1378 l_item_ccid);
1379
1380 IF l_error_flag = -1 THEN
1381 GOTO end_processing;
1382 END IF;
1383
1384 END LOOP;
1385
1386 CLOSE l_autoaccount_defns;
1387
1388 l_test_ccid := fnd_flex_ext.get_combination_id('SQLGL',
1389 'GL#',
1390 l_chart_of_accts_id,
1391 sysdate,
1392 l_num_segs,
1393 l_segvalues,
1394 l_ccid);
1395
1396 if (l_test_ccid = TRUE) then
1397 commit;
1398 else
1399 l_test_ccid := fnd_flex_ext.get_combination_id('SQLGL',
1400 'GL#',
1401 l_chart_of_accts_id,
1402 sysdate,
1403 l_num_segs,
1404 l_segvalues,
1405 l_ccid);
1406
1407 if (l_test_ccid = TRUE) then
1408 commit;
1409 else
1410 l_error_msg := 'AUTOGL ERROR - Could not obtain or create CODE_COMBINATION_ID.';
1411 JA_AU_update_errors(x_rowid,l_transaction_id);
1412 goto end_processing;
1413 end if;
1414 end if;
1415
1416 JA_AU_update_reqinterface(x_rowid,
1417 l_transaction_id,
1418 l_ccid);
1419
1420 <<end_processing>>
1421 commit;
1422
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 -- DBMS_OUTPUT.NEW_LINE;
1426 -- DBMS_OUTPUT.PUT('*** ERROR in JA_AU_AUTOACCOUNTING ***');
1427 -- DBMS_OUTPUT.NEW_LINE;
1428 -- DBMS_OUTPUT.PUT(SQLERRM);
1429 -- DBMS_OUTPUT.NEW_LINE;
1430 null;
1431 END JA_AU_AUTOACCOUNTING ;
1432
1433
1434
1435
1436 -- + ============================================================================+
1437 -- | NAME: POST_VALIDATE_USER_EXTENSIONS
1438 -- | DESC: Top level procedure that you can use to add logic to ReqImport to
1439 -- | implement any extra functionality that you need. Make sure that
1440 -- | all the logic that accesses the PO_REQUISITIONS_INTERFACE table
1441 -- | restricts by the X_REQUEST_ID parameter.
1442 -- | ARGS: request_id
1443 -- | ALGR: None
1444 -- +=============================================================================+
1445
1446 PROCEDURE POST_VALIDATE_USER_EXTENSIONS(x_request_id IN number)
1447 IS
1448
1449
1450 CURSOR l_po_interface_lines
1451 IS SELECT pri.source_type_code,
1452 pri.requisition_header_id,
1453 pri.requisition_line_id,
1454 pri.req_distribution_id,
1455 pri.requisition_type,
1456 pri.unit_price,
1457 pri.autosource_flag,
1458 pri.item_id,
1459 pri.charge_account_id,
1460 pri.unit_of_measure,
1461 pri.uom_code,
1462 pri.source_organization_id,
1463 pri.destination_organization_id,
1464 pri.source_subinventory,
1465 pri.destination_organization_id,
1466 pri.destination_subinventory,
1467 pri.destination_type_code,
1468 pri.deliver_to_location_id,
1469 pri.quantity,
1470 pri.transaction_id,
1471 pri.rowid
1472 ,pri.autosource_doc_header_id
1473 ,pri.autosource_doc_line_num
1474 ,pri.suggested_vendor_id
1475 ,pri.suggested_vendor_site_id
1476 FROM po_requisitions_interface pri
1477 WHERE request_id = x_request_id ;
1478
1479
1480 l_source_type_code
1481 po_requisitions_interface.source_type_code%TYPE;
1482 l_requisition_header_id
1483 po_requisitions_interface.requisition_header_id%TYPE;
1484 l_requisition_line_id
1485 po_requisitions_interface.requisition_line_id%TYPE;
1486 l_req_distribution_id
1487 po_requisitions_interface.req_distribution_id%TYPE;
1488 l_requisition_type
1489 po_requisitions_interface.requisition_type%TYPE;
1490 l_unit_price
1491 po_requisitions_interface.unit_price%TYPE;
1492 --Bug 3032472 : Move it to global variable
1493 --l_autosource_flag
1494 -- po_requisitions_interface.autosource_flag%TYPE;
1495 l_item_id
1496 po_requisitions_interface.item_id%TYPE;
1497 l_charge_account_id
1498 po_requisitions_interface.charge_account_id%TYPE;
1499 l_unit_of_measure
1500 po_requisitions_interface.unit_of_measure%TYPE;
1501 l_uom_code
1502 po_requisitions_interface.uom_code%TYPE;
1503 l_source_organization_id
1504 po_requisitions_interface.source_organization_id%TYPE;
1505 l_destination_organization_id
1506 po_requisitions_interface.source_organization_id%TYPE;
1507 l_source_subinventory
1508 po_requisitions_interface.source_subinventory%TYPE;
1509 l_dest_org_id
1510 po_requisitions_interface.destination_organization_id%TYPE;
1511 l_dest_subinventory
1512 po_requisitions_interface.destination_subinventory%TYPE;
1513 l_destination_type_code
1514 po_requisitions_interface.destination_type_code%TYPE;
1515 l_deliver_to_location_id
1516 po_requisitions_interface.deliver_to_location_id%TYPE;
1517 l_transaction_id
1518 po_requisitions_interface.transaction_id%TYPE;
1519 l_quantity
1520 po_requisitions_interface.quantity%TYPE;
1521 l_rowid varchar2(25) ;
1522 l_autosource_doc_header_id
1523 po_requisitions_interface.autosource_doc_header_id%TYPE;
1524 l_autosource_doc_line_num
1525 po_requisitions_interface.autosource_doc_line_num%TYPE;
1526 l_suggested_vendor_id
1527 po_requisitions_interface.suggested_vendor_id%TYPE;
1528 l_suggested_vendor_site_id
1529 po_requisitions_interface.suggested_vendor_site_id%TYPE;
1530
1531 l_call_autoacct varchar2(1); -- execution control for auto accounting procedure.
1532 l_po_imp_req_flag VARCHAR2(1); -- pkg execution control profile variable
1533
1534
1535 BEGIN
1536
1537 -- JA_AU_PO_IMP_REQ_FLAG profile controls execution of this program.
1538
1539 FND_PROFILE.GET('JA_AU_PO_IMP_REQ_FLAG',l_po_imp_req_flag);
1540
1541 IF nvl(l_po_imp_req_flag,'N') <> 'Y' THEN
1542
1543 return;
1544
1545 END IF;
1546 --
1547
1548 -- Get profile value which tells us if auto accounting is to be called.
1549
1550 FND_PROFILE.GET('JA_AU_PO_AUTO_ACCT',l_call_autoacct);
1551
1552 OPEN l_po_interface_lines ;
1553
1554 LOOP
1555
1556 FETCH l_po_interface_lines
1557 INTO l_source_type_code,
1558 l_requisition_header_id,
1559 l_requisition_line_id,
1560 l_req_distribution_id,
1561 l_requisition_type,
1562 l_unit_price,
1563 g_autosource_flag,
1564 l_item_id,
1565 l_charge_account_id,
1566 l_unit_of_measure,
1567 l_uom_code,
1568 l_source_organization_id,
1569 l_destination_organization_id,
1570 l_source_subinventory,
1571 l_dest_org_id,
1572 l_dest_subinventory,
1573 l_destination_type_code,
1574 l_deliver_to_location_id,
1575 l_quantity,
1576 l_transaction_id,
1577 l_rowid ,
1578 l_autosource_doc_header_id ,
1579 l_autosource_doc_line_num ,
1580 l_suggested_vendor_id ,
1581 l_suggested_vendor_site_id ;
1582
1583 EXIT WHEN l_po_interface_lines%NOTFOUND ;
1584
1585 IF l_requisition_type = 'INTERNAL' THEN
1586
1587 g_count := 0; -- Bug 6277514
1588
1589 JA_AU_uoi_conversion(l_item_id,
1590 l_source_organization_id,
1591 l_quantity,
1592 l_unit_price,
1593 l_unit_of_measure,
1594 l_rowid) ;
1595
1596 IF (l_destination_type_code = 'INVENTORY')
1597 and (l_call_autoacct = 'Y') THEN
1598 JA_AU_autoaccounting(l_rowid,
1599 l_destination_organization_id,
1600 l_dest_subinventory,
1601 l_item_id,
1602 l_transaction_id);
1603 END IF;
1604 ELSE
1605 -- ** SGOGGIN JUNE 1996
1606 -- Modified to check if Autosourcing has been disabled in the
1607 -- Interface table.
1608 IF g_autosource_flag IN ('Y','P') THEN -- Bug 3032472
1609 JA_AU_autosource(l_item_id,
1610 l_destination_organization_id,
1611 l_quantity,
1612 l_uom_code,
1613 l_unit_of_measure,
1614 l_rowid,
1615 l_autosource_doc_header_id ,
1616 l_autosource_doc_line_num ,
1617 l_suggested_vendor_id ,
1618 l_suggested_vendor_site_id) ;
1619 END IF;
1620
1621 END IF;
1622
1623
1624 END LOOP ;
1625 CLOSE l_po_interface_lines ;
1626
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN NULL;
1630 END POST_VALIDATE_USER_EXTENSIONS;
1631
1632
1633 END PO_REQIMP_PKG;