develop:接收接口表
英文资料：
11.5.10 / R12 ROI How To Perform Receipt On Internal Requisition / Internal Sales Order Via Receiving Open Interface?
文档 ID:365200.1
Applies to:
Oracle Inventory Management - Version: 11.5.10 to 12.0
Information in this document applies to any platform.
EXECUTABLE:RVCTP - Receiving Transaction Processor
Goal
New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) is ROI supports for Internal Order
. RECEIVE transaction
. DELIVER transaction
How to enter a Receipt (Receive/Deliver Transactions) for an Internal Requisition / Internal Sales Order via the Receiving Open Interface?
Solution
Following SAMPLE script is intended to enter a Receive and Deliver to Inventory Transaction with Inventory destination type for an Internal Sales Order between 2 inventory organizations with Direct Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)
The script will load records into the tables
RCV_HEADERS_INTERFACE,
RCV_TRANSACTIONS_INTERFACE,
If the item is a lot or serial controlled standard item, records must also be inserted into
MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE tables.
------------------------SETUP------------------------
0) Ensure to apply the patches listed in Note 367396.1- Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J)
1) Inventory Setup: Organizations/Shipping Networks
from Sending Organization Org1 D2 Miami to Destination Organization Org2 M2 Boston
Transfer Type=Intransit
Receipt Routing=Direct
2) Miscellaneous Account receipt in org D2 On subinventory FGI for a quantity =500
has been entered on item ‘AS54888’
Item ‘AS54888’ is a standard item - Not a lot and serial controlled Item
SQL:
Select mp.organization_id Org_Id,
mp.organization_code Org_Code,
msi.inventory_item_id,
msi.segment1,
decode(to_char(msi.lot_control_code), '2', 'Y', '1', 'N') LOT_CONTROL,
decode(to_char(msi.serial_number_control_code), '1', 'None', '2', 'Predefined', '5', 'Dynamic at INV receipt', '6', 'Dynamic at SO issue') ERIAL_CONTROL from mtl_system_items_b msi,
mtl_parameters mp
where msi.segment1 like 'item'
and msi.organization_id=mp.organization_id;
It returns LOT_CONTROL='N' and SERIAL_CONTROL='None'inventory_item_id=149 for item=AS54888
3) In Purchasing responsibility
.Enter and approve Internal requisition number 5709
One line for inventory item AS54888 quantity=10 UOM=Each
with
Destination Organization=M2 Boston
Destination location= M2- Boston
Source=Inventory
Source Organization=D2 Miami
Source Subinventory=FGI
. Run the Create Internal Orders program to interface the Internal item to OM
4) In Order Management Responsibility, process the Internal Sales Order:
. Run Order Import concurrent program. (OEOIMP module: Order Import)
. Orders, Returns/Order Organizer
For Internal Requisition 5709 Internal Sales Order number 56753 has been created and booked
. Shipping/Release Sales Orders
Release the sales order 56743
. Shipping Transactions
1 Line related to delivery = 64883
Line status = Staged/Pick Confirmed
. Shipping/Ship Confirm Deliveries SRS
Run the program for Delivery = 64883
. Ensure to run WSHINTERFACE module: Interface Trip Stop
. Shipping / Transactions - For ISO Sales Order 56753
Line Status=Interfaced
Delivery status=Closed
5) In Purchasing Responsibility
Receiving/Receipts
In org M2 Boston
Find expected receipts
Source Type =Internal
Requisition = 5709
- Ensure 1 line is available to be receipted
Order type= Internal Order
Order =64883
(= Delivery name of Internal Sales order 56753)
6) Run the following scripts so to find the necessary information to insert
into the RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE table
for Receipt of internal Requisition Number 5709
6.1) SQL; Select rh.*
from po_requisition_headers_all rh
where segment1 like 'InternaReqNumber';
REQUISITION_HEADER_ID=56678
SEGMENT1=5709
TYPE_LOOKUP_CODE=INTERNAL
ORG_ID=204
6.2) SQL; Select rl.*
from po_requisition_lines_all rl,
po_requisition_headers_all rh
where rh.requisition_header_id = rl.requisition_header_id
and rh.requisition_header_id =ReqHeaderId;
REQUISITION_LINE_ID=60678
REQUISITION_HEADER_ID=56678
CATEGORY_ID=91
UNIT_MEAS_LOOKUP_CODE=Each
QUANTITY=10
DELIVER_TO_LOCATION_ID=209
TO_PERSON_ID=13536
SOURCE_TYPE_CODE=INVENTORY
ITEM_ID=149
QUANTITY_DELIVERED=0
SOURCE_ORGANIZATION_ID=210
DESTINATION_TYPE_CODE=INVENTORY
DESTINATION_ORGANIZATION_ID=209
ORG_ID=204
6.3) SQL; Select rsh.*
from rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_requisition_lines_all rl
where rsl.shipment_header_id = rsh.shipment_header_id
and rsl.requisition_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SHIPMENT_HEADER_ID=59417
RECEIPT_SOURCE_CODE=INTERNAL ORDER
ORGANIZATION_ID=210
SHIPMENT_NUM=64883
SHIP_TO_LOCATION_ID=209
SHIP_TO_ORG_ID=209
6.4) SQL; Select rsl.*
from rcv_shipment_lines rsl,
po_requisition_lines_all rl
where rsl.requisition_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SHIPMENT_LINE_ID=66367
SHIPMENT_HEADER_ID=59417
LINE_NUM=1
CATEGORY_ID=91
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=0
UNIT_OF_MEASURE=Each
ITEM_DESCRIPTION=Sentinel Standard Desktop
ITEM_ID=149
SHIPMENT_LINE_STATUS_CODE=EXPECTED
SOURCE_DOCUMENT_CODE=REQ
REQUISITION_LINE_ID=60678
REQ_DISTRIBUTION_ID=59631
ROUTING_HEADER_ID=3
FROM_ORGANIZATION_ID=210
DELIVER_TO_PERSON_ID=13536
DESTINATION_TYPE_CODE=INVENTORY
TO_ORGANIZATION_ID=209
DELIVER_TO_LOCATION_ID=209
SHIP_TO_LOCATION_ID=209
OE_ORDER_HEADER_ID=null
OE_ORDER_LINE_ID=null
AMOUNT_RECEIVED=0
6.5) SQL; Select ms.*
from mtl_supply ms,
po_requisition_lines_all rl
where ms.req_line_id = rl.requisition_line_id
and rl.requisition_line_id =RequisitionLineId;
SUPPLY_TYPE_CODE=SHIPMENT
SUPPLY_SOURCE_ID=66367
REQ_HEADER_ID=56678
REQ_LINE_ID=60678
SHIPMENT_HEADER_ID=59417
SHIPMENT_LINE_ID=66367
ITEM_ID=149
QUANTITY=10
UNIT_OF_MEASURE=Each
NEED_BY_DATE=05-APR-06
EXPECTED_DELIVERY_DATE=07-APR-06
DESTINATION_TYPE_CODE=INVENTORY
FROM_ORGANIZATION_ID=210
TO_ORGANIZATION_ID=209
6.6) You may also run the OMSE11i.SQL release 11i script for the Sales Order Note 133464.1
In order to find the sales order number please do the following in Purchasing responsibility:
1. Open Requisition Summary
2. Enter Requisition and Line then Find
3. View Sales Orders
If only the internal requisition number is known and not the sales order number,
following query can be used to find the sales order number:
Select ORDER_NUMBER, ORIG_SYS_DOCUMENT_REF, SOURCE_DOCUMENT_ID
from OE_ORDER_HEADERS_ALL
where ORIG_SYS_DOCUMENT_REF 'InternaReqNumber';
(SOURCE_DOCUMENT_ID = Requisition Header Id)
-RECEIVE/DELIVER to INVENTORY Transaction for Internal REQUISITION / Sales ORDER Example-
1) Insert via ROI a Direct DELIVER Receipt for Shipment Number '64883' (SHIPMENT_HEADER_ID=59417)
of 2 items in destination organization
Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM= '64883'
RECEIPT_SOURCE_CODE='INTERNAL ORDER'
and VALIDATION_FLAG='Y'
. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=66367
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INTERNAL ORDER' and SOURCE_DOCUMENT_CODE='REQ'
VALIDATION_FLAG='Y'
and specifying
REQUISITION_LINE_ID= 60678
REQ_DISTRIBUTION_ID= 59631
SQL; INSERT INTO RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
SHIPMENT_NUM,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
SHIPPED_DATE,
EMPLOYEE_ID,
VALIDATION_FLAG
)
VALUES
(rcv_headers_interface_s.nextval , --HEADER_INTERFACE_ID
rcv_interface_groups_s.nextval, --GROUP_ID
'PENDING', --PROCESSING_STATUS_CODE
'INTERNAL ORDER', --RECEIPT_SOURCE_CODE
'NEW', --TRANSACTION_TYPE
'DELIVER', --AUTO_TRANSACT_CODE
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATE_BY
0, --LAST_UPDATE_LOGIN
SYSDATE, --CREATION_DATE
0, --CREATED_BY
'64883', --SHIPMENT_NUM
209, --SHIP_TO_ORGANIZATION_ID
SYSDATE+1, --EXPECTED_RECEIPT_DATE
SYSDATE, --SHIPPED_DATE
13536, --EMPLOYEE_ID
'Y' --VALIDATION_FLAG
);
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
UNIT_OF_MEASURE,
INTERFACE_SOURCE_CODE,
ITEM_ID,
EMPLOYEE_ID,
AUTO_TRANSACT_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
SHIP_TO_LOCATION_ID,
RECEIPT_SOURCE_CODE,
TO_ORGANIZATION_ID,
SOURCE_DOCUMENT_CODE,
REQUISITION_LINE_ID,
REQ_DISTRIBUTION_ID,
DESTINATION_TYPE_CODE,
DELIVER_TO_PERSON_ID,
LOCATION_ID,
DELIVER_TO_LOCATION_ID,
SUBINVENTORY,
SHIPMENT_NUM,
EXPECTED_RECEIPT_DATE,
SHIPPED_DATE,
HEADER_INTERFACE_ID,
VALIDATION_FLAG
)
VALUES
( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID
rcv_interface_groups_s.currval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
0, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
0, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
2, --QUANTITY
'Each', --UNIT_OF_MEASURE
'RCV', --INTERFACE_SOURCE_CODE
149, --ITEM_ID
13536, --EMPLOYEE_ID
'DELIVER', --AUTO_TRANSACT_CODE
59417, --SHIPMENT_HEADER_ID
66367, --SHIPMENT_LINE_ID
209, --SHIP_TO_LOCATION_ID
'INTERNAL ORDER', --RECEIPT_SOURCE_CODE
209, --TO_ORGANIZATION_ID
'REQ', --SOURCE_DOCUMENT_CODE
60678, --REQUISITION_LINE_ID
59631, --REQ_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
13536, --DELIVER_TO_PERSON_ID
209, --LOCATION_ID
209, --DELIVER_TO_LOCATION_ID
'Stores', --SUBINVENTORY
'64883', --SHIPMENT_NUM
SYSDATE+1, --EXPECTED_RECEIPT_DATE,
SYSDATE, --SHIPPED_DATE
rcv_headers_interface_s.currval, --HEADER_INTERFACE_ID
'Y' --VALIDATION_FLAG
);
Commit;
2) Run the following scripts to check data have been correctly inserted
SQL; Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
RECEIPT_SOURCE_CODE=INTERNAL ORDER
SHIPMENT_NUM=64883
VALIDATION_FLAG=Y
SQL; Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
INTERFACE_TRANSACTION_ID=67448
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INTERNAL ORDER
TO_ORGANIZATION_ID=209
SOURCE_DOCUMENT_CODE=REQ
REQUISITION_LINE_ID=60678
REQ_DISTRIBUTION_ID=59631
SHIPMENT_NUM=64883
VALIDATION_FLAG=Y
3) In Purchasing Responsibility, Change to receiving organization M2 and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=21309)
4) Navigate to Receiving / Receiving Transactions Summary form
For Internal Requisition 5709, Receipt Number 5020 on Shipment Number 64883 has Receive and Deliver transaction.
5) Check how the following application tables have been populated/updated
SQL; Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like 'Shipment_Num';
GROUP_ID=21309
HEADER_INTERFACE_ID=40152
PROCESSING_STATUS_CODE=SUCCESS
RECEIPT_SOURCE_CODE=INTERNAL ORDER
SHIPMENT_NUM=64883
SQL; Select * from RCV_TRANSACTIONS where REQUISITION_LINE_ID=60678
It returns 2 records
For TRANSACTION_TYPE=RECEIVE
TRANSACTION_ID=110699
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=-1
QUANTITY=2
For TRANSACTION_TYPE=DELIVER
TRANSACTION_ID=110700
DESTINATION_TYPE_CODE=INVENTORY
PARENT_TRANSACTION_ID=110699
QUANTITY=2
References
Note 290489.1 - New Receiving Open Interface (ROI) Functionality in Procurement Family Pack J/11.5.10
Note 367396.1 - Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J)
Keywords
ROI; INTERNAL~ORDER; INTERNAL~REQUISITION; RECEIVING~OPEN~INTERFACE; RCV_HEADERS_INTERFACE; RCV_TRANSACTIONS_INTERFACE;
案例：
procedure insert_po_receipt_api(p_group_id number) is
/*
功能说明：根据组标识参数，从中间表中取出需要处理的9％订单信息；
将退货订单信息插入退货接收相关接口表，
包括：po.RCV_HEADERS_INTERFACE，po.RCV_TRANSACTIONS_INTERFACE，inv.MTL_TRANSACTION_LOTS_INTERFACE，
以及自定义的中间表HEK_OM_ZZC_LOT（验证批次状态转换时使用）。
过程中根据9％订单行的发货组织，取得相关的子库位、货位信息，用于创建批次接口表。
参数说明：
p_group_id：说明中间表中，需要处理（退货接收）的组标识；
*/
l_lot_no varchar2(30); --批次编号
l_iimb_item_id number; --ic_item_mst_b获取批次信息所需
l_subinventory varchar2(30); --子库存
l_locator varchar2(10); --子库存对应的货位
--接受中间表中的9%订单
cursor cur_order_header is
select h.header_id, h.sold_to_org_id
from ont.oe_order_headers_all h,
hek.hek_om_zzc_autocreate a
where h.header_id = a.old_order_id --guo 这里应该为9%订单
and nvl(a.status, 'Y') = 'Y' -- 永久失效的记录
and nvl(a.return_receive_status, 'N') = 'N' --没有接受的
and a.group_id = p_group_id
order by 1;
--9%订单行
cursor cur_order_line(g_header_id number) is
select l.header_id,
l.line_id,
l.sold_to_org_id,
l.ship_from_org_id,
l.ordered_item_id,
l.ordered_item,
l.ordered_quantity,
l.order_quantity_uom
from ont.oe_order_lines_all l
where nvl(l.ordered_quantity, 0) > 0
and l.header_id = g_header_id
order by l.line_id;
begin
--guo
----增加价格更改环节的验证
--if not count_error_status(p_group_id, '5') then
-- raise_application_error(-20000, '存在价格更改异常的退货订单记录');
--end if;
apps.fnd_file.put_line(apps.fnd_file.log,
rpad('begin', 15, '=') || '数据插入接收接口表');
init;
--序列（接受组标识）
select po.rcv_interface_groups_s.nextval into v_po_batch_id from dual;
apps.fnd_file.put_line(apps.fnd_file.log,
'退货订单接收组标识:+' || v_po_batch_id);
v_err := '10';
for rec_order_header in cur_order_header loop
v_err := 'begin INSERT INTO po.RCV_HEADERS_INTERFACE';
--RCV_HEADERS_INTERFACE存储接收头的信息
INSERT INTO po.RCV_HEADERS_INTERFACE
(HEADER_INTERFACE_ID, --唯一标识
GROUP_ID, --组标识
PROCESSING_STATUS_CODE, --头行处理状态
RECEIPT_SOURCE_CODE, --货物源类型
TRANSACTION_TYPE, --目的类型：NEW, REPLACE, ADD or CANCEL
AUTO_TRANSACT_CODE, --自动转换码??
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAsT_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
CUSTOMER_ID,
--SHIP_To_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE, --装运的预期抵达日期
VALIDATION_FLAG) --标记验证后，开放接口
VALUES
(po.rcv_headers_interface_s.nextval, --Header_Interface_Id
v_po_batch_id, --Group_Id
'PENDING', --Processing_Status_Code
'CUSTOMER', --Receipt_Source_Code --'VENDOR',--
'NEW', --'RECEIVE', --Transaction_Type new
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
g_user_id, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
g_user_id, --Created_By
rec_order_header.sold_to_org_id, --530941, --Customer_Id
--156, --rec_order_header.ship_from_org_id,--156, --Ship_To_Organization_Id,"ZZC"
SYSDATE, --Expected_Receipt_Date
'Y' --Validation_Flag
);
for rec_order_line in cur_order_line(rec_order_header.header_id) loop
v_err := 'begin get subinventory';
--取得接收仓库对应的子库存:来源于订单行的出货仓库（没有限定ZZC）
select msub.secondary_inventory_name
into l_subinventory --, msub.locator_type, msub.description
from mtl_secondary_inventories msub
where msub.organization_id =
nvl(rec_order_line.ship_from_org_id, msub.organization_id)
and nvl(msub.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
and exists
(select null
from mtl_system_items msi
where msi.organization_id =
nvl(rec_order_line.ship_from_org_id,
msi.organization_id)
and msi.inventory_item_id =
rec_order_line.ordered_item_id
and (msi.restrict_subinventories_code = 2 or
(msi.restrict_subinventories_code = 1 and exists
(select null
from mtl_item_sub_inventories mis
where mis.organization_id =
nvl(rec_order_line.ship_from_org_id,
mis.organization_id)
and mis.inventory_item_id = msi.inventory_item_id
and mis.secondary_inventory =
msub.secondary_inventory_name))))
and rownum = 1;
apps.fnd_file.put_line(apps.fnd_file.log, '子库存 ： ' || l_subinventory);
v_err := 'begin get locator';
--取得子库存的默认库位（默认第一个）
select l.segment1
into l_locator
from MTL_ITEM_LOCATIONS l
where l.enabled_flag = 'Y'
AND L.SUBINVENTORY_CODE = l_subinventory
AND ROWNUM = 1
ORDER BY 1;
apps.fnd_file.put_line(apps.fnd_file.log, '子库存库位 ： ' || l_locator);
v_err := 'begin INSERT INTO po.RCV_TRANSACTIONS_INTERFACE';
--存储接口交易处理信息
INSERT INTO po.RCV_TRANSACTIONS_INTERFACE
(INTERFACE_TRANSACTION_ID, --标识符
GROUP_ID, --组标识
HEADER_INTERFACE_ID, --头标识
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_TYPE, --接收类型
TRANSACTION_DATE, --接受日期
PROCESSING_STATUS_CODE, --处理状态
PROCESSING_MODE_CODE, --处理模式
TRANSACTION_STATUS_CODE, --交易状况接口行
QUANTITY, --数量
UNIT_OF_MEASURE, --单位
INTERFACE_SOURCE_CODE, --接口行
ITEM_ID, --项目编号ID
EMPLOYEE_ID, --接收人
AUTO_TRANSACT_CODE, --自动交易码
RECEIPT_SOURCE_CODE, --接受来源
TO_ORGANIZATION_ID, --目的地组织
SOURCE_DOCUMENT_CODE, --文档类型
DESTINATION_TYPE_CODE, --描述
DELIVER_TO_LOCATION_ID, --供货地点
SUBINVENTORY, --子库存
LOCATOR, --位置
EXPECTED_RECEIPT_DATE, --装运的预期抵达日期
OE_ORDER_HEADER_ID,
OE_ORDER_LINE_ID,
CUSTOMER_ID,
--CUSTOMER_SITE_ID,
VALIDATION_FLAG)
Values
(po.RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --INTERFACE_TRANSACTION_ID
v_po_batch_id, --RCV_INTERFACE_GROUPS_S.CURRVAL, --GROUP_ID
po.RCV_HEADERS_INTERFACE_S.CURRVAL, --HEADER_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
g_user_id, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
g_user_id, --CREATED_BY
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_MODE_CODE
rec_order_line.ordered_quantity, --3, --QUANTITY
rec_order_line.order_quantity_uom, --'箱', --UNIT_OF_MEASURE--================
'RCV', --INTERFACE_SOURCE_CODE
rec_order_line.ordered_item_id, --281, --ITEM_ID
4464, --EMPLOYEE_ID --default========
'DELIVER', --AUTO_TRANSACT_CODE
'CUSTOMER', --RECEIPT_SOURCE_CODE
rec_order_line.ship_from_org_id, --156, --TO_ORGANIZATION_ID--==========ZZC
'RMA', --SOURCE_DOCUMENT_CODE
'INVENTORY', --DESTINATION_TYPE_CODE
142, --DELIVER_TO_LOCATION_ID--==========
l_subinventory, --'PT1', --SUBINVENTORY
l_locator, --'A', --LOCATOR
SYSDATE, --EXPECTED_RECEIPT_DATE
rec_order_line.header_id, --193880, --OE_ORDER_HEADER_ID
rec_order_line.line_id, --398048, --OE_ORDER_LINE_ID
rec_order_line.sold_to_org_id, --530941, --CUSTOMER_ID--====================
--16134, --CUSTOMER_SITE_ID
'Y');
v_err := 'begin get lot_no';
--取得批次信息:rrrrmmdd+2位序号+'KA'；订单行中储存的是inventory_item_id，需转换为item_id
l_lot_no := '';
select iimb.item_id
into l_iimb_item_id
from ic_item_mst_b iimb
where iimb.item_no = rec_order_line.ordered_item;
select nvl(to_char(max(to_number(substr(a.lot_no, 1, 10))) + 1),
to_char(sysdate, 'rrrrmmdd') || '01') || 'KA'
into l_lot_no
from ic_lots_mst a
where substr(a.lot_no, 1, 8) = to_char(sysdate, 'yyyymmdd')
and to_number(substr(a.lot_no, 9, 2)) < 50 --09052302
and substr(a.lot_no, 11, 2) = 'KA'
and a.item_id = l_iimb_item_id;
apps.fnd_file.put_line(apps.fnd_file.log, '批次 ： ' || l_lot_no);
/*--09053101 在procedure:synchronization_batch_no中实现
--批次信息插入中间表中，方便批次状态转换
v_err := 'delete from order line from HEK_OM_ZZC_LOT';
delete from hek_om_zzc_lot t
where t.group_id = p_group_id
and t.order_line_id = rec_order_line.line_id;
v_err := 'begin insert into HEK_OM_ZZC_LOT';
insert into HEK_OM_ZZC_LOT
(group_id,
INTERFACE_TRANSACTION_ID,
ORDER_LINE_ID,
ITEM_NO,
LOT_NO,
WSHE_CODE,
LOCATOR,
QTY,
CREATED_BY,
CREATION_DATE)
values
(p_group_id,
po.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL,
rec_order_line.line_id,
rec_order_line.ordered_item,
l_lot_no,
l_subinventory,
l_locator, --'A',
rec_order_line.ordered_quantity,
g_user_id,
sysdate);*/
--库存
v_err := 'begin insert into inv.MTL_TRANSACTION_LOTS_INTERFACE';
INSERT INTO inv.MTL_TRANSACTION_LOTS_INTERFACE
(TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES
(inv.MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
g_user_id, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
g_user_id, --CREATED_BY
0, --LAST_UPDATE_LOGIN
l_lot_no, --LOT_NUMBER
rec_order_line.ordered_quantity, --3, --TRANSACTION_QUANTITY
rec_order_line.ordered_quantity, --3, --PRIMARY_QUANTITY
'RCV', --PRODUCT_CODE
po.RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --PRODUCT_TRANSACTION_ID
);
apps.fnd_file.put_line(apps.fnd_file.log, '库存交易数量 ： ' || rec_order_line.ordered_quantity);
end loop;
end loop;
commit;
apps.fnd_file.put_line(apps.fnd_file.log,
rpad('end', 15, '=') || '数据插入接收接口表');
exception
when others then
rollback;
apps.fnd_file.put_line(apps.fnd_file.log,
'ERROR in insert_po_receipt_api+' || v_err || '+' ||
sqlerrm);
raise_application_error(-20000,
'ERROR in insert_po_receipt_api+' || v_err ||
sqlerrm);
end insert_po_receipt_api;
成长
/ | \
学习 总结 分享
QQ交流群：122230156
