Below is the query to extract Payroll data from General Ledger, historical data for a week.
select gjl.je_line_num, gcc.concatenated_segments,
nvl(gjl.entered_dr,0) entered_debit,nvl(gjl.entered_cr,0) entered_credit,gjl.description
from gl_je_lines gjl,gl_code_combinations_kfv gcc, gl_je_headers gjh,gl_je_sources gs,gl_je_categories gc
where 1=1
and gjl.code_combination_id=gcc.code_combination_id
and gjh.je_source=gs.je_source_name
and gjh.je_category=gc.je_category_name
and gjh.je_header_id=gjl.je_header_id
–and gjh.je_header_id=P_HEADER_ID
and gjh.ledger_id=P_LEDGER_ID
and trunc(gjh.posted_date)>=trunc(sysdate-7)
and (upper(gc.je_category_name) =upper(C_PAYROLL) or upper(gs.je_source_name)=upper(C_PAYROLL));
— Order By gjl.je_line_num,gcc.concatenated_segments,gjl.entered_dr,gjl.entered_cr,gjl.description;
