Oracle Apps Purchase Order Tax Query

The blog provides the Oracle Apps R12 Purchase Order (PO) SQL Query to get Tax details and another Oracle Apps R12 Purchase Order (PO) SQL Query to get Tax details mapped with Purchase Order and the Associated Item with price and shipment details.

Oracle Apps R12 Purchase Order Query to get Tax Columns

SELECT   PO_TAX_QUERY.ORDER_NO1,
         PO_TAX_QUERY.TAX_RATE, SUM (NVL (PO_TAX_QUERY.TAX_AMOUNT, 0)) TAX_AMOUNT,
         PO_TAX_QUERY.TAX_NAME, PO_TAX_QUERY.TAX_LINE_NO
    FROM (SELECT   H.SEGMENT1 ORDER_NO1, JAI_PO_TAXES_TBL.LINE_LOCATION_ID,
                   JAI_PO_TAXES_TBL.TAX_RATE TAX_RATE,
                   JAI_PO_TAXES_TBL.TAX_AMOUNT,
                   RTRIM (NVL (JAI_CMN_TAXES_ALL_TBL.TAX_DESCR, NULL)) TAX_NAME,
                   JAI_PO_TAXES_TBL.TAX_LINE_NO
              FROM PO.PO_HEADERS_ALL H,
                   PO_LINES_ALL PO_LINES_ALL_TBL,
                   JAI_PO_TAXES JAI_PO_TAXES_TBL,
                   JAI_CMN_TAXES_ALL JAI_CMN_TAXES_ALL_TBL
          WHERE    JAI_PO_TAXES_TBL.TAX_ID = JAI_CMN_TAXES_ALL_TBL.TAX_ID(+)
               AND JAI_CMN_TAXES_ALL_TBL.END_DATE IS NULL
               AND PO_LINES_ALL_TBL.PO_LINE_ID = JAI_PO_TAXES_TBL.PO_LINE_ID(+)
               AND PO_LINES_ALL_TBL.PO_HEADER_ID = JAI_PO_TAXES_TBL.PO_HEADER_ID(+)
               AND H.PO_HEADER_ID = PO_LINES_ALL_TBL.PO_HEADER_ID(+)
               AND JAI_PO_TAXES_TBL.TAX_AMOUNT < > 0
               AND H.TYPE_LOOKUP_CODE = 'STANDARD'
               AND H.SEGMENT1 = :PO_ORDER
          ORDER BY JAI_PO_TAXES_TBL.TAX_LINE_NO) PO_TAX_QUERY
GROUP BY PO_TAX_QUERY.ORDER_NO1,
         PO_TAX_QUERY.TAX_RATE, 
	 PO_TAX_QUERY.TAX_NAME, 
	 PO_TAX_QUERY.TAX_LINE_NO
ORDER BY TAX_LINE_NO

Oracle Apps Purchase Order Query to PO, Tax and Item Details

SELECT 	PO_HEADERS_ALL_TBL.SEGMENT1, 
		PO_LINES_ALL_TBL.LINE_NUM,
		PO_LINE_LOC_ALL_TBL.SHIPMENT_NUM,
		PO_HEADERS_ALL_TBL.COMMENTS,
		MSI.SEGMENT1 "ITEM CODE",
		PO_LINES_ALL_TBL.ITEM_DESCRIPTION,
		PO_LINES_ALL_TBL.UNIT_MEAS_LOOKUP_CODE "UOM",
		PO_LINES_ALL_TBL.BASE_UNIT_PRICE, PO_LINES_ALL_TBL.UNIT_PRICE,      
                PO_LINES_ALL_TBL.QUANTITY,
		(PO_LINES_ALL_TBL.UNIT_PRICE * PO_LINES_ALL_TBL.QUANTITY) "LINE AMOUNT",
		ZL_DET_FACTORS_TBL.INPUT_TAX_CLASSIFICATION_CODE, 
		ZL_DET_FACTORS_TBL.USER_DEFINED_FISC_CLASS,
		ZX_LINES_TBL.TAX_RATE,
		ZX_LINES_TBL.TAXABLE_AMT
FROM 	        ZX_LINES_DET_FACTORS ZL_DET_FACTORS_TBL,
		ZX_LINES ZX_LINES_TBL,
		PO_HEADERS_ALL PO_HEADERS_ALL_TBL,
		PO_LINES_ALL PO_LINES_ALL_TBL,
		PO_LINE_LOCATIONS_ALL PO_LINE_LOC_ALL_TBL,
		MTL_SYSTEM_ITEMS_B MSI,
		PO_DISTRIBUTIONS_ALL PDA
