Support Engineers Tips & Tricks Quickies May 2015

 

We invited our fabulous support engineers to share more of their Tips & Tricks Quickies for FMS Central [1525252.1]. Here are May 2015 contributions. Enjoy!


Mario Mendoza [ARBI] Identifying Duplicate Invoices

A customer wanted to identify the duplicate invoices caused by approving billing worksheets twice, so they can cancel the second one.  The invoices share the same contract number, invoice date, operator id and bill status.  This SQL returns the larger of the invoice ids, where there is more than one for given combination.

 

SELECT *
FROM   ps_bi_hdr b
WHERE  b.contract_num <> ' '
AND    invoice_dt = '&Date'
AND    invoice LIKE 'INV%'
AND    last_maint_oprid = '&Operator'
AND    bill_status = 'RDY'
AND    b.invoice =
       (
                SELECT   Max(b2.invoice)
                FROM     ps_bi_hdr b2
                WHERE    b2.business_unit = b.business_unit
                AND      b2.contract_num = b.contract_num
                GROUP BY b2.contract_num)
AND    (
                SELECT   count(*)
                FROM     ps_bi_hdr b3
                WHERE    b3.business_unit = b.business_unit
                AND      b3.contract_num = b.contract_num
                GROUP BY b3.contract_num) > 1

 

Raul Cocias [AM] Check if depreciation is wrong!

A first step to check for depreciation of an asset if it is incorrect you can do the following verification Sum the cost field for the lines on the cost table – (less) salvage value and compare it with the sum of the depr filed from depreciation table. If sums are different means depreciation is wrong.

 

e.g Asset asset1_raul has 2 lines on cost ADD 100 and ADJ 30   with salvage value 10 and on depreciation I find 3 lines with (40 50 60)I will do :100+30-10 is 120 on cost and 40+50+60 which is e 150 on depreciation . It means there is something wrong with my depreciation.


Jos Bergman [PA] Troubleshooting Tip: Performance

Experiencing bad performance while running any of the feeder engines (such as PC_AP_TO_PC), check if changes were made to the delivered index for the table PS_PROJ_RESOURCE. Query the tables DBA_INDEXES and DBA_IND_COLUMNS to compare the rows with the ones in DEMO.