PROCEDURE get_approver_comments_doc (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT VARCHAR2,
document_type IN OUT VARCHAR2
)
IS
BEGIN
document := NULL;
FOR var IN
(SELECT '<tr><td class="x1l x4x">'
|| papf.full_name||'('||wnv.responder||')'
|| '</td><td class="x1l x4x">'
||nvl((select attribute_display_value from wf_notification_attr_resp_v where group_id = wnar.group_id AND ATTRIBUTE_NAME != 'COMMENETS' ),'Withdrawn' )
--|| nvl(ATTRIBUTE_DISPLAY_VALUE, case when wc.USER_COMMENT = 'Withdrawn by the Requestor' then 'Withdraw' end)
|| '</td><td class="x1l x4x">'
|| nvl(wnar.attribute_display_value,wc.USER_COMMENT)
|| '</td><td class="x1l x4x">'
|| TO_CHAR (wnv.end_date, 'DD-Mon-YYYY HH:MI:SS AM')
|| '<br></td></tr>' AS NEWLINE
FROM WF_NOTIFICATIONS WNV
,wf_notification_attr_resp_v wnar
,fnd_user fu
,per_all_people_f papf
,wf_comments wc
WHERE 1=1
AND wnv.MESSAGE_TYPE like '%XXXX%'
AND wnar.MESSAGE_TYPE = wnv.MESSAGE_TYPE
and wnv.notification_id = wc.notification_id
and wc.ACTION = 'RESPOND'
AND wnv.GROUP_ID = wnar.GROUP_ID
and wnv.responder = fu.user_name
and fu.PERSON_PARTY_ID = papf.party_id(+)
AND wnar.ATTRIBUTE_NAME = 'COMMENETS'
AND wnar.MESSAGE_TYPE LIKE '%XXXX%'
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
and substr(item_key,1,instr(item_key,'/',-1)-1) = SUBSTR (wnv.CONTEXT,INSTR (wnv.CONTEXT, ':', 1)+1,INSTR (wnv.CONTEXT, '/', 1,3)- INSTR (wnv.CONTEXT, ':', 1)-1)
AND wnv.CONTEXT LIKE (SELECT DISTINCT SUBSTR (wn.CONTEXT,1,INSTR (wn.CONTEXT, '/', 1,3)-1)
FROM wf_notifications wn
WHERE wn.notification_id = document_id)|| '%'
ORDER BY wnv.end_date)
LOOP
document := document || var.NEWLINE;
END LOOP;
IF LENGTH (document) > 0
THEN
document :=
'<table cellpadding="1" cellspacing="0" border="1" width="100%" style="font-family:arial;font-size:12px;" bgcolor=white><tr><th align=CENTER colspan=4 ><span style="color:#005588; font-family=arial;font-size:12">Comments of previous approvers</span></th></tr><tr><th scope="col" class="x1r">Action Owner</th><th scope="col" class="x1r x4j">Action</th><th scope="col" class="x1r x4j">Comments</th><th scope="col" class="x1r x4j">Approval Date</th></tr>'
|| document;
document := document || '</table>';
END IF;
END get_approver_comments_doc;
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT VARCHAR2,
document_type IN OUT VARCHAR2
)
IS
BEGIN
document := NULL;
FOR var IN
(SELECT '<tr><td class="x1l x4x">'
|| papf.full_name||'('||wnv.responder||')'
|| '</td><td class="x1l x4x">'
||nvl((select attribute_display_value from wf_notification_attr_resp_v where group_id = wnar.group_id AND ATTRIBUTE_NAME != 'COMMENETS' ),'Withdrawn' )
--|| nvl(ATTRIBUTE_DISPLAY_VALUE, case when wc.USER_COMMENT = 'Withdrawn by the Requestor' then 'Withdraw' end)
|| '</td><td class="x1l x4x">'
|| nvl(wnar.attribute_display_value,wc.USER_COMMENT)
|| '</td><td class="x1l x4x">'
|| TO_CHAR (wnv.end_date, 'DD-Mon-YYYY HH:MI:SS AM')
|| '<br></td></tr>' AS NEWLINE
FROM WF_NOTIFICATIONS WNV
,wf_notification_attr_resp_v wnar
,fnd_user fu
,per_all_people_f papf
,wf_comments wc
WHERE 1=1
AND wnv.MESSAGE_TYPE like '%XXXX%'
AND wnar.MESSAGE_TYPE = wnv.MESSAGE_TYPE
and wnv.notification_id = wc.notification_id
and wc.ACTION = 'RESPOND'
AND wnv.GROUP_ID = wnar.GROUP_ID
and wnv.responder = fu.user_name
and fu.PERSON_PARTY_ID = papf.party_id(+)
AND wnar.ATTRIBUTE_NAME = 'COMMENETS'
AND wnar.MESSAGE_TYPE LIKE '%XXXX%'
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
and substr(item_key,1,instr(item_key,'/',-1)-1) = SUBSTR (wnv.CONTEXT,INSTR (wnv.CONTEXT, ':', 1)+1,INSTR (wnv.CONTEXT, '/', 1,3)- INSTR (wnv.CONTEXT, ':', 1)-1)
AND wnv.CONTEXT LIKE (SELECT DISTINCT SUBSTR (wn.CONTEXT,1,INSTR (wn.CONTEXT, '/', 1,3)-1)
FROM wf_notifications wn
WHERE wn.notification_id = document_id)|| '%'
ORDER BY wnv.end_date)
LOOP
document := document || var.NEWLINE;
END LOOP;
IF LENGTH (document) > 0
THEN
document :=
'<table cellpadding="1" cellspacing="0" border="1" width="100%" style="font-family:arial;font-size:12px;" bgcolor=white><tr><th align=CENTER colspan=4 ><span style="color:#005588; font-family=arial;font-size:12">Comments of previous approvers</span></th></tr><tr><th scope="col" class="x1r">Action Owner</th><th scope="col" class="x1r x4j">Action</th><th scope="col" class="x1r x4j">Comments</th><th scope="col" class="x1r x4j">Approval Date</th></tr>'
|| document;
document := document || '</table>';
END IF;
END get_approver_comments_doc;