Report Using API

concurrent program : PPL - New Uninvoiced Receipts Report

CREATE OR REPLACE PACKAGE APPS.XXPPL_CST_UNINVRECEIPTS_REP_V1 AS
/* $Header: CSTVURRS.pls 120.1 2006/02/15 23:38:24 bigoyal noship $ */
-----------------------------------------------------------------------------
-- Start of comments
--   API name        : Start_Process
--   Type            : Private
--   Function        : Starting point for Uninvoiced Receipt Report
--
--   Pre-reqs        : None.
--   Parameters      :
--   IN              :
--     p_title                  VARCHAR2  Required
--     p_accrued_receipts       VARCHAR2  Required
--     p_inc_online_accruals    VARCHAR2  Required
--     p_inc_closed_pos         VARCHAR2  Required
--     p_struct_num             NUMBER    Required
--     p_category_from          VARCHAR2  Required
--     p_category_to            VARCHAR2  Required
--     p_min_extended_value     NUMBER    Required
--     p_period_name            VARCHAR2  Required
--     p_vendor_from            VARCHAR2  Required
--     p_vendor_to              VARCHAR2  Required
--     p_orderby                VARCHAR2  Required
--     p_qty_precision          NUMBER    Required
--
--   OUT             :
--     errbuf             VARCHAR2
--     retcode            NUMBER
--
--   Version : Current version       1.0
--
-- End of comments
-----------------------------------------------------------------------------
PROCEDURE Start_Process
(
    errbuf                          OUT     NOCOPY VARCHAR2,
    retcode                         OUT     NOCOPY NUMBER,
    p_title                         IN      VARCHAR2,
    p_accrued_receipts              IN      VARCHAR2,
    p_inc_online_accruals           IN      VARCHAR2,
    p_inc_closed_pos                IN      VARCHAR2,
    p_struct_num                    IN      NUMBER,
    p_category_from                 IN      VARCHAR2,
    p_category_to                   IN      VARCHAR2,
    p_min_accrual_amount            IN      NUMBER,
    p_period_name                   IN      VARCHAR2,
    p_vendor_from                   IN      VARCHAR2,
    p_vendor_to                     IN      VARCHAR2,
    p_orderby                       IN      NUMBER,
    p_qty_precision                 IN      NUMBER
);
-----------------------------------------------------------------------------
-- Start of comments
--   API name        : Generate_XML
--   Type            : Private
--   Function        : The procedure generates and returns the XML data for
--                     the reference cursor passed by the calling API.
--
--   Pre-reqs        : None.
--   Parameters      :
--   IN              :
--     p_api_version      NUMBER        Required
--     p_init_msg_list    VARCHAR2      Required
--     p_validation_level NUMBER        Required
--     p_ref_cur          SYS_REFCURSOR Required
--     p_row_tag          VARCHAR2      Required
--     p_row_set_tag      VARCHAR2      Required
--
--   OUT             :
--     x_return_status    VARCHAR2
--     x_msg_count        NUMBER
--     x_msg_data         VARCHAR2
--     x_xml_data         CLOB
--
--   Version : Current version       1.0
--
-- End of comments
-----------------------------------------------------------------------------
PROCEDURE Generate_XML
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_ref_cur                       IN      SYS_REFCURSOR,
    p_row_tag                       IN      VARCHAR2,
    p_row_set_tag                   IN      VARCHAR2,
    x_xml_data                      OUT     NOCOPY CLOB
);
-----------------------------------------------------------------------------
-- Start of comments
--   API name        : Merge_XML
--   Type            : Private
--   Function        : The procedure merges data from two XML objects into a
--                     single XML object and adds a root tag to the resultant
--                     XML data.
--
--   Pre-reqs        : None.
--   Parameters      :
--   IN              :
--     p_api_version      NUMBER       Required
--     p_init_msg_list    VARCHAR2     Required
--     p_validation_level NUMBER       Required
--     p_xml_src1         CLOB         Required
--     p_xml_src2         CLOB         Required
--     p_root_tag         VARCHAR2     Required
--
--   OUT             :
--     x_return_status    VARCHAR2
--     x_msg_count        NUMBER
--     x_msg_data         VARCHAR2
--     x_xml_doc          CLOB
--
--   Version : Current version       1.0
--
-- End of comments
-----------------------------------------------------------------------------
PROCEDURE Merge_XML
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_xml_src1                      IN      CLOB,
    p_xml_src2                      IN      CLOB,
    p_root_tag                      IN      VARCHAR2,
    x_xml_doc                       OUT     NOCOPY CLOB
);
-----------------------------------------------------------------------------
-- Start of comments
--   API name        : Print_ClobOutput
--   Type            : Private
--   Function        : The procedure writes the XML data to the report output
--                     file. The XML publisher picks the data from this output
--                     file to display the data in user specified format.
--
--   Pre-reqs        : None.
--   Parameters      :
--   IN              :
--     p_api_version      NUMBER       Required
--     p_init_msg_list    VARCHAR2     Required
--     p_validation_level NUMBER       Required
--     p_xml_data         CLOB
--
--   OUT             :
--     x_return_status    VARCHAR2
--     x_msg_count        NUMBER
--     x_msg_data         VARCHAR2
--
--   Version : Current version       1.0
--
-- End of comments
-----------------------------------------------------------------------------
PROCEDURE Print_ClobOutput
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_xml_data                      IN      CLOB
) ;
END XXPPL_CST_UNINVRECEIPTS_REP_V1;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXPPL_CST_UNINVRECEIPTS_REP_V1 AS
/* $Header: CSTVURRB.pls 120.7.12000000.6 2008/07/29 09:48:58 svelumur ship $ */
G_PKG_NAME CONSTANT VARCHAR2(30):='XXPPL_CST_UNINVRECEIPTS_REP_V1';
G_LOG_LEVEL CONSTANT NUMBER  := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
G_GL_APPLICATION_ID CONSTANT NUMBER       := 101;
G_PO_APPLICATION_ID CONSTANT NUMBER       := 201;
-----------------------------------------------------------------------------
-- PROCEDURE    :   Start_Process
-- DESCRIPTION  :   Starting point for Uninvoiced Receipt Report
-----------------------------------------------------------------------------
PROCEDURE Start_Process
(
    errbuf                          OUT     NOCOPY VARCHAR2,
    retcode                         OUT     NOCOPY NUMBER,
    p_title                         IN      VARCHAR2,
    p_accrued_receipts              IN      VARCHAR2,
    p_inc_online_accruals           IN      VARCHAR2,
    p_inc_closed_pos                IN      VARCHAR2,
    p_struct_num                    IN      NUMBER,
    p_category_from                 IN      VARCHAR2,
    p_category_to                   IN      VARCHAR2,
    p_min_accrual_amount            IN      NUMBER,
    p_period_name                   IN      VARCHAR2,
    p_vendor_from                   IN      VARCHAR2,
    p_vendor_to                     IN      VARCHAR2,
    p_orderby                       IN      NUMBER,
    p_qty_precision                 IN      NUMBER
)
IS
    l_api_name     CONSTANT         VARCHAR2(30) :='Start_Process';
    l_api_version  CONSTANT         NUMBER       := 1.0;
    l_return_status                 VARCHAR2(1);
    l_full_name    CONSTANT         VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
    l_module       CONSTANT         VARCHAR2(60) := 'cst.plsql.'||l_full_name;
    /* Log Severities*/
    /* 6- UNEXPECTED */
    /* 5- ERROR      */
    /* 4- EXCEPTION  */
    /* 3- EVENT      */
    /* 2- PROCEDURE  */
    /* 1- STATEMENT  */
    /* In general, we should use the following:
    G_LOG_LEVEL    CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
    l_uLog         CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
    l_errorLog     CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
    l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
    l_eventLog     CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
    l_pLog         CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
    l_sLog         CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
    */
    l_uLog         CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
    l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
    l_pLog         CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
    l_sLog         CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
    l_msg_count                     NUMBER;
    l_msg_data                      VARCHAR2(240);
    l_header_ref_cur                SYS_REFCURSOR;
    l_body_ref_cur                  SYS_REFCURSOR;
    l_row_tag                       VARCHAR2(100);
    l_row_set_tag                   VARCHAR2(100);
    l_xml_header                    CLOB;
    l_xml_body                      CLOB;
    l_xml_report                    CLOB;
    l_conc_status                   BOOLEAN;
    l_return                        BOOLEAN;
    l_status                        VARCHAR2(1);
    l_industry                      VARCHAR2(1);
    l_schema                        VARCHAR2(30);
    l_application_id                NUMBER;
    l_legal_entity                  NUMBER;
    l_end_date                      DATE;
    l_sob_id                        NUMBER;
    l_order_by                      VARCHAR2(15);
    l_multi_org_flag                VARCHAR2(1);
    l_stmt_num                      NUMBER;
    l_row_count                     NUMBER;
