project QadFinancials > class BDebtorReport > method DebtorAccountsHistoryDet

report procedure


Parameters


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrDebtorAccountsHistoryDetoutputdataset
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


unused


program code (program7/bdebtorreport.p)

empty temp-table tqDebtorAccountsHistory.
empty temp-table tqDebtorAccountsHistoryDet.

<M-1 run GetReportLabels
   (input  'DebtorAccountsHistoryDet':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-2 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-3 assign vlFcQueryRecordsAvailable = QDInvoiceMovementAll (NoCache)
       (input viCompanyId, (CompanyId)
        input ?, (DaemonId)) in BQDInvoiceMovement >
    if vlFcQueryRecordsAvailable = true or
       vlFcQueryRecordsAvailable = ?
    then do:
        <M-4 run SetMessage
           (input  #T-5'Run the Balance daemon to update customer balances.':70(65165)t-5# (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QADFIN-4114':U (icFcMsgNumber), 
            input  #T-6'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-6# (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output oiReturnStatus (oiReturnStatus)) in BDebtorReport> 
        assign oiReturnStatus = -1.
        return.
    end.
end.

/* eliminate debtors within Debtor Balance criteria */
/*filter out records according to 
ReportingCurrencyFilter, CurrencyCodeFilter, DebtorBalance info*/
<Q-7 run DebtorBalance (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input ?, (DebtorId)
    input viCurrencyIdFilter, (CurrencyId)
    input vcFromDebtorCodeFilter, (FromDebtor)
    input vcToDebtorCodeFilter, (ToDebtor)
    input vcCandoDebtorCodeFilter, (CanDoDebtor)
    input ?, (ActiveDebtors)
    input ?, (WithReminders)
    output dataset tqDebtorBalance) in BDebtorReport >
                         
/*Calculates sums of CC,LC and TC
It is obvious that sum of TC doesn't make sens if no currency is specified
that is when vcCurrencyCodeFilter is blank and viCurrencyIdFilter is ? 
but for TC it should return correct sum*/
assign vdSumDebtorBalanceCC = 0
       vdSumDebtorBalanceLC = 0
       vdSumDebtorBalanceTC = 0.
for each tqDebtorBalance break by tqDebtorBalance.tiDebtor_ID :
    /*eliminate ? values*/       
    assign vdSumDebtorBalanceCC = vdSumDebtorBalanceCC + if tqDebtorBalance.tdDBalanceCC = ? then 0 else tqDebtorBalance.tdDBalanceCC
           vdSumDebtorBalanceLC = vdSumDebtorBalanceLC + if tqDebtorBalance.tdDBalanceLC = ? then 0 else tqDebtorBalance.tdDBalanceLC
           vdSumDebtorBalanceTC = vdSumDebtorBalanceTC + if tqDebtorBalance.tdDBalanceTC = ? then 0 else tqDebtorBalance.tdDBalanceTC.

    if last-of(tqDebtorBalance.tiDebtor_ID)
    then do:
        /*eliminate Debtor if his balance doesn't fit the filter criteria*/
        /*UC says
        when currency selected  DebtorBalance in selected currency
        otherwise summ of Debtorbalances in LC
        not found in selected currency => not valid    
        >  0 when <selection> Debtor Balance = D
        <  0 when <selection> Debtor Balance = C
        <> 0 when <selection> Debtor Balance = B    
        otherwise  valid (A or empty)*/        
                         if /*CC*/
           (vcReportingCurrencyFilter = {&CURRENCYTYPE-CC} and
            vcDebtorBalanceFilter     = {&DEBTORBALANCE-CREDIT} and
            vdSumDebtorBalanceCC      > 0) or
           (vcReportingCurrencyFilter = {&CURRENCYTYPE-CC} and
            vcDebtorBalanceFilter     = {&DEBTORBALANCE-DEBIT} and
            vdSumDebtorBalanceCC      < 0) or
           (vcReportingCurrencyFilter = {&CURRENCYTYPE-CC} and
            vcDebtorBalanceFilter     = {&DEBTORBALANCE-BALANCE} and
            vdSumDebtorBalanceCC      = 0) or
           (vcReportingCurrencyFilter = {&CURRENCYTYPE-CC} and
            vcDebtorBalanceFilter     <> {&DEBTORBALANCE-ALL} and
            vdSumDebtorBalanceCC      = ?) or
            /*LC*/
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-CREDIT} and
            vdSumDebtorBalanceLC      > 0) or
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-DEBIT} and
            vdSumDebtorBalanceLC      < 0) or
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-BALANCE} and
            vdSumDebtorBalanceLC      = 0) or
           (vcDebtorBalanceFilter     <> {&DEBTORBALANCE-ALL} and
            vdSumDebtorBalanceLC      = ?) or
           /*TC*/
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-CREDIT} and
            vdSumDebtorBalanceTC      > 0) or
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-DEBIT} and
            vdSumDebtorBalanceTC      < 0) or
           (vcDebtorBalanceFilter     = {&DEBTORBALANCE-BALANCE} and
            vdSumDebtorBalanceTC      = 0) or
           (vcDebtorBalanceFilter     <> {&DEBTORBALANCE-ALL} and
            vdSumDebtorBalanceTC      = ?)
        then do:
            /* Actions for debtor who did not match our criteria */              
        end.
        else do:
            create tAvailableDebtors.
            assign tAvailableDebtors.tiDebtor_ID = tqDebtorBalance.tiDebtor_ID.
        end.
        assign vdSumDebtorBalanceCC = 0
               vdSumDebtorBalanceLC = 0
               vdSumDebtorBalanceTC = 0.
    end. /* if last-of */
