create or replace package cux_adel_po_order as
procedure main(errbuf out varchar2,
retcode out number,
p_org_id in number,
p_po_number_from in varchar2,
p_po_number_to in varchar2,
p_vendor_from in number,
p_vendor_to in number,
p_buyer in varchar2,
p_create_date_from in varchar2,
p_create_date_to in varchar2);
end cux_adel_po_order;
/
create or replace package body cux_adel_po_order is
procedure main(errbuf out varchar2, --错误代码
retcode out number, --1代表正常，2代表报警，3代表报错
p_org_id in number,
p_po_number_from in varchar2,
p_po_number_to in varchar2,
/*p_vendor_from in varchar2,
p_vendor_to in varchar2,*/ --change by liyao
p_vendor_from in number,
p_vendor_to in number,
p_buyer in varchar2,
p_create_date_from in varchar2,
p_create_date_to in varchar2) as
v_org_id number := p_org_id;
v_po_number_from varchar2(20) := p_po_number_from;
v_po_number_to varchar2(20) := p_po_number_to;
/*v_vendor_from varchar2(240):=p_vendor_from;
v_vendor_to varchar2(240):=p_vendor_to;*/ --change by liyao
v_vendor_from number := p_vendor_from;
v_vendor_to number := p_vendor_to;
v_buyer varchar2(30) := p_buyer;
v_create_date_from date := to_date(p_create_date_from,
'yyyy/mm/dd hh24:mi:ss');
v_create_date_to date := to_date(p_create_date_to,
'yyyy/mm/dd hh24:mi:ss');
v_org_name varchar2(240);
v_count number;
v_nbsp varchar2(10) := '&' || 'nbsp;';
begin
fnd_file.PUT_LINE(fnd_file.LOG,
'**************************************************************');
fnd_file.PUT_LINE(fnd_file.LOG,
'程序开始运行 ' ||
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
fnd_file.PUT_LINE(fnd_file.LOG, p_create_date_from);
--获取组织名称信息
begin
select hrou.name
into v_org_name
from hr_all_organization_units hrou
where hrou.organization_id = v_org_id;
exception
when others then
v_org_name := '';
fnd_file.PUT_LINE(fnd_file.LOG,
'错误：获取组织名称时出现错误，程序退出。');
end;
v_org_name := substr(v_org_name, instr(v_org_name, '_', -1) + 1);
--输出标头
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>采购订单</title>
<STYLE type=text/css>
.sub_title{
FONT-WEIGHT: bold;
FONT-SIZE: 4mm;
VERTICAL-ALIGN: middle;
TEXT-ALIGN: center;
background-color: #ffff66
}
.context {
font-size: 12px;
BORDER-TOP-WIDTH: 0.6mm;
PADDING-RIGHT: 1mm;
PADDING-LEFT: 1mm;
BORDER-LEFT-WIDTH: 0.6mm;
BORDER-BOTTOM-WIDTH: 0.6mm;
PADDING-BOTTOM: 0mm;
PADDING-TOP: 0mm;
BORDER-COLLAPSE: collapse;
BORDER-RIGHT-WIDTH: 0.6mm
}
.context td{
border:1px solid #009900;
}
</STYLE>
</head>');
--输出体标识.
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'<body>
<div align="center">');
--输出标题:
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'<table width="1020" border="0" align="center">
<tr>
<td><div align="center"><font size="3"><strong>' ||
v_org_name ||
'采购订单</strong></font></div></td>
</tr>
</table>');
for v_vendor in (select distinct pha.org_id,
pha.po_header_id,
pha.vendor_id,
nvl(pov.segment1, v_nbsp) vendor_number,
nvl(pov.vendor_name, v_nbsp) vendor_name,
nvl(per.full_name, v_nbsp) buyer,
nvl(pvs.address_line1, v_nbsp) address
from po_headers_all pha,
per_people_v7 per,
po_vendors pov,
po_vendor_sites_all pvs
where 1 = 1
and pha.org_id = v_org_id
and pha.authorization_status = 'APPROVED'
and nvl(pha.cancel_flag, 'N') = 'N'
and pha.segment1 >=
nvl(v_po_number_from, pha.segment1)
and pha.segment1 <=
nvl(v_po_number_to, pha.segment1)
and pha.creation_date >=
nvl(v_create_date_from, pha.creation_date)
and pha.creation_date <=
nvl(v_create_date_to, pha.creation_date)
and pha.agent_id = per.person_id(+)
and per.full_name = nvl(v_buyer, per.full_name)
and pha.vendor_id = pov.vendor_id
/*and pov.vendor_name>=nvl(v_vendor_from,pov.vendor_name)
and pov.vendor_name<=nvl(v_vendor_to,pov.vendor_name)*/ --change by liyao
and pov.vendor_id >=
nvl(v_vendor_from, pov.vendor_id)
and pov.vendor_id <= nvl(v_vendor_to, pov.vendor_id)
and pvs.vendor_id = pov.vendor_id
and pvs.org_id = pha.org_id) loop
--输出供应商信息
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'<table width="1020" border="0" align="center">
<tr>
<td width="82"><strong><font size="2">供应商编号:</font></strong></td>
<td width="403"><font size="2"><strong>' ||
v_vendor.vendor_number ||
'</strong></font></td>
<td width="29"><font size="2"> </font></td>
<td width="99"><font size="2"><div align="right"><strong>采购员:</strong></div></font></td>
<td width="370"><font size="2"><strong>' ||
v_vendor.buyer ||
'</strong></font></td>
<td width="15"> </td>
</tr>
<tr>
<td><font size="2"><strong>供应商名称:</strong></font></td>
<td><font size="2"><strong>' ||
v_vendor.vendor_name ||
'</strong></font></td>
<td><font size="2"> </font></td>
<td height="18"><font size="2"><div align="right"><strong>供应商地址:</strong></div></font></td>
<td><font size="2"><strong>' ||
v_vendor.address ||
'</strong></font></td>
<td> </td>
</tr>
</table>');
--输出具体数据
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'<table width="100%" border="1" align="center" borderColor=#009900 class="context">
<tr>
<td width="55" height="16"> <div align="center"><strong>创建日期</strong></div></td>
<td width="76"><div align="center"><strong>采购单号</strong></div></td>
<td width="29"><div align="center"><strong>行号</strong></div></td>
<td width="118"><div align="center"><strong>物料</strong></div></td>
<td width="123"><div align="center"><strong>物料说明</strong></div></td>
<td width="119"><div align="center"><strong>长说明</strong></div></td>
<td width="39"><div align="center"><strong>数量</strong></div></td>
<td width="26"><div align="center"><strong>单位</strong></div></td>
<td width="42"><div align="center"><strong>重量</strong></div></td>
<td width="60"><div align="center"><strong>交货日期</strong></div></td>
<td width="114"><div align="center"><strong>说明</strong></div></td>
<td width="100"><div align="center"><strong>备注</strong></div></td>
</tr>');
--------- v_count := 0; -----------????
for v_po in (select to_char(pha.creation_date, 'yy-mm-dd') create_date,
pha.segment1 po_number,
pla.line_num,
msib.segment1 item_number,
msib.description item_desc,
nvl(trim(msit.long_description), v_nbsp) long_desc,
--to_char(pla.quantity) quantity,
to_char(pll.quantity) quantity,
uom.unit_of_measure uom,
nvl(to_char(pll.quantity * msib.unit_weight),
v_nbsp) weight,
to_char(pll.need_by_date, 'yy-mm-dd') need_by_date,
nvl(trim(pla.supplier_ref_number), v_nbsp) comments,
nvl(trim(pha.comments), v_nbsp) po_desc
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all pll,
mtl_system_items_b msib,
mtl_system_items_tl msit,
mtl_units_of_measure uom
where 1 = 1
and pha.org_id = v_vendor.org_id
and pha.po_header_id = v_vendor.po_header_id
and pha.po_header_id = pla.po_header_id
and pha.org_id = pla.org_id
--and (pla.cancel_date is null or pla.cancel_date >=sysdate)
and nvl(pla.cancel_flag, 'N') != 'Y'
and pla.org_id = pll.org_id
and pla.po_header_id = pll.po_header_id
and pla.po_line_id = pll.po_line_id
--and nvl(pll.closed_code,'OPEN') ='OPEN'
and pll.ship_to_organization_id = msib.organization_id
and pla.item_id = msib.inventory_item_id
and msib.organization_id = msit.organization_id
and msib.inventory_item_id = msit.inventory_item_id
and msit.language = userenv('LANG')
and pla.unit_meas_lookup_code = uom.UNIT_OF_MEASURE
order by pha.creation_date, pha.segment1, pla.line_num) loop
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'
<tr>
<td height="16"> <div align="center">' ||
v_po.create_date ||
'</div></td>
<td><div align="center">' ||
v_po.po_number ||
'</div></td>
<td><div align="center">' ||
v_po.line_num ||
'</div></td>
<td><div align="left">' ||
v_po.item_number ||
'</div></td>
<td><div align="left">' ||
v_po.item_desc ||
'</div></td>
<td><div align="left">' ||
v_po.long_desc ||
'</div></td>
<td><div align="right">' ||
v_po.quantity ||
'</div></td>
<td><div align="center">' ||
v_po.uom ||
'</div></td>
<td><div align="right">' ||
v_po.weight ||
'</div></td>
<td><div align="center">' ||
v_po.need_by_date ||
'</div></td>
<td><div align="left">' ||
v_po.comments ||
'</div></td>
<td><div align="left">' ||
v_po.po_desc ||
'</div></td>
</tr>');
------------ v_count := v_count+1;-------------????
end loop;
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'
</table>');
--输出表尾
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'<table width="1020" border="0">
<tr>
<td width="107"><div align="right"><strong><font size="2">制单人:</font></strong></div></td>
<td width="227"> </td>
<td width="111"><div align="right"><strong><font size="2">制单时间:</font></strong></div></td>
<td width="290"> </td>
<td width="64"> </td>
<td width="62"> </td>
<td width="129"> </td>
</tr>
</table>');
--------- fnd_file.PUT_LINE(fnd_file.OUTPUT,v_count); ----------????
end loop;
--输出页尾
fnd_file.PUT_LINE(fnd_file.OUTPUT, '</div> </body> </html>');
exception
when others then
fnd_file.PUT_LINE(fnd_file.OUTPUT,
'错误：执行报表输出时出现错误，错误信息为：' || sqlerrm || chr(10) ||
sqlcode);
end;
end cux_adel_po_order;
1988
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
