ODI BIAPPS find Forward and Backward Dependancy

In ODI, many times we want to know where 
i) a table is used as source (Forward)
ii) tables used to populate the target table (Backward)

The following procedure recursively scans the usage of yellow interfaces as source tables as well as lookups and determine the Forward and Backward Dependancy. There is a column available in output known as Lineage Path which shows the path to determine the dependancy.

The Path column shows the lineage of dependency in following format

InterfaceName : POP_ID(Target Table) -> Next Lineage

For eg as below:

SIL_GroupAccountReverse_GetReversalIntID.W_REVERSAL_INT_ID_TMP_SQ_W_GL_OTHER_F:45089002(Temp)»SIL_GroupAccountReverse_GetReversalIntID.W_REVERSAL_INT_ID_TMP:34912002(W_REVERSAL_INT_ID_TMP)

For forward dependency,    it shows the various targets populated by the table mentioned

For backward dependency, it shows the various sources used to populated the table mentioned

           Step 1- Compile the following objects

drop type dependancy_tab;

drop type dependancy_row;

create type dependancy_row as object ( trace_type varchar2(400), table_name varchar2(400), parent_folder varchar2(400), folder varchar2(400), scenario varchar2(400), dependant_table varchar2(400), lineage_path varchar2(4000) );

/

create type dependancy_tab is table of dependancy_row;

/

Step 2 - Compile the following function

CREATE OR REPLACE FUNCTION find_dependancy(
    p_table_list VARCHAR2,
    p_load_plan  NUMBER DEFAULT 1)
  RETURN dependancy_tab
IS
  l_result_tab dependancy_tab := dependancy_tab();
  l_path_string VARCHAR2(32627);
  l_tab_list    VARCHAR2(32627);
  l_scen_name   VARCHAR2(4000);
  l_table_name  VARCHAR2(30);
  o_table_name  VARCHAR2(30);
type l_tab_type
IS
  TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(4000);
  l_scen l_tab_type;
  --Backward Trace
PROCEDURE backward_trace(
    l_pop   NUMBER,
    l_level NUMBER)
IS
  CURSOR c1
  IS
    SELECT DISTINCT src.res_name source_table_name,
      snp_source_tab.i_pop_sub,
      snp_pop.pop_name AS interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_table src
    WHERE snp_pop.i_pop         = l_pop
    AND snp_data_set.i_data_set = snp_source_tab.i_data_set (+)
    AND snp_pop.i_pop           = snp_data_set.i_pop (+)
    AND snp_table.i_table(+)    = snp_pop.i_table
    AND src.i_table (+)         = snp_source_tab.i_table;
BEGIN
  FOR r1 IN c1
  LOOP
    IF r1.source_table_name IS NOT NULL THEN
      l_path_string         := l_path_string||'»'||r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
      l_result_tab.extend;
      l_result_tab(l_result_tab.last) := dependancy_row('Backward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.source_table_name,l_path_string);
      --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||r1.source_table_name||'|'||l_path_string);
    ELSE
      backward_trace(r1.i_pop_sub, l_level+1);
    END IF;
  END LOOP;
