Configuring Wireless

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Friday, March 8, 2013

Lot Genealogy (Part 1): Building a Cache From R12 Data

Posted on 2:59 AM by Unknown
UPDATE: 15-APR-2013 fixed a bug with WIP Issue transactions, added in some more logging (see source code).

If you have had any opportunities to work using Oracle Process Manufacturing one of features that Oracle have removed in R12 is reporting on Lot Genealogy, specifically the ability to look at the entire genealogy for a lot.

I've tackled this issue previously using Noetix (see here) and this is the first part of a series in which I'll attempt to provide a means for generating a cache for the lot genealogy information that you can report on (rather than having to build the genealogy at the time you run the report) without using Noetix.

So here's the plan;

Lot Genealogy Cache: Process Overview
This plan is based on the established Lot and Batch cycle;

PO > Lot > Batch Cycle
It's the iterative nature of lots > batches and batches > lots that causes a lot of problems when you're writing reports. The key things the process above includes that the simplified diagram doesn't is the ability to reverse a the creation of batches from a lot and the consumption of batches into lots.

Looking at the process overview the first part we need to worry about is the second box down (Get Next Transaction Data). What we need to do in this process is get a sequential list of all transactions since the last time we ran the process. The SQL below will do this;

SELECT MMT.TRANSACTION_ID,
       GBH.Batch_No,
       MTLN.LOT_NUMBER,
       MTLN.TRANSACTION_QUANTITY,
       MTT.TRANSACTION_TYPE_NAME,
       MSIB.SEGMENT1             Item_Number,
       MSIT.DESCRIPTION          Item_Description,
       MSIB.ITEM_TYPE            Item_Type
  FROM INV.MTL_TRANSACTION_LOT_NUMBERS MTLN,
       INV.MTL_MATERIAL_TRANSACTIONS   MMT,
       INV.MTL_SYSTEM_ITEMS_B          MSIB,
       INV.MTL_SYSTEM_ITEMS_TL         MSIT,
       INV.MTL_LOT_NUMBERS             MLN, -- Used to identify lots that have been disabled
       GME.GME_BATCH_HEADER            GBH,
       INV.MTL_TRANSACTION_TYPES       MTT
 WHERE 1 = 1
   AND MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID
   AND MTLN.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
   AND MTLN.ORGANIZATION_ID = MMT.ORGANIZATION_ID
   AND MTLN.TRANSACTION_SOURCE_TYPE_ID = 5 /* Job Or Schedule */
   AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MMT.Organization_Id = MSIB.Organization_Id
   AND MSIT.INVENTORY_ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID
   AND MSIT.ORGANIZATION_ID(+) = MSIB.ORGANIZATION_ID
   AND MMT.Transaction_Source_ID = GBH.Batch_Id(+)
   AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID(+)
   AND MTLN.Lot_Number = MLN.Lot_Number
   AND MTLN.Inventory_Item_Id = MLN.Inventory_Item_Id
   AND MTLN.Organization_Id = MLN.Organization_Id
   AND MLN.Disable_Flag IS NULL;


