How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information (11.5.10+ ) - 467334.1

A. Contract Header Data

This SQL takes data from views rather than from actual contracts tables and is useful for reviewing data but not ideal for verifying if base tables hold correct data.

SELECT contract_number "Contract"

     , TO_CHAR( id ) "Id"

     , short_description "Description"

     , inv_organization_id "Organization Id"

     , sts_code "Status"

     , scs_code

     , currency_code "Currency"

     , TO_CHAR( date_terminated

              , 'DD-MON-YYYY' )

          "Date Terminated"

     , trn_code

     , TO_CHAR( start_date

              , 'DD-MON-YYYY' )

          "Start Date"

     , TO_CHAR( end_date

              , 'DD-MON-YYYY' )

          " End Date"

     , upg_orig_system_ref

     , price_list_id

     , bill_to_site_use_id

     , ship_to_site_use_id

     , payment_term_id

     , acct_rule_id

     , inv_rule_id

     , ar_interface_yn

     , class_meaning

     , template_yn

     , hold_billing

  FROM oks_auth_headers_v

 WHERE contract_number = :p_contract_number;

SELECT *

  FROM okc_k_headers_b

 WHERE contract_number = :p_contract_number

   AND contract_number_modifier = :p_contract_modifier;

B. Contract Line Data 

Note: in OKC_K_LINES_B the chr_id field is only populated with the contract header id for contract lines.  For contract sublines, this value is NULL.  Dnz_chr_id is populated with the contract header id for both lines and sublines.

B1. This SQL takes data from views rather than from actual contracts tables and is useful for reviewing data but not ideal for verifying if base tables hold correct data 

  SELECT DISTINCT oal.line_number

                , oll.lse_name

                , oal.sts_code "Status"

                , oal.trn_code

                , oal.lse_id

                , old.service_name

                , oal.currency_code "Currency|Code"

                , TO_CHAR( oal.start_date

                         , 'DD-MON-YYYY' )

                     "Start Date"

                , TO_CHAR( oal.end_date

                         , 'DD-MON-YYYY' )

                     "End Date"

                , qpl.name "Price List Name"

                , cust_acct_id

                , bill_to_site_use_id

                , inv_rule_id

                , ship_to_site_use_id

                , ship_to_site_use_id

                , acct_rule_id

                , usage_period

                , usage_type

                , uom_quantified

                , billing_schedule_type

                , invoice_text

    FROM oks_auth_lines_v oal

       , okc_launch_lgrid_v oll

       , qp_pricelists_lov_v qpl

       , oks_line_details_v old

   WHERE oal.id = oll.id

     AND cle_id IS NULL

     AND qpl.price_list_id = oal.price_list_id

     AND old.contract_id = oll.chr_id

     AND oll.chr_id = '<value of id taken from query A1>'

ORDER BY TO_NUMBER line_number ;

***********************************************************
B2. Data taken directly from contract table.  (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_b will return more than one contract if the contract has been renewed).

SELECT *

  FROM okc_k_lines_b

 WHERE chr_id IN (SELECT id

                    FROM okc_k_headers_b

                   WHERE contract_number = :p_contract_number);

***********************************************************

C. Contract Subline Data 

Note: When you add a subline to a contract OKC_K_LINES_B is populated with data, some of the data created there for each subline is internal data.  Use the LSE_ID to restrict the data returned when querying.

C1. Query for all the sublines on a contract with a Level type that can be seen when authoring the contract (i.e. restricts to lines which have Level of Product, Site, Item, System, Customer or Site).  (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_b will return more than one contract if the contract has been renewed). 

SELECT id

     , line_number

     , cle_id

     , sts_code

     , hidden_ind

     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"

     , object_version_number

     , price_negotiated

     , price_level_ind

     , price_unit

     , price_unit_percent

     , price_type

     , currency_code

     , price_list_id

     , price_list_line_id

     , item_to_price_yn

     , pricing_date

     , date_terminated

     , start_date

     , end_date

  FROM okc_k_lines_b

 WHERE dnz_chr_id IN (SELECT id

                        FROM okc_k_headers_b

                       WHERE contract_number = :p_contract_number)

   AND lse_id IN (8, 7, 9, 10, 11, 35);

*****************************************************************

C2. Query for contract sublines for a given contract line only. Replace <parent line number> with the line number of the required contract line (e.g. 1, 2. 3), taken either from the contract form, or from query B2.  (Note that this query may appear to return duplicate lines, as the query on okc_k_headers_b will return more than one contract if the contract has been renewed).

