XML Bursting in Oracle Apps R12

XML Bursting in Oracle Apps R12:

It is used to deliver report to multiple emails, let it be your pay slips or PO’s or others.

1. Create rdf data model.
2. Register Executable.
3. Register Concurrent Program
4. Setup the temporary Working directory.
5. XML Bursting control File.
6. Create the rtf template.
7. Register Data definition.
8. Upload XML control file to data definition.
9. Register data template.
10. Move rdf file to registered TOP.
11. Move rtf file to tmp directory.
12. Attach the concurrent program to request group.
13. Attach another XML Publisher Report Bursting Program concurrent program to same request group.
14. Run the report.
15. Errors and solutions

1. Create rdf data model.

Query:

select full_name, person_id,email_address,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE

from per_people_x ppx

where email_address is not null

and employee_number in ('9213','9214','9215')

and business_group_id = :p_business_group_id

and rownum < 5

function AfterReport return boolean is

 req_id number;

begin

            req_id := FND_REQUEST.SUBMIT_REQUEST(

                application =>'XDO',

             program     =>'XDOBURSTREP',

             description =>null,

             start_time  =>null,

             sub_request =>FALSE,

             argument1   =>'Y',

             argument2   =>:P_CONC_REQUEST_ID,

             argument3     =>'Y');

   

 if req_id = 0 then

   srw.message(100,'Failed to submit bursting program');

 end if;                                        

 SRW.user_exit('FND SRWEXIT');

 return (TRUE);

end;

2. Register Executable

3. Register Concurrent

4. 

5.

6.

7.

8.

9.

10.

11.

12, 13.

14.

Errors and Solution:

If the xml report run is normal and Bursting concurrent Program ends with Error then check the patch number Patch 9501440:R12.XDO.B is applied or not.

 

SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%9501440%'

 

Reference: Doc ID 1276845.

If both programs ends normal, but no email then need to check with the network and database team regarding server and port which we configured in the xml control file.

User hook Create, Update and Delete api scripts.

1.
Finding user hook for SIT before insert:

SELECT ahk.api_hook_id,
ahk.api_module_id,
ahk.hook_package,
ahk.hook_procedure
FROM hr_api_hooks ahk, hr_api_modules ahm
WHERE ahm.module_name like '%CREATE_SIT%'
AND ahm.api_module_type = 'BP'
AND ahk.api_hook_type = 'AP'
AND ahk.api_module_id = ahm.api_module_id

API_HOOK_ID = 2879
API_MODULE_ID = 1279

2.  Create User hook:

DECLARE
L_API_HOOK_ID NUMBER:= 2879;
L_API_HOOK_CALL_ID NUMBER;
L_OBJECT_VERSION_NUMBER NUMBER;
L_SEQUENCE NUMBER;
BEGIN
SELECT HR_API_HOOKS_S.NEXTVAL
INTO L_SEQUENCE
FROM DUAL;
HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL
(P_VALIDATE => FALSE,
P_EFFECTIVE_DATE => TO_DATE('01-JAN-1952','DD-MON-YYYY'),
P_API_HOOK_ID =>L_API_HOOK_ID ,
P_API_HOOK_CALL_TYPE => 'PP',
P_SEQUENCE => L_SEQUENCE,
P_ENABLED_FLAG => 'Y',
P_CALL_PACKAGE => 'XXFUJ_CREATE_SIT_A_UHPKG',
P_CALL_PROCEDURE => 'XXFUJ_CREATE_SIT_LEV_VAL',
P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID,
P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.PUT_LINE('L_API_HOOK_CALL_ID '|| L_API_HOOK_CALL_ID);
END ;

3. COMMIT

SELECT API_HOOK_CALL_ID,API_HOOK_ID,
OBJECT_VERSION_NUMBER,
CALL_PACKAGE,
CALL_PROCEDURE,
ENABLED_FLAG,
STATUS
FROM HR_API_HOOK_CALLS
--WHERE api_hook_id = 2878
--WHERE API_HOOK_CALL_ID = 1263
WHERE CALL_PACKAGE LIKE '%FUJ%'

commit

4. runnig the script
declare
l_api_module_id number := 1279; --Value 1731 is derived from Step 1 above using following query
begin
hr_api_user_hooks_utility.create_hooks_one_module (l_api_module_id);
dbms_output.put_line('Success');
exception when others then
dbms_output.put_line('Exception : '||SQLERRM);
end;

5. COMMIT

6. to delete the user hook:

BEGIN
Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,
p_api_hook_call_id => 1274,
p_object_version_number =>2
);
DBMS_OUTPUT.PUT_LINE('deleted Successfully');
END;

