EBS报表

最近学习了EBS 开发报表输出的两种格式：

1.文本格式

CREATE OR REPLACE PROCEDURE giti_inv_fh_rpt(errbuf      OUT VARCHAR2,

                                            retcode     OUT VARCHAR2,

                                            l_date_from IN VARCHAR2,

                                            l_date_to   IN VARCHAR2 ) IS

  --创建游标 mycur

  --游标的开始

  CURSOR mycur IS

  ---需求名称：各工厂库存旬报

    SELECT rownum ro,

           hou.NAME organization_name,

           ms.segment2 order_type,

           msi.segment1 item_code,

           giti_item_pkg.get_item_class(msi.segment1) class1,

           SUM(mmt.transaction_quantity) quantity,

           mmt.subinventory_code sub_code,

           to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date

      FROM inv.mtl_material_transactions mmt,

           inv.mtl_sales_orders          ms,

           inv.mtl_system_items_b        msi,

           hr.hr_all_organization_units  hou

     WHERE mmt.transaction_source_type_id = 2

       AND mmt.transaction_source_id = ms.sales_order_id

       AND msi.inventory_item_id = mmt.inventory_item_id

       AND msi.organization_id = 83

       AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')

       AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')

       AND mmt.transaction_date >= fnd_date.垃圾广告ical_to_date(l_date_from)--to_date(l_date_from, 'yyyy/mm/dd ')

       AND mmt.transaction_date < fnd_date.垃圾广告ical_to_date(l_date_to)+1 --to_date(l_date_to, 'yyyy/mm/dd') + 1

       AND hou.organization_id = mmt.organization_id

       AND mmt.transaction_quantity < 0

     GROUP BY rownum,

              hou.NAME,

              hou.organization_id,

              ms.segment2,

              msi.segment1,

              mmt.subinventory_code,

              to_char(mmt.transaction_date, 'YYYY-MM-DD')

    UNION ALL

    SELECT rownum,

           hou.NAME organization_name,

           ms.segment2 order_type,

           msi.segment1 item_code,

           giti_item_pkg.get_item_class(msi.segment1) ,

           SUM(mmt.transaction_quantity) quantity,

           mmt.subinventory_code,

           to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date

      FROM inv.mtl_material_transactions mmt,

           inv.mtl_sales_orders          ms,

           inv.mtl_system_items_b        msi,

           hr.hr_all_organization_units  hou

     WHERE mmt.transaction_source_type_id = 12

       AND mmt.transaction_source_id = ms.sales_order_id

       AND msi.inventory_item_id = mmt.inventory_item_id

       AND msi.organization_id = 83

       AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')

       AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')

       AND mmt.transaction_date >= fnd_date.垃圾广告ical_to_date(l_date_from)   --to_date(l_date_from, 'yyyy/mm/dd ')

       AND mmt.transaction_date < fnd_date.垃圾广告ical_to_date(l_date_to)+1    --to_date(l_date_to, 'yyyy/mm/dd') + 1

       AND hou.organization_id = mmt.organization_id

       AND mmt.transaction_quantity < 0

     GROUP BY hou.NAME,

              rownum,

              hou.organization_id,

              ms.segment2,

              msi.segment1,

              mmt.subinventory_code,

              to_char(mmt.transaction_date, 'YYYY-MM-DD');

  --游标的结束

  l_print_line VARCHAR2(4000);

  --l_dem        VARCHAR2(10) := ',';

  l_k          VARCHAR2(30) := '           ';

BEGIN

  fnd_file.put_line(fnd_file.output, lpad('各工厂库存情况旬报表', 78));

  l_print_line := '序号'||l_k||'组织名称' ||  l_k || '销售类型' ||  l_k || '品号' ||  l_k||'大类' ||l_k|| '销售数量' || l_k  || '子库名称/子库' || l_k || '交易日';

  fnd_file.put_line(fnd_file.output, l_print_line);

  FOR rec IN mycur

  LOOP

    l_print_line :=rec.ro||l_k|| rec.organization_name ||'  '|| rec.order_type ||'     '|| rec.item_code ||'         '||rec.class1 ||'            ' || rec.quantity || '           '|| rec.sub_code  ||'               '|| rec.transaction_date;

    fnd_file.put_line(fnd_file.output, l_print_line);

  END LOOP;

  fnd_file.put_line(fnd_file.output, lpad('***报表结束***', 80));

