Setting up email override in Oracle Cloud (Fusion)

I created this post to show you how to over-ride email addresses in Oracle Fusion applications (Cloud) for testing in the sandbox environment.

Brace yourself !

Here we go ..... 🙂

Step 1: Access Setup and Maintenance

Step 2: Hover your mouse to the Search Tasks window and Type 'Manage Task' and click search

Step 3: Select the appropriate 'Manage Task Configuration'

Step 4: Click on Administration button

Step 5: Scroll further down and click to edit the Test Notification Email Address

Step 6: Enter your email address

Step 7: Select Save and Bingo !

An error has occurred while running an API import. No return type specified in PL/SQL parameter list 50000:XXPDH_COST_UPLOAD_PLS attribute 3.

It took me hours to figure this out.

BNE_ATTRIBUTES need to be correctly updated for resolving the same
following are the import attributes.

when i created the importer the attribute1 had a default value of FUNCTION, which had to be updated to a PROCEDURE.

Also Make sure to update the Reference Fields while creating the importer, else you will have to manually update from the database as below.



UPDATE BNE_ATTRIBUTES
   SET ATTRIBUTE1 = 'PROCEDURE'
 WHERE ATTRIBUTE_CODE = 'XXPDH_CST_UPLOAD_INTG_XA1';

UPDATE BNE_ATTRIBUTES
   SET ATTRIBUTE1 = 'P_GROUP_ID'
     , ATTRIBUTE2 =  'NUMBER'
 WHERE ATTRIBUTE_CODE = 'XXPDH_CST_UPLOAD_INTG_XA2';

UPDATE BNE_ATTRIBUTES
   SET ATTRIBUTE1 =  'X_RETURN_STATUS'
 WHERE ATTRIBUTE_cODE = 'XXPDH_CST_UPLOAD_INTG_XA3';  

UPDATE BNE_ATTRIBUTES
   SET ATTRIBUTE1 =  'X_MSG_DATA'
 WHERE ATTRIBUTE_cODE = 'XXPDH_CST_UPLOAD_INTG_XA4';  

R12 WEBADI ERROR: oracle.jbo.TooManyObjectsException: JBO-25013

I knew this error had to do with some kind of redundant Cache:

After several testing sessions and research i have compiled this list which will effectively help in cache related web adi errors:

-----------------------
  --  diagnostics --
-----------------------

1) clear bne cache
-------------------
http://<machine>:<port>/OA_HTML/BneAdminServlet


2) clear browser cache
----------------------
clear all the cache in the respective browser


3) clear core services cache
----------------------------------
functional administrator > core services > caching framework > global configuration


4) delete data from bne_tables
-------------------------------------
clear data from all bne% tables related to the integrator


5) run the wf backgroud process in deferred mode
------------------------------------------------
parameters: Null, Null, Null, No, Yes, No


6) Apache bounce
-----------------
cd $INST_TOP
cd admin/scripts
sh adapcctl.sh stop
sh adapcctl.sh start

Sending concatenated segments for validating/generating GL Code Combinations

DECLARE
   l_code_combination_id   NUMBER;
   l_boolean               BOOLEAN;
   l_segment_array         apps.fnd_flex_ext.segmentarray;
   l_segment               VARCHAR2(155):= '10.7110.64110.00.0000.0000';
   l_segment_count         NUMBER;
   l_structure_number      apps.fnd_id_flex_structures.id_flex_num%TYPE;
   l_id_flex_code          apps.fnd_id_flex_structures.id_flex_code%TYPE;
   g_user_id               NUMBER:= 1896;
   g_resp_id               NUMBER:= 50774;
   g_resp_appl_id          NUMBER:= 707; 
   ERR                     EXCEPTION;
