Changing front end password using script

Following script can be used for changing the password of a user:

select fnd_web_Sec.change_password(‘PRAY‘,’Welcome1234’) from dual;

 

Following block can be used to assign the user with the responsibilities from backend:

BEGIN

    fnd_user_pkg.addresp(

        ‘PRAY’,    ‘SYSADMIN’,  ‘SYSTEM_ADMINISTRATOR’,    ‘STANDARD’,

        ‘Add Responsibility to USER using pl/sql’,  SYSDATE,

        SYSDATE + 100   );

    COMMIT;

    dbms_output.put_line(  ‘Responsibility Added Successfully’

    );

EXCEPTION    WHEN OTHERS THEN

        dbms_output.put_line(

            ‘ Responsibility is not added due to ‘ ||  sqlcode    ||  substr(

                sqlerrm,     1,100)

        );

        ROLLBACK;

END;

/

Defaulting DFF Value

The requirement was to copy the content entered in DFF for each subsequent lines in Inter Org Transfer

Following are the steps followed :

  1. insert into table
  2. default from DFF definition screen
  3. deleted from table

We saved the data entered in the first line’s DFF into a table. We were unable to use Global temporary table. For some unknown reason the data could not be fetched.

And then fetch the data from the table and default it at the DFF definition level.

After saving the data from the table gets deleted.

 

1

1

 

=’declare
begin
BWSSB_FORM_PRSNAZ_PKG.BWSSB_INTER_ORG(”’||${item.mtl_trx_line.attribute1.value}||”’,”’||${item.mtl_trx_line.attribute2.value}||”’,”’||${item.mtl_trx_line.attribute3.value}||”’,”’||${item.mtl_trx_line.attribute4.value}||”’,”’||${item.mtl_trx_line.attribute5.value}||”’,”’||${item.mtl_trx_line.attribute6.value}||”’,”’||${item.mtl_trx_line.attribute7.value}||”’,”’||${item.mtl_trx_line.attribute8.value}||”’);
END’

Defaulting at DFF level

Now go to DFF definition screen and select each segment and pass default value.

1

Default Value : SELECT DISTINCT attribute1 from BWBSSB_TEMP_TBL

If date column is present, in sert into the table as charector for format and use following for defaulting:

SELECT DISTINCT To_DATE(attribute2,’YYYY/MM/DD HH24:MI:SS’) from BWBSSB_TEMP_TBL

Clearing table :

1

 

1

 

Package :

CREATE OR REPLACE package BODY BWSSB_FORM_PRSNAZ_PKG
AS
PROCEDURE BWSSB_INTER_ORG(P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_ATTRIBUTE4 VARCHAR2,
P_ATTRIBUTE5 VARCHAR2,
P_ATTRIBUTE6 VARCHAR2,
P_ATTRIBUTE7 VARCHAR2,
P_ATTRIBUTE8 VARCHAR2)
AS
BEGIN
INSERT INTO BWBSSB_TEMP_TBL VALUES(P_ATTRIBUTE1,P_ATTRIBUTE2, P_ATTRIBUTE3,P_ATTRIBUTE4,P_ATTRIBUTE5,P_ATTRIBUTE6,P_ATTRIBUTE7,P_ATTRIBUTE8);
COMMIT;
END BWSSB_INTER_ORG;
PROCEDURE BWSSB_DELETE_TEMP_TABLE
AS
BEGIN
DELETE BWBSSB_TEMP_TBL;
COMMIT;
END BWSSB_DELETE_TEMP_TABLE;
END BWSSB_FORM_PRSNAZ_PKG;

 

How to delete Future records in HRMS

If you want to delete the future changes made in a record.

Example:
Employee had an assignment starting on 1-Jan-2017 to 31-Dec-4712
Made a change on o1-Feb-2017 by mistake,
Now has 2 Records
1. 1-Jan-2017 to 31-Jan-2007
2. 1-Feb-2017 to 31-Dec-4712

Now we want to delete the second record in the system

Steps for Solutions –>
1.  Date track to a date before the date of the update.
Check that the Effective End Date is the day before the change date.
2.  Press the Delete icon.
3.  At the message ‘Do you really want to delete this record?’ click on ‘Yes’.
4.  You will get a list of options – ‘Next’, ‘All’, ‘Purge’.
Press ‘Next’ to remove the update.
Press ‘All’ if there are more that one updates to this record in the future and you want to remove them all.
Press ‘Purge’ to remove the record totally from the system (not what you want in this case).
You may not be able to do all 3 options in which case the unavailable ones will either not be shown or will
generate a meaningful error message.
5. When you have pressed ‘Next’ or ‘All’ the record will disappear from the screen and you should press Save.

Data link in Data Template

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.

One way for doing it is using Bind variable. But sometimes this method may not work.

Eaxample

<dataQuery>
<sqlStatement name=”Q1″>
<![CDATA[
SELECT DEPTNUM,DNAME,LOC
from scott.dept
order by deptno ]]>
</sqlStatement>
<sqlStatement name=”Q2″>
<![CDATA[
SELECT DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
from scott.EMP
where DEPTNO = :DEPTNUM ]]>
</sqlStatement>
</dataQuery>

 

 

Using Link

<link name=”DEPTEMP_LINK” parentQuery=”Q1″ parentColumn=”DEPTNO” childQuery=”Q_2″ childColumn=”DEPARTMENTNO”/>

 

Example

<dataQuery>
<sqlStatement name=”Q1″>
<![CDATA[
SELECT DEPTNO,DNAME,LOC from scott.dept
order by deptno ]]>
</sqlStatement>
<sqlStatement name=”Q2″>
<![CDATA[
SELECT DEPARTMENTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
from scott.EMP ]]>
</sqlStatement>
<link name=”DEPTEMP_LINK” parentQuery=”Q1″ parentColumn=”DEPTNO” childQuery=”Q_2″ childColumn=”DEPARTMENTNO”/>
</dataQuery>

 

			

Data Template error :The uploaded file is invalid. the file should be in xml-data-template format

While defining Data Definition

Error:
The uploaded file is invalid. the file should be in xml-data-template format.

Solution : Check if the Data Template XML file that is being uploaded is in proper XML format. Try opening the file in IE and check if it is coming in proper format or erroring out(or not in proper format).