Oracle Apps R12 Workflow Mailer --> Remove the Actions button(Approve/Reject) from the Email

 Remove the Actions button(Approve/Reject) from the Email

Solution:
Open the wtf file in workflow Builder
Right click and add attribute to the message.
Attribute: #WFM_OPEN_MAIL
Default Value: WFMAIL:OPEN_MAIL_OUTLOOK

Save and retest. 

Oracle apps r12 Workflow Mailer --> EBS FYI notification closed automatically when email is fired.

EBS notification closed automatically when email is fired.

Solution:

OAM --> Notification Mailer / Edit / Advanced / Go to Step 4 (Message Generation).

Uncheck Autoclose FYI

Oracle Apps R12 api to create organization , assign organization classification

Using Cursor................

declare
   l_organization_id number ;
   l_object_version_number number;
   l_object_version_number2 number;
   l_org_information_id number;
   cursor c1 is
   select a org_name, b location_id,c type
   from testa
   ;
begin
for i in c1 loop
apps.hr_organization_api.create_organization
                     (p_validate                    => NULL,
                      p_effective_date              => sysdate,
                      p_language_code               => apps.hr_api.userenv_lang,
                      p_business_group_id           => 80,
                      p_date_from                   => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
                      p_name                        => i.org_name,
                      p_location_id                 => i.location_id,
                      --p_date_to                     => sysdate,
                      p_internal_external_flag          => 'INT',
                      --p_internal_external_flag      => NULL,
                      p_internal_address_line       => NULL,
                      p_type                        => i.type,
                      p_comments                    => NULL,
                      p_attribute_category          => NULL,
                      p_attribute1                  => NULL,
                      p_organization_id             => l_organization_id,
                      p_object_version_number       => l_object_version_number
                      );
                      dbms_output.put_line(l_organization_id);
                      commit;
                              begin
                                 apps.hr_organization_api.create_org_classification
                                 (p_validate                   => NULL,
                                  p_effective_date             => sysdate,
                                  p_organization_id            => l_organization_id,
                                  p_org_classif_code           =>'HR_ORG',
                                  p_org_information_id         => l_org_information_id,
                                  p_object_version_number      => l_object_version_number2
                                                           );
                                exception when others then
                                dbms_output.put_line(sqlerrm);
                                end;     
                      end loop;
exception when others then
dbms_output.put_line(sqlerrm);
end;                     
   

Create single organization, assigning classification.

declare
ln_business_group_id number := 81;
 l_organization_name varchar2(200):='TestAfzal_API' ;
  l_organization_id number ;
   l_object_version_number number;
   l_org_type number := 60;
   ln_location_id number := 142;
begin
apps.hr_organization_api.create_organization
                     (p_validate                    => NULL,
                      p_effective_date              => sysdate,
                      p_language_code               => apps.hr_api.userenv_lang,
                      p_business_group_id           => 81,
                      p_date_from                   => TO_DATE ('01-JAN-1950', 'DD-MON-YYYY'),
                      p_name                        => l_organization_name,
                      p_location_id                 => ln_location_id,
                      --p_date_to                     => sysdate,
                      p_internal_external_flag          => 'INT',
                      --p_internal_external_flag      => NULL,
                      p_internal_address_line       => NULL,
                      p_type                        => l_org_type,
                      p_comments                    => NULL,
                      p_attribute_category          => NULL,
                      p_attribute1                  => NULL,
                      p_organization_id             => l_organization_id,
                      p_object_version_number       => l_object_version_number
                      );
                      dbms_output.put_line(l_organization_id);
exception when others then
dbms_output.put_line(sqlerrm);
end;                     
   

declare
 l_organization_id number := 2031;
 l_org_information_id number:= 88302;
 l_org_classif_code varchar2(20) := 'HR_ORG';
 l_object_version_number number;
begin
apps.hr_organization_api.create_org_classification
                         (p_validate                   => NULL,
                          p_effective_date             => sysdate,
                          p_organization_id            => l_organization_id,
                          p_org_classif_code           =>l_org_classif_code,
                          p_org_information_id         => l_org_information_id,
                          p_object_version_number      => l_object_version_number
                           );
exception when others then
dbms_output.put_line(sqlerrm);
end;     

select * from HR_ORGANIZATION_INFORMATION_V
where organization_id in(2030,2031)

select * from hr_all_organization_units
where organization_id in(2030,2031)

Oracle Apps R12 SSHR query to check final action on a transaction

SELECT ACTION FROM PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680'
AND CREATION_DATE = (SELECT MAX(CREATION_DATE) FROM  PQH_SS_APPROVAL_HISTORY
WHERE TRANSACTION_ITEM_KEY ='161680')

Query for R12 Scheduled concurrent Programs

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

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