With the following SQL code when issuing or editing 1 document, a copy of it is automatically created in a desired document. Also, optionally, it is possible to change the document based on the customer / supplier code.
This code must be affixed to the corresponding document, in place:
Settings-> Purchases / Sales-> Documents-> Execute SQL
For sale by automatic copying to a document with line number 104:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
-- COPY DOCUMENT EXECUTE BLOCK AS declare newdocid int = 104; declare fromtitle varchar(1000) = ''; -- related document suffix declare multiplier smallint = 1; -- 1 sales -1 purchases BEGIN ------------------------------------------------- ------------------------------------------------- -- change newdocid based on customer code if ((select "pelates"."Kvdikos" from "pelates" where "pelates"."Aa"=:cl)='customer code') then newdocid=1; if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle))>0) then begin -- UPDATE STOCK update "apouhkh" set "apouhkh"."Diauesimothta"="apouhkh"."Diauesimothta"+ :multiplier*(select sum(grm."Posothta") from "pvlhseis" pvm,"grammes" grm where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) and grm."Aapar"=pvm."Aa" and "apouhkh"."Aa"=grm."Eidos") where "apouhkh"."Aa" in (select grm."Eidos" from "pvlhseis" pvm,"grammes" grm where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) and grm."Aapar"=pvm."Aa") ; -- DELETE OLD DOCUMENT LINES delete from "grammes" where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) ; -- CREATE NEW DOCUMENT LINES insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys", "grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_", "grammes"."Timh","grammes"."Ekptvsh","grammes"."order" ) select (select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE), (select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa||:fromtitle), "apouhkh"."Aa","apouhkh"."Kvdikos", "apouhkh"."Perigrafh","apouhkh"."fpaT", graa."Posothta", graa."Monada",graa."monada_",graa."Timh",graa."Ekptvsh",graa."order" from "apouhkh","pvlhseis" pvll,"grammes" graa where pvll."Aa"=graa."Aapar" and "apouhkh"."Aa"= graa."Eidos" and pvll."Aa"=:aa -- and trim("apouhkh"."custom2")=:fromtitle ; end else begin -- CREATE NEW DOCUMENT insert into "pvlhseis" ("pvlhseis"."modified","pvlhseis"."Aa", "pvlhseis"."Ariumospar", "pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika") values ('Now', (select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE), (select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=:newdocid), 'NOW', :newdocid, (select substring(eid2."Parastatiko" from 1 for 3)||' '||pvl2."Seira"||'#'||pvl2."Ariumospar" from "pvlhseis" pvl2 left join "eidhpar" eid2 on eid2."Aa"=pvl2."Parastatiko" where pvl2."Aa"=:aa)||' &'||:aa||:fromtitle ) ; -- INCREMENT NUMBER TO DOCUMENT TYPE update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= :newdocid ; -- CREATE NEW DOCUMENT LINES insert into "grammes" ("grammes"."Aa","grammes"."Aapar","grammes"."Eidos","grammes"."KvdikosEidoys", "grammes"."PerigrafhEidoys","grammes"."fpa_","grammes"."Posothta","grammes"."Monada","grammes"."monada_", "grammes"."Timh","grammes"."Ekptvsh","grammes"."order" ) select (select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE), (select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa||:fromtitle), "apouhkh"."Aa","apouhkh"."Kvdikos", "apouhkh"."Perigrafh","apouhkh"."fpaT", graa."Posothta", graa."Monada",graa."monada_",graa."Timh",graa."Ekptvsh",graa."order" from "apouhkh","pvlhseis" pvll,"grammes" graa where pvll."Aa"=graa."Aapar" and "apouhkh"."Aa"= graa."Eidos" and pvll."Aa"=:aa -- and trim("apouhkh"."custom2")=:fromtitle ; end -- UPDATE STOCK update "apouhkh" set "apouhkh"."Diauesimothta"="apouhkh"."Diauesimothta"- :multiplier*(select sum(grm."Posothta") from "pvlhseis" pvm,"grammes" grm where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) and grm."Aapar"=pvm."Aa" and "apouhkh"."Aa"=grm."Eidos") where "apouhkh"."Aa" in (select grm."Eidos" from "pvlhseis" pvm,"grammes" grm where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) and grm."Aapar"=pvm."Aa") ; -- UPDATE related document update "pvlhseis" pvm set pvm."modified"='Now', pvm."Sxetiko"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) where pvm."Aa"=:aa ; -- UPDATE NEW DOCUMENT ADDING TOTALS update "pvlhseis" pvm set pvm."modified"='Now', pvm."synolofpa_"=(select pvm2."synolofpa_" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."synolonpe_"=(select pvm2."synolonpe_" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."synolo_"=(select pvm2."synolo_" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."synoloposothtas_"=(select pvm2."synoloposothtas_" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."ogkos_syn"=(select pvm2."ogkos_syn" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."embado_syn"=(select pvm2."embado_syn" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."baros_syn"=(select pvm2."baros_syn" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."ajia_syn"=(select pvm2."ajia_syn" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."synolikopososto"=(select pvm2."synolikopososto" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."loipes_"=(select pvm2."loipes_" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Plhrvmh"=(select pvm2."Plhrvmh" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Hmeromhnia"=(select pvm2."Hmeromhnia" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Sxolio"=(select pvm2."Sxolio" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Tropos"=(select pvm2."Tropos" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Skopos"=(select pvm2."Skopos" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Kvdikospelath"=(select pvm2."Kvdikospelath" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), -- customer id pvm."Paradosh"=(select pvm2."Paradosh" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."assignor"=(select pvm2."assignor" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Apostolh"=(select pvm2."Apostolh" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."custom1"=(select pvm2."custom1" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."custom2"=(select pvm2."custom2" from "pvlhseis" pvm2 where pvm2."Aa"=:aa), pvm."Fortvsh"=(select pvm2."Fortvsh" from "pvlhseis" pvm2 where pvm2."Aa"=:aa) -- ,pvm."Parastatiko"=:newdocid where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa||:fromtitle) ; END; |
