DBA Data[Home] [Help]

APPS.PON_UDA_IMPORT_PKG SQL Statements

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

Line: 96

         l_program_update_date      DATE;
Line: 97

         l_last_update_date         DATE;
Line: 99

         l_last_update_login        NUMBER;
Line: 101

         l_last_updated_by          NUMBER;
Line: 103

         l_last_updated_by_name     VARCHAR2(100);
Line: 133

         SELECT     INTERFACE_REFERENCE_ID
                   ,TRANSACTION_ID
                   ,ROW_IDENTIFIER
                   ,ATTR_GROUP_ID
                   ,ATTR_GROUP_APP_ID
                   ,ATTR_GROUP_NAME
                   ,DATA_LEVEL_1
                   ,DATA_LEVEL_2
                   ,DATA_LEVEL_3
                   ,TRANSACTION_TYPE
                   ,ATTR_NAME
                   ,ATTR_VALUE_STR
                   ,ATTR_VALUE_NUM
                   ,ATTR_VALUE_DATE
                   ,ATTR_DISP_VALUE
                   ,ATTR_UNIT_OF_MEASURE
                   ,USER_ROW_IDENTIFIER
                   ,UDA_TEMPLATE_ID
                   ,PROCESS_STATUS
                   ,PROCESS_PHASE
                   ,REQUEST_ID
                   ,PROGRAM_ID
                   ,PROGRAM_APPLICATION_ID
                   ,PROGRAM_UPDATE_DATE
                   ,LAST_UPDATE_DATE
                   ,CREATION_DATE
                   ,LAST_UPDATE_LOGIN
                   ,CREATED_BY
                   ,LAST_UPDATED_BY
                   ,CREATED_BY_NAME
                   ,LAST_UPDATED_BY_NAME
                   ,INTERFACE_SOURCE_CODE
                   ,INTERFACE_SOURCE_LINE_ID
         FROM      PO_UDA_INTERFACE
         WHERE     PROCESS_STATUS = PON_UDA_IMPORT_PKG.IN_PROGRESS
         AND       NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
         AND       NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
         AND       NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
         AND       NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
         AND       NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
         AND       ATTR_GROUP_TYPE = p_attr_grp_type
         ORDER BY  ATTR_GROUP_ID;
Line: 201

             SELECT    INTERFACE_REFERENCE_ID
             INTO      g_interface_reference_id
             FROM      PO_UDA_INTERFACE
             WHERE     NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
             AND       NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
             AND       NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
             AND       NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
             AND       NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
             AND       ROWNUM < 2;
Line: 267

                  ,l_program_update_date
                  ,l_last_update_date
                  ,l_creation_date
                  ,l_last_update_login
                  ,l_created_by
                  ,l_last_updated_by
                  ,l_created_by_name
                  ,l_last_updated_by_name
                  ,l_interface_source_code
                  ,l_interface_source_line_id;
Line: 304

            PON_UDA_IMPORT_PKG.DEBUG('l_program_update_date : ' || l_program_update_date, d_progress);
Line: 305

            PON_UDA_IMPORT_PKG.DEBUG('l_last_update_date : ' || l_last_update_date, d_progress);
Line: 307

            PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by : ' || l_last_updated_by, d_progress);
Line: 309

            PON_UDA_IMPORT_PKG.DEBUG('l_last_updated_by_name : ' || l_last_updated_by_name, d_progress);
Line: 354

            SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
            FROM   po_uda_ag_template_usages
            WHERE  TEMPLATE_ID=l_uda_template_id AND
                   ATTRIBUTE_GROUP_ID = l_attr_group_id;
Line: 360

              SELECT  distinct agtl.DESCRIPTIVE_FLEX_CONTEXT_NAME, attr.DATABASE_COLUMN
              INTO    l_desc_flex_cntxt_name, l_database_col
              FROM    FND_DESCR_FLEX_CONTEXTS_tl agtl,
                      FND_DESCR_FLEX_CONTEXTS ag,
                      EGO_ATTRS_V attr,
                      po_uda_ag_templates t,
                      po_uda_ag_template_usages u,
                      ego_fnd_dsc_flx_ctx_ext egoag
              WHERE attr.ATTR_GROUP_TYPE = ag.DESCRIPTIVE_FLEXFIELD_NAME
                    AND attr.ATTR_GROUP_NAME = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
                    AND ag.DESCRIPTIVE_FLEXFIELD_NAME = agtl.DESCRIPTIVE_FLEXFIELD_NAME
                    AND ag.DESCRIPTIVE_FLEX_CONTEXT_CODE = agtl.DESCRIPTIVE_FLEX_CONTEXT_CODE
                    AND agtl.LANGUAGE = UserEnv('LANG')
                    AND t.template_id = u.template_id
                    AND egoag.attr_group_id = u.attribute_group_id
                    AND egoag.DESCRIPTIVE_FLEXFIELD_NAME = ag.DESCRIPTIVE_FLEXFIELD_NAME
                    AND egoag.DESCRIPTIVE_FLEX_CONTEXT_CODE = ag.DESCRIPTIVE_FLEX_CONTEXT_CODE
                    AND t.template_id = l_uda_template_id
                    AND u.ATTRIBUTE_CATEGORY = l_attr_category
                    AND egoag.attr_group_id = l_attr_group_id
                    AND attr.ATTR_NAME = l_attr_name;
