API to Expire Instance in Oracle Install Base

DECLARE
   l_record_status                              VARCHAR2(1);
   l_sv_status                                  VARCHAR2(2000);
   l_msg_data                                   VARCHAR2(2000);
   l_mesg                                       VARCHAR2(4000);
   l_mesg_len                                   NUMBER;
   l_mesg_count                                 NUMBER;


   SUBTYPE instance_rec IS csi_datastructures_pub.instance_rec;

   SUBTYPE transaction_rec IS csi_datastructures_pub.transaction_rec;

   SUBTYPE id_tbl IS csi_datastructures_pub.id_tbl;

   SUBTYPE instance_query_rec IS csi_datastructures_pub.instance_query_rec;

   SUBTYPE party_query_rec IS csi_datastructures_pub.party_query_rec;

   SUBTYPE party_account_query_rec IS csi_datastructures_pub.party_account_query_rec;

   SUBTYPE instance_header_tbl IS csi_datastructures_pub.instance_header_tbl;

   -- Get Item Instance parameters
   l_instance_query_rec                         instance_query_rec;
   l_party_query_rec                            party_query_rec;
   l_account_query_rec                          party_account_query_rec;
   l_instance_header_tbl                        instance_header_tbl;

   -- Expire Item Instance parameters
   l_instance_rec                               instance_rec;
   l_txn_rec                                    transaction_rec;
   l_instance_id_lst                            id_tbl;

   l_return_status       VARCHAR2(1) := okl_api.g_ret_sts_success;
   l_overall_status      VARCHAR2(1) := okl_api.g_ret_sts_success;

   l_api_name            CONSTANT VARCHAR2(30) := 'expire_item';
   l_api_version         CONSTANT NUMBER := 1;
   l_msg_count           NUMBER := fnd_api.g_miss_num;
BEGIN
   l_instance_query_rec.instance_id  := 5321932;

   csi_item_instance_pub.get_item_instances(
   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_query_rec   => l_instance_query_rec
 , p_party_query_rec      => l_party_query_rec
 , p_account_query_rec    => l_account_query_rec
 , p_transaction_id       => NULL
 , p_resolve_id_columns   => fnd_api.g_false
 , p_active_instance_only => fnd_api.g_true
 , x_instance_header_tbl  => l_instance_header_tbl
 , x_return_status        => l_return_status
 , x_msg_count            => l_msg_count
 , x_msg_data             => l_msg_data);


l_instance_rec.instance_id:= l_instance_header_tbl( 1).instance_id;
l_instance_rec.object_version_number:=l_instance_header_tbl( 1).object_version_number;
   l_instance_rec.active_end_date   := SYSDATE;

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

   -- **************************************
   -- Call Installed Base API to expire item
   -- **************************************

   csi_item_instance_pub.expire_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_expire_children                         => fnd_api.g_true
 , p_txn_rec                                 => l_txn_rec
 , x_instance_id_lst                         => l_instance_id_lst
 , x_return_status                           => l_return_status
 , x_msg_count                               => l_msg_count
 , x_msg_data                                => l_msg_data);

   IF l_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line(   'instance '
                           || l_instance_rec.instance_id
                           || ' expired successfully');
   -- **************************************
   -- Display errors encounted for the expiration
   -- **************************************
   ELSE
      l_mesg_count          := fnd_msg_pub.count_msg;

      IF l_mesg_count > 0
      THEN
         l_mesg             :=
               CHR( 10)
            || SUBSTR(fnd_msg_pub.get(fnd_msg_pub.g_first
                                    , fnd_api.g_false)
                    , 1
                    , 512);

         FOR i IN 1 .. (l_mesg_count - 1)
         LOOP
            l_mesg      :=
                  l_mesg
               || CHR( 10)
               || SUBSTR(fnd_msg_pub.get(fnd_msg_pub.g_next
                                       , fnd_api.g_false)
                       , 1
                       , 512);
         END LOOP;

         fnd_msg_pub.delete_msg();

         l_mesg_len           := LENGTH( l_mesg);

         FOR i IN 1 .. CEIL( l_mesg_len / 255)
         LOOP
            DBMS_OUTPUT.put_line(SUBSTR(l_mesg
                                      , ((i * 255) - 254)
                                      , 255));
         END LOOP;
      END IF;
   END IF;
