利用xml publisher開發報表

CREATE OR REPLACE PACKAGE APPS.cux_po_undelivery_pkg IS

    /**************************************************************************
    File name                : cux_po_undelivery_pkg.pck
    Doc Ref(s)               :
    Project                  :
    Description              : po have been received but not delivery
                              
    Change History Information
    --------------------------
    Version  Date         Author           Change Reference / Description
    -------  -----------  ---------------  ------------------------------------
    1.0      2009-07-31  Randolph            First Version
                          
    **************************************************************************/
    PROCEDURE xml_main(retbuffer    OUT VARCHAR2,
                       retcode      OUT NUMBER,
                       p_po_number  IN VARCHAR2,
                       p_vendor_id  IN NUMBER,
                       p_shipnum_fm IN VARCHAR2,
                       p_shipnum_to IN VARCHAR2,
                       p_date_fm    IN VARCHAR2,
                       p_date_to    IN VARCHAR2);
END cux_po_undelivery_pkg;
/

CREATE OR REPLACE PACKAGE BODY APPS.cux_po_undelivery_pkg IS
    /*=======================================================================
    --   PROCEDURE NAME:    output_xml()          
    =========================================================================*/
    PROCEDURE output_xml(p_tag   IN VARCHAR2,
                         p_value IN VARCHAR2) AS
    BEGIN
        fnd_file.put_line(fnd_file.output,
                          '' ||
                          nvl(p_value,
                              ' ') || '' || upper(TRIM(p_tag)) || '>');
    END output_xml;
    /*=======================================================================
    --   PROCEDURE NAME:    output_tail()          
    ========================================================================*/
    PROCEDURE output_tail(p_tag IN VARCHAR2) AS
    BEGIN
        fnd_file.put_line(fnd_file.output,
                          '' || upper(TRIM(p_tag)) || '>');
    END output_tail;
    /*=======================================================================
    --   PROCEDURE NAME:    output_header()          
    =========================================================================*/
    PROCEDURE output_header(p_tag IN VARCHAR2) AS
    BEGIN
        fnd_file.put_line(fnd_file.output,
                          '');
    END output_header;
    PROCEDURE xml_main(retbuffer    OUT VARCHAR2,
                       retcode      OUT NUMBER,
                       p_po_number  IN VARCHAR2,
                       p_vendor_id  IN NUMBER,
                       p_shipnum_fm IN VARCHAR2,
                       p_shipnum_to IN VARCHAR2,
                       p_date_fm    IN VARCHAR2,
                       p_date_to    IN VARCHAR2) IS
        CURSOR cur_data(c_date_fm IN DATE, c_date_to IN DATE) IS
            SELECT rt.transaction_type,
                   rt.primary_quantity,
                   to_char(rt.transaction_date,'YYYY-MM-DD') transaction_date,
                   ph.segment1 po_number,
                   pv.vendor_name,
                                     pv.segment1 vendor_num,
                   rsh.receipt_num,
                                     rt.unit_of_measure,
                                     rt.currency_code,
                   msi.concatenated_segments item_num,
                   msi.description item_desc,
                   NVL(cic.ITEM_COST,0)  cost_type
              FROM rcv_transactions      rt,
                   po_headers_all        ph,
                   po_vendors            pv,
                   rcv_shipment_headers  rsh,
                   po_lines_all          pl,
                   po_line_locations_all pll,
                   mtl_system_items_kfv  msi,
                   BOM.CST_ITEM_COSTS cic
             WHERE rt.transaction_type = 'RECEIVE'
               AND ph.po_header_id = rt.po_header_id
               AND ph.vendor_id = pv.vendor_id
               AND ph.po_header_id = pl.po_header_id
               AND pll.po_header_id = ph.po_header_id
               AND pll.po_line_id = pl.po_line_id
               AND msi.inventory_item_id = pl.item_id
               AND msi.organization_id = rt.organization_id
               AND msi.inventory_item_id =cic.INVENTORY_ITEM_ID(+)
               AND msi.organization_id =cic.ORGANIZATION_ID(+)
               AND cic.COST_TYPE_ID(+)=1
                  --parameter
               AND rt.transaction_date >=
                   nvl(c_date_fm,rt.transaction_date)
               AND rt.transaction_date <=
                   nvl(c_date_to + .99999,rt.transaction_date)
               AND ph.segment1 = nvl(p_po_number,ph.segment1)
               AND ph.vendor_id = nvl(p_vendor_id,ph.vendor_id)
               AND rsh.receipt_num >=
                   nvl(p_shipnum_fm,rsh.receipt_num)
               AND rsh.receipt_num <=
                   nvl(p_shipnum_to,rsh.receipt_num)
                  --
               AND rt.shipment_header_id = rsh.shipment_header_id(+)
               AND NOT EXISTS
             (SELECT 'x'
                      FROM rcv_transactions rt1
                     WHERE rt1.transaction_type = 'DELIVER'
                    CONNECT BY rt1.parent_transaction_id = PRIOR
                               rt1.transaction_id
                           AND PRIOR rt1.transaction_type = 'DELIVER'
                     START WITH rt1.transaction_id = rt.transaction_id);
        l_date_fm DATE;
        l_date_to DATE;
    BEGIN
        fnd_file.put_line(fnd_file.output,
                          '');
        output_header('G_DATA');
        l_date_fm := fnd_date.canonical_to_date(p_date_fm);
        l_date_to := fnd_date.canonical_to_date(p_date_to);
        FOR rec_data IN cur_data(l_date_fm,
                                 l_date_to) LOOP
            output_header('G_RECEIVING');
            output_xml('TRANSACTION_TYPE',
                       rec_data.transaction_type);
            output_xml('PRIMARY_QUANTITY',
                       rec_data.primary_quantity);
            output_xml('TRANSACTION_DATE',
                       rec_data.transaction_date);
            output_xml('PO_NUMBER',
                       rec_data.po_number);
            output_xml('VENDOR_NAME',
                       rec_data.vendor_name);
            output_xml('VENDOR_NUM',
                       rec_data.vendor_num);   
            output_xml('ITEM_NUM',
                       rec_data.item_num);   
            output_xml('ITEM_DESC',
                       rec_data.item_desc);
            output_xml('UOM',
                       rec_data.unit_of_measure);
             output_xml('StdCost',
                       rec_data.cost_type);
         
            output_xml('CURRENCY_CODE',
                       rec_data.currency_code);
            output_xml('RECEIPT_NUM',
                       rec_data.receipt_num);
            output_tail('G_RECEIVING');
        END LOOP;
        output_tail('G_DATA');
    EXCEPTION
        WHEN OTHERS THEN
            retbuffer := SQLCODE || ':' || SQLERRM;
            retcode   := 1;
    END xml_main;
END cux_po_undelivery_pkg;
/

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11536986/viewspace-620206/，如需轉載，請註明出處，否則將追究法律責任。