Oracle Queries

Covering Topics on Oracle Queries in a Detailed Way!
Jun 03

SQL Connect

By Product

How do you connect to your Oracle Cloud ERP/HCM Database?
If you are using the standard mechanism to do this using Oracle and BI tools – read on. We may be able to save 20% or more of your development and support time – by changing the way you do Adhoc Query against Oracle ERP/HCM Cloud database.

Get your FREE Trial Now! and Start using SQLConnect today.

SQLConnect helps you connect to your Oracle Cloud ERP, Oracle HCM Cloud, Oracle PPM cloud database via standard Oracle API from your desktop. We are Oracle developers at heart. When we started developing with and supporting Oracle ERP/HCM Cloud users, we found various issues that other developers resonate with:

  • EBS developers, who are used to tools like Toad, SQL Developer, etc. lack the ability for rapid SQL queries and analysis with Oracle Cloud.
  • Tools provided within the Oracle BI toolkit are not efficient and user-friendly.
  • It is also time-consuming as you need to go through multiple hoops before able to execute a query.
  • Results are restricted by OTBI limitations.
  • Oracle does not have any immediate plans to enhance/release new tools to support developers.
  • For SQL-savvy super users or end-users, there is no easy way to query information for analysis except building OTBI analyses or build BIP reports.

BUY NOW

SQLConnect – Running a HCM/benefits query against Oracle HCM Cloud database

Get Your FREE TRIAL NOW

Features:

  • Rapid access to the database from your desktop – both Mac and Windows
  • Multiple connections, Query execution and saving just like Toad and SQL Developer
  • Webservice driven – which means you are always getting the real-time data from the cloud using the latest version of web API.
  • Ability to save, search, find-replace, and manage multiple queries/files
  • Secured by Oracle Cloud Role access
  • Export to CSV & MS Excel

Benefits:

  • Efficient: Faster development
  • Easy: User-friendly; Users don’t need to login to Oracle Cloud to run a query

Create multiple connections and use the same tool to connect to DEV, TEST, PROD and various pods – effortlessly!How to create connection

SQL Connect: Seamlessly create multiple pod connections with Oracle Cloud.

Intellisense: Don’t remember Cloud tables or column names? Don’t worry. SQLConnect does. With Intellisense enabled, SQLConnect will prompt Tables and Column names. It also provides you running search so you can jump to the column that you are looking for.

Get Your FREE TRIAL NOW

PRICING

Enterprise

  • Initial one time setup for one pod – included
  • Administrator and User Guides
  • Training videos
  • Product support : Includes new releases, bug fixes, production issues support
  • 5 users minimum

Individual

  • Initial one time setup for one pod – included
  • Administrator and User Guides
  • Training videos
  • Product support : Includes new releases
  • No minimum commitment
  • 1 User Minimum



Contact for Demo


Buy Now!

Sep 14

SQL Query to find all active errors reported on Orders in Oracle SCM Cloud

By Product

SQL Query to find all active errors reported on Orders. This is the query to see errors reported for Orders entered from UI –

SELECT DISTINCT dha.creation_date,
        dha.source_order_number  ,
        DMT.message_text         ,
        DMB.message_type
FROM    fusion.doo_headers_all dha     ,
        FUSION.DOO_MESSAGE_ENTITIES DME,
        fusion.DOO_MESSAGES_B DMB      ,
        fusion.DOO_MESSAGES_TL DMT     ,
        fusion.DOO_MESSAGE_requests dmr
WHERE   dha.header_id          = DME.entity_id
        AND DME.entity_name    = 'ORDER'
        AND DMB.msg_request_id = DME.msg_request_id
        AND DMT.message_id     = DMB.message_id
        AND dmr.MSG_REQUEST_ID = DMB.MSG_REQUEST_ID
        AND dmr.ACTIVE_FLAG    = 'Y'
ORDER BY dha.creation_date DESC

And this is for Errors reported for imported orders:

SELECT DISTINCT dohai.creation_date    ,
        dohai.source_transaction_number,
        DMT.message_text               ,
        DMB.message_type
FROM    fusion.DOO_ORDER_HEADERS_ALL_INT dohai,
        FUSION.DOO_MESSAGE_ENTITIES DME       ,
        fusion.DOO_MESSAGES_B DMB             ,
        fusion.DOO_MESSAGES_TL DMT            ,
        fusion.DOO_MESSAGE_requests dmr