BEGIN
  
    apps.fnd_global.apps_initialize (g_user_id, g_resp_id, g_resp_appl_id);

    DBMS_OUTPUT.put_line ('SYSDATE'||SYSDATE);
   
    SELECT fifs.id_flex_num, fifs.id_flex_code
      INTO l_structure_number, l_id_flex_code
      FROM apps.fnd_id_flex_structures fifs
     WHERE fifs.id_flex_code = 'GL#'
       AND fifs.id_flex_structure_code = 'XXPDH_ACCOUNTING_FLEXFIELD';
   
    SELECT REGEXP_COUNT (l_segment, '[^.]+')
      INTO l_segment_count
      FROM dual; 
    
    /* splitting the string into individual segments */
    FOR i in 1..l_segment_count
    LOOP       
 
        SELECT REGEXP_SUBSTR (l_segment, '[^.]+', 1, i)
          INTO l_segment_array(i)
          FROM dual;
       
    END LOOP;
    
       
   l_boolean :=
      apps.fnd_flex_ext.get_combination_id (
         application_short_name   => 'SQLGL',
         key_flex_code            => l_id_flex_code,
         structure_number         => l_structure_number,
         validation_date          => SYSDATE,
         n_segments               => 6,
         segments                 => l_segment_array,
         combination_id           => l_code_combination_id,
         data_set                 => -1);


   IF l_code_combination_id IS NULL
   THEN
      RAISE ERR;
   ELSE
      DBMS_OUTPUT.put_line ('l_code_combination_id - '||l_code_combination_id);
   END IF;
EXCEPTION
   WHEN ERR
   THEN
        DBMS_OUTPUT.put_line (SQLERRM);
   WHEN OTHERS
   THEN
        DBMS_OUTPUT.put_line (SQLERRM);  
END;

bulk exception types

There are 2 types of bulk exceptions

1) When the exception occurs inside the body of the bulk
2) When the exception occurs inside FORALL statement

For exception 1,
the below statement does not work
  print_log(l_module_name||' : number of statements that failed -'||l_err_count);

it works only for  exception 2, because whenever there is an exception in the body of the bulk, the it directly goes to WHEN OTHERS.


 BEGIN
                           
                            OPEN cur_get_bulk_transactions ( l_bulk_lot (l).organization_id
                                                           , l_bulk_lot (l).inventory_item_id
                                                           , l_bulk_lot (l).lot_number
                                                           , p_trx_date_from
                                                           , p_trx_date_to);
                            LOOP
                                FETCH cur_get_bulk_transactions
                                BULK COLLECT INTO l_bulk_trx;
                                FOR t IN 1 .. l_bulk_trx.COUNT
                                LOOP
                                                           
                                    print_log(l_module_name||' : Inside cur_get_bulk_transactions');
                                   
                                    -- generate the record_id
                                    -- if there are any errors, raise exception as record_id is the primary key
                                    BEGIN
                                        SELECT xxgil_lot_status_report_seq.NEXTVAL
                                          INTO l_bulk_trx (t).record_id
                                          FROM dual;
                                    EXCEPTION
                                        WHEN OTHERS THEN
                                            l_err_msg := SQLERRM;
                                            l_err_msg:= substr(l_err_msg||' Unable to set record_id', 1, 32000);
                                            RAISE e_track;
                                    END;
                                       
                                    print_log(l_module_name||' : xxgil_lot_status_report_seq.NEXTVAL - '||l_bulk_trx (t).record_id);
                                    print_log(l_module_name||' : l_bulk_lot (l).mfg_date - '||l_bulk_lot (l).mfg_date);
                                   
                                     
                                    -- assign the mfg and release date derived from the CURSOR cur_get_bulk_lots
                                    l_bulk_trx (t).mfg_date := to_date (l_bulk_lot (l).mfg_date, 'YYYY/MM/ HH24:MI:SS');
                                    l_bulk_trx (t).release_date := l_bulk_lot (l).release_date;
                                    l_bulk_trx (t).status := 'N';
                                   
                                    print_log(l_module_name||' : l_bulk_trx (t).mfg_date - '||l_bulk_trx (t).mfg_date);
                                    print_log(l_module_name||' : l_bulk_trx (t).release_date - '||l_bulk_trx (t).release_date);
                                    print_log(l_module_name||' : l_bulk_trx (t).status - '||l_bulk_trx (t).status);

                                   
                                END LOOP;
                                print_log(l_module_name||' : Outside l_bulk_trx (t) END LOOP - ');
                               

                                EXIT WHEN l_bulk_trx.COUNT = 0;

                                FORALL x IN l_bulk_trx.FIRST .. l_bulk_trx.LAST
                                    INSERT INTO xxgil.xxgil_bulk_lot_status VALUES l_bulk_trx (x);

                                COMMIT;

                            END LOOP;
                            print_log(l_module_name||' : BEFORE CLOSE cur_get_bulk_transactions - ');

                            CLOSE cur_get_bulk_transactions;
                           
                            print_log(l_module_name||' : AFTER CLOSE cur_get_bulk_transactions - ');
                           
                        EXCEPTION
                            WHEN OTHERS THEN
                                print_log(l_module_name||'INSIDE OTHERS EXCEPTION');
                                l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
                                print_log(l_module_name||' : number of statements that failed -'||l_err_count);
                               
                                FOR i IN 1..l_err_count
                                LOOP
                                    print_log(l_module_name||' : error #' || i || ' occurred during '||'iteration #' || SQL%bulk_exceptions(i).error_index);  
                                    print_log(l_module_name||' : error message is ' ||SQLERRM(SQL%bulk_exceptions(i).error_code));
                                END LOOP;
                               
                                -- reset the error counter
                                l_err_count := 0;
                                print_log(l_module_name||'ENDING OTHERS EXCEPTION');
                               
                        END;                       

