APPS.PAY_US_PPM_HOOK SQL Statements

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

Line: 23

    Description : This package is called from the BEFORE INSERT/UPDATE
                  User Hooks. The following are the functionalities present
                  in User Hook

                  1. Validates the Bank Account Number in the Bank Details KFF

                  2. Validates the IAT Transit Code in the Further Information DFF

                  3. Validates the Tranks Code in the Bank Details KFF


    Change List
    -----------
    Name           Date          Version Bug      Text
    -------------- -----------   ------- -------  -----------------------------
    mikarthi       05-Dec-2008   115.0   8806003  Initial Version
    mikarthi       20-Jan-2010   115.4   8904560  Included validation for DFI
                                                  Qualifier and Country Code.

  *****************************************************************************/

/* *****************************************************************************
   Name        : VALIDATE_BANK_DETAILS
   Scope       : LOCAL
   Description : This Function validates the Transit Code and Bank AccountNum
                 During Insert operation
******************************************************************************/

    procedure VALIDATE_BANK_DETAILS(P_ORG_PAYMENT_METHOD_ID in NUMBER
                                   ,P_SEGMENT3 in VARCHAR2
                                   ,P_SEGMENT4 in VARCHAR2
                                   ,p_ppm_information1 in VARCHAR2
                                   ,p_ppm_information2 in VARCHAR2
                                   ,p_ppm_information3 in VARCHAR2
                                   ,p_effective_date in date) is

    l_is_foreign_trans  VARCHAR2(30);

Line: 64

        select PMETH_INFORMATION9
        INTO l_is_foreign_trans
        from pay_org_payment_methods_f where ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
        AND p_effective_date BETWEEN effective_start_date
                         AND effective_end_date;

Line: 103

       Name        : UPDATE_BANK_DETAILS
       Scope       : LOCAL
       Description : This Function validates the Transit Code and Bank AccountNum
                     During Update operation
    ******************************************************************************/

    procedure UPDATE_BANK_DETAILS(P_PERSONAL_PAYMENT_METHOD_ID in NUMBER
                                   ,P_SEGMENT3 in VARCHAR2
                                   ,P_SEGMENT4 in VARCHAR2
                                   ,p_ppm_information1 in VARCHAR2
                                   ,p_ppm_information2 in VARCHAR2
                                   ,p_ppm_information3 in VARCHAR2
                                   ,p_effective_date in date) is

        l_is_foreign_trans  VARCHAR2(30);

Line: 126

        SELECT EXT.SEGMENT3, EXT.SEGMENT4
        FROM   PAY_PERSONAL_PAYMENT_METHODS_F PPM, PAY_EXTERNAL_ACCOUNTS EXT
        WHERE  PPM.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
        AND    EXT.EXTERNAL_ACCOUNT_ID        = PPM.EXTERNAL_ACCOUNT_ID
        AND    P_EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
                                AND     PPM.EFFECTIVE_END_DATE;

Line: 134

        SELECT PPM.PPM_INFORMATION1,
               PPM.PPM_INFORMATION2,
               PPM.PPM_INFORMATION3
        FROM   PAY_PERSONAL_PAYMENT_METHODS_F PPM
        WHERE  PPM.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
        AND    P_EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
                                AND     PPM.EFFECTIVE_END_DATE;

Line: 144

        select popm.PMETH_INFORMATION9
        INTO l_is_foreign_trans
        from pay_org_payment_methods popm,  pay_Personal_payment_methods pppm
        where popm.ORG_PAYMENT_METHOD_ID =  pppm.ORG_PAYMENT_METHOD_ID
        and pppm.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
        AND p_effective_date BETWEEN popm.effective_start_date
                             AND popm.effective_end_date;

Line: 206

    end   UPDATE_BANK_DETAILS;