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