oracle ebs采购申请接口表,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;