[Home] [Help]
PACKAGE BODY: APPS.POS_CREATE_ASN
Source
1 PACKAGE BODY POS_CREATE_ASN AS
2 /* $Header: POSASNTB.pls 120.4 2010/05/04 10:03:22 vchiranj ship $*/
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 l_line_location_id po_line_locations_all.LINE_LOCATION_ID%TYPE;
662 l_distribution_type po_distributions_all.distribution_type%TYPE;
663 l_matching_basis po_line_locations_all.matching_basis%TYPE;
664 l_accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE;
665 l_code_combination_id po_distributions_all.code_combination_id%TYPE;
666 l_budget_account_id po_distributions_all.budget_account_id%TYPE;
667 l_partial_funded_flag po_distributions_all.partial_funded_flag%TYPE;
668 l_unit_meas_lookup_code po_line_locations_all.unit_meas_lookup_code%TYPE;
669 l_funded_value NUMBER;
670 l_quantity_funded NUMBER;
671 l_amount_funded NUMBER;
672 l_quantity_received NUMBER;
673 l_amount_received NUMBER;
674 l_quantity_delivered NUMBER;
675 l_amount_delivered NUMBER;
676 l_quantity_billed NUMBER;
677 l_amount_billed NUMBER;
678 l_quantity_cancelled NUMBER;
679 l_amount_cancelled NUMBER;
680 l_return_status VARCHAR2(1000);
681 l_clm_flag po_doc_style_headers.clm_flag%TYPE;
682
683 BEGIN
684
685 x_progress := '005';
686
687
688 /*
689 ** Get PO quantity information.
690 */
691
692 SELECT nvl(pll.quantity, 0),
693 nvl(pll.quantity_received, 0),
694 nvl(pll.quantity_shipped, 0),
695 nvl(pll.quantity_cancelled,0),
696 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
697 pll.qty_rcv_exception_code,
698 pl.item_id,
699 pl.unit_meas_lookup_code
700 INTO x_quantity_ordered,
701 x_quantity_received,
702 x_quantity_shipped,
703 x_quantity_cancelled,
704 x_qty_rcv_tolerance,
705 x_qty_rcv_exception_code,
706 x_item_id,
707 x_po_uom
708 FROM po_line_locations_all pll,
709 po_lines_all pl
710 WHERE pll.line_location_id = p_line_location_id
711 AND pll.po_line_id = pl.po_line_id;
712
713
714 x_progress := '010';
715
716 /*
717 ** Get any unprocessed receipt or match transaction against the
718 ** PO shipment. x_interface_quantity is in primary uom.
719 **
720 ** The min(primary_uom) is neccessary because the
721 ** select may return multiple rows and we only want one value
722 ** to be returned. Having a sum and min group function in the
723 ** select ensures that this sql statement will not raise a
724 ** no_data_found exception even if no rows are returned.
725 */
726
727 SELECT nvl(sum(primary_quantity),0),
728 min(primary_unit_of_measure)
729 INTO x_interface_quantity,
730 x_primary_uom
731 FROM rcv_transactions_interface
732 WHERE processing_status_code = 'PENDING'
733 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
734 AND po_line_location_id = p_line_location_id;
735
736 l_line_location_id := p_line_location_id;
737
738 po_clm_intg_grp.get_funding_info(p_line_location_id => l_line_location_id,
739 x_distribution_type => l_distribution_type,
740 x_matching_basis => l_matching_basis,
741 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
742 x_code_combination_id => l_code_combination_id,
743 x_budget_account_id => l_budget_account_id,
744 x_partial_funded_flag => l_partial_funded_flag,
745 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
746 x_funded_value => l_funded_value,
747 x_quantity_funded => l_quantity_funded,
748 x_amount_funded => l_amount_funded,
749 x_quantity_received => l_quantity_received,
750 x_amount_received => l_amount_received,
751 x_quantity_delivered => l_quantity_delivered,
752 x_amount_delivered => l_amount_delivered,
753 x_quantity_billed => l_quantity_billed,
754 x_amount_billed => l_amount_billed,
755 x_quantity_cancelled => l_quantity_cancelled,
756 x_amount_cancelled => l_amount_cancelled,
757 x_return_status => l_return_status);
758
759 SELECT Nvl(PDSH.CLM_FLAG,'N') CLM_FLAG
760 INTO l_clm_flag
761 FROM PO_HEADERS_ALL POH,
762 PO_LINE_LOCATIONS_ALL POLL,
763 PO_DOC_STYLE_HEADERS PDSH
764 WHERE POLL.LINE_LOCATION_ID = l_line_location_id AND
765 POLL.PO_HEADER_ID = POH.PO_HEADER_ID AND
766 NVL(POH.STYLE_ID, 1) = PDSH.STYLE_ID (+) AND
767 PDSH.STATUS (+) = 'ACTIVE' AND
768 ROWNUM =1;
769
770
771 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
772 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_quantity_ordered = ' ||x_quantity_ordered);
773 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_quantity_received = ' ||x_quantity_received);
774 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_quantity_shipped = ' ||x_quantity_shipped);
775 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_quantity_cancelled = ' ||x_quantity_cancelled);
776 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_qty_rcv_tolerance = ' ||x_qty_rcv_tolerance);
777 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_qty_rcv_exception_code = ' ||x_qty_rcv_exception_code);
778 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_item_id = ' ||x_item_id);
779 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_po_uom = ' ||x_po_uom);
780 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_line_location_id = ' ||l_line_location_id);
781 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_distribution_type = ' ||l_distribution_type);
782 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_matching_basis = ' ||l_matching_basis);
783 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_accrue_on_receipt_flag = ' ||l_accrue_on_receipt_flag);
784 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_code_combination_id = ' ||l_code_combination_id);
785 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_budget_account_id = ' ||l_budget_account_id);
786 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_partial_funded_flag = ' ||l_partial_funded_flag);
787 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_unit_meas_lookup_code = ' ||l_unit_meas_lookup_code);
788 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_funded_value = ' ||l_funded_value);
789 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_quantity_funded = ' ||l_quantity_funded);
790 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_amount_funded = ' ||l_amount_funded);
791 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_quantity_received = ' ||l_quantity_received);
792 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_amount_received = ' ||l_amount_received);
793 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_quantity_delivered = ' ||l_quantity_delivered);
794 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_amount_delivered = ' ||l_amount_delivered);
795 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_quantity_billed = ' ||l_quantity_billed);
796 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_amount_billed = ' ||l_amount_billed);
797 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_quantity_cancelled = ' ||l_quantity_cancelled);
798 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_amount_cancelled = ' ||l_amount_cancelled);
799 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_return_status = ' ||l_return_status);
800 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','l_clm_flag = ' ||l_clm_flag);
801 END IF;
802
803 IF (x_interface_quantity = 0) THEN
804
805 /*
806 ** There is no unprocessed quantity. Simply set the
807 ** x_interface_qty_in_po_uom to 0. There is no need for uom
808 ** conversion.
809 */
810
811 x_interface_qty_in_po_uom := 0;
812
813 ELSE
814
815 /*
816 ** There is unprocessed quantity. Convert it to the PO uom
817 ** so that the available quantity can be calculated in the PO uom
818 */
819
820 x_progress := '015';
821 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
822 x_po_uom, x_interface_qty_in_po_uom);
823
824 END IF;
825
826 /*
827 ** Calculate the quantity available to be received.
828 */
829 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
830 p_available_quantity := l_quantity_funded - x_quantity_received - x_quantity_shipped -
831 x_quantity_cancelled - x_interface_qty_in_po_uom;
832 ELSE
833 p_available_quantity := x_quantity_ordered - x_quantity_received - x_quantity_shipped -
834 x_quantity_cancelled - x_interface_qty_in_po_uom;
835 END IF;
836
837 /*
838 ** p_available_quantity can be negative if this shipment has been over
839 ** received. In this case, the available quantity that needs to be passed
840 ** back should be 0.
841 */
842
843 IF (p_available_quantity < 0) THEN
844 p_available_quantity := 0;
845 END IF;
846
847 /*
848 ** Calculate the maximum quantity that can be received allowing for
849 ** tolerance.
850 */
851
852 IF (l_clm_flag = 'Y' and l_partial_funded_flag = 'Y') THEN
853 p_tolerable_quantity := l_quantity_funded - x_quantity_received - x_quantity_shipped -
854 x_quantity_cancelled - x_interface_qty_in_po_uom;
855 ELSE
856 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
857 x_quantity_received - x_quantity_shipped - x_quantity_cancelled -
858 x_interface_qty_in_po_uom;
859 END IF;
860
861 /*
862 ** p_tolerable_quantity can be negative if this shipment has been over
863 ** received. In this case, the tolerable quantity that needs to be passed
864 ** back should be 0.
865 */
866
867 IF (p_tolerable_quantity < 0) THEN
868 p_tolerable_quantity := 0;
869 END IF;
870
871 /*
872 ** Return the PO unit of measure
873 */
874 p_unit_of_measure := x_po_uom;
875
876 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','x_interface_qty_in_po_uom = ' ||x_interface_qty_in_po_uom);
878 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','p_available_quantity = ' ||p_available_quantity);
879 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','p_tolerable_quantity = ' ||p_tolerable_quantity);
880 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, 'pos.plsql.POS_CREATE_ASN.getShipmentQuantity.invoked','p_unit_of_measure = ' ||p_unit_of_measure);
881 END IF;
882
883 EXCEPTION
884
885 WHEN OTHERS THEN
886
887 po_message_s.sql_error('getShipmentQuantity', x_progress, sqlcode);
888
889 RAISE;
890
891 END getShipmentQuantity;
892
893
894 /* procedure added to get converted quantity based on new UOM */
895
896 PROCEDURE getConvertedQuantity ( p_line_location_id IN NUMBER,
897 p_available_quantity IN NUMBER,
898 p_new_unit_of_measure IN VARCHAR2,
899 p_converted_quantity OUT NOCOPY NUMBER ) IS
900
901 /* p_available_quantity is in new UOM */
902
903 x_converted_quantity NUMBER := 0;
904 x_po_uom VARCHAR2(26);
905 x_item_id NUMBER;
906
907 BEGIN
908
909 SELECT pl.item_id,
910 pl.unit_meas_lookup_code
911 INTO x_item_id,
912 x_po_uom
913 FROM po_line_locations_all pll,
914 po_lines_all pl
915 WHERE pll.line_location_id = p_line_location_id
916 AND pll.po_line_id = pl.po_line_id;
917
918
919 IF (x_po_uom = p_new_unit_of_measure) THEN
920
921 p_converted_quantity := p_available_quantity;
922
923 ELSE
924
925 po_uom_s.uom_convert(p_available_quantity, p_new_unit_of_measure, x_item_id,
926 x_po_uom, x_converted_quantity);
927
928 p_converted_quantity := x_converted_quantity;
929
930 END IF;
931
932
933 END getConvertedQuantity;
934
935 /* end of procedure added to get converted quantity based on new UOM */
936
937
938 PROCEDURE callPreProcessor(p_groupId in number) IS
939
940 l_org_id number;
941 l_po_header_id number;
942
943 begin
944
945 -- All PO's for a particular group id should have the same org_id
946 select max(po_header_id)
947 into l_po_header_id
948 from rcv_transactions_interface rti, rcv_headers_interface rhi
949 where rhi.group_id = p_groupId
950 and rhi.header_interface_id = rti.header_interface_id
951 group by rti.header_interface_id;
952
953 select org_id
954 into l_org_id
955 from po_headers_all
956 where po_header_id = l_po_header_id;
957
958 fnd_client_info.set_org_context(to_char(l_org_id));
959
960 rcv_shipment_object_sv.create_object (p_groupId);
961
962
963 exception
964
965 when others then
966 raise;
967
968 end callPreProcessor;
969
970
971 PROCEDURE VALIDATE_FREIGHT_CARRIER (
972 p_organization_id IN NUMBER,
973 p_freight_code IN VARCHAR2,
974 p_count OUT NOCOPY NUMBER
975 ) IS
976
977 l_count number;
978
979 begin
980
981 select count(*)
982 into l_count
983 from ORG_FREIGHT
984 where
985 freight_code = p_freight_code and
986 organization_id = p_organization_id;
987
988 p_count := l_count;
989
990 end VALIDATE_FREIGHT_CARRIER;
991
992
993
994 END POS_CREATE_ASN;
995