Lookup For Hold Release Reasons In Order Management

 Oracle has provided an OE lookup "RELEASE_REASON" where it stores all the hold release reason codes. 

You can enter your release reasons as well in the lookup.

Query To Find Concurrent Program status and other Details

 SELECT fu.user_name

             requested_by,fu.user_id,

         fcr.request_id,--fcr.concurrent_program_id,

         CASE

             WHEN fcr.phase_code = 'R' AND fcr.status_code = 'R'

             THEN

                 'RUNNING'

             ELSE

                 CASE

                     WHEN fcr.phase_code = 'C' AND fcr.status_code = 'C'

                     THEN

                         'COMPLETED NORMAL'

                     ELSE

                         CASE

                             WHEN     fcr.phase_code = 'C'

                                  AND fcr.status_code = 'G'

                             THEN

                                 'WARNING'

                             ELSE

                                 CASE

                                     WHEN     fcr.phase_code = 'C'

                                          AND fcr.status_code = 'E'

                                     THEN

                                         'ERROR'

                                     ELSE

                                         CASE

                                             WHEN     fcr.phase_code = 'P'

                                                  AND fcr.status_code = 'Q'

                                             THEN

                                                 'PENDING QUEUE INACTIVE MANAGER'

                                             ELSE

                                                 CASE

                                                     WHEN     fcr.phase_code =

                                                              'C'

                                                          AND fcr.status_code =

                                                              'D'

                                                     THEN

                                                         'COMPLETED CANCELLED'

                                                     ELSE

                                                         CASE

                                                             WHEN     fcr.phase_code =

                                                                      'C'

                                                                  AND fcr.status_code =

                                                                      'X'

                                                             THEN

                                                                 'TERMINATED'

                                                             ELSE

                                                                 'CHECK STATUS'

                                                         END

                                                 END

                                         END

                                 END

                         END

                 END

         END

             status,

         parent_request_id,

         fcpv.user_concurrent_program_name,fcpv.concurrent_program_id,

         fcr.argument_text,

         sysdate,

         fcr.requested_start_date,

         TO_CHAR (fcr.actual_start_date, 'DD-MM-RRRR HH:MI:SS PM')

             actual_start_date,

         fcr.actual_completion_date,

         fcr.responsibility_id,

         frv.responsibility_name,

         fcr.phase_code,

         fcr.status_code,

         fcr.completion_text,

         logfile_node_name,

         outfile_node_name,

         printer,

         print_style,

         EMAIL_ADDRESS,

         argument1,

         argument2, argument3, argument4, argument5,argument6,argument7,argument8, argument9, argument10,argument11,argument12

         , fcr.outfile_name

    FROM fnd_concurrent_requests   fcr,

         fnd_responsibility_vl     frv,

         fnd_concurrent_programs_vl fcpv,

         fnd_user                  fu

   WHERE     fcr.concurrent_program_id = fcpv.concurrent_program_id

         AND fcr.requested_by = fu.user_id

         AND (UPPER (fcpv.user_concurrent_program_name) LIKE

                  UPPER ('%<Request_set  OR Concurrent_program Name>%'))

         AND fcr.responsibility_id = frv.responsibility_id

ORDER BY fcr.request_id DESC;

Query to Find Request set Name

SELECT distinct rs.user_request_set_name         "Request Set" 

     , rss.display_sequence             Seq

     , cp.user_concurrent_program_name  "Concurrent Program" 

     , e.execution_file_name 

     , lv.meaning                       file_type, rs.*

  FROM apps.fnd_request_sets_vl         rs 

     , apps.fnd_req_set_stages_form_v   rss 

     , applsys.fnd_request_set_programs rsp

     , apps.fnd_concurrent_programs_vl  cp

     , apps.fnd_executables             e

     , apps.fnd_lookup_values           lv

 WHERE 1 = 1 

   AND rs.application_id             = rss.set_application_id 

   AND rs.request_set_id             = rss.request_set_id

   AND rss.set_application_id        = rsp.set_application_id

   AND rss.request_set_id            = rsp.request_set_id

   AND rss.request_set_stage_id      = rsp.request_set_stage_id

   AND rsp.program_application_id    = cp.application_id

   AND rsp.concurrent_program_id     = cp.concurrent_program_id

   AND cp.executable_id              = e.executable_id

   AND cp.executable_application_id  = e.application_id

   AND lv.lookup_type                = 'CP_EXECUTION_METHOD_CODE'

   AND lv.lookup_code                = e.execution_method_code

   AND  rs.user_request_set_name = '<request set name>' 

--AND cp.user_concurrent_program_name = <concurrent_program_name>

   AND rs.end_date_active IS NULL

   AND language = 'US'

ORDER BY rss.display_sequence

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;

PL/SQL Record Type And Table Type

In this blog, I will explain about PLSQL Record Type and Table Type With Example.

Please don't forget to leave a comment/suggestion.