Line: 382

              SELECT REGEXP_SUBSTR(l_database_col,'N[^N]+R') INTO l_compare_string FROM dual;
Line: 503

        SELECT    END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
        FROM      FND_DESCR_FLEX_COLUMN_USAGES
        WHERE     DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
        AND       DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
Line: 535

                    SELECT ATTR_GROUP_ID INTO l_attr_group_id
                    FROM   ego_fnd_dsc_flx_ctx_ext
                    WHERE  DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type AND
                            DESCRIPTIVE_FLEX_CONTEXT_CODE = l_attr_group_name;
Line: 540

                    SELECT TEMPLATE_ID INTO l_uda_template_id
                    FROM   po_uda_ag_templates
                    WHERE  FUNCTIONAL_AREA='SOURCING' AND
                            ENTITY_CODE=l_attr_group_type;
Line: 545

                    SELECT ATTRIBUTE_CATEGORY INTO l_attr_category
                    FROM   po_uda_ag_template_usages
                    WHERE  TEMPLATE_ID=l_uda_template_id AND
                           ATTRIBUTE_GROUP_ID = l_attr_group_id;
Line: 567

                            SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
                            INTO   l_user_row_identifier
                            FROM   DUAL;
Line: 610

                                  SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
Line: 645

                              SELECT REGEXP_SUBSTR(l_attr_col_name,'N[^N]+R') INTO l_compare_string FROM dual;
Line: 764

        SELECT    END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
        FROM      FND_DESCR_FLEX_COLUMN_USAGES
        WHERE     DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
        AND       DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
Line: 773

        SELECT  DISTINCT LOOKUP_CODE , TAG
        FROM    FND_LOOKUP_VALUES
        WHERE   LOOKUP_TYPE = p_address_lookup_type
                ORDER BY TAG;
Line: 834

                                    SELECT  DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
                                    INTO    l_attr_group_type, l_attr_group_name
                                    FROM    EGO_FND_DSC_FLX_CTX_EXT
                                    WHERE   ATTR_GROUP_ID = l_attr_group_id;
Line: 852

                                l_sql_query := 'SELECT 1 FROM ' || l_base_table;
Line: 912

                                            SELECT  DATA_LEVEL_NAME
                                            INTO    l_data_level
                                            FROM    EGO_DATA_LEVEL_B
                                            WHERE   APPLICATION_ID = 201
                                            AND     ATTR_GROUP_TYPE = l_attr_group_type
                                            AND     DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
                                            AND     ATTR_GROUP_TYPE NOT LIKE '%DUMMY%';
Line: 930

                                        SELECT  Count(*)
                                        INTO    l_xd_present
                                        FROM    EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
                                        WHERE   classification_code = p_template_id
                                        AND     attr_group_id = l_attr_group_id
                                        AND     execution_method = 'XD'
                                        AND     EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id;
Line: 950

                                                    SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
                                                    INTO   l_row_identifier
                                                    FROM   DUAL;
Line: 993

                                                        SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
                                                        INTO   l_user_row_identifier
                                                        FROM   DUAL;
Line: 1042

                                                SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
                                                INTO   l_row_identifier
                                                FROM   DUAL;
Line: 1085

                                                    SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
                                                    INTO   l_user_row_identifier
                                                    FROM   DUAL;
Line: 1261

            SELECT  EGO_ACTION_DISPLAYS_B.action_id FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
            WHERE   classification_code = p_template_id
            AND     attr_group_id = p_attribute_group_id
            AND     execution_method = p_event
            AND     EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id
            ORDER BY SEQUENCE;
Line: 1312

                        SELECT  DATA_LEVEL_NAME, DATA_LEVEL_ID
                        INTO    l_data_level_name, l_data_level_id
                        FROM    EGO_DATA_LEVEL_B
                        WHERE   ATTR_GROUP_TYPE = l_attr_row_obj.ATTR_GROUP_TYPE
                        AND     DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
