Email Concurrent Program Output

#REM ============================================================
#REM  Description: This Shell script program accepts request_id, email and Subject as argument
#REM                     and Send email with attachment of output file for the request
#REM=============================================================

echo "Current Request :" $4
echo "Output Request :"$5
echo "Email :"$6
echo "Email Subject :"$7

fname=`sqlplus -s $1 <set head off
set feedback off
set verify off
select outfile_name from fnd_concurrent_requests
where request_id = $5
/
xxxEOFxxx`

echo "File name from SQL :"$fname
fname=`ls $fname`
echo "Actual file name :" $fname
if [ -z "$fname" ]
then
        echo "No request matching id $5"
elif [ -s $fname ]
then
        echo "Getting output file: $fname"
 echo "Sending Email to :" $6
            /usr/bin/uuencode $fname "$5.out" | mailx -s "$7" $6

else
        echo "Either file $fname does not exist or it is of 0 bytes"
fi

Lookup Tables

SELECT lookup_type, description
  FROM apps.fnd_lookup_types_vl
 WHERE lookup_type LIKE '%ICSO%'

SELECT *
  FROM apps.fnd_lookup_values
 WHERE lookup_type = 'RSOL_PREVENTION_HOLDS'

Alert Tables

SELECT alert_name,
       description,
       start_date_active,
       DECODE (alert_condition_type,  'P', 'Periodic',  'E', 'Event')
          ALERT_TYPE,
       frequency_type,
       insert_flag,
       update_flag
  FROM APPS.alr_alerts
 WHERE     enabled_flag = 'Y'
       AND end_date_active IS NULL
       AND alert_name LIKE 'AA%'
     
SELECT *
  FROM apps.ALR_ACTIONS_V
 WHERE subject LIKE 'XX%Notification%'

Order-Delivery-Item Complete Details

