Monday, 7 November 2016

Hierarchical Query n Top n Analysis

--find manager against employee
select e.employee_id,e.first_name,m.first_name,e.manager_id
from employees e , employees m
where e.manager_id = m.employee_id ;

--Advance Queries...

-- Level -- To display/print list from 1 to  15 
    SELECT LEVEL
      FROM DUAL
CONNECT BY LEVEL <= 15;

--1. SELECT… CONNECT BY

Select data with a hierarchical (parent/child) relationship. (also used by EXPLAIN PLAN)

Syntax:

   SELECT…clause 
   [START WITH initial_condition]
   CONNECT BY [nocycle] PRIOR recurse_condition
   [ORDER SIBLINGS BY order_by_clause]

 Key:
    START WITH        : The row(s) to be used as the root of the hierarchy
    CONNECT BY        : Condition that identifies the relationship between
                        parent and child rows of the hierarchy
    NOCYCLE           : Do not circle around loops (where the current row has
                        a child which is also its ancestor.)
    ORDER SIBLINGS BY : Preserve ordering of the hierarchical query 
                        then apply the order_by_clause to the sibling rows
Examples
--Display employee and its manager 
    SELECT employee_id, manager_id
      FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH employee_id = 100;

--Display the full tree 
    SELECT LEVEL emp_level,
  -- LPAD will just pad the spaces before the e_name as per the Level hierarchy 
           LPAD (' ', 2 * (LEVEL - 1)) || Last_name || ', ' || First_name s
      FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

--Specifies a condition that identifies the row(s) to be used as the root(s) of a hierarchical query
    SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;  

-- Connect_By_Root
-- The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:


    SELECT first_name || ', ' || last_name emp_name,
-- Connect_By_Root will give the list of Manager 
           CONNECT_BY_ROOT first_name || ', ' || last_name MGR_name,
           LEVEL - 1 PATHLEN,
           department_id
      FROM employees
-- Level >1 will give the employee list excluding the Top manager 
 WHERE LEVEL > 1 AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id
;

    SELECT last_name,
           CONNECT_BY_ROOT last_name            MGR,
           LEVEL - 1                            PATHLEN,
-- SYS_CONNECT_BY_PATH will give the hierarchical structure of emp / manager relationship 
           SYS_CONNECT_BY_PATH (last_name, '/') PATH
      FROM employees
     WHERE LEVEL > 1 AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id
  ORDER BY last_name, MGR, PATHLEN, PATH;
  
  
  
3. --Display the Hierarchical/ Tree structure of employees and there department
    SELECT LPAD (' ', LEVEL * 2, ' ') || last_name LNAME, d.department_id
      FROM employees e, departments d
     WHERE e.department_id = d.department_id
START WITH employee_id = 100
CONNECT BY PRIOR e.employee_id = e.manager_id;  

O2C Cycle Technical Flow

Generic Order to Cash Cycle

Standard Order to Cash Cycle
Entered: - When the Order is Created in the system EBS below tables are affected
select * from oe_order_headers_all
(FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N)
select * from oe_order_lines_all 
( FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, OPEN_FLAG = Y)
select * from oe_transaction_types_tl

After the Order is Booked 
select * from oe_order_headers_all
(FLOW_STATUS_CODE = Booked, BOOKED_FLAG = Y)
select * from oe_order_lines_all 
(Flow_status_code = Awaiting_Shipping, BOOKED_FLAG = Y)
select * from wsh_delivery_details 
(Release_status= R - ready to release, SOURCE_LINE_ID = order Lines’s LINE_ID)
select * from wsh_delivery_assignments
(DELIVERY_ASSIGNMENT_ID  = wsh_delivery_details.DELIVERY_DETAIL_ID, DELIVERY_ID remains blank till this stage)
In shipping transaction form order status remains “Ready to Release”. 
At the same time, Demand interface program runs in background and insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE.
select * from mtl_demand
(DEMAND_SOURCE_LINE = order Lines’s LINE_ID)

Reservation:
This step is required for doing reservations. SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully completed, the MTL_DEMAND and MTL_RESERVATIONS tables get updated
select * from mtl_demand
(DEMAND_SOURCE_LINE = order Lines’s LINE_ID)
select * from mtl_reservations

