EBS报表

最新推荐文章于 2025-01-11 11:29:41 发布
转载 最新推荐文章于 2025-01-11 11:29:41 发布 · 466 阅读 · 0

最近学习了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 ACRA.RECEIPT_NUMBER, --收款编号
                                                                                             T.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, --全免客户
                                                                                             T.ISSUE_BANK_NAME BANK_NAME, --银行信息
                                                                                             ACRA.RECEIPT_DATE, --收款日期
                                                                                             T.DUE_DATE, --汇票到期日期
                                                                                             T.ATTRIBUTE8 RELE_DATE, --释放日期
                                                                                             ROUND(TO_NUMBER(T.ATTRIBUTE1), 2) CHARGE_AMT,
                                                                                             CBCR.BASE_RULE, --手续费率
                                                                                             DECODE(T.ATTRIBUTE6,
                                                                                                    'Y',
                                                                                                    CBCR.MAIN_ADJ_RULE,
                                                                                                    'N',
                                                                                                    CBCR.OTHER_ADJ_RULE) BANK_RULE, -- 银行比率
                                                                                             T.ATTRIBUTE4 AMT_RULE, --金额比率
                                                                                             T.ATTRIBUTE3 DAYS_RULE, --天数比率
                                                                                             CBCR.BASE_RULE * NVL(T.ATTRIBUTE4, 1) * NVL(T.ATTRIBUTE3, 1) *
                                                                                             NVL(DECODE(T.ATTRIBUTE6,
                                                                                                        'Y',
                                                                                                        CBCR.MAIN_ADJ_RULE,
                                                                                                        'N',
                                                                                                        CBCR.OTHER_ADJ_RULE),
                                                                                                 1) COMB_RULE, --组合比率
                                                                                             T.ATTRIBUTE5 SPEC_RULE, --特惠比率
                                                                                             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) F_RULE, --最终比率
                                                                                             T.ATTRIBUTE9 INTERFACE_ID
                                                                                        FROM CUX.CUX_BILL_OF_EXCHANGES_ALL T,
                                                                                             AR_CASH_RECEIPTS_ALL          ACRA,
                                                                                             AR_CASH_RECEIPT_HISTORY_ALL   ACRH,
                                                                                             CUX_BOE_CALC_RULE             CBCR,
                                                                                             --CUX_BOE_CUSTOMER_V            CUST
                                                                                             CUX_BOE_TM_INTERFACE CUST
                                                                                       WHERE T.RELATED_DOCUMENT_ID = ACRA.CASH_RECEIPT_ID
                                                                                         AND ACRA.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
                                                                                         AND CBCR.CALC_RULE_ID = T.ATTRIBUTE2
                                                                                         AND CUST.DOCUMENT_ID = T.DOCUMENT_ID
                                                                                         AND CBCR.POLICY_ID = CUST.POLICY_ID
                                                                                            --AND ACRH.FIRST_POSTED_RECORD_FLAG = 'Y'
                                                                                         AND CUST.ORG_ID = FND_GLOBAL.ORG_ID
                                                                                         AND CBCR.POLICY_ID = P_POLICY_ID
                                                                                         AND ACRH.GL_DATE BETWEEN P_DATE_FROM AND P_DATE_TO
                                                                                       ORDER BY ACRA.RECEIPT_NUMBER;*/
      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;

    /*SELECT START_DATE, END_DATE
     INTO V_START_DATE, V_END_DATE
     FROM GL_PERIOD_STATUSES
    WHERE CLOSING_STATUS IN ('O', 'C')
      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;*/

    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/

