* ВЫБОРКА ОСНОВНЫХ ЗАТРАТ НА ПРОИЗВОДСТВО ИЗДЕЛИЙ за месяц MAT-материалы,KOMPL-комплектующ,ZARPL зарплаты,BRAK-брак
SELECT R_IZD.KODI,SIZD.NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,mg ;
FROM R_IZD INNER JOIN SIZD ON SIZD.KODI = R_IZD.KODI WHERE MG = THISFORM.MESGOD ORDER BY ID_RIZ INTO CURSOR CIZD
IF _TALLY = 0
    RETURN
ENDIF    

*Выборка общезаводских расходов за месяц WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO
SELECT WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW,mg FROM R_ZAW WHERE R_ZAW.mg = THISFORM.MESGOD INTO CURSOR CZAW
IF _TALLY = 0
    RETURN
ENDIF    

*распределение общезаводских расходов за месяц по изделиям пропорционально ......
SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG, ;
 ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,;
 ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG ;
FROM CIZD,CZAW, ;
(SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ;
INTO CURSOR CR1 READWRITE

* подсчет итогов по распределенным общезаводским расходам
SELECT SUM(WZW) WZW,SUM(ENERG) ENERG,SUM(ZARPD) ZARPD,SUM(NALOG) NALOG,SUM(ZATRP) ZATRP,SUM(PROCIE) PROCIE,SUM(WSPOMOG) WSPOMOG FROM CR1 INTO CURSOR CSUM

*РАСЧЕТ  КУРСОР РАСХОЖДЕНИЯ ИТОГОВ
SELECT CSUM
kpl=FCOUNT('CSUM')

TEXT TO POGR noshow
    SELECT 
endtext

FOR i=1  TO kpl
TEXT TO POGR NOSHOW ADDITIVE PRETEXT 1+2+4+8 TEXTMERGE
    <<IIF(I=1,"",",")>>CZAW.<<FIELD(i,'CSUM')>>-CSUM.<<FIELD(i,'CSUM')>> <<FIELD(i,'CSUM')>>
endtext
endfor
TEXT TO POGR NOSHOW additive
 from  CZAW,CSUM INTO CURSOR CRAZN READWRITE
endtext

EXECSCRIPT(POGR)

*Скорректируем  CR1 чтобы полученный итог = общезавод сумме
* для этого полученную разницу по +1(-1) разбросаем в соотв. колонках таблицы CR1
kpl=FCOUNT('CRAZN')
SELECT CR1
skor=1
DO WHILE skor > 0
SCAN
    skor=0 && признак того что  разноска погрешности завершена(сумма абсолютных величин не разнесенных погрешностей )
    FOR i=1 TO kpl
        SELECT CR1
        wyr="repl CR1."+FIELD(i,'CRAZN')+' with CR1.' +FIELD(i,'CRAZN')+' + SIGN(crazn.'+ FIELD(i,'CRAZN')+")"
        EXECSCRIPT(wyr)
        SELECT crazn
        wyr="repl crazn."+FIELD(i,'CRAZN')+' with crazn.' +FIELD(i,'CRAZN')+'- SIGN(crazn.'+ FIELD(i,'CRAZN')+")"
        EXECSCRIPT(wyr)
        wyr='skor=skor+ abs(crazn.'+ FIELD(i,'CRAZN')+')'
        EXECSCRIPT(wyr)
    endfor
    IF skor = 0
        exit
    endif
endscan
enddo
USE IN CRAZN

* Таблица с суммами
Create Cursor curSumm (id i, sumTotal i)
Insert into curSumm values (1, 203)
Insert into curSumm values (1, 301)
Insert into curSumm values (1, 427)

* Таблица со строками
Create Cursor curLine (id i, LineNum i)
Insert into curLine values (1,1)
Insert into curLine values (1,2)

* "Размазываем" сумму по каждой строке
Select ;
    curSumm.id, ;
    curSumm.sumtotal, ;
    curLine.linenum, ;
    Round(curSumm.sumtotal / 2, 0) as sumline ;
from curSumm ;
inner join curLine on curSumm.id = curLine.id

 SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG, ;  
   ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,;  
   ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG ;  
  FROM CIZD,CZAW, ;  
  (SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ;  
  INTO CURSOR CR1 READWRITE  

(SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR

ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG, ;  
ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,;  
ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG 

 * Таблица с суммами
Create Cursor curSumm (id i, sumTotal i)
Insert into curSumm values (1, 203)
Insert into curSumm values (1, 301)
Insert into curSumm values (1, 427)
Insert into curSumm values (2, 203)
Insert into curSumm values (2, 301)

 * Таблица со строками
Create Cursor curLine (id i, LineNum i)
Insert into curLine values (1,1)
Insert into curLine values (1,2)
Insert into curLine values (1,3)
Insert into curLine values (2,1)
Insert into curLine values (2,2)

* Подсчитываем делители для каждого curSumm.id
SELECT id, max(LineNum) PackCount INTO CURSOR curCounts FROM curLine GROUP BY id

* "Размазываем" сумму по каждой строке
Select ;
    curSumm.id, ;
    curSumm.sumtotal, ;
    curLine.linenum, ;
    ICASE(LineNum=1, ;
        curSumm.sumtotal-Round(curSumm.sumtotal / PackCount, 0)*(PackCount - 1), ;
        Round(curSumm.sumtotal / PackCount, 0) ;
        ) as sumline, ;
    PackCount ;
from curSumm ;
inner join curLine on curSumm.id = curLine.id ;
inner join curCounts on curSumm.id = curCounts.id

*СОЗДАНИЕ КУРСОРА  R_IZD ПОИЗДЕЛЬНЫЕ РАСХОДЫ
SELE 0
CREATE CURSOR R_IZD (MG D(8),KODI N(3),KOL N(10),MAT N(18,2),KOMPL N(18,2),ZARPL N(18,2),BRAK N(18,2),ID_RIZ I(4))
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},97,158023,25734730.00,8045582.00,10990993.00,0.00,5)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},98,941578,153339965.00,47939466.00,37652998.00,0.00,6)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},36,0,0.00,0.00,215311.00,0.00,7)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},39,0,0.00,0.00,449921.00,0.00,8)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},22,6,494504024.00,1690053099.00,114482658.00,0.00,9)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},21,4,319962034.00,1103246177.00,75080204.00,0.00,10)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},57,50,642315236.00,518212438.00,101247100.00,95973.00,11)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},66,0,0.00,0.00,2769937.00,0.00,12)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},4,9,640657365.00,2568605824.00,49814370.00,0.00,13)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},54,6,102039724.00,68890506.00,17994804.00,11517.00,14)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},62,2,19419596.00,4954984.00,1329646.00,0.00,15)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},53,0,17330467.00,11481751.00,3143147.00,1919.00,16)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},38,3,30611434.00,12017892.00,3397599.00,0.00,17)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},39,0,11707406.00,0.00,4660935.00,0.00,18)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},45,2,31365714.00,9948195.00,3363200.00,0.00,19)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},43,4,82071738.00,22064890.00,12581699.00,0.00,20)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},52,23,326890881.00,228354598.00,68646233.00,-20.00,21)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},58,10,139964888.00,93725323.00,23858510.00,0.00,22)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},53,0,1238571.00,0.00,3462356.00,0.00,23)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},55,0,420540.00,0.00,1186061.00,0.00,24)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},61,0,1010417.00,1375.00,477406.00,0.00,25)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},3,0,5969222.00,42935905.00,14915533.00,0.00,27)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},97,236247,120969200.00,45224888.00,14968135.00,0.00,28)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},98,845102,169669322.00,63431653.00,53242028.00,0.00,29)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},67,0,3994479.00,464113.00,0.00,0.00,30)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},21,0,0.00,43751394.00,0.00,0.00,31)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},22,0,-72160.00,134308559.00,0.00,0.00,32)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},97,36193,9885162.00,2075866.00,7542335.00,0.00,33)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},98,1069974,292237073.00,61369261.00,60565028.00,0.00,34)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},1,0,395862.00,0.00,882170.00,0.00,35)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},3,0,5552030.00,0.00,14509349.00,0.00,36)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},21,10,927846782.00,3277690098.00,206470560.00,0.00,37)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},22,3,289214730.00,934184795.00,62965461.00,0.00,38)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},23,0,0.00,0.00,3013544.00,0.00,39)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},67,0,0.00,0.00,1574275.00,0.00,40)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},36,1,8450273.00,4923126.00,867247.00,0.00,41)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},43,9,156148597.00,61161384.00,19633599.00,0.00,42)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},45,1,15988327.00,6301706.00,1681600.00,0.00,43)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},55,0,1680548.00,0.00,6454095.00,0.00,44)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},52,2,31314495.00,44261550.00,20067287.00,0.00,45)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},53,26,456978741.00,335828754.00,89894012.00,0.00,46)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},59,4,70304423.00,51665962.00,16229848.00,0.00,47)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},61,5,52226001.00,14531663.00,4062805.00,0.00,48)
INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},58,0,-37653500.00,10447437.00,0.00,0.00,49)

