XLA events XLA headers , XLA lines and AP payments join

with event as (
                  select /*+ materialize */
                       xe.entity_id ,
                       sum(xl.accounted_cr) accounted_dr,
             avg(case when xe.event_type_code in ('PAYMENT CREATED','PAYMENT CANCELLED') then 1 else 0 end) ex_type,
                         max(xh.accounting_date) event_date
                  from
                       xla_events xe,
                       xla_ae_headers xh,
                       xla_ae_lines xl,
                       gl_ledgers lg
                  where /*xe.event_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and */xh.accounting_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
                        and xe.process_status_code='P'
                        and xh.event_id=xe.event_id
                        and xh.ae_header_id=xl.ae_header_id
                        and xl.accounting_class_code='CASH'
                        and xh.ledger_id=lg.ledger_id
                        and lg.ledger_category_code='PRIMARY'
                      --  and xe.entity_id=64139354
                  group by xe.entity_id
                  )
select /*+ materialize */
                   aip.invoice_id,
                 
                  xe.entity_id,
                   xe.event_id,
                   xe.event_date,
                   e.event_date,
                   aip.amount,
                   accounted_dr
 
                 
                 
              from
                    xla_events xe inner join ap_invoice_payments_all aip
                                             on xe.event_id=aip.accounting_event_id
                     
                                  inner join event e
                                             on  xe.entity_id=e.entity_id
                                           
                                       
                                           and e.event_date<=to_date('30/04/2018', 'dd/mm/yyyy')
                                  inner join ap_checks_all ca       
                                             on aip.check_id=ca.check_id
                                  left join  gl_daily_rates dr
                                             on ca.currency_code=dr.from_currency
                                             and dr.to_currency='USD'
                                             and xe.event_date=dr.conversion_date

API to update employee supervisor

DECLARE
   -- Local Variables
   -- -----------------------
   lc_dt_ud_mode           VARCHAR2(100)    := NULL;
   ln_assignment_id       NUMBER                  := 18546;
   ln_supervisor_id        NUMBER                  := 14024;
   ln_object_number       NUMBER                  := 3;
   ln_people_group_id  NUMBER                  /*:= 1*/;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                                 BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id       HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   ln_comment_id                             PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning        BOOLEAN;
 
   ln_special_ceiling_step_id                    PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                                          VARCHAR2(30);
 ld_effective_start_date                             PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 ld_effective_end_date                              PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning   BOOLEAN;
 lb_other_manager_warning                  BOOLEAN;
 lb_spp_delete_warning                          BOOLEAN;
 lc_entries_changed_warning                VARCHAR2(30);
 lb_tax_district_changed_warn             BOOLEAN;
 l_effective_date date;
 l_emp_hire_date date;
 l_sup_hire_date date;
 l_person_id number;



BEGIN
 
for i in (select af.assignment_id,
       af.effective_start_date,
       af.object_version_number,
       p.person_id supervisor_id,
       af.person_id
 from xxcorp_emp_sup es,
              (select distinct person_id,
                      to_number(employee_number) employee_number
                      from per_people_f) p,
             
              (select * from (
select af.person_id,
       af.effective_start_date,
       af.assignment_id,
       af.object_version_number,
       row_number() over (partition by af.person_id order by af.effective_start_date desc) row_id
from PER_ALL_ASSIGNMENTS_F af
) where row_id=1) af
where
 es.person_id=af.person_id
and es.status is null
and es.supervisor_num=p.employee_number
--and es.person_id=1236
)
loop
 
ln_assignment_id  := i.assignment_id;
   ln_supervisor_id := i.supervisor_id;
   ln_object_number  := i.object_version_number;
   l_effective_date := i.effective_start_date;

 select hire_date into l_emp_hire_date from xxbak_employees_hire_date where person_id= i.person_id;
  select hire_date into l_sup_hire_date from xxbak_employees_hire_date where person_id= i.supervisor_id;

