method CalcAllDInvoiceByDebtor

query calculated fields procedure

Description

Assign a value to query calculated fields

Parameters

iiCompanyId	input	integer	Company id
iiDebtorID	input	integer
itInvoicStartDate	input	date
itInvoiceEndDate	input	date
ilDInvoiceIsOpenEQUALS	input	logical
icOIAdjustLnInvType	input	character
ilDInvoiceIsOpenDIFFERSFROM	input	logical
oiReturnStatus	output	integer	Return status of the method.

Internal usage

QadFinancials

query DinvoiceByDebtor

program code (program1/bdinvoice.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 {&tdOpenAmountLCForSales}     = {&tdDInvoiceBalanceDebitLC} - {&tdDInvoiceBalanceCreditLC}
           {&tdOpenAmountTCForSales}     = {&tdDInvoiceBalanceDebitTC} - {&tdDInvoiceBalanceCreditTC}
           {&tdOpenAmountCCForSales}     = {&tdDInvoiceBalanceDebitCC} - {&tdDInvoiceBalanceCreditCC}
           {&tdOriginalAmountLCForSales} = {&tdDInvoiceOriginalDebitLC} - {&tdDInvoiceOriginalCreditLC}
           {&tdOriginalAmountTCForSales} = {&tdDInvoiceOriginalDebitTC} - {&tdDInvoiceOriginalCreditTC}
           {&tdOriginalAmountCCForSales} = {&tdDInvoiceOriginalDebitCC} - {&tdDInvoiceOriginalCreditCC}.

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

    assign {&tiOverdueWeeksForSales}     =  truncate({&tiOverdueDaysForSales} / 7 , 0).

    if {&tiDInvoicePostingYear} <> ? or {&tcJournalCode} <> ? or {&tiDInvoiceVoucher} <> ?
    then assign {&tcDInvoiceNumberForSales} = string({&tiDInvoicePostingYear}) + "/":U + {&tcJournalCode} + "/":U + string({&tiDInvoiceVoucher},"999999999":U).
    else assign {&tcDInvoiceNumberForSales} = NOW.


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

    /* work with a do because the translated and untranslated values can be the same */
    assign {&tcDInvoiceTypeForSales} = "?":U.
    do viCounter = num-entries(vcInvoiceTypes,chr(2)) to 1 by -2 :
        if entry(viCounter,vcInvoiceTypes,chr(2)) = {&tcDInvoiceType}
        then do :
            assign {&tcDInvoiceTypeForSales} = entry(viCounter - 1,vcInvoiceTypes,chr(2)) no-error.
            leave.
        end. /* if entry */
    end.

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