What's new in Customer Journeys?

Connect and learn more!
Issue with bursting query — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Issue with bursting query

Accepted answer
51
Views
4
Comments
edited Oct 22, 2023 8:36AM in Learning 4 comments

Hi,

Facing some issue with custom email notification for overdue learning in the learning module. Have developed a SQL query with bursting but employees are receiving multiple emails for different courses.

Actual aim is to get the list of courses in a single email and not to trigger multiple emails.

Please fine below the query that I'm using here. Can please suggest if this is possible and if there's any issue with the SQL.

Thanks in advance.

Version (include the version you are using, if applicable):

23B

Code Snippet (add any code snippets that support your topic, if applicable):

SELECT DISTINCT
PAPF.PERSON_NUMBER as "KEY",
'notification' TEMPLATE,
'en-US' LOCALE,
'HTML' OUTPUT_FORMAT,
'EMAIL' DEL_CHANNEL,
'Overdue Learnings' OUTPUT_NAME,
'****@test.com'PARAMETER1,
''PARAMETER2,
'xyz'||' '||'[email protected]'PARAMETER3,
'Overdue Learning Items'PARAMETER4,
''PARAMETER5,
'FALSE'PARAMETER6,
'[email protected]'PARAMETER7,
PAPF.PERSON_NUMBER,
PPNF.DISPLAY_NAME,
PAAM.ASSIGNMENT_STATUS_TYPE,
WLIFT.NAME As Course_Name,
TO_CHAR(WARF.ASSIGNED_ON_DATE, 'YYYY/MM/DD')Assigned_Date,
TO_CHAR(WARF.CALCULATED_DUE_DATE, 'YYYY/MM/DD') Due_Date
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
WLF_LEARNING_ITEMS_F_TL WLIFT,
WLF_LEARNING_ITEMS_F WLIF,
wlf_assignment_records_f WARF
WHERE
1=1
AND PAPF.PERSON_NUMBER = '30076541'
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.ASSIGNMENT_TYPE = 'E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND WLIFT.LEARNING_ITEM_ID = wlif.LEARNING_ITEM_ID
AND warf.LEARNING_ITEM_ID = wlif.LEARNING_ITEM_ID
AND PAPF.PERSON_ID = warf.LEARNER_ID
AND WARF.CALCULATED_DUE_DATE <= SYSDATE
AND warf.STATUS = 'ORA_ASSN_REC_ACTIVE'
AND warf.STATUS NOT IN ('ORA_ASSN_REC_COMPLETE')
AND warf.EVENT_TYPE = 'ORA_REQUIRE_ASSIGNMENT'
AND WLIF.LEARNING_ITEM_TYPE IN ('ORA_COURSE','ORA_SPECIALIZATION')
AND TRUNC(SYSDATE)BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE)BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE)BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
ORDER BY PAPF.PERSON_NUMBER

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!