1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
|
CREATE OR REPLACE PROCEDURE mh_test
AS
vInHandle utl_file.file_type;
vNewLine CLOB;
CURSOR crGetAdmissionTagStakeholder IS
SELECT XMLELEMENT("Stakeholder", XMLELEMENT("ULI", a.ULI),
XMLELEMENT("Gender",a.GENDER),
XMLELEMENT("BirthDate", XMLATTRIBUTES(a.BIRTH_DATE_TYPE AS "type"), a.BIRTH_DATE),
XMLELEMENT("HealthCareNumberInformation",XMLELEMENT("HealthCareNumber",a.HCN),
XMLFOREST(NVL(a.PROV_ISSUING_HCN,'AB') AS "ProvinceTerritoryIssuingHCN")),
XMLELEMENT("SubmissionType",a.SUBMISSION_TYPE),
(SELECT XMLAGG(XMLELEMENT("ServiceEpisode", XMLELEMENT("AdmissionDate", a2.ADMISSION_DATE),
XMLELEMENT("EpisodeStart",XMLFOREST(a2.SITE_CASE_NR AS "ChartNumber"
, a2.PSYCHIATRIC_STATUS AS "PsychiatricStatus"
, a2.PUBLIC_GUARDIAN_CODE AS "PublicGuardianCode"),
XMLFOREST(XMLFOREST(a2.PATIENT_REFERRED_BY AS "PatientReferredBy") AS "ReferralInformation"),
XMLFOREST(a2.FORENSIC_LEGAL_STATUS AS "ForensicLegalStatus",
a2.RESPONSIBILITY_FOR_PAYMENT AS "ResponsibilityForPayment"),
XMLFOREST(XMLFOREST(a2.PROGRAM_TYPE1 AS "MentalHealthProgramType",
a2.PROGRAM_START_DATE1 AS "ServiceProgramStartDate") AS "ProgramInformation"),
XMLFOREST(XMLFOREST(a2.MOST_RESPONSIBLE_PROVIDER_ID "MostResponsibleProviderID",
a2.PROVIDER_EXPERTISE AS "MostResponsibleProviderExp") AS "PhysicianInformation"),
(SELECT XMLAGG(XMLFOREST(XMLFOREST(d3.DIAGNOSIS_TYPE AS "DiagnosisType",
d3.ICD10_CODE AS "ICD10CACode",
d3.GAF_ADMIT AS "DSMIVTRGAFScore") AS "Diagnosis"))
FROM Diagnosis d3
WHERE a2.uli = d3.uli
AND a2.encounter_number = d3.encounter_number),
XMLFOREST(XMLFOREST(a2.ADMIT_VIA_AMBULANCE AS"AdmissionViaAmbulance"
, a2.ACCOMPANIED_BY_AT_ADMIT AS "AccompaniedByAtAdmission") AS "AdmissionInformation"),
CASE
WHEN a2.HOMELESS_FLAG IS NOT NULL THEN
XMLELEMENT("AddressInformation",XMLELEMENT("HomelessFlag",a2.HOMELESS_FLAG),
XMLELEMENT("OtherCountryFlag",a2.OTHER_COUNTRY_KNOWN_FLAG),
XMLELEMENT("ResidenceInformation",XMLFOREST(CASE
WHEN a2.OTHER_COUNTRY_KNOWN_FLAG = 'N'
THEN a2.PROVINCE_OF_RESIDENCE
END AS "ProvinceOfPatientsResidence"
, a2.POSTALCODE_KNOWN_FLAG AS "PCOfPatientsCurrentResKnownFlg"
, CASE
WHEN (a2.OTHER_COUNTRY_KNOWN_FLAG = 'N' OR a2.HOMELESS_FLAG = 'N')
THEN a2.POSTALCODE
END AS "PCOfPatientsCurrentRes")))
END
),
CASE
WHEN (SELECT MAX(e2.SERVICE_DATE) FROM SERVICE_EVENT e2, ADMISSIONS a2 WHERE a2.ULI = e2.ULI) IS NOT NULL THEN
(SELECT XMLAGG(XMLELEMENT("StakeholderServiceEvent",XMLELEMENT("ServiceEventIdentifier",e3.SERVICE_EVENT_IDENTIFIER),
XMLELEMENT("ServiceEventStartDate",e3.SERVICE_DATE),
XMLELEMENT("ServiceEventDuration",e3.SERVICE_EVENT_DURATION),
XMLELEMENT("GroupServiceEventInformation",XMLELEMENT("GroupServiceEventFlag",e3.GROUP_FLAG),
CASE
WHEN e3.GROUP_FLAG = 'Y' THEN
XMLFOREST(e3.NO_PARTICIPANTS_GROUP AS "NumberOfParticipantsInGroup")
END),
XMLELEMENT("InterventionInformation", XMLELEMENT("InterventionCode",e3.CCI_CD)
,XMLFOREST( e3.CCI_STATUS AS "InterventionAttributeStatus"
,e3.CCI_LOCATION AS "InterventionAttibuteDelivMde"
,e3.CCI_EXTENT AS "InterventionAttributeExtent")),
(SELECT XMLAGG(XMLFOREST(XMLFOREST(e4.PROVIDER_TYPE_CD AS "ServiceProviderRoleType",
e4.PROVIDER_CD AS "ServiceProviderID")"ServiceEventProvider"))
FROM ServiceProvider e4
WHERE e3.uli = e4.uli
AND e3.SERVICE_EVENT_IDENTIFIER = e4.SERVICE_EVENT_IDENTIFIER)))
FROM Service_Event e3
WHERE a2.uli = e3.uli
AND a2.encounter_number = e3.encounter_number
AND e3.service_event_identifier like '414%')
END,
XMLFOREST(XMLFOREST(d2.DISCHARGE_WAITLIST_START AS "DatePlacedOnDischWaitlist",
d2.DISCHARGE_WAITLIST_REASON AS "ReasonPlacedOnDischWaitlist") AS "OnDischargeWaitlist"),
XMLFOREST(XMLFOREST(d2.DISCHARGE_WAITLIST_END AS "DateTakenOffDischWaitlist") AS "OffDischargeWaitlist")))--,
FROM admissions a2
, discharge d2
WHERE a.uli = a2.uli
AND a.uli = d2.uli
AND a2.encounter_number = d2.encounter_number),
CASE
WHEN d3.DISCHARGE_DATE IS NOT NULL THEN
XMLELEMENT("EpisodeClose", XMLFOREST(d3.DISCHARGE_DATE AS "DischargeDate"),
XMLFOREST(XMLFOREST(d3.DISCHARGE_DISPOSITION AS "DischargeDisposition") AS "DispositionInformation"))
END
)
.getClobVal() AS "result"
FROM Admissions a
, Discharge d3
WHERE a.ULI = d3.ULI
AND a.ENCOUNTER_NUMBER = d3.ENCOUNTER_NUMBER
AND a.uli = 3131
GROUP BY a.ULI
, a.GENDER
, a.BIRTH_DATE_TYPE
, a.BIRTH_DATE
, a.HCN
, a.PROV_ISSUING_HCN
, a.SUBMISSION_TYPE
, d3.DISCHARGE_DATE
, d3.DISCHARGE_DISPOSITION
;
vInHandle := utl_file.fopen('/filedest/', dz.txt, 'w', 32767);
OPEN crGetAdmissionTagStakeholder;
LOOP
FETCH crGetAdmissionTagStakeholder INTO vNewLine;
EXIT WHEN crGetAdmissionTagStakeholder%NOTFOUND;
BEGIN print_clob (vInHandle,vNewLine); END;
END LOOP;
CLOSE crGetAdmissionTagStakeholder;
utl_file.fclose(vInHandle);
-- EXCEPTION
-- WHEN OTHERS THEN 'Failed';
-- EXIT;
END mh_test;
/
This is the procedure that prints the file:
CREATE OR REPLACE PROCEDURE print_clob (p_file IN utl_file.file_type, p_clob IN CLOB) AS
l_buffer VARCHAR2(32767);
l_offset NUMBER := 1;
BEGIN
LOOP
l_buffer := DBMS_LOB.substr(p_clob, 32767, l_offset);
EXIT WHEN l_buffer IS NULL;
UTL_FILE.put(p_file, l_buffer);
-- UTL_FILE.fflush(p_file);
l_offset := l_offset + 32767;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Do something useful here.
RAISE;
END print_clob;
/
|
