AR Customer Query Report

-- SQL CUSTOM AR Customer Query
SELECT hp.party_name Customer_Name
      ,a.account_number Customer_Number
	  ,a.account_name Account_Name
	  ,a.orig_system_reference Customer_Reference 
	  --,a.status 
	  ,(case when sysdate<=nvl(a.ACCOUNT_TERMINATION_DATE,Sysdate+1)  then 'Active' else 'Inactive'end ) status
      ,ZR.REGISTRATION_NUMBER Tax_Reference      -- hp.tax_reference 
      ,tax_idnt.tax_payer_number Taxpayer_Id --hp.jgzz_fiscal_code Taxpayer_Id
      ,hp.mission_statement
	  ,a.attribute_category Context_Value
	  ,a.attribute1 Omni_Customer_Code
	  ,a.attribute2 INDI_Group_Hold
	  ,a.attribute3 INDI_Region_Code
	  ,a.global_attribute_category Cust_Global_Attr_Cat
	  ,a.global_attribute1 Cust_Global_Attr1
	  ,a.customer_class_code Customer_Class
      ,b.attribute1 Bill_to_Location --c.location 
      ,c.site_use_code Site_Use
	  ,b.attribute2 Selling_Profile 
	  ,b.attribute3 OMS_Co
	  ,b.ATTRIBUTE4 Cloud_Entity_Code
	  ,b.ATTRIBUTE5 AKA_Name
	  ,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8||'.'||gcc.segment9  Receivables_Acct-- ok
      ,c.primary_flag Site_Use_Primary_Flag
	  ,c.status Site_Use_Status
	  --,c.tax_classification Site_Tax_Classification
	  ,site_tax.TAX_CLASSIFICATION_CODE Site_Tax_Classification
      ,null Site_Global_Attr_Cat -- c.global_attribute_category  
	  ,null Site_Global_Attr1    -- c.global_attribute1 
      ,c.sic_code SIC_Code
	  ,b.orig_system_reference Site_Reference
	  ,hps.identifying_address_flag Identifying_Flag 
      ,hl.address1 
	  ,hl.address2 
	  ,hl.address3 
	  ,hl.address4 
	  ,hl.city 
	  ,hl.postal_code zip
	  ,hl.state
	  ,hl.province
	  ,hl.county
	  ,hl.country
	  ,(select INITCAP(NLS_LANGUAGE) from FND_LANGUAGES  where LANGUAGE_CODE=b.ACCT_SITE_LANGUAGE)  language 
      ,b.customer_category_code Customer_Category
	  ,h.name Site_Payment_Term
	  ,d.name Prof_Std_Term
	  ,e.override_terms Prof_Override_Term 
	  ,bu.bu_name Business_unit 
      ,g.name Profile_Class_Name
	  ,e.status Profile_Status
	  ,f.name Profile_Collector_Name
	  ,e.tolerance Profile_Tolerance
	  ,e.discount_terms Profile_Discount_Terms
	  ,cpa.currency_code Credit_Currency_Code
	  ,cpa.trx_credit_limit
	  ,cpa.overall_credit_limit  
      ,rm.name Payment_Method
	  ,rpm.primary_flag
      ,abb.bank_name
	  ,abb.bank_branch_name Branch_name
	  ,aba.bank_account_num_electronic Bank_Acct_No
	  ,aba.bank_account_name Bank_Acct_Name
	  ,abau.primary_flag Bank_Primary_Flag 
	  ,to_char(abau.start_date,'MM/DD/YYYY') Bank_Start_Date
	  ,to_char(abau.end_date,'MM/DD/YYYY') Bank_End_Date 
	  ,hpc.person_first_name Contact_First_Name
	  ,hpc.person_last_name Contact_Last_Name 
	  ,hoc.job_title_code Contact_Job_Title
	  ,hp.sic_code SIC_Code_Header
	  ,hp.sic_code_type SIC_Code_Type_Header
	  ,e.risk_code
	  ,e.credit_rating
	  ,flv.meaning Credit_Classification 
	  ,e.credit_hold 	  
      ,ZR.TAX  Site_Tax_Code --c.tax_code  
      ,e1.global_attribute_category
	  ,e1.global_attribute1
	  ,e1.global_attribute2 
      ,b.global_attribute_category Site_Global_Attribute_Category
	  ,b.global_attribute2 Site_Global_Attribute2
	  ,b.global_attribute3 Site_Global_Attribute3
	  ,b.global_attribute4 Site_Global_Attribute4
	  ,b.global_attribute5 Site_Global_Attribute5
	  ,b.global_attribute6 Site_Global_Attribute6
	  ,b.global_attribute7 Site_Global_Attribute7
	  ,b.global_attribute8 Site_Global_Attribute8
      ,b.global_attribute9 Site_Global_Attribute9 
      ,null territory --b.territory, 
	  ,b.party_site_id
