eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.XTR_REPLICATE_BANK_BRANCHES_P SQL Statements

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

Line: 4


 * PROCEDURE update_bank_branches
 *
 * DESCRIPTION
 *     This procedure is called directly by CE to update
 *      the bank branch related data into XTR tables.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *
 *     p_xtr_party_info_rec    	Record type of XTR_PARTY_INFO.
 *					             This record type contains the Bank/Bank Branch
 *                              related information about the bank attached with
 *                              Bank Account.
 *    p_update_type             To determine what parameters CE has updated
 *   IN/OUT:
 *
 *   OUT:
 *      x_return_status                  Return status after the call. The
 *                                      status can be
 *                      FND_API.G_RET_STS_SUCCESS - for success
 *                      FND_API.G_RET_STS_ERR   - for expected error
 *                      FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
 *      x_msg_count                     To return the number of error messages
 *                                      in stack
 *      x_msg_data                      To return the error message if
 *                                      x_msg_count = 1.
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   06-17-2005    Bhargav Adireddy        	o Created.
 *
 */

    PROCEDURE UPDATE_BANK_BRANCHES
      ( p_xtr_party_info_rec   IN XTR_PARTY_INFO%ROWTYPE,
        p_update_type          IN NUMBER,
        x_return_status    OUT NOCOPY VARCHAR2,
        x_msg_count     OUT NOCOPY NUMBER,
        x_msg_data     OUT NOCOPY VARCHAR2
        ) IS

l_check_branch VARCHAR2(10);

Line: 51


    SELECT 'Y'
    FROM XTR_PARTY_INFO
    WHERE ce_bank_branch_id = p_xtr_party_info_rec.ce_bank_branch_id;

Line: 66


        VALIDATE_BANK_BRANCH(p_xtr_party_info_rec,p_update_type,x_return_status);

Line: 68


            MODIFY_BANK_BRANCH(p_xtr_party_info_rec,p_update_type,x_return_status);

Line: 91


END UPDATE_BANK_BRANCHES;

Line: 96


PROCEDURE UPDATE_BANK_BRANCHES
      ( p_ce_bank_branch_id	IN	XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
        p_short_name	IN	XTR_PARTY_INFO.short_name%TYPE,
        p_full_name	IN	XTR_PARTY_INFO.full_name%TYPE,
        p_swift_id	IN	XTR_PARTY_INFO.swift_id%TYPE,
        x_return_status    OUT NOCOPY VARCHAR2,
        x_msg_count     OUT NOCOPY NUMBER,
        x_msg_data     OUT NOCOPY VARCHAR2
        ) IS

l_xtr_party_info_rec   XTR_PARTY_INFO%ROWTYPE;

Line: 116


	UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,1, x_return_status,
        x_msg_count,
        x_msg_data);

Line: 128


END UPDATE_BANK_BRANCHES;

Line: 131


PROCEDURE UPDATE_BANK_BRANCHES
      ( p_ce_bank_branch_id	IN	XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
        p_address_2	IN	XTR_PARTY_INFO.address_2%TYPE,
        p_address_3	IN	XTR_PARTY_INFO.address_3%TYPE,
        p_address_4	IN	XTR_PARTY_INFO.address_4%TYPE,
        p_address_5	IN	XTR_PARTY_INFO.address_5%TYPE,
        p_country_code	IN	XTR_PARTY_INFO.country_code%TYPE,
        p_state_code	IN	XTR_PARTY_INFO.state_code%TYPE,
        x_return_status    OUT NOCOPY VARCHAR2,
        x_msg_count     OUT NOCOPY NUMBER,
        x_msg_data     OUT NOCOPY VARCHAR2
        ) IS

l_xtr_party_info_rec   XTR_PARTY_INFO%ROWTYPE;

Line: 156


	UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,4, x_return_status,
        x_msg_count,
        x_msg_data);

Line: 168


END UPDATE_BANK_BRANCHES;

Line: 171


