[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TRXN_REFERENCE_NUMBER,
TRXN_SUBTYPE_CODE_ID,
TRANSACTION_DATE,
ANTICIPATED_VALUE_DATE,
TRANSACTION_DESCRIPTION,
PAYMENT_CURRENCy_CODE,
PAYMENT_AMOUNT,
SOURCE_PARTY_ID,
SOURCE_LEGAL_ENTITY_ID,
SOURCE_BANK_ACCOUNT_ID,
DESTINATION_PARTY_ID,
DESTINATION_LEGAL_ENTITY_ID,
DESTINATION_BANK_ACCOUNT_ID,
CREATED_FROM_DIR,
CREATE_FROM_STMTLINE_ID,
BANK_TRXN_NUMBER,
PAYMENT_OFFSET_CCID,
RECEIPT_OFFSET_CCID
FROM
CE_PAYMENT_TRANSACTIONS
WHERE
trxn_reference_number = p_reference_number;
select cashflow_exchange_rate_type
into l_exchange_rate_type
from ce_system_parameters
where legal_entity_id = p_le_id;
select currency_code
into bank_currency_code
from ce_bank_accounts
where bank_account_id = p_bank_account_id;
select statement_date
into p_exch_date
from ce_statement_headers sh,
ce_statement_lines sl
where sh.statement_header_id = sl.statement_header_id
and sl.statement_line_id = G_created_from_stmtline_id;
select gl_date
into p_exch_date
from ce_statement_headers sh,
ce_statement_lines sl
where sh.statement_header_id = sl.statement_header_id
and sl.statement_line_id = G_created_from_stmtline_id;
select trx_date
into p_exch_date
from ce_statement_lines sl
where sl.statement_line_id = G_created_from_stmtline_id;
select exchange_rate,
exchange_rate_type,
exchange_rate_date
into p_exch_rate,
p_exch_type,
p_exch_date
from ce_statement_lines
where statement_line_id = G_created_from_stmtline_id;
select bat_exchange_date_type
into l_exchange_date_type
from ce_system_parameters
where legal_entity_id = p_le_id;
select count(1)
into p_temp
from ce_cashflow_acct_h
where cashflow_id = p_cashflow_number
and status_code = 'ACCOUNTING_ERROR';
select count(1),status_code
into p_temp,p_status
from ce_cashflow_acct_h
where cashflow_id = p_cashflow_number
group by status_code;
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = p_ledger_id;
log('values to be inserted in cashfows are');
--check if its in insert or update mode
BEGIN
SELECT COUNT(*)
INTO p_count
FROM CE_CASHFLOWS
WHERE TRXN_REFERENCE_NUMBER = p_trxn_reference_number;
l_mode := 'UPDATE';
l_mode := 'INSERT';
IF (l_mode = 'INSERT') THEN
--insert a source cashflow
CE_CASHFLOWS_PKG.insert_row (
X_ROWID => l_row_id,
X_CASHFLOW_ID => p_cashflow_id1,
X_CASHFLOW_LEDGER_ID => p_source_ledger_id,
X_CASHFLOW_LEGAL_ENTITY_ID => G_source_le_id,
X_CASHFLOW_BANK_ACCOUNT_ID => G_source_bank_acct_id,
X_CASHFLOW_DIRECTION => p_source_dir,
X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
X_CASHFLOW_DATE => G_transaction_date,
X_CASHFLOW_AMOUNT => G_payment_amount,
X_BASE_AMOUNT => p_pay_base_amount,
X_DESCRIPTION => G_transaction_desc,
X_CASHFLOW_EXCHANGE_RATE => p_pay_exchange_rate,
X_CASHFLOW_EXCHANGE_DATE => p_pay_exch_date,
X_CASHFLOW_EXCHANGE_RATE_TYPE => p_pay_exch_rate_type,
X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
X_BANK_TRXN_NUMBER => G_bank_trxn_number,
X_SOURCE_TRXN_TYPE => 'BAT',
X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
X_STATEMENT_LINE_ID => G_created_from_stmtline_id,
X_ACTUAL_VALUE_DATE => null,
X_COUNTERPARTY_PARTY_ID => G_dest_party_id,
X_COUNTERPARTY_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
X_OFFSET_CCID => p_source_offset,
X_CASHFLOW_STATUS_CODE => 'CREATED',
X_CLEARED_DATE => null,
X_CLEARED_AMOUNT => null,
X_CLEARED_EXCHANGE_RATE => null,
X_CLEARED_EXCHANGE_DATE => null,
X_CLEARED_EXCHANGE_RATE_TYPE => null,
X_CLEARING_CHARGES_AMOUNT => null,
X_CLEARING_ERROR_AMOUNT => null,
X_CLEARED_BY_FLAG => null,
X_REFERENCE_TEXT => null,
X_BANK_ACCOUNT_TEXT => null,
X_CUSTOMER_TEXT => null,
X_CREATED_BY => nvl(fnd_global.user_id, -1),
X_CREATION_DATE => sysdate,
X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1)
) ;
-- insert a destination cashflow
CE_CASHFLOWS_PKG.insert_row (
X_ROWID => l_row_id,
X_CASHFLOW_ID => p_cashflow_id2,
X_CASHFLOW_LEDGER_ID => p_dest_ledger_id,
X_CASHFLOW_LEGAL_ENTITY_ID => G_dest_le_id,
X_CASHFLOW_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
X_CASHFLOW_DIRECTION => p_dest_dir,
X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
X_CASHFLOW_DATE => G_transaction_date,
X_CASHFLOW_AMOUNT => G_payment_amount,
X_BASE_AMOUNT => p_recp_base_amount,
X_DESCRIPTION => G_transaction_desc,
X_CASHFLOW_EXCHANGE_RATE => p_recp_exchange_rate,
X_CASHFLOW_EXCHANGE_DATE => p_recp_exch_date,
X_CASHFLOW_EXCHANGE_RATE_TYPE => p_recp_exch_rate_type,
X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
X_BANK_TRXN_NUMBER => G_bank_trxn_number,
X_SOURCE_TRXN_TYPE => 'BAT',
X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
X_STATEMENT_LINE_ID => null,
X_ACTUAL_VALUE_DATE => null,
X_COUNTERPARTY_PARTY_ID => G_source_party_id,
X_COUNTERPARTY_BANK_ACCOUNT_ID => G_source_bank_acct_id,
X_OFFSET_CCID => p_dest_offset,
X_CASHFLOW_STATUS_CODE => 'CREATED',
X_CLEARED_DATE => null,
X_CLEARED_AMOUNT => null,
X_CLEARED_EXCHANGE_RATE => null,
X_CLEARED_EXCHANGE_DATE => null,
X_CLEARED_EXCHANGE_RATE_TYPE => null,
X_CLEARING_CHARGES_AMOUNT => null,
X_CLEARING_ERROR_AMOUNT => null,
X_CLEARED_BY_FLAG => null,
X_REFERENCE_TEXT => null,
X_BANK_ACCOUNT_TEXT => null,
X_CUSTOMER_TEXT => null,
X_CREATED_BY => nvl(fnd_global.user_id, -1),
X_CREATION_DATE => sysdate,
X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1)
) ;
ELSIF (l_mode = 'UPDATE') THEN
BEGIN
select rowid, cashflow_id, object_version_number
bulk collect into l_rowid, l_cashflow, l_objectversion
from ce_cashflows
where trxn_reference_number = p_trxn_reference_number
order by cashflow_id;
--update the source cashflow
CE_CASHFLOWS_PKG.update_row (
X_ROWID => l_rowid(1),
X_CASHFLOW_ID => p_cashflow_id1,
X_CASHFLOW_LEDGER_ID => p_source_ledger_id,
X_CASHFLOW_LEGAL_ENTITY_ID => G_source_le_id,
X_CASHFLOW_BANK_ACCOUNT_ID => G_source_bank_acct_id,
X_CASHFLOW_DIRECTION => p_source_dir,
X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
X_CASHFLOW_DATE => G_transaction_date,
X_CASHFLOW_AMOUNT => G_payment_amount,
X_BASE_AMOUNT => p_pay_base_amount,
X_DESCRIPTION => G_transaction_desc,
X_CASHFLOW_EXCHANGE_RATE => p_pay_exchange_rate,
X_CASHFLOW_EXCHANGE_DATE => p_pay_exch_date,
X_CASHFLOW_EXCHANGE_RATE_TYPE => p_pay_exch_rate_type,
X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
X_BANK_TRXN_NUMBER => G_bank_trxn_number,
X_SOURCE_TRXN_TYPE => 'BAT',
X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
X_STATEMENT_LINE_ID => G_created_from_stmtline_id,
X_ACTUAL_VALUE_DATE => null,
X_COUNTERPARTY_PARTY_ID => G_dest_party_id,
X_COUNTERPARTY_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
X_OFFSET_CCID => p_source_offset,
X_CASHFLOW_STATUS_CODE => 'CREATED',
X_CLEARED_DATE => null,
X_CLEARED_AMOUNT => null,
X_CLEARED_EXCHANGE_RATE => null,
X_CLEARED_EXCHANGE_DATE => null,
X_CLEARED_EXCHANGE_RATE_TYPE => null,
X_CLEARING_CHARGES_AMOUNT => null,
X_CLEARING_ERROR_AMOUNT => null,
X_CLEARED_BY_FLAG => null,
X_REFERENCE_TEXT => null,
X_BANK_ACCOUNT_TEXT => null,
X_CUSTOMER_TEXT => null,
X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1),
X_OBJECT_VERSION_NUMBER => l_objectversion(1)
);
--update a destination cashflow
CE_CASHFLOWS_PKG.update_row(
X_ROWID => l_rowid(2),
X_CASHFLOW_ID => p_cashflow_id2,
X_CASHFLOW_LEDGER_ID => p_dest_ledger_id,
X_CASHFLOW_LEGAL_ENTITY_ID => G_dest_le_id,
X_CASHFLOW_BANK_ACCOUNT_ID => G_dest_bank_acct_id,
X_CASHFLOW_DIRECTION => p_dest_dir,
X_CASHFLOW_CURRENCY_CODE => G_payment_curr_code,
X_CASHFLOW_DATE => G_transaction_date,
X_CASHFLOW_AMOUNT => G_payment_amount,
X_BASE_AMOUNT => p_recp_base_amount,
X_DESCRIPTION => G_transaction_desc,
X_CASHFLOW_EXCHANGE_RATE => p_recp_exchange_rate,
X_CASHFLOW_EXCHANGE_DATE => p_recp_exch_date,
X_CASHFLOW_EXCHANGE_RATE_TYPE => p_recp_exch_rate_type,
X_TRXN_REFERENCE_NUMBER => G_trxn_reference_number,
X_BANK_TRXN_NUMBER => G_bank_trxn_number,
X_SOURCE_TRXN_TYPE => 'BAT',
X_SOURCE_TRXN_SUBTYPE_CODE_ID => G_trxn_subtype_code_id,
X_STATEMENT_LINE_ID => null,
X_ACTUAL_VALUE_DATE => null,
X_COUNTERPARTY_PARTY_ID => G_source_party_id,
X_COUNTERPARTY_BANK_ACCOUNT_ID => G_source_bank_acct_id,
X_OFFSET_CCID => p_dest_offset,
X_CASHFLOW_STATUS_CODE => 'CREATED',
X_CLEARED_DATE => null,
X_CLEARED_AMOUNT => null,
X_CLEARED_EXCHANGE_RATE => null,
X_CLEARED_EXCHANGE_DATE => null,
X_CLEARED_EXCHANGE_RATE_TYPE => null,
X_CLEARING_CHARGES_AMOUNT => null,
X_CLEARING_ERROR_AMOUNT => null,
X_CLEARED_BY_FLAG => null,
X_REFERENCE_TEXT => null,
X_BANK_ACCOUNT_TEXT => null,
X_CUSTOMER_TEXT => null,
X_LAST_UPDATED_BY => nvl(fnd_global.user_id, -1),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATE_LOGIN => nvl(fnd_global.user_id, -1),
X_OBJECT_VERSION_NUMBER => l_objectversion(2)
);