create or replace procedure APSP_AP_AUTORUN is get_ap_info_contract_ varchar2(10);
get_ap_info_company_ varchar2(10);
get_ap_info_cust_id_ varchar2(10);
get_ap_info_invoice_type_ varchar2(10);
get_ap_info_invoice_no_ varchar2(10);
get_order_no_order_no_ varchar(20);
get_order_no_part_no_ varchar(10);
get_order_info_order_info_ varchar(2000);
cursor get_ap_info is
select company,apsp_storedprocedure_lists.Get_Contract(company) as contract,customer_id,ledger_item_series_id,ledger_item_id
from apsp_ap_autonotice_tab
order by company,customer_id,dute_date asc;
cursor get_order_no(Contract_ varchar2,Cust_ID_ varchar2,Invoice_Type_ varchar2,Invoice_no_ varchar2) is
select a.Contract,b.identity,a.series_id,a.invoice_no,b.c1 as order_no,b.c5 as part_no
from Customer_Order_Inv_Head a,INVOICE_ITEM_TAB b
where a.company = b.company and a.invoice_id = b.invoice_id
and a.party_type = b.party_type and a.contract=Contract_
and a.identity = Cust_ID_ and a.series_id=Invoice_Type_ and a.invoice_no=Invoice_no_;
cursor get_order_info(Contract_ varchar2,Part_no_ varchar2,Order_no_ varchar2) is
select '订单(' || ORDER_NO || ',' || Description || ') 发货时间:' || to_char(Date_Applied,'yyyy-mm-dd') || ',数量:' || Quantity_to_ship || ' ' || sales_unit_meas as Order_Info
from apsp_salecount_tab
where contract=Contract_ and part_no = Part_no_ and order_no = Order_no_
order by Date_Applied asc;
begin for get_ap_info_row in get_ap_info loop
get_ap_info_company_ := get_ap_info_row.company;
get_ap_info_contract_ := get_ap_info_row.contract;
get_ap_info_cust_id_ := get_ap_info_row.customer_id;
get_ap_info_invoice_type_ := get_ap_info_row.ledger_item_series_id;
get_ap_info_invoice_no_ := get_ap_info_row.ledger_item_id;
for get_order_no_row in get_order_no(get_ap_info_contract_,get_ap_info_cust_id_,get_ap_info_invoice_type_,get_ap_info_invoice_no_) loop
get_order_no_order_no_ := get_order_no_row.invoice_no;
get_order_no_part_no_ := get_order_no_row.part_no;
for get_order_info_row in get_order_info(get_ap_info_contract_,get_order_no_part_no_,get_order_no_order_no_) loop
get_order_info_order_info_ := get_order_info_row.order_info;
update apsp_ap_autonotice_tab set description = get_order_info_order_info_
where company = get_ap_info_company_
and customer_id = get_ap_info_cust_id_
and ledger_item_series_id = get_ap_info_invoice_type_
and ledger_item_id = get_ap_info_invoice_no_;
end loop;
end loop;
end loop;
end APSP_AP_AUTORUN;
那句Update提示有错,无法更新到服务器上,请各位大哥帮忙搞掂它吧,分不够再加,十万火急啊,在线等~~!!!
错误提示:
Compilation errors for PROCEDURE IFSAPP.APSP_AP_AUTORUNError: PLS-00560: character set mismatch
Line: 105
Text: update apsp_ap_autonotice_tab set DESCRIPTION = get_order_info_order_info_Error: PL/SQL: SQL Statement ignored
Line: 105
Text: update apsp_ap_autonotice_tab set DESCRIPTION = get_order_info_order_info_
get_ap_info_company_ varchar2(10);
get_ap_info_cust_id_ varchar2(10);
get_ap_info_invoice_type_ varchar2(10);
get_ap_info_invoice_no_ varchar2(10);
get_order_no_order_no_ varchar(20);
get_order_no_part_no_ varchar(10);
get_order_info_order_info_ varchar(2000);
cursor get_ap_info is
select company,apsp_storedprocedure_lists.Get_Contract(company) as contract,customer_id,ledger_item_series_id,ledger_item_id
from apsp_ap_autonotice_tab
order by company,customer_id,dute_date asc;
cursor get_order_no(Contract_ varchar2,Cust_ID_ varchar2,Invoice_Type_ varchar2,Invoice_no_ varchar2) is
select a.Contract,b.identity,a.series_id,a.invoice_no,b.c1 as order_no,b.c5 as part_no
from Customer_Order_Inv_Head a,INVOICE_ITEM_TAB b
where a.company = b.company and a.invoice_id = b.invoice_id
and a.party_type = b.party_type and a.contract=Contract_
and a.identity = Cust_ID_ and a.series_id=Invoice_Type_ and a.invoice_no=Invoice_no_;
cursor get_order_info(Contract_ varchar2,Part_no_ varchar2,Order_no_ varchar2) is
select '订单(' || ORDER_NO || ',' || Description || ') 发货时间:' || to_char(Date_Applied,'yyyy-mm-dd') || ',数量:' || Quantity_to_ship || ' ' || sales_unit_meas as Order_Info
from apsp_salecount_tab
where contract=Contract_ and part_no = Part_no_ and order_no = Order_no_
order by Date_Applied asc;
begin for get_ap_info_row in get_ap_info loop
get_ap_info_company_ := get_ap_info_row.company;
get_ap_info_contract_ := get_ap_info_row.contract;
get_ap_info_cust_id_ := get_ap_info_row.customer_id;
get_ap_info_invoice_type_ := get_ap_info_row.ledger_item_series_id;
get_ap_info_invoice_no_ := get_ap_info_row.ledger_item_id;
for get_order_no_row in get_order_no(get_ap_info_contract_,get_ap_info_cust_id_,get_ap_info_invoice_type_,get_ap_info_invoice_no_) loop
get_order_no_order_no_ := get_order_no_row.invoice_no;
get_order_no_part_no_ := get_order_no_row.part_no;
for get_order_info_row in get_order_info(get_ap_info_contract_,get_order_no_part_no_,get_order_no_order_no_) loop
get_order_info_order_info_ := get_order_info_row.order_info;
update apsp_ap_autonotice_tab set description = get_order_info_order_info_
where company = get_ap_info_company_
and customer_id = get_ap_info_cust_id_
and ledger_item_series_id = get_ap_info_invoice_type_
and ledger_item_id = get_ap_info_invoice_no_;
end loop;
end loop;
end loop;
end APSP_AP_AUTORUN;
那句Update提示有错,无法更新到服务器上,请各位大哥帮忙搞掂它吧,分不够再加,十万火急啊,在线等~~!!!
错误提示:
Compilation errors for PROCEDURE IFSAPP.APSP_AP_AUTORUNError: PLS-00560: character set mismatch
Line: 105
Text: update apsp_ap_autonotice_tab set DESCRIPTION = get_order_info_order_info_Error: PL/SQL: SQL Statement ignored
Line: 105
Text: update apsp_ap_autonotice_tab set DESCRIPTION = get_order_info_order_info_
Cause: An expression was used that has the wrong character set for this context.
Action: Adjust the expression, using TRANSLATE(... USING ...) or by rethinking the logic.