Advance Shipment Notice

PO: Advance Shipment Notice:-
------------------------------------

Contributed By:- Dasaradhi Reddy

There is some functionlity called Advance Shipment Notice in 11i. When we raise a Purchasing Order and before vendor/supplier send the material to us, he can send the advance shipment notice to us.

The following scripts is design for the Advance Shipment Notice concept.

This script is tested on 11.5.10 Instance.

I thank Dasaradhi Reddy for this script.

set serveroutput on;

declare
CURSOR C_GET_IDS IS
SELECT distinct
ph.po_header_id po_header_id
,pl.po_line_id po_line_id
,pll.line_location_id line_location_id
,msi.concatenated_segments item_number
FROM
po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_distributions_all pd
,mtl_system_items_kfv msi
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pd.line_location_id
AND ph.authorization_status = 'APPROVED'
AND nvl(ph.cancel_flag,'N') = 'N'
AND nvl(pl.cancel_flag,'N') = 'N'
AND nvl(pll.cancel_flag,'N') = 'N'
and msi.organization_id = pll.ship_to_organization_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = 1884 -- Organization Id
AND ph.segment1 = '4467' -- PO NUMBER
and msi.concatenated_segments in ('TEST110' , 'TEST112'); -- ITEMS


CURSOR c_invoices IS
SELECT
'ASNTEST' shipment_prefix -- SHIPMENT NUM PREFIX
FROM dual;


v_shipment_num VARCHAR2(30);
v_vendor_id NUMBER(15);
v_vendor_site_id NUMBER(15);
v_currency_code VARCHAR2(3);
v_inventory_organization_id NUMBER(15);
v_unit_meas_lookup_code po_lines.unit_meas_lookup_code%type;
v_item_id po_lines.item_id%type;
v_item_description po_lines.item_description%type;
v_ship_to_location_id po_line_locations.ship_to_location_id%type;
v_item_num mtl_system_items.segment1%type;
v_primary_uom mtl_uom_conversions_view.primary_unit_of_measure%type;
v_primary_uom_qty NUMBER(15);
v_document_num po_headers.segment1%type;
v_document_line_num NUMBER(15);
v_shipment_line_num NUMBER(15);
v_location_code hr_locations.location_code%type;
v_temp NUMBER(15):= 1;
temp_id NUMBER(15);
temp_id2 NUMBER(15);
v_currency_conversion_type varchar2(30);
v_currency_conversion_rate number;
v_currency_conversion_date date;

v_lot_number varchar2(80);
v_cur_seq number := 6583 ;
v_start_seq varchar2(80);
v_ser_number varchar2(80);


l_shipment_count NUMBER := 150;

BEGIN

dbms_output.enable(100000);

FOR v_get_ids IN c_get_ids
LOOP

--dbms_output.put_line('First updating Details Records');


FOR i in 1..l_shipment_count -- NO of SHIPMENTS to be inserted
LOOP

FOR v_invoices IN c_invoices
LOOP
v_shipment_num := v_invoices.shipment_prefix||'-'||to_char(sysdate,'dd-mm-hh-mi-yy');

if v_temp is null then
v_temp:=1;
End if;

dbms_output.put_line('v_shipment_num:'||v_shipment_num);

--dbms_output.put_line('v_temp:'||v_temp);

SELECT
vendor_id
,vendor_site_id
,currency_code
,rate_type
,rate
,rate_date
INTO
v_vendor_id
,v_vendor_site_id
,v_currency_code
,v_currency_conversion_type
,v_currency_conversion_rate
,v_currency_conversion_date
FROM
po_headers_all
WHERE
po_header_id = v_get_ids.po_header_id;

/*
dbms_output.put_line('v_vendor_id:'||v_vendor_id||
',v_vendor_site_id:'||v_vendor_site_id||
',v_currency_code:'||v_currency_code||
',v_currency_conversion_type:'||v_currency_conversion_type||
',v_currency_conversion_rate:'||v_currency_conversion_rate||
',v_currency_conversion_date:'||v_currency_conversion_date);
*/
select
pll.ship_to_organization_id
into
v_inventory_organization_id
from
po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
where
nvl(ph.cancel_flag,'N') <> 'Y' and
nvl(pl.cancel_flag,'N') <> 'Y' and
nvl(pll.cancel_flag,'N') <> 'Y' and
ph.po_header_id = pl.po_header_id and
ph.po_header_id = v_get_ids.po_header_id AND
pl.po_line_id = pll.po_line_id and
rownum=1;

