Usage Of Ref Cursor

DECLARE
    TYPE lcur_order_details IS REF CURSOR;
    lcur_order_details_rc lcur_order_details;
   
    l_order_number VARCHAR2(50);
BEGIN
    OPEN lcur_order_details_rc FOR SELECT order_number FROM oe_order_headers_all WHERE order_number = 52424905;
        LOOP
            FETCH lcur_order_details_rc INTO l_order_number;
            EXIT WHEN lcur_order_details_rc%NOTFOUND;
        END LOOP;
    CLOSE lcur_order_details_rc;
    dbms_output.put_line('l_order_number :- ' || l_order_number);
END;

-------------------------------------------------------
DECLARE
TYPE lcur_order_details IS REF CURSOR;
lcur_order_details_rc lcur_order_details;

TYPE lr_order_detail_rec IS RECORD
        ( l_order_number oe_order_headers_all.order_number%TYPE,
          l_line_number  VARCHAR2(20),
          l_ordered_item oe_order_lines_all.ordered_item%Type,
          l_line_status  oe_order_lines_all.flow_status_code%TYPE,
          l_order_status oe_order_headers_all.flow_status_code%TYPE);
   
    TYPE lt_order_detail_tab IS TABLE OF lr_order_detail_rec
        INDEX BY BINARY_INTEGER;
   
    lt_order_detail_tab_o     lt_order_detail_tab;

l_order_number VARCHAR2(50);
BEGIN
OPEN lcur_order_details_rc FOR SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,
 oola.shipment_number,
 oola.option_number,
 oola.component_number,
 oola.service_number)
line_num,
oola.ordered_item,
oola.flow_status_code,
ooha.flow_status_code
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND ooha.order_number = '52424905';
LOOP
FETCH lcur_order_details_rc BULK COLLECT INTO lt_order_detail_tab_o;
EXIT WHEN lcur_order_details_rc%NOTFOUND;
END LOOP;
CLOSE lcur_order_details_rc;
dbms_output.put_line('lt_order_detail_tab_o.COUNT :- ' || lt_order_detail_tab_o.COUNT);
END;

Query to find executable file details of concurrent program by passing the concurrent prorgam name as input

As part of supporting Oracle daily, sometimes we have to write certain queries multiple times in order to find the details. One such query is to find the concurrent program details like its executable file name. In order to reduce this tedious task, please use below query and keep it handy.

SELECT user_concurrent_program_name, description, concurrent_program_name,EXECUTION_FILE_NAME , 
fe.EXECUTION_METHOD_CODE, fcp.*
  FROM fnd_concurrent_programs_vl fcp, fnd_executables fe
 WHERE fcp.executable_id = fe.executable_id
    AND fcp.enabled_flag = 'Y'
--and UPPER(execution_file_name) LIKE  'XX%'  -- pass the file name here
and  UPPER(user_concurrent_program_name) LIKE 
    UPPER( '<Pass the concurrent program here>')
--and concurrent_program_id = 32766
--and concurrent_program_name = '<pass the concurrent prorgam short name here>'
;

Kindly comment if you like it :)

API to Delete Sales Order Through backend

DECLARE
    v_api_version_number           NUMBER := 1;
    v_return_status                VARCHAR2 (2000);
    v_msg_count                    NUMBER;
    v_msg_data                     VARCHAR2 (2000);

    -- IN Variables --
    v_header_rec                   oe_order_pub.header_rec_type;
    v_line_tbl                     oe_order_pub.line_tbl_type;
    v_action_request_tbl           oe_order_pub.request_tbl_type;
    v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

    -- OUT Variables --
    v_header_rec_out               oe_order_pub.header_rec_type;
    v_header_val_rec_out           oe_order_pub.header_val_rec_type;
    v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
    v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
    v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
    v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
    v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
    v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
    v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
    v_line_tbl_out                 oe_order_pub.line_tbl_type;
    v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
    v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
    v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
    v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
    v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
    v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
    v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
    v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
    v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
    v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
    v_action_request_tbl_out       oe_order_pub.request_tbl_type;
