report procedure
Description
This is the main method of Bill report print. First, we will get Bill by filter parameters. Then we will get reference information of this bill ,such as Debtor information, Employee information, Bank information and so on. And in the core logic, we will create two dataset, one is tqBillReportDInvoice which is for the invoice part, another one is tBillReportPayment which is for the payment part. At last, if current print activity is ‘Confirm Print’, confirmed date and opening balance will be saved into Bill table.
Parameters
| icLanguageCode | input | character | |
| tFilter | input | temp-table | |
| dcrBillReportDInvoice | output | dataset | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
unused
program code (program7/bbillreport.p)
empty temp-table tBillReportPayment.
empty temp-table tqBillReportDInvoice.
empty temp-table tBillConfirmedTable.
assign vlPrintItemDetail = no.
<M-52 run GetReportLabels
(input 'BillReportDInvoice':U (icReportName),
input icLanguageCode (icLanguageCode),
input tFilter (tFilter),
output tqHeader (tqHeader),
output tqFilter (tqFilter),
output tqText (tqText),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
if oiReturnStatus <> 0
then return.
<M-86 run SetDataItemsBasesOnFilterTT (output oiReturnStatus (oiReturnStatus)) in BBillReport>
if oiReturnStatus <> 0
then return.
/* Get Bill */
<Q-91 run GetBillByPrintFilter (all) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input ?, (CurrencyCode)
input vcDebtorCodeFilter, (DebtorCode)
input viBillYearFilter, (BillYear)
input vtBillDateFromFilter, (BillDateFrom)
input vtBillDateToFilter, (BillDateTo)
input viBillNumberFromFilter, (BillNumberFrom)
input viBillNumberToFilter, (BillNumberTo)
input viEmployeeIDFilter, (EmployeeID)
input vcBillStatusFilter, (BillStatus)
output dataset tqGetBillByPrintFilter) in BBill>
for each tqGetBillByPrintFilter break by tqGetBillByPrintFilter.tiDebtor_ID
by tqGetBillByPrintFilter.tiCurrency_ID:
if first-of(tqGetBillByPrintFilter.tiDebtor_ID) and first-of(tqGetBillByPrintFilter.tiCurrency_ID) then
do:
/*Use Append Mode */
<Q-47 run DInvoiceMovementIdForBill (first) (Append) (NoCache)
(input ?, (CompanyId)
input tqGetBillByPrintFilter.tiDebtor_ID, (Debtor_ID)
input tqGetBillByPrintFilter.tiCurrency_ID, (Currency_ID)
output dataset tqDInvoiceMovementIdForBill) in BDInvoice>
end.
end.
assign viBillReport_ID = 0.
/* Loop for Bill */
for each tqGetBillByPrintFilter no-lock:
/* Clear tFilter table */
empty temp-table tFilter.
empty temp-table tDinvoiceForBillFilterDS.
/* Initialize total amount */
assign vdNonBillPay = 0
vdbillpay = 0
vdSalesAmt = 0
vdSalesTax = 0
vdSalesTotalAmt = 0
vdRestoreBillPayment = 0
viBillReport_ID = viBillReport_ID + 1
viCurHighWaterMark = 0
vcBillCollector = "":U
vlIsPreBillInitial = false
viFinalDInvoiceMovementID = 0
vlIsFirstBill = false
viBillCompany_ID = viCompanyId
vcCompanyAddress = '':U
vcCompanyName = '':U
vcContactInfo = '':U.
create tFilter.
assign tFilter.tcBusinessFieldName = 'tDInvoice.Bill_ID':U
tFilter.tcDataType = 'i':U
tFilter.tcOperator = '=':U
tFilter.tcParameterValue = string(tqGetBillByPrintFilter.tiBill_ID).
assign viCurCurrency_ID = tqGetBillByPrintFilter.tiCurrency_ID
viCurDebtor_ID = tqGetBillByPrintFilter.tiDebtor_ID.
/* Get Debtor Address and other info */
<M-64 run GetBillReportTitleInfo
(input tqGetBillByPrintFilter.tiBill_ID (iiBill_ID),
input tqGetBillByPrintFilter.tiEmployee_ID (iiEmployee_ID),
output vcBusinessRelationName (ocBusinessRelationName),
output vcBusinessRelationID (ocBusinessRelationID),
output vcBusinessRelationAddr1 (ocBusinessRelationAddr1),
output vcBusinessRelationAddr2 (ocBusinessRelationAddr2),
output vcPaymentConditionCode (ocPaymentConditionCode),
output vtBillDate (otBillDate),
output viBillYear (oiBillYear),
output viCurHighWaterMark (oiCurHighWaterMark),
output vcCurBillStatus (ocCurBillStatus),
output vcBankAccountCode (ocBankAccountCode),
output vcBankAccountType (ocBankAccountType),
output vcBankNumber (ocBankNumber),
output vcBankNumberBranch (ocBankNumberBranch),
output vcBankNumberSwiftCode (ocBankNumberSwiftCode),
output vcBankName (ocBankName),
output vcBillCollector (ocBillCollector),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
/* The API Query will retrieve the data from PostingHist table*/
/* Get DInvoice info */
<Q-54 run api/bbillquery/dinvoiceforbill.p
(input ? (icApiLogin),
input ? (icApiPassword),
input ? (icApiExtra),
input viSessionID (iiApiSessionId),
input 'A':U (icRange),
input ? (icRowid),
input 0 (iiRowNum),
input 0 (iiNumber),
input '':U (icSortColumns),
input false (ilCountOnly),
input yes (ilForwardRead),
input 0 (iiMaximumBrowseRecordsToCount),
input dataset tFilter (izFilterDS),
output viQueryCount (oiCount),
output vlEndofQuery (olEndOfQuery),
output dataset tDinvoiceForBillFilterDS (ozResultsetDS),
output dataset tFcMessages (ozFcMessagesDS),
output viFcReturnSuper (oiReturnStatus))>
find first tDinvoiceForBillFilterDS no-lock no-error.
if available tDinvoiceForBillFilterDS
then do:
/* Get bill invoice company */
if tDinvoiceForBillFilterDS.tiCompany_ID <> viCompanyId then
assign viBillCompany_ID = tDinvoiceForBillFilterDS.tiCompany_ID.
/* Get last invoice movement_id */
find first tqDInvoiceMovementIdForBill where
tqDInvoiceMovementIdForBill.tiDebtor_ID = tqGetBillByPrintFilter.tiDebtor_ID and
tqDInvoiceMovementIdForBill.tiCompany_ID = viCompanyId and
tqDInvoiceMovementIdForBill.tiDInvoiceCurrency_ID = tqGetBillByPrintFilter.tiCurrency_ID no-lock no-error.
if available tqDInvoiceMovementIdForBill then
assign viFinalDInvoiceMovementID = tqDInvoiceMovementIdForBill.tiDInvoiceMovement_ID.
/* Get previous bill */
<Q-35 run GetPreConfirmedBill (first) (Read) (NoCache)
(input viCompanyId, (CompanyId)
input viCurCurrency_ID, (Currency_ID)
input viCurDebtor_ID, (Debtor_ID)
input viBillYearFilter, (BillYear)
input tDinvoiceForBillFilterDS.tiBill_ID, (Bill_ID)
input viBillCompany_ID, (DInvoiceCompany_ID)
output dataset tqGetPreConfirmedBill) in BBill>
find first tqGetPreConfirmedBill no-lock no-error.
if available tqGetPreConfirmedBill
then do:
if tqGetPreConfirmedBill.tcBillStatus = {&BILLSTATUS-INITIAL}
then do:
/* Error message */
if vcPrintTypeFilter = {&BILLREPORTTYPE-CONFIRMPRINT} then do:
<M-14 run SetMessage
(input trim(#T-10'You cannot confirm the bill because there is an earlier bill with the status Initial for the same customer, currency, and entity.':255(882707162)T-10#) (icMessage),
input '':U (icArguments),
input '':U (icFieldName),
input '':U (icFieldValue),
input 'E':U (icType),
input 3 (iiSeverity),
input ? (icRowid),
input 'qadfin-798229':U (icFcMsgNumber),
input '':U (icFcExplanation),
input '':U (icFcIdentification),
input '':U (icFcContext),
output viFcReturnSuper (oiReturnStatus)) in BBillReport>
assign oiReturnStatus = -1.
return.
end.
/* This field will be used in print layout, if previous bill is initial,
so previous opening balance will show 'N/A'. */
assign vlIsPreBillInitial = true
vdPreBillBalance = 0
viPrevHighWaterMark = viFinalDInvoiceMovementID
vtPreBillDate = tqGetPreConfirmedBill.ttBillDate.
end.
else if tqGetPreConfirmedBill.tcBillStatus = {&BILLSTATUS-CONFIRM} or
tqGetPreConfirmedBill.tcBillStatus = {&BILLSTATUS-CLOSED}
then do:
assign vdPreBillBalance = tqGetPreConfirmedBill.tdBillClosingBalanceTC
viPrevHighWaterMark = tqGetPreConfirmedBill.tiDInvoiceMovement_ID
vtPreBillDate = tqGetPreConfirmedBill.ttBillDate.
end.
end.
else do:
/* Get all the open items balance */
<M-68 run GetPreBillDate
(input tDinvoiceForBillFilterDS.tiBill_ID (iiBill_Id),
output vtPreBillDate (otPreBillDate),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
<M-23 run CaculateBalanceOpenItems
(input tDinvoiceForBillFilterDS.tiDebtor_ID1 (iiDebtorId),
input tDinvoiceForBillFilterDS.tiCurrency_ID (iiCurrencyId),
input vtPreBillDate (itPreBillDate),
output vdPreBillBalance (odBalance),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
assign viPrevHighWaterMark = 0
vlIsFirstBill = true.
end.
end.
/* Get Company Info */
<Q-78 run GetCompanyInfoForBill (all) (Read) (NoCache)
(input viBillCompany_ID, (CompanyId)
output dataset tqGetCompanyInfoForBill) in BBillReport>
for each tqGetCompanyInfoForBill break by tqGetCompanyInfoForBill.tcAddressTypeCode:
if tqGetCompanyInfoForBill.tcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-HEADOFFICE} then
do:
assign vcCompanyAddress = tqGetCompanyInfoForBill.tcAddressStreet1
vcCompanyName = tqGetCompanyInfoForBill.tcBusinessRelationName1
vcContactInfo = {&ADDRESSTYPECODESYSTEM-HEADOFFICE} + ":":U + tqGetCompanyInfoForBill.tcAddressTelephone.
if tqGetCompanyInfoForBill.tcAddressCity <> ? and tqGetCompanyInfoForBill.tcAddressCity <> "" then
vcCompanyAddress = vcCompanyAddress + ", " + tqGetCompanyInfoForBill.tcAddressCity.
if tqGetCompanyInfoForBill.tcStateDescription <> ? and tqGetCompanyInfoForBill.tcStateDescription <> "" then
vcCompanyAddress = vcCompanyAddress + ", " + tqGetCompanyInfoForBill.tcStateDescription .
if tqGetCompanyInfoForBill.tcCountyDescription <> ? and tqGetCompanyInfoForBill.tcCountyDescription <> "" then
vcCompanyAddress = vcCompanyAddress + ", " + tqGetCompanyInfoForBill.tcCountyDescription.
if tqGetCompanyInfoForBill.tcCountryDescription <> ? and tqGetCompanyInfoForBill.tcCountryDescription <> "" then
vcCompanyAddress = vcCompanyAddress + ", " + tqGetCompanyInfoForBill.tcCountryDescription.
end.
if tqGetCompanyInfoForBill.tcAddressTypeCode = {&ADDRESSTYPECODESYSTEM-REMITTANCE} then
assign vcContactInfo = vcContactInfo + " ,":U + {&ADDRESSTYPECODESYSTEM-REMITTANCE} + ":":U + tqGetCompanyInfoForBill.tcAddressTelephone.
end.
/*Create Bill Report invoice section */
<M-24 run GetBillReportDInvoice
(input vdPreBillBalance (idPreBillBalance),
input viCurHighWaterMark (iiCurHighWaterMark),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
/* Set the start date of Movement as the day after Previous Bill Date */
if vtPreBillDate <> ? then
assign vtPreBillDate = vtPreBillDate + 1.
/* Find the payments for the bill invoice in previous bill period */
<Q-59 run DInvoiceMovementForBill (all) (Append) (NoCache)
(input viBillCompany_ID, (CompanyId)
input tqGetBillByPrintFilter.tiDebtor_ID, (Debtor_ID)
input ?, (PostDateFrom)
input vtPreBillDate - 1, (PostDateTo)
input tqGetBillByPrintFilter.tiCurrency_ID, (Currency_ID)
input ?, (DInvoiceMovementIDFrom)
input ?, (DInvoiceMovementIDTo)
input tqGetBillByPrintFilter.tiBill_ID, (Bill_ID)
output dataset tqDInvoiceMovementForBill) in BBillReport>
/* Find the payments for the bill period and based on the postingdate of the movement */
<Q-84 run DInvoiceMovementForBill (all) (Append) (NoCache)
(input viBillCompany_ID, (CompanyId)
input tqGetBillByPrintFilter.tiDebtor_ID, (Debtor_ID)
input vtPreBillDate, (PostDateFrom)
input vtBillDate, (PostDateTo)
input tqGetBillByPrintFilter.tiCurrency_ID, (Currency_ID)
input ?, (DInvoiceMovementIDFrom)
input ?, (DInvoiceMovementIDTo)
input ?, (Bill_ID)
output dataset tqDInvoiceMovementForBill) in BBillReport>
/* Find the payments that should have been on previous bills but where created
after the previous bill was Confirm Printed based on the HighWaterMark of the previous bill.
If current Bill is the first one, then skip this part.*/
if vlIsFirstBill <> true then
do:
<Q-89 run DInvoiceMovementForBill (all) (Append) (NoCache)
(input viBillCompany_ID, (CompanyId)
input tqGetBillByPrintFilter.tiDebtor_ID, (Debtor_ID)
input ?, (PostDateFrom)
input vtPreBillDate - 1, (PostDateTo)
input tqGetBillByPrintFilter.tiCurrency_ID, (Currency_ID)
input viPrevHighWaterMark, (DInvoiceMovementIDFrom)
input viCurHighWaterMark, (DInvoiceMovementIDTo)
input ?, (Bill_ID)
output dataset tqDInvoiceMovementForBill) in BBillReport>
end. /* if vtPreBillDate <> ? then */
empty temp-table tDInvoiceMovementForBill.
for each tqDInvoiceMovementForBill:
if not can-find (first tDInvoiceMovementForBill where
tDInvoiceMovementForBill.tiDInvoiceMovement_ID = tqDInvoiceMovementForBill.tiDInvoiceMovement_ID no-lock) then
do:
create tDInvoiceMovementForBill.
buffer-copy tqDInvoiceMovementForBill to tDInvoiceMovementForBill.
end.
end. /* for each tqDInvoiceMovementForBill */
if can-find (first tDInvoiceMovementForBill no-lock) then
do:
<M-39 run GetBillReportPayment
(input tqGetBillByPrintFilter.tiBill_ID (iiCurBillID),
input vcCurBillStatus (icCurBillStatus),
input viCurHighWaterMark (iiCurHighWaterMark),
input vlIsFirstBill (ilIsFirstBill),
input vtPreBillDate (itPreBillDate),
output oiReturnStatus (oiReturnStatus)) in BBillReport>
end.
/* Set sum fields */
for each tqBillReportDInvoice where tqBillReportDInvoice.tiBill_ID = tqGetBillByPrintFilter.tiBill_ID:
assign tqBillReportDInvoice.tdPaymentAmt = vdBillPay
tqBillReportDInvoice.tdSalesTotalAmt = vdSalesAmt /*+ vdRestoreBillPayment*/
tqBillReportDInvoice.tdSalesTax = vdSalesTax.
end.
/* Create Bill confirmed table for ConfirmPrint save */
create tBillConfirmedTable.
assign tBillConfirmedTable.tiBill_ID = tqGetBillByPrintFilter.tiBill_ID
tBillConfirmedTable.tdBillPay = vdPreBillBalance + vdSalesAmt - vdBillPay
tBillConfirmedTable.tiDInvoiceMovement_ID = viFinalDInvoiceMovementID
tBillConfirmedTable.tdPaymentAmt = vdBillPay
tBillConfirmedTable.tdSalesTotalAmt = vdSalesAmt - vdSalesTax /* without tax */
tBillConfirmedTable.tdSalesTax = vdSalesTax
tBillConfirmedTable.tdPreviousBalance = vdPreBillBalance.
/* Empty query table */
empty temp-table tqDInvoiceMovementForBill.
end. /*Loop Bill_ID*/
if can-find (first tBillConfirmedTable no-lock) and
vcPrintTypeFilter = {&BILLREPORTTYPE-CONFIRMPRINT} then
do:
<I-27 {bFcStartAndOpenInstance
&ADD-TO-TRANSACTION = "false"
&CLASS = "BBill"}>
<M-72 run ConfirmPrint
(input tBillConfirmedTable (tBillConfirmedTableForSave),
output oiReturnStatus (oiReturnStatus)) in BBill>
<I-5 {bFcCloseAndStopInstance
&CLASS = "BBill"}>
end.