WHERE 	        1=1
                AND PO_HEADERS_ALL_TBL.PO_HEADER_ID = PO_LINES_ALL_TBL.PO_HEADER_ID
		AND PO_LINES_ALL_TBL.PO_LINE_ID = PO_LINE_LOC_ALL_TBL.PO_LINE_ID
		AND PDA.PO_HEADER_ID = PO_HEADERS_ALL_TBL.PO_HEADER_ID
		AND PDA.PO_LINE_ID = PO_LINES_ALL_TBL.PO_LINE_ID
		AND PDA.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
		AND ZL_DET_FACTORS_TBL.TRX_ID = ZX_LINES_TBL.TRX_ID
		AND ZX_LINES_TBL.TRX_ID = PO_LINE_LOC_ALL_TBL.PO_HEADER_ID
		AND ZL_DET_FACTORS_TBL.ENTITY_CODE = 'PURCHASE_ORDER'
		AND PO_HEADERS_ALL_TBL.SEGMENT1 = :P_PO_NUMBER

Oracle Apps AP Invoice Supplier Tax Registration Number Query

The blog provides the Oracle Apps Query to get AP Invoice Supplier Tax Registration Number, query to get supplier tax details in oracle apps r12

SELECT 
	  AIA.INVOICE_NUM,
          AP_INVOICES_ALL_TBL.INVOICE_CURRENCY_CODE,
          AIL.PERIOD_NAME,
	  AP_INVOICES_ALL_TBL.INVOICE_ID,
          PO_VENDORS_TBL.VENDOR_NAME,
          PO_VENDORS_TBL.SEGMENT1 VENDOR_NUM,
          PO_VENDOR_SITES_ALL_TBL.VENDOR_SITE_CODE,
          AP_INVOICES_ALL_TBL.DESCRIPTION,
          AP_INVOICES_ALL_TBL.GL_DATE,
          AP_INVOICES_ALL_TBL.INVOICE_DATE,   
	 ROUND(  (ZLV.UNROUNDED_TAXABLE_AMT  *NVL(ZLV.CURRENCY_CONVERSION_RATE,1) ),2) AMOUNT,
          1 QUANTITY,
          ZLV.TAX_RATE_CODE,
          AP_INVOICES_PKG.GET_POSTING_STATUS (AP_INVOICES_ALL_TBL.INVOICE_ID) POSTING_FLAG,
         (SELECT VAT_REGISTRATION_NUM FROM AP_SUPPLIERS WHERE VENDOR_ID = PO_VENDORS_TBL.VENDOR_ID) SUPPLIER_TRN,  
            CASE
		WHEN AP_INVOICES_ALL_TBL.INVOICE_CURRENCY_CODE = 'AED' THEN ROUND (ZLV.TAX_AMT, 2)
		ELSE ROUND ( (ZLV.TAX_AMT) * AP_INVOICES_ALL_TBL.EXCHANGE_RATE, 2)
	   END
             TAX_AMT
     FROM AP_INVOICES_ALL AP_INVOICES_ALL_TBL,
          AP_INVOICE_LINES_ALL AIL,
          PO_VENDORS PO_VENDORS_TBL,
          PO_VENDOR_SITES_ALL PO_VENDOR_SITES_ALL_TBL,
          ZX_LINES_V ZLV
    WHERE AP_INVOICES_ALL_TBL.INVOICE_ID = AIL.INVOICE_ID
          AND AIL.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX')
          AND AP_INVOICES_ALL_TBL.INVOICE_TYPE_LOOKUP_CODE NOT IN
                 ('CREDIT', 'DEBIT' )
          AND AP_INVOICES_ALL_TBL.VENDOR_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_ID
          AND PO_VENDORS_TBL.VENDOR_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_ID
          AND AP_INVOICES_ALL_TBL.VENDOR_SITE_ID = PO_VENDOR_SITES_ALL_TBL.VENDOR_SITE_ID
          AND AP_INVOICES_ALL_TBL.INVOICE_ID = ZLV.TRX_ID
          AND ZLV.TAX_RATE_CODE = '<TAX-RATE-CODE>'
          AND ZLV.SELF_ASSESSED_FLAG <> 'Y'
          AND ZLV.CANCEL_FLAG = 'N'
          AND AP_INVOICES_PKG.GET_POSTING_STATUS (AP_INVOICES_ALL_TBL.INVOICE_ID) <> 'N'
          AND AIL.CANCELLED_FLAG = 'N'
          AND ZLV.TAX_AMT_INCLUDED_FLAG='N'
          AND NVL(PO_VENDOR_SITES_ALL_TBL.COUNTRY,'<COUNTRY-CODE>')='<COUNTRY-CODE>'
          AND NOT EXISTS
                 (SELECT 'Y'
                    FROM AP_INVOICE_PAYMENTS_ALL AIP, AP_CHECKS_ALL AC
                   WHERE AIP.INVOICE_ID = AP_INVOICES_ALL_TBL.INVOICE_ID
                         AND AIP.CHECK_ID = AC.CHECK_ID
                         AND (AIP.BANK_ACCOUNT_NUM = '<BANK_ACC_NUM>'
                              OR AC.BANK_ACCOUNT_NAME =
                                    '<BANK-ACC-NAME>'))
          AND AIL.LINE_NUMBER = NVL (ZLV.TRX_LINE_NUMBER, AIL.LINE_NUMBER)
          AND AP_INVOICES_ALL_TBL.ORG_ID=:P_ORG_ID
          AND TRUNC (AP_INVOICES_ALL_TBL.GL_DATE) BETWEEN NVL ('01-'||:P_FROM_DATE,
                                             TRUNC (AP_INVOICES_ALL_TBL.GL_DATE))
                                      AND NVL (LAST_DAY('01-'||:P_TO_DATE),
                                             TRUNC (AP_INVOICES_ALL_TBL.GL_DATE))
		ORDER BY AP_INVOICES_ALL_TBL.GL_DATE;