7. COMMIT

8. to update the user hook:

declare
p_object_version_number number;
BEGIN
Hr_Api_Hook_Call_Api.update_api_hook_call ( p_validate  =>TRUE,
   p_effective_date               => to_date('01-jan-2004'),
   p_api_hook_call_id             =>1270,
   --p_sequence                     in     number    default hr_api.g_number,
   --p_enabled_flag                 in     varchar2  default hr_api.g_varchar2,
   --p_call_package                 in     varchar2  default hr_api.g_varchar2,
   --p_call_procedure               in     varchar2  default hr_api.g_varchar2,
   p_object_version_number      =>p_object_version_number
);
DBMS_OUTPUT.PUT_LINE('updated Successfully');
END;

9. commit

SELECT ANALYSIS_CRITERIA_ID,
ID_FLEX_NUM,
SEGMENT1 Allowance_Name,
SEGMENT2 EFFECTIVE_DATE,
SEGMENT11 AMOUNT
  FROM
PER_ANALYSIS_CRITERIA
WHERE ANALYSIS_CRITERIA_ID = 4949604

SELECT ELEMENT_NAME, ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F_TL
WHERE LANGUAGE = USERENV('LANG')
AND ELEMENT_TYPE_ID IN (593,559,562,561,564,590,589,597)

User hook on Special Info to attach element to Assignment entries screen

User hook on Special Info to attach element to Assignment entries screen:

1. Requirement once the employee applies for Allowance and get final approved , Once data entered into HR tables, then the allowance should be attached auto to Assignment Entries screen.

2. Solution using user hooks on SIT.

Step1: Creating Procedure for Creating Element Entries.

Step2: Creating Package for user hook.

Step3: Registering user hook.

Step1:

--FOR USER HOOK IN SIT Allowances

PROCEDURE CREATE_ELEMENT_ENTRY_UH_P

(P_BUSINESS_GROUP_ID IN NUMBER,

P_PERSON_ID IN NUMBER,

P_ANALYSIS_CRITERIA_ID IN NUMBER,

P_SEGMENT1 VARCHAR2, --ELEMENT_TYPE_ID

P_SEGMENT2 VARCHAR2, --EFFECTIVE DATE

P_SEGMENT11 VARCHAR2 --AMOUNT

--P_SEGMENT1 VARCHAR2

)

IS

/*DECLARE

P_BUSINESS_GROUP_ID NUMBER := 81;

--EVENT_TYPE VARCHAR2(20) := 'insert';

P_PERSON_ID NUMBER := 26876;

--P_ABSENCE_ATTENDANCE_ID NUMBER := 3033909;

P_SEGMENT1 VARCHAR2(20) := 589; --ELEMENT_TYPE_ID

P_SEGMENT2 VARCHAR2(20) := '2015/12/10 00:00:00';--EFFECTIVE DATE

P_SEGMENT11 VARCHAR2(20) := 100; --AMOUNT

*/

l_effective_start_date DATE := NULL;

l_effective_end_date DATE := NULL;

l_object_version_number NUMBER := NULL;

l_element_entry_id NUMBER := NULL;

l_create_warning BOOLEAN;

l_err_msg VARCHAR2 (500) := NULL;

L_ASS_ID NUMBER;

L_PAYROLL_ID NUMBER;

L_ELEMENT_LINK_ID NUMBER;

l_date_start DATE;

l_date_end DATE;

L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;

L_INPUT_VALUE_ID NUMBER;

l_reason NUMBER;

p_value6 VARCHAR2(1);

p_value7 VARCHAR2(1);

reqid number;

BEGIN

-----------TO GET ASSIGNMENT_ID, PAYROLL_ID

BEGIN

SELECT DISTINCT ASSIGNMENT_ID, PAYROLL_ID

INTO L_ASS_ID, L_PAYROLL_ID

FROM PER_ALL_ASSIGNMENTS_F X

WHERE X.PERSON_ID= P_PERSON_ID

AND X.PRIMARY_FLAG='Y'

