即时打印SQL包代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】即时打印SQL包
作者:Dezai.CN / 发布于2013/4/8/ 1628

	create or replace package body CUX_17_GME_FORMULA_PKG is
	 /*================================================================*
	 | FUNCTION:
	 | cux_17_gme_print_FORMULA
	 |
	 | Description:
	 | 配方信息汇总表
	 |
	 | Out Parameters:
	 | 无
	 |
	 | In Parameters:
	 | p_in_status 状态
	 ,p_in_formula 配方
	 ,p_in_version 版本
	 ,p_in_description 说明
	 ,p_in_product 产品
	 ,p_in_formula_class 配方分类
	 ,p_in_orgnazition 组织
	 ,p_in_owner 责任人
	 ,p_in_scal_allow 允许按比例增减
	 ,p_in_cul_pro_qty 计算产品数量
	 ,p_in_mark_for_deletion 标记为删除
	 | In Out Parameters:
	 | 无
	 | Return:
	 | 返回CLOB,将数据存在CLOB中
	 | History:
	 | Created By raofengbo on 2012-10-２3
	 *=================================================================*/
	 FUNCTION cux_17_gme_print_FORMULA(p_in_status IN VARCHAR2
	 ,p_in_formula in VARCHAR2
	 ,p_in_version in number
	 ,p_in_description in VARCHAR2
	 ,p_in_product in VARCHAR2
	 ,p_in_formula_class in VARCHAR2
	 ,p_in_orgnazition in VARCHAR2
	 ,p_in_owner in VARCHAR2
	 ,p_in_scal_allow in VARCHAR2
	 ,p_in_cul_pro_qty in VARCHAR2
	 ,p_in_mark_for_deletion in VARCHAR2) RETURN CLOB
	 IS
	 l_sql_header VARCHAR2(2000);
	 l_sql_line VARCHAR2(2000);
	 l_clob CLOB;
	 l_clob_temp CLOB;
	 l_clob_line CLOB;
	 l_clob_foot CLOB;
	 l_offset NUMBER;
	 l_ctx dbms_xmlquery.ctxType;
	 BEGIN
	 --头SQL
	 l_sql_header :='select ''' || p_in_status || ''' status,''' || p_in_formula || ''' formula,''' ||
	 p_in_version || ''' version,''' ||p_in_description || ''' description,''' ||
	 p_in_product || ''' product,''' ||p_in_formula_class || ''' formula_class,''' ||
	 p_in_orgnazition || ''' orgnazition,''' ||p_in_owner || ''' in_owner,''' ||
	 p_in_scal_allow || ''' scal_allow,''' ||p_in_cul_pro_qty || ''' cul_pro_qty,''' ||
	 p_in_mark_for_deletion || ''' mark_for_deletion' || ' from dual';
	 --行SQL,
	 l_sql_line := '
	 select ffm.formula_no,
	 ffm.formula_status,
	 ffm.delete_mark,
	 gs.meaning status_des,
	 ffm.formula_vers,
	 ffm.formula_desc1,
	 ffm.formula_desc2,
	 ffm.formula_class,
	 ffc.formula_class_desc,
	 ffm.owner_organization_id,
	 org.organization_code,
	 org.organization_name,
	 ffm.owner_id,
	 fu.user_name,
	 fu.description user_des,
	 ffm.scale_type,
	 ffm.formula_type,
	 ffm.auto_product_calc,
	 fmd.inventory_item_id,
	 msi.segment1,
	 msi.description item_des,
	 fmd.qty,
	 fmd.detail_uom,
	 fmd.scale_type,
	 fmd.release_type
	 from fm_matl_dtl fmd,
	 fm_form_mst ffm,
	 gmd_status gs,
	 fm_form_cls ffc,
	 org_organization_definitions org,
	 fnd_user fu,
	 mtl_system_items_b msi
	 where fmd.line_type=1
	 and fmd.formula_id=ffm.formula_id
	 and ffm.formula_status=gs.status_code
	 and ffm.formula_class=ffc.formula_class(+)
	 and ffm.owner_organization_id=org.organization_id
	 and ffm.owner_id=fu.user_id
	 and fmd.inventory_item_id=msi.inventory_item_id
	 and msi.organization_id=161';
	 --定义XML头
	 l_ctx := dbms_xmlquery.newContext(l_sql_header);
	 dbms_xmlquery.setEncodingTag(l_ctx,'GBK');
	 dbms_xmlquery.setRowsetTag(l_ctx,'REPORT');
	 dbms_xmlquery.setRowTag(l_ctx,'WH_HEADER');
	 dbms_xmlquery.useNullAttributeIndicator(l_ctx,TRUE);
	 l_clob := dbms_xmlquery.getXML(l_ctx);
	 dbms_xmlquery.closeContext(l_ctx);
	 --定义XML行
	 l_ctx := dbms_xmlquery.newContext(l_sql_line);
	 dbms_xmlquery.setRowsetTag(l_ctx,'WH_LINES');
	 dbms_xmlquery.setRowTag(l_ctx,'LINE');
	 dbms_xmlquery.useNullAttributeIndicator(l_ctx,TRUE);
	 l_clob_line := dbms_xmlquery.getXML(l_ctx);
	 l_offset := dbms_lob.instr(l_clob_line,'>');
	 dbms_lob.createtemporary(l_clob_temp, TRUE);
	 dbms_lob.copy(l_clob_temp,l_clob_line,dbms_lob.getlength(l_clob_line) - l_offset - 1, 1, l_offset + 2);
	 --l_clob_temp := dbms_lob.substr(l_clob_temp,dbms_lob.getlength(l_clob_temp) - l_offset,l_offset +2);
	 --将头行拼接在一起
	 l_offset := dbms_lob.instr(l_clob,'</WH_HEADER>');
	 l_clob_foot := dbms_lob.substr(l_clob,dbms_lob.getlength(l_clob) - l_offset,l_offset );
	 l_clob := dbms_lob.substr(l_clob, l_offset - 1, 1);
	 dbms_lob.append(l_clob, l_clob_temp);
	 /* IF dbms_lob.getlength(l_clob) > 0 THEN
	 dbms_lob.write(l_clob,dbms_lob.getlength(l_clob_temp),l_offset - 1,l_clob_temp);
	 END IF;*/
	 dbms_lob.append(l_clob,l_clob_foot);
	 dbms_xmlquery.closeContext(l_ctx);
	 RETURN l_clob;
	 END cux_17_gme_print_FORMULA;
	 /*================================================================*
	 | Procedure:
	 | cux_17_gme_print_FORMULA
	 |
	 | Description:
	 | 配方信息汇总表,将数据输出到并发请求的输出
	 |
	 | Out Parameters:
	 | p_out_errcode 请求标准参数
	 | p_out_errbuf 请求标准参数
	 |
	 | In Parameters:
	 | p_in_status 状态
	 ,p_in_formula 配方
	 ,p_in_version 版本
	 ,p_in_description 说明
	 ,p_in_product 产品
	 ,p_in_formula_class 配方分类
	 ,p_in_orgnazition 组织
	 ,p_in_owner 责任人
	 ,p_in_scal_allow 允许按比例增减
	 ,p_in_cul_pro_qty 计算产品数量
	 ,p_in_mark_for_deletion 标记为删除
	 | In Out Parameters:
	 | 无
	 | Return:
	 | 返回CLOB,将数据存在CLOB中
	 | History:
	 | Created By raofengbo on 2012-10-２3
	 *=================================================================*/
	 PROCEDURE cux_17_gme_print_FORMULA(p_out_errcode OUT VARCHAR2
	 ,p_out_errbuf OUT VARCHAR2
	 ,p_in_status IN VARCHAR2
	 ,p_in_formula in VARCHAR2
	 ,p_in_version in number
	 ,p_in_description in VARCHAR2
	 ,p_in_product in VARCHAR2
	 ,p_in_formula_class in VARCHAR2
	 ,p_in_orgnazition in VARCHAR2
	 ,p_in_owner in VARCHAR2
	 ,p_in_scal_allow in VARCHAR2
	 ,p_in_cul_pro_qty in VARCHAR2
	 ,p_in_mark_for_deletion in VARCHAR2)
	 IS
	 l_clob CLOB;
	 l_count number;
	 BEGIN
	 l_clob := cux_17_gme_print_FORMULA(p_in_status
	 ,p_in_formula
	 ,p_in_version
	 ,p_in_description
	 ,p_in_product
	 ,p_in_formula_class
	 ,p_in_orgnazition
	 ,p_in_owner
	 ,p_in_scal_allow
	 ,p_in_cul_pro_qty
	 ,p_in_mark_for_deletion);
	 cux_17_docprint_pkg.output_xml(l_clob);
	 END cux_17_gme_print_FORMULA;
	end CUX_17_GME_FORMULA_PKG;
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　即时打印　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)