Configuration steps to start the Workflow Notification mailer in Test environments

Introduction

At times, there is a need to receive the notification emails that are sent through the Oracle workflow mailer in Development and Test environments. However, these emails should not go to the mailboxes of the users of the system. Instead, they should be redirected to a common mailbox. This blog article is a step by step guide to configure the workflow mailer for the above objective.

Important Note: These steps should NEVER be performed in the production environment.

Steps

Part 1 - Configure the mail server and email sender

1)      Login using ‘SYSADMIN user and go to responsibility  ‘Workflow Administrator Web Application’ > Workflow Manager

2)     You will see Notification mailer is Down



3) Click on ‘Down’.You will find the below window.


4) To Configure Workflow Mailer, press on Edit button



5) You will find the below window



6) Set the Server Name to your Email /SMTP server. Set the ‘Reply-To-Address’ to ‘NoReplyTo@AtYourCompany.com’.



7) Click on button 'Advanced'



8) Press 'Next'



9) Press 'Next'




10) Press 'Next'



11)   Update from field to ‘Workflow Mailer <Name of the Test Environment>’ for e.g. “Workflow Mailer DEV”




Part 2 - Configure the override address

Override address is the address where all the emails will redirected. This can be configured from front end also. However following are the steps to set it up from back end.

1)      Run the below sql statement

    select fscpv.parameter_value

      from fnd_svc_comp_params_tl fscpt

          ,fnd_svc_comp_param_vals fscpv

     where fscpt.display_name = 'Test Address' 

                     and fscpt.parameter_id = fscpv.parameter_id;

2) The result should be ‘NONE’

3) Now run the below sql update statement and commit

UPDATE fnd_svc_comp_param_vals fscpv 

  SET fscpv.PARAMETER_VALUE = 'redirectaddress@yourcompany.com' --( put the email address to which all emails will be redirected)

WHERE fscpv.parameter_id 

(SELECT fscpt.parameter_id

  FROM fnd_svc_comp_params_tl fscpt

 WHERE fscpt.display_name = 'Test Address');

4) Now run the SQL in Step 1 to verify that the email address has been set as required.

5) Now start the workflow mailer by going to ‘Workflow Administrator Web Application’ > Workflow Manager.   You will see Notification mailer is Down. Click on 'Down'. On next page '1)      Select ‘Start’ from drop down list and Press on Go button.


6) After the above step the workflow notification mailer should start and the status should be 'Running'.

Part 3 - Test the setup

1) Click on 'Workflow Notification Mailer'.



2)  Click on ‘Test Mailer’



3) Select a valid user for which email address has been setup and click 'Accept' button.

Two test emails should be sent to the email address that you have setup in the Part 2 (step 3). The emails should not be sent to the actual email address that is set for the user that you have selected.

This completes the steps needed to redirect emails on your test or development environment. Enjoy!!

Oracle Applications : Delete concurrent program & executable

The concurrent program and executable cannot be deleted from Oracle Applications front end,  instead you can only disable the concurrent program. This is because if deletion is allowed then the corresponding history of previous runs, audit records will be left with orphan records.

But sometimes during the development phase, you would want the option of deleting the concurrent program and executable. Following is the code snippet to delete a concurrent program and its executable from database.

