Steps to Create With Holding Tax in Oracle Apps

Set Up Steps

 

ü  Enable Withhold Tax in Payable Options

ü  Enable Withhold Tax at Supplier Site

ü  Define Special Calendar for Withhold Tax

ü  Define Tax Codes

ü  Review Your Supplier

ü  Book an invoice with same supplier

ü  Review Withhold Tax Invoice created by Payables

Step No: 1

Enabling Withhold Tax at Payable Options

Nav: Setup > Options/Payable Options

Step No: 2

Enabling Withhold Tax at Supplier Site

Nav: Supplier > Entry

Step No: 3

Define Special Calendar for Withhold Tax

Nav: Setup/calendar/Special Calendar

Step No: 4

Define Tax Codes

Nav: Setup/Tax/Codes

Step No: 5

Review Your Supplier

Nav: Supplier/ Entry

Step No: 6

Book an standard invoice with same supplier for testing of With Holding. 

Nav: Invoices/ Entry/Invoice

Invoices Eligible for Payments Query in oracle apps

                                This query can give count of invoices by batch, payment method and payment group that can be or can not be selected for payments. If an invoices is on hold or not validated or not due for payment, it will not be selected by Payment Process Request.

 /* Formatted on 9/1/2015 11:05:52 AM (QP5 v5.240.12305.39446) */
  SELECT pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date,
         COUNT (1)
    FROM (SELECT pay_group_lookup_code,
                 aps.payment_method_code,
                 wfapproval_status,
                 NVL (
                    (SELECT 'N'
                       FROM ap_invoice_distributions_all aid
                      WHERE     aid.invoice_id = aia.invoice_id
                            AND match_status_flag <> 'A'
                            AND ROWNUM = 1),
                    'Y')
                    validated,
                 (SELECT batch_name
                    FROM ap_batches_all ab
                   WHERE ab.batch_id = aia.batch_id)
                    batch_name,
                 NVL (
                    (SELECT 'Y'
                       FROM ap_holds_all
                      WHERE     invoice_id = aia.invoice_id
                            AND release_lookup_code IS NULL
                            AND ROWNUM = 1),
                    'N')
                    holds,
                 TRUNC (aps.due_date) due_date
            FROM ap_invoices_all aia, ap_payment_schedules_all aps
           WHERE     aia.payment_status_flag = 'N'
                 AND aia.cancelled_date IS NULL
                 AND aia.invoice_id = aps.invoice_id) a
GROUP BY pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date
ORDER BY pay_group_lookup_code,
         payment_method_code,
         batch_name,
         holds,
         wfapproval_status,
         validated,
         due_date,
         8 DESC;

PAYMENT_TYPE_FLAG in AP_CHECKS_ALL table in oracle apps

AP_CHECKS_ALL

Important columns: 

AMOUNT : Payment amount
BANK_ACCOUNT_ID : longer used
BANK_ACCOUNT_NAME : Bank account name
CHECK_ID NUMBER : Payment identifier
CHECK_NUMBER : Payment number
VENDOR_NAME :  Supplier name
VENDOR_SITE_CODE :  Supplier site code

PAYMENT_TYPE_FLAG : Type of payment
A - Payment Process Request
M - Manual
Q - Quick payment
R - Reunfund

Description: 

AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers.  You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers. Your Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment.

Your Oracle Payables application stores address information for all payments. If you allow changes to the supplier payment address on manual payments or Quick payments, your Oracle Payables application maintains the new address information in this table. Your Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and BANK_ACCOUNT_TYPE for the supplier's bank information when you use the Electronic payment method. Your Oracle Payables application stores a dummy value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.

AP to GL link in oracle apps

