PO_创建采购申请

       API po_create_requisition_sv.process_requisition 可以创建采购申请，但是如果采购申请关联到项目、任务字段。还是需要将数据插入接口表，然后运行“导入申请”请求，生成采购申请。具体代码如下图所示：

DECLARE

    CURSOR cur_errors(c_batch_id IN NUMBER) IS
        SELECT DISTINCT pie.error_message
        FROM   po.po_requisitions_interface_all pr
              ,po_interface_errors              pie
        WHERE  pie.interface_type = 'REQIMPORT'
        AND    pr.transaction_id = pie.interface_transaction_id
        AND    pr.batch_id = c_batch_id;

    l_request_id NUMBER;
    l_bl_result  BOOLEAN;
    l_phase      VARCHAR2(30);
    l_dev_status VARCHAR2(30);
    l_status     VARCHAR2(20);
    l_dev_phase  VARCHAR2(200);
    l_message    VARCHAR2(2000);

    x_return_status VARCHAR2(1);
    x_msg_data      VARCHAR2(240);

    l_requisition_header_id NUMBER;
    l_req_rec               po.po_requisitions_interface_all%ROWTYPE;
    l_req_dist_rec          po.po_req_dist_interface_all%ROWTYPE;
BEGIN
    fnd_global.apps_initialize(user_id      => 0
                              ,resp_id      => 51427
                              ,resp_appl_id => 201);
    mo_global.set_policy_context('S'
                                ,106);
    --who
    l_req_rec.creation_date     := SYSDATE;
    l_req_rec.created_by        := fnd_global.user_id;
    l_req_rec.last_update_login := fnd_global.login_id;
    l_req_rec.last_update_date  := SYSDATE;
    l_req_rec.last_updated_by   := fnd_global.user_id;

    --来源字段 
    l_req_rec.interface_source_code    := 'PSM';
    l_req_rec.interface_source_line_id := '10001';
    l_req_rec.autosource_flag          := 'P';
    l_req_rec.batch_id                 := po_bulkload_batch_s.nextval;
    dbms_output.put_line('batch_id :=' || l_req_rec.batch_id);

    --采购申请头字段
    l_req_rec.org_id               := 106; --业务实体
    l_req_rec.requisition_type     := 'PURCHASE'; --采购申请类型
    l_req_rec.req_number_segment1  := '20201229_02';
    l_req_rec.authorization_status := 'INCOMPLETE'; --状态 
    l_req_rec.preparer_id          := 88; --编制人
    l_req_rec.header_description   := 'Purchase Order Requistion Demo Import';

    --采购申请行字段
    l_req_rec.line_num                    := 1;
    l_req_rec.line_type_id                := 1; --类型：1.货物
    l_req_rec.item_id                     := 5001; --物料编码ID
    l_req_rec.category_id                 := 5123; --物料类别ID
    l_req_rec.currency_code               := 'CNY'; --币种
    l_req_rec.unit_of_measure             := '件'; --单位
    l_req_rec.quantity                    := 100; --数量
    l_req_rec.unit_price                  := 1; --单价
    l_req_rec.need_by_date                := trunc(SYSDATE); --需求日期
    l_req_rec.rate_date                   := trunc(SYSDATE);
    l_req_rec.destination_type_code       := 'INVENTORY'; --目的地类型
    l_req_rec.source_type_code            := 'VENDOR'; --来源类型 
    l_req_rec.deliver_to_requestor_id     := 88; --申请人ID
    l_req_rec.destination_organization_id := 305; --接收组织ID
    l_req_rec.deliver_to_location_id      := 142; --接收地点ID
    l_req_rec.suggested_vendor_id         := NULL; --供应商
    l_req_rec.suggested_vendor_site_id    := NULL; --供应商地点

    --借记账户 
    SELECT mpv.material_account
    INTO   l_req_rec.charge_account_id
    FROM   mtl_parameters_view mpv
    WHERE  mpv.organization_id = l_req_rec.destination_organization_id;

    --分配行信息 
    l_req_rec.req_dist_sequence_id := '1';
    l_req_rec.project_id           := NULL;
    l_req_rec.task_id              := NULL;
    
    --有project_id ，project_accounting_context 需要有值
	IF l_req_rec.project_id IS NOT NULL THEN
		l_req_rec.project_accounting_context := 'Y';
	ELSE
		l_req_rec.project_accounting_context := 'N';
	END IF;

    --如果目的地类型是费用，支出类型、支出组织、支出时间 要求必输
    l_req_rec.expenditure_type            := NULL;
    l_req_rec.expenditure_organization_id := NULL;
    l_req_rec.expenditure_item_date       := NULL;
    l_req_rec.multi_distributions         := 'N';

    INSERT INTO po.po_requisitions_interface_all
    VALUES l_req_rec;

    --如果是单个分配， po_req_dist_interface_all 不需要，11i之后这个表不用

    --submit request
    fnd_request.set_org_id(l_req_rec.org_id);

    l_request_id := fnd_request.submit_request(application => 'PO'
                                              ,program     => 'REQIMPORT'
                                              ,sub_request => FALSE
                                              ,argument1   => 'PSM' --Import Source 
                                              ,argument2   => to_char(l_req_rec.batch_id) --Import Batch ID
                                              ,argument3   => 'BUYER' --Group By
                                              ,argument4   => l_req_rec.req_number_segment1 --Last Requisition Number
                                              ,argument5   => 'N' -- Multiple Distributions
                                              ,argument6   => 'N'); -- Initiate Approval after ReqImport


    IF l_request_id > 0 THEN
        dbms_output.put_line('l_request_id :=' || l_request_id);
        COMMIT;
        l_bl_result := fnd_concurrent.wait_for_request(request_id => l_request_id
                                                      ,INTERVAL   => 5
                                                      ,phase      => l_phase
                                                      ,status     => l_status
                                                      ,dev_phase  => l_dev_phase
                                                      ,dev_status => l_dev_status
                                                      ,message    => l_message);
        IF l_bl_result THEN
            IF l_dev_status = 'NORMAL' THEN
            
                BEGIN
                    SELECT t.requisition_header_id
                    INTO   l_requisition_header_id
                    FROM   po.po_requisition_headers_all t
                    WHERE  t.segment1 = l_req_rec.req_number_segment1
                    AND    t.org_id = l_req_rec.org_id;
                EXCEPTION
                    WHEN OTHERS THEN
                        l_requisition_header_id := NULL;
                END;
            
                IF l_requisition_header_id IS NULL THEN
                    x_return_status := fnd_api.g_ret_sts_error;
                    FOR rec_error IN cur_errors(c_batch_id => l_req_rec.batch_id) LOOP
                        dbms_output.put_line('error message :=' || rec_error.error_message);
                    END LOOP;
                END IF;
            ELSE
                x_return_status := fnd_api.g_ret_sts_error;
                x_msg_data      := '请求:' || l_request_id || ' 出现错误，错误信息：' || l_message;
            END IF;
        ELSE
            x_return_status := fnd_api.g_ret_sts_error;
            x_msg_data      := '请求:' || l_request_id || ' 出现错误';
        END IF;
    ELSE
        x_return_status := fnd_api.g_ret_sts_error;
        x_msg_data      := '提交"导入申请"请求失败！';
    END IF;

    --清除接口表错误数据 
    IF x_return_status = fnd_api.g_ret_sts_error THEN
        DELETE po.po_requisitions_interface_all pr
        WHERE  pr.batch_id = l_req_rec.batch_id;
        COMMIT;
    END IF;

    dbms_output.put_line('x_return_status :=' || x_return_status);
    dbms_output.put_line('x_msg_data :=' || x_msg_data);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        dbms_output.put_line('SQLERRM : ' || SQLERRM);
END;