Form Personalization - Property Name - Displayed vs Displayed (Applications Cover)

In Form Personalization, We are often confused on the selection of property Name such as

Enabled vs Enabled (Applications Cover)

Alterable vs Alterable (Applications Cover)

Here, From the functional perspective, i will give an example which will elaborate the difference.

1) Property Name =  Enabled

After saving the changes, notice that although the field is disabled, the value cannot be copied

2) Property Name = Enabled (Applications Cover)

After saving the changes, notice that the field value can be copied

Raising a Standard Business event

Raising a standard business event is pretty simple.

The following block should give you an idea -

declare
p_event_name    VARCHAR2(4000);
p_event_key     VARCHAR2(4000);
p_event_data    VARCHAR2(4000);
p_parameters    wf_parameter_list_t;  

begin
     
      dbms_output.put_line('Before Event Raise');
   
      wf_event.RAISE (p_event_name   => 'oracle.apps.gme.batch.rescheduled',
                      p_event_key    => p_event_key,
                      p_event_data   => NULL,
                      p_parameters   => p_parameters);
                     
      dbms_output.put_line('After Event Raise');
exception
    when others then
        dbms_output.put_line('SQLERRM - '||SQLERRM);                    
end;

finding the webadi integrators having upload type as PROCEDURE

select substr(intg.INTEGRATOR_CODE,1,length(intg.INTEGRATOR_CODE)-5) integrator, attribute1, attribute2
  from bne_interfaces_vl intf
     , bne_integrators_vl intg
     , bne_attributes att
 where intf.integrator_code = intg.integrator_code
   and intf.upload_type = 2
   and intg.enabled_flag = 'Y'
   and substr(intg.INTEGRATOR_CODE,1,length(intg.INTEGRATOR_CODE)-5)||'_P0_ATT' = att.ATTRIBUTE_CODE
   and upper(attribute1) = 'PROCEDURE'

How to check if a particular date is a working date

-- identify the calendar code
SELECT organization_code,
       calendar_code,
       negative_inv_receipt_code,
       stock_locator_control_code,
       organization_id
  FROM mtl_parameters
 WHERE organization_code = '313'

-- check if it is a working day time
 SELECT 1
  FROM  bom_calendars cal,
        bom_shift_dates sd,
        bom_shift_times st
  WHERE cal.calendar_code = 'GIL_FC_CMO'--c_calendar_code
  AND sd.calendar_code = cal.calendar_code
  AND st.calendar_code = sd.calendar_code
  AND sd.shift_num = st.shift_num
  -- B4610901, Rajesh Patangya 15-Sep-2005
  AND (sd.shift_date + (st.from_time/86400)) <= '30-JAN-2015'
  AND DECODE(
        SIGN(st.from_time - st.to_time),
    1,(sd.shift_date+1), sd.shift_date
         ) + (st.to_time/86400) >=  '30-JAN-2015'
  AND sd.seq_num IS NOT NULL

