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;
/

Reset Password for Application User

DECLARE

v_user_name varchar2(30):=UPPER('VMALLAREDDY');

v_new_password varchar2(30):='welcome';

v_status BOOLEAN;

BEGIN

v_status:= fnd_user_pkg.ChangePassword (

    username => v_user_name,

    newpassword => v_new_password

);

IF v_status =TRUE THEN

dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

commit;

ELSE

DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

ROLLBACK;

END IF;

END;