Oracle expense report issue / Bug finding Queries

LINE AMOUNT IS NULL : Expense ReportExport failing with LINE AMOUNT IS NULL

           
SELECT apeh.org_id,   
apeh.invoice_num,
apeh.report_header_id ,
 apel.report_line_id ,
' Line Amount is Null '
FROM ap_expense_report_headers_all apeh,  
ap_expense_report_lines_all apel,  
ap_exp_report_dists_all aped             
WHERE apeh.report_header_id    = apel.report_header_id
AND apeh.report_header_id    = aped.report_header_id
AND apel.report_line_id      = aped.report_line_id        
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date
AND ((apel.AMOUNT           IS NULL)
))    


INVALID LINE TYPE LOOKUP


SELECT apeh.org_id,   
apeh.invoice_num,
apeh.report_header_id ,
 apel.report_line_id ,
' Invalid Line Type Lookup Code '
from ap_expense_report_headers_all apeh,
ap_expense_report_lines_all apel
WHERE            
 apeh.report_header_id = apel.report_header_id
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date 
AND apeh.EXPENSE_STATUS_CODE = 'INVOICED'
AND apel.LINE_TYPE_LOOKUP_CODE <> 'ITEM'



Duplicate Distribution Line Number 


select apeh.org_id,apeh.invoice_num,apeh.report_header_id, apel.report_line_id,' Duplicate Distribution Line Number '
from ap_expense_report_headers_all apeh,
ap_expense_report_lines_all apel,
ap_expense_report_lines_all apel1
where apel.report_header_id = apeh.report_header_id
and apel1.report_header_id = apeh.report_header_id
and apel.report_line_id != apel1.report_line_id
and apel.distribution_line_number = apel1.distribution_line_number
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date ;



EXPENSE HEADER LINE MISMATCH


SELECT  apeh.org_id,  
apeh.invoice_num, 
apeh.report_header_id,             
apel.report_line_id,                     
' Expense Header Line Mismatch '
from ap_expense_report_lines_all apel,
  ap_expense_report_headers_all apeh
WHERE apeh.report_header_id           = apel.report_header_id
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date
AND apeh.expense_status_code        = ''INVOICED''
AND apel.itemization_parent_id     IS NOT NULL
AND apel.itemization_parent_id     <> -1
AND apel.itemization_parent_id NOT   IN
  (SELECT report_line_id
  FROM ap_Expense_report_lines_all rl
  WHERE rl.itemization_parent_id = -1
  )
  

  Expense Header Line Mismatch: Wrong Due amount to Credit Card Company 
  
 SELECT DISTINCT aeh.org_id,aeh.invoice_num,aeh.report_header_id,null    ,         
' Expense Header Line Mismatch - Wrong Due amount to Credit Card Company  '      
 FROM ap_expense_report_headers_all AEH ,
  ap_expense_report_lines_all AEL,
  ap_credit_card_trxns_all ACCT
WHERE  aeh.report_header_id       = ael.report_header_id
AND acct.report_header_id      = aeh.report_header_id
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date 
AND acct.payment_due_from_code = ('COMPANY')
AND acct.category             IN ('PERSONAL','BUSINESS')
AND ael.credit_card_trx_id     = acct.trx_id
AND ael.credit_card_trx_id    IS NOT NULL
AND aeh.amt_due_ccard_company !=
  (SELECT SUM(amount)
  FROM ap_expense_report_lines_all AELA
  WHERE aela.report_header_id  = aeh.report_header_id
  AND aela.credit_card_trx_id IS NOT NULL
  )

  
 

 Expense Header Line Mismatch : orphan Itemization Parent id 
  
