O2C CYCLE steps

Steps

1. Enter sales order
2. Book the sales order
3. Pick release ( Release sales order)
4. Move order
5. Ship confirm
6. Create Invoice
7. Create Receipt
8. Transfer to General ledger
9. Journal import
10. journal posting

---

1. CREATE SALES ORDER 
   1. Header
      1. With customer info
   2. Line with item info
   3. Check availability qty of item
   4. Check the bank end tables
      1. OE_ORDER_HEADERS_ALL
         1. Flow_status_code = Entered
      2. OE_ORDER_LINES_ALL
         1. Flow_status_code = Entered 
2. BOOK SALES ORDER 
   1. Sales order status : booked
   2. Check the back end tables
      1. OE_ORDER_HEADERS_ALL
         1. Flow_status_code = Booked
      2. OE_ORDER_LINES_ALL
         1. (Flow_status_code = Awaiting shipping)
      3. WSH_DELIVERY_DETAILS
         1. RELEASED_STATUS= 'R' (ready to release)
      4. WSH_DELIVERY_ASSIGNMENTS
         1. new row inserted
   3. Backgroud programs
      1. DEMAND INTERFACE PROGRAM
3. PICK RELEASE ( Release sales order) [ Items in SO must be released from inventory ]
   1. Nav :( Shipping > Release sales order > Release sales order)
   2. search sales order
      1. auto create delivery ,Auto pick confirm, Auto pack delivery, Auto allocate = 'Y'
      2. Click 'Execute now' button
      3. Pick release process completes successfully and calls programs
         1. Auto pack report
         2. shipping exception report
         3. Pick slip report
   3. Back end tables 
      1. WSH_NEW_DELIVERIES ( NEW ROW CREATED)
      2. WSH_DELIVERY_DETAILS
         1. ( if Auto pick confirm = y )
            1. RELEASED_STATUS= 'S' (Release to warehouse)
         2. else do following from step 4 
4. PICK CONFIRM (Move order )[ Confirning that the released item is picked for shipping ]
   1. (Nav: Inventory super user > Move order > Transact move order)
   2. search with 4th number in 'pick slip report' (batch request number)
      1. click 'view update allocation ' > click Transact button > Transact button will be deactivated
      2. close Transact move order form
   3. Go to order organizer form> search SO number > additional info > deliveries tab > pick status > Staged / Pick confirm
   4. > view delivery details button > Delivery line details button >
      1. Line status : Staged / Pick confirm
      2. Next step  field value : ship confirm
