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

 

Script to assign a concurrent program to a request group from backend

DECLARE
  l_program_short_name  VARCHAR2 (200);
  l_program_application VARCHAR2 (200);
  l_request_group       VARCHAR2 (200);
  l_group_application   VARCHAR2 (200);
  l_check               VARCHAR2 (2);
BEGIN
  --
  L_PROGRAM_SHORT_NAME := '<CONCURRENT PROGRAM SHORTNAME>';
  L_REQUEST_GROUP := '<REQUEST GROUP NAME>';
  begin
  --Query to find application of request group
    SELECT APPLICATION_NAME
    INTO l_group_application
    FROM FND_APPLICATION_TL FA,
      FND_REQUEST_GROUPS FRG
    WHERE FA.APPLICATION_ID   =FRG.APPLICATION_ID
    and FRG.REQUEST_GROUP_NAME=L_REQUEST_GROUP;
    --Query to find application of concurrent program
    SELECT APPLICATION_NAME
    INTO l_program_application
    FROM FND_CONCURRENT_PROGRAMS FC,
      FND_APPLICATION_TL FA
    WHERE FA.APPLICATION_ID       =FC.APPLICATION_ID
    and FC.CONCURRENT_PROGRAM_NAME= L_PROGRAM_SHORT_NAME ;
 --   query to check if concurrent program already assigned to request group
    SELECT 'Y'
    INTO l_check
    FROM fnd_request_groups frg,
      FND_REQUEST_GROUP_UNITS FRGU,
      fnd_concurrent_programs fcp
    WHERE frg.request_group_id      = frgu.request_group_id
    AND frg.application_id          = frgu.application_id
    AND frgu.request_unit_id        = fcp.concurrent_program_id
    AND frgu.unit_application_id    = fcp.application_id
    AND fcp.concurrent_program_name = L_PROGRAM_SHORT_NAME
    AND FRG.REQUEST_GROUP_NAME      = L_REQUEST_GROUP ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    l_check:='N';
  WHEN OTHERS THEN
    dbms_output.put_line ('Adding Concurrent Program to Request Group Failed: '|| SQLERRM);
  END;
  IF(l_check <> 'Y') THEN
    apps.fnd_program.add_to_group (program_short_name => l_program_short_name, program_application => l_program_application, request_group => l_request_group, group_application => l_group_application );
    dbms_output.put_line ('Program assigned to request group: '|| l_request_group);
  ELSE
    dbms_output.put_line ('Program already assigned to request group: '|| l_request_group);
  END IF;
  --
  COMMIT;
END;

Converting rows into column using pivot function

we can convert row into columns using pivot function
let us consider the following data as sample data

DEPTNO	ENAME	JOB
10	ALLEN	ANALYST
10	JONES	CLERK
10	FORD	MANAGER
10	ABCD	PRESIDENT
10	CLARK	SALESMAN
20	MILLER	ANALYST
20	SMITH	CLERK
20	WARD	MANAGER
20	efgh	PRESIDENT
20	MARTIN	SALESMAN
30	SCOTT	ANALYST
30	TURNER	CLERK
30	ADAMS	MANAGER
30	BLAKE	PRESIDENT
30	KING	SALESMAN

now create a table as emp_jobs with this data


using pivot function
we are converting rows into columns

select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));

the output is in the form

DEPTNO	PRESIDENT'	MANAGER'	CLERK'	ANALYST'	SALESMAN'
20	efgh	WARD	SMITH	MILLER	MARTIN
10	ABCD	FORD	JONES	ALLEN	CLARK
30	BLAKE	ADAMS	TURNER	SCOTT	KING

but while using pivot function aggregate function should be used 

we can also achieve this conversion without using pivot function using this sql query

 select
   a1.deptno,
   a1.ename  AS PRESIDENT,
   a2.ename AS MANAGER,
   a3.ename AS CLERK
from
   emp_jobs  a1,
   emp_jobs a2,
   emp_jobs  a3
   where
   a1.deptno=a2.deptno
   and a2.deptno=a3.deptno
   and a3.deptno=a1.deptno
   and a1.job = 'PRESIDENT'
   and a2.job = 'MANAGER'
   and a3.job ='CLERK';