PROCEDURE UPDATE_BANK_BRANCHES
      ( p_ce_bank_branch_id	IN	XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
        p_contact_name	IN	XTR_PARTY_INFO.contact_name%TYPE,
        p_email_address	IN	XTR_PARTY_INFO.email_address%TYPE,
        p_fax_number	IN	XTR_PARTY_INFO.fax_number%TYPE,
        p_phone_number	IN	XTR_PARTY_INFO.phone_number%TYPE,
        x_return_status    OUT NOCOPY VARCHAR2,
        x_msg_count     OUT NOCOPY NUMBER,
        x_msg_data     OUT NOCOPY VARCHAR2
        ) IS

l_xtr_party_info_rec   XTR_PARTY_INFO%ROWTYPE;

Line: 192


	UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,3, x_return_status,
        x_msg_count,
        x_msg_data);

Line: 204


END UPDATE_BANK_BRANCHES;

Line: 206


PROCEDURE UPDATE_BANK_BRANCHES
      ( p_ce_bank_branch_id	IN	XTR_PARTY_INFO.ce_bank_branch_id%TYPE,
        p_address_2	IN	XTR_PARTY_INFO.address_2%TYPE,
        p_address_3	IN	XTR_PARTY_INFO.address_3%TYPE,
        p_address_4	IN	XTR_PARTY_INFO.address_4%TYPE,
        p_address_5	IN	XTR_PARTY_INFO.address_5%TYPE,
        p_p_address_1	IN	XTR_PARTY_INFO.p_address_1%TYPE,
        p_p_address_2	IN	XTR_PARTY_INFO.p_address_2%TYPE,
        p_p_address_3	IN	XTR_PARTY_INFO.p_address_3%TYPE,
        p_p_address_4	IN	XTR_PARTY_INFO.p_address_4%TYPE,
        p_state_code	IN	XTR_PARTY_INFO.state_code%TYPE,
        p_swift_id	IN	XTR_PARTY_INFO.swift_id%TYPE,
        x_return_status    OUT NOCOPY VARCHAR2,
        x_msg_count     OUT NOCOPY NUMBER,
        x_msg_data     OUT NOCOPY VARCHAR2
        ) IS

l_xtr_party_info_rec   XTR_PARTY_INFO%ROWTYPE;

Line: 239


	UPDATE_BANK_BRANCHES( l_xtr_party_info_rec,2, x_return_status,
        x_msg_count,
        x_msg_data);

Line: 251


END UPDATE_BANK_BRANCHES;

Line: 260


 *      before it is inserted into XTR_PARTY_INFO. This procedure will perform the
 *      required validations and puts the corresponding error messages into list
 *
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *     p_xtr_party_info_rec    	Record type of XTR_PARTY_INFO.
 *					             This record type contains the Bank/Bank Branch
 *                              related information about the bank attached with
 *                              Bank Account.
 *    p_update_type             Parameter to determine what parmeters are updated by CE
 *   IN/OUT:
 *
 *   OUT:
 *      x_return_status                  Return status after the call. The
 *                                      status can be
 *                      FND_API.G_RET_STS_SUCCESS - for success
 *                      FND_API.G_RET_STS_ERR   - for expected error
 *                      FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   06-17-2005    Bhargav Adireddy        	o Created.
 *
 */

    PROCEDURE VALIDATE_BANK_BRANCH
      ( p_xtr_party_info_rec   IN XTR_PARTY_INFO%ROWTYPE,
        p_update_type          IN NUMBER,
        x_return_status   IN OUT NOCOPY VARCHAR2
        ) IS

BEGIN
    -- Verifies if the ce_bank_branch_id in XTR_PARTY_INFO is passed as null
    IF(p_xtr_party_info_rec.ce_bank_branch_id is null) THEN
        x_return_status := FND_API.G_RET_STS_ERROR;

Line: 303


    IF(p_xtr_party_info_rec.short_name is null and p_update_type = 1) THEN
        x_return_status := FND_API.G_RET_STS_ERROR;

Line: 308


    IF(p_xtr_party_info_rec.full_name is null and p_update_type = 1) THEN
        x_return_status := FND_API.G_RET_STS_ERROR;

Line: 313


    IF(p_xtr_party_info_rec.country_code is null and p_update_type = 4) THEN
        x_return_status := FND_API.G_RET_STS_ERROR;