5. SHIP CONFIRM [ Confirming that the item is Shipped at customers location ]
   1. Nav : OM > Shipping > tansactions
   2. query order number
   3. Click on Delivery tab shows > click Ship confirm button 
      1. Enable following  : Yes 
         1. Ship entered qty :  
         2. Create delivery for staged quantities : yes
         3. Set delivery in transit : yes
         4. Create bill of lading : yes
         5. Close trip: yes
      2. Get a message saying : Delivery was successfully confirmed 
   4. Ship confirm will call other programs like, in Run> View request
      1. Interface trip stop
      2. Vehicle load sheet details
      3. Commercial invoice
      4. Packing slip report
      5. Bill of lading
   5. Backend tables
      1. WSH_DELIVERY_DETAILS (RELEASE STATUS = C (means Ship confirm))
      2. OE_ORDER_LINES_ALL (Flow status code = shipped)
      3. MTL_RESERVATIONS( INVENTORY_ITEM_ID = 'o2citem' => table empty > data freezed)\
      4. MTL_ONHAND_QUANTITIES_DETAIL(for item 'o2c' reduces by qty of SO)
      5. MTL_MATERIAL_TRANSACTIONS( for inventory item id > will show 4 rows 
         1. look at transaction qty column  
            1. 1 row for adding items
            2. 1 row > release from Inventory 
            3. 1 for moving to staging area
            4. 1 row > staging to transportation
6. SO INTERFACE 
   1. Order organizer > SO > additional info >> deliveries > view delivery details >
      1. Line status > interfaced 
      2. next step  field : Not applicable
   2. RA_INTERFACE_LINES_ALL :  (INTERFACE_LINE_ATTRIBUTE1 = SO NUMBER)
      1. Now shipping status will be interfaced 
7. CREATE INVOICE 
   1. Run Workflow background process
      1. item type : OM standard
      2. process deferred : yes
      3. Process time out : Yes
   2. This calls 2 more programs
      1. ADS ( Auto invoice master)
      2. Auto invoice import
   3. Go to AR > Transactions form
   1. Query the invoice using SO in 'Reference' field 
   2. Note the invoice number
   3. create accounting
   4. Back end tables
      1. Transaction tables
         1. RA_CUSTOMER_TRX_ALL ( TRX NUMBER = INV NUM) - Note CUSTOMER_TRX_ID 
         2. RA_CUSTOMER_TRX_LINES_ALL ( CUSTOMER_TRX_ID = xx)
         3. RA_CUSTOMER_TRX_LINE_GL_DIST_ALL ( CUSTOMER_TRX_ID = xx) -
            1. Look for TAX amount
            2. Find EVENT_ID
      2. Create accounting - SLA
         1. SLA
            1. XLA_EVENTS (where EVENT_ID =xx)
            2. XLA_AE_HEADERS (where EVENT_ID = XX)
            3. XLA_AE_LINES (where AE_HEADER_ID = XX)
      3. Interface
         1. GL_INTERFACE ( Reference26 = EVENT_ID)
      4. GL_TABLES
         1. Create accounting
            1. View request > journal import > view output > note : batch name 
         2. GL resp
         3.  Tables
            1. GL_JE_BATCHES( NAME = BATCH NAME)
            2. GL_JE_HEADERS (JE_BATCH_ID)
            3. GL_JE_LINES (JE_HEADER_ID)
8. CREATE RECEIPT
   1. Create Reciepts for all the invoices created in previous step
   2. AR > Receipts form ( TRX NUMBER)
9. TRANSFER TO GL
   1. To transfer the Receivables accounting information to general ledger,
   2. run General Ledger Transfer Program.
      Navigation: Receivables> View Requests
   3. ( We use this for invoices from AUTO INVOICE PROGRAM )
10. JOURNAL IMPORT
    1. To transfer the data from General Ledger Interface table to General Ledger,
    2. run the Journal Import program from Oracle General Ledger.
    3. Navigation: General Ledger > Journal> Import> Run
11. JOURNAL POSTING
    1. We have to Post journal batches that we have imported previously to update the account balances in General Ledger.
    2. Navigation: General Ledger> Journals > EnterQuery for the unposted journals for a specific period as shown below.

Custom Business Events - How to Create, Subscribe and Raise

Create Business Event and its Subscription. How to test Business event!!!

Business event will function same as trigger. when ever any create, update or delete action will happen on business event related page it will trigger this Business event. 

We will add any PLSQL function, procedure or any java class to create some action which is related to our requirement.

e.g. if suppose on supplier page we need detail for any updated field then we can assign any custom function in subscription of business event "apps.oracle.pos.supplier.profile"

 To Create Business Event kindly add Workflow Administration Responsibility to your user.
In that responsibility you will find business event setup in below given navigation.
Workflow => Administrator Workflow => Business Events

Press on Create Event Button and enter information for Event Name, Display Name, Owner Name and Owner Tag details as shown in below screenshot.

Click on Apply Button. Then search for xxcust.oracle.test.funcBE in Business event window.

and click on subscription.

Now click on Create subscription Button

Enter system node information and mentioned Action Type as "Custom". and then click on Next Button.

Create custom function which will insert business event related information into temp table.

I have created one function "xxcust_anyevent_log" which have logic to insert business event related detail into custom table.

Mention same function name in PLSQL Rule function section as shown in below given screenshot.

click on Next button. on next page insert information for Owner name and owner Tag and then click on apply button. 

Again go back to business event Tab and search for custom business event name. you confirm business event name and subscription PLSQL function name.

To test this business event click on Test Button.

Enter Event Key as any number and then click on Raise in PLSQL button , It will show you message Event xxcust.oracle.test.funcBE is raised.

Log in to SqlDeveloper and query to custom table xxcust_test_event_log. it will show you records with custom business event.

HOW TO RAISE THE CUSTOM BUSINESS EVENT

wf_event.RAISE to be used for the purpose.
Sample code for the same can be seen below.

DECLARE
  x_event_parameter_list wf_parameter_list_t;
  x_user_id                    INTEGER := 99999;
  x_user_name               VARCHAR2(100) := 'ANILPASSI';
  x_param                     wf_parameter_t;
  x_event_name             VARCHAR2(100) := 'xx.oracle.apps.test01';
  x_event_key               VARCHAR2(100) := 'ANIL_0001';
  x_parameter_index       NUMBER := 0;
BEGIN
  x_event_parameter_list := wf_parameter_list_t();
  --Lets add the first value to the Event Parameter i.e. user_id
  x_param := wf_parameter_t(NULL
                           ,NULL);
  x_event_parameter_list.EXTEND;
  x_param.setname('XX_TEST_USER_ID');
  x_param.setvalue(x_user_id);
  x_parameter_index := x_parameter_index + 1;
  x_event_parameter_list(x_parameter_index) := x_param;
  --Lets add the second value to the Event Parameter i.e. User Name
  x_param := wf_parameter_t(NULL
                           ,NULL);
  x_event_parameter_list.EXTEND;
  x_param.setname('XX_TEST_USER_NAME');
  x_param.setvalue(x_user_name);
  x_parameter_index := x_parameter_index + 1;
  x_event_parameter_list(x_parameter_index) := x_param;
  wf_event.RAISE(p_event_name => x_event_name
                ,p_event_key  => x_event_key
                ,p_parameters => x_event_parameter_list
             /*,p_event_data   =>  p_data*/
                );
END;
/

Important points to remember:

------------------------------------------------------------------------------------------------------------------------

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department, count (*) as cnt

From emp

Group by department

Correct way is:

Select deptno as department, count (*) as cnt

From emp

Group by deptno

------------------------------------------------------------------------------------------------------------------------

WITH CHECK OPTION

The WITH CHECK OPTION clause is used for an updatable view to prohibits the changes to the view that would produce rows which are not included in the defining query.
Sql> create or replace view t1_view as select name, id from t1 where id > 3 with check option;
The COUNT (*) will count all rows in the table.
--------------------------------------------------------------------------------------------------------------------------
If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.
SELECT DISTINCT  (a.sal) 

-------------------------------------------------------------------------------------------------------------------------
Which is faster – IN or EXISTS?

• IN for big outer query and small inner query.

• EXISTS for small outer query and big inner query.

CREATE TABLE Employees
Emp_ID int NOT NULL CHECK (Emp_ID>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255)
);

 Table Based Records

 Cursor Based Records

 Programmer-defined Records

