[Home] [Help]
PACKAGE BODY: APPS.POS_CREATE_ASN
Source
1 PACKAGE BODY POS_CREATE_ASN AS
2 /* $Header: POSASNTB.pls 120.3 2006/01/30 10:46:08 shgao noship $*/
3
4 PROCEDURE create_asn_iface(
5 P_GROUP_ID IN NUMBER,
6 P_LAST_UPDATED_BY IN NUMBER,
7 P_LAST_UPDATE_LOGIN IN NUMBER,
8 P_CREATED_BY IN NUMBER,
9 P_SHIPMENT_NUM IN VARCHAR2,
10 P_VENDOR_NAME IN VARCHAR2,
11 P_VENDOR_ID IN NUMBER,
12 P_VENDOR_SITE_CODE IN VARCHAR2,
13 P_VENDOR_SITE_ID IN NUMBER,
14 P_BILL_OF_LADING IN VARCHAR2,
15 P_PACKING_SLIP IN VARCHAR2,
16 P_SHIPPED_DATE IN VARCHAR2,
17 P_FREIGHT_CARRIER_CODE IN VARCHAR2,
18 P_EXPECTED_RECEIPT_DATE IN VARCHAR2,
19 P_NUM_OF_CONTAINERS IN NUMBER,
20 P_WAYBILL_AIRBILL_NUM IN VARCHAR2,
21 P_COMMENTS IN VARCHAR2,
22 P_PACKAGING_CODE IN VARCHAR2,
23 P_CARRIER_METHOD IN VARCHAR2,
24 P_CARRIER_EQUIPMENT IN VARCHAR2,
25 P_SPECIAL_HANDLING_CODE IN VARCHAR2,
26 P_INVOICE_NUM IN VARCHAR2,
27 P_INVOICE_DATE IN VARCHAR2,
28 P_TOTAL_INVOICE_AMOUNT IN NUMBER,
29 P_PAYMENT_TERMS_ID IN NUMBER,
30 P_HAZARD_CODE IN VARCHAR2,
31 P_FREIGHT_TERMS IN VARCHAR2,
32 P_FREIGHT_AMOUNT IN NUMBER,
33 P_CURRENCY_CODE IN VARCHAR2,
34 P_CURRENCY_CONVERSION_TYPE IN VARCHAR2,
35 P_CURRENCY_CONVERSION_RATE IN NUMBER,
36 P_CURRENCY_CONVERSION_DATE IN VARCHAR2,
37 p_gross_weight IN NUMBER,
38 p_gross_weight_uom IN VARCHAR2 ,
39 p_net_weight IN NUMBER ,
40 p_net_weight_uom IN VARCHAR2 ,
41 p_tar_weight IN NUMBER ,
42 p_tar_weight_uom IN VARCHAR2 ,
43 p_freight_bill_num IN VARCHAR2 ,
44
45 /* rcv transaction interface parameters */
46 P_QUANTITY_T IN NUMBER,
47 P_UNIT_OF_MEASURE_T IN VARCHAR2,
48 P_ITEM_ID_T IN NUMBER,
49 P_ITEM_REVISION_T IN VARCHAR2,
50 P_SHIP_TO_LOCATION_CODE_T IN VARCHAR2,
51 P_SHIP_TO_ORG_ID_T IN NUMBER,
52 P_PO_HEADER_ID_T IN NUMBER,
53 P_PO_REVISION_NUM_T IN NUMBER,
54 P_PO_LINE_ID_T IN NUMBER,
55 P_PO_LINE_LOCATION_ID_T IN NUMBER,
56 P_PO_UNIT_PRICE_T IN NUMBER,
57 P_PACKING_SLIP_T IN VARCHAR2,
58 P_SHIPPED_DATE_T IN VARCHAR2,
59 P_EXPECTED_RECEIPT_DATE_T IN VARCHAR2,
60 P_NUM_OF_CONTAINERS_T IN NUMBER,
61 P_VENDOR_ITEM_NUM_T IN VARCHAR2,
62 P_VENDOR_LOT_NUM_T IN VARCHAR2,
63 P_COMMENTS_T IN VARCHAR2,
64 P_TRUCK_NUM_T IN VARCHAR2,
65 P_CONTAINER_NUM_T IN VARCHAR2,
66 P_DELIVER_TO_LOCATION_CODE_T IN VARCHAR2,
67 P_BARCODE_LABEL_T IN VARCHAR2,
68 P_COUNTRY_OF_ORIGIN_CODE_T IN VARCHAR2,
69 P_DOCUMENT_LINE_NUM_T IN NUMBER,
70 P_DOCUMENT_SHIPMENT_LINE_NUM_T IN NUMBER,
71 p_error_code IN OUT NOCOPY VARCHAR2,
72 p_error_message IN OUT NOCOPY VARCHAR2,
73 P_PAYMENT_TERMS_NAME IN VARCHAR2,
74 P_OPERATING_UNIT_ID IN NUMBER,
75 P_PO_RELEASE_ID IN NUMBER,
76 p_tax_amount IN VARCHAR2,
77 p_license_plate_number in varchar2,
78 p_lpn_group_id in number) --mji
79 IS
80
81 x_count number := 0;
82 h_count number := 0;
83 x_pla_count number := 0;
84 x_progress varchar2(3) := '000';
85 l_org_id number;
86 l_iface_txn_id number := 0; /* RTI.INTERFACE_TRANSACTION_ID */
87 l_header_id number := 0; /* RHI.HEADER_INTERFACE_ID */
88 l_transaction_type varchar2(15);
89 l_auto_transact_code varchar2(15);
90 l_quantity_invoiced number := 0;
91 l_buyer_id number;
92 l_ItemType VARCHAR2(100) := 'POSASNIB';
93 l_ItemKey VARCHAR2(100);
94 k NUMBER := 1;
95 x_note_count number := 0;
96 l_primary_unit_of_measure varchar2(25);
97 l_item_id NUMBER;
98 l_item_revision PO_LINES_ALL.ITEM_REVISION%TYPE;
99 l_converted_qty NUMBER;
100 x_ship_to_location_id NUMBER;
101 l_supplier_username VARCHAR2(80);
102 l_supplier_displayname VARCHAR2(100);
103
104 cursor dis_details_cur(linelocid in number) is
105 select WIP_ENTITY_ID ,
106 WIP_LINE_ID ,
107 WIP_OPERATION_SEQ_NUM ,
108 PO_DISTRIBUTION_ID
109 from po_distributions
110 where line_location_id = linelocid;
111
112 dis_details_rec dis_details_cur%rowtype;
113
114 BEGIN
115
116 x_progress := '010' ;
117
118 -- Get org context for the PO
119 BEGIN
120 select org_id
121 into l_org_id
122 from po_headers_all
123 where po_header_id = p_po_header_id_t;
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 raise_application_error(-20001,'Org Id not found for ' || to_char(p_po_header_id_t));
127 END;
128
129 /* We need to set the org context because the user could
130 ** create a single ASN out of multiple POs each belonging
131 ** to different operating unit. We get some stuff from
132 ** some striped tables later so we set the org context.
133 */
134
135 if l_org_id is not null then
136 fnd_client_info.set_org_context(to_char(l_org_id));
137 end if;
138
139 x_progress := '015' ;
140
141 select ship_to_location_id
142 into x_ship_to_location_id
143 from po_line_locations_all
144 where line_location_id = P_PO_LINE_LOCATION_ID_T;
145
146 /* Insert into RHI only if a record for the same ship_to_org
147 and ship_to_location is not already inserted for the group id */
148 /* Commented out ship_to_location */
149
150 select count(*)
151 into x_count
152 from rcv_headers_interface
153 where ship_to_organization_id = p_ship_to_org_id_t
154 --and location_id = x_ship_to_location_id
155 and vendor_id = P_VENDOR_ID
156 and vendor_site_id = P_VENDOR_SITE_ID
157 and group_id = p_group_id
158 and shipment_num = p_shipment_num;
159
160
161 if x_count < 1 then
162
163 x_progress := '020' ;
164
165 SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
166 INTO l_header_id
167 from dual;
168
169 x_progress := '030' ;
170
171 insert into rcv_headers_interface
172 (HEADER_INTERFACE_ID ,
173 GROUP_ID ,
174 PROCESSING_STATUS_CODE ,
175 -- PROCESSING_REQUEST_ID ,
176 RECEIPT_SOURCE_CODE ,
177 TRANSACTION_TYPE ,
178 LAST_UPDATE_DATE ,
179 LAST_UPDATED_BY ,
180 LAST_UPDATE_LOGIN ,
181 CREATION_DATE ,
182 CREATED_BY ,
183 -- LOCATION_CODE ,
184 -- LOCATION_ID ,
185 SHIP_TO_ORGANIZATION_ID ,
186 VENDOR_ID ,
187 VENDOR_SITE_ID ,
188 SHIPPED_DATE ,
189 ASN_TYPE ,
190 SHIPMENT_NUM ,
191 EXPECTED_RECEIPT_DATE ,
192 PACKING_SLIP ,
193 WAYBILL_AIRBILL_NUM ,
194 BILL_OF_LADING ,
195 FREIGHT_CARRIER_CODE ,
196 FREIGHT_TERMS ,
197 NUM_OF_CONTAINERS ,
198 COMMENTS ,
199 CARRIER_METHOD ,
200 CARRIER_EQUIPMENT ,
201 PACKAGING_CODE ,
202 SPECIAL_HANDLING_CODE ,
203 INVOICE_NUM ,
204 INVOICE_DATE ,
205 TOTAL_INVOICE_AMOUNT ,
206 FREIGHT_AMOUNT ,
207 TAX_NAME ,
208 TAX_AMOUNT ,
209 CURRENCY_CODE ,
210 CONVERSION_RATE_TYPE ,
211 CONVERSION_RATE ,
212 CONVERSION_RATE_DATE ,
213 PAYMENT_TERMS_ID ,
214 PAYMENT_TERMS_NAME ,
215 VALIDATION_FLAG
216 )
217 VALUES
218 (
219 l_header_id ,
220 P_GROUP_ID ,
221 'PENDING' ,
222 -- P_GROUP_ID ,
223 'VENDOR' ,
224 'NEW' ,
225 sysdate ,
226 P_LAST_UPDATED_BY ,
227 P_LAST_UPDATE_LOGIN ,
228 sysdate ,
229 P_CREATED_BY ,
230 -- P_SHIP_TO_LOCATION_CODE_T ,
231 -- x_ship_to_location_id ,
232 P_SHIP_TO_ORG_ID_T ,
233 P_VENDOR_ID ,
234 P_VENDOR_SITE_ID ,
235 to_date(P_SHIPPED_DATE,'YYYY-MM-DD'),
236 decode(P_INVOICE_NUM, NULL, 'ASN', 'ASBN'),
237 P_SHIPMENT_NUM ,
238 to_date(P_EXPECTED_RECEIPT_DATE,'YYYY-MM-DD'),
239 P_PACKING_SLIP ,
240 P_WAYBILL_AIRBILL_NUM ,
241 P_BILL_OF_LADING ,
242 P_FREIGHT_CARRIER_CODE ,
243 P_FREIGHT_TERMS ,
244 P_NUM_OF_CONTAINERS ,
245 P_COMMENTS ,
246 P_CARRIER_METHOD ,
247 P_CARRIER_EQUIPMENT ,
248 P_PACKAGING_CODE ,
249 P_SPECIAL_HANDLING_CODE ,
250 P_INVOICE_NUM ,
251 to_date(P_INVOICE_DATE,'YYYY-MM-DD'),
252 P_TOTAL_INVOICE_AMOUNT ,
253 P_FREIGHT_AMOUNT ,
254 null , /* TAX_NAME */
255 p_tax_amount , /* TAX_AMOUNT */
256 P_CURRENCY_CODE ,
257 P_CURRENCY_CONVERSION_TYPE ,
258 P_CURRENCY_CONVERSION_RATE ,
259 to_date(P_CURRENCY_CONVERSION_DATE,'YYYY-MM-DD'),
260 P_PAYMENT_TERMS_ID ,
261 P_PAYMENT_TERMS_NAME ,
262 'Y' );
263
264 ELSE
265
266 SELECT header_interface_id
267 into l_header_id
268 from rcv_headers_interface
269 where ship_to_organization_id = p_ship_to_org_id_t
270 --and location_id = x_ship_to_location_id
271 and vendor_id = P_VENDOR_ID
272 and vendor_site_id = P_VENDOR_SITE_ID
273 and group_id = p_group_id
274 and shipment_num = p_shipment_num;
275 end if;
276
277 x_progress := '040' ;
278
279
280 /* Get the values of some of the columns which were not
281 ** passed as parameters
282 */
283
284 -- Get transaction type
285 /* We need to join with po_location_associations because
286 ** if the PO is for OSP and if the WIP job has two
287 ** OSP operations in sequence then we want the first OSP
288 ** vendor to actually ship the goods to the second OSA
289 ** vendor but the receipt and delivery should be recorded
290 ** in the buyer's system. In order for the receipt and
291 ** delivery to be done automatically for such cases we
292 ** need to have a transaction type of RECEIVE instead of
293 ** SHIP with a auto transact code of DELIVER.
294 */
295
296 select count(*)
297 into x_pla_count
298 from po_location_associations PLA
299 where pla.location_id =
300 (select location_id from hr_locations_all
301 where location_code = P_SHIP_TO_LOCATION_CODE_T) and
302 pla.vendor_id is not null and pla.vendor_site_id is not null;
303
304 x_progress := '050' ;
305 if x_pla_count > 0 then
306 l_transaction_type := 'RECEIVE';
307 l_auto_transact_code := 'DELIVER';
308
309 /* Since we are here we know that we are OSP type
310 ** of a PO. So we will have only one distribution
311 ** To be on the safe side lets get the wip details
312 ** through a cursor.
313 */
314
315 x_progress := '060' ;
316 open dis_details_cur(p_po_line_location_id_t);
317 fetch dis_details_cur into dis_details_rec;
318 close dis_details_cur;
319
320 /* We don't close the dis_details_cur cursor over here because
321 ** this procedure will be called for each shipment. Each shipment
322 ** could have multiple distributions.
323 ** For the purpose of setting the auto_transact_code and
324 ** transaction_type, one record is sufficient but we need to
325 ** start the WIP workflow for each distribution.
326 */
327
328 else
329 x_progress := '070' ;
330 l_transaction_type := 'SHIP';
331 l_auto_transact_code := 'SHIP';
332 end if;
333
334 -- Get quantity invoiced
335 x_progress := '080' ;
336 l_quantity_invoiced := POS_QUANTITIES_S.get_invoice_qty
337 (P_PO_LINE_LOCATION_ID_T,
338 P_UNIT_OF_MEASURE_T,
339 P_ITEM_ID_T,
340 P_QUANTITY_T);
341
342 x_progress := '090' ;
343 select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
344 into l_iface_txn_id
345 from dual;
346
347 x_progress := '100' ;
348
349 select unit_meas_lookup_code,
350 item_id,
351 item_revision
352 into l_primary_unit_of_measure,
353 l_item_id,
354 l_item_revision
355 from po_lines_all
356 where po_line_id = P_PO_LINE_ID_T;
357
358 po_uom_s.uom_convert(P_QUANTITY_T, P_UNIT_OF_MEASURE_T, l_item_id,
359 l_primary_unit_of_measure, l_converted_qty);
360
361
362
363 insert into rcv_transactions_interface
364 ( INTERFACE_TRANSACTION_ID ,
365 HEADER_INTERFACE_ID ,
366 GROUP_ID ,
367 TRANSACTION_TYPE ,
368 TRANSACTION_DATE ,
369 PROCESSING_STATUS_CODE ,
370 PROCESSING_MODE_CODE ,
371 TRANSACTION_STATUS_CODE ,
372 AUTO_TRANSACT_CODE ,
373 RECEIPT_SOURCE_CODE ,
374 SOURCE_DOCUMENT_CODE ,
375 PO_HEADER_ID ,
376 PO_LINE_ID ,
377 PO_LINE_LOCATION_ID ,
378 QUANTITY ,
379 PRIMARY_QUANTITY ,
380 UNIT_OF_MEASURE ,
381 PRIMARY_UNIT_OF_MEASURE ,
382 LAST_UPDATE_DATE ,
383 LAST_UPDATED_BY ,
384 LAST_UPDATE_LOGIN ,
385 CREATION_DATE ,
386 CREATED_BY ,
387 ITEM_ID ,
388 ITEM_REVISION ,
389 EXPECTED_RECEIPT_DATE ,
390 COMMENTS ,
391 BARCODE_LABEL ,
392 CONTAINER_NUM ,
393 COUNTRY_OF_ORIGIN_CODE ,
394 VENDOR_ITEM_NUM ,
395 VENDOR_LOT_NUM ,
396 TRUCK_NUM ,
397 NUM_OF_CONTAINERS ,
398 PACKING_SLIP ,
399 VALIDATION_FLAG ,
400 WIP_ENTITY_ID ,
401 WIP_LINE_ID ,
402 WIP_OPERATION_SEQ_NUM ,
403 PO_DISTRIBUTION_ID ,
404 DOCUMENT_LINE_NUM ,
405 DOCUMENT_SHIPMENT_LINE_NUM ,
406 VENDOR_ID ,
407 VENDOR_SITE_ID ,
408 QUANTITY_INVOICED ,
409 SHIP_TO_LOCATION_CODE ,
410 SHIP_TO_LOCATION_ID ,
411 PO_RELEASE_ID,
412 license_plate_number,
413 lpn_group_id)
414 values
415 ( l_iface_txn_id ,
416 l_header_id ,
417 P_GROUP_ID ,
418 l_transaction_type ,
419 sysdate ,
420 'PENDING' ,
421 'BATCH' ,
422 'RUNNING',
423 l_auto_transact_code ,
424 'VENDOR' ,
425 'PO' ,
426 P_PO_HEADER_ID_T ,
427 P_PO_LINE_ID_T ,
428 P_PO_LINE_LOCATION_ID_T ,
429 P_QUANTITY_T ,
430 l_converted_qty ,
431 P_UNIT_OF_MEASURE_T ,
432 l_primary_unit_of_measure ,
433 sysdate ,
434 P_LAST_UPDATED_BY ,
435 P_LAST_UPDATE_LOGIN ,
436 sysdate ,
437 P_CREATED_BY ,
438 P_ITEM_ID_T ,
439 l_item_revision ,
440 to_date(P_EXPECTED_RECEIPT_DATE_T,'YYYY-MM-DD') ,
441 P_COMMENTS_T ,
442 P_BARCODE_LABEL_T ,
443 P_CONTAINER_NUM_T ,
444 P_COUNTRY_OF_ORIGIN_CODE_T ,
445 P_VENDOR_ITEM_NUM_T ,
446 P_VENDOR_LOT_NUM_T ,
447 P_TRUCK_NUM_T ,
448 P_NUM_OF_CONTAINERS_T ,
449 P_PACKING_SLIP_T ,
450 'Y' ,
451 dis_details_rec.WIP_ENTITY_ID ,
452 dis_details_rec.WIP_LINE_ID ,
453 dis_details_rec.WIP_OPERATION_SEQ_NUM ,
454 dis_details_rec.PO_DISTRIBUTION_ID ,
455 P_DOCUMENT_LINE_NUM_T ,
456 P_DOCUMENT_SHIPMENT_LINE_NUM_T ,
457 P_VENDOR_ID ,
458 P_VENDOR_SITE_ID ,
459 l_quantity_invoiced ,
460 P_SHIP_TO_LOCATION_CODE_T ,
461 x_ship_to_location_id ,
462 P_PO_RELEASE_ID,
463 p_license_plate_number,
464 p_lpn_group_id);
465
466
467
468 /* See comments above related to WIP jobs and auto_transact_code */
469 x_progress := '110' ;
470 OPEN dis_details_cur(p_po_line_location_id_t);
471 LOOP
472 x_progress := '120' ;
473 fetch dis_details_cur into dis_details_rec;
474 exit when dis_details_cur%notfound;
475
476 /* the wip workflow needs to be called only for wip jobs */
477 IF dis_details_rec.wip_entity_id is not null THEN
478 x_progress := '130' ;
479 wip_osp_shp_i_wf.StartWFProcToAnotherSupplier
480 ( dis_details_rec.po_distribution_id ,
481 P_QUANTITY_T ,
482 P_UNIT_OF_MEASURE_T ,
483 to_date(P_SHIPPED_DATE,'YYYY-MM-DD'),
484 to_date(P_EXPECTED_RECEIPT_DATE,'YYYY-MM-DD'),
485 P_PACKING_SLIP_T ,
486 P_WAYBILL_AIRBILL_NUM ,
487 p_bill_of_lading ,
488 p_packaging_code ,
489 p_num_of_containers_t ,
490 p_gross_weight ,
491 p_gross_weight_uom ,
492 p_net_weight ,
493 p_net_weight_uom ,
494 p_tar_weight ,
495 p_tar_weight_uom ,
496 null, /* p_hazard_class */
497 null, /* p_hazard_code */
498 null, /* p_hazard_desc */
499 p_special_handling_code ,
500 p_freight_carrier_code ,
501 p_freight_terms ,
502 p_carrier_equipment ,
503 p_carrier_method ,
504 p_freight_bill_num ,
505 null, /*p_receipt_num */
506 null /* p_ussgl_txn_code */
507 );
508 END IF;
509 END LOOP;
510 x_progress := '140' ;
511 CLOSE dis_details_cur;
512
513 /* at this stage we have reached end of pos_create_asn procedure
514 * if no error has happened till now then we should send the
515 * asn creation notification to the buyer
516 */
517
518 select agent_id
519 into l_buyer_id
520 from po_headers_all
521 where po_header_id = p_po_header_id_t;
522
523 select count(*)
524 into x_note_count
525 from rcv_transactions_interface
526 where header_interface_id = l_header_id;
527
528 k := k + x_note_count;
529
530 WF_DIRECTORY.GetUserName( 'FND_USR',
531 P_LAST_UPDATED_BY,
532 l_supplier_username,
533 l_supplier_displayname);
534
535 /* Commenting out the Workflow Call since Create ASN will use POSASNNB
536 for sending notifications to Buyers
537
538 l_ItemKey := 'POS_CREATE_ASN' || to_char(l_header_id) || '-' || to_char(k);
539
540 wf_engine.createProcess(ItemType => l_ItemType,
541 ItemKey => l_ItemKey,
542 Process => 'BUYER_NOTIFICATION');
543
544 wf_engine.SetItemAttrNumber(itemtype => l_ItemType,
545 itemkey => l_ItemKey,
546 aname => 'BUYER_USER_ID',
547 avalue => l_buyer_id);
548
549 wf_engine.SetItemAttrText(itemtype => l_ItemType,
550 itemkey => l_ItemKey,
551 aname => 'SHIPMENT_NUM',
552 avalue => P_SHIPMENT_NUM);
553
554 wf_engine.SetItemAttrDate(itemtype => l_ItemType,
555 itemkey => l_ItemKey,
556 aname => 'SHIP_DATE',
557 avalue => to_date(P_SHIPPED_DATE,'YYYY-MM-DD'));
558
559 wf_engine.SetItemAttrDate(itemtype => l_ItemType,
560 itemkey => l_ItemKey,
561 aname => 'EXPECTED_RECEIPT_DATE',
562 avalue => to_date(P_EXPECTED_RECEIPT_DATE,'YYYY-MM-DD'));
563
564 wf_engine.SetItemAttrNumber(itemtype => l_ItemType,
565 itemkey => l_ItemKey,
566 aname => 'SUPPLIER_ID',
567 avalue => P_VENDOR_ID);
568
569 wf_engine.SetItemAttrText(itemtype => l_ItemType,
570 itemkey => l_ItemKey,
571 aname => 'SUPPLIER',
572 avalue => P_VENDOR_NAME);
573
574 wf_engine.SetItemAttrText(itemtype => l_ItemType,
575 itemkey => l_ItemKey,
576 aname => 'SUPPLIER_USER_NAME',
577 avalue => l_supplier_username);
578
579 wf_engine.StartProcess(ItemType => l_ItemType,
580 ItemKey => l_ItemKey );
581
582 */
583
584 EXCEPTION
585
586 WHEN OTHERS THEN
587
588 p_ERROR_CODE := 'Y';
589 p_ERROR_MESSAGE := x_progress||':'||sqlcode ||':'||sqlerrm(sqlcode);
590
591 END create_asn_iface;
592
593 FUNCTION getAvailableShipmentQuantity (p_lineLocationID IN NUMBER)
594 RETURN NUMBER IS
595 v_availableQuantity NUMBER;
596 v_tolerableQuantity NUMBER;
597 v_unitOfMeasure VARCHAR2(25);
598 x_progress VARCHAR2(3);
599
600 BEGIN
601
602 x_progress := '001';
603
604 getShipmentQuantity( p_lineLocationID,
605 v_availableQuantity,
606 v_tolerableQuantity,
607 v_unitOfMeasure);
608
609 RETURN v_availableQuantity;
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 po_message_s.sql_error('getAvailableShipmentQuantity', x_progress, sqlcode);
614 RAISE;
615
616 END getAvailableShipmentQuantity;
617
618 FUNCTION getTolerableShipmentQuantity(p_lineLocationID IN NUMBER)
619 RETURN NUMBER IS
620 v_availableQuantity NUMBER;
621 v_tolerableQuantity NUMBER;
622 v_unitOfMeasure VARCHAR2(25);
623 x_progress VARCHAR2(3);
624
625 BEGIN
626
627 x_progress := '001';
628
629 getShipmentQuantity( p_lineLocationID,
630 v_availableQuantity,
631 v_tolerableQuantity,
632 v_unitOfMeasure);
633
634 RETURN v_tolerableQuantity;
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 po_message_s.sql_error('getTolerableShipmentQuantity', x_progress, sqlcode);
639 RAISE;
640
641 END getTolerableShipmentQuantity;
642
643 PROCEDURE getShipmentQuantity ( p_line_location_id IN NUMBER,
644 p_available_quantity IN OUT NOCOPY NUMBER,
645 p_tolerable_quantity IN OUT NOCOPY NUMBER,
646 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
647
648 x_progress VARCHAR2(3) := NULL;
649 x_quantity_ordered NUMBER := 0;
650 x_quantity_received NUMBER := 0;
651 x_quantity_shipped NUMBER := 0;
652 x_interface_quantity NUMBER := 0; /* in primary_uom */
653 x_quantity_cancelled NUMBER := 0;
654 x_qty_rcv_tolerance NUMBER := 0;
655 x_qty_rcv_exception_code VARCHAR2(26);
656 x_po_uom VARCHAR2(26);
657 x_item_id NUMBER;
658 x_primary_uom VARCHAR2(26);
659 x_interface_qty_in_po_uom NUMBER := 0;
660
661 BEGIN
662
663 x_progress := '005';
664
665
666 /*
667 ** Get PO quantity information.
668 */
669
670 SELECT nvl(pll.quantity, 0),
671 nvl(pll.quantity_received, 0),
672 nvl(pll.quantity_shipped, 0),
673 nvl(pll.quantity_cancelled,0),
674 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
675 pll.qty_rcv_exception_code,
676 pl.item_id,
677 pl.unit_meas_lookup_code
678 INTO x_quantity_ordered,
679 x_quantity_received,
680 x_quantity_shipped,
681 x_quantity_cancelled,
682 x_qty_rcv_tolerance,
683 x_qty_rcv_exception_code,
684 x_item_id,
685 x_po_uom
686 FROM po_line_locations_all pll,
687 po_lines_all pl
688 WHERE pll.line_location_id = p_line_location_id
689 AND pll.po_line_id = pl.po_line_id;
690
691
692 x_progress := '010';
693
694 /*
695 ** Get any unprocessed receipt or match transaction against the
696 ** PO shipment. x_interface_quantity is in primary uom.
697 **
698 ** The min(primary_uom) is neccessary because the
699 ** select may return multiple rows and we only want one value
700 ** to be returned. Having a sum and min group function in the
701 ** select ensures that this sql statement will not raise a
702 ** no_data_found exception even if no rows are returned.
703 */
704
705 SELECT nvl(sum(primary_quantity),0),
706 min(primary_unit_of_measure)
707 INTO x_interface_quantity,
708 x_primary_uom
709 FROM rcv_transactions_interface
710 WHERE processing_status_code = 'PENDING'
711 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
712 AND po_line_location_id = p_line_location_id;
713
714 IF (x_interface_quantity = 0) THEN
715
716 /*
717 ** There is no unprocessed quantity. Simply set the
718 ** x_interface_qty_in_po_uom to 0. There is no need for uom
719 ** conversion.
720 */
721
722 x_interface_qty_in_po_uom := 0;
723
724 ELSE
725
726 /*
727 ** There is unprocessed quantity. Convert it to the PO uom
728 ** so that the available quantity can be calculated in the PO uom
729 */
730
731 x_progress := '015';
732 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
733 x_po_uom, x_interface_qty_in_po_uom);
734
735 END IF;
736
737 /*
738 ** Calculate the quantity available to be received.
739 */
740
741 p_available_quantity := x_quantity_ordered - x_quantity_received - x_quantity_shipped -
742 x_quantity_cancelled - x_interface_qty_in_po_uom;
743
744 /*
745 ** p_available_quantity can be negative if this shipment has been over
746 ** received. In this case, the available quantity that needs to be passed
747 ** back should be 0.
748 */
749
750 IF (p_available_quantity < 0) THEN
751 p_available_quantity := 0;
752 END IF;
753
754 /*
755 ** Calculate the maximum quantity that can be received allowing for
756 ** tolerance.
757 */
758
759 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
760 x_quantity_received - x_quantity_shipped - x_quantity_cancelled -
761 x_interface_qty_in_po_uom;
762
763 /*
764 ** p_tolerable_quantity can be negative if this shipment has been over
765 ** received. In this case, the tolerable quantity that needs to be passed
766 ** back should be 0.
767 */
768
769 IF (p_tolerable_quantity < 0) THEN
770 p_tolerable_quantity := 0;
771 END IF;
772
773 /*
774 ** Return the PO unit of measure
775 */
776 p_unit_of_measure := x_po_uom;
777
778 EXCEPTION
779
780 WHEN OTHERS THEN
781
782 po_message_s.sql_error('getShipmentQuantity', x_progress, sqlcode);
783
784 RAISE;
785
786 END getShipmentQuantity;
787
788
789
790 /* procedure added to get converted quantity based on new UOM */
791
792 PROCEDURE getConvertedQuantity ( p_line_location_id IN NUMBER,
793 p_available_quantity IN NUMBER,
794 p_new_unit_of_measure IN VARCHAR2,
795 p_converted_quantity OUT NOCOPY NUMBER ) IS
796
797 /* p_available_quantity is in new UOM */
798
799 x_converted_quantity NUMBER := 0;
800 x_po_uom VARCHAR2(26);
801 x_item_id NUMBER;
802
803 BEGIN
804
805 SELECT pl.item_id,
806 pl.unit_meas_lookup_code
807 INTO x_item_id,
808 x_po_uom
809 FROM po_line_locations_all pll,
810 po_lines_all pl
811 WHERE pll.line_location_id = p_line_location_id
812 AND pll.po_line_id = pl.po_line_id;
813
814
815 IF (x_po_uom = p_new_unit_of_measure) THEN
816
817 p_converted_quantity := p_available_quantity;
818
819 ELSE
820
821 po_uom_s.uom_convert(p_available_quantity, p_new_unit_of_measure, x_item_id,
822 x_po_uom, x_converted_quantity);
823
824 p_converted_quantity := x_converted_quantity;
825
826 END IF;
827
828
829 END getConvertedQuantity;
830
831 /* end of procedure added to get converted quantity based on new UOM */
832
833
834 PROCEDURE callPreProcessor(p_groupId in number) IS
835
836 l_org_id number;
837 l_po_header_id number;
838
839 begin
840
841 -- All PO's for a particular group id should have the same org_id
842 select max(po_header_id)
843 into l_po_header_id
844 from rcv_transactions_interface rti, rcv_headers_interface rhi
845 where rhi.group_id = p_groupId
846 and rhi.header_interface_id = rti.header_interface_id
847 group by rti.header_interface_id;
848
849 select org_id
850 into l_org_id
851 from po_headers_all
852 where po_header_id = l_po_header_id;
853
854 fnd_client_info.set_org_context(to_char(l_org_id));
855
856 rcv_shipment_object_sv.create_object (p_groupId);
857
858
859 exception
860
861 when others then
862 raise;
863
864 end callPreProcessor;
865
866
867 PROCEDURE VALIDATE_FREIGHT_CARRIER (
868 p_organization_id IN NUMBER,
869 p_freight_code IN VARCHAR2,
870 p_count OUT NOCOPY NUMBER
871 ) IS
872
873 l_count number;
874
875 begin
876
877 select count(*)
878 into l_count
879 from ORG_FREIGHT
880 where
881 freight_code = p_freight_code and
882 organization_id = p_organization_id;
883
884 p_count := l_count;
885
886 end VALIDATE_FREIGHT_CARRIER;
887
888
889
890 END POS_CREATE_ASN;
891