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.
select to_char(last_update_date,'YYYY') Year , sum(decode(payment_Status_flag,'Y',1, 0)) "Paid Invoices" , sum(decode(payment_Status_flag,'N', decode(cancelled_date,NULL,1, 0),0)) "Unpaid Invoices" , sum(decode(payment_Status_flag,'N', decode(cancelled_date,NULL,0, 1),0)) "Cancelled Invoices" from ap_invoices_all where last_update_date between to_date('01-jan-2015','dd-mon-yyyy') and to_date('01-jan-2020','dd-mon-yyyy') group by to_char(last_update_date,'YYYY') order by 1;

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

Link to Setup Guide

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.

SELECT /*+ leading (pmt_mod pmt doc) use_nl (pmt_mod pmt doc) */ call_app_pay_service_req_code , pmt.payment_id , pmt.paper_document_number , doc.document_status , COUNT(9) , SUM(doc.payment_amount) , pmt.payment_amount FROM iby_payments_all pmt , iby.iby_docs_payable_all doc , ( SELECT /*+ leading (psr_in) use_nl (psr doc_in) NO_UNNEST */ DISTINCT doc_in.payment_id , call_app_pay_service_req_code FROM iby_docs_payable_all doc_in , iby_pay_service_requests psr_in WHERE doc_in.payment_service_request_id = psr_in.payment_service_request_id AND psr_in.creation_date > sysdate -100 AND psr_in.call_app_pay_service_req_code LIKE '%EFT%' /* This is filter payment requests*/ AND doc_in.document_status IN ('REMOVED') ) pmt_mod WHERE pmt.payment_id = pmt_mod.payment_id AND pmt.payment_id = doc.payment_id GROUP BY call_app_pay_service_req_code , pmt.payment_id , doc.document_status , pmt.paper_document_number , pmt.payment_amount ORDER BY 1,2,3 ;

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;

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.

declare l_msg_count number; l_msg_data VARCHAR2(4000); l_return_status VARCHAR2(3); l_index NUMBER; -- -- Following values should be changed based on your environment -- Make sure that you can delete project using the responsibility given below otherwise you will get following error -- "You do not have update privileges on this project. A project can be updated only by its key members or users with cross-project responsibility." -- l_org_id NUMBER := 1; l_user_id NUMBER := 500; l_resp_id NUMBER := 22593 /* Responsibility = Project Super User */; l_resp_appl_id NUMBER := 275 /* Application = Projects*/ ; CURSOR c_projs is SELECT pm_product_code, pm_project_reference, project_id, segment1 from pa_projects where created_by = l_user_id and creation_date > to_date('11-DEC-2018 08:00:00', 'DD-MON-YYYY HH24:MI:SS') and segment1 like 'SC%' ; begin mo_global.set_policy_context('S',l_org_id); fnd_global.APPS_INITIALIZE(user_id=>l_user_id , resp_id=> l_resp_id, resp_appl_id=> l_resp_appl_id); l_index := 0; FOR l_rec in c_projs LOOP l_index := l_index + 1; dbms_output.put(to_char(l_index,'99999') || ' Project ' || l_rec.segment1); pa_project_pub.delete_project ( p_api_version_number => 1.0 , p_commit => FND_API.G_FALSE , p_init_msg_list => FND_API.G_TRUE , p_msg_count => l_msg_count , p_msg_data => l_msg_data , p_return_status => l_return_status , p_pm_product_code => l_rec.pm_product_code , p_pm_project_reference => l_rec.pm_project_reference , p_pa_project_id => l_rec.project_id ) ; IF l_return_status = 'S' THEN dbms_output.put_line(' Deleted Successfully'); ELSE dbms_output.put_line(' Deletion Failed, errors are:'); FOR j in 1..FND_MSG_PUB.count_msg LOOP FND_MSG_PUB.get ( p_msg_index => j , p_encoded => 'F' , p_data => l_msg_data , p_msg_index_out => l_index ) ; dbms_output.put_line(' Error : ' || l_msg_data); END LOOP; END IF; END LOOP; end;

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.

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.
   

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.

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.

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.