SELECT id

     , line_number

     , cle_id

     , sts_code

     , DECODE( lse_id,  8, 'Party',  7, 'Item',  9, 'Product',  10, 'Site',  11, 'System',  35, 'Customer' ) "Level"

     , object_version_number

     , price_negotiated

     , price_level_ind

     , price_unit

     , price_unit_percent

     , price_type

     , currency_code

     , price_list_id

     , price_list_line_id

     , item_to_price_yn

     , pricing_date

     , date_terminated

     , start_date

     , end_date

  FROM okc_k_lines_b

 WHERE dnz_chr_id IN (SELECT id

                        FROM okc_k_headers_b

                       WHERE contract_number = :p_contract_number)

   AND cle_id IN (SELECT id

                    FROM okc_k_lines_b

                   WHERE chr_id IN (SELECT id

                    FROM okc_k_headers_b

                   WHERE contract_number = :p_contract_number)

   AND line_number = :p_parent_line_number)

   AND lse_id IN (8, 7, 9, 10, 11, 35);

***********************************************************

C3. This query returns the inventory item for a given contract subline where the Level = Product (i.e. the subline is for a particular install base instance).

SELECT kl.line_number

     , ks.name

     , i.segment1

  FROM okc_k_headers_b kh

     , okc_k_lines_b kl

     , okc_k_items ki

     , okc_line_styles_v ks

     , csi_item_instances c

     , mtl_system_items_b i

 WHERE kh.contract_number = :p_contract_number

   AND kh.contract_number_modifier IS NULL                                                                 --can be populated

   AND kh.id = kl.dnz_chr_id

   AND kh.id = ki.dnz_chr_id

   AND kl.id = ki.cle_id

   AND kl.lse_id = ks.id

   AND ki.jtot_object1_code IN ('OKX_CUSTPROD')

   AND c.last_vld_organization_id = i.organization_id

   AND TO_NUMBER( ki.object1_id1 ) = c.instance_id

   AND c.inventory_item_id = i.inventory_item_id;

***********************************************************

D. Contract Billing Data

D1. This query shows the billing invoice details. Note that -99 will be shown for invoice number if the 'Service Contracts Fetch Receivables Info For Billing' concurrent program has not been run after Autoinvoice has been run. 

SELECT DISTINCT d.contract_number

              , a.trx_number "Invoice Number"

              , TO_CHAR( b.date_billed_from

                       , 'DD-MON-YYYY HH24-MI' )

                   "Bill From"

              , TO_CHAR( b.date_billed_to

                       , 'DD-MON-YYYY HH24-MI' )

                   "Bill To"

              , b.amount

  FROM oks_bill_transactions a

     , oks_bill_txn_lines aa

     , oks_bill_cont_lines b

     , okc_k_lines_b c

     , okc_k_headers_b d

 WHERE a.id = aa.btn_id

   AND aa.bcl_id = b.id

   AND b.cle_id = c.cle_id

   AND c.dnz_chr_id = d.id

   AND d.id = '<contract id FROM query A1>';

********************************************************** 
D2. This query shows the billing transaction details. The data in this table is shown in the History tab of the Billing Schedule form in the contract.

For the bill_action, the codes have the following meanings:

Regular Invoice -RI,

Termination Credit - TR,

Averaging - AV,

Settlement Invoice - SRI,

Settlement Credit - STR. 

  SELECT hdr.contract_number "Contract"

       , hdr.contract_number_modifier "Modifier"

       , hdr.id

       , TO_CHAR( cont.creation_date

                , 'DD-MON-YYYY HH24:MI' )

            "Creation Date"

       , bill_action

       , btn_id "Billing Transaction ID"

       , amount

       , TO_CHAR( date_billed_from

                , 'DD-MON-YYYY' )

            "Date Billed From"

       , TO_CHAR( date_billed_to

                , 'DD-MON-YYYY' )

            "Date Billed To"

    FROM oks_bill_cont_lines cont

       , okc_k_lines_b line

       , okc_k_headers_b hdr

   WHERE hdr.id = line.dnz_chr_id

     AND cont.cle_id = line.id

     AND hdr.id = '<contract id from query A1>'

ORDER BY cont.creation_date;

*****************************************************************

D3. This query returns data about the contract Line ids corresponding to each invoice as well as invoice details.

