详解EBS接口开发之采购申请导入

更多内容可以参考我的博客



详解EBS接口开发之采购订单导入 http://blog.csdn.net/cai_xingyun/article/details/17114697

/*+++++++++++++++++++++++++++++++++++++++++++++++++++  --将数据写入至采购申请接口表  +++++++++++++++++++++++++++++++++++++++++++++++++++*/  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;

                           --多条记录M，单条记录S，全部记录A    mo_global.set_policy_context(p_access_mode => 'S', p_org_id => org_id);    fnd_request.set_org_id(org_id);    l_request_id := fnd_request.submit_request('PO',                                               'REQIMPORT',                                               '',                                               to_char(SYSDATE,                                                       'YYYY/MM/DD HH24:MI:SS'),                                               FALSE,                                               'ROI',                                               '',                                               'ALL',                                               '',                                               'N',                                               'N',                                               chr(0),                                                   COMMIT;