project QadFinancials > class BDebtorReport > method DebtorAccountMovements

report procedure

Description

This method lists all account movements within selected period and within selected debtors.


Parameters


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrDebtorAccountMovementsoutputdataset
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


unused


program code (program7/bdebtorreport.p)

empty temp-table tqDebtorAccountMovements.
empty temp-table tqCustomerInfo.

<M-10 run GetReportLabels (input  'DebtorAccountMovements':U (icReportName), 
                           input  icLanguageCode (icLanguageCode), 
                           input  tFilter (tFilter), 
                           output tqHeader (tqHeader), 
                           output tqFilter (tqFilter), 
                           output tqText (tqText), 
                           output oiReturnStatus (oiReturnStatus)) in BDebtorReport>
if oiReturnStatus <> 0
then return.

/*assign tFilter*/
<M-1 run SetDataItemsBasedOnFilterTT (output  viFcReturnSuper (oiReturnStatus)) in BDebtorReport>

/* We need to check if there are still some transactions pending in the historydaemon */
if vlDCheckHistoryFilter = ? or vlDCheckHistoryFilter
then do:
    <Q-11 assign vlFcQueryRecordsAvailable = QDInvoiceMovementAll (NoCache)
       (input viCompanyId, (CompanyId)
        input ?, (DaemonId)) in BQDInvoiceMovement >
    if vlFcQueryRecordsAvailable = true or
       vlFcQueryRecordsAvailable = ?
    then do:        
        <M-16 run SetMessage
           (input  #T-16'Run the Balance daemon to update customer balances.':70(65165)T-16# (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QADFIN-3742':U (icFcMsgNumber), 
            input  #T-17'Transactions exist in the Balance daemon queue. The data will not be current until the requests are processed.':150(20387)T-17# (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output oiReturnStatus (oiReturnStatus)) in BDebtorReport>                      
        assign oiReturnStatus = -1.
        return.
    end.
end.

/* CA784858 - From - To Period need to be specified */
if viFromAccYearFilter = ? or viFromAccYearFilter = 0 or 
   viToAccYearFilter = ? or viToAccYearFilter = 0 or
   viFromAccPeriodFilter = ? or viToAccPeriodFilter = ?
then do:
    <M-22 run SetMessage
       (input  #T-28' You must complete the [from-to] Period filter.':60(61984)T-28# (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  'E':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6262':U (icFcMsgNumber), 
        input  '' (icFcExplanation), 
        input  '' (icFcIdentification), 
        input  '' (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
    assign oiReturnStatus = -1.
    return.
end.

/* =============================================== */
/* Set some variables                              */
/* If no sorting selected -> chronological is used */
/* =============================================== */
if vcChronologicalLogicalFilter = ? 
then assign vcChronologicalLogicalFilter = {&DEBTORSORT-CHRONOLOGICAL}.

if vcSummaryByFilter = '' or vcSummaryByFilter = ?
then assign vcSummaryByFilter = 'NONE'.

assign vcCr = {&POSTINGTYPE-CREDIT-TR}
       vcDt = {&POSTINGTYPE-DEBIT-TR}.
       
/* ==================================================== */
/* 1 - Get the OpeningBalance at FromYearPeriod - 1     */
/* ==================================================== */       
<Q-3 run DebtorBalanceDHist (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoBRCodeFilter, (CandoBRCode)
    input vcCandoDebtorCodeFilter, (CandoDebtorCode)
    input vcCandoDContrGLCodeFilter, (CandoGLCode)
    input viFromYearPeriodFilter - 1, (EndOfYearPeriod)
    input vcFromBRCodeFilter, (FromBRCode)
    input vcFromDebtorCodeFilter, (FromDebtorCode)
    input vcFromDContrGLCodeFilter, (FromGLCode)
    input vcToBRCodeFilter, (ToBRCode)
    input vcToDebtorCodeFilter, (ToDebtorCode)
    input vcToDContrGLCodeFilter, (ToGLCode)
    output dataset tqDebtorBalanceDHist) in BDebtorReport >      
    
/* Get the OpeningBalance by Customer Currency GL DayBook */
for each tqDebtorBalanceDHist
    break by tqDebtorBalanceDHist.tcDebtorCode
          by tqDebtorBalanceDHist.tcCurrencyCode
          by tqDebtorBalanceDHist.tcGLCode
          by tqDebtorBalanceDHist.tcJournalCode:

    if first-of (tqDebtorBalanceDHist.tcJournalCode)
    then assign vdOpeningBalanceTC = 0
                vdOpeningBalanceLC = 0
                vdOpeningBalanceCC = 0.

    assign vdOpeningBalanceCC = vdOpeningBalanceCC + tqDebtorBalanceDHist.tdDHistBalanceCC
           vdOpeningBalanceLC = vdOpeningBalanceLC + tqDebtorBalanceDHist.tdDHistBalanceLC           
           vdOpeningBalanceTC = vdOpeningBalanceTC + tqDebtorBalanceDHist.tdDHistBalanceTC.

    if last-of (tqDebtorBalanceDHist.tcJournalCode)
    then do :
        /* One section per Customer, holding Customer info + Opening balance */ 
        create tqDebtorAccountMovements.
        assign tqDebtorAccountMovements.tcDebtorCode               = tqDebtorBalanceDHist.tcDebtorCode
               tqDebtorAccountMovements.tiDebtor_ID                = tqDebtorBalanceDHist.tiDebtor_ID
               tqDebtorAccountMovements.tcCurrencyCode             = tqDebtorBalanceDHist.tcCurrencyCode
               tqDebtorAccountMovements.tcJournalCode              = tqDebtorBalanceDHist.tcJournalCode
               tqDebtorAccountMovements.tcJournalDescription       = tqDebtorBalanceDHist.tcJournalDescription
               tqDebtorAccountMovements.tcJournalTypeCode          = tqDebtorBalanceDHist.tcJournalTypeCode
               tqDebtorAccountMovements.tcGLCode                   = tqDebtorBalanceDHist.tcGLCode   
               tqDebtorAccountMovements.tcGLDescription            = tqDebtorBalanceDHist.TCGLDESCRIPTION
               tqDebtorAccountMovements.tcGLType                   = tqDebtorBalanceDHist.tcGLTypeCode
               tqDebtorAccountMovements.tiCurrencyNumberOfDecimals = tqDebtorBalanceDHist.tiCurrencyNumberOfDecimals
               tqDebtorAccountMovements.tdOpeningBalanceTC         = vdOpeningBalanceTC
               tqDebtorAccountMovements.tdOpeningBalanceLC         = vdOpeningBalanceLC
               tqDebtorAccountMovements.tdOpeningBalanceCC         = vdOpeningBalanceCC
               tqDebtorAccountMovements.tcSortKey                  = ?
               tqDebtorAccountMovements.tcActivityKey              = ?
               tqDebtorAccountMovements.tcInvoiceKey               = ?
               tqDebtorAccountMovements.tcIsInitial                = ?
               tqDebtorAccountMovements.tlInitialWithinPeriod      = ?
               tqDebtorAccountMovements.tcSummarizationInfo        = vcSummaryByFilter
               tqDebtorAccountMovements.tcReportingInfo            = vcChronologicalLogicalFilter
               tqDebtorAccountMovements.tlDebtorPerPageInfo        = vlDebtorPerPageFilter
               tqDebtorAccountMovements.tcReportingCurrency        = vcReportingCurrencyFilter. 

    end. /* last-of ((tqDebtorBalanceDHist.tcDebtorCode) */

end. /* for each tqDebtorBalanceDHist */


/* ===================================================== */
/* 2 - Get all the movements between the selected period */
/* ===================================================== */
<Q-83 run DebtorMovements (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoBRCodeFilter, (CandoBRCode)
    input vcCandoDebtorCodeFilter, (CandoDebtorCode)
    input vcCandoDContrGLCodeFilter, (CandoGLCode)
    input vcFromBRCodeFilter, (FromBRCode)
    input vcFromDebtorCodeFilter, (FromDebtorCode)
    input vcFromDContrGLCodeFilter, (FromGLCode)
    input viFromYearPeriodFilter, (FromYearPeriod)
    input vcToBRCodeFilter, (ToBRCode)
    input vcToDebtorCodeFilter, (ToDebtorCode)
    input vcToDContrGLCodeFilter, (ToGLCode)
    input viToYearPeriodFilter, (ToYearPeriod)
    output dataset tqDebtorMovements) in BDebtorReport >


/* Go through all the activities now */
for each tqDebtorMovements 
    break by tqDebtorMovements.tiDInvoice_ID
          by tqDebtorMovements.tcDInvoiceMovementType :

        if first-of (tqDebtorMovements.tcDInvoiceMovementType)
        then assign vlisInitInPeriod = (tqDebtorMovements.tcDInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}).

        create tqDebtorAccountMovements.
        assign tqDebtorAccountMovements.tcDebtorCode         = tqDebtorMovements.tcDebtorCode
               tqDebtorAccountMovements.tiDebtor_ID          = tqDebtorMovements.tiDebtor_ID
               tqDebtorAccountMovements.tcCurrencyCode       = tqDebtorMovements.tcCurrencyCode
               tqDebtorAccountMovements.tcJournalCode        = tqDebtorMovements.tcJournalCode
               tqDebtorAccountMovements.tcJournalDescription = tqDebtorMovements.tcJournalDescription
               tqDebtorAccountMovements.tcJournalTypeCode    = tqDebtorMovements.tcJournalTypeCode
               tqDebtorAccountMovements.tcGLDescription      = tqDebtorMovements.tcGLDescription
               tqDebtorAccountMovements.tcGLCode             = tqDebtorMovements.tcGLCode
               tqDebtorAccountMovements.tcGLType             = tqDebtorMovements.tcGLTypeCode

               tqDebtorAccountMovements.ttDInvoiceDate             = tqDebtorMovements.ttDInvoiceDate
               tqDebtorAccountMovements.ttDInvoiceDiscountDueDate  = tqDebtorMovements.ttDInvoiceDiscountDueDate
               tqDebtorAccountMovements.ttDInvoiceDueDate          = tqDebtorMovements.ttDInvoiceDueDate

               tqDebtorAccountMovements.tiCurrencyNumberOfDecimals = tqDebtorMovements.tiCurrencyNumberOfDecimals
               tqDebtorAccountMovements.tlInitialWithinPeriod      = vlisInitInPeriod
               tqDebtorAccountMovements.tiPeriodYear               = tqDebtorMovements.tiPostingYear   
               tqDebtorAccountMovements.tiPeriodPeriod             = tqDebtorMovements.tiPostingPeriod
               tqDebtorAccountMovements.ttPostingDate              = tqDebtorMovements.ttPostingDate
               tqDebtorAccountMovements.tiPostingLineSequence      = tqDebtorMovements.tiPostingLineSequence
               tqDebtorAccountMovements.tcPostingLineText          = tqDebtorMovements.tcPostingLineText
               tqDebtorAccountMovements.tcPostingText              = tqDebtorMovements.tcPostingText
               tqDebtorAccountMovements.tiPostingVoucher           = tqDebtorMovements.tiPostingVoucher
               tqDebtorAccountMovements.tcDInvoiceDIText       = tqDebtorMovements.tcDInvoiceDIText

               tqDebtorAccountMovements.tdAmountCC                 = tqDebtorMovements.tdPostingLineDebitCC - tqDebtorMovements.tdPostingLineCreditCC
               tqDebtorAccountMovements.tdAmountLC                 = tqDebtorMovements.tdPostingLineDebitLC - tqDebtorMovements.tdPostingLineCreditLC
               tqDebtorAccountMovements.tdAmountTC                 = tqDebtorMovements.tdPostingLineDebitTC - tqDebtorMovements.tdPostingLineCreditTC            
               tqDebtorAccountMovements.tcActivityKey              = string (tqDebtorMovements.tiPostingYear) + "/" +
                                                                     tqDebtorMovements.tcJournalCode + "/" +
                                                                     string (tqDebtorMovements.tiPostingVoucher, "999999999")
               tqDebtorAccountMovements.tcInvoiceKey               = string (tqDebtorMovements.tiDInvoicePostingYear) + "/" +
                                                                     tqDebtorMovements.tcInvoiceJournalCode + "/" +
                                                                     string (tqDebtorMovements.tiDInvoiceVoucher, "999999999")
               tqDebtorAccountMovements.tcPostingInvoiceReferenceText = tqDebtorAccountMovements.tcInvoiceKey + ' ' + 
                                                                        tqDebtorMovements.tcDInvoiceDescription

               tqDebtorAccountMovements.tcIsInitial               = if tqDebtorMovements.tcDInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}
                                                                      then '*'
                                                                      else ''
               tqDebtorAccountMovements.tcSummarizationInfo        = vcSummaryByFilter
               tqDebtorAccountMovements.tcReportingInfo            = vcChronologicalLogicalFilter
               tqDebtorAccountMovements.tlDebtorPerPageInfo        = vlDebtorPerPageFilter
               tqDebtorAccountMovements.tcReportingCurrency        = vcReportingCurrencyFilter
               tqDebtorAccountMovements.tcShipper                  = tqDebtorMovements.tcPostingLineLegalDocNbr.
                                                                        
           if vcChronologicalLogicalFilter = {&DEBTORSORT-LOGICAL}
           then 
               /* sort by InvoicePostingDate - Invoice - Activity */ 
               assign tqDebtorAccountMovements.tcSortKey = 
                             string (year(tqDebtorMovements.ttDInvoicePostingDate),"9999") +
                             string (month(tqDebtorMovements.ttDInvoicePostingDate),"99") +
                             string (day(tqDebtorMovements.ttDInvoicePostingDate),"99") +
                             string (tqDebtorMovements.tiDInvoicePostingYear) + 
                             tqDebtorMovements.tcInvoiceJournalCode  +
                             string (tqDebtorMovements.tiDInvoiceVoucher) +
                             string (year(tqDebtorMovements.ttPostingDate),"9999") +
                             string (month(tqDebtorMovements.ttPostingDate),"99") +
                             string (day(tqDebtorMovements.ttPostingDate),"99") +
                             string (tqDebtorMovements.tiPostingYear) + 
                             tqDebtorMovements.tcJournalCode +  
                             string (tqDebtorMovements.tiPostingVoucher).
                   
           else 
               /* sort by ActivityPostingDate - Activity */ 
               assign tqDebtorAccountMovements.tcSortKey = 
                             string (year(tqDebtorMovements.ttPostingDate),"9999") +
                             string (month(tqDebtorMovements.ttPostingDate),"99") +
                             string (day(tqDebtorMovements.ttPostingDate),"99") +
                             string (tqDebtorMovements.tiPostingYear) + 
                             tqDebtorMovements.tcJournalCode +
                             string (tqDebtorMovements.tiPostingVoucher).

end. /* for each tqDebtorMovements */

/* ===================================================== */
/* 3 - Get the Customer Address Contact Data             */
/* ===================================================== */
empty temp-table tqDebtorAddressContactInfo.

<Q-84 run AddressTypeByCode (all) (Read) (NoCache)
   (input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
    output dataset tqAddressTypeByCode) in BAddressType>

find first tqAddressTypeByCode where
           tqAddressTypeByCode.tcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}
           no-error.
