project QadFinancials > class BBillReport > method BillReportDInvoice

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


icLanguageCodeinputcharacter
tFilterinputtemp-table
dcrBillReportDInvoiceoutputdataset
oiReturnStatusoutputintegerReturn 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.