END;
/

API to swap Service contract for Newly created Install Base with Old install base

DECLARE
   p_transaction_type            VARCHAR2(50);
   p_instance_id                 NUMBER;
   p_new_instance_id             NUMBER;
   p_vld_org_id                  NUMBER;
   p_quantity                    NUMBER;
   p_party_account_id1           NUMBER;
   p_party_account_id2           NUMBER;
   p_transaction_date            DATE;
   p_source_transaction_date     DATE;
   p_transaction_id              NUMBER;
   p_grp_call_contracts          VARCHAR2(1);
   p_txn_type_id                 NUMBER;
   p_system_id                   NUMBER;
   p_order_line_id               NUMBER;
   p_call_from_bom_expl          VARCHAR2(1);
   lp_oks_txn_inst_tbl           oks_ibint_pub.txn_instance_tbl;
   lx_return_status              VARCHAR2(1);
   lx_msg_count                  NUMBER;
   lx_msg_data                   VARCHAR2(500);
   l_msg_index_out               NUMBER;
   l_return_msg                  VARCHAR2(32600);
   k_rpl_tbl                     oks_extwarprgm_pvt.contract_tbl;

   CURSOR get_k_for_rpl_csr
   IS
        SELECT tmp.old_customer_product_id instance_id
             , tmp.termination_date
             , tmp.installation_date
             , tmp.transaction_date
             , tmp.old_customer_acct_id
             , tmp.new_customer_acct_id
             , tmp.system_id
             , tmp.old_quantity
             , tmp.new_quantity
             , tmp.new_customer_product_id
             , ki.cle_id subline_id
             , ki.dnz_chr_id
             , kh.start_date hdr_sdt
             , kh.end_date hdr_edt
             , kh.sts_code hdr_sts
             , kl.cle_id
             , kl.price_negotiated
             , kl.start_date
             , kl.end_date
             , kl.sts_code prod_sts
             , kl.cust_acct_id
             , tl.start_date srv_sdt
             , tl.end_date srv_edt
             , kh.sts_code
             , kh.contract_number
             , ki.number_of_items
             , tl.price_negotiated
             , kl.date_terminated
             , tmp.old_inventory_item_id
             , kh.authoring_org_id
             , kh.inv_organization_id
             , kl.lse_id
             , kh.scs_code
             , tmp.new_customer_product_id
             , kis.object1_id1
             , tl.currency_code
             , tmp.old_unit_of_measure
             , kl.line_renewal_type_code
             , tmp.raise_credit
             , NULL
             , okl.tax_amount
             , kl.price_unit
             , kl.name
             , kl.item_description
             , kl.upg_orig_system_ref
             , kl.upg_orig_system_ref_id
             , tmp.new_inventory_item_id
             , tmp.return_reason_code
             , tmp.order_line_id
             , okl.price_uom
             , okl.toplvl_uom_code
             , okl.toplvl_price_qty
          FROM okc_k_items ki
             , okc_k_headers_all_b kh
             , okc_k_lines_v kl
             , okc_statuses_b st
             , oks_instance_temp tmp
             , okc_k_lines_b tl
             , okc_k_items kis
             , oks_k_lines_b okl
         WHERE tmp.rpl = 'Y'
           AND ki.object1_id1 = TO_CHAR( tmp.old_customer_product_id)
           AND ki.jtot_object1_code = 'OKX_CUSTPROD'
           AND ki.dnz_chr_id = kh.id
           AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
           AND ki.cle_id = kl.id
           AND tl.id = kl.cle_id
           AND kis.cle_id = tl.id
           AND kis.dnz_chr_id = tl.dnz_chr_id
           AND kl.sts_code = st.code
           AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED', 'HOLD')
           AND kl.date_terminated IS NULL
           AND okl.cle_id = kl.id
           AND kh.template_yn = 'N'
           AND ((TRUNC( tmp.transaction_date) <= TRUNC( kl.end_date)
             AND TRUNC( tmp.transaction_date) >= TRUNC( kl.start_date))
             OR (TRUNC( tmp.transaction_date) <= TRUNC( kl.start_date)))
      ORDER BY tmp.old_customer_product_id
             , kh.creation_date;
