Issue with receipt reversal: APP-AR-999967187: The receipt is in the process of funds capture settlement

We see few cases where receipt could be in the remitted status and when we try to reverse this receipt, it gives below error:

APP-AR-999967187: The receipt is in the process of funds capture settlement. Receipt reversal is allowed only after settlement is processed in Payments module

So, we have below options:
1. wait till the receipt goes into cleared status and then reverse the receipt
2. review the setups and ensure receipt doesn't go to the remitted status and goes directly to cleared status (can be done if we are not using cash management and statement reconciliation)
3. Use below queries to update the data and proceed with the reversal:

update apps.IBY_TRXN_SUMMARIES_ALL set status = 0, object_version_number = 55
where initiator_extension_id in (
select payment_trxn_extension_id from apps.ar_cash_receipts_all where cash_receipt_id = &cash_receipt_id) and trxntypeid = 100;


Depending on the way receipts are reconciled, accounted and reported, you need to setup your receipt class and receipt method. Remittance method and clearance method play a vital role here.

Setup navigation:
Receivables Manager -> Setups -> Receipts -> Receipt class

Below is the description about these two values and how it impacts the receipt statuses (From Oracle documentation):

To require receipts created using a payment method assigned to this receipt class to be reconciled before posting them to your cash account in the general ledger, choose one of the following Clearance Methods:

·         Directly: Choose this method if you do not expect the receipts to be remitted to the bank and subsequently cleared. These receipts will be assumed to be cleared at the time of receipt entry and will require no further processing.

·         By Automatic Clearing: Choose this method to clear receipts using the Automatic Clearing program. (Receipts using this method can also be cleared in Oracle Cash Management.)

·         By Matching: Choose this method if you want to clear your receipts manually in Oracle Cash Management.

If you checked the Require Confirmation box, choose a Remittance Method. The remittance method determines the accounts that Receivables uses for automatic receipts that you create using payment methods to which you assign this receipt class. Choose one of the following methods:

·         Standard: Use the remittance account for automatic receipts or for standard bills receivable assigned to a payment method with this receipt class.

·         Factoring: Use the factoring account for automatic receipts or for factored bills receivable assigned to a payment method with this receipt class.

·         Standard and Factoring: Choose this method if you want Receivables to select receipts assigned to this receipt class for remittance regardless of the batch remittance method. In this case, you can specify either of these remittance methods when creating your remittance batches.

·         No Remittance: Choose this method if you do not require receipts assigned to this receipt class to be remitted

Oracle Workflow - Important Queries

I've collected below queries from internet and kept in my notes. These are very useful set of queries for workflow related issues. Sharing for everyone's use:

Oracle Workflow - Important Queries
--------------------------------------------------------------

--Select all workflow items for a given item type
SELECT item_type,
       item_key,
       to_char(begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       to_char(end_date,
               'DD-MON-RR HH24:MI:SS') end_date,
       root_activity activity
  FROM apps.wf_items
 WHERE item_type = '&item_type'
   AND end_date IS NULL
 ORDER BY to_date(begin_date,
                  'DD-MON-YYYY hh24:mi:ss') DESC;


-- notifications sent by a given workflow
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type = 'WSHSUPI'
and  wias.item_key = 'CMS21408'
/

--prompt **** Find the Activity Statuses for all workflow activities of a given item type and item key
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/


--Get a list of all Errored Workflow Activities for a given item type/ item key
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/


--prompt *** Error Process Activity Statuses
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/


prompt **** Error Process Errored Activities
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/


prompt **** Attribute Values
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper('&item_type')
   AND item_key = nvl('&item_key',
                      item_key)
/
--Count of all workflow deferred activities based
SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = 'DEFERRED'
      --AND was.item_type = 'REQAPPRV'
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;


--check the various workflow agent listeners and their statuses
SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';                        
 
--query to find records that are pending in each of the workflow agent listener queues
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
       ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
       ' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            'and corrid like ''' || t.correlation_id || ''' ',
            NULL) || 'having count(*)>0;'
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';


--Look for deferred events in wf_deferred. this can also be used to track the status of notifications/business events that are waiting to be processed/that have errored out
SELECT a.user_data.geteventname(),
       decode(a.state,
              0,
              '0 = Ready',
              1,
              '1 = Delayed',
              2,
              '2 = Retained/Processed',
              3,
              '3 = Exception',
              to_char(a.state)) state,
       a.user_data.PARAMETER_LIST,
       a.user_data.event_data,
       a.user_data.event_key,
       a.*
  FROM apps.wf_deferred a
 WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
   AND rownum < 10;

Query for fetching claim from ChRM/Trade Management

Below is a basic query to fetch claims, claim types and status. Can add more columns if needed.

SELECT      (SELECT vendor_name
                        FROM apps.ap_suppliers aps
                          where oca.vendor_id = aps.vendor_id) vendor_name,
       oca.claim_id,
       oca.claim_number,
       oca.creation_date,
       oct.name,
       claim_date,
       oca.amount,
       aus.name user_status,
  FROM
       apps.ozf_claims_all oca,
       apps.ozf_claim_types_all_tl oct,
       apps.ams_user_statuses_vl aus
 WHERE     oca.claim_type_id = oct.claim_type_id
       AND aus.user_status_id = oca.user_status_id;