congdunyi6756
关注
EBS 报表开发：XML Publisher
weixin_42683386的博客
05-22 1525
XML Publisher (现在叫BI Publisher) 是随Oracle EBS一起发布的一套基于模版的打印解决方案， 提供一种设计报表和打印报表的新方式，很好的将桌面文字处理工具和EBS数据报表输出集成在一起。 XML Publisher 将数据结构和数据表现分开，通过EBS取得数据，在模版文件中定义报表的输出格式。 在运行的时候，XML Publisher合并数据和模版文件来输出各种样式和格式的报表。 数据获取、布局设计、界面翻译三大工作全部分离，可由不同的人甚至第三方分工协作。 XML Pub
OracleEBS-报表开发工具
07-25
OracleEBS-报表开发工具 OracleEBS-报表开发工具
oracle ebs 报表输出
03-27
NULL 博文链接：https://anole1982.iteye.com/blog/1174035
EBS之实现报表的两种方式
weixin_44710155的博客
10-20 2209
方式1：使用代码生成 1 首先新建一个doc文件然后定义好自己的模板，如图： 2另存为xml格式的文件然后用浏览器打开，把代码全部复制下来，如图 3在pl/sql中贴入代码，使用fnd_file.put_line(fnd_file.output,'第二步的代码')写出excel报表 说明：应该有三部分组成，第一是<Row>......</Row>之前的代码，第二是<Row>......</Row>之间这里是写你的数据，第三是<..
EBS 报表开发：PLSQL Utils@SYLK_UTIL_PKG
weixin_42683386的博客
09-26 494
使用gen_xl_xml包生成Excel文件。（在网站Sanjeev’ Oracle World 可以下载包和示例） ## 1. GEN_XL_XML的用法 用单Sheet的Excel的例子，测试一下gen_xl_xml的用法。（多Sheet的示例也比较简单，就不测试了） 步骤1. 下载gen_xl_xml包并部署到数据库 步骤2. 创建生成Excel的目录 create or replace directory EXCEL_CREATION_TEST as ‘/usr/tmp’ 步骤3. 使用gen_x
精选资源
EBS报表-html输出Excel
09-05
用作输出Excel报表
Oracle EBS 报表输出字符字段前部＂0＂被Excel自动去掉问题
08-02
总的来说，解决Oracle EBS报表输出到Excel时丢失前导零的问题需要综合考虑数据格式、报表设计、导出格式以及可能需要的后期处理。通过理解Excel的行为和Oracle EBS的报表工具，可以有效地避免或纠正这一问题。
精选资源
EBS报表-xml输出Excel
09-05
用作输出Excel报表
ebs 报表开发
09-11
Oracle EBS（Enterprise Business Suite）报表开发是Oracle企业级应用套件的重要组成部分，它提供了强大的数据分析和报告生成能力，帮助用户从海量数据中提取有价值的信息。在这个“ebs 报表开发”主题中，我们将...
EBS 监控、报告、故障排除
qq_32445015的博客
11-27 148
目录。
EBS HTML报表开发过程
09-25
此手册为EBS html报表开发过程，包含如何在ebs中挂菜单等操作
【EBS】ORACLE EBS常用表
misswjr的专栏
01-09 2808
*--------------------------//接收事务处理------------------------************/注意:红冲收款报表时间跨月的问题；/*==================物料发送请求行视图==================================*/---------------------------//发运------------------------------
EBS-Report报表创建
最新发布
a2982955295的博客
01-11 1661
进入应用点击文件--连接。
EBS 多sheet页Excel动态报表开发过程
kikiwhq的专栏
11-16 970
摘自：EBS 多sheet页Excel动态报表开发过程_ITPUB博客 前言 本文讲述的多Sheet页EXCEL报表开发方式和开发HTML,PDF这类报表的方法大致是一致的，唯一不同的是该报表输出是一个XML文件，但是这种XML文件支持EXCEL直接打开。 这种方式有如下两点点非常明显的优点： （1） 灵活性。 如果客户对报表显示样式要求非常严格的话，那用这种方式就非常方便了，就比如我这次项目的客户是个德国人，对报表的显示样式要求极为苛刻，甚至严格到每个列的颜色，边框线，列宽，字体等。用这种方
EBS FORM通用报表
oBinger的博客
07-05 412
@[EBS FORM通用报表（只要会写SQL和简单的配置后导出 EXCEL 文件）] ##程序界面（后续待完善） 用户在这里输入 定义好的报表，点运行，导出报表 运行 报表后直接输出 XLSX文件 ...
结合EBS并发做报表技术小结
乔克速速
12-11 2394
对于结合EBS并发做报表的方式，到目前为止主要学习了三种方式，在此总结一下: 一.并发+Report Builder+Xmlp: Step1.利用Report Builder创建获取数据的RDF文件，通常步骤如下： 1.1 添加用户参数P_CONC_REQUEST_ID ，这个参数是一定要有的； 1.2 根据实际的需求添加其他的用户参数，当然这一步你可以在写SQL查询的时候
EBS R12输出Excel报表的几种方式
m0_67158898的博客
09-05 1934
EBS R12输出Excel报表的几种方式
Oracle EBS 根据工作日历取工作日
weixin_30608503的博客
12-26 230
/*===================================================== 根据NLT工作日历 如果p_due_date 是工作日， 返回p_due_date 如果p_due_date 不是工作日，取得 p_due_date 之前最近的一个工作日，返回 Add by xiaoiqng.zhang@2012-12-05 ===...
EBS 报表开发：标准报表Text输出改为XML输出
weixin_42683386的博客
05-25 1247
很多EBS的标准报表，特别是EBS R11都只有text格式输出，输出结果不好整理数据，需修改为Excel格式输出。 下面介绍两种类型（Oracle Report, Spawned）的报表如何修改。 Oracle Report类型@ Cost Type Comparison Report Step1. 复制text格式输出的请求，建立一个XML输出的新的请求。 Step2. 运行请求，得到XML文件 Step3: 根据XML文件编写rtf模块并上传（略） Spawned Report类型@ Appli
Oracle EBS报表开发实践指南
在深入分析与学习Oracle E-Business Suite (EBS) 报表开发的相关内容之前，我们首先需要了解几个核心概念，它们构成...通过详细了解和应用这些知识点，可以有效地创建、部署和维护EBS报表，为企业的决策提供有力支持。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值