view.sql

Delphi6开发的HIS程序

create or replace view v_rmedicine as
select sick.sickid                sicke_sickid,
       sick.isarrearage           sicke_isarrearage,
       sick.code                  sicke_code,
       sick.birthday              sicke_birthday,
       sick.sex                   sicke_sex,
       sick.cname                 sicke_cname,
       sick.created               sicke_created,
       sick.isactive              sicke_isactive,
       sick.isvalid               sicke_isvalid,
       sick.st                    sicke_st,
       sick.updated               sicke_updated,
       sick.professionid          sicke_professionid,
       sick.wedlockid             sicke_wedlockid,
       sick.nativeplaceid         sicke_nativeplaceid,
       sick.nationid              sicke_nationid,
       sick.paymentmethodid       sicke_paymentmethodid,
       sick.createby              sicke_createby,
       sick.updateby              sicke_updateby,
       sick.tollstationid         sicke_tollstationid,
       recipe.recipeid            recipe_recipeid,
       recipe.created             recipe_created,
       recipe.isactive            recipe_isactive,
       recipe.isvalid             recipe_isvalid,
       recipe.isprinted           recipe_isprinted,
       recipe.st                  recipe_st,
       recipe.updated             recipe_updated,
       recipe.doctorid            recipe_doctorid,
       recipe.reckoningid         recipe_reckoningid,
       recipe.createby            recipe_createby,
       recipe.updateby            recipe_updateby,
       rmedicine.rmedicineid      rmedicine_rmedicineid,
       rmedicine.isactive         rmedicine_isactive,
       rmedicine.price            rmedicine_price,
       rmedicine.total            rmedicine_total,
       rmedicine.amount           rmedicine_amount,
       rmedicine.reckoningid      rmedicine_reckoningid,
       rmedicine.pstockmedicineid rmedicine_pstockmedicineid,
       medicinestock.medicineid   medicinestock_medicineid,
       medicine.code              medicine_code,
       medicine.memo              medicine_memo,
       medicine.alais             medicine_alais,
       medicine.model             medicine_model,
       medicine.caption           medicine_caption,
       medicine.doseid            medicine_doseid,
       medicine.unitsid           medicine_unitsid,
       medicine.usemethodid       medicine_usemethodid,
       medicine.medicinecatalogid medicine_medicinecatalogid,
       rmedicine.*
from   sick,
       recipe,
       rmedicine,
       pstockmedicine,
       medicinestock,
       medicine
where  sick.sickid = recipe.sickid and
       recipe.reckoningid = rmedicine.reckoningid(+) and
       rmedicine.pstockmedicineid = pstockmedicine.pstockmedicineid(+) and
       pstockmedicine.medicinestockid = medicinestock.medicinestockid(+) and
       medicinestock.medicineid = medicine.medicineid(+);

create or replace view v_recipe as
select recipe.recipeid      recipe_recipeid,
       recipe.created       recipe_created,
       recipe.isactive      recipe_isactive,
       recipe.isvalid       recipe_isvalid,
       recipe.isprinted     recipe_isprinted,
       recipe.st            recipe_st,
       recipe.updated       recipe_updated,
       recipe.doctorid      recipe_doctorid,
       recipe.reckoningid   recipe_reckoningid,
       recipe.createby      recipe_createby,
       recipe.updateby      recipe_updateby,
       recipe.sickid        recipe_sickid,
       recipe.sickid        sick_sickid,
       sick.isarrearage     sick_isarrearage,
       sick.code            sick_code,
       sick.birthday        sick_birthday,
       sick.sex             sick_sex,
       sick.cname           sick_cname,
       sick.created         sick_created,
       sick.isactive        sick_isactive,
       sick.isvalid         sick_isvalid,
       sick.st              sick_st,
       sick.updated         sick_updated,
       sick.professionid    sick_professionid,
       sick.wedlockid       sick_wedlockid,
       sick.nativeplaceid   sick_nativeplaceid,
       sick.nationid        sick_nationid,
       sick.paymentmethodid sick_paymentmethodid,
       sick.createby        sick_createby,
       sick.updateby        sick_updateby,
       sick.tollstationid   sick_tollstationid
from   recipe, sick
where  recipe.sickid = sick.sickid;

create or replace view v_recipe_today as
select recipe.recipeid      recipe_recipeid,
       recipe.created       recipe_created,
       recipe.isactive      recipe_isactive,
       recipe.isvalid       recipe_isvalid,
       recipe.isprinted     recipe_isprinted,
       recipe.st            recipe_st,
       recipe.updated       recipe_updated,
       recipe.doctorid      recipe_doctorid,
       recipe.reckoningid   recipe_reckoningid,
       recipe.createby      recipe_createby,
       recipe.updateby      recipe_updateby,
       recipe.sickid        recipe_sickid,
       recipe.sickid        sick_sickid,
       sick.isarrearage     sick_isarrearage,
       sick.code            sick_code,
       sick.birthday        sick_birthday,
       sick.sex             sick_sex,
       sick.cname           sick_cname,
       sick.created         sick_created,
       sick.isactive        sick_isactive,
       sick.isvalid         sick_isvalid,
       sick.st              sick_st,
       sick.updated         sick_updated,
       sick.professionid    sick_professionid,
       sick.wedlockid       sick_wedlockid,
       sick.nativeplaceid   sick_nativeplaceid,
       sick.nationid        sick_nationid,
       sick.paymentmethodid sick_paymentmethodid,
       sick.createby        sick_createby,
       sick.updateby        sick_updateby,
       sick.tollstationid   sick_tollstationid
