report procedure
Parameters
Internal usage
unused
program code (program4/bdebtorreport.p)
empty temp-table tqCustomerCreditOverview.
/* get Report Labels */
<M-2 run GetReportLabels
(input 'CustomerCreditOverview':U (icReportName),
input icLanguageCode (icLanguageCode),
input tFilter (tFilter),
output tqHeader (tqHeader),
output tqFilter (tqFilter),
output tqText (tqText),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* return when unable to get label */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/* get Filter parameter value and save as class variables to be used for query */
<M-3 run SetDataItemsBasedOnFilterTT
(output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* handle error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
assign vcSelectedCurrency = vcReportCurrencyFilter.
<Q-37 run CompanyPrim
(Start) in BCompany >
/* Assign value for Entity. */
if vcDRCompanyFilter <> ? then
assign vcSelectedCompanyCode = vcDRCompanyFilter.
else do:
<Q-36 run CompanyPrim (all) (Read) (NoCache)
(input ?, (LookupCompanyId)
input ?, (CompanyCode)
output dataset tqCompanyPrim) in BCompany >
assign vcSelectedCompanyCode = "":U.
for each tqCompanyPrim:
vcSelectedCompanyCode = vcSelectedCompanyCode + tqCompanyPrim.tcCompanyCode + ",".
end.
assign vcSelectedCompanyCode = trim(vcSelectedCompanyCode, ",").
end.
<Q-38 run CompanyPrim
(Stop) in BCompany >
<Q-30 run CustomerCreditOverviewMR (Start) in BDebtorReport >
<Q-31 run CompanyPropertyByCompany (Start) in BCompanyProperty >
<Q-46 run DInvoiceByDebtorByCompIsOpen (Start) in BDebtorReport >
<Q-32 run DInvoiceReminderLevelSortDesc (Start) in BDebtorReport >
/* run query CustomerCreditOverviewMR to get required data */
<Q-39 run CustomerCreditOverviewMR (all) (Read) (NoCache)
(input ?, (CompanyId)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input vcDebtorTypeFilter, (DebtorType)
output dataset tqCustomerCreditOverviewMR) in BDebtorReport >
/* If Report Currency is blank, use the Base Currency. */
if vcSelectedCurrency = ? then do:
<Q-19 run CompanyPropertyByCompany (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (CompanyCode)
input ?, (CurrencyCode)
output dataset tqCompanyPropertyByCompany) in BCompanyProperty >
find first tqCompanyPropertyByCompany no-error.
if available tqCompanyPropertyByCompany
then assign vcSelectedCurrency = tqCompanyPropertyByCompany.tcCurrencyCode.
/*
for each tqCompanyPropertyByCompany:
assign
vcSelectedCurrency = tqCompanyPropertyByCompany.tcCurrencyCode.
leave.
end.
*/
end.
/* create output temp-table and copy the query results to target output temp-table */
for each tqCustomerCreditOverviewMR:
/* Calculate the Open Balance, the High Credit Limit, the Credit Limit and the Highest Reminder Level */
/* the Open Balance */
assign vdOpenBalanceQTY = 0.
<Q-40 run DInvoiceByDebtorByCompIsOpen (all) (Read) (NoCache)
(input ?, (CompanyId)
input true, (DInvoiceIsOpen)
input tqCustomerCreditOverviewMR.tiDebtor_ID, (DebtorId)
input vcSelectedCompanyCode, (CompanyCode)
output dataset tqDInvoiceByDebtorByCompIsOpen) in BDebtorReport >
for each tqDInvoiceByDebtorByCompIsOpen:
assign
vdBalanceTC = tqDInvoiceByDebtorByCompIsOpen.tdDInvoiceBalanceDebitTC - tqDInvoiceByDebtorByCompIsOpen.tdDInvoiceBalanceCreditTC
vdBalanceLC = tqDInvoiceByDebtorByCompIsOpen.tdDInvoiceBalanceDebitLC - tqDInvoiceByDebtorByCompIsOpen.tdDInvoiceBalanceCreditLC.
if tqDInvoiceByDebtorByCompIsOpen.tcCurrencyCode = vcSelectedCurrency
then assign vdOpenBalanceQTY = vdOpenBalanceQTY + vdBalanceTC.
else do:
<Q-41 run CompanyPropertyByCompany (all) (Read) (NoCache)
(input tqDInvoiceByDebtorByCompIsOpen.tiCompany_ID, (CompanyId)
input ?, (CompanyCode)
output dataset tqCompanyPropertyByCompany) in BCompanyProperty >
find first tqCompanyPropertyByCompany no-lock no-error.
if available tqCompanyPropertyByCompany
then do:
if tqCompanyPropertyByCompany.tcCurrencyCode = tqDInvoiceByDebtorByCompIsOpen.tcCurrencyCode
then assign vdOpenBalanceQTY = vdOpenBalanceQTY + vdBalanceLC.
end.
else do:
<M-72 run GetExchangeRate
(input ? (iiCompanyID),
input ? (iiFromCurrencyID),
input tqDInvoiceByDebtorByCompIsOpen.tcCurrencyCode (icFromCurrencyCode),
input ? (iiToCurrencyID),
input vcSelectedCurrency (icToCurrencyCode),
input ? (iiExchangeRateTypeID),
input {&EXCHANGERATETYPE-ACCOUNTING} (icExchangeRateTypeCode),
input today (itValidityDate),
output vdrate (odExchangeRate),
output vdRateScale (odExchangeScaleFactor),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* handle error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
assign vdOpenBalanceQTY = vdOpenBalanceQTY +
<M-45 RoundAmount
(input vdBalanceTC * vdRate * vdRateScale (idUnroundedAmount),
input ? (iiCurrencyID),
input vcSelectedCurrency (icCurrencyCode)) in business>.
end. /* if available tqCompanyPropertyByCompany */
end. /* if tqDInvoiceByDebtorIsOpen.tcCurrencyCode = icCurrencyCode */
end. /* for each tqDInvoiceByDebtorByCompIsOpen */
/* Deal with the filter SuppressZeroAmounts */
if vlSuppressZeroAmountsFilter = true and vdOpenBalanceQTY = 0
then next.
if viBDebtorID = 0 or
viBDebtorID = ?
then do:
<I-90 {bFcStartAndOpenInstance
&ADD-TO-TRANSACTION = "false"
&CLASS = "BDebtor"}>
end.
else do:
<I-62 {bFcOpenInstance
&CLASS = "BDebtor"}>
end.
/* the Credit Limit */
if tqCustomerCreditOverviewMR.tlDebtorIsFixedCredLim = false and tqCustomerCreditOverviewMR.tlDebtorIsTurnOverCredLim = false then do:
vdDebtorCreditLimitTemp = 0.
end.
else if tqCustomerCreditOverviewMR.tlDebtorIsFixedCredLim = true and tqCustomerCreditOverviewMR.tlDebtorIsTurnOverCredLim = false then do:
vdDebtorCreditLimitTemp = tqCustomerCreditOverviewMR.tdDebtorFixedCredLimTC.
end.
else if tqCustomerCreditOverviewMR.tlDebtorIsFixedCredLim = false and tqCustomerCreditOverviewMR.tlDebtorIsTurnOverCredLim = true then do:
<M-28 run CalculateCreditOnTurnover
(input tqCustomerCreditOverviewMR.tiDebtor_ID (iiDebtorId),
input tqCustomerCreditOverviewMR.tcDebtorCode (icDebtorCode),
input vcSelectedCurrency (icCurrencyCode),
input tqCustomerCreditOverviewMR.tdDebtorPercTurnOverCredLim (idPercentageOfTurnover),
output vdDebtorCreditLimitTemp (odCreditOnTurnover),
output viFcReturnSuper (oiReturnStatus)) in BDebtor>
end.
else do:
<M-29 run CalculateCreditOnTurnover
(input tqCustomerCreditOverviewMR.tiDebtor_ID (iiDebtorId),
input tqCustomerCreditOverviewMR.tcDebtorCode (icDebtorCode),
input vcSelectedCurrency (icCurrencyCode),
input tqCustomerCreditOverviewMR.tdDebtorPercTurnOverCredLim (idPercentageOfTurnover),
output vdDebtorCreditLimitTemp (odCreditOnTurnover),
output viFcReturnSuper (oiReturnStatus)) in BDebtor>
if vdDebtorCreditLimitTemp > tqCustomerCreditOverviewMR.tdDebtorFixedCredLimTC then do:
assign vdDebtorCreditLimitTemp = tqCustomerCreditOverviewMR.tdDebtorFixedCredLimTC.
end.
end. /* if tqCustomerCreditOverviewMR.tlDebtorIsFixedCredLim = false and tqCustomerCreditOverviewMR.tlDebtorIsTurnOverCredLim = false */
<I-75 {bFcCloseAndStopInstance
&CLASS = "BDebtor"}>
/* Deal with the filter OverCreditLimitOnly */
if vlOverCreditLimitOnlyFilter = true and vdOpenBalanceQTY <= vdDebtorCreditLimitTemp
then next.
/* the High Credit Limit */
if tqCustomerCreditOverviewMR.tcCurrencyCode <> vcSelectedCurrency
then do:
<M-68 run GetExchangeRate
(input ? (iiCompanyID),
input ? (iiFromCurrencyID),
input tqCustomerCreditOverviewMR.tcCurrencyCode (icFromCurrencyCode),
input ? (iiToCurrencyID),
input vcSelectedCurrency (icToCurrencyCode),
input ? (iiExchangeRateTypeID),
input {&EXCHANGERATETYPE-ACCOUNTING} (icExchangeRateTypeCode),
input today (itValidityDate),
output vdrate (odExchangeRate),
output vdRateScale (odExchangeScaleFactor),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* handle error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
end.
else do:
assign vdRate = 1
vdRateScale = 1.
end. /* if tqCustomerCreditOverviewMR.tcCurrencyCode <> vcSelectedCurrency */
assign vhFcComponent = ?.
assign vdDebtorHighCreditTemp = <M-52 RoundAmount
(input tqCustomerCreditOverviewMR.tdDebtorHighCredit * vdRate * vdRateScale (idUnroundedAmount),
input ? (iiCurrencyID),
input vcSelectedCurrency (icCurrencyCode)) in BDebtorReport>.
/* the Highest Reminder Level */
<Q-22 run DInvoiceReminderLevelSortDesc (all) (Read) (NoCache)
(input ?, (CompanyId)
input tqCustomerCreditOverviewMR.tiDebtor_ID, (DebtorId)
input vcSelectedCompanyCode, (CompanyCode)
output dataset tqDInvoiceReminderLevelSortDesc) in BDebtorReport >
assign viHighestReminderLevelTemp = 0.
/*
for each tqDInvoiceReminderLevelSortDesc:
assign
viHighestReminderLevelTemp = tqDInvoiceReminderLevelSortDesc.tiDInvoiceReminderCount.
leave.
end.
*/
find first tqDInvoiceReminderLevelSortDesc no-error.
if available tqDInvoiceReminderLevelSortDesc
then assign viHighestReminderLevelTemp = tqDInvoiceReminderLevelSortDesc.tiDInvoiceReminderCount.
create tqCustomerCreditOverview.
assign
tqCustomerCreditOverview.tc_Rowid = tqCustomerCreditOverviewMR.tc_rowid
tqCustomerCreditOverview.tcBusinessRelationName1 = tqCustomerCreditOverviewMR.tcBusinessRelationName1
tqCustomerCreditOverview.tcCurrencyCode = tqCustomerCreditOverviewMR.tcCurrencyCode
tqCustomerCreditOverview.tcDebtorCode = tqCustomerCreditOverviewMR.tcDebtorCode
tqCustomerCreditOverview.tcPaymentConditionCode = tqCustomerCreditOverviewMR.tcPaymentConditionCode
tqCustomerCreditOverview.tdDebtorHighCredit = tqCustomerCreditOverviewMR.tdDebtorHighCredit
tqCustomerCreditOverview.ti_Sequence = tqCustomerCreditOverviewMR.ti_sequence
tqCustomerCreditOverview.tiDebtor_ID = tqCustomerCreditOverviewMR.tiDebtor_ID
tqCustomerCreditOverview.tlDebtorIsFinanceCharge = tqCustomerCreditOverviewMR.tlDebtorIsFinanceCharge
tqCustomerCreditOverview.tlDebtorIsLockedCredLim = tqCustomerCreditOverviewMR.tlDebtorIsLockedCredLim
tqCustomerCreditOverview.ttDebtorHighCreditDate = tqCustomerCreditOverviewMR.ttDebtorHighCreditDate
tqCustomerCreditOverview.ttDebtorLastPayment = tqCustomerCreditOverviewMR.ttDebtorLastPayment
tqCustomerCreditOverview.ttDebtorLastSale = tqCustomerCreditOverviewMR.ttDebtorLastSale
tqCustomerCreditOverview.tiHighestReminderLevel = viHighestReminderLevelTemp
tqCustomerCreditOverview.tdOpenBalance = vdOpenBalanceQTY
tqCustomerCreditOverview.tdDebtorHighCredit = vdDebtorHighCreditTemp
tqCustomerCreditOverview.tdCreditLimit = vdDebtorCreditLimitTemp.
/*
/* Deal with the filters OverCreditLimitOnly and SuppressZeroAmounts */
if vlOverCreditLimitOnlyFilter = true then do:
if vdOpenBalanceQTY > vdDebtorCreditLimitTemp then do:
if vlSuppressZeroAmountsFilter = true then do:
if vdOpenBalanceQTY <> 0 then do:
create tqCustomerCreditOverview.
assign
tqCustomerCreditOverview.tc_Rowid = tqCustomerCreditOverviewMR.tc_rowid
tqCustomerCreditOverview.tcBusinessRelationName1 = tqCustomerCreditOverviewMR.tcBusinessRelationName1
tqCustomerCreditOverview.tcCurrencyCode = tqCustomerCreditOverviewMR.tcCurrencyCode
tqCustomerCreditOverview.tcDebtorCode = tqCustomerCreditOverviewMR.tcDebtorCode
tqCustomerCreditOverview.tcPaymentConditionCode = tqCustomerCreditOverviewMR.tcPaymentConditionCode
tqCustomerCreditOverview.tdDebtorHighCredit = tqCustomerCreditOverviewMR.tdDebtorHighCredit
tqCustomerCreditOverview.ti_Sequence = tqCustomerCreditOverviewMR.ti_sequence
tqCustomerCreditOverview.tiDebtor_ID = tqCustomerCreditOverviewMR.tiDebtor_ID
tqCustomerCreditOverview.tlDebtorIsFinanceCharge = tqCustomerCreditOverviewMR.tlDebtorIsFinanceCharge
tqCustomerCreditOverview.tlDebtorIsLockedCredLim = tqCustomerCreditOverviewMR.tlDebtorIsLockedCredLim
tqCustomerCreditOverview.ttDebtorHighCreditDate = tqCustomerCreditOverviewMR.ttDebtorHighCreditDate
tqCustomerCreditOverview.ttDebtorLastPayment = tqCustomerCreditOverviewMR.ttDebtorLastPayment
tqCustomerCreditOverview.ttDebtorLastSale = tqCustomerCreditOverviewMR.ttDebtorLastSale
tqCustomerCreditOverview.tiHighestReminderLevel = viHighestReminderLevelTemp
tqCustomerCreditOverview.tdOpenBalance = vdOpenBalanceQTY
tqCustomerCreditOverview.tdDebtorHighCredit = vdDebtorHighCreditTemp
tqCustomerCreditOverview.tdCreditLimit = vdDebtorCreditLimitTemp.
end. /* if vdOpenBalanceQTY <> 0 */
end.
else do:
create tqCustomerCreditOverview.
assign
tqCustomerCreditOverview.tc_Rowid = tqCustomerCreditOverviewMR.tc_rowid
tqCustomerCreditOverview.tcBusinessRelationName1 = tqCustomerCreditOverviewMR.tcBusinessRelationName1
tqCustomerCreditOverview.tcCurrencyCode = tqCustomerCreditOverviewMR.tcCurrencyCode
tqCustomerCreditOverview.tcDebtorCode = tqCustomerCreditOverviewMR.tcDebtorCode
tqCustomerCreditOverview.tcPaymentConditionCode = tqCustomerCreditOverviewMR.tcPaymentConditionCode
tqCustomerCreditOverview.tdDebtorHighCredit = tqCustomerCreditOverviewMR.tdDebtorHighCredit
tqCustomerCreditOverview.ti_Sequence = tqCustomerCreditOverviewMR.ti_sequence
tqCustomerCreditOverview.tiDebtor_ID = tqCustomerCreditOverviewMR.tiDebtor_ID
tqCustomerCreditOverview.tlDebtorIsFinanceCharge = tqCustomerCreditOverviewMR.tlDebtorIsFinanceCharge
tqCustomerCreditOverview.tlDebtorIsLockedCredLim = tqCustomerCreditOverviewMR.tlDebtorIsLockedCredLim
tqCustomerCreditOverview.ttDebtorHighCreditDate = tqCustomerCreditOverviewMR.ttDebtorHighCreditDate
tqCustomerCreditOverview.ttDebtorLastPayment = tqCustomerCreditOverviewMR.ttDebtorLastPayment
tqCustomerCreditOverview.ttDebtorLastSale = tqCustomerCreditOverviewMR.ttDebtorLastSale
tqCustomerCreditOverview.tiHighestReminderLevel = viHighestReminderLevelTemp
tqCustomerCreditOverview.tdOpenBalance = vdOpenBalanceQTY
tqCustomerCreditOverview.tdDebtorHighCredit = vdDebtorHighCreditTemp
tqCustomerCreditOverview.tdCreditLimit = vdDebtorCreditLimitTemp.
end. /* vlSuppressZeroAmountsFilter = true */
end. /* if vdOpenBalanceQTY > vdDebtorCreditLimitTemp */
end.
else do:
if vlSuppressZeroAmountsFilter = true then do:
if vdOpenBalanceQTY <> 0 then do:
create tqCustomerCreditOverview.
assign
tqCustomerCreditOverview.tc_Rowid = tqCustomerCreditOverviewMR.tc_rowid
tqCustomerCreditOverview.tcBusinessRelationName1 = tqCustomerCreditOverviewMR.tcBusinessRelationName1
tqCustomerCreditOverview.tcCurrencyCode = tqCustomerCreditOverviewMR.tcCurrencyCode
tqCustomerCreditOverview.tcDebtorCode = tqCustomerCreditOverviewMR.tcDebtorCode
tqCustomerCreditOverview.tcPaymentConditionCode = tqCustomerCreditOverviewMR.tcPaymentConditionCode
tqCustomerCreditOverview.tdDebtorHighCredit = tqCustomerCreditOverviewMR.tdDebtorHighCredit
tqCustomerCreditOverview.ti_Sequence = tqCustomerCreditOverviewMR.ti_sequence
tqCustomerCreditOverview.tiDebtor_ID = tqCustomerCreditOverviewMR.tiDebtor_ID
tqCustomerCreditOverview.tlDebtorIsFinanceCharge = tqCustomerCreditOverviewMR.tlDebtorIsFinanceCharge
tqCustomerCreditOverview.tlDebtorIsLockedCredLim = tqCustomerCreditOverviewMR.tlDebtorIsLockedCredLim
tqCustomerCreditOverview.ttDebtorHighCreditDate = tqCustomerCreditOverviewMR.ttDebtorHighCreditDate
tqCustomerCreditOverview.ttDebtorLastPayment = tqCustomerCreditOverviewMR.ttDebtorLastPayment
tqCustomerCreditOverview.ttDebtorLastSale = tqCustomerCreditOverviewMR.ttDebtorLastSale
tqCustomerCreditOverview.tiHighestReminderLevel = viHighestReminderLevelTemp
tqCustomerCreditOverview.tdOpenBalance = vdOpenBalanceQTY
tqCustomerCreditOverview.tdDebtorHighCredit = vdDebtorHighCreditTemp
tqCustomerCreditOverview.tdCreditLimit = vdDebtorCreditLimitTemp.
end. /* if vdOpenBalanceQTY <> 0 */
end.
else do:
create tqCustomerCreditOverview.
assign
tqCustomerCreditOverview.tc_Rowid = tqCustomerCreditOverviewMR.tc_rowid
tqCustomerCreditOverview.tcBusinessRelationName1 = tqCustomerCreditOverviewMR.tcBusinessRelationName1
tqCustomerCreditOverview.tcCurrencyCode = tqCustomerCreditOverviewMR.tcCurrencyCode
tqCustomerCreditOverview.tcDebtorCode = tqCustomerCreditOverviewMR.tcDebtorCode
tqCustomerCreditOverview.tcPaymentConditionCode = tqCustomerCreditOverviewMR.tcPaymentConditionCode
tqCustomerCreditOverview.tdDebtorHighCredit = tqCustomerCreditOverviewMR.tdDebtorHighCredit
tqCustomerCreditOverview.ti_Sequence = tqCustomerCreditOverviewMR.ti_sequence
tqCustomerCreditOverview.tiDebtor_ID = tqCustomerCreditOverviewMR.tiDebtor_ID
tqCustomerCreditOverview.tlDebtorIsFinanceCharge = tqCustomerCreditOverviewMR.tlDebtorIsFinanceCharge
tqCustomerCreditOverview.tlDebtorIsLockedCredLim = tqCustomerCreditOverviewMR.tlDebtorIsLockedCredLim
tqCustomerCreditOverview.ttDebtorHighCreditDate = tqCustomerCreditOverviewMR.ttDebtorHighCreditDate
tqCustomerCreditOverview.ttDebtorLastPayment = tqCustomerCreditOverviewMR.ttDebtorLastPayment
tqCustomerCreditOverview.ttDebtorLastSale = tqCustomerCreditOverviewMR.ttDebtorLastSale
tqCustomerCreditOverview.tiHighestReminderLevel = viHighestReminderLevelTemp
tqCustomerCreditOverview.tdOpenBalance = vdOpenBalanceQTY
tqCustomerCreditOverview.tdDebtorHighCredit = vdDebtorHighCreditTemp
tqCustomerCreditOverview.tdCreditLimit = vdDebtorCreditLimitTemp.
end. /* vlSuppressZeroAmountsFilter = true */
end. /* if vlOverCreditLimitOnlyFilter = true */
*/
end. /* for each tqCustomerCreditOverviewMR */
<Q-47 run CustomerCreditOverviewMR (Stop) in BDebtorReport >
<Q-48 run CompanyPropertyByCompany (Stop) in BCompanyProperty >
<Q-49 run DInvoiceByDebtorByCompIsOpen (Stop) in BDebtorReport >
<Q-50 run DInvoiceReminderLevelSortDesc (Stop) in BDebtorReport >