WHERE   dohai.source_transaction_number = DME.entity_id
        AND DME.entity_name            IN ('SRC_ORDER_NUM','SRC_ORDER','RECORD_NUMBER')
        AND DMB.msg_request_id          = DME.msg_request_id
        AND DMT.message_id              = DMB.message_id
        AND dmr.MSG_REQUEST_ID          = DMB.MSG_REQUEST_ID
        AND dmr.ACTIVE_FLAG             = 'Y'
ORDER BY dohai.creation_date DESC
Sep 14

Organization SQL Query in Oracle Cloud ERP

By Product

The main table for inventory organizations and parameters is called INV_ORG_PARAMETERS. The MASTER_ORGANIZATION_ID relates back to the same table listing the parent of the organization. Other organization information is stored in Human Resource (HR) tables like HR_ORGANIZATION_UNITS_F_TL.

Note that the _TL table is stripped by language so the example query just looks at the US language.

You can change it as required. The meaning of the code for each parameter is found in FND_LOOKUPS.  Here is a sample query looking at organizations’ parameters.

There are many other interesting columns but this query focuses on a few related to the main controls of the organization:

SELECT 
  a.ORGANIZATION_ID,
  a.ORGANIZATION_CODE,
  g.NAME ORGANIZATION_NAME,
  a.MASTER_ORGANIZATION_ID,
  b.ORGANIZATION_CODE MASTER_ORGANIZATION_CODE,
  a.BUSINESS_UNIT_ID,
  a.LEGAL_ENTITY_ID,
  a.CALENDAR_CODE,
  a.NEGATIVE_INV_RECEIPT_CODE,
  e.MEANING NEG_MEANING,
  a.STOCK_LOCATOR_CONTROL_CODE,
  c.meaning STOCK_LOCATOR_MEANING,
  a.SERIAL_NUMBER_TYPE,
  d.meaning SERIAL_NUMBER_TYPE_MEANING,
  a.LOT_NUMBER_UNIQUENESS,
  f.meaning LOT_UNIQ_MEANING
FROM 
  INV_ORG_PARAMETERS a,
  INV_ORG_PARAMETERS b,
  FND_LOOKUPS c,
  FND_LOOKUPS d,
  FND_LOOKUPS e,
  FND_LOOKUPS f,
  HR_ORGANIZATION_UNITS_F_TL g
WHERE 
      a.MASTER_ORGANIZATION_ID = b.ORGANIZATION_ID
  AND c.LOOKUP_TYPE = 'INV_LOCATION_CONTROL'
  AND a.STOCK_LOCATOR_CONTROL_CODE = c.LOOKUP_CODE (+)
  AND d.LOOKUP_TYPE = 'INV_SERIAL_NUMBER'
  AND a.SERIAL_NUMBER_TYPE = d.LOOKUP_CODE (+)
  AND e.LOOKUP_TYPE = 'INV_YES_NO_NUMERIC'
  AND a.NEGATIVE_INV_RECEIPT_CODE = e.LOOKUP_CODE (+)
  AND f.LOOKUP_TYPE = 'INV_LOT_UNIQUENESS'
  AND a.LOT_NUMBER_UNIQUENESS = f.LOOKUP_CODE (+)
  AND g.ORGANIZATION_ID = a.ORGANIZATION_ID
  AND g.LANGUAGE = 'US'
ORDER BY 
  a.ORGANIZATION_ID;
Sep 10

Step by step to run a SQL query in Oracle Fusion Cloud

By Product

Here is How to run a SQL Query in Oracle SAAS (Fusion) cloud environment.

As you probably know by now, there are multiple ways to extract desired data out of Oracle Cloud SAAS applications including HCM/ERP/SCM cloud.

These steps are a quick and easy way to extract data by running a SQL query in Oracle Fusion Cloud with SQLconnect

Step 1 – Launch SQLConnect app from your desktop.

Step 2 – If you already have a connection setup to your desired Pod, then use that or create a new connection. This should take 2 minutes.

Step 3 – Write your query, or Open the existing file that contains your query.

Step 4 – Press CTRL – Enter or Press the RUN button on the user interface.

Step 5 – You should be able to see the Query results if there are no errors. If the query has any errors, you can review the errors and correct them before running the query again.

You can also review the time it took to run the query and other relevant details by clicking the “view SQL” link on the status bar. And that is how you run a SQL query in Oracle fusion cloud.

 