BEGIN
    l_stmt_num := 0;
    -- Procedure level log message for Entry point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.begin',
               'Start_Process <<' ||
               'p_title = '                 || p_title               ||','||
               'p_accrued_receipts = '      || p_accrued_receipts    ||','||
               'p_inc_online_accruals = '   || p_inc_online_accruals ||','||
               'p_inc_closed_pos = '        || p_inc_closed_pos      ||','||
               'p_struct_num = '            || p_struct_num          ||','||
               'p_category_from = '         || p_category_from       ||','||
               'p_category_to = '           || p_category_to         ||','||
               'p_min_accrual_amount = '    || p_min_accrual_amount  ||','||
               'p_period_name = '           || p_period_name         ||','||
               'p_vendor_from = '           || p_vendor_from         ||','||
               'p_vendor_to = '             || p_vendor_to           ||','||
               'p_orderby = '               || p_orderby             ||','||
               'p_qty_precision = '         || p_qty_precision
               );
    END IF;
    -- Initialize message list if p_init_msg_list is set to TRUE.
    FND_MSG_PUB.initialize;
    --  Initialize API return status to success
    l_return_status := FND_API.G_RET_STS_SUCCESS;
    -- Check whether GL is installed
    l_stmt_num := 10;
    l_return := FND_INSTALLATION.GET_APP_INFO (
                    'SQLGL',
                    l_status,
                    l_industry,
                    l_schema
                    );
    IF (l_status = 'I') THEN
        l_application_id := G_GL_APPLICATION_ID;
    ELSE
        l_application_id := G_PO_APPLICATION_ID;
    END IF;
    -- Convert Accrual Cutoff date from Legal entity timezone to
    -- Server timezone
    l_stmt_num := 20;
    SELECT set_of_books_id
    INTO   l_sob_id
    FROM   financials_system_parameters;
    SELECT  TO_NUMBER(org_information2)
    INTO    l_legal_entity
    FROM    hr_organization_information
    WHERE   organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
    AND     org_information_context = 'Operating Unit Information';
    l_stmt_num := 30;
    SELECT  INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE (gps.end_date,
                                                            l_legal_entity)
    INTO    l_end_date
    FROM    gl_period_statuses gps
    WHERE   gps.application_id = l_application_id
    AND     gps.set_of_books_id = l_sob_id
    AND     gps.period_name = NVL(p_period_name,
                                  (SELECT  gp.period_name
                                  FROM    gl_periods gp,
                                          gl_sets_of_books sob
                                  WHERE   sob.set_of_books_id = l_sob_id
                                  AND     sob.period_set_name = gp.period_set_name
                                  AND     sob.accounted_period_type = gp.period_type
                                  AND     gp.start_date <= TRUNC(SYSDATE)
                                  AND     gp.end_date >= TRUNC(SYSDATE))
                                  );
    ---------------------------------------------------------------------
    -- Call the common API CST_PerEndAccruals_PVT.Create_PerEndAccruals
    -- This API creates period end accrual entries in the temporary
    -- table CST_PER_END_ACCRUALS_TEMP.
    ---------------------------------------------------------------------
    l_stmt_num := 60;
    CST_PerEndAccruals_PVT.Create_PerEndAccruals (
        p_api_version           => 1.0,
        p_init_msg_list         => FND_API.G_FALSE,
        p_commit                => FND_API.G_FALSE,
        p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
        x_return_status         => l_return_status,
        x_msg_count             => l_msg_count,
        x_msg_data              => l_msg_data,
        p_min_accrual_amount    => p_min_accrual_amount,
        p_vendor_from           => p_vendor_from,
        p_vendor_to             => p_vendor_to,
        p_category_from         => p_category_from,
        p_category_to           => p_category_to,
        p_end_date              => l_end_date,
        p_accrued_receipt       => NVL(p_accrued_receipts, 'N'),
        p_online_accruals       => NVL(p_inc_online_accruals, 'N'),
        p_closed_pos            => NVL(p_inc_closed_pos, 'N'),
        p_calling_api           => CST_PerEndAccruals_PVT.G_UNINVOICED_RECEIPT_REPORT
    );
    -- If return status is not success, add message to the log
    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
        l_msg_data := 'Failed generating Period End Accrual information';
        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    l_stmt_num := 90;
    DBMS_LOB.createtemporary(l_xml_header, TRUE);
    DBMS_LOB.createtemporary(l_xml_body, TRUE);
    DBMS_LOB.createtemporary(l_xml_report, TRUE);
    -- Count the no. of rows in the accrual temp table
    -- l_row_count will be part of report header information
    l_stmt_num := 100;
    SELECT  COUNT('X')
    INTO    l_row_count
    FROM    CST_PER_END_ACCRUALS_TEMP
    WHERE   ROWNUM = 1;
    IF(p_orderby = 1) THEN
        l_order_by := 'Category';
    ELSIF(p_orderby = 2) THEN
        l_order_by := 'Vendor';
    ELSE
        l_order_by := ' ';
    END IF;
    -------------------------------------------------------------------------
    -- Open reference cursor for fetching data related to report header
    -------------------------------------------------------------------------
    l_stmt_num := 110;
    OPEN l_header_ref_cur FOR
        'SELECT gsb.name                        company_name,
                :p_title                        report_title,
                SYSDATE                         report_date,
                DECODE(:p_accrued_receipts,
                        ''Y'', ''Yes'',
                        ''N'', ''No'')          accrued_receipt,
                DECODE(:p_inc_online_accruals,
                        ''Y'', ''Yes'',
                        ''N'', ''No'')          include_online_accruals,
                DECODE(:p_inc_closed_pos,
                        ''Y'', ''Yes'',
                        ''N'', ''No'')          include_closed_pos,
                :p_category_from                category_from,
                :p_category_to                  category_to,
                :p_min_accrual_amount           minimum_accrual_amount,
                :p_period_name                  period_name,
                :p_vendor_from                  vendor_from,
                :p_vendor_to                    vendor_to,
                :l_order_by                     order_by,
                :l_row_count                    row_count
        FROM    gl_sets_of_books gsb
        WHERE   gsb.set_of_books_id = :l_sob_id'
        USING
                p_title,
                p_accrued_receipts,
                p_inc_online_accruals,
                p_inc_closed_pos,
                p_category_from,
                p_category_to,
                p_min_accrual_amount,
                p_period_name,
                p_vendor_from,
                p_vendor_to,
                l_order_by,
                l_row_count,
                l_sob_id;
    -- Set row_tag as HEADER for report header data
    l_row_tag := 'HEADER';
    l_row_set_tag := NULL;
    -- Generate XML data for header part
    l_stmt_num := 120;
    Generate_XML (
        p_api_version           => 1.0,
        p_init_msg_list         => FND_API.G_FALSE,
        p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
        x_return_status         => l_return_status,
        x_msg_count             => l_msg_count,
        x_msg_data              => l_msg_data,
        p_ref_cur               => l_header_ref_cur,
        p_row_tag               => l_row_tag,
        p_row_set_tag           => l_row_set_tag,
        x_xml_data              => l_xml_header
    );
    -- If return status is not success, add message to the log
    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
        l_msg_data := 'Failed generating XML data to the report output' ;
        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- If row_count is 0, no need to open body_ref_cursor
    IF (l_row_count > 0) THEN
        ---------------------------------------------------------------------
        -- Open reference cursor for fetching data related to report body
        ---------------------------------------------------------------------
        l_stmt_num := 140;
        OPEN l_body_ref_cur FOR
            'select poh.segment1                            po_number,
                    porl.release_num                        po_release_number,
                    poh.po_header_id                        po_header_id,
                    pol.po_line_id                          po_line_id,
                    cpea.shipment_id                        po_shipment_id,
                    cpea.distribution_id                    po_distribution_id,
                    plt.line_type                           line_type,
                    pol.line_num                            line_num,
                    msi.concatenated_segments               item_name,
                    mca.concatenated_segments               category,
                    pol.item_description                    item_description,
                    pov.vendor_name                         vendor_name,
                    fnc2.currency_code                      accrual_currency_code,
                    poll.shipment_num                       shipment_number,
                    poll.unit_meas_lookup_code              uom_code,
                    pod.distribution_num                    distribution_num,
                    cpea.quantity_received                  quantity_received,
                    cpea.quantity_billed                    quantity_billed,
                    cpea.accrual_quantity                   quantity_accrued,
                    round(cpea.unit_price,
                            nvl(fnc2.precision, 2))         po_unit_price,
                    cpea.currency_code                      po_currency_code,
                    round(decode(nvl(fnc1.minimum_accountable_unit, 0),
                                     0, cpea.unit_price * cpea.currency_conversion_rate,
                                     (cpea.unit_price / fnc1.minimum_accountable_unit)
                                        * cpea.currency_conversion_rate
                                        * fnc1.minimum_accountable_unit), nvl(fnc1.precision, 2))
                                                            func_unit_price,
                    gcc1.concatenated_segments              charge_account,
                    gcc2.concatenated_segments              accrual_account,
                    cpea.accrual_amount                     accrual_amount,
                    round(decode(nvl(fnc1.minimum_accountable_unit, 0),
                                     0, cpea.accrual_amount * cpea.currency_conversion_rate,
                                     (cpea.accrual_amount / fnc1.minimum_accountable_unit)
                                        * cpea.currency_conversion_rate
                                        * fnc1.minimum_accountable_unit), nvl(fnc1.precision, 2))
                                                            func_accrual_amount,
                    xla_oa_functions_pkg.get_ccid_description(gcc1.chart_of_accounts_id
                                                             ,gcc1.code_combination_id)
                                                             charge_account_desc,
                    xla_oa_functions_pkg.get_ccid_description(gcc2.chart_of_accounts_id
                                                             ,gcc2.code_combination_id)
                                                             accrual_account_desc,
                    xxppl_receipt_num_date(poll.line_location_id,
                                           poh.po_header_id,pol.po_line_id) receipt_num,
                    xxppl_sub_inv_rep(poll.line_location_id,
                                      poh.po_header_id,pol.po_line_id) sub_inv,
                    xxppl_dc_number_rep(poll.line_location_id,
                                        poh.po_header_id,pol.po_line_id) dc_no,
                    xxppl_dc_date_rep(poll.line_location_id,
                                      poh.po_header_id,pol.po_line_id) dc_date,
                    xxppl_tax_category_rep(poh.po_header_id,pol.po_line_id) tax_catg,
                    xxppl_tax_catg_desc_rep(poh.po_header_id,pol.po_line_id) tax_catg_desc                                                                                                                                                                                                         
            from    cst_per_end_accruals_temp   cpea,
                    po_headers_all              poh,
                    po_lines_all                pol,
                    po_line_locations_all       poll,
                    po_distributions_all        pod,
                    po_vendors                  pov,
                    po_line_types               plt,
                    po_releases_all             porl,
                    mtl_system_items_kfv        msi,
                    fnd_currencies              fnc1,
                    fnd_currencies              fnc2,
                    mtl_categories_kfv          mca,
                    gl_code_combinations_kfv    gcc1,
                    gl_code_combinations_kfv    gcc2,
                    gl_sets_of_books sob
            where   pod.po_distribution_id = cpea.distribution_id
            and     poh.po_header_id = pol.po_header_id
            and     pol.po_line_id = poll.po_line_id
            and     poll.line_location_id = pod.line_location_id
            and     pol.line_type_id = plt.line_type_id
            and     porl.po_release_id (+)  = poll.po_release_id
            and     poh.vendor_id = pov.vendor_id
            and     msi.inventory_item_id (+)  = pol.item_id
            and     (msi.organization_id is null
                    or
                    (msi.organization_id = poll.ship_to_organization_id and msi.organization_id is not null))
            and     fnc1.currency_code =  cpea.currency_code
            and     fnc2.currency_code = sob.currency_code
            and     cpea.category_id = mca.category_id
            and     gcc1.code_combination_id = pod.code_combination_id
            and     gcc2.code_combination_id = pod.accrual_account_id
            and     sob.set_of_books_id = :l_sob_id
            order by decode(:l_order_by,
                            ''Category'', mca.concatenated_segments,
                            ''Vendor'', pov.vendor_name),
                    poh.segment1,
                    pol.line_num,
                    poll.shipment_num,
                    pod.distribution_num'
            USING   l_sob_id, l_order_by
            ;
        l_row_tag := 'BODY';
        l_row_set_tag := 'ACCRUAL_INFO';
        -- Generate XML data for report body
        l_stmt_num := 150;
        Generate_XML (
            p_api_version           => 1.0,
            p_init_msg_list         => FND_API.G_FALSE,
            p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
            x_return_status         => l_return_status,
            x_msg_count             => l_msg_count,
            x_msg_data              => l_msg_data,
            p_ref_cur               => l_body_ref_cur,
            p_row_tag               => l_row_tag,
            p_row_set_tag           => l_row_set_tag,
            x_xml_data              => l_xml_body
        );
        -- If return status is not success, add message to the log
        IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
            l_msg_data := 'Failed generating XML data to the report output' ;
            RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
        END IF;
    END IF;
    -- Merge the header part with the body part.
    -- 'ACR_REPORT' will be used as root tag for resultant XML data
    l_stmt_num := 160;
    Merge_XML
    (
        p_api_version           => 1.0,
        p_init_msg_list         => FND_API.G_FALSE,
        p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
        x_return_status         => l_return_status,
        x_msg_count             => l_msg_count,
        x_msg_data              => l_msg_data,
        p_xml_src1              => l_xml_header,
        p_xml_src2              => l_xml_body,
        p_root_tag              => 'ACR_REPORT',
        x_xml_doc               => l_xml_report
    );
    -- If return status is not success, add message to the log
    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
        l_msg_data := 'Failed generating XML data to the report output' ;
        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Print the XML data to the report output
    l_stmt_num := 170;
    Print_ClobOutput(
        p_api_version           => 1.0,
        p_init_msg_list         => FND_API.G_FALSE,
        p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
        x_return_status         => l_return_status,
        x_msg_count             => l_msg_count,
        x_msg_data              => l_msg_data,
        p_xml_data              => l_xml_report
        );
    -- If return status is not success, add message to the log
    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
        l_msg_data := 'Failed writing XML data to the report output' ;
        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Write log messages to request log
    l_stmt_num := 180;
    CST_UTILITY_PUB.writelogmessages (
        p_api_version   => 1.0,
        p_msg_count     => l_msg_count,
        p_msg_data      => l_msg_data,
        x_return_status => l_return_status
        );
    -- If return status is not success, add message to the log
    IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
        l_msg_data := 'Failed writing log messages' ;
        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Procedure level log message for exit point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.end',
               'Start_Process >>'
               );
    END IF;
