project QadFinancials > class BCreditorReport > method CreditorAccountsHistory

report procedure


Parameters


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrCreditorAccountsHistoryoutputdataset
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


unused


program code (program7/bcreditorreport.p)

/*Sorting is done on CristalReport side*/

empty temp-table tqCreditorAccountsHistory.

<M-8 run GetReportLabels
   (input  'CreditorAccountsHistory':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-4 run SetDataItemsBasedOnFilterTT  (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>

if vlCheckHistoryFilterCR = ? or
   vlCheckHistoryFilterCR
then do:   
    <Q-9 assign vlFcQueryRecordsAvailable = QCInvoiceMovementAll (NoCache)
       (input viCompanyId, (CompanyId)
        input ?, (DaemonId)) in BQCInvoiceMovement >
    if vlFcQueryRecordsAvailable = true or
       vlFcQueryRecordsAvailable = ?
    then do:              
        <M-10 run SetMessage
           (input  #T-11'Run the Balance daemon to update the supplier balances.':70(65167)t-11# (icMessage), 
            input  '':U (icArguments), 
            input  '':U (icFieldName), 
            input  '':U (icFieldValue), 
            input  'E':U (icType), 
            input  3 (iiSeverity), 
            input  '':U (icRowid), 
            input  'QADFIN-3463':U (icFcMsgNumber), 
            input  #T-12'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-12# (icFcExplanation), 
            input  '':U (icFcIdentification), 
            input  '':U (icFcContext), 
            output oiReturnStatus (oiReturnStatus)) in BCreditorReport>                                              
        assign oiReturnStatus = -1.
        return.
    end.
end.

/* eliminate Creditors within Creditor Balance criteria */
/*filter out records according to 
ReportingCurrencyFilter, CurrencyCodeFilter, CreditorBalance info*/
<Q-3 run CreditorBalance (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoCreditorCodeFilter1, (CanDoCreditor)
    input viCurrencyIdFilter1, (CurrencyId)
    input ?, (CreditorId)
    input vcFromCreditorCodeFilter1, (FromCreditor)
    input vcToCreditorCodeFilter1, (ToCreditor)
    output dataset tqCreditorBalance) in BCreditorReport >

/*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 vdSumCreditorBalanceCC = 0
       vdSumCreditorBalanceLC = 0
       vdSumCreditorBalanceTC = 0.
for each tqCreditorBalance break by tqCreditorBalance.tiCreditor_ID :
    /*eliminate ? values*/       
    assign vdSumCreditorBalanceCC = vdSumCreditorBalanceCC + if tqCreditorBalance.tdCBalanceCC = ? then 0 else tqCreditorBalance.tdCBalanceCC
           vdSumCreditorBalanceLC = vdSumCreditorBalanceLC + if tqCreditorBalance.tdCBalanceLC = ? then 0 else tqCreditorBalance.tdCBalanceLC
           vdSumCreditorBalanceTC = vdSumCreditorBalanceTC + if tqCreditorBalance.tdCBalanceTC = ? then 0 else tqCreditorBalance.tdCBalanceTC.

    if last-of(tqCreditorBalance.tiCreditor_ID)
    then do:
        /*eliminate Creditor if his balance doesn't fit the filter criteria*/
        /*UC says
        when currency selected  CreditorBalance in selected currency
        otherwise summ of Creditorbalances in LC
        not found in selected currency => not valid    
        >  0 when <selection> Creditor Balance = D
        <  0 when <selection> Creditor Balance = C
        <> 0 when <selection> Creditor Balance = B    
        otherwise  valid (A or empty)*/
        if /*CC*/
           (vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
            vcCreditorBalanceFilter1     = {&CREDITORBALANCE-CREDIT} and
            vdSumCreditorBalanceCC      > 0) or
           (vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
            vcCreditorBalanceFilter1     = {&CREDITORBALANCE-DEBIT} and
            vdSumCreditorBalanceCC      < 0) or
           (vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
            vcCreditorBalanceFilter1     = {&CREDITORBALANCE-BALANCE} and
            vdSumCreditorBalanceCC      = 0) or
           (vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
            vcCreditorBalanceFilter1     <> {&CREDITORBALANCE-ALL} and
            vdSumCreditorBalanceCC      = ?) or
            /*LC*/
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-CREDIT} and
            vdSumCreditorBalanceLC      > 0) or
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-DEBIT} and
            vdSumCreditorBalanceLC      < 0) or
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-BALANCE} and
            vdSumCreditorBalanceLC      = 0) or
           (vcCreditorBalanceFilter1     <> {&CREDITORBALANCE-ALL} and
            vdSumCreditorBalanceLC      = ?) or
           /*TC*/
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-CREDIT} and
            vdSumCreditorBalanceTC      > 0) or
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-DEBIT} and
            vdSumCreditorBalanceTC      < 0) or
           (vcCreditorBalanceFilter1     = {&CREDITORBALANCE-BALANCE} and
            vdSumCreditorBalanceTC      = 0) or
           (vcCreditorBalanceFilter1     <> {&CREDITORBALANCE-ALL} and
            vdSumCreditorBalanceTC      = ?)
        then do:
            /* Actions for Creditor who did not match our criteria */              
        end.
        else do:
            create tAvailableCreditors.
            assign tAvailableCreditors.tiCreditor_ID = tqCreditorBalance.tiCreditor_ID.
        end.
        assign vdSumCreditorBalanceCC = 0
               vdSumCreditorBalanceLC = 0
               vdSumCreditorBalanceTC = 0.
    end. /* if last-of */
