Friday 27 September 2013

ORA-02291: Integrity Constraint (GMI.IC_LOTS_MST_IC_ITEM_MST_B_FK) Violated

This error because of Trigger in IC_ITEM_MST (OPM Item Master). Whenever item created in OPM it will creates a item in Discrete Inventory side(MTL_System_Items_B).


Please recompile Flexfields.
Navigate to Oracle Inventory > Setup > Flexfields > Key > Segments.
Query on Application 'Inventory' and flexfield title ' System Items'.
To recompile the flexfield

Friday 13 September 2013

APP-FND-00​500: AFPPRN Received a Return Code of Failure from Routine FDUPRN. Program Exited With Status 64

If the Payment instruction contains Payments from other Operating Units and 
if MOAC security is enabled, then we see this error will come. 

Check the below setups.

a.) check the profile 'MO: Security Profile' at user level or responsibility level.

b.)Navigate to HRMS Super User Responsibility > Security > Define Security Profile. 
Search for the profile '%%Value set in Step a %%'

Check the Security Type, It should be 'Secure organizations by organization hierarchy and/or 
organization list' in the organizations list, 
please ensure whether the right Operating unit is added. (as assigned to your responsibility).

After this, run 'Security List Maintenance request' concurrent program with the parameters: 

- Generate lists for = One Named Security Profile 
- Security Profile = 'Value set in Step a'

2. Ensure that the profile option 'Sequential Numbering' is set to 'Not Used'.

3. Navigate to 'Concurrent >> Program' 
Search for 'Format Payment Instruction Program'. 
Uncheck 'print' option. Save. 
Now run the concurrent program again.

Monday 9 September 2013

How to Change the Output File Name Created by Payment Process Request

R12 after finishing Payment Process Request,Format Payment Instructions with Text Output request generates out put in particular directory depends on the below set up.
Default name of file is Request id.file extension

Oracle Payments Setup > Payment Process Profiles >


So the general requirement will be rename the file with sysdate, R11 i we can attach a custom program to payment program which will rename the file with sysdate for that payment request,Where as in R12 we don't have option to attach any custom program to do that.
By using the below script we can rename with sysdate.(Scheduled the same in UNIX)
#!/bin/sh
# Created: Anil Maguluri
# To rename the UK Format Payment Instructions with Sysdate
#-------------------------------------------------------------------
{
echo Format Payment Instructions
for file in UK*.xml
do
newfile=$(date "+%d%b%Y_$file" -d "$(stat -c "%y" $file)")
mv "$file" "$newfile"
done
echo rename the file
} > UK_install.log 2>&1

Sunday 8 September 2013

Dependent value sets in discoverer

This can be done only in Oracle Discover Plus 10g.

Go to parameter screen,on the right bottom of the form you have an Option "Filter the List of Values based on the selection conditions". Specify the parameters based on which the value need to be filtered.


 

Friday 6 September 2013

Oracle Forms Development steps


1.      Set New Environment Variable for FORMS60_PATH
My Computer à PropertiesàAdvanced
Click on Environmental Variables  à Goto System variables
Click on New

Enter   Variable name: FORMS60_PATH
            Variable value: your local resource library path

Close and open the forms builder.

2.      Now copy the .plls from /ora_home/au/11.5.0/resource/
1.      FNDSQF
2.      JE
3.      JL
4.      GLOBE
5.      APPCORE2
6.      HRKPI
7.      PSB
8.      PSA
9.      IGI_CBC
10.  IGI_CC
11.  IGI_DOS
12.  IGI_EXP
13.  IGI_IAC
14.  IGI_MHC
15.  IGI_SIA
16.  IGI_STP
17.  GMS
18.  FV
19.  APPDAYPK
20.  APPFLDR
21.  GHR
22.  IGILUTIL2
23.  IGILUTIL
24.  JA
25.  APPCORE
26.  VERT
27.  PQH_GEN
28.  PSAC
29.  CUSTOM
30.  OPM
31.  VERT1
32.  VERT2
33.  VERT3
34.  VERT4
35.  VERT5
3.      Now copy the APPSTAND.fmb & TEMPLATE.fmb into your local directory
4.      Open the Oracle Forms Builder and try to open the libraries in the same order as above
5.      Open the APPSTAND.fmb or TEMPLATE.fmb they will be opened.
6.      Now for TEMPLATE.fmb Save as.. your required form name according to naming conversions. And change the name in FORM property palette.
7.      Open the Windows navigator and delete the BLOCKNAME window.
8.      Open the Canvases navigator and delete the BLOCKNAME canvas.
9.      Open the Data Blocks navigator and delete the BLOCKNAME data block.
10.  At data block navigator click + sign in tool palette proceed to create a data block
11.  Similarly use wizard to prepare layout as required
12.  Enter the details in PRE-FORM (Form level) trigger, for author etc., enter the first block name, and window name
13.  Enter the FORM NAME in the WHEN-NEW-FORM-INSTANCE (Form Level) trigger.
14.  Create new windows as required, and attach the window to canvas and vice versa.
15.  Include Subclass Information for all newly created things in their respective Property palette.
16.  At Program Unit: APP_CUSTOM (Package Body) enter <your first window> and <it child window> and <yet its child window> this will help to create relationship between windows viz. master-child.
17.  Save all the changes and SFTP to /ora_home/au/11.5.0/forms.
18.  Open telnet...IP address <username>, <password> cd to /ora_home/au/11.5.0/forms
19.  type the following command:

