EBS 采用API 创建(标准/计划)订单(根据请购订单生成的PO)

1、该API是官方内部使用未放开的API，只需要把数据写入到 po_headers_interface，po_lines_interface 接口中

2、由于没有把数据写入到：po_distributions_interface 接口因此最好创建 分配行的触发器(获取CCID)的校验

CREATE OR REPLACE TRIGGER CUX_PO_DISTRIBUTIONS_TRG
  BEFORE INSERT ON "PO"."PO_DISTRIBUTIONS_ALL#"
  FOR EACH ROW
DECLARE
  L_CHART_OF_ACCOUNTS_ID     NUMBER;
  L_SUPPLIER_SITES_CCID      NUMBER; --供应商地址CCID
  L_SUPPLIER_SITES_SEGMENTS  VARCHAR2(240); --供应商地址账户信息
  L_DIS_ACCOUNT_ID           NUMBER; ---采购分配行CCID
  L_DIS_ACCOUNT_SEGMENTS     VARCHAR2(240); ---采购分配行账户信息
  X_ACCRUAL_ACCOUNT_SEGMENTS VARCHAR2(240); ---替换后的新账户信息
  X_ACCRUAL_ACCOUNT_ID       NUMBER; --输出CCID

BEGIN
    SELECT DISTINCT OOD.CHART_OF_ACCOUNTS_ID
    INTO L_CHART_OF_ACCOUNTS_ID
    FROM ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE OOD.OPERATING_UNIT = :NEW.ORG_ID;
  ---查找采购地址CCID
  BEGIN
    SELECT ASSA.ACCTS_PAY_CODE_COMBINATION_ID
      INTO L_SUPPLIER_SITES_CCID
      FROM PO_HEADERS_ALL PHA, PO_VENDOR_SITES_ALL ASSA
     WHERE 1 = 1
       AND PHA.VENDOR_ID = ASSA.VENDOR_ID
       AND PHA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
       AND PHA.PO_HEADER_ID = :NEW.PO_HEADER_ID;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001, '获取采购地址CCID失败: ' || SQLERRM);
  END;
 
  ----采购地址账户信息
  BEGIN
    L_SUPPLIER_SITES_SEGMENTS := FND_FLEX_EXT.GET_SEGS('SQLGL',
                                                       'GL#',
                                                       L_CHART_OF_ACCOUNTS_ID,
                                                       L_SUPPLIER_SITES_CCID);
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20002,
                              '获取采购地址账户信息失败: ' || SQLERRM);
  END;

  L_DIS_ACCOUNT_ID := :NEW.ACCRUAL_ACCOUNT_ID;
  IF L_DIS_ACCOUNT_ID > 0 THEN
    ---查找分配行账户信息
    BEGIN
      L_DIS_ACCOUNT_SEGMENTS := FND_FLEX_EXT.GET_SEGS('SQLGL',
                                                      'GL#',
                                                      L_CHART_OF_ACCOUNTS_ID,
                                                      L_DIS_ACCOUNT_ID);
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003,
                                '获取分配行账户信息失败: ' || SQLERRM);
    END;
    ------将分配行账户信息‘内部往来段’替换成 供应商地址账户的‘内部往来段’
    BEGIN
      X_ACCRUAL_ACCOUNT_SEGMENTS := SUBSTR(L_DIS_ACCOUNT_SEGMENTS,
                                           1,
                                           INSTR(L_DIS_ACCOUNT_SEGMENTS,
                                                 '.',
                                                 1,
                                                 6)) ||
                                    SUBSTR(L_SUPPLIER_SITES_SEGMENTS,
                                           INSTR(L_SUPPLIER_SITES_SEGMENTS,
                                                 '.',
                                                 1,
                                                 6) + 1,
                                           INSTR(L_SUPPLIER_SITES_SEGMENTS,
                                                 '.',
                                                 1,
                                                 7) - INSTR(L_SUPPLIER_SITES_SEGMENTS,
                                                            '.',
                                                            1,
                                                            6) - 1) ||
                                    SUBSTR(L_DIS_ACCOUNT_SEGMENTS,
                                           INSTR(L_DIS_ACCOUNT_SEGMENTS,
                                                 '.',
                                                 -1,
                                                 2));
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20004,
                                '获取修改后的账户信息失败: ' || SQLERRM);
    END;
  
    BEGIN
      ---获取更新后的CCID
      X_ACCRUAL_ACCOUNT_ID := FND_FLEX_EXT.GET_CCID('SQLGL',
                                                    'GL#',
                                                    L_CHART_OF_ACCOUNTS_ID,
                                                    FND_DATE.DATE_TO_CANONICAL(SYSDATE),
                                                    --TO_DATE('2015-12-03','yyyy-mm-dd'),
                                                    X_ACCRUAL_ACCOUNT_SEGMENTS);
       IF X_ACCRUAL_ACCOUNT_ID <= 0 THEN
         ---ADD BY IVEN.LIN 2021-10-26
         RAISE_APPLICATION_ERROR(-20005,
                                '获取修改后的CCID失败: ' || X_ACCRUAL_ACCOUNT_SEGMENTS||
                                ':NEW.ORG_ID'||:NEW.ORG_ID ||':NEW.PO_HEADER_ID:'||:NEW.PO_HEADER_ID);
       END IF; 
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20005,
                                '获取修改后的CCID失败: ' || SQLERRM);
    END;
   :NEW.ACCRUAL_ACCOUNT_ID :=  X_ACCRUAL_ACCOUNT_ID;
  -- :NEW.ATTRIBUTE15 := X_ACCRUAL_ACCOUNT_SEGMENTS;
  ELSE
    RAISE_APPLICATION_ERROR(-20006, '获CCID失败: ' || SQLERRM);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20009,
                            '更新分配行PO应计帐户失败: ' || SQLERRM);
