Sqlguru.ai

Sqlguru.ai

Blogs

Query to Confirm Approval of Purchase Orders, Including Blanket POs, by the ‘Owner’

The query to confirm the approval of Purchase Orders (POs), encompassing Blanket POs, by the ‘owner,’ where the creator and approver of the Purchase Order (PO) are identical. SELECT distinct poh.org_id “ORG ID”, poh.segment1 “PO NUM”, poh.type_lookup_code “TYPE”, (SELECT agent_name … Read More

Query for GL Journal Creation, Approval, and Posting by the Same User

The query for GL Journal Created Approved and Posted Same User – 3 queries with the same Condition GL Journal Created Approved and Posted Same User: Query -1 SELECT l.NAME “Ledger Name”, SHORT_NAME “Ledger Short Name”, LEDGER_CATEGORY_CODE, APPROVER_EMPLOYEE_ID, b.je_batch_id batch_id … Read More

Procedural Guidelines for Access Control and Entitlement Creation in AACG 8.6.3

Global Condition and Global Waiver – AACG 8.6.3 Creating Access Controls: To ensure the effective creation of access controls, it’s imperative to note that each control is based on a model, and every model is reliant on entitlements. Consequently, it … Read More

SQL Query for Retrieving Inactive Users in Oracle Fusion BI Report

SELECT * FROM “FUSION”.”ASE_USER_VL” “ASE_USER_VL” where EXISTS (select ‘1’ from “FUSION”.”PER_USERS” “PER_USERS” where UPPER(“ASE_USER_VL”.”USER_DISPLAY_NAME”) = UPPER(“PER_USERS”.”USERNAME”) AND (“PER_USERS”.”ACTIVE_FLAG” = ‘N’ OR “PER_USERS”.”SUSPENDED” = ‘Y’))

SQL Query for Extracting Role, Duty Role, and Effective Date Navigation Path in Oracle Fusion BI Report

select “ASE_USER_ROLE_MBR”.”USER_ID” as “USER_ID”, (select “ASE_USER_VL”.”USER_DISPLAY_NAME” from “FUSION”.”ASE_USER_VL” “ASE_USER_VL” where “ASE_USER_VL”.”USER_ID” = “ASE_USER_ROLE_MBR”.”USER_ID”) as “USER_DISPLAY_NAME”, “ASE_USER_ROLE_MBR”.”ROLE_ID” as “JOB_ROLE_ID”, (select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “ASE_USER_ROLE_MBR”.”ROLE_ID”) as “JOB_ROLE_NAME”, “ASE_USER_ROLE_MBR”.”EFFECTIVE_START_DATE” as “EFFECTIVE_START_DATE”, “ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” as “EFFECTIVE_END_DATE” from “FUSION”.”ASE_USER_ROLE_MBR” “ASE_USER_ROLE_MBR” where (“ASE_USER_ROLE_MBR”.”EFFECTIVE_END_DATE” … Read More

Retrieving Role, Duty Role, and Privileges Navigation Path in Oracle Fusion BI Report with SQL Query

select DISTINCT “C”.”Job Role id”, “C”.”Job Role Name”, “C”.”Duty Role id”, (select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “C”.”Duty Role id”) as “Duty Role Name”, “ASE_PRIV_ROLE_MBR”.”PRIVILEGE_ID” as “PRIVILEGE_ID”, (select “ASE_PRIVILEGE_VL”.”NAME” from “FUSION”.”ASE_PRIVILEGE_VL” “ASE_PRIVILEGE_VL” where “ASE_PRIVILEGE_VL”.”PRIVILEGE_ID” = “ASE_PRIV_ROLE_MBR”.”PRIVILEGE_ID”) as “Privilege_name”, … Read More

Retrieving Oracle Fusion BI Report Role, Duty, and Navigation Path using SQL Query

select “A”.”Job_Role_id”, (select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “A”.”Job_Role_id”) as “Job_Role_Name”, “A”.”Duty_Role_id”, “A”.”Duty_Role_Name”, “A”.”ROLE_TYPE_NAME”, “A”.”Navigation_path” from (select distinct connect_by_root “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” as “Job_Role_id”, NVL(“ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”,”ASE_ROLE_VL”.”ROLE_ID”) as “Duty_Role_id”, NVL((select “ASE_ROLE_VL”.”ROLE_NAME” from “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL” where “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”), “ASE_ROLE_VL”.”ROLE_NAME”) as “Duty_Role_Name”, (select … Read More

Comprehensive SQL Query for Retrieving Data Access across Various Security Context Types

You can utilize the subsequent SQL queries to retrieve information from the “Manage Data Access for Users” task, which encompasses Security Context Types and values, User names, and their associated roles. Executing the provided queries enables the extraction of data … Read More

Workday Application Security Role Creation and Assignment Guide

The “Workday Application Security Role Creation and Assignment Guide” is a comprehensive resource for effectively managing security roles and groups in the Workday application. Workday is a widely used platform for human capital management and financial management, making security role … Read More

Unlocking Insights: Creating Infolets with Reports & Analytics in Oracle Fusion Application

Infolets function as customizable information portals located on your homepage, delivering insightful reports that are generated from your transactional data. To build these Infolets, navigate to the Report & Analytics section and generate your report. Utilize the views functionality to … Read More