程序包头：
CREATE OR REPLACE PACKAGE CUX_PO_DOC_IMP_PKG IS
/* $Header: CUXPOIMPS.pls 120.1 2012/02/18 12:30:11 iven.lin ship $ */
/*============================================================
Copyright (C) IBM Global Business Services
AllRights Reserved
==============================================================
* ============================================================
* REQUEST NUMBER:
* PROGRAM NAME: CUX_PO_DOC_IMP_PKG
* TYPE: PACKAGE
* DESCRIPTION: To import Open PO
*
* HISTORY:
* Version Date Author Name Desc
* 1.0 02/18/2012 IVEN.LIN Draft
*
*
* ===========================================================*/
/* =============================================
* REQUEST NUMBER:
* FUNCTION / PROCEDURE: PROCEDURE
* NAME: open_po_import
* DESCRIPTION:
* Main procedure to import PO
* ARGUMENT:
* p_cux_paramN VARCHAR2 -- 自定义数据列不属于接口表,用于扩展
* 其它参数同表 的列含义。或者对应接口表中ID/CODE列的名称。
*
* RETURN:
* HISTORY:
* Version Date Author Name Desc
* 1.0 02/18/2009 IVEN.LIN Draft
*
* 自定义数据列说明：
* p_cux_paramN 用于收集模板中没有对应的接口表字段的信息。
* 比如接口表中存放ID或CODE列，而数据收集为名称/说明信息。可以使用自定义数据列来传递名称/说明信息。从而转换成接口需要的字段。
*
* 以下对程序中占用的自定义数据列说明。根据模板的不同，开发人员可相应的修改这些自定义数据列对应的信息，以及校验规则。
* ==============================================*/
PROCEDURE open_po_import( --Pub---------
p_submit_request_flag IN VARCHAR2 DEFAULT 'Y',
p_org_name in varchar2 default null,
p_org_id in number default null,
p_order_num in varchar2 default null,
p_vendor_name in varchar2 default null,
p_vendor_id in number default null,
p_vendor_site in varchar2 default null,
p_vendor_site_id in number default null,
p_ship_location in varchar2 default null,
p_ship_location_id in number default null,
p_bill_location in varchar2 default null,
p_bill_location_id in number default null,
p_agent_name in varchar2 default null,
p_agent_id in number default null,
p_po_amount in number default null,
p_tax_name in varchar2 default null,
p_tax_code in varchar2 default null,
p_tax_rate in number default null,
p_currency_name in varchar2 default null,
p_currency_code in varchar2 default null,
p_line_num in number default null,
p_line_type_name in varchar2 default null,
p_line_type_code in varchar2 default null,
p_item_code in varchar2 default null,
p_item_id in number default Null,
p_item_category in varchar2 default null,
p_item_category_id in number default null,
p_item_desc in varchar2 default null,
p_uom in varchar2 default null,
p_uom_code in varchar2 default null,
p_order_quantity in number default null,
p_unit_price in number default null,
p_need_by_date in date default null,
p_payment_term in varchar2 default null,
p_term_id in number default null,
p_source_doc_type in varchar2 default null,
p_source_doc_num in varchar2 default null,
p_source_doc_line in number default null,
p_ship_org in varchar2 default null,
p_ship_org_id in number default null,
p_error_flag in varchar2 default null,
p_error_message in varchar2 default null,
p_attribute1 in varchar2 default null,
p_attribute2 in varchar2 default null,
p_attribute3 in varchar2 default null,
p_attribute4 in varchar2 default null,
p_attribute5 in varchar2 default null,
p_attribute6 in varchar2 default null,
p_attribute7 in varchar2 default null,
p_attribute8 in varchar2 default null,
p_attribute9 in varchar2 default null,
p_attribute10 in varchar2 default null,
p_cux_param1 IN VARCHAR2 DEFAULT NULL,
p_cux_param2 IN VARCHAR2 DEFAULT NULL,
p_cux_param3 IN VARCHAR2 DEFAULT NULL,
p_cux_param4 IN VARCHAR2 DEFAULT NULL,
p_cux_param5 IN VARCHAR2 DEFAULT NULL,
p_cux_param6 IN VARCHAR2 DEFAULT NULL,
p_cux_param7 IN VARCHAR2 DEFAULT NULL,
p_cux_param8 IN VARCHAR2 DEFAULT NULL,
p_cux_param9 IN VARCHAR2 DEFAULT NULL,
p_cux_param10 IN VARCHAR2 DEFAULT NULL,
p_cux_param11 IN VARCHAR2 DEFAULT NULL,
p_cux_param12 IN VARCHAR2 DEFAULT NULL,
p_cux_param13 IN VARCHAR2 DEFAULT NULL,
p_cux_param14 IN VARCHAR2 DEFAULT NULL,
p_cux_param15 IN VARCHAR2 DEFAULT NULL,
p_cux_param16 IN VARCHAR2 DEFAULT NULL,
p_cux_param17 IN VARCHAR2 DEFAULT NULL,
p_cux_param18 IN VARCHAR2 DEFAULT NULL,
p_cux_param19 IN VARCHAR2 DEFAULT NULL,
p_cux_param20 IN VARCHAR2 DEFAULT NULL);
END CUX_PO_DOC_IMP_PKG;
程序包体：
CREATE OR REPLACE PACKAGE BODY CUX_PO_DOC_IMP_PKG IS
/* $Header: CUXPOIMPS.pls 120.1 2009/02/18 12:30:11 iven.lin ship $ */
g_session_id NUMBER := fnd_global.session_id;
g_request_id NUMBER;
g_interface_header_id Number := -1;
g_order_num Varchar2(40) := '-1';
g_org_id Number := -1;
g_vendor_id Number := -1;
g_vendor_site_id Number := -1;
/* =============================================
* REQUEST NUMBER:
* FUNCTION / PROCEDURE: PROCEDURE
* NAME: open_po_import
* DESCRIPTION:
* 导入采购订单
* ARGUMENT:
*
* RETURN:
* HISTORY:
* Version Date Author Name Desc
* 1.0 02/18/2009 IVEN.LIN Draft
*
* ==============================================*/
PROCEDURE open_po_import( --Pub---------
p_submit_request_flag IN VARCHAR2 DEFAULT 'Y',
p_org_name in varchar2 default null,
p_org_id in number default null,
p_order_num in varchar2 default null,
p_vendor_name in varchar2 default null,
p_vendor_id in number default null,
p_vendor_site in varchar2 default null,
p_vendor_site_id in number default null,
p_ship_location in varchar2 default null,
p_ship_location_id in number default null,
p_bill_location in varchar2 default null,
p_bill_location_id in number default null,
p_agent_name in varchar2 default null,
p_agent_id in number default null,
p_po_amount in number default null,
p_tax_name in varchar2 default null,
p_tax_code in varchar2 default null,
p_tax_rate in number default null,
p_currency_name in varchar2 default null,
p_currency_code in varchar2 default null,
p_line_num in number default null,
p_line_type_name in varchar2 default null,
p_line_type_code in varchar2 default null,
p_item_code in varchar2 default null,
p_item_id in number default Null,
p_item_category in varchar2 default null,
p_item_category_id in number default null,
p_item_desc in varchar2 default null,
p_uom in varchar2 default null,
p_uom_code in varchar2 default null,
p_order_quantity in number default null,
p_unit_price in number default null,
p_need_by_date in date default null,
p_payment_term in varchar2 default null,
p_term_id in number default null,
p_source_doc_type in varchar2 default null,
p_source_doc_num in varchar2 default null,
p_source_doc_line in number default null,
p_ship_org in varchar2 default null,
p_ship_org_id in number default null,
p_error_flag in varchar2 default null,
p_error_message in varchar2 default null,
p_attribute1 in varchar2 default null,
p_attribute2 in varchar2 default null,
p_attribute3 in varchar2 default null,
p_attribute4 in varchar2 default null,
p_attribute5 in varchar2 default null,
p_attribute6 in varchar2 default null,
p_attribute7 in varchar2 default null,
p_attribute8 in varchar2 default null,
p_attribute9 in varchar2 default null,
p_attribute10 in varchar2 default null,
p_cux_param1 IN VARCHAR2 DEFAULT NULL,
p_cux_param2 IN VARCHAR2 DEFAULT NULL,
p_cux_param3 IN VARCHAR2 DEFAULT NULL,
p_cux_param4 IN VARCHAR2 DEFAULT NULL,
p_cux_param5 IN VARCHAR2 DEFAULT NULL,
p_cux_param6 IN VARCHAR2 DEFAULT NULL,
p_cux_param7 IN VARCHAR2 DEFAULT NULL,
p_cux_param8 IN VARCHAR2 DEFAULT NULL,
p_cux_param9 IN VARCHAR2 DEFAULT NULL,
p_cux_param10 IN VARCHAR2 DEFAULT NULL,
p_cux_param11 IN VARCHAR2 DEFAULT NULL,
p_cux_param12 IN VARCHAR2 DEFAULT NULL,
p_cux_param13 IN VARCHAR2 DEFAULT NULL,
p_cux_param14 IN VARCHAR2 DEFAULT NULL,
p_cux_param15 IN VARCHAR2 DEFAULT NULL,
p_cux_param16 IN VARCHAR2 DEFAULT NULL,
p_cux_param17 IN VARCHAR2 DEFAULT NULL,
p_cux_param18 IN VARCHAR2 DEFAULT NULL,
p_cux_param19 IN VARCHAR2 DEFAULT NULL,
p_cux_param20 IN VARCHAR2 DEFAULT NULL) IS
lv_err_msg VARCHAR2(2000); --返回错误信息
lv_status VARCHAR2(30); --处理状态
ln_user_id NUMBER := fnd_global.user_id;
lr_ph_rec po_headers_interface%ROWTYPE;
lr_pl_rec po_lines_interface%ROWTYPE;
lr_pd_rec po_distributions_interface%ROWTYPE;
lb_ph_existed_flag BOOLEAN := FALSE;
lb_pl_existed_flag BOOLEAN := FALSE;
lb_pd_existed_flag BOOLEAN := FALSE;
lb_expense_acct_id Number;
ln_cnt NUMBER := 0;
-- < This part of the code should be added for Bug>------------------------
msgdata VARCHAR2(32000);
msgname VARCHAR2(30);
msgapp VARCHAR2(50);
msgencoded VARCHAR2(32100);
msgencodedlen NUMBER(6);
msgnameloc NUMBER(6);
msgtextloc NUMBER(6);
BEGIN
lv_status := NULL;
lv_err_msg := NULL;
lr_ph_rec := NULL;
lr_pl_rec := NULL;
lr_pd_rec := NULL;
/*-- Step 1: Validate the records ---------------------------------------------*/
--Init variables
--po_headers_interface
lr_ph_rec.interface_source_code := 'PO';
lr_ph_rec.document_type_code := 'STANDARD';
lr_ph_rec.document_num := p_order_num;
lr_ph_rec.process_code := 'PENDING';
lr_ph_rec.approval_status := 'APPROVED';
lr_ph_rec.action := 'ORIGINAL';
lr_ph_rec.currency_code := p_currency_code;
lr_ph_rec.agent_id := p_agent_id;
lr_ph_rec.vendor_id := p_vendor_id;
lr_ph_rec.vendor_site_id := p_vendor_site_id;
lr_ph_rec.ship_to_location_id := p_ship_location_id;
lr_ph_rec.bill_to_location_id := p_bill_location_id;
lr_ph_rec.terms_id := p_term_id;
lr_ph_rec.creation_date := SYSDATE;
lr_ph_rec.created_by := ln_user_id;
lr_ph_rec.last_update_date := Sysdate;
lr_ph_rec.Last_Updated_By := ln_user_id;
lr_ph_rec.org_id := p_org_id;
--po_lines_interface
lr_pl_rec.action := 'ORIGINAL';
lr_pl_rec.line_num := p_line_num;
lr_pl_rec.shipment_num := 1;
lr_pl_rec.line_type := p_line_type_code;
lr_pl_rec.item_id := p_item_id;
lr_pl_rec.uom_code := p_uom_code;
lr_pl_rec.quantity := p_order_quantity;
lr_pl_rec.unit_price := p_unit_price;
lr_pl_rec.promised_date := p_need_by_date;
lr_pl_rec.need_by_date := p_need_by_date;
lr_pl_rec.shipment_type := 'STANDARD';
lr_pl_rec.ship_to_location_id := p_ship_location_id;
lr_pl_rec.last_update_date := SYSDATE;
lr_pl_rec.last_updated_by := ln_user_id;
lr_pl_rec.creation_date := SYSDATE;
lr_pl_rec.created_by := ln_user_id;
--po_distributions_interface
lr_pd_rec.quantity_ordered := p_order_quantity;
lr_pd_rec.charge_account_id := p_attribute1;
lr_pd_rec.last_update_date := SYSDATE;
lr_pd_rec.last_updated_by := ln_user_id;
lr_pd_rec.creation_date := SYSDATE;
lr_pd_rec.created_by := ln_user_id;
--Header
--==============================================================================
--Org
IF lr_ph_rec.org_id IS NULL THEN
BEGIN
Select hou.organization_id
Into lr_ph_rec.org_id
From hr_operating_units hou
Where hou.Name = p_org_name;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '组织错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--Vendor Name
IF lr_ph_rec.vendor_id IS NULL THEN
BEGIN
Select pv.vendor_id
Into lr_ph_rec.vendor_id
From po_vendors pv
Where pv.vendor_name = p_vendor_name
And pv.enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '供应商名称错误;';
lv_status := 'E';
END;
END IF;
--==============================================================================
--Vendor Site
IF lr_ph_rec.vendor_site_id IS NULL THEN
BEGIN
Select pvs.vendor_site_id
Into lr_ph_rec.vendor_site_id
From po_vendor_sites_all pvs
Where pvs.vendor_site_code = p_vendor_site
And pvs.vendor_id = lr_ph_rec.vendor_id
And pvs.org_id = lr_ph_rec.org_id;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '供应商地点错误;';
lv_status := 'E';
END;
END IF;
--==============================================================================
--Ship Site
IF lr_ph_rec.ship_to_location_id IS NULL THEN
BEGIN
Select pl.location_id
Into lr_ph_rec.ship_to_location_id
From po_locations_val_v pl
Where pl.location_code = p_ship_location
And pl.ship_to_site_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '供应商收货地点错误;';
lv_status := 'E';
END;
END IF;
--==============================================================================
--Bill Site
IF lr_ph_rec.bill_to_location_id IS NULL THEN
BEGIN
Select pl.location_id
Into lr_ph_rec.bill_to_location_id
From po_locations_val_v pl
Where pl.location_code = p_bill_location
And pl.bill_to_site_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '供应商发票地点错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--Buyer
IF lr_ph_rec.agent_id IS NULL THEN
BEGIN
Select pan.buyer_id
Into lr_ph_rec.agent_id
From po_agents_name_v pan
Where pan.full_name = p_agent_name;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '供应商发票地点错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--Term
IF lr_ph_rec.terms_id IS NULL THEN
BEGIN
Select term_id
Into lr_ph_rec.terms_id
From ap_terms
Where Name = p_payment_term;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '付款条款错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--Currency
IF lr_ph_rec.currency_code IS NULL THEN
BEGIN
Select currency_code
Into lr_ph_rec.currency_code
From fnd_currencies
Where currency_code = p_currency_name;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '币种错误;';
lv_status := 'E';
END;
END If;
--Line
--==============================================================================
--Line Type
IF lr_pl_rec.line_type IS NULL THEN
BEGIN
/* -- Modify By Jianghua at 2009/03/20 09:01
Select decode(p_line_type_name,
'货物', 'Goods',
'服务', 'Services','Goods')
Into lr_pl_rec.line_type
From dual;
*/
SELECT pt.line_type_id
INTO lr_pl_rec.line_type_id
FROM po_line_types pt
WHERE pt.line_type = p_line_type_name;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '发票行类型错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--ITEM, Material Account
IF lr_pl_rec.item_id IS Null THEN
BEGIN
Select msi.inventory_item_id,
msi.organization_id,
msi.expense_account
Into lr_pl_rec.item_id,
lr_pl_rec.ship_to_organization_id,
lb_expense_acct_id
From mtl_system_items_b msi, org_organization_definitions ood
Where 1 = 1
And ood.operating_unit = lr_ph_rec.org_id
And ood.organization_code = p_ship_org
And ood.organization_id = msi.organization_id
/* msi.organization_id = lr_ph_rec.Org_Id */ -- Remark By Jianghua at 2009/03/19 10:50
And msi.segment1 = p_item_code
And msi.purchasing_item_flag = 'Y';
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '物料名称和费用科目错误;';
lv_status := 'E';
END;
END If;
--==============================================================================
--UOM
IF lr_pl_rec.Uom_Code IS Null THEN
BEGIN
Select muo.uom_code
Into lr_pl_rec.Uom_Code
From mtl_units_of_measure muo
Where muo.unit_of_measure_tl = p_uom;
/*muo.unit_of_measure = p_uom; */ -- Remark By Jianghua at 2009/03/19 10:50
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || '物料单位错误;';
lv_status := 'E';
END;
END If;
--Distribution
--==============================================================================
--Expense
IF lr_pd_rec.charge_account_id IS Null THEN
If lb_expense_acct_id Is Not Null Then
lr_pd_rec.charge_account_id := lb_expense_acct_id;
Else
lv_err_msg := lv_err_msg || '物料费用科目错误;';
lv_status := 'E';
End If;
END If;
IF lv_status = 'E' THEN
raise_application_error(-20000, lv_err_msg);
RETURN;
END IF;
/*-- Step 2: Import valid records into interface ------------------------------*/
--==============================================================================
--Assign Sequence
If lr_ph_rec.interface_header_id IS Null Then
If lr_ph_rec.org_id = g_org_id And
lr_ph_rec.document_num = g_order_num And
lr_ph_rec.vendor_id = g_vendor_id And
lr_ph_rec.vendor_site_id = g_vendor_site_id Then
lb_ph_existed_flag := True;
lr_ph_rec.interface_header_id := g_interface_header_id;
Else
Select po_headers_interface_s.Nextval
Into lr_ph_rec.interface_header_id
From dual;
lb_ph_existed_flag := False;
-- Add By Jianghua at 2009/03/20 10:50
g_interface_header_id := lr_ph_rec.interface_header_id;
End If;
End If;
If lr_pl_rec.interface_header_id IS Null And
lr_pl_rec.interface_line_id Is Null Then
lr_pl_rec.interface_header_id := g_interface_header_id;
Select po_lines_interface_s.Nextval
Into lr_pl_rec.interface_line_id
From dual;
End If;
If lr_pd_rec.interface_header_id Is Null And
lr_pd_rec.interface_line_id Is Null And
lr_pd_rec.Interface_Distribution_Id Is Null Then
lr_pd_rec.interface_header_id := g_interface_header_id;
Select po_lines_interface_s.Currval,
po_distributions_interface_s.Nextval
Into lr_pd_rec.interface_line_id,
lr_pd_rec.Interface_Distribution_Id
From dual;
End If;
If Not lb_ph_existed_flag Then
Begin
Insert Into po_headers_interface Values lr_ph_rec;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || 'po_headers_interface接口表插入异常;' ||
substr(SQLERRM, 1, 20);
lv_status := 'E';
End;
End If;
IF lv_status = 'E' THEN
raise_application_error(-20000, lv_err_msg);
END IF;
Begin
Insert Into po_lines_interface Values lr_pl_rec;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || 'po_lines_interface接口表插入异常;' ||
substr(SQLERRM, 1, 20);
lv_status := 'E';
End;
IF lv_status = 'E' THEN
raise_application_error(-20000, lv_err_msg);
END IF;
Begin
Insert Into po_distributions_interface Values lr_pd_rec;
EXCEPTION
WHEN OTHERS THEN
lv_err_msg := lv_err_msg || 'po_distributions_interface接口表插入异常;' ||
substr(SQLERRM, 1, 20);
lv_status := 'E';
End;
IF lv_status = 'E' THEN
raise_application_error(-20000, lv_err_msg);
END IF;
g_order_num := lr_ph_rec.document_num;
g_org_id := lr_ph_rec.org_id;
g_vendor_id := lr_ph_rec.vendor_id;
g_vendor_site_id := lr_ph_rec.vendor_site_id;
-- Remark By Jianghua at 2009/03/20 10:50
-- g_interface_header_id := lr_ph_rec.interface_header_id;
/*-- Step 3: Submit request to process interface ------------------------------*/
--Can not return request id to Web ADI. Hold.
IF g_request_id IS NULL AND p_submit_request_flag = 'Y' THEN
g_request_id := fnd_request.submit_request(application => 'PO',
program => 'POXPOPDOI',
description => 'STANDARD',
argument1 => null,
argument2 => 'STANDARD',
argument3 => null,
argument4 => 'N',
argument5 => null,
argument6 => 'APPROVED',
argument7 => null,
argument8 => Null,
argument9 => Null,
argument10 => null);
IF g_request_id = 0 THEN
lv_err_msg := lv_err_msg || '提交系统请求发生异常;' ||
substr(SQLERRM, 1, 200);
--lv_status := 'E';
raise_application_error(-20000, lv_err_msg);
END IF;
END IF;
--< This part of the code should be added >-------------------------
msgencoded := fnd_message.get_encoded();
msgencodedlen := length(msgencoded);
msgnameloc := instr(msgencoded, chr(0));
msgapp := substr(msgencoded, 1, msgnameloc - 1);
msgencoded := substr(msgencoded, msgnameloc + 1, msgencodedlen);
msgencodedlen := length(msgencoded);
msgtextloc := instr(msgencoded, chr(0));
msgname := substr(msgencoded, 1, msgtextloc - 1);
IF (msgname <> 'CONC-SINGLE PENDING REQUEST') THEN
fnd_message.set_name(msgapp, msgname);
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20000, SQLERRM);
END open_po_import;
END CUX_PO_DOC_IMP_PKG;
标签：default,接口,订单,varchar2,lr,EBS,rec,null,id From： https://www.cnblogs.com/ivenlin/p/18120334