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.M4R_3B2IN_PKG

Source


1 PACKAGE BODY M4R_3B2IN_PKG AS
2 /* $Header: M4R3B2IB.pls 120.1 2005/11/03 05:36:28 amchaudh noship $ */
3 
4 l_debug_level        NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
5 
6 -- Start of comments
7 --        API name         : RCV_TXN_INPROCESS
8 --        Type             : Private
9 --        Pre-reqs         : None.
10 --        Function         : Used in the inprocessing of the XGM for populating the RCV_TXN tables.
11 --        Version          : Current version         1.0
12 --                           Initial version         1.0
13 --        Notes            : None.
14 -- End of comments
15 
16 
17  PROCEDURE RCV_TXN_INPROCESS
18     (p_document_line_num IN NUMBER,
19      p_document_shipment_line_num IN NUMBER,
20      p_release_num IN NUMBER,
21      p_po_number IN VARCHAR,
22      p_supplier_code IN VARCHAR,
23      p_item_num IN VARCHAR,
24      p_supplier_item_num IN VARCHAR,
25      p_org_id  OUT NOCOPY NUMBER,
26      p_ship_to_org_id OUT NOCOPY NUMBER,
27      p_po_header_id OUT NOCOPY NUMBER,
28      p_vendor_id  OUT NOCOPY  NUMBER,
29      p_vendor_site_id  OUT NOCOPY  NUMBER,
30      p_ship_to_edi_location_code IN VARCHAR,
31      p_ship_to_location_id OUT NOCOPY VARCHAR,
32      p_error_code  OUT NOCOPY NUMBER,
33      p_error_message OUT NOCOPY VARCHAR) IS
34 	l_count_num  NUMBER;
35     x_ship_org_num NUMBER;
36   BEGIN
37     IF (l_Debug_Level <= 1) THEN
38            cln_debug_pub.Add('------ Entering M4R_3B2IN_PKG.RCV_TXN_INPROCESS ------');
39            cln_debug_pub.Add('Value of in Variables:');
40            cln_debug_pub.Add('p_document_line_num:' ||p_document_line_num, 1);
41            cln_debug_pub.Add('p_document_shipment_line_num:' ||p_document_shipment_line_num, 1);
42 	   cln_debug_pub.Add('p_release_num:' ||p_release_num, 1);
43 	   cln_debug_pub.Add('p_po_number:' ||p_po_number, 1);
44 	   cln_debug_pub.Add('p_supplier_code:' ||p_supplier_code, 1);
45 	   cln_debug_pub.Add('p_item_num:' ||p_item_num, 1);
46 	   cln_debug_pub.Add('p_supplier_item_num:' ||p_supplier_item_num, 1);
47 	   cln_debug_pub.Add('p_ship_to_edi_location_code:' ||p_ship_to_edi_location_code, 1);
48     END IF;
49    p_error_code := 0;
50     if ((p_release_num is null) OR (p_release_num = 0)) then
51      SELECT min(poh.ORG_ID)
52      INTO p_org_id
53      FROM
54      po_headers_all poh,
55      po_lines_all pol,
56      po_line_locations_all pll,
57      Mtl_system_items_kfv msi
58     WHERE
59      poh.SEGMENT1 = p_po_number AND
60      nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
61      nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
62      pol.po_header_id =  poh.po_header_id AND
63      pol.line_num = p_document_line_num AND
64      pol.po_line_id = pll.po_line_id AND
65      pll.shipment_num = p_document_shipment_line_num AND
66      pll.ship_to_organization_id = msi.organization_id AND
67      pol.item_id = msi.inventory_item_id (+);
68     else
69      SELECT min(poh.ORG_ID)
70      INTO p_org_id
71      FROM
72      po_headers_all poh,
73      po_lines_all pol,
74      po_line_locations_all pll,
75      po_releases_all prl,
76      Mtl_system_items_kfv msi
77     WHERE
78      poh.SEGMENT1 = p_po_number AND
79      nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
80      nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
81      pol.po_header_id =  poh.po_header_id AND
82      pol.line_num = p_document_line_num AND
83      pol.po_line_id = pll.po_line_id AND
84      pll.shipment_num = p_document_shipment_line_num AND
85      pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
86      prl.release_num = p_release_num AND
87      pll.ship_to_organization_id = msi.organization_id AND
88      pol.item_id = msi.inventory_item_id (+);
89     end if;
90 	   IF (l_Debug_Level <= 1) THEN
91            cln_debug_pub.Add(' p_org_id:' ||  p_org_id, 1);
92        END IF;
93      select min(po_header_id)
94      into p_po_header_id
95      from po_headers_all
96      where segment1 = p_po_number
97      and org_id = p_org_id;
98      IF (l_Debug_Level <= 1) THEN
99            cln_debug_pub.Add(' p_po_header_id' || p_po_header_id, 1);
100      END IF;
101          if ((p_release_num is null) OR (p_release_num = 0)) then
102           select
103             min(pll.ship_to_organization_id)
104           into
105             p_ship_to_org_id
106           from
107             po_headers_all poh,
108             po_lines_all pol,
109             po_line_locations_all pll
110           where
111             poh.po_header_id = p_po_header_id and
112             poh.po_header_id = pol.po_header_id and
113             pol.line_num = p_document_line_num and
114             pol.po_line_id = pll.po_line_id and
115             pll.shipment_num = p_document_shipment_line_num;
116          else
117           select
118             min(pll.ship_to_organization_id)
119           into
120             p_ship_to_org_id
121           from
122             po_headers_all poh,
123             po_lines_all pol,
124             po_line_locations_all pll,
125             po_releases_all prl
126           where
127             poh.po_header_id = p_po_header_id and
128             poh.po_header_id = pol.po_header_id and
129             pol.line_num = p_document_line_num and
130             pol.po_line_id = pll.po_line_id and
131             pll.shipment_num = p_document_shipment_line_num and
132             pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
133             prl.release_num = p_release_num;
134          end if;
135      IF (l_Debug_Level <= 1) THEN
136            cln_debug_pub.Add(' p_ship_to_org_id:' || p_ship_to_org_id, 1);
137      END IF;
138 	 --- POS_ASN_XML.DERIVE_vendor_ID	 begins here
139 	 select
140 	 	poh.vendor_site_id,poh.vendor_id
141 	 into 	p_vendor_site_id, p_vendor_id
142 	 from
143 	 	po_headers_all poh
144 	 where
145 	    poh.po_header_id = p_po_header_id and
146 	    poh.segment1 =p_po_number;
147      IF (l_Debug_Level <= 1) THEN
148            cln_debug_pub.Add(' p_vendor_site_id:' ||p_vendor_site_id, 1);
149            cln_debug_pub.Add(' p_vendor_id:' ||p_vendor_id, 1);
150      END IF;
151 	----ship to organisaction id
152 
153 	 BEGIN
154 	      SELECT location_id
155      	  INTO  p_ship_to_location_id
156      	  FROM hr_locations
157      	  WHERE ECE_TP_LOCATION_CODE = p_ship_to_edi_location_code;
158 	 EXCEPTION
159 	      WHEN no_data_found then
160 		  IF (l_Debug_Level <= 1) THEN
161         	    	  cln_debug_pub.Add('No Data Found in the hr_locations table for p_ship_to_edi_location_code :'||p_ship_to_location_id, 1);
162      		  END IF;
163 	 END;
164 
165 	 IF (l_Debug_Level <= 1) THEN
166         	  cln_debug_pub.Add('p_ship_to_location_id:' ||p_ship_to_location_id, 1);
167          END IF;
168 
169      IF (l_Debug_Level <= 1) THEN
170          cln_debug_pub.Add('------ Exiting M4R_3B2IN_PKG.RCV_TXN_INPROCESS ------');
171      END IF;
172    EXCEPTION
173     WHEN OTHERS THEN
174      p_error_code := 1;
175      p_vendor_id := 0;
176      p_vendor_site_id := 0;
177      p_error_message := 'Exception in M4R_3B2IN_PKG.RCV_TXN_INPROCESS ';
178 end RCV_TXN_INPROCESS;
179 
180 
181 
182 -- Start of comments
183 --        API name         : RCV_TXN_INPROCESS2
184 --        Type             : Private
185 --        Pre-reqs         : None.
186 --        Function         : Used in the inprocessing of the XGM for populating the RCV_TXN tables.
187 --        Version          : Current version         1.1
188 --                           Initial version         1.0
189 --        Notes            : None.
190 -- End of comments
191 
192 
193 
194 PROCEDURE  RCV_TXN_INPROCESS2
195    (p_po_header_id IN NUMBER,
196    p_line_num IN NUMBER,
197    p_document_shipment_line_num IN NUMBER,
198    p_release_num IN NUMBER,
199    p_item_id OUT NOCOPY NUMBER,
200    p_item_num OUT NOCOPY VARCHAR,
201    p_item_revision OUT NOCOPY VARCHAR,
202    p_supplier_item_num OUT NOCOPY VARCHAR,
203    p_ship_to_location_id IN OUT NOCOPY NUMBER,
204    p_po_line_id OUT NOCOPY NUMBER,
205    p_line_location_id OUT NOCOPY NUMBER,
206    p_ship_to_org_id OUT NOCOPY NUMBER,
207    p_po_release_id OUT NOCOPY NUMBER,
208    p_uom_code IN VARCHAR,
209    p_unit_of_measure OUT NOCOPY VARCHAR,
210    p_error_code OUT NOCOPY NUMBER,
211    p_error_message OUT NOCOPY VARCHAR) is
212    x_po_num VARCHAR2(100);
213    l_count NUMBER;
214    x_ship_to_location_id NUMBER;
215 BEGIN
216      IF (l_Debug_Level <= 1) THEN
217            cln_debug_pub.Add('----- Entering M4R_3B2IN_PKG.RCV_TXN_INPROCESS2-----');
218            cln_debug_pub.Add('Value of in Variables:');
219            cln_debug_pub.Add('p_po_header_id:' ||p_po_header_id, 1);
220            cln_debug_pub.Add('p_line_num:' ||p_line_num, 1);
221            cln_debug_pub.Add('p_document_shipment_line_num:' ||p_document_shipment_line_num, 1);
222            cln_debug_pub.Add('p_release_num :' || p_release_num , 1);
223            cln_debug_pub.Add('p_ship_to_location_id:' ||p_ship_to_location_id, 1);
224            cln_debug_pub.Add('p_uom_code:' ||p_uom_code, 1);
225      END IF;
226 
227   p_error_code := 0;
228   /* save the inbound value for ship_to_location_id for matching */
229   x_ship_to_location_id := p_ship_to_location_id;
230 
231   select segment1 into x_po_num from po_headers_all where po_header_id=p_po_header_id;
232 
233 if ((p_release_num is null) OR (p_release_num = 0)) then
234 
235  SELECT
236    pol.ITEM_ID,
237    msi.CONCATENATED_SEGMENTS ITEM_NUM,
238    pol.ITEM_REVISION,
239    pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
240    pll.ship_to_location_id,
241    pol.PO_LINE_ID,
242    pll.LINE_LOCATION_ID,
243    pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID
244  INTO
245    p_item_id,
246    p_item_num,
247    p_item_revision,
248    p_supplier_item_num,
249    p_ship_to_location_id,
250    p_po_line_id,
251    p_line_location_id,
252    p_ship_to_org_id
253  FROM
254   po_headers_all poh,
255   po_lines_all pol,
256   po_line_locations_all pll,
257   MTL_SYSTEM_ITEMS_KFV MSI
258  WHERE
259   POH.PO_HEADER_ID = POL.PO_HEADER_ID
260   and POL.PO_LINE_ID = PLL.PO_LINE_ID
261   and pol.item_id = msi.inventory_item_id (+)
262   and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
263   and poh.PO_HEADER_ID = p_po_header_id
264   and pol.LINE_NUM = p_line_num
265   and pll.shipment_num = p_document_shipment_line_num;
266 else
267   SELECT
268    pol.ITEM_ID,
269    msi.CONCATENATED_SEGMENTS ITEM_NUM,
270    pol.ITEM_REVISION,
271    pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
272    pll.ship_to_location_id,
273    pol.PO_LINE_ID,
274    pll.LINE_LOCATION_ID,
275    pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID,
276    prl.PO_RELEASE_ID
277 INTO
278    p_item_id,
279    p_item_num,
280    p_item_revision,
281    p_supplier_item_num,
282    p_ship_to_location_id,
283    p_po_line_id,
284    p_line_location_id,
285    p_ship_to_org_id,
286    p_po_release_id
287 FROM
288    po_headers_all poh,
289    po_lines_all pol,
290    po_line_locations_all pll,
291    po_releases_all prl,
292    MTL_SYSTEM_ITEMS_KFV MSI
293 WHERE
294   POH.PO_HEADER_ID = POL.PO_HEADER_ID
295   and POL.PO_LINE_ID = PLL.PO_LINE_ID
296   and pll.PO_RELEASE_ID = prl.PO_RELEASE_ID
297   and pol.item_id = msi.inventory_item_id (+)
298   and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
299   and poh.PO_HEADER_ID = p_po_header_id
300   and pol.LINE_NUM = p_line_num
301   and pll.shipment_num = p_document_shipment_line_num
302   and prl.release_num = p_release_num;
303 
304 
305  end if;
306 
307   IF (l_Debug_Level <= 1) THEN
308            cln_debug_pub.Add('p_item_id:' ||p_item_id, 1);
309            cln_debug_pub.Add('p_item_num:' ||p_item_num, 1);
310            cln_debug_pub.Add('p_item_revision:' ||p_item_revision, 1);
311            cln_debug_pub.Add('p_supplier_item_num :' || p_supplier_item_num, 1);
312            cln_debug_pub.Add('p_ship_to_location_id:' ||p_ship_to_location_id, 1);
313            cln_debug_pub.Add('p_po_line_id:' ||p_po_line_id, 1);
314            cln_debug_pub.Add('p_line_location_id :' || p_line_location_id, 1);
315            cln_debug_pub.Add('p_ship_to_location_id:' ||p_ship_to_location_id, 1);
316            cln_debug_pub.Add('p_po_release_id:' ||p_po_release_id, 1);
317    END IF;
318 
319 
320   /* now validate whether the ship_to_location_id derived from derive_location method
321      is the same as the ship_to_location_id obtained from the PO Shipment */
322 
323    if (x_ship_to_location_id <> p_ship_to_location_id) then
324       p_error_code := 1;
325       p_error_message := 'Ship-to-location derived from EDI Location Code is different from';
326       p_error_message := p_error_message || ' the Ship-to_location on PO Shipment';
327       p_error_message := p_error_message || ' for PO Number ' || x_po_num;
328       p_error_message := p_error_message || ', Line Number  ' || p_line_num;
329       p_error_message := p_error_message || ', Shipment Number  ' || p_document_shipment_line_num;
330    end if;
331 
332 
333   select count(*)
334   into l_count
335   from mtl_units_of_measure_tl
336   where uom_code = p_uom_code
337   and language = USERENV('LANG');
338 
339   if (l_count = 0) then
340 
341     p_error_code := 1;
342     p_error_message := 'No matching Unit Of Measure for UOM Code ' || p_uom_code;
343     p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
344 
345   elsif (l_count > 1) then
346 
347      p_error_code := 1;
348      p_error_message := 'Multiple matching records of Unit Of Measure for UOM Code ' || p_uom_code;
349      p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
350 
351   else          /* l_count = 1*/
352 
353    select unit_of_measure
354    into p_unit_of_measure
355    from mtl_units_of_measure_tl
356    where uom_code = p_uom_code
357    and language = USERENV('LANG');
358 
359   end if;
360 
361   IF (l_Debug_Level <= 1) THEN
362        cln_debug_pub.Add('----- Exiting M4R_3B2IN_PKG.RCV_TXN_INPROCESS2-----');
363   END IF;
364 
365 EXCEPTION
366     WHEN OTHERS THEN
367       p_error_code := 2;
368       p_error_message := 'Exception in derive_line_cols in deriving fields for ASN line with ';
369       p_error_message := p_error_message || ' PO Number ' || x_po_num;
370       p_error_message := p_error_message || ', po_header_id ' || p_po_header_id;
371       p_error_message := p_error_message || ', document_line_num ' || p_line_num;
372       p_error_message := p_error_message || ', document_shipment_line_num ' || p_document_shipment_line_num;
373       p_error_message := p_error_message || ', release_num ' || p_release_num;
374       p_error_message := p_error_message || ', UOM Code ' || p_uom_code;
375       p_error_message := p_error_message || ' , and language ' || USERENV('LANG');
376 END RCV_TXN_INPROCESS2 ;
377 
378 
379 
380 -- Start of comments
381 --        API name         : GET_VALUES_HEADER
382 --        Type             : Private
383 --        Pre-reqs         : None.
384 --        Function         : Used in the getting the values at the header level of the XGM.
385 --        Version          : Current version         1.0
386 --                           Initial version         1.0
387 --        Notes            : None.
388 -- End of comments
389 
390 PROCEDURE GET_VALUES_HEADER
391 (p_header_interface_id IN NUMBER,
392     p_ship_to_org_id OUT NOCOPY NUMBER,
393     p_vendor_id OUT NOCOPY NUMBER,
394     p_vendor_site_id OUT NOCOPY NUMBER,
395     p_bill_of_lading OUT NOCOPY VARCHAR,
396     p_waybill_airbill_num OUT NOCOPY VARCHAR,
397 	p_packing_slip OUT NOCOPY VARCHAR,
398     p_error_code OUT NOCOPY NUMBER,
399     p_error_message OUT NOCOPY VARCHAR
400 	) is
401     x_ship_org_count NUMBER;
402 BEGIN
403 
404    IF (l_Debug_Level <= 1) THEN
405            cln_debug_pub.Add('----- Entering M4R_3B2IN_PKG.GET_VALUES_HEADER -----');
406            cln_debug_pub.Add('Value of in Variables:');
407            cln_debug_pub.Add('p_header_interface_id:' ||p_header_interface_id, 1);
408    END IF;
409 
410    select count(*)
411    into x_ship_org_count
412    from (select distinct to_organization_id
413          from rcv_transactions_interface
414          where header_interface_id = p_header_interface_id);
415 
416 
417 
418    if (x_ship_org_count = 1) then
419     p_error_code := 0;
420 
421     select
422      min(to_organization_id),
423      min(vendor_id),
424      min(vendor_site_id),
425 	 max(bill_of_lading),
426 	 max(waybill_airbill_num),
427 	 max(packing_slip)
428    into
429      p_ship_to_org_id,
430      p_vendor_id,
431      p_vendor_site_id,
432      p_bill_of_lading,
433 	 p_waybill_airbill_num,
434 	 p_packing_slip
435    from
436     rcv_transactions_interface
437    where
438      header_interface_id = p_header_interface_id;
439 
440    IF (l_Debug_Level <= 1) THEN
441            cln_debug_pub.Add('p_ship_to_org_id:' ||p_ship_to_org_id, 1);
442            cln_debug_pub.Add('p_vendor_id:' ||p_vendor_id, 1);
443            cln_debug_pub.Add('p_vendor_site_id:' ||p_vendor_site_id, 1);
444    END IF;
445 
446    update rcv_headers_interface
447    set vendor_id = p_vendor_id,
448        vendor_site_id = p_vendor_site_id,
449        ship_to_organization_id = p_ship_to_org_id,
450 	   bill_of_lading = p_bill_of_lading,
451        waybill_airbill_num =p_waybill_airbill_num,
452        packing_slip = p_packing_slip
453    where header_interface_id = p_header_interface_id;
454 
455    update rcv_transactions_interface
456    set bill_of_lading = p_bill_of_lading,
457        waybill_airbill_num =p_waybill_airbill_num,
458        packing_slip = p_packing_slip
459    where header_interface_id = p_header_interface_id;
460 
461    elsif (x_ship_org_count > 1) then
462      p_error_code := 1;
463      p_error_message := 'ASN contains lines from Multiple Ship To Organizations';
464    else
465      p_error_code := 2;
466      p_error_message := 'No matching Ship To Organization found';
467    end if;
468 
469    IF (l_Debug_Level <= 1) THEN
470            cln_debug_pub.Add('----- Exiting M4R_3B2IN_PKG.GET_VALUES_HEADER -----');
471    END IF;
472 EXCEPTION
473      WHEN OTHERS THEN
474      p_error_code := 3;
475      p_error_message := 'Error in GET_VALUES_HEADER procedure for header_interface_id: ' || p_header_interface_id;
476 END GET_VALUES_HEADER;
477 
478 
479 END M4R_3B2IN_PKG;