BEGIN
    DBMS_OUTPUT.PUT_LINE ('Starting of script');

    -- Setting the Environment --

    mo_global.init ('ONT');
    fnd_global.apps_initialize (user_id        => 1234,
                                resp_id        => 2234,
                                resp_appl_id   => 660);
    mo_global.set_policy_context ('S', 4150);

    -- Header Record --

    FOR l_header_id IN (SELECT header_id
                          FROM oe_order_headers_all a
                         WHERE     order_number IN (3761124)
                               AND a.flow_status_code = 'ENTERED'
   AND org_id = 4150)
    LOOP
        v_header_rec := oe_order_pub.g_miss_header_rec;
        v_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
        v_header_rec.header_id := l_header_id.header_id;           --14997384;


        DBMS_OUTPUT.PUT_LINE ('Starting of API');

        -- CALLING THE API TO DELETE AN ORDER --

        OE_ORDER_PUB.PROCESS_ORDER (
            p_api_version_number       => v_api_version_number,
            p_header_rec               => v_header_rec,
            p_line_tbl                 => v_line_tbl,
            p_action_request_tbl       => v_action_request_tbl,
            p_line_adj_tbl             => v_line_adj_tbl      -- OUT variables
                                                        ,
            x_header_rec               => v_header_rec_out,
            x_header_val_rec           => v_header_val_rec_out,
            x_header_adj_tbl           => v_header_adj_tbl_out,
            x_header_adj_val_tbl       => v_header_adj_val_tbl_out,
            x_header_price_att_tbl     => v_header_price_att_tbl_out,
            x_header_adj_att_tbl       => v_header_adj_att_tbl_out,
            x_header_adj_assoc_tbl     => v_header_adj_assoc_tbl_out,
            x_header_scredit_tbl       => v_header_scredit_tbl_out,
            x_header_scredit_val_tbl   => v_header_scredit_val_tbl_out,
            x_line_tbl                 => v_line_tbl_out,
            x_line_val_tbl             => v_line_val_tbl_out,
            x_line_adj_tbl             => v_line_adj_tbl_out,
            x_line_adj_val_tbl         => v_line_adj_val_tbl_out,
            x_line_price_att_tbl       => v_line_price_att_tbl_out,
            x_line_adj_att_tbl         => v_line_adj_att_tbl_out,
            x_line_adj_assoc_tbl       => v_line_adj_assoc_tbl_out,
            x_line_scredit_tbl         => v_line_scredit_tbl_out,
            x_line_scredit_val_tbl     => v_line_scredit_val_tbl_out,
            x_lot_serial_tbl           => v_lot_serial_tbl_out,
            x_lot_serial_val_tbl       => v_lot_serial_val_tbl_out,
            x_action_request_tbl       => v_action_request_tbl_out,
            x_return_status            => v_return_status,
            x_msg_count                => v_msg_count,
            x_msg_data                 => v_msg_data);

        DBMS_OUTPUT.PUT_LINE ('Completion of API');


        IF v_return_status = fnd_api.g_ret_sts_success
        THEN
            COMMIT;
            DBMS_OUTPUT.put_line (
                'Order Deletion Success : ' || v_header_rec_out.header_id);
        ELSE
            DBMS_OUTPUT.put_line ('Order Deletion failed:' || v_msg_data);
            ROLLBACK;

            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;
        END IF;
    END LOOP;
END;
/

Query To Find Delivery Details For Sales Order

select ooha.order_number,wdd.source_header_id, source_line_id,oe_flex_util.get_concat_value (oola.line_number,
                                          oola.shipment_number,
                                          oola.option_number,
                                          oola.component_number,
                                          oola.service_number)
                                 line_num, wdd.released_status, wdd.delivery_detail_id, wda.delivery_id , oola.actual_shipment_date, wdd.date_scheduled--,wnd.*
  from oe_order_headers_all ooha, oe_order_lines_all oola, wsh_delivery_details wdd,  wsh_delivery_assignments wda, wsh_new_deliveries wnd
  where ooha.header_id = oola.header_id
  and oola.line_id = wdd.source_line_id
  and wdd.delivery_detail_id = wda.delivery_detail_id
    and wda.delivery_id = wnd.delivery_id(+)
and ooha.order_number =52426574 

and ooha.org_id  = 123

Usage Of Execute Immediate

DECLARE
    l_query VARCHAR2(1000);
    l_order_number VARCHAR2(50) := '52424905';
   
    TYPE lr_order_detail_rec IS RECORD
        ( l_order_number oe_order_headers_all.order_number%TYPE,
          l_line_number  VARCHAR2(30),
          l_ordered_item oe_order_lines_all.ordered_item%Type,
          l_line_status  oe_order_lines_all.flow_status_code%TYPE,
          l_order_status oe_order_headers_all.flow_status_code%TYPE);
   
    TYPE lt_order_detail_tab IS TABLE OF lr_order_detail_rec
        INDEX BY BINARY_INTEGER;
   
    lt_order_detail_tab_o     lt_order_detail_tab;