end. /* for each debtor balance */

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

if viFromAccPeriodFilter = ? then assign viFromAccPeriodFilter = 1.
if viToAccPeriodFilter = ? then assign viToAccPeriodFilter = 12.

assign viToAccPeriodFilter = viToAccYearFilter * 100 + viToAccPeriodFilter
       viFromAccPeriodFilter = viFromAccYearFilter * 100 + viFromAccPeriodFilter.

/* ================ */
/* Call base querry */
/* ================ */
<Q-8 run DHistForDebtorAccountsSummary (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcFromDebtorCodeFilter, (FromDebtorCode)
    input vcToDebtorCodeFilter, (ToDebtorCode)
    input vcCandoDebtorCodeFilter, (CandoDebtorCode)
    input vcFromBRCodeFilter, (FromBRCode)
    input vcToBRCodeFilter, (ToBRCode)
    input vcCandoBRCodeFilter, (CandoBRCode)
    input viToAccPeriodFilter, (ToAccYearPeriod)
    input viFromAccPeriodFilter, (FromAccYearPeriod)
    output dataset tqDHistForDebtorAccountsSummary) in BDebtorReport >

/* Set initial values if some not entered */
if vcReportingCurrencyFilter = ?
then vcReportingCurrencyFilter = {&CURRENCYTYPE-LC}.

