[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* to be inserted or updated.
* 'I' - Insert,'U' - Update
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE REPLICATE_BANK_ACCOUNTS
( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
p_action_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR c_branch_name
IS
select full_name
from xtr_party_info
where party_code = p_xtr_bank_account_rec.bank_code;
INSERT_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
ELSIF(p_action_flag = 'U')THEN -- If user has updated an existing bank
-- account in CE
UPDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
* PROCEDURE insert_bank_accounts
*
* DESCRIPTION
* This procedure is called in replicate_bank_accounts to insert
* the bank account related data into XTR tables. This procedure is
* called when p_action_flag = 'I'
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
* This record type contains the Bank Account
* related information.
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE INSERT_BANK_ACCOUNTS
( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
--
VALIDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec,x_return_status);
xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Insert_Bank_Accounts');
xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'x_return_status' , x_return_status);
xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Insert_Bank_Accounts');
END INSERT_BANK_ACCOUNTS;
* PROCEDURE update_bank_accounts
*
* DESCRIPTION
* This procedure is called in replicate_bank_accounts to update
* the bank account related data into XTR tables. This procedure is
* called when p_action_flag = 'I'
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
*
* p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
* This record type contains the Bank Account
* related information.
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE UPDATE_BANK_ACCOUNTS
( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
-- This account already exists in XTR and it is being updated in CE
VALIDATE_BANK_ACCOUNTS(p_xtr_bank_account_rec
,x_return_status);
xtr_risk_debug_pkg.dpush('xtr_replicate_bank_accounts_P: '||'Update_Bank_Accounts');
xtr_risk_debug_pkg.dlog('Insert_Bank_Accounts: ' || 'bank_acct_rec.ce_bank_account_id',p_xtr_bank_account_rec.ce_bank_account_id);
xtr_risk_debug_pkg.dlog('Update_Bank_Accounts: ' || 'x_return_status', x_return_status);
xtr_risk_debug_pkg.dpop('xtr_replicate_bank_accounts_P: '||'Update_Bank_Accounts');
END UPDATE_BANK_ACCOUNTS;
* it is inserted/updated into XTR_BANK_ACCOUNTS. This procedure will
* perform the required validations and puts the corresponding error
* messages into list
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
* This record type contains the Bank Account
* related information.
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE VALIDATE_BANK_ACCOUNTS
( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- This cursor verifies if the code combination id passed is defined in
-- XTR_GL_REFERENCES_V for that company
CURSOR c_cc_id IS
SELECT 'Y'
FROM XTR_GL_REFERENCES_V
WHERE code_combination_id = p_xtr_bank_account_rec.code_combination_id
AND company_code = p_xtr_bank_account_rec.party_code;
select authorized
from xtr_price_models_v
where deal_type = 'CA'
and code = p_xtr_bank_account_rec.pricing_model;
select authorised,legal_entity_id
from xtr_parties_v
where party_code = p_xtr_bank_account_rec.party_code
and party_type = 'C';
select default_acct,ce_bank_account_id -- Modified Bug 4764437
from xtr_bank_accounts
where party_code = p_xtr_bank_account_rec.party_code
and currency = p_xtr_bank_account_rec.currency
and default_acct = 'Y';
* This procedure will insert/update XTR_BANK_ACCOUNTS table with the
* Bank account data passed form CE. It will insert if p_action_flag = 'I'
* and update if p_action_flag = 'U'
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_xtr_bank_account_rec Record type of XTR_BANK_ACCOUNTS
* This record type contains the Bank Account
* p_action_flag Indicates wether Bank account information needs
* to be inserted or updated.
* 'I' - Insert,'U' - Update related information.
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE MODIFY_BANK_ACCOUNTS
( p_xtr_bank_account_rec IN XTR_BANK_ACCOUNTS%ROWTYPE,
p_action_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
-- This cursor verifies if there is a lock on the row that user is trying to update
CURSOR c_chk_lock IS
SELECT ce_bank_account_id
FROM XTR_BANK_ACCOUNTS
WHERE ce_bank_account_id = p_xtr_bank_account_rec.ce_bank_account_id
FOR UPDATE NOWAIT;
IF ( (p_action_flag = 'I') or (p_action_flag = 'U' and l_ce_bank_account_id Is Null )) THEN -- The bank account has to be inserted
INSERT INTO XTR_BANK_ACCOUNTS
(account_number
,authorised
,party_code
,party_type
,bank_code
,currency
,bank_short_code
,default_acct
,created_by
,created_on
,eft_script_name
,code_combination_id
,interest_calculation_basis
,location
,portfolio_code
,primary_settlement_method
,street
,year_calc_type
,swift_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,pricing_model
,legal_account_name
,ce_bank_account_id
,bank_branch_id
,bank_acct_use_id
,gl_company_code
)
VALUES
(p_xtr_bank_account_rec.account_number
,'Y'
,p_xtr_bank_account_rec.party_code
,'C'
,p_xtr_bank_account_rec.bank_code
,p_xtr_bank_account_rec.currency
,p_xtr_bank_account_rec.bank_short_code
,p_xtr_bank_account_rec.default_acct
,fnd_global.user_id
,sysdate
,p_xtr_bank_account_rec.eft_script_name
,p_xtr_bank_account_rec.code_combination_id
,p_xtr_bank_account_rec.interest_calculation_basis
,p_xtr_bank_account_rec.location
,p_xtr_bank_account_rec.portfolio_code
,'E'
,p_xtr_bank_account_rec.street
,p_xtr_bank_account_rec.year_calc_type
,p_xtr_bank_account_rec.swift_id
,p_xtr_bank_account_rec.attribute_category
,p_xtr_bank_account_rec.attribute1
,p_xtr_bank_account_rec.attribute2
,p_xtr_bank_account_rec.attribute3
,p_xtr_bank_account_rec.attribute4
,p_xtr_bank_account_rec.attribute5
,p_xtr_bank_account_rec.attribute6
,p_xtr_bank_account_rec.attribute7
,p_xtr_bank_account_rec.attribute8
,p_xtr_bank_account_rec.attribute9
,p_xtr_bank_account_rec.attribute10
,p_xtr_bank_account_rec.attribute11
,p_xtr_bank_account_rec.attribute12
,p_xtr_bank_account_rec.attribute13
,p_xtr_bank_account_rec.attribute14
,p_xtr_bank_account_rec.attribute15
,p_xtr_bank_account_rec.pricing_model
,p_xtr_bank_account_rec.legal_account_name
,p_xtr_bank_account_rec.ce_bank_account_id
,p_xtr_bank_account_rec.bank_branch_id
,p_xtr_bank_account_rec.bank_acct_use_id
,p_xtr_bank_account_rec.party_code
);
ELSIF (p_action_flag = 'U' and l_ce_bank_account_id Is Not Null ) THEN -- The bank account has to be updated
UPDATE XTR_BANK_ACCOUNTS
SET authorised = p_xtr_bank_account_rec.authorised
,bank_code = p_xtr_bank_account_rec.bank_code
,bank_short_code = p_xtr_bank_account_rec.bank_short_code
,default_acct = p_xtr_bank_account_rec.default_acct
,eft_script_name = p_xtr_bank_account_rec.eft_script_name
,code_combination_id = p_xtr_bank_account_rec.code_combination_id
-- ,interest_calculation_basis = p_xtr_bank_account_rec.interest_calculation_basis Bug 5398434
,location = p_xtr_bank_account_rec.location
,portfolio_code = p_xtr_bank_account_rec.portfolio_code
,street = p_xtr_bank_account_rec.street
,updated_by = fnd_global.user_id
,updated_on = sysdate
-- ,year_calc_type = p_xtr_bank_account_rec.year_calc_type Bug 5398434
,swift_id = p_xtr_bank_account_rec.swift_id
,attribute_category = p_xtr_bank_account_rec.attribute_category
,attribute1 = p_xtr_bank_account_rec.attribute1
,attribute2 = p_xtr_bank_account_rec.attribute2
,attribute3 = p_xtr_bank_account_rec.attribute3
,attribute4 = p_xtr_bank_account_rec.attribute4
,attribute5 = p_xtr_bank_account_rec.attribute5
,attribute6 = p_xtr_bank_account_rec.attribute6
,attribute7 = p_xtr_bank_account_rec.attribute7
,attribute8 = p_xtr_bank_account_rec.attribute8
,attribute9 = p_xtr_bank_account_rec.attribute9
,attribute10 = p_xtr_bank_account_rec.attribute10
,attribute11 = p_xtr_bank_account_rec.attribute11
,attribute12 = p_xtr_bank_account_rec.attribute12
,attribute13 = p_xtr_bank_account_rec.attribute13
,attribute14 = p_xtr_bank_account_rec.attribute14
,attribute15 = p_xtr_bank_account_rec.attribute15
,pricing_model = p_xtr_bank_account_rec.pricing_model
,legal_account_name = p_xtr_bank_account_rec.legal_account_name
,currency = p_xtr_bank_account_rec.currency
WHERE ce_bank_account_id = l_ce_bank_account_id; -- change this
SELECT text
FROM xtr_sys_languages_vl
WHERE item_name = p_name
AND MODULE_NAME = 'XTRSECOM'
UNION
SELECT text
FROM xtr_sys_languages_vl
WHERE item_name = p_name
AND MODULE_NAME = 'XTRSECPY';
* schedule being updated.
* p_interest_rounding This is the ROUNDING_TYPE in XTR_BANK_ACCOUNTS.
* CE will pass this parameter. This will tell us
* what is the updated interest rounding.
* p_interest_includes This is the DAY_COUNT_TYPE in XTR_BANK_ACCOUNTS.
* CE will pass this parameter. This will tell us
* what is the updated Interest Includes.
* p_interest_calculation
* _basis This is the BASIS in XTR_BANK_ACCOUNTS.
* CE will pass this parameter. This will tell us
* what is the updated BASIS.
* p_day_count_basis This is the DAY_COUNT_BASIS in XTR_BANK_ACCOUNTS.
* CE will pass this parameter. This will tell us
* what is the updated day count basis.
* 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
*
* 05-19-2005 Bhargav Adireddy o Created.
*
*/
PROCEDURE REPLICATE_INTEREST_SCHEDULES
( p_ce_bank_account_id IN XTR_BANK_ACCOUNTS.ce_bank_account_id%TYPE,--Chnage this to bank_account_id
p_interest_rounding IN XTR_BANK_ACCOUNTS.ROUNDING_TYPE%TYPE,
p_interest_includes IN XTR_BANK_ACCOUNTS.DAY_COUNT_TYPE%TYPE,
p_interest_calculation_basis IN XTR_BANK_ACCOUNTS.INTEREST_CALCULATION_BASIS%TYPE,
p_day_count_basis IN XTR_BANK_ACCOUNTS.YEAR_CALC_TYPE%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_chk_lock_interest IS
SELECT ce_bank_account_id
FROM XTR_BANK_ACCOUNTS
WHERE ce_bank_account_id = p_ce_bank_account_id
FOR UPDATE NOWAIT;
UPDATE XTR_BANK_ACCOUNTS
SET rounding_type = p_interest_rounding
,day_count_type = p_interest_includes
,year_calc_type = p_day_count_basis,
interest_calculation_basis = p_interest_calculation_basis
WHERE ce_bank_account_id = l_ce_bank_account_id; -- change this
XTR_REPLICATE_BANK_BALANCES.UPDATE_ROUNDING_DAYCOUNT
(p_ce_bank_account_id ,p_interest_rounding,p_interest_includes
,x_return_status);