test stored produre in database

create or replace PROCEDURE               SP_SEE(p_cursor   OUT   Pk_Test.cursortype) AS

BEGIN
OPEN p_cursor FOR
Select y.ref_num "SP Number"
,x.fyi_application_no "SEE Tracking Number"
--  ,x.sys_author
,case when x.sys_life_cycle = 'W' then 'W' Else '' End "SEE In Management"
,case when x.sys_life_cycle = 'L' then 'L' Else '' End "SEE In Library"
,x.fyi_project_officer PO
,x.sys_author "Company Name"
from
(Select distinct substr(reference_number,3) ref_num from ohma_agl.sp_agl_web) y,
(Select f.fyi_reference_id
,f.fyi_application_no
,sd.sys_author
,sd.sys_life_cycle
,f.fyi_folder_type
,f.fyi_project_officer
from fyiadm.fyi_xmpt_applic_f f
,fyiadm.fyi_sysdata sd
where f.sys_fkey = sd.sys_fkey
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and (lower(sd.sys_author) like '%safety equi%' or sd.sys_author like '%SEE%')
and f.fyi_folder_type = 'General Correspondence') x
--lower(sd.sys_author) like 'safety eq%')  x
where y.ref_num = x.fyi_reference_id(+);
/*Select distinct f.fyi_application_no
,f.fyi_reference_id
,sd.sys_author
,f.fyi_folder_type
,f.fyi_disposition
,f.fyi_project_officer
,f.FYI_APPLICATION_DATE
,wl.FYI_MEMBERID
,f.fyi_project_status
,sd.sys_date_created
,CASE WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 60 AND 120 THEN '(2) 60-120'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 121 AND 180 THEN '(3) 121-180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') > 180 THEN '(4) >180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') < 60 THEN '(1) <60'
ELSE 'Not categorized'
END   Aging
from fyiadm.FYI_XMPT_APPLIC_F f
,fyiadm.fyi_sysdata sd
,fyiadm.fyi_workflow wf
,fyiadm.fyi_wfworklist wl
where sd.sys_fkey = f.sys_fkey
and wf.sys_wf_fkey = f.sys_fkey
and wf.sys_wf_skey = wl.FYI_SKEY
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and sd.sys_life_cycle = 'W'
and wf.SYS_WF_PROCNAME = 'Exemption Process'
AND wl.fyi_status IN ('New', 'Open', 'Subprocess', 'Complete')
and wl.FYI_MEMBERID not like 'XMPT%'
and sd.sys_date_created <= to_char(trunc(sysdate),'yyyy-mm-dd')
and lower(sd.sys_author) like 'safety eq%'
union
Select distinct f.fyi_application_no
,f.fyi_reference_id
,sd.sys_author
,f.fyi_folder_type
,f.fyi_disposition
,f.fyi_project_officer
,f.FYI_APPLICATION_DATE
,'No Member Id'
,f.fyi_project_status
,sd.sys_date_created
,CASE WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 60 AND 120 THEN '(2) 60-120'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') BETWEEN 121 AND 180 THEN '(3) 121-180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') > 180 THEN '(4) >180'
WHEN trunc(sysdate - 0) - to_date(sd.sys_date_created,'yyyy-mm-dd') < 60 THEN '(1) <60'
ELSE 'Not categorized'
END   Aging
from fyiadm.FYI_XMPT_APPLIC_F f
,fyiadm.fyi_sysdata sd
,fyiadm.fyi_workflow wf
where sd.sys_fkey = f.sys_fkey
and wf.sys_wf_fkey = f.sys_fkey
and sd.sys_dkey = 0
and sd.sys_delete_date is null
and sd.sys_life_cycle = 'W'
and sd.sys_date_created <= to_char(trunc(sysdate),'yyyy-mm-dd')
and wf.sys_wf_status <> 'Active'
and lower(sd.sys_author) like 'safety eq%';*/

END;

Select allOpen in new window