report procedure
Description
This method lists all account movements within selected period and within selected debtors.
Parameters
Internal usage
unused
program code (program7/bdebtorreport.p)
empty temp-table tqDebtorAccountMovements.
empty temp-table tqCustomerInfo.
<M-10 run GetReportLabels (input 'DebtorAccountMovements':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-1 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-11 assign vlFcQueryRecordsAvailable = QDInvoiceMovementAll (NoCache)
(input viCompanyId, (CompanyId)
input ?, (DaemonId)) in BQDInvoiceMovement >
if vlFcQueryRecordsAvailable = true or
vlFcQueryRecordsAvailable = ?
then do:
<M-16 run SetMessage
(input #T-16'Run the Balance daemon to update customer balances.':70(65165)T-16# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3742':U (icFcMsgNumber),
input #T-17'Transactions exist in the Balance daemon queue. The data will not be current until the requests are processed.':150(20387)T-17# (icFcExplanation),
input '':U (icFcIdentification),
input '':U (icFcContext),
output oiReturnStatus (oiReturnStatus)) in BDebtorReport>
assign oiReturnStatus = -1.
return.
end.
end.
/* CA784858 - From - To Period need to be specified */
if viFromAccYearFilter = ? or viFromAccYearFilter = 0 or
viToAccYearFilter = ? or viToAccYearFilter = 0 or
viFromAccPeriodFilter = ? or viToAccPeriodFilter = ?
then do:
<M-22 run SetMessage
(input #T-28' You must complete the [from-to] Period filter.':60(61984)T-28# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-6262':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
assign oiReturnStatus = -1.
return.
end.
/* =============================================== */
/* Set some variables */
/* If no sorting selected -> chronological is used */
/* =============================================== */
if vcChronologicalLogicalFilter = ?
then assign vcChronologicalLogicalFilter = {&DEBTORSORT-CHRONOLOGICAL}.
if vcSummaryByFilter = '' or vcSummaryByFilter = ?
then assign vcSummaryByFilter = 'NONE'.
assign vcCr = {&POSTINGTYPE-CREDIT-TR}
vcDt = {&POSTINGTYPE-DEBIT-TR}.
/* ==================================================== */
/* 1 - Get the OpeningBalance at FromYearPeriod - 1 */
/* ==================================================== */
<Q-3 run DebtorBalanceDHist (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcCandoBRCodeFilter, (CandoBRCode)
input vcCandoDebtorCodeFilter, (CandoDebtorCode)
input vcCandoDContrGLCodeFilter, (CandoGLCode)
input viFromYearPeriodFilter - 1, (EndOfYearPeriod)
input vcFromBRCodeFilter, (FromBRCode)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcFromDContrGLCodeFilter, (FromGLCode)
input vcToBRCodeFilter, (ToBRCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input vcToDContrGLCodeFilter, (ToGLCode)
output dataset tqDebtorBalanceDHist) in BDebtorReport >
/* Get the OpeningBalance by Customer Currency GL DayBook */
for each tqDebtorBalanceDHist
break by tqDebtorBalanceDHist.tcDebtorCode
by tqDebtorBalanceDHist.tcCurrencyCode
by tqDebtorBalanceDHist.tcGLCode
by tqDebtorBalanceDHist.tcJournalCode:
if first-of (tqDebtorBalanceDHist.tcJournalCode)
then assign vdOpeningBalanceTC = 0
vdOpeningBalanceLC = 0
vdOpeningBalanceCC = 0.
assign vdOpeningBalanceCC = vdOpeningBalanceCC + tqDebtorBalanceDHist.tdDHistBalanceCC
vdOpeningBalanceLC = vdOpeningBalanceLC + tqDebtorBalanceDHist.tdDHistBalanceLC
vdOpeningBalanceTC = vdOpeningBalanceTC + tqDebtorBalanceDHist.tdDHistBalanceTC.
if last-of (tqDebtorBalanceDHist.tcJournalCode)
then do :
/* One section per Customer, holding Customer info + Opening balance */
create tqDebtorAccountMovements.
assign tqDebtorAccountMovements.tcDebtorCode = tqDebtorBalanceDHist.tcDebtorCode
tqDebtorAccountMovements.tiDebtor_ID = tqDebtorBalanceDHist.tiDebtor_ID
tqDebtorAccountMovements.tcCurrencyCode = tqDebtorBalanceDHist.tcCurrencyCode
tqDebtorAccountMovements.tcJournalCode = tqDebtorBalanceDHist.tcJournalCode
tqDebtorAccountMovements.tcJournalDescription = tqDebtorBalanceDHist.tcJournalDescription
tqDebtorAccountMovements.tcJournalTypeCode = tqDebtorBalanceDHist.tcJournalTypeCode
tqDebtorAccountMovements.tcGLCode = tqDebtorBalanceDHist.tcGLCode
tqDebtorAccountMovements.tcGLDescription = tqDebtorBalanceDHist.TCGLDESCRIPTION
tqDebtorAccountMovements.tcGLType = tqDebtorBalanceDHist.tcGLTypeCode
tqDebtorAccountMovements.tiCurrencyNumberOfDecimals = tqDebtorBalanceDHist.tiCurrencyNumberOfDecimals
tqDebtorAccountMovements.tdOpeningBalanceTC = vdOpeningBalanceTC
tqDebtorAccountMovements.tdOpeningBalanceLC = vdOpeningBalanceLC
tqDebtorAccountMovements.tdOpeningBalanceCC = vdOpeningBalanceCC
tqDebtorAccountMovements.tcSortKey = ?
tqDebtorAccountMovements.tcActivityKey = ?
tqDebtorAccountMovements.tcInvoiceKey = ?
tqDebtorAccountMovements.tcIsInitial = ?
tqDebtorAccountMovements.tlInitialWithinPeriod = ?
tqDebtorAccountMovements.tcSummarizationInfo = vcSummaryByFilter
tqDebtorAccountMovements.tcReportingInfo = vcChronologicalLogicalFilter
tqDebtorAccountMovements.tlDebtorPerPageInfo = vlDebtorPerPageFilter
tqDebtorAccountMovements.tcReportingCurrency = vcReportingCurrencyFilter.
end. /* last-of ((tqDebtorBalanceDHist.tcDebtorCode) */
end. /* for each tqDebtorBalanceDHist */
/* ===================================================== */
/* 2 - Get all the movements between the selected period */
/* ===================================================== */
<Q-83 run DebtorMovements (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcCandoBRCodeFilter, (CandoBRCode)
input vcCandoDebtorCodeFilter, (CandoDebtorCode)
input vcCandoDContrGLCodeFilter, (CandoGLCode)
input vcFromBRCodeFilter, (FromBRCode)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcFromDContrGLCodeFilter, (FromGLCode)
input viFromYearPeriodFilter, (FromYearPeriod)
input vcToBRCodeFilter, (ToBRCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input vcToDContrGLCodeFilter, (ToGLCode)
input viToYearPeriodFilter, (ToYearPeriod)
output dataset tqDebtorMovements) in BDebtorReport >
/* Go through all the activities now */
for each tqDebtorMovements
break by tqDebtorMovements.tiDInvoice_ID
by tqDebtorMovements.tcDInvoiceMovementType :
if first-of (tqDebtorMovements.tcDInvoiceMovementType)
then assign vlisInitInPeriod = (tqDebtorMovements.tcDInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}).
create tqDebtorAccountMovements.
assign tqDebtorAccountMovements.tcDebtorCode = tqDebtorMovements.tcDebtorCode
tqDebtorAccountMovements.tiDebtor_ID = tqDebtorMovements.tiDebtor_ID
tqDebtorAccountMovements.tcCurrencyCode = tqDebtorMovements.tcCurrencyCode
tqDebtorAccountMovements.tcJournalCode = tqDebtorMovements.tcJournalCode
tqDebtorAccountMovements.tcJournalDescription = tqDebtorMovements.tcJournalDescription
tqDebtorAccountMovements.tcJournalTypeCode = tqDebtorMovements.tcJournalTypeCode
tqDebtorAccountMovements.tcGLDescription = tqDebtorMovements.tcGLDescription
tqDebtorAccountMovements.tcGLCode = tqDebtorMovements.tcGLCode
tqDebtorAccountMovements.tcGLType = tqDebtorMovements.tcGLTypeCode
tqDebtorAccountMovements.ttDInvoiceDate = tqDebtorMovements.ttDInvoiceDate
tqDebtorAccountMovements.ttDInvoiceDiscountDueDate = tqDebtorMovements.ttDInvoiceDiscountDueDate
tqDebtorAccountMovements.ttDInvoiceDueDate = tqDebtorMovements.ttDInvoiceDueDate
tqDebtorAccountMovements.tiCurrencyNumberOfDecimals = tqDebtorMovements.tiCurrencyNumberOfDecimals
tqDebtorAccountMovements.tlInitialWithinPeriod = vlisInitInPeriod
tqDebtorAccountMovements.tiPeriodYear = tqDebtorMovements.tiPostingYear
tqDebtorAccountMovements.tiPeriodPeriod = tqDebtorMovements.tiPostingPeriod
tqDebtorAccountMovements.ttPostingDate = tqDebtorMovements.ttPostingDate
tqDebtorAccountMovements.tiPostingLineSequence = tqDebtorMovements.tiPostingLineSequence
tqDebtorAccountMovements.tcPostingLineText = tqDebtorMovements.tcPostingLineText
tqDebtorAccountMovements.tcPostingText = tqDebtorMovements.tcPostingText
tqDebtorAccountMovements.tiPostingVoucher = tqDebtorMovements.tiPostingVoucher
tqDebtorAccountMovements.tcDInvoiceDIText = tqDebtorMovements.tcDInvoiceDIText
tqDebtorAccountMovements.tdAmountCC = tqDebtorMovements.tdPostingLineDebitCC - tqDebtorMovements.tdPostingLineCreditCC
tqDebtorAccountMovements.tdAmountLC = tqDebtorMovements.tdPostingLineDebitLC - tqDebtorMovements.tdPostingLineCreditLC
tqDebtorAccountMovements.tdAmountTC = tqDebtorMovements.tdPostingLineDebitTC - tqDebtorMovements.tdPostingLineCreditTC
tqDebtorAccountMovements.tcActivityKey = string (tqDebtorMovements.tiPostingYear) + "/" +
tqDebtorMovements.tcJournalCode + "/" +
string (tqDebtorMovements.tiPostingVoucher, "999999999")
tqDebtorAccountMovements.tcInvoiceKey = string (tqDebtorMovements.tiDInvoicePostingYear) + "/" +
tqDebtorMovements.tcInvoiceJournalCode + "/" +
string (tqDebtorMovements.tiDInvoiceVoucher, "999999999")
tqDebtorAccountMovements.tcPostingInvoiceReferenceText = tqDebtorAccountMovements.tcInvoiceKey + ' ' +
tqDebtorMovements.tcDInvoiceDescription
tqDebtorAccountMovements.tcIsInitial = if tqDebtorMovements.tcDInvoiceMovementType = {&MOVEMENTTYPE-INITIAL}
then '*'
else ''
tqDebtorAccountMovements.tcSummarizationInfo = vcSummaryByFilter
tqDebtorAccountMovements.tcReportingInfo = vcChronologicalLogicalFilter
tqDebtorAccountMovements.tlDebtorPerPageInfo = vlDebtorPerPageFilter
tqDebtorAccountMovements.tcReportingCurrency = vcReportingCurrencyFilter
tqDebtorAccountMovements.tcShipper = tqDebtorMovements.tcPostingLineLegalDocNbr.
if vcChronologicalLogicalFilter = {&DEBTORSORT-LOGICAL}
then
/* sort by InvoicePostingDate - Invoice - Activity */
assign tqDebtorAccountMovements.tcSortKey =
string (year(tqDebtorMovements.ttDInvoicePostingDate),"9999") +
string (month(tqDebtorMovements.ttDInvoicePostingDate),"99") +
string (day(tqDebtorMovements.ttDInvoicePostingDate),"99") +
string (tqDebtorMovements.tiDInvoicePostingYear) +
tqDebtorMovements.tcInvoiceJournalCode +
string (tqDebtorMovements.tiDInvoiceVoucher) +
string (year(tqDebtorMovements.ttPostingDate),"9999") +
string (month(tqDebtorMovements.ttPostingDate),"99") +
string (day(tqDebtorMovements.ttPostingDate),"99") +
string (tqDebtorMovements.tiPostingYear) +
tqDebtorMovements.tcJournalCode +
string (tqDebtorMovements.tiPostingVoucher).
else
/* sort by ActivityPostingDate - Activity */
assign tqDebtorAccountMovements.tcSortKey =
string (year(tqDebtorMovements.ttPostingDate),"9999") +
string (month(tqDebtorMovements.ttPostingDate),"99") +
string (day(tqDebtorMovements.ttPostingDate),"99") +
string (tqDebtorMovements.tiPostingYear) +
tqDebtorMovements.tcJournalCode +
string (tqDebtorMovements.tiPostingVoucher).
end. /* for each tqDebtorMovements */
/* ===================================================== */
/* 3 - Get the Customer Address Contact Data */
/* ===================================================== */
empty temp-table tqDebtorAddressContactInfo.
<Q-84 run AddressTypeByCode (all) (Read) (NoCache)
(input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
output dataset tqAddressTypeByCode) in BAddressType>
find first tqAddressTypeByCode where
tqAddressTypeByCode.tcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}
no-error.
if available tqAddressTypeByCode
then do:
<Q-74 run DebtorAddressContactInfo (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input {&ADDRESSTYPECODESYSTEM-HEADOFFICE}, (AddressTypeCode)
input vcCandoBRCodeFilter, (CandoBRCode)
input vcCandoDebtorCodeFilter, (CandoDebtorCode)
input vcFromBRCodeFilter, (FromBRCode)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcToBRCodeFilter, (ToBRCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input tqAddressTypeByCode.tiAddressType_ID, (AddressType_ID)
output dataset tqDebtorAddressContactInfo) in BDebtorReport>
end.
for each tqDebtorAccountMovements
break by tqDebtorAccountMovements.tiDebtor_ID :
if first-of(tqDebtorAccountMovements.tiDebtor_ID)
then do:
/* First look for an address and a contact */
find first tqDebtorAddressContactInfo where
tqDebtorAddressContactInfo.tiDebtor_ID = tqDebtorAccountMovements.tiDebtor_ID and
tqDebtorAddressContactInfo.tcContactName > ''
no-error.
if not available tqDebtorAddressContactInfo
then do :
/* First look for an address only */
find first tqDebtorAddressContactInfo where
tqDebtorAddressContactInfo.tiDebtor_ID = tqDebtorAccountMovements.tiDebtor_ID
no-error.
end.
if available tqDebtorAddressContactInfo
then do :
create tqCustomerInfo.
assign tqCustomerInfo.tcDebtorCode = tqDebtorAddressContactInfo.tcDebtorCode
tqCustomerInfo.tcBusinessRelationCode = tqDebtorAddressContactInfo.tcBusinessRelationCode
tqCustomerInfo.tcBusinessRelationName1 = tqDebtorAddressContactInfo.tcBusinessRelationName1
tqCustomerInfo.tcBusinessRelationName2 = tqDebtorAddressContactInfo.tcBusinessRelationName2
tqCustomerInfo.tcAddressStreet2 = tqDebtorAddressContactInfo.tcAddressStreet2
tqCustomerInfo.tcAddressStreet1 = tqDebtorAddressContactInfo.tcAddressStreet1
tqCustomerInfo.tcAddressFax = tqDebtorAddressContactInfo.tcAddressFax
tqCustomerInfo.tcAddressCity = tqDebtorAddressContactInfo.tcAddressCity
tqCustomerInfo.tcAddressZip = tqDebtorAddressContactInfo.tcAddressZip
tqCustomerInfo.tcAddressTelephone = tqDebtorAddressContactInfo.tcAddressTelephone
tqCustomerInfo.tcContactPhone = tqDebtorAddressContactInfo.tcContactTelephone
tqCustomerInfo.tcContactName = tqDebtorAddressContactInfo.tcContactName
tqCustomerInfo.tcContactMobile = tqDebtorAddressContactInfo.tcContactMobilePhone
tqCustomerInfo.tcContactEmail = tqDebtorAddressContactInfo.tcContactEmail.
end.
end.
end. /* for each tqDebtorAccountMovements */