if l_emp_hire_date < l_sup_hire_date then
  lc_dt_ud_mode := 'UPDATE';
  else
     lc_dt_ud_mode := 'CORRECTION';
     end if;
   
     if l_sup_hire_date > l_effective_date
       then l_effective_date := l_sup_hire_date;
       end if;
     

 
   if l_emp_hire_date < l_sup_hire_date then
  lc_dt_ud_mode := 'UPDATE';
  else  lc_dt_ud_mode := 'CORRECTION';
  end if;

select decode(lc_dt_ud_mode,'UPDATE',l_effective_date+1,l_effective_date) into l_effective_date from dual;


  -- Update Employee Assignment
  -- ---------------------------------------------
 l_person_id := i.person_id;
  begin
    -- DBMS_OUTPUT.put_line(lc_dt_ud_mode);
 hr_assignment_api .update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => l_effective_date+1,
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => ln_supervisor_id,
  p_change_reason                           => NULL,
--  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_ass_attribute1 =>92,
--  p_segment1                                       => 204,
--  p_segment3                                       => 'N',
 -- p_normal_hours                              => 10,
 -- p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );
 update xxcorp_emp_sup s set status='Y' where s.person_id=l_person_id;
 EXCEPTION
         WHEN OTHERS THEN
                    --  ROLLBACK;
                    dbms_output.put_line(l_person_id);
                     DBMS_OUTPUT.put_line(lc_dt_ud_mode);
                      dbms_output.put_line(SQLERRM);
                     
                     -- update xxcorp_emp_sup s set status= where s.person_id=l_person_id;
                   

 end;
 end loop;

 --COMMIT;


END;

How to find encumbrance, budget and actual from backend

select

sum(case when gb.actual_flag='B'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) budget,





sum(case when gb.actual_flag='A'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) actual,
sum(case when gb.actual_flag='E'
then (SUM(NVL(PERIOD_NET_DR,0)) - SUM(NVL(PERIOD_NET_CR,0)))
else 0 end) encumb
from apps.gl_balances gb,
apps.gl_code_combinations gcc
where





gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
and gb.LEDGER_ID = 6045
and gb.PERIOD_YEAR = '2018'
and gb.PERIOD_NUM <= 3 -- March
and gb.CURRENCY_CODE = 'AZN'
and gcc.code_combination_id = 2133476

How to select PO Match Approval Level from PO_LINE_LOCATIONS_ALL

SELECT

DECODE (INSPECTION_REQUIRED_FLAG || RECEIPT_REQUIRED_FLAG,





'NN', '2-Way',

'NY', '3-Way',

'YY', '4-Way',

'Not Specified')

"Match Approval Level"






FROM PO_LINE_LOCATIONS_ALL PLL

API to delete PO

DECLARE
l_result BOOLEAN ;
l_po_header_id NUMBER ;
l_type_lookup_code VARCHAR2(100) ;
l_validation VARCHAR2(1) := 'Y' ;
cursor c is
sELECT po_header_id, pha.segment1, authorization_status , type_lookup_code, PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID), pha.closed_code
FROM po_headers_all pha
WHERE org_id = 93
and NVL(cancel_flag, 'N') = 'N'
and PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID) like '%Incomplete%'
and type_lookup_code='STANDARD';

BEGIN

for i in c loop
l_po_header_id:= i.po_header_id;
l_type_lookup_code := i.type_lookup_code;

DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||l_po_header_id);
l_result := po_headers_sv1.delete_po (X_po_header_id => l_po_header_id
,X_type_lookup_code => l_type_lookup_code
,p_skip_validation => l_validation);

IF l_result = TRUE THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Deleted Successfully');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Failed to Delete');
END IF;
DBMS_OUTPUT.put_line ( 'Deletion Process Over');
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);
END;

How to get AP invoice payments using XLA events

with event as (
select /*+ materialize */
entity_id ,
max(xe.event_date) event_date
from
xla_events xe

where xe.event_date <= sysdate
and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
and xe.process_status_code='P'
group by entity_id




)
select
aip.invoice_id,
sum(aip.amount) payment_amount






from
xla_events xe inner join ap_invoice_payments_all aip
on xe.event_id=aip.accounting_event_id
inner join event e
on xe.entity_id=e.entity_id
and xe.event_date<=e.event_date
inner join ap_checks_all ca
on aip.check_id=ca.check_id


