Need help in SQL queries

[dvsprao108]

Hi Friends,

I need some SQL queries for the below requirements.

1) need to review the data (unposted journals) in GL which was transferred from SOURCE AP, PO & PA.

2) need to review the data (posted journals) in GL which was transferred from SOURCE AP, PO & PA

3) need to review the data of Approved Purchase Orders, but not yet received material from Supplier.

Could anybody help me please?

Thanks,
Surya

[oteixeira]

Hello Surya.

The following query will return the data for your requirement 1) and 2).
As I don?t know exactly which data you need to see, I?m just including a few columns. You will need to modify the query according to your needs.

Code: Select all

SELECT l.effective_date, 
    h.name, 
    cc.segment1, 
    cc.segment2, 
    cc.segment3, 
    l.accounted_dr, 
    l.accounted_cr, 
    l.description
FROM gl_je_lines l,
    gl_je_headers h,
    gl_code_combinations cc
WHERE cc.code_combination_id = l.code_combination_id
AND h.je_header_id = l.je_header_id
AND h.je_source = <your source: 'Purchasing', 'Payables' or 'Projects'
AND l.set_of_books_id = <your set_of_books_id>
AND l.period_name = <your period name>
and l.status = <'P' for posted or 'U' for unposted

Hope this helps.
Octavio