SELECT  DISTINCT apeh.org_id,  
apeh.invoice_num, 
apeh.report_header_id,             
apel.report_line_id,                     
' Expense Header Line Mismatch - Personal lines considered while calculating  line amount'
FROM ap_expense_report_lines_all apel,
ap_expense_report_headers_all apeh,
ap_expense_report_params_all aerpa
WHERE 
apel.report_header_id = apeh.report_header_id
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date 
and apel.org_id = apeh.org_id
and apeh.reject_code = 'EXPENSE HEADER LINE MISMATCH'
and aerpa.expense_type_code = 'PERSONAL'
and (apeh.source <> 'NONVALIDATEDWEBEXPENSE' or apeh.workflow_approved_flag is null)
and apeh.source <> 'BOTH PAY'
and nvl(
apeh.expense_status_code,
ap_web_oa_active_pkg.getreportstatuscode (
apeh.source,
apeh.workflow_approved_flag,
apeh.report_header_id, 'N'))
in ('MGRPAYAPPR', 'INVOICED', 'PAID')
and not exists (
select aerpa.parameter_id
from ap_expense_report_params_all aerpa,
ap_expense_reports_all aera
where aera.report_type = ''SEEDED PERSONAL EXPENSE''
and aerpa.expense_type_code = ''PERSONAL''
and aera.expense_report_id = aerpa.expense_report_id
and apel.web_parameter_id = aerpa.parameter_id)
and ( (itemization_parent_id is null) or ( itemization_parent_id <> -1 ));


Expense Line Dist Mismatch

SELECT  apeh.org_id,  
apeh.invoice_num, 
apeh.report_header_id,             
apel.report_line_id,                     
'Expense Line Dist Mismatch'
from 
ap_expense_report_headers_all apeh,
  ap_expense_report_lines_all apel
WHERE  apeh.report_header_id   = apel.report_header_id
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date 
and apeh.report_header_id in 
(
SELECT report_header_id
FROM
  (SELECT SUM(lines_total) lines_total,
    SUM(lines_count) lines_count,
    SUM(dist_total) distribution_total,
    SUM(dist_count) distribution_count,
    report_header_id
  FROM
    (SELECT NVL(SUM(al.amount),0) lines_total,
      COUNT(al.report_header_id) lines_count,
      0 dist_total,
      0 dist_count,
      al.report_header_id report_header_id
    FROM ap_expense_report_lines_all al
    WHERE (al.itemization_parent_id IS NULL
    OR al.itemization_parent_id   <> -1)
    AND al.web_parameter_id NOT     IN
      (SELECT axp.parameter_id
      FROM ap_expense_report_params_all axp
      WHERE axp.expense_type_code = 'PERSONAL'
      )
    GROUP BY al.report_header_id
    UNION
    SELECT 0 lines_total,
      0 lines_count,
      NVL(SUM(ad.amount),0) dist_total,
      COUNT(ad.report_header_id) dist_count,
      ad.report_header_id rep_id
    FROM ap_exp_report_dists_all ad
    WHERE  NVL(ad.web_parameter_id,
      (SELECT web_parameter_id
      FROM ap_expense_report_lines_all aerl
      WHERE aerl.report_line_id = ad.report_line_id
      )) NOT                   IN
      (SELECT axp.parameter_id
      FROM ap_expense_report_params_all axp
      WHERE axp.expense_type_code = 'PERSONAL'
      )
    GROUP BY ad.report_header_id
    )
  GROUP BY report_header_id
  )
WHERE lines_total <> distribution_total
OR lines_count    <> distribution_count
)
UNION
SELECT  apeh.org_id,  
apeh.invoice_num, 
apeh.report_header_id,
apel.report_line_id,                   
''CAT7'' CORRUPTION_TYPE,
'' Expense Line Dist Mismatch - Receipt Currency Amount is NULL or 0. <BR> ** Please Contact Oracle Support for Fix. ** </BR> ''
FROM 
ap_expense_report_headers_all apeh,
  ap_expense_report_lines_all apel
WHERE  apeh.report_header_id   = apel.report_header_id 
AND apeh.WEEK_END_DATE between :p_start_date and :p_end_date 
and apeh.report_header_id in (select apel.report_header_id from ap_exp_report_dists_all aerda,ap_expense_report_lines_all apel  where 
aerda.report_line_id  = apel.report_line_id and 
(aerda.receipt_currency_amount is null or aerda.receipt_currency_amount = 0 ));