business logic query BaseProjectMastercard

Parameters

iiCompanyId	integer	Company id
icFromProject	character
icToProject	character
icCandoProject	character
icFromProjectManager	character
icToProjectManager	character
icCandoProjectManager	character
icFromAccountManager	character
icToAccountManager	character
icCandoAccountManager	character
icFromAdminManager	character
icToAdminManager	character
icCandoAdminManager	character
icFromController	character
icToController	character
icCandoController	character
icFromBR	character
icToBR	character
icCandoBR	character
icFromDebtor	character
icToDebtor	character
icCandoDebtor	character
icFromPenholderComp	character
icToPenholderComp	character
icCandoPenholderComp	character
icFromDivision	character
icToDivision	character
icCandoDivision	character
icFromProjectGroup	character
icToProjectGroup	character
icCandoProjectGroup	character
icFromMainProject	character
icToMainProject	character
icCandoMainProject	character
icStatusCode	character
icProjectType	character
itStartDate	date
itEndDate	date

query condition

each Project where

Project.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project.ProjectCode >= icFromProject AND
Project.ProjectCode <= icToProject AND
Project.ProjectCode can-do icCandoProject AND
Project.ProjectSystemStatus = icStatusCode AND
Project.ProjectTypeCode = icProjectType AND
Project.ProjectStartDate = itStartDate AND
Project.ProjectEndDate = itEndDate

first Address1 (conditional-join) where

Address1.Address_ID = Project.SiteAddress_ID AND

first Address2 (conditional-join) where

Address2.Address_ID = Project.InvoiceAddress_ID AND

first BusinessRelation (conditional-join) where

BusinessRelation.BusinessRelation_ID = Project.BusinessRelation_ID AND
BusinessRelation.BusinessRelationCode >= icFromBR AND
BusinessRelation.BusinessRelationCode <= icToBR AND
BusinessRelation.BusinessRelationCode can-do icCandoBR

first Company (conditional-join) where

Company.Company_Id = iiCompanyId AND
Company.Company_ID = Project.PenholderCompany_ID AND
Company.CompanyCode >= icFromPenholderComp AND
Company.CompanyCode <= icToPenholderComp AND
Company.CompanyCode can-do icCandoPenholderComp

first Debtor (conditional-join) where

Debtor.SharedSet_Id = vi_DEBTOR_sharedset(iiCompanyId) AND
Debtor.Debtor_ID = Project.Debtor_ID AND
Debtor.DebtorCode >= icFromDebtor AND
Debtor.DebtorCode <= icToDebtor AND
Debtor.DebtorCode can-do icCandoDebtor

first Division (conditional-join) where

Division.SharedSet_Id = vi_DIVISION_sharedset(iiCompanyId) AND
Division.Division_ID = Project.Division_ID AND
Division.DivisionCode >= icFromDivision AND
Division.DivisionCode <= icToDivision AND
Division.DivisionCode can-do icCandoDivision

first Project2 (conditional-join) where

Project2.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project2.MainProject_ID = Project.Project_ID AND
Project2.ProjectCode >= icFromMainProject AND
Project2.ProjectCode <= icToMainProject AND
Project2.ProjectCode can-do icCandoMainProject

first Project3 (conditional-join) where

Project3.SharedSet_Id = vi_PROJECT_sharedset(iiCompanyId) AND
Project3.TemplateProject_ID = Project.Project_ID AND

first ProjectGroup (inner-join) where

ProjectGroup.ProjectGroup_ID = Project.ProjectGroup_ID AND
ProjectGroup.ProjectGroupCode >= icFromProjectGroup AND
ProjectGroup.ProjectGroupCode <= icToProjectGroup AND
ProjectGroup.ProjectGroupCode can-do icCandoProjectGroup

each ProjectSafDefault (conditional-join) where

ProjectSafDefault.Project_ID = Project.Project_ID AND

first Saf (conditional-join) where

Saf.Saf_ID = ProjectSafDefault.Saf_ID AND

first SafConcept (conditional-join) where

SafConcept.SafConcept_ID = ProjectSafDefault.SafConcept_ID AND

first Usr1 (inner-join) where

Usr1.Usr_ID = Project.ProjectManagerUsr_ID AND
Usr1.UsrName >= icFromProjectManager AND
Usr1.UsrName <= icToProjectManager AND
Usr1.UsrName can-do icCandoProjectManager

first Usr2 (conditional-join) where

Usr2.Usr_ID = Project.AdminRespUsr_ID AND
Usr2.UsrName >= icFromAdminManager AND
Usr2.UsrName <= icToAdminManager AND
Usr2.UsrName can-do icCandoAdminManager

first Usr3 (conditional-join) where

Usr3.Usr_ID = Project.ControllerUsr_ID AND
Usr3.UsrName >= icFromController AND
Usr3.UsrName <= icToController AND
Usr3.UsrName can-do icCandoController

first Usr4 (conditional-join) where

Usr4.Usr_ID = Project.SalesRespUsr_ID AND
Usr4.UsrName >= icFromAccountManager AND
Usr4.UsrName <= icToAccountManager AND
Usr4.UsrName can-do icCandoAccountManager

query resultset tqBaseProjectMastercard

