Monday, 19 June 2017

GL Transactions for AP

SELECT /*+ leading (ael aeh) */
       aia.invoice_type_lookup_code trans_type,
       ent.transaction_number invoice_num,
       TO_CHAR (aia.invoice_date) invoice_date,
       TO_CHAR (pod.po_number) ponum_inv_linenum,
             THEN aia.description
          ELSE aid.description
       END line_desc,
       TO_CHAR (pod.requested_by) requestor_qty_inv,
       TO_CHAR (pod.approved_date) appr_date_unitprice,
       TO_CHAR (aia.doc_sequence_value) doc_seq_revamt, NULL acct_class,
       NULL PERCENT, NULL amount,
       DECODE (aid.base_amount, NULL, aid.amount, aid.base_amount) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       apps.ap_invoices_all aia,
       apps.ap_invoice_distributions_all aid,
       (SELECT poh.segment1 po_number, poh.approved_date approved_date,
               pod.po_distribution_id po_dist_id, pod.code_combination_id,
               pap.full_name requested_by
          FROM apps.po_headers_all poh,
               apps.po_distributions_all pod,
               (SELECT pap1.person_id, pap2.full_name
                  FROM (SELECT   MAX (effective_end_date) end_date,
                                 person_id person_id
                            FROM apps.per_all_people_f pap
                        GROUP BY person_id) pap1,
                       (SELECT full_name, person_id,
                               effective_end_date end_date
                          FROM apps.per_all_people_f) pap2
                 WHERE pap1.person_id = pap2.person_id
                   AND pap1.end_date = pap2.end_date) pap
         WHERE poh.po_header_id = pod.po_header_id
           AND pod.deliver_to_person_id = pap.person_id) pod
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND ent.transaction_number = aia.invoice_num
   AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
   AND xdl.source_distribution_type = 'AP_INV_DIST'
   AND aid.invoice_id = aia.invoice_id
   AND ent.entity_code = 'AP_INVOICES'
   AND NVL (aid.amount, 0) <> 0
   AND aid.po_distribution_id = pod.po_dist_id(+)
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

No comments:

Post a Comment