, , , , , ,

How to Get IP address and host address in Oracle

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

    /by
    , , , ,

    R12 – Meaning for Concurrent Request Status Codes and Phase Codes

    Concurrent Request Phase Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_PHASE_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    C
    Completed
    I
    Inactive
    P
    Pending
    R
    Running

    Concurrent Request Status Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_STATUS_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    R
    Normal
    I
    Normal
    Z
    Waiting
    D
    Cancelled
    U
    Disabled
    E
    Error
    M
    No Manager
    C
    Normal
    H
    On Hold
    W
    Paused
    B
    Resuming
    P
    Scheduled
    Q
    Standby
    S
    Suspended
    X
    Terminated
    T
    Terminating
    A
    Waiting
    G
    Warning

    Normally a concurrent request proceeds through three, possibly four, life cycle stages or phases,

    Phase Code
    Meaning with Description
    Pending
    Request is waiting to be run
    Running
    Request is running
    Completed
    Request has finished
    Inactive
    Request cannot be run

    Within each phase, a request’s condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

    The status and the description of each meaning given below:

    Phase
    Status
    Description
    PENDING
    Normal
    Request is waiting for the next available manager.
    Standby
    Program to run request is incompatible with other program(s) currently running.
    Scheduled
    Request is scheduled to start at a future time or date.
    Waiting
    A child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.



    RUNNING
    Normal
    Request is running normally.
    Paused
    Parent request pauses for all its child requests to complete. For example, a report set pauses for all reports in the set to complete.
    Resuming
    All requests submitted by the same parent request have completed running. The Parent request is waiting to be restarted.
    Terminating
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    COMPLETED
    Normal
    Request completes normally.
    Error
    Request failed to complete successfully.
    Warning
    Request completes with warnings. For example, a report is generated successfully but fails to print.
    Cancelled
    Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
    Terminated
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    INACTIVE
    Disabled
    Program to run request is not enabled. Contact your system administrator.
    On Hold
    Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
    No Manager
    No manager is defined to run the request. Check with your system administrator.
    /by
    , , ,

    R12 – How to See the AP Invoices Interface from the Front end?

    Let’s say Invoices are imported from External Systems – irrespective of the transportation layer / method. Instead of viewing the Interface tables in the back end – say AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE, they can be viewed in the front-end.


    Responsibility – Payables Manager
    Navigation – Invoices – Entry – Open Interface Invoices
    Clicking on the above link, opens the below form ..
    Open the Query-mode to view the Invoice in the Interface table
    Above is the header line. Click on Lines to view the lines information.
    This would avoid looking for Invoices by querying in the back-end.
    /by
    , , , , ,

    How to Setup for E-Business Suite Tax on Payable Invoices in R12

    Use E-Business Tax to set up and maintain your transaction tax requirements in all geographic locations where you do business. You can set up tax configurations to include the rules, default values, and other information necessary for each separate tax requirement. At transaction time, E-Business Tax uses your tax configuration to determine the taxes that apply to each transaction and to calculate the tax amounts.

    With E-Business Tax, you can:
    • Set up and maintain a tax configuration for each tax that you are subject to.
    • Set up and maintain records for your legal entities and operating units and the taxes they are subject to.
    • Manage the sharing of tax configuration data by the legal entities and operating units in your organization.
    • Set up and maintain tax registrations and classifications for your legal establishments and third parties.
    • Set up and maintain classifications of the products that you buy and sell.
    • Set up and maintain classifications for your transactions.
    • Set up and maintain tax rules and default values to manage tax determination and tax recovery on your transactions:
    • Set up default values and a minimum number of tax rules for simple tax requirements.
    • Set up default values and a comprehensive set of tax rules to manage complex tax requirements.
    • Set up and maintain tax-related records for your important transactions.
    • Set up and maintain automatic accounting of all tax-related transactions.
    • Manage user control of updates and overrides of tax information on transactions.
    • Set up and maintain codes for tax reporting purposes.
    • Run a full set of reports for your tax authority tax requirements.
    • Set up and maintain access to third party tax calculation services.

    You can use the E-Business Tax Home page to manage access to all parts of the E-Business Tax system for setup and maintenance. The tasks involved in setting up a tax requirement in E-Business Tax fall into three general categories:
    1. Setting up transaction taxes. 
    2. Completing all of the setups and settings related to the processing of taxes on transactions. 
    3. Setting up tax rules and defaults to manage tax processing.

    Steps for E-Business Suite Tax:

    Step 1: Create Tax Regime
    Step 2: Create Tax
    Step 3: Create Tax Status
    Step 4: Create Jurisdiction
    Step 5: Create Rate
    Step 6: Enter Tax Accounts
    Step 7: Create Tax Rules (Populate values for all defaults)
    Step 8: Make Tax Available for Transaction
    Step 9: Test the Tax

    Check how the Party/OU has been setup, Tax Manager/Administrator > Parties > Party Tax Profiles


    Query for the following:
    Party Type: Operating Unit Owning Tax Content

    Party Name: Vision Operations (Name of the OU you wish to setup for tax)

    Click Go

    Click View Tax Profile

    Confirm if ‘Use Subscription of the Legal Entity’ is checked.

    Step 1: Create Tax Regime

    Navigation : Tax Manager/Administrator > Tax Configuration > Tax Regimes

    Click Create to enter a new Tax Regime.

    We are going to create a United Kingdom regime.

    Enter the following:
    Field                                       Value
    Tax Regime Code            XXX UK REGIME
    Name                             XXX UK REGIME
    Regime Level                  Country
    Country Name                United Kingdom
    Effective From                10-Jan-2000
    Parent Regime Code       NULL
    Used to Group Regimes   Unchecked

    Click on Show Controls and Defaults.



    Enter settings for Controls and Defaults as follows:
    Field
    Value
    Controls:

    Allow Tax Recovery
    Unchecked
    Allow Override and Entry of Inclusive Tax Lines 
    Checked
    Allow Tax Exemptions
    Checked
    Allow Tax Exceptions
    Checked
    Defaults:

    Tax Currency
    GBP
    Minimum Accountable Unit
    0.01 
    Rounding Rule
    Nearest
    Tax Precision
    2
    Allow Multiple Jurisdictions
    Unchecked

     Click Continue



    Enter the following information:
    Field
    Value
    Party Name
    Operating Unit
    Party Type
    Operating Unit Owning Tax Content *
    Configuration for Taxes and Rules
    Common Configuration
    Configuration for Product Exceptions
    Common Configuration
    Effective From
    10-Jan-2000
    * If ‘Use Subscription of the Legal Entity’ was checked in the prereq step then this would have been entered against the First Party Legal Entity and not the OU

    Click on Finish
    Ensure you see confirmation message:

     Step 2: Create Tax

    Navigation: Tax Manager/Administrator > Tax Configuration > Taxes


    Click on Create

    Enter the following information:
    Field
    Value
    Tax Regime Code
    XXX UK REGIME (From LOV – Regime defined in Step 1)
    Configuration Owner     
    Global Configuration Owner
    Tax Source
    Create a New Tax
    Tax
    XXX UK TAX
    Tax Name
    XXX UK TAX
    Tax Type
    VAT
    Geography Type
    Country
    Parent Geography Type
    Country
    Parent Geography Name
    United Kingdom
    Set as Offset Tax
    Unchecked
    Set Tax for Reporting Purposes
    Unchecked


    Click on Show Controls and Defaults

    Enter the following information:
    Field
    Value
    Controls:

    Allow Tax Inclusion
    Unchecked
    Allow Override and Entry of Inclusive Tax Lines
    Checked
    Allow Tax Rounding Override
    Checked
    Allow Override for Calculated Tax Lines
    Checked
    Use Legal Registration Number
    Unchecked
    Allow Multiple Jurisdictions

    Allow Mass Creation of Jurisdictions
    Checked
    Defaults:
    Checked
    Allow Tax Rate Rules
    Checked



    Click Apply and ensure the confirmation message appears.

    Step 3: Create Tax Status
    Tax Manager/Administrator > Tax Configuration > Tax Statuses
    Click Create


    Enter the following information:

    Field
    Value
    Tax Regime
    XXX UK REGIME (From LOV in Step 1)
    Tax
    XXX UK TAX (From LOV in Step 2)
    Tax Status Code
    XXX UK STATUS
    Name
    XXX UK STATUS
    Effective From
    10-Jan-2000 (Defaults)
    Set as Default Tax Status
    Checked
    Default Status Effective From
    10-Jan-2000 *
    * Does not default and is not required so remember to enter this.

     Click Apply

    View confirmation message:



    Step 4: Create Jurisdiction
    Tax Manager/Administrator > Tax Configuration > Tax Jurisdiction
    Click Create        (see above screenshot)
    Enter the following information:

    Field
    Value
    Tax Jurisdiction Code
    XXX UK JURISDICTION
    Tax Jurisdiction Name
    XXX UK JURISDICTION
    Tax Regime Code
    XXX UK REGIME (From LOV Step 1)
    Tax        
    XXX UK TAX (From LOV Step 2)
    Geography Type
    COUNTRY
    Effective From
    10-JAN-2000
    Geography Name
    United Kingdom
    Default Tax Jurisdiction Settings:

    Set as default Tax Jurisdiction
    Checked
    Default Effective From
    10-Jan-2000

    Click Apply

    Ensure confirmation message is received.


    Step 5: Create Rate
    Tax Manager/Administrator > Tax Configuration > Tax Rates
    Click Create (screenshot above)

    Enter the following information:
    Field
    Value
    Tax Regime Code
    XXX UK REGIME (From LOV Step 1)
    Configuration Owner
    Global Configuration Owner
    Tax
    XXX UK TAX (From LOV Step 2)
    Tax Status Code
    XXX UK STATUS (From LOV Step 3)
    Tax Jurisdiction Code
    XXX UK JURISDICTION (From LOV Step 4)
    Tax Rate Code
    XXX UK RATE
    Rate Type
    Percentage
    Rate Periods:

    Percentage Rate
    20
    Effective From 
    01-Jan-2000


     Click on Rate Details button and scroll down


    Enter the following:
    Field
    Value
    Set as Default Rate
    Checked
    Default Effective Date
    01-Jan-2000 

    Click Apply. & Click Apply again.
    Ensure confirmation message is received
      
    Step 6: Enter Tax Accounts

    Tax Manager/Administrator > Tax Configuration > Taxes

    Query XXX UK Tax -> Click Go -> Click Tax Accounts


      Search for Vision Operations OU and click create


    Enter the Operating Unit and populate Tax Expense and Tax Recoverable/Liability accounts.

     

    Click Apply. &  Click Apply againClick Apply a third time

     

    Step 7: Create Tax Rules (Populate values for all defaults)

    Tax Manager/Administrator > Tax Configuration > Tax Rules

    Enter the following:

    Field
    Value
    Configuration Owner
    Global Configuration Owner
    Tax Regime Code
    XXX UK REGIME
    Tax
    XXX UK TAX


      

    Click Go to search for existing default rule

      


    Click the pencil icon in the Set Default column to change any of the defaults defined


    Update the defaults as follows:
    Field
    Value
    Determine Place of Supply
    Ship to, use bill to if ship to is not found
    Determine Tax Applicability
    Applicable
    Determine Tax Registration
    STANDARD_TB
    Calculate Tax Amounts
    STANDARD_TC

    Step 8: Make Tax Available for Transaction

    Tax Manager/Administrator > Tax Configuration > Taxes 

    Query Tax   

    Click on Update Pencil Icon

    Check the box ‘Make Tax Available for Transactions’

     


    Click Apply



    This warning is ok since we know we didn’t enter an exchange rate type.

    Click Yes and receive confirmation of changes saved

     

    Step 9: Test the Tax

    Payables > Invoices : Entry > Invoices



    Enter Invoice for Supplier with a UK Ship-To addressEnter Line information


    Ensure the SHIP TO location at the line level contains a UK location (As that is how we determine place of supply)




    Click distributions
    Enter Invoice distribution


    Save
    Go back to Invoices and click Calculate Tax
     


    Tax line is created – click on Tax Details 

    You can see that the SAM UK REGIME has been applied and tax calculated at 20%.
    /by
    , , , ,

    How to Define Ledger Set In R12

    A ledger set is a group of ledgers that share the same chart of accounts and calendar/period type combination. Ledger sets allow you to run processes and reports for multiple ledgers simultaneously.

    For example, you can open/close periods for multiple ledgers at once, run recurring journals that update balances for multiple ledgers, or run consolidated financial reports that summarize balances across multiple ledgers in a ledger set. You can group all types of ledgers in a ledger set, such as primary ledger, secondary
    ledgers, and reporting currencies (journal and subledger levels), as long as they share the same chart of accounts and calendar/period type combination. The same ledger can belong to multiple ledger sets, and ledger sets can contain other ledger sets.

    To define a ledger set:

    Navigation: General Ledger –> Setup –> Financials –> Ledger sets.

    1. Navigate to the Ledger Set window.

    2. Enter a name for the Ledger Set.

    3. Enter a Short Name for the ledger set.

    4. (Optional) Enter a Description for the ledger set.

    5. Choose a Chart of Accounts.

    6. Choose a Calendar and Period Type.

    7. (Optional) Specify a default ledger. The default ledger automatically defaults in all windows where the Ledger field is required.
    Note: A Default Ledger is required for Financial Statement Generator (FSG).

    8. (Optional) Select the Enable Security checkbox to secure the Ledger Set definition. If you do not enable security, all users who have access to this definition will be able to use, view, and modify the ledger set definition. If the Assign Access function is available for your responsibility, the Assign Access
    button will be enabled once you select the Enable Security checkbox. Choose the Assign Access button to assign the definition to one or more Definition Access Sets with the desired privileges.

    9.In the Ledger/Ledger Set column, choose the ledgers and/or ledger sets to be included in the ledger set. Only those ledgers and ledger sets that share the same chart of accounts, calendar, and period type specified for the ledger set definition will be available.
    If you use reporting currencies (journal or subledger level), you can choose reporting currencies to be included in the ledger set. Only those reporting currencies that share the same chart of accounts, calendar, and period type specified for the ledger set definition will be available

    10. Save your work. The General Ledger Accounting Setup Program will be submitted. Ensure this program completes successfully. Once saved, a ledger set cannot be deleted. You can only add or remove ledgers
    and ledger sets from ledger sets.

    Note: You must have at least one ledger or ledger set assigned to a ledger set. Before you can begin using the ledgers contained in your ledger set for transaction processing, you must assign the ledger set to the profile option, GL: Data Access Set.



    Save.
    Once you create the ledger set system default created a data access set with the same name of the our Ledger set name.

    Navigation: General Ledger –> Setup –> Financials –>Data Access Set

    Assign this ledger set to your Responsibility.
    Navigation: System administrator –> Profiles –> Systems.


    Click on find

    Save.
    Now we can access one or more Ledgers at a time.

    /by