/*PAYMENT FILE LAYOUT****************************************************************************/
/* This reports return a list of payments that need to be applied to Revenue Results.*/
/* Written by Linda Oliveira in August 2008*/
/* Modification history: */
/* Run History */
/* This report is run on a daily basis for Collections.*/



select spriden_id||'|'||spriden_last_name||'|'||spriden_first_name||'|'||tbbacct_deli_code||'|'||sum(tbraccd_amount)||'|'||trunc(sysdate)||'|'||
(case when spriden_pidm in (select spriden_pidm from spriden
where spriden_change_ind is null
and spriden_pidm not in
	(select distinct sfrstcr_pidm from sfrstcr)
and spriden_pidm not in
	(select distinct tbraccd_pidm from tbraccd, tbbdetc
	where tbraccd_detail_code = tbbdetc_detail_code
	and tbbdetc_dcat_code like 'TUI')
and spriden_pidm not in
	(select distinct tbraccd_pidm from tbraccd, tbbdetc
	where tbraccd_detail_code = tbbdetc_detail_code
	and tbbdetc_dcat_code like '%NV'
	and substr(tbraccd_detail_code,1,1) in ('1','Z','V')
	and tbraccd_term_code <> 'ARTERM')
and spriden_pidm not in
	(select distinct tbbcprf_pidm
	from tbbcprf
	where tbbcprf_ecat_code in ('SS','US','CS','GS'))) 
then 'GAR' else 'SAR' end)
from tbraccd, spriden,tbbacct, tbbdetc, tbbcprf
where tbraccd_pidm = spriden_pidm
and spriden_change_ind is null
and tbraccd_pidm = tbbacct_pidm 
and tbraccd_pidm = tbbcprf_pidm(+)
and trunc(tbraccd_entry_date) = trunc(sysdate)-1
--and trunc(tbraccd_entry_date) between '12-NOV-2010' and '14-NOV-2010'
and tbraccd_detail_code = tbbdetc_detail_code
and tbbdetc_type_ind = 'P'
and tbbacct_deli_code is not null
and tbbacct_deli_code not in ('1W', '2W', '4W')
--,'1R','2R','4R')
group by spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, tbbacct_deli_code, tbbcprf_ecat_code
having sum(tbraccd_amount) <> 0
