Oracle EBS - Order Management Sales Order Line Need to Split using Oracle API

set serveroutput on;

DECLARE

 l_header_rec OE_ORDER_PUB.Header_Rec_Type;

 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;

 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;

 l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 l_request_rec OE_ORDER_PUB.Request_Rec_Type ;

 l_return_status VARCHAR2(1000);

 l_msg_count NUMBER;

 l_msg_data VARCHAR2(1000);

 p_api_version_number NUMBER :=1.0;

 p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;

 p_return_values VARCHAR2(10) := FND_API.G_FALSE;

 p_action_commit VARCHAR2(10) := FND_API.G_FALSE;

 x_return_status VARCHAR2(1);

 x_msg_count NUMBER;

 x_msg_data VARCHAR2(100);

 p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_old_header_rec OE_ORDER_PUB.Header_Rec_Type :=  OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;

 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;

 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;

 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;

 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type; 

 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;

 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;

 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;

 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;

 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;

 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;

 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;

 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;

 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;

 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;

 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 X_DEBUG_FILE VARCHAR2(100);

 l_line_tbl_index NUMBER;

 l_msg_index_out NUMBER(10);

BEGIN

 dbms_output.enable(1000000);

 fnd_global.apps_initialize(<user_id>,<resp_id>,<app_id>); -- pass in user_id, responsibility_id, and application_id

 oe_msg_pub.initialize;

 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data

 dbms_output.put_line('START OF NEW DEBUG');

--This is to UPDATE order line

 l_line_tbl_index :=1; -- First Existing Line Update

-- Changed attributes

 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

 l_header_rec.header_id := <header_id>; -- header_id of the order

 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE; --** For the Existing line Operation is Update

 l_line_tbl(l_line_tbl_index).split_by := <user_id>; -- Pass user_id who is splitting the line

 l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index).header_id := <header_id>; -- header_id of the order

 l_line_tbl(l_line_tbl_index).line_id := <line_id>; -- line_id of the order line which needs to split

 l_line_tbl(l_line_tbl_index).ordered_quantity := <order_qty>; -- new ordered quantity of the line which you want to split (Original qty = 100 and now want to split as 70 and 30)

 l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- Enter the reason code for this Split

 l_line_tbl_index :=2; --For New Split Line

 l_line_tbl(l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index ).operation := OE_GLOBALS.G_OPR_CREATE; --** For the Splited line Operation is Create

 l_line_tbl(l_line_tbl_index ).split_by := 'USER'; -- Enter user(Bug 25793299)

 l_line_tbl(l_line_tbl_index ).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index ).split_from_line_id := <line_id>; -- line_id of  original line from which the line is splitted

 l_line_tbl(l_line_tbl_index ).inventory_item_id := <item_id>; -- inventory item id

 l_line_tbl(l_line_tbl_index ).ordered_quantity := 30; -- Enter the remaining qty after Split in ordered quantity (Enter 30 as you entered as 70 at top)

-- CALL TO PROCESS ORDER

 OE_ORDER_PUB.process_order (

  p_api_version_number => 1.0

  , p_init_msg_list => fnd_api.g_false

  , p_return_values => fnd_api.g_false

  , p_action_commit => fnd_api.g_false

  , x_return_status => l_return_status

  , x_msg_count => l_msg_count

  , x_msg_data => l_msg_data

  , p_header_rec => l_header_rec

  , p_line_tbl => l_line_tbl

  , p_action_request_tbl => l_action_request_tbl

-- OUT PARAMETERS

  , x_header_rec => p_header_rec

  , x_header_val_rec => x_header_val_rec

  , x_Header_Adj_tbl => x_Header_Adj_tbl

  , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl

  , x_Header_price_Att_tbl => x_Header_price_Att_tbl

  , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl

  , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl

  , x_Header_Scredit_tbl => x_Header_Scredit_tbl

  , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl

  , x_line_tbl => p_line_tbl

  , x_line_val_tbl => x_line_val_tbl

  , x_Line_Adj_tbl => x_Line_Adj_tbl

  , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl

  , x_Line_price_Att_tbl => x_Line_price_Att_tbl

  , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl

  , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl

  , x_Line_Scredit_tbl => x_Line_Scredit_tbl

  , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl

  , x_Lot_Serial_tbl => x_Lot_Serial_tbl

  , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl

  , x_action_request_tbl => l_action_request_tbl

 );

 dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages

 FOR i IN 1 .. l_msg_count

 LOOP

  Oe_Msg_Pub.get( p_msg_index => i

  , p_encoded => Fnd_Api.G_FALSE

  , p_data => l_msg_data

  , p_msg_index_out => l_msg_index_out);

  DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);

  DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);

 END LOOP;