Prerequisites for this is that you must have the right role in order to run query and get the data required. If you do not have the role you may need to contact your BI admin to provide assistance in getting this setup. Its a one time activity, and will save you hours of work in future. Its easy to run SQL query against Oracle SAAS Cloud using SQLConnect.

Jul 22

On Hand Quantity Query in Oracle SCM Cloud

By Oracle Queries 
select ESI.ITEM_NUMBER ,
  IODV.ORGANIZATION_NAME,
  ESI.DESCRIPTION ,
  IOP.ORGANIZATION_CODE ,
  SUM(IOQD.TRANSACTION_QUANTITY) TRX_QTY,
  UOMT.UNIT_OF_MEASURE ,
  IOQD.SUBINVENTORY_CODE ,
  IOQD.LOT_NUMBER,
  IIL.SEGMENT1,
  IIL.SEGMENT2,
  IIL.SEGMENT3,
  IIL.SEGMENT4
from INV_ONHAND_QUANTITIES_DETAIL IOQD ,
  EGP_SYSTEM_ITEMS ESI ,
  INV_ORG_PARAMETERS IOP ,
  INV_ORGANIZATION_DEFINITIONS_V IODV,
  INV_UNITS_OF_MEASURE_TL UOMT ,
  INV_UNITS_OF_MEASURE_B UOMB,
  INV_ITEM_LOCATIONS IIL
where 1                     = 1
and IODV.ORGANIZATION_ID    =IOP.ORGANIZATION_ID
and IOQD.INVENTORY_ITEM_ID  = ESI.INVENTORY_ITEM_ID
and IOQD.ORGANIZATION_ID    = ESI.ORGANIZATION_ID
and ESI.ORGANIZATION_ID     = IOP.ORGANIZATION_ID
and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID
and UOMB.UOM_CODE           = IOQD.TRANSACTION_UOM_CODE
and IOQD.ORGANIZATION_ID    = IIL.ORGANIZATION_ID(+)
and IOQD.SUBINVENTORY_CODE  = IIL.SUBINVENTORY_CODE(+)
and IOQD.LOCATOR_ID         =IIL.INVENTORY_LOCATION_ID(+)
and ESI.ITEM_NUMBER        in '<Item Numbers>'
group by ESI.ITEM_NUMBER ,
  IODV.ORGANIZATION_NAME,
  ESI.DESCRIPTION ,
  IOP.ORGANIZATION_CODE ,
  UOMT.UNIT_OF_MEASURE ,
  IOQD.SUBINVENTORY_CODE ,
  IOQD.LOT_NUMBER,
  IIL.SEGMENT1,
  IIL.SEGMENT2,
  IIL.SEGMENT3,
  IIL.SEGMENT4
order by 1,2

Using this query you can find out On hand quantity for any item in Oracle SCM cloud. It used tables in SCM Cloud such as EGP_system_Items, INVentory tables like INV_units_of_measure, INV_org_parameters, INV_onhand_quantities_detail and INV_item_locations tables.

Add any additional columns that you want and use Oracle Cloud Tools SQLConect database browser to learn more about each of these tables/views .

Jul 22

Goods Receipt Quantity Query in SCM Cloud

