Onhand Quantity on any historical Date

Now here is another requirement. There is a need to get onhand quantity of an item in a manufacturing organization at any given date.
Oracle has provided with a report that gives this information. The name of concurrent program is Transaction historical summary

But has that solved all our problem, what if there is a requirement and we need to use a query in a report to do some calculation or have some logic based upon historical value.
Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id

Note: The query is not fully tested, if found any problem please comment with issue.

API to Create User

The code below is very useful if you want to create a user without logging into application and avoid entering same information again and again. Also if the instances are refresed frequently and the user does not exist in production the script can be very handy.
The script below will prompt for user name and employee name. A commit is required at the end to have affect in the application.

DECLARE
 v_session_id INTEGER := userenv('sessionid');
 v_user_name  VARCHAR2(30) := upper('&User_Name');
 v_employee_name per_all_people_f.full_name%TYPE := '&employee_name';
 v_employee_id NUMBER;
 v_email_address per_all_people_f.email_address%TYPE;
BEGIN
   BEGIN
   SELECT person_id, email_address
   INTO   v_employee_id
       , v_email_address
   FROM   per_all_people_f
   WHERE  upper(full_name) LIKE Upper('%v_employee_name%')
   GROUP  BY person_id
           ,email_address;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('Employee '||v_employee_name
              ||' does not exist');
   WHEN OTHERS THEN
       dbms_output.put_line('Unexpected Error '||SQLERRM);
       dbms_output.put_line('while selected person_id');   
   END;
 fnd_user_pkg.createuser(x_user_name                  => v_user_name
                        ,x_owner                      => ''
                        ,x_unencrypted_password       => 'welcome1'
                        ,x_session_number             => v_session_id
                        ,x_start_date                 => SYSDATE - 10
                        ,x_end_date                   => SYSDATE + 100
                        ,x_last_logon_date            => SYSDATE - 10
                        ,x_description                => v_user_name||' Created using API'
                        ,x_password_date              => SYSDATE - 10
                        ,x_password_accesses_left     => 10000
                        ,x_password_lifespan_accesses => 10000
                        ,x_password_lifespan_days     => 10000
                        ,x_employee_id                => v_employee_id
                        ,x_email_address => v_email_address
                        ,x_fax           => ''
                        ,x_customer_id   => ''
                        ,x_supplier_id   => '');
 fnd_user_pkg.addresp(username       => v_user_name
                     ,resp_app       => 'SYSADMIN'
                     ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                     ,security_group => 'STANDARD'
                     ,description    => 'System Administrator'
                     ,start_date     => SYSDATE - 10
                     ,end_date       => SYSDATE + 1000);
-- Created by Suresh Vaishya
END;
/

Link Purchase Order and Requisition

You have a purchase order and you have a requisition, but wait how do you know how this purchase order is linked with requisition. Here is the query thats answers this. Use this query find linked Purchase order and Requisition.
This could be really a helpful one.

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

Find concurrent program & Request set attached to a responsibility

I have a concurrent program which I want to submit .. but here is the problem. I dont know which responsibility this program is attached to. No wonder .. below is the query that can be used to find the list of responsibilities to which the programs is attached to.

Query to find concurrent program

    select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    From fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.concurrent_program_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    and fcpt.language = USERENV('LANG')
    and fcpt.user_concurrent_program_name = :conc_prg_name
    order by 1,2,3,4

Query to find Request Set

    select frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_request_set_name
    From apps.fnd_Responsibility fr, apps.fnd_responsibility_tl frt,
    apps.fnd_request_groups frg, apps.fnd_request_group_units frgu,
    apps.fnd_request_Sets_tl fcpt
    where frt.responsibility_id = fr.responsibility_id
    and frg.request_group_id = fr.request_group_id
    and frgu.request_group_id = frg.request_group_id
    and fcpt.request_set_id = frgu.request_unit_id
    and frt.language = USERENV('LANG')
    and fcpt.language = USERENV('LANG')
    and fcpt.user_request_set_name = :request_set_name
    order by 1,2,3,4

Related Post:
Query to get Concurrent Program name and its Parameter

Concurrent Program Attached to a Responsibility

Below query can be a handy one to find the list of responsibilities where the concurret program is attached.

select frg.request_group_name
      ,fcpt.user_concurrent_program_name CONC_PGM_NAME
  from fnd_concurrent_programs_tl fcpt
      ,fnd_request_group_units    frgu
      ,fnd_request_groups         frg
      ,fnd_responsibility         fr
      ,fnd_responsibility_tl      frt
 where 1 = 1
   and frgu.request_unit_id = fcpt.concurrent_program_id
   and frg.request_group_id = frgu.request_group_id
   and fr.request_group_id = frg.request_group_id
   and frt.responsibility_id = fr.responsibility_id
   and frt.language = USERENV('LANG')
   and fcpt.language = USERENV('LANG')
   and frt.responsibility_name = '&RESPONSIBILITY_NAME'
 order by fcpt.user_concurrent_program_name

Related Posts:
Find Concurrent Program and Request Set attached to a responsibility

Query to get Concurrent Program name and its Parameter