end. /* for each Creditor balance */

/* 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.
if viFromAccPeriodFilter1 = 0 then viFromAccPeriodFilter1 = -999.

assign viToAccPeriodFilter1 = viToAccYearFilter1 * 100 + viToAccPeriodFilter1
       viFromAccPeriodFilter1 = viFromAccYearFilter1 * 100 + viFromAccPeriodFilter1.

/* ================ */
/* Call base querry */
/* ================ */
<Q-5 run BaseCreditorAccounts (all) (Read) (NoCache)
   (input viCompanyId, (CompanyId)
    input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
    input vcCandoBRCodeFilter1, (CandoBRCode)
    input vcFromBRCodeFilter1, (FromBRCode)
    input vcFromCreditorCodeFilter1, (FromCreditorCode)
    input viToAccPeriodFilter1, (ToAccYearPeriod)
    input vcToBRCodeFilter1, (ToBRCode)
    input vcToCreditorCodeFilter1, (ToCreditorCode)
    input viFromAccPeriodFilter1, (FromAccYearPeriod)
    output dataset tqBaseCreditorAccounts) in BCreditorReport >

FOR EACH tqBaseCreditorAccounts by tqBaseCreditorAccounts.tcCreditorCode:

    find first tAvailableCreditors where tAvailableCreditors.tiCreditor_ID = tqBaseCreditorAccounts.tiCreditor_ID no-error.
    if not available tAvailableCreditors then next.

    if tqBaseCreditorAccounts.tdCHistNettoInvoiceTC  = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceTC = 0.
    if tqBaseCreditorAccounts.tdCHistNettoCredNoteTC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteTC = 0.
    if tqBaseCreditorAccounts.tdCHistGrossInvoiceTC  = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceTC = 0.
    if tqBaseCreditorAccounts.tdCHistGrossCredNoteTC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteTC = 0.
    
    if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC} or 
       vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC}
    then do:
        if tqBaseCreditorAccounts.tdCHistNettoInvoiceLC  = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceLC = 0.
        if tqBaseCreditorAccounts.tdCHistNettoCredNoteLC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteLC = 0.
        if tqBaseCreditorAccounts.tdCHistGrossInvoiceLC  = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceLC = 0.
        if tqBaseCreditorAccounts.tdCHistGrossCredNoteLC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteLC = 0.        
    end.
    else do:
        if tqBaseCreditorAccounts.tdCHistNettoInvoiceCC  = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceCC = 0.
        if tqBaseCreditorAccounts.tdCHistNettoCredNoteCC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteCC = 0.
        if tqBaseCreditorAccounts.tdCHistGrossInvoiceCC  = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceCC = 0.
        if tqBaseCreditorAccounts.tdCHistGrossCredNoteCC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteCC = 0.       
    end.
    
    case vcDetailLevelFilter1:
        when {&CREDITORDETAILLEVEL-PERIOD} then 
            create tqCreditorAccountsHistory.
        when {&CREDITORDETAILLEVEL-YEAR} then
        do:
            if not can-find(tqCreditorAccountsHistory where 
                    tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode and
                    tqCreditorAccountsHistory.tiPeriodYear = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom  > 0
                                                           then truncate(tqBaseCreditorAccounts.tiCHistYearPeriodFrom / 100,0)
                                                           else 0)
            then create  tqCreditorAccountsHistory.
        end.    
        when {&CREDITORDETAILLEVEL-CREDITOR} THEN  
        do:
            if not can-find(tqCreditorAccountsHistory where 
                    tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode )
            then create tqCreditorAccountsHistory.
        end.
        when {&CREDITORDETAILLEVEL-CURRENCY} THEN  
        do:
            if not can-find(tqCreditorAccountsHistory where 
                    tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode and
                    tqCreditorAccountsHistory.tcCurrencyCode = tqBaseCreditorAccounts.tcCurrencyCode)       
            then create tqCreditorAccountsHistory.
        end.
    END CASE. 
    
    assign tqCreditorAccountsHistory.tcBusinessRelationName1 = tqBaseCreditorAccounts.tcBusinessRelationName1
           tqCreditorAccountsHistory.tcBusinessRelationName2 = tqBaseCreditorAccounts.tcBusinessRelationName2
           tqCreditorAccountsHistory.tcBusinessRelationCode  = tqBaseCreditorAccounts.tcBusinessRelationCode
           /* BTS 4876 : Assign the BusRel ID */
           tqCreditorAccountsHistory.tiBusinessRelation_ID   = tqBaseCreditorAccounts.tiBusinessRelation_ID
           /* BTS 4876 : Assign the BusRel ID */
           tqCreditorAccountsHistory.tcCurrencyCode          = tqBaseCreditorAccounts.tcCurrencyCode
           tqCreditorAccountsHistory.tcCreditorCode          = tqBaseCreditorAccounts.tcCreditorCode
           tqCreditorAccountsHistory.tdCreditorCreditNotesTC = tqCreditorAccountsHistory.tdCreditorCreditNotesTC + tqBaseCreditorAccounts.tdCHistNettoCredNoteTC
           tqCreditorAccountsHistory.tdGrossMovementsTC      = tqCreditorAccountsHistory.tdGrossMovementsTC      + tqBaseCreditorAccounts.tdCHistGrossInvoiceTC - tqBaseCreditorAccounts.tdCHistGrossCredNoteTC
           tqCreditorAccountsHistory.tdCreditorInvoicesTC    = tqCreditorAccountsHistory.tdCreditorInvoicesTC    + tqBaseCreditorAccounts.tdCHistNettoInvoiceTC
           tqCreditorAccountsHistory.tdNetMovementsTC        = tqCreditorAccountsHistory.tdNetMovementsTC        + tqBaseCreditorAccounts.tdCHistNettoInvoiceTC - tqBaseCreditorAccounts.tdCHistNettoCredNoteTC
           tqCreditorAccountsHistory.tdCreditorCreditNotesRC = 
           tqCreditorAccountsHistory.tdCreditorCreditNotesRC + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
                                                               then tqBaseCreditorAccounts.tdCHistGrossCredNoteCC
                                                               else tqBaseCreditorAccounts.tdCHistGrossCredNoteLC
           tqCreditorAccountsHistory.tdCreditorInvoicesRC    = 
           tqCreditorAccountsHistory.tdCreditorInvoicesRC    + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
                                                               then tqBaseCreditorAccounts.tdCHistGrossInvoiceCC
                                                               else tqBaseCreditorAccounts.tdCHistGrossInvoiceLC
           tqCreditorAccountsHistory.tdNetMovementsRC        = 
           tqCreditorAccountsHistory.tdNetMovementsRC        + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
                                                               then (tqBaseCreditorAccounts.tdCHistNettoInvoiceCC - tqBaseCreditorAccounts.tdCHistNettoCredNoteCC)
                                                               else (tqBaseCreditorAccounts.tdCHistNettoInvoiceLC - tqBaseCreditorAccounts.tdCHistNettoCredNoteLC)
           tqCreditorAccountsHistory.tdGrossMovementsRC      = 
           tqCreditorAccountsHistory.tdGrossMovementsRC      + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
                                                               then (tqBaseCreditorAccounts.tdCHistGrossInvoiceCC - tqBaseCreditorAccounts.tdCHistGrossCredNoteCC)
                                                               else (tqBaseCreditorAccounts.tdCHistGrossInvoiceLC - tqBaseCreditorAccounts.tdCHistGrossCredNoteLC) 
           tqCreditorAccountsHistory.tiPeriodPeriod          = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom > 0
                                                             then tqBaseCreditorAccounts.tiCHistYearPeriodFrom mod 100 else 0
           tqCreditorAccountsHistory.tiPeriodYear            = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom  > 0
                                                             then truncate(tqBaseCreditorAccounts.tiCHistYearPeriodFrom / 100,0) else 0
           tqCreditorAccountsHistory.tcSortingByFilter1      = if vcDetailLevelFilter1 = ? or vcDetailLevelFilter1 = '':U
                                                             then {&CREDITORDETAILLEVEL-CREDITOR}
                                                             else vcDetailLevelFilter1.                                                                                                                                                                                                                      
end.

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