Invoice Headers and Accounted and Validated Status Info

If you can log into your database as the APPS user, or something like that, this can be useful to finding out the approval status, accounted status and things like that.

-- ##############################################################################
--      INVOICE DETAILS - EXTRA INFO
-- ############################################################################*/
 
SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , aia.invoice_date
     , aia.validation_request_id
     , aia.validation_worker_id
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
     , aia.cancelled_date
     , DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) validation_status_v1 -- http://m-burhan.blogspot.co.uk/2012/06/function-which-provide-ap-validation.html
     , DECODE(apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL', 'Fully Applied', 'UNAPPROVED' , 'Unvalidated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'APPROVED', 'Validated', 'NEVER APPROVED', 'Never Validated', 'CANCELLED', 'Cancelled', 'UNPAID', 'Unpaid', 'AVAILABLE', 'Available') validation_status_v2 -- https://community.oracle.com/thread/3573183
     , apps.ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
     , apps.ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
     , apps.ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_terms_tl att            ON aia.terms_id = att.term_id
  JOIN applsys.fnd_user fu           ON aia.created_by = fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id = pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id 
 WHERE 1 = 1
--   AND pv.vendor_name = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (252962,2088392,2165995,2500940,273058)                  -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2317661,2353095)          -- ## VOUCHER ## --
   AND DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) = 'Needs Revalidation'
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      EXTRA INVOICE DETAILS
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , DECODE(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) validation_status_v1 -- http://m-burhan.blogspot.co.uk/2012/06/function-which-provide-ap-validation.html
--     , DECODE(apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL', 'Fully Applied', 'UNAPPROVED' , 'Unvalidated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'APPROVED', 'Validated', 'NEVER APPROVED', 'Never Validated', 'CANCELLED', 'Cancelled', 'UNPAID', 'Unpaid', 'AVAILABLE', 'Available') validation_status_v2 -- https://community.oracle.com/thread/3573183
--     , ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
--     , ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
--     , ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND pv.vendor_name = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (2109872)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2039086)         -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers and Invoice Distributions

This SQL includes invoice distributions, and also Project details.

-- ##############################################################################
--      INVOICES INCLUDING DISTRIBUTIONS AND PROJECTS
-- ############################################################################*/
 
    SELECT aia.invoice_id inv_id
         , aia.invoice_num inv_num
         , aia.doc_sequence_value vchr
         , aia.invoice_amount inv_amt
         , aia.invoice_type_lookup_code
         , ppa.segment1 project
         , pt.task_number
         , pv.vendor_name supplier
         , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 account
         , gcc.enabled_flag
         , gcc.detail_posting_allowed_flag
         , gcc.detail_budgeting_allowed_flag
         , gcc.last_update_date last_update_gcc
         , aida.distribution_line_number
         , aida.creation_date
         , aida.invoice_distribution_id
         , aida.posted_flag
         , aida.last_update_date
         , aida.last_updated_by
         , aida.line_type_lookup_code
         , aida.period_name
         , aida.amount
         , (REPLACE(REPLACE(aida.description,CHR(10),''),CHR(13),' ')) distrib_description
         , aida.match_status_flag
         , aida.quantity_invoiced
         , aida.encumbered_flag
         , aida.pa_addition_flag
         , '#######################'
         , aia.creation_date inv_created
         , aida.creation_date dist_created
         , aida.expenditure_item_date
      FROM ap.ap_invoices_all aia
      JOIN ap.ap_invoice_distributions_all aida ON aia.invoice_id =                aida.invoice_id
      JOIN ap.ap_terms_tl att                   ON aia.terms_id =                  att.term_id
      JOIN applsys.fnd_user fu                  ON aia.created_by =                fu.user_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =                 pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa        ON aia.vendor_site_id =            pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
      JOIN gl.gl_code_combinations gcc          ON aida.dist_code_combination_id = gcc.code_combination_id
      JOIN ap.ap_suppliers pv                   ON aia.vendor_id =                 pv.vendor_id
 LEFT JOIN pa.pa_projects_all ppa               ON aida.project_id =               ppa.project_id
 LEFT JOIN pa.pa_tasks pt                       ON aida.task_id =                  pt.task_id
     WHERE 1 = 1
--       AND aia.doc_sequence_value IN (2683928)
       AND aia.invoice_id = 1966606
--       AND ABS(aida.amount) = 12.13
       AND ppa.segment1 = 'ABC12345'
