Method 为'PL/SQL Stored Produre ，'输出 Format 为'XML'报表例子

于 2012-01-04 11:28:58 发布
阅读量146 收藏
点赞数
CREATE OR REPLACE PACKAGE BODY WIPMTLTRX_1 IS

  P_Org         number;
  P_Subinv_Lo   varchar2(20);
  P_Subinv_Hi   varchar2(20);
  P_Txn_Date_Lo varchar2(20);
  P_Txn_Date_Hi varchar2(20);
  P_Item_Lo     varchar2(20);
  P_Item_Hi     varchar2(20);

  v_trx_date_lo varchar2(20);
  v_trx_date_hi varchar2(20);

  v_P_Item_Lo number;
  v_P_Item_Hi number;

  v_MATERIAL_PART_NUMBER varchar2(100);
  v_ASSEMBLY             varchar2(100);
  v_JOB_ORDER            varchar2(100);
  v_TRANSACTION_DATE     varchar2(100);
  v_TRANSACTION_QTY      varchar2(100);
  v_TRANSACTION_UOM      varchar2(100);
  v_PRIMARY_QTY          varchar2(100);
  v_PRIMARY_UOM          varchar2(100);
  v_TRANSACTION_TYPE     varchar2(100);
  v_REMARK               varchar2(100);

  v_count_WIPMTLTRX_ALL_1 number;

  PROCEDURE MAIN(ERRBUF        OUT VARCHAR2,
                 RETCODE       OUT VARCHAR2,
                 P_Org         IN number,
                 P_Subinv_Lo   IN varchar2,
                 P_Subinv_Hi   IN varchar2,
                 P_Txn_Date_Lo IN varchar2,
                 P_Txn_Date_Hi IN varchar2,
                 P_Item_Lo     IN varchar2,
                 P_Item_Hi     IN varchar2) IS
 
    cursor c1 is
      select *
        from WIPMTLTRX_ALL_1
       order by MATERIAL_PART_NUMBER, ASSEMBLY, JOB_ORDER;
 
  BEGIN
 
    --P_Txn_Date_Lo :2011/06/01 00:00:00
    --P_Txn_Date_Hi :2011/06/29 00:00:00
 
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Txn_Date_Lo :' || P_Txn_Date_Lo);
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Txn_Date_Hi :' || P_Txn_Date_Hi);
 
    v_trx_date_lo := to_date(substr(P_Txn_Date_Lo, 9, 2) || '-' ||
                             substr(P_Txn_Date_Lo, 6, 2) || '-' ||
                             substr(P_Txn_Date_Lo, 1, 4),
                             'DD-MM-YYYY');
    v_trx_date_hi := to_date(substr(P_Txn_Date_Hi, 9, 2) || '-' ||
                             substr(P_Txn_Date_Hi, 6, 2) || '-' ||
                             substr(P_Txn_Date_Hi, 1, 4),
                             'DD-MM-YYYY');
 
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_trx_date_lo :' || v_trx_date_lo);
    FND_FILE.PUT_LINE(FND_FILE.LOG, 'v_trx_date_hi :' || v_trx_date_hi);
 
    IF (P_Item_Lo is not null) and (P_Item_Hi is not null) THEN
      select inventory_item_id
        into v_P_Item_Lo
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org
         and segment1 = P_Item_Lo;
      select inventory_item_id
        into v_P_Item_Hi
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org
         and segment1 = P_Item_Hi;
    ELSIF (P_Item_Lo is not null) and (P_Item_Hi is null) THEN
      select inventory_item_id
        into v_P_Item_Lo
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org
         and segment1 = P_Item_Lo;
      select max(inventory_item_id)
        into v_P_Item_Hi
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org;
    ELSIF (P_Item_Lo is null) and (P_Item_Hi is not null) THEN
      select min(inventory_item_id)
        into v_P_Item_Lo
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org;
      select inventory_item_id
        into v_P_Item_Hi
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org
         and segment1 = P_Item_Hi;
    ELSIF (P_Item_Lo is null) and (P_Item_Hi is null) THEN
      select min(inventory_item_id)
        into v_P_Item_Lo
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org;
      select max(inventory_item_id)
        into v_P_Item_Hi
        from apps.MTL_SYSTEM_ITEMS
       where organization_id = P_Org;
    END IF;
 
    /* Start */
 
    delete WIPMTLTRX_TL_1;
    delete WIPMTLTRX_ALL_1;
 
    /* Insert Data into TEMP table */
 
    INSERT INTO WIPMTLTRX_TL_1
      (MATERIAL_PART_NUMBER,
       ASSEMBLY,
       JOB_ORDER,
       TRANSACTION_DATE,
       TRANSACTION_QTY,
       TRANSACTION_UOM,
       PRIMARY_QTY,
       PRIMARY_UOM,
       TRANSACTION_TYPE,
       REMARK)
      (select msi.segment1 MATERIAL_PART_NUMBER,
              NVL((SELECT msi1.SEGMENT1
                 FROM apps.WIP_ENTITIES              wip,
                      apps.MTL_SYSTEM_ITEMS          msi1
                WHERE msi1.organization_id = wip.organization_id
                  and msi1.inventory_item_id = wip.primary_item_id
                  AND wip.wip_entity_NAME=mmt.transaction_reference),'N/A')ASSEMBLY,----2012-1-3 ice.li
              --msi1.segment1 ASSEMBLY,
              nvl(mmt.transaction_reference, 0) JOB_ORDER,
              to_char(mmt.transaction_date, 'DD-MM-YYYY HH:MM') TRANSACTION_DATE,
              mmt.transaction_quantity TRANSACTION_QTY,
              mmt.transaction_uom TRANSACTION_UOM,
              mmt.primary_quantity PRIMARY_QTY,
              msi.primary_uom_code PRIMARY_UOM,
              mtxt.transaction_type_name TRANSACTION_TYPE,
              null REMARK
         from APPS.MTL_MATERIAL_TRANSACTIONS mmt,
              apps.MTL_TRANSACTION_TYPES     mtxt,
              apps.MTL_SYSTEM_ITEMS          msi
              --apps.WIP_ENTITIES              wip,
              --apps.MTL_SYSTEM_ITEMS          msi1
        where mmt.transaction_type_id = mtxt.transaction_type_id
          and mmt.organization_id = P_Org -
          and mmt.subinventory_code between P_Subinv_Lo and P_Subinv_Hi
          and msi.inventory_item_id between v_P_Item_Lo and v_P_Item_Hi
          and mmt.transaction_date between v_trx_date_lo and v_trx_date_hi
          and mtxt.transaction_type_name in
              ('WIP Backflush Transfer (Prod)','WIP Backflush Transfer')
          and msi.inventory_item_id = mmt.inventory_item_id
          and msi.organization_id = mmt.organization_id
          
     union all
      select msi.segment1 MATERIAL_PART_NUMBER,
              msi1.segment1 ASSEMBLY,
              wip.wip_entity_name JOB_ORDER,
              to_char(mmt.transaction_date, 'DD-MM-YYYY HH:MM') TRANSACTION_DATE,
              mmt.transaction_quantity TRANSACTION_QTY,
              mmt.transaction_uom TRANSACTION_UOM,
              mmt.primary_quantity PRIMARY_QTY,
              msi.primary_uom_code PRIMARY_UOM,
              mtxt.transaction_type_name TRANSACTION_TYPE,
              null REMARK
         from APPS.MTL_MATERIAL_TRANSACTIONS mmt,
              apps.MTL_TRANSACTION_TYPES     mtxt,
              apps.MTL_SYSTEM_ITEMS          msi,
              apps.WIP_ENTITIES              wip,
              apps.MTL_SYSTEM_ITEMS          msi1
        where mmt.transaction_type_id = mtxt.transaction_type_id
          and mmt.organization_id = P_Org --596
          and mmt.subinventory_code between P_Subinv_Lo and P_Subinv_Hi
          and msi.inventory_item_id between v_P_Item_Lo and v_P_Item_Hi
          and mmt.transaction_date between v_trx_date_lo and v_trx_date_hi
          and mtxt.transaction_type_name in('WIP Issue','WIP Return')
          and msi.inventory_item_id = mmt.inventory_item_id
          and msi.organization_id = mmt.organization_id
          and (/*mmt.transaction_reference = wip.wip_entity_name or*/
              mmt.transaction_source_id = wip.wip_entity_id)
          and msi1.organization_id = wip.organization_id
          and msi1.inventory_item_id = wip.primary_item_id         
    );
 
    INSERT INTO WIPMTLTRX_ALL_1
      (MATERIAL_PART_NUMBER,
       ASSEMBLY,
       JOB_ORDER,
       TRANSACTION_DATE,
       TRANSACTION_QTY,
       TRANSACTION_UOM,
       PRIMARY_QTY,
       PRIMARY_UOM,
       TRANSACTION_TYPE,
       REMARK)
      (select MATERIAL_PART_NUMBER,
              ASSEMBLY,
              JOB_ORDER,
              TRANSACTION_DATE,
              TRANSACTION_QTY,
              TRANSACTION_UOM,
              PRIMARY_QTY,
              PRIMARY_UOM,
              TRANSACTION_TYPE,
              REMARK
         from WIPMTLTRX_TL_1);
 
    /* Insert Subtotal into TEMP table */
 
    INSERT INTO WIPMTLTRX_ALL_1
      (MATERIAL_PART_NUMBER,
       ASSEMBLY,
       JOB_ORDER,
       TRANSACTION_DATE,
       TRANSACTION_QTY,
       TRANSACTION_UOM,
       PRIMARY_QTY,
       PRIMARY_UOM,
       TRANSACTION_TYPE,
       REMARK)
      (select MATERIAL_PART_NUMBER,
              ASSEMBLY,
              TITLE_BT,
              SUBTOTAL_BT,
              TITLE_IS,
              SUBTOTAL_IS,
              TITLE_RT,
              SUBTOTAL_RT,
              TITLE_OUT,
              SUBTOTAL_OUT
         from BG_SUBTOTAL_ALL_V);
 
    /* Insert Grand Total into TEMP table */
 
    INSERT INTO WIPMTLTRX_ALL_1
      (MATERIAL_PART_NUMBER,
       ASSEMBLY,
       JOB_ORDER,
       TRANSACTION_DATE,
       TRANSACTION_QTY,
       TRANSACTION_UOM,
       PRIMARY_QTY,
       PRIMARY_UOM,
       TRANSACTION_TYPE,
       REMARK)
      (select MATERIAL_PART_NUMBER,
              TITLE_TOTAL,
              TITLE_BT,
              TOTAL_BT,
              TITLE_IS,
              TOTAL_IS,
              TITLE_RT,
              TOTAL_RT,
              TITLE_OUT,
              TOTAL_OUT
         from TOTAL_ALL_V);
 
    select count(*)
      into v_count_WIPMTLTRX_ALL_1
      from WIPMTLTRX_ALL_1;
 
    /* Prepare XML for Excel */
 
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0"?>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?mso-application progid="Excel.Sheet"?>');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' xmlns:o="urn:schemas-microsoft-com:office:office"');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ' xmlns:x="urn:schemas-microsoft-com:office:excel"');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ' xmlns:html="http://www.w3.org/TR/REC-html40">');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      ' ');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  BHL');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  BHL');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '  2011-12-19T02:43:14Z');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '  2011-12-19T02:43:57Z');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  14.00');
    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,
                      '  7995');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '  20115');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  240');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  150');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '  False');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '  False');
    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, '    ');
    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,
                      '   False');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
                      '   False');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '  ');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
    FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
 
  END MAIN;

END WIPMTLTRX_1;

参考:http://www.mysjtu.com/page/M0/S693/693111.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26118480/viewspace-714389/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26118480/viewspace-714389/

cscy21287
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值