Alter the Workflow Notification Timeout for testing directly from Backend

SELECT aav.PROCESS_ACTIVITY_ID, 
       pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
  FROM wf_process_activities pa, wf_activity_attr_values aav
 WHERE    1=1 
       AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
       AND aav.process_activity_id = pa.instance_id
       AND activity_name = 'PO_REQ_APPROVE'
       AND aav.name = '#TIMEOUT'


UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2      --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_APPROVE'
                  AND aav.name = '#TIMEOUT')

UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2                                                 --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER1'
                  AND aav.name = '#TIMEOUT')

UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2                                                 --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER2'
                  AND aav.name = '#TIMEOUT')
   

Find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount

Below query will help to find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount. 


  SELECT prha.requisition_header_id,
         prha.segment1,
         gdr.conversion_rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (
                    DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                    1)),
            2)
            usd_amount,
         gdr.to_currency,
         gdr.from_currency,
         prla.rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (prla.rate, 1)),
            2)
            line_currency_amount,
         prla.currency_code line_currency,
         ROUND (
            SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
            2)
            actual_requisition_amount,
         gs.currency_code func_currency,
         TRUNC (prha.creation_date) creation_date,
         gcc.segment3,
         prha.org_id
    FROM po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_requisition_headers_all prha,
         gl_code_combinations_kfv gcc,
         gl_sets_of_books gs,
         gl_daily_rates gdr
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND NVL (prla.cancel_flag, 'N') <> 'Y'
         --         AND  prha.org_id = 9944
         AND prla.requisition_line_id = prda.requisition_line_id
         AND prha.authorization_status = 'APPROVED'
         AND prda.code_combination_id = gcc.code_combination_id
         AND gs.set_of_books_id = prda.set_of_books_id
         --         AND gs.currency_code <> prla.currency_code
         AND gdr.from_currency = gs.currency_code(+)
         AND gdr.to_currency(+) = 'USD'
         AND TRUNC (gdr.conversion_date) =
                TRUNC (NVL (prla.rate_date, prha.creation_date))
--         AND gcc.segment3 IN ('15510','15610','15620' )
--         AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
         prha.segment1,
         prla.rate,
         gdr.conversion_rate,
         TRUNC (prha.creation_date),
         gcc.segment3,
         gdr.to_currency,
         gdr.from_currency,
         gs.currency_code,
         prla.currency_code,
         prha.org_id
  HAVING SUM (
              NVL (prda.req_line_quantity, 1)
            * NVL (prla.unit_price, 1)
            * NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                   1)) > 100000

Query to find DFF and there segment details along with tables in Oracle Apps

  SELECT ffv.descriptive_flexfield_name "DFF Name",
         ffv.TITLE "DFF Title",
         ffv.application_table_name "Table Name",
         ap.application_name "Application",
         ffc.descriptive_flex_context_code "Context Code",
         ffc.descriptive_flex_context_name "Context Name",
         ffc.description "Context Desc",
         ffc.enabled_flag "Context Enable Flag",
         att.column_seq_num "Segment Number",
         att.form_left_prompt "Segment Name",
         att.application_column_name "Column",
         fvs.flex_value_set_name "Value Set",
         att.display_flag "Displayed",
         att.enabled_flag "Enabled",
         att.required_flag "Required"
    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 ap.application_name = 'Inventory'
         AND ffv.title = :P_DFF_Title
--         AND att.form_left_prompt LIKE '%Cross%'
--         AND ffc.descriptive_flex_context_code LIKE 'Give Context Code Value'
ORDER BY att.column_seq_num
;

Difference between Function and Procedure

1. Function is mainly used in the case where it must return a value. Whereas a procedure may or may not return a value or may return more than one value using the OUT parameter. 
2. Function can be called from SQL statements whereas procedure cannot be called from the SQL statements 

3. Functions are normally used for computations whereas procedures are normally used for executing business logic. 