By Oracle Queries 
--Goods Receipt Quantity
SELECT Distinct
       pha.segment1                                                                              as "PO Number"
     , ps.Vendor_ID                                                                              as "Vendor Number"
     , ps.attribute1                                                                             as "Vendor Class"
     , ps.attribute2                                                                             as "Managing Office"
     , ps.ATTRIBUTE_NUMBER1                                                                      as "Supplier Legacy"
     , ps.vendor_name                                                                            as "Vendor Name"
     , rsl.VENDOR_ITEM_NUM                                                                       as "Vendor Item Num"
     , rsl.ITEM_DESCRIPTION                                                                      as "Item Description"
     , rsl.QUANTITY_SHIPPED                                                                      as "Quantity Shipped"
     , rsl.QUANTITY_RECEIVED                                                                     as "Quantity Received"
     , to_char( rct.TRANSACTION_DATE, 'DD/MM/YYYY')                                              as "Transaction Date"
     , pla.UNIT_PRICE* pla.QUANTITY                                                              as "Line Value"
     , HOU.Name                                                                                  as "Organization Name"
     , pla.UNIT_PRICE                                                                            as "Unit Price"
     , To_Char( pha.creation_date,'DD/MM/YYYY')                                                  as "PO Creation Date"
     , gl_flexfields_pkg.get_concat_description (chart_of_accounts_id, PDA.code_combination_id ) as "Code Combination Description"
     , gcc.segment1
              ||'.'
              ||gcc.segment2
              ||'.'
              ||gcc.segment3
              ||'.'
              || gcc.segment4
              ||'.'
              ||gcc.segment5
              ||'.'
              ||gcc.segment6
              ||'.'
              ||gcc.segment7
              ||'.'
              ||gcc.segment8
              ||'.'
              ||gcc.segment9
              ||'.'
              ||gcc.segment10 																	as "Concatenated Segments"
     , RSH.RECEIPT_NUM        																	as "Receipt Number"
     , pla.QUANTITY           																	as "PO Line Ordered Quantity"
     , CASE
              When pha.segment1 LIKE '4%'
                     THEN 'Franchise'
                     ELSE 'Company owned'
       End                                     													as "Business ID"
     , Null                                    													as "DS PO Indicator"
     , TO_CHAR( rct.Transaction_Date, 'MON' )  													as "Fiscal Period"
     , TO_CHAR( rct.Transaction_Date, 'YYYY' ) 													as "Fiscal Year"
     , TO_CHAR( sysdate, 'MON' )               													as "Regular Period"
     , TO_CHAR( sysdate, 'YYYY' )              													as "Regular Year"
     , Null                                    													as "PO_USER_FLD_1"
     , Null                                    													as "PO_USER_FLD_6"
     , rct.COMMENTS                            													as "Receipt Description"
FROM
       RCV_SHIPMENT_HEADERS 		RSH
     , RCV_SHIPMENT_LINES   		RSL
     , PO_HEADERS_ALL       		PHA
     , PER_PERSON_NAMES_F_V 		PersonName
     , PO_LINES_ALL         		pla
     , PO_DISTRIBUTIONS_ALL 		pda
     , PO_LINE_TYPES_B				PLTB
     , POZ_SUPPLIERS_V             	ps
     , POZ_SUPPLIER_SITES_V        	pss
     , RCV_TRANSACTIONS            	rct
     , HR_ALL_ORGANIZATION_UNITS_X 	HOU
     , GL_CODE_COMBINATIONS        	GCC
WHERE
       RSH.SHIPMENT_HEADER_ID			=RSL.SHIPMENT_HEADER_ID
       AND RSL.po_header_id				=PHA.po_header_id
       AND HOU.ORGANIZATION_ID			=rct.ORGANIZATION_ID
       AND GCC.CODE_COMBINATION_ID		=PDA.CODE_COMBINATION_ID
       AND PersonName.PERSON_ID			=PhA.agent_id
       AND pla.po_header_id				=PHA.po_header_id
       AND PDA.PO_DISTRIBUTION_ID		=rct.PO_DISTRIBUTION_ID
       AND PLTB.line_type_id			=pla.LINE_TYPE_ID
       AND pla.PO_LINE_ID				=rsl.PO_LINE_ID
       AND pha.VENDOR_ID				=ps.vendor_id
       AND pss.VENDOR_ID				=ps.vendor_id
       AND rct.SHIPMENT_HEADER_ID		=RSH.SHIPMENT_HEADER_ID
       AND rsl.SHIPMENT_LINE_ID			=rct.SHIPMENT_LINE_ID
       AND trunc( rct.TRANSACTION_DATE) between :Receipt_Date_From and :Receipt_Date_To
       AND ps.ATTRIBUTE_NUMBER1			=:Sup_Leg_No
May 22

Oracle Cloud Source System IDs for all Business Objects

By Oracle Queries

Below listed are some of the queries to get Source System Ids for business objects Person, Periods of Service, National Identified, and Person Legislative Info from Oracle HCM Cloud application.

SELECT papf.person_number
	,h.source_system_owner
	,h.source_system_id
	,h.surrogate_id
FROM HRC_INTEGRATION_KEY_MAP h
	,PER_ALL_PEOPLE_F Papf
WHERE h.object_name = 'Person'
	AND h.surrogate_id = papf.PERSON_ID
	AND sysdate BETWEEN papf.effective_start_date
		AND papf.effective_end_date