--dbms_output.put_line('org is:'||v_inventory_organization_id);
--dbms_output.put_line('inserting into rcv_headers_interface');

if v_temp = 1 then

dbms_output.put_line ('*********** inserting Header *************');

INSERT INTO rcv_headers_interface
(HEADER_INTERFACE_ID
,GROUP_ID
,PROCESSING_STATUS_CODE
,RECEIPT_SOURCE_CODE
,ASN_TYPE
,TRANSACTION_TYPE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,NOTICE_CREATION_DATE
,SHIPMENT_NUM
,VENDOR_ID
,VENDOR_SITE_ID
,SHIP_TO_ORGANIZATION_ID
,SHIPPED_DATE
,EXPECTED_RECEIPT_DATE
,CURRENCY_CODE
,VALIDATION_FLAG
,NUM_OF_CONTAINERS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE15
--,organization_id
)
VALUES
(RCV_HEADERS_INTERFACE_S.NEXTVAL --INTERFACE_HEADER_ID
,RCV_INTERFACE_GROUPS_S.NEXTVAL --GROUP_ID
,'PENDING' --PROCESSING_STATUS_CODE
,'VENDOR' --RECEIPT_SOURCE_CODE
,'ASN' --ASN_TYPE
,'NEW' --TRANSACTION_TYPE
,SYSDATE --LAST_UPDATE_DATE
,FND_GLOBAL.USER_ID --LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,FND_GLOBAL.USER_ID --CREATED_BY
,SYSDATE --NOTICE_CREATION_DATE
,v_shipment_num --SHIPMENT_NUM
,v_vendor_id --VENDOR_ID
,v_vendor_site_id --VENDOR_SITE_ID
,v_inventory_organization_id --SHIP_TO_ORGANIZATION_ID
,(sysdate-1) --v_invoices.departure_date --SHIPPED_DATE
,sysdate --v_invoices.final_shipment_date --EXPECTED_RECEIPT_DATE
,v_currency_code --CURRENCY_CODE
,'Y' --VALIDATTION_FLAG
,null --NUM_OF_CONTAINERS
,null --,'VEHICLES' --ATTRIBUTE_CATEGORY
,null --v_invoices.vessel_name --VESSEL_NAME
,null --v_invoices.unique_id --ATTRIBUTE15
--, 204 -- Master Organization Id
);
END IF; -- for rcv_headers_interface

--dbms_output.put_line('v_temp is '||v_temp||'Insert line');

SELECT
item_id
,unit_meas_lookup_code
INTO
v_item_id
,v_unit_meas_lookup_code
FROM
po_lines_all
WHERE
po_lines_all.po_line_id = v_get_ids.po_line_id;

SELECT
pll.ship_to_location_id
,hl.location_code
INTO
v_ship_to_location_id
,v_location_code
FROM
po_line_locations_all pll
,hr_locations hl
WHERE
line_location_id = v_get_ids.line_location_id AND
pll.ship_to_location_id = hl.location_id ;

/*
dbms_output.put_line('v_inventory_organization_id:'||v_inventory_organization_id
||' v_item_id:'||v_item_id
||' v_unit_meas_lookup_code:'||v_unit_meas_lookup_code );
*/
SELECT
primary_unit_of_measure,
1* conversion_rate
INTO
v_primary_uom,
v_primary_uom_qty
FROM MTL_UOM_CONVERSIONS_view
WHERE organization_id = v_inventory_organization_id AND
inventory_item_id = v_item_id AND
unit_of_measure = v_unit_meas_lookup_code;

--dbms_output.put_line('after getting Primary UOM');

SELECT
concatenated_segments
INTO
v_item_num
FROM
mtl_system_items_kfv
WHERE
organization_id = v_inventory_organization_id AND
inventory_item_id = v_item_id;

--dbms_output.put_line('after getting item_id'|| 'item = '|| v_item_num);

