Greece Notes Receivable - Post Dated Checks and Drafts Report

SELECT    to_char(max(crh.gl_date), 'mm/DD/yyyy') GL_date,
          cr.receipt_number receipt_number,
          arm.name receipt_method,
          to_char(cr.issue_date,'mm/DD/yyyy') issue_date,
          to_Char(cr.receipt_date,'mm/DD/yyyy') receipt_date,
          to_char(aps.due_date,'mm/DD/yyyy') maturity_date,
          hp.party_number||' '||substr(hp.party_name,1,40) customer_header,
          --hp_branch.party_name Issue_bank,
          cr.issuer_bank_branch_id ,
         -- cr.issuer_name Issuer_name,
		 cr.attribute2 Issue_bank,
		 cr.attribute1 Issuer_name,
		 hp_bank.party_name remitted_bank,
           cr.amount receipt_amount,
           gcc.segment4 segment_value,
		   (select distinct ffvtl.description
						from fnd_flex_values_vl ffv,
						     gl_ledgers gl,
							 fnd_id_flex_segments_vl ffs,
							 fun_all_business_units_v fub,
							 fnd_flex_values_tl ffvtl
						where 1=1 
						and gl.CHART_OF_ACCOUNTS_ID = ffs.ID_FLEX_NUM 
						and ffs.description = 'Account'
						and ffs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
						and gl.ledger_id = fub.primary_ledger_id
						and ffv.flex_value = gcc.segment4
						and ffvtl.language='EL'
						and ffvtl.flex_value_id=ffv.flex_value_id
						and fub.bu_id = cr.org_id) segment_description ,
	     cr.org_id,
		 --'CUSTOM A.E.B.E' Company_Name1 
		 (select NAME from XLE_ENTITY_PROFILES where LEGAL_ENTITY_IDENTIFIER='3241') Company_Name1  --REF-284098
						/* ,NVL((SELECT nvl(hl.global_attribute1, 'not exist')
                               FROM   hr_locations_all hl
                                WHERE 1 = 1
								      and hps.location_id = hl.location_id
								      and hl.location_code = 'CUSTOM A.E.B.E.'),'Not Exist') Company_Name --R12*/
          /* ,cr.PAY_FROM_CUSTOMER,	  
          (SELECT account.BANK_ACCOUNT_NUM FROM iby_ext_bank_accounts   account
                                              , iby_pmt_instr_uses_all  acc_instr
                                              , iby_external_payers_all ext_payer 
                                          WHERE 1= 1
                                                AND account.ext_bank_account_id = acc_instr.instrument_id
                                                AND acc_instr.ext_pmt_party_id  = ext_payer.ext_payer_id
                                                AND ext_payer.cust_account_id   = cr.PAY_FROM_CUSTOMER
           ) customer_Bank_account_num */
        ,cr.cash_receipt_id	
		
FROM    ar_cash_receipts_all cr, 
		ar_cash_receipt_history_all crh,
		ar_payment_schedules_all aps,
		hz_cust_site_uses_all csu,
		hz_cust_acct_sites_all cas,
		hz_party_sites hps,
		hz_parties hp,
		ce_bank_acct_uses_all cbaua,
		ce_bank_accounts cba,
		hz_parties hp_bank,
		hz_parties hp_branch,
		hz_relationships hp_relation,
		ar_receipt_methods arm,
		gl_code_combinations gcc
WHERE 1=1
	--and cr.receipt_number = 'Test12345'
	and cr.cash_receipt_id = crh.cash_receipt_id
	and cr.cash_receipt_id = aps.cash_receipt_id
	and cr.customer_site_use_id = csu.site_use_id
	and csu.cust_acct_site_id = cas.cust_acct_site_id
	and cas.party_site_id = hps.party_site_id
	and hps.party_id = hp.party_id
	and cr.remit_bank_acct_use_id = cbaua.bank_acct_use_id
	and cr.org_id = cbaua.org_id
	and cbaua.bank_account_id = cba.bank_account_id
	and cba.bank_id = hp_bank.party_id
	and cba.bank_branch_id = hp_branch.party_id
	and hp_bank.party_id (+) = hp_relation.object_id
	and hp_relation.subject_id (+) = hp_branch.party_id
	and hp_relation.relationship_type (+)             = 'BANK_AND_BRANCH'
	and hp_relation.relationship_code (+)             = 'BRANCH_OF'
	and hp_relation.subject_table_name (+)            = 'HZ_PARTIES'
	and hp_relation.subject_type (+)                  = 'ORGANIZATION'
	and hp_relation.object_table_name (+)             = 'HZ_PARTIES'
	and hp_relation.object_type (+)                   = 'ORGANIZATION'
	and cr.receipt_method_id = arm.receipt_method_id
	and crh.account_code_combination_id = gcc.code_combination_id	
	and crh.status in ('REMITTED','CONFIRMED') -- CLEARED 'Test12345'
	--and 'Draft' = :p_checks
	and (( gcc.segment4 in ('3390000000','3390020000','3391000000') and :p_checks='Check' and arm.name like '%ΕΠΙΤΑΓΕΣ%' )   
	       OR
		 (gcc.segment4 in ('3100000000','3101000000','3103000000') and :p_checks='Draft' and arm.name like '%ΓΡΑΜΜΑΤΙΑ%')
		)

	and cr.org_id = NVL(:p_org_id,cr.org_id)
	--and trunc(crh.gl_date) <= NVL(:p_gl_date,trunc(crh.gl_date))
	 and crh.status = (select status
                   from ar_cash_receipt_history_all
                   where cash_receipt_history_id = (select max(cash_receipt_history_id)
                                        from ar_cash_receipt_history_all
                                        where gl_date <=:p_due_gl_date
                                        and cash_receipt_id = cr.cash_receipt_id
and REVERSAL_GL_DATE is null ))
	and hp.party_name = NVL(:p_customer_name,hp.party_name)
	and cr.org_id in ( SELECT distinct bu.bu_id
								FROM fusion.FUN_ALL_BUSINESS_UNITS_V bu
								,fusion.FUN_USER_ROLE_DATA_ASGNMNTS role
								,fusion.per_users pu
								,fusion.per_roles_dn_vl pr
								,fusion.per_roles_dn_tl prtl
								WHERE role.org_id = bu.bu_id
								AND pu.USER_GUID = role.USER_GUID
								AND pr.ROLE_COMMON_NAME = role.ROLE_NAME
								and role.active_flag ='Y' 
								and pu.username = fnd_global.user_name
								and bu.bu_name ='GR BU EUR'
								and pr.role_id=prtl.role_id
								and prtl.LANGUAGE='US'
								and prtl.ROLE_NAME  in  ('XXC Accounts Receivable Analyst'
														 ,'XXC Accounts Receivable Manager'
														 ,'XXC Receivables Inquiry'
														)
							   )
	
GROUP BY 
        cr.cash_receipt_id,
        cr.receipt_number, 
		arm.name, 
		cr.issue_date, 
		cr.receipt_date, 
		aps.due_date, 
		hp.party_number||' '||substr(hp.party_name,1,40), 
		hp_branch.party_name, 
		cr.issuer_bank_branch_id ,
		cr.attribute2 ,
		cr.attribute1 ,
		hp_bank.party_name,
        cr.amount, 
	    gcc.segment4,
	    cr.org_id
ORDER BY aps.due_date ASC