Various techniques to store Debug log message to handle Errors - Part 3

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I have used debug file generation technique which generates one common log file for debug messages with time stamp. Which help us to debug errors in code or we can also track code execution flow.

Example:

In this example we have created one common procedure which appends all text messages (debug Message) to common log file which is placed in our server (location of file and name are predefined in common procedure). Main motive of common procedure is to open file and append log messages. We can call this procedure from any package to append log messages, we have to pass only log message.It will automatically add times tamp and message will get append to log file.

Create directory

SQL> CREATE DIRECTORY Test_dir AS '/appl/gl/user'';

SQL> GRANT READ ON DIRECTORY Test_dir TO PUBLIC;

Executable

CREATE OR REPLACE PROCEDURE xx_test_debug_message (p_str VARCHAR2)

AS

v_file_type     UTL_FILE.FILE_TYPE;

v_file_path        VARCHAR2(100);

v_file_name      VARCHAR2(30) := ‘TEST_DEBUG_LOG’

intval    BINARY_INTEGER;

strval    VARCHAR2 (256);

paramtype        BINARY_INTEGER;

BEGIN

paramtype := DBMS_UTILITY.get_parameter_value ('utl_file_dir', intval, strval);

g_file_path := strval;

v_file_type := UTL_FILE.FOPEN('v_file_path','v_file_name','a');      

UTL_FILE.PUT_LINE(v_file_type,TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'||':'|| p_str));      

UTL_FILE.FCLOSE(v_file_type);

EXCEPTION     

WHEN OTHERS THEN          

DBMS_OUTPUT.PUT_LINE ('ERROR ' || TO_CHAR (SQLCODE) || SQLERRM);          

NULL; 

END XX_TEST_DEBUG_MESSAGE; 

Testing

Declare

l_test VARCHAR2 (30):='test data';

BEGIN

xx_test_debug_message (l_test);

END;

(Note: This is very basic example which can be enhanced as per business requirements for e.g. you can set some debug level and control these messages.)

Various techniques to store Debug log message to handle Errors - Part 2

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I am using one common framework to handle errors as well as progress of execution flow. To achieve this functionality I have created three custom tables one common package which is used across the instance.

Below are the Required Database Objects:

Error Handling Tables:

xx_process_log:

This table is used to track the progress of Procedure/Function execution flow. This is first table where data is inserted having statuses like‘Start’ and after execution ‘Complete’ Or ‘Error’.

It has one unique message id which is shared by other two tables to link messages i.e. for unique reference.

xx_message_log:

This Table is used to store random user friendly messages like DBMS_OUTPUT.PUT_LINE. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

xx_error_log:

This Table is used to store standard Errors i.e. exception message with sqlcode and sqlerrm. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

Common Package:

xx_common_error_handle:

This package is used to Store logic for Inserting data into three error handling tables. It has three separate procedures to insert data into xx_process_log,xx_message_log and, xx_error_log.

Mandatory Common Procedure in all packages where we have to use this Framework:

xx_process_track_log:

This Procedure is common and mandatory in all Packages where we have to use Error handling framework. This is called by each Procedure or Function at start and at end to update the record into table xx_process_log.

When first time we called, it will insert details (as per your requirement you can keep in parameter) in table xx_process_log with status ‘Start’ and it has one sequence which generate unique message id and store in table. Also this message id is required OUT parameter, as we will use this message id as a reference while inserting data into table’s xx_message_log and, xx_error_log.

(Note: This is very basic example which can be enhanced as per business requirements for

e.g.

a) You can use some profile options or lookup to control these messages.

b) You can send email notification when error occurs

c) You can send data to dashboard for display using business events.

)

Various techniques to store Debug log message to handle Errors - Part 1

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I have created Error table, sequence (to generate unique message id) and one procedure. This procedure is used to insert the log messages into error table. So whenever we have to capture some debug message (user define) we can call this procedure and this will log messages in error table.

Create Sequence

CREATE sequence xx_log_mesg_sminvalue 1 start with 1 increment BY 1 NOCACHE;

Create table