f60genm Module=<formname> Userid=<userid/password> [Parameters].
Optional parameters are (default values given):

Ex: f60genm Module=employee.fmb Userid=apps/apps Module_Type=FORM Output_File=/ora_home/cutom_folder/11.5.0/forms/US/employee.fmx

20.  The server shows all the compiled details with errors if any.
21.  Register the form as usual.

Note: To open any Oracle Standard Form copy into local system and Try to open it show what are all the libraries it needs to load, write them in a notepad and copy them from the server /ora_home/au/resource path and put in the default local resource library path

How to enable record history in Oracle custom form

Step1 :

The below is the Oracle  custom form, When we click on record history getting error as record history is not available here


Step 2 :

Alter the custom table by adding who columns.

alter table
      XXXXXXXXXXXXXXXXXX
add   (CREATION_DATE        DATE,
       CREATED_BY           NUMBER,
       LAST_UPDATE_LOGIN    NUMBER,
       LAST_UPDATED_BY      NUMBER,
       LAST_UPDATE_DATE     DATE
       )

Step 3 : Go to the forms builder and select the FMB.



             Then click on Data Block and Refresh  

Step 4 :  Select the new added Who columns
               

 Step 5 : 

Select PRE-INSET and PRE-UPDATE triggers and then  add

FND_STANDARD.SET_WHO;

Setp 6 :

 Compile the FMX and here you go :)




         

            
              

 

How to make a Oracle form Query Only


1. Login into the System Administrator/Application Developer responsibility.
2. Navigate to 'Security -> Responsibility -> Define'
3. Query for the responsibility for which you want to make the form as 'Query Only' and copy the 'Menu' name.
4. Navigate to 'Application -> Menu' and query with the menu name which we got in step 3 in 'User Menu Name'
5. Now look out for the form (Prompt) you want to make it as query only. Copy the value in the 'Function' field w.r.t the form that needs to be made as 'Query Only'.
6. Navigate to 'Application -> Funtion' and query with the function name we got in Step 5 in 'User Function Name' field.
7. Identify the correct Function for your form and then naviagate to the 'Form' tab.
8. Enter the value: QUERY_ONLY=YES

Thursday 5 September 2013

Customer Profile Class API in Oracle Apps (hz_customer_profile_v2pub.update_customer_profile)

BEGIN
   p_customer_profile_rec_type.cust_account_profile_id := 19273;
   --p_customer_profile_rec_type.credit_rating := 'Excellent';
   p_customer_profile_rec_type.PROFILE_CLASS_ID :=1085;
   p_object_version_number := 3;
  
   hz_customer_profile_v2pub.
   update_customer_profile (
      p_init_msg_list           => 'T',
      p_customer_profile_rec    => p_customer_profile_rec_type,
      p_object_version_number   => p_object_version_number,
      x_return_status           => x_return_status,
      x_msg_count               => x_msg_count,
      x_msg_data                => x_msg_data);
   FND_FILE. put_line (fnd_file.output,'x_return_status = ' || SUBSTR (x_return_status, 1, 255));
   FND_FILE. put_line (fnd_file.output,'Object Version Number = ' || TO_CHAR (p_object_version_number));
   FND_FILE. put_line (fnd_file.output,'Credit Rating = ' || p_customer_profile_rec_type.credit_rating);
   FND_FILE. put_line (fnd_file.output,'x_msg_count = ' || TO_CHAR (x_msg_count));
   FND_FILE. put_line (fnd_file.output,'x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
   IF x_msg_count > 1
   THEN
      FOR I IN 1 .. x_msg_count
      LOOP
         FND_FILE. put_line(fnd_file.output,SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
      END LOOP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      FND_FILE.put_line(fnd_file.output,'Error: ' || SQLERRM);
END;

This record in table hz_customer_profiles cannot be locked as it has been updated by another user.

The current Object Version Number did not assign to p_object_version_number value before calling public API.

HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE API

Please add the following in script before calling API

SELECT object_version_number
INTO in_out_object_version_number
FROM hz_customer_profiles
WHERE cust_account_profile_id = &cust_account_profile_id;