ALTER SESSION SET CURRENT_SCHEMA = APPS
/
select * from wf_comments where notification_id in (153918790,
153918791,
153831006,
153831005)
/
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
/
select * from wf_notifications where subject like 'Transfer and Promotions with Salary for Kumar, Pushpendra%'
/
SELECT * FROM PER_ALL_PEOPLE_F
--where employee_number in ('04635')
where person_id = 1066811
and trunc(sysdate) between effective_start_date and effective_end_date
/
select person_id from per_All_Assignments_f where ass_attribute16 = 'POS-496-OCT-2018-HKG-42'
/
SELECT *FROM FND_USER
--where user_id = 14169
WHERE USER_NAME = '76981'
--where employee_id = 1190518
/
select ppf.employee_number, ppf.full_name, fu.user_name, wr.status,wr.*
from per_people_f ppf,
fnd_user fu,
wf_roles wr
where ppf.person_id =fu.employee_id (+)
and ppf.person_id =wr.orig_system_id (+)
and ppf.current_employee_flag= 'Y' and fu.USER_NAME = '76981'
/
select * from per_all_assignments_f where person_id = 1066811
/
select * from hr_all_positions_f where position_id = 1407272
/
select * from fnd_lookup_values where lookup_type = 'QAG_REDES_SMA_PROCESS'
/
select * from AME_TEMP_INSERTIONS where transaction_id in ('165306') and application_id = '-386'--78630
/
select * from AME_TEMP_INSERTIONS where transaction_id in ('27603') and application_id = '-526'--78630
/
select * from AME_TEMP_INSERTIONS where transaction_id in ('11327') and application_id = '-486'--78630
/
select * from AME_TRANS_APPROVAL_history where transaction_id = ('27603') order by order_number asc --120259239
/
select * from ame_exceptions_log where transaction_id = '128477915' order by 1 desc -- 1005499 --33209 --119828206
/
select * from wf_roles where display_name like '%Franciscus%Cornelis%'
/
select * from wf_local_roles where display_name like '%Franciscus%Cornelis%'
/
select * from AME_TEMP_OLD_APPROVer_lists where transaction_id in (27603) and application_id = '-526' order by transaction_id
/
select * from AME_TEMP_OLD_APPROVer_lists
where application_id in ('-486','-386','-488','-526')and name = '46243' and approval_status is null
order by application_id
/
select * from ame_calling_apps where application_name like 'QAG%'
/
--QAG SRF Transaction Type QAG_SRF_TRAN_TYPE1 -306
--QAG HR Payroll Checklist Details QAG_HR_PC_TRANS_TYPE_DET -366
--QAG HR SRF Release Process QAG_SRF_RELEASE_TRANS_TYPE -546
--QAG HR SRF Delete Process QAG_SRF_BULKDELETE_TRANS_TYPE -566
--QAG HR Joining Salary Advance Request QAG_HR_JONRQ_TRANS_TYPE -606
--QAG HR Leave Salary Advance Request QAG_HR_LVRQT_TRANS_TYPE -608
--QAG SRF Transaction Details Using Apps Standard QAG_SRF_TRANSACTION_DET -346
--QAG HR WF Admin Module Process QAG_HR_WF_ADMIN_TYPE -506
--QAG OS Peer Comparison NewHire Process QAG_OS_PC_NEWHIRE_TYPE -508
--QAG HR MSMA OS New Joiner Process QAG_MSMA_OS_NJ_TRANS_TYPE -486
--QAG HR OS Promotion SMA Process QAG_HR_PROM_SMA_OS_SAL_TYPE -488
--QAG Peer Comparison Promotion Process QAG_PC_PROM_TYPE -446
--QAG Mass SMA Transaction Type QAG_MSMA_TRANS_TYPE -386
--QAG Peer Comparison NewHire Process QAG_PC_NEWHIRE_TYPE -426
--QAG HR QRnSubs Promotion SMA Process QAG_HR_PROM_SMA_QRNSUBS_SAL_TYPE -526
--QAG OS Peer Comparison Promotion Process QAG_OS_PC_PROM_TYPE -528
/
select * from per_all_assignments_f where person_id = 1005499
/
select * from per_all_people_f
--where person_id = 1005499 -- 64099
where employee_number = '31026'
/
select * from fnd_user where user_id = 201079 -- Kishore, Ms. Kavya
/
select * from qag_Srf_headers_t where item_key in (
SELECT item_key FROM WF_NOTIFICATIONS --where from_user = '59112'
--WHERE NOTIFICATION_ID = 149441657 and message_type = 'QAGHRSRF'
where responder = '31026' and message_type in ('HRSSA') and trunc(begin_date) >= to_date('01-SEP-2021')) and status in ('PUBLISHED','APPROVED') order by creation_Date desc
--and subject like 'Please%'
/
select * from WF_ROUTING_RULES
where role = '89000' order by 4 desc
--where action_argument = '89000'
/
select * from all_objects where object_name like upper('%routing%rule%')
/
select * from wf_notifications where status = 'OPEN'
--and to_user = 'Putter, Robert'
and original_recipient = '83477' and message_type in ('HRSSA','QAGOSSMA','QAGMSMA','QAGHRSRF','QAGPROM','QAGPC','QAGOSPC','QAGSRFRE') order by begin_date desc
/
select begin_date,From_user,To_user,subject,item_key,message_type from wf_notifications where status = 'OPEN'
--and to_user = 'Putter, Robert'
and original_recipient = 'T27174' and message_type in ('QAGMSMA','QAGPROM','HRSSA') order by begin_date desc
/
select * from wf_notifications where notification_id = 165808472
/
select * from wf_notifications where from_user = 'Wemyss, Kimberley Neville'
/
SELECT * FROM WF_NOTIFICATIONS WHERE ITEM_KEY = '8675282' and message_type in ('HRSSA','QAGOSSMA','QAGMSMA','QAGHRSRF','QAGPROM','QAGPC','QAGOSPC','QAGSRFRE') ORDER BY BEGIN_DATE DESC --6923747 --6927003
/
SELECT * FROM WF_NOTIFICATIONS WHERE ITEM_KEY in (SELECT item_key FROM WF_NOTIFICATIONS WHERE NOTIFICATION_ID = 158206104 ) ORDER BY BEGIN_DATE DESC --6923747 --6927003
/
SELECT * FROM FND_USER_PREFERENCES WHERE USER_NAME = '73434'
/
apps.per_asg_bus2
/
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
/
SELECT * FROM HR_API_TRANSACTIONS
--WHERE SELECTED_PERSON_ID = 929334
--WHERE TRANSACTION_ID in (119143176)
where item_key = '8028456'
/
select * from HR_API_TRANSACTION_STEPS where transaction_id = 121095272
/
select * from HR_API_TRANSACTION_VALUES where transaction_step_id = 123756483
/
SELECT A.STATUS,b.full_name,b.employee_number,a.transaction_id,item_key,process_name,a.creation_Date,b.person_id,a.created_by
FROM HR_API_TRANSACTIONS A, PER_ALL_PEOPLE_F B
WHERE A.SELECTED_PERSON_ID = B.PERSON_ID
--AND B.EMPLOYEE_NUMBER IN ('70323')
and a.transaction_id = 120697253
--and item_key in (7097945)
AND A.STATUS = 'Y'
and process_name in ('QAG_HR_TRAN_PRO_SAL_JSP_PRC','QAG_HR_TRA_PRO_NOSAL_JSP_PRC','QAG_SRF_CONCURRENT_JSP_PRC')
and trunc(sysdate) between effective_start_date and effective_end_date
order by creation_date desc
/
select * from HR_API_TRANSACTIONS where process_name like '%NOSAL%'
/
SELECT b.full_name,b.employee_number,count(a.selected_person_id)
FROM HR_API_TRANSACTIONS A, PER_ALL_PEOPLE_F B
WHERE A.SELECTED_PERSON_ID = B.PERSON_ID
--AND B.EMPLOYEE_NUMBER IN ('63632')
AND A.STATUS = 'Y'
and process_name in ('QAG_HR_TRAN_PRO_SAL_JSP_PRC','QAG_HR_TRA_PRO_NOSAL_JSP_PRC')
and trunc(sysdate) between effective_start_date and effective_end_date
group by b.employee_number,b.full_name having count(*) > 1
--51013
--00780
/
SELECT A.* FROM PQH_SS_TRANSACTION_HISTORY A
--where item_key = '7460931'
where transaction_history_id = 119595160
/
SELECT A.* FROM PQH_SS_TRANSACTION_HISTORY A , PER_ALL_PEOPLE_F B
WHERE A.SELECTED_PERSON_ID = B.PERSON_ID
--AND B.EMPLOYEE_NUMBER IN ('70323')
AND TRANSACTION_HISTORY_ID in (120697253)
--where item_key = 7032310
--and selected_person_id = 929334
and process_name in ('QAG_HR_TRAN_PRO_SAL_JSP_PRC','QAG_HR_TRA_PRO_NOSAL_JSP_PRC')
and trunc(sysdate) between b.effective_start_date and b.effective_end_date
order by a.creation_date desc
/
select * from PQH_SS_VALUE_HISTORY where step_history_id in (119213239,
119213240,
119213242,
119213256)
/
select * from PQH_SS_STEP_HISTORY where transaction_history_id = 116989036
/
SELECT c.* FROM PQH_SS_TRANSACTION_HISTORY A, PQH_SS_STEP_HISTORY B, PQH_SS_VALUE_HISTORY C
WHERE A.TRANSACTION_HISTORY_ID = B.TRANSACTION_HISTORY_ID
AND B.STEP_HISTORY_ID = C.STEP_HISTORY_ID
AND NAME = 'P_GRADE_NAME' AND VALUE LIKE ('CC%')
/
select * from qag_hr_sma_transaction_tab where transaction_id in (
SELECT a.transaction_id
FROM HR_API_TRANSACTIONS A, HR_API_TRANSACTION_STEPS B, HR_API_TRANSACTION_VALUES C
WHERE A.TRANSACTION_ID = B.TRANSACTION_ID
AND B.TRANSACTION_STEP_ID = C.TRANSACTION_STEP_ID
AND NAME = 'P_GRADE_NAME' AND VARCHAR2_VALUE in ('FD.04') and original_varchar2_value in ('FD.02')
and trunc(a.creation_Date) between to_Date('01-APR-2018') and to_date('08-JUL-2018')
--and a.selected_person_id in (select d.person_id from per_all_people_F d where employee_number = '53458'
--and trunc(sysdate) between d.effective_start_date and d.effective_end_date)
AND A.STATUS = 'Y')
/
qag_sma_wf_status_rep_new_pkg
/
update HR_API_TRANSACTIONS set status = 'D' where TRANSACTION_ID = 117678353
/
SELECT * FROM WF_NOTIFICATIONS WHERE ITEM_KEY IN (SELECT ITEM_KEY FROM QAG_MSMA_ALL_BATCH_T
where applicant_number in (86037)) ORDER BY BEGIN_DATE DESC --6923747 --6927003
/
select * from wf_routing_rules where role = '46243' order by begin_date desc
/
SELECT ApproverGroupEO.APPROVAL_GROUP_ID,
ApproverGroupEO.START_DATE,
ApproverGroupEO.END_DATE,
ApproverGroupConfigEO.APPROVAL_GROUP_ID AS APPROVAL_GROUP_ID1,
ApproverGroupConfigEO.APPLICATION_ID,
ApproverGroupConfigEO.START_DATE AS START_DATE1,
ApproverGroupConfigEO.END_DATE AS END_DATE1,
ApproverGroupEO.NAME,
ApproverGroupEO.USER_APPROVAL_GROUP_NAME,
ApproverGroupEO.CREATED_BY,
ApproverGroupEO.DESCRIPTION,
ApproverGroupEO.QUERY_STRING,
ApproverGroupEO.IS_STATIC,
ApproverGroupEO.OBJECT_VERSION_NUMBER,
ApproverGroupConfigEO.VOTING_REGIME,
ApproverGroupConfigEO.ORDER_NUMBER,
ApproverGroupConfigEO.CREATED_BY AS CREATED_BY1,
ApproverGroupConfigEO.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_NUMBER1,
QryLookup.Meaning AS USAGE_TYPE,
VotingLookup.Meaning AS VOTING_REGIME_TL
FROM AME_APPROVAL_GROUPS_VL ApproverGroupEO,
AME_APPROVAL_GROUP_CONFIG APPROVERGROUPCONFIGEO,
apps.FND_LOOKUPS QRYLOOKUP,
apps.FND_LOOKUPS VotingLookup
WHERE ApproverGroupEO.APPROVAL_GROUP_ID = ApproverGroupConfigEO.APPROVAL_GROUP_ID
AND QryLookup.lookup_type = 'AME_QUERY_USAGE_TYPE'
AND QryLookup.lookup_code = ApproverGroupEO.is_static
AND VotingLookup.lookup_type = 'AME_APG_VOTING_REGIME'
AND VotingLookup.lookup_code = ApproverGroupConfigEO.VOTING_REGIME
and sysdate between approvergroupeo.start_date and nvl(approvergroupeo.end_date -(1/84600),sysdate)
AND SYSDATE BETWEEN APPROVERGROUPCONFIGEO.START_DATE AND NVL(APPROVERGROUPCONFIGEO.END_DATE-(1/84600),SYSDATE)
--and ApproverGroupEO.QUERY_STRING like '%83227%'
AND APPROVERGROUPEO.APPROVAL_GROUP_ID = 133032
--AND APPROVERGROUPEO.NAME like 'QAG_SRF%SUPER%'
--AND ApproverGroupConfigEO.APPLICATION_ID = '-526'--'-488'
/
select 'person_id:'||qag_hr_qas_approval_pkg.get_qas_second_approver(:transactionId) from dual -- person_id:1017877
/
SELECT *
FROM
(SELECT wias.item_type AS ITEM_TYPE,
wias.item_key AS ITEM_KEY,
wias.process_activity AS PROCESS_ACTIVITY,
wias.ROWID AS ROW_ID,
'R' AS ROW_SOURCE,
wias.NOTIFICATION_ID AS NOTIF_ID,
DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER) AS ASSIGNED_USER,
wias.ACTIVITY_STATUS AS ACTIVITY_STATUS,
wias.ACTIVITY_RESULT_CODE AS ACTIVITY_RESULT_CODE,
wias.EXECUTION_TIME AS EXECUTION_TIME,
wias.BEGIN_DATE AS BEGIN_DATE,
wias.END_DATE AS END_DATE,
wias.DUE_DATE AS DUE_DATE,
WL.MEANING AS STATUS_DISPLAY,
wa.name AS ACTIVITY_NAME,
wa.display_name AS ACTIVITY_DISPLAY,
wi.user_key AS USER_KEY,
wa2.name AS PARENT_ACTIVITY,
DECODE(wa2.name, 'ROOT', '', wa2.display_name) AS PARENT_ACTIVITY_DISPLAY_NAME,
wa.type AS ACTIVITY_TYPE,
wf_fwkmon.getroleemailaddress(DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER)) AS ROLE_EMAIL_ADDRESS,
--wf_directory.getroledisplayname2(DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wias.ASSIGNED_USER),wias.ASSIGNED_USER)) AS ROLE_DISPLAY_NAME,
DECODE(wias.ACTIVITY_RESULT_CODE, '#NULL', 'WfNoCloseDate', 'WfCloseDate') AS END_DATE_COL_SWITCH,
DECODE(wias.ACTIVITY_STATUS, 'ERROR', 'WfStatusErrorText', 'WfStatusNoterrText') AS STATUS_COLUMN_SWITCH,
DECODE(wias.ACTIVITY_STATUS, 'ERROR', 'WfStatusError', 'COMPLETE', 'WfStatusComplete', 'SUSPEND', 'WfStatusSuspended', 'WAITING', 'WfStatusWaiting', 'DEFERRED', 'WfStatusDeferred', 'NOTIFIED', 'WfStatusNotified', 'WfStatusActive') AS IMAGE_COLUMN_SWITCH,
wf_core.activity_result(wa.result_type, DECODE(wias.ACTIVITY_RESULT_CODE, '#NULL', NULL,wias.ACTIVITY_RESULT_CODE)) AS RESULT_DISPLAY,
wpa.activity_item_type AS ACTIVITY_ITEM_TYPE,
DECODE(wa.type, 'NOTICE', DECODE(wias.ACTIVITY_STATUS, 'NOTIFIED','WfReassignEnabled', 'ERROR', 'WfReassignEnabled', 'WfReassignDisabled'), 'WfReassignDisabled') AS REASSIGN_SWITCHER,
DECODE(wias.ACTIVITY_STATUS, 'NOTIFIED', 'N', 'ACTIVE', 'N', 'ERROR', 'N', 'WAITING', 'N', 'DEFERRED', 'N', 'Y') AS SELECT_DISABLED,
DECODE(wa.type, 'PROCESS', DECODE(wias.ACTIVITY_STATUS, 'SUSPEND', 'WfResumeEnabled', 'COMPLETE', 'WfSuspResDisabled', 'WfSuspendEnabled'), 'WfSuspResDisabled') AS SUSPEND_SWITCHER,
wa.expand_role AS EXPAND_ROLE,
DECODE(wn.STATUS, 'OPEN', nvl2(wn.more_info_role,WF_CORE.Translate('WFNTF_MOREINFO_REQUESTED'),wnl.MEANING), wnl.MEANING) AS NOTIFICATION_STATUS
FROM WF_ITEM_ACTIVITY_STATUSES wias
LEFT JOIN wf_notifications wn
ON wias.notification_id = wn.notification_id
LEFT JOIN wf_lookups wnl
ON wnl.lookup_code = wn.STATUS
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wias.ACTIVITY_STATUS
AND wl.lookup_type = 'WFENG_STATUS'
AND wias.ITEM_TYPE = wi.item_type
AND wias.ITEM_KEY = wi.item_key
AND wias.PROCESS_ACTIVITY = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date AND NVL(wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND WPA.PROCESS_VERSION = WA2.VERSION
AND wias.ITEM_TYPE in ('QAGMSMA','HRSSA')
AND WIAS.ITEM_KEY = :2
UNION ALL
SELECT wiash.item_type AS ITEM_TYPE,
wiash.item_key AS ITEM_KEY,
wiash.process_activity AS PROCESS_ACTIVITY,
wiash.ROWID AS ROW_ID,
'H' AS ROW_SOURCE,
wiash.NOTIFICATION_ID AS NOTIF_ID,
DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER) AS ASSIGNED_USER,
wiash.ACTIVITY_STATUS AS ACTIVITY_STATUS,
wiash.ACTIVITY_RESULT_CODE AS ACTIVITY_RESULT_CODE,
wiash.EXECUTION_TIME AS EXECUTION_TIME,
wiash.BEGIN_DATE AS BEGIN_DATE,
wiash.END_DATE AS END_DATE,
wiash.DUE_DATE AS DUE_DATE,
wl.meaning AS STATUS_DISPLAY,
wa.name AS ACTIVITY_NAME,
wa.display_name AS ACTIVITY_DISPLAY,
wi.user_key AS USER_KEY,
wa2.name AS PARENT_ACTIVITY,
DECODE(wa2.name, 'ROOT', '', wa2.display_name) AS PARENT_ACTIVITY_DISPLAY_NAME,
wa.type AS ACTIVITY_TYPE,
wf_fwkmon.getroleemailaddress(DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER)) AS ROLE_EMAIL_ADDRESS,
-- wf_directory.getroledisplayname2(DECODE(wn.STATUS, 'OPEN', NVL(wn.more_info_role,wiash.ASSIGNED_USER),wiash.ASSIGNED_USER)) AS ROLE_DISPLAY_NAME,
DECODE(wiash.ACTIVITY_RESULT_CODE, '#NULL', 'WfNoCloseDate', 'WfCloseDate') AS END_DATE_COL_SWITCH,
DECODE(wiash.ACTIVITY_STATUS, 'ERROR', 'WfStatusErrorText', 'WfStatusNoterrText') AS STATUS_COLUMN_SWITCH,
DECODE(wiash.ACTIVITY_STATUS, 'ERROR', 'WfStatusError', 'COMPLETE', 'WfStatusComplete', 'SUSPEND', 'WfStatusSuspended', 'WAITING', 'WfStatusWaiting', 'DEFERRED', 'WfStatusDeferred', 'NOTIFIED', 'WfStatusNotified', 'WfStatusActive') AS IMAGE_COLUMN_SWITCH,
wf_core.activity_result(wa.result_type, DECODE(wiash.ACTIVITY_RESULT_CODE, '#NULL', NULL, wiash.ACTIVITY_RESULT_CODE)) AS RESULT_DISPLAY,
wpa.activity_item_type AS ACTIVITY_ITEM_TYPE,
DECODE(wa.type, 'NOTICE', DECODE(wiash.ACTIVITY_STATUS, 'NOTIFIED','WfReassignEnabled', 'ERROR', 'WfReassignEnabled', 'WfReassignDisabled'), 'WfReassignDisabled') AS REASSIGN_SWITCHER,
DECODE(wiash.ACTIVITY_STATUS, 'NOTIFIED', 'N', 'ACTIVE', 'N', 'ERROR', 'N', 'WAITING', 'N', 'DEFERRED', 'N', 'Y') AS SELECT_DISABLED,
DECODE(wa.type, 'PROCESS', DECODE(wiash.ACTIVITY_STATUS, 'SUSPEND', 'WfResumeEnabled', 'COMPLETE', 'WfSuspResDisabled', 'WfSuspendEnabled'), 'WfSuspResDisabled') AS SUSPEND_SWITCHER,
wa.expand_role AS EXPAND_ROLE,
DECODE(wn.STATUS, 'OPEN', nvl2(wn.more_info_role,WF_CORE.Translate('WFNTF_MOREINFO_REQUESTED'),wnl.MEANING), wnl.MEANING) AS NOTIFICATION_STATUS
FROM WF_ITEM_ACTIVITY_STATUSES_H wiash
LEFT JOIN wf_notifications wn
ON wiash.notification_id = wn.notification_id
LEFT JOIN wf_lookups wnl
ON wnl.lookup_code = wn.STATUS
AND wnl.lookup_type = 'WF_NOTIFICATION_STATUS',
wf_lookups wl,
wf_items wi,
wf_activities_vl wa,
wf_process_activities wpa,
wf_activities_vl wa2
WHERE wl.lookup_code = wiash.ACTIVITY_STATUS
AND wl.lookup_type = 'WFENG_STATUS'
AND wiash.ITEM_TYPE = wi.item_type
AND wiash.ITEM_KEY = wi.item_key
AND wiash.PROCESS_ACTIVITY = wpa.instance_id
AND wpa.activity_name = wa.name
AND wpa.activity_item_type = wa.item_type
AND wi.begin_date BETWEEN wa.begin_date AND NVL(wa.end_date, wi.begin_date)
AND wpa.process_name = wa2.name
AND wpa.process_item_type = wa2.item_type
AND WPA.PROCESS_VERSION = WA2.VERSION
AND wiash.ITEM_TYPE in ('QAGMSMA','HRSSA')
AND WIASH.ITEM_KEY = :4
) QRSLT
ORDER BY 11 DESC,
10 DESC
/
SELECT rownum H_SEQUENCE,
H_NOTIFICATION_ID,
H_FROM_ROLE,
--H_FROM_USER,
H_TO_ROLE,
H_TO_USER,
H_ACTION,
H_COMMENT,
H_ACTION_DATE
FROM
(SELECT H_NOTIFICATION_ID,
H_FROM_ROLE,
-- H_FROM_USER,
H_TO_ROLE,
H_TO_USER,
H_ACTION,
H_COMMENT,
H_ACTION_DATE
FROM
(SELECT 99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
-- wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
WF_CORE.TRANSLATE('WF_SYSTEM') H_TO_USER,
wf_core.activity_result(A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE) H_ACTION,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = IAS.NOTIFICATION_ID
AND name = 'WF_NOTE'
) H_COMMENT,
NVL(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE
FROM WF_ITEM_ACTIVITY_STATUSES IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
WHERE IAS.ITEM_TYPE in ('QAGMSMA','HRSSA','QAGPC','QAGPROM','QAGOSSMA')
AND IAS.ITEM_KEY = :2
AND IAS.ITEM_TYPE = I.ITEM_TYPE
AND IAS.ITEM_KEY = I.ITEM_KEY
AND IAS.ACTIVITY_RESULT_CODE IS NOT NULL
AND IAS.ACTIVITY_RESULT_CODE NOT IN( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
AND I.BEGIN_DATE BETWEEN A.BEGIN_DATE AND NVL(A.END_DATE, I.BEGIN_DATE)
AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_NAME = A.NAME
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
UNION ALL
SELECT 99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
-- wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
WF_CORE.TRANSLATE('WF_SYSTEM') H_TO_USER,
wf_core.activity_result(A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE) H_ACTION,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = IAS.NOTIFICATION_ID
AND name = 'WF_NOTE'
) H_COMMENT,
NVL(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE
FROM WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
WHERE IAS.ITEM_TYPE in ('QAGMSMA','HRSSA','QAGPC','QAGPROM','QAGOSSMA')
AND IAS.ITEM_KEY = :5
AND IAS.ITEM_TYPE = I.ITEM_TYPE
AND IAS.ITEM_KEY = I.ITEM_KEY
AND IAS.ACTIVITY_RESULT_CODE IS NOT NULL
AND IAS.ACTIVITY_RESULT_CODE NOT IN( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
AND I.BEGIN_DATE BETWEEN A.BEGIN_DATE AND NVL(A.END_DATE, I.BEGIN_DATE)
AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_NAME = A.NAME
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
UNION ALL
SELECT C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
-- C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
WF_CORE.TRANSLATE(C.ACTION) H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
FROM WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C
WHERE IAS.ITEM_TYPE in ('QAGMSMA','HRSSA','QAGPC','QAGPROM','QAGOSSMA')
AND IAS.ITEM_KEY = :8
AND IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
AND C.ACTION NOT IN( 'RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
UNION ALL
SELECT C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
-- C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
WF_CORE.TRANSLATE(C.ACTION) H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE
FROM WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C
WHERE IAS.ITEM_TYPE in ('QAGMSMA','HRSSA','QAGPC','QAGPROM','QAGOSSMA')
AND IAS.ITEM_KEY = :11
AND IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
AND C.ACTION NOT IN( 'RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
)
ORDER BY H_ACTION_DATE,
H_NOTIFICATION_ID,
H_SEQUENCE
) WHERE H_NOTIFICATION_ID IS NOT NULL ORDER BY H_ACTION_DATE DESC
/
--Approved list records
---------------------
SELECT * FROM QR_GEMS.QAG_GEMS_SRF_TRACKING_DET WHERE srf_number in (
select srf_number from qag_Srf_headers_t where item_key in
(SELECT ITEM_KEY
FROM
(SELECT H_NOTIFICATION_ID,
H_FROM_ROLE,
-- H_FROM_USER,
H_TO_ROLE,
H_TO_USER,
H_ACTION,
H_COMMENT,
H_ACTION_DATE,ITEM_KEY
FROM
(SELECT 99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
-- wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
WF_CORE.TRANSLATE('WF_SYSTEM') H_TO_USER,
wf_core.activity_result(A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE) H_ACTION,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = IAS.NOTIFICATION_ID
AND name = 'WF_NOTE'
) H_COMMENT,
NVL(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE,IAS.ITEM_KEY
FROM WF_ITEM_ACTIVITY_STATUSES IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
WHERE IAS.ITEM_TYPE in ('HRSSA')
AND IAS.ASSIGNED_USER = '31026'
AND IAS.ITEM_TYPE = I.ITEM_TYPE
AND IAS.ITEM_KEY = I.ITEM_KEY
AND IAS.ACTIVITY_RESULT_CODE IS NOT NULL
AND IAS.ACTIVITY_RESULT_CODE NOT IN( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
AND I.BEGIN_DATE BETWEEN A.BEGIN_DATE AND NVL(A.END_DATE, I.BEGIN_DATE)
AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_NAME = A.NAME
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
UNION ALL
SELECT 99999999 H_SEQUENCE,
IAS.NOTIFICATION_ID H_NOTIFICATION_ID,
IAS.ASSIGNED_USER H_FROM_ROLE,
-- wf_directory.getRoleDisplayName2(IAS.ASSIGNED_USER) H_FROM_USER,
'WF_SYSTEM' H_TO_ROLE,
WF_CORE.TRANSLATE('WF_SYSTEM') H_TO_USER,
wf_core.activity_result(A.RESULT_TYPE, IAS.ACTIVITY_RESULT_CODE) H_ACTION,
(SELECT text_value
FROM wf_notification_attributes
WHERE notification_id = IAS.NOTIFICATION_ID
AND name = 'WF_NOTE'
) H_COMMENT,
NVL(IAS.END_DATE, IAS.BEGIN_DATE) H_ACTION_DATE,IAS.ITEM_KEY
FROM WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_ACTIVITIES A,
WF_PROCESS_ACTIVITIES PA,
WF_ITEMS I
WHERE IAS.ITEM_TYPE in ('HRSSA')
AND IAS.ASSIGNED_USER = '31026'
AND IAS.ITEM_TYPE = I.ITEM_TYPE
AND IAS.ITEM_KEY = I.ITEM_KEY
AND IAS.ACTIVITY_RESULT_CODE IS NOT NULL
AND IAS.ACTIVITY_RESULT_CODE NOT IN( '#EXCEPTION', '#FORCE', '#MAIL', '#NULL', '#STUCK', '#TIMEOUT')
AND I.BEGIN_DATE BETWEEN A.BEGIN_DATE AND NVL(A.END_DATE, I.BEGIN_DATE)
AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
AND PA.ACTIVITY_NAME = A.NAME
AND PA.ACTIVITY_ITEM_TYPE = A.ITEM_TYPE
UNION ALL
SELECT C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
-- C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
WF_CORE.TRANSLATE(C.ACTION) H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE,IAS.ITEM_KEY
FROM WF_ITEM_ACTIVITY_STATUSES IAS,
WF_COMMENTS C
WHERE IAS.ITEM_TYPE in ('HRSSA')
AND IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
AND C.ACTION NOT IN( 'RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
AND C.FROM_ROLE = '31026'
UNION ALL
SELECT C.SEQUENCE H_SEQUENCE,
C.NOTIFICATION_ID H_NOTIFICATION_ID,
C.FROM_ROLE H_FROM_ROLE,
-- C.FROM_USER H_FROM_USER,
C.TO_ROLE H_TO_ROLE,
C.TO_USER H_TO_USER,
WF_CORE.TRANSLATE(C.ACTION) H_ACTION,
C.USER_COMMENT H_COMMENT,
C.COMMENT_DATE H_ACTION_DATE,IAS.ITEM_KEY
FROM WF_ITEM_ACTIVITY_STATUSES_H IAS,
WF_COMMENTS C
WHERE IAS.ITEM_TYPE in ('HRSSA')
AND C.FROM_ROLE = '31026'
AND IAS.NOTIFICATION_ID = C.NOTIFICATION_ID
AND C.ACTION NOT IN( 'RESPOND', 'RESPOND_WA', 'RESPOND_RULE', 'SEND')
)
ORDER BY H_ACTION_DATE,
H_NOTIFICATION_ID,
H_SEQUENCE
) WHERE H_NOTIFICATION_ID IS NOT NULL and h_action = 'Approve' )
and trunc(creation_Date) >= to_date('01-SEP-2021') and status not in ('PUBLSIHED','APPROVED')) order by srf_creation_Date desc
/
select * from fnd_lookup_values where tag = '01989'
--Mr. Mahindar Atma Singh Singh
/
select * from per_all_people_f
where person_id = 319503
--where employee_number = '49109'
/
select * from fnd_lookup_values where tag = '83227'
/
select * from QR_SRF.QAG_HR_AME_HRCHY_HEADER_T where os_flag = 'N' and end_Date is null
/
SELECT * FROM QAG_MSMA_ALL_BATCH_T where transaction_id in
(select transaction_id from AME_TEMP_OLD_APPROVer_lists
where application_id in ('-386')and name = '46243' and approval_status is null) and status not in ('REJECTED','NEW')
and trunc(creation_date) >= to_date('01-SEP-2018') -- 5 QR New Joiner
and item_key not in (select item_key from wf_notifications where status = 'OPEN'
and original_recipient = '46243' and message_type in ('QAGMSMA'))
--order by application_id
/
SELECT * FROM QR_SRF.QAG_HR_OS_MSMA_ALL_BATCH_T where transaction_id in
(select transaction_id from AME_TEMP_OLD_APPROVer_lists
where application_id in ('-486')and name = '46243' and approval_status is null ) and status not in ('REJECTED')
and trunc(creation_date) >= to_date('01-SEP-2018') -- 7 OS New Joiner
/
select * from QAG_HR_OS_PROM_SMA_ALL_BATCH_T where transaction_id in
(select transaction_id from AME_TEMP_OLD_APPROVer_lists
where application_id in ('-488')and name = '46243' and approval_status is null) and status not in ('REJECTED','DRAFT')
and trunc(creation_date) >= to_date('01-SEP-2018') -- 10 OS Promotion
/
select * from QAG_HR_PROM_SMA_ALL_BATCH_T where transaction_id in -- 11 QR Promotion
(select transaction_id from AME_TEMP_OLD_APPROVer_lists
where application_id in ('-526')and name = '46243' and approval_status is null) and status not in ('REJECTED','DRAFT','PROMOTED')
and trunc(creation_date) >= to_date('01-MAR-2018')
and item_key not in (select item_key from wf_notifications where status = 'OPEN'
and original_recipient = '46243' and message_type in ('QAGPROM'))
/
alter session set current_schema = apps
/
select * from fnd_lookup_values where lookup_type = 'QAG_HR_SMA_OS_DIV_CC_MAPING'
/
select * from all_objects where object_name like 'QAG%HRCHY%'
/
select * from QAG_HR_OS_AME_HRCHY_MASTER_T where employee_number = '95417' and end_date is null
/
select * from QAG_HR_SMA_HRCHY_SETUP where grade_range like 'DI%' and grade_range not in ('DI 01-09','DI 10-13')
/
select * from QR_SRF.QAG_HR_AME_HRCHY_LINES_T where AME_HRCHY_HDR_ID in
(select AME_HRCHY_HDR_ID from QR_SRF.QAG_HR_AME_HRCHY_HEADER_T where os_flag = 'N' and module = 'SMA' and ame_hrchy_hdr_id = 5865)
/
select * from QR_SRF.QAG_HR_AME_HRCHY_HEADER_T where grade_id = 200 grade_range like 'CC%'
where os_flag = 'Y' and module = 'SMA' and grade_Range = 'MV 01-06'
/
select * from per_Grades where name like 'JF%'
/
select * from qag_srf_org_matrix_v
/
select * from QR_SRF.QAG_HR_AME_VALIDATIONS_T
/
select * from per_all_people_F where employee_number = '42791'
/
select * from QR_SRF.QAG_HR_AME_HRCHY_HEADER_T where department_id in(
select department_id,department_name,bu_country_name,bu_country_id from fnd_lookup_values a,qag_srf_org_matrix_v b
where a.description = b.department_name
and lookup_type = 'QAG_SMA_GCEO_APPROVAL_DEPTS'
and enabled_flag = 'Y')
/
select * from fnd_lookup_values
where lookup_type = 'QAG_SMA_GCEO_APPROVAL_DEPTS'
--where description = 'Cabin Crew - Recruitment'
/
select * from QR_SRF.QAG_HR_AME_HRCHY_HEADER_T
--where grade_range = 'TR 01-06' order by 1 desc
where 1 = 1 --and country_company_id = 87
and AME_HRCHY_HDR_ID = 13127
--and grade_range like 'DI%' and end_Date is null order by 5 desc
--and department_id = 3994
/
select * from QR_SRF.QAG_HR_AME_HRCHY_LINES_T
--where employee_number = '95417'
--where attribute4 = 'HR'
where AME_HRCHY_HDR_ID in (13127) order by ame_hrchy_line_id asc
/
select * from QR_SRF.QAG_HR_AME_HRCHY_LINES_T where AME_HRCHY_HDR_ID in (1568,1580,1592) and appr_level = 30
--where grade_range like 'FD%'
/
SEQUENCE
--------
QAG_HR_AME_VLDTN_ID_S
QAG_HR_AME_HRCHY_ID_S
QAG_HR_AME_HRCHY_HDR_ID_S
QAG_HR_AME_HRCHY_LINE_ID_S
/
PACKAGE
-------
QAG_HR_AMEUTIL_SS
QAG_HR_AME_HRCHY_PKG
/
Hierarchy Query
---------------
SELECT *
FROM
(SELECT HDR.AME_HRCHY_HDR_ID,
HDR.REGION_ID,
HDR.COUNTRY_COMPANY_ID,
HDR.STATION_DIVISION_ID,
HDR.DEPARTMENT_ID,
HDR.COST_CENTER,
lines.START_DATE,
lines.END_DATE,
HDR.PROCESS_TYPE,
HDR.OS_FLAG,
HDR.AME_IND,
HDR.AME_CATEGORY,
QAG_MSMA_UTILITIES_PKG.GET_ORG_NAME(HDR.REGION_ID) REGION,
QAG_MSMA_UTILITIES_PKG.GET_ORG_NAME(HDR.COUNTRY_COMPANY_ID) COUNTRY,
NULL STATION,
QAG_MSMA_UTILITIES_PKG.GET_ORG_NAME(HDR.DEPARTMENT_ID) DEPT,
NVL(QAG_MSMA_UTILITIES_PKG.GET_FULL_NAME_T(LINES.PERSON_ID),
(SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE IN ('QAG_HR_SMA_APPROVERS_INFO','QAG_HR_SMA_OS_APPROVER_TYPE')
AND ENABLED_FLAG = 'Y'
AND lookup_code = LINES.EMPLOYEE_NUMBER
AND sysdate BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,sysdate+1)
)) EMP_NAME,
LINES.AME_HRCHY_LINE_ID,
LINES.APPR_LEVEL,
LINES.PERSON_ID,
LINES.EMPLOYEE_NUMBER,
(select current_employee_flag from per_all_people_f papf where papf.person_id = lines.person_id
and trunc(sysdate) between effective_Start_Date and effective_end_Date) current_employee_flag,
LINES.START_DATE L_START_DATE,
LINES.END_DATE L_END_DATE,
QAG_MSMA_UTILITIES_PKG.GET_GRADE_NAME(HDR.GRADE_ID) grade,
QAG_MSMA_UTILITIES_PKG.GET_POSITION_NAME(HDR.POSITION_ID) position,
HDR.GRADE_ID,
REDUCD Min,
mac mid,
NORML Max ,
grade_range,
QAG_HR_AME_HRCHY_PKG.get_ht_div_station(HDR.STATION_DIVISION_ID) ex_div_name,
DECODE (
(SELECT COUNT(1) FROM QAG_HR_AME_VALIDATIONS_T vald WHERE ame_hrchy_hdr_id=HDR.AME_HRCHY_HDR_ID
AND NVL(vald.END_DATE,TRUNC(SYSDATE)) >=TRUNC(SYSDATE)
AND AME_VALDTN_CODE! ='1'
),0,'No','Yes') vldYN,
HDR.module,
LINES.attribute1 a_max
FROM QAG_HR_AME_HRCHY_HEADER_T HDR,
QAG_HR_AME_HRCHY_LINES_T LINES
WHERE HDR.AME_HRCHY_HDR_ID = LINES.AME_HRCHY_HDR_ID
AND NVL(HDR.END_DATE,TRUNC(SYSDATE)) > = TRUNC(sysdate)
AND NVL(LINES.END_DATE,TRUNC(SYSDATE)) > = TRUNC(sysdate)
ORDER BY HDR.REGION_ID,
HDR.COUNTRY_COMPANY_ID,
HDR.STATION_DIVISION_ID,
LINES.end_date,
LINES.APPR_LEVEL
) QRSLT
where AME_HRCHY_HDR_ID = 7115
/
WHERE (region_id = 6053
AND COUNTRY_COMPANY_ID = 594
AND trim(STATION_DIVISION_ID) IS NULL
AND trim(COST_CENTER) IS NULL
AND DEPARTMENT_ID IS NULL
AND GRADE_ID IS NULL
AND AME_CATEGORY = 'EXCPN'
AND GRADE_RANGE = 'QR 01-06'
AND PROCESS_TYPE = 'PRM'
AND OS_FLAG = 'N'
AND MODULE = 'SMA'
AND QAG_HR_AME_HRCHY_PKG.qag_hr_get_valid_hdr_flag (AME_HRCHY_HDR_ID,'') = 'Y'
AND END_DATE IS NULL)
/
SELECT t.*
FROM
(SELECT *
FROM
(SELECT hdr.transaction_id
,(COUNT(DISTINCT hdr.ame_hrchy_hdr_id) over(partition BY hdr.transaction_id)) AS trns_cnt
,hdr.ame_hrchy_hdr_id
,lns.appr_level
,dense_rank() over(partition BY lns.ame_hrchy_hdr_id order by lns.appr_level) apprv_rnk
,lns.lns_pers_id apprv_pers_id
,(SELECT papf.employee_number
FROM hr.per_all_people_f papf
WHERE TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = lns.lns_pers_id
AND rownum = 1
) apprv_emp_num
,(SELECT papf.full_name
FROM hr.per_all_people_f papf
WHERE TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = lns.lns_pers_id
AND rownum = 1
) apprv_name
FROM
(SELECT trns.transaction_id
,trns.attribute3 ame_hrchy_hdr_id
FROM qair.qag_msma_transaction_t trns
WHERE 1=1
AND trns.transaction_id = :p_transaction_id
) hdr
,(SELECT lns.*
,(
CASE
WHEN lns.employee_number IN('L1','DH')
THEN qag_qr_njr_ame_apprvrs_pkg.get_line1_div_head_person_id(:p_transaction_id,lns.employee_number)
ELSE lns.person_id
END) lns_pers_id
FROM qr_srf.qag_hr_ame_hrchy_lines_t lns
WHERE 1 = 1
) lns
WHERE TRUNC(sysdate) BETWEEN TRUNC(NVL(lns.start_date,sysdate)) AND TRUNC(NVL(lns.end_date,sysdate))
AND lns.ame_hrchy_hdr_id = hdr.ame_hrchy_hdr_id
AND((lns.norml = DECODE(:p_apprv_hrchy,'NORMAL','Y',NULL))
OR(lns.attribute1 = DECODE(:p_apprv_hrchy,'MAX','Y',NULL))
OR(lns.reducd = DECODE(:p_apprv_hrchy,'MIN','Y',NULL))
OR(lns.mac = DECODE(:p_apprv_hrchy,'MID','Y',NULL)))
)
ORDER BY transaction_id
,ame_hrchy_hdr_id
,appr_level
) t
WHERE 1 = 1
/
select qag_qr_njr_ame_apprvrs_pkg.get_qr_njr_normal_approval(:p_transaction_id) from dual
/
select qag_qr_njr_ame_apprvrs_pkg.get_hier_approver_code(:p_transaction_id) from dual
/
SELECT hdr.ame_hrchy_hdr_id
,lines.ame_hrchy_line_id
,DECODE(hdr.process_type,'PRM','Promotions','NJR','New Joiners','RDSG','Redesignation',hdr.process_type) process_type
,hdr.os_flag
,DECODE(hdr.ame_category,'EXCPN','Exception','NEXCP','Non-Exception',hdr.ame_category) ame_category
,qag_msma_utilities_pkg.get_org_name(hdr.region_id) region
,qag_msma_utilities_pkg.get_org_name(hdr.country_company_id) country
,qag_hr_ame_hrchy_pkg.get_ht_div_station(hdr.station_division_id) division
,hdr.cost_center
,qag_msma_utilities_pkg.get_org_name(hdr.department_id) dept
,qag_msma_utilities_pkg.get_grade_name(hdr.grade_id) grade
,grade_range
,qag_msma_utilities_pkg.get_position_name(hdr.position_id) position
,sponsorship
,to_date(TRUNC(lines.start_date),'DD-MON-RRRR') start_date
,to_date(TRUNC(lines.end_date),'DD-MON-RRRR') end_date
,lines.employee_number
,NVL(qag_msma_utilities_pkg.get_full_name_t(lines.person_id),(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type IN('QAG_HR_SMA_APPROVERS_INFO','QAG_HR_SMA_OS_APPROVER_TYPE')
AND enabled_flag = 'Y'
AND lookup_code = lines.employee_number
AND sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate+1)
)) emp_name
,lines.appr_level
,reducd MIN
,mac MID
,norml MAX
,DECODE(
(SELECT COUNT(1) FROM qag_hr_ame_validations_t vald WHERE ame_hrchy_hdr_id=hdr.ame_hrchy_hdr_id
AND NVL(vald.end_date,TRUNC(sysdate)) >=TRUNC(sysdate)
AND ame_valdtn_code!='1'
),0,'No','Yes') vldyn
,(SELECT
CASE
WHEN papf.current_employee_flag='Y'
THEN 'Active'
ELSE 'Ex-Employee'
END
FROM hr.per_all_people_f papf
WHERE 1=1
AND papf.employee_number =lines.employee_number
AND TRUNC(sysdate) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
) empl_status
FROM qag_hr_ame_hrchy_header_t hdr
,qag_hr_ame_hrchy_lines_t lines
WHERE hdr.ame_hrchy_hdr_id = lines.ame_hrchy_hdr_id
AND NVL(hdr.end_date,TRUNC(sysdate)) > = TRUNC(sysdate)
AND NVL(lines.end_date,TRUNC(sysdate)) > = TRUNC(sysdate)
AND hdr.os_flag='Y'
ORDER BY hdr.region_id
,hdr.country_company_id
,hdr.station_division_id
,lines.end_date
,lines.ame_hrchy_hdr_id
,lines.appr_level
No comments:
Post a Comment