程序包头
create or replace package cux_ws_scs_op_pba_pub is
/*===============================================
* ===============================================
* PROGRAM NAME:
* cux_ws_op_vendor_scs_pub
* DESCRIPTION:
*
* 接口2：SCS回传PBA给EBS
* HISTORY:
* 1.00 2020-12-30 iven.lin Creation
*
* ==============================================*/
type t_header_tbl is table of cux.cux_pba_head_iface%rowtype index by binary_integer;
type t_line_tbl is table of cux.cux_pba_line_iface%rowtype index by binary_integer;
g_transaction_id number; --记录调用历史记录ID
--价目表启用组织
procedure insert_ga(org_id number,
purchasing_org number,
p_po_header_id number,
p_vendor_site_id number,
p_create_id number);
--设置物料属性---
procedure set_po_item(p_io_id number,
p_item_id number,
p_jiagong_qty number,
p_mqt_qty number,
p_message out varchar2);
procedure trx_parse_json(p_json_clob in clob,
x_header_tbl out t_header_tbl,
x_line_tbl out t_line_tbl,
x_return_code out varchar2,
x_return_mesg out varchar2);
procedure trx_pba_parse(p_transaction_id in number,
p_request_data in clob,
p_bacth_number in varchar2,
x_return_code out varchar2,
x_return_mesg out varchar2,
x_response_data out clob);
procedure trx_pba_import(p_transaction_id in number,
x_return_code out varchar2,
x_return_mesg out varchar2,
x_response_data out clob);
end cux_ws_scs_op_pba_pub;
程序包体：
create or replace package body cux_ws_scs_op_pba_pub as
g_output varchar2(15) := 'OUTPUT'; --'DB';--'OUTPUT';
g_user_id number := fnd_global.user_id;
g_pkg_name constant varchar2(30) := 'CUX_WS_SCS_PBA_PUB';
g_debug varchar2(1) := nvl(fnd_profile.value('CUX_WS_ALLOW_TRANSFER_LOG'),
'Y');
g_success constant varchar2(8) := cux_ws_common_utl.g_ret_sts_success;
g_error constant varchar2(8) := cux_ws_common_utl.g_ret_sts_exception;
g_app_name fnd_application.application_short_name%type := cux_fnd_api.g_app_name;
g_iface_param cux_ws_common_utl.cux_ws_param;
g_err_xml constant varchar2(8) := cux_ws_common_utl.g_err_xml;
g_sysdate date := sysdate;
g_login_id number := fnd_global.login_id;
G_bacth_number varchar2(50);
g_request_id number;
g_iface_code varchar2(200) := 'CUX_WS_SCS_PBA_SCS_PUB'; ---定义调用接口代码
--g_transaction_id number; --记录调用历史记录ID
procedure write_to_clob(p_buffer in varchar2, x_clob in out clob) is
v_writing_position number := 0;
v_amount number;
begin
v_amount := length(p_buffer);
v_writing_position := dbms_lob.getlength(x_clob) + 1;
dbms_lob.write(x_clob, v_amount, v_writing_position, p_buffer);
end;
--- 启用多组织
procedure insert_ga(org_id number,
purchasing_org number,
p_po_header_id number,
p_vendor_site_id number,
p_create_id number) is
begin
insert into po_ga_org_assignments
(org_assignment_id, --序列
po_header_id, --PO头ID
organization_id, --请求组织
purchasing_org_id, --采购组织
enabled_flag, --状态
vendor_site_id, ---地址 -6
created_by,
last_updated_by,
creation_date,
last_update_date,
last_update_login)
values
(po_ga_org_assignments_s.nextval,
p_po_header_id,
org_id,
purchasing_org,
'Y',
p_vendor_site_id,
p_create_id,
p_create_id,
sysdate,
sysdate,
-1);
/* insert into po_ga_org_assignments
(org_assignment_id,
po_header_id,
organization_id,
purchasing_org_id,
enabled_flag,
vendor_site_id,
created_by,
last_updated_by,
creation_date,
last_update_date,
last_update_login)
select po_ga_org_assignments_s.nextval,
h.po_header_id,
org_id,
purchasing_org,
'Y',
h.vendor_site_id,
-1,
-1,
sysdate,
sysdate,
-1
from po_headers_all h
where h.org_id = purchasing_org
and h.type_lookup_code = 'BLANKET'
and h.global_agreement_flag = 'Y'
and not exists (select 'x'
from po_ga_org_assignments s
where s.po_header_id = h.po_header_id
and s.organization_id = org_id);*/
exception
when others then
null;
end;
--- 物料属性设置---
procedure set_po_item(p_io_id number,
p_item_id number,
p_jiagong_qty number,
p_mqt_qty number,
p_message out varchar2) is
l_item_rec inv_item_grp.item_rec_type;
x_item_rec inv_item_grp.item_rec_type;
x_error_tbl inv_item_grp.error_tbl_type;
x_return_status varchar2(1);
x_error_message varchar2(2000) := null;
begin
l_Item_Rec.Inventory_Item_Id := p_item_id;
l_Item_Rec.Organization_Id := p_io_id; --122;
l_Item_Rec.FULL_LEAD_TIME := p_jiagong_qty;
l_Item_Rec.FIXED_LOT_MULTIPLIER := p_mqt_qty;
Inv_Item_Grp.Update_Item(p_Commit => Fnd_Api.g_False,
p_Item_Rec => l_Item_Rec,
x_Item_Rec => x_Item_Rec,
x_Return_Status => x_Return_Status,
x_Error_Tbl => x_Error_Tbl);
x_error_message := null;
IF x_Return_Status <> Fnd_Api.g_Ret_Sts_Success THEN
-- ROLLBACK;
FOR i IN 1 .. x_Error_Tbl.Count LOOP
x_error_message := nvl(x_error_message, '') || x_Error_Tbl(i)
.Message_Text;
END LOOP;
IF (x_Return_Status = Fnd_Api.g_Ret_Sts_Unexp_Error) THEN
Dbms_Output.Put_Line('g_Exc_Unexpected_Error:' || x_Return_Status);
ELSIF (x_Return_Status = Fnd_Api.g_Ret_Sts_Error) THEN
Dbms_Output.Put_Line('g_Exc_Error:' || x_Return_Status);
END IF;
p_message := x_error_message;
ELSE
commit;
p_message := null;
--Dbms_Output.Put_Line('ok:' || x_Item_Rec.Item_Number);
END IF;
end;
function replace_string_json(p_string in varchar2) return varchar2 is
l_temp varchar2(30000);
begin
l_temp := p_string;
l_temp := replace(l_temp, '\', '\\');
l_temp := replace(l_temp, '''', '\''');
l_temp := replace(l_temp, '"', '\"');
return l_temp;
end replace_string_json;
PROCEDURE Log(i_Chr_Message IN VARCHAR2) IS
BEGIN
Fnd_File.Put_Line(Fnd_File.Log, i_Chr_Message);
--dbms_output.put_line(i_chr_message);
END;
procedure trx_parse_json(p_json_clob in clob,
x_header_tbl out t_header_tbl,
x_line_tbl out t_line_tbl,
x_return_code out varchar2,
x_return_mesg out varchar2) is
tempobj json;
temparray json_list;
level_0 json_list;
level_1 json_list;
level_2 json_list;
header_j json;
line_j json;
item_j json;
l_header_row CUX.CUX_PBA_HEAD_IFACE%rowtype;
l_line_row CUX.CUX_PBA_LINE_IFACE%rowtype;
l_header_tbl t_header_tbl;
l_line_tbl t_line_tbl;
l_line_count number;
l_line_item_count number;
l_req_data clob;
h_index number;
my_clob clob;
l_msg varchar2(1000);
begin
x_return_code := fnd_api.g_ret_sts_success;
l_header_tbl.delete;
l_line_tbl.delete;
----scs---
-- my_clob :='W3siT3JnQ29kZSI6ICJPVV8xMDEiLCJQcmljZUNvZGUiOiAiSk1CNjY2IiwiU3VwcGxpZXJDb2RlIjogIkVQUzIwMjAxIiwiU2l0ZU5hbWUiOiAiMCIsIkN1cnJlbmN5Q29kZSI6ICJKUFkiLCJNYXRlcmlhbENvZGUiOiAiMjAwMDEwMjc2MDA0MTEyMyIsIk5vdFRheFByaWNlIjogIjEyMDAiLCJUYXhQcmljZSI6ICIxMzU2IiwiSW52YWxpZERhdGUiOiAiMjAyMS0wNy0wOCIsIlJlbWFya3MiOiAiIiwiUHJpY2VUeXBlIjogIuS4gOWPo+S7tyIsIlByaWNlU3RhdHVzIjogIjEiLCJNYXJrZXRQcmljZSI6ICIwIiwiUHJvY2Vzc2luZ0FkdmFuY2VEYXlzIjogIjAiLCJNaW5QYWNrYWdlTnVtYmVyIjogIjAiLCJTQ1NVbmlxdWVDb2RlIjogIjQ0MyIsIkxhZGRlclRhYmxlIjogW119XQ==';
-- my_clob := 'W3siT3JnQ29kZSI6ICJPVV8xMDEiLCJQcmljZUNvZGUiOiAiUFBMMjAxNzA4MjMwMUJDIiwiU3VwcGxpZXJDb2RlIjogIkQxNDciLCJTaXRlTmFtZSI6ICLmnZDmlpnph4fotK0iLCJDdXJyZW5jeUNvZGUiOiAiQ05ZIiwiTWF0ZXJpYWxDb2RlIjogIjYxNDAwMDAwMzM1MTEwMDAiLCJOb3RUYXhQcmljZSI6ICIwLjMiLCJUYXhQcmljZSI6ICIwLjMzOSIsIkludmFsaWREYXRlIjogIjIwMjEtMDQtMTAiLCJSZW1hcmtzIjogIm5vc3BlYyIsIlByaWNlVHlwZSI6ICLpmLbmoq/ku7ciLCJQcmljZVN0YXR1cyI6ICIxIiwiTWFya2V0UHJpY2UiOiAiMCIsIlByb2Nlc3NpbmdBZHZhbmNlRGF5cyI6ICIwIiwiTWluUGFja2FnZU51bWJlciI6ICIxMDAiLCJTQ1NVbmlxdWVDb2RlIjogIjI1NDQzOCIsIkxhZGRlclRhYmxlIjogW3siTGFkZGVyRGF0ZSI6ICIyMDIxLTA0LTEwIiwiTGFkZGVyTnVtYmVyIjogIjUwMDAiLCJMYWRkZXJOb3RUYXhQcmljZSI6ICIwLjMifSx7IkxhZGRlckRhdGUiOiAiMjAyMS0wNC0xMCIsIkxhZGRlck51bWJlciI6ICI5OTk5OTk5OSIsIkxhZGRlck5vdFRheFByaWNlIjogIjAuMiJ9XX1d';
-- l_req_data := cux_ws_common_utl.decode_base64(my_clob);
-- dbms_output.put_line(l_req_data);
temparray := json_list(p_json_clob);
l_line_count := 0;
l_line_item_count := 0;
h_index := 1;
l_msg := '11';
--最外层的[],有几个header就有几个
for i in 1 .. temparray.count loop
header_j := json(temparray.get(i));
l_header_row.s_transaction_id := g_transaction_id;
l_header_row.OrgCode := json_ext.get_string(header_j,
'OrgCode');
l_header_row.PriceCode := json_ext.get_string(header_j,
'PriceCode');
l_header_row.suppliercode := json_ext.get_string(header_j,
'SupplierCode');
l_header_row.SiteName := json_ext.get_string(header_j,
'SiteName');
l_header_row.CurrencyCode := json_ext.get_string(header_j,
'CurrencyCode');
l_header_row.MaterialCode := json_ext.get_string(header_j,
'MaterialCode');
l_header_row.NotTaxPrice := json_ext.get_string(header_j,
'NotTaxPrice');
l_header_row.InvalidDate := json_ext.get_string(header_j,
'InvalidDate');
/*l_header_row.Remarks := json_ext.get_string(header_j,
'Remarks');*/
l_header_row.Remarks := substr(json_ext.get_string(header_j,
'Remarks'),
0,
75);
l_header_row.PriceType := json_ext.get_string(header_j,
'PriceType');
l_header_row.PriceStatus := json_ext.get_string(header_j,
'PriceStatus');
l_header_row.MarketPrice := json_ext.get_string(header_j,
'MarketPrice');
l_header_row.ProcessingAdvanceDays := json_ext.get_string(header_j,
'ProcessingAdvanceDays');
l_header_row.MinPackageNumber := json_ext.get_string(header_j,
'MinPackageNumber');
l_header_row.taxprice := json_ext.get_string(header_j,
'TaxPrice');
l_header_row.scsuniquecode := json_ext.get_string(header_j,
'SCSUniqueCode');
l_header_row.create_date := SYSDATE;
l_header_row.clientprice := json_ext.get_string(header_j,
'ClientPrice');
l_header_row.clientpricecurrency := json_ext.get_string(header_j,
'ClientPriceCurrency');
l_header_row.clientpriceremarks := json_ext.get_string(header_j,
'ClientPriceRemarks');
l_msg := '22';
--行循环
level_1 := json_list(header_j.get('LadderTable'));
----回退不写行信息
for k in 1 .. level_1.count loop
l_line_count := l_line_count + 1;
line_j := json(level_1.get(k));
l_line_row.s_transaction_id := g_transaction_id;
l_line_row.LadderDate := json_ext.get_string(line_j,
'LadderDate');
l_line_row.LadderNumber := json_ext.get_string(line_j,
'LadderNumber');
l_line_row.LadderNotTaxPrice := json_ext.get_string(line_j,
'LadderNotTaxPrice');
l_line_row.pricecode := json_ext.get_string(header_j,
'PriceCode');
l_line_row.orgcode := json_ext.get_string(header_j,
'OrgCode');
l_line_row.mindou_qty := json_ext.get_string(header_j,
'ProcessingAdvanceDays');
l_line_row.mqt_qty := json_ext.get_string(header_j,
'MinPackageNumber');
l_line_row.materialcode := json_ext.get_string(header_j,
'MaterialCode');
l_line_row.laddertaxprice := json_ext.get_string(line_j,
'LadderTaxPrice');
l_line_tbl(l_line_count) := l_line_row;
l_msg := '33';
--COMMIT;
end loop;
--END LOOP;
l_header_tbl(i) := l_header_row;
end loop;
x_header_tbl := l_header_tbl;
x_line_tbl := l_line_tbl;
exception
when others then
x_return_code := fnd_api.g_ret_sts_error;
x_return_mesg := sqlerrm || '-报文解析失败--' || l_msg;
end;
procedure trx_pba_parse(p_transaction_id in number,
p_request_data in clob,
p_bacth_number in varchar2,
x_return_code out varchar2,
x_return_mesg out varchar2,
x_response_data out clob) is
l_api_name varchar2(20) := 'trx_pba_parse';
l_header_tbl t_header_tbl;
l_line_tbl t_line_tbl;
-- l_req_data clob; --request json data
l_json_return_code varchar2(30);
begin
x_return_code := g_success;
g_transaction_id := p_transaction_id;
G_bacth_number := p_bacth_number;
trx_parse_json(p_json_clob => p_request_data,
x_header_tbl => l_header_tbl,
x_line_tbl => l_line_tbl,
x_return_code => l_json_return_code,
x_return_mesg => x_return_mesg);
if l_json_return_code = fnd_api.g_ret_sts_success then
for i in 1 .. l_header_tbl.count loop
insert into cux.CUX_PBA_head_SCS_IFACE values l_header_tbl (i);
end loop;
for j in 1 .. l_line_tbl.count loop
insert into CUX.CUX_PBA_LINE_SCS_IFACE values l_line_tbl (j);
end loop;
COMMIT;
else
raise fnd_api.g_exc_error;
end if;
exception
when fnd_api.g_exc_error then
x_return_code := g_error;
return;
when others then
x_return_code := g_error;
x_return_mesg := g_app_name || '.' || l_api_name || ':' || sqlerrm;
end;
procedure trx_pba_import(p_transaction_id in number,
x_return_code out varchar2,
x_return_mesg out varchar2,
x_response_data out clob) is
--This provides batch id which is used to run particular set of data
l_batch_id po_headers_interface.batch_id%type;
l_org_id po_headers_interface.org_id%type;
l_agent_id po_headers_interface.agent_id%type;
l_vendor_id po_headers_interface.vendor_id%type;
l_vendor_site_id po_headers_interface.vendor_site_id%type;
l_ship_to_location_id po_headers_interface.ship_to_location_id%type;
l_bill_to_location_id po_headers_interface.bill_to_location_id%type;
---------------------------------------------------------------------------------
--Line level information
---------------------------------------------------------------------------------
l_line_type po_lines_interface.line_type%type := '货物';
l_item_id po_lines_interface.item_id%type;
l_inv_org_id po_lines_interface.organization_id%type;
l_uom_code po_lines_interface.uom_code%type;
l_unit_price po_lines_interface.unit_price%type;
l_sub_price number;
l_price_override po_line_locations_interface.price_override%type;
l_quantity po_line_locations_interface.quantity%type;
l_header_count number := 1;
l_line_count number := 1;
l_price_break_lookup_code varchar2(100);
l_organization_id number := 0;
--Specifies number of price breaks to be created per Line
l_prc_brk_count number := 1;
l_progress varchar2(100);
l_exist_line number;
l_action varchar2(20);
l_po_line_id number;
l_sum_tax number;
l_close_flag varchar2(10);
L_BOOLEAN BOOLEAN;
E_END EXCEPTION;
l_line_num number;
vendor_site_count number;
l_max_number number;
l_item_desc varchar2(300);
l_break_flag varchar2(10);
l_uom varchar2(10);
l_pb_line_num number;
l_start_date date;
l_end_date date;
v_return_status varchar2(2000);
errbuf varchar2(20000);
v_po_header_ids number;
v_return_status_s varchar2(100);
l_exception_msg_s varchar2(1000);
pp_flag number;
v_request_id number;
--- 获取需要更新的PBA数据---
cursor c_head_update(v_ous_code varchar2,
v_po_number varchar2,
v_sit_name varchar2,
v_vendor_name varchar2,
v_p_transaction_id number) is
select c.orgcode,
c.pricecode,
c.sitename,
c.currencycode,
c.materialcode,
c.nottaxprice,
c.invaliddate,
c.remarks,
c.pricetype,
c.pricestatus,
c.marketprice,
c.minpackagenumber,
c.processingadvancedays,
c.taxprice,
c.clientprice,
c.clientpricecurrency,
c.clientpriceremarks
from cux.CUX_PBA_head_SCS_IFACE c
where c.s_transaction_id = v_p_transaction_id
and c.orgcode = v_ous_code
and c.pricecode = v_po_number
and c.sitename = v_sit_name
and c.suppliercode = v_vendor_name;
p_head_id number;
cursor cur_po_line(v_line_org varchar2,
v_po_number varchar2,
v_sit_name varchar2,
v_curr_code varchar2,
v_sup_vendor varchar2,
l_p_transaction_id number) is
select c.orgcode,
c.pricecode,
c.sitename,
c.currencycode,
c.materialcode,
c.nottaxprice,
c.invaliddate,
c.remarks,
c.pricetype,
c.pricestatus,
c.marketprice,
c.minpackagenumber,
c.processingadvancedays,
c.taxprice,
c.SUPPLIERCODE,
c.clientprice,
c.clientpricecurrency,
c.clientpriceremarks
from cux.CUX_PBA_head_SCS_IFACE c
where c.s_transaction_id = l_p_transaction_id
and c.orgcode = v_line_org
and c.pricecode = v_po_number
and c.sitename = v_sit_name
and c.currencycode = v_curr_code
and c.suppliercode = v_sup_vendor;
cursor cur_po_ll(vv_line_org varchar2,
vv_po_number varchar2,
vv_item_code varchar2,
vv_p_transaction_id number) is
select cl.ladderdate,
cl.laddernumber,
cl.laddernottaxprice,
cl.pricecode,
cl.materialcode,
cl.orgcode,
cl.moudouqty,
cl.mqtqty,
cl.laddertaxprice
from cux.cux_pba_line_scs_iface cl
where cl.s_transaction_id = vv_p_transaction_id
and cl.orgcode = vv_line_org
and cl.pricecode = vv_po_number
and cl.materialcode = vv_item_code
order by to_number(cl.laddernumber) asc;
p_end_qty number;
p_ATTRIBUTE14 number;
p_PRICE_OVERRIDE number;
p_end_date date;
max_qty number;
cursor cur_head_all(ch_p_transaction_id number) is
select c.orgcode,
c.pricecode,
c.sitename,
c.currencycode,
max(c.remarks) remarks,
c.suppliercode,
max(c.materialcode) item_code
from cux.CUX_PBA_head_SCS_IFACE c
where c.s_transaction_id = ch_p_transaction_id
group by c.orgcode,
c.pricecode,
c.sitename,
c.currencycode,
c.suppliercode;
p_exists_flag number;
p_uom varchar2(300);
p_uom_unit varchar2(300);
p_item_desc varchar2(300);
l_responsed_data CLOB;
l_report_json CLOB;
l_rep_value CLOB;
p_msg varchar2(150);
APP_EXP EXCEPTION; --自定义异常
g_global_flag varchar2(10); ---是否是全局变量 N,Y
----校验参数---------------
v_validate varchar2(30000); --- 错误的信息汇总
v_ou_id number; --- 验证OU_ID的参数
v_pass varchar2(1) := 'T'; --校验是不是所有的数据都符合EBS格式
v_vendor_id number;
v_vendor_site_id number;
-------匹配校验------------------
p_validate varchar2(30000); --匹配是校验信息汇总
p_pass varchar2(1) := 'T';
p_org_organization_id number;
p_item_id number;
p_item_message varchar2(30000); -- 更新物料异常信息
f_item_id number;
l_line_ids number;
f_flag number;
v_status varchar2(1000);
f_count number;
v_date_temp date;
cursor cur_locations_all(pp_po_num varchar2,
pp_item_num varchar2,
ppl_transaction_id number) is
select *
from CUX_PBA_LINE_SCS_IFACE x
where x.s_transaction_id = ppl_transaction_id --12345678;
and x.pricecode = pp_po_num
and x.materialcode = pp_item_num;
----还原数据-----
cursor cur_line_back(c_transaction_id varchar2) is
select lbk.*
from cux.cux_po_line_scs_back lbk
where lbk.p_transaction_id = c_transaction_id;
g_po_number varchar2(1000);
cursor cur_valdate_line(vv_line_org varchar2,
vv_po_number varchar2,
vv_sit_name varchar2,
vv_curr_code varchar2,
vv_sup_vendor varchar2,
vval_transaction_id number) is
select c.orgcode,
c.pricecode,
c.sitename,
c.currencycode,
c.materialcode,
c.nottaxprice,
c.invaliddate,
c.remarks,
c.pricetype,
c.pricestatus,
c.marketprice,
c.minpackagenumber,
c.processingadvancedays,
c.taxprice,
c.SUPPLIERCODE,
c.scsuniquecode
from cux.CUX_PBA_head_SCS_IFACE c
where c.s_transaction_id = vval_transaction_id
and c.orgcode = vv_line_org
and c.pricecode = vv_po_number
and c.sitename = vv_sit_name
and c.currencycode = vv_curr_code
and c.suppliercode = vv_sup_vendor;
v_po_count number;
v_exits_flag varchar2(100);
v_po_num_flag varchar2(100);
v_head_stuts varchar2(1000);
v_head_info varchar2(1000);
g_SCSUniqueCode varchar2(30000);
t_scs_flag varchar2(10);
p_inter_head_id number;
p_inter_line_id number;
g_batch_id number;
p_api_info varchar2(30000);
p_line_count number;
p_jie_count number; ---增加价格类型是阶梯价，没有传送阶梯段的判断 2021-11-20
p_fen_end_date date; --临时价格失效
P_PRICE_BREAK_LOOKUP_CODE varchar2(1000);
begin
-- 开始批量校验数据-----
v_pass := 'T';
g_po_number := null;
v_po_count := 0;
v_po_num_flag := null;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,1,SYSDATE);
for c_validate in cur_head_all(p_transaction_id) loop
--- 校验公司组织是否有效---
v_po_count := v_po_count + 1;
v_po_num_flag := c_validate.pricecode;
begin
select ous.organization_id
into v_ou_id
from hr_operating_units ous
where ous.short_code = c_validate.orgcode;
exception
when others then
v_ou_id := 0;
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',' || '公司编码：' ||
c_validate.orgcode || '无效';
end;
----校验同一个价目表同一个组织只能处理--
begin
select ci.p_status
into v_exits_flag
from cux.cux_scs_po_num_index ci
where ci.p_org_id = v_ou_id
and ci.p_po_num = c_validate.pricecode;
exception
when no_data_found then
v_exits_flag := 'N';
end;
t_scs_flag := 'T';
if v_exits_flag = 'N' then
insert into cux.cux_scs_po_num_index
(p_org_id, p_po_num, p_status, create_date, last_date)
values
(v_ou_id, c_validate.pricecode, 'T', sysdate, sysdate);
elsif v_exits_flag = 'T' then
v_pass := 'F';
t_scs_flag := 'N';
v_validate := nvl(v_validate, '') || ',' || '公司编码：' ||
c_validate.orgcode || ',的价目表:' ||
c_validate.pricecode || '正在SCS系统处理中,请稍后在同步EBS....';
null;
elsif v_exits_flag = 'F' then
update cux.cux_scs_po_num_index px
set px.p_status = 'T', px.last_date = sysdate
where px.p_org_id = v_ou_id
and px.p_po_num = c_validate.pricecode;
end if;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,2,SYSDATE);
v_head_stuts := null;
v_head_info := null;
p_fen_end_date := sysdate + 1;
IF t_scs_flag = 'T' then
begin
select pha.AUTHORIZATION_STATUS, nvl(pha.END_DATE, sysdate + 1)
into v_head_stuts, p_fen_end_date
from po_headers_all pha
where pha.SEGMENT1 = c_validate.pricecode
and pha.ORG_ID = v_ou_id;
exception
when no_data_found then
v_head_stuts := 'NEW';
p_fen_end_date := sysdate + 1;
end;
if p_fen_end_date < sysdate then
v_pass := 'F';
v_validate := nvl(v_validate, '') || '价目表:' ||
c_validate.pricecode || '临时价格失效';
end if;
IF v_head_stuts = 'NEW' THEN
NULL;
ELSE
if v_head_stuts <> 'APPROVED' THEN
v_pass := 'F';
if v_head_stuts = 'IN PROCESS' then
v_head_info := '处理中,请到EBS处理';
elsif v_head_stuts = 'REQUIRES REAPPROVAL' then
v_head_info := '要求重新审批,请到EBS处理';
elsif v_head_stuts = 'INCOMPLETE' then
v_head_info := '未完成,请到EBS处理';
else
v_head_info := '未知无效状态,请到EBS处理';
end if;
v_validate := nvl(v_validate, '') || '价目表:' ||
c_validate.pricecode || '状态' || v_head_info;
end if;
END IF;
end if;
---校验供应商是否有效---
begin
select pov.vendor_id
into v_vendor_id
from po_vendors pov
where pov.segment1 = c_validate.SupplierCode --cur_heads_api.SupplierCode
and sysdate between nvl(pov.start_date_active, sysdate - 1) and
nvl(pov.end_date_active, sysdate + 1);
exception
when others then
v_vendor_id := 0;
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',' || '供应商编码：' ||
c_validate.SupplierCode || '无效';
end;
--- 校验供应商地址------
begin
select pvs.vendor_site_id
into v_vendor_site_id
from po_vendor_sites_all pvs
where pvs.vendor_id = v_vendor_id
and pvs.org_id = v_ou_id
and pvs.vendor_site_code = c_validate.sitename; --cur_heads_api.sitename;
exception
when others then
v_vendor_site_id := 0;
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',供应编码:' ||
c_validate.SupplierCode || ',' || '公司组织:' ||
c_validate.orgcode || '对应的供应商地址' ||
c_validate.sitename || '无效';
end;
if v_vendor_site_id > 0 then
select count(1)
into vendor_site_count
from po_vendor_sites_all psa
where psa.VENDOR_SITE_ID = v_vendor_site_id
and nvl(psa.inactive_date, sysdate) >= sysdate;
if vendor_site_count = 0 then
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',供应编码:' ||
c_validate.SupplierCode || ',' || '公司组织:' ||
c_validate.orgcode || '对应的供应商地址' ||
c_validate.sitename || '已经失效';
end if;
end if;
--- 校验物料组织是否存在------
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,3,SYSDATE);
g_SCSUniqueCode := null;
for c_date_item in cur_valdate_line(c_validate.orgcode,
c_validate.pricecode,
c_validate.sitename,
c_validate.currencycode,
c_validate.suppliercode,
p_transaction_id) loop
begin
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,4,SYSDATE);
if g_SCSUniqueCode is null then
g_SCSUniqueCode := '"' || c_date_item.scsuniquecode || '"';
else
g_SCSUniqueCode := g_SCSUniqueCode || ',' || '"' ||
c_date_item.scsuniquecode || '"';
end if;
select msi.INVENTORY_ITEM_ID
into f_item_id
from mtl_system_items_b msi
where msi.ORGANIZATION_ID = 122
/*(select org.ORGANIZATION_ID
from org_organization_definitions org
where org.OPERATING_UNIT = v_ou_id)*/
and msi.SEGMENT1 = c_date_item.materialcode;
exception
when others then
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',' || '公司编码:' ||
c_date_item.orgcode || ',' || '不存在该物料:' ||
c_date_item.materialcode;
end;
--- 判断阶梯价起始数量
p_line_count := 0;
begin
select count(1)
into p_line_count
from CUX_PBA_LINE_SCS_IFACE x
where x.s_transaction_id = p_transaction_id --12345678;
and x.pricecode = c_validate.pricecode
and x.materialcode = c_date_item.materialcode
and nvl(x.laddernumber, 0) <= 0;
exception
when others then
p_line_count := 0;
end;
if p_line_count > 0 then
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',物料:' ||
c_date_item.materialcode || '阶梯价开始数量不能小于零';
end if;
if c_date_item.pricetype in ('阶梯价', '累计价') then
begin
select count(1)
into p_jie_count
from CUX_PBA_LINE_SCS_IFACE cpl
where cpl.s_transaction_id = p_transaction_id
and cpl.pricecode = c_validate.pricecode
and cpl.materialcode = c_date_item.materialcode;
exception
when others then
p_jie_count := 0;
end;
end if;
if p_jie_count <= 0 then
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',物料:' ||
c_date_item.materialcode || '类型为阶梯\累计的，报文必须包含阶梯价';
end if;
end loop;
end loop;
if v_po_count >= 2 then
v_pass := 'F';
v_validate := nvl(v_validate, '') || ',' ||
'一份报文不能同时存在两个价目表,请联系SCS处理!';
end if;
if instr(v_validate, '一份报文不能同时存在两个价目表') >= 1 then
v_validate := '一份报文不能同时存在两个价目表,请联系SCS处理!';
end if;
commit;
----结束批量校验数据------
if nvl(v_pass, 'T') = 'T' then
for c_all in cur_head_all(p_transaction_id) LOOP
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,5,SYSDATE);
select cux_po_import_batch_s.nextval into g_batch_id from dual;
---根据SCS 传入的报文OU_100 获取对应的OU组织ID
l_org_id := 0;
select ous.organization_id
into l_org_id
from hr_operating_units ous
where ous.short_code = c_all.orgcode;
/*select org.ORGANIZATION_ID
into p_org_organization_id
from org_organization_definitions org
where org.OPERATING_UNIT = l_org_id;*/
----香港组织统一为全局变量，其他组织非全局变量
if l_org_id = 101 then
g_global_flag := 'Y';
else
g_global_flag := 'N';
end if;
-- 判斷價目表是否存在
begin
select p.AGENT_ID, p.PO_HEADER_ID
into l_agent_id, p_head_id
from po_headers_all p
where p.SEGMENT1 = c_all.pricecode --cur_heads.segment1
and p.ORG_ID = l_org_id; --c_all.orgcode;
exception
when others then
l_agent_id := 0;
p_head_id := 0;
end;
/* select org.ORGANIZATION_ID
into l_organization_id
from org_organization_definitions org
where org.OPERATING_UNIT = l_org_id;*/
----- 对应更新的数据先后台更新------
if nvl(p_head_id, 0) <> 0 then
for ah in c_head_update(c_all.orgcode,
c_all.pricecode,
c_all.sitename,
c_all.SUPPLIERCODE,
p_transaction_id) loop
l_po_line_id := null;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,6,SYSDATE);
begin
select l.po_line_id
into l_po_line_id
from po_headers_all h,
po_lines_all l,
mtl_system_items_b msi
where h.SEGMENT1 = c_all.pricecode
and l.po_header_id = h.po_header_id
and nvl(l.cancel_flag, 'N') = 'N'
and nvl(l.closed_code, 'OPEN') = 'OPEN'
and msi.INVENTORY_ITEM_ID = l.ITEM_ID
and msi.ORGANIZATION_ID = 122 --l_organization_id --p_inv_id
and msi.SEGMENT1 = ah.MATERIALCODE
and l.org_id = l_org_id; --c_all.orgcode;
exception
when no_data_found then
l_po_line_id := null;
when Too_many_rows then
----当存在多条价目表时----
select a.PO_LINE_ID
into l_po_line_id
from (select l.line_num,
l.item_description,
l.PO_LINE_ID,
l.ITEM_ID
from po_lines_all l,
po_headers_all h,
mtl_system_items_b b
where h.segment1 = ah.pricecode
and l.po_header_id = h.po_header_id
and b.inventory_item_id = l.item_id
and ah.materialcode = b.segment1
and nvl(l.cancel_flag, 'N') = 'N'
and nvl(l.closed_code, 'OPEN') = 'OPEN'
and h.org_id = l_org_id --l_org_id
and b.ORGANIZATION_ID = 122 --l_organization_id
order by l.EXPIRATION_DATE desc) a
where rownum = 1;
when others then
l_po_line_id := null;
end;
----- UPDATE ----
if nvl(l_po_line_id, 0) <> 0 then
---先备份数据，后续API调用失败还原使用
insert into cux.cux_po_line_scs_back
select ls.market_price,
ls.unit_price,
ls.list_price_per_unit,
ls.expiration_date,
ls.ATTRIBUTE5,
ls.attribute8,
p_transaction_id,
sysdate,
l_po_line_id
from po_lines_all ls
where po_line_id = l_po_line_id;
--- 更新行最新到期日期，含税价，不含税价
if to_date(ah.invaliddate, 'yyyy-mm-dd') <= sysdate then
v_date_temp := to_date('2099-12-31', 'yyyy-mm-dd');
else
v_date_temp := to_date(ah.invaliddate, 'yyyy-mm-dd');
end if;
if ah.pricetype in ('累计价') then
P_PRICE_BREAK_LOOKUP_CODE := 'CUMULATIVE';
else
P_PRICE_BREAK_LOOKUP_CODE := 'NON CUMULATIVE';
end if;
update po_lines_all l
set l.market_price = ah.taxprice,
l.unit_price = ah.nottaxprice,
l.list_price_per_unit = ah.nottaxprice,
l.expiration_date = v_date_temp, /*to_date(ah.invaliddate,
'yyyy-mm-dd'),*/
l.last_update_date = sysdate,
l.ATTRIBUTE5 = ah.remarks,
l.attribute8 = ah.pricetype, --- 价格类型
l.ATTRIBUTE9 = ah.clientpriceremarks,
l.ATTRIBUTE10 = ah.clientprice,
l.ATTRIBUTE11 = ah.clientpricecurrency,
l.PRICE_BREAK_LOOKUP_CODE = P_PRICE_BREAK_LOOKUP_CODE
where po_line_id = l_po_line_id;
insert into cux_scs_po_line_history
(h_transaction_id,
h_type,
h_line_id,
h_market_price,
h_unit_price,
h_expiration_date,
create_date)
values
(p_transaction_id,
'U',
l_po_line_id,
ah.taxprice,
ah.nottaxprice,
v_date_temp,
sysdate
);
---判断是否有分段价格----
/*begin
select max(cpl.laddernumber)
into max_qty
from CUX.CUX_PBA_LINE_SCS_IFACE cpl
where cpl.pricecode = ah.pricecode
and cpl.materialcode = ah.materialcode
and cpl.s_transaction_id = p_transaction_id;
exception
when others then
max_qty := 0;
end;*/
---更新阶梯价---
/*if nvl(max_qty, 0) <> 0 then
for c in cur_locations_all(ah.pricecode, ah.materialcode) loop
update po_line_locations_all plla
set plla.ATTRIBUTE14 = c.pricecode, ---暂时定为一样
plla.PRICE_OVERRIDE = c.pricecode,
plla.end_date = to_date(ah.invaliddate,
'yyyy-mm-dd'),
plla.LAST_UPDATE_DATE = sysdate,
plla.LAST_UPDATED_BY = g_user_id
where plla.QUANTITY =
(select max(plla.QUANTITY)
from po_line_locations_all pllas
where pllas.PO_LINE_ID = l_po_line_id
and pllas.SHIPMENT_TYPE = 'PRICE BREAK'
and c.laddernumber >= pllas.QUANTITY)
and plla.SHIPMENT_TYPE = 'PRICE BREAK'
and plla.PO_LINE_ID = l_po_line_id;
end loop;
end if;*/
end if;
end loop;
end if;
------更新结束--
commit;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,7,SYSDATE);
----api--- 调用处理---
mo_global.init('PO');
mo_global.set_policy_context('S', l_org_id);
l_progress := '001.1';
select pov.vendor_id
into l_vendor_id
from po_vendors pov
where pov.segment1 = c_all.SupplierCode
and sysdate between nvl(pov.start_date_active, sysdate - 1) and
nvl(pov.end_date_active, sysdate + 1);
l_progress := '001.2';
select pvs.vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all pvs
where pvs.vendor_id = l_vendor_id
and pvs.org_id = l_org_id
and pvs.vendor_site_code = c_all.sitename;
select assa.bill_to_location_id, assa.ship_to_location_id
into l_bill_to_location_id, l_ship_to_location_id
from ap_supplier_sites_all assa
where assa.vendor_site_id = l_vendor_site_id;
select count(1)
into p_exists_flag
from po_headers_all pha
where pha.SEGMENT1 = c_all.pricecode
and pha.ORG_ID = l_org_id;
---存在价目表类型就是UPDATE,否则是 ORIGINAL
if nvl(p_exists_flag, 0) <> 0 then
l_action := 'UPDATE';
else
l_action := 'ORIGINAL';
end if;
p_inter_head_id := po_headers_interface_s.nextval;
insert into po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id,
creation_date,
global_agreement_flag,
amount_limit,
comments,
interface_source_code,
amount_agreed,
document_num)
values
(p_inter_head_id,
g_batch_id,
'PENDING',
l_action, --'ORIGINAL',
l_org_id, --l_org_id,
'BLANKET',
c_all.currencycode, --bpa.currency_code,
61, --cur_heads_api.agent_id, --l_agent_id,
l_vendor_id,
l_vendor_site_id,
l_ship_to_location_id,
l_bill_to_location_id,
sysdate,
g_global_flag, --bpa.global_flag,
9999999999.00,
c_all.remarks, --bpa.remark,
'SCS_to_EBS',
9999999999.00,
c_all.pricecode);
g_po_number := c_all.pricecode;
l_line_num := 0;
--- 获取是否存在最大行号
select nvl(max(l.line_num), 0)
into l_max_number
from po_lines_all l, po_headers_all h
where h.segment1 = c_all.pricecode
and l.po_header_id = h.po_header_id
and h.org_id = l_org_id;
----PO 行信息--------
for cur_line_api in cur_po_line(c_all.orgcode,
c_all.pricecode,
c_all.sitename,
c_all.currencycode,
c_all.SUPPLIERCODE,
p_transaction_id) loop
l_line_num := l_line_num + 1;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,8,SYSDATE);
--累计定价
if cur_line_api.pricetype = '累计价' then
l_price_break_lookup_code := 'CUMULATIVE';
else
l_price_break_lookup_code := 'NON CUMULATIVE';
end if;
if nvl(p_exists_flag, 0) <> 0 then
begin
select ood.ORGANIZATION_ID
into l_organization_id
from org_organization_definitions ood
where ood.OPERATING_UNIT = l_org_id; --l_org_id;
exception
when others then
l_organization_id := 123;
end;
begin
select a.line_num, a.item_description, a.PO_LINE_ID
into l_line_num, l_item_desc, l_line_ids
from (select l.line_num,
l.item_description,
l.PO_LINE_ID,
l.ITEM_ID
from po_lines_all l,
po_headers_all h,
mtl_system_items_b b
where h.segment1 = cur_line_api.pricecode
and l.po_header_id = h.po_header_id
and b.inventory_item_id = l.item_id
and cur_line_api.materialcode = b.segment1
and nvl(l.cancel_flag, 'N') = 'N'
and nvl(l.closed_code, 'OPEN') = 'OPEN'
and h.org_id = l_org_id --l_org_id
and b.ORGANIZATION_ID = 122 --l_organization_id
order by l.EXPIRATION_DATE desc) a
where rownum = 1;
l_break_flag := 'N';
l_action := 'UPDATE';
-- dbms_output.put_line(l_line_num);
exception
when others then
l_action := 'ADD';
l_max_number := l_max_number + 1;
end;
else
l_action := 'ADD';
l_max_number := l_max_number + 1;
end if;
select msi.PRIMARY_UOM_CODE,
msi.PRIMARY_UNIT_OF_MEASURE,
msi.DESCRIPTION,
msi.INVENTORY_ITEM_ID
into p_uom, p_uom_unit, p_item_desc, p_item_id
from mtl_system_items_b msi
where msi.ORGANIZATION_ID = 122 --l_organization_id --p_inv_id
and msi.SEGMENT1 = cur_line_api.materialcode;
p_inter_line_id := po_lines_interface_s.nextval;
insert into po.po_lines_interface
(interface_line_id,
interface_header_id,
action,
line_num,
line_type,
item, ---6
uom_code,
unit_of_measure,
market_price,
unit_price,
ship_to_organization_id,
ship_to_location_id,
-- need_by_date,
-- promised_date,
creation_date,
price_break_lookup_code,
-- line_attribute1,
line_attribute8,
line_attribute5,
list_price_per_unit,
item_description,
expiration_date,
line_attribute_category_lines,
line_attribute9, --- 客指价说明
line_attribute10, -- 客指价
line_attribute11)
values
(p_inter_line_id, --po_lines_interface_s.nextval,
p_inter_head_id, --po_headers_interface_s.currval,
l_action, --
decode(l_action, 'ADD', l_max_number, l_line_num),
l_line_type,
cur_line_api.materialcode, ---6
p_uom,
p_uom_unit,
cur_line_api.taxprice,
cur_line_api.nottaxprice,
l_organization_id, --p_inv_id,
l_ship_to_location_id,
--null, -- SYSDATE,
-- null, --SYSDATE,
sysdate,
l_price_break_lookup_code,
-- cur_all_api.ATTRIBUTE1,
cur_line_api.pricetype,
substr(cur_line_api.remarks, 1, 145),
cur_line_api.nottaxprice,
l_item_desc,
to_date(cur_line_api.invaliddate, 'yyyy-mm-dd'),
'BLANKET',
substr(cur_line_api.clientpriceremarks, 1, 145),
cur_line_api.clientprice,
cur_line_api.clientpricecurrency);
-- add by iven.lin 2021-03-13
/* cux_ws_scs_op_pba_pub.set_po_item(p_io_id => p_org_organization_id,
p_item_id => p_item_id,
p_jiagong_qty => cur_line_api.processingadvancedays,
p_mqt_qty => cur_line_api.minpackagenumber,
p_message => p_item_message);
if p_item_message is not null then
x_return_code := g_error;
return;
end if;*/
begin
select count(1)
into f_count
from cux.cux_pba_line_scs_iface cl
where cl.s_transaction_id = p_transaction_id
and cl.orgcode = c_all.orgcode
and cl.pricecode = c_all.pricecode
and cl.materialcode = cur_line_api.materialcode;
exception
when others then
f_count := 0;
end;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,9,SYSDATE);
if f_count > 0 then
--- 备份阶梯价--当插入失败时候还原---
insert into CUX.CUX_PO_LINE_LOCATIONS_ALL_BACK
(LINE_LOCATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
PO_LINE_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
QUANTITY,
QUANTITY_RECEIVED,
QUANTITY_ACCEPTED,
QUANTITY_REJECTED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
UNIT_MEAS_LOOKUP_CODE,
PO_RELEASE_ID,
SHIP_TO_LOCATION_ID,
SHIP_VIA_LOOKUP_CODE,
NEED_BY_DATE,
PROMISED_DATE,
LAST_ACCEPT_DATE,
PRICE_OVERRIDE,
ENCUMBERED_FLAG,
ENCUMBERED_DATE,
UNENCUMBERED_QUANTITY,
FOB_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE,
TAXABLE_FLAG,
TAX_NAME,
ESTIMATED_TAX_AMOUNT,
FROM_HEADER_ID,
FROM_LINE_ID,
FROM_LINE_LOCATION_ID,
START_DATE,
END_DATE,
LEAD_TIME,
LEAD_TIME_UNIT,
PRICE_DISCOUNT,
TERMS_ID,
APPROVED_FLAG,
APPROVED_DATE,
CLOSED_FLAG,
CANCEL_FLAG,
CANCELLED_BY,
CANCEL_DATE,
CANCEL_REASON,
FIRM_STATUS_LOOKUP_CODE,
FIRM_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
UNIT_OF_MEASURE_CLASS,
ENCUMBER_NOW,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
QTY_RCV_EXCEPTION_CODE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
SHIP_TO_ORGANIZATION_ID,
SHIPMENT_NUM,
SOURCE_SHIPMENT_ID,
SHIPMENT_TYPE,
CLOSED_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
USSGL_TRANSACTION_CODE,
GOVERNMENT_CONTEXT,
RECEIVING_ROUTING_ID,
ACCRUE_ON_RECEIPT_FLAG,
CLOSED_REASON,
CLOSED_DATE,
CLOSED_BY,
ORG_ID,
QUANTITY_SHIPPED,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
COUNTRY_OF_ORIGIN_CODE,
TAX_USER_OVERRIDE_FLAG,
MATCH_OPTION,
TAX_CODE_ID,
CALCULATE_TAX_FLAG,
CHANGE_PROMISED_DATE_REASON,
NOTE_TO_RECEIVER,
SECONDARY_QUANTITY,
SECONDARY_UNIT_OF_MEASURE,
PREFERRED_GRADE,
SECONDARY_QUANTITY_RECEIVED,
SECONDARY_QUANTITY_ACCEPTED,
SECONDARY_QUANTITY_REJECTED,
SECONDARY_QUANTITY_CANCELLED,
VMI_FLAG,
CONSIGNED_FLAG,
RETROACTIVE_DATE,
SUPPLIER_ORDER_LINE_NUMBER,
AMOUNT,
AMOUNT_RECEIVED,
AMOUNT_BILLED,
AMOUNT_CANCELLED,
AMOUNT_REJECTED,
AMOUNT_ACCEPTED,
DROP_SHIP_FLAG,
SALES_ORDER_UPDATE_DATE,
TRANSACTION_FLOW_HEADER_ID,
FINAL_MATCH_FLAG,
MANUAL_PRICE_CHANGE_FLAG,
SHIPMENT_CLOSED_DATE,
CLOSED_FOR_RECEIVING_DATE,
CLOSED_FOR_INVOICE_DATE,
SECONDARY_QUANTITY_SHIPPED,
VALUE_BASIS,
MATCHING_BASIS,
PAYMENT_TYPE,
DESCRIPTION,
WORK_APPROVER_ID,
BID_PAYMENT_ID,
QUANTITY_FINANCED,
AMOUNT_FINANCED,
QUANTITY_RECOUPED,
AMOUNT_RECOUPED,
RETAINAGE_WITHHELD_AMOUNT,
RETAINAGE_RELEASED_AMOUNT,
AMOUNT_SHIPPED,
OUTSOURCED_ASSEMBLY,
TAX_ATTRIBUTE_UPDATE_CODE,
ORIGINAL_SHIPMENT_ID,
LCM_FLAG,
UDA_TEMPLATE_ID,
DRAFT_ID,
CLM_PERIOD_PERF_END_DATE,
CLM_PERIOD_PERF_START_DATE,
REVISION_NUM,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
X_TRANSACTION_ID,
X_CREATE_DATE)
(select l.LINE_LOCATION_ID,
l.LAST_UPDATE_DATE,
l.LAST_UPDATED_BY,
l.PO_HEADER_ID,
l.PO_LINE_ID,
l.LAST_UPDATE_LOGIN,
l.CREATION_DATE,
l.CREATED_BY,
l.QUANTITY,
l.QUANTITY_RECEIVED,
l.QUANTITY_ACCEPTED,
l.QUANTITY_REJECTED,
l.QUANTITY_BILLED,
l.QUANTITY_CANCELLED,
l.UNIT_MEAS_LOOKUP_CODE,
l.PO_RELEASE_ID,
l.SHIP_TO_LOCATION_ID,
l.SHIP_VIA_LOOKUP_CODE,
l.NEED_BY_DATE,
l.PROMISED_DATE,
l.LAST_ACCEPT_DATE,
l.PRICE_OVERRIDE,
l.ENCUMBERED_FLAG,
l.ENCUMBERED_DATE,
l.UNENCUMBERED_QUANTITY,
l.FOB_LOOKUP_CODE,
l.FREIGHT_TERMS_LOOKUP_CODE,
l.TAXABLE_FLAG,
l.TAX_NAME,
l.ESTIMATED_TAX_AMOUNT,
l.FROM_HEADER_ID,
l.FROM_LINE_ID,
l.FROM_LINE_LOCATION_ID,
l.START_DATE,
l.END_DATE,
l.LEAD_TIME,
l.LEAD_TIME_UNIT,
l.PRICE_DISCOUNT,
l.TERMS_ID,
l.APPROVED_FLAG,
l.APPROVED_DATE,
l.CLOSED_FLAG,
l.CANCEL_FLAG,
l.CANCELLED_BY,
l.CANCEL_DATE,
l.CANCEL_REASON,
l.FIRM_STATUS_LOOKUP_CODE,
l.FIRM_DATE,
l.ATTRIBUTE_CATEGORY,
l.ATTRIBUTE1,
l.ATTRIBUTE2,
l.ATTRIBUTE3,
l.ATTRIBUTE4,
l.ATTRIBUTE5,
l.ATTRIBUTE6,
l.ATTRIBUTE7,
l.ATTRIBUTE8,
l.ATTRIBUTE9,
l.ATTRIBUTE10,
l.UNIT_OF_MEASURE_CLASS,
l.ENCUMBER_NOW,
l.ATTRIBUTE11,
l.ATTRIBUTE12,
l.ATTRIBUTE13,
l.ATTRIBUTE14,
l.ATTRIBUTE15,
l.INSPECTION_REQUIRED_FLAG,
l.RECEIPT_REQUIRED_FLAG,
l.QTY_RCV_TOLERANCE,
l.QTY_RCV_EXCEPTION_CODE,
l.ENFORCE_SHIP_TO_LOCATION_CODE,
l.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
l.DAYS_EARLY_RECEIPT_ALLOWED,
l.DAYS_LATE_RECEIPT_ALLOWED,
l.RECEIPT_DAYS_EXCEPTION_CODE,
l.INVOICE_CLOSE_TOLERANCE,
l.RECEIVE_CLOSE_TOLERANCE,
l.SHIP_TO_ORGANIZATION_ID,
l.SHIPMENT_NUM,
l.SOURCE_SHIPMENT_ID,
l.SHIPMENT_TYPE,
l.CLOSED_CODE,
l.REQUEST_ID,
l.PROGRAM_APPLICATION_ID,
l.PROGRAM_ID,
l.PROGRAM_UPDATE_DATE,
l.USSGL_TRANSACTION_CODE,
l.GOVERNMENT_CONTEXT,
l.RECEIVING_ROUTING_ID,
l.ACCRUE_ON_RECEIPT_FLAG,
l.CLOSED_REASON,
l.CLOSED_DATE,
l.CLOSED_BY,
l.ORG_ID,
l.QUANTITY_SHIPPED,
l.GLOBAL_ATTRIBUTE_CATEGORY,
l.GLOBAL_ATTRIBUTE1,
l.GLOBAL_ATTRIBUTE2,
l.GLOBAL_ATTRIBUTE3,
l.GLOBAL_ATTRIBUTE4,
l.GLOBAL_ATTRIBUTE5,
l.GLOBAL_ATTRIBUTE6,
l.GLOBAL_ATTRIBUTE7,
l.GLOBAL_ATTRIBUTE8,
l.GLOBAL_ATTRIBUTE9,
l.GLOBAL_ATTRIBUTE10,
l.GLOBAL_ATTRIBUTE11,
l.GLOBAL_ATTRIBUTE12,
l.GLOBAL_ATTRIBUTE13,
l.GLOBAL_ATTRIBUTE14,
l.GLOBAL_ATTRIBUTE15,
l.GLOBAL_ATTRIBUTE16,
l.GLOBAL_ATTRIBUTE17,
l.GLOBAL_ATTRIBUTE18,
l.GLOBAL_ATTRIBUTE19,
l.GLOBAL_ATTRIBUTE20,
l.COUNTRY_OF_ORIGIN_CODE,
l.TAX_USER_OVERRIDE_FLAG,
l.MATCH_OPTION,
l.TAX_CODE_ID,
l.CALCULATE_TAX_FLAG,
l.CHANGE_PROMISED_DATE_REASON,
l.NOTE_TO_RECEIVER,
l.SECONDARY_QUANTITY,
l.SECONDARY_UNIT_OF_MEASURE,
l.PREFERRED_GRADE,
l.SECONDARY_QUANTITY_RECEIVED,
l.SECONDARY_QUANTITY_ACCEPTED,
l.SECONDARY_QUANTITY_REJECTED,
l.SECONDARY_QUANTITY_CANCELLED,
l.VMI_FLAG,
l.CONSIGNED_FLAG,
l.RETROACTIVE_DATE,
l.SUPPLIER_ORDER_LINE_NUMBER,
l.AMOUNT,
l.AMOUNT_RECEIVED,
l.AMOUNT_BILLED,
l.AMOUNT_CANCELLED,
l.AMOUNT_REJECTED,
l.AMOUNT_ACCEPTED,
l.DROP_SHIP_FLAG,
l.SALES_ORDER_UPDATE_DATE,
l.TRANSACTION_FLOW_HEADER_ID,
l.FINAL_MATCH_FLAG,
l.MANUAL_PRICE_CHANGE_FLAG,
l.SHIPMENT_CLOSED_DATE,
l.CLOSED_FOR_RECEIVING_DATE,
l.CLOSED_FOR_INVOICE_DATE,
l.SECONDARY_QUANTITY_SHIPPED,
l.VALUE_BASIS,
l.MATCHING_BASIS,
l.PAYMENT_TYPE,
l.DESCRIPTION,
l.WORK_APPROVER_ID,
l.BID_PAYMENT_ID,
l.QUANTITY_FINANCED,
l.AMOUNT_FINANCED,
l.QUANTITY_RECOUPED,
l.AMOUNT_RECOUPED,
l.RETAINAGE_WITHHELD_AMOUNT,
l.RETAINAGE_RELEASED_AMOUNT,
l.AMOUNT_SHIPPED,
l.OUTSOURCED_ASSEMBLY,
l.TAX_ATTRIBUTE_UPDATE_CODE,
l.ORIGINAL_SHIPMENT_ID,
l.LCM_FLAG,
l.UDA_TEMPLATE_ID,
l.DRAFT_ID,
l.CLM_PERIOD_PERF_END_DATE,
l.CLM_PERIOD_PERF_START_DATE,
l.REVISION_NUM,
l.CLM_DELIVERY_PERIOD,
l.CLM_DELIVERY_PERIOD_UOM,
l.CLM_POP_DURATION,
l.CLM_POP_DURATION_UOM,
l.CLM_PROMISE_PERIOD,
l.CLM_PROMISE_PERIOD_UOM,
p_transaction_id,
sysdate
from po_line_locations_all l
where l.PO_LINE_ID = l_line_ids
and l.SHIPMENT_TYPE = 'PRICE BREAK');
----删除阶梯价----
delete from po_line_locations_all plla
where plla.PO_LINE_ID = l_line_ids
and plla.SHIPMENT_TYPE = 'PRICE BREAK';
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,10,SYSDATE);
commit;
l_pb_line_num := 0;
for cur_bpa_ll_api in cur_po_ll(c_all.orgcode,
c_all.pricecode,
cur_line_api.materialcode,
p_transaction_id) loop
l_pb_line_num := l_pb_line_num + 1;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,11,SYSDATE);
if l_price_break_lookup_code = 'NON CUMULATIVE' then
l_start_date := null; --rec_pb_ll.start_date;
l_end_date := to_date(cur_line_api.invaliddate,
'yyyy-mm-dd'); --to_date(cur_bpa_ll_api.ladderdate,'yyyy-mm-dd');
else
l_start_date := null;
l_end_date := null;
end if;
insert into po.po_line_locations_interface
(interface_line_location_id,
interface_header_id,
interface_line_id,
process_code,
shipment_type,
shipment_num,
ship_to_organization_id,
ship_to_location_id,
quantity,
start_date,
end_date,
attribute14,
price_override,
creation_date
-- attribute15
)
values
(po_line_locations_interface_s.nextval,
p_inter_head_id, --po_headers_interface_s.currval,
p_inter_line_id, -- po_lines_interface_s.currval,
'PENDING',
'PRICE BREAK',
l_pb_line_num,
l_organization_id,
l_ship_to_location_id,
cur_bpa_ll_api.laddernumber,
l_start_date,
l_end_date, --to_date(cur_line_api.invaliddate, 'yyyy-mm-dd'),
cur_bpa_ll_api.laddertaxprice,
cur_bpa_ll_api.laddernottaxprice,
sysdate
-- cur_bpa_ll_api.attribute15
);
end loop;
else
--- add by iven.lin 2021-11-16 start--
if cur_line_api.pricetype = '一口价' then
pp_flag := 0;
begin
select count(1)
into pp_flag
from po_line_locations_all plla
where plla.PO_LINE_ID = l_line_ids
and plla.SHIPMENT_TYPE = 'PRICE BREAK';
exception
when others then
pp_flag := 0;
end;
if pp_flag > 0 then
--- 备份阶梯价--当插入失败时候还原---
insert into CUX.CUX_PO_LINE_LOCATIONS_ALL_BACK
(LINE_LOCATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
PO_LINE_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
QUANTITY,
QUANTITY_RECEIVED,
QUANTITY_ACCEPTED,
QUANTITY_REJECTED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
UNIT_MEAS_LOOKUP_CODE,
PO_RELEASE_ID,
SHIP_TO_LOCATION_ID,
SHIP_VIA_LOOKUP_CODE,
NEED_BY_DATE,
PROMISED_DATE,
LAST_ACCEPT_DATE,
PRICE_OVERRIDE,
ENCUMBERED_FLAG,
ENCUMBERED_DATE,
UNENCUMBERED_QUANTITY,
FOB_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE,
TAXABLE_FLAG,
TAX_NAME,
ESTIMATED_TAX_AMOUNT,
FROM_HEADER_ID,
FROM_LINE_ID,
FROM_LINE_LOCATION_ID,
START_DATE,
END_DATE,
LEAD_TIME,
LEAD_TIME_UNIT,
PRICE_DISCOUNT,
TERMS_ID,
APPROVED_FLAG,
APPROVED_DATE,
CLOSED_FLAG,
CANCEL_FLAG,
CANCELLED_BY,
CANCEL_DATE,
CANCEL_REASON,
FIRM_STATUS_LOOKUP_CODE,
FIRM_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
UNIT_OF_MEASURE_CLASS,
ENCUMBER_NOW,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
QTY_RCV_EXCEPTION_CODE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
SHIP_TO_ORGANIZATION_ID,
SHIPMENT_NUM,
SOURCE_SHIPMENT_ID,
SHIPMENT_TYPE,
CLOSED_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
USSGL_TRANSACTION_CODE,
GOVERNMENT_CONTEXT,
RECEIVING_ROUTING_ID,
ACCRUE_ON_RECEIPT_FLAG,
CLOSED_REASON,
CLOSED_DATE,
CLOSED_BY,
ORG_ID,
QUANTITY_SHIPPED,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
COUNTRY_OF_ORIGIN_CODE,
TAX_USER_OVERRIDE_FLAG,
MATCH_OPTION,
TAX_CODE_ID,
CALCULATE_TAX_FLAG,
CHANGE_PROMISED_DATE_REASON,
NOTE_TO_RECEIVER,
SECONDARY_QUANTITY,
SECONDARY_UNIT_OF_MEASURE,
PREFERRED_GRADE,
SECONDARY_QUANTITY_RECEIVED,
SECONDARY_QUANTITY_ACCEPTED,
SECONDARY_QUANTITY_REJECTED,
SECONDARY_QUANTITY_CANCELLED,
VMI_FLAG,
CONSIGNED_FLAG,
RETROACTIVE_DATE,
SUPPLIER_ORDER_LINE_NUMBER,
AMOUNT,
AMOUNT_RECEIVED,
AMOUNT_BILLED,
AMOUNT_CANCELLED,
AMOUNT_REJECTED,
AMOUNT_ACCEPTED,
DROP_SHIP_FLAG,
SALES_ORDER_UPDATE_DATE,
TRANSACTION_FLOW_HEADER_ID,
FINAL_MATCH_FLAG,
MANUAL_PRICE_CHANGE_FLAG,
SHIPMENT_CLOSED_DATE,
CLOSED_FOR_RECEIVING_DATE,
CLOSED_FOR_INVOICE_DATE,
SECONDARY_QUANTITY_SHIPPED,
VALUE_BASIS,
MATCHING_BASIS,
PAYMENT_TYPE,
DESCRIPTION,
WORK_APPROVER_ID,
BID_PAYMENT_ID,
QUANTITY_FINANCED,
AMOUNT_FINANCED,
QUANTITY_RECOUPED,
AMOUNT_RECOUPED,
RETAINAGE_WITHHELD_AMOUNT,
RETAINAGE_RELEASED_AMOUNT,
AMOUNT_SHIPPED,
OUTSOURCED_ASSEMBLY,
TAX_ATTRIBUTE_UPDATE_CODE,
ORIGINAL_SHIPMENT_ID,
LCM_FLAG,
UDA_TEMPLATE_ID,
DRAFT_ID,
CLM_PERIOD_PERF_END_DATE,
CLM_PERIOD_PERF_START_DATE,
REVISION_NUM,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
X_TRANSACTION_ID,
X_CREATE_DATE)
(select l.LINE_LOCATION_ID,
l.LAST_UPDATE_DATE,
l.LAST_UPDATED_BY,
l.PO_HEADER_ID,
l.PO_LINE_ID,
l.LAST_UPDATE_LOGIN,
l.CREATION_DATE,
l.CREATED_BY,
l.QUANTITY,
l.QUANTITY_RECEIVED,
l.QUANTITY_ACCEPTED,
l.QUANTITY_REJECTED,
l.QUANTITY_BILLED,
l.QUANTITY_CANCELLED,
l.UNIT_MEAS_LOOKUP_CODE,
l.PO_RELEASE_ID,
l.SHIP_TO_LOCATION_ID,
l.SHIP_VIA_LOOKUP_CODE,
l.NEED_BY_DATE,
l.PROMISED_DATE,
l.LAST_ACCEPT_DATE,
l.PRICE_OVERRIDE,
l.ENCUMBERED_FLAG,
l.ENCUMBERED_DATE,
l.UNENCUMBERED_QUANTITY,
l.FOB_LOOKUP_CODE,
l.FREIGHT_TERMS_LOOKUP_CODE,
l.TAXABLE_FLAG,
l.TAX_NAME,
l.ESTIMATED_TAX_AMOUNT,
l.FROM_HEADER_ID,
l.FROM_LINE_ID,
l.FROM_LINE_LOCATION_ID,
l.START_DATE,
l.END_DATE,
l.LEAD_TIME,
l.LEAD_TIME_UNIT,
l.PRICE_DISCOUNT,
l.TERMS_ID,
l.APPROVED_FLAG,
l.APPROVED_DATE,
l.CLOSED_FLAG,
l.CANCEL_FLAG,
l.CANCELLED_BY,
l.CANCEL_DATE,
l.CANCEL_REASON,
l.FIRM_STATUS_LOOKUP_CODE,
l.FIRM_DATE,
l.ATTRIBUTE_CATEGORY,
l.ATTRIBUTE1,
l.ATTRIBUTE2,
l.ATTRIBUTE3,
l.ATTRIBUTE4,
l.ATTRIBUTE5,
l.ATTRIBUTE6,
l.ATTRIBUTE7,
l.ATTRIBUTE8,
l.ATTRIBUTE9,
l.ATTRIBUTE10,
l.UNIT_OF_MEASURE_CLASS,
l.ENCUMBER_NOW,
l.ATTRIBUTE11,
l.ATTRIBUTE12,
l.ATTRIBUTE13,
l.ATTRIBUTE14,
l.ATTRIBUTE15,
l.INSPECTION_REQUIRED_FLAG,
l.RECEIPT_REQUIRED_FLAG,
l.QTY_RCV_TOLERANCE,
l.QTY_RCV_EXCEPTION_CODE,
l.ENFORCE_SHIP_TO_LOCATION_CODE,
l.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
l.DAYS_EARLY_RECEIPT_ALLOWED,
l.DAYS_LATE_RECEIPT_ALLOWED,
l.RECEIPT_DAYS_EXCEPTION_CODE,
l.INVOICE_CLOSE_TOLERANCE,
l.RECEIVE_CLOSE_TOLERANCE,
l.SHIP_TO_ORGANIZATION_ID,
l.SHIPMENT_NUM,
l.SOURCE_SHIPMENT_ID,
l.SHIPMENT_TYPE,
l.CLOSED_CODE,
l.REQUEST_ID,
l.PROGRAM_APPLICATION_ID,
l.PROGRAM_ID,
l.PROGRAM_UPDATE_DATE,
l.USSGL_TRANSACTION_CODE,
l.GOVERNMENT_CONTEXT,
l.RECEIVING_ROUTING_ID,
l.ACCRUE_ON_RECEIPT_FLAG,
l.CLOSED_REASON,
l.CLOSED_DATE,
l.CLOSED_BY,
l.ORG_ID,
l.QUANTITY_SHIPPED,
l.GLOBAL_ATTRIBUTE_CATEGORY,
l.GLOBAL_ATTRIBUTE1,
l.GLOBAL_ATTRIBUTE2,
l.GLOBAL_ATTRIBUTE3,
l.GLOBAL_ATTRIBUTE4,
l.GLOBAL_ATTRIBUTE5,
l.GLOBAL_ATTRIBUTE6,
l.GLOBAL_ATTRIBUTE7,
l.GLOBAL_ATTRIBUTE8,
l.GLOBAL_ATTRIBUTE9,
l.GLOBAL_ATTRIBUTE10,
l.GLOBAL_ATTRIBUTE11,
l.GLOBAL_ATTRIBUTE12,
l.GLOBAL_ATTRIBUTE13,
l.GLOBAL_ATTRIBUTE14,
l.GLOBAL_ATTRIBUTE15,
l.GLOBAL_ATTRIBUTE16,
l.GLOBAL_ATTRIBUTE17,
l.GLOBAL_ATTRIBUTE18,
l.GLOBAL_ATTRIBUTE19,
l.GLOBAL_ATTRIBUTE20,
l.COUNTRY_OF_ORIGIN_CODE,
l.TAX_USER_OVERRIDE_FLAG,
l.MATCH_OPTION,
l.TAX_CODE_ID,
l.CALCULATE_TAX_FLAG,
l.CHANGE_PROMISED_DATE_REASON,
l.NOTE_TO_RECEIVER,
l.SECONDARY_QUANTITY,
l.SECONDARY_UNIT_OF_MEASURE,
l.PREFERRED_GRADE,
l.SECONDARY_QUANTITY_RECEIVED,
l.SECONDARY_QUANTITY_ACCEPTED,
l.SECONDARY_QUANTITY_REJECTED,
l.SECONDARY_QUANTITY_CANCELLED,
l.VMI_FLAG,
l.CONSIGNED_FLAG,
l.RETROACTIVE_DATE,
l.SUPPLIER_ORDER_LINE_NUMBER,
l.AMOUNT,
l.AMOUNT_RECEIVED,
l.AMOUNT_BILLED,
l.AMOUNT_CANCELLED,
l.AMOUNT_REJECTED,
l.AMOUNT_ACCEPTED,
l.DROP_SHIP_FLAG,
l.SALES_ORDER_UPDATE_DATE,
l.TRANSACTION_FLOW_HEADER_ID,
l.FINAL_MATCH_FLAG,
l.MANUAL_PRICE_CHANGE_FLAG,
l.SHIPMENT_CLOSED_DATE,
l.CLOSED_FOR_RECEIVING_DATE,
l.CLOSED_FOR_INVOICE_DATE,
l.SECONDARY_QUANTITY_SHIPPED,
l.VALUE_BASIS,
l.MATCHING_BASIS,
l.PAYMENT_TYPE,
l.DESCRIPTION,
l.WORK_APPROVER_ID,
l.BID_PAYMENT_ID,
l.QUANTITY_FINANCED,
l.AMOUNT_FINANCED,
l.QUANTITY_RECOUPED,
l.AMOUNT_RECOUPED,
l.RETAINAGE_WITHHELD_AMOUNT,
l.RETAINAGE_RELEASED_AMOUNT,
l.AMOUNT_SHIPPED,
l.OUTSOURCED_ASSEMBLY,
l.TAX_ATTRIBUTE_UPDATE_CODE,
l.ORIGINAL_SHIPMENT_ID,
l.LCM_FLAG,
l.UDA_TEMPLATE_ID,
l.DRAFT_ID,
l.CLM_PERIOD_PERF_END_DATE,
l.CLM_PERIOD_PERF_START_DATE,
l.REVISION_NUM,
l.CLM_DELIVERY_PERIOD,
l.CLM_DELIVERY_PERIOD_UOM,
l.CLM_POP_DURATION,
l.CLM_POP_DURATION_UOM,
l.CLM_PROMISE_PERIOD,
l.CLM_PROMISE_PERIOD_UOM,
p_transaction_id,
sysdate
from po_line_locations_all l
where l.PO_LINE_ID = l_line_ids
and l.SHIPMENT_TYPE = 'PRICE BREAK');
----删除阶梯价----
delete from po_line_locations_all plla
where plla.PO_LINE_ID = l_line_ids
and plla.SHIPMENT_TYPE = 'PRICE BREAK';
end if;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,12,SYSDATE);
end if;
--- end
end if;
end loop;
commit;
mo_global.init('PO');
mo_global.set_policy_context('S', l_org_id);
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,13,SYSDATE);
delete from cux_po_head_num_error;
po_pdoi_grp.start_process(p_api_version => 1,
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 => g_batch_id,
p_batch_size => po_pdoi_constants.g_def_batch_size,
p_buyer_id => NULL,
p_document_type => 'BLANKET',
p_document_subtype => null,
p_create_items => null,
p_create_sourcing_rules_flag => null,
p_rel_gen_method => null,
p_sourcing_level => 1,
p_sourcing_inv_org_id => null,
p_approved_status => 'APPROVED',
p_process_code => po_pdoi_constants.g_process_code_pending,
p_interface_header_id => null,
p_org_id => l_org_id,
p_ga_flag => null);
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,14,SYSDATE);
p_api_info := null;
if v_return_status <> fnd_api.g_ret_sts_success then
for l_index in 1 .. fnd_msg_pub.count_msg loop
errbuf := errbuf || chr(10) ||
oe_msg_pub.get(p_msg_index => l_index,
p_encoded => 'F');
p_api_info := oe_msg_pub.get(p_msg_index => l_index,
p_encoded => 'F');
end loop;
if p_api_info is not null then
p_pass := 'F';
else
errbuf := errbuf || '-API返回未知异常-';
end if;
else
begin
select err.error_message
into errbuf
from po_interface_errors err
where 1 = 1
and rownum = 1
and err.batch_id = g_batch_id
and err.INTERFACE_TYPE <> 'RCV-856'; --- add by 2021-05-20 屏蔽历史错误batch_id
p_pass := 'F';
errbuf := '价目表创建失败_Batch_id:' || g_batch_id || ',' || errbuf;
begin
----- 还原po_line 数据----
for c_line_bk in cur_line_back(p_transaction_id) loop
update po_lines_all l
set l.market_price = c_line_bk.market_price,
l.unit_price = c_line_bk.unit_price,
l.list_price_per_unit = c_line_bk.unit_price,
l.expiration_date = c_line_bk.expiration_date,
l.ATTRIBUTE5 = c_line_bk.attribute5,
l.attribute8 = c_line_bk.attribute8, --- 价格类型
l.LAST_UPDATE_DATE = sysdate,
l.LAST_UPDATED_BY = 1190
where po_line_id = c_line_bk.p_line_id;
---记录被还原的数据
begin
insert into cux_scs_po_line_history
(h_transaction_id,
h_type,
h_line_id,
h_market_price,
h_unit_price,
h_expiration_date,
create_date)
values
(p_transaction_id,
'B',
c_line_bk.p_line_id,
c_line_bk.market_price,
c_line_bk.unit_price,
c_line_bk.expiration_date,
sysdate);
exception
when others then
null;
end;
end loop;
----还原po_location----数据---
insert into po_line_locations_all
(LINE_LOCATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PO_HEADER_ID,
PO_LINE_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
QUANTITY,
QUANTITY_RECEIVED,
QUANTITY_ACCEPTED,
QUANTITY_REJECTED,
QUANTITY_BILLED,
QUANTITY_CANCELLED,
UNIT_MEAS_LOOKUP_CODE,
PO_RELEASE_ID,
SHIP_TO_LOCATION_ID,
SHIP_VIA_LOOKUP_CODE,
NEED_BY_DATE,
PROMISED_DATE,
LAST_ACCEPT_DATE,
PRICE_OVERRIDE,
ENCUMBERED_FLAG,
ENCUMBERED_DATE,
UNENCUMBERED_QUANTITY,
FOB_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE,
TAXABLE_FLAG,
TAX_NAME,
ESTIMATED_TAX_AMOUNT,
FROM_HEADER_ID,
FROM_LINE_ID,
FROM_LINE_LOCATION_ID,
START_DATE,
END_DATE,
LEAD_TIME,
LEAD_TIME_UNIT,
PRICE_DISCOUNT,
TERMS_ID,
APPROVED_FLAG,
APPROVED_DATE,
CLOSED_FLAG,
CANCEL_FLAG,
CANCELLED_BY,
CANCEL_DATE,
CANCEL_REASON,
FIRM_STATUS_LOOKUP_CODE,
FIRM_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
UNIT_OF_MEASURE_CLASS,
ENCUMBER_NOW,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
QTY_RCV_TOLERANCE,
QTY_RCV_EXCEPTION_CODE,
ENFORCE_SHIP_TO_LOCATION_CODE,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
DAYS_EARLY_RECEIPT_ALLOWED,
DAYS_LATE_RECEIPT_ALLOWED,
RECEIPT_DAYS_EXCEPTION_CODE,
INVOICE_CLOSE_TOLERANCE,
RECEIVE_CLOSE_TOLERANCE,
SHIP_TO_ORGANIZATION_ID,
SHIPMENT_NUM,
SOURCE_SHIPMENT_ID,
SHIPMENT_TYPE,
CLOSED_CODE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
USSGL_TRANSACTION_CODE,
GOVERNMENT_CONTEXT,
RECEIVING_ROUTING_ID,
ACCRUE_ON_RECEIPT_FLAG,
CLOSED_REASON,
CLOSED_DATE,
CLOSED_BY,
ORG_ID,
QUANTITY_SHIPPED,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
COUNTRY_OF_ORIGIN_CODE,
TAX_USER_OVERRIDE_FLAG,
MATCH_OPTION,
TAX_CODE_ID,
CALCULATE_TAX_FLAG,
CHANGE_PROMISED_DATE_REASON,
NOTE_TO_RECEIVER,
SECONDARY_QUANTITY,
SECONDARY_UNIT_OF_MEASURE,
PREFERRED_GRADE,
SECONDARY_QUANTITY_RECEIVED,
SECONDARY_QUANTITY_ACCEPTED,
SECONDARY_QUANTITY_REJECTED,
SECONDARY_QUANTITY_CANCELLED,
VMI_FLAG,
CONSIGNED_FLAG,
RETROACTIVE_DATE,
SUPPLIER_ORDER_LINE_NUMBER,
AMOUNT,
AMOUNT_RECEIVED,
AMOUNT_BILLED,
AMOUNT_CANCELLED,
AMOUNT_REJECTED,
AMOUNT_ACCEPTED,
DROP_SHIP_FLAG,
SALES_ORDER_UPDATE_DATE,
TRANSACTION_FLOW_HEADER_ID,
FINAL_MATCH_FLAG,
MANUAL_PRICE_CHANGE_FLAG,
SHIPMENT_CLOSED_DATE,
CLOSED_FOR_RECEIVING_DATE,
CLOSED_FOR_INVOICE_DATE,
SECONDARY_QUANTITY_SHIPPED,
VALUE_BASIS,
MATCHING_BASIS,
PAYMENT_TYPE,
DESCRIPTION,
WORK_APPROVER_ID,
BID_PAYMENT_ID,
QUANTITY_FINANCED,
AMOUNT_FINANCED,
QUANTITY_RECOUPED,
AMOUNT_RECOUPED,
RETAINAGE_WITHHELD_AMOUNT,
RETAINAGE_RELEASED_AMOUNT,
AMOUNT_SHIPPED,
OUTSOURCED_ASSEMBLY,
TAX_ATTRIBUTE_UPDATE_CODE,
ORIGINAL_SHIPMENT_ID,
LCM_FLAG,
UDA_TEMPLATE_ID,
DRAFT_ID,
CLM_PERIOD_PERF_END_DATE,
CLM_PERIOD_PERF_START_DATE,
REVISION_NUM,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM)
(select lli.LINE_LOCATION_ID,
lli.LAST_UPDATE_DATE,
lli.LAST_UPDATED_BY,
lli.PO_HEADER_ID,
lli.PO_LINE_ID,
lli.LAST_UPDATE_LOGIN,
lli.CREATION_DATE,
lli.CREATED_BY,
lli.QUANTITY,
lli.QUANTITY_RECEIVED,
lli.QUANTITY_ACCEPTED,
lli.QUANTITY_REJECTED,
lli.QUANTITY_BILLED,
lli.QUANTITY_CANCELLED,
lli.UNIT_MEAS_LOOKUP_CODE,
lli.PO_RELEASE_ID,
lli.SHIP_TO_LOCATION_ID,
lli.SHIP_VIA_LOOKUP_CODE,
lli.NEED_BY_DATE,
lli.PROMISED_DATE,
lli.LAST_ACCEPT_DATE,
lli.PRICE_OVERRIDE,
lli.ENCUMBERED_FLAG,
lli.ENCUMBERED_DATE,
lli.UNENCUMBERED_QUANTITY,
lli.FOB_LOOKUP_CODE,
lli.FREIGHT_TERMS_LOOKUP_CODE,
lli.TAXABLE_FLAG,
lli.TAX_NAME,
lli.ESTIMATED_TAX_AMOUNT,
lli.FROM_HEADER_ID,
lli.FROM_LINE_ID,
lli.FROM_LINE_LOCATION_ID,
lli.START_DATE,
lli.END_DATE,
lli.LEAD_TIME,
lli.LEAD_TIME_UNIT,
lli.PRICE_DISCOUNT,
lli.TERMS_ID,
lli.APPROVED_FLAG,
lli.APPROVED_DATE,
lli.CLOSED_FLAG,
lli.CANCEL_FLAG,
lli.CANCELLED_BY,
lli.CANCEL_DATE,
lli.CANCEL_REASON,
lli.FIRM_STATUS_LOOKUP_CODE,
lli.FIRM_DATE,
lli.ATTRIBUTE_CATEGORY,
lli.ATTRIBUTE1,
lli.ATTRIBUTE2,
lli.ATTRIBUTE3,
lli.ATTRIBUTE4,
lli.ATTRIBUTE5,
lli.ATTRIBUTE6,
lli.ATTRIBUTE7,
lli.ATTRIBUTE8,
lli.ATTRIBUTE9,
lli.ATTRIBUTE10,
lli.UNIT_OF_MEASURE_CLASS,
lli.ENCUMBER_NOW,
lli.ATTRIBUTE11,
lli.ATTRIBUTE12,
lli.ATTRIBUTE13,
lli.ATTRIBUTE14,
lli.ATTRIBUTE15,
lli.INSPECTION_REQUIRED_FLAG,
lli.RECEIPT_REQUIRED_FLAG,
lli.QTY_RCV_TOLERANCE,
lli.QTY_RCV_EXCEPTION_CODE,
lli.ENFORCE_SHIP_TO_LOCATION_CODE,
lli.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
lli.DAYS_EARLY_RECEIPT_ALLOWED,
lli.DAYS_LATE_RECEIPT_ALLOWED,
lli.RECEIPT_DAYS_EXCEPTION_CODE,
lli.INVOICE_CLOSE_TOLERANCE,
lli.RECEIVE_CLOSE_TOLERANCE,
lli.SHIP_TO_ORGANIZATION_ID,
lli.SHIPMENT_NUM,
lli.SOURCE_SHIPMENT_ID,
lli.SHIPMENT_TYPE,
lli.CLOSED_CODE,
lli.REQUEST_ID,
lli.PROGRAM_APPLICATION_ID,
lli.PROGRAM_ID,
lli.PROGRAM_UPDATE_DATE,
lli.USSGL_TRANSACTION_CODE,
lli.GOVERNMENT_CONTEXT,
lli.RECEIVING_ROUTING_ID,
lli.ACCRUE_ON_RECEIPT_FLAG,
lli.CLOSED_REASON,
lli.CLOSED_DATE,
lli.CLOSED_BY,
lli.ORG_ID,
lli.QUANTITY_SHIPPED,
lli.GLOBAL_ATTRIBUTE_CATEGORY,
lli.GLOBAL_ATTRIBUTE1,
lli.GLOBAL_ATTRIBUTE2,
lli.GLOBAL_ATTRIBUTE3,
lli.GLOBAL_ATTRIBUTE4,
lli.GLOBAL_ATTRIBUTE5,
lli.GLOBAL_ATTRIBUTE6,
lli.GLOBAL_ATTRIBUTE7,
lli.GLOBAL_ATTRIBUTE8,
lli.GLOBAL_ATTRIBUTE9,
lli.GLOBAL_ATTRIBUTE10,
lli.GLOBAL_ATTRIBUTE11,
lli.GLOBAL_ATTRIBUTE12,
lli.GLOBAL_ATTRIBUTE13,
lli.GLOBAL_ATTRIBUTE14,
lli.GLOBAL_ATTRIBUTE15,
lli.GLOBAL_ATTRIBUTE16,
lli.GLOBAL_ATTRIBUTE17,
lli.GLOBAL_ATTRIBUTE18,
lli.GLOBAL_ATTRIBUTE19,
lli.GLOBAL_ATTRIBUTE20,
lli.COUNTRY_OF_ORIGIN_CODE,
lli.TAX_USER_OVERRIDE_FLAG,
lli.MATCH_OPTION,
lli.TAX_CODE_ID,
lli.CALCULATE_TAX_FLAG,
lli.CHANGE_PROMISED_DATE_REASON,
lli.NOTE_TO_RECEIVER,
lli.SECONDARY_QUANTITY,
lli.SECONDARY_UNIT_OF_MEASURE,
lli.PREFERRED_GRADE,
lli.SECONDARY_QUANTITY_RECEIVED,
lli.SECONDARY_QUANTITY_ACCEPTED,
lli.SECONDARY_QUANTITY_REJECTED,
lli.SECONDARY_QUANTITY_CANCELLED,
lli.VMI_FLAG,
lli.CONSIGNED_FLAG,
lli.RETROACTIVE_DATE,
lli.SUPPLIER_ORDER_LINE_NUMBER,
lli.AMOUNT,
lli.AMOUNT_RECEIVED,
lli.AMOUNT_BILLED,
lli.AMOUNT_CANCELLED,
lli.AMOUNT_REJECTED,
lli.AMOUNT_ACCEPTED,
lli.DROP_SHIP_FLAG,
lli.SALES_ORDER_UPDATE_DATE,
lli.TRANSACTION_FLOW_HEADER_ID,
lli.FINAL_MATCH_FLAG,
lli.MANUAL_PRICE_CHANGE_FLAG,
lli.SHIPMENT_CLOSED_DATE,
lli.CLOSED_FOR_RECEIVING_DATE,
lli.CLOSED_FOR_INVOICE_DATE,
lli.SECONDARY_QUANTITY_SHIPPED,
lli.VALUE_BASIS,
lli.MATCHING_BASIS,
lli.PAYMENT_TYPE,
lli.DESCRIPTION,
lli.WORK_APPROVER_ID,
lli.BID_PAYMENT_ID,
lli.QUANTITY_FINANCED,
lli.AMOUNT_FINANCED,
lli.QUANTITY_RECOUPED,
lli.AMOUNT_RECOUPED,
lli.RETAINAGE_WITHHELD_AMOUNT,
lli.RETAINAGE_RELEASED_AMOUNT,
lli.AMOUNT_SHIPPED,
lli.OUTSOURCED_ASSEMBLY,
lli.TAX_ATTRIBUTE_UPDATE_CODE,
lli.ORIGINAL_SHIPMENT_ID,
lli.LCM_FLAG,
lli.UDA_TEMPLATE_ID,
lli.DRAFT_ID,
lli.CLM_PERIOD_PERF_END_DATE,
lli.CLM_PERIOD_PERF_START_DATE,
lli.REVISION_NUM,
lli.CLM_DELIVERY_PERIOD,
lli.CLM_DELIVERY_PERIOD_UOM,
lli.CLM_POP_DURATION,
lli.CLM_POP_DURATION_UOM,
lli.CLM_PROMISE_PERIOD,
lli.CLM_PROMISE_PERIOD_UOM
from CUX.CUX_PO_LINE_LOCATIONS_ALL_BACK lli
where lli.X_TRANSACTION_ID = p_transaction_id);
update cux.cux_scs_po_num_index px
set px.p_status = 'F', px.last_date = sysdate
where px.p_org_id = v_ou_id
and px.p_po_num = c_all.pricecode;
commit;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,15,SYSDATE);
p_pass := 'F';
exception
when others then
p_pass := 'F';
errbuf := nvl(errbuf, '') || '还原数据失败';
end;
raise fnd_api.g_exc_error;
exception
when others then
update cux.cux_scs_po_num_index px
set px.p_status = 'F', px.last_date = sysdate
where px.p_org_id = v_ou_id
and px.p_po_num = c_all.pricecode;
/* p_pass := 'F';
errbuf := nvl(errbuf, '') || '还原数据失败'||sqlerrm;*/
end;
end if;
begin
SELECT PA.PO_HEADER_ID
INTO v_po_header_ids
FROM PO_HEADERS_ALL PA
WHERE PA.SEGMENT1 = c_all.pricecode --'PPL2012020438'
AND PA.ORG_ID = l_org_id;
---更新价目表---
for scs_line in (select cpl.laddernumber, cpl.laddernottaxprice
from cux.cux_pba_line_scs_iface cpl
where cpl.s_transaction_id = p_transaction_id --134167761
and cpl.orgcode = 'OU_201'
and cpl.pricecode = '201171486') loop
update po_line_locations_all plla
set plla.PRICE_OVERRIDE = scs_line.laddernottaxprice
where plla.PO_HEADER_ID = v_po_header_ids
and plla.QUANTITY = scs_line.laddernumber
and plla.SHIPMENT_TYPE = 'PRICE BREAK'
and plla.PRICE_OVERRIDE <> scs_line.laddernottaxprice;
end loop;
commit;
exception
when others then
v_po_header_ids := null;
p_pass := 'F';
insert into cux_po_head_num_error values (c_all.pricecode);
end;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,16,SYSDATE);
if v_po_header_ids is not null then
-------启用多组织----2021-03-05----
begin
if l_org_id = 101 then
cux_ws_scs_op_pba_pub.insert_ga(org_id => 82,
purchasing_org => 101,
p_po_header_id => v_po_header_ids,
p_vendor_site_id => l_vendor_site_id,
p_create_id => g_user_id);
cux_ws_scs_op_pba_pub.insert_ga(org_id => 83,
purchasing_org => 101,
p_po_header_id => v_po_header_ids,
p_vendor_site_id => l_vendor_site_id,
p_create_id => g_user_id);
cux_ws_scs_op_pba_pub.insert_ga(org_id => 441,
purchasing_org => 101,
p_po_header_id => v_po_header_ids,
p_vendor_site_id => l_vendor_site_id,
p_create_id => g_user_id);
--- 新增加106组织
cux_ws_scs_op_pba_pub.insert_ga(org_id => 541,
purchasing_org => 101,
p_po_header_id => v_po_header_ids,
p_vendor_site_id => l_vendor_site_id,
p_create_id => g_user_id);
---- 702组织
cux_ws_scs_op_pba_pub.insert_ga(org_id => 709,
purchasing_org => 101,
p_po_header_id => v_po_header_ids,
p_vendor_site_id => l_vendor_site_id,
p_create_id => g_user_id);
end if;
exception
when others then
null;
end;
commit;
--- 存在价目表
mo_global.init('PO');
mo_global.set_policy_context('S', l_org_id);
/*fnd_global.apps_initialize(user_id => 1170,
resp_id => 50821,
resp_appl_id => 201);*/
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,17,SYSDATE);
if p_pass = 'T' then
po_document_action_pvt.do_approve(p_document_id => v_po_header_ids,
p_document_type => 'PO',
p_document_subtype => 'STANDARD',
p_note => 'success',
p_approval_path_id => 1,
x_return_status => v_return_status_s,
x_exception_msg => l_exception_msg_s);
if v_return_status_s <> 'S' then
/* p_pass := 'F';
p_validate := nvl(p_validate, '') || ',' || '价目表审批失败:' ||
c_all.pricecode;*/
begin
select pha.AUTHORIZATION_STATUS
into v_status
from po_headers_all pha
where pha.PO_HEADER_ID = v_po_header_ids;
exception
when others then
v_status := 'xxxx';
end;
if v_status <> 'APPROVED' then
update po_headers_all t
set t.authorization_status = 'INCOMPLETE',
t.last_update_date = sysdate,
t.last_updated_by = fnd_global.user_id,
t.last_update_login = fnd_global.login_id
where 1 = 1
and t.PO_HEADER_ID = v_po_header_ids;
p_pass := 'F';
p_validate := nvl(p_validate, '') || ',价目表:' ||
c_all.pricecode || '已经同步EBS未审批通过,请到EBS提交审批!';
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,18,SYSDATE);
end if;
end if;
end if;
commit;
end if;
commit;
end loop;
commit;
else
update cux.cux_scs_po_num_index px
set px.p_status = 'F', px.last_date = sysdate
where px.p_org_id = v_ou_id
and px.p_po_num = v_po_num_flag;
end if;
l_report_json := ' ';
-- l_rep_value := '[';
-- write_to_clob(l_rep_value, l_report_json);
if v_pass = 'T' AND p_pass = 'T' then
-- {"result_mes":"success"}
x_return_code := g_success;
l_report_json := '{"result_mes":"success","SCSUniqueCode":[' ||
g_SCSUniqueCode || ']}';
update cux.CUX_PBA_head_SCS_IFACE phs
set phs.ebs_flag = 'Y'
where phs.s_transaction_id = p_transaction_id;
-- {"SCSUniqueCode":[1,2,3,4,5],"SyncResult":"S","ErrorMsg":"aaaaaa"}
else
x_return_mesg := p_validate || '--' || v_validate || '--' || errbuf;
x_return_code := g_error;
l_report_json := '{"result_mes":"' || x_return_mesg ||
'","SCSUniqueCode":[' || g_SCSUniqueCode || ']}';
-- RAISE APP_EXP;
update cux.CUX_PBA_head_SCS_IFACE phs
set phs.ebs_flag = 'N'
where phs.s_transaction_id = p_transaction_id;
end if;
-- l_rep_value := ']';
--write_to_clob(l_rep_value, l_report_json);
x_response_data := cux_ws_common_utl.encode_base64(l_report_json);
begin
insert into cux_scs_pba_txt
(t_type,
return_code,
transaction_id,
create_date,
contont,
mi_contont,
po_number)
values
('A',
x_return_code,
p_transaction_id,
sysdate,
l_report_json,
x_response_data,
g_po_number);
exception
when others then
null;
end;
/* insert_ga(82, 101);
insert_ga(83, 101);
insert_ga(82, 83);
insert_ga(83, 82);*/
--x_return_code := g_success; --g_error
-- x_return_mesg := '价格更新成功:' || g_transaction_id;
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,19,SYSDATE);
v_request_id := fnd_request.submit_request('CUX',
'CUX_SCS_PO_BLANKET_CRUD_PKG',
null,
null,
false,
p_transaction_id);
INSERT INTO cux.cux_scs_op_pba_log(transaction_id,bacth_number,line_num,log_TIME)
VALUES (g_transaction_id,g_bacth_number,20,SYSDATE);
exception
when others then
rollback;
x_return_code := g_error;
x_return_mesg := x_return_mesg || sqlerrm;
l_report_json := ' ';
-- l_rep_value := '[';
write_to_clob(l_rep_value, l_report_json);
-- l_report_json := '[{"result_mes":"' || x_return_mesg || '"}';
l_report_json := '{"result_mes":"' || x_return_mesg ||
'","SCSUniqueCode":[' || g_SCSUniqueCode || ']}';
-- l_rep_value := ']';
-- write_to_clob(l_rep_value, l_report_json);
x_response_data := cux_ws_common_utl.encode_base64(l_report_json);
begin
insert into cux_scs_pba_txt
(t_type,
return_code,
transaction_id,
create_date,
contont,
mi_contont,
po_number)
values
('B',
x_return_code,
p_transaction_id,
sysdate,
l_report_json,
x_response_data,
g_po_number);
exception
when others then
null;
end;
update cux.cux_scs_po_num_index px
set px.p_status = 'F', px.last_date = sysdate
where px.p_org_id = v_ou_id
and px.p_po_num = v_po_num_flag;
update cux.CUX_PBA_head_SCS_IFACE phs
set phs.ebs_flag = 'N'
where phs.s_transaction_id = p_transaction_id;
end;
end cux_ws_scs_op_pba_pub;
标签：一揽子,number,ID,lli,API,po,EBS,line,id From： https://www.cnblogs.com/ivenlin/p/18120829