Oracle EBS 采购订单接收接口表
一、创建接收题头
PROCEDURE PROC_CREATE_SHIPMENT_HEADER(X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_RETURN_MESSAGE OUT NOCOPY VARCHAR2,
P_SHIP_TO_ORGANIZATION_ID IN NUMBER,
P_VENDOR_ID IN NUMBER,
P_VENDOR_SITE_ID IN NUMBER,
P_COMMENTS IN VARCHAR2,
P_RECEIPT_HEADER_ID OUT NUMBER) IS
X_HEADER_RECORD RCV_SHIPMENT_HEADER_SV.HEADERRECTYPE;
BEGIN
X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
X_RETURN_MESSAGE := NULL;
MO_GLOBAL.SET_POLICY_CONTEXT(P_ACCESS_MODE => 'S', P_ORG_ID => 82);
FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, 54092, 401);
SELECT PO.RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO X_HEADER_RECORD.HEADER_RECORD.HEADER_INTERFACE_ID
FROM DUAL;
SELECT PO.RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO X_HEADER_RECORD.HEADER_RECORD.GROUP_ID
FROM DUAL;
X_HEADER_RECORD.HEADER_RECORD.PROCESSING_STATUS_CODE := 'PENDING';
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_SOURCE_CODE := 'VENDOR';
X_HEADER_RECORD.HEADER_RECORD.TRANSACTION_TYPE := 'NEW';
X_HEADER_RECORD.HEADER_RECORD.AUTO_TRANSACT_CODE := 'RECEIVE';
X_HEADER_RECORD.HEADER_RECORD.VENDOR_ID := P_VENDOR_ID;
X_HEADER_RECORD.HEADER_RECORD.VENDOR_SITE_ID := P_VENDOR_SITE_ID; --Optional
X_HEADER_RECORD.HEADER_RECORD.EXPECTED_RECEIPT_DATE := SYSDATE;
X_HEADER_RECORD.HEADER_RECORD.VALIDATION_FLAG := 'Y';
X_HEADER_RECORD.HEADER_RECORD.SHIP_TO_ORGANIZATION_ID := P_SHIP_TO_ORGANIZATION_ID;
X_HEADER_RECORD.HEADER_RECORD.COMMENTS := P_COMMENTS;
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM := NULL; --Automatic Number
--Process Receipt Num
SELECT DECODE(USER_DEFINED_RECEIPT_NUM_CODE,
'AUTOMATIC',
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM,
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_HEADER_ID)
INTO X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM
FROM RCV_PARAMETERS
WHERE ORGANIZATION_ID =
X_HEADER_RECORD.HEADER_RECORD.SHIP_TO_ORGANIZATION_ID;
IF X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM IS NULL THEN
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM := FUNC_GET_RECEIPT_NUM(P_ORGANIZATION_ID => X_HEADER_RECORD.HEADER_RECORD.SHIP_TO_ORGANIZATION_ID);
END IF;
--Initiate error record
X_HEADER_RECORD.ERROR_RECORD.ERROR_STATUS := 'S';
X_HEADER_RECORD.ERROR_RECORD.ERROR_MESSAGE := NULL;
--Invoke API
RCV_SHIPMENT_HEADER_SV.CREATE_SHIPMENT_HEADER(X_HEADER_RECORD => X_HEADER_RECORD);
--Result
IF X_HEADER_RECORD.ERROR_RECORD.ERROR_STATUS IN ('S', 'W') THEN
P_RECEIPT_HEADER_ID := X_HEADER_RECORD.HEADER_RECORD.RECEIPT_HEADER_ID;
/*
DBMS_OUTPUT.PUT_LINE('Success.');
DBMS_OUTPUT.PUT_LINE('Receipt Number: ' ||
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_NUM);
DBMS_OUTPUT.PUT_LINE('Receipt Header ID: ' ||
X_HEADER_RECORD.HEADER_RECORD.RECEIPT_HEADER_ID);*/
ELSE
X_RETURN_STATUS := X_HEADER_RECORD.ERROR_RECORD.ERROR_STATUS;
X_RETURN_MESSAGE := X_HEADER_RECORD.ERROR_RECORD.ERROR_MESSAGE;
/* DBMS_OUTPUT.PUT_LINE('Error Status:' ||
X_HEADER_RECORD.ERROR_RECORD.ERROR_STATUS);
DBMS_OUTPUT.PUT_LINE('Error Message:' ||
X_HEADER_RECORD.ERROR_RECORD.ERROR_MESSAGE);*/
END IF;
EXCEPTION
WHEN OTHERS THEN
X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
X_RETURN_MESSAGE := SQLERRM;
END PROC_CREATE_SHIPMENT_HEADER;
二、导入接口表
PROCEDURE PROC_CREATE_RCV_INTERFACE(X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_RETURN_MESSAGE OUT NOCOPY VARCHAR2,
P_GROUP_ID IN NUMBER,
P_SHIPMENT_HEADER_ID IN NUMBER,
P_ORG_ID IN NUMBER,
P_VENDOR_ID IN NUMBER,
P_VENDOR_SITE_ID IN NUMBER,
P_PO_HEADER_ID IN NUMBER,
P_PO_LINE_ID IN NUMBER,
P_LINE_LOCATION_ID IN NUMBER,
P_PO_DISTRIBUTION_ID IN NUMBER,
P_UNIT_MEAS_LOOKUP_CODE IN VARCHAR2,
P_CURRENCY_CODE IN VARCHAR2,
P_ITEM_ID IN NUMBER,
P_SHIP_TO_ORGANIZATION_ID IN NUMBER,
P_SHIP_TO_LOCATION_ID IN NUMBER,
P_RECEIVING_ROUTING_ID IN NUMBER,
P_TRANSACTION_QUANTITY IN NUMBER,
P_SUBINVENTORY IN VARCHAR2,
P_LOCATOR_ID IN NUMBER,
P_LOT_CONTROL_CODE IN NUMBER,
P_SERIAL_NUMBER_CONTROL_CODE IN NUMBER,
P_LOT_SN_NUM IN VARCHAR2) IS
L_RCV_TRANS RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
L_RCV_LOT RCV_LOTS_INTERFACE%ROWTYPE;
L_RCV_TEMP MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
L_RSI_REC RCV_SERIALS_INTERFACE%ROWTYPE;
L_MSNT_REC MTL_SERIAL_NUMBERS_TEMP%ROWTYPE;
V_RCV_HEADER_ID NUMBER;
V_RCV_TRX_ID NUMBER;
V_GROUP_ID NUMBER := P_GROUP_ID;
L_RETURN_STATUS VARCHAR2(12);
L_MSG_DATA VARCHAR2(4000);
L_TRANSACTION_DATE DATE;
L_USER_ID NUMBER := FND_GLOBAL.USER_ID;
V_GROUP_HEADER_ID NUMBER;
L_CNT NUMBER;
CURSOR C_SERIAL IS
SELECT CIT.ROWID,
TRIM(CIT.ATTRIBUTE1) PO_NUM, --采购订单号
TRIM(CIT.ATTRIBUTE2) ITEM_NUMBER, --物料编码
TRIM(CIT.ATTRIBUTE3) RCV_QRY, --供应商送货数量
TRIM(CIT.ATTRIBUTE4) REMARKS, --备注
TRIM(CIT.ATTRIBUTE5) SUBINVENTORY, --子库存
TRIM(CIT.ATTRIBUTE6) LOCATOR_CODE, --货位
TRIM(CIT.ATTRIBUTE7) SERIAL_NUMBER, --序列号
TO_NUMBER(CIT.CONVERTED_ATTRIBUTE1) PO_HEADER_ID,
TO_NUMBER(CIT.CONVERTED_ATTRIBUTE2) ITEM_ID,
CIT.CONVERTED_ATTRIBUTE5,
CIT.CONVERTED_ATTRIBUTE6,
CIT.CONVERTED_ATTRIBUTE7,
TO_NUMBER(CIT.CONVERTED_ATTRIBUTE10) CONVERTED_ATTRIBUT10, --未接收数量
TO_NUMBER(CIT.CONVERTED_ATTRIBUTE11) LOT_CONTROL_CODE,
TO_NUMBER(CIT.CONVERTED_ATTRIBUTE12) SERIAL_NUMBER_CONTROL_CODE
FROM CUX_COMMON_IMPORTS_TEMP CIT
WHERE 1 = 1
AND CIT.IMPORT_STATUS = G_VALID_CODE
AND CIT.COMMON_IMPORTS_ID =
CUX_COMMON_IMPORTS_PKG.GET_COMMON_IMPORTS_ID
AND TO_NUMBER(CIT.CONVERTED_ATTRIBUTE12) = 5
AND CIT.CONVERTED_ATTRIBUTE40 IS NULL --未导入
AND TO_NUMBER(CIT.CONVERTED_ATTRIBUTE1) = P_PO_HEADER_ID
AND TO_NUMBER(CIT.CONVERTED_ATTRIBUTE2) = P_ITEM_ID
ORDER BY TRIM(CIT.ATTRIBUTE7) ASC;
BEGIN
X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
X_RETURN_MESSAGE := NULL;
V_GROUP_HEADER_ID := RCV_INTERFACE_GROUPS_S.NEXTVAL;
--V_GROUP_ID := RCV_INTERFACE_GROUPS_S.NEXTVAL;
L_TRANSACTION_DATE := SYSDATE;
V_RCV_HEADER_ID := RCV_HEADERS_INTERFACE_S.NEXTVAL;
V_RCV_TRX_ID := RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL;
L_RCV_TRANS.HEADER_INTERFACE_ID := NULL;
L_RCV_TRANS.INTERFACE_TRANSACTION_ID := V_RCV_TRX_ID;
L_RCV_TRANS.GROUP_ID := V_GROUP_ID; --p_group_id;
L_RCV_TRANS.LAST_UPDATE_DATE := SYSDATE;
L_RCV_TRANS.LAST_UPDATED_BY := L_USER_ID;
L_RCV_TRANS.CREATION_DATE := SYSDATE;
L_RCV_TRANS.CREATED_BY := L_USER_ID; --update by fmlin 2015-09-24
L_RCV_TRANS.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
L_RCV_TRANS.TRANSACTION_DATE := L_TRANSACTION_DATE;
L_RCV_TRANS.PROCESSING_STATUS_CODE := 'PENDING';
L_RCV_TRANS.PROCESSING_MODE_CODE := 'BATCH';
L_RCV_TRANS.TRANSACTION_STATUS_CODE := 'PENDING';
--po_return
L_RCV_TRANS.TRANSACTION_TYPE := 'RECEIVE' /*'RETURN TO VENDOR'*/
;
L_RCV_TRANS.INTERFACE_SOURCE_CODE := 'RCV'; ---------------
L_RCV_TRANS.RECEIPT_SOURCE_CODE := 'VENDOR';
L_RCV_TRANS.SOURCE_DOCUMENT_CODE := 'PO';
L_RCV_TRANS.SHIPMENT_HEADER_ID := P_SHIPMENT_HEADER_ID;
L_RCV_TRANS.ORG_ID := P_ORG_ID;
L_RCV_TRANS.VENDOR_ID := P_VENDOR_ID;
L_RCV_TRANS.VENDOR_SITE_ID := P_VENDOR_SITE_ID;
L_RCV_TRANS.PO_HEADER_ID := P_PO_HEADER_ID;
L_RCV_TRANS.PO_LINE_ID := P_PO_LINE_ID;
L_RCV_TRANS.PO_LINE_LOCATION_ID := P_LINE_LOCATION_ID;
L_RCV_TRANS.PO_DISTRIBUTION_ID := P_PO_DISTRIBUTION_ID;
L_RCV_TRANS.UNIT_OF_MEASURE := P_UNIT_MEAS_LOOKUP_CODE;
L_RCV_TRANS.CURRENCY_CODE := P_CURRENCY_CODE;
L_RCV_TRANS.UOM_CODE := P_UNIT_MEAS_LOOKUP_CODE;
L_RCV_TRANS.ITEM_ID := P_ITEM_ID;
L_RCV_TRANS.TO_ORGANIZATION_ID := P_SHIP_TO_ORGANIZATION_ID;
L_RCV_TRANS.SHIP_TO_LOCATION_ID := P_SHIP_TO_LOCATION_ID;
L_RCV_TRANS.ROUTING_HEADER_ID := P_RECEIVING_ROUTING_ID;
L_RCV_TRANS.QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_TRANS.PRIMARY_QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_TRANS.PRIMARY_UNIT_OF_MEASURE := P_UNIT_MEAS_LOOKUP_CODE;
--L_RCV_TRANS.COMMENTS := SUBSTRB(P_COMMENTS, 1, 240);
L_RCV_TRANS.SOURCE_DOC_QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_TRANS.SOURCE_DOC_UNIT_OF_MEASURE := P_UNIT_MEAS_LOOKUP_CODE;
/*序列控制的物料，直接交货时，以下两个字段必填，否则会报错全部序列数量不一致*/
L_RCV_TRANS.USE_MTL_LOT := P_LOT_CONTROL_CODE;
L_RCV_TRANS.USE_MTL_SERIAL := P_SERIAL_NUMBER_CONTROL_CODE;
IF P_RECEIVING_ROUTING_ID = 3 THEN
--直接交货时，需要子库和货位信息
L_RCV_TRANS.SUBINVENTORY := P_SUBINVENTORY;
L_RCV_TRANS.LOCATOR_ID := P_LOCATOR_ID;
--采购接收
L_RCV_TRANS.AUTO_TRANSACT_CODE := 'DELIVER';
L_RCV_TRANS.DESTINATION_TYPE_CODE := 'INVENTORY';
L_RCV_TRANS.DESTINATION_CONTEXT := 'INVENTORY';
ELSE
L_RCV_TRANS.AUTO_TRANSACT_CODE := 'RECEIVE';
L_RCV_TRANS.DESTINATION_TYPE_CODE := 'RECEIVING';
L_RCV_TRANS.DESTINATION_CONTEXT := 'RECEIVING';
END IF;
--L_RCV_TRANS.VALIDATION_FLAG := 'Y';
L_RCV_TRANS.INSPECTION_STATUS_CODE := 'NOT INSPECTED';
INSERT INTO RCV_TRANSACTIONS_INTERFACE VALUES L_RCV_TRANS;
IF P_RECEIVING_ROUTING_ID = 3 THEN
--直接交货时，需要批次或序列信息
IF P_LOT_CONTROL_CODE = 2 THEN
--批次
L_RCV_LOT.INTERFACE_TRANSACTION_ID := V_RCV_TRX_ID;
L_RCV_LOT.PROGRAM_UPDATE_DATE := SYSDATE;
L_RCV_LOT.TRANSACTION_DATE := L_TRANSACTION_DATE;
L_RCV_LOT.LOT_NUM := P_LOT_SN_NUM;
L_RCV_LOT.QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_LOT.PRIMARY_QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_LOT.ITEM_ID := P_ITEM_ID;
L_RCV_LOT.LAST_UPDATE_DATE := SYSDATE;
L_RCV_LOT.LAST_UPDATED_BY := L_USER_ID;
L_RCV_LOT.CREATION_DATE := SYSDATE;
L_RCV_LOT.CREATED_BY := L_USER_ID;
L_RCV_LOT.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO RCV_LOTS_INTERFACE VALUES L_RCV_LOT;
--批次临时表
L_RCV_TEMP.TRANSACTION_TEMP_ID := V_RCV_TRX_ID;
L_RCV_TEMP.LOT_NUMBER := P_LOT_SN_NUM;
L_RCV_TEMP.TRANSACTION_QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_TEMP.PRIMARY_QUANTITY := P_TRANSACTION_QUANTITY;
L_RCV_TEMP.GROUP_HEADER_ID := V_GROUP_HEADER_ID;
L_RCV_TEMP.LOT_EXPIRATION_DATE := SYSDATE;
L_RCV_TEMP.ORIGINATION_DATE := SYSDATE;
L_RCV_TEMP.PRODUCT_CODE := 'RCV';
L_RCV_TEMP.PRODUCT_TRANSACTION_ID := V_RCV_TRX_ID;
L_RCV_TEMP.LAST_UPDATE_DATE := SYSDATE;
L_RCV_TEMP.LAST_UPDATED_BY := L_USER_ID;
L_RCV_TEMP.CREATION_DATE := SYSDATE;
L_RCV_TEMP.CREATED_BY := L_USER_ID;
L_RCV_TEMP.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO MTL_TRANSACTION_LOTS_TEMP VALUES L_RCV_TEMP;
ELSIF P_SERIAL_NUMBER_CONTROL_CODE = 5 THEN
L_CNT := 1;
FOR R IN C_SERIAL LOOP
IF L_CNT <= P_TRANSACTION_QUANTITY THEN
L_CNT := L_CNT + 1;
--序列接口表
L_RSI_REC.INTERFACE_TRANSACTION_ID := V_RCV_TRX_ID;
L_RSI_REC.TRANSACTION_DATE := L_TRANSACTION_DATE;
L_RSI_REC.FM_SERIAL_NUM := R.SERIAL_NUMBER;
L_RSI_REC.TO_SERIAL_NUM := R.SERIAL_NUMBER;
L_RSI_REC.SERIAL_PREFIX := 1;
L_RSI_REC.ITEM_ID := P_ITEM_ID;
L_RSI_REC.ORGANIZATION_ID := P_SHIP_TO_ORGANIZATION_ID;
L_RSI_REC.LAST_UPDATE_DATE := SYSDATE;
L_RSI_REC.LAST_UPDATED_BY := L_USER_ID;
L_RSI_REC.CREATION_DATE := SYSDATE;
L_RSI_REC.CREATED_BY := L_USER_ID;
L_RSI_REC.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO RCV_SERIALS_INTERFACE VALUES L_RSI_REC;
--序列临时表
L_MSNT_REC.TRANSACTION_TEMP_ID := V_RCV_TRX_ID;
L_MSNT_REC.FM_SERIAL_NUMBER := R.SERIAL_NUMBER;
L_MSNT_REC.TO_SERIAL_NUMBER := R.SERIAL_NUMBER;
L_MSNT_REC.SERIAL_PREFIX := 1;
L_MSNT_REC.GROUP_HEADER_ID := V_GROUP_HEADER_ID;--V_RCV_HEADER_ID;
L_MSNT_REC.PRODUCT_CODE := 'RCV';
L_MSNT_REC.PRODUCT_TRANSACTION_ID := V_RCV_TRX_ID;
L_MSNT_REC.LAST_UPDATE_DATE := SYSDATE;
L_MSNT_REC.LAST_UPDATED_BY := L_USER_ID;
L_MSNT_REC.CREATION_DATE := SYSDATE;
L_MSNT_REC.CREATED_BY := L_USER_ID;
L_MSNT_REC.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
INSERT INTO MTL_SERIAL_NUMBERS_TEMP VALUES L_MSNT_REC;
--更新导入标识
UPDATE CUX_COMMON_IMPORTS_TEMP CIT
SET CIT.CONVERTED_ATTRIBUTE40 = 1
WHERE 1 = 1
AND CIT.COMMON_IMPORTS_ID =
CUX_COMMON_IMPORTS_PKG.GET_COMMON_IMPORTS_ID
AND CIT.ROWID = R.ROWID;
ELSE
EXIT;
END IF;
END LOOP;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
X_RETURN_MESSAGE := SQLERRM;
END PROC_CREATE_RCV_INTERFACE;
三、提交 接收事务处理处理器 请求
PROCEDURE PROC_SUBMIT_REQUEST(P_GROUP_ID IN NUMBER) IS
V_REQID NUMBER;
L_PHASE VARCHAR(200);
L_STATUS VARCHAR(200);
L_DEV_PHASE VARCHAR(200);
L_DEV_STATUS VARCHAR(200);
L_MESSAGE VARCHAR(2000);
L_REQUEST_STATUS BOOLEAN;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, 54092, 401);
V_REQID := FND_REQUEST.SUBMIT_REQUEST('PO',
'RVCTP',
'',
'',
FALSE,
'BATCH',
P_GROUP_ID,
'',
CHR(0));
COMMIT;
IF (V_REQID > 0) THEN
L_REQUEST_STATUS := FND_CONCURRENT.WAIT_FOR_REQUEST(V_REQID,
5,
0,
L_PHASE,
L_STATUS,
L_DEV_PHASE,
L_DEV_STATUS,
L_MESSAGE);
END IF;
END PROC_SUBMIT_REQUEST;
Oracle EBS 采购订单接收接口表
http://localhost:8091//archives/oracleebs%E9%87%87%E8%B4%AD%E8%AE%A2%E5%8D%95%E6%8E%A5%E6%94%B6%E6%8E%A5%E5%8F%A3%E8%A1%A8