--       AND aia.invoice_id IN (2110097, 2110217, 2111004, 2111694, 2111857)
--       AND aida.invoice_distribution_id IN (40573879,40573874,40573875)
       AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      invoices including distributions and projects
-- ############################################################################*/

SELECT aia.invoice_id inv_id
     , aia.invoice_num inv_num
     , aia.doc_sequence_value vchr
     , aia.invoice_amount inv_amt
     , aia.invoice_type_lookup_code
     , aida.creation_date cr_dt
     , aida.last_update_date upd_dt
     , aida.amount dist_amt
     , aida.base_amount base_amt
     , aida.distribution_line_number dist_line
     , aida.invoice_distribution_id
     , aida.pa_addition_flag
     , ppa.segment1 proj
     , ppa.project_id
     , aida.period_name
     , aida.line_type_lookup_code   
     , aida.po_distribution_id
     , aida.accounting_event_id
     , aida.request_id
     , gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6
     , gcc.detail_posting_allowed_flag, gcc.detail_budgeting_allowed_flag, gcc.enabled_flag
     , gcc.end_date_active
     , gcc.last_update_date
     , aida.awt_gross_amount
     , aida.awt_withheld_amt
     , aia.pre_withholding_amount
--     , ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
--     , '###############'
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , ap.ap_suppliers pv
     , ap.ap_supplier_sites_all pvsa
     , gl.gl_code_combinations gcc
     , pa.pa_projects_all ppa
 WHERE aia.invoice_id = aida.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND aida.dist_code_combination_id = gcc.code_combination_id
   AND aida.project_id = ppa.project_id(+)
--   AND ppa.segment1 = 'P117684'
--   AND aia.invoice_id IN (460808)              -- ##   ID    ## --
--   AND aia.invoice_num = '91420'               -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2439927)       -- ## VOUCHER ## --
   AND aida.invoice_distribution_id = 37143990 
   AND 1 = 1
ORDER BY aia.creation_date DESC;

Invoice Headers and Invoice Lines

This SQL includes AP Invoice Lines (which came with with Oracle E-Business Suite Release 12):

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES
-- ############################################################################*/
 
SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , aila.line_number
     , aila.line_type_lookup_code
     , aila.amount
     , aila.original_amount
     , aila.description
     , aila.tax_classification_code tax_code
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_invoice_lines_all aila  ON aia.invoice_id =     aila.invoice_id 
  JOIN ap.ap_terms_tl att            ON aia.terms_id =       att.term_id 
  JOIN applsys.fnd_user fu           ON aia.created_by =     fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id =      pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
 WHERE 1 = 1
   AND aia.invoice_id IN (1966606)                   -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')               -- ## NUMBER  ## --
--   AND aia.doc_sequence_value IN (1326821)         -- ## VOUCHER ## --
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      BASIC INVOICE DETAILS WITH LINES
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , aila.line_number
     , aila.line_type_lookup_code
     , aila.amount
     , aila.description
     , aila.tax_classification_code tax_code
  FROM ap.ap_invoices_all aia
     , ap.ap_invoice_lines_all aila
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.invoice_id = aila.invoice_id
   AND aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
--   AND aia.invoice_id IN (2109872)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
   AND aia.doc_sequence_value IN (1966859,1934769,257578,2262677)         -- ## VOUCHER ## --
   AND 1 = 1;

Invoice Headers

This SQL lists basic AP invoice header details.

-- ##############################################################################
--      BASIC INVOICE DETAILS
-- ############################################################################*/
 
SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.source
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , aia.cancelled_date
     , aia.cancelled_by
     , aia.cancelled_amount
     , pv.vendor_name supplier
     , pv.segment1
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by
--     , aia.payment_status_flag paid
--     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_terms_tl att            ON aia.terms_id =       att.term_id
  JOIN applsys.fnd_user fu           ON aia.created_by =     fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id =      pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
 WHERE 1 = 1
--   AND pv.vendor_name = 'A Whole Lotta Cheese'
--   AND aia.invoice_num = '55263'
   AND aia.invoice_id IN (2139498, 2095523)      -- ## ID ## --