Pick Released

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order. 
Pick Release can be done from 
(N) Shipping >Release Sales Order > Release Sales Order or ‘Pick release SRS’ program can be scheduled in background.
If specific line needs to be pick release, it can be done from ‘Shipping Transaction form’. For this case Pick Release is done from ‘Release Sales Order’ form with Pick Confirm=NO.
select * from oe_order_headers_all
(Flow_status_code = Booked)
select * from oe_order_lines_all 
(Flow_status_code = Picked)
select * from wsh_delivery_details 
(Release_status= S)
select * from wsh_delivery_assignments
((DELIVERY_ASSIGNMENT_ID  = wsh_delivery_details.DELIVERY_DETAIL_ID, DELIVERY_ID which comes from WSH_NEW_DELIVERIES)
select * from mtl_demand 
(records populated)
select * from mtl_reservations 
(records populated)
After the Pick Release for the Order is done WSH_NEW_DELIVERIES a new record gets inserted 
select * from wsh_new_deliveries
( SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP (Open))
select * from MTL_MATERIAL_TRANSACTIONS 
(Two records: 1.issue item (-ve) 2.Revieced item in Sub Inv (+ve))

Pick Confirm/ Move Order Transaction:
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Sales Order lines status is changed to ‘Picked’ and in the Shipping Transaction Form the status is shown as ‘Staged/Pick Confirmed’.

Ship Confirmed
(N) Shipping >Transaction
Interface Trip Stop Program (Update OE_INTERFACE_FLAG = Y and INV_INTERFACED_FLAG = Y in the WSH_DELIVERY_DETAILS table)
select * from oe_order_headers_all
(Flow_status_code = Booked)
select * from oe_order_lines_all (Flow_status_code = Shipped)
select * from wsh_delivery_details (Release_status= D  and OE_INTERFACE_FLAG = Y and INV_INTERFACED_FLAG = Y )
select * from wsh_delivery_assignments
select * from mtl_demand (records deleted)
select * from mtl_reservations (records deleted)
select * from wsh_new_deliveries
select * from MTL_MATERIAL_TRANSACTIONS (One more record populated: 1.sale order issue (-ve))
  Shipping Module : 
select * from WSH_DELIVERY_LEGS
select * from WSH_TRIP_STOP
select * from WSH_TRIP

Interface 
select * from RA_INTERFACE_LINES_ALL
select * from RA_INTERFACE_SALESCREDITS_ALL 
Run Workflow Background Process
(N)(M) View>request>(B)Submit New Request >(RB)Single Request >(B)OK
for Item Type: OM Order Line
This request submit Internally “ADS, (Autoinvoice Master Program)” Which will import the invoice to Receivables Module
select * from RA_CUSTOMER_TRX_ALL (INTERFACE_HEADER_ATTRIBUTE1 = Sales Order Number)
select * from RA_CUSTOMER_TRX_LINES_ALL 
select * from AR_PAYMENT_SCHEDULES_ALL
Create Receipts
(N)Responsibility > Receivables
AR_RECEIVABLE_APPLICATIONS_ALL
select * from oe_order_headers_all
(Flow_status_code = Closed)
STATUS on AR_CASH_RECEIPTS_ALL is changed to APP
AMOUNT_DUE_REMAINING is became 0

Standard Order to Cash Cycle with backOrder
Entered
Booked
Pick Released
Ship Confirmed
(N) Shipping >Transaction
During the ship confirm activity we can do backorder the unshipped quantity.
a. Enter the sales order number in the query manager form (B) Find
b. Go to (T) Lines/LPN’s tab
c. Enter the Shipped quantity and backordered quantity
d. Go to (T) Delivery
e. Select Ship confirm action
f. Select the radio button Ship enterd Quantitied
g. Select Backorder in unspecified quantity field
Two records created in OE_ORDER_HEADERS_ALL 
RELEASE_STATUS = B for backorder in WSH_DELIVERY_DETAILS
-- Find the Line_nummber 
SELECT    TO_CHAR (l.line_number)
       || DECODE (l.shipment_number,
                  NULL, NULL,
                  '.' || TO_CHAR (l.shipment_number))
       || DECODE (l.option_number,
                  NULL, NULL,
                  '.' || TO_CHAR (l.option_number))
       || DECODE (
             l.component_number,
             NULL, NULL,
                DECODE (l.option_number, NULL, '.', NULL)
             || '.'
             || TO_CHAR (l.component_number))
       || DECODE (
             l.service_number,
             NULL, NULL,
                DECODE (l.component_number, NULL, '.', NULL)
             || DECODE (l.option_number, NULL, '.', NULL)
             || '.'
             || TO_CHAR (l.service_number)) LINE_NUMBER
      ,h.order_number, l.ORDERED_ITEM, l.*
  FROM oe_order_headers_all h, oe_order_lines_all l
 WHERE h.header_id = l.header_id AND l.open_flag = 'Y'
 AND h.header_id =  2525423

--=============================================================
-- Find Onhand Item Quanitity for perticular Sales Order 
  SELECT ola.line_id,
         mo.inventory_item_id,
         OLA.ORDERED_ITEM,
         wdd.SUBINVENTORY,
         mmt.organization_id,
         OLA.ORDERED_QUANTITY,
         SUM (mo.TRANSACTION_QUANTITY)
    FROM wsh_delivery_details wdd,
         oe_order_lines_all ola,
        -- wsh_delivery_assignments wda,
         MTL_MATERIAL_TRANSACTIONS mmt,
         mtl_onhand_quantities mo
   WHERE     wdd.source_line_id = ola.line_id
         AND ola.header_id = 2502719
        -- AND wdd.DELIVERY_DETAIL_ID = wda.DELIVERY_DETAIL_ID
         AND mmt.source_line_id = ola.line_id
         AND wdd.subinventory = mmt.SUBINVENTORY_CODE
         AND mo.inventory_item_id = ola.inventory_item_id
         AND mmt.organization_id = mo.organization_id
         AND mo.SUBINVENTORY_CODE = mmt.SUBINVENTORY_CODE
         AND wdd.released_status = 'B'
GROUP BY ola.line_id,
         mo.inventory_item_id,
         OLA.ORDERED_ITEM,
         wdd.SUBINVENTORY,
         mmt.organization_id,
         OLA.ORDERED_QUANTITY
--having SUM(mo.TRANSACTION_QUANTITY) < OLA.ORDERED_QUANTITY
ORDER BY 1, 2 

--===========================================================
To generate a debug for Pick Release:

1. Following Profile Options need to be set before each test case, then reset after completion:
     OM: Debug Level - set to 5
     WSH: Debug Enabled - set to Yes
     INV: Debug Trace - set to Yes
2. The following Profile Option once set do not need to be changed.
     WSH: Debug Level - set to Statement


     INV: Debug Level - set to 11 > 

SQL PLSQL interview Questions


SELECT Employee_ID, Salary, 
row_number() over (ORDER BY Salary DESC) AS DenseRank
FROM Employee;

SELECT Employee_ID, Salary, 
rank() over (ORDER BY Salary DESC) AS Ranking
FROM Employees;

SELECT Employee_ID, Salary, 
dense_rank() over (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

--*. Grouping ROLLUP 
--ROLLUP creates sub totals from most detailed to grand total.  It moves from right to left in the list of columns given in ROLLUP. 
--ROLLUP gives grand total for all column and subtotal for all columns excluding first column.
select ccode,fcode, count(*)
   from batches
  group by rollup(ccode,fcode);
----------------
--GROUPING function 
--This will return a value of 1 if the column’s value is generated by ROLLUP. So we can use DECODE and GROUPING functions to get the same result as the above.

 select decode(grouping(ccode),1,'ALL courses',ccode)  ccode,
        decode(grouping(fcode),1,'All faculty',fcode)  fcode,
        count(*) count
  from  batches
group by rollup(ccode,fcode);

--CUBE 
--CUBE generate subtotals and grand total for all menstioned columns. That means it provides number of batches taken by each faculty 
--also along with Roll Up functionality. 
select  decode(grouping(department_id), 1, 'All_Dept',department_id) dept_id ,  
      decode(grouping(e.MANAGER_ID), 1, 'All_mngr', manager_id) mngr_id, 
 round(avg(salary)), count(*) 
 from EMPLOYEES e
where e.MANAGER_ID is not null and e.DEPARTMENT_ID is not null
GROUP by CUBE( e.DEPARTMENT_ID,  e.MANAGER_ID)
order by  1, 2;

select decode(grouping(ccode),1,'ALL courses',ccode)  ccode,
       decode(grouping(fcode),1,'All faculty',fcode)  fcode, 
       count(*) count
from  batches
group by cube(ccode,fcode);

--*. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.


--*. Differnece between NVL and NVL2

SELECT nvl2('Col1', 'col2', 'col3') FROM dual; -- Return Col2 if col1 is NOT NULL else return col3 (if col1 is NULL).
SELECT nvl('Col1', 'col2') FROM dual; -- Return Col2 if col1 is null.

--*. What is COALESCE 
SELECT COALESCE('Col1', 'col2', 'col3', 'col4', 'col5') FROM dual; -- Return Col2 if col1 is null , if col2 is also NULL return col3.....

--SQL Interview Questions

--1. How do you check the current user name ?
sql show user;

--2. How do you change SQL prompt name?
set sqlprompt "test ".


--3. What is the output of SIGN function?
1 for posite value,
0 for Zero,
-1 for Negaative value.

--4. What are different type of Joins in oracle SQL?
Equi Join
NON Equi Join
Inner Join
Outer JOin  Left outer, Right Outer,
Full Outer 
Self Join
Cartesian Join 

--5. How do you switch to DOS prompt?
SQL host

--6. How do you eliminate the duplicate rows from a table?
SQL: delete FROM emp
     WHERE ROWID not in (select MAX (ROWID) 
                          FROM emp 
                      group by empno);
                      
--7. How do you display row number with records
SQL select rownum, sal, ename FROM emp;

--8. How do you display the records between two ranges?
    select rownum , ename, empno 
      FROM emp
     WHERE rowid in (select rowid FROM emp WHERE rownum <= &upto-MAX
                      MINUS
                      select rowid FROM emp WHERE rownum <= &start-MIN);
                      
--9. NVL function only allow the name data typw (ie. number or char or date NVL(comm, 0)), if commission is null 
  --then the text "Not Applicatible" want to display, instead of blank space. How do you write the query?
SQL select NVL(to_char(comm.),'NA') from table_name;

--10. How do you find out nth Highest salary from emp table 
    select distinct (a.sal) 
      FROM emp a 
     where &N = (select count(distinct (b.sal) FROM emp b WHERE a.sal >= b.sal));
     
--11. How do you view installed Oracle version information?
SQL select baner FROM v$version;

--12. How do you display the number value in Words?
SQL select (to_char(to_date(salary, 'j'),'Jsp')) FROM employees;

--13. How do you display Odd/ Even number of  records?
Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum, 2) FROM emp);

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum, 2) FROM emp);

