Oracle EBS:使用接口表抛数所生成OM订单

  /*插入至接口表中*/
  procedure InsertOOMINTERFACE(P_SOE_ORDERNO varchar2,
                               v_retval      out varchar2,
                               v_retmessage  out varchar2) is
    cursor cur_om_header is
    
      select *
        from edu_om_temp_header oth
       where oth.soe_order_no = P_SOE_ORDERNO
         and rownum = 1
       order by oth.soeheadid desc;
  
    cursor cur_om_lines is
      select *
        from edu_om_temp_line otl
       where otl.soe_order_no = P_SOE_ORDERNO;
  
    v_header_id  varchar(20);
    retval       varchar2(5000);
    v_line_id    varchar(20);
    v_errorcode  varchar(1000);
    v_bill_to_id Number; ---收单方ID
    v_count      number;
    v_request_id number;
  
  begin
  
    for crm_header_rec in cur_om_header loop
    
      Select Oe_Order_Headers_s.Nextval Into v_header_id From Dual;
    
      Insert Into oe_headers_iface_all
        (Operation_Code,
         order_Source_Id,
         org_Id,
         orig_Sys_Document_Ref,
         ordered_Date,
         order_Type_Id,
         Price_List_Id,
         Transactional_Curr_Code,
         Salesrep_Id,
         Payment_Term_Id,
         Sold_From_Org_Id,
         Sold_To_Org_Id,
         sold_to_contact_id,
         Ship_From_Org_Id,
         Ship_To_Org_Id,
         ship_to_contact_id,
         Invoice_To_Org_Id,
         invoice_to_contact_id,
         Booked_Flag,
         Closed_Flag,
         Created_By,
         Creation_Date,
         Last_Update_Date,
         Last_Updated_By,
         Request_Date,
         Return_Reason_Code,
         Accounting_Rule_Id,
         Conversion_Type_Code,
         Demand_Class_Code,
         Fob_Point_Code,
         Freight_Carrier_Code,
         Freight_Terms_Code,
         Invoicing_Rule_Id,
         Last_Update_Login,
         order_Category,
         order_Date_Type_Code,
         Sales_Channel_Code,
         Tax_Exempt_Flag,
         global_attribute2,
         global_attribute3)
      Values
        ('Insert',
         0,
         102,
         'OE_ORDER_HEADERS_ALL' || v_header_id,
         crm_header_rec.Ordered_Date,
         crm_header_rec.Order_Type_Id,
         crm_header_rec.Price_List_Id,
         crm_header_rec.Transactional_Curr_Code,
         crm_header_rec.Salesrep_Id,
         crm_header_rec.Payment_Term_Id,
         crm_header_rec.customer_id,
         crm_header_rec.Sold_To_Org_Id,
         NULL,
         crm_header_rec.Ship_To_Org_Id,
         null,
         NULL,
         crm_header_rec.Invoice_To_Org_Id,
         NULL,
         'Y',
         'N',
         -1,
         crm_header_rec.Creation_Date,
         crm_header_rec.Creation_Date,
         -1,
         SYSDATE,
         NULL,
         1,
         'Corporate',
         crm_header_rec.Demand_Class_Code,
         NULL,
         NULL,
         NULL,
         -2,
         -1,
         'ORDER',
         NULL,
         NULL,
         'S',
         crm_header_rec.soeheadid,
         crm_header_rec.soe_order_no);
    
      FOR crm_line_rec IN cur_om_lines LOOP
      
        Select Oe_Order_Lines_s.NEXTVAL INTO v_line_id FROM Dual;
      
        --dbms_output.put_line('line_id is ' || v_line_id);
      
        /*v_line_id := v_line_id + 1;*/
        Insert INTO oe_lines_iface_all
          (Orig_Sys_Document_Ref,
           order_Source_Id,
           orig_Sys_Line_Ref,
           Created_By,
           Creation_Date,
           Last_Updated_By,
           Last_Update_Date,
           org_Id,
           Inventory_Item_Id,
           ordered_Quantity,
           order_Quantity_Uom,
           Unit_Selling_Price,
           Unit_List_Price,
           Request_Date,
           Schedule_Ship_Date,
           Return_Reason_Code,
           Subinventory,
           Sold_From_Org_Id,
           Sold_To_Org_Id,
           Ship_From_Org_Id,
           Ship_To_Org_Id,
           Invoice_To_Org_Id,
           Operation_Code,
           Line_Type_Id,
           Line_Number,
           Price_List_Id,
           Pricing_Date,
           Tax_Code,
           Tax_Value,
           Tax_Date,
           Payment_Term_Id,
           Salesrep_Id,
           Item_Revision,
           Calculate_Price_Flag,
           Accounting_Rule_Id,
           Closed_Flag,
           Delivery_Lead_Time,
           Demand_Class_Code,
           Invoicing_Rule_Id,
           Item_Type_Code,
           Last_Update_Login,
           Option_Flag,
           Pricing_Quantity,
           Pricing_Quantity_Uom,
           Promise_Date,
           Schedule_Arrival_Date,
           Shipment_Number,
           Shipment_Priority_Code,
           Ship_Set_Id,
           Ship_Tolerance_Above,
           Ship_Tolerance_Below,
           Source_Type_Code,
           Tax_Exempt_Flag,
           global_attribute2,
           ATTRIBUTE12,
           global_attribute3)
        VALUES
          ('OE_ORDER_HEADERS_ALL' || v_header_id,
           0,
           'OE_ORDER_LINES_ALL' || v_line_id,
           -1,
           SYSDATE,
           -1,
           SYSDATE,
           102,
           crm_line_rec.inventory_item_id,
           crm_line_rec.ordered_quantity,
           crm_line_rec.pricing_quantity_uom,
           crm_line_rec.Unit_Selling_Price,
           crm_line_rec.Unit_List_Price,
           crm_line_rec.Request_Date,
           NULL,
           NULL,
           NULL,
           crm_line_rec.Sold_From_Org_Id,
           crm_line_rec.Sold_To_Org_Id,
           crm_line_rec.Ship_From_Org_Id,
           crm_line_rec.Ship_To_Org_Id,
           crm_header_rec.Invoice_To_Org_Id,
           'Insert',
           crm_line_rec.Line_Type_Id,
           NULL,
           crm_line_rec.Price_List_Id,
           crm_line_rec.Pricing_Date,
           NULL,
           NULL,
           NULL,
           crm_header_rec.payment_term_id,
           crm_line_rec.Salesrep_Id,
           NULL,
           'Y',
           1,
           'N',
           NULL,
           crm_line_rec.Demand_Class_Code,
           -2,
           'STANDARD',
           -1,
           NULL,
           crm_line_rec.Pricing_Quantity,
           crm_line_rec.Pricing_Quantity_Uom,
           crm_line_rec.Promise_Date,
           NULL,
           NULL,
           NULL,
           NULL,
           0,
           0,
           'INTERNAL',
           'S',
           crm_line_rec.global_attribute2,
           crm_line_rec.soelineid,
           crm_line_rec.soe_order_no);
      
        retval := 1;
      
      END LOOP;
    
    END LOOP;
  

        COMMIT;
  
  end InsertOOMINTERFACE;