Searching for API's in R12

Most developers know that they can integrate their external applications with the E-Business Suite via the business service interfaces and SOA service endpoints documented in the E-Business Suite's Integration Repository.  This is shipped as part of EBS 12.

Responsibility to access irep > Integrated SOA Gateway

Process to Setup And Use AME in General

Assign AME Roles and Responsibilities 

AME responsibilities in 11i.AME.A are assigned directly to the users.  However, In R12 or 11i.AME.B and higher, AME responsibilities are assigned indirectly to users through roles.  The roles are assigned to the users by the SYSADMIN user using the User Management responsibility.  Once the roles are assigned, the AME responsibilities are automatically available to the users without specifically assigning the AME responsibilities to the users.  Here are steps to assign the roles:

1) Login as System Administrator user

2) Select the responsibility "User Management".  (NOTE: User Management data is stored in the UMX schema)

3) Select "Users" menu option

4) Search for the user to whom you wish to grant AME roles

5) In the results table, click on update icon 

6) In the update user page, user details can be seen along with a list of roles available to user

    Click on "Assign Roles" ( This will appear only if the Approval Management Administrator or Approval 

    Management Business User Analyst responsibilities are already assigned to the User)

7) Search for Approval% and Select roles from the resulting LOV.  Choose the roles that are applicable (proper authority) for the user, and click the Select button.

8) Specify justification and relevant dates for the newly assigned roles, and click Apply to assign the roles to the user.

Grant Transaction Type Access to Users

AME restricts access to transaction types using Data Security.  Grant users access to the transaction types using the Grants page. Set up user access as follows:

1) Navigate to the Personal Home Page

2) Select Functional Administrator Responsibility 

3) From the Grants page, press on the Create Grant button

4) Create a grant with the following information: 

             · Name <specify a descriptive name>
             · Grantee Type = Specific User
             · Grantee = <The user which you just created>
             · Object = AME Transaction Types 

5) Click Next and select the Object Data Context

     Data Context Type = All Rows

6) Click Next to define the object parameters and Select Set

    Set = AME Calling Applications

7) Click Next, review the setups and then Finish the process.

Query - Customer Details

SELECT DISTINCT hp.party_name "Customer Name",
                hca.account_name "Account Description",
                hou.name "Operating Unit",
                hp.party_number,
                hca.account_number,
                hca.status "Customer Status",
                hps.party_site_number,
                hps.status "Site Status",
                hl.address1,
                hl.address2,
                hl.address3,
                hl.city,
                hl.state,
                hl.county,
                hl.province,
                hl.postal_code,
                hl.country,
                hcsu.site_use_code,
                hcsu.status "Site Use Status",
                hcsu.primary_flag,
                hcsu.location,
                hcsu.bill_to_site_use_id "Bill to Location",
                rt.name "Payment Term",
                hcsu.attribute1 "Revenue Location",
                null as "Ship Method",
                rt.name "Payment Term",
                qlh.name,
                hl.address1 "Internal Location",              
                hl.address1 "Internal Organization",
                'HZ_CPUI' as "CREATED_BY_MODULE",
                fu.user_name
  FROM hz_parties hp,
       hz_party_sites hps,
       hz_locations hl,
       hz_cust_accounts_all hca,
       hz_cust_acct_sites_all hcsa,
       hz_cust_site_uses_all hcsu,
       hr_operating_units hou,
       ra_terms rt,
       qp_list_headers qlh,
       fnd_user fu
 WHERE     hp.party_id = hps.party_id
       AND hps.location_id = hl.location_id
       AND hp.party_id = hca.party_id
       AND hcsa.party_site_id = hps.party_site_id
       AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
       AND hca.cust_account_id = hcsa.cust_account_id
       AND hou.organization_id = hcsa.org_id
       AND rt.term_id = hcsu.payment_term_id
       AND QLH.list_header_id = hcsu.price_list_id
       AND fu.user_id = hca.created_by
       AND hl.address1 LIKE 'US1%CLIN%FIS%'

