- 表名
- Interface：rcv_transactions_interface
- Interface：rcv_headers_interface
- 客制:spo_ep_arn_ch01d1（存放ASN资料）
- 客制：sep_po_asn_list（ASN资料SYNC）
- 序列
- rcv_headers_interface_s.nextval
- rcv_interface_groups_s.nextval
- APIs&Request
- PO模組：Receiving Transaction Processor
參考：
- 案例
- 创建Manage Shipments（ASN）
procedure confirmed_asn(errbuf out varchar2,
retcode out varchar2,
p_org_id in number) as
l_po_num po_headers_all.segment1%type;
l_line_num po_lines_all.line_num%type;
l_shipment_num po_line_locations_all.shipment_num%type;
l_h_rec rcv_headers_interface%rowtype;
l_l_rec rcv_transactions_interface%rowtype;
l_organization_id number;
l_opo_quantity number;
l_group_id number;
l_request_id number;
l_cnt number;
l_d6_rec spo_ep_d6%rowtype;
l_d6_cnt number;
l_return_status varchar2(1);
l_msg_data varchar2(2000);
l_exp exception;
l_weeks varchar2(10);
l_r_shipment_num varchar2(64);
l_p_asn_no varchar2(64);
l_r_asn_no varchar2(64);
l_r_seq_no varchar2(64);
l_asn_no varchar2(64);
l_seq_no varchar2(64);
l_rank number;
cursor c_sh is
select nvl(d1.data_source,'EP1.0') data_source, --资料来源
d1.corp_no,--Org Code
d1.asn_no,--ASN 号码
pav.vendor_id,
trim(d1.vendor_no) vendor_no,--供应商代码
d1.reply_date,--ETA(到货日期）
d1.invoice_no,--发票号码
d1.invoice_date,--发票日期
d1.mt_list, --料号
d1.waybill_airbill_num, --提货单/送货单
row_number() over(partition by d1.data_source,d1.corp_no,d1.asn_no order by d1.reply_date desc) rank
from spo_ep_arn_ch01d1 d1, hr_operating_units ou, ap_suppliers pav
where (d1.arn_type = 'S' and d1.epr_status = '20')
--arn_type 表示ASN资料，epr_status 表示签核流程
and trim(d1.asn_code) is null
--特结标志 (目的为了取消，有部分收料特结）
and trim(d1.asn_cancel) is null
--取消标志(目的为了取消，无收料取消）
and d1.asn_qty - d1.ch_caqty > 0
--ch_caqty 已收料数量
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id
and pav.segment1 = trim(d1.vendor_no)
and not exists (select 1
from rcv_shipment_lines
where bar_code_label = d1.asn_no
AND truck_num=d1.ep_uid
)
group by d1.data_source,
d1.corp_no,
d1.asn_no,
pav.vendor_id,
trim(d1.vendor_no),
d1.reply_date,
d1.invoice_no,
d1.invoice_date,
d1.mt_list,
d1.waybill_airbill_num
order by d1.asn_no, pav.vendor_id, d1.reply_date;
cursor c_sl( p_asn_no in varchar2,
p_item in varchar2,
p_data_source in varchar2,
p_vendor_no in varchar2,
p_reply_date in date,
p_invoice_no in varchar2,
p_invoice_date in varchar2,
p_waybill_airbill_num in varchar2) is
select d1.*
from spo_ep_arn_ch01d1 d1, hr_operating_units ou
where (d1.arn_type = 'S' and d1.epr_status = '20')
and trim(d1.asn_code) is null
and trim(d1.asn_cancel) is null
and d1.asn_qty - d1.ch_caqty > 0
and d1.asn_no = p_asn_no
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id
and d1.mt_list = p_item
and d1.data_source=p_data_source
and trim(d1.vendor_no)=trim(p_vendor_no)
and ((d1.reply_date=p_reply_date AND p_reply_date IS NOT NULL)
OR (p_reply_date IS NULL AND d1.reply_date IS NULL) )
and NVL(d1.invoice_no,'-1')=NVL(p_invoice_no,'-1')
and NVL(d1.invoice_date,'-1')=NVL(p_invoice_date,'-1')
and NVL(d1.waybill_airbill_num,'-1')=NVL(p_waybill_airbill_num,'-1')
order by d1.mt_list, d1.po_no;
begin
fnd_file.put_line(fnd_file.log, 'g_org_id:' || g_org_id);
fnd_file.put_line(fnd_file.log, 'Confirmed ASN begin :');
begin
select inventory_organization_id
into l_organization_id
from financials_system_parameters
where org_id = g_org_id;
exception
when others then
raise_application_error(-20001,
'get inventory_organization_id error:' ||
sqlerrm);
end;
fnd_file.put_line(fnd_file.log,
'l_organization_id:' || l_organization_id);
select count(1)
into l_cnt
from spo_ep_arn_ch01d1 d1, hr_operating_units ou
where (d1.arn_type = 'S' and d1.epr_status = '20')
--arn_type 表示ASN资料，epr_status 表示签核流程
and trim(d1.asn_code) is null
--特结标志
and trim(d1.asn_cancel) is null --取消标志
and d1.asn_qty - d1.ch_caqty > 0 --ch_caqty交货数量
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id
and not exists (select 1
from rcv_shipment_lines
where bar_code_label = d1.asn_no
AND truck_num=d1.ep_uid
);
if l_cnt > 0 then--该程序是定时跑，避免取无效Group ID
select rcv_interface_groups_s.nextval into l_group_id from dual;
end if;
for r_sh in c_sh loop
l_h_rec := null;
l_return_status := null;
l_msg_data := null;
savepoint sp_sh;
l_asn_no :=r_sh.asn_no;
if nvl(l_p_asn_no,'p')<> l_asn_no then
l_p_asn_no :=r_sh.asn_no;
get_asn_seq_no(x_seq_no => l_r_seq_no,
x_asn_no => l_r_asn_no,
x_shipment_num => l_r_shipment_num,
p_org_id => null,
p_asn_no => l_asn_no,
p_shipment_num => null,
p_setup => 0);
end if;
l_r_shipment_num :=l_asn_no||'-'||lpad(to_number(l_r_seq_no)+r_sh.Rank,3,'0');
select rcv_headers_interface_s.nextval
into l_h_rec.header_interface_id
from dual;
l_h_rec.group_id := l_group_id;
l_h_rec.processing_status_code := 'PENDING';
l_h_rec.receipt_source_code := 'VENDOR';
l_h_rec.asn_type := 'ASN';
l_h_rec.transaction_type := 'NEW';
l_h_rec.auto_transact_code := 'SHIP';
l_h_rec.shipped_date := sysdate;
IF r_sh.invoice_no IS NOT NULL THEN
l_h_rec.attribute5 := r_sh.invoice_no;
l_h_rec.waybill_airbill_num := r_sh.waybill_airbill_num;
l_h_rec.invoice_date := to_date(r_sh.invoice_date,'yyyy/mm/dd');
END IF;
l_h_rec.last_update_date := sysdate;
l_h_rec.last_updated_by := g_user_id;
l_h_rec.last_update_login := g_login_id;
l_h_rec.creation_date := sysdate;
l_h_rec.created_by := g_user_id;
l_h_rec.shipment_num := l_r_shipment_num;
l_h_rec.vendor_id := r_sh.vendor_id;
l_h_rec.ship_to_organization_id := l_organization_id;
if trunc(r_sh.reply_date, 'dd') <= trunc(sysdate, 'dd') then
l_h_rec.expected_receipt_date := trunc(sysdate, 'dd') + 0.99999;
else
l_h_rec.expected_receipt_date := trunc(r_sh.reply_date, 'dd') +
0.99999;
end if;
l_h_rec.org_id := g_org_id;
l_h_rec.validation_flag := 'Y';
insert into rcv_headers_interface values l_h_rec;
for r_sl in c_sl( r_sh.asn_no,
r_sh.mt_list,
r_sh.data_source ,
r_sh.vendor_no ,
r_sh.reply_date ,
r_sh.invoice_no ,
r_sh.invoice_date ,
r_sh.waybill_airbill_num ) loop
begin
l_l_rec := null;
l_po_num := null;
l_line_num := null;
l_shipment_num := null;
l_opo_quantity := null;
l_d6_rec := null;
get_po_number(p_po_no => r_sl.po_no,
x_po_num => l_po_num,
x_line_num => l_line_num,
x_shipment_num => l_shipment_num,
x_return_status => l_return_status,
x_msg_data => l_msg_data);
if l_return_status = 'E' then
l_msg_data := 'get_po_number error: po_no(' || r_sl.po_no || ')' ||
l_msg_data;
raise l_exp;
end if;
get_po_info(p_org_id => g_org_id,
p_po_num => l_po_num,
p_line_num => l_line_num,
p_shipment_num => l_shipment_num,
x_po_header_id => l_l_rec.po_header_id,
x_po_line_id => l_l_rec.po_line_id,
x_line_location_id => l_l_rec.po_line_location_id,
x_item_id => l_l_rec.item_id,
x_unit_of_measure => l_l_rec.unit_of_measure,
x_agent_id => l_d6_rec.agent_id,
x_po_creation_date => l_d6_rec.po_creation_date,
x_po_promised_date => l_d6_rec.po_promised_date,
x_opo_quantity => l_opo_quantity,
x_return_status => l_return_status,
x_msg_data => l_msg_data);
if l_return_status = 'E' then
l_msg_data := 'get_po_info error: po_no(' || r_sl.po_no || ')' ||
l_msg_data;
raise l_exp;
end if;
select rcv_transactions_interface_s.nextval
into l_l_rec.interface_transaction_id
from dual;
l_l_rec.group_id := l_h_rec.group_id;
l_l_rec.last_update_date := sysdate;
l_l_rec.last_updated_by := g_user_id;
l_l_rec.last_update_login := g_login_id;
l_l_rec.creation_date := sysdate;
l_l_rec.created_by := g_user_id;
l_l_rec.transaction_type := 'SHIP';
l_l_rec.transaction_date := sysdate;
l_l_rec.processing_status_code := 'PENDING';
l_l_rec.processing_mode_code := 'BATCH';
l_l_rec.transaction_status_code := 'PENDING';
--与PO的数量比较，取小
l_l_rec.quantity := least(l_opo_quantity,
r_sl.asn_qty);
l_l_rec.auto_transact_code := 'SHIP';
l_l_rec.receipt_source_code := 'VENDOR';
l_l_rec.source_document_code := 'PO';
l_l_rec.to_organization_id := l_organization_id;
l_l_rec.header_interface_id := l_h_rec.header_interface_id;
l_l_rec.org_id := g_org_id;
l_l_rec.validation_flag := 'Y';
l_l_rec.barcode_label := r_sl.asn_no;
l_l_rec.truck_num := to_char(r_sl.ep_uid);
insert into rcv_transactions_interface values l_l_rec;
l_return_status := 'S';
l_msg_data := null;
exception
when l_exp then
l_return_status := 'E';
exit;
when others then
l_return_status := 'E';
l_msg_data := 'Other error: po_no(' || r_sl.po_no || ')' ||
sqlerrm;
exit;
end;
end loop;
if l_return_status = 'E' then
rollback to sp_sh;
fnd_file.put_line(fnd_file.log, l_msg_data);
end if;
end loop;
if l_group_id is not null then
--Submit Request: Receiving Transaction Processor
l_request_id := fnd_request.submit_request
( 'PO' --application short name
, 'RVCTP' --concurrent short name
,null --
,null --
,false --
,'BATCH' --Transaction Processing Mode
,l_group_id --Transaction group id
, g_org_id --Operating Unit
);
commit;
--
--等待request run 完
declare
l_r_phase varchar2(100);
l_r_status varchar2(100);
l_r_dev_phase varchar2(100);
l_r_dev_status varchar2(100);
l_r_message varchar2(4000);
l_r_request_flag boolean;
begin
IF l_request_id>0 THEN
l_r_request_flag := fnd_concurrent.wait_for_request
(request_id => l_request_id
,interval => 1
,max_wait => 0
,phase => l_r_phase
,status => l_r_status
,dev_phase => l_r_dev_phase
,dev_status => l_r_dev_status
,message => l_r_message);
END IF;
if l_r_dev_phase <> 'COMPLETE' or l_r_dev_status <> 'NORMAL' then
null;
--根据需要进行写错误处理
else
-- null;
--根据需要进行后续处理
END IF;
end ;
end if;
--建议增加一个错误资料处理，这里语句参考后续语句
--confirmed_asn_error(g_org_id,l_request_id);
end confirmed_asn;
-
- 获取ASN_NO
procedure get_asn_seq_no( x_seq_no out varchar2,
x_asn_no out varchar2,
x_shipment_num out varchar2,
p_org_id in number,
p_asn_no in varchar2,
p_shipment_num in varchar2,
p_setup in number
) as
v_asn_no varchar2(64);
v_shipment_num varchar2(64);
begin
if p_asn_no is null then
select bar_code_label
into v_asn_no
from rcv_shipment_lines rsl
where rsl.shipment_header_id in ( select rsh.shipment_header_id
from rcv_shipment_headers rsh
where rsh.shipment_num like p_shipment_num
)
and bar_code_label is not null
and rownum=1;
else
v_asn_no :=p_asn_no;
end if;
--通過ASN 獲取最大shipment_num數值
select max(shipment_num)
into v_shipment_num
from ( select rsh.shipment_num
from rcv_shipment_headers rsh
where rsh.shipment_header_id in (select shipment_header_id
from rcv_shipment_lines rsl
where rsl.bar_code_label=v_asn_no)
union
select rhi.shipment_num from rcv_headers_interface rhi
where rhi.header_interface_id in ( select rti.header_interface_id
from rcv_transactions_interface rti
where rti.barcode_label=v_asn_no)
) a ;
if v_shipment_num is null then
x_seq_no :='000';
else
select nvl(lpad(substr(v_shipment_num,length(v_asn_no)+2,length(v_asn_no)),3,'0'),'000')
into x_seq_no
from dual;
end if;
select lpad(to_number(x_seq_no)+p_setup,3,'0')
into x_seq_no
from dual;
x_asn_no :=v_asn_no;
if p_setup=0 then
x_shipment_num :=x_asn_no||'-'||lpad(to_number(x_seq_no)+1,3,'0');
else
x_shipment_num :=x_asn_no||'-'||x_seq_no;
end if;
end;
-
- 获取PO单号码（get_po_number）
procedure get_po_number(p_po_no in varchar2,
x_po_num out nocopy varchar2,
x_line_num out nocopy number,
x_shipment_num out nocopy number,
x_return_status out nocopy varchar2,
x_msg_data out nocopy varchar2) as
l_po_no spo_ep_arn_ch01d1.po_no%type;
l_start number := 1;
l_length number;
l_end number;
begin
x_return_status := 'S';
for i in 1 .. 3 loop
l_end := instr(p_po_no, '_', 1, i);
if l_end = 0 then
l_length := length(p_po_no) - l_start + 1;
else
l_length := l_end - l_start;
end if;
l_po_no := substr(p_po_no, l_start, l_length);
if i = 1 then
x_po_num := l_po_no;
elsif i = 2 then
x_line_num := to_number(l_po_no);
elsif i = 3 then
x_shipment_num := to_number(l_po_no);
end if;
l_start := l_end + 1;
end loop;
if x_po_num is null or x_line_num is null or x_shipment_num is null then
x_return_status := 'E';
end if;
exception
when others then
x_return_status := 'E';
x_msg_data := sqlerrm;
end get_po_number;
-
- 获取PO信息get_po_info
procedure get_po_info(p_org_id in number,
p_po_num in varchar2,
p_line_num in number,
p_shipment_num in number,
x_po_header_id out nocopy number,
x_po_line_id out nocopy number,
x_line_location_id out nocopy number,
x_item_id out nocopy number,
x_unit_of_measure out nocopy varchar2,
x_opo_quantity out nocopy number,
x_agent_id out nocopy number,
x_po_creation_date out nocopy date,
x_po_promised_date out nocopy date,
x_return_status out nocopy varchar2,
x_msg_data out nocopy varchar2) as
begin
x_return_status := 'S';
select pha.po_header_id,
pla.po_line_id,
plla.line_location_id,
pla.item_id,
pla.unit_meas_lookup_code,
pha.agent_id,
pha.creation_date,
nvl(plla.promised_date, plla.need_by_date)
into x_po_header_id,
x_po_line_id,
x_line_location_id,
x_item_id,
x_unit_of_measure,
x_agent_id,
x_po_creation_date,
x_po_promised_date
from po_headers_all pha, po_lines_all pla, po_line_locations_all plla
where pha.segment1 = p_po_num
and pha.type_lookup_code = 'STANDARD'
and pha.org_id = p_org_id
and pla.po_header_id = pha.po_header_id
and pla.line_num = p_line_num
and plla.po_line_id = pla.po_line_id
and plla.shipment_num = p_shipment_num;
begin
select quantity
into x_opo_quantity
from mtl_supply
where supply_type_code = 'PO'
and po_header_id = x_po_header_id
and po_line_id = x_po_line_id
and po_line_location_id = x_line_location_id;
exception
when others then
x_opo_quantity := 0;
end;
exception
when others then
x_return_status := 'E';
x_msg_data := sqlerrm;
end get_po_info;
-
- 错误资料处理
SELECT rhi.group_id,
rhi.attribute5 invoice_no,
mp.organization_code,
rti.barcode_label asn_no,
pha.segment1 PO_NUM,
pla.line_num LINE_NUM,
plla.shipment_num shipment_num,
msib.segment1 item_no,
rti.quantity ep_asn_qty,
0 erp_asn_qty,
error_message
FROM Rcv_Headers_Interface rhi,
RCV_TRANSACTIONS_INTERFACE rti,
PO_INTERFACE_ERRORS pie,
mtl_system_items_b msib,
mtl_parameters mp,
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla
WHERE rhi.header_interface_id = rti.header_interface_id
AND RTI.INTERFACE_TRANSACTION_ID = PIE.INTERFACE_LINE_ID(+)
AND rti.item_id = msib.inventory_item_id
AND rti.to_organization_id = msib.organization_id
AND rti.to_organization_id = mp.organization_id
AND rti.po_header_id = pha.po_header_id
AND rti.po_line_id = pla.po_line_id
AND rti.po_line_location_id = plla.line_location_id
and rhi.group_id= 10090732 -- 10090732 为group_id，
-
- 取消Manage Shipments（ASN）
procedure close_or_cancel(errbuf out varchar2,
retcode out varchar2,
p_org_id in number) as
type ep_data_curtype is ref cursor;
l_sql_cur varchar2(4000);
c_d1 ep_data_curtype;
r_d1 spo_ep_arn_ch01d1%rowtype;
l_db_link varchar2(1000);
l_h_rec rcv_headers_interface%rowtype;
l_l_rec rcv_transactions_interface%rowtype;
l_group_id_cancel number;
l_group_id_new number;
l_cnt number;
l_closed_flag varchar2(1);
l_po_no spo_ep_arn_ch01d1.po_no%type;
l_pos number;
l_len number;
l_max number;
l_request_id number;
l_return_status varchar2(1);
l_msg_data varchar2(2000);
l_exp exception;
l_request_status boolean;
l_phase varchar2(20);
l_status varchar2(20);
l_dev_phase varchar2(20);
l_dev_status varchar2(20);
l_message varchar2(2000);
l_weeks varchar2(20);
cursor c_sh is
select d1.asn_no,d1.corp_no,NVL(d1.data_source,'EP1.0') data_source,d1.ep_uid,d1.source_seq_id--2022.08.24 BY SEN SHAN ADD ,d1.corp_no,NVL(d1.data_source,'EP1.0') data_source
from spo_ep_arn_ch01d1 d1, hr_operating_units ou
where (d1.asn_code = 'Y'
or d1.asn_cancel = 'Y')
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id
and NVL(d1.data_source,'EP1.0')='SCM'
and exists
(select 1
from rcv_shipment_lines sl,
po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
where sl.bar_code_label = d1.asn_no
and sl.truck_num=d1.ep_uid
and sl.shipment_line_status_code not in
('CANCELLED', 'FULLY RECEIVED')
and ph.po_header_id = sl.po_header_id
and pl.po_header_id = ph.po_header_id
and pl.po_line_id = sl.po_line_id
and pll.po_line_id = pl.po_line_id
and pll.line_location_id = sl.po_line_location_id
and ph.segment1 || '_' || pl.line_num || '_' ||
pll.shipment_num = d1.po_no
and not exists
(select 1
from spo_ep_arn_ch01d1 e
where e.asn_no = sl.bar_code_label
and sl.truck_num=e.ep_uid
and (e.arn_type = 'S' and e.epr_status = '20')
and NVL(e.data_source,'EP1.0')='SCM'
and trim(e.asn_code) is null
and trim(e.asn_cancel) is null
and e.asn_qty - e.ch_caqty > 0
and e.po_no = ph.segment1 || '_' || pl.line_num || '_' ||
pll.shipment_num))
group by d1.asn_no,d1.corp_no,NVL(d1.data_source,'EP1.0'),d1.ep_uid,d1.source_seq_id
;
cursor c_sl(p_asn_no in varchar2,p_ep_uid in varchar2) is
select sl.*
from rcv_shipment_lines sl
where sl.bar_code_label = p_asn_no
and (sl.truck_num =p_ep_uid or p_ep_uid is null)
and sl.shipment_line_status_code not in
('CANCELLED', 'FULLY RECEIVED');
begin
fnd_file.put_line(fnd_file.log, 'g_org_id:' || g_org_id);
fnd_file.put_line(fnd_file.log, 'Colse Or Cancel begin :'); 3
select count(1)
into l_cnt
from spo_ep_arn_ch01d1 d1, hr_operating_units ou
where trim(d1.erp_code) is null
and (d1.asn_code = 'Y' --特結
or d1.asn_cancel = 'Y') --取消
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id
and exists (select 1
from rcv_shipment_lines l
where l.bar_code_label = d1.asn_no
and l.shipment_line_status_code not in
('CANCELLED', 'FULLY RECEIVED'));
if l_cnt > 0 then
select rcv_interface_groups_s.nextval
into l_group_id_cancel
from dual;
select rcv_interface_groups_s.nextval into l_group_id_new from dual;
fnd_file.put_line(fnd_file.log, 'run to close_cancel_d6:Y');
close_cancel_d6;
end if;
fnd_file.put_line(fnd_file.log, 'run to LOOP:Y');
--開始處理動作
for r_sh in c_sh loop
l_h_rec := null;
l_pos := null;
l_len := null;
savepoint sp_cc;
for r_sl in c_sl(r_sh.asn_no,r_sh.ep_uid) loop
begin
l_l_rec := null;
l_closed_flag := null;
l_po_no := null;
l_return_status := null;
l_msg_data := null;
begin
select pha.segment1 || '_' || pla.line_num || '_' ||
plla.shipment_num
into l_po_no
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla
where pha.po_header_id = r_sl.po_header_id
and pla.po_header_id = pha.po_header_id
and pla.po_line_id = r_sl.po_line_id
and plla.po_line_id = pla.po_line_id
and plla.line_location_id = r_sl.po_line_location_id;
exception
when others then
l_msg_data := 'get l_po_no error: shipment_line_id(' ||
r_sl.shipment_line_id || ')' || sqlerrm;
raise l_exp;
end;
begin
select distinct 'Y'
into l_closed_flag
from spo_ep_arn_ch01d1 d1, hr_operating_units ou
where trim(d1.erp_code) is null
and (d1.asn_code = 'Y'
or d1.asn_cancel = 'Y')
and d1.asn_no = r_sl.bar_code_label
and d1.po_no = l_po_no
and to_char(ou.organization_id) = d1.corp_no
and ou.organization_id = g_org_id;
exception
when no_data_found then
l_closed_flag := 'N';
when others then
l_msg_data := 'get l_closed_flag error: shipment_line_id(' ||
r_sl.shipment_line_id || ')' || sqlerrm;
raise l_exp;
end;
rcv_insert_rti_sv.insert_into_rti
(
x_group_id => l_group_id_cancel,
x_transaction_type => 'CANCEL',
x_transaction_date => sysdate,
x_processing_status_code => 'PENDING',
x_processing_mode_code => 'BATCH',
x_transaction_status_code => 'PENDING',
x_last_update_date => sysdate,
x_last_updated_by => g_user_id,
x_last_update_login => g_login_id,
x_interface_source_code => 'RCV',
x_creation_date => sysdate,
x_created_by => g_user_id,
x_auto_transact_code => 'CANCEL',
x_receipt_source_code => 'VENDOR',
x_po_header_id => r_sl.po_header_id,
x_po_release_id => r_sl.po_release_id,
x_po_line_id => r_sl.po_line_id,
x_shipment_line_id => r_sl.shipment_line_id,
x_shipment_header_id => r_sl.shipment_header_id,
x_po_line_location_id => r_sl.po_line_location_id,
x_deliver_to_location_id => r_sl.deliver_to_location_id,
x_to_organization_id => r_sl.to_organization_id,
x_item_id => r_sl.item_id,
x_quantity_shipped => r_sl.quantity_shipped,
x_source_document_code => r_sl.source_document_code,
x_category_id => r_sl.category_id,
x_unit_of_measure => r_sl.unit_of_measure,
x_item_description => r_sl.truck_num,
x_employee_id => r_sl.employee_id,
x_destination_type_code => r_sl.destination_type_code,
x_destination_context => r_sl.destination_context,
x_subinventory => r_sl.to_subinventory,
x_routing_header_id => r_sl.routing_header_id,
x_primary_unit_of_measure => null,
x_ship_to_location_id => r_sl.ship_to_location_id,
x_vendor_id => null,
p_operating_unit_id => g_org_id);
if l_closed_flag = 'N' then
if l_h_rec.header_interface_id is null then
select rcv_headers_interface_s.nextval
into l_h_rec.header_interface_id
from dual;
begin
select vendor_id,
ship_to_org_id,
expected_receipt_date,
shipment_num
into l_h_rec.vendor_id,
l_h_rec.ship_to_organization_id,
l_h_rec.expected_receipt_date,
l_h_rec.shipment_num
from rcv_shipment_headers
where shipment_header_id = r_sl.shipment_header_id;
exception
when others then
l_msg_data := 'get rcv_shipment_headers error: shipment_line_id(' ||
r_sl.shipment_line_id || ')' || sqlerrm;
raise l_exp;
end;
get_asn_seq_no(x_seq_no => l_r_seq_no,
x_asn_no => l_r_asn_no,
x_shipment_num => l_r_shipment_num,
p_org_id => null,
p_asn_no => null,
p_shipment_num => l_h_rec.shipment_num,
p_setup => 1);
l_h_rec.shipment_num :=l_r_shipment_num;
if l_h_rec.shipment_num is null then
l_msg_data := 'shipment_line_id(' || r_sl.shipment_line_id || ')' ||
'l_h_rec.shipment_num is invaild or missing';
raise l_exp;
end if;
if l_h_rec.expected_receipt_date <= trunc(sysdate, 'dd') then
l_h_rec.expected_receipt_date := trunc(sysdate, 'dd') + 1;
end if;
l_h_rec.group_id := l_group_id_new;
l_h_rec.processing_status_code := 'PENDING';
l_h_rec.receipt_source_code := 'VENDOR';
l_h_rec.asn_type := 'ASN';
l_h_rec.transaction_type := 'NEW';
l_h_rec.auto_transact_code := 'SHIP';
l_h_rec.shipped_date := sysdate;
l_h_rec.last_update_date := sysdate;
l_h_rec.last_updated_by := g_user_id;
l_h_rec.last_update_login := g_login_id;
l_h_rec.creation_date := sysdate;
l_h_rec.created_by := g_user_id;
l_h_rec.org_id := g_org_id;
l_h_rec.validation_flag := 'Y';
insert into rcv_headers_interface values l_h_rec;
end if;
select rcv_transactions_interface_s.nextval
into l_l_rec.interface_transaction_id
from dual;
l_l_rec.group_id := l_h_rec.group_id;
l_l_rec.last_update_date := sysdate;
l_l_rec.last_updated_by := g_user_id;
l_l_rec.last_update_login := g_login_id;
l_l_rec.creation_date := sysdate;
l_l_rec.created_by := g_user_id;
l_l_rec.transaction_type := 'SHIP';
l_l_rec.transaction_date := sysdate;
l_l_rec.processing_status_code := 'PENDING';
l_l_rec.processing_mode_code := 'BATCH';
l_l_rec.transaction_status_code := 'PENDING';
l_l_rec.quantity := r_sl.quantity_shipped -
r_sl.quantity_received;
l_l_rec.auto_transact_code := 'SHIP';
l_l_rec.receipt_source_code := 'VENDOR';
l_l_rec.source_document_code := 'PO';
l_l_rec.to_organization_id := r_sl.to_organization_id;
l_l_rec.header_interface_id := l_h_rec.header_interface_id;
l_l_rec.org_id := g_org_id;
l_l_rec.validation_flag := 'Y';
l_l_rec.barcode_label := r_sl.bar_code_label;
l_l_rec.truck_num := r_sl.truck_num;
l_l_rec.po_header_id := r_sl.po_header_id;
l_l_rec.po_line_id := r_sl.po_line_id;
l_l_rec.po_line_location_id := r_sl.po_line_location_id;
insert into rcv_transactions_interface values l_l_rec;
end if;
l_return_status := 'S';
exception
when l_exp then
l_return_status := 'E';
exit;
when others then
l_return_status := 'E';
l_msg_data := 'Other error: shipment_line_id(' ||
r_sl.shipment_line_id || ')' || sqlerrm;
exit;
end;
end loop;--shipment
if l_return_status = 'E' then
rollback to sp_cc;
fnd_file.put_line(fnd_file.log, l_msg_data);
end if;
end loop;--d1
if l_group_id_cancel is not null then
--Submit Request: Receiving Transaction Processor
l_request_id := fnd_request.submit_request
('PO' --application short name
,'RVCTP' --concurrent short name
,null --
,null --
,false --
,'BATCH' --Transaction Processing Mode
,l_group_id_cancel --Transaction group id
,g_org_id --Operating Unit
);
commit;
if l_request_id <> 0 then
l_request_status := fnd_concurrent.wait_for_request
( request_id => l_request_id,
interval => 1,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
message => l_message
);
if l_dev_status = 'NORMAL' and l_group_id_new is not null then
--Submit Request: Receiving Transaction Processor
l_request_id := fnd_request.submit_request
('PO' --application short name
,'RVCTP' --concurrent short name
,null
,null
,false
,'BATCH' --Transaction Processing Mode
,l_group_id_new --Transaction group id
,g_org_id --Operating Unit
);
commit;
--
--等待request run 完
declare
l_r_phase varchar2(100);
l_r_status varchar2(100);
l_r_dev_phase varchar2(100);
l_r_dev_status varchar2(100);
l_r_message varchar2(4000);
l_r_request_flag boolean;
begin
IF l_request_id>0 THEN
l_r_request_flag := fnd_concurrent.wait_for_request
(request_id => l_request_id
,interval => 1
,max_wait => 0
,phase => l_r_phase
,status => l_r_status
,dev_phase => l_r_dev_phase
,dev_status => l_r_dev_status
,message => l_r_message);
END IF;
if l_r_dev_phase <> 'COMPLETE' or l_r_dev_status <> 'NORMAL' then
--根据需要进行写错误处理
else
-- null;
--根据需要进行后续处理
END IF;
end ;
end if;
end if;
end if;
end close_or_cancel;
- 其他说明
880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