END;

 

3、生成PO编码的方法

function get_po_number(p_org_id             number,
                       p_type               varchar2,
                       p_organization_id    number,
                       p_subinventory       varchar2,
                       p_pr_organization_id number,
                       p_venodr_id          number,
                       p_attribute2         varchar2) return varchar2 is
  v_po_number   varchar2(100);
  v_po_number_t varchar2(100);
  --采购for update 方式生成唯一不重复的PO单号
  cursor cur_po_number is
    select p.current_max_unique_identifier
      from po_unique_identifier_cont_all p
     where p.table_name = p_type
       and p.org_id = p_org_id
       for update;
  v_data_rec cur_po_number%rowtype;
  v_type       varchar2(20);
  nv_po_number varchar2(100);
  v_attribute2 varchar2(50); 
begin
  open cur_po_number;
  fetch cur_po_number
    into v_data_rec;
  if cur_po_number%notfound then
    close cur_po_number;
    return null;
  end if;
  v_po_number   := v_data_rec.current_max_unique_identifier + 1;
  v_po_number_t := v_po_number;
  nv_po_number  := v_po_number;
  v_attribute2  := p_attribute2;
  update po_unique_identifier_cont_all p
     set p.current_max_unique_identifier = v_po_number,
         p.last_update_date              = sysdate,
         p.last_updated_by               = g_user_id,
         p.creation_date                 = sysdate,
         p.created_by                    = g_user_id,
         p.last_update_login             = g_login_id
   where p.table_name = p_type
     and p.org_id = p_org_id;
  close cur_po_number;

  if p_subinventory = '102165' then
    --风险策备仓 102165
    v_po_number := 'CB' || v_po_number;
  elsif p_subinventory = '101101' and v_attribute2 = 'WM采购' then
    v_po_number := 'WM' || v_po_number;
  else
    if p_organization_id = 123 then
      v_po_number := 'XQ' || v_po_number;
    elsif p_organization_id = 124 then
      v_po_number := 'TC' || v_po_number;
    elsif p_organization_id = 542 then
      v_po_number := 'TX' || v_po_number;
    elsif p_organization_id = 442 
     then
      v_po_number := 'VN' || v_po_number;
    elsif p_organization_id = 621 then
      v_po_number := 'SG' || v_po_number;
      -- modiby by iven.lin 2023-12-12  
    else
      if p_org_id = 363 then
        v_po_number := 'WY' || v_po_number;
      elsif p_org_id = 601 then
        v_po_number := 'HN' || v_po_number;
      elsif p_org_id = 721 then
        v_po_number := 'QC' || v_po_number;
      else
        v_po_number := v_po_number;
      end if;
    end if;
  
    --- iven.lin 2021-08-30
    if p_org_id = 441 and p_pr_organization_id = 442 then
      v_po_number := v_po_number_t;
    elsif p_org_id = 101 and p_pr_organization_id = 442 then
      v_po_number := 'VN' || v_po_number_t;
    elsif p_org_id = 101 and p_pr_organization_id = 722 then
      v_po_number := 'QC' || v_po_number_t; 
    end if;
    if p_org_id in (441, 709) and p_venodr_id in (5014, 1120) then
      v_po_number := 'NB' || nv_po_number;
    end if;
  end if;
  return v_po_number;
exception
  when others then
    return null;
end;

 

4、关闭PR

  /* ===============================================================
  *   procedure
  *   name :    final_close_pr
  *
  *   description: 关闭请购单，在创建请购单成功后，再关闭请购单
  *   argument:  p_req_header_id     请购单头id
                 p_po_type           请购单行id
                 x_return_status    返回状态码
                 x_return_mesg      返回信息
  *
  *
  *   history:
  *      1.00   2016-1-10   iven.lin   creation
  * ===============================================================*/
  procedure final_close_pr(p_req_header_id in number,
                           p_req_line_id   in number,
                           x_return_status out varchar2,
                           x_return_mesg   out varchar2) is
    v_api_name             varchar2(20) := 'final_close_pr';
    x_req_control_error_rc varchar2(1);
  begin
    x_return_status := fnd_api.g_ret_sts_success;
    x_return_mesg   := null;
    po_reqs_control_sv.update_reqs_status(p_req_header_id,
                                          p_req_line_id,
                                          '',
                                          'REQUISITION',
                                          'PURCHASE',
                                          'FINALLY CLOSE',
                                          '',
                                          to_date(sysdate, 'DD-MON-YYYY'),
                                          'N',
                                          'Y',
                                          x_req_control_error_rc --y/n
                                          );
    commit;
    if x_req_control_error_rc = 'Y' then
      x_return_mesg := '关闭请购单出错，请手工关闭';
      raise fnd_api.g_exc_error;
    end if;
  exception
    when fnd_api.g_exc_error then
      x_return_status := fnd_api.g_ret_sts_error;
      x_return_mesg   := g_pkg_name || '.' || v_api_name || x_return_mesg;
    when others then
      x_return_status := fnd_api.g_ret_sts_unexp_error;
      x_return_mesg   := g_pkg_name || '.' || v_api_name ||
                         '其他错误，请联系系统管理员:' || sqlerrm;
  end;

 

