Query to Find Schema and its status

Query to list out the Schemas and the status of the products in Oracle Applications.


SQL Queries for practice

SQL Queries for practice


Using SQL Functions

Queries Using Joins


SQL’s using SQL Hierarchy Queries

Psuedo Columns

Set Operators in SQL

Views In Oracle


DBA Related Stuff 1

DBA Related Stuff 2

On-line Analytical Processing (OLAP)

Update And Delete


SQL Advanced Tables

Object Oriented Database Management System




Thanks Rajesh for Providing the SQL Material.

Create Receivable Invoice

Create Receivable Invoice

check the status of the order line.
Navigate to the sales order form, and query your sales order. Go to the Line Items tab and check that the status of the line is ‘Shipped’.
While still on the line, go to Tools > Workflow Status.



Verify that the order line workflow is now at the Fulfill – Deferred activity with a status of Deferred. This means that the next steps is to run the Workflow Background Process.

Navigate to Reports, Requests > Run Requests.
Run the Workflow Background Process with the following parameters.
Item Type : OM Order Line
Process Deferred : Yes
Process Timeout : Yes


Go to View > Requests, and verify that the Workflow Background Process (WFBP) has completed successfully. In addition, notice that the AR concurrent requests Autoinvoice and Autoinvoice Import Program get kicked off automatically to import the invoices into AR. OM inserts invoice data into the AR interface tables through ‘Invoice Interface’, which runs in the background (notice the workflow activity ‘Invoice Interface’ in the screenshot from Step 4 below). AR picks up the data from the interface tables through the ‘Autoinvoice’ concurrent program.


Once the data is interfaced to AR or AR Interface Tables the status of the order Lines will be changed to Closed.

Query the order in the sales order form, and go to the lines. Notice the status is now Closed. Note that the Closed status in the order lines signifies that OM has successfully passed the invoice data into the AR interface tables, and does not necessarily signify that the invoice has been successfully imported into AR (in case there was some error during that process).

The workflow status for the order line (go to Tools > Workflow Status) should now be Ended.


Once the Auto invoice program is Run you can see the AR Invoice Created. To check this Change the Responsibility to Receivables, Vision Operations(USA), Transactions –> Transactions and query for Source as ORDER ENTRY and Reference as the Sales Order number 66429.


The data is inserted into RA_CUSTOMER_TRX_ALL with interface header attribute1 will store the Sales Order Number and RA_CUSTOMER_TRX_LINES_ALL with interface line attribute6 having the sales order line Id stored.


Some time the Auto invoice Import Program might not invoice the Lines and the instance might have issues like opening Periods. To Open the GL Periods Navigate to Control –> Accounting –> Open/Close Periods and Open the Period which the System Date falls in.


Run the Auto invoice Import Program Manually by navigating to Receivables > Interfaces > Autoinvoice, and selecting the Autoinvoice Master Program.

Enter the following parameters and submit the concurrent request :
Invoice Source : ORDER ENTRY
Default Date : SYSTEM DATE
(Low) Sales Order Number / (High) Sales Order Number : 66429

After submission of the concurrent request, go to View > Requests.
You should see the Autoinvoice Master Program followed by the Autoinvoice Import Program.
After completion, click on the View Output button for the Autoinvoice Import Program to check for the error messages. Autoinvoice needs to be resubmitted after the errors are fixed.

Ship Confirm the Sales Order

Ship Confirm the Sales Order

Navigate to Shipping > Transactions.

In the Query Manager screen, enter your order number in the From Order Number field and tab out. Then, click on the Find button. All non-shipped lines will appear. Click on the details button to see detailed line information.


Notice the following in the Lines/LPNs tab.
Detail : The delivery detail of the shipment
Delivery : The delivery that got autocreated during pick release
Line Status : Staged/Pick Confirmed
Next Step : Ship Confirm/Close Trip Stop


Click on the Delivery tab and the details button to see detailed delivery information. You can enter the waybill number & additional info etc. here.To ship confirm the order, select the Actions list in the Delivery Tab, choose Ship Confirm and GO.