BEGIN
    l_query := 'SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,
                                          oola.shipment_number,
                                          oola.option_number,
                                          oola.component_number,
                                          oola.service_number)
                                 line_num,
                oola.ordered_item,
                oola.flow_status_code,
                ooha.flow_status_code
        FROM oe_order_headers_all ooha, oe_order_lines_all oola
        WHERE ooha.header_id = oola.header_id
            AND ooha.order_number = ' ||  l_order_number;
   
    BEGIN
        EXECUTE IMMEDIATE l_query
        BULK COLLECT INTO lt_order_detail_tab_o;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Inside exception ' || SQLERRM);
    END;
   
    dbms_output.put_line('lt_order_detail_tab_o.COUNT :- ' || lt_order_detail_tab_o.COUNT);
    dbms_output.put_line('Order Number || Line Number || ordered Item || Order Status || Line Status ' );
   
    FOR i IN lt_order_detail_tab_o.FIRST..lt_order_detail_tab_o.LAST
    LOOP
        dbms_output.put_line( lt_order_detail_tab_o(i).l_order_number|| ' '||
                                lt_order_detail_tab_o(i).l_line_number || ' ' ||
                                lt_order_detail_tab_o(i).l_ordered_item ||' '||
                                lt_order_detail_tab_o(i).l_order_status ||' ' ||
                                lt_order_detail_tab_o(i).l_line_status);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Inside exception when others' || SQLERRM);
END;

API To Create Relationship Of Line With CONFIG Line Instance

To create relationship API CSI_II_RELATIONSHIPS_PUB.CREATE_RELATIONSHIP will be used as :-

DECLARE

V_RELATIONSHIP_ID       NUMBER;

X_RELATIONSHIP_TBL      CSI_DATASTRUCTURES_PUB.II_RELATIONSHIP_tbl;

X2_TXN_REC              CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;

P2_COMMIT               VARCHAR2 (5);

P2_INIT_MSG_LST         VARCHAR2 (500);

P2_VALIDATION_LEVEL     NUMBER;

x2_return_status        VARCHAR2 (100);

x2_msg_count            NUMBER;

x2_msg_data             VARCHAR2 (2000);

l_object_id             NUMBER ;

l_subject_id            NUMBER ;

l_transaction_type_id   NUMBER ;

BEGIN

BEGIN

SELECT csi_ii_relationships_s.NEXTVAL

INTO V_RELATIONSHIP_ID

FROM DUAL;

EXCEPTION

WHEN OTHERS

THEN

V_RELATIONSHIP_ID := NULL;

END;

BEGIN

SELECT line_type_id

INTO l_transaction_type_id

FROM oe_order_lines_all 

WHERE line_id = <LINE_ID>;

EXCEPTION

WHEN OTHERS THEN

l_transaction_type_id := NULL;

END;

BEGIN

SELECT cii.instance_id

INTO l_object_id -- parent

FROM oe_order_lines_all oola, csi_item_instances cii

WHERE oola.line_id = cii.last_oe_order_line_id

AND oola.inventory_item_id = cii.inventory_item_id

AND oola.item_type_code = 'CONFIG'

AND oola.line_id = <LINE_ID>;

EXCEPTION

WHEN OTHERS THEN

l_object_id := NULL;

END;

BEGIN

SELECT cii.instance_id

INTO l_subject_id -- child

FROM oe_order_lines_all oola, csi_item_instances cii

WHERE oola.line_id = cii.last_oe_order_line_id

AND oola.inventory_item_id = cii.inventory_item_id

AND oola.line_id = <LINE_ID>;

EXCEPTION

WHEN OTHERS THEN

l_subject_id := NULL;

END;

X_RELATIONSHIP_TBL (1).RELATIONSHIP_ID := V_RELATIONSHIP_ID;

X_RELATIONSHIP_TBL (1).RELATIONSHIP_TYPE_CODE := 'COMPONENT-OF';

X_RELATIONSHIP_TBL (1).OBJECT_ID := l_object_id;             --22062065;

X_RELATIONSHIP_TBL (1).SUBJECT_ID := l_subject_id;           --33531940;

X_RELATIONSHIP_TBL (1).SUBJECT_HAS_CHILD := 'N';

X_RELATIONSHIP_TBL (1).POSITION_REFERENCE := NULL;

X_RELATIONSHIP_TBL (1).ACTIVE_START_DATE := SYSDATE;

