Key Indicators Payment Report

SELECT
PAYMENT_BATCH,
CREATED_BY,
PAYMENT_METHOD,
PAYMENT_STATUS,
INTERNAL_BANK_ACCOUNT,
PAYMENT_DATE,
PAYMENT_CURRENCY,
PAYMENT_AMOUNT,
SUPPLIER_NUMBER,
TRADING_PARTNER,
SUPPLIER_NAME,
SUPPLIER_TYPE,
PARTY_ID,
VENDOR_ID,
SUPPLIER_SITE_ID,
SUPPLIER_SITE,
LEGAL_ENTITY,
BUSINESS_UNIT,
--CREATION_DATE,
CREATION_PERIOD,
NO_OF_PAYMENTS,
CHECK_NUMBER,
REGION
FROM
(
SELECT 
	APC.CHECKRUN_NAME PAYMENT_BATCH,
	APC.CREATED_BY CREATED_BY,
	APC.PAYMENT_METHOD_CODE PAYMENT_METHOD,
	FLVSTA.MEANING PAYMENT_STATUS,
	APC.BANK_ACCOUNT_NAME INTERNAL_BANK_ACCOUNT,
	APC.CHECK_DATE PAYMENT_DATE,
	APC.CURRENCY_CODE PAYMENT_CURRENCY,
	APC.AMOUNT PAYMENT_AMOUNT,
	PSV.SEGMENT1 SUPPLIER_NUMBER,
	NVL(APC.REMIT_TO_SUPPLIER_NAME,HP.PARTY_NAME) TRADING_PARTNER,
	PSV.VENDOR_NAME SUPPLIER_NAME,		
	PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
	PSV.PARTY_ID,
	PSV.VENDOR_ID,
	NVL(PSSAM.ATTRIBUTE1,PSSV.VENDOR_SITE_ID) SUPPLIER_SITE_ID,
	PSSV.VENDOR_SITE_CODE SUPPLIER_SITE,
	XEP.NAME LEGAL_ENTITY,
	HOU.NAME BUSINESS_UNIT,
	--PAY_TBL.ACCOUNTING_DATE CREATION_DATE,
	PAY_TBL.PERIOD_NAME CREATION_PERIOD,
	COUNT(APC.CHECK_NUMBER) OVER (PARTITION BY APC.CHECK_DATE,PSSV.VENDOR_SITE_CODE,APC.CHECKRUN_NAME) NO_OF_PAYMENTS,
	APC.CHECK_NUMBER,
	FLV.DESCRIPTION REGION
FROM 
	AP_CHECKS_ALL APC,
	HZ_PARTIES 	  HP,
	POZ_SUPPLIERS_V PSV,
	POZ_SUPPLIER_SITES_V PSSV,
	XLE_ENTITY_PROFILES XEP,
	HR_OPERATING_UNITS HOU,
	--AP_INVOICE_PAYMENTS_ALL AIPA,
	FND_LOOKUP_VALUES_VL FLV,
	POZ_SUPPLIER_SITES_ALL_M PSSAM,
	GL_PERIODS GP,
	GL_LEDGERS GL,
	FND_LOOKUP_VALUES FLVSTA,
	(
	SELECT 
	DISTINCT
	AIPA.CHECK_ID,
	AIPA.SET_OF_BOOKS_ID,
	AIPA.PERIOD_NAME
	--AIPA.ACCOUNTING_DATE
	FROM
	AP_INVOICES_ALL AIA,
	AP_INVOICE_PAYMENTS_ALL AIPA
	WHERE 1=1
	AND AIA.INVOICE_ID=AIPA.INVOICE_ID

	) PAY_TBL
WHERE 
	1=1
	AND PSV.VENDOR_ID = APC.VENDOR_ID
	AND APC.party_id = HP.party_id(+)
	AND PSSV.VENDOR_ID = PSV.VENDOR_ID
	AND APC.VENDOR_SITE_ID = PSSV.VENDOR_SITE_ID
	AND XEP.LEGAL_ENTITY_ID = APC.LEGAL_ENTITY_ID
	AND APC.ORG_ID = HOU.ORGANIZATION_ID
	AND PAY_TBL.CHECK_ID = APC.CHECK_ID
	AND APC.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
	AND PSV.VENDOR_ID = PSSAM.VENDOR_ID
	AND SYSDATE BETWEEN PSSAM.EFFECTIVE_START_DATE AND PSSAM.EFFECTIVE_END_DATE
	AND FLV.LOOKUP_CODE = HOU.NAME
	AND FLV.LOOKUP_TYPE = 'BU_REGION_REPORTING'
	AND PAY_TBL.SET_OF_BOOKS_ID = GL.LEDGER_ID
	AND GL.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
	AND PAY_TBL.PERIOD_NAME = GP.PERIOD_NAME
	AND FLVSTA.LOOKUP_CODE(+)=APC.STATUS_LOOKUP_CODE
	AND FLVSTA.LOOKUP_TYPE(+)='CHECK STATE'
	AND FLVSTA.LANGUAGE(+) = USERENV('LANG') 

	--PARAMETERS
	AND GP.START_DATE >= (SELECT DISTINCT START_DATE FROM GL_PERIODS WHERE PERIOD_NAME = (:P_PERIOD_FROM))
	AND GP.END_DATE <= (SELECT DISTINCT END_DATE FROM GL_PERIODS WHERE PERIOD_NAME = (:P_PERIOD_TO))
	AND (PSV.VENDOR_TYPE_LOOKUP_CODE IN (:P_SUPPLIER_TYP) OR 'ALL' IN ('ALL'||:P_SUPPLIER_TYP))
	AND (FLV.DESCRIPTION IN (:P_REGION) OR 'ALL' IN ('ALL'||:P_REGION))
	AND (APC.ORG_ID IN (:P_BU) OR 'ALL' IN ('ALL'||:P_BU))
	AND (APC.LEGAL_ENTITY_ID IN (:P_LEGAL_ENTITY) OR 'ALL' IN ('ALL'||:P_LEGAL_ENTITY))	
	
	UNION ALL

SELECT
    APC.CHECKRUN_NAME PAYMENT_BATCH,
	APC.CREATED_BY CREATED_BY,
	APC.PAYMENT_METHOD_CODE PAYMENT_METHOD,
	FLVSTA.MEANING PAYMENT_STATUS,
	APC.BANK_ACCOUNT_NAME INTERNAL_BANK_ACCOUNT,
	APC.CHECK_DATE PAYMENT_DATE,
	APC.CURRENCY_CODE PAYMENT_CURRENCY,
	APC.AMOUNT PAYMENT_AMOUNT,
	HP.PARTY_NUMBER SUPPLIER_NUMBER,
	NVL(APC.REMIT_TO_SUPPLIER_NAME,HP.PARTY_NAME) TRADING_PARTNER,
	HP.PARTY_NAME SUPPLIER_NAME,	
	HP.PARTY_TYPE SUPPLIER_TYPE,
	HP.PARTY_ID,
	APC.VENDOR_ID,
	NULL SUPPLIER_SITE_ID,
	NULL SUPPLIER_SITE,
	XEP.NAME LEGAL_ENTITY,
	HOU.NAME BUSINESS_UNIT,
	--PAY_TBL.ACCOUNTING_DATE CREATION_DATE,
	PAY_TBL.PERIOD_NAME CREATION_PERIOD,
	COUNT(APC.CHECK_NUMBER) OVER (PARTITION BY APC.CHECK_DATE,APC.CHECKRUN_NAME) NO_OF_PAYMENTS,
	APC.CHECK_NUMBER,
	FLV.DESCRIPTION REGION

FROM 
	AP_CHECKS_ALL APC,
	HZ_PARTIES 	  HP,
	XLE_ENTITY_PROFILES XEP,
	HR_OPERATING_UNITS HOU,
	FND_LOOKUP_VALUES_VL FLV,
	GL_PERIODS GP,
	GL_LEDGERS GL,
	FND_LOOKUP_VALUES FLVSTA,	
	(
	SELECT 
	DISTINCT
	AIPA.CHECK_ID,
	AIPA.SET_OF_BOOKS_ID,
	AIPA.PERIOD_NAME
	--AIPA.ACCOUNTING_DATE
	FROM
	AP_INVOICES_ALL AIA,
	AP_INVOICE_PAYMENTS_ALL AIPA
	WHERE 1=1
	AND AIA.INVOICE_ID=AIPA.INVOICE_ID
	AND UPPER(AIA.INVOICE_TYPE_LOOKUP_CODE) IN ('PAYMENT REQUEST','REFUND')
	) PAY_TBL
WHERE 
	1=1
	
	AND APC.party_id = HP.party_id(+)
	AND XEP.LEGAL_ENTITY_ID = APC.LEGAL_ENTITY_ID
	AND APC.ORG_ID = HOU.ORGANIZATION_ID
	AND PAY_TBL.CHECK_ID = APC.CHECK_ID
	AND FLV.LOOKUP_CODE = HOU.NAME
	AND FLV.LOOKUP_TYPE = 'BU_REGION_REPORTING'
	AND PAY_TBL.SET_OF_BOOKS_ID = GL.LEDGER_ID
	AND GL.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
	AND PAY_TBL.PERIOD_NAME = GP.PERIOD_NAME
	AND FLVSTA.LOOKUP_CODE(+)=APC.STATUS_LOOKUP_CODE
	AND FLVSTA.LOOKUP_TYPE(+)='CHECK STATE'
	AND FLVSTA.LANGUAGE(+) = USERENV('LANG') 
	--PARAMETERS
	AND GP.START_DATE >= (SELECT DISTINCT START_DATE FROM GL_PERIODS WHERE PERIOD_NAME = (:P_PERIOD_FROM))
	AND GP.END_DATE <= (SELECT DISTINCT END_DATE FROM GL_PERIODS WHERE PERIOD_NAME = (:P_PERIOD_TO))
	AND (HP.PARTY_TYPE IN (:P_SUPPLIER_TYP) OR 'ALL' IN ('ALL'||:P_SUPPLIER_TYP))
	AND (FLV.DESCRIPTION IN (:P_REGION) OR 'ALL' IN ('ALL'||:P_REGION))
	AND (APC.ORG_ID IN (:P_BU) OR 'ALL' IN ('ALL'||:P_BU))
	AND (APC.LEGAL_ENTITY_ID IN (:P_LEGAL_ENTITY) OR 'ALL' IN ('ALL'||:P_LEGAL_ENTITY))	
)	
ORDER BY
SUPPLIER_NAME,
SUPPLIER_SITE,
CHECK_NUMBER
	--AND UPPER(AIPA.INVOICE_PAYMENT_TYPE) IN ('PAYMENT REQUEST','REFUND')
	--AND APC.CHECK_NUMBER IN ('868','863','2200','2201','2202')