--jca.pan_no,jca.tan_no,jca.cst_reg_no,jca.st_reg_no,jca.vat_reg_no,jca.service_tax_regno,jca.service_type_code -- not required in fusion
             FROM hz_cust_accounts a,   
                  hz_parties hp,    
                  hz_cust_acct_sites_all b,   
                  hz_party_sites hps, 
                  hz_locations hl, 
                  hz_cust_site_uses_all c, 
            	  gl_code_combinations gcc,   
                  ra_terms_tl d, 
                  ra_terms_tl h, 
                  hz_customer_profiles_f e,
                  hz_customer_profiles_f e1,
                  ar_collectors f, 
                  hz_cust_profile_classes g,   
                  hz_relationships hr, 
                  hz_parties hpc,   
                  hz_org_contacts hoc,  
                  CE_BANK_BRANCHES_V abb, 
                  IBY_EXT_BANK_ACCOUNTS_V aba, 
                  IBY_PMT_INSTR_USES_ALL abau, 
                  IBY_EXTERNAL_PAYERS_ALL iepa, 
                  hz_cust_profile_amts_f cpa,   
                  ra_cust_receipt_methods rpm,   
                  ar_receipt_methods rm, 
                  fnd_lookup_values flv,   
                  -- JAI_CMN_CUS_ADDRESSES jca
				  zx_party_tax_profile tax,
				  zx_party_tax_profile site_tax,
				  zx_registrations ZR,
				  zx_party_taxpayer_idntfs tax_idnt,
ar_ref_accounts_all ara,
fnd_setid_assignments   fsa,
fun_all_business_units_v bu,
fnd_setid_sets_vl       fs				  
             WHERE a.cust_account_id = b.cust_account_id 
             AND b.cust_acct_site_id = c.cust_acct_site_id 
             AND c.site_use_id = e.site_use_id(+)
             --AND sysdate between e.effective_start_date AND  e.effective_end_date	
             AND sysdate  >= e.effective_start_date (+)
			 AND  sysdate <=e.effective_end_date(+)			 
             AND a.party_id = hp.party_id 
             AND b.party_site_id = hps.party_site_id 
             AND hps.party_site_id = hoc.party_site_id(+) 
             AND hps.location_id = hl.location_id 
             AND hoc.party_relationship_id = hr.relationship_id(+) 
             AND hr.subject_id = hpc.party_id(+) 
             AND nvl(hr.subject_type, 'PERSON') = 'PERSON'
             AND a.cust_account_id = e1.cust_account_id(+) 
             AND e1.site_use_id (+) IS NULL 
			 AND sysdate between e1.effective_start_date AND  e1.effective_end_date
             AND e.profile_class_id = g.profile_class_id(+) 
             AND e.collector_id = f.collector_id(+) 
             AND nvl(c.payment_term_id,-1) = d.term_id(+) 
             AND nvl(e.standard_terms,-1) = h.term_id(+) 
             AND nvl(e.credit_classification,'X') = flv.lookup_code(+) 
             AND flv.lookup_type(+) = 'AR_CMGT_CREDIT_CLASSIFICATION' 
             AND flv.enabled_flag(+) = 'Y' 
             AND d.language(+) = 'US'
             AND h.language(+) = 'US' 
             AND flv.language(+) = 'US'
             AND e.cust_account_profile_id = cpa.cust_account_profile_id(+) 
			 and trunc(sysdate) <= cpa.EFFECTIVE_END_DATE (+)
             AND c.site_use_id = iepa.acct_site_use_id(+) 
             AND abau.ext_pmt_party_id(+) = iepa.ext_payer_id 
             AND abau.instrument_id = aba.bank_account_id(+) 
             AND aba.branch_party_id = abb.branch_party_id(+) 
             AND c.site_use_id = rpm.site_use_id(+) 
             AND rpm.receipt_method_id = rm.receipt_method_id(+) 
             AND ara.REC_CCID  = gcc.code_combination_id(+) 
             --AND b.cust_acct_site_id = jca.address_id(+) 
             AND c.site_use_id = ara.source_ref_account_id(+)
             AND ara.source_ref_table(+) = 'HZ_CUST_SITE_USES_ALL'
             AND ara.REC_CCID = gcc.code_combination_id(+)
             AND fs.set_id=b.set_id
             AND fsa.determinant_type = 'BU'
             AND fsa.determinant_value = bu.bu_id
             AND fsa.set_id = fs.set_id
             AND fsa.reference_group_name LIKE 'HZ_CUSTOMER_ACCOUNT_SITE'
             AND nvl(cpa.currency_code,'All') in ( nvl(:p_currency_code,nvl(cpa.currency_code,'All')))
             AND b.party_site_id = site_tax.PARTY_ID(+)
             and hp.party_id = tax.PARTY_ID(+)
             AND TAX.PARTY_TAX_PROFILE_ID = ZR.PARTY_TAX_PROFILE_ID(+)
             AND site_tax.party_type_code(+) ='THIRD_PARTY_SITE'
             AND tax.party_type_code(+) ='THIRD_PARTY'
             AND sysdate  >= zr.effective_from (+)
             AND sysdate <= nvl(zr.effective_to,sysdate)
			 AND tax.party_tax_profile_id = tax_idnt.entity_id (+) 
			 AND sysdate  >= tax_idnt.effective_from (+)
             AND sysdate <= nvl(tax_idnt.effective_to,sysdate)
             AND (bu.bu_id  in ( :p_bu_id ) or 'All' in ( :p_bu_id||'All') )
             AND ((sysdate<=nvl(a.ACCOUNT_TERMINATION_DATE,Sysdate+1) AND :p_cust_status='Active')
			       or
			      ( sysdate > nvl(a.ACCOUNT_TERMINATION_DATE,Sysdate+1)  AND  :p_cust_status='Inactive')
				  OR
				  (:p_cust_status='All')
			     )
			 AND bu.bu_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
									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 hp.party_id =100000029189015
--and a.account_number  in ('654321')
             ORDER BY bu.bu_name, hp.party_name, c.location