for each tqDHistForDebtorAccountsSummary break by tqDHistForDebtorAccountsSummary.tcDebtorCode: /*4233*/

    find first tAvailableDebtors where tAvailableDebtors.tiDebtor_ID = tqDHistForDebtorAccountsSummary.tiDebtor_ID no-error.
    if not available tAvailableDebtors then next.
    
    if tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceTC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceTC = 0.
    if tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteTC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteTC = 0.
    if tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceTC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceTC = 0.
    if tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteTC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteTC = 0.
    
    if vcReportingCurrencyFilter = {&CURRENCYTYPE-LC} or 
       vcReportingCurrencyFilter = {&CURRENCYTYPE-TC}
    then do:
        if tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceLC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceLC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteLC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteLC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceLC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceLC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteLC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteLC = 0.
    end.
    else do:
        if tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceCC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceCC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteCC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteCC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceCC  = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceCC = 0.
        if tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteCC = ? then assign tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteCC = 0.
    end.
    
    CASE vcDetailLevelFilter:
        WHEN {&DEBTORDETAILLEVEL-PERIOD} THEN
            create tqDebtorAccountsHistory.
        WHEN {&DEBTORDETAILLEVEL-YEAR} THEN  
        DO:
            if not can-find(tqDebtorAccountsHistory where 
                    tqDebtorAccountsHistory.tcDebtorCode = tqDHistForDebtorAccountsSummary.tcDebtoRCODE and
                    tqDebtorAccountsHistory.tiPeriodYear = if tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom  > 0
                                                           then truncate(tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom / 100,0)
                                                           else 0)
            then create tqDebtorAccountsHistory.
        END.
        WHEN {&DEBTORDETAILLEVEL-DEBTOR} THEN  
        DO:
            if not can-find(tqDebtorAccountsHistory where 
                    tqDebtorAccountsHistory.tcDebtorCode = tqDHistForDebtorAccountsSummary.tcDebtorCode)
            then create tqDebtorAccountsHistory.
        END.
        WHEN {&DEBTORDETAILLEVEL-CURRENCY} THEN  
        DO:
            if not can-find(tqDebtorAccountsHistory where 
                    tqDebtorAccountsHistory.tcDebtorCode = tqDHistForDebtorAccountsSummary.tcDebtoRCODE and
                    tqDebtorAccountsHistory.tcCurrencyCode = tqDHistForDebtorAccountsSummary.tcCurrencyCode)       
            then create tqDebtorAccountsHistory.
        END.
    END CASE. 
    
       
    assign tqDebtorAccountsHistory.tcBusinessRelationName1 = tqDHistForDebtorAccountsSummary.tcBusinessRelationName1
           tqDebtorAccountsHistory.tcBusinessRelationName2 = tqDHistForDebtorAccountsSummary.tcBusinessRelationName2
           tqDebtorAccountsHistory.tcBusinessRelationCode  = tqDHistForDebtorAccountsSummary.tcBusinessRelationCode
           tqDebtorAccountsHistory.tiBusinessRelation_ID   = tqDHistForDebtorAccountsSummary.tiBusinessRelation_ID
           tqDebtorAccountsHistory.tcCurrencyCode          = tqDHistForDebtorAccountsSummary.tcCurrencyCode
           tqDebtorAccountsHistory.tiCurrencyNumberOfDecimals = tqDHistForDebtorAccountsSummary.tiCurrencyNumberOfDecimals
           tqDebtorAccountsHistory.tcDebtorCode            = tqDHistForDebtorAccountsSummary.tcDebtorCode
           tqDebtorAccountsHistory.tdDebtorCreditNotesTC = tqDebtorAccountsHistory.tdDebtorCreditNotesTC + tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteTC
           tqDebtorAccountsHistory.tdGrossMovementsTC      = tqDebtorAccountsHistory.tdGrossMovementsTC      + tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceTC - tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteTC
           tqDebtorAccountsHistory.tdDebtorInvoicesTC    = tqDebtorAccountsHistory.tdDebtorInvoicesTC    + tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceTC
           tqDebtorAccountsHistory.tdNetMovementsTC        = tqDebtorAccountsHistory.tdNetMovementsTC        + tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceTC - tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteTC
           tqDebtorAccountsHistory.tdDebtorCreditNotesRC = 
           tqDebtorAccountsHistory.tdDebtorCreditNotesRC + if vcReportingCurrencyFilter = {&CURRENCYTYPE-CC}
                                                               then tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteCC
                                                               else tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteLC
           tqDebtorAccountsHistory.tdDebtorInvoicesRC    = 
           tqDebtorAccountsHistory.tdDebtorInvoicesRC    + if vcReportingCurrencyFilter = {&CURRENCYTYPE-CC}
                                                               then tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceCC
                                                               else tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceLC
           tqDebtorAccountsHistory.tdNetMovementsRC        = 
           tqDebtorAccountsHistory.tdNetMovementsRC        + if vcReportingCurrencyFilter = {&CURRENCYTYPE-CC}
                                                               then (tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceCC - tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteCC)
                                                               else (tqDHistForDebtorAccountsSummary.tdDHistNettoInvoiceLC - tqDHistForDebtorAccountsSummary.tdDHistNettoCredNoteLC)
           tqDebtorAccountsHistory.tdGrossMovementsRC      = 
           tqDebtorAccountsHistory.tdGrossMovementsRC      + if vcReportingCurrencyFilter = {&CURRENCYTYPE-CC}
                                                               then (tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceCC - tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteCC)
                                                               else (tqDHistForDebtorAccountsSummary.tdDHistGrossInvoiceLC - tqDHistForDebtorAccountsSummary.tdDHistGrossCredNoteLC) 
           tqDebtorAccountsHistory.tcSortBy                = if vcDetailLevelFilter = ? or vcDetailLevelFilter = "":U 
                                                             then 
                                                                 {&DEBTORDETAILLEVEL-DEBTOR}
                                                             else
                                                                 vcDetailLevelFilter    
           tqDebtorAccountsHistory.tiPeriodPeriod          = if tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom > 0
                                                             then tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom mod 100 else 0
           tqDebtorAccountsHistory.tiPeriodYear            = if tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom  > 0
                                                             then truncate(tqDHistForDebtorAccountsSummary.tiDHistYearPeriodFrom / 100,0) else 0.                          
  
