business logic query BoxesByYearBySupplier

Parameters

iiCompanyId	integer	Company id
icJournalTypeList	character	a list of journal types
icInvoiceTypeList	character	List of invoice types
icInvoiceMovementType	character	an invoice movement type, can be Initial or Movement
icPaymentYear	character
icSupplierFrom	character
icFederalTaxID	character
icSupplierTill	character
icCreditorTypeList	character
icCompanyList	character
icAddressType	character

query condition

each Box1099 where

Box1099.Box1099CalendarYear = icPaymentYear AND
Box1099.Box1099IsActive = true

each Box1099PurchaseTypeLink (inner-join) where

Box1099PurchaseTypeLink.Box1099_ID = Box1099.Box1099_ID AND

first PurchaseType (inner-join) where

PurchaseType.PurchaseType_ID = Box1099PurchaseTypeLink.PurchaseType_ID AND

each CInvoice (inner-join) where

CInvoice.PurchaseType_ID = PurchaseType.PurchaseType_ID AND
CInvoice.CInvoiceType can-do icInvoiceTypeList AND
CInvoice.Company_ID <> 0

each CInvoiceMovement (inner-join) where

CInvoiceMovement.Company_Id = iiCompanyId AND
CInvoiceMovement.CInvoice_ID = CInvoice.CInvoice_ID AND
CInvoiceMovement.CInvoiceMovementType can-do icInvoiceMovementType AND
year(CInvoiceMovementPostDate) = icPaymentYear

first PostingLine (inner-join) where

PostingLine.Company_Id = iiCompanyId AND
PostingLine.PostingLine_ID = CInvoiceMovement.PostingLine_ID AND

first Currency (inner-join) where

Currency.Currency_ID = PostingLine.Currency_ID AND

first Posting (inner-join) where

Posting.Company_Id = iiCompanyId AND
Posting.Posting_ID = PostingLine.Posting_ID AND

first Journal (inner-join) where

Journal.SharedSet_Id = vi_JOURNAL_sharedset(iiCompanyId) AND
Journal.Journal_ID = Posting.Journal_ID AND
Journal.JournalTypeCode can-do icJournalTypeList

