*СОЗДАНИЕ КУРСОРА  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)  
     
 *СОЗДАНИЕ КУРСОРА  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} && МЕСЯЦ ОТЧЕТНЫЙ  
    
* выберем поиздельные расходы за отчётный м-ц и занумеруем
SELECT *,RECNO() NZ FROM (SELECT R_IZD.KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,mg ;    
   FROM R_IZD  WHERE MG = MG_OTC ORDER BY ID_RIZ) CIZD1 INTO CURSOR CIZD   

* ПОДСЧЁТ ТЕКУЩЕГО ОСТАТКА ПО ZARPL И MAT ПО УБЫВАНИЮ (ВЛАДИМИР МАКСИМОВ подсказал в 2008 году)
 SELECT CIZD.NZ, SUM(CIZD1.ZARPL) ITZ,SUM(CIZD1.MAT) ITM  ;
    FROM CIZD,CIZD CIZD1 WHERE CIZD1.NZ >= CIZD.NZ GROUP BY 1 INTO CURSOR CITG
*присоединим текущие остатки к поиздельные расходы за отчётный м-ц    
SELECT KODI,KOL,MAT,KOMPL,ZARPL,BRAK,ID_RIZ,MG,CIZD.NZ,CITG.ITZ,CITG.ITM ;
FROM CIZD INNER JOIN CITG ON CITG.NZ = CIZD.NZ INTO CURSOR CIT 

*Выборка общезаводских расходов  за отчётный м-ц (одна запись)  
SELECT WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG FROM R_ZAW WHERE R_ZAW.mg = MG_OTC ;
    INTO CURSOR CZAW    

SCATTER  NAME OSUM   && для хранения текущего остатка общезаводских расходов        
SCATTER BLANK NAME PSUM  && для расчёта доли общезаводских расходов для изделия        

* ЗАЧАТИЕ ПУСТОГО результирующего КУРСОРА
SELECT KODI,KOL,MAT,KOMPL,ZARPL,BRAK,CAST(0 AS N(18,2)) WZW,CAST(0 AS N(18,2)) ENERG,CAST(0 AS N(18,2)) ZARPD, ;
CAST(0 AS N(18,2)) NALOG,CAST(0 AS N(18,2)) ZATRP,CAST(0 AS N(18,2)) PROCIE,CAST(0 AS N(18,2)) WSPOMOG ;
  FROM CIT WHERE .F. INTO CURSOR CREZ

SELECT CIT
SCAN
    *расчёт текущего остатка общезаводских расходов
    OSUM.WZW = OSUM.WZW - PSUM.WZW
    OSUM.ENERG = OSUM.ENERG - PSUM.ENERG
    OSUM.ZARPD = OSUM.ZARPD - PSUM.ZARPD
    OSUM.NALOG = OSUM.NALOG - PSUM.NALOG
    OSUM.ZATRP = OSUM.ZATRP - PSUM.ZATRP
    OSUM.PROCIE = OSUM.PROCIE - PSUM.PROCIE
    OSUM.WSPOMOG = OSUM.WSPOMOG - PSUM.WSPOMOG
    *расчёт доли общезаводских расходов для изделия
    PSUM.WZW = ROUND(OSUM.WZW*CIT.MAT/CIT.ITM,2)
    PSUM.ENERG = ROUND(OSUM.ENERG*CIT.ZARPL/CIT.ITZ,2)
    PSUM.ZARPD = ROUND(OSUM.ZARPD*CIT.ZARPL/CIT.ITZ,2) 
    PSUM.NALOG = ROUND(OSUM.NALOG*CIT.ZARPL/CIT.ITZ,2) 
    PSUM.ZATRP = ROUND(OSUM.ZATRP*CIT.ZARPL/CIT.ITZ,2) 
    PSUM.PROCIE = ROUND(OSUM.PROCIE*CIT.ZARPL/CIT.ITZ,2) 
    PSUM.WSPOMOG = ROUND(OSUM.WSPOMOG*CIT.ZARPL/CIT.ITZ,2) 

    NZK=CIT.NZ
    *расчитанные доли общезаводских расходов для изделия присоединяем в результирующий курсор
    SELECT * FROM CREZ ;
    UNION ALL ;
    SELECT KODI,KOL,MAT,KOMPL,ZARPL,BRAK, ;
        PSUM.WZW , ;
        PSUM.ENERG, ;
        PSUM.ZARPD, ;
        PSUM.NALOG , ;
        PSUM.ZATRP , ;
        PSUM.PROCIE , ;
        PSUM.WSPOMOG  ;
     FROM CIT WHERE NZ=M.NZK INTO CURSOR CREZ
ENDSCAN     

* добавим наименование изделия
SELECT CREZ.KODI,SIZD.NAME,KOL,MAT,KOMPL,ZARPL,BRAK,WZW,ENERG,ZARPD, ;
    NALOG,ZATRP,PROCIE,WSPOMOG FROM CREZ INNER JOIN SIZD ON SIZD.KODI = CREZ.KODI  INTO CURSOR CR
BROW

* проверим отсутствие остатков при распределении
SELECT 'исходн. суммы к распр    ' тип, WZW,ENERG,ZARPD,NALOG,ZATRP,PROCIE,WSPOMOG FROM czaw ;
UNION ALL ;
SELECT 'итоги получ.по изд. сумм ' тип,  SUM(WZW),SUM(ENERG),SUM(ZARPD),SUM(NALOG),SUM(ZATRP),SUM(PROCIE), ;
    SUM(WSPOMOG) FROM cr INTO CURSOR ckontr
brow