X_RELATIONSHIP_TBL (1).ACTIVE_END_DATE := '';

X_RELATIONSHIP_TBL (1).DISPLAY_ORDER := NULL;

X_RELATIONSHIP_TBL (1).MANDATORY_FLAG := 'N';

X_RELATIONSHIP_TBL (1).CONTEXT := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE1 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE2 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE3 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE4 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE5 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE6 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE7 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE8 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE9 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE10 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE11 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE12 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE13 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE14 := NULL;

X_RELATIONSHIP_TBL (1).ATTRIBUTE15 := NULL;

X_RELATIONSHIP_TBL (1).OBJECT_VERSION_NUMBER := 1;

X2_TXN_REC.TRANSACTION_DATE := TRUNC (SYSDATE);

X2_TXN_REC.SOURCE_TRANSACTION_DATE := TRUNC (SYSDATE);

X2_TXN_REC.TRANSACTION_TYPE_ID := l_transaction_type_id;         --1003;

X2_TXN_REC.OBJECT_VERSION_NUMBER := 1;

--

CSI_II_RELATIONSHIPS_PUB.CREATE_RELATIONSHIP (

p_api_version        => 1.0,

p_commit             => P2_COMMIT,

p_init_msg_list      => P2_INIT_MSG_LST,

p_validation_level   => P2_VALIDATION_LEVEL,

p_relationship_tbl   => X_RELATIONSHIP_TBL,

p_txn_rec            => X2_TXN_REC,

x_return_status      => X2_RETURN_STATUS,

x_msg_count          => X2_MSG_COUNT,

x_msg_data           => X2_MSG_DATA);

END;      

API To Create Instance manually in Install Base

We can create instance manually using API CSI_ITEM_INSTANCE_PUB.CREATE_ITEM_INSTANCE.

Application of API:-

DECLARE
l_instance_rec            csi_datastructures_pub.instance_rec;
l_ext_attrib_values_tbl   csi_datastructures_pub.extend_attrib_values_tbl;
l_party_tbl               csi_datastructures_pub.party_tbl;
l_party_account_tbl       csi_datastructures_pub.party_account_tbl;
l_pricing_attribs_tbl     csi_datastructures_pub.pricing_attribs_tbl;
l_org_assignments_tbl     csi_datastructures_pub.organization_units_tbl;
l_asset_assignment_tbl    csi_datastructures_pub.instance_asset_tbl;
l_txn_rec                 csi_datastructures_pub.transaction_rec;
x_instance_id_lst         csi_datastructures_pub.id_tbl;

lr_party_rec              csi_datastructures_pub.party_rec;
lr_party_account_rec      csi_datastructures_pub.party_account_rec;
lr_ext_attrib_value_rec   csi_datastructures_pub.extend_attrib_values_rec;

l_return_status           VARCHAR2 (1);
l_msg_count               NUMBER;
l_msg_data                VARCHAR2 (2000);
l_msg_index_out           VARCHAR2 (100);
l_api_version    CONSTANT NUMBER := 1.0;

l_error_stage             VARCHAR2 (240);
l_start_date              DATE;
l_start_time              DATE;
l_location_id  hz_locations.location_id%TYPE;
l_party_id  hz_parties.party_id%TYPE;
l_cust_account_id  hz_cust_accounts.cust_account_id%TYPE;
BEGIN
BEGIN
SELECT internal_party_id
INTO l_internal_party_id
FROM csi_install_parameters;
EXCEPTION
WHEN OTHERS THEN
l_internal_party_id := NULL;
END;

BEGIN
SELECT hps.location_id
INTO l_location_id
FROM oe_order_lines_all oola,
HZ_CUST_SITE_USES_ALL hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps
WHERE     oola.ship_to_org_id = hcsua.site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND oola.line_id = <LINE_ID>;
EXCEPTION
WHEN OTHERS THEN
l_location_id := NULL;
END;

BEGIN
SELECT party_id, cust_account_id
INTO l_party_id, l_cust_account_id
FROM hz_cust_accounts hca, oe_order_lines_all oola
WHERE hca.cust_account_id = oola.sold_to_org_id
AND oola.line_id = <LINE_ID>;
EXCEPTION
WHEN OTHERS THEN
l_party_id := NULL;
END;

l_instance_rec.serial_number := '';
l_instance_rec.instance_id := NULL;
l_instance_rec.instance_number := NULL;
l_instance_rec.external_reference := '';

