Advance Shipping Notification Cancel Process

Script to get the ASN line against PO:

 There are two scenarios possible when you take receipts against these ASN shipments:

•          When these ASN shipment line are received against Purchase orders, it creates duplicate records in the rcv_shipment_lines table, which shows up as an expected receipts in ASCP, though they are already been received against these corresponding Purchase orders.

•          When these ASN shipment line are received against ASN, it does not create any duplicate records in the rcv_shipment_lines table and hence unprocessed records doesn’t get piled up in Receiving tables and hence the expected receipts shows the correct picture in ASCP planning.

(SELECT rsh.shipment_header_id

  FROM mtl_supply ms,

       rcv_shipment_lines rsl,

       rcv_shipment_headers rsh,

       po_line_locations_all poll

 WHERE ms.supply_type_code = 'SHIPMENT'

   AND rsl.SHIPMENT_LINE_STATUS_CODE = 'EXPECTED'

   AND ms.shipment_line_id = rsl.shipment_line_id

   AND rsl.shipment_header_id = rsh.shipment_header_id

   AND rsh.asn_type = 'ASN'

   AND nvl(poll.closed_code, 'OPEN') in ('CLOSED FOR RECEIVING', 'CLOSED', 'FINALLY CLOSED')

   AND ms.po_line_location_id = poll.line_location_id

   AND poll.quantity <= nvl(poll.quantity_received, 0))

   AND SHIPMENT_LINE_STATUS_CODE = 'EXPECTED')

DECLARE

x_vendor_id NUMBER;

CURSOR rsls IS

SELECT * FROM RCV_SHIPMENT_LINES 

 WHERE shipment_header_id IN 

(SELECT rsh.shipment_header_id

  FROM mtl_supply ms,

       rcv_shipment_lines rsl,

       rcv_shipment_headers rsh,

       po_line_locations_all poll

 WHERE ms.supply_type_code = 'SHIPMENT'

   AND rsl.SHIPMENT_LINE_STATUS_CODE = 'EXPECTED'

   AND ms.shipment_line_id = rsl.shipment_line_id

   AND rsl.shipment_header_id = rsh.shipment_header_id

   AND rsh.asn_type = 'ASN'

   AND nvl(poll.closed_code, 'OPEN') in ('CLOSED FOR RECEIVING', 'CLOSED', 'FINALLY CLOSED')

   AND ms.po_line_location_id = poll.line_location_id

   AND poll.quantity <= nvl(poll.quantity_received, 0))

   AND SHIPMENT_LINE_STATUS_CODE = 'EXPECTED';

BEGIN

FOR c1_rec IN rsls LOOP

SELECT vendor_id

INTO x_vendor_id

  FROM po_headers_all

 WHERE po_header_id = c1_rec.po_header_id;

/*SELECT TO_DATE(end_date,'DD-MON-RR')

INTO X_TRX_DATE

    FROM org_acct_periods_v

   WHERE organization_id=c1_rec.to_organization_id

   AND STATUS= 'Open';*/

INSERT INTO RCV_TRANSACTIONS_INTERFACE 

(INTERFACE_TRANSACTION_ID,

GROUP_ID,

SHIPMENT_HEADER_ID,

SHIPMENT_LINE_ID,

SHIP_TO_LOCATION_ID,

PRIMARY_QUANTITY,-- Add

PRIMARY_UNIT_OF_MEASURE,-- Add

VENDOR_ID,

VENDOR_SITE_ID,--Add

TO_ORGANIZATION_ID,

PO_HEADER_ID,

PO_LINE_ID,

PO_LINE_LOCATION_ID,

PO_RELEASE_ID,

PROCESSING_STATUS_CODE,

TRANSACTION_STATUS_CODE,

CATEGORY_ID,-- Add

PROCESSING_MODE_CODE,

INTERFACE_SOURCE_CODE,

AUTO_TRANSACT_CODE,

TRANSACTION_TYPE,

SOURCE_DOCUMENT_CODE,

RECEIPT_SOURCE_CODE,

DESTINATION_TYPE_CODE,

CREATION_DATE,

LAST_UPDATE_DATE,

TRANSACTION_DATE,

LAST_UPDATED_BY,

CREATED_BY,

LAST_UPDATE_LOGIN,

QUANTITY,

UNIT_OF_MEASURE,

EMPLOYEE_ID,--Add

--VALIDATION_FLAG,

ROUTING_HEADER_ID,--Add

LOCATION_ID, -- Add

ITEM_ID, --Add

ITEM_DESCRIPTION, --Add

ORG_ID --Add

)

SELECT

rcv_transactions_interface_s.nextval,

RCV_INTERFACE_GROUPS_S.currval,

SHIPMENT_HEADER_ID,

SHIPMENT_LINE_ID,

SHIP_TO_LOCATION_ID,-- Add

quantity_shipped,--Add

PRIMARY_UNIT_OF_MEASURE, --Add

x_vendor_id,

NULL,--Add

TO_ORGANIZATION_ID,

PO_HEADER_ID,

PO_LINE_ID,

PO_LINE_LOCATION_ID,

'',

'PENDING',--PROCESSING_STATUS_CODE

'PENDING', --TRANSACTION_STATUS_CODE

category_id, -- add

 'BATCH',  --PROCESSING_MODE_CODE

'RCV', --INTERFACE_SOURCE_CODE

'CANCEL', --AUTO_TRANSACT_CODE

'CANCEL',  --TRANSACTION_TYPE

'PO',  --SOURCE_DOCUMENT_CODE

'VENDOR', --RECEIPT_SOURCE_CODE

 'INVENTORY', --DESTINATION_TYPE_CODE CHANGE TO ''RECEIVE'

  SYSDATE,

  SYSDATE,

  SYSDATE,

1,

1,

1,

quantity_shipped,

unit_of_measure,

employee_id,-- Add

--'Y',

routing_header_id,-- Add

deliver_to_location_id, -- Add 167

item_id,--Add

ITEM_DESCRIPTION, --Add

103 --Add

from rcv_shipment_lines where shipment_line_id = c1_rec.shipment_line_id;

END LOOP;

COMMIT;

END;

Run the >Purchasing Super User>Receiving Transaction Processor(Group_id) as parameter from RCV_TRANSACTION_INTERFACE table it should be unique for all the ASN line which are going to process at a time.