Procedure RUNOMIMPORTREQUEST(retcode out number, retmessage out varchar2) is
    v_request_id   number;
    v_request_flag BOOLEAN;
    v_concat       varchar2(25) := 'CUX' || to_char(sysdate, 'YYMMDDHH24');
    v_phase        varchar2(240);
    v_status       varchar2(240);
    v_dev_phase    varchar2(240);
    v_dev_status   varchar2(240);
    v_message      varchar2(240);
    custom_exception EXCEPTION;
  Begin
    --初始化设置 dezai.cn
    fnd_global.apps_initialize(5047, 1013, 201);
  
    v_request_id := FND_REQUEST.SUBMIT_REQUEST('ONT', -- 请求定义的模组代码
                                               'OEOIMP', -- Concurrent 名称
                                               NULL, --固定值
                                               NULL, --固定值
                                               FALSE, --固定值
                                               '', -- 订单来源
                                               '', --Order Reference
                                               'NO', --validate only,
                                               '', --Instance
                                               '', --change sequence
                                               null, --Trim Trailing blanks
                                               'Yes', --validate descriptive flexfields
                                               chr(0) -- 代表传参结束,本例子说明传了3个参数
                                               );
  
    IF v_request_id <> 0 THEN
    
      COMMIT;
    
    END IF;
  
  EXCEPTION
    WHEN custom_exception THEN
    
      retcode    := -20002;
      retmessage := ' 错误提示：导入ERP正式表出错！' || SUBSTR(SQLERRM, 1, 250);
    
      --RAISE custom_exception;
      ROLLBACK;
      RETURN;
    
    WHEN OTHERS THEN
    
      retcode    := -20003;
      retmessage := ' 错误提示：导入ERP正式表出错！' || SUBSTR(SQLERRM, 1, 250);
    
      FND_FILE.PUT_LINE(FND_FILE.LOG, retcode);
      --RAISE custom_exception;
      ROLLBACK;
      RETURN;
    
  end RUNOMIMPORTREQUEST;