Select COMPANY,
PTY,
CCY ,
TRANS_DATE,
TRANSACTION_NUMBER,
N_DAYS,
ACC_INT_BF ,
PRIN_ACTION,
PRIN_ADJUST,
BAL_OUT,
INT_CALC,
INT_RATE,
SET_DATE,
int_set,
rounding_type,
day_count_type,
rounding_factor,
ig_year_basis,
XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER) CF_PRV_DAY,
XTR_XTRIGSUM_XMLP_PKG.bal_bfformula(COMPANY, PTY, CCY, TRANS_DATE) BAL_BF,
XTR_XTRIGSUM_XMLP_PKG.cf_prv_rateformula(COMPANY, PTY, CCY, TRANS_DATE) CF_PRV_RATE,
XTR_XTRIGSUM_XMLP_PKG.cf_interest_for_dummy_recformu(XTR_XTRIGSUM_XMLP_PKG.cf_no_of_daysformula(N_DAYS, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER), TRANS_DATE), rounding_factor, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER), TRANS_DATE, XTR_XTRIGSUM_XMLP_PKG.bal_bfformula(COMPANY, PTY, CCY, TRANS_DATE), XTR_XTRIGSUM_XMLP_PKG.cf_prv_rateformula(COMPANY, PTY, CCY, TRANS_DATE), rounding_type) CF_interest_for_dummy_rec,
XTR_XTRIGSUM_XMLP_PKG.cf_int_cfformula(COMPANY, PTY, CCY, XTR_XTRIGSUM_XMLP_PKG.cf_interest_for_dummy_recformu(XTR_XTRIGSUM_XMLP_PKG.cf_no_of_daysformula(N_DAYS, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER), TRANS_DATE), rounding_factor, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER), TRANS_DATE, XTR_XTRIGSUM_XMLP_PKG.bal_bfformula(COMPANY, PTY, CCY, TRANS_DATE), XTR_XTRIGSUM_XMLP_PKG.cf_prv_rateformula(COMPANY, PTY, CCY, TRANS_DATE), rounding_type), day_count_type, ACC_INT_BF) CF_int_Cf,
XTR_XTRIGSUM_XMLP_PKG.cf_no_of_daysformula(N_DAYS, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER), TRANS_DATE) CF_no_of_days
from
(
select company_code COMPANY
,party_code PTY
,currency CCY
,transfer_date TRANS_DATE
,transaction_number TRANSACTION_NUMBER
,no_of_days N_DAYS
,interest_settled ACC_INT_BF
,principal_action PRIN_ACTION
,Decode(Principal_action,'REC',(-1)*principal_adjust,principal_adjust) PRIN_ADJUST
,balance_out BAL_OUT
,interest INT_CALC
,interest_rate INT_RATE
,settle_date SET_DATE
,decode(settle_date,Null,0,accum_interest_bf) int_set
,rounding_type
,day_count_type
from XTR_INTERGROUP_TRANSFERS_V
where party_code like nvl(upper(:SEL_PARTY2),party_code)
and company_code like nvl(upper(:SEL_COMP2),company_code)
and transfer_date between nvl(:PER_FROM, transfer_date) and :PER_TO
AND COMPANY_CODE IN( SELECT xca.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES xca,
XTR_DEALER_CODES xdc
WHERE company_code = xca.party_code
AND xca.dealer_code = xdc.dealer_code
AND xca.company_authorised_for_input = 'Y'
AND xdc.user_id = fnd_global.user_id)
UNION
select company_code
,party_code
,currency
,:Per_To
,transaction_number+1
,:Per_To - Transfer_Date
,0
,'DUMMY'
,0
,balance_out
,0
,interest_rate
,transfer_date
,0
,rounding_type
,day_count_type
/*,
XTR_XTRIGSUM_XMLP_PKG.cf_prv_dayformula(COMPANY, PTY, CCY, TRANS_DATE, TRANSACTION_NUMBER) CF_PRV_DAY,
XTR_XTRIGSUM_XMLP_PKG.bal_bfformula(COMPANY, PTY, CCY, TRANS_DATE) BAL_BF,
XTR_XTRIGSUM_XMLP_PKG.cf_prv_rateformula(COMPANY, PTY, CCY, TRANS_DATE) CF_PRV_RATE,
XTR_XTRIGSUM_XMLP_PKG.cf_interest_for_dummy_recformu(:CF_no_of_days, rounding_factor, ig_year_basis, day_count_type, SET_DATE, PTY, COMPANY, CCY, PRIN_ACTION, :CF_PRV_DAY, TRANS_DATE, :BAL_BF, :CF_PRV_RATE, rounding_type) CF_interest_for_dummy_rec,
XTR_XTRIGSUM_XMLP_PKG.cf_int_cfformula(COMPANY, PTY, CCY, :CF_interest_for_dummy_rec, day_count_type, ACC_INT_BF) CF_int_Cf,
XTR_XTRIGSUM_XMLP_PKG.cf_no_of_daysformula(N_DAYS, ig_year_basis, day_count_type, transfer_date, party_code, company_code, currency, 'DUMMY', :CF_PRV_DAY, TRANS_DATE) CF_no_of_days*/
from XTR_INTERGROUP_TRANSFERS_V ,
(
Select Comp_Code,
CParty_Code,
Curr,
Trans_Date,
max(Transaction_number) trans_no
From
Xtr_InterGroup_Transfers_V a,
(
Select Company_Code Comp_Code ,
Party_Code CParty_Code ,
Currency Curr ,
Max(transfer_date) Trans_Date,
min(:per_to - transfer_date) min_diff
From Xtr_InterGroup_Transfers_V
where party_code like nvl(upper(:sel_party2),party_code)
and company_code like nvl(upper(:sel_comp2),company_code)
and transfer_date <= :Per_To
AND COMPANY_CODE IN( SELECT xca.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES xca,
XTR_DEALER_CODES xdc
WHERE company_code = xca.party_code
AND xca.dealer_code = xdc.dealer_code
AND xca.company_authorised_for_input = 'Y' and xdc.user_id = fnd_global.user_id)
Group by Company_Code ,
Party_Code ,
Currency
) q
where a.company_code = q.comp_code
and a.party_code = q.cparty_code
and a.currency = q.curr
and a.transfer_date = q.trans_date
and q.min_diff <> 0
group by comp_Code,CParty_Code,curr,trans_Date
) q
where party_code = q.Cparty_Code
and company_code =q.Comp_Code
and currency = q.curr
and transfer_date = q.trans_date
and transaction_number = q.trans_no
) j, xtr_master_currencies_v v
Where j.ccy = v.currency
order by 1,2,3,4 asc,5 asc |
