Friday, June 18, 2021

Query to get Count of Payable Invoices by Year and Status (Paid, Unpaid or Cancelled)


We were purging payable invoices and i wanted to know how many invoices are still not paid in a given year. So i used this simple query.

Wednesday, January 29, 2020

Late Charges Calculation and Set Up: A Guide for Oracle Receivables Users (Doc ID 1269062.1)

Link to Setup Guide

Monday, June 10, 2019

List of Checks/EFT Payments where invoice or credit memo is removed


We wanted to find out what are the checks/EFT payments from where an invoice or a credit memo has been removed. So I wrote this query. This query can give me count of invoices/credit memos along with total amount that have been paid and removed in a payment document.

Thursday, April 11, 2019

Date Formats in Oracle SQL

In one of the table in Oracle application, we were storing date value in VARCHAR2 column (ATTRIBUTE1-15). I was saving date in YYYYMMDD format. Oracle Value set FND_STANDARD_DATE save set in YYYY/MM/DD HH24:MI:SS format.
My custom program did not fail when converted to DATE using to_date function. So I tired following queries and all of them completed without any error. It seems to_date function does not care about separator (i.e. / Or - or NULL).



select to_date('20190721' , 'YYYY/MM/DD HH24:MI:SS') from dual;
select to_date('201907/21', 'YYYY/MM/DD HH24:MI:SS') from dual;
select to_date('04JUL19' , 'DD-MON-YY HH24:MI:SS' ) from dual;
select to_date('04/JUL/19', 'DD-MON-YY HH24:MI:SS' ) from dual;
select to_date('04|JUL-19', 'DD-MON-YY HH24:MI:SS' ) from dual;

Wednesday, December 12, 2018

Oracle API to Delete Projects (pa_project_pub.delete_project)


I was creating oracle projects from a text file. In order to use same text file for multiple test cases, I was deleting project using Oracle Projects Form. It was little inconvenient. So I wrote this simple script to delete project. This script can be modified to select projects that need to be deleted.

Thursday, August 09, 2018

How to Debug Account Generator


I needed to debug workflow PAAPINVW (Project Supplier Invoice Account Generation). As per oracle documentation, it can debugged by setting profile option "Account Generator:Run in Debug Mode" to "Yes". But I could not find where debug information is being saved. So I opened the package "fnd_flex_workflow_apis" and found that debug information is being saved in a file named fdfsrvdbg.log. File is created in first directory specified by parameter 'utl_file_dir'. The file fdfsrvdbg.log is opened in APPEND mode. So in order to view only your debug information, you may delete all lines from file or move this file other dir before you start your debug.

Wednesday, August 08, 2018

PAAPINVW - Steps to Customize Project Supplier Invoice Account Generator Workflow (Doc ID 1344854.1)

This is a good oracle document describing how to customize Project Supplier Invoice Account Generation" Workflow PAAPINVW - Steps to Customize Project Supplier Invoice Account Generator Workflow (Doc ID 1344854.1)


I had to read it multiple times to get good understanding of steps needed to modify it.

Oracle Projects Implementation Guide (Release 12.2 Part Number E49019-07 AutoAccounting, the Account Generator, and Subledger Accounting) too was useful.

For account generator workflow, setups created using RULES and ASSIGN RULES forms (Responsibility -> Project costing Super User, Navigation -> Setup -> AutoAccounting) are probably not applicable to "Project Supplier Invoice Account Generator Workflow". The workflow uses only LOOKUP SETS (Same navigation as RULES, ASSIGN RULES).

If you create a new process for generating default account for supplier invoices then you need to update process name for item type "Project Supplier Invoice Account Generation". It can be done as given below.

  1. Login using System Administrator Responsibility
  2. Navigate to Application -> Flexfield -> Key -> Accounts
  3. Query your "Accounting Structure"
  4. Naviate to "Process" block
  5. Query item type "Project Supplier Invoice Account Generation"
  6. Enter New Process created for generating account for supplier invoices.

Tuesday, July 17, 2018

Oracle Project API "PA_PROJECT_PUB" Error: "Your current responsibility does not have access to this function."

Will it hurt Oracle developers to put Function Id too in the error message? How do i know which function i don't have access?