-- Check the return status

 IF l_return_status = FND_API.G_RET_STS_SUCCESS

 THEN

  dbms_output.put_line('SO Line Qty Update Sucessful');

 ELSE

  dbms_output.put_line('AO Line Qty update Failed');

 END IF;

END;

/

Commit;

Oracle EBS: Interface Tables and Base Tables

 **********************

--General Ledger--

**********************

--Interface Table--

gl_interface

gl_budget_interface

--Base Table--

gl_je_batches

gl_je_headers

gl_je_lines

gl_je_sources

gl_je_categories

gl_sets_of_books

gl_daily_rates

gl_balances

gl_periods

gl_period_sets

gl_code_conbinations

 

**********************

--Receivables--

**********************

--Interface Table--

ra_customers_interface_all  

ra_contact_phones_int_all

ra_customer_profiles_int_all

--Base Table--

hz_cust_sit_use_all

hz_cust_acct_sites_all

hz_parties hz_cust_accounts

hz_party_sites

hz_locations

hz_party_site_uses

hz_customer_profiles

hz_organization_profiles

hz_person_profiles

 

--Invoice--

--Interface Table--

ra_interface_lines_all

ra_interface_distributions_all

ra_interface_salescredits_all

--Base Table--

ra_customer_trx_all

ra_customer_trx_lines_all

ra_cust_trx_line_gl_dist_all

ra_cust_trx_types_all

ar_payment_schedules_all

ra_batch_sources_All

ar_vat_tax_all

ra_terms ar_periods

ar_period_types

 

**********************

--Order Management--

**********************

--Interface Table--

oe_headers_iface_all

oe_lines_iface_all

oe_actions_iface_all

--Base Table--

oe_order_headers_all

oe_order_lines_all

oe_order_sources

oe_transaction_types_tl

oe_transaction_types_all

 

**********************

--Inventory--

**********************

--Interface Table--

mtl_system_items_interface

mtl_item_categories_interface

mtl_item_revisions_interface

mtl_interface_errors

 

--Base Table--

mtl_system_items_b

mtl_system_items_tl

mtl_item_locations

mtl_item_locations

mtl_item_categories

mtl_item_revisions_b

mtl_parameters

hr_all_organizations_units

cst_item_costs

qp_list_headers

jft_rs_salesreps

 

**********************

--Purchasing--

**********************

--Interface Table--

PO REQUISITIONS

po_requistitions_interface_all

po_req_dist_interface_all

 

--Base Table--

PO REQUISITIONS

po_requisition_headers_all

po_requisition_lines_all

po_req_distributions_all

 

**********************

--PURCHASE ORDERS--

**********************

--Interface Table--

po_headers_interface

po_lines_interface

po_distributions_interface

 

--Base Table--

po_headers_all

po_lines_all

po_line_locations_all

po_distributions_all

 

**********************

--Payables--

**********************

--Interface Table--

ap_invoices_interface

ap_invoice_lines_interface

 

--Base Table--

ap_invoices_all

ap_invoice_distributions_all

ap_payments_schedules_all

 

**********************

--Fixed Assets--

**********************

--Interface Table--

fa_inv_interface

fa_production_interface

fa_mass_additions

 

--Base Table--

fa_additions_b

fa_additions_tl

fa_books

fa_deprn_summary

fa_deprn_details

fa_deprn_history

fa_retirements

Oracle EBS: How to Create Materialized View & How to Refresh Materialized View

Subject: We will review here how to create materialized view and how to refresh the materialized view on demand.

 

Step1: Create Employee and Employee Address table based on that we create Materialized view. Also, insert 1 -1 record each.

create table xx_emp (emp_num NUMBER, emp_name VARCHAR2(240));

INSERT INTO xx_emp VALUES(1001,'David');

 

create table xx_emp_add (emp_num NUMBER, address VARCHAR2(240));

INSERT INTO xx_emp_add VALUES(1001,'USA');

 

Step2: Create materialized view based on the above-created custom tables.

 

create materialized view xx_emp_v AS

select

a.emp_num,

a.emp_name,

b.address

from

xx_emp a,

xx_emp_add b

where a.emp_num=b.emp_num;

 

Step3: Try to run the materialized view.

select * from xx_emp_v;  --It will give one record only

                                    

 

Step4: Create a new Entry and check the materialized view output.

INSERT INTO xx_emp VALUES(1002,'John');

INSERT INTO xx_emp_add VALUES(1002,'France');

 

select * from xx_emp_v;  --It will give one record only

 

Step5: We need to refresh the materialized view using the below script and check the Materialized view output.

exec dbms_mview.refresh('xx_emp_v'); -- Materialized view refresh script.

select * from xx_emp_v;  --It will give two records only