project QadFinancials > class BCreditorReport > method CreditorAccountMovements

report procedure

Description

CreditorAccountMovements


Parameters


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrCreditorAccountMovementsoutputdataset
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


unused


program code (program7/bcreditorreport.p)

empty temp-table tqCreditorAccountMovements.
empty temp-table tAvailableCreditors.

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

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

if vlCheckHistoryFilterCR = ? or
   vlCheckHistoryFilterCR
then do:   
    <Q-42 assign vlFcQueryRecordsAvailable = QCInvoiceMovementAll (NoCache)
       (input viCompanyId, (CompanyId)
        input ?, (DaemonId)) in BQCInvoiceMovement >
    if vlFcQueryRecordsAvailable = true or
       vlFcQueryRecordsAvailable = ?
    then do:              
        <M-43 run SetMessage
           (input  #T-44'Run the Balance daemon to update the supplier balances.':70(65167)T-44# (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QADFIN-3440':U (icFcMsgNumber), 
            input  #T-45'There are still transactions in the Balance daemon queue. Run to Balance daemon to process the queue or data inconsistencies could occur.':150(65166)T-45# (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output oiReturnStatus (oiReturnStatus)) in BCreditorReport>                                              
        assign oiReturnStatus = -1.
        return.
    end.
end.

/* CA784858 - From - To Period need to be specified */
if viFromAccYearFilter1 = ? or viFromAccYearFilter1 = 0 or 
   viToAccYearFilter1 = ? or viToAccYearFilter1 = 0 or
   viFromAccPeriodFilter1 = ? or viToAccPeriodFilter1 = ?
then do:
    assign vcMessage = trim(#T-51'If you want to use the filter for year and period, provide all 4 parameters (from-to year and from-to period). Otherwise, use none.':255(64945)T-51#).
    <M-48 run SetMessage
       (input  vcMessage (icMessage), 
        input  '':U (icArguments), 
        input  '':U (icFieldName), 
        input  '':U (icFieldValue), 
        input  'E':U (icType), 
        input  3 (iiSeverity), 
        input  '':U (icRowid), 
        input  'QadFin-6856':U (icFcMsgNumber), 
        input  '' (icFcExplanation), 
        input  '':U (icFcIdentification), 
        input  '':U (icFcContext), 
        output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>     
    assign oiReturnStatus = -1.
    return.
end.   

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

if vcSummaryByFilter1 = '' or vcSummaryByFilter1 = ?
then assign vcSummaryByFilter1 = 'NONE':u.

assign vcCr = {&POSTINGTYPE-CREDIT-TR}
       vcDt = {&POSTINGTYPE-DEBIT-TR}.
       
/* ==================================================== */
/* 1 - Get the OpeningBalance at FromYearPeriod - 1     */
/* ==================================================== */
<Q-70 run CreditorBalanceCHist (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoBRCodeFilter1, (CandoBrCode)
    input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
    input vcCandoCContrGLCodeFilter1, (CandoGLCode)
    input viFromYearPeriodFilter1 - 1, (EndOfYearPeriod)
    input vcFromBRCodeFilter1, (FromBRCode)
    input vcFromCreditorCodeFilter1, (FromCreditorCode)
    input vcFromCContrGLCodeFilter1, (FromGLCode)
    input vcToBRCodeFilter1, (ToBRCode)
    input vcToCreditorCodeFilter1, (ToCreditorCode)
    input vcToCContrGLCodeFilter1, (ToGLCode)
    output dataset tqCreditorBalanceCHist) in BCreditorReport >
    
/* Get the OpeningBalance by Supplier Currency GL DayBook */
for each tqCreditorBalanceCHist
    break by tqCreditorBalanceCHist.tcCreditorCode
          by tqCreditorBalanceCHist.tcCurrencyCode
          by tqCreditorBalanceCHist.tcGLCode
          by tqCreditorBalanceCHist.tcJournalCode:

    if first-of (tqCreditorBalanceCHist.tcJournalCode)
    then assign vdOpeningBalanceTC = 0
                vdOpeningBalanceLC = 0
                vdOpeningBalanceCC = 0.
    
    assign vdOpeningBalanceCC = vdOpeningBalanceCC + tqCreditorBalanceCHist.tdCHistBalanceCC
           vdOpeningBalanceLC = vdOpeningBalanceLC + tqCreditorBalanceCHist.tdCHistBalanceLC           
           vdOpeningBalanceTC = vdOpeningBalanceTC + tqCreditorBalanceCHist.tdCHistBalanceTC.
                
    if last-of (tqCreditorBalanceCHist.tcJournalCode)
    then do :
        /* One section per supplier, holding supplier info + Opening balance */ 
        create tqCreditorAccountMovements.
        assign tqCreditorAccountMovements.tcCreditorCode             = tqCreditorBalanceCHist.tcCreditorCode
               tqCreditorAccountMovements.tiCreditor_ID              = tqCreditorBalanceCHist.tiCreditor_ID
               tqCreditorAccountMovements.tcCurrencyCode             = tqCreditorBalanceCHist.tcCurrencyCode
               tqCreditorAccountMovements.tcJournalCode              = tqCreditorBalanceCHist.tcJournalCode
               tqCreditorAccountMovements.tcJournalDescription       = tqCreditorBalanceCHist.tcJournalDescription
               tqCreditorAccountMovements.tcJournalTypeCode          = tqCreditorBalanceCHist.tcJournalTypeCode
               tqCreditorAccountMovements.tcGLCode                   = tqCreditorBalanceCHist.tcGLCode   
               tqCreditorAccountMovements.tcGLDescription            = tqCreditorBalanceCHist.TCGLDESCRIPTION
               tqCreditorAccountMovements.tcGLType                   = tqCreditorBalanceCHist.tcGLTypeCode
               tqCreditorAccountMovements.tiCurrencyNumberOfDecimals = tqCreditorBalanceCHist.tiCurrencyNumberOfDecimals
               tqCreditorAccountMovements.tdOpeningBalanceTC         = vdOpeningBalanceTC
               tqCreditorAccountMovements.tdOpeningBalanceLC         = vdOpeningBalanceLC
               tqCreditorAccountMovements.tdOpeningBalanceCC         = vdOpeningBalanceCC
               tqCreditorAccountMovements.tcSortKey                  = ?
               tqCreditorAccountMovements.tcActivityKey              = ?
               tqCreditorAccountMovements.tcInvoiceKey               = ?
               tqCreditorAccountMovements.tcIsInitial                = ?
               tqCreditorAccountMovements.tlInitialWithinPeriod      = ?
               tqCreditorAccountMovements.tcSummarizationInfo        = vcSummaryByFilter1
               tqCreditorAccountMovements.tcReportingInfo            = vcChronologicalLogicalFilter1
               tqCreditorAccountMovements.tlCreditorPerPageInfo      = vlCreditorPerPageFilter1
               tqCreditorAccountMovements.tcReportingCurrency        = vcReportingCurrencyFilter1.

    end. /* last-of ((tqCreditorBalanceCHist.tcCreditorCode) */
    
end. /* for each tqCreditorBalanceCHist */

/* ===================================================== */
/* 2 - Get all the movements between the selected period */
/* ===================================================== */   
<Q-97 run CreditorMovements (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoBRCodeFilter1, (CandoBRCode)
    input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
    input vcCandoCContrGLCodeFilter1, (CandoGLCode)
    input vcFromBRCodeFilter1, (FromBRCode)
    input vcFromCreditorCodeFilter1, (FromCreditorCode)
    input vcFromCContrGLCodeFilter1, (FromGLCode)
    input viFromYearPeriodFilter1, (FromYearPeriod)
    input vcToBRCodeFilter1, (ToBRCode)
    input vcToCreditorCodeFilter1, (ToCreditorCode)
    input vcToCContrGLCodeFilter1, (ToGLCode)
    input viToYearPeriodFilter1, (ToYearPeriod)
    output dataset tqCreditorMovements) in BCreditorReport >


/* Go through all the activities now */
for each tqCreditorMovements 
    break by tqCreditorMovements.tiCInvoice_ID
          by tqCreditorMovements.tcCInvoiceMovementType :

        if first-of (tqCreditorMovements.tcCInvoiceMovementType)
        then assign vlInitialWithinPeriod = (tqCreditorMovements.tcCInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}).

        create tqCreditorAccountMovements.
        assign tqCreditorAccountMovements.tcCreditorCode       = tqCreditorMovements.tcCreditorCode
               tqCreditorAccountMovements.tiCreditor_ID        = tqCreditorMovements.tiCreditor_ID
               tqCreditorAccountMovements.tcCurrencyCode       = tqCreditorMovements.tcCurrencyCode
               tqCreditorAccountMovements.tcJournalCode        = tqCreditorMovements.tcJournalCode
               tqCreditorAccountMovements.tcJournalDescription = tqCreditorMovements.tcJournalDescription
               tqCreditorAccountMovements.tcJournalTypeCode    = tqCreditorMovements.tcJournalTypeCode
               tqCreditorAccountMovements.tcGLDescription      = tqCreditorMovements.tcGLDescription
               tqCreditorAccountMovements.tcGLCode             = tqCreditorMovements.tcGLCode
               tqCreditorAccountMovements.tcGLType             = tqCreditorMovements.tcGLTypeCode

               tqCreditorAccountMovements.ttCInvoiceDate             = tqCreditorMovements.ttCInvoiceDate
               tqCreditorAccountMovements.ttCInvoiceDiscountDueDate  = tqCreditorMovements.ttCInvoiceDiscountDueDate
               tqCreditorAccountMovements.ttCInvoiceDueDate          = tqCreditorMovements.ttCInvoiceDueDate
               tqCreditorAccountMovements.tcCInvoiceReference        = tqCreditorMovements.tcCInvoiceReference
               tqCreditorAccountMovements.tiCurrencyNumberOfDecimals = tqCreditorMovements.tiCurrencyNumberOfDecimals
               tqCreditorAccountMovements.tlInitialWithinPeriod      = vlInitialWithinPeriod
               tqCreditorAccountMovements.tiPeriodYear               = tqCreditorMovements.tiPostingYear   
               tqCreditorAccountMovements.tiPeriodPeriod             = tqCreditorMovements.tiPostingPeriod
               tqCreditorAccountMovements.ttPostingDate              = tqCreditorMovements.ttPostingDate
               tqCreditorAccountMovements.tiPostingLineSequence      = tqCreditorMovements.tiPostingLineSequence
               tqCreditorAccountMovements.tcPostingLineText          = tqCreditorMovements.tcPostingLineText
               tqCreditorAccountMovements.tcPostingText              = tqCreditorMovements.tcPostingText
               tqCreditorAccountMovements.tiPostingVoucher           = tqCreditorMovements.tiPostingVoucher

               tqCreditorAccountMovements.tdAmountCC                 = tqCreditorMovements.tdPostingLineDebitCC - tqCreditorMovements.tdPostingLineCreditCC
               tqCreditorAccountMovements.tdAmountLC                 = tqCreditorMovements.tdPostingLineDebitLC - tqCreditorMovements.tdPostingLineCreditLC
               tqCreditorAccountMovements.tdAmountTC                 = tqCreditorMovements.tdPostingLineDebitTC - tqCreditorMovements.tdPostingLineCreditTC            
               tqCreditorAccountMovements.tcActivityKey              = string (tqCreditorMovements.tiPostingYear) + "/" +
               tqCreditorMovements.tcJournalCode + "/" + 
                                                                       string (tqCreditorMovements.tiPostingVoucher, "999999999")
               tqCreditorAccountMovements.tcInvoiceKey               = string (tqCreditorMovements.tiCInvoicePostingYear) + "/" +
                                                 /*kkh*/ /*            tqCreditorMovements.tcInvoiceJournalCode + "/" + */
                                                                       string (tqCreditorMovements.tiCInvoiceVoucher, "999999999")
               tqCreditorAccountMovements.tcPostingInvoiceReferenceText = tqCreditorAccountMovements.tcInvoiceKey + ' ' + 
                                                                         tqCreditorMovements.tcCInvoiceReference  

               tqCreditorAccountMovements.tcIsInitial               = if tqCreditorMovements.tcCInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}
                                                                      then '*'
                                                                      else ''
               tqCreditorAccountMovements.tcSummarizationInfo        = vcSummaryByFilter1
               tqCreditorAccountMovements.tcReportingInfo            = vcChronologicalLogicalFilter1
               tqCreditorAccountMovements.tlCreditorPerPageInfo      = vlCreditorPerPageFilter1               
               tqCreditorAccountMovements.tcReportingCurrency        = vcReportingCurrencyFilter1.
                                                                        
           if vcChronologicalLogicalFilter1 = {&CREDITORSORT-LOGICAL}
           then 
               /* sort by InvoicePostingDate - Invoice - Activity */ 
               assign tqCreditorAccountMovements.tcSortKey = 
                             string (year(tqCreditorMovements.ttCInvoicePostingDate),"9999") +
                             string (month(tqCreditorMovements.ttCInvoicePostingDate),"99") +
                             string (day(tqCreditorMovements.ttCInvoicePostingDate),"99") +
                             string (tqCreditorMovements.tiCInvoicePostingYear) + 
/*kkh*/ /*                             tqCreditorMovements.tcInvoiceJournalCode  +*/
                             string (tqCreditorMovements.tiCInvoiceVoucher) +
                             string (year(tqCreditorMovements.ttPostingDate),"9999") +
                             string (month(tqCreditorMovements.ttPostingDate),"99") +
                             string (day(tqCreditorMovements.ttPostingDate),"99") +
                             string (tqCreditorMovements.tiPostingYear) + 
                             tqCreditorMovements.tcJournalCode +  
                             string (tqCreditorMovements.tiPostingVoucher).
                   
           else 
               /* sort by ActivityPostingDate - Activity */ 
               assign tqCreditorAccountMovements.tcSortKey = 
                             string (year(tqCreditorMovements.ttPostingDate),"9999") +
                             string (month(tqCreditorMovements.ttPostingDate),"99") +
                             string (day(tqCreditorMovements.ttPostingDate),"99") +
                             string (tqCreditorMovements.tiPostingYear) + 
                             tqCreditorMovements.tcJournalCode +
                             string (tqCreditorMovements.tiPostingVoucher).

end. /* for each tqCreditorMovements */

/* ===================================================== */
/* 3 - Get the Supplier Address Contact Data             */
/* ===================================================== */
<Q-17 run CreditorAddressContactInfo (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcAddressTypeCode, (AddressTypeCode)
    input vcCandoBRCodeFilter1, (CandoBRCode)
    input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
    input vcFromBRCodeFilter1, (FromBRCode)
    input vcFromCreditorCodeFilter1, (FromCreditorCode)
    input vcToBRCodeFilter1, (ToBRCode)
    input vcToCreditorCodeFilter1, (ToCreditorCode)
    output dataset tqCreditorAddressContactInfo) in BCreditorReport >   
    
    
    
for each tqCreditorAccountMovements
    break by tqCreditorAccountMovements.tiCreditor_ID :

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