Oracle Applications Knowledge Sharing- Ajay Atre

Sometimes we need to regenerate the DSNO file again, usually we perform this activity for troubleshooting purpose-
Steps below depicts how to run DSNO program again.


When a customer is enabled for EDI DSNO transaction where in we define the EDI setups in e-commerce responsibility.
We then ship an order using shipping transaction form
in Request SRS - we see program getting fired as Outbound Triggering process(Triggering Process Outbound) program. This is DSNO generator program which generates DSNO file into file directory (E-Commerce profile)


So in concurrent program we don't see any such program exists as Outbound Triggering process rather exists Triggering Process Outbound
Assign Triggering Process Outbound to desired responsibility


Run the Triggering Process Outbound program with parameter as
File name in the first argument#1 as Pickup Stop ID


you can get the Pickup Stop ID from below sqls


SELECT
   fcr.request_date "Date Released"
  , fcr.argument2 "DSNO_file"
  , wdl.delivery_id "Delivery"
  , fcr.argument5 "Pickup Stop ID"
  , usr.user_name "User ID"
     FROM
    FND_CONCURRENT_REQUESTS fcr
  , wsh_new_deliveries wnd
  , wsh_delivery_legs wdl
  , fnd_user usr
    WHERE
    fcr.argument5           = TO_CHAR (wdl.pick_up_stop_id)
    AND wdl.delivery_id = wnd.delivery_id
    and fcr.argument2 like 'DSNO%'
    and fcr.requested_by = usr.user_id
    order by 1 desc, 2, 3, 4;




SELECT
fcr.argument2 "DSNO_file"
, fcr.request_date "Date Released"
, wdl.delivery_id "Delivery"
, wdl.pick_up_stop_id "Pickup Stop ID"
, wts.trip_id
, wnd.asn_seq_number
, wnd.confirmed_By "User_ID"
, wnd.organization_id
, mp.organization_code
FROM
FND_CONCURRENT_REQUESTS fcr
, wsh_new_deliveries wnd
, wsh_delivery_legs wdl
, wsh_trip_stops wts
, mtl_parameters mp
WHERE
wnd.delivery_id = wdl.delivery_id
and wdl.pick_up_stop_id = wts.stop_id
and TO_CHAR (wdl.pick_up_stop_id) = fcr.argument5
AND fcr.argument2 LIKE 'DSNO%'
and wnd.organization_id = mp.organization_id;

--Script to pull menus, submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT 
menu_id,
         (SELECT menu_name
            FROM FND_MENUS menu
           WHERE menu_id = menuent.menu_id)
            menu_name,
         (SELECT USER_MENU_NAME
            FROM FND_MENUS_vl menu
           WHERE menu_id = menuent.menu_id)
            USER_MENU_NAME,
         ENTRY_SEQUENCE,
         PROMPT,
         DESCRIPTION,
         GRANT_FLAG,
         SUB_MENU_ID,
         (SELECT MENU_NAME
            FROM FND_MENUS_VL sub
           WHERE sub.menu_id = menuent.SUB_MENU_ID)
            SUB_MENU_name,
         FUNCTION_ID,
         CASE
            WHEN Function_id IS NOT NULL
            THEN
               (SELECT FUNCTION_name
                  FROM fnd_form_functions fun
                 WHERE fun.function_id = menuent.FUNCTION_ID)
         END
            FUNCTION_name,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATE_LOGIN
   FROM FND_MENU_ENTRIES_VL menuent
--WHERE (MENU_Id in (83718,83719))
ORDER BY 2, ENTRY_SEQUENCE;








--Script to pull responsibility,request_group, Menu submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT responsibility_id,
       RESPONSIBILITY_NAME,
       (SELECT REQUEST_GROUP_NAME
          FROM fnd_request_groups rg
         WHERE rg.REQUEST_GROUP_ID = fr.REQUEST_GROUP_ID)
          REQUEST_GROUP_NAME,
       fr.LAST_UPDATE_DATE,
       fr.LAST_UPDATED_BY,
       fr.CREATED_BY,
       fr.CREATION_DATE,
       fr.LAST_UPDATE_LOGIN,
       a.*
  FROM apps.fnd_responsibility_vl fr,
       (  SELECT menu_id,
                 (SELECT menu_name
                    FROM FND_MENUS menu
                   WHERE menu_id = menuent.menu_id)
                    menu_name,
                 (SELECT USER_MENU_NAME
                    FROM FND_MENUS_vl menu
                   WHERE menu_id = menuent.menu_id)
                    USER_MENU_NAME,
                 ENTRY_SEQUENCE,
                 PROMPT,
                 DESCRIPTION,
                 GRANT_FLAG,
                 SUB_MENU_ID,
                 (SELECT MENU_NAME
                    FROM FND_MENUS_VL sub
                   WHERE sub.menu_id = menuent.SUB_MENU_ID)
                    SUB_MENU_name,
                 FUNCTION_ID,
                 CASE
                    WHEN Function_id IS NOT NULL
                    THEN
                       (SELECT FUNCTION_name
                          FROM fnd_form_functions fun
                         WHERE fun.function_id = menuent.FUNCTION_ID)
                 END
                    FUNCTION_name,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATED_BY,
                 CREATION_DATE,
                 LAST_UPDATE_LOGIN
            FROM FND_MENU_ENTRIES_VL menuent
        ORDER BY 2, ENTRY_SEQUENCE) a
 WHERE fr.menu_id = a.menu_id;

DECLARE
   -- This will prints the whole menu tree for a given responsibility, run for responsibility as a parameters into v_resp_name


   v_resp_name   VARCHAR2 (100) := '&RESPONSIBILITY_NAME';
   v_menu_id     NUMBER;
   v_main_menu   VARCHAR2 (100);


   PROCEDURE get_menu (p_menu_id IN VARCHAR2, p_level IN NUMBER)
   IS
      CURSOR cur_get_submenus
      IS
           SELECT e.entry_sequence,
                  m.menu_name,
                  m.user_menu_name,
                  e.sub_menu_id,
                  e.function_id,
                  f.function_name,
                  e.prompt,
                  f.web_html_call,
                  f.user_function_name,
                  e.grant_flag
             FROM fnd_menus_vl m,
                  fnd_menu_entries_vl e,
                  fnd_form_functions_vl f
            WHERE     e.sub_menu_id = m.menu_id(+)
                  AND e.function_id = f.function_id(+)
                  AND e.menu_id = p_menu_id
         ORDER BY 1;
      l_spaces   VARCHAR2 (30) := '';
      granted    VARCHAR2 (30);
   BEGIN
      -- for addting space
      FOR i IN 1 .. p_level
      LOOP
         l_spaces := l_spaces || '..';
      END LOOP;

      FOR c IN cur_get_submenus
      LOOP
         granted := '';
         IF c.grant_flag = 'Y'
         THEN
            granted := ' [granted]';
         END IF;
         IF c.sub_menu_id IS NULL
         THEN
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'FUNCTION- '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.function_name
               || ' ('
               || c.user_function_name
               || ')');
         -- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
         ELSE
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'MENU ('
               || p_level
               || ') '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.menu_name
               || ' ('
               || c.user_menu_name
               || ')');
         END IF;
         get_menu (c.sub_menu_id, p_level + 1);
      END LOOP;
   END;

