Upgrade Issues

1. Output widths change after upgrade, or change of character set (Doc ID 330717.1)

Get Application Password

create or replace PACKAGE get_data
AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2;
END get_data;

create or replace PACKAGE BODY get_data
AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
(java.lang.String,java.lang.String) return java.lang.String';
END get_data;

Select Usr.User_Name, Usr.Description,
get_data.Decrypt((Select (Select get_data.Decrypt
(apps.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 = &user_name

How can customizations be removed from a workflow? (Doc ID 287369.1)

will prevent our seeded workflow from overwriting your custom process.

The custom process can be removed by one of two methods:

a. Run wfrmitt.sql to completely remove the workflow item_type from the database then reload it.
You will also need to reload the ones for each language.

OR

b. You retrieve it using the Workflow Builder then delete it before saving it back into the
database. This should remove the custom process.

Employee as Supplier in R12

http://oracleebusinesssuite.files.wordpress.com/2008/09/employee-as-supplier-in-payable.pdf

BI Publisher: Configuring Concurrent Programs to Publish Large Quantities of Data

Refer to the below website:

http://cyberserf.com/2012/05/31/bi-publisher-configuring-concurrent-programs-to-publish-large-quantities-of-data/

Payables Invoice Status

Invoice Status is not stored in AP_INVOICES_ALL. It is derived based on below rules:

The invoice header form derives the invoice validation status based on the following:

'Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'

'Never Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'

'Needs Revalidation'

-          If there are any rows in AP_HOLDS that do not have a release code.

-          If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.

-          If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).

I hope this information helps you.

Please refer below link for more information.

http://www.shareoracleapps.com/2010/10/api-to-find-ap-invoice-status.html

Workflow Roles and User/Person Synchronization

http://www.kmtconsulting.co.uk/appstechzone/2010/12/01/workflow-roles-and-userperson-synchronization/

To get Profile Option Level from Database

SELECT b.user_profile_option_name "Profile Name",
  DECODE (TO_CHAR (c.level_id), '10001', 'Site', '10002', 'Application', '10003', 'Responsibility', '10004', 'User', 'Unknown') "Level",
  DECODE ( TO_CHAR (c.level_id), '10001', 'Site', '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)), '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)), '10004', NVL (e.user_name, TO_CHAR (c.level_value)), 'Unknown') "Level Value",
  c.profile_option_value
FROM apps.fnd_profile_options a,
  apps.FND_PROFILE_OPTIONS_VL b,
  apps.FND_PROFILE_OPTION_VALUES c,
  apps.FND_USER d,
  apps.FND_USER e,
  apps.FND_RESPONSIBILITY_VL g,
  apps.FND_APPLICATION h
WHERE b.profile_option_name LIKE 'XX%'
AND a.profile_option_name = b.profile_option_name
AND a.profile_option_id   = c.profile_option_id
AND a.application_id      = c.application_id
AND c.last_updated_by     = d.user_id(+)
AND c.level_value         = e.user_id(+)
AND c.level_value         = g.responsibility_id(+)
AND c.level_value         = h.application_id(+)
ORDER BY b.user_profile_option_name,
  c.level_id,
  DECODE ( TO_CHAR (c.level_id), '10001', 'Site', '10002', NVL (h.application_short_name, TO_CHAR (c.level_value)), '10003', NVL (g.responsibility_name, TO_CHAR (c.level_value)), '10004', NVL (e.user_name, TO_CHAR (c.level_value)), 'Unknown');

2-way, 3-way, and 4-way Matching

2-way, 3-way, and 4-way Matching

When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching).

Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.

Monitor Concurrent program running SQL

select SQL_TEXT from v$sql where sql_id = (
select d.sql_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 2450673
AND a.phase_code = 'R');

Update email address on FND_USER (Useful for data scrambling)

Declare
  v_action_UorR VARCHAR2(1) := 'U'; -- 'U' for Update email address and 'R' for revert the update
  V_Session_Id INTEGER := Userenv('sessionid');
  result       BOOLEAN;
  v_user_id    INTEGER;
  CURSOR Cur_User
  IS
    Select * From Fnd_User
    Where User_Name Like 'VMALLAREDDY%'
    AND email_address is not null;
Begin
  IF &v_action_UorR = 'U' THEN
  FOR Rec_User IN Cur_User
  LOOP
    Fnd_User_Pkg.Updateuser(X_User_Name => Rec_User.User_Name ,
                            X_Owner => 'SEED' ,
                            X_Description => Rec_User.Description,
                            X_Email_Address=> Rec_User.Email_Address||'_XX'
                            );
    Dbms_Output.Put_Line ( 'User updated '||rec_user.user_name ) ;
  END LOOP;

  Else
    FOR Rec_User IN Cur_User
  LOOP
    Fnd_User_Pkg.Updateuser(X_User_Name => Rec_User.User_Name ,
                            X_Owner => 'SEED' ,
                            X_Description => Rec_User.Description,
                            X_Email_Address=> replace(Rec_User.Email_Address,'_XX','')
                            );
    Dbms_Output.Put_Line ( 'User updated '||rec_user.user_name ) ;
  End Loop;
  END IF;
  Commit;
EXCEPTION
   WHEN OTHERS THEN
        Dbms_Output.Put_Line ('Failed'|| Substr (Sqlerrm, 1, 100));
        Rollback;
END;
/