Oracle apps r12 XML Template sum of column showing output as NNNN

if AMT is your column to be sum in the rtf template then use as below.

<?sum(AMT[.!=''])?>

Oracle Apps R12 SSHR 'Request Information' (QUESTION/ANSWER) from plsql

1. Procedure ..

    CREATE OR replace PROCEDURE apps.equest_information(pAction IN VARCHAR2, --QUESTION , for Answer keep null
                                pComments IN VARCHAR2, -- Manager request for more info details
                                userName IN VARCHAR2,  --to whom to be intended
                                pNotification_id IN NUMBER, -- Notification ID
                                pStatus OUT VARCHAR2,
                                pMessage OUT VARCHAR2
                                )
            IS
            BEGIN
                    BEGIN

IF(pAction = 'QUESTION') THEN
wf_notification.UpdateInfo(pNotification_id, userName,pComments,'MANAGER.API','WA','');
ELSE
wf_notification.UpdateInfo(pNotification_id, '',pComments,'','','');
END IF;

                    pStatus := 'S';
                    pMessage := 'Successfully '||initcap(pAction);
                    COMMIT;
            EXCEPTION
            WHEN OTHERS THEN
            pStatus := 'E';
            pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;
            END equest_information;
END;           


2..----------------------------to check the transaction history then ----------
SELECT rownum l_sequence ,a.l_notification_id ,a.l_role role ,a.l_action action ,a.l_comments ,to_char(a.l_date,'dd-mm-yyyy hh:mi:ss am')  l_date FROM (
SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname
 (nvl (wn.more_info_role ,wn.recipient_role)) l_role ,
 decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING')) ,
 decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action ,
 na.text_value l_comments ,ias.end_date l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT pah.notification_id l_notification_id ,wf_directory.getroledisplayname (pah.user_name) l_role
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action)) l_action ,pah.user_comment l_comments
 ,pah.last_update_date l_date FROM pqh_ss_approval_history pah WHERE pah.transaction_item_type = :p_item_type
 AND pah.transaction_item_key = :p_item_key AND pah.action NOT IN ('APPROVED','REJECTED','SUBMIT' ,'RFC','RESUBMIT','TIMEOUT','QUESTION','ANSWER')
 UNION
 SELECT DISTINCT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (wn.recipient_role) l_role
 ,decode (l.lookup_code ,'SFL' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'PENDING'))
 ,decode (wn.status ,'CANCELED' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN')) ,l.meaning)) l_action
 ,decode (wn.status ,'CANCELED' ,NULL ,nvl (na.text_value ,
 ( SELECT user_comment FROM wf_comments WHERE notification_id = wn.notification_id AND action = l.lookup_code AND rownum = 1 ))) l_comments
 ,nvl (ias.end_date ,ias.begin_date) l_date FROM wf_activities a ,wf_notification_attributes na ,wf_process_activities pa ,wf_items i
 ,wf_item_activity_statuses_h ias ,wf_lookups_tl l ,wf_user_roles wur ,wf_notifications wn
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND ias.item_type = i.item_type AND ias.item_key = i.item_key
 AND ias.notification_id IS NOT NULL AND wn.notification_id = na.notification_id (+) AND na.name (+) = 'WF_NOTE'
 AND nvl (ias.activity_result_code ,'A') NOT IN ('SFL','RESUBMIT') AND ias.process_activity = pa.instance_id
 AND pa.activity_name = a.name AND pa.activity_item_type = a.item_type AND a.result_type NOT IN ('*','HR_DONE')
 AND i.begin_date BETWEEN a.begin_date AND nvl (a.end_date ,i.begin_date) AND a.result_type = l.lookup_type
 AND nvl (ias.activity_result_code ,'SFL') = l.lookup_code AND l.language = userenv('LANG')
 AND ias.assigned_user = wur.role_name AND ias.notification_id = wn.group_id
 UNION
 SELECT wn.notification_id l_notification_id ,wf_directory.getroledisplayname (nvl (wn.more_info_role ,wn.recipient_role)) l_role
 ,decode (wn.status ,'CANCELED' ,decode (pah.action ,'TIMEOUT' ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))
 ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,'BEATEN'))) ,(hr_general.decode_lookup ('PQH_SS_APPROVAL_STATUS' ,pah.action))) l_action
 ,decode (pah.notification_id ,wn.notification_id ,pah.user_comment ,NULL) l_comments ,pah.last_update_date l_date
 FROM pqh_ss_approval_history pah ,wf_notifications wn
 WHERE pah.transaction_item_type = :p_item_type AND pah.transaction_item_key = :p_item_key
 AND pah.action IN ('TIMEOUT','RESUBMIT') AND wn.group_id = ( SELECT group_id FROM wf_notifications WHERE notification_id = pah.notification_id
 AND rownum = 1 )
 UNION
 select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role
 -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES_H IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id
 and wn.notification_id = c.notification_id and C.ACTION in('QUESTION', 'ANSWER') UNION
 select wn.notification_id l_notification_id ,wf_directory.getroledisplayname (C.FROM_ROLE) l_role -- ,WF_CORE.TRANSLATE(C.ACTION) l_action
 ,C.ACTION l_action ,C.USER_COMMENT l_comments -- ,nvl (ias.end_date,ias.begin_date) l_date
 ,c.comment_date l_date from WF_ITEM_ACTIVITY_STATUSES IAS, WF_COMMENTS C, wf_notifications wn
 where IAS.ITEM_TYPE = :p_item_type and IAS.ITEM_KEY = :p_item_key and IAS.NOTIFICATION_ID = wn.group_id and wn.notification_id = c.notification_id
 and C.ACTION in('QUESTION', 'ANSWER')
 UNION SELECT 0 ,wf_directory.getroledisplayname (owner_role) l_role ,wf_core.translate ('SUBMIT')
 ,appr.text_value note ,begin_date l_date
 FROM wf_items i ,wf_item_attribute_values appr
 WHERE i.item_type = :p_item_type AND i.item_key = :p_item_key AND i.item_type = appr.item_type (+) AND i.item_key = appr.item_key (+)
 AND appr.name (+) = 'SUBMIT_COMMENTS' ORDER BY l_notification_id ,l_date ) a

