Проектирование реляционной базы данных автоматизации учета методом нормализации

idbalanceacc,BFB_AccAPI.GetMnemoCode(r.idbalanceacc) idbalanceaccHL,r.idbalancetax,BFB_AccAPI.GetMnemoCode(r.idbalancetax) idbalancetaxHL,r.idbalancemng,BFB_AccAPI.GetMnemoCode(r.idbalancemng) idbalancemngHL,r.idspendacc,BFB_AccAPI.GetMnemoCode(r.idspendacc) idspendaccHL,r.idspendtax,BFB_AccAPI.GetMnemoCode(r.idspendtax) idspendtaxHL,r.idspendmng,BFB_AccAPI.GetMnemoCode(r.idspendmng) idspendmngHL,r.idamortacc,BFB_AccAPI.GetMnemoCode(r.idamortacc) idamortaccHL,r.idamorttax,BFB_AccAPI.GetMnemoCode(r.idamorttax) idamorttaxHL,r.idamortmng,BFB_AccAPI.GetMnemoCode(r.idamortmng) idamortmngHL , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd - mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd - mr2.fSumAmortTaxEnd - mr2.fSumAmortBonusEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainTaxEnd , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceTaxEnd , trim(replace(replace(to_char(mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortAccEnd , trim(replace(replace(to_char(mr2.fSumAmortTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortTaxEnd,au.sDescription,i.sFactoryNum,i.sModel,(select ii.sDescription from asf_invnumb ii where ii.id = i.id) as sPosition,max(tp.sCode) as sCode-- ,EQP_EquipmentAPI.GetIDTypeEquipment_MC(ee.IDTypeEquipment) as IDTypeEquipment_MC , case when ASF.bZalog1(t.idInvNumb, null, au.ddate,1) = 1 then 'V' else '' end as sZalog , case when nvl(i.bIsRealProperty,0) = 1 then 'V' else '' end as bIsRealProperty , case when nvl(r.idstate, 0) = asf_stateapi.findByMnemocode('INPRESERVE') then 'V' else '' end as bConservfromASF_ActUnionObjectDet t,asf_actunionobject au,ASF_InvNumbi,ASF_InvCardClassItem c,Asf_Classifiercf,Asf_Classitem ci,eqp_equipmentee,eqp_techplacetp, (select *fromAsf_Registry rwherer.nperiod = to_number(to_char(:super$dDate, 'yyyymm'))) r, (selectr.asf_invnumb as invnumb, sum(nvl(case when b.sCost in ('BALANCEACC','OUTBALANCEACC') and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceAccEnd,sum(nvl(case when b.sCost in ('BALANCETAX','OUTBALANCETAX') and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceTaxEnd,sum(nvl(case when b.sCost = 'AMORTACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortAccEnd,sum(nvl(case when b.sCost = 'AMORTTAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortTaxEnd,sum(nvl(case when b.sCost = 'AMORTBONUS'andr.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortBonusEnd,sum(nvl(case when s.idAdditionalCapitalAcc = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalAccEnd,sum(nvl(case when s.idAdditionalCapitalTax = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalTaxEnd,sum(nvl(case when b.sCost = 'OUTBALANCEACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceAccEnd,sum(nvl(case when b.sCost = 'OUTBALANCETAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceTaxEndfromASF_ActUnionObjectDet a,ACT_EntTransRepFull r, ASF_CostAcc b,BFB_AccAccMap c,ASF_SettingMap s,ASF_DepOwnerChangeSettingMap dwherea.idActUnionObject = :super$idandr.asf_invnumb = a.idinvnumbandto_number(to_char(r.dDate, 'yyyymm')) <= to_number(to_char(:super$dDate, 'yyyymm'))andd.IDDepOwnerInvNumb = :super$idDepOwnerandc.idAccSlave = r.idAccFromandc.idAccOwner = b.idAccandd.IDDepOwnerEntTrans = r.iddepownerandd.IDAdjustMethod = r.idadjustmethodands.iddepowner = d.IDDepOwnerInvNumbgroup by r.ASF_INVNUMB ) mr2 wheret.idActUnionObject = :super$idandt.idInvNumb = i.id(+)andt.idInvNumb = c.idinvcard(+)andc.idclassitem = ci.id(+)andc.idclassifiers = cf.id(+)andt.idInvNumb = r.idInvNumb(+)andt.idInvNumb=mr2.invnumb(+) -- tvorogov - (+) надообязательноand t.idActUnionObject=au.idandt.idInvNumb=ee.idInvNumb(+)andee.IDTechPlace=tp.id(+)group by t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber,i.sCaption,t.idClass,r.nusefullifeacc,r.nusefullifetax,r.nusefullifemng,r.iddepartment,r.idresponsibleperson,r.idbalanceacc,r.idbalancetax,r.idbalancemng,r.idspendacc,r.idspendtax,r.idspendmng,r.idamortacc,r.idamorttax,r.idamortmng,t.nOrder, mr2.fSumBalanceAccEnd, mr2.fSumAmortAccEnd, mr2.fSumBalanceTaxEnd, mr2.fSumAmortTaxEnd, mr2.fSumAmortBonusEnd,au.sDescription,au.DDATE,i.sFactoryNum,i.sModel,i.id,i.nUsefulLifeRef,i.bIsRealProperty,r.idstateorder by t.nOrder --i.sNumber#][ #&Second=select t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber as idInvNumb_MC,i.sCaption as idInvNumb_HL,t.idClass,t.nOrder,max(casewhen upper(cf.smnemocode) = 'OKOF' thenci.sClassCodeend) as sOkofMC,max(casewhen upper(cf.smnemocode) = 'OKOF' thenci.sCaptionend) as sOkof,max(casewhen upper(cf.smnemocode) = 'NGROUP' thenci.sClassCodeend) as sAmortMC,max(casewhen upper(cf.smnemocode) = 'NGROUP' thenci.sCaptionend) as sAmort,max(casewhen upper(cf.smnemocode) = 'ENAO' thenci.sClassCodeend) as sEnaoMC,max(casewhen upper(cf.smnemocode) = 'ENAO' thenci.sCaptionend) as sEnao,nvl(r.nusefullifeacc,i.nUsefulLifeRef)nusefullifeacc,nvl(r.nusefullifetax,i.nUsefulLifeRef)nusefullifetax,nvl(r.nusefullifemng,i.nUsefulLifeRef)nusefullifemng,r.iddepartment as idDep,bs_departmentapi.GetMnemoCode(r.iddepartment) as idDepMC,bs_departmentapi.GetHeadLine(r.iddepartment) as idDepHL,r.idresponsibleperson as idRespPerson,bs_responsiblepersonapi.GetMnemoCode(r.idresponsibleperson) as idRespPersonMC,bs_responsiblepersonapi.GetHeadLine(r.idresponsibleperson) as idRespPersonHL,r.idbalanceacc,BFB_AccAPI.GetMnemoCode(r.idbalanceacc) idbalanceaccHL,r.idbalancetax,BFB_AccAPI.GetMnemoCode(r.idbalancetax) idbalancetaxHL,r.idbalancemng,BFB_AccAPI.GetMnemoCode(r.idbalancemng) idbalancemngHL,r.idspendacc,BFB_AccAPI.GetMnemoCode(r.idspendacc) idspendaccHL,r.idspendtax,BFB_AccAPI.GetMnemoCode(r.idspendtax) idspendtaxHL,r.idspendmng,BFB_AccAPI.GetMnemoCode(r.idspendmng) idspendmngHL,r.idamortacc,BFB_AccAPI.GetMnemoCode(r.idamortacc) idamortaccHL,r.idamorttax,BFB_AccAPI.GetMnemoCode(r.idamorttax) idamorttaxHL,r.idamortmng,BFB_AccAPI.GetMnemoCode(r.idamortmng) idamortmngHL , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd - mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd - mr2.fSumAmortTaxEnd - mr2.fSumAmortBonusEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumRemainTaxEnd , trim(replace(replace(to_char(mr2.fSumBalanceAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceAccEnd , trim(replace(replace(to_char(mr2.fSumBalanceTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumBalanceTaxEnd , trim(replace(replace(to_char(mr2.fSumAmortAccEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortAccEnd , trim(replace(replace(to_char(mr2.fSumAmortTaxEnd,'999,999,999,990.99'),',',' '),'.',',')) as fSumAmortTaxEnd,au.sDescription,i.sFactoryNum,i.sModel,(select ii.sDescription from asf_invnumb ii where ii.id = i.id) as sPosition,max(tp.sCode) as sCode-- ,EQP_EquipmentAPI.GetIDTypeEquipment_MC(ee.IDTypeEquipment) as IDTypeEquipment_MC , case when ASF.bZalog1(t.idInvNumb, null, au.ddate,1) = 1 then 'V' else '' end as sZalog , case when nvl(i.bIsRealProperty,0) = 1 then 'V' else '' end as bIsRealProperty , case when nvl(r.idstate, 0) = asf_stateapi.findByMnemocode('INPRESERVE') then 'V' else '' end as bConservfromASF_ActUnionObjectDet t,asf_actunionobject au,ASF_InvNumbi,ASF_InvCardClassItem c,Asf_Classifiercf,Asf_Classitem ci,eqp_equipmentee,eqp_techplacetp, (select *fromAsf_Registry rwherer.nperiod = to_number(to_char(:super$dDate, 'yyyymm'))) r, (selectr.asf_invnumb as invnumb, sum(nvl(case when b.sCost in ('BALANCEACC','OUTBALANCEACC') and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceAccEnd,sum(nvl(case when b.sCost in ('BALANCETAX','OUTBALANCETAX') and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumBalanceTaxEnd,sum(nvl(case when b.sCost = 'AMORTACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortAccEnd,sum(nvl(case when b.sCost = 'AMORTTAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortTaxEnd,sum(nvl(case when b.sCost = 'AMORTBONUS'andr.idaccfrom != nvl(s.idretiretax,-1) then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAmortBonusEnd,sum(nvl(case when s.idAdditionalCapitalAcc = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalAccEnd,sum(nvl(case when s.idAdditionalCapitalTax = r.idAccFrom then (2*r.bCredit-1) * r.nSumBase end, 0)) as fSumAddCapitalTaxEnd,sum(nvl(case when b.sCost = 'OUTBALANCEACC' and r.idaccfrom != nvl(s.idretireacc,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceAccEnd,sum(nvl(case when b.sCost = 'OUTBALANCETAX' and r.idaccfrom != nvl(s.idretiretax,-1) then (1-2*r.bCredit) * r.nSumBase end, 0)) as fSumOutBalanceTaxEndfromASF_ActUnionObjectDet a,ACT_EntTransRepFull r, ASF_CostAcc b,BFB_AccAccMap c,ASF_SettingMap s,ASF_DepOwnerChangeSettingMap dwherea.idActUnionObject = :super$idandr.asf_invnumb = a.idinvnumbandto_number(to_char(r.dDate, 'yyyymm')) <= to_number(to_char(:super$dDate, 'yyyymm'))andd.IDDepOwnerInvNumb = :super$idDepOwnerandc.idAccSlave = r.idAccFromandc.idAccOwner = b.idAccandd.IDDepOwnerEntTrans = r.iddepownerandd.IDAdjustMethod = r.idadjustmethodands.iddepowner = d.IDDepOwnerInvNumbgroup by r.ASF_INVNUMB ) mr2 wheret.idActUnionObject = :super$idandt.idInvNumb = i.id(+)andt.idInvNumb = c.idinvcard(+)andc.idclassitem = ci.id(+)andc.idclassifiers = cf.id(+)andt.idInvNumb = r.idInvNumb(+)andt.idInvNumb=mr2.invnumb(+) -- tvorogov - (+) надообязательноand t.idActUnionObject=au.idandt.idInvNumb=ee.idInvNumb(+)andee.IDTechPlace=tp.id(+)group by t.ID,t.idActUnionObject,t.idInvNumb,i.sNumber,i.sCaption,t.idClass,r.nusefullifeacc,r.nusefullifetax,r.nusefullifemng,r.iddepartment,r.idresponsibleperson,r.idbalanceacc,r.idbalancetax,r.idbalancemng,r.idspendacc,r.idspendtax,r.idspendmng,r.idamortacc,r.idamorttax,r.idamortmng,t.nOrder, mr2.fSumBalanceAccEnd, mr2.fSumAmortAccEnd, mr2.fSumBalanceTaxEnd, mr2.fSumAmortTaxEnd, mr2.fSumAmortBonusEnd,au.sDescription,au.DDATE,i.sFactoryNum,i.sModel,i.id,i.nUsefulLifeRef,i.bIsRealProperty,r.idstateorder by t.nOrder --i.sNumber#]