Query¶
A query is a data source that uses a free-formed SQL select, to provide read-only data.
This is typically generated for all referenced entity models and need not be specified, unless some properties or attributes need to be changed.
query Table {
from = "user_tables";
keys = TableName;
attribute TableName Text;
attribute TablespaceName Text;
attribute Status Text;
}
Do
- Use UpperCamel for the name:
- query PostingProposal
- Name in singular
- Query is needed if you need to use UNION
Consider
- It is ok to use Query if the where-clause contains complex business logic.
Avoid
- Avoid using Query unless you have to. Combine entity with references if it is possible.
- Avoid using Query just because a view exist.
- Avoid using Query just because it is currently better performance, the performance of an entity is discussed by framework, and how to improve it.
Example 1 (A simple query):¶
query ProfileSpecificationQuery {
from = "profile_specification";
where = "company_id IS NOT NULL";
keys = CompanyId, ProfileId;
attribute CompanyId Text;
attribute ProfileId Text;
attribute ProfileName Text;
attribute Description Text;
attribute ValidFrom Date;
attribute ValidTo Date;
}
Example 2 (Query with method calls):¶
query TaskTimeReportQuery {
from = "jt_task_transaction_uiv";
where = " transaction_type_db = 'PERSONNEL' ";
keys = TaskSeq, ExecutionInstanceSeq, TransactionId;
attribute ExecutionInstanceSeq Number;
attribute TaskSeq Number;
attribute TransactionId Number;
attribute Quantity Number;
attribute CostAmount Number;
attribute ReportComment Text;
attribute TimeTypeId Text;
attribute ResourceDesc Text {
fetch = "Resource_API.Get_Description(RESOURCE_SEQ)";
}
attribute ExpenceDsc Text {
fetch = "Jt_Task_Transaction_API.Get_Transaction_Type(transaction_id)";
}
}
Example 3 (Query with references and user specific where clause):¶
query UserMaterialStockQuery {
ludependencies = InventoryPartInStock;
from = "inventory_part_in_stock_uiv";
where = "warehouse IN (SELECT warehouse_id
FROM user_warehouse_connection
WHERE user_id = Fnd_Session_API.Get_Fnd_User)
AND qty_onhand >0";
keys = Contract, PartNo, ConfigurationId, LocationNo, LotBatchNo, SerialNo, EngChgLevel, WaivDevRejNo, ActivitySeq, HandlingUnitId;
attribute Contract Text;
attribute PartNo Text;
attribute Warehouse Text;
attribute QtyOnhand Number;
attribute QtyReserved Number;
attribute ConfigurationId Text;
attribute LocationNo Text;
attribute LotBatchNo Text;
attribute SerialNo Text;
attribute EngChgLevel Text;
attribute WaivDevRejNo Text;
attribute ActivitySeq Number;
attribute HandlingUnitId Number;
attribute QtyAvailable Number {
fetch = "qty_onhand - qty_reserved";
}
attribute ConditionCode Text {
fetch = "Condition_Code_API.Get_Description(Condition_Code_Manager_API.Get_Condition_Code(part_no,serial_no,lot_batch_no))";
}
attribute LocationDescription Text {
fetch = "NVL(Inventory_Location_API.Get_Location_Name(contract,location_no),location_no)";
}
reference InventoryPartRef(Contract, PartNo) to InventoryPart(Contract, PartNo);
reference WarehouseRef(Warehouse) to Warehouse(WarehouseId);
}
Example 4 (Query with Arrays):¶
query CompanyOrgStructureQuery {
from = "Company_Org_Head_Struct";
keys = StructBuId, Version, Year, OrgCode;
attribute Version Number;
attribute Year Number;
attribute OrgCode Text;
attribute OrgName Text;
attribute SupOrgCode Text;
attribute OrgStrValidFrom Date;
attribute OrgStrValidTo Date;
attribute OrgValidTo Date;
attribute OrgValidFrom Date;
attribute StructBuId Number;
attribute CompanyId Text;
attribute BusinessUnitId Number;
attribute BusinessUnitCode Text;
attribute BusinessUnitTypeDb Text;
array StructureArray(StructBuId, Year, Version, OrgCode) to CompanyOrgStructureQuery(StructBuId, Year, Version, SupOrgCode) {
where = " :child.StructBuId = :parent.StructBuId AND trunc(SYSDATE) BETWEEN :child.OrgStrValidFrom AND :child.OrgStrValidTo";
}
array SubPlanOrgArray(StructBuId, Year, Version, OrgCode) to HeadcountPlanOrgTempQuery(StructBuId, Year, Version, OrgCode);
array OrgHeadArray(StructBuId, Year, Version, BusinessUnitId) to HeadcountPlanOrgHead(StructBuId, Year, Version, BusinessUnitId);
array VacancyRequestArray(StructBuId, CompanyId, OrgCode) to VacancyRequest(StructBuId, CompanyId, OrgCode) {
where = " :parent.OrgCode = :child.OrgCode AND :parent.Year between nvl(Accounting_Period_API.Get_Accounting_Year(:child.CompanyId, :child.DateFrom),to_number(to_char(:child.DateFrom,'YYYY'))) and nvl(Accounting_Period_API.Get_Accounting_Year(:child.CompanyId, :child.DateTo),to_number(to_char(:child.DateTo,'YYYY')))
AND nvl(:parent.Year ,0) between to_char(:child.DateFrom,'YYYY') AND to_char(:child.DateTo,'YYYY')";
}
array AssignedArray(StructBuId, CompanyId, OrgCode) to CompanyPersAssignEmployee(StructBuId, CompanyId, OrgCode) {
where = "nvl(:parent.Year ,0) between to_char(:child.ValidFrom,'YYYY') AND to_char(:child.ValidTo,'YYYY')";
}
array InfoSubArray(OrgCode) to CompanyOrgAll(OrgCode) {
where = ":parent.BusinessUnitTypeDb != 'CUSTOM'";
}
array InfoMatrixSubArray(OrgCode) to BusinessUnitOrg(OrgCode) {
where = ":parent.BusinessUnitTypeDb = 'CUSTOM'";
}
}
Example 5 (Query with computed values):¶
query UnassignedTimelineQuery {
ludependencies = JtTaskResource, JtExecutionInstance;
from = "dual";
keys = DummyTrue;
attribute DummyTrue Text {
fetch = "'TRUE'";
}
// First declared array will define what entities are counted in compute for that specific entity.
computed OverdueCount Integer {
compute = count(ResourceDemandEntity,
[IsOverdue = "TRUE"]);
}
computed TodayCount Integer {
compute = count(ResourceDemandEntity,
[IsToday = "TRUE"]);
}
computed FutureCount Integer {
compute = count(ResourceDemandEntity,
[IsFuture = "TRUE"]);
}
--array AllAssignmentsArray(DummyTrue) to ResourceDemandEntity(DummyTrue);
array OverdueArray(DummyTrue) to ResourceDemandEntity(IsOverdue);
array TodayArray(DummyTrue) to ResourceDemandEntity(IsToday);
array FutureArray(DummyTrue) to ResourceDemandEntity(IsFuture);
}
Query with Summary¶
A query data source with summary information that uses a free-formed SQL select, to provide read-only data.
It is similar to a query definition, but the summary also does a group by all defined keys, useful for analysis and reporting.
summary OrdersByRegion {
from = "customer_order o, region r";
where = "o.region_id = r.region_id";
keys = OrderNo, RegionId;
attribute OrderNo Text;
attribute RegionId Text {
fetch = "o.region_id";
}
attribute Orders Number {
fetch = "count(1)";
}
attribute Profit Text {
fetch = "sum(0.sales_price)";
}
}
Crud¶
This setting is optional and defaults to Read.
None has been added as a possible value for crud, in case the query should be hidden in the metadata. This is mainly targeted for integration development, where a clean integration api is desired.
```