AND TO_DATE(SUBSTR(P_SEGMENT2, 1, 10), 'YYYY/MM/DD') BETWEEN X.EFFECTIVE_START_DATE AND X.EFFECTIVE_END_DATE

;

END;

--gettting the Element_link_id of the person

--GET ELEMENT LINK

BEGIN

SELECT DISTINCT F.ELEMENT_LINK_ID,PIV.INPUT_VALUE_ID

INTO L_ELEMENT_LINK_ID, L_INPUT_VALUE_ID

FROM

PAY_ELEMENT_LINKS_F F,

PAY_ELEMENT_TYPES_F P,

PAY_INPUT_VALUES_F PIV

WHERE

TRUNC(SYSDATE) BETWEEN F.EFFECTIVE_START_DATE AND F.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE

AND F.ELEMENT_TYPE_ID=P.ELEMENT_TYPE_ID

AND F.PAYROLL_ID = L_PAYROLL_ID

AND P.ELEMENT_TYPE_ID = P_SEGMENT1

AND F.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID

AND PIV.NAME = 'Override Amount';

END;

BEGIN

pay_element_entry_api.create_element_entry (

p_validate => FALSE,

p_effective_date => trunc(TO_DATE(SUBSTR(P_SEGMENT2, 1, 10), 'YYYY/MM/DD'),'mm'),--P_SEGMENT2

p_business_group_id => P_BUSINESS_GROUP_ID,

p_assignment_id => L_ASS_ID,

p_element_link_id => L_ELEMENT_LINK_ID,

p_entry_type => 'E',

p_input_value_id2 => L_INPUT_VALUE_ID, ---Override Amount

p_entry_value2 => to_number(P_SEGMENT11),--Amount

p_effective_start_date => l_effective_start_date,

p_effective_end_date => l_effective_end_date,

p_element_entry_id => l_element_entry_id,

p_object_version_number => l_object_version_number,

p_create_warning => l_create_warning

);

COMMIT;

DBMS_OUTPUT.put_line (

'Element has been uploaded: ' || l_element_entry_id

);

EXCEPTION

WHEN OTHERS

THEN

l_err_msg := SQLERRM;

DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

END;

Step2:

CREATE OR REPLACE PACKAGE APPS.XXFUJ_CREATE_SIT_A_UHPKG IS

--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A

PROCEDURE XXFUJ_CREATE_SIT_LEV_VAL

--THIS WILL VALIDATE THE SIT WITH REFERENCE TO THE LEAVE EXISTS.

/*

Example: If a emp is on Annual leave from 01-jan-2017 to 30-jan-2017

now we are entering in SIT (Training) 10-jan-2017 to 15-jan-2017

As training days falls between the annual leave days, then the user entering the SIT will be notified by warning message.

*/