first Company (inner-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = CInvoice.Company_ID AND
Company.CompanyCode can-do icCompanyList

each CompanyProperty (inner-join) where

CompanyProperty.Company_Id = iiCompanyId AND
CompanyProperty.Company_ID = Company.Company_ID AND

first BusinessRelation2 (outer-join) where

BusinessRelation2.BusinessRelation_ID = CompanyProperty.BusinessRelation_ID AND
BusinessRelation2.BusinessRelationIsTaxRep = true

each Address2 (outer-join) where

Address2.BusinessRelation_ID = BusinessRelation2.BusinessRelation_ID AND
Address2.AddressTaxIDFederal can-do icFederalTaxID

first AddressType2 (conditional-join) where

AddressType2.AddressType_ID = Address2.AddressType_ID AND
AddressType2.AddressTypeCode = icAddressType

first State2 (outer-join) where

State2.State_ID = Address2.State_ID AND

first Creditor (inner-join) where

Creditor.SharedSet_Id = vi_CREDITOR_sharedset(iiCompanyId) AND
Creditor.Creditor_ID = CInvoice.Creditor_ID AND
Creditor.CreditorCode >= icsupplierfrom AND
Creditor.CreditorCode <= icsuppliertill AND
Creditor.CreditorIsTaxReport = true AND
Creditor.CreditorTaxIDFederal <> ?

first BusinessRelation1 (inner-join) where

BusinessRelation1.BusinessRelation_ID = Creditor.BusinessRelation_ID AND

each Address1 (outer-join) where

Address1.BusinessRelation_ID = BusinessRelation1.BusinessRelation_ID AND

first AddressType1 (conditional-join) where

AddressType1.AddressType_ID = Address1.AddressType_ID AND
AddressType1.AddressTypeCode = icAddressType

first State1 (outer-join) where

State1.State_ID = Address1.State_ID AND

first CreditorType (conditional-join) where

CreditorType.CreditorType_ID = Creditor.CreditorType_ID AND
CreditorType.CreditorTypeCode can-do icCreditorTypeList

query resultset tqBoxesByYearBySupplier

field name	data type	db field	description
tiBox1099_ID	integer	Box1099.Box1099_ID	Record ID
tiBox1099CalendarYear	integer	Box1099.Box1099CalendarYear	Calendar Year
tcBox1099Code	character	Box1099.Box1099Code	1099 Box Code
tcBox1099Description	character	Box1099.Box1099Description	Description
tdBox1099MinimumAmountLC	decimal	Box1099.Box1099MinimumAmountLC	Minimum Amount
tcBox1099Reference	character	Box1099.Box1099Reference	1099 Box Reference
tcBusinessRelationName1	character	BusinessRelation2.BusinessRelationName1	Name: The full name of the business relation. This field sets the default name for linked addresses such as customers and suppliers.
	api annotation:		PartialUpdate = yes - defaults to the BusinessRelationCode.
tiCInvoice_ID	integer	CInvoice.CInvoice_ID	Record ID
tdCInvoiceMovementDiscountTC	decimal	CInvoiceMovement.CInvoiceMovementDiscountTC	CInvoiceMovementDiscountTC. The amount of payment discount in TC.
tcCInvoiceMovementType	character	CInvoiceMovement.CInvoiceMovementType	CInvoiceMovementType. "Initial" indicates that the movement represents the initial creation of the invoice, "movement" stands for all subsequent modifications of the invoice balance (payments, adjustments etc).
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.
tcCompanyCity	character	Address2.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcCompanyCode	character	Company.CompanyCode	Entity Code
tcCompanyStateCode	character	State2.StateCode	State
tcCompanyStateDescription	character	State2.StateDescription	Description
tcCompanyStreet1	character	Address2.AddressStreet1	Address: Line one of address details. This field can be blank.
tcCompanyStreet2	character	Address2.AddressStreet2	Address.Line two of address details. This field can be blank.
tcCompanyStreet3	character	Address2.AddressStreet3	Address.Line three of address details. This field can be blank.
tcCompanyTaxIDFederal	character	Address2.AddressTaxIDFederal	Federal Tax. The value of this filed is the tax ID assigned to this address by the federal or national government. If Tax Report is true, the Federal tax ID must be unique; otherwise, related business relations can share an ID.
tcCompanyTelephone	character	Address2.AddressTelephone	Telephone. The telephone number for calling this business relation address. This field can be blank.
tcCompanyZip	character	Address2.AddressZip	Zip. The postal code or US zip code associated with this address. This field is used to decide the tax zone for the address. This field can be blank.
tcCreditorCode	character	Creditor.CreditorCode	Supplier Code
tcCreditorTaxIDFederal	character	Creditor.CreditorTaxIDFederal	Federal Tax
tcCurrencyCode	character	Currency.CurrencyCode	Currency Code
tcJournalCode	character	Journal.JournalCode	A daybook code (maximum eight characters).
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).
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.
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.
tiPostingPeriod	integer	Posting.PostingPeriod	The GL period for the posting. The period must be open.
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.
tiPostingYear	integer	Posting.PostingYear	The GL calendar year for the posting.
tcPurchaseTypeCode	character	PurchaseType.PurchaseTypeCode	Purchase Type
tiState_ID	integer	State2.State_ID	Record ID
tiState_ID1	integer	State1.State_ID	Record ID
tcSupplierCity	character	Address1.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcSupplierName	character	BusinessRelation1.BusinessRelationName1	Name: The full name of the business relation. This field sets the default name for linked addresses such as customers and suppliers.
	api annotation:		PartialUpdate = yes - defaults to the BusinessRelationCode.
tcSupplierNameO	character	Address1.AddressName	Name. The full name of this particular address. The name of a headoffice address is the same as the name of the business relationship and can not be modified.
	api annotation:		PartialUpdate = yes - defaults to the name of the businessRelation.
tcSupplierStateCode	character	State1.StateCode	State
tcSupplierStateDescription	character	State1.StateDescription	Description
tcSupplierStreet1	character	Address1.AddressStreet1	Address: Line one of address details. This field can be blank.
tcSupplierStreet2	character	Address1.AddressStreet2	Address.Line two of address details. This field can be blank.
tcSupplierStreet3	character	Address1.AddressStreet3	Address.Line three of address details. This field can be blank.
tcSupplierTaxIDFederal	character	Address1.AddressTaxIDFederal	Federal Tax. The value of this filed is the tax ID assigned to this address by the federal or national government. If Tax Report is true, the Federal tax ID must be unique; otherwise, related business relations can share an ID.
tcSupplierTelephone	character	Address1.AddressTelephone	Telephone. The telephone number for calling this business relation address. This field can be blank.
tcSupplierZip	character	Address1.AddressZip	Zip. The postal code or US zip code associated with this address. This field is used to decide the tax zone for the address. This field can be blank.

Internal usage

QadFinancials

method BBox1099Report.M1099MiscPaperDeclaration
method BBox1099Report.M1099MiscReport