CREATE TABLE xx_error_message

  (

Text_message  VARCHAR2(100),

Mesaage_id    NUMBER,

Cretion_date DATE   

  );

Executable:

CREATE OR REPLACE PROCEDURE XX_log_message(p_str VARCHAR2)

AS

BEGIN

  INSERT

  INTO xx_error_message

  (      Text_message,

Mesaage_id,

Cretion_date    )

    VALUES

( p_str ,

xx_log_mesg_s.nextval ,

      SYSDATE    );

  COMMIT;

END XX_log_message;

Testing

DECLARE

  l_test VARCHAR2(30) :='test data';

BEGIN

XX_log_message(l_test);

END;

How to get Called and Calling Procedure name?

Sometimes we got requirement where we required few parameters like Owner name, calling procedure name, and Called procedure name.

In such scenario oracle provide some standard methods by using which we can achieve the functionality.

Below are the two ways I have used to fetch called procedure name and calling procedure name.

Method 1: Using OWA_UTIL.WHO_CALLED_ME

Method 2: Using $$PLSQL_UNIT

Example:

CREATE OR REPLACE PROCEDURE XX_CALLED

as

l_owner  varchar2(20);

l_name  varchar2(20);

l_lineno number;

l_callervarchar2(20);

BEGIN

OWA_UTIL.WHO_CALLED_ME (l_owner, l_name,l_lineno,l_caller);

dbms_output.put_line('You are called by '||'owner   '||l_owner||

' Calling Object '||l_name||' Line no '||l_lineno||' Caller Type '||l_caller  );

dbms_output.put_line('Calling Object '|| $$PLSQL_UNIT);

END XX_CALLED;

Testing

BEGIN

XX_CALLED;

END;

Update Sales Order Price list and Add required Modifier to Line level using standard API

Below anonymous block will update Sales order Price list and add 
Modifier (as per Business Requirement ) in Order Management.
(Note: Change values as per your requirement also ON DBMS Output window to see DBMS Output message.)

DECLARE  

  l_api_version_number NUMBER := 1;  

  l_return_status      VARCHAR2(2000);  

  l_msg_count          NUMBER;  

  l_msg_data           VARCHAR2(2000);  

  -- PARAMETERS  

  l_debug_level NUMBER := 5;   -- OM DEBUG LEVEL (MAX 5)  

  l_org         NUMBER := 194; -- 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; 

  lt_line_adj_tbl OE_ORDER_PUB.Line_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', l_org);  

  mo_global.init('ONT');  

  -- INITIALIZE DEBUG INFO  

  IF (l_debug_level > 0) THEN  

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

    oe_debug_pub.initialize;  

    oe_debug_pub.setdebuglevel(l_debug_level);  

    Oe_Msg_Pub.initialize;  

  END IF;  

  -- INITIALIZE ENVIRONMENT  

  fnd_global.apps_initialize (user_id => 2106, resp_id => 50663, 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_UPDATE;

 l_header_rec.header_id := 71477;

   

    -- Indexation Applicability  

  -- INITIALIZE ACTION REQUEST RECORD  

  l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;

  l_action_request_tbl(1).request_type := oe_globals.g_book_order;

  l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;

  -- INITIALIZE LINE RECORD  

  l_line_tbl(1)                   := OE_ORDER_PUB.G_MISS_LINE_REC;  

  l_line_tbl(1).operation         := OE_GLOBALS.G_OPR_UPDATE; -- Mandatory Operation to Pass 

  l_line_tbl(1).line_id := 240148;

  -- INITIALIZE PRICE ADJUSTMENT RECORD

  lt_line_adj_tbl(1)                     := OE_ORDER_PUB.G_MISS_LINE_ADJ_REC;

  lt_line_adj_tbl(1).operation           := OE_GLOBALS.G_OPR_CREATE;

  lt_line_adj_tbl(1).applied_flag       := 'Y';  

  lt_line_adj_tbl(1).line_index         := 1;

  lt_line_adj_tbl(1).list_header_id     := 6007;  -- Need To change as per business requirement

  lt_line_adj_tbl(1).list_line_id       := 6007;  -- Need To change as per business requirement  

  lt_line_adj_tbl(1).header_id     := 71477;

  lt_line_adj_tbl(1).line_id       := 240148;

  lt_line_adj_tbl(1).updated_flag       := 'Y';

  lt_line_adj_tbl(1).automatic_flag     := 'Y';

  lt_line_adj_tbl(1).operand             := 20;

  lt_line_adj_tbl(1).arithmetic_operator := 'AMT';

    

  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_action_request_tbl => l_action_request_tbl, 

p_line_adj_tbl => lt_line_adj_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  

      IF (l_debug_level > 0) THEN  

        DBMS_OUTPUT.PUT_LINE('Sales Order Successfully Created');  

      END IF;  

      COMMIT;  

    ELSE  

      IF (l_debug_level > 0) THEN  

        DBMS_OUTPUT.PUT_LINE('Failed to Create Sales Order');  

      END IF;  

      ROLLBACK;  

    END IF;  

  END LOOP;   

  -- DISPLAY ERROR MSGS  

  IF (l_debug_level > 0) THEN  

    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 IF;     

END;

Add Short text Attachment to Standard Sales Order on Both Header and line level using API

Below anonymous block will Add Attachment to Sales order header level in Order Management.
(Note:
a ) Change values as per your requirement also ON DBMS Output window to see DBMS Output message.
b) Similarly you Can add attachment on On Line level wit suitable changes in Add_Attachment API. )

