report procedure
Parameters
Internal usage
unused
program code (program7/bdebtorreport.p)
empty temp-table tqDebtorOpenItemsQuick.
assign vcDinvoiceOpInfoList = "":U.
<M-1 run GetReportLabels (input 'DebtorOpenItems':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-2 run SetDataItemsBasedOnFilterTT (output viFcReturnSuper (oiReturnStatus)) in BDebtorReport>
/* Cache Gl by Progile link */
<Q-4 run ProfileLinkByGL (all) (Read) (NoCache)
(input ?, (GlProfileId)
input viCompanyId, (CompanyId)
output dataset tqProfileLinkByGL) in BProfile >
/*call the base query*/
<Q-5 run BaseDebtorOpenItems (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcFromDebtorCodeFilter, (FromDebtorCode)
input vcToDebtorCodeFilter, (ToDebtorCode)
input vcCandoDebtorCodeFilter, (CandoDebtorCode)
input vcFromBRCodeFilter, (FromBRCode)
input vcToBRCodeFilter, (ToBRCode)
input vcCandoBRCodeFilter, (CandoBRCode)
input vcFromJournalCodeFilter, (FromJournalCode)
input vcToJournalCodeFilter, (ToJournalCode)
input vcCandoJournalCodeFilter, (CandoJournalCode)
input vcCurrencyCodeFilter, (CurrencyCode)
input ?, (ReasonCode)
input ?, (ActiveDebtors)
input ?, (WithReminders)
output dataset tqBaseDebtorOpenItems) in BDebtorReport>
/*do some extra filtering on the result set of the base query
that can't be written in the query itself*/
for each tqBaseDebtorOpenItems
break by tqBaseDebtorOpenItems.tiDInvoice_ID:
/* Assign DinvoiceOpInfo list */
assign vcDinvoiceOpInfoList = tqBaseDebtorOpenItems.tcDInvoiceShipperList.
if vcDinvoiceOpInfoList <> "":U
and vcDinvoiceOpInfoList <> ?
and tqBaseDebtorOpenItems.tcDInvoiceGoldenTaxInvoiceList <> "":U
and tqBaseDebtorOpenItems.tcDInvoiceGoldenTaxInvoiceList <> ?
then do:
assign vcDinvoiceOpInfoList = vcDinvoiceOpInfoList + ",".
end.
assign vcDinvoiceOpInfoList = vcDinvoiceOpInfoList + tqBaseDebtorOpenItems.tcDInvoiceGoldenTaxInvoiceList.
/* in case there are more than one DInvoiceOpInfo records with type SHIPPER, we will only use one record per invoice */
if not last-of(tqBaseDebtorOpenItems.tiDInvoice_ID)
then next.
/*FromDContrGLProfileCode, ToDContrGLProfileCode, CandoDContrGLProfileCode
these can't be written in the query as I have one set of condition/filter items
that must be applied to 2 Debtor fields - InvControlGLProfileCode and CnControlGLProfileCode
go to the next record if this doesn't fit to the condition*/
if /*first check InvControlGLProfileCode*/
((vcFromDContrGLProfileCodeFilter <> ? and
vcFromDContrGLProfileCodeFilter > tqBaseDebtorOpenItems.tcInvControlGLProfileCode) or
(vcToDContrGLProfileCodeFilter <> ? and
vcToDContrGLProfileCodeFilter < tqBaseDebtorOpenItems.tcInvControlGLProfileCode) or
(vcCandoDContrGLProfileCodeFilter <> ? and
not can-do(vcCandoDContrGLProfileCodeFilter, tqBaseDebtorOpenItems.tcInvControlGLProfileCode))) and
/*secondly check CnControlGLProfileCode*/
((vcFromDContrGLProfileCodeFilter <> ? and
vcFromDContrGLProfileCodeFilter > tqBaseDebtorOpenItems.tcCnControlGLProfileCode) or
(vcToDContrGLProfileCodeFilter <> ? and
vcToDContrGLProfileCodeFilter < tqBaseDebtorOpenItems.tcCnControlGLProfileCode) or
(vcCandoDContrGLProfileCodeFilter <> ? and
not can-do(vcCandoDContrGLProfileCodeFilter, tqBaseDebtorOpenItems.tcCnControlGLProfileCode)))
then next.
/*GL info initialization*/
assign vlControlGLIsValid = false
vcInvControlGLCode = "":U
vcInvControlGLDescription= "":U
vcCnControlGLCode = "":U
vcCnControlGLDescription = "":U.
/*get GLCode for InvControlGLProfileCode*/
find first tqProfileLinkByGL where
tqProfileLinkByGL.tiProfile_ID = tqBaseDebtorOpenItems.tiInvControlGLProfileID no-error.
/*check GLCode for InvControlGLProfileCode*/
if available tqProfileLinkByGL and
((vcFromDContrGLCodeFilter <= tqProfileLinkByGL.tcGLCode or
vcFromDContrGLCodeFilter = "":U or
vcFromDContrGLCodeFilter = "?":U or
vcFromDContrGLCodeFilter = ?) and
(vcToDContrGLCodeFilter >= tqProfileLinkByGL.tcGLCode or
vcToDContrGLCodeFilter = "":U or
vcToDContrGLCodeFilter = "?":U or
vcToDContrGLCodeFilter = ?) and
(can-do(vcCandoDContrGLCodeFilter, tqProfileLinkByGL.tcGLCode) or
vcCandoDContrGLCodeFilter = "":U or
vcCandoDContrGLCodeFilter = "?":U or
vcCandoDContrGLCodeFilter = ?))
then assign vlControlGLIsValid = true
viInvControlGLId = tqProfileLinkByGL.tiGL_ID
vcInvControlGLCode = tqProfileLinkByGL.tcGLCode
vcInvControlGLDescription = tqProfileLinkByGL.tcGLDescription.
/*get GLCode for CnControlGLProfileCode*/
find first tqProfileLinkByGL where
tqProfileLinkByGL.tiProfile_ID = tqBaseDebtorOpenItems.tiCnControlGLProfileID no-error.
/*check GLCode for CnControlGLProfileCode*/
if available tqProfileLinkByGL and
((vcFromDContrGLCodeFilter <= tqProfileLinkByGL.tcGLCode or
vcFromDContrGLCodeFilter = "":U or
vcFromDContrGLCodeFilter = "?":U or
vcFromDContrGLCodeFilter = ?) and
(vcToDContrGLCodeFilter >= tqProfileLinkByGL.tcGLCode or
vcToDContrGLCodeFilter = "":U or
vcToDContrGLCodeFilter = "?":U or
vcToDContrGLCodeFilter = ?) and
(can-do(vcCandoDContrGLCodeFilter, tqProfileLinkByGL.tcGLCode) or
vcCandoDContrGLCodeFilter = "":U or
vcCandoDContrGLCodeFilter = "?":U or
vcCandoDContrGLCodeFilter = ?))
then assign vlControlGLIsValid = true
viCnControlGLId = tqProfileLinkByGL.tiGL_ID
vcCnControlGLCode = tqProfileLinkByGL.tcGLCode
vcCnControlGLDescription = tqProfileLinkByGL.tcGLDescription.
if not vlControlGLIsValid
then next.
/*record is valid only if
a) DInvoice of type "Invoice" (according to HanHor in this case type <> "CreditNote") and
vcInvControlGLCode exists and fits the filter criteria or
b) DInvoice of type "CreditNote" and
vcCnControlGLCode exists and fits the filter criteria */
if not ((tqBaseDebtorOpenItems.tcDInvoiceType <> {&INVOICETYPE-CREDITNOTE} and
vcInvControlGLCode <> "":U and
vcInvControlGLCode <> "?":U and
vcInvControlGLCode <> ?) or
(tqBaseDebtorOpenItems.tcDInvoiceType = {&INVOICETYPE-CREDITNOTE} and
vcCnControlGLCode <> "":U and
vcCnControlGLCode <> "?":U and
vcCnControlGLCode <> ?))
then next.
/*in here all invalid recods were filter out and we can create a report record*/
create tqDebtorOpenItemsQuick.
buffer-copy tqBaseDebtorOpenItems to tqDebtorOpenItemsQuick.
/*GL info assign*/
assign tqDebtorOpenItemsQuick.tcInvControlGLCode = vcInvControlGLCode
tqDebtorOpenItemsQuick.tcInvControlGLDescription = vcInvControlGLDescription
tqDebtorOpenItemsQuick.tcCnControlGLCode = vcCnControlGLCode
tqDebtorOpenItemsQuick.tcCnControlGLDescription = vcCnControlGLDescription
tqDebtorOpenItemsQuick.tiPostingVoucher = tqBaseDebtorOpenItems.tiDInvoiceVoucher
tqDebtorOpenItemsQuick.tcDInvoiceOpInfoCode = vcDinvoiceOpInfoList
/*CnControlGL for DInvoice of type "CreditNote" otherwise InvControlGL*/
tqDebtorOpenItemsQuick.tiControlGLId =
if tqDebtorOpenItemsQuick.tcDInvoiceType = {&INVOICETYPE-CREDITNOTE}
then viCnControlGLId
else viInvControlGLId
tqDebtorOpenItemsQuick.tcControlGLCode =
if tqDebtorOpenItemsQuick.tcDInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLCode
else vcInvControlGLCode
tqDebtorOpenItemsQuick.tcControlGLDescription =
if tqDebtorOpenItemsQuick.tcDInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLDescription
else vcInvControlGLDescription.
if last-of(tqBaseDebtorOpenItems.tiDInvoice_ID) then assign vcDinvoiceOpInfoList = "".
end.
/*Calculates some extra information*/
if vlCalcExtraInfoFilter = true
then for each tqDebtorOpenItemsQuick break by tqDebtorOpenItemsQuick.tiBusinessRelation_ID:
/*Calculates Address information for the BusinessRelation_ID*/
assign vhFcComponent = ?
vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
if first-of (tqDebtorOpenItemsQuick.tiBusinessRelation_ID)
then do:
<M-6 run GetAddressWithType (input tqDebtorOpenItemsQuick.tiBusinessRelation_ID (iiBusinessRelationId),
input tqDebtorOpenItemsQuick.tcBusinessRelationCode (icBusinessRelationCode),
input ? (iiAddressTypeId),
input vcAddressTypeCode (icAddressTypeCode),
output tAddressQueryRef (tAddressQuery),
output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>
find first tAddressQueryRef no-error.
<Q-9 run ContactByAddress (all) (Read) (NoCache)
(input tAddressQueryRef.tiAddress_ID, (AddressId)
output dataset tqContactByAddress) in BBusinessRelation >
find first tqContactByAddress no-error.
end.
if available tAddressQueryRef
then assign tqDebtorOpenItemsQuick.tcAddressCity = tAddressQueryRef.tcAddressCity
tqDebtorOpenItemsQuick.tcAddressFax = tAddressQueryRef.tcAddressFax
tqDebtorOpenItemsQuick.tcAddressStreet1 = tAddressQueryRef.tcAddressStreet1
tqDebtorOpenItemsQuick.tcAddressStreet2 = tAddressQueryRef.tcAddressStreet2
tqDebtorOpenItemsQuick.tcAddressTelephone= tAddressQueryRef.tcAddressTelephone
tqDebtorOpenItemsQuick.tcAddressZip = tAddressQueryRef.tcAddressZip.
/*Calculates Contact info for the BusinessRelation_ID*/
if available tqContactByAddress
then assign tqDebtorOpenItemsQuick.tcContactEmail = tqContactByAddress.tcContactEmail
tqDebtorOpenItemsQuick.tcContactName = tqContactByAddress.tcContactName
tqDebtorOpenItemsQuick.tcContactPhone = tqContactByAddress.tcContactTelephone
tqDebtorOpenItemsQuick.tcContactMobile = tqContactByAddress.tcContactMobilePhone.
end.
/* Add filterfields to one special line
It has two fields separated by '#'
tcreportingInfo: SummaryByFiler#ReportingCurrency */
if can-find(first tqDebtorOpenItemsQuick)
then do:
if vcSummaryByFilter = ? then assign vcSummaryByFilter = {&DEBTORSUMMARYBY-ALL}.
if vcReportingCurrencyFilter = ? then assign vcReportingCurrencyFilter = {&CURRENCYTYPE-LC}.
if vlDebtorPerPageFilter = ? then assign vlDebtorPerPageFilter = no.
create tqDebtorOpenItemsQuick.
assign tqDebtorOpenItemsQuick.tcReportingInfo = vcSummaryByFilter + "#":U + vcReportingCurrencyFilter + "#":U + String(vlDebtorPerPageFilter).
end.