SELECT
DISTINCT
         oeh.flow_status_code header_status,
         oel.flow_status_code line_status,
         DECODE (wdd.released_status,
                 'S', 'SUBMITTED FOR RELEASE',
                 'R', 'READY TO RELEASE',
                 'B', 'BACKORDERED',
                 'Y', 'STAGED',
                 'C', 'SHIPPED',
                 'D', 'CANCELLED')
         released_status,          
         nvl(oel.invoice_interface_status_code, 'NO') ar_interfaced,    
         oeh.creation_date,
         (SELECT user_name
            FROM apps.fnd_user
           WHERE user_id = oeh.created_by)
            created_by,
         (SELECT user_name
            FROM apps.fnd_user
           WHERE user_id = oeh.last_updated_by)
            last_upd_by,
         (SELECT name
            FROM apps.oe_order_sources
           WHERE order_source_id = oeh.order_source_id)
            hdr_source,
         oeh.header_id,
         oeh.org_id header_org_id,
         (SELECT hou.name
            FROM hr_operating_units hou
           WHERE hou.organization_id = oeh.org_id)
            header_ou,        
         oeh.order_number,
         (SELECT ott.name
            FROM apps.oe_transaction_types_tl ott
           WHERE ott.transaction_type_id = oeh.order_type_id)
            order_type,
         oeh.cust_po_number,
         oeh.fob_point_code,
         oeh.shipping_method_code,
         (SELECT name
            FROM apps.ra_salesreps_all rsa
           WHERE rsa.salesrep_id = oeh.salesrep_id)
            salesrep_name,
         oeh.transactional_curr_code trx_curr,
         oeh.ship_to_contact_id, -- contact_id in ra_contacts, ship to contact
         oeh.sold_to_contact_id, -- contact_id in ra_contacts, customer level contact
         oeh.ship_to_org_id,     -- site_use_id in hz_cust_site_uses_all
         oeh.sold_to_org_id,     -- cust_account_id in hz_cust_accounts
         oel.creation_date line_creation_date,
         oel.last_update_date line_upd_date,
         (SELECT user_name
            FROM apps.fnd_user
           WHERE user_id = oel.last_updated_by)
            line_upd_by,        
         oel.line_id,
         RTRIM (
               oel.line_number
            || '.'
            || oel.shipment_number
            || '.'
            || oel.option_number
            || '.'
            || oel.component_number,
            '.')
            line_number,
         oel.org_id line_org_id,
         (SELECT hou.name
            FROM hr_operating_units hou
           WHERE hou.organization_id = oel.org_id)
            line_ou,
         (SELECT ROUND (SUM (cic.item_cost), 2)
            FROM apps.cst_item_costs cic
           WHERE     cic.inventory_item_id = MSI.inventory_item_id
                 AND cic.organization_id = MSI.organization_id
                 AND cic.cost_type_id = 1)
            cogs,
         oel.unit_selling_price usp,
         oel.tax_code,
         oel.tax_value,
         oel.ordered_quantity ord_qty,
         (SELECT ott.name
            FROM apps.oe_transaction_types_tl ott
           WHERE ott.transaction_type_id = oel.line_type_id)
            line_type,
         oel.schedule_ship_date,
         oel.promise_date,
         oel.latest_acceptable_date latest_acc_date,
         oel.request_date,
         oel.request_id,
         (SELECT a.order_number
            FROM apps.oe_order_headers_all a, apps.oe_order_lines_all b
           WHERE a.header_id = b.header_id AND b.line_id = oel.attribute17)
            icso_number,
         oel.attribute17 icso_line_id,
         (SELECT RTRIM (
                       oola.line_number
                    || '.'
                    || oola.shipment_number
                    || '.'
                    || oola.option_number
                    || '.'
                    || oola.component_number,
                    '.')
            FROM apps.oe_order_lines_all oola
           WHERE oola.line_id = oel.attribute17)
            icso_line_num,
         NVL (oel.attribute18, 'EXTERNAL SO') attribute18,
         oel.attribute19 pto_model,
         oel.global_attribute1 flag_3A4,
         oeh.Global_Attribute3 hdr_attr3,
         oel.Global_Attribute3 line_attr3,
         oeh.Global_Attribute4 hdr_attr4,
         oel.Global_Attribute4 line_attr4,
         oel.booked_flag,
         oel.open_flag,
         oeh.booked_date,
         (SELECT set_name
            FROM apps.oe_sets
           WHERE set_id = oel.ship_set_id)
            ship_set_name,
         oel.subinventory,
         oel.top_model_line_id,        
         msi.item_type,
         msi.inventory_item_id item_id,
         msi.segment1 item_num,
         msi.shippable_item_flag,  
         (SELECT ood.organization_name
            FROM apps.org_organization_definitions ood
           WHERE ood.organization_id = msi.default_shipping_org)
            default_shipping_org,
         oel.ship_from_org_id,  
         (SELECT ood.organization_name
            FROM apps.org_organization_definitions ood
           WHERE ood.organization_id = oel.ship_from_org_id)
            ship_from_org,
         wdd.last_update_date del_upd_date,
         (SELECT user_name
            FROM apps.fnd_user
           WHERE user_id = wdd.last_updated_by)
            del_upd_by,
         wda.delivery_id,
         wdd.delivery_detail_id,
         wdd.move_order_line_id,
         wdd.request_id del_request_id,
         mtrl.from_subinventory_code from_subinv,
         mtrl.to_subinventory_code to_subinv,
         mtrl.quantity_delivered qty_delivered,
         mtrl.pick_slip_number,
         mtrl.pick_slip_date,
         oel.actual_shipment_date        
    FROM apps.oe_order_headers_all oeh,
         apps.oe_order_lines_all oel,
         wsh.wsh_delivery_details wdd,
         apps.wsh_delivery_assignments wda,
         apps.mtl_system_items msi,
         apps.mtl_txn_request_lines mtrl
   WHERE     oeh.header_id = oel.header_id
         AND oel.line_id = wdd.source_line_id(+)
         AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
         AND msi.inventory_item_id = oel.inventory_item_id
         AND oel.ship_from_org_id = msi.organization_id
         AND mtrl.line_id(+) = wdd.move_order_line_id
         AND oeh.order_number IN ('1885000297')
         --AND oeh.header_id = '1344613'
         --AND oeh.order_type_id <> 1037
         --AND oel.global_attribute1 = 'TRANSMITTED'
         --AND oel.org_id = 26
         -- AND oel.line_id IN (6269374)
         --AND oel.line_number = 104357064
         --AND oel.flow_status_code NOT IN ('CLOSED', 'CANCELLED')  
         --AND oel.inventory_item_id in (1928882, 1928414, 1927654, 1927835)
         --AND msi.organization_id = 26
         --AND msi.segment1 = '22013144'
         --AND msi.shippable_item_flag = 'Y'
         --AND wda.delivery_id = '2242405'
         --AND wdd.delivery_detail_id IN (969822688, 969917104, 969917105, 969917106, 969917107)
         --AND mtrl. pick_slip_number = '2246389'