(P_PERSON_ID in NUMBER

,P_BUSINESS_GROUP_ID in NUMBER

,P_ID_FLEX_NUM in NUMBER

,P_EFFECTIVE_DATE in DATE

,P_COMMENTS in VARCHAR2

,P_DATE_FROM in DATE

,P_DATE_TO in DATE

,P_REQUEST_ID in NUMBER

,P_PROGRAM_APPLICATION_ID in NUMBER

,P_PROGRAM_ID in NUMBER

,P_PROGRAM_UPDATE_DATE in DATE

,P_ATTRIBUTE_CATEGORY in VARCHAR2

,P_ATTRIBUTE1 in VARCHAR2

,P_ATTRIBUTE2 in VARCHAR2

,P_ATTRIBUTE3 in VARCHAR2

,P_ATTRIBUTE4 in VARCHAR2

,P_ATTRIBUTE5 in VARCHAR2

,P_ATTRIBUTE6 in VARCHAR2

,P_ATTRIBUTE7 in VARCHAR2

,P_ATTRIBUTE8 in VARCHAR2

,P_ATTRIBUTE9 in VARCHAR2

,P_ATTRIBUTE10 in VARCHAR2

,P_ATTRIBUTE11 in VARCHAR2

,P_ATTRIBUTE12 in VARCHAR2

,P_ATTRIBUTE13 in VARCHAR2

,P_ATTRIBUTE14 in VARCHAR2

,P_ATTRIBUTE15 in VARCHAR2

,P_ATTRIBUTE16 in VARCHAR2

,P_ATTRIBUTE17 in VARCHAR2

,P_ATTRIBUTE18 in VARCHAR2

,P_ATTRIBUTE19 in VARCHAR2

,P_ATTRIBUTE20 in VARCHAR2

,P_SEGMENT1 in VARCHAR2

,P_SEGMENT2 in VARCHAR2

,P_SEGMENT3 in VARCHAR2

,P_SEGMENT4 in VARCHAR2

,P_SEGMENT5 in VARCHAR2

,P_SEGMENT6 in VARCHAR2

,P_SEGMENT7 in VARCHAR2

,P_SEGMENT8 in VARCHAR2

,P_SEGMENT9 in VARCHAR2

,P_SEGMENT10 in VARCHAR2

,P_SEGMENT11 in VARCHAR2

,P_SEGMENT12 in VARCHAR2

,P_SEGMENT13 in VARCHAR2

,P_SEGMENT14 in VARCHAR2

,P_SEGMENT15 in VARCHAR2

,P_SEGMENT16 in VARCHAR2

,P_SEGMENT17 in VARCHAR2

,P_SEGMENT18 in VARCHAR2

,P_SEGMENT19 in VARCHAR2

,P_SEGMENT20 in VARCHAR2

,P_SEGMENT21 in VARCHAR2

,P_SEGMENT22 in VARCHAR2

,P_SEGMENT23 in VARCHAR2

,P_SEGMENT24 in VARCHAR2

,P_SEGMENT25 in VARCHAR2

,P_SEGMENT26 in VARCHAR2

,P_SEGMENT27 in VARCHAR2

,P_SEGMENT28 in VARCHAR2

,P_SEGMENT29 in VARCHAR2

,P_SEGMENT30 in VARCHAR2

,P_CONCAT_SEGMENTS in VARCHAR2

,P_ANALYSIS_CRITERIA_ID in NUMBER

,P_PERSON_ANALYSIS_ID in NUMBER

,P_PEA_OBJECT_VERSION_NUMBER in NUMBER);

END;

/

CREATE OR REPLACE PACKAGE BODY XXFUJ_CREATE_SIT_A_UHPKG IS

--USER HOOK PACKAGE FOR HR_SIT_BK1.CREATE_SIT_A

PROCEDURE XXFUJ_CREATE_SIT_LEV_VAL

--THIS WILL VALIDATE THE SIT WITH REFERENCE TO THE LEAVE EXISTS.

/*

Example: If a emp is on Annual leave from 01-jan-2017 to 30-jan-2017

now we are entering in SIT (Training) 10-jan-2017 to 15-jan-2017

As training days falls between the annual leave days, then the user entering the SIT will be notified by warning message.

*/

(P_PERSON_ID in NUMBER

,P_BUSINESS_GROUP_ID in NUMBER

,P_ID_FLEX_NUM in NUMBER

,P_EFFECTIVE_DATE in DATE

,P_COMMENTS in VARCHAR2

,P_DATE_FROM in DATE

,P_DATE_TO in DATE

,P_REQUEST_ID in NUMBER

,P_PROGRAM_APPLICATION_ID in NUMBER

,P_PROGRAM_ID in NUMBER

,P_PROGRAM_UPDATE_DATE in DATE

,P_ATTRIBUTE_CATEGORY in VARCHAR2

,P_ATTRIBUTE1 in VARCHAR2

,P_ATTRIBUTE2 in VARCHAR2

,P_ATTRIBUTE3 in VARCHAR2

,P_ATTRIBUTE4 in VARCHAR2

,P_ATTRIBUTE5 in VARCHAR2

,P_ATTRIBUTE6 in VARCHAR2

,P_ATTRIBUTE7 in VARCHAR2

,P_ATTRIBUTE8 in VARCHAR2

,P_ATTRIBUTE9 in VARCHAR2

,P_ATTRIBUTE10 in VARCHAR2

,P_ATTRIBUTE11 in VARCHAR2

,P_ATTRIBUTE12 in VARCHAR2

,P_ATTRIBUTE13 in VARCHAR2

,P_ATTRIBUTE14 in VARCHAR2

,P_ATTRIBUTE15 in VARCHAR2

,P_ATTRIBUTE16 in VARCHAR2

,P_ATTRIBUTE17 in VARCHAR2

,P_ATTRIBUTE18 in VARCHAR2

,P_ATTRIBUTE19 in VARCHAR2

,P_ATTRIBUTE20 in VARCHAR2

,P_SEGMENT1 in VARCHAR2

,P_SEGMENT2 in VARCHAR2

,P_SEGMENT3 in VARCHAR2

,P_SEGMENT4 in VARCHAR2

,P_SEGMENT5 in VARCHAR2

,P_SEGMENT6 in VARCHAR2

,P_SEGMENT7 in VARCHAR2

,P_SEGMENT8 in VARCHAR2

,P_SEGMENT9 in VARCHAR2

,P_SEGMENT10 in VARCHAR2

,P_SEGMENT11 in VARCHAR2

,P_SEGMENT12 in VARCHAR2

,P_SEGMENT13 in VARCHAR2

,P_SEGMENT14 in VARCHAR2

,P_SEGMENT15 in VARCHAR2

,P_SEGMENT16 in VARCHAR2

,P_SEGMENT17 in VARCHAR2

,P_SEGMENT18 in VARCHAR2

,P_SEGMENT19 in VARCHAR2

,P_SEGMENT20 in VARCHAR2

,P_SEGMENT21 in VARCHAR2

,P_SEGMENT22 in VARCHAR2

,P_SEGMENT23 in VARCHAR2

,P_SEGMENT24 in VARCHAR2

,P_SEGMENT25 in VARCHAR2

,P_SEGMENT26 in VARCHAR2

,P_SEGMENT27 in VARCHAR2

,P_SEGMENT28 in VARCHAR2

,P_SEGMENT29 in VARCHAR2

,P_SEGMENT30 in VARCHAR2

,P_CONCAT_SEGMENTS in VARCHAR2

,P_ANALYSIS_CRITERIA_ID in NUMBER

,P_PERSON_ANALYSIS_ID in NUMBER

,P_PEA_OBJECT_VERSION_NUMBER in NUMBER)

