Supplier Detail Report

WITH BANK_DETAILS AS
(
SELECT
			IPI.ext_pmt_party_id
		,	CBB.bank_name				 	SITE_BANK_NAME
        ,	CBB.bank_number				 	SITE_BANK_NUMBER
		,	CBB.bank_branch_name		 	SITE_BRANCH_NAME
        ,	CBB.branch_number			 	SITE_BRANCH_NUMBER
		,	IEB.masked_bank_account_num	 	SITE_BANK_ACC_NUM
		,	IEB.end_date					END_DATE
FROM
			iby_pmt_instr_uses_all 	   		IPI
        ,   iby_ext_bank_accounts 	   		IEB
		,	ce_bank_branches_v 		   		CBB
	
WHERE
			1							=1
		AND	IPI.instrument_id			=IEB.ext_bank_account_id
        AND IEB.bank_id 	   			=CBB.bank_party_id
        AND IEB.branch_id 				=CBB.branch_party_id
		AND SYSDATE BETWEEN IPI.start_date AND IPI.end_date
)

SELECT 
			HOU.name											ORG_NAME
		,	PSV.vendor_name										SUPPLIER_NAME
		,	PSV.vendor_name_alt 								SUPPLIER_NAME_ALT
		,	PSV.parent_vendor_id 								PARENT_VENDOR_ID
		,	PSV.segment1 										SUPPLIER_NUMBER
		,	PSV.vendor_id 										VENDOR_ID
		,	PSSAM.VENDOR_SITE_CODE 								VENDOR_SITE_NAME
		,	PSSAM.vendor_site_id 								VENDOR_SITE_ID
		,	ZPTA.rep_registration_number 						TAX_REGISTRATION_NUM
		,	PSV.vendor_type_lookup_code 						SUPPLIER_TYPE
		,	TO_CHAR(PSV.creation_date,'MM/DD/YYYY') 			CREATION_DATE
		,	TO_CHAR(PSV.last_update_date,'MM/DD/YYYY') 			UPDATE_DATE
		,	PSP.income_tax_id 									TAXPAYER_ID
		,	TO_CHAR(PSV.tax_verification_date,'MM/DD/YYYY') 	VERIFICATION_DATE
		,	PSV.tax_reporting_name 								TAX_REPORTING_NAME
		,	PSV.type_1099 										INCOME_TAX_TYPE
		,	IEPPM.payment_method_code 							SUPPLIER_PAYMENT_METHOD
		,	PSSAM.vat_registration_num 							SITE_TAX_REGISTRATION_NUM
		,	ZPTA.tax_classification_code 						SITE_TAX_CLASS_CODE
		,	HL.address1 										ADDRESS_LINE1
		,	HL.address2 										ADDRESS_LINE2
		,	HL.address3 										ADDRESS_LINE3
		,	HL.city 											CITY
		,	HL.state 											S_STATE
		,	HL.postal_code 										ZIP
		,	HL.country 											COUNTRY
		,	HL.state ||' '|| HL.postal_code ||' '|| HL.country 	STATE_ZIP_COUNTRY
		,	PSSAM.telex 										SITE_TELEPHONE
		,	PSSAM.fax 											SITE_FAX
		,	PSSAM.email_address 								SITE_EMAIL_ADDRESS
		,	ATT.name 											PAYMENTS_TERM
		,	IEPPM_SITE.payment_method_code 						SUPPLIER_SITE_PAYMENT_METHOD
		,	PSSAM.invoice_currency_code 						SITE_INVOICE_CURRENCY
		,	PSSAM.payment_currency_code 						SITE_PAYMENT_CURRENCY
		,	TO_CHAR(PSSAM.creation_date,'MM/DD/YYYY') 			SITE_CREATION_DATE
		,	TO_CHAR(PSSAM.last_update_date,'MM/DD/YYYY') 		SITE_UPDATE_DATE
		,	PSSAM.pay_group_lookup_code 						PAY_GROUP
		,	PSSAM.payment_priority 								PAYMENT_PRIORITY
		,	IEPA_SITE.remit_advice_email						SITE_REMIT_EMAIL
		,	CASE WHEN PURCHASING_SITE_FLAG='Y' THEN 'Purchasing Site' END||CASE WHEN PAY_SITE_FLAG='Y' THEN ',' END||
			CASE WHEN PAY_SITE_FLAG='Y' THEN 'Pay Site' END||CASE WHEN PRIMARY_PAY_SITE_FLAG='Y' THEN ',' END||
			CASE WHEN PRIMARY_PAY_SITE_FLAG='Y' THEN 'Primary Pay Site' END||CASE WHEN PCARD_SITE_FLAG='Y' THEN ',' END||
			CASE WHEN PCARD_SITE_FLAG='Y' THEN 'Procurement card Site' END||CASE WHEN RFQ_ONLY_SITE_FLAG='Y' THEN ',' END||
			CASE WHEN RFQ_ONLY_SITE_FLAG='Y' THEN 'Sourcing Only Site' END		SITE_USES
		,	IEPA_SITE.exclusive_payment_flag 					PAY_ALONE
		,	PSSAM.hold_unmatched_invoices_flag 					HOLD_UNMATCHED_INVOICES
		,	PSSAM.hold_future_payments_flag 					HOLD_UNVALIDATED_INVOICES
		,	PSSAM.hold_all_payments_flag 						HOLD_ALL_PAYMENTS
		,	PSSAM.ATTRIBUTE5 									COUPA_ENABLED_SITE
		,	CASE WHEN PSSAM.inactive_date IS NULL THEN 'Y'
            ELSE
                 CASE WHEN PSSAM.inactive_date > (TO_DATE(SYSDATE,'YYYY-MM-DD')+1) THEN 'Y'
             ELSE 'N' END END				 					INACTIVE_IN_COUPA
		,	CASE WHEN PSV.end_date_active IS NULL THEN 'Active'
            ELSE
                 CASE WHEN PSV.end_date_active > (TO_DATE(SYSDATE,'YYYY-MM-DD')+1) THEN 'Active'
             ELSE 'Inactive' END END							SUPPLIER_STATUS
		,	TO_CHAR(PSV.end_date_active,'MM/DD/YYYY') 			SUPPLIER_END_DATE
		,	CASE WHEN PSSAM.inactive_date IS NULL THEN 'Active'
            ELSE
                 CASE WHEN PSSAM.inactive_date > (TO_DATE(SYSDATE,'YYYY-MM-DD')+1) THEN 'Active'
             ELSE 'Inactive' END END							SUPPLIER_SITE_STATUS
		,	TO_CHAR(PSSAM.inactive_date,'MM/DD/YYYY') 			SITE_END_DATE
		,	GCC.CONCATENATED_SEGMENTS 							SITE_LIABILITY_ACCOUNT
		/*,	(
				SELECT 
						distinct description 
				FROM 
						fnd_vs_typed_values_vl 
				WHERE
						value = GCC.segment4
			) 													SITE_LIA_ACC_DESC*/
		,   acc_desc.description SITE_LIA_ACC_DESC
		,	PSSAM.bank_charge_bearer 							BANK_CHARGES
		,	BD.SITE_BANK_NAME
        ,	BD.SITE_BANK_NUMBER
		,	BD.SITE_BRANCH_NAME
        ,	BD.SITE_BRANCH_NUMBER
		,	BD.SITE_BANK_ACC_NUM
		
