目录
Open Inerface最简单例子（新建接收头和接收）（11.5.9 GITI环境测试通过）
快速参考
这个接口能做的事情其实是非常多的，并不像115mfgapi.pdf说的那样，不支持Separate receive and deliver transactions、Corrections、Returns、Receiving against Drop Ship Orders；实际上这些都可以通过接口完成。文档宣称的其他一些“不支持”，还有待进一步测试。
| 参考点 | 内容 |
| 功能导航 | N: PO/Receiving |
| 并发请求 | N: View/Request/Receiving Transaction Processor |
| 接口表 | rcv_headers_interface |
| rcv_transactions_interface | |
| API | |
| 错误信息表 | po_interface_errors |
| 接口Form | N: PO/Receiving/Transaction Status Summary |
| 接口Report | N: PO/Receiving Exceptions Report |
| Debug Profile | RCV: Debug Mode |
| PO:Enable Sql Trace for Receiving Processor | |
| INV:Debug Level（如果有入库）10 | |
| INV: Debug Trace（如果有入库）1 | |
| OM: Debug Level（如果是Dropship）5 | |
| 详细例子 | oracle_iface_scripts_pkg.rcv_trx_iface |
| 官方文档 | 115mfgapi.pdf：Oracle Manufacturing APIs and Open Interfaces Manual |
| 数据验证包 | rcv_roi_preprocessor.preprocessor可以预先验证接口表的数据 |
| rcv_shipment_object_sv | |
| rcv_shipment_header_sv/rcv_headers_interface_sv | |
| rcv_shipment_line_sv/rcv_transactions_interface_sv |
开发参考
接口表、错误信息
| 接口表 | 数据表 | 说明 |
| rcv_headers_interface | rcv_shipment_headers | 接收单头表 |
| rcv_transactions_interface | rcv_shipment_lines | 可单独使用。接收当行表、接收事务、库存事务 |
| rcv_transactions | ||
| mtl_material_transactions | ||
| rcv_lots_interface | rcv_lot_transactions | 入库时，有批次控制的物料需要提供批次信息 |
| Mtl_Transaction_Lots_Interface | 入库使用这个表才能入库成功 插入rcv_lots_interface，会产生接收批次。不插入的话，在前台做退库的时候无法选的上一步入库的批次进行退库。 | |
| rcv_serials_interface | rcv_serial_transactions | 入库时，有序列控制的物料需要提供序列号信息 |
| po_interface_errors | 错误信息表 |
Table Relation:
rcv_headers_interface.header_interface_id := rcv_transactions_interface.header_interface_id and
rcv_headers_interface.group_id := rcv_transactions_interface.group_id;
rcv_transactions_interface.interface_transaction_id := rcv_lots_interface.interface_transaction_id;
po_interface_errors的关联表
SELECT *
FROM po_interface_errors pie, rcv_transactions_interface rti
WHERE pie.interface_transaction_id = rti.interface_transaction_id
AND pie.creation_date > SYSDATE - 0.2;
SELECT *
FROM po_interface_errors pie, rcv_headers_interface rhi
WHERE pie.interface_header_id = rhi.header_interface_id
AND pie.creation_date > SYSDATE - 0.2;注意事项！！！！！
- 如果进行进行入库，批次需要插入Mtl_Transaction_Lots_Interface和Rcv_Lots_Interface着两张表；
- Mtl_Transaction_Lots_Interface产生库存的物料事务处理，并且头表Rcv_Transactions_Interface.Use_Mtl_Lot必须为2，否则不成功；
- Rcv_Lots_Interface会产生rcv_lot_transaction的批次记录，在前台可以看到入库批次，如果退库，前台才能选择到批次；
请求（request）
请求名：Receiving Transaction Processor
请求名：接收事务处理处理器
关键字段（参数）+最简单例子
关键字段
rcv_headers_interface
| 字段 | 说明 |
| header_interface_id | po.rcv_headers_interface_s.NEXTVAL |
| group_id | po.rcv_interface_groups_s.NEXTVAL |
| processing_status_code | 给PENDING |
| receipt_source_code | 给VENDOR、CUSTOMER |
| asn_type | 如果是ASN，就写ASN |
| transaction_type | 给NEW |
| auto_transact_code | 给RECEIVE |
| receipt_num | 接收号。自动编号给空即可 |
| vendor_id | 供应商ID |
| vendor_site_id | 供应商地点ID，可选 |
| expected_receipt_date | 接收日期 |
| validation_flag | 给Y |
| ship_to_organization_id | 接收库存ID |
| 5个who字段 | 习惯上都给 |
rcv_transactions_interface
| 字段 | 说明 |
| header_interface_id | 单独使用放空，否则需要和头一致 |
| group_id | 单独使用po.rcv_interface_groups_s.NEXTVAL，否则需要和头一致 |
| interface_transaction_id | rcv_transactions_interface_s.NEXTVAL |
| processing_status_code | 给PENDING |
| transaction_status_code | 给PENDING |
| processing_mode_code | 给BATCH |
| validation_flag | 给Y |
| receipt_source_code | 给VENDOR |
| vendor_id | 供应商ID |
| source_document_code | 给PO |
| po_header_id | PO头ID |
| po_line_id | PO行ID |
| po_line_location_id | PO发运ID |
| po_release_id | PO Release ID |
| transaction_type | 接收类型，这3个要配合使用；仅接收给RECEIVE |
| auto_transact_code | 自动事务代码，这3个要配合使用；仅接收给RECEIVE |
| destination_type_code | 目标类型，这3个要配合使用；仅接收给RECEIVING |
| parent_transaction_id | 上层接收事务ID，比如检验针对的接收ID；接收放空 |
| item_id | 物料ID |
| transaction_date | 接收日期 |
| Quantity | 数量 |
| unit_of_measure | 单位，名称，非代码 |
| to_organization_id | 接收组织ID |
| ship_to_location_id | 接收地址ID |
| location_id | 地止ID |
| interface_source_code | 随便给，用来追踪 |
| interface_source_line_id | 随便给，用来追踪 |
| 5个who字段 | 习惯上都给 |
| Comments | 会带到MMT表的transaction_reference |
| vendor_lot_num | 会带到MMT表的vendor_lot_number |
rcv_lots_interface
| 字段 | 说明 |
| interface_transaction_id | rcv_transactions_interface.interface_transaction_id |
| quantity | 事务处历数量，事务单位和rcv_transactions_interface一样 |
| primary_quantity | 主数量 |
| item_id | 物料ID |
| lot_num | 批次 |
| transaction_date | 接收日期 |
| 5个who字段 | 习惯上都给 |
rcv_serials_interface
| 字段 | 说明 |
| interface_transaction_id | #NAME? |
| lot_num | #NAME? |
| organization_id | #NAME? |
| item_id | 物料ID |
| transaction_date | 接收日期 |
| fm_serial_num | 开始序列号 |
| to_serial_num | 结束序列号 |
| 5个who字段 | 习惯上都给 |
例子
ASN接收、检验、入库、退货
客制化数据库对象
DROP TABLE XXCUS.xxcus_rd_rcv_headers_t;
-- Create table
create table XXCUS.xxcus_rd_rcv_headers_t
( Rcv_Header_Id NUMBER
,Shipment_Number VARCHAR2(100)
,Shipment_Header_Id NUMBER
,Transaction_Type VARCHAR2(20) --要求的事务处理类型，RECEIVE=接收;DELIVERY=发运;RECEIVE&DELIVERY==从接收到发运
,User_Name VARCHAR2(100)
,Batch_Id NUMBER
,Process_Step VARCHAR2(200) --执行到哪一步,RECEIVE;DELIVERY;ACCEPT;RETURN；
,Process_Step_Name VARCHAR2(200) --执行到哪一步
,Process_Status VARCHAR2(20) --当前步骤的状态
,Process_Message VARCHAR2(2000) --当前步骤的错误信息
,Status VARCHAR2(1)
,Error_Message VARCHAR2(2000)
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER);
CREATE OR REPLACE SYNONYM xxcus_rd_rcv_headers_t FOR xxcus.xxcus_rd_rcv_headers_t;
Drop Sequence Xxcus.xxcus_rd_rcv_headers_s;
CREATE Sequence Xxcus.xxcus_rd_rcv_headers_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_rcv_headers_s FOR Xxcus.xxcus_rd_rcv_headers_s;
DROP TABLE XXCUS.xxcus_rd_rcv_lines_t;
-- Create table
create table XXCUS.xxcus_rd_rcv_lines_t
( Rcv_Header_Id NUMBER
,Rcv_Line_Id NUMBER
,Shipment_Line_Id NUMBER --WMS传递
,Ship_Quantity NUMBER --WMS传递
,Subinventory_Code VARCHAR2(100) --WMS传递
,Locator_Id NUMBER --WMS传递
,Manufacturer_Number VARCHAR2(2000) --制造商--WMS传递
,rt_transaction_id_in NUMBER
,rt_transaction_id NUMBER
,Transaction_Date DATE --WMS传递,不给则默认当前时间
,quality_type VARCHAR2(15) --WMS传递--QUALIFIED=合格,Transaction_Type='ACCEPT'；UNQUALIFIED=不合格,Transaction_Type='REJECT';
,Source_Code VARCHAR2(200) --WMS传递
,Source_Line_Id NUMBER --WMS传递
,Status VARCHAR2(10)
,Error_Message VARCHAR2(2000)
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER);
CREATE OR REPLACE SYNONYM xxcus_rd_rcv_lines_t FOR xxcus.xxcus_rd_rcv_lines_t;
Drop Sequence Xxcus.xxcus_rd_rcv_lines_s;
CREATE Sequence Xxcus.xxcus_rd_rcv_lines_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_rcv_lines_s FOR Xxcus.xxcus_rd_rcv_lines_s;
DROP TABLE XXCUS.xxcus_rd_rcv_lots_t;
-- Create table
create table XXCUS.xxcus_rd_rcv_lots_t
( Rcv_Line_Id NUMBER
,rcv_header_id NUMBER
,rcv_lot_id NUMBER
,Lot_Number VARCHAR2(200)
,Ship_Quantity NUMBER
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER);
CREATE OR REPLACE SYNONYM xxcus_rd_rcv_lots_t FOR xxcus.xxcus_rd_rcv_lots_t;
Drop Sequence Xxcus.xxcus_rd_rcv_lots_s;
CREATE Sequence Xxcus.xxcus_rd_rcv_lots_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_rcv_lots_s FOR Xxcus.xxcus_rd_rcv_lots_s;
DROP TABLE XXCUS.xxcus_rd_rcv_step_t;
-- Create table
create table XXCUS.xxcus_rd_rcv_step_t
( rcv_header_id number,
rcv_step_id NUMBER,--xxcus_rd_step_s
step_code VARCHAR2(200) --执行到哪一步
,Status VARCHAR2(20) --当前步骤的状态
,error_Message VARCHAR2(4000) --当前步骤的错误信息
,Step_Date DATE--该步骤的时间
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER
)
;
CREATE OR REPLACE SYNONYM xxcus_rd_rcv_step_t FOR xxcus.xxcus_rd_rcv_step_t;
-- Create sequence
Drop Sequence Xxcus.xxcus_rd_rcv_step_s;
CREATE Sequence Xxcus.xxcus_rd_rcv_step_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_rcv_step_s FOR Xxcus.xxcus_rd_rcv_step_s;
DROP TABLE XXCUS.xxcus_rd_headers_t;
-- Create table
create table XXCUS.xxcus_rd_headers_t
( Shipment_Number VARCHAR2(100)
,Shipment_Header_Id NUMBER
,Transaction_Type VARCHAR2(20)
,User_Name VARCHAR2(100)
,header_id NUMBER --xxcus_rd_headers_s
,Rcv_Header_Id NUMBER
,Rcv_Step_Id NUMBER
,group_id number--每一个asn是一个group_id,提交api时候作为标识
,Batch_Id NUMBER --同一批处理的数据，可能会有多个asn，共用一个batch_id
,Ou_Id NUMBER
,User_Id NUMBER
,Resp_Id NUMBER
,Resp_Appl_Id NUMBER
,Currency_Code VARCHAR2(50)
,Process_Step VARCHAR2(200) --执行到哪一步
,Process_Step_Name VARCHAR2(200) --执行到哪一步
,Process_Status VARCHAR2(20) --当前步骤的状态
,Process_Message VARCHAR2(2000) --当前步骤的错误信息
,STATUS VARCHAR2(1)--最终结果
/*P=处理中，数据正在被处理或者中间出现异常；需要ERP进行处理
S=处理完成，成功；
E=处理完成，失败，一般是验证未通过，需要WMS再发送报文*/
,ERROR_MESSAGE VARCHAR2(2000)--最终错误
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER
)
;
CREATE OR REPLACE SYNONYM xxcus_rd_headers_t FOR xxcus.xxcus_rd_headers_t;
-- Create sequence
Drop Sequence Xxcus.xxcus_rd_headers_s;
CREATE Sequence Xxcus.xxcus_rd_headers_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_headers_s FOR Xxcus.xxcus_rd_headers_s;
Drop Sequence Xxcus.xxcus_rd_batch_s;
CREATE Sequence Xxcus.xxcus_rd_batch_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_batch_s FOR Xxcus.xxcus_rd_batch_s;
DROP TABLE XXCUS.xxcus_rd_lines_t;
-- Create table
create table XXCUS.xxcus_rd_lines_t
( header_id NUMBER
,Line_Id NUMBER--xxcus_rd_lines_s
,Rcv_Header_Id NUMBER
,Rcv_line_Id NUMBER
,Rcv_Step_Id NUMBER
,Shipment_Line_Id NUMBER
,Ship_Quantity NUMBER
,quality_type VARCHAR2(15)--WMS传递--QUALIFIED=合格,Transaction_Type='ACCEPT'；UNQUALIFIED=不合格,Transaction_Type='REJECT';
,Subinventory_Code VARCHAR2(100)
,Locator_Id NUMBER
,Transaction_Date DATE
,Transaction_Uom VARCHAR2(50)
,po_Uom VARCHAR2(50)
,Manufacturer_Number VARCHAR2(2000) --制造商--WMS传递
,rt_transaction_id NUMBER
,rt_transaction_id_in NUMBER
,Source_Code VARCHAR2(200)
,Source_Line_Id NUMBER
,to_organization_id NUMBER--ERP根据 Shipment_Line_Id抓取
,Inventory_Item_Id NUMBER--ERP根据 Shipment_Line_Id抓取
,Po_Distribution_Id NUMBER--ERP根据 Shipment_Line_Id抓取,rsl好像不存这个数据，需要line_location_id关联pda表获取
,po_line_location_id NUMBER
,po_line_id NUMBER
,Rate_Type VARCHAR2(50)
,Rate_Date DATE
,Rate NUMBER
,Status VARCHAR2(10)
,Error_Message VARCHAR2(2000)
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER
)
;
CREATE OR REPLACE SYNONYM xxcus_rd_lines_t FOR xxcus.xxcus_rd_lines_t;
-- Create sequence
Drop Sequence Xxcus.xxcus_rd_lines_s;
CREATE Sequence Xxcus.xxcus_rd_lines_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_lines_s FOR Xxcus.xxcus_rd_lines_s;
DROP TABLE XXCUS.xxcus_rd_lots_t;
-- Create table
create table XXCUS.xxcus_rd_lots_t
( line_id NUMBER
,header_id NUMBER
,lot_id NUMBER--xxcus_rd_lots_s
,Rcv_Header_Id NUMBER
,Rcv_line_Id NUMBER
,Rcv_lot_Id NUMBER
,Rcv_Step_Id NUMBER
,Lot_Number VARCHAR2(200)
,Ship_Quantity NUMBER
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER
,created_by NUMBER
,creation_date DATE
,last_update_login NUMBER
)
;
CREATE OR REPLACE SYNONYM xxcus_rd_lots_t FOR xxcus.xxcus_rd_lots_t;
-- Create sequence
Drop Sequence Xxcus.xxcus_rd_lots_s;
CREATE Sequence Xxcus.xxcus_rd_lots_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_lots_s FOR Xxcus.xxcus_rd_lots_s;
DROP TABLE XXCUS.xxcus_rd_step_t;
-- Create table
create table XXCUS.xxcus_rd_step_t
( header_id number,
step_id NUMBER,--xxcus_rd_step_s
step_code VARCHAR2(200) --执行到哪一步
,Status VARCHAR2(20) --当前步骤的状态
,error_Message VARCHAR2(4000) --当前步骤的错误信息
,Step_Date DATE--该步骤的时间
)
;
CREATE OR REPLACE SYNONYM xxcus_rd_step_t FOR xxcus.xxcus_rd_step_t;
-- Create sequence
Drop Sequence Xxcus.xxcus_rd_step_s;
CREATE Sequence Xxcus.xxcus_rd_step_s Minvalue 1 Maxvalue 9999999999999999 START
WITH 1 Increment BY 1;
CREATE OR REPLACE Synonym xxcus_rd_step_s FOR Xxcus.xxcus_rd_step_s;
主体程序
包头
CREATE OR REPLACE PACKAGE Xxcus_Rcv_Rd_Handle_Pkg IS
-- Author : BRAYDEN.LIU
-- Created : 2023/1/29 9:47:14
-- Purpose : PO采购接收工具包
--各STEP阶段标识
Gv_Init CONSTANT VARCHAR2(4) := 'INIT';
/*Gv_Rec_Validate CONSTANT VARCHAR2(16) := 'RECEIVE_VALIDATE';
Gv_Receive CONSTANT VARCHAR2(16) := 'RECEIVE_INSERT';
Gv_Del_Validate CONSTANT VARCHAR2(20) := 'DELIVERY_VALIDATE';
Gv_Delivery CONSTANT VARCHAR2(16) := 'DEVLIVER_INSERT';
Gv_Accept_Validate CONSTANT VARCHAR2(20) := 'ACCEPT_VALIDATE';
Gv_Accept CONSTANT VARCHAR2(16) := 'ACCEPT_INSERT';
Gv_Ret_Validate CONSTANT VARCHAR2(16) := 'RETURN_VALIDATE';
Gv_Return CONSTANT VARCHAR2(16) := 'RETURNING';*/
Gv_Validate CONSTANT VARCHAR2(8) := 'VALIDATE';
Gv_Insert_Interface CONSTANT VARCHAR2(16) := 'INSERT_INTERFACE';
Gv_Commit CONSTANT VARCHAR2(6) := 'COMMIT';
--真实的事务处理类型，也是父表step的stepcode,这个英文值不能随意修改，必须和ERP接口的事务处理类型一致
Gv_Process_r CONSTANT VARCHAR2(7) := 'RECEIVE'; --接收
Gv_Process_d CONSTANT VARCHAR2(8) := 'DELIVER'; --入库
Gv_Process_Rv CONSTANT VARCHAR2(16) := 'RETURN TO VENDOR'; --退货
Gv_Process_a CONSTANT VARCHAR2(6) := 'ACCEPT'; --退货
--状态标识
Gv_Status_p CONSTANT VARCHAR2(7) := 'P'; --进行中 pending
Gv_Status_s CONSTANT VARCHAR2(1) := 'S'; --成功 success
Gv_Status_e CONSTANT VARCHAR2(1) := 'E'; --失败 error
Gv_Status_w CONSTANT VARCHAR2(1) := 'W'; --等待处理 wait
--接口事务处理类型标识
Gv_Trntype_Receive CONSTANT VARCHAR2(10) := 'RECEIVE';
Gv_Trntype_Accept CONSTANT VARCHAR2(10) := 'ACCEPT';
Gv_Trntype_Delivery CONSTANT VARCHAR2(10) := 'DELIVERY';
Gv_Trntype_Rd CONSTANT VARCHAR2(20) := 'RECEIVE-DELIVERY';
Gv_Trntype_Ad CONSTANT VARCHAR2(20) := 'ACCEPT-DELIVERY';
Gv_Trntype_Returnv CONSTANT VARCHAR2(20) := 'RETURN';
--提交请求的方法标识
Gv_Trn_Online_Type CONSTANT VARCHAR2(10) := 'ONLINE';
Gv_Trn_Batch_Type CONSTANT VARCHAR2(10) := 'BATCH';
--
Gv_Interface_Source_Code CONSTANT VARCHAR2(9) := 'XXCUS-RCV';
--接口传入数据类型
--------实际事务处理类型
TYPE Shipment_Lot_Record IS RECORD(
Lot_Id NUMBER
,Line_Id NUMBER
,Header_Id NUMBER
,Rcv_Process_Code VARCHAR2(50)
,Rcv_Header_Id NUMBER
,Rcv_Line_Id NUMBER
,Rcv_Lot_Id NUMBER
,Rcv_Step_Id NUMBER
--xxcus_rd_lots_s
,Lot_Number VARCHAR2(200)
,Ship_Quantity NUMBER);
TYPE Shipment_Lot_Table IS TABLE OF Shipment_Lot_Record INDEX BY BINARY_INTEGER;
TYPE Shipment_Line_Record IS RECORD(
Header_Id NUMBER
,Line_Id NUMBER
,Rcv_Process_Code VARCHAR2(50)
,Rcv_Header_Id NUMBER
,Rcv_Line_Id NUMBER
,Rcv_Step_Id NUMBER
,Shipment_Line_Id NUMBER --WMS传递
,Quality_Type VARCHAR2(15) --WMS传递--QUALIFIED=合格,Transaction_Type='ACCEPT'；UNQUALIFIED=不合格,Transaction_Type='REJECT';
,Ship_Quantity NUMBER --WMS传递
,Subinventory_Code VARCHAR2(100) --WMS传递
,Locator_Id NUMBER --WMS传递
,Manufacturer_Number VARCHAR2(2000) --制造商--WMS传递
,Transaction_Date DATE --WMS传递,不给则默认当前时间
,Transaction_Uom VARCHAR2(50) --WMS原接口逻辑为直接去物料主单位
,Po_Uom VARCHAR2(50) --程序取po行的单位，如果与主单位有差异，需要做数量转换，因为WMS原接口只传递数量，单位为主单位数量
,Currency_Code VARCHAR2(50)
,Rate_Type VARCHAR2(50)
,Rate_Date DATE
,Rate NUMBER
,Base_Currency_Code VARCHAR2(50) --OU所属账簿的本位币
,Source_Code VARCHAR2(200) --WMS传递
,Source_Line_Id NUMBER --WMS传递
,Rt_Transaction_Id_In NUMBER --检验、入库、退货需要该字段
,To_Organization_Id NUMBER --ERP根据 Shipment_Line_Id抓取
,Inventory_Item_Id NUMBER --ERP根据 Shipment_Line_Id抓取
,Inspection_Required_Flag VARCHAR2(1) ----该物料是否为要求检验
,Po_Header_Id NUMBER
,Po_Release_Id NUMBER
,Category_Id NUMBER
,Item_Description VARCHAR2(240)
,Ship_To_Location_Id NUMBER
,Location_Id NUMBER
,Po_Distribution_Id NUMBER --ERP根据 Shipment_Line_Id抓取
,Po_Line_Location_Id NUMBER
,Po_Line_Id NUMBER
,Po_Unit_Price NUMBER
,Employee_Id NUMBER
,Rt_Transaction_Id NUMBER --生成实际的本次rt 的id,ERP api成功后给该字段赋值
,Rt_Interface_Id NUMBER --接口Interface_Transaction_Id，如果报错可以用这个id查询rt的接口表
,Status VARCHAR2(10)
,Error_Message VARCHAR2(2000)
,Shipment_Lot_Tbl Shipment_Lot_Table);
TYPE Shipment_Lines_Table IS TABLE OF Shipment_Line_Record INDEX BY BINARY_INTEGER;
--TYPE Shipment_Step_Table IS TABLE OF Step_Record INDEX BY VARCHAR2(50);
TYPE Shipment_Header_Record IS RECORD(
Header_Id NUMBER --每一个asn是一个group_id
,Rcv_Header_Id NUMBER
--,Rcv_Step_Id NUMBER
,Shipment_Number VARCHAR2(100)
,Shipment_Header_Id NUMBER
,Rcv_Step_Id NUMBER --插入表使用数据
,Transaction_Type VARCHAR2(20) --Transaction_Type事务处理类型：RECEIVE;DELIVERY;ACCEPT;RETURN；Rcv_Header_Record.step_tbl的step_code
,Commit_Type VARCHAR2(10) --在线提交还是请求提交
,User_Name VARCHAR2(100)
,Group_Id NUMBER
,Batch_Id NUMBER --同一批处理的数据，可能会有多个asn，共用一个batch_id
,Ou_Id NUMBER
,Vendor_Id NUMBER
,Vendor_Site_Id NUMBER
,User_Id NUMBER
,Resp_Id NUMBER
,Resp_Appl_Id NUMBER
,Parent_Transaction_Type VARCHAR2(30) --接口传递进来的事务处处理类型，RECEIVE=接收;DELIVERY=发运;RECEIVE&DELIVERY==从接收到发运;用来判断使用哪种校验逻辑
,Process_Step VARCHAR2(200) --执行到哪一步
,Process_Step_Name VARCHAR2(200) --执行到哪一步
,Process_Status VARCHAR2(20) --当前步骤的状态
,Process_Message VARCHAR2(2000) --当前步骤的错误信息
,Status VARCHAR2(1)
,Error_Message VARCHAR2(2000)
,Shipment_Lines Shipment_Lines_Table);
--TYPE Shipment_Header_Table IS TABLE OF Shipment_Header_Record INDEX BY BINARY_INTEGER;
----------------
TYPE Rcv_Step_Record IS RECORD(
Rcv_Step_Id NUMBER
,Rcv_Header_Id NUMBER
,Step_Code VARCHAR2(200) --执行到哪一步
,Status VARCHAR2(20) --当前步骤的状态
,Error_Message VARCHAR2(4000) --当前步骤的错误信息
,Step_Date DATE --该步骤的时间
,Shipment_Header_Rec Shipment_Header_Record);
TYPE Rcv_Step_Table IS TABLE OF Rcv_Step_Record INDEX BY BINARY_INTEGER;
TYPE Rcv_Lot_Record IS RECORD(
Rcv_Lot_Id NUMBER
,Rcv_Header_Id NUMBER
,Rcv_Line_Id NUMBER
,Lot_Number VARCHAR2(200)
,Ship_Quantity NUMBER);
TYPE Rcv_Lot_Table IS TABLE OF Rcv_Lot_Record INDEX BY BINARY_INTEGER;
TYPE Rcv_Line_Record IS RECORD(
Rcv_Header_Id NUMBER
,Rcv_Line_Id NUMBER
,Shipment_Line_Id NUMBER --WMS传递
,Ship_Quantity NUMBER --WMS传递
,Subinventory_Code VARCHAR2(100) --WMS传递
,Locator_Id NUMBER --WMS传递
,Manufacturer_Number VARCHAR2(2000) --制造商--WMS传递
,Transaction_Date DATE --WMS传递,不给则默认当前时间
,Quality_Type VARCHAR2(15) --WMS传递--QUALIFIED=合格,Transaction_Type='ACCEPT'；UNQUALIFIED=不合格,Transaction_Type='REJECT';
,Rt_Transaction_Id_In NUMBER --WMS传递，或者在接收的时候生成，上一阶段的rt transaction_id
,Rt_Transaction_Id NUMBER --ERP生成的本次的rt transaction id
,Source_Code VARCHAR2(200) --WMS传递
,Source_Line_Id NUMBER --WMS传递
,Status VARCHAR2(10)
,Error_Message VARCHAR2(2000)
,Shipment_Lot_Tbl Rcv_Lot_Table);
TYPE Rcv_Lines_Table IS TABLE OF Rcv_Line_Record INDEX BY BINARY_INTEGER;
TYPE Rcv_Header_Record IS RECORD(
Rcv_Header_Id NUMBER
,Shipment_Number VARCHAR2(100)
,Shipment_Header_Id NUMBER
,Transaction_Type VARCHAR2(20) --要求的事务处理类型，RECEIVE=接收;DELIVERY=发运;RECEIVE&DELIVERY==从接收到发运
,User_Name VARCHAR2(100)
,Batch_Id NUMBER
,Process_Step VARCHAR2(200) --执行到哪一步,RECEIVE;DELIVERY;ACCEPT;RETURN；
,Process_Step_Id NUMBER --step的key
,Process_Status VARCHAR2(20) --当前步骤的状态
,Process_Message VARCHAR2(2000) --当前步骤的错误信息
,Status VARCHAR2(1)
,Error_Message VARCHAR2(2000)
,Rcv_Lines Rcv_Lines_Table
,Rcv_Steps Rcv_Step_Table);
--采购接收事务处理
PROCEDURE Rcv_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
END Xxcus_Rcv_Rd_Handle_Pkg;
Open Inerface最简单例子（新建接收头和接收）（11.5.9 GITI环境测试通过）
DECLARE
l_iface_hdr_rec po.rcv_headers_interface%ROWTYPE;
l_iface_rcv_rec po.rcv_transactions_interface%ROWTYPE;
l_cur_mfg_org_id NUMBER := 82; --Current Inv Organization
l_user_id NUMBER := 0; --User ID?Sysadmin
BEGIN
l_iface_hdr_rec.last_update_date := SYSDATE;
l_iface_hdr_rec.last_updated_by := l_user_id;
l_iface_hdr_rec.creation_date := SYSDATE;
l_iface_hdr_rec.created_by := l_user_id;
l_iface_hdr_rec.last_update_login := -1;
l_iface_rcv_rec.last_update_date := SYSDATE;
l_iface_rcv_rec.last_updated_by := l_user_id;
l_iface_rcv_rec.creation_date := SYSDATE;
l_iface_rcv_rec.created_by := l_user_id;
l_iface_rcv_rec.last_update_login := -1;
SELECT po.rcv_headers_interface_s.NEXTVAL
INTO l_iface_hdr_rec.header_interface_id
FROM dual;
SELECT po.rcv_interface_groups_s.NEXTVAL
INTO l_iface_hdr_rec.group_id
FROM dual;
l_iface_hdr_rec.processing_status_code := 'PENDING';
l_iface_hdr_rec.receipt_source_code := 'VENDOR';
l_iface_hdr_rec.transaction_type := 'NEW';
l_iface_hdr_rec.auto_transact_code := 'RECEIVE';
l_iface_hdr_rec.receipt_num := NULL; --Automatic Number
l_iface_hdr_rec.vendor_id := 29843;
l_iface_hdr_rec.vendor_site_id := 765; --Optional
l_iface_hdr_rec.expected_receipt_date := SYSDATE;
l_iface_hdr_rec.validation_flag := 'Y';
l_iface_hdr_rec.ship_to_organization_id := l_cur_mfg_org_id;
INSERT INTO po.rcv_headers_interface VALUES l_iface_hdr_rec;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_iface_rcv_rec.interface_transaction_id
FROM dual;
l_iface_rcv_rec.header_interface_id := l_iface_hdr_rec.header_interface_id;
l_iface_rcv_rec.group_id := l_iface_hdr_rec.group_id;
l_iface_rcv_rec.processing_status_code := 'PENDING';
l_iface_rcv_rec.transaction_status_code := 'PENDING';
l_iface_rcv_rec.processing_mode_code := 'BATCH';
l_iface_rcv_rec.validation_flag := 'Y';
l_iface_rcv_rec.receipt_source_code := 'VENDOR';
l_iface_rcv_rec.vendor_id := 29843;
l_iface_rcv_rec.vendor_site_id := 765; --Optional
l_iface_rcv_rec.source_document_code := 'PO';
l_iface_rcv_rec.po_header_id := 148552;
l_iface_rcv_rec.po_line_id := 1062557;
l_iface_rcv_rec.po_line_location_id := 731140;
l_iface_rcv_rec.po_release_id := NULL;
l_iface_rcv_rec.transaction_type := 'RECEIVE';
l_iface_rcv_rec.auto_transact_code := 'RECEIVE';
l_iface_rcv_rec.destination_type_code := 'RECEIVING';
l_iface_rcv_rec.category_id := 395;
l_iface_rcv_rec.item_id := 18753;
l_iface_rcv_rec.transaction_date := SYSDATE;
l_iface_rcv_rec.quantity := 1.11;
l_iface_rcv_rec.unit_of_measure := '件'; --Not Code
l_iface_rcv_rec.to_organization_id := l_cur_mfg_org_id;
l_iface_rcv_rec.ship_to_location_id := 141;
l_iface_rcv_rec.location_id := 141;
l_iface_rcv_rec.interface_source_code := 'Just Test';
l_iface_rcv_rec.interface_source_line_id := '987654321';
INSERT INTO po.rcv_transactions_interface VALUES l_iface_rcv_rec;
dbms_output.put_line('group_id:' || l_iface_rcv_rec.group_id);
END;
包体
CREATE OR REPLACE PACKAGE BODY Xxcus_Rcv_Rd_Handle_Pkg IS
Gv_Package_Name VARCHAR2(200) := 'Xxcus_Rcv_Rd_Handle_Pkg.'; --当前程序包名称
PROCEDURE Show_Log(Pv_Str IN VARCHAR2) IS
BEGIN
Dbms_Output.Put_Line(Pv_Str);
END Show_Log;
FUNCTION Validate_Step_Status(Pr_Header_Rec IN Shipment_Header_Record) RETURN BOOLEAN IS
BEGIN
--验证当前步骤是否正确，如果不正确，则不会进行下一步
IF Pr_Header_Rec.Process_Status != Gv_Status_s THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END Validate_Step_Status;
--获取本位币信息
FUNCTION Get_Base_Currency_Code(Pn_Ou_Id IN NUMBER) RETURN Gl_Ledgers.Currency_Code%TYPE Result_Cache IS
v_Base_Currency_Code Gl_Ledgers.Currency_Code%TYPE;
BEGIN
SELECT l.Currency_Code
INTO v_Base_Currency_Code
FROM Gl_Ledgers l
,Hr_Operating_Units Hou
WHERE l.Ledger_Id = Hou.Set_Of_Books_Id
AND Hou.Organization_Id = Pn_Ou_Id;
RETURN v_Base_Currency_Code;
EXCEPTION
WHEN OTHERS THEN
RETURN v_Base_Currency_Code;
END Get_Base_Currency_Code;
--初始化采购接收数据
PROCEDURE Init_Receive(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Pr_Shipment_Line_Rec IN OUT Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Init_Receive';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化组织id等信息';
SELECT Rsh.Shipment_Header_Id
,Pha.Org_Id
,Rsh.Shipment_Num
,Rsh.Vendor_Id
,Rsh.Vendor_Site_Id
,Pda.Destination_Organization_Id
,Pha.Currency_Code
,Pha.Rate_Type
--,Pha.Rate_Date
,Pha.Rate
,Pla.Item_Id
,Nvl(Msib.Inspection_Required_Flag, 'N')
,Pda.Po_Distribution_Id
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Unit_Of_Measure --采购订单的单位，实际接口传递时按照主单位传递的数量，所以可能需要转换
--原WMS单位转换使用的是po 行的单位，本次修改认为ASN接收参考shipment的单位更合理
,Msib.Primary_Uom_Code
--,Plla.Receiving_Routing_Id
,Pla.Unit_Price
,Rsl.Item_Description
,Rsl.Ship_To_Location_Id
,Rsl.Ship_To_Location_Id
,Pda.Po_Distribution_Id
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Po_Header_Id
,Rsl.Category_Id
,Rsl.Po_Release_Id
INTO Pr_Shipment_Header_Rec.Shipment_Header_Id
,Pr_Shipment_Header_Rec.Ou_Id
,Pr_Shipment_Header_Rec.Shipment_Number
,Pr_Shipment_Header_Rec.Vendor_Id
,Pr_Shipment_Header_Rec.Vendor_Site_Id
,Pr_Shipment_Line_Rec.To_Organization_Id
,Pr_Shipment_Line_Rec.Currency_Code
,Pr_Shipment_Line_Rec.Rate_Type
,Pr_Shipment_Line_Rec.Rate
,Pr_Shipment_Line_Rec.Inventory_Item_Id
,Pr_Shipment_Line_Rec.Inspection_Required_Flag
,Pr_Shipment_Line_Rec.Po_Distribution_Id
,Pr_Shipment_Line_Rec.Po_Line_Location_Id
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Uom
,Pr_Shipment_Line_Rec.Transaction_Uom
,Pr_Shipment_Line_Rec.Po_Unit_Price
,Pr_Shipment_Line_Rec.Item_Description
,Pr_Shipment_Line_Rec.Ship_To_Location_Id
,Pr_Shipment_Line_Rec.Location_Id
,Pr_Shipment_Line_Rec.Po_Distribution_Id --ERP根据 Shipment_Line_Id抓取
,Pr_Shipment_Line_Rec.Po_Line_Location_Id
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Header_Id
,Pr_Shipment_Line_Rec.Category_Id
,Pr_Shipment_Line_Rec.Po_Release_Id
FROM Rcv_Shipment_Headers Rsh
,Rcv_Shipment_Lines Rsl
,Po_Headers_All Pha
,Po_Distributions_All Pda
,Po_Lines_All Pla
,Po_Line_Locations_All Plla
,Mtl_System_Items_b Msib
WHERE 1 = 1
AND Pda.Destination_Organization_Id = Msib.Organization_Id
AND Pla.Item_Id = Msib.Inventory_Item_Id
AND Rsl.Po_Line_Location_Id = Plla.Line_Location_Id
AND Rsl.Po_Line_Id = Pla.Po_Line_Id
AND Rsl.Po_Line_Location_Id = Pda.Line_Location_Id
AND Rsl.Po_Header_Id = Pha.Po_Header_Id
AND Rsh.Shipment_Header_Id = Rsl.Shipment_Header_Id
AND Rsl.Shipment_Line_Id = Pr_Shipment_Line_Rec.Shipment_Line_Id;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Init_Receive;
--初始接收购接收数据
PROCEDURE Init_Accept(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Pr_Shipment_Line_Rec IN OUT Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Init_Accept';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Rsh.Shipment_Header_Id
,Pha.Org_Id
,Rsh.Shipment_Num
,Rsh.Vendor_Id
,Rsh.Vendor_Site_Id
,Rsl.To_Organization_Id
,Rt.Currency_Code
,Rt.Currency_Conversion_Type
,Rt.Currency_Conversion_Rate
,Rsl.Item_Id
,Rsl.Po_Distribution_Id
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Unit_Of_Measure --采购订单的单位，实际接口传递时按照主单位传递的数量，所以可能需要转换
--原WMS单位转换使用的是po 行的单位，本次修改认为ASN接收参考shipment的单位更合理
,Msib.Primary_Uom_Code
--,Plla.Receiving_Routing_Id
,Rt.Po_Unit_Price
,Rsl.Item_Description
,Rsl.Ship_To_Location_Id
,Rsl.Ship_To_Location_Id
,Rsl.Po_Distribution_Id
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Po_Header_Id
,Rsl.Category_Id
,Rsl.Po_Release_Id
,Rt.Employee_Id
,Msib.Inspection_Required_Flag
INTO Pr_Shipment_Header_Rec.Shipment_Header_Id
,Pr_Shipment_Header_Rec.Ou_Id
,Pr_Shipment_Header_Rec.Shipment_Number
,Pr_Shipment_Header_Rec.Vendor_Id
,Pr_Shipment_Header_Rec.Vendor_Site_Id
,Pr_Shipment_Line_Rec.To_Organization_Id
,Pr_Shipment_Line_Rec.Currency_Code
,Pr_Shipment_Line_Rec.Rate_Type
,Pr_Shipment_Line_Rec.Rate
,Pr_Shipment_Line_Rec.Inventory_Item_Id
,Pr_Shipment_Line_Rec.Po_Distribution_Id
,Pr_Shipment_Line_Rec.Po_Line_Location_Id
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Uom
,Pr_Shipment_Line_Rec.Transaction_Uom
,Pr_Shipment_Line_Rec.Po_Unit_Price
,Pr_Shipment_Line_Rec.Item_Description
,Pr_Shipment_Line_Rec.Ship_To_Location_Id
,Pr_Shipment_Line_Rec.Location_Id
,Pr_Shipment_Line_Rec.Po_Distribution_Id --ERP根据 Shipment_Line_Id抓取
,Pr_Shipment_Line_Rec.Po_Line_Location_Id
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Header_Id
,Pr_Shipment_Line_Rec.Category_Id
,Pr_Shipment_Line_Rec.Po_Release_Id
,Pr_Shipment_Line_Rec.Employee_Id
,Pr_Shipment_Line_Rec.Inspection_Required_Flag
FROM Rcv_Shipment_Headers Rsh
,Rcv_Shipment_Lines Rsl
,Po_Headers_All Pha
,Po_Line_Locations_All Plla
,Mtl_System_Items_b Msib
,Rcv_Transactions Rt
,Rcv_Supply Rs
WHERE 1 = 1
AND Rsl.To_Organization_Id = Msib.Organization_Id
AND Rsl.Item_Id = Msib.Inventory_Item_Id
AND Rsl.Po_Line_Location_Id = Plla.Line_Location_Id
AND Rsl.Po_Header_Id = Pha.Po_Header_Id
AND 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 Rsh.Shipment_Header_Id = Rsl.Shipment_Header_Id
AND Rsl.Routing_Header_Id = 2
AND Rsl.Shipment_Line_Id = Pr_Shipment_Line_Rec.Shipment_Line_Id
AND Nvl(Msib.Inspection_Required_Flag, 'N') = 'Y' --要求检验会进入检验步骤
AND Rt.Transaction_Id = Pr_Shipment_Line_Rec.Rt_Transaction_Id_In; --可能有多次接收，但是数据应该是一样的
EXCEPTION
WHEN No_Data_Found THEN
--未找到数据，则不需要检验
Pr_Shipment_Line_Rec.Status := 'N'; --不需要检验
Pr_Shipment_Line_Rec.Error_Message := '该物料无需检验'; --不需要检验
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Init_Accept;
--初始接收购接收数据
PROCEDURE Init_Delivery(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Pr_Shipment_Line_Rec IN OUT Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Init_Delivery';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Rsh.Shipment_Header_Id
,Pha.Org_Id
,Rsh.Shipment_Num
,Rsh.Vendor_Id
,Rsh.Vendor_Site_Id
,Rsl.To_Organization_Id
,Rt.Currency_Code
,Rt.Currency_Conversion_Type
,Rt.Currency_Conversion_Rate
,Rsl.Item_Id
--,Rsl.Po_Distribution_Id
--,Rsl.Po_Line_Location_Id
--,Rsl.Po_Line_Id
,Rsl.Unit_Of_Measure --采购订单的单位，实际接口传递时按照主单位传递的数量，所以可能需要转换
--原WMS单位转换使用的是po 行的单位，本次修改认为ASN接收参考shipment的单位更合理
,Msib.Primary_Uom_Code
--,Plla.Receiving_Routing_Id
,Rt.Po_Unit_Price
,Rsl.Item_Description
,Rsl.Ship_To_Location_Id
,Rsl.Ship_To_Location_Id
,Pda.Po_Distribution_Id --Rsl.Po_Distribution_Id--rsl里面没有这个值，需要关联pda取值
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Po_Header_Id
,Rsl.Category_Id
,Rsl.Po_Release_Id
,Rt.Employee_Id
,Msib.Inspection_Required_Flag
INTO Pr_Shipment_Header_Rec.Shipment_Header_Id
,Pr_Shipment_Header_Rec.Ou_Id
,Pr_Shipment_Header_Rec.Shipment_Number
,Pr_Shipment_Header_Rec.Vendor_Id
,Pr_Shipment_Header_Rec.Vendor_Site_Id
,Pr_Shipment_Line_Rec.To_Organization_Id
,Pr_Shipment_Line_Rec.Currency_Code
,Pr_Shipment_Line_Rec.Rate_Type
,Pr_Shipment_Line_Rec.Rate
,Pr_Shipment_Line_Rec.Inventory_Item_Id
--,Pr_Shipment_Line_Rec.Po_Distribution_Id
--,Pr_Shipment_Line_Rec.Po_Line_Location_Id
--,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Uom
,Pr_Shipment_Line_Rec.Transaction_Uom
,Pr_Shipment_Line_Rec.Po_Unit_Price
,Pr_Shipment_Line_Rec.Item_Description
,Pr_Shipment_Line_Rec.Ship_To_Location_Id
,Pr_Shipment_Line_Rec.Location_Id
,Pr_Shipment_Line_Rec.Po_Distribution_Id --ERP根据 Shipment_Line_Id抓取
,Pr_Shipment_Line_Rec.Po_Line_Location_Id --如果是接收入库，这个字段必须有值
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Header_Id
,Pr_Shipment_Line_Rec.Category_Id
,Pr_Shipment_Line_Rec.Po_Release_Id
,Pr_Shipment_Line_Rec.Employee_Id
,Pr_Shipment_Line_Rec.Inspection_Required_Flag
FROM Rcv_Shipment_Headers Rsh
,Rcv_Shipment_Lines Rsl
,Po_Headers_All Pha
,Po_Line_Locations_All Plla
,Mtl_System_Items_b Msib
,Rcv_Transactions Rt
,Rcv_Supply Rs
,Po_Distributions_All Pda
WHERE 1 = 1
AND Plla.Line_Location_Id = Pda.Line_Location_Id
AND Rsl.To_Organization_Id = Msib.Organization_Id
AND Rsl.Item_Id = Msib.Inventory_Item_Id
AND Rsl.Po_Line_Location_Id = Plla.Line_Location_Id
AND Rsl.Po_Header_Id = Pha.Po_Header_Id
AND 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 Rsh.Shipment_Header_Id = Rsl.Shipment_Header_Id
-- AND Rsl.Routing_Header_Id = 2
AND Rsl.Shipment_Line_Id = Pr_Shipment_Line_Rec.Shipment_Line_Id
AND Rt.Transaction_Id = Pr_Shipment_Line_Rec.Rt_Transaction_Id_In; --可能有多次接收，但是数据应该是一样的
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Init_Delivery;
--初始退回到供应商
PROCEDURE Init_Returnv(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Pr_Shipment_Line_Rec IN OUT Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Init_Returnv';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Rsh.Shipment_Header_Id
,Pha.Org_Id
,Rsh.Shipment_Num
,Rsh.Vendor_Id
,Rsh.Vendor_Site_Id
,Rsl.To_Organization_Id
,Rt.Currency_Code
,Rt.Currency_Conversion_Type
,Rt.Currency_Conversion_Rate
,Rsl.Item_Id
,Rsl.Unit_Of_Measure --采购订单的单位，实际接口传递时按照主单位传递的数量，所以可能需要转换
--原WMS单位转换使用的是po 行的单位，本次修改认为ASN接收参考shipment的单位更合理
,Msib.Primary_Uom_Code
--,Plla.Receiving_Routing_Id
,Rt.Po_Unit_Price
,Rsl.Item_Description
--,Rsl.Ship_To_Location_Id
--,Rsl.Ship_To_Location_Id
,Pda.Po_Distribution_Id --Rsl.Po_Distribution_Id--rsl里面没有这个值，需要关联pda取值
,Rsl.Po_Line_Location_Id
,Rsl.Po_Line_Id
,Rsl.Po_Header_Id
,Rsl.Category_Id
,Rsl.Po_Release_Id
,Rt.Employee_Id
,Msib.Inspection_Required_Flag
INTO Pr_Shipment_Header_Rec.Shipment_Header_Id
,Pr_Shipment_Header_Rec.Ou_Id
,Pr_Shipment_Header_Rec.Shipment_Number
,Pr_Shipment_Header_Rec.Vendor_Id
,Pr_Shipment_Header_Rec.Vendor_Site_Id
,Pr_Shipment_Line_Rec.To_Organization_Id
,Pr_Shipment_Line_Rec.Currency_Code
,Pr_Shipment_Line_Rec.Rate_Type
,Pr_Shipment_Line_Rec.Rate
,Pr_Shipment_Line_Rec.Inventory_Item_Id
,Pr_Shipment_Line_Rec.Po_Uom
,Pr_Shipment_Line_Rec.Transaction_Uom
,Pr_Shipment_Line_Rec.Po_Unit_Price
,Pr_Shipment_Line_Rec.Item_Description
--,Pr_Shipment_Line_Rec.Ship_To_Location_Id
--,Pr_Shipment_Line_Rec.Location_Id
,Pr_Shipment_Line_Rec.Po_Distribution_Id --ERP根据 Shipment_Line_Id抓取
,Pr_Shipment_Line_Rec.Po_Line_Location_Id --如果是接收入库，这个字段必须有值
,Pr_Shipment_Line_Rec.Po_Line_Id
,Pr_Shipment_Line_Rec.Po_Header_Id
,Pr_Shipment_Line_Rec.Category_Id
,Pr_Shipment_Line_Rec.Po_Release_Id
,Pr_Shipment_Line_Rec.Employee_Id
,Pr_Shipment_Line_Rec.Inspection_Required_Flag
FROM Rcv_Shipment_Headers Rsh
,Rcv_Shipment_Lines Rsl
,Po_Headers_All Pha
,Po_Line_Locations_All Plla
,Mtl_System_Items_b Msib
,Rcv_Transactions Rt
--,Rcv_Supply Rs
,Po_Distributions_All Pda
WHERE 1 = 1
AND Plla.Line_Location_Id = Pda.Line_Location_Id
AND Rsl.To_Organization_Id = Msib.Organization_Id
AND Rsl.Item_Id = Msib.Inventory_Item_Id
AND Rsl.Po_Line_Location_Id = Plla.Line_Location_Id
AND Rsl.Po_Header_Id = Pha.Po_Header_Id
AND 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 Rsh.Shipment_Header_Id = Rsl.Shipment_Header_Id
-- AND Rsl.Routing_Header_Id = 2
AND Rsl.Shipment_Line_Id = Pr_Shipment_Line_Rec.Shipment_Line_Id
AND Rt.Transaction_Id = Pr_Shipment_Line_Rec.Rt_Transaction_Id_In; --可能有多次接收，但是数据应该是一样的
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Init_Returnv;
--初始化采购接收数据
PROCEDURE Init_Transaction(Pr_Rcv_Header_Rec IN Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Init_Transaction';
Lv_Process_Step VARCHAR2(3000);
Ln_Last_Header_Id NUMBER := -1;
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
--Pr_Shipment_Header_Rec Shipment_Header_Record;
Shipment_Lines_Tbl Shipment_Lines_Table;
Shipment_Lot_Tbl Shipment_Lot_Table;
Le_Too_Many_Asn EXCEPTION;
Le_Insert_Err EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
--Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Pr_Shipment_Header_Rec.Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Header_Id;
Pr_Shipment_Header_Rec.Rcv_Step_Id := Pr_Rcv_Header_Rec.Process_Step_Id;
Pr_Shipment_Header_Rec.Parent_Transaction_Type := Pr_Rcv_Header_Rec.Transaction_Type;
Pr_Shipment_Header_Rec.Transaction_Type := Pr_Rcv_Header_Rec.Process_Step; --Pr_Rcv_Header_Rec.Transaction_Type;
--得先给id因为父表的step是以str做key的，插入表不一定是按照执行顺序插入的
/* SELECT Xxcus_Rd_Headers_s.Nextval
INTO Pr_Shipment_Header_Rec.Header_Id
FROM Dual;*/
Pr_Shipment_Header_Rec.Shipment_Number := Pr_Rcv_Header_Rec.Shipment_Number;
Pr_Shipment_Header_Rec.Shipment_Header_Id := Pr_Rcv_Header_Rec.Shipment_Header_Id;
Pr_Shipment_Header_Rec.User_Name := Pr_Rcv_Header_Rec.User_Name;
Pr_Shipment_Header_Rec.Commit_Type := Gv_Trn_Online_Type; --在线执行
Pr_Shipment_Header_Rec.User_Id := 16786; --后面使用autorun用户
Pr_Shipment_Header_Rec.Resp_Id := 58560;
Pr_Shipment_Header_Rec.Resp_Appl_Id := 20003;
Pr_Shipment_Header_Rec.Process_Step := Gv_Init;
Pr_Shipment_Header_Rec.Status := Gv_Status_p; --等待处理
/*
TODO: owner="brayden.liu" created="2023/1/29"
text="初始化接收入库的数据"
*/
FOR i IN 1 .. Pr_Rcv_Header_Rec.Rcv_Lines.Count LOOP
Xxcus_Fnd_Message.Init_Msg;
Shipment_Lines_Tbl(i).Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Header_Id;
Shipment_Lines_Tbl(i).Rcv_Line_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Line_Id;
Shipment_Lines_Tbl(i).Rcv_Step_Id := Pr_Shipment_Header_Rec.Rcv_Step_Id;
Shipment_Lines_Tbl(i).Shipment_Line_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Line_Id;
Shipment_Lines_Tbl(i).Ship_Quantity := Pr_Rcv_Header_Rec.Rcv_Lines(i).Ship_Quantity;
Shipment_Lines_Tbl(i).Subinventory_Code := Pr_Rcv_Header_Rec.Rcv_Lines(i).Subinventory_Code;
Shipment_Lines_Tbl(i).Locator_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Locator_Id;
Shipment_Lines_Tbl(i).Manufacturer_Number := Pr_Rcv_Header_Rec.Rcv_Lines(i)
.Manufacturer_Number;
Shipment_Lines_Tbl(i).Transaction_Date := Pr_Rcv_Header_Rec.Rcv_Lines(i).Transaction_Date;
Shipment_Lines_Tbl(i).Source_Code := Pr_Rcv_Header_Rec.Rcv_Lines(i).Source_Code;
Shipment_Lines_Tbl(i).Source_Line_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Source_Line_Id;
Shipment_Lines_Tbl(i).Rt_Transaction_Id_In := Pr_Rcv_Header_Rec.Rcv_Lines(i)
.Rt_Transaction_Id_In; --WMS传递或者接收的时候生成的
Shipment_Lot_Tbl.Delete;
FOR j IN 1 .. Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl.Count LOOP
Shipment_Lot_Tbl(j).Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Header_Id;
Shipment_Lot_Tbl(j).Rcv_Line_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Line_Id;
Shipment_Lot_Tbl(j).Rcv_Lot_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j)
.Rcv_Lot_Id;
Shipment_Lot_Tbl(j).Rcv_Step_Id := Pr_Shipment_Header_Rec.Rcv_Step_Id;
Shipment_Lot_Tbl(j).Lot_Number := Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j)
.Lot_Number;
Shipment_Lot_Tbl(j).Ship_Quantity := Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j)
.Ship_Quantity;
END LOOP;
Shipment_Lines_Tbl(i).Shipment_Lot_Tbl := Shipment_Lot_Tbl;
--初始化行状态
Shipment_Lines_Tbl(i).Status := Gv_Status_p;
/*SELECT Xxcus_Rd_Lines_s.Nextval
INTO Shipment_Lines_Tbl(i).Line_Id
FROM Dual;*/ --需要先赋值，插入interface才能有source_line_id
Lv_Process_Step := '初始化组织id等信息';
IF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_r THEN
Init_Receive(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pr_Shipment_Line_Rec => Shipment_Lines_Tbl(i)
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_a THEN
Shipment_Lines_Tbl(i).Quality_Type := Pr_Rcv_Header_Rec.Rcv_Lines(i).Quality_Type;
Init_Accept(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pr_Shipment_Line_Rec => Shipment_Lines_Tbl(i)
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_d THEN
Init_Delivery(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pr_Shipment_Line_Rec => Shipment_Lines_Tbl(i)
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_Rv THEN
Init_Returnv(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pr_Shipment_Line_Rec => Shipment_Lines_Tbl(i)
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;
IF Shipment_Lines_Tbl(i).Rate_Type = 'Corporate' THEN
--汇率逻辑参考 WMS原接口逻辑Xxcus_Wms_Process_Pkg.Currency_Conversion_Pro
Lv_Process_Step := '获取本位币';
Shipment_Lines_Tbl(i).Base_Currency_Code := Get_Base_Currency_Code(Pn_Ou_Id => Pr_Shipment_Header_Rec.Ou_Id);
--处理汇率
IF Shipment_Lines_Tbl(i).Base_Currency_Code != Shipment_Lines_Tbl(i).Currency_Code THEN
BEGIN
SELECT r.Conversion_Rate
INTO Shipment_Lines_Tbl(i).Rate
FROM Gl_Daily_Rates r
WHERE r.From_Currency = Shipment_Lines_Tbl(i).Currency_Code
AND r.To_Currency = Shipment_Lines_Tbl(i).Base_Currency_Code
AND r.Conversion_Date = Trunc(Shipment_Lines_Tbl(i).Transaction_Date)
AND r.Status_Code = 'C'
AND r.Conversion_Type = 'Corporate';
EXCEPTION
WHEN OTHERS THEN
Xxcus_Fnd_Message.Add_Msg('币种' || Shipment_Lines_Tbl(i).Currency_Code || '与本位币 ' ||
To_Char(Shipment_Lines_Tbl(i).Transaction_Date
,'YYYY-MM-DD') || '的汇率不存在或异常');
END;
Shipment_Lines_Tbl(i).Rate_Date := Shipment_Lines_Tbl(i).Transaction_Date;
ELSE
Shipment_Lines_Tbl(i).Rate_Type := NULL;
Shipment_Lines_Tbl(i).Rate_Date := NULL;
Shipment_Lines_Tbl(i).Rate := NULL;
END IF;
END IF;
IF Ln_Last_Header_Id = -1 THEN
Ln_Last_Header_Id := Pr_Shipment_Header_Rec.Shipment_Header_Id;
ELSIF Ln_Last_Header_Id != Pr_Shipment_Header_Rec.Shipment_Header_Id THEN
Xxcus_Fnd_Message.Add_Msg('存在多张asn情况无法一次接收');
Ln_Last_Header_Id := Pr_Shipment_Header_Rec.Shipment_Header_Id;
END IF;
IF Xxcus_Fnd_Message.Count_Msg >= 1 THEN
--添加发运行验证状态
Shipment_Lines_Tbl(i).Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Shipment_Lines_Tbl(i).Error_Message := Xxcus_Fnd_Message.Get_All_Msg;
ELSE
Shipment_Lines_Tbl(i).Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_s;
END IF;
END LOOP;
Pr_Shipment_Header_Rec.Shipment_Lines := Shipment_Lines_Tbl;
--Pr_Shipment_Header_Rec := Shipment_Header_Rec;
--同步数据到表
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Shipment_Table(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Insert_Err;
END IF;
EXCEPTION
WHEN Le_Insert_Err THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Init_Transaction;
PROCEDURE End_Transaction(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'End_Receive';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
BEGIN
--处理完成，把信息加入上级表
Pr_Shipment_Header_Rec.Status := Pr_Shipment_Header_Rec.Process_Status;
IF Pr_Shipment_Header_Rec.Status != Gv_Status_s THEN
Pr_Shipment_Header_Rec.Error_Message := '执行步骤 ' || Pr_Shipment_Header_Rec.Process_Step ||
'出现异常' || Pr_Shipment_Header_Rec.Process_Message;
END IF;
--Pr_Rcv_Header_Rec.Rcv_Steps(Pr_Rcv_Header_Rec.Process_Step).Shipment_Header_Rec := Pr_Shipment_Header_Rec; 该对象的状态变更由上级程序处理，本程序只处理 shipment的状态，并返回整体状态给上级程序
Lv_Process_Step := '同步接收的rt_transaction_id 到上级行表';
--便于后续检验和入库使用
--因为可能后续还有检验和入库，所以不在此同步上级行表状态
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Pr_Rcv_Header_Rec.Rcv_Lines(i).Rt_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Transaction_Id;
END LOOP;
/*IF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_r THEN
End_Receive(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_a THEN
End_Accept(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_d THEN
End_Delivery(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Rcv_Header_Rec.Process_Step = Gv_Process_Rv THEN
End_Returnv(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;*/
--同步数据到表
Lv_Process_Step := '同步数据到表';
Xxcus_Rcv_Rd_Datatransfer_Pkg.Sync_Shipment_Table(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Xv_Ret_Status := Pr_Shipment_Header_Rec.Status;
Xv_Ret_Message := Pr_Shipment_Header_Rec.Error_Message;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END End_Transaction;
PROCEDURE Transaction_Commit(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Transaction_Commit';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Header_Rec.Commit_Type = Gv_Trn_Online_Type THEN
Lv_Process_Step := '在线调用api执行';
--必须先提交数据，否则online的api无法找到该数据
COMMIT;
Lv_Process_Step := '初始化数据';
Xxcus_Rcv_Rd_Commit_Pkg.Rcv_Init(Pn_Init_Ou_Id => Pr_Shipment_Header_Rec.Ou_Id
,Pn_Init_User_Id => Pr_Shipment_Header_Rec.User_Id
,Pn_Init_Resp_Id => Pr_Shipment_Header_Rec.Resp_Id
,Pn_Init_Resp_Appl_Id => Pr_Shipment_Header_Rec.Resp_Appl_Id);
Lv_Process_Step := '在线提交请求';
Xxcus_Rcv_Rd_Commit_Pkg.Rcv_Online_Commit(Pn_Ou_Id => Pr_Shipment_Header_Rec.Ou_Id
,Pn_Group_Id => Pr_Shipment_Header_Rec.Group_Id
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSE
Lv_Process_Step := '批处理调用请求';
--提交请求
NULL;
Lv_Process_Step := '检查执行结果';
END IF;
Lv_Process_Step := '获取执行结果id';
IF Lv_Ret_Status = Fnd_Api.g_Ret_Sts_Success THEN
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
SELECT Rt.Transaction_Id
INTO Pr_Shipment_Header_Rec.Shipment_Lines(i).Rt_Transaction_Id
FROM Rcv_Transactions Rt
WHERE Rt.Interface_Source_Code = Gv_Interface_Source_Code
AND Rt.Interface_Source_Line_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Line_Id
AND Rt.Interface_Transaction_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Interface_Id
AND Rt.Transaction_Type = Pr_Shipment_Header_Rec.Transaction_Type;
END LOOP;
END IF;
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Ret_Message;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Transaction_Commit;
/*
PROCEDURE Receive_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Po_Transaction';
Lv_Process_Step VARCHAR2(3000);
--Pr_Shipment_Header_Rec Shipment_Header_Record;
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Le_Exe_Error EXCEPTION;
Lv_Step VARCHAR2(30);
PROCEDURE c_Step IS
BEGIN
Change_Step(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pv_Step => Lv_Step
,Pv_Step_Status => Lv_Ret_Status
,Pv_Message => Lv_Ret_Message);
END c_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
\*
TODO: owner="brayden.liu" created="2023/1/29"
text="校验采购接收的数据"
*\
Lv_Process_Step := '初始化数据';
Lv_Step := Gv_Init;
Init_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '检验数据';
Lv_Step := Gv_Rec_Validate;
Xxcus_Rcv_Rd_Validate_Pkg.Receive_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '插入接收接口数据';
--验证通过则插入接口
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Lv_Step := Gv_Receive;
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Receive_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Rcv_Commit(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;
Lv_Process_Step := '处理完成后状态';
End_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
Show_Log('Receive_Main:' || Pr_Rcv_Header_Rec.Process_Status || ':' || Xv_Ret_Status || ',' ||
Xv_Ret_Message);
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
Lv_Ret_Status := Xv_Ret_Status;
Lv_Ret_Message := Lv_Ret_Status;
c_Step;
END Receive_Main;
PROCEDURE Accept_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Accept_Main';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lv_Step VARCHAR2(30);
PROCEDURE c_Step IS
BEGIN
Change_Step(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pv_Step => Lv_Step
,Pv_Step_Status => Lv_Ret_Status
,Pv_Message => Lv_Ret_Message);
END c_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
Lv_Step := Gv_Init;
Init_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '检验数据';
Lv_Step := Gv_Accept_Validate;
Xxcus_Rcv_Rd_Validate_Pkg.Accept_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '插入接收接口数据';
--验证通过则插入接口
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Lv_Step := Gv_Receive;
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Accept_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Rcv_Commit(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;
Lv_Process_Step := '处理完成后状态';
End_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
Show_Log('Accept_Main:' || Pr_Rcv_Header_Rec.Process_Status || ':' || Xv_Ret_Status || ',' ||
Xv_Ret_Message);
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Accept_Main;
PROCEDURE Delivery_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Delivery_Main';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lv_Step VARCHAR2(30);
PROCEDURE c_Step IS
BEGIN
Change_Step(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pv_Step => Lv_Step
,Pv_Step_Status => Lv_Ret_Status
,Pv_Message => Lv_Ret_Message);
END c_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
Lv_Step := Gv_Init;
Init_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '检验数据';
Lv_Step := Gv_Del_Validate;
Xxcus_Rcv_Rd_Validate_Pkg.Delivery_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
Lv_Process_Step := '插入接收接口数据';
--验证通过则插入接口
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Lv_Step := Gv_Delivery;
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Delivery_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Validate_Step_Status(Pr_Header_Rec => Pr_Shipment_Header_Rec) THEN
Rcv_Commit(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;
Lv_Process_Step := '处理完成后状态';
End_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
Show_Log('Accept_Main:' || Pr_Rcv_Header_Rec.Process_Status || ':' || Xv_Ret_Status || ',' ||
Xv_Ret_Message);
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Delivery_Main;*/
PROCEDURE Transaction_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Transaction_Main';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lv_Step VARCHAR2(30);
Le_Step_Err EXCEPTION;
Le_End_Err EXCEPTION;
PROCEDURE Change_Step IS
BEGIN
Pr_Shipment_Header_Rec.Process_Step := Lv_Step;
Pr_Shipment_Header_Rec.Process_Status := Lv_Ret_Status;
Pr_Shipment_Header_Rec.Process_Message := Lv_Ret_Message;
End_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status
,Xv_Ret_Message => Xv_Ret_Message);
END Change_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据:' || Pr_Rcv_Header_Rec.Process_Step;
Lv_Step := Gv_Init;
Show_Log('-->>' || Lv_Process_Step);
Init_Transaction(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || '->' || Pr_Shipment_Header_Rec.Process_Status || ':' ||
Lv_Ret_Message);
IF Lv_Ret_Status != Gv_Status_s THEN
RAISE Le_Step_Err;
END IF;
Lv_Process_Step := '检验数据';
Lv_Step := Gv_Validate;
Show_Log('-->>' || Lv_Process_Step);
Xxcus_Rcv_Rd_Validate_Pkg.Transaction_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || '->' || Pr_Shipment_Header_Rec.Process_Status || ':' ||
Lv_Ret_Message);
IF Lv_Ret_Status != Gv_Status_s THEN
RAISE Le_Step_Err;
END IF;
--验证通过则插入接口
Lv_Process_Step := '插入接收接口数据';
Lv_Step := Gv_Insert_Interface;
Show_Log('-->>' || Lv_Process_Step);
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || '->' || Pr_Shipment_Header_Rec.Process_Status || ':' ||
Lv_Ret_Message);
IF Lv_Ret_Status != Gv_Status_s THEN
RAISE Le_Step_Err;
END IF;
--校验通过，则提交请求
Lv_Step := Gv_Commit;
Show_Log('-->>' || Lv_Process_Step);
Transaction_Commit(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || '->' || Pr_Shipment_Header_Rec.Process_Status || ':' ||
Lv_Ret_Message);
IF Lv_Ret_Status != Gv_Status_s THEN
RAISE Le_Step_Err;
END IF;
Lv_Process_Step := '处理完成后状态';
Change_Step;
IF Lv_Ret_Status != Gv_Status_s THEN
RAISE Le_End_Err;
END IF;
Show_Log('Accept_Main:' || Pr_Rcv_Header_Rec.Process_Status || ':' || Xv_Ret_Status || ',' ||
Xv_Ret_Message);
EXCEPTION
WHEN Le_Step_Err THEN
--执行中出现异常
Change_Step;
WHEN Le_End_Err THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Transaction_Main;
/* --汇总执行接口
PROCEDURE Po_Transaction(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
--,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Po_Transaction';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lr_Shipment_Header_Rec Shipment_Header_Record;
Lv_Step VARCHAR2(30) := Gv_Init;
Ln_This_Step_Key NUMBER := 1;
FUNCTION Start_Porcess_Step(Lv_Validate_Step IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
IF Ln_This_Step_Key <= Pr_Rcv_Header_Rec.Rcv_Steps.Count THEN
IF Pr_Rcv_Header_Rec.Rcv_Steps(Ln_This_Step_Key).Step_Code = Lv_Validate_Step AND
(Ln_This_Step_Key = 1 OR Pr_Rcv_Header_Rec.Rcv_Steps(Ln_This_Step_Key - 1)
.Status = Gv_Status_s) THEN
--验证步骤与本步骤相同，且上一步骤是成功状态，则开始本次步骤
--修改本次步骤为进行中，开始事务处理
Change_Step(Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pv_Step_Key => Ln_This_Step_Key
,Pv_Step_Status => Gv_Status_p);
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSE
RETURN FALSE;
END IF;
END Start_Porcess_Step;
PROCEDURE c_Step IS
BEGIN
--同步本次步骤的状态
Change_Step(Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pv_Step_Key => Ln_This_Step_Key
,Pv_Step_Status => Lv_Ret_Status --Pr_Rcv_Header_Rec.Rcv_Steps(Ln_This_Step_Key).Shipment_Header_Rec.Status
,Pv_Message => Lv_Ret_Message --Pr_Rcv_Header_Rec.Rcv_Steps(Ln_This_Step_Key).Shipment_Header_Rec.Error_Message
);
IF Lv_Ret_Status = Gv_Status_s THEN
Ln_This_Step_Key := Ln_This_Step_Key + 1; --进入下一步
END IF;
Lv_Ret_Status := NULL;
Lv_Ret_Message := NULL;
END c_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
--接收->质检->入库
-----
IF Start_Porcess_Step(Gv_Trntype_Receive) THEN
Lv_Process_Step := '进行采购接收';
Receive_Main(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Lr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Start_Porcess_Step(Gv_Trntype_Accept) THEN
Lv_Process_Step := '进行质检';
Accept_Main(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Lr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Start_Porcess_Step(Gv_Process_d) THEN
Lv_Process_Step := '进行采购入库';
Delivery_Main(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Lr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
c_Step;
END IF;
IF Start_Porcess_Step(Gv_Process_Rv) THEN
Lv_Process_Step := '进行采购退库';
\*Delivery_Main(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Lr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);*\
c_Step;
END IF;
--处理全部完成的状态数据
Pr_Rcv_Header_Rec.Status := Pr_Rcv_Header_Rec.Process_Status;
IF Pr_Rcv_Header_Rec.Status != Gv_Status_s THEN
Pr_Rcv_Header_Rec.Error_Message := '执行步骤 ' || Pr_Rcv_Header_Rec.Process_Step || '出现异常' ||
Pr_Rcv_Header_Rec.Process_Message;
END IF;
Xv_Ret_Status := Pr_Rcv_Header_Rec.Status;
Xv_Ret_Message := Pr_Rcv_Header_Rec.Error_Message;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Po_Transaction;*/
--初始化批次数据数据
PROCEDURE Rcv_Init_Data(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
--,Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
--,Pt_Shipment_Lines_Tbl IN OUT Shipment_Lines_Table
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'rcv_Init_Data';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Rcv_Step_Table Rcv_Step_Table;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
/*
TODO: owner="brayden.liu" created="2023/1/29"
text="初始化接收入库的数据"
*/
Lt_Rcv_Step_Table(1).Step_Code := Gv_Init;
Lt_Rcv_Step_Table(1).Status := Gv_Status_s;
Lv_Process_Step := '根据事务处理类型，建立步骤列表';
IF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Receive THEN
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_r;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
ELSIF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Accept THEN
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_a;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
ELSIF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Delivery THEN
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_d;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
ELSIF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Returnv THEN
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_Rv;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
ELSIF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Ad THEN
--检验
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_a;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
--入库
Lt_Rcv_Step_Table(3).Step_Code := Gv_Process_d;
Lt_Rcv_Step_Table(3).Status := Gv_Status_w; --最后一步没有下一步
ELSIF Pr_Rcv_Header_Rec.Transaction_Type = Gv_Trntype_Rd THEN
--接收
Lt_Rcv_Step_Table(2).Step_Code := Gv_Process_r;
Lt_Rcv_Step_Table(2).Status := Gv_Status_w;
--检验
Lt_Rcv_Step_Table(3).Step_Code := Gv_Process_a;
Lt_Rcv_Step_Table(3).Status := Gv_Status_w;
--入库
Lt_Rcv_Step_Table(4).Step_Code := Gv_Process_d;
Lt_Rcv_Step_Table(4).Status := Gv_Status_w; --最后一步没有下一步
END IF;
Pr_Rcv_Header_Rec.Rcv_Steps := Lt_Rcv_Step_Table;
Pr_Rcv_Header_Rec.Status := Gv_Status_w;
Pr_Rcv_Header_Rec.Process_Step := Gv_Init;
Pr_Rcv_Header_Rec.Process_Status := Gv_Status_s;
Xxcus_Rcv_Rd_Datatransfer_Pkg.Insert_Api_Table(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Rcv_Init_Data;
/*
1. 检查完成后的数据状态；
2. 完成头行的状态和错误信息；
3. 将type数据同步到table
*/
PROCEDURE Rcv_Sync_Data(Pr_Shipment_Header_Rec IN OUT Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Sync_Data';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF (Pr_Shipment_Header_Rec.Transaction_Type = Gv_Trntype_Receive AND
Pr_Shipment_Header_Rec.Process_Step != Gv_Commit) OR
Pr_Shipment_Header_Rec.Process_Status != Fnd_Api.g_Ret_Sts_Success THEN
--数据异常
Pr_Shipment_Header_Rec.Status := Fnd_Api.g_Ret_Sts_Error;
Pr_Shipment_Header_Rec.Error_Message := '处理失败';
ELSIF (Pr_Shipment_Header_Rec.Transaction_Type = Gv_Trntype_Delivery AND
Pr_Shipment_Header_Rec.Process_Step != Gv_Commit) OR
Pr_Shipment_Header_Rec.Process_Status != Fnd_Api.g_Ret_Sts_Success THEN
Pr_Shipment_Header_Rec.Status := Fnd_Api.g_Ret_Sts_Error;
Pr_Shipment_Header_Rec.Error_Message := '处理失败';
ELSIF (Pr_Shipment_Header_Rec.Transaction_Type = Gv_Trntype_Rd AND
Pr_Shipment_Header_Rec.Process_Step != Gv_Commit) OR
Pr_Shipment_Header_Rec.Process_Status != Fnd_Api.g_Ret_Sts_Success THEN
Pr_Shipment_Header_Rec.Status := Fnd_Api.g_Ret_Sts_Error;
Pr_Shipment_Header_Rec.Error_Message := '处理失败';
ELSIF (Pr_Shipment_Header_Rec.Transaction_Type = Gv_Trntype_Returnv AND
Pr_Shipment_Header_Rec.Process_Step != Gv_Commit) OR
Pr_Shipment_Header_Rec.Process_Status != Fnd_Api.g_Ret_Sts_Success THEN
Pr_Shipment_Header_Rec.Status := Fnd_Api.g_Ret_Sts_Error;
Pr_Shipment_Header_Rec.Error_Message := '处理失败';
ELSE
Pr_Shipment_Header_Rec.Status := Fnd_Api.g_Ret_Sts_Success;
Pr_Shipment_Header_Rec.Error_Message := NULL;
END IF;
/*Xxcus_Rcv_Rd_Datatransfer_Pkg.Sync_Table(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);*/
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Rcv_Sync_Data;
PROCEDURE Rcv_Main(Pr_Rcv_Header_Rec IN OUT Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'rcv_Main';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Le_Exe_Error EXCEPTION;
Lr_Shipment_Header_Rec Shipment_Header_Record;
PROCEDURE c_Step(Pn_This_Step_Key IN NUMBER
,Pv_Step_Status IN VARCHAR2
,Pv_Message IN VARCHAR2 DEFAULT NULL) IS
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
BEGIN
Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Status := Pv_Step_Status;
Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Error_Message := Pv_Message;
Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Step_Date := SYSDATE;
Pr_Rcv_Header_Rec.Process_Step_Id := Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Rcv_Step_Id;
Pr_Rcv_Header_Rec.Process_Step := Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Step_Code;
Pr_Rcv_Header_Rec.Process_Status := Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key).Status;
Pr_Rcv_Header_Rec.Process_Message := Pr_Rcv_Header_Rec.Rcv_Steps(Pn_This_Step_Key)
.Error_Message;
--同步状态数据到表
Xxcus_Rcv_Rd_Datatransfer_Pkg.Sync_Api_Table(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END c_Step;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
-- Lv_Step := Gv_Init;
Rcv_Init_Data(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || Pr_Rcv_Header_Rec.Process_Status);
--c_Step;
--按照步骤循环执行事务处理；
--第一步为 init 在初始化中完成
FOR i IN 2 .. Pr_Rcv_Header_Rec.Rcv_Steps.Count LOOP
IF Pr_Rcv_Header_Rec.Process_Status = Gv_Status_s THEN
--Process_Step为 Gv_Status_s 则表示上一步执行成功
c_Step(Pn_This_Step_Key => i, Pv_Step_Status => Gv_Status_p, Pv_Message => NULL);
Lv_Process_Step := '进行事务处理' || Pr_Rcv_Header_Rec.Process_Step;
Show_Log('---------');
Show_Log('---------');
Show_Log('---------');
Show_Log(Lv_Process_Step);
Transaction_Main(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Pr_Shipment_Header_Rec => Lr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Show_Log(Lv_Process_Step || Pr_Rcv_Header_Rec.Process_Status);
c_Step(Pn_This_Step_Key => i
,Pv_Step_Status => Lv_Ret_Status
,Pv_Message => Lv_Ret_Message);
END IF;
END LOOP;
Pr_Rcv_Header_Rec.Status := Pr_Rcv_Header_Rec.Process_Status;
IF Pr_Rcv_Header_Rec.Status != Gv_Status_s THEN
Pr_Rcv_Header_Rec.Error_Message := '执行步骤 ' || Pr_Rcv_Header_Rec.Process_Step || '出现异常' ||
Pr_Rcv_Header_Rec.Process_Message;
END IF;
--修改行表数据
FOR i IN 1 .. Lr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Pr_Rcv_Header_Rec.Rcv_Lines(i).Status := Lr_Shipment_Header_Rec.Shipment_Lines(i).Status;
Pr_Rcv_Header_Rec.Rcv_Lines(i).Error_Message := Lr_Shipment_Header_Rec.Shipment_Lines(i)
.Error_Message;
END LOOP;
--同步表状态
Xxcus_Rcv_Rd_Datatransfer_Pkg.Sync_Api_Table(Pr_Rcv_Header_Rec => Pr_Rcv_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Exe_Error;
END IF;
EXCEPTION
WHEN Le_Exe_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
/*Sync_Data(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);*/
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
/*Sync_Data(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);*/
END Rcv_Main;
BEGIN
-- Initialization
NULL;
END Xxcus_Rcv_Rd_Handle_Pkg;
数据校验
包头
CREATE OR REPLACE PACKAGE Xxcus_Rcv_Rd_Validate_Pkg IS
-- Author : BRAYDEN.LIU
-- Created : 2023/1/29 9:48:05
-- Purpose : PO采购接收的提交请求的程序包
--通用校验
PROCEDURE Transaction_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2);
END Xxcus_Rcv_Rd_Validate_Pkg;
包体
CREATE OR REPLACE PACKAGE BODY Xxcus_Rcv_Rd_Validate_Pkg IS
Gv_Package_Name VARCHAR2(200) := 'Xxcus_Rcv_Rd_Validate_Pkg.'; --当前程序包名称
--通用验证
PROCEDURE Validate_Po_Status(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Po_Status';
Lv_Process_Step VARCHAR2(3000);
v_Count NUMBER := 0;
v_Cancel_Flag VARCHAR2(10);
v_Closed_Code VARCHAR2(30);
v_Approved_Flag VARCHAR2(10);
BEGIN
SELECT COUNT(*)
,MAX(Nvl(Pll.Cancel_Flag, 'N'))
,MAX(Nvl(Pll.Closed_Code, 'OPEN'))
,MAX(Nvl(Pll.Approved_Flag, 'N'))
INTO v_Count
,v_Cancel_Flag
,v_Closed_Code
,v_Approved_Flag
FROM Po_Line_Locations_All Pll
WHERE Pll.Po_Line_Id = Pr_Shipment_Rec.Po_Line_Id
AND Pll.Line_Location_Id = Pr_Shipment_Rec.Po_Line_Location_Id;
IF v_Count = 0 THEN
Xxcus_Fnd_Message.Add_Msg('采购订单发运行不存在');
ELSIF v_Cancel_Flag = 'Y' THEN
Xxcus_Fnd_Message.Add_Msg('采购订单发运行已取消');
ELSIF v_Closed_Code NOT IN ('OPEN', 'CLOSED FOR INVOICE') THEN
Xxcus_Fnd_Message.Add_Msg('采购订单发运行已关闭');
ELSIF v_Approved_Flag <> 'Y' THEN
Xxcus_Fnd_Message.Add_Msg('采购订单发运行未审批');
END IF;
END Validate_Po_Status;
--通用验证
PROCEDURE Validate_Period(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Period';
Lv_Process_Step VARCHAR2(3000);
v_Po_Gl_Period_Status VARCHAR2(10);
v_Count NUMBER := 0;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '检查库存会计期是否打开';
v_Count := 0;
SELECT COUNT(*)
INTO v_Count
FROM Inv.Org_Acct_Periods Op
WHERE Op.Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Open_Flag = 'Y'
AND Period_Start_Date <= Trunc(Pr_Shipment_Rec.Transaction_Date)
AND Schedule_Close_Date >= Trunc(Pr_Shipment_Rec.Transaction_Date);
IF (v_Count = 0) THEN
Xxcus_Fnd_Message.Add_Msg('该事务处理时间的库存会计期间未打开');
END IF;
Lv_Process_Step := '检查采购会计期间是否打开';
v_Count := 0;
--update by pengyongping 20160128 判断采购期间
BEGIN
SELECT Closing_Status
INTO v_Po_Gl_Period_Status
FROM Gl_Period_Statuses Gp
WHERE Application_Id = 201
AND Set_Of_Books_Id = (SELECT d.Set_Of_Books_Id
FROM Org_Organization_Definitions d
WHERE d.Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Rownum = 1)
--AND closing_status ='O'
AND Trunc(Pr_Shipment_Rec.Transaction_Date) BETWEEN Start_Date AND End_Date
AND Rownum = 1;
EXCEPTION
WHEN OTHERS THEN
v_Po_Gl_Period_Status := 'C';
END;
IF v_Po_Gl_Period_Status <> 'O' THEN
Xxcus_Fnd_Message.Add_Msg('该事务处理时间的采购会计期间未打开');
END IF;
END Validate_Period;
--数据是否必填
PROCEDURE Validate_Request(Pv_Parent_Transaction_Type IN VARCHAR2
,Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Request';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pv_Parent_Transaction_Type IN
(Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Receive, Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Accept) THEN
IF Pr_Shipment_Rec.Ship_Quantity IS NULL OR Pr_Shipment_Rec.Shipment_Line_Id IS NULL THEN
Xxcus_Fnd_Message.Add_Msg('数量、Shipment_Line_Id为必填。');
END IF;
ELSIF Pv_Parent_Transaction_Type IN
(Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Rd
,Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Rd
,Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Returnv
,Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Delivery) THEN
--子库存是否有货位管理
IF Pr_Shipment_Rec.Ship_Quantity IS NULL OR Pr_Shipment_Rec.Shipment_Line_Id IS NULL OR
Pr_Shipment_Rec.Subinventory_Code IS NULL THEN
Xxcus_Fnd_Message.Add_Msg('数量、子库存、Shipment_Line_Id为必填。');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Request;
--给阶段验证逻辑不同
--检验rt上阶段数量是否满足本次事务处理
PROCEDURE Validate_Quantity(Pv_Transaction_Type IN VARCHAR2
,Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Quantity';
Lv_Process_Step VARCHAR2(3000);
Ln_Shipped_Quantity NUMBER;
Lv_Pre_Trn_Type VARCHAR2(50);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Rec.Shipment_Line_Id IS NOT NULL THEN
IF Pv_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_r THEN
--接收验证
--已发运数量
SELECT t.Quantity_Shipped - t.Quantity_Received
,'ASN'
INTO Ln_Shipped_Quantity
,Lv_Pre_Trn_Type
FROM Rcv_Shipment_Lines t
WHERE t.Shipment_Line_Id = Pr_Shipment_Rec.Shipment_Line_Id;
ELSE
SELECT Rt.Quantity - Nvl((SELECT SUM(Rt1.Quantity)
FROM Rcv_Transactions Rt1
WHERE Rt1.Parent_Transaction_Id = Rt.Transaction_Id)
,0)
,Rt.Transaction_Type
INTO Ln_Shipped_Quantity
,Lv_Pre_Trn_Type
FROM Rcv_Transactions Rt
WHERE Rt.Transaction_Id = Pr_Shipment_Rec.Rt_Transaction_Id_In;
END IF;
Ln_Shipped_Quantity := Inv_Convert.Inv_Um_Convert(Item_Id => Pr_Shipment_Rec.Inventory_Item_Id
,PRECISION => 10
,From_Quantity => Ln_Shipped_Quantity
,From_Unit => Pr_Shipment_Rec.Po_Uom
,To_Unit => Pr_Shipment_Rec.Transaction_Uom
,From_Name => NULL
,To_Name => NULL);
--执行中的数量，api在执行中的
BEGIN
SELECT Ln_Shipped_Quantity - SUM(l.Ship_Quantity)
INTO Ln_Shipped_Quantity
FROM Xxcus_Rd_Lines_t l
,Xxcus_Rd_Headers_t h
WHERE h.Header_Id = l.Header_Id
AND l.Shipment_Line_Id = Pr_Shipment_Rec.Shipment_Line_Id
AND h.Transaction_Type = Pv_Transaction_Type
AND h.Status = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_p; --等待处理状态的,本次处理的数据状态也是
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--计算剩余量
IF Ln_Shipped_Quantity < 0 THEN
Xxcus_Fnd_Message.Add_Msg('发运单（ASN）可接收数量不足。');
END IF;
--
IF Lv_Pre_Trn_Type != 'ASN' THEN
IF Pv_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_a THEN
--本次为接收
IF Lv_Pre_Trn_Type != 'RECEIVE' THEN
--上一步不是接收
Xxcus_Fnd_Message.Add_Msg('传递的rcv_transaction_id ' ||
Pr_Shipment_Rec.Rt_Transaction_Id_In || '不是接收事务处理。');
END IF;
ELSIF Pv_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_d THEN
--本次为入库
IF Lv_Pre_Trn_Type != 'ACCEPT' THEN
--上一步不是接收
Xxcus_Fnd_Message.Add_Msg('传递的rcv_transaction_id ' ||
Pr_Shipment_Rec.Rt_Transaction_Id_In || '不是接受（检验）事务处理。');
END IF;
ELSIF Pv_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_Rv THEN
--本次为退货
IF Lv_Pre_Trn_Type != 'DELIVER' THEN
--上一步不是接收
Xxcus_Fnd_Message.Add_Msg('传递的rcv_transaction_id ' ||
Pr_Shipment_Rec.Rt_Transaction_Id_In || '不是入库事务处理。');
END IF;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Quantity;
--物料状态是否为有效
PROCEDURE Validate_Item(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Item';
Lv_Process_Step VARCHAR2(3000);
Ln_Count NUMBER;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT COUNT(1)
INTO Ln_Count
FROM Mtl_System_Items_b Msib
WHERE Msib.Inventory_Item_Id = Pr_Shipment_Rec.Inventory_Item_Id
AND Msib.Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Nvl(Msib.Inventory_Item_Status_Code, 'Active') <> 'Inactive';
IF Ln_Count = 0 THEN
Xxcus_Fnd_Message.Add_Msg('不是有效的物料ID');
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Item;
PROCEDURE Validate_Manufacturer(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Manufacturer';
Lv_Process_Step VARCHAR2(3000);
Ln_Count NUMBER;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Rec.Manufacturer_Number IS NOT NULL THEN
SELECT COUNT(1)
INTO Ln_Count
FROM Xxcus_Inv_Manufacturers Xim
WHERE Xim.Manufacturer_Number = Pr_Shipment_Rec.Manufacturer_Number;
IF Ln_Count = 0 THEN
Xxcus_Fnd_Message.Add_Msg('无效的制造商');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Manufacturer;
--物料对应仓库
PROCEDURE Validate_Itemsub(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_itemsub';
Lv_Process_Step VARCHAR2(3000);
Lv_Asset_Flag1 VARCHAR2(20);
Lv_Asset_Flag2 VARCHAR2(20);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Rec.Subinventory_Code IS NOT NULL THEN
SELECT Decode(Asset_Inventory, 1, 'asset', 2, 'cost')
INTO Lv_Asset_Flag1
FROM Mtl_Secondary_Inventories
WHERE Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Secondary_Inventory_Name = Pr_Shipment_Rec.Subinventory_Code;
SELECT Decode(Inventory_Asset_Flag, 'Y', 'asset', 'cost')
INTO Lv_Asset_Flag2
FROM Mtl_System_Items_b
WHERE Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Inventory_Item_Id = Pr_Shipment_Rec.Inventory_Item_Id;
IF Lv_Asset_Flag2 = 'cost' THEN
--费用类物料需要去找下工单装配件
BEGIN
SELECT Decode(Inventory_Asset_Flag, 'Y', 'asset', 'cost')
INTO Lv_Asset_Flag2
FROM Wip_Discrete_Jobs Wdj
,Mtl_System_Items_b Msib
,Po_Distributions_All Pda
WHERE Wdj.Primary_Item_Id = Msib.Inventory_Item_Id
AND Wdj.Wip_Entity_Id = Pda.Wip_Entity_Id
AND Msib.Organization_Id = Wdj.Organization_Id
AND Pda.Po_Distribution_Id = Pr_Shipment_Rec.Po_Distribution_Id;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
IF Lv_Asset_Flag1 <> Lv_Asset_Flag2 THEN
Xxcus_Fnd_Message.Add_Msg('不允许费用料号入资产仓或者资产料号入费用仓');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Itemsub;
--批次数量与行数据是否一致
PROCEDURE Validate_Lot(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_lot';
Lv_Process_Step VARCHAR2(3000);
Ln_Lot_Control_Code NUMBER;
Ln_Quantity NUMBER := 0;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT m.Lot_Control_Code
INTO Ln_Lot_Control_Code
FROM Mtl_System_Items_b m
WHERE m.Inventory_Item_Id = Pr_Shipment_Rec.Inventory_Item_Id
AND m.Organization_Id = Pr_Shipment_Rec.To_Organization_Id;
IF Ln_Lot_Control_Code = 2 THEN
IF Pr_Shipment_Rec.Shipment_Lot_Tbl.Count = 0 THEN
Xxcus_Fnd_Message.Add_Msg('该物料为批次控制物料，需要填写批次信息');
END IF;
END IF;
FOR i IN 1 .. Pr_Shipment_Rec.Shipment_Lot_Tbl.Count LOOP
--是否有填写批次，数量合计是否等于入库数
IF Pr_Shipment_Rec.Shipment_Lot_Tbl(i).Lot_Number IS NULL THEN
Xxcus_Fnd_Message.Add_Msg('未填写批次号');
END IF;
IF Pr_Shipment_Rec.Shipment_Lot_Tbl(i).Ship_Quantity IS NULL THEN
Xxcus_Fnd_Message.Add_Msg('未填写批次数量');
END IF;
Ln_Quantity := Ln_Quantity + Nvl(Pr_Shipment_Rec.Shipment_Lot_Tbl(i).Ship_Quantity, 0);
END LOOP;
IF Ln_Quantity != Pr_Shipment_Rec.Ship_Quantity THEN
Xxcus_Fnd_Message.Add_Msg('批次数量合计不等于发运数量');
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Lot;
PROCEDURE Validate_Sub_Loc(Pr_Shipment_Rec IN Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Validate_Sub_Loc';
Lv_Process_Step VARCHAR2(3000);
Ln_Locator_Type NUMBER;
Ln_Erp_Locator_Id NUMBER;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Rec.Subinventory_Code IS NOT NULL THEN
Lv_Process_Step := '子库是否存在，是否货位控制';
BEGIN
SELECT Locator_Type
INTO Ln_Locator_Type
FROM Mtl_Secondary_Inventories i
WHERE i.Secondary_Inventory_Name = Pr_Shipment_Rec.Subinventory_Code
AND i.Organization_Id = Pr_Shipment_Rec.To_Organization_Id;
EXCEPTION
WHEN No_Data_Found THEN
Ln_Locator_Type := NULL;
Xxcus_Fnd_Message.Add_Msg('子库存' || Pr_Shipment_Rec.Subinventory_Code || '不存在');
WHEN OTHERS THEN
Ln_Locator_Type := 1;
END;
IF Nvl(Ln_Locator_Type, 1) != 1 THEN
Lv_Process_Step := '货位是否存在';
IF Pr_Shipment_Rec.Locator_Id IS NULL THEN
Xxcus_Fnd_Message.Add_Msg('货位不能为空');
ELSE
BEGIN
SELECT Inventory_Location_Id
INTO Ln_Erp_Locator_Id
FROM Mtl_Item_Locations Mil
WHERE Organization_Id = Pr_Shipment_Rec.To_Organization_Id
AND Mil.Subinventory_Code = Pr_Shipment_Rec.Subinventory_Code
AND Mil.Inventory_Location_Id = Pr_Shipment_Rec.Locator_Id
AND Nvl(Mil.Disable_Date, SYSDATE + 1) > SYSDATE;
EXCEPTION
WHEN No_Data_Found THEN
Xxcus_Fnd_Message.Add_Msg('货位不存在或已失效');
WHEN OTHERS THEN
Xxcus_Fnd_Message.Add_Msg('货位查询异常：' || SQLERRM);
END;
END IF;
END IF;
ELSE
Xxcus_Fnd_Message.Add_Msg('子库存必填');
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Validate_Sub_Loc;
--采购接收验证
PROCEDURE Receive_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Receive_Validate';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Ship_Rec Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record;
Le_Step_Error EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Xxcus_Fnd_Message.Init_Msg;
Lt_Ship_Rec := Pr_Shipment_Header_Rec.Shipment_Lines(i);
Lt_Ship_Rec.Status := 'VS';
Lv_Process_Step := '验证PO 发运状态';
Validate_Po_Status(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '验证期间';
Validate_Period(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验必填';
Validate_Request(Pv_Parent_Transaction_Type => Pr_Shipment_Header_Rec.Parent_Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验shipment行数量是否足够';
Validate_Quantity(Pv_Transaction_Type => Pr_Shipment_Header_Rec.Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验物料是否有效';
Validate_Item(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验Xxcus_Inv_Manufacturers，制造商是否无效';
Validate_Manufacturer(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
IF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Rd THEN
--本次为接收，但是父阶要求的是接收并入库，则在本次都进行验证
--需要做接收入库才会验证这里
Lv_Process_Step := '如果是费用料号，则不允许费用料号入资产仓或者资产料号入费用仓'; --发运要求检验
Validate_Itemsub(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果批次控制，批次是否为空，如果是退货，批次是否与入库批次一致'; --发运要求检验
Validate_Lot(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验子库和货位是否存在和有效'; --发运要求检验
Validate_Sub_Loc(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
END IF;
IF Xxcus_Fnd_Message.Count_Msg >= 1 THEN
--添加发运行验证状态
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Lt_Ship_Rec.Error_Message := Xxcus_Fnd_Message.Get_All_Msg;
Xv_Ret_Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Xv_Ret_Message := '检验接收数据存在异常';
ELSE
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_s;
END IF;
Pr_Shipment_Header_Rec.Shipment_Lines(i) := Lt_Ship_Rec;
END LOOP;
EXCEPTION
WHEN Le_Step_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Receive_Validate;
--采购接收验证
PROCEDURE Accept_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Accept_Validate';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Ship_Rec Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record;
Le_Step_Error EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Xxcus_Fnd_Message.Init_Msg;
Lt_Ship_Rec := Pr_Shipment_Header_Rec.Shipment_Lines(i);
Lt_Ship_Rec.Status := 'VS';
Lv_Process_Step := '验证PO 发运状态';
Validate_Po_Status(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '验证期间';
Validate_Period(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验必填';
Validate_Request(Pv_Parent_Transaction_Type => Pr_Shipment_Header_Rec.Parent_Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验shipment行数量是否足够';
Validate_Quantity(Pv_Transaction_Type => Pr_Shipment_Header_Rec.Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验物料是否有效';
Validate_Item(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验Xxcus_Inv_Manufacturers，制造商是否无效';
Validate_Manufacturer(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
IF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Delivery OR
Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Rd THEN
--需要做接收入库才会验证这里
Lv_Process_Step := '如果是费用料号，则不允许费用料号入资产仓或者资产料号入费用仓'; --发运要求检验
Validate_Itemsub(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果批次控制，批次是否为空，如果是退货，批次是否与入库批次一致'; --发运要求检验
Validate_Lot(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验子库和货位是否存在和有效'; --发运要求检验
Validate_Sub_Loc(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
END IF;
IF Xxcus_Fnd_Message.Count_Msg >= 1 THEN
--添加发运行验证状态
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Lt_Ship_Rec.Error_Message := Xxcus_Fnd_Message.Get_All_Msg;
Xv_Ret_Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Xv_Ret_Message := '检验数据存在异常';
ELSE
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_s;
END IF;
Pr_Shipment_Header_Rec.Shipment_Lines(i) := Lt_Ship_Rec;
END LOOP;
EXCEPTION
WHEN Le_Step_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Accept_Validate;
--采购接收验证
PROCEDURE Delivery_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Delivery_Validate';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Ship_Rec Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record;
Le_Step_Error EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Xxcus_Fnd_Message.Init_Msg;
Lt_Ship_Rec := Pr_Shipment_Header_Rec.Shipment_Lines(i);
Lt_Ship_Rec.Status := 'VS';
Lv_Process_Step := '验证PO 发运状态';
Validate_Po_Status(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '验证期间';
Validate_Period(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验必填';
Validate_Request(Pv_Parent_Transaction_Type => Pr_Shipment_Header_Rec.Parent_Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验shipment行数量是否足够';
Validate_Quantity(Pv_Transaction_Type => Pr_Shipment_Header_Rec.Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验物料是否有效';
Validate_Item(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验Xxcus_Inv_Manufacturers，制造商是否无效';
Validate_Manufacturer(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果是费用料号，则不允许费用料号入资产仓或者资产料号入费用仓'; --发运要求检验
Validate_Itemsub(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果批次控制，批次是否为空，如果是退货，批次是否与入库批次一致'; --发运要求检验
Validate_Lot(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验子库和货位是否存在和有效'; --发运要求检验
Validate_Sub_Loc(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
IF Xxcus_Fnd_Message.Count_Msg >= 1 THEN
--添加发运行验证状态
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Lt_Ship_Rec.Error_Message := Xxcus_Fnd_Message.Get_All_Msg;
Xv_Ret_Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Xv_Ret_Message := '检验数据存在异常';
ELSE
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_s;
END IF;
Pr_Shipment_Header_Rec.Shipment_Lines(i) := Lt_Ship_Rec;
END LOOP;
EXCEPTION
WHEN Le_Step_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Delivery_Validate;
--退回到供应商验证
PROCEDURE Returnv_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Returnv_Validate';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Ship_Rec Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record;
Le_Step_Error EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Xxcus_Fnd_Message.Init_Msg;
Lt_Ship_Rec := Pr_Shipment_Header_Rec.Shipment_Lines(i);
Lt_Ship_Rec.Status := 'VS';
Lv_Process_Step := '验证PO 发运状态';
Validate_Po_Status(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '验证期间';
Validate_Period(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验必填';
Validate_Request(Pv_Parent_Transaction_Type => Pr_Shipment_Header_Rec.Parent_Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验shipment行数量是否足够';
Validate_Quantity(Pv_Transaction_Type => Pr_Shipment_Header_Rec.Transaction_Type
,Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验物料是否有效';
Validate_Item(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验Xxcus_Inv_Manufacturers，制造商是否无效';
Validate_Manufacturer(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果是费用料号，则不允许费用料号入资产仓或者资产料号入费用仓'; --发运要求检验
Validate_Itemsub(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '如果批次控制，批次是否为空，如果是退货，批次是否与入库批次一致'; --发运要求检验
Validate_Lot(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
Lv_Process_Step := '检验子库和货位是否存在和有效'; --发运要求检验
Validate_Sub_Loc(Pr_Shipment_Rec => Lt_Ship_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
IF Lv_Ret_Status != Fnd_Api.g_Ret_Sts_Success THEN
RAISE Le_Step_Error;
END IF;
IF Xxcus_Fnd_Message.Count_Msg >= 1 THEN
--添加发运行验证状态
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Lt_Ship_Rec.Error_Message := Xxcus_Fnd_Message.Get_All_Msg;
Xv_Ret_Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_e;
Xv_Ret_Message := '检验数据存在异常';
ELSE
Lt_Ship_Rec.Status := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Status_s;
END IF;
Pr_Shipment_Header_Rec.Shipment_Lines(i) := Lt_Ship_Rec;
END LOOP;
EXCEPTION
WHEN Le_Step_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Returnv_Validate;
--通用的验证
--各种类型的验证单独再里面有逻辑
PROCEDURE Transaction_Validate(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2 --程序是否正常完成
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Transaction_Validate';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lt_Ship_Rec Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Line_Record;
Le_Step_Error EXCEPTION;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
IF Pr_Shipment_Header_Rec.Parent_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Rd AND
Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_r THEN
--接口为接收并入库，事务处理为接收，则进行校验，后面的检验和入库不需要再校验
Receive_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Parent_Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Trntype_Ad AND
Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_a THEN
--接口为检验并入库，并且为检验的校验阶段，则进行校验，后面的入库不进行校验
Accept_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
ELSE
IF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_r THEN
Lv_Process_Step := '校验接收';
Receive_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_a THEN
Lv_Process_Step := '校验接收';
Accept_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_d THEN
Lv_Process_Step := '校验接收';
Delivery_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_Rv THEN
Lv_Process_Step := '校验接收';
Returnv_Validate(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Xv_Ret_Status --程序是否正常完成
,Xv_Ret_Message => Xv_Ret_Message);
END IF;
END IF;
EXCEPTION
WHEN Le_Step_Error THEN
Xv_Ret_Status := Lv_Ret_Status;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || Lv_Ret_Message;
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Transaction_Validate;
BEGIN
-- Initialization
NULL;
END Xxcus_Rcv_Rd_Validate_Pkg;
数据库表和interface数据处理
包头
CREATE OR REPLACE PACKAGE Xxcus_Rcv_Rd_Datatransfer_Pkg IS
-- Author : BRAYDEN.LIU
-- Created : 2023/1/30 16:16:43
-- Purpose : 采购接收入库table数据处理程序包
-- Public type declarations
PROCEDURE Insert_Api_Table(Pr_Rcv_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
PROCEDURE Sync_Api_Table(Pr_Rcv_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
PROCEDURE Insert_Shipment_Table(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
PROCEDURE Sync_Shipment_Table(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
PROCEDURE Insert_Receive_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
--进行采购检验
PROCEDURE Insert_Accept_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
--进行采购接收
PROCEDURE Insert_Delivery_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
--进行采购接收
PROCEDURE Insert_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2);
END Xxcus_Rcv_Rd_Datatransfer_Pkg;
包体
CREATE OR REPLACE PACKAGE BODY Xxcus_Rcv_Rd_Datatransfer_Pkg IS
Gv_Package_Name VARCHAR2(200) := 'Xxcus_Rcv_Rd_Datatransfer_Pkg.'; --当前程序包名称
PROCEDURE Insert_Api_Table(Pr_Rcv_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_Api_Table';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Xxcus_Rd_Rcv_Headers_s.Nextval
INTO Pr_Rcv_Header_Rec.Rcv_Header_Id
FROM Dual;
INSERT INTO Xxcus_Rd_Rcv_Headers_t
(Shipment_Number
,Shipment_Header_Id
,Transaction_Type
,User_Name
,Rcv_Header_Id
,Batch_Id
--,Process_Step
,Process_Step_Name
,Process_Status
,Process_Message
,Status
,Error_Message
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Rcv_Header_Rec.Shipment_Number
,Pr_Rcv_Header_Rec.Shipment_Header_Id
,Pr_Rcv_Header_Rec.Transaction_Type
,Pr_Rcv_Header_Rec.User_Name
,Pr_Rcv_Header_Rec.Rcv_Header_Id
,Pr_Rcv_Header_Rec.Batch_Id
,Pr_Rcv_Header_Rec.Process_Step
--,Pr_Rcv_Header_Rec.Process_Step_Name
,Pr_Rcv_Header_Rec.Process_Status
,Pr_Rcv_Header_Rec.Process_Message
,Pr_Rcv_Header_Rec.Status
,Pr_Rcv_Header_Rec.Error_Message
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
FOR i IN 1 .. Pr_Rcv_Header_Rec.Rcv_Steps.Count LOOP
SELECT Xxcus_Rd_Rcv_Step_s.Nextval
INTO Pr_Rcv_Header_Rec.Rcv_Steps(i).Rcv_Step_Id
FROM Dual;
Pr_Rcv_Header_Rec.Rcv_Steps(i).Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Header_Id;
INSERT INTO Xxcus_Rd_Rcv_Step_t
(Rcv_Header_Id
,Rcv_Step_Id
,Step_Code
,Status
,Error_Message
,Step_Date
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Rcv_Header_Rec.Rcv_Steps(i).Rcv_Header_Id
,Pr_Rcv_Header_Rec.Rcv_Steps(i).Rcv_Step_Id
,Pr_Rcv_Header_Rec.Rcv_Steps(i).Step_Code
,Pr_Rcv_Header_Rec.Rcv_Steps(i).Status
,Pr_Rcv_Header_Rec.Rcv_Steps(i).Error_Message
,Nvl(Pr_Rcv_Header_Rec.Rcv_Steps(i).Step_Date, SYSDATE)
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
END LOOP;
FOR i IN 1 .. Pr_Rcv_Header_Rec.Rcv_Lines.Count LOOP
SELECT Xxcus_Rd_Lines_s.Nextval
INTO Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Line_Id
FROM Dual;
Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Header_Id;
INSERT INTO Xxcus_Rd_Rcv_Lines_t
(Rcv_Header_Id
,Rcv_Line_Id
,Shipment_Line_Id
,Ship_Quantity
,Quality_Type
,Subinventory_Code
,Locator_Id
,Transaction_Date
,Manufacturer_Number
,Source_Code
,Source_Line_Id
,Rt_Transaction_Id_In
,Status
,Error_Message
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Header_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Line_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Line_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Ship_Quantity
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Quality_Type
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Subinventory_Code
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Locator_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Transaction_Date
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Manufacturer_Number
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Source_Code
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Source_Line_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Rt_Transaction_Id_In
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Status
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Error_Message
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
FOR j IN 1 .. Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl.Count LOOP
SELECT Xxcus_Rd_Lots_s.Nextval
INTO Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Lot_Id
FROM Dual;
Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Header_Id := Pr_Rcv_Header_Rec.Rcv_Header_Id;
Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Line_Id := Pr_Rcv_Header_Rec.Rcv_Lines(i)
.Rcv_Line_Id;
INSERT INTO Xxcus_Rd_Rcv_Lots_t
(Rcv_Line_Id
,Rcv_Header_Id
,Rcv_Lot_Id
,Lot_Number
,Ship_Quantity
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Line_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Header_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Rcv_Lot_Id
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Lot_Number
,Pr_Rcv_Header_Rec.Rcv_Lines(i).Shipment_Lot_Tbl(j).Ship_Quantity
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Api_Table;
PROCEDURE Sync_Api_Table(Pr_Rcv_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Rcv_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Sync_Api_Table';
Lv_Process_Step VARCHAR2(3000);
Lv_Key VARCHAR2(200);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
UPDATE Xxcus_Rd_Rcv_Headers_t t
SET t.Last_Update_Date = SYSDATE
,t.Status = Pr_Rcv_Header_Rec.Status
,t.Error_Message = Pr_Rcv_Header_Rec.Error_Message
,t.Process_Step = Pr_Rcv_Header_Rec.Process_Step
,t.Process_Status = Pr_Rcv_Header_Rec.Process_Status
,t.Process_Message = Pr_Rcv_Header_Rec.Process_Message
--,t.Group_Id = Pr_rcv_Header_Rec.Group_Id
WHERE t.Rcv_Header_Id = Pr_Rcv_Header_Rec.Rcv_Header_Id;
FOR i IN 1 .. Pr_Rcv_Header_Rec.Rcv_Lines.Count LOOP
UPDATE Xxcus_Rd_Rcv_Lines_t t
SET t.Last_Update_Date = SYSDATE
,t.Status = Pr_Rcv_Header_Rec.Rcv_Lines(i).Status
,t.Error_Message = Pr_Rcv_Header_Rec.Rcv_Lines(i).Error_Message
,t.Rt_Transaction_Id = Pr_Rcv_Header_Rec.Rcv_Lines(i).Rt_Transaction_Id
WHERE t.Rcv_Line_Id = Pr_Rcv_Header_Rec.Rcv_Lines(i).Rcv_Line_Id;
END LOOP;
Dbms_Output.Put_Line('Pr_Rcv_Header_Rec.Rcv_Steps.Count:' || Pr_Rcv_Header_Rec.Rcv_Steps.Count);
--Lv_Key := Pr_Rcv_Header_Rec.Rcv_Steps.First;
FOR i IN 1 .. Pr_Rcv_Header_Rec.Rcv_Steps.Count LOOP
UPDATE Xxcus_Rd_Rcv_Step_t t
SET t.Status = Pr_Rcv_Header_Rec.Rcv_Steps(i).Status
,t.Error_Message = Pr_Rcv_Header_Rec.Rcv_Steps(i).Error_Message
,t.Step_Date = Nvl(Pr_Rcv_Header_Rec.Rcv_Steps(i).Step_Date, SYSDATE)
WHERE t.Rcv_Step_Id = Pr_Rcv_Header_Rec.Rcv_Steps(i).Rcv_Step_Id
AND t.Status != Pr_Rcv_Header_Rec.Rcv_Steps(i).Status;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Sync_Api_Table;
PROCEDURE Insert_Shipment_Table(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_shipment_Table';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Xxcus_Rd_Headers_s.Nextval
INTO Pr_Shipment_Header_Rec.Header_Id
FROM Dual;
INSERT INTO Xxcus_Rd_Headers_t
(Shipment_Number
,Shipment_Header_Id
,Transaction_Type
,User_Name
,Header_Id
,Rcv_Header_Id
,Rcv_Step_Id
,Group_Id
,Batch_Id
,Ou_Id
,User_Id
,Resp_Id
,Resp_Appl_Id
,Process_Step
,Process_Step_Name
,Process_Status
,Process_Message
,Status
,Error_Message
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Shipment_Header_Rec.Shipment_Number
,Pr_Shipment_Header_Rec.Shipment_Header_Id
,Pr_Shipment_Header_Rec.Transaction_Type
,Pr_Shipment_Header_Rec.User_Name
,Pr_Shipment_Header_Rec.Header_Id
,Pr_Shipment_Header_Rec.Rcv_Header_Id
,Pr_Shipment_Header_Rec.Rcv_Step_Id
,Pr_Shipment_Header_Rec.Group_Id
,Pr_Shipment_Header_Rec.Batch_Id
,Pr_Shipment_Header_Rec.Ou_Id
,Pr_Shipment_Header_Rec.User_Id
,Pr_Shipment_Header_Rec.Resp_Id
,Pr_Shipment_Header_Rec.Resp_Appl_Id
,Pr_Shipment_Header_Rec.Process_Step
,Pr_Shipment_Header_Rec.Process_Step_Name
,Pr_Shipment_Header_Rec.Process_Status
,Pr_Shipment_Header_Rec.Process_Message
,Pr_Shipment_Header_Rec.Status
,Pr_Shipment_Header_Rec.Error_Message
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
SELECT Xxcus_Rd_Lines_s.Nextval
INTO Pr_Shipment_Header_Rec.Shipment_Lines(i).Line_Id
FROM Dual;
Pr_Shipment_Header_Rec.Shipment_Lines(i).Header_Id := Pr_Shipment_Header_Rec.Header_Id;
INSERT INTO Xxcus_Rd_Lines_t
(Header_Id
,Line_Id
,Rcv_Header_Id
,Rcv_Line_Id
,Rcv_Step_Id
,Shipment_Line_Id
,Ship_Quantity
,Quality_Type
,Subinventory_Code
,Locator_Id
,Transaction_Date
,Manufacturer_Number
,Source_Code
,Source_Line_Id
,To_Organization_Id
,Inventory_Item_Id
,Po_Line_Id
,Po_Line_Location_Id
,Po_Distribution_Id
,Rate_Type
,Rate_Date
,Rate
,Status
,Error_Message
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Shipment_Header_Rec.Shipment_Lines(i).Header_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rcv_Header_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rcv_Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rcv_Step_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Ship_Quantity
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Quality_Type
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Subinventory_Code
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Locator_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Transaction_Date
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Manufacturer_Number
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Source_Code
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Source_Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).To_Organization_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Inventory_Item_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Po_Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Po_Line_Location_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Po_Distribution_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate_Type
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate_Date
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Status
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Error_Message
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
FOR j IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl.Count LOOP
SELECT Xxcus_Rd_Lots_s.Nextval
INTO Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Lot_Id
FROM Dual;
Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Header_Id := Pr_Shipment_Header_Rec.Header_Id;
Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Line_Id;
INSERT INTO Xxcus_Rd_Lots_t
(Line_Id
,Header_Id
,Lot_Id
,Rcv_Header_Id
,Rcv_Line_Id
,Rcv_Lot_Id
,Rcv_Step_Id
,Lot_Number
,Ship_Quantity
,Last_Update_Date
,Last_Updated_By
,Created_By
,Creation_Date
,Last_Update_Login)
VALUES
(Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Header_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Lot_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Rcv_Header_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Rcv_Line_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Rcv_Lot_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Rcv_Step_Id
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Lot_Number
,Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j).Ship_Quantity
,SYSDATE
,-1
,-1
,SYSDATE
,-1);
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Shipment_Table;
PROCEDURE Sync_Shipment_Table(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Sync_shipment_Table';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
UPDATE Xxcus_Rd_Headers_t t
SET t.Last_Update_Date = SYSDATE
,t.Status = Pr_Shipment_Header_Rec.Status
,t.Error_Message = Pr_Shipment_Header_Rec.Error_Message
,t.Process_Step = Pr_Shipment_Header_Rec.Process_Step
,t.Process_Status = Pr_Shipment_Header_Rec.Process_Status
,t.Process_Message = Pr_Shipment_Header_Rec.Process_Message
,t.Group_Id = Pr_Shipment_Header_Rec.Group_Id
WHERE t.Header_Id = Pr_Shipment_Header_Rec.Header_Id;
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
UPDATE Xxcus_Rd_Lines_t t
SET t.Last_Update_Date = SYSDATE
,t.Po_Distribution_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Po_Distribution_Id
,t.Inventory_Item_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Inventory_Item_Id
,t.To_Organization_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).To_Organization_Id
,t.Transaction_Date = Pr_Shipment_Header_Rec.Shipment_Lines(i).Transaction_Date
,t.Rate_Type = Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate_Type
,t.Rate_Date = Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate_Date
,t.Rate = Pr_Shipment_Header_Rec.Shipment_Lines(i).Rate
,t.Transaction_Uom = Pr_Shipment_Header_Rec.Shipment_Lines(i).Transaction_Uom
,t.Po_Uom = Pr_Shipment_Header_Rec.Shipment_Lines(i).Po_Uom
,t.Manufacturer_Number = Pr_Shipment_Header_Rec.Shipment_Lines(i).Manufacturer_Number
,t.Source_Code = Pr_Shipment_Header_Rec.Shipment_Lines(i).Source_Code
,t.Source_Line_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Source_Line_Id
,t.Rt_Transaction_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Rt_Transaction_Id
,t.Rt_Transaction_Id_In = Pr_Shipment_Header_Rec.Shipment_Lines(i).Rt_Transaction_Id_In
WHERE t.Line_Id = Pr_Shipment_Header_Rec.Shipment_Lines(i).Line_Id;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Sync_Shipment_Table;
--接口表通用数据插入
PROCEDURE Insert_Interface_Common(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Pn_Line_Index IN NUMBER
,Lr_Iface_Rcv_Rec IN OUT Po.Rcv_Transactions_Interface%ROWTYPE
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'insert_interface_common';
Lv_Process_Step VARCHAR2(3000);
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
SELECT Rcv_Transactions_Interface_s.Nextval
INTO Lr_Iface_Rcv_Rec.Interface_Transaction_Id
FROM Dual;
Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index).Rt_Interface_Id := Lr_Iface_Rcv_Rec.Interface_Transaction_Id;
Lr_Iface_Rcv_Rec.Last_Update_Date := SYSDATE;
Lr_Iface_Rcv_Rec.Last_Updated_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Iface_Rcv_Rec.Creation_Date := SYSDATE;
Lr_Iface_Rcv_Rec.Created_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Iface_Rcv_Rec.Last_Update_Login := -1;
Lr_Iface_Rcv_Rec.Processing_Mode_Code := Pr_Shipment_Header_Rec.Commit_Type; --'ONLINE'; --'BATCH';
Lr_Iface_Rcv_Rec.Shipment_Header_Id := Pr_Shipment_Header_Rec.Shipment_Header_Id;
Lr_Iface_Rcv_Rec.Shipment_Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Shipment_Line_Id;
Lr_Iface_Rcv_Rec.Vendor_Id := Pr_Shipment_Header_Rec.Vendor_Id;
Lr_Iface_Rcv_Rec.Vendor_Site_Id := Pr_Shipment_Header_Rec.Vendor_Site_Id;
Lr_Iface_Rcv_Rec.Po_Header_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Header_Id;
Lr_Iface_Rcv_Rec.Po_Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Line_Id;
Lr_Iface_Rcv_Rec.Po_Line_Location_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Line_Location_Id;
Lr_Iface_Rcv_Rec.Po_Release_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Release_Id;
Lr_Iface_Rcv_Rec.Category_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Category_Id;
Lr_Iface_Rcv_Rec.Item_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Inventory_Item_Id;
Lr_Iface_Rcv_Rec.Item_Description := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Item_Description;
-- Lr_Iface_Rcv_Rec.Unit_Of_Measure
Lr_Iface_Rcv_Rec.To_Organization_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.To_Organization_Id;
Lr_Iface_Rcv_Rec.Org_Id := Pr_Shipment_Header_Rec.Ou_Id;
Lr_Iface_Rcv_Rec.Ship_To_Location_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Ship_To_Location_Id;
Lr_Iface_Rcv_Rec.Location_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Location_Id;
Lr_Iface_Rcv_Rec.Uom_Code := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Transaction_Uom; --不传也行，但是WMS接口有传，所以继承过来
Lr_Iface_Rcv_Rec.Primary_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Ship_Quantity; --不传也行，但是WMS接口有传，所以继承过来
Lr_Iface_Rcv_Rec.Po_Distribution_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Distribution_Id;
Lr_Iface_Rcv_Rec.Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Ship_Quantity;
Lr_Iface_Rcv_Rec.Po_Unit_Price := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Po_Unit_Price;
Lr_Iface_Rcv_Rec.Unit_Of_Measure := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Transaction_Uom;
Lr_Iface_Rcv_Rec.Transaction_Date := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Transaction_Date; --To_Date('2022-12-29 12:21:00', 'yyyy-mm-dd hh24:mi:ss'); --SYSDATE;
Lr_Iface_Rcv_Rec.Interface_Source_Code := Xxcus_Rcv_Rd_Handle_Pkg.Gv_Interface_Source_Code; --'rcv test 1';
Lr_Iface_Rcv_Rec.Interface_Source_Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Line_Id; --'987654321';
--汇率转换
Lr_Iface_Rcv_Rec.Currency_Code := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Currency_Code;
Lr_Iface_Rcv_Rec.Currency_Conversion_Type := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Rate_Type;
Lr_Iface_Rcv_Rec.Currency_Conversion_Date := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index)
.Rate_Date;
Lr_Iface_Rcv_Rec.Currency_Conversion_Rate := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index).Rate;
Lr_Iface_Rcv_Rec.Employee_Id := Pr_Shipment_Header_Rec.Shipment_Lines(Pn_Line_Index).Employee_Id; --不传也行，但是WMS接口有传，所以继承过来
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Interface_Common;
--进行采购接收
PROCEDURE Insert_Receive_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'insert_Receive_inerface';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Ln_Employee_Id NUMBER := 9350; --实际执行不必要，WMS有此逻辑所以加上
Lr_Iface_Rcv_Rec Po.Rcv_Transactions_Interface%ROWTYPE;
--Ln_User_Id NUMBER := Fnd_Global.User_Id;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
SELECT Po.Rcv_Interface_Groups_s.Nextval
INTO Lr_Iface_Rcv_Rec.Group_Id
FROM Dual;
Pr_Shipment_Header_Rec.Group_Id := Lr_Iface_Rcv_Rec.Group_Id;
Lr_Iface_Rcv_Rec.Header_Interface_Id := NULL; --ASN接收不需要传递该字段
Lv_Process_Step := '循环插入行表';
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Insert_Interface_Common(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pn_Line_Index => i
,Lr_Iface_Rcv_Rec => Lr_Iface_Rcv_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Lr_Iface_Rcv_Rec.Processing_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Receipt_Source_Code := 'VENDOR';
Lr_Iface_Rcv_Rec.Source_Document_Code := 'PO';
Lr_Iface_Rcv_Rec.Transaction_Type := 'RECEIVE';
Lr_Iface_Rcv_Rec.Auto_Transact_Code := 'RECEIVE'; --仅接收使用
Lr_Iface_Rcv_Rec.Destination_Type_Code := 'RECEIVING';
INSERT INTO Po.Rcv_Transactions_Interface
VALUES Lr_Iface_Rcv_Rec;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Receive_Inerface;
--进行采购检验
PROCEDURE Insert_Accept_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_Accept_Inerface';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lr_Iface_Rcv_Rec Po.Rcv_Transactions_Interface%ROWTYPE;
Ln_Employee_Id NUMBER; --质检和入库取rt表
--Ln_User_Id NUMBER := Fnd_Global.User_Id;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
SELECT Po.Rcv_Interface_Groups_s.Nextval
INTO Lr_Iface_Rcv_Rec.Group_Id
FROM Dual;
Pr_Shipment_Header_Rec.Group_Id := Lr_Iface_Rcv_Rec.Group_Id;
Lr_Iface_Rcv_Rec.Header_Interface_Id := NULL; --ASN接收不需要传递该字段
Lv_Process_Step := '循环插入行表';
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
IF Nvl(Pr_Shipment_Header_Rec.Shipment_Lines(i).Status, 'S') != 'N' THEN
--N为初始化数据时候，该物料熟悉为不要求检验，直接过就可以
--无需检验物料不插入
Insert_Interface_Common(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pn_Line_Index => i
,Lr_Iface_Rcv_Rec => Lr_Iface_Rcv_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Lr_Iface_Rcv_Rec.Processing_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Receipt_Source_Code := 'VENDOR';
Lr_Iface_Rcv_Rec.Source_Document_Code := 'PO';
--Lr_Iface_Rcv_Rec.Destination_Context := 'RECEIVING';--没有这个值也可以接收成功
Lr_Iface_Rcv_Rec.Destination_Type_Code := 'RECEIVING';
Lr_Iface_Rcv_Rec.Parent_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Transaction_Id_In;
Lr_Iface_Rcv_Rec.Transaction_Type := 'ACCEPT';
IF Nvl(Pr_Shipment_Header_Rec.Shipment_Lines(i).Quality_Type, 'QUALIFIED') = 'QUALIFIED' THEN
Lr_Iface_Rcv_Rec.Inspection_Status_Code := 'ACCEPTED';
ELSE
Lr_Iface_Rcv_Rec.Inspection_Status_Code := 'REJECTED';
END IF;
--l_Iface_Rcv_Rec.Parent_Transaction_Id := 1302098;
INSERT INTO Po.Rcv_Transactions_Interface
VALUES Lr_Iface_Rcv_Rec;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Accept_Inerface;
--进行采购接收
PROCEDURE Insert_Delivery_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_Delivery_Inerface';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lr_Iface_Rcv_Rec Po.Rcv_Transactions_Interface%ROWTYPE;
Ll_Iface_Mtl_Lots_Rec Inv.Mtl_Transaction_Lots_Interface%ROWTYPE;
Lr_Rcv_Lot_Rec Rcv_Lots_Interface%ROWTYPE;
Ln_Employee_Id NUMBER; --质检和入库取rt表
Ln_Group_Id NUMBER;
--Ln_User_Id NUMBER := Fnd_Global.User_Id;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
SELECT Po.Rcv_Interface_Groups_s.Nextval
INTO Ln_Group_Id
FROM Dual;
Pr_Shipment_Header_Rec.Group_Id := Ln_Group_Id;
Lr_Iface_Rcv_Rec.Header_Interface_Id := NULL; --ASN接收不需要传递该字段
Lv_Process_Step := '循环插入行表';
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Lr_Iface_Rcv_Rec := NULL; --初始化
Lr_Iface_Rcv_Rec.Group_Id := Ln_Group_Id;
Insert_Interface_Common(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pn_Line_Index => i
,Lr_Iface_Rcv_Rec => Lr_Iface_Rcv_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Lr_Iface_Rcv_Rec.Processing_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Receipt_Source_Code := 'VENDOR';
Lr_Iface_Rcv_Rec.Source_Document_Code := 'PO';
Lr_Iface_Rcv_Rec.Transaction_Type := 'DELIVER';
Lr_Iface_Rcv_Rec.Destination_Type_Code := 'INVENTORY';
Lr_Iface_Rcv_Rec.Parent_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Transaction_Id;
Lr_Iface_Rcv_Rec.Subinventory := Pr_Shipment_Header_Rec.Shipment_Lines(i).Subinventory_Code;
Lr_Iface_Rcv_Rec.Locator_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i).Locator_Id;
--l_Iface_Rcv_Rec.Parent_Transaction_Id := 1302098;
--入库时候才给批次，接收和检验不需要
FOR j IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl.Count LOOP
Lr_Iface_Rcv_Rec.Use_Mtl_Lot := 2; --如果是批次控制则，必须给该值
SELECT Mtl_Material_Transactions_s.Nextval
INTO Ll_Iface_Mtl_Lots_Rec.Transaction_Interface_Id
FROM Dual;
Ll_Iface_Mtl_Lots_Rec.Last_Update_Date := SYSDATE;
Ll_Iface_Mtl_Lots_Rec.Last_Updated_By := Pr_Shipment_Header_Rec.User_Id;
Ll_Iface_Mtl_Lots_Rec.Creation_Date := SYSDATE;
Ll_Iface_Mtl_Lots_Rec.Created_By := Pr_Shipment_Header_Rec.User_Id;
Ll_Iface_Mtl_Lots_Rec.Last_Update_Login := -1;
Ll_Iface_Mtl_Lots_Rec.Product_Code := 'RCV';
Ll_Iface_Mtl_Lots_Rec.Product_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Transaction_Id_In;
Ll_Iface_Mtl_Lots_Rec.Lot_Number := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Lot_Number;
Ll_Iface_Mtl_Lots_Rec.Transaction_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Ship_Quantity;
Ll_Iface_Mtl_Lots_Rec.Primary_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Ship_Quantity;
Ll_Iface_Mtl_Lots_Rec.Origination_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Transaction_Date;
Ll_Iface_Mtl_Lots_Rec.Serial_Transaction_Temp_Id := NULL;
Ll_Iface_Mtl_Lots_Rec.Source_Code := NULL;
--采购入库，不插入Rcv_Lots_Interface，而是插入Mtl_Transaction_Lots_Interface
INSERT INTO Mtl_Transaction_Lots_Interface
VALUES Ll_Iface_Mtl_Lots_Rec;
----
--==========Rcv_Lots_Interface这个表也要插入，否则rcv_lot表没有数据，无法退货
Lr_Rcv_Lot_Rec.Interface_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Interface_Id;
Lr_Rcv_Lot_Rec.Lot_Num := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Lot_Number;
Lr_Rcv_Lot_Rec.Item_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Inventory_Item_Id;
Lr_Rcv_Lot_Rec.Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Ship_Quantity;
Lr_Rcv_Lot_Rec.Primary_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Ship_Quantity;
/*Lr_Rcv_Lot_Rec.Expiration_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Transaction_Date;*/
Lr_Rcv_Lot_Rec.Transaction_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i).Transaction_Date;
Lr_Rcv_Lot_Rec.Shipment_Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Line_Id;
Lr_Rcv_Lot_Rec.Last_Update_Date := SYSDATE;
Lr_Rcv_Lot_Rec.Last_Updated_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Rcv_Lot_Rec.Creation_Date := SYSDATE;
Lr_Rcv_Lot_Rec.Created_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Rcv_Lot_Rec.Last_Update_Login := -1;
INSERT INTO Rcv_Lots_Interface
VALUES Lr_Rcv_Lot_Rec;
END LOOP;
INSERT INTO Po.Rcv_Transactions_Interface
VALUES Lr_Iface_Rcv_Rec;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Delivery_Inerface;
--进行退回到供应商
PROCEDURE Insert_Returnv_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_Returnv_Inerface';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
Lr_Iface_Rcv_Rec Po.Rcv_Transactions_Interface%ROWTYPE;
Ll_Iface_Mtl_Lots_Rec Inv.Mtl_Transaction_Lots_Interface%ROWTYPE;
Lr_Rcv_Lot_Rec Rcv_Lots_Interface%ROWTYPE;
Ln_Employee_Id NUMBER; --质检和入库取rt表
Ln_Group_Id NUMBER;
--Ln_User_Id NUMBER := Fnd_Global.User_Id;
BEGIN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Success;
Lv_Process_Step := '初始化数据';
SELECT Po.Rcv_Interface_Groups_s.Nextval
INTO Ln_Group_Id
FROM Dual;
Pr_Shipment_Header_Rec.Group_Id := Ln_Group_Id;
Lr_Iface_Rcv_Rec.Header_Interface_Id := NULL; --ASN接收不需要传递该字段
Lv_Process_Step := '循环插入行表';
FOR i IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines.Count LOOP
Lr_Iface_Rcv_Rec := NULL; --初始化
Lr_Iface_Rcv_Rec.Group_Id := Ln_Group_Id;
Insert_Interface_Common(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Pn_Line_Index => i
,Lr_Iface_Rcv_Rec => Lr_Iface_Rcv_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
Lr_Iface_Rcv_Rec.Processing_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Transaction_Status_Code := 'PENDING';
Lr_Iface_Rcv_Rec.Receipt_Source_Code := 'VENDOR';
Lr_Iface_Rcv_Rec.Source_Document_Code := 'PO';
Lr_Iface_Rcv_Rec.Transaction_Type := 'RETURN TO VENDOR';
Lr_Iface_Rcv_Rec.Destination_Type_Code := 'RECEIVING';
Lr_Iface_Rcv_Rec.Parent_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Transaction_Id_In;
Lr_Iface_Rcv_Rec.From_Subinventory := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Subinventory_Code;
Lr_Iface_Rcv_Rec.From_Locator_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i).Locator_Id;
Lr_Iface_Rcv_Rec.From_Organization_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.To_Organization_Id;
--l_Iface_Rcv_Rec.Parent_Transaction_Id := 1302098;
--入库时候才给批次，接收和检验不需要
FOR j IN 1 .. Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl.Count LOOP
--Lr_Iface_Rcv_Rec.Use_Mtl_Lot := 2; --如果是批次控制则，必须给该值
SELECT Mtl_Material_Transactions_s.Nextval
INTO Ll_Iface_Mtl_Lots_Rec.Transaction_Interface_Id
FROM Dual;
Ll_Iface_Mtl_Lots_Rec.Last_Update_Date := SYSDATE;
Ll_Iface_Mtl_Lots_Rec.Last_Updated_By := Pr_Shipment_Header_Rec.User_Id;
Ll_Iface_Mtl_Lots_Rec.Creation_Date := SYSDATE;
Ll_Iface_Mtl_Lots_Rec.Created_By := Pr_Shipment_Header_Rec.User_Id;
Ll_Iface_Mtl_Lots_Rec.Last_Update_Login := -1;
Ll_Iface_Mtl_Lots_Rec.Product_Code := 'RCV';
Ll_Iface_Mtl_Lots_Rec.Product_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Interface_Id;
Ll_Iface_Mtl_Lots_Rec.Lot_Number := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Lot_Number;
Ll_Iface_Mtl_Lots_Rec.Transaction_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Ship_Quantity;
Ll_Iface_Mtl_Lots_Rec.Primary_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Ship_Quantity;
Ll_Iface_Mtl_Lots_Rec.Origination_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Transaction_Date;
Ll_Iface_Mtl_Lots_Rec.Serial_Transaction_Temp_Id := NULL;
Ll_Iface_Mtl_Lots_Rec.Source_Code := NULL;
--采购入库，不插入Rcv_Lots_Interface，而是插入Mtl_Transaction_Lots_Interface
INSERT INTO Mtl_Transaction_Lots_Interface
VALUES Ll_Iface_Mtl_Lots_Rec;
----
--==========Rcv_Lots_Interface这个表也要插入，否则rcv_lot表没有数据，无法退货
Lr_Rcv_Lot_Rec.Interface_Transaction_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Rt_Interface_Id;
Lr_Rcv_Lot_Rec.Lot_Num := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Lot_Number;
Lr_Rcv_Lot_Rec.Item_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Inventory_Item_Id;
Lr_Rcv_Lot_Rec.Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Ship_Quantity;
Lr_Rcv_Lot_Rec.Primary_Quantity := Pr_Shipment_Header_Rec.Shipment_Lines(i)
.Ship_Quantity;
/*Lr_Rcv_Lot_Rec.Expiration_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Lot_Tbl(j)
.Transaction_Date;*/
Lr_Rcv_Lot_Rec.Transaction_Date := Pr_Shipment_Header_Rec.Shipment_Lines(i).Transaction_Date;
Lr_Rcv_Lot_Rec.Shipment_Line_Id := Pr_Shipment_Header_Rec.Shipment_Lines(i).Shipment_Line_Id;
Lr_Rcv_Lot_Rec.Last_Update_Date := SYSDATE;
Lr_Rcv_Lot_Rec.Last_Updated_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Rcv_Lot_Rec.Creation_Date := SYSDATE;
Lr_Rcv_Lot_Rec.Created_By := Pr_Shipment_Header_Rec.User_Id;
Lr_Rcv_Lot_Rec.Last_Update_Login := -1;
INSERT INTO Rcv_Lots_Interface
VALUES Lr_Rcv_Lot_Rec;
END LOOP;
INSERT INTO Po.Rcv_Transactions_Interface
VALUES Lr_Iface_Rcv_Rec;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Returnv_Inerface;
--进行采购接收
PROCEDURE Insert_Inerface(Pr_Shipment_Header_Rec IN OUT Xxcus_Rcv_Rd_Handle_Pkg.Shipment_Header_Record
,Xv_Ret_Status OUT VARCHAR2
,Xv_Ret_Message OUT VARCHAR2) IS
Lv_Process_Name VARCHAR2(200) := Gv_Package_Name || 'Insert_Inerface';
Lv_Process_Step VARCHAR2(3000);
Lv_Ret_Status VARCHAR2(2000);
Lv_Ret_Message VARCHAR2(2000);
BEGIN
IF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_r THEN
Lv_Process_Step := '插入接收接口数据';
Insert_Receive_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_a THEN
Lv_Process_Step := '插入检验接口数据';
Insert_Accept_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_d THEN
Lv_Process_Step := '插入发运接口数据';
Insert_Delivery_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
ELSIF Pr_Shipment_Header_Rec.Transaction_Type = Xxcus_Rcv_Rd_Handle_Pkg.Gv_Process_Rv THEN
Lv_Process_Step := '插入退货接口数据';
Insert_Returnv_Inerface(Pr_Shipment_Header_Rec => Pr_Shipment_Header_Rec
,Xv_Ret_Status => Lv_Ret_Status
,Xv_Ret_Message => Lv_Ret_Message);
END IF;
EXCEPTION
WHEN OTHERS THEN
Xv_Ret_Status := Fnd_Api.g_Ret_Sts_Unexp_Error; --Fnd_Api.g_Ret_Sts_Unexp_Error;
Xv_Ret_Message := Lv_Process_Name || Lv_Process_Step || ' 时发生错误:' || SQLERRM;
END Insert_Inerface;
BEGIN
-- Initialization
NULL;
END Xxcus_Rcv_Rd_Datatransfer_Pkg;
1090
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