SELECT papf.person_number
	,H.source_system_owner
	,H.source_system_id
	,H.surrogate_id
	,'Period_Of_Service_Id'
	,to_char(ppos.DATE_START)
	,to_char(ppos.ACTUAL_TERMINATION_DATE)
FROM HRC_INTEGRATION_KEY_MAP H
	,PER_PERIODS_OF_SERVICE ppos
	,per_all_people_f papf
WHERE H.object_name = 'PeriodOfService'
	AND H.surrogate_id = ppos.PERIOD_OF_SERVICE_ID
	AND ppos.PERSON_ID = papf.person_id
	AND sysdate BETWEEN papf.effective_start_date
		AND papf.effective_end_date
SELECT papf.person_number
	,source_system_owner
	,source_system_id
	,surrogate_id
	,'National_Identifier_ID'
	,PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_NUMBER
	,PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_TYPE
FROM HRC_INTEGRATION_KEY_MAP H
	,PER_NATIONAL_IDENTIFIERS_V PER_NATIONAL_IDENTIFIERS_V
	,per_all_people_f papf
WHERE h.object_name = 'NationalIdentifier'
	AND h.surrogate_id = PER_NATIONAL_IDENTIFIERS_V.NATIONAL_IDENTIFIER_ID
	AND PER_NATIONAL_IDENTIFIERS_V.person_id = papf.person_id
	AND sysdate BETWEEN papf.effective_start_date
		AND papf.effective_end_date
SELECT papf.person_number
	,source_system_owner
	,source_system_id
	,surrogate_id
	,PER_PEOPLE_LEGISLATIVE_F.SEX
	,PER_PEOPLE_LEGISLATIVE_F.MARITAL_STATUS
FROM HRC_INTEGRATION_KEY_MAP H
	,PER_PEOPLE_LEGISLATIVE_F PER_PEOPLE_LEGISLATIVE_F
	,per_all_people_f papf
WHERE h.object_name = 'PersonLegislativeInfo'
	AND h.surrogate_id = PER_PEOPLE_LEGISLATIVE_F.PERSON_LEGISLATIVE_ID
	AND PER_PEOPLE_LEGISLATIVE_F.person_id = papf.person_id
	AND sysdate BETWEEN papf.effective_start_date
		AND papf.effective_end_date
May 22

HCM Cloud – Time in Job, Time in Position Query

By Oracle Queries

This query in Oracle HCM Cloud provides information on Employees and how much time they have been in a certain job or in a certain Position.

As you can see in the below screenshot from SQLConnect, this query provides Time in Job and Time in a particular position in YEARS. Copy this query and use it in SQLConnect – Get the data you want without even logging into BI tools for Oracle HCM cloud.

You can remove a few conditions from this query, if you want the time to be calculated even across global transfers/rehires.

SELECT papf.person_number
,paam.assignment_number
,paam.assignment_status_type
,pjfv.name Job_Name
,hapfv.name Position_Name
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.job_id = paam.job_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.job_id <> paam1.job_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_job_years
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.position_id = paam.position_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.position_id <> paam1.position_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_position_years
FROM per_all_assignments_m paam
,per_all_people_f papf
,HR_ALL_POSITIONS_F_VL hapfv
,PER_JOBS_F_VL pjfv
WHERE papf.person_id = paam.person_id
AND paam.assignment_type NOT IN ('ET','CT','PT')
AND paam.assignment_status_type = 'ACTIVE'
AND paam.primary_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND paam.position_id = hapfv.position_id(+)
AND paam.job_id = pjfv.job_id(+)
AND sysdate BETWEEN pjfv.effective_start_date(+)
AND pjfv.effective_end_date(+)
AND sysdate BETWEEN hapfv.effective_start_date(+)
AND hapfv.effective_end_date(+)
AND sysdate BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND sysdate BETWEEN paam.effective_start_date
AND paam.effective_end_date
May 22

Direct and Indirect Reports for a Manager

By Oracle Queries

In this query, you can get the details of Managers and their direct/indirect reports from Oracle HCM Cloud.

This query uses following tables from ORacle HCM CLoud –

PER_ASSIGNMENT_SUPERVISORS_F ,

PER_ALL_PEOPLE_F,

PER_ALL_ASSIGNMENTS_M and

PER_ASSIGNMENT_SUPERVISORS_F

tables to get the manager information of an employee.