END giti_inv_fh_rpt;

/

2.html 格式

--------用html输出报表----------------

CREATE OR REPLACE PACKAGE giti_inv_xbb IS

  -- Author  : YUWEILIANG

  -- Created : 2009-4-28 7:57:25

  -- Purpose : 各工厂库存情况旬报表

  PROCEDURE giti_inv_fh_rpt(errbuf      OUT VARCHAR2,

                            retcode     OUT VARCHAR2,

                            l_date_from IN VARCHAR2,

                            l_date_to   IN VARCHAR2);

  PROCEDURE print_line(g_srs_flag     IN VARCHAR2,

                       p_line_type    IN VARCHAR2,

                       p_line_content IN VARCHAR2);

END giti_inv_xbb;

/

CREATE OR REPLACE PACKAGE BODY giti_inv_xbb IS

  PROCEDURE giti_inv_fh_rpt(errbuf      OUT VARCHAR2,

                            retcode     OUT VARCHAR2,

                            l_date_from IN VARCHAR2,

                            l_date_to   IN VARCHAR2) IS

    --创建游标 mycur

    --游标的开始

    --需求名称：各工厂库存旬报

    CURSOR mycur IS

      SELECT rownum ro,

             hou.NAME organization_name,

             ms.segment2 order_type,

             msi.segment1 item_code,

             SUM(mmt.transaction_quantity) quantity,

             mmt.subinventory_code sub_code,

             to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date

        FROM inv.mtl_material_transactions mmt,

             inv.mtl_sales_orders          ms,

             inv.mtl_system_items_b        msi,

             hr.hr_all_organization_units  hou

       WHERE mmt.transaction_source_type_id = 2

         AND mmt.transaction_source_id = ms.sales_order_id

         AND msi.inventory_item_id = mmt.inventory_item_id

         AND msi.organization_id = 83

         AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')

         AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')

         AND mmt.transaction_date >= to_date(l_date_from, 'yyyy-mm-dd ')

         AND mmt.transaction_date < to_date(l_date_to, 'yyyy-mm-dd') + 1

         AND hou.organization_id = mmt.organization_id

         AND mmt.transaction_quantity < 0

       GROUP BY rownum,

                hou.NAME,

                hou.organization_id,

                ms.segment2,

                msi.segment1,

                mmt.subinventory_code,

                to_char(mmt.transaction_date, 'YYYY-MM-DD')

      UNION ALL

      SELECT rownum,

             hou.NAME organization_name,

             ms.segment2 order_type,

             msi.segment1 item_code,

             SUM(mmt.transaction_quantity) quantity,

             mmt.subinventory_code,

             to_char(mmt.transaction_date, 'YYYY-MM-DD') transaction_date

        FROM inv.mtl_material_transactions mmt,

             inv.mtl_sales_orders          ms,

             inv.mtl_system_items_b        msi,

             hr.hr_all_organization_units  hou

       WHERE mmt.transaction_source_type_id = 12

         AND mmt.transaction_source_id = ms.sales_order_id

         AND msi.inventory_item_id = mmt.inventory_item_id

         AND msi.organization_id = 83

         AND (msi.segment1 LIKE '1%' OR msi.segment1 LIKE 'Y1%')

         AND mmt.organization_id IN ('82', '441', '442', '443', '444', '445', '635')

         AND mmt.transaction_date >= to_date(l_date_from, 'yyyy-mm-dd ')

         AND mmt.transaction_date < to_date(l_date_to, 'yyyy-mm-dd') + 1

         AND hou.organization_id = mmt.organization_id

         AND mmt.transaction_quantity < 0

       GROUP BY hou.NAME,

                rownum,

                hou.organization_id,

                ms.segment2,

                msi.segment1,

                mmt.subinventory_code,

                to_char(mmt.transaction_date, 'YYYY-MM-DD');

    --游标的结束 

    l_temp_string VARCHAR2(10000);

  BEGIN

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '

