Query to find the responsibility for quering supplier sites of a perticular operating unit in R12

Finding  the operating unit of a Supplier:

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

SELECT HZP.PARTY_NAME "VENDOR NAME",hou.name                       operating_unit

, APS.SEGMENT1 "VENDOR NUMBER"

, ASS.VENDOR_SITE_CODE "SITE CODE"

FROM HZ_PARTIES HZP

, AP_SUPPLIERS APS

, HZ_PARTY_SITES SITE_SUPP

, AP_SUPPLIER_SITES_ALL ASS

--, IBY_EXTERNAL_PAYEES_ALL IEP

, hr_all_organization_units       hou

WHERE HZP.PARTY_ID = APS.PARTY_ID

AND HZP.PARTY_ID = SITE_SUPP.PARTY_ID

AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID

AND ASS.VENDOR_ID = APS.VENDOR_ID

and  ass.org_id    = hou.organization_id

AND ASS.VENDOR_SITE_CODE='xxxxx'

 Finding  the responsibility  of a Supplier:

SELECT distinct B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

, per_organization_structures a1

, PER_ORG_STRUCTURE_VERSIONS b1

, per_org_structure_elements_v c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and d.ORGANIZATION_STRUCTURE_ID = a1.ORGANIZATION_STRUCTURE_ID

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

and a1.ORGANIZATION_STRUCTURE_ID = b1.ORGANIZATION_STRUCTURE_ID

and b1.ORG_STRUCTURE_VERSION_ID= c1.ORG_STRUCTURE_VERSION_ID

and (d1.organization_id = c1.ORGANIZATION_ID_CHILD

or d1.organization_id = c1.ORGANIZATION_ID_parent)

AND d1.name='14101 Travelscape, LLC'

UNION ALL

SELECT B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name

FROM FND_PROFILE_OPTIONS_VL A

, fnd_profile_option_values b

, fnd_responsibility_vl c

, per_security_profiles_v d

,PER_SECURITY_ORGANIZATIONS_V c1

, hr_operating_units d1

WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'

and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID

and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID

and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE

--AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'

AND c1.SECURITY_PROFILE_ID = d.SECURITY_PROFILE_ID

and c1.ORGANIZATION_ID = d1.ORGANIZATION_ID

AND d1.name='xxxxx'

--

Deleting a template from back end

declare   

   l_templateCode    varchar2 (100) := 'XX_AR_XXXREPORT'; -- Template Code   

begin

   for r in (select t1.application_short_name template_app_name,

                    t1.data_source_code,

                    t1.application_short_name def_app_name

               from xdo_templates_b t1

              where t1.template_code = l_templateCode)

   loop

     

      xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

      delete from xdo_lobs

            where lob_code = l_templateCode

                  and application_short_name = r.template_app_name

                  and lob_type in ('TEMPLATE_SOURCE', 'TEMPLATE');

      delete from xdo_config_values

            where application_short_name = r.template_app_name

                  and template_code = l_templateCode

                  and data_source_code = r.data_source_code

                  and config_level = 50;

  end loop;

end;

--
Best Regards,
Boge Prasanth kumar Reddy.

Concurrent Program Phase codes and Status Codes

Concurrent Program Phase Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_PHASE_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

Concurrent Program Status Codes:

SELECT lookup_code, meaning

  FROM fnd_lookup_values

 WHERE lookup_type = 'CP_STATUS_CODE' AND LANGUAGE = 'US'

   AND enabled_flag = 'Y';

LOOKUP_CODE	MEANING
R	Normal
I	Normal
Z	Waiting
D	Cancelled
U	Disabled
E	Error
M	No Manager
C	Normal
H	On Hold
W	Paused
B	Resuming
P	Scheduled
Q	Standby
S	Suspended
X	Terminated
T	Terminating
A	Waiting
G	Warning

--

How to enable About this page in oracle apps

How to enable About this page in oracle apps

Navigate to System Administrator --> Profile --> System

Profile: FND: Diagnostics 
Set the site value to Yes




Logout and log back in.

Note:This information provided courtesy of <http://oracleappsengineering.blogspot.ie>.
-- 

RICE/CEMLI Terminology

RICE/CEMLI Terminology

In my Oracle career I often hear confusion over RICE/CEMLI terminologies, what they stands for ? what is the meaning of...blah blah blah....so with the help of my very good friend Charan, I have decided to write my take on this.

AIM (Applications Implementation Methodology) => During packaged ERP(enterprise resource planning) implementations, Clients often have additional requirements apart from the existing (standard) business process, for which they need to create/change theVanila system (Unchanged ERP Implemented system) processes, and its these changes that come under RICE/RICEW components. AIM is the methodology/standards/published guidelines, which Oracle suggests it's Clients to follow while developing 
RICE/RICEW components for their business requirements.

RICE stands for Reports, Interfaces, Conversions, Enhancements / Extensions
-----
 > Sometimes extended to FRICE > F for Forms
                                         -------
                                        OR  
                                        RICEW > W for Workflow.
                                         -------

Forms/Reports/Workflows : Create/Change existing forms/reports/workflows available in  ERP system to  meet the Clients business requirements.

Interfaces : Linking (Programs) between other systems to ERP system in order to synchronize the Data.They can be Manual, Batch or Real-Time. Interfaces can be either outbound or inbound. An outbound interface reads data from Oracle Apps tables and usually creates output files in the third party tool specified format. An inbound interface reads data from flat files (usually) and calls Oracle APIs to upload data into Oracle Apps.

