business logic query CInvoiceForMatchingVariance

Parameters

icFromCreditorCode	character
icToCreditorCode	character
icCompanyCode	character
icFromLogisticCharge	character
iiCompanyId	integer
icFromPurchaseOrder	character
icToPurchaseOrder	character
icFromItem	character
icToItem	character
itFromInvoiceDate	date
itToInvoiceDate	date
icReceiptSite	character
icCurrencyCode	character
icToLogisticCharge	character
ilIsLgCharge	logical
itFromInvoicePostingDt	date
itToInvoicePostingDt	date
icOrderType	character
iiDomainId	integer

query condition

each CInvoice where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoiceDate >= itFromInvoiceDate AND
CInvoice.CInvoiceDate <= itToInvoiceDate AND
CInvoice.CInvoicePostingDate >= itFromInvoicePostingDt AND
CInvoice.CInvoicePostingDate <= itToInvoicePostingDt AND
CInvoice.CInvoiceIsLogisticMatching = true

each APMatching (inner-join) where

APMatching.CInvoice_ID = CInvoice.CInvoice_ID AND
APMatching.APMatchingStatus = {&APMATCHINGSTATUS-FINISHED} AND
APMatching.Company_ID = CInvoice.Company_ID

each APMatchingLn (inner-join) where

APMatchingLn.APMatching_ID = APMatching.APMatching_ID AND
APMatchingLn.APMatchingLnPvodOrder >= icFromPurchaseOrder AND
APMatchingLn.APMatchingLnPvodOrder <= icToPurchaseOrder AND
APMatchingLn.APMatchingLnPvodItem >= icFromItem AND
APMatchingLn.APMatchingLnPvodItem <= icToItem AND
APMatchingLn.APMatchingLnPvodLgCharge >= icFromLogisticCharge AND
APMatchingLn.APMatchingLnPvodLgCharge <= icToLogisticCharge AND
APMatchingLn.APMatchingLnPvodSite can-do icReceiptSite AND
APMatchingLn.APMatchingLnPvodIsLgCharge = ilIsLgCharge AND
APMatchingLn.APMatchingLnPvodOrderType can-do icOrderType

first Currency (inner-join) where

Currency.Currency_ID = APMatching.Currency_ID AND
Currency.CurrencyCode can-do icCurrencyCode