BEGIN
   SELECT menu_id
     INTO v_menu_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = v_resp_name;
   SELECT menu_name
     INTO v_main_menu
     FROM fnd_menus
    WHERE menu_id = v_menu_id;
   DBMS_OUTPUT.put_line ('MAIN MENU- ' || v_main_menu);
   get_menu (v_menu_id, 1);
END;

-- ----------------------------------------------------------------------------------------
--  Query To get All Notifications Sent By A Particular Workflow
-- ----------------------------------------------------------------------------------------
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type =  '&item_type'
and  wias.item_key =  '&item_key';

-- -----------------------------------------------------------------------------------------------------------------------------
-- To Check Workflow Mailer Up or down
-- -----------------------------------------------------------------------------------------------------------------------------
SELECT component_name as Component, component_status as Status FROM fnd_svc_components WHERE component_type = 'WF_MAILER'
-- --------------------------------------------------------------------------------------------------------------
--  Query To get the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key
-- --------------------------------------------------------------------------------------------------------------
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/
-- -------------------------------------------------------------------------------------------
-- Query To get Errored Workflow Activities For A Particular Item Type/ Item Key
-- -------------------------------------------------------------------------------------------
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
-- ---------------------------------------------------------------------------------------------------
-- Query To get Errored Process Activity Statuses For A Particular Item Type/Item Key
-- ----------------------------------------------------------------------------------------------------
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
-- ---------------------------------------------------------------------------------------------
--  Query To get The Errored Activities For A Particular Item Type/Item Key
-- ---------------------------------------------------------------------------------------------
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/
 Query To get Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper('&item_type')
   AND item_key = nvl('&item_key',
                      item_key)
/
-- -----------------------------------------------------------------------------------------------
--  Query To get Out Number Of Deferred Workflow Activities
-- -----------------------------------------------------------------------------------------------
SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = 'DEFERRED'
      --AND was.item_type = 'REQAPPRV'
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;
 -- ------------------------------------------------------------------------------------------------
 --  Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
 -- ------------------------------------------------------------------------------------------------
SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';                         
-- --------------------------------------------------------------------------------------------------
--  Query To get Records That Are Pending In Each Of The Workflow Agent Listener Queues
-- --------------------------------------------------------------------------------------------------
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
       ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
       ' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            'and corrid like ''' || t.correlation_id || ''' ',
            NULL) || 'having count(*)>0;'
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';

-- -----------------------------------------------------------------------------------
-- For finding COncurrent program attached to responsibility
-- -----------------------------------------------------------------------------------
SELECT                                                   
         DISTINCT fcpt.user_concurrent_program_name, frt.responsibility_name
    --   frg.request_group_name,
    --   frgu.request_unit_type,
    -- frgu.request_unit_id,
    --,decode(instance,'CFGAPPRD','FAP1C','N')
    FROM apps.fnd_Responsibility fr,                  
         apps.fnd_responsibility_tl frt,
         apps.fnd_request_groups frg,
         apps.fnd_request_group_units frgu,
         apps.fnd_concurrent_programs_tl fcpt
   WHERE     frt.responsibility_id = fr.responsibility_id
         AND frg.request_group_id = fr.request_group_id
         AND frgu.request_group_id = frg.request_group_id
         AND fcpt.concurrent_program_id = frgu.request_unit_id
         AND frt.language = USERENV ('LANG')
         AND fcpt.language = USERENV ('LANG')
         AND fcpt.user_concurrent_program_name LIKE
                '%%'
                ORDER BY 2;
-- -----------------------------------------------------------------------------------
-- Query to get Menus & Functions attached to Responsibility.
-- -----------------------------------------------------------------------------------
  SELECT LEVEL,
                  LPAD (' ', (LEVEL-1)*3)||prompt,
                  Description
    FROM  fnd_menu_entries_vl fme
 WHERE  prompt IS NOT NULL
       AND grant_flag='Y'
      AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='M'
                      AND frf.action_id = NVL(fme.sub_menu_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)
   AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='F'
                      AND frf.action_id = NVL(fme.function_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)                    
 CONNECT BY PRIOR sub_menu_id = menu_id
   AND prompt IS NOT NULL
   AND NOT EXISTS (SELECT 1
                     FROM fnd_resp_functions frf,
                          fnd_responsibility_vl frv
                    WHERE frv.responsibility_id = frf.responsibility_id
                      AND frf.application_id = frv.application_id
                      AND frf.rule_type='M'
                      AND frf.action_id = NVL(fme.menu_id,-1)
                      AND frv.responsibility_name=:RESP_NAME)
 START WITH menu_id = (SELECT menu_id
                         FROM fnd_responsibility_vl
                        WHERE responsibility_name=:RESP_NAME)
