--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;
扫一扫
1489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
