Order Management Tables. in Oracle Apps by Avinash
Order Management Tables.
Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes
then populated
oe_order_holds_all If any hold applied for order like
credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to
Warehouse (Line has been released to Inventory
for processing)
wsh_picking_batches After batch is created for pick
release.
mtl_reservations This is only soft reservations. No
physical movement of stock
Full Transaction
mtl_material_transactions No records in
mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data
populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard
Reservations. Picked the stock. Physical movement of
stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped
;Delivery Note get printed Delivery assigned
to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form,
check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e
its deferred then OM & inventory not updated. If
Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup
& drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then
only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run
workflow background process.
ra_interface_lines_all Data will be populated after
wkfw process.
ra_customer_trx_all After running Autoinvoice Master
Program for
ra_customer_trx_lines_all specific batch transaction
tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms Payment terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic
Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include
name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can
get)
oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text
information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule
Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Release
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt
Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.
By,
Jayakrishnagoud Nimmagudem
Mobile: +91-9963262526
E-Mail :Nimmagudem.jaya@gmail.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ORACLE ALERTS
Overview of Alerts:
Oracle Alerts facilitates the flow of information
within the organization by letting us create entities called alerts to
monitor the business information and to notify us of the information we want.
Oracle Alerts
· Keeps
us informed of the critical activities in the database.
· Delivers
the key information from applications, in the format we want.
· Provides
us with regular reports on database information.
· Automate
system maintenance, and routine online tasks.
Types of Alerts:
1. Event
Alert
2. Periodic
Alert
Event Alert: An Event Alert immediately notifies us of
activity in the database as it occurs i.e. This type of
alert will fire on a database event, such as an update or
insert into a specified table.
Periodic Alert: A periodic Alert checks the database
for information according to a schedule we
define.
Defining Periodic Alert:
Responsibility : Alert Manager, Vision Enterprises
Navigation : Alert → Define
1. Choose the
Application that owns the alert for example: Inventory.
2. Enter Name (for example XX_TEST1) for the alert.
3. Check Enabled flag – for activation of alert. You can
leave it unchecked until you are ready to run the alert.
4. Choose Periodic tab. Enter the Frequency and Start Time
you want the alert to run.
Select a frequency for your periodic alert from
nine frequency options:
• On Demand—If you choose this frequency, Oracle Alert
checks your periodic alert only when you specify. You do not have to fill in
any other field in the Periodic Details block. You check on
demand alerts by using the Request Periodic Alert Check
window.
• On Day of the Month—If you choose this frequency,
Oracle Alert checks your alert on a monthly basis on the day number you enter
in the Day field.
• On Day of the Week—If you choose this frequency,
Oracle Alert checks your alert on the day of the week you enter in the Day
field.
• Every N Calendar Days—If you choose this frequency,
enter a value in the Days field. Oracle Alert considers every day a calendar
day, and does not skip holidays.
• Every Day—Choosing this frequency is the same as choosing
Every N Calendar Days and entering a value of 1 in the Days field.
• Every Other Day—Choosing this frequency is the same
as choosing Every N Calendar Days and entering a value of 2 in the Days field.
• Every N Business Days—If you choose this frequency,
enter a value in the Days field. Oracle Alert lets you choose your business
days, but does not skip any holidays. A value of 1 indicates that Oracle Alert
should check the alert every business day, and a value of 2 indicates that
Oracle Alert should check the alert every other business day. If you enter 3 in
the Days field, Oracle Alert checks your periodic alert every three business
days.
For example, if you enable your alert on a Monday, Oracle
Alert checks the alert first on that Monday, then on Thursday, then on the
following Tuesday, then the following Friday, and so on.
• Every Business Day—Choosing this frequency is the
same as choosing Every N Business Days and entering a value of 1 in the Days
field.
• Every Other Business Day—Choosing this frequency is
the same as choosing Every N Business Days and entering a value of 2 in the
Days field.
6. Specify the number of days you want to keep the
alert history logs in Keep Days.
7. Enter SQL statement in Select Statement.
Select statement must include an INTO clause that
contains one output for each column selected by your Select statement.
For Example:
SELECT SEGMENT1,:DAYS
INTO &ITEM_NAME,&NDAYS
FROM MTL_SYSTEM_ITEMS_B
WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE-:DAYS);
Identify inputs with a colon before the name (in above
example :DAYS) and outputs with an ampersand (&)
before the name (in above example &ITEM_NAME,
&NDAYS).
you may also import a SQL Select statement from a file in
your operating system.
Choose Verify to parse the Select statement. The
results will be displayed in a Note window.
Choose Run to execute the Select statement. It
will display the number of rows returned in a Note window.
Save your work.
Re-Query the alert created and click on Alert
Details Button.
The Alert Details window includes information such as which
Application installations you want the alert to run against, what default
values you want your inputs variables to use, and whatadditional
characteristics you want your output variables to have.
The Alert Details window that appears contains
three tabbed regions: Installations, Inputs, and Outputs.
In the Inputs tabbed region, Oracle Alert automatically
displays the inputs used in your Select statement, unless they are the implicit.
Specify the Default Values for the inputs.
In the Outputs tabbed region, Oracle Alert automatically
displays the outputs used in your alert Select statement without
the ampersand (&).
In the Installations tabbed region,Oracle Alert automatically
displays the Oracle ID that is associated with the application that owns your
alert.
Specify the organization you want the alert to run
against in the Operating Unit field.
Creating Alert Actions:
After defining a Period or Event alert, you need to create
actions for the alert to perform. On the same alert form, click
on Actions button.
1. Enter the Action Name for the Alert.
2. Select a level for Action: Detail, Summary or
No Exception.
During an alert check, a detail action performs once for
each individual exception found, a summary action performs once for all
exceptions found, and a no exception action performs when no exceptions
are found.
Choose Action Details button in Actions form.
On the Action Details page, select the type of action
you want to create in the Action Type field.
Depending upon the action level and type you choose,
different fields appear in the Action Details window.
Message Action: To send a message as an action we
create Message Actions.
Concurrent Program Actions: To run a concurrent program
as an event action we create Concurrent Program Actions.
Operating Script Actions: To run an operating script as
an event action create Operating Script Actions.
SQL Statement Script Actions: To run a SQL statement
script as its action then create SQL Statement Script Actions.
Specify the electronic mail IDs of the recipients you want
to send your message to in the To: field.
You can also enter the IDs of recipients to whom you
want to send ”carbon” (Cc) and ”blind” (Bcc) copies of your alert message.
Specify the subject for the message
in Subject field.
If you want to write your message in Oracle Alert,
choose the Text option and enter the text in the Text field. (You can
also click on the Import button and import the contents of a file into the Text
field.)
You can include any outputs in the message text. (In the
above screen shot you can see that ITEM_NAME is the output variable).
Save the Record.
Creating an Action Set:
Once you create your alert actions, you must include them in
an enabled action set for Oracle Alert to perform during an alert check.
An action set can include an unlimited number of actions and any combination of
actions.
During each action set check, Oracle Alert executes each
action set member in the sequence you specify.
Steps:
1. Click
on Action Set button in Alert main form and enter the Action Set Name
and save the record.
2. Re-Query
the Alert and click on Action Set Button. You can see that input and output variables
are
automatically displayed in respective Input and Output tabs.
Note: At runtime, alert will use the values from the
Action Sets form. If you want the alert to run for different
Input value then make changes in the Action Set Definition
form.
3. In the
Members tab choose the Action you want the alert to perform.
Save the Record.
Running an Alert:
To manually run a Periodic Alert, Navigate to Request →
Check
Enter the Application that owns the Alert
Enter the name of the Periodic Alert you want to check and
submit the Request.
Once the concurrent program is done successfully navigate to
Alert → History to review history.
Enter the Application that owns the Alert.
Enter the name of the Periodic Alert you want to view.
Oracle Alert automatically displays the alert type,
the number of days of history kept, and when the alert was last checked.
Click on Find Checks button.
In this form it displays the history of action sets
that were run: the date and time when the action set completed and the Oracle
ID that the action set ran against.
In the Action Set Checks it displays the name of the Action
set and the number of exceptions found and the number of actions performed.
Click on Find Exceptions in the History window.
In this form it displays the Exceptions found for each out
put variable and their corresponding values.
Click on Find Actions button.
In this form it displays the action name, type of action and
Finished time of the action.
It also displays the details of the recipient to whom the
message was sent.
Defining Event Alert:
Responsibility : Alert Manager, Vision Enterprises
Navigation : Alert → Define
1. Choose the Application that owns the alert i.e. Inventory
2. Enter Name & Description for the alert
3. Check Enabled flag – for activation of alert. You can
leave it unchecked until you are ready to run the alert
4. Choose Event tab.
5. Choose the Application and database table you want Oracle
Alert to monitor.
6. Check After Insert and/or After Update if you want the
event alert to run when a user inserts and/or updates a row in the database
table.
7. Specify the number of days you want to keep the alert
history logs in Keep Days.
8. Enter SQL statement in Select Statement
SELECT SEGMENT1, INVENTORY_ITEM_ID,:ROWID,:ORG_ID,:MAILID
INTO &ITEM_NAME,
&item_id,&ROWID,&ORG_ID,&MAILID
FROM MTL_SYSTEM_ITEMS_B
WHERE TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
Choose Verify to parse the Select statement. The
results will be displayed in a Note window.
Choose Run to execute the Select statement. It
will display the number of rows returned in a Note window.
Creating Actions and Action sets for Event alert is same as
Creating Actions and Action sets for Periodic alert.
Event alert gets checked when an insert or an update to
an event table occurs.
Implicit Inputs:
Oracle Alert automatically displays values for the Implicit
Inputs.
:ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED.
The values of the implicit inputs are as follows:
• ROWID—Contains the ID number of the row where the insert
or update that triggers an event alert
occurs.
• MAILID—Contains the email user name of the person who
enters an insert or update that triggers an
event alert.
• ORG_ID—Contains the organization ID that is selected when
the alert runs.
• DATE_LAST_CHECKED—Contains the date and time that the
alert was most recently checked.
For Example consider an Event Alert with the select
statement:
SELECT SEGMENT1, INVENTORY_ITEM_ID,:ROWID,:ORG_ID,: DATE_LAST_CHECKED
INTO &ITEM_NAME,
&item_id,&ROWID,&ORG_ID,& DATE_LAST_CHECKED
FROM MTL_SYSTEM_ITEMS_B
where TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
Text in the Action Details
-------------------------------
item name :&ITEM_NAME
ITEM_ID: &ITEM_ID
ROWID: &ROWID
ORG_ID:&ORG_ID
DATE_LAST_CHECKED:&DATE_LAST_CHECKED
Output
------------
item name :SR_ITEM
ITEM_ID: 17849
ROWID: AAAehOAAkAAAB9SAAL
ORG_ID:204
DATE_LAST_CHECKED:31-MAR-2009 11:34:28
