Oracle Apps Sample Script of Receiving Open Interface

CLEAR BUFFER 
SET VERIFY OFF 
SET LINESIZE 140 
SET PAGESIZE 60 
SET ARRAYSIZE 1 
SET SERVEROUTPUT ON SIZE 100000 
SET FEEDBACK OFF 
SET ECHO OFF 

Script To List The Values Of A Profile Option At All Levels

Script:

SELECT fpo.profile_option_id, fpot.profile_option_name profile_short_name 
, substr(fpot.user_profile_option_name,1,60) profile_name 
, DECODE(fpov.level_id,10001,'site',10002,'Appl',10003,'Resp',10004,'User') profile_level 
, substr(DECODE(fpov.level_id,10001,null, 10002,fa.application_short_name 
,10003,fr.responsibility_name, 10004,fu.user_name),1,30) level_value 
, fpov.profile_option_value profile_value 
FROM fnd_profile_option_values fpov 

Workflow tables/queries in Oracle Apps

WORKFLOW TABLES

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS

SELECT * FROM WF_USER_ROLES

SELECT * FROM WF_ROLES

SELECT * FROM WF_ITEMS

How to Launch Workflow from PL/SQL

declare
   v_itemtype   VARCHAR2(50);
   v_itemkey    VARCHAR2(50);
   v_process    VARCHAR2(50);
   v_userkey    VARCHAR2(50);
begin
 

Bulk Binding in Native Dynamic SQL

Let’s look at bulk binding enhancements in native dynamic SQL

• Defining
• In-Binding
• Out-Binding
• Oracle 9I Enhancement For Bulk Fetch From Cursor Variable Assigned By Native Dynamic SQL 

Handling and Reporting Exceptions - Bulk Binding in Native Dynamic SQL

Consider a program to insert the elements in a PL/SQL collection into a database table. It’s possible that some elements might fail and that the designer would regard this as a non-fatal error and want to continue to insert subsequent elements. The explicit row by row implementation would handle the exception, and probably record it for subsequent review thus…

Out-Binding - Bulk Binding in Native Dynamic SQL

The progression is also supported for implicit query in a DML statement via the returning keyword…

declare

  type employee_ids_t is table of employees.employee_id%type

    index by binary_integer;

In-Binding - Bulk Binding in Native Dynamic SQL

The same progression (explicit row by row processing, bulk binding, bulk binding in native dynamic SQL) is supported for DML (insert, update and delete) thus…

declare

  type employee_ids_t is table of employees.employee_id%type

    index by binary_integer;

  employee_ids employee_ids_t;

begin

Defining - Bulk Binding in Native Dynamic SQL

Consider a program to populate elements of a PL/SQL collection from a SELECT query thus…

declare

  type employee_ids_t is table of employees.employee_id%type

    index by binary_integer;