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)";
   }
}