business logic query PostingVatByPeriod

Description

GetPostingVatDetail

Parameters

iiCompanyId	integer	Company id
icDeliveryType	character	Delivery Type
iiFromAccPeriod	integer
iiToAccPeriod	integer	To Account Period
itFromPostingDate	date	FromPostingDate
itToPostingDate	date	ToPostingDate
itFromTaxpointDate	date
itToTaxpointDate	date
icGLTypeCode	character
icJournalCode	character	Daybook Code
icJournalTypeCode	character
icVatCode	character	VatCode
icVatInOut	character	VatInOut
icVatPeriodMark	character	VatPeriodMark
icVatTransactionType	character	VatTransactionType
iiFromVoucher	integer	FromVoucher
iiToVoucher	integer	ToVoucher

query condition

each PostingVat where

PostingVat.PostingVatTaxPointDate >= itToTaxPointDate AND
PostingVat.PostingVatTaxPointDate <= itFromTaxpointDate

first Vat (inner-join) where

Vat.Vat_ID = PostingVat.Vat_ID AND
Vat.VatCode can-do icVatCode AND
Vat.VatInOut can-do icVatInOut AND
Vat.VatTransactionType can-do icVatTransactionType AND
Vat.VatDeliveryType matches icDeliveryType

first VatPeriod (inner-join) where

VatPeriod.Company_Id = iiCompanyId AND
VatPeriod.VatPeriod_ID = PostingVat.VatPeriod_ID AND
(VatPeriod.VatPeriodYear > if iiFromVatYear = -99999 then 0 else iiFromVatYear OR
(VatPeriod.VatPeriodYear = if iiFromVatYear = -99999 then 0 else iiFromVatYear AND
VatPeriod.VatPeriodPeriod >= if iiFromVatPeriod = -99999 then 0 else iiFromVatPeriod )) AND
(VatPeriod.VatPeriodYear < if iiToVatYear = -99999 then 0 else iiToVatYear OR
(VatPeriod.VatPeriodYear = if iiToVatYear = -99999 then 0 else iiToVatYear AND
VatPeriod.VatPeriodPeriod <= if iiToVatPeriod = -99999 then 0 else iiToVatPeriod ))

first PeriodMark (inner-join) where

PeriodMark.Company_Id = iiCompanyId AND
PeriodMark.PeriodMark_ID = VatPeriod.PeriodMark_ID AND
PeriodMark.PeriodMarkCode matches icVatPeriodMark