So queried fnd_new_message to get message code (select * from fnd_new_messages where message_text like 'Your current resp%';).
Message code was "PA_FUNCTION_SECURITY_ENFORCED".


Searched source code of PA_PROJECT_PUB.create_project for the message code. it was function "PA_PM_CREATE_PROJECT" that my responsibility did not have access to.


I could create projects using API via some other custom responsibility. So I used THIS QUERY replaced "%" for user_menu_name with menu_name for responsibility that does not give error. I found the following sub menu has all the functions that may be needed for creating/updating projects/task using projects APIs


Open Integration Toolkit Funtions Menu

You may have noticed that Function is spelled incorrectly. I think it has come from Oracle itself.


Added Open Integration Toolkit Funtions Menu as sub-menu to custom responsibility's menu and I was able to create project using API.




Monday, May 21, 2018

Project API Error "Project: '&PROJECT'Organization is invalid." (PA_INVALID_ORG_AMG)


I was trying to create a project using "pa_project_pub.create_project" and I got the above error. I searched for message code for the above message and found following message code


PA_INVALID_ORG_AMG


Then I search dba_source to see what validation package is generating the above message. I could not find any oracle code that was using this message. I knew that oracle project changes message code for some unknown reason. so I searched for PA_INVALID_ORG and fortunately I found few instances where message was 'PA_INVALID_ORG'.


select * from dba_source where text like '%PA_INVALID_ORG%'; --'%PA_INVALID_ORG_AMG%';



So I looked at pa_project_pub. API was validating carrying_out_organization_id using pa_project_pvt.check_valid_org. Carrying out organization had become invalid due to some setup changes. Validation performed is to check existence in table pa_all_organization and inactive_date equal to null.


Tuesday, December 05, 2017

Parameters of a Scheduled Request Set


At my site, few users have scheduled a request set with different parameters. If support staff needs to hold a particular instance of that request set then there is no ways to identify that request. Oracle does not provide a mechanism to view parameters to a scheduled request set. Many times support staff guess based on "Requested By" of request set. So I wrote following query that can give me parameters to all scheduled instances of a request set.

In the query below, I have provision for 20 parameters only. there could be up to 100 parameters. If there are more parameters to any concurrent program of a request set, then you need to modify DECODE and add sql statements for remaining parameters.

Tuesday, November 07, 2017

TCA Module: Table HZ_ORGANIZATION_PROFILES has multiple ACTIVE records for a single party

In our test instance (and in production instance too) we found that there few many cases where a party has multiple active records in table hz_organization_profiles. When party information is updated via Customer interface or UI, the existing record in hz_organization_profiles is end dated and new record is created. (If a party is created and updated on same day then new record in hz_organization_profile is not created and existing record is simply updated.). I was not able to simulate this issue. When I searched oracle support, I found following note

Trading Community: GDF: Ensure HZ_Organization_Profiles Have Appropriate Effective_End_Date Values and Contain No Over-Lapping Date Ranges (Doc ID 2260595.1)

This note provides a data fix script but does not offer permanent solution. I think this table is not used much in Oracle application that is why this bug is not fixed in last many years. I have seen this issue sometime in year 2012.

Effective end date column of this table too is not being updated properly. When a new record for a party is added to this table hz_organization_profiles, existing record is updated with effective_end_date trunc(sysdate - 1) and new record have effective_start_date as sysdate. For example if you update an organization at 11/06/2017 3:43pm then old record in hz_organization_profiles will have effective end date as 11/05/2017 00:00:00 and new record will have effective start date as 11/06/2017 15:43:00. So there was no active record for organization between 11/05/2017 00:00:00 and 11/06/2017 15:42:59. If Oracle wants to use time component then use in both effective start and end dates. This pains me to see how careless is Oracle AR application development.

Monday, November 06, 2017

Receivables Transactions WORKBENCH

Recently I found following note at support.oracle.com

Receivables Transactions WORKBENCH(Doc ID 1458368.2)

This document provides good information setups, defaulting. document sequence numbering etc. This is very good reference for receivables module. I hope oracle keeps this document updated.

The above document is not available anymore. I found this document. I don't remember if it is same or different but provides good information.

Transaction Workbench: FAQ (Doc ID 220026.1)

Monday, April 24, 2017

Knight's Tour


