List profile options and their values

-
This select will give you all the values for a specific profile option. 
SELECT potl.language,
       potl.user_profile_option_name,
       potl.description,
       po.profile_option_id,
       DECODE(pov.level_id, 10001, 'Site',
                            10002, 'Application',
                            10003, 'Responsibility',
                            10004, 'User',
                            10005, 'Server',
                            10006, 'Org',
                            10007, DECODE(to_char(pov.level_value2), '-1', 'Responsibility',
                                   DECODE(to_char(pov.level_value), '-1', 'Server',
                                   'Server+Resp')),
                            'UnDef') level_set,
       DECODE(to_char(pov.level_id), '10001', '',
                                     '10002', app.application_short_name,
                                     '10003', rsp.responsibility_key,
                                     '10004', usr.user_name,
                                     '10005', svr.node_name,
                                     '10006', org.name,
                                     '10007', DECODE(to_char(pov.level_value2), '-1', 
                                                     rsp.responsibility_key,
                                              DECODE(to_char(pov.level_value), '-1',
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2),
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2)||'-'||
                                                              rsp.responsibility_key)),
                                     'UnDef') context,
       pov.profile_option_value value
FROM fnd_profile_options po,
     fnd_profile_options_tl potl,
     fnd_profile_option_values pov,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility rsp,
     fnd_nodes svr,
     hr_operating_units org
WHERE po.profile_option_id = pov.profile_option_id
AND   po.profile_option_name = potl.profile_option_name
AND   usr.user_id (+) = pov.level_value
AND   app.application_id (+) = pov.level_value
AND   rsp.application_id (+) = pov.level_value_application_id
AND   rsp.responsibility_id (+) = pov.level_value
AND   svr.node_id (+) = pov.level_value
AND   org.organization_id (+) = pov.level_value
AND   po.profile_option_name = profile_option_name
ORDER BY 1, 5, 6
Tested with 11i.

Comments

Post a Comment

Popular posts from this blog

How to create a custom integration interface in SOA Gateway

-
Image
This example will illustrate on how to develop a simple interface using PL/SQL. The Integration Repository will recognize integrations through annotations in the code (details about annotations can be found in the Integrated SOA Gateway Developer's Guide, Appendix A). You can annotate *.pls and *.pkh files. For PL/SQL packages, only the package spec should be annotated. Do not annotate the body. Example of a annotated package spec: CREATE OR REPLACE PACKAGE xx_test_soagway AS /* $Header: $ */ /*# * This package returns different data from Financials (GL). * @rep:scope public * @rep:product gl * @rep:displayname xx_test_soagway * @rep:lifecycle active * @rep:compatibility S * @rep:category BUSINESS_ENTITY GL_ACCOUNT_COMBINATION */ /*# * Returns CCID * @param P_SEGMENT1 varchar2 Segment 1 * @param P_SEGMENT2 varchar2 Segment 2 * @param P_SEGMENT3 varchar2 Segment 3 * @param P_SEGMENT4 varchar2 Segment 4 * @param P_SEGMENT5 varchar2 Segment 5 * @return CCID ...
Read more

Fix for java.sql.SQLException: Invalid column type when extending a VO

-
Image
When you are extending a view object (VO) in OA Framework it's possible that you will run in to the problem  java.sql.SQLException: Invalid column type , especially if it's an LOV VO you are trying to extend. When you click on the LOV torch for the first time it is working fine. But whenever you click on the GO button in the LOV Region it gives an error. The error can look like this: oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.   Statement: SELECT * FROM (SELECT ca.location LocId, ca.party_site_number LocNumber, ca.party_name PartyName, ca.party_number PartyNumber, ca.address1 Address1, ca.address2 Address2, ca.address3 Address3, ca.address4 Address4, ca.city City, ca.state State, ca.zip PostalCode, ca.country Country, ca.party_id partyId, hzp.site_use_type CurrSiteUsage FROM csi_hzpty_addresses_v ca, hz_party_site_uses hzp, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu W...
Read more

Status of a customer

-
Image
One of my clients who run the Finance modules (AR, ARP, GL etc), Service Contracts and Field Service in E-Business Suite R12 had some issues with customers who were inactive. The issue arised when they were creating Service Requests (through API cs_servicerequest_pub.Create_ServiceRequest) and got an error reported by the API that the request could  not be created due to the customer was invalid. The reason that a customer is invalid can vary but one reason is that some level of the customer is inactive. In AR and the TCA model the customer (party) status can be set in the following tables: hz_parties (TCA) hz_party_sites (TCA) hz_party_site_uses (TCA) hz_cust_accounts (TCA-AR) hz_cust_acct_sites_all (TCA-AR) hz_cust_site_uses_all (TCA-AR) In my clients case status was Active in all tables except  hz_party_site_uses . Below I will explain how it's possible to change the statuses. Set status in Account Receivables If you inactivate an Account Site Address/Acc...
Read more