4. You can have DML (insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.

5. Function returns 1 value only. Procedure can return multiple values (max 1024). 

6. Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc. But actually not exists in database is allowed only in during creation but runtime throws error Function won't support deferred name resolution. 

7. Stored procedure returns always integer value by default zero. whereas function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan whereas functions are not.
9. A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. 
10. We can't have any DDL,DML and TLC command inside a function, if that function is called from a query. But if the function is not called from query then we can have all transactional statement(DDL,DML and TLC ) inside a function.

Find the Workflow of Order or Line in Order Apps

To find the workflow for Sales Order or Line

SELECT transaction_type_id, tl.*

  FROM oe_transaction_types_tl tl

 WHERE name LIKE '%Non%Standard%';

  SELECT *

    FROM oe_order_lines_all

   WHERE line_type_id = :transaction_type_id 

     AND creation_date IS NOT NULL

ORDER BY creation_date DESC;

SELECT *

  FROM oe_order_headers_all

 WHERE header_id = 2374832;

To open the workflow for specific order, search the Order Number' IN "Sales Order  or Order organizer" form under "OM Superuser" responsibility and switch to Line then under Tools Menu CHECK THE workflow.

Query to list all Categories associated with Item in Oracle Apps

  SELECT msib.inventory_item_id,
         msib.organization_id,
         mcs.CATEGORY_SET_NAME,
         RTRIM (
               mc.segment1
            || '|'
            || mc.segment2
            || '|'
            || mc.segment3
            || '|'
            || mc.segment4,
            '|')
            category,
         msib.segment1 Inv_Item,
         msib.description
    FROM mtl_system_items_b msib,
         mtl_item_categories mic,
         mtl_categories mc,
         MTL_CATEGORY_SETS mcs
   WHERE     1 = 1
         AND msib.segment1 = :P_Item
         AND mic.inventory_item_id = msib.inventory_item_id
         AND mic.organization_id = msib.organization_id
         AND mic.organization_id = 28
         AND mc.CATEGORY_ID = mic.CATEGORY_ID
         AND mc.STRUCTURE_ID = mcs.STRUCTURE_ID
         AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_ID
ORDER BY 1
;

Query to display Sales Order Line Number in Oracle Apps

SELECT  TO_CHAR (l.line_number) 
        || DECODE (l.shipment_number, NULL, NULL, '.' || TO_CHAR (l.shipment_number))
        || DECODE (l.option_number, NULL, NULL, '.' || TO_CHAR (l.option_number))
        || DECODE (l.component_number, NULL, NULL, 
                   DECODE (l.option_number, NULL, '.', NULL)|| '.' ||TO_CHAR (l.component_number))
        || DECODE (l.service_number, NULL, NULL, 
                   DECODE (l.component_number, NULL, '.', NULL)
        || DECODE (l.option_number, NULL, '.', NULL) || '.'|| TO_CHAR (l.service_number))
          line_number,
       h.order_number
  FROM oe_order_headers_all h, oe_order_lines_all l
 WHERE     h.header_id = l.header_id
       AND l.open_flag = 'Y'
       AND h.header_id = :P_header_id
;

Query to find delivery details along with Ship method in Oracle Apps

It will find the list of all delivery details for specified SO orders if any


  SELECT wdd.source_header_type_name ORDER_TYPE,
         wdd.source_header_number ORDER_NUMBER,
         wdd.source_line_number ORDER_LINE_NUMBER,
         wdd.CUST_PO_NUMBER, 
         wnd.delivery_id,
         wnd.ship_method_code,
         wnd.waybill,
         wdd.ORG_ID OPERATING_UNIT_ID,
         wdd.SUBINVENTORY,
         wdd.SHIPPED_QUANTITY
    FROM APPS.wsh_new_deliveries wnd,
         APPS.wsh_delivery_assignments wda,
         APPS.wsh_delivery_details wdd
   WHERE     wnd.delivery_id = wda.delivery_id
         AND wda.delivery_detail_id = wdd.delivery_detail_id
         AND wdd.ORG_ID IN (27, 121, 36, 201) -- US OU, US DEVICES OU, Germany OU, EUR DEVICES OU
         AND wnd.confirm_date > '31-AUG-2007'
         AND wdd.source_header_number = :P_Order_Number
ORDER BY wdd.SOURCE_HEADER_NUMBER, wdd.SOURCE_LINE_NUMBER
;
--------------------------------------------------------------------------------------------------------

Query to find All the responsibility attached with User and their end_date

SELECT FUSER.USER_NAME USER_NAME,
       FUSER.START_DATE,
       FUSER.END_DATE,
       FUSER.EMAIL_ADDRESS,
       fuser.description,
       per.FULL_NAME FULL_NAME,
       per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
       frt.RESPONSIBILITY_NAME RESPONSIBILITY
  FROM FND_USER fuser,
       PER_PEOPLE_F per,
       FND_USER_RESP_GROUPS furg,
       FND_RESPONSIBILITY_TL FRT
 WHERE     FUSER.EMPLOYEE_ID = PER.PERSON_ID
       AND (   PER.EFFECTIVE_END_DATE > SYSDATE
            OR PER.EFFECTIVE_END_DATE IS NULL)
       AND fuser.USER_ID = furg.USER_ID
       AND (TO_CHAR (fuser.END_DATE) IS NULL OR fuser.END_DATE > SYSDATE)
       AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
       AND (TO_CHAR (furg.END_DATE) IS NULL OR furg.END_DATE > SYSDATE)
       AND FRT.LANGUAGE = 'US'
       AND fuser.USER_NAME = :P_User_Name
;

Query to find details of Form Personzalization in Oracle Apps

Find the List of Form Personlization Rules for any specific from 


 SELECT ffv.form_id "Form ID",  ffv.form_name "Form Name",
         ffv.user_form_name "User Form Name",
         ffv.description "Form Description",
         ffcr.sequence "Sequence",
         ffcr.description "Personalization Rule Name"
    FROM fnd_form_vl ffv, fnd_form_custom_rules ffcr
   WHERE ffv.form_name = ffcr.form_name AND ffv.form_name = 'POXPOEPO' /*Name of the form for which you want to find the personalizations */
ORDER BY ffv.form_name, ffcr.sequence;
-----------------------------------------------------------------------------------------------------------


Elaborates From Personalization along with Conditions, Trigger_Event and Action details  


  SELECT DISTINCT A.Id,
                  A.Form_Name,
                  A.Enabled,
                  C.User_Form_Name,
                  D.Application_Name,
                  a.sequence,
                  A.Description,
                  a.trigger_event,
                  a.trigger_object,
                  a.condition,
                  Ca.Action_Type,
                  ca.summary Action_description,
                  Ca.Enabled,
                  Ca.Object_Type,
                  ca.TARGET_OBJECT,
                  ca.property_value,
                  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 = 660                       --For Order Management
         --       AND C.User_Form_Name LIKE 'Sales%' --All the Forms that Start with Sales
         AND A.Enabled = 'Y'
         AND a.id = ca.rule_id
         AND (   CA.TARGET_OBJECT LIKE '%REQUEST_DATE%'
              OR CA.property_value LIKE '%REQUEST_DATE%'
              OR UPPER (A.Description) LIKE '%REQUEST_DATE%'
              OR UPPER (ca.summary) LIKE '%REQUEST_DATE%')
ORDER BY A.description
;
-----------------------------------------------------------------------------------------------------------

List of All Tables use to track Form Personlizations

SELECT * FROM Fnd_Form_Custom_Actions where

SELECT * FROM all_tables where table_name like 'FND%FORM%'

SELECT * FROM FND_FORM_CUSTOM_SCOPES

SELECT * FROM FND_FORM_CUSTOM_PARAMS

SELECT * FROM FND_FORM_CUSTOM_RULES

SELECT * FROM FND_FORM_CUSTOM_PROP_VALUES

SELECT * FROM FND_FORM_CUSTOM_PROP_LIST

SELECT * FROM FND_FORM_CUSTOM_ACTIONS

FND_FORM_CUSTOM_RULES - The Rules for the form customization. A rule must have 1 or more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.

FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule 

FND_FORM - stores information about your registered application forms.

Query to Find Defaulting Rules in Order Management for specific Attribute

Query to Find Defaulting Rules in Order Management for specific Attribute  like in below case finding the defaulting rules for REQUEST_DATE (Customer_Request_date or Shcedule_Request_date) field


SELECT dadr.DATABASE_OBJECT_NAME,
       dadr.ATTRIBUTE_CODE,
       dac.ATTRIBUTE_CODE,
       dac.DATABASE_OBJECT_NAME,
       aoa.column_name,
       aoa.attribute_application_id,
       aoa.REQUIRED_FLAG,
       DADR.*
  FROM oe_def_attr_def_rules dadr,
       oe_def_attr_condns dac,
       ak_object_attributes aoa
 WHERE     dadr.ATTR_DEF_CONDITION_ID = dac.ATTR_DEF_CONDITION_ID
       AND dac.ENABLED_FLAG = 'Y'
       AND dac.ATTRIBUTE_CODE = aoa.ATTRIBUTE_CODE
       AND dac.DATABASE_OBJECT_NAME = aoa.DATABASE_OBJECT_NAME
       AND UPPER (DADR.SRC_API_FN) LIKE '%REQUEST_DATE%'

Query to find the last run date of a Concurrent Program in Oracle Apps

Query to find the last run date of a Concurrent Program

--input:     Concurrent Program name

SELECT MAX (fcr.REQUEST_DATE)

  FROM fnd_concurrent_requests          fcr,

       fnd_concurrent_programs_tl       fcpt

 WHERE     fcr.concurrent_program_id = fcpt.concurrent_program_id

       AND fcr.program_application_id = fcpt.application_id

       AND fcr.actual_start_date > SYSDATE - :days_to_check

       AND fcpt.user_concurrent_program_name LIKE '%' || :Program_Name || '%'

Query to find the responsibilities which can run the concurrent program in Oracle Apps

Query to find the responsibilities which can run the concurrent program

-- input:     Concurrent Program name


SELECT frv.responsibility_name
  FROM apps.fnd_request_group_units frgu,
       apps.fnd_responsibility_vl frv,
       apps.fnd_concurrent_programs_vl fcpv
 WHERE     frv.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
       AND fcpv.concurrent_program_id = frgu.REQUEST_UNIT_ID
       AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
       AND EXISTS
              (SELECT 'Y'
                 FROM apps.fnd_user_resp_groups grps
                WHERE     grps.responsibility_id = frv.responsibility_id
                      AND NVL (grps.end_date, SYSDATE + 1) > SYSDATE)
       AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'
Order By 1

Concurrent Program - Query to find Status of Concurrent Requests in Oracle Apps

Query will list the details for the concurrent program like 
Concurrent Program name, Arguments, log / output file path and the Status 


   SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) <= trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
   AND fcr.request_id = :P_Request_ID
 ORDER BY fcr.request_date,
          fcr.request_id DESC;