ORDER SIBLINGS BY entry_sequence
-- -----------------------------------------------------------------------------------
-- Find all menus to which function is attached
-- -----------------------------------------------------------------------------------
select fme.menu_id
,FM.MENU_NAME
,fme.entry_sequence
,fff.function_name
from fnd_menu_entries_vl fme
,fnd_menus_vl fm
,fnd_form_functions_vl fff
where fme.menu_id=fm.menu_id
and fme.function_id=fff.function_id
and function_name LIKE 'XX%';
-- -----------------------------------------------------------------------------------
-- Query for finding menu exclusion for a responsibility
-- -----------------------------------------------------------------------------------
select res.responsibility_name
     , app.application_name
     , res.responsibility_key
     , res.description
     , res.start_date
     , res.end_date
     , dat.data_group_name
     , apd.application_name
     , mnu.menu_name
     , req.request_group_name
     , apr.application_name
     , decode(exc.rule_type,'F','Function','M', 'Menu', rule_type) Exclusion_Type
     , decode(exc.rule_type,'F',(select function_name || ',' || description
                                   from fnd_form_functions_vl fnc
                                  where fnc.function_id = exc.action_id
                                )
                           ,'M',(select menu_name || ',' || description
                                   from fnd_menus_vl imn
                                   where imn.menu_id = exc.action_id
                                )
                           , to_char(exc.action_id)
             ) Excluded_Menu_Or_func
  from apps.fnd_responsibility_vl res
     , apps.fnd_application_vl    app
     , apps.fnd_data_groups       dat
     , apps.fnd_menus_vl          mnu
     , apps.fnd_request_groups    req
     , apps.fnd_application_vl    apd
     , apps.fnd_application_vl    apr
     , apps.fnd_resp_functions    exc
 where res.application_id            = app.application_id
   and res.data_group_id             = dat.data_group_id
   and res.data_group_application_id = apd.application_id
   and res.menu_id                   = mnu.menu_id
   and req.request_group_id          = res.request_group_id
   and req.application_id            = res.group_application_id
   and apr.application_id            = req.application_id
   and exc.application_id            = res.application_id
   and exc.responsibility_id         = res.responsibility_id
   and responsibility_name ='APAC IT Support';
  
 -- -----------------------------------------------------------------------------------
 -- To find responsibilities assigned to particular user
 -- -----------------------------------------------------------------------------------
 SELECT resp_type 
       ,user_id 
       ,user_name 
       ,responsibility_id 
       ,responsibility_name 
       ,start_date 
       ,end_date 
       ,application_short_name 
       ,application_name 
 FROM  (SELECT 'DIRECT' resp_type 
              ,fu.user_id 
              ,fu.user_name 
              ,resp.responsibility_id 
              ,resp.responsibility_name 
              ,frd.start_date 
              ,frd.end_date 
              ,app.application_short_name 
              ,app.application_name 
        FROM  fnd_user                    fu 
             ,fnd_user_resp_groups_direct frd 
             ,fnd_responsibility_vl       resp 
             ,fnd_application_vl          app 
        WHERE fu.user_id            = frd.user_id 
        AND   frd.responsibility_id = resp.responsibility_id 
        AND   resp.application_id   = app.application_id 
        UNION ALL 
        SELECT 'INDIRECT' resp_type 
              ,fu.user_id 
              ,fu.user_name 
              ,resp.responsibility_id 
              ,resp.responsibility_name 
              ,fri.start_date 
              ,fri.end_date 
              ,app.application_short_name 
              ,app.application_name 
        FROM  fnd_user                      fu 
             ,fnd_user_resp_groups_indirect fri 
             ,fnd_responsibility_vl         resp 
             ,fnd_application_vl            app 
        WHERE fu.user_id           = fri.user_id 
        AND  fri.responsibility_id = resp.responsibility_id 
        AND  resp.application_id   = app.application_id) 
 WHERE 1=1 
 AND   user_name           = 'AJTEST'                -- Comment this if you need all user of a responsibility 
 AND   responsibility_name = 'System Administrator'; -- Comment this if you need all responsibilities of a user 

 -- --------------------------------------------------------------------------------------
 -- Profile Option
 -- --------------------------------------------------------------------------------------

select user_profile_option_name ,sum(level_value)
from fnd_profile_option_values fp
    ,fnd_profile_options_vl fpv
where fp.profile_option_id=fpv.profile_option_id
and level_id =10004
group by fpv.user_profile_option_name
having sum(level_value)>2;

 -- -----------------------------------------------------------------------------------
 -- Query to get the profile option values
 -- -----------------------------------------------------------------------------------
 SELECT b.user_profile_option_name "Long Name" ,
  a.profile_option_name "Short Name" ,
  NVL(g.responsibility_name,c.level_value)  "Level Value" ,
  c.PROFILE_OPTION_VALUE "Profile Value",
  b.sql_validation
FROM apps.fnd_profile_options a ,
  apps.FND_PROFILE_OPTIONS_VL b ,
  apps.FND_PROFILE_OPTION_VALUES c ,
  apps.FND_USER d ,
  apps.FND_USER e ,
  apps.FND_RESPONSIBILITY_VL g ,
  apps.FND_APPLICATION h
WHERE 1                   =1
AND a.profile_option_name = b.profile_option_name
AND a.profile_option_id   = c.profile_option_id
AND a.application_id      = c.application_id
AND c.last_updated_by     = d.user_id (+)
AND c.level_value         = e.user_id (+)
AND c.level_value         = g.responsibility_id (+)
AND c.level_value         = h.application_id (+)
  --
AND c.level_id            = 10003
AND g.responsibility_name = 'APAC IT Support'
ORDER BY b.user_profile_option_name,  c.level_id
;
-- -----------------------------------------------------------------------------------
-- Query for form function details
-- -----------------------------------------------------------------------------------
SELECT DISTINCT u.user_name, rtl.responsibility_name, ff.function_name, ffl.user_function_name
           FROM fnd_compiled_menu_functions cmf,
                fnd_form_functions ff,
                fnd_form_functions_tl ffl,
                fnd_responsibility r,
                fnd_responsibility_tl rtl,
                fnd_user_resp_groups urg,
                fnd_user u
          WHERE cmf.function_id = ff.function_id
            AND r.menu_id = cmf.menu_id
            AND urg.responsibility_id = r.responsibility_id
            AND rtl.responsibility_id = r.responsibility_id
            AND cmf.grant_flag = 'Y'
            AND r.application_id = urg.responsibility_application_id
            AND u.user_id = urg.user_id
            AND UPPER (ffl.user_function_name) = UPPER ('Returns')   --CES DEU1 Purchasing Plant Super User
            AND FF.FUNCTION_ID = FFL.FUNCTION_ID
       ORDER BY u.user_name;
   
-- -----------------------------------------------------------------------------------
-- Query to get all fuctions attached to responsibilities
-- -----------------------------------------------------------------------------------

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;
-- -----------------------------------------------------------------------------------
-- Following are the FND_PROFILE values that can be used in the PL/SQL code:
-- -----------------------------------------------------------------------------------
   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');
Following are the FND_GLOBAL values that can be used in the PL/SQL code:
   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;
  
 -- -----------------------------------------------------------------------------------
 -- AOL Defeinitions
 -- -----------------------------------------------------------------------------------
--CONCURRENT_PROGRAM_NAME
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM FND_CONCURRENT_PROGRAMS_TL
WHERE LANGUAGE = 'US'
AND APPLICATION_ID = 50001
ORDER BY USER_CONCURRENT_PROGRAM_NAME;
--FORM
SELECT USER_FORM_NAME , FORM_NAME
FROM FND_FORM_VL
WHERE APPLICATION_ID = 50001
ORDER BY 1,2;
--FORM FUNCTIONS
SELECT USER_FUNCTION_NAME
FROM FND_FORM_FUNCTIONS_VL
WHERE FORM_ID IN (SELECT FORM_ID
                  FROM FND_FORM_VL
                  WHERE APPLICATION_ID = 50001)
