AP Invoice Approval Status Tracking:

AP Invoice Approval Status Tracking:

Invoice Validation
Invoice Approval Workflow

select a.invoice_id,a.invoice_num, a.invoice_amount, a.approval_ready_flag, a.approval_iteration, a.wfapproval_status
from
ap_invoices_all a
where invoice_num = 'INV_TEST_2' ;

select * from
ap_inv_aprvl_hist_all
where invoice_id = 53931065 ;

select * from
wf_notifications
where item_key like '53931065%'
and message_type = 'APINVAPR';

SQL Query to get Oracle Value Set definition

Query 1:

select b.flex_value_set_name,a.additional_where_clause from FND_FLEX_VALIDATION_TABLES a, fnd_flex_value_sets b
where a.application_table_name = 'MTL_SECONDARY_INVENTORIES'
and a.value_column_name ='SECONDARY_INVENTORY_NAME'
and a.flex_value_set_id = b.flex_value_set_id



Query 2:

SELECT ffvs.flex_value_set_id ,
       ffvs.flex_value_set_name ,
       ffvs.description set_description ,
       ffvs.validation_type,
       ffvt.value_column_name ,
       ffvt.meaning_column_name ,
       ffvt.id_column_name ,
       ffvt.application_table_name ,
       ffvt.additional_where_clause
FROM   APPS.fND_FLEX_VALUE_SETS FFVS ,
       APPS.FND_FLEX_VALIDATION_TABLES FFVT
WHERE  ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name IN ('VALUE SET NAME')

Generate Spool for multiple db objects / How to generate Spool for multiple DB objects in oracle

SET PAGES 0

SET TIMING   OFF
SET ECHO     OFF
SET FEEDBACK OFF

SET TERMOUT  OFF

SPOOL C:\Emirates\Assignments\views\manyspools_8.sql