------------------------------------------------------------
------------------------------------------------------------
begin
------------------Program Short Name, Application Name 
if fnd_program.PROGRAM_EXISTS('XXWS01A',''My ABC Application') then
   fnd_program.DELETE_PROGRAM('XXWS01A',''My ABC Application');
end if;

------------------Executable Short Name, Application Name 
if fnd_program.EXECUTABLE_EXISTS('XXWS01A',''My ABC Application') then
   fnd_program.DELETE_EXECUTABLE('XXWS01A','My ABC Application');
end if;

end;
/

commit;
/
------------------------------------------------------------
------------------------------------------------------------

Oracle OPM Interview Questions

1)      What are the different types of manufacturing processes? And what are the primary differences between them? OR

Explain Process Manufacturing v/s Discrete manufacturing?

Answer - http://www.inspirage.com/2007/03/understanding-the-primary-differences-between-opm-and-discrete-processes/

2)      What are the modules that come under oracle’s OPM solution?

Answer: OPM includes OPM Process Planning, Product Development(which includes Formula,Recipe,Quality), Production, Financials(Costing,MAC), Logistics, Regulatory Management etc. These are products which come under this umbrella..

·         OPM Cost Management

·         OPM Formula Management

·         OPM Intelligence

·         OPM Inventory Management

·         OPM Laboratory Management

·         OPM Master Production Scheduling

·         OPM Material Requirements Planning

·         OPM Production Management

·         OPM Purchasing Management

·         OPM Quality Management

·         OPM Capacity

·         OPM Sales Management

·         Oracle Financial

3)      Name and explain frequently used terms in Oracle Process Manufacturing?

Answer: http://souravpupu.blogspot.in/2011/08/brief-identity-of-process-manufacturing.html

4)      Name some important tables used in OPM? ORWhich tables stores the formula information?

Answer:

1)      select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,decode(b.line_type,-1,’Ingredient’,'Product’) Type
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID

2)      Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,decode(c.line_type,-1,’Ingredient’,'Product’) Type,
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION

5)  Explain what do you mean by  Formula and Recipe?
Formula is Ingredients and their proportions
Receipe is Formula + Routing.

6) What are different kinds of losses?
Fixed loss and Variable loss.


Also, visit the following link for topics on which the interview questions can be asked for Oracle SQL, Database, Forms and Report
http://tenthsense.blogspot.in/2012/04/fresher-interview-for-oracle-database.html

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 3

7.   PL/SQL Packages, Procedures and Functions

Identification & Verification

The initial list of unused PL/SQL code objects (Packages/Procedures/Functions) can be identified based on the strategy discussed in Identification section above. Also the unused application components (Workflows, Forms and Reports) removed above will give us additional list of PL/SQL code objects that can potentially be removed.

We then need to verify if the PL/SQL code object is not used in any workflow activity, view definition, any other package, oracle form, oracle report or concurrent program executable.

Check if the PL/SQL code object is used in any workflow activity.

SELECT *   FROM wf_activities  WHERE UPPER (function) LIKE UPPER('%<PACKAGE_NAME>%')    AND end_date IS NULL  ORDER BY version

Check if the PL/SQL code object is used in any views, packages, procedures, functions etc.

SELECT ad.name, -- Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<PACKAGE_NAME>%')

Check if the PL/SQL code object is used in any concurrent executable

     

SELECT *   FROM fnd_executables  WHERE UPPER (execution_file_name) LIKE UPPER('%<PACKAGE_NAME>%')    AND execution_method_code = 'I' -- ‘I’ for Oracle PLSQL Code

Check if the PL/SQL code object is used in any form

     

Go to $AU_TOP/forms/US grep –ir <PACKAGE_NAME> *.fmb

Check if the PL/SQL code object is used in any report

     

Go to $CUSTOM_TOP/reports/US grep –ir <PACKAGE_NAME> *.rdf

Removal

The package, procedure or function can be removed using the following SQL command.

DROP PACKAGE <PACKAGE_NAME> DROP PROCEDURE <PROCEDURE_NAME> DROP FUNCTION <FUNCTION_NAME>

8.   Database Views

Identification & Verification

The initial list of unused database views can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of views that can potentially be removed.

We then need to verify if the views are not used in any code such as package/procedure/function, oracle form, oracle report, table type value set and flex fields.

Check if the view is used in any other view, package, procedure, function etc.

     

SELECT ad.name, --Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any form

     

Go to $AU_TOP/forms/US grep –ir <VIEW_NAME> *.fmb

Check if the view is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <VIEW_NAME> *.rdf

Removal

The view can be removed using the following SQL command.

DROP VIEW <VIEW_NAME>

9.   Database Tables

Identification & Verification

The initial list of unused database tables can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of tables that can potentially be removed.