ORDER BY 1;
--LOOKUP
SELECT DISTINCT LOOKUP_TYPE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'XX%'
ORDER BY LOOKUP_TYPE;
SELECT LOOKUP_TYPE,LOOKUP_CODE
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE LIKE 'XX%'
AND ENABLED_FLAG = 'Y'
ORDER BY LOOKUP_TYPE,LOOKUP_CODE;
--REQUEST GROUPS
SELECT fr.REQUEST_GROUP_NAME,fp.USER_CONCURRENT_PROGRAM_NAME
FROM FND_REQUEST_GROUPS fr
    , FND_REQUEST_GROUP_UNITS fru
    , FND_CONCURRENT_PROGRAMS_TL fp
WHERE fr.REQUEST_GROUP_ID  = fru.REQUEST_GROUP_ID
AND fru.REQUEST_UNIT_ID = fp.CONCURRENT_PROGRAM_ID
AND fp.LANGUAGE = 'US'
AND fru.REQUEST_UNIT_TYPE = 'P'
AND  fr.APPLICATION_ID = 50001
UNION
SELECT fr.REQUEST_GROUP_NAME,frs.USER_REQUEST_SET_NAME
FROM FND_REQUEST_GROUPS fr
    , FND_REQUEST_GROUP_UNITS fru
    , FND_REQUEST_SETS_TL frs
WHERE fr.REQUEST_GROUP_ID  = fru.REQUEST_GROUP_ID
AND fru.REQUEST_UNIT_ID = frs.REQUEST_SET_ID
AND frs.LANGUAGE = 'US'
AND fru.REQUEST_UNIT_TYPE = 'S'
AND  fr.APPLICATION_ID = 50001
ORDER BY 1,2;
--MENUS--
SELECT fm.MENU_NAME , PROMPT
     , (CASE WHEN SUB_MENU_ID IS NOT NULL THEN (SELECT MENU_NAME
                                                FROM FND_MENUS
                                                WHERE MENU_ID = fmv.SUB_MENU_ID )
             WHEN fmv.FUNCTION_ID IS NOT NULL THEN (SELECT USER_FUNCTION_NAME
                                                 FROM FND_FORM_FUNCTIONS_TL f
                                                 WHERE f.FUNCTION_ID = fmv.FUNCTION_ID
                                                 AND f.LANGUAGE = 'US')
             END) MENU_FUNCTION
FROM FND_MENU_ENTRIES_VL fmv
  , FND_MENUS fm
WHERE fm.MENU_ID = fmv.MENU_ID
AND fm.MENU_NAME LIKE 'XX%'

-- --------------------------------------------------------------------------------------------
-- Form Personalization query
-- --------------------------------------------------------------------------------------------
Select Distinct
    A.Id,
    A.Form_Name ,
    A.Enabled,
    C.User_Form_Name,
    D.Application_Name ,
    A.Description,
    Ca.Action_Type,
    Ca.Enabled,
    Ca.Object_Type,
    ca.message_type,
    ca.message_text
from
    FND_FORM_CUSTOM_RULES a,
    FND_FORM b,
    FND_FORM_TL c,
    Fnd_Application_Tl D,
    Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
    And B.Form_Id = C.Form_Id
    And B.Application_Id = D.Application_Id
    --And D.Application_Id = 230 --For Order Management
    And C.User_Form_Name Like 'Inventory%'  --All the Forms that Start with Sales
    And A.Enabled ='Y'
    and a.id = ca.rule_id
--------------------------------------------------------------------------------------------
select ffv.form_id , ffv.form_name, ffv.user_form_name, ffv.description, ffcr.description, ffcr.sequence
from fnd_form_vl ffv,fnd_form_custom_rules ffcr
where ffv.form_name = ffcr.form_name
and ffcr.description like '%7663%';

-- -----------------------------------------------------------------------------------------
-- Template and its bursting path
-- -----------------------------------------------------------------------------------------
select
lt.application_short_name || '.' || lt.template_code || '.' || lt.default_language || '.' || lt.default_territory as burst_template_xdo_location
from
apps.xdo_templates_b lt
where lt.template_code ='XXAR0387'
-- -------------------------------------------------------------------------------------------
-- Query to Find Only Required fields in a particular DFF
-- -------------------------------------------------------------------------------------------
-- Query to Find All the fields in a particular DFF:-
SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title;
-- Query to Find Only Required fields in a particular DFF
SELECT *
  FROM (  SELECT ffv.descriptive_flexfield_name DFFName,
                 ffv.application_table_name TableName,
                 ffv.title Title,
                 ap.application_name Application,
                 att.column_seq_num SegmentNumber,
                 att.form_left_prompt SegmentName,
                 att.application_column_name,
                 fvs.flex_value_set_name ValueSet,
                 att.required_flag
            FROM apps.fnd_descriptive_flexs_vl ffv,
                 apps.fnd_descr_flex_contexts_vl ffc,
                 apps.fnd_descr_flex_col_usage_vl att,
                 apps.fnd_flex_value_sets fvs,
                 apps.fnd_application_vl ap
           WHERE     ffv.descriptive_flexfield_name =
                        att.descriptive_flexfield_name
                 AND ap.application_id = ffv.application_id
                 AND ffv.descriptive_flexfield_name =
                        ffc.descriptive_flexfield_name
                 AND ffv.application_id = ffc.application_id
                 AND ffc.descriptive_flex_context_code =
                        att.descriptive_flex_context_code
                 AND fvs.flex_value_set_id = att.flex_value_set_id
                 AND ffv.title IN ('Invoice Line Information')
        ORDER BY ffv.title)
 WHERE required_flag = 'Y';
-- --------------------------------------------------------------------------------------
-- Request id and its resp
-- --------------------------------------------------------------------------------------
select c.request_id, r.responsibility_name
from fnd_responsibility_vl r, fnd_concurrent_requests c
where c.request_id = 97131851
and c.responsibility_application_id = r.application_id
and c.responsibility_id = r.responsibility_id


-- --------------------------------------------------------------------------------------
-- FND_DATE.CANONICAL_TO_DATE (Oracle error we receive - Ora-01861)
-- --------------------------------------------------------------------------------------
Error - Oracle error we receive - Ora-01861 literal dose not match the format string


1. Date parameters for Oracle apps concurrent programs in Oracle Applications R12 must be set up using the FND_STANDARD_DATE valueset.
2. And The date value entered by the user at runtime is passed to the underlying program(PLSQL package, RDF etc.) as a DATE-TIME STRING and not as a DATE.
This format of  VARCHAR2 data item is "YYYY/MM/DD HH24:MI:SS" (e.g. "2019/05/10 00:00:00").
and we need value as 10-MAY-19
In order to use this VARCHAR2 value in a WHERE clause , it can be converted to a DATE value using the fnd_date.canonical_to_date function,
as shown in the following example:
Sample code is as below.
PROCEDURE XX(p_start_date IN VARCHAR2,ERRBUFF IN VARCHAR2,RETCODE IN
 VARCHAR2)
 IS
 l_start_date DATE;
 BEGIN
 l_start_date:=FND_DATE.CANONICAL_TO_DATE(p_start_date);
 END;