Conversion : It is converting the data structure and data design of legacy system data to satisfy the customer's business rules before importing it into Oracle .It is like a one time run of an inbound interface except that the amount of data processed during conversion could be potentially huge since all the required data from the legacy system would be transferred to Oracle.

Enhancements/Extensions : Please see below.

>> In R12 RICE components have been extended to CEMLI components.
                                                                             --------

CEMLI Stands for Configurations/Customization, Extension, Modification, Localization, and Integration.


Configurations : Configure the existing, pre-built application features according to your client's requirement.Changing setups and profile values can be the example of configurations.

Customization : Customization means altering/changing the standard objects or creation of custom object to meet client's business need. It may be Extensions or Modifications.

Extensions : Extension means creating custom code from scratch, existing objects (views, packages and java classes etc) can be used. It is having different behaviour from seeded one.

Modifications : Modifications is enhancing/changing the existing code to meet the client's requirements. It is the modification of seeded behaviour.

Localization :  It is to define the different legislative support provided by oracle Applications based on country/region/language requirements. 

Integration : It can be Data Integration or Application Integration, options for these two are Open Interface tables, APIs, EAI(Enterprise Application Integration Tools), BPEL, AQ, EDI etc.

             >> Apart from these there is one more term and that is Personalization.

Personalization : Tailoring the layout or visibility of page content to meet client requirements is Personalization. Changing the user interface (UI) look-and-feel, making any field visible/enabled/disabled/mandatory/non mandatory comes under Personalization.



>> There are many guidelines and risks related to RICE/CEMLI components, but that's for some other day, here I'll restrict myself to overview of these terminologies.

Note: This above information is courtesy  of http://abhayappssolution.blogspot.ie

--

Alerts in Oracle Applications

Alerts are basically used automate the system maintenance, provide reports in the format chosen, for database activity/Business Requirement as it occurs..

few scenarios:

- A database event which was supposed to be monitored for instance insert into some table(event based alert).

- A SQL statement which provides specific information (event based alert).

- The frequency we want the report of a SQL statement(periodic alert)

Responsibility Name: Alert Manager

Pre-requisites:

Navigation: Alert Manager --> System --> Options

-- Electronic Account Mail setup

-- Oracle Alert Mailbox 

Navigation: Alert Manager --> System --> Installations

-- Setup if there are any CUSTOM application where the Alert needs to be created.

Types of Alerts: Event based and Periodic

-- Both types of alerts are defined by SQL statement specified in the SQL section.

Periodic Alert: Navigate to alert form and select the periodic Alert option and then below tasks to be performed.

- Application Name : Application name that owns the alert

- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field.

- Frequency : Select the frequency of the alert,below are the available options.

On Demand

On Day of the month

On Day of the week

Every N calendar Days

Every Day

Every Other Day

Every N Business Days

Every Business Day

Every Other Business Day

Also Start time and end time, number of times in 24 hours and end date if not required after some date.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.The statement should have INTO clause and one 

output field.

Example: Input field    :INPUT_NAME

     Output field   &OUTPUT_NAME

Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.

Example: SELECT PACKAGE.FUNCTION_NAME(:INPUT_VALUE) INTO &OUTPUT_VALUE FROM DUAL;

IMPORT Option: Rather than creating a SQL statement, we can also import from file and can use in the application.

Event Alert: Navigate to alert form and select the Event Alert option and then below tasks to be performed.

- Application Name : Application name that owns the alert

- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field

- Specify the event table name (application be different that the table resides) but privileges should be present.

- Check the insert/update, when the alert has to be fired.

- SQL : Enter a SQL Statement that retrieves all the alert information, that can used for actions to be planned.The statement should have INTO clause and one 

output field.

Example: Input field    :INPUT_NAME

     Output field   &OUTPUT_NAME

Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.

Also make sure there is a condition based on the event table in the SQL, by using :ROWID condition.

Example:

SELECT user_name INTO &NEWUSER FROM fnd_user WHERE rowid = :ROWID;

The SQL statement should be verified, run then it should be saved in the alert.

- Alert Details: Provide the input,output and installation/user id details.

- Action: Create the action Name and click on the action details.

Action type can be set as Message,SQL Statement script and operating system script.

and provide the details for the message details like To list and message data.

- Action sets: Once the alert actions are created, those alert actions can be included in the action sets.

Oracle Inventory --- Purchasing Category:

Oracle Inventory – Purchasing Category: Purchasing category is nothing but an Oracle Inventory category which is attached to a category set mapped to Purchasing functional area. Following setup steps need to be performed to setup a Purchasing category.

Step-1: Define "Item Category" flexfield structure

Responsibility: "Inventory Superuser" or similar
Navigate to: Setup -> Flexfields -> Key -> Segments
Query for Application "Inventory" and Flexfield Title "Item Categories".

There is already one seeded structure for the purchasing category as "PO_ITEM_CATEGORY" but if you want to define a custom one for your business please create it by entering the Structure code, title and description. Add the required category segments to the structure.

Step-2: Define Item Categories

Navigate to: Setup -> Items -> Categories -> Category Codes.
Enter the structure name and click on the "Find" button.
Define the Item category codes and description in the "Categories" form.

Step-3: Define Category Set

Navigate to: Setup -> Items -> Categories -> Category Sets
Enter the category set name, description, flex structure (the one you have setup in the Step-1), Controlled at and Default Category values.
Add the category codes defined in the previous step to the details section of the "Category Sets" form.

Step-4: Assign the category set to the functional area

Navigate to: Setup -> Items -> Categories -> Default Category Sets
Assign the category set name defined in the Step-3 to the "Purchasing" functional area.

Now the purchasing category setup is complete.

--