WITH e AS
(
Select* from enrollment
Where effective_end_dt IS NULL
),
h AS
(
Select* from member_status_history
Where effective_end_dt IS NULL
)
Select Distinct
(m.ssn),
d.department_cd,
m.last_nm,
e.plan_id,
p.plan_cd,
S.enrollmemt_status_desc
From
member m,
e,
h,
PLAN p,
enrollment_status_s,
department d
Where
m.member_id =e.member_id
AND
m.member_id = h.member_id
AND
e.plan_id = p.plan_id
AND
e.enrollment_status_id =s.enrollment_status_id
AND
s.enrollment_status_id='510'
AND
m.department_id=d.department_id
AND
h.member_status='530'
AND
e.plan_id IN (307,308,309)
AND
NOT EXISTS
(
SELECT t.member_id
From transaction_register t
Where m.member_id = t.member_id
AND t.plan_id = e.plan_id
AND t.update_dt >= TO_DATE('01/11/2016 00:00:00', 'mm/DD/yyyy hh24:mi:ss')
AND t.update_dt <= TO_DATE('01/12/2016 23:59:59', 'mm/DD/yyyy hh24:mi:ss')
AND t.transaction_type_cd='PAYDE'
AND t.update_user_id='PAYPGM'
)
Order By
e.plan_id,
ssn
What I have tried:
I have created equivalent stored procedure but it seems wrong can anyone help me out, what will be the equivalent stored procedure for above query.
CREATE OR REPLACE Procedure SP_GET_UNMATCHED_MEMBER
BEGIN
WITH e AS
(
Select* from enrollment
Where effective_end_dt IS NULL
),
h AS
(
Select* from member_status_history
Where effective_end_dt IS NULL
)
Select Distinct
(m.ssn),
d.department_cd,
m.last_nm,
e.plan_id,
p.plan_cd,
S.enrollmemt_status_desc
From
member m,
e,
h,
PLAN p,
enrollment_status_s,
department d
Where
m.member_id =e.member_id
AND
m.member_id = h.member_id
AND
e.plan_id = p.plan_id
AND
e.enrollment_status_id =s.enrollment_status_id
AND
s.enrollment_status_id='510'
AND
m.department_id=d.department_id
AND
h.member_status='530'
AND
e.plan_id IN (307,308,309)
AND
NOT EXISTS
(
SELECT t.member_id
From transaction_register t
Where m.member_id = t.member_id
AND t.plan_id = e.plan_id
AND t.update_dt >= TO_DATE('01/11/2016 00:00:00', 'mm/DD/yyyy hh24:mi:ss')
AND t.update_dt <= TO_DATE('01/12/2016 23:59:59', 'mm/DD/yyyy hh24:mi:ss')
AND t.transaction_type_cd='PAYDE'
AND t.update_user_id='PAYPGM'
)
Order By
e.plan_id,
ssn
END;