Query to find letters sent to customers by Advanced collections/Dunning

Below query can be used for fetching the mails that were sent out to customers by advanced collections. You can add more columns if you need and change the date accordingly:

SELECT ixr.status,
       ixr.destination,
       ixr.creation_date,
       hp.party_name,
       hca.account_number,
       hcsa.cust_acct_site_id
  FROM IEX_XML_REQUEST_HISTORIES ixr,
       hz_cust_site_uses_all hcsua,
       hz_cust_acct_sites_all hcsa,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE     ixr.creation_date > '10-AUG-2013'
       AND ixr.method = 'EMAIL'
       AND ixr.cust_site_use_id = hcsua.site_use_id
       AND hcsua.cust_acct_site_id = hcsa.cust_acct_site_id
       AND hcsa.cust_account_id = hca.cust_account_id
       AND hca.party_id = hp.party_id;

Query for fetching all responsibilities a user has

We can use below simple query to find all the responsibilities a user has:

SELECT
              (SELECT responsibility_name
                  FROM fnd_responsibility_vl frv
               WHERE fur.responsibility_id = frv.responsibility_id
                    AND nvl(frv.end_date,sysdate+1)  >  sysdate) responsibility_name,
              fur.end_date
  FROM apps.fnd_user_resp_groups_direct fur,
              fnd_user fu
 WHERE fu.user_id = fur.user_id
      AND fu.user_name = '&USERNAME';

Unix/Linux mail commands

While working on a unix based application, I used uuencode command to send mails with attachments. This also helps in avoiding FTP for small files

Below is the command:

uuencode $ATTFILE $ATTFILE | mail -s "$SUBJECT" $MAILTO

Here 

$ATTFILE is the full file name with extension

$SUBJECT is what we want to see in the subject of the mail

$MAILTO is the TO mail address

When I tried the same command to send mail in a Linux machine, it gave junk values. So, I used mutt command. Below is the syntax:

mutt -s $SUBJECT $MAILTO -a $ATTFILE1 $ATTFILE2 < mail_body.txt

$ATTFILE is the full file name with extension
$SUBJECT is what we want to see in the subject of the mail
$MAILTO is the TO mail address

mail_body.txt is what we want to see in the email body

Error while opening the forms (identifier APP_ITEM_PROPERTY.SET_PROPERTY must be declared)

Below learning was shared by one of my teammates:

While compiling forms in form builder sometimes we get some standard errors like –

“identifier APP_ITEM_PROPERTY.SET_PROPERTY must be declared”

“identifier APP_WINDOW.CLOSE_FIRST_WINDOW must be declared”

“identifier FND_STANDARD.SET_WHO must be declared”

This happens because some standard library functions will be unavailable in your form builder library.

You can resolve this by placing all standard libraries required into your ‘LIB’ folder in the following path – C:\Oracle_Home (Wherever oracle forms is installed)

You can verify the same by checking if the below files are present in your ‘Attached Libraries’ in your form builder – (this is for 6i version)

        APPCORE.pll

                    APPDAYPK.pll

                    GLOBE.pll

                    FNDSQF.pll

                    JA.pll

                    JE.pll

                    JL.PLL

                    GHR.pll

                    CUSTOM.pll

If you don't have these in your local machine,FTP these in binary mode from $AU_TOP/resource

System Profiles and values

From beginner's point of view, Profiles are something similar to global variables used by Oracle to set context based values based on which the application behavior can be controlled. There are seeded profiles(part and parcel with EBS) and we have flexibility to define custom profiles. We can control the values which can be allowed to enter as well. We need to be careful while changing values for seeded profiles as it could impact the system behavior.

Oracle allows the profile values to be set at different Levels i.e

• Site Level
• Application Level
• Responsibility Level
• User Level

The hierarchy that is followed in Oracle Applications to get the value of the profile is

• User Level
• Responsibility Level
• Application Level
• Site Level

User Level is the lowest level and oracle takes this value (if it is set) for a logged in user

Some profiles are set at responsibility level (Eg: Ledger ID) which is applicable whenever that responsibility is accessed

Site level is the highest level; if no application/resp/user values were set,this value will be considered 

Once the value is set a record gets inserted into fnd_profile_option_values. One record gets inserted for each level’s value for that profile. Oracle uses the following values for the level_id column to identify different levels

10001 è Site,

10002 è Application,

10003 è Responsibility,

10004 è User

If we are retrieving the value for a profile using SQL then we must mention the level as well in where clause or else it will go into too many rows exception.

The sql to get the values of a profile for all levels is as below

SELECT   SUBSTR (e.profile_option_name, 1, 35) PROFILE,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Resp',

                 10004, 'User'

                ) LEVEL,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, c.application_short_name,

                 10003, b.responsibility_name,

                 10004, d.user_name

                ) lvalue,

         NVL (a.profile_option_value, 'Is Null') VALUE

    FROM fnd_profile_option_values a,

         fnd_responsibility_tl b,

         fnd_application c,

         fnd_user d,

         fnd_profile_options e

   WHERE e.profile_option_name LIKE '%MO%'

     AND e.profile_option_id = a.profile_option_id

     AND a.level_value = b.responsibility_id(+)

     AND a.level_value = c.application_id(+)

     AND a.level_value = d.user_id(+)

