[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_XML
Source
1 Package Body POS_ASN_XML AS
2 /* $Header: POSASNXB.pls 120.1 2006/06/16 23:19:50 hvadlamu noship $*/
3
4 Procedure validate_shipment_num
5 (p_shipment_num IN VARCHAR,
6 p_vendor_id IN NUMBER,
7 p_vendor_site_id IN NUMBER,
8 p_ship_to_org_id IN NUMBER,
9 p_error_code OUT NOCOPY NUMBER,
10 p_error_message OUT NOCOPY VARCHAR) is
11
12 v_temp NUMBER;
13 p_count NUMBER;
14
15 BEGIN
16
17 /* the conditions which need to applied are:
18 no ASN for the same vendor and the same vendor site
19 must have the same ASN
20 */
21
22 p_error_code := 0;
23
24 select count(*)
25 into v_temp
26 from rcv_headers_interface
27 where
28 shipment_num = p_shipment_num and
29 vendor_id = p_vendor_id and
30 nvl(vendor_site_id, -9999) = nvl(p_vendor_site_id, -9999);
31 /* and shipped_date >= add_months(sysdate,-12) */
32
33
34
35 select count(*)
36 into p_count
37 from rcv_shipment_headers
38 where
39 shipment_num = p_shipment_num and
40 vendor_id = p_vendor_id and
41 nvl(vendor_site_id, -9999) = nvl(p_vendor_site_id, -9999);
42 /* and shipped_date >= add_months(sysdate,-12) */
43
44
45
46 /* here we will check to see whether the v_temp is >1
47 because due to parameter requirements this procedure
48 can be called only at post_insert stage in the root-post level
49 */
50
51 if (p_count > 0 OR v_temp > 1) then
52 p_error_code := 1;
53 p_error_message := 'Another ASN exists for same Vendor and Vendor Site with the same Shipment Number: ' || p_shipment_num;
54 end if;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 p_error_code := 2;
59 p_error_message := 'Exception in validate_shipment_num procedure for shipment_num: ' || p_shipment_num;
60
61
62 END validate_shipment_num;
63
64
65
66 Procedure validate_shipment_date
67 (p_shipment_date IN DATE,
68 p_error_code OUT NOCOPY NUMBER,
69 p_error_message OUT NOCOPY VARCHAR) is
70
71 BEGIN
72
73 p_error_code := 0;
74
75 if (trunc(p_shipment_date) > trunc(sysdate)) then
76 p_error_code := 1;
77 p_error_message := 'Shipment date ' || p_shipment_date || ' cannot be greater than current date';
78 end if;
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 p_error_code := 2;
83 p_error_message := 'Exception in validate_shipment_date for shipment_date: ' || p_shipment_date;
84
85 END validate_shipment_date;
86
87
88
89 Procedure validate_receipt_date
90 (p_shipment_date IN DATE,
91 p_expected_receipt_date IN DATE,
92 p_error_code OUT NOCOPY NUMBER,
93 p_error_message OUT NOCOPY VARCHAR) is
94
95 BEGIN
96
97 p_error_code := 0;
98
99 if (trunc(p_shipment_date) > trunc(p_expected_receipt_date)) then
100 p_error_code := 1;
101 p_error_message := 'Shipment date ' || p_shipment_date || ' cannot be greater than Expected Receipt date' || p_expected_receipt_date;
102 end if;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 p_error_code := 2;
107 p_error_message := 'Exception in validate_receipt_date for shipment_date: ' || p_shipment_date ;
108 p_error_message := p_error_message || ', Receipt date: ' || p_expected_receipt_date;
109
110 END validate_receipt_date;
111
112
113
114 Procedure validate_quantity
115 (p_line_location_id IN NUMBER,
116 p_quantity IN NUMBER,
117 p_unit_of_measure IN VARCHAR,
118 p_error_code OUT NOCOPY NUMBER,
119 p_error_message OUT NOCOPY VARCHAR) is
120
121 l_converted_quantity NUMBER;
122 l_tolerable_quantity NUMBER;
123
124 BEGIN
125
126 p_error_code := 0;
127
128 POS_CREATE_ASN.getConvertedQuantity ( p_line_location_id,
129 p_quantity ,
130 p_unit_of_measure,
131 l_converted_quantity);
132
133 l_tolerable_quantity := POS_CREATE_ASN.getTolerableShipmentQuantity(p_line_location_id);
134
135 if (l_tolerable_quantity < l_converted_quantity) then
136
137 p_error_code := 1;
138 p_error_message := 'Quantity shipped ' || p_quantity || ' is greater than remaining quantity for this PO Shipment line ';
139 p_error_message := p_error_message || ', for unit_of_measure ' || p_unit_of_measure;
140 p_error_message := p_error_message || ', line_location_id ' || p_line_location_id;
141
142 end if;
143
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 p_error_code := 2;
148 p_error_message := 'Exception in validate_quantity ';
149 p_error_message := p_error_message || ' for quantity ' || p_quantity;
150 p_error_message := p_error_message || ', unit_of_measure ' || p_unit_of_measure;
151 p_error_message := p_error_message || ', line_location_id ' || p_line_location_id;
152
153 END validate_quantity;
154
155
156
157 Procedure validate_freight_carrier_code
158 (p_freight_code IN VARCHAR,
159 p_error_code OUT NOCOPY NUMBER) is
160
161 l_count NUMBER;
162
163 BEGIN
164
165 select count(*)
166 into l_count
167 from ORG_FREIGHT
168 where
169 freight_code = p_freight_code;
170
171 if (l_count = 0) then
172 p_error_code := 1;
173 else
174 p_error_code := 0;
175 end if;
176
177 END validate_freight_carrier_code;
178
179
180
181
182 Procedure validate_freight_terms
183 (p_freight_terms IN VARCHAR,
184 p_error_code OUT NOCOPY NUMBER) is
185
186 l_count NUMBER;
187
188 BEGIN
189
190 select count(*)
191 into l_count
192 from po_lookup_codes
193 where lookup_type = 'FREIGHT TERMS'
194 and lookup_code = p_freight_terms
195 and sysdate < nvl(inactive_date, sysdate + 1);
196
197 if (l_count = 1) then
198 p_error_code := 1;
199 else
200 p_error_code := 0;
201 end if;
202
203 END validate_freight_terms;
204
205
206
207 Procedure use_preProcessor
208 (p_group_id IN NUMBER,
209 p_org_id IN NUMBER,
210 p_error_message OUT NOCOPY VARCHAR,
211 p_error_code OUT NOCOPY NUMBER,
212 p_po_num OUT NOCOPY VARCHAR,
213 p_line_num OUT NOCOPY NUMBER,
214 p_po_shipment_line_num OUT NOCOPY NUMBER) is
215
216 l_count NUMBER;
217
218 BEGIN
219 p_error_code := 0;
220
221 /*
222 POS_CREATE_ASN.callPreProcessor(p_group_id);
223 */
224
225 fnd_client_info.set_org_context(to_char(p_org_id));
226
227 rcv_shipment_object_sv.create_object(p_group_id);
228
229
230 /*
231 select
232 poh.segment1,
233 pol.line_num,
234 poll.shipment_num,
235 pie.error_message
236 into
237 p_po_num,
238 p_line_num,
239 p_po_shipment_line_num,
240 p_error_message
241 from
242 rcv_transactions_interface rti, po_interface_errors pie, po_headers_all poh, po_lines_all pol,
243 po_line_locations_all poll
244 where
245 pie.interface_header_id = rti.header_interface_id and
246 pie.interface_type in ('RECEIVING','RCV-856') and
247 rti.po_header_id = poh.po_header_id and
248 rti.po_line_id = pol.po_line_id and
249 rti.po_line_location_id = poll.line_location_id and
250 rti.group_id = p_group_id;
251 */
252
253 select count(*)
254 into l_count
255 from
256 rcv_transactions_interface rti, po_interface_errors pie
257 where
258 pie.interface_header_id = rti.header_interface_id and
259 -- pie.interface_type in ('RECEIVING','RCV-856') and
260 rti.group_id = p_group_id;
261
262
263 if (l_count <> 0) then
264
265 p_error_code := 1;
266
267 select
268 min(pie.error_message)
269 into
270 p_error_message
271 from
272 rcv_transactions_interface rti, po_interface_errors pie
273 where
274 pie.interface_header_id = rti.header_interface_id and
275 -- pie.interface_type in ('RECEIVING','RCV-856') and
276 rti.group_id = p_group_id;
277
278 end if;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 p_error_code := 2;
283 p_error_message := 'Exception in use_preProcessor for group_id: ' || p_group_id || ', and org_id: ' || p_org_id;
284
285 END use_preProcessor;
286
287
288
289 Procedure derive_location_id
290 (p_ship_to_partner_id IN VARCHAR,
291 p_org_id IN NUMBER,
292 p_address1 IN VARCHAR,
293 p_address2 IN VARCHAR,
294 p_city IN VARCHAR,
295 p_postal_code IN VARCHAR,
296 p_country IN VARCHAR,
297 p_ship_to_location_id OUT NOCOPY NUMBER,
298 p_auto_transact_code OUT NOCOPY VARCHAR,
299 p_transaction_type OUT NOCOPY VARCHAR,
300 p_error_code OUT NOCOPY NUMBER,
301 p_error_message OUT NOCOPY VARCHAR) is
302
303 l_count_num NUMBER;
304 l_loc_count NUMBER;
305 x_pla_count NUMBER;
306
307 BEGIN
308
309 p_error_code := 0;
310
311 IF ((p_ship_to_partner_id is null) OR (p_ship_to_partner_id = '')) THEN
312
313 /* use address */
314
315 p_auto_transact_code := 'SHIP';
316 p_transaction_type := 'SHIP';
317
318 SELECT count(*)
319 INTO l_loc_count
320 FROM hz_locations
321 WHERE
322 address1 = p_address1 and
323 nvl(address2, 99) = nvl(p_address2, 99) and
324 city = p_city and
325 postal_code = p_postal_code and
326 country = p_country;
327
328 if (l_loc_count = 1) then
329
330 SELECT min(location_id)
331 INTO p_ship_to_location_id
332 FROM hz_locations
333 WHERE
334 address1 = p_address1 and
335 nvl(address2, 99) = nvl(p_address2, 99) and
336 city = p_city and
337 postal_code = p_postal_code and
338 country = p_country;
339
340 select count(*)
341 into x_pla_count
342 from po_location_associations_all pla
343 where pla.org_id = p_org_id
344 and pla.location_id = p_ship_to_location_id
345 and pla.vendor_id is not null
346 and pla.vendor_site_id is not null;
347
348 if (x_pla_count = 0) then
349
350 p_auto_transact_code := 'SHIP';
351 p_transaction_type := 'SHIP';
352
353 else
354
355 p_auto_transact_code := 'DELIVER';
356 p_transaction_type := 'RECEIVE';
357
358 end if;
359
360
361 elsif (l_loc_count > 1) then
362
363 p_ship_to_location_id := 0;
364 p_error_code := 1;
365 p_error_message := 'Multiple matching locations found ';
366 p_error_message := p_error_message || ' for address1 ' || p_address1;
367 p_error_message := p_error_message || ' , address2 ' || p_address2;
368 p_error_message := p_error_message || ' , city ' || p_city;
369 p_error_message := p_error_message || ' , postal_code ' || p_postal_code;
370 p_error_message := p_error_message || ' , country ' || p_country;
371
372
373 else
374
375 p_ship_to_location_id := 0;
376 p_error_code := 1;
377 p_error_message := 'No matching location found ';
378 p_error_message := p_error_message || ' for address1 ' || p_address1;
379 p_error_message := p_error_message || ' , address2 ' || p_address2;
380 p_error_message := p_error_message || ' , city ' || p_city;
381 p_error_message := p_error_message || ' , postal_code ' || p_postal_code;
382 p_error_message := p_error_message || ' , country ' || p_country;
383
384 end if;
385
386
387 ELSE /* use edi_code */
388
389 SELECT count(*)
390 INTO l_count_num
391 FROM hr_locations_all
392 WHERE ece_tp_location_code = p_ship_to_partner_id;
393
394 if (l_count_num = 0) then
395
396 p_ship_to_location_id := 0;
397 p_auto_transact_code := 'SHIP';
398 p_transaction_type := 'SHIP';
399
400 else
401
402 SELECT min(location_id)
403 INTO p_ship_to_location_id
404 FROM hr_locations_all
405 WHERE ece_tp_location_code = p_ship_to_partner_id;
406
407
408 select count(*)
409 into x_pla_count
410 from po_location_associations_all pla
411 where pla.org_id = p_org_id
412 and pla.location_id = p_ship_to_location_id
413 and pla.vendor_id is not null
414 and pla.vendor_site_id is not null;
415
416 IF (x_pla_count = 0) THEN
417
418 p_auto_transact_code := 'SHIP';
419 p_transaction_type := 'SHIP';
420
421 ELSE
422
423 p_auto_transact_code := 'DELIVER';
424 p_transaction_type := 'RECEIVE';
425
426 END IF;
427
428 end if;
429
430 IF ((p_ship_to_location_id = null) OR (p_ship_to_location_id <= 0)) THEN
431
432 SELECT count(*)
433 INTO l_loc_count
434 FROM
435 hz_locations loc,
436 hz_party_sites party,
437 hz_cust_acct_sites_all cust
438 WHERE
442 and party.location_id = loc.location_id;
439 cust.ece_tp_location_code = p_ship_to_partner_id
440 and cust.org_id = p_org_id
441 and cust.party_site_id = party.party_site_id
443
444
445
446 if (l_loc_count = 1) then
447
448
449 SELECT min(loc.location_id)
450 INTO p_ship_to_location_id
451 FROM
452 hz_locations loc,
453 hz_party_sites party,
454 hz_cust_acct_sites_all cust
455 WHERE
456 cust.ece_tp_location_code = p_ship_to_partner_id
457 and cust.org_id = p_org_id
458 and cust.party_site_id = party.party_site_id
459 and party.location_id = loc.location_id;
460
461
462
463 elsif (l_loc_count > 1) then
464
465 p_ship_to_location_id := 0;
466 p_error_code := 1;
467 p_error_message := 'Multiple matching locations found for Ship To Partner Id (PARTNRIDX) ' || p_ship_to_partner_id;
468
469 else
470 p_ship_to_location_id := 0;
471 p_error_code := 1;
472 p_error_message := 'No matching location found for Ship To Partner Id (PARTNRIDX) ' || p_ship_to_partner_id;
473
474 end if;
475
476 END IF;
477
478 END IF; /* end of if-else use address */
479
480
481 EXCEPTION
482 WHEN OTHERS THEN
483
484 p_ship_to_location_id := 0;
485 p_error_code := 2;
486 p_error_message := 'Exception in derive_location_id for ship_to_partner_id: ' || p_ship_to_partner_id;
487 p_error_message := p_error_message || ', and org_id ' || p_org_id;
488
489
490 END derive_location_id;
491
492
493
494 Procedure derive_org_id
495 (p_document_line_num IN NUMBER,
496 p_document_shipment_line_num IN NUMBER,
497 p_release_num IN NUMBER,
498 p_po_number IN VARCHAR,
499 p_supplier_code IN VARCHAR,
500 p_item_num IN VARCHAR,
501 p_supplier_item_num IN VARCHAR,
502 p_org_id OUT NOCOPY NUMBER,
503 p_ship_to_org_id OUT NOCOPY NUMBER,
504 p_po_header_id OUT NOCOPY NUMBER,
505 p_error_code OUT NOCOPY NUMBER,
506 p_error_message OUT NOCOPY VARCHAR) is
507
508 l_count_num NUMBER;
509 x_ship_org_num NUMBER;
510
511 BEGIN
512
513 p_error_code := 0;
514
515 if ((p_release_num is null) OR (p_release_num = 0)) then
516
517 SELECT count(*)
518 INTO l_count_num
519 FROM
520 po_headers_all poh,
521 po_lines_all pol,
522 po_line_locations_all pll,
523 Mtl_system_items_kfv msi
524 WHERE
525 poh.SEGMENT1 = p_po_number AND
526 poh.Vendor_Site_ID IN
527 (SELECT Vendor_Site_ID
528 FROM PO_Vendor_Sites_All
529 WHERE ECE_TP_LOCATION_CODE = p_supplier_code) AND
530 nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
531 nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
532 pol.po_header_id = poh.po_header_id AND
533 pol.line_num = p_document_line_num AND
534 pol.po_line_id = pll.po_line_id AND
535 pll.shipment_num = p_document_shipment_line_num AND
536 pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
537 pol.item_id = msi.inventory_item_id (+);
538
539 else
540
541 SELECT count(*)
542 INTO l_count_num
543 FROM
544 po_headers_all poh,
545 po_lines_all pol,
546 po_line_locations_all pll,
547 po_releases_all prl,
548 Mtl_system_items_kfv msi
549 WHERE
550 poh.SEGMENT1 = p_po_number AND
551 poh.Vendor_Site_ID IN
552 (SELECT Vendor_Site_ID
553 FROM PO_Vendor_Sites_All
554 WHERE ECE_TP_LOCATION_CODE = p_supplier_code) AND
555 nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
556 nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
557 pol.po_header_id = poh.po_header_id AND
558 pol.line_num = p_document_line_num AND
559 pol.po_line_id = pll.po_line_id AND
560 pll.shipment_num = p_document_shipment_line_num AND
561 pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
562 prl.release_num = p_release_num AND
563 pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
564 pol.item_id = msi.inventory_item_id (+);
565
566
567 end if;
568
569
570 if (l_count_num = 0) then
571 p_error_code := 1;
572 p_error_message := 'No matching record found for Ship From Partner Id (PARTNRIDX) : ' || p_supplier_code;
573 p_error_message := p_error_message || ', PO Number :' || p_po_number;
574 p_error_message := p_error_message || ', Line Number ' || p_document_line_num;
575 p_error_message := p_error_message || ', Shipment Number ' || p_document_shipment_line_num;
576 p_error_message := p_error_message || ', Release Number ' || p_release_num;
577 p_error_message := p_error_message || ', Item Number ' || p_item_num;
578 p_error_message := p_error_message || ', Supplier Item Number ' || p_supplier_item_num;
579 end if;
580
581
585 p_error_message := p_error_message || ', PO Number :' || p_po_number;
582 if (l_count_num > 1) then
583 p_error_code := 4;
584 p_error_message := 'Multiple matching records found for Ship From Partner Id (PARTNRIDX) : ' || p_supplier_code;
586 p_error_message := p_error_message || ', Line Number ' || p_document_line_num;
587 p_error_message := p_error_message || ', Shipment Number ' || p_document_shipment_line_num;
588 p_error_message := p_error_message || ', Release Number ' || p_release_num;
589 p_error_message := p_error_message || ', Item Number ' || p_item_num;
590 p_error_message := p_error_message || ', Supplier Item Number ' || p_supplier_item_num;
591 end if;
592
593
594 if (p_error_code = 0) then /* get the org_id */
595
596 if ((p_release_num is null) OR (p_release_num = 0)) then
597
598 SELECT min(poh.ORG_ID)
599 INTO p_org_id
600 FROM
601 po_headers_all poh,
602 po_lines_all pol,
603 po_line_locations_all pll,
604 Mtl_system_items_kfv msi
605 WHERE
606 poh.SEGMENT1 = p_po_number AND
607 poh.Vendor_Site_ID IN
608 (SELECT Vendor_Site_ID
609 FROM PO_Vendor_Sites_All
610 WHERE ECE_TP_LOCATION_CODE = p_supplier_code) AND
611 nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
612 nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
613 pol.po_header_id = poh.po_header_id AND
614 pol.line_num = p_document_line_num AND
615 pol.po_line_id = pll.po_line_id AND
616 pll.shipment_num = p_document_shipment_line_num AND
617 pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
618 pol.item_id = msi.inventory_item_id (+);
619
620 else
621
622 SELECT min(poh.ORG_ID)
623 INTO p_org_id
624 FROM
625 po_headers_all poh,
626 po_lines_all pol,
627 po_line_locations_all pll,
628 po_releases_all prl,
629 Mtl_system_items_kfv msi
630 WHERE
631 poh.SEGMENT1 = p_po_number AND
632 poh.Vendor_Site_ID IN
633 (SELECT Vendor_Site_ID
634 FROM PO_Vendor_Sites_All
635 WHERE ECE_TP_LOCATION_CODE = p_supplier_code) AND
636 nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
637 nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
638 pol.po_header_id = poh.po_header_id AND
639 pol.line_num = p_document_line_num AND
640 pol.po_line_id = pll.po_line_id AND
641 pll.shipment_num = p_document_shipment_line_num AND
642 pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
643 prl.release_num = p_release_num AND
644 pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
645 pol.item_id = msi.inventory_item_id (+);
646
647
648 end if;
649
650
651 select min(po_header_id)
652 into p_po_header_id
653 from po_headers_all
654 where segment1 = p_po_number
655 and org_id = p_org_id;
656
657
658 if ((p_release_num is null) OR (p_release_num = 0)) then
659
660 select
661 count(*)
662 into
663 x_ship_org_num
664 from
665 po_headers_all poh,
666 po_lines_all pol,
667 po_line_locations_all pll
668 where
669 poh.po_header_id = p_po_header_id and
670 poh.po_header_id = pol.po_header_id and
671 pol.line_num = p_document_line_num and
672 pol.po_line_id = pll.po_line_id and
673 pll.shipment_num = p_document_shipment_line_num;
674
675 else
676
677 select
678 count(*)
679 into
680 x_ship_org_num
681 from
682 po_headers_all poh,
683 po_lines_all pol,
684 po_line_locations_all pll,
685 po_releases_all prl
686 where
687 poh.po_header_id = p_po_header_id and
688 poh.po_header_id = pol.po_header_id and
689 pol.line_num = p_document_line_num and
690 pol.po_line_id = pll.po_line_id and
691 pll.shipment_num = p_document_shipment_line_num and
692 pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
693 prl.release_num = p_release_num;
694
695
696 end if;
697
698
699 if (x_ship_org_num > 0) then
700
701 if ((p_release_num is null) OR (p_release_num = 0)) then
702
703 select
704 min(pll.ship_to_organization_id)
705 into
706 p_ship_to_org_id
707 from
708 po_headers_all poh,
709 po_lines_all pol,
710 po_line_locations_all pll
711 where
712 poh.po_header_id = p_po_header_id and
713 poh.po_header_id = pol.po_header_id and
714 pol.line_num = p_document_line_num and
715 pol.po_line_id = pll.po_line_id and
716 pll.shipment_num = p_document_shipment_line_num;
717
718 else
719
723 p_ship_to_org_id
720 select
721 min(pll.ship_to_organization_id)
722 into
724 from
725 po_headers_all poh,
726 po_lines_all pol,
727 po_line_locations_all pll,
728 po_releases_all prl
729 where
730 poh.po_header_id = p_po_header_id and
731 poh.po_header_id = pol.po_header_id and
732 pol.line_num = p_document_line_num and
733 pol.po_line_id = pll.po_line_id and
734 pll.shipment_num = p_document_shipment_line_num and
735 pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
736 prl.release_num = p_release_num;
737
738 end if;
739
740 else /* x_ship_org_num is 0 */
741 p_error_code := 2;
742 p_error_message := 'No matching record found for Ship From Partner Id (PARTNRIDX) : ' || p_supplier_code;
743 p_error_message := p_error_message || ', PO Number :' || p_po_number;
744 p_error_message := p_error_message || ', Line Number ' || p_document_line_num;
745 p_error_message := p_error_message || ', Shipment Number ' || p_document_shipment_line_num;
746 p_error_message := p_error_message || ', Release Number ' || p_release_num;
747 p_error_message := p_error_message || ', Item Number ' || p_item_num;
748 p_error_message := p_error_message || ', Supplier Item Number ' || p_supplier_item_num;
749
750 end if;
751
752
753 end if; /* error_code is 0 */
754
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 p_error_code := 3;
759 p_error_message := 'Exception in derive_org_id';
760 p_error_message := p_error_message || ' for PO Number ' || p_po_number;
761 p_error_message := p_error_message || ', and supplier_code: ' || p_supplier_code;
762 p_error_message := p_error_message || ', po_header_id ' || p_po_header_id;
763 p_error_message := p_error_message || ', document_line_num ' || p_document_line_num;
764 p_error_message := p_error_message || ', document_shipment_line_num ' || p_document_shipment_line_num;
765 p_error_message := p_error_message || ', item_num ' || p_item_num;
766 p_error_message := p_error_message || ', supplier_item_num ' || p_supplier_item_num;
767 p_error_message := p_error_message || ', release_num ' || p_release_num;
768
769 END derive_org_id;
770
771
772
773 Procedure derive_vendor_id
774 (p_org_id IN NUMBER,
775 p_supplier_code IN VARCHAR,
776 p_vendor_id OUT NOCOPY NUMBER,
777 p_vendor_site_id OUT NOCOPY NUMBER,
778 p_error_code OUT NOCOPY NUMBER,
779 p_error_message OUT NOCOPY VARCHAR) is
780
781 BEGIN
782 p_error_code := 0;
783
784 /*Need to put error message here */
785
786 SELECT
787 vendor_site_id,
788 vendor_id
789 INTO
790 p_vendor_site_id,
791 p_vendor_id
792 FROM po_vendor_sites_all
793 WHERE ece_tp_location_code = p_supplier_code
794 AND org_id = p_org_id;
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 p_error_code := 1;
799 p_vendor_id := 0;
800 p_vendor_site_id := 0;
801 p_error_message := 'No matching vendor_id, vendor_site_id found in derive_vendor_id';
802 p_error_message := p_error_message || ' for supplier code ' || p_supplier_code;
803
804 END derive_vendor_id;
805
806
807 Procedure store_line_vendor_error
808 (p_error_code IN NUMBER,
809 p_error_message IN VARCHAR,
810 line_vendor_error_code OUT NOCOPY NUMBER,
811 line_vendor_error_message OUT NOCOPY VARCHAR) is
812
813 BEGIN
814
815 if (p_error_code > 0) then
816
817 line_vendor_error_code := p_error_code;
818 line_vendor_error_message := p_error_message;
819
820 end if;
821
822 END store_line_vendor_error;
823
824
825
826 Procedure store_line_org_error
827 (p_error_code IN NUMBER,
828 p_error_message IN VARCHAR,
829 line_org_error_code OUT NOCOPY NUMBER,
830 line_org_error_message OUT NOCOPY VARCHAR) is
831
832 BEGIN
833
834 if (p_error_code > 0) then
835
836 line_org_error_code := p_error_code;
837 line_org_error_message := p_error_message;
838
839 end if;
840
841 END store_line_org_error;
842
843
844
845 Procedure store_line_location_error
846 (p_error_code IN NUMBER,
847 p_error_message IN VARCHAR,
848 line_location_error_code OUT NOCOPY NUMBER,
849 line_location_error_message OUT NOCOPY VARCHAR) is
850
851 BEGIN
852
853 if (p_error_code > 0) then
854
855 line_location_error_code := p_error_code;
856 line_location_error_message := p_error_message;
857
858 end if;
859
860 END store_line_location_error;
861
862
863
864 Procedure get_user_id
865 (p_user_name IN VARCHAR,
866 p_user_id OUT NOCOPY NUMBER,
870 l_count NUMBER;
867 p_error_code OUT NOCOPY NUMBER,
868 p_error_message OUT NOCOPY VARCHAR) is
869
871
872 BEGIN
873
874 p_error_code := 0;
875
876 select count(*)
877 into l_count
878 from fnd_user
879 where user_name = upper(p_user_name);
880
881
882 if (l_count = 0) then
883
884 p_error_code := 1;
885 p_user_id := 0;
886 p_error_message := 'Invalid User Name ' || p_user_name;
887
888 else
889
890 select user_id
891 into p_user_id
892 from fnd_user
893 where user_name = upper(p_user_name);
894
895 end if;
896
897 END get_user_id;
898
899
900
901 Procedure pre_validate
902 (p_header_interface_id IN NUMBER,
903 p_ship_to_org_id OUT NOCOPY NUMBER,
904 p_vendor_id OUT NOCOPY NUMBER,
905 p_vendor_site_id OUT NOCOPY NUMBER,
906 p_error_code OUT NOCOPY NUMBER,
907 p_error_message OUT NOCOPY VARCHAR) is
908
909 x_ship_org_count NUMBER;
910
911 BEGIN
912
913 select count(*)
914 into x_ship_org_count
915 from (select distinct to_organization_id
916 from rcv_transactions_interface
917 where header_interface_id = p_header_interface_id);
918
919
920 if (x_ship_org_count = 1) then
921
922 p_error_code := 0;
923
924 select
925 min(to_organization_id),
926 min(vendor_id),
927 min(vendor_site_id)
928 into
929 p_ship_to_org_id,
930 p_vendor_id,
931 p_vendor_site_id
932 from
933 rcv_transactions_interface
934 where
935 header_interface_id = p_header_interface_id;
936
937 update rcv_headers_interface
938 set vendor_id = p_vendor_id,
939 vendor_site_id = p_vendor_site_id,
940 ship_to_organization_id = p_ship_to_org_id
941 where header_interface_id = p_header_interface_id;
942
943 elsif (x_ship_org_count > 1) then
944
945 p_error_code := 1;
946 p_error_message := 'ASN contains lines from Multiple Ship To Organizations';
947
948 else
949
950 p_error_code := 2;
951 p_error_message := 'No matching Ship To Organization found';
952
953 end if;
954
955
956 EXCEPTION
957 WHEN OTHERS THEN
958
959 p_error_code := 3;
960 p_error_message := 'Error in pre_validate procedure for header_interface_id: ' || p_header_interface_id;
961
962 END pre_validate;
963
964
965
966 Procedure derive_line_cols
967 (p_po_header_id IN NUMBER,
968 p_line_num IN NUMBER,
969 p_document_shipment_line_num IN NUMBER,
970 p_release_num IN NUMBER,
971 p_item_id OUT NOCOPY NUMBER,
972 p_item_num OUT NOCOPY VARCHAR,
973 p_item_revision OUT NOCOPY VARCHAR,
974 p_supplier_item_num OUT NOCOPY VARCHAR,
975 p_ship_to_location_id IN OUT NOCOPY NUMBER,
976 p_po_line_id OUT NOCOPY NUMBER,
977 p_line_location_id OUT NOCOPY NUMBER,
978 p_ship_to_org_id OUT NOCOPY NUMBER,
979 p_po_release_id OUT NOCOPY NUMBER,
980 p_error_code OUT NOCOPY NUMBER,
981 p_error_message OUT NOCOPY VARCHAR) is
982
983 x_po_num VARCHAR2(100);
984 l_count NUMBER;
985
986 x_ship_to_location_id NUMBER;
987
988
989 BEGIN
990
991 p_error_code := 0;
992
993 /* save the inbound value for ship_to_location_id for matching */
994
995 x_ship_to_location_id := p_ship_to_location_id;
996
997 select segment1 into x_po_num from po_headers_all where po_header_id=p_po_header_id;
998
999 if ((p_release_num is null) OR (p_release_num = 0)) then
1000
1001 SELECT
1002 count(*)
1003 INTO
1004 l_count
1005 FROM
1006 po_headers_all poh,
1007 po_lines_all pol,
1008 po_line_locations_all pll,
1009 MTL_SYSTEM_ITEMS_KFV MSI
1010 WHERE
1011 POH.PO_HEADER_ID = POL.PO_HEADER_ID
1012 and POL.PO_LINE_ID = PLL.PO_LINE_ID
1013 and pol.item_id = msi.inventory_item_id (+)
1014 and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
1015 and poh.PO_HEADER_ID = p_po_header_id
1016 and pol.LINE_NUM = p_line_num
1017 and pll.shipment_num = p_document_shipment_line_num;
1018
1019
1020 else
1021
1022 SELECT
1023 count(*)
1024 INTO
1025 l_count
1026 FROM
1027 po_headers_all poh,
1028 po_lines_all pol,
1029 po_line_locations_all pll,
1030 po_releases_all prl,
1031 MTL_SYSTEM_ITEMS_KFV MSI
1032 WHERE
1033 POH.PO_HEADER_ID = POL.PO_HEADER_ID
1034 and POL.PO_LINE_ID = PLL.PO_LINE_ID
1035 and pll.PO_RELEASE_ID = prl.PO_RELEASE_ID
1036 and pol.item_id = msi.inventory_item_id (+)
1037 and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
1038 and poh.PO_HEADER_ID = p_po_header_id
1039 and pol.LINE_NUM = p_line_num
1043 end if;
1040 and pll.shipment_num = p_document_shipment_line_num
1041 and prl.release_num = p_release_num;
1042
1044
1045 IF (l_count = 1) THEN
1046
1047 if ((p_release_num is null) OR (p_release_num = 0)) then
1048
1049 SELECT
1050 pol.ITEM_ID,
1051 msi.CONCATENATED_SEGMENTS ITEM_NUM,
1052 pol.ITEM_REVISION,
1053 pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
1054 pll.ship_to_location_id,
1055 pol.PO_LINE_ID,
1056 pll.LINE_LOCATION_ID,
1057 pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID
1058 INTO
1059 p_item_id,
1060 p_item_num,
1061 p_item_revision,
1062 p_supplier_item_num,
1063 p_ship_to_location_id,
1064 p_po_line_id,
1065 p_line_location_id,
1066 p_ship_to_org_id
1067 FROM
1068 po_headers_all poh,
1069 po_lines_all pol,
1070 po_line_locations_all pll,
1071 MTL_SYSTEM_ITEMS_KFV MSI
1072 WHERE
1073 POH.PO_HEADER_ID = POL.PO_HEADER_ID
1074 and POL.PO_LINE_ID = PLL.PO_LINE_ID
1075 and pol.item_id = msi.inventory_item_id (+)
1076 and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
1077 and poh.PO_HEADER_ID = p_po_header_id
1078 and pol.LINE_NUM = p_line_num
1079 and pll.shipment_num = p_document_shipment_line_num;
1080
1081
1082 else
1083
1084 SELECT
1085 pol.ITEM_ID,
1086 msi.CONCATENATED_SEGMENTS ITEM_NUM,
1087 pol.ITEM_REVISION,
1088 pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
1089 pll.ship_to_location_id,
1090 pol.PO_LINE_ID,
1091 pll.LINE_LOCATION_ID,
1092 pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID,
1093 prl.PO_RELEASE_ID
1094 INTO
1095 p_item_id,
1096 p_item_num,
1097 p_item_revision,
1098 p_supplier_item_num,
1099 p_ship_to_location_id,
1100 p_po_line_id,
1101 p_line_location_id,
1102 p_ship_to_org_id,
1103 p_po_release_id
1104 FROM
1105 po_headers_all poh,
1106 po_lines_all pol,
1107 po_line_locations_all pll,
1108 po_releases_all prl,
1109 MTL_SYSTEM_ITEMS_KFV MSI
1110 WHERE
1111 POH.PO_HEADER_ID = POL.PO_HEADER_ID
1112 and POL.PO_LINE_ID = PLL.PO_LINE_ID
1113 and pll.PO_RELEASE_ID = prl.PO_RELEASE_ID
1114 and pol.item_id = msi.inventory_item_id (+)
1115 and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
1116 and poh.PO_HEADER_ID = p_po_header_id
1117 and pol.LINE_NUM = p_line_num
1118 and pll.shipment_num = p_document_shipment_line_num
1119 and prl.release_num = p_release_num;
1120
1121
1122 end if;
1123
1124 /* now validate whether the ship_to_location_id derived from derive_location method
1125 is the same as the ship_to_location_id obtained from the PO Shipment */
1126
1127 if (x_ship_to_location_id <> p_ship_to_location_id) then
1128
1129 p_error_code := 1;
1130 p_error_message := 'Ship-to-location derived from EDI Location Code is different from';
1131 p_error_message := p_error_message || ' the Ship-to_location on PO Shipment';
1132 p_error_message := p_error_message || ' for PO Number ' || x_po_num;
1133 p_error_message := p_error_message || ', Line Number ' || p_line_num;
1134 p_error_message := p_error_message || ', Shipment Number ' || p_document_shipment_line_num;
1135
1136 end if;
1137
1138
1139 END IF;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 p_error_code := 2;
1144 p_error_message := 'Exception in derive_line_cols in deriving fields for ASN line with ';
1145 p_error_message := p_error_message || ' PO Number ' || x_po_num;
1146 p_error_message := p_error_message || ', po_header_id ' || p_po_header_id;
1147 p_error_message := p_error_message || ', document_line_num ' || p_line_num;
1148 p_error_message := p_error_message || ', document_shipment_line_num ' || p_document_shipment_line_num;
1149 p_error_message := p_error_message || ', release_num ' || p_release_num;
1150
1151 END derive_line_cols;
1152
1153
1154 Procedure populate_doc_id
1155 (p_header_interface_id IN NUMBER,
1156 p_location_id IN NUMBER,
1157 p_bill_of_lading IN VARCHAR,
1158 p_packing_slip IN VARCHAR,
1159 p_waybill_airbill_num IN VARCHAR) is
1160
1161 x_err_code NUMBER;
1162
1163 BEGIN
1164
1165 update rcv_headers_interface
1166 set bill_of_lading = p_bill_of_lading,
1167 packing_slip = p_packing_slip,
1168 waybill_airbill_num = p_waybill_airbill_num,
1169 location_id = p_location_id
1170 where header_interface_id = p_header_interface_id;
1171
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 x_err_code := 2;
1176
1177 END populate_doc_id;
1178
1179
1180 Procedure derive_unit_of_measure
1181 (p_uom_code IN VARCHAR,
1182 p_unit_of_measure OUT NOCOPY VARCHAR,
1183 p_error_code OUT NOCOPY NUMBER,
1184 p_error_message OUT NOCOPY VARCHAR) is
1185
1186 l_count NUMBER;
1187
1188 BEGIN
1189
1190 p_error_code := 0;
1191
1192 select count(*)
1193 into l_count
1194 from mtl_units_of_measure_tl
1195 where uom_code = p_uom_code
1196 and language = USERENV('LANG');
1197
1201 p_error_message := 'No matching Unit Of Measure for UOM Code ' || p_uom_code;
1198 if (l_count = 0) then
1199
1200 p_error_code := 1;
1202 p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
1203
1204 elsif (l_count > 1) then
1205
1206 p_error_code := 1;
1207 p_error_message := 'Multiple matching records of Unit Of Measure for UOM Code ' || p_uom_code;
1208 p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
1209
1210 else /* l_count = 1 */
1211
1212 select unit_of_measure
1213 into p_unit_of_measure
1214 from mtl_units_of_measure_tl
1215 where uom_code = p_uom_code
1216 and language = USERENV('LANG');
1217
1218 end if;
1219
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 p_error_code := 2;
1223 p_error_message := 'Error in deriving Unit Of Measure for UOM Code ' || p_uom_code;
1224 p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
1225
1226 END derive_unit_of_measure;
1227
1228 END POS_ASN_XML;
1229