Query for employee, element , entry value and input entry value

SELECT distinct
PETF.ELEMENT_NAME “Element Entry Name” ,
pivf.name “Input value”,
PAPF.FULL_NAME “Employee Name”,
PEEVF.EFFECTIVE_START_DATE “Entry Start Date” ,
PEEVF.EFFECTIVE_END_DATE “Entry End Date” ,
PEEVF.SCREEN_ENTRY_VALUE “Entry Values”
FROM
PAY_ELEMENT_TYPES_F PETF ,
PAY_ELEMENT_LINKS_F PELF ,
PAY_ELEMENT_ENTRIES_F PEEF ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PER_ALL_PEOPLE_F PAPF ,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF ,
PAY_INPUT_VALUES_F PIVF
WHERE 1 = 1
AND PELF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEF.EFFECTIVE_START_DATE) AND TRUNC(PEEF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PIVF.EFFECTIVE_START_DATE) AND TRUNC(PIVF.EFFECTIVE_END_DATE)
AND PAAF.ASSIGNMENT_ID = PEEF.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.CURRENT_EMP_OR_APL_FLAG = ‘Y’
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND PEEVF.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
AND paaf.primary_flag = ‘Y’
AND paaf.assignment_type = ‘E’
AND TRUNC(SYSDATE) BETWEEN TRUNC(PEEVF.EFFECTIVE_START_DATE) AND TRUNC(PEEVF.EFFECTIVE_END_DATE)
AND PETF.ELEMENT_NAME = ‘ESPP’
and papf.business_group_id = 96;

Advertisements

Business group query

SELECT o.organization_id,
       o.organization_id,
       otl.NAME,
       o.date_from,
       o.date_to,
       o.internal_address_line,
       o.location_id,
       o.comments
  FROM hr_all_organization_units    o,
       hr_all_organization_units_tl otl,
       hr_organization_information  o2,
       hr_organization_information  o3,
       hr_organization_information  o4
 WHERE o.organization_id = otl.organization_id
   AND o.organization_id = o2.organization_id(+)
   AND o.organization_id = o3.organization_id
   AND o.organization_id = o4.organization_id
   AND o3.org_information_context = 'Business Group Information'
   AND o2.org_information_context(+) = 'Work Day Information'
   AND o4.org_information_context = 'CLASS'
   AND o4.org_information1 = 'HR_BG'
   AND o4.org_information2 = 'Y';

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;

/

Number to Word conversion in RTF

xdofx vs xdoxslt

<?xdofx:expression?> for extended SQL functions
<?xdoxslt:expression?> for extended XSL functions.

You cannot mix xdofx statements with XSL expressions in the same context

This function enables the conversion of numbers to words for RTF template output. This is a common requirement for check printing.

The new function is “to_check_number”. The syntax of this function is

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

The following table describes the function attributes:

 
Attribute Description Valid Value
amount The number to be transformed. Any number
precisionOrCurrency For this attribute you can specify either the precision, which is the number of digits after the decimal point; or the currency code, which will govern the number of digits after the decimal point. The currency code does not generate a currency symbol in the output. An integer, such as 2; or a currency code, such as ‘USD’.
caseType The case type of the output. Valid values are:
‘CASE_UPPER’,
‘CASE_LOWER’,
‘CASE_INIT_CAP’
decimalStyle Output type of the decimal fraction area. Valid values are:
‘DECIMAL_STYLE_FRACTION1’, ‘DECIMAL_STYLE_FRACTION2’,
‘DECIMAL_STYLE_WORD’

The following examples display the function as entered in an RTF template and the returned output:

 
RTF Template Entry Returned Output
<?xdofx:to_check_number(12345.67, 2)?> Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345.67, ‘USD’)?> Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345, ‘JPY’, ‘CASE_UPPER’)?> TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
<?xdofx:to_check_number(12345.67, ‘EUR’, ‘CASE_LOWER’, ‘DECIMAL_STYLE_WORDS’)?> twelve thousand three hundred forty-five and sixty-seven

Please note this is only available for R12 and not currently present on 11i.

Alternative

<?xdoxslt:toWordsAmt(TEST_AMOUNT)?>

Then it will display amount in words in Indian currency means Rupees not millions.

Example:

<?xdoxslt: toWordsAmt(123454.879)?>

Then it will display as

One Lakh Twenty Three thousand Four hundred Fifty Four and paise Eighty Eight

Alternative

Use the following function in rdf level

IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TEST_AMOUNT)

 

 

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;