XTR Intercompany - Statements

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