SELECT bcl.id

     , bcl.cle_id

     , bcl.btn_id

     , bcl.bill_action

     , okl.id "Line id"

     , okh.id "Contract id"

     , btn.trx_number "Invoice"

     , bcl.date_billed_from

     , bcl.date_billed_to

  FROM oks_bill_cont_lines bcl

     , okc_k_lines_b okl

     , okc_k_headers_b okh

     , oks_bill_transactions btn

     , oks_bill_txn_lines btl

 WHERE okh.contract_number = :p_contract_number

   AND okh.id = okl.dnz_chr_id

   AND okl.cle_id IS NULL

   AND okl.id = bcl.cle_id

   AND btn.id = bcl.btn_id

   AND btl.btn_id = btn.id

   AND btl.bill_instance_number IS NOT NULL;

D4. This query returns information about what the contract billing schedule for a contract and can be used to investigate amounts expected to be billed in a billing period.

SELECT TO_CHAR( bcl.id )

     , TO_CHAR( bsl.id )

     , TO_CHAR( lvl.id )

     , lvl.date_start

     , bsl.date_billed_from

     , lvl.date_end

     , bsl.date_billed_to

     , bcl.date_next_invoice

     , lvl.date_transaction

     , lvl.date_to_interface

     , lvl.date_completed

     , TO_CHAR( rul_id )

     , TO_CHAR( lvl.parent_cle_id )

     , bsl.amount

  FROM oks_bill_sub_lines bsl

     , oks_bill_cont_lines bcl

     , oks_level_elements lvl

     , okc_k_lines_b kl

     , okc_k_headers_b kh

 WHERE kh.contract_number = :p_contract_number

   AND kl.dnz_chr_id = kh.id

   AND lvl.dnz_chr_id = kh.id

   AND bcl.cle_id = kl.id

   AND bcl.id = bsl.bcl_id

   AND lvl.cle_id = bsl.cle_id;

E. Receivables Interface Data

E1. Query to return all the data in the RA interface table for a given service contract.  This will return the data populated into the table by Service Contracts Main Billing.   Note that this query will not return any data if Autoinvoice has been run since the records are deleted from this table once they have been successfully processed by Autoinvoice.

SELECT *

  FROM ra_interface_lines_all

 WHERE sales_order = :p_contract_number;

F. Subscription Contracts

F1.  How to find the install base instance created for the subscription line item.  (Note that when you enter a subscription line, the application automatically creates an Oracle Install Base item instance.  This is what this query is retrieving).

SELECT osh.instance_id

     , okh.contract_number

     , okh.contract_number_modifier

     , okl.line_number

  FROM oks_subscr_header_b osh

     , okc_k_headers_b okh

     , okc_k_lines_b okl

 WHERE osh.dnz_chr_id = okh.id

   AND osh.cle_id = okl.id

   AND okl.chr_id = okh.id

   AND okh.contract_number = :p_contract_number

   AND NVL( okh.contract_number_modifier, '-' ) = NVL( :p_contract_modifier, '-' );

F2. Query to find the install base instances created by a Subscription Contract as a result of subscription fulfillment.

SELECT csi.instance_number

  FROM oks_subscr_elements ose

     , csi_item_instances csi

 WHERE ose.dnz_chr_id IN (SELECT id

                            FROM okc_k_headers_b

                           WHERE contract_number = :p_contract_number

                             AND NVL( contract_number_modifier, '-' ) = NVL( :p_contract_modifier, '-' ))

   AND ose.order_line_id = csi.last_oe_order_line_id;

F3. Query to find which subscription contract line created the install base instance, for a subscription fulfillment.

SELECT okh.contract_number

     , okh.contract_number_modifier

     , okl.line_number

  FROM oks_subscr_elements ose

     , csi_item_instances csi

     , okc_k_headers_b okh

     , okc_k_lines_b okl

 WHERE csi.instance_number = :p_instance_number

   AND ose.order_line_id = csi.last_oe_order_line_id

   AND okh.id = ose.dnz_chr_id

   AND okl.chr_id = okh.id

   AND okl.id = ose.dnz_cle_id;

G. Order Details for Contracts Created from Order Management

G1. Query which retrieves the order header id  and order line id for a given contract.  object1_id1 gives the order header id where jtot_object1_code = OKX_ORDERHEAD

and the order line id where jtot_object1_code = OKX_ORDERLINE