-- --------------------------------------------------------------------------------------
--find concurrent program usage since they have been installed -fnd_conc_prog_onsite_info
-- --------------------------------------------------------------------------------------
SELECT Distinct fa.application_short_name,
       fcpt.USER_CONCURRENT_PROGRAM_NAME,
       fcp.concurrent_program_name concurrent_program_short_name,
       fcp.concurrent_program_id,
       fe.EXECUTABLE_NAME,
       fe.execution_file_name,
(CASE
           WHEN REGEXP_COUNT (UPPER (fe.EXECUTION_FILE_NAME), '\.', 1) = 2
           THEN
              (SUBSTR (
                  SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
                            INSTR (UPPER (fe.EXECUTION_FILE_NAME),
                                   '.',
                                   1,
                                   1)
                          + 1),
                  1,
                    INSTR (SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
                                     INSTR (UPPER (fe.EXECUTION_FILE_NAME),
                                            '.',
                                            1,
                                            1)
                                   + 1),
                           '.')
                  - 1))
           WHEN REGEXP_COUNT (UPPER (fe.EXECUTION_FILE_NAME), '\.', 1) = 1
           THEN
              (SUBSTR (UPPER (fe.EXECUTION_FILE_NAME),
                       1,
                       INSTR (UPPER (fe.EXECUTION_FILE_NAME), '.') - 1))
           ELSE
              UPPER (fe.EXECUTION_FILE_NAME)
        END)
          OBJECT_NAME,
       fcp.executable_id,
       fe.EXECUTION_METHOD_CODE,
       flv.meaning,
       fcpoi.RESET_DATE,
       fcpoi.LAST_RUN_DATE,
       fcpoi.SUCCESSFUL_COMPLETION,
       fcpoi.WARNING_COMPLETION,
       fcpoi.ERROR_COMPLETION,
       (NVL(fcpoi.SUCCESSFUL_COMPLETION,0) + NVL(fcpoi.WARNING_COMPLETION,0) + NVL(fcpoi.ERROR_COMPLETION,0) )TOTAL_EXECUTIONS
  FROM fnd_conc_prog_onsite_info fcpoi,
       fnd_concurrent_programs_tl FCPT,
       fnd_concurrent_programs FCP,
       fnd_executables fe,
       fnd_lookup_values flv,
       fnd_application fa
 WHERE     fcpoi.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID
       AND fcpoi.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
       AND fcpt.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
       AND fcp.executable_id = fe.executable_id
       AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
       AND flv.LOOKUP_CODE = fe.EXECUTION_METHOD_CODE
       AND flv.Language ='US'
       AND FCP.application_id = fa.application_id
       AND (   fa.application_short_name LIKE 'XX%'
            OR fe.executable_name LIKE 'XX%'
            OR fe.executable_name LIKE 'FSSC%'
            OR fa.application_short_name LIKE 'NOETIX%')

EDI queries
-- -----------------------------------------------------------------------------------
-- Processing Rule - Address Level
-- -----------------------------------------------------------------------------------   
  SELECT distinct
     hcasa.ece_tp_location_code "Ship-to Location"
     ,hp.party_name
     ,hca.account_number
    , hl.city
    , hl.postal_code
    , hl.province
    , hl.county
    , hl.address1 || hl.address2 || hl.address3 || hl.address4
                                                                   address
    , mci.customer_item_number
    , mci.customer_item_desc
    , mif.item_number
    , mif.description
    , mcc.commodity_code
    , terms.customer_id
    -----------DEMAND FENCES---------------------------------------------
    , terms.pln_frozen_day_from
    , terms.pln_frozen_day_to 
    , terms.pln_firm_day_from  
    , terms.pln_firm_day_to 
    , terms.pln_forecast_day_from
    , terms.pln_forecast_day_to
    , terms.pln_mrp_forecast_day_from
    , terms.pln_mrp_forecast_day_to
    , terms.shp_frozen_day_from
    , terms.shp_frozen_day_to  
    , terms.shp_firm_day_from  
    , terms.shp_firm_day_to
    , terms.shp_forecast_day_from
    , terms.shp_forecast_day_to
    , terms.shp_mrp_forecast_day_from
    , terms.shp_mrp_forecast_day_to
    , terms.seq_frozen_day_from
    , terms.seq_frozen_day_to        
    , terms.seq_firm_day_from  
    , terms.seq_firm_day_to
    , terms.seq_forecast_day_from
    , terms.seq_forecast_day_to          
    , terms.seq_mrp_forecast_day_from 
    , terms.seq_mrp_forecast_day_to