IS

L_ANL VARCHAR2(1);

BEGIN

BEGIN

IF P_ID_FLEX_NUM = 50453 --Allowance Request

THEN

XX_SIT_EVENTS.CREATE_ELEMENT_ENTRY_UH_P(P_BUSINESS_GROUP_ID, P_PERSON_ID, P_ANALYSIS_CRITERIA_ID, P_SEGMENT1, P_SEGMENT2, P_SEGMENT11);

--HR_UTILITY.SET_MESSAGE (800, 'LSG_ANN_LEAVE_GREATER_THAN_30');

--HR_UTILITY.set_warning;

--RAISE_ERROR;

END IF;

EXCEPTION WHEN OTHERS THEN NULL;

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

EXCEPTION WHEN OTHERS THEN NULL;

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

END;

Step3:

Register user hook.

XXFUJ_CREATE_SIT_A_UHPKG.XXFUJ_CREATE_SIT_LEV_VAL

XX_SIT_EVENTS.CREATE_ELEMENT_ENTRY_UH_P

1.

SELECT ahk.api_hook_id,

ahk.api_module_id,

ahk.hook_package,

ahk.hook_procedure

FROM hr_api_hooks ahk, hr_api_modules ahm

WHERE ahm.module_name like '%CREATE_SIT%'

AND ahm.api_module_type = 'BP'

AND ahk.api_hook_type = 'AP'

AND ahk.api_module_id = ahm.api_module_id

API_HOOK_ID = 2879

API_MODULE_ID = 1279

2.

DECLARE

L_API_HOOK_ID NUMBER:= 2879;

L_API_HOOK_CALL_ID NUMBER;

L_OBJECT_VERSION_NUMBER NUMBER;

L_SEQUENCE NUMBER;

BEGIN

SELECT HR_API_HOOKS_S.NEXTVAL

INTO L_SEQUENCE

FROM DUAL;

HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL

(P_VALIDATE => FALSE,

P_EFFECTIVE_DATE => TO_DATE('01-JAN-1952','DD-MON-YYYY'),

P_API_HOOK_ID =>L_API_HOOK_ID ,

P_API_HOOK_CALL_TYPE => 'PP',

P_SEQUENCE => L_SEQUENCE,

P_ENABLED_FLAG => 'Y',

P_CALL_PACKAGE => 'XXFUJ_CREATE_SIT_A_UHPKG',

P_CALL_PROCEDURE => 'XXFUJ_CREATE_SIT_LEV_VAL',

P_API_HOOK_CALL_ID => L_API_HOOK_CALL_ID,

P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);