DECLARE

  l_rowid rowid;

  l_attached_document_id NUMBER;

  l_document_id          NUMBER;

  l_document_id1         NUMBER;

  l_media_id             NUMBER;

  l_file_id              NUMBER;

  l_category_id          NUMBER                     :=1000881; --1000728; -- Invoice Internal

  l_pk1_value fnd_attached_documents.pk1_value%TYPE := '5';

  l_description fnd_documents_tl.description%TYPE   := 'WHT_Detail';

 -- l_filename fnd_documents_tl.file_name%TYPE        := 'b1.jpg.bmp';

  l_seq_num NUMBER;

  l_blob BLOB;

  l_bfile bfile;

  l_byte          NUMBER;

  l_return_status VARCHAR2(2000);

  l_msg_count     NUMBER(10);

  l_msg_data      VARCHAR2(1000);

  l_debug_file VARCHAR2(200);  

  l_debug_level NUMBER := 5;   -- OM DEBUG LEVEL (MAX 5)  

  l_org         NUMBER := 194; -- OPERATING UNIT 

BEGIN

-- INITIALIZATION REQUIRED FOR R12  

  mo_global.set_policy_context ('S', l_org);  

  mo_global.init('ONT'); 

  -- INITIALIZE DEBUG INFO  

  IF (l_debug_level > 0) THEN  

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

    oe_debug_pub.initialize;  

    oe_debug_pub.setdebuglevel(l_debug_level);  

    Oe_Msg_Pub.initialize;  

  END IF;  

  -- INITIALIZE ENVIRONMENT  

  fnd_global.apps_initialize (user_id => 2106, resp_id => 50663, resp_appl_id => 660);  

mo_global.set_policy_context ('S', 194);  

--- context done ------------

dbms_output.put_line('Before Calling the API to Create_Short_Text_Document' );

Oe_Fnd_Attachments_Pub.Create_Short_Text_Document

(

p_api_version => 1.0,

p_document_text => 'PRPTEST NEW ORDER RECORD',

p_document_category =>l_category_id,

p_document_description =>'PRPTEST NEW desc',--l_doc_description,

p_language =>'AMERICAN',

p_security_type =>4,

p_security_id => NULL,

p_publish_flag =>'Y',

p_usage_type => 'O',

p_start_date_active => SYSDATE,

p_end_date_active => SYSDATE,

p_commit => 'Y',

x_document_id =>l_document_id,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data

);

IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN

dbms_output.put_line('success:');

COMMIT;

ELSIF l_return_status IS NULL THEN

DBMS_OUTPUT.PUT_LINE('Status is null');

ELSE