----------------DEMAND mANAGEMENT--------------------------------------------
   -- , terms.schedule_hierarchy_code "Consume Demand Hierarchy Code"
    --,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
   -- , terms.unshipped_firm_disp_cd "ATS PreHorizon Disp Code"
   -- ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
   -- , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
    , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
    , terms.ship_delivery_rule_name "Ship Delivery Code"
    , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
                                           'H','Friday','J','Saturday','K','Sunday','N','As Directed',
                                           'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
                                           'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
                                           'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
                                           '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
                                           '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
    , terms.demand_tolerance_above "Demand Tolerance Positive"
    , terms.demand_tolerance_below "Demand Tolerance Negative"
    , terms.round_to_std_pack_flag "Std Pack Round to"
     , terms.std_pack_qty     "STD Pack Qty"
     -------------------Order Management------------------------------------
     , ooh.order_number       "Order Number"
     --, ott.NAME               "Sales Order Type"
     , terms.intransit_time   "Intransit Time"
     , terms.time_uom_code    "Intransit UOM Code"
     , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
     , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
    ---------------------CUM MANAGEMENT------------------------------------   
    --, terms.cum_control_code
   -- ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
   -- , terms.cum_org_level_code "CUM Org Level"
   -- , terms.cum_shipment_rule_code "Shipment Rule Code"
   -- , terms.cust_shipto_terms_id
    ---------------------GENERAL------------------------------------------
    --, terms.cust_assign_supplier_cd "Assigned Supplier Code"
    , terms.address_id
    , terms.customer_id
    , terms.ship_from_org_id
    , terms.ship_method
    , terms.intransit_time
    , terms.time_uom_code
    --, terms.cum_current_record_year
    --, terms.cum_previous_record_year
    , terms.cum_current_start_date
    , terms.cum_previous_start_date
   -- , terms.cum_yesterd_time_cutoff
    --, terms.cust_assign_supplier_cd
   -- , terms.customer_rcv_calendar_cd
    , terms.freight_code
    --, terms.supplier_shp_calendar_cd
    --, terms.unship_firm_cutoff_days
    , terms.use_edi_sdp_code_flag
    , terms.inactive_date
   -- , terms.match_across_key
    --, terms.match_within_key
    , terms.header_id
    , terms.price_list_id
    --, terms.critical_attribute_key
    , terms.customer_contact_id
    , terms.supplier_contact_id
    , terms.agreement_id
    , terms.agreement_name
    , terms.future_agreement_id
    , terms.future_agreement_name
    , terms.comments
    , terms.last_updated_by
    , terms.last_update_date
    , terms.creation_date
    , terms.created_by
    , terms.attribute_category
    , terms.attribute1
    , terms.attribute2
    , terms.attribute3
    , terms.attribute4
    , terms.attribute5
    , terms.attribute6
    , terms.attribute7
    , terms.attribute8
    , terms.attribute9
    , terms.attribute10
    , terms.attribute11
    , terms.attribute12
    , terms.attribute13
    , terms.attribute14
    , terms.attribute15
    , terms.last_update_login
    , terms.request_id
    , terms.program_application_id
    , terms.program_id
    , terms.program_update_date
    , terms.tp_attribute1
    , terms.tp_attribute2
    , terms.tp_attribute3
    , terms.tp_attribute4
    , terms.tp_attribute5
    , terms.tp_attribute6
    , terms.tp_attribute7
    , terms.tp_attribute8
    , terms.tp_attribute9
    , terms.tp_attribute10
    , terms.tp_attribute11
    , terms.tp_attribute12
    , terms.tp_attribute13
    , terms.tp_attribute14
    , terms.tp_attribute15
    , terms.tp_attribute_category
    --, terms.intransit_calc_basis
    --,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
    , terms.default_ship_from
    , terms.pln_frozen_flag
    , terms.shp_frozen_flag
    , terms.seq_frozen_flag
    , terms.issue_warning_drop_parts_flag
    , terms.org_id
    , terms.blanket_number
    , terms.release_rule
    , terms.release_time_frame
    , terms.release_time_frame_uom
    , terms.exclude_non_workdays_flag
    , terms.disable_create_cum_key_flag
 FROM apps.mtl_customer_items mci
    , apps.mtl_commodity_codes mcc
      , apps.XXAR4385_RA_CUST_R12_V racust
      , apps.XXAR4385_RA_ADDRESS_R12_V raadd
    , apps.rlm_cust_shipto_terms_all terms,
     APPS.HZ_PARTIES HP,
     APPS.HZ_PARTY_SITES HPS,
     HZ_CUST_ACCT_SITES_ALL HCASA,
       HZ_LOCATIONS HL,
       HZ_CUST_ACCOUNTS HCA
    , apps.mtl_item_flexfields mif
    , apps.mtl_customer_item_xrefs mcix
    , apps.oe_order_headers_All ooh
    --, oe_transaction_types_tl ott
WHERE terms.address_id = mci.address_id
AND
  terms.address_id IS NOT NULL
  AND  hp.party_id=hps.party_id
  and    hps.party_site_id=hcasa.party_site_id
  and    hps.location_id=hl.location_id               
  AND   hca.party_id=hp.party_id
  AND hca.cust_account_id = terms.customer_id
  AND hcasa.cust_acct_site_id = terms.address_id
  AND mci.commodity_code_id = mcc.commodity_code_id
  AND mif.inventory_item_id = mcix.inventory_item_id
  AND mif.organization_id = mcix.master_organization_id
  AND mcix.customer_item_id = mci.customer_item_id
   and terms.ship_from_org_id in (select organization_id from org_organization_definitions where organization_code in ('JES','MHF') )
  AND mcix.preference_number =
        (SELECT MIN (preference_number)
           FROM mtl_customer_item_xrefs
          WHERE customer_item_id = mci.customer_item_id
            AND inactive_flag <> 'Y')
  and ooh.header_id=terms.header_id
--  and  hca.account_number=:customer_number
  and hcasa.ece_tp_location_code is not null;
 
  -- -----------------------------------------------------------------------------------
  -- Processing Rule - Customer level
  -- -----------------------------------------------------------------------------------
 
 
