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>

Friday, October 02, 2015

Payables Table: AP_SUPPLIER_SITES_ALL


This is a simple table. I saved this query to remind myself that phone, fax and email entered on Supplier Address region are stored in hz_contact_points table but phone, fax, telex entered on Site communication region are stored in site table itself. since vendor site is associated with a party site and there is no way to differenciate phone attached to party site and vendor site, Oracle saves Vendor site level phone, fax in site record itself and does not use TCA architecture. May be Oracle should use "AP_SUPPLIER_SITES_ALL" too for hz_contact_points.owner_table_name

Wednesday, September 16, 2015

Simple Invoices Purge

Simple Invoices i.e. invoices that are not matched to purchase orders can be purged using form "Purge" (Form name = APXPGSUB.fmx). This form is accessible to responsibility "Payables Purge". This form allows to purge payables data based on Last Activity date. This form does not allow to purge a single invoice to see how invoice purge works. Based on Last Activity data parameters, you can limit the number of invoices selected for Purge. This form can be used for purging

  1. Simple Invoices
  2. Simple PUrchase Orders
  3. Simple Requisitions
  4. Suppliers
  5. Invoices and Purchase Orders
By default this form queries only those purges that are not in following statuses
      COMPLETED-ABORTED
      COMPLETED-PURGED
In order to purge simple invoices, do the following
  1. Name: Purge batch name ( i normally use org id + Last activity date + some identifier such as A1, A2 to make it unique)
  2. Category should be "Simple Invoices"
  3. Select Last Activity Date. If invoice has any activity such as payment, cancellation of payment or accounting etc. on or before this date will not be selected for purge.
  4. Pay Alone only. This parameter is applicable to invoice purge only. If it is 'All' then invoice may be purged irrespective of the fact that invoice is paid alone or bunched with other invoice(s) for payment.
To start purge process, first click on "Initiate" button. It will start two concurrent program.

Initiate Purge (AP/PO Purge Initiation (Selection) Routine)
Print Purge (Purge Report Listings)

"Initiate Purge" will not start if any other invoice purge is in process i.e. a record in FINANCIALS_PURGES_ALL with status other than 'COMPLETED-PURGED' or 'COMPLETED-ABORTED'. If you selected a batch for purging and started purge process but cancelled the concurrent program due to slow performance then a record in FINANCIALS_PURGES_ALL may remain in status 'DELETING'. In that case you have to update status manually and change it to 'COMPLETED-ABORTED'

All invoices selected for purge are inserted into AP_PURGE_INVOICE_LIST

The program "Print Purge (Purge Report Listings)" waits for Initiate purge to complete. It prints list of invoices that are selected for purge. This report could be too big.

After completion of above two programs, come back to purge form. It will automatically query the purge in process. At this time you have option to
  1. Abort the purge process
  2. Confirm the Deletion of invoices selected
  3. Restart. Clicking on Restart after Initiate process will submit concurrent program "Print Purge (Purge Report Listings)" again
After initiate, you may click on "Confirm" Button to start deletion of invoices. It will start concurrent program "Delete Purge (AP/PO Purge Deletion Routine)". It will start multiple threads of program "Parallel Purge (AP/PO Purge Parallel Routine)" to delete data in parallel. Number of thread are determined by setting of profile option AP: Purge Worker Count. It will delete selected invoices and related data such as payments, holds. List of tables from where data is deleted for "Simple Invoices" is given below
  1. ap_checks
  2. ap_invoice_selection_criteria
  3. ap_doc_sequence_audit
  4. ap_payment_history
  5. ap_invoice_payments
  6. ap_payment_schedules
  7. ap_trial_balance
  8. ap_holds
  9. ap_inv_aprvl_hist
  10. ap_invoice_distributions
  11. ap_invoice_lines
  12. ap_invoices
  13. ap_batches
  14. ap_liability_balance
  15. ap_purge_invoice_list
  16. ap_encumbrance_lines
  17. ap_purge_invoice_list
When you click "Confirm" button, Oracle will verify again if invoices selected are still eligible for purge. This could be time consuming. If you are deleting selected invoice immediately after "Initiate" run then you may set profile option AP: Purge without Review to Yes and then oracle will not verify eligibility of purge. it will just go ahead and purge selected invoices.

Thursday, July 02, 2015

Profile Option: Folders: Allow Customization


If this profile option set 'Yes'or NULL then user can create and/or update folders. If you do not want users to modify any folder then set this profile option to 'No'

Saturday, June 06, 2015

GL Data Purge