*СОЗДАНИЕ КУРСОРА  R_ZAW ОБЩЕЗАВОДСКИЕ РАСХОДЫ
 
SELE 0
CREATE CURSOR R_ZAW (MG D(8),WZW N(18,2),ENERG N(18,2),ZARPD N(18,2),NALOG N(18,2),ZATRP N(18,2),PROCIE N(18,2),WSPOMOG N(18,2),KOMERC N(18,2),ZATRO N(18,2),ID_RZW I(4))
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-2-1},-48831992.00,295962897.00,93783510.00,235436065.00,1531631710.00,0.00,0.00,156066710.00,852104801.00,4)
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-1-1},-24749255.00,262416138.00,99334934.00,162673602.00,1287188920.00,0.00,0.00,101508082.00,667290409.00,5)
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-2-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,6)
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-5-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,7)
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-12-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,8)
INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-3-1},-24148365.00,258492891.00,77093513.00,223540698.00,1361506990.00,0.00,0.00,194836002.00,693881847.00,9)


*СОЗДАНИЕ КУРСОРА  SIZD СПРАВОЧНИК ИЗДЕЛИЙ
 
SELE 0
CREATE CURSOR SIZD (KODI N(3),NAME C(45),GR N(2),KODW N(3))
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (1,'АПП-6А-01                                    ',1,20)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (3,'АПП-6Д                                       ',1,20)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (4,'АПП-6Д  БЕЗ ШЛАНГОВ И ВАКУУМОПРОВОДА         ',1,20)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (21,'КОМБАЙН ЛИДА-1300-06-6                       ',2,35)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (22,'КОМБАЙН ЛИДА 1300-06-7                       ',2,35)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (23,'КОМБАЙН ЛИДА-1300-07-5                       ',2,35)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (36,'СЕЯЛКА СПУ-3                                 ',3,50)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (38,'СЕЯЛКА СПУ-4                                 ',3,50)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (39,'СЕЯЛКА СПУ-4Д                                ',3,50)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (43,'СЕЯЛКА СПУ-6Д                                ',3,50)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (45,'СЕЯЛКА СПУ-6Л                                ',3,50)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (52,'СЕЯЛКА СТВ-8К                                ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (53,'СЕЯЛКА СТВ-8КУ                               ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (54,'СЕЯЛКА СТВ-8КУ БЕЗ ШЛАНГОВ СЕМЯПРОВОДА       ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (55,'СЕЯЛКА СТВ-12                                ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (57,'СЕЯЛКА СТВ-8К БЕЗ ШЛАНГОВ                    ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (58,'СЕЯЛКА СТВ-8К В РАЗОБРАН.ВИДЕ                ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (59,'СЕЯЛКА СТВ-8КУ В РАЗОБРАН.ВИДЕ               ',4,60)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (61,'СЕЯЛКА СУ-12-01                              ',5,65)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (62,'СЕЯЛКА СУ-12 БЕЗ ШЛАНГОВ СЕМЯПРОВОДА         ',5,65)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (66,'ПРИЦЕП 2ПТС-14                               ',6,70)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (67,'ПРИЦЕП 2ПТС-14 В РАЗОБР.ВИДЕ                 ',6,70)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (97,'ЗАПЧАСТИ К ЗЕРНОУБОРОЧНОЙ ТЕХНИКЕ            ',97,97)
INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (98,'ЗАПЧАСТИ К ПОСЕВНОЙ ТЕХНИКЕ                  ',98,98)


MG_OTC={^2012-3-1} && МЕСЯЦ ОТЧЕТНЫЙ

* ВЫБОРКА ОСНОВНЫХ ЗАТРАТ НА ПРОИЗВОДСТВО ИЗДЕЛИЙ за месяц MAT-материалы,KOMPL-комплектующ,ZARPL зарплаты,BRAK-брак  
  SELECT R_IZD.KODI,SIZD.NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,mg ;  
  FROM R_IZD INNER JOIN SIZD ON SIZD.KODI = R_IZD.KODI WHERE MG = MG_OTC ORDER BY ID_RIZ INTO CURSOR CIZD  
  IF _TALLY = 0  
      RETURN  
  ENDIF      
    
 *Выборка общезаводских расходов за месяц WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO  
  SELECT WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW,mg FROM R_ZAW WHERE R_ZAW.mg = MG_OTC INTO CURSOR CZAW  
  IF _TALLY = 0  
      RETURN  
  ENDIF      
    
 *распределение общезаводских расходов за месяц по изделиям пропорционально ......  
  SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG, ;  
   ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,;  
   ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG ;  
  FROM CIZD,CZAW, ;  
  (SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ;  
  INTO CURSOR CR1 READWRITE  
    
 * подсчет итогов по распределенным общезаводским расходам  
  SELECT SUM(WZW) WZW,SUM(ENERG) ENERG,SUM(ZARPD) ZARPD,SUM(NALOG) NALOG,SUM(ZATRP) ZATRP,SUM(PROCIE) PROCIE,SUM(WSPOMOG) WSPOMOG FROM CR1 INTO CURSOR CSUM  
    
 *РАСЧЕТ  КУРСОР РАСХОЖДЕНИЯ ИТОГОВ  
  SELECT CSUM  
  kpl=FCOUNT('CSUM')  
    
  TEXT TO POGR noshow  
      SELECT   
  endtext  
    
  FOR i=1  TO kpl  
  TEXT TO POGR NOSHOW ADDITIVE PRETEXT 1+2+4+8 TEXTMERGE  
      <<IIF(I=1,"",",")>>CZAW.<<FIELD(i,'CSUM')>>-CSUM.<<FIELD(i,'CSUM')>> <<FIELD(i,'CSUM')>>  
  endtext  
  endfor  
  TEXT TO POGR NOSHOW additive  
   from  CZAW,CSUM INTO CURSOR CRAZN READWRITE  
  endtext  
    
  EXECSCRIPT(POGR)  
    
 *Скорректируем  CR1 чтобы полученный итог = общезавод сумме  
 * для этого полученную разницу по +1(-1) разбросаем в соотв. колонках таблицы CR1  
  kpl=FCOUNT('CRAZN')  
  SELECT CR1  
  skor=1  
  DO WHILE skor > 0  
  SCAN  
      skor=0 && признак того что  разноска погрешности завершена(сумма абсолютных величин не разнесенных погрешностей )  
      FOR i=1 TO kpl  
          SELECT CR1  
          wyr="repl CR1."+FIELD(i,'CRAZN')+' with CR1.' +FIELD(i,'CRAZN')+' + SIGN(crazn.'+ FIELD(i,'CRAZN')+")"  
          EXECSCRIPT(wyr)  
          SELECT crazn  
          wyr="repl crazn."+FIELD(i,'CRAZN')+' with crazn.' +FIELD(i,'CRAZN')+'- SIGN(crazn.'+ FIELD(i,'CRAZN')+")"  
          EXECSCRIPT(wyr)  
          wyr='skor=skor+ abs(crazn.'+ FIELD(i,'CRAZN')+')'  
          EXECSCRIPT(wyr)  
      endfor  
      IF skor = 0  
          exit  
      endif  
  endscan  
  enddo  
  USE IN CRAZN
SELECT CR1
BROWSE

*СОЗДАНИЕ КУРСОРА  R_IZD ПОИЗДЕЛЬНЫЕ РАСХОДЫ  
  SELE 0  
  CREATE CURSOR R_IZD (MG D(8),KODI N(3),KOL N(10),MAT N(18,2),KOMPL N(18,2),ZARPL N(18,2),BRAK N(18,2),ID_RIZ I(4))  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},97,158023,25734730.00,8045582.00,10990993.00,0.00,5)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},98,941578,153339965.00,47939466.00,37652998.00,0.00,6)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},36,0,0.00,0.00,215311.00,0.00,7)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},39,0,0.00,0.00,449921.00,0.00,8)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},22,6,494504024.00,1690053099.00,114482658.00,0.00,9)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},21,4,319962034.00,1103246177.00,75080204.00,0.00,10)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},57,50,642315236.00,518212438.00,101247100.00,95973.00,11)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},66,0,0.00,0.00,2769937.00,0.00,12)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},4,9,640657365.00,2568605824.00,49814370.00,0.00,13)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},54,6,102039724.00,68890506.00,17994804.00,11517.00,14)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},62,2,19419596.00,4954984.00,1329646.00,0.00,15)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-1-1},53,0,17330467.00,11481751.00,3143147.00,1919.00,16)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},38,3,30611434.00,12017892.00,3397599.00,0.00,17)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},39,0,11707406.00,0.00,4660935.00,0.00,18)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},45,2,31365714.00,9948195.00,3363200.00,0.00,19)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},43,4,82071738.00,22064890.00,12581699.00,0.00,20)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},52,23,326890881.00,228354598.00,68646233.00,-20.00,21)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},58,10,139964888.00,93725323.00,23858510.00,0.00,22)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},53,0,1238571.00,0.00,3462356.00,0.00,23)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},55,0,420540.00,0.00,1186061.00,0.00,24)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},61,0,1010417.00,1375.00,477406.00,0.00,25)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},3,0,5969222.00,42935905.00,14915533.00,0.00,27)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},97,236247,120969200.00,45224888.00,14968135.00,0.00,28)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},98,845102,169669322.00,63431653.00,53242028.00,0.00,29)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},67,0,3994479.00,464113.00,0.00,0.00,30)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},21,0,0.00,43751394.00,0.00,0.00,31)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-2-1},22,0,-72160.00,134308559.00,0.00,0.00,32)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},97,36193,9885162.00,2075866.00,7542335.00,0.00,33)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},98,1069974,292237073.00,61369261.00,60565028.00,0.00,34)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},1,0,395862.00,0.00,882170.00,0.00,35)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},3,0,5552030.00,0.00,14509349.00,0.00,36)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},21,10,927846782.00,3277690098.00,206470560.00,0.00,37)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},22,3,289214730.00,934184795.00,62965461.00,0.00,38)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},23,0,0.00,0.00,3013544.00,0.00,39)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},67,0,0.00,0.00,1574275.00,0.00,40)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},36,1,8450273.00,4923126.00,867247.00,0.00,41)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},43,9,156148597.00,61161384.00,19633599.00,0.00,42)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},45,1,15988327.00,6301706.00,1681600.00,0.00,43)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},55,0,1680548.00,0.00,6454095.00,0.00,44)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},52,2,31314495.00,44261550.00,20067287.00,0.00,45)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},53,26,456978741.00,335828754.00,89894012.00,0.00,46)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},59,4,70304423.00,51665962.00,16229848.00,0.00,47)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},61,5,52226001.00,14531663.00,4062805.00,0.00,48)  
  INSERT INTO R_IZD (MG,KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ) VALUES ({^2012-3-1},58,0,-37653500.00,10447437.00,0.00,0.00,49)  
    
 *СОЗДАНИЕ КУРСОРА  R_ZAW ОБЩЕЗАВОДСКИЕ РАСХОДЫ  
     
  SELE 0  
  CREATE CURSOR R_ZAW (MG D(8),WZW N(18,2),ENERG N(18,2),ZARPD N(18,2),NALOG N(18,2),ZATRP N(18,2),PROCIE N(18,2),WSPOMOG N(18,2),KOMERC N(18,2),ZATRO N(18,2),ID_RZW I(4))  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-2-1},-48831992.00,295962897.00,93783510.00,235436065.00,1531631710.00,0.00,0.00,156066710.00,852104801.00,4)  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-1-1},-24749255.00,262416138.00,99334934.00,162673602.00,1287188920.00,0.00,0.00,101508082.00,667290409.00,5)  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-2-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,6)  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-5-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,7)  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2011-12-1},0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,8)  
  INSERT INTO R_ZAW (MG,WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW) VALUES ({^2012-3-1},-24148365.00,258492891.00,77093513.00,223540698.00,1361506990.00,0.00,0.00,194836002.00,693881847.00,9)  
    
    
 *СОЗДАНИЕ КУРСОРА  SIZD СПРАВОЧНИК ИЗДЕЛИЙ  
     
  SELE 0  
  CREATE CURSOR SIZD (KODI N(3),NAME C(45),GR N(2),KODW N(3))  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (1,'АПП-6А-01                                    ',1,20)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (3,'АПП-6Д                                       ',1,20)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (4,'АПП-6Д  БЕЗ ШЛАНГОВ И ВАКУУМОПРОВОДА         ',1,20)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (21,'КОМБАЙН ЛИДА-1300-06-6                       ',2,35)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (22,'КОМБАЙН ЛИДА 1300-06-7                       ',2,35)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (23,'КОМБАЙН ЛИДА-1300-07-5                       ',2,35)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (36,'СЕЯЛКА СПУ-3                                 ',3,50)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (38,'СЕЯЛКА СПУ-4                                 ',3,50)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (39,'СЕЯЛКА СПУ-4Д                                ',3,50)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (43,'СЕЯЛКА СПУ-6Д                                ',3,50)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (45,'СЕЯЛКА СПУ-6Л                                ',3,50)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (52,'СЕЯЛКА СТВ-8К                                ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (53,'СЕЯЛКА СТВ-8КУ                               ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (54,'СЕЯЛКА СТВ-8КУ БЕЗ ШЛАНГОВ СЕМЯПРОВОДА       ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (55,'СЕЯЛКА СТВ-12                                ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (57,'СЕЯЛКА СТВ-8К БЕЗ ШЛАНГОВ                    ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (58,'СЕЯЛКА СТВ-8К В РАЗОБРАН.ВИДЕ                ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (59,'СЕЯЛКА СТВ-8КУ В РАЗОБРАН.ВИДЕ               ',4,60)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (61,'СЕЯЛКА СУ-12-01                              ',5,65)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (62,'СЕЯЛКА СУ-12 БЕЗ ШЛАНГОВ СЕМЯПРОВОДА         ',5,65)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (66,'ПРИЦЕП 2ПТС-14                               ',6,70)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (67,'ПРИЦЕП 2ПТС-14 В РАЗОБР.ВИДЕ                 ',6,70)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (97,'ЗАПЧАСТИ К ЗЕРНОУБОРОЧНОЙ ТЕХНИКЕ            ',97,97)  
  INSERT INTO SIZD (KODI,NAME,GR,KODW) VALUES (98,'ЗАПЧАСТИ К ПОСЕВНОЙ ТЕХНИКЕ                  ',98,98)  
    
    
  MG_OTC={^2012-3-1} && МЕСЯЦ ОТЧЕТНЫЙ  
    
 * ВЫБОРКА ОСНОВНЫХ ЗАТРАТ НА ПРОИЗВОДСТВО ИЗДЕЛИЙ за месяц MAT-материалы,KOMPL-комплектующ,ZARPL зарплаты,BRAK-брак    
    SELECT R_IZD.KODI,SIZD.NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,mg ;    
    FROM R_IZD INNER JOIN SIZD ON SIZD.KODI = R_IZD.KODI WHERE MG = MG_OTC ORDER BY ID_RIZ INTO CURSOR CIZD    
    IF _TALLY = 0    
        RETURN    
    ENDIF        
        
  *Выборка общезаводских расходов за месяц WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO    
    SELECT WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG,KOMERC,ZATRO,ID_RZW,mg FROM R_ZAW WHERE R_ZAW.mg = MG_OTC INTO CURSOR CZAW    
    IF _TALLY = 0    
        RETURN    
    ENDIF        
        
  *распределение общезаводских расходов за месяц по изделиям пропорционально ......    
    SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG, ;    
     ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,;    
     ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG ;    
    FROM CIZD,CZAW, ;    
    (SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ;    
    INTO CURSOR CR1 

  
SELECT CR1.*,RECNO() ID_ZAP FROM CR1 INTO CURSOR CR2 READWRITE 
KLZAP=_TALLY  

       
* подсчет итогов по распределенным общезаводским расходам    
    SELECT SUM(WZW) WZW,SUM(ENERG) ENERG,SUM(ZARPD) ZARPD,SUM(NALOG) NALOG,SUM(ZATRP) ZATRP,SUM(PROCIE) PROCIE,SUM(WSPOMOG) WSPOMOG FROM CR2 INTO CURSOR CSUM    
        
  *РАСЧЕТ  КУРСОР РАСХОЖДЕНИЯ ИТОГОВ    
    SELECT     CZAW.WZW-CSUM.WZW WZW,CZAW.ENERG-CSUM.ENERG ENERG,CZAW.ZARPD-CSUM.ZARPD ZARPD,CZAW.NALOG-CSUM.NALOG NALOG,CZAW.ZATRP-CSUM.ZATRP ZATRP ,;
    CZAW.PROCIE-CSUM.PROCIE PROCIE,CZAW.WSPOMOG-CSUM.WSPOMOG WSPOMOG from  CZAW,CSUM INTO CURSOR CRAZN 

*Скорректируем  CR2 чтобы полученный итог = общезавод сумме    
UPDATE CR2 SET  WZW = WZW+ INT(CRAZN.WZW/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.WZW),KLZAP),0,SIGN(CRAZN.WZW)), ;
                ENERG = ENERG+ INT(CRAZN.ENERG/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.ENERG),KLZAP),0,SIGN(CRAZN.ENERG)), ;
                ZARPD = ZARPD+ INT(CRAZN.ZARPD/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.ZARPD),KLZAP),0,SIGN(CRAZN.ZARPD)), ;
                NALOG = NALOG+ INT(CRAZN.NALOG/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.NALOG),KLZAP),0,SIGN(CRAZN.NALOG)), ;
                ZATRP = ZATRP+ INT(CRAZN.ZATRP/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.ZATRP),KLZAP),0,SIGN(CRAZN.ZATRP)), ;
                PROCIE = PROCIE+ INT(CRAZN.PROCIE/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.PROCIE),KLZAP),0,SIGN(CRAZN.PROCIE)), ;
                WSPOMOG = WSPOMOG+ INT(CRAZN.WSPOMOG/KLZAP)+IIF(CR2.ID_ZAP > MOD(ABS(CRAZN.WSPOMOG),KLZAP),0,SIGN(CRAZN.WSPOMOG))

