Subledger-Ledger Linkage (Purchasing)

PO (Receipt)

select distinct
       rsh.receipt_num
      ,gjl.entered_cr
      ,gjl.entered_dr
      ,gjl.accounted_cr
      ,gjl.accounted_dr   
      ,gjh.name journal_name
      ,gjh.creation_date journal_creation_date
      ,gcc.segment1
  from rcv_shipment_headers rsh
      ,rcv_shipment_lines rsl
      ,rcv_transactions rt
      ,rcv_receiving_sub_ledger rrsl
      ,xla_distribution_links xdl
      ,xla_ae_lines xal
      ,xla_ae_headers xah
      ,gl_import_references gir
      ,gl_je_lines gjl
      ,gl_je_headers gjh
      ,gl_je_batches gjb
      ,gl_code_combinations gcc
 where rsl.shipment_header_id = rsh.shipment_header_id
   and rt.shipment_line_id = rsl.shipment_line_id
   and rrsl.rcv_transaction_id = rt.transaction_id
   and rrsl.rcv_sub_ledger_id = xdl.source_distribution_id_num_1
   and xdl.ae_line_num = xal.ae_line_num
   and xdl.ae_header_id = xal.ae_header_id
   and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
   and xal.gl_sl_link_table = gir.gl_sl_link_table
   and xal.gl_sl_link_id = gir.gl_sl_link_id
   and xah.ae_header_id = xdl.ae_header_id
   and xah.ae_header_id = xal.ae_header_id
   and xdl.event_id = xah.event_id
   and gir.je_header_id = gjl.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gjl.je_header_id = gjh.je_header_id
   and gjh.je_batch_id = gjb.je_batch_id
   and gcc.code_combination_id = gjl.code_combination_id
   --and rsh.receipt_num = '23478'
;

PO (Transaction)


SELECT pha.segment1 po#,
       plla.shipment_num,
       pra.release_num,
       gjl.entered_cr,
       gjl.entered_dr,
       gjl.accounted_cr,
       gjl.accounted_dr,
       gjh.name journal_name,
       gjh.creation_date journal_creation_date,
       gcc.segment1
FROM po_headers_all pha,
     po_lines_all pla,
     po_line_locations_all plla,
     po_distributions_all pda,
     po_releases_all pra,
     xla_distribution_links xdl,
     xla_ae_lines xal,
     gl_import_references gir,
     gl_je_lines gjl,
     gl_je_headers gjh,
     gl_je_batches gjb,
     gl_code_combinations gcc
WHERE     pla.po_header_id = pha.po_header_id
      AND plla.po_line_id = pla.po_line_id
      AND pda.line_location_id = plla.line_location_id
      AND pda.po_release_id = pra.po_release_id(+)
      AND pda.po_distribution_id = xdl.source_distribution_id_num_1
      AND xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
      AND xdl.application_id = 201
      AND xdl.ae_line_num = xal.ae_line_num
      AND xdl.ae_header_id = xal.ae_header_id
      AND xal.gl_sl_link_table = gir.gl_sl_link_table
      AND xal.gl_sl_link_id = gir.gl_sl_link_id
      AND gir.je_header_id = gjl.je_header_id
      AND gir.je_line_num = gjl.je_line_num
      AND gjl.je_header_id = gjh.je_header_id
      AND gjh.je_batch_id = gjb.je_batch_id
      AND gjl.code_combination_id = gcc.code_combination_id
--and pha.segment1= 'P12367450'