EXCEPTION
    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
        IF (l_exceptionLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_EXCEPTION,
               l_module || '.' || l_stmt_num,
               l_msg_data
               );
        END IF;
        -- Write log messages to request log
        CST_UTILITY_PUB.writelogmessages (
            p_api_version   => 1.0,
            p_msg_count     => l_msg_count,
            p_msg_data      => l_msg_data,
            x_return_status => l_return_status
            );
        -- Set concurrent program status to error
        l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_msg_data);
    WHEN OTHERS THEN
        -- Unexpected level log message for FND log
        IF (l_uLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_UNEXPECTED,
               l_module || '.' || l_stmt_num,
               SQLERRM
               );
        END IF;
        IF      FND_MSG_PUB.Check_Msg_Level
                (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (       G_PKG_NAME,
                    l_api_name,
                    '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
            );
        END IF;
        -- Write log messages to request log
        CST_UTILITY_PUB.writelogmessages (
            p_api_version   => 1.0,
            p_msg_count     => l_msg_count,
            p_msg_data      => l_msg_data,
            x_return_status => l_return_status
            );
        -- Set concurrent program status to error
        l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
                         'An unexpected error has occurred, please contact System Administrator. ');
END Start_Process;
-----------------------------------------------------------------------------
-- PROCEDURE    :   Generate_XML
-- DESCRIPTION  :   The procedure generates and returns the XML data for
--                  the reference cursor passed by the calling API.
-----------------------------------------------------------------------------
PROCEDURE Generate_XML
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_ref_cur                       IN      SYS_REFCURSOR,
    p_row_tag                       IN      VARCHAR2,
    p_row_set_tag                   IN      VARCHAR2,
    x_xml_data                      OUT     NOCOPY CLOB
)
IS
    l_api_name     CONSTANT         VARCHAR2(30) :='Generate_XML';
    l_api_version  CONSTANT         NUMBER       := 1.0;
    l_return_status                 VARCHAR2(1);
    l_full_name    CONSTANT         VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
    l_module       CONSTANT         VARCHAR2(60) := 'cst.plsql.'||l_full_name;
    l_uLog         CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
    l_pLog         CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
    l_sLog         CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
    l_stmt_num                      NUMBER;
    l_ctx                           DBMS_XMLGEN.CTXHANDLE;
BEGIN
    l_stmt_num := 0;
    -- Procedure level log message for Entry point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.begin',
               'Generate_XML <<');
    END IF;
    -- Standard call to check for call compatibility.
    IF NOT FND_API.Compatible_API_Call ( l_api_version,
                                         p_api_version,
                                         l_api_name,
                                         G_PKG_NAME )
    THEN
           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Initialize message list if p_init_msg_list is set to TRUE.
    IF FND_API.to_Boolean( p_init_msg_list ) THEN
           FND_MSG_PUB.initialize;
    END IF;
    --  Initialize API return status to success
    x_return_status     := FND_API.G_RET_STS_SUCCESS;
    l_return_status     := FND_API.G_RET_STS_SUCCESS;
    -- create a new context with the SQL query
    l_stmt_num := 10;
    l_ctx := DBMS_XMLGEN.newContext (p_ref_cur);
    -- Add tag names for rows and row sets
    l_stmt_num := 20;
    DBMS_XMLGEN.setRowSetTag(l_ctx, p_row_tag);
    DBMS_XMLGEN.setRowTag(l_ctx, p_row_set_tag);
    -- generate XML data
    l_stmt_num := 30;
    x_xml_data := DBMS_XMLGEN.getXML (l_ctx);
    -- close the context
    l_stmt_num := 40;
    DBMS_XMLGEN.CLOSECONTEXT(l_ctx);
    -- Procedure level log message for exit point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.end',
               'Generate_XML >>'
               );
    END IF;
    -- Get message count and if 1, return message data.
    FND_MSG_PUB.Count_And_Get
    (       p_count                 =>      x_msg_count,
            p_data                  =>      x_msg_data
    );
EXCEPTION
    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
    WHEN OTHERS THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
        -- Unexpected level log message
        IF (l_uLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_UNEXPECTED,
               l_module || '.' || l_stmt_num,
               SQLERRM
               );
        END IF;
        IF      FND_MSG_PUB.Check_Msg_Level
                (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (       G_PKG_NAME,
                    l_api_name,
                    '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
            );
        END IF;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
END Generate_XML;
-----------------------------------------------------------------------------
-- PROCEDURE    :   Merge_XML
-- DESCRIPTION  :   The procedure merges data from two XML objects into a
--                  single XML object and adds a root tag to the resultant
--                  XML data.
-----------------------------------------------------------------------------
PROCEDURE Merge_XML
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_xml_src1                      IN      CLOB,
    p_xml_src2                      IN      CLOB,
    p_root_tag                      IN      VARCHAR2,
    x_xml_doc                       OUT     NOCOPY CLOB
)
IS
    l_api_name     CONSTANT         VARCHAR2(30) :='Merge_XML';
    l_api_version  CONSTANT         NUMBER       := 1.0;
    l_return_status                 VARCHAR2(1);
    l_full_name    CONSTANT         VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
    l_module       CONSTANT         VARCHAR2(60) := 'cst.plsql.'||l_full_name;
    l_uLog         CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
    l_pLog         CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
    l_sLog         CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
    l_ctx                           DBMS_XMLGEN.CTXHANDLE;
    l_offset                        NUMBER;
    l_stmt_num                      NUMBER;
    l_length_src1                   NUMBER;
    l_length_src2                   NUMBER;
    /*Bug 7282242*/
    l_encoding             VARCHAR2(30);
    l_xml_header           VARCHAR2(100);
