SELECT
reg.REQUESTER_PERSON_ID as Key,
reg.SUPPLIER_NAME AS Company_Name,
reg.request_number AS Registration_Request,
to_char(
reg.requested_date, 'mm/dd/yyyy'
) AS Registration_Date,
initcap(reg.registration_status) AS Approval_Decision,
to_char(reg.Approved_date, 'mm/dd/yyyy') as Registration_Approval_or_Rejection_Date,
reg.supplier_number as vendor_id,
st.vendor_site_code site_id,
reg.reject_comments as Rejection_Comments
FROM
poz_supplier_registrations reg
LEFT JOIN poz_supplier_sites_all_m st ON st.vendor_id = reg.vendor_id
WHERE
(
(
reg.registration_status = 'APPROVED'
and (
(
: p_from_date is not null
and reg.Approved_date >= TO_DATE(
: p_from_date, 'RRRR-MM-DD"T"HH24:MI:SS'
)
)
OR (
: p_from_date is null
and reg.Approved_date >= TO_DATE(
(
select
nvl(
max(erh.processend),
'2001-01-01'
)
from
ess_request_history erh,
fnd_lookup_values flv
where
1 = 1
and flv.lookup_type = 'ORA_EGP_ESS_REQUEST_STATUS'
and flv.lookup_code = erh.state
and upper(erh.definition) = 'JOBDEFINITION://ORACLE/APPS/ESS/CUSTOM/SHARED/IRB_I416_SUPPLIER_REG_APPROVAL'
and upper(flv.meaning)= 'SUCCEEDED'
),
'RRRR-MM-DD"T"HH24:MI:SS'
)
)
)
and (
(
: p_to_date is null
and reg.Approved_date <= TO_DATE(
TO_CHAR(
sysdate, 'RRRR-MM-DD"T"HH24:MI:SS'
),
'RRRR-MM-DD"T"HH24:MI:SS'
)+ 1
)
OR (
: p_to_date is not null
and TO_DATE(
: p_to_date, 'RRRR-MM-DD"T"HH24:MI:SS'
)+ 1
)
)
)
or (
reg.registration_status = 'REJECTED'
and (
(
: p_from_date is not null
and reg.last_update_date >= TO_DATE(
: p_from_date, 'RRRR-MM-DD"T"HH24:MI:SS'
)
)
OR (
: p_from_date is null
and reg.last_update_date >= TO_DATE(
(
select
nvl(
max(erh.processend),
'2001-01-01'
)
from
ess_request_history erh,
fnd_lookup_values flv
where
1 = 1
and flv.lookup_type = 'ORA_EGP_ESS_REQUEST_STATUS'
and flv.lookup_code = erh.state
and upper(erh.definition) = 'JOBDEFINITION://ORACLE/APPS/ESS/CUSTOM/SHARED/IRB_I416_SUPPLIER_REG_APPROVAL'
and upper(flv.meaning)= 'SUCCEEDED'
),
'RRRR-MM-DD"T"HH24:MI:SS'
)
)
)
and (
(
: p_to_date is null
and reg.last_update_date <= TO_DATE(
TO_CHAR(
sysdate, 'RRRR-MM-DD"T"HH24:MI:SS'
),
'RRRR-MM-DD"T"HH24:MI:SS'
)+ 1
)
OR (
: p_to_date is not null
and TO_DATE(
: p_to_date, 'RRRR-MM-DD"T"HH24:MI:SS'
)+ 1
)
)
)
)
UNION ALL
SELECT
1 AS KEY,
NULL AS Company_Name,
NULL AS Registration_Request,
NULL AS Registration_Date,
NULL AS Approval_Decision,
NULL AS Registration_Approval_or_Rejection_Date,
NULL AS vendor_id,
NULL as site_id,
NULL AS Registration_comment
FROM
dual
order by
1 desc