report procedure
Parameters
Internal usage
unused
program code (program7/bcreditorreport.p)
empty temp-table tqCreditorPrepayments.
<M-9 run GetReportLabels (input 'CreditorPrepayments':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-10'You must enter the currency code.':50(1460)T-10# + chr(10).
if vcCErrorMessage <> "":U
then do:
<M-2 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-950':U (icFcMsgNumber),
input '' (icFcExplanation),
input '' (icFcIdentification),
input '' (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
end.
/*base query*/
assign vcCInvoiceType = {&INVOICETYPE-PREPAYMENT}.
/* 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.
if viFromAccPeriodFilter1 = 0 then viFromAccPeriodFilter1 = -999.
assign viFromAccPeriodFilter1 = (viFromAccYearFilter1 * 100) + viFromAccPeriodFilter1.
assign viToAccPeriodFilter1 = (viToAccYearFilter1 * 100) + viToAccPeriodFilter1.
<Q-3 run BaseCreditor (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcCandoBRCodeFilter1, (CandoBR)
input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
input vcCandoDivisionCodeFilter1, (CandoDivisionCode)
input vcCandoJournalCodeFilter1, (CandoJournalCode)
input vcCurrencyCodeFilter1, (CurrencyCode)
input vcCInvoiceType, (CInvoiceType)
input vcFromBRCodeFilter1, (FromBR)
input vcFromCreditorCodeFilter1, (FromCreditorCode)
input vcFromDivisionCodeFilter1, (FromDivisionCode)
input vcFromJournalCodeFilter1, (FromJournalCode)
input viFromAccPeriodFilter1, (FromPeriodPeriod)
input vcToBRCodeFilter1, (ToBR)
input vcToCreditorCodeFilter1, (ToCreditorCode)
input vcToDivisionCodeFilter1, (ToDivisionCode)
input vcToJournalCodeFilter1, (ToJournalCode)
input viToAccPeriodFilter1, (ToPeriodPeriod)
output dataset tqBaseCreditor) in BCreditorReport >
/*Start queries*/
<Q-4 run ProfileLinkByGL (Start) 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 tqBaseCreditor:
/*FromCContrGLProfileCode, ToCContrGLProfileCode, CandoCContrGLProfileCode
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
InvControlGLProfileCode and CnControlGLProfileCode are mandatory for a Creditor therefor I do not
have to check whether these are blank or undefined or unknown*/
if /*first check InvControlGLProfileCode*/
((vcFromCContrGLProfCodeFilter1 <> ? and
vcFromCContrGLProfCodeFilter1 > tqBaseCreditor.tcInvControlGLProfileCode) or
(vcToCContrGLProfCodeFilter1 <> ? and
vcToCContrGLProfCodeFilter1 < tqBaseCreditor.tcInvControlGLProfileCode) or
(vcCandoCContrGLProfCodeFilter1 <> ? and
not can-do(vcCandoCContrGLProfCodeFilter1, tqBaseCreditor.tcInvControlGLProfileCode))) and
/*secondly check CnControlGLProfileCode*/
((vcFromCContrGLProfCodeFilter1 <> ? and
vcFromCContrGLProfCodeFilter1 > tqBaseCreditor.tcCnControlGLProfileCode) or
(vcToCContrGLProfCodeFilter1 <> ? and
vcToCContrGLProfCodeFilter1 < tqBaseCreditor.tcCnControlGLProfileCode) or
(vcCandoCContrGLProfCodeFilter1 <> ? and
not can-do(vcCandoCContrGLProfCodeFilter1, tqBaseCreditor.tcCnControlGLProfileCode)))
then next.
/*GL info initialization*/
assign vlControlGLIsValid = false
vcInvControlGLCode = "":U
vcInvControlGLDescription= "":U
vcCnControlGLCode = "":U
vcCnControlGLDescription = "":U.
/*get GLCode for InvControlGLProfileCode*/
<Q-5 run ProfileLinkByGL (all) (Read) (Cache)
(input tqBaseCreditor.tiInvControlGLProfile_ID, (GlProfileId)
input viCompanyId, (CompanyId)
output dataset tqProfileLinkByGL) in BProfile >
find first tqProfileLinkByGL where
tqProfileLinkByGL.tiProfile_ID = tqBaseCreditor.tiInvControlGLProfile_ID 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(vcCandoCContrGLProfCodeFilter1, tqProfileLinkByGL.tcGLCode) or
vcCandoCContrGLCodeFilter1 = "":U or
vcCandoCContrGLCodeFilter1 = "?":U or
vcCandoCContrGLCodeFilter1 = ?))
then assign vlControlGLIsValid = true
vcInvControlGLCode = tqProfileLinkByGL.tcGLCode
vcInvControlGLDescription = tqProfileLinkByGL.tcGLDescription.
/*get GLCode for CnControlGLProfileCode*/
<Q-6 run ProfileLinkByGL (all) (Read) (Cache)
(input tqBaseCreditor.tiCnControlGLProfile_ID, (GlProfileId)
input viCompanyId, (CompanyId)
output dataset tqProfileLinkByGL) in BProfile >
find first tqProfileLinkByGL where
tqProfileLinkByGL.tiProfile_ID = tqBaseCreditor.tiCnControlGLProfile_ID 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(vcCandoCContrGLProfCodeFilter1, 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 ((tqBaseCreditor.tcCInvoiceType <> {&INVOICETYPE-CREDITNOTE} and
vcInvControlGLCode <> "":U and
vcInvControlGLCode <> "?":U and
vcInvControlGLCode <> ?) or
(tqBaseCreditor.tcCInvoiceType = {&INVOICETYPE-CREDITNOTE} and
vcCnControlGLCode <> "":U and
vcCnControlGLCode <> "?":U and
vcCnControlGLCode <> ?))
then next.
/*filter out all BaseCreditor data based on related CInvoiceMovements, PostingLines*/
/*only CInvoiceMovements of Type MOVEMENT allowed*/
assign vcCInvoiceMovementType = {&MOVEMENTTYPE-MOVEMENT}.
<Q-7 run CInvoiceMovement (all) (Read) (NoCache)
(input tqBaseCreditor.tiCInvoice_ID, (Cinvoice_ID)
input vcCInvoiceMovementType, (CInvoiceMovementType)
input ?, (ToPeriod)
input viCompanyId, (CompanyId)
output dataset tqCInvoiceMovement) in BCreditorReport >
/*exclude CInvoiceMovements (Postings) belonging to the selected period, meaning
only Postings with the higher period are allowed*/
/*Calculates sums of movements in 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 vdSumPostingLineBalanceCC = 0
vdSumPostingLineBalanceLC = 0
vdSumPostingLineBalanceTC = 0.
for each tqCInvoiceMovement where
tqCInvoiceMovement.tiCInvoice_ID = tqBaseCreditor.tiCInvoice_ID and
tqCInvoiceMovement.tcCInvoiceMovementType = vcCInvoiceMovementType and
tqCInvoiceMovement.tiPeriodYear >= viToAccYearFilter1 and
tqCInvoiceMovement.tiPeriodPeriod > viToAccPeriodFilter1:
/*for reporting currency = TC only movements with the selected currency allowed*/
if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC} and
vcCurrencyCodeFilter1 <> "":U and
vcCurrencyCodeFilter1 <> "?":U and
vcCurrencyCodeFilter1 <> ? and
vcCurrencyCodeFilter1 <> tqCInvoiceMovement.tcCurrencyCode
then next.
/*no unknown (?) values in LC, CC and TC fields expected/allowed*/
assign vdSumPostingLineBalanceCC = vdSumPostingLineBalanceCC +
(tqCInvoiceMovement.tdPostingLineDebitCC - tqCInvoiceMovement.tdPostingLineCreditCC)
vdSumPostingLineBalanceLC = vdSumPostingLineBalanceLC +
(tqCInvoiceMovement.tdPostingLineDebitLC - tqCInvoiceMovement.tdPostingLineCreditLC)
vdSumPostingLineBalanceTC = vdSumPostingLineBalanceTC +
(tqCInvoiceMovement.tdPostingLineDebitTC - tqCInvoiceMovement.tdPostingLineCreditTC).
end.
/*TODO calculate PrepaymentStatus - now based on LC values only
regardless the reporting currency*/
if tqBaseCreditor.tlCInvoiceIsOpen = false and
vdSumPostingLineBalanceLC = 0
then assign vcPrepaymentStatus = {&PREPAYMENTSTATUS-ALLOCBEFORE}
vcPrepaymentStatusTr = {&PREPAYMENTSTATUS-ALLOCBEFORE-TR}.
if tqBaseCreditor.tlCInvoiceIsOpen = true and
vdSumPostingLineBalanceLC = 0
then assign vcPrepaymentStatus = {&PREPAYMENTSTATUS-UNALLOC}
vcPrepaymentStatusTr = {&PREPAYMENTSTATUS-UNALLOC-TR}.
if tqBaseCreditor.tlCInvoiceIsOpen = true and
vdSumPostingLineBalanceLC <> 0
then assign vcPrepaymentStatus = {&PREPAYMENTSTATUS-PARTALLOC}
vcPrepaymentStatusTr = {&PREPAYMENTSTATUS-PARTALLOC-TR}.
if tqBaseCreditor.tlCInvoiceIsOpen = false and
vdSumPostingLineBalanceLC <> 0
then assign vcPrepaymentStatus = {&PREPAYMENTSTATUS-ALLOCAFTER}
vcPrepaymentStatusTr = {&PREPAYMENTSTATUS-ALLOCAFTER-TR}.
/*TODO exclude prepayments if Unallocated only chosen
from UC it is not clear which prepayments should be excluded*/
if vlUnallocatedOnlyFilter1 and
vcPrepaymentStatus <> {&PREPAYMENTSTATUS-UNALLOC}
then next.
/*in here all invalid recods were filter out and we can create a report record*/
create tqCreditorPrepayments.
buffer-copy tqBaseCreditor to tqCreditorPrepayments.
/*GL info assign*/
assign tqCreditorPrepayments.tcInvControlGLCode = vcInvControlGLCode
tqCreditorPrepayments.tcInvControlGLDescription = vcInvControlGLDescription
tqCreditorPrepayments.tcCnControlGLCode = vcCnControlGLCode
tqCreditorPrepayments.tcCnControlGLDescription = vcCnControlGLDescription
/*CnControlGL for CInvoice of type "CreditNote" otherwise InvControlGL*/
tqCreditorPrepayments.tcControlGLCode =
if tqCreditorPrepayments.tcCInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLCode
else vcInvControlGLCode
tqCreditorPrepayments.tcControlGLDescription =
if tqCreditorPrepayments.tcCInvoiceType = {&INVOICETYPE-CREDITNOTE}
then vcCnControlGLDescription
else vcInvControlGLDescription
/*calculate Originals*/
vdCInvoiceOriginalCC = tqCreditorPrepayments.tdCInvoiceOriginalDebitCC - tqCreditorPrepayments.tdCInvoiceOriginalDebitCC
vdCInvoiceOriginalLC = tqCreditorPrepayments.tdCInvoiceOriginalDebitLC - tqCreditorPrepayments.tdCInvoiceOriginalDebitLC
vdCInvoiceOriginalTC = tqCreditorPrepayments.tdCInvoiceOriginalDebitTC - tqCreditorPrepayments.tdCInvoiceOriginalDebitTC
/*calculate PrepaymentAllocated values*/
tqCreditorPrepayments.tdPrepaymentAllocatedCC = vdCInvoiceOriginalCC -
tqCreditorPrepayments.tdCInvoiceBalanceCC + vdSumPostingLineBalanceCC
tqCreditorPrepayments.tdPrepaymentAllocatedLC = vdCInvoiceOriginalLC -
tqCreditorPrepayments.tdCInvoiceBalanceLC + vdSumPostingLineBalanceLC
tqCreditorPrepayments.tdPrepaymentAllocatedTC = vdCInvoiceOriginalTC -
tqCreditorPrepayments.tdCInvoiceBalanceTC + vdSumPostingLineBalanceTC
/*calculate PrepaymentUnallocated values*/
tqCreditorPrepayments.tdPrepaymentUnallocatedCC = vdCInvoiceOriginalCC -
tqCreditorPrepayments.tdPrepaymentAllocatedCC
tqCreditorPrepayments.tdPrepaymentUnallocatedLC = vdCInvoiceOriginalLC -
tqCreditorPrepayments.tdPrepaymentAllocatedLC
tqCreditorPrepayments.tdPrepaymentUnallocatedTC = vdCInvoiceOriginalTC -
tqCreditorPrepayments.tdPrepaymentAllocatedTC
tqCreditorPrepayments.tcPrepaymentStatus = vcPrepaymentStatusTr.
end.
/*Stop queries*/
<Q-8 run ProfileLinkByGL (Stop) in BProfile >
if can-find(first tqCreditorPrepayments)
then do:
create tqCreditorPrepayments.
assign tqCreditorPrepayments.tcReportingInfo = vcReportingCurrencyFilter1.
end.