Tuesday, May 13, 2014

General Ledger Queries

SELECT jeh.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, gl_je_lines gl
, po_lines_all pol
, po_line_locations_all pll
, po_distributions_all pod
WHERE 1=1
AND pod.line_location_id = pll.line_location_id
AND pll.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
and gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = pod.po_distribution_id
AND xld.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
AND xll.gl_sl_link_table = 'XLAJEL'


select jel.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, rcv_receiving_sub_ledger rsl
, gl_je_lines jel
WHERE gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = rsl.rcv_sub_ledger_id
AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xll.gl_sl_link_table = 'XLAJEL'


SELECT api.invoice_num
, jel.accounted_cr
, jel.creation_date
, jeh.*
FROM gl_import_references gli
, xla_ae_lines xll
, xla_ae_headers xlh
, xla_distribution_links xld
, ap_invoices_all api
, ap_invoice_lines_all apl
, ap_invoice_distributions_all adp
, gl_je_lines jel
, gl_je_headers jeh
WHERE 1 = 1
AND api.invoice_id = apl.invoice_id
AND apl.invoice_id = adp.invoice_id
AND apl.line_number = adp.invoice_line_number
AND gli.gl_sl_link_table = xll.gl_sl_link_table
AND gli.gl_sl_link_id = xll.gl_sl_link_id
AND xll.ae_header_id = xlh.ae_header_id
AND xld.ae_header_id = xlh.ae_header_id
AND jel.je_header_id = jeh.je_header_id
AND jel.je_header_id = gli.je_header_id
AND jel.je_line_num = gli.je_line_num
AND xld.source_distribution_id_num_1 = adp.invoice_distribution_id
AND xld.source_distribution_type = 'AP_PMT_DIST'
AND xll.gl_sl_link_table = 'XLAJEL'


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect