初学
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>'
);
扫一扫
1899
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