FND Load - Form (entire or Personalization) download and Upload commands

You should take a note that upon uploading, all prior personalizations for that function are first deleted, and then the contents of the loader file are inserted. So be careful..)

 FNDLOAD - A pain killer for migration

you can either Download for a specific form or specfic function of the form. In both the case same affrmcus.lct is used.

Download -specific form< /STRONG>

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES

form_name=<form name>< /FONT>

Download -for all personalizations< /FONT>

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>< /FONT>

• Function_name is a required parameter

• You cann't download lct file with out any supplied value.

e.g. 

FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct OEXOEORD_PERSONALIZATION.ldt FND_FORM_CUSTOM_RULES form_name =OEXOEORD

OEXOEORD_PERSONALIZATION.ldt :- Any name for downloaded file

FND_FORM_CUSTOM_RULES          :- Specifies to download form personalization

Upload

FNDLOAD <userid>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt>

e.g. 

FNDLOAD apps/appsmv 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct OEXOEORDNEW.ldt

Uasge

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct xxapperiod.lct FND_FORM_CUSTOM_RULES form_name=APXSUMPS

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct xxapperiod.lct

Because the architecture has built-in support for all languages, you don’t translate ldt files, but instead you enter multiple Actions for different languages.

Query to Find Concurrent Program Details using Executable Name in Oracle Apps

