AP Disbursements Report

SELECT  --APT.*
		AIL.ORG_ID BU_ID,
		HOUF.NAME OPERATING_UNIT,
		AIL.PAY_GROUP_LOOKUP_CODE,
		APSA.Payment_Method_Code PAYMENT_METHOD_LOOK_UP_CODE,
		AIL.INVOICE_NUM,
		AIL.INVOICE_DATE,
		AIL.INVOICE_RECEIVED_DATE,
		AIL.INVOICE_AMOUNT,
		--(NVL(AIL.INVOICE_AMOUNT,0) - NVL(AIL.AMOUNT_PAID,0)) AMOUNT_DUE_REMAINING,
		--NVL(AIL.AMOUNT_PAID,0) AMOUNT_PAID,
		APSA.AMOUNT_REMAINING AMOUNT_DUE_REMAINING,
		AIL.INVOICE_CURRENCY_CODE,
		AIL.GL_DATE ACCOUNTING_DATE,
		AIL.DESCRIPTION INVOICE_DESCRIPTION,
		--AIL.WFAPPROVAL_STATUS INVOICE_STATUS,
		XEP.NAME LEGAL_ENTITY,
		PSV.VENDOR_NAME SUPPLIER_NAME ,
		PSV.SEGMENT1 SUPPLIER_NUMBER,
		GL.CURRENCY_CODE Functional_Currency ,
		hzps.PARTY_SITE_NAME	SUPPLIER_SITE_NAME,
		PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE ,
		AIL.VENDOR_SITE_ID	,
		--AP Liability AccountACCTS_PAY_CODE_COMBINATION_ID
		PSSAM.PAY_GROUP_LOOKUP_CODE PAY_GROUP,
		PSSAM.Hold_All_Payments_Flag SUPPLIER_SITE_HOLD_ALL_INVOICES,
		PSSAM.Hold_Unmatched_Invoices_Flag SUPPLIER_SITE_HOLD_ALL_UNMATCHED_INVOICES,
		PSSAM.Hold_Reason SUPPLIER_SITE_HOLD_REASON,
		--NVL(AHA.Hold_Reason,'No') INVOICE_HOLD,
		CASE WHEN AIL.APPROVAL_STATUS='APPROVED' THEN NULL ELSE (SELECT HOLD_REASON FROM (SELECT
																	AHA.INVOICE_ID,
																	LISTAGG(distinct AHA.HOLD_REASON,',') WITHIN GROUP(ORDER BY AHA.INVOICE_ID) AS HOLD_REASON
																FROM
																	AP_HOLDS_ALL AHA
																WHERE AHA.INVOICE_ID=AIL.INVOICE_ID
																	AND AHA.RELEASE_REASON IS NULL
																GROUP BY
																	AHA.INVOICE_ID
																ORDER BY
																	AHA.INVOICE_ID)) END INVOICE_HOLD,
		APSA.HOLD_FLAG PAYMENT_HOLD,
		--(SELECT TEMPLATE_NAME FROM AP_PAYMENT_TEMPLATES WHERE VENDOR_ID=PSV.VENDOR_ID AND VENDOR_TYPE_LOOKUP_CODE = PSV.VENDOR_TYPE_LOOKUP_CODE	) TEMPLATE_NAME,
		APT.TEMPLATE_NAME TEMPLATE_NAME,
		ATV.NAME  SUP_PAYMENT_TERMS,
		ATL.NAME Invoice_TERMS_NAME,
		TRUNC(APSA.DUE_DATE) DUE_DATE,
		APSA.IBY_HOLD_REASON  PAYMENT_HOLD_REASON,
		--DECODE(APSA.PAYMENT_STATUS_FLAG,'Y','Paid','N','Unpaid') PAYMENT_STATUS,
		FLV.meaning PAYMENT_STATUS,
		--NVL(AIL.EXCHANGE_RATE,1) Exchange_Rate,
		(SELECT  DECODE(AIL.INVOICE_CURRENCY_CODE, 'USD', 1, GDR.CONVERSION_RATE)  FROM GL_DAILY_RATES GDR, GL_DAILY_CONVERSION_TYPES GCT
		WHERE UPPER(GCT.USER_CONVERSION_TYPE) = 'PERIOD END'
		AND GCT.CONVERSION_TYPE = GDR.CONVERSION_TYPE
		AND GDR.FROM_CURRENCY = AIL.INVOICE_CURRENCY_CODE
		AND GDR.TO_CURRENCY = 'USD'
		AND TO_CHAR(TRUNC(GDR.CONVERSION_DATE), 'DD-MON-YYYY') = TO_CHAR(LAST_DAY(AIL.GL_DATE), 'DD-MON-YYYY')
		) Exchange_Rate,
		(CASE WHEN AIL.INVOICE_CURRENCY_CODE = 'USD' THEN APSA.AMOUNT_REMAINING ELSE NVL(APSA.AMOUNT_REMAINING,0)*(SELECT GDR.CONVERSION_RATE  FROM GL_DAILY_RATES GDR, GL_DAILY_CONVERSION_TYPES GCT
		WHERE UPPER(GCT.USER_CONVERSION_TYPE) = 'PERIOD END'
		AND GCT.CONVERSION_TYPE = GDR.CONVERSION_TYPE
		AND GDR.FROM_CURRENCY = AIL.INVOICE_CURRENCY_CODE
		AND GDR.TO_CURRENCY = 'USD'
		AND TO_CHAR(TRUNC(GDR.CONVERSION_DATE), 'DD-MON-YYYY') =TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)), 'DD-MON-YYYY'))
		 END)
		 AMOUNT_DUE_REMAINING_USD,
		TO_CHAR(ADD_MONTHS(TRUNC(AIL.GL_DATE), -1), 'DD-MON-YYYY') Last_Day_GL_DT,
		LAST_DAY(AIL.GL_DATE) Last_Day_ACC_DT,
		APSA.PAYMENT_PRIORITY PAYMENT_PRIORITY,
		AISP.CHECKRUN_NAME,
		AC.PAYMENT_INSTRUCTION_ID CHECKRUN_ID,
		TRUNC(SYSDATE) - TRUNC(APSA.DUE_DATE) Days_Past_Due,
		(SELECT SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT6||'.'||SEGMENT6||'.'||SEGMENT7 FROM GL_CODE_COMBINATIONS GCC WHERE CODE_COMBINATION_ID = AIL.ACCTS_PAY_CODE_COMBINATION_ID) AP_LIABILITY_ACCOUNT,
		AISP.TEMPLATE_ID ,
		FLV_REG.DESCRIPTION REGION_NAME,
		(CASE WHEN DECODE (
				AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS (
				   AIL.INVOICE_ID,
				   AIL.INVOICE_AMOUNT,
				   AIL.PAYMENT_STATUS_FLAG,
				   AIL.INVOICE_TYPE_LOOKUP_CODE
				),
				'FULL',
				'Fully Applied',
				'NEVER APPROVED',
				'Never Validated',
				'NEEDS REAPPROVAL',
				'Needs Revalidation',
			
				'UNPAID',
				'Unpaid',
				'AVAILABLE',
				'Available',
				'UNAPPROVED',
				'Unvalidated',
				'APPROVED',
				'Validated',
				'PERMANENT',
				'Permanent Prepayment',
				NULL
			 ) <> 'Validated' THEN 'Unvalidated' ELSE 'Validated' END) INVOICE_STATUS,
		(CASE WHEN AIL.INVOICE_CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE  FROM GL_DAILY_RATES GDR, GL_DAILY_CONVERSION_TYPES GCT
		WHERE UPPER(GCT.USER_CONVERSION_TYPE) = 'PERIOD END'
		AND GCT.CONVERSION_TYPE = GDR.CONVERSION_TYPE
		AND GDR.FROM_CURRENCY = AIL.INVOICE_CURRENCY_CODE
		AND GDR.TO_CURRENCY = 'USD'
		AND TO_CHAR(TRUNC(GDR.CONVERSION_DATE), 'DD-MON-YYYY') =TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)), 'DD-MON-YYYY')) END) CONVERSION_EXCHANGE_RATE ,
		LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) CONVERSION_DATE
		

	FROM
		AP_INVOICES_ALL AIL,
		XLE_ENTITY_PROFILES XEP,
		POZ_SUPPLIERS PS,
		POZ_SUPPLIERS_V PSV,
		POZ_SUPPLIER_SITES_ALL_M PSSAM,
		GL_LEDGERS GL,
		HZ_PARTIES hzp,
		hz_party_sites hzps,
		AP_TERMS_B ATB,
		AP_TERMS_TL ATL,
		AP_PAYMENT_SCHEDULES_ALL APSA, --will duplicate if one invoice has multiple payments
		--ap_holds_all AHA, --will duplicate if one invoice has multiple holds
		AP_INV_SELECTION_CRITERIA_ALL AISP,
		AP_PAYMENT_TEMPLATES APT,
		HR_ORGANIZATION_UNITS_F_TL HOUF,
		FND_LOOKUP_VALUES FLV,
		FND_LOOKUP_VALUES_VL FLV_REG,
		AP_TERMS_V ATV,
		AP_INVOICE_PAYMENTS_ALL AIPA,
		AP_CHECKS_ALL AC

	WHERE
		1=1
		AND AIL.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
		--AND AIL.INVOICE_NUM = 'Test 02'
		AND PS.VENDOR_ID = AIL.VENDOR_ID
		AND PSV.VENDOR_ID = PS.VENDOR_ID
		AND GL.LEDGER_ID = AIL.SET_OF_BOOKS_ID
		AND hzp.PARTY_ID = AIL.PARTY_ID
		AND hzps.PARTY_SITE_ID = AIL.PARTY_SITE_ID
		AND PSSAM.VENDOR_ID = PSV.VENDOR_ID
		AND UPPER(PSV.VENDOR_TYPE_LOOKUP_CODE) NOT IN ('INTERCOMPANY', 'EDC')
		AND PSSAM.VENDOR_SITE_ID = AIL.VENDOR_SITE_ID
		AND PSSAM.TERMS_ID = ATV.TERM_ID(+)
		AND ATB.TERM_ID = AIL.TERMS_ID
		AND ATB.ENABLED_FLAG = 'Y'
		AND ATL.TERM_ID = ATB.TERM_ID
		AND ATL.LANGUAGE = USERENV('LANG')
		AND APSA.INVOICE_ID = AIL.INVOICE_ID
		--AND APSA.PAYMENT_STATUS_FLAG IN ('N','P')
		AND AIL.INVOICE_ID = AIPA.INVOICE_ID(+)
		AND AIPA.CHECK_ID  =AC.CHECK_ID(+)
		AND AC.VOID_DATE(+) IS NULL 
		AND NVL(AIPA.REVERSAL_FLAG(+),'N') <> 'Y'
		--AND AHA.INVOICE_ID(+) = AIL.INVOICE_ID
		AND APSA.CHECKRUN_ID = AISP.CHECKRUN_ID(+)
		AND APT.TEMPLATE_ID(+) = AISP.TEMPLATE_ID
		AND AIL.ORG_ID          = HOUF.ORGANIZATION_ID
		AND FLV_REG.LOOKUP_CODE = HOUF.NAME
		AND FLV_REG.LOOKUP_TYPE = 'BU_REGION_REPORTING'
		AND HOUF.LANGUAGE = USERENV('LANG')
		AND AIL.PAYMENT_STATUS_FLAG <> 'Y'
		AND NVL(APSA.PAYMENT_STATUS_FLAG,'N') <>'Y'
		AND AIL.APPROVAL_STATUS <> 'CANCELLED'
		AND (FLV_REG.DESCRIPTION IN (:P_REGION) OR 'All' IN ('All'||:P_REGION))
		AND (PSSAM.PAY_GROUP_LOOKUP_CODE IN (:P_PAY_GROUP) OR 'All' IN ('All'||:P_PAY_GROUP))
		AND (AISP.CHECKRUN_NAME IN (:P_CHECKRUN) OR 'All' IN ('All'||:P_CHECKRUN))
		AND (APT.TEMPLATE_NAME IN (:P_TEMPLATE) OR 'All' IN ('All'||:P_TEMPLATE))
		AND (AIL.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
		AND (APSA.Payment_Method_Code IN (:P_PAY_METHOD) OR 'All' IN ('All'||:P_PAY_METHOD))
		AND (FLV.MEANING IN (:P_PAY_STATUS) OR 'All' IN ('All'||:P_PAY_STATUS))
		AND TRUNC(APSA.DUE_DATE) BETWEEN NVL(:P_DUE_START,TRUNC(APSA.DUE_DATE)) AND NVL(:P_DUE_END,TRUNC(APSA.DUE_DATE))
		AND (PSV.VENDOR_TYPE_LOOKUP_CODE IN (:P_SUP_TYPE) OR 'All' IN ('All'||:P_SUP_TYPE))
		AND ((CASE WHEN DECODE (
				AP_INVOICES_UTILITY_PKG.GET_APPROVAL_STATUS (
				   AIL.INVOICE_ID,
				   AIL.INVOICE_AMOUNT,
				   AIL.PAYMENT_STATUS_FLAG,
				   AIL.INVOICE_TYPE_LOOKUP_CODE
				),
				'FULL',
				'Fully Applied',
				'NEVER APPROVED',
				'Never Validated',
				'NEEDS REAPPROVAL',
				'Needs Revalidation',
				'UNPAID',
				'Unpaid',
				'AVAILABLE',
				'Available',
				'UNAPPROVED',
				'Unvalidated',
				'APPROVED',
				'Validated',
				'PERMANENT',
				'Permanent Prepayment',
				NULL
			 ) <> 'Validated' THEN 'Unvalidated' ELSE 'Validated' END) IN (:P_INV_STATUS) OR 'All' IN ('All'||:P_INV_STATUS))
		AND AIL.PAYMENT_STATUS_FLAG		= FLV.lookup_code
		AND FLV.lookup_type 				= 'INVOICE PAYMENT STATUS'
		AND FLV.language					= 'US'
	ORDER BY AIL.INVOICE_NUM