SELECT okl.id subline

     , okl.cle_id service_line

     , okl.dnz_chr_id

     , obj.object1_id1

     , obj.jtot_object1_code

  FROM okc_k_lines_b okl

     , okc_k_rel_objs_v obj

 WHERE okl.dnz_chr_id = obj.chr_id

   AND okl.dnz_chr_id = :p_contract_id_from_query_a1

   AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35);

G2. The table OKS_K_ORDER_DETAILS captures service contract details when a service is ordered in Order Management.

SELECT *

  FROM oks_k_order_details

 WHERE chr_id = '<contract id from query A1>';

Script to Calculate the Price and Tax details of the Product Line in the Service Contract and Update to the Service Contract.( oks_qp_int_pvt.compute_price)

Please use this Script Carefully as the API updates the values to the base tables.


DECLARE
   l_input_details                                   oks_qp_pkg.input_details;
   l_output_details                                  oks_qp_pkg.price_details;
   l_modif_details                                   qp_preq_grp.line_detail_tbl_type;
   l_pb_details                                      oks_qp_pkg.g_price_break_tbl_type;
   l_return_status                                   VARCHAR2(20);
   l_msg_count                                       NUMBER;
   l_msg_data                                        VARCHAR2(2000);
   l_status_tbl                                      oks_qp_int_pvt.pricing_status_tbl;
   l_final_status_tbl                                oks_qp_int_pvt.pricing_status_tbl;
   l_count                                           NUMBER;
   l_msg_index_out                                   NUMBER;
   l_num                                             NUMBER;
   l_error_message                                   VARCHAR2(2000);
BEGIN
   l_num                                          := 10;
   l_input_details.intent                         := 'SP';
--   l_input_details.lse_id                         := 1;
--   l_input_details.chr_id                         := 1549063;
--   l_input_details.line_id                        := 364326736554344799961890872824534166768;
   l_input_details.subline_id                     := 364326736554364142775004706891329465584; --364326736554353262442628175228757110000;
   l_num                                          := 20;
   oks_qp_int_pvt.compute_price(p_api_version                 => 1.0
                              , p_init_msg_list               => 'T'
                              , p_detail_rec                  => l_input_details
                              , x_price_details               => l_output_details
                              , x_modifier_details            => l_modif_details
                              , x_price_break_details         => l_pb_details
                              , x_return_status               => l_return_status
                              , x_msg_count                   => l_msg_count
                              , x_msg_data                    => l_msg_data
                               );
   l_num                                          := 30;

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      l_num                                          := 40;

      IF l_msg_count > 0
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            l_num                                          := 50;
            apps.fnd_msg_pub.get(p_msg_index                   => i, p_encoded => fnd_api.g_false, p_data => l_msg_data
                               , p_msg_index_out               => l_msg_index_out);

            IF l_error_message IS NULL
            THEN
               l_error_message                                := SUBSTR(l_msg_data, 1, 250);
            ELSE
               l_error_message                                := l_error_message || ' /' || SUBSTR(l_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line('*****************************************');
      ELSE
         apps.fnd_msg_pub.get(p_msg_index                   => 1, p_encoded => fnd_api.g_false, p_data => l_msg_data
                            , p_msg_index_out               => l_msg_index_out);
         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_msg_data);
         DBMS_OUTPUT.put_line('*****************************************');
      END IF;
   ELSE
      l_num                                          := 60;
      DBMS_OUTPUT.put_line('API Success');
      DBMS_OUTPUT.put_line('Product Quantity : ' || l_output_details.prod_qty);
      DBMS_OUTPUT.put_line('Product Quantity UOM : ' || l_output_details.prod_qty_uom);
      DBMS_OUTPUT.put_line('Service Quantity : ' || l_output_details.serv_qty);
      DBMS_OUTPUT.put_line('Service Quantity UOM : ' || l_output_details.serv_qty_uom);
      DBMS_OUTPUT.put_line('Product Price List Id : ' || l_output_details.prod_price_list_id);
      DBMS_OUTPUT.put_line('Service Price List Id : ' || l_output_details.serv_price_list_id);
      DBMS_OUTPUT.put_line('Product Price List Line Id : ' || l_output_details.prod_price_list_line_id);
      DBMS_OUTPUT.put_line('Service Price List Line Id : ' || l_output_details.serv_price_list_line_id);
      DBMS_OUTPUT.put_line('Product List Unit Price : ' || l_output_details.prod_list_unit_price);
      DBMS_OUTPUT.put_line('Service List Unit Price : ' || l_output_details.serv_list_unit_price);
      DBMS_OUTPUT.put_line('Product Adjustment Unit Price : ' || l_output_details.prod_adj_unit_price);
      DBMS_OUTPUT.put_line('Service Adjustment Unit Price : ' || l_output_details.serv_adj_unit_price);
      DBMS_OUTPUT.put_line('Product Priced Quantity : ' || l_output_details.prod_priced_qty);
      DBMS_OUTPUT.put_line('Product Priced UOM : ' || l_output_details.prod_priced_uom);
      DBMS_OUTPUT.put_line('Product Extension Amount : ' || l_output_details.prod_ext_amount);
      DBMS_OUTPUT.put_line('Service Priced Quantity : ' || l_output_details.serv_priced_qty);
      DBMS_OUTPUT.put_line('Service Priced UOM : ' || l_output_details.serv_priced_uom);
      DBMS_OUTPUT.put_line('Service Extended Amount : ' || l_output_details.serv_ext_amount);
      DBMS_OUTPUT.put_line('Service OPerand : ' || l_output_details.serv_operand);
      DBMS_OUTPUT.put_line('Service Operator : ' || l_output_details.serv_operator);
      DBMS_OUTPUT.put_line('Status Code : ' || l_output_details.status_code);
      DBMS_OUTPUT.put_line('Status Text : ' || l_output_details.status_text);

      IF l_modif_details.COUNT > 0
      THEN
         l_num                                          := 70;

         FOR i IN l_modif_details.FIRST .. l_modif_details.LAST
         LOOP
            l_num                                          := 80;
            DBMS_OUTPUT.put_line('List Price : ' || l_modif_details(i).list_price);
         END LOOP;
      END IF;

      l_num                                          := 90;

      IF l_pb_details.COUNT > 0
      THEN
         l_num                                          := 100;

         FOR i IN l_pb_details.FIRST .. l_pb_details.LAST
         LOOP
            l_num                                          := 110;
            DBMS_OUTPUT.put_line('Price Break List Price : ' || l_pb_details(i).list_price);
         END LOOP;
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(l_num || ' Unexpected Error : ' || SUBSTR(SQLERRM, 1, 250));
END;