SELECT ppnf_emp.full_name
,pmhd.person_id
,papf_emp.person_number
,pmhd.assignment_id
,pmhd.manager_id
,papf_sup.person_number manager_number
,pmhd.manager_assignment_id
,pmhd.manager_level
,pmhd.manager_type
,pmhd.effective_start_date
,pmhd.effective_end_date
,decode(pmhd.manager_level, '1', 'Direct Reportee', 'Indirect Reportee') Direct_Indirect
FROM per_manager_hrchy_dn pmhd
,per_person_names_f_v ppnf_emp
,per_all_people_f papf_emp
,per_all_people_f papf_sup
,per_person_names_f_v ppnf_sup
WHERE 1 = 1
AND pmhd.manager_type = 'LINE_MANAGER'
AND pmhd.person_id = ppnf_emp.person_id
AND ppnf_emp.person_id = papf_emp.person_id
AND papf_sup.person_id = pmhd.manager_id
AND ppnf_sup.person_id = pmhd.manager_id
AND ppnf_emp.name_type = 'GLOBAL'
AND ppnf_sup.name_type = 'GLOBAL'
AND sysdate BETWEEN papf_emp.effective_start_date
AND papf_emp.effective_end_date
AND sysdate BETWEEN papf_sup.effective_start_date
AND papf_sup.effective_end_date
AND sysdate BETWEEN ppnf_emp.effective_start_date
AND ppnf_emp.effective_end_date
AND sysdate BETWEEN ppnf_sup.effective_start_date
AND ppnf_sup.effective_end_date
AND sysdate BETWEEN pmhd.effective_start_date
AND pmhd.effective_end_date
AND papf_sup.person_number = :MANAGER_PERSON_NUMBER --Comment if you want all managers
AND pmhd.manager_level = '1' -- 1 for direct reports
ORDER BY papf_emp.person_number

And here is the output as it looks in HCM Demo environment, after running the query using SQLConnect.

May 15

SQLConnect Shortcuts

By Support

Here is a list of shortcuts – keystroke combinations – that can help you do a number of tasks quickly in OracleCloudTools SQLConnect applications.

For example when running query against Oracle cloud saas applications, if you want to change a column’s date format and add to_Char to the query quickly, try ctrl-D…

 

 # Function Windows Shortcut Mac Shortcut Comments
1 Execute SQL Control + Enter Command + Enter  
2 Show Connections Control + Shift + H Command + Shift + H  
3 Comment Line(s) Control + / Command + / You can select multiple Lines
4 Uncomment Line(s) Control + / Command + / You can select multiple Lines
5 Find Control + F Command + F  
6 Replace Control + H Command + H  
7 Format F2 F2 Format Selected Text or Whole editor if Text not selected
8 Database Browser F4 F4 Search selected text if selected, otherwise open the DB Browser
9 Date Format Control + D Command + D Add a to_char for a column in the format selected in Preferences
10 Increase Font  Control + Command +  + is the addition sign on your keyboard
11 Decrease Font Control – Command –  – is the minus sign or dash sign on your keyboard
12 Open Control + O Command + O  
13 Save Control + S Command + S  
14 Save All Control + Shift + S Command + Shift + S  
15 Cut Control + X Command + X  
16 Copy Control + C Command + C  
17 Paste Control + V Command + V  
18 Select All Control + A Command + A  
19 Undo Control + Z Command + Z  
20 Redo Control + Shift + Z Command + Shift + Z  
Mar 27

Covid-19 Reporting in Oracle HCM Cloud

By Oracle Queries

Here is a list and pre-built reports to help Human Resources with COVID-19 Reporting with Oracle HCM Cloud. Hope this helps.

Oracle realizes that many of HCM Cloud users are busy responding to the current COVID-19 Coronavirus pandemic. Hope that this series of reports will provide relevant content designed to save you time.

Monitor Passport Expiry for Employees

Many organizations are trying to identify which employees have a passport that expires in the coming months. Here is a catalog file of a report that shows a list of those employees.

Steps to Use:

  1. Download the catalog file from this link
  2. Within the BI Catalog, locate the folder where you want to save the content, e.g. Shared Folders>Custom> YourFolder
  3. Unarchive the catalog file and you will see the new report
  4. Link to the original post on Customer Connect https://cloudcustomerconnect.oracle.com/posts/30396bfe04

Below is the Query to identify which employees have a passport that expires in the coming months.