group by aip.invoice_id

How to get invoice and purchase orders (po) / relations between invoices and purchase orders (pos)

select invoice_id,
LISTAGG(po_number, '; ') WITHIN GROUP (ORDER BY po_number ) po_number,
LISTAGG(PO_creator_name, '; ') WITHIN GROUP (ORDER BY po_number ) PO_creator_name,
LISTAGG(requestor_name, '; ') WITHIN GROUP (ORDER BY po_number ) requestor_name




from(

select
ail.invoice_id,
ail.po_header_id,
pha.segment1 po_number,
he1.full_name PO_creator_name,
pr.requestor_name,
row_number() over (partition by ail.invoice_id, ail.po_header_id,pha.segment1
,he1.full_name, pr.requestor_name order by ail.invoice_id) row_id





from ap_invoice_lines_all ail inner join po_headers_all pha on ail.po_header_id=pha.po_header_id
left join fnd_user fuv1 on pha.created_by=fuv1.user_id
left join hr_employees he1 on fuv1.employee_id=he1.employee_id
left join xxcorp_po_requestor pr on pha.po_header_id=pr.po_header_id





) where row_id=1
group by invoice_id

How to Create Purchase Order from Backend by using interface tables

declare
CURSOR c2 (v_po_number varchar2)
        IS
        select distinct po_num, po.line_num,po.item_id, po.po_header_id,po.promised_date, po.agent_id, po.type_lookup_code,
         po.attribute12, po.attribute14, po.terms_id, po.org_id, po.rate_date,
                    po.RATE_TYPE,  po.vendor_id, po.vendor_site_id, po.currency_code, po.need_by_date, po.shipment_num,
                    remaining_qty, po.unit_price, po.SHIP_TO_ORGANIZATION_ID, line_type_id,
                    po.DISTRIBUTION_NUM,  po.code_combination_id, po.DESTINATION_CONTEXT, po.po_line_id, req_distribution_id,
                      po.item_description, unit_meas_lookup_code
                     from xxbak_open_po_v po
                   
                     where
                          remaining_qty>0
                          and po.PO_NUM=v_po_number
                   
                          ;
                   




        l_user_emp_id NUMBER;
        l_item_account_id NUMBER;
        l_location_id NUMBER;
        l_user_id NUMBER;
        l_incident_number NUMBER;
        l_org_id NUMBER;
        l_sob_id NUMBER;
        l_sob_currency VARCHAR2 (15);
        l_line_no NUMBER;
        l_request_id NUMBER;
        l_doc_num NUMBER;
         l_organization_id NUMBER;




  BEGIN
            l_user_id := fnd_profile.VALUE ('USER_ID');
            l_org_id :=fnd_profile.VALUE ('ORG_ID');
            l_organization_id :=114; -- fnd_profile.VALUE ('GAA_ORGANIZATION_ID');
            l_sob_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');   
             
         
          -- dbms_output.put_line(1);
         
     FOR h in (select distinct po_num,
                      po.po_header_id,
                      po.agent_id,
                      po.type_lookup_code,
                      po.attribute12,
                      po.attribute14,
                      po.terms_id,
                      po.org_id,
                      po.rate_date,
                      po.RATE_TYPE,
                      po.rate,
                      po.vendor_id,
                      po.vendor_site_id,
                      po.currency_code,
                      po.ship_to_location_id_h,
                      po.bill_to_location_id ,
                      po.fob_lookup_code,
                      po.note_to_authorizer,
                      po.note_to_vendor_h,
                      po.note_to_receiver,
                      po.comments,
                      po.attribute_category,
                      attribute4,
                          attribute5,
                          attribute7,
                          attribute9,
                          attribute10,
                          attribute13
                                       
                     from xxbak_open_po_v po
                      where remaining_qty>0                                 
                 
                           
                       ) loop
                             





           INSERT INTO po_headers_interface
                        (INTERFACE_HEADER_ID, --1
                          ACTION,--2
                          ORG_ID,--3
                          AGENT_ID,--4
                          DOCUMENT_TYPE_CODE, --5
                          DOCUMENT_NUM,--6
                          VENDOR_ID, --7
                          VENDOR_SITE_ID, ---8
                          LOAD_SOURCING_RULES_FLAG ,---9
                          COMMENTS, ---10
                          CURRENCY_CODE, --11
                          RATE_TYPE, --12
                          rate, ---13
                          RATE_DATE, ---14
                          attribute12, ---15 cost_center
                          attribute14, ---16  asr
                          ship_to_location_id,
                          bill_to_location_id,
                          terms_id,
                          fob,
                          note_to_vendor ,
                          note_to_receiver,
                          attribute_category,
                          attribute4,
                          attribute5,
                          attribute7,
                          attribute9,
                          attribute10,
                          attribute13
                          )
                    VALUES
                        (PO_HEADERS_INTERFACE_S.NEXTVAL, --1
                        'ORIGINAL', --2
                         h.org_id,---3
                         h.agent_id, --4
                         h.type_lookup_code, --5
                         'P-'||h.po_num, ---6
                         h.vendor_id, ---7
                         h.vendor_site_id,---8
                         'N', ----9                   
                         h.comments, --10
                         h.currency_code, --11
                         decode(h.RATE_TYPE,1000,'User',h.RATE_TYPE), --12
                         h.rate,---13
                         decode(h.CURRENCY_CODE,'AZN', null,h.rate_date), ---14
                         h.attribute12, --15
                         h.attribute14,
                         h.ship_to_location_id_h,
                         h.bill_to_location_id,
                         h.terms_id,
                         h.fob_lookup_code,
                         h.note_to_vendor_h,
                         h.note_to_receiver,
                         h.attribute_category,
                         h.attribute4,
                         h.attribute5,
                         h.attribute7,
                         h.attribute9,
                         h.attribute10,
                         h.attribute13
                         ); 




         for l in c2(h.po_num) loop   
         
                    INSERT INTO po_lines_interface
                    (interface_line_id, ---1
                    interface_header_id, ---2
                    line_type_id,---3
                    item_id, ---4
                    item_description,  ---5
                    unit_price, ---6
                    unit_of_measure, ---7
                    quantity,---8
                    note_to_vendor, ---9
                    shipment_num, ----10
                    line_num, ---11
                    NEED_BY_DATE,  ---12
                    PROMISED_DATE, ---13
                    SHIPMENT_TYPE, ---14
                    ship_to_organization_id)  ---15 
                    VALUES
                    ( PO_LINES_INTERFACE_S.NEXTVAL,---1
                     PO_HEADERS_INTERFACE_S.CURRVAL, ---2
                     l.line_type_id,---3
                     l.item_id, ---4
                     l.item_description, ---5
                     l.unit_price, ---6
                     l.unit_meas_lookup_code, ---7
                     l.remaining_qty, ---8
                     l.item_description,---9
                     l.shipment_num, ---10
                     l.line_num, ---11
                     l.need_by_date,---12
                     l.promised_date,---13
                     'STANDARD',---14
                     l.SHIP_TO_ORGANIZATION_ID);
                   
                   INSERT INTO po_distributions_interface
                        (interface_line_id,---1
                        interface_header_id, ---2
                        interface_distribution_id, ---3
                        ORG_ID, ---4
                        QUANTITY_ORDERED, ---5
                        DESTINATION_TYPE_CODE, ---6
                        CHARGE_ACCOUNT_ID, -----7
                        DISTRIBUTION_NUM, -----8
                        DESTINATION_ORGANIZATION_ID, --9
                        req_distribution_id)
                    VALUES
                        (PO_LINES_INTERFACE_S.CURRVAL, ---1
                         PO_HEADERS_INTERFACE_S.CURRVAL, ---2
                         PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,----3
                         l.ORG_ID,  ---4
                         l.REMAINING_QTY, ----5
                         l.DESTINATION_CONTEXT, ----6
                         l.code_combination_id, ----7
                         l.DISTRIBUTION_NUM, ---8
                         l.SHIP_TO_ORGANIZATION_ID,
                         l.req_distribution_id);
                       
                     
             end loop;
     
   

    END LOOP;
    Commit;

                END;

