Oracle FORM Personalization to make DFF Mandatory / Oracle FORM Personalization to creating DFF Mandatory

Making DFF Mandatory using Oracle Form Personalization: In Oracle R12:


Scenario: Solution:







Above Condition SQL Code:


:HEADER.USER_JE_SOURCE_NAME = 'Manual' and
:HEADER.SHOW_TAX_STATUS = 'Not Required' and
(SUBSTR(:LINES.ACCOUNTING_FLEXFIELD,11,6) IN ('113330','122235')) and
:LINES.CONTEXT is null




Creating the Scenario to test Personalization:









Here in above screen, I provided the all the required values so that it will not generate any error.
Below screen will generate the error.






Here in above screen second circle, provided the value for DFF and last field DFF value is empty, so that above error generated.

My Notes

Form Personalization:

http://manoharbabuapps.blogspot.com/2014/12/form-personalization-with-examples.html

SQL Query to check Site Level Profile Options enabled or not / Check Site Level enabled Profiles / Check Profiles

SELECT
    p.profile_option_name short_name,
    n.user_profile_option_name name,
    DECODE(
        v.level_id,
        10001,
        'Site',
        10002,
        'Application',
        10003,
        'Responsibility',
        10004,
        'User',
        10005,
        'Server',
        10006,
        'Org',
        10007,
        DECODE(
            TO_CHAR(v.level_value2),
            '-1',
            'Responsibility',
            DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
        ),
        'UnDef'
    ) level_set,
    DECODE(
        TO_CHAR(v.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(v.level_value2),
            '-1',
            rsp.responsibility_key,
            DECODE(
                TO_CHAR(v.level_value),
                '-1',
                (
                    SELECT
                        node_name
                    FROM
                        fnd_nodes
                    WHERE
                        node_id = v.level_value2
                ),
                (
                    SELECT
                        node_name
                    FROM
                        fnd_nodes
                    WHERE
                        node_id = v.level_value2
                )
                 ||  '-'
                 ||  rsp.responsibility_key
            )
        ),
        'UnDef'
    ) "CONTEXT",
    v.profile_option_value value
FROM
    fnd_profile_options p,
    fnd_profile_option_values v,
    fnd_profile_options_tl n,
    fnd_user usr,
    fnd_application app,
    fnd_responsibility rsp,
    fnd_nodes svr,
    hr_operating_units org
WHERE
    p.profile_option_id = v.profile_option_id (+)
AND
    p.profile_option_name = n.profile_option_name
AND
    upper(p.profile_option_name) IN (
        SELECT
            profile_option_name
        FROM
            fnd_profile_options_tl
        WHERE
            upper(user_profile_option_name) LIKE upper('MO: Operating Unit')
    )
AND
    usr.user_id (+) = v.level_value
AND
    rsp.application_id (+) = v.level_value_application_id
AND
    rsp.responsibility_id (+) = v.level_value
AND
    app.application_id (+) = v.level_value
AND
    svr.node_id (+) = v.level_value
AND
    org.organization_id (+) = v.level_value
AND
    DECODE(
        v.level_id,
        10001,
        'Site',
        10002,
        'Application',
        10003,
        'Responsibility',
        10004,
        'User',
        10005,
        'Server',
        10006,
        'Org',
        10007,
        DECODE(
            TO_CHAR(v.level_value2),
            '-1',
            'Responsibility',
            DECODE(TO_CHAR(v.level_value),'-1','Server','Server+Resp')
        ),
        'UnDef'
    ) = 'Site'
ORDER BY
    short_name,
    user_profile_option_name,
    level_id,
    level_set;

Optimizer costs don't match SQL execution performance

Question:  I am tuning an Oracle SQL query and I've noted that the costs don't match the execution speed and SQL where the optimizer shows a lower cost actually take more time to execute.  I want to know why in second case the cost is less but time taken to execute the query is more?
Answer:  It's important to note that the cost figures that are displayed in an execution plan are not described in any Oracle documentation, and it's been demonstrated that the plan with the lowest ?cost? number is not always the plan chosen by the optimizer.  Further, the ?cost? figures do not always indicate the ?best:? execution plan for a query, given the divergent optimizer goals of first_rows (optimizer for response time) and all_rows (optimize for minimizing computing resources).

In general, the lower the CBO-calculated cost, the faster the query will run, but we have to remember that the costs used by the CBO are only estimates, and the numbers are based on metadata statistics collected by dbms_stats.  The cost based optimizer (CBO) creates these estimates based on many variables:


you can refer below link for more details:


1) http://www.dba-oracle.com/t_cbo_cost_does_not_match_performance.htm


2) http://www.dba-oracle.com/t_display_sql_optimizer_features.htm
3) http://www.dba-oracle.com/t_system_session_fix_control.htm

list of major Oracle features by release

http://www.dba-oracle.com/t_history_oracle.htm

SQL Query to find Application Use Credentials from Backend / SQL Query to Get the User Passwords from back end based on the username

SELECT usr.user_name,
       get_pwd.decrypt (
          (SELECT (SELECT get_pwd.decrypt (
                             fnd_web_sec.get_guest_username_pwd,
                             usertable.encrypted_foundation_password)
                     FROM DUAL)
                     AS apps_password
             FROM fnd_user usertable
            WHERE usertable.user_name =
                     (SELECT SUBSTR (
                                fnd_web_sec.get_guest_username_pwd,
                                1,
                                  INSTR (fnd_web_sec.get_guest_username_pwd,
                                         '/')
                                - 1)
                        FROM DUAL)),
          usr.encrypted_user_password)
          PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = 'USERNAME';

Oracle OAF (Oracle Application Framework) VO Extension Steps: Technical Development

There are the scenarios for VO Extensions:


Step 1: If there is new field addition in the Page which is not there in the existing VO.
Step 2: There might be No New field addition in OAF Page, but there will be the requirement to    add the extra condition in WHERE CLAUSE or HAVING CLAUSE or GROUP BY.


=======================================


Now my scenario is Step 1: (My Requirement)


a. First understand the requirement where to do and what to do.
b. Find the right page (Page Path), and right VO (VO Path) where change is required. Here there will be the chances of Page Path and VO Path different.
c. Create the same VO path in local system.
d. Go JAVA_TOP in respected instance (Server) and path, download the ".class" files into local system of same path which is created in above step "c".
e. Open the JDeveloper and Create empty project with custom path like example:
xxcustom.oracle.apps.ap.invoice.request.webui
f. To avoid errors while creation of new vos, before creating a new custom vos, create the .java files for standard .class files, so that SQL can easily modifiable without any errors.
g. After creation of Project in JDeveloper create the New VO with naming conventions, details below:
VO Name: xxcustom..VO
Custom Path: xxcustom.oracle.apps.ap.invoice.request.server
Extends: select standard VO
path and vo like below:
oracle.apps.ap.invoice.request.server.ApinvoiceHdrDetailsVO
h. After creation of custom vo then compile the VO and Make the changes.
i. Do the Substitution for created custom VO and Standard VO:
Navigation: Project --> Properties --> Business Componenets --> Substitutions -->
right side select Standard VO and Left side Custom VO and finally Submit.
j. import the file into server with below commands:
jpximport D:\Yadul\p9879989_R12_160918\jdevhome\jdev\myprojects\xxprojectname.jpx -username apps -password apps-dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1988))(CONNECT_DATA=(SID=VIS12)))"
k. then after move the .class files into server.
l. move the .project file ".jpx" file. For this we need to create one custom path in Server Instance ($JAVA_TOP/xxcustom/jpxfiles/install/) , here we need to move the .jpx file.
m. here in the same path run the below command to implement the changes into the page:


java oracle.jrad.tools.xml.importer.JPXImporter $JAVA_TOP/xxcustom/jpxfiles/install/xxprojectname.jpx -userId 1 -username apps -password dwcsapps0119 -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1988))(CONNECT_DATA=(SID=VIS12)))"


n. Our changes done, we can see the changes in the standard page.