Validated Unpaid Invoices in Oracle AP

  SELECT hou.name OU_Name,

         i.invoice_num,

         v.vendor_name supplier_name,

         i.invoice_date,

         ps.due_date,

         i.amount_paid,

         --i.invoice_amount,ps.amount_remaining,

         SUM (i.invoice_amount) invoice_amount,

         SUM (ps.amount_remaining) amount_remaining

    FROM apps.ap_payment_schedules_all ps,

         apps.ap_invoices_all i,

         apps.po_vendors v,

         apps.po_vendor_sites_all vs,

         apps.hr_operating_units hou

   WHERE     i.invoice_id = ps.invoice_id

         AND i.vendor_id = v.vendor_id

         AND i.vendor_site_id = vs.vendor_site_id

         AND i.payment_status_flag = 'N'

         AND (NVL (ps.amount_remaining, 0) * NVL (i.exchange_rate, 1)) != 0

         AND DECODE (APPS.Ap_Invoices_Pkg.GET_APPROVAL_STATUS (

                        i.INVOICE_ID,

                        i.INVOICE_AMOUNT,

                        i.PAYMENT_STATUS_FLAG,

                        i.INVOICE_TYPE_LOOKUP_CODE),

                     'NEVER APPROVED', 'Never Validated',

                     'NEEDS REAPPROVAL', 'Needs Revalidation',

                     'Validated') = 'Validated'

         AND i.org_id = hou.organization_id

         AND ps.due_date > SYSDATE

GROUP BY hou.name,

         v.vendor_name,

         i.invoice_num,

         i.invoice_date,

         ps.due_date,

         i.invoice_amount,

         i.amount_paid,

         ps.amount_remaining

ORDER BY v.vendor_name, i.invoice_num;

Oracle Query to Replace Special Characters from a string

SELECT REGEXP_REPLACE('##$!%*~``$123&&!!__!','[^[:alnum:]'' '']', NULL) FROM dual

Oracle Queries/Commands to Kill Session

select * from v$access 

where object = 'XX_PACKAGE_NAME';

/* Pick the sids from above query and pass to the below query */

select * from v$session

where sid in (322,368);

Syntax: ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

ALTER SYSTEM KILL SESSION '322,48848';

Query to Find Oracle Concurrent Request Trace File Location

/* In the below query pass request id value to bind variable &request_id*/
SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request_id
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

Register a View as Table with Oracle Applications

/* In the below commands replace XXXX with the custom schema name where the view is created*/

CREATE OR REPLACE VIEW XXXX_Product_view AS (SELECT DISTINCT segment5 PRODUCT FROM gl_code_combinations);

CREATE OR REPLACE PUBLIC SYNONYM XXXX_PRODUCT FOR XXXX_Product_view;

EXEC ad_dd.register_table('XXXX','XXXX_PRODUCT','T');

EXEC ad_dd.register_column('XXXX','XXXX_PRODUCT','PRODUCT',1,'Varchar2',30,'N','N');

Script to Reset Oracle Application User Password from Backend

BEGIN
   IF fnd_user_pkg.changepassword ('&uname', '&pswd')
   THEN
      DBMS_OUTPUT.put_line ('Password changed successfully');
   ELSE
      DBMS_OUTPUT.put_line ('Failed changing password');
   END IF;
COMMIT;
END;

Query to retrieve Oracle open AR invoices by Batch Source

select b.trx_number,b.trx_date,x.name trx_type from apps.ra_customer_trx_all b
,apps.ar_payment_schedules_all a
,apps.ra_batch_sources_all d
,apps.ra_cust_trx_types_all x
where a.customer_trx_id=b.customer_trx_id
and d.batch_source_id=b.batch_source_id
and x.cust_trx_type_id=b.cust_trx_type_id
and amount_due_remaining<>0

Script to Add Responsibility to a Oracle Application User from Backend

/* Below script adds Sys Admin Responsibility to user FIRST.LAST*/
DECLARE
   lc_user_name              VARCHAR2 (100) := 'FIRST.LAST';
   lc_resp_appl_short_name   VARCHAR2 (100) := 'SYSADMIN';
   lc_responsibility_key     VARCHAR2 (100) := 'SYSTEM_ADMINISTRATOR';
   lc_security_group_key     VARCHAR2 (100) := 'STANDARD';
   ld_resp_start_date        DATE           := SYSDATE;
   ld_resp_end_date          DATE           := NULL;
BEGIN
   fnd_user_pkg.addresp (
                         username            => lc_user_name,
                         resp_app            => lc_resp_appl_short_name,
                         resp_key            => lc_responsibility_key,
                         security_group      => lc_security_group_key,
                         description         => NULL,
                         start_date          => ld_resp_start_date,
                         end_date            => ld_resp_end_date
                        );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

Cursor Attributes in Oracle PL/SQL

You can manipulate cursors using the OPEN, FETCH, and CLOSE statements. When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

Attribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Example:
DECLARE
   CURSOR emps
   IS
        SELECT *
          FROM employees
         WHERE ROWNUM < 6
      ORDER BY 1;
   emp   employees%ROWTYPE;
   row   NUMBER := 1;
BEGIN
   OPEN emps;
   FETCH emps INTO emp;
   LOOP
      IF emps%FOUND
      THEN
         DBMS_OUTPUT.put_line (
            'Looping over record ' || row || ' of ' || emps%ROWCOUNT);
         FETCH emps INTO emp;
         row := row + 1;
      ELSIF emps%NOTFOUND
      THEN
         EXIT;
      END IF;
   END LOOP;
   IF emps%ISOPEN
   THEN
      CLOSE emps;
   END IF;
END;
/

Oracle OTL Timecard Query

  SELECT ppf.full_name,
         ppf.email_address,
         ppf.person_id,
         htbb1.start_time week_start_date,
         htbb1.stop_time week_end_date,
         hts.submission_date submitted_date,
         day.start_time day,
         detail.measure hours,
         ppa.project_number,
         pt.task_number
    FROM apps.per_all_people_f ppf,
         apps.hxc_time_building_blocks htbb1,
         apps.hxc_time_building_blocks DAY,
         apps.hxc_time_building_blocks detail,
         apps.hxc_base_pa_online_projects_v ppa,
         apps.hxc_base_pa_online_tasks_v pt,
         apps.hxc_time_attribute_usages htau,
         apps.hxc_time_attribute_usages htau1,
         apps.hxc_time_attributes hta,
         apps.hxc_bld_blk_info_types hbbit,
         apps.hxc_time_attributes hta1,
         apps.hxc_timecard_summary hts
   WHERE     1 = 1
         AND htbb1.resource_id = ppf.person_id
         AND htbb1.SCOPE = 'TIMECARD'
         AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                                 AND NVL (ppf.effective_end_date,
                                          TRUNC (SYSDATE))
         AND DAY.parent_building_block_id = htbb1.time_building_block_id
         AND DAY.parent_building_block_ovn = htbb1.object_version_number
         AND DAY.SCOPE = 'DAY'
         AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
         AND detail.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
         AND detail.SCOPE = 'DETAIL'
         AND detail.parent_building_block_id = DAY.time_building_block_id
         AND detail.parent_building_block_ovn = DAY.object_version_number
         AND hta.attribute1 = ppa.project_id
         AND hta.attribute2 = pt.task_id
         AND ppa.project_id = pt.project_id
         --
         AND htau.time_building_block_id = detail.time_building_block_id
         AND htau.time_building_block_ovn = detail.object_version_number
         AND htau1.time_building_block_id = detail.time_building_block_id
         AND htau1.time_building_block_ovn = detail.object_version_number
         AND htau.time_attribute_id = hta.time_attribute_id
         AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id
         AND hbbit.bld_blk_info_type = 'PROJECTS'
         AND htau1.time_attribute_id = hta1.time_attribute_id
         AND hta1.attribute_category = 'SECURITY'
         AND hts.timecard_id = htbb1.time_building_block_id
         AND NVL (hts.timecard_ovn, htbb1.object_version_number) =
                htbb1.object_version_number
        --AND ppf.full_name LIKE 'XX_FULLNAME%'      
        --AND htbb1.stop_time BETWEEN TO_DATE ('01/01/2014', 'MM/DD/YYYY')
                                 AND TO_DATE ('02/15/2014', 'MM/DD/YYYY')
         --AND hts.submission_date >= TO_DATE ('03/18/2014', 'MM/DD/YYYY')
ORDER BY day.start_time DESC, htbb1.start_time DESC

DB Link Creation Command to connect to Oracle Database

CREATE DATABASE LINK XX_DBLINK_NAME CONNECT TO XX_USER IDENTIFIED BY XX_PASSWORD USING ' (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST= XX_HOSTNAME)
      (PORT= XX_PORTNO)
    )
    (CONNECT_DATA=
      (SERVICE_NAME= XX_SERVICE_NAME)
    )
  )'
/

In the above command replace:

XX_DBLINK_NAME with new Database Link Name to be created
XX_USER with the database username of the target instance
XX_PASSWORD with database password of the target instance
XX_HOSTNAME with the hostname of the target instance
XX_PORTNO with the port number of the target instance
XX_SERVICE_NAME with the service name of the target instance


Hint: You can replace the TNSNAMES of the target instance you are connecting to after the USING clause in the above statement

Query to find Oracle Concurrent Program Last Run Time:

SELECT DISTINCT user_concurrent_program_name,

                (SELECT MAX (actual_start_date)

                   FROM APPS.FND_CONCURRENT_REQUESTS x

                  WHERE x.concurrent_program_id = fcp.concurrent_program_id)

                   last_run_date,

                fu.user_name,

                frt.responsibility_name

  FROM apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_requests fcr,

       apps.fnd_user fu,

       apps.fnd_responsibility_tl frt

 WHERE     1 = 1

       AND fcp.concurrent_Program_id = fcpt.concurrent_program_id

       AND fcp.concurrent_program_id = fcr.concurrent_program_id(+)

       AND fu.user_id = fcr.requested_by

       AND frt.responsibility_id = fcr.RESPONSIBILITY_ID

       AND user_concurrent_program_name = 'XXXX Program Name'