My son, currently in 9th grade, has been pestering me to learn Python or Java or something else instead of reading Facebook all the time. So finally I gave in and told him to setup Python on one of the old unused desktop. He installed Ubuntu, Python, some editor called "ATOM" and some linter for Python program. Python seems to be very easy. There are lots of good material available. Whatever question I had, I got answer from stack overflow. As first program in Python I wanted to try Knight's tour. It took some time but I could complete it. Here is the code for Knight's tour.

Wednesday, February 15, 2017

Example of Receipt Reversal API ar_receipt_api_pub.reverse and Invoice Adjustment API ar_adjust_pub.create_adjustment


I was working on a custom program to apply receipts, credit memos to invoices and debit memos. I was creating good amount of data. Before starting a new set of tests, I wanted to close transactions and payments created in prior run. It was little cumbersome to adjust invoices, Debit memos, credit memos and reverse receipts using forms. So I created this small script and run it using a simple concurrent program. This script can adjust hundreds or invoices and receipts in no time.

Thursday, October 13, 2016

Determination of Default Receipt Application GL Date

  1. Check the value of profile option "AR: Application GL Date Default"

    1. If this profile option is equal to “INV_REC_SYS_DT”, then Receipt application GL date is largest of

      1. Receipt GL Date

      2. Invoice GL Date

      3. System Date

    2. If this profile option is equal to NULL or INV_REC_DT then Receipt application GL date is largest of

      1. Receipt GL Date

      2. Invoice GL Date

  2. If Receipt Application Date is in CLOSED GL Period then

    1. Get the minimum value of START_DATE from GL_PERIOD_STATUS for the application, set of books such that period status is either ‘O’ or ‘N’ or ‘F’

  3. The GL Date for receipt application is derived using ARP_UTIL.VALIDATE_AND_DEFAULT_GL_DATE. If there is any need to default GL is custom program then this procedure can be used. This procedure checks if input date is valid or not. If input GL date is not valid then it provides next available GL date. For example suppose Receipt and AR Invoice GL dates are 20-Sep-2016 and period SEP-2016 is closed than this procedure will provide first date of next open period. I have not tested what happens if no period is open. I think it is highly unlikely that no GL period is open receivables.

Wednesday, October 05, 2016

Purchasing API Example: PO_CHANGE_API1_S.update_po


I had to modify deliver to location and PO distribution DFF on multiple purchase orders. All those purchase orders had "Authorization Status" as "Approved". I thought of using Oracle Purchasing API PO_CHANGE_API1_S.update_po. It was not easy to use this API for following reasons.

  1. API did not allow update of Deliver to location although Purchase Orders form allows update of deliver to location.

  2. Handling of DFF update has bugs. When I updated flexfield, I got following error
    The flexfield on this field contains a flexfield bind variable with a :BLOCK.FIELD reference. This prohibits operation in the mode that optimizes performance over a wide-area network. To continue using this form, have the system administrator either remove all :BLOCK.FIELD references, or turn off wide area operation by setting the profile option - Flexfields: Validate on Server to N. The reference may be in a WHERE_CLAUSE token passed into the flexfield definition in the form, a WHERE clause in a table-validated value set, or a default value for one of the segments of this flexfield.

    We could not update PO distribution DFF to remove :block.field references. By setting profile option "Flexfields:Validate On Server" to "No" did not help. Refer to FLEXFIELDS: Validate on Server Profile Option (Doc ID 273044.1) for more information on this profile option.
Since I had created a test script so I tried to see how it works by updating PO description, item description.

Updating PO Line quantity worked very well. When I updated line quantity, it updated shipment quantity and distribution quantity too. I was thinking that I have provide these values in shipment and distribution records. Sample is given below. If you a better way to initialize TYPE PO_PUB_UPDATE_REC_TYPE, please let me know.

Thursday, September 08, 2016

Log and Output Files of Concurrent Requests


I do not perform user acceptance testing but recently I had to help users with acceptance testing. I was supposed to run a request set and save log files and output files. That request set had 38 concurrent programs and users wanted to save 20 log and output files. It was painful to save log and output files. So I created this small script that can save log and output files for all requests. This script should be modified for

  1. Username who ran the request sets
  2. Request set run start and end time
  3. Test Case Number (It is used for group results of a request set run, it could be Request Set request id too)
  4. Directory name where file should be copied