field name	data type	db field	description
tcAccountManager	character	Usr4.UsrName	User Name
tcAddressCity	character	Address2.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcAddressState	character	Address2.AddressState	Obsolete field
tcAddressStreet1	character	Address2.AddressStreet1	Address: Line one of address details. This field can be blank.
tcAddressStreet2	character	Address2.AddressStreet2	Address.Line two of address details. This field can be blank.
tcAddressZip	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.
tcAdminManager	character	Usr2.UsrName	User Name
tcBusinessRelationCode	character	BusinessRelation.BusinessRelationCode	Business Relation. A code to identify the business relation. If this field does not have a input from the user, the system automatically generates a number for the record based on the sequence defined in Business Relation Autonumber Create.
tcBusinessRelationName1	character	BusinessRelation.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.
tcController	character	Usr3.UsrName	User Name
tiCurrency_ID	integer	Project.Currency_ID	Currency
tcDebtorCode	character	Debtor.DebtorCode	A code (maximum eight characters) that identifies a customer. If the code you specify matches an existing supplier code, a warning message displays. You can choose to ignore the warning, and create the record. However, when a supplier and customer share the same code, they must reference the same business relation.
	api annotation:		If you leave the Customer Code field blank, the system automatically generates a number for the record based on the sequence defined in Customer Autonumber Create.
tiDivision_ID	integer	Division.Division_ID	Record ID
tcDivisionCode	character	Division.DivisionCode	Sub-Account
tcDivisionDescription	character	Division.DivisionDescription	Description
tiIndustry_ID	integer	Project.Industry_ID	Link to Industry
tiInvoiceCountry_ID	integer	Address2.Country_ID	Country Format
tcMainProjectCode	character	Project2.ProjectCode	Project
tcMainProjectDescription	character	Project2.ProjectDescription	Description
tiPaymentCondition_ID	integer	Project.PaymentCondition_ID	Credit Terms
tcPenholderCompanyCode	character	Company.CompanyCode	Entity Code
tiPriceList_ID	integer	Project.ObsoletePriceList_ID	Link to Price List
tiProject_ID	integer	Project.Project_ID	Record ID
tiProject_ID1	integer	Project2.Project_ID	Record ID
tiProject_ID2	integer	Project3.Project_ID	Record ID
tcProjectCode	character	Project.ProjectCode	Project
tdProjectContractAmountTC	decimal	Project.ProjectContractAmountTC	TC Contract Amount
ttProjectContractDate	date	Project.ProjectContractDate	Contract Date
tcProjectContractReference	character	Project.ProjectContractReference	Contract Reference
tcProjectDescription	character	Project.ProjectDescription	Description
ttProjectEndDate	date	Project.ProjectEndDate	End Date
tiProjectGroup_ID	integer	ProjectGroup.ProjectGroup_ID	Record ID
tcProjectGroupCode	character	ProjectGroup.ProjectGroupCode	Code
tcProjectGroupDescription	character	ProjectGroup.ProjectGroupDescription	Description
tlProjectGroupIsActive	logical	ProjectGroup.ProjectGroupIsActive	Active
tlProjectIsAltRevProdCalc	logical	Project.ProjectIsAltRevProdCalc	Alt Production Calculation
tlProjectIsTemplate	logical	Project.ProjectIsTemplate	Save as Template
tlProjectIsWithSaf	logical	Project.ProjectIsWithSaf	SAF Enabled
tcProjectManager	character	Usr1.UsrName	User Name
tcProjectPriceTypeCode	character	Project.ProjectPriceTypeCode	Price Type
ttProjectStartDate	date	Project.ProjectStartDate	Start Date
tiProjectStatus_ID	integer	Project.ProjectStatus_ID	Status Code
tcProjectSystemStatus	character	Project.ProjectSystemStatus	System Status
tcProjectTypeCode	character	Project.ProjectTypeCode	Project Type
tiRegion_ID	integer	Project.Region_ID	Region
tiSaf_ID	integer	Saf.Saf_ID	Record ID
tcSafCode	character	Saf.SafCode	SAF Code
tiSafConcept_ID	integer	SafConcept.SafConcept_ID	Record ID
tcSafConceptCode	character	SafConcept.SafConceptCode	SAF Concept Code
tcSafConceptDescription	character	SafConcept.SafConceptDescription	SAF Concept Description
tlSafConceptIsActive	logical	SafConcept.SafConceptIsActive	Active
tcSafDescription	character	Saf.SafDescription	SAF Description
tlSafIsActive	logical	Saf.SafIsActive	Active
tcSiteAddressCity	character	Address1.AddressCity	City. The city for this address. This field can be used to decide the tax zone of the address.
tcSiteAddressState	character	Address1.AddressState	Obsolete field
tcSiteAddressStreet1	character	Address1.AddressStreet1	Address: Line one of address details. This field can be blank.
tcSiteAddressStreet2	character	Address1.AddressStreet2	Address.Line two of address details. This field can be blank.
tcSiteAddressZip	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.
tiSiteCountry_ID	integer	Address1.County_ID	Description
tcTemplateProjectCode	character	Project3.ProjectCode	Project
tcTemplateProjectDescription	character	Project3.ProjectDescription	Description
ti_sequence	integer	calculated	primary index
tc_rowid	character	calculated	= rowid(Project),rowid(Address1),rowid(Address2),rowid(BusinessRelation),rowid(Company),rowid(Debtor),rowid(Division),rowid(Project2),rowid(Project3),rowid(ProjectGroup),rowid(ProjectSafDefault),rowid(Saf),rowid(SafConcept),rowid(Usr1),rowid(Usr2),rowid(Usr3),rowid(Usr4)

Internal usage

QadFinancials

method BProjectReport.ProjectMastercard