DBMS_OUTPUT.PUT_LINE('L_API_HOOK_CALL_ID '|| L_API_HOOK_CALL_ID);

END ;

COMMIT

SELECT API_HOOK_CALL_ID,API_HOOK_ID,

OBJECT_VERSION_NUMBER,

CALL_PACKAGE,

CALL_PROCEDURE,

ENABLED_FLAG,

STATUS

FROM HR_API_HOOK_CALLS

--WHERE api_hook_id = 2878

--WHERE API_HOOK_CALL_ID = 1263

WHERE CALL_PACKAGE LIKE '%FUJ%'

commit

3.

declare

l_api_module_id number := 1279; --Value 1731 is derived from Step 1 above using following query

begin

hr_api_user_hooks_utility.create_hooks_one_module (l_api_module_id);

dbms_output.put_line('Success');

exception when others then

dbms_output.put_line('Exception : '||SQLERRM);

end;

COMMIT

To delete an user hook:

BEGIN

Hr_Api_Hook_Call_Api.delete_api_hook_call ( p_validate => FALSE,

p_api_hook_call_id => 1274,

p_object_version_number =>2

);

DBMS_OUTPUT.PUT_LINE('deleted Successfully');

END;

COMMIT

To Update an user hook:

declare

p_object_version_number number;

BEGIN

Hr_Api_Hook_Call_Api.update_api_hook_call ( p_validate =>TRUE,

p_effective_date => to_date('01-jan-2004'),

p_api_hook_call_id =>1270,

--p_sequence in number default hr_api.g_number,

--p_enabled_flag in varchar2 default hr_api.g_varchar2,

--p_call_package in varchar2 default hr_api.g_varchar2,

--p_call_procedure in varchar2 default hr_api.g_varchar2,

p_object_version_number =>p_object_version_number

);

DBMS_OUTPUT.PUT_LINE('updated Successfully');

END;

SELECT ANALYSIS_CRITERIA_ID,

ID_FLEX_NUM,

SEGMENT1 Allowance_Name,

SEGMENT2 EFFECTIVE_DATE,

SEGMENT11 AMOUNT

FROM

PER_ANALYSIS_CRITERIA

WHERE ANALYSIS_CRITERIA_ID = 4949604

SELECT ELEMENT_NAME, ELEMENT_TYPE_ID

FROM PAY_ELEMENT_TYPES_F_TL

WHERE LANGUAGE = USERENV('LANG')

AND ELEMENT_TYPE_ID IN (593,559,562,561,564,590,589,597)

Errors and Solution:

1. Don’t forget to commit the transactions in the Step3.

2. If you get the error in the HR as “ Error: run the script $PER_TOP/admin/sql/hrahkpar.sql”

Solution: Delete the user hook , commit, create again using Step3.

3. If updated the package, procedure delete again the user hook and recreate again.

4. No need to bounce the apache or middle just run the Step3 of 3rd point.

13.2 OAF Update Page Creation From Data Entry Page

To Create Update Page from the Existing Data Entry Page:
1. Close the Jdeveloper.
2. Copy the D:\JDev_Home\jdevhome\jdev\myprojects\xxffrd\oracle\apps\xxcust\ffrd\webui\EntryPG.xml
3. Paste in the same folder will show as ‘EntryPG– Copy.xml’
4. Change the name to UpdatePG.xml
5. Open the jdev and you can see the Upage Page.
6. Now open the EntryPGCO (Controller of EntryPG) in jdev, click save as ‘UpdagePGCO’
7. Open the UpdagePGCO and change the name of the Class from EntryPGCO to UpdatePGCO.
8. Change the Controller attached to region of UpdatePG from EntyPGCO to UpdagePGCO.
9. Its done and you got your update Page. 

28. OAF Working with Attachments.

2(I)
2.1 Create Item with
Style    attachmentTable
Instance InsertRecordsVO1.
Text     Attachments
Render Search Region False
2.2 Give EntityMap properties Entity as XX_STRILING_MAP
2.3 Right click the EntityMap and click new PrimaryKey, Give Attribute (primary Key of your VO)
2.4 no need of categoryMap.
2(II)
2.1 Create Item with Style attachmentLink , give Instance as InsertRecordsVO1.
2.2 Right click the EntityMap and click new PrimaryKey, Give Attribute (primary Key of your VO)
2.3 Right click the EntityMap and click new categoryMap, In properties under Attachments give Category as MISC

