Categories
- All Categories
- 4 Oracle Analytics Sharing Center
- 12 Oracle Analytics Lounge
- 189 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.4K Oracle Analytics Forums
- 5.9K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 65 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Query for Issue description and resolution in transaction console

Dears ,
I am working on a query to get issue description and resolution , I found them but I could not exact fault message for transaction.
Here is the code I am using .
If anyone can help , it is highly appreciated.
Thank You
—————————————————————————-Used code :SELECT to_char(apae.per_absence_entry_id),
papf.person_id person_id,
papf.person_number person_number,
ppnf.Display_name full_name,
to_char(apae.submitted_date, 'DD-Mon-YY', 'NLS_DATE_LANGUAGE = AMERICAN') submitted_date,
aatv.name Abs_type,
apae.comments,
decode(apae.approval_status_cd, 'APPROVED', 'Completed', 'AWAITING', 'Awaiting approval') status,
PD.NAME Department
from
anc_per_abs_entries apae,
anc_absence_types_vl aatv,
per_all_people_f papf,
per_person_names_f ppnf,
PER_ALL_ASSIGNMENTS_M apaeM, PER_DEPARTMENTS PD, hrc_txn_header hth, hrc_txn_console_entry htce, FND_CONSOLE_TRANSACTION_INFO fcti, FND_CONSOLE_ISSUE fci, HRC_CONSOLE_FAULT_DETAILS_b hcfdv, HRC_CONSOLE_FAULT_DETAILS_tl hcfdtl, hrc_txn_data htd
WHERE
apae.person_id = papf.person_id
AND papf.person_id = ppnf.person_id (+)
AND ppnf.name_type = 'SA'
AND apae.absence_type_id = aatv.absence_type_id
and apae.approval_status_cd = 'AWAITING'
and hth.subject_id = papf.PERSON_ID
and hth.MODULE_IDENTIFIER = 'Global Absence Recording'
and hth.transaction_id = htce.transaction_id
and htce.process_category like 'Global Absence Recording%'
and fcti.TRANSACTION_ID = htce.Transaction_Id
and fci.CONSOLE_TRANSACTION_ID = fcti.CONSOLE_TRANSACTION_ID
and hcfdv.FAULT_DETAILS_ID = hcfdtl.fault_details_id
and HCFDTL.LANGUAGE = 'US'
and hcfdv.issue_type_code=fci.issue_type_code
and hcfdv.fault_code = fci.fault_code
and hcfdv.error_code = fci.error_code
and htd.transaction_id = fcti.transaction_id
AND PAPF.PERSON_ID = apaeM.PERSON_ID AND apaeM.PRIMARY_FLAG = 'Y' AND apaeM.ASSIGNMENT_TYPE = 'E' AND apaeM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND apaeM.ORGANIZATION_ID = PD.ORGANIZATION_ID (+) AND (SYSDATE) BETWEEN apaeM.EFFECTIVE_START_DATE AND apaeM.EFFECTIVE_END_DATE AND (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND (SYSDATE) BETWEEN pd.EFFECTIVE_START_DATE AND pd.EFFECTIVE_END_DATE AND aatv.name = nvl(:P_ABS_TYPE, aatv.name) AND PD.NAME = nvl(:P_DEPARTMENT, PD.NAME ) and papf.person_number = nvl(:P_PERSON_NUMBER,papf.person_number) AND (apae.START_DATE BETWEEN NVL ( :P_START_DATE, TRUNC (SYSDATE)) AND NVL ( :P_END_DATE, TRUNC (SYSDATE)) OR (NVL ( :P_START_DATE, TRUNC (SYSDATE)) BETWEEN TRUNC ( apae.start_date) AND TRUNC ( apae.end_date)) )AND (apae.END_DATE BETWEEN NVL ( :P_START_DATE, TRUNC (SYSDATE)) AND NVL ( :P_END_DATE, TRUNC (SYSDATE)) OR (NVL ( :P_END_DATE, TRUNC (SYSDATE)) BETWEEN TRUNC (apae.start_date) AND TRUNC (apae.end_date)) )
Answers
-
Hi, To better serve you, please post this question in the Reporting and Analytics for HCM forum since its specific to HCM subject areas/use cases. Thank you.
0 -
Hi Beshoy,
Can you please check below suggestion?
Thanks.
0