--14. Which date function return number value?
MONTHS_BETWEEN      -- return the number of months between two dates.

--15. How do you replace query result null value with a test?
SQL  set NULL 'N/A'
select * from emp where (rowid,0) in (select rowid, mod(rownum, 2) FROM emp);

to reset
SQL SET NULL ''

--16. What are the more common pseudo-columns?
SYSDATE, USER, UID, CURVAL, NEXTVAL, ROWID, ROWNUM

--17. What is the difference between Inner Join and Outer Join?
Inner join: return the rows satisfying the criteria from both the tables.

Outer Join: return the rows satifying the criteria from both tables and the unmatched rows from left, right or both the tables.

--18. When do you use WHERE clause and when do you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause.

WHERE clause is used when you want to specify a condition for columns.


---------------------------------------------------------------
-- Transpose of column to Row 
-- Pivot 


SELECT ...
FROM   ...
PIVOT [XML]
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...
/*
In addition to the new PIVOT keyword, we can see three new pivot clauses, described below.

pivot_clause: defines the columns to be aggregated (pivot is an aggregate operation);
pivot_for_clause: defines the columns to be grouped and pivoted;
pivot_in_clause: defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).
*/
CREATE TABLE yourTable(color varchar(5), Paul int, John int, Tim int, Eric int);