Query - Generating missing months

select * FROM (
             SELECT DISTINCT
                    md.inventory_item_id
                  , md.organization_id
                  , md.plan_id
                  , md.plan_run_id
                  , md.project_id
                  , md.sr_instance_id
                  , md.vmi_flag
                  , md.io_plan_flag
                  , md.owning_org_id
                  , md.owning_inst_id
                  , md.aggr_type
                  , md.category_set_id
                  , md.sr_category_id
                  , EXTRACT(YEAR FROM md.order_date) years
                  , cal.min_month_date
                  , cal.max_month_date
               FROM msc_demands_f md
                  , (
                         SELECT TO_NUMBER(year_name) year_name
                              , MIN(mpd.month_start_date) min_month_date
                              , MAX(mpd.month_end_date) max_month_date
                           FROM msc_phub_dates_mv mpd
                          GROUP BY TO_NUMBER(year_name)
                                 , month_name
                    ) cal
              WHERE cal.year_name = EXTRACT(YEAR FROM md.order_date)
                AND cal.min_month_date >= (
                                              SELECT MIN(mpd.month_start_date)
                                                FROM msc_demands_f mdf
                                                   , msc_phub_dates_mv mpd
                                               WHERE mpd.calendar_date = mdf.order_date
                                                 AND mdf.inventory_item_id = md.inventory_item_id
                                                 AND mdf.organization_id = md.organization_id
                                                 AND mdf.plan_id = md.plan_id
                                                 AND mdf.plan_run_id = md.plan_run_id
                                         )
                AND cal.min_month_date <= (
                                               SELECT MAX(mpd.month_start_date)
                                                 FROM msc_demands_f mdf
                                                    , msc_phub_dates_mv mpd
                                                WHERE mpd.calendar_date = mdf.order_date
                                                  AND mdf.inventory_item_id = md.inventory_item_id
                                                  AND mdf.organization_id = md.organization_id
                                                  AND mdf.plan_id = md.plan_id
                                                  AND mdf.plan_run_id = md.plan_run_id
                                          )
             ) md_out
  WHERE 1=1
    AND md_out.inventory_item_id = 1369
    AND md_out.organization_id = 593
    AND md_out.plan_id = 2022
    AND md_out.plan_run_id = 91911
  ORDER BY min_month_date

Query : Operating Unit and Inventory Org link

SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC 

improving the performance of Work Order and Material transactions

The key to improving the performance of a work order and a material transaction is by feeding the organization details to gme_batch_header

After several sessions, here is the optimized query