SELECT
segment1
INTO
v_document_num
FROM
po_headers_all
WHERE
po_header_id = v_get_ids.po_header_id;

SELECT
line_num
INTO
v_document_line_num
FROM
po_lines_all
WHERE
po_line_id = v_get_ids.po_line_id;

SELECT
shipment_num
INTO
v_shipment_line_num
FROM
po_line_locations_all
WHERE
line_location_id = v_get_ids.line_location_id;

-- dbms_output.put_line('After getting Document Num,Line Numa and Shipment Num');

INSERT INTO rcv_transactions_interface
(INTERFACE_TRANSACTION_ID
,GROUP_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,TRANSACTION_TYPE
,TRANSACTION_DATE
,PROCESSING_STATUS_CODE
,PROCESSING_MODE_CODE
,TRANSACTION_STATUS_CODE
,QUANTITY
,UNIT_OF_MEASURE
,ITEM_ID
,ITEM_DESCRIPTION
,AUTO_TRANSACT_CODE
,SHIP_TO_LOCATION_ID
,PRIMARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,RECEIPT_SOURCE_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,SOURCE_DOCUMENT_CODE
,PO_HEADER_ID
,PO_LINE_ID
,PO_LINE_LOCATION_ID
,EXPECTED_RECEIPT_DATE
,HEADER_INTERFACE_ID
,ITEM_NUM
,DOCUMENT_NUM
,DOCUMENT_LINE_NUM
,SHIP_TO_LOCATION_CODE
,DOCUMENT_SHIPMENT_LINE_NUM
,VALIDATION_FLAG
,LICENSE_PLATE_NUMBER
,LPN_GROUP_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,SHIP_LINE_ATTRIBUTE_CATEGORY --VEHICLES
,SHIP_LINE_ATTRIBUTE1 --AP Invoice Number
,SHIP_LINE_ATTRIBUTE2 --Unit FOB Amount
,SHIP_LINE_ATTRIBUTE3 --Unit Freight Amount
,SHIP_LINE_ATTRIBUTE4 --Unit Insurance Amount
,SHIP_LINE_ATTRIBUTE5 --Unit Interest Amount
,SHIP_LINE_ATTRIBUTE6 --Unit FOB Charge
,SHIP_LINE_ATTRIBUTE7 --Unit Contract Price
,SHIP_LINE_ATTRIBUTE8 -- INV Freight Amount
,SHIP_LINE_ATTRIBUTE15 --Unique Data ID
)
VALUES
(RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL --INTERFACE_TRANSACTION_ID
,RCV_INTERFACE_GROUPS_S.CURRVAL --GROUP_ID
,SYSDATE --LAST_UPDATE_DATE
,FND_GLOBAL.USER_ID --LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,FND_GLOBAL.USER_ID --CREATED_BY
,'SHIP' --TRANSACTION_TYPE
,SYSDATE --TRANSACTION_DATE
,'PENDING' --PROCESSING_STATUS_CODE
,'BATCH' --PROCESSING_MODE_CODE
,'PENDING' --TRANSACTION_STATUS_CODE
,1 --QUANTITY
,v_unit_meas_lookup_code --UNIT_OF_MEASURE
,v_item_id --ITEM_ID
,v_item_description --ITEM_DESCRIPTION
,'SHIP' --AUTO_TRANSACT_CODE
,v_ship_to_location_id --SHIP_TO_LOCATION_ID
,v_primary_uom_qty --PRIMARY_QUANTITY
,v_primary_uom --PRIMARY_UNIT_OF_MEASURE
,'VENDOR' --RECEIPT_SOURCE_CODE
,v_vendor_id --VENDOR_ID
,v_vendor_site_id --VENDOR_SITE_ID
,'PO' --SOURCE_DOCUMENT_CODE
,v_get_ids.po_header_id --PO_HEADER_ID
,v_get_ids.po_line_id --PO_LINE_ID
,v_get_ids.line_location_id --PO_LINE_LOCATION_ID
,(sysdate -1) --EXPECTED_RECEIPT_DATE
,RCV_HEADERS_INTERFACE_S.CURRVAL --HEADER_INTERFACE_ID
,v_item_num --ITEM_NUM
,v_document_num --DOCUMENT_NUM
,v_document_line_num --DOCUMENT_LINE_NUM
,v_location_code --SHIP_TO_LOCATION_CODE
,v_shipment_line_num --DOCUMENT_SHIPMENT_LINE_NUM
,'Y' --VALIDATION_FLAG
,v_shipment_num --LICENSE_PLATE_NUMBER
,RCV_INTERFACE_GROUPS_S.CURRVAL --LPN_GROUP_ID
,v_currency_code --CURRENCY_CODE
,v_currency_conversion_type --CURRENCY_CONVERSION_TYPE
,v_currency_conversion_rate --CURRENCY_CONVERSION_RATE
,v_currency_conversion_date --CURRENCY_CONVERSION_DATE
,null
,null --SHIP_LINE_ATTRIBUTE1 --AP Invoice Number
,null --SHIP_LINE_ATTRIBUTE2 --Unit FOB Amount
,null
,null --SHIP_LINE_ATTRIBUTE4 --Unit Insurance Amount
,null --SHIP_LINE_ATTRIBUTE5 --Unit Interest Amount
,null
,null
,null --SHIP_LINE_ATTRIBUTE8 --INV Freight Amount
,null
);

