report procedure
Description
CreditorOpenItems
Parameters
| icLanguageCode | input | character | |
| tFilter | input | temp-table | |
| dcrCreditorOpenItems | output | dataset | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
unused
program code (program7/bcreditorreport.p)
empty temp-table tqCreditorOpenItems.
<M-11 run GetReportLabels (input 'CreditorOpenItems':U (icReportName),
input icLanguageCode (icLanguageCode),
input tFilter (tFilter),
output tqHeader (tqHeader),
output tqFilter (tqFilter),
output tqText (tqText),
output oiReturnStatus (oiReturnStatus)) in BCreditorReport>
if oiReturnStatus <> 0
then return.
/*assign tFilter*/
<M-1 run SetDataItemsBasedOnFilterTT (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
/* Check If Filterfields are correct */
/*
assign vcCErrorMessage = "":U.
if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC} and vcCurrencyCodeFilter1 = ?
then assign vcCErrorMessage = vcCErrorMessage + #T-12'You must enter the currency code.':50(1460)T-12# + chr(10).
if vcCErrorMessage <> "":U
then do:
<M-9 run SetMessage (input vcCErrorMessage (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-901':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
assign oiReturnStatus = -1.
return.
end.
*/
/*call the base query*/
<Q-2 run BaseCreditorOpenItems (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcFromCreditorCodeFilter1, (FromCreditorCode)
input vcToCreditorCodeFilter1, (ToCreditorCode)
input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
input vcFromBRCodeFilter1, (FromBRCode)
input vcToBRCodeFilter1, (ToBRCode)
input vcCandoBRCodeFilter1, (CandoBRCode)
input vcFromDivisionCodeFilter1, (FromDivisionCode)
input vcToDivisionCodeFilter1, (ToDivisionCode)
input vcCandoDivisionCodeFilter1, (CandoDivisionCode)
input vcFromJournalCodeFilter1, (FromJournalCode)
input vcToJournalCodeFilter1, (ToJournalCode)
input vcCandoJournalCodeFilter1, (CandoJournalCode)
input vcCurrencyCodeFilter1, (CurrencyCode)
input vlIncludeInitial, (IncludeInitial)
output dataset tqBaseCreditorOpenItems) in BCreditorReport >
/*Start queries*/
<Q-10 run ProfileLinkByGL (all) (Read) (NoCache)
(input ?, (GlProfileId)
input viCompanyId, (CompanyId)
output dataset tqProfileLinkByGL) in BProfile >
/*do some extra filtering on the result set of the base query
that can't be written in the query itself*/
for each tqBaseCreditorOpenItems:
/*FromCContrGLProfCode, ToCContrGLProfCode, CandoCContrGLProfCode
these can't be written in the query as I have one set of condition/filter items
that must be applied to 2 Creditor fields - InvControlGLProfileCode and CnControlGLProfileCode
go to the next record if this doesn't fit to the condition*/
if /*first check InvControlGLProfileCode*/
((vcFromCContrGLProfCodeFilter1 <> ? and
vcFromCContrGLProfCodeFilter1 > tqBaseCreditorOpenItems.tcInvControlGLProfileCode) or
(vcToCContrGLProfCodeFilter1 <> ? and
vcToCContrGLProfCodeFilter1 < tqBaseCreditorOpenItems.tcInvControlGLProfileCode) or
(vcCandoCContrGLProfCodeFilter1 <> ? and
not can-do(vcCandoCContrGLProfCodeFilter1, tqBaseCreditorOpenItems.tcInvControlGLProfileCode))) and
/*secondly check CnControlGLProfileCode*/
((vcFromCContrGLProfCodeFilter1 <> ? and
vcFromCContrGLProfCodeFilter1 > tqBaseCreditorOpenItems.tcCnControlGLProfileCode) or
(vcToCContrGLProfCodeFilter1 <> ? and
vcToCContrGLProfCodeFilter1 < tqBaseCreditorOpenItems.tcCnControlGLProfileCode) or
(vcCandoCContrGLProfCodeFilter1 <> ? and
not can-do(vcCandoCContrGLProfCodeFilter1, tqBaseCreditorOpenItems.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 = tqBaseCreditorOpenItems.tiInvControlGLProfileID no-error.
/*check GLCode for InvControlGLProfileCode*/
if available tqProfileLinkByGL and
((vcFromCContrGLCodeFilter1 <= tqProfileLinkByGL.tcGLCode or
vcFromCContrGLCodeFilter1 = "":U or
vcFromCContrGLCodeFilter1 = "?":U or
vcFromCContrGLCodeFilter1 = ?) and
(vcToCContrGLCodeFilter1 >= tqProfileLinkByGL.tcGLCode or
vcToCContrGLCodeFilter1 = "":U or
vcToCContrGLCodeFilter1 = "?":U or
vcToCContrGLCodeFilter1 = ?) and
(can-do(vcCandoCContrGLCodeFilter1, tqProfileLinkByGL.tcGLCode) or
vcCandoCContrGLCodeFilter1 = "":U or
vcCandoCContrGLCodeFilter1 = "?":U or
vcCandoCContrGLCodeFilter1 = ?))
then assign vlControlGLIsValid = true
vcInvControlGLCode = tqProfileLinkByGL.tcGLCode
vcInvControlGLDescription = tqProfileLinkByGL.tcGLDescription.
/*get GLCode for CnControlGLProfileCode*/
find first tqProfileLinkByGL where
tqProfileLinkByGL.tiProfile_ID = tqBaseCreditorOpenItems.tiCnControlGLProfileID no-error.
/*check GLCode for CnControlGLProfileCode*/
if available tqProfileLinkByGL and
((vcFromCContrGLCodeFilter1 <= tqProfileLinkByGL.tcGLCode or
vcFromCContrGLCodeFilter1 = "":U or
vcFromCContrGLCodeFilter1 = "?":U or
vcFromCContrGLCodeFilter1 = ?) and
(vcToCContrGLCodeFilter1 >= tqProfileLinkByGL.tcGLCode or
vcToCContrGLCodeFilter1 = "":U or
vcToCContrGLCodeFilter1 = "?":U or
vcToCContrGLCodeFilter1 = ?) and
(can-do(vcCandoCContrGLCodeFilter1, tqProfileLinkByGL.tcGLCode) or
vcCandoCContrGLCodeFilter1 = "":U or
vcCandoCContrGLCodeFilter1 = "?":U or
vcCandoCContrGLCodeFilter1 = ?))
then assign vlControlGLIsValid = true
vcCnControlGLCode = tqProfileLinkByGL.tcGLCode
vcCnControlGLDescription = tqProfileLinkByGL.tcGLDescription.
if not vlControlGLIsValid
then next.
/*record is valid only if
a) CInvoice of type "Invoice" (according to HanHor in this case type <> "CreditNote") and
vcInvControlGLCode exists and fits the filter criteria or
b) CInvoice of type "CreditNote" and
vcCnControlGLCode exists and fits the filter criteria */
if not ((tqBaseCreditorOpenItems.tcCInvoiceType <> {&INVOICETYPE-CREDITNOTE} and
vcInvControlGLCode <> "":U and
vcInvControlGLCode <> "?":U and
vcInvControlGLCode <> ?) or
(tqBaseCreditorOpenItems.tcCInvoiceType = {&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 tqCreditorOpenItems.
buffer-copy tqBaseCreditorOpenItems to tqCreditorOpenItems.
/*GL info assign*/
assign tqCreditorOpenItems.tcInvControlGLCode = vcInvControlGLCode
tqCreditorOpenItems.tcInvControlGLDescription = vcInvControlGLDescription
tqCreditorOpenItems.tcCnControlGLCode = vcCnControlGLCode
tqCreditorOpenItems.tcCnControlGLDescription = vcCnControlGLDescription
tqCreditorOpenItems.tiPostingVoucher = tqBaseCreditorOpenItems.tiCInvoiceVoucher
/*CnControlGL for CInvoice of type "CreditNote" otherwise InvControlGL*/
tqCreditorOpenItems.tcControlGLCode =
if tqCreditorOpenItems.tcCInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLCode
else vcInvControlGLCode
tqCreditorOpenItems.tcControlGLDescription =
if tqCreditorOpenItems.tcCInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLDescription
else vcInvControlGLDescription.
end.
/*Calculates some extra information*/
if vlCalcExtraInfoFilter1
then for each tqCreditorOpenItems break by tqCreditorOpenItems.tcCreditorCode:
/*Calculates Address information for the BusinessRelation_ID*/
assign vhFcComponent = ?
vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
if first-of(tqCreditorOpenItems.tcCreditorCode)
then do:
<M-7 run GetAddressWithType (input tqCreditorOpenItems.tiBusinessRelation_ID (iiBusinessRelationId),
input tqCreditorOpenItems.tcBusinessRelationCode (icBusinessRelationCode),
input ? (iiAddressTypeId),
input vcAddressTypeCode (icAddressTypeCode),
output tAddressQueryRef1 (tAddressQuery),
output viFcReturnSuper (oiReturnStatus)) in BBusinessRelation>
find first tAddressQueryRef1 no-error.
/*Calculates Contact info */
<Q-13 run ContactByAddress (all) (Read) (NoCache) (input tAddressQueryRef1.tiAddress_ID, (AddressId)
output dataset tqContactByAddress) in BBusinessRelation >
find first tqContactByAddress no-error.
end.
if available tAddressQueryRef1
then assign tqCreditorOpenItems.tcAddressCity = tAddressQueryRef1.tcAddressCity
tqCreditorOpenItems.tcAddressFax = tAddressQueryRef1.tcAddressFax
tqCreditorOpenItems.tcAddressStreet1 = tAddressQueryRef1.tcAddressStreet1
tqCreditorOpenItems.tcAddressStreet2 = tAddressQueryRef1.tcAddressStreet2
tqCreditorOpenItems.tcAddressTelephone= tAddressQueryRef1.tcAddressTelephone
tqCreditorOpenItems.tcAddressZip = tAddressQueryRef1.tcAddressZip.
if available tqContactByAddress
then assign tqCreditorOpenItems.tcContactEmail = tqContactByAddress.tcContactEmail
tqCreditorOpenItems.tcContactName = tqContactByAddress.tcContactName
tqCreditorOpenItems.tcContactPhone = tqContactByAddress.tcContactTelephone
tqCreditorOpenItems.tcContactMobile = tqContactByAddress.tcContactMobilePhone.
end.
/* Add filterfields to one special line
It has two fields separated by '#'
tcreportingInfo: SummaryByFiler#ReportingCurrency */
if can-find(first tqCreditorOpenItems)
then do:
if vcSummaryByFilter1 = ? then assign vcSummaryByFilter1 = {&CREDITORSUMMARYBY-ALL}.
if vcReportingCurrencyFilter1 = ? then assign vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC}.
if vlCreditorPerPageFilter1 = ? then assign vlCreditorPerPageFilter1 = no.
create tqCreditorOpenItems.
assign tqCreditorOpenItems.tcReportingInfo = vcSummaryByFilter1 + "#":U + vcReportingCurrencyFilter1 + "#":U + String(vlCreditorPerPageFilter1).
end.