report procedure
Parameters
Internal usage
unused
program code (program7/bcreditorreport.p)
/*Sorting is done on CristalReport side*/
empty temp-table tqCreditorAccountsHistory.
<M-8 run GetReportLabels
(input 'CreditorAccountsHistory':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-4 run SetDataItemsBasedOnFilterTT (output viFcReturnSuper (oiReturnStatus)) in BCreditorReport>
if vlCheckHistoryFilterCR = ? or
vlCheckHistoryFilterCR
then do:
<Q-9 assign vlFcQueryRecordsAvailable = QCInvoiceMovementAll (NoCache)
(input viCompanyId, (CompanyId)
input ?, (DaemonId)) in BQCInvoiceMovement >
if vlFcQueryRecordsAvailable = true or
vlFcQueryRecordsAvailable = ?
then do:
<M-10 run SetMessage
(input #T-11'Run the Balance daemon to update the supplier balances.':70(65167)t-11# (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input '':U (icRowid),
input 'QADFIN-3463':U (icFcMsgNumber),
input #T-12'There are still transactions in the Balance daemon queue. Run to Balance daemon to process the queue or data inconsistencies could occur.':150(65166)t-12# (icFcExplanation),
input '':U (icFcIdentification),
input '':U (icFcContext),
output oiReturnStatus (oiReturnStatus)) in BCreditorReport>
assign oiReturnStatus = -1.
return.
end.
end.
/* eliminate Creditors within Creditor Balance criteria */
/*filter out records according to
ReportingCurrencyFilter, CurrencyCodeFilter, CreditorBalance info*/
<Q-3 run CreditorBalance (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcCandoCreditorCodeFilter1, (CanDoCreditor)
input viCurrencyIdFilter1, (CurrencyId)
input ?, (CreditorId)
input vcFromCreditorCodeFilter1, (FromCreditor)
input vcToCreditorCodeFilter1, (ToCreditor)
output dataset tqCreditorBalance) in BCreditorReport >
/*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 vdSumCreditorBalanceCC = 0
vdSumCreditorBalanceLC = 0
vdSumCreditorBalanceTC = 0.
for each tqCreditorBalance break by tqCreditorBalance.tiCreditor_ID :
/*eliminate ? values*/
assign vdSumCreditorBalanceCC = vdSumCreditorBalanceCC + if tqCreditorBalance.tdCBalanceCC = ? then 0 else tqCreditorBalance.tdCBalanceCC
vdSumCreditorBalanceLC = vdSumCreditorBalanceLC + if tqCreditorBalance.tdCBalanceLC = ? then 0 else tqCreditorBalance.tdCBalanceLC
vdSumCreditorBalanceTC = vdSumCreditorBalanceTC + if tqCreditorBalance.tdCBalanceTC = ? then 0 else tqCreditorBalance.tdCBalanceTC.
if last-of(tqCreditorBalance.tiCreditor_ID)
then do:
/*eliminate Creditor if his balance doesn't fit the filter criteria*/
/*UC says
when currency selected CreditorBalance in selected currency
otherwise summ of Creditorbalances in LC
not found in selected currency => not valid
> 0 when <selection> Creditor Balance = D
< 0 when <selection> Creditor Balance = C
<> 0 when <selection> Creditor Balance = B
otherwise valid (A or empty)*/
if /*CC*/
(vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
vdSumCreditorBalanceCC > 0) or
(vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT} and
vdSumCreditorBalanceCC < 0) or
(vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
vcCreditorBalanceFilter1 = {&CREDITORBALANCE-BALANCE} and
vdSumCreditorBalanceCC = 0) or
(vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC} and
vcCreditorBalanceFilter1 <> {&CREDITORBALANCE-ALL} and
vdSumCreditorBalanceCC = ?) or
/*LC*/
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
vdSumCreditorBalanceLC > 0) or
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT} and
vdSumCreditorBalanceLC < 0) or
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-BALANCE} and
vdSumCreditorBalanceLC = 0) or
(vcCreditorBalanceFilter1 <> {&CREDITORBALANCE-ALL} and
vdSumCreditorBalanceLC = ?) or
/*TC*/
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-CREDIT} and
vdSumCreditorBalanceTC > 0) or
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-DEBIT} and
vdSumCreditorBalanceTC < 0) or
(vcCreditorBalanceFilter1 = {&CREDITORBALANCE-BALANCE} and
vdSumCreditorBalanceTC = 0) or
(vcCreditorBalanceFilter1 <> {&CREDITORBALANCE-ALL} and
vdSumCreditorBalanceTC = ?)
then do:
/* Actions for Creditor who did not match our criteria */
end.
else do:
create tAvailableCreditors.
assign tAvailableCreditors.tiCreditor_ID = tqCreditorBalance.tiCreditor_ID.
end.
assign vdSumCreditorBalanceCC = 0
vdSumCreditorBalanceLC = 0
vdSumCreditorBalanceTC = 0.
end. /* if last-of */
end. /* for each Creditor balance */
/* 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 viToAccPeriodFilter1 = viToAccYearFilter1 * 100 + viToAccPeriodFilter1
viFromAccPeriodFilter1 = viFromAccYearFilter1 * 100 + viFromAccPeriodFilter1.
/* ================ */
/* Call base querry */
/* ================ */
<Q-5 run BaseCreditorAccounts (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input vcCandoCreditorCodeFilter1, (CandoCreditorCode)
input vcCandoBRCodeFilter1, (CandoBRCode)
input vcFromBRCodeFilter1, (FromBRCode)
input vcFromCreditorCodeFilter1, (FromCreditorCode)
input viToAccPeriodFilter1, (ToAccYearPeriod)
input vcToBRCodeFilter1, (ToBRCode)
input vcToCreditorCodeFilter1, (ToCreditorCode)
input viFromAccPeriodFilter1, (FromAccYearPeriod)
output dataset tqBaseCreditorAccounts) in BCreditorReport >
FOR EACH tqBaseCreditorAccounts by tqBaseCreditorAccounts.tcCreditorCode:
find first tAvailableCreditors where tAvailableCreditors.tiCreditor_ID = tqBaseCreditorAccounts.tiCreditor_ID no-error.
if not available tAvailableCreditors then next.
if tqBaseCreditorAccounts.tdCHistNettoInvoiceTC = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceTC = 0.
if tqBaseCreditorAccounts.tdCHistNettoCredNoteTC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteTC = 0.
if tqBaseCreditorAccounts.tdCHistGrossInvoiceTC = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceTC = 0.
if tqBaseCreditorAccounts.tdCHistGrossCredNoteTC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteTC = 0.
if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-LC} or
vcReportingCurrencyFilter1 = {&CURRENCYTYPE-TC}
then do:
if tqBaseCreditorAccounts.tdCHistNettoInvoiceLC = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceLC = 0.
if tqBaseCreditorAccounts.tdCHistNettoCredNoteLC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteLC = 0.
if tqBaseCreditorAccounts.tdCHistGrossInvoiceLC = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceLC = 0.
if tqBaseCreditorAccounts.tdCHistGrossCredNoteLC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteLC = 0.
end.
else do:
if tqBaseCreditorAccounts.tdCHistNettoInvoiceCC = ? then assign tqBaseCreditorAccounts.tdCHistNettoInvoiceCC = 0.
if tqBaseCreditorAccounts.tdCHistNettoCredNoteCC = ? then assign tqBaseCreditorAccounts.tdCHistNettoCredNoteCC = 0.
if tqBaseCreditorAccounts.tdCHistGrossInvoiceCC = ? then assign tqBaseCreditorAccounts.tdCHistGrossInvoiceCC = 0.
if tqBaseCreditorAccounts.tdCHistGrossCredNoteCC = ? then assign tqBaseCreditorAccounts.tdCHistGrossCredNoteCC = 0.
end.
case vcDetailLevelFilter1:
when {&CREDITORDETAILLEVEL-PERIOD} then
create tqCreditorAccountsHistory.
when {&CREDITORDETAILLEVEL-YEAR} then
do:
if not can-find(tqCreditorAccountsHistory where
tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode and
tqCreditorAccountsHistory.tiPeriodYear = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom > 0
then truncate(tqBaseCreditorAccounts.tiCHistYearPeriodFrom / 100,0)
else 0)
then create tqCreditorAccountsHistory.
end.
when {&CREDITORDETAILLEVEL-CREDITOR} THEN
do:
if not can-find(tqCreditorAccountsHistory where
tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode )
then create tqCreditorAccountsHistory.
end.
when {&CREDITORDETAILLEVEL-CURRENCY} THEN
do:
if not can-find(tqCreditorAccountsHistory where
tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode and
tqCreditorAccountsHistory.tcCurrencyCode = tqBaseCreditorAccounts.tcCurrencyCode)
then create tqCreditorAccountsHistory.
end.
END CASE.
assign tqCreditorAccountsHistory.tcBusinessRelationName1 = tqBaseCreditorAccounts.tcBusinessRelationName1
tqCreditorAccountsHistory.tcBusinessRelationName2 = tqBaseCreditorAccounts.tcBusinessRelationName2
tqCreditorAccountsHistory.tcBusinessRelationCode = tqBaseCreditorAccounts.tcBusinessRelationCode
/* BTS 4876 : Assign the BusRel ID */
tqCreditorAccountsHistory.tiBusinessRelation_ID = tqBaseCreditorAccounts.tiBusinessRelation_ID
/* BTS 4876 : Assign the BusRel ID */
tqCreditorAccountsHistory.tcCurrencyCode = tqBaseCreditorAccounts.tcCurrencyCode
tqCreditorAccountsHistory.tcCreditorCode = tqBaseCreditorAccounts.tcCreditorCode
tqCreditorAccountsHistory.tdCreditorCreditNotesTC = tqCreditorAccountsHistory.tdCreditorCreditNotesTC + tqBaseCreditorAccounts.tdCHistNettoCredNoteTC
tqCreditorAccountsHistory.tdGrossMovementsTC = tqCreditorAccountsHistory.tdGrossMovementsTC + tqBaseCreditorAccounts.tdCHistGrossInvoiceTC - tqBaseCreditorAccounts.tdCHistGrossCredNoteTC
tqCreditorAccountsHistory.tdCreditorInvoicesTC = tqCreditorAccountsHistory.tdCreditorInvoicesTC + tqBaseCreditorAccounts.tdCHistNettoInvoiceTC
tqCreditorAccountsHistory.tdNetMovementsTC = tqCreditorAccountsHistory.tdNetMovementsTC + tqBaseCreditorAccounts.tdCHistNettoInvoiceTC - tqBaseCreditorAccounts.tdCHistNettoCredNoteTC
tqCreditorAccountsHistory.tdCreditorCreditNotesRC =
tqCreditorAccountsHistory.tdCreditorCreditNotesRC + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
then tqBaseCreditorAccounts.tdCHistGrossCredNoteCC
else tqBaseCreditorAccounts.tdCHistGrossCredNoteLC
tqCreditorAccountsHistory.tdCreditorInvoicesRC =
tqCreditorAccountsHistory.tdCreditorInvoicesRC + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
then tqBaseCreditorAccounts.tdCHistGrossInvoiceCC
else tqBaseCreditorAccounts.tdCHistGrossInvoiceLC
tqCreditorAccountsHistory.tdNetMovementsRC =
tqCreditorAccountsHistory.tdNetMovementsRC + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
then (tqBaseCreditorAccounts.tdCHistNettoInvoiceCC - tqBaseCreditorAccounts.tdCHistNettoCredNoteCC)
else (tqBaseCreditorAccounts.tdCHistNettoInvoiceLC - tqBaseCreditorAccounts.tdCHistNettoCredNoteLC)
tqCreditorAccountsHistory.tdGrossMovementsRC =
tqCreditorAccountsHistory.tdGrossMovementsRC + if vcReportingCurrencyFilter1 = {&CURRENCYTYPE-CC}
then (tqBaseCreditorAccounts.tdCHistGrossInvoiceCC - tqBaseCreditorAccounts.tdCHistGrossCredNoteCC)
else (tqBaseCreditorAccounts.tdCHistGrossInvoiceLC - tqBaseCreditorAccounts.tdCHistGrossCredNoteLC)
tqCreditorAccountsHistory.tiPeriodPeriod = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom > 0
then tqBaseCreditorAccounts.tiCHistYearPeriodFrom mod 100 else 0
tqCreditorAccountsHistory.tiPeriodYear = if tqBaseCreditorAccounts.tiCHistYearPeriodFrom > 0
then truncate(tqBaseCreditorAccounts.tiCHistYearPeriodFrom / 100,0) else 0
tqCreditorAccountsHistory.tcSortingByFilter1 = if vcDetailLevelFilter1 = ? or vcDetailLevelFilter1 = '':U
then {&CREDITORDETAILLEVEL-CREDITOR}
else vcDetailLevelFilter1.
end.
/*Calculates some extra information*/
for each tqCreditorAccountsHistory break by tqCreditorAccountsHistory.tcCreditorCode:
/*Calculates Address information for the BusinessRelation_ID*/
assign vhFcComponent = ?
vcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE}.
if first-of (tqCreditorAccountsHistory.tcCreditorCode)
then do:
<M-6 run GetAddressWithType
(input tqCreditorAccountsHistory.tiBusinessRelation_ID (iiBusinessRelationId),
input tqCreditorAccountsHistory.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 tqCreditorAccountsHistory.tcAddressCity = tAddressQueryRef1.tcAddressCity
tqCreditorAccountsHistory.tcAddressFax = tAddressQueryRef1.tcAddressFax
tqCreditorAccountsHistory.tcAddressStreet1 = tAddressQueryRef1.tcAddressStreet1
tqCreditorAccountsHistory.tcAddressStreet2 = tAddressQueryRef1.tcAddressStreet2
tqCreditorAccountsHistory.tcAddressTelephone= tAddressQueryRef1.tcAddressTelephone
tqCreditorAccountsHistory.tcAddressZip = tAddressQueryRef1.tcAddressZip.
if available tqContactByAddress
then assign tqCreditorAccountsHistory.tcContactEmail = tqContactByAddress.tcContactEmail
tqCreditorAccountsHistory.tcContactName = tqContactByAddress.tcContactName.
end.