Line: 326


 *     This procedure will update XTR_PARTY_INFO table with the
 *      Bank Branch data passed form CE.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *     p_xtr_party_info_rec    	Record type of XTR_PARTY_INFO.
 *					             This record type contains the Bank/Bank Branch
 *                              related information about the bank attached with
 *                              Bank Account.                        related information.
 *    p_update_type             Parameter to determine which parameters are updated by CE
 *   IN/OUT:
 *
 *   OUT:
 *      x_return_status                  Return status after the call. The
 *                                      status can be
 *                      FND_API.G_RET_STS_SUCCESS - for success
 *                      FND_API.G_RET_STS_ERR   - for expected error
 *                      FND_API.G_RET_STS_UNEXP_ERR - for unexpected error
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   06-17-2005    Bhargav Adireddy        	o Created.
 *
 */


    PROCEDURE MODIFY_BANK_BRANCH
      ( p_xtr_party_info_rec   IN XTR_PARTY_INFO%ROWTYPE,
        p_update_type          IN NUMBER,
        x_return_status  IN  OUT NOCOPY VARCHAR2
        ) IS
CURSOR c_chk_lock IS
    SELECT ce_bank_branch_id
    FROM XTR_PARTY_INFO
    WHERE ce_bank_branch_id = p_xtr_party_info_rec.ce_bank_branch_id
    FOR UPDATE NOWAIT;

Line: 373


    IF(p_update_type = 1) then
        UPDATE XTR_PARTY_INFO
        SET short_name      =   nvl(p_xtr_party_info_rec.short_name,short_name)
        ,full_name          =   p_xtr_party_info_rec.full_name
        ,updated_by        =   fnd_global.user_id
        ,updated_on        =   sysdate
                        WHERE   ce_bank_branch_id = l_ce_bank_branch_id;

Line: 380


   ELSIF(p_update_type = 2) THEN
        UPDATE XTR_PARTY_INFO
        SET address_2       =   p_xtr_party_info_rec.address_2
        ,address_3    =   p_xtr_party_info_rec.address_3
        ,address_4 =  p_xtr_party_info_rec.address_4
        ,address_5 =   p_xtr_party_info_rec.address_5
        ,updated_by        =   fnd_global.user_id
        ,updated_on        =   sysdate
        ,p_address_1        =   p_xtr_party_info_rec.p_address_1
        ,p_address_2        =   p_xtr_party_info_rec.p_address_2
        ,p_address_3        =   p_xtr_party_info_rec.p_address_3
        ,p_address_4        =   p_xtr_party_info_rec.p_address_4
        ,state_code        =   p_xtr_party_info_rec.state_code
        ,swift_id           =   p_xtr_party_info_rec.swift_id
                WHERE   ce_bank_branch_id = l_ce_bank_branch_id;

Line: 396


   ELSIF(p_update_type = 3) THEN

        UPDATE XTR_PARTY_INFO
        SET contact_name          =   p_xtr_party_info_rec.contact_name
        ,country_code    =   p_xtr_party_info_rec.country_code
        ,email_address           =   p_xtr_party_info_rec.email_address
        ,updated_by        =   fnd_global.user_id
        ,updated_on        =   sysdate
        ,fax_number    =   p_xtr_party_info_rec.fax_number
        ,phone_number =  p_xtr_party_info_rec.phone_number
                WHERE   ce_bank_branch_id = l_ce_bank_branch_id;

Line: 408


  ELSIF(p_update_type = 4) THEN
        UPDATE XTR_PARTY_INFO
        SET address_2       =   p_xtr_party_info_rec.address_2
        ,address_3    =   p_xtr_party_info_rec.address_3
        ,address_4 =  p_xtr_party_info_rec.address_4
        ,address_5 =   p_xtr_party_info_rec.address_5
        ,updated_by        =   fnd_global.user_id
        ,updated_on        =   sysdate
        ,country_code    =   p_xtr_party_info_rec.country_code
        ,state_code        =   p_xtr_party_info_rec.state_code
                       WHERE   ce_bank_branch_id = l_ce_bank_branch_id;

Line: 478


    SELECT authorised
    FROM XTR_PARTY_INFO
    WHERE ce_bank_branch_id = p_ce_bank_branch_id;