4、非开放接口创建标准订单：

declare

  v_po_headers_all_tbl   po.po_headers_interface%rowtype;
  v_po_lines_all_tbl     po_lines_interface%rowtype;
  v_po_distributions_tbl po_distributions_interface%rowtype;

  --固定参数
  v_process_code po.po_headers_interface.process_code%type := 'PENDING';
  --ORIGINAL 意义是生成新的PO即NEW PO
  v_action                po.po_headers_interface.action%type := 'ORIGINAL';
  v_approval_status       po.po_headers_interface.approval_status%type := 'APPROVED';
  v_interface_source_code po.po_headers_interface.interface_source_code%type := 'HK:SUPPLY';

  g_pkg_name constant varchar2(30) := 'cux_po_create_stand_plan_pkg';

  g_user_id number := fnd_global.user_id;

  g_login_id number := fnd_global.login_id;

  g_employee_id number := replace(fnd_global.employee_id, '-1', 61);

  --业务实体
  v_org_id number := 82;

begin
  x_return_status := fnd_api.g_ret_sts_success;
  x_return_mesg   := null;

  --订单头
  --赋值
  select po_headers_interface_s.nextval
    into v_po_headers_all_tbl.interface_header_id
    from dual;
  -- set who
  v_po_headers_all_tbl.creation_date     := sysdate;
  v_po_headers_all_tbl.created_by        := g_user_id;
  v_po_headers_all_tbl.last_update_date  := sysdate;
  v_po_headers_all_tbl.last_updated_by   := g_user_id;
  v_po_headers_all_tbl.last_update_login := g_login_id;
  --业务实体
  v_po_headers_all_tbl.org_id := v_org_id;

  --通知供应商栏位
  v_po_headers_all_tbl.comments := v_comments;
  --供应商
  v_po_headers_all_tbl.vendor_id := rec_header.vendor_id;
  --供应商地点
  v_po_headers_all_tbl.vendor_site_id := rec_header.vendor_site_id;

  --收单地点
  --收货地点
  begin
    select psv.ship_to_location_id
      into x_ship_to_id
      from hr_locations_v psv
     where psv.inventory_organization_id = p_inv_org_id --收货业务实体
       and psv.bill_to_site_flag = 'Y'
       and psv.ship_to_site_flag = 'Y'
       and nvl(psv.attribute1, 'XXX') <> 'N'; --收单地点生效标记(弹性域：附加的地点详细资料)
  exception
    when others then
      x_return_mesg := '找不到收货组织：' || sqlerrm;
      raise fnd_api.g_exc_error;
  end;
  --假如 v_org_id =82 是集成业务实体
  if v_org_id = 82 then
    v_po_headers_all_tbl.bill_to_location_id := rec_header.bill_to_location_id;
    v_po_headers_all_tbl.ship_to_location_id := rec_header.ship_to_location_id;
  else
    --非集成的收货地点和收单地点一样
    v_po_headers_all_tbl.bill_to_location_id := x_ship_to_id;
    v_po_headers_all_tbl.ship_to_location_id := x_ship_to_id;
  end if;

  --1、采购员
  --2、币别
  v_po_headers_all_tbl.agent_id      := g_employee_id;
  v_po_headers_all_tbl.currency_code := rec_header.currency_code;

  --1、快码分类
  --2、数据来源
  --3、batch_id
  v_po_headers_all_tbl.attribute_category    := 'STANDARD';
  v_po_headers_all_tbl.interface_source_code := v_interface_source_code;
  v_batch_id                                 := v_po_headers_all_tbl.interface_header_id;

  v_po_headers_all_tbl.batch_id := v_batch_id;
  v_po_headers_all_tbl.style_id := 1;

  --根据规则生成的PO编码
  v_document_number                 := get_po_number(v_org_id,
                                                     'PO_HEADERS',
                                                     rec_header.attribute5,
                                                     rec_header.t_subinventory,
                                                     rec_header.des_org_id,
                                                     rec_header.vendor_id,
                                                     rec_header.attribute2);
  v_po_headers_all_tbl.document_num := v_document_number;

  --1、写入接口状态
  --2、创建新的PO的
  --3、创建订单类型是标准订单
  --4、状态是审批状态,实际上还是需要人工审批的
  v_po_headers_all_tbl.process_code       := 'PENDING';
  v_po_headers_all_tbl.action             := 'ORIGINAL';
  v_po_headers_all_tbl.document_type_code := 'STANDARD';
  v_po_headers_all_tbl.approval_status    := 'APPROVED';

  --写入到接口头表
  insert into po.po_headers_interface values v_po_headers_all_tbl;

  --订单行
  -- 含税价,属于企业客开的栏位,EBS标准程界面没有的

  v_market_price := nvl(rec_line.market_price,
                        get_market_price(rec_header.vendor_id,
                                         rec_header.vendor_site_id,
                                         rec_line.unit_price));
  --每次赋值都必须清空行接口内容
  v_po_lines_all_tbl := null;
  v_line_num         := v_line_num + 1;
  --该栏位必须跟接口头内容header_id 一致
  v_po_lines_all_tbl.interface_header_id := v_po_headers_all_tbl.interface_header_id;
  select po_lines_interface_s.nextval
    into v_po_lines_all_tbl.interface_line_id
    from dual;

  --set  who
  v_po_lines_all_tbl.creation_date     := sysdate;
  v_po_lines_all_tbl.created_by        := g_user_id;
  v_po_lines_all_tbl.last_update_date  := sysdate;
  v_po_lines_all_tbl.last_updated_by   := g_user_id;
  v_po_lines_all_tbl.last_update_login := g_login_id;
  --PO行号
  v_po_lines_all_tbl.line_num     := v_line_num;
  v_po_lines_all_tbl.shipment_num := 1;
  --含税单价
  v_po_lines_all_tbl.market_price := v_market_price;
  --由于是基于PR创建的PO,为了后续的PR跟PO的关联以及判断PR行是否重复创建使用
  v_po_lines_all_tbl.line_attribute4 := rec_line.requisition_line_id;

  --1、采购订单数量
  --2、承诺日期可以赋值为空
  v_po_lines_all_tbl.quantity      := rec_line.quantity;
  v_po_lines_all_tbl.promised_date := null;

  --1、通知供应商栏位
  v_po_lines_all_tbl.note_to_vendor := rec_line.note_to_vendor;

  -- 根据请购单行ID获取
  --1、收货组织
  --2、类型
  --3、收单地址
  --4、物料类别
  --5、需求日期
  select prl.destination_organization_id,
         prl.line_type_id,
         prl.item_id,
         psv.ship_to_location_id,
         prl.category_id,
         prl.need_by_date
    into v_po_lines_all_tbl.ship_to_organization_id,
         v_po_lines_all_tbl.line_type_id,
         v_po_lines_all_tbl.item_id,
         v_po_lines_all_tbl.ship_to_location_id,
         v_category_id,
         v_po_lines_all_tbl.need_by_date
    from po_requisition_lines_all prl,
         --financials_system_params_all fsp 
         hr_locations_v psv
   where prl.requisition_line_id = rec_line.requisition_line_id
     and psv.bill_to_site_flag = 'Y'
     and psv.ship_to_site_flag = 'Y'
     and nvl(psv.attribute1, 'XXX') <> 'N' --收单地点生效标记(弹性域：附加的地点详细资料)
     and prl.destination_organization_id = psv.inventory_organization_id;
  --请购行ID
  v_po_lines_all_tbl.requisition_line_id := rec_line.requisition_line_id;
  --未税价格
  v_po_lines_all_tbl.unit_price := rec_line.unit_price;

  --PO行价格的来源
  v_po_lines_all_tbl.from_line_id := rec_line.blanket_line_id;

  --快码的类别
  v_po_lines_all_tbl.line_attribute_category_lines := 'STANDARD';

  --判断是否是VMI
  begin
    select nvl(t.consigned_from_supplier_flag, 'N')
      into v_vmi_flag
      from po_asl_attributes_v t, org_organization_definitions ood
     where t.item_id = rec_line.item_id
       and t.using_organization_id = ood.organization_id
       and ood.operating_unit = v_org_id
       and rownum = 1;
  exception
    when others then
      v_vmi_flag := 'N';
  end;
  if v_vmi_flag = 'Y' then
    v_po_lines_all_tbl.receipt_required_flag  := 'N';
    v_po_lines_all_tbl.accrue_on_receipt_flag := 'N';
    v_po_lines_all_tbl.consigned_flag         := 'Y';
  end if;
  insert into po_lines_interface values v_po_lines_all_tbl;
  --初始化
  mo_global.init('PO');
  mo_global.set_policy_context('S', v_po_headers_all_tbl.org_id);
  po_pdoi_grp.start_process(p_api_version                => 1.0,
                            p_init_msg_list              => fnd_api.g_true,
                            p_validation_level           => fnd_api.g_valid_level_full,
                            p_commit                     => fnd_api.g_false,
                            x_return_status              => v_return_status,
                            p_gather_intf_tbl_stat       => fnd_api.g_false,
                            p_calling_module             => po_pdoi_constants.g_call_mod_concurrent_prgm,
                            p_selected_batch_id          => v_batch_id,
                            p_batch_size                 => po_pdoi_constants.g_def_batch_size,
                            p_buyer_id                   => null,
                            p_document_type              => 'STANDARD',
                            p_document_subtype           => null,
                            p_create_items               => 'N',
                            p_create_sourcing_rules_flag => null,
                            p_rel_gen_method             => null,
                            p_sourcing_level             => null,
                            p_sourcing_inv_org_id        => null,
                            p_approved_status            => 'INCOMPLETE',
                            p_process_code               => po_pdoi_constants.g_process_code_pending,
                            p_interface_header_id        => null,
                            p_org_id                     => v_org_id,
                            p_ga_flag                    => null);

  if v_return_status <> fnd_api.g_ret_sts_success then
  
    begin
      select listagg(pie.error_message, ',') within group(order by pie.error_message) info
        into x_return_mesg
        from po_interface_errors pie
       where pie.interface_header_id = v_batch_id --1461289
       group by pie.interface_header_id;
    exception
      when others then
        select max(pie.error_message)
          into v_error_info
          from po.po_interface_errors pie
         where pie.batch_id = v_batch_id;
      
    end;
  
    if v_error_info is null then
      x_return_mesg := nvl(x_return_mesg, '') || v_error_info;
    end if;
  
    x_return_mesg := 'STD采购订单创建失败Batch_id:' || v_batch_id || '详细错误:' ||
                     x_return_mesg;
    raise fnd_api.g_exc_error;
  else
    select count(1)
      into v_count_po
      from po_headers_all ph
     where ph.segment1 = v_document_number;
    if v_count_po = 0 then
      begin
        select listagg(pie.error_message, ',') within group(order by pie.error_message) info
          into x_return_mesg
          from po_interface_errors pie
         where pie.interface_header_id = v_batch_id --1461289
         group by pie.interface_header_id;
      exception
        when others then
          x_return_mesg := 0;
      end;
    
      x_return_mesg := x_return_mesg || '采购订单创建失败,请重试或者联系系统管理员';
    
      raise fnd_api.g_exc_error;
    else
      x_po_number := v_document_number;
    
      update cux.cux_po_autocreate_auto_temp cpt
         set cpt.attribute2 = 'Y'
       where cpt.attribute1 = v_document_number;
    
    end if;
    begin
      select p.authorization_status, p.po_header_id
        into p_status, p_po_head_id
        from po_headers_all p
       where p.segment1 = x_po_number
         and p.org_id = v_org_id;
    exception
      when others then
        p_status := null;
      
        begin
          select listagg(pie.error_message, ',') within group(order by pie.error_message) info
            into x_return_mesg
            from po_interface_errors pie
           where pie.interface_header_id = v_batch_id --1461289
           group by pie.interface_header_id;
        exception
          when others then
            x_return_mesg := 0;
        end;
     
        x_return_mesg := x_return_mesg || '采购订单创建失败,请重试或者联系系统管理员';
        raise fnd_api.g_exc_error;
    end;
  
    if p_status = 'APPROVED' then
    
      for rec_close_po in (select prl.requisition_line_id,
                                  prl.requisition_header_id,
                                  prl.org_id
                             from cux_po_autocreate_temp          tmp,
                                  po_requisition_lines_all        prl,
                                  cux.cux_po_autocreate_auto_temp ap
                            where tmp.requisition_line_id =
                                  prl.requisition_line_id
                              and tmp.vendor_id = rec_header.vendor_id
                                 
                              and ap.requisition_line_id =
                                  prl.requisition_line_id
                              and ap.attribute2 = 'Y'
                                 
                              and tmp.vendor_site_id =
                                  rec_header.vendor_site_id
                              and tmp.currency_code =
                                  rec_header.currency_code
                              and tmp.attribute5 = rec_header.attribute5
                              and nvl(tmp.t_subinventory, 'xxx') =
                                  nvl(rec_header.t_subinventory, 'xxx')) loop
        po_moac_utils_pvt.set_org_context(rec_close_po.org_id);
        final_close_pr(p_req_header_id => rec_close_po.requisition_header_id,
                       p_req_line_id   => rec_close_po.requisition_line_id,
                       x_return_status => x_return_status,
                       x_return_mesg   => x_return_mesg);
        if x_return_status <> 'S' then
          x_return_mesg := '关闭PR错误：' || x_return_mesg;
          raise fnd_api.g_exc_error;
        end if;
      end loop;
    
    else
    
      po_document_action_pvt.do_approve(p_document_id      => p_po_head_id,
                                        p_document_type    => 'PO',
                                        p_document_subtype => 'STANDARD',
                                        p_note             => 'success',
                                        p_approval_path_id => 1,
                                        x_return_status    => p_pro_return_status,
                                        x_exception_msg    => x_return_mesg);
    
      if p_pro_return_status = 'S' then
      
        -- 关闭pr
        for rec_close_po in (select prl.requisition_line_id,
                                    prl.requisition_header_id,
                                    prl.org_id
                               from cux_po_autocreate_temp   tmp,
                                    po_requisition_lines_all prl
                              where tmp.requisition_line_id =
                                    prl.requisition_line_id
                                and tmp.vendor_id = rec_header.vendor_id
                                and tmp.vendor_site_id =
                                    rec_header.vendor_site_id
                                and tmp.currency_code =
                                    rec_header.currency_code
                                and tmp.attribute5 = rec_header.attribute5
                                and nvl(tmp.t_subinventory, 'xxx') =
                                    nvl(rec_header.t_subinventory, 'xxx')) loop
          po_moac_utils_pvt.set_org_context(rec_close_po.org_id);
          final_close_pr(p_req_header_id => rec_close_po.requisition_header_id,
                         p_req_line_id   => rec_close_po.requisition_line_id,
                         x_return_status => x_return_status,
                         x_return_mesg   => x_return_mesg);
          if x_return_status <> 'S' then
            x_return_mesg := '关闭PR错误：' || x_return_mesg;
            raise fnd_api.g_exc_error;
          end if;
        end loop;
      
        /*    
        mo_global.init('PO');
        mo_global.set_policy_context('S', v_po_headers_all_tbl.org_id);
        
        cux_common_pkg.init_application(p_org_code      => '201',
                                        p_moudle        => 'PO',
                                        p_user_id       => 61,
                                        x_return_status => v_return_status,
                                        x_return_mesg   => x_return_mesg);
        
        po_document_action_pvt.do_approve(p_document_id      => v_document_id,
                                          p_document_type    => 'PO',
                                          p_document_subtype => 'STANDARD',
                                          p_note             => null,
                                          p_approval_path_id => v_approval_path_id,
                                          x_return_status    => v_return_status,
                                          x_exception_msg    => x_return_mesg);
        
        if v_return_status <> fnd_api.g_ret_sts_success then
          x_return_mesg := '采购订单创建失败' || x_return_mesg;
          raise fnd_api.g_exc_error;
        end if;*/
      
      else
        x_return_mesg := '采购订单创建成功,未审批' || x_return_mesg;
        raise fnd_api.g_exc_error;
      end if;
    
    end if;
  
    commit;
  end if;

