report procedure
Parameters
Internal usage
QadFinancials
program code (program7/bcreditorreport.p)
empty temp-table tqCreditorAgeingAnalysisCurrent.
<M-20 run GetReportLabels
(input 'CreditorAgeingAnalysisCurrent':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.
<M-26 run ValidateAgeingReport
(output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
if viFcReturnSuper < 0 or
oiReturnStatus = 0
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0
then return.
/* 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.
assign viToAccPeriodFilter1 = viToAccYearFilter1 * 100 + viToAccPeriodFilter1.
if vcReportingCurrencyFilter1 = ?
then assign vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC}.
<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-29 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-40 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-3 run BaseCreditorAgeingAnalysisCurr (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 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 vcToPaymentGroupFilter1, (ToPaymentGroup)
input vcFromPaymentGroupFilter1, (FromPaymentGroup)
input vcCandoPaymentGroupFilter1, (CandoPaymentGroup)
output dataset tqBaseCreditorAgeingAnalysisCurr) in BCreditorReport>
/* Do some extra filtering on the result set of the base query that can't be written in the query itself */
for each tqBaseCreditorAgeingAnalysisCurr:
/* Prepayments and Adjustments: valid only if "Within terms = yes" */
/* if vlInvoicesWithinTermsFilter1 <> true and
(tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT})
then next. */
<Q-37 run CInvoiceStageByInvoice (all) (Read) (NoCache)
(input tqBaseCreditorAgeingAnalysisCurr.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 = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID)
then do:
find first tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID and
tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate < vtDateForAgeingCalcFilter1 and
tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC > tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC
no-error.
if not available tqCInvoiceStageByInvoice
then next.
end.
else
if tqBaseCreditorAgeingAnalysisCurr.ttCInvoiceDueDate >= vtDateForAgeingCalcFilter1
then next.
end.
/* Create a report record */
create tqCreditorAgeingAnalysisCurrent.
buffer-copy tqBaseCreditorAgeingAnalysisCurr to tqCreditorAgeingAnalysisCurrent.
assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = 0
tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = 0
tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC = 0
tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = 0
tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod5BalanceXC = 0
vdBalanceCC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitCC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditCC
vdBalanceLC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitLC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditLC
vdBalanceTC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceDebitTC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceBalanceCreditTC
vdOriginalCC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitCC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditCC
vdOriginalLC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitLC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditLC
vdOriginalTC = tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalDebitTC - tqBaseCreditorAgeingAnalysisCurr.tdCInvoiceOriginalCreditTC.
if tqBaseCreditorAgeingAnalysisCurr.tcPaymentConditionPdDiscType <> "" and
tqBaseCreditorAgeingAnalysisCurr.tdPaymentConditionPercentage <> 0 and
tqBaseCreditorAgeingAnalysisCurr.tiPaymentConditionDayMthDisc <> 0
then assign tqCreditorAgeingAnalysisCurrent.tlDiscountPaymentCondApl = true.
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.
assign tqCreditorAgeingAnalysisCurrent.tdBalanceXC =
if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC}
then vdBalanceTC
else vdBalanceXC
tqCreditorAgeingAnalysisCurrent.tdBalanceTC = vdBalanceTC.
/* if tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-PREPAYMENT} or
tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-ADJUSTMENT}
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = vdBalanceXC.
else */
if can-find(first tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID)
then do:
for each tqCInvoiceStageByInvoice where
tqCInvoiceStageByInvoice.tiCInvoice_ID = tqBaseCreditorAgeingAnalysisCurr.tiCInvoice_ID and
tqCInvoiceStageByInvoice.tdCInvoiceStageAmountTC > tqCInvoiceStageByInvoice.tdCInvoiceStageAmtAppliedTC:
if vlInvoicesWithinTermsFilter1 = false and
tqCInvoiceStageByInvoice.ttCInvoiceStageDueDate >= vtDateForAgeingCalcFilter1
then next.
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 tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-INVOICE} or
tqBaseCreditorAgeingAnalysisCurr.tcCInvoiceType = {&INVOICETYPE-INVOICECORRECTION}
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 do:
case tAgeingPeriod1.tiAgeingPeriodSeq:
when 1
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC + vdXCValue.
when 2
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC + vdXCValue.
when 3
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod3BalanceXC = tqCreditorAgeingAnalysisCurrent.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 tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC + vdXCValue.
end case.
end.
end.
end.
else do:
find first tAgeingPeriod1 where
tAgeingPeriod1.ttAgeingPeriodFromDate <= tqCreditorAgeingAnalysisCurrent.ttCInvoiceDueDate and
tAgeingPeriod1.ttAgeingPeriodToDate >= tqCreditorAgeingAnalysisCurrent.ttCInvoiceDueDate
no-error.
if available tAgeingPeriod1
then do:
case tAgeingPeriod1.tiAgeingPeriodSeq:
when 1
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod1BalanceXC = vdBalanceXC.
when 2
then assign tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod2BalanceXC = vdBalanceXC.
when 3
then assign tqCreditorAgeingAnalysisCurrent.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 tqCreditorAgeingAnalysisCurrent.tdAgeingPeriod4BalanceXC = vdBalanceXC.
end case.
end.
end.
end.
end.
<Q-34 run CInvoiceStageByInvoice (Stop) in BCreditorReport >
/* Remove records that don't match the Supplier Balance filter */
/* Fill some extra info for the others */
for each tqCreditorAgeingAnalysisCurrent
break by tqCreditorAgeingAnalysisCurrent.tcCreditorCode:
accumulate tqCreditorAgeingAnalysisCurrent.tdBalanceXC (total by tqCreditorAgeingAnalysisCurrent.tcCreditorCode).
assign tqCreditorAgeingAnalysisCurrent.tcSortByName3 = vcSortByFilter1
tqCreditorAgeingAnalysisCurrent.tcCreditorAgeingTypeCurrent = vcAgeingTypeFilter1.
if last-of(tqCreditorAgeingAnalysisCurrent.tcCreditorCode)
then do:
assign vdBalanceXC = accum total by tqCreditorAgeingAnalysisCurrent.tcCreditorCode tqCreditorAgeingAnalysisCurrent.tdBalanceXC.
if vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
vdBalanceXC > 0 or
vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT} and
vdBalanceXC < 0
then do:
for each bCreditorAgeingAnalysisCurrent where
bCreditorAgeingAnalysisCurrent.tcCreditorCode = tqCreditorAgeingAnalysisCurrent.tcCreditorCode and
rowid(bCreditorAgeingAnalysisCurrent) <> rowid(tqCreditorAgeingAnalysisCurrent):
delete bCreditorAgeingAnalysisCurrent.
end.
delete tqCreditorAgeingAnalysisCurrent.
end.
else do:
assign vhFcComponent = ?.
<M-38 run GetAddressWithType
(input tqCreditorAgeingAnalysisCurrent.tiBusinessRelation_ID (iiBusinessRelationId),
input tqCreditorAgeingAnalysisCurrent.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-39 run ContactByAddress (all) (Read) (NoCache)
(input tAddressQueryRef1.tiAddress_ID, (AddressId)
output dataset tqContactByAddress) in BBusinessRelation >
find first tqContactByAddress
no-error.
for each bCreditorAgeingAnalysisCurrent where
bCreditorAgeingAnalysisCurrent.tcCreditorCode = tqCreditorAgeingAnalysisCurrent.tcCreditorCode:
assign bCreditorAgeingAnalysisCurrent.tcAddressCity = tAddressQueryRef1.tcAddressCity
bCreditorAgeingAnalysisCurrent.tcAddressFax = tAddressQueryRef1.tcAddressFax
bCreditorAgeingAnalysisCurrent.tcAddressStreet1 = tAddressQueryRef1.tcAddressStreet1
bCreditorAgeingAnalysisCurrent.tcAddressStreet2 = tAddressQueryRef1.tcAddressStreet2
bCreditorAgeingAnalysisCurrent.tcAddressStreet3 = tAddressQueryRef1.tcAddressStreet3
bCreditorAgeingAnalysisCurrent.tcAddressTelephone = tAddressQueryRef1.tcAddressTelephone
bCreditorAgeingAnalysisCurrent.tcAddressZip = tAddressQueryRef1.tcAddressZip
bCreditorAgeingAnalysisCurrent.tcAddressCounty = tAddressQueryRef1.tcAddressCounty
bCreditorAgeingAnalysisCurrent.tcAddressState = tAddressQueryRef1.tcAddressState
bCreditorAgeingAnalysisCurrent.tiAddressFormat = tAddressQueryRef1.tiAddressFormat
bCreditorAgeingAnalysisCurrent.tcStateCode = tAddressQueryRef1.tcStateCode
bCreditorAgeingAnalysisCurrent.tcCountyDescription = tAddressQueryRef1.tcCountyDescription
bCreditorAgeingAnalysisCurrent.tcCountryDescription = tAddressQueryRef1.tcCountryDescription.
if available tqContactByAddress
then assign bCreditorAgeingAnalysisCurrent.tcContactEmail = tqContactByAddress.tcContactEmail
bCreditorAgeingAnalysisCurrent.tcContactName = tqContactByAddress.tcContactName
bCreditorAgeingAnalysisCurrent.tcContactMobile = tqContactByAddress.tcContactMobilePhone
bCreditorAgeingAnalysisCurrent.tcContactPhone = tqContactByAddress.tcContactTelephone.
end.
end.
end.
end.
end.