ORDER BY 1, 2

Basics of financial accounting for technical folks

When we are working on Oracle Financials, we need to know basics of financial accounting to interact with business users, understand their requirements and deliver better solution. This knowledge also helps us appreciate the intention behind data model and UI. Below information I've gathered from internet and some I learnt from my CA teammates. Note that this item is very basic and just enough for us to perform our day to day tasks; not a comprehensive document

Accounting - Definition

Accounting may be defined as the art of

recording,

classifying,

summarizing,

analyzing and Interpreting

the financial transactions and communicating the results thereof to the persons interested in such information

Why Accounting

To know

• What business owns?
• What business owes?
• Whether the business has made profit or it is under loss?
• What is the financial position? Can we make investment and expand the business or we need to raise more money by loan?
• Which segment in the business is making profits and where losses are seen?

Recording Financial Transactions

• Also called as Book Keeping
• Enter each and every financial activity in a “Book”
• Enter these data in an orderly, easy to understand manner
• Each entry in this book will be called as “Journal”
• Depending on from where this Journal data has originated, you may give different names, like Sales Journal, Purchase Journal, Contra Journal etc

Classifying Financial Transactions

• Ability to group financial transactions of same nature at one place
• Categorize each journal entry
• Eg: Having separate accounting head for each item
• Eg: Salary Expense, Income from Services, VAT Tax liability etc

Account Types

• 1. Assets:
  • Assets are Tangible and Intangible items of Value that the business owns.
    Eg: Cash
    Cars
    Buildings
    Machinery
    Furniture
    Debtors (money to be received from customers)
    Stock/Inventory
    2. Liabilities:
    Liabilities are those items which are owed by the business to bodies outside of the business.
    Eg: Loans to Banks
    Creditors (Money owed to Suppliers)
    Bank Overdrafts
    Tax to Government
  • 3. Revenue:

    Revenue are the Items from where the business is making money from.

    Eg: Revenue from BPO

    Revenue from Package Implementation

    Revenue from Software Services

    Revenue from Products
  • 4. Expense:

    Expense are the Items where Business is spending money.

    Eg: Salary Expense

    Travel Expense

    Marketing Expense

    Computer Purchase Expense

    Software maintenance expense
  • 5. Owner’s Equity:
    Owner’s equity is the capital invested by the Owner and Profit (or Loss) to date.
    
    
    Accounting Equation
  • Assets – Liabilities = Owner’s Equity

    = Capital + Retained Earning

    = Capital + Revenue – Expense

    Assets + Expense = Capital + Revenue + Liability                                                              

    LHS = RHS

    Debit (Dr) = Credit (Cr)

    
    

    Accounting Rules

    
    
    
  •  

    Account Type	Increasing	Decreasing
    Assets	Dr	Cr
    Liability	Cr	Dr
    Revenue	Cr	Dr
    Expense	Dr	Cr
    Owner's equity	Cr	Dr

  • 
    

 Balance Sheet

• • The Balance sheet shows a snapshot of the Business’s net worth at a given point in time.

    
    
  • 
    

  • 				XYZ Company
    				Balance Sheet
    				31-Mar-11
    Assets					Liabilities
    Current Assets:					Current Liabilities
    	Cash	Rs	50000			Loan Payable	Rs	150000
    	Accounts Receivable	Rs	75000			Accounts Payable	Rs	50000
    	Total Current Assets	Rs	125000		Total Liabilities		Rs	200000
    Fixed Assets:
    	Land	Rs	200000		Owner's Equity
    	Vehicle	Rs	100000			Capital	Rs	150000
    	Total Fixed Assets	Rs	300000			Retained Earning	Rs	75000
    Total Assets		Rs	425000		Total of Capital and Liability		Rs	425000

    Profit and Loss Account
  • P&L statement shows the current financial year’s net operating profits broken down into various sales and expenses
  •  

    	Sales
    		Income from Software services	Rs	100000
    		Income from Products	Rs	150000
    	Total Sales		Rs	250000
    	Expenses
    		Marketing Expense	Rs	50000
    		Salary	Rs	100000
    	Total Expenses		Rs	150000
    	Total Profit/Loss			100000
    		Tax Paid	Rs	25000
    	Profit After Tax		Rs	75000

  • Accounting Methods
  • 1. Cash based Accounting:

    Record the transaction only when the company receives/pays cash. Even if the sale is done today, and company receives the payment after 15 days, transaction will be entered only after 15 days

    Generally followed in small businesses

    
    

    2. Accrual Based Accounting

    Record the transaction as soon as the financial even has happened ie as soon as sale is done, record the income, as soon as purchase is done record expense irrespective of when the company receives/pays money

    
    

    All large organizations follow this accounting method

    This is the norm for all listed companies