project QadFinancials > class BAPMatching > method GetPendingVouchersLogChargesQuery
Description
Submethod of GetPendingVouchersLogCharges
Parameters
| icAPMatchingTcRowid | input | character | icAPMatchingTcRowid: Rowid (converted to character) of the matching. This corresponds with field tAPMatching.tc_rowid. |
| icCreditorCode | input | character | |
| ilIncludeEmptyCreditors | input | logical | |
| icCurrencyCode | input | character | |
| idCInvoiceTCLCRate | input | decimal | |
| idCInvoiceTCLCScale | input | decimal | |
| idCInvoiceTCCCRate | input | decimal | |
| idCInvoiceTCCCScale | input | decimal | |
| idCInvoiceTotalAmountTC | input | decimal | |
| idCInvoiceARRecTaxTC | input | decimal | |
| idCInvoiceARNRecTaxTC | input | decimal | |
| itTransactionDateFrom | input | date | |
| itTransactionDateTill | input | date | |
| itTransactionDate | input | date | |
| itTransactionDetailDateFrom | input | date | |
| itTransactionDetailDateTill | input | date | |
| itTransactionDetailDate | input | date | |
| icExternalReferenceFrom | input | character | |
| icExternalReferenceTo | input | character | |
| icExternalReference | input | character | |
| icInternalReferenceFrom | input | character | |
| icInternalReferenceTo | input | character | |
| icInternalReference | input | character | |
| icShipToFrom | input | character | |
| icShipToTo | input | character | |
| icShipTo | input | character | |
| icItemNumberFrom | input | character | |
| icItemNumberTo | input | character | |
| icItemNumber | input | character | |
| icLogisticChargeFrom | input | character | |
| icLogisticChargeTo | input | character | |
| icLogisticCharge | input | character | |
| icShipFromFrom | input | character | |
| icShipFromTo | input | character | |
| icShipFrom | input | character | |
| ilAutoSelect | input | logical | |
| icOrderFrom | input | character | |
| icOrderTo | input | character | |
| icOrder | input | character | |
| ilCallGTM | input | logical | ilCallGTM: Set to true in case you want this method to call GTM to recalculate the taxes. If false, then the taxes will just be taken from the pending-voucher |
| iiApiPvoId | input | integer | |
| iiApiPvodLineId | input | integer | Pending voucher detail ID |
| idApiMatchAmtTC | input | decimal | Match amount of the logistic charge. This parameter is valid only , when PvoID and PvoLineID paramters are supplied. It means, this procedure processes only one pending voucher record. |
| ilApiIsPvodFinished | input | logical | |
| oiReturnStatus | output | integer | Return status of the method. |
Internal usage
QadFinancials
program code (program7/bapmatching.p)
/* =================================================================================================================== */
/* GetPendingVouchersNoLogChargesQuery: submethod of GetPendingVouchersNoLogCharges that holds the query-call to query */
/* BMfgPendingVoucher:PendingVoucherForMatchingNoLC(). This metthod can be used for customisations and thus provides */
/* customers to take actions to extend the content of tqPendingVoucherForMatchingLC */
/* =================================================================================================================== */
/* ===================================================================================================================== */
/* Query the correct pending-vouchers */
/* Run once a query that returns all kind of information on the Pvod and related details */
/* If CompanyID = 0 then take all companies, not only current one */
/* Note: the result-set of the query can contain multiple records for a single pvod_det as taxes are joined in the query */
/* ===================================================================================================================== */
<Q-64 run PendingVoucherForMatchingLC (all) (Read) (NoCache)
(input itTransactionDateFrom, (TransactionDateFrom)
input itTransactionDateTill, (TransactionDateTill)
input itTransactionDate, (TransactionDate)
input icExternalReferenceFrom, (ExternalReferenceFrom)
input icExternalReferenceTo, (ExternalReferenceTo)
input icExternalReference, (ExternalReference)
input icInternalReferenceFrom, (InternalReferenceFrom)
input icInternalReferenceTo, (InternalReferenceTo)
input icInternalReference, (InternalReference)
input icShipToFrom, (ShipToFrom)
input icShipToTo, (ShipToTo)
input icShipTo, (ShipTo)
input icCurrencyCode, (CurrencyCode)
input vcDomainCode, (DomainCode)
input ?, (CompanyId)
input icCreditorCode, (CreditorCode)
input icShipFromFrom, (ShipFromFrom)
input icShipFromTo, (ShipFromTo)
input icShipFrom, (ShipFrom)
input icLogisticChargeFrom, (LogisticChargeFrom)
input icLogisticChargeTo, (LogisticChargeTo)
input icLogisticCharge, (LogisticCharge)
input icOrderFrom, (OrderFrom)
input icOrderTo, (OrderTo)
input icOrder, (Order)
input iiApiPvoId, (PvoID)
input iiApiPvodLineId, (PvodLineId)
input itTransactionDetailDateTill, (TransactionDetailDateTill)
input itTransactionDetailDateFrom, (TransactionDetailDateFrom)
input itTransactionDetailDate, (TransactionDetailDate)
output dataset tqPendingVoucherForMatchingLC) in BMfgPendingVoucher>
/* Check if the ERP version is QAD2014 or greater. If yes, the set vlVersion2014 to true */
if viMfgProMajorVersionBAPM > 3 /* Meaning later than eB3 */ or
(viMfgProMajorVersionBAPM = 3 /* Meaning eB3 */and
viMfgProMinorVersionBAPM >= 14 /* Meaning SP14 */)
then do:
assign vlVersion2014 = true.
end.
else do:
find first tqPendingVoucherForMatchingLC where
( num-entries(tqPendingVoucherForMatchingLC.tcpvo_external_ref,'::':U) = 9 ) and
tqPendingVoucherForMatchingLC.tcpvo_lc_charge <> '':U and
tqPendingVoucherForMatchingLC.tcpvo_internal_ref_type = '13':U
no-lock no-error.
if not available tqPendingVoucherForMatchingLC then assign vlVersion2014 = false.
else vlVersion2014 = true.
end.
/* When running APMatching via the normal UI (idApiMatchAmtTC empty) then we will simply not return any Pending-vouchers that relate to Fiscal Receipts */
if idApiMatchAmtTC = 0 or
idApiMatchAmtTC = ?
then do :
<Q-76 run POAccountingControlByDomainV01 (all) (Read) (NoCache)
(input vcDomainCode, (Domain)
output dataset tqPOAccountingControlByDomainV01) in BMfgPOAccountingControl>
find first tqPOAccountingControlByDomainV01 where
tqPOAccountingControlByDomainV01.tcpoc_domain = vcDomainCode
no-lock no-error.
if available tqPOAccountingControlByDomainV01 and
tqPOAccountingControlByDomainV01.tlpoc_fiscal_confirm = true
then do:
for each tqPendingVoucherForMatchingLC where
( num-entries(tqPendingVoucherForMatchingLC.tcpvo_external_ref,',':U) = 2 and vlVersion2014 = false) or
( num-entries(tqPendingVoucherForMatchingLC.tcpvo_external_ref,'::':U) = 9 and vlVersion2014 = true):
assign vcLegalDocumentNbr = entry(1,tqPendingVoucherForMatchingLC.tcpvo_external_ref)
vcDateFormat = session:date-format
session:date-format = 'ymd'
vtEffectiveDate = if vlVersion2014 = false then date(entry(2,tqPendingVoucherForMatchingLC.tcpvo_external_ref))
else tqPendingVoucherForMatchingLC.ttpvo_eff_date
session:date-format = vcDateFormat.
<Q-9 run LegalDocumentByLegalDocNbr (all) (Read) (NoCache)
(input vcDomainCode, (DomainCode)
input vcLegalDocumentNbr, (LegalDocNbr)
input vtEffectiveDate, (EffIssueDate)
input ?, (Supplier)
output dataset tqLegalDocumentByLegalDocNbr) in BMFGLegalDocument>
find first tqLegalDocumentByLegalDocNbr where
tqLegalDocumentByLegalDocNbr.tclgd_domain = vcDomainCode and
tqLegalDocumentByLegalDocNbr.tclgd_nbr = vcLegalDocumentNbr and
tqLegalDocumentByLegalDocNbr.ttlgd_effdate = vtEffectiveDate and
(tqLegalDocumentByLegalDocNbr.tclgd_shipfrom = tqPendingVoucherForMatchingLC.tcpvo_supplier or
tqLegalDocumentByLegalDocNbr.tclgd_shipfrom = tqPendingVoucherForMatchingLC.tcpvo_shipto )and
tqLegalDocumentByLegalDocNbr.tclgdd_order = tqPendingVoucherForMatchingLC.tcpvo_order and
tqLegalDocumentByLegalDocNbr.tilgdd_order_line = tqPendingVoucherForMatchingLC.tipvod_order_line
no-lock no-error.
if available tqLegalDocumentByLegalDocNbr
then do:
delete tqPendingVoucherForMatchingLC.
end. /* if available tqLegalDocumentByLegalDocNbr */
end. /*for each tqPendingVoucherForMatchingLC where */
end. /* if available tqPOAccountingControlByDomainV01 */
end. /* if idApiMatchAmtTC = 0 or */
/* ====================================================================================================== */
/* Specific actions required in case a PVO is originated from the Fiscal Receiving */
/* Pending vouchers originated from Fiscal Receiving can be distinguished by this: */
/* pvo_mstr.pvo_external_ref consists of two comma-sep entries (where the first */
/* entry is the LegalDocumentNumber and the second one is the transaction/effective-date) */
/* For PVO's originated from the Fiscal Receiving this query will only return the taxes belonging to the */
/* POReceipt that preceeded the Fiscal Receipt. */
/* ====================================================================================================== */
/* Note that we will Pending-vouchers that relate to Fiscal Receipts can only be processed by APMatching */
/* when the APMatching is used by ERS (this means idApiMatchAmtTC is filled). */
/* When running APMatching via the normal UI (idApiMatchAmtTC empty) then we will simply not return any */
/* Pending-vouchers that relate to Fiscal Receipts */
/* ====================================================================================================== */
/* Sample: We only want the tx2d_det records that actually belong to the pvod, so we delete the ones that */
/* do not really belong to the pvod */
/* Sample Data: */
/* tctx2d_ref: Either just "R0000125", */
/* Either "lorre,X11-LD3,11/10/12" with the date in format YY MM DD (12 October) */
/* pvo_external_ref: "X11-LD3,11/10/12" with the date in format YY MM DD (12 October) */
/* pvo_supplier: lorre */
/* ====================================================================================================== */
/* We need to manualy define a buffer on tqPendingVoucherForMatchingLC as CB doesn't support this */
define buffer btqPendingVoucherForMatchingLC for tqPendingVoucherForMatchingLC.
/* Go through all pending-voucher-details that relate to fiscal-receipts */
for each tqPendingVoucherForMatchingLC where
( num-entries(tqPendingVoucherForMatchingLC.tcpvo_external_ref,',':U) = 2 and vlVersion2014 = false) or
( num-entries(tqPendingVoucherForMatchingLC.tcpvo_external_ref,'::':U) = 9 and vlVersion2014 = true)
break by tqPendingVoucherForMatchingLC.tcpvod_domain
by tqPendingVoucherForMatchingLC.tipvod_id
by tqPendingVoucherForMatchingLC.tipvod_id_line :
/* Last-of is needed as tqPendingVoucherForMatchingLC holds a records for every tax-code linked to the pending-voucher-detail */
if last-of (tqPendingVoucherForMatchingLC.tipvod_id_line)
then do :
/* Retrieve all details of Fiscal receipt, these details are used for taxes */
assign vcLegalDocumentDomain = tqPendingVoucherForMatchingLC.tcpvo_domain
vcLegalDocumentNbr = entry(1,tqPendingVoucherForMatchingLC.tcpvo_internal_ref,":":U)
vtEffectiveDate = date(integer(substring(entry(5,tqPendingVoucherForMatchingLC.tcpvo_internal_ref,":":U), 7, 2, "CHARACTER")),
integer(substring(entry(5,tqPendingVoucherForMatchingLC.tcpvo_internal_ref,":":U), 9, 2, "CHARACTER")),
integer(substring(entry(5,tqPendingVoucherForMatchingLC.tcpvo_internal_ref,":":U), 1, 4, "CHARACTER")))
vcLegalDocumentSupplier = entry(3,tqPendingVoucherForMatchingLC.tcpvo_internal_ref,":":U).
if not(can-find(first tLgddDetLCForAPMBAPM where
tLgddDetLCForAPMBAPM.tclgd_domain = vcLegalDocumentDomain and
tLgddDetLCForAPMBAPM.tclgd_nbr = vcLegalDocumentNbr and
tLgddDetLCForAPMBAPM.ttlgd_effdate = vtEffectiveDate and
tLgddDetLCForAPMBAPM.tclgd_shipfrom = vcLegalDocumentSupplier))
then do:
if viBMFGLegalDocumentID = 0 or
viBMFGLegalDocumentID = ?
then do:
<I-21 {bFcStartAndOpenInstance
&ADD-TO-TRANSACTION = "no"
&CLASS = "BMFGLegalDocument"}>
end.
else if not valid-handle(vhBMFGLegalDocumentInst)
then do:
<I-31 {bFcOpenInstance
&CLASS = "BMFGLegalDocument"}>
end.
<M-27 run GetLegalDocumentDetForAPMatching
(input vcLegalDocumentDomain (icLgdDomain),
input vcLegalDocumentNbr (icLgdNbr),
input vcLegalDocumentSupplier (icLgdSuppl),
input vtEffectiveDate (itLgdEffDate),
input ? (icInvSuppl),
input ? (iiLgddLine),
input ? (icLgddDocType),
input ? (icLgddOrder),
input ? (iiLgddOrderLine),
output tLgddDetMatForAPMBAPM (tLgddDetMatForAPM),
output tLgddDetLCForAPMBAPM (tLgddDetLCForAPM),
output tLgddDetLCDistForAPMBAPM (tLgddDetLCDistForAPM),
output tLgddDetTaxForAPMBAPM (tLgddDetTaxForAPM),
output viFcReturnSuper (oiReturnStatus)) in BMFGLegalDocument>
if viFcReturnSuper < 0 or viFcReturnSuper > 0 and oiReturnStatus = 0 then assign oiReturnStatus = viFcReturnSuper.
if oiReturnStatus < 0 then leave.
end. /* if not(can-find first tLgddDetLCForAPMBAPM where */
/* Indicate we have not yet found a tqTaxDetailByKey-record by setting vlFoundtqTaxDetailByKey to false */
assign vlFoundtqTaxDetailByKey = false.
/* Legal document amounts are alwayes entered in base currency, that means also all taxes are also stored in base currency. */
/* Because the invoice can be created in different currency, we have to get exchange rate between base currency and the */
/* Currency of the legal document */
assign vdLegalDocumentExchangeRate = tqPendingVoucherForMatchingLC.tdpvod_ex_rate / tqPendingVoucherForMatchingLC.tdpvod_ex_rate2.
/* Create new records in the output temp-table tPendingVoucherForMatchingLC with correct taxes */
for each tLgddDetLCForAPMBAPM where
tLgddDetLCForAPMBAPM.tclgd_domain = vcLegalDocumentDomain and
tLgddDetLCForAPMBAPM.tclgd_nbr = vcLegalDocumentNbr and
tLgddDetLCForAPMBAPM.ttlgd_effdate = vtEffectiveDate and
tLgddDetLCForAPMBAPM.tclgd_shipfrom = vcLegalDocumentSupplier,
each tLgddDetLCDistForAPMBAPM where
tLgddDetLCDistForAPMBAPM.tc_ParentRowid = tLgddDetLCForAPMBAPM.tc_Rowid and
tLgddDetLCDistForAPMBAPM.tcPvoDomain = tqPendingVoucherForMatchingLC.tcpvo_domain and
tLgddDetLCDistForAPMBAPM.tiPvoId = tqPendingVoucherForMatchingLC.tipvo_id and
tLgddDetLCDistForAPMBAPM.tiPvodIdLine = tqPendingVoucherForMatchingLC.tipvod_id_line:
/* Store some extra data about the receipt of material of the logistic charge */
<Q-44 run LegalDocDetailForAPMatching (all) (Read) (Cache)
(input tLgddDetLCDistForAPMBAPM.tdmat_oid_lgdd_det, (OidLegalDocumentDetail)
output dataset tqLegalDocDetailForAPMatching) in BMFGLegalDocument>
find first tqLegalDocDetailForAPMatching where
tqLegalDocDetailForAPMatching.tdoid_lgdd_det = tLgddDetLCDistForAPMBAPM.tdmat_oid_lgdd_det
no-error.
if available tqLegalDocDetailForAPMatching
then assign tqPendingVoucherForMatchingLC.tcpvod_part = tqLegalDocDetailForAPMatching.tcpvod_part
tqPendingVoucherForMatchingLC.tcprh_type = tqLegalDocDetailForAPMatching.tcprh_type
tqPendingVoucherForMatchingLC.tcprh_um = tqLegalDocDetailForAPMatching.tcprh_um
tqPendingVoucherForMatchingLC.tdprh_ps_qty = tqLegalDocDetailForAPMatching.tdprh_ps_qty.
for each tLgddDetTaxForAPMBAPM where
tLgddDetTaxForAPMBAPM.tc_ParentRowid = tLgddDetLCDistForAPMBAPM.tc_Rowid:
/* Copy the current tqPendingVoucherForMatchingLC record into a new record and copy and overwrite the tax-info in the new records with the info of tqTaxDetailByKey */
create btqPendingVoucherForMatchingLC.
buffer-copy tqPendingVoucherForMatchingLC to btqPendingVoucherForMatchingLC.
buffer-copy tLgddDetTaxForAPMBAPM except tc_ParentRowid to btqPendingVoucherForMatchingLC.
/* Convert all amounts from base currency into transaction currency of the document */
if vdLegalDocumentExchangeRate <> 1
then assign btqPendingVoucherForMatchingLC.tdpvod_accrued_amt = <M-54 RoundAmount
(input btqPendingVoucherForMatchingLC.tdpvod_accrued_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdpvod_vouchered_amt = <M-79 RoundAmount
(input btqPendingVoucherForMatchingLC.tdpvod_vouchered_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_abs_ret_amt = <M-15 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_abs_ret_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_cur_abs_ret_amt = <M-22 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_cur_abs_ret_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_cur_recov_amt = <M-3 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_cur_recov_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_cur_tax_amt = <M-18 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_cur_tax_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_tax_amt = <M-51 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_tax_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_taxable_amt = <M-57 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_taxable_amt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_totamt = <M-26 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_totamt * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>
btqPendingVoucherForMatchingLC.tdtx2d_tottax = <M-94 RoundAmount
(input btqPendingVoucherForMatchingLC.tdtx2d_tottax * vdLegalDocumentExchangeRate (idUnroundedAmount),
input ? (iiCurrencyID),
input tqPendingVoucherForMatchingLC.tcpvo_curr (icCurrencyCode)) in BAPMatching>.
/* Indicate we have found a tqTaxDetailByKey-record by setting vlFoundtqTaxDetailByKey to true */
assign vlFoundtqTaxDetailByKey = true.
end. /* for each tLgddDetTaxForAPMBAPM where */
end. /* for each tLgddDetLCForAPMBAPM where */
/* If we have created new tqPendingVoucherForMatchingLC-records in the previous loop on tqTaxDetailByKey */
/* then we will now remove the original tqPendingVoucherForMatchingLC-records that hold the incorrect taxes */
/* We can distinguish the old ones from the newly created ones as the old ones haven't got 3 entries in field tctctx2d_ref */
if vlFoundtqTaxDetailByKey = true
then do :
for each btqPendingVoucherForMatchingLC where
btqPendingVoucherForMatchingLC.tcpvod_domain = tqPendingVoucherForMatchingLC.tcpvod_domain and
btqPendingVoucherForMatchingLC.tipvod_id = tqPendingVoucherForMatchingLC.tipvod_id and
btqPendingVoucherForMatchingLC.tipvod_id_line = tqPendingVoucherForMatchingLC.tipvod_id_line and
rowid(btqPendingVoucherForMatchingLC) <> rowid(tqPendingVoucherForMatchingLC) and
num-entries(btqPendingVoucherForMatchingLC.tctx2d_ref,",":U) <> 3:
delete btqPendingVoucherForMatchingLC.
end. /* for each btqPendingVoucherForMatchingLC where */
delete tqPendingVoucherForMatchingLC.
end. /* if vlFoundtqTaxDetailByKey = true */
end. /* if last-of (tqPendingVoucherForMatchingLC.tipvod_id_line) */
end. /* for each tqPendingVoucherForMatchingLC where*/