SELECT          /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */
       DISTINCT gjh.doc_sequence_value gl_vou_num, ac.check_id, xal.source_id,
                aipa.invoice_payment_id, gjl.effective_date accounting_date,
                xal.accounted_dr, xal.accounted_cr, aps.segment1 vendor_code,
                aps.vendor_name vendor_name, aipa.invoice_id, aia.invoice_num,
                aia.invoice_date, aia.invoice_currency_code cur,
                aia.invoice_amount inv_amt_frc,
                aia.exchange_rate inv_exc_rate,
                aia.exchange_date inv_exc_date,
                aia.doc_sequence_value ap_vou_num, aipa.amount amt_paid,
                aipa.exchange_rate paid_exc_rate,
                aipa.exchange_date paid_exc_date, ac.check_number
               FROM gl_je_headers gjh,
                gl_je_lines gjl,
                xla_ae_headers xah,
                xla_ae_lines xal,
                xla.xla_transaction_entities xte,
                ap.ap_checks_all ac,
                gl_code_combinations glcc,
                ap_invoice_payments_all aipa,
                ap_invoices_all aia,
                ap_suppliers aps,
                ap_invoice_lines_all aila,
                ap_invoice_distributions_all aida,
                gl_import_references gir,
                xla_distribution_links xdl,
                ap_payment_hist_dists aphd
          WHERE 1 = 1
            AND gjh.je_header_id = gjl.je_header_id
            AND gjh.ledger_id = '1001'
            AND gjh.je_source = 'Payables'
            AND gjh.je_category = 'Payments'
            AND gjl.status = 'P'
            AND xal.accounting_class_code = 'LOSS'
            AND xdl.accounting_line_code = 'AP_LOSS_PMT'
            AND gjl.effective_date BETWEEN :p_from_date AND :p_to_date --GL Line Effective Dates
            AND aipa.accounting_event_id = xah.event_id
            AND NVL (xte.source_id_int_1, (-99)) = TO_CHAR (ac.check_id)
            AND xal.code_combination_id = glcc.code_combination_id
            AND xal.ae_header_id = xah.ae_header_id
            AND xte.application_id = xah.application_id
            AND xte.application_id = 200
            AND xte.ledger_id = 1001
            AND xte.entity_id = xah.entity_id
            AND ac.check_id = aipa.check_id
            AND aipa.invoice_id = aia.invoice_id
            AND aia.vendor_id = aps.vendor_id
            AND aia.invoice_id = aila.invoice_id
            AND aila.invoice_id = aida.invoice_id
            AND aila.line_number = aida.invoice_line_number
            AND aila.line_number = 1
          --AND glcc.segment3 = '123456789'
          --AND aida.distribution_line_number = 1
            AND gir.gl_sl_link_id = xal.gl_sl_link_id
            AND gir.gl_sl_link_table = xal.gl_sl_link_table
            AND gir.je_header_id = gjl.je_header_id
            AND gir.je_line_num = gjl.je_line_num
            AND aipa.invoice_payment_id = aphd.invoice_payment_id
            AND xdl.source_distribution_type = 'AP_PMT_DIST'
            AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
            AND xdl.ae_header_id = xal.ae_header_id
            AND xdl.ae_line_num = xal.ae_line_num
            AND xdl.application_id = xal.application_id

QUERY TO FIND PAYMENT AGAINST INVOICE IN ORACLE APPS

SELECT b.segment1 vendor_number,
       b.vendor_name vendor_name,
       c.vendor_site_code,
       c.pay_group_lookup_code,
       a.invoice_num invoice_number,
       a.invoice_date,
       a.gl_date,
       d.due_date,
       a.invoice_currency_code,
       a.invoice_amount,
       a.amount_paid,
       a.pay_group_lookup_code,
       d.payment_priority,
       (SELECT MAX (check_date)
          FROM ap_checks_all aca, ap_invoice_payments_all aip
         WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)
          "Last Payment Made on",
          a.cancelled_date
  FROM apps.ap_invoices_all a,
       apps.ap_suppliers b,
       apps.ap_supplier_sites_all c,
       apps.ap_payment_schedules_all d,
       apps.ap_invoice_payments_all ap,
       ap_checks_all ac
WHERE     a.vendor_id = b.vendor_id
       AND a.vendor_site_id = c.vendor_site_id
       AND b.vendor_id = c.vendor_id
       AND a.invoice_id = d.invoice_id
       AND ap.invoice_id = a.invoice_id
       AND ac.CHECK_ID = ap.CHECK_ID
       and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
       AND a.org_id = 89
       and a.invoice_id= 1234
       AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
       AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

AP Invoice to Payment link Query in oracle apps

  SELECT aca.check_number "Document Number",
         asa.segment1 "Supplier Number",
         asa.vendor_name "Supplier Name",
         aca.STATUS_LOOKUP_CODE "Payment Reconcilation Status",
         aca.CLEARED_AMOUNT "Payment Amount",
         aca.CURRENCY_CODE "Currency",
         aca.CLEARED_DATE "Payment Date",
         aia.invoice_num,
         aia.invoice_amount,
         AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
                                              aia.INVOICE_AMOUNT,
                                              aia.PAYMENT_STATUS_FLAG,
                                              aia.INVOICE_TYPE_LOOKUP_CODE)
            "Invoice Acct Status"
    FROM ap_checks_all aca,
         ap_invoice_payments_all aipa,
         ap_invoices_all aia,
         ap_suppliers asa
   WHERE     aca.check_id = aipa.check_id
         AND aipa.invoice_id = aia.invoice_id
         AND aia.vendor_id = asa.vendor_id
--         AND aipa.ACCRUAL_POSTED_FLAG = 'N'
--         AND POSTED_FLAG = 'N'
         AND aia.INVOICE_ID=47597781
ORDER BY check_number;

Invoice with or without PO query in oracle apps

/* Formatted on 6/8/2015 3:08:16 PM (QP5 v5.240.12305.39446) */

SELECT PO_DISTRIBUTION_ID

  FROM po_distributions_all

 WHERE PO_DISTRIBUTION_ID IN

          (SELECT PO_DISTRIBUTION_ID

             FROM ap_invoice_distributions_all apid, ap_invoices_all api

            WHERE api.invoice_id = apid.invoice_id AND api.invoice_num = '19-NOV-13-083205100626214565USDE')