--   AND aia.invoice_num IN ('SSE-1604-003')     -- ## NO  ## --
--   AND AIA.doc_sequence_value IN (2683928)     -- ## VOUCHER ## --
--   AND aia.last_updated_by = 67137
--   AND aia.creation_date BETWEEN '26-NOV-2015' AND '27-NOV-2015'
--   AND aia.invoice_date BETWEEN '01-MAY-2007' AND '01-JUL-2007'
--   AND aia.creation_date > '12-AUG-2015'
--   AND pv.segment1 IN ('9450242','9485283','9522614')
--   AND aia.last_update_date > '23-JUL-2015'
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      BASIC INVOICE DETAILS
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.source
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
--     , aia.payment_status_flag paid
--     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
--   AND aia.creation_date > '13-APR-2015'
   AND aia.invoice_id IN (2095523,2139498,2127401)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('54829','55263')              -- ## NUMBER  ## --
--   AND aia.doc_sequence_value IN (1966859,1934769)         -- ## VOUCHER ## --
   AND 1 = 1;

Quickly navigate and search through My Oracle Support using Keyword Bookmarks

Intro

When working with Oracle I often need to look up a Note or Patch in My Oracle Support.

You can use keyword bookmarks in Firefox and Chrome to search for items using the address bar (using a combination of a pre-defined keyword followed by the search term), which saves a lot of time compared to having to open up My Oracle Support, and then clicking into the search form and searching for whatever you need, or clicking into the SR or Patch tabs and running searches from there.

In fact, they’re not always searches – for example, if you know a My Oracle Support Note, you can easily navigate to that Note ID without having to search for it using this method – you just type in the keyword bookmark (e.g. n), followed by the Note ID, press enter, and you’re redirected to the page on My Oracle Support for that Note. There’s no need to search for it in My Oracle Support – you just go direct to the right place, bypassing any searching. The same applies to Bugs, Patches and Service Requests.

Additionally, the URL I use for Service Requests is handy because unlike the link you see in an email notification to say you’ve had an update on the Service Request, this URL takes you to a version of the Service Request page which does not have relative dates. For example, instead of it saying the SR was raised 5+ months ago and was update 2 days ago, it gives you actual dates instead, which I find a lot more useful.

Firefox

1 – Type CTRL + SHIFT + B to open the Bookmark Manager:

2 – Create a new folder for your keyword bookmarks by right-clicking on the relevant folder you want to put the new folder in, and click “New Folder”:

2017-10-25-firefox-01

3 – Give the folder a name

4 – Once done, right-click the new folder and click “New Bookmark”

2017-10-25-firefox-02

5 – Add in a new Bookmark as follows:

2017-10-25-firefox-03

6 – For each of the following:

Name Location Keyword
Google – Search https://www.google.co.uk/search?q=%s gs
Google – Image Search https://www.google.co.uk/search?q=%s&tbm=isch gi
Google – Map Search https://www.google.co.uk/maps/place/%s gm
MOS – Search https://support.oracle.com/epmos/faces/KMConsolidatedSearch?kmPgTpl:r1:0:mr1:s1:mSearch:bk_srchTxt%20No%20%s sch
MOS – Note https://support.oracle.com/rs?type=doc&id=%s n
MOS – SR https://support.oracle.com/epmos/faces/SrDetail?srDetailRelativeDateParam=false&srNumber=%s sr
MOS – Patch https://support.oracle.com/rs?type=patch&id=%s p
MOS – Bug https://support.oracle.com/rs?type=bug&id=%s b

7 – When finished, the bookmarks will be listed as follows

2017-10-25-firefox-04

As you can see from the Google examples above – the same principle can be applied to any URL which includes the key bit of info in the querystring.

8 – Click the “More” down arrow to access the keyword if you want to edit it at a later date:

2017-10-25-firefox-05

The Keyword field is now visible:

2017-10-25-firefox-06

Chrome

1 – Right-click the address bar and click “Edit Search Engines”

2 – The following opens:

2017-10-25-chrome-01

3 – Click the “Add” button and add in the details – for example:

2017-10-25-chrome-02

4 – Repeat for the following:

Name Location Keyword
Google – Search https://www.google.co.uk/search?q=%s gs
Google – Image Search https://www.google.co.uk/search?q=%s&tbm=isch gi
Google – Map Search https://www.google.co.uk/maps/place/%s gm
MOS – Search https://support.oracle.com/epmos/faces/KMConsolidatedSearch?kmPgTpl:r1:0:mr1:s1:mSearch:bk_srchTxt%20No%20%s sch
MOS – Note https://support.oracle.com/rs?type=doc&id=%s n
MOS – SR https://support.oracle.com/epmos/faces/SrDetail?srDetailRelativeDateParam=false&srNumber=%s sr
MOS – Patch https://support.oracle.com/rs?type=patch&id=%s p
MOS – Bug https://support.oracle.com/rs?type=bug&id=%s b

