Hello everyone! I have a short question. I'm using toad for extracting from Oracle 9i Database and I don't have permissions to create objects in our database. I was asked to take an existing package modify it and run it to produce an extract. The problem is I don't think I can run the contents of a package just like I would regular SQL statements without creating a packing first. Is this indeed the case? I would really appreciate any help. Below is the code for the package that I need to run to produce my extract. Thank you very much!
Code:
CREATE OR REPLACE PACKAGE THOT.GET_SATISFACTION_SURVEYS IS
cons_region_type_VPD constant varchar2(10) := 'VPD';
cons_region_type_area constant varchar2(10) := 'AREA';
cons_region_type_acdo constant varchar2(10) := 'ACDO';
FUNCTION GET_FIRST_SHIPMENT_DATE (v_therapy_id IN NUMBER, v_patient_id IN NUMBER) RETURN DATE;
FUNCTION GET_LASTEST_OPEN_THERAPY_ID (v_pt_id IN NUMBER, v_primary_therapy_type IN VARCHAR2) RETURN NUMBER;
FUNCTION get_claim_center(p_patient_id in patients_table.id%type, p_date1 in
DATE, p_date2 in date) RETURN NUMBER;
CURSOR Survey_NP_c(p_dt1 IN date, p_dt2 IN date) IS
-- A Patient is considered new if this is the first ever shipment he or she
-- ever received
-- or if the patient was reinstated back on service but not within the same month.
SELECT DISTINCT /*+ rule */
'NEW' labtype,
pt.id patient_id,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.country,
pt.last,
pt.first,
pt.mi,
pt.svcbr_id pt_svcbr_id,
NULL claim_center_flag,
pt.primary_therapy_type therapy_type,
pt.sex,
'NP' patient_type,
TO_CHAR(p_dt1,'mmyy') extract_dt,
pt.LANGUAGE_SPOKEN LANG
FROM thot.ADDRESSES a ,
thot.PATIENTS_TABLE pt,
thot.prescriptions_table rxm
WHERE
rxm.patient_id=pt.id
AND rxm.shipment_id is NOT NULL
AND rxm.prescr_ship_date BETWEEN p_dt1 AND p_dt2
AND
(NOT EXISTS
( SELECT 'x'
FROM thot.SHIPMENTS sh, thot.SHIPMENT_ITEMS si, thot.PRESCRIPTIONS_TABLE rx,INVENTORY i
WHERE sh.completed_date IS NOT NULL
AND sh.shipment_date < p_dt1
AND sh.shipment_date > ( SELECT NVL(MAX(pth.stop_date), TO_DATE('01-JAN-1980','DD-MON-yyyy'))
FROM thot.PATIENT_THERAPIES pth
WHERE pth.patient_id = pt.id
AND pth.therapy_type = pt.primary_therapy_type
)
AND rx.patient_id = pt.id
AND rx.therapy_type = pt.primary_therapy_type
AND rx.shipment_id = sh.id
AND sh.id = si.shipment_id
AND si.inventory_id = i.id
AND i.tdrug_abbrev IS NOT NULL --(i.gdrug_abbrev IS NOT NULL OR )
AND rx.rx_status IN ('ACTIVE','DC')
)
)
/* AND EXISTS ( SELECT 1
FROM thot.PATIENT_THERAPIES pth
WHERE pth.patient_id = pt.id
AND pth.therapy_type = pt.primary_therapy_type
AND START_DATE BETWEEN p_dt1 AND p_dt2
AND stop_date IS NULL
) */
AND a.addr_seq = thot.get_addr_seq('P', pt.id, 'S')
AND a.name_type (+) = 'P'
AND a.name_id (+) = pt.id
--and pt.id in (265016,122444)
AND EXISTS ( SELECT 1
FROM thot.PRESCRIPTIONS_TABLE rx,
thot.SHIPMENTS s,
thot.SHIPMENT_ITEMS si,
thot.INVENTORY i
WHERE rx.therapy_type = pt.primary_therapy_type
AND rx.patient_id = pt.id
AND rx.completed_flag = 'Y' -- rx completed_flag must be 'Y'
AND rx.prescr_ship_date =
thot.GET_SATISFACTION_SURVEYS.get_first_shipment_date(
thot.GET_SATISFACTION_SURVEYS.get_lastest_open_therapy_id(
pt.id,pt.primary_therapy_type),pt.id)
AND rx.rx_status IN ('ACTIVE','DC')
AND rx.void_date IS NULL -- void_date must be null
AND rx.shipment_id = s.id
AND s.shipment_date BETWEEN p_dt1 AND p_dt2
AND s.completed_Date IS NOT NULL
AND s.completed_flag = 'Y' -- shipments completed_flag must be 'Y'
AND s.id = si.shipment_id
AND si.inventory_id = i.id
AND (i.gdrug_abbrev IS NOT NULL OR i.tdrug_abbrev IS NOT NULL)
GROUP BY 1
)
AND pt.svcbr_id NOT IN ( SELECT g_num_value
FROM thot.GROUPR
WHERE TYPE = 'SB'
AND name = 'PATIENT_SURVEY_EXCLUDE'
)
AND pt.team NOT IN ( SELECT g_char_value
FROM thot.GROUPR
WHERE TYPE = 'TM'
AND name = 'PATIENT_SURVEY_EXCLUE'
)
AND PT.PRIMARY_THERAPY_TYPE IN ( SELECT g_char_value
FROM thot.GROUPR -- added by reddy
WHERE TYPE = 'CS'
);
CURSOR Survey_DC_c(p_dt1 IN date, p_dt2 IN date) IS
SELECT DISTINCT /* FIRST_ROWS */
'DC' labtype,
pt.id patient_id,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.country,
pt.last,
pt.first,
pt.mi,
pt.svcbr_id pt_svcbr_id,
NULL claim_center_flag,
pt.primary_therapy_type therapy_type,
pt.sex,
'DC' patient_type,
to_char(p_dt1,'mmyy') extract_dt,
pt.LANGUAGE_SPOKEN LANG
FROM thot.addresses a,
thot.patients_table pt
WHERE a.name_type (+) = 'P'
AND a.addr_seq (+) = thot.get_addr_seq('P', pt.id, 'S')
AND a.name_id (+) = pt.id
-- make sure that there is at least one prescription for this patient
AND exists (SELECT 1 from prescriptions_table rx, shipments s, shipment_items si, inventory i
WHERE rx.patient_id = pt.id
AND pt.primary_Therapy_type = rx.therapy_type
and rx.void_date is null
and rx.shipment_id = s.id
and s.completed_date <= p_dt2
and s.id = si.shipment_id
and si.inventory_id = i.id
and (i.gdrug_abbrev is not null or i.tdrug_abbrev is not null))
-- make sure no open therapy exists for patient
AND NOT exists (select 1 from patient_therapies pth
where pth.patient_id = pt.id
AND pth.therapy_type = pt.primary_therapy_type
AND pth.stop_date is null)
-- if no open therapy exists make sure that the therapy did not stop
-- due to death and also make sure that the stop date is between
-- the entered date ranges.
AND NOT exists (select 1 from patient_therapies pth
where pth.patient_id = pt.id
and pth.therapy_type = pt.primary_therapy_type
and pth.stop_date between p_dt1 and p_dt2
and pth.stop_reason in (select g_char_value from groupr
where type = 'SR'
and name = 'PATIENT SURVEY OMIT'))
AND EXISTS (select 1 from patient_therapies pth
where pth.patient_id = pt.id
and pth.therapy_type = pt.primary_therapy_type
and pth.stop_date between p_dt1 and p_dt2)
-- MAKE SURE PATIENT IS NOT IN CERTAIN SERVICE BRANCHES
AND pt.svcbr_id not in (select g_num_value from groupr
where type = 'SB'
AND name = 'PATIENT_SURVEY_EXCLUDE_DC')
AND pt.team NOT IN (select g_char_value from groupr
where type = 'TM' and name = 'PATIENT_SURVEY_EXCLUE')
AND PT.PRIMARY_THERAPY_TYPE IN (select g_char_value from groupr -- added by reddy
where type = 'CS');
CURSOR Survey_LT_c(p_dt1 IN date, p_dt2 IN date) IS
SELECT /* FIRST_ROWS */
'YEAR' labtype,
pt.id patient_id,
a.addr1,
a.addr2,
a.city,
a.state,
a.zip,
a.country,
pt.last,
pt.first,
pt.mi,
pt.svcbr_id pt_svcbr_id,
NULL claim_center_flag,
pt.primary_therapy_type therapy_type,
pt.sex,
'LT' patient_type,
to_char(p_dt1,'mmyy') extract_dt,
pt.LANGUAGE_SPOKEN LANG
FROM thot.addresses a,
thot.patients_table pt
WHERE a.addr_seq (+) = thot.get_addr_seq('P', pt.id, 'S')
AND a.name_type (+) = 'P'
AND a.name_id (+) = pt.id
AND exists (select 1
from patient_therapies pth
WHERE pth.stop_date is null
and pth.patient_id = pt.id
and pth.therapy_type = pt.primary_therapy_type
and pth.start_date = (select max(start_date)
from patient_therapies pth1
where pth1.patient_id = pth.patient_id
and pth1.therapy_type = pt.primary_therapy_type)
and exists (Select 1
from shipments s, prescriptions_table rx,
shipment_items si, inventory i
where rx.patient_id = pt.id
and rx.therapy_type = pt.primary_therapy_type
and rx.shipment_id = s.id
and rx.rx_status in ('DC', 'ACTIVE')
and s.shipment_date >= pth.start_date
and s.shipment_date <= p_dt2
and s.completed_Date is not null
and s.id = si.shipment_id
and si.inventory_id = i.id
and (i.gdrug_abbrev is not null or i.tdrug_abbrev is not null)
group by rx.patient_id, rx.therapy_type
having mod((p_dt2 - min(s.shipment_date)), 365) between 1 AND p_dt2-p_dt1+1
AND FLOOR((p_dt2-MIN(S.SHIPMENT_DATE))/365) >= 1))
AND pt.svcbr_id not in (select g_num_value from groupr
where type = 'SB' AND name = 'PATIENT_SURVEY_EXCLUDE_LT')
AND pt.team NOT IN (select g_char_value from groupr
where type = 'TM' and name = 'PATIENT_SURVEY_EXCLUE')
AND PT.PRIMARY_THERAPY_TYPE IN (select g_char_value from groupr -- added by reddy
where type = 'CS')
AND EXISTS (SELECT 1 FROM PATIENT_STATUS PS
WHERE PS.PATIENT_ID = PT.ID
AND PS.STATUS_TYPE = 'PHARMACY'
AND PS.STATUS LIKE 'ACTIVE%'
AND PS.STATUS_DATE = (SELECT MAX(STATUS_DATE)
FROM PATIENT_STATUS PS1
WHERE PS1.PATIENT_ID = PS.PATIENT_ID
AND PS1.STATUS_TYPE = PS.STATUS_TYPE
AND STATUS_DATE <= P_DT2));
PROCEDURE get_survey_data(p_date1 IN DATE DEFAULT NULL, p_date2 IN DATE DEFAULT NULL);
PROCEDURE generate_customer_survey(p_date1 IN DATE DEFAULT NULL);
PROCEDURE get_region(p_svcbr_id IN patients_table.svcbr_id%TYPE ,p_region OUT VARCHAR2, p_region_name OUT VARCHAR2);
END;
/
show errors
exit![[profile]](/data/assets/smilies/profile.gif "[profile] [profile]")