SELECT "Request Set Name" , Request_id "Request ID" , "Stage Seq" , "Program Seq" , "Concurrent Program Name" , "Parameter Name" , DECODE(num, 1, (SELECT argument1 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 2, (SELECT argument2 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 3, (SELECT argument3 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 4, (SELECT argument4 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 5, (SELECT argument5 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 6, (SELECT argument6 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 7, (SELECT argument7 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 8, (SELECT argument8 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 9, (SELECT argument9 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 10, (SELECT argument10 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 11, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 12, (SELECT argument12 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 13, (SELECT argument13 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 14, (SELECT argument14 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 15, (SELECT argument15 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 16, (SELECT argument16 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 17, (SELECT argument17 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 18, (SELECT argument18 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 19, (SELECT argument19 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 20, (SELECT argument20 FROM fnd_run_requests a1 WHERE parent_request_id= a.request_id and a1.concurrent_program_id = a.concurrent_program_id), 'Add Additional Sql to get parametervalues' ) "Parameter Value" FROM (SELECT rs.user_request_set_name "Request Set Name" , req.request_id Request_ID , rss.display_sequence "Stage Seq" , rsp.sequence "Program Seq" , cp.user_concurrent_program_name "Concurrent Program Name" , cp_param.column_seq_num "Param Seq" , form_left_prompt "Parameter Name" , rs.REQUEST_SET_ID , cp.concurrent_program_id , row_number () over (partition by req.request_id, CP.CONCURRENT_PROGRAM_ID order by cp_param.column_seq_num )num FROM apps.fnd_request_sets_vl rs , apps.fnd_req_set_stages_form_v rss , applsys.fnd_request_set_programs rsp , apps.fnd_concurrent_programs_vl cp , apps.fnd_descr_flex_col_usage_vl cp_param , fnd_concurrent_requests req , fnd_concurrent_programs rset_prog WHERE rs.user_request_set_name LIKE '<User Request Set Name>' AND rs.application_id = rss.set_application_id AND rs.request_set_id = rss.request_set_id AND rss.set_application_id = rsp.set_application_id AND rss.request_set_id = rsp.request_set_id AND rss.request_set_stage_id = rsp.request_set_stage_id AND rsp.program_application_id = cp.application_id AND rsp.concurrent_program_id = cp.concurrent_program_id AND rs.end_date_active IS NULL AND cp_param.descriptive_flexfield_name (+) = '$SRS$.' || cp.concurrent_program_name AND cp_param.application_id (+) = cp.application_id AND cp_param.enabled_flag (+) = 'Y' AND req.concurrent_program_id = rset_prog.concurrent_program_id AND req.program_application_id = rset_prog.application_id AND req.phase_code = 'P' AND rset_prog.concurrent_program_name = 'FNDRSSUB' AND req.argument1 = to_char(rs.application_id) AND req.argument2 = to_char(rs.request_set_id) ORDER BY req.request_id , rss.display_sequence , rsp.sequence , cp_param.column_seq_num ) a;

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.

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)

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.

# import copy boardPostions = [] solutionNumber = 0 def getEmptyBoard(): """Get empty board.""" emptyBoard = [[0, 0, 0, 0, 0, 0, 0, 0], # Row number 1 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 2 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 3 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 4 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 5 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 6 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 7 [0, 0, 0, 0, 0, 0, 0, 0], # Row Number 8 [0, 0, 0]] # row and column position of knight and current move number return emptyBoard def printBoard(singleBoardPosition): """Print board postions.""" global solutionNumber print("===", solutionNumber, singleBoardPosition[8][0], singleBoardPosition[8][1], singleBoardPosition[8][2], ":") if singleBoardPosition[8][2] > 63: solutionNumber = solutionNumber + 1 for i in range(8): for j in range(8): print("%3d" % (singleBoardPosition[i][j]), end="") print() def copyBoardElements(b1, b2): """Copy board elements.""" if b1 is None: print("copyBoardElements, b1 is None") if b2 is None: print("copyBoardElements, b2 is None") for cp1 in range(8): for cp2 in range(8): b1[cp1][cp2] = b2[cp1][cp2] b1[8][0] = b2[8][0] b1[8][1] = b2[8][1] b1[8][2] = b2[8][2] def canReachFrom2Squares(board): """Check if all empty postions can be reached from two other positions.""" # # Function to check if board has an empty square that can not be reached from 2 or more squares # i = 0 j = 0 totalCannotReach = 0 if board[8][2] >= 58: # print("Return from top line") return True # else: # print('Current postion on the board is ', board[8][2]) # print("checking unreacheable squares", i, j) for i in range(8): for j in range(8): canReach = 0 if board[i][j] == 0: newRow = i+2 newCol = j+1 # print ("checking 1", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-1") # newRow = i+2 newCol = j-1 # print ("checking 2", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-2") # newRow = i-2 newCol = j+1 # print ("checking 3", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-3") # newRow = i-2 newCol = j-1 # print("checking 4", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-4") # newRow = i+1 newCol = j+2 # print("checking 5", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-5") # newRow = i+1 newCol = j-2 # print("checking 6", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-6") # newRow = i-1 newCol = j+2 # print("checking 7", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-7") # newRow = i-1 newCol = j-2 # print("checking 8", newRow, newCol) if canReach < 2 and newRow in range(0, 8) and newCol in range(0, 8) and board[newRow][newCol] == 0: canReach = canReach+1 # print("Can reach Number-8") # # print("canReach value = ", canReach) if canReach < 2: totalCannotReach = totalCannotReach+1 if totalCannotReach > 1: # print("all not reachable from two positions", i, j) # printBoard(board) return False return True def makeInitialMove(): """Make initial move on the board.""" for i in range(0, 8): for j in range(0, 8): initBoard = getEmptyBoard() initBoard[i][j] = 1 initBoard[8][0] = i initBoard[8][1] = j initBoard[8][2] = 1 boardPostions.append(initBoard) def makeSingleMove(i, j, k, board): """Make knight move onthe board.""" # check if new position is in the board or out of board and postion should be empty if i in range(0, 8) and j in range(0, 8) and board[i][j] == 0: newBoard = getEmptyBoard() copyBoardElements(newBoard, board) # newBoard[8][2] = k # before making a move, check if every position can be reached from two other postions # no need to perform that check if knight is moving for the first time # no need to check if knight has already traversed all blocks, goal achieved. if k == 64 or canReachFrom2Squares(newBoard): newBoard[i][j] = k newBoard[8][0] = i newBoard[8][1] = j newBoard[8][2] = k if k == 64: print("Knight traversed whole board without repeating any block", end="") printBoard(newBoard) else: # if knight traversed all block then why put board back on the stack? boardPostions.append(newBoard) # else: # printBoard(newBoard) # print("it will make board unreachable if knight move to ", i, j, "for move number=", k) def makeKnightmove(): """Make knight move.""" if boardPostions[len(boardPostions)-1] is None: return 1 currentBoardPostion = getEmptyBoard() # copyBoardElements(currentBoardPostion, boardPostions[len(boardPostions)-1]) # # print("Removing last board") # boardPostions.pop() currentBoardPostion = boardPostions.pop() row = currentBoardPostion[8][0] col = currentBoardPostion[8][1] moveNumber = currentBoardPostion[8][2] makeSingleMove(row+2, col+1, moveNumber+1, currentBoardPostion) makeSingleMove(row+2, col-1, moveNumber+1, currentBoardPostion) makeSingleMove(row-2, col+1, moveNumber+1, currentBoardPostion) makeSingleMove(row-2, col-1, moveNumber+1, currentBoardPostion) makeSingleMove(row-1, col+2, moveNumber+1, currentBoardPostion) makeSingleMove(row-1, col-2, moveNumber+1, currentBoardPostion) makeSingleMove(row+1, col+2, moveNumber+1, currentBoardPostion) makeSingleMove(row+1, col-2, moveNumber+1, currentBoardPostion) # return moveNumber+1 # solutionNumber = 0 # for i in range(8): # for j in range(8): # # boardPostions.append(makeMove(i, j, 1, None)) # intialBoard = getEmptyBoard() # makeSingleMove(i, j, 1, None) print("Make initial moves. it will put 64 boards each board representing initial position of knight.") makeInitialMove() print("Now start making moves after initial moves.") loop_count = 0 current_move = 1 while True: # print("") # print("loop number", loop_count, "Number of boards", len(boardPostions)) makeKnightmove() loop_count = loop_count+1 if len(boardPostions) == 0: break

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.

DECLARE l_adjust_and_reverse varchar2(30) := '&1'; CURSOR c_receipts_or_invoices IS SELECT acct.account_number , party.party_name , aps.trx_number , decode(aps.class,'PMT', cash_receipt_id, payment_schedule_id) Id , aps.class , aps.amount_due_original , aps.amount_due_remaining FROM ar_payment_schedules aps , hz_cust_accounts acct , hz_parties party WHERE aps.amount_due_remaining <> 0 AND aps.customer_id = acct.cust_account_id AND acct.party_id = party.party_id AND party.party_name like 'SANJAI%' ORDER BY acct.account_number , aps.trx_number ; PROCEDURE get_error_msg IS i number; l_msg_data varchar2(4000); BEGIN IF fnd_msg_pub.count_msg > 0 THEN FOR j in 1..FND_MSG_PUB.count_msg LOOP FND_MSG_PUB.get ( p_msg_index => j , p_encoded => 'F' , p_data => l_msg_data , p_msg_index_out => i ); fnd_file.put_line( fnd_file.log, 'Error: ' || j || ':' || l_msg_data); END LOOP; END IF; END get_error_msg; -- -- PROCEDURE reverse_receipt ( p_cash_receipt_id number ) IS l_ret_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(32000); BEGIN ar_receipt_api_pub.reverse( -- Standard API parameters. p_api_version => 1.0 , p_init_msg_list => FND_API.G_TRUE , p_commit => FND_API.G_FALSE , p_validation_level => FND_API.G_VALID_LEVEL_FULL , x_return_status => l_ret_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data -- Receipt reversal related parameters , p_cash_receipt_id => p_cash_receipt_id , p_receipt_number => NULL , p_reversal_category_code => 'NSF' , p_reversal_category_name => NULL , p_reversal_gl_date => NULL , p_reversal_date => NULL , p_reversal_reason_code => 'NSF' , p_reversal_reason_name => NULL , p_reversal_comments => NULL , p_called_from => NULL , p_attribute_rec => AR_RECEIPT_API_PUB.attribute_rec_const , p_global_attribute_rec => AR_RECEIPT_API_PUB.global_attribute_rec_const , p_cancel_claims_flag => 'Y' , p_org_id => fnd_global.org_id ) ; get_error_msg; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status for receipt id (' || p_cash_receipt_id || ') = ' || l_ret_status); FND_FILE.PUT_LINE(FND_FILE.LOG, ' '); END; -- -- PROCEDURE adjust_transaction ( p_payment_schedule_id NUMBER ) IS l_msg_count NUMBER; l_msg_data VARCHAR2(32000); l_ret_status VARCHAR2(1); l_adj_rec ar_adjustments_all % ROWTYPE ; l_adj_num ar_adjustments_all.adjustment_number % TYPE; l_adj_id ar_adjustments_all.adjustment_id % TYPE; l_old_adj_id ar_adjustments_all.adjustment_id % TYPE; BEGIN l_adj_rec.type := 'INVOICE'; l_adj_rec.payment_schedule_id := p_payment_schedule_id; l_adj_rec.receivables_trx_id := 3026; l_adj_rec.apply_date := trunc(sysdate); l_adj_rec.gl_date := trunc(sysdate); l_adj_rec.created_from := 'SKM_SCRIPT'; ar_adjust_pub.create_adjustment ( p_api_name => 'AR_ADJUST_PUB' , p_api_version => 1.0 , p_init_msg_list => FND_API.G_TRUE , p_commit_flag => FND_API.G_FALSE , p_validation_level => FND_API.G_VALID_LEVEL_FULL , p_msg_count => l_msg_count , p_msg_data => l_msg_data , p_return_status => l_ret_status , p_adj_rec => l_adj_rec , p_chk_approval_limits => FND_API.G_TRUE , p_check_amount => FND_API.G_TRUE , p_move_deferred_tax => NULL , p_new_adjust_number => l_adj_num , p_new_adjust_id => l_adj_id , p_called_from => 'SKM_TEST_SCRIPT' , p_old_adjust_id => l_old_adj_id , p_org_id => fnd_global.org_id ) ; get_error_msg; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status = ' || l_ret_status || ', Adjustment ID = ' || l_adj_id); FND_FILE.PUT_LINE(FND_FILE.LOG, ' '); END adjust_transaction; -- -- BEGIN -- create_ar_invoice; FND_FILE.PUT_LINE(FND_FILE.LOG, 'Following Transactions will be Adjusted or Reversed'); FND_FILE.PUT_LINE(FND_FILE.LOG,''); FND_FILE.PUT_LINE(FND_FILE.LOG, 'Account number Party Name Trx Number ID Class Amt Due Orig Amt'); FND_FILE.PUT_LINE(FND_FILE.LOG, '====================================================================================================================='); FOR l_rec IN c_receipts_or_invoices LOOP FND_FILE.PUT_LINE(FND_FILE.LOG, rpad(l_rec.account_number , 20,' ') || ' ' || rpad(substr(l_rec.party_name,1,30), 30,' ') || ' ' || rpad(substr(l_rec.trx_number,1,20), 20,' ') || ' ' || to_char(l_rec.id,'999999999') || ' ' || rpad(substr(l_rec.class ,1, 5), 5,' ') || ' ' || to_char(l_rec.amount_due_remaining,'999999999.99') || ' ' || to_char(l_rec.amount_due_original ,'999999999.99') ); IF l_adjust_and_reverse = 'Y' THEN IF l_rec.class = 'PMT' THEN reverse_receipt(l_rec.id); ELSE adjust_transaction(l_rec.id); END IF; END IF; END LOOP; END; /

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.
   
   

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.

DECLARE CURSOR c_dists_to_update IS SELECT pod.po_distribution_id , pod.distribution_num , pod.line_location_id , pll.shipment_num , pod.po_line_id , pol.line_num , pod.po_header_id , poh.org_id , poh.segment1 , pod.attribute1 pod_attr1 , pod.attribute2 pod_attr2 , 'SKM_LOCATION' new_location_code /* New Location code was determined using subquery and must exist in hr_locations */ FROM po_distributions_all pod , po_headers_all poh , po_lines_all pol , po_line_locations_all pll WHERE poh.po_header_id = pod.po_header_id AND pol.po_line_id = pod.po_line_id AND pll.line_location_id = pod.line_location_id AND poh.segment1 LIKE 'SKM-20161005-0%' ; TYPE varchar2_table IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER; TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; l_attr13 VARCHAR2_TABLE; l_attr13_new VARCHAR2_TABLE; l_line_location_id NUMBER_TABLE; l_distribution_id NUMBER_TABLE; l_hr_location_id NUMBER_TABLE; l_rec_count NUMBER; l_po_rec PO_PUB_UPDATE_REC_TYPE; l_errors PO_API_ERRORS_REC_TYPE; l_ret_status VARCHAR2(240); PROCEDURE update_po ( p_po_header_id NUMBER , p_segment1 VARCHAR2 , p_org_id NUMBER , p_line_num NUMBER , p_shipment_num NUMBER , p_distribution_num NUMBER , p_new_location_code VARCHAR2 ) IS BEGIN l_po_rec := po_pub_update_rec_type ( po_header_id => null , po_number => null , run_submission_checks => null , launch_approvals_flag => null , org_id => null , approval_background_flag => null , po_header_changes => PO_PUB_HEADER_REC_TYPE ( buyer_name => null , fob_lookup_code => null , payment_terms => null , description => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_line_changes => PO_PUB_LINE_TAB_TYPE ( PO_PUB_LINE_REC_TYPE ( line_num => null , line_type => null , item_category => null , unit_price => null , vendor_product_num => null , quantity => null , amount => null , unit_of_measure => null , secondary_quantity => null , preferred_grade => null , item_desc => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_line_loc_changes => PO_PUB_LINE_LOC_TAB_TYPE ( PO_PUB_LINE_LOC_REC_TYPE ( shipment_num => null , quantity => null , amount => null , promised_date => null , need_by_date => null , ship_to_location_code => null , split_shipment_num => null , new_split_ship_num => null , qty_rcv_tolerance => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null , po_dist_changes => PO_PUB_DIST_TAB_TYPE ( PO_PUB_DIST_REC_TYPE ( distribution_num => null , deliver_to_location_code => null , project => null , task_name => null , expenditure_type => null , expenditure_organization => null , expenditure_item_date => null , award_number => null , end_item_unit_number => null , quantity_ordered => null , split_distribution_num => null , new_split_dist_num => null , amount_ordered => null , attribute_category => null , attribute1 => null , attribute2 => null , attribute3 => null , attribute4 => null , attribute5 => null , attribute6 => null , attribute7 => null , attribute8 => null , attribute9 => null , attribute10 => null , attribute11 => null , attribute12 => null , attribute13 => null , attribute14 => null , attribute15 => null , attribute16 => null , attribute17 => null , attribute18 => null , attribute19 => null , attribute20 => null ) ) ) ) ) ) ) ) ; l_po_rec.po_header_id := p_po_header_id; l_po_rec.org_id := p_org_id; l_po_rec.launch_approvals_flag := fnd_api.g_true; l_po_rec.po_header_changes.description := 'Update on ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'); l_po_rec.po_header_changes.attribute1 := 'ABC'; l_po_rec.po_header_changes.attribute3 := '2015/01/01 00:00:00'; l_po_rec.po_header_changes.attribute4 := '2018/12/31 00:00:00'; l_po_rec.po_header_changes.po_line_changes(1).line_num := p_line_num; l_po_rec.po_header_changes.po_line_changes(1).quantity := 65; l_po_rec.po_header_changes.po_line_changes(1).item_desc := 'Item Desc ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'); l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).shipment_num := p_shipment_num; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).ship_to_location_code := p_new_location_code; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).distribution_num := p_distribution_num; /* API does not allow update of deliver to location if authroiztion status is other than Incomplete. Although Purchase Order form allows update of deliver to location. Form does not allow update of shipto lication but API allows that. This behavior is weird because Form and API should behace same. l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).deliver_to_location_code := p_new_location_code ; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute13 := 'aaaaaaaa wwwww~187TS5' ; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute1 := l_dist_rec.pod_attr1; l_po_rec.po_header_changes.po_line_changes(1).po_line_loc_changes(1).po_dist_changes(1).attribute2 := l_dist_rec.pod_attr2; */ fnd_file.put_line( fnd_file.log, p_segment1 || ' ' || to_char(p_line_num,'99999999999') || ' ' || to_char(p_shipment_num,'99999999') || ' ' || to_char(p_distribution_num,'999999999999') || ' ' ) ; PO_CHANGE_API1_S.update_po ( p_api_version => 1.0 , p_init_msg_list => 'T' , x_return_status => l_ret_status , p_changes => l_po_rec , x_api_errors => l_errors ); IF l_errors IS NOT NULL THEN FOR i in 1..l_errors.message_name.COUNT LOOP fnd_file.put_line(fnd_file.log, 'Message name = ' || l_errors.message_name(i)); fnd_file.put_line(fnd_file.log, 'Message text = ' || l_errors.message_text(i)); END LOOP; END IF; END update_po; -- -- Begin of main pl/sql block -- BEGIN FOR l_dist_rec IN c_dists_to_update LOOP update_po ( l_dist_rec.po_header_id , l_dist_rec.segment1 , l_dist_rec.org_id , l_dist_rec.line_num , l_dist_rec.shipment_num , l_dist_rec.distribution_num , l_dist_rec.new_location_code ) ; END LOOP; END; /

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.

DECLARE l_outfile_suffix varchar2(30); l_test_case_id varchar2(15) := 'TestCase01_' ; l_dir varchar2(60) := '/home/myid/tmp'; l_user_name varchar2(30) := 'myOracleUserName'; l_start_time varchar2(30) := '07-SEP-2016 14:23:51'; l_end_time varchar2(30) := '07-SEP-2016 20:23:51'; CURSOR c_req IS SELECT req.logfile_name , nvl(( SELECT file_name FROM fnd_conc_req_outputs WHERE concurrent_request_id = req.request_id ), outfile_name ) outfile_name , translate(prog.user_concurrent_program_name, ' '|| chr(38), '_-') name , req.request_id FROM apps.fnd_concurrent_programs_vl prog , apps.fnd_concurrent_requests req , apps.fnd_responsibility_vl resp , apps.fnd_user usr WHERE prog.concurrent_program_id = req.concurrent_program_id AND prog.application_id = req.program_application_id AND req.responsibility_id = resp.responsibility_id AND req.responsibility_application_id = resp.application_id AND req.requested_by = usr.user_id AND usr.user_name = l_user_name AND req.actual_start_date BETWEEN to_date(l_start_time, 'dd-mon-yyyy hh24:mi:ss') AND to_date(l_end_time , 'dd-mon-yyyy hh24:mi:ss') -- -- The condition below will remove request set stages. Request set stage log files do not have -- any relevant information for user acceptance -- AND prog.user_concurrent_program_name NOT LIKE '%Request%Set%Stage' ORDER BY req.actual_start_date ; BEGIN FOR l_req in c_req LOOP l_outfile_suffix := substr(l_req.outfile_name, instr(l_req.outfile_name, '.')); IF l_outfile_suffix = '.out' THEN l_outfile_suffix := '_out.txt'; END IF; dbms_output.put_line('cp ' || l_req.logfile_name || ' ' || l_dir || '/' || l_test_case_id || l_req.request_id || '_' || l_req.name || '_log.txt' || ';'); dbms_output.put_line('if [[ -e ' || l_req.outfile_name || ' ]] && [[ -s ' || l_req.outfile_name || ' ]] ; then' ); dbms_output.put_line('cp ' || l_req.outfile_name || ' ' || l_dir || '/' || l_test_case_id || l_req.request_id || '_' || l_req.name|| l_outfile_suffix || ';'); dbms_output.put_line('fi'); END LOOP; dbms_output.put_line('zip ' || l_test_case_id || ' *.*' ); dbms_output.put_line('rm ' || l_test_case_id || '*.txt'); dbms_output.put_line('rm ' || l_test_case_id || '*.PDF'); END;

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.

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.

select rct.trx_number , rct.interface_header_attribute2 Source_TRXN , ctt.name , hca.account_number , site.location , line.description , line.unit_selling_price , line.quantity_invoiced , line.uom_code , dist.amount , dist.account_class , gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 gl_account , dist.attribute1 , dist.attribute2 , rct.customer_trx_id , dist.cust_trx_line_gl_dist_id , rct.creation_date from ra_customer_trx rct , ra_customer_trx_lines line , ra_cust_trx_line_gl_dist dist , hz_cust_accounts hca , hz_cust_site_uses site , ra_cust_trx_types ctt , gl_code_combinations gcc where rct.creation_date between to_date('08-JUL-16 07:25', 'DD-MON-YY HH24:MI') and to_date('08-JUL-16 08:50', 'DD-MON-YY HH24:MI') and rct.customer_trx_id = dist.customer_trx_id and dist.customer_trx_line_id = line.customer_trx_line_id (+) and rct.cust_trx_type_id = ctt.cust_trx_type_id and rct.bill_to_customer_id = hca.cust_account_id and rct.bill_to_site_use_id = site.site_use_id and dist.code_combination_id = gcc.code_combination_id order by rct.creation_date , rct.trx_number , hca.account_number ;

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.
select rct.trx_number "INV Number" , ctt_inv.name "Invoice Type" , ps_cm.trx_number "CM/RCPT Number" , ctt_cm.name "CM/Receipt Type" , ps_inv.amount_due_original "INV Amount" , ps_cm.amount_due_original *-1 "CM/RCPT Amount" , app.amount_applied "Amt Applied" , ps_inv.amount_due_remaining "INV Amt Due" , ps_cm.amount_due_remaining *-1 "CM/RCPT Amt Due" , site_inv.location "INV Loc" , site_cm.location "CM/RCPT Loc" , app.receivable_application_id "ID" , app.creation_date from ar_receivable_applications app , ra_customer_trx rct , ar_payment_schedules ps_cm , ar_payment_schedules ps_inv , ra_cust_trx_types ctt_inv , ra_cust_trx_types ctt_cm , hz_cust_site_uses site_inv , hz_cust_site_uses site_cm where apply_date between to_date('13-Jun-2016 08:00','DD-MON-YYYY HH24:MI') and to_date('13-Jun-2016 10:10','DD-MON-YYYY HH24:MI') and app.creation_date between to_date('13-Jun-2016 08:00','DD-MON-YYYY HH24:MI') and to_date('13-Jun-2016 10:00','DD-MON-YYYY HH24:MI') /* and app.status = 'APP' */ and app.application_type = 'CM' and app.applied_customer_trx_id = rct.customer_trx_id and app.payment_schedule_id = ps_cm.payment_schedule_id and rct.cust_trx_type_id = ctt_inv.cust_trx_type_id and ps_cm.cust_trx_type_id = ctt_cm.cust_trx_type_id and rct.customer_trx_id = ps_inv.customer_trx_id and ps_inv.customer_site_use_id = site_inv.site_use_id and ps_cm.customer_site_use_id = site_cm.site_use_id union select inv.trx_number , ctt_inv.name , rcpt.receipt_number , rm.name , ps_inv.amount_due_original , ps_rcpt.amount_due_original *-1 , app.amount_applied , ps_inv.amount_due_remaining , ps_rcpt.amount_due_remaining *-1 , site_inv.location inv_loc , site_rcpt.location rcpt_location , app.receivable_application_id , app.creation_date from ar_receivable_applications app , ar_cash_receipts rcpt , ar_payment_schedules ps_rcpt , ra_customer_trx inv , ar_payment_schedules ps_inv , ra_cust_trx_types ctt_inv , ar_receipt_methods rm , hz_cust_site_uses site_inv , hz_cust_site_uses site_rcpt where app.apply_date = to_date('13-Jun-2016','DD-MON-YYYY') and app.creation_date between to_date('13-Jun-2016 08:00','DD-MON-YYYY HH24:MI') and to_date('13-Jun-2016 10:00','DD-MON-YYYY HH24:MI') and app.applied_customer_trx_id = inv.customer_trx_id and app.cash_receipt_id = rcpt.cash_receipt_id and inv.cust_trx_type_id = ctt_inv.cust_trx_type_id and app.payment_schedule_id = ps_rcpt.payment_schedule_id and app.applied_payment_schedule_id = ps_inv.payment_schedule_id and rcpt.receipt_method_id = rm.receipt_method_id and inv.bill_to_site_use_id = site_inv.site_use_id and rcpt.customer_site_use_id = site_rcpt.site_use_id /* and app.status = 'APP' */ and app.application_type = 'CASH' order by 1, creation_date, ID ;

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?

UPDATE GL_POSTING_INTERIM PI SET (PI.period_net_dr, PI.period_net_cr) = (SELECT nvl(PI.period_net_dr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, abs(JEL.stat_amount), 0 ), 0 )), 0), nvl(PI.period_net_cr,0) + nvl(sum(decode(JEL.period_name, PI.period_name, decode(sign(JEL.stat_amount), 1, 0, abs(JEL.stat_amount)), 0 )), 0) FROM GL_JE_BATCHES JEB, GL_JE_HEADERS JEH, GL_JE_LINES JEL WHERE JEB.status = 'I' AND JEB.chart_of_accounts_id = 50225 AND JEB.posting_run_id = 229329 AND JEB.average_journal_flag = 'N' AND JEH.je_batch_id = JEB.je_batch_id AND JEH.status != 'P' AND JEL.je_header_id = JEH.je_header_id AND nvl(JEL.status,'U') = 'U' AND JEL.stat_amount is not null AND PI.ledger_id = JEH.ledger_id AND PI.code_combination_id = JEL.code_combination_id AND PI.actual_flag = JEH.actual_flag AND PI.period_name = JEL.period_name AND PI.currency_code = 'STAT' AND nvl(PI.budget_version_id, -1) = nvl(JEH.budget_version_id, -1) AND nvl(PI.encumbrance_type_id, -1) = nvl(JEH.encumbrance_type_id, -1) GROUP BY JEH.ledger_id, JEH.actual_flag, JEH.encumbrance_type_id, JEH.budget_version_id, JEL.period_name, JEL.code_combination_id) WHERE (PI.ledger_id, PI.actual_flag, nvl(PI.budget_version_id, -1), nvl(PI.encumbrance_type_id, -1), PI.currency_code, PI.period_name, PI.code_combination_id) IN (SELECT JEH2.ledger_id, JEH2.actual_flag, nvl(JEH2.budget_version_id, -1), nvl(JEH2.encumbrance_type_id, -1), 'STAT', JEH2.period_name, JEL2.code_combination_id FROM GL_JE_BATCHES JEB2, GL_JE_HEADERS JEH2, GL_JE_LINES JEL2 WHERE JEB2.status = 'I' AND JEB2.chart_of_accounts_id = 50225 AND JEB2.posting_run_id = 229329 AND JEB2.average_journal_flag = 'N' AND JEH2.je_batch_id = JEB2.je_batch_id AND JEL2.je_header_id = JEH2.je_header_id AND nvl(JEL2.status,'U') = 'U' AND JEL2.stat_amount is not null)

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

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

SELECT ca.account_number "Customer Account Number" , null "Site address" , piu.start_date "Bank Account Use Start Date" , piu.end_date "Bank Account Use End Date" , eba.bank_account_num "Bank Account Number" , eba.bank_account_name "Bank Account Name" , eba.start_date "Bank Account Start Date" , eba.end_date "Bank Account End Date" FROM hz_cust_accounts ca , iby_external_payers_all epa , iby_pmt_instr_uses_all piu , iby_ext_bank_accounts eba WHERE ca.cust_account_id = epa.cust_account_id AND piu.ext_pmt_party_id = epa.ext_payer_id AND piu.payment_function = 'CUSTOMER_PAYMENT' AND eba.ext_bank_account_id = piu.instrument_id AND ca.account_number = 'SKM-PERSON-01' AND epa.acct_site_use_id is null union SELECT ca.account_number , (SELECT address1 || ' ' || loc.city || ' ' || loc.state || '-' || loc.postal_code FROM hz_locations loc , hz_party_sites hps WHERE loc.location_id = hps.location_id AND hps.party_site_id = cas.party_site_id ) addr , piu.start_date , piu.end_date , eba.bank_account_num , eba.bank_account_name , eba.start_date , eba.end_date FROM hz_cust_site_uses_all csu , iby_external_payers_all epa , iby_pmt_instr_uses_all piu , iby_ext_bank_accounts eba , hz_cust_acct_sites_all cas , hz_cust_accounts ca WHERE ca.account_number = 'SKM-PERSON-01' AND cas.cust_account_id = ca.cust_account_id AND csu.cust_acct_site_id = cas.cust_acct_site_id AND epa.acct_site_use_id = csu.site_use_id AND piu.ext_pmt_party_id = epa.ext_payer_id AND piu.payment_function = 'CUSTOMER_PAYMENT' AND eba.ext_bank_account_id = piu.instrument_id

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.

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>