PO TO GL LINK IN R12

  PO TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )

SELECT   prh.segment1 "PO Requisition Number",
         pha.segment1 "PO Number",
         aps.SEGMENT1 "Supplier Number",
         aps.vendor_name,
         apss.vendor_site_code,
         apsc.first_name,
         apsc.last_name,
         pla.item_id,
         plla.ship_to_organization_id,
         plla.ship_to_location_id,
         rt.transaction_type,
         rt.destination_type_code,
         rsh.receipt_num "PO Receipt Number",
         aia.invoice_num,
         aida.dist_code_combination_id,
         aca.check_number,
         gjh.ledger_id,
         gjh.name
  FROM   po_requisition_headers_all prh,
         po_requisition_lines_all prl,
         po_req_distributions_all prd,
         po_headers_all pha,
         po_lines_all pla,
         po_distributions_all pda,
         po_line_locations_all plla,
         ap_suppliers aps,
         ap_supplier_sites_all apss,
         ap_supplier_contacts apsc,
         rcv_transactions rt,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl,
         ap_invoices_all aia,
         ap_invoice_lines_all aila,
         ap_invoice_distributions_all aida,
         ap_invoice_payments_all aipa,
         ap_checks_all aca,
         xla.xla_transaction_entities xte,
         xla_events xe,
         xla_ae_headers xah,
         xla_ae_lines xal,
         xla_distribution_links xdl,
         gl_import_references gir,
         gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl
 WHERE       prh.segment1 = :RequitionNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
         AND aps.vendor_id = pha.vendor_id
         AND apss.vendor_id = aps.vendor_id
         AND apss.vendor_site_id(+) = pha.vendor_site_id
         AND apss.vendor_site_id = aca.vendor_site_id
         AND apsc.vendor_site_id = apss.vendor_site_id
         AND apsc.vendor_contact_id = pha.vendor_contact_id
         AND prl.requisition_header_id = prh.requisition_header_id
         AND prd.requisition_line_id = prl.requisition_line_id
         AND pda.req_distribution_id = prd.distribution_id
         AND pla.po_header_id = pda.po_header_id
         AND pla.po_line_id = pda.po_line_id
         AND pha.po_header_id = pla.po_header_id
         AND pha.org_id = 204
         AND plla.po_header_id = pla.po_header_id
         AND plla.po_line_id = pla.po_line_id
         AND rt.transaction_type = 'DELIVER'
         AND rt.po_header_id = pha.po_header_id
         AND rt.po_line_id = pla.po_line_id
         AND rsh.shipment_header_id = rt.shipment_header_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND aila.po_header_id = pha.po_header_id
         AND aila.po_line_id = pla.po_line_id
         AND aia.invoice_id = aila.invoice_id
         AND aida.invoice_id = aila.invoice_id
         AND aida.invoice_line_number = aila.line_number
         AND aipa.invoice_id = aia.invoice_id
         AND aca.check_id = aipa.check_id
         AND xte.entity_code = 'AP_PAYMENTS'
         AND xte.transaction_number = aca.check_number
         AND xte.source_id_int_1 = aipa.check_id
         AND xte.security_id_int_1 = aia.org_id
         AND xe.entity_id = xte.entity_id
         AND xah.event_id = xe.event_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xal.ae_line_num = aida.invoice_line_number
         AND xdl.ae_header_id = xah.ae_header_id
         AND xdl.ae_line_num = xal.ae_line_num
         AND xdl.applied_to_dist_id_num_1 = aida.invoice_distribution_id
         AND gir.reference_5 = xte.entity_id                      -- Entity Id
         AND gir.reference_6 = TO_CHAR (xe.event_id)                --Event Id
         AND gir.reference_7 = TO_CHAR (xah.ae_header_id)      -- AE Header Id
         AND gir.gl_sl_link_id = xal.gl_sl_link_id
         --and gir.created_by = 1318
         AND gjb.je_batch_id = gir.je_batch_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjh.je_header_id = gir.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gir.je_line_num