END;
--Trace Backward Main
PROCEDURE trace_backward
IS
  CURSOR c_main
  IS
    SELECT DISTINCT snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name,
      src.res_name source_table_name,
      snp_source_tab.i_pop_sub,
      snp_folder_parent.folder_name parent_folder_name,
      snp_folder.folder_name,
      snp_pop.pop_name interface_name,
      snp_table.res_name target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_table src,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_package,
      snp_scen,
      snp_step,
      snp_folder,
      snp_folder snp_folder_parent
    WHERE src.i_table (+)          = snp_source_tab.i_table
    AND snp_folder.i_folder        = snp_pop.i_folder
    AND snp_folder_parent.i_folder = snp_folder.par_i_folder
      --AND snp_folder_parent.folder_name in ('SILOS','PLP','CUSTOM_SILOS','CUSTOM_SDE_ORAR1213_Adaptor','CUSTOM_PLP','SDE_ORAR1213_Adaptor','Mappings')
    AND snp_data_set.i_data_set = snp_source_tab.i_data_set
    AND snp_pop.i_pop           = snp_data_set.i_pop
    AND snp_table.i_table       = snp_pop.i_table
    AND snp_table.res_name      = o_table_name
    AND snp_package.i_package   = snp_step.i_package
    AND snp_step.i_pop          = snp_pop.i_pop
    AND snp_package.i_package   = snp_scen.i_package
    AND ((p_load_plan           = 1
    AND snp_scen.scen_version  IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan <> 1);
  BEGIN
    FOR r1 IN c_main
    LOOP
      IF r1.source_table_name IS NOT NULL THEN
        l_path_string         := r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
        l_scen_name           := r1.scen_name;
        l_result_tab.extend;
        l_result_tab(l_result_tab.last) := dependancy_row('Backward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.source_table_name,l_path_string);
        --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||r1.source_table_name||'|'||l_path_string);
      ELSE
        backward_trace(r1.i_pop_sub, 1);
      END IF;
    END LOOP;
  END;
  --Forward Trace
PROCEDURE forward_trace(
    l_pop   NUMBER,
    l_level NUMBER)
IS
  CURSOR c1
  IS
    SELECT DISTINCT snp_pop.pop_name AS interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_data_set,
      snp_pop,
      snp_table
    WHERE l_pop                     = snp_source_tab.i_pop_sub
    AND snp_data_set.i_data_set (+) = snp_source_tab.i_data_set
    AND snp_pop.i_pop (+)           = snp_data_set.i_pop
    AND snp_table.i_table(+)        = snp_pop.i_table;
  CURSOR c2(c_pop NUMBER)
  IS
    SELECT DISTINCT snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name
    FROM snp_scen,
      snp_package,
      snp_step,
      snp_pop,
      snp_folder,
      snp_folder snp_folder_parent
    WHERE snp_package.i_package    = snp_step.i_package
    AND snp_step.i_pop             = snp_pop.i_pop
    AND snp_pop.i_pop              = c_pop
    AND snp_folder.i_folder        = snp_pop.i_folder
    AND snp_folder_parent.i_folder = snp_folder.par_i_folder
    AND snp_package.i_package      = snp_scen.i_package
    AND ((p_load_plan              = 1
    AND snp_scen.scen_version     IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan <> 1);
  BEGIN
    FOR r1 IN c1
    LOOP
      l_path_string := l_path_string ||'»' ||r1.interface_name ||':' ||r1.i_pop ||'(' ||r1.target_name ||')';
      FOR r2 IN c2(r1.i_pop)
      LOOP
        l_scen(r2.scen_name) := r1.target_name;
      END LOOP;
      forward_trace(r1.i_pop, l_level +1);
    END LOOP;
  END;
  --Trace Forward Main
PROCEDURE trace_forward
IS
  l_tab l_tab_type;
  CURSOR c_main
  IS
    SELECT DISTINCT src.res_name source_table_name,
      snp_folder_parent.folder_name
      ||'|'
      ||snp_folder.folder_name
      ||'|'
      ||snp_scen.scen_name scen_name,
      snp_pop.pop_name interface_name,
      nvl2(snp_pop.i_pop, nvl2(snp_pop.i_table, snp_table.res_name, 'Temp'), NULL) target_name,
      snp_pop.i_pop
    FROM snp_source_tab,
      snp_table src,
      snp_folder,
      snp_folder snp_folder_parent,
      snp_data_set,
      snp_pop,
      snp_table,
      snp_step,
      snp_package,
      snp_scen
    WHERE src.i_table                  = snp_source_tab.i_table
    AND src.res_name                   = o_table_name
    AND snp_folder.i_folder            = snp_pop.i_folder
    AND snp_folder_parent.i_folder     = snp_folder.par_i_folder
    --AND snp_folder_parent.folder_name IN ('SILOS','PLP','CUSTOM_SILOS','CUSTOM_SDE_ORAR1213_Adaptor','CUSTOM_PLP','SDE_ORAR1213_Adaptor','Mappings')
    AND snp_data_set.i_data_set        = snp_source_tab.i_data_set
    AND snp_pop.i_pop                  = snp_data_set.i_pop
    AND snp_table.i_table(+)           = snp_pop.i_table
    AND snp_step.i_pop (+)             = snp_pop.i_pop
    AND snp_package.i_package (+)      = snp_step.i_package
    AND snp_package.i_package          = snp_scen.i_package (+)
    AND ((p_load_plan                  = 1
    AND snp_scen.scen_version         IN
      (SELECT MAX(snp_scen_ver.scen_version)
      FROM snp_lpi_step,
        snp_scen snp_scen_ver
      WHERE snp_lpi_step.scen_name = snp_scen_ver.scen_name
      AND snp_scen.scen_name       = snp_scen_ver.scen_name
      ))
    OR p_load_plan           <> 1
    OR snp_scen.scen_version IS NULL);
  BEGIN
    l_tab.delete;
    FOR r1 IN c_main
    LOOP
      l_path_string := r1.interface_name||':'||r1.i_pop||'('||r1.target_name||')';
      l_scen.delete;
      forward_trace(r1.i_pop, 2);
      l_scen_name         := NULL;
      l_scen_name         := l_scen.first;
      IF l_scen_name      IS NOT NULL THEN
        WHILE l_scen_name IS NOT NULL
        LOOP
          l_table_name := l_scen(l_scen_name);
          l_result_tab.extend;
          l_result_tab(l_result_tab.last) := dependancy_row('Forward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),l_table_name,l_path_string);
          --dbms_output.put_line(o_table_name||'|'||l_scen_name||'|'||l_table_name||'|'||l_path_string);
          l_tab(l_table_name) := l_table_name;
          l_scen_name         := l_scen.next(l_scen_name);
        END LOOP;
      ELSIF r1.target_name <> 'Temp' THEN
        l_result_tab.extend;
        l_scen_name                     := r1.scen_name;
        l_result_tab(l_result_tab.last) := dependancy_row('Forward',o_table_name,SUBSTR(l_scen_name,1,instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|')+1,instr(l_scen_name,'|',1,2)-instr(l_scen_name,'|')-1), SUBSTR(l_scen_name,instr(l_scen_name,'|',1,2)+1),r1.target_name,l_path_string);
        --dbms_output.put_line(o_table_name||'|||||'||l_path_string);
      END IF;
    END LOOP;
  END;
  BEGIN
    l_tab_list := p_table_list;
    --dbms_output.put_line('Target Table|Parent Folder|Folder|Scenario|Source Table|Backward Path');
    WHILE rtrim(l_tab_list,',') IS NOT NULL
    LOOP
      o_table_name    := rtrim(SUBSTR(l_tab_list, instr(l_tab_list, ',')+1),',');
      IF o_table_name IS NOT NULL THEN
        trace_backward;
        trace_forward;
      END IF;
      l_tab_list := rtrim(REPLACE(l_tab_list,o_table_name),',');
    END LOOP;
    RETURN l_result_tab;
  END;
  /

Step 3- Run the following query to view output

select * 

from table(find_dependancy('W_GL_ACCOUNT_D,W_NATURAL_ACCOUNT_D'))

where scenario is not null; -- Filter the orphan lineages

Avego Real Time Ride Sharing System, Share and Go-Green

I have recently come across this wonderful mobile phone app which was not possible in the last decade. A real time ride sharing system using smart phones.

Though there are many applications in the market for car pooling, a real time ride sharing system is one of it's kind. It allows you to make your single driven car into a ride sharing system.

Sean'O Sullivan a venture capitalist and humanitarian, is the CEO of this new company and I wish this product to be a success story across the world.

Sean O' Sullivan is credited by MIT university to coin the term cloud computing. His company way back in 1990 was the pioneer in cloud computing technology. A Polytechnic student and computer programmer, his built a multi-million product in 1990s and did exit strategy to follow his passion for movies. He went to Iran to capture a documentary and became a humanitarian starting a new organization for war-throne Iraq. His organization Jump-Info was instrumental in rebuiling the Iraq. Now he is a venture capitalist, movie maker, actor and enterpreneur.

Avego product features

1. Real time car sharing
2. Automatic Money Transfer Mechanism, where there will be an electronic wallet.
3. Security for authentication of passenger
4. Maps feature
5. Preference of riders about whom to share car with.
6. Completely free

Requirements

1. An i-Phone or Windows Smart Phone for riders
2. 3G connection
3. A mentality to share

I believe that it can be implemented in India too. With petrol price and the number of cars increasing day by day, it is high time to utilize such a wonderful product which is free for world.
Why can't we utilize such a beautiful idea and utilize it?
Like some products which are crowd-sourced, Avego also needs active participation from the people to male it a success. I wish that the middle-families in India use their smart phones to do something for the world and to reduce the petrol consumption and save something for future generations.

Avego is coming up with a next generation app supporting all types of smart phones viz i-Phone, Android and Windows Mobile.

Ways to go -green
1. Use bikes and cycles
2. Use a car pooling system
3. Reduce cars
4. Car-pooling

Speaking about Crowd-Sourcing another great product to try which is free.
www.waze.com
Collects data from your 3G phone to understand the traffic congestion in the path you are traversing.

References:

Avego Product Page
Avego Real-time Ridesharing Demo
AvegoVideos

Sean'O Sullivan profile in College Alma Mater page
Sean'O Sullivan interview
About Sean'O Sullivan
Who Coined Cloud Computing?

Oracle Discrete and Repetitive Schedule Manufacturing

Please use the following document to understand about Discrete and Repetitive manufacturing

Discrete and Repetitive Schedule

Useful FND Queries

http://onlyappsr12.blogspot.com/p/usefull-fnd-queries.html

1. Concurrent Program Info
SELECT fcp.user_concurrent_program_name Concurrent_Program,
  fdfcu.column_seq_num Sequence_number                    ,
  fdfcu.default_value default_value                       ,
  fdfcu.form_left_prompt prompt                           ,
  fdfcu.description description                           ,
  ffvs.flex_value_set_name                                ,
  fl.meaning                                              ,
  fe.executable_name                                      ,
  fe.execution_file_name
FROM apps.fnd_concurrent_programs_vl fcp,
  apps.fnd_lookups fl                   ,
  apps.fnd_executables fe               ,
  apps.fnd_descr_flex_col_usage_vl fdfcu,
  apps.fnd_application fa               ,
  apps.fnd_flex_value_sets ffvs
WHERE fcpt.user_concurrent_program_name = '&user_concurrent_program_name'
  AND fdfcu.application_id = fa.application_id
  AND fdfcu.descriptive_flex_context_code = 'Global Data Elements'
  AND fdfcu.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
  AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
  AND fcp.execution_method_code = fl.lookup_code
  AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
  AND fcp.executable_id = fe.executable_id
ORDER BY 1,
  2       ,
  3;


select cpt.USER_CONCURRENT_PROGRAM_NAME,
cpt.DESCRIPTION,
cp.CONCURRENT_PROGRAM_NAME,
a.APPLICATION_SHORT_NAME,
e.EXECUTABLE_NAME,
e.execution_file_name,
cp.CREATION_DATE,
uc.USER_NAME CREATOR,
cp.LAST_UPDATE_DATE,
ulu.USER_NAME UPDATER,
cp.ENABLED_FLAG,
cp.RUN_ALONE_FLAG,
cp.SRS_FLAG,
cp.PRINT_FLAG,
cp.SAVE_OUTPUT_FLAG,
cp.REQUIRED_STYLE,
cp.OUTPUT_PRINT_STYLE,
cp.PRINTER_NAME,
cp.MINIMUM_WIDTH,
cp.MINIMUM_LENGTH,
cp.OUTPUT_FILE_TYPE,
cp.ENABLE_TRACE,
cp.RESTART,
cp.NLS_COMPLIANT,
cp.REQUEST_SET_FLAG
from applsys.fnd_concurrent_programs cp,
applsys.fnd_concurrent_programs_tl cpt,
applsys.fnd_application a,
applsys.fnd_executables e,
applsys.fnd_user uc,
applsys.fnd_user ulu
where cp.CREATION_DATE > to_date('17-MAY-1995')-- Enter Creation Date
and cp.APPLICATION_ID = cpt.APPLICATION_ID
and cp.CONCURRENT_PROGRAM_ID = cpt.CONCURRENT_PROGRAM_ID
and cpt.LANGUAGE = sys_context('USERENV', 'LANG')
and cp.APPLICATION_ID = a.APPLICATION_ID
and cp.EXECUTABLE_APPLICATION_ID = e.APPLICATION_ID
and cp.EXECUTABLE_ID = e.EXECUTABLE_ID
and cp.CREATED_BY = uc.USER_ID
and cp.LAST_UPDATED_BY = ulu.USER_ID
and cpt.user_concurrent_program_name='Payables Open Interface Import'
order by cp.CONCURRENT_PROGRAM_NAME


2. Concurrent Program parameters

select cp.CONCURRENT_PROGRAM_NAME, cu.*, vs.FLEX_VALUE_SET_NAME
from applsys.fnd_concurrent_programs cp,
applsys.fnd_descr_flex_column_usages cu,
applsys.fnd_flex_value_sets vs
where cp.CREATION_DATE > to_date('17-MAY-1995') --Enter Creation Date
AND cu.application_id = cp.application_id
AND cu.descriptive_flexfield_name = '$SRS$.' || cp.concurrent_program_name
AND cu.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_ID
ANd cp.CONCURRENT_PROGRAM_ID=:CONCURRENT_PROGRAM_ID --(Enter CONCURRENT_PROGRAM_ID of CONCURRENT_PROGRAM)
order by cp.CONCURRENT_PROGRAM_NAME, cu.COLUMN_SEQ_NUM

3. Concurrent Request Details

select p.concurrent_program_name,
pt.user_concurrent_program_name,
rt.responsibility_name,
r.*
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_programs p,
applsys.fnd_concurrent_programs_tl pt,
applsys.fnd_responsibility rsp,
applsys.fnd_responsibility_tl rt
where r.request_id = &Request_ID -- Enter Request ID
and r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and p.application_id = pt.application_id
and p.concurrent_program_id = pt.concurrent_program_id
and pt.language = sys_context('USERENV', 'LANG')
and r.responsibility_application_id = rsp.application_id
and r.responsibility_id = rsp.responsibility_id
and rsp.application_id = rt.application_id
and rsp.responsibility_id = rt.responsibility_id
and rt.language = sys_context('USERENV', 'LANG')

4. Request Group Details

select rga.application_short_name "Req Grp App",
rg.request_group_name "Request Group Name",
rg.request_group_code "Request Group Code",
rg.description "Request Group Desc",
cp.concurrent_program_name "Concurrent Program",
ea.application_short_name "Exec App",
e.executable_name "Executable",
rga.application_id "Req Grp App ID",
rg.request_group_id "Req Grp ID",
e.application_id "Exec App ID"
from applsys.fnd_executables e,
applsys.fnd_concurrent_programs cp,
applsys.fnd_request_group_units rgu,
applsys.fnd_request_groups rg,
applsys.fnd_application rga,
applsys.fnd_application ea
where e.application_id like '%' -- Enter Application ID
and e.executable_name like '%' -- Enter Executable Name
and cp.executable_application_id = e.application_id
and cp.executable_id = e.executable_id
and cp.enabled_flag = 'Y'
and rgu.request_unit_id = cp.concurrent_program_id
and rgu.unit_application_id = cp.application_id
and rgu.request_unit_type = 'P'
and rg.application_id = rgu.application_id
and rg.request_group_id = rgu.request_group_id
and rga.application_id = rgu.application_id
and ea.application_id = e.application_id

5. Request Set Details

select rst.USER_REQUEST_SET_NAME,
rs.REQUEST_SET_NAME,
rsst_start.USER_STAGE_NAME start_stage,
rss.DISPLAY_SEQUENCE stage_sequence,
rss.STAGE_NAME,
rss.REQUEST_SET_STAGE_ID,
rss.SUCCESS_LINK,
rss.WARNING_LINK,
rss.ERROR_LINK,
rsst.USER_STAGE_NAME,
rsst.DESCRIPTION stage_description,
rsp.sequence program_sequence,
cp.user_concurrent_program_name,
pa.*
from applsys.fnd_request_sets_tl rst,
applsys.fnd_request_sets rs,
applsys.fnd_request_set_stages_tl rsst_start,
applsys.fnd_request_set_stages rss,
applsys.fnd_request_set_stages_tl rsst,
applsys.fnd_request_set_programs rsp,
applsys.fnd_concurrent_programs_tl cp,
applsys.fnd_request_set_program_args pa
where rs.REQUEST_SET_NAME like '%' -- Enter Request Set Name
and rs.application_id = rst.application_id
and rs.REQUEST_SET_ID = rst.REQUEST_SET_ID
and rst.language = sys_context('USERENV', 'LANG')
and rsst_start.SET_APPLICATION_ID = rs.application_id
and rsst_start.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst_start.REQUEST_SET_STAGE_ID = rs.START_STAGE
and rsst_start.language = 'US'
and rss.SET_APPLICATION_ID = rs.application_id
and rss.REQUEST_SET_ID = rs.REQUEST_SET_ID
and rsst.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsst.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsst.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsst.language = 'US'
and rsp.SET_APPLICATION_ID = rss.SET_APPLICATION_ID
and rsp.REQUEST_SET_ID = rss.REQUEST_SET_ID
and rsp.REQUEST_SET_STAGE_ID = rss.REQUEST_SET_STAGE_ID
and rsp.program_application_id = cp.application_id
and rsp.concurrent_program_id = cp.concurrent_program_id
and cp.language = 'US'
and pa.application_id (+) = rsp.SET_APPLICATION_ID
and pa.REQUEST_SET_ID (+) = rsp.REQUEST_SET_ID
and pa.REQUEST_SET_PROGRAM_ID (+) = rsp.REQUEST_SET_PROGRAM_ID
order by rst.USER_REQUEST_SET_NAME, rss.DISPLAY_SEQUENCE, rsp.sequence,
pa.DESCRIPTIVE_FLEX_APPL_ID, pa.DESCRIPTIVE_FLEXFIELD_NAME, pa.APPLICATION_COLUMN_NAME


1.Find Locks Info

SELECT sid, id1, id2, lmode, request, block
FROM v$lock
WHERE sid = &sid

2.Find Session Info

SELECT module, action, sid, serial# from v$session
where action like '%%'

3.Find Lock Mode

select a.sid, b.name, a.id1, a.lmode, a.request from v$lock a, sys.obj$ b
where b.obj# = a.id1

4.Find Locked Objects

select oracle_username, os_user_name, session_id, process,
locked_mode, o.owner, o.object_type, o.object_name
from v$locked_object l,
sys.dba_objects o
where l.object_id = o.object_id
order by o.owner, o.object_name, session_id

5.Control File Query

select * from v$controlfile

6.Folder Info Query

SELECT b.application_short_name,
d.responsibility_name,
a.OBJECT,
c.NAME Folder_Name
FROM apps.fnd_default_folders a,
apps.fnd_application b,
apps.fnd_folders c,
apps.fnd_responsibility_vl d
WHERE a.application_id = b.application_id
AND a.folder_id = c.folder_id
AND d.responsibility_id = - (a.user_id)
AND d.responsibility_name LIKE '%%'
ORDER BY b.application_short_name,
d.responsibility_name;

Concurrent Job Executed by User

SELECT fcr.REQUEST_ID,
 FU.USER_NAME,
 fcr.PHASE_CODE, 
 fcr.STATUS_CODE,
 (fcr.ACTUAL_COMPLETION_DATE – fcr.ACTUAL_START_DATE) * 24 * 60 TIME_MINS,    
 fcpt.USER_CONCURRENT_PROGRAM_NAME,
 fcp.CONCURRENT_PROGRAM_NAME,
 fcr.ACTUAL_START_DATE,
 fcr.ACTUAL_COMPLETION_DATE,
 fcp.CONCURRENT_PROGRAM_NAME
FROM apps.FND_CONCURRENT_REQUESTS FCR,
         apps.FND_CONCURRENT_PROGRAMS FCP,
         apps.FND_CONCURRENT_PROGRAMS_TL FCPT,
         apps.FND_USER FU
WHERE 1=1
 AND FCP.CONCURRENT_PROGRAM_ID=FCR.CONCURRENT_PROGRAM_ID
 AND FCPT.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
 AND FCR.REQUESTED_BY = FU.USER_ID
 AND FU.USER_NAME = ‘&user’
 ORDER BY FCR.REQUEST_ID DESC;

Use extended ASCII set as Delimtter for Oracle's q-quote operator in Oracle PL/SQL

Refer this blog to know about q-quote in Oracle
http://kerryosborne.oracle-guy.com/2009/08/quotes-in-strings-oracle-q-quote/

q'##' is a new format available in PL/SQL to declare quoted strings to avoid the escaping of quote(') character, where # or any other character defined here would be the new delimiter. Make sure that this delimiter character does not exist in .

A good approach here to avoid problems would be use the extra character available in ASCII as per he below table which will not be normally used in typed characters and can be a good delimitter to avoid most cases

CHR(128):€
CHR(129):
CHR(130):‚
CHR(131):ƒ
CHR(132):„
CHR(133):…
CHR(134):†
CHR(135):‡
CHR(136):ˆ
CHR(137):‰
CHR(138):Š
CHR(139):‹
CHR(140):Œ
CHR(141):
CHR(142):Ž
CHR(143):
CHR(144):
CHR(145):‘
CHR(146):’
CHR(147):“
CHR(148):”
CHR(149):•
CHR(150):–
CHR(151):—
CHR(152):˜
CHR(153):™
CHR(154):š
CHR(155):›
CHR(156):œ
CHR(157):
CHR(158):ž
CHR(159):Ÿ
CHR(160):
CHR(161):¡
CHR(162):¢
CHR(163):£
CHR(164):¤
CHR(165):¥
CHR(166):¦
CHR(167):§
CHR(168):¨
CHR(169):©
CHR(170):ª
CHR(171):«
CHR(172):¬
CHR(173):­
CHR(174):®
CHR(175):¯
CHR(176):°
CHR(177):±
CHR(178):²
CHR(179):³
CHR(180):´
CHR(181):µ
CHR(182):¶
CHR(183):·
CHR(184):¸
CHR(185):¹
CHR(186):º
CHR(187):»
CHR(188):¼
CHR(189):½
CHR(190):¾
CHR(191):¿
CHR(192):À
CHR(193):Á
CHR(194):Â
CHR(195):Ã
CHR(196):Ä
CHR(197):Å
CHR(198):Æ
CHR(199):Ç
CHR(200):È
CHR(201):É
CHR(202):Ê
CHR(203):Ë
CHR(204):Ì
CHR(205):Í
CHR(206):Î
CHR(207):Ï
CHR(208):Ð
CHR(209):Ñ
CHR(210):Ò
CHR(211):Ó
CHR(212):Ô
CHR(213):Õ
CHR(214):Ö
CHR(215):×
CHR(216):Ø
CHR(217):Ù
CHR(218):Ú
CHR(219):Û
CHR(220):Ü
CHR(221):Ý
CHR(222):Þ
CHR(223):ß
CHR(224):à
CHR(225):á
CHR(226):â
CHR(227):ã
CHR(228):ä
CHR(229):å
CHR(230):æ
CHR(231):ç
CHR(232):è
CHR(233):é
CHR(234):ê
CHR(235):ë
CHR(236):ì
CHR(237):í
CHR(238):î
CHR(239):ï
CHR(240):ð
CHR(241):ñ
CHR(242):ò
CHR(243):ó
CHR(244):ô
CHR(245):õ
CHR(246):ö
CHR(247):÷
CHR(248):ø
CHR(249):ù
CHR(250):ú
CHR(251):û
CHR(252):ü
CHR(253):ý
CHR(254):þ
CHR(255):ÿ

Oracle PL/SQL Find Matching Braces/Brackets or any character

It is normally difficult to find matching braces while parsing.

The below code snippet will help you to find the matching braces.

This will save the sub_strings into a record type with start_pos and end_pos.


The logic is like this

  Find first open brace
  Loop till last open brace
    Find first close brace after this open brace
    Loop
      Count number of open braces and close braces between this open brace and close brace.
      If open count = close count -- The logic sits here
         Then we have found a matching brace substring.
      End if;
      Find next close brace after this open brace
    End Loop 

    Find Next Open Brace
  Loop End

declare

l_expression varchar2(1000) := q'# ( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) ) ( 7 ( 8 ) ( 9 ) ) #';

type pos_record is record (open_pos number, end_pos number, value varchar2(400));

type pos_tab_type is table of pos_record index by binary_integer;

l_pos_tab pos_tab_type;

l_index number := 1;

l_open_pos number;

l_end_pos number;

l_open_count number;

l_end_count number;

l_sub_expression varchar2(1000);

begin

l_open_pos := instr(l_expression, '(', 1);

while l_open_pos > 0 loop

l_pos_tab(l_index).open_pos := l_open_pos;

l_end_pos := instr(l_expression, ')', l_open_pos+1);

l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);

l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));

l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));

while l_open_count <> l_end_count loop

l_end_pos := instr(l_expression, ')', l_end_pos+1);

l_sub_expression := substr(l_expression, l_open_pos, l_end_pos-l_open_pos+1);

l_open_count := length(l_sub_expression)-length(replace(l_sub_expression,'('));

l_end_count := length(l_sub_expression)-length(replace(l_sub_expression,')'));

end loop;

l_pos_tab(l_index).end_pos := l_end_pos;

l_pos_tab(l_index).value := l_sub_expression;

l_open_pos := instr(l_expression, '(', l_open_pos+1);

l_index := l_index + 1;

end loop;

l_index := l_pos_tab.first;

while l_index is not null loop

dbms_output.put_line(l_index||'>'||l_pos_tab(l_index).open_pos||'-'||l_pos_tab(l_index).end_pos||':'||l_pos_tab(l_index).value);

l_index := l_pos_tab.next(l_index);

end loop;

end;

Output

-------

1>2-36:( 1 ( 2 ( 3 ) ( 4 ) ) ( 5 ) ( 6 ) )

2>6-22:( 2 ( 3 ) ( 4 ) )

3>10-14:( 3 )

4>16-20:( 4 )

5>24-28:( 5 )

6>30-34:( 6 )

7>38-54:( 7 ( 8 ) ( 9 ) )

8>42-46:( 8 )

9>48-52:( 9 )

Out VARCHAR2 parameters in PL/SQL

The PL/SQL takes the maximum size of output variable for VARCHAR2 from the calling block/procedure.

Consider the following example.

--

create or replace package test_pkg as

procedure test_val(i_num in number, o_expression out varchar2);

end test_pkg;
--

create or replace package body test_pkg as

procedure test_val(i_num in number, o_expression out varchar2) as

begin

  o_expression := LPAD('>', i_num);

end;

end;

--

declare

  l_expression VARCHAR2(5);

begin

  test_pkg.test_val(i_num => 5,

  o_expression => l_expression);

  dbms_output.put_line('Output:'||l_expression);

end;

Output: >

The value of l_expression is 5. so the maximum permissible size of o_expression is 5. If it increases as per the below execution, it will give an exception.

Error report:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "DEMO.TEST_PKG", line 5

ORA-06512: at line 4

06502. 00000 - "PL/SQL: numeric or value error%s"

*Cause:

*Action: