| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PLS-707 error?
OK, but it's a long one.
I think, by virtue of the dbms_sql statements, drop & create statements are available for each table.
Any help is greatly appreciated.
A question which may give me a hint. What makes a function become invalid?
--****************************
--****************************
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.er_visit_log CASCADE CONSTRAINTS' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
null ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Drop Table ER_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.er_visit_log ( '||chr(10)||
'app_msg VARCHAR2(100), '||chr(10)||
'app_date DATE) '||chr(10)||
'TABLESPACE master_data PCTUSED 60 PCTFREE 10 ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ; exception when others then raise ;
insert into akinsgre.er_visit_log values ('Dropping table ER_INITIAL',
SYSDATE) ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Drop Table ER_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.ER_INITIAL CASCADE CONSTRAINTS' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
INSERT INTO ER_VISIT_LOG VALUES ('An Error Occurred Dropping
ER_initial', SYSDATE) ;
end ;
insert into er_visit_log values ('Dropping table MC_INITIAL', SYSDATE) ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Drop Table MC_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.MC_INITIAL CASCADE CONSTRAINTS' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
INSERT INTO ER_VISIT_LOG VALUES ('ERROR: Occurred Dropping
MC_initial', SYSDATE) ;
raise ;
end ;
INSERT INTO ER_VISIT_LOG VALUES ('Creating table ER_initial', sysdate) ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create Table ER_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.ER_INITIAL ( '||chr(10)||
'MEM_NO VARCHAR2(20), '||chr(10)||
'FIRST_DOS DATE) '||chr(10)||
'TABLESPACE master_data PCTUSED 60 PCTFREE 20 NOLOGGING
' ; dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ;
when others then
insert into er_visit_log values ('ERROR: Occurred Creating
ER_initial', sysdate) ;
raise ;
end ;
INSERT INTO ER_VISIT_LOG VALUES ('Creating table MC_initial', sysdate) ;
Declare
ddlString varchar2(400) ;
v_cursor number ;
BEGIN /* Create Table MC_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.MC_INITIAL ( '||chr(10)||' ;
'MEMBER VARCHAR2(20), '||chr(10)||
'FIRST_DOS DATE, '||chr(10)||
'DOCUMENT VARCHAR2(20), '||chr(10)|| 'LINE_CODE VARCHAR2(10), '||chr(10)|| 'REGION VARCHAR2(20), '||chr(10)|| 'LOB varchar2(10), '||chr(10)|| 'DENY_FLAG VARCHAR2(10), '||chr(10)|| 'PRIMARY_DIAGNOSIS VARCHAR2(10), '||chr(10)|| 'VENDOR varchar2(10), '||chr(10)|| 'PROVIDER VARCHAR2(10), '||chr(10)|| 'VENDOR_TYPE VARCHAR2(10), '||chr(10)|| 'PAY_DT DATE) '||chr(10)||
'TABLESPACE master_data PCTUSED 60 PCTFREE 20 NOLOGGING
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('Error creating mc_initial',
sysdate) ;
commit ;
raise ;
insert into er_visit_log values ('Populating MC_INITIAL', sysdate) ;
commit ;
begin
insert /*+ append */ into mc_initial (select mc.member,
mc.first_dos,
mc.document,
md.line_code,
mc.region,
mc.lob,
MC.DENY_FLAG,
MC.PRIMARY_DIAGNOSIS,
MC.VENDOR,
MC.PROVIDER,
MC.VENDOR_TYPE,
md.pay_dt
from MASTER_CLAIM#DETAIL_at_eis MD, MASTER_CLAIM_at_eis MC
where md.document = mc.document
and mc.first_dos between startdate and enddate
AND MD.LINE_CODE IN ('99281', '99282', '99283',
'99284', '99285', 'W9045', 'W9047', 'W9048')) ;
commit ;
commit ;
raise ;
end ;
insert into er_visit_log values ('MC_INITIAL Populated', sysdate) ;
insert into er_visit_log values ('Populating ER_INITIAL', sysdate) ;
commit ;
-- This block select ER visits from MASTER_CLAIM
-- and inserts them into a temporary table
INSERT /*+ append */ into er_initial (select distinct mc.member,
mc.first_dos
from mc_initial mc) ;
commit ;
insert into er_visit_log values ('ER_INITIAL successfully populated',
sysdate) ;
commit ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Analyze ER_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'ANALYZE TABLE ER_INITIAL COMPUTE STATISTICS' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred analyzing
ER_initial', sysdate) ;
commit ;
raise ;
to determine whether an ER visit
resulted in an IP stay.
The results are deleted from ER_INITIAL */
insert into er_visit_log values ('Removing IP from ER_INITIAL',
sysdate) ;
commit ;
DECLARE
v_commit_counter binary_integer := 0 ;
BEGIN
FOR IP_RECORD in (select distinct b.member, b.first_dos
from master_claim#detail_at_eis c,
master_claim_at_eis b,
er_initial a
where b.document = c.document
and a.mem_no = b.member
AND b.first_dos BETWEEN startdate and enddate
AND ((a.first_dos = b.first_dos)
OR (a.first_dos = b.first_dos - 1))
AND c.line_code LIKE 'IP%') LOOP
DELETE from er_initial where mem_no = IP_RECORD.member and
first_dos = IP_RECORD.first_dos ;
v_commit_counter := v_commit_counter + 1 ;
IF v_commit_counter/100 = 0 then
commit ;
end if ;
END LOOP;
commit ;
dbms_output.put_line(to_char(v_commit_counter)) ;
exception
when others then
insert into er_visit_log values ('ERROR: removing IP''s from
er_initial', sysdate) ;
commit ;
raise ;
END;
/* This block matches items from ER_initial
to determine whether an ER visit
resulted in an IP stay.
The results are deleted from existing er_visits data */
INSERT INTO ER_VISIT_LOG VALUES ('Removing IP from er_visits', SYSDATE)
;
commit ;
DECLARE
v_commit_counter binary_integer := 0 ;
BEGIN
FOR IP_RECORD IN (select distinct b.member, b.first_dos as first_dos
from master_claim#detail_at_eis c,
master_claim_at_eis b,
er_visits a
where b.document = c.document
and a.member = b.member
and ((a.first_dos = b.first_dos) or (a.first_dos =
b.first_dos - 1))
and c.line_code like 'IP%') LOOP
delete from er_visits where member = IP_RECORD.MEMBER and first_dos
= IP_RECORD.first_dos ;
v_commit_counter := v_commit_counter + 1 ; if v_commit_counter > 100 then
commit ;
v_commit_counter := 0 ;
COMMIT ;
raise ;
END;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Drop Table REV_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.REV_INITIAL CASCADE CONSTRAINTS' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('An Error Occurred Dropping
Rev_initial', sysdate) ;
COMMIT ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create table REV_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.REV_INITIAL ( '||chr(10)||
'DOCUMENT VARCHAR2(20)) '||chr(10)||
'TABLESPACE master_DATA PCTUSED 40 PCTFREE 10 ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ;
when others then
insert into er_visit_log values ('ERROR: Occurred creating
Rev_initial', sysdate) ;
COMMIT ;
raise ;
/* This block matches items from ER_initial
to determine whether an ER visit
has been reversed.
The results are inserted into rev_initial */
insert into er_visit_log values ('Populating table with reversals',
sysdate) ;
commit ;
DECLARE
v_commit_counter binary_integer := 0 ;
BEGIN
for REV_RECORD in (select mc.document as document
from mc_initial mc, mc_initial md
where mc.document = substr(md.document, 2, 99)
and mc.document like 'R%') LOOP
INSERT into rev_initial (document) values (rev_record.document) ;
v_commit_counter := v_commit_counter + 1 ;
if v_commit_counter > 500 then
commit ;
v_commit_counter := 0 ;
raise ;
END;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Analyzing REV_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'ANALYZE TABLE REV_INITIAL COMPUTE STATISTICS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred analyzing
Rev_initial', sysdate) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create index rev_initial_idx*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE INDEX akinsgre.REV_INITIAL_IDX ON '||chr(10)||
'REV_INITIAL(DOCUMENT) '||chr(10)||
'TABLESPACE master_indxs PCTFREE 10 ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ;
when others then
insert into er_visit_log values ('ERROR: Occurred creating
Rev_initial_idx', sysdate) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Analyzing REV_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'ANALYZE TABLE REV_INITIAL COMPUTE STATISTICS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred analyzing
Rev_initial', sysdate) ;
raise ;
end ;
insert into er_visit_log values ('Removing Reversals from er_visits ',
sysdate) ;
/* Delete records from er_visits which were found
in current REV query */
BEGIN
FOR REV_RECORD IN (select ER.document
from er_visits er, REV_INITIAL rev where ER.document = rev.document) LOOPdelete from er_visits where document = REV_RECORD.document ; END LOOP;
raise ;
END;
insert into er_visit_log values ('Creating Diagnosis table', sysdate) ;
commit ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Dropping MG_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.MG_INITIAL CASCADE CONSTRAINTS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred dropping
MG_initial', sysdate) ;
commit ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create table MG_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.MG_INITIAL ( '||chr(10)||
'DOCUMENT VARCHAR2(20), '||chr(10)||
'DIAG_CODE VARCHAR2(10), '||chr(10)||
'ROW_ID NUMBER) ' ||chr(10)||
'TABLESPACE master_data PCTUSED 40 PCTFREE 10
nologging' ; dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ; exception when others then insert into er_visit_log values ('ERROR: Occurred creating MG_INITIAL', sysdate) ; commit ; raise ;
begin
INSERT /*+ append */ into mg_initial (select a.document, a.diag_code,
a.row_id
from master_claim#diag_at_eis a,
(select mg.document, max(mg.row_id) as row_id
from master_claim#diag_at_EIS mg,
mc_initial mc
where mc.document = mg.document
and mc.first_dos between startdate and enddate
and row_id <=2
group by mg.document
) b
WHERE A.DOCUMENT = B.DOCUMENT
AND A.ROW_ID = B.ROW_ID
) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create index MG_INITIAL_DOCDIAG_IDX*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE INDEX akinsgre.MG_INITIAL_DOCDIAG_IDX ON
'||chr(10)||
'MG_INITIAL(DOCUMENT, DIAG_CODE) '||chr(10)||
'TABLESPACE master_indxs PCTFREE 10 ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred creating
MG_INITIAL_DOCDIAG_IDX', sysdate) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Analyzing REV_INITIAL*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'ANALYZE TABLE MG_INITIAL COMPUTE STATISTICS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred analyzing
MG_INITIAL', sysdate) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create index er_visits*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.er_visits#non_Grouped CASCADE
CONSTRAINTS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred dropping
er_visits', sysdate) ;
end ;
Declare
ddlString varchar2(1000) ;
v_cursor number ;
BEGIN /* Create table er_visits#non_Grouped*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.er_visits#non_Grouped (
'||chr(10)||
'DOCUMENT VARCHAR2(20), '||chr(10)||
'MEMBER VARCHAR2(20), '||chr(10)||
'FIRST_DOS DATE, '||chr(10)||
'DENY_FLAG VARCHAR2(1), '||chr(10)||
'PAY_DT DATE, '||chr(10)||
'AGE NUMBER, '||chr(10)||
'SEX VARCHAR2(1), '||chr(10)||
'PRIMARY_DIAGNOSIS VARCHAR2(10), '||chr(10)||
'VENDOR VARCHAR2(10), '||chr(10)||
'PROVIDER VARCHAR2(10), '||chr(10)||
'VENDOR_TYPE VARCHAR2(10), '||chr(10)||
'DIAG_CODE VARCHAR2(10), '||chr(10)||
'CASE_NO VARCHAR2(20), '||chr(10)||
'LOB VARCHAR2(10), '||chr(10)||
'REGION VARCHAR2(20), '||chr(10)||
'ROW_ID NUMBER) '||chr(10)||
'TABLESPACE master_data PCTUSED 40 PCTFREE 10
nologging' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred creating
er_visits#non_Grouped ', sysdate) ;
raise ;
end ;
begin
INSERT into er_visits#non_grouped (
select distinct mc.document as document,
MC.MEMBER AS MEMBER,
MC.FIRST_DOS AS FIRST_DOS,
MC.DENY_FLAG AS DENY_FLAG,
Mc.PAY_DT AS PAY_DT,
FLOOR(MONTHS_BETWEEN(MC.FIRST_DOS, M.BIRTH)/12) AS AGE,
M.SEX AS SEX,
MC.PRIMARY_DIAGNOSIS AS PRIMARY_DIAGNOSIS,
MC.VENDOR AS VENDOR,
MC.PROVIDER AS PROVIDER,
MC.VENDOR_TYPE AS VENDOR_TYPE,
MG.DIAG_CODE AS SECONDARY_DIAGNOSIS,
M.CASE_NO AS CASE_NO,
MC.LOB AS LOB,
MC.REGION AS REGION,
MG.ROW_ID
from eR_INITIAL ER,
Mc_initial MC,
MEMBERS M,
MG_INITIAL MG,
REV_INITIAL R
WHERE ER.MEM_NO = MC.MEMBER
AND ER.FIRST_DOS = MC.FIRST_DOS
AND MC.DOCUMENT = R.DOCUMENT(+)
AND R.DOCUMENT IS NULL
AND MC.DOCUMENT NOT LIKE 'R%'
AND MC.DOCUMENT = MG.DOCUMENT
AND ER.MEM_NO = M.MEM_NO) ;
insert into er_visit_log values ('ERROR: populating er_visits#non_grouped', sysdate) ;
raise ;
end ;
/* update file */
begin
UPDATE er_visits#non_grouped
SET DENY_FLAG = 0
WHERE DENY_FLAG IS NULL ;
UPDATE er_visits#non_grouped
SET VENDOR_TYPE = 'XXXX'
WHERE VENDOR_TYPE IS NULL ;
exception
when others then
insert into ER_VISIT_LOG values ('ERROR: updating
ER_VISITS#NON_GROUPED', sysdate) ;
raise ;
end ;
Declare
ddlString varchar2(200) ;
v_cursor number ;
BEGIN /* Create index er_visits*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'DROP TABLE akinsgre.er_visits#Grouped CASCADE
CONSTRAINTS ' ;
dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
dbms_sql.close_cursor(v_cursor) ;
exception
when others then
insert into er_visit_log values ('ERROR: Occurred dropping
er_visits', sysdate) ;
end ;
insert into er_visit_log values ('Creating ER_VISITS#GROUPED table',
sysdate) ;
Declare
ddlString varchar2(1000) ;
v_cursor number ;
BEGIN /* Create table er_visits#Grouped*/
v_cursor := dbms_sql.open_cursor ;
ddlString := 'CREATE TABLE akinsgre.er_visits#Grouped ( '||chr(10)||er2.case_no, er2.lob, er2.region) ;
'DOCUMENT VARCHAR2(20), '||chr(10)||
'MEMBER VARCHAR2(20), '||chr(10)||
'FIRST_DOS DATE, '||chr(10)||
'DENY_FLAG VARCHAR2(1), '||chr(10)||
'PAY_DT DATE, '||chr(10)||
'AGE NUMBER, '||chr(10)||
'SEX VARCHAR2(1), '||chr(10)||
'PRIMARY_DIAGNOSIS VARCHAR2(10), '||chr(10)||
'VENDOR VARCHAR2(10), '||chr(10)||
'PROVIDER VARCHAR2(10), '||chr(10)||
'VENDOR_TYPE VARCHAR2(10), '||chr(10)||
'DIAG_CODE VARCHAR2(10), '||chr(10)||
'CASE_NO VARCHAR2(20), '||chr(10)||
'LOB VARCHAR2(10), '||chr(10)||
'REGION VARCHAR2(20)) '||chr(10)||
'TABLESPACE master_data PCTUSED 40 PCTFREE 10
nologging' ; dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ; dbms_sql.close_cursor(v_cursor) ; exception when others then insert into er_visit_log values ('ERROR: Occurred creating er_visits#non_Grouped ', sysdate) ; raise ; end ; insert into er_visit_log values ('Grouping new ER claims into er_visits#grouped', sysdate) ; commit ; begin INSERT /*+ append */ INTO er_visits#grouped (select er2.document, er2.member, er2.first_dos, er2.deny_flag, er2.pay_dt, er2.age, er2.sex, er2.primary_diagnosis, er2.vendor, er2.provider, er2.vendor_Type, er2.diag_code, er2.case_no, er2.lob, er2.region from (select min(er2.document) as document, er2.member, er2.first_dos, er2.deny_flag, er2.pay_dt, er2.vendor_Type, er2.row_id from er_visits#non_grouped er2, (select er2.member, er2.first_dos, er2.deny_flag, er2.row_id, er2.vendor_Type, min(er2.pay_dt) as pay_dt from er_visits#non_grouped er2, (select er2.member, er2.first_dos, er2.deny_Flag, er2.row_id, min(er2.vendor_type) as vendor_Type from er_visits#non_grouped er2, (select erA.member, erA.first_dos, erA.deny_flag, max(erA.row_id) as row_id from er_visits#non_grouped erA, (select Member, first_dos, min(deny_flag) as deny_flag from er_visits#non_grouped group by member, first_dos) erB where erA.member = erB.member and erA.first_dos = erB.first_dos and erA.deny_flag = erB.deny_flag group by erA.member, erA.first_dos, erA.deny_flag ) er1 where er2.member = er1.member and er2.first_dos = er1.first_dos and er2.deny_flag = er1.deny_flag and er2.row_id = er1.row_id group by er2.member, er2.first_dos, er2.deny_flag, er2.row_id) er3 where er2.member = er3.member and er2.first_dos = er3.first_dos and er2.row_id = er3.row_id and er2.deny_flag = er3.deny_flag and er2.vendor_type = er3.vendor_type group by er2.member, er2.first_dos, er2.deny_flag, er2.row_id, er2.vendor_Type) er5 where er2.member = er5.member and er2.first_dos = er5.first_dos and er2.deny_flag = er5.deny_flag and er2.row_id = er5.row_id and er2.vendor_type = er5.vendor_type and er2.pay_dt = er5.pay_dt group by er2.member, er2.first_dos, er2.deny_flag, er2.pay_dt, er2.vendor_Type, er2.row_id ) er6, er_visits#non_grouped er2 where er2.member = er6.member and er2.first_dos = er6.first_dos and er2.deny_flag = er6.deny_flag and er2.row_id = er6.row_id and er2.vendor_type = er6.vendor_type and er2.pay_dt = er6.pay_dt and er2.document = er6.document group by er2.document, er2.member, er2.first_dos, er2.deny_flag, er2.pay_dt, er2.age, er2.sex, er2.primary_diagnosis, er2.vendor, er2.provider, er2.vendor_Type, er2.diag_code,
raise ;
end ;
commit ;
insert into er_visit_log values ('Appending new er_visits#grouped into
er_visits', sysdate) ;
commit ;
declare
er_Updated boolean ;
v_UpdateCnt integer ;
v_InsertCnt integer ;
er_Rec er_visits%rowtype ;
insertStr varchar2(100) ;
cursor er_cursor is select er.*
from er_visits er, er_visits#grouped erg where er.member = erg.member and er.first_dos = erg.first_dos and er.first_dos between startdate and enddate order by er.member, er.first_dos ; cursor erg_cursor is select * from er_visits#grouped order by member, first_dos ;
er_Updated := False ;
while not er_Updated and er_cursor%found loop /* while not eof
and record locate attempting */
if (erg_rec.member = er_rec.member) and (erg_rec.first_dos =
er_rec.first_dos) then
if erg_rec.deny_flag = 0 and er_rec.deny_flag <> 0 then
update er_visits set document = er_rec.document,
deny_flag = erg_rec.deny_flag,
pay_dt = erg_rec.pay_dt,
age = erg_rec.age,
sex = erg_rec.sex,
primary_diagnosis = erg_rec.primary_diagnosis,
vendor = erg_rec.vendor,
provider = erg_rec.provider,
vendor_type = erg_rec.vendor_type,
secondar_diagnosis =
erg_rec.diag_code,
case_no = erg_rec.case_no,
lob = erg_rec.lob,
region = erg_rec.region
where member = erg_rec.member and first_dos = erg_rec.first_dos ;
elsif er_rec.deny_flag <> 0 and (erg_rec.vendor_type =
'HOSP' and er_rec.vendor_type <> 'HOSP') then
update er_visits set document = er_rec.document,
deny_flag = erg_rec.deny_flag,
pay_dt = erg_rec.pay_dt,
age = erg_rec.age,
sex = erg_rec.sex,
primary_diagnosis = erg_rec.primary_diagnosis,
vendor = erg_rec.vendor,
provider = erg_rec.provider,
vendor_type = erg_rec.vendor_type,
secondar_diagnosis =
erg_rec.diag_code,
case_no = erg_rec.case_no,
lob = erg_rec.lob,
region = erg_rec.region
where member = erg_rec.member and first_dos = erg_rec.first_dos ;
elsif er_rec.deny_flag = 0 and er_rec.vendor_type = 'HOSP' and
(erg_rec.pay_dt > er_rec.pay_dt) then
update er_visits set document = er_rec.document,
deny_flag = erg_rec.deny_flag,
pay_dt = erg_rec.pay_dt,
age = erg_rec.age,
sex = erg_rec.sex,
primary_diagnosis = erg_rec.primary_diagnosis,
vendor = erg_rec.vendor,
provider = erg_rec.provider,
vendor_type = erg_rec.vendor_type,
secondar_diagnosis =
erg_rec.diag_code,
case_no = erg_rec.case_no,
lob = erg_rec.lob,
region = erg_rec.region
where member = erg_rec.member and first_dos = erg_rec.first_dos ;
end if ;
er_updated := true ;
v_UpdateCnt := v_UpdateCnt + 1 ;
fetch er_cursor into er_rec ;
commit ;
elsif erg_rec.member < er_rec.member or (erg_rec.member =
er_rec.member and erg_rec.first_dos < er_rec.first_dos) then
insert into er_visits values (erg_rec.document,
erg_rec.member,
erg_rec.first_dos,
erg_rec.deny_Flag,
erg_rec.pay_dt,
erg_rec.age,
erg_rec.sex,
erg_rec.primary_diagnosis,
erg_rec.vendor,
erg_rec.provider,
erg_rec.vendor_type,
erg_rec.diag_code,
erg_rec.case_no,
erg_rec.lob,
erg_rec.region) ;
commit ;
er_updated := true ;
v_InsertCnt := v_InsertCnt + 1 ;
else
fetch er_cursor into er_rec ;
end if ;
if not er_updated and er_cursor%notfound then
insert into er_visits values (erg_rec.document,
erg_rec.member,
erg_rec.first_dos,
erg_rec.deny_Flag,
erg_rec.pay_dt,
erg_rec.age,
erg_rec.sex,
erg_rec.primary_diagnosis,
erg_rec.vendor,
erg_rec.provider,
erg_rec.vendor_type,
erg_rec.diag_code,
erg_rec.case_no,
erg_rec.lob,
erg_rec.region) ;
end if ;
end loop ; /* while */
if not er_updated and er_cursor%notfound then
insert into er_visits values (erg_rec.document,
erg_rec.member,
erg_rec.first_dos,
erg_rec.deny_Flag,
erg_rec.pay_dt,
erg_rec.age,
erg_rec.sex,
erg_rec.primary_diagnosis,
erg_rec.vendor,
erg_rec.provider,
erg_rec.vendor_type,
erg_rec.diag_code,
erg_rec.case_no,
erg_rec.lob,
erg_rec.region) ;
commit ;
v_InsertCnt := v_InsertCnt + 1 ;
end if ;
raise ;
end ;
commit ;
return 1 ;
exception
when others then
raise ;
end er_visit_refresh ;
-greg
 |
 |