BEGIN
    l_stmt_num := 0;
    -- Procedure level log message for Entry point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.begin',
               'Merge_XML <<');
    END IF;
    -- Standard call to check for call compatibility.
    IF NOT FND_API.Compatible_API_Call ( l_api_version,
                                         p_api_version,
                                         l_api_name,
                                         G_PKG_NAME )
    THEN
           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Initialize message list if p_init_msg_list is set to TRUE.
    IF FND_API.to_Boolean( p_init_msg_list ) THEN
           FND_MSG_PUB.initialize;
    END IF;
    --  Initialize API return status to success
    x_return_status := FND_API.G_RET_STS_SUCCESS;
    l_return_status := FND_API.G_RET_STS_SUCCESS;
    l_stmt_num := 10;
    l_length_src1 := DBMS_LOB.GETLENGTH(p_xml_src1);
    l_length_src2 := DBMS_LOB.GETLENGTH(p_xml_src2);
    l_stmt_num := 20;
    DBMS_LOB.createtemporary(x_xml_doc, TRUE);
    IF (l_length_src1 > 0) THEN
        -- Get the first occurence of XML header
        l_stmt_num := 30;
        l_offset := DBMS_LOB.instr (lob_loc => p_xml_src1,
                                    pattern => '>',
                                    offset  => 1,
                                    nth     => 1);
        -- Copy XML header part to the destination XML doc
        l_stmt_num := 40;
    /*Bug 7282242*/
    /*Remove the header (21 characters)*/
    --DBMS_LOB.copy (x_xml_doc, p_xml_src1, l_offset + 1);
        /*The following 3 lines of code ensures that XML data generated here uses the right encoding*/
    l_encoding       := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
    l_xml_header     := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
    DBMS_LOB.writeappend (x_xml_doc, length(l_xml_header), l_xml_header);
        -- Append the root tag to the XML doc
        l_stmt_num := 50;
        DBMS_LOB.writeappend (x_xml_doc, LENGTH(p_root_tag) + 2, '<' || p_root_tag || '>');
        -- Append the 1st XML doc to the destination XML doc
        l_stmt_num := 60;
        DBMS_LOB.copy ( x_xml_doc,
                        p_xml_src1,
                        l_length_src1 - l_offset,
                        DBMS_LOB.GETLENGTH(x_xml_doc) + 1,
                        l_offset + 1
                        );
        -- Append the 2nd XML doc to the destination XML doc
        IF (l_length_src2 > 0) THEN
            l_stmt_num := 70;
            DBMS_LOB.copy ( x_xml_doc,
                            p_xml_src2,
                            l_length_src2 - l_offset,
                            DBMS_LOB.GETLENGTH(x_xml_doc) + 1,
                            l_offset + 1
                            );
        END IF;
        -- Append the root tag to the end of XML doc
        l_stmt_num := 80;
        DBMS_LOB.writeappend (x_xml_doc, LENGTH(p_root_tag) + 3, '</' || p_root_tag || '>');
    END IF;
    -- Procedure level log message for exit point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.end',
               'Merge_XML >>'
               );
    END IF;
    -- Get message count and if 1, return message data.
    FND_MSG_PUB.Count_And_Get
    (       p_count                 =>      x_msg_count,
            p_data                  =>      x_msg_data
    );
EXCEPTION
    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
    WHEN OTHERS THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
        -- Unexpected level log message
        IF (l_uLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_UNEXPECTED,
               l_module || '.' || l_stmt_num,
               SQLERRM
               );
        END IF;
        IF      FND_MSG_PUB.Check_Msg_Level
                (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (       G_PKG_NAME,
                    l_api_name,
                    '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
            );
        END IF;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
END Merge_XML;
-----------------------------------------------------------------------------
-- PROCEDURE    :   Merge_XML
-- DESCRIPTION  :   The procedure writes the XML data to the report output
--                  file. The XML publisher picks the data from this output
--                  file to display the data in user specified format.
-----------------------------------------------------------------------------
PROCEDURE Print_ClobOutput
(
    p_api_version                   IN      NUMBER,
    p_init_msg_list                 IN      VARCHAR2,
    p_validation_level              IN      NUMBER,
    x_return_status                 OUT     NOCOPY VARCHAR2,
    x_msg_count                     OUT     NOCOPY NUMBER,
    x_msg_data                      OUT     NOCOPY VARCHAR2,
    p_xml_data                      IN      CLOB
)
IS
    l_api_name     CONSTANT         VARCHAR2(30) :='Print_ClobOutput';
    l_api_version  CONSTANT         NUMBER       := 1.0;
    l_return_status                 VARCHAR2(1);
    l_full_name    CONSTANT         VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
    l_module       CONSTANT         VARCHAR2(60) := 'cst.plsql.'||l_full_name;
    l_uLog         CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
    l_pLog         CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
    l_sLog         CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
    l_stmt_num                      NUMBER;
    l_amount                        NUMBER;
    l_offset                        NUMBER;
    l_length                        NUMBER;
    l_data                          VARCHAR2(32767);
BEGIN
    l_stmt_num := 0;
    -- Procedure level log message for Entry point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.begin',
               'Print_ClobOutput <<');
    END IF;
    -- Standard call to check for call compatibility.
    IF NOT FND_API.Compatible_API_Call ( l_api_version,
                                         p_api_version,
                                         l_api_name,
                                         G_PKG_NAME )
    THEN
           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
    END IF;
    -- Initialize message list if p_init_msg_list is set to TRUE.
    IF FND_API.to_Boolean( p_init_msg_list ) THEN
           FND_MSG_PUB.initialize;
    END IF;
    --  Initialize API return status to success
    x_return_status := FND_API.G_RET_STS_SUCCESS;
    l_return_status := FND_API.G_RET_STS_SUCCESS;
    -- Get length of the CLOB p_xml_data
    l_stmt_num := 10;
    l_length := nvl(DBMS_LOB.getlength(p_xml_data), 0);
    -- Set the offset point to be the start of the CLOB data
    l_offset := 1;
    -- l_amount will be used to read 32KB of data once at a time
    l_amount := 16383;  --Changed for bug 6954937
  -- Loop until the length of CLOB data is zero
  l_stmt_num := 20;
  LOOP
    EXIT WHEN l_length <= 0;
    -- Read 32 KB of data and print it to the report output
    DBMS_LOB.read (p_xml_data, l_amount, l_offset, l_data);
    FND_FILE.PUT(FND_FILE.OUTPUT, l_data);
    l_length := l_length - l_amount;
    l_offset := l_offset + l_amount;
  END LOOP;
    -- Procedure level log message for exit point
    IF (l_pLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_PROCEDURE,
               l_module || '.end',
               'Print_ClobOutput >>'
               );
    END IF;
    -- Get message count and if 1, return message data.
    FND_MSG_PUB.Count_And_Get
    (       p_count                 =>      x_msg_count,
            p_data                  =>      x_msg_data
    );
