The script must be pasted on the corresponding document at:
Settings->Purchase/sales->Documents->Run SQL
To automatically create a consignment document that includes the combined products of the receipt issued, paste the following script to the corresponding documents:
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 | --CHILD PRODUCTS - DOCUMENT ID: 7 EXECUTE BLOCK AS BEGIN if ((select count("pvlhseis"."Aa") from "pvlhseis" where "pvlhseis"."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa))>0) then begin --DELETE OLD DOCUMENT LINES delete from "grammes" where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa) ; --CREATE NEW DOCUMENT LINES INCLUDED ONE PRODUCT WITH ID 'LOY' 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), "apouhkh"."Aa","apouhkh"."Kvdikos", "apouhkh"."Perigrafh","apouhkh"."fpaT", "relatedproducts"."Quantity"*graa."Posothta", graa."Monada",graa."monada_",graa."Timh",graa."Ekptvsh",graa."order" from "relatedproducts", "apouhkh","pvlhseis" pvll,"grammes" graa where "relatedproducts"."RelatedAa"="apouhkh"."Aa" and pvll."Aa"=graa."Aapar" and "relatedproducts"."BaseAa"= graa."Eidos" and pvll."Aa"=:aa ; end else begin --INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID 7 update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= 7 ; --CREATE NEW DOCUMENT WITH LINE ID 7 insert into "pvlhseis" ("pvlhseis"."Aa", "pvlhseis"."Ariumospar","pvlhseis"."Kvdikospelath", "pvlhseis"."Hmeromhnia","pvlhseis"."Parastatiko","pvlhseis"."Sxetika" ) values ( (select gen_id("gen_pvlhseis_id", 1) as fname from RDB$DATABASE), (select eid1."Ariumos" from "eidhpar" eid1 where eid1."Aa"=7), (select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa), 'NOW',7, (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 ) ; --CREATE NEW DOCUMENT LINES INCLUDED ONE PRODUCT WITH ID 'LOY' 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), "apouhkh"."Aa","apouhkh"."Kvdikos", "apouhkh"."Perigrafh","apouhkh"."fpaT", "relatedproducts"."Quantity"*graa."Posothta", graa."Monada",graa."monada_",graa."Timh",graa."Ekptvsh",graa."order" from "relatedproducts", "apouhkh","pvlhseis" pvll,"grammes" graa where "relatedproducts"."RelatedAa"="apouhkh"."Aa" and pvll."Aa"=graa."Aapar" and "relatedproducts"."BaseAa"= graa."Eidos" and pvll."Aa"=:aa ; end --UPDATE NEW DOCUMENT ADDING TOTALS update "pvlhseis" pvm set pvm."synolofpa_"= (select sum(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from "grammes" gra where gra."Aapar"=pvm."Aa" ), pvm."synolonpe_"= (select sum(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from "grammes" gra where gra."Aapar"=pvm."Aa" ), pvm."synolo_"= (select sum((gra ."Posothta"* gra."Timh") + (((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100)) from "grammes" gra where gra."Aapar"=pvm."Aa" ), pvm."synoloposothtas_"= (select sum(gra ."Posothta") from "grammes" gra where gra."Aapar"=pvm."Aa" ), pvm."ogkos_syn"=0,pvm."embado_syn"=0,pvm."baros_syn"=0,pvm."ajia_syn"=0,pvm."synolikopososto"=0,pvm."loipes_"=0 where pvm."Aa"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa) ; END; |