Script to Calculate the Tax details of the Product Line in the Service Contract(apps.oks_tax_util_pvt.get_tax)

DECLARE
   p_chr_id                                          NUMBER;
   p_cle_id                                          NUMBER;
   x_rail_rec                                        apps.oks_tax_util_pvt.ra_rec_type;
   l_return_status                                   VARCHAR2(20);
   l_msg_count                                       NUMBER;
   l_msg_data                                        VARCHAR2(2000);
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2(2000);
   l_num                                             NUMBER;
BEGIN
   p_chr_id                                       := 1549063;
   p_cle_id                                       := 364326736554364142775004706891329465584;
   apps.oks_tax_util_pvt.get_tax(p_api_version                 => 1.0
                               , p_init_msg_list               => fnd_api.g_false
                               , p_chr_id                      => p_chr_id
                               , p_cle_id                      => p_cle_id
                               , px_rail_rec                   => x_rail_rec
                               , x_msg_count                   => l_msg_count
                               , x_msg_data                    => l_msg_data
                               , x_return_status               => l_return_status
                                );

   IF l_return_status <> fnd_api.g_ret_sts_success
   THEN
      l_num                                          := 40;

      IF l_msg_count > 0
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            l_num                                          := 50;
            apps.fnd_msg_pub.get(p_msg_index                   => i, p_encoded => fnd_api.g_false, p_data => l_msg_data
                               , p_msg_index_out               => l_msg_index_out);

            IF l_error_message IS NULL
            THEN
               l_error_message                                := SUBSTR(l_msg_data, 1, 250);
            ELSE
               l_error_message                                := l_error_message || ' /' || SUBSTR(l_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line('*****************************************');
      ELSE
         apps.fnd_msg_pub.get(p_msg_index                   => 1, p_encoded => fnd_api.g_false, p_data => l_msg_data
                            , p_msg_index_out               => l_msg_index_out);
         DBMS_OUTPUT.put_line('*****************************************');
         DBMS_OUTPUT.put_line('API Error : ' || l_msg_data);
         DBMS_OUTPUT.put_line('*****************************************');
      END IF;
   ELSE
      l_num                                          := 60;
      DBMS_OUTPUT.put_line('*******************************************************************************************************');
      DBMS_OUTPUT.put_line('API Success');
      DBMS_OUTPUT.put_line('Header Id : ' || x_rail_rec.header_id);
      DBMS_OUTPUT.put_line('Line Id : ' || x_rail_rec.line_id);
      DBMS_OUTPUT.put_line('Tax Exempt Flag : ' || x_rail_rec.tax_exempt_flag);
      DBMS_OUTPUT.put_line('Tax exempt reason : ' || x_rail_rec.tax_exempt_reason);
      DBMS_OUTPUT.put_line('Tax exempt number : ' || x_rail_rec.tax_exempt_number);
      DBMS_OUTPUT.put_line('Sales Tax ID : ' || x_rail_rec.sales_tax_id);
      DBMS_OUTPUT.put_line('Org Id : ' || x_rail_rec.org_id);
      DBMS_OUTPUT.put_line('Orig_system_ship_address_id : ' || x_rail_rec.orig_system_ship_address_id);
      DBMS_OUTPUT.put_line('Orig_system_ship_contact_id : ' || x_rail_rec.orig_system_ship_contact_id);
      DBMS_OUTPUT.put_line('ORIG_SYSTEM_SHIP_CUSTOMER_I : ' || x_rail_rec.orig_system_ship_customer_id);
      DBMS_OUTPUT.put_line('SHIP_TO_SITE_USE_ID         : ' || x_rail_rec.ship_to_site_use_id);
      DBMS_OUTPUT.put_line('SHIP_TO_POSTAL_CODE         : ' || x_rail_rec.ship_to_postal_code);
      DBMS_OUTPUT.put_line('SHIP_TO_LOCATION_ID         : ' || x_rail_rec.ship_to_location_id);
      DBMS_OUTPUT.put_line('SHIP_TO_CUSTOMER_NUMBER     : ' || x_rail_rec.ship_to_customer_number);
      DBMS_OUTPUT.put_line('SHIP_TO_CUSTOMER_NAME       : ' || x_rail_rec.ship_to_customer_name);
      DBMS_OUTPUT.put_line('SHIP_TO_ORG_ID              : ' || x_rail_rec.ship_to_org_id);
      DBMS_OUTPUT.put_line('WAREHOUSE_ID                : ' || x_rail_rec.warehouse_id);
      DBMS_OUTPUT.put_line('ORIG_SYSTEM_SOLD_CUSTOMER_I : ' || x_rail_rec.orig_system_sold_customer_id);
      DBMS_OUTPUT.put_line('ORIG_SYSTEM_BILL_CUSTOMER_I : ' || x_rail_rec.orig_system_bill_customer_id);
      DBMS_OUTPUT.put_line('ORIG_SYSTEM_BILL_ADDRESS_ID : ' || x_rail_rec.orig_system_bill_address_id);
      DBMS_OUTPUT.put_line('ORIG_SYSTEM_BILL_CONTACT_ID : ' || x_rail_rec.orig_system_bill_contact_id);
      DBMS_OUTPUT.put_line('BILL_TO_SITE_USE_ID         : ' || x_rail_rec.bill_to_site_use_id);
      DBMS_OUTPUT.put_line('BILL_TO_POSTAL_CODE         : ' || x_rail_rec.bill_to_postal_code);
      DBMS_OUTPUT.put_line('BILL_TO_LOCATION_ID         : ' || x_rail_rec.bill_to_location_id);
      DBMS_OUTPUT.put_line('BILL_TO_CUSTOMER_NUMBER     : ' || x_rail_rec.bill_to_customer_number);
      DBMS_OUTPUT.put_line('BILL_TO_CUSTOMER_NAME       : ' || x_rail_rec.bill_to_customer_name);
      DBMS_OUTPUT.put_line('BILL_TO_ORG_ID              : ' || x_rail_rec.bill_to_org_id);
      DBMS_OUTPUT.put_line('RECEIPT_METHOD_NAME         : ' || x_rail_rec.receipt_method_name);
      DBMS_OUTPUT.put_line('RECEIPT_METHOD_ID           : ' || x_rail_rec.receipt_method_id);
      DBMS_OUTPUT.put_line('CONVERSION_TYPE             : ' || x_rail_rec.conversion_type);
      DBMS_OUTPUT.put_line('CONVERSION_DATE             : ' || x_rail_rec.conversion_date);
      DBMS_OUTPUT.put_line('CONVERSION_RATE             : ' || x_rail_rec.conversion_rate);
      DBMS_OUTPUT.put_line('CUSTOMER_TRX_ID             : ' || x_rail_rec.customer_trx_id);
      DBMS_OUTPUT.put_line('TRX_DATE                    : ' || x_rail_rec.trx_date);
      DBMS_OUTPUT.put_line('GL_DATE                     : ' || x_rail_rec.gl_date);
      DBMS_OUTPUT.put_line('DOCUMENT_NUMBER             : ' || x_rail_rec.document_number);
      DBMS_OUTPUT.put_line('TRX_NUMBER                  : ' || x_rail_rec.trx_number);
      DBMS_OUTPUT.put_line('LINE_NUMBER                 : ' || x_rail_rec.line_number);
      DBMS_OUTPUT.put_line('QUANTITY                    : ' || x_rail_rec.quantity);
      DBMS_OUTPUT.put_line('QUANTITY_ORDERED            : ' || x_rail_rec.quantity_ordered);
      DBMS_OUTPUT.put_line('UNIT_SELLING_PRICE          : ' || x_rail_rec.unit_selling_price);
      DBMS_OUTPUT.put_line('UNIT_STANDARD_PRICE         : ' || x_rail_rec.unit_standard_price);
      DBMS_OUTPUT.put_line('PRINTING_OPTION             : ' || x_rail_rec.printing_option);
      DBMS_OUTPUT.put_line('INVENTORY_ITEM_ID           : ' || x_rail_rec.inventory_item_id);
      DBMS_OUTPUT.put_line('INVENTORY_ORG_ID            : ' || x_rail_rec.inventory_org_id);
      DBMS_OUTPUT.put_line('UOM_CODE                    : ' || x_rail_rec.uom_code);
      DBMS_OUTPUT.put_line('UOM_NAME                    : ' || x_rail_rec.uom_name);
      DBMS_OUTPUT.put_line('RELATED_TRX_NUMBER          : ' || x_rail_rec.related_trx_number);
      DBMS_OUTPUT.put_line('RELATED_CUSTOMER_TRX_ID     : ' || x_rail_rec.related_customer_trx_id);
      DBMS_OUTPUT.put_line('PREVIOUS_CUSTOMER_TRX_ID    : ' || x_rail_rec.previous_customer_trx_id);
      DBMS_OUTPUT.put_line('REASON_CODE                 : ' || x_rail_rec.reason_code);
      DBMS_OUTPUT.put_line('TAX_RATE                    : ' || x_rail_rec.tax_rate);
      DBMS_OUTPUT.put_line('TAX_CODE                    : ' || x_rail_rec.tax_code);
      DBMS_OUTPUT.put_line('TAX_PRECEDENCE              : ' || x_rail_rec.tax_precedence);
      DBMS_OUTPUT.put_line('INVOICING_RULE_NAME         : ' || x_rail_rec.invoicing_rule_name);
      DBMS_OUTPUT.put_line('INVOICING_RULE_ID           : ' || x_rail_rec.invoicing_rule_id);
      DBMS_OUTPUT.put_line('PURCHASE_ORDER              : ' || x_rail_rec.purchase_order);
      DBMS_OUTPUT.put_line('SET_OF_BOOKS_ID             : ' || x_rail_rec.set_of_books_id);
      DBMS_OUTPUT.put_line('LINE_TYPE                   : ' || x_rail_rec.line_type);
      DBMS_OUTPUT.put_line('DESCRIPTION                 : ' || x_rail_rec.description);
      DBMS_OUTPUT.put_line('CURRENCY_CODE               : ' || x_rail_rec.currency_code);
      DBMS_OUTPUT.put_line('AMOUNT                      : ' || x_rail_rec.amount);
      DBMS_OUTPUT.put_line('CUST_TRX_TYPE_NAME          : ' || x_rail_rec.cust_trx_type_name);
      DBMS_OUTPUT.put_line('CUST_TRX_TYPE_ID            : ' || x_rail_rec.cust_trx_type_id);
      DBMS_OUTPUT.put_line('PAYMENT_TERM_ID             : ' || x_rail_rec.payment_term_id);
      DBMS_OUTPUT.put_line('AMOUNT_INCLUDES_TAX_FLAG    : ' || x_rail_rec.amount_includes_tax_flag);
      DBMS_OUTPUT.put_line('TAX_CAL_PLSQL_BLOCK         : ' || x_rail_rec.tax_cal_plsql_block);
      DBMS_OUTPUT.put_line('MINIMUM_ACCOUNTABLE_UNIT    : ' || x_rail_rec.minimum_accountable_unit);
      DBMS_OUTPUT.put_line('PRECISION                   : ' || x_rail_rec.PRECISION);
      DBMS_OUTPUT.put_line('FOB_POINT                   : ' || x_rail_rec.fob_point);
      DBMS_OUTPUT.put_line('TAXABLE_BASIS               : ' || x_rail_rec.taxable_basis);
      DBMS_OUTPUT.put_line('VAT_TAX_ID                  : ' || x_rail_rec.vat_tax_id);
      DBMS_OUTPUT.put_line('TAX_VALUE                   : ' || x_rail_rec.tax_value);
      DBMS_OUTPUT.put_line('TOTAL_PLUS_TAX              : ' || x_rail_rec.total_plus_tax);
      DBMS_OUTPUT.put_line('*******************************************************************************************************');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(l_num || ' Unexpected Error : ' || SUBSTR(SQLERRM, 1, 250));
END;

Output for the above Script.

=====================================

*******************************************************************************************************
API Success
Header Id : 1549063
Line Id : 364326736554364142775004706891329465584
Tax Exempt Flag : S
Tax exempt reason :
Tax exempt number :
Sales Tax ID :
Org Id : 102
Orig_system_ship_address_id : 326265
Orig_system_ship_contact_id :
ORIG_SYSTEM_SHIP_CUSTOMER_I : 2043938
SHIP_TO_SITE_USE_ID         : 401915
SHIP_TO_POSTAL_CODE         : 98031
SHIP_TO_LOCATION_ID         : 2143
SHIP_TO_CUSTOMER_NUMBER     :
SHIP_TO_CUSTOMER_NAME       :
SHIP_TO_ORG_ID              : 102
WAREHOUSE_ID                :
ORIG_SYSTEM_SOLD_CUSTOMER_I : 491186
ORIG_SYSTEM_BILL_CUSTOMER_I : 491186
ORIG_SYSTEM_BILL_ADDRESS_ID : 72442
ORIG_SYSTEM_BILL_CONTACT_ID :
BILL_TO_SITE_USE_ID         : 101790
BILL_TO_POSTAL_CODE         : 80112
BILL_TO_LOCATION_ID         : 2870
BILL_TO_CUSTOMER_NUMBER     :
BILL_TO_CUSTOMER_NAME       :
BILL_TO_ORG_ID              : 102
RECEIPT_METHOD_NAME         :
RECEIPT_METHOD_ID           :
CONVERSION_TYPE             :
CONVERSION_DATE             : 22-JAN-14
CONVERSION_RATE             :
CUSTOMER_TRX_ID             :
TRX_DATE                    :
GL_DATE                     :
DOCUMENT_NUMBER             :
TRX_NUMBER                  :
LINE_NUMBER                 :
QUANTITY                    : 1
QUANTITY_ORDERED            :
UNIT_SELLING_PRICE          : 5500.53
UNIT_STANDARD_PRICE         :
PRINTING_OPTION             :
INVENTORY_ITEM_ID           : 109135
INVENTORY_ORG_ID            : 103
UOM_CODE                    : Ea
UOM_NAME                    :
RELATED_TRX_NUMBER          :
RELATED_CUSTOMER_TRX_ID     :
PREVIOUS_CUSTOMER_TRX_ID   
REASON_CODE                 :
TAX_RATE                    : 0
TAX_CODE                    : Sales Tax
TAX_PRECEDENCE              :
INVOICING_RULE_NAME         :
INVOICING_RULE_ID           :
PURCHASE_ORDER              :
SET_OF_BOOKS_ID             : 1002
LINE_TYPE                   :
DESCRIPTION                 :
CURRENCY_CODE               : USD
AMOUNT                      : 2712.590136812997
CUST_TRX_TYPE_NAME          :
CUST_TRX_TYPE_ID            : 1005
PAYMENT_TERM_ID             :
AMOUNT_INCLUDES_TAX_FLAG    : N
TAX_CAL_PLSQL_BLOCK         :
MINIMUM_ACCOUNTABLE_UNIT    :
PRECISION                   : 2
FOB_POINT                   :
TAXABLE_BASIS               :
VAT_TAX_ID                  : 10094
TAX_VALUE                   : 0
TOTAL_PLUS_TAX              : 2712.590136812997
*******************************************************************************************************