ORDER BY line_number, line_id, delivery_id

Item Category and Set Details

SELECT
DISTINCT msi.segment1,
                   msi.item_type,
                   mcb.segment1 category_name,
                   mcst.category_set_name,
                   msi.comms_nl_trackable_flag,
                   msi.serviceable_product_flag
  FROM apps.mtl_system_items msi,
       apps.mtl_category_sets mcst,
       apps.mtl_categories_b mcb,
       apps.mtl_item_categories mic
 WHERE     msi.inventory_item_id = mic.inventory_item_id
       AND mic.category_set_id = mcst.category_set_id
       AND mic.category_id = mcb.category_id
       AND msi.organization_id = mic.organization_id
       AND mcst.structure_id = mcb.structure_id
       AND msi.segment1 = '21138299'

Reset Application Password from Database

declare
    v boolean;
begin
    v := fnd_user_pkg.changepassword('CHI59342','welcome12345');
    commit;
end;

Script to push SO Line status from AWAITING_SHIPPING or AWAITING_FULFILLMENT to CLOSED

DECLARE
   l_org_id     NUMBER;
   l_count      NUMBER;
   l_result     VARCHAR2 (30);
   l_file_val   VARCHAR2 (60);

   CURSOR c_rec
   IS
      SELECT oel.line_id
        FROM apps.oe_order_lines_all oel
       WHERE oel.line_id IN
                ('5569696',
                 '5569705',
                 '6057802');
BEGIN
   oe_debug_pub.debug_on;
   oe_debug_pub.initialize;
   l_file_val := OE_DEBUG_PUB.Set_Debug_Mode ('FILE');
   oe_Debug_pub.setdebuglevel (5);
   DBMS_OUTPUT.put_line ('File : ' || l_file_val);

   FOR i IN c_rec
   LOOP
      UPDATE apps.oe_order_lines_all
         SET invoice_interface_status_code = NULL
       WHERE line_id = i.line_id;

      OE_Standard_WF.OEOL_SELECTOR (p_itemtype   => 'OEOL',
                                    p_itemkey    => TO_CHAR (i.line_id),
                                    p_actid      => 12345,
                                    p_funcmode   => 'SET_CTX',
                                    p_result     => l_result);
      wf_engine.handleError ('OEOL',
                             i.line_id,
                             'INVOICE_INTERFACE',
                             'RETRY',
                             NULL);
      DBMS_OUTPUT.put_line (
         'File name ' || OE_DEBUG_PUB.G_DIR || '/' || OE_DEBUG_PUB.G_FILE);
   COMMIT;
   END LOOP;
END;

Backordered Lines

Steps to follow for Backordered lines in OM Shipping

1. Check in Shipping Transaction Form, make sure the order still in “Ready to Release”

2. There might be a lot of possibilities for this problem:
i) Order on hold
ii) Do not have enough qty
iii) Lot expired
iv) Wrong reservation (even inventory have enough qty)
v) Inventory reserved for other orders.
vi) Inventory picked up by other orders
vii) Previously return to stock not done properly.

3. Check the order type in Shipping Transaction Form - Detail > Source
    Or go to WMS Control Board > Picking
    Or go to OM Shipping View

