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