project QadFinancials > class BBill > method GetDInvoiceFromBillQuery

Description

Setup the search condition in tFilter table.


Parameters


itInvoiceDateFrominputdate
itInvoiceDateToinputdate
icDebtorListinputcharacter
icEmployeeCodeinputcharacter
icCurrencyCodeinputcharacter
icInvoiceRefFrominputcharacter
icInvoiceRefToinputcharacter
ilIsAllEntitiesinputlogical
icActivityCodeinputcharacter
iiBill_IDinputinteger
iiDInvoice_IDinputinteger
tInnerQueryDinvoiceForBilloutputtemp-table
oiReturnStatusoutputintegerReturn status of the method.


Internal usage


QadFinancials
method BBill.GetToBillDInvoice


program code (program4/bbill.p)

empty temp-table tFilter.
empty temp-table tInnerQueryDinvoiceForBill.

if icActivityCode <> 'Create' and
   icActivityCode <> 'SetBillDate'
then do:
    /* Bill ID */
    if iiBill_ID <> 0
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'tDInvoice.Bill_ID':U
               tFilter.tcDataType = 'i':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = string(iiBill_ID).
    end.

    /* DInvoice ID */
    if iiDInvoice_ID <> 0
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'iiDInvoice_ID':U
               tFilter.tcDataType = 'i':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = string(iiDInvoice_ID).
    end.
end.
else do:
    /* DebtorList */
    if icDebtorList <> ? and 
       icDebtorList <> ''
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'tDebtor.DebtorCode':U
               tFilter.tcDataType = 'c':U
               tFilter.tcOperator = 'can-do':U
               tFilter.tcParameterValue = icDebtorList.
    end.
    /* Collector */
    if icEmployeeCode <> ? and
        icEmployeeCode <> ''
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'tEmployee.EmployeeCode':U
               tFilter.tcDataType = 'c':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = icEmployeeCode.
    end.
    /* Currency */
    if icCurrencyCode <> ? and
       icCurrencyCode <> ''
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'tCurrency.CurrencyCode':U
               tFilter.tcDataType = 'c':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = icCurrencyCode.
    end.
    
    /* Invoice Reference */
    if (icInvoiceRefFrom <> ? and icInvoiceRefFrom <> '') or
       (icInvoiceRefTo   <> ? and icInvoiceRefTo <> '')
    then do:
        if icInvoiceRefFrom = icInvoiceRefTo
        then do:
            create tFilter.
            assign tFilter.tcBusinessFieldName = 'tDinvoice.DInvoiceDescription':U
                   tFilter.tcDataType = 'c':U
                   tFilter.tcOperator = '=':U
                   tFilter.tcParameterValue = icInvoiceRefFrom.
        end.
        else do:
            if icInvoiceRefFrom <> '' and icInvoiceRefFrom <> ?
            then do:
                create tFilter.
                assign tFilter.tcBusinessFieldName = 'tDinvoice.DInvoiceDescription':U
                       tFilter.tcDataType = 'c':U
                       tFilter.tcOperator = '>=':U
                       tFilter.tcParameterValue = icInvoiceRefFrom.
            end.
            if icInvoiceRefTo <> '' and icInvoiceRefTo <> ?
            then do:
                create tFilter.
                assign tFilter.tcBusinessFieldName = 'tDinvoice.DInvoiceDescription':U
                       tFilter.tcDataType = 'c':U
                       tFilter.tcOperator = '<=':U
                       tFilter.tcParameterValue = icInvoiceRefTo.
            end.
        end.
    end.
    
    /* Invoice Ref Date From */
    if itInvoiceDateFrom <> ? 
    then do:
        assign vctempDate = string(year(itInvoiceDateFrom)) + string(month(itInvoiceDateFrom), "99") + string(day(itInvoiceDateFrom), "99").
        create tFilter.
            assign tFilter.tcBusinessFieldName = 'itDInvoiceRefDateFrom':U
                   tFilter.tcDataType = 't':U
                   tFilter.tcOperator = '=':U
                   tFilter.tcParameterValue = vctempDate.
    end.
    /* Invoice Ref Date To */
    if itInvoiceDateTo <> ? 
    then do:
        assign vctempDate = string(year(itInvoiceDateTo)) + string(month(itInvoiceDateTo), "99") + string(day(itInvoiceDateTo), "99").
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'itDInvoiceRefDateTo':U
               tFilter.tcDataType = 't':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = vctempDate.
    end.
    
    /* Is All Entities */
    if ilIsAllEntities = no
    then do:
        create tFilter.
        assign tFilter.tcBusinessFieldName = 'iiCompanyId':U
               tFilter.tcDataType = 'i':U
               tFilter.tcOperator = '=':U
               tFilter.tcParameterValue = string(viCompanyId).
    end.

    /* Bill ID */
    create tFilter.
    assign tFilter.tcBusinessFieldName = 'tDinvoice.Bill_ID':U
           tFilter.tcDataType = 'i':U
           tFilter.tcOperator = '=':U
           tFilter.tcParameterValue = string(0).


end.



if icActivityCode = 'Create' or icActivityCode = 'SetBillDate' then
do:
    /* DInvoice is open
    When the invoice was paid, we still need to see the invoice on Bill View/Moidfy/Delete
    So for other activity, we do not need to add the isopen condition */
    create tFilter.
    assign tFilter.tcBusinessFieldName = 'tDinvoice.DInvoiceIsOpen':U
           tFilter.tcDataType = 'l':U
           tFilter.tcOperator = '=':U
           tFilter.tcParameterValue = string(yes).
end.



/* DInvoice Type*/
create tFilter.
assign tFilter.tcBusinessFieldName = 'tDinvoice.DinvoiceType ':U
       tFilter.tcDataType = 'c':U
       tFilter.tcOperator = '<>':U
       tFilter.tcParameterValue = {&INVOICETYPE-PREPAYMENT}.

create tFilter.
assign tFilter.tcBusinessFieldName = 'tDinvoice.DinvoiceType ':U
       tFilter.tcDataType = 'c':U
       tFilter.tcOperator = '<>':U
       tFilter.tcParameterValue = {&INVOICETYPE-ADJUSTMENT}.

create tFilter.
assign tFilter.tcBusinessFieldName = 'tDinvoice.DinvoiceType ':U
       tFilter.tcDataType = 'c':U
       tFilter.tcOperator = '<>':U
       tFilter.tcParameterValue = {&INVOICETYPE-DEDUCTION}.

/* The API Query will retrieve the data from PostingHist table*/
<Q-71 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 tInnerQueryDinvoiceForBill (ozResultsetDS), 
    output dataset tFcMessages (ozFcMessagesDS), 
    output viFcReturnSuper (oiReturnStatus))>