project QadFinancials > class BCreditorReport > method CreditorAgeingAnalysisCurrent

report procedure


Parameters


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrCreditorAgeingAnalysisCurrentoutputdataset
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BCreditorReport.CreditorAACGroup


program code (program7/bcreditorreport.p)

empty temp-table tqCreditorAgeingAnalysisCurrent.

<M-20 run GetReportLabels
   (input  'CreditorAgeingAnalysisCurrent':U (icReportName), 
    input  icLanguageCode (icLanguageCode), 
    input  tFilter (tFilter), 
    output tqHeader (tqHeader), 
    output tqFilter (tqFilter), 
    output tqText (tqText), 
    output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

if viFcReturnSuper < 0 or
   oiReturnStatus  = 0
then assign oiReturnStatus = viFcReturnSuper.

if oiReturnStatus < 0
then return.

<M-1 run SetDataItemsBasedOnFilterTT
   (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

if viFcReturnSuper < 0 or
   oiReturnStatus  = 0
then assign oiReturnStatus = viFcReturnSuper.

if oiReturnStatus < 0
then return.

create tqFilter.
assign 
    tqFilter.ti_Sequence = 0
    tqFilter.tcBusinessFieldLabel = "icActivityCode":U
    tqFilter.tcBusinessFieldName = "SummaryByInfo":U
    tqFilter.tcParameterValue = vcSummaryByFilter1.

/* Calculate Ageing periods */
<M-2 run SetAgeingPeriod
   (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

if viFcReturnSuper < 0 or
   oiReturnStatus  = 0
then assign oiReturnStatus = viFcReturnSuper.

if oiReturnStatus < 0
then return.

<M-26 run ValidateAgeingReport
   (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

if viFcReturnSuper < 0 or
   oiReturnStatus  = 0
then assign oiReturnStatus = viFcReturnSuper.

if oiReturnStatus < 0
then return.

/* There cannot be year 0, this is converted to -999 so it won't be translated in querry as 0 -> true !!! */
if viToAccYearFilter1 = 0
then viToAccYearFilter1 = -999.

assign viToAccPeriodFilter1 = viToAccYearFilter1 * 100 + viToAccPeriodFilter1.

if vcReportingCurrencyFilter1 = ?
then assign vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC}.

<Q-33 run CInvoiceStageByInvoice (Start) in BCreditorReport >

/* Cross-company iteration */
do viCRCompanyIterator = 1 to viCRCompanyEntries:
    /* Get Info about Company (this method fills viCRCurrentCompany_ID) */
    <M-29 run CrossCompanyValues
       (input  viCRCompanyIterator (iiEntryNumber), 
        output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

    if viFcReturnSuper < 0 or
       oiReturnStatus  = 0
    then assign oiReturnStatus = viFcReturnSuper.

    if oiReturnStatus < 0
    then return.

    if vlIncludeUnpostedTransactions1 = ? then
        assign vlIncludeUnpostedTransactions1 = true.

    if vlIncludeUnpostedTransactions1 = true then
    do:
        assign vhFcComponent = ?.
          <M-40 run CheckForUnpostedTransByPerYr
             (input  viToAccYearFilter1 (iiYear), 
              input  viToAccPeriodFilter1 (iiPeriod), 
              output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
          if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
          then assign oiReturnStatus = viFcReturnSuper.
          if oiReturnStatus < 0 then return.
    end.

    <Q-3 run BaseCreditorAgeingAnalysisCurr (all) (Read) (NoCache)
       (input viCRCurrentCompany_ID, (CompanyId)
        input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
        input vcCandoBRCodeFilter1, (CandoBRCode)
        input vcCandoDivisionCodeFilter1, (CandoDivisionCode)
        input vcCandoJournalCodeFilter1, (CandoJournalCode)
        input vcCurrencyCodeFilter1, (CurrencyCode)
        input vcFromBRCodeFilter1, (FromBRCode)
        input vcFromCreditorCodeFilter1, (FromCreditorCode)
        input vcFromDivisionCodeFilter1, (FromDivisionCode)
        input vcFromJournalCodeFilter1, (FromJournalCode)
        input viToAccPeriodFilter1, (ToAccPeriod)
        input vcToBRCodeFilter1, (ToBRCode)
        input vcToCreditorCodeFilter1, (ToCreditorCode)
        input vcToDivisionCodeFilter1, (ToDivisionCode)
        input vcToJournalCodeFilter1, (ToJournalCode)
        input vcFromCContrGLCodeFilter1, (FromGLCode)
        input vcToCContrGLCodeFilter1, (ToGLCode)
        input vcCandoCContrGLCodeFilter1, (CanDoGLCode)
        input vcFromProjectCodeFilter1, (FromProjectCode)
        input vcToProjectCodeFilter1, (ToProjectCode)
        input vcCandoProjectCodeFilter1, (CanDoProjectCode)
        input vcFromCCCodeFilter1, (FromCCCode)
        input vcToCCCodeFilter1, (ToCCCode)
        input vcCanDoCCCodeFilter1, (CanDoCCCode)
        input vcToPaymentGroupFilter1, (ToPaymentGroup)
        input vcFromPaymentGroupFilter1, (FromPaymentGroup)
        input vcCandoPaymentGroupFilter1, (CandoPaymentGroup)
        output dataset tqBaseCreditorAgeingAnalysisCurr) in BCreditorReport>

    /* Do some extra filtering on the result set of the base query that can't be written in the query itself */
    for each tqBaseCreditorAgeingAnalysisCurr:
        
        /* Prepayments and Adjustments: valid only if "Within terms = yes" */
        /* if vlInvoicesWithinTermsFilter1                   <> true                      and
          (tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
           tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT})
        then next. */

        <Q-37 run CInvoiceStageByInvoice (all) (Read) (NoCache)
           (input tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID, (CInvoiceId)
            output dataset tqCInvoiceStageByInvoice) in BCreditorReport >

        /* If "Within terms = no", skip all invoices that aren't due yet */
        if vlInvoicesWithinTermsFilter1 = false
        then do:
            if can-find(first tqCInvoiceStageByInvoice where
                              tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID)
            then do:
                find first tqCInvoiceStageByInvoice where
                           tqCInvoiceStageByInvoice.tiCInvoice_ID           = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID and
                           tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate  < vtDateForAgeingCalcFilter1                     and
                           tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC > tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC
                           no-error.

                if not available tqCInvoiceStageByInvoice
                then next.
            end.
            else
            if tqBaseCreditorAgeingAnalysisCurr.ttCInvoiceDueDate >= vtDateForAgeingCalcFilter1
            then next.
        end.

        /* Create a report record */
        create tqCreditorAgeingAnalysisCurrent.
        buffer-copy tqBaseCreditorAgeingAnalysisCurr to tqCreditorAgeingAnalysisCurrent.

        assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = 0
               tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = 0
               tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC = 0
               tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = 0
               tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod5BalanceXC = 0
               vdBalanceCC  = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitCC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditCC
               vdBalanceLC  = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitLC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditLC
               vdBalanceTC  = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitTC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditTC
               vdOriginalCC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitCC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditCC
               vdOriginalLC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitLC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditLC
               vdOriginalTC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitTC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditTC.

        if tqBaseCreditorAgeingAnalysisCurr.tcPaymentConditionPdDiscType <> "" and
           tqBaseCreditorAgeingAnalysisCurr.tdPaymentConditionPercentage <> 0  and
           tqBaseCreditorAgeingAnalysisCurr.tiPaymentConditionDayMthDisc <> 0 
        then assign tqCreditorAgeingAnalysisCurrent.tlDiscountPaymentCondApl = true.

        case vcReportingCurrencyFilter1:
            when {&CURRENCYTYPE-CC}
            then assign vdBalanceXC  = vdBalanceCC
                        vdOriginalXC = vdOriginalCC.

            when {&CURRENCYTYPE-LC}
            then assign vdBalanceXC  = vdBalanceLC
                        vdOriginalXC = vdOriginalLC.

            when {&CURRENCYTYPE-TC}
            then assign vdBalanceXC  = vdBalanceTC
                        vdOriginalXC = vdOriginalTC.
        end.

        assign tqCreditorAgeingAnalysisCurrent.tdBalanceXC =
                   if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC}
                   then vdBalanceTC
                   else vdBalanceXC
               tqCreditorAgeingAnalysisCurrent.tdBalanceTC = vdBalanceTC.

        /* if tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
           tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT}
        then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = vdBalanceXC.
        else */
        if can-find(first tqCInvoiceStageByInvoice where
                          tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID)
        then do:

            for each tqCInvoiceStageByInvoice where
                     tqCInvoiceStageByInvoice.tiCInvoice_ID           = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID and
                     tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC > tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC:
                if vlInvoicesWithinTermsFilter1 = false and
                   tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate >= vtDateForAgeingCalcFilter1
                then next.

                case vcReportingCurrencyFilter1:
                    when {&CURRENCYTYPE-CC}
                    then assign vdStageAmount1XC = tqCInvoiceStageByInvoice.tdCInvoiceStageAmountCC - tqCInvoiceStageByInvoice.tdCInvoiceStageAmountCC * tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC / tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC.

                    when {&CURRENCYTYPE-LC}
                    then assign vdStageAmount1XC = tqCInvoiceStageByInvoice.tdCInvoiceStageAmountLC - tqCInvoiceStageByInvoice.tdCInvoiceStageAmountLC * tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC / tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC.

                    when {&CURRENCYTYPE-TC}
                    then assign vdStageAmount1XC = tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC - tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC.
                end.

                if vdStageAmount1XC < 0
                then vdXCValue = vdStageAmount1XC * -1.
                else vdXCValue = vdStageAmount1XC.

                /* if vdXCValue > 0
                then assign vdXCValue = vdXCValue * -1. */

                if tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-INVOICE} or
                   tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-INVOICECORRECTION}
                then assign vdXCValue = vdXCValue * -1.

                find first tAgeingPeriod1 where
                           tAgeingPeriod1.ttAgeingPeriodFromDate <= tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate and
                           tAgeingPeriod1.ttAgeingPeriodToDate   >= tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate
                           no-error.

                if available tAgeingPeriod1 /* and
                   vdBalanceXC < 0 */
                then do:
                    case tAgeingPeriod1.tiAgeingPeriodSeq:
                        when 1
                        then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC + vdXCValue.

                        when 2
                        then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC + vdXCValue.

                        when 3
                        then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC + vdXCValue.

                        when 4 or when 5 /* in the past there was a fifth column on the report maybe it gets back that's the reason for this solution */
                        then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC + vdXCValue.

                    end case.
                end.
            end.
        end.
        else do:
            find first tAgeingPeriod1 where
                       tAgeingPeriod1.ttAgeingPeriodFromDate <= tqCreditorAgeingAnalysisCurrent.ttCInvoiceDueDate and
                       tAgeingPeriod1.ttAgeingPeriodToDate   >= tqCreditorAgeingAnalysisCurrent.ttCInvoiceDueDate
                       no-error.

            if available tAgeingPeriod1
            then do:
                case tAgeingPeriod1.tiAgeingPeriodSeq:
                    when 1
                    then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = vdBalanceXC.

                    when 2
                    then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = vdBalanceXC.

                    when 3
                    then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC = vdBalanceXC.

                    when 4 or when 5 /* in the past there was a fifth column on the report maybe it gets back that's the reason for this solution */
                    then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = vdBalanceXC.

                end case.
            end.
        end.
    end.
end.

<Q-34 run CInvoiceStageByInvoice (Stop) in BCreditorReport >

/* Remove records that don't match the Supplier Balance filter */
/* Fill some extra info for the others                         */
for each tqCreditorAgeingAnalysisCurrent
         break by tqCreditorAgeingAnalysisCurrent.tcCreditorCode:
    accumulate tqCreditorAgeingAnalysisCurrent.tdBalanceXC (total by tqCreditorAgeingAnalysisCurrent.tcCreditorCode).
    
    assign tqCreditorAgeingAnalysisCurrent.tcSortByName3               = vcSortByFilter1
           tqCreditorAgeingAnalysisCurrent.tcCreditorAgeingTypeCurrent = vcAgeingTypeFilter1.
    if last-of(tqCreditorAgeingAnalysisCurrent.tcCreditorCode)
    then do:
        assign vdBalanceXC = accum total by tqCreditorAgeingAnalysisCurrent.tcCreditorCode tqCreditorAgeingAnalysisCurrent.tdBalanceXC.

        if vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
           vdBalanceXC              > 0                         or
           vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT}  and
           vdBalanceXC              < 0
        then do:
            for each bCreditorAgeingAnalysisCurrent where
                     bCreditorAgeingAnalysisCurrent.tcCreditorCode = tqCreditorAgeingAnalysisCurrent.tcCreditorCode and
                     rowid(bCreditorAgeingAnalysisCurrent)        <> rowid(tqCreditorAgeingAnalysisCurrent):
                delete bCreditorAgeingAnalysisCurrent.
            end.

            delete tqCreditorAgeingAnalysisCurrent.
        end.
        else do:
            assign vhFcComponent = ?.

            <M-38 run GetAddressWithType
               (input  tqCreditorAgeingAnalysisCurrent.tiBusinessRelation_ID (iiBusinessRelationId), 
                input  tqCreditorAgeingAnalysisCurrent.tcBusinessRelationCode (icBusinessRelationCode), 
                input  ? (iiAddressTypeId), 
                input  {&ADDRESSTYPECODESYSTEM-HEADOFFICE} (icAddressTypeCode), 
                output tAddressQueryRef1 (tAddressQuery), 
                output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>

            find first tAddressQueryRef1
                       no-error.

            if available tAddressQueryRef1
            then do:
                <Q-39 run ContactByAddress (all) (Read) (NoCache)
                   (input tAddressQueryRef1.tiAddress_ID, (AddressId)
                    output dataset tqContactByAddress) in BBusinessRelation >

                find first tqContactByAddress
                           no-error.

                for each bCreditorAgeingAnalysisCurrent where
                         bCreditorAgeingAnalysisCurrent.tcCreditorCode = tqCreditorAgeingAnalysisCurrent.tcCreditorCode:
                    assign bCreditorAgeingAnalysisCurrent.tcAddressCity        = tAddressQueryRef1.tcAddressCity
                           bCreditorAgeingAnalysisCurrent.tcAddressFax         = tAddressQueryRef1.tcAddressFax
                           bCreditorAgeingAnalysisCurrent.tcAddressStreet1     = tAddressQueryRef1.tcAddressStreet1
                           bCreditorAgeingAnalysisCurrent.tcAddressStreet2     = tAddressQueryRef1.tcAddressStreet2
                           bCreditorAgeingAnalysisCurrent.tcAddressStreet3     = tAddressQueryRef1.tcAddressStreet3
                           bCreditorAgeingAnalysisCurrent.tcAddressTelephone   = tAddressQueryRef1.tcAddressTelephone
                           bCreditorAgeingAnalysisCurrent.tcAddressZip         = tAddressQueryRef1.tcAddressZip
                           bCreditorAgeingAnalysisCurrent.tcAddressCounty      = tAddressQueryRef1.tcAddressCounty
                           bCreditorAgeingAnalysisCurrent.tcAddressState       = tAddressQueryRef1.tcAddressState
                           bCreditorAgeingAnalysisCurrent.tiAddressFormat      = tAddressQueryRef1.tiAddressFormat
                           bCreditorAgeingAnalysisCurrent.tcStateCode          = tAddressQueryRef1.tcStateCode
                           bCreditorAgeingAnalysisCurrent.tcCountyDescription  = tAddressQueryRef1.tcCountyDescription
                           bCreditorAgeingAnalysisCurrent.tcCountryDescription = tAddressQueryRef1.tcCountryDescription.

                    if available tqContactByAddress
                    then assign bCreditorAgeingAnalysisCurrent.tcContactEmail  = tqContactByAddress.tcContactEmail
                                bCreditorAgeingAnalysisCurrent.tcContactName   = tqContactByAddress.tcContactName
                                bCreditorAgeingAnalysisCurrent.tcContactMobile = tqContactByAddress.tcContactMobilePhone
                                bCreditorAgeingAnalysisCurrent.tcContactPhone  = tqContactByAddress.tcContactTelephone.
                end.
            end.
        end.
    end.
end.