INSERT INTO yourTable  (color, Paul, John, Tim, Eric) VALUES    ('Red', 1, 5, 1, 3);
INSERT INTO yourTable  (color, Paul, John, Tim, Eric) VALUES    ('Green', 8, 4, 3, 5);
INSERT INTO yourTable  (color, Paul, John, Tim, Eric) VALUES    ('Blue', 2, 2, 9, 1);
/*
insert into yourTable select 
    'Red' ,1 ,5 ,1 ,3 from dual union all select
    'Green' ,8 ,4 ,3 ,5 from dual union all select
    'Blue' ,2 ,2 ,9 ,1 from dual ;
*/
select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name;

select * from YOURTABLE;

SELECT name, Red, Green, Blue
  FROM (SELECT name, color,  value  --sum(value) value
          FROM yourtable 
       UNPIVOT ( value FOR name IN (Paul, John, Tim, Eric) ) unpiv
--      group by color, name
      ) src 
 PIVOT ( SUM(value) FOR color IN ('Red' Red, 'Green' Green, 'Blue' Blue) ) piv;
--====================================== 
 WITH pivot_data AS (
         SELECT department_id, job, salary
         FROM   employees
where department_id in (10,20,30,40)
         )
 SELECT *
 FROM   pivot_data
 PIVOT (
            SUM(salary)        --<-- pivot_clause
        FOR department_id      --<-- pivot_for_clause
        IN  (10,20,30,40)    --<-- pivot_in_clause
       );


