AP(Payables) invoice related important table

 AP(Payables) invoice related important table

Invoice

AP_BATCHES_ALL

SELECT
  *
FROM
  AP_BATCHES_ALL
WHERE
  batch_id IN
  (
    SELECT
      batch_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );

AP_INVOICES_ALL

SELECT DISTINCT
  ai.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , ai.accts_pay_code_combination_id
  ) "Accts Pay Account"
FROM
  AP_INVOICES_ALL ai
WHERE
  invoice_id = :P_INVOICE_ID  
ORDER BY
  ai.invoice_id ASC;

AP_INVOICE_LINES_ALL
          
SELECT
  *
FROM
  AP_INVOICE_LINES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_DISTRIBUTIONS_ALL

SELECT
  aid.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aid.accts_pay_code_combination_id) "Accts Pay Account",
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , aid.dist_code_combination_id)
  "Dist Account"
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aid.invoice_id,
  aid.invoice_line_number,
  aid.distribution_line_number ASC;

ZX_LINES 
         
SELECT
  *
FROM
  ZX_LINES
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_summary 
         
SELECT
  *
FROM
  zx_lines_summary
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


zx_rec_nrec_dist
          
SELECT
  *
FROM
  zx_rec_nrec_dist
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_det_factors  
        
SELECT
  *
FROM
  zx_lines_det_factors
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


AP_PAYMENT_SCHEDULES_ALL 
         
SELECT
  *
FROM
  AP_PAYMENT_SCHEDULES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

ap_recurring_payments_all
 
SELECT DISTINCT
  arp.*
FROM
  ap_recurring_payments_all arp,
  ap_invoices_all ai
WHERE
  ai.recurring_payment_id = arp.recurring_payment_id
AND ai.invoice_id         = :P_INVOICE_ID;


AP_HOLDS_ALL 
         
SELECT
  *
FROM
  AP_HOLDS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

AP_HOLD_CODES
          
SELECT
  *
FROM
  AP_HOLD_CODES
WHERE
  hold_lookup_code IN
  (
    SELECT
      hold_lookup_code
    FROM
      AP_HOLDS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_TERMS
          
SELECT
  *
FROM
  AP_TERMS
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );



AP_TERMS_LINES
          
SELECT
  *
FROM
  AP_TERMS_LINES
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_INVOICE_PREPAYS_ALL 
         
SELECT
  *
FROM
  AP_INVOICE_PREPAYS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_CHRG_ALLOCATIONS_ALL 

SELECT DISTINCT
  aca.*
FROM
  AP_CHRG_ALLOCATIONS_ALL aca,
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  (
    aca.charge_dist_id = aid.invoice_distribution_id
  OR aca.item_dist_id  = aid.invoice_distribution_id
  )
AND aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aca.charge_dist_id;

AP_INV_APRVL_HIST_ALL
          
SELECT
  *
FROM
  AP_INV_APRVL_HIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID
ORDER BY
  1;


AP_ALLOCATION_RULES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_ALLOCATION_RULE_LINES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULE_LINES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_SELF_ASSESSED_TAX_DIST_ALL
          
SELECT
  *
FROM
  AP_SELF_ASSESSED_TAX_DIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_RELATIONSHIPS
           
SELECT
  *
FROM
  AP_INVOICE_RELATIONSHIPS
WHERE
  original_invoice_id = :P_INVOICE_ID
OR related_invoice_id = :P_INVOICE_ID;