EXCEPTION
    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
    WHEN OTHERS THEN
        x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
        -- Unexpected level log message
        IF (l_uLog) THEN
           FND_LOG.STRING(
               FND_LOG.LEVEL_UNEXPECTED,
               l_module || '.' || l_stmt_num,
               SQLERRM
               );
        END IF;
        IF FND_MSG_PUB.Check_Msg_Level
                (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (       G_PKG_NAME,
                    l_api_name,
                    '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
            );
        END IF;
        FND_MSG_PUB.Count_And_Get
        (       p_count                 =>      x_msg_count,
                p_data                  =>      x_msg_data
        );
END Print_ClobOutput;
END XXPPL_CST_UNINVRECEIPTS_REP_V1;
/

TK PROF trace file for report/form/ conc program

1. How to generate a table or view?
2. How to generate a form, library or menu?
3. How to generate a report?
4. How to generate a trace file for a form?
5. How to generate a trace file for a report or concurrent program?
6. How to find the trace file?
7. How to format the trace file?
8. How to get the version of forms?
9. How to get the version of reports?
10. How to get the version of libraries?
11. How to get the version of packages (SQL or PL/SQL scripts)?
12. How to find a file?
13. How to view the text of a view?
QUESTIONS & ANSWERS
-------------------
1. How to generate a table or view?
Answer
------
11.0 and 11i: adodfcmp
Table:
adodfcmp \
odffile=.odf userid=ar/ar changedb=yes mode=tables \
touser=apps/apps priv_schema=system/manager
View:
adodfcmp \
odffile=.odf userid=apps/apps changedb=yes mode=views \
touser=apps/apps priv_schema=system/manager
References
----------
Note 137176.1 Ext/Pub The ADODFCMP Utility
Note 69756.1 Int/Pub ADODFCMP and Constraints within Oracle Applications
2. How to generate a form, library or menu?
Answer
------
11.0: f45gen
11i: f60gen
windows NT: ifcmp60nt
Form:
cd $AU_TOP/forms/US
f45gen module=.fmb userid=apps/ module_type=FORM
output_file=.fmx batch=yes compile_all=special
Copy the .fmx to the correct directory.
For example: $AP_TOP/forms/US
Library:
cd $AU_TOP/resource
f45gen module=.pll userid=apps/ module_type=LIBRARY
output_file=.plx batch=yes compile_all=special
Menu:
f45gen module=.mmb userid=apps/ module_type=MENU
output_file=.mmx batch=yes compile_all=special
References
----------
Note 130686.1 Ext/Pub How to Generate Form, Library and Menu for Oracle Applications
3. How to generate a report?
Answer
------
11.0: r25convm
11i: rwcon60
r25convm userid=apps/ source=.rdf dest=.rex
stype=rdffile dtype=rexfile logfile=.log overwrite=yes batch=yes
r25convm userid=apps/ source=.rex dest=.rdf
stype=rexfile dtype=rdffile logfile=.log overwrite=yes batch=yes
References
----------
Note 66703.1 Ext/Pub Batch Generation of Reports
Note 106504.1 Ext/Pub Payables Reports Frequently Asked Questions
4. How to generate a trace file for a form?
Answer
------
11.0:
Open the form and select Help -> Tools -> Trace
11i:
Open the form and select Help -> Diagnostics -> Trace with binds
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
5. How to generate a trace file for a report or concurrent program?
Answer
------
11.0 and 11i:
Select the Enable Trace flag in the concurrent program definition.
Responsibility: System Administrator
Path: Concurrent -> Program -> Define
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
6. How to find the trace file?
Answer
------
The directory where the trace files reside is defined by variable USER_DUMP_DEST,
if it is not set, execute the following sql command from sqlplus (apps account):
select value from v$parameter
where name='user_dump_dest';
Trace files are named ora_.trc. To retrieve your trace file you can try
unix command: ls -lt
Verify timestamp and also search a characteristic string with grep command.
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
7. How to format the trace file?
Answer
------
Use tkprof utility to format the raw trace in more readable file.
tkprof sys=no explain=apps/
References
----------
Note 130182.1 Ext/Rev HOW TO TRACE FROM SCREEN, REPORT, PROGRAM AND OTHERS IN ORACLE APPLICATIONS
Note 142898.1 Ext/Pub How To Use Tkprof and Trace With Applications
8. How to get the version of forms?
Answer
------
11.0:
Open the form and select Help -> Tools -> About Oracle Applications
Verify the form name and version in the Current Form section.
11i:
Open the form and select Help -> Diagnostics -> About Oracle Applications
Verify the form name and version in the Current Form section.
You can verify the version to the Oracle Applications, Oracle Forms and RDBMS using this option.
or go to the directory of the file (for example: cd $AP_TOP/forms/US):
cd $/forms/
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
9. How to get the version of reports?
Answer
------
Go to the directory of the file (for example: cd $AP_TOP/reports/US):
cd $/reports/
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
10. How to get the version of libraries?
Answer
------
cd $AU_TOP/resource
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
11. How to get the version of packages (SQL or PL/SQL scripts)?
Answer
------
Go to $/admin/sql or /patch/110/sql for last version.
You should find the corresponding file with .sql, .pls, .pkh, or .pkb extension.
On Unix:
strings -a * |grep -i '$Header'
On Windows (DOS box) :
find "Header"
or use the following script in the sql&plus:
select text
from dba_source
where name like ''
and line in (1,2,3,4);
References
----------
Note 121916.1 How To Get Version of Programs, Files, Objects Necessary for Oracle Applications
12. How to find a file?
Answer
------
Execute the following commands on unix:
cd $
find . -name '**' -print
find /i "$Header"
grep -i '' *
References
----------
Note 136962.1 Int/Rev How can we find which C source files contain a specific symbol or string
13. How to view the text of a view?
Answer
------
Execute the following script in the sql&plus:
select text
from all_views
where view_name =''
and owner = 'APPS';
References
----------
Note 1018636.102 Ext/Pub HOW TO SEE THE SELECT STATEMENT THAT DEFINES A VIEW.
Note 1075505.6 Ext/Arc HOW DO YOU FIND THE SCRIPT THAT CREATES A PARTICULAR VIEW?
Note 1016346.102 Ext/Pub GENERATE VIEW CREATION SCRIPT
Mohammed Riyaz Ahmed Khan
“The PATHAN Boy”
Using TKPROF and Explain Plan with Oracle -- Contents
Oracle TRACE Utility
Simple Steps for TRACE with a Simple Query
The Sections of a TRACE Output
The SQL Statement
The Statistics Section
Information Section
The EXPLAIN PLAN
A More Complex TKPROF Output
Using EXPLAIN PLAN Alone
An Additional EXPLAIN PLAN Example for a Simple Query
EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?
Reading the EXPLAIN PLAN
Setting AUTOTRACE On
Oracle TRACE Utility
The Oracle TRACE utility is used to measure timing statistics for a given query, a batch process, or an entire system. It is a fast method of finding where potential bottlenecks on the system reside. TRACE has the following functionality:
TRACE runs the query and generates statistics about an Oracle query that is executed.
TRACE helps developers analyze every section of a query.
Simple Steps for TRACE with a Simple Query
The steps for setting up and running Oracle's TRACE utility are as follows:
Set the following init.ora parameters:
  TIMED.STATISTICS = TRUE
  MAX_DUMP_FILE_SIZE = 2000000 (Not 2M)
  USER_DUMP_DEST = /oracle8/rich_trc
 
In Oracle7, the database must be shut down and restarted for these parameters to take effect. In Oracle8, the TIMED_STATISTICS parameter may be set via an ALTER SESSION (for an individual session) or ALTER SYSTEM (for the entire system) command. The USER_DUMP_DEST specifies the location to put the files and the MAX_DUMP_FILE_SIZE specifies the maximum file size.
Enable TRACE for a SQL*Plus session (this starts TRACing for an individual session):
  alter session set SQL_TRACE true;
 
Run the query to be TRACEd:
  select    table_name,
            owner,
            initial_extent,
            uniqueness
  from     ind2
  where     owner || '' = 'SCOTT';
  --    (Note: An index on "OWNER" is suppressed)
 
Disable TRACE for the SQL*Plus session:
  alter session set SQL_TRACE false;
 
You can also enable TRACE for all sessions by setting the SQLTRACE parameter in the init.ora. You must shut down and restart the database for this to take effect. This is not suggested!
  SQL_TRACE = TRUE
 
After running TRACE, your output file will look something like the following:
  5_19554.trc
 
TIP Setting TIMED_STATISTICS=TRUE in the init.ora will begin TRACing upon the user's command. But, be careful; setting SQLTRACE=TRUE in the init.ora will cause the entire system and all queries to be TRACEd and could cause performance degradations.
Run TKPROF to put the TRACE file into readable format:
  tkprof 5_19554.trc rich2.prf explain=system/manager
 
The TKPROF utility translates the TRACE file generated by the SQLTRACE facility to a readable format. You can run TKPROF against a TRACE file that you have previously created, or you can run it while the program that is creating the TRACE file is still running. Options for TKPROF are listed next.
  tkprof tracefile output_file [sort = parameters]
  [print =number] [explain=username/password@sid]
 
Command-Line Options:
TRACEfile
The name of the TRACE file containing the statistics by SQL_TRACE.
output_file
The name of the file where TKPROF writes its output.
SORT= parameters
The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.
PRINT=number
The number of statements to include in the output. included, TKPROF will list all statements in the output.
EXPLAIN=username/password@sid
Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.
New options in 7.3+:
INSERT= filename
This option creates a script to create a table and store the TRACE file statistics for each SQL statement TRACEd.
RECORD= filename
This option will produce a file of all the user's SQL statements.
SYS= YES/NO
This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.
SORT= parameters
There is a tremendous number of sorting options that are available. My favorites are FCHCPU (CPU time of fetch), FCHDSK (disk reads for fetch), FCHCU and FCHQRY (memory reads for fetch), FCH ROW (number of rows fetched), EXEDSK (disk reads during execute), EXECU and EXEQRY (memory reads during execute), EXEROW (rows processed during execute), EXECPU (execute CPU time), and PRSCNT (times parsed).
TIP
The TKPROF utility puts a TRACEd output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query.

The output of the file rich2.prf (query with the index suppressed):
  select    table_name,
            owner,
            initial_extent,
            uniqueness
  from      ind2
  where     owner = 'SCOTT';
 count cpu elap disk query current   rows
Parse:     1     1        2       0        0         0
Execute:    1     0        0       0        0         2      0
Fetch:     2    69      113     142      430         0     36
 
Execution Plan (no index used):
TABLE ACCESS (FULL) OF 'IND2'
 
The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem.
TIP A TRACEd query with a large number of physical reads usually indicates a missing index. The disk column indicates the physical reads (usually where an index is not used) and the query added to the current columns indicates the memory reads (usually reads where an index is being used).

Here's what happens when I rerun the query (after restarting the system) to be TRACEd, now using an index on the owner table:
  select    table_name,
            owner,
            initial_extent,
            uniqueness
  from      ind2
  where     owner = 'SCOTT';
  (The index on "OWNER" is not suppressed)
 count cpu elap disk query current   rows
Parse:     2     0        0       0        0         0
Execute:    2     0        0       0        0         0      0
Fetch:     4     6        6       0      148         0     72
 
Execution Plan (index used):
TABLE ACCESS (BY ROWID) OF 'IND2'
  INDEX (RANGE SCAN) OF 'IND2_1' (NON-UNIQUE)
 
TIP
A TRACEd query output with only memory reads (query-consistent reads) indicates that an index is being used.
The Sections of a TRACE Output
The TRACE utility has multiple sections including the SQL statements, statistics, information, and the EXPLAIN PLAN. Each of these different topics are discussed in the following sections.
The SQL Statement
The first section of a TKPROF statement is the SQL statement. This statement will be the exact same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output. This can be helpful when you are reviewing the output from multiple sessions. If you find a statement causing problems, you would be able to search for the exact statement. Remember, some of the statements from Oracle forms are generated dynamically.
The Statistics Section
This section contains all the statistics for this SQL statement and all the recursive SQL statements generated to satisfy this statement. In this section there are eight columns, the first being the type of call to the database. There are three types of calls, parse, execute, and fetch. Each type of call will generate a separate line of statistics. The other seven columns are the statistics for each type of call.
count
The number of times this type of call was made.
cpu
The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.
elapsed
The total elapsed time for this call.
disk
The total number of data blocks retrieved from disk to satisfy this call.
query
The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.
current
The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.
rows
The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.
Information Section
This section contains information about the number of misses in the library cache from parse and execute calls. If the number of misses is high, there may be a problem with the size of the Shared Pool. You should check the hit ratio and the reload rate of the library cache. There is also information about the current optimizer mode setting. This section shows the username of the last user to parse this statement.
The EXPLAIN PLAN
This is the section of the TKPROF I find to be the most useful. The first column of this section of the TRPROF output listing below, is the number of rows processed by each line of the execution plan. Here, you will be able to see how bad a statement is. If the total number of rows in the Fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN, you may want to review the statement.
It is also possible that there is only one line of the execution plan that is processing a large number of rows compared to the rest of the statement. This can be caused by full table scans or the use of a bad index.
A More Complex TKPROF Output
The following example illustrates a TRACEd query with a slightly higher complexity.
select    Item_Item_Id, InitCap( Item_Description )
from      Item
where     Item_Classification = 1
and       Item_Item_Id Between 1000000 And 2700000
and       Item_Item_Id Not In ( Select Invitem_Item_Id
          from  Inventory_Item
          where Invitem_Location_Id = '405')
call count     cpu elapsed    disk     query   current  rows
Parse     1    0.00      0.00       0         0         0     0
Execute     1    0.00      0.00       0         0         0     0
Fetch      27   20.87     21.24       0      4408         0   399
Totals     29   20.87     21.24       0      4408         0   399
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 106  (C12462)
Rows   Execution Plan
0      SELECT STATEMENT OPTIMIZER HINT: CHOOSE
572    FILTER
598       TABLE ACCESS (BY ROWID) OF 'ITEM'
599   INDEX (RANGE SCAN) OF 'ITEM_PK' (UNIQUE)
278790   INDEX (RANGE SCAN) OF 'INVITEM_PK' (UNIQUE)
Some of the things to look for in the TKPROF output are listed in this table:
Problems Solutions
High numbers for the parsing The SHARED_POOL_SIZE may need to be increased.
The disk reads are very high Indexes are not used or may not exist.
The "query" and/or "current" (memory reads) are very high Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance.
The parse elapse time is high There may be a problem with the number of open cursors.
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement.
If the number of misses in the library cache during parse is greater than 1 This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora.

Using EXPLAIN PLAN Alone
The EXPLAIN PLAN command allows a developer to view the query execution plan that the Oracle optimizer will use to execute a SQL statement. This command is very helpful in improving performance of SQL statements, since it does not actually execute the SQL statement--it only outlines the plan to use and inserts this execution plan in an Oracle table. Prior to using the EXPLAIN PLAN command, a file called UTLXPLAN.sql (located in the same directory as CATALOG.sql) must be executed under the Oracle account that will be executing the EXPLAIN PLAN command. The script creates a table called PLAN_TABLE that is used by the EXPLAIN PLAN command to insert the query execution plan in the form of records. This table can then be queried and viewed to determine if there needs to be any modifications to the SQL statement to force a different execution plan. An EXPLAIN PLAN example is shown next (executed in SQL*Plus).
Q.  Why use EXPLAIN PLAN without TRACE?
A. The statement is not executed; it only shows what will happen if the statement is executed.
Q. When do you use EXPLAIN PLAN without TRACE?
A. When the query will take exceptionally long to run.

The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:
TRACE
It takes four hours to TRACE a query that takes four hours to run.

Set up Init.ora Parameters
Create PLAN_TABLE table
Run Query
Statement is executed PLAN_TABLE is populated
Run TKPROF
Output shows disk and memory reads in addition to EXPLAIN PLAN output
 EXPLAIN PLAN
It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run.

Create PLAN_TABLE table
Explain Query
PLAN_TABLE is populated
Query PLAN_TABLE
Output shows EXPLAIN PLAN


Q.
How do I use EXPLAIN PLAN by itself
A.
Find the script; it is usually in the ORACLE_HOME/rdbms/admin:
"utlxplan.sql"
Execute the script XPLAINPL.sql in SQL*Plus:
        @utlxplan
       
This creates the PLAN_TABLE for the user executing the script. You may create your own PLAN_TABLE, but use Oracle"s syntax or else!!!
Run EXPLAIN PLAN for the query to be optimized:
        explain plan for
        select CUSTOMER_NUMBER
        from   CUSTOMER
        where  CUSTOMER_NUMBER = 111;
        Explained.
       
EXPLAIN PLAN for the query to be optimized (using a tag for the statement):
 explain plan
 set statement_id = 'CUSTOMER' for
 select  CUSTOMER_NUMBER
 from CUSTOMER
 where CUSTOMER_NUMBER = 111;

TIP Use the SET STATEMENT_ID = "your identifier" when the PLAN_TABLE will be populated by many different developers. I rarely use the SET STATEMENT_ID statement. Instead, I EXPLAIN PLAN a query, look at the output, and then delete from the PLAN_TABLE table. I continue to do this (making changes to the query), until I see an execution plan that I think will be favorable. I then run the query to see if the performance has been improved. If multiple developers/DBAs are using the same PLAN_TABLE, then the SET STATEMENT_ID will be essential to identifying a statement.
Select the output from the PLAN_TABLE table:
 select operation,
  options,
  object_name,
  id,
  parent_id
 from plan_table
 where  statement_id = 'CUSTOMER';
 Operation   Options Object Name ID Parent
 select statement      0
 Table Access   By ROWID Customer  1
 Index      Range Scan CUST_IDX  2      1
       
TIP Use EXPLAIN PLAN instead of TRACE so that you don't have to wait for the query to run. EXPLAIN PLAN will show the path of a query without actually running the query. Use TRACE only for multi-query batch jobs to find out which of the many queries in the batch job are slow.
An Additional EXPLAIN PLAN Example for a Simple Query
Run the query with the EXPLAIN syntax embedded prior to the query:
  explain plan
  set statement_id ='query 1' for
  select  customer_number,
     name
   customer
  from    customer
  where   customer_number = '111';
 
Retrieve the output of EXPLAIN PLAN by querying the PLAN_TABLE:
To retrieve the information for viewing, a SQL statement must be executed. Two scripts provided in the Oracle documentation are displayed in this step and in step 3, along with the results of each based on the previous EXPLAIN PLAN command. Note that this example varies from the last example. The customer_number column is an indexed number field, which in the second example is suppressed (by forcing a to_char) because of a data type mismatch ('111' is in quotes). In the first example, I treated the customer_number column correctly as a number field (111 is not in quotes).
  select  operation,
   options,
   object_name,
   id,
   parent_id,
   position
  from   plan_table
  where   statement_id = 'query 1'
  order by id;
Operation   Options Object Name  ID Parent
select statement        0
Table Access   Full  Customer_Information  1
 
Retrieving a more intuitive and easy to read output of EXPLAIN PLAN:
select  lpad(' ', 2*(level-1)) || operation || ' ' || options || ' ' ||
 object_name || ' ' || decode(id, 0, 'Cost = ' || position) 'Query Plan'
from plan_table
start with id = 0
and statement_id = 'query 1'
connect by prior id = parent_id
and statement_id = 'query 1';
 
Output:
Query Plan
select statement Cost=220
    Table Access Full Customer
 
EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?
Actually, it depends on how you write the query that retrieves the information from the PLAN_TABLE table. That is probably why many people differ on which way to read the result (all of them may be correct). Next, I give an example with the order of execution based on the query that retrieves the information. In this example, the output is read top to bottom with one caveat...you must read from the innermost to the outermost. The example here shows a method that should clear up any questions.
The SQL statement should be placed after the FOR clause of the EXPLAIN PLAN:
delete from plan_table;
explain plan
set  statement_id = 'SQL1' for
select to_char(sysdate, 'MM/DD/YY HH:MM AM'),
 to_char((trunc((sysdate -4, -1), 'day') +1), 'DD-MON-YY'),
from bk, ee
where bk_shift_date >= to_char((trunc(( sysdate - 4 - 1), 'day' + 1),
                         'DD-MON-YY')
and bk_shift_date <= to_char((sysdate - 4), 'DD-MON-YY')
and bk_empno = ee_empno (+)
and substr( ee_hierarchy_code, 1, 3) in ('PNA', 'PNB', 'PNC',
                                             'PND', 'PNE', 'PNF')
order by ee_job_group,
  bk_empno,
  bk_shift_date
/
select LPad(' ', 2*(Level-1)) || Level || '.' || nvl(Position,0) ||
        ' ' || Operation || ' ' || Options || ' ' || Object_Name ||
        ' ' || Object_Type || ' ' || Decode(id, 0, Statement_id || 
        'Cost = ' || Position) || Other || ' ' ||
 Object_Node "Query Plan"
from plan_table
start with id = 0
and  statement_id = 'SQL1'
connect by prior id = parent_id
and statement_id = 'SQL1'
/
Query Plan
1.0 SELECT STATEMENT SQL1 Cost =
    2.1 SORT ORDER BY
        3.1 FILTER
            4.1 NESTED LOOPS OUTER
                5.1 TABLE ACCESS BY ROWID BK
                    6.1 INDEX RANGE SCAN I_BK_06 NON-UNIQUE
                5.2 TABLE ACCESS BY ROWID EE
                    6.1 INDEX UNIQUE SCAN I_EE_01 UNIQUE
Reading the EXPLAIN PLAN
Using the previous EXPLAIN PLAN, I will explain the steps below. A located in the left column in the following table identifies each step. order in which they were executed.
Step Action
6.1 This is the index range scan of I_BK_06. This is the first step. This index is on the bk_shift_dt column. This step performs a scan of this index to produce a list of ROWIDs that fall between the two dates.
5.1 Retrieve the rows from the BK table.
6.1 Scan of the I_EE_01 index. This index is on the ee_empno column. Using the bk_empno retrieved from the previous step, this index is scanned to retrieve the ROWIDs to produce a list of the ee_empnos that match the bk_empnos.
5.2 Retrieve the rows from the EE table.
4.1 NESTED LOOP. The two lists are joined, producing one list.
3.1 FILTER. The rest of the conditions of the WHERE clause are applied. 
2.1 SORT ORDER BY. The remaining rows are sorted according to the ORDER BY clause.
1.0 This tells what type of statement it is.

TIP Whether the EXPLAIN PLAN is read from top to bottom or from the bottom to the top is dependent entirely on the query used to select information from the PLAN_TABLE table. Both methods of reading the query may be correct, given the query selecting the information is correctly structured.
Setting AUTOTRACE On
There is also an easier method with SQL*Plus for generating an EXPLAIN PLAN and statistics about the performance of a query. The AUTOTRACE command (available in SQL*Plus 3.3 and later) generates similar information, as shown in this example:
SET AUTOTRACE ON
select count(name)
from emp7
where  name = 'branches';
Output
COUNT(NAME)
100
Query Plan
   0   SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREDATE)
   2    1     INDEX (RANGE SCAN) OF 'EMP7_I1' (NON-UNIQUE)
Statistics
 0  recursive calls
 0  db block gets
 1  consistent gets
 1  physical reads
 0  redo size
      223  bytes sent via SQL*Net to client
      274  bytes recd via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed
TIP The AUTOTRACE option provides an EXPLAIN PLAN and statistics for a query. The AUTOTRACE provides many of the TRACE and TKPROF statistics such as disk reads (physical reads) and memory reads (consistent reads ÷ db block gets).

1. Set the following init.ora parameters:

   TIMED.STATISTICS = TRUE
     MAX_DUMP_FILE_SIZE = 2000000 (Not 2M)
     USER_DUMP_DEST = /oracle8/rich_trc
     

   In Oracle7, the database must be shut down and restarted for these parameters to take effect. In Oracle8, the TIMED_STATISTICS parameter may be set via an ALTER SESSION (for an individual session) or ALTER SYSTEM (for the entire system) command. The USER_DUMP_DEST specifies the location to put the files and the MAX_DUMP_FILE_SIZE specifies the maximum file size.
2. Enable TRACE for a SQL*Plus session (this starts TRACing for an individual session):

   alter session set SQL_TRACE true;
     

3. Run the query to be TRACEd:

   select    table_name,
               owner, 
               initial_extent,
               uniqueness
     from     ind2
     where     owner || '' = 'SCOTT';
     --    (Note: An index on "OWNER" is suppressed)
     

4. Disable TRACE for the SQL*Plus session:

   alter session set SQL_TRACE false;
     

5. You can also enable TRACE for all sessions by setting the SQLTRACE parameter in the init.ora. You must shut down and restart the database for this to take effect. This is not suggested!

   SQL_TRACE = TRUE
     

   After running TRACE, your output file will look something like the following:

   5_19554.trc
     

   TIP Setting TIMED_STATISTICS=TRUE in the init.ora will begin TRACing upon the user's command. But, be careful; setting SQLTRACE=TRUE in the init.ora will cause the entire system and all queries to be TRACEd and could cause performance degradations.
6. Run TKPROF to put the TRACE file into readable format:

   tkprof 5_19554.trc rich2.prf explain=system/manager
     

   The TKPROF utility translates the TRACE file generated by the SQLTRACE facility to a readable format. You can run TKPROF against a TRACE file that you have previously created, or you can run it while the program that is creating the TRACE file is still running. Options for TKPROF are listed next.

   tkprof tracefile output_file [sort = parameters]
     [print =number] [explain=username/password@sid]
     

   Command-Line Options:
   

   TRACEfile

   The name of the TRACE file containing the statistics by SQL_TRACE.

   output_file

   The name of the file where TKPROF writes its output.

   SORT= parameters

   The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.

   PRINT=number

   The number of statements to include in the output. included, TKPROF will list all statements in the output.

   EXPLAIN=username/password@sid

   Run the EXPLAIN PLAN on the user's SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.

   New options in 7.3+:

   INSERT= filename

   This option creates a script to create a table and store the TRACE file statistics for each SQL statement TRACEd.

   RECORD= filename

   This option will produce a file of all the user's SQL statements.

   SYS= YES/NO

   This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.

   SORT= parameters

   There is a tremendous number of sorting options that are available. My favorites are FCHCPU (CPU time of fetch), FCHDSK (disk reads for fetch), FCHCU and FCHQRY (memory reads for fetch), FCH ROW (number of rows fetched), EXEDSK (disk reads during execute), EXECU and EXEQRY (memory reads during execute), EXEROW (rows processed during execute), EXECPU (execute CPU time), and PRSCNT (times parsed).

   TIP
   The TKPROF utility puts a TRACEd output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying "explain=username/password" (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query.
   


7. The output of the file rich2.prf (query with the index suppressed):

   select    table_name,
               owner,
               initial_extent,
               uniqueness
     from      ind2
     where     owner = 'SCOTT';
   
    count cpu elap disk query current   rows
   
   Parse:     1     1        2       0        0         0
   Execute:    1     0        0       0        0         2      0
   Fetch:     2    69      113     142      430         0     36
     

   Execution Plan (no index used):

   TABLE ACCESS (FULL) OF 'IND2'
     

   The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem. TIP A TRACEd query with a large number of physical reads usually indicates a missing index. The disk column indicates the physical reads (usually where an index is not used) and the query added to the current columns indicates the memory reads (usually reads where an index is being used).
   


8. Here's what happens when I rerun the query (after restarting the system) to be TRACEd, now using an index on the owner table:

   select    table_name,
               owner,
               initial_extent,
               uniqueness
     from      ind2
     where     owner = 'SCOTT';
     (The index on "OWNER" is not suppressed)
   
    count cpu elap disk query current   rows
   
   Parse:     2     0        0       0        0         0
   Execute:    2     0        0       0        0         0      0
   Fetch:     4     6        6       0      148         0     72 
     

   Execution Plan (index used):

   TABLE ACCESS (BY ROWID) OF 'IND2'
     INDEX (RANGE SCAN) OF 'IND2_1' (NON-UNIQUE)
     

   TIP A TRACEd query output with only memory reads (query-consistent reads) indicates that an index is being used.

The Sections of a TRACE Output

The SQL Statement

The Statistics Section

count

The number of times this type of call was made.

cpu

The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.

elapsed

The total elapsed time for this call.

disk

The total number of data blocks retrieved from disk to satisfy this call.

query

The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.

current

The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.

rows

The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.

Information Section

The EXPLAIN PLAN

It is also possible that there is only one line of the execution plan that is processing a large number of rows compared to the rest of the statement. This can be caused by full table scans or the use of a bad index. The following example illustrates a TRACEd query with a slightly higher complexity.

A More Complex TKPROF Output

select    Item_Item_Id, InitCap( Item_Description )
from      Item
where     Item_Classification = 1
and       Item_Item_Id Between 1000000 And 2700000
and       Item_Item_Id Not In ( Select Invitem_Item_Id
          from  Inventory_Item
          where Invitem_Location_Id = '405')

call count     cpu elapsed    disk     query   current  rows
Parse     1    0.00      0.00       0         0         0     0
Execute     1    0.00      0.00       0         0         0     0
Fetch      27   20.87     21.24       0      4408         0   399
Totals     29   20.87     21.24       0      4408         0   399

Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 106  (C12462)

Rows   Execution Plan
0      SELECT STATEMENT OPTIMIZER HINT: CHOOSE
572    FILTER
598       TABLE ACCESS (BY ROWID) OF 'ITEM'
599   INDEX (RANGE SCAN) OF 'ITEM_PK' (UNIQUE)
278790   INDEX (RANGE SCAN) OF 'INVITEM_PK' (UNIQUE)

Some of the things to look for in the TKPROF output are listed in this table:

Problems	Solutions
High numbers for the parsing	The SHARED_POOL_SIZE may need to be increased.
The disk reads are very high	Indexes are not used or may not exist.
The "query" and/or "current" (memory reads) are very high	Indexes may be on columns with high cardinality (columns where an individual value generally makes up a large percentage of the table). Removing or suppressing the index may increase performance.
The parse elapse time is high	There may be a problem with the number of open cursors.
The number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows	This could be a sign of an index with a poor distribution distinct keys (unique values for a column). Or this could also be a sign of a poorly written statement.
If the number of misses in the library cache during parse is greater than 1	This is an indication that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora.

The EXPLAIN PLAN command allows a developer to view the query execution plan that the Oracle optimizer will use to execute a SQL statement. This command is very helpful in improving performance of SQL statements, since it does not actually execute the SQL statement--it only outlines the plan to use and inserts this execution plan in an Oracle table. Prior to using the EXPLAIN PLAN command, a file called UTLXPLAN.sql (located in the same directory as CATALOG.sql) must be executed under the Oracle account that will be executing the EXPLAIN PLAN command. The script creates a table called PLAN_TABLE that is used by the EXPLAIN PLAN command to insert the query execution plan in the form of records. This table can then be queried and viewed to determine if there needs to be any modifications to the SQL statement to force a different execution plan. An EXPLAIN PLAN example is shown next (executed in SQL*Plus).

Using EXPLAIN PLAN Alone

Q.	Why use EXPLAIN PLAN without TRACE?
A.	The statement is not executed; it only shows what will happen if the statement is executed.
Q.	When do you use EXPLAIN PLAN without TRACE?
A.	When the query will take exceptionally long to run.

The following diagram demonstrates the procedures for running TRACE versus EXPLAIN PLAN:

TRACE It takes four hours to TRACE a query that takes four hours to run. Set up Init.ora Parameters Create PLAN_TABLE table Run Query Statement is executed PLAN_TABLE is populated Run TKPROF Output shows disk and memory reads in addition to EXPLAIN PLAN output

EXPLAIN PLAN It takes less than a minute to EXPLAIN PLAN a query that takes four hours to run. Create PLAN_TABLE table Explain Query PLAN_TABLE is populated Query PLAN_TABLE Output shows EXPLAIN PLAN

Q.

How do I use EXPLAIN PLAN by itself

A.

1. Find the script; it is usually in the ORACLE_HOME/rdbms/admin:

   "utlxplan.sql"

2. Execute the script XPLAINPL.sql in SQL*Plus:

   @utlxplan
           

   This creates the PLAN_TABLE for the user executing the script. You may create your own PLAN_TABLE, but use Oracle"s syntax or else!!!
3. Run EXPLAIN PLAN for the query to be optimized:

   explain plan for 
           select CUSTOMER_NUMBER 
           from   CUSTOMER
           where  CUSTOMER_NUMBER = 111;
   
           Explained.
           

4. EXPLAIN PLAN for the query to be optimized (using a tag for the statement):

   explain plan 
    set statement_id = 'CUSTOMER' for 
    select  CUSTOMER_NUMBER
    from CUSTOMER
    where CUSTOMER_NUMBER = 111;
    

   TIP Use the SET STATEMENT_ID = "your identifier" when the PLAN_TABLE will be populated by many different developers. I rarely use the SET STATEMENT_ID statement. Instead, I EXPLAIN PLAN a query, look at the output, and then delete from the PLAN_TABLE table. I continue to do this (making changes to the query), until I see an execution plan that I think will be favorable. I then run the query to see if the performance has been improved. If multiple developers/DBAs are using the same PLAN_TABLE, then the SET STATEMENT_ID will be essential to identifying a statement.
5. Select the output from the PLAN_TABLE table:

   select operation,
     options, 
     object_name, 
     id, 
     parent_id
    from plan_table
    where  statement_id = 'CUSTOMER';
   
    Operation   Options Object Name ID Parent
    select statement      0
    Table Access   By ROWID Customer  1
    Index      Range Scan CUST_IDX  2      1
           

   TIP Use EXPLAIN PLAN instead of TRACE so that you don't have to wait for the query to run. EXPLAIN PLAN will show the path of a query without actually running the query. Use TRACE only for multi-query batch jobs to find out which of the many queries in the batch job are slow.

An Additional EXPLAIN PLAN Example for a Simple Query

1. Run the query with the EXPLAIN syntax embedded prior to the query:

   explain plan
     set statement_id ='query 1' for
     select  customer_number,
        name
      customer
     from    customer
     where   customer_number = '111';
     

2. Retrieve the output of EXPLAIN PLAN by querying the PLAN_TABLE: To retrieve the information for viewing, a SQL statement must be executed. Two scripts provided in the Oracle documentation are displayed in this step and in step 3, along with the results of each based on the previous EXPLAIN PLAN command. Note that this example varies from the last example. The customer_number column is an indexed number field, which in the second example is suppressed (by forcing a to_char) because of a data type mismatch ('111' is in quotes). In the first example, I treated the customer_number column correctly as a number field (111 is not in quotes).
   

   select  operation, 
      options,
      object_name, 
      id, 
      parent_id, 
      position
     from   plan_table
     where   statement_id = 'query 1'
     order by id; 
   
   Operation   Options Object Name  ID Parent
   select statement        0
   Table Access   Full  Customer_Information  1
     

3. Retrieving a more intuitive and easy to read output of EXPLAIN PLAN:

   select  lpad(' ', 2*(level-1)) || operation || ' ' || options || ' ' ||
    object_name || ' ' || decode(id, 0, 'Cost = ' || position) 'Query Plan' 
   from plan_table
   start with id = 0
   and statement_id = 'query 1'
   connect by prior id = parent_id
   and statement_id = 'query 1';
     

   Output:

   Query Plan
   
   select statement Cost=220
       Table Access Full Customer
     

EXPLAIN PLAN--Read It Top to Bottom or Bottom to Top?

The SQL statement should be placed after the FOR clause of the EXPLAIN PLAN:

delete from plan_table;
explain plan
set  statement_id = 'SQL1' for
select to_char(sysdate, 'MM/DD/YY HH:MM AM'), 
 to_char((trunc((sysdate -4, -1), 'day') +1), 'DD-MON-YY'),
from bk, ee
where bk_shift_date >= to_char((trunc(( sysdate - 4 - 1), 'day' + 1),
                         'DD-MON-YY')
and bk_shift_date <= to_char((sysdate - 4), 'DD-MON-YY')
and bk_empno = ee_empno (+)
and substr( ee_hierarchy_code, 1, 3) in ('PNA', 'PNB', 'PNC',
                                             'PND', 'PNE', 'PNF')
order by ee_job_group, 
  bk_empno,
  bk_shift_date
/
select LPad(' ', 2*(Level-1)) || Level || '.' || nvl(Position,0) ||
        ' ' || Operation || ' ' || Options || ' ' || Object_Name ||
        ' ' || Object_Type || ' ' || Decode(id, 0, Statement_id ||  
        'Cost = ' || Position) || Other || ' ' || 
 Object_Node "Query Plan"
from plan_table
start with id = 0
and  statement_id = 'SQL1'
connect by prior id = parent_id
and statement_id = 'SQL1'
/

Query Plan
1.0 SELECT STATEMENT SQL1 Cost =
    2.1 SORT ORDER BY
        3.1 FILTER
            4.1 NESTED LOOPS OUTER
                5.1 TABLE ACCESS BY ROWID BK
                    6.1 INDEX RANGE SCAN I_BK_06 NON-UNIQUE
                5.2 TABLE ACCESS BY ROWID EE
                    6.1 INDEX UNIQUE SCAN I_EE_01 UNIQUE

Reading the EXPLAIN PLAN

Step	Action
6.1	This is the index range scan of I_BK_06. This is the first step. This index is on the bk_shift_dt column. This step performs a scan of this index to produce a list of ROWIDs that fall between the two dates.
5.1	Retrieve the rows from the BK table.
6.1	Scan of the I_EE_01 index. This index is on the ee_empno column. Using the bk_empno retrieved from the previous step, this index is scanned to retrieve the ROWIDs to produce a list of the ee_empnos that match the bk_empnos.
5.2	Retrieve the rows from the EE table.
4.1	NESTED LOOP. The two lists are joined, producing one list.
3.1	FILTER. The rest of the conditions of the WHERE clause are applied.
2.1	SORT ORDER BY. The remaining rows are sorted according to the ORDER BY clause.
1.0	This tells what type of statement it is.

TIP Whether the EXPLAIN PLAN is read from top to bottom or from the bottom to the top is dependent entirely on the query used to select information from the PLAN_TABLE table. Both methods of reading the query may be correct, given the query selecting the information is correctly structured. There is also an easier method with SQL*Plus for generating an EXPLAIN PLAN and statistics about the performance of a query. The AUTOTRACE command (available in SQL*Plus 3.3 and later) generates similar information, as shown in this example:

Setting AUTOTRACE On

SET AUTOTRACE ON
select count(name)
from emp7
where  name = 'branches';

Output

COUNT(NAME)
100

Query Plan
   0   SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREDATE)
   2    1     INDEX (RANGE SCAN) OF 'EMP7_I1' (NON-UNIQUE)

Statistics
 0  recursive calls
 0  db block gets
 1  consistent gets
 1  physical reads
 0  redo size
      223  bytes sent via SQL*Net to client
      274  bytes recd via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 1  sorts (memory)
 0  sorts (disk)
 1  rows processed

TIP The AUTOTRACE option provides an EXPLAIN PLAN and statistics for a query. The AUTOTRACE provides many of the TRACE and TKPROF statistics such as disk reads (physical reads) and memory reads (consistent reads ÷ db block gets). Using the previous EXPLAIN PLAN, I will explain the steps below. A located in the left column in the following table identifies each step. order in which they were executed. Actually, it depends on how you write the query that retrieves the information from the PLAN_TABLE table. That is probably why many people differ on which way to read the result (all of them may be correct). Next, I give an example with the order of execution based on the query that retrieves the information. In this example, the output is read top to bottom with one caveat...you must read from the innermost to the outermost. The example here shows a method that should clear up any questions. This section contains information about the number of misses in the library cache from parse and execute calls. If the number of misses is high, there may be a problem with the size of the Shared Pool. You should check the hit ratio and the reload rate of the library cache. There is also information about the current optimizer mode setting. This section shows the username of the last user to parse this statement. This is the section of the TKPROF I find to be the most useful. The first column of this section of the TRPROF output listing below, is the number of rows processed by each line of the execution plan. Here, you will be able to see how bad a statement is. If the total number of rows in the Fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN, you may want to review the statement. The TRACE utility has multiple sections including the SQL statements, statistics, information, and the EXPLAIN PLAN. Each of these different topics are discussed in the following sections. The first section of a TKPROF statement is the SQL statement. This statement will be the exact same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output. This can be helpful when you are reviewing the output from multiple sessions. If you find a statement causing problems, you would be able to search for the exact statement. Remember, some of the statements from Oracle forms are generated dynamically. This section contains all the statistics for this SQL statement and all the recursive SQL statements generated to satisfy this statement. In this section there are eight columns, the first being the type of call to the database. There are three types of calls, parse, execute, and fetch. Each type of call will generate a separate line of statistics. The other seven columns are the statistics for each type of call.