APPS.CN_SCA_WF_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 24

PROCEDURE BATCH_POST_DIST_UPDATE (
    p_start_header_id  IN  number,
    p_end_header_id    IN  number,
    p_user_id          IN  number,
    p_login_id         IN  number)
IS
BEGIN

    UPDATE cn_sca_lines_output lines
    SET allocation_percentage =
            (SELECT ROUND(lines.allocation_percentage +
                          (100 - SUM(g_lines.allocation_percentage)),4)
             FROM cn_sca_headers_interface g_headers,
                  cn_sca_lines_output g_lines
             WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
             AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
             AND   g_lines.revenue_type = G_REVENUE
             AND   g_headers.process_status = G_REV_NOT_100
             GROUP BY g_headers.sca_headers_interface_id),
        last_updated_by   = p_user_id,
        last_update_date  = SYSDATE,
        last_update_login = p_login_id
    WHERE lines.revenue_type = G_REVENUE
    AND lines.sca_headers_interface_id
            BETWEEN p_start_header_id AND p_end_header_id
    AND EXISTS (SELECT 1
                FROM cn_sca_headers_interface headers
                WHERE headers.process_status = G_REV_NOT_100
                AND   headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id)
    AND lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
                                     FROM cn_sca_lines_output g_lines
                                     WHERE lines.sca_headers_interface_id =
                                        g_lines.sca_headers_interface_id
                                     AND g_lines.revenue_type = G_REVENUE);

Line: 62

END BATCH_POST_DIST_UPDATE;

Line: 80

PROCEDURE ONLINE_POST_DIST_UPDATE (
    p_sca_batch_id     IN  number)
IS
BEGIN

    UPDATE cn_sca_lines_output_gtt lines
    SET allocation_percentage =
            (SELECT ROUND(lines.allocation_percentage +
                          (100 - SUM(g_lines.allocation_percentage)),4)
             FROM cn_sca_headers_interface_gtt g_headers,
                  cn_sca_lines_output_gtt g_lines
             WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
             AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
             AND   g_lines.revenue_type = G_REVENUE
             AND   g_headers.process_status = G_REV_NOT_100
             GROUP BY g_headers.sca_headers_interface_id)
    WHERE lines.revenue_type = G_REVENUE
    AND   lines.sca_batch_id = p_sca_batch_id
    AND   EXISTS (SELECT 1
                  FROM cn_sca_headers_interface_gtt headers
                  WHERE headers.process_status = G_REV_NOT_100
                  AND   headers.sca_headers_interface_id =
                            lines.sca_headers_interface_id)
    AND   lines.sca_lines_output_id = (SELECT MIN(sca_lines_output_id)
                                       FROM cn_sca_lines_output_gtt g_lines
                                       WHERE lines.sca_headers_interface_id =
                                        g_lines.sca_headers_interface_id
                                       AND g_lines.revenue_type = G_REVENUE);

Line: 111

END ONLINE_POST_DIST_UPDATE;

Line: 144

    UPDATE cn_sca_lines_output_gtt lines
    SET allocation_percentage =
            (SELECT ROUND(lines.allocation_percentage +
                          (100 - SUM(g_lines.allocation_percentage))
                          / COUNT(*),4)
             FROM cn_sca_headers_interface_gtt g_headers,
                  cn_sca_lines_output_gtt g_lines
             WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
             AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
             AND   g_lines.revenue_type = G_REVENUE
             AND   g_headers.process_status = G_REV_NOT_100
             GROUP BY g_headers.sca_headers_interface_id)
    WHERE lines.revenue_type = G_REVENUE
    AND   lines.sca_batch_id = p_sca_batch_id
    AND   EXISTS (SELECT 1
                  FROM cn_sca_headers_interface_gtt headers
                  WHERE headers.process_status = G_REV_NOT_100
                  AND   headers.sca_headers_interface_id =
                            lines.sca_headers_interface_id);

Line: 167

    ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);

Line: 170

    UPDATE cn_sca_headers_interface_gtt headers
    SET process_status    = G_ALLOCATED
    WHERE headers.process_status = G_REV_NOT_100
    AND   headers.sca_batch_id   = p_sca_batch_id;

Line: 219

    UPDATE cn_sca_lines_output lines
    SET allocation_percentage =
            (SELECT ROUND(lines.allocation_percentage +
                          (100 - SUM(g_lines.allocation_percentage))
                          / COUNT(*),4)
             FROM cn_sca_headers_interface g_headers,
                  cn_sca_lines_output g_lines
             WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
             AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
             AND   g_lines.revenue_type = G_REVENUE
             AND   g_headers.process_status = G_REV_NOT_100
             GROUP BY g_headers.sca_headers_interface_id),
        last_updated_by   = l_user_id,
        last_update_date  = SYSDATE,
        last_update_login = l_login_id
    WHERE lines.revenue_type = G_REVENUE
    AND   lines.sca_headers_interface_id
            BETWEEN p_start_header_id AND p_end_header_id
    AND   EXISTS (SELECT 1
                  FROM cn_sca_headers_interface headers
                  WHERE headers.process_status = G_REV_NOT_100
                  AND   headers.sca_headers_interface_id =
                            lines.sca_headers_interface_id);

Line: 246

    BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
                           p_end_header_id   => p_end_header_id,
                           p_user_id         => l_user_id,
                           p_login_id        => l_login_id);

Line: 252

    UPDATE cn_sca_headers_interface headers
    SET process_status    = G_ALLOCATED,
        last_updated_by   = l_user_id,
        last_update_date  = SYSDATE,
        last_update_login = l_login_id
    WHERE headers.process_status = G_REV_NOT_100
    AND   headers.sca_headers_interface_id
    BETWEEN p_start_header_id AND p_end_header_id;

