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

Read More

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

Read More

FA : Query to fetch Asset Books - Cost History

Here is query to get the Cost History fotr particular asset
select
    fb.book_type_code               "Book",
    fa.asset_number                 "Asset Number",
    fb.transaction_header_id_in     "Reference Number",
    fth.transaction_type_code       "Transaction Type",
    fth.transaction_date_entered    "Transaction Date",
    fth.date_effective              "Date Effective",
    fb.cost                         "Cost",
   fa.asset_id
from
    fa_additions_b fa,
    fa_books fb,
    fa_transaction_headers fth
where
    1=1
and fb.asset_id = fa.asset_id
and fth.transaction_header_id (+) = fb.transaction_header_id_in
and fth.asset_id (+) = fb.asset_id
and fa.asset_number ='asset number'
order by
    1,2, fb.transaction_header_id_in desc

Read More

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.

Read More

AR : Receipt to fetch Total Uncleared Receipt Amount

SELECT   NVL (SUM (ps.amount_due_remaining), 0) total_uncleared_receipts
  FROM   hz_cust_accounts_all cust_acct,
         ar_payment_schedules_all ps,
         ar_receivable_applications_all arr,
         hz_cust_acct_sites_all acct_site,
         hz_party_sites party_site,
         hz_locations loc,
         hz_cust_site_uses_all site_uses,
         ar_cash_receipt_history_all crh,
         ar_cash_receipts_all acr,
         gl_code_combinations cc
 WHERE       TRUNC (ps.gl_date) <= :p_as_of_date
         AND ps.customer_id = cust_acct.cust_account_id
         AND cust_acct.account_number = :p_account_number
         AND ps.customer_id = cust_acct.cust_account_id
         AND acct_site.party_site_id = party_site.party_site_id
         AND loc.location_id = party_site.location_id
         AND ps.cash_receipt_id = acr.cash_receipt_id
         AND acr.cash_receipt_id = crh.cash_receipt_id
         AND crh.account_code_combination_id = cc.code_combination_id
         AND ps.trx_date <= :p_as_of_date
         AND ps.CLASS = 'PMT'
         AND ps.cash_receipt_id = arr.cash_receipt_id
         AND arr.status = 'UNAPP'
         AND ps.status = 'OP'
         AND site_uses.site_use_code = 'BILL_TO'
         AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
         AND NVL (site_uses.status, 'A') = 'A'
         AND cust_acct.cust_account_id = acct_site.cust_account_id
         AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
         AND ps.customer_id = acct_site.cust_account_id
         AND ps.customer_site_use_id = site_uses.site_use_id
         AND ps.cash_receipt_id = crh.cash_receipt_id
         AND crh.status NOT IN ('CLEARED')
HAVING   NVL (SUM (arr.amount_applied), 0) > 0;

Read More