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(+)