AP_CHECKS_ALL.PAYMENT_METHOD_LOOKUP_CODE and AP_CHECKS_ALL.PAYMENT_METHOD_CODE in Oracle APPS

Below is from Oracle Metalink ID 1919051.1

PAYMENT_METHOD_LOOKUP_CODE is obsolete in R12 hence it should not be referred in R12. If you want to refer payment method code from Invoice header then PAYMENT_METHOD_CODE should be used. During upgrade to R12 we populate PAYMENT_METNHOD_CODE with PAYMENT_METHOD_LOOKUP_CODE for migrated invoices.

SELECT payment_method_code, payment_method_name, description

  FROM iby_payment_methods_tl

 WHERE payment_method_code IN

          (SELECT DISTINCT PAYMENT_METHOD_CODE

              FROM AP_CHECKS_ALL

              )

AND language = USERENV('LANG')

Query to get Supplier Site Details(Payment Method, Term) details in Oracle APPS

SELECT assa.vendor_id

      ,asa.vendor_name 

      ,assa.vendor_site_code

      ,asa.segment1 vendor_number

      ,assa.vendor_site_id

      ,assa.address_line1

      ,assa.address_line2

      ,assa.city

      ,assa.state

      ,assa.zip postal_code

      ,assa.country

      ,asa.vat_registration_num

      ,asa.vendor_type_lookup_code vendor_type

      ,decode(nvl(assa.hold_unmatched_invoices_flag, 'N'), 'Y', 'PO', 'NONPO') invoice_type

      ,asa.invoice_currency_code currency_code

      ,assa.org_id

      ,hou.name operating_unit

      ,assa.vendor_site_code 

      ,gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 liability_account

      ,apt.name term_name

      ,ipmb.payment_method_code

  FROM fnd_lookup_values       lv

      ,iby_payment_methods_b   ipmb

      ,iby_ext_party_pmt_mthds epm

      ,hr_organization_units   hou

      ,iby_external_payees_all iepa

      ,ap_terms                apt

      ,gl_code_combinations    gcc

      ,ap_supplier_sites_all   assa

      ,ap_suppliers            asa

 WHERE lv.attribute2(+) = 'Y'

   AND lv.lookup_code(+) = assa.pay_group_lookup_code

   AND ipmb.payment_method_code(+) = epm.payment_method_code

   AND NVL(epm.primary_flag, 'Y') = 'Y' -- this is the current/active version.  when you make a change, object_version_number changes and old record primary_flag will change to N

   AND iepa.supplier_site_id = assa.vendor_site_id

   AND epm.ext_pmt_party_id (+) = iepa.ext_payee_id

   AND iepa.payee_party_id = asa.party_id

   AND apt.term_id = assa.terms_id

   AND gcc.code_combination_id = assa.accts_pay_code_combination_id

   AND assa.vendor_id = asa.vendor_id

   AND assa.org_id = hou.organization_id

   -- Supplier and Site Validations

   AND nvl(assa.inactive_date, SYSDATE + 1) >= TRUNC(SYSDATE)  -- site must be active

   AND nvl(asa.end_date_active, SYSDATE + 1) >= TRUNC(SYSDATE) -- supplier must be active

   AND asa.vendor_type_lookup_code = 'VENDOR'

AP_INVOICE_LINES_ALL.LINE_SOURCE in Oracle APPS

LINE_SOURCE --> Source of the invoice line. Validated against AP_LOOKUP_CODES.LOOKUP_CODE for LOOKUP_TYPE as LINE SOURCE

 

SELECT *

  FROM ap_lookup_codes

 WHERE lookup_type = 'LINE SOURCE'

AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE(INVOICE LINE TYPE) in Oracle APPS

LINE_TYPE_LOOKUP_CODE --> Type of invoice line. Possible values for this column are derived from FND_LOOKUP_VALUES for lookup_type 'INVOICE LINE TYPE'.

 

SELECT *

  FROM fnd_lookup_values

 WHERE lookup_type = 'INVOICE LINE TYPE'

    AND language = USERENV('LANG')

Query to get AWT Invoice for given Standard Invoice in Oracle Payables.

 SELECT wht_inv.invoice_id

  FROM ap_invoices_all std_inv

      ,ap_invoice_distributions_all std_inv_dist

      ,ap_invoices_all wht_inv

WHERE std_inv.invoice_id = 123456789

  AND std_inv.invoice_id = std_inv_dist.invoice_id

  AND std_inv.org_id = std_inv_dist.org_id

  AND std_inv_dist.awt_invoice_id = wht_inv.invoice_id

  AND std_inv.org_id = wht_inv.org_id

Where 123456789 is the Standard Invoice ID.

