Script to Create Sales Order from Backend

DECLARE
  l_api_version_number NUMBER := 1;
  l_return_status      VARCHAR2(2000);
  l_msg_count          NUMBER;
  L_MSG_DATA           VARCHAR2(2000);
  ---PARAMETERS
  l_org       NUMBER := 2; ---- OPERATING UNIT
  l_no_orders NUMBER := 1; ---- NO OF ORDERS
  ---- INPUT VARIABLES FOR PROCESS_ORDER API
  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;
  ---- OUT VARIABLES FOR PROCESS_ORDER API
  l_header_rec_out oe_order_pub.header_rec_type;
  l_header_val_rec_out oe_order_pub.header_val_rec_type;
  l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
  l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
  l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
  l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
  l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
  l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
  l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
  l_line_tbl_out oe_order_pub.line_tbl_type;
  l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
  l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
  l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
  l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
  l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
  l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
  l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
  l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
  l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
  l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
  L_ACTION_REQUEST_TBL_OUT OE_ORDER_PUB.REQUEST_TBL_TYPE;
  v_hdr_adj_tbl oe_order_pub.Header_Adj_Tbl_Type;
  l_msg_index  NUMBER;
  l_data       VARCHAR2(2000);
  l_loop_count NUMBER;
  L_DEBUG_FILE VARCHAR2(200);
BEGIN
  ---- INITIALIZATION REQUIRED FOR R12
  mo_global.set_policy_context ('S', 2);
  mo_global.init('ONT');
  ---- INITIALIZE ENVIRONMENT
  fnd_global.apps_initialize (user_id => 129201, RESP_ID => 21623, resp_appl_id => 660);
  --- INITIALIZE HEADER RECORD
  l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
  ---- POPULATE REQUIRED ATTRIBUTES
  l_header_rec.operation               := OE_GLOBALS.G_OPR_CREATE;
  l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
  L_HEADER_REC.PRICING_DATE            := sysdate;
  l_header_rec.cust_po_number          := 'TEST-001';
  l_header_rec.sold_to_org_id          := 13562;
  l_header_rec.price_list_id           := 7314584;
  L_HEADER_REC.ORDERED_DATE            := SYSDATE;
  l_header_rec.shipping_method_code    := '000001_BEST WAY_L_LTL';
  L_HEADER_REC.SOLD_FROM_ORG_ID        := 2;
  l_header_rec.ship_from_org_id        := 72;
  l_header_rec.ship_to_org_id          := 1257135;
  L_HEADER_REC.SALESREP_ID             := -3;
  --L_HEADER_REC.BOOKED_FLAG:='Y';--Assign "Y" If we need to create in Booked Status
  --l_header_rec.flow_status_code:='BOOKED';
  L_HEADER_REC.ORDER_TYPE_ID       := 1510;
  L_HEADER_REC.FREIGHT_CARRIER_CODE:='BEST WAY';
  L_HEADER_REC.FREIGHT_TERMS_CODE  :='FLAT';
  L_HEADER_REC.PRICE_LIST_ID       :=7314584;
  ---REQUIRED HEADER DFF INFORMATIONS
  l_header_rec.attribute4 :='Testmail'; ---- Entering Branch
  ---REQUIRED HEADER DFF INFORMATIONS FOR REFERENCE DEFINED ATTRIBUTE4
  l_line_tbl(1)                     := OE_ORDER_PUB.G_MISS_LINE_REC;
  l_line_tbl(1).operation           := OE_GLOBALS.G_OPR_CREATE; ---- Mandatory Operation to Pass
  l_line_tbl(1).inventory_item_id   := 30192021;
  l_line_tbl(1).ordered_quantity    := 1;
  L_LINE_TBL(1).SHIP_FROM_ORG_ID    := 73;
  L_LINE_TBL(1).SUBINVENTORY        := 'ZPARTS';
  L_LINE_TBL(1).UNIT_SELLING_PRICE  :=449.00;
  L_LINE_TBL(1).CALCULATE_PRICE_FLAG:='Y';--Assign "Y" If we need to create in along with Charges.
  FOR i IN 1..l_no_orders
  LOOP ---- BEGIN LOOP
    ---CALLTO PROCESS ORDER API
    oe_order_pub.process_order( p_org_id => l_org, p_operating_unit => NULL, p_api_version_number => l_api_version_number, p_header_rec => l_header_rec, P_LINE_TBL => L_LINE_TBL,
    --p_Header_Adj_tbl        => v_hdr_adj_tbl,
    p_action_request_tbl => l_action_request_tbl,
    ---- OUT variables
    x_header_rec => l_header_rec_out, x_header_val_rec => l_header_val_rec_out, x_header_adj_tbl => l_header_adj_tbl_out, x_header_adj_val_tbl => l_header_adj_val_tbl_out, x_header_price_att_tbl => l_header_price_att_tbl_out, x_header_adj_att_tbl => l_header_adj_att_tbl_out, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out, x_header_scredit_tbl => l_header_scredit_tbl_out, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out, x_line_tbl => l_line_tbl_out, x_line_val_tbl => l_line_val_tbl_out, x_line_adj_tbl => l_line_adj_tbl_out, x_line_adj_val_tbl => l_line_adj_val_tbl_out, x_line_price_att_tbl => l_line_price_att_tbl_out, x_line_adj_att_tbl => l_line_adj_att_tbl_out, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out, x_line_scredit_tbl => l_line_scredit_tbl_out, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out, x_lot_serial_tbl => l_lot_serial_tbl_out, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out, x_action_request_tbl => l_action_request_tbl_out, x_return_status =>
    l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data);
    ---- CHECK RETURN STATUS
    IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
      DBMS_OUTPUT.PUT_LINE('Sales Order Successfully Created');
      COMMIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('Failed to Create Sales Order');
      ROLLBACK;
    END IF;
  END LOOP;
  ---- DISPLAY RETURN STATUS FLAGS
  DBMS_OUTPUT.PUT_LINE('Process Order Return Status is:  '|| L_RETURN_STATUS);
  DBMS_OUTPUT.PUT_LINE('Process Order msg data is: ' ||L_MSG_DATA);
  DBMS_OUTPUT.PUT_LINE('Sales Order Created is: '|| TO_CHAR(L_HEADER_REC_OUT.ORDER_NUMBER));
  DBMS_OUTPUT.PUT_LINE('Flow_Status_Code For the Sales Order is=>:'|| L_HEADER_REC_OUT.FLOW_STATUS_CODE);
  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_data ,P_MSG_INDEX_OUT => L_MSG_INDEX);
    DBMS_OUTPUT.PUT_LINE('message is:'|| L_DATA);
    DBMS_OUTPUT.PUT_LINE('message index is:'|| l_msg_index);
  END LOOP;