Query to find Oracle Concurrent Program Request groups Assignment:

SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",

       DECODE(rgu.request_unit_type,

              'P', 'Program',

              'S', 'Set',

              rgu.request_unit_type)        "Unit Type",

       cp.concurrent_program_name           "Concurrent Program Short Name",

       rg.application_id                    "Application ID",

       rg.request_group_name                "Request Group Name",

       fat.application_name                 "Application Name",

       fa.application_short_name            "Application Short Name",

       fa.basepath                          "Basepath"

  FROM apps.fnd_request_groups          rg,

       apps.fnd_request_group_units     rgu,

       apps.fnd_concurrent_programs     cp,

       apps.fnd_concurrent_programs_tl  cpt,

       apps.fnd_application             fa,

       apps.fnd_application_tl          fat

 WHERE rg.request_group_id       =  rgu.request_group_id

   AND rgu.request_unit_id       =  cp.concurrent_program_id

   AND cp.concurrent_program_id  =  cpt.concurrent_program_id

   AND rg.application_id         =  fat.application_id

   AND fa.application_id         =  fat.application_id

   AND cpt.language              =  USERENV('LANG')

   AND fat.language              =  USERENV('LANG')

   AND cpt.user_concurrent_program_name = :Program_Name;

with responsibility names and request groups:

SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       frv.responsibility_name              "Responsibility Name",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"   
  FROM apps.fnd_request_groups          rg,
       apps.fnd_request_group_units     rgu,
       apps.fnd_concurrent_programs     cp,
       apps.fnd_concurrent_programs_tl  cpt,
       apps.fnd_application             fa,
       apps.fnd_application_tl          fat,
       apps.FND_RESPONSIBILITY_VL frv
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND rg.request_group_id           = frv.request_group_id 

   AND ((frv.end_date is null) or (trunc(frv.end_date) >= SYSDATE))                   
   AND cpt.user_concurrent_program_name =:Program_Name;

Query to find Oracle Users having access to a responsibility

SELECT fu.user_name,

       frt.responsibility_name,

       furg.start_date resp_start_date,

       furg.end_date resp_end_date

  FROM apps.fnd_user_resp_groups furg,

       apps.fnd_responsibility fr,

       apps.fnd_responsibility_tl frt,

       apps.fnd_user fu

 WHERE     1 = 1

       AND fu.user_id = furg.user_id

       AND furg.responsibility_id = fr.responsibility_id

       AND frt.responsibility_id = fr.responsibility_id

       AND frt.responsibility_name LIKE 'XXX Resp Name%'

       order by frt.responsibility_name,fu.user_name

Query to get Oracle Concurrent Program completion time:

select  user_concurrent_program_name,

(round((actual_completion_date - actual_start_date)*24,2)) completion_hrs,

(round((actual_completion_date - actual_start_date)*24*60,2)) completion_mins,

actual_start_date, 
actual_completion_date,
argument_text parameters,
a.* 
from apps.fnd_concurrent_requests a,

apps.fnd_concurrent_programs_tl b

where a.concurrent_program_id = b.concurrent_program_id

and user_concurrent_program_name = 'XXX Program Name'

order by requested_start_date desc;

Query to find Scheduled Oracle Concurrent Program Details

SELECT req.request_id

, decode (prg.user_concurrent_program_name, 'Report Set', 'Report Set:' || req.description, prg.user_concurrent_program_name) AS name

, argument_text as parameters

, req.resubmit_interval

, nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')) AS schedule_type

, decode (nvl2 (req.resubmit_interval, 'Periodically', nvl2 (req.release_class_id, 'On specific days', 'Once')),

'Periodically', 'Every ' || req.resubmit_interval || ' ' || lower(req.resubmit_interval_unit_code) || ' from ' || lower(req.resubmit_interval_type_code) || ' of previous run',

'Once', 'At :' || to_char (req.requested_start_date, 'DD-MON-RR HH24:MI'), 'Every: ' || crc.class_info) as schedule

, fus.user_name as owner

, to_char(requested_start_date,'DD-MON-YYYY HH24:MI:SS') as next_submission

FROM

apps.fnd_concurrent_programs_tl prg

, apps.fnd_concurrent_requests req

, apps.fnd_user fus

, apps.fnd_conc_release_classes crc

WHERE 1=1

AND prg.application_id = req.program_application_id

AND prg.concurrent_program_id = req.concurrent_program_id

AND req.requested_by = fus.user_id

AND req.phase_code = 'P'

AND req.requested_start_date > sysdate

AND prg.language = 'US'

AND crc.release_class_id(+) = req.release_class_id

AND crc.application_id(+) = req.release_class_app_id

AND prg.user_concurrent_program_name = 'XXX Program'

ORDER BY name;