Here is the code.
p_id number,
p_mc varchar2
Both of the above parameters will be passed in during a procedure call.
EXECUTE IMMEDIATE 'INSERT INTO COUNT_MASTER_TEMP ' || 'SELECT COUNT (ar.'|| p_mc || ')' ||
'
FROM app_recipient ar
WHERE EXISTS (SELECT r.' || p_mc ||
' FROM app_recipient r
WHERE r.ID =' || p_id || ' AND ar.'|| p_mc || '= r.'|| p_mc ||')';
My stored procedure is as follows:
input parameters are p_id (which is a numeric value), p_mc is a actually a column name. sample data is p_id = 6372325 and p_mc can be MC1, MC2 till MC14.
CREATE OR REPLACE PROCEDURE HCP_360.sp_get_all_records
(p_id NUMBER,
p_mc varchar2,
p_detail OUT SYS_REFCURSOR)
IS
v_count NUMBER;
v_master_id NUMBER;
v_sql VARCHAR2(2000);
--v_sql2 VARCHAR2(2000);
v_mc VARCHAR2(255):=p_mc;
cur_detail SYS_REFCURSOR;
BEGIN
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM COUNT_MASTER_TEMP';
execute immediate 'INSERT INTO COUNT_MASTER_TEMP ' ||
'SELECT COUNT (ar.'|| v_mc|| ')' || '
FROM app_recipient ar
WHERE EXISTS (SELECT r.' || v_mc || ' FROM app_recipient r
WHERE r.ID =' || p_id || ' AND ar.'|| v_mc || '= r.'|| v_mc||')';
END;
BEGIN
select c_count
into v_count
from COUNT_MASTER_TEMP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_count := 0;
END;
BEGIN
IF v_count > 0
THEN
v_sql := 'SELECT master_id
FROM app_recipient
WHERE ' || v_mc || ' IN (SELECT r.'||v_mc ||
' FROM app_recipient r
WHERE r.ID = ' || p_id || ')
AND ROWNUM <= 1
AND master_id IS NOT NULL
ORDER BY master_id DESC';
EXECUTE IMMEDIATE 'DELETE FROM COUNT_MASTER_TEMP';
EXECUTE IMMEDIATE 'INSERT INTO COUNT_MASTER_TEMP ' || v_sql;
END IF;
select c_count
into v_master_id
from COUNT_MASTER_TEMP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_master_id := 0;
END;
BEGIN
v_sql :=
'
SELECT r.ID,
r.master_id,
v.RECIPIENT_STATUS,
v.PARENT_OR_CHILD,
nvl(v.CHILD_COUNT, 0) CHILD_COUNT,
r.IS_PICKABLE,
r.IS_GOLDEN,
r.request_wf_state,
r.record_type,
r.first_name,
r.last_name,
r.'||v_mc ||
',r.middle,
r.title,
r.name_of_organization,
r.name_of_business,
r.address,
r.city,
r.state,
r.country,
v.HCP_TYPE,
v.HCP_SUBTYPE,
v.is_edit_locked,
v.record_type as rec_type,
v.DATA_SOURCE_NAME,
v.DEA_DATA,
v.NPI_DATA,
v.STATE_DATA,
RPPS,
v.finess,
v.siren_number
FROM app_recipient r
left join V_MASTER_RECIP_W_TRXN_OP v
on r.id = v.id
or r.master_id =' || v_master_id||
'WHERE' ||'r.'||v_mc || '= ANY
(SELECT ar.'||v_mc || 'FROM app_recipient ar WHERE r.ID =' || p_id || ')
UNION ALL
SELECT r.ID,
r.master_id,
v.RECIPIENT_STATUS,
v.PARENT_OR_CHILD,
nvl(v.CHILD_COUNT, 0) CHILD_COUNT,
r.IS_PICKABLE,
r.IS_GOLDEN,
r.request_wf_state,
r.record_type,
r.first_name,
r.last_name,
r.'||v_mc ||
',r.middle,
r.title,
r.name_of_organization,
r.name_of_business,
r.address,
r.city,
r.state,
r.country,
v.HCP_TYPE,
v.HCP_SUBTYPE,
v.is_edit_locked,
v.record_type as rec_type,
v.DATA_SOURCE_NAME,
v.DEA_DATA,
v.NPI_DATA,
v.STATE_DATA,
RPPS,
v.finess,
v.siren_number
FROM app_recipient r
left join V_MASTER_RECIP_W_TRXN_OP2 v
on r.id = v.id
or r.master_id =' || v_master_id ||'
WHERE r.'||v_mc ||'= ANY (SELECT ar.'||v_mc ||'FROM app_recipient ar WHERE r.ID =' ||v_master_id || ')';
open cur_detail for v_sql;
p_detail := cur_detail;
end;
END;
/
