Oracle Shipping Network SQL Query

Useful Script for Oracle Shipping Network , I wrote custom function for GL Code , you may find below

SELECT a.from_organization_code, a.from_organization_name,
       a.to_organization_code, a.to_organization_name,  DECODE (a.intransit_type,
                 2, 'Intransit',
                 1, 'Direct'
                ) "Transfer Type",
       (SELECT meaning
          FROM apps.mfg_lookups
         WHERE lookup_type = 'MTL_FOB_POINT'
           AND lookup_code = a.fob_point) fob_point,
       a.elemental_visibility_enabled, DECODE (a.routing_header_id,
                 1, 'Standard',
                 2, 'Inspection',
                 3, 'Direct',
                 NULL, NULL
                ) receipt_routing,
       manual_receipt_expense,DECODE (a.internal_order_required_flag,
                 1, 'Y',
                 2, 'N'
                ) intenal_order_required,
        DECODE (a.matl_interorg_transfer_code,
                 1, 'None',
                 '2', 'Requested Value',
                 '3', 'Requested %',
                 '4', 'Predefined'
                ) transfer_Charge,INTERORG_TRNSFR_CHARGE_PERCENT,
       distance_uom_code, to_organization_distance Value,
       apps.xxr12_get_val
                  (interorg_transfer_cr_account,
                   'GLCODE'
                  ) interorg_transfer_cr_account,
       apps.xxr12_get_val
                      (interorg_price_var_account,
                       'GLCODE'
                      ) interorg_price_var_account,
       apps.xxr12_get_val
                  (interorg_receivables_account,
                   'GLCODE'
                  ) interorg_receivables_account,
       apps.xxr12_get_val
                        (interorg_payables_account,
                         'GLCODE'
                        ) interorg_payables_account,
       apps.xxr12_get_val (intransit_inv_account,
                           'GLCODE'
                          ) intransit_inv_account
  FROM mtl_shipping_network_view a, gl_code_combinations_kfv b
 WHERE a.interorg_transfer_cr_account = b.code_combination_id

CREATE OR REPLACE FUNCTION APPS.xxr12_get_val (p_val varchar2, p_type VARCHAR2)
--Reason : r12 Implementation
--By : Shagul
RETURN VARCHAR2
IS
   l_op_val  VARCHAR (100);
BEGIN
--Function
   BEGIN
      IF p_type = 'GLCODE'
      THEN
         BEGIN
            l_op_val:= NULL;

            SELECT concatenated_segments
              INTO l_op_val
              FROM gl_code_combinations_kfv
             WHERE code_combination_id = p_val AND 'GLCODE' = p_type;
         END;
      ELSIF p_type = 'ORGCODE'
      THEN
         BEGIN
            SELECT organization_code
              INTO l_op_val
              FROM org_organization_definitions
             WHERE organization_id = p_val AND 'ORGCODE' = p_type;
         END;
      ELSIF p_type = 'ORGID'
      THEN
         BEGIN
            SELECT organization_id
              INTO l_op_val
              FROM org_organization_definitions
             WHERE organization_code = p_val AND 'ORGID' = p_type;
         END;
      END IF;
   END;

   RETURN l_op_val;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/