Introduction

This Post is used for GL Transactions for AP  in Oracle EBS R12.

 

Script used for 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,

CASE

WHEN aia.SOURCE = ‘RECURRING INVOICE.’

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;

What we expect in the script

This script helps us to comprehend how to GL Transactions for AP. Couple of tables which is being used in the Query are

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 etc.

Summary

This Post described the script GL Transactions for AP.  in Oracle EBS R12.

 

Got any queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.