Automatically document creation

Automatically create a new document when issuing a cash payment document and then convert the form of payment into the current document into credit.

This is required in case of connection to analytic accounting where it is required to properly maintain the cash.

Copy the following code into the settings->purchases/sales->documents->run SQL in the document you want to create at the same time.

You must fill in the fields:
dd            the line ID of the new document to be created.
loy           the product handle to be included in the new document with the full amount.
paym      the payment method line ID for the new document.
cpaym    the payment method line ID for the current document. It will change along with the creation of the new document.

EXECUTE BLOCK AS
declare dd int = 58; -- new document id
declare loy varchar(10) = 'REC'; -- code of product for receive money
declare paym int = 1; -- new payment id
declare cpaym int = 5; -- current payment id                                                                                                                                                
begin        
        
    --IF CUSTOMER ID IS NOT 0 AND PAYMENT WAY NOT CREDIT
    if ((:cl>0)
    and
    ((select diak."Ejoflhuh" from "diakanonismos" diak,"pvlhseis" ppl where ppl."Plhrvmh"=diak."Aa" and ppl."Aa"=:aa)=1))

    THEN 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
            
            --UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'
            update "grammes" set
            "grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
            "grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
            "grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
            "grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
            "grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
            "grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
            "grammes"."Posothta"=1,
            "grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
            "grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"=:loy),
            "grammes"."Timh"=   
            
            

            coalesce(
                (select ppl."synolo_" from "pvlhseis" ppl where ppl."Aa"=:aa)
            ,0)
            
            
            
            ,
            "grammes"."Ekptvsh"=0,
            "grammes"."order"=0
            
            where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)                                                                                     
            ;
            
            end else begin
            
            --INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID
            update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= :dd
            ;
            
            --CREATE NEW DOCUMENT WITH LINE ID
            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"=:dd),
                (select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),
                'NOW',:dd,
                (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 LINE 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"
            )
            values(
                (select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),
                (select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),
                
                (select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
                (select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
                (select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
                (select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
                                
                1  --quantity
                
                ,
                (select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),
                (select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"=:loy),
                
                coalesce(
                (select ppl."synolo_" from "pvlhseis" ppl where ppl."Aa"=:aa)
            ,0)
                            
                ,
                0,0
            )
            
            ;
        end                            




        
        --UPDATE NEW DOCUMENT ADDING TOTALS
        update "pvlhseis" pvm set                                 
        
        pvm."synolofpa_"=
        (select
            (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
            "grammes" gra where gra."Aapar"=pvm."Aa"
        ),
        
        pvm."synolonpe_"=
        (select
            (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
            "grammes" gra where gra."Aapar"=pvm."Aa"
        ),
        
        pvm."synolo_"=
        (select (gra ."Posothta"* gra."Timh")
            +    (((gra ."Posothta"* gra."Timh")* gra."fpa_"   )/100) from
            "grammes" gra where gra."Aapar"=pvm."Aa"
        ),
        
        pvm."synoloposothtas_"=
        (select floor(gra ."Posothta") from
            "grammes" gra where gra."Aapar"=pvm."Aa"
        ),
        

        pvm."Plhrvmh"=:paym

        
         
        
        
        ,
        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)
        
        ;
        
        --change current payment
        update "pvlhseis" set "pvlhseis"."Plhrvmh"=:cpaym where "pvlhseis"."Aa"=:aa
            ;
        END
        END;

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

EXECUTE BLOCK AS

declare dd int = 58; -- new document id

declare loy varchar(10) = 'REC'; -- code of product for receive money

declare paym int = 1; -- new payment id

declare cpaym int = 5; -- current payment id

begin

--IF CUSTOMER ID IS NOT 0 AND PAYMENT WAY NOT CREDIT

if ((:cl>0)

and

((select diak."Ejoflhuh" from "diakanonismos" diak,"pvlhseis" ppl where ppl."Plhrvmh"=diak."Aa" and ppl."Aa"=:aa)=1))

THEN 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

--UPDATE DOCUMENT LINE INCLUDED ONE PRODUCT WITH ID 'LOY'

update "grammes" set

"grammes"."Aa"=(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

"grammes"."Aapar"=(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

"grammes"."Eidos"=(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

"grammes"."KvdikosEidoys"=(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

"grammes"."PerigrafhEidoys"=(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

"grammes"."fpa_"=(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

"grammes"."Posothta"=1,

"grammes"."Monada"=(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

"grammes"."monada_"=(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"=:loy),

"grammes"."Timh"=

coalesce(

(select ppl."synolo_" from "pvlhseis" ppl where ppl."Aa"=:aa)

,0)

"grammes"."Ekptvsh"=0,

"grammes"."order"=0

where "grammes"."Aapar"=(select pvl."Aa" from "pvlhseis" pvl where pvl."Sxetika" like '%&'||:aa)

;

end else begin

--INCREMENT NUMBER TO DOCUMENT TYPE WITH LINE ID

update "eidhpar" set "eidhpar"."Ariumos"="eidhpar"."Ariumos"+1 where "eidhpar"."Aa"= :dd

;

--CREATE NEW DOCUMENT WITH LINE ID

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"=:dd),

(select pvl1."Kvdikospelath" from "pvlhseis" pvl1 where pvl1."Aa"=:aa),

'NOW',:dd,

(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 LINE 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"

)

values(

(select gen_id("gen_grammes_id", 1) as fname from RDB$DATABASE),

(select pvl1."Aa" from "pvlhseis" pvl1 where pvl1."Sxetika" like '%&'||:aa),

(select apo1."Aa" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

(select apo1."Kvdikos" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

(select apo1."Perigrafh" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

(select apo1."fpaT" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

1 --quantity

(select apo1."Monada" from "apouhkh" apo1 where apo1."Kvdikos"=:loy),

(select "monades"."Monades" from "apouhkh" apo1 left join "monades" on "monades"."Aa"=apo1."Monada" where apo1."Kvdikos"=:loy),

coalesce(

(select ppl."synolo_" from "pvlhseis" ppl where ppl."Aa"=:aa)

,0)

0,0

)

;

end

--UPDATE NEW DOCUMENT ADDING TOTALS

update "pvlhseis" pvm set

pvm."synolofpa_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolonpe_"=

(select

(((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synolo_"=

(select (gra ."Posothta"* gra."Timh")

+ (((gra ."Posothta"* gra."Timh")* gra."fpa_" )/100) from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."synoloposothtas_"=

(select floor(gra ."Posothta") from

"grammes" gra where gra."Aapar"=pvm."Aa"

pvm."Plhrvmh"=:paym

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)

;

--change current payment

update "pvlhseis" set "pvlhseis"."Plhrvmh"=:cpaym where "pvlhseis"."Aa"=:aa

;

END

END;