In order to Purge GL Data, Following steps are needed.

  1. Close GL Period Permanently. Follow these steps to close GL Period Permanently. when you update status of a period to Permanently Closed, all other prior periods too get permanently closed. When you save the changes, form will submit concurrent program Periods - Permanently Close Period

  2. Before purging, set storage parameter for GL Archive tables using form "GLXSTSPM". This form is available to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Storage".

  3. GL Data can be archived and Purged using form GLXSTPRG. This form is accessible to responsibility General Ledger Super User. Navigation for this form is "Setup -> System -> Purge". If you do not have access to responsibility General Ledger Super User then you may use this link to determine which other responsibility have access to that form.

  4. In order to Purge GL data, it must be archived first. GL Data is purged from following six tables

    1. GL_BALANCES
    2. GL_JE_BATCHES
    3. GL_JE_HEADERS
    4. GL_JE_LINES
    5. GL_IMPORT_REFERENCES
    6. GL_JE_SEGMENT_VALUES

  5. The GL data from above tables is archived into following tables

    1. GL_ARCHIVE_BALANCES
    2. GL_ARCHIVE_BATCHES
    3. GL_ARCHIVE_HEADERS
    4. GL_ARCHIVE_LINES
    5. GL_ARCHIVE_REFERENCES
    6. GL_ARCHIVE_SEGMENT_VALS

  6. Data could be archived for a range of periods. Once data is archived, it could be exported for future reference

  7. After archiving GL data for a period or range of periods, you can not archive GL data for another period. Before you could archive another period, You need to do the following

    1. Purge GL data. If GL data is archived for multiple periods then purge could be performed one period at time

    2. Export archived data for future reference. This data not be loaded back into GL tables. Oracle does not recommend it. You have to develop custom programs if you intend to review archived data. This step is optional

    3. When all archived data is purged, Truncate/Delete archived data

  8. GL_ARCHIVE_HISTORY: This table has information about archive and purge runs. This table is used for List of value for Archive and Purge "Periods To" in "Archive and Purge" form (GLXSTSPM)

  9. When we purged GL data, Purge process was slow. We disabled few indexes on GL_BALANCES and GL_JE_LINES and purge performance was acceptable.

Thursday, May 21, 2015

Purge Purchasing Open Interface Processed Data


The concurrent program "Purge Purchasing Open Interface Processed Data" can be used for purging data from PO interface tables that has been either imported successfully or rejected. This program provides good set of parameters for purging data from interface tables. This program runs sql script $PO_TOP/sql/POXPOIPR.sql to purge data. This must be very old program because it does not make any sense to run pl/sql script. it should have been executing a pl/sql package because that would more efficient. The script calls following procedure to purge data from interface tables.

po_docs_interface_purge.process_po_interface_tables

The concurrent program does not provide any parameter for purging "IN PROCESS" records but this procedure does a check for deleting "IN PROCESS" records. On a second look, i found that program can purge "IN PROCESS" interface records only if parameter "Document" (PO Number) is passed to this program. At my site, i can not pass "Document" parameter because of incorrect definition of value set "PO_PDOI_PURGABLE_DOC_NUM".

This procedure deletes data from following table

  1. po_headers_interface
  2. po_lines_interface
  3. po_line_locations_interface
  4. po_distributions_interface
  5. po_price_diff_interface
  6. po_attr_values_interface
  7. po_attr_values_tlp_interface
Data from all tables is deleted based on interface_header_id column of po_headers_interface table.

Tuesday, May 19, 2015

Navigation Path for Oracle Form/OAF Page (Query#2)


This is another query, probably slightly better query to find out responsibility and navigation path for a particular Form or OAF Page. This query takes advantage of CONNECT_BY_ROOT to get responsibility associated with top level menu. The other query uses SYS_CONNECT_BY_PATH(menu_id,' --> ') to join with fnd_responsibilities_vl. Although that worked but i am little apprehensive of that kind of trick.

Purchasing Table: PO_HEADERS_ALL


This query provides information about lookup codes and foreign keys used by PO_HEADERS_ALL table. I have added few additional columns that i use frequently.

Tuesday, September 30, 2014

Trace Option for Concurrent Program

By setting profile option "Concurrent: Allow Debugging", the "Debug Option" button on Standard Request Submission form is enabled and you can enter type of sql trace such as

  1. Sql Trace Regular
  2. Sql Trace Regular with binds and waits
  3. Sql Trace Regular with binds
  4. Sql Trace Regular with waits

Saturday, September 06, 2014

Oracle SQL Hint NO_UNNEST


One of my custom program suddenly started showing poor performance. Following sql was the culprit



This was the sql plan



The table AP_INVOICE_DISTRIBUTIONS_ALL has more than 100 million rows, so HASH JOIN did not work for me. In this case hint NO_UNNEST was very useful. (/*+ NO_UNNEST */ was added to subquery to force optimizer to not UNNEST)
I think in this case if Oracle optimizer had unnested subquery and used nested loop, it might have been better performance. i have noticed that in our instance whenever a large table is used in HASH JOIN, it causes problem.

The sql plan after adding the hint

Although total cost for sql plan with hint was much higher, it took just few seconds. But without hint, it took 30 minutes.

Friday, August 08, 2014

Difference in GL Interface and GL Interface History Structure


I was running Journal import and found that insert into GL_Interface_history is very slow. After completion of Journal import program, it moves records from GL_Interface table to GL_Interface_History table. I was looking for certain column in GL_INTERFACE_HISTORY but could not find it, so i wanted to compare structure of both tables. I did not know any better way, so i used following sql to see the differences between two tables. By ordering the result by 2, i get column that are not in GL_INTERFACE_HISTORY.

Tuesday, August 05, 2014

Oracle Procedure DBMS_SPACE.SPACE_USAGE


We were doing GL Purge. I wanted to see how much space has been freed up from GL Purge process. So i used following query

Result of above query did not change from what i got before purge. GL Purge will definitely not release freed up space. I wanted a general idea of what has happened to table blocks. I used procedure DBMS_SPACE.SPACE_USAGE to see how many blocks have what kind of free space. The script is as follows