end;

 

5、非开放接口创建计划订单：

 

declare

  v_po_headers        po_headers_all%rowtype;
  v_po_lines          po_lines_all%rowtype;
  v_po_line_locations po_line_locations_all%rowtype;
  v_po_distributions  po_distributions_all%rowtype;

begin
  x_return_status := fnd_api.g_ret_sts_success;
  x_return_mesg   := null;
  mo_global.init('PO');
  mo_global.set_policy_context('S', p_org_id);

  --需要初始化否则取不到数据
  begin
    select fsp.ship_to_location_id,
           fsp.terms_id,
           fsp.set_of_books_id,
           fsp.inventory_organization_id,
           mp.material_account,
           mp.ap_accrual_account,
           mp.invoice_price_var_account
      into v_ship_to_location_id,
           v_terms_id,
           v_set_of_books_id,
           v_inventory_organization_id,
           v_material_account,
           v_ap_accrual_account,
           v_invoice_price_var_account
      from financials_system_parameters fsp, mtl_parameters mp
     where fsp.inventory_organization_id = mp.organization_id;
  exception
    when others then
      x_return_mesg := '获取财务选项设置错误:' || sqlerrm;
      raise fnd_api.g_exc_error;
  end;
  --获取采购订单号
  v_po_number := get_po_number(v_org_id,
                               'PO_HEADERS',
                               p_inv_org_id, --v_inventory_organization_id,
                               rec_header.t_subinventory,
                               rec_header.des_org_id,
                               rec_header.vendor_id,
                               rec_header.attribute2);

  v_header_id                    := po_headers_s.nextval;
  v_po_headers.po_header_id      := v_header_id;
  v_po_headers.agent_id          := g_employee_id;
  v_po_headers.type_lookup_code  := 'PLANNED';
  v_po_headers.last_update_date  := sysdate;
  v_po_headers.last_updated_by   := g_user_id;
  v_po_headers.creation_date     := sysdate;
  v_po_headers.created_by        := g_user_id;
  v_po_headers.last_update_login := g_login_id;
  v_po_headers.segment1          := v_po_number;
  v_po_headers.summary_flag      := 'N';
  v_po_headers.enabled_flag      := 'Y';
  v_po_headers.vendor_id         := rec_header.vendor_id;
  v_po_headers.vendor_site_id    := rec_header.vendor_site_id;

  --集中采购获取收单信息
  if p_org_id = 101 then
    v_po_headers.ship_to_location_id := rec_header.ship_to_location_id;
    v_po_headers.bill_to_location_id := rec_header.bill_to_location_id;
  else
    v_po_headers.ship_to_location_id := v_ship_to_id;
    v_po_headers.bill_to_location_id := v_ship_to_id;
  end if;

  v_po_headers.terms_id                 := nvl(rec_header.terms_id,
                                               v_terms_id);
  v_po_headers.currency_code            := rec_header.currency_code;
  v_po_headers.rate_date                := trunc(sysdate);
  v_po_headers.revision_num             := 0;
  v_po_headers.min_release_amount       := 0;
  v_po_headers.print_count              := 0;
  v_po_headers.confirming_order_flag    := 'N';
  v_po_headers.acceptance_required_flag := 'Y';
  v_po_headers.cancel_flag              := 'N';
  v_po_headers.firm_status_lookup_code  := 'N';
  v_po_headers.frozen_flag              := 'N';
  v_po_headers.supply_agreement_flag    := 'N';
  v_po_headers.org_id                   := v_org_id;
  v_po_headers.document_creation_method := 'ENTER_PO';
  v_po_headers.created_language         := userenv('LANG');
  v_po_headers.style_id                 := 1;
  v_po_headers.clm_document_number      := v_po_number;
  v_po_headers.attribute_category       := 'PLANNED'; --快码的类型
  v_po_headers.attribute1               := rec_header.attribute2;
  v_po_headers.comments                 := v_comments;

  --写入接口头信息
  insert into po_headers_all values v_po_headers;

  v_line_num     := 1;
  v_shipment_num := 1;
  v_line_count   := 0;
  --含税价,属于客开栏位
  v_market_price := nvl(rec_line.market_price,
                        get_market_price(rec_header.vendor_id,
                                         rec_header.vendor_site_id,
                                         rec_line.unit_price));
  v_line_count   := v_line_count + 1;

  --inserting into line table
  v_line_id                              := po_lines_s.nextval;
  v_po_lines.po_line_id                  := v_line_id;
  v_po_lines.last_update_date            := sysdate;
  v_po_lines.last_updated_by             := g_user_id;
  v_po_lines.creation_date               := sysdate;
  v_po_lines.created_by                  := g_user_id;
  v_po_lines.last_update_login           := g_login_id;
  v_po_lines.po_header_id                := v_header_id;
  v_po_lines.line_type_id                := 1;
  v_po_lines.line_num                    := v_line_num;
  v_po_lines.item_id                     := rec_line.item_id;
  v_po_lines.item_revision               := null;
  v_po_lines.category_id                 := rec_line.category_id;
  v_po_lines.item_description            := rec_line.item_description;
  v_po_lines.unit_meas_lookup_code       := rec_line.unit_meas_lookup_code;
  v_po_lines.allow_price_override_flag   := 'N';
  v_po_lines.list_price_per_unit         := 0;
  v_po_lines.unit_price                  := rec_line.unit_price;
  v_po_lines.quantity                    := rec_line.quantity;
  v_po_lines.market_price                := v_market_price;
  v_po_lines.unordered_flag              := 'N';
  v_po_lines.closed_flag                 := 'N';
  v_po_lines.cancel_flag                 := 'N';
  v_po_lines.capital_expense_flag        := 'N';
  v_po_lines.negotiated_by_preparer_flag := 'Y';
  v_po_lines.attribute_category          := 'PLANNED';
  v_po_lines.org_id                      := v_org_id;
  v_po_lines.order_type_lookup_code      := 'QUANTITY';
  v_po_lines.purchase_basis              := 'GOODS';
  v_po_lines.matching_basis              := 'QUANTITY';
  v_po_lines.base_unit_price             := v_po_lines.unit_price;
  v_po_lines.manual_price_change_flag    := 'N';
  v_po_lines.ip_category_id              := -2;
  v_po_lines.attribute_category          := 'PLANNED';
  v_po_lines.attribute4                  := rec_line.requisition_line_id;

  --价格来源
  v_po_lines.from_line_id := rec_line.blanket_line_id;

  insert into po_lines_all values v_po_lines;

  v_line_location_id                                 := po_line_locations_s.nextval;
  v_po_line_locations.line_location_id               := v_line_location_id;
  v_po_line_locations.last_update_date               := sysdate;
  v_po_line_locations.last_updated_by                := g_user_id;
  v_po_line_locations.creation_date                  := sysdate;
  v_po_line_locations.created_by                     := g_user_id;
  v_po_line_locations.last_update_login              := g_login_id;
  v_po_line_locations.po_header_id                   := v_header_id;
  v_po_line_locations.po_line_id                     := v_line_id;
  v_po_line_locations.quantity                       := rec_line.quantity;
  v_po_line_locations.quantity_received              := 0;
  v_po_line_locations.quantity_accepted              := 0;
  v_po_line_locations.quantity_rejected              := 0;
  v_po_line_locations.quantity_billed                := 0;
  v_po_line_locations.quantity_cancelled             := 0;
  v_po_line_locations.unit_meas_lookup_code          := rec_line.unit_meas_lookup_code;
  v_po_line_locations.ship_to_location_id            := v_ship_to_location_id; --18943; --v_ship_to_location_id; modify by iven.lin 2021-07-06
  v_po_line_locations.need_by_date                   := rec_line.need_by_date;
  v_po_line_locations.promised_date                  := null;
  v_po_line_locations.last_accept_date               := rec_line.need_by_date + 30;
  v_po_line_locations.price_override                 := rec_line.unit_price;
  v_po_line_locations.encumbered_flag                := 'N';
  v_po_line_locations.taxable_flag                   := 'Y';
  v_po_line_locations.cancel_flag                    := 'N';
  v_po_line_locations.firm_status_lookup_code        := 'N';
  v_po_line_locations.inspection_required_flag       := 'N';
  v_po_line_locations.receipt_required_flag          := 'Y';
  v_po_line_locations.qty_rcv_tolerance              := 0;
  v_po_line_locations.qty_rcv_exception_code         := 'REJECT'; --modified by iven.lin 2020-01-06
  v_po_line_locations.enforce_ship_to_location_code  := 'NONE';
  v_po_line_locations.allow_substitute_receipts_flag := 'Y';
  v_po_line_locations.days_early_receipt_allowed     := 5;
  v_po_line_locations.days_late_receipt_allowed      := 5;
  v_po_line_locations.receipt_days_exception_code    := 'NONE';
  v_po_line_locations.invoice_close_tolerance        := 0;
  v_po_line_locations.receive_close_tolerance        := 0;
  v_po_line_locations.ship_to_organization_id        := p_inv_org_id; --v_inventory_organization_id;-- p_inv_org_id modify by iven.lin 2021-07-16
  v_po_line_locations.shipment_num                   := v_shipment_num;
  v_po_line_locations.shipment_type                  := 'PLANNED';
  v_po_line_locations.closed_code                    := 'OPEN';
  v_po_line_locations.receiving_routing_id           := 3;
  v_po_line_locations.accrue_on_receipt_flag         := 'Y';
  v_po_line_locations.org_id                         := v_org_id;
  v_po_line_locations.country_of_origin_code         := 'CN';
  v_po_line_locations.match_option                   := 'R';
  v_po_line_locations.value_basis                    := 'QUANTITY';
  v_po_line_locations.matching_basis                 := 'QUANTITY';
  v_po_line_locations.outsourced_assembly            := 2;
  v_po_line_locations.line_location_id               := v_line_location_id;

  if v_vmi_flag = 'Y' then
    v_po_line_locations.receipt_required_flag  := 'N';
    v_po_line_locations.accrue_on_receipt_flag := 'N';
    v_po_line_locations.consigned_flag         := 'Y';
    --  v_po_line_locations.closed_code            := 'CLOSED FOR INVOICE';
  end if;
  insert into po_line_locations_all values v_po_line_locations;

  v_distribution_id                              := po_distributions_s.nextval;
  v_po_distributions.po_distribution_id          := v_distribution_id;
  v_po_distributions.line_location_id            := v_line_location_id;
  v_po_distributions.last_update_date            := sysdate;
  v_po_distributions.last_updated_by             := g_user_id;
  v_po_distributions.creation_date               := sysdate;
  v_po_distributions.created_by                  := g_user_id;
  v_po_distributions.last_update_login           := g_login_id;
  v_po_distributions.po_header_id                := v_header_id;
  v_po_distributions.po_line_id                  := v_line_id;
  v_po_distributions.line_location_id            := v_line_location_id;
  v_po_distributions.set_of_books_id             := v_set_of_books_id;
  v_po_distributions.code_combination_id         := v_material_account;
  v_po_distributions.quantity_ordered            := rec_line.quantity;
  v_po_distributions.quantity_delivered          := 0;
  v_po_distributions.quantity_billed             := 0;
  v_po_distributions.quantity_cancelled          := 0;
  v_po_distributions.rate_date                   := sysdate;
  v_po_distributions.encumbered_flag             := 'N';
  v_po_distributions.destination_type_code       := 'INVENTORY';
  v_po_distributions.destination_organization_id := p_inv_org_id;
  v_po_distributions.accrual_account_id          := v_ap_accrual_account;
  v_po_distributions.variance_account_id         := v_invoice_price_var_account;
  v_po_distributions.prevent_encumbrance_flag    := 'N';
  v_po_distributions.distribution_num            := 1;
  v_po_distributions.accrue_on_receipt_flag      := 'Y';
  v_po_distributions.org_id                      := v_org_id;
  v_po_distributions.recoverable_tax             := v_recoverable_tax;
  v_po_distributions.nonrecoverable_tax          := 0;
  v_po_distributions.distribution_type           := 'PLANNED';

  if v_vmi_flag = 'Y' then
    v_po_distributions.accrue_on_receipt_flag := 'N';
  end if;

  begin
    select prd.distribution_id
      into v_req_distribution_id
      from po_req_distributions_all prd
     where prd.requisition_line_id = rec_line.requisition_line_id;
  exception
    when others then
      v_req_distribution_id := null;
  end;
  v_po_distributions.req_distribution_id := v_req_distribution_id;
  insert into po_distributions_all values v_po_distributions;

  commit;
  apps.po_document_update_grp.launch_po_approval_wf(p_api_version              => 1.0,
                                                    p_init_msg_list            => fnd_api.g_true,
                                                    x_return_status            => x_return_status,
                                                    p_document_id              => v_header_id,
                                                    p_document_type            => 'PO',
                                                    p_document_subtype         => 'PLANNED',
                                                    p_preparer_id              => g_employee_id,
                                                    p_approval_background_flag => po_core_s.g_parameter_no,
                                                    p_mass_update_releases     => 'N');
  if x_return_status <> 'S' then
  
    po_document_action_pvt.do_approve(p_document_id      => v_header_id,
                                      p_document_type    => 'PO',
                                      p_document_subtype => 'STANDARD',
                                      p_note             => 'success',
                                      p_approval_path_id => 1,
                                      x_return_status    => x_return_status,
                                      x_exception_msg    => x_exception_msg);
  
    if x_return_status <> 'S' then
    
      v_target := 'E';
      /*for i in 1 .. fnd_msg_pub.count_msg loop
        v_return_mesg := fnd_msg_pub.get(p_msg_index => i,
                                         p_encoded   => 'F');
      
        x_return_mesg := nvl(x_return_mesg, '') || v_return_mesg;
      end loop;*/
    
      x_return_mesg := x_return_mesg;
    
      raise fnd_api.g_exc_error;
    else
      update cux.cux_po_autocreate_auto_temp cpt
         set cpt.attribute2 = 'Y'
       where cpt.attribute1 = v_po_number;
    end if;
  else
    update cux.cux_po_autocreate_auto_temp cpt
       set cpt.attribute2 = 'Y'
     where cpt.attribute1 = v_po_number;
  end if;
  x_po_number := v_po_number;
end loop;
commit;

if v_target = 'S' then

  for rec_close_po in (select prl.requisition_line_id,
                              prl.requisition_header_id
                         from cux_po_autocreate_temp          tmp,
                              po_requisition_lines_all        prl,
                              cux.cux_po_autocreate_auto_temp ap
                        where tmp.requisition_line_id =
                              prl.requisition_line_id
                          and ap.requisition_line_id =
                              prl.requisition_line_id
                          and ap.attribute2 = 'Y') loop
    final_close_pr(p_req_header_id => rec_close_po.requisition_header_id,
                   p_req_line_id   => rec_close_po.requisition_line_id,
                   x_return_status => v_return_status,
                   x_return_mesg   => v_return_mesg);
    if v_return_status <> 'S' then
      v_return_mesg := '关闭PR错误：' || v_return_mesg;
      x_return_mesg := nvl(x_return_mesg, '') || v_return_mesg;
      raise fnd_api.g_exc_error;
    end if;
  end loop;
  commit;

end if;

end;