API query CInvoiceByID

Description

This query lists all CInvoice records based on creditor invoice id.

query filter (table tFilter)

tcBusinessFieldName	tcDataType	tcOperator
iiCompanyId	i
iiCInvoiceID	i	=
tCInvoice.CInvoiceBalanceCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceBalanceDebitTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceCCRate	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceCCScale	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceDate	t	=,>=,>,<>,<=,<
tCInvoice.CInvoiceExchangeRate	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceIsInitialStatus	l	=,<>
tCInvoice.CInvoiceIsOpen	l	=,<>
tCInvoice.CInvoiceOriginalCreditCC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalCreditLC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitCC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitLC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceOriginalDebitTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoicePostingYear	i	=,>=,>,<>,<=,<
tCInvoice.CInvoiceRateScale	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceType	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCInvoice.CInvoiceVatBaseCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceVatBaseDebitTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceVatCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceVatDebitTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceVoucher	i	=,>=,>,<>,<=,<
tCInvoice.CInvoiceWHTTotAmtCreditTC	d	=,>=,>,<>,<=,<
tCInvoice.CInvoiceWHTTotAmtDebitTC	d	=,>=,>,<>,<=,<
tCostCentre.CostCentreCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tCurrency.CurrencyCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tDivision.DivisionCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tJournal.JournalCode	c	=,>=,>,<>,<=,<,begins,can-do,matches
tPeriod.PeriodPeriod	i	=,>=,>,<>,<=,<
tPeriod.PeriodYear	i	=,>=,>,<>,<=,<
tProject.ProjectCode	c	=,>=,>,<>,<=,<,begins,can-do,matches

Parameters (internal)

iiCompanyId	integer	Company id
iiCInvoiceID	integer	Cinvoice ID

query condition

each CInvoice where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = iiCInvoiceID

first CostCentre (conditional-join) where

CostCentre.SharedSet_Id = vi_COSTCENTRE_sharedset(iiCompanyId) AND
CostCentre.CostCentre_ID = CInvoice.CostCentre_ID AND

first Currency (inner-join) where

Currency.Currency_ID = CInvoice.CInvoiceCurrency_ID AND

