SELECT CHR.CONTRACT_NUMBER VND_CONTRACT_NUMBER
, HOU.NAME VND_OPERATING_UNIT
, CHR.START_DATE VND_START_DATE
, CHR.END_DATE VND_END_DATE
, APS_PARTY.VENDOR_NAME VND_PARTY_NAME
, FL.MEANING VND_PARTY_ROLE
, APS_VENDOR.VENDOR_NAME VND_VENDOR_NAME
, APS_PARTY.VENDOR_NAME VND_PARTY_VENDOR_NAME
FROM OKL_PARTY_PAYMENT_DTLS PPD
, OKC_K_PARTY_ROLES_B CPL
, OKC_K_HEADERS_ALL_B CHR
, AP_SUPPLIERS APS_PARTY
, AP_SUPPLIERS APS_VENDOR
, HR_OPERATING_UNITS HOU
, FND_LOOKUPS FL
WHERE CHR.ID = CPL.CHR_ID
AND PPD.CPL_ID = CPL.ID
AND CPL.RLE_CODE = 'OKL_VENDOR'
AND APS_PARTY.VENDOR_ID = CPL.OBJECT1_ID1
AND APS_VENDOR.VENDOR_ID = PPD.VENDOR_ID
AND PPD.VENDOR_ID <> APS_PARTY.VENDOR_ID
AND HOU.ORGANIZATION_ID = CHR.ORG_ID
AND MO_GLOBAL.CHECK_ACCESS(CHR.ORG_ID) = 'Y'
AND FL.LOOKUP_TYPE = 'OKC_ROLE'
AND FL.LOOKUP_CODE = CPL.RLE_CODE
AND CHR.ORG_ID = NVL(:P_OPERATING_UNIT,CHR.ORG_ID)
AND CHR.SCS_CODE = DECODE(NVL(:P_OBJECT_TYPE,'PROGRAM'),'PROGRAM','PROGRAM')
AND CHR.START_DATE BETWEEN NVL(:P_START_DATE_FROM, CHR.START_DATE) AND NVL(:P_START_DATE_TO, TO_DATE('31-12-9999','dd-mm-yyyy'))
UNION
SELECT CHR.CONTRACT_NUMBER VND_CONTRACT_NUMBER
, HOU.NAME VND_OPERATING_UNIT
, CHR.START_DATE VND_START_DATE
, CHR.END_DATE VND_END_DATE
, HZP.PARTY_NAME VND_PARTY_NAME
, FL.MEANING VND_PARTY_ROLE
, APS_VENDOR.VENDOR_NAME VND_VENDOR_NAME
, APS_PARTY.VENDOR_NAME VND_PARTY_VENDOR_NAME
FROM OKL_PARTY_PAYMENT_DTLS PPD
, OKC_K_PARTY_ROLES_B CPL
, OKC_K_HEADERS_ALL_B CHR
, AP_SUPPLIERS APS_PARTY
, AP_SUPPLIERS APS_VENDOR
, HZ_PARTIES HZP
, HR_OPERATING_UNITS HOU
, FND_LOOKUPS FL
WHERE CHR.ID = CPL.CHR_ID
AND PPD.CPL_ID = CPL.ID
AND CPL.RLE_CODE NOT IN ('OKL_VENDOR','LEASE HR')
AND PPD.VENDOR_ID = APS_VENDOR.VENDOR_ID
AND HZP.PARTY_ID = CPL.OBJECT1_ID1
AND APS_VENDOR.PARTY_ID <> HZP.PARTY_ID
AND APS_PARTY.PARTY_ID(+) = HZP.PARTY_ID
AND HOU.ORGANIZATION_ID = CHR.ORG_ID
AND MO_GLOBAL.CHECK_ACCESS(CHR.ORG_ID) = 'Y'
AND FL.LOOKUP_TYPE = 'OKC_ROLE'
AND FL.LOOKUP_CODE = CPL.RLE_CODE
AND CHR.ORG_ID = NVL(:P_OPERATING_UNIT,CHR.ORG_ID)
AND CHR.SCS_CODE = DECODE(NVL(:P_OBJECT_TYPE,'PROGRAM'),'PROGRAM','PROGRAM')
AND CHR.START_DATE BETWEEN NVL(:P_START_DATE_FROM, CHR.START_DATE) AND NVL(:P_START_DATE_TO, TO_DATE('31-12-9999','dd-mm-yyyy')) |