APPS.IGI_CIS_UPGRADE_PKG SQL Statements

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

Line: 7

        CURSOR C_UPDATE IS
            SELECT
                DISTINCT AATR.VENDOR_ID
            FROM
                AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
            WHERE
                --TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) bug 5620412
                --AND TRUNC(NVL(END_DATE, SYSDATE)) bug 5620412
                --AND bug 5620412
		AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
        AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 19

        C_REC_INFO C_UPDATE%ROWTYPE;

Line: 30

        For C_REC_INFO in C_UPDATE LOOP

            TEMP_NI_NUMBER := NULL;

Line: 39

            SELECT
                VENDOR_NAME,VENDOR_TYPE_LOOKUP_CODE
                INTO TEMP_VENDOR_NAME,TEMP_VENDOR_TYPE
            FROM
                AP_SUPPLIERS
            WHERE
                VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 54

            UPDATE AP_SUPPLIERS
                 SET CIS_ENABLED_FLAG = 'Y'
            WHERE
                VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 64

                SELECT
                    COUNT(DISTINCT(ICCNN.NI_NUMBER)) INTO TEMP_COUNT
                FROM
                    AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                WHERE
                    AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                    AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
                    -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
                    -- AND TRUNC(NVL(END_DATE, SYSDATE))
                    -- Bug 5620412
                    AND ICCNN.NI_NUMBER IS NOT NULL
                    AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 87

                    SELECT
                        COUNT(DISTINCT(ICCNN.NI_NUMBER)) INTO TEMP_COUNT2
                    FROM
                        AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                    WHERE
                        AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                        AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
                        AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
                        AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
                        AND ICCNN.NI_NUMBER IS NOT NULL
                        AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 110

                    SELECT
                        DISTINCT (ICCNN.NI_NUMBER) INTO TEMP_NI_NUMBER
                    FROM
                        AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                    WHERE
                        AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                        AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
                        AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
                        AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
                        AND ICCNN.NI_NUMBER IS NOT NULL
                        AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 123

                        UPDATE AP_SUPPLIERS
                            SET NATIONAL_INSURANCE_NUMBER = TEMP_NI_NUMBER
                        WHERE
                            VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 136

                SELECT
                    DISTINCT (ICCNN.NI_NUMBER) INTO TEMP_NI_NUMBER
                FROM
                    AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                WHERE
                    AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                    AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
                    -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
                    -- AND TRUNC(NVL(END_DATE, SYSDATE))
                    -- Bug 5620412
                    AND ICCNN.NI_NUMBER IS NOT NULL
                    AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 150

                    UPDATE AP_SUPPLIERS
                        SET NATIONAL_INSURANCE_NUMBER = TEMP_NI_NUMBER
                    WHERE
                        VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 169

                SELECT
                    COUNT(DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10))) INTO TEMP_COUNT
                FROM
                    AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                WHERE
                    AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                    AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
                    --AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))  bug 5620412
                    --AND TRUNC(NVL(END_DATE, SYSDATE))  bug 5620412
                    AND AATR.CERTIFICATE_NUMBER IS NOT NULL
                    AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 191

                    SELECT
                        COUNT(DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10))) INTO TEMP_COUNT2
                    FROM
                        AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                    WHERE
                        AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                        AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
                        AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
                        AND TRUNC(NVL(END_DATE, SYSDATE))
                        AND AATR.CERTIFICATE_NUMBER IS NOT NULL
                        AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 213

                    SELECT
                        DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10)) INTO TEMP_UTR_NO
                    FROM
                        AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                    WHERE
                        AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
                        AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
                        AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
                        AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
                        AND AATR.CERTIFICATE_NUMBER IS NOT NULL
                        AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 228

                            UPDATE AP_SUPPLIERS
                            SET PARTNERSHIP_UTR = TO_NUMBER (TEMP_UTR_NO)
                            WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 232

                            UPDATE AP_SUPPLIERS
                            SET UNIQUE_TAX_REFERENCE_NUM = TO_NUMBER(TEMP_UTR_NO)
                            WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 245

                SELECT
                    DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10)) INTO TEMP_UTR_NO
                FROM
                    AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
                WHERE
                    VENDOR_ID = C_REC_INFO.VENDOR_ID
                    AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
                    -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) bug 5620412
                    -- AND TRUNC(NVL(END_DATE, SYSDATE)) bug 5620412
                    AND AATR.CERTIFICATE_NUMBER IS NOT NULL
                    AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);

Line: 260

                        UPDATE AP_SUPPLIERS
                        SET PARTNERSHIP_UTR = TO_NUMBER (TEMP_UTR_NO)
                        WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;

Line: 264

                        UPDATE AP_SUPPLIERS
                        SET UNIQUE_TAX_REFERENCE_NUM = TO_NUMBER(TEMP_UTR_NO)
                        WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;