first Division (outer-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = CInvoice.Division_ID AND

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

first Project (conditional-join) where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.Project_ID = CInvoice.Project_ID AND

query resultset tqCInvoiceByID

field name	data type	db field	description
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tdCInvoiceBalanceCreditTC	decimal	CInvoice.CInvoiceBalanceCreditTC	TC Balance Amount CR. This field displays the sum of the credit amounts of all posting lines in transaction currency.
tdCInvoiceBalanceDebitTC	decimal	CInvoice.CInvoiceBalanceDebitTC	TC Balance Amount DR. This field displays the sum of the debit amounts of all posting lines in transaction currency.
tdCInvoiceCCRate	decimal	CInvoice.CInvoiceCCRate	SC Rate. The statutory exchange rate used by the invoice. The SC Invoice Amount is calculated based on the SC rate.
tdCInvoiceCCScale	decimal	CInvoice.CInvoiceCCScale	Scale Factor. This field indicates the scale factor applying to the SC rate in use for foreign currency payments.
tiCInvoiceCurrency_ID	integer	CInvoice.CInvoiceCurrency_ID	Currency Code
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.
tlCInvoiceIsInitialStatus	logical	CInvoice.CInvoiceIsInitialStatus	Initial Status. This field indicates if the invoice is in initial status.
	api annotation:		This field defaults from the Initial Status field of the Invoice status code.
tlCInvoiceIsOpen	logical	CInvoice.CInvoiceIsOpen	Open.This field indicates if the invoice has been completely paid. This field is updated automatically when complete payment is confirmed. This field is read-only.
	api annotation:		PartialUpdate = yes - defaults to true
tdCInvoiceOriginalCreditCC	decimal	CInvoice.CInvoiceOriginalCreditCC	SC Invoice Amount (CR). This field displays the credit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalCreditLC	decimal	CInvoice.CInvoiceOriginalCreditLC	BC Invoice Amount(CR). This field displays the credit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(CR) and the Exchange rate.
tdCInvoiceOriginalCreditTC	decimal	CInvoice.CInvoiceOriginalCreditTC	TC Invoice Amount CR. This field displays the credit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the BC Invoice Amount remains the same.
tdCInvoiceOriginalDebitCC	decimal	CInvoice.CInvoiceOriginalDebitCC	SC Invoice Amount (DR). This field displays the debit total invoice amount in the statutory currency. This field is calculated by the system using the TC Invoice Amount(CR) and the SC Rate.
tdCInvoiceOriginalDebitLC	decimal	CInvoice.CInvoiceOriginalDebitLC	BC Invoice Amount(DR). This field displays the debit total invoice amount in the base currency. This field is calculated by the system using the TC Invoice Amount(DR) and the Exchange rate.
tdCInvoiceOriginalDebitTC	decimal	CInvoice.CInvoiceOriginalDebitTC	TC AP Amount. This field displays the debit total invoice amount in the transaction currency. If you modify this field, the system automatically recalculates the exchange rate to ensure that the TC Invoice Amount remains the same.
tiCInvoicePostingYear	integer	CInvoice.CInvoicePostingYear	Year.This field indicates the accounting year for the invoice. If you modify these fields, the posting and tax dates are changed correspondingly.
	api annotation:		This field defaults to the accounting year associated with the posting date.
tdCInvoiceRateScale	decimal	CInvoice.CInvoiceRateScale	Scale Factor. This field indicates the scale factor applying to the exchange rate in use for foreign currency payments.
tcCInvoiceType	character	CInvoice.CInvoiceType	Invoice Type. This field identifies the invoice type. The value can be Invoice Correction and Credit Note Correction type only when the appropriate daybook types have already been defined.
tdCInvoiceVatBaseCreditTC	decimal	CInvoice.CInvoiceVatBaseCreditTC	TC Base Amount CR. This field displays the credit base amount in the transaction currency. This field is calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatBaseDebitTC	decimal	CInvoice.CInvoiceVatBaseDebitTC	TC Base Amount DR. This field displays the debit base amount in the transaction currency. This field is calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatCreditTC	decimal	CInvoice.CInvoiceVatCreditTC	TC Tax Amount CR. This field displays the credit tax amount (TC) calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
tdCInvoiceVatDebitTC	decimal	CInvoice.CInvoiceVatDebitTC	TC Tax Amount DR. This field displays the debit tax amount (TC) calculated by the system using the total invoice amount (TC) and the applicable tax rate code.
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.
tdCInvoiceWHTTotAmtCreditTC	decimal	CInvoice.CInvoiceWHTTotAmtCreditTC	TC WHT Total Amount (CR)
tdCInvoiceWHTTotAmtDebitTC	decimal	CInvoice.CInvoiceWHTTotAmtDebitTC	TC WHT Total Amount (DR)
tiCompany_ID	integer	CInvoice.Company_ID	Link to Company
tcCostCentreCode	character	CostCentre.CostCentreCode	Cost Center: A code that identifies the cost center.
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tiJournal_ID	integer	CInvoice.Journal_ID	Daybook Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tiPeriod_ID	integer	CInvoice.Period_ID	Link to Period
tiPeriodPeriod	integer	Period.PeriodPeriod	GL Period
tiPeriodYear	integer	Period.PeriodYear	GL Calendar Year
tcProjectCode	character	Project.ProjectCode	Project
tiTransactionEndCountry_ID	integer	CInvoice.TransactionEndCountry_ID	Country Code
tiTransactionStartCountry_ID	integer	CInvoice.TransactionStartCountry_ID	Country Code
CInvoiceCustom	character	calculated	custom fields
CostCentreCustom	character	calculated	custom fields
CurrencyCustom	character	calculated	custom fields
DivisionCustom	character	calculated	custom fields
JournalCustom	character	calculated	custom fields
PeriodCustom	character	calculated	custom fields
ProjectCustom	character	calculated	custom fields
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(CInvoice),rowid(CostCentre),rowid(Currency),rowid(Division),rowid(Journal),rowid(Period),rowid(Project)

Internal usage

QadFinancials

method BAPMatching.PostSave
method BCInvoice.CheckCInvoiceForPayment
method BCInvoice.CreateCInvoiceMovementsCrossCy
method BCInvoice.GetInvoiceNumber
method BCInvoiceAPMatching.CreateCInvoiceAPMatchingAllInOneTran
method BCInvoiceAPMatching.CreateCInvoiceAPMatchingOneInOneTran
method BCInvoiceJournalEntry.ApiStdMaintainTTV01
method BPaymentSelection.AdditionalUpdatesUnconfirm

Sample code: how to call this query through RPCRequestService (QXtend Inbound)

define temp-table ttContext no-undo
    field propertyQualifier as character
    field propertyName as character
    field propertyValue as character
    index entityContext is primary unique
        propertyQualifier
        propertyName
    index propertyQualifier
        propertyQualifier.

define dataset dsContext for ttContext.

define variable vhContextDS as handle no-undo.
define variable vhExceptionDS as handle no-undo.
define variable vhServer as handle no-undo.
define variable vhInputDS as handle no-undo.
define variable vhInputOutputDS as handle no-undo.
define variable vhOutputDS as handle no-undo.
define variable vhParameter as handle no-undo.

/* Create context */
create ttContext.
assign ttContext.propertyName = "programName"
       ttContext.propertyValue = "BCInvoice".
create ttContext.
assign ttContext.propertyName = "methodName"
       ttContext.propertyValue = "CInvoiceByID".
create ttContext.
assign ttContext.propertyName = "applicationId"
       ttContext.propertyValue = "fin".
create ttContext.
assign ttContext.propertyName = "entity"
       ttContext.propertyValue = "1000".
create ttContext.
assign ttContext.propertyName = "userName"
       ttContext.propertyValue = "mfg".
create ttContext.
assign ttContext.propertyName = "password"
       ttContext.propertyValue = "".

/* Create input dataset */
create dataset vhInputDS.
vhInputDS:read-xmlschema("file", "xml/bcinvoice.cinvoicebyid.i.xsd", ?).
vhParameter = vhInputDS:get-buffer-handle("tParameterI").
vhParameter:buffer-create().

assign vhParameter::icRange = "A"
       vhParameter::icRowid = ""
       vhParameter::iiRownum = 0
       vhParameter::iiNumber = 5 /* Number of records to read */
       vhParameter::icSortColumns = ""
       vhParameter::ilCountOnly = false
       vhParameter::ilForwardRead = true
       vhParameter::iiMaximumBrowseRecordsToCount = 0.

vhParameter = vhInputDS:get-buffer-handle("tFilter").
vhParameter:buffer-create().

assign vhParameter::<field-name-1> = <field-value-1>
       vhParameter::<field-name-2> = <field-value-2>
       ...

/* Connect the AppServer */
create server vhServer.
vhServer:connect("-URL <appserver-url>").

if not vhServer:connected()
then do:
    message "Could not connect AppServer" view-as alert-box error title "Error".
    return.
end.

/* Run */
assign vhContextDS = dataset dsContext:handle.

run program/rpcrequestservice.p on vhServer
    (input-output dataset-handle vhContextDS by-reference,
           output dataset-handle vhExceptionDS,
     input        dataset-handle vhInputDS by-reference,
     input-output dataset-handle vhInputOutputDS by-reference,
           output dataset-handle vhOutputDS).

/* Handle output however you want, in this example, we dump it to xml */
if valid-handle(vhExceptionDS)
then vhExceptionDS:write-xml("file", "Exceptions.xml", true).

if valid-handle(vhOutputDS)
then vhOutputDS:write-xml("file", "Output.xml", true).

/* Cleanup */
vhServer:disconnect().
assign vhServer = ?.

if valid-handle(vhInputDS)
then delete object vhInputDS.

if valid-handle(vhOutputDS)
then delete object vhOutputDS.

if valid-handle(vhExceptionDS)
then delete object vhExceptionDS.