详解EBS接口开发之采购申请导入(转载)-优快云博客

/*+++++++++++++++++++++++++++++++++++++++++++++++++++
--将数据写入至采购申请接口表
+++++++++++++++++++++++++++++++++++++++++++++++++++*/
PROCEDURE insert_procure_main(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2) AS
l_poreq_inter_record po.po_requisitions_interface_all%ROWTYPE;
CURSOR poreq_cur IS
/**此处为游标收集数据的SQL，略***/
--索引表
TYPE l_poreq_tbl IS TABLE OF poreq_cur%ROWTYPE INDEX BY BINARY_INTEGER;
l_poreq_record l_poreq_tbl;
/*
L_TEMPLATE_ID Number;
L_LINE_ID Number; --记录上一行
L_HEADER_ID Number; --记录上一个头信息
L_COUNT Number := 0; --记录行中非本状态的行数
L_LINE_STATUS Varchar2(120);
L_HEADER_STATUS Varchar2(120);
L_IMPORTED_COUNT Number := 0; --已导入的行数
L_LINE_COUNT Number := 0;
L_ERROR_COUNT Number := 0; --导入发生错误的行数*/
BEGIN
--初始化
--RETCODE := '0';
--ERRBUF := Null;
l_poreq_record.delete;
----Body Start
OPEN poreq_cur;
--批量获取数据
FETCH poreq_cur BULK COLLECT
INTO l_poreq_record;
IF l_poreq_record.count > 0
THEN
--在输出结果时既可以使用集合的count属性和可以使用first和last：
--for i in L_POREQ_RECORD.first .. L_POREQ_RECORD.last
FOR i IN 1 .. l_poreq_record.count LOOP
--接口表的序列:PO_REQUISITIONS_INTER_ALL_s
SELECT po_requisitions_inter_all_s.nextval
INTO l_poreq_inter_record.request_id
FROM dual;
--费用账户ID
SELECT mpv.material_account
INTO l_poreq_inter_record.charge_account_id
FROM mtl_parameters_view mpv
WHERE mpv.organization_id = l_poreq_record(i).received_org_id;
--who
l_poreq_inter_record.creation_date := SYSDATE;
l_poreq_inter_record.created_by := fnd_global.user_id;
l_poreq_inter_record.last_update_login := fnd_global.login_id;
l_poreq_inter_record.last_update_date := SYSDATE;
l_poreq_inter_record.last_updated_by := fnd_global.user_id;
--purchase
l_poreq_inter_record.interface_source_code := 'ROI'; --来源代码，用来控制接口请求的运行范围
l_poreq_inter_record.source_type_code := 'VENDOR'; --来源类型。如果目标类型是EXPENSE或者SHOP FLOOR，那么必须给，一般是VENDOR
l_poreq_inter_record.requisition_type := 'PURCHASE'; --采购申请类型：默认采购申请
l_poreq_inter_record.destination_type_code := l_poreq_record(i)
.destination_type_code; --目的地类型
l_poreq_inter_record.item_description := l_poreq_record(i)
.item_description; --物料名称
l_poreq_inter_record.quantity := l_poreq_record(i)
.quantity; --数量
l_poreq_inter_record.unit_price := l_poreq_record(i)
.unit_price; --物料编码携带的价格
l_poreq_inter_record.authorization_status := 'INCOMPLETE'; --状态，未审批INCOMPLETE
l_poreq_inter_record.req_number_segment1 := l_poreq_record(i)
.application_no; --申请编号
l_poreq_inter_record.header_attribute10 := l_poreq_record(i)
.current_month; --月份
l_poreq_inter_record.header_attribute11 := l_poreq_record(i)
.equip_mai_required; --是否设备维修
l_poreq_inter_record.note_to_buyer := l_poreq_record(i)
.note_to_buyer; --通知采购员
l_poreq_inter_record.note_to_receiver := l_poreq_record(i)
.note_to_receiver; --通知接收员
l_poreq_inter_record.item_id := l_poreq_record(i)
.item_id; --物料编码ID
l_poreq_inter_record.item_segment1 := l_poreq_record(i)
.item_number; --物料编码
l_poreq_inter_record.category_id := l_poreq_record(i)
.category_id; --物料类别的ID
l_poreq_inter_record.category_segment1 := l_poreq_record(i)
.category_segment1; --物料类别
l_poreq_inter_record.unit_of_measure := l_poreq_record(i)
.unit_of_measure; --计量单位
l_poreq_inter_record.line_type := '货物'; --行类型默认为货物（物料类型）
l_poreq_inter_record.un_number_id := l_poreq_record(i)
.un_number_id; --提报部门ID
l_poreq_inter_record.un_number := l_poreq_record(i)
.un_number; --提报部门
l_poreq_inter_record.hazard_class_id := l_poreq_record(i)
.project_number_id; --工程项目号ID
l_poreq_inter_record.hazard_class := l_poreq_record(i)
.project_number; --工程项目号
l_poreq_inter_record.destination_organization_id := l_poreq_record(i)
.received_org_id; --接收组织ID
l_poreq_inter_record.deliver_to_location_id := l_poreq_record(i)
.received_place_id; --接收地点ID
l_poreq_inter_record.deliver_to_requestor_id := l_poreq_record(i)
.deliver_to_requestor_id; --申请人ID
l_poreq_inter_record.deliver_to_requestor_name := l_poreq_record(i)
.deliver_to_requestor_name; --申请人
l_poreq_inter_record.suggested_buyer_id := l_poreq_record(i)
.buyer_id; --采购员ID
l_poreq_inter_record.suggested_buyer_name := l_poreq_record(i)
.suggested_buyer_name; --采购员
l_poreq_inter_record.suggested_vendor_item_num := substrb(l_poreq_record(i)
.equip_spare_use,
1,
25); --设备备件用途
l_poreq_inter_record.line_attribute_category := '';--<span id="kM0.9812031188048422">@此处为说明性弹性域的段值</span>
l_poreq_inter_record.line_attribute2 := l_poreq_record(i)
.line_attribute2; --<span id="kM0.2401782302185893">@弹性域值（客户化）</span>
l_poreq_inter_record.line_attribute6 := l_poreq_record(i)
.line_attribute6; --维修号
l_poreq_inter_record.need_by_date := l_poreq_record(i)
.require_time; --需要时间
l_poreq_inter_record.currency_code := l_poreq_record(i)
.currency_code; --币种
l_poreq_inter_record.org_id := l_poreq_record(i).org_id; --业务实体
--L_POREQ_INTER_RECORD.CHARGE_ACCOUNT_ID := L_POREQ_INTER_REC(i).ACCOUNT_ID;
--L_POREQ_INTER_RECORD.ACCRUAL_ACCOUNT_ID := --应计帐户
--L_POREQ_INTER_RECORD.VARIANCE_ACCOUNT_ID := --差异帐户
INSERT INTO po.po_requisitions_interface_all
VALUES l_poreq_inter_record;
/*******此处需要增加反写记录设置，即是标记哪些数据已经被创建过申请等*********/
COMMIT;
END LOOP;
CLOSE poreq_cur;
retcode := '0';
errbuf := '导入成功' || l_poreq_record.count || '条采购申请。';
END IF;
/* --勾选的记录的数量与成功导入的记录数的关系
If L_POREQ_RECORD.COUNT*/
END;