Add Responsibility to a User in oracle apps

Syntax:

fnd_user_pkg.addresp(username => v_user_name

,resp_app => 'SYSADMIN'

,resp_key => 'SYSTEM_ADMINISTRATOR'

,security_group => 'STANDARD'

,description => 'Auto Assignment'

,start_date => SYSDATE - 10

,end_date => SYSDATE + 1000);

Instance Migration Scripts - AOL messages and Lookups

--  Check Folder option columns

SELECT *

  FROM apps.fnd_folder_columns

 WHERE folder_id IN (

              SELECT folder_id

                FROM apps.fnd_folders

               WHERE NAME = 'OM' AND OBJECT = 'OE_ORDERS'

                     AND public_flag = 'Y')

   AND item_prompt = 'Pricing Contract'

-- AOL Messages

  SELECT *

    FROM fnd_new_messages

   WHERE message_name IN

            ('XXZP_INVALID_TEMPLATE_CHANGE',

             'XXZP_ITEM_COST_DEFINED',

             'XXZP_BATCH_SELECTION',

             'XXZP_NEW_BATCH',

             'XXZP_RECORD_ERROR')

ORDER BY message_name

-- Lookups

SELECT *

  FROM apps.fnd_lookup_values

 WHERE lookup_type IN ('XYZ_EC_ORDERTYPE_LKP')

Adding concurrent program to a request group

Adding conc prog to a request group

---------------------------------

DECLARE

   v_program_short_name    VARCHAR2 (200);

   v_program_application   VARCHAR2 (200);

   v_request_group         VARCHAR2 (200);

   v_group_application     VARCHAR2 (200);

Instance Migration Scripts - XML Publisher reports

-- Query to check XML Publisher Verification

SELECT b.*

FROM apps.xdo_lobs l, apps.xdo_templates_b b

WHERE l.application_short_name = 'QP'

AND l.lob_code IN ('XYZ123')

AND l.application_short_name = b.application_short_name

Instance Migration Scripts - Value sets

--  Check Value Sets (Table Type)

SELECT *

  FROM apps.fnd_flex_validation_tables

 WHERE flex_value_set_id IN (

                             SELECT flex_value_set_id

                               FROM apps.fnd_flex_value_sets

                              WHERE flex_value_set_name =

                                                         'XYZ_COR_ORDER_VS')

                                                        

--  Check Value Sets (Independent)

SELECT *

  FROM apps.fnd_flex_values

 WHERE flex_value_set_id IN (

                             SELECT flex_value_set_id

                               FROM apps.fnd_flex_value_sets

                              WHERE flex_value_set_name =

                                                         'XYZ_ORDER_TYPES_VS')

ORDER BY flex_value                                                         

--  Delete values from Independent Value set

 FND_FLEX_VAL_API.DELETE_VALUESET(v_valueset);

dbms_output.put_line('Droped the Valueset'||v_valueset); 

Instance Migration Scripts - Profile Options

--  Check Profile Option Values

SELECT DECODE (fpov.level_id,

               10001, 'Site',

               10002, 'Appl',

               10003, 'Resp',

               10004, 'User',

               'Unkown'

              ) "Level",

       DECODE (fpov.level_id,

               10002, fa.application_name,

               10003, fr.responsibility_name,

               10004, fu.user_name,

               '-'

              ) "Location",

       fpov.profile_option_value "Value"

  FROM apps.fnd_profile_option_values fpov,

       apps.fnd_profile_options fpo,

       apps.fnd_profile_options_tl fpot,

       apps.fnd_responsibility_tl fr,

       apps.fnd_user fu,

       apps.fnd_application_tl fa

 WHERE fpov.profile_option_id = fpo.profile_option_id

   AND fpo.profile_option_name = fpot.profile_option_name

   AND fpov.level_value = fr.responsibility_id(+)

   AND fpov.level_value = fu.user_id(+)

   AND fpov.level_value = fa.application_id(+)

   AND fpot.user_profile_option_name = 'XYZ:Billing Term Column';

  

-- Profile Queries

