Search for:
Jun 4 2023
BIP – Extract Checklist configuration details

Below query can be used to extract checklist configuration details:

select PER_CHECKLISTS_TL.NAME
      ,PER_CHECKLISTS_TL.DESCRIPTION checklist_desc
      ,PER_TASKS_IN_CHECKLIST_B.CHECKLIST_TASK_NAME tin_ctn
      ,PER_TASKS_IN_CHECKLIST_B.DESCRIPTION task_desc
      ,PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_ID
      ,PER_TASKS_IN_CHECKLIST_B.TASK_CONFIGURATION
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_VALUE
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_CATEGORY
      ,PER_CHECKLIST_TASKS_B.ACTION_TYPE
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_ID ctb_action_id
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
      ,PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_NAME ctc_ctn
      ,PER_CHECKLIST_TASKS_TL.DESCRIPTION
      ,PER_CHECKLIST_TASKS_TL.ACTION_URL
      ,PER_CHECKLIST_TASKS_TL.USER_DISPLAY_NAME
FROM  PER_CHECKLISTS_TL PER_CHECKLISTS_TL
     ,PER_TASKS_IN_CHECKLIST_VL PER_TASKS_IN_CHECKLIST_B 
     ,PER_CHECKLIST_TASKS_B PER_CHECKLIST_TASKS_B
     ,PER_CHECKLIST_TASKS_TL PER_CHECKLIST_TASKS_TL
WHERE PER_CHECKLIST_TASKS_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
AND PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID = PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_ID
AND PER_CHECKLIST_TASKS_TL.LANGUAGE = 'US'
AND PER_CHECKLISTS_TL.LANGUAGE = 'US'
AND PER_TASKS_IN_CHECKLIST_B.CHECKLIST_ID = PER_CHECKLISTS_TL.CHECKLIST_ID
AND PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_CODE = PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
AND PER_TASKS_IN_CHECKLIST_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
Mandeep Gupta 0 Comments
Mar 28 2021
Reports (BIP) – Basic Query for Checklist E-sign Report

Oracle provided a new functionality to include e signatures in checklist tasks. A custom report can be configured and upon e-signature and completion, it will be saved to worker’s document of records.

You can follow the below document on my oracle support for detailed steps – 2611795.1

Below is the basic SQL query to start the data model development. You can add additional tables/columns as per your need.

SELECT DISTINCT Person.PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
--,PAT.ALLOCATED_TASK_ID AS TASK_ID
,PersonName.FULL_NAME FULL_NAME
,PersonName.DISPLAY_NAME DISPLAY_NAME
,PersonName.first_name FIRST_NAME
,PersonName.last_name LAST_NAME
,PersonName.middle_names MIDDLE_NAMES
,to_char(Person.date_of_birth,'DD-MM-RRRR', 'nls_date_language=American') DOB
,(SELECT flv.meaning
FROM FND_LOOKUP_VALUES flv
WHERE flv.lookup_type = 'TITLE'
AND flv.language = 'US'
AND flv.lookup_code = PersonName.title) TITLE
--,PAT.SIGNER_NAME AS P_SIGNER_NAME
--,TO_CHAR(PAT.SIGN_DATE,'MM/DD/YY') AS P_SIGN_DATE
,:P_SIGNER_NAME P_SIGNER_NAME
,:P_SIGN_DATE P_SIGN_DATE
FROM PER_PERSONS Person,
PER_PERSON_NAMES_F PersonName,
PER_ALLOCATED_CHECKLISTS PAC,
PER_ALLOCATED_TASKS_VL PAT
WHERE PAC.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAC.ALLOCATED_CHECKLIST_ID)
AND PAC.ALLOCATED_CHECKLIST_ID=PAT.ALLOCATED_CHECKLIST_ID
AND PAT.PERFORMER_ORIG_SYS_ID = Person.PERSON_ID
AND PersonName.PERSON_ID = Person.PERSON_ID
AND PersonName.NAME_TYPE = 'GLOBAL'
AND GREATEST(TRUNC(PAC.ACTION_DATE),TRUNC(SYSDATE)) BETWEEN LEAST(TRUNC(SYSDATE),PersonName.EFFECTIVE_START_DATE) AND PersonName.EFFECTIVE_END_DATE
AND PAT.STATUS='COM'
AND PAT.allocated_task_id=3000012222113131
Mandeep Gupta 0 Comments
Feb 11 2021
Reports (BIP) – Query to get checklist configurable attributes
SELECT DISTINCT PAT.PERFORMER_ORIG_SYS_ID PERSON_ID
,PAT.ALLOCATED_CHECKLIST_ID AS P_ALLOCATED_CHECKLIST_ID
,PAT.ATTRIBUTE1
,PAT.ATTRIBUTE3
,PAT.ATTRIBUTE2
,PAT.ATTRIBUTE4
,PAT.ATTRIBUTE5
,PAT.ATTRIBUTE6
,PAT.ATTRIBUTE7
,PAT.ATTRIBUTE8
,PAT.ATTRIBUTE9
,PAT.ATTRIBUTE12
,PAT.ATTRIBUTE13
,PAT.ATTRIBUTE10
,PAT.ATTRIBUTE11
,PAT.ATTRIBUTE14
,PAT.ATTRIBUTE15
,PAT.FLEX_CONTEXT_CODE
FROM PER_ALLOCATED_TASKS_VL PAT
WHERE PAT.FLEX_CONTEXT_CODE IN ('XYZ Details')
AND PAT.STATUS='COM'
AND PAT.ALLOCATED_CHECKLIST_ID = NVL(:P_ALLOCATED_CHECKLIST_ID,PAT.ALLOCATED_CHECKLIST_ID)
Mandeep Gupta 0 Comments