BEGIN
   --   lp_oks_txn_inst_tbl( 1).old_customer_product_id          := 4367640;
   apps.csi_item_instance_pvt.call_to_contracts(
  p_transaction_type                        => 'RPL'
, p_instance_id                             => 4367640
, p_new_instance_id                         => 5302910
, p_vld_org_id                              => 103
, p_quantity                                => NULL
, p_party_account_id1                       => NULL
, p_party_account_id2                       => NULL
, p_transaction_date                        => SYSDATE
, p_source_transaction_date                 => SYSDATE
, p_grp_call_contracts                      => fnd_api.g_false
, p_oks_txn_inst_tbl                        => lp_oks_txn_inst_tbl
, x_return_status                           => lx_return_status
, x_msg_count                               => lx_msg_count
, x_msg_data                                => lx_msg_data);

   IF lx_return_status = fnd_api.g_ret_sts_success
   THEN
      FORALL i IN lp_oks_txn_inst_tbl.FIRST .. lp_oks_txn_inst_tbl.LAST
         INSERT INTO oks_instance_temp
              VALUES lp_oks_txn_inst_tbl( i);

      fnd_global.apps_initialize(12247
                               , 50305
                               , 515);

      OPEN get_k_for_rpl_csr;

      FETCH get_k_for_rpl_csr BULK COLLECT INTO k_rpl_tbl;

      CLOSE get_k_for_rpl_csr;

      apps.oks_extwarprgm_pvt.update_contract_ibreplace(
           k_rpl_tbl
         , lx_return_status
         , lx_msg_count
         , lx_msg_data);

      IF lx_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line( 'Swapped syccessfully.');
         COMMIT;
      ELSE
         IF lx_msg_count > 0
         THEN
            FOR i IN 1 .. lx_msg_count
            LOOP
               fnd_msg_pub.get(p_msg_index    => i
                             , p_encoded      => fnd_api.g_false
                             , p_data         => lx_msg_data
                             , p_msg_index_out=> l_msg_index_out);

               IF l_return_msg IS NULL
               THEN
                  l_return_msg                :=
                        l_msg_index_out
                     || ':'
                     || lx_msg_data;
               ELSE
                  l_return_msg                :=
                        l_return_msg
                     || '/'
                     || l_msg_index_out
                     || ':'
                     || lx_msg_data;
               END IF;
            END LOOP;
         END IF;

         DBMS_OUTPUT.put_line(   'Swaping of Service contract for IB failed : '
                              || NVL(lx_msg_data, l_return_msg));
      END IF;
   ELSE
      IF lx_msg_count > 0
      THEN
         FOR i IN 1 .. lx_msg_count
         LOOP
            fnd_msg_pub.get(p_msg_index    => i
                          , p_encoded      => fnd_api.g_false
                          , p_data         => lx_msg_data
                          , p_msg_index_out=> l_msg_index_out);

            IF l_return_msg IS NULL
            THEN
               l_return_msg                :=
                     l_msg_index_out
                  || ':'
                  || lx_msg_data;
            ELSE
               l_return_msg                :=
                     l_return_msg
                  || '/'
                  || l_msg_index_out
                  || ':'
                  || lx_msg_data;
            END IF;
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line(   'call to contract failure : '
                           || NVL(lx_msg_data, l_return_msg));
   END IF;
END;

Matrix/Crosstab SQL using PIVOT functionality

Sample SQL's 

Using Decode and aggregate functions.

SELECT item
     , inventory
     , product_family
  FROM ( SELECT msi.segment1 item
, MAX( DECODE(mcs.category_set_name, 'Inventory', mc.segment1))       inventory
, MAX( DECODE(mcs.category_set_name, 'Product Family', mc.segment1))   product_family
            FROM mtl_system_items_b msi
               , mtl_item_categories mic
               , mtl_categories mc
               , mtl_category_sets mcs
           WHERE msi.organization_id = mic.organization_id
             AND msi.inventory_item_id = mic.inventory_item_id
             AND msi.segment1 = :p_item
             AND msi.organization_id = 103
             AND mic.category_set_id = mcs.category_set_id
             AND mic.category_id = mc.category_id
             AND mc.structure_id = mcs.structure_id
        GROUP BY msi.segment1)
                       


SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT XML (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set)   --<-- pivot_for_clause
IN (select category_set_name from mtl_category_sets)) 
--<-- pivot_in_clause
                                                   
                                                   
                                                   
SELECT *
  FROM (SELECT msi.segment1 item
             , mcs.category_set_name category_set
             , mc.segment1
          FROM mtl_system_items_b msi
             , mtl_item_categories mic
             , mtl_categories mc
             , mtl_category_sets mcs
         WHERE 1 = 1
           AND msi.inventory_item_id = mic.inventory_item_id
           AND msi.organization_id = mic.organization_id
           AND msi.segment1 = :p_item
           AND msi.organization_id = 103
           AND mic.category_set_id = mcs.category_set_id
           AND mic.category_id = mc.category_id
           AND mc.structure_id = mcs.structure_id) 
PIVOT (MAX( segment1) AS category  --<-- pivot_clause
FOR( category_set) --<-- pivot_for_clause
IN  ('Inventory', 'Series', 'Model')) --<-- pivot_in_clause

Template to Use SAVE EXCEPTIONS in COLLECTIONS (Oracle Pl/SQL table Types)

DECLARE -- Oracle9i and above!
  l_array   <array_type_declaration>;
  bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 );
BEGIN   
   FORALL indx IN l_array.FIRST .. l_array.LAST
      SAVE EXCEPTIONS
      /*DML statement of choice*/
      |;