4. Go to Material Workbench to check if the lot has expired or not having enough inventory for this order.

5. We can also check if any lot is reserved for other order as well.

6. In Material Work Bench, go check for each lot in the physical locator by clicking on the “Attribute” button. (lot level reservation)

7. Alternatively, go to Shipping Transaction Form - OM Shipping View, or go to “Lot Number” (Ctrl + L), click on Attribute column to check the SO reservation of the lot.

8. Compare the SO info with the order that are having problem.

9. Go to “Reserve Supply” form (Ctrl + L) to check if the order is wrongly reserved.

10. In the “Reserve Supply form, we can check if the qty is being picked up by other order as well. (order level reservation regardless of lot)

11. Also, can go to Material Workbench > Availability, to check for the available to reserve. It might have high possibility that the qty is taken up by order with different line, or other orders which have been auto launched. If this is the case, must do the backorder using below steps
- Go to Transact Move Order Form tab “Pick Wave” > View/Update allocation > Lot/Serial.
- Click on the checkbox for order which need to be backordered, then go to Tool ® Back Order Line.

12. Ask the user if the order has done return to stock previously. If yes, it might has high possibility that the return to stock not done properly.

13. Check the lot state, must be in “Resides in inventory”.

14. Check in Material Workbench to see if the lot is still packed with LPN or not.

15. Check also if the lot is in physical locator rather than logical locator.

Submit Workflow from Database

Submit OEOH or OEOL (OM Header or line workflow) from database


BEGIN
wf_engine.Background (itemtype=>'OEOH', -- ‘OEOL’
                      minthreshold => NULL,
                      maxthreshold => NULL,
                      process_deferred => TRUE,
                      process_timeout=> FALSE,
                      process_stuck => NULL);
END;

Script to Cancel Sales Order Line

DECLARE

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_header_rec_in oe_order_pub.header_rec_type; -- pl/sql table and record definition to be used as IN parameters
l_line_tbl_in oe_order_pub.line_tbl_type; -- pl/sql table and record definition to be used as IN parameters
l_action_request_tbl_in oe_order_pub.request_tbl_type;-- Used to assigining Book Order related input parameters
l_header_rec_out oe_order_pub.header_rec_type; -- pl/sql table and record definition to be used as OUT parameters
l_line_tbl_out oe_order_pub.line_tbl_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_chr_program_unit_name VARCHAR2 (100); -- To store the package and procedure name for logging
l_chr_ret_status VARCHAR2 (1000) := NULL;-- To store the error message code returned by API
l_msg_count NUMBER := 0; -- To store the number of error messages API has encountered
l_msg_data VARCHAR2 (2000); -- To store the error message text returned by API
l_num_api_version NUMBER := 1.0; -- API version

CURSOR c_so_details IS

SELECT oh.order_number, ol.*
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE oh.header_id = ol.header_id
AND oh.org_id = ol.org_id
AND NVL (ol.cancelled_flag,'N') = 'N'
AND oh.order_number = '294006726'; -- Enter the Order Number


BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'DCOBB';

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'OM SUPERUSER 294';

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR iso_rec IN c_so_details LOOP

l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl_in (1).line_id := iso_rec.line_id;
l_line_tbl_in (1).ordered_quantity := 0;
l_line_tbl_in (1).change_reason := 'Admin Error';
l_line_tbl_in (1).change_comments := 'CANCEL ORDER';
l_line_tbl_in (1).operation := oe_globals.g_opr_update;

oe_msg_pub.delete_msg;

