Query to Find On Hand Quantity in Oracle Apps R12

SELECT  segment1,
  it.inventory_item_id,
  it.organization_id,
  transaction_quantity,
  internal_ORDER_FLAG
FROM mtl_system_items_b it,--MATERIAL TABLE
            mtl_onhand_quantities onhand -- ONHAND QTY TABLE
WHERE it.inventory_item_id  = onhand.inventory_item_id
AND it.organization_id      = onhand.organization_id
AND it.organization_id      = 4
AND IT.INVENTORY_ITEM_ID= 28934;

OUTPUT :
SEGMENT1            ITEM_ID     ORG_ID    TRANSACTION QTY    INTERNAL_ORDER_FLAG
M0800RCSM004 28934            4                     42                                   Y
M0800RCSM004 28934            4                     90                                   Y

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find Pricing Table List in Oracle Apps R12

SELECT    qlhb.list_header_id,qlhb.list_type_code,qlht.name,qlht.description

FROM qp_list_headers_b qlhb,
             qp_list_headers_tl qlht
WHERE qlhb.list_header_id = qlht.list_header_id
 AND qlht.name = 'COMM_CONT';

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find Concurrent Programs Performance Query in Oracle Apps R12

SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,' R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
FROM  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
       AND f.program_application_id = p.application_id
       AND f.concurrent_program_id = pt.concurrent_program_id
       AND f.program_application_id = pt.application_id
       AND pt.user_concurrent_program_name='Order Import'
ORDER BY
      f.actual_start_date DESC;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

How to Set Org Context in 11i and R12

In 11i:

Begin
    dbms_application_info.set_client_info('&ORG_ID');
End;


In R12:

BEGIN
   MO_GLOBAL.SET_POLICY_CONTEXT('S', <orgid>);
END; 

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Find the Product Installed on your EBS Environment

SELECT a.application_id,
  a.application_short_name,
  a.application_name,
  b.oracle_id,
  b.last_update_date,
  b.product_version,
  b.patch_level
FROM Fnd_application_vl a,
            Fnd_PRODUCT_INSTALLATIONS b
WHERE a.application_id = b.application_id;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.