DBMS_OUTPUT.PUT_LINE('failure:'|| l_msg_data ||'v_msg_count'||l_msg_count);

FOR i IN 1 .. l_msg_count

     LOOP

        l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

        dbms_output.put_line( i|| ') '|| l_msg_data);

     END LOOP;    

ROLLBACK;

END IF;

dbms_output.put_line('Before Calling the API to Add_Attachment' );

OE_FND_ATTACHMENTS_PUB.Add_Attachment

(

p_api_version     =>1.0,

p_entity_name     =>'OE_ORDER_HEADERS',

p_pk1_value =>l_pk1_value,

p_document_id     =>l_document_id,               

x_attachment_id =>l_document_id1,

x_return_status =>l_return_status,

x_msg_count =>l_msg_count, 

x_msg_data =>l_msg_data

);

IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN

dbms_output.put_line('success:');

COMMIT;

ELSIF l_return_status IS NULL THEN

DBMS_OUTPUT.PUT_LINE('Status is null');

ELSE

DBMS_OUTPUT.PUT_LINE('failure:'|| l_msg_data ||'v_msg_count'||l_msg_count);

FOR i IN 1 .. l_msg_count

     LOOP

        l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

        dbms_output.put_line( i|| ') '|| l_msg_data);

     END LOOP;

    

ROLLBACK;

END IF;

END XXVIS5_PROC;

Add Hold to standard Sales Order Using hod API

Below anonymous block will Add hold to existing Sales order in Order Management.
(Note: Change values as per your requirement also On DBMS Output window to see DBMS Output message)


DECLARE

v_return_status    VARCHAR2(30);
v_msg_data         VARCHAR2(4000);
v_msg_count        NUMBER;
v_hold_source_rec  OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE;
v_hold_id          NUMBER       DEFAULT 2032;
v_hold_entity_code VARCHAR2(10) DEFAULT 'O';
v_header_id        NUMBER       DEFAULT 5;
l_debug_file VARCHAR2(200);
l_debug_level NUMBER := 5;   -- OM DEBUG LEVEL (MAX 5)
  l_org         NUMBER := 194; -- OPERATING UNIT
BEGIN

-- INITIALIZATION REQUIRED FOR R12
  mo_global.set_policy_context ('S', l_org);
  mo_global.init('ONT');

  -- INITIALIZE DEBUG INFO
  IF (l_debug_level > 0) THEN
    l_debug_file   := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
    oe_debug_pub.initialize;
    oe_debug_pub.setdebuglevel(l_debug_level);
    Oe_Msg_Pub.initialize;
  END IF;

  -- INITIALIZE ENVIRONMENT
  fnd_global.apps_initialize (user_id => 2106, resp_id => 50663, resp_appl_id => 660);
mo_global.set_policy_context ('S', 194);
--- context done ------------

v_hold_source_rec                  := OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC;
v_hold_source_rec.hold_id          := v_hold_id;
v_hold_source_rec.hold_entity_code := v_hold_entity_code;
v_hold_source_rec.hold_entity_id   := v_header_id;
v_hold_source_rec.header_id        := v_header_id;
v_return_status                    := NULL;
v_msg_data                         := NULL;
v_msg_count                        := NULL;

dbms_output.put_line('Calling the API to Apply hold' );

OE_HOLDS_PUB.APPLY_HOLDS (
                       p_api_version     => 1.0,
                       p_init_msg_list   => FND_API.G_TRUE,
                       p_commit          => FND_API.G_FALSE,
                       p_hold_source_rec => v_hold_source_rec,
                       x_return_status   => v_return_status,
                       x_msg_count       => v_msg_count,
                       x_msg_data        => v_msg_data
                         );


IF v_return_status = FND_API.G_RET_STS_SUCCESS THEN
dbms_output.put_line('success:');
COMMIT;
ELSIF v_return_status IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Status is null');
ELSE
DBMS_OUTPUT.PUT_LINE('failure:'|| v_msg_data ||'v_msg_count'||v_msg_count);