if available tqAddressTypeByCode
then do:
    <Q-74 run DebtorAddressContactInfo (all) (Read) (NoCache)
       (input viCompanyId, (CompanyId)
        input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
        input vcCandoBRCodeFilter, (CandoBRCode)
        input vcCandoDebtorCodeFilter, (CandoDebtorCode)
        input vcFromBRCodeFilter, (FromBRCode)
        input vcFromDebtorCodeFilter, (FromDebtorCode)
        input vcToBRCodeFilter, (ToBRCode)
        input vcToDebtorCodeFilter, (ToDebtorCode)
        input tqAddressTypeByCode.tiAddressType_ID, (AddressType_ID)
        output dataset tqDebtorAddressContactInfo) in BDebtorReport>
end.

for each tqDebtorAccountMovements
    break by tqDebtorAccountMovements.tiDebtor_ID :

    if first-of(tqDebtorAccountMovements.tiDebtor_ID)
    then do:
        /* First look for an address and a contact */
        find first tqDebtorAddressContactInfo where
                   tqDebtorAddressContactInfo.tiDebtor_ID = tqDebtorAccountMovements.tiDebtor_ID and
                   tqDebtorAddressContactInfo.tcContactName > ''
                   no-error.
        if not available tqDebtorAddressContactInfo
        then do :
            /* First look for an address only */
            find first tqDebtorAddressContactInfo where
                       tqDebtorAddressContactInfo.tiDebtor_ID = tqDebtorAccountMovements.tiDebtor_ID
                       no-error.
        end.
    
        if available tqDebtorAddressContactInfo
        then do :
            create tqCustomerInfo.
            assign tqCustomerInfo.tcDebtorCode            = tqDebtorAddressContactInfo.tcDebtorCode
                   tqCustomerInfo.tcBusinessRelationCode  = tqDebtorAddressContactInfo.tcBusinessRelationCode               
                   tqCustomerInfo.tcBusinessRelationName1 = tqDebtorAddressContactInfo.tcBusinessRelationName1
                   tqCustomerInfo.tcBusinessRelationName2 = tqDebtorAddressContactInfo.tcBusinessRelationName2
    
                   tqCustomerInfo.tcAddressStreet2        = tqDebtorAddressContactInfo.tcAddressStreet2
                   tqCustomerInfo.tcAddressStreet1        = tqDebtorAddressContactInfo.tcAddressStreet1
                   tqCustomerInfo.tcAddressFax            = tqDebtorAddressContactInfo.tcAddressFax
                   tqCustomerInfo.tcAddressCity           = tqDebtorAddressContactInfo.tcAddressCity
                   tqCustomerInfo.tcAddressZip            = tqDebtorAddressContactInfo.tcAddressZip
                   tqCustomerInfo.tcAddressTelephone      = tqDebtorAddressContactInfo.tcAddressTelephone
    
                   tqCustomerInfo.tcContactPhone          = tqDebtorAddressContactInfo.tcContactTelephone
                   tqCustomerInfo.tcContactName           = tqDebtorAddressContactInfo.tcContactName
                   tqCustomerInfo.tcContactMobile         = tqDebtorAddressContactInfo.tcContactMobilePhone
                   tqCustomerInfo.tcContactEmail          = tqDebtorAddressContactInfo.tcContactEmail.
        end.
    end.
end. /* for each tqDebtorAccountMovements */