Line: 300

    UPDATE cn_sca_lines_output_gtt lines
    SET allocation_percentage =
              (SELECT ROUND(
                lines.allocation_percentage + lines.allocation_percentage *
                    (100 - SUM(g_lines.allocation_percentage)) /
                    SUM(g_lines.allocation_percentage),4)
               FROM cn_sca_headers_interface_gtt g_headers,
                    cn_sca_lines_output_gtt g_lines
               WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
               AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
               AND   g_lines.revenue_type = G_REVENUE
               AND   g_headers.process_status = G_REV_NOT_100
               GROUP BY g_headers.sca_headers_interface_id)
    WHERE lines.revenue_type = G_REVENUE
    AND   lines.sca_batch_id = p_sca_batch_id
    AND   EXISTS (SELECT 1
                  FROM cn_sca_headers_interface_gtt headers
                  WHERE headers.process_status = G_REV_NOT_100
                  AND   headers.sca_headers_interface_id =
                            lines.sca_headers_interface_id);

Line: 324

    ONLINE_POST_DIST_UPDATE(p_sca_batch_id => p_sca_batch_id);

Line: 327

    UPDATE cn_sca_headers_interface_gtt headers
    SET process_status    = G_ALLOCATED
    WHERE headers.process_status = G_REV_NOT_100
    AND   headers.sca_batch_id   = p_sca_batch_id;

Line: 375

    UPDATE cn_sca_lines_output lines
    SET allocation_percentage =
              (SELECT ROUND(
                lines.allocation_percentage + lines.allocation_percentage *
                    (100 - SUM(g_lines.allocation_percentage)) /
                    SUM(g_lines.allocation_percentage),4)
               FROM cn_sca_headers_interface g_headers,
                    cn_sca_lines_output g_lines
               WHERE g_headers.sca_headers_interface_id =
                        lines.sca_headers_interface_id
               AND   g_headers.sca_headers_interface_id =
                        g_lines.sca_headers_interface_id
               AND   g_lines.revenue_type = G_REVENUE
               AND   g_headers.process_status = G_REV_NOT_100
               GROUP BY g_headers.sca_headers_interface_id),
        last_updated_by   = l_user_id,
        last_update_date  = SYSDATE,
        last_update_login = l_login_id
    WHERE lines.revenue_type = G_REVENUE
    AND   lines.sca_headers_interface_id
            BETWEEN p_start_header_id AND p_end_header_id
    AND   EXISTS (SELECT 1
                  FROM cn_sca_headers_interface headers
                  WHERE headers.process_status = G_REV_NOT_100
                  AND   headers.sca_headers_interface_id =
                            lines.sca_headers_interface_id);

Line: 403

    BATCH_POST_DIST_UPDATE(p_start_header_id => p_start_header_id,
                           p_end_header_id   => p_end_header_id,
                           p_user_id         => l_user_id,
                           p_login_id        => l_login_id);

Line: 409

    UPDATE cn_sca_headers_interface headers
    SET process_status    = G_ALLOCATED,
        last_updated_by   = l_user_id,
        last_update_date  = SYSDATE,
        last_update_login = l_login_id
    WHERE headers.process_status = G_REV_NOT_100
    AND   headers.sca_headers_interface_id
            BETWEEN p_start_header_id AND p_end_header_id;

Line: 585

        SELECT shig.transaction_source
        INTO   l_trxSrc
        FROM   cn_sca_headers_interface_gtt shig
        WHERE  shig.sca_batch_id = p_sca_batch_id
        AND    ROWNUM = 1;

Line: 607

        SELECT fa.application_id
        INTO l_appId
        FROM   fnd_application fa
        WHERE  fa.application_short_name = l_trxSrc;

Line: 788

        SELECT fa.application_id
        INTO   l_appId
        FROM   fnd_application fa
        WHERE  fa.application_short_name = UPPER(TRIM(p_trx_source));

Line: 1017

PROCEDURE SELECTOR (
    itemType    IN  varchar2,
    itemKey     IN  varchar2,
    actId       IN  number,
    funcMode    IN  varchar2,
    resultOut   OUT NOCOPY varchar2)
IS
BEGIN

    -- RUN mode: Normal process execution
    IF (funcMode = 'RUN') THEN
        IF UPPER(itemType) = 'CNSCARPR' THEN
            resultOut := 'CN_SCA_REV_DIST_PR';

Line: 1048

                        'SELECTOR',
                        itemType,
                        itemKey,
                        TO_CHAR(actId),
                        funcMode);

Line: 1055

END SELECTOR;

Line: 1082

PROCEDURE REV_DIST_SELECT (
    itemType    IN  varchar2,
    itemKey     IN  varchar2,
    actId       IN  number,
    funcMode    IN  varchar2,
    resultOut   OUT NOCOPY varchar2)
IS
    -- Local Variables
    l_revFunc varchar2(30);

Line: 1124

                        'REV_DIST_SELECT',
                        itemType,
                        itemKey,
                        TO_CHAR(actId),
                        funcMode);

Line: 1131

END REV_DIST_SELECT;

Line: 1404

PROCEDURE TRX_LOAD_SELECT (
    itemType    IN  varchar2,
    itemKey     IN  varchar2,
    actId       IN  number,
    funcMode    IN  varchar2,
    resultOut   OUT NOCOPY varchar2)
IS
    -- Local Variables
    l_trxSrc varchar2(30);

Line: 1451

                        'TRX_LOAD_SELECT',
                        itemType,
                        itemKey,
                        TO_CHAR(actId),
                        funcMode);

Line: 1458

END TRX_LOAD_SELECT;