SELECT
		TRUNC(f.dateacct) 									AS dateacct,
		TRUNC(f.created) 									AS created,
	
	        /*CASE WHEN f.c_doctype_id IS NOT NULL THEN
			(SELECT MIN(printname)
				FROM c_doctype
	                        WHERE c_doctype_id = f.c_doctype_id)
	        ELSE
			(SELECT MIN(name)
				FROM ad_ref_list
				WHERE ad_reference_id = '183'
				AND value = f.docbasetype)
	        END 											AS documenttype, */
	
	        NULL											AS documenttype,
	
	        COALESCE('[INV]~' || i.documentno || '  ' || COALESCE(i.description, ' '),
	        '[PMT]~' || i2.documentno || '  ' || COALESCE(i2.description, ' '),
	        '[PMT]~' || fft.description,
	        '[PMT]~' || fp.documentno || '  ' || COALESCE(fp.description, ' '),
	        '[   ]~' || COALESCE(f.description, ' '))						AS description,
	
	
		f.amtacctdr 										AS debit,
		f.amtacctcr 										AS credit,
		(f.amtacctdr - f.amtacctcr) 								AS net,
	
		(SELECT
			CONCAT(
				COALESCE(c_location.address1, ''), ' ', COALESCE(c_location.address2, '')
				,'~' , COALESCE(c_location.city, ' ')
				,'~' , COALESCE(c_region.name, ' ')
				,'~' , COALESCE(c_location.postal, ' ')
				)
		FROM c_bpartner
		LEFT JOIN c_bpartner_location 	ON (c_bpartner.c_bpartner_id 		= c_bpartner_location.c_bpartner_id)
		LEFT JOIN c_location 		ON (c_bpartner_location.c_location_id 	= c_location.c_location_id)
		LEFT JOIN c_region 		ON (c_location.c_region_id 		= c_region.c_region_id)
	
		WHERE c_bpartner.c_bpartner_id 		= '4B90BC2D3F864310B3E5D8CD47695635'
		AND c_bpartner_location.isactive 	= 'Y'
		AND c_bpartner_location.IsBillTo	= 'Y'
		LIMIT 1) 										AS to_Info,
	
		bp.name2 										AS to_legalname,
		bp.name 										AS bpname,
		o.ad_org_id 										AS organizationid,
		o.Social_Name 										AS org_legalname,
		loc.address1 || ' ' || COALESCE(TO_CHAR(loc.address2), TO_CHAR('')) 			AS org_address,
	
		loc.city || ', ' ||
		ad_column_identifier('C_Region', loc.c_region_id, 'en_US') || ' ' ||
		loc.postal  										AS org_postal,
	
		(SELECT
			CONCAT(
				'Phone : ', COALESCE(c_bpartner_location.phone, '')
				,'~Fax : ', COALESCE(c_bpartner_location.fax, '')
				,'~Email : ', COALESCE(ad_user.email, '')
				)
		FROM c_bpartner
		LEFT JOIN ad_user 		ON (c_bpartner.c_bpartner_id 		= ad_user.c_bpartner_id)
		LEFT JOIN c_bpartner_location 	ON (c_bpartner.c_bpartner_id 		= c_bpartner_location.c_bpartner_id)
	
		WHERE c_bpartner.c_bpartner_id 		= oinfo.c_bpartner_id
		AND ad_user.isactive 			= 'Y'
		AND ad_user.em_cdai_recieveinvoice 	= 'Y'
		AND c_bpartner_location.isactive 	= 'Y'
		AND c_bpartner_location.IsBillTo	= 'Y'
		LIMIT 1) 										AS org_Info,
	
		(SELECT MAX(email)
			FROM ad_user
			WHERE ad_user_id = oinfo.ad_user_id) 						AS email,
	
		(SELECT MAX(phone)
			FROM ad_user
			WHERE ad_user_id = oinfo.ad_user_id) 						AS phone,
	
		issotrx.issotrx										AS issotrx,
		MSCF_CUST_VEND_OPEN_BAL('428125A1CFA94E27829FF69A54D8E040', '4B90BC2D3F864310B3E5D8CD47695635', 'A73ACFCC9789412392625CE75FB6CE52', 'cus') 	AS OPEN_BAL_CUS,
		MSCF_CUST_VEND_OPEN_BAL('428125A1CFA94E27829FF69A54D8E040', '4B90BC2D3F864310B3E5D8CD47695635', 'A73ACFCC9789412392625CE75FB6CE52', 'ven') 	AS OPEN_BAL_VEN
	
	FROM fact_acct f
	LEFT OUTER JOIN c_acctschema acct 		ON (f.c_acctschema_id 	= acct.c_acctschema_id)
	LEFT OUTER JOIN c_bpartner bp 			ON (f.c_bpartner_id 	= bp.c_bpartner_id)
	LEFT OUTER JOIN c_invoice i 			ON (f.record_id 	= i.c_invoice_id)
	LEFT OUTER JOIN fin_payment fp 			ON (f.record_id 	= fp.fin_payment_id)
	LEFT OUTER JOIN fin_finacc_transaction fft 	ON (f.record_id 	= fft.fin_finacc_transaction_id)
	LEFT OUTER JOIN fin_payment_schedule ps 	ON (f.record_id2 	= ps.fin_payment_schedule_id)
	LEFT OUTER JOIN c_invoice i2 			ON (ps.c_invoice_id 	= i2.c_invoice_id)
	
	JOIN
	
	(SELECT ad_org_id
		FROM ad_org
		WHERE ad_isorgincluded(ad_org_id, '428125A1CFA94E27829FF69A54D8E040', ad_client_id) <> -1) org ON (f.ad_org_id = org.ad_org_id)
	
	, ad_org o
	
	LEFT JOIN ad_orginfo oinfo 	ON (o.ad_org_id		= oinfo.ad_org_id)
	LEFT JOIN c_location loc 	ON (oinfo.c_location_id	= loc.c_location_id)
	
	, (SELECT CASE WHEN value = 1 THEN 'Y'
			ELSE 'N' END AS issotrx
		FROM ad_integer
		WHERE value < 3) issotrx
	
	WHERE o.ad_org_id = '428125A1CFA94E27829FF69A54D8E040'
	AND (
		('cus' IN ('cus','both')
		AND account_id IN (SELECT account_id
					FROM c_validcombination
					WHERE c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52'
					AND (c_validcombination_id 	IN (SELECT c_receivable_acct
										FROM c_bp_customer_acct
										WHERE c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'
										AND c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52')
					OR c_validcombination_id	IN (SELECT c_prepayment_acct
										FROM c_bp_customer_acct
										WHERE c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'
										AND c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52')))
		AND issotrx.issotrx = 'Y')
	
		OR
	
		('cus' IN ('ven','both')
		AND account_id IN (SELECT account_id
					FROM c_validcombination
					WHERE c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52'
					AND (c_validcombination_id 		IN (SELECT v_liability_acct
											FROM c_bp_vendor_acct
											WHERE c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'
											AND c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52')
						OR c_validcombination_id 	IN (SELECT v_prepayment_acct
											FROM c_bp_vendor_acct
											WHERE c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'
											AND c_acctschema_id = 'A73ACFCC9789412392625CE75FB6CE52')))
		AND issotrx.issotrx = 'N')
		)
	
	AND f.c_bpartner_id 	=  '4B90BC2D3F864310B3E5D8CD47695635'
	AND f.c_acctschema_id 	= 'A73ACFCC9789412392625CE75FB6CE52'
	
	AND trunc(f.dateacct) 	>= (CASE WHEN (NULL IS NULL OR NULL='') THEN TO_DATE('01-01-0001')
					ELSE TO_DATE(NULL) END)
	
	AND trunc(f.dateacct) 	<= (CASE WHEN (NULL IS NULL OR NULL='') 	THEN TO_DATE('09-09-9999')
					ELSE TO_DATE(NULL) END)
	
	AND f.ad_table_id IN ('318','D1A97202E832470285C9B1EB026D54E2','4D8C3B3C31D1410DA046140C9F024D17','B1B7075C46934F0A9FD4C4D0F1457B42')
	
	ORDER BY
	issotrx.issotrx,
	f.dateacct,
	f.created