The output of this script can be transferred to shell script and by executing that shell script, log and output files can be copied to desired directory.

Thursday, July 21, 2016

Receivables AutoInvoice

This is a great oracle document about Autoinvoice.

AutoInvoice Setup for Release 12: A Case Study Using a Simple Script metalink note [ID 1067402.1](Doc ID 1067402.1)

I feel Oracle should have provided validation of DFF for invoice header, lines and distributions tables. In this way invoice lines/distributions with invalid DFF would have remained in interface tables and users can update them and import in the next run of autoinvoice.

Saturday, July 16, 2016

Query for Imported AR Invoices


We import Receivable Invoices using AutoInvoice interface. AutoInvoice program displays total number of invoices imported and errors if any. It does not tell what invoices were imported. So i wrote this simple query to find out invoices imported. This query works because oracle has provided index on creation date column of RA_CUSTOMER_TRX_ALL table. This sample query returns invoices that were created on 8th Jul between 7:25 and 8:50 am.

Saturday, June 25, 2016

Query for Application of Receipts/Credit Memos to Invoices/Debit Memos

I have a custom pl/sql program that applied credit memos and receipts to invoices and debit memos. At the end of program i wanted to see what has been applied to what. Receipt UI was not very good for viewing All applications. So i wrote query to check how receipts and credit memos are applied to invoices and debit memos.

I noticed that when a receipt is applied to invoice or debit memo, APPLY_DATE in AR_RECEIVABLES_APPLICATIONS table has no time component but when a credit memo is applied to invoice or debit memo, it has time component.

This query tells me what has been applied on 13th Jun 2016 between 8 am to 10 am. If APPLY_DATE for receipt application had time component, there would not be any need for condition of creation_date column. I can not use only creation date because there is no index on this column.

Friday, March 04, 2016

Poor Performance of Posting: Single Ledger: Query#1

We were upgrading to Oracle database 12c. During regression testing we found that following sql is running very poorly for some JV Batches. Query is given below. If you look at the query you would notice that POSTING_RUN_ID is hard coded. It means this is a dynamic sql and Oracle developer had appended POSTING_RUN_ID to the query instead of using a bind variable. Biggest drawback of hard coded value: Every time sql is different, it has different sql_id, plan hash value and i can not pin a sqlplan for this query. This query works fine when a JE header has less number of lines. In our case it does not work very well when one header has 200k lines. it trying to access GL_JE_LINES using unique index but LINE_NUM is not used in the query so it ends up accessing 200K for each Header record and hence slow performance. following bug were filed longtime back but no resolution

Bug 6415832: No resolution

Bug 7044466: No resolution. As per this bug customer suggested creating a new index on JE_HEADER_ID, CODE_COMBINATION_ID and asked Oracle's blessing but i think it was not approved because no such index in Rel12.1.3. To solve our problem in PROD, we are thinking about creating an index on CODE_COMBINATION_ID , PERIOD_NAME and JE_HEADER_ID. This index works great for us.

I wonder what is the use of where clause in this query? Subquery has same where clause. So if record in not found pi.period_net_dr and pi.period_net_dr will be just overwritten. Am i right?

Tuesday, March 01, 2016

Oracle Alert Creation Example

