Oracle Fusion SQL : SQL to Extract Transfer Order details

SELECT d.header_number
       , e.line_number
       , so.organization_code source_inv_org
       , do.organization_code dest_inv_org
       , sku.item_number
       , e.requested_qty
       , e.shipped_qty
       , e.received_qty
       , e.delivered_qty
       , e.source_subinventory_code source_subinventory
       , e.destination_subinventory_code dest_subinventory
       , d.fulfill_orchestration_required
       , d.source_type_lookup  hdr_source_type_lookup
       , e.source_organization_id
       , e.destination_organization_id
       , e.line_id
       , e.interface_status_lookup
       , e.source_type_lookup
       , e.destination_type_lookup
       , e.destination_location_id
       , e.status_lookup
       , e.need_by_date
       , e.scheduled_ship_date
       , e.qty_uom_code
       , e.unit_price
       , e.req_bu_id
       , e.supply_order_reference_number
       , e.supply_order_ref_line_number
       , d.header_id
       , sku.inventory_item_id
       , d.creation_date
  FROM fusion.inv_transfer_order_headers d
       , fusion.inv_transfer_order_lines e
       , fusion.inv_org_parameters so
       , fusion.inv_org_parameters do
       , fusion.egp_system_items_b sku
 WHERE 1 = 1
   AND sku.inventory_item_id = e.inventory_item_id
   AND sku.organization_id = e.source_organization_id
   AND do.organization_id = e.destination_organization_id
   AND so.organization_id = e.source_organization_id
   AND d.header_id = e.header_id
   AND so.organization_code = :p_from_organization
   AND do.organization_code = :p_to_organization
   AND sku.item_number = NVL(:p_part_number, sku.item_number)
   AND e.supply_order_reference_number = Nvl(:p_supply_order, e.supply_order_reference_number)
 ORDER BY d.creation_date DESC
          , 1
          , Lpad(e.line_number, 2);

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

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>