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;

Oracle Object Oriented Programming

CREATE OR REPLACE TYPE xx_data_struc_obj AS OBJECT(
   num_col  NUMBER
 , num_col2 NUMBER);
/

CREATE OR REPLACE TYPE xx_data_construct_obj AS OBJECT(

   --Define member Functions
   xx_data_struc xx_data_struc_obj

 -- this function performs addition
 , MEMBER FUNCTION addition
      RETURN NUMBER

 -- this function performs substraction
 , MEMBER FUNCTION substraction
      RETURN NUMBER

 -- this function performs multiplication
 , MEMBER FUNCTION multiplication
      RETURN NUMBER

 -- this function performs dividation
 , MEMBER FUNCTION dividation
      RETURN NUMBER);
/


CREATE OR REPLACE TYPE BODY xx_data_construct_obj
AS
   MEMBER FUNCTION addition
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col + xx_data_struc.num_col2;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'ADDITION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END addition;

   MEMBER FUNCTION substraction
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col2 - xx_data_struc.num_col;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'SUBSTRACTION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END substraction;

   MEMBER FUNCTION multiplication
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
      l_result := xx_data_struc.num_col * xx_data_struc.num_col2;
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'MULTIPLICATION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END multiplication;

   MEMBER FUNCTION dividation
      RETURN NUMBER
   AS
      l_result                                     NUMBER;
   BEGIN
     l_result:= ROUND(xx_data_struc.num_col/xx_data_struc.num_col2);
      RETURN l_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(   'DIVIDATION : UNEXP_ERROR : '
                              || SUBSTR(SQLERRM
                                      , 1
                                      , 250));
   END dividation;
END;
/

--------------Execution -------------------------

DECLARE
   data_struc  xx_data_struc_obj := xx_data_struc_obj(25, 60);
   data_construct xx_data_construct_obj 
                        := xx_data_construct_obj( data_struc);
BEGIN
   DBMS_OUTPUT.put_line( 'Addition Output: '||data_construct.addition);
   DBMS_OUTPUT.put_line( 'Substraction Output: '||data_construct.substraction);
   DBMS_OUTPUT.put_line( 'Multiplication Output: '||data_construct.multiplication);
   DBMS_OUTPUT.put_line( 'Dividation Output: '||data_construct.dividation);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'BLOCK : UNEXP_ERROR : '
                           || SUBSTR(SQLERRM
                                   , 1
                                   , 250));
END;

Oracle User-Defined Aggregate Functions Interface (Convert into a Single String)

This section describes the routines that need to be implemented to define a user-defined aggregate function. The routines are implemented as methods in an object type. Then the CREATE FUNCTION statement is used to actually create the aggregate function

ODCIAggregateDelete()	Removes an input value from the current group.
ODCIAggregateInitialize()	Initializes the aggregation context and instance of the implementation object type, and returns it as an OUT parameter.
ODCIAggregateIterate()	Iterates through input rows by processing the input values, updating and then returning the aggregation context.
ODCIAggregateMerge()	Merges two aggregation contexts into a single object instance during either serial or parallel evaluation of the user-defined aggregate.
ODCIAggregateTerminate()	Calculates the result of the aggregate computation and performs all necessary cleanup, such as freeing memory.
ODCIAggregateWrapContext()	Integrates all external pieces of the current aggregation context to make the context self-contained.

---First define the Object type

CREATE OR REPLACE TYPE string_agg_obj_typ AS OBJECT(
   g_string VARCHAR2(32767)

, STATIC FUNCTION odciaggregateinitialize( 
  sctx IN OUT string_agg_obj_typ) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateiterate(
  self  IN OUT string_agg_obj_typ
, VALUE     IN VARCHAR2)RETURN NUMBER
 
, MEMBER FUNCTION odciaggregateterminate(
  self        IN     string_agg_obj_typ
, returnvalue OUT    VARCHAR2
, flags       IN     NUMBER) RETURN NUMBER
 
, MEMBER FUNCTION odciaggregatemerge(
  self        IN OUT string_agg_obj_typ
, ctx2        IN     string_agg_obj_typ) RETURN NUMBER);
/
SHOW ERRORS

---First define the Object type body 
CREATE OR REPLACE TYPE BODY string_agg_obj_typ
IS
   STATIC FUNCTION odciaggregateinitialize( 
      sctx IN OUT string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      sctx    := string_agg_obj_typ( NULL);
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateiterate(
  self         IN OUT string_agg_obj_typ
, VALUE        IN     VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string :=
            self.g_string
         || ','
         || VALUE;
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregateterminate(
  self          IN  string_agg_obj_typ
, returnvalue  OUT  VARCHAR2
, flags         IN  NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      returnvalue   :=
         RTRIM(LTRIM(self.g_string
                   , ',')
             , ',');
      RETURN 0;
   END;

   MEMBER FUNCTION odciaggregatemerge(
  self          IN OUT string_agg_obj_typ
, ctx2          IN     string_agg_obj_typ)
      RETURN NUMBER
   IS
   BEGIN
      self.g_string  :=
            self.g_string
         || ','
         || ctx2.g_string;
      RETURN 0;
   END;
END;
/

SHOW ERRORS

---- Create a function which will accept the string a input 
---- parameter and return a concatenated String

CREATE OR REPLACE FUNCTION string_agg_fnc( p_input VARCHAR2)
   RETURN VARCHAR2
   PARALLEL_ENABLE
   AGGREGATE USING string_agg_obj_typ;
/

SHOW ERRORS

--Now run the SQL as below  
 SQL>   SELECT organization_id
  2         , string_agg_fnc( segment1)
  3      FROM mtl_system_items_b
  4     WHERE organization_id = 103
  5       AND ROWNUM <= 10
  6  GROUP BY organization_id;

ORGANIZATION_ID     STRING_AGG_FNC(SEGMENT1)
---------------    ----------------------------------------
            103     01R0593,06P5760,06P5759,06P5755,06P5323
SQL>



------------------------------------------------------------------
SQL to Convert ROWS into Concatenated string
-----------------------------------------------------------------
SQL> SELECT organization_id,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(segment1,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS items
FROM   (SELECT organization_id,
               segment1,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) AS curr,
 ROW_NUMBER() OVER (PARTITION BY organization_id ORDER BY segment1) -1 AS prev
 FROM   mtl_system_items_b where organization_id = 103 and rownum <= 5)
GROUP BY organization_id
CONNECT BY prev = PRIOR curr 
      AND organization_id = PRIOR organization_id
START WITH curr = 1

ORGANIZATION_ID    ITEMS
---------------   ------------------------------------------
            103   01R0593,06P5323,06P5755,06P5759,06P5760
           
SQL>