[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_AUTO_RCV_PROC
Source
1 PACKAGE BODY JMF_SHIKYU_AUTO_RCV_PROC AS
2 --$Header: JMFRSKUB.pls 120.25 2006/12/16 01:51:06 vchu noship $
3 --+===========================================================================+
4 --| Copyright (c) 2005 Oracle Corporation |
5 --| Redwood Shores, California, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| |
9 --| FILENAME : JMFRSKUB.pls |
10 --| |
11 --| DESCRIPTION: Body file of the Auto-Receive Subcontracting |
12 --| Components Processor package. |
13 --| This processor automatically receives |
14 --| Subcontracting components for manufacturing |
15 --| outsourced assemblies into the Manufacturing |
16 --| Partner organization, after the predefined |
17 --| in-transit lead time. |
18 --| |
19 --| FUNCTION/PROCEDURE: auto_rcv_subcon_comp |
20 --| auto_receive_by_inventory |
21 --| auto_receive |
22 --| validate_ship_from_to |
23 --| validate_receive_date |
24 --| validate_rcv_error |
25 --| compare_lines_quantity |
26 --| get_in_transit |
27 --| get_customer_id |
28 --| get_supplier_id |
29 --| |
30 --| HISTORY: |
31 --| 19-MAY-2005 jizheng Created. |
32 --| 05-OCT_2005 jizheng Delete the parameter 'p_org_id' |
33 --| 13-OCT-2005 jizheng add some log for debug |
34 --| 31-OCT-2005 jizheng add input parameter po_line_location_id |
35 --| when invoke JMF_SHIKYU_RCV_PVT. |
36 --| process_rcv_trx in process_rcv_interface |
37 --| 22-Nov-2005 jizheng add a new logic for get shipping method |
38 --| from replenishment SO 's header |
39 --| 22-Nov-2005 jizheng get transit date from shipping net work |
40 --| 05-Dec-2005 jizheng add a new method for back ordered line |
41 --| 15-Dec-2005 jizheng remove full table scan for performance |
42 --| 18-Jan-2006 jizheng fix bug 4961147, HQAX1:SCMX1DM2 : AUTO |
43 --| RECEIVING INSERT DUPLICATE RECORDS IN RCV |
44 --| INTERFACE |
45 --| 07-Feb-2006 jizheng remove all fnd_file.put_line() |
46 --| 14-Mar-2006 SHU remove all Commented code |
47 --| 10-May-2006 Amy updated procecure .get_backorder_shipped_ |
48 --| quantity to fix bug #5212672 |
49 --| 19-May-2006 THE2 pick up the Sales Order Lines whose status|
50 --| is 'Closed' to fix #5231430 |
51 --| 08-Jun-2006 THE2 correct the hard code of operation unit |
52 --| 15-JUN-2006 THE2 Add locator_id process logic |
53 --| 27-JUN-2006 THE2 Removed the logic for checking duplicated |
54 --| RCV transactions. |
55 --| 28-JUN-2006 THE2 Met the interface change of procedure |
56 --| jmf_shikyu_util.Get_Replenish_So_Returned_Qty|
57 --| and JMF_SHIKYU_RCV_PVT.process_rcv_trx |
58 --| 03-AUG-2006 THE2 Modified cursor po_distributions_c to fix |
59 --| bug #5434983 |
60 --| 18-AUG-2006 THE2 Modified cursor po_distributions_c again |
61 --| to fix bug #5434983 |
62 --| 25-AUG-2006 THE2 Modified cursor po_distributions_c again |
63 --| and Changed process_rcv_interface() |
64 --| logic to process receiving against each SO|
65 --| line to fix bug #5434983 |
66 --| 06-SEP-2006 THE2 Modified cursor all_inventory_c to fix bug|
67 --| #5510525 |
68 --| 10-OCT-2006 THE2 Bug fix for 5592230: Changed |
69 --| get_supplier_id() from function to |
70 --| procedure in order to get the supplier |
71 --| site id and pass to the newly added |
72 --| parameter p_vendor_site_id of |
73 --| JMF_SHIKYU_RCV_PVT.process_rcv_header() |
74 --| 09-NOV-2006 THE2 Modified source to fix bug#5647346 |
75 --| 14-NOV-2006 VCHU Bug fix for 5659317: This package became |
76 --| invalid in xBuild 16, since the changes |
77 --| in version 120.22, including changes to |
78 --| the call to |
79 --| JMF_SHIKYU_RCV_PVT.process_rcv_header() |
80 --| because of a signature change resulted |
81 --| from bug fix for 5592230, got overwritten.|
82 --| 14-DEC-2006 THE2 Modified source to fix bug#5708707 |
83 --+===========================================================================+
84
85 --==========================================================================
86 -- API NAME: AUTO_RCV_SUBCON_COMP
87 --
88 -- DESCRIPTION: the procedure is the main procedure of this package, it will be
89 -- run in concurrent program.
90 --
91 -- PARAMETERS: In: p_org_id OMAC
92 -- p_tp_org_id inventory org
93
94 --
95 -- Out: errbuf OUT NOCOPY varchar2
96 -- retcode OUT NOCOPY VARCHAR2
97 --
98 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
99 --
100 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
101 --===========================================================================
102
103 PROCEDURE AUTO_RCV_SUBCON_COMP
104 ( errbuf OUT NOCOPY VARCHAR2
105 , retcode OUT NOCOPY VARCHAR2
106 , p_tp_org_id IN NUMBER
107 )
108 IS
109
110 l_api_name VARCHAR2(30) := 'AUTO_RCV_SUBCON_COMP';
111 l_inventory_org_id NUMBER;
112 l_shikyu_not_enable VARCHAR2(500);
113 l_conc_succ BOOLEAN;
114 l_org_id NUMBER := MO_GLOBAL.get_current_org_id;
115 --find all TP inventory by org_id
116 CURSOR all_inventory_c IS
117 SELECT haou.organization_id
118 FROM mtl_parameters mp,
119 hr_organization_information hoi,
120 hr_all_organization_units haou,
121 HR_ALL_ORGANIZATION_UNITS_TL haoutl
122 WHERE mp.organization_id = hoi.organization_id
123 AND haou.organization_id = hoi.organization_id
124 AND haou.organization_id = haoutl.organization_id
125 AND mp.trading_partner_org_flag = 'Y'
126 AND hoi.org_information_context = 'Accounting Information'
127 AND hoi.org_information3 = MO_GLOBAL.get_current_org_id
128 AND haoutl.LANGUAGE = USERENV('LANG');
129
130 BEGIN
131 JMF_SHIKYU_UTIL.debug_output
132 (
133 p_output_to => 'FND_LOG.STRING'
134 ,p_api_name => l_api_name
135 ,p_message => 'procedure begin'
136 );
137 JMF_SHIKYU_UTIL.debug_output
138 (
139 p_output_to => 'FND_LOG.STRING'
140 ,p_api_name => l_api_name
141 ,p_message => 'p_tp_org_id:'||p_tp_org_id
142 );
143 JMF_SHIKYU_UTIL.debug_output
144 (
145 p_output_to => 'FND_LOG.STRING'
146 ,p_api_name => l_api_name
147 ,p_message => 'l_org_id :'||l_org_id
148 );
149
150 -- get org_id from MOAC
151 l_org_id := MO_GLOBAL.get_current_org_id;
152
153 -- is Shikyu is not enable , show a error message.
154 IF (FND_PROFILE.VALUE('JMF_SHK_CHARGE_BASED_ENABLED')='Y')-- OR 1=1
155 THEN
156 IF p_tp_org_id IS NULL
157 THEN
158 -- begin log
159 JMF_SHIKYU_UTIL.debug_output
160 (
161 p_output_to => 'FND_LOG.STRING'
162 ,p_api_name => l_api_name
163 ,p_message => 'p_tp_org_id is null. '
164 );
165 -- end log
166
167 -- select all inventory id, under this OU , find inventory_org_id;
168 OPEN all_inventory_c;
169 LOOP
170 FETCH all_inventory_c
171 INTO l_inventory_org_id;
172 EXIT WHEN all_inventory_c%NOTFOUND;
173
174 -- begin log
175 JMF_SHIKYU_UTIL.debug_output
176 (
177 p_output_to => 'FND_LOG.STRING'
178 ,p_api_name => l_api_name
179 ,p_message => 'The inventory org id is : '||l_inventory_org_id
180 );
181 --end log
182
183 -- begin auto_receive_by_inventory, loop the id
184 auto_receive_by_inventory( p_org_id => l_org_id
185 , p_inventory_org_id => l_inventory_org_id
186 );
187
188
189 END LOOP;
190 ELSE /*inverntory is not null*/
191 -- use this invertory id
192 auto_receive_by_inventory( p_org_id => l_org_id
193 , p_inventory_org_id => p_tp_org_id
194 );
195 END IF; /*inventory id is null*/
196
197 ELSE/*FND_PROFILE.VALUE('JMF_SHIKYU_ENABLED')='Y'*/
198 fnd_message.SET_NAME('JMF', 'JMF_SHK_NOT_ENABLE');
199 l_shikyu_not_enable := fnd_message.get;
200
201 JMF_SHIKYU_UTIL.debug_output
202 (
203 p_output_to => 'FND_LOG.STRING'
204 ,p_api_name => l_api_name
205 ,p_message => l_shikyu_not_enable
206 );
207
208 l_conc_succ := fnd_concurrent.set_completion_status(status => 'WARNING'
209 , message => l_shikyu_not_enable
210 );
211 END IF; /*FND_PROFILE.VALUE('JMF_SHIKYU_ENABLED')='Y'*/
212
213 JMF_SHIKYU_UTIL.debug_output
214 (
215 p_output_to => 'FND_LOG.STRING'
216 ,p_api_name => l_api_name
217 ,p_message => 'END procedure. '
218 );
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 JMF_SHIKYU_UTIL.debug_output
223 (
224 p_output_to => 'FND_LOG.STRING'
225 ,p_api_name => l_api_name
226 ,p_message => 'Unknown error : '||SQLCODE||SQLERRM
227 );
228
229 l_conc_succ := fnd_concurrent.set_completion_status( status => 'WARNING'
230 , message => SQLCODE||SQLERRM
231 );
232
233 END AUTO_RCV_SUBCON_COMP;
234
235 --==========================================================================
236 -- API NAME: AUTO_RCVEIVE_by_inventory
237 --
238 -- DESCRIPTION: the procedure is auto receive the SO belong a inventory
239 --
240 -- PARAMETERS: In: p_org_id
241 -- p_inventory_org_id
242
243 -- Out:
244 --
245 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
246 --
247 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
248 --===========================================================================
249 PROCEDURE auto_receive_by_inventory
250 ( p_org_id IN NUMBER
251 , p_inventory_org_id IN NUMBER
252 )
253 IS
254
255 l_api_name VARCHAR2(50) := 'auto_receive_by_inventory';
256 l_header_id oe_order_headers_all.header_id%TYPE;
257 l_customer_id NUMBER;
258 l_customer_site_id NUMBER;
259
260
261 CURSOR replenish_so_c(customer_id NUMBER, customer_site VARCHAR2)
262 IS
263 SELECT h.header_id
264 FROM oe_order_headers_all h
265 WHERE
266 h.header_id IN (SELECT DISTINCT r.replenishment_so_header_id FROM jmf_shikyu_replenishments r)
267 AND h.org_Id = p_org_id
268 AND h.sold_to_org_id = customer_id
269 -- AND h.header_id = r.replenishment_so_header_id
270 -- AND h.sold_to_site_use_id = customer_site
271 AND (h.flow_status_code = 'BOOKED'
272 OR h.flow_status_code = 'INVOICED'
273 OR h.flow_status_code = 'INVOICE_HOLD'
274 OR h.flow_status_code = 'INVOICE_INCOMPLETE'
275 OR h.flow_status_code = 'ACTIVE'
276 OR h.flow_status_code = 'CUSTOMER_ACCEPTED'
277 OR h.flow_status_code = 'INTERNAL_APPROVED'
278 OR h.flow_status_code = 'PENDING_CUSTOMER_ACCEPTANCE'
279 OR h.flow_status_code = 'PENDING_INTERNAL_APPROVAL'
280 OR h.flow_status_code = 'SUBMITTED'
281 OR h.flow_status_code = 'WORKING'
282 OR h.flow_status_code = 'CLOSED');
283
284 BEGIN
285
286 JMF_SHIKYU_UTIL.debug_output
287 (
288 p_output_to => 'FND_LOG.STRING'
289 ,p_api_name => l_api_name
290 ,p_message => 'procedure begin'
291 );
292 JMF_SHIKYU_UTIL.debug_output
293 (
294 p_output_to => 'FND_LOG.STRING'
295 ,p_api_name => l_api_name
296 ,p_message => ' org_id : '||p_org_id||' inv_org_id:'||p_inventory_org_id
297 );
298
299 -- get customer id and customer site id by org_id
300 get_customer_id( p_org_inventory_id => p_inventory_org_id
301 , x_customer_id => l_customer_id
302 , x_customer_site_id => l_customer_site_id
303 );
304
305
306 -- debug log
307 JMF_SHIKYU_UTIL.debug_output
308 (
309 p_output_to => 'FND_LOG.STRING'
310 ,p_api_name => l_api_name
311 ,p_message => 'customer_site_id : '||l_customer_site_id
312 );
313
314 -- find regular replenishment SO's header_id, loop by header_id, do auto receive
315 OPEN replenish_so_c(l_customer_id, l_customer_site_id);
316 LOOP
317 FETCH replenish_so_c INTO l_header_id;
318 EXIT WHEN replenish_so_c%NOTFOUND;
319 -- begin log
320 JMF_SHIKYU_UTIL.debug_output
321 (
322 p_output_to => 'FND_LOG.STRING'
323 ,p_api_name => l_api_name
324 ,p_message => 'The SO header id is :'||l_header_id
325 );
326 --end log
327
328 -- do_auto_receive() procedure
329 auto_receive(p_inventory_org_id => p_inventory_org_id
330 , p_header_id => l_header_id); --l_header_id
331
332 END LOOP;
333 CLOSE replenish_so_c;
334
335 JMF_SHIKYU_UTIL.debug_output
336 (
337 p_output_to => 'FND_LOG.STRING'
338 ,p_api_name => l_api_name
339 ,p_message => 'END procedure.'
340 );
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 JMF_SHIKYU_UTIL.debug_output
345 (
346 p_output_to => 'FND_LOG.STRING'
347 ,p_api_name => l_api_name
348 ,p_message => 'Unknown error'||SQLCODE||SQLERRM
349 );
350 RAISE;
351
352 END auto_receive_by_inventory;
353
354 --==========================================================================
355 -- API NAME: AUTO_RECEIVE
356 --
357 -- DESCRIPTION: the procedure is auto receive the so lines belong one SO
358 --
359 -- PARAMETERS: In: p_header_id replenishment SO 's header_id
360 -- p_inventory_org_id inventory org id
361
362 -- Out:
363 --
364 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
365 --
366 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
367 --===========================================================================
368 PROCEDURE auto_receive
369 ( p_inventory_org_id IN NUMBER
370 , p_header_id IN NUMBER
371 )
372 IS
373
374 l_api_name VARCHAR2(20):= 'auto_receive';
375 l_line_id oe_order_lines_all.line_id%TYPE;
376
377 l_auto_receive_quantity oe_order_lines_all.shipped_quantity%TYPE;
378
379 l_date_flag NUMBER;
380 l_ship_flag NUMBER;
381
382 l_po_header_id jmf_shikyu_replenishments.replenishment_po_header_id%TYPE;
383 l_po_line_id jmf_shikyu_replenishments.replenishment_po_line_id%TYPE;
384 l_po_shipment_id jmf_shikyu_replenishments.replenishment_po_shipment_id%TYPE;
385
386 l_ship_date oe_order_lines.ACTUAL_SHIPMENT_DATE%TYPE;
387 l_ship_from_org_id oe_order_headers_all.ship_from_org_id%TYPE;
388 l_ship_to_org_id oe_order_headers_all.ship_to_org_id%TYPE;
389 l_ship_method oe_order_headers_all.shipping_method_code%TYPE;
390 l_sold_to_org_id oe_order_lines_all.sold_to_org_id%TYPE;
391
392 l_primary_uom_code VARCHAR2(30);
393
394 l_lines_id line_id_tbl := line_id_tbl();
395
396 CURSOR replenish_so_line_c IS
397 SELECT r.replenishment_so_line_id
398 FROM jmf_shikyu_replenishments r
399 WHERE r.replenishment_so_header_id = p_header_id;
400
401 BEGIN
402
403 JMF_SHIKYU_UTIL.debug_output
404 (
405 p_output_to => 'FND_LOG.STRING'
406 ,p_api_name => l_api_name
407 ,p_message => 'procedure begin'
408 );
409 JMF_SHIKYU_UTIL.debug_output
410 (
411 p_output_to => 'FND_LOG.STRING'
412 ,p_api_name => l_api_name
413 ,p_message => 'p_header_id :'||p_header_id
414 );
415
416 -- init l_lines_id
417 l_lines_id := line_id_tbl();
418 OPEN replenish_so_line_c;
419 LOOP
420 FETCH replenish_so_line_c INTO l_line_id;
421 EXIT WHEN replenish_so_line_c%NOTFOUND;
422
423 -- begin log
424 JMF_SHIKYU_UTIL.debug_output
425 (
426 p_output_to => 'FND_LOG.STRING'
427 ,p_api_name => l_api_name
428 ,p_message => 'l_line_id'||l_line_id
429 );
430 -- end log
431
432 BEGIN
433 --select line_ship_from_org_id by line_id
434 SELECT
435 r.replenishment_po_header_id
436 , r.replenishment_po_line_id
437 , r.replenishment_po_shipment_id
438 INTO
439 l_po_header_id
440 , l_po_line_id
441 , l_po_shipment_id
442 FROM jmf_shikyu_replenishments r
443 WHERE r.replenishment_so_line_id = l_line_id;
444 EXCEPTION
445 WHEN no_data_found THEN
446 JMF_SHIKYU_UTIL.debug_output
447 (
448 p_output_to => 'FND_LOG.STRING'
449 ,p_api_name => l_api_name
450 ,p_message => 'no data find when select PO info by so_line_id in jmf_shikyu_replenishments'
451 );
452 END;
453
454 -- begin debug log
455 JMF_SHIKYU_UTIL.debug_output
456 (
457 p_output_to => 'FND_LOG.STRING'
458 ,p_api_name => l_api_name
459 ,p_message => 'l_po_header_id'||l_po_header_id
460 );
461 JMF_SHIKYU_UTIL.debug_output
462 (
463 p_output_to => 'FND_LOG.STRING'
464 ,p_api_name => l_api_name
465 ,p_message => 'l_po_line_id'||l_po_line_id
466 );
467 JMF_SHIKYU_UTIL.debug_output
468 (
469 p_output_to => 'FND_LOG.STRING'
470 ,p_api_name => l_api_name
471 ,p_message => 'l_po_shipment_id'||l_po_shipment_id
472 );
473 -- end debug log
474
475 BEGIN
476 --select actual_shipment_date by line id
477 SELECT l.actual_shipment_date --actual_shipment_date
478 , l.ship_from_org_id
479 , l.ship_to_org_id
480 , l.sold_to_org_id
481 , l.shipping_method_code
482 INTO l_ship_date
483 , l_ship_from_org_id
484 , l_ship_to_org_id
485 , l_sold_to_org_id
486 , l_ship_method
487 FROM oe_order_lines_all l
488 WHERE l.line_id = l_line_id;
489
490 EXCEPTION
491 WHEN no_data_found THEN
492 JMF_SHIKYU_UTIL.debug_output
493 (
494 p_output_to => 'FND_LOG.STRING'
495 ,p_api_name => l_api_name
496 ,p_message => 'no data find when select ship org id or other info by line_id in oe_order_lines_all'
497 );
498 END;
499
500 --get ship method from SO header
501 IF l_ship_method IS NULL
502 THEN
503 SELECT
504 shipping_method_code
505 INTO
506 l_ship_method
507 FROM oe_order_headers_all
508 WHERE header_id = p_header_id;
509
510 END IF;
511
512 -- begin debug log
513 JMF_SHIKYU_UTIL.debug_output
514 (
515 p_output_to => 'FND_LOG.STRING'
516 ,p_api_name => l_api_name
517 ,p_message => 'l_ship_date'||l_ship_date
518 );
519 JMF_SHIKYU_UTIL.debug_output
520 (
521 p_output_to => 'FND_LOG.STRING'
522 ,p_api_name => l_api_name
523 ,p_message => 'l_ship_from_org_id'||l_ship_from_org_id
524 );
525 JMF_SHIKYU_UTIL.debug_output
526 (
527 p_output_to => 'FND_LOG.STRING'
528 ,p_api_name => l_api_name
529 ,p_message => 'l_ship_to_org_id'||l_ship_to_org_id
530 );
531 JMF_SHIKYU_UTIL.debug_output
532 (
533 p_output_to => 'FND_LOG.STRING'
534 ,p_api_name => l_api_name
535 ,p_message => 'l_ship_method'||l_ship_method
536 );
537 -- end debug log
538
539 validate_ship_from_to(p_line_id => l_line_id
540 , p_header_id => p_header_id
541 , p_line_ship_from_org_id => l_ship_from_org_id
542 , p_line_ship_to_org_id => l_ship_to_org_id
543 , x_ship_flag => l_ship_flag
544 );
545
546
547 validate_receive_date(p_line_ship_from_org_id => l_ship_from_org_id
548 , p_line_ship_to_org_id => l_sold_to_org_id -- because the sold_to_org_id is the customer id
549 , p_actual_shipment_date => l_ship_date
550 , p_ship_method => l_ship_method
551 , x_date_flag => l_date_flag
552 );
553
554
555 -- begin log
556 JMF_SHIKYU_UTIL.debug_output
557 (
558 p_output_to => 'FND_LOG.STRING'
559 ,p_api_name => l_api_name
560 ,p_message => 'l_ship_flag is '|| l_ship_flag||' l_date_flag is '||l_date_flag
561 );
562 --end log
563
564 IF l_ship_flag=1 AND l_date_flag=1
565 THEN
566 --add line id to l_lines_id
567 l_lines_id.EXTEND;
568 l_lines_id(l_lines_id.COUNT) := l_line_id;
569 ELSE /*l_ship_flag=0 OR l_date_flag=0*/
570 JMF_SHIKYU_UTIL.debug_output
571 (
572 p_output_to => 'FND_LOG.STRING'
573 ,p_api_name => l_api_name
574 ,p_message => 'complete'
575 );
576 END IF; /*end if l_ship_flag=1 AND l_date_flag=1*/
577
578 END LOOP; /* end loop replenish_so_line_c*/
579 CLOSE replenish_so_line_c;
580
581 -- log the line count
582 JMF_SHIKYU_UTIL.debug_output
583 (
584 p_output_to => 'FND_LOG.STRING'
585 ,p_api_name => l_api_name
586 ,p_message => 'lines count '|| l_lines_id.COUNT
587 );
588
589 IF l_lines_id.COUNT > 0
590 THEN
591 compare_lines_quantity(p_so_header_id => p_header_id
592 , p_inventory_org_id => p_inventory_org_id
593 , p_lines_id => l_lines_id
594 , p_po_header_id => l_po_header_id
595 , p_po_line_id => l_po_line_id
596 , p_po_shipment_id => l_po_shipment_id
597 , p_ship_from_org_id => l_ship_from_org_id
598 , x_receive_quantity => l_auto_receive_quantity
599 , x_uom_code => l_primary_uom_code
600 );
601
602 -- log the quantity should be auto receive
603 -- begin log
604 JMF_SHIKYU_UTIL.debug_output
605 (
606 p_output_to => 'FND_LOG.STRING'
607 ,p_api_name => l_api_name
608 ,p_message => 'p_header_id'||p_header_id
609 );
610 JMF_SHIKYU_UTIL.debug_output
611 (
612 p_output_to => 'FND_LOG.STRING'
613 ,p_api_name => l_api_name
614 ,p_message => 'p_inventory_org_id'||p_inventory_org_id
615 );
616 JMF_SHIKYU_UTIL.debug_output
617 (
618 p_output_to => 'FND_LOG.STRING'
619 ,p_api_name => l_api_name
620 ,p_message => 'l_po_header_id'||l_po_header_id
621 );
622 JMF_SHIKYU_UTIL.debug_output
623 (
624 p_output_to => 'FND_LOG.STRING'
625 ,p_api_name => l_api_name
626 ,p_message => 'l_po_line_id'||l_po_line_id
627 );
628 JMF_SHIKYU_UTIL.debug_output
629 (
630 p_output_to => 'FND_LOG.STRING'
631 ,p_api_name => l_api_name
632 ,p_message => 'the quantity should be auto receive is '|| l_auto_receive_quantity
633 );
634 JMF_SHIKYU_UTIL.debug_output
635 (
636 p_output_to => 'FND_LOG.STRING'
637 ,p_api_name => l_api_name
638 ,p_message => 'the primary uom code is : '|| l_primary_uom_code
639 );
640 -- end log
641
642 IF l_auto_receive_quantity > 0
643 THEN
644 process_rcv_interface(p_inventory_org_id => p_inventory_org_id
645 , p_lines_id => l_lines_id
646 , p_po_header_id => l_po_header_id
647 , p_po_line_id => l_po_line_id
648 , p_po_shipment_id => l_po_shipment_id
649 , p_ship_from_org_id => l_ship_from_org_id
650 , p_ship_to_org_id => l_ship_to_org_id
651 , p_receive_quantity => l_auto_receive_quantity
652 , p_primary_uom_code => l_primary_uom_code
653 );
654
655 ELSE -- /*l_auto_receive_quantity <= 0*/
656 JMF_SHIKYU_UTIL.debug_output
657 (
658 p_output_to => 'FND_LOG.STRING'
659 ,p_api_name => l_api_name
660 ,p_message => 'the quantity should be auto receive is <= 0'
661 );
662 END IF; /*l_auto_receive_quantity > 0 */
663
664
665 END IF; /*end if l_lines_id.count > 0 */
666
667 JMF_SHIKYU_UTIL.debug_output
668 (
669 p_output_to => 'FND_LOG.STRING'
670 ,p_api_name => l_api_name
671 ,p_message => 'END procedure. '
672 );
673
674 EXCEPTION
675 WHEN OTHERS THEN
676 JMF_SHIKYU_UTIL.debug_output
677 (
678 p_output_to => 'FND_LOG.STRING'
679 ,p_api_name => l_api_name
680 ,p_message => 'Unknown error '||SQLCODE||SQLERRM
681 );
682 RAISE;
683
684
685 END auto_receive;
686
687 --==========================================================================
691 -- replenishment SO line and SO header, if it is same , retrun 1
688 -- API NAME: validate_ship_from_to
689 --
690 -- DESCRIPTION: the procedure is validate the ship from and ship to in the
692 -- ElSE return 0;
693 -- the warehouse at line level is same as warehouse at header level
694 -- the ship-to org is same as MP organiztion
695 --
696 -- PARAMETERS: In: p_header_id replenishment SO 's header_id
697 -- p_line_id replenishment SO 's line_id
698 -- p_line_ship_from_org_id
699 -- p_line_ship_to_org_id
700
701 -- Out: x_ship_flag validate flag of ship from to
702 --
703 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
704 --
705 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
706 --===========================================================================
707 PROCEDURE validate_ship_from_to
708 ( p_line_id IN NUMBER
709 , p_header_id IN NUMBER
710 , p_line_ship_from_org_id IN NUMBER
711 , p_line_ship_to_org_id IN NUMBER
712 , x_ship_flag OUT NOCOPY NUMBER
713 )
714 IS
715 l_api_name VARCHAR2(30) := 'validate_ship_from_to';
716
717 l_ship_from_org_id_h oe_order_headers_all.ship_from_org_id%TYPE;
718 l_ship_to_org_id_h oe_order_headers_all.ship_to_org_id%TYPE;
719
720 l_ship_flag NUMBER;
721
722 BEGIN
723 JMF_SHIKYU_UTIL.debug_output
724 (
725 p_output_to => 'FND_LOG.STRING'
726 ,p_api_name => l_api_name
727 ,p_message => 'procedure begin'
728 );
729 -- init l_ship_flag
730 l_ship_flag := 0;
731 BEGIN
732 SELECT h.ship_from_org_id
733 , h.ship_to_org_id
734 INTO l_ship_from_org_id_h
735 , l_ship_to_org_id_h
736 FROM oe_order_headers_all h
737 WHERE h.header_id = p_header_id;
738 EXCEPTION
739 WHEN no_data_found THEN
740 JMF_SHIKYU_UTIL.debug_output
741 (
742 p_output_to => 'FND_LOG.STRING'
743 ,p_api_name => l_api_name
744 ,p_message => 'can find ship from, or ship to by SO_header_id'
745 );
746 RAISE;
747 END;
748
749 IF p_line_ship_from_org_id = l_ship_from_org_id_h AND p_line_ship_to_org_id = l_ship_to_org_id_h
750 THEN
751 l_ship_flag := 1;
752 END IF;
753
754 x_ship_flag := l_ship_flag;
755
756 JMF_SHIKYU_UTIL.debug_output
757 (
758 p_output_to => 'FND_LOG.STRING'
759 ,p_api_name => l_api_name
760 ,p_message => 'END procedure. '
761 );
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 JMF_SHIKYU_UTIL.debug_output
766 (
767 p_output_to => 'FND_LOG.STRING'
768 ,p_api_name => l_api_name
769 ,p_message => 'Unknown error, and ship flag is 0 '||SQLCODE||SQLERRM
770 );
771 x_ship_flag := 0;
772
773 END validate_ship_from_to;
774
775 --==========================================================================
776 -- API NAME: validate_receive_date
777 --
778 -- DESCRIPTION: the procedure is validate the receive date of SO line receive
779 -- date and current date, if receive date < = current date then return
780 -- 1 else , return 0
781 --
782 -- PARAMETERS: In:
783 -- p_line_id in number
784 -- p_line_ship_from_org_id IN NUMBER
785 -- p_line_ship_to_org_id IN Date
786 -- p_actual_shipment_date IN Varchar2
787 -- p_ship_method
788
789 -- Out: x_date_flag if receive date <= current date return 1 ,
790 -- else return 0
791 --
792 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
793 --
794 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
795 --===========================================================================
796 PROCEDURE validate_receive_date
797 ( p_line_ship_from_org_id IN NUMBER
798 , p_line_ship_to_org_id IN NUMBER
799 , p_actual_shipment_date IN DATE
800 , p_ship_method IN VARCHAR2
801 , x_date_flag OUT NOCOPY NUMBER
802 )
803 IS
804
805 l_api_name VARCHAR2(30) := 'validate_receive_date';
806 l_in_transit MTL_INTERORG_SHIP_METHODS.INTRANSIT_TIME%TYPE;
807 l_intransit_type MTL_SHIPPING_NETWORK_VIEW.INTRANSIT_TYPE%TYPE;
808 l_receive_date DATE;
809
810 l_date_flag NUMBER;
811 l_tp_org_id NUMBER;
812 l_customer_information HR_ORGANIZATION_INFORMATION.Org_Information1%TYPE;
813
814 BEGIN
815 JMF_SHIKYU_UTIL.debug_output
816 (
817 p_output_to => 'FND_LOG.STRING'
818 ,p_api_name => l_api_name
819 ,p_message => 'procedure begin'
820 );
821 -- init l_date_flag
822 l_date_flag := 0;
823
824 -- get org id of TP org.
825 l_customer_information := p_line_ship_to_org_id;
826 BEGIN
827 SELECT hoi.Organization_Id
828 INTO l_tp_org_id
829 FROM HR_ORGANIZATION_INFORMATION hoi
830 WHERE hoi.org_information1 = l_customer_information;
831
832 EXCEPTION
833 WHEN no_data_found THEN
834 JMF_SHIKYU_UTIL.debug_output
835 (
839 );
836 p_output_to => 'FND_LOG.STRING'
837 ,p_api_name => l_api_name
838 ,p_message => 'no data found , when find tp org id by ship_to_org_id'
840 RAISE;
841 WHEN too_many_rows THEN
842 JMF_SHIKYU_UTIL.debug_output
843 (
844 p_output_to => 'FND_LOG.STRING'
845 ,p_api_name => l_api_name
846 ,p_message => 'find too many ship from or ship to by p_header_id'
847 );
848 RAISE;
849 END;
850
851 -- get in transit type from shiping network
852 BEGIN
853
854 SELECT ship_net.INTRANSIT_TYPE -- 1 is direct and 2 is instrant
855 INTO l_intransit_type
856 FROM MTL_SHIPPING_NETWORK_VIEW ship_net
857 WHERE ship_net.FROM_ORGANIZATION_ID = p_line_ship_from_org_id
858 AND ship_net.TO_ORGANIZATION_ID = l_tp_org_id;
859 EXCEPTION
860 WHEN no_data_found THEN
861 JMF_SHIKYU_UTIL.debug_output
862 (
863 p_output_to => 'FND_LOG.STRING'
864 ,p_api_name => l_api_name
865 ,p_message => 'no data found in-transit type'
866 );
867 RAISE;
868 WHEN too_many_rows THEN
869 JMF_SHIKYU_UTIL.debug_output
870 (
871 p_output_to => 'FND_LOG.STRING'
872 ,p_api_name => l_api_name
873 ,p_message => 'too many rows when find in-transit type'
874 );
875 RAISE;
876 END;
877
878 -- get in_transit data
879 IF l_intransit_type =1
880 THEN
881 l_in_transit := 0;
882 ELSIF l_intransit_type = 2
883 THEN
884 get_in_transit(p_ship_from_org_id => p_line_ship_from_org_id
885 , p_ship_to_org_id => l_tp_org_id
886 , p_ship_method => p_ship_method
887 , x_in_transit => l_in_transit);
888
889 ELSE -- the intransit_type is not 1 and 2 (direct and intransit)
890 JMF_SHIKYU_UTIL.debug_output
891 (
892 p_output_to => 'FND_LOG.STRING'
893 ,p_api_name => l_api_name
894 ,p_message => 'Error: intransit type is not direct nor intransit'
895 );
896 END IF; /*l_intransit_type = 1 */
897
898 -- begin debug log
899 JMF_SHIKYU_UTIL.debug_output
900 (
901 p_output_to => 'FND_LOG.STRING'
902 ,p_api_name => l_api_name
903 ,p_message => 'l_in_transit'||l_in_transit
904 );
905 -- end debug log
906
907 l_receive_date := p_actual_shipment_date + l_in_transit;
908
909 IF l_receive_date <= SYSDATE
910 THEN
911 l_date_flag := 1;
912 END IF; /*l_receive_date <= SYSDATE */
913
914 x_date_flag := l_date_flag;
915
916 JMF_SHIKYU_UTIL.debug_output
917 (
918 p_output_to => 'FND_LOG.STRING'
919 ,p_api_name => l_api_name
920 ,p_message => 'END procedure. '
921 );
922 EXCEPTION
923 WHEN OTHERS THEN
924 JMF_SHIKYU_UTIL.debug_output
925 (
926 p_output_to => 'FND_LOG.STRING'
927 ,p_api_name => l_api_name
928 ,p_message => 'Unknown error and date flag is 0 '||SQLCODE||SQLERRM
929 );
930 x_date_flag := 0;
931
932 END validate_receive_date;
933
934 --==========================================================================
935 -- API NAME: validate_rcv_error
936 --
937 -- DESCRIPTION: this procedure avoid process the error line again. if rcv flag
938 -- return 1 this line is no error , else if return 0 is error.
939 --
940 -- PARAMETERS: In:
941 -- p_po_line_id replenishment PO 's line_id
942
943 -- Out: x_rcv_flag if this po line is rcv error , return 0
944 --
945 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
946 --
947 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
948 --===========================================================================
949
950 --==========================================================================
951 -- API NAME: compare_lines_quantity
952 --
953 -- DESCRIPTION: the procedure is compare the quantity of SO line and PO shipment
954 -- when one SO header has more than one SO lines, return the different of this
955 -- two quantity
956 --
957 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
958 -- p_lines_id IN line_id_tbl
959 -- p_po_header_id IN NUMBER
960 -- p_po_line_id IN NUMBER
961 -- p_po_shipment_id IN NUMBER
962 --
963 -- Out: x_receive_quantity the different of SO quantity and PO quantity
964 -- x_uom_code OUT NOCOPY VARCHAR2
965 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
966 --
967 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
968 --===========================================================================
969 PROCEDURE compare_lines_quantity
970 ( p_so_header_id IN NUMBER
971 , p_inventory_org_id IN NUMBER
972 , p_lines_id IN line_id_tbl
973 , p_po_header_id IN NUMBER
974 , p_po_line_id IN NUMBER
975 , p_po_shipment_id IN NUMBER
976 , p_ship_from_org_id IN NUMBER
977 , x_receive_quantity OUT NOCOPY NUMBER
978 , x_uom_code OUT NOCOPY VARCHAR2
979 )
980 IS
981
985 l_returned_quantity NUMBER;
982 l_api_name VARCHAR2(40) := 'compare_lines_quantity';
983 l_shipped_quantity oe_order_lines_all.shipped_quantity%TYPE;
984 l_line_quantity oe_order_lines_all.shipped_quantity%TYPE;
986 l_received_quantity NUMBER;
987
988 l_primary_uom_code mtl_units_of_measure_tl.uom_code%TYPE;
989
990 l_prm_uom_quantity_so NUMBER;
991 l_prm_uom_quantity_po NUMBER;
992
993 l_po_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
994 l_po_uom_code mtl_units_of_measure_tl.uom_code%TYPE;
995
996 l_item_id oe_order_lines_all.inventory_item_id%TYPE;
997 l_uom_code oe_order_lines_all.shipping_quantity_uom%TYPE;
998
999 l_backorder_shipped_quantity NUMBER;
1000
1001 l_index NUMBER;
1002
1003 BEGIN
1004
1005 JMF_SHIKYU_UTIL.debug_output
1006 (
1007 p_output_to => 'FND_LOG.STRING'
1008 ,p_api_name => l_api_name
1009 ,p_message => 'procedure begin'
1010 );
1011
1012 -- init x_receive_quantity
1013 x_receive_quantity := 0;
1014 l_line_quantity := 0;
1015 l_shipped_quantity :=0;
1016 l_received_quantity := 0;
1017 l_returned_quantity := 0;
1018 l_backorder_shipped_quantity := 0;
1019
1020 l_index := p_lines_id.FIRST;
1021 WHILE l_index IS NOT NULL
1022 LOOP
1023 -- init variable
1024 l_line_quantity := 0;
1025 l_prm_uom_quantity_so := 0;
1026 l_returned_quantity := 0;
1027 BEGIN
1028 SELECT NVL(l.shipped_quantity,0)
1029 , l.inventory_item_id
1030 --, l.shipping_quantity_uom
1031 , l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
1032 INTO l_line_quantity
1033 , l_item_id
1034 , l_uom_code
1035 FROM oe_order_lines_all l
1036 WHERE l.line_id = p_lines_id(l_index);
1037
1038 EXCEPTION
1039 WHEN no_data_found THEN
1040 JMF_SHIKYU_UTIL.debug_output
1041 (
1042 p_output_to => 'FND_LOG.STRING'
1043 ,p_api_name => l_api_name
1044 ,p_message => 'no data found when find shipped_quantity and item_id'
1045 );
1046 RAISE;
1047 WHEN too_many_rows THEN
1048 JMF_SHIKYU_UTIL.debug_output
1049 (
1050 p_output_to => 'FND_LOG.STRING'
1051 ,p_api_name => l_api_name
1052 ,p_message => 'too many rows when find shipped_quantity and item_id'
1053 );
1054 RAISE;
1055 END;
1056
1057 --convert UOM
1058 --get primary uom code
1059 l_primary_uom_code := Jmf_Shikyu_Rpt_Util.get_item_primary_uom_code
1060 ( p_org_id => p_inventory_org_id
1061 , p_item_id => l_item_id
1062 , p_current_uom_code => l_uom_code
1063 );
1064
1065 x_uom_code := l_primary_uom_code;
1066
1067 l_prm_uom_quantity_so := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1068 ( p_org_id => p_inventory_org_id
1069 , p_item_id => l_item_id
1070 , p_current_uom_code => l_uom_code
1071 , p_current_qty => l_line_quantity
1072 );
1073
1074 JMF_SHIKYU_UTIL.debug_output
1075 (
1076 p_output_to => 'FND_LOG.STRING'
1077 ,p_api_name => l_api_name
1078 ,p_message => 'l_prm_uom_quantity_so :' || l_prm_uom_quantity_so
1079 );
1080
1081 --get back order line shipped quantity
1082 get_backorder_shipped_quantity(p_so_header_id => p_so_header_id
1083 ,p_so_line_id => p_lines_id(l_index)
1084 ,p_inventory_org_id => p_inventory_org_id
1085 ,x_backorder_shipped_quantity => l_backorder_shipped_quantity
1086 );
1087
1088 JMF_SHIKYU_UTIL.debug_output
1089 (
1090 p_output_to => 'FND_LOG.STRING'
1091 ,p_api_name => l_api_name
1092 ,p_message => 'l_backorder_shipped_quantity :' || l_backorder_shipped_quantity
1093 );
1094
1095 -- this should be open if this porcedure is avilable.
1096
1097 -- sub the return quantity (the returned quantity is in primary UOM)
1098
1099 l_returned_quantity := jmf_shikyu_util.Get_Replenish_So_Returned_Qty(p_replenishment_so_line_id => p_lines_id(l_index));
1100
1101 JMF_SHIKYU_UTIL.debug_output
1102 (
1103 p_output_to => 'FND_LOG.STRING'
1104 ,p_api_name => l_api_name
1105 ,p_message => 'l_returned_quantity :' || l_returned_quantity
1106 );
1107
1108 --l_returned_quantity := 0; -- this statements is replace the procedure above.
1109 l_line_quantity := l_prm_uom_quantity_so + l_backorder_shipped_quantity - l_returned_quantity;
1110 l_shipped_quantity := l_shipped_quantity + l_line_quantity;
1111
1112 l_index := p_lines_id.NEXT(l_index);
1113 END LOOP; -- l_index is not null
1114
1115 BEGIN
1116 SELECT locate.quantity_received
1117 , locate.unit_meas_lookup_code
1118 INTO l_received_quantity
1119 , l_po_uom
1120 FROM po_line_locations_all locate
1121 WHERE locate.line_location_id = p_po_shipment_id;
1122
1123 EXCEPTION
1124 WHEN no_data_found THEN
1125 JMF_SHIKYU_UTIL.debug_output
1126 (
1127 p_output_to => 'FND_LOG.STRING'
1128 ,p_api_name => l_api_name
1129 ,p_message => 'no data found , when find quantity_received by line_location_id'
1130 );
1131 RAISE;
1132 END;
1133
1134 BEGIN
1135 SELECT DISTINCT uom.uom_code
1136 INTO l_po_uom_code
1137 FROM mtl_units_of_measure_tl uom
1138 WHERE uom.unit_of_measure = l_po_uom
1139 AND uom.LANGUAGE = userenv('LANG');
1140
1141 EXCEPTION
1142 WHEN no_data_found THEN
1143 JMF_SHIKYU_UTIL.debug_output
1144 (
1145 p_output_to => 'FND_LOG.STRING'
1146 ,p_api_name => l_api_name
1147 ,p_message => 'no data found , when find uom_code by unit_of_measure'
1148 );
1149 RAISE;
1150 END;
1151
1152 -- convery the received quantity to Primary UOM
1153 l_prm_uom_quantity_po := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1154 ( p_org_id => p_inventory_org_id
1155 , p_item_id => l_item_id
1156 , p_current_uom_code => l_po_uom_code
1157 , p_current_qty => l_received_quantity
1158 );
1159
1160 -- compare
1161 x_receive_quantity := l_shipped_quantity - l_prm_uom_quantity_po;
1162
1163 JMF_SHIKYU_UTIL.debug_output
1164 (
1165 p_output_to => 'FND_LOG.STRING'
1166 ,p_api_name => l_api_name
1167 ,p_message => 'END procedure. '
1168 );
1169 EXCEPTION
1170 WHEN OTHERS THEN
1171 JMF_SHIKYU_UTIL.debug_output
1172 (
1173 p_output_to => 'FND_LOG.STRING'
1174 ,p_api_name => l_api_name
1175 ,p_message => 'Unknown error and x_receive_quantity is 0 '||SQLCODE||SQLERRM
1176 );
1177 x_receive_quantity := 0;
1178 END compare_lines_quantity;
1179
1180 --==========================================================================
1181 -- API NAME: get_backorder_shipped_quantity
1182 --
1183 -- DESCRIPTION: the procedure can get the back orderer quantity which is shipped
1184 -- by the split from line id
1185 --
1186 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
1187 -- p_line_id IN NUMBER
1188 -- p_so_header_id IN NUMBER
1189 --
1190 -- Out: x_backorder_shipped_quantity OUT NUMBER
1191 --
1192 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1193 --
1194 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1195 -- CHANGE HISTORY: 10-May-06 Amy updated procecure .get_backorder_shipped_quantity to fix bug #5212672
1196 --===========================================================================
1197 PROCEDURE get_backorder_shipped_quantity
1198 (p_so_header_id IN NUMBER
1199 ,p_so_line_id IN NUMBER
1200 ,p_inventory_org_id IN NUMBER
1201 ,x_backorder_shipped_quantity OUT NOCOPY NUMBER
1202 )
1203 IS
1204 l_api_name VARCHAR2(100) := 'get_backorder_shipped_quantity';
1205 l_shipped_quantity NUMBER;
1206 l_shipped_quantity_amount NUMBER;
1207 l_inventory_item_id NUMBER;
1208 l_uom mtl_units_of_measure_tl.uom_code%TYPE;
1209 l_line_count NUMBER;
1210 l_prm_uom_quantity_so NUMBER;
1211
1212 CURSOR back_order_line_c (p_so_header_id IN NUMBER,p_line_id IN NUMBER)
1213 IS
1214 SELECT NVL(l.shipped_quantity,0)
1215 , l.inventory_item_id
1216 --, l.shipping_quantity_uom
1217 , l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
1218
1219 FROM oe_order_lines_all l
1220 WHERE l.split_from_line_id = p_line_id
1221 AND l.header_id = p_so_header_id;
1222 BEGIN
1223
1224 JMF_SHIKYU_UTIL.debug_output
1225 (
1226 p_output_to => 'FND_LOG.STRING'
1227 ,p_api_name => l_api_name
1228 ,p_message => 'procedure begin'
1229 );
1230
1231 -- init the return quantity
1232 x_backorder_shipped_quantity := 0;
1233 l_shipped_quantity := 0;
1234 l_shipped_quantity_amount := 0;
1235 --get the line count of replenishment so
1236 SELECT
1237 COUNT(*)
1238 INTO
1239 l_line_count
1240 FROM
1241 oe_order_lines_all
1242 WHERE header_id = p_so_header_id;
1243
1244 IF l_line_count < 2
1245 THEN
1246 x_backorder_shipped_quantity := 0;
1247 ELSE
1248 -- begin log
1249 JMF_SHIKYU_UTIL.debug_output
1250 (
1251 p_output_to => 'FND_LOG.STRING'
1252 ,p_api_name => l_api_name
1253 ,p_message => 'back order line exist in '||p_so_header_id
1254 );
1255 -- end log
1256 OPEN back_order_line_c(p_so_header_id,p_so_line_id);
1257 LOOP
1258 FETCH back_order_line_c INTO l_shipped_quantity, l_inventory_item_id, l_uom;
1259 EXIT WHEN back_order_line_c%NOTFOUND;
1260
1261 --get the shipped quantity in primary quantity
1262 l_prm_uom_quantity_so := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1263 ( p_org_id => p_inventory_org_id
1264 , p_item_id => l_inventory_item_id
1265 , p_current_uom_code => l_uom
1266 , p_current_qty => l_shipped_quantity
1267 );
1268 l_shipped_quantity_amount := l_shipped_quantity_amount + l_prm_uom_quantity_so;
1269
1270
1271 END LOOP;
1272
1273 x_backorder_shipped_quantity := l_shipped_quantity_amount;
1274 END IF;
1275
1276 -- begin log
1277 JMF_SHIKYU_UTIL.debug_output
1278 (
1279 p_output_to => 'FND_LOG.STRING'
1280 ,p_api_name => l_api_name
1281 ,p_message => 'back order line shipped quantity is '||l_shipped_quantity_amount
1282 );
1283 -- end log
1284
1285 JMF_SHIKYU_UTIL.debug_output
1286 (
1287 p_output_to => 'FND_LOG.STRING'
1288 ,p_api_name => l_api_name
1289 ,p_message => 'END procedure. '
1290 );
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 JMF_SHIKYU_UTIL.debug_output
1295 (
1296 p_output_to => 'FND_LOG.STRING'
1297 ,p_api_name => l_api_name
1298 ,p_message => 'Unknown error and x_receive_quantity is 0 '||SQLCODE||SQLERRM
1299 );
1300 x_backorder_shipped_quantity :=0;
1301
1302 END get_backorder_shipped_quantity;
1303
1304 --==========================================================================
1305 -- API NAME: get_in_transit
1306 --
1307 -- DESCRIPTION: the procedure is get the in-transit time by shipping network
1308 --
1309 -- PARAMETERS: In: p_ship_from_org_id the ship from org in shipping network
1310 -- p_ship_to_org_id the ship to org in shipping network
1311 -- p_ship_method
1312 -- Out: x_in_transit in-transit value
1313 --
1314 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1315 --
1316 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1317 --===========================================================================
1318 PROCEDURE get_in_transit
1319 ( p_ship_from_org_id IN NUMBER
1320 , p_ship_to_org_id IN NUMBER
1321 , p_ship_method IN VARCHAR2
1322 , x_in_transit OUT NOCOPY NUMBER
1323 )
1324 IS
1325 l_api_name VARCHAR2(30):= 'get_in_transit';
1326 l_intransit MTL_INTERORG_SHIP_METHODS.INTRANSIT_TIME%TYPE;
1327 l_default_flag NUMBER;
1328 l_ship_method MTL_INTERORG_SHIP_METHODS.ship_method%TYPE;
1329
1330 CURSOR ship_method_c IS
1331 SELECT
1332 ship_methods.Intransit_Time
1333 , ship_methods.default_flag
1334 , ship_methods.ship_method
1335 FROM
1336 MTL_INTERORG_SHIP_METHODS ship_methods
1337 WHERE
1338 ship_methods.from_organization_id = p_ship_from_org_id
1339 AND ship_methods.To_Organization_Id = p_ship_to_org_id
1340 AND ship_methods.default_flag = 1;
1341
1342 BEGIN
1343 JMF_SHIKYU_UTIL.debug_output
1344 (
1345 p_output_to => 'FND_LOG.STRING'
1346 ,p_api_name => l_api_name
1347 ,p_message => 'procedure begin'
1348 );
1349
1350 l_intransit := 0;
1351 OPEN ship_method_c;
1352 LOOP
1353 FETCH ship_method_c INTO l_intransit, l_default_flag, l_ship_method;
1354 EXIT WHEN ship_method_c%NOTFOUND;
1355 IF l_default_flag = 1
1356 THEN
1357 x_in_transit := l_intransit;
1358 EXIT;
1359 END IF;
1360
1361 END LOOP;
1362 CLOSE ship_method_c;
1363
1364 JMF_SHIKYU_UTIL.debug_output
1365 (
1366 p_output_to => 'FND_LOG.STRING'
1367 ,p_api_name => l_api_name
1368 ,p_message => 'END procedure. '
1369 );
1370
1371 END get_in_transit;
1372
1373 --==========================================================================
1374 -- API NAME: get_customer_id
1375 --
1376 -- DESCRIPTION: To get customer id and customer site id by p_org_id
1377 -- in org define module
1378 --
1379 -- PARAMETERS: In: p_org_inventory_id IN NUMBER
1380
1381 -- Out: x_customer_id OUT NOCOPY NUMBER
1382 -- x_customer_site_id OUT NOCOPY NUMBER
1383 --
1384 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1385 --
1386 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1387 --===========================================================================
1388 PROCEDURE get_customer_id
1389 ( p_org_inventory_id IN NUMBER
1390 , x_customer_id OUT NOCOPY NUMBER
1391 , x_customer_site_id OUT NOCOPY NUMBER
1392 )
1393 IS
1394 l_api_name VARCHAR2(30) := 'get_customer_id';
1395 l_customer_id NUMBER;
1396 l_customer_site_id NUMBER;
1397 BEGIN
1398 JMF_SHIKYU_UTIL.debug_output
1399 (
1400 p_output_to => 'FND_LOG.STRING'
1401 ,p_api_name => l_api_name
1402 ,p_message => 'procedure begin'
1403 );
1404
1405 BEGIN
1406 SELECT
1407 hoi.org_information1 Customer_id
1408 ,hoi.org_information2 Customer_site_id
1409 --,hoi.org_information3 Supplier_id
1410 --,hoi.org_information4 Supplier_site_id
1411 INTO
1412 l_customer_id
1413 , l_customer_site_id
1414 FROM
1415 HR_ORGANIZATION_INFORMATION hoi
1416 WHERE hoi.org_information_context = 'Customer/Supplier Association'
1417 AND hoi.organization_id = p_org_inventory_id;
1418 EXCEPTION
1419 WHEN no_data_found THEN
1420 JMF_SHIKYU_UTIL.debug_output
1421 (
1422 p_output_to => 'FND_LOG.STRING'
1423 ,p_api_name => l_api_name
1424 ,p_message => 'no data find customer name, customer site name'
1425 );
1426 RAISE;
1427 WHEN too_many_rows THEN
1428 JMF_SHIKYU_UTIL.debug_output
1429 (
1430 p_output_to => 'FND_LOG.STRING'
1431 ,p_api_name => l_api_name
1432 ,p_message => 'too many rows when find customer name, customer site name'
1433 );
1434 RAISE;
1435 END;
1436
1437 x_customer_id := l_customer_id;
1438 x_customer_site_id := l_customer_site_id;
1439
1440 JMF_SHIKYU_UTIL.debug_output
1441 (
1442 p_output_to => 'FND_LOG.STRING'
1443 ,p_api_name => l_api_name
1444 ,p_message => 'END procedure. '
1445 );
1446
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 JMF_SHIKYU_UTIL.debug_output
1450 (
1451 p_output_to => 'FND_LOG.STRING'
1452 ,p_api_name => l_api_name
1453 ,p_message => 'Unknown error'||SQLCODE||SQLERRM
1454 );
1455 x_customer_id := NULL;
1456 x_customer_site_id := NULL;
1457
1458 END get_customer_id;
1459
1460 --==========================================================================
1461 -- API NAME: get_supplier_id
1462 --
1463 -- DESCRIPTION: the procedure is get supplier name by p_org_id in org define module
1464 --
1465 -- PARAMETERS: In: p_sold_from_org_id
1466
1467 -- Return : supplier_id
1468 --
1469 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1470 --
1471 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1472 -- 14-Nov-06 Vincent.Chu Bug 5592230/5659317: Changed type
1473 -- from Function to Procedure
1474 --===========================================================================
1475 PROCEDURE get_supplier_id
1476 ( p_sold_from_org_id IN NUMBER
1477 , x_supplier_id OUT NOCOPY NUMBER
1478 , x_supplier_site_id OUT NOCOPY NUMBER
1479 )
1480 IS
1481 l_api_name VARCHAR2(30) := 'get_supplier_name';
1482 l_supplier_id po_vendors.vendor_id%TYPE;
1483 l_supplier_site_id po_vendor_sites_all.vendor_site_id%TYPE;
1484 l_supplier_name po_vendors.vendor_name%TYPE;
1485
1486 BEGIN
1487
1488 JMF_SHIKYU_UTIL.debug_output
1489 (
1490 p_output_to => 'FND_LOG.STRING'
1491 ,p_api_name => l_api_name
1495 BEGIN
1492 ,p_message => 'procedure begin'
1493 );
1494
1496 SELECT
1497 hoi.org_information3 Supplier_id
1498 , hoi.org_information4 Supplier_site_id
1499 INTO
1500 l_supplier_id
1501 , l_supplier_site_id
1502 FROM
1503 HR_ORGANIZATION_INFORMATION hoi
1504 WHERE hoi.org_information_context = 'Customer/Supplier Association'
1505 AND hoi.organization_id = p_sold_from_org_id;
1506 EXCEPTION
1507 WHEN no_data_found THEN
1508 JMF_SHIKYU_UTIL.debug_output
1509 (
1510 p_output_to => 'FND_LOG.STRING'
1511 ,p_api_name => l_api_name
1512 ,p_message => 'no data find supplier id'
1513 );
1514 RAISE;
1515 WHEN too_many_rows THEN
1516 JMF_SHIKYU_UTIL.debug_output
1517 (
1518 p_output_to => 'FND_LOG.STRING'
1519 ,p_api_name => l_api_name
1520 ,p_message => 'too many rows when find supplier id'
1521 );
1522 RAISE;
1523 END;
1524
1525 x_supplier_id := l_supplier_id;
1526 x_supplier_site_id := l_supplier_site_id;
1527
1528 BEGIN
1529 SELECT po_vendors.vendor_name
1530 INTO l_supplier_name
1531 FROM po_vendors
1532 WHERE po_vendors.vendor_id = l_supplier_id;
1533 EXCEPTION
1534 WHEN no_data_found THEN
1535 JMF_SHIKYU_UTIL.debug_output
1536 (
1537 p_output_to => 'FND_LOG.STRING'
1538 ,p_api_name => l_api_name
1539 ,p_message => 'no data find supplier name'
1540 );
1541 RAISE;
1542 WHEN too_many_rows THEN
1543 JMF_SHIKYU_UTIL.debug_output
1544 (
1545 p_output_to => 'FND_LOG.STRING'
1546 ,p_api_name => l_api_name
1547 ,p_message => 'too many rows when find supplier name'
1548 );
1549 RAISE;
1550
1551 END;
1552
1553 JMF_SHIKYU_UTIL.debug_output
1554 (
1555 p_output_to => 'FND_LOG.STRING'
1556 ,p_api_name => l_api_name
1557 ,p_message => 'END procedure. '
1558 );
1559
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 JMF_SHIKYU_UTIL.debug_output
1563 (
1564 p_output_to => 'FND_LOG.STRING'
1565 ,p_api_name => l_api_name
1566 ,p_message => 'Unknown error '||SQLCODE||SQLERRM
1567 );
1568
1569 x_supplier_id := NULL;
1570 x_supplier_site_id := NULL;
1571
1572 END get_supplier_id;
1573
1574 --==========================================================================
1575 -- API NAME: process_rcv_interface
1576 --
1577 -- DESCRIPTION: To insert value to rcv_header_interface and
1578 -- rcv_transcation_interface
1579 --
1580 -- PARAMETERS: In: p_inventory_org_id Manufacturing Partner Organization id
1581 -- p_line_id replenishment SO line id
1582 -- p_po_header_id replenishment PO header id
1583 -- p_po_line_id replenishment PO line id
1584 -- p_po_shipment_id replenishment PO shipment id
1585 -- p_ship_from_org_id ship from org id
1586 -- p_ship_to_org_id ship to org id
1587 -- p_receive_quantity the quantity which should auto receive
1588 -- p_primary_uom_code primary_uom_code
1589 --
1590 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1591 --
1592 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1593 -- 14-Nov-06 Vincent.Chu Bug 5592230/5659317: Changed the
1594 -- calls to get_supplier_id and
1595 -- JMF_SHIKYU_RCV_PVT.process_rcv_header
1596 -- in order to pass in the supplier
1597 -- site id for the RCV transaction.
1598 --===========================================================================
1599 PROCEDURE process_rcv_interface
1600 ( p_inventory_org_id IN NUMBER
1601 --, p_line_id IN NUMBER
1602 , p_lines_id IN line_id_tbl
1603 , p_po_header_id IN NUMBER
1604 , p_po_line_id IN NUMBER
1605 , p_po_shipment_id IN NUMBER
1606 , p_ship_from_org_id IN NUMBER
1607 , p_ship_to_org_id IN NUMBER
1608 , p_receive_quantity IN NUMBER
1609 , p_primary_uom_code IN VARCHAR2
1610 )
1611 IS
1612
1613 l_api_name VARCHAR2(50) := 'process_rcv_interface';
1614 l_routing_header_id PO_LINE_LOCATIONS_ALL.Receiving_Routing_Id%TYPE;
1615 l_dest_subinventory rcv_transactions_interface.subinventory%TYPE;
1616 l_dest_locator_id wip_parameters.default_pull_supply_locator_id%TYPE;
1617 l_default_locator_id wip_parameters.default_pull_supply_locator_id%TYPE;
1618 l_locator_type MTL_SECONDARY_INVENTORIES.locator_type%TYPE;
1619 l_project_id OE_ORDER_LINES_ALL.project_id%TYPE;
1620 l_task_id OE_ORDER_LINES_ALL.task_id%TYPE;
1621
1622 l_group_id rcv_headers_interface.group_id%TYPE;
1623 l_return_number NUMBER;
1624 --l_unit_of_measure mtl_units_of_measure_tl.unit_of_measure%TYPE;
1625
1626 l_rcv_header_id NUMBER;
1627 l_transaction_type VARCHAR2(20) := 'RECEIVE';
1628
1629 l_index NUMBER;
1630
1631 -- Bug 5592230
1632 l_supplier_id NUMBER;
1633 l_supplier_site_id NUMBER;
1634
1635 CURSOR po_distributions_c(p_location_id IN NUMBER, p_line_id IN NUMBER) IS
1639 d.quantity_delivered,
1636 SELECT d.po_distribution_id,
1637 d.distribution_num,
1638 d.quantity_ordered,
1640 l.unit_meas_lookup_code,
1641 oola.shipped_quantity,
1642 uom.unit_of_measure,
1643 oola.inventory_item_id
1644 FROM po_distributions_all d,
1645 po_line_locations_all l,
1646 MTL_UNITS_OF_MEASURE uom,
1647 jmf_shikyu_replenishments jsr,
1648 oe_order_lines_all oola
1649 WHERE d.line_location_id = p_location_id
1650 AND d.LINE_LOCATION_ID = jsr.replenishment_PO_shipment_ID
1651 and oola.line_id = jsr.replenishment_so_line_id
1652 and nvl(oola.shipped_quantity, 0) > 0
1653 AND l.line_location_id = d.line_location_id
1654 AND oola.order_quantity_uom = uom.uom_code(+)
1655 and oola.line_id = p_line_id
1656 AND not exists (select 1
1657 from rcv_transactions rt
1658 where REPLENISH_ORDER_LINE_ID = oola.line_id)
1659 AND not exists (select 1
1660 from rcv_transactions_interface rti
1661 where REPLENISH_ORDER_LINE_ID = oola.line_id
1662 and TRANSACTION_STATUS_CODE <> 'ERROR'
1663 AND PROCESSING_STATUS_CODE <> 'ERROR')
1664 ORDER BY d.distribution_num; --If multiple Replenishment PO Distributions exist then handle in their order.
1665
1666 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1667 l_distribution_num po_distributions_all.distribution_num%TYPE;
1668 l_quantity_ordered po_distributions_all.quantity_ordered%TYPE;
1669 l_quantity_delivered po_distributions_all.quantity_delivered%TYPE;
1670 --l_receive_quantity NUMBER;
1671 l_insert_quantity NUMBER;
1672 l_allocated_quantity NUMBER;
1673
1674 -- Bug#5647346
1675 l_prm_uom_quantity_ordered po_distributions_all.quantity_ordered%TYPE;
1676 l_prm_uom_quantity_delivered po_distributions_all.quantity_delivered%TYPE;
1677 l_prm_uom_allocated_quantity NUMBER;
1678 l_prm_uom_receive_quantity NUMBER;
1679 l_primary_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1680 l_po_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1681 l_so_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1682 l_item_id oe_order_lines_all.inventory_item_id%TYPE;
1683
1684 BEGIN
1685 JMF_SHIKYU_UTIL.debug_output
1686 (
1687 p_output_to => 'FND_LOG.STRING'
1688 ,p_api_name => l_api_name
1689 ,p_message => 'procedure begin'
1690 );
1691 /* JMF_SHIKYU_UTIL.debug_output
1692 (
1693 p_output_to => 'FND_LOG.STRING'
1694 ,p_api_name => l_api_name
1695 ,p_message => 'p_line_id '||p_line_id
1696 );*/
1697 JMF_SHIKYU_UTIL.debug_output
1698 (
1699 p_output_to => 'FND_LOG.STRING'
1700 ,p_api_name => l_api_name
1701 ,p_message => 'p_po_header_id '||p_po_header_id
1702 );
1703 JMF_SHIKYU_UTIL.debug_output
1704 (
1705 p_output_to => 'FND_LOG.STRING'
1706 ,p_api_name => l_api_name
1707 ,p_message => 'p_po_line_id'||p_po_line_id
1708 );
1709 JMF_SHIKYU_UTIL.debug_output
1710 (
1711 p_output_to => 'FND_LOG.STRING'
1712 ,p_api_name => l_api_name
1713 ,p_message => 'p_po_shipment_id'||p_po_shipment_id
1714 );
1715 JMF_SHIKYU_UTIL.debug_output
1716 (
1717 p_output_to => 'FND_LOG.STRING'
1718 ,p_api_name => l_api_name
1719 ,p_message => 'p_ship_from_org_id'||p_ship_from_org_id
1720 );
1721 JMF_SHIKYU_UTIL.debug_output
1722 (
1723 p_output_to => 'FND_LOG.STRING'
1724 ,p_api_name => l_api_name
1725 ,p_message => 'p_ship_to_org_id'||p_ship_to_org_id
1726 );
1727 JMF_SHIKYU_UTIL.debug_output
1728 (
1729 p_output_to => 'FND_LOG.STRING'
1730 ,p_api_name => l_api_name
1731 ,p_message => 'p_receive_quantity'||p_receive_quantity
1732 );
1733
1734 l_prm_uom_receive_quantity := p_receive_quantity;
1735 -- get UOM by UOM_code
1736
1737 BEGIN
1738 SELECT
1739 uom.unit_of_measure
1740 INTO
1741 l_primary_uom
1742 FROM
1743 mtl_units_of_measure_vl uom
1744 WHERE uom.uom_code = p_primary_uom_code;
1745 EXCEPTION
1746 WHEN no_data_found THEN
1747 JMF_SHIKYU_UTIL.debug_output
1748 (
1749 p_output_to => 'FND_LOG.STRING'
1750 ,p_api_name => l_api_name
1751 ,p_message => 'no data found when select receiving_routing_id by line_location_id'
1752 );
1753 RAISE;
1754 END;
1755
1756 JMF_SHIKYU_UTIL.debug_output
1757 (
1758 p_output_to => 'FND_LOG.STRING'
1759 ,p_api_name => l_api_name
1760 ,p_message => 'l_unit_of_measure'||l_primary_uom
1761 );
1762
1763 -- get routing_header_id and get received method
1764 BEGIN
1765 SELECT
1766 location.RECEIVING_ROUTING_ID -- 1 is standard and 2 is inspection, 3 is direct
1767 INTO
1768 l_routing_header_id
1769 FROM
1770 PO_LINE_LOCATIONS_ALL location
1771 WHERE location.line_location_id = p_po_shipment_id;
1772 EXCEPTION
1773 WHEN no_data_found THEN
1774 JMF_SHIKYU_UTIL.debug_output
1775 (
1776 p_output_to => 'FND_LOG.STRING'
1777 ,p_api_name => l_api_name
1778 ,p_message => 'no data found when select receiving_routing_id by line_location_id'
1779 );
1780 RAISE;
1781 END;
1782
1783 -- get supplier_id and supplier_site_id
1784 get_supplier_id( p_sold_from_org_id => p_ship_from_org_id
1785 , x_supplier_id => l_supplier_id
1789 -- begin debug log
1786 , x_supplier_site_id => l_supplier_site_id
1787 );
1788
1790 JMF_SHIKYU_UTIL.debug_output
1791 (
1792 p_output_to => 'FND_LOG.STRING'
1793 ,p_api_name => l_api_name
1794 ,p_message => 'begin process_rcv_header'
1795 );
1796 JMF_SHIKYU_UTIL.debug_output
1797 (
1798 p_output_to => 'FND_LOG.STRING'
1799 ,p_api_name => l_api_name
1800 ,p_message => 'p_vendor_id '||l_supplier_id
1801 );
1802 JMF_SHIKYU_UTIL.debug_output
1803 (
1804 p_output_to => 'FND_LOG.STRING'
1805 ,p_api_name => l_api_name
1806 ,p_message => 'p_vendor_site_id '||l_supplier_site_id
1807 );
1808 JMF_SHIKYU_UTIL.debug_output
1809 (
1810 p_output_to => 'FND_LOG.STRING'
1811 ,p_api_name => l_api_name
1812 ,p_message => 'p_ship_to_org_id'||p_inventory_org_id
1813 );
1814 -- end debug log
1815
1816 --process rcv header , insert header data into rcv_headers_interface
1817 JMF_SHIKYU_RCV_PVT.process_rcv_header( p_vendor_id => l_supplier_id --l_customer_name
1818 , p_vendor_site_id => l_supplier_site_id
1819 , p_ship_to_org_id => p_inventory_org_id
1820 , x_rcv_header_id => l_rcv_header_id
1821 , x_group_id => l_group_id
1822 );
1823
1824 -- begin debug log
1825 JMF_SHIKYU_UTIL.debug_output
1826 (
1827 p_output_to => 'FND_LOG.STRING'
1828 ,p_api_name => l_api_name
1829 ,p_message => 'l_rcv_header_id '||l_rcv_header_id
1830 );
1831 JMF_SHIKYU_UTIL.debug_output
1832 (
1833 p_output_to => 'FND_LOG.STRING'
1834 ,p_api_name => l_api_name
1835 ,p_message => 'l_group_id'||l_group_id
1836 );
1837 JMF_SHIKYU_UTIL.debug_output
1838 (
1839 p_output_to => 'FND_LOG.STRING'
1840 ,p_api_name => l_api_name
1841 ,p_message => 'end process_rcv_header'
1842 );
1843 -- end debug log
1844
1845 -- begin debug log
1846 JMF_SHIKYU_UTIL.debug_output
1847 (
1848 p_output_to => 'FND_LOG.STRING'
1849 ,p_api_name => l_api_name
1850 ,p_message => 'l_routing_header_id'||l_routing_header_id
1851 );
1852 -- end debug log\
1853
1854 l_index := p_lines_id.FIRST;
1855 WHILE l_index IS NOT NULL
1856 LOOP
1857
1858 -- begin debug log
1859 JMF_SHIKYU_UTIL.debug_output
1860 (
1861 p_output_to => 'FND_LOG.STRING'
1862 ,p_api_name => l_api_name
1863 ,p_message => 'Begin cursor po_distributions_c loop'
1864 );
1865 JMF_SHIKYU_UTIL.debug_output
1866 (
1867 p_output_to => 'FND_LOG.STRING'
1868 ,p_api_name => l_api_name
1869 ,p_message => 'p_po_shipment_id'||p_po_shipment_id
1870 );
1871 JMF_SHIKYU_UTIL.debug_output
1872 (
1873 p_output_to => 'FND_LOG.STRING'
1874 ,p_api_name => l_api_name
1875 ,p_message => 'p_lines_id(l_index)'||p_lines_id(l_index)
1876 );
1877 -- end debug log\
1878
1879 OPEN po_distributions_c(p_po_shipment_id, p_lines_id(l_index));
1880
1881 LOOP
1882 l_insert_quantity := 0;
1883 FETCH
1884 po_distributions_c
1885 INTO
1886 l_po_distribution_id
1887 , l_distribution_num
1888 , l_quantity_ordered
1889 , l_quantity_delivered
1890 , l_po_uom
1891 , l_allocated_quantity
1892 , l_so_uom
1893 , l_item_id;
1894 EXIT WHEN po_distributions_c%NOTFOUND;
1895
1896 -- Bug#5647346: convert l_quantity_ordered, l_quantity_delivered, l_allocated_quantity to primary UOM
1897 l_prm_uom_quantity_ordered := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1898 ( p_org_id => p_inventory_org_id
1899 , p_item_id => l_item_id
1900 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_po_uom)
1901 , p_current_qty => l_quantity_ordered
1902 );
1903 l_prm_uom_quantity_delivered := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1904 ( p_org_id => p_inventory_org_id
1905 , p_item_id => l_item_id
1906 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_po_uom)
1907 , p_current_qty => l_quantity_delivered
1908 );
1909 l_prm_uom_allocated_quantity := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1910 ( p_org_id => p_inventory_org_id
1911 , p_item_id => l_item_id
1912 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_so_uom)
1913 , p_current_qty => l_allocated_quantity
1914 );
1915
1916 -- begin insert data to interface,
1917 IF l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered > 0
1918 THEN
1919 -- begin insert data to interface, pay attion to sub inventory
1920 --quantity
1921 IF l_prm_uom_receive_quantity > l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered
1922 THEN
1926 l_prm_uom_receive_quantity := l_prm_uom_receive_quantity - (l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered);
1923 --quantity = l_quantity_ordered - l_quantity_delivered
1924 -- p_receive_quantity := p_receive_quantity - (l_quantity_ordered - l_quantity_delivered)
1925 l_insert_quantity := l_allocated_quantity;
1927
1928 ELSE /*p_receive_quantity <= l_quantity_ordered - l_quantity_delivered*/
1929 l_insert_quantity := l_allocated_quantity;
1930
1931 END IF ; /*end if p_receive_quantity > l_quantity_ordered - l_quantity_delivered*/
1932
1933 /*
1934 If there is no subinventory associated with PO Distributions then get
1935 Default Supply Subinventory from WIP Parameters.
1936 */
1937 IF l_dest_subinventory IS NULL
1938 THEN
1939 -- find the default subinventory
1940 -- if default is not null
1941 -- then ok
1942 -- else send a message.
1943 JMF_SHIKYU_UTIL.debug_output
1944 (
1945 p_output_to => 'FND_LOG.STRING'
1946 ,p_api_name => l_api_name
1947 ,p_message => 'Dest_subinventory is null'
1948 );
1949
1950 BEGIN
1951 SELECT wip_para.default_pull_supply_subinv
1952 INTO l_dest_subinventory
1953 FROM wip_parameters wip_para
1954 WHERE wip_para.Organization_Id = p_inventory_org_id;
1955 EXCEPTION
1956 WHEN no_data_found THEN
1957 JMF_SHIKYU_UTIL.debug_output
1958 (
1959 p_output_to => 'FND_LOG.STRING'
1960 ,p_api_name => l_api_name
1961 ,p_message => 'no data found when found subinventory by org id'
1962 );
1963
1964 -- log a message that the default sub inventory should be setup
1965 fnd_message.set_name('JMF', 'JMF_SHK_WIP_SUBINV_MIS');
1966 JMF_SHIKYU_UTIL.debug_output
1967 (
1968 p_output_to => 'FND_LOG.STRING'
1969 ,p_api_name => l_api_name
1970 ,p_message => fnd_message.GET
1971 );
1972
1973 RAISE;
1974 WHEN too_many_rows THEN
1975 JMF_SHIKYU_UTIL.debug_output
1976 (
1977 p_output_to => 'FND_LOG.STRING'
1978 ,p_api_name => l_api_name
1979 ,p_message => 'too many rows when found sub inventory by org id'
1980 );
1981 RAISE;
1982 END;
1983
1984 END IF;
1985
1986 -- begin debug log
1987 JMF_SHIKYU_UTIL.debug_output
1988 (
1989 p_output_to => 'FND_LOG.STRING'
1990 ,p_api_name => l_api_name
1991 ,p_message => 'l_dest_subinventory'||l_dest_subinventory
1992 );
1993 -- end debug log\
1994
1995 /*
1996 Get the Locator ID.
1997 */
1998 IF l_dest_locator_id IS NULL
1999 THEN
2000 --get the DEFAULT_PULL_SUPPLY_LOCATOR_ID
2001 BEGIN
2002 SELECT wip_para.default_pull_supply_locator_id
2003 INTO l_default_locator_id
2004 FROM wip_parameters wip_para
2005 WHERE wip_para.Organization_Id = p_inventory_org_id;
2006 EXCEPTION
2007 WHEN no_data_found THEN
2008 JMF_SHIKYU_UTIL.debug_output
2009 (
2010 p_output_to => 'FND_LOG.STRING'
2011 ,p_api_name => l_api_name
2012 ,p_message => 'no data found when found locator by org id'
2013 );
2014
2015 RAISE;
2016 WHEN too_many_rows THEN
2017 JMF_SHIKYU_UTIL.debug_output
2018 (
2019 p_output_to => 'FND_LOG.STRING'
2020 ,p_api_name => l_api_name
2021 ,p_message => 'too many rows when found default locator by org id'
2022 );
2023 RAISE;
2024 END;
2025
2026 -- begin debug log
2027 JMF_SHIKYU_UTIL.debug_output
2028 (
2029 p_output_to => 'FND_LOG.STRING'
2030 ,p_api_name => l_api_name
2031 ,p_message => 'l_default_locator_id'||l_default_locator_id
2032 );
2033 -- end debug log\
2034
2035 --get the LOCATOR_TYPE column of the subinventory
2036 BEGIN
2037 SELECT msi.locator_type
2038 INTO l_locator_type
2039 FROM MTL_SECONDARY_INVENTORIES msi
2040 WHERE msi.Organization_Id = p_inventory_org_id
2041 AND msi.SECONDARY_INVENTORY_NAME = l_dest_subinventory;
2042 EXCEPTION
2043 WHEN no_data_found THEN
2044 JMF_SHIKYU_UTIL.debug_output
2045 (
2046 p_output_to => 'FND_LOG.STRING'
2047 ,p_api_name => l_api_name
2048 ,p_message => 'no data found when found locator type by org id and locator id'
2049 );
2050 RAISE;
2051 END;
2052
2053 -- begin debug log
2054 JMF_SHIKYU_UTIL.debug_output
2055 (
2056 p_output_to => 'FND_LOG.STRING'
2057 ,p_api_name => l_api_name
2058 ,p_message => 'l_locator_type'||l_locator_type
2059 );
2060 -- end debug log\
2061
2062 --get the project id and task id
2063 BEGIN
2064 SELECT oola.project_id, oola.task_id
2068 EXCEPTION
2065 INTO l_project_id, l_task_id
2066 FROM OE_ORDER_LINES_ALL oola
2067 WHERE oola.line_id = p_lines_id(l_index);
2069 WHEN no_data_found THEN
2070 JMF_SHIKYU_UTIL.debug_output
2071 (
2072 p_output_to => 'FND_LOG.STRING'
2073 ,p_api_name => l_api_name
2074 ,p_message => 'no data found when found project id and task id by org id and locator id'
2075 );
2076 RAISE;
2077 END;
2078
2079 -- begin debug log
2080 JMF_SHIKYU_UTIL.debug_output
2081 (
2082 p_output_to => 'FND_LOG.STRING'
2083 ,p_api_name => l_api_name
2084 ,p_message => 'l_project_id'||l_project_id
2085 );
2086 JMF_SHIKYU_UTIL.debug_output
2087 (
2088 p_output_to => 'FND_LOG.STRING'
2089 ,p_api_name => l_api_name
2090 ,p_message => 'l_task_id'||l_task_id
2091 );
2092 -- end debug log\
2093
2094 --if this LOCATOR_TYPE column = 3 and if the project id of the Replenishment Sales Order Line is not null,
2095 --then call the PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator procedure.
2096 --else pass the DEFAULT_PULL_SUPPLY_LOCATOR_ID to RCV_Transaction_Interface.
2097 IF (l_locator_type = 3 and l_project_id IS NOT NULL)
2098 THEN
2099 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator(p_organization_id => p_inventory_org_id
2100 , p_locator_id => l_default_locator_id
2101 , p_project_id => l_project_id
2102 , p_task_id => l_task_id
2103 , p_project_locator_id => l_dest_locator_id
2104 );
2105 ELSE
2106 l_dest_locator_id := l_default_locator_id;
2107 END IF;
2108
2109 -- begin debug log
2110 JMF_SHIKYU_UTIL.debug_output
2111 (
2112 p_output_to => 'FND_LOG.STRING'
2113 ,p_api_name => l_api_name
2114 ,p_message => 'l_dest_locator_id'||l_dest_locator_id
2115 );
2116 -- end debug log\
2117
2118 End IF;
2119
2120 -- get all of the data
2121 ELSE /*l_quantity_ordered - l_quantity_delivered <= 0*/
2122 --begin log
2123 JMF_SHIKYU_UTIL.debug_output
2124 (
2125 p_output_to => 'FND_LOG.STRING'
2126 ,p_api_name => l_api_name
2127 ,p_message => 'the quantity auto receive finished '
2128 );
2129 --end log
2130 END IF ; /*end if l_quantity_ordered - l_quantity_delivered > 0 */
2131
2132 -- begin debug log
2133 JMF_SHIKYU_UTIL.debug_output
2134 (
2135 p_output_to => 'FND_LOG.STRING'
2136 ,p_api_name => l_api_name
2137 ,p_message => 'begin process_rcv_trx'
2138 );
2139 JMF_SHIKYU_UTIL.debug_output
2140 (
2141 p_output_to => 'FND_LOG.STRING'
2142 ,p_api_name => l_api_name
2143 ,p_message => 'l_rcv_header_id '||l_rcv_header_id
2144 );
2145 JMF_SHIKYU_UTIL.debug_output
2146 (
2147 p_output_to => 'FND_LOG.STRING'
2148 ,p_api_name => l_api_name
2149 ,p_message => 'l_insert_quantity'||l_insert_quantity
2150 );
2151 JMF_SHIKYU_UTIL.debug_output
2152 (
2153 p_output_to => 'FND_LOG.STRING'
2154 ,p_api_name => l_api_name
2155 ,p_message => 'l_unit_of_measure'||l_so_uom
2156 );
2157 JMF_SHIKYU_UTIL.debug_output
2158 (
2159 p_output_to => 'FND_LOG.STRING'
2160 ,p_api_name => l_api_name
2161 ,p_message => 'p_po_header_id'||p_po_header_id
2162 );
2163 JMF_SHIKYU_UTIL.debug_output
2164 (
2165 p_output_to => 'FND_LOG.STRING'
2166 ,p_api_name => l_api_name
2167 ,p_message => 'p_po_line_id'||p_po_line_id
2168 );
2169 JMF_SHIKYU_UTIL.debug_output
2170 (
2171 p_output_to => 'FND_LOG.STRING'
2172 ,p_api_name => l_api_name
2173 ,p_message => 'l_dest_subinventory'||l_dest_subinventory
2174 );
2175 JMF_SHIKYU_UTIL.debug_output
2176 (
2177 p_output_to => 'FND_LOG.STRING'
2178 ,p_api_name => l_api_name
2179 ,p_message => 'l_transaction_type'||l_transaction_type
2180 );
2181 JMF_SHIKYU_UTIL.debug_output
2182 (
2183 p_output_to => 'FND_LOG.STRING'
2184 ,p_api_name => l_api_name
2185 ,p_message => 'l_dest_locator_id'||l_dest_locator_id
2186 );
2187 JMF_SHIKYU_UTIL.debug_output
2188 (
2189 p_output_to => 'FND_LOG.STRING'
2190 ,p_api_name => l_api_name
2191 ,p_message => 'p_replenish_order_line_id '||p_lines_id(l_index)
2192 );
2193 -- end debug log\
2194
2195 IF l_routing_header_id = 1 OR l_routing_header_id = 2
2196 THEN
2197 -- process rcv trx, insert transactions data into rcv_transactions_interface
2198 JMF_SHIKYU_RCV_PVT.process_rcv_trx(p_rcv_header_id => l_rcv_header_id
2199 , p_group_id => l_group_id
2203 , p_po_line_id => p_po_line_id
2200 , p_quantity => l_insert_quantity
2201 , p_unit_of_measure => l_so_uom
2202 , p_po_header_id => p_po_header_id
2204 , p_subinventory => NULL
2205 , p_transaction_type => l_transaction_type
2206 , p_auto_transact_code => 'RECEIVE'
2207 , p_parent_transaction_id => NULL
2208 , p_po_line_location_id => p_po_shipment_id
2209 , P_locator_id => l_dest_locator_id
2210 , p_replenish_order_line_id => p_lines_id(l_index)
2211 );
2212 ELSIF l_routing_header_id = 3
2213 THEN
2214 -- process rcv trx, insert transactions data into rcv_transactions_interface
2215 JMF_SHIKYU_RCV_PVT.process_rcv_trx(p_rcv_header_id => l_rcv_header_id
2216 , p_group_id => l_group_id
2217 , p_quantity => l_insert_quantity
2218 , p_unit_of_measure => l_so_uom
2219 , p_po_header_id => p_po_header_id
2220 , p_po_line_id => p_po_line_id
2221 , p_subinventory => l_dest_subinventory
2222 , p_transaction_type => l_transaction_type
2223 , p_auto_transact_code => 'DELIVER'
2224 , p_parent_transaction_id => NULL
2225 , p_po_line_location_id => p_po_shipment_id
2226 , P_locator_id => l_dest_locator_id
2227 , p_replenish_order_line_id => p_lines_id(l_index)
2228 );
2229
2230 END IF;
2231
2232 END LOOP;
2233 CLOSE po_distributions_c;
2234 l_index := p_lines_id.NEXT(l_index);
2235 END LOOP;
2236 -- submit concurrent request in PL/SQL program.
2237
2238 l_return_number := fnd_request.submit_request(application => 'PO'
2239 , program => 'RVCTP'
2240 , description => 'Receiving Transaction Processor'
2241 , start_time => SYSDATE
2242 , sub_request => FALSE
2243 , argument1 => 'BATCH'
2244 , argument2 => l_group_id
2245 );
2246
2247 JMF_SHIKYU_UTIL.debug_output
2248 (
2249 p_output_to => 'FND_LOG.STRING'
2250 ,p_api_name => l_api_name
2251 ,p_message => 'The request id is : ' || l_return_number
2252 );
2253
2254 JMF_SHIKYU_UTIL.debug_output
2255 (
2256 p_output_to => 'FND_LOG.STRING'
2257 ,p_api_name => l_api_name
2258 ,p_message => 'END procedure. '
2259 );
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 JMF_SHIKYU_UTIL.debug_output
2264 (
2265 p_output_to => 'FND_LOG.STRING'
2266 ,p_api_name => l_api_name
2267 ,p_message => 'Unknown error'||SQLCODE||SQLERRM
2268 );
2269 RAISE;
2270
2271 END process_rcv_interface;
2272
2273 END JMF_SHIKYU_AUTO_RCV_PROC;