Important Questions in Oracle Payables

• Types of Invoices in Oracle Apps?
• Standard
• Prepayment
• Expense Report
• Mixed Invoice
• Credit Invoice
• Debit Invoice

• What is Mixed Type Invoice in Oracle Apps?
• Mixed Invoice is one of the Invoice Type in the Oracle Payables. 
• You can enter both the Negative(-) and the Positive(+) amount for this Mixed Type Invoice. 
• This Payment type is not rigid like Standard, Prepayment, Credit & Debit Memo Invoices to enter the amount in the specific signs (Positive or Negative).

• Difference between the Manual Hold and the System Hold?
• System Hold apply to the Invoice if something mismatched in the Invoice as per the Standard Process like Invoice Header Total and Line Total Should be equal other System Hold for Example related to Invoice Matching if something goes about the Invoice Tolerance Limit then System put the Hold. 
• System hold is something related to setup Controls but whereas Manual Hold is something which put manually in the Invoice due to any reason like Product received from the Supplier is damaged so need to hold the Payment for that Invoice.

• What is Pay alone in AP Invoice?
• Pay alone is something related to Invoice Payment. 
• This is the Flag we set for AP Invoice, it means this invoice will be paid alone. 
• For example, Supplier XYZ has two Invoices, but for one Invoice we have enabled the Pay Alone Flag then when we will run the Payment Batch then System will create one check for one invoices and separate one check for Pay Alone Invoice. This is the working of Pay Alone.

• Can we pay the AP invoice before Due Date?
• Yes, we can pay the Invoice before Due date. 
• For Manual Payment, there is no concern but for Payment Batch, when we are running the Payment Process Request((PPR) then we need to Enter the Pay Through Date this Date is Very Important, If we have set the Pay Through Date “15-Aug” then this Payment Request will pick only those AP invoices which has been due before “15-Aug-YYYY”.

• How AP Invoice Payment Due Date Calculates in Oracle Payables?
• Invoice Payment Due date depends on the two factors.
• Payment Terms attached to the Invoice
• Invoice Date.
• For Example, if Payment Terms is 30 Days and the Invoice Date is 01-Aug-2020 then Payment Due Date Will be 01-Sep-2020

• What is Primary and Secondary Ledger in Oracle Payables?
• Primary Ledger is the main Ledger and Secondary Ledger is the replica of the Primary Ledger. 
• Transactions will be done in the Primary Ledger. 
• The main reason of using Primary and Secondary Ledger is the difference in the Organization requirement and the statuary requirement. 
• For Ex, One US based company office is in India and as per US, their Calendar works from Oct to Sep but the in India their calendar works from April to March. So in this Kind of requirement, Primary and Secondary Ledger concept comes. Where we can design the Primary calendar as per the US based but can design the Secondary calendar as per India statuary requirement. 

• What is Recurring Invoice in Oracle?
• As its name represents ‘Recurring’. Recurring means again and again. If any Organization books the Office rent invoice every month for the same amount or any other fixed expenses every month then oracle has provided the Recurring Invoice. We just need to do the recurring Invoice setup for that amount and system will create the invoice automatically on the First day of the Months.

• What is the use of Payables Trial Balance Report ?
• Payables Trial Balance Report shows the Total Liability or the Supplier Outstanding in the System. 
• This Report shows the Liability in the System supplier and Site Level. 
• This Provide the Summary Information’s for all the Unpaid amount for the Supplier Invoices which are validated.

• What we do in the AP and GL reconciliation ?
• In the AP and GL reconciliation, we try to match the Total Liability from the Payables with the Liability accounts total in the GL. 
• We have some set of Liability accounts in the Payables, which we only use in the Invoice Headers to book the Liability and we match only these Liability GL accounts in the AP and GL reconciliation report.
• We took the help of Payables Trial Balance report to find the Total AP liability and then run the GL Trial Balance report to match the Payables Trial Balance Report Total with the GL trial Liability Accounts.

• Distribution Set in Oracle Payables?
• Distribution set is the combination of multiple Distribution lines using different -2 GL accounts Combination. 
• For Example, we booked most of the AP invoices in two different GL accounts combination so each time we need to enter two lines in the invoice distributions to book the AP invoices expenses in these two GL accounts but this process can be make query quickly and easy. 
• Oracle have functionality like Distributions set in Oracle Payables where we can any number of GL accounts line for a Given Distribution sets and then in the Oracle Payables Invoices we don’t need to create Distribution lines manually. 
• We just need to enter the Distributions Set in the AP Invoice Lines and oracle system automatically creates the Invoice distribution lines with GL accounts given in the distribution set in Oracle Payables R12.