SELECT *

  FROM apps.fnd_profile_options_vl

 WHERE profile_option_name IN

          ('XYZ_OOPS_ERROR_HOLD',

           'XYZ_OOPS_HOLD_COMMENT',

           'XYZ_OOPS_HOLD_REL_REASON',

           'XYZ_OOPS_HOLD_REL_COMMENT'

          )   

Instance Migration Scripts - Workflows

--  Check Workflow Activity Status

  SELECT COUNT (1)

        IN ln_completed_lines

        FROM wf_item_activity_statuses a,

             wf_process_activities b,

             oe_order_lines_all c

Instance Migration Scripts - Form Personalization Rules

--  Verify Personalization Rules

SELECT   ffcr.SEQUENCE "Seq", ffcr.description "Description",

         DECODE (ffcr.rule_type,

                 'F', 'Form',

                 'A', 'Function',

                 'Other'

                ) "Level",

Instance Migration Scripts - Concurrent programs

--  Verify Concurrent Programs

  SELECT *

    FROM apps.fnd_concurrent_programs_vl

   WHERE user_concurrent_program_name = 'XYZ Price Utility Report'

ORDER BY user_concurrent_program_name;

--  Verify Concurrent Program is assigned  request set or not

SELECT fcpt.user_concurrent_program_name, fcpt.concurrent_program_id,

       frg.request_group_name

  FROM apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_request_group_units frgu,

       apps.fnd_request_groups frg

 WHERE frgu.request_unit_type = 'P'

   AND frgu.request_unit_id = fcpt.concurrent_program_id

   AND frgu.request_group_id = frg.request_group_id

   AND fcpt.user_concurrent_program_name LIKE 'abc%';

Oracle apps Value set - Sample Code

SELECT ffv.attribute1

  FROM fnd_flex_value_sets ffvs, fnd_flex_values_tl ffvt, fnd_flex_values ffv

 WHERE     ffvs.flex_value_set_name = 'XX_SAMPLE_VALUESET_VS'

       AND ffvs.flex_value_set_id = ffv.flex_value_set_id

       AND ffv.flex_value_id = ffvt.flex_value_id

       AND ffvt.description = lv_value

Submit a concurrent program and wait for request

BEGIN
   fnd_file.put_line (fnd_file.output,
                      '*** Call The XXXX Import Program  ***');
   fnd_global.apps_initialize (
      user_id             => fnd_profile.VALUE ('USER_ID'),
      resp_id             => fnd_profile.VALUE ('RESP_ID'),
      resp_appl_id        => fnd_profile.VALUE ('RESP_APPL_ID'),
      security_group_id   => 0);
   fnd_file.put_line (fnd_file.LOG, 'Batch ID:' || ln_group_id);
   ln_req_id :=

Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

DECLARE

  v_job  varchar2(50);      

BEGIN

   SELECT job              

     INTO v_job

     FROM emp

    where ename='ALLEN';

        dbms_output.put_line ('Job Of this Employee iS '||v_job);

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE ('There Is An Exception'||SQLERRM);

END;

/

So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Oracle Apps Financials period closure monitoring scripts

Accounts Payable:

To find whether any orphan entry exist (this prevents period close)

Select *

from apps.ap_ae_lines_all

Where ae_header_id in(

Select ae_header_id

Oracle Apps Inventory period closure monitoring scripts

To check if there is any unprocessed material

select transaction_header_id                Txn_hdr_id,

       mmtt.transaction_temp_id             Txn_tmp_id,

       mmtt.inventory_item_id               Inv_Item_id,

       revision                             Rev,

       subinventory_code                    Subinv_code,

       locator_id                           Loc_id,

       mtlt.LOT_NUMBER                      Lot_Number,

       msnt.fm_serial_number                FmSerial_Num,

Copy existing sales order through Open interface

Copy an existing order by importing it through Open interface tables & Import orders program.

Provide the order number to copy & the order source id in the below code snippet.

DECLARE

   l_return_status       VARCHAR2 (1);

   l_order_number_from   NUMBER := <Order_Number1>;

   l_order_source_id     NUMBER := <Order_Source_ID>;