Skip to content

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.

```