oe_order_pub.process_order
(p_api_version_number => l_num_api_version,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_action_commit => fnd_api.g_false,
p_header_rec    => l_header_rec_in,
p_line_tbl => l_line_tbl_in,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl=> l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_chr_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

l_msg_data := NULL;

IF l_chr_ret_status <> 'S' THEN

FOR iindx IN 1 .. l_msg_count LOOP

l_msg_data := l_msg_data ||'.'|| oe_msg_pub.get (iindx);

END LOOP;

END IF;

DBMS_OUTPUT.ENABLE (10000);

DBMS_OUTPUT.put_line ('Sales Order => '||
iso_rec.order_number||
' - Line Number => '||
iso_rec.line_number||
' - Shipment Number => '||
iso_rec.shipment_number||
' Having Line ID=> '||
iso_rec.line_id||
' Cancelled Successfully' );

DBMS_OUTPUT.put_line ('Return Status: '|| l_chr_ret_status);
DBMS_OUTPUT.put_line ('Error Message: '|| l_msg_data);

END LOOP;

END;

Online Users

SELECT
p.spid -- The UNIX PID
,s.sid ,s.serial#
,p.username as os_user
,s.username ,s.status
,p.terminal ,p.program
FROM v$session s ,v$process p
WHERE p.addr = s.paddr
ORDER BY s.username ,p.spid ,s.sid ,s.serial# ;

Users Locking Object

SELECT c.owner, c.object_name, c.object_type, fu.user_name locking_user_name,
fl.start_time, vs.module, vs.machine, vs.osuser, vs.SID, vp.pid,
vp.spid AS os_process_to_kill, vs.serial#, vs.status, vs.saddr,
vs.audsid, vs.process
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE 'AP_CUSTOM_PKG%';

List of Installed Applications

SELECT fat.application_id, fat.application_name, fpi.creation_date, fpi.product_version, fpi.status, fpi.TABLESPACE, fpi.install_group_num, fpi.db_status, fpi.patch_level, fpi.industry
FROM fnd_product_installations fpi, fnd_application_tl fat
WHERE fpi.application_id = fat.application_id AND fpi.status = 'I'

Different Column Values are as below:

STATUS
S- Shared Install
I- Installed Product
L- Custom Product
N- Not Installed

INSTALL_GROUP_NUM
0- SOA Products
1- MOA (Multiple oracle account) Products. It needs multiple oracle account to support multiple set of books

INDUSTRY
C- Commercial
G- Educational or Non-Profit Use
B- Project Billing
P- Project Costing

OU-SOB-INVORG Details

  SELECT hou.name operating_unit_name,
         hou.organization_id operating_unit_id,
         hou.set_of_books_id sob_id,
         gsob.name sob_name,
         ood.organization_name inv_org_name,
         ood.organization_code inv_org_code,
         ood.organization_id inv_org_id,
         ood.chart_of_accounts_id
    FROM hr_operating_units hou,
         apps.org_organization_definitions ood,
         apps.gl_sets_of_books gsob
   WHERE     1 = 1
         AND hou.organization_id = ood.operating_unit
         AND hou.set_of_books_id = gsob.set_of_books_id
ORDER BY hou.organization_id ASC

Operating Units and Inventory Orgs

List of Operating Units

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'OPERATING_UNIT'
       AND org_information2 = 'Y'
     
List of INV Organizations

SELECT a.name,
       a.organization_id,
       a.creation_date,
       a.last_update_date
  FROM hr_organization_units a, hr_organization_information b
 WHERE     a.organization_id = b.organization_id
       AND org_information1 = 'INV'
       AND org_information2 = 'Y'

Concurrent Program/Set attached to Request Groups

 SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  apps.fnd_request_groups rg,
  apps.fnd_request_group_units rgu,
  apps.fnd_concurrent_programs cp,
  apps.fnd_concurrent_programs_tl cpt
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name = 'XX CM Pack Slip - US'

Menu attached to Responsibilities

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = ‘20538’
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

Custom List

List of Custom Oracle Reports

SELECT DISTINCT FAV.application_name, FCP.user_concurrent_program_name, FCP.concurrent_program_name, FCP.description
FROM fnd_concurrent_programs_vl fcp, FND_EXECUTABLES_FORM_V fef, fnd_application_vl FAV
WHERE fcp.executable_id = fef.executable_id AND fcp.application_id = FAV.application_id AND fcp.concurrent_program_name like 'XX%' AND fef.execution_method_code = 'P'

List of Custom Profile Options

SELECT DISTINCT fav.application_name, fpo.profile_option_name, fpo.creation_date FROM fnd_profile_options fpo, fnd_application_vl FAV
WHERE FPO.application_id = FAV.application_id AND fpo.profile_option_name LIKE 'XX%'

List of Custom Request Groups

SELECT DISTINCT fav.application_name, frg.request_group_name, frg.description, frg.request_group_code FROM fnd_request_groups FRG, FND_APPLICATION_VL fav
WHERE frg.application_id = fav.application_id AND frg.request_group_code LIKE 'XX%'

List of Custom Responsibilities

SELECT DISTINCT fav.application_name, frv.responsibility_key, frv.responsibility_name, frv.description FROM fnd_responsibility_vl FRV, fnd_application_vl FAV
WHERE FRV.application_id = FAV.application_id AND FRV.responsibility_key LIKE 'XX%'

List of Custom Forms

SELECT DISTINCT fav.application_name, ffv.form_name, ffv.user_form_name, ffv.description FROM fnd_form_vl ffv, FND_APPLICATION_VL fav
WHERE ffv.application_id = fav.application_id AND form_name LIKE 'XX%'

List of Custom Applications

SELECT APPLICATION_ID, APPLICATION_SHORT_NAME, BASEPATH, APPLICATION_NAME, DESCRIPTION FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME LIKE 'XX%'

Validate Accounting Segment

SELECT COUNT (1)
INTO lc_segment1
FROM gl_sets_of_books GSOB, fnd_id_flex_structures_vl FIFST, fnd_id_flex_segments_vl FIFSE, fnd_flex_value_sets FFVS, fnd_flex_values_vl FFV WHERE GSOB.chart_of_accounts_id = FIFST.id_flex_num
AND FIFST.id_flex_num = FIFSE.id_flex_num
AND FIFST.id_flex_code = FIFSE.id_flex_code
AND FIFSE.flex_value_set_id = FFVS.flex_value_set_id
AND FFVS.flex_value_set_id = FFV.flex_value_set_id
AND FIFSE.application_column_name = 'SEGMENT1'
AND FFV.flex_value = rec_cur_new.company -- Company Segment Value
AND GSOB.set_of_books_id = lc_sob_id; -- Ledger ID

Form Function attached to Responsibilities

List of Responsibilities associated with Form Functions

SELECT DISTINCT responsibility_id, responsibility_name
    FROM apps.fnd_responsibility_vl a
   WHERE     a.end_date IS NULL
         AND a.menu_id IN
                (    SELECT menu_id
                       FROM apps.fnd_menu_entries_vl
                 START WITH menu_id IN
                               (SELECT menu_id
                                  FROM apps.fnd_menu_entries_vl
                                 WHERE function_id IN
                                          (SELECT function_id
                                             FROM applsys.fnd_form_functions a
                                            WHERE function_name =
                                                     :pc_function_name))
                 CONNECT BY PRIOR menu_id = sub_menu_id)
         AND a.responsibility_id NOT IN
                (SELECT responsibility_id
                   FROM apps.fnd_responsibility_vl
                  WHERE responsibility_id IN
                           (SELECT responsibility_id
                              FROM applsys.fnd_resp_functions resp
                             WHERE action_id IN
                                      (SELECT function_id
                                         FROM applsys.fnd_form_functions a
                                        WHERE function_name = :pc_function_name)))
         AND a.responsibility_id NOT IN
                (SELECT responsibility_id
                   FROM apps.fnd_responsibility_vl
                  WHERE responsibility_id IN
                           (SELECT responsibility_id
                              FROM applsys.fnd_resp_functions resp
                             WHERE action_id IN
                                      (    SELECT menu_id
                                             FROM apps.fnd_menu_entries_vl
                                       START WITH menu_id IN
                                                     (SELECT menu_id
                                                        FROM apps.fnd_menu_entries_vl
                                                       WHERE function_id IN
                                                                (SELECT function_id
                                                                   FROM applsys.fnd_form_functions a
                                                                  WHERE function_name =
                                                                           :pc_function_name))
                                       CONNECT BY PRIOR menu_id = sub_menu_id)))

ORDER BY responsibility_id