SELECT 'SPOOL C:\Emirates\Assignments\objects\' || object_name || '.sql' || CHR (10) ||
       'SELECT text from dba_source WHERE name = ''' || object_name || ''';' || CHR (10) ||
       'SPOOL OFF'
  from xxtemp_hr_stg where object_type in ('PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION');

SPOOL OFF

@ C:\Emirates\Assignments\objects\manyspools_8.sql

SET TERMOUT ON

--Note:

-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of table "dba_source", need to use dba_views for views

--My stage table: where my list of objects stored in stage table

--xxtemp_hr_stg

--Table Script:

create table xxtemp_hr_stg

(

 SNO NUMBER,

 OWNER VARCHAR2(10),

 OBJECT_NAME VARCHAR2(100),

 OBJECT_TYPE VARCHAR2(20),

 REMARKS VARCHAR2(2000),

 MODIFY_YN VARCHAR2(2000),

 ADDITIONAL_COMMENTS VARCHAR2(4000)

);

Generate Spool file for Single (particular DB Object) / How to generate the spool for db objects in oracle

set feedback off
set heading off
set timing off
set termout off
set linesize 1000
set trimspool on
set verify off
spool C:\YADUL\FILES_220519\XX_DB_OBJECT_NAME.sql
prompt set define off
select text
    from dba_source
    where name = upper('XX_DB_OBJECT_NAME')
    order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
set timing on

--Note:
-- This script can run from SQL Developer / TOAD / PLSQL Developer
-- In above script, instead of this table "dba_source", need to user dba_views for views

SQL Query to find Responsibility for given Request group / Find Request group attached responsibilities

SELECT responsibility_name ,
  request_group_name        ,
  frg.description
   FROM fnd_request_groups frg,
  fnd_responsibility_vl frv
  WHERE frv.request_group_id = frg.request_group_id
AND request_group_name    LIKE 'Request group name'
ORDER BY responsibility_name;

SQL Query to find Concurrent program details with Parameters, status, submitted date and responsibility

SELECT
    cpt.user_concurrent_program_name   "Concurrent Program Name",
    decode(rgu.request_unit_type, 'P', 'Program', 'S', 'Set',
           rgu.request_unit_type) "Unit Type",
    fcr.status_code                    status,
    fcr.request_date                   prog_submitted_date,
    fnr.responsibility_name,
    fcr.argument1
    || '-'
    || fcr.argument2
    || '-'
    || fcr.argument3
    || '-'
    || fcr.argument4
    || '-'
    || fcr.argument5
    || '-'
    || fcr.argument6
    || '-'
    || fcr.argument7
    || '-'
    || fcr.argument8
    || '-'
    || fcr.argument9
    || '-'
    || fcr.argument10
    || '-'
    || fcr.argument11
    || '-'
    || fcr.argument12
    || '-'
    || fcr.argument13
    || '-'
    || fcr.argument14
    || '-'
    || fcr.argument15 parameters,
    cp.concurrent_program_name         "Concurrent Program Short Name",
    rg.application_id                  "Application ID",
    rg.request_group_name              "Request Group Name",
    fat.application_name               "Application Name",
    fa.application_short_name          "Application Short Name",
    fa.basepath                        "Basepath",
    cpt.concurrent_program_id
FROM
    fnd_request_groups           rg,
    fnd_request_group_units      rgu,
    fnd_concurrent_programs      cp,
    fnd_concurrent_programs_tl   cpt,
    fnd_application              fa,
    fnd_application_tl           fat--,
    ,
    fnd_concurrent_requests      fcr,
    fnd_responsibility_tl        fnr
WHERE
    rg.request_group_id = rgu.request_group_id
    AND rgu.request_unit_id = cp.concurrent_program_id
    AND cp.concurrent_program_id = cpt.concurrent_program_id
    AND rg.application_id = fat.application_id
    AND fa.application_id = fat.application_id
    AND cpt.language = userenv('LANG')
    AND fat.language = userenv('LANG')
    AND cpt.concurrent_program_id = fcr.concurrent_program_id
    AND fcr.responsibility_id = fnr.responsibility_id
    AND cpt.user_concurrent_program_name = 'Concurrent program name'
ORDER BY
    fcr.request_date DESC

How to find password of a User in Oracle Apps R12

--Package Specification

CREATE OR REPLACE PACKAGE get_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_pwd;

/

--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
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_pwd;
/

--Query to execute
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 = '&USER_NAME';

How to find patches applied/installed on Oracle database

SELECT
e.patch_name,
c.end_date,
a.bug_number,
b.applied_flag
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
AND a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id

AND d.applied_patch_id = e.applied_patch_id

For Oracle 11g Release:

1. using Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2.  select * from sys.registry$history;



For Oracle 12c Release:

1. Opatch utility

cd $ORACLE_HOME/OPatch

opatch lsinventory

2. using dba_registry_sqlpatch view:

SQL> select * from dba_registry_sqlpatch;

3. using package dbms_qopatch

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Profile to setup the language for Oracle Tools in Local Machine

Profile to setup the language for Oracle Tools in Local Machine

Start -> Run (CMD) -> REGEDIT --> Enter,

Then search for specific Tool (Report Builder, Workflow (WF) or ..etc)

API to Cancel Approved Purchase Order (In whole PO or Particular PO Line)

API to Cancel Approved Purchase Order

Complete PO:

DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12

--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
null, -- p_doc_line_num
null, -- p_doc_line_loc_id
null, -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor

-- Get any messages returned by the Cancel API

FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END;

Specific Line :

DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12

--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
'1', -- p_doc_line_num
null, -- p_doc_line_loc_id
null, -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor

-- Get any messages returned by the Cancel API

FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END;

Finding Nth highest salary in a table

Finding Nth highest salary in a table, Here is a way to do this task using dense_rank() function.


select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;


DENSE_RANK :

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

2. This function accepts arguments as any numeric data type and returns NUMBER.

3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
 
4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.