初学
oracle报表开发笔记
-- process report
output('<HTML xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">');
output('<head>');
output('<title>库存现有量
报表</title>');
output('<style>');
output('body,table{font-size:13px;font-family:"Book Antiqua","Segoe UI",
Tahoma, "Trebuchet MS", verdana, helvetica, arial, sans-serif, Georgia;}.text {mso-number-format:"\@";}.retnum {mso-number-format:"0\.00";}');
output('</style>');
output('</head>');
output('<body>');
output('<h2 align=center><B>库存现有量
报表</B></h2>');
output('<table width=600 border=0 bordercolor=black>');
output(' <tr>');
output('<th align=right>OU:</th>');
output('<td align=left>' || g_ou_name || '</td>');
output('<th align=right>组织:</th>');
output('<td align=left>' || l_organization_name || '</td>');
output(' </tr>');
output('</table>');
output('<table width=1800 border=1 bordercolor=black>');
output(' <tr>');
output('<th rowspan=1 width=50>库存组织编码</th>');
output('<th rowspan=1 width=50>库存组织说明</th>');
output('<th rowspan=1 width=50>子库编码</th>');
output('<th rowspan=1 width=50>子库说明</th>');
output('<th rowspan=1 width=50>物品编码</th>');
output('<th rowspan=1 width=50>物品说明</th>');
output('<th rowspan=1 width=50>批次</th>');
output('<th rowspan=1 width=50>库存量 </th>');
output('<th rowspan=1 width=50>最小库存量</th>');
output('<th rowspan=1 width=50>最大库存量</th>');
output(' </tr>');
FOR cl IN (select OOD.ORGANIZATION_CODE, --库存组织编码
OOD.ORGANIZATION_NAME, --库存组织说明
MSA.SECONDARY_INVENTORY_NAME subinventory_code, --子库编码
MSA.DESCRIPTION subinventory_name, --子库说明
MSIV.SEGMENT1 item_no, --物品编码
MSIV.DESCRIPTION item_desc, --物品说明
MOQD.LOT_NUMBER, --批次
sum(MOQD.Primary_Transaction_Quantity) Primary_Transaction_Quantity, --库存量
MSIV.MIN_MINMAX_QUANTITY, --最小库存量
MSIV.MAX_MINMAX_QUANTITY --最大库存量
from mtl_onhand_quantities_detail moqd,
ORG_ORGANIZATION_DEFINITIONS OOD,
mtl_subinventories_all_v MSA,
MTL_SYSTEM_ITEMS_VL MSIV
where moqd.inventory_item_id = msiv.INVENTORY_ITEM_ID
and moqd.organization_id = msiv.ORGANIZATION_ID
and moqd.organization_id = ood.ORGANIZATION_ID
and moqd.subinventory_code = msa.SECONDARY_INVENTORY_NAME
and moqd.organization_id = msa.ORGANIZATION_ID
group by OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_NAME,
MSA.SECONDARY_INVENTORY_NAME,
MSA.DESCRIPTION,
MSIV.SEGMENT1,
MSIV.DESCRIPTION,
MOQD.LOT_NUMBER,
MSIV.MIN_MINMAX_QUANTITY,
MSIV.MAX_MINMAX_QUANTITY) LOOP
output(' <tr>');
output(' <td align=left><font size=1>' || cl.ORGANIZATION_CODE ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.ORGANIZATION_NAME ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.subinventory_code ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.subinventory_name ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.item_no ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.item_desc ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.LOT_NUMBER ||
'</font></td>');
output(' <td align=left><font size=1>' ||
cl.Primary_Transaction_Quantity || '</font></td>');
output(' <td align=left><font size=1>' || cl.MIN_MINMAX_QUANTITY ||
'</font></td>');
output(' <td align=left><font size=1>' || cl.MAX_MINMAX_QUANTITY ||
'</font></td>');
output(' </tr>');
2545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