FOR i IN 1 .. v_msg_count
     LOOP
        v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
        dbms_output.put_line( i|| ') '|| v_msg_data);
     END LOOP;
 
ROLLBACK;
END IF;

EXCEPTION
WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Error : '||SQLCODE||'---'||SQLERRM);
END;

Create Standard Sales Order using API

Below anonymous block will create Sales order in Order Management.
(Note: Change values as per your requirement also On DBMS Output window to see DBMS Output message)

DECLARE
  l_api_version_number NUMBER := 1;
  l_return_status      VARCHAR2(2000);
  l_msg_count          NUMBER;
  l_msg_data           VARCHAR2(2000);
  -- PARAMETERS
  l_debug_level NUMBER := 5;   -- OM DEBUG LEVEL (MAX 5)
  l_org         NUMBER := 194; -- 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;
  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', l_org);
  mo_global.init('ONT');
  -- INITIALIZE DEBUG INFO
  IF (l_debug_level > 0) THEN
    l_debug_file   := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
    oe_debug_pub.initialize;
    oe_debug_pub.setdebuglevel(l_debug_level);
    Oe_Msg_Pub.initialize;
  END IF;
  -- INITIALIZE ENVIRONMENT
  fnd_global.apps_initialize (user_id => 2106, resp_id => 50663, 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.orig_sys_document_ref   := 'OE_ORDER_HEADERS_PRPTEST';
  l_header_rec.TRANSACTIONAL_CURR_CODE := 'USD';
  l_header_rec.pricing_date            := SYSDATE;
  l_header_rec.cust_po_number          := NUll;
  l_header_rec.sold_to_org_id          := 25074;
  l_header_rec.price_list_id           := 6015;
  l_header_rec.ordered_date            := SYSDATE;
  l_header_rec.shipping_method_code    := NULL;
  l_header_rec.sold_from_org_id        := 194;
  l_header_rec.ship_from_org_id        := 174;
  l_header_rec.ship_to_org_id          := 4926;
  l_header_rec.salesrep_id             := -3;
  l_header_rec.flow_status_code        :='ENTERED';
  l_header_rec.order_type_id           := 1485;
  -- REQUIRED HEADER DFF INFORMATIONS
  l_header_rec.attribute1  :=193;        -- Entering Branch
  l_header_rec.attribute3  := 'Y';       -- Indexation applicable
  l_header_rec.attribute5  := '2.5';     -- Indexation Tolerance percentage
 l_header_rec.attribute7  := 100000045; -- Field Sales representative
  l_header_rec.attribute11 := '100';     -- Indexation Applicability
  -- INITIALIZE ACTION REQUEST RECORD
  l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
  l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
  -- INITIALIZE LINE RECORD
  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 := 524216;
  l_line_tbl(1).ordered_quantity  := 1;
  l_line_tbl(1).ship_from_org_id  := 174;
  l_line_tbl(1).subinventory      := NULL;
  -- REQUIRED LINE DFF INFORMATIONS
  l_line_tbl(1).attribute2  := '20.99998'; -- Gross Margin
  l_line_tbl(1).attribute3  := '101686'; -- Business Cost
  l_line_tbl(1).attribute10 := 'Y';     -- Original Cust Requested Qty
  l_line_tbl(1).attribute11 := '662.772';  -- Baseline Margin
  l_line_tbl(1).attribute16 := 'DBP';      -- Buy Price Basis
  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_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
      IF (l_debug_level > 0) THEN
        DBMS_OUTPUT.PUT_LINE('Sales Order Successfully Created');
      END IF;
      COMMIT;
    ELSE
      IF (l_debug_level > 0) THEN
        DBMS_OUTPUT.PUT_LINE('Failed to Create Sales Order');
      END IF;
      ROLLBACK;
    END IF;
  END LOOP;  
  -- DISPLAY ERROR MSGS
  IF (l_debug_level > 0) THEN
    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 IF;    
END;

Create form having one field with standard calender attached

(Note: Whenever you start developing a new custom form first copy resource folder in your local machine and give reference of resource folder to Oracle Form Builder tool. Also use Template.fmb file as reference for creation of new form and rename this file as per requirement.)

Step 1:

Open Oracle Form Builder tool and open Template.fmb file and rename this file as per our requirement.

Using:  file -> open -> (go to Resource folder and select Template.fmb file)

Step 2:

Create new window by selecting Window and pressing ‘+’ sign.

Similarly create Canvas and assigned this Canvas to window by opening Property palette (You can open Property palette by right clicking on canvas created or by selecting Canvas and pressing ‘F4’ button) and in Window field select your newly created window.

Step 3:

Create Data block by selecting Data block and pressing ‘+’ sign and select ‘build a data block manually’. And create one text item similarly using ‘+’ sign and assign this item to Canvas you created in step 2.

Step 4:

Open property Palette and select list of values as ‘ENABLE_LIST_LAMP’ and save the job.  Right click on Item and select “KEY-LISTVAL” trigger using smart trigger and right “calender.show ();” inside trigger. Also you can set specific Prompt to this item in property palette using Prompt property.

(Using calendar .show we can populate standard calendar define in template .fmb)

Compile and Run Oracle Form on Your Desktop

Compile and Run Oracle Form on Your Desktop:

When we work on Oracle forms Enhancement or new development projects, Most of the time we have to compile form on client server i.e. client application server. And to view the changes we need to login to front end application only.

To learn some small things on oracle form we can develop such form using Oracle form builder and needs to follow the same approach. This will waste lots of time on debugging, to avoid this and save our valuable time we can run and view your form on desktop itself.

Below are to some initial setup we need to perform to view and compile form on your Desktop.

Go to your Oracle home

Forms -> server -> formsweb.cfg

Paste below code at the end of file, to configure your server setting.

[TEST]

baseHTMLJInitiator=basejpi.htm

baseHTMLjinitiator=webutiljini.htm

form=MODULE1.fmx

baseHTMLjpi=webutiljpi.htm

separateFrame=True

Open form builder

Go to

Edit -> Preferences

Go to tab Runtime and define your server and default browser to view Form

For E.g.:

Application server URL: http://LIN07000404.corp.:8889/forms/frmservlet?&config=test&separateFrame=True

Web Browser Location: C:\Program Files\Internet Explorer\iexplore.exe

Save settings using Ok button and try to compile your form on your Machine.

(Note: Make sure before compiling any form on your Desktop first you should "start OC4J Instance".)

Now you can compile and view form on your desktop using Run and debug button provided in oracle form builder.

Commands to DOWNLOAD / UPLOAD ldt files

Commands to DOWNLOAD / UPLOAD ldt files

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

1. Lookups

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

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program

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

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile

-- ---------

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS

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

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"

     

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function

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

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts

-- ---------

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set

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

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template

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

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

Basic Workflow for Leave management:

(A)   Basic Workflow for Leave management:

This section leads you through the creation of a test workflow process definition. The values you are instructed to use for Internal Names and Display Names are suggestions for the purpose of consistent references throughout the exercise. You can use different Internal or Display Names. However, Internal Names must have no spaces or colons (:) within them.  Also, an internal name cannot be changed after the object is created.

On the Windows client PC where The Builder has been installed:

Example

In this example a workflow demonstrating the approval process of leave request submitted by an employee has been created. (This is basic flow to understand the Workflow concepts for Beginner.)

Steps of creating The Workflow

    1.  Open Workflow Builder

            Select Start --> Programs --> Oracle for Windows --> Oracle Workflow Builder

    2.  Create a data store (or workspace) for your new process definition with the label 'Untitled'

            Select File --> New

    3.  Copy the Standard and system error Item Type into your new data store.

a.     Open WFSTD.WFT and WFERROR.WFT Files from your workflow home.

            Select File --> New -> Open -> Browse -> <Oracle Workflow Home>-> WF -> Data -> US

b.    Copy Standard and system error Item Type from Respective WFT files to your untitled

Close your WFT files Except 'Untitled' using

Select File --> Close Store

    4. Create a new item type in your data store.

        a. Right click the 'Untitled' data store, then choose 'New Item Type'

b. This opens a dialog box for the properties of your item type.

On the 'Item Type' tab:

            1. For 'Internal', enter XX_LEAVE

            2. For ''Display Name', enter XX_LEAVE

            3. For Description ‘XX_LEAVE’

            4. Click OK

Item Type Attributes

We accept the Start Date, End date, required approver name and requestor name from the user. So we create item type attributes for these parameters.

        a. Right click the “Attribute” then choose 'New Item Attribute

b. This opens a dialog box for the properties of your item type. 

 On the 'Attribute' tab:

            1. For 'Internal', enter XX_REQUESTOR

            2. For ''Display Name', enter XX_REQUESTOR

            3. For Description ‘XX_REQUESTOR

            4. Click OK

(Note: Similarly create attribute for Approver, Start Date and End Date (Keep Type as Date for start and End date))

Notification

To send message to Approver We have to create message and we need to attach this message to Notification.

1.     Message Creation:

       a. Right click the “Messages” then choose 'New Message

b. This opens a dialog box for the properties of your item type.

 On the ‘Message’ tab:

            1. For 'Internal', enter XX_LEAVE_APPLICATION

            2. For ''Display Name', enter XX_LEAVE_APPLICATION

            3. For Description ‘XX_LEAVE_APPLICATION’

            4. Click OK

On the ‘Body’ tab:

            In text Body Write some Description for Leave application

On the ‘’Result’ tab:

            1. For ''Display Name', enter RESULT

            2. for Description ‘RESULT

            3. For Lookup Type select Approval

            4. Click OK

(Note: You can use Attribute to populate values runtime use &<Attribute_name>)

2.     Notification:

a. Right click the “Notifications” then choose 'New Notification

b. This opens a dialog box for the properties of your item type

On the ‘Activity’ tab:

            1. For 'Internal', enter XX_LEAVE_NOTI

            2. For ''Display Name', enter XX_LEAVE_NOTI

            3. For Description ‘XX_LEAVE_NOTI

            4. Attached message created in previous step by selecting it from drop down list.

            5. For Result Type Select Approval from lookup list.

            6. Click ‘OK’

On the ‘Node tab:

1.     For Performer

a.     Type ‘Item Attribute’

b.     Value ‘XX_approver’

          2. Click ‘OK’

Now we have to create actual Process:

           

   3.     Process:

a. Right click the “Processes” then choose 'New Process’

b. This opens a dialog box for the properties of your item type

On the ‘Activity’ tab:

            1. For 'Internal', enter XX_LEAVE

            2. For ''Display Name', enter XX_LEAVE

            3. For Description ‘XX_LEAVE’

            4. Click ‘OK’

Build your test workflow process definition:

        a. Expand the 'Standard' item type by clicking the '+' beside it

        b. Under the 'Standard' item type, expand 'Functions' by clicking the '+' beside it

        c. Under 'Functions' for the 'Standard' item type

 1.  Select the 'Start' function, then drag and drop it into the diagram window (Visible when u double click on                                       Process)

            2. Right click on the 'Start' icon in the diagram window and choose properties.  This opens a dialog box for

                The properties

               Of the function

            3. on the Node’ tab:

               For 'Start/End', click the drop-down menu and select 'Start'

            4. Click OK

         d. Under 'Functions' for the 'Standard' item type (Use two End function to end with different result)

            1. Select the 'End' function, then drag and drop it into the diagram window

            2. Right click on the 'End' icon in the diagram window and choose properties.  This opens a dialog box for

                The   properties

               Of the function.

            3. on the ‘Node’ tab:

               For 'Start/End', click the drop-down menu and select 'End'

a.     Click OK

.  Under 'Notifications' for your 'Untitled' item type

               Select the ’ XX_LEAVE_NOTI’, then drag and drop it into the diagram window

         Within the diagram window:

            1. Right-click on the 'Start' icon and drag a line over to the XX_LEAVE_NOTI icon

            2. Right-click on the XX_LEAVE_NOTI icon and drag a line

               Over to the 'End' icon one for Approve and another for reject.

Now compile your Workflow using  button and save it in data Base.