Generate random number from DBMS package

select DBMS_RANDOM.value() from dual

Business Events in Oracle APPS

https://blogs.oracle.com/oracleworkflow/entry/defining_business_events_with_synchronous

Metalink Notes for Send Remittance Advice

Separate Remittance Advice (SRA) Email Not Getting Sent (Doc ID 1518482.1)
Unable To Submit Concurrent Request 'Send Separate Remittance Advices' "Payment Instruction ID" has no LOV (Doc ID 1472071.1)
R12: How to Make "Send Separate Remittance Advices" Request Generate an Output (Doc ID 602772.1)
R12 Configure e-Mail Separate Remittance Advice (Doc ID 1528042.1)

Adding new fields in PO Output for Communication

It is not easy to customize PO_COMMUNICATION_PVT.POXMLGEN. If you customize it then you have to customize the workflow which references this procedure and also the Java concurrent program. So, try to avoid customization of PO Output for Communication program. 

To add new fields in PO Output for Communication Program, we have following options:

1. Look if the fields you are trying to add are already available in the XML data generated by the program.
2. If not all the fields are available, try to populate the missing fields in attribute columns so that you can reference those in your template.
3. This is not recommended, but you can try to modify the logic behind the views PO_HEADERS_XML, PO_LINES_XML, PO_DISTRIBUTIONS_XML which fetch the data for this program.

Also, for the ability to use RTF Template you can follow metalink note 378031.1 which gives you the details of a patch to be applied. You can also refer to the metalink note 387670.1 for more info.

SQL Query to check programs assigned to a request set

SELECT rs.user_request_set_name "Request Set"
 , rss.display_sequence Seq
 , cp.user_concurrent_program_name "Concurrent Program"
, e.EXECUTABLE_NAME
 , e.execution_file_name
 , lv.meaning file_type
 ,fat.application_name "Application Name"
-- ,get_appl_name(e.application_id) "Application Name"
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_executables e
 , apps.fnd_lookup_values lv
 , apps.fnd_application_tl fat
WHERE 1=1
--and rs.application_id IN ( 20006 )
AND rs.application_id = rss.set_application_id
AND rs.request_set_id = rss.request_set_id
AND rs.user_request_set_name =-- &amp p_request_set_name
AND e.APPLICATION_ID =FAT.APPLICATION_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 cp.executable_id = e.executable_id
AND cp.executable_application_id = e.application_id
AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lv.lookup_code = e.execution_method_code
and lv.language='US'
and fat.language='US'
AND rs.end_date_active IS NULL
ORDER BY 1,2

Invoice Validation Status showing as "Selected for Validation"

To fix the invoices for which the invoice status showing as "Selected For Validation".

The validation_request_id should be removed on ap_invoices_all when the validation program completes.

ORA-04061: existing state of package body “XXXX” has been invalidated

[WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-04061: existing state of  has been invalidated

ORA-04061: existing state of package body "APPS.XX_CHANGE_REQUEST_PKG" has been invalidated

ORA-04065: not executed, altered or dropped package body "APPS.XXI_CHANGE_REQUEST_PKG"

ORA-06508: PL/SQL: could not find program unit being called: "APPS.XX_CHANGE_REQUEST_PKG"

Wf_Notification.GetAttrClob(2836675, XX_NOTIFICAT' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK=

WF_MAIL.GetLOBMessage3(2836675, PRDMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of  has been invalidated

ORA-04061: existing state of package body "APPS.XX_CHANGE_REQUEST_PKG" has been invalidated

ORA-04065: not executed, altered or dropped package body "APPS.XX_CHANGE_REQUEST_PKG"

ORA-06508: PL/SQL: could not find program unit being called: "APPS.XX_CHANGE_REQUEST_PKG"

Wf_Notification.GetAttrClob(2836675, XX_NOTIFICATION_MESSAGE, text/html)

Wf_Notification.oldGetAttrClob(2836675, XX_NOTIFICATION_MESSAGE, text/html)

WF_NOTIFICATION.GetFullBody(nid => 2836675, disptype => text/html)

WF_MAIL.GetLOBMessage3(nid => 2836675, r_ntf_pref => MAILHTML), Step -> Getting text/html body)

WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 2836675)

WF_XML.Generate(oracle.apps.wf.notification.send, 2836675)

WF_XML.Generate(oracle.apps.wf.notification.send, 2836675)

Wf_Event.setMessage(oracle.apps.wf.notification.send, 2836675, WF_XML.Generate)

Wf_Event.dispatch_internal()

--For the above issue the below solution worked for us.

A bounce of Workflow and Mailer Services

The below reference helped

http://ioraclefusion.wordpress.com/2010/03/22/ora-04061-existing-state-of-package-body-xxxx-has-been-invalidated/

How to update the field value to null using customer update API

Customer APIs Issue: How to Null out an Existing Database Value Using an Update API? (Doc ID 287488.1)

How to pass parameters to the URL in the OAF page

WHen you are calling the new custom page from the standard side menu navigation.The parameters can be passed by using the below code in process request of the controller.
  
  DictionaryData dictionarydata = new DictionaryData();
      dictionarydata.put("ImcPartyId", partyId);
      dictionarydata.put("ImcPartyName", partyName);
      pageContext.setFunctionParameterDataObject(dictionarydata); 

How to bounce apache or oc4j in oracle r12

The following steps need to be followed in the same sequence to bounce Apache or OC4J in R12.

1) login to the apps tier with ‘putty’ or any equivalent software.
2) cd $ADMIN_SCRIPTS_HOME
3) adapcctl.sh stop
4) adoacorectl.sh stop
5) adapcctl.sh start
6) adoacorectl.sh start