How to Cancel PO line and Return PR and add PR to same PO?

Purchase Orders is Approved and it is necessary:

• Cancel PO line
• Return PR line
• Make necessary modifications in PR and submit second time for approval
• Add changed PR line to the same PO

1. Search for ‘Approved’ Purchase Order:

Note: You can search PO by its lines and cancel PO line without opening PO. Or you can search PO
by header, go inside PO,  stand on the line→ Tools→ Control→ in order to cancel PO line.

The difference of doing this is when you search PO by its lines and cancel line without opening it
then status of PO will remain as ‘Approved’. (in case if you need to keep the Approved status if you
have a lot of lines and do not want to submit PO to approval second time)

But when you search PO by its header and go inside PO and cancel line, then status of PO automatically
will change to ‘Requires Reapproval’.

Select the line which is going to be cancelled→ Tools→ Control→

Cancel PO Line:

After Cancel Line search PO to check its status. Status of PO has not been changed: “Approved’

From Purchasing Super User Navigator go to Autocreate in order to see cancelled PO Line which has been
returned to Autocreate:

Search by entering PR number which was related to your PO:

PR Line is shown in Autocreate. Select the line and go to Tools→ Return Requisition

Enter reason for Return and press Ok:

Check PR status: “Return”. Now you will be able to make necessary changes.
Note: When PR is in “Approved” status it is not possible to Edit PR.

