Oracle Apps Inventory Queries
--Reservation Qty for SKU
select SUM(mtr.reservation_quantity) from
mtl_reservations mtr , oe_order_lines_all ool, oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id and mtr.inventory_item_id =1
and ooh.order_number = 316
--Reservation Qty for Order
select SUM(mtr.reservation_quantity) from
mtl_reservations mtr , oe_order_lines_all ool, oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id and ooh.order_number = 316
--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id, hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag, hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id, hps.location_id,hpsu.primary_per_type ,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county FROM oe_order_headers_all ooh, hz_cust_accounts_all hca, hz_parties hp,hz_party_sites hps, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, hz_locations hl where ooh.sold_to_org_id = hca.cust_account_id and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id and hl.location_id = hps.location_id
and ooh.order_number = 351 order by hps.party_site_id
Read more: http://prasanthapps.blogspot.com/2011/04/oracle-apps-inventory-queries.html#ixzz1dIPSLwwQ
SQL script to generate Item Cross References
SQL script to generate Item Cross References
From time to time we want to look up cross references in Oracle Manufacturing, but we have so many types of references
to so many Items, and the application can be so slow. This query lets users specify New (Oracle) Items, Old Items, creation or update date--or it defaults to 'All Cross References'. It spools output to a CSV file.
The user then opens the Excel spreadsheet manually. It has an Auto_Open macro that is just looking for the spool file (name
is hard-coded, so the spool file name in the query is vital). Excel opens the text spool file, formats it for viewing, and displays messages to the user. Then the auto-open file closes
automatically, too, leaving only 'the data' on screen in Excel. The information in Excel is much easier to read more quickly, sort as required, and print. (By itself, the query simply generates the CSV file, and the user can do what he wants with that afterward, of course. You don't HAVE to use the Excel sheet.)
References to the J:\ drive in the script and Excel file are to access a common drive that we share between European and
American users in this company. You'd probably want to change that, but you would have to do the same in the SQL query AND in the spreadsheet file. To open the spreadsheet for editing, hold down the shift key while the file is selected to be
opened. Sheet protection is 'on' with no password, and the macro sheet is 'hidden', also easy to find and open with
simple Excel commands.
SET heading OFF serveroutput ON pagesize 15000 buffer 150 SET linesize 150 echo OFF verify OFF serveroutput ON SELECT DISTINCT cross_reference_type
FROM inv.mtl_cross_references /
ACCEPT v_type PROMPT 'Search a C. R. Type from the list (or default "ALL"): ' ACCEPT v_seg1 PROMPT 'Search for Particular NEW Item (default "ALL"): '
ACCEPT v_item PROMPT 'Search for Particular OLD Item (default "ALL"): '
ACCEPT v_crea PROMPT 'Created since (default "ALL"; Or enter dd-MMM-yyyy): ' ACCEPT v_upda PROMPT 'Updated since (default "ALL"; Or enter dd-MMM-yyyy): ' SPOOL J:\Queries\Cross_Ref.LST;
--Capture output to specific file that Excel will look for later. --You may want to spool to C:\Path\Path\filename.
SELECT DISTINCT a.cross_reference_type ||','|| b.segment1 ||','||
a.cross_reference ||','|| a.description ||','||
TO_CHAR( a.organization_id) ||','|| TO_CHAR( a.creation_date) ||','||
DECODE( TRUNC( a.last_update_date - a.creation_date), 0, ' -',
TO_CHAR( a.last_update_date)) ||','|| c.name || '--' ||
RTRIM( TO_CHAR( sysdate, 'fmDD-Mon-YYYY "at" fmHH:MIam "GMT"')) ||',' --Concatenated comma-separated values for export to Excel CSV file.
--Be sure to keep the trailing comma for the final column. FROM inv.mtl_cross_references a,
inv.mtl_system_items b, v$database c
WHERE a.cross_reference_type LIKE UPPER( NVL( '%&v_type%', '%')) AND b.segment1 LIKE UPPER( NVL( '%&v_seg1%', '%'))
AND a.cross_reference LIKE UPPER( NVL( '%&v_item%', '%'))
AND a.creation_date >= UPPER( TO_DATE( NVL( '&v_crea', '01-JAN-1950'), 'dd-Mon-yyyy'))
AND a.last_update_date >= UPPER( TO_DATE( NVL( '&v_upda', '01-JAN-1950'), 'dd-Mon-yyyy'))
AND a.inventory_item_id = b.inventory_item_id --'Limited Inclusion' variables added 26-Feb-99. ORDER BY a.cross_reference_type ||','|| b.segment1 ||','|| a.cross_reference ||','|| a.description ||','|| TO_CHAR( a.organization_id) ||','|| TO_CHAR( a.creation_date) ||','||
DECODE( TRUNC( a.last_update_date - a.creation_date), 0, ' -',
TO_CHAR( a.last_update_date)) ||','|| c.name || '--' ||
/
SPOOL OFF SET linesize 60
SELECT 'Now use Excel to open the file c:\windows\temp\crossref.xls',
'(Really. Do it this very minute.)' FROM dual
/
SET heading ON pagesize 23 linesize 150 ' ' Open_New_Cross_Reference_List Macro
' Open whatever is the newest Cross Reference export from ORACLE (on J:\Queries). ' by Chris Nelson, Jan/Feb 1999. Eaton MAD / Oracle Implementation Team. '
'
Sub Auto_Open()
' This macro runs upon the opening of the spreadsheet, and opens the text file generated by ' the SQL*Plus Query "Export_Cross_Ref.SQL". (J:\Queries\Cross_Ref.LST)
Workbooks.OpenText Filename:="J:\Queries\Cross_Ref.LST", _ Origin:=xlWindows, StartRow:=2, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab _ :=True, Semicolon:=False, Comma:=True, Space:=False, Other _
:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array( _ 4, 2), Array(5, 2), Array(6, 3), Array(7, 3), Array(8, 2))
Range("A1").EntireRow.Insert ' Set column headings Range("A1").Formula = "C.R. Type"
Range("B1").Formula = "ORACLE Item" Range("C1").Formula = "Value"
Range("D1").Formula = "Description" Range("E1").Formula = "Org"
Range("F1").Formula = "Created" Range("G1").Formula = "Updated" Range("H1").Formula = "Query Date"
Range("I:I").EntireColumn.Delete ' This column has extra 'spaces' from the import. Range("A1:H1").Select ' Format the column headers
Selection.Font.Bold = True
Selection.Columns.AutoFit ' Adjust column widths ActiveCell.SpecialCells(xlLastCell).Select
Selection.EntireRow.Delete ' Remove "rows selected" Range("A2").Select
ActiveWindow.FreezePanes = True ' Set Windows 'panes'. ' Messages to user(s).
MsgBox "This is the most recent Cross Reference query from ORACLE." & Chr(10) & _ "If you want to save this file, please 'SAVE AS' another name." & Chr(10) & Chr(10) & _ "PLEASE NOTE that this MAY NOT BE an all-inclusive Cross-Reference Listing." & Chr(10) & _
"More comments in J:\SQL\Export_Cross_Ref.SQL."
MsgBox "Query Date and Time(when this list was exported) is shown" & Chr(10) & _ "in the rightmost column of the spreadsheet." & Chr(10) & Chr(10) & _
"Be aware that this is NOT a 'dynamic' query, and that data may have" & Chr(10) & _ "changed since then. The 'latest and greatest' data is in ORACLE."
Windows("Open_Cross_Refs.XLS").Close End Sub
SQL query to display Items in a multi-org setup
SQL query to display Items in a multi-org setup which have
been created or modified within the past 30 days, or which
will be made effective at some future date
This query shows Items in a multi-org setup which have been created or modified within the past 30 days, or which will be
made effective at some future date. (One would of course change the ACCEPT prompt to reflect one's own Organiziation_ID numbers and Organization Names, as noted in the code comments below.)
One of the most interesting things about writing this query was using the techniques for
NEW_VALUE to make a more interactive and informative TTITLE. (Since we have several Test instances along with our Production instance, on top of the multi-org setup, it's important to know what you are looking at !)
SET heading OFF verify OFF echo OFF feedback OFF SET pagesize 66
COLUMN "Item" FORMAT A15
COLUMN "Description" FORMAT A25 TRUNCATE COLUMN "Org" FORMAT 999
COLUMN "Rev" FORMAT 999 COLUMN "Effective" FORMAT A9 COLUMN "New ?" FORMAT A5
BREAK ON "Item" ON "Description" ON "Org" ON "Eff. Dt."
ACCEPT v_org PROMPT "(Default 'All Orgs' or '590' for MST, '591' for NL, '586' for US) Org? :"
--Modify to suit your Organization Name and Number setup. --(The NVL is handled below as the default.)
COLUMN q_org NEW_VALUE this_org NOPRINT COLUMN q_day NEW_VALUE this_day NOPRINT COLUMN q_now NEW_VALUE right_now NOPRINT SELECT TO_CHAR( sysdate, 'dd-Mon-yyyy') q_day , TO_CHAR( sysdate, 'HH24:MI "(System Time)"') q_now , 'Items and Revisions Created OR Made Effective in ' || NVL( '&&v_org', 'All Orgs') || ' in ' || db.name q_org FROM dual
, v$database db /
TTITLE LEFT this_day - CENTER right_now -
RIGHT 'Page ' FORMAT 999 sql.pno SKIP 2 - CENTER this_org SKIP -
CENTER "Within the Past 30 Days OR To Be Made Effective in the Future" SKIP 2 -- Techniques from Oracle: A Beginner's Guide, Pages 294 - 295:
-- using NEW_VALUE to set 'new' columns, for use in TTITLE and BTITLE, -- and using sql.pno to show page numbers when the default's not set.
BTITLE SKIP LEFT "Effective '-' means 'Same day as 'Created'.'" - RIGHT "ITEM_REVS.SQL"
SET heading ON feedback ON SELECT b.segment1 "Item" , b.description "Description" , b.organization_id "Org" , a.revision "Rev"
, a.creation_date "Created"
, DECODE( TRUNC( a.effectivity_date - a.creation_date) , 0, ' -'
, 1, ' Next day'
, TO_CHAR( a.effectivity_date)) "Effective" , DECODE( a.revision
, 0, ' NEW' , NULL) "New ?"
FROM inv.mtl_item_revisions a , inv.mtl_system_items b
WHERE a.inventory_item_id = b.inventory_item_id AND a.organization_id = b.organization_id
AND (a.effectivity_date > sysdate - 30 OR a.creation_date > sysdate - 30)
AND TO_CHAR( a.organization_id) LIKE NVL( '&v_org', '%') ORDER BY "Item", "Org"
/
CLEAR COLUMN TTITLE OFF BTITLE OFF
--Review Items/Revisions created within the past 30 days, --OR effective within the past 30 days,
--OR to be made effective in the future.
Script to load or convert Notes/Attachments associtaed with items
Script to load or convert Notes/Attachments associtaed with
items
-- Script to load or convert Notes/Attachments associated with items. DECLARE l_doc_category_id NUMBER; l_document_id NUMBER; l_attached_document_id NUMBER; ll_media_id NUMBER; l_fnd_user_id NUMBER; l_short_datatype_id NUMBER; BEGIN -- Select User_id SELECT user_id INTO l_fnd_user_id FROM apps.fnd_user
-- Get Data type id for Short Text types of attachments SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes WHERE name ='SHORT_TEXT';
-- Select Category id for "Vendor/To Supplier" Attachments SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories WHERE name = 'Vendor';
-- Select nexvalues of document id, attached document id and -- l_media_id SELECT apps.fnd_documents_s.NEXTVAL, apps.fnd_attached_documents_s.NEXTVAL, apps.fnd_documents_short_text_s.NEXTVAL INTO l_document_id, l_attached_document_id, l_media_id FROM DUAL;
INSERT INTO apps.fnd_documents (document_id, creation_date, created_by, last_update_date, last_updated_by, datatype_id, category_id, security_type, security_id, publish_flag, usage_type ) VALUES (l_document_id, SYSDATE, l_fnd_user_id, SYSDATE, l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT' l_doc_category_id, -- Category_id
1, -- 'Organization' Level Security
352, -- Organization id for Inventory Item Master Org 'Y', -- Publish_flag
'O' -- Usage_type of 'One Time' );
INSERT INTO apps.fnd_documents_tl (document_id, creation_date, created_by, last_update_date, last_updated_by, language, description, media_id, translated ) VALUES (l_document_id, SYSDATE, l_fnd_user_id, SYSDATE, l_fnd_user_id, 'AMERICAN', -- language
'EXTENDED DESCRIPTION', -- description l_media_id, -- media_id
'Y' -- translated );
INSERT INTO apps.fnd_attached_documents (attached_document_id, document_id, creation_date, created_by, last_update_date, last_updated_by, seq_num, entity_name, pk1_value, pk2_value, automatically_added_flag ) VALUES (l_attached_document_id, l_document_id, SYSDATE, lcl_fnd_user_id, SYSDATE, lcl_fnd_user_id,
20, -- Sequence Number of attachment.
'MTS_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment 352, -- Organization id for Inventory Item Master Org
567, -- Inventory Item Id
'N' -- Automatically_added_flag );
INSERT INTO apps.fnd_documents_short_text (media_id,
short_text )
VALUES (lcl_media_id,
'Write your Short Text Here' -- Notes/Attachments text );
COMMIT; END; /
Coping Items using Item Interface
Coping Items using Item Interface
If an item is already in one inventory organization, then you can copy same item in other oragnization using Item Import
Program. You have to populate the copy_organization_id and copy_item_id columns to copy an item. You have to run the item import interface after loading these values into interface table. You can also change the values of an item attribute by
populating a value for that attribute in interface table. If a value is assigned to an attribute, then interface will use the
new value instead of the value in MTL_SYSTEM_ITEMS for that item in old organization.
Prevent transactions in Master Inventory Organization
There are two ways to control access transaction in Master Oranization:
1. Do not open any Inventory Accounting periods in the Master Organization. This will prevent any transactions from happening in Master organization.
2. The other way to restrict Inventory organizations by responsibility.
Navigate to Organization Access menu option under Setup->Organizations->Organization Access to restrict a responsibility to a organization from.
