method CalcAllDInvoiceMovementByDebtorPm

query calculated fields procedure

Description

Assign a value to query calculated fields

Parameters

iiCompanyId	input	integer	Company id
itFromPostingDate	input	date
itToPostingDate	input	date
iiDebtorID	input	integer
oiReturnStatus	output	integer	Return status of the method.

Internal usage

QadFinancials

query DInvoiceMovementByDebtorPm

program code (program1/bdinvoice.p)

create query vhQuery in widget-pool "non-persistent".
vhQuery:forward-only = yes.
vhQuery:set-buffers(ih_Buffer).
vhQuery:query-prepare("for each ":U + ih_Buffer:table).
vhQuery:query-open().
vhQuery:get-first().
do while not vhQuery:query-off-end:

    assign {&tcDInvoiceNumber} = string({&tiDInvoicePostingYear}) + "/":U + {&tcJournalInvoiceCode} + "/":U + string({&tiDInvoiceVoucher},"999999999":U)
           {&tcDocumentNumber} =  string({&tiPostingYear}) + "/":U + {&tcJournalCode} + "/":U + string({&tiPostingVoucher},"999999999":U)
           {&tdOpenAmountLC} = {&tdDInvoiceBalanceDebitLC} - {&tdDInvoiceBalanceCreditLC}
           {&tdOpenAmountTC} = {&tdDInvoiceBalanceDebitTC} - {&tdDInvoiceBalanceCreditTC}
           {&tdOpenAmountCC} = {&tdDInvoiceBalanceDebitCC} - {&tdDInvoiceBalanceCreditCC}
           {&tdOriginalAmountLC} = {&tdDInvoiceOriginalDebitLC} - {&tdDInvoiceOriginalCreditLC}
           {&tdOriginalAmountTC} = {&tdDInvoiceOriginalDebitTC} - {&tdDInvoiceOriginalCreditTC}
           {&tdOriginalAmountCC} = {&tdDInvoiceOriginalDebitCC} - {&tdDInvoiceOriginalCreditCC}.

    if {&ttDInvoiceDueDate} <> ?
    then do:
        /* Invoice is open and overdue */
        if {&tlDInvoiceIsOpen}   = true  and
           {&ttDInvoiceDueDate} <= today
        then assign {&tiOverdueDays} = today - {&ttDInvoiceDueDate}.
        /* Invoice is closed but payed after due date */
        else if {&tlDInvoiceIsOpen}  = false                    and
                {&ttDInvoiceDueDate} < {&ttDInvoiceClosingDate}
        then assign {&tiOverdueDays} = {&ttDInvoiceClosingDate} - {&ttDInvoiceDueDate}.
        /* Invoice is payed within time or is still opened but not overdue */
        else assign {&tiOverdueDays} = 0. 
    end.

    assign vttempdate = date(1,1,year({&ttPostingDate})) + (2 - weekday(date(1,1,year({&ttPostingDate})))).
    if weekday(date(1,1,year({&ttPostingDate}))) >= 6 then 
        vttempdate = vttempdate + 7.
    
    assign {&tiWeekNumbers} = truncate(((date({&ttPostingDate}) - vttempdate) + 7) / 7 , 0).
    
    if {&tiWeekNumbers} > 52 
    then do:
        assign vttempdate = date(1,1,year({&ttPostingDate}) + 1) + (2 - weekday(date(1,1,year({&ttPostingDate}) + 1))).
        if weekday(date(1,1,year({&ttPostingDate}) + 1)) >= 6 then 
            vttempdate = vttempdate + 7.
        if {&ttPostingDate} < vttempdate then
             {&tiWeekNumbers} = 52.           
    end.

    assign {&tdPaymentAmountTC} = {&tdPostingLineDebitTC} - {&tdPostingLineCreditTC}.
    
  
    vhQuery:get-next().
end.
vhQuery:query-close().
delete object vhQuery.