x



View created.

--------------------------------------------------------------------------------------------------------------------------

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

--------------------------------------------------------------------------------------------------------------------------------------
SQL Inline Views
An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query.

--------------------------------------------------------------------------------------------------------------------------------------
Query to find the Nth highest salary:

FROM EMP A 
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) 
            FROM EMP B 
            WHERE a.sal<=b.sal)

In other words,

If both the outer query and inner query were large, either could work well – the choice would depend on indexes and other factors. Use whichever makes logical sense in the context. Or whichever is your personal favorite. Oracle will figure out the most efficient way to run the query, either way.

--------------------------------------------------------------------------------------------------------------------------------------
Union all is faster than union, union's duplicate elimination requires a sorting operation, which takes time.

--------------------------------------------------------------------------------------------------------------------------------------
The SQL CHECK constraint bounds/limits the value range that can be placed in a column.

(

--------------------------------------------------------------------------------------------------------------------------------------

PL/SQL Collections

 Records

 PL/SQL Tables

 Varrays

 Nested Tables

--------------------------------------------------------------------------------------------------------------------------------------

In XML Publisher Data Template The location of the trigger indicate at what point the trigger fires:

• Place a beforeReport trigger anywhere in your data template before the <dataStructure> section.. A beforeRepot trigger fires before the dataQuery is executed.
• Place an afterReport trigger after the <dataStructure> section. An afterReport trigger fires after you exit and after XML output has been generated.

--------------------------------------------------------------------------------------------------------------------------------------
What is the difference between the bad file and the discard file in SQL*Loader.
Answer:  The bad file and discard files both contain rejected rows, but they are rejected for different reasons:

• Bad file:  The bad file contains rows that were rejected because of errors.  These errors might include bad datatypes or referential integrity constraints.
• Discard file:  The discard file contains rows that were discarded because they were filtered out because of a statement in the SQL*Loader control file.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------