Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Query for Issue description and resolution in transaction console

Received Response
22
Views
2
Comments

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

Welcome!

It looks like you're new here. Sign in or register to get started.