report procedure
Parameters
Internal usage
QadFinancials
program code (program7/bcreditorreport.p)
empty temp-table tqCreditorAgeingABackwards.
<M-12 run GetReportLabels
(input 'CreditorAgeingABackwards':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.
/* Validate filter */
assign vcCErrorMessage = "":U.
/* 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 assign viToAccYearFilter1 = -999.
assign viToAccPeriodFilter1 = viToAccYearFilter1 * 100 + viToAccPeriodFilter1.
if viToAccPeriodFilter1 <> ?
then do:
/* Calculate period date from period.year and period.period filterfields */
<Q-11 run PeriodByYearPeriodRange (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input viToAccPeriodFilter1, (PeriodYearPeriodFrom)
input viToAccPeriodFilter1, (PeriodYearPeriodTo)
output dataset tqPeriodByYearPeriodRange) in BCreditorReport >
find first tqPeriodByYearPeriodRange where
tqPeriodByYearPeriodRange.tiPeriodYearPeriod >= viToAccPeriodFilter1 and
tqPeriodByYearPeriodRange.tiPeriodYearPeriod <= viToAccPeriodFilter1
no-error.
if not available tqPeriodByYearPeriodRange
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-31'The GL period does not exist.':50(1474)T-31#) + chr(10).
end.
else assign vcCErrorMessage = vcCErrorMessage + trim(#T-16'You must specify the GL calendar year and GL period.':50(1473)T-16#) + chr(10).
if vtDateForAgeingCalcFilter1 = ?
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-18'You must enter the date for aging calculation.':50(1467)T-18#) + chr(10).
if vcAgeingTypeFilter1 = ?
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-19'You must enter the aging type.':50(1468)T-19#) + chr(10).
if viAgeingOffsetFilter1 = ?
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-20'You must enter the aging offset.':50(1469)T-20#) + chr(10).
if vcAgeingTypeFilter1 = {&AGEINGTYPE-DAYS} and
(viAgeingOffsetFilter1 < 1 or
viAgeingOffsetFilter1 > 60)
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-21'Aging offset is out of range (1-60).':50(1470)T-21#) + chr(10).
if vcAgeingTypeFilter1 = {&AGEINGTYPE-MONTHS} and
(viAgeingOffsetFilter1 < 1 or
viAgeingOffsetFilter1 > 12)
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-22'Aging offset is out of range (1-12).':50(1471)T-22#) + chr(10).
/*
if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC} and
vcCurrencyCodeFilter1 = ?
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-23'You must enter the currency code.':50(65069)T-23#) + chr(10).
*/
if vlCreditorTotalsOnlyFilter1 = true and
vlShowDetailLinesFilterCR = true
then assign vcCErrorMessage = vcCErrorMessage + trim(#T-29'The Supplier Totals Only and Show Detail Lines filter fields cannot both be set to Yes.':100(16245)T-29#) + chr(10).
if vcCErrorMessage <> "":U
then do:
<M-8 run SetMessage
(input trim(vcCErrorMessage, chr(10)) (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-900':U (icFcMsgNumber),
input '':U (icFcExplanation),
input '':U (icFcIdentification),
input '':U (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
assign oiReturnStatus = -1.
return.
end.
if vcReportingCurrencyFilter1 = ?
then assign vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC}.
/* Start gathering data */
<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-38 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-39 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-5 run BaseCreditorAgeingAnalBack (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 tqPeriodByYearPeriodRange.ttPeriodEndDate, (AgeingDate)
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 vcFromPaymentGroupFilter1, (FromPaymentGroup)
input vcToPaymentGroupFilter1, (ToPaymentGroup)
input vcCandoPaymentGroupFilter1, (CandoPaymentGroup)
output dataset tqBaseCreditorAgeingAnalBack) in BCreditorReport>
for each tqBaseCreditorAgeingAnalBack
break by tqBaseCreditorAgeingAnalBack.tiCInvoice_ID:
if first-of(tqBaseCreditorAgeingAnalBack.tiCInvoice_ID)
then do:
assign vlSkip = false.
/* Prepayments and Adjustments: valid only if "Within terms = yes" */
/* if vlInvoicesWithinTermsFilter1 <> true and
(tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT})
then assign vlSkip = true.
else do: */
<Q-34 run CInvoiceStageByInvoice (all) (Read) (NoCache)
(input tqBaseCreditorAgeingAnalBack.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 = tqBaseCreditorAgeingAnalBack.tiCInvoice_ID)
then do:
find first tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalBack.tiCInvoice_ID and
tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate < vtDateForAgeingCalcFilter1 and
tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC > tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC
no-error.
if not available tqCInvoiceStageByInvoice
then assign vlSkip = true.
end.
else
if tqBaseCreditorAgeingAnalBack.ttCInvoiceDueDate >= vtDateForAgeingCalcFilter1
then assign vlSkip = true.
end.
/* end. */
assign vdBalanceCC = 0
vdBalanceLC = 0
vdBalanceTC = 0.
end.
if vlSkip
then next.
assign vdBalanceTC = vdBalanceTC + tqBaseCreditorAgeingAnalBack.tdPostingLineDebitTC - tqBaseCreditorAgeingAnalBack.tdPostingLineCreditTC
vdBalanceLC = vdBalanceLC + tqBaseCreditorAgeingAnalBack.tdPostingLineDebitLC - tqBaseCreditorAgeingAnalBack.tdPostingLineCreditLC
vdBalanceCC = vdBalanceCC + tqBaseCreditorAgeingAnalBack.tdPostingLineDebitCC - tqBaseCreditorAgeingAnalBack.tdPostingLineCreditCC.
if last-of (tqBaseCreditorAgeingAnalBack.tiCInvoice_ID)
then do:
assign vdOriginalCC = tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalDebitCC - tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalCreditCC
vdOriginalLC = tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalDebitLC - tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalCreditLC
vdOriginalTC = tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalDebitTC - tqBaseCreditorAgeingAnalBack.tdCInvoiceOriginalCreditTC.
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.
create tqCreditorAgeingABackwards.
buffer-copy tqBaseCreditorAgeingAnalBack to tqCreditorAgeingABackwards.
assign tqCreditorAgeingABackwards.tcControlGLCode = tqBaseCreditorAgeingAnalBack.tcGLCode
tqCreditorAgeingABackwards.tcControlGLDescription = tqBaseCreditorAgeingAnalBack.tcGLDescription
tqCreditorAgeingABackwards.tdBalanceXC = if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC}
then vdBalanceTC
else vdBalanceXC
tqCreditorAgeingABackwards.tdBalanceTC = vdBalanceTC
tqCreditorAgeingABackwards.tcDiscountType = "Y":U
tqCreditorAgeingABackwards.tdAgeingPeriod1BalanceXC = 0
tqCreditorAgeingABackwards.tdAgeingPeriod2BalanceXC = 0
tqCreditorAgeingABackwards.tdAgeingPeriod3BalanceXC = 0
tqCreditorAgeingABackwards.tdAgeingPeriod4BalanceXC = 0
tqCreditorAgeingABackwards.tdAgeingPeriod5BalanceXC = 0.
/* if tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT}
then assign tqCreditorAgeingABackwards.tdAgeingPeriod1BalanceXC = vdBalanceXC.
else */
if can-find(first tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalBack.tiCInvoice_ID)
then do:
for each tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalBack.tiCInvoice_ID:
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 tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-INVOICE} or
tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-INVOICECORRECTION} or
(tqBaseCreditorAgeingAnalBack.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT} and
vdOriginalXC < 0)
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 case tAgeingPeriod1.tiAgeingPeriodSeq:
when 1
then assign tqCreditorAgeingABackwards.tdAgeingPeriod1BalanceXC = tqCreditorAgeingABackwards.tdAgeingPeriod1BalanceXC + vdXCValue.
when 2
then assign tqCreditorAgeingABackwards.tdAgeingPeriod2BalanceXC = tqCreditorAgeingABackwards.tdAgeingPeriod2BalanceXC + vdXCValue.
when 3
then assign tqCreditorAgeingABackwards.tdAgeingPeriod3BalanceXC = tqCreditorAgeingABackwards.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 tqCreditorAgeingABackwards.tdAgeingPeriod4BalanceXC = tqCreditorAgeingABackwards.tdAgeingPeriod4BalanceXC + vdXCValue.
end case.
end.
end.
else do:
find first tAgeingPeriod1 where
tAgeingPeriod1.ttAgeingPeriodFromDate <= tqCreditorAgeingABackwards.ttCInvoiceDueDate and
tAgeingPeriod1.ttAgeingPeriodToDate >= tqCreditorAgeingABackwards.ttCInvoiceDueDate
no-error.
if available tAgeingPeriod1
then case tAgeingPeriod1.tiAgeingPeriodSeq:
when 1
then assign tqCreditorAgeingABackwards.tdAgeingPeriod1BalanceXC = vdBalanceXC.
when 2
then assign tqCreditorAgeingABackwards.tdAgeingPeriod2BalanceXC = vdBalanceXC.
when 3
then assign tqCreditorAgeingABackwards.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 tqCreditorAgeingABackwards.tdAgeingPeriod4BalanceXC = vdBalanceXC.
end case.
end.
end.
end.
end.
<Q-35 run CInvoiceStageByInvoice (Stop) in BCreditorReport >
/* Remove records that don't match the Supplier Balance filter */
/* Fill some extra info for the others */
for each tqCreditorAgeingABackwards
break by tqCreditorAgeingABackwards.tcCreditorCode:
accumulate tqCreditorAgeingABackwards.tdBalanceXC (total by tqCreditorAgeingABackwards.tcCreditorCode).
assign tqCreditorAgeingABackwards.tcSortByName2 = vcSortByFilter1
tqCreditorAgeingABackwards.tcCreditorAgeingTypeBackwards = vcAgeingTypeFilter1.
if last-of(tqCreditorAgeingABackwards.tcCreditorCode)
then do:
assign vdBalanceXC = accum total by tqCreditorAgeingABackwards.tcCreditorCode tqCreditorAgeingABackwards.tdBalanceXC.
if vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
vdBalanceXC > 0 or
vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT} and
vdBalanceXC < 0
then do:
for each bCreditorAgeingABackwards where
bCreditorAgeingABackwards.tcCreditorCode = tqCreditorAgeingABackwards.tcCreditorCode and
rowid(bCreditorAgeingABackwards) <> rowid(tqCreditorAgeingABackwards):
delete bCreditorAgeingABackwards.
end.
delete tqCreditorAgeingABackwards.
end.
else do:
assign vhFcComponent = ?.
<M-36 run GetAddressWithType
(input tqCreditorAgeingABackwards.tiBusinessRelation_ID (iiBusinessRelationId),
input tqCreditorAgeingABackwards.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-37 run ContactByAddress (all) (Read) (NoCache)
(input tAddressQueryRef1.tiAddress_ID, (AddressId)
output dataset tqContactByAddress) in BBusinessRelation >
find first tqContactByAddress
no-error.
for each bCreditorAgeingABackwards where
bCreditorAgeingABackwards.tcCreditorCode = tqCreditorAgeingABackwards.tcCreditorCode:
assign bCreditorAgeingABackwards.tcAddressCity = tAddressQueryRef1.tcAddressCity
bCreditorAgeingABackwards.tcAddressFax = tAddressQueryRef1.tcAddressFax
bCreditorAgeingABackwards.tcAddressStreet1 = tAddressQueryRef1.tcAddressStreet1
bCreditorAgeingABackwards.tcAddressStreet2 = tAddressQueryRef1.tcAddressStreet2
bCreditorAgeingABackwards.tcAddressStreet3 = tAddressQueryRef1.tcAddressStreet3
bCreditorAgeingABackwards.tcAddressTelephone = tAddressQueryRef1.tcAddressTelephone
bCreditorAgeingABackwards.tcAddressZip = tAddressQueryRef1.tcAddressZip
bCreditorAgeingABackwards.tcAddressCounty = tAddressQueryRef1.tcAddressCounty
bCreditorAgeingABackwards.tcAddressState = tAddressQueryRef1.tcAddressState
bCreditorAgeingABackwards.tiAddressFormat = tAddressQueryRef1.tiAddressFormat
bCreditorAgeingABackwards.tcStateCode = tAddressQueryRef1.tcStateCode
bCreditorAgeingABackwards.tcCountyDescription = tAddressQueryRef1.tcCountyDescription
bCreditorAgeingABackwards.tcCountryDescription = tAddressQueryRef1.tcCountryDescription.
if available tqContactByAddress
then assign bCreditorAgeingABackwards.tcContactEmail = tqContactByAddress.tcContactEmail
bCreditorAgeingABackwards.tcContactName = tqContactByAddress.tcContactName
bCreditorAgeingABackwards.tcContactMobile = tqContactByAddress.tcContactMobilePhone
bCreditorAgeingABackwards.tcContactPhone = tqContactByAddress.tcContactTelephone.
end.
end.
end.
end.
end.