END;

Query to get the Responsiblity Name when User Form Function Name is passed as parameter

SELECT DISTINCT fft.USER_FUNCTION_NAME,
  fft.FUNCTION_ID,
  fme.menu_id,
  fr.RESPONSIBILITY_ID,
  frt.RESPONSIBILITY_name
FROM FND_FORM_FUNCTIONS_TL FFT,
  FND_MENU_ENTRIES FME,
  FND_MENU_ENTRIES_TL FMET,
  FND_RESPONSIBILITY FR,
  FND_RESPONSIBILITY_TL frt
WHERE FFT.FUNCTION_ID     =FME.FUNCTION_ID
AND FMET.MENU_ID          =FME.MENU_ID
AND FR.MENU_ID            =FME.MENU_ID
AND FFT.USER_FUNCTION_NAME=:USER_FORM_FUNCTION--Name of the user form function
AND frt.RESPONSIBILITY_ID =fr.RESPONSIBILITY_ID
AND fft.language          ='US';

How to get a explain plan for a query

1. Add "explain plan for" before select as "explain plan for select 'explain plan test' from dual"

2. And then execute "select * from table(dbms_xplan.display)"

Creating a User in Oracle Cloud ERP

Login to Oracle Cloud application click on Navigator 

Click on setup and Maintainance link

Navigate to All Task Tab and search for task 'Create Implementation user' and click on go to task.

New window opens

To create a New User we shold have access to role 'IDENTITY USER ADMINISTRATORS' to Check for the role click on My Roles to view all the roles assigned.

If Role does not exist Request for the role.

Now we have privileges to create a User.

Click on create user

Enter all mandatory fields and click on save

New User is created.

In the Roles Tab we can add the Roles

Difference Between Fusion And R12 Application

Difference Between Fusion And R12 Application

Component	Oracle e-Business Suite	Oracle Fusion Applications
Database	Oracle Database 10g	Oracle Database 11g
Application Server	Oracle application server 10.1.2 (Forms) Oracle Application server 10.1.3 (OC4J)	Oracle WebLogic
User Interface	Forms, JSPs	Oracle ADF + ADF Java Server Faces
Portal	Oracle Portal	Oracle Webcenter
Data Model	Oracle eBS Data Model	Oracle eBS Data Model + Siebel + PoepleSoft (Trees, Data Effectivity, person Model)
Attachments/Imaging	BLOBs	UCM/Stellant
Workflow/Approval	PL/SQL	BPEL
Reports	Reports (11i), Discoverer	BI Publisher
Analytics	Discoverer	OBIEE
Financial Reporting	Financial Statement Generator	Hyperion
Integration	AIA	AIA + BPEL + More web services
XML Gateway	XML Gateway	BPEL, B2B Adapter