first PostingLine (inner-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.PostingLine_ID = PostingVat.PostingLine_ID AND

first GL (inner-join) where

GL.SharedSet_Id = vi_GL_sharedset(iiCompanyId) AND
GL.GL_ID = PostingLine.GL_ID AND
GL.GLTypeCode matches icGLTypeCode

first Posting (inner-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = PostingLine.Posting_ID AND
Posting.PostingVoucher >= iiFromVoucher AND
Posting.PostingVoucher <= iiToVoucher AND
Posting.PostingDate >= itFromPostingDate AND
Posting.PostingDate <= itToPostingDate

each CInvoicePosting (conditional-join) where

CInvoicePosting.Posting_ID = Posting.Posting_ID AND

first CInvoice (conditional-join) where

CInvoice.Company_Id = iiCompanyId AND
CInvoice.CInvoice_ID = CInvoicePosting.CInvoice_ID AND

each DInvoicePosting (conditional-join) where

DInvoicePosting.Posting_ID = Posting.Posting_ID AND

first DInvoice (conditional-join) where

DInvoice.Company_Id = iiCompanyId AND
DInvoice.DInvoice_ID = DInvoicePosting.DInvoice_ID AND

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = Posting.Journal_ID AND
Journal.JournalCode can-do icJournalCode AND
Journal.JournalTypeCode can-do icJournalTypeCode

first Period (inner-join) where

Period.Company_Id = iiCompanyId AND
Period.Period_ID = Posting.Period_ID AND
Period.PeriodYearPeriod >= iiFromAccPeriod AND
Period.PeriodYearPeriod <= iiToAccPeriod

first VatRule (inner-join) where

VatRule.VatRule_ID = PostingVat.VatRule_ID AND

query resultset tqPostingVatByPeriod

field name	data type	db field	description
tiJournal_ID	integer	Journal.Journal_ID	Record ID
tiVat_ID	integer	Vat.Vat_ID	Record ID
tiCreditor_ID	integer	CInvoice.Creditor_ID	Link to Creditor
tiCTransActionStartCountry_ID	integer	CInvoice.TransactionStartCountry_ID	Country Code
tiCTransActionEndCountry_ID	integer	CInvoice.TransactionEndCountry_ID	Country Code
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.
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tiDTransActionEndCountry_ID	integer	DInvoice.TransactionEndCountry_ID	Country Code
tiDTransActionStartCountry_ID	integer	DInvoice.TransactionStartCountry_ID	Country Code
tiDebtor_ID	integer	DInvoice.Debtor_ID	Link to Debtor
tiDInvoiceVoucher	integer	DInvoice.DInvoiceVoucher	Voucher.This field displays the numeric identifier assigned to the posting. When the daybook of the journal entry is charged(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.
tiDInvoice_ID	integer	DInvoice.DInvoice_ID	Record ID
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
tcJournalDescription	character	Journal.JournalDescription	A brief description of the daybook.
	api annotation:		Value for this field defaults to null.
tcJournalTypeCode	character	Journal.JournalTypeCode	A daybook type code.
	api annotation:		Value for this field defaults to "Journal Entries".
tiPeriodYearPeriod	integer	Period.PeriodYearPeriod	GL Calendar Year/GL Period
tcVatCode	character	Vat.VatCode	Tax Code
tcVatDescription	character	Vat.VatDescription	Description
tcVatInOut	character	Vat.VatInOut	Tax In/Out
tcVatTransactionType	character	Vat.VatTransactionType	Transaction Type
tcTxuTaxUsage2	character	Vat.TxuTaxUsage	Tax Usage
tdPostingLineDebitLC	decimal	PostingLine.PostingLineDebitLC	BC Debit
	api annotation:		Partial Update = yes - it will get a default value depending on the value of PostingLineDebitTC, PostingLineExchangeRate and PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update = false, enter the correct value into this field.
tdPostingLineDebitTC	decimal	PostingLine.PostingLineDebitTC	TC Debit
tiPostingLine_ID	integer	PostingLine.PostingLine_ID	Record ID
tiPosting_ID	integer	PostingLine.Posting_ID	Link to Posting
tiCurrency_ID	integer	PostingLine.Currency_ID	Currency Code
tdPostingLineCreditLC	decimal	PostingLine.PostingLineCreditLC	BC Credit
	api annotation:		Partial Update = yes - it will get a default value depends on the value of PostingLineCreditTC, PostingLineExchangeRate & PostingLineRateScale. BC Amount = TC Amount * Exchange rate (TC/BC) * Scale Factor (TC/BC). If Partial Update is false, enter a value for this field.
tdPostingLineCreditTC	decimal	PostingLine.PostingLineCreditTC	TC Credit
tcGLTypeCode	character	GL.GLTypeCode	A code identifying the type of account.
ttPostingDate	date	Posting.PostingDate	The accounting date on which the journal entry is posted. The posting date must be a valid calendar date and must be within the GL calendar year (PostingYear) and period (PostingPeriod).
tcPostingParentText	character	Posting.PostingParentText	Contains the description field from the parent object that triggered the posting. When the posting is an invoice posting, this field contains the invoice description. When the posting is a banking entry posting, the description of the bank statement line is copied to this field, etc. Used for information purposes on reports and browses.
	api annotation:		Value for this field defaults to blank.
tiPostingVoucher	integer	Posting.PostingVoucher	Voucher is a sequential number that uniquely identifies the journal entry (within the combination of an entity and a daybook code).
	api annotation:		In create mode,enter 0 in this field - the voucher number is generated by the system. In Modify/Delete, the correct voucher number must be entered because this identifies the record.
tcTxtyTaxType	character	PostingVat.TxtyTaxType	Tax Type
tcTxuTaxUsage	character	PostingVat.TxuTaxUsage	Tax Usage. A tax usage code previously defined in Tax Usage Maintenance. Tax usage codes identify the normal use of items sold to this address. Common tax usages are retail, manufacturing, and industrialization.
	api annotation:		The value of Tax Usage defaults to the header of transactions created for this address.
tdPostingVatBaseDebitLC	decimal	PostingVat.PostingVatBaseDebitLC	BC (Base/Local Currency) Base Debit amount.
tdPostingVatBaseDebitTC	decimal	PostingVat.PostingVatBaseDebitTC	TC (Transaction Currency) Base Debit amount.
ttPostingVatTaxPointDate	date	PostingVat.PostingVatTaxPointDate	The date when this posting is taken into account in the Tax sub-adminstration.
	api annotation:		?
tiPostingVat_ID	integer	PostingVat.PostingVat_ID	Record ID
tdPostingVatBaseCreditLC	decimal	PostingVat.PostingVatBaseCreditLC	BC (Base/Local Currency) Base Credit amount.
tdPostingVatBaseCreditTC	decimal	PostingVat.PostingVatBaseCreditTC	TC (Transaction Currency) Base Credit amount.
tcPostingVatInOut	character	PostingVat.PostingVatInOut	In/Out?
	api annotation:		Aivalable value for this field: "INPUT" represents purchases, "OUTPUT" represents sales.
tdPostingVatTaxCreditTC	decimal	PostingVat.PostingVatTaxCreditTC	TC (Transaction Currency) Tax Credit amount.
	api annotation:		?
tdPostingVatTaxDebitTC	decimal	PostingVat.PostingVatTaxDebitTC	TC (Transaction Currency) Tax Debit amount.
	api annotation:		?
tcPostingVatTaxTransType	character	PostingVat.PostingVatTaxTransType	Tax Transaction Type?
	api annotation:		Available value for this field: 29 represents "AP Payment Check (Discount at Payment)", 55 represents "AP Transactions", 22 represents "AP Invoice", 18 represents "AR Invoice", 16 represents "AR Operational Invoice", 19 represents "AR Payment (Discount at Payment)", 56 represents "AR Transactions", 45 represents "Logistic Accounting - DO Shipment", 27 represents "Logistic Accounting - PO Receipt", 43 represents "Logistic Accounting - SO Shipment", 20 represents "Purchase Order", 21 represents "Purchase Order Receipt", 25 represents "Purchase Order Return"
tcPostingVatTransType	character	PostingVat.PostingVatTransType	Transaction Type?
	api annotation:		Available value for this field is: ACQUISITION represents "IC Acquisition", BOTH represents "Both", EXPORT represents "Export", IN represents "IN", OUT represents "OUT", IMPORT represents "Import", INLAND-PURCHASE represents "INLAND-PURCHASE", INLAND-SALES represents "INLAND-SALES", PURCHASE represents "Inland Purchase", SALES represents "Inland Sales", SUPPLY represents "IC Supply", TRIANGULARPURCHASE represents "Triangular Purchase", TRIANGULARSALES represents "Triangular Sales".
tiCreditNoteVatGroup_ID	integer	VatRule.CreditNoteVatGroup_ID	Credit Note Tax Group
tiInvoiceVatGroup_ID	integer	VatRule.InvoiceVatGroup_ID	Invoice Tax Group
tiVatPeriodYear	integer	VatPeriod.VatPeriodYear	Tax Year
ttVatPeriodEndDate	date	VatPeriod.VatPeriodEndDate	End Date
tiVatPeriodPeriod	integer	VatPeriod.VatPeriodPeriod	Tax Period
ttVatPeriodStartDate	date	VatPeriod.VatPeriodStartDate	Start Date
tcPeriodMarkCode	character	PeriodMark.PeriodMarkCode	Mark
tcFromTxzTaxZone	character	PostingVat.FromTxzTaxZone	From Tax Zone.
	api annotation:		?
tcToTxzTaxZone	character	PostingVat.ToTxzTaxZone	To Tax Zone
	api annotation:		?
tcTxclTaxCls	character	PostingVat.TxclTaxCls	Tax Class. A tax class previously defined in Tax Class Maintenance. Tax classes group addresses taxed at specific rates or that are tax-exempt and help determine the default tax environment (set of tax types) for related transactions.
	api annotation:		The value of Tax Class defaults to the header of transactions created for this address
tcTxenvTaxEnv	character	PostingVat.TxenvTaxEnv	Tax Environment. A code (maximum 16 characters) that identifies a set of tax types for a tax zone/tax class combination. On transactions, this code identifies the transaction or line-item tax environment.
	api annotation:		?

Internal usage

QadFinancials

method BVATExportListing.ApiExportQuarterSum