SELECT CR2
BROW

SELECT CR2.KODI,CR2.NAME,CR2.KOL,CR2.MAT,CR2.KOMPL,CR2.ZARPL,CR2.BRAK,
    CR2.WZW+TRUNC(CRAZN.WZW/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.WZW),KLZAP)),1,0,SIGN(CRAZN.WZW)) WZW,
    CR2.ENERG+TRUNC(CRAZN.ENERG/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.ENERG),KLZAP)),1,0,SIGN(CRAZN.ENERG)) ENERG,       
    CR2.ZARPD+TRUNC(CRAZN.ZARPD/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.ZARPD),KLZAP)),1,0,SIGN(CRAZN.ZARPD)) ZARPD,
    CR2.NALOG+TRUNC(CRAZN.NALOG/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.NALOG),KLZAP)),1,0,SIGN(CRAZN.NALOG)) NALOG,      
    CR2.ZATRP+TRUNC(CRAZN.ZATRP/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.ZATRP),KLZAP)),1,0,SIGN(CRAZN.ZATRP)) ZATRP, 
    CR2.PROCIE+TRUNC(CRAZN.PROCIE/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.PROCIE),KLZAP)),1,0,SIGN(CRAZN.PROCIE)) PROCIE,
    CR2.WSPOMOG+TRUNC(CRAZN.WSPOMOG/CCNT.KLZAP)+DECODE(SIGN(CR2.ID_ZAP - MOD(ABS(CRAZN.WSPOMOG),KLZAP)),1,0,SIGN(CRAZN.WSPOMOG)) WSPOMOG
    FROM 
(
SELECT CZAW.WZW-CSUM.WZW WZW,CZAW.ENERG-CSUM.ENERG ENERG,CZAW.ZARPD-CSUM.ZARPD ZARPD,CZAW.NALOG-CSUM.NALOG NALOG,CZAW.ZATRP-CSUM.ZATRP ZATRP ,  
      CZAW.PROCIE-CSUM.PROCIE PROCIE,CZAW.WSPOMOG-CSUM.WSPOMOG WSPOMOG from  CZAW,

(SELECT SUM(WZW) WZW,SUM(ENERG) ENERG,SUM(ZARPD) ZARPD,SUM(NALOG) NALOG,SUM(ZATRP) ZATRP,SUM(PROCIE) PROCIE,SUM(WSPOMOG) WSPOMOG FROM 

(SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG,       
       ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,      
       ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG       
      FROM CIZD,CZAW,      
      (SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ) CR1 ) CSUM ) CRAZN,
      (SELECT COUNT(*) KLZAP FROM CIZD) CCNT ,
(SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG,       
       ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,      
       ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG,
       ROWNUM ID_ZAP FROM CIZD,CZAW,      
      (SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD ) CZAR ) CR2 

WITH c1 AS (SELECT .... FROM ...),
c2 AS (SELECT ... FROM ... даже c1 можно сюда приспособить),
c3 AS (...)
SELECT окончательный запрос FROM с1 INNER JOIN с2 ON ... INNER JOIN c3 ON ...

SELECT * FROM ( WITH R_IZD AS (
SELECT TO_DATE('01-03-2012') MG,97 KODI,36193 KOL,9885162.00 MAT,2075866.00 KOMPL,7542335.00 ZARPL,0.00 BRAK,33 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,98 KODI,1069974 KOL,292237073.00 MAT,61369261.00 KOMPL,60565028.00 ZARPL,0.00 BRAK,34 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,1 KODI,0 KOL,395862.00 MAT,0.00 KOMPL,882170.00 ZARPL,0.00 BRAK,35 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,3 KODI,0 KOL,5552030.00 MAT,0.00 KOMPL,14509349.00 ZARPL,0.00 BRAK,36 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,21 KODI,10 KOL,927846782.00 MAT,3277690098.00 KOMPL,206470560.00 ZARPL,0.00 BRAK,37 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,22 KODI,3 KOL,289214730.00 MAT,934184795.00 KOMPL,62965461.00 ZARPL,0.00 BRAK,38 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,23 KODI,0 KOL,0.00 MAT,0.00 KOMPL,3013544.00 ZARPL,0.00 BRAK,39 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,67 KODI,0 KOL,0.00 MAT,0.00 KOMPL,1574275.00 ZARPL,0.00 BRAK,40 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,36 KODI,1 KOL,8450273.00 MAT,4923126.00 KOMPL,867247.00 ZARPL,0.00 BRAK,41 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,43 KODI,9 KOL,156148597.00 MAT,61161384.00 KOMPL,19633599.00 ZARPL,0.00 BRAK,42 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,45 KODI,1 KOL,15988327.00 MAT,6301706.00 KOMPL,1681600.00 ZARPL,0.00 BRAK,43 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,55 KODI,0 KOL,1680548.00 MAT,0.00 KOMPL,6454095.00 ZARPL,0.00 BRAK,44 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,52 KODI,2 KOL,31314495.00 MAT,44261550.00 KOMPL,20067287.00 ZARPL,0.00 BRAK,45 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,53 KODI,26 KOL,456978741.00 MAT,335828754.00 KOMPL,89894012.00 ZARPL,0.00 BRAK,46 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,59 KODI,4 KOL,70304423.00 MAT,51665962.00 KOMPL,16229848.00 ZARPL,0.00 BRAK,47 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,61 KODI,5 KOL,52226001.00 MAT,14531663.00 KOMPL,4062805.00 ZARPL,0.00 BRAK,48 ID_RIZ  FROM DUAL
UNION ALL
SELECT TO_DATE('01-03-2012') MG,58 KODI,0 KOL,-37653500.00 MAT,10447437.00 KOMPL,0.00 ZARPL,0.00 BRAK,49 ID_RIZ  FROM DUAL
),
R_ZAW AS (
SELECT TO_DATE('01-03-2012') MG,-24148365.00 WZW,258492891.00 ENERG,77093513.00 ZARPD,223540698.00 NALOG,1361506990.00 ZATRP,0.00 PROCIE,0.00 WSPOMOG,194836002.00 KOMERC,693881847.00 ZATRO,9 ID_RZW  FROM DUAL
),
SIZD AS (
SELECT 1 KODI,'АПП-6А-01                                    '  NAME,1 GR,20 KODW  FROM DUAL
UNION ALL
SELECT 3 KODI,'АПП-6Д                                       '  NAME,1 GR,20 KODW  FROM DUAL
UNION ALL
SELECT 4 KODI,'АПП-6Д  БЕЗ ШЛАНГОВ И ВАКУУМОПРОВОДА         '  NAME,1 GR,20 KODW  FROM DUAL
UNION ALL
SELECT 21 KODI,'КОМБАЙН ЛИДА-1300-06-6                       '  NAME,2 GR,35 KODW  FROM DUAL
UNION ALL
SELECT 22 KODI,'КОМБАЙН ЛИДА 1300-06-7                       '  NAME,2 GR,35 KODW  FROM DUAL
UNION ALL
SELECT 23 KODI,'КОМБАЙН ЛИДА-1300-07-5                       '  NAME,2 GR,35 KODW  FROM DUAL
UNION ALL
SELECT 36 KODI,'СЕЯЛКА СПУ-3                                 '  NAME,3 GR,50 KODW  FROM DUAL
UNION ALL
SELECT 38 KODI,'СЕЯЛКА СПУ-4                                 '  NAME,3 GR,50 KODW  FROM DUAL
UNION ALL
SELECT 39 KODI,'СЕЯЛКА СПУ-4Д                                '  NAME,3 GR,50 KODW  FROM DUAL
UNION ALL
SELECT 43 KODI,'СЕЯЛКА СПУ-6Д                                '  NAME,3 GR,50 KODW  FROM DUAL
UNION ALL
SELECT 45 KODI,'СЕЯЛКА СПУ-6Л                                '  NAME,3 GR,50 KODW  FROM DUAL
UNION ALL
SELECT 52 KODI,'СЕЯЛКА СТВ-8К                                '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 53 KODI,'СЕЯЛКА СТВ-8КУ                               '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 54 KODI,'СЕЯЛКА СТВ-8КУ БЕЗ ШЛАНГОВ СЕМЯПРОВОДА       '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 55 KODI,'СЕЯЛКА СТВ-12                                '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 57 KODI,'СЕЯЛКА СТВ-8К БЕЗ ШЛАНГОВ                    '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 58 KODI,'СЕЯЛКА СТВ-8К В РАЗОБРАН.ВИДЕ                '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 59 KODI,'СЕЯЛКА СТВ-8КУ В РАЗОБРАН.ВИДЕ               '  NAME,4 GR,60 KODW  FROM DUAL
UNION ALL
SELECT 61 KODI,'СЕЯЛКА СУ-12-01                              '  NAME,5 GR,65 KODW  FROM DUAL
UNION ALL
SELECT 62 KODI,'СЕЯЛКА СУ-12 БЕЗ ШЛАНГОВ СЕМЯПРОВОДА         '  NAME,5 GR,65 KODW  FROM DUAL
UNION ALL
SELECT 66 KODI,'ПРИЦЕП 2ПТС-14                               '  NAME,6 GR,70 KODW  FROM DUAL
UNION ALL
SELECT 67 KODI,'ПРИЦЕП 2ПТС-14 В РАЗОБР.ВИДЕ                 '  NAME,6 GR,70 KODW  FROM DUAL
UNION ALL
SELECT 97 KODI,'ЗАПЧАСТИ К ЗЕРНОУБОРОЧНОЙ ТЕХНИКЕ            '  NAME,97 GR,97 KODW  FROM DUAL
UNION ALL
SELECT 98 KODI,'ЗАПЧАСТИ К ПОСЕВНОЙ ТЕХНИКЕ                  '  NAME,98 GR,98 KODW  FROM DUAL
),
CIZD AS (
SELECT R_IZD.KODI,SIZD.NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,mg      
      FROM R_IZD INNER JOIN SIZD ON SIZD.KODI = R_IZD.KODI ORDER BY ID_RIZ
),
CZAR AS (
SELECT SUM(ZARPL) ITZ,SUM(MAT) ITM FROM CIZD
),
CR AS (
 SELECT KODI,NAME,KOL,MAT,KOMPL,ZARPL,BRAK,ROUND(CIZD.MAT*WZW/CZAR.ITM,0) WZW,ROUND(CIZD.ZARPL*ENERG/CZAR.ITZ,0) ENERG,  
       ROUND(CIZD.ZARPL*ZARPD/CZAR.ITZ,0) ZARPD,ROUND(CIZD.ZARPL*NALOG/CZAR.ITZ,0) NALOG,      
       ROUND(CIZD.ZARPL*ZATRP/CZAR.ITZ,0) ZATRP,ROUND(CIZD.ZARPL*PROCIE/CZAR.ITZ,0) PROCIE,ROUND(CIZD.ZARPL*WSPOMOG/CZAR.ITZ,0) WSPOMOG,  
         ROWNUM ID_ZAP        
      FROM CIZD,R_ZAW,CZAR      
),
CSUM AS (
SELECT SUM(WZW) WZW,SUM(ENERG) ENERG,SUM(ZARPD) ZARPD,SUM(NALOG) NALOG,SUM(ZATRP) ZATRP,SUM(PROCIE) PROCIE,SUM(WSPOMOG) WSPOMOG FROM CR
),
CRAZN AS (
SELECT R_ZAW.WZW-CSUM.WZW WZW,R_ZAW.ENERG-CSUM.ENERG ENERG,R_ZAW.ZARPD-CSUM.ZARPD ZARPD,R_ZAW.NALOG-CSUM.NALOG NALOG,R_ZAW.ZATRP-CSUM.ZATRP ZATRP ,    
        R_ZAW.PROCIE-CSUM.PROCIE PROCIE,R_ZAW.WSPOMOG-CSUM.WSPOMOG WSPOMOG from  R_ZAW,CSUM 
),
CCNT AS (
SELECT COUNT(*) KLZAP FROM CIZD
),
CR1 AS (
SELECT CR.KODI,CR.NAME,CR.KOL,CR.MAT,CR.KOMPL,CR.ZARPL,CR.BRAK,  
      CR.WZW+TRUNC(CRAZN.WZW/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.WZW),KLZAP)),1,0,SIGN(CRAZN.WZW)) WZW,  
      CR.ENERG+TRUNC(CRAZN.ENERG/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.ENERG),KLZAP)),1,0,SIGN(CRAZN.ENERG)) ENERG,         
      CR.ZARPD+TRUNC(CRAZN.ZARPD/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.ZARPD),KLZAP)),1,0,SIGN(CRAZN.ZARPD)) ZARPD,  
      CR.NALOG+TRUNC(CRAZN.NALOG/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.NALOG),KLZAP)),1,0,SIGN(CRAZN.NALOG)) NALOG,        
      CR.ZATRP+TRUNC(CRAZN.ZATRP/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.ZATRP),KLZAP)),1,0,SIGN(CRAZN.ZATRP)) ZATRP,   
      CR.PROCIE+TRUNC(CRAZN.PROCIE/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.PROCIE),KLZAP)),1,0,SIGN(CRAZN.PROCIE)) PROCIE,  
      CR.WSPOMOG+TRUNC(CRAZN.WSPOMOG/CCNT.KLZAP)+DECODE(SIGN(CR.ID_ZAP - MOD(ABS(CRAZN.WSPOMOG),KLZAP)),1,0,SIGN(CRAZN.WSPOMOG)) WSPOMOG  
      FROM CR,CRAZN,CCNT
)      
SELECT CR1.* FROM CR1 )