l_instance_rec.inventory_item_id :=
<INVENTORY_ITEM_ID>;
l_instance_rec.inv_master_organization_id := <MASTER_ORGANIZATION_ID>;
l_instance_rec.vld_organization_id :=
<SHIP_FROM_ORG_ID of LINE>;
l_instance_rec.LAST_OE_ORDER_LINE_ID :=
<LINE_ID FROM OE_ORDER_LINES_ALL>;
--l_instance_rec.instance_status_id         := 1011;
l_instance_rec.object_version_number := 1.0;
-- l_instance_rec.location_type_code := 'HZ_PARTY_SITES';
l_instance_rec.quantity := <SHIPPED_QUANTITY of LINE_ID>;
l_instance_rec.unit_of_measure :=
<UOM of ORDERED_ITEM LINE_ID>;
l_instance_rec.mfg_serial_number_flag := 'N';
l_instance_rec.version_label := 'AS_CREATED';
-- l_instance_rec.location_id := l_location_id;
l_instance_rec.active_start_date := SYSDATE;
l_instance_rec.install_date := SYSDATE;
l_instance_rec.location_id := l_location_id;
l_instance_rec.owner_party_id := l_party_id;
--l_instance_rec.ACCOUNTING_CLASS_CODE := l_accounting_class_code;--'CUST_PROD';
--l_instance_rec.rec.active_end_date := l_hdr_rec.ib_end_date;

lr_party_rec.party_source_table := 'HZ_PARTIES';
lr_party_rec.instance_id := NULL;
lr_party_rec.relationship_type_code := 'OWNER';
lr_party_rec.party_id := l_party_id;
lr_party_rec.contact_flag := 'N';
l_party_tbl (1) := lr_party_rec;

lr_party_account_rec.relationship_type_code := 'OWNER';
lr_party_account_rec.parent_tbl_index := 1;
lr_party_account_rec.party_account_id := l_cust_account_id;
lr_party_account_rec.instance_party_id := l_party_id;
--lr_party_account_rec.bill_to_address := '';
--lr_party_account_rec.ship_to_address := '';
l_party_account_tbl (1) := lr_party_account_rec;

lr_ext_attrib_value_rec.attribute_value_id := NULL;

l_txn_rec.transaction_date := SYSDATE;
l_txn_rec.source_transaction_date := SYSDATE;
l_txn_rec.transaction_type_id := 1;

l_error_stage :=
'Call API CSI_ITEM_INSTANCE_PUB.CREATE_ITEM_INSTANCE';

l_msg_data := NULL;
l_msg_index_out := NULL;
l_msg_count := NULL;

CSI_ITEM_INSTANCE_PUB.CREATE_ITEM_INSTANCE (
p_api_version             => l_api_version,
p_commit                  => FND_API.G_FALSE,
p_init_msg_list           => FND_API.G_FALSE,
p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
p_instance_rec            => l_instance_rec,
p_ext_attrib_values_tbl   => l_ext_attrib_values_tbl,
p_party_tbl               => l_party_tbl,
p_account_tbl             => l_party_account_tbl,
p_pricing_attrib_tbl      => l_pricing_attribs_tbl,
p_org_assignments_tbl     => l_org_assignments_tbl,
p_asset_assignment_tbl    => l_asset_assignment_tbl,
p_txn_rec                 => l_txn_rec,
x_return_status           => l_return_status,
x_msg_count               => l_msg_count,
x_msg_data                => l_msg_data);

IF (l_return_status IN ('E', 'U'))
THEN
--l_error_code := 'E';
ROLLBACK;
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get (p_msg_index       => i,
p_encoded         => 'F',
p_data            => l_msg_data,
p_msg_index_out   => l_msg_index_out);
dbms_output.put_line (

'l_return_status: ' || l_return_status);
dbms_output.put_line (
 
  'l_msg_data: ' || SUBSTR (l_msg_data, 1, 250));
END LOOP;
dbms_output.put_line( 'Instance creation failed for order# ' || order_rec.order_number || ' Serial Number :- ' || l_serial_number);
ELSE
dbms_output.put_line ('------------------------------------------');
dbms_output.put_line ('l_return_status: ' || l_return_status);
dbms_output.put_line('The instance ID: '
|| TO_CHAR (l_instance_rec.INSTANCE_ID)
|| ' Created For Order# :- ' || order_rec.order_number
|| ' and Serial number :- ' || l_serial_number);
dbms_output.put_line ('------------------------------------------');
COMMIT;
END IF;
END;