from   (select rmedicine.reckoningid from rmedicine group by reckoningid) t_reckoningid,
       recipe,
       sick
where  t_reckoningid.reckoningid = recipe.reckoningid and
       recipe.sickid = sick.sickid and
       to_char(sick.created, 'yyyy-mm-dd') =
       to_char(sysdate,      'yyyy-mm-dd');

create or replace view v_pstockoutorder as
select pstockoutorder.pstockoutorderid pstockoutorderid,
       pstockoutorder.total            pstockoutorder_total,
       pstockoutorder.taxrate          pstockoutorder_taxrate,
       pstockoutorder.amount           pstockoutorder_amount,
       pstockoutorder.isactive         pstockoutorder_isactive,
       pstockoutorder.isvalid          pstockoutorder_isvalid,
       pstockoutorder.isapprove        pstockoutorder_isapprove,
       pstockoutorder.approvedate      pstockoutorder_approvedate,
       pstockoutorder.created          pstockoutorder_created,
       pstockoutorder.updated          pstockoutorder_updated,
       pstockoutorder.checkoutdate     pstockoutorder_checkoutdate,
       pstockoutorder.createby         pstockoutorder_createby,
       pstockoutorder.updateby         pstockoutorder_updateby,
       pstockoutorder.approveby        pstockoutorder_approveby,
       pstockoutorder.price            pstockoutorder_price,
       pstockoutorder.departmentid     pstockoutorder_departmentid,
       pstockmedicine.pstockmedicineid pstockmedicineid,
       pstockmedicine.samount          pstockmedicine_samount,
       pstockmedicine.amount           pstockmedicine_amount,
       pstockmedicine.created          pstockmedicine_created,
       pstockmedicine.updated          pstockmedicine_updated,
       medicinestock.medicinestockid   medicinestockid,
       medicinestock.created           medicinestock_created,
       medicinestock.updated           medicinestock_updated,
       medicinestock.minmodel          medicinestock_minmodel,
       medicinestock.amount            medicinestock_amount,
       medicinestock.minamount         medicinestock_minamount,
       medicinestock.minunitscode      medicinestock_minunitscode,
       medicine.medicineid             medicine_medicineid,
       medicine.code                   medicine_code,
       medicine.topqty                 medicine_topqty,
       medicine.downqty                medicine_downqty,
       medicine.memo                   medicine_memo,
       medicine.alais                  medicine_alais,
       medicine.model                  medicine_model,
       medicine.pinyin                 medicine_pinyin,
       medicine.caption                medicine_caption,
       medicine.doseid                 medicine_doseid,
       medicine.unitsid                medicine_unitsid,
       medicine.usemethodid            medicine_usemethodid,
       medicine.medicinecatalogid      medicine_medicinecatalogid
from   pstockoutorder, pstockmedicine, medicinestock, medicine
where  pstockoutorder.pstockmedicineid = pstockmedicine.pstockmedicineid and
       pstockmedicine.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid;

create or replace view v_rmedicine_2 as
select rmedicine.*,
       medicine.code              medicine_code,
       medicine.memo              medicine_memo,
       medicine.alais             medicine_alais,
       medicine.model             medicine_model,
       medicine.caption           medicine_caption,
       medicine.doseid            medicine_doseid,
       medicine.unitsid           medicine_unitsid,
       medicine.usemethodid       medicine_usemethodid,
       medicine.medicinecatalogid medicine_medicinecatalogid
from   rmedicine, pstockmedicine, medicinestock, medicine
where  rmedicine.pstockmedicineid = pstockmedicine.pstockmedicineid and
       pstockmedicine.medicinestockid = medicinestock.medicinestockid(+) and
       medicinestock.medicineid = medicine.medicineid(+);

create or replace view v_rmedicine2 as
select rmedicine                 .*,
       medicine.code              m_code,
       medicine.memo              m_memo,
       medicine.alais             m_alais,
       medicine.model             m_model,
       medicine.caption           m_caption,
       medicine.doseid            m_doseid,
       medicine.unitsid           m_unitsid,
       medicine.usemethodid       m_usemethodid,
       medicine.minunitsid        m_minunitsid,
       medicine.medicinecatalogid m_medicinecatalogid
from   rmedicine, pstockmedicine, medicinestock, medicine
where  rmedicine.pstockmedicineid = pstockmedicine.pstockmedicineid and
       pstockmedicine.medicinestockid = medicinestock.medicinestockid and
       medicinestock.medicineid = medicine.medicineid;

create or replace view v_reckoningdetail as
select rmedicine.price,
       rmedicine.total,
       rmedicine.amount,
       rmedicine.reckoningid,
       rmedicine.created,
       medicine.caption         caption,
       units.caption            units,
       medicinecatalog.caption  catalog,
       chargecatalog.caption chargecatalog
from   rmedicine, medicine, units, medicinecatalog, chargecatalog
where  rmedicine.medicineid = medicine.medicineid and
       medicine.minunitsid = units.unitsid and
       medicine.medicinecatalogid = medicinecatalog.medicinecatalogid and
       medicinecatalog.chargecatalogcode = chargecatalog.chargecatalogcode
union
select rservice.price,
       rservice.total,
       rservice.amount,
       rservice.reckoningid,
       rservice.created,
       service.caption caption,
       '次' units,
       servicecatalog.caption catalog, 
       chargecatalog.caption chargecatalog
from rservice, service, servicecatalog, chargecatalog
where rservice.serviceid = service.serviceid and
      service.servicecatalogid = servicecatalog.servicecatalogid and
      servicecatalog.chargecatalogcode = chargecatalog.chargecatalogcode;