5 – They appear as follows:

2017-10-25-chrome-03

Using Keyword Bookmarks

1 – When in Firefox or Chrome, press CTRL + L to move into the browser’s address bar.

2 – To search for a My Oracle Support Note, type the keyword (e.g. n) followed by the note ID – e.g.

2017-10-25-address-bar

3 – Press enter to run the search or redirect to the relevant Note ID, Patch, Bug or Service Request.

These are quick to set up, and once you get used to them, they save a lot of time. You can then add in others you find useful for all sorts of other websites.

Email search problem in Outlook 2016

I’ve been using Outlook 2016 for the past 11 months.

One thing that’s annoyed me about it is the fact that the search facility has not worked. I’d press CTRL + SHIFT + F to open the Advanced Find option.

Then run a search, making sure to click the Browse button, and select to include all sub folders.

Anyway, this is more for a note for myself.

I found a fix via this post on lookeen.com, which included a comment by Rachel Harrell-Harper which fixed the issue for me:

  1. Open Control Panel > Programs > Programs and Features
  2. Highlight your Office product
  3. Click change at top
  4. Click quick repair.

 

SyntaxHighlighter and Bootstrap – line number alignment problem

I’m a fan of Alex Gorbatchev’s SyntaxHighlighter, which provides an easy way to include syntax highlighting in web pages.

I noticed that when using it in conjunction with the Bootstrap, that the line numbers appeared to be misaligned – here’s an example:

Misaligned line numbers

I had a look on the web, and found a solution via SyntaxHighter’s GitHub page, on an issue post about Lines misaligned with numbers.

The fix involves adding this CSS:

.syntaxhighlighter table td.gutter {
    vertical-align: middle !important;
}

As provided by Robert Butler.

Once applied, the issue was fixed:

Unable to Sign in with Skype for Business: Certificate Error

Update 11th November 2017 – last week I also had to add live.com to the list of allowed sites on Open DNS, as our home Skype stopped working… I think I also added in skypeassets.com. Adding in these new ‘allowed’ sites fixed the Skype issue. Alternatively you could just disable the ‘Chat’ category in your Web Content Filtering section in Open DNS, but then that allows other sites in.

This week I have been working from home. When I connected my laptop to wifi, and tried to sign into Skype for Business, I received an error message:

Can't sign in to Skype for Business - there was a problem verifying the certificate from the server

Skype for Business Certificate Error

In the end, I remembered that we use Open DNS at home to add in some basic content filtering so that our kids can’t access unsuitable content when using Wifi (now they’re all old enough to have mobiles with 3 or 4G then maybe Open DNS is failrly pointless).

I found a query on the Office 365 URLs and IP address ranges about a similar issue. That pointed me to a page on the Office 365 website about Office 365 URLs and IP address ranges.

I already had these 2 domains whitelisted in my OpenDNS settings:

  • skype.com
  • skype.net

Once I added these in, the problem was solved:

  • cloudappsecurity.com
  • live.com
  • lync.com
  • microsoftonline.com
  • office365.com
  • onmicrosoft.com
  • skypeassets.com
  • skypeforbusiness.com

It might be that was overkill and that I didn’t need to whitelist all of them, but one or more of them being whitelisted fixed the issue.

Schema Browser SQL

Table Information

Often I want to check some data in a table, and I know that the table is, for example, in the AP schema.

I’m not sure what the table is called, and, while I could load the schema browser in SQL Developer or TOAD, I find it a lot quicker using SQL to check.

  SELECT table_name
       , owner
       , num_rows
    FROM all_tables
   WHERE owner = 'AP'
     AND table_name LIKE '%INVOICE%'
     AND num_rows > 0
ORDER BY num_rows DESC;

The num_rows value is updated when Gather Schema Statistics runs

For example – I installed Oracle Database 11g Express Edition and it comes with a tonne of tables as you’d expect, plus some tables in the HR schema.

I ran this SQL: SELECT owner, table_name, num_rows, last_analyzed FROM ALL_TABLES WHERE owner= 'HR';

The LAST_ANALZED was some time in 2014.

I did a quick Gather Schema Stats:

begin
    DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'HR',
       OPTIONS => 'GATHER',  
       ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
       DEGREE=>4);
end;
/

Then re-ran the SQL and it returned:

