This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

    UTL_INADDR

    The UTL_INADDR provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.
    The GET_HOST_ADDRESS function returns the IP address of the specified host name.
    SQL> SELECT UTL_INADDR.get_host_address('TEST') FROM dual;

    UTL_INADDR.GET_HOST_ADDRESS('TEST')
    --------------------------------------------------------------------------------
    192.167.1.56

    SQL>

     The IP address of the database server is returned if the specified host name is NULL or is omitted.

    SQL> SELECT UTL_INADDR.get_host_address from dual;

    GET_HOST_ADDRESS
    --------------------------------------------------------------------------------
    192.161.1.55

    SYS_CONTEXT

    The SYS_CONTEXT function is able to return the following host and IP address information for the current session:
    • TERMINAL – An operating system identifier for the current session. This is often the client machine name.
    • HOST – The host name of the client machine.
    • IP_ADDRESS – The IP address of the client machine.
    • SERVER_HOST – The host name of the server running the database instance.
    SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

    SYS_CONTEXT('USERENV','TERMINAL')
    --------------------------------------------------------------------
    TEST10

    SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

    SYS_CONTEXT('USERENV','IP_ADDRESS')
    --------------------------------------------------------------------
    192.167.1.55

    SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

    SYS_CONTEXT('USERENV','SERVER_HOST')
    --------------------------------------------------------------------
    Z4210gr11

    V$INSTANCE

    The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.
    SQL> SELECT host_name FROM v$instance;

    HOST_NAME
    ------------------------------------------------
    Z4210gR11

    V$SESSION

    The V$SESSION view contains the following host information for all database sessions:

    TERMINAL – The operating system terminal name for the client. This is often set to the client machine name.

    MACHINE – The operating system name for the client machine. This may include the domain name if present.

    The following examples show the typical output for each column.

    SQL> SELECT terminal, machine FROM v$session WHERE username = 'OEAG';

    TERMINAL MACHINE
    ------------------------------ ----------------------------------------------------
    TEST10 ORACLE-BASETEST10

    Here is the SQL Query for Link Between PO-RCV-XLA-AP.

    SELECT DISTINCT
           A.PO_NO,
           A.LINE_NUM,
           A.DIST_NUM,
           A.ITEM_CATEGORY,
           A.IS_CAPITAL,
           A.SEGMENT2 PO_ACCT,
           A.ACCT PO_ACCT_DESC,
           A.UNIT_PRICE,
           A.AMOUNT_ORDERED,
           NVL (
              (SELECT TO_CHAR (RT.TRANSACTION_DATE, ‘yyyy-mm-dd’)
                 FROM RCV_TRANSACTIONS RT
                WHERE     RT.PO_HEADER_ID = A.PO_HEADER_ID
                      AND RT.PO_LINE_ID = A.PO_LINE_ID
                      AND RT.PO_LINE_LOCATION_ID = A.LINE_LOCATION_ID
                      AND RT.PO_DISTRIBUTION_ID = A.PO_DISTRIBUTION_ID
                      AND RT.TRANSACTION_TYPE = ‘DELIVER’
                      AND ROWNUM = 1),
              ‘N’)
              RECIEVED_FLAG,
           B.SEGMENT2 SLA_ACCT,
           B.ACCT SLA_ACCT_DESC,
           B.ENTERED_DR,
           B.ENTERED_CR,
           B.ACCOUNTED_DR,
           B.ACCOUNTED_CR,
           C.SEGMENT2 AP_ACCT,
           C.ACCT AP_ACCT_DESC,
           C.AMOUNT,
           A.SHIP_RECEIPT_FLAG,
           A.DIST_RECEIPT_FLAG
      FROM (SELECT POH.SEGMENT1 PO_NO,
                   POL.LINE_NUM,
                   POLL.LINE_LOCATION_ID,
                   POL.PO_LINE_ID,
                   POL.PO_HEADER_ID,
                   POD.DISTRIBUTION_NUM DIST_NUM,
                   POD.CODE_COMBINATION_ID,
                   POD.PO_DISTRIBUTION_ID,
                   POL.UNIT_PRICE,
                   POD.AMOUNT_ORDERED,
                   GCC.SEGMENT2,
                   GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                      GCC.CHART_OF_ACCOUNTS_ID,
                      GCC.CODE_COMBINATION_ID,
                      ‘.’)
                      ACCT,
                   NVL (POLL.ACCRUE_ON_RECEIPT_FLAG, ‘N’) SHIP_RECEIPT_FLAG,
                   NVL (POD.ACCRUE_ON_RECEIPT_FLAG, ‘N’) DIST_RECEIPT_FLAG,
                   (SELECT SEGMENT1
                      FROM MTL_CATEGORIES_B
                     WHERE CATEGORY_ID = POL.CATEGORY_ID AND ROWNUM = 1)
                      ITEM_CATEGORY,
                   HL_PO_UTL_PKG.IS_CAPITAL (POL.CATEGORY_ID) IS_CAPITAL
              FROM PO_HEADERS_ALL POH,
                   PO_LINES_ALL POL,
                   PO_LINE_LOCATIONS_ALL POLL,
                   PO_DISTRIBUTIONS_ALL POD,
                   GL_CODE_COMBINATIONS GCC
             WHERE     POH.PO_HEADER_ID = POL.PO_HEADER_ID
                   AND POH.PO_HEADER_ID = POD.PO_HEADER_ID
                   AND POL.PO_LINE_ID = POD.PO_LINE_ID
                   AND POH.PO_HEADER_ID = POLL.PO_HEADER_ID
                   AND POL.PO_LINE_ID = POLL.PO_LINE_ID
                   AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
                   AND POD.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                   AND POL.ITEM_ID IS NULL /*AND poh.po_header_id IN
                                           (SELECT * FROM tmp_po_accrue_on_receipt_flag)*/
                                          ) A,
           (SELECT RT.PO_HEADER_ID,
                   RT.PO_LINE_ID,
                   RT.PO_LINE_LOCATION_ID,
                   RT.PO_DISTRIBUTION_ID,
                   RT.VENDOR_ID,
                   RT.VENDOR_SITE_ID,
                   SLA.*
              FROM (SELECT XTE.SOURCE_ID_INT_1,
                           XL.CODE_COMBINATION_ID,
                           XL.ENTERED_DR,
                           XL.ENTERED_CR,
                           XL.ACCOUNTED_DR,
                           XL.ACCOUNTED_CR,
                           GCC.SEGMENT2,
                           GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                              GCC.CHART_OF_ACCOUNTS_ID,
                              GCC.CODE_COMBINATION_ID,
                              ‘.’)
                              ACCT
                      FROM XLA.XLA_AE_HEADERS XH,
                           XLA.XLA_AE_LINES XL,
                           XLA.XLA_TRANSACTION_ENTITIES XTE,
                           XLA.XLA_EVENTS XEA,
                           GL.GL_CODE_COMBINATIONS GCC
                     WHERE     XH.AE_HEADER_ID = XL.AE_HEADER_ID
                           AND XTE.ENTITY_ID = XH.ENTITY_ID
                           AND XL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                           AND XEA.EVENT_ID = XH.EVENT_ID
                           AND XH.BALANCE_TYPE_CODE = ‘A’
                           AND XH.JE_CATEGORY_NAME = ‘Receiving’
                           AND XL.ACCOUNTED_DR IS NOT NULL) SLA,
                   RCV_TRANSACTIONS RT
             WHERE RT.TRANSACTION_ID = SLA.SOURCE_ID_INT_1(+)
                   AND RT.TRANSACTION_TYPE = ‘DELIVER’) B,
           (SELECT AID.DIST_CODE_COMBINATION_ID,
                   POD.PO_DISTRIBUTION_ID,
                   POD.PO_HEADER_ID,
                   POD.PO_LINE_ID,
                   POD.LINE_LOCATION_ID,
                   GCC.SEGMENT2,
                   GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION (
                      GCC.CHART_OF_ACCOUNTS_ID,
                      GCC.CODE_COMBINATION_ID,
                      ‘.’)
                      ACCT,
                   AID.AMOUNT
              FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,
                   PO_DISTRIBUTIONS_ALL POD,
                   GL_CODE_COMBINATIONS GCC
             WHERE     AID.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
                   AND AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                   AND AID.LINE_TYPE_LOOKUP_CODE IN (‘ITEM’, ‘ACCRUAL’)) C
     WHERE     A.PO_HEADER_ID = B.PO_HEADER_ID(+)
           AND A.PO_LINE_ID = B.PO_LINE_ID(+)
           AND A.LINE_LOCATION_ID = B.PO_LINE_LOCATION_ID(+)
           AND A.PO_DISTRIBUTION_ID = B.PO_DISTRIBUTION_ID(+)
           AND A.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+);

    In R12, We Can’t just directly get the Code Combination ( i.e Accounting Flex Field) Description from a single table. But Oracle has provided a package, which will help to get the description easily.

    Script:
    SELECT GCC.CODE_COMBINATION_ID,
           GCC.SEGMENT1,
           GCC.SEGMENT2,
           GCC.SEGMENT3,
           GCC.SEGMENT4,
           GCC.SEGMENT5,
           GCC.SEGMENT6,
           GCC.SEGMENT7,
           GCC.SEGMENT8,
           SUBSTR (
              APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                 GCC.CHART_OF_ACCOUNTS_ID,
                 1,
                 GCC.SEGMENT1),
              1,
              40)
              SEGMENT1_DESC,
           SUBSTR (
              APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                 GCC.CHART_OF_ACCOUNTS_ID,
                 2,
                 GCC.SEGMENT2),
              1,
              40)
              SEGMENT2_DESC,
           DECODE (
              GCC.SEGMENT3,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    3,
                    GCC.SEGMENT3),
                 1,
                 40))
              SEGMENT3_DESC,
           DECODE (
              GCC.SEGMENT4,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    4,
                    GCC.SEGMENT4),
                 1,
                 40))
              SEGMENT4_DESC,
           DECODE (
              GCC.SEGMENT5,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    5,
                    GCC.SEGMENT5),
                 1,
                 40))
              SEGMENT5_DESC,
           DECODE (
              GCC.SEGMENT6,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    6,
                    GCC.SEGMENT6),
                 1,
                 40))
              SEGMENT6_DESC,
           DECODE (
              GCC.SEGMENT7,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    7,
                    GCC.SEGMENT7),
                 1,
                 40))
              SEGMENT7_DESC,
           DECODE (
              GCC.SEGMENT9,
              NULL, ”,
              SUBSTR (
                 APPS.GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL (
                    GCC.CHART_OF_ACCOUNTS_ID,
                    8,
                    GCC.SEGMENT8),
                 1,
                 40))
              SEGMENT8_DESC,
           GCC.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,
           GCC.ACCOUNT_TYPE
      FROM GL_CODE_COMBINATIONS GCC
      WHERE CODE_COMIBINATION_ID = :P_ID

    Where: P_ID, you can pass Code Combination Id to get description for particular Accounting Combination.