method CalcAllCInvoiceByCreditorAllInfo

query calculated fields procedure

Description

Assign a value to query calculated fields

Parameters

iiCompanyId	input	integer	Company id
iiCreditorID	input	integer
itInvoiceStartDate	input	date
itInvoiceEndDate	input	date
ilCInvoiceIsOpen	input	logical
icCompanyListID	input	character
icCreditorCode	input	character
itCInvoiceDate	input	date
icCreditorCodeList	input	character
oiReturnStatus	output	integer	Return status of the method.

Internal usage

QadFinancials

query CInvoiceByCreditorAllInfo

program code (program1/bcinvoice.p)

assign vcInvoiceTypes = {&INVOICETYPES}.

create query vhQuery in widget-pool "non-persistent".
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}              = string({&tiCInvoicePostingYear}) + '/':U + {&tcJournalCode} + '/':U + string({&tiCInvoiceVoucher},"999999999")
           {&tcCInvoiceNumberOperational}   = string({&tiCInvoicePostingYear}) + '/':U + {&tcJournalCode} + string({&tiCInvoiceVoucher},"999999999")
           {&tdOpenAmountLC}                = {&tdCInvoiceBalanceCreditLC} - {&tdCInvoiceBalanceDebitLC}
           {&tdOpenAmountTC}                = {&tdCInvoiceBalanceCreditTC} - {&tdCInvoiceBalanceDebitTC}
           {&tdOpenAmountCC}                = {&tdCInvoiceBalanceCreditCC} - {&tdCInvoiceBalanceDebitCC} 
           {&tdOriginalAmountLC}            = {&tdCInvoiceOriginalCreditLC} - {&tdCInvoiceOriginalDebitLC}
           {&tdOriginalAmountTC}            = {&tdCInvoiceOriginalCreditTC} - {&tdCInvoiceOriginalDebitTC}
           {&tdOriginalAmountCC}            = {&tdCInvoiceOriginalCreditCC} - {&tdCInvoiceOriginalDebitCC}
           {&tiOverdueWeeks}                = truncate({&tiOverdueDays} / 7 , 0).
    if {&ttCInvoiceDueDate} <> ?
    then do:
        if NOT {&tlCInvoiceIsOpen}
        then assign {&tiOverdueDays} = if {&ttCInvoiceClosingDate} > {&ttCInvoiceDueDate} then {&ttCInvoiceClosingDate} - {&ttCInvoiceDueDate} else 0.
        else do: 
            if today >= {&ttCInvoiceDueDate}  
            then assign {&tiOverdueDays} = today - {&ttCInvoiceDueDate}.
            else assign {&tiOverdueDays} = 0.
        end.            
    end. 
    
    /* Week Numbers */
    assign vttempdate = date(1,1,year({&ttCInvoiceDueDate})) + (2 - weekday(date(1,1,year({&ttCInvoiceDueDate})))).
    if weekday(date(1,1,year({&ttCInvoiceDueDate}))) >= 6 then 
        vttempdate = vttempdate + 7.
    
    assign {&tiWeekNumbers} = truncate(((date({&ttCInvoiceDueDate}) - vttempdate) + 7) / 7 , 0).
    
    if {&tiWeekNumbers} > 52 
    then do:
        assign vttempdate = date(1,1,year({&ttCInvoiceDueDate}) + 1) + (2 - weekday(date(1,1,year({&ttCInvoiceDueDate}) + 1))).
        if weekday(date(1,1,year({&ttCInvoiceDueDate}) + 1)) >= 6 then 
            vttempdate = vttempdate + 7.
        if {&ttCInvoiceDueDate} < vttempdate then
             {&tiWeekNumbers} = 52.           
    end.

    assign {&tcCInvoiceTypeForSales} = "?":U.
    do viCounter = num-entries(vcInvoiceTypes,chr(2)) to 1 by -2 :
        if entry(viCounter,vcInvoiceTypes,chr(2)) = {&tcCInvoiceType}
        then do :
            assign {&tcCInvoiceTypeForSales} = entry(viCounter - 1, vcInvoiceTypes, chr(2)) no-error.
            leave.
        end. /* if entry */
    end.

    vhQuery:get-next().
end.
vhQuery:query-close().
delete object vhQuery.