eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POS_ASN_XML

Source


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