SELECT
   0 s_0,
   "Workforce Management - Person Real Time"."Person Passport Details"."Country" s_1,
   "Workforce Management - Person Real Time"."Person Passport Details"."Expiration Date" s_2,
   "Workforce Management - Person Real Time"."Person Passport Details"."Issuing Authority" s_3,
   "Workforce Management - Person Real Time"."Person Passport Details"."Issuing Country" s_4,
   "Workforce Management - Person Real Time"."Worker"."Employee Display Name" s_5,
   "Workforce Management - Person Real Time"."Worker"."Manager Name" s_6,
   "Workforce Management - Person Real Time"."Worker"."Person Number" s_7,
   DESCRIPTOR_IDOF("Workforce Management - Person Real Time"."Person Passport Details"."Country") s_8,
   DESCRIPTOR_IDOF("Workforce Management - Person Real Time"."Person Passport Details"."Issuing Country") s_9,
   TIMESTAMPDIFF(SQL_TSI_MONTH, CURRENT_DATE ,"Workforce Management - Person Real Time"."Person Passport Details"."Expiration Date") s_10,
   COUNT(DISTINCT "Workforce Management - Person Real Time"."Worker"."Person ID") s_11,
   REPORT_AGGREGATE(COUNT(DISTINCT "Workforce Management - Person Real Time"."Worker"."Person ID") BY ) s_12
FROM "Workforce Management - Person Real Time"
WHERE
(("Person Passport Details"."Expiration Date" IS NOT NULL) AND (TIMESTAMPDIFF(SQL_TSI_MONTH, CURRENT_DATE , "Person Passport Details"."Expiration Date") BETWEEN -7 AND 24) AND ("Worker"."Assignment Status Type Code" = 'ACTIVE'))
ORDER BY 1
FETCH FIRST 75001 ROWS ONLY

Query has been tested on Fusion 20A but applicable to earlier versions as well.

Mar 15

Comparing OTBI and SQLConnect

By Product

So you need some data out of your Oracle ERP/HCM Cloud?

There are many ways of doing it. I’ll show you two simple ways, and then allow you to compare which one seems better.

In order for me to make a fair comparison, I’ll assume that you are an Oracle EBS user, and going to Oracle Cloud ERP. You have a basic understanding of writing ad-hoc queries in Oracle EBS and running them using tools like Toad, SQL Developer, etc.

Now, in Oracle Cloud ERP, there is no such tool. Oracle ERP Cloud comes with Oracle Transactional Business Intelligence (OTBI) that you can use to get your answers from Oracle – these ad-hoc queries are also called Analyses.

So, let’s see what it takes to create an analysis in OTBI?

Tutorial Purpose: Shows you how to create analyses with different views in Oracle BI Cloud Service.

Time Taken: This tutorial takes approximately 15 minutes to complete.

What do you need? : Access to Oracle BI Cloud service.

Steps:

  • Sign in to Oracle Cloud with your credentials. From your cloud homepage, you can select Tools > Reports and Analytics > Browse Catalog.
  • From the Catalog, select menu NEW > Analysis.
  • Then select a Subject Area. Lets choose Workforce Management – Worker Assignment Real Time for this example.
  • In the Criteria tab, in the Subject Area pane, drag the following columns to Selected Columns:
    • Select Person Number from Worker
    • Select First Name , Last Name from Worker
Criteria in OTBI

 

 

  •  
  • Click Results.

You should see the results on the right hand side in a tabular format. You can not export these to CSV or Excel if you so desire.

How much time did this exercise take?

Now, lets go to SQLConnect

  • Connect to you pod
  • In the new Tab that opened, Paste this Query –
select first_name,
last_name,
person_number
from  PER_ALL_PEOPLE_F ppf,
per_person_names_f_v ppln
where ppln.person_id = ppf.person_id;
  • and hit CTRL + Enter
Results of Query in SQLConnect
  •  

How long did it take to run this in SQLConnect? You can now export this to your CSV or Excel if you so desire!

Ability OTBI SQLConnect
Connect to Oracle Cloud Pod Yes Yes
Run Adhoc Query Create Analysis Yes, SQL
Get Info you need Need to know the Subject Area, Column Names etc. Need to know the Table and Columns names, Joins. You can use DB Browser for help.
Export to Excel and CSV Yes Yes
Time Taken 15 mins 5 mins

What is your feedback?

If you want to try out the power of SQLConnect to connect to Oracle HCM cloud or Oracle ERP Cloud and run adhoc queries, contact us today!

