[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.26 2010/11/29 07:38:52 abhissri ship $
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 |
62 --| 25-AUG-2006 THE2 Modified cursor po_distributions_c again |
59 --| bug #5434983 |
60 --| 18-AUG-2006 THE2 Modified cursor po_distributions_c again |
61 --| to fix bug #5434983 |
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
189 END LOOP;
186 );
187
188
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 );
344 JMF_SHIKYU_UTIL.debug_output
341
342 EXCEPTION
343 WHEN OTHERS THEN
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 --Bugfix 10281833: New variable
397 l_inventory_org_id NUMBER := p_inventory_org_id;
398
399 CURSOR replenish_so_line_c IS
400 SELECT r.replenishment_so_line_id
401 FROM jmf_shikyu_replenishments r
402 WHERE r.replenishment_so_header_id = p_header_id;
403
404 BEGIN
405
406 JMF_SHIKYU_UTIL.debug_output
407 (
408 p_output_to => 'FND_LOG.STRING'
409 ,p_api_name => l_api_name
410 ,p_message => 'procedure begin'
411 );
412 JMF_SHIKYU_UTIL.debug_output
413 (
414 p_output_to => 'FND_LOG.STRING'
415 ,p_api_name => l_api_name
416 ,p_message => 'p_header_id :'||p_header_id || ' p_inventory_org_id:' || p_inventory_org_id
417 );
418
419 -- init l_lines_id
420 l_lines_id := line_id_tbl();
421 OPEN replenish_so_line_c;
422 LOOP
423 FETCH replenish_so_line_c INTO l_line_id;
424 EXIT WHEN replenish_so_line_c%NOTFOUND;
425
426 -- begin log
427 JMF_SHIKYU_UTIL.debug_output
428 (
429 p_output_to => 'FND_LOG.STRING'
430 ,p_api_name => l_api_name
431 ,p_message => 'l_line_id'||l_line_id
432 );
433 -- end log
434
435 BEGIN
436 --select line_ship_from_org_id by line_id
437 SELECT
438 r.replenishment_po_header_id
439 , r.replenishment_po_line_id
440 , r.replenishment_po_shipment_id
441 INTO
442 l_po_header_id
443 , l_po_line_id
444 , l_po_shipment_id
445 FROM jmf_shikyu_replenishments r
446 WHERE r.replenishment_so_line_id = l_line_id;
447 EXCEPTION
448 WHEN no_data_found THEN
449 JMF_SHIKYU_UTIL.debug_output
450 (
451 p_output_to => 'FND_LOG.STRING'
452 ,p_api_name => l_api_name
453 ,p_message => 'no data find when select PO info by so_line_id in jmf_shikyu_replenishments'
454 );
455 END;
456
457 -- begin debug log
458 JMF_SHIKYU_UTIL.debug_output
459 (
460 p_output_to => 'FND_LOG.STRING'
461 ,p_api_name => l_api_name
462 ,p_message => 'l_po_header_id'||l_po_header_id
463 );
464 JMF_SHIKYU_UTIL.debug_output
465 (
466 p_output_to => 'FND_LOG.STRING'
467 ,p_api_name => l_api_name
468 ,p_message => 'l_po_line_id'||l_po_line_id
469 );
470 JMF_SHIKYU_UTIL.debug_output
471 (
472 p_output_to => 'FND_LOG.STRING'
473 ,p_api_name => l_api_name
474 ,p_message => 'l_po_shipment_id'||l_po_shipment_id
475 );
476 -- end debug log
477
478 BEGIN
479 --select actual_shipment_date by line id
480 SELECT l.actual_shipment_date --actual_shipment_date
481 , l.ship_from_org_id
482 , l.ship_to_org_id
483 , l.sold_to_org_id
484 , l.shipping_method_code
485 INTO l_ship_date
486 , l_ship_from_org_id
487 , l_ship_to_org_id
488 , l_sold_to_org_id
489 , l_ship_method
490 FROM oe_order_lines_all l
491 WHERE l.line_id = l_line_id;
492
493 EXCEPTION
494 WHEN no_data_found THEN
495 JMF_SHIKYU_UTIL.debug_output
496 (
497 p_output_to => 'FND_LOG.STRING'
498 ,p_api_name => l_api_name
499 ,p_message => 'no data find when select ship org id or other info by line_id in oe_order_lines_all'
500 );
501 END;
502
503 --get ship method from SO header
504 IF l_ship_method IS NULL
505 THEN
506 SELECT
507 shipping_method_code
508 INTO
509 l_ship_method
510 FROM oe_order_headers_all
511 WHERE header_id = p_header_id;
512
513 END IF;
514
515 -- begin debug log
516 JMF_SHIKYU_UTIL.debug_output
517 (
518 p_output_to => 'FND_LOG.STRING'
519 ,p_api_name => l_api_name
520 ,p_message => 'l_ship_date'||l_ship_date
521 );
522 JMF_SHIKYU_UTIL.debug_output
523 (
524 p_output_to => 'FND_LOG.STRING'
525 ,p_api_name => l_api_name
526 ,p_message => 'l_ship_from_org_id'||l_ship_from_org_id
527 );
528 JMF_SHIKYU_UTIL.debug_output
529 (
530 p_output_to => 'FND_LOG.STRING'
531 ,p_api_name => l_api_name
532 ,p_message => 'l_ship_to_org_id'||l_ship_to_org_id
533 );
534 JMF_SHIKYU_UTIL.debug_output
535 (
536 p_output_to => 'FND_LOG.STRING'
537 ,p_api_name => l_api_name
538 ,p_message => 'l_ship_method'||l_ship_method
539 );
540 -- end debug log
541
542 validate_ship_from_to(p_line_id => l_line_id
543 , p_header_id => p_header_id
544 , p_line_ship_from_org_id => l_ship_from_org_id
545 , p_line_ship_to_org_id => l_ship_to_org_id
546 , x_ship_flag => l_ship_flag
547 );
548
549
550 validate_receive_date(p_line_ship_from_org_id => l_ship_from_org_id
551 --Bugfix 10281833: customer id is not needed.
552 --Passing the value of MP organization instead.
553 --, p_line_ship_to_org_id => l_sold_to_org_id -- because the sold_to_org_id is the customer id
554 , p_inventory_org_id => l_inventory_org_id
555 , p_actual_shipment_date => l_ship_date
556 , p_ship_method => l_ship_method
557 , x_date_flag => l_date_flag
558 );
559
560
561 -- begin log
562 JMF_SHIKYU_UTIL.debug_output
563 (
564 p_output_to => 'FND_LOG.STRING'
565 ,p_api_name => l_api_name
566 ,p_message => 'l_ship_flag is '|| l_ship_flag||' l_date_flag is '||l_date_flag
567 );
568 --end log
569
570 IF l_ship_flag=1 AND l_date_flag=1
571 THEN
572 --add line id to l_lines_id
573 l_lines_id.EXTEND;
574 l_lines_id(l_lines_id.COUNT) := l_line_id;
575 ELSE /*l_ship_flag=0 OR l_date_flag=0*/
576 JMF_SHIKYU_UTIL.debug_output
577 (
578 p_output_to => 'FND_LOG.STRING'
579 ,p_api_name => l_api_name
580 ,p_message => 'complete'
581 );
582 END IF; /*end if l_ship_flag=1 AND l_date_flag=1*/
583
584 END LOOP; /* end loop replenish_so_line_c*/
585 CLOSE replenish_so_line_c;
586
587 -- log the line count
588 JMF_SHIKYU_UTIL.debug_output
589 (
590 p_output_to => 'FND_LOG.STRING'
591 ,p_api_name => l_api_name
592 ,p_message => 'lines count '|| l_lines_id.COUNT
593 );
594
595 IF l_lines_id.COUNT > 0
596 THEN
597 compare_lines_quantity(p_so_header_id => p_header_id
598 , p_inventory_org_id => p_inventory_org_id
599 , p_lines_id => l_lines_id
600 , p_po_header_id => l_po_header_id
601 , p_po_line_id => l_po_line_id
602 , p_po_shipment_id => l_po_shipment_id
603 , p_ship_from_org_id => l_ship_from_org_id
604 , x_receive_quantity => l_auto_receive_quantity
605 , x_uom_code => l_primary_uom_code
606 );
607
608 -- log the quantity should be auto receive
609 -- begin log
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_header_id'||p_header_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 => 'p_inventory_org_id'||p_inventory_org_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_header_id'||l_po_header_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 => 'l_po_line_id'||l_po_line_id
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 quantity should be auto receive is '|| l_auto_receive_quantity
639 );
640 JMF_SHIKYU_UTIL.debug_output
641 (
642 p_output_to => 'FND_LOG.STRING'
643 ,p_api_name => l_api_name
644 ,p_message => 'the primary uom code is : '|| l_primary_uom_code
645 );
646 -- end log
647
648 IF l_auto_receive_quantity > 0
649 THEN
650 process_rcv_interface(p_inventory_org_id => p_inventory_org_id
651 , p_lines_id => l_lines_id
652 , p_po_header_id => l_po_header_id
653 , p_po_line_id => l_po_line_id
654 , p_po_shipment_id => l_po_shipment_id
655 , p_ship_from_org_id => l_ship_from_org_id
656 , p_ship_to_org_id => l_ship_to_org_id
657 , p_receive_quantity => l_auto_receive_quantity
658 , p_primary_uom_code => l_primary_uom_code
659 );
660
661 ELSE -- /*l_auto_receive_quantity <= 0*/
662 JMF_SHIKYU_UTIL.debug_output
663 (
664 p_output_to => 'FND_LOG.STRING'
665 ,p_api_name => l_api_name
666 ,p_message => 'the quantity should be auto receive is <= 0'
667 );
668 END IF; /*l_auto_receive_quantity > 0 */
669
670
671 END IF; /*end if l_lines_id.count > 0 */
672
673 JMF_SHIKYU_UTIL.debug_output
674 (
675 p_output_to => 'FND_LOG.STRING'
676 ,p_api_name => l_api_name
677 ,p_message => 'END procedure. '
678 );
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 JMF_SHIKYU_UTIL.debug_output
683 (
684 p_output_to => 'FND_LOG.STRING'
685 ,p_api_name => l_api_name
686 ,p_message => 'Unknown error '||SQLCODE||SQLERRM
687 );
688 RAISE;
689
690
691 END auto_receive;
692
693 --==========================================================================
694 -- API NAME: validate_ship_from_to
695 --
696 -- DESCRIPTION: the procedure is validate the ship from and ship to in the
697 -- replenishment SO line and SO header, if it is same , retrun 1
698 -- ElSE return 0;
699 -- the warehouse at line level is same as warehouse at header level
700 -- the ship-to org is same as MP organiztion
701 --
702 -- PARAMETERS: In: p_header_id replenishment SO 's header_id
703 -- p_line_id replenishment SO 's line_id
704 -- p_line_ship_from_org_id
705 -- p_line_ship_to_org_id
706
707 -- Out: x_ship_flag validate flag of ship from to
708 --
709 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
710 --
711 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
712 --===========================================================================
713 PROCEDURE validate_ship_from_to
714 ( p_line_id IN NUMBER
715 , p_header_id IN NUMBER
716 , p_line_ship_from_org_id IN NUMBER
717 , p_line_ship_to_org_id IN NUMBER
718 , x_ship_flag OUT NOCOPY NUMBER
719 )
720 IS
721 l_api_name VARCHAR2(30) := 'validate_ship_from_to';
722
723 l_ship_from_org_id_h oe_order_headers_all.ship_from_org_id%TYPE;
724 l_ship_to_org_id_h oe_order_headers_all.ship_to_org_id%TYPE;
725
726 l_ship_flag NUMBER;
727
728 BEGIN
729 JMF_SHIKYU_UTIL.debug_output
730 (
731 p_output_to => 'FND_LOG.STRING'
732 ,p_api_name => l_api_name
733 ,p_message => 'procedure begin'
734 );
735 -- init l_ship_flag
736 l_ship_flag := 0;
737 BEGIN
738 SELECT h.ship_from_org_id
739 , h.ship_to_org_id
740 INTO l_ship_from_org_id_h
741 , l_ship_to_org_id_h
742 FROM oe_order_headers_all h
743 WHERE h.header_id = p_header_id;
744 EXCEPTION
745 WHEN no_data_found THEN
746 JMF_SHIKYU_UTIL.debug_output
747 (
748 p_output_to => 'FND_LOG.STRING'
749 ,p_api_name => l_api_name
750 ,p_message => 'can find ship from, or ship to by SO_header_id'
751 );
752 RAISE;
753 END;
754
755 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
756 THEN
757 l_ship_flag := 1;
758 END IF;
759
760 x_ship_flag := l_ship_flag;
761
762 JMF_SHIKYU_UTIL.debug_output
763 (
764 p_output_to => 'FND_LOG.STRING'
765 ,p_api_name => l_api_name
766 ,p_message => 'END procedure. '
767 );
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 JMF_SHIKYU_UTIL.debug_output
772 (
773 p_output_to => 'FND_LOG.STRING'
774 ,p_api_name => l_api_name
775 ,p_message => 'Unknown error, and ship flag is 0 '||SQLCODE||SQLERRM
776 );
777 x_ship_flag := 0;
778
779 END validate_ship_from_to;
780
781 --==========================================================================
782 -- API NAME: validate_receive_date
783 --
784 -- DESCRIPTION: the procedure is validate the receive date of SO line receive
785 -- date and current date, if receive date < = current date then return
786 -- 1 else , return 0
787 --
788 -- PARAMETERS: In:
789 -- p_line_id in number
790 -- p_line_ship_from_org_id IN NUMBER
791 -- p_line_ship_to_org_id IN Date
792 -- p_actual_shipment_date IN Varchar2
793 -- p_ship_method
794
795 -- Out: x_date_flag if receive date <= current date return 1 ,
796 -- else return 0
797 --
798 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
799 --
800 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
801 --===========================================================================
802 PROCEDURE validate_receive_date
803 ( p_line_ship_from_org_id IN NUMBER
804 --, p_line_ship_to_org_id IN NUMBER --Bugfix 10281833: Parameter p_line_ship_to_org_id is not needed.
805 , p_inventory_org_id IN NUMBER --Bugfix 10281833: Passing the value of MP organization_id.
806 , p_actual_shipment_date IN DATE
807 , p_ship_method IN VARCHAR2
808 , x_date_flag OUT NOCOPY NUMBER
809 )
810 IS
811
812 l_api_name VARCHAR2(30) := 'validate_receive_date';
813 l_in_transit MTL_INTERORG_SHIP_METHODS.INTRANSIT_TIME%TYPE;
814 l_intransit_type MTL_SHIPPING_NETWORK_VIEW.INTRANSIT_TYPE%TYPE;
815 l_receive_date DATE;
816
817 l_date_flag NUMBER;
818 l_tp_org_id NUMBER;
819
820 --Bugfix 10281833: Variable l_customer_information is not needed
821 --l_customer_information HR_ORGANIZATION_INFORMATION.Org_Information1%TYPE;
822
823 BEGIN
824 JMF_SHIKYU_UTIL.debug_output
825 (
826 p_output_to => 'FND_LOG.STRING'
827 ,p_api_name => l_api_name
828 ,p_message => 'procedure begin'
829 );
830 -- init l_date_flag
831 l_date_flag := 0;
832
833 -- get org id of TP org.
834 /*Bugfix 10281833
835 l_customer_information := p_line_ship_to_org_id;
836 BEGIN
837 SELECT hoi.Organization_Id
838 INTO l_tp_org_id
839 FROM HR_ORGANIZATION_INFORMATION hoi
840 WHERE hoi.org_information1 = l_customer_information;
841
842 EXCEPTION
843 WHEN no_data_found THEN
844 JMF_SHIKYU_UTIL.debug_output
845 (
846 p_output_to => 'FND_LOG.STRING'
847 ,p_api_name => l_api_name
848 ,p_message => 'no data found , when find tp org id by ship_to_org_id'
849 );
850 RAISE;
851 WHEN too_many_rows THEN
852 JMF_SHIKYU_UTIL.debug_output
853 (
854 p_output_to => 'FND_LOG.STRING'
855 ,p_api_name => l_api_name
856 ,p_message => 'find too many ship from or ship to by p_header_id'
857 );
858 RAISE;
859 END;
860 */
861
862 l_tp_org_id := p_inventory_org_id;
863 JMF_SHIKYU_UTIL.debug_output
864 (
865 p_output_to => 'FND_LOG.STRING'
866 ,p_api_name => l_api_name
867 ,p_message => 'p_inventory_org_id:' || p_inventory_org_id || ' l_tp_org_id:' || l_tp_org_id
868 );
869 --End Bugfix 10281833
870
871 -- get in transit type from shiping network
872 BEGIN
873
874 SELECT ship_net.INTRANSIT_TYPE -- 1 is direct and 2 is instrant
875 INTO l_intransit_type
876 FROM MTL_SHIPPING_NETWORK_VIEW ship_net
877 WHERE ship_net.FROM_ORGANIZATION_ID = p_line_ship_from_org_id
878 AND ship_net.TO_ORGANIZATION_ID = l_tp_org_id;
879 EXCEPTION
880 WHEN no_data_found THEN
881 JMF_SHIKYU_UTIL.debug_output
882 (
883 p_output_to => 'FND_LOG.STRING'
884 ,p_api_name => l_api_name
885 ,p_message => 'no data found in-transit type'
886 );
887 RAISE;
888 WHEN too_many_rows THEN
889 JMF_SHIKYU_UTIL.debug_output
890 (
891 p_output_to => 'FND_LOG.STRING'
892 ,p_api_name => l_api_name
893 ,p_message => 'too many rows when find in-transit type'
894 );
895 RAISE;
896 END;
897
898 -- get in_transit data
899 IF l_intransit_type =1
900 THEN
901 l_in_transit := 0;
902 ELSIF l_intransit_type = 2
903 THEN
904 get_in_transit(p_ship_from_org_id => p_line_ship_from_org_id
905 , p_ship_to_org_id => l_tp_org_id
906 , p_ship_method => p_ship_method
907 , x_in_transit => l_in_transit);
908
909 ELSE -- the intransit_type is not 1 and 2 (direct and intransit)
910 JMF_SHIKYU_UTIL.debug_output
911 (
912 p_output_to => 'FND_LOG.STRING'
913 ,p_api_name => l_api_name
914 ,p_message => 'Error: intransit type is not direct nor intransit'
915 );
916 END IF; /*l_intransit_type = 1 */
917
918 -- begin debug log
919 JMF_SHIKYU_UTIL.debug_output
920 (
921 p_output_to => 'FND_LOG.STRING'
922 ,p_api_name => l_api_name
923 ,p_message => 'l_in_transit'||l_in_transit
924 );
925 -- end debug log
926
927 l_receive_date := p_actual_shipment_date + l_in_transit;
928
929 IF l_receive_date <= SYSDATE
930 THEN
931 l_date_flag := 1;
932 END IF; /*l_receive_date <= SYSDATE */
933
934 x_date_flag := l_date_flag;
935
936 JMF_SHIKYU_UTIL.debug_output
937 (
938 p_output_to => 'FND_LOG.STRING'
939 ,p_api_name => l_api_name
940 ,p_message => 'END procedure. '
941 );
942 EXCEPTION
943 WHEN OTHERS THEN
944 JMF_SHIKYU_UTIL.debug_output
945 (
946 p_output_to => 'FND_LOG.STRING'
947 ,p_api_name => l_api_name
948 ,p_message => 'Unknown error and date flag is 0 '||SQLCODE||SQLERRM
949 );
950 x_date_flag := 0;
951
952 END validate_receive_date;
953
954 --==========================================================================
955 -- API NAME: validate_rcv_error
956 --
957 -- DESCRIPTION: this procedure avoid process the error line again. if rcv flag
958 -- return 1 this line is no error , else if return 0 is error.
959 --
960 -- PARAMETERS: In:
961 -- p_po_line_id replenishment PO 's line_id
962
963 -- Out: x_rcv_flag if this po line is rcv error , return 0
964 --
965 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
966 --
967 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
968 --===========================================================================
969
970 --==========================================================================
971 -- API NAME: compare_lines_quantity
972 --
973 -- DESCRIPTION: the procedure is compare the quantity of SO line and PO shipment
974 -- when one SO header has more than one SO lines, return the different of this
975 -- two quantity
976 --
977 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
978 -- p_lines_id IN line_id_tbl
979 -- p_po_header_id IN NUMBER
980 -- p_po_line_id IN NUMBER
981 -- p_po_shipment_id IN NUMBER
982 --
983 -- Out: x_receive_quantity the different of SO quantity and PO quantity
984 -- x_uom_code OUT NOCOPY VARCHAR2
985 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
986 --
987 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
988 --===========================================================================
989 PROCEDURE compare_lines_quantity
990 ( p_so_header_id IN NUMBER
991 , p_inventory_org_id IN NUMBER
992 , p_lines_id IN line_id_tbl
993 , p_po_header_id IN NUMBER
994 , p_po_line_id IN NUMBER
995 , p_po_shipment_id IN NUMBER
996 , p_ship_from_org_id IN NUMBER
997 , x_receive_quantity OUT NOCOPY NUMBER
998 , x_uom_code OUT NOCOPY VARCHAR2
999 )
1000 IS
1001
1002 l_api_name VARCHAR2(40) := 'compare_lines_quantity';
1003 l_shipped_quantity oe_order_lines_all.shipped_quantity%TYPE;
1004 l_line_quantity oe_order_lines_all.shipped_quantity%TYPE;
1005 l_returned_quantity NUMBER;
1006 l_received_quantity NUMBER;
1007
1008 l_primary_uom_code mtl_units_of_measure_tl.uom_code%TYPE;
1009
1010 l_prm_uom_quantity_so NUMBER;
1011 l_prm_uom_quantity_po NUMBER;
1012
1013 l_po_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1014 l_po_uom_code mtl_units_of_measure_tl.uom_code%TYPE;
1015
1016 l_item_id oe_order_lines_all.inventory_item_id%TYPE;
1017 l_uom_code oe_order_lines_all.shipping_quantity_uom%TYPE;
1018
1019 l_backorder_shipped_quantity NUMBER;
1020
1021 l_index NUMBER;
1022
1023 BEGIN
1024
1025 JMF_SHIKYU_UTIL.debug_output
1026 (
1027 p_output_to => 'FND_LOG.STRING'
1028 ,p_api_name => l_api_name
1029 ,p_message => 'procedure begin'
1030 );
1031
1032 -- init x_receive_quantity
1033 x_receive_quantity := 0;
1034 l_line_quantity := 0;
1035 l_shipped_quantity :=0;
1036 l_received_quantity := 0;
1037 l_returned_quantity := 0;
1038 l_backorder_shipped_quantity := 0;
1039
1040 l_index := p_lines_id.FIRST;
1041 WHILE l_index IS NOT NULL
1042 LOOP
1043 -- init variable
1044 l_line_quantity := 0;
1045 l_prm_uom_quantity_so := 0;
1046 l_returned_quantity := 0;
1047 BEGIN
1048 SELECT NVL(l.shipped_quantity,0)
1049 , l.inventory_item_id
1050 --, l.shipping_quantity_uom
1051 , l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
1052 INTO l_line_quantity
1053 , l_item_id
1054 , l_uom_code
1055 FROM oe_order_lines_all l
1056 WHERE l.line_id = p_lines_id(l_index);
1057
1058 EXCEPTION
1059 WHEN no_data_found THEN
1060 JMF_SHIKYU_UTIL.debug_output
1061 (
1062 p_output_to => 'FND_LOG.STRING'
1063 ,p_api_name => l_api_name
1064 ,p_message => 'no data found when find shipped_quantity and item_id'
1065 );
1066 RAISE;
1067 WHEN too_many_rows THEN
1068 JMF_SHIKYU_UTIL.debug_output
1069 (
1070 p_output_to => 'FND_LOG.STRING'
1071 ,p_api_name => l_api_name
1072 ,p_message => 'too many rows when find shipped_quantity and item_id'
1073 );
1074 RAISE;
1075 END;
1076
1077 --convert UOM
1078 --get primary uom code
1079 l_primary_uom_code := Jmf_Shikyu_Rpt_Util.get_item_primary_uom_code
1080 ( p_org_id => p_inventory_org_id
1081 , p_item_id => l_item_id
1082 , p_current_uom_code => l_uom_code
1083 );
1084
1085 x_uom_code := l_primary_uom_code;
1086
1087 l_prm_uom_quantity_so := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1088 ( p_org_id => p_inventory_org_id
1089 , p_item_id => l_item_id
1090 , p_current_uom_code => l_uom_code
1091 , p_current_qty => l_line_quantity
1092 );
1093
1094 JMF_SHIKYU_UTIL.debug_output
1095 (
1096 p_output_to => 'FND_LOG.STRING'
1097 ,p_api_name => l_api_name
1098 ,p_message => 'l_prm_uom_quantity_so :' || l_prm_uom_quantity_so
1099 );
1100
1101 --get back order line shipped quantity
1102 get_backorder_shipped_quantity(p_so_header_id => p_so_header_id
1103 ,p_so_line_id => p_lines_id(l_index)
1104 ,p_inventory_org_id => p_inventory_org_id
1105 ,x_backorder_shipped_quantity => l_backorder_shipped_quantity
1106 );
1107
1108 JMF_SHIKYU_UTIL.debug_output
1109 (
1110 p_output_to => 'FND_LOG.STRING'
1111 ,p_api_name => l_api_name
1112 ,p_message => 'l_backorder_shipped_quantity :' || l_backorder_shipped_quantity
1113 );
1114
1115 -- this should be open if this porcedure is avilable.
1116
1117 -- sub the return quantity (the returned quantity is in primary UOM)
1118
1119 l_returned_quantity := jmf_shikyu_util.Get_Replenish_So_Returned_Qty(p_replenishment_so_line_id => p_lines_id(l_index));
1120
1121 JMF_SHIKYU_UTIL.debug_output
1122 (
1123 p_output_to => 'FND_LOG.STRING'
1124 ,p_api_name => l_api_name
1125 ,p_message => 'l_returned_quantity :' || l_returned_quantity
1126 );
1127
1128 --l_returned_quantity := 0; -- this statements is replace the procedure above.
1129 l_line_quantity := l_prm_uom_quantity_so + l_backorder_shipped_quantity - l_returned_quantity;
1130 l_shipped_quantity := l_shipped_quantity + l_line_quantity;
1131
1132 l_index := p_lines_id.NEXT(l_index);
1133 END LOOP; -- l_index is not null
1134
1135 BEGIN
1136 SELECT locate.quantity_received
1137 , locate.unit_meas_lookup_code
1138 INTO l_received_quantity
1139 , l_po_uom
1140 FROM po_line_locations_all locate
1141 WHERE locate.line_location_id = p_po_shipment_id;
1142
1143 EXCEPTION
1144 WHEN no_data_found THEN
1145 JMF_SHIKYU_UTIL.debug_output
1146 (
1147 p_output_to => 'FND_LOG.STRING'
1148 ,p_api_name => l_api_name
1149 ,p_message => 'no data found , when find quantity_received by line_location_id'
1150 );
1151 RAISE;
1152 END;
1153
1154 BEGIN
1155 SELECT DISTINCT uom.uom_code
1156 INTO l_po_uom_code
1157 FROM mtl_units_of_measure_tl uom
1158 WHERE uom.unit_of_measure = l_po_uom
1159 AND uom.LANGUAGE = userenv('LANG');
1160
1161 EXCEPTION
1162 WHEN no_data_found THEN
1163 JMF_SHIKYU_UTIL.debug_output
1164 (
1165 p_output_to => 'FND_LOG.STRING'
1166 ,p_api_name => l_api_name
1167 ,p_message => 'no data found , when find uom_code by unit_of_measure'
1168 );
1169 RAISE;
1170 END;
1171
1172 -- convery the received quantity to Primary UOM
1173 l_prm_uom_quantity_po := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1174 ( p_org_id => p_inventory_org_id
1175 , p_item_id => l_item_id
1176 , p_current_uom_code => l_po_uom_code
1177 , p_current_qty => l_received_quantity
1178 );
1179
1180 -- compare
1181 x_receive_quantity := l_shipped_quantity - l_prm_uom_quantity_po;
1182
1183 JMF_SHIKYU_UTIL.debug_output
1184 (
1185 p_output_to => 'FND_LOG.STRING'
1186 ,p_api_name => l_api_name
1187 ,p_message => 'END procedure. '
1188 );
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 JMF_SHIKYU_UTIL.debug_output
1192 (
1193 p_output_to => 'FND_LOG.STRING'
1194 ,p_api_name => l_api_name
1195 ,p_message => 'Unknown error and x_receive_quantity is 0 '||SQLCODE||SQLERRM
1196 );
1197 x_receive_quantity := 0;
1198 END compare_lines_quantity;
1199
1200 --==========================================================================
1201 -- API NAME: get_backorder_shipped_quantity
1202 --
1203 -- DESCRIPTION: the procedure can get the back orderer quantity which is shipped
1204 -- by the split from line id
1205 --
1206 -- PARAMETERS: In: p_inventory_org_id IN NUMBER
1207 -- p_line_id IN NUMBER
1208 -- p_so_header_id IN NUMBER
1209 --
1210 -- Out: x_backorder_shipped_quantity OUT NUMBER
1211 --
1212 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1213 --
1214 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1215 -- CHANGE HISTORY: 10-May-06 Amy updated procecure .get_backorder_shipped_quantity to fix bug #5212672
1216 --===========================================================================
1217 PROCEDURE get_backorder_shipped_quantity
1218 (p_so_header_id IN NUMBER
1219 ,p_so_line_id IN NUMBER
1220 ,p_inventory_org_id IN NUMBER
1221 ,x_backorder_shipped_quantity OUT NOCOPY NUMBER
1222 )
1223 IS
1224 l_api_name VARCHAR2(100) := 'get_backorder_shipped_quantity';
1225 l_shipped_quantity NUMBER;
1226 l_shipped_quantity_amount NUMBER;
1227 l_inventory_item_id NUMBER;
1228 l_uom mtl_units_of_measure_tl.uom_code%TYPE;
1229 l_line_count NUMBER;
1230 l_prm_uom_quantity_so NUMBER;
1231
1232 CURSOR back_order_line_c (p_so_header_id IN NUMBER,p_line_id IN NUMBER)
1233 IS
1234 SELECT NVL(l.shipped_quantity,0)
1235 , l.inventory_item_id
1236 --, l.shipping_quantity_uom
1237 , l.order_quantity_uom -- Bug#5647346: changed to order_quantity_uom from shipping_quantity_uom since the shipped_quantity is order_quantity_uom
1238
1239 FROM oe_order_lines_all l
1240 WHERE l.split_from_line_id = p_line_id
1241 AND l.header_id = p_so_header_id;
1242 BEGIN
1243
1244 JMF_SHIKYU_UTIL.debug_output
1245 (
1246 p_output_to => 'FND_LOG.STRING'
1247 ,p_api_name => l_api_name
1248 ,p_message => 'procedure begin'
1249 );
1250
1251 -- init the return quantity
1252 x_backorder_shipped_quantity := 0;
1253 l_shipped_quantity := 0;
1254 l_shipped_quantity_amount := 0;
1255 --get the line count of replenishment so
1256 SELECT
1257 COUNT(*)
1258 INTO
1259 l_line_count
1260 FROM
1261 oe_order_lines_all
1262 WHERE header_id = p_so_header_id;
1263
1264 IF l_line_count < 2
1265 THEN
1266 x_backorder_shipped_quantity := 0;
1267 ELSE
1268 -- begin log
1269 JMF_SHIKYU_UTIL.debug_output
1270 (
1271 p_output_to => 'FND_LOG.STRING'
1272 ,p_api_name => l_api_name
1273 ,p_message => 'back order line exist in '||p_so_header_id
1274 );
1275 -- end log
1276 OPEN back_order_line_c(p_so_header_id,p_so_line_id);
1277 LOOP
1278 FETCH back_order_line_c INTO l_shipped_quantity, l_inventory_item_id, l_uom;
1279 EXIT WHEN back_order_line_c%NOTFOUND;
1280
1281 --get the shipped quantity in primary quantity
1282 l_prm_uom_quantity_so := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1283 ( p_org_id => p_inventory_org_id
1284 , p_item_id => l_inventory_item_id
1285 , p_current_uom_code => l_uom
1286 , p_current_qty => l_shipped_quantity
1287 );
1288 l_shipped_quantity_amount := l_shipped_quantity_amount + l_prm_uom_quantity_so;
1289
1290
1291 END LOOP;
1292
1293 x_backorder_shipped_quantity := l_shipped_quantity_amount;
1294 END IF;
1295
1296 -- begin log
1297 JMF_SHIKYU_UTIL.debug_output
1298 (
1299 p_output_to => 'FND_LOG.STRING'
1300 ,p_api_name => l_api_name
1301 ,p_message => 'back order line shipped quantity is '||l_shipped_quantity_amount
1302 );
1303 -- end log
1304
1305 JMF_SHIKYU_UTIL.debug_output
1306 (
1307 p_output_to => 'FND_LOG.STRING'
1308 ,p_api_name => l_api_name
1309 ,p_message => 'END procedure. '
1310 );
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 JMF_SHIKYU_UTIL.debug_output
1315 (
1316 p_output_to => 'FND_LOG.STRING'
1317 ,p_api_name => l_api_name
1318 ,p_message => 'Unknown error and x_receive_quantity is 0 '||SQLCODE||SQLERRM
1319 );
1320 x_backorder_shipped_quantity :=0;
1321
1322 END get_backorder_shipped_quantity;
1323
1324 --==========================================================================
1325 -- API NAME: get_in_transit
1326 --
1327 -- DESCRIPTION: the procedure is get the in-transit time by shipping network
1328 --
1329 -- PARAMETERS: In: p_ship_from_org_id the ship from org in shipping network
1330 -- p_ship_to_org_id the ship to org in shipping network
1331 -- p_ship_method
1332 -- Out: x_in_transit in-transit value
1333 --
1334 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1335 --
1336 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1337 --===========================================================================
1338 PROCEDURE get_in_transit
1339 ( p_ship_from_org_id IN NUMBER
1340 , p_ship_to_org_id IN NUMBER
1341 , p_ship_method IN VARCHAR2
1342 , x_in_transit OUT NOCOPY NUMBER
1343 )
1344 IS
1345 l_api_name VARCHAR2(30):= 'get_in_transit';
1346 l_intransit MTL_INTERORG_SHIP_METHODS.INTRANSIT_TIME%TYPE;
1347 l_default_flag NUMBER;
1348 l_ship_method MTL_INTERORG_SHIP_METHODS.ship_method%TYPE;
1349
1350 CURSOR ship_method_c IS
1351 SELECT
1352 ship_methods.Intransit_Time
1353 , ship_methods.default_flag
1354 , ship_methods.ship_method
1355 FROM
1356 MTL_INTERORG_SHIP_METHODS ship_methods
1357 WHERE
1358 ship_methods.from_organization_id = p_ship_from_org_id
1359 AND ship_methods.To_Organization_Id = p_ship_to_org_id
1360 AND ship_methods.default_flag = 1;
1361
1362 BEGIN
1363 JMF_SHIKYU_UTIL.debug_output
1364 (
1365 p_output_to => 'FND_LOG.STRING'
1366 ,p_api_name => l_api_name
1367 ,p_message => 'procedure begin'
1368 );
1369
1370 l_intransit := 0;
1371 OPEN ship_method_c;
1372 LOOP
1373 FETCH ship_method_c INTO l_intransit, l_default_flag, l_ship_method;
1374 EXIT WHEN ship_method_c%NOTFOUND;
1375 IF l_default_flag = 1
1376 THEN
1377 x_in_transit := l_intransit;
1378 EXIT;
1379 END IF;
1380
1381 END LOOP;
1382 CLOSE ship_method_c;
1383
1384 JMF_SHIKYU_UTIL.debug_output
1385 (
1386 p_output_to => 'FND_LOG.STRING'
1387 ,p_api_name => l_api_name
1388 ,p_message => 'END procedure. '
1389 );
1390
1391 END get_in_transit;
1392
1393 --==========================================================================
1394 -- API NAME: get_customer_id
1395 --
1396 -- DESCRIPTION: To get customer id and customer site id by p_org_id
1397 -- in org define module
1398 --
1399 -- PARAMETERS: In: p_org_inventory_id IN NUMBER
1400
1401 -- Out: x_customer_id OUT NOCOPY NUMBER
1402 -- x_customer_site_id OUT NOCOPY NUMBER
1403 --
1404 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1405 --
1406 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1407 --===========================================================================
1408 PROCEDURE get_customer_id
1409 ( p_org_inventory_id IN NUMBER
1410 , x_customer_id OUT NOCOPY NUMBER
1411 , x_customer_site_id OUT NOCOPY NUMBER
1412 )
1413 IS
1414 l_api_name VARCHAR2(30) := 'get_customer_id';
1415 l_customer_id NUMBER;
1416 l_customer_site_id NUMBER;
1417 BEGIN
1418 JMF_SHIKYU_UTIL.debug_output
1419 (
1420 p_output_to => 'FND_LOG.STRING'
1421 ,p_api_name => l_api_name
1422 ,p_message => 'procedure begin'
1423 );
1424
1425 BEGIN
1426 SELECT
1427 hoi.org_information1 Customer_id
1428 ,hoi.org_information2 Customer_site_id
1429 --,hoi.org_information3 Supplier_id
1430 --,hoi.org_information4 Supplier_site_id
1431 INTO
1432 l_customer_id
1433 , l_customer_site_id
1434 FROM
1435 HR_ORGANIZATION_INFORMATION hoi
1436 WHERE hoi.org_information_context = 'Customer/Supplier Association'
1437 AND hoi.organization_id = p_org_inventory_id;
1438 EXCEPTION
1439 WHEN no_data_found THEN
1440 JMF_SHIKYU_UTIL.debug_output
1441 (
1442 p_output_to => 'FND_LOG.STRING'
1443 ,p_api_name => l_api_name
1444 ,p_message => 'no data find customer name, customer site name'
1445 );
1446 RAISE;
1447 WHEN too_many_rows THEN
1448 JMF_SHIKYU_UTIL.debug_output
1449 (
1450 p_output_to => 'FND_LOG.STRING'
1451 ,p_api_name => l_api_name
1452 ,p_message => 'too many rows when find customer name, customer site name'
1453 );
1454 RAISE;
1455 END;
1456
1457 x_customer_id := l_customer_id;
1458 x_customer_site_id := l_customer_site_id;
1459
1460 JMF_SHIKYU_UTIL.debug_output
1461 (
1462 p_output_to => 'FND_LOG.STRING'
1463 ,p_api_name => l_api_name
1464 ,p_message => 'END procedure. '
1465 );
1466
1467 EXCEPTION
1468 WHEN OTHERS THEN
1469 JMF_SHIKYU_UTIL.debug_output
1470 (
1471 p_output_to => 'FND_LOG.STRING'
1472 ,p_api_name => l_api_name
1473 ,p_message => 'Unknown error'||SQLCODE||SQLERRM
1474 );
1475 x_customer_id := NULL;
1476 x_customer_site_id := NULL;
1477
1478 END get_customer_id;
1479
1480 --==========================================================================
1481 -- API NAME: get_supplier_id
1482 --
1483 -- DESCRIPTION: the procedure is get supplier name by p_org_id in org define module
1484 --
1485 -- PARAMETERS: In: p_sold_from_org_id
1486
1487 -- Return : supplier_id
1488 --
1489 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1490 --
1491 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1492 -- 14-Nov-06 Vincent.Chu Bug 5592230/5659317: Changed type
1493 -- from Function to Procedure
1494 --===========================================================================
1495 PROCEDURE get_supplier_id
1496 ( p_sold_from_org_id IN NUMBER
1497 , x_supplier_id OUT NOCOPY NUMBER
1498 , x_supplier_site_id OUT NOCOPY NUMBER
1499 )
1500 IS
1501 l_api_name VARCHAR2(30) := 'get_supplier_name';
1502 l_supplier_id po_vendors.vendor_id%TYPE;
1503 l_supplier_site_id po_vendor_sites_all.vendor_site_id%TYPE;
1504 l_supplier_name po_vendors.vendor_name%TYPE;
1505
1506 BEGIN
1507
1508 JMF_SHIKYU_UTIL.debug_output
1509 (
1510 p_output_to => 'FND_LOG.STRING'
1511 ,p_api_name => l_api_name
1512 ,p_message => 'procedure begin'
1513 );
1514
1515 BEGIN
1516 SELECT
1517 hoi.org_information3 Supplier_id
1518 , hoi.org_information4 Supplier_site_id
1519 INTO
1520 l_supplier_id
1521 , l_supplier_site_id
1522 FROM
1523 HR_ORGANIZATION_INFORMATION hoi
1524 WHERE hoi.org_information_context = 'Customer/Supplier Association'
1525 AND hoi.organization_id = p_sold_from_org_id;
1526 EXCEPTION
1527 WHEN no_data_found THEN
1528 JMF_SHIKYU_UTIL.debug_output
1529 (
1530 p_output_to => 'FND_LOG.STRING'
1531 ,p_api_name => l_api_name
1532 ,p_message => 'no data find supplier id'
1533 );
1534 RAISE;
1535 WHEN too_many_rows THEN
1536 JMF_SHIKYU_UTIL.debug_output
1537 (
1538 p_output_to => 'FND_LOG.STRING'
1539 ,p_api_name => l_api_name
1540 ,p_message => 'too many rows when find supplier id'
1541 );
1542 RAISE;
1543 END;
1544
1545 x_supplier_id := l_supplier_id;
1546 x_supplier_site_id := l_supplier_site_id;
1547
1548 BEGIN
1549 SELECT po_vendors.vendor_name
1550 INTO l_supplier_name
1551 FROM po_vendors
1552 WHERE po_vendors.vendor_id = l_supplier_id;
1553 EXCEPTION
1554 WHEN no_data_found THEN
1555 JMF_SHIKYU_UTIL.debug_output
1556 (
1557 p_output_to => 'FND_LOG.STRING'
1558 ,p_api_name => l_api_name
1559 ,p_message => 'no data find supplier name'
1560 );
1561 RAISE;
1562 WHEN too_many_rows THEN
1563 JMF_SHIKYU_UTIL.debug_output
1564 (
1565 p_output_to => 'FND_LOG.STRING'
1566 ,p_api_name => l_api_name
1567 ,p_message => 'too many rows when find supplier name'
1568 );
1569 RAISE;
1570
1571 END;
1572
1573 JMF_SHIKYU_UTIL.debug_output
1574 (
1575 p_output_to => 'FND_LOG.STRING'
1576 ,p_api_name => l_api_name
1577 ,p_message => 'END procedure. '
1578 );
1579
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 JMF_SHIKYU_UTIL.debug_output
1583 (
1584 p_output_to => 'FND_LOG.STRING'
1585 ,p_api_name => l_api_name
1586 ,p_message => 'Unknown error '||SQLCODE||SQLERRM
1587 );
1588
1589 x_supplier_id := NULL;
1590 x_supplier_site_id := NULL;
1591
1592 END get_supplier_id;
1593
1594 --==========================================================================
1595 -- API NAME: process_rcv_interface
1596 --
1597 -- DESCRIPTION: To insert value to rcv_header_interface and
1598 -- rcv_transcation_interface
1599 --
1600 -- PARAMETERS: In: p_inventory_org_id Manufacturing Partner Organization id
1601 -- p_line_id replenishment SO line id
1602 -- p_po_header_id replenishment PO header id
1603 -- p_po_line_id replenishment PO line id
1604 -- p_po_shipment_id replenishment PO shipment id
1605 -- p_ship_from_org_id ship from org id
1606 -- p_ship_to_org_id ship to org id
1607 -- p_receive_quantity the quantity which should auto receive
1608 -- p_primary_uom_code primary_uom_code
1609 --
1610 -- DESIGN REFERENCES: SHIKYU_AutoReceiving_TD_New.doc
1611 --
1612 -- CHANGE HISTORY: 18-May-05 Jim.Zheng Created.
1613 -- 14-Nov-06 Vincent.Chu Bug 5592230/5659317: Changed the
1614 -- calls to get_supplier_id and
1615 -- JMF_SHIKYU_RCV_PVT.process_rcv_header
1616 -- in order to pass in the supplier
1617 -- site id for the RCV transaction.
1618 --===========================================================================
1619 PROCEDURE process_rcv_interface
1620 ( p_inventory_org_id IN NUMBER
1621 --, p_line_id IN NUMBER
1622 , p_lines_id IN line_id_tbl
1623 , p_po_header_id IN NUMBER
1624 , p_po_line_id IN NUMBER
1625 , p_po_shipment_id IN NUMBER
1626 , p_ship_from_org_id IN NUMBER
1627 , p_ship_to_org_id IN NUMBER
1628 , p_receive_quantity IN NUMBER
1629 , p_primary_uom_code IN VARCHAR2
1630 )
1631 IS
1632
1633 l_api_name VARCHAR2(50) := 'process_rcv_interface';
1634 l_routing_header_id PO_LINE_LOCATIONS_ALL.Receiving_Routing_Id%TYPE;
1635 l_dest_subinventory rcv_transactions_interface.subinventory%TYPE;
1636 l_dest_locator_id wip_parameters.default_pull_supply_locator_id%TYPE;
1640 l_task_id OE_ORDER_LINES_ALL.task_id%TYPE;
1637 l_default_locator_id wip_parameters.default_pull_supply_locator_id%TYPE;
1638 l_locator_type MTL_SECONDARY_INVENTORIES.locator_type%TYPE;
1639 l_project_id OE_ORDER_LINES_ALL.project_id%TYPE;
1641
1642 l_group_id rcv_headers_interface.group_id%TYPE;
1643 l_return_number NUMBER;
1644 --l_unit_of_measure mtl_units_of_measure_tl.unit_of_measure%TYPE;
1645
1646 l_rcv_header_id NUMBER;
1647 l_transaction_type VARCHAR2(20) := 'RECEIVE';
1648
1649 l_index NUMBER;
1650
1651 -- Bug 5592230
1652 l_supplier_id NUMBER;
1653 l_supplier_site_id NUMBER;
1654
1655 CURSOR po_distributions_c(p_location_id IN NUMBER, p_line_id IN NUMBER) IS
1656 SELECT d.po_distribution_id,
1657 d.distribution_num,
1658 d.quantity_ordered,
1659 d.quantity_delivered,
1660 l.unit_meas_lookup_code,
1661 oola.shipped_quantity,
1662 uom.unit_of_measure,
1663 oola.inventory_item_id
1664 FROM po_distributions_all d,
1665 po_line_locations_all l,
1666 MTL_UNITS_OF_MEASURE uom,
1667 jmf_shikyu_replenishments jsr,
1668 oe_order_lines_all oola
1669 WHERE d.line_location_id = p_location_id
1670 AND d.LINE_LOCATION_ID = jsr.replenishment_PO_shipment_ID
1671 and oola.line_id = jsr.replenishment_so_line_id
1672 and nvl(oola.shipped_quantity, 0) > 0
1673 AND l.line_location_id = d.line_location_id
1674 AND oola.order_quantity_uom = uom.uom_code(+)
1675 and oola.line_id = p_line_id
1676 AND not exists (select 1
1677 from rcv_transactions rt
1678 where REPLENISH_ORDER_LINE_ID = oola.line_id)
1679 AND not exists (select 1
1680 from rcv_transactions_interface rti
1681 where REPLENISH_ORDER_LINE_ID = oola.line_id
1682 and TRANSACTION_STATUS_CODE <> 'ERROR'
1683 AND PROCESSING_STATUS_CODE <> 'ERROR')
1684 ORDER BY d.distribution_num; --If multiple Replenishment PO Distributions exist then handle in their order.
1685
1686 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
1687 l_distribution_num po_distributions_all.distribution_num%TYPE;
1688 l_quantity_ordered po_distributions_all.quantity_ordered%TYPE;
1689 l_quantity_delivered po_distributions_all.quantity_delivered%TYPE;
1690 --l_receive_quantity NUMBER;
1691 l_insert_quantity NUMBER;
1692 l_allocated_quantity NUMBER;
1693
1694 -- Bug#5647346
1695 l_prm_uom_quantity_ordered po_distributions_all.quantity_ordered%TYPE;
1696 l_prm_uom_quantity_delivered po_distributions_all.quantity_delivered%TYPE;
1697 l_prm_uom_allocated_quantity NUMBER;
1698 l_prm_uom_receive_quantity NUMBER;
1699 l_primary_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1700 l_po_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1701 l_so_uom mtl_units_of_measure_tl.unit_of_measure%TYPE;
1702 l_item_id oe_order_lines_all.inventory_item_id%TYPE;
1703
1704 BEGIN
1705 JMF_SHIKYU_UTIL.debug_output
1706 (
1707 p_output_to => 'FND_LOG.STRING'
1708 ,p_api_name => l_api_name
1709 ,p_message => 'procedure begin'
1710 );
1711 /* JMF_SHIKYU_UTIL.debug_output
1712 (
1713 p_output_to => 'FND_LOG.STRING'
1714 ,p_api_name => l_api_name
1715 ,p_message => 'p_line_id '||p_line_id
1716 );*/
1717 JMF_SHIKYU_UTIL.debug_output
1718 (
1719 p_output_to => 'FND_LOG.STRING'
1720 ,p_api_name => l_api_name
1721 ,p_message => 'p_po_header_id '||p_po_header_id
1722 );
1723 JMF_SHIKYU_UTIL.debug_output
1724 (
1725 p_output_to => 'FND_LOG.STRING'
1726 ,p_api_name => l_api_name
1727 ,p_message => 'p_po_line_id'||p_po_line_id
1728 );
1729 JMF_SHIKYU_UTIL.debug_output
1730 (
1731 p_output_to => 'FND_LOG.STRING'
1732 ,p_api_name => l_api_name
1733 ,p_message => 'p_po_shipment_id'||p_po_shipment_id
1734 );
1735 JMF_SHIKYU_UTIL.debug_output
1736 (
1737 p_output_to => 'FND_LOG.STRING'
1738 ,p_api_name => l_api_name
1739 ,p_message => 'p_ship_from_org_id'||p_ship_from_org_id
1740 );
1741 JMF_SHIKYU_UTIL.debug_output
1742 (
1743 p_output_to => 'FND_LOG.STRING'
1744 ,p_api_name => l_api_name
1745 ,p_message => 'p_ship_to_org_id'||p_ship_to_org_id
1746 );
1747 JMF_SHIKYU_UTIL.debug_output
1748 (
1749 p_output_to => 'FND_LOG.STRING'
1750 ,p_api_name => l_api_name
1751 ,p_message => 'p_receive_quantity'||p_receive_quantity
1752 );
1753
1754 l_prm_uom_receive_quantity := p_receive_quantity;
1755 -- get UOM by UOM_code
1756
1757 BEGIN
1758 SELECT
1759 uom.unit_of_measure
1760 INTO
1761 l_primary_uom
1762 FROM
1763 mtl_units_of_measure_vl uom
1764 WHERE uom.uom_code = p_primary_uom_code;
1765 EXCEPTION
1766 WHEN no_data_found THEN
1767 JMF_SHIKYU_UTIL.debug_output
1768 (
1769 p_output_to => 'FND_LOG.STRING'
1770 ,p_api_name => l_api_name
1771 ,p_message => 'no data found when select receiving_routing_id by line_location_id'
1772 );
1773 RAISE;
1774 END;
1775
1776 JMF_SHIKYU_UTIL.debug_output
1777 (
1778 p_output_to => 'FND_LOG.STRING'
1779 ,p_api_name => l_api_name
1780 ,p_message => 'l_unit_of_measure'||l_primary_uom
1781 );
1782
1786 location.RECEIVING_ROUTING_ID -- 1 is standard and 2 is inspection, 3 is direct
1783 -- get routing_header_id and get received method
1784 BEGIN
1785 SELECT
1787 INTO
1788 l_routing_header_id
1789 FROM
1790 PO_LINE_LOCATIONS_ALL location
1791 WHERE location.line_location_id = p_po_shipment_id;
1792 EXCEPTION
1793 WHEN no_data_found THEN
1794 JMF_SHIKYU_UTIL.debug_output
1795 (
1796 p_output_to => 'FND_LOG.STRING'
1797 ,p_api_name => l_api_name
1798 ,p_message => 'no data found when select receiving_routing_id by line_location_id'
1799 );
1800 RAISE;
1801 END;
1802
1803 -- get supplier_id and supplier_site_id
1804 get_supplier_id( p_sold_from_org_id => p_ship_from_org_id
1805 , x_supplier_id => l_supplier_id
1806 , x_supplier_site_id => l_supplier_site_id
1807 );
1808
1809 -- begin debug log
1810 JMF_SHIKYU_UTIL.debug_output
1811 (
1812 p_output_to => 'FND_LOG.STRING'
1813 ,p_api_name => l_api_name
1814 ,p_message => 'begin process_rcv_header'
1815 );
1816 JMF_SHIKYU_UTIL.debug_output
1817 (
1818 p_output_to => 'FND_LOG.STRING'
1819 ,p_api_name => l_api_name
1820 ,p_message => 'p_vendor_id '||l_supplier_id
1821 );
1822 JMF_SHIKYU_UTIL.debug_output
1823 (
1824 p_output_to => 'FND_LOG.STRING'
1825 ,p_api_name => l_api_name
1826 ,p_message => 'p_vendor_site_id '||l_supplier_site_id
1827 );
1828 JMF_SHIKYU_UTIL.debug_output
1829 (
1830 p_output_to => 'FND_LOG.STRING'
1831 ,p_api_name => l_api_name
1832 ,p_message => 'p_ship_to_org_id'||p_inventory_org_id
1833 );
1834 -- end debug log
1835
1836 --process rcv header , insert header data into rcv_headers_interface
1837 JMF_SHIKYU_RCV_PVT.process_rcv_header( p_vendor_id => l_supplier_id --l_customer_name
1838 , p_vendor_site_id => l_supplier_site_id
1839 , p_ship_to_org_id => p_inventory_org_id
1840 , x_rcv_header_id => l_rcv_header_id
1841 , x_group_id => l_group_id
1842 );
1843
1844 -- begin debug log
1845 JMF_SHIKYU_UTIL.debug_output
1846 (
1847 p_output_to => 'FND_LOG.STRING'
1848 ,p_api_name => l_api_name
1849 ,p_message => 'l_rcv_header_id '||l_rcv_header_id
1850 );
1851 JMF_SHIKYU_UTIL.debug_output
1852 (
1853 p_output_to => 'FND_LOG.STRING'
1854 ,p_api_name => l_api_name
1855 ,p_message => 'l_group_id'||l_group_id
1856 );
1857 JMF_SHIKYU_UTIL.debug_output
1858 (
1859 p_output_to => 'FND_LOG.STRING'
1860 ,p_api_name => l_api_name
1861 ,p_message => 'end process_rcv_header'
1862 );
1863 -- end debug log
1864
1865 -- begin debug log
1866 JMF_SHIKYU_UTIL.debug_output
1867 (
1868 p_output_to => 'FND_LOG.STRING'
1869 ,p_api_name => l_api_name
1870 ,p_message => 'l_routing_header_id'||l_routing_header_id
1871 );
1872 -- end debug log\
1873
1874 l_index := p_lines_id.FIRST;
1875 WHILE l_index IS NOT NULL
1876 LOOP
1877
1878 -- begin debug log
1879 JMF_SHIKYU_UTIL.debug_output
1880 (
1881 p_output_to => 'FND_LOG.STRING'
1882 ,p_api_name => l_api_name
1883 ,p_message => 'Begin cursor po_distributions_c loop'
1884 );
1885 JMF_SHIKYU_UTIL.debug_output
1886 (
1887 p_output_to => 'FND_LOG.STRING'
1888 ,p_api_name => l_api_name
1889 ,p_message => 'p_po_shipment_id'||p_po_shipment_id
1890 );
1891 JMF_SHIKYU_UTIL.debug_output
1892 (
1893 p_output_to => 'FND_LOG.STRING'
1894 ,p_api_name => l_api_name
1895 ,p_message => 'p_lines_id(l_index)'||p_lines_id(l_index)
1896 );
1897 -- end debug log\
1898
1899 OPEN po_distributions_c(p_po_shipment_id, p_lines_id(l_index));
1900
1901 LOOP
1902 l_insert_quantity := 0;
1903 FETCH
1904 po_distributions_c
1905 INTO
1906 l_po_distribution_id
1907 , l_distribution_num
1908 , l_quantity_ordered
1909 , l_quantity_delivered
1910 , l_po_uom
1911 , l_allocated_quantity
1912 , l_so_uom
1913 , l_item_id;
1914 EXIT WHEN po_distributions_c%NOTFOUND;
1915
1916 -- Bug#5647346: convert l_quantity_ordered, l_quantity_delivered, l_allocated_quantity to primary UOM
1917 l_prm_uom_quantity_ordered := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1918 ( p_org_id => p_inventory_org_id
1919 , p_item_id => l_item_id
1920 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_po_uom)
1921 , p_current_qty => l_quantity_ordered
1922 );
1923 l_prm_uom_quantity_delivered := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1924 ( p_org_id => p_inventory_org_id
1925 , p_item_id => l_item_id
1926 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_po_uom)
1927 , p_current_qty => l_quantity_delivered
1928 );
1929 l_prm_uom_allocated_quantity := Jmf_Shikyu_Rpt_Util.get_item_primary_quantity
1930 ( p_org_id => p_inventory_org_id
1931 , p_item_id => l_item_id
1932 , p_current_uom_code => JMF_SHIKYU_UTIL.Get_Uom_Code(l_so_uom)
1933 , p_current_qty => l_allocated_quantity
1934 );
1935
1936 -- begin insert data to interface,
1937 IF l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered > 0
1938 THEN
1939 -- begin insert data to interface, pay attion to sub inventory
1940 --quantity
1941 IF l_prm_uom_receive_quantity > l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered
1942 THEN
1943 --quantity = l_quantity_ordered - l_quantity_delivered
1944 -- p_receive_quantity := p_receive_quantity - (l_quantity_ordered - l_quantity_delivered)
1945 l_insert_quantity := l_allocated_quantity;
1946 l_prm_uom_receive_quantity := l_prm_uom_receive_quantity - (l_prm_uom_quantity_ordered - l_prm_uom_quantity_delivered);
1947
1948 ELSE /*p_receive_quantity <= l_quantity_ordered - l_quantity_delivered*/
1949 l_insert_quantity := l_allocated_quantity;
1950
1951 END IF ; /*end if p_receive_quantity > l_quantity_ordered - l_quantity_delivered*/
1952
1953 /*
1954 If there is no subinventory associated with PO Distributions then get
1955 Default Supply Subinventory from WIP Parameters.
1956 */
1957 IF l_dest_subinventory IS NULL
1958 THEN
1959 -- find the default subinventory
1960 -- if default is not null
1961 -- then ok
1962 -- else send a message.
1963 JMF_SHIKYU_UTIL.debug_output
1964 (
1965 p_output_to => 'FND_LOG.STRING'
1966 ,p_api_name => l_api_name
1967 ,p_message => 'Dest_subinventory is null'
1968 );
1969
1970 BEGIN
1971 SELECT wip_para.default_pull_supply_subinv
1972 INTO l_dest_subinventory
1973 FROM wip_parameters wip_para
1974 WHERE wip_para.Organization_Id = p_inventory_org_id;
1975 EXCEPTION
1976 WHEN no_data_found THEN
1977 JMF_SHIKYU_UTIL.debug_output
1978 (
1979 p_output_to => 'FND_LOG.STRING'
1980 ,p_api_name => l_api_name
1981 ,p_message => 'no data found when found subinventory by org id'
1982 );
1983
1984 -- log a message that the default sub inventory should be setup
1985 fnd_message.set_name('JMF', 'JMF_SHK_WIP_SUBINV_MIS');
1986 JMF_SHIKYU_UTIL.debug_output
1987 (
1988 p_output_to => 'FND_LOG.STRING'
1989 ,p_api_name => l_api_name
1990 ,p_message => fnd_message.GET
1991 );
1992
1993 RAISE;
1994 WHEN too_many_rows THEN
1995 JMF_SHIKYU_UTIL.debug_output
1996 (
1997 p_output_to => 'FND_LOG.STRING'
1998 ,p_api_name => l_api_name
1999 ,p_message => 'too many rows when found sub inventory by org id'
2000 );
2001 RAISE;
2002 END;
2003
2004 END IF;
2005
2006 -- begin debug log
2007 JMF_SHIKYU_UTIL.debug_output
2008 (
2009 p_output_to => 'FND_LOG.STRING'
2010 ,p_api_name => l_api_name
2011 ,p_message => 'l_dest_subinventory'||l_dest_subinventory
2012 );
2013 -- end debug log\
2014
2015 /*
2016 Get the Locator ID.
2017 */
2018 IF l_dest_locator_id IS NULL
2019 THEN
2020 --get the DEFAULT_PULL_SUPPLY_LOCATOR_ID
2021 BEGIN
2022 SELECT wip_para.default_pull_supply_locator_id
2023 INTO l_default_locator_id
2024 FROM wip_parameters wip_para
2028 JMF_SHIKYU_UTIL.debug_output
2025 WHERE wip_para.Organization_Id = p_inventory_org_id;
2026 EXCEPTION
2027 WHEN no_data_found THEN
2029 (
2030 p_output_to => 'FND_LOG.STRING'
2031 ,p_api_name => l_api_name
2032 ,p_message => 'no data found when found locator by org id'
2033 );
2034
2035 RAISE;
2036 WHEN too_many_rows THEN
2037 JMF_SHIKYU_UTIL.debug_output
2038 (
2039 p_output_to => 'FND_LOG.STRING'
2040 ,p_api_name => l_api_name
2041 ,p_message => 'too many rows when found default locator by org id'
2042 );
2043 RAISE;
2044 END;
2045
2046 -- begin debug log
2047 JMF_SHIKYU_UTIL.debug_output
2048 (
2049 p_output_to => 'FND_LOG.STRING'
2050 ,p_api_name => l_api_name
2051 ,p_message => 'l_default_locator_id'||l_default_locator_id
2052 );
2053 -- end debug log\
2054
2055 --get the LOCATOR_TYPE column of the subinventory
2056 BEGIN
2057 SELECT msi.locator_type
2058 INTO l_locator_type
2059 FROM MTL_SECONDARY_INVENTORIES msi
2060 WHERE msi.Organization_Id = p_inventory_org_id
2061 AND msi.SECONDARY_INVENTORY_NAME = l_dest_subinventory;
2062 EXCEPTION
2063 WHEN no_data_found THEN
2064 JMF_SHIKYU_UTIL.debug_output
2065 (
2066 p_output_to => 'FND_LOG.STRING'
2067 ,p_api_name => l_api_name
2068 ,p_message => 'no data found when found locator type by org id and locator id'
2069 );
2070 RAISE;
2071 END;
2072
2073 -- begin debug log
2074 JMF_SHIKYU_UTIL.debug_output
2075 (
2076 p_output_to => 'FND_LOG.STRING'
2077 ,p_api_name => l_api_name
2078 ,p_message => 'l_locator_type'||l_locator_type
2079 );
2080 -- end debug log\
2081
2082 --get the project id and task id
2083 BEGIN
2084 SELECT oola.project_id, oola.task_id
2085 INTO l_project_id, l_task_id
2086 FROM OE_ORDER_LINES_ALL oola
2087 WHERE oola.line_id = p_lines_id(l_index);
2088 EXCEPTION
2089 WHEN no_data_found THEN
2090 JMF_SHIKYU_UTIL.debug_output
2091 (
2092 p_output_to => 'FND_LOG.STRING'
2093 ,p_api_name => l_api_name
2094 ,p_message => 'no data found when found project id and task id by org id and locator id'
2095 );
2096 RAISE;
2097 END;
2098
2099 -- begin debug log
2100 JMF_SHIKYU_UTIL.debug_output
2101 (
2102 p_output_to => 'FND_LOG.STRING'
2103 ,p_api_name => l_api_name
2104 ,p_message => 'l_project_id'||l_project_id
2105 );
2106 JMF_SHIKYU_UTIL.debug_output
2107 (
2108 p_output_to => 'FND_LOG.STRING'
2109 ,p_api_name => l_api_name
2110 ,p_message => 'l_task_id'||l_task_id
2111 );
2112 -- end debug log\
2113
2114 --if this LOCATOR_TYPE column = 3 and if the project id of the Replenishment Sales Order Line is not null,
2115 --then call the PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator procedure.
2116 --else pass the DEFAULT_PULL_SUPPLY_LOCATOR_ID to RCV_Transaction_Interface.
2117 IF (l_locator_type = 3 and l_project_id IS NOT NULL)
2118 THEN
2119 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator(p_organization_id => p_inventory_org_id
2120 , p_locator_id => l_default_locator_id
2121 , p_project_id => l_project_id
2122 , p_task_id => l_task_id
2123 , p_project_locator_id => l_dest_locator_id
2124 );
2125 ELSE
2126 l_dest_locator_id := l_default_locator_id;
2127 END IF;
2128
2129 -- begin debug log
2130 JMF_SHIKYU_UTIL.debug_output
2131 (
2132 p_output_to => 'FND_LOG.STRING'
2133 ,p_api_name => l_api_name
2134 ,p_message => 'l_dest_locator_id'||l_dest_locator_id
2135 );
2136 -- end debug log\
2137
2138 End IF;
2139
2140 -- get all of the data
2141 ELSE /*l_quantity_ordered - l_quantity_delivered <= 0*/
2142 --begin log
2143 JMF_SHIKYU_UTIL.debug_output
2144 (
2145 p_output_to => 'FND_LOG.STRING'
2146 ,p_api_name => l_api_name
2147 ,p_message => 'the quantity auto receive finished '
2148 );
2149 --end log
2150 END IF ; /*end if l_quantity_ordered - l_quantity_delivered > 0 */
2151
2152 -- begin debug log
2153 JMF_SHIKYU_UTIL.debug_output
2154 (
2155 p_output_to => 'FND_LOG.STRING'
2156 ,p_api_name => l_api_name
2157 ,p_message => 'begin process_rcv_trx'
2158 );
2159 JMF_SHIKYU_UTIL.debug_output
2160 (
2161 p_output_to => 'FND_LOG.STRING'
2162 ,p_api_name => l_api_name
2163 ,p_message => 'l_rcv_header_id '||l_rcv_header_id
2164 );
2165 JMF_SHIKYU_UTIL.debug_output
2166 (
2167 p_output_to => 'FND_LOG.STRING'
2168 ,p_api_name => l_api_name
2169 ,p_message => 'l_insert_quantity'||l_insert_quantity
2170 );
2171 JMF_SHIKYU_UTIL.debug_output
2172 (
2173 p_output_to => 'FND_LOG.STRING'
2174 ,p_api_name => l_api_name
2175 ,p_message => 'l_unit_of_measure'||l_so_uom
2176 );
2177 JMF_SHIKYU_UTIL.debug_output
2178 (
2179 p_output_to => 'FND_LOG.STRING'
2180 ,p_api_name => l_api_name
2181 ,p_message => 'p_po_header_id'||p_po_header_id
2182 );
2183 JMF_SHIKYU_UTIL.debug_output
2184 (
2185 p_output_to => 'FND_LOG.STRING'
2186 ,p_api_name => l_api_name
2187 ,p_message => 'p_po_line_id'||p_po_line_id
2188 );
2189 JMF_SHIKYU_UTIL.debug_output
2190 (
2191 p_output_to => 'FND_LOG.STRING'
2192 ,p_api_name => l_api_name
2193 ,p_message => 'l_dest_subinventory'||l_dest_subinventory
2194 );
2195 JMF_SHIKYU_UTIL.debug_output
2196 (
2197 p_output_to => 'FND_LOG.STRING'
2198 ,p_api_name => l_api_name
2199 ,p_message => 'l_transaction_type'||l_transaction_type
2200 );
2201 JMF_SHIKYU_UTIL.debug_output
2202 (
2203 p_output_to => 'FND_LOG.STRING'
2204 ,p_api_name => l_api_name
2205 ,p_message => 'l_dest_locator_id'||l_dest_locator_id
2206 );
2207 JMF_SHIKYU_UTIL.debug_output
2208 (
2209 p_output_to => 'FND_LOG.STRING'
2210 ,p_api_name => l_api_name
2211 ,p_message => 'p_replenish_order_line_id '||p_lines_id(l_index)
2212 );
2213 -- end debug log\
2214
2215 IF l_routing_header_id = 1 OR l_routing_header_id = 2
2216 THEN
2217 -- process rcv trx, insert transactions data into rcv_transactions_interface
2218 JMF_SHIKYU_RCV_PVT.process_rcv_trx(p_rcv_header_id => l_rcv_header_id
2219 , p_group_id => l_group_id
2220 , p_quantity => l_insert_quantity
2221 , p_unit_of_measure => l_so_uom
2222 , p_po_header_id => p_po_header_id
2223 , p_po_line_id => p_po_line_id
2224 , p_subinventory => NULL
2225 , p_transaction_type => l_transaction_type
2226 , p_auto_transact_code => 'RECEIVE'
2227 , p_parent_transaction_id => NULL
2228 , p_po_line_location_id => p_po_shipment_id
2229 , P_locator_id => l_dest_locator_id
2230 , p_replenish_order_line_id => p_lines_id(l_index)
2231 );
2232 ELSIF l_routing_header_id = 3
2233 THEN
2234 -- process rcv trx, insert transactions data into rcv_transactions_interface
2235 JMF_SHIKYU_RCV_PVT.process_rcv_trx(p_rcv_header_id => l_rcv_header_id
2236 , p_group_id => l_group_id
2237 , p_quantity => l_insert_quantity
2238 , p_unit_of_measure => l_so_uom
2239 , p_po_header_id => p_po_header_id
2240 , p_po_line_id => p_po_line_id
2241 , p_subinventory => l_dest_subinventory
2242 , p_transaction_type => l_transaction_type
2243 , p_auto_transact_code => 'DELIVER'
2244 , p_parent_transaction_id => NULL
2245 , p_po_line_location_id => p_po_shipment_id
2246 , P_locator_id => l_dest_locator_id
2247 , p_replenish_order_line_id => p_lines_id(l_index)
2248 );
2249
2250 END IF;
2251
2252 END LOOP;
2253 CLOSE po_distributions_c;
2254 l_index := p_lines_id.NEXT(l_index);
2255 END LOOP;
2256 -- submit concurrent request in PL/SQL program.
2257
2258 l_return_number := fnd_request.submit_request(application => 'PO'
2259 , program => 'RVCTP'
2260 , description => 'Receiving Transaction Processor'
2261 , start_time => SYSDATE
2262 , sub_request => FALSE
2263 , argument1 => 'BATCH'
2264 , argument2 => l_group_id
2265 );
2266
2267 JMF_SHIKYU_UTIL.debug_output
2268 (
2269 p_output_to => 'FND_LOG.STRING'
2270 ,p_api_name => l_api_name
2271 ,p_message => 'The request id is : ' || l_return_number
2272 );
2273
2274 JMF_SHIKYU_UTIL.debug_output
2275 (
2276 p_output_to => 'FND_LOG.STRING'
2277 ,p_api_name => l_api_name
2278 ,p_message => 'END procedure. '
2279 );
2280
2281 EXCEPTION
2282 WHEN OTHERS THEN
2283 JMF_SHIKYU_UTIL.debug_output
2284 (
2285 p_output_to => 'FND_LOG.STRING'
2286 ,p_api_name => l_api_name
2287 ,p_message => 'Unknown error'||SQLCODE||SQLERRM
2288 );
2289 RAISE;
2290
2291 END process_rcv_interface;
2292
2293 END JMF_SHIKYU_AUTO_RCV_PROC;