SELECT  (select organization_code from org_organization_definitions where organization_id=terms.ship_from_org_id)"Organization code"
         , racust.account_number
         , hp.party_name
       , terms.pln_frozen_day_from 
       , terms.pln_frozen_day_to  
       , terms.pln_firm_day_from   
       , terms.pln_firm_day_to  
       , terms.pln_forecast_day_from
       , terms.pln_forecast_day_to
       , terms.pln_mrp_forecast_day_from
       , terms.pln_mrp_forecast_day_to
       , terms.shp_frozen_day_from 
       , terms.shp_frozen_day_to   
       , terms.shp_firm_day_from   
       , terms.shp_firm_day_to
       , terms.shp_forecast_day_from
       , terms.shp_forecast_day_to
       , terms.shp_mrp_forecast_day_from 
       , terms.shp_mrp_forecast_day_to
       , terms.seq_frozen_day_from 
       , terms.seq_frozen_day_to         
       , terms.seq_firm_day_from   
       , terms.seq_firm_day_to
       , terms.seq_forecast_day_from 
       , terms.seq_forecast_day_to           
       , terms.seq_mrp_forecast_day_from  
       , terms.seq_mrp_forecast_day_to
       ,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
       ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
       , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
       , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
       , terms.ship_delivery_rule_name "Ship Delivery Code"
       , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
                                              'H','Friday','J','Saturday','K','Sunday','N','As Directed',
                                              'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
                                              'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
                                              'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
                                              '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
                                              '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
       , terms.demand_tolerance_above "Demand Tolerance Positive"
       , terms.demand_tolerance_below "Demand Tolerance Negative"
       , terms.round_to_std_pack_flag "Std Pack Round to"
        , terms.std_pack_qty     "STD Pack Qty"
        , ooh.order_number       "Order Number"
       , terms.intransit_time   "Intransit Time"
        , terms.time_uom_code    "Intransit UOM Code"
        , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
        ,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
        , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
       , terms.cum_control_code
       ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
       , terms.cum_org_level_code "CUM Org Level"
       , terms.cum_shipment_rule_code "Shipment Rule Code"
       , terms.cust_shipto_terms_id
       , terms.cust_assign_supplier_cd "Assigned Supplier Code"
       , terms.address_id
       , terms.customer_id
       , terms.ship_from_org_id
       , terms.ship_method
       , terms.cum_current_record_year
       , terms.cum_previous_record_year
       , terms.cum_current_start_date
       , terms.cum_previous_start_date
       , terms.cum_yesterd_time_cutoff
       , terms.cust_assign_supplier_cd
       , terms.customer_rcv_calendar_cd
       , terms.freight_code
       , terms.supplier_shp_calendar_cd
       , terms.unship_firm_cutoff_days
       , terms.use_edi_sdp_code_flag
       , terms.inactive_date
       , terms.match_across_key
       , terms.match_within_key
       , terms.header_id
       , terms.price_list_id
       , terms.critical_attribute_key
       , terms.customer_contact_id
       , terms.supplier_contact_id
       , terms.agreement_id
       , terms.agreement_name
       , terms.future_agreement_id
       , terms.future_agreement_name
       , terms.comments
       , terms.last_updated_by
       , terms.last_update_date
       , terms.creation_date
       , terms.created_by
       , terms.attribute_category
       , terms.attribute1
       , terms.attribute2
       , terms.attribute3
       , terms.attribute4
       , terms.attribute5
       , terms.attribute6
       , terms.attribute7
       , terms.attribute8
       , terms.attribute9
       , terms.attribute10
       , terms.attribute11
       , terms.attribute12
       , terms.attribute13
       , terms.attribute14
       , terms.attribute15
       , terms.last_update_login
       , terms.request_id
       , terms.program_application_id
       , terms.program_id
       , terms.program_update_date
       , terms.tp_attribute1
       , terms.tp_attribute2
       , terms.tp_attribute3
       , terms.tp_attribute4
       , terms.tp_attribute5
       , terms.tp_attribute6
       , terms.tp_attribute7
       , terms.tp_attribute8
       , terms.tp_attribute9
       , terms.tp_attribute10
       , terms.tp_attribute11
       , terms.tp_attribute12
       , terms.tp_attribute13
       , terms.tp_attribute14
       , terms.tp_attribute15
       , terms.tp_attribute_category
       , terms.intransit_calc_basis
       , terms.default_ship_from
       , terms.pln_frozen_flag
       , terms.shp_frozen_flag
       , terms.seq_frozen_flag
       , terms.issue_warning_drop_parts_flag
       , terms.org_id
       , terms.blanket_number
       , terms.release_rule
       , terms.release_time_frame
       , terms.release_time_frame_uom
       , terms.disable_create_cum_key_flag
    FROM   apps.hz_cust_accounts racust
         ,apps.hz_parties hp
       , apps.rlm_cust_shipto_terms_all terms
       , apps.org_organization_definitions orgdef
       , apps.oe_order_headers_All ooh
   WHERE 
    racust.cust_account_id = terms.customer_id
     and  hp.party_id=racust.party_id
     AND orgdef.organization_id = terms.ship_from_org_id
     AND terms.address_id IS NULL
      and ooh.header_id=terms.header_id; 
  
-- -----------------------------------------------------------------------------------
--    Processing Rule - Item Level
-- -----------------------------------------------------------------------------------
SELECT distinct  cust_acct.account_number customer_number ,acct_site.CUST_ACCT_SITE_ID 
        ,loc.location_id 
       , mci.customer_item_number 
       , mci.customer_item_desc 
       , mif.item_number 
       , mif.description 
       , mcc.commodity_code 
       , terms.customer_id 
       , terms.pln_frozen_day_from  
       , terms.pln_frozen_day_to   
       , terms.pln_firm_day_from    
       , terms.pln_firm_day_to   
       , terms.pln_forecast_day_from 
       , terms.pln_forecast_day_to 
       , terms.pln_mrp_forecast_day_from 
       , terms.pln_mrp_forecast_day_to 
       , terms.shp_frozen_day_from  
       , terms.shp_frozen_day_to    
       , terms.shp_firm_day_from    
       , terms.shp_firm_day_to 
       , terms.shp_forecast_day_from 
       , terms.shp_forecast_day_to 
       , terms.shp_mrp_forecast_day_from  
       , terms.shp_mrp_forecast_day_to 
       , terms.seq_frozen_day_from  
       , terms.seq_frozen_day_to          
       , terms.seq_firm_day_from    
       , terms.seq_firm_day_to 
       , terms.seq_forecast_day_from  
       , terms.seq_forecast_day_to            
       , terms.seq_mrp_forecast_day_from   
       , terms.seq_mrp_forecast_day_to 
       , terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
       , terms.ship_delivery_rule_name "Ship Delivery Code" 
       , decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday', 
                                              'H','Friday','J','Saturday','K','Sunday','N','As Directed', 
                                              'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday', 
                                              'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday', 
                                              'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday', 
                                              '16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately', 
                                              '21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern" 
       , terms.demand_tolerance_above "Demand Tolerance Positive"
       , terms.demand_tolerance_below "Demand Tolerance Negative" 
       , terms.round_to_std_pack_flag "Std Pack Round to"
        , terms.std_pack_qty "STD Pack Qty"
         , ooh.order_number "Order Number"
        , terms.intransit_time "Intransit Time"
        , terms.time_uom_code  "Intransit UOM Code"
        , decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
        , terms.exclude_non_workdays_flag "Exclude Non-Workdays"
       , terms.address_id 
       , terms.customer_id 
       , terms.ship_from_org_id 
       , terms.ship_method 
       , terms.intransit_time 
       , terms.time_uom_code 
      , terms.cum_current_start_date 
       , terms.cum_previous_start_date 
       , terms.freight_code 
       , terms.use_edi_sdp_code_flag 
       , terms.inactive_date 
       , terms.header_id 
       , terms.price_list_id 
       , terms.customer_contact_id 
       , terms.supplier_contact_id 
       , terms.agreement_id 
       , terms.agreement_name 
       , terms.future_agreement_id 
       , terms.future_agreement_name 
       , terms.comments 
       , terms.last_updated_by 
       , terms.last_update_date 
       , terms.creation_date 
       , terms.created_by 
       , terms.attribute_category 
       , terms.attribute1 
       , terms.attribute2 
       , terms.attribute3 
       , terms.attribute4 
       , terms.attribute5 
       , terms.attribute6 
       , terms.attribute7 
       , terms.attribute8 
       , terms.attribute9 
       , terms.attribute10 
       , terms.attribute11 
       , terms.attribute12 
       , terms.attribute13 
       , terms.attribute14 
       , terms.attribute15 
       , terms.last_update_login 
       , terms.request_id 
       , terms.program_application_id 
       , terms.program_id 
       , terms.program_update_date 
       , terms.tp_attribute1 
       , terms.tp_attribute2 
       , terms.tp_attribute3 
       , terms.tp_attribute4 
       , terms.tp_attribute5 
       , terms.tp_attribute6 
       , terms.tp_attribute7 
       , terms.tp_attribute8 
       , terms.tp_attribute9 
       , terms.tp_attribute10 
       , terms.tp_attribute11 
       , terms.tp_attribute12 
       , terms.tp_attribute13 
       , terms.tp_attribute14 
       , terms.tp_attribute15 
       , terms.tp_attribute_category 
        , terms.default_ship_from 
       , terms.pln_frozen_flag 
       , terms.shp_frozen_flag 
       , terms.seq_frozen_flag 
       , terms.issue_warning_drop_parts_flag 
       , terms.org_id 
       , terms.blanket_number 
       , terms.release_rule 
       , terms.release_time_frame 
       , terms.release_time_frame_uom 
       , terms.exclude_non_workdays_flag 
       , terms.disable_create_cum_key_flag 
    FROM apps.mtl_customer_items mci 
       , apps.mtl_commodity_codes mcc 
        ,hz_parties party  
          ,hz_cust_accounts cust_acct 
          ,hz_party_sites party_site 
         ,hz_locations loc 
         ,hz_cust_acct_sites_all acct_site 
         , apps.rlm_cust_item_terms_all terms 
       , apps.mtl_item_flexfields mif 
       , apps.mtl_customer_item_xrefs mcix 
       , apps.oe_order_headers_All ooh 
    WHERE terms.customer_item_id = mci.customer_item_id 
     AND terms.address_id IS NOT NULL 
    And cust_acct.CUST_ACCount_ID = terms.customer_id 
            AND cust_acct.PARTY_ID = party.party_id 
     and party.party_id=party_site.party_id 
     and acct_site.party_site_id = party_site.party_site_id 
      AND loc.location_id = party_site.location_id 
      AND acct_site.cust_acct_site_id=terms.address_id 
     AND mci.commodity_code_id = mcc.commodity_code_id 
     AND mif.inventory_item_id = mcix.inventory_item_id 
     AND mif.organization_id = mcix.master_organization_id 
     AND mcix.customer_item_id = mci.customer_item_id 
     AND mcix.preference_number = 
           (SELECT MIN (preference_number) 
              FROM mtl_customer_item_xrefs 
             WHERE customer_item_id = mci.customer_item_id 
               AND inactive_flag <> 'Y') 
     and ooh.header_id=terms.header_id
     and terms.ship_from_org_id in (625,626);
 
