即时打印SQL包

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;