Oracle Apps R12 Query to get Tax Codes in PO

The blog provides the Oracle Apps R12 useful SQL Queries to get Tax_classification_code in Purchase Order (PO) , Query to get Tax Codes and Tax Rates defined for EBTAX.

Oracle Apps R12 Query for Tax Codes in Purchase Order (PO)

SELECT PO_HDRS_ALL_TBL.SEGMENT1, PO_LINES_ALL_TBL.LINE_NUM,POLL.SHIPMENT_NUM,
ZL_DET_FACTORS_TBL.INPUT_TAX_CLASSIFICATION_CODE, 
ZL_DET_FACTORS_TBL.USER_DEFINED_FISC_CLASS
FROM ZX_LINES_DET_FACTORS ZL_DET_FACTORS_TBL,
ZX_LINES ZXL,
PO_HEADERS_ALL PO_HDRS_ALL_TBL,
PO_LINES_ALL PO_LINES_ALL_TBL,
PO_LINE_LOCATIONS_ALL POLL
WHERE PO_HDRS_ALL_TBL.PO_HEADER_ID = PO_LINES_ALL_TBL.PO_HEADER_ID AND
PO_LINES_ALL_TBL.PO_LINE_ID = POLL.PO_LINE_ID AND
ZL_DET_FACTORS_TBL.TRX_ID = ZXL.TRX_ID AND
ZXL.TRX_ID = POLL.PO_HEADER_ID AND
ZL_DET_FACTORS_TBL.ENTITY_CODE = 'PURCHASE_ORDER' AND
PO_HDRS_ALL_TBL.SEGMENT1 = '<PONUMBER>&'

Oracle Apps R12 Query to get Tax Code and Tax Rate for EBTAX

SELECT DISTINCT 
ZX_TAXES_B_tbl.TAX_ID,
ZX_TAXES_B_tbl.TAX,
ZX_TAXES_B_tbl.TAX_FULL_NAME,
ZX_TAXES_B_tbl.TAX_REGIME_CODE,
ZX_TAXES_B_tbl.TAX_TYPE_CODE,
ZX_RATES_B_TBL.TAX_RATE_CODE,
ZX_RATES_B_TBL.TAX_STATUS_CODE,
ZX_RATES_B_TBL.RATE_TYPE_CODE,
ZX_RATES_B_TBL.PERCENTAGE_RATE,
ZX_RATES_B_TBL.DESCRIPTION,
ZX_RATES_TL_TBL.TAX_RATE_NAME
FROM
ZX_TAXES_B ZX_TAXES_B_tbl,
ZX_TAXES_TL ZX_TAXES_TL_TBL,
ZX_RATES_B ZX_RATES_B_TBL,
ZX_RATES_TL ZX_RATES_TL_TBL,
WHERE 1=1
AND ZX_TAXES_B_tbl.TAX_ID =ZX_TAXES_TL_TBL.TAX_ID
AND ZX_RATES_B_TBL.TAX = ZX_TAXES_B_tbl.TAX
AND ZX_RATES_B_TBL.TAX_RATE_ID = ZX_RATES_TL_TBL.TAX_RATE_ID
ORDER BY
ZX_TAXES_B_tbl.TAX,
ZX_RATES_B_TBL.TAX_RATE_CODE

