method CalcAllCInvoiceGetPrepayByCreditor

query calculated fields procedure

Description

Assign a value to query calculated fields

Parameters

iiCompanyId	input	integer	Company id
icCompanyListID	input	character
iiCreditorID	input	integer
itPostingStartDate	input	date
itPostingEndDate	input	date
icCreditorCode	input	character
itPostingDate	input	date
icCInvoiceMovementType	input	character
icCreditorCodeList	input	character
oiReturnStatus	output	integer	Return status of the method.

Internal usage

QadFinancials

query CInvoiceGetPrepayByCreditor

program code (program1/bcinvoice.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 {&tcCInvoiceNumber}   = substitute("&1/&2/&3":U, {&tiCInvoicePostingYear}, {&tcJournalInvoiceCode}, string({&tiCInvoiceVoucher},"999999999"))
           {&tcDocumentNumber}   = substitute("&1/&2/&3":U, {&tiPostingYear}, {&tcJournalCode}, string({&tiPostingVoucher},"999999999":U))
           {&tdOpenAmountLC}     = {&tdCInvoiceBalanceCreditLC} - {&tdCInvoiceBalanceDebitLC}
           {&tdOpenAmountCC}     = {&tdCInvoiceBalanceCreditCC} - {&tdCInvoiceBalanceDebitCC}
           {&tdOpenAmountTC}     = {&tdCInvoiceBalanceCreditTC} - {&tdCInvoiceBalanceDebitTC}
           {&tdOriginalAmountLC} = {&tdCInvoiceOriginalCreditLC} - {&tdCInvoiceOriginalDebitLC}
           {&tdOriginalAmountCC} = {&tdCInvoiceOriginalCreditCC} - {&tdCInvoiceOriginalDebitCC}
           {&tdOriginalAmountTC} = {&tdCInvoiceOriginalCreditTC} - {&tdCInvoiceOriginalDebitTC}
           {&tdPaymentAmountTC}  = {&tdPostingLineDebitTC} - {&tdPostingLineCreditTC}.

    /* Invoice is open and overdue */
    if {&tlCInvoiceIsOpen}   = true  and
       {&ttCInvoiceDueDate} <= today
    then assign {&tiOverdueDays} = today - {&ttCInvoiceDueDate}.
    /* Invoice is closed but payed after due date */
    else if {&tlCInvoiceIsOpen}  = false                    and
            {&ttCInvoiceDueDate} < {&ttCInvoiceClosingDate}
    then assign {&tiOverdueDays} = {&ttCInvoiceClosingDate} - {&ttCInvoiceDueDate}.
    /* Invoice is payed within time or is still opened but not overdue */
    else assign {&tiOverdueDays} = 0. 

    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.
        
    vhQuery:get-next().
end.
vhQuery:query-close().
delete object vhQuery.