end.

/*Calculates some extra information*/
if vlCalcExtraInfoFilter = true
then for each tqDebtorAccountsHistory break by tqDebtorAccountsHistory.tcDebtorCode:
    /*Calculates Address information for the BusinessRelation_ID*/ 
    assign vhFcComponent = ?
           vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
    if first-of(tqDebtorAccountsHistory.tcDebtorCode)
    then do:
        <M-9 run GetAddressWithType
           (input  tqDebtorAccountsHistory.tiBusinessRelation_ID (iiBusinessRelationId), 
            input  tqDebtorAccountsHistory.tcBusinessRelationCode (icBusinessRelationCode), 
            input  ? (iiAddressTypeId), 
            input  vcAddressTypeCode (icAddressTypeCode), 
            output tAddressQueryRef (tAddressQuery), 
            output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>    
        find first tAddressQueryRef no-error.
        /*Calculates Contact info */
        <Q-11 run ContactByAddress (all) (Read) (NoCache)
           (input tAddressQueryRef.tiAddress_ID, (AddressId)
            output dataset tqContactByAddress) in BBusinessRelation >
        find first tqContactByAddress no-error.
    end.
    if available tAddressQueryRef
    then assign tqDebtorAccountsHistory.tcAddressCity     = tAddressQueryRef.tcAddressCity
                tqDebtorAccountsHistory.tcAddressFax      = tAddressQueryRef.tcAddressFax
                tqDebtorAccountsHistory.tcAddressStreet1  = tAddressQueryRef.tcAddressStreet1
                tqDebtorAccountsHistory.tcAddressStreet2  = tAddressQueryRef.tcAddressStreet2
                tqDebtorAccountsHistory.tcAddressTelephone= tAddressQueryRef.tcAddressTelephone
                tqDebtorAccountsHistory.tcAddressZip      = tAddressQueryRef.tcAddressZip.
    if available tqContactByAddress
    then assign tqDebtorAccountsHistory.tcContactEmail = tqContactByAddress.tcContactEmail
                tqDebtorAccountsHistory.tcContactName  = tqContactByAddress.tcContactName.
end.

for each tqDebtorAccountsHistory:
    create tqDebtorAccountsHistoryDet.
    buffer-copy tqDebtorAccountsHistory to tqDebtorAccountsHistoryDet.
    assign tqDebtorAccountsHistoryDet.tcSortBy = if vcDetailLevelFilter = ? or vcDetailLevelFilter = "":U 
                                                 then
                                                     {&DEBTORDETAILLEVEL-DEBTOR}
                                                 else
                                                     vcDetailLevelFilter.         
end.