sql query to check AP invoice and payments

  SELECT aca.check_number "Document Number",
         asa.segment1 "Supplier Number",
         asa.vendor_name "Supplier Name",
         aca.STATUS_LOOKUP_CODE "Payment Reconcilation Status",
         aca.CLEARED_AMOUNT "Payment Amount",
         aca.CURRENCY_CODE "Currency",
         aca.CLEARED_DATE "Payment Date",
         aia.invoice_num,
         aia.invoice_amount,
         AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
                                              aia.INVOICE_AMOUNT,
                                              aia.PAYMENT_STATUS_FLAG,
                                              aia.INVOICE_TYPE_LOOKUP_CODE)
            "Invoice Acct Status"
    FROM ap_checks_all aca,
         ap_invoice_payments_all aipa,
         ap_invoices_all aia,
         ap_suppliers asa
   WHERE     aca.check_id = aipa.check_id
         AND aipa.invoice_id = aia.invoice_id
         AND aia.vendor_id = asa.vendor_id
         AND aipa.ACCRUAL_POSTED_FLAG = 'N'
         AND POSTED_FLAG = 'N'
         AND TRUNC (aca.CLEARED_DATE) BETWEEN '01-JUN-2013' AND '30-JUN-2013'
ORDER BY check_number;

sql to check Instance clone date

SELECT

resetlogs_time FROM v$database;

Profiles for OAF Personalisation

FND: Personalization Region Link Enabled -->Yes
FND: Personalization Seeding Mode -->Yes
Personalize Self-Service Defn -->Yes

 

Order Management APIs Fail with ORA-20001 in MO_GLOBAL.INIT [ID 973664.1]

Order Management APIs Fail with ORA-20001 in MO_GLOBAL.INIT [ID 973664.1]

How to retrieve data from cs_sr_incidents_v_sec views

unable To Retreive Data From cs_sr_incidents_v_sec And cs_incidents_b_sec [ID 1368799.1]

It is mandatory to set the database session language to American ,if your session language is not American.

select * from NLS_SESSION_PARAMETERS;

The Priority of NLS Parameters Explained (Where To Define NLS Parameters) [ID 241047.1]

NLS_LANG Explained (How does Client-Server Character Conversion Work?) [ID 158577.1]

The correct NLS_LANG in a Microsoft Windows Environment [ID 179133.1]

How to check fmb file version from unix

strings -a XTRINWHL.fmb | grep Header

adident Header XTRINWHL.fmb

API to reset apps login password

DECLARE
   l_Flag   BOOLEAN;
BEGIN
   l_Flag :=
      fnd_user_pkg. ChangePassword (username => 'USERNAME', newpassword => 'PASSWORD');
   IF l_Flag
   THEN
      DBMS_OUTPUT.put_line ('The password has been resetted');
   ELSE
      DBMS_OUTPUT.put_line ('The password has not been resetted');
   END IF;
END;

Issues in Send Remittance Advice in R12

Ensure the following setup is complete.
1. Set the IBY: XML Publisher Delivery Manager Configuration File system profile with the directory where the xdodelivery.cfg file is located.
2. Verify both of the following profiles are set and are correct:

IEX: SMTP From
IEX: SMTP Host

3. In the Application Developer responsibility
- Navigate to / Application / Messages
- Query up IBY_FD_SRA_EMAIL_FROM
- Change the email in the "Current Message Text" to your desired e-mail address
4. Retest the issue.

Common Errors in Send Remittance Advice:

oracle.apps.xdo.delivery.DeliveryException: Error occurred while creating to the temporary file : 

Reason for the above Error:Some issues in the xdodelivery.cfg file.

• Check the temp file directory path
• Host detais.

standard oaf pages in the server

1) class files at $JAVA_TOP


2) pages in mds directory.

ex: /oracle/apps/ar/custstd/cust/CustPG.xml will be found at $AR_TOP/mds/custstd/cust/custpg.xml.

Customer and its bank relation

select hp.party_name customer_name,hp.party_name,


ieb.bank_name,ieba.bank_account_num,ieba.branch_id

--,ieba.*

from hz_parties hp,

IBY_ACCOUNT_OWNERS iao,

IBY_EXT_BANK_ACCOUNTS ieba,

IBY_EXT_BANKS_V ieb

--, hz_parties branch

where hp.party_id =22052

and iao.account_owner_party_id = hp.party_id --party_id of customer

and iao.ext_bank_account_id = ieba.ext_bank_account_id

and ieb.bank_party_id = ieba.bank_id;