---==============================================
SQL PLSQL Fundamental Question

--Raise -- To raise Exception.
--RAISE used inside exception will ReRaise the same exception 
DECLARE
 v_msg  VARCHAR2(100);
 v_name VARCHAR2(100);
BEGIN
 BEGIN
SELECT e.LAST_NAME INTO v_name FROM employees e WHERE first_name = 'Sandeep';
 EXCEPTION
 WHEN no_data_found THEN
SYS.DBMS_OUTPUT.put_line ('1st exception');
raise;
 END;
EXCEPTION
WHEN too_many_rows THEN
 SYS.DBMS_OUTPUT.put_line ('2st exception');
 when others then 
 SYS.DBMS_OUTPUT.put_line ('Other exception');
END;

--============================================================================
--PROCEDURE / PACKAGE : IN mode 
--IN parameters lets you pass a value to the subprogram being called. The value cannot be changed inside the subprogram. It is like a constant in the subprogram.


CREATE OR REPLACE PROCEDURE test_in_para_prod (test_para in varchar2)
IS
 v_msg VARCHAR2(100);
BEGIN
 v_msg := '1st Return Test start'||test_para;
 test_para := v_msg;
 DBMS_OUTPUT.PUT_LINE(test_para);
END;
9923894398 / 9011350481
Error(6,3): PLS-00363: expression 'TEST_PARA' cannot be used as an assignment target
--============================================================================
--Return in function 
select test_func from dual;
--Function is end at first use of RETURN
CREATE OR REPLACE FUNCTION test_func
 RETURN VARCHAR2
IS
 v_msg VARCHAR2(100);
BEGIN
 v_msg := '1st Return Test start';
 RETURN v_msg;
 V_msg:= '2nd Return after call Test Start';
 RETURN v_msg;
END;


FND_PROFILE and FND_GLOBAL values
Posted by Aopu Mohsin


Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');
   fnd_profile.value('MFG_ORGANIZATION_ID');
   fnd_profile.value('ORG_ID');
   fnd_profile.value('LOGIN_ID');
   fnd_profile.value('USER_ID');
   fnd_profile.value('USERNAME');
   fnd_profile.value('CONCURRENT_REQUEST_ID');
   fnd_profile.value('GL_SET_OF_BKS_ID');
   fnd_profile.value('SO_ORGANIZATION_ID');
   fnd_profile.value('APPL_SHRT_NAME');
   fnd_profile.value('RESP_NAME');
   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;
   FND_GLOBAL.APPS_INTIALIZE;
   FND_GLOBAL.LOGIN_ID;
   FND_GLOBAL.CONC_LOGIN_ID;
   FND_GLOBAL.PROG_APPL_ID;
   FND_GLOBAL.CONC_PROGRAM_ID;
   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;
   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;
   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;
   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');
   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;
   v_resp_id       := fnd_global.resp_id;
   v_user_id       := fnd_global.user_id;
     
   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);