SELE 0
CREATE CURSOR CRAS (NMAT C(42),ED_IZM C(10),CENA N(8,2),KR N(8,4),SR N(20,2),NAMR C(34),KOL N(8,4),SI N(8,2),NZ I(4))
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'Сеялка спу-4д                     ',6.4512,4.52,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'Сеялка ств-8ку точного высева     ',2.2176,1.55,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'Сеялка спу-6д-01                  ',5.4432,3.81,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'Сеялка ств-6к                     ',0.1512,0.11,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'З/Ч                               ',7.8372,5.49,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'Заказ № 421 о переоборудовании    ',0.3024,0.21,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Заклепка 6*22 Ст1 ГОСТ 10300-80 П         ','КГ        ',0.70,22.7052,15.89,'СЕРВИС                            ',0.3024,0.21,4)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА  ПРУЖИН. 1,8ММ                  ','          ',1.43,1.0560,1.51,'Сеялка ств-8ку точного высева     ',0.9540,1.37,18)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА  ПРУЖИН. 1,8ММ                  ','          ',1.43,1.0560,1.51,'Сеялка ств-6к                     ',0.1020,0.15,18)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'Сеялка спу-4д                     ',7.0720,22.49,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'Сеялка ств-8ку точного высева     ',2.1956,6.98,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'Сеялка ств-8ку точного высева     ',3.5200,11.19,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'Сеялка спу-6д-01                  ',1.7964,5.71,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'Сеялка ств-6к                     ',0.1996,0.63,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              ','          ',3.18,15.2256,48.42,'З/Ч                               ',0.4420,1.41,20)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРУТОК  БРАЖ  9-4  20ММ                   ','КГ        ',15.50,12.0275,186.43,'Сеялка спу-4д                     ',1.4720,22.82,23)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРУТОК  БРАЖ  9-4  20ММ                   ','КГ        ',15.50,12.0275,186.43,'Сеялка ств-8ку точного высева     ',8.0700,125.09,23)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРУТОК  БРАЖ  9-4  20ММ                   ','КГ        ',15.50,12.0275,186.43,'Сеялка спу-6д-01                  ',1.6560,25.67,23)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРУТОК  БРАЖ  9-4  20ММ                   ','КГ        ',15.50,12.0275,186.43,'Сеялка ств-6к                     ',0.7110,11.02,23)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('ПРУТОК  БРАЖ  9-4  20ММ                   ','КГ        ',15.50,12.0275,186.43,'З/Ч                               ',0.1185,1.84,23)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Проволока пружин.  2.2мм                  ','Кг        ',0.19,0.1785,0.03,'Сеялка спу-4д                     ',0.0816,0.02,30)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Проволока пружин.  2.2мм                  ','Кг        ',0.19,0.1785,0.03,'Сеялка спу-6д-01                  ',0.0459,0.01,30)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Проволока пружин.  2.2мм                  ','Кг        ',0.19,0.1785,0.03,'З/Ч                               ',0.0510,0.01,30)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Сеялка ств-8ку точного высева     ',0.2640,0.15,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Сеялка ств-8ку точного высева     ',1.1000,0.63,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Ппр-145 пресс-подборщик / эксп.   ',0.0336,0.02,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Ппр-145 пресс-подборщик / эксп.   ',0.0960,0.06,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Сеялка ств-6к                     ',0.0240,0.01,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'Сеялка ств-6к                     ',0.0500,0.03,36)
INSERT INTO CRAS (NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,SI,NZ) VALUES ('Шпонка         8*12 ст 45                 ','Кг        ',0.58,1.7466,1.01,'З/Ч                               ',0.1790,0.10,36)