PLSQL Record Type

A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row or some columns from a table row. The fields correspond to table columns.

Understanding PL/SQL Records

Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns. Your code keeps working even after columns are added to the table. If you want to represent a subset of columns in a table, or columns from different tables, you can define a view or declare a cursor to select the right columns and do any necessary joins, and then apply %ROWTYPE to the view or cursor.

Defining a Record

The record type can be defined as  below :- 

TYPE

type_name IS RECORD

  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],

   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION],

   ...

   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);

record-name  type_name;

Here "record-name" is object of "type_name". we can not access record type directly with type_name.

Example:- 

DECLARE

TYPE books IS RECORD

(title  varchar(50),

    author  varchar(50),

    subject varchar(100),

    book_id   number);

book1 books;

book2 books;

Assigning Values To PLSQL record Type:-

To access any field of a record we use (.) dot operator. 

SYNTAX:-

<type_name>.<field_name>

Example:-

DECLARE

   type books is record

      (title varchar(50),

       author varchar(50),

       subject varchar(100),

       book_id number);

   book1 books;

   book2 books;

BEGIN

   -- Book 1 specification

   book1.title  := 'C Programming';

   book1.author := 'Nuha Ali '; 

   book1.subject := 'C Programming Tutorial';

   book1.book_id := 6495407;

   -- Book 2 specification

   book2.title := 'Telecom Billing';

   book2.author := 'Zara Ali';

   book2.subject := 'Telecom Billing Tutorial';

   book2.book_id := 6495700;

   -- Print book 1 record

   dbms_output.put_line('Book 1 title : '|| book1.title);

   dbms_output.put_line('Book 1 author : '|| book1.author);

   dbms_output.put_line('Book 1 subject : '|| book1.subject);

   dbms_output.put_line('Book 1 book_id : ' || book1.book_id);

  

   -- Print book 2 record

   dbms_output.put_line('Book 2 title : '|| book2.title);

   dbms_output.put_line('Book 2 author : '|| book2.author);

   dbms_output.put_line('Book 2 subject : '|| book2.subject);

   dbms_output.put_line('Book 2 book_id : '|| book2.book_id);

END;

PLSQL Table Type

Both PLSQL Table Types i.e. Index-by tables and Nested tables have same structure and their rows are accessed using subscript notation.

Index-By Tables :-

Index-By Table is also called Associate Arrary. Index-by table is a set of key value pair, where its subscript is called as key and value assigned to it called as value. Each key in Index-by table is unique and it can either be Number or String.

Syntax :-

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

Declaring and Using Index-By Table:-

DECLARE

     -- Associative array indexed by string:   

     TYPE book IS TABLE OF NUMBER  -- Associative array type

       INDEX BY VARCHAR2(64);   

     book_price  book;        -- Associative array variable

     i                VARCHAR2(64);

   BEGIN

     -- Add new elements to associative array:

     book_price('ABC')  := 200;

     book_price('DEF')     := 750;

     book_price('GHI') := 1000;

     -- Change value associated with key 'ABC':

     book_price('Smallville') := 2001;

     -- Print associative array:

     i := book_price.FIRST;

     WHILE i IS NOT NULL LOOP

       DBMS_Output.PUT_LINE

         ('Price of ' || i || ' is ' || TO_CHAR(book_price(i)));

       i := book_price.NEXT(i);

     END LOOP;

   END;

Nested Tables :-

A nested table is one-dimensional array with arbitrary number of elements whose size can increase dynamically. Nested table are like Index-By Tables, but there is no INDEX BY clause in Nested Tables.

Syntax:-

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array(Index-By table) cannot be stored in the database.

Difference between Nested Table and Array:-

Nested tables are like arrays where in both the case data is stored in one dimension with below differences:-

1. An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
2. An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

Declaring and Using Nested table:-

DECLARE

   TYPE books_table IS TABLE OF VARCHAR2(10);

   TYPE price IS TABLE OF INTEGER;

   names books_table;

   rate price;

   total integer;

BEGIN

   names := books_table('ABC', 'DEF', 'GHI', 'JKL', 'MNO');

   marks:= rate(98, 97, 78, 87, 92);

   total := names.count;

   dbms_output.put_line('Total '|| total || ' books');

   FOR i IN 1 .. total LOOP

      dbms_output.put_line('Book:'||names(i)||', Price:' || rate(i));

   end loop;

END;

Using PLSQL Record Type and Table Type Together

DECLARE

    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;

    

    CURSOR lc_order_cur

    IS

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

        

BEGIN

    OPEN lc_order_cur;

        LOOP

            FETCH lc_order_cur BULK COLLECT INTO lt_order_detail_tab_o;

            EXIT WHEN lc_order_cur%NOTFOUND;

        END LOOP;

    CLOSE lc_order_cur;

    

    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;

        

END;

Kindly leave a comment/suggestion.