dbms_output.put_line('After Populating RCV_TRANSACTIONS_INTERFACE');

IF v_temp = 1 THEN
INSERT INTO wms_lpn_interface
(LICENSE_PLATE_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,ORGANIZATION_ID
,SOURCE_GROUP_ID
)
VALUES
(v_shipment_num --LICENSE_PLATE_NUMBER
,SYSDATE --LAST_UPDATE_DATE
,FND_GLOBAL.USER_ID --LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,FND_GLOBAL.USER_ID --CREATED_BY
,v_inventory_organization_id --ORGANIZATION_ID
,RCV_INTERFACE_GROUPS_S.CURRVAL --SOURCE_GROUP_ID
);
dbms_output.put_line('After inserting into WMS_LPN_INTERFACE');
v_temp := v_temp+1;

END IF;

SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO
temp_id
FROM
dual;

dbms_output.put_line('After getting temp_id');

v_lot_number := to_char(sysdate,'MON-DD');

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LOT_NUMBER
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,PRODUCT_CODE
,PRODUCT_TRANSACTION_ID
,C_ATTRIBUTE1
)
VALUES
(temp_id --TRANSACTION_INTERFACE_ID
,SYSDATE --LAST_UPDATE_DATE
,FND_GLOBAL.USER_ID --LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,FND_GLOBAL.USER_ID --CREATED_BY
,v_lot_number --LOT_NUMBER
,1 --TRANSACTION_QUANTITY
,v_primary_uom_qty --PRIMARY_QUANTITY
,'RCV' --PRODUCT_CODE
,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
,'TEST'
);

dbms_output.put_line('After inserting into MTL_TRANSACTIONS_LOTS_INTERFACE');

SELECT
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO
temp_id2
FROM
dual;

v_cur_seq := v_cur_seq + 1;

v_start_seq := lpad(v_cur_seq ,4,'0');

v_ser_number := 'MO6584-'||'CH'||v_start_seq;

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,FM_SERIAL_NUMBER
,TO_SERIAL_NUMBER
,PRODUCT_CODE
,PRODUCT_TRANSACTION_ID
,C_ATTRIBUTE10 --Engine Number
,C_ATTRIBUTE11 --Chasis Number
,C_ATTRIBUTE13 --Production Code
)VALUES
(temp_id2 --TRANSACTION_INTERFACE_ID
,SYSDATE --LAST_UPDATE_DATE
,FND_GLOBAL.USER_ID --LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,FND_GLOBAL.USER_ID --CREATED_BY
,v_ser_number
,v_ser_number
,'RCV' --PRODUCT_CODE
,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
,null
,null
,null
);

dbms_output.put_line('After inserting into MTL_SERIAL_NUMBERS_INTERFACE');

UPDATE
MTL_TRANSACTION_LOTS_INTERFACE
SET
serial_transaction_temp_id = temp_id2
WHERE
transaction_interface_id = temp_id;

END LOOP;
END LOOP;
END LOOP;
COMMIT;-- for invoice

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR:'||substr(sqlerrm,1,250));
rollback;
END;
/