创建一个PLSQL报表.docx
《创建一个PLSQL报表.docx》由会员分享,可在线阅读,更多相关《创建一个PLSQL报表.docx(12页珍藏版)》请在冰豆网上搜索。
创建一个PLSQL报表
创建一个PL/SQL报表—采购订单打印
1、在PL/SQLDeveloper中,创建一个程序包:
命名为QPO_PRINT_你名字拼音的缩写_PKG:
2、删除Packagespecification中的预置代码(须保留第一行create和最后一行end的代码),然后在Packagespecification中添加代码:
PROCEDUREprint_po
(
errbufOUTVARCHAR2,
retcodeOUTNUMBER,
p_segment1INVARCHAR2
);
该过程将作为系统内PL/SQL类型并发请求的执行过程。
其中OUT类型的参数errbuf和retcode是系统要求的固定参数,所有PL/SQL类型并发请求的执行过程都必须具有这两个参数。
errbuf参数的内容将在日志文件中输出,效果与调用过程fnd_file.put_line输出文本到日志文件相同。
retcode参数的值将决定并发请求的完成状态,0为正常(默认值即为0)、1为警告、2为错误。
效果与调用过程fnd_concurrent.set_completion_status设置请求的完成状态相同。
3、删除Packagebody中的预置代码(须保留第一行create和最后一行end的代码),然后在Packagebody中添加代码:
PROCEDUREprint_po
(
errbufOUTVARCHAR2,
retcodeOUTNUMBER,
p_segment1INVARCHAR2
)IS
CURSORc_po_headerIS
SELECTph.segment1,
ments,
pv.vendor_name,
pvs.vendor_site_code,
ppx.last_name,
hlb.location_codebill_to_location_code,
hls.location_codeship_to_location_code,
po_headers_sv3.get_po_status(ph.po_header_id)status
FROMpo_headers_allph,
po_vendorspv,
po_vendor_sites_allpvs,
hr_locations_allhlb,
hr_locations_allhls,
per_people_xppx
WHEREph.segment1=p_segment1
ANDph.vendor_id=pv.vendor_id(+)
ANDph.vendor_site_id=pvs.vendor_site_id(+)
ANDph.bill_to_location_id=hlb.location_id(+)
ANDph.ship_to_location_id=hls.location_id(+)
ANDph.agent_id=ppx.person_id;
CURSORc_distributionsIS
SELECTpl.line_num||'-'||pll.shipment_num||'-'||pd.distribution_numline_number,
msi.segment1item_num,
pl.item_description,
pl.unit_meas_lookup_code,
pl.unit_price,
mp.organization_code,
pd.quantity_ordered,
to_char(pll.need_by_date,
'YYYY/MM/DD')need_by_date,
ppa.segment1project_number,
pt.task_number,
pd.expenditure_type,
to_char(pd.expenditure_item_date,
'YYYY/MM/DD')expenditure_item_date,
gcc1.segment1||'.'||gcc1.segment2||'.'||gcc1.segment3||'.'||
gcc1.segment4||'.'||gcc1.segment5||'.'||gcc1.segment6||'.'||
gcc1.segment7expense_acct_code,
gcc2.segment1||'.'||gcc2.segment2||'.'||gcc2.segment3||'.'||
gcc2.segment4||'.'||gcc2.segment5||'.'||gcc2.segment6||'.'||
gcc2.segment7accrual_acct_code,
gcc3.segment1||'.'||gcc3.segment2||'.'||gcc3.segment3||'.'||
gcc3.segment4||'.'||gcc3.segment5||'.'||gcc3.segment6||'.'||
gcc3.segment7budget_acct_code
FROMpo_headers_allph,
po_lines_allpl,
po_line_locations_allpll,
po_distributions_allpd,
pa_projects_allppa,
pa_taskspt,
mtl_parametersmp,
mtl_parametersmp_mst,
mtl_system_items_bmsi,
gl_code_combinationsgcc1,
gl_code_combinationsgcc2,
gl_code_combinationsgcc3
WHEREph.segment1=p_segment1
ANDpl.po_header_id=ph.po_header_id
ANDpl.po_line_id=pll.po_line_id
ANDpll.line_location_id=pd.line_location_id
ANDpll.ship_to_organization_id=mp.organization_id
ANDmp_mst.organization_code='MST'
ANDmsi.inventory_item_id(+)=pl.item_id
AND(msi.organization_idISNULLORmsi.organization_id=mp_mst.organization_id)
ANDpd.project_id=ppa.project_id(+)
ANDpd.task_id=pt.task_id(+)
ANDpd.code_combination_id=gcc1.code_combination_id(+)
ANDpd.accrual_account_id=gcc2.code_combination_id(+)
ANDpd.budget_account_id=gcc3.code_combination_id(+)
ORDERBYpl.line_num,
pll.shipment_num,
pd.distribution_num;
BEGIN
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'
');
fnd_file.put_line(fnd_file.output,'');
fnd_file.put_line(fnd_file.output,'
');
FORr_po_headerINc_po_headerLOOP
fnd_file.put_line(fnd_file.output,'9pt">');
fnd_file.put_line(fnd_file.output,'
');fnd_file.put_line(fnd_file.output,'订单编号:
');
fnd_file.put_line(fnd_file.output,''||nvl(r_po_header.segment1,
' ')||'');
fnd_file.put_line(fnd_file.output,'状态:
');
fnd_file.put_line(fnd_file.output,''||nvl(r_po_header.status,
' ')||'');
fnd_file.put_line(fnd_file.output,'
');
fnd_file.put_line(fnd_file.output,'
');fnd_file.put_line(fnd_file.output,'订单说明:
');
fnd_file.put_line(fnd_file.output,''||nvl(r_po_ments,
' ')||'');
fnd_file.put_line(fnd_file.output,'采购员:
');
fnd_file.put_line(fnd_file.output,''||nvl(r_po_header.last_name,
' ')||'');
fnd_file.put_line(fnd_file.output,'
');
fnd_file.put_line(fnd_file.output,'
');fnd_file.put_line(fnd_file.output,'供应商:
');
fnd_file.put_line(fnd_file.output,''||nvl(r_po_header.vendor_name,
' ')||'');
fnd_file.put_line(fnd_file.output,'
展开阅读全文
相关搜索