-- -----------------------------------------------------------------------------------
-- Trading Partner Query
-- -----------------------------------------------------------------------------------
SELECT    etg.tp_group_code           "Group"
              ,etg.tp_group_description  "Description"
              ,hp.party_name         "Customer Name"--rc.customer_name          "Customer Name"       --mk
              ,HCA.ACCOUNT_NUMBER        "Customer_number" --,rc.CUSTOMER_NUMBER   "Customer Number"  --mk
              ,HL.address1||' '||HL.city||' '||HL.state     "Site Name"
              ,hcasa.ece_tp_location_code   "EDI Location"
              ,eth.tp_code                     "Partner"
              ,eth.tp_description            "Description"
              ,eth.tp_reference_ext1      "Reference1"
              ,eth.tp_reference_ext2      "Reference2"
              ,sub1.document                "Transaction"
              ,sub1.document_type2       "Type"
              ,sub1.translator_code         "Translator Code"  
              ,sub1.document_standard   "Document Standard"
              ,sub1.map_code                "Map"
              ,pvs.ece_tp_location_code  "TP Location Code"
              ,sub1.document_type
              ,sub1.edi_flag
              ,sub1.print_flag
              ,sub1.test_flag
              ,sub1.ATTRIBUTE_CATEGORY "TP Transaction"
              ,sub1.ATTRIBUTE1 "Account No"
              ,sub1.ATTRIBUTE2 "Account Name"
              ,sub1.ATTRIBUTE3 "Bank Name"
              ,eth.ATTRIBUTE_CATEGORY "Trading Partner"
              ,eth.attribute9 "Remit To Name"
              ,eth.attribute10 "Remit To Street"
              ,eth.attribute11 "Remit To City"
              ,eth.attribute12 "Remit To State"
              ,eth.attribute13 "Remit To Postal Code"
              ,eth.attribute14 "Remit To Country"
              ,eth.attribute15 "Remit To VAT NO"
FROM           hz_cust_accounts          hca     --ra_addresses_all         ra
             ,po_vendor_sites_all      pvs
             ,ap_bank_branches  ab
             ,hr_locations            hr
             ,ece_tp_headers      eth
             ,ece_tp_group         etg
             ,hz_parties          hp             --ra_customers         rc --mk
             ,hz_party_sites      hps            --mk
             ,hz_locations        hl             --mk
             ,hz_cust_acct_sites_All  hcasa      --mk
            ,( SELECT    etd.document_id,
                              etd.document_type,
                              etd.translator_code,
                              etd.edi_flag,
                              etd.print_flag,
                              etd.test_flag,
                              elv1.meaning document,
                              elv2.meaning document_type2,
                              etd.tp_header_id,
                              etd.document_standard,
                              em.map_code,
                              etd.ATTRIBUTE_CATEGORY,
                              etd.ATTRIBUTE1,
                              etd.ATTRIBUTE2,
                              etd.ATTRIBUTE3
               FROM      ece_tp_details etd,
                             ece_lookup_values elv1,
                             ece_lookup_values elv2,
                             ece_mappings       em
               WHERE    elv1.lookup_type = 'DOCUMENT'
               AND        elv1.lookup_code = etd.document_id
              AND        etd.map_id = em.map_id
              AND        elv1.enabled_flag = 'Y'
              AND        elv2.lookup_type = etd.document_id||':DOCUMENT_TYPE'
              AND        elv2.lookup_code = etd.document_type
              AND        elv2.enabled_flag = 'Y'
             ) sub1
WHERE  etg.tp_group_id = eth.tp_group_id
AND       eth.tp_header_id = pvs.tp_header_id (+)
AND      eth.tp_header_id = hcasa.tp_header_id (+)  --mk
AND      eth.tp_header_id = ab.tp_header_id (+)
AND      eth.tp_header_id = hr.tp_header_id (+)
AND      eth.tp_header_id = sub1.tp_header_id
AND      eth.tp_header_id = hcasa.tp_header_id
AND      hca.party_id = hp.party_id              --mk                             
AND      HP.PARTY_ID=HPS.PARTY_ID                --mk
AND      HPS.LOCATION_ID=HL.LOCATION_ID          --mk
AND      HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID   --mk
--AND      HCA.ACCOUNT_NUMBER=:account_number 
--AND     hp.party_name=:party_name
--and     hcasa.ece_tp_location_code=:EDI_LOCATION
ORDER BY     etg.tp_group_code, eth.tp_code