Cannot Query PO Tax Lines in Additional Tax Page

In Oracle Apps R12 , when Purchase Order Tax Lines are searched in the Additional Tax Information Page, then instead of returning single PO Tax Line , it’s returning all Tax Lines.

The above mentioned issue is identified as an Bug 21480203 – CANNOT QUERY TAX INFORMATION IN ADDITION TAX INFO PAGE

Steps to perform PO Tax Line Search

Query the Purchase Order in the Purchase Order Form
Navigate to Manage tax -> Additional Tax Information
Click on Search Function
The Additional Tax Information Page returns all lines instead of 1 line

Resolution Steps

Download the Patch 21480203:R12.ZX.B from the Oracle Support
Request for Patch Password from Oracle Support
Take the Backup of the Environment where patch is to be applied
Run the Patch
Execute the command strings -a $XX_TOP/filename.class |grep ‘$Header’
The below given file versions to be updated

MaintainDetFactorsAMImpl.java 120.19.12010000.7
UpdateDetFactorsCO.java 120.21.12010000.5

Retest the issue

Oracle EBS R12 SQL to get AP and AGIS Invoices

The below given SQL Query will retrieve the set of data for AP and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined

SELECT AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
AII.ORG_ID AP_ORG_ID,
RCTA.ATTRIBUTE9,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID AR_ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID
FROM AP_INVOICES_INTERFACE AII,
FUN_TRX_HEADERS FTH,
RA_CUSTOMER_TRX_ALL RCTA,
FUN_SUPPLIER_MAPS FSM,
FUN_TRADE_RELATIONS FTR,
RA_BATCH_SOURCES_ALL RBS,
RA_TERMS RT,
FUN_DIST_LINES FDL,
AP_INVOICE_LINES_INTERFACE AILI
WHERE     FTH.TRX_ID = AII.GROUP_ID
AND RCTA.TRX_NUMBER = AII.INVOICE_NUM
AND aii.SOURCE = :<enter_source_name>
AND FSM.RELATION_ID = FTR.RELATION_ID
AND RBS.NAME = 'Global Intercompany'
AND RCTA.BATCH_SOURCE_ID = RBS.BATCH_SOURCE_ID
AND RCTA.ORG_ID = RBS.ORG_ID
AND RCTA.TERM_ID = RT.TERM_ID(+)
AND FSM.VENDOR_ID = AII.VENDOR_ID
AND FSM.VENDOR_SITE_ID = AII.VENDOR_SITE_ID
AND RCTA.ORG_ID = FTR.TRANSACTION_ORG_ID
AND AII.ORG_ID = FTR.TP_ORG_ID
AND AII.WORKFLOW_FLAG IS NULL
AND aii.org_id = :<enter_org_id>
AND AII.INVOICE_ID = AILI.INVOICE_ID
AND FDL.DIST_NUMBER = AILI.LINE_NUMBER
AND AII.GROUP_ID = FTH.TRX_ID
AND FTH.TRX_ID = FDL.TRX_ID
AND FDL.DIST_TYPE_FLAG = 'L'
AND FDL.PARTY_TYPE_FLAG = 'R'
AND FDL.AUTO_GENERATE_FLAG = 'N'
GROUP BY AII.INVOICE_ID,
AII.GROUP_ID,
AII.INVOICE_NUM,
AII.INVOICE_AMOUNT,
FTH.ATTRIBUTE1,
AII.ORG_ID,
RCTA.ATTRIBUTE9,
FTH.ATTRIBUTE2,
FTH.ATTRIBUTE12,
FTR.TRANSACTION_LE_ID,
FTR.TP_LE_ID,
FTH.TRX_ID,
RCTA.ORG_ID,
RT.NAME,
FTH.BATCH_ID,
AII.SOURCE,
FTH.TO_LEDGER_ID,
AII.INVOICE_CURRENCY_CODE,
AII.GL_DATE
ORDER BY AII.ORG_ID, AII.INVOICE_ID;

Account Payables differences in EBS R11i and EBS R12

Please find below the major differences in 11i and R12 Account Payables

Type Description Available in R12 Available in 11i