The ship confirmation window will appear and give you the options to back order, ship all or ship partial quantities and set user defined shipping documents to print. Enter the trip creation options viz: Ship method, departure date, close trip and defer interface.The ship confirm process triggers the inventory interface automatically to update quantities, and triggers the Order Management Interface to update the status of the order lines.

Not checking the Defer Interface check box, as shown here, ensures that the next step, running the Interface Trip Stop (ITS) concurrent program, happens automatically. Deferring ITS (checking this box) means you need to run the concurrent in a separate step.

Interface Trip Stop (ITS) can be done from Shipping > Interfaces > Run (Interface Trip Stop – SRS).


Even if we get warning messages regarding the Carrier Code ignore at this point of time.

Go to View > Requests, and verify that the Interface Trip Stop completed successfully.

To check if the item has actually been shipped out of inventory.
Navigate to Inventory > Change Organization and select M1.
Navigate to Inventory > Transactions > Material Transactions


Enter the following information in the Find Material Transactions screen and click on Find
Transaction Date : Ensure the transaction date is within the range specified
Source Type : Sales Order
Source :
-Evoke the LOV and click on the button Combinations
-Enter your Sales Order Number and Order Type Mixed, then click OK
-Example : 66429.Mixed.ORDER ENTRY

Go to the Transaction Type tab, and notice the line with Transaction Type = Sales Order Issue. This is the Inventory Transaction that happened when the item was shipped out of the warehouse M1, hence the negative quantity -1.

The other two lines with Transaction Type = Sales Order Pick, are for the move order transaction that happened automatically during Pick Release , when the item was moved from the pick-from sub inventory to the staging sub inventory. Hence there is one line each for a negative and positive transaction from/to each sub inventory. The final shipment to the customer (sales order issue) is made from the staging sub inventory.


Tables effected in this transactions are:




Pick Release Sales Order

Pick release Sales Order

Once the order is scheduled and booked then we need to release the order to warehouse for shipping. To Release the sales Order Navigate to

Shipping –> Release Sales Orders –> Release Sales Orders

  • In the Order tab, enter your order number.
  • In some cases, you may need to remove the defaulted ‘To’ value for ‘Scheduled Ship Dates’ and ‘Requested Dates’ fields, if the order line you created has a future date for these fields. Pick release will only consider lines within the dates specified.


Go to the Inventory tab and enter the warehouse M1. Set the following value if it is not defaulted.
Auto Allocate = Yes.

Make sure to include a Warehouse, based on this the order lines available on the order will be filtered. Choose Auto Allocate as “Yes” such that the system will suggest from which sub inventory or locator the goods can be shipped.


Go to the Shipping tab. Set the following value if it is not defaulted.
Set Autocreate delivery to Yes, such that a shipper/Delivery is automatically created.Check Auto Pick Confirm box for accepting the suggestions given by system and staging the goods at staging subinventory. If you don’t want to use the suggestions made by the system then say Auto pick confirm as “No”. Then you need to go to Transact move order screen in Inventory and  modify the suggestions made by the system and thus manually pick confirm and Click on the Concurrent button.


Once the Concurrent Button is pressed

Pick Select tin List Generation, Pick Slip report, Shipping Exception Reports are run in the background.


Some times the even the Programs are kicked off from the background Process the Status of the Delivery will not be updated to Staged/Pick Confirmed. In such case we need to pick release the Order from Navigate to Shipping –> Transactions Form and Select the Delivery Detail and Launch Pick Release and then Go button.



The tables which are effected by entering these transactions are:



WSH_DELIVERY_ASSIGNMENTS apart from Material transactions Table.


We can also check the Pick Confirm activity by Navigate to Inventory –> Transact Move Orders Search the Sales order with order type, Order number and source as ‘ORDER ENTRY’ or by searching using the Picking batch number from the report.

After finding the Move order, click on view/update allocation to update the system generated inventory allocations. View the allocation done by the system. You can change the sub inventory and quantity and click on transact.View the status of the order line in Shipping transactions window. The status should be changed to Staged/Pick confirmed.