EXCEPTION
   WHEN bulk_errors
   THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            'Error ' || indx || ' occurred during ' ||
            'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
            ' updating name to ' ||SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE (
            'Oracle error is ' ||
            SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
      END LOOP;  
END;
/

--- Other way 

DECLARE -- Oracle9i and above!
  l_array   <array_type_declaration>;
BEGIN    
   FORALL indx IN l_array.FIRST .. l_array.LAST
      SAVE EXCEPTIONS
      /*DML statement of choice*/
      |;
EXCEPTION
   WHEN OTHERS
   THEN 
               IF SQLCODE = -24381
               THEN
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.PUT_LINE (
            'Error ' || indx || ' occurred during ' ||
            'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
            ' updating name to ' ||SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX);
         DBMS_OUTPUT.PUT_LINE (
            'Oracle error is ' ||
            SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
      END LOOP;   
           ELSE
               RAISE;
           END IF;
END;
/

Sample Script to APPLY_HOLD using OE_ORDER_PUB ( R12 ) at header level

CREATE OR REPLACE PROCEDURE apply_hold( p_request_rec IN oe_order_pub.request_rec_type)
IS
 l_header_rec              oe_order_pub.header_rec_type;
 l_line_tbl                oe_order_pub.line_tbl_type;
 l_request_rec             oe_order_pub.request_rec_type;
 l_action_request_tbl      oe_order_pub.request_tbl_type;
 x_header_val_rec          oe_order_pub.header_val_rec_type;
 x_header_adj_tbl          oe_order_pub.header_adj_tbl_type;
 x_header_adj_val_tbl      oe_order_pub.header_adj_val_tbl_type;
 x_header_price_att_tbl    oe_order_pub.header_price_att_tbl_type;
 x_header_adj_att_tbl      oe_order_pub.header_adj_att_tbl_type;
 x_header_adj_assoc_tbl    oe_order_pub.header_adj_assoc_tbl_type;
 x_header_scredit_tbl      oe_order_pub.header_scredit_tbl_type;
 x_header_scredit_val_tbl  oe_order_pub.header_scredit_val_tbl_type;
 x_line_val_tbl            oe_order_pub.line_val_tbl_type;
 x_line_adj_tbl            oe_order_pub.line_adj_tbl_type;
 x_line_adj_val_tbl        oe_order_pub.line_adj_val_tbl_type;
 x_line_price_att_tbl      oe_order_pub.line_price_att_tbl_type;
 x_line_adj_att_tbl        oe_order_pub.line_adj_att_tbl_type;
 x_line_adj_assoc_tbl      oe_order_pub.line_adj_assoc_tbl_type;
 x_line_scredit_tbl        oe_order_pub.line_scredit_tbl_type;
 x_line_scredit_val_tbl    oe_order_pub.line_scredit_val_tbl_type;
 x_lot_serial_tbl          oe_order_pub.lot_serial_tbl_type;
 x_lot_serial_val_tbl      oe_order_pub.lot_serial_val_tbl_type;
 x_action_request_tbl      oe_order_pub.request_tbl_type;
 x_debug_file              VARCHAR2(100);
 x_return_msg              VARCHAR2(4000); 
 l_msg_index_out           NUMBER(10);
 l_msg_count               NUMBER;
 l_msg_data                VARCHAR2(250);
 l_return_status           VARCHAR2(1);

BEGIN
   DBMS_OUTPUT.enable( 1000000);
   fnd_global.apps_initialize(12247
                            , 52844
                            , 660); -- pass in user_id, 
      ----responsibility_id, and application_id
   mo_global.init( 'ONT');
   mo_global.set_policy_context('S'
                              , 'org_id');
oe_msg_pub.initialize;
l_request_rec.entity_id := p_request_rec.entity_id;--SO header_id
l_request_rec.entity_code:= p_request_rec.entity_code; 
-- oe_globals.g_entity_header;
l_request_rec.request_type := p_request_rec.request_type; 
-- oe_globals.g_apply_hold;
l_request_rec.param1   := p_request_rec.param1; -- Hold_id
l_request_rec.param2 := p_request_rec.param2; --'O'; 
-- indicator that it is an order hold
l_request_rec.param3:= p_request_rec.param3; -- header_id
l_action_request_tbl( 1)            := l_request_rec;
   -- CALL TO PROCESS ORDER
   oe_order_pub.process_order(
   p_api_version_number             => 1.0
 , p_init_msg_list                  => fnd_api.g_false
 , p_return_values                  => fnd_api.g_false
 , p_action_commit                  => fnd_api.g_false
 , x_return_status                  => l_return_status
 , x_msg_count                      => l_msg_count
 , x_msg_data                       => l_msg_data
 , p_header_rec                     => l_header_rec
 , p_line_tbl                       => l_line_tbl
 , p_action_request_tbl             => l_action_request_tbl
 --OUT PARAMETERS
 , x_header_rec                     => l_header_rec
 , x_header_val_rec                 => x_header_val_rec
 , x_header_adj_tbl                 => x_header_adj_tbl
 , x_header_adj_val_tbl             => x_header_adj_val_tbl
 , x_header_price_att_tbl           => x_header_price_att_tbl
 , x_header_adj_att_tbl             => x_header_adj_att_tbl
 , x_header_adj_assoc_tbl           => x_header_adj_assoc_tbl
 , x_header_scredit_tbl             => x_header_scredit_tbl
 , x_header_scredit_val_tbl         => x_header_scredit_val_tbl
 , x_line_tbl                       => l_line_tbl
 , x_line_val_tbl                   => x_line_val_tbl
 , x_line_adj_tbl                   => x_line_adj_tbl
 , x_line_adj_val_tbl               => x_line_adj_val_tbl
 , x_line_price_att_tbl             => x_line_price_att_tbl
 , x_line_adj_att_tbl               => x_line_adj_att_tbl
 , x_line_adj_assoc_tbl             => x_line_adj_assoc_tbl
 , x_line_scredit_tbl               => x_line_scredit_tbl
 , x_line_scredit_val_tbl           => x_line_scredit_val_tbl
 , x_lot_serial_tbl                 => x_lot_serial_tbl
 , x_lot_serial_val_tbl             => x_lot_serial_val_tbl
 , x_action_request_tbl             => x_action_request_tbl);

   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line(   'Hold applied for header id       ===============> '|| TO_CHAR( l_request_rec.entity_id)
                  || ' successfully!!!');
   ELSE
      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_msg_data
                      , p_msg_index_out  => l_msg_index_out);

         IF x_return_msg IS NULL
         THEN
            x_return_msg              :=
                  l_msg_index_out
               || ':'
               || l_msg_data;
         ELSE
            x_return_msg              :=
                  x_return_msg
               || '/'
               || l_msg_index_out
               || ':'
               || l_msg_data;
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line(   'Hold Application failed with reason : ' || x_return_msg);
   END IF;
END;