FROM 
			hr_operating_units						HOU
		,	poz_suppliers_v 						PSV
		,	poz_supplier_sites_all_m 				PSSAM
		,	zx_party_tax_profile 					ZPTA
		,	poz_suppliers_pii 						PSP
		,	hz_parties 								HP
		,	iby_external_payees_all 				IEPA
		,	iby_ext_party_pmt_mthds 				IEPPM
		,	hz_locations							HL
		,	ap_terms_tl 							ATT	
		,	poz_site_assignments_all_m				PSAAM
		,	gl_code_combinations 					GCC
		,	iby_external_payees_all 				IEPA_SITE
		,	iby_ext_party_pmt_mthds 				IEPPM_SITE
		,	bank_details							BD
		, (SELECT DISTINCT 
         gcc.CODE_COMBINATION_ID,
		 gcc.segment4,
         ffv.description
      FROM gl_code_combinations gcc,
           fnd_flex_values_vl ffv,
           fnd_id_flex_segments ffs
      WHERE gcc.segment4 = ffv.flex_value
        AND ffv.flex_value_set_id = ffs.flex_value_set_id
        AND ffs.application_column_name = 'SEGMENT4'
        AND ffs.id_flex_code = 'GL#') acc_desc
	
WHERE
			1										=1
		AND	PSV.vendor_id 							=PSSAM.vendor_id
		AND PSSAM.prc_bu_id							=HOU.organization_id
		AND PSV.vendor_id							=PSP.vendor_id
		AND PSV.party_id							=HP.party_id 
		AND HP.iden_addr_party_site_id				=ZPTA.party_id(+)
		AND PSV.party_id							=IEPA.payee_party_id(+)
		AND (IEPA.SUPPLIER_SITE_ID(+) IS NULL) 
		AND (IEPA.PAYEE_PARTY_ID(+) IS NOT NULL) 
		AND (IEPA.PARTY_SITE_ID(+) IS NULL)
		AND IEPA.ext_payee_id						=IEPPM.ext_pmt_party_id(+)
		AND SYSDATE BETWEEN IEPPM.start_date(+) AND IEPPM.end_date(+)
		--AND IEPPM.primary_flag(+)					='Y'
		AND PSSAM.location_id						=HL.location_id
		AND PSSAM.terms_id							=ATT.term_id(+)
		AND	ATT.language(+)							=userenv('lang')
		AND PSSAM.vendor_site_id					=PSAAM.vendor_site_id(+)
		AND PSAAM.accts_pay_code_combination_id 	=GCC.code_combination_id(+)
		AND PSAAM.accts_pay_code_combination_id 	=acc_desc.code_combination_id(+)
		AND PSSAM.vendor_site_id					=IEPA_SITE.supplier_site_id(+)
		AND IEPA_SITE.ext_payee_id  				=IEPPM_SITE.ext_pmt_party_id(+)
		--AND IEPPM_SITE.primary_flag	='Y'
		AND SYSDATE BETWEEN IEPPM_SITE.start_date(+) AND IEPPM_SITE.end_date(+)
		AND IEPA_SITE.ext_payee_id   				=BD.ext_pmt_party_id(+)
		AND (PSV.vendor_name IN (:P_SUPPLIER_NAME) OR LEAST (:P_SUPPLIER_NAME) IS NULL)
		AND (PSV.segment1	 IN (:P_SUPPLIER_NUM) OR LEAST (:P_SUPPLIER_NUM) IS NULL)
		AND TRUNC(PSV.creation_date) BETWEEN NVL(:P_CREATION_DATE_FROM, TRUNC(PSV.creation_date)) AND NVL(:P_CREATION_DATE_TO, TRUNC(PSV.creation_date))
		AND ((CASE WHEN PSV.end_date_active IS NULL THEN 'Active'
            ELSE
                 CASE WHEN PSV.end_date_active > (TO_DATE(SYSDATE,'YYYY-MM-DD')+1) THEN 'Active'
             ELSE 'Inactive' END END)		IN (:P_STATUS) OR LEAST (:P_STATUS) IS NULL)
		AND (HOU.name 	IN (:P_PRC_BU) OR LEAST (:P_PRC_BU) IS NULL)
		AND (PSSAM.attribute5						IN (:P_SRC_SYS_FLAG) OR LEAST (:P_SRC_SYS_FLAG) IS NULL)
     	--AND PSV.vendor_name							='SALLE DEVELOPMENTS LTD'