--Find Concurrent Program Details using Executable Name 

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name, 
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id 
       AND fe.executable_id = fcp.executable_id 
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = :P_Executable_Name

  ;

Query to find out Concurrent programs attached to request group in Oracle Apps

Find Out Concurrent Program attache to user

SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               '-NA-'
              ) request_unit_type,
       user_concurrent_program_name program_name
  FROM fnd_request_groups a,
       fnd_request_group_units b,
       fnd_concurrent_programs_vl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.concurrent_program_id
   AND (c.CONCURRENT_PROGRAM_NAME = :P_Conc_Prog_Short_Name
   OR request_group_name = 'OM Concurrent Programs')
   AND request_unit_type = 'P'
;

List of Responsibility Key along with application short name attached with your fnd_user profile in Oracle Apps

  SELECT r.responsibility_key, fa.application_short_name
    FROM fnd_user u,
         fnd_user_resp_groups ur,
         fnd_responsibility r,
         fnd_application_tl a,
         fnd_application fa
   WHERE     u.user_id = ur.user_id
         AND u.user_name = :p_user_name
         AND r.version = 'W'
         AND ur.responsibility_id = r.responsibility_id
         AND fa.application_id = a.application_id
         AND r.application_id = a.application_id
ORDER BY 1

FND_PROFILE and FND_GLOBAL values in Oracle Apps

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;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

    

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Query to Check available Table-Space size details and Alter the table-space in Oracle database

