report procedure
Parameters
| icLanguageCode | input | character | |
| tFilter | input | temp-table | |
| dcrDebtorAccountState | output | dataset | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
unused
program code (program7/bdebtorreport.p)
empty temp-table tqDebtorAccountState.
/* get all the field labels of the report */
assign vhFcComponent = ?.
<M-10 run GetReportLabels
(input 'DebtorAccountState':U (icReportName),
input icLanguageCode (icLanguageCode),
input tFilter (tFilter),
output tqHeader (tqHeader),
output tqFilter (tqFilter),
output tqText (tqText),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* dealing with error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/*assign tFilter*/
<M-1 run SetDataItemsBasedOnFilterTT (output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* dealing with error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/* ===================== */
/* Assign default values */
/* ===================== */
if vtStatementDateFilter = ?
then assign vtStatementDateFilter = today.
if vcDRCompanyFilter = ?
then assign vcDRCompanyFilter = "":U.
if vcEntityFilter = ?
then assign vcEntityFilter = vcCompanyCode.
/* start query */
<Q-13 run DebtorBalance
(Start) in BDebtorReport >
<Q-14 run DebtorOpenItemsByStatement
(Start) in BDebtorReport >
<Q-15 run CompanyPrim
(Start) in BCompany >
<Q-16 run CompanyPropertyForAllData (Start) in BCompanyProperty >
if vcEntityFilter = "":U or vcEntityFilter = ?
then assign vcDErrorMessage = vcDErrorMessage + #T-35'You must specify the header entity parameter to run this report':255(65088)T-35# + chr(10).
if vcDErrorMessage <> "":U
then do:
<M-34 run SetMessage (input vcDErrorMessage (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-6958':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
assign oiReturnStatus = -1.
return.
end.
<Q-36 run CompanyPrim (all) (Read) (NoCache)
(input ?, (LookupCompanyId)
input vcEntityFilter, (CompanyCode)
output dataset tqCompanyPrim) in BCompany >
find first tqCompanyPrim no-error.
/* validate some parameters */
assign vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
<Q-28 run GetCompanyAddress (all) (Read) (NoCache)
(input tqCompanyPrim.tiCompany_ID, (CompanyId)
input vcAddressTypeCode, (AddressTypeCode)
input true, (ContactIsPrimary)
input '':U, (CompanyCode)
output dataset tqGetCompanyAddress) in BDebtorReport >
find first tqGetCompanyAddress no-lock no-error.
if not available tqGetCompanyAddress or
(available tqGetCompanyAddress and tqGetCompanyAddress.tcContactName = "":U)
then do:
assign vcDErrorMessage = #T-30'You have to fill in the primary contact of your own entity before a customer statement of account can be printed':255(71097)T-30#.
end.
if vcDErrorMessage <> "":U
then do:
<M-37 run SetMessage (input vcDErrorMessage (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QadFin-6959':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
assign oiReturnStatus = -1.
return.
end.
/* Cross-company Iteration */
do viDRCompanyIterator = 1 to viDRCompanyEntries:
<M-33 run CrossCompanyValues (input viDRCompanyIterator (iiEntryNumber),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* dealing with error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
/* =================================================================== */
/* First Phase */
/* Choose active debtors, with active reminders which have balance > 0 */
/* =================================================================== */
/* <Q-3 run DebtorBalance (all) (Read) (NoCache)
(input viDRCurrentCompany_ID, (CompanyId)
input ?, (DebtorId)
input ?, (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 vdSumDebtorBalance = 0.
for each tqDebtorBalance break by tqDebtorBalance.tiDebtor_ID:
/*eliminate ? values*/
assign vdSumDebtorBalance = vdSumDebtorBalance + if tqDebtorBalance.tdDBalanceLC = ? then 0 else tqDebtorBalance.tdDBalanceLC.
if last-of(tqDebtorBalance.tiDebtor_ID)
then do:
if vdSumDebtorBalance > 0
then do:
create tAvailableDebtors.
assign tAvailableDebtors.tiDebtor_ID = tqDebtorBalance.tiDebtor_ID.
end.
assign vdSumDebtorBalance = 0.
end.
end. */
/* ======================================== */
/* Phase two */
/* Use open items querry and advance filter */
/* ======================================== */
/*call the base query*/
<Q-21 run DebtorOpenItemsByStatement (all) (Read) (NoCache)
(input viDRCurrentCompany_ID, (CompanyId)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input vcCandoDebtorCodeFilter, (CandoDebtorCode)
input vcFromBRCodeFilter, (FromBRCode)
input vcToBRCodeFilter, (ToBRCode)
input vcCandoBRCodeFilter, (CandoBRCode)
input vtStatementDateFilter, (StatementDate)
input vcStatementCycleFilter, (StatementCycle)
output dataset tqDebtorOpenItemsByStatement) in BDebtorReport >
for each tqDebtorOpenItemsByStatement break by tqDebtorOpenItemsByStatement.tiDInvoice_ID: /* break by tqDebtorOpenItemsByStatement.tiDebtor_ID by tqDebtorOpenItemsByStatement.tiDInvoiceReminderCount desc: */
/* If this debtor has balance < 0 we'll skip him */
/* if not can-find(first tAvailableDebtors where
tAvailableDebtors.tiDebtor_ID = tqDebtorOpenItemsByStatement.tiDebtor_ID)
then next. */
if first-of(tqDebtorOpenItemsByStatement.tiDInvoice_ID)
then do:
assign
vdBalanceCreditTC = 0
vdBalanceCreditLC = 0
vdBalanceDebitTC = 0
vdBalanceDebitLC = 0
vcDInvoiceDeductionStatus = '':U.
/* Get deduction status */
if tqDebtorOpenItemsByStatement.tcDInvoiceType = {&INVOICETYPE-DEDUCTION} then
do:
<M-94 run CalculateDIDeductionStatus
(input tqDebtorOpenItemsByStatement.tiDInvoice_ID (iiDInvoiceId),
output vcDInvoiceDeductionStatus (ocDIDeductionStatus),
output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
end.
end.
assign
vdBalanceCreditTC = vdBalanceCreditTC + tqDebtorOpenItemsByStatement.tdPostingLineCreditTC
vdBalanceCreditLC = vdBalanceCreditLC + tqDebtorOpenItemsByStatement.tdPostingLineCreditLC
vdBalanceDebitTC = vdBalanceDebitTC + tqDebtorOpenItemsByStatement.tdPostingLineDebitTC
vdBalanceDebitLC = vdBalanceDebitLC + tqDebtorOpenItemsByStatement.tdPostingLineDebitLC.
if last-of(tqDebtorOpenItemsByStatement.tiDInvoice_ID)
then do:
if (vdBalanceDebitTC - vdBalanceCreditTC) = 0 or
(vdBalanceDebitLC - vdBalanceCreditLC) = 0
then do:
next.
end.
if tqDebtorOpenItemsByStatement.tcPaymentConditionPaymentTyp = {&PAYMENTCONDITIONPAYMENTTYPE-STAGED}
and tqDebtorOpenItemsByStatement.tcDInvoiceType <> {&INVOICETYPE-DEDUCTION}
and tqDebtorOpenItemsByStatement.tcDInvoiceType <> {&INVOICETYPE-PREPAYMENT}
then do:
<Q-40 run DInvoiceStageByInvoice (all) (Read) (NoCache)
(input tqDebtorOpenItemsByStatement.tiDInvoice_ID, (DInvoiceId)
output dataset tqDInvoiceStageByInvoice) in BDebtorReport>
for each tqDInvoiceStageByInvoice:
if tqDInvoiceStageByInvoice.tdDInvoiceStageAmountTC - tqDInvoiceStageByInvoice.tdDInvoiceStageAmtAppliedTC = 0
then next.
else do:
create tqDebtorAccountState.
buffer-copy tqDebtorOpenItemsByStatement to tqDebtorAccountState.
assign
tqDebtorAccountState.tlIsOwnAddressLine = false
tqDebtorAccountState.tdDInvoiceOriginalDebitTC = if tqDebtorOpenItemsByStatement.tdDInvoiceOriginalDebitTC > tqDebtorOpenItemsByStatement.tdDInvoiceOriginalCreditTC
then tqDInvoiceStageByInvoice.tdDInvoiceStageAmountTC
else 0
tqDebtorAccountState.tdDInvoiceOriginalCreditTC = if tqDebtorOpenItemsByStatement.tdDInvoiceOriginalDebitTC < tqDebtorOpenItemsByStatement.tdDInvoiceOriginalCreditTC
then tqDInvoiceStageByInvoice.tdDInvoiceStageAmountTC
else 0
tqDebtorAccountState.tdDInvoiceBalanceDebitTC = if tqDebtorOpenItemsByStatement.tdDInvoiceOriginalDebitTC > tqDebtorOpenItemsByStatement.tdDInvoiceOriginalCreditTC
then tqDInvoiceStageByInvoice.tdDInvoiceStageAmountTC - tqDInvoiceStageByInvoice.tdDInvoiceStageAmtAppliedTC
else 0
tqDebtorAccountState.tdDInvoiceBalanceCreditTC = if tqDebtorOpenItemsByStatement.tdDInvoiceOriginalDebitTC < tqDebtorOpenItemsByStatement.tdDInvoiceOriginalCreditTC
then tqDInvoiceStageByInvoice.tdDInvoiceStageAmountTC - tqDInvoiceStageByInvoice.tdDInvoiceStageAmtAppliedTC
else 0
tqDebtorAccountState.ttDInvoiceDueDate = tqDInvoiceStageByInvoice.ttDInvoiceStageDueDate
tqDebtorAccountState.tcDInvoiceTSMNumber = tqDebtorOpenItemsByStatement.tcDInvoiceTSMNumber
tqDebtorAccountState.tcDInvoiceDescription = tqDebtorOpenItemsByStatement.tcDInvoiceDescription.
if vtStatementDateFilter <= tqDInvoiceStageByInvoice.ttDInvoiceStageDueDate
then assign tqDebtorAccountState.tlOverDue = false.
else assign tqDebtorAccountState.tlOverDue = true.
end.
end. /* for each tqDInvoiceStageByInvoice */
end.
else do:
create tqDebtorAccountState.
buffer-copy tqDebtorOpenItemsByStatement to tqDebtorAccountState.
assign
tqDebtorAccountState.tdDInvoiceBalanceDebitTC = vdBalanceDebitTC
tqDebtorAccountState.tdDInvoiceBalanceDebitLC = vdBalanceDebitLC
tqDebtorAccountState.tdDInvoiceBalanceCreditTC = vdBalanceCreditTC
tqDebtorAccountState.tdDInvoiceBalanceCreditLC = vdBalanceCreditLC
tqDebtorAccountState.tcDInvoiceDeductionStatus = vcDInvoiceDeductionStatus
tqDebtorAccountState.tlIsOwnAddressLine = false.
if vtStatementDateFilter > tqDebtorOpenItemsByStatement.ttDInvoiceDueDate
then assign tqDebtorAccountState.tlOverDue = true.
else assign tqDebtorAccountState.tlOverDue = false.
end.
end.
end.
/* ======================= */
/* Phase 3 */
/* Add Address Information */
/* ======================= */
for each tqDebtorAccountState break by tqDebtorAccountState.tcDebtorCode:
/*Calculates Address information for the BusinessRelation_ID*/
assign vhFcComponent = ?
vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
if first-of (tqDebtorAccountState.tcDebtorCode)
then do:
<M-5 run GetAddressWithType
(input tqDebtorAccountState.tiBusinessRelation_ID (iiBusinessRelationId),
input tqDebtorAccountState.tcBusinessRelationCode (icBusinessRelationCode),
input ? (iiAddressTypeId),
input vcAddressTypeCode (icAddressTypeCode),
output tAddressQueryRef (tAddressQuery),
output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>
/* dealing with error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
end. /*first-of (tqDebtorAccountState.tcDebtorCode)*/
find first tAddressQueryRef no-error.
if available tAddressQueryRef
then do:
buffer-copy tAddressQueryRef to tqDebtorAccountState.
/* Add state information */
<Q-38 run GetStateInfoByAddressId (all) (Read) (NoCache)
(input tAddressQueryRef.tiAddress_ID, (AddressId)
output dataset tqGetStateInfoByAddressId) in BDebtorReport >
for first tqGetStateInfoByAddressId:
assign
tqDebtorAccountState.tcStateCode = tqGetStateInfoByAddressId.tcStateCode
tqDebtorAccountState.tiAddressFormat = tqGetStateInfoByAddressId.tiAddressFormat.
end.
end.
assign vcDInvoiceOpInfoType = {&DINVOICEOPINFOTYPE-SHIPPER} + "," + {&DINVOICEOPINFOTYPE-GOLDENTAX}
vcDInvoiceOpInfoCode = "".
/* Add Shipper_ID */
<Q-89 run DInvoiceOpInfoForLegalDocGT (all) (Read) (NoCache)
(input tqDebtorAccountState.tiDInvoice_ID, (DInvoiceID)
input vcDInvoiceOpInfoType, (DInvoiceOpInfoType)
output dataset tqDInvoiceOpInfoForLegalDocGT) in BDebtorReport>
for each tqDInvoiceOpInfoForLegalDocGT break by tqDInvoiceOpInfoForLegalDocGT.tiDInvoice_ID:
if first-of(tqDInvoiceOpInfoForLegalDocGT.tiDInvoice_ID) then
vcDInvoiceOpInfoCode = tqDInvoiceOpInfoForLegalDocGT.tcDInvoiceOpInfoCode.
else
vcDInvoiceOpInfoCode = vcDInvoiceOpInfoCode + "," + tqDInvoiceOpInfoForLegalDocGT.tcDInvoiceOpInfoCode.
end.
assign tqDebtorAccountState.tcDInvoiceOpInfoCode = vcDInvoiceOpInfoCode.
end. /* for each tqDebtorAccountState break by tqDebtorAccountState.tcDebtorCode */
/* Now get own address in case a result set hold some data */
if can-find(first tqDebtorAccountState)
then do:
assign vhFcComponent = ?
vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
<Q-6 run CompanyPrim (all) (Read) (NoCache) (input ?, (LookupCompanyId)
input vcEntityFilter, (CompanyCode)
output dataset tqCompanyPrim) in BCompany >
find first tqCompanyPrim no-error.
if available tqCompanyPrim
then assign viEntityFilter = tqCompanyPrim.tiCompany_ID.
<Q-7 run CompanyPropertyForAllData (all) (Read) (NoCache)
(input viEntityFilter, (CompanyId)
output dataset tqCompanyPropertyForAllData) in BCompanyProperty >
find first tqCompanyPropertyForAllData no-error.
if available tqCompanyPropertyForAllData then do:
assign vhFcComponent = ?.
<M-12 run GetAddressWithType
(input tqCompanyPropertyForAllData.tiBusinessRelation_ID (iiBusinessRelationId),
input ? (icBusinessRelationCode),
input ? (iiAddressTypeId),
input vcAddressTypeCode (icAddressTypeCode),
output tAddressQueryRef (tAddressQuery),
output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>
/* dealing with error */
if viFcReturnSuper < 0 or (viFcReturnSuper > 0 and oiReturnStatus >= 0)
then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then return.
find first tAddressQueryRef no-error.
if available tAddressQueryRef
then do:
create tqDebtorAccountState.
buffer-copy tAddressQueryRef to tqDebtorAccountState.
assign tqDebtorAccountState.tlIsOwnAddressLine = true.
find first tqGetCompanyAddress no-lock no-error.
if available tqGetCompanyAddress
then do:
assign
tqDebtorAccountState.tcContactName = tqGetCompanyAddress.tcContactName
tqDebtorAccountState.tcContactFunction = tqGetCompanyAddress.tcContactFunction
tqDebtorAccountState.tcContactTelephone = tqGetCompanyAddress.tcAddressTelephone
tqDebtorAccountState.tcStateCode = tqGetCompanyAddress.tcStateCode
tqDebtorAccountState.tiAddressFormat = tqGetCompanyAddress.tiAddressFormat.
end.
end.
end.
end.
end. /* do viDRCompanyIterator = 1 to viDRCompanyEntries */
/* stop queries */
<Q-17 run DebtorBalance
(Stop) in BDebtorReport >
<Q-18 run DebtorOpenItemsByStatement
(Stop) in BDebtorReport >
<Q-19 run CompanyPrim
(Stop) in BCompany >
<Q-20 run CompanyPropertyForAllData (Stop) in BCompanyProperty >