创建一个PLSQL报表

创建一个PL/SQL报表采购订单打印1、 在PL/SQL Developer中，创建一个程序包：命名为QPO_PRINT_你名字拼音的缩写_PKG：2、 删除Package specification中的预置代码（须保留第一行create和最后一行end的代码），然后在Package specification中添加代码： PROCEDURE print_po ( errbuf OUT VARCHAR2, retcode OUT NUMBER, p_segment1 IN VARCHAR2 );该过程将作为系统内PL/SQL类型并发请求的执行过程。其中OUT类型的参数errbuf和retcode是系统要求的固定参数，所有PL/SQL类型并发请求的执行过程都必须具有这两个参数。errbuf参数的内容将在日志文件中输出，效果与调用过程fnd_file.put_line输出文本到日志文件相同。retcode参数的值将决定并发请求的完成状态，0为正常（默认值即为0）、1为警告、2为错误。效果与调用过程fnd_concurrent.set_completion_status设置请求的完成状态相同。3、 删除Package body中的预置代码（须保留第一行create和最后一行end的代码），然后在Package body中添加代码： PROCEDURE print_po ( errbuf OUT VARCHAR2, retcode OUT NUMBER, p_segment1 IN VARCHAR2 ) IS CURSOR c_po_header IS SELECT ph.segment1, ments, pv.vendor_name, pvs.vendor_site_code, ppx.last_name, hlb.location_code bill_to_location_code, hls.location_code ship_to_location_code, po_headers_sv3.get_po_status(ph.po_header_id) status FROM po_headers_all ph, po_vendors pv, po_vendor_sites_all pvs, hr_locations_all hlb, hr_locations_all hls, per_people_x ppx WHERE ph.segment1 = p_segment1 AND ph.vendor_id = pv.vendor_id(+) AND ph.vendor_site_id = pvs.vendor_site_id(+) AND ph.bill_to_location_id = hlb.location_id(+) AND ph.ship_to_location_id = hls.location_id(+) AND ph.agent_id = ppx.person_id; CURSOR c_distributions IS SELECT pl.line_num | - | pll.shipment_num | - | pd.distribution_num line_number, msi.segment1 item_num, pl.item_description, pl.unit_meas_lookup_code, pl.unit_price, anization_code, pd.quantity_ordered, to_char(pll.need_by_date, YYYY/MM/DD) need_by_date, ppa.segment1 project_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.segment7 expense_acct_code, gcc2.segment1 | . | gcc2.segment2 | . | gcc2.segment3 | . | gcc2.segment4 | . | gcc2.segment5 | . | gcc2.segment6 | . | gcc2.segment7 accrual_acct_code, gcc3.segment1 | . | gcc3.segment2 | . | gcc3.segment3 | . | gcc3.segment4 | . | gcc3.segment5 | . | gcc3.segment6 | . | gcc3.segment7 budget_acct_code FROM po_headers_all ph, po_lines_all pl, po_line_locations_all pll, po_distributions_all pd, pa_projects_all ppa, pa_tasks pt, mtl_parameters mp, mtl_parameters mp_mst, mtl_system_items_b msi, gl_code_combinations gcc1, gl_code_combinations gcc2, gl_code_combinations gcc3 WHERE ph.segment1 = p_segment1 AND pl.po_header_id = ph.po_header_id AND pl.po_line_id = pll.po_line_id AND pll.line_location_id = pd.line_location_id AND pll.ship_to_organization_id = anization_id AND mp_anization_code = MST AND msi.inventory_item_id(+) = pl.item_id AND (anization_id IS NULL OR anization_id = mp_anization_id) AND ject_id = ject_id(+) AND pd.task_id = pt.task_id(+) AND pd.code_combination_id = gcc1.code_combination_id(+) AND pd.accrual_account_id = gcc2.code_combination_id(+) AND pd.budget_account_id = gcc3.code_combination_id(+) ORDER BY pl.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, ); FOR r_po_header IN c_po_header LOOP 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.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, 收单地点：); fnd_file.put_line(fnd_file.output, | nvl(r_po_header.bill_to_location_code,  ) | ); 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_site_code,  ) | ); fnd_file.put_line(fnd_file.output, 收货地点：); fnd_file.put_line(fnd_file.output, | nvl(r_po_header.ship_to_location_code,  ) | ); 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, 物料编码); 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, 发运组织); 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, 任务编号); 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, ); 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, ); FOR r_distributions IN c_distributions LOOP fnd_file.put_line(fnd_file.output, ); fnd_file.put_line(fnd_file.output, | r_distributions.line_number | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.item_num,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.item_description,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.unit_meas_lookup_code,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(to_char(r_distributions.unit_price),  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_anization_code,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(to_char(r_distributions.quantity_ordered),  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.need_by_date,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_ject_number,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.task_number,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.expenditure_type,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.expenditure_item_date,  ) | ); fnd_file.put_line(fnd_file.output, ); fnd_file.put_line(fnd_file.output, ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.expense_acct_code,  ) | ); fnd_file.put_line(fnd_file.output, | nvl(r_distributions.accrual_acct_c