-- To Check available tablespace in database



SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       ROUND (100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace))
          "Pct. Free"
  FROM (  SELECT tablespace_name, ROUND (SUM (bytes) / 1048576) TotalSpace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
       (  SELECT ROUND (SUM (bytes) / (1024 * 1024)) totalusedspace,
                 tablespace_name
            FROM dba_segments
        GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name;


--To alter Tablespace


SELECT FILE_NAME
  FROM dba_data_files
 WHERE TABLESPACE_NAME = :P_TABLESPACE_NAME;

 -- copy past the File_name against datafile

ALTER DATABASE
DATAFILE 'enter_file_path/with_file_name'

RESIZE 800M;

Find Employee- Supervisor Hierarchy in Oracle Apps

  SELECT papf.employee_number,           papf.person_id,
           papf.full_name,
           ppg.segment1 officer_flag, --executive flag for officers (Y is officer, N is not)
           NVL (spapf.full_name, 'NONE') supervisor_name,
           spapf.person_id supv_person_id,
           LEVEL
      FROM per_people_x papf,
           PER_PEOPLE_X SPAPF,
           per_assignments_x paaf,
           pay_people_groups ppg
     WHERE     papf.person_id = paaf.person_id
           AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
           AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
           and sysdate between SPAPF.EFFECTIVE_START_DATE and SPAPF.EFFECTIVE_END_DATE
           --AND xxx_person_type.is_employee(:p_eff_date, papf.person_id) = 'TRUE'
           AND paaf.assignment_type IN ('E', 'C')
           AND paaf.supervisor_id = spapf.person_id
           AND paaf.people_group_id = ppg.people_group_id
           AND paaf.primary_flag = 'Y'
START WITH papf.employee_number = :p_emp_num --emp num of employee or top level supervisor?
CONNECT BY PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level
  ORDER BY LEVEL DESC
;

--------------------------------------------------------------------
OR

    SELECT LEVEL seq,
           e.person_id,
           e.grade_id,
           e.job_id,
           e.supervisor_id,
           e.employee_number,
           e.full_name
      FROM (SELECT DISTINCT paf.person_id,
                            paf.grade_id,
                            paf.job_id,
                            paf.supervisor_id,
                            ppf.employee_number,
                            ppf.full_name
              FROM    per_all_people_f ppf
                   LEFT JOIN
                      per_all_assignments_f paf
                   ON     ppf.person_id = paf.person_id
                      AND paf.person_id IS NOT NULL
                      AND (SYSDATE BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date)
                      AND (SYSDATE BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date)) e
CONNECT BY person_id = PRIOR supervisor_id
START WITH employee_number = :p_employee_number         --person_id = :P_PERSON_ID
  ORDER BY LEVEL
;

Query to find the Responsibility attached to Form Function in Oracle Apps

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)))