report procedure
Parameters
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.