Some Important Oracle HRMS Queries

 ----------------------------------------------------------------------------------------
      --- Cursor to check whether a person SSN already exists in the oracle or not
      ----------------------------------------------------------------------------------------
      CURSOR cur_per_ssn (
         p_national_identifier    per_all_people_f.national_identifier%TYPE,
         p_biz_id                 NUMBER)
      IS
         SELECT national_identifier
           FROM apps.per_all_people_f papf
          WHERE papf.national_identifier = p_national_identifier

Oracle Apps Application Object Library SQL scripts

Oracle apps provides a number of scripts which are very useful for the data base administrator for DB monitoring & tracking purpose.

Here is the application server path were you can see the following SQL scripts.

Oracle Applications $FND_TOP/sql directory has a collection of scripts. Most of them can be used for different purposes.

Some frequently used queries for oracle apps developers

Get all the views that refer a particular table:

SELECT o.object_name, o.status, o.last_ddl_time

  FROM SYS.dba_objects o

 WHERE o.object_type = 'VIEW'

   AND EXISTS (

              SELECT 'x'

                   FROM SYS.dba_dependencies d

           WHERE d.referenced_type = 'TABLE'

                 AND d.TYPE = 'VIEW'

                    AND d.owner = 'APPS'

             AND d.NAME = o.object_name

                 AND d.referenced_name LIKE '<TABLE_NAME>');

Useful oracle apps queries/ scripts/ codes/ tips

Visit our new blog: Oracle apps scripts and tips

I am working on this blog post. For now i have placed only few scripts. Keep checking this post for further updates:

• How to get Login Passwords for application & Data Base instances
• Wait for request in Oracle Apps
• Queries related to Security profile in Oracle apps MOAC
• How to add System administrator responsibility from backend
• Useful queries/code/scripts in Oracle Apps Order Management
• Find the Org and Organization mappings
• Frequently used queries for oracle apps developers
• Order Import Interface training with examples
• Different ways to wrap a pl sql code
• How to submit a concurrent request set from backend
• How to submit a concurrent program from backend
• Oracle Apps AOL Sql Scripts
• Sample UTL_FILE Package scripts
• Concurrent Program monitoring Scripts
• Oracle Performance Tuning related Scripts
• Important Join conditions between tables in oracle apps
• Currently running sessions in the database
• How to set org context in oracle apps R12 and 11i
• How to send an Email from PL/SQL
• Amount to words function
• How to Resubmit Unprocessed Transactions Stuck in the MTL_MATERIAL_TRANSACTION_TEMP Table
• Oracle Apps General DBA Scripts
• Important Oracle Purchasing related scripts
• Queries to track issues while period (Inventory / Financial) closure
• Oracle Apps Inventory Management Scripts
• Oracle SQL*Loader
• Sample PL/SQL Programs
• Sample PL/SQL examples

• Script to Apply Hold Via Oe_order_pub.Process_order in R12

• Book an Order Using OE_INBOUND_INT.PROCESS_ORDER

• Update an Existing Order Line using OE_ORDER_PUB.process_order API

• Unreserve an Order Line using OE_ORDER_PUB.process_order API

• Split an Order Line OE_ORDER_PUB.process_order API

• Reserve an Order Line using OE_ORDER_PUB.process_order API

• Release Hold in a Sales Order using OE_ORDER_PUB.process_order API

• Delete an Order using OE_ORDER_PUB.process_order API

• Delete an Order Line OE_ORDER_PUB

• Create an Order With One Line using OE_ORDER_PUB.process_order API

• Cancel an Existing Order using OE_ORDER_PUB.process_order API

• Cancel an Existing Order Line using OE_ORDER_PUB

• Apply Hold to a Sales Order using OE_ORDER_PUB.process_order API

• Add A New Line To An Existing Order using process_order API

• Create an RMA via Process Order API

• Create an Order via Order Import For A Simple ATO Model

• Update Order Header Details Using Process_Order API For R12

• Book an Order Using Process_Order Api

• Oracle Apps Cancel PO API Training

• Oracle Apps Change PO API Training

• Create Requisitions Using Requisition Import - Training
• Sample SQL Queries
• Oracle apps OM Process order API