Setting the Applications Context FND_GLOBAL.APPS_INITIALIZE in oracle applications:
Use the API FND_GLOBAL.APPS_INITIALIZE to set the applications context in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections.
procedure APPS_INITIALIZE(user_id IN number,resp_id IN number,resp_appl_id IN number security_group_id IN number);
USER_ID - The User ID number.
RESP_ID - The ID number of the responsibility.
RESP_APPL_ID - The ID number of the application TO which the responsibility belongs.
SECURITY_GROUP_ID - The ID number of the security GROUP. This argument IS automatically defaulted BY the API. The caller should NOT pass a value FOR it.
Usage: fnd_global.APPS_INITIALIZE (1010,20417,201):
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session). The typical use for this routine would be as part of the logic for launching a separate non–Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection. You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.
begin
fnd_global.APPS_INITIALIZE(200131258,20004,51710);
end;
SELECT fnd_profile.value (‘RESP_ID’) FROM dual
SELECT fnd_profile.value (‘USER_ID’) FROM dual
SELECT fnd_profile.value (‘APPLICATION_ID’) FROM dual
SELECT TO_NUMBER (FND_PROFILE.VALUE( ‘LOGIN_ID ‘)) FROM dual
SELECT FND_PROFILE.VALUE(‘ORG_ID’) FROM dual
SELECT FND_PROFILE.VALUE(‘SO_ORGANIZATION_ID’) FROM dual
SELECT FND_PROFILE.VALUE(‘USERNAME’) FROM dual
SELECT FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’) FROM dual
Wednesday 29 July 2009
Inventory KEY Tables
Oracle Apps Inventory KEY Tables:
MTL_SYSTEM_ITEMS_B:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item–related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.
You can set up the item with multiple segments, since it is implemented as a flexfield. Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications.
Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table. PRIMARY_UOM_CODE is the 3–character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25–character unit that is used throughout Oracle Purchasing.
Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only. "
MTL_DEMAND:
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.
Summary Demand is direct demand for an item within an organization a particular date that originated from a particular source. For hard reservations there are several columns which further define what the reservation is for, and where it is being placed. Currently, four sources of demand are supported, Sales Order, Account, Account Alias, and User Defined transaction sources.
Five different types of demand, denoted by DEMAND_TYPE column, are used. These five types are Model, Option Class, Option Item, Configuration Item and Derived. Derived demand rows are inserted by BOM Demand exploder when demanded item has ATPable components.
Each Summary Demand row may be associated with one or more Reservation rows. Reservation may be placed against a particular inventory control (that is, specific sub inventory, locator, revision and lot) against any sources (that is, Account Number, Account Alias, Sales Order or even User–Defined sources).
Each Summary Demand row may be associated with one or more detailed rows. The detailed rows consist of reservations and open demand. A reservation row represents a firm promise of a supply source. Currently, two types of reservation are supported, reservations to on–hand, and reservations to WIP jobs.
Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un–reserved portion of the the Summary Demand.
MTL_CATEGORIES_B :
MTL_CATEGORIES_B is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. The item category is a key flexfield with a flex code of MCAT. The flexfield structure identifier is also stored in this table.
MTL_CATEGORY_SETS_B:
MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.
Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with thecategory set. Only categories with the same flexfield structure may be grouped into a category set.
CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned. VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.
MTL_ITEM_CATEGORIES:
MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.
MTL_ITEM_SUB_INVENTORIES :
MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These subinventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.
RCV_TRANSACTIONS:
It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.
RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES:
For example:1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5
a. The following records are created:
One rcv_shipment_header record
Five rcv_shipment_lines records
b. rcv_shipment_lines.shipment_header_id will be the same for all five records, which will also be the same value as rcv_shipment_header.shipment_header_id
2. Receive one Shipment Line:
rcv_shipment_headers record will be updated with the Receipt Number
3. Subsequent Receiving transactions will reference the same rcv_shipment_header record (therefore, there can only be one Receipt Number).
MTL_SYSTEM_ITEMS_B:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item–related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.
You can set up the item with multiple segments, since it is implemented as a flexfield. Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications.
Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table. PRIMARY_UOM_CODE is the 3–character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25–character unit that is used throughout Oracle Purchasing.
Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only. "
MTL_DEMAND:
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows, Open Demand Rows, and Reservation Rows.
Summary Demand is direct demand for an item within an organization a particular date that originated from a particular source. For hard reservations there are several columns which further define what the reservation is for, and where it is being placed. Currently, four sources of demand are supported, Sales Order, Account, Account Alias, and User Defined transaction sources.
Five different types of demand, denoted by DEMAND_TYPE column, are used. These five types are Model, Option Class, Option Item, Configuration Item and Derived. Derived demand rows are inserted by BOM Demand exploder when demanded item has ATPable components.
Each Summary Demand row may be associated with one or more Reservation rows. Reservation may be placed against a particular inventory control (that is, specific sub inventory, locator, revision and lot) against any sources (that is, Account Number, Account Alias, Sales Order or even User–Defined sources).
Each Summary Demand row may be associated with one or more detailed rows. The detailed rows consist of reservations and open demand. A reservation row represents a firm promise of a supply source. Currently, two types of reservation are supported, reservations to on–hand, and reservations to WIP jobs.
Each summary demand row may be associated with one and only one open demand row. Open Demand rows represent the un–reserved portion of the the Summary Demand.
MTL_CATEGORIES_B :
MTL_CATEGORIES_B is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. The item category is a key flexfield with a flex code of MCAT. The flexfield structure identifier is also stored in this table.
MTL_CATEGORY_SETS_B:
MTL_CATEGORY_SETS_B contains the entity definition for category sets. A category set is a categorization scheme for a group of items.
Items may be assigned to different categories in different category sets to represent the different groupings of items used for different purposes.An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with thecategory set. Only categories with the same flexfield structure may be grouped into a category set.
CONTROL_LEVEL defines whether the category set is controlled at the item or the item/organization level. When an item is assigned to an item level category set within the item master organization, the category set assignment is propagated to all other organizations to which the item is assigned. VALIDATE_FLAG defines whether a list of valid categories is used to validate category usage within the set. Validated category sets will not allow item assignment to the category set in categories that are not in a predefined list of valid categories. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL.
MTL_ITEM_CATEGORIES:
MTL_ITEM_CATEGORIES stores the item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items may be assigned to multiple categories and category sets but may be assigned to only one category in a given category set. This table may be populated through the Master Items and Organization Items windows. It can also be populated by performing item assignments when a category set is defined. It is also populated when an item is transferred from engineering to manufacturing.
MTL_ITEM_SUB_INVENTORIES :
MTL_ITEM_SUB_INVENTORIES maintains a listing of subinventories assigned to an inventory or engineering item. These subinventories make up the list of valid subinventories when transacting this specific item and the user has specified (in the master window) that the item must use subinventories restricted to a pre–defined list.
RCV_TRANSACTIONS:
It stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table. Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction quantity does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing.
RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES:
When a Ship Confirm is processed, one record is inserted in rcv_shipment_headers and one record is inserted in rcv_shipment_lines for each of the Sales Order Lines. The rcv_shipment_lines are linked to the one rcv_shipment_header record by shipment_header_id.
When a Shipment Line is received, the Receipt Number is populated in the rcv_shipment_headers record that was created for that Shipment. Since only one rcv_shipment_headers record is created for each Ship Confirm process and the Receipt Number is also on rcv_shipment_headers record, there can only be one Receipt Number for a specific Shipment.For example:1. Ship Confirm Sales Order Lines 1, 2, 3, 4, 5
a. The following records are created:
One rcv_shipment_header record
Five rcv_shipment_lines records
b. rcv_shipment_lines.shipment_header_id will be the same for all five records, which will also be the same value as rcv_shipment_header.shipment_header_id
2. Receive one Shipment Line:
rcv_shipment_headers record will be updated with the Receipt Number
3. Subsequent Receiving transactions will reference the same rcv_shipment_header record (therefore, there can only be one Receipt Number).
Creating a Custom Application in R12
Step By Step Guide to Creating a Custom Application in R12 :
PURPOSE:
This note describes the basic steps needed to setup a Custom Top within Oracle Applications R12.
Creating a Custom Top in Applications R12:
Custom Tops are required if you are creating new forms, reports, workflows, OAF pages etc. This allows you to segregate your custom written files from the standard seeded functionality that Oracle Applications provide. Customizations can therefore be preserved when applying patches or upgrades to your environment.
Steps to be followed:
cd $APPL_TOP
mkdir appl/xx_hr
mkdir appl/xx_ums
3) Add the custom module into the environment
Apply ADX.E.1 and add the entry to topfile.txt as a standard product top entry (follow the existing model in the file) Customised environment variables can be added to AutoConfig by using the filename specificed by s_custom_file, which is then called from the APPSORA.env file. If using Forms Listener Servlet, you may also need to add $CUSTOM_TOP to formsservlet.ini in $APACHE_TOP/Jserv/etc
4) Create new tablespace for database objects
create tablespace xx_ums datafile '/emea/oracle/visuk09/visuk09data/xx_ums .dbf' size 10M default storage(initial 10k next 10k)
5) Create schema
create user xx_ums identified by xx_ums
default tablespace xx_ums
temporary tablespace temp
quota unlimited on xx_ums
quota unlimited on temp;
grant connect, resource to xx_ums;
6) Register your Oracle Schema.
Login to Applications with System Administrator responsibility Navigate to Application-->Register
Application = xx_ums Custom
Short Name = xx_ums
Basepath = xx_ums_TOP
Description = xx_ums Custom Application
7) Register Oracle User
Navigate to Security-->Oracle-->Register
Database User Name = xx_ums
Password = xx_ums
Privilege = Enabled
Install Group = 0
Description = xx_ums Custom Application User
8) Add Application to a Data Group
Navigate to Security-->Oracle-->DataGroup
Data Group = xx_ums Group
Description = xx_ums Custom Data Group
Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
Application = xx_ums Custom
Oracle ID = APPS
Description = xx_ums Custom Application
9) Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)Navigate to Security-->responsibility-->Request
Group= xx_ums Request Group
Application = xx_ums Custom
Code= xx_ums
Description = xx_ums Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
10) Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage) We will create two menus, one for Core Applications and one for Self Service. Navigate to Application-->Menu Menu= xx_ums_CUSTOM_MENU
User Menu Name = xx_ums Custom Application
Menu Type =
Description= xx_ums Custom Application Menu
Seq= 100
Prompt= View Requests
Submenu=
Function = View All Concurrent Requests
Description = View Requests
Seq= 110
Prompt= Run Requests
Submenu=
Function= Requests: Submit
Description = Submit Requests
Menu= xx_ums_CUSTOM_MENU_SSWA
User Menu Name = xx_ums Custom Application SSWA
Menu Type=
Description = xx_ums Custom Application Menu for SSWA
11) Create new responsibility.
One for Core Applications and One for Self Service (SSWA) Navigate to Security-->Responsibility-->Define
Responsibility Name= xx_ums Custom
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOM
Description= xx_ums Custom Responsibility
Available From= Oracle Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application
Request Group Name= xx_ums Request Group
Responsibility Name= xx_ums Custom SSWA
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOMSSWA
Description = xx_ums Custom Responsibility SSWA
Available From= Oracle Self Service Web Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application SSWA
Request Group Name= xx_ums Request Group
12) Add responsibility to user
Navigate to Security-->User-->DefineAdd xx_ums Custom responsibility to users as required.
13) Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc Create the source code files in the xx_ums _TOP directory appropriate for the type of object. For example forms would be located in
$xx_ums_TOP/forms/US or package source code in
$xx_ums_TOP/admin/sql for example. Database Objects, such as tables, indexes and sequences should be created in the xx_ums schema, then you need to
For example: logged in as APPS user
Create synonym myTable for xx_ums.myTable; Other database objects, such as views and packages should be created directly in the APPS schema.
PURPOSE:
This note describes the basic steps needed to setup a Custom Top within Oracle Applications R12.
Creating a Custom Top in Applications R12:
Custom Tops are required if you are creating new forms, reports, workflows, OAF pages etc. This allows you to segregate your custom written files from the standard seeded functionality that Oracle Applications provide. Customizations can therefore be preserved when applying patches or upgrades to your environment.
Steps to be followed:
1) Make the directory structure for Custom Pages Top.
cd $JAVA_TOP
cd classes
mkdir classes/xx_hr
mkdir classes/xx_ums
mkdir classes/xx_ums/oracle/apps/
mkdir classes/ xx_ums/oracle/apps/
2) Make the directory structure for Custom Forms and Reports Custom top. cd $APPL_TOP
mkdir appl/xx_hr
mkdir appl/xx_ums
3) Add the custom module into the environment
Apply ADX.E.1 and add the entry to topfile.txt as a standard product top entry (follow the existing model in the file) Customised environment variables can be added to AutoConfig by using the filename specificed by s_custom_file, which is then called from the APPSORA.env file. If using Forms Listener Servlet, you may also need to add $CUSTOM_TOP to formsservlet.ini in $APACHE_TOP/Jserv/etc
4) Create new tablespace for database objects
create tablespace xx_ums datafile '/emea/oracle/visuk09/visuk09data/xx_ums .dbf' size 10M default storage(initial 10k next 10k)
5) Create schema
create user xx_ums identified by xx_ums
default tablespace xx_ums
temporary tablespace temp
quota unlimited on xx_ums
quota unlimited on temp;
grant connect, resource to xx_ums;
6) Register your Oracle Schema.
Login to Applications with System Administrator responsibility Navigate to Application-->Register
Application = xx_ums Custom
Short Name = xx_ums
Basepath = xx_ums_TOP
Description = xx_ums Custom Application
7) Register Oracle User
Navigate to Security-->Oracle-->Register
Database User Name = xx_ums
Password = xx_ums
Privilege = Enabled
Install Group = 0
Description = xx_ums Custom Application User
8) Add Application to a Data Group
Navigate to Security-->Oracle-->DataGroup
Data Group = xx_ums Group
Description = xx_ums Custom Data Group
Click on "Copy Applications from" and pick Standard data Group, then add the following entry.
Application = xx_ums Custom
Oracle ID = APPS
Description = xx_ums Custom Application
9) Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)Navigate to Security-->responsibility-->Request
Group= xx_ums Request Group
Application = xx_ums Custom
Code= xx_ums
Description = xx_ums Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
10) Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage) We will create two menus, one for Core Applications and one for Self Service. Navigate to Application-->Menu Menu= xx_ums_CUSTOM_MENU
User Menu Name = xx_ums Custom Application
Menu Type =
Description= xx_ums Custom Application Menu
Seq= 100
Prompt= View Requests
Submenu=
Function = View All Concurrent Requests
Description = View Requests
Seq= 110
Prompt= Run Requests
Submenu=
Function= Requests: Submit
Description = Submit Requests
Menu= xx_ums_CUSTOM_MENU_SSWA
User Menu Name = xx_ums Custom Application SSWA
Menu Type=
Description = xx_ums Custom Application Menu for SSWA
11) Create new responsibility.
One for Core Applications and One for Self Service (SSWA) Navigate to Security-->Responsibility-->Define
Responsibility Name= xx_ums Custom
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOM
Description= xx_ums Custom Responsibility
Available From= Oracle Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application
Request Group Name= xx_ums Request Group
Responsibility Name= xx_ums Custom SSWA
Application= xx_ums Custom
Responsibility Key = xx_ums CUSTOMSSWA
Description = xx_ums Custom Responsibility SSWA
Available From= Oracle Self Service Web Applications
Data Group Name= xx_ums Group
Data Group Application = xx_ums Custom
Menu= xx_ums Custom Application SSWA
Request Group Name= xx_ums Request Group
12) Add responsibility to user
Navigate to Security-->User-->DefineAdd xx_ums Custom responsibility to users as required.
13) Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc Create the source code files in the xx_ums _TOP directory appropriate for the type of object. For example forms would be located in
$xx_ums_TOP/forms/US or package source code in
$xx_ums_TOP/admin/sql for example. Database Objects, such as tables, indexes and sequences should be created in the xx_ums schema, then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example: logged in as xx_ums user
grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object For example: logged in as APPS user
Create synonym myTable for xx_ums.myTable; Other database objects, such as views and packages should be created directly in the APPS schema.
Order to Cash Cycle
1. Order Entry :
2.Order Booking:
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.
oe_order_headers_alL (flow_status_code as BOOKED, booked_flag updated to Y)
oe_order_lines_all (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
wsh_delivery_details (DELIVERY_DETAIL_ID is assigned here, released_status ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
wsh_delivery_assignments (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in wsh_delivery_details, DELIVERY_ID remains blank till this stage)
*In shipping transaction form order status remains "Ready to Release".
At the same time, Demand interface program runs in background And insert into inventory tables mtl_demand, here LINE_ID come as a reference in DEMAND_SOURCE_LINE
3. Reservation:
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the mtl_demand and mtl_reservations table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.
4. Pick Release:
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.Once pick release is done these are the tables get affected:
If step 3 is not done then MTL_RESERVATIONS gets updated now.
wsh_new_deliveries (one record gets inserted with SOURCE_HEADER_ID= order header ID, status_code=OP =>open)
wsh_delivery_assignments (DELIVERY_ID gets assigned which comes from wsh_new_deliveries)
wsh_delivery_details (released_status ‘S’ ‘submitted for release’)
MTL_TXN_REQUEST_HEADERS
MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
(move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id, this table holds the record temporally)
MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )
*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS
5.Pick Confirm/ Move Order Transaction:
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.
MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
oe_order_lines_all (flow_status_code ‘PICKED’ )
MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
mtl_transaction_accounts
wsh_delivery_details (released_status becomes ‘Y’ => ‘Released’ )
wsh_delivery_assignments
MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release
6.Ship Confirm:
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries.
The items on the delivery gets shipped to customer at this stage.
oe_order_lines_all (flow_status_code ‘shipped’)
wsh_delivery_details (released_status ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
mtl_transaction_interface
mtl_material_TRANSACTIONS (linked through Transaction source header id)
mtl_transaction_accounts
Data deleted from mtl_demand, MTL_reservations
Item deducted from MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')
7.Enter Invoice:
After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
ra_interface_lines_all (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to order number and line_id of the orders)
8.Complete Line:
In this stage order line level table get updated with Flow status and open flag.oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order:
This is last step of Order Processing. In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)
oe_order_HEADERS_all
This is first stage, When the order is entered in the system, it creates a record in order headers and Order Lines table.
Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked_flag = N), Primary key=HEADER_ID
No record exists in any other table for this order till now.
Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID. oe_order_lines_all (flow_status_code = ENTERED, booked_flag = N, open_flag = Y) Primary key= LINE_IDEnter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table oe_order_headers_all flow_status_code = ENTERED, booked_flag = N), Primary key=HEADER_ID
No record exists in any other table for this order till now.
2.Order Booking:
This is next stage, when Order is booked then the Flow status changed from Entered to Booked. At this stage, these below table get affected.
oe_order_headers_alL (flow_status_code as BOOKED, booked_flag updated to Y)
oe_order_lines_all (flow_status_code as AWAITING_SHIPPING, booked_flag updated Y)
wsh_delivery_details (DELIVERY_DETAIL_ID is assigned here, released_status ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
wsh_delivery_assignments (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in wsh_delivery_details, DELIVERY_ID remains blank till this stage)
*In shipping transaction form order status remains "Ready to Release".
At the same time, Demand interface program runs in background And insert into inventory tables mtl_demand, here LINE_ID come as a reference in DEMAND_SOURCE_LINE
3. Reservation:
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once this program get successfully get completed, the mtl_demand and mtl_reservations table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.
4. Pick Release:
Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.
Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.Once pick release is done these are the tables get affected:
If step 3 is not done then MTL_RESERVATIONS gets updated now.
wsh_new_deliveries (one record gets inserted with SOURCE_HEADER_ID= order header ID, status_code=OP =>open)
wsh_delivery_assignments (DELIVERY_ID gets assigned which comes from wsh_new_deliveries)
wsh_delivery_details (released_status ‘S’ ‘submitted for release’)
MTL_TXN_REQUEST_HEADERS
MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)
(move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)
Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id, this table holds the record temporally)
MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)
MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )
*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS
5.Pick Confirm/ Move Order Transaction:
Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.
Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.
MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
oe_order_lines_all (flow_status_code ‘PICKED’ )
MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
mtl_transaction_accounts
wsh_delivery_details (released_status becomes ‘Y’ => ‘Released’ )
wsh_delivery_assignments
MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release
6.Ship Confirm:
Here ship confirm interface program runs in background. Data removed from wsh_new_deliveries.
The items on the delivery gets shipped to customer at this stage.
oe_order_lines_all (flow_status_code ‘shipped’)
wsh_delivery_details (released_status ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
mtl_transaction_interface
mtl_material_TRANSACTIONS (linked through Transaction source header id)
mtl_transaction_accounts
Data deleted from mtl_demand, MTL_reservations
Item deducted from MTL_ONHAND_QUANTITIES
MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')
7.Enter Invoice:
After shipping the order the order lines gets eligible to get transfered to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order.
ra_interface_lines_all (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to order number and line_id of the orders)
8.Complete Line:
In this stage order line level table get updated with Flow status and open flag.oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)
9.Close Order:
This is last step of Order Processing. In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)
oe_order_HEADERS_all
Open Interfaces in Oracle Applications for Data Migration
General Ledger :
• Budget Upload (Refer to Uploading Budgets section of the Budgets chapter of the Oracle General Ledger User Guide)
• Importing Journals (Refer to Journal Import section of the Journals chapter of the Oracle General Ledger User Guide)
• Loading Daily Rates (Refer to Loading Daily Rates section of the Multi–Currency chapter of the Oracle General Ledger User Guide)
Oracle Payables:
• Credit Card Transaction Interface Table (Refer to Payable Credit Card Transactions Table appendix in the Oracle Payables User Guide)
• Invoice Import Interface. This interface is no longer supported for importing invoices. Use the Payables Open Interface instead. If you want to review column descriptions for the table, refer to the Invoice Import Interface Tables appendix in the Oracle Payables User’s Guide)
• Payables Open Interface (Refer to Payables Open Interface Tables appendix in the Oracle Payables User Guide)
• Purchase Order Matching (Refer to Purchase Order Matching Database Tables appendix in the Oracle Payables User Guide)
Oracle Receivables:
• AutoInvoice (Refer to Importing Invoice Information Using AutoInvoice in the Oracle Receivables User Guide)
• AutoLockbox (Refer to Using AutoLockbox in the Oracle Receivables User Guide)
• Customer Interface (Refer to Customer Interface in the Oracle Receivables User Guide)
• Sales Tax Rate Interface (Refer to Importing Address Validation Data and Sales Tax Rates in the Oracle Receivables Tax Manual)
• Tax Vendor Extension (Refer to Implementing the Tax Vendor Extension in the Oracle Receivables Tax Manual)
Oracle Assets:
• ACE Interface (Refer to About the ACE Interface section of the Oracle Assets User Guide)
• Budget Open Interface (Refer to Budget Open Interface section of the Oracle Assets User Guide)
• Mass Additions Interface (Refer to About the Mass Additions Interface section of the Oracle Assets User Guide)
• Production Interface (Refer to Using the Production Interface section of the Oracle Assets User Guide)
• Physical Inventory (Refer to Loading Physical Inventory Data section of the Oracle Assets User Guide)
Oracle Cash Management:
• Bank Statement Open Interface (Refer to Bank Statement Open Interface section of the Oracle Cash Management User Guide)
• Forecasting Open Interface (Refer to Forecasting Open Interface section of the Oracle Cash Management User Guide)
• Reconciliation Open Interface (Refer to Reconciliation Open Interface section of the Oracle Cash Management User Guide)Oracle Purchasing
• Requisitions Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
• Purchasing Documents Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
• Receiving Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
Oracle Inventory:
• Customer Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Replenishment Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Transaction Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Cycle Count Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Reservations Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Move Orders Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
Oracle Projects:
• Activity Mangement Gateway (Refer to Activity Management Gateway Technical Reference Manual)
• Client Extensions (Refer to Client Extensions in the Oracle Projects User Guide)
• Transaction Import (Refer to Intergrating Oracle Projects with non–Oracle Products in the Oracle Projects User Guide)
• Budget Upload (Refer to Uploading Budgets section of the Budgets chapter of the Oracle General Ledger User Guide)
• Importing Journals (Refer to Journal Import section of the Journals chapter of the Oracle General Ledger User Guide)
• Loading Daily Rates (Refer to Loading Daily Rates section of the Multi–Currency chapter of the Oracle General Ledger User Guide)
Oracle Payables:
• Credit Card Transaction Interface Table (Refer to Payable Credit Card Transactions Table appendix in the Oracle Payables User Guide)
• Invoice Import Interface. This interface is no longer supported for importing invoices. Use the Payables Open Interface instead. If you want to review column descriptions for the table, refer to the Invoice Import Interface Tables appendix in the Oracle Payables User’s Guide)
• Payables Open Interface (Refer to Payables Open Interface Tables appendix in the Oracle Payables User Guide)
• Purchase Order Matching (Refer to Purchase Order Matching Database Tables appendix in the Oracle Payables User Guide)
Oracle Receivables:
• AutoInvoice (Refer to Importing Invoice Information Using AutoInvoice in the Oracle Receivables User Guide)
• AutoLockbox (Refer to Using AutoLockbox in the Oracle Receivables User Guide)
• Customer Interface (Refer to Customer Interface in the Oracle Receivables User Guide)
• Sales Tax Rate Interface (Refer to Importing Address Validation Data and Sales Tax Rates in the Oracle Receivables Tax Manual)
• Tax Vendor Extension (Refer to Implementing the Tax Vendor Extension in the Oracle Receivables Tax Manual)
Oracle Assets:
• ACE Interface (Refer to About the ACE Interface section of the Oracle Assets User Guide)
• Budget Open Interface (Refer to Budget Open Interface section of the Oracle Assets User Guide)
• Mass Additions Interface (Refer to About the Mass Additions Interface section of the Oracle Assets User Guide)
• Production Interface (Refer to Using the Production Interface section of the Oracle Assets User Guide)
• Physical Inventory (Refer to Loading Physical Inventory Data section of the Oracle Assets User Guide)
Oracle Cash Management:
• Bank Statement Open Interface (Refer to Bank Statement Open Interface section of the Oracle Cash Management User Guide)
• Forecasting Open Interface (Refer to Forecasting Open Interface section of the Oracle Cash Management User Guide)
• Reconciliation Open Interface (Refer to Reconciliation Open Interface section of the Oracle Cash Management User Guide)Oracle Purchasing
• Requisitions Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
• Purchasing Documents Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
• Receiving Open Interface (Refer to Purchasing in the Oracle Manufacturing and Distributions Open Interfaces Manual)
Oracle Inventory:
• Customer Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Item Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Replenishment Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Open Transaction Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Cycle Count Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Reservations Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
• Move Orders Open Interface (Refer to Inventory in the Oracle Manufacturing and Distribution Open Interfaces Manual)
Oracle Projects:
• Activity Mangement Gateway (Refer to Activity Management Gateway Technical Reference Manual)
• Client Extensions (Refer to Client Extensions in the Oracle Projects User Guide)
• Transaction Import (Refer to Intergrating Oracle Projects with non–Oracle Products in the Oracle Projects User Guide)
PL/SQL Collections
PL/SQL RECORD:
A record is a group of related data items stored in fields, each with its own name and datatype. Suppose you have various data about an employee such as name, salary, and hire date. These items are logically related but dissimilar in type. A record containing a field for each item lets you treat the data as a logical unit. Thus, records make it easier to organize and represent information.
The attribute %ROWTYPE lets you declare a record that represents a row in a database table. However, you cannot specify the datatypes of fields in the record or declare fields of your own. The datatype RECORD lifts those restrictions and lets you define your own records.
Manipulating Records
The datatype RECORD letxs you collect information about the attributes of something.
The information is easy to manipulate because you can refer to the collection as a whole.
In the following example, you collect accounting figures from database tables assets and liabilities, then use ratio analysis to compare the performance of two subsidiary companies:
DECLARE
TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
Sub1_figs FiguresRec;
Sub2_figs FiguresRec;
FUNCTION acid_test (figs FiguresRec) RETURN REAL IS...
BEGIN
SELECT cash, notes ... INTO sub1_figs FROM assets, liabilities
WHERE assets.sub = 1 AND liabilities.sub = 1;
SELECT cash, notes ... INTO sub2_figs FROM assets, liabilities
WHERE assets.sub = 2 AND liabilities.sub = 2;
IF acid_test (sub1_figs) > acid_test (sub2_figs) THEN...
...
END;
Notice how easy it is to pass the collected figures to the function acid_test, which computes a financial ratio.
----------------------------------------------------------------------------------------------
In the example below, you fetch rows from database table flights into record flight_info. That way, you can treat all the information about a flight, including its passenger list, as a logical unit.
DECLARE
TYPE FlightRec IS RECORD (
flight_no NUMBER(3), gate CHAR(5), departure CHAR(15), arrival CHAR(15),
passengers PassengerList);
flight_info FlightRec;
CURSOR c1 IS SELECT * FROM flights;
seat_not_available EXCEPTION;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO flight_info;
EXIT WHEN c1%NOTFOUND;
FOR i IN 1...FLIGHT_info.passengers.LAST LOOP
IF flight_INFO.PASSENGERS (i).seat = ’NA’ THEN
dbms_output.put_LINE (flight_INFO.PASSENGERS (i).name);
RAISE seat_not_available;
END IF;
...
END LOOP;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN seat_not_available THEN
...
END;
PL/SQL TABLE:
In the example below, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into index-by table empnos. The previous values are overwritten.
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
BULK COLLECT / FORALL / DYNAMIC SQL:
CREATE OR REPLACE PROCEDURE bulkcollect_9i_demo (whr_in IN VARCHAR2 := NULL)
IS
TYPE numlist_t IS TABLE OF NUMBER;
TYPE namelist_t IS TABLE OF VARCHAR2 (100);
-- New Oracle9i pre-defined REF CURSOR type. This is equivalent to:
-- TYPE sys_refcursor IS REF CURSOR
emp_cv sys_refcursor;
empnos numlist_t;
enames namelist_t;
enames_updated namelist_t;
ename_filter namelist_t := namelist_t ('S%', 'E%', 'M%');
sals numlist_t;
l_count PLS_INTEGER;
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
-- Bulk fetch with cursor variable
OPEN emp_cv FOR 'SELECT empno, ename FROM emp WHERE ' NVL (whr_in, '1=1');
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
-- Bulk fetch with "implicit cursor"
EXECUTE IMMEDIATE 'SELECT sal FROM emp WHERE ' NVL (whr_in, '1=1')
BULK COLLECT INTO sals;
Dbms_Output.put_line (sals.COUNT);
-- Bulk, dynamic UPDATE
FORALL indx IN empnos.FIRST .. empnos.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1 WHERE empno = :employee_key '
'RETURNING ename INTO :names_updated'
USING empnos(indx) -- Must specify individual row with FORALL index
RETURNING BULK COLLECT INTO enames_updated; -- Specify collection as whole
-- Using SQL%BULK_ROWCOUNT: how many rows modified by each statement?
FORALL indx IN ename_filter.FIRST .. ename_filter.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1
WHERE ename LIKE :employee_filter'
USING ename_filter(indx);
FOR indx IN ename_filter.FIRST .. ename_filter.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Number of employees with names like "'
ename_filter(indx)
'" given a raise: ' SQL%BULK_ROWCOUNT(indx));
END LOOP;
END;
/
A record is a group of related data items stored in fields, each with its own name and datatype. Suppose you have various data about an employee such as name, salary, and hire date. These items are logically related but dissimilar in type. A record containing a field for each item lets you treat the data as a logical unit. Thus, records make it easier to organize and represent information.
The attribute %ROWTYPE lets you declare a record that represents a row in a database table. However, you cannot specify the datatypes of fields in the record or declare fields of your own. The datatype RECORD lifts those restrictions and lets you define your own records.
Manipulating Records
The datatype RECORD letxs you collect information about the attributes of something.
The information is easy to manipulate because you can refer to the collection as a whole.
In the following example, you collect accounting figures from database tables assets and liabilities, then use ratio analysis to compare the performance of two subsidiary companies:
DECLARE
TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
Sub1_figs FiguresRec;
Sub2_figs FiguresRec;
FUNCTION acid_test (figs FiguresRec) RETURN REAL IS...
BEGIN
SELECT cash, notes ... INTO sub1_figs FROM assets, liabilities
WHERE assets.sub = 1 AND liabilities.sub = 1;
SELECT cash, notes ... INTO sub2_figs FROM assets, liabilities
WHERE assets.sub = 2 AND liabilities.sub = 2;
IF acid_test (sub1_figs) > acid_test (sub2_figs) THEN...
...
END;
Notice how easy it is to pass the collected figures to the function acid_test, which computes a financial ratio.
----------------------------------------------------------------------------------------------
In the example below, you fetch rows from database table flights into record flight_info. That way, you can treat all the information about a flight, including its passenger list, as a logical unit.
DECLARE
TYPE FlightRec IS RECORD (
flight_no NUMBER(3), gate CHAR(5), departure CHAR(15), arrival CHAR(15),
passengers PassengerList);
flight_info FlightRec;
CURSOR c1 IS SELECT * FROM flights;
seat_not_available EXCEPTION;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO flight_info;
EXIT WHEN c1%NOTFOUND;
FOR i IN 1...FLIGHT_info.passengers.LAST LOOP
IF flight_INFO.PASSENGERS (i).seat = ’NA’ THEN
dbms_output.put_LINE (flight_INFO.PASSENGERS (i).name);
RAISE seat_not_available;
END IF;
...
END LOOP;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN seat_not_available THEN
...
END;
PL/SQL TABLE:
In the example below, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into index-by table empnos. The previous values are overwritten.
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
BULK COLLECT / FORALL / DYNAMIC SQL:
CREATE OR REPLACE PROCEDURE bulkcollect_9i_demo (whr_in IN VARCHAR2 := NULL)
IS
TYPE numlist_t IS TABLE OF NUMBER;
TYPE namelist_t IS TABLE OF VARCHAR2 (100);
-- New Oracle9i pre-defined REF CURSOR type. This is equivalent to:
-- TYPE sys_refcursor IS REF CURSOR
emp_cv sys_refcursor;
empnos numlist_t;
enames namelist_t;
enames_updated namelist_t;
ename_filter namelist_t := namelist_t ('S%', 'E%', 'M%');
sals numlist_t;
l_count PLS_INTEGER;
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN
-- Bulk fetch with cursor variable
OPEN emp_cv FOR 'SELECT empno, ename FROM emp WHERE ' NVL (whr_in, '1=1');
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;
-- Bulk fetch with "implicit cursor"
EXECUTE IMMEDIATE 'SELECT sal FROM emp WHERE ' NVL (whr_in, '1=1')
BULK COLLECT INTO sals;
Dbms_Output.put_line (sals.COUNT);
-- Bulk, dynamic UPDATE
FORALL indx IN empnos.FIRST .. empnos.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1 WHERE empno = :employee_key '
'RETURNING ename INTO :names_updated'
USING empnos(indx) -- Must specify individual row with FORALL index
RETURNING BULK COLLECT INTO enames_updated; -- Specify collection as whole
-- Using SQL%BULK_ROWCOUNT: how many rows modified by each statement?
FORALL indx IN ename_filter.FIRST .. ename_filter.LAST
EXECUTE IMMEDIATE
'UPDATE emp SET sal = sal * 1.1
WHERE ename LIKE :employee_filter'
USING ename_filter(indx);
FOR indx IN ename_filter.FIRST .. ename_filter.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (
'Number of employees with names like "'
ename_filter(indx)
'" given a raise: ' SQL%BULK_ROWCOUNT(indx));
END LOOP;
END;
/
Oracle Applications Data Structure
Oracle Applications Data Structure:
Introduction :
Oracle Applications has a very flexible architecture containing valuable information but with flexibility comes complexity in the underlying data structure. Understanding the basic Oracle Applications structure is critical in retrieving important and relevant content. This information is an asset and when used appropriately can be used as a competitive advantage.
This article provides a high level overview of the Oracle Applications data structure and some business examples. However, before we get understand the data structure there is some technical terminology that needs explanation.
The business examples will be illustrated in Oracle SQL*Plus but you can use the same table joins in your preferred reporting writing tool such as Oracle Querybuilder and Oracle Discoverer.
Please refer to the Oracle Technical Reference manuals for more detail on the entity data models, table descriptions and functional decomposition.
Also note that the following information is based on Oracle Applications Release 11.03 data structures.
Definitions:
Table
The basic entity of storage in a relational database management system.
Consist of one or more units of information (records) each of which contains a number of data elements or fields (or columns).
Record/Row
One set of related information contained in a table.
Column/Field
A subdivision of a table, with a column name and a specific data type.
View
A datasource made up of columns from one or more database tables combined into one logical table or object.
Synonym
Another name assigned to a table for easy identification and used for data classification between Oracle Application modules.
Primary Key
The column or columns that uniquely identify each row of a table.
Foreign Key
One of more columns in one table whose values refer to the primary key values in another table.
Query
Series of command to instruct the database to retrieve the data you have specified.
Relationship
A connection between two or more tables.
Entity Relationship Diagram (ERD)
Tips
· There is a screen in Oracle Applications where you would like to report the information.
Click on Help > About This Record from the Menu.
· There is a table name (e.g. ITEM) you would like to use but unsure of the exact name.
Login to SQL*Plus and type
SELECT table_name
FROM all_tables
WHERE table_name LIKE ‘%ITEM%’
· There is a column (e.g. CUSTOMER_NAME) that you would like to use but unsure which table the column belongs to.
Login to SQL*Plus and type
SELECT table_name, column_name
FROM all_tab_columns
WHERE column_name = ‘CUSTOMER_NAME’
· The columns org_id and organization_id are different. Organisation_id refers to the inventory organizations in Oracle Inventory and org_id refers to the operating unit.
· Table names that ends with _all has the column org_id included. The org_id signifies the operating unit. Therefore, if you have multi-organization functionality, you will need to use the _all tables. Note that not all tables will have _all suffix.
· The majority of the entities describes in the following are tables but you may find that there is a view that combines all your requirements. These views will be based on non _all tables. You need to type the following in SQL*Plus to see the data in these views.exec dbms_application_info.set_client_info(org_id)Org_id is the operating unit in hr_organization_units.
The following illustrates a simplified view of the Oracle Applications data schema. Each data schema is mapped to an application module. The apps schema has a number of synonyms and views of all the Oracle Application modules. The fnd has all the schema all the Oracle Application foundation information such as user profiles, responsibility and value sets.You need to login to the appropriate schema when using query tools on a table. Therefore, consult your Database Administrator regarding security to the schema/schemas you need access.
Oracle General Ledger:
Gl_code_combinations
: Setup > Accounts > Combinations
This table stores the valid account combinations.
The value in your chart of account segments is stored in the columns segment1 to segment30 depending on your application configuration.
For example, say your chart of accounts is Company – Cost Centre – Accountthen segment1 = company, segment 2 = cost centre and segment3 = account.However, this sequencing of segments is not guaranteed therefore, its best to check your configuration.
Another important column is the account_type, which signifies your account, is an Asset, Liability, Revenue, Expense or Owners Equity account.
Gl_je_batches
: Journals > Enter
This table stores the journal entry batches. Journal entries are batched in General Ledger.
Some columns of interest includes:
· Name
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Status
· Default_period_name
· Posted_date
· Posting_run_id
Gl_je_headers
: Journals > Enter
This table stores the journal entry headers. There is always two journal lines for each journal header.
Some columns of interest includes:
· Je_category
· Period_name
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Posted_flag
· Je_source
· Name
· Status
Gl_je_lines
: Journals > Enter
This table stores the journal entry lines.
The entered_dr and entered_cr stores the amount in the entered currency whereas the accounted_dr and accounted_cr stores the amount in the functional currency.
Other columns of interest includes:
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Period_name
· Status
· Description
· Reference_1..reference10 (these columns links back to your Subledgers)
For example, for Purchasing transactions
Reference_1 = ‘PO’Reference_2 = po_headers_all.po_header_idReference_3 = po_distributions_all.po_distribution_idReference_4 = po_headers_all.segment? (this is the purchase order number
Oracle Payables:
Ap_invoices_all
: Invoices > Entry > Invoices
This table stores all the invoices you enter. For an invoice to be approved, the total invoice amount must be stored in ap_invoice_distributions_all and ap_payment_schedules_all.
Some columns of interest includes :
· Invoice_num
· Invoice_date
· Amount_paid
· Invoice_currency_code
· Invoice_type_lookup_code
· Payment_status_flag
Ap_invoice_distributions_all
: Invoices > Entry > Invoices
This table stores the accounting information for the invoice you have entered. There is one row for each invoice disribution, that is this table corresponds to the Distributions window.
Some columns of interest includes :
· Line_type_lookup_code
· Dist_code_combination_id (credit entry)
· Accts_pay_code_combination_id (debit_entry)
· Base_amount (in functional currency)
Ap_checks_all
: Payments > Entry > Payments
This table stores payments to suppliers.
Some columns of interest includes :
· Amount (in functional currency)
· Check_date
· Bank_account_name
· Check_number
· Payment_method_lookup_code
· Payment_type_flag
Ap_invoice_payments_all
: Payments > Entry > Payments
This table stores invoice payments to suppliers. This table is updated when you confirm an automatic payment batch, enter a manual payment or process a Quick Payment. Void payments are represented as a negative of the original payment line.
Some columns of interest includes:
· Accounting_date
· Period_name
· Amount
· Payment_num
Ap_payment_distributions_all
: Payments > Entry > Payments
This table stores accounting information for payments. There is at least one CASH payment distribution for each invoice payment. Additional rows may include DISCOUNT, GAIN and LOSS distributions where appropriate.
Some columns of interest includes :
· Line_type_lookup_code (CASH/DISCOUNT/GAIN/LOSS)
· Base_amount
Oracle Purchasing:
Po_vendors
: Supply Base > Suppliers
This table stores supplier information.
Some columns of interest includes :
· Segment1 (supplier number)
· Vendor_name
· Terms_id
· Vendor_type
· Ship_to_location (link to hr_locations for location information)
· Bill_to_location (link to hr_locations for location information)
Po_vendor_sites_all
: Supply Base > Suppliers
This table stores supplier sites information.
Some columns of interest includes:
· Pay_site_flag
· Purchasing_site_flag
· Address_line1 to address_line3
· City
· State
· Area_code
· Zip
Po_headers_all
: Purchase Orders > Purchase Orders
This table stores the seven types of purchasing documents such as Purchase Order and Blanket Agreement.
Segment1 is the document number (i.e. purchase order number)
Some columns of interest includes:
· Agent_id (link to per_people_f for the buyer)
· Type_lookup_code
Po_lines_all
: Purchase Orders > Purchase Orders
This table stores purchasing document lines.
Some columns of interest includes :
· Line_num
· Item_description
· Unit_price
· Unit_meas_lookup_code (unit of measure)
· Quantity
· Item_id (link to mtl_system_items for the item number)
· Category_id (link to mtl_categories for the category name)
Po_line_locations_all
: Purchase Orders > Purchase Orders
This table stores purchase order shipment schedules and blanket agreement price breaks. A purchase order is closed when QUANTITY is equal to QUANTITY_RECEIVED.
Some columns of interest includes:
· Quantity
· Quantity_accepted
· Quantity_received
· Quantity_cancelled
· Need_by_date
· Ship_to_organization_id (link to org_organization_definitions for the organization code)
Po_distributions_all
: Purchase Orders > Purchase Orders
This table stores the accounting information on a purchase order shipment. This table is used for Standard and Planned Purchase Orders and Planned and Blanket Purchase Order Release.
Some columns of interest includes:
· Quantity_ordered
· Quantity_billed
· Amount_billed
· Quantity_delivered
· Quantity_cancelled
· Destination_organization_id (link to org_organization_definitions for the organization code)
· Destination_subinventory
Rcv_shipment_headers
: Receiving > Receipts
This table stores the receiving information. The three receipt sources are Supplier, Inventory and Internal Order. There is one receipt header per receipt source.
Some columns of interest includes:
· Receipt_num
· Shipment_num
· Receipt_source_code
· Shipped_date
· Ship_to_org_id
Rcv_shipment_lines
: Receiving > Receipts
This table stores information about items that have been shipped and/or received from a receipt source.
Some columns of interest includes:
· Line_num
· Quantity_shipped
· Unit_of_measure
· Item_id (link to mtl_system_items for item number)
· To_organization_id (link to org_organization_definitions for organization code)
· To_subinventory
· Shipment_line_status_code (EXPECTED, FULLY RECEIVED, PARTIALLY RECEIVED)
· Quantity_received
· Quantity_shipped
Oracle Inventory:
Org_organization_definitions
: Setup > Organizations > Parameters
This view contains basic information on all inventory organisations.
Some columns of interest includes:
· Organization_code
· Organization_name
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Inventory_enabled_flag
Mtl_secondary_inventories
: Setup > Organizations > Subinventories
This table stores all subinventory information for an inventory organisation.
Some columns of interest includes:
· Secondary_inventory_name
· Description
Mtl_material_transactions
: Transactions > Material Transactions (Inquiry)
This table stores all inventory transactions including cost updates.
Some columns of interest includes:
· Transaction_quantity
· Transaction_type_id
· Transaction_source_type_id
· Transaction_source_name
Mtl_transaction_accounts
: Transactions > Material Distributions (Inquiry)
This table stores the inventory accounting information. There are two rows in this table for each transaction in mtl_material_transactions.
Some columns of interest includes:
· Transaction_date
· Gl_batch_id
· Accounting_line_type
· Base_transaction_value
Mtl_system_items
: Items > Master Items or Items > Organization Items
This table stores the item definition. An item must exist in an inventory organisation.
Your item number is stored in the columns segment1 to segment20 depending on your application configuration. If you have configured your items to have to segments then you may be using segment1 and segment2
Some columns of interest includes:
· Segment1 to segment20
· Description
· Invetory_item_flag
· Purchasing_item_flag
· Inventory_asset_flag
· Stock_enabled_flag
· Invoiceable_item_flag
· Shippable_item_flag
· So_transaction_flag
· Mtl_transactions_enabled_flag
· Primary_unit_of_measure
Mtl_onhand_quantities
: On-hand, Availability > On-hand Quantities
This table stores quantity on hand in a location for each item.
Some columns of interest includes:
· Date_received
· Transaction_quantity
· Subinventory_code
Cst_item_costs
: Costs > Item Costs
This table stores the item cost information. Note that there can be multiple costs per item and the actual cost is where the cost type is Frozen.
Some columns of interest includes:
· Cost_type_id (link to cst_cost_types)
· Item_cost
Oracle Receivables:
Ra_customers
: Customers > Standard
This table stores customer information.
Some columns of interest includes:
· Customer_name
· Customer_number
· Status
· Customer_prospect_code
· Customer_type
· Orig_system_reference (for imported customers from an external source)
Ra_addresses_all
: Customers > Standard
This table stores customer address information and your remit-to addresses.
Some columns of interest includes:
· Status
· Orig_system_reference (for imported customer addresses from an external source)
· Address1 to address4
· City
· State
· Postal_code
Ra_site_uses_all
: Customers > Standard
This table stores the customer’s site and site purpose. You must have one row for each address. A customer must have one bill to address for Receivables. A customer must have one ship to address and one bill to address for Order Entry.
Some columns of interest includes:
· Site_use_code (BILL_TO, SHIP_TO, STMTS, DUN/LEGAL)
· Primary_flag
· Status
· Location
Ra_customer_trx_all
: Transactions > Transactions
This table stores invoice, debit memo, chargeback, commitment and credit memo header information.
Some columns of interest includes:
· Cust_trx_type_id (link to ra_cust_trx_types_all)
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Terms_id (link to ra_terms)
· Trx_number (invoice number)
· Trx_date (invoice date)
Ra_customer_trx_lines_all
: Transactions > Transactions
This table stores the invoice, debit memo, chargeback, commitment and credit memo line information.
Some columns of interest includes:
· Line_number
· Description
· Quantity_ordered
· Quantity_credited
· Quantity_invoiced
· Unit_standard_price
· Unit_selling_price
· Line_type
· Extended_amount
· Revenue_amount
Ra_cust_trx_line_gl_dist_all
: Transactions > Transactions
This table stores the accounting information for revenue, unearned revenue, unbilled receivables, receivables, charges, freight and tax for each invoice or credit memo line.
Some columns of interest includes:
· Amount_gl_date
· Gl_posted_date
· Account_class (CHARGES/FREIGHT/TAX/REC/REV/UNBILL/UNEARN)
· Acctd_amount (functional currency)
Ar_cash_receipts
: Receipts > Receipts
This table stores the payment information.
Some columns of interest includes:
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
· Status (APP, UNAPP, UNID, NSF, STOP, REV)
· Type (CASH, MISC)
· Receipt_number
· Amount
· Currency_code
· Pay_from_customer
· Receipt_date
Ar_receivable_applications
: Receipts > Receipts
This table stores accounting entries for cash and credit memo applications.
Some columns of interest includes:
· Amount_applied
· Line_applied
· Tax_applied
· Application_type
· Display
· Gl_date
· Set_of_books_id (when you have more than one set of book, you’ll also need to link to gl_sets_of_books)
Ar_payment_schedules
: Transactions > Transactions and Receipts > Receipts
This table stores all transactions except adjustments and miscellaneous cash receipts. This table is updated when a transaction occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Some columns of interest includes:
· Amount_due_original
· Status
· Class (DEP, DM, PMT, GUAR, CM, CB, INV)
· Due_date
· Amount_due_remaining
· Invoice_currency_code
· Amount_applied
· Anmount_credited
· Amount_adjusted
Oracle Order Entry:
So_headers_all
: Orders, Returns > Orders, Returns
This table stores the orders and returns information.
The s1 to s30 and s1_date to s30_date relates to the order cycle status.
Some columns of interest includes:
· Order_category (I, P. S, R, RMA)
· Order_number
· Purchase_order_num
· Original_system_source_code (for imported orders from an external source)
· Original_system_reference (for imported orders from an external source)
· Order_type_id (link to so_order_types_all for order type)
· Date_ordered
· S1 to s30
· S1_date to s30_date
So_lines_all
: Orders, Returns > Orders, Returns
This table stores the orders and returns line information.
The s1 to s30 and s1_date to s30_date relates to the order line cycle status.
Some columns of interest includes:
· Line_type_code (DETAIL, PARENT, REGULAR, RETURN)
· Ordered_quantity
· Cancelled_quantity
· Selling_price
· Price_list_id (links to so_price_lists for price list)
· Schedule_date
· Promise_date
So_line_details
: Orders, Returns > Orders, Returns
This table stores order scheduling information.
Some columns of interest includes :
· Released_flag
· Quantity
· Schedule_date
· Delivery (link to mtl_demand for reservation details)
So_picking_batches_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the batch of orders that have been pick released.
The header_count column indicates the number of picking headers are contained in a picking batch.
So_picking_headers_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the picking headers within a picking batch.
Some columns of interest includes:
· Status_code (BACKORDERED, BACKORDER RELEASE, CLOSED, OPEN, PENDING, IN PROGRESS)
· Pick_slip_number
· Picked_by (link to per_people_f for picked by user)
· Date_released
· Date_confirmed
· Date_shipped
So_picking_lines_all
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the picking lines for a picking header.
Some columns of interest includes:
· Requested_quantity
· shipped_quantity
· Date_requested
· Cancelled_quantity
So_picking_line_details
: Shipping > Release Sales Orders or Shipping > Release Sales Orders, SRS
This table stores the location for the picking lines that have been reserved.
Some columns of interest includes:
· Requested_quantity
· Serial_number
Wsh_departure
: Shipping > Departure Planning> Departure Planning or Shipping > Departure Planning > New Departures
This table stores departure information for departure planning.
Some columns of interest includes:
· Name
· Planned_departure_date
· Actual_depature_date
Wsh_delivery
: Shipping > Departure Planning> Departure Planning or Shipping > Departure Planning > New Deliveries
This table stores delivery information within a departure.
Some columns of interest includes:
· Name
· Planned_departure_date
· Actual_depature_date
· Waybill
· Date_closed
Subscribe to:
Posts (Atom)