Blog / Category
AR
39 articles in this category
Resolve Apply Receipts Using AutoMatch ESS Performance Issues in Oracle Fusion
Resolving Long-Running Apply Receipts Using AutoMatch ESS Process in Oracle Fusion Introduction / Issue The Apply Receipts Using AutoMatch ESS process in Oracle…
Creating and Applying Receipts Through Lockbox Using FBDI
Step 1 – Create a Lockbox Navigate to Settings and Actions > Setup and Maintenance > Manage Lockbox, and define a new lockbox record. Step 2 – Confirm the Lockb…
Building an Hourly AR Invoice Line-Count Report by Batch Source
Introduction/ Issue: Our finance operations team had no easy way to see how AR invoices were being created across the day. When a customer complained about a de…
Query to give party and location details for a sales order
Introduction: This query will fetch you the party and location details for a sales order. The input parameter passed is ‘Sales order number’which will get proce…
AR Customer Collector Portfolio Report
Introduction: This blog has the SQL query that can be used to pull the data for the AR Customer Collector Portfolio Report Cause of the issue: Business wants to…
AR Customer Dunning Strategy Status Report
Introduction: This blog has the SQL query that can be used to pull the data for the AR Customer Dunning Strategy Status Report Cause of the issue: Business want…
Configuration to update the same customer tax registration number for multiple customers/sites
Introduction : This blog has the configuration update the same customer tax registration number for multiple customers/sites Why we need to do : As per the lega…
How to skip deletion of the entries from AR_STATEMENT_HEADERS and AR_STATEMENT_LINE_CLUSTERS
Introduction : This blog has the configuration to skip the deletion of the entries from AR_STATEMENT_HEADERS and AR_STATMENT_LINE_CL Why we need to do : While c…
Query to extract payment terms associated with a customer
oracle-ebs-customer-payment-terms SELECT customer_name, site_use_code, location, payment_term_id, name FROM apps.hz_cust_site_uses_all a1, hz_cust_acct_sites_al…
Query to get the accounting entries details by receipt.
SELECT amount_dr, amount_cr, acctd_amount_dr, acct_amount_cr, gcc.segment1 ||’.’ ||gcc.segment2 ||’.’ ||gcc.segment3 ||’.’ ||gcc.segment4 ||’.’ ||gcc.segment5 F…
Credit Memo Application Report
Introduction: This blog has the script to get the credit memo application details in Oracle Cloud application for Audit purpose. Cause of the issue: Auditor wan…
Query to display the customer name and customer address details
Introduction: This query is used to fetch the data of customer name and also the details like the account number, addresses, city, country, postal code and cust…
Query to get Accounting issue invoice details
select org_id, customer_trx_id, sum(tax) tax, sum(rev) rev, sum(rec) rec, sum(tax)+sum(rev) acutal_rec from ( select org_id, customer_trx_id, case when account_…
Query to get the AR Netting details
select * from (select fnar.batch_id, fnar.customer_trx_id, rct.bill_to_customer_id cust_acct_id, rct.bill_to_site_use_id cust_site_use_id, fnar.ar_txn_rank, nvl…
Customer Contact Details in EBS
Introduction: This blog has the SQL query that can be used to pull the data Organization contact details. Cause of the issue: Business wants to reach the…
Auto invoice get rejected Blog
auto-invoice-get-rejected-blog
Oracle R12 AR Lockbox Query
SELECT hou.name operating_unit, xep.name legal_entity, ala.lockbox_number “NUMBER”, absa.name batch_source, cebb.bank_name, –cebb.bank_number, –cebb.bank_branch…
Script to extract the AR partial credit memo’s with reference invoices
Introduction: This script will used to extract the AR partial credit memo’s with reference invoice details. select c.trx_number CM_Number, c.trx_date CM_DATE, (…
Scripts to get the customer bank details in fusion.
Introduction: This scripts will used to extract the customer bank information in fusion. SELECT IBY_EBA.*,hp.party_name, CA.ACCOUNT_NUMBER, cbb.bank_bran…
Script to extract the accounting rule and source wise AR invoice counts
Introduction: This script will extract the accounting rule and trx source wise invoice counts. SELECT C.NAME RULE_NAME, D.NAME SOURCE_NAME, count(A.TRX_NUMBER),…
AR Accounted Receipt outbound from Cloud
Introduction: This script will used to extract the AR accounted receipts details. select ‘HEADERS’ “FILE_IDENTIFIER” ,DECODE(ou.name,’IM US BU’,’IM US OU’,’IM C…
AR Adjustments Outbound from EBS
Introduction: This procedure is used to extract the adjustments from EBS for Cloud Import. create or replace PROCEDURE XXXX_AR_ADJ_EXTRACT_CLOUD ( errbuf OUT VA…
Steps to be considered when we get ‘Please Correct Revenue Account Assignment’ in AR Autoinvoice import program?
Introduction: This is used to fix the invalid revenue account for AR invoice Auto accounting is used to determine the accounts when the distribution table is no…
Customer Address change script
/******************************************************************************** *PURPOSE: To Customer Locations information from back-end * *AUTHOR: Shailende…
Third party Registration Validation
create or replace procedure xx_Thirdpart_reg_validation(P_CUSTOMER varchar2) as LV_ERR_MSG VARCHAR2 (4000); LV_STATUS_FLAG VARCHAR2 (1); LV_CUSTOMER_ID NUMBER;…
Ship to Site Address Validation
create or replace procedure xx_shipto_site_validation(P_CUSTOMER varchar2) as LV_ERR_MSG VARCHAR2 (4000); LV_STATUS_FLAG VARCHAR2 (1); LV_CUSTOMER_ID NUMBER; LV…
Bill to Site Address Validation
create or replace procedure xx_billto_site_validation(P_CUSTOMER varchar2) as LV_ERR_MSG VARCHAR2 (4000); LV_STATUS_FLAG VARCHAR2 (1); LV_CUSTOMER_ID NUMBER; LV…
CSB related table Info and related Table Status & Queries
Some of the Important Tables related to CSB: iby_trxn_summaries_all – Holds the information related the CSB current batch ( DEBIT_AUTHORIZATION_ID, MBATCHID (Ho…
Possibilities of having Different Receipt Maturity dates in a Single batch
When the Automatic Receipt master program is planned to execute on “28-FEB-20” and when there are a couple of invoices that were created with Due date as “24-FE…
Automatic Receipts and Direct debit related programs
Automatic Receipts and Direct Debit feature: Instead of manually entering receipts, you can use the Receivables automatic receipts feature to automatically gene…
API to Update Customer Name
SET SERVEROUTPUT ON; SET DEFINE OFF; DECLARE l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE; l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE; –l_party_obj_ve…
Multiple Customers having same bank account
customer-bank-account This is to know how to add multiple customers which share the same bank account details. user cannot add the bank details directly, using…
AR Invoice Image Upload Using SOAP Request – Oracle Fusion
This blog contains sample payload to upload an image file in AR invoice attachments in Oracle Fusion Applications. During fusion implementation, we might come a…
Query to find the HSN Summary
Introduction: This report will display the HSN summary details. SELECT ‘HSN Summary’ inv_type ,hsn_data.supp_gstin s_gstin ,hsn_data.period inv_period ,hsn_data…
Dynamically Displaying XSL Element
<?xml version=”1.0″ encoding=”utf-8″?> <!– +======================================================================+ –> <!– | Copyright (c) 2005, 2014 Oracle and…
To Create Business Purpose as Statements for Customer Site
To Create Business Purpose as Statements for Customer Site / SET SERVEROUTPUT ON; DECLARE p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;…
Oracle Apps(EBS) – AR Receipt Register Query with Bank statement Header and Line Details
Below query is useful when you required Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off) SELE…
Electronic File Transfer in AR – Brazil – Technical Information
Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to elec…
Brazil Electronic File Transfer in Receivables
Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to elec…