OWNER   TABLE_NAME     NUM_ROWS LAST_ANAL
------- ------------ ---------- ---------
HR      REGIONS               4 21-OCT-17
HR      LOCATIONS            23 21-OCT-17
HR      DEPARTMENTS          27 21-OCT-17
HR      JOBS                 19 21-OCT-17
HR      EMPLOYEES           107 21-OCT-17
HR      JOB_HISTORY          10 21-OCT-17
HR      COUNTRIES            25 21-OCT-17

If I had inserted rows into some of the tables, and then re-ran the Gather Schema Stats, the num_rows would have been updated too.

Views

Sometimes you might want to get to the bottom of the SQL that makes up a view. In Toad, you can do that by putting the cursor onto the view name in your SELECT statement, and then pressing F4, or, I think you can CTRL + Click the view name (that works in SQL Developer too). That opens up the view properties, and you can then click the “Script” tab to get at the details.

However, the time delay to get at that can sometimes be rather long. I get around it with this SQL:

SELECT view_name
     , TEXT
  FROM all_views
 WHERE view_name = 'PO_AGENTS_V';

The SQL making up the view is stored in the “TEXT” field.

Columns

Here’s an example – while working on a bit of SQL to find out about Projects in the pa_projects_all table, I can see there’s a column in the table called “project_status_code”. Somewhere is another table which holds the descriptive name of the various different project statuses.

As a quick example, you can dig out the details of any tables with a “project_status_code” column name, owned by “PA”.

-- COLUMNS
SELECT atc.column_name
     , atc.data_type
     , att.table_name
     , att.owner
     , att.num_rows
     , att.last_analyzed
  FROM all_tab_columns atc
  JOIN all_tables att ON atc.table_name = att.table_name AND atc.owner = att.owner
 WHERE atc.owner = 'PA'
   AND column_name = 'PROJECT_STATUS_CODE';

You can also use the same SQL to look for specific column names in the same table – for example, what if you wanted to look for all of the columns in the “pa_events” table with “BILL” in their name? Try something like:

-- COLUMNS
SELECT atc.column_name
     , atc.data_type
     , att.table_name
     , att.owner
     , att.num_rows
     , att.last_analyzed
  FROM all_tab_columns atc
  JOIN all_tables att ON atc.table_name = att.table_name AND atc.owner = att.owner
 WHERE att.table_name = 'PA_EVENTS'
   AND atc.owner = 'PA'
   AND column_name LIKE '%BILL%';

You can also use SQL to look for column names in Views.

-- COLUMNS FOR VIEWS
  SELECT atv.view_name
       , atc.column_name
       , atc.column_id
       , atc.data_type
       , atv.owner
    FROM all_tab_columns atc
       , all_views atv
   WHERE atc.table_name = atv.view_name
     AND atc.owner = atv.owner
     AND atv.view_name LIKE 'MTL%EAM%ASSET%V'
--     AND column_name LIKE '%OWN%'
--     AND atc.owner = 'CSI'
ORDER BY 1, 3;

Other ALL% Tables

There are many many other ALL% tables you can make use of.

Here are some handy ones (courtesy of Oracle – Get list of all Tables, Views, Procedures, Functions, Triggers).

Get a list of Procedures

SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE';
--OR
SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='PROCEDURE';

Get a list of Functions

SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='FUNCTION';
--OR
SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='FUNCTION';

Get a list of Triggers

SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='TRIGGER';
--OR
SELECT * FROM ALL_TRIGGERS WHERE OWNER='HR';
--OR
SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='TRIGGER';

Get a list of Packages

SELECT * FROM ALL_OBJECTS WHERE object_type = 'PACKAGE';

APPS.POR_AME_APPROVAL_LIST Error Message

Today I had a support call where a user was raising a requisition on behalf of another member of staff in iProcurement (using R12.1.3).

When they tried to send the requisition for approval they saw this message:

Error Page
You have encountered an unexpected error. Please contact the System Administrator for assistance.

I enabled the FND: Diagnostics profile at User Level for the user, and they tried again.

This time a link appeared allowing them to click into the details.

This time they got a massive error message – starting with:

Exception Details.
oracle.apps.fnd.framework.OAException: java.sql.SQLException: ORA-20001: The approver group First Supervisor with Authority has dynamic query in wrong format or it has returned an invalid approver. ORA-06512: at "APPS.POR_AME_APPROVAL_LIST", line 3232 ORA-06512: at line 1 at...

The user was able to raise requisitions on behalf of other users. When I compared the HR record for one of those users, with the one that led to the error, I noticed that person had no Supervisor showing on their HR Assignment. Once that was added in, problem solved.