SELECT CRAS
GO TOP
NZT=0
SCAN
    IF CRAS.NZ <> NZT
        NZT = CRAS.NZ
        KRT = CRAS.KR - CRAS.KOL
        SRT = CRAS.SR - CRAS.SI
    ELSE
        REPLACE SI WITH ROUND(SRT/KRT*CRAS.KOL,2) IN CRAS
        KRT = KRT - CRAS.KOL
        SRT = SRT - CRAS.SI
    ENDIF
ENDSCAN
BROW

SELECT NMAT,ED_IZM,CENA,KR,SR,NAMR,KOL,
ROUND(SUM(KOL * CENA) OVER (partition by NZ ORDER BY NAMR ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) -
             ROUND(NVL(SUM(KOL * CENA) OVER (partition by NZ ORDER BY NAMR ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 2) SN,SI,
NZ FROM
(SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'Сеялка спу-4д                     '  NAMR,6.4512 KOL,4.52 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'Сеялка ств-8ку точного высева     '  NAMR,2.2176 KOL,1.55 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'Сеялка спу-6д-01                  '  NAMR,5.4432 KOL,3.81 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'Сеялка ств-6к                     '  NAMR,0.1512 KOL,0.11 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'З/Ч                               '  NAMR,7.8372 KOL,5.49 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'Заказ № 421 о переоборудовании    '  NAMR,0.3024 KOL,0.21 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'Заклепка 6*22 Ст1 ГОСТ 10300-80 П         '  NMAT,'КГ        '  ED_IZM,0.70 CENA,22.7052 KR,15.89 SR,'СЕРВИС                            '  NAMR,0.3024 KOL,0.21 SI,4 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА  ПРУЖИН. 1,8ММ                  '  NMAT,'          '  ED_IZM,1.43 CENA,1.0560 KR,1.51 SR,'Сеялка ств-8ку точного высева     '  NAMR,0.9540 KOL,1.37 SI,18 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА  ПРУЖИН. 1,8ММ                  '  NMAT,'          '  ED_IZM,1.43 CENA,1.0560 KR,1.51 SR,'Сеялка ств-6к                     '  NAMR,0.1020 KOL,0.15 SI,18 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'Сеялка спу-4д                     '  NAMR,7.0720 KOL,22.49 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'Сеялка ств-8ку точного высева     '  NAMR,2.1956 KOL,6.98 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'Сеялка ств-8ку точного высева     '  NAMR,3.5200 KOL,11.19 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'Сеялка спу-6д-01                  '  NAMR,1.7964 KOL,5.71 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'Сеялка ств-6к                     '  NAMR,0.1996 KOL,0.63 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРОВОЛОКА ПРУЖ.. 6ММ СТ60С2А              '  NMAT,'          '  ED_IZM,3.18 CENA,15.2256 KR,48.42 SR,'З/Ч                               '  NAMR,0.4420 KOL,1.41 SI,20 NZ  FROM DUAL
UNION ALL
SELECT 'ПРУТОК  БРАЖ  9-4  20ММ                   '  NMAT,'КГ        '  ED_IZM,15.50 CENA,12.0275 KR,186.43 SR,'Сеялка спу-4д                     '  NAMR,1.4720 KOL,22.82 SI,23 NZ  FROM DUAL
UNION ALL
SELECT 'ПРУТОК  БРАЖ  9-4  20ММ                   '  NMAT,'КГ        '  ED_IZM,15.50 CENA,12.0275 KR,186.43 SR,'Сеялка ств-8ку точного высева     '  NAMR,8.0700 KOL,125.09 SI,23 NZ  FROM DUAL
UNION ALL
SELECT 'ПРУТОК  БРАЖ  9-4  20ММ                   '  NMAT,'КГ        '  ED_IZM,15.50 CENA,12.0275 KR,186.43 SR,'Сеялка спу-6д-01                  '  NAMR,1.6560 KOL,25.67 SI,23 NZ  FROM DUAL
UNION ALL
SELECT 'ПРУТОК  БРАЖ  9-4  20ММ                   '  NMAT,'КГ        '  ED_IZM,15.50 CENA,12.0275 KR,186.43 SR,'Сеялка ств-6к                     '  NAMR,0.7110 KOL,11.02 SI,23 NZ  FROM DUAL
UNION ALL
SELECT 'ПРУТОК  БРАЖ  9-4  20ММ                   '  NMAT,'КГ        '  ED_IZM,15.50 CENA,12.0275 KR,186.43 SR,'З/Ч                               '  NAMR,0.1185 KOL,1.84 SI,23 NZ  FROM DUAL
UNION ALL
SELECT 'Проволока пружин.  2.2мм                  '  NMAT,'Кг        '  ED_IZM,0.19 CENA,0.1785 KR,0.03 SR,'Сеялка спу-4д                     '  NAMR,0.0816 KOL,0.02 SI,30 NZ  FROM DUAL
UNION ALL
SELECT 'Проволока пружин.  2.2мм                  '  NMAT,'Кг        '  ED_IZM,0.19 CENA,0.1785 KR,0.03 SR,'Сеялка спу-6д-01                  '  NAMR,0.0459 KOL,0.01 SI,30 NZ  FROM DUAL
UNION ALL
SELECT 'Проволока пружин.  2.2мм                  '  NMAT,'Кг        '  ED_IZM,0.19 CENA,0.1785 KR,0.03 SR,'З/Ч                               '  NAMR,0.0510 KOL,0.01 SI,30 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Сеялка ств-8ку точного высева     '  NAMR,0.2640 KOL,0.15 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Сеялка ств-8ку точного высева     '  NAMR,1.1000 KOL,0.63 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Ппр-145 пресс-подборщик / эксп.   '  NAMR,0.0336 KOL,0.02 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Ппр-145 пресс-подборщик / эксп.   '  NAMR,0.0960 KOL,0.06 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Сеялка ств-6к                     '  NAMR,0.0240 KOL,0.01 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'Сеялка ств-6к                     '  NAMR,0.0500 KOL,0.03 SI,36 NZ  FROM DUAL
UNION ALL
SELECT 'Шпонка         8*12 ст 45                 '  NMAT,'Кг        '  ED_IZM,0.58 CENA,1.7466 KR,1.01 SR,'З/Ч                               '  NAMR,0.1790 KOL,0.10 SI,36 NZ  FROM DUAL ) CUN