We then need to verify if the tables are not used in any code such as view definition, package/procedure/function, oracle form, oracle report, table type value set and flex field.

Check if the table is used in any views, package, procedure, function etc.

SELECT name,-- Referencing Object        type -- Type of referencing object   FROM all_dependencies  WHERE referenced_name LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any form

     

Go to $AU_TOP/forms/US grep –ir <TABLE_NAME> *.fmb

Check if the table is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <TABLE_NAME> *.rdf

Check the latest time a DML Commit operation was performed on a table. If the table stores transactional data, this query will give you a good indication of whether the table is used or not.

Note: The following query will not consider SELECTs performed on the table.

     

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn))   FROM <TABLE_NAME>

Removal

The table can be removed using the following SQL command. It is also a suggested to take a temporary backup of the table data before dropping the table.

CREATE TABLE <TABLE_NAME_BKUP>          AS (SELECT * FROM <TABLE_NAME>); DROP TABLE <TABLE_NAME>;

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 2

5.   Oracle Forms

Identification & Verification

The initial list of unused forms can be identified based on the strategy discussed in Identification section above.

Additionally, unused forms can also be identified by checking the latest time when each form was accessed by a user. Forms which were accessed long back are probably not in use any more and are likely candidates for removal.

Note: The following query will give latest access time for a form only if -

a.     The value for the profile ’Sign-On:Audit Level’ is set to ‘FORM’ and

b.     The built in ‘FND_STANDARD.FORM_INFO’ is called in the ‘PRE-FORM’ trigger with appropriate values for the form.

SELECT ff.form_name,        MAX(flrf.start_time) last_accessed_time   FROM fnd_form ff,        fnd_login_resp_forms flrf        WHERE ff.form_id = flrf.form_id (+)    AND UPPER(ff.form_name) LIKE 'XX%'  GROUP BY ff.form_name    ORDER BY ff.form_name

 Check if any other form is calling the identified form.

Go to $AU_TOP/forms/US grep –ir <FORM_NAME> *.fmb

Removal

Following steps need to be carried out to remove the form.

1) Delete the <FORM_NAME>.fmb file from to $AU_TOP/forms/US

rm –f <FORM_NAME>.fmb

2)Delete the <FORM_NAME>.fmx file from to $CUSTOM_TOP/forms/US

rm –f <FORM_NAME>.fmx

3) Remove the Form Function from the menu

4) Delete the Form Function

6.   Oracle Reports

Identification & Verification

The initial list of unused reports can be identified based on the strategy discussed in Identification section above.

Additionally, unused reports can also be identified by checking if the corresponding concurrent programs are disabled.

SELECT fe.execution_file_name,        fcp.concurrent_program_name   FROM fnd_executables fe,        fnd_concurrent_programs fcp  WHERE fe.execution_method_code = 'P'    -- ‘P’ for Oracle reports    AND fe.executable_id = fcp.executable_id    AND fcp.enabled_flag = 'N'    AND fe.execution_file_name LIKE 'XX%' -- Assuming custom reports are   -- named starting with XX

For each unused report the corresponding executable and concurrent program can be identified. The concurrent program can then be verified as follows.

Check the last time when the report concurrent program was ran.

Note: This data might not give correct usage picture as normally the concurrent program run data is frequently purged.

SELECT *   FROM fnd_conc_req_summary_v  WHERE program_short_name LIKE UPPER('%<PROGRAM_SHORT NAME>%')   AND TRUNC (actual_start_date) < TRUNC (SYSDATE-365)

Removal

Following steps need to be carried out to remove the report.

1)Delete the corresponding executable and the concurrent program

BEGIN   FND_PROGRAM.DELETE_EXECUTABLE ('<EXECUTABLE_SHORT_NAME>', '<APPLICATION_NAME>');   FND_PROGRAM.DELETE_PROGRAM ('<PROGRAM_SHORT_NAME>', '<APPLICATION_NAME>'); END;

2)Delete the <REPORT_NAME>.rdf file from to $CUSTOM_TOP/reports/US

rm –f <REPORT_NAME>.rdf