Search This Blog

Wednesday, January 30, 2008

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
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 =
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.

Saturday, January 26, 2008

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.

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;
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
dbms_output.put_line('Employee '||v_employee_name
||' does not exist');
dbms_output.put_line('Unexpected Error '||SQLERRM);
dbms_output.put_line('while selected person_id');
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'
,security_group => 'STANDARD'
,description => 'System Administrator'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
-- Created by Suresh Vaishya

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
,prl.line_num req_line_num
,prl.item_description req_item_description
,prl.unit_price req_unit_price
,prl.quantity req_quantity
,ph.segment1 po_number
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,
    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,
    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

Wednesday, January 9, 2008

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

Copyright (c) All rights reserved. Presented by Suresh Vaishya