Rather than embedding this into our process I think a good idea would be to create an external view (which I'm going to call LOT_GENEALOGY_GETTRANSACTIONS). The source code file for this is available here (via Google Drive).

The next thing we need to consider is how we're going to store the information in the cache itself. The following are source files for each required object;
  • LOT_GENEALOGY_SEQ (Sequence) 
  • LOT_GENEALOGY_BATCH_PRODUCT (Table)
  • LOT_GENEALOGY_LOGGING (Table)
  • LOT_GENEALOGY_SETTINGS (Table)
  • LOT_GENEALOGY_WHEREUSED (Table)
Assuming you've built the view and the tables above the main package can then be built. The source is here.

Once that's been built you need to configure the settings table;

insert into LOT_GENEALOGY_SETTINGS (setting_name, value_number, value_text, value_date)
values ('MAX_TRANS_NO', -1, null, null);
insert into LOT_GENEALOGY_SETTINGS (setting_name, value_number, value_text, value_date)
values ('LOGGING', null, 'YES', null);
commit;

And then you can run the stored procedure;

begin
  -- Call the procedure
  lot_genealogy.refreshwholecache;
end;


That will populate the cache for the first time, you then need to schedule;

begin
  -- Call the procedure
  lot_genealogy.doupdate;
end;


Which will incrementally update the cache based on new transactions. We schedule this to run every 30 minutes but it will depend on your volume of transactions as to how often you want it to run!


Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in lot genealogy, Oracle Process Manufacturing | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Oracle PL/SQL: Working With Oracle Jobs (Showing/ Deleting/ Adding)
    Showing All Oracle Jobs To see a list of the currently configured Oracle Jobs use the SQL; SELECT job,        schema_user,        last_date,...
  • Oracle PL/SQL: Copying Column Comments From One View To Another
    This blog post gives a piece of simple SQL that will allow you to automatically copy the comments from one database view to another. In the ...
  • PL/SQL: Using R12 Item Open Interface Tables
    I'm sure we won't be the only company to need to do a batch update of all the existing Items (in INV.MTL_SYSTEM_ITEMS_B) so I though...
  • SSRS: Deployment Problems With Large Data Models
    This blog post describes how to solve the "Maximum request length exceeded" error when deploying large data models; the "maxi...
  • Oracle PL/SQL: Using DBMS_LDAP To Query Active Directory
    It's always useful to be able to retrieve details from Active Directory when working within an Oracle Database (I'll do a separate p...
  • SSRS: Creating a "Shared Reports" folder in Reporting Services
    This blog post covers step-by step instructions on how to create a folder that can be shared between multiple users without being publicly f...
  • Oracle EBS: Creating New Menu Items in Oracle e-Business Suite
    NOTE: Don't do this on a production environment. Did that need saying? Apparently one person who submitted a comment seemed to think so ...
  • Lot Genealogy, Part 3: Debugging Lots and Batches
    As you might have noticed I've just updated the LOT_GENEALOGY source code for this project to a new version following the discovery of a...
  • SSRS: Adding Calculated Fields To Data Sets
    This blog post covers an example of how to add a simple calculated field to a Dataset in SQL Server Reporting Services using Report Builder ...
  • Oracle PL/SQL: Making All Tables in a Schema Read-Only
    This article covers preventing casual users from writing data to some tables within an Oracle Schema. This routine will prevent the user wri...

Categories

  • .net framework
  • #Error
  • 1080p
  • 1248ub
  • 2007
  • 2008R2
  • 32-bit
  • 4.1.1
  • 64-bit
  • 720p
  • accellion
  • active directory
  • ad
  • airplay
  • All_Col_Comments
  • All_MViews
  • All_Objects
  • All_Source
  • All_Tab_Columns
  • All_Tables
  • All_Views
  • ALR_Action_Outputs_Pkg
  • ALR_Action_Sets
  • ALR_Actions_Pkg
  • ALR_Alert_Installations_Pkg
  • ALR_Alert_Outputs_Pkg
  • ALR_Alerts_Pkg
  • ALR_DBTrigger
  • amazon wishlist
  • aod
  • AP
  • AP_Credit_Card_Trxns_All
  • AP_Invoices_All
  • AP_Payables
  • AP_Vendor_Sites_Pkg
  • AP_Vendors_Pkg
  • app-v
  • apple
  • apple mac
  • apple maps
  • apple tv
  • application virtualisation
  • AR_Receivables
  • arbury carnival
  • arbury community centre
  • arbury court
  • arbury court library
  • army of darkness
  • army of darkness defense
  • asp.net
  • audiobooks
  • bar hill cambridgeshire uk
  • bar hill library
  • bbc micro
  • bids
  • biztalk 2009
  • british telecom
  • business intelligence development studio
  • business objects
  • c sharp
  • cambridge central library
  • cambridge regional college
  • cambridge station
  • cambridgeshire county council
  • cambridgeshire library service
  • Cast()
  • ccc
  • CDate()
  • citi 1
  • city councillor
  • classic pc
  • cmdb
  • commodore 64
  • Concurren Requests
  • configuration items
  • configuration management database
  • conservative
  • Count()
  • county councillor
  • crc
  • D600
  • data model
  • data source
  • database link
  • dataset
  • DateAdd()
  • DateSerial()
  • dba_jobs
  • DBA_Objects
  • DBA_Tab_Columns
  • dbms_job
  • DBMS_LDAP
  • dbms_refresh
  • dbo.AllUserData
  • dbo.Catalog
  • dbo.ExecutionLogStorage
  • Dell
  • district councillor
  • doodle.com
  • dos box
  • driver
  • e-Business Suite
  • easypush
  • EBS
  • email
  • epetitions
  • excel
  • ExecutionLog2
  • fa
  • FA_Fixed_Assets
  • fixed assets
  • FND_Form_Functions
  • FND_Form_Functions_Pkg
  • FND_Global
  • FND_Menu_Entries
  • FND_Menu_Entries_Pkg
  • FND_Menus
  • FND_Profile_Option_Values
  • FND_Profile_Options
  • FND_Program
  • FND_Request
  • FND_Users
  • FOI
  • Format()
  • freedom of information
  • Functional Administrator
  • GL_Daily_Rates_V
  • GL_Item_Cst
  • GL_Je_Lines
  • GL_Ledger
  • Gmail
  • GMD_Product_Development
  • GME_Process_Execution
  • GMF_OPM_Financials
  • GMF_Period_Balances
  • GMF_SLA_Cost_Subledger
  • gmfg0_item_costs
  • GMI_Onhand_Inv_By_Lot
  • GMI_Process_Planning
  • google
  • google dns
  • google knol
  • google maps
  • green
  • gremlin
  • group policy
  • guided bus
  • high definition
  • home hub 3.0
  • home sharing
  • hr.net
  • i-Expenses
  • ibm
  • iccid
  • iExpenses
  • IIF
  • IIF()
  • iis
  • iis 6
  • imei
  • information
  • installation
  • InStr
  • InStrRev
  • Internet Expenses
  • INV_Forecasts
  • INV_Inventory
  • INV_Item_Onhand_By_lot
  • inv_lot_transactions
  • INV_Onhand_Quantities
  • INV_Period_Close_Details
  • INV_Quantity_Tree_Pub
  • inv_reservations
  • iOS
  • iOS 6
  • ip address
  • iPad
  • ipconfig
  • iPhone
  • iPod
  • iresign
  • itunes
  • java
  • Join()
  • june
  • key flex field
  • Key Flex Fields
  • kff
  • labour
  • Latitude
  • Left()
  • level 50
  • Liberal Democrat
  • libraries
  • Lookup()
  • lot genealogy
  • materialized views
  • maximo
  • microsoft
  • microsoft app-v
  • microsoft exchange
  • microsoft paint
  • migration
  • MobileIron
  • Month()
  • MRP_Forecast_Dates
  • MRP_Forecast_Designators
  • msi
  • Mtl_Material_Status_History
  • MTL_System_Items_B
  • mtl_system_items_interface
  • mustek
  • N_Buffer
  • N_F_KFF_Flex_Sources
  • N_GSeg_Pkg
  • N_Gseg_Utility_Pkg
  • N_KFF_Ctlg_Grp
  • N_KFF_GL_Acct
  • N_KFF_Item_Loc
  • N_KFF_Mtl_Cat
  • N_KFF_Sys_Item
  • N_KFF_Sys_Item_Pkg
  • N_Role_View_Templates
  • N_View_Column_Property_Templates
  • N_View_Column_Templates
  • N_View_Columns
  • N_View_Query_Templates
  • N_View_Table_Templates
  • N_View_Templates
  • N_View_Where_Templates
  • N_Views
  • native-mode
  • ncm
  • NLS_Language
  • NLS_Territory
  • noetix
  • noetix customization maintenance
  • noetix views
  • Now()
  • OE_Order_Entry
  • OIE
  • open interface
  • open source software
  • opensource-it.com
  • opm
  • ORA-01795
  • Oracle
  • Oracle Alerts
  • oracle client
  • Oracle General Ledger
  • Oracle Internet Expenses
  • Oracle Payables
  • Oracle Process Manufacturing
  • oracle sql developer
  • orchard park
  • os x
  • os x lion
  • Outlook
  • parish councillor
  • Payables
  • pc line
  • pcl-3000
  • pl/sql
  • PO_Distributions_All
  • PO_Purchasing
  • PO_Vendor_Sites
  • PO_Vendors
  • port forwarding
  • quick guide
  • Recyclebin
  • Release 11
  • Release 12
  • remote server administration tools
  • Replace()
  • report builder 3
  • router
  • run as a different user
  • sap
  • scom
  • services
  • sharepoint
  • sharepoint 2007
  • sharepoint 2010
  • sharepoint content types
  • sharepoint document library
  • sharepoint integrated-mode
  • sharepoint native-mode
  • sla
  • smtp
  • sql server
  • sql server 2012
  • sql server analysis services
  • sql server integration services
  • sql server reporting services
  • ssas
  • ssis
  • ssrs
  • subledger accounting
  • subsidence
  • super hub
  • sysdate
  • system centre operations manager
  • telnet
  • test
  • textfile-search-and-replace
  • tnsnames.ora
  • town councillor
  • udid
  • ukip
  • umbraco
  • user accounts
  • User_Triggers
  • virgin media
  • vizual
  • vmware fusion
  • windows
  • windows 2003
  • windows 2008r2
  • windows 7
  • windows 8
  • windows 8 consumer preview
  • windows 8 server
  • windows update
  • windows vista
  • Wireless Drivers
  • wireless networking
  • wItem Installer
  • wnoetxu2.sql
  • wnoetxu5.sql
  • wnoetxu6.sql
  • work order
  • workflow builder
  • world of spectrum
  • xcode
  • XLA_Distribution_Links
  • xxk_mtl_cat
  • XXNAO
  • Year()
  • zool
  • zx spectrum

Blog Archive

  • ▼  2013 (43)
    • ►  August (2)
    • ►  June (1)
    • ►  May (2)
    • ►  April (8)
    • ▼  March (3)
      • Oracle SQL Developer: Using 'Windows Authenticatio...
      • Lot Genealogy (Part 1): Building a Cache From R12 ...
      • Fixing Windows Update Error Code 80246008
    • ►  February (14)
    • ►  January (13)
  • ►  2012 (63)
    • ►  December (2)
    • ►  October (1)
    • ►  September (4)
    • ►  August (4)
    • ►  July (5)
    • ►  June (6)
    • ►  May (3)
    • ►  April (4)
    • ►  March (10)
    • ►  February (11)
    • ►  January (13)
  • ►  2011 (65)
    • ►  December (8)
    • ►  November (8)
    • ►  October (7)
    • ►  September (9)
    • ►  August (9)
    • ►  July (9)
    • ►  June (6)
    • ►  May (2)
    • ►  March (1)
    • ►  February (5)
    • ►  January (1)
  • ►  2010 (9)
    • ►  December (1)
    • ►  November (3)
    • ►  September (1)
    • ►  July (1)
    • ►  June (1)
    • ►  February (2)
  • ►  2009 (9)
    • ►  December (1)
    • ►  November (1)
    • ►  August (1)
    • ►  July (1)
    • ►  May (3)
    • ►  March (1)
    • ►  February (1)
  • ►  2008 (11)
    • ►  November (2)
    • ►  October (1)
    • ►  July (1)
    • ►  May (1)
    • ►  April (2)
    • ►  February (1)
    • ►  January (3)
  • ►  2007 (4)
    • ►  December (4)
  • ►  2004 (1)
    • ►  December (1)
Powered by Blogger.

About Me

Unknown
View my complete profile