first Company (inner-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = CInvoice.Company_ID AND
Company.CompanyCode can-do icCompanyCode AND
Company.Domain_ID = iiDomainId

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND
Creditor.CreditorCode >= icFromCreditorCode AND
Creditor.CreditorCode <= icToCreditorCode

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = CInvoice.Journal_ID AND

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = CInvoice.Period_ID AND

query resultset tqCInvoiceForMatchingVariance

field name	data type	db field	description
ttAPMatchingDate	date	APMatching.APMatchingDate	Date
tiAPMatchingLn_ID	integer	APMatchingLn.APMatchingLn_ID	Record ID
tlAPMatchingLnIsPvodFinished	logical	APMatchingLn.APMatchingLnIsPvodFinished	Finished
tdAPMatchingLnMatchQty	decimal	APMatchingLn.APMatchingLnMatchQty	Matched Quantity
tdAPMatchingLnMatchUnitPrice	decimal	APMatchingLn.APMatchingLnMatchUnitPrice	Matched Unit Price
tdAPMatchingLnPvodExchRate	decimal	APMatchingLn.APMatchingLnPvodExchRate	Exchange Rate
tdAPMatchingLnPvodExchRate2	decimal	APMatchingLn.APMatchingLnPvodExchRate2	Exchange Rate 2
tlAPMatchingLnPvodIsLgCharge	logical	APMatchingLn.APMatchingLnPvodIsLgCharge	Log Charge
tcAPMatchingLnPvodItem	character	APMatchingLn.APMatchingLnPvodItem	Item
tcAPMatchingLnPvodItemDescr1	character	APMatchingLn.APMatchingLnPvodItemDescr1	Item Description
tcAPMatchingLnPvodItemType	character	APMatchingLn.APMatchingLnPvodItemType	Possible values: Normal, Memo or Sub-contract
tcAPMatchingLnPvodLgCharge	character	APMatchingLn.APMatchingLnPvodLgCharge	Logistic Charge
tdAPMatchingLnPvodOpenQty	decimal	APMatchingLn.APMatchingLnPvodOpenQty	Open Quantity
tcAPMatchingLnPvodOrder	character	APMatchingLn.APMatchingLnPvodOrder	Order
tiAPMatchingLnPvodOrderLnNbr	integer	APMatchingLn.APMatchingLnPvodOrderLnNbr	Order Line Nbr
tdAPMatchingLnPvodReceiptQty	decimal	APMatchingLn.APMatchingLnPvodReceiptQty	Receipt Quantity
tcAPMatchingLnPvodSite	character	APMatchingLn.APMatchingLnPvodSite	APMatchingLnPvodSite
tcAPMatchingLnPvodUoM	character	APMatchingLn.APMatchingLnPvodUoM	GL Unit of Measure
tdAPMatchingLnReceiptAmtLC	decimal	APMatchingLn.APMatchingLnReceiptAmtLC	BC Receipt Amt
tdAPMatchingLnVarExchRateLC	decimal	APMatchingLn.APMatchingLnVarExchRateLC	BC Exch Rate Variance
tdAPMatchingLnVarRateLC	decimal	APMatchingLn.APMatchingLnVarRateLC	BC Rate Variance
tdAPMatchingLnVarRateTC	decimal	APMatchingLn.APMatchingLnVarRateTC	TC Rate Variance
tdAPMatchingLnVarUsageLC	decimal	APMatchingLn.APMatchingLnVarUsageLC	BC Usage Variance
tdAPMatchingLnVarUsageTC	decimal	APMatchingLn.APMatchingLnVarUsageTC	TC Usage Variance
tcAPMatchingStatus	character	APMatching.APMatchingStatus	Status
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
ttCInvoiceDate	date	CInvoice.CInvoiceDate	Invoice Date. This field indicates the invoice creation date. The system uses this field with the credit terms to calculate due date and discount date.
	api annotation:		This field defaults from the system date.
tdCInvoiceExchangeRate	decimal	CInvoice.CInvoiceExchangeRate	Exchange Rate. The exchange rate used by the invoice. The BC Invoice Amount is calculated based on the exchange rate. If user modify the TC Invoice Amount, the BC invoice amount is automatically adjusted.
tlCInvoiceIsLogisticMatching	logical	CInvoice.CInvoiceIsLogisticMatching	Receiver Matching.This field indicates If the Receiver Matching function is enabled. This field is calculated by the system using the invoice status code.
tcCinvoiceJournal	character	Journal.JournalCode	A daybook code (maximum eight characters).
ttCInvoicePostingDate	date	CInvoice.CInvoicePostingDate	Posting Date. This filed indicates the date on which the invoice is to be posted.
	api annotation:		This field defaults from the invoice creation date.
tdCInvoiceRateScale	decimal	CInvoice.CInvoiceRateScale	Scale Factor. This field indicates the scale factor applying to the exchange rate in use for foreign currency payments.
tcCInvoiceReference	character	CInvoice.CInvoiceReference	Reference. An alphanumeric reference to help identify the invoice in the system. This reference is typically the ID number of the invoice received from the supplier.
tiCInvoiceVoucher	integer	CInvoice.CInvoiceVoucher	Voucher. This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is changed (after transfer), the voucher is cleared. Voucher must be unique in one GL period. When the user input is zero, the system automatically assign a value not used yet for this field.
tcCompanyCode	character	Company.CompanyCode	Entity Code
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcMatchingType	character	calculated
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tiPvodLineID	integer	APMatchingLn.PvodLineID	Pinv Line ID
tcPvoDomain	character	APMatchingLn.PvoDomain	Domain
tiPvoID	integer	APMatchingLn.PvoID	Pinv ID
tiPvoPostingLine_ID	integer	APMatchingLn.PvoPostingLine_ID	Link to PostingLine
tiVarExchRatePostingLine_ID	integer	APMatchingLn.VarExchRatePostingLine_ID	Exch Rate Posting Line
tiVarRatePostingLine_ID	integer	APMatchingLn.VarRatePostingLine_ID	Link to PostingLine
tiVarUsagePostingLine_ID	integer	APMatchingLn.VarUsagePostingLine_ID	Link to PostingLine

Internal usage

QadFinancials

method BAPMatchingReport.MatchingVariance
method BAPMatchingReport.MatchingVarianceForLC
dataset BAPMatchingReport.tGetMatchingVariance