I had created one alert long time back and few days back i had to test one alert and I did not remember anything, So read the this document and noted following points for future reference.

  1. Responsibility: Alert Manager

  2. Navigation: Alert -> Define

  3. You will see following form (with all fields balnk)


  4. Periodic tab has information for concurrent program "Periodic Alert Scheduler". "Periodic Alert Scheduler" will schedule a concurrent program to run the alert based on Frequency column value.

    Concurrent program name will be Alert Name + ' ' + (Check Periodic Alert).

    Start Time, End Time and Check Interval are used for running it multiple times in a day starting at "Stat Time and ending at "End Time" at an interval given by "Check Interval" Value. Suppose an alert is defined to run "Every 1 Calendar Days" and start time, end time and check interval are 00:00:00, 9:00:00 and 01:00:00 and "Periodic Alert Scheduler" is scheduled to run everyday at 7 am, then Alert will run between 7 am to 9 pm every 1 hours. Three times in this case. If "Periodic Alert Scheduler" was scheduled to run at 12 am everyday then this alert will run 10 times every day (12 am to 9 am). In this particular case i had changed the schedule of "Periodic Alert Scheduler" to start at 7am and run indefinitely.

    If "Periodic Alert Scheduler" is not running then any alert will not be run. This may happen in development environment so that users do not get unnecessary alert about test data or developer can change the email change the alert recipient's email address

  5. Import Button: If you do not want to cut and paste query from a file or sql developer, you can use this button to enter sql statement for Alert

  6. Export Button: If you do not want to use cut and paste, you can use this button to save query to a file

  7. Verify Button: Use this button to check for syntax errors

  8. Run Button: Use this button to know how many rows will be returned.

  9. Actions Button: It seems results of sql statement is called EXCEPTION. Based on results of the query you may want to

    1. If Query returns No rows then it is a case of "NO Exception" and you may take an action such as send email that informing alert recipient that No results were found. it may be good or bad. For example if no orders were shipped yesterday then it is bad and if no concurrent programs failed yesterday, it is good

    2. If query returns some result then you may send "Summary" action i.e. send one email providing results of sql statements

    3. If query returns some results then you may send "Detail" action i.e. send alert for each row of query result. For example, Alert query may identify users whose password will expire within N days. Detail action can send email to each user separately and request them to change password.

    Each action can be of following 4 types

    1. Message

    2. Concurrent Program

    3. Operating system Script

    4. Sql Statement Script

    i have use only "Message" action type. For "Message" action type, you need to fill in detail like Email Address for alert recipient, subject and Layout for Query output.

  10. Action Sets Button: Any of the "Actions" created in previous step will not do anything unless included in some action set. In Members tab in Action Set Detail form, you may added actions created in previous step.

  11. Response Set Button: Did not use it

  12. Alert Details Button: In this form you can define operating unit for Alert. If i available under table "Installations" I did not change Oracle ID value, it was displayed by default as APPS

Monday, January 04, 2016

Customer Bank Account Details

Bank account can be associated with customer account or customer site. This query can find all bank accounts associated with a customer and it's sites

Tuesday, November 17, 2015

Changing Password using Sql Developer


At my site i have access to 6 database instances. Every three months i have to change passwords for each instance and as per rules, we have to use longer and complicated passwords. For changing password in one instance, I had to do the following

  1. Login to that instance using sqlplus, i had to enter existing password once
  2. Type Password
  3. Enter Old Password
  4. Enter New Password
  5. Enter New Password
And if there is any mistake in typing, redo whole thing again. Today I found a better way to change password in all instance
  1. Login to database using sql developer
  2. Type following command
    alter user user_name identified by "New_Password" replace "Old_Password";
and password changes without any problem.

Note: New_Password and Old_Password values should be in double quotes.

Wednesday, October 28, 2015

Oracle Forms Runtime Diagnostics and Trace

This is applicable to Oracle application release 12.1.3

Following metalink notes provide step to generate Forms runtime diagnostics and trace

  1. R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications ( Doc ID 438652.1 )
  2. How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12 (Doc ID 373548.1)


To Generate Forms Trace, this is what I did:

  1. Checked profile option definition for profile Forms Runtime Parameters. If this profile is not updateable by user then make it updateable by user.

  2. Now modify the profile profile Forms Runtime Parameters at user level and set it to record=forms tracegroup=full. Record and trace group are separated by a single space character. This setting of profile will generate forms trace.

  3. It will create trace file in the directory $FORMS_TRACE_DIR. Trace file name will be forms_<pid>.trc . This file is binary file and it needs to be converted to readable format using following command. First connamd give xml output and second one gives html output that i prefer. (please note that $AFJVAPRG is an environment variable that points to java executable

    1. $AFJVAPRG -cp $ORACLE_HOME/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=$FORMS_TRACE_DIR/forms_13840.trc outputfile=$MY_DIR/forms_trace.xml outputclass=WriteOut


    2. $AFJVAPRG -cp $ORACLE_HOME/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=$FORMS_TRACE_DIR/forms_13840.trc outputfile=$MY_DIR/forms_trace.xml outputclass=WriteOutHTML


To Generate Forms Runtime diagnostics, this is what I did:

  1. Set profile Forms Runtime Parameters at user level to record=collect

  2. It will create FRD output in $FORMS_TRACE_DIR and filename would be collect__<pid>