Supplier	Supplier Details as in HTML Form	Yes	No
Supplier	Enable with MOAC Access	Yes	No
Supplier	Supplier as a TCA Party	Yes	No
Supplier	Supplier Setup Definition in AP .	No	Yes
Supplier	Usage of Supplier Site as a TCA Party Site for each distinct Address	Yes	No
Supplier	Supplier Contact copy – Each Supplier Site	No	Yes

Type Description Available in R12 Available in 11i

Payment	Payment Process Profile	Yes	No
Payment	Operating Unit at Header	Yes	No
Payment	Payment manager	Yes	Payment Batch

Type Description Available in R12 Available in 11i

Payable Option	Accounting,Method,Transfer to GL	No	Yes
Payable system set up	Enablement of Supplier numbering	Yes	At financial option

Type Description Available in R12 Available in 11i

Invoice	Operating Unit at Header	Yes	No
Invoice	Invoice Lines as a new additional line added in Invoice screen.	Yes	No
Invoice	All distribution option	Yes	No
Invoice	DFF at Header	Yes	No
Invoice	DFF at Distribution	Yes	At Invoice Header
Invoice	Allocate freight and special charges are define to the lines on the invoice	Yes	No
Invoice	Allocation of freight and special charges are define at the distribution level only	No	Yes
Invoice	Match Action come to Header	Header level	Near to Match
Invoice	Tax Details Option	Yes	No
Invoice	Tax Calculation Option	Yes	No
Invoice	Create Accounting	Draft,Final,Final Post	only
Invoice	Multi Organization Access Control (MOAC)	Yes	No
Invoice	Sub ledger Accounting (SLA)	Yes	No

Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

The blog provides the Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

SELECT AIA.ORG_ID,AIA.INVOICE_ID, AIA.INVOICE_AMOUNT, AIA.GL_DATE, AIA.INVOICE_DATE, NVL(SUM(AIDA.AMOUNT),0) AMOUNT
FROM AP_INVOICES_ALL AIA,AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE 1=1
--AND AIA.org_id = <ORG_ID_VALUE>
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE NOT IN ('PREPAY')
AND AIA.CANCELLED_DATE IS NOT NULL
AND ROWNUM <100
GROUP BY AIA.ORG_ID,AIA.INVOICE_ID, AIA.INVOICE_AMOUNT ,AIA.GL_DATE, AIA.INVOICE_DATE
HAVING (AIA.INVOICE_AMOUNT <> NVL(SUM(AIDA.AMOUNT),0))
ORDER BY AIA.INVOICE_ID ASC

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE  AIDA.INVOICE_ID = <INVOICE_ID>

Oracle EBS R12 SQL to get AP invoices posted to GL

The blog provides the Oracle EBS R12 SQL to get AP invoices posted to General Ledger (GL). The Account Payables (AP) Invoices should be validated and Posted for AP reconciliation during month end period closure. Few are the validations given below which are being used to ensure that the Invoice has been accounted with approved Status and Posting Status.

Oracle EBS R12 AP invoice Status

There are 4 invoice status as given below
‘S’ – Selected
‘P’ – Partial
‘N’ – Unposted
‘Y’ – Posted

Check and ensure that the Invoice Id entry should be in the table – AP_INVOICE_DISTRIBUTIONS_ALL
Check and ensure that the Invoice Id entry should be in the table – AP_SELF_ASSESSED_TAX_DIST_ALL
Check if DIST VAR hold is placed on this invoice. DIST VAR Hold could be placed when no distributions exist and the invoice status should be NEEDS REAPPROVAL
Check and ensure that there is NO Hold applied on the Invoice
Check if the Invoice is NOT Cancelled
Check Table – AP_INVOICE_DISTRIBUTIONS_ALL for the Invoice ID match_status_flag IS NOT NULL and count should be 0

SQL to get AP Invoices Validated but Not Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='N

SQL Query to get AP Invoices Validated & Posted to General Ledger (GL)

SELECT INVOICE_NUM
FROM AP_INVOICES_ALL
WHERE AP_INVOICES_PKG.GET_APPROVAL_STATUS
(INVOICE_ID,
INVOICE_AMOUNT,
PAYMENT_STATUS_FLAG,
INVOICE_TYPE_LOOKUP_CODE
) ='APPROVED'
AND AP_INVOICES_PKG.GET_POSTING_STATUS(INVOICE_ID)='P'

Oracle EBS R12 SQL to get AP Invoice Status