SELECT msib.segment1,
                           msib.description,
                           mtln.lot_number,
                           gbh.batch_no,
                           mp.organization_code,
                           mp.organization_id,
                           mmt.transaction_uom,
                           mtln.transaction_id,
                           mtln.transaction_quantity,
                           mtln.transaction_date
                      FROM inv.mtl_transaction_lot_numbers mtln,
                           inv.mtl_material_transactions mmt,
                           inv.mtl_system_items_b msib,
                           inv.mtl_lot_numbers mln,
                           gme.gme_batch_header gbh,
                           gme.gme_material_details gmd,
                           mtl_parameters mp
                     WHERE     1 = 1
                           AND mtln.transaction_id = mmt.transaction_id
                           AND mtln.inventory_item_id = mmt.inventory_item_id
                           AND mtln.organization_id = mmt.organization_id
                           AND mmt.transaction_source_type_id = 5 /* Job Or Schedule */
                           AND mtln.transaction_source_type_id = 5 /* Job Or Schedule */
                           AND mmt.inventory_item_id = msib.inventory_item_id
                           AND mmt.organization_id = msib.organization_id
                           AND mmt.transaction_source_id = gbh.batch_id
                           AND mmt.transaction_type_id IN (44, 17) /* WIP Completion and WIP completion Return */
                           AND mtln.lot_number = mln.lot_number
                           AND mtln.inventory_item_id = mln.inventory_item_id
                           AND mtln.organization_id = mln.organization_id
                           AND mtln.status_id = 20    /* Quarantine Routine */
                           AND mln.status_id = 20     /* Quarantine Routine */
                           AND mln.disable_flag IS NULL
                           AND mp.organization_id = mmt.organization_id
                           AND mp.organization_id = gbh.organization_id
                           AND gmd.batch_id = gbh.batch_id
                           AND mmt.trx_source_line_id = gmd.material_detail_id
                           AND gmd.inventory_item_id = mmt.inventory_item_id
                           AND mmt.inventory_item_id = mtln.inventory_item_id
                           AND gmd.line_type = 1
                           AND msib.segment1 = NVL (:P_ITEM_NUM, msib.segment1)
                           AND mtln.lot_number = NVL (:P_LOT_NUM, mtln.lot_number)
                  GROUP BY msib.segment1,
                           msib.description,
                           mtln.lot_number,
                           gbh.batch_no,
                           mp.organization_code,
                           mp.organization_id,
                           mmt.transaction_uom,
                           mtln.transaction_id,
                           mtln.transaction_quantity,
                           mtln.transaction_date

Request Set Concurrent Programs and their average execution time

SELECT frsp.sequence,
         fcp.user_concurrent_program_name,
         fe.execution_file_name,
         (SELECT ROUND (
                    AVG ( (actual_completion_date - actual_start_date) * 1440),
                    2)
                    AVG
            FROM fnd_concurrent_Requests
           WHERE concurrent_program_id = frsp.concurrent_program_id)
            avg_time
    FROM fnd_request_set_programs frsp,
         fnd_concurrent_programs_vl fcp,
         fnd_executables fe
   WHERE request_set_id =
            (SELECT request_set_id
               FROM fnd_request_sets_tl
              WHERE UPPER (user_request_set_name) = UPPER ('GIL MV Refresh'))
         AND frsp.concurrent_program_id = fcp.concurrent_program_id
         AND fe.executable_id = fcp.executable_id
ORDER BY sequence 

ldt script error

make sure there is no space after "=" in an ldt script

FNDLOAD apps/apps2devq0 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXGILOPENORDER_RG.ldt REQUEST_GROUP REQUEST_GROUP_NAME='GIL_GME_3_OPM PRODUCTION SUPE' APPLICATION_SHORT_NAME='GME'

Oracle Reports Conversion to BI Publisher in EBS

Most of Oracle Reports (.rdf) are likely to be converted to full fledged Oracle BI Reports in the next few versions of Oracle Apps.

Here are some fantastic blogs and articles by my fellow Oracle Apps Consultants

http://bipconsulting.blogspot.com/2009/08/oracle-reports-migration-to-bi.html

http://bipconsulting.blogspot.com/2009/08/oracle-reports-migration-to-bi_17.html

http://bipublisher.blogspot.com/2009/05/bi-publisher-reports6i-to-bip.html

Assigning Roles - Approvals Management Business Analyst

Certain Responsibilities need to have specific roles assigned to be visible in your login.

These are controlled via User Management Responsibility

For User Management Responsibility to be visible, Security Administrator role needs to be assigned to your login through a SYSADMIN user (User Management Responsibility).

Now once you have User Management Responsibility following steps need to be followed :

- Access User Tab and query for the username (FND user)

- Click on Update

- Click on Assign Roles

- Select an appropriate role such as "Approvals Management Business Analyst"

- Provide a Justification and save

- Go to Sysadmin responsibility and run the program "CREATE FND_RESP WF ROLES"

- Now you can view all the functions and menus in the responsibility "Approvals Management Business Analyst"

Queries - GL

Find the Legal Entity associated to a Ledger:

SELECT *
  FROM APPS.GL_LEDGER_LE_BSV_SPECIFIC_V
 WHERE 1 = 1