Line: 1495

                                       x_attr_name_value_pairs.DELETE(l_attr_id_index);
Line: 1503

                          x_attributes_row_table.DELETE(l_attr_group_id_index);
Line: 1618

        PON_UDA_IMPORT_PKG.DEBUG('Before PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to IN_PROGRESS from PENDING', d_progress);
Line: 1619

        PON_UDA_IMPORT_PKG.UPDATE_STATUS
        (
             p_process_status       => PON_UDA_IMPORT_PKG.IN_PROGRESS
            ,p_pre_process_status   => PON_UDA_IMPORT_PKG.PENDING
            ,p_process_phase        => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
            ,p_pk1_value            => p_pk1_value
            ,p_pk2_value            => p_pk2_value
            ,p_pk3_value            => p_pk3_value
            ,p_pk4_value            => p_pk4_value
            ,p_pk5_value            => p_pk5_value
        );
Line: 1630

        PON_UDA_IMPORT_PKG.DEBUG('After PON_UDA_IMPORT_PKG.UPDATE_STATUS to change to PENDING from IN_PROGRESS', d_progress);
Line: 1690

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1735

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1758

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1796

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.PRE_DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1840

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1863

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.EGO_VALIDATE
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1915

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.DEFAULTING
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1939

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 1987

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.EGO_VALIDATE
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2038

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.LOAD
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2062

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.LOAD
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2113

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.CUSTOM_VALIDATE
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2164

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.UDA_ERROR
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.LOAD
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2187

            PON_UDA_IMPORT_PKG.UPDATE_STATUS
            (
                 p_process_status       => PON_UDA_IMPORT_PKG.COMPLETED
                ,p_pre_process_status   => PON_UDA_IMPORT_PKG.IN_PROGRESS
                ,p_process_phase        => PON_UDA_IMPORT_PKG.LOAD
                ,p_pk1_value            => p_pk1_value
                ,p_pk2_value            => p_pk2_value
                ,p_pk3_value            => p_pk3_value
                ,p_pk4_value            => p_pk4_value
                ,p_pk5_value            => p_pk5_value
            );
Line: 2206

                UPDATE PO_UDA_INTERFACE
                SET    PK1_VALUE = NULL, PK2_VALUE=NULL, PROCESS_STATUS = 1, PROCESS_PHASE = NULL
                WHERE   PK1_VALUE = p_pk1_value;
Line: 2228

            PO_INTERFACE_ERRORS_SV1.insert_po_interface_errors_msg
            (
                 X_interface_type       => g_attr_grp_type,
                 X_Interface_Header_ID  => g_interface_reference_id,
                 X_Interface_Line_Id    => NULL,
                 X_Interface_Dist_Id    => NULL,
                 X_error_message_text   => p_msg,
                 X_error_message_name   => p_msg_code,
                 X_column_name          => NULL,
                 X_table_name           => NULL,
                 X_batch_id             => p_attr_grp_id
            );
Line: 2246

    PROCEDURE UPDATE_STATUS
    (
         p_process_status       IN  NUMBER
        ,p_pre_process_status   IN  NUMBER
        ,p_process_phase        IN  VARCHAR2
        ,p_pk1_value            IN  NUMBER
        ,p_pk2_value            IN  NUMBER
        ,p_pk3_value            IN  NUMBER
        ,p_pk4_value            IN  NUMBER
        ,p_pk5_value            IN  NUMBER
    )
    IS
    BEGIN
        IF  p_process_status IN (PON_UDA_IMPORT_PKG.PENDING, PON_UDA_IMPORT_PKG.IN_PROGRESS, PON_UDA_IMPORT_PKG.COMPLETED) THEN
            UPDATE  PO_UDA_INTERFACE
            SET     PROCESS_STATUS = p_process_status
                    ,PROCESS_PHASE = p_process_phase
            WHERE     NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
            AND       NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
            AND       NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
            AND       NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
            AND       NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
            AND       PROCESS_STATUS = p_pre_process_status;
Line: 2293

            select o.pk1_column_name,
                   o.pk2_column_name,
                   o.pk3_column_name,
                   o.pk4_column_name,
                   o.pk5_column_name
            into   l_pk1_column_name,
                   l_pk2_column_name,
                   l_pk3_column_name,
                   l_pk4_column_name,
                   l_pk5_column_name
            from   fnd_objects_vl o
            where  o.obj_name = p_obj_name;