The blog provides the Oracle EBS R12 SQL to get AP Invoice Status. The AP Invoices are required to be validated , processed and closed.

The below given SQL Query can be used to retrieve AP Invoice data for different Invoice Status – ‘ APPROVED ‘, ‘ UNAPPROVED ‘, ‘ CANCELLED ‘ in the Account Payables.

SELECT DISTINCT
AIA.INVOICE_ID           " Invoice Id",
AIA.INVOICE_NUM          " Invoice Number" ,
AIA.INVOICE_DATE         " Invoice Date",
AIA.CREATION_DATE        " Invoice Creation Date",
AIA.INVOICE_AMOUNT       " Invoice Amount" ,
ALC4.LOOKUP_TYPE         " Invoice Lookup Type",
ALC4.LOOKUP_CODE         " Invoice Lookup Code",
AIA.WFAPPROVAL_STATUS    " Approval Status ",
AIA.GL_DATE              " GL Date ",
AIA.ORG_ID               " ORG ID ",
AIA.SOURCE               " Invoice Source"
FROM
AP_INVOICES_ALL AIA,
AP_LOOKUP_CODES ALC4
WHERE
ALC4.LOOKUP_TYPE = ('NLS TRANSLATION')
AND ALC4.LOOKUP_CODE IN('APPROVED','UNAPPROVED','CANCELLED')
AND AIA.INVOICE_ID IN (478094)
ORDER BY AIA.CREATION_DATE DESC

Oracle EBS R12 AR SQL to get Customer Bank details

The below given Bank Query retrieves data for AR Cash Receipt Number

SELECT   APBB.BANK_NAME BANK_ACCOUNT_NAME ,
APBAA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,
APBAA.BANK_ACCOUNT_ID,
HP.PARTY_NAME CUSTOMER_NAME,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ACR.PAY_FROM_CUSTOMER CUST_ACCOUNT_ID,
ACR.RECEIPT_NUMBER RECEIPT_NUMBER,
ACR.CURRENCY_CODE,
ACR.SET_OF_BOOKS_ID,
ACR.TYPE,
ACR.DEPOSIT_DATE DEPOSIT_DATE,
ACR.RECEIPT_DATE RECEIPT_DATE,
ACR.AMOUNT RECEIPT_AMOUNT ,
ARM.NAME DOCUMENT_TYPE
FROM
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS_ALL HCA,
AR_CASH_RECEIPTS_ALL ACR,
AR_RECEIPT_METHODS ARM,
AR_RECEIPT_CLASSES ARC,
AP_BANK_ACCOUNTS_ALL APBAA,
AP_BANK_BRANCHES APBB
WHERE       1 = 1
AND ACR.CONFIRMED_FLAG = 'Y'
AND ACR.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+)
AND HP.PARTY_ID(+) = HCA.PARTY_ID
AND ACR.REMITTANCE_BANK_ACCOUNT_ID = APBAA.BANK_ACCOUNT_ID(+)
AND APBB.BANK_BRANCH_ID(+) = APBAA.BANK_BRANCH_ID
AND ACR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID
--AND APBAA.Bank_Account_Id = NVL (:p_bank_account_id, APBAA.Bank_Account_Id)
AND ACR.RECEIPT_NUMBER = '538AR201507030'
--AND TRUNC (acr.receipt_date) >= '31-MAY-2015'
-- AND TRUNC (acr.receipt_date) <= '30-JUN-2015'
ORDER BY ACR.RECEIPT_DATE DESC

oracleappshelp.com

Category: AP- Account Payables

Oracle Apps Purchase Order Tax Query

Oracle Apps R12 Purchase Order Query to get Tax Columns

Oracle Apps Purchase Order Query to PO, Tax and Item Details

Oracle Apps AP Invoice Supplier Tax Registration Number Query

Oracle Apps R12 Query to get Tax Codes in PO

Oracle Apps R12 Query for Tax Codes in Purchase Order (PO)

Oracle Apps R12 Query to get Tax Code and Tax Rate for EBTAX

Cannot Query PO Tax Lines in Additional Tax Page

Steps to perform PO Tax Line Search

Resolution Steps

Oracle EBS R12 SQL to get AP and AGIS Invoices

Account Payables differences in EBS R11i and EBS R12

Oracle EBS R12 SQL get AP cancelled invoices with distribution lines

Oracle EBS R12 SQL to get AP invoices posted to GL

Oracle EBS R12 SQL to get AP Invoice Status

Oracle EBS R12 AR SQL to get Customer Bank details