Showing posts with label AP. Show all posts
Showing posts with label AP. Show all posts

AP : QUERY: Supplier Sites - Payment Details

Here is query to Fetch QUERY: Supplier Sites - Payment Details.

select
    s.segment1                              "Supplier Number",
    ass.vendor_site_code                    "Site Name",
    haou.name                               "Operating Unit",
    iv.payment_method_code                  "Payment Method Code",
    iv.payment_method_name                  "Payment Method",
    iv.inactive_date                        "End Date",
    iep.delivery_channel_code               "Delivery: Channel",
    ibi1.meaning                            "Delivery: Bank Instr 1",
    ibi2.meaning                            "Delivery: Bank Instr 2",
    iep.bank_instruction_details            "Delivery: Bank Instr Det",
    iep.settlement_priority                 "Delivery: Settlement Priority",
    iep.payment_text_message1               "Delivery: Pay Text 1",
    iep.payment_text_message2               "Delivery: Pay Text 2",
    iep.payment_text_message3               "Delivery: Pay Text 3",
    iep.exclusive_payment_flag              "Spec: Pay Each Document Alone",
    iep.bank_charge_bearer                  "Spec: Bank Charge Bearer",
    ipr.meaning                             "Spec: Payment Reason",
    iep.payment_reason_comments             "Spec: Payment Reason Comments",
    ifo.format_name                         "Spec: Payee-specified Format",  
    iep.remit_advice_delivery_method        "Rem Advice: Delivery Method",
    iep.remit_advice_email                  "Rem Advice: E-Mail",
    iep.remit_advice_fax                    "Rem Advice: Fax",
    s.vendor_id,
    s.party_id,
    iep.ext_payee_id,
    iep.bank_instruction1_code, iep.bank_instruction2_code,
    iep.payment_reason_code,
    iep.payment_format_code
from 
    ap_suppliers s,
    ap_supplier_sites ass,
    hr_all_organization_units haou,
    (select ieppm.ext_pmt_party_id, ieppm.payment_method_code, ieppm.inactive_date, ipm.payment_method_name
     from   iby_ext_party_pmt_mthds ieppm, iby_payment_methods_vl ipm
     where  ieppm.primary_flag = 'Y'
     and    ieppm.payment_flow = 'DISBURSEMENTS'
     and    ipm.payment_method_code = ieppm.payment_method_code) iv,
    iby_external_payees_all iep,
    iby_bank_instructions_vl ibi1,
    iby_bank_instructions_vl ibi2,
    iby_payment_reasons_vl ipr,
    iby_formats_vl ifo
where
    1=1
and ass.vendor_id = s.vendor_id
and haou.organization_id = ass.org_id
and iep.payee_party_id = s.party_id
and iep.supplier_site_id = ass.vendor_site_id
and iv.ext_pmt_party_id (+) = iep.ext_payee_id
and ibi1.bank_instruction_code (+) = iep.bank_instruction1_code
and ibi2.bank_instruction_code (+) = iep.bank_instruction2_code
and ipr.payment_reason_code(+) = iep.payment_reason_code
and ifo.format_code(+) = iep.payment_format_code
and s.segment1 = '11208'
order by 1,2,3,4
Filed under   | 

Query: Supplier Sites - Invoice Management


select

    s.segment1                              "Supplier Number",
    hps.party_site_name                     "Address Name",
    haou.name                               "Operating Unit",
    ass.vendor_site_code                    "Supplier Site",
    ass.invoice_amount_limit                "Invoicing: Amount Limit",
    null                                    "Invoicing: Tolerance",
    decode(ass.match_option,
            'R','Receipt',
            'P','Purchase Order',
            null)                           "Inv: Match Option",
    ass.invoice_currency_code               "Inv: Currency",
    ass.hold_all_payments_flag              "Inv: Hold - All Invoices",
    ass.hold_unmatched_invoices_flag        "Inv: Hold - Unmatched Inv",
    ass.hold_future_payments_flag           "Inv: Hold - Unvalidated Inv",
    ass.hold_reason                         "Inv: Payment Hold Reason",
    ato.tolerance_name                      "Inv: Service Tolerance",
    ass.payment_currency_code               "Payment: Currency",
    ass.payment_priority                    "Payment: Priority",
    ass.pay_group_lookup_code               "Payment: Pay Group",
    flv.meaning                             "Payment: Deduct Bank Charge",
    at.name                                 "Terms: Terms",
    ass.terms_date_basis                    "Terms: Terms Date Basis",
    ass.pay_date_basis_lookup_code          "Terms: Pay Date Basis",
    ass.retainage_rate                      "Terms: Retainage Rate",
    ass.always_take_disc_flag               "Terms: Take Discount",
    ass.exclude_freight_from_discount       "Terms: Ex Freight Discount",
    s.vendor_id,
    ass.vendor_site_id
from 
    ap_suppliers s,
    ap_supplier_sites ass,
    hz_party_sites hps,
    hr_all_organization_units haou,
    ap_tolerances ato,
    ap_terms at,
    fnd_lookup_values_vl flv
where
    1=1
and ass.vendor_id = s.vendor_id
and hps.party_id = s.party_id
and hps.party_site_id = ass.party_site_id
and haou.organization_id = ass.org_id
and ato.tolerance_id (+) = ass.services_tolerance_id
and at.term_id (+) = ass.terms_id
and flv.lookup_type (+) = 'BANK CHARGE BEARER'
and flv.lookup_code (+) = ass.bank_charge_bearer
and s.segment1 = '112345'
order by    1,2,3,4
Filed under   | 

AP : Reconciling A/P Accrual Accounts Balance


At any given time, the balance of the A/P accrual accounts can account for the following transactions:

  •         Uninvoiced Receipts
  •         Over-invoiced Receipts
  •         Invoice Price Variance (for transactions created using Release 9)
  •         Errors (Invoices or inventory transactions charged to this Account by mistake)

You need to analyze the balance of the A/P accrual accounts, distinguish accrued receipts from invoice price variances, and identify errors.


AP : Debugging AP data Movement


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'
              );
        );

 

Filed under   |