[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct ba.CURRENCY_CODE
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE = l_date
and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.LEDGER_BALANCE is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.LEDGER_BALANCE is not null;
select LEDGER_BALANCE
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.AVAILABLE_BALANCE is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.AVAILABLE_BALANCE is not null;
select AVAILABLE_BALANCE
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.VALUE_DATED_BALANCE is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.VALUE_DATED_BALANCE is not null;
select VALUE_DATED_BALANCE
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.ONE_DAY_FLOAT is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.ONE_DAY_FLOAT is not null;
select ONE_DAY_FLOAT
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.TWO_DAY_FLOAT is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.TWO_DAY_FLOAT is not null;
select TWO_DAY_FLOAT
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.AVERAGE_CLOSE_LEDGER_MTD is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.AVERAGE_CLOSE_LEDGER_MTD is not null;
select AVERAGE_CLOSE_LEDGER_MTD
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.AVERAGE_CLOSE_LEDGER_YTD is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.AVERAGE_CLOSE_LEDGER_YTD is not null;
select AVERAGE_CLOSE_LEDGER_YTD
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null;
select AVERAGE_CLOSE_AVAILABLE_MTD
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select min(abs(BALANCE_DATE - p_date))
into v_date_offset
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null;
select min(balance_date)
into v_date
from ce_bank_acct_balances bb
where bb.BANK_ACCOUNT_ID = p_bank_account_id
and abs(BALANCE_DATE - p_date) = v_date_offset
and bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null;
select AVERAGE_CLOSE_AVAILABLE_YTD
into v_balance
from ce_bank_acct_balances
where BANK_ACCOUNT_ID = p_bank_account_id
and BALANCE_DATE = v_date;
select distinct ba.CURRENCY_CODE
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE = l_date
and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
select ba.bank_account_id
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE = l_date
and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
and ba.CURRENCY_CODE = currency
and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID);
select BANK_BRANCH_NAME
into l_Bank_Branch_Name
from ce_bank_branches_v
where BRANCH_PARTY_ID = p_branch_party_id;
select BANK_ACCOUNT_NAME
into l_Bank_ACCT_NAME
from ce_bank_accounts
where BANK_ACCOUNT_ID = p_bank_acct_id;
select name
into l_legal_entity_name
from CE_LE_BG_OU_VS_V
where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
select bb.LEDGER_BALANCE, bb.AVAILABLE_BALANCE, bb.VALUE_DATED_BALANCE, bb.ONE_DAY_FLOAT, bb.TWO_DAY_FLOAT, bb.AVERAGE_CLOSE_LEDGER_MTD, bb.AVERAGE_CLOSE_LEDGER_YTD, bb.AVERAGE_CLOSE_AVAILABLE_MTD, bb.AVERAGE_CLOSE_AVAILABLE_YTD
into v_LegerBalanceAC, v_AvailableBalanceAC, v_IntCalBalanceAC, v_OneDayFloatAC, v_TwoDayFloatAC, v_AvgLegerMTDAC, v_AvgLegerYTDAC, v_AvgAvailableMTDAC, v_AvgAvailableYTDAC
from ce_bank_acct_balances bb
where bb.BALANCE_DATE = l_date
and bb.BANK_ACCOUNT_ID = v_bank_account_id;
select decode(v_exchange_rate, -1, v_dummy, v_LegerBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')),p_exchange_rate_type))
into v_LegerBalanceRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_AvailableBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')) ,p_exchange_rate_type))
into v_AvailableBalanceRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_IntCalBalanceAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')),p_exchange_rate_type))
into v_IntCalBalanceRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_OneDayFloatAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')) ,p_exchange_rate_type))
into v_OneDayFloatRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_TwoDayFloatAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
into v_TwoDayFloatRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_AvgLegerMTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
into v_AvgLegerMTDRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_AvgLegerYTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
into v_AvgLegerYTDRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_AvgAvailableMTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date, to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
into v_AvgAvailableMTDRC
from dual;
select decode(v_exchange_rate, -1, v_dummy, v_AvgAvailableYTDAC*CE_BANKACCT_BA_REPORT_UTIL.get_rate(v_currency_code, p_reporting_currency, nvl(p_exchange_rate_date,to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')), p_exchange_rate_type))
into v_AvgAvailableYTDRC
from dual;
select xmlelement("BankAccount",
xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
ba.BANK_ACCOUNT_NAME as "BankAccountName",
ba.BANK_ACCOUNT_NUM as "BankAccountNum",
ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
bh.BANK_NAME as "BankName",
bh.BANK_BRANCH_NAME as "BankBranchName",
ba.CURRENCY_CODE as "BankAccountCurrency",
p_reporting_currency as "ReportingCurrency",
ba.MIN_TARGET_BALANCE as "TargetBalanceMinimum",
ba.MAX_TARGET_BALANCE as "TargetBalanceMaximum",
bb.BALANCE_DATE as "BalanceDate"),
xmlelement("LedgerBalanceAC", xmlattributes(v_LegerBalance_Date as "BalanceDate",
decode(v_LegerBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceAC, ba.CURRENCY_CODE)),
xmlelement("LedgerBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceRC, p_reporting_currency)),
xmlelement("AvailableBalanceAC", xmlattributes(v_AvailableBalance_Date as "BalanceDate", decode(v_AvailableBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceAC, ba.CURRENCY_CODE)),
xmlelement("AvailableBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceRC, p_reporting_currency)),
xmlelement("IntCalBalanceAC", xmlattributes(v_IntCalBalance_Date as "BalanceDate", decode(v_IntCalBalance_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceAC, ba.CURRENCY_CODE)),
xmlelement("IntCalBalanceRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceRC, p_reporting_currency)),
xmlelement("OneDayFloatAC", xmlattributes(v_OneDayFloat_Date as "BalanceDate", decode(v_OneDayFloat_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatAC, ba.CURRENCY_CODE)),
xmlelement("OneDayFloatRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatRC, p_reporting_currency)),
xmlelement("TwoDayFloatAC", xmlattributes(v_TwoDayFloat_Date as "BalanceDate", decode(v_TwoDayFloat_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatAC, ba.CURRENCY_CODE)),
xmlelement("TwoDayFloatRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatRC, p_reporting_currency)),
xmlelement("AvgLegerMTDAC", xmlattributes(v_AvgLegerMTD_Date as "BalanceDate", decode(v_AvgLegerMTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDAC, ba.CURRENCY_CODE)),
xmlelement("AvgLegerMTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDRC, p_reporting_currency)),
xmlelement("AvgLegerYTDAC", xmlattributes(v_AvgLegerYTD_Date as "BalanceDate", decode(v_AvgLegerYTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDAC, ba.CURRENCY_CODE)),
xmlelement("AvgLegerYTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDRC, p_reporting_currency)),
xmlelement("AvgAvailableMTDAC", xmlattributes(v_AvgAvailableMTD_Date as "BalanceDate", decode(v_AvgAvailableMTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDAC, ba.CURRENCY_CODE)),
xmlelement("AvgAvailableMTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDRC, p_reporting_currency)),
xmlelement("AvgAvailableYTDAC", xmlattributes(v_AvgAvailableYTD_Date as "BalanceDate", decode(v_AvgAvailableYTD_Date - l_date, 0, '', '*') as "Flag"), CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDAC, ba.CURRENCY_CODE)),
xmlelement("AvgAvailableYTDRC", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDRC, p_reporting_currency))
)
into v_xml_seg1
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE = l_date
and ba.BANK_ACCOUNT_ID = v_bank_account_id;
select xmlconcat(v_xml_seg1, v_xml_seg2)
into v_xml_seg3
from dual;
select
xmlelement("BankAcctGroupByCurrency", xmlattributes(v_currency_code as "AccountCurrency", v_exchange_rate as "ExchangeRate"),
v_xml_seg2,
xmlforest(CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceSubTAC, v_currency_code) as "SubTotalLedgerBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceSubTAC, v_currency_code) as "SubTotalAvailableBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceSubTAC, v_currency_code) as "SubTotalIntCalBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatSubTAC, v_currency_code) as "SubTotalOneDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatSubTAC, v_currency_code) as "SubtotalTwoDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDSubTAC, v_currency_code) as "SubTotalAvgLegerMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDSubTAC, v_currency_code) as "SubTotalAvgLegerYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDSubTAC, v_currency_code) as "SubTotalAvgAvailableMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDSubTAC, v_currency_code) as "SubTotalAvgAvailableYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceSubTRC, p_reporting_currency) as "SubTotalLedgerBalanceRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceSubTRC, p_reporting_currency) as "SubTotalAvailableBalanceRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceSubTRC, p_reporting_currency) as "SubTotalIntCalBalanceRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatSubTRC, p_reporting_currency) as "SubTotalOneDayFloatRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatSubTRC, p_reporting_currency) as "SubtotalTwoDayFloatRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDSubTRC, p_reporting_currency) as "SubTotalAvgLegerMTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDSubTRC, p_reporting_currency) as "SubTotalAvgLegerYTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDSubTRC, p_reporting_currency) as "SubTotalAvgAvailableMTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDSubTRC, p_reporting_currency) as "SubTotalAvgAvailableYTDRC"
)
)
into v_xml_1
from dual;
select xmlconcat(v_xml_1, v_xml_2)
into v_xml_3
from dual;
select
xmlelement("BankAccountList",
xmlelement("BankBranchName", l_Bank_Branch_Name),
xmlelement("BankAcctNum", l_Bank_ACCT_NAME),
xmlelement("BankAC", p_bank_acct_currency),
xmlelement("LegalEntity", l_legal_entity_name),
xmlelement("ReportingCurrency", p_reporting_currency),
xmlelement("ReportDate", sysdate),
xmlelement("AsOfDate", to_char(l_date)),
xmlelement("ExchangeRateType", p_exchange_rate_type),
xmlelement("ExchangeRateDate", l_exchange_rate_date),
xmlelement("TotalBalanceSummationFlag", l_total_balance_flag),
v_xml_2, -- xml node 'BankAccttGroupByCurrency'
xmlelement("LegerBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_LegerBalanceTotal, p_reporting_currency)),
xmlelement("AvailableBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvailableBalanceTotal, p_reporting_currency)),
xmlelement("IntCalBalanceTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_IntCalBalanceTotal, p_reporting_currency)),
xmlelement("OneDayFloatTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_OneDayFloatTotal, p_reporting_currency)),
xmlelement("TwoDayFloatTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_TwoDayFloatTotal, p_reporting_currency)),
xmlelement("AvgLegerMTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerMTDTotal, p_reporting_currency)),
xmlelement("AvgLegerYTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgLegerYTDTotal, p_reporting_currency)),
xmlelement("AvgAvailableMTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableMTDTotal, p_reporting_currency)),
xmlelement("AvgAvailableYTDTotal", CE_BANKACCT_BA_REPORT_UTIL.get_balance(v_AvgAvailableYTDTotal, p_reporting_currency))
)
into l_xml
from dual;
select BANK_BRANCH_NAME, BANK_NAME
into l_Bank_Branch_Name, l_Bank_Name
from ce_bank_branches_v
where BRANCH_PARTY_ID = p_branch_party_id;
select BANK_ACCOUNT_NAME
into l_Bank_ACCT_NAME
from ce_bank_accounts
where BANK_ACCOUNT_ID = p_bank_acct_id;
select name
into l_legal_entity_name
from CE_LE_BG_OU_VS_V
where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
select
xmlelement("BankAccountList",
xmlelement("BankName", l_Bank_Name),
xmlelement("BankBranchName", l_Bank_Branch_Name),
xmlelement("BankAcctName", l_Bank_ACCT_NAME),
xmlelement("BankAC", p_bank_acct_currency),
xmlelement("LegalEntity", l_legal_entity_name),
xmlelement("ReportingCurrency", p_reporting_currency),
xmlelement("ReportDate", sysdate),
xmlelement("FromDate", to_char(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'))),
xmlelement("ToDate", to_char(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'))),
xmlelement("ExchangeRateType", p_exchange_rate_type),
xmlelement("ExchangeRateDate", l_exchange_rate_date),
xmlagg(xmlelement("BankAccount",
xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
ba.BANK_ACCOUNT_NAME as "BankAccountName",
ba.BANK_ACCOUNT_NUM as "BankAccountNum",
ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
bh.BANK_NAME as "BankName",
bh.BANK_BRANCH_NAME as "BankBranchName",
ba.CURRENCY_CODE as "BankAccountCurrency",
p_reporting_currency as "ReportingCurrency",
CE_BANKACCT_BA_REPORT_UTIL.get_rate(ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "ExchangeRate",
to_char(ba.MIN_TARGET_BALANCE, FND_CURRENCY.GET_FORMAT_MASK(ba.CURRENCY_CODE, 30)) as "TargetBalanceMinimum",
to_char(ba.MAX_TARGET_BALANCE, FND_CURRENCY.GET_FORMAT_MASK(ba.CURRENCY_CODE, 30)) as "TargetBalanceMaximum",
bb.BALANCE_DATE as "BalanceDate",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE) as "LedgerBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "LedgerBalanceRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE) as "AvailableBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvailableBalanceRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE) as "InterestCalBalAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "InterestCalBalRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE) as "OneDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "OneDayFloatRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE) as "TwoDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "TwoDayFloatRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE) as "AvgCloseLedgerMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseLedgerMTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE) as "AvgCloseLedgerYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseLedgerYTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE) as "AvgCloseAvailableMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseAvailableMTDRC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE) as "AvgCloseAvailableYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_reporting_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE, p_reporting_currency, p_exchange_rate_date,p_exchange_rate_type) as "AvgCloseAvailableYTDRC"
))))
into l_xml
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE between nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE) and nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE)
and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID)
and (bb.LEDGER_BALANCE is not null
or bb.AVAILABLE_BALANCE is not null
or bb.VALUE_DATED_BALANCE is not null
or bb.ONE_DAY_FLOAT is not null
or bb.TWO_DAY_FLOAT is not null
or bb.AVERAGE_CLOSE_LEDGER_MTD is not null
or bb.AVERAGE_CLOSE_LEDGER_YTD is not null
or bb.AVERAGE_CLOSE_AVAILABLE_MTD is not null
or bb.AVERAGE_CLOSE_AVAILABLE_YTD is not null);
select BANK_BRANCH_NAME, BANK_NAME
into l_Bank_Branch_Name, l_Bank_Name
from ce_bank_branches_v
where BRANCH_PARTY_ID = p_branch_party_id;
select BANK_ACCOUNT_NAME
into l_Bank_ACCT_NAME
from ce_bank_accounts
where BANK_ACCOUNT_ID = p_bank_acct_id;
select name
into l_legal_entity_name
from CE_LE_BG_OU_VS_V
where legal_entity_id = p_legal_entity_id and organization_type = 'LEGAL_ENTITY';
select meaning
into l_balance_type_meaning
from ce_lookups
where lookup_code = p_actual_balance_type
and lookup_type = 'BANK_ACC_BAL_TYPE';
select
xmlelement("BankAccountList",
xmlelement("BankName", l_Bank_Name),
xmlelement("BankBranchName", l_Bank_Branch_Name),
xmlelement("BankAcctName", l_Bank_ACCT_NAME),
xmlelement("BankAC", p_bank_acct_currency),
xmlelement("LegalEntity", l_legal_entity_name),
xmlelement("ReportDate", sysdate),
xmlelement("FromDate", to_char(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'))),
xmlelement("ToDate", to_char(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'))),
xmlelement("ActualBalanceType", l_balance_type_meaning),
xmlagg(xmlelement("BankAccount",
xmlforest(ba.BANK_ACCOUNT_ID as "BankAccountID",
ba.BANK_ACCOUNT_NAME as "BankAccountName",
ba.BANK_ACCOUNT_NUM as "BankAccountNum",
ba.ACCOUNT_OWNER_ORG_ID as "LegalEntity",
bh.BANK_NAME as "BankName",
bh.BANK_BRANCH_NAME as "BankBranchName",
ba.CURRENCY_CODE as "BankAccountCurrency",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(ba.MIN_TARGET_BALANCE,ba.CURRENCY_CODE) as "TargetBalanceMinimum",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(ba.MAX_TARGET_BALANCE, ba.CURRENCY_CODE) as "TargetBalanceMaximum",
bb.BALANCE_DATE as "BalanceDate",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.LEDGER_BALANCE, ba.CURRENCY_CODE) as "LedgerBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVAILABLE_BALANCE, ba.CURRENCY_CODE) as "AvailableBalanceAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.VALUE_DATED_BALANCE, ba.CURRENCY_CODE) as "InterestCalBalAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.ONE_DAY_FLOAT, ba.CURRENCY_CODE) as "OneDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.TWO_DAY_FLOAT, ba.CURRENCY_CODE) as "TwoDayFloatAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_MTD, ba.CURRENCY_CODE) as "AvgCloseLedgerMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_LEDGER_YTD, ba.CURRENCY_CODE) as "AvgCloseLedgerYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_MTD, ba.CURRENCY_CODE) as "AvgCloseAvailableMTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(bb.AVERAGE_CLOSE_AVAILABLE_YTD, ba.CURRENCY_CODE) as "AvgCloseAvailableYTDAC",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(CE_BANKACCT_BA_REPORT_UTIL.get_variance(bb.BANK_ACCOUNT_ID, bb.BALANCE_DATE, p_actual_balance_type),ba.CURRENCY_CODE) as "Variance",
l_balance_type_meaning as "ActualBalanceType",
CE_BANKACCT_BA_REPORT_UTIL.get_balance(pb.PROJECTED_BALANCE,ba.CURRENCY_CODE) as "ProjectedBalance"
))))
into l_xml
from ce_bank_accounts ba, ce_bank_branches_v bh, ce_bank_acct_balances bb, ce_projected_balances pb
where ba.BANK_BRANCH_ID = bh.BRANCH_PARTY_ID
and ba.BANK_ACCOUNT_ID = bb.BANK_ACCOUNT_ID
and bb.BALANCE_DATE between nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE) and nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'),bb.BALANCE_DATE)
and bh.BRANCH_PARTY_ID = nvl(p_branch_party_id, bh.BRANCH_PARTY_ID)
and ba.BANK_ACCOUNT_ID = nvl(p_bank_acct_id, ba.BANK_ACCOUNT_ID)
and ba.CURRENCY_CODE = nvl(p_bank_acct_currency, ba.CURRENCY_CODE)
and ba.ACCOUNT_OWNER_ORG_ID = nvl(p_legal_entity_id, ba.ACCOUNT_OWNER_ORG_ID)
and pb.BANK_ACCOUNT_ID (+) = bb.BANK_ACCOUNT_ID
and pb.BALANCE_DATE (+) = bb.BALANCE_DATE;