Posts

Showing posts from April, 2014

Oracle APPS Useful Queries

ACCOUNT PAYABLES



Payable Open Interface Rejection:

SELECT ail.*
FROM AP_INVOICES_INTERFACE ai ,
  ap_invoice_lines_interface ail ,
  ap_interface_rejections AIR
WHERE ai.GROUP_ID='EO_AUG-13_010813_443'
AND ai.status    ='REJECTED'
AND ai.invoice_id=ail.invoice_id
  --and ail.dist_code_concatenated='66810-00-999-00000000-00000000-00000-FI100'
AND air.parent_id = DECODE(air.parent_table,'AP_INVOICES_INTERFACE',AI.INVOICE_ID,'AP_INVOICE_LINES_INTERFACE',ail.invoice_line_id,NULL)
ORDER BY 2;

AP Invoices Query:

/*+INDEX (AP_INVOICES_ALL [XXDG_TEST]) */

SELECT  apia.invoice_id,
       alc.displayed_field "Invoice Type",
       apia.invoice_num "Invoice Number",
       apia.invoice_date "Invoice Date",
       apia.gl_date   "Invoice GL Date",
       nvl(apia.VOUCHER_NUM,doc_sequence_value)"Voucher Number",
       apia.description inv_Description,
       apia.invoice_currency_code,
       gcc.concaten…