How to Get the Active sessions of database in SQL Developer

How to Get the Active sessions of database in SQL Developer

Steps to get the active sessions.

1) Connect to the server through SQL Developer.

2) Click on ‘View’ menu.

3) Select Reports.

4) Open Data Dictionary Reports.

5) Open Database Administration.

6) Open Session.

7) Double-Click on Active Sessions.

Now you will get the list of all active sessions.

           

Steps for Moving the Function from one Responsibility to another.

Steps for Moving the Function from one Responsibility to another.

Step1:-Go to the responsibility where your function is there.

                For ex:- SYN US OM SUPER USER.

           

Step2:-Note down the menu attached to that Responsibility.

                Navigation:-System Administrator-- >Security-- >Responsibility-- >Define.

Step3:-Go to that menu and remove the function from there.

                Navigation:-Application Developer-- >Application-- >Menu

Step4:-Go to the responsibility where u want to put the function.

                Suppose if u want to put this function in SYN US Purchasing

               

Step5:- Note down the menu attached to that Responsibility.

Step6:-Go to the menu and attach the function to that.

Now the function is successfully attached to the new responsibility.

OE_ORDER_PUB.PROCESS_ORDER (Script for Updating Sales Order)

OE_ORDER_PUB.PROCESS_ORDER (Script for Updating Sales Order)

Below is the sample script to update the promise_date of sales order lines.

 set serveroutput on

DECLARE

            l_error             VARCHAR2 (1000);     

            PRAGMA AUTONOMOUS_TRANSACTION;

            line_id NUMBER;

            l_count NUMBER := 0;

            ----for API

            l_archive_file_name VARCHAR2(1000);

            l_shipment_exists NUMBER:=0;

            l_header_id NUMBER;

            l_line_id NUMBER;

            p_header_rec Oe_Order_Pub.Header_Rec_Type;

            l_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

            l_header_rec OE_ORDER_PUB.Header_Rec_Type;

            l_header_val_out_rec OE_ORDER_PUB.Header_Val_Rec_Type;

            l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;

            l_header_adj_val_out_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;

            l_header_price_att_out_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;

            l_header_adj_att_out_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;

            l_header_adj_assoc_out_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;

            l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;

            l_header_scredit_val_out_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;

            l_line_rec OE_ORDER_PUB.line_rec_type;

           

            l_line_out_tbl OE_ORDER_PUB.Line_Tbl_Type;

            l_line_val_out_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;

            l_line_adj_out_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;

            l_line_adj_val_out_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;

            l_line_price_att_out_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;

            l_line_adj_att_out_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;

            l_line_adj_assoc_out_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;

            l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;

            l_line_scredit_val_out_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;

            l_lot_serial_out_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;

            l_lot_serial_val_out_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;

            l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;

            l_msg_count NUMBER;

            l_msg_data VARCHAR2(2000);

            l_return_status VARCHAR2(1);

            l_index NUMBER;

            l_rows_processed NUMBER:=0;

            l_line_mawb VARCHAR2(240);

            l_line_hawb VARCHAR2(240);

            l_line_flight_num VARCHAR2(240);

            l_org_id NUMBER :=FND_GLOBAL.ORG_ID;

            l_resp_id NUMBER:=FND_GLOBAL.RESP_ID;

            l_resp_appl_id NUMBER:=FND_GLOBAL.RESP_APPL_ID;

            l_user_id number := FND_GLOBAL.USER_ID;

            l_promise_date date;

            v_line_count number:=0;

            x_debug_file                   VARCHAR2 (100);

            g_error_message VARCHAR2(2000):= null;

            i number:=0;

BEGIN

          dbms_output.enable(1000000);

          fnd_global.apps_initialize(1318,21623,660); -- pass in user_id, responsibility_id, and application_id

          p_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

          p_header_rec.header_id := l_header_id;

          l_line_tbl := oe_order_pub.g_miss_line_tbl;

            l_line_tbl(i) := oe_order_pub.g_miss_line_rec;

            l_line_tbl(i).header_id := l_header_id;

            l_line_tbl(i).line_id := l_line_id;

            l_line_tbl(i).promise_date := sysdate;     --l_promise_date;

            l_line_tbl(i).operation := oe_globals.g_opr_update;

-- CALL TO PROCESS ORDER

            BEGIN

                        x_debug_file := oe_debug_pub.set_debug_mode ('FILE');

                        oe_debug_pub.setdebuglevel (5);

                        apps.oe_order_pub.process_order

                                                      (p_api_version_number => 1.0

                                                       ,p_init_msg_list => fnd_api.g_true

                                                       ,p_return_values => 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 => p_header_rec

                                                       ,p_line_tbl => l_line_tbl

                                                       -- out variables

                                                       ,x_header_rec => l_header_rec

                                                       ,x_header_val_rec => l_header_val_out_rec

                                                       ,x_header_adj_tbl => l_header_adj_out_tbl

                                                       ,x_header_adj_val_tbl => l_header_adj_val_out_tbl

                                                       ,x_header_price_att_tbl => l_header_price_att_out_tbl

                                                       ,x_header_adj_att_tbl => l_header_adj_att_out_tbl

                                                       ,x_header_adj_assoc_tbl => l_header_adj_assoc_out_tbl

                                                       ,x_header_scredit_tbl => l_header_scredit_out_tbl

                                                       ,x_header_scredit_val_tbl => l_header_scredit_val_out_tbl

                                                       ,x_line_tbl => l_line_out_tbl

                                                       ,x_line_val_tbl => l_line_val_out_tbl

                                                       ,x_line_adj_tbl => l_line_adj_out_tbl

                                                       ,x_line_adj_val_tbl => l_line_adj_val_out_tbl

                                                       ,x_line_price_att_tbl => l_line_price_att_out_tbl

                                                       ,x_line_adj_att_tbl => l_line_adj_att_out_tbl

                                                       ,x_line_adj_assoc_tbl => l_line_adj_assoc_out_tbl

                                                       ,x_line_scredit_tbl => l_line_scredit_out_tbl

                                                       ,x_line_scredit_val_tbl => l_line_scredit_val_out_tbl

                                                      ,x_lot_serial_tbl => l_lot_serial_out_tbl

                                                      ,x_lot_serial_val_tbl => l_lot_serial_val_out_tbl

                                                      ,x_action_request_tbl => l_action_request_out_tbl

                                                       );

        

                                                      g_error_message:='Error in API --->'||SQLERRM;

       

             EXCEPTION when others then

                    g_error_message:='Exception inside order api-'||SQLERRM;

                    null;

            END;

             -- Retrieve messages

            IF l_return_status <> fnd_api.g_ret_sts_success THEN

                ROLLBACK;

                g_error_message:=g_error_message||'Error updating SO line attributes-';

            END IF;

 EXCEPTION     

      WHEN OTHERS

      THEN

         g_error_message := 'Error in process order api' || SQLERRM;

         p_error := g_error_message;

 END;

/

Commit;