Send to approval after necessary changes:

Now go to Autocreate search for your PR.  Tick PR line and in ‘Action’ box below choose in order to be able to
add to existing PO, then choose Manual.

Note: If you want to create completely new PO from the same PR then you need to select ‘Create’ from Action
box.

Click ‘Add to Document’ and ‘Create’

PO is created with details. You need just to submit for approval.

API to Close PO

DECLARE

x_action constant varchar2(20) := 'CLOSE';
x_calling_mode constant varchar2(2) := 'PO';
x_conc_flag constant varchar2(1) := 'N';
x_return_code_h varchar2(100);
x_auto_close constant varchar2(1) := 'N';
x_origin_doc_id number;
x_returned boolean;






CURSOR c_po_details IS

SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.closed_code,
pha.closed_date, authorization_status, PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID)
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND authorization_status = 'APPROVED'
AND pha.closed_code not in ('CLOSED', 'FINALLY CLOSED')
and PO_HEADERS_SV3.GET_PO_STATUS(pha.PO_HEADER_ID) not like '%Cancelled'

begin



fnd_global.apps_initialize (user_id => 6837,
resp_id => 20707,
resp_appl_id => 201);


for po_head in c_po_details

LOOP

mo_global.init (po_head.document_type_code);
mo_global.set_policy_context ('S', po_head.org_id);






x_returned :=
po_actions.close_po(
p_docid => po_head.po_header_id,
p_doctyp => po_head.document_type_code,
p_docsubtyp => po_head.document_subtype,
p_lineid => NULL,
p_shipid => NULL,
p_action => x_action,
p_reason => NULL,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL);


DBMS_OUTPUT.PUT_LINE(x_return_code_h);
IF x_returned = TRUE THEN

DBMS_OUTPUT.PUT_LINE(x_return_code_h);

DBMS_OUTPUT.PUT_LINE ('Purchase Order which just got Closed/Finally Closed is ' || po_head.segment1);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE ('API Failed to Close/Finally Close the Purchase Order');

END IF;





END LOOP;

END;

How Payable Invoices related Payment Data is stored in Oracle Apps R12? (Oracle Payments, Oracle Payables)

In this post, we will find the tables involved in storing the Payment Data related to the Payable INVOICE ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

