Oracle apps EBS r12 Report for the HRMS and
Payroll Report - Report Name - Temporary Appointment Letter
Template
File - RTF File Design :
Download the Format of
the RTF File means Template File:
https://drive.google.com/file/d/1Eg7BkSJb0esOF5O6wlFeL83hyKmg4AZW/view?usp=sharing
Format Like This.
-----------------------------------------------------------------------------------------------
---------------------------------- XXBEXTEMPAPPOINTLETTR.xml
--------------------------
Data
Definition File :
Download the Format of
the Data Definition File :
<?xml
version="1.0" encoding="UTF-8" ?>
<dataTemplate name="XXBEXTEMPAPPOINTLETTR"
version="1.0">
<properties>
<property
name="xml_tag_case" value="upper" />
</properties>
<parameters>
<parameter
name="P_IN_LEGAL_ENTITY" dataType =
"VARCHAR2"></parameter>
<parameter
name="P_IN_APPLICANT_NUM" dataType =
"VARCHAR2"></parameter>
<parameter
name="P_IN_BASIC" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_HOUSE_RENT" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_MEDICAL_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_CONVEY_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_SPCL_ALLOWANCE" dataType = "varchar2"></parameter>
<parameter
name="P_IN_UTILITY_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_CHILD_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_FAMILY_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_ENTERTAIN_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_FIELD_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_UPKEEP_ALLOWANCE" dataType =
"varchar2"></parameter>
<parameter name="P_IN_OTHER_ALLOWANCE"
dataType = "varchar2"></parameter>
<parameter name="P_MOBILE"
dataType="NUMBER"></parameter>
<parameter name="P_PERS"
dataType="NUMBER"></parameter>
<parameter
name="P_IN_CONCEN1" dataType =
"varchar2"></parameter>
<parameter name="P_IN_CONCEN2"
dataType = "varchar2"></parameter>
<parameter
name="P_IN_CONCEN3" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_CONCEN4" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_CONCEN5" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_TEMP_START_DATE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_TEMP_END_DATE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_APPL_DEPT" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_APPL_DESIG" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_APPL_LOCATE" dataType =
"varchar2"></parameter>
<parameter
name="P_IN_APPL_GRADE" dataType =
"varchar2"></parameter>
</parameters>
<dataQuery>
<sqlStatement
name="Q_APLCNT">
<![CDATA[
SELECT
DECODE(:P_IN_LEGAL_ENTITY,'BAIL','Bangladesh Antibiotic Industries
Limited','BPL','Beximco Pharmaceuticals Limited','IIS','I&I Services
Limited','PCL','Pharmatek Chemicals Limited','SPL', 'Shuktara Printers Limited')
legal_entity,
trim(initcap(papf.first_name
||' '
||papf.middle_names
||DECODE(papf.middle_names,NULL,'',' ')
||papf.last_name)) applicant_name,
initcap(papf.title||' '||papf.last_name)
last_name,
:P_IN_APPL_DESIG appl_designation,
:P_IN_APPL_DEPT appl_department,
:P_IN_APPL_LOCATE appl_location,
:P_IN_APPL_GRADE appl_grade,
TO_CHAR(to_date(:P_IN_TEMP_START_DATE,'YYYY/MM/DD
HH24:MI:SS'),'DD-Mon-YYYY') start_date,
TO_CHAR(to_date(:P_IN_TEMP_END_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-Mon-YYYY')
end_date,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE rownum =1
AND person_id=:P_IN_CONCEN1
) concern1,
(SELECT
SUBSTR(pp.name,1,instr(pp.name,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN1
AND asgn.position_id=pp.position_id
AND sysdate BETWEEN asgn.effective_start_date
AND asgn.effective_end_date
) pos1,
(SELECT
SUBSTR(pp.name,1,instr(pp.name,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN2
AND asgn.position_id=pp.position_id
AND sysdate BETWEEN asgn.effective_start_date
AND asgn.effective_end_date
) pos2,
(SELECT
SUBSTR(pp.name,1,instr(pp.name,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN3
AND asgn.position_id=pp.position_id
AND sysdate BETWEEN asgn.effective_start_date
AND asgn.effective_end_date
) pos3,
(SELECT
SUBSTR(pp.name,1,instr(pp.name,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN4
AND asgn.position_id=pp.position_id
AND sysdate BETWEEN asgn.effective_start_date
AND asgn.effective_end_date
) pos4,
(SELECT
SUBSTR(pp.name,1,instr(pp.name,'|')-1)
FROM per_all_assignments_f asgn,
per_all_positions pp
WHERE 1 =1
AND asgn.person_id =:P_IN_CONCEN5
AND asgn.position_id=pp.position_id
AND sysdate BETWEEN asgn.effective_start_date
AND asgn.effective_end_date
) pos5,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE rownum =1
AND person_id=:P_IN_CONCEN2
) concern2,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE rownum =1
AND person_id=:P_IN_CONCEN3
) concern3,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE rownum =1
AND person_id=:P_IN_CONCEN4
) concern4,
(SELECT trim(initcap(peop.first_name
||' '
||peop.middle_names
||DECODE(peop.middle_names,NULL,'',' ')
||peop.last_name))
FROM per_all_people_f peop
WHERE rownum =1
AND person_id=:P_IN_CONCEN5
) concern5,
(SELECT trim(initcap(ppf.first_name||'
'||ppf.middle_names||DECODE(ppf.middle_names,NULL,'',' ')||ppf.last_name))
person_name
FROM per_people_extra_info pei ,
per_all_people_f ppf ,
fnd_user usr ,
pqh_roles rls
WHERE information_type = 'PQH_ROLE_USERS'
AND pei.person_id = ppf.person_id
AND sysdate BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND usr.employee_id = ppf.person_id
AND rls.role_name = 'HR Letters Signatory'
AND rls.role_id = to_number(pei.pei_information3)
AND rownum=1
) hod_hr_name,
(SELECT jobd.segment2
FROM per_people_extra_info pei ,
per_all_people_f ppf ,
fnd_user usr ,
pqh_roles rls,
per_all_assignments_f paf,
per_jobs pjob,
per_job_definitions jobd
WHERE information_type = 'PQH_ROLE_USERS'
AND pei.person_id = ppf.person_id
AND usr.employee_id = ppf.person_id
AND paf.person_id =ppf.person_id
AND rls.role_name ='HR Letters Signatory'
AND paf.job_id = pjob.job_id(+)
AND pjob.job_definition_id =
jobd.job_definition_id(+)
AND rls.role_id = to_number(pei.pei_information3)
AND sysdate BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND sysdate BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND rownum=1
) hod_position,
addrs.address_line1,
addrs.address_line2,
addrs.address_line3,
addrs.town_or_city,
addrs.postal_code,
(select TERRITORY_SHORT_NAME from
FND_TERRITORIES_TL where language='US' and territory_code=addrs.country)
country,
:P_IN_BASIC basic,
:P_IN_HOUSE_RENT housing,
:P_IN_MEDICAL_ALLOWANCE medical,
:P_IN_CONVEY_ALLOWANCE conveyance,
:P_IN_SPCL_ALLOWANCE special,
:P_IN_UTILITY_ALLOWANCE utility,
:P_IN_CHILD_ALLOWANCE children,
:P_IN_FAMILY_ALLOWANCE family,
:P_IN_ENTERTAIN_ALLOWANCE entertain,
:P_IN_FIELD_ALLOWANCE field,
:P_IN_UPKEEP_ALLOWANCE upkeep,
:P_IN_OTHER_ALLOWANCE other,
:P_MOBILE MOB,
:P_PERS PERS,
(NVL(:P_IN_BASIC,0)
+NVL(:P_IN_HOUSE_RENT,0)+NVL(:P_IN_MEDICAL_ALLOWANCE,0)+NVL(:P_IN_CONVEY_ALLOWANCE,0)+NVL(:P_IN_SPCL_ALLOWANCE,0)+NVL(:P_IN_UTILITY_ALLOWANCE,0)+NVL(:P_IN_CHILD_ALLOWANCE,0)+NVL(:P_IN_FAMILY_ALLOWANCE,0)+NVL(:P_IN_ENTERTAIN_ALLOWANCE,0)+NVL(:P_IN_FIELD_ALLOWANCE,0)+NVL(:P_IN_UPKEEP_ALLOWANCE,0)
+NVL(:P_IN_OTHER_ALLOWANCE,0)+ NVL(:P_MOBILE,0) + NVL(:P_PERS,0))total_sal,
xxbex_num_to_word_convert_func(NVL(:P_IN_BASIC,0)+NVL(:P_IN_HOUSE_RENT,0)+NVL(:P_IN_MEDICAL_ALLOWANCE,0)+NVL(:P_IN_CONVEY_ALLOWANCE,0)+NVL(:P_IN_SPCL_ALLOWANCE,0)+NVL(:P_IN_UTILITY_ALLOWANCE,0)+NVL(:P_IN_CHILD_ALLOWANCE,0)+NVL(:P_IN_FAMILY_ALLOWANCE,0)+NVL(:P_IN_ENTERTAIN_ALLOWANCE,0)+NVL(:P_IN_FIELD_ALLOWANCE,0)+NVL(:P_IN_UPKEEP_ALLOWANCE,0)
+NVL(:P_IN_OTHER_ALLOWANCE,0) + NVL(:P_MOBILE,0) + NVL(:P_PERS,0)) total_sal_words
FROM per_all_people_f
papf,
per_addresses addrs
WHERE 1 =1
AND
papf.person_id=addrs.person_id(+)
AND
addrs.primary_flag(+)='Y'
AND papf.person_id
=:P_IN_APPLICANT_NUM
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_APLCNT"
dataType="varchar2" source="Q_APLCNT">
<element name="LEGAL_ENTITY" dataType="varchar2"
value="LEGAL_ENTITY"/>
<element
name="APPLICANT_NAME" dataType="varchar2"
value="APPLICANT_NAME"/>
<element
name="LAST_NAME" dataType="varchar2"
value="LAST_NAME"/>
<element name="APPL_DESIGNATION"
dataType="varchar2" value="APPL_DESIGNATION"/>
<element
name="APPL_DEPARTMENT" dataType="varchar2"
value="APPL_DEPARTMENT"/>
<element
name="APPL_LOCATION" dataType="varchar2"
value="APPL_LOCATION"/>
<element
name="APPL_GRADE" dataType="varchar2"
value="APPL_GRADE"/>
<element
name="START_DATE" dataType="varchar2"
value="START_DATE"/>
<element
name="END_DATE" dataType="varchar2"
value="END_DATE" />
<element
name="CONCERN1" dataType="varchar2"
value="CONCERN1" />
<element
name="POS1" dataType="varchar2" value="POS1"/>
<element
name="CONCERN2" dataType="varchar2"
value="CONCERN2"/>
<element
name="POS2" dataType="varchar2" value="POS2"/>
<element
name="CONCERN3" dataType="varchar2"
value="CONCERN3"/>
<element
name="POS3" dataType="varchar2" value="POS3"/>
<element
name="CONCERN4" dataType="varchar2"
value="CONCERN4"/>
<element
name="POS4" dataType="varchar2" value="POS4"/>
<element
name="CONCERN5" dataType="varchar2"
value="CONCERN5"/>
<element
name="POS5" dataType="varchar2" value="POS5"/>
<element
name="HOD_HR_NAME" dataType="varchar2"
value="HOD_HR_NAME"/>
<element
name="HOD_POSITION" dataType="varchar2"
value="HOD_POSITION"/>
<element
name="ADDRESS_LINE1" dataType="varchar2" value="ADDRESS_LINE1"/>
<element
name="ADDRESS_LINE2" dataType="varchar2"
value="ADDRESS_LINE2"/>
<element
name="ADDRESS_LINE3" dataType="varchar2"
value="ADDRESS_LINE3"/>
<element
name="TOWN_OR_CITY" dataType="varchar2"
value="TOWN_OR_CITY"/>
<element
name="POSTAL_CODE" dataType="varchar2"
value="POSTAL_CODE"/>
<element
name="COUNTRY" dataType="varchar2"
value="COUNTRY"/>
<element
name="BASIC" dataType="varchar2"
value="BASIC"/>
<element
name="HOUSING" dataType="varchar2" value="HOUSING"/>
<element
name="MEDICAL" dataType="varchar2"
value="MEDICAL"/>
<element
name="CONVEYANCE" dataType="varchar2"
value="CONVEYANCE"/>
<element
name="SPECIAL" dataType="varchar2"
value="SPECIAL"/>
<element
name="UTILITY" dataType="varchar2"
value="UTILITY"/>
<element
name="CHILDREN" dataType="varchar2"
value="CHILDREN"/>
<element
name="FAMILY" dataType="varchar2"
value="FAMILY"/>
<element
name="ENTERTAIN" dataType="varchar2" value="ENTERTAIN"/>
<element
name="FIELD" dataType="varchar2"
value="FIELD"/>
<element
name="UPKEEP" dataType="varchar2"
value="UPKEEP"/>
<element
name="OTHER" dataType="varchar2"
value="OTHER"/>
<element
name="P_MOB" dataType="varchar2" value="MOB"/>
<element
name="P_PER" dataType="varchar2"
value="PERS"/>
<element
name="TOTAL_SAL" dataType="varchar2"
value="TOTAL_SAL"/>
<element
name="TOTAL_SAL_WORDS" dataType="varchar2"
value="TOTAL_SAL_WORDS"/>
</group>
</dataStructure>
</dataTemplate>
----------------------------------
End----------------
Download the Format of
the XXBEXTEMPAPPOINTLETTR.rtf::
https://drive.google.com/file/d/1Eg7BkSJb0esOF5O6wlFeL83hyKmg4AZW/view?usp=sharing
Download the Format of
the XXBEXTEMPAPPOINTLETTR.xml ::
https://drive.google.com/file/d/1BMKCDaQXAwI1D5HEsWNKn7tDZjRDYaIQ/view?usp=sharing
Download the Format of
the
XXBEXTEMPAPPOINTLETTR_CP.ldt::
Download the Format of
the
XXBEXTEMPAPPOINTLETTR_DD.ldt ::
https://drive.google.com/file/d/1lcahrr82A16fD6oNsmaEzSBB_mKXjWrG/view?usp=sharing
Download the Format of
the
XXBEXTEMPAPPOINTLETTR_REQ.ldt::
https://drive.google.com/file/d/1j6kNmLzHNSA7qXt7so_Yad5TNM0ot5P2/view?usp=sharing
No comments:
Post a Comment