Drilldown from GL to AP for Liability and Prepayment and Expense account

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT
    batch.name batch_name,
    header.name journal_name,
    lines.je_line_num
FROM
    gl_je_batches batch,
    gl_je_headers header,
    gl_je_lines lines
WHERE        batch.je_batch_id = header.je_batch_id
    AND      header.je_header_id = lines.je_header_id
    AND      header.ledger_id = lines.ledger_id
    AND      batch.name = 'Payables A 421678 1792483 2'
    AND      header.name = '01-2016 Purchase Invoices USD'
    AND      lines.code_combination_id = 2005 -- Liability Account GL code combination id
    AND      header.ledger_id = 2021
    AND      header.period_name = '01-2016'
    AND      header.je_source = 'Payables'
    AND      header.je_category = 'Purchase Invoices';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT batch.name batch_name,
header.name journal_name,
SUM(NVL(lines.accounted_dr,0)) accounted_dr,
SUM(NVL(lines.accounted_cr,0)) accounted_cr,
SUM(NVL(lines.accounted_dr,0) - NVL(lines.accounted_cr,0)) Balance
FROM
    gl_je_batches batch,
    gl_je_headers header,
    gl_je_lines lines
WHERE      batch.je_batch_id = header.je_batch_id
    AND    header.je_header_id = lines.je_header_id
    AND    header.ledger_id = lines.ledger_id
    --and batch.name = ‘Payables A 421678 1792483 2′
    --and header.name = ’01-2016 Purchase Invoices USD’
    AND    lines.code_combination_id = 2005 -- Liability Account GL code combination id
    AND    header.ledger_id = 2021
    AND    header.period_name = '01-2016'
    AND    header.je_source = 'Payables'
    AND    header.je_category = 'Purchase Invoices'
  GROUP BY
           batch.name,
           header.name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SELECT batch.name batch_name,
header.name journal_name,
inv.invoice_num,
SUM(NVL(link.unrounded_accounted_dr,0)) accounted_dr,
SUM(NVL(link.unrounded_accounted_cr,0)) accounted_cr,
SUM(NVL(link.unrounded_accounted_dr,0) - NVL(link.unrounded_accounted_cr,0)) balance
FROM
    gl_je_batches batch,
    gl_je_headers header,
    gl_je_lines lines,
    gl_import_references REF,
    xla_ae_lines xla_lines,
    xla_ae_headers xla_headers,
    xla_events events,
    ap_invoice_distributions_all dist,
    ap_invoice_lines_all ap_lines,
    ap_invoices_all inv,
    xla_distribution_links link
WHERE     batch.je_batch_id = header.je_batch_id
    AND   header.je_header_id = lines.je_header_id
    AND   header.ledger_id = lines.ledger_id
    --AND batch.name = 'Payables A 421678 1792483 2'
    --AND header.name = '01-2016 Purchase Invoices USD'
    AND   lines.code_combination_id = 2005 --Liability Account GL code combination id
    AND   header.ledger_id = 2021
    AND   header.period_name = '01-2016'
    AND   header.je_source = 'Payables'
    --AND header.je_category = 'Purchase Invoices'
    AND   REF.je_batch_id = batch.je_batch_id
    AND   REF.je_header_id = header.je_header_id
    AND   REF.je_line_num = lines.je_line_num
    AND   REF.gl_sl_link_id = xla_lines.gl_sl_link_id
    AND   xla_lines.ae_header_id = xla_headers.ae_header_id
    AND   xla_headers.ledger_id = xla_lines.ledger_id
    AND   xla_headers.event_id = events.event_id
    AND   xla_headers.ledger_id = header.ledger_id
    AND   events.event_id = dist.accounting_event_id
    AND   dist.invoice_id = ap_lines.invoice_id
    AND   dist.invoice_id = inv.invoice_id
    AND   dist.invoice_line_number = ap_lines.line_number
    AND   link.ae_header_id = xla_headers.ae_header_id
    AND   link.ae_line_num = xla_lines.ae_line_num
    AND   link.source_distribution_id_num_1 = dist.invoice_distribution_id
    AND   inv.invoice_num = '1024'
  GROUP BY
        batch.name,
        header.name,
        inv.invoice_num
  ORDER BY inv.invoice_num;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SELECT
    batch.name batch_name,
    header.name journal_name,
    lines.je_line_num,
    inv.invoice_num,
    nvl(dist.base_amount,dist.amount) dist_accounted_amt,
    SUM(nvl(xla_lines.accounted_dr,0) - nvl(xla_lines.accounted_cr,0)) xla_accounted_dr
FROM
    gl_je_batches batch,
    gl_je_headers header,
    gl_je_lines lines,
    gl_import_references REF,
    xla_ae_lines xla_lines,
    xla_ae_headers xla_headers,
    xla_events events,
    ap_invoice_distributions_all dist,
    ap_invoice_lines_all ap_lines,
    ap_invoices_all inv
WHERE    batch.je_batch_id = header.je_batch_id
    AND  header.je_header_id = lines.je_header_id
    AND  header.ledger_id = lines.ledger_id
    --AND batch.name = 'Payables A 483784 1937554'
    --AND header.name = '12-2016 Purchase Invoices USD'
    AND  lines.code_combination_id = 2005
    AND  header.ledger_id = 2021
    AND  header.period_name = '12-2016'
    AND  header.je_source = 'Payables'
    AND  header.je_category = 'Purchase Invoices'
    AND  REF.je_batch_id = batch.je_batch_id
    AND  REF.je_header_id = header.je_header_id
    AND  REF.je_line_num = lines.je_line_num
    AND  REF.gl_sl_link_id = xla_lines.gl_sl_link_id
    AND  xla_lines.ae_header_id = xla_headers.ae_header_id
    AND  xla_headers.ledger_id = xla_lines.ledger_id
    AND  xla_headers.event_id = events.event_id
    AND  xla_headers.ledger_id = header.ledger_id
    AND  events.event_id = dist.accounting_event_id
    AND  dist.invoice_id = ap_lines.invoice_id
    AND  dist.invoice_id = inv.invoice_id
    AND  dist.invoice_line_number = ap_lines.line_number
    AND  dist.line_type_lookup_code = 'PREPAY'
GROUP BY
    batch.name,
    header.name,
    lines.je_line_num,
    inv.invoice_num,
    dist.base_amount,
    dist.amount
    --order by xla_lines.ae_line_num;