27. OAF Showing image from the database

3.1 in the VO add new column   "Null Image_Source" below created VO from EO.
SELECT StirlingPatientEntryEO.PATIENT_ID,
       StirlingPatientEntryEO.PATIENT_NAME,
       StirlingPatientEntryEO.REVIEW,
       StirlingPatientEntryEO.PHONE_NUMBER,
       StirlingPatientEntryEO.SEX,
       StirlingPatientEntryEO.AGE,
       StirlingPatientEntryEO.AMOUNT,
       StirlingPatientEntryEO.DATE_CHECKED,
       StirlingPatientEntryEO.ADDRESS,
       StirlingPatientEntryEO.LAST_UPDATE_DATE,
       StirlingPatientEntryEO.LAST_UPDATED_BY,
       StirlingPatientEntryEO.CREATION_DATE,
       StirlingPatientEntryEO.CREATED_BY,
       StirlingPatientEntryEO.LAST_UPDATE_LOGIN,
       StirlingPatientEntryEO.IMAGE,
       Null Image_Source
FROM XXSTIRLING_PATIENT_ENTRY_TABLE StirlingPatientEntryEO
3.2 Created UpdatePG with the above created VO
Check how to create UpdatePG in OAF in this Blog
3.3 for the Attribute StirlingPatientEntryEO.IMAGE below are the properties.
ID imageIT
style image
datatype CLOB
ViewInstance PatientEntryVO1
ViewAtribute Image
3.4 Add code under the processRequest of UpdagePG Controller.
           //for image display starts here
                     if(pageContext.isLoggingEnabled(1))
                     pageContext.writeDiagnostics(this, "Entering processRequest..", 1);
                     Serializable[] param = {pageContext.getTemporaryImageLocation()};
                     pageContext.getApplicationModule(webBean).invokeMethod("initImg",param); //initImg is the method we will create in AM
                     OAImageBean oaimagebean = (OAImageBean)webBean.findIndexedChildRecursive("imageIT"); //id of image in the PG
                     if(oaimagebean != null)
                     {
                     oaimagebean.setAttributeValue(UIConstants.SOURCE_ATTR, new OADataBoundValueViewObject(oaimagebean, "ImageSource")); //ImageSource we created null column in the VO
                     oaimagebean.setWidth(100);
                     oaimagebean.setHeight(110);
                     oaimagebean.setBorderWidth(2);
                     }
                     if(pageContext.isLoggingEnabled(1))
                     pageContext.writeDiagnostics(this, "Leaving processRequest..", 1);
                     }
               //for image display ends here here
              
              

3.5 method in AM .
//for image dob starts here
public void initImg(String s2)
{
OAViewObjectImpl oaviewobjectimpl = getPatientEntryVO1();      //VO name
oaviewobjectimpl.executeQuery();
if(oaviewobjectimpl.first() != null)
{
OARow oarow = (OARow)oaviewobjectimpl.first();
BlobDomain blobdomain = (BlobDomain)oarow.getAttribute("Image"); //attribute name of blob column i.e., Image
if(blobdomain != null)
{
String s3 = createFile(s2, blobdomain, (String)oarow.getAttribute("PatientName")); //attribute name of PatientName
oarow.setAttribute("ImageSource", s3);
}
}
}
public void fromInputToOutput(InputStream inputstream, OutputStream outputstream)
throws IOException
{
byte abyte0[] = new byte[255];
for(int i = 255; i == 255;)
{
i = inputstream.read(abyte0);
if(i < 0)
break;
outputstream.write(abyte0, 0, i);
}
outputstream.close();
inputstream.close();
}
public String createFile(String s, BlobDomain blobdomain, String s1)
{
File file = new File(s);
if(!file.exists())
file.mkdirs();
File file1 = new File(s, s1);
try
{
fromInputToOutput(blobdomain.getBinaryStream(), new FileOutputStream(file1));
}
catch(IOException ioexception)
{
ioexception.printStackTrace();
}
return "fwk/t/" + file1.getName();
}

//for image dob ends here              

ref: https://community.oracle.com/message/2197822#2197822

26. OAF Storing Images into Database

1. In database table create column datatype as Blob
2. In VO Attribute based on EO Check the attribute type is BlobDomain
3. Create Item in Region:
itemStyle : MessageFileUpload.
 DataType : Blob