');

    print_line('Y', 'OUTFILE', '各工厂库存旬报表' || '');

    print_line('Y', 'OUTFILE', '<!-- ========================================================= --&gt');

    print_line('Y', 'OUTFILE', '<!--             Copyright (c) 2008 GITI Corporation           --&gt');

    print_line('Y', 'OUTFILE', '<!--                  All rights reserved                      --&gt');

    print_line('Y', 'OUTFILE', '<!-- ========================================================= --&gt');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '  div.breakafter { page-break-after:always; }');

    print_line('Y', 'OUTFILE', '  .tableheader { background: #ccce99; color: #546699;}');

    print_line('Y', 'OUTFILE', '  div.breakbefore { page-break-before:always; }');

    print_line('Y',

               'OUTFILE',

               '  body {font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: black}');

    print_line('Y',

               'OUTFILE',

               '  table {font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: black}');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '

各工厂库存旬报表 ');

    print_line('Y', 'OUTFILE', '');

    print_line('Y', 'OUTFILE', '

序号	组织名称	销售类型	品号	销售数量	子库名称/子库	交易日
'  || nvl(rec.ro, 0)                || '	'  || nvl(rec.organization_name, '-') || '	'  || nvl(rec.order_type, '-')        || '	'                || nvl(rec.item_code, '-')         || '	'                || nvl(TO_CHAR(rec.quantity), '-')          || '	'                || nvl(rec.sub_code, '-')          || '	'                || nvl(rec.transaction_date, NULL)  || '

Exception ');

      print_line('Y', 'OUTFILE', ' ' || 'ERROR' || '');

      print_line('Y', 'OUTFILE', '

');

      print_line('Y', 'OUTFILE', ' ' || SQLERRM || '');

      print_line('Y', 'OUTFILE', '

');

      print_line('Y', 'OUTFILE', '');

      print_line('Y', 'OUTFILE', '');

  END;

  PROCEDURE print_line(g_srs_flag     IN VARCHAR2,

                       p_line_type    IN VARCHAR2,

                       p_line_content IN VARCHAR2) IS

  BEGIN

    IF g_srs_flag = 'N' THEN

      htp.p(p_line_content);

    ELSE

      IF p_line_type = 'OUTFILE' THEN

        fnd_file.put_line(fnd_file.output, p_line_content);

      ELSIF p_line_type = 'LOGFILE' THEN

        fnd_file.put_line(fnd_file.log, p_line_content);

      END IF;

    END IF;

  END print_line;

END giti_inv_xbb;

/

或者

CREATE OR REPLACE PACKAGE BODY CUX_BOE_CUST_PERIOD_RPT IS

  g_conc_request_id CONSTANT NUMBER := fnd_global.conc_request_id;

  g_srs_flag        VARCHAR2(1) := 'Y';

  g_formt_mask      VARCHAR2(100) := 'YYYY-MM-DD';

  PROCEDURE MAIN(ERRBUF      OUT VARCHAR2,

                 RETCODE     OUT VARCHAR2,

                 P_POLICY_ID IN NUMBER,

                 P_PERIOD    IN VARCHAR2) IS

    LV_HEADER VARCHAR2(10000) := '

                                佳通轮胎有限公司客户当前手续费报表

                                                                <!--

                                   body         {background-color: #ffffff;}

                                   .subheader1  {font-family: Arial, Helvetica, Geneva, sans-serif;

                                                 font-size: 13pt;

                                                 font-weight: bold;

                                                 color: #336699;}

                                   .tableheader {font-family: Arial, Helvetica, Geneva, sans-serif;

                                                 font-size: 10pt;

                                                 font-weight: bold;

                                                 background: #cccc99;

                                                 color: #336699;

                                                 text-align: left;}

                                   .tabledata   {font-family: Arial, Helvetica, Geneva, sans-serif;

                                                 font-size: 10pt;

                                                 background: #f7f7e7;

                                                 color: #000000;}

                                --&gt

佳通轮胎有限公司客户当前手续费报表

销售中心

收款编号

客户编码

客户名称

特惠客户

全免客户

银行信息

收款日期

汇票到期日

释放日期

收款金额

手续费

总超出金额

汇票比例

总汇票比例

特惠比率

减免比例

基础比率

超额比率

银行/天数比率

金额固定

组合比率

累计增加

最终比率

银行/天数固定

                                    银行/金额比率

                                    银行/金额固定

                                    金额比率

                                    准确计息比率

                                    天数比率

                                    天数固定

                                    传送标志

                                    冻结状态

                                    手续费率

                                 ';

    LV_BODY   VARCHAR2(3000) := '

                              TEXT01

                              TEXT02

                              TEXT03

                              TEXT04

                              TEXT05

                                TEXT06

                              TEXT07

                                TEXT08

                              TEXT09

                              TEXT10

                              TEXT11

                              TEXT12

                                TEXT13

                                TEXT14

                                TEXT15

                                TEXT16

                                TEXT17

                                TEXT58

                                TEXT68

                                TEXT19

                                TEXT20

                                TEXT21

                                TEXT22

                                TEXT23

                                TEXT24

                                TEXT25

                                TEXT26

                                TEXT27

                                TEXT28

                                TEXT29

                                TEXT30

                                TEXT31

                                TEXT32

                                TEXT18

                             ';

    LV_STRING VARCHAR2(3000);

    LV_TAIL   VARCHAR2(200) := '';

    CURSOR C_RECEIPTS(P_ORG_ID IN NUMBER) IS

      SELECT CUST.NAME, --销售中心

             CUST.RECEIPT_NUMBER, --收款编号

             CUST.REMITTED_AMOUNT, --收款金额

             CUST.CUSTOMER_NUMBER, --客户编码

             CUST.CUSTOMER_NAME, --客户名称

             DECODE(CUST.ATTRIBUTE1, 'Y', '是', 'N', '否') SPEC_BENE_FLAG, --特惠客户

             DECODE(CUST.ATTRIBUTE2, 'Y', '是', 'N', '否') ALLOW_ALL_FREE_FLAG, --全免客户

             CUST.BANK_NAME, --银行信息

             CUST.RECEIPT_DATE, --收款日期

             CUST.DUE_DATE, --汇票到期日期

             CUST.RELEASE_DATE RELE_DATE, --释放日期

             CUST.SOURCE_LINES_AMOUNT CHARGE_AMT, --手续费

             get_cust_base_rule(CUST.ORG_ID,CUST.POLICY_ID,CUST.CUSTOMER_ID) NEW_BASE_RULE, --基础比率

             get_cust_overtake_rule(CUST.ORG_ID,CUST.POLICY_ID,CUST.CUSTOMER_ID)  NEW_OVERTAKE_RULE,--超额比率

             CBCR.BASE_RULE, --手续费率

             DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),

                    '07',

                    DECODE(CUST.ATTRIBUTE2,  --brain.chen on 2007.05.31

                           'Y',

                           '',

                           DECODE(T.ATTRIBUTE6,

                                  'Y',

                                  CBCR.MAIN_ADJ_RULE,

                                  'N',

                                  CBCR.OTHER_ADJ_RULE)),

                    T.ATTRIBUTE10 --08年银行比率

                                  )BANK_RULE, -- 银行比率

             T.ATTRIBUTE11 BANK_AMOUNT,         --银行固定 08年政策新增

             T.ATTRIBUTE4 AMT_RULE, --金额比率

             T.ATTRIBUTE9 AMT_AMOUNT, --金额固定 08年政策新增

             T.ATTRIBUTE5 PREC_RULE, --准确计息比率

             T.ATTRIBUTE3 DAYS_RULE, --天数比率

             T.ATTRIBUTE8 DAYS_AMOUNT, --天数固定 08年政策新增

             DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),

                    '07',

                    CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *

                    NVL(T.ATTRIBUTE5, 1) * NVL(DECODE(T.ATTRIBUTE6,

                                                      'Y',

                                                      CBCR.MAIN_ADJ_RULE,

                                                      'N',

                                                      CBCR.OTHER_ADJ_RULE), 1),

                    CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *

                    NVL(T.ATTRIBUTE5, 1)) COMB_RULE, --组合比率

             NVL(T.ATTRIBUTE11, 0) + NVL(T.ATTRIBUTE9, 0) + NVL(T.ATTRIBUTE8, 0) TOTAL_AMOUNT,  --累计增加 08年政策

             CUST.SPEC_RULE, --特惠比率

             DECODE(SUBSTR(CUST.PERIOD_NAME, 4, 5),

                           '07',

                           CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *

                           NVL(T.ATTRIBUTE5, 1) * NVL(CUST.SPEC_RULE, 1) *

                           NVL(DECODE(T.ATTRIBUTE6,

                                      'Y',

                                      CBCR.MAIN_ADJ_RULE,

                                      'N',

                                      CBCR.OTHER_ADJ_RULE),

                               1),

                           NVL(T.ATTRIBUTE15,

                           --CUST.SOURCE_LINES_AMOUNT / NVL(CUST.REMITTED_AMOUNT, CUST.SOURCE_LINES_AMOUNT)

                              DECODE(NVL(CUST.REMITTED_AMOUNT,0),

                                     0,

                                     DECODE(NVL(CUST.SOURCE_LINES_AMOUNT, 0),

                                            0,

                                            0,

                                            1),

                                     NVL(CUST.SOURCE_LINES_AMOUNT, 0) / CUST.REMITTED_AMOUNT)

                              --MODI BY LEONFENG 20080108

                           )

                   )F_RULE, --最终比率

             DECODE(CUST.IMPORT_FLAG, 'Y', '是', 'N', '否') IMPORT_FLAG,

             CUST.BOE_SCALE,  --汇票比率

             T.ATTRIBUTE1,  --减免比率

             CUST.TOTAL_BOE_SCALE, --总汇票比率

             CUST.OVER_AMOUNT,  --总超出金额

             T.ATTRIBUTE16,  --银行金额比例

             T.ATTRIBUTE17,   --银行金额固定

             DECODE(CUST.FREEZE_FLAG,

                    'Y',

                    '冻结',

                    '释放') FREEZE_FLAG

        FROM CUX.CUX_BILL_OF_EXCHANGES_ALL T,

             CUX_BOE_CALC_RULE             CBCR,

             CUX_BOE_TM_INTERFACE_V        CUST

       WHERE T.DOCUMENT_ID = CUST.DOCUMENT_ID

         AND CBCR.CALC_RULE_ID = T.ATTRIBUTE2

         AND CUST.POLICY_ID = CBCR.POLICY_ID

         AND CUST.ORG_ID =

             DECODE(P_ORG_ID, 88, CUST.ORG_ID, P_ORG_ID, P_ORG_ID) --FND_GLOBAL.ORG_ID

         AND CUST.POLICY_ID = P_POLICY_ID

         AND CUST.PERIOD_NAME = P_PERIOD

       ORDER BY CUST.RECEIPT_NUMBER;

    V_START_DATE DATE;

    V_END_DATE   DATE;

    V_ORG_ID     NUMBER;

  BEGIN

    --100总公司显示所有OU的报表，其他分公司只显示当前OU的报表

    V_ORG_ID := FND_GLOBAL.ORG_ID;

    CUX_CONC_UTL.OUT_MSG(LV_HEADER);

    FOR REC IN C_RECEIPTS(V_ORG_ID) LOOP

      LV_STRING := LV_BODY;

      LV_STRING := REPLACE(LV_STRING, 'TEXT01', NVL(REC.NAME, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT02',

                           NVL(TO_CHAR(REC.RECEIPT_NUMBER), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT03',

                           NVL(REC.CUSTOMER_NUMBER, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT04',

                           NVL(REC.CUSTOMER_NAME, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT05',

                           NVL(REC.SPEC_BENE_FLAG, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT06',

                           NVL(REC.ALLOW_ALL_FREE_FLAG, ''));

      LV_STRING := REPLACE(LV_STRING, 'TEXT07', NVL(REC.BANK_NAME, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT08',

                           NVL(TO_CHAR(REC.RECEIPT_DATE, 'YYYY-MM-DD'),

                               ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT09',

                           NVL(TO_CHAR(REC.DUE_DATE, 'YYYY-MM-DD'), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT10',

                           NVL(TO_CHAR(REC.RELE_DATE, 'YYYY-MM-DD'), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT11',

                           NVL(TO_CHAR(REC.REMITTED_AMOUNT), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT12',

                           NVL(TO_CHAR(ROUND(REC.CHARGE_AMT, 4)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT13',

                           NVL(TO_CHAR(ROUND(REC.OVER_AMOUNT, 4)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT14',

                           NVL(TO_CHAR(ROUND(REC.BOE_SCALE, 4)), ''));

      --银行固定

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT15',

                           NVL(TO_CHAR(ROUND(REC.TOTAL_BOE_SCALE, 4)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT16',

                           NVL(TO_CHAR(ROUND(REC.SPEC_RULE, 4)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT17',

                           NVL(TO_CHAR(REC.ATTRIBUTE1), ''));

      --基础比率

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT58',

                           NVL(TO_CHAR(REC.NEW_BASE_RULE), ''));

      --超额比率

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT68',

                           NVL(TO_CHAR(REC.NEW_OVERTAKE_RULE), ''));

      --金额比率

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT18',

                           NVL(TO_CHAR(REC.BASE_RULE), ''));

      --金额固定

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT19',

                           NVL(TO_CHAR(REC.BANK_RULE), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT20',

                           NVL(TO_CHAR(REC.AMT_AMOUNT), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT21',

                           NVL(TO_CHAR(ROUND(REC.COMB_RULE, 4)), ''));

      --天数固定

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT22',

                           NVL(TO_CHAR(ROUND(REC.TOTAL_AMOUNT, 4)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT23',

                           NVL(TO_CHAR(ROUND(REC.F_RULE, 6)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT24',

                           NVL(TO_CHAR(REC.BANK_RULE), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT25',

                           NVL(TO_CHAR(ROUND(REC.ATTRIBUTE16, 6)), ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT26',

                           NVL(TO_CHAR(ROUND(REC.ATTRIBUTE17, 6)), ''));

      --汇票比例

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT27',

                           NVL(TO_CHAR(round(REC.AMT_RULE, 4)), ''));

      --减免比例

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT28',

                           NVL(TO_CHAR(REC.PREC_RULE), ''));

      --总超出金额

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT29',

                           NVL(TO_CHAR(ROUND(REC.DAYS_RULE, 4)), ''));

      --总汇票比例

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT30',

                           NVL(TO_CHAR(ROUND(REC.DAYS_AMOUNT, 4)), ''));

      --传送标志

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT31',

                           NVL(REC.IMPORT_FLAG, ''));

      LV_STRING := REPLACE(LV_STRING,

                           'TEXT32',

                           NVL(REC.FREEZE_FLAG, ''));

      CUX_CONC_UTL.OUT_MSG(LV_STRING);

    END LOOP;

    CUX_CONC_UTL.OUT_MSG(LV_TAIL);

  END MAIN;

  PROCEDURE CUXZGSAT(ERRBUF      OUT VARCHAR2,

                     RETCODE     OUT VARCHAR2,

                     P_ORG_ID    IN NUMBER,

                     P_PERIOD    IN VARCHAR2) IS

    l_start_date  DATE;

    l_end_date    DATE;

    CURSOR C_RECEIPTS(p_start_date DATE, p_end_date DATE) IS

      SELECT hou.name ORG_NAME,

             hp.party_name cust_name,

             flv.meaning boe_doc_type,

             boe.remittor_name,

             boe.issue_bank_name,

             boe.date_of_issue,

             boe.due_date,

             boe.remitted_amount,

             boe.document_number1 || boe.document_number2 || boe.document_number3 document_number,

             boe.remittee_name,

             boe.receive_bank_name,

             boe.receive_bank_account_name,

             boe.issue_bank_account_name,

             boe.agreement_number,

             boe.currency_code,

             boe.issue_bank_number,

             boe.issue_bank_address,

             get_first_endor(boe.document_id)first_endor,

             get_second_endor(boe.document_id)second_endor,

             cr.receipt_number

      FROM cux.cux_bill_of_exchanges_all boe,

           ar_cash_receipts_all              cr,

           ar_cash_receipt_history_all       crh,

           ar_cash_receipt_history_all       crhc,

           hz_cust_accounts              hca,

           hz_parties                    hp,

           fnd_lookup_values             flv,

           HR_ORGANIZATION_UNITS    HOU

      WHERE 1 = 1

      AND   boe.related_document_id = cr.cash_receipt_id

      AND   cr.cash_receipt_id = crh.cash_receipt_id

      AND   crh.first_posted_record_flag = 'Y'

      AND   cr.cash_receipt_id = crhc.cash_receipt_id

      AND   crhc.current_record_flag = 'Y'

      AND   crhc.status <> 'REVERSED'

      AND   cr.pay_from_customer = hca.cust_account_id(+)

      AND   hca.party_id = hp.party_id(+)

      AND   flv.lookup_code(+) = boe.document_type

      AND   flv.LANGUAGE(+) = 'ZHS'

      AND   flv.lookup_type(+) = 'CUX_BOE_DOC_TYPE'

      AND   crh.gl_date between nvl(p_start_date, crh.gl_date) and nvl(p_end_date, crh.gl_date)

      AND   CR.ORG_ID = DECODE(P_ORG_ID,88,CR.ORG_ID,NVL(P_ORG_ID,CR.ORG_ID))

      AND   CRH.ORG_ID = DECODE(P_ORG_ID,88,CRH.ORG_ID,NVL(P_ORG_ID,CRH.ORG_ID))

      AND   CRHC.ORG_ID = DECODE(P_ORG_ID,88,CRHC.ORG_ID,NVL(P_ORG_ID,CRHC.ORG_ID))

      AND CR.ORG_ID = HOU.organization_id

      ;

    V_START_DATE DATE;

    V_END_DATE   DATE;

    V_ORG_ID     NUMBER;

    l_temp_string VARCHAR2(4000);

    l_org_name   VARCHAR2(500);

  BEGIN

    IF P_ORG_ID IS NOT NULL THEN

      SELECT ou.name

      INTO l_org_name

      FROM hr_operating_units ou

      WHERE ou.organization_id = P_ORG_ID;

    END IF;

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '

');

    print_line(g_srs_flag, 'OUTFILE', '银行票据信息' || l_org_name || '');

    print_line(g_srs_flag, 'OUTFILE', '<!-- ========================================================= --&gt');

    print_line(g_srs_flag, 'OUTFILE', '<!--             Copyright (c) 2006 Hand Corporation           --&gt');

    print_line(g_srs_flag, 'OUTFILE', '<!--                  All rights reserved                      --&gt');

    print_line(g_srs_flag, 'OUTFILE', '<!-- ========================================================= --&gt');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '  div.breakafter { page-break-after:always; }');

    print_line(g_srs_flag, 'OUTFILE', '  .tableheader { background: #cccc99; color: #336699;}');

    print_line(g_srs_flag, 'OUTFILE', '  div.breakbefore { page-break-before:always; }');

    print_line(g_srs_flag, 'OUTFILE', '  body {font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: black}');

    print_line(g_srs_flag, 'OUTFILE', '  table {font-family: Arial, Helvetica, Geneva, sans-serif; font-size: 9pt; color: black}');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '

银行票据信息 ');

    print_line(g_srs_flag, 'OUTFILE', '

业务实体 :	' || l_org_name || '
打印时间 :	' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || '

');

    print_line(g_srs_flag, 'OUTFILE', '

    l_temp_string := '

' ||

                     '

业务实体

' ||

                     '

客户名称

' ||

                     '

汇票类型

' ||

                     '

出票人全称

' ||

                     '

付款银行名称

' ||

                     '

出票日期

' ||

                     '

到日期

' ||

                     '

出票金额

' ||

                     '

汇票编号

' ||

                     '

收款人名称

' ||

                     '

收款人开户银行

' ||

                     '

收款人帐号

' ||

                     '

出票人帐号

' ||

                     '

承兑协议编号

' ||

                     '

付款币种

' ||

                     '

付款行行号

' ||

                     '

付款行地址

' ||

                     '

第一背书人

' ||

                     '

第二背书人

' ||

                     '

收款编号

' ||

                     '';

    print_line(g_srs_flag, 'OUTFILE', l_temp_string);

    BEGIN

      SELECT START_DATE, END_DATE

        INTO l_start_date, l_end_date

        FROM GL_PERIOD_STATUSES

       WHERE CLOSING_STATUS IN ('O', 'C', 'N')

         AND APPLICATION_ID = 101

         AND SET_OF_BOOKS_ID = 1

         AND ADJUSTMENT_PERIOD_FLAG = 'N'

            --AND TO_CHAR(SYSDATE, 'YYYY') = PERIOD_YEAR

         AND PERIOD_NAME = P_PERIOD;

    EXCEPTION

      WHEN OTHERS THEN

        l_start_date := null;

        l_end_date := null;

    END;

    FOR x IN C_RECEIPTS(l_start_date, l_end_date)

    LOOP

      l_temp_string := '

' ||

                         '' || x.ORG_NAME || '' ||

                         '' || nvl(x.cust_name, '') || '' ||

                         '' || x.boe_doc_type || '' ||

                         '' || x.remittor_name || '' ||

                         '' || x.issue_bank_name || '' ||

                         '' || to_char(x.date_of_issue, 'YYYY-MM-DD') || '' ||

                         '' || to_char(x.due_date, 'YYYY-MM-DD') || '' ||

                         '' || to_char(round(x.remitted_amount, 2)) || '' ||

                         '' || x.document_number || '' ||

                         '' || x.remittee_name || '' ||

                         '' || x.receive_bank_name || '' ||

                         '' || x.receive_bank_account_name || '' ||

                         '' || x.issue_bank_account_name || '' ||

                         '' || nvl(x.agreement_number, '') || '' ||

                         '' || x.currency_code || '' ||

                         '' || x.issue_bank_number || '' ||

                         '' || x.issue_bank_address || '' ||

                         '' || nvl(x.first_endor, '') || '' ||

                         '' || nvl(x.second_endor, '') || '' ||

                         '' || x.receipt_number || '' ||

                         '';

      print_line(g_srs_flag, 'OUTFILE', l_temp_string);

    END LOOP;

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '');

    print_line(g_srs_flag, 'OUTFILE', '');

    EXCEPTION

    WHEN OTHERS THEN

      print_line(g_srs_flag, 'OUTFILE', '

异常错误发生 ');

      print_line(g_srs_flag, 'OUTFILE', '

');

      print_line(g_srs_flag, 'OUTFILE', ' ' || SQLERRM || '');

      print_line(g_srs_flag, 'OUTFILE', '

');

      print_line(g_srs_flag, 'OUTFILE', '

转载于:http://blog.itpub.net/13996372/viewspace-607788/