Friday, April 22, 2016

PO TO GL LINK IN R12 QUERY FOR PO TO GL

PO TO GL LINK IN R12

SELECT prh.segment1 req_number,
  prd.distribution_id,
  poh.segment1 po_num,
  poh.org_id,
  rsh.receipt_num,
  api.invoice_num,
  apl.line_number,
  aps.vendor_name
FROM po_requisition_headers_all prh,
  po_requisition_lines_all prl,
  po_req_distributions_all prd,
  po_distributions_all pod,
  po_headers_all poh,
  po_lines_all pol,
  rcv_transactions rct,
  rcv_shipment_lines rsl,
  rcv_shipment_headers rsh,
  ap_invoice_distributions_all apd,
  ap_invoice_lines_all apl,
  ap_invoices_all api,
  xla_distribution_links xldl,
  apps.xla_ae_lines al,
  xla_ae_headers ah,
  apps.xla_events e,
  apps.xla_transaction_entities te,
  gl_import_references gir,
  apps.gl_je_lines jl,
  gl_je_headers glh,
  apps.gl_code_combinations glcc,
  ap_suppliers aps
WHERE 1                               = 1
AND prh.requisition_header_id         = prl.requisition_header_id
AND prl.requisition_line_id           = prd.requisition_line_id
AND prd.distribution_id               = pod.req_distribution_id
AND pol.po_line_id                    = pod.po_line_id
AND poh.po_header_id                  = pol.po_header_id
AND pod.po_distribution_id            = rct.po_distribution_id
AND rct.shipment_line_id              = rsl.shipment_line_id
AND rsh.shipment_header_id            = rsl.shipment_header_id
AND apd.po_distribution_id            = rct.po_distribution_id -
AND api.invoice_id                    = apd.invoice_id
AND api.invoice_id                    = apl.invoice_id
AND xldl.applied_to_source_id_num_1   = api.invoice_id
AND xldl.source_distribution_id_num_1 = apd.invoice_distribution_id
AND xldl.ae_line_num                  = al.ae_line_num
AND xldl.ae_header_id                 = al.ae_header_id
AND al.ae_header_id                   = ah.ae_header_id
AND al.application_id                 = ah.application_id
AND ah.event_id                       = e.event_id
AND e.entity_id                       = te.entity_id(+)
AND e.application_id                  = te.application_id(+)
AND al.gl_sl_link_id                  = gir.gl_sl_link_id
AND gir.je_header_id                  = jl.je_header_id
AND gir.je_line_num                   = jl.je_line_num
AND jl.je_header_id                   = glh.je_header_id
AND jl.code_combination_id            = glcc.code_combination_id
AND api.vendor_id                     = aps.vendor_id
AND ah.je_category_name               = 'Purchase Invoices'
AND ah.gl_transfer_status_code        = 'Y'
AND glh.STATUS                        ='P'
AND API.INVOICE_NUM                   = :P_INCOIVE_NUMBER
AND apl.line_number                   = 1

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...