Thursday, July 21, 2016

Accruals Query , PO accruals r12 Query

--Accruals

SELECT i.INVOICE_NUM,  
  i.INVOICE_DATE,  
  i.SUPPLIER_TAX_INVOICE_NUMBER,  
  i.DOC_SEQUENCE_VALUE,  
  v.SEGMENT1 VENDOR_NUM,  
  v.VENDOR_NAME,  
  vs.VENDOR_SITE_CODE,  
  i.INVOICE_CURRENCY_CODE,  
  i.INVOICE_TYPE_LOOKUP_CODE,  
  l.ACCOUNTING_DATE,  
  l.ACCOUNTING_CLASS_CODE,  
    NVL(d.UNROUNDED_ACCOUNTED_DR,0)-NVL(d.UNROUNDED_ACCOUNTED_CR,0) AMOUNT_FUNC,  
  NVL(d.UNROUNDED_ENTERED_DR,0)  -NVL(d.UNROUNDED_ENTERED_CR,0) AMOUNT_CURR,  
  g.CONCATENATED_SEGMENTS,  
  g.segment1,  
  g.segment2,  
  g.segment3,  
  g.segment4,  
  g.segment5,  
  g.segment6,  
  g.segment7,  
  g.segment8,  
  dd.invoice_line_number,  
  dd.LINE_TYPE_LOOKUP_CODE,  
  dd.DESCRIPTION,  
  dd.DIST_MATCH_TYPE,  
  ph.SEGMENT1 PO_NUMBER,  
  ph.TYPE_LOOKUP_CODE PO_TYPE_LOOKUP_CODE,  
  ph.VENDOR_ORDER_NUM  
  FROM apps.xla_ae_lines l,  
  apps.gl_code_combinations_kfv g,  
  apps.xla_distribution_links d,  
  apps.ap_invoice_distributions_all dd,  
  apps.ap_invoices_all i,  
  apps.po_vendors v,  
  apps.po_vendor_sites_all vs,  
  apps.po_distributions_all pd,  
  apps.po_headers_all ph,  
  apps.xla_ae_headers h  
WHERE l.ledger_id   = xxxx  
AND l.application_id=200  
AND l.accounting_date BETWEEN to_date('01022015','DDMMRRRR') AND to_date('28022015','DDMMRRRR')  
AND l.CODE_COMBINATION_ID          =g.CODE_COMBINATION_ID  
AND l.AE_HEADER_ID                 =d.AE_HEADER_ID  
AND l.AE_HEADER_ID                 =h.AE_HEADER_ID  
AND l.AE_LINE_NUM                  =d.AE_LINE_NUM  
AND d.source_distribution_type     ='AP_INV_DIST'  
AND l.accounting_class_code NOT   IN ('PREPAID_EXPENSE','LIABILITY','NRTAX','RTAX')  
AND dd.INVOICE_DISTRIBUTION_ID     =d.SOURCE_DISTRIBUTION_ID_NUM_1  
AND i.invoice_id                   =dd.invoice_id  
AND i.VENDOR_ID                    =v.VENDOR_ID  
AND i.VENDOR_SITE_ID               =vs.VENDOR_SITE_ID  
AND dd.PO_DISTRIBUTION_ID          =pd.PO_DISTRIBUTION_ID (+)  
AND pd.PO_HEADER_ID                =ph.po_header_id (+)  
AND h.accounting_entry_status_code!='D'  
and  nvl(i.pay_group_lookup_Code,'XX')<>'INTERCOMPANY'
and i.INVOICE_TYPE_LOOKUP_CODE<>'EXPENSE REPORT'
ORDER BY 4

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...