采购 接收检验入库 接口处理例子
来源:互联网 发布:淘宝店铺简单装修 编辑:程序博客网 时间:2024/05/16 09:33
--PO接收
DECLARE
--待接收采购订单
CURSOR C1 IS
SELECT PHA.VENDOR_ID
,PHA.VENDOR_SITE_ID
,PHA.CURRENCY_CODE
,PHA.RATE_TYPE
,PLA.ITEM_ID
,PLA.UNIT_PRICE
,PLLA.PO_HEADER_ID
,PLLA.PO_LINE_ID
,PLLA.LINE_LOCATION_ID
,PLLA.SHIP_TO_ORGANIZATION_ID
,PLLA.SHIP_TO_LOCATION_ID
,PLLA.RECEIVING_ROUTING_ID
,PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED TRANSACTION_QUANTITY
,PLLA.UNIT_MEAS_LOOKUP_CODE
-- ,PLLA.*
FROM PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,PO_LINE_LOCATIONS_ALL PLLA
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = 3671155
AND NVL(PLLA.APPROVED_FLAG
,'N') = 'Y'
AND NVL(PLLA.CANCEL_FLAG
,'N') = 'N'
AND NVL(PLLA.CLOSED_CODE
,'OPEN') != 'FINALLY CLOSED'
AND PLLA.SHIPMENT_TYPE IN ('STANDARD'
,'BLANKET'
,'SCHEDULED');
V_RCV_HEADERS_IFACE PO.RCV_HEADERS_INTERFACE%ROWTYPE;
V_RCV_TRANSACTION_IFACE PO.RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
V_PO_GL_PERIOD_STATUS VARCHAR2(1);
V_INV_PERIOD_STATUS VARCHAR2(1);
BEGIN
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 1027
,RESP_ID => 54872
,RESP_APPL_ID => 401);
--检查采购或总账会计期是否打开
SELECT CLOSING_STATUS
INTO V_PO_GL_PERIOD_STATUS
FROM GL_PERIOD_STATUSES_V GP
WHERE APPLICATION_ID = 101
AND SET_OF_BOOKS_ID = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')
AND CLOSING_STATUS != 'N'
AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE;
IF V_PO_GL_PERIOD_STATUS = 'C'
THEN
APP_EXCEPTION.RAISE_EXCEPTION('采购或总账会计期间未打开');
END IF;
FOR R1 IN C1
LOOP
--检查库存会计期是否打开
BEGIN
SELECT 1
INTO V_INV_PERIOD_STATUS
FROM ORG_ACCT_PERIODS_V
WHERE REC_TYPE = 'ORG_PERIOD'
AND ORGANIZATION_ID = R1.SHIP_TO_ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN START_DATE AND END_DATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('库存会计期间未打开');
END;
--接收头信息
V_RCV_HEADERS_IFACE.TRANSACTION_TYPE := 'NEW'; --ADD添加至接收 NEW新建接收
-- V_RCV_HEADERS_IFACE.RECEIPT_HEADER_ID := V_RECEIPT_HEADER_ID; 添加至接收时先获取到已接收ID
V_RCV_HEADERS_IFACE.LAST_UPDATE_DATE := SYSDATE;
V_RCV_HEADERS_IFACE.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_RCV_HEADERS_IFACE.CREATION_DATE := SYSDATE;
V_RCV_HEADERS_IFACE.CREATED_BY := FND_GLOBAL.USER_ID;
V_RCV_HEADERS_IFACE.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
V_RCV_HEADERS_IFACE.PROCESSING_STATUS_CODE := 'PENDING';
V_RCV_HEADERS_IFACE.RECEIPT_SOURCE_CODE := 'VENDOR';
V_RCV_HEADERS_IFACE.AUTO_TRANSACT_CODE := 'RECEIVE'; --'RECEIVE'需要进过检验接收 DELIVER是直接交货
V_RCV_HEADERS_IFACE.RECEIPT_NUM := NULL; --Automatic Number
V_RCV_HEADERS_IFACE.VENDOR_ID := R1.VENDOR_ID;
V_RCV_HEADERS_IFACE.VENDOR_SITE_ID := R1.VENDOR_SITE_ID; --Optional
V_RCV_HEADERS_IFACE.EXPECTED_RECEIPT_DATE := SYSDATE;
V_RCV_HEADERS_IFACE.VALIDATION_FLAG := 'Y';
V_RCV_HEADERS_IFACE.SHIP_TO_ORGANIZATION_ID := R1.SHIP_TO_ORGANIZATION_ID;
SELECT PO.RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO V_RCV_HEADERS_IFACE.HEADER_INTERFACE_ID
FROM DUAL;
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO V_RCV_HEADERS_IFACE.GROUP_ID
FROM DUAL;
INSERT INTO PO.RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID
,GROUP_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROCESSING_STATUS_CODE
,RECEIPT_SOURCE_CODE
,TRANSACTION_TYPE
,AUTO_TRANSACT_CODE
,RECEIPT_NUM
,VENDOR_ID
,VENDOR_SITE_ID
,EXPECTED_RECEIPT_DATE
,VALIDATION_FLAG
,SHIP_TO_ORGANIZATION_ID
,RECEIPT_HEADER_ID)
VALUES
(V_RCV_HEADERS_IFACE.HEADER_INTERFACE_ID
,V_RCV_HEADERS_IFACE.GROUP_ID
,V_RCV_HEADERS_IFACE.LAST_UPDATE_DATE
,V_RCV_HEADERS_IFACE.LAST_UPDATED_BY
,V_RCV_HEADERS_IFACE.CREATION_DATE
,V_RCV_HEADERS_IFACE.CREATED_BY
,V_RCV_HEADERS_IFACE.LAST_UPDATE_LOGIN
,V_RCV_HEADERS_IFACE.PROCESSING_STATUS_CODE
,V_RCV_HEADERS_IFACE.RECEIPT_SOURCE_CODE
,V_RCV_HEADERS_IFACE.TRANSACTION_TYPE
,V_RCV_HEADERS_IFACE.AUTO_TRANSACT_CODE
,V_RCV_HEADERS_IFACE.RECEIPT_NUM
,V_RCV_HEADERS_IFACE.VENDOR_ID
,V_RCV_HEADERS_IFACE.VENDOR_SITE_ID
,V_RCV_HEADERS_IFACE.EXPECTED_RECEIPT_DATE
,V_RCV_HEADERS_IFACE.VALIDATION_FLAG
,V_RCV_HEADERS_IFACE.SHIP_TO_ORGANIZATION_ID
,V_RCV_HEADERS_IFACE.RECEIPT_HEADER_ID);
--接收行信息
V_RCV_TRANSACTION_IFACE.LAST_UPDATE_DATE := SYSDATE;
V_RCV_TRANSACTION_IFACE.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_RCV_TRANSACTION_IFACE.CREATION_DATE := SYSDATE;
V_RCV_TRANSACTION_IFACE.CREATED_BY := FND_GLOBAL.USER_ID;
V_RCV_TRANSACTION_IFACE.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO V_RCV_TRANSACTION_IFACE.INTERFACE_TRANSACTION_ID
FROM DUAL;
V_RCV_TRANSACTION_IFACE.HEADER_INTERFACE_ID := V_RCV_HEADERS_IFACE.HEADER_INTERFACE_ID;
V_RCV_TRANSACTION_IFACE.GROUP_ID := V_RCV_HEADERS_IFACE.GROUP_ID;
V_RCV_TRANSACTION_IFACE.PROCESSING_STATUS_CODE := 'PENDING';
V_RCV_TRANSACTION_IFACE.TRANSACTION_STATUS_CODE := 'PENDING';
V_RCV_TRANSACTION_IFACE.PROCESSING_MODE_CODE := 'IMMEDIATE';
V_RCV_TRANSACTION_IFACE.VALIDATION_FLAG := 'Y';
V_RCV_TRANSACTION_IFACE.RECEIPT_SOURCE_CODE := 'VENDOR';
V_RCV_TRANSACTION_IFACE.VENDOR_ID := R1.VENDOR_ID;
V_RCV_TRANSACTION_IFACE.VENDOR_SITE_ID := R1.VENDOR_SITE_ID; --Optional
V_RCV_TRANSACTION_IFACE.SOURCE_DOCUMENT_CODE := 'PO';
V_RCV_TRANSACTION_IFACE.PO_HEADER_ID := R1.PO_HEADER_ID;
V_RCV_TRANSACTION_IFACE.PO_LINE_ID := R1.PO_LINE_ID;
V_RCV_TRANSACTION_IFACE.PO_LINE_LOCATION_ID := R1.LINE_LOCATION_ID;
V_RCV_TRANSACTION_IFACE.PO_RELEASE_ID := NULL;
V_RCV_TRANSACTION_IFACE.TRANSACTION_TYPE := 'RECEIVE';
IF R1.RECEIVING_ROUTING_ID = 3 --直接交货
THEN
V_RCV_TRANSACTION_IFACE.AUTO_TRANSACT_CODE := 'DELIVER';
V_RCV_TRANSACTION_IFACE.DESTINATION_TYPE_CODE := 'INVENTORY';
ELSE
V_RCV_TRANSACTION_IFACE.AUTO_TRANSACT_CODE := 'RECEIVE';
V_RCV_TRANSACTION_IFACE.DESTINATION_TYPE_CODE := 'RECEIVING';
END IF;
V_RCV_TRANSACTION_IFACE.ITEM_ID := R1.ITEM_ID;
V_RCV_TRANSACTION_IFACE.TRANSACTION_DATE := SYSDATE;
V_RCV_TRANSACTION_IFACE.QUANTITY := R1.TRANSACTION_QUANTITY;
V_RCV_TRANSACTION_IFACE.UNIT_OF_MEASURE := R1.UNIT_MEAS_LOOKUP_CODE; --Not Code
V_RCV_TRANSACTION_IFACE.TO_ORGANIZATION_ID := R1.SHIP_TO_ORGANIZATION_ID;
V_RCV_TRANSACTION_IFACE.SHIP_TO_LOCATION_ID := R1.SHIP_TO_LOCATION_ID;
V_RCV_TRANSACTION_IFACE.LOCATION_ID := R1.SHIP_TO_LOCATION_ID;
V_RCV_TRANSACTION_IFACE.CURRENCY_CODE := R1.CURRENCY_CODE; --add on 2011-08-10 by sie chenyueyong
V_RCV_TRANSACTION_IFACE.CURRENCY_CONVERSION_TYPE := R1.RATE_TYPE;
V_RCV_TRANSACTION_IFACE.CURRENCY_CONVERSION_DATE := SYSDATE;
V_RCV_TRANSACTION_IFACE.PO_UNIT_PRICE := R1.UNIT_PRICE;
INSERT INTO PO.RCV_TRANSACTIONS_INTERFACE
(LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INTERFACE_TRANSACTION_ID
,HEADER_INTERFACE_ID
,GROUP_ID
,PROCESSING_STATUS_CODE
,TRANSACTION_STATUS_CODE
,PROCESSING_MODE_CODE
,VALIDATION_FLAG
,RECEIPT_SOURCE_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,SOURCE_DOCUMENT_CODE
,PO_HEADER_ID
,PO_LINE_ID
,PO_LINE_LOCATION_ID
,PO_RELEASE_ID
,TRANSACTION_TYPE
,AUTO_TRANSACT_CODE
,DESTINATION_TYPE_CODE
,ITEM_ID
,TRANSACTION_DATE
,QUANTITY
,UNIT_OF_MEASURE
,TO_ORGANIZATION_ID
,SHIP_TO_LOCATION_ID
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,PO_UNIT_PRICE)
VALUES
(V_RCV_TRANSACTION_IFACE.LAST_UPDATE_DATE
,V_RCV_TRANSACTION_IFACE.LAST_UPDATED_BY
,V_RCV_TRANSACTION_IFACE.CREATION_DATE
,V_RCV_TRANSACTION_IFACE.CREATED_BY
,V_RCV_TRANSACTION_IFACE.LAST_UPDATE_LOGIN
,V_RCV_TRANSACTION_IFACE.INTERFACE_TRANSACTION_ID
,V_RCV_TRANSACTION_IFACE.HEADER_INTERFACE_ID
,V_RCV_TRANSACTION_IFACE.GROUP_ID
,V_RCV_TRANSACTION_IFACE.PROCESSING_STATUS_CODE
,V_RCV_TRANSACTION_IFACE.TRANSACTION_STATUS_CODE
,V_RCV_TRANSACTION_IFACE.PROCESSING_MODE_CODE
,V_RCV_TRANSACTION_IFACE.VALIDATION_FLAG
,V_RCV_TRANSACTION_IFACE.RECEIPT_SOURCE_CODE
,V_RCV_TRANSACTION_IFACE.VENDOR_ID
,V_RCV_TRANSACTION_IFACE.VENDOR_SITE_ID
,V_RCV_TRANSACTION_IFACE.SOURCE_DOCUMENT_CODE
,V_RCV_TRANSACTION_IFACE.PO_HEADER_ID
,V_RCV_TRANSACTION_IFACE.PO_LINE_ID
,V_RCV_TRANSACTION_IFACE.PO_LINE_LOCATION_ID
,V_RCV_TRANSACTION_IFACE.PO_RELEASE_ID
,V_RCV_TRANSACTION_IFACE.TRANSACTION_TYPE
,V_RCV_TRANSACTION_IFACE.AUTO_TRANSACT_CODE
,V_RCV_TRANSACTION_IFACE.DESTINATION_TYPE_CODE
,V_RCV_TRANSACTION_IFACE.ITEM_ID
,V_RCV_TRANSACTION_IFACE.TRANSACTION_DATE
,V_RCV_TRANSACTION_IFACE.QUANTITY
,V_RCV_TRANSACTION_IFACE.UNIT_OF_MEASURE
,V_RCV_TRANSACTION_IFACE.TO_ORGANIZATION_ID
,V_RCV_TRANSACTION_IFACE.SHIP_TO_LOCATION_ID
,V_RCV_TRANSACTION_IFACE.CURRENCY_CODE
,V_RCV_TRANSACTION_IFACE.CURRENCY_CONVERSION_TYPE
,V_RCV_TRANSACTION_IFACE.CURRENCY_CONVERSION_DATE
,V_RCV_TRANSACTION_IFACE.PO_UNIT_PRICE);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(V_RCV_TRANSACTION_IFACE.GROUP_ID);
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('PO'
,'RVCTP'
,'接收事务处理处理器'
,TO_CHAR(SYSDATE
,'dd-mon-yy hh:mi:ss')
,FALSE
,'IMMEDIATE'
, --Start Date
V_RCV_TRANSACTION_IFACE.GROUP_ID
,CHR(0)
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,'');
COMMIT;
END;
--检验
DECLARE
--接收未检验
CURSOR C1 IS
SELECT RT.INTERFACE_TRANSACTION_ID
,RT.GROUP_ID
,RT.LAST_UPDATE_DATE
,RT.LAST_UPDATED_BY
,RT.CREATION_DATE
,RT.CREATED_BY
,RT.LAST_UPDATE_LOGIN
,RT.TRANSACTION_TYPE
,RT.TRANSACTION_DATE
,RS.QUANTITY UNACCEPTED_QTY --接收未检验数量
,RT.QUANTITY --接收数量
,RT.UNIT_OF_MEASURE
,RT.INTERFACE_SOURCE_CODE
,RT.SHIPMENT_HEADER_ID
,RT.SHIPMENT_LINE_ID
,RT.VENDOR_ID
,RT.VENDOR_SITE_ID
,RT.SOURCE_DOCUMENT_CODE
,RT.PARENT_TRANSACTION_ID
,RT.PO_HEADER_ID
,RT.PO_LINE_ID
,RT.PO_LINE_LOCATION_ID
,RT.INSPECTION_STATUS_CODE
,RT.DESTINATION_TYPE_CODE
,RT.LOCATION_ID
,RT.DESTINATION_CONTEXT
,RT.TRANSACTION_ID
,RSL.PO_DISTRIBUTION_ID
,RSL.ITEM_ID
,RSL.TO_ORGANIZATION_ID
,RSL.SHIP_TO_LOCATION_ID
,RT.CURRENCY_CODE
,RT.CURRENCY_CONVERSION_TYPE
,RT.PO_UNIT_PRICE
FROM RCV_TRANSACTIONS RT
,RCV_SHIPMENT_HEADERS RSH
,RCV_SHIPMENT_LINES RSL
,RCV_SUPPLY RS
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND RT.TRANSACTION_ID = RS.RCV_TRANSACTION_ID
AND RT.INTERFACE_TRANSACTION_ID = 5613930;
L_IFACE_RCV_REC PO.RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
V_DISTRIBUTION_ID NUMBER;
V_REQUEST_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 1027
,RESP_ID => 54872
,RESP_APPL_ID => 401);
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO L_IFACE_RCV_REC.GROUP_ID
FROM DUAL;
FOR R1 IN C1
LOOP
L_IFACE_RCV_REC.LAST_UPDATE_DATE := SYSDATE;
L_IFACE_RCV_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
L_IFACE_RCV_REC.CREATION_DATE := SYSDATE;
L_IFACE_RCV_REC.CREATED_BY := FND_GLOBAL.USER_ID;
L_IFACE_RCV_REC.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO L_IFACE_RCV_REC.INTERFACE_TRANSACTION_ID
FROM DUAL;
L_IFACE_RCV_REC.SHIPMENT_HEADER_ID := R1.SHIPMENT_HEADER_ID; --Shipment Header
L_IFACE_RCV_REC.PROCESSING_STATUS_CODE := 'INSPECTION';
L_IFACE_RCV_REC.TRANSACTION_STATUS_CODE := 'INSPECTION';
L_IFACE_RCV_REC.PROCESSING_MODE_CODE := 'IMMEDIATE';
L_IFACE_RCV_REC.RECEIPT_SOURCE_CODE := 'VENDOR';
L_IFACE_RCV_REC.VENDOR_ID := R1.VENDOR_ID;
L_IFACE_RCV_REC.VENDOR_SITE_ID := R1.VENDOR_SITE_ID; --Optional
L_IFACE_RCV_REC.SOURCE_DOCUMENT_CODE := 'PO';
L_IFACE_RCV_REC.PO_HEADER_ID := R1.PO_HEADER_ID;
L_IFACE_RCV_REC.PO_LINE_ID := R1.PO_LINE_ID;
L_IFACE_RCV_REC.PO_LINE_LOCATION_ID := R1.PO_LINE_LOCATION_ID;
L_IFACE_RCV_REC.TRANSACTION_TYPE := 'ACCEPT'; --交货时为DELIER
L_IFACE_RCV_REC.DESTINATION_TYPE_CODE := 'RECEIVING'; --交货时为INVENTORY
L_IFACE_RCV_REC.SHIPMENT_LINE_ID := R1.SHIPMENT_LINE_ID;
L_IFACE_RCV_REC.PARENT_TRANSACTION_ID := R1.TRANSACTION_ID;
L_IFACE_RCV_REC.ITEM_ID := R1.ITEM_ID;
L_IFACE_RCV_REC.TRANSACTION_DATE := SYSDATE;
L_IFACE_RCV_REC.QUANTITY := R1.UNACCEPTED_QTY;
L_IFACE_RCV_REC.UNIT_OF_MEASURE := R1.UNIT_OF_MEASURE; --Not Code
L_IFACE_RCV_REC.PRIMARY_QUANTITY := R1.UNACCEPTED_QTY;
L_IFACE_RCV_REC.PO_UNIT_PRICE := R1.PO_UNIT_PRICE;
L_IFACE_RCV_REC.PRIMARY_UNIT_OF_MEASURE := R1.UNIT_OF_MEASURE;
L_IFACE_RCV_REC.TO_ORGANIZATION_ID := R1.TO_ORGANIZATION_ID;
L_IFACE_RCV_REC.INTERFACE_SOURCE_CODE := 'RCV';
L_IFACE_RCV_REC.INSPECTION_STATUS_CODE := 'ACCEPTED';
L_IFACE_RCV_REC.DESTINATION_CONTEXT := 'RECEIVING';
L_IFACE_RCV_REC.CURRENCY_CODE := R1.CURRENCY_CODE;
L_IFACE_RCV_REC.CURRENCY_CONVERSION_TYPE := R1.CURRENCY_CONVERSION_TYPE;
-- L_IFACE_RCV_REC.SUBINVENTORY := R1.SUBINVENTORY_CODE;
SELECT PO_DISTRIBUTION_ID
INTO V_DISTRIBUTION_ID
FROM PO_DISTRIBUTIONS_ALL
WHERE LINE_LOCATION_ID = R1.PO_LINE_LOCATION_ID;
L_IFACE_RCV_REC.PO_DISTRIBUTION_ID := V_DISTRIBUTION_ID; --R1.PO_DISTRIBUTION_ID;
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INTERFACE_TRANSACTION_ID
,GROUP_ID
,SHIPMENT_HEADER_ID
,PROCESSING_STATUS_CODE
,TRANSACTION_STATUS_CODE
,PROCESSING_MODE_CODE
,RECEIPT_SOURCE_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,SOURCE_DOCUMENT_CODE
,PO_HEADER_ID
,PO_LINE_ID
,PO_LINE_LOCATION_ID
,TRANSACTION_TYPE
,AUTO_TRANSACT_CODE
,DESTINATION_TYPE_CODE
,SHIPMENT_LINE_ID
,PARENT_TRANSACTION_ID
,PO_DISTRIBUTION_ID
,ITEM_ID
,TRANSACTION_DATE
,QUANTITY
,UNIT_OF_MEASURE
,PRIMARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,TO_ORGANIZATION_ID
,INTERFACE_SOURCE_CODE
,INSPECTION_STATUS_CODE
,DESTINATION_CONTEXT
,SUBINVENTORY
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,PO_UNIT_PRICE)
VALUES
(L_IFACE_RCV_REC.LAST_UPDATE_DATE
,L_IFACE_RCV_REC.LAST_UPDATED_BY
,L_IFACE_RCV_REC.CREATION_DATE
,L_IFACE_RCV_REC.CREATED_BY
,L_IFACE_RCV_REC.LAST_UPDATE_LOGIN
,L_IFACE_RCV_REC.INTERFACE_TRANSACTION_ID
,L_IFACE_RCV_REC.GROUP_ID
,L_IFACE_RCV_REC.SHIPMENT_HEADER_ID
,L_IFACE_RCV_REC.PROCESSING_STATUS_CODE
,L_IFACE_RCV_REC.TRANSACTION_STATUS_CODE
,L_IFACE_RCV_REC.PROCESSING_MODE_CODE
,L_IFACE_RCV_REC.RECEIPT_SOURCE_CODE
,L_IFACE_RCV_REC.VENDOR_ID
,L_IFACE_RCV_REC.VENDOR_SITE_ID
,L_IFACE_RCV_REC.SOURCE_DOCUMENT_CODE
,L_IFACE_RCV_REC.PO_HEADER_ID
,L_IFACE_RCV_REC.PO_LINE_ID
,L_IFACE_RCV_REC.PO_LINE_LOCATION_ID
,L_IFACE_RCV_REC.TRANSACTION_TYPE
,L_IFACE_RCV_REC.AUTO_TRANSACT_CODE
,L_IFACE_RCV_REC.DESTINATION_TYPE_CODE
,L_IFACE_RCV_REC.SHIPMENT_LINE_ID
,L_IFACE_RCV_REC.PARENT_TRANSACTION_ID
,L_IFACE_RCV_REC.PO_DISTRIBUTION_ID
,L_IFACE_RCV_REC.ITEM_ID
,L_IFACE_RCV_REC.TRANSACTION_DATE
,L_IFACE_RCV_REC.QUANTITY
,L_IFACE_RCV_REC.UNIT_OF_MEASURE
,L_IFACE_RCV_REC.PRIMARY_QUANTITY
,L_IFACE_RCV_REC.PRIMARY_UNIT_OF_MEASURE
,L_IFACE_RCV_REC.TO_ORGANIZATION_ID
,L_IFACE_RCV_REC.INTERFACE_SOURCE_CODE
,L_IFACE_RCV_REC.INSPECTION_STATUS_CODE
,L_IFACE_RCV_REC.DESTINATION_CONTEXT
,L_IFACE_RCV_REC.SUBINVENTORY
,L_IFACE_RCV_REC.CURRENCY_CODE
,L_IFACE_RCV_REC.CURRENCY_CONVERSION_TYPE
,SYSDATE
,L_IFACE_RCV_REC.PO_UNIT_PRICE);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(L_IFACE_RCV_REC.GROUP_ID);
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('PO'
,'RVCTP'
,'接收事务处理处理器'
,TO_CHAR(SYSDATE
,'dd-mon-yy hh:mi:ss')
,FALSE
,'IMMEDIATE'
, --Start Date
L_IFACE_RCV_REC.GROUP_ID
,CHR(0)
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,'');
COMMIT;
END;
--交货程序
DECLARE
--检验未交货
CURSOR C2 IS
SELECT RT.INTERFACE_TRANSACTION_ID
,RT.GROUP_ID
,RT.LAST_UPDATE_DATE
,RT.LAST_UPDATED_BY
,RT.CREATION_DATE
,RT.CREATED_BY
,RT.LAST_UPDATE_LOGIN
,RT.TRANSACTION_TYPE
,RT.TRANSACTION_DATE
,RS.QUANTITY UNACCEPTED_QTY --检验未入库数量
,RS.QUANTITY QUANTITY --本次入库数量
,RT.UNIT_OF_MEASURE
,RT.INTERFACE_SOURCE_CODE
,RT.SHIPMENT_HEADER_ID
,RT.SHIPMENT_LINE_ID
,RT.VENDOR_ID
,RT.VENDOR_SITE_ID
,RT.SOURCE_DOCUMENT_CODE
,RT.PARENT_TRANSACTION_ID
,RT.PO_HEADER_ID
,RT.PO_LINE_ID
,RT.PO_LINE_LOCATION_ID
,RT.INSPECTION_STATUS_CODE
,RT.DESTINATION_TYPE_CODE
,RT.LOCATION_ID
,RT.DESTINATION_CONTEXT
,RT.TRANSACTION_ID
,RSL.PO_DISTRIBUTION_ID
,RSL.ITEM_ID
,RSL.TO_ORGANIZATION_ID
,RSL.SHIP_TO_LOCATION_ID
,RT.CURRENCY_CODE
,RT.CURRENCY_CONVERSION_TYPE
,RT.PO_UNIT_PRICE
,'0610' SUBINVENTORY_CODE --
FROM RCV_TRANSACTIONS RT
,RCV_SHIPMENT_HEADERS RSH
,RCV_SHIPMENT_LINES RSL
,RCV_SUPPLY RS
WHERE RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE IN ('ACCEPT'
,'REJECT')
AND RT.TRANSACTION_ID = RS.RCV_TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = 4378911;
L_IFACE_RCV_REC PO.RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
V_GROUP_ID1 NUMBER;
V_DISTRIBUTION_ID NUMBER;
V_REQUEST_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 1027
,RESP_ID => 54872
,RESP_APPL_ID => 401);
--交货事务处理开始
SELECT PO.RCV_INTERFACE_GROUPS_S.NEXTVAL INTO V_GROUP_ID1 FROM DUAL;
-- FND_MESSAGE.DEBUG(1);
FOR R2 IN C2
LOOP
-- FND_MESSAGE.DEBUG(2);
L_IFACE_RCV_REC.LAST_UPDATE_DATE := SYSDATE;
L_IFACE_RCV_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
L_IFACE_RCV_REC.CREATION_DATE := SYSDATE;
L_IFACE_RCV_REC.CREATED_BY := FND_GLOBAL.USER_ID;
L_IFACE_RCV_REC.LAST_UPDATE_LOGIN := -1;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO L_IFACE_RCV_REC.INTERFACE_TRANSACTION_ID
FROM DUAL;
L_IFACE_RCV_REC.SHIPMENT_HEADER_ID := R2.SHIPMENT_HEADER_ID; --Shipment Header
L_IFACE_RCV_REC.PROCESSING_STATUS_CODE := 'PENDING';
L_IFACE_RCV_REC.TRANSACTION_STATUS_CODE := 'PENDING';
L_IFACE_RCV_REC.PROCESSING_MODE_CODE := 'IMMEDIATE';
L_IFACE_RCV_REC.RECEIPT_SOURCE_CODE := 'VENDOR';
L_IFACE_RCV_REC.VENDOR_ID := R2.VENDOR_ID;
L_IFACE_RCV_REC.VENDOR_SITE_ID := R2.VENDOR_SITE_ID; --Optional
L_IFACE_RCV_REC.SOURCE_DOCUMENT_CODE := 'PO';
L_IFACE_RCV_REC.PO_HEADER_ID := R2.PO_HEADER_ID;
L_IFACE_RCV_REC.PO_LINE_ID := R2.PO_LINE_ID;
L_IFACE_RCV_REC.PO_LINE_LOCATION_ID := R2.PO_LINE_LOCATION_ID;
L_IFACE_RCV_REC.TRANSACTION_TYPE := 'DELIVER'; --交货时为DELIER
L_IFACE_RCV_REC.AUTO_TRANSACT_CODE := NULL;
L_IFACE_RCV_REC.DESTINATION_TYPE_CODE := 'INVENTORY'; --交货时为INVENTORY
L_IFACE_RCV_REC.SHIPMENT_LINE_ID := R2.SHIPMENT_LINE_ID;
L_IFACE_RCV_REC.PARENT_TRANSACTION_ID := R2.TRANSACTION_ID;
L_IFACE_RCV_REC.ITEM_ID := R2.ITEM_ID;
L_IFACE_RCV_REC.TRANSACTION_DATE := SYSDATE;
L_IFACE_RCV_REC.QUANTITY := R2.QUANTITY;
L_IFACE_RCV_REC.UNIT_OF_MEASURE := R2.UNIT_OF_MEASURE; --Not Code
L_IFACE_RCV_REC.PRIMARY_QUANTITY := R2.QUANTITY;
L_IFACE_RCV_REC.PO_UNIT_PRICE := R2.PO_UNIT_PRICE;
L_IFACE_RCV_REC.PRIMARY_UNIT_OF_MEASURE := R2.UNIT_OF_MEASURE;
L_IFACE_RCV_REC.TO_ORGANIZATION_ID := R2.TO_ORGANIZATION_ID;
L_IFACE_RCV_REC.INTERFACE_SOURCE_CODE := 'RCV';
L_IFACE_RCV_REC.INSPECTION_STATUS_CODE := 'ACCEPTED';
L_IFACE_RCV_REC.DESTINATION_CONTEXT := 'INVENTORY';
L_IFACE_RCV_REC.CURRENCY_CODE := R2.CURRENCY_CODE;
L_IFACE_RCV_REC.CURRENCY_CONVERSION_TYPE := R2.CURRENCY_CONVERSION_TYPE;
L_IFACE_RCV_REC.SUBINVENTORY := R2.SUBINVENTORY_CODE;
SELECT PO_DISTRIBUTION_ID
INTO V_DISTRIBUTION_ID
FROM PO_DISTRIBUTIONS_ALL
WHERE LINE_LOCATION_ID = R2.PO_LINE_LOCATION_ID;
L_IFACE_RCV_REC.PO_DISTRIBUTION_ID := V_DISTRIBUTION_ID; --R2.PO_DISTRIBUTION_ID;
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,INTERFACE_TRANSACTION_ID
,GROUP_ID
,SHIPMENT_HEADER_ID
,PROCESSING_STATUS_CODE
,TRANSACTION_STATUS_CODE
,PROCESSING_MODE_CODE
,RECEIPT_SOURCE_CODE
,VENDOR_ID
,VENDOR_SITE_ID
,SOURCE_DOCUMENT_CODE
,PO_HEADER_ID
,PO_LINE_ID
,PO_LINE_LOCATION_ID
,TRANSACTION_TYPE
,AUTO_TRANSACT_CODE
,DESTINATION_TYPE_CODE
,SHIPMENT_LINE_ID
,PARENT_TRANSACTION_ID
,PO_DISTRIBUTION_ID
,ITEM_ID
,TRANSACTION_DATE
,QUANTITY
,UNIT_OF_MEASURE
,PRIMARY_QUANTITY
,PRIMARY_UNIT_OF_MEASURE
,TO_ORGANIZATION_ID
,INTERFACE_SOURCE_CODE
,INSPECTION_STATUS_CODE
,DESTINATION_CONTEXT
,SUBINVENTORY
,CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_DATE
,PO_UNIT_PRICE)
VALUES
(L_IFACE_RCV_REC.LAST_UPDATE_DATE
,L_IFACE_RCV_REC.LAST_UPDATED_BY
,L_IFACE_RCV_REC.CREATION_DATE
,L_IFACE_RCV_REC.CREATED_BY
,L_IFACE_RCV_REC.LAST_UPDATE_LOGIN
,L_IFACE_RCV_REC.INTERFACE_TRANSACTION_ID
,V_GROUP_ID1
,L_IFACE_RCV_REC.SHIPMENT_HEADER_ID
,L_IFACE_RCV_REC.PROCESSING_STATUS_CODE
,L_IFACE_RCV_REC.TRANSACTION_STATUS_CODE
,L_IFACE_RCV_REC.PROCESSING_MODE_CODE
,L_IFACE_RCV_REC.RECEIPT_SOURCE_CODE
,L_IFACE_RCV_REC.VENDOR_ID
,L_IFACE_RCV_REC.VENDOR_SITE_ID
,L_IFACE_RCV_REC.SOURCE_DOCUMENT_CODE
,L_IFACE_RCV_REC.PO_HEADER_ID
,L_IFACE_RCV_REC.PO_LINE_ID
,L_IFACE_RCV_REC.PO_LINE_LOCATION_ID
,L_IFACE_RCV_REC.TRANSACTION_TYPE
,L_IFACE_RCV_REC.AUTO_TRANSACT_CODE
,L_IFACE_RCV_REC.DESTINATION_TYPE_CODE
,L_IFACE_RCV_REC.SHIPMENT_LINE_ID
,L_IFACE_RCV_REC.PARENT_TRANSACTION_ID
,L_IFACE_RCV_REC.PO_DISTRIBUTION_ID
,L_IFACE_RCV_REC.ITEM_ID
,L_IFACE_RCV_REC.TRANSACTION_DATE
,L_IFACE_RCV_REC.QUANTITY
,L_IFACE_RCV_REC.UNIT_OF_MEASURE
,L_IFACE_RCV_REC.PRIMARY_QUANTITY
,L_IFACE_RCV_REC.PRIMARY_UNIT_OF_MEASURE
,L_IFACE_RCV_REC.TO_ORGANIZATION_ID
,L_IFACE_RCV_REC.INTERFACE_SOURCE_CODE
,L_IFACE_RCV_REC.INSPECTION_STATUS_CODE
,L_IFACE_RCV_REC.DESTINATION_CONTEXT
,L_IFACE_RCV_REC.SUBINVENTORY
,L_IFACE_RCV_REC.CURRENCY_CODE
,L_IFACE_RCV_REC.CURRENCY_CONVERSION_TYPE
,SYSDATE
,L_IFACE_RCV_REC.PO_UNIT_PRICE);
END LOOP;
COMMIT;
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('PO'
,'RVCTP'
,'接收事务处理处理器'
,TO_CHAR(SYSDATE
,'dd-mon-yy hh:mi:ss')
,FALSE
,'IMMEDIATE'
, --Start Date
V_GROUP_ID1
,CHR(0)
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,''
,'');
COMMIT;
DBMS_OUTPUT.PUT_LINE(V_GROUP_ID1);
END;
- 采购 接收检验入库 接口处理例子
- 采购接收检验入库程序
- EBS 采购检验-入库接口
- OracleEBS采购接收入库接口开发
- OracleEBS采购接收入库接口开发
- 采购接收入库事务处理数据流
- 通过外来系统抛ERP采购接收接口,系统自动接收入库
- 详解EBS接口开发之库事务处理带提前发运通知(ASN)采购接收入库-补充
- 采购接收直接入库interface导入
- 详解EBS接口开发之采购接收&退货处理之关键API--(补充)
- 采购接收接口开发 (PO RECEIPTS INTERFACE)
- 采购接收事务处理接口表错误信息查询
- OpenERP 生产型企业原材料采购及入库的处理
- 采购单入库操作
- EBS 采购订单入库
- 详解EBS接口开发之库存事务处理采购接收--补充
- 详解EBS接口开发之库存事务处理采购接收--补充
- PO Receiving: 从接收到入库过程中的财务处理
- 终于解决“Git Windows客户端保存用户名与密码”的问题
- 常用git命令及注意事项
- 开源磁盘恢复工具--TestDisk
- java 模拟HTTP doPost请求 设置参数
- CX51 用户手册----PAGEWIDTH伪指令
- 采购 接收检验入库 接口处理例子
- ASP.NET系统开发(二):漫话级联删除“三剑客”
- Windows的图形设备接口(GDI)
- linux环境变量设置方法
- CX51 用户手册----PREPRINT伪指令
- 计算几何 - 题目列表
- myeclipse下手动配置多个tomcat实例
- 三十二、Java图形化界面设计——布局管理器之CardLayout(卡片布局)
- C库函数