AP_TERMS
SELECT *
FROM AP_TERMS
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_TERMS_LINES
SELECT *
FROM AP_TERMS_LINES
WHERE term_id IN
( SELECT DISTINCT terms_id
FROM AP_INVOICES_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_SCHEDULES_ALL
SELECT
amount_remaining,
batch_id,
due_date,
gross_amount,
hold_flag,
invoice_id,
payment_num,
SUBSTR(payment_status_flag,1,1) payment_status_flag,
org_id
FROM
AP_PAYMENT_SCHEDULES_ALL
WHERE
invoice_id = '166014';

AP_INVOICE_PAYMENTS_ALL
SELECT
check_id,
SUBSTR(invoice_payment_id,1,15) invoice_payment_id,
amount,
payment_base_amount,
invoice_base_amount,
accounting_date,
period_name,
posted_flag,
accounting_event_id,
invoice_id,
org_id
FROM
AP_INVOICE_PAYMENTS_ALL
WHERE
invoice_id = '166014'
ORDER BY check_id ASC;

AP_PAYMENT_DISTRIBUTIONS_ALL
SELECT tab.*
FROM AP_INVOICE_PAYMENTS_ALL aip,
AP_PAYMENT_DISTRIBUTIONS_ALL tab
WHERE aip.invoice_payment_id = tab.invoice_payment_id
AND aip.invoice_id = '166014';

AP_CHECKS_ALL
SELECT
check_id,
check_number,
vendor_site_code,
amount,
base_amount,
checkrun_id,
checkrun_name,
check_date,
SUBSTR(status_lookup_code,1,15) status_lookup_code,
void_date,
org_id
FROM
AP_CHECKS_ALL
WHERE check_id IN
( SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_PAYMENT_HISTORY_ALL
SELECT
payment_history_id,
check_id,
accounting_date,
SUBSTR(transaction_type,1,20) transaction_type,
posted_flag,
SUBSTR(accounting_event_id,1,10) accounting_event_id,
rev_pmt_hist_id,
org_id
FROM
AP_PAYMENT_HISTORY_ALL
WHERE check_id IN
(SELECT DISTINCT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
)
ORDER BY payment_history_id ASC;

AP_PAYMENT_HIST_DISTS
SELECT aphd.*
FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
AP_PAYMENT_HIST_DISTS aphd,
AP_PAYMENT_HISTORY_ALL aph
WHERE aid.invoice_id = '166014'
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aph.payment_history_id = aphd.payment_history_id;


AP_RECON_DISTRIBUTIONS_ALL
SELECT *
FROM AP_RECON_DISTRIBUTIONS_ALL
WHERE check_id IN
( SELECT check_id
FROM AP_INVOICE_PAYMENTS_ALL
WHERE invoice_id = '166014'
);

AP_DOCUMENTS_PAYABLE
SELECT
pay_proc_trxn_type_code,
calling_app_doc_unique_ref1 check_id,
calling_app_doc_unique_ref2 invoice_id,
calling_app_doc_unique_ref4 invoice_payment_id,
calling_app_doc_ref_number invoice_number,
payment_function,
payment_date,
document_date,
document_type,
payment_currency_code,
payment_amount,
payment_method_code
FROM
AP_DOCUMENTS_PAYABLE
WHERE calling_app_id = 200 -- Application id for Payables
AND calling_app_doc_unique_ref2 = '166014';

IBY_DOCS_PAYABLE_ALL
SELECT *
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014';

IBY_PAYMENTS_ALL
SELECT *
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);

IBY_PAY_INSTRUCTIONS_ALL
SELECT *
FROM IBY_PAY_INSTRUCTIONS_ALL
WHERE payment_instruction_id IN
(SELECT payment_instruction_id
FROM IBY_PAYMENTS_ALL
WHERE payment_id IN
(SELECT payment_id
FROM IBY_DOCS_PAYABLE_ALL
WHERE calling_app_id = 200
AND calling_app_doc_unique_ref2 = '166014'
);
);

AP PAYMENT TABLES TO XLA TABLES LINK

XLA_EVENTS
SELECT
DISTINCT xe.*
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xe.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
ORDER BY
xe.entity_id,
xe.event_number;

XLA_AE_HEADERS
SELECT DISTINCT xeh.*
FROM xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xeh.event_id,
xeh.ae_header_id ASC;

XLA_AE_LINES
SELECT DISTINCT xel.*,
fnd_flex_ext.get_segs('SQLGL','GL#', '50577' ,xel.code_combination_id) "Account"
FROM xla_ae_lines xel,
xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xel.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xel.ae_header_id,
xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*
FROM xla_distribution_links xdl,
xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xdl.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xdl.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id