Oracle Apps R12 api to upload PER_QUALIFICATION_TYPES in english and Arabic (Multi-Lingual)

1. Interface Table

CREATE TABLE XXFUJ_INT_QUAL_TYPES
(
ID NUMBER,
CODE VARCHAR2(240),
NAME_ENG VARCHAR2(240),
NAME_AR VARCHAR2(240),
CATEGORY VARCHAR2(240),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE5 VARCHAR2(240)
)

2. FOR ENGLISH

declare
l_qualification_type_id NUMBER;
l_object_version_number NUMBER;
CURSOR C1 IS
SELECT * FROM XXFUJ_INT_QUAL_TYPES
--WHERE CODE = 100
WHERE ID IS NULL
;
begin
FOR I IN C1 LOOP
HR_QUALIFICATION_TYPE_API.create_qualification_type(
P_EFFECTIVE_DATE => SYSDATE
,P_NAME => I.NAME_ENG
,P_CATEGORY => I.CATEGORY
--,p_language_code => 'AR'
,p_qualification_type_id => l_qualification_type_id
,p_object_version_number => l_object_version_number
);
UPDATE XXFUJ_INT_QUAL_TYPES
SET ID = l_qualification_type_id
WHERE CODE= I.CODE;
COMMIT;
END LOOP;
exception when others then
dbms_output.put_line(sqlerrm);
end;


--SELECT * FROM PER_QUALIFICATION_TYPES


3.FOR ARABIC-

declare
l_qualification_type_id NUMBER;
l_object_version_number NUMBER := 1;
l_language_code VARCHAR2(200) := 'AR';
--L_object_version_number NUMBER;
CURSOR C1 IS
SELECT OBJECT_VERSION_NUMBER,
ID, CODE, (select flv.meaning --flv.lookup_CODE,
from fnd_lookup_types hrl,fnd_lookup_values flv
where hrl.lookup_type  = flv.lookup_type
AND language = 'AR'
and hrl.lookup_type  = 'AE_EDUCATION_LEVEL'
AND XXFUJ.CODE = FLV.LOOKUP_CODE
)NAME_AR1 FROM XXFUJ_INT_QUAL_TYPES XXFUJ, PER_QUALIFICATION_TYPES PER
WHERE
PER.QUALIFICATION_TYPE_ID = XXFUJ.ID
--AND XXFUJ.ID NOT IN ( 1030,1010,1011,1012,1013,1014)
;
/*SELECT
ID, CODE, (select flv.meaning --flv.lookup_CODE,
from fnd_lookup_types hrl,fnd_lookup_values flv
where hrl.lookup_type  = flv.lookup_type
AND language = 'AR'
and hrl.lookup_type  = 'AE_EDUCATION_LEVEL'
AND XXFUJ.CODE = FLV.LOOKUP_CODE
)NAME_AR1 FROM XXFUJ_INT_QUAL_TYPES XXFUJ
WHERE ID NOT IN ( 1030,1010,1011,1012,1013)
;*/
begin
FOR  I IN C1 LOOP
--dbms_output.put_line('HI');
HR_QUALIFICATION_TYPE_API.update_qualification_type(
p_qualification_type_id => I.ID
,p_object_version_number => I.OBJECT_VERSION_NUMBER
,P_language_code => L_language_code
,P_EFFECTIVE_DATE => SYSDATE
,P_NAME => I.NAME_AR1
--,p_language_code => l_language_code
--,p_qualification_type_id => l_qualification_type_id
);
commit;
END LOOP;
dbms_output.put_line(sqlerrm);
exception when others then
dbms_output.put_line(sqlerrm);
end;

Query Oracle Apps R12 Payroll Result Values Element Query with Effective Date

select papf.employee_number,papf.full_name,ppa.effective_date,pp.payroll_name,
pet.element_name,piv.name input_value,prrv.result_value,ppa.payroll_action_id
from apps.pay_payroll_actions ppa,
 pay_assignment_actions paa,
pay_payrolls_f pp,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
apps.per_all_assignments_f paaf,
apps.per_all_people_f papf
--where ppa.payroll_action_id = :payroll_action_id -- give your payroll_action_id
where
ppa.payroll_id = nvl(:payroll_id,ppa.payroll_id)
--and paaf.organization_id = nvl(:p_organization_id, paaf.organization_id)
--and paaf.person_id = nvl(:P_PERSON_ID, paaf.person_id)
--and paa.assignment_action_id = :assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = pp.payroll_id
and paa.assignment_action_id = prr.assignment_action_id
and prr.run_result_id= prrv.run_result_id
and prrv.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and paaf.assignment_id = paa.assignment_id
and paaf.person_id = papf.person_id
and trunc(sysdate) between pp.effective_start_date and pp.effective_end_date
and trunc(sysdate) between pet.effective_start_date and pet.effective_end_date
and trunc(sysdate) between piv.effective_start_date and piv.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.employee_number = '1234'  --give your employee number here
and ppa.effective_date = to_date('28-MAR-2018')--between (:P_FROM_DATE) and last_day(:P_FROM_TO)
and ELEMENT_NAME = 'Net Salary'
AND piv.name = 'Pay Value'
order by employee_number