procedure check_interface(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) is
cursor c1 is
select ji.rowid row_id,ji.*
from jw_po_imes_erp_interface ji
where ji.flag = 'Y';
v_num1 number;
v_num2 number;
begin
DBMS_LOCK.SLEEP(5); --为防止程序成功结束，但是interface暂未删除导致
for v1 in c1 loop
begin
select count(*)
into v_num1
from rcv_transactions_interface rti
where rti.interface_source_code = 'IMES_TO_ERP'
AND rti.interface_source_line_id = v1.interface_id;
select count(*)
into v_num2
from rcv_transactions rt
where rt.attribute15 = v1.interface_id;
if (v_num1 = 0) and (v_num2 = 2) then--标准接口表数据清掉了，同时标准表也有数据了代表成功
update jw_po_imes_erp_interface j
set j.flag = 'S' --更新成s代表 已经接收成功
where j.rowid = v1.row_id;
commit;
end if;
exception
when others then
null;
end;
end loop;
insert into jw_po_imes_erp_interface_his----成功的数据备份插入临时表
select * from jw_po_imes_erp_interface where flag = 'S';
commit;
delete from jw_po_imes_erp_interface jp
where jp.flag = 'S'
and exists (select 1
from jw_po_imes_erp_interface ji
where ji.interface_id = jp.interface_id);
commit;
null;
end;
procedure MAIN_NEW(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) is
/*create by jam huang 20160623
因为采购接收方式由原来的标准接收改为直接入库，so 之前的代码不能用了*/
cursor c_select is
select j.rowid row_id,
ood.ORGANIZATION_ID,
pla.item_id,
trim(j.item_number) item_number,
J.QUANTITY * decode(j.uom_code,'KPS',1000,'KPCS',1000,1) PRIMARY_QUANTITY,
pha.po_header_id,
pla.po_line_id,
pha.Authorization_Status
from jw_po_imes_erp_interface j,
org_organization_definitions ood,
po_headers_all pha,
po_lines_all pla
where trim(j.po_number) = pha.segment1
and pha.po_header_id = pla.po_header_id
and pla.line_num = j.po_line_number
and pha.org_id = ood.OPERATING_UNIT
and j.flag is null;
cursor c_header is
select distinct pha.vendor_id,pha.vendor_site_id,pha.org_id,jp.comments
from jw_po_imes_erp_interface jp
,po_headers_all pha
where jp.po_number = pha.segment1
and jp.flag = 'C';
cursor c_line(p_vendor_id number,p_vendor_site_id number,p_org_id number,p_comment varchar2) is
select j.rowid row_id,
pha.po_header_id,
pla.po_line_id,
PLA.ITEM_ID,
pha.vendor_id,
pha.vendor_site_id,
OOD.ORGANIZATION_ID,
j.interface_id,--接口表的id，记录下后面好查找
J.SUBINVENTORY,
j.quantity,--数量
j.uom_code,--单位
j.user_name,--imes操作的人
j.delivery_number,--交货项次
j.delivery_no, --交货单号
j.delivery_po --客户po单号
from jw_po_imes_erp_interface j,
po_headers_all pha,
po_lines_all pla,
org_organization_definitions ood,
mtl_system_items_b msib
where trim(j.po_number) = pha.segment1
and pha.po_header_id = pla.po_header_id
and pla.line_num = j.po_line_number
and trim(j.item_number) = msib.segment1
and msib.inventory_item_id = pla.item_id
and ood.OPERATING_UNIT = pla.org_id
and msib.organization_id = ood.ORGANIZATION_ID
and pha.vendor_id = p_vendor_id
and pha.vendor_site_id = p_vendor_site_id
and pha.org_id = p_org_id
and j.flag = 'C';
V_NUM NUMBER;
V_NUM1 NUMBER;
tl_rcv_transactions_interface rcv_transactions_interface%rowtype;
tl_rcv_headers_interface rcv_headers_interface%rowtype;--header
ln_request_id NUMBER;
ln_acct_period_id NUMBER;
V_ERROR_MESSAGE VARCHAR2(100);
V_PO_LINE_ID NUMBER;
V_UNRCV_QUANTITY NUMBER;
lb_success BOOLEAN DEFAULT TRUE;
lv_phase VARCHAR2(100);
lv_status VARCHAR2(100);
lv_dev_phase VARCHAR2(100);
lv_dev_status VARCHAR2(100);
lv_message VARCHAR2(2000);
L_ERROR_MESSAGE VARCHAR2(2000);
L_STATUS NUMBER;
r_j exception;
begin
SELECT COUNT(*)
INTO V_NUM
FROM jw_po_imes_erp_interface JP
WHERE JP.FLAG IS NULL;
IF V_NUM = 0 THEN --没有新数据就退出
RETURN;
END IF;
------------防呆
for v_select in c_select loop
begin
select count(*)-------防呆 gl and po期间
into v_num1
from GL_PERIOD_STATUSES grs
where grs.set_of_books_id = 2021
and grs.closing_status = 'O'
AND grs.application_id in (101,201)
and trunc(SYSDATE) BETWEEN grs.start_date AND
grs.end_date;
if v_num1 <> 2 then
V_ERROR_MESSAGE := 'GL OR PO期间没有打开';
raise r_j;
end if;
BEGIN-------防呆，组织期间
SELECT oap.acct_period_id
INTO ln_acct_period_id
FROM org_acct_periods oap
WHERE oap.organization_id = v_select.ORGANIZATION_ID
AND oap.open_flag = 'Y'
AND trunc(SYSDATE) BETWEEN oap.period_start_date AND
oap.schedule_close_date;
EXCEPTION
WHEN no_data_found THEN
V_ERROR_MESSAGE := '组织期间没有打开';
raise r_j;
END;
IF v_select.Authorization_Status <> 'APPROVED' THEN
V_ERROR_MESSAGE := '采购订单未审批';
raise r_j;
END IF;
select count(*)
into v_num1
from mtl_system_items_b msib
where msib.segment1 = v_select.item_number
and msib.inventory_item_id = v_select.item_id
and msib.organization_id = v_select.organization_id;
if v_num1 = 0 then
V_ERROR_MESSAGE := '采购订单行与料号对应不一致';
raise r_j;
end if;
select plla.po_line_id,
SUM((plla.quantity - plla.quantity_received) *
decode(plla.unit_meas_lookup_code, 'KPCS', 1000, 1))
INTO V_PO_LINE_ID, V_UNRCV_QUANTITY
from po_line_locations_all plla
where plla.po_header_id = v_select.po_header_id
and plla.po_line_id = v_select.po_line_id
GROUP BY plla.po_line_id;
IF v_select.Primary_Quantity > V_UNRCV_QUANTITY THEN
V_ERROR_MESSAGE := '数量大于未接收的数量';
raise r_j;
END IF;
update jw_po_imes_erp_interface jp
set jp.flag = 'C'
WHERE JP.ROWID = v_select.Row_Id;
COMMIT;
exception
when r_j then
update jw_po_imes_erp_interface jp
set jp.flag = 'E',
JP.ERROR_MESSAGE = V_ERROR_MESSAGE
WHERE JP.ROWID = v_select.Row_Id;
COMMIT;
end;
end loop;
------------防呆
for v_header in c_header loop
begin
select po.rcv_headers_interface_s.NEXTVAL
into tl_rcv_headers_interface.header_interface_id
from dual;
select po.rcv_interface_groups_s.NEXTVAL
into tl_rcv_headers_interface.group_id
from dual;
select ood.ORGANIZATION_Id
into tl_rcv_headers_interface.Ship_To_Organization_Id
from org_organization_definitions ood
where ood.OPERATING_UNIT = v_header.org_id;
tl_rcv_headers_interface.processing_status_code := 'PENDING';
tl_rcv_headers_interface.receipt_source_code := 'VENDOR';
tl_rcv_headers_interface.transaction_type := 'NEW';
tl_rcv_headers_interface.expected_receipt_date := sysdate;
tl_rcv_headers_interface.validation_flag := 'Y';
tl_rcv_headers_interface.vendor_id := v_header.vendor_id;
tl_rcv_headers_interface.vendor_site_id := v_header.vendor_site_id;
tl_rcv_headers_interface.CREATION_DATE := SYSDATE;
tl_rcv_headers_interface.CREATED_BY := fnd_profile.VALUE('user_id');
tl_rcv_headers_interface.LAST_UPDATE_DATE := SYSDATE;
tl_rcv_headers_interface.LAST_UPDATED_BY := fnd_profile.VALUE('user_id');
tl_rcv_headers_interface.LAST_UPDATE_LOGIN := NULL;
tl_rcv_headers_interface.comments := v_header.comments;
INSERT INTO rcv_headers_interface VALUES tl_rcv_headers_interface;
for v_line in c_line(v_header.vendor_id,v_header.vendor_site_id,v_header.org_id,v_header.comments) loop
begin
select rcv_transactions_interface_s.nextval
into tl_rcv_transactions_interface.interface_transaction_id
from dual;
tl_rcv_transactions_interface.subinventory := v_line.SUBINVENTORY;
tl_rcv_transactions_interface.quantity := v_line.QUANTITY;
tl_rcv_transactions_interface.group_id := tl_rcv_headers_interface.group_id;
tl_rcv_transactions_interface.transaction_type := 'RECEIVE';--'RECEIVE';
tl_rcv_transactions_interface.transaction_date := SYSDATE;
tl_rcv_transactions_interface.processing_status_code := 'PENDING';
tl_rcv_transactions_interface.processing_mode_code := 'BATCH';
tl_rcv_transactions_interface.transaction_status_code := 'PENDING';
-- tl_rcv_transactions_interface.category_id := 1582;
--tl_rcv_transactions_interface.unit_of_measure := 'PCS';
--tl_rcv_transactions_interface.primary_unit_of_measure := 'PCS';
-- tl_rcv_transactions_interface.interface_source_code := 'RCV';
select decode(v_line.uom_code,'KPCS','KPS','KPS','KPS','PCS')
into tl_rcv_transactions_interface.uom_code
from dual;
tl_rcv_transactions_interface.validation_flag := 'Y';
tl_rcv_transactions_interface.item_id := v_line.ITEM_ID;
-- tl_rcv_transactions_interface.item_description := 'Switch IC,RT9711CGB,2.5～5.5V,80mΩ, 1.5A/0.6A High-Side Power Switches with Flag,SOT23-5,0D95_3X3X1,-40～+85,SMT,LF,Richtek';
--tl_rcv_transactions_interface.uom_code := 'PCS';
tl_rcv_transactions_interface.auto_transact_code := 'DELIVER';
tl_rcv_transactions_interface.receipt_source_code := 'VENDOR';
tl_rcv_transactions_interface.to_organization_id := v_line.ORGANIZATION_ID;
-- tl_rcv_transactions_interface.routing_header_id := 3;
--tl_rcv_transactions_interface.routing_step_id := 1;
tl_rcv_transactions_interface.source_document_code := 'PO';
tl_rcv_transactions_interface.destination_type_code := 'INVENTORY';
/* select DECODE(SUBSTR(tl_rcv_transactions_interface.subinventory,
2,
1),
'2',
143,
'1',
142,
143)
into tl_rcv_transactions_interface.location_id
from dual;*/
tl_rcv_transactions_interface.location_id := 142;
tl_rcv_transactions_interface.deliver_to_location_id := tl_rcv_transactions_interface.location_id;
-- tl_rcv_transactions_interface.subinventory := tl_rcv_transactions_interface.subinventory;
tl_rcv_transactions_interface.expected_receipt_date := SYSDATE;
tl_rcv_transactions_interface.destination_context := 'INVENTORY';
tl_rcv_transactions_interface.vendor_id := v_header.vendor_id;
tl_rcv_transactions_interface.vendor_site_id := v_header.Vendor_Site_Id;
tl_rcv_transactions_interface.po_header_id := v_line.po_header_id;
tl_rcv_transactions_interface.po_line_id := v_line.po_line_id;
-- tl_rcv_transactions_interface.po_line_location_id := 103689;
-- tl_rcv_transactions_interface.po_distribution_id := 103322;
--- tl_rcv_transactions_interface.oe_order_header_id := v_line.header_id;
-- tl_rcv_transactions_interface.oe_order_line_id := v_line.order_line_id;
---- tl_rcv_transactions_interface.customer_id := tl_rcv_headers_interface.customer_id;
tl_rcv_transactions_interface.header_interface_id := tl_rcv_headers_interface.header_interface_id;
tl_rcv_transactions_interface.last_update_date := SYSDATE;
tl_rcv_transactions_interface.last_updated_by := fnd_profile.VALUE('user_id');--fnd_global.user_id;
tl_rcv_transactions_interface.creation_date := SYSDATE;
tl_rcv_transactions_interface.created_by := fnd_profile.VALUE('user_id');--fnd_global.user_id;
tl_rcv_transactions_interface.last_update_login := NULL;--fnd_global.login_id;
tl_rcv_transactions_interface.interface_source_code := 'IMES_TO_ERP';
tl_rcv_transactions_interface.interface_source_line_id := v_line.INTERFACE_ID;
tl_rcv_transactions_interface.attribute1 := v_line.DELIVERY_NUMBER;
tl_rcv_transactions_interface.attribute4 := v_line.DELIVERY_NO;
tl_rcv_transactions_interface.attribute2 := v_line.DELIVERY_PO;
tl_rcv_transactions_interface.attribute15 := v_line.INTERFACE_ID;------为了后面好检查是否接收成功
insert into rcv_transactions_interface values tl_rcv_transactions_interface;
COMMIT;
UPDATE JW_PO_IMES_ERP_INTERFACE JP
SET JP.FLAG = 'Y'
WHERE JP.ROWID = v_line.ROW_ID;
COMMIT;
null;
exception
when OTHERS then
UPDATE JW_PO_IMES_ERP_INTERFACE JP
SET JP.FLAG = 'F'
WHERE JP.ROWID = v_line.ROW_ID;
COMMIT;
end;
end loop;
null;
exception
when others then
null;
end;
end loop;
------------提交系统标准的请求
ln_request_id := fnd_request.submit_request(application => 'PO'
,program => 'RVCTP'
,sub_request => FALSE
,argument1 => 'BATCH'--v_split.OPERATING_UNIT
,argument2 => null--tl_rcv_headers_interface.group_id--v_split.order_source_id
,argument3 => NULL--v_split.orig_sys_document_ref
,argument4 => ''
,argument5 => ''
,argument6 => ''
,argument7 => ''
,argument8 => ''
,argument9 => ''
,argument10 => ''
,argument11 => ''
,argument12 => ''
,argument13 => ''
,argument14 => ''
,argument15 => '');
commit;
if ln_request_id <> 0 then
lb_success := fnd_concurrent.wait_for_request(request_id => ln_request_id
,INTERVAL => 5
,phase => lv_phase
,status => lv_status
,dev_phase => lv_dev_phase
,dev_status => lv_dev_status
,message => lv_message);
if lb_success AND lv_dev_status = 'NORMAL' THEN
check_interface(L_ERROR_MESSAGE, L_STATUS);
end if;
end if;
null;
end;