Mar 15

Oracle Cloud Reporting Tips and Tricks

By Oracle Queries

Oracle ERP Cloud, HCM Cloud and SCM Cloud are becoming more and more popular and mainstream ERP applications. With the number of increasing users, there are a lot of questions on how to get data our of Oracle ERP Cloud. How do you use OTBI? What are the gotchas with OTBI and BI reporting in Oracle ERP or HCM Cloud.

In this article, we are going to share some important tips, things to remember and how to effectively use Oracle OTBI/BI for ERP Cloud applications. Just to remind, you can always use SQLConnect to query data from your Oracle SAAS Cloud applications.

What is the real-time subject area?

Real time subject areas in Oracle OTBI will only bring the latest record. These are not useful if you want to build trend reports or comparisons.

How can you make custom reports or standard HR dashboard available to only some employees based on their roles, without them going into OTBI?

There are many ways in which you can accomplish this. Here are a few:

  1. Create an OTBI dashboard that is accessed via a new menu option that is secured to a role.
  2. Create an OTBI dashboard or just one analysis and embed them into HCM Cloud – There is an example Line Manager dashboard with instructions on how to add it to My Team.
  3. Create OTBI infolet analyses and embed them into Infolets (Infolets can also be secured to roles). These can then include drills to full-size analyses or a dashboard.
  4. Email reports to workers using Agents.

How can I get my pivot table to display the number zero instead of leaving the cell blank?

Use a custom format of “#,##0;-#,##0;0” – the first part is for positive numbers, the second for negative and the third for null.

How can I make parameters dependent on each other?

Use the Limit Values by under Options when you create a dashboard prompt. If you select this check box, you can select any/all the columns that must restrict the values from the current column.

OracleCloudTools SQLConnect offers bind variables feature that will allow you to create ad-hoc queries and run them with different variable values against your Oracle HCM Cloud, Oracle ERP Cloud, Oracle SCM Cloud, Oracle PPM Cloud right from your desktop.

How can I conditionally format one column based on a value in another?

Ensure that Value Suppression is set to Repeat in the column properties of the column in which you want to apply the conditional format. More information here.

What are Request Variables used for?

A Request Variable is used to override the value of session variable in a particular session. For example: if there is a session variable named ABC which renders value as ‘New’. You can set the value of this session variable as ‘Old’ for that specific session using the following syntax SET VARIABLE ABC = ‘OLD’; in Advance tab > Prefix column. The request variable overrides the value of session variable only for this request, the session variable will have the actual value ‘New’ outside of this request.

Does a re-usable filter need to be from the same subject area as the analysis it is applied to?

A: Not necessarily. If it is coming from other subject areas, instead of ‘Is Prompted’, you need to pass values using presentation variables.

Where is the best place to save filters and columns?

They can be saved anywhere, but if you save them under the subject area name then they are not available in queries that are not using that subject area.

Does OTBI deliver the dates with time in the timezone of the user or the server? 

The server.

What circumstances would Selection Steps be useful?  

Filters are applied before the query is aggregated meaning that it could affect calculations and measures because the query is affected. Whereas selections steps are applied after the query is aggregated meaning that the only thing that is affected is what the user will see, this will not affect calculations.

What does DESCRIPTOR_IDOF(“Workforce Management – Worker Assignment Real Time”.”Position”.”Position Active Flag”) = ‘A’ mean?

This is a feature of the OBIEE Admin tool, i.e the RPD.  Certain columns can be specified as ‘Double’ columns which basically holds the code and value.  This helps in queries and filters in improving the performance and passing values irrespective of what it is as actual value.

Can I use OTBI to burst output to many users?

Yes. OTBI provides bursting via agents. Create an analysis that includes the lowercase company email address or username.  This column can then be used in the agent to split the delivered report so that each email/username only receives the relevant rows.

Can I report current and previous values side by side?

Yes. One common example is reporting the current assignment values and the previous ones after an assignment change.  This report must be written in Logical SQL. More information on Logical SQL can be found here: https://docs.oracle.com/en/cloud/saas/business-intelligence/index.html.

 

How do I query Oracle ERP/HCM Cloud data from my desktop using a tools like toad or SQL developer?

You use SQLConnect !

Can I report a list of workers who don’t have a goal (or absence, or something else)?

Yes.

How can I export a whole OTBI dashboard to Excel?