Tuesday, 31 May 2016

Alter the Workflow Notification Timeout for testing directly from Backend



SELECT aav.PROCESS_ACTIVITY_ID, 
       pa.process_item_type,
       pa.process_name,
       pa.process_version,
       pa.activity_item_type,
       pa.activity_name,
       aav.value_type,
       aav.text_value,
       aav.number_value
  FROM wf_process_activities pa, wf_activity_attr_values aav
 WHERE    1=1 
       AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
       AND aav.process_activity_id = pa.instance_id
       AND activity_name = 'PO_REQ_APPROVE'
       AND aav.name = '#TIMEOUT'


UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2      --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_APPROVE'
                  AND aav.name = '#TIMEOUT')

UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2                                                 --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER1'
                  AND aav.name = '#TIMEOUT')

UPDATE WF_ACTIVITY_ATTR_VALUES
   SET NUMBER_VALUE = 2                                                 --2880
 WHERE process_activity_id IN
          (SELECT aav.PROCESS_ACTIVITY_ID
             FROM wf_process_activities pa, wf_activity_attr_values aav
            WHERE     1 = 1
                  AND pa.process_name = 'JDSU_NOTIFY_APPROVER_PROCESS_O'
                  AND aav.process_activity_id = pa.instance_id
                  AND activity_name = 'PO_REQ_REMINDER2'
                  AND aav.name = '#TIMEOUT')
   

Find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount

Below query will help to find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount. 


  SELECT prha.requisition_header_id,
         prha.segment1,
         gdr.conversion_rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (
                    DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                    1)),
            2)
            usd_amount,
         gdr.to_currency,
         gdr.from_currency,
         prla.rate,
         ROUND (
            SUM (
                 NVL (prda.req_line_quantity, 1)
               * NVL (prla.unit_price, 1)
               * NVL (prla.rate, 1)),
            2)
            line_currency_amount,
         prla.currency_code line_currency,
         ROUND (
            SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
            2)
            actual_requisition_amount,
         gs.currency_code func_currency,
         TRUNC (prha.creation_date) creation_date,
         gcc.segment3,
         prha.org_id
    FROM po_requisition_lines_all prla,
         po_req_distributions_all prda,
         po_requisition_headers_all prha,
         gl_code_combinations_kfv gcc,
         gl_sets_of_books gs,
         gl_daily_rates gdr
   WHERE     prha.requisition_header_id = prla.requisition_header_id
         AND NVL (prla.cancel_flag, 'N') <> 'Y'
         --         AND  prha.org_id = 9944
         AND prla.requisition_line_id = prda.requisition_line_id
         AND prha.authorization_status = 'APPROVED'
         AND prda.code_combination_id = gcc.code_combination_id
         AND gs.set_of_books_id = prda.set_of_books_id
         --         AND gs.currency_code <> prla.currency_code
         AND gdr.from_currency = gs.currency_code(+)
         AND gdr.to_currency(+) = 'USD'
         AND TRUNC (gdr.conversion_date) =
                TRUNC (NVL (prla.rate_date, prha.creation_date))
--         AND gcc.segment3 IN ('15510','15610','15620' )
--         AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
         prha.segment1,
         prla.rate,
         gdr.conversion_rate,
         TRUNC (prha.creation_date),
         gcc.segment3,
         gdr.to_currency,
         gdr.from_currency,
         gs.currency_code,
         prla.currency_code,
         prha.org_id
  HAVING SUM (
              NVL (prda.req_line_quantity, 1)
            * NVL (prla.unit_price, 1)
            * NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
                   1)) > 100000