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