Friday, October 28, 2016

Query to find suppliers with zip codes other than numeric and dash characters

  SELECT pv.vendor_name,
         pv.segment1 vendor_number,
         pvs.vendor_site_code,
         pvs.ADDRESS_LINE1,
         pvs.ADDRESS_LINE2,
         pvs.ADDRESS_LINE3,
         pvs.state,
         pvs.City,
         pvs.zip,
         pvs.country,
         hou.NAME OU
    FROM apps.po_vendors pv,
         apps.po_vendor_sites_all pvs,
         apps.hr_operating_units hou
   WHERE     pv.vendor_id = pvs.vendor_id
         AND pvs.org_id = hou.organization_id
         AND pv.ENABLED_FLAG = 'Y'
         AND NVL (pv.END_DATE_ACTIVE, SYSDATE + 1) >= SYSDATE
         AND NVL (pvs.INACTIVE_DATE, SYSDATE + 1) >= SYSDATE
         AND pvs.country = 'US'
         AND TRANSLATE (zip,
                        CHR (0) || '0123456789-' || CHR (9),
                        CHR (0))
                IS NOT NULL
ORDER BY pvs.org_id, pv.vendor_name

Tuesday, September 13, 2016

Discoverer Report Last Run By Stats Query

SELECT qs_doc_name disc_rpt_name,
         qs_doc_owner disc_rpt_owner,
         (SELECT user_name
            FROM fnd_user
           WHERE '#' || user_id = qs_created_by)
            disc_rpt_run_by,
         qs_created_date disc_rpt_run_date
    FROM disprd.eul5_qpp_stats
   WHERE qs_doc_name = 'XX Disc Report Name'
ORDER BY qs_created_date DESC

Monday, December 28, 2015

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;

Wednesday, September 16, 2015

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');


SQL Query to Find Oracle Web ADI Importer Package Procedure Name

 SELECT ba.attribute2     wed_adi_package_procedure_name   FROM apps.bne_attributes      ba,        apps.bne_param_lists_b   bplb,        ap...