[Home] [Help]
455:
456: --
457: BEGIN
458: --
459: hr_utility.set_location(l_proc, 10);
460: --Fetch applicantion's current session Language
461: open get_nls_lang(userenv('LANG'));
462: fetch get_nls_lang into l_intial_set_lang;
463: close get_nls_lang;
466: l_current_set_lang := l_intial_set_lang;
467:
468: IF (funcmode='RUN') THEN
469:
470: hr_utility.set_location(l_proc, 20);
471:
472: -- get the event name
473: Event:=wf_engine.getActivityAttrEvent
474: (itemtype => itemtype
486: -- only when the new row is inserted with 'EXTENDED' offer status in irc_offer_status_history table,
487: -- proceed further
488: IF ( l_offer_status = 'EXTENDED') THEN
489:
490: hr_utility.set_location(l_proc, 30);
491:
492: -- get the note entered when extending the offer
493: open csr_offer_notes(l_offer_status_history_id);
494: fetch csr_offer_notes into l_note;
510: open csr_get_user_name(l_creator_id);
511: fetch csr_get_user_name into l_creator_name;
512: close csr_get_user_name;
513:
514: hr_utility.set_location(l_proc, 40);
515:
516: -- get the view offer letter url
517: -- pass the applicant assignment id because drilldown used in application details page uses it
518: l_url := get_view_offer_url
526: fetch csr_get_employee_id into l_applicant_id;
527: close csr_get_employee_id;
528:
529:
530: hr_utility.set_location(l_proc, 50);
531:
532: -- Fetch applicant pref language
533:
534: open get_user_lang(l_applicant_id);
547: fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
548: fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
549: l_subject := fnd_message.get;
550:
551: hr_utility.set_location(l_proc, 60);
552:
553: -- build html body
554: fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_HTML');
555: fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
569: || ''
570: || '
';
571: end if;
572:
573: hr_utility.set_location(l_proc, 70);
574:
575: -- build text body
576: --
577: fnd_message.set_name('PER','IRC_OFFER_RECEIVED_APL_TEXT');
590: || '\n';
591: end if;
592:
593: --
594: hr_utility.set_location(l_proc, 80);
595:
596: -- send notification
597: if l_applicant_id is not null then
598: l_id := irc_notification_helper_pkg.send_notification
602: , p_text_body => l_text_body
603: , p_from_role => l_creator_name
604: );
605: end if;
606: hr_utility.set_location(l_proc, 90);
607:
608: -- now send the notification to manager also
609: IF( l_manager_id is not null or l_recruiter_id is not null) THEN
610: --
607:
608: -- now send the notification to manager also
609: IF( l_manager_id is not null or l_recruiter_id is not null) THEN
610: --
611: hr_utility.set_location(l_proc, 100);
612:
613: -- get the employee id for manager
614: open csr_get_employee_id(l_manager_id);
615: fetch csr_get_employee_id into l_manager_id;
637: fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
638: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
639: l_subject := fnd_message.get;
640:
641: hr_utility.set_location(l_proc, 110);
642:
643:
644: -- build html body
645: fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_HTML');
647: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
648: l_html_body := fnd_message.get || '
' || l_note_text;
649:
650: --
651: hr_utility.set_location(l_proc, 120);
652:
653: -- build text body
654: --
655: fnd_message.set_name('PER','IRC_OFFER_SENT_MGR_TEXT');
657: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
658: l_text_body := fnd_message.get || '\n' || l_note_text;
659:
660: --
661: hr_utility.set_location(l_proc, 130);
662:
663: -- send notification to manager
664: if l_manager_id is not null then
665: l_id := irc_notification_helper_pkg.send_notification
679: , p_text_body => l_text_body
680: , p_from_role => l_creator_name
681: );
682: end if;
683: hr_utility.set_location(l_proc, 140);
684:
685: --
686: END IF;
687: END IF;
687: END IF;
688: --
689: END IF;
690: resultout:='COMPLETE';
691: hr_utility.set_location(' Leaving:'||l_proc, 150);
692: --
693: END send_rcvd_wf_notification;
694: --
695: --
730:
731: --
732: BEGIN
733: --
734: hr_utility.set_location('Entering'||l_proc, 10);
735:
736: processed_record_count :=0;
737: failure_record_count :=0;
738:
775: open csr_get_user_name(offer_expiry_rec.creator_id);
776: fetch csr_get_user_name into l_creator_name;
777: close csr_get_user_name;
778:
779: hr_utility.set_location(l_proc,20);
780:
781: -- get offer view url
782: l_url := get_view_offer_url
783: (p_person_id => offer_expiry_rec.applicant_id
808: fnd_message.set_token('VACANCY_NAME',offer_expiry_rec.job_posting_title, false);
809: fnd_message.set_token('JOB_TITLE',offer_expiry_rec.ipc_job_title, false);
810: l_subject := fnd_message.get;
811:
812: hr_utility.set_location(l_proc,30);
813:
814: --
815: -- Build the body of the message both in text and html
816: --
831: || ''
832: || '
';
833: end if;
834: --
835: hr_utility.set_location(l_proc,40);
836:
837: -- build text body
838:
839: fnd_message.set_name('PER','IRC_OFFER_EXPIRY_APL_TEXT');
848: || '\n'||l_url
849: || '\n';
850: end if;
851: --
852: hr_utility.set_location(l_proc,50);
853:
854: -- send notification to applicant
855: BEGIN
856: if l_applicant_id is not null then
881: ,buff => 'Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
882: );
883: END;
884:
885: hr_utility.set_location(l_proc,60);
886:
887: -- send notification to manager
888: IF ( offer_expiry_rec.manager_id is not null) THEN
889:
886:
887: -- send notification to manager
888: IF ( offer_expiry_rec.manager_id is not null) THEN
889:
890: hr_utility.set_location(l_proc,70);
891:
892: --get the employee id for manager
893: open csr_get_employee_id(offer_expiry_rec.manager_id);
894: fetch csr_get_employee_id into l_manager_id;
893: open csr_get_employee_id(offer_expiry_rec.manager_id);
894: fetch csr_get_employee_id into l_manager_id;
895: close csr_get_employee_id;
896:
897: hr_utility.set_location(l_proc,80);
898:
899: -- get extend offer duration url
900: l_url := get_extend_offer_duration_url
901: (p_person_id => l_manager_id
901: (p_person_id => l_manager_id
902: ,p_offer_id => offer_expiry_rec.offer_id);
903:
904:
905: hr_utility.set_location(l_proc,90);
906: --fetch the resp. Manager pref. language
907: open get_user_lang(l_manager_id);
908: fetch get_user_lang into l_mgr_lang;
909: close get_user_lang;
939: || 'Extend Duration'
940: || ''
941: || '
';
942: --
943: hr_utility.set_location(l_proc,100);
944:
945: -- build text body
946:
947: fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_TEXT');
955: || '\n'||'Extend Duration'
956: || '\n'||l_url
957: || '\n';
958: --
959: hr_utility.set_location(l_proc,110);
960:
961: else --- send_notitfication_status is failure
962: -- build html body
963: fnd_message.set_name('PER','IRC_OFFER_EXPIRY_MGR_HTML');
989: , p_text_body => l_text_body
990: , p_from_role => l_creator_name
991: );
992: end if;
993: hr_utility.set_location(l_proc,120);
994: EXCEPTION
995: When OTHERS then
996: fnd_file.put_line
997: ( which => fnd_file.log
1026: ,buff => 'Total Record Processed ' || processed_record_count ||
1027: ' Successful Records '|| (processed_record_count - failure_record_count) ||
1028: ' Error Records '|| failure_record_count
1029: );
1030: hr_utility.set_location(' Leaving:'||l_proc, 130);
1031:
1032: exception
1033: when others then
1034: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1071:
1072: --
1073: BEGIN
1074: --
1075: hr_utility.set_location('Entering'||l_proc, 10);
1076: processed_record_count :=0;
1077: failure_record_count :=0;
1078:
1079: --setting header to the log file
1113: open csr_get_user_name(offer_expired_rec.creator_id);
1114: fetch csr_get_user_name into l_creator_name;
1115: close csr_get_user_name;
1116:
1117: hr_utility.set_location(l_proc,20);
1118:
1119: -- get the person id
1120: open csr_get_employee_id(offer_expired_rec.applicant_id);
1121: fetch csr_get_employee_id into l_applicant_id;
1136: fnd_message.set_token('VACANCY_NAME',offer_expired_rec.job_posting_title, false);
1137: fnd_message.set_token('JOB_TITLE',offer_expired_rec.ipc_job_title, false);
1138: l_subject := fnd_message.get;
1139:
1140: hr_utility.set_location(l_proc,30);
1141:
1142: --
1143: -- Build the body of the message both in text and html
1144: --
1148: fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
1149: l_html_body := fnd_message.get;
1150:
1151: --
1152: hr_utility.set_location(l_proc,40);
1153:
1154: -- build text body
1155: fnd_message.set_name('PER','IRC_OFFER_EXPIRED_APL_TEXT');
1156: fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
1156: fnd_message.set_token('SENT_DATE',l_offer_sent_date, false);
1157: l_text_body := fnd_message.get;
1158:
1159: --
1160: hr_utility.set_location(l_proc,50);
1161:
1162: -- send notification
1163:
1164: BEGIN
1191: ,buff => 'Failure Reason: '|| fnd_message.get_string('PER','IRC_412059_NO_ROLE_4_PARTY')
1192: );
1193: END;
1194:
1195: hr_utility.set_location(l_proc,60);
1196:
1197: -- send notification to manager
1198: IF ( offer_expired_rec.manager_id is not null ) THEN
1199: --
1196:
1197: -- send notification to manager
1198: IF ( offer_expired_rec.manager_id is not null ) THEN
1199: --
1200: hr_utility.set_location(l_proc,70);
1201:
1202:
1203: --get the employee id for manager
1204: open csr_get_employee_id(offer_expired_rec.manager_id);
1243: || 'Extend Duration'
1244: || ''
1245: || '
';
1246: --
1247: hr_utility.set_location(l_proc,80);
1248:
1249: -- build text body
1250: fnd_message.set_name('PER','IRC_OFFER_EXPIRED_MGR_TEXT');
1251: fnd_message.set_token('APPLICANT_NAME',offer_expired_rec.applicant_name, false);
1278: ||'
Please Take Appropriate Action.';
1279:
1280: End if;
1281:
1282: hr_utility.set_location(l_proc,90);
1283: ---- sending notification to the manager
1284: BEGIN
1285: if l_manager_id is not null then
1286: l_id := irc_notification_helper_pkg.send_notification
1290: , p_text_body => l_text_body
1291: , p_from_role => l_creator_name
1292: );
1293: end if;
1294: hr_utility.set_location(l_proc,100);
1295: EXCEPTION
1296: When OTHERS then
1297: fnd_file.put_line
1298: ( which => fnd_file.log
1315: ,p_offer_id => offer_expired_rec.offer_id
1316: ,p_change_reason => 'EXPIRED'
1317: ,p_status_change_date => trunc(sysdate)
1318: );
1319: hr_utility.set_location(l_proc,110);
1320:
1321: if send_notitfication_status = 'success' then
1322: fnd_file.put_line
1323: ( which => fnd_file.log
1342: ,buff => 'Total Record Processed ' || processed_record_count ||
1343: ' Successful Records '|| (processed_record_count - failure_record_count) ||
1344: ' Error Records '|| failure_record_count
1345: );
1346: hr_utility.set_location(' Leaving:'||l_proc, 120);
1347: exception
1348: when others then
1349: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',l_intial_set_lang);
1350: --
1429: --
1430: BEGIN
1431: --
1432:
1433: hr_utility.set_location(l_proc, 10);
1434: --Fetch applicantion's current session Language
1435: open get_nls_lang(userenv('LANG'));
1436: fetch get_nls_lang into l_intial_set_lang;
1437: close get_nls_lang;
1442:
1443:
1444: IF (funcmode='RUN') THEN
1445:
1446: hr_utility.set_location(l_proc, 20);
1447:
1448: Event:=wf_engine.getActivityAttrEvent
1449: (itemtype => itemtype
1450: ,itemkey => itemkey
1469: END IF;
1470:
1471: IF ( l_apl_response = true ) THEN
1472: --
1473: hr_utility.set_location(l_proc, 30);
1474:
1475: open csr_send_apl_resp(l_offer_id);
1476: fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;
1477: close csr_send_apl_resp;
1485: open csr_get_user_employee_id(l_creator_id);
1486: fetch csr_get_user_employee_id into l_creator_id;
1487: close csr_get_user_employee_id;
1488:
1489: hr_utility.set_location(l_proc, 40);
1490:
1491: -- get the person id
1492: open csr_get_employee_id(l_applicant_id);
1493: fetch csr_get_employee_id into l_applicant_id;
1497: open csr_get_apl_referrer_id(l_offer_id);
1498: fetch csr_get_apl_referrer_id into l_referrer_id;
1499: close csr_get_apl_referrer_id;
1500:
1501: hr_utility.set_location(l_proc, 50);
1502:
1503: -- Fetch applicant pref language
1504:
1505: open get_user_lang(l_applicant_id);
1521: l_subject := fnd_message.get;
1522: --
1523:
1524: --
1525: hr_utility.set_location(l_proc, 60);
1526:
1527: open csr_offer_notes(l_offer_status_history_id);
1528: fetch csr_offer_notes into l_note;
1529: if csr_offer_notes%found then
1545: fnd_message.set_token('VACANCY_NAME',l_job_posting_title, false);
1546: fnd_message.set_token('JOB_TITLE',l_ipc_job_title, false);
1547: l_html_body := fnd_message.get || '
' || l_note_text;
1548:
1549: hr_utility.set_location(l_proc, 70);
1550:
1551: -- build text body
1552: IF ( l_apl_accepted = true ) THEN
1553: fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_APL_TEXT');
1559: l_text_body := fnd_message.get || '\n' || l_note_text;
1560:
1561:
1562: --
1563: hr_utility.set_location(l_proc, 80);
1564:
1565: -- send notification
1566: if l_applicant_id is not null then
1567: l_id := irc_notification_helper_pkg.send_notification
1571: , p_text_body => l_text_body
1572: , p_from_role => l_creator_name
1573: );
1574: end if;
1575: hr_utility.set_location(l_proc, 90);
1576:
1577: --send notification to manager
1578: IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1579:
1576:
1577: --send notification to manager
1578: IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
1579:
1580: hr_utility.set_location(l_proc, 100);
1581:
1582: IF ( l_apl_accepted = false ) THEN
1583: -- get the decline reason
1584: l_decline_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
1620: fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1621: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1622: l_html_body := fnd_message.get || '
' || l_note_text;
1623: --
1624: hr_utility.set_location(l_proc, 120);
1625: --
1626: -- get the applicant_assignment_id
1627: open csr_get_apl_assignment_id (l_offer_id);
1628: fetch csr_get_apl_assignment_id into l_applicant_asg_id;
1627: open csr_get_apl_assignment_id (l_offer_id);
1628: fetch csr_get_apl_assignment_id into l_applicant_asg_id;
1629: close csr_get_apl_assignment_id;
1630: --
1631: hr_utility.set_location(l_proc, 130);
1632: --
1633: l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
1634: ,p_apl_asg_id =>l_applicant_asg_id);
1635: --
1632: --
1633: l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
1634: ,p_apl_asg_id =>l_applicant_asg_id);
1635: --
1636: hr_utility.set_location(l_proc, 140);
1637: --
1638: l_html_body := l_html_body
1639: || '
Click '
1642: || ' to view details.'
1643: || '
';
1644: --
1645:
1646: hr_utility.set_location(l_proc, 110);
1647:
1648: -- build text body
1649: IF ( l_apl_accepted = true ) THEN
1650: fnd_message.set_name('PER','IRC_OFFER_ACCEPTED_MGR_TEXT');
1660: || '\n\n'||'View Details'
1661: || '\n'||l_url
1662: || '\n';
1663: --
1664: hr_utility.set_location(l_proc, 120);
1665:
1666: -- send notification to manager
1667: IF( l_manager_id is not null ) THEN
1668: l_id := irc_notification_helper_pkg.send_notification
1671: , p_html_body => l_html_body
1672: , p_text_body => l_text_body
1673: , p_from_role => l_creator_name
1674: );
1675: hr_utility.set_location(l_proc, 130);
1676: END IF;
1677: -- send notification to recruiter
1678: IF( l_recruiter_id is not null
1679: and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
1683: , p_html_body => l_html_body
1684: , p_text_body => l_text_body
1685: , p_from_role => l_creator_name
1686: );
1687: hr_utility.set_location(l_proc, 140);
1688: END IF;
1689: -- send notification to creator
1690: IF( l_creator_id is not null
1691: and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
1696: , p_html_body => l_html_body
1697: , p_text_body => l_text_body
1698: , p_from_role => l_creator_name
1699: );
1700: hr_utility.set_location(l_proc, 150);
1701:
1702: END IF;
1703:
1704: -- send notification to referrer
1730: fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1731: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1732: l_html_body := fnd_message.get;
1733:
1734: hr_utility.set_location(l_proc, 155);
1735:
1736: -- build text body
1737: fnd_message.set_name('PER','IRC_412509_OFR_ACCEPT_REF_TEXT');
1738: fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
1753: END IF;
1754: --
1755: END IF;
1756: resultout:='COMPLETE';
1757: hr_utility.set_location(' Leaving:'||l_proc, 160);
1758: --Reset applicantion's current session Language
1759: if l_intial_set_lang <> l_current_set_lang then
1760: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1761: end if;
1803: --
1804: BEGIN
1805: --
1806:
1807: hr_utility.set_location(l_proc, 10);
1808: --Fetch applicantion's current session Language
1809: open get_nls_lang(userenv('LANG'));
1810: fetch get_nls_lang into l_intial_set_lang;
1811: close get_nls_lang;
1814: --Assign the session language to current set language.
1815: l_current_set_lang := l_intial_set_lang;
1816: IF (funcmode='RUN') THEN
1817:
1818: hr_utility.set_location(l_proc, 20);
1819:
1820: Event:=wf_engine.getActivityAttrEvent
1821: (itemtype => itemtype
1822: ,itemkey => itemkey
1827: l_offer_id:= to_number(irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_id'));
1828: l_offer_status:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/offer_status');
1829:
1830:
1831: hr_utility.set_location(l_proc, 30);
1832: open csr_onhold_offer(l_offer_id);
1833: fetch csr_onhold_offer into l_vacancy_name,l_creator_id,l_job_title,l_applicant_name;
1834: close csr_onhold_offer;
1835:
1842: open csr_get_user_employee_id(l_creator_id);
1843: fetch csr_get_user_employee_id into l_creator_id;
1844: close csr_get_user_employee_id;
1845:
1846: hr_utility.set_location(l_proc, 40);
1847:
1848: --send notification to manager
1849: IF(l_creator_id is not null) THEN
1850:
1847:
1848: --send notification to manager
1849: IF(l_creator_id is not null) THEN
1850:
1851: hr_utility.set_location(l_proc, 50);
1852:
1853: --fetch the Manager pref. language
1854: open get_user_lang(l_creator_id);
1855: fetch get_user_lang into l_mgr_lang;
1880: fnd_message.set_token('VACANCY_NAME',l_vacancy_name, false);
1881: l_text_body := fnd_message.get;
1882:
1883: --
1884: hr_utility.set_location(l_proc, 60);
1885:
1886: -- send notification to creator
1887: IF( l_creator_id is not null ) THEN
1888: l_id := irc_notification_helper_pkg.send_notification
1891: , p_html_body => l_html_body
1892: , p_text_body => l_text_body
1893: , p_from_role => l_creator_name
1894: );
1895: hr_utility.set_location(l_proc, 70);
1896:
1897: END IF;
1898: --
1899: END IF;
1899: END IF;
1900:
1901: END IF;
1902: resultout:='COMPLETE';
1903: hr_utility.set_location(' Leaving:'||l_proc, 90);
1904: --Reset applicantion's current session Language
1905: if l_intial_set_lang <> l_current_set_lang then
1906: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
1907: end if;
2003: --
2004: BEGIN
2005: --
2006:
2007: hr_utility.set_location(l_proc, 10);
2008: --Fetch applicantion's current session Language
2009: open get_nls_lang(userenv('LANG'));
2010: fetch get_nls_lang into l_intial_set_lang;
2011: close get_nls_lang;
2013: l_current_set_lang := l_intial_set_lang;
2014:
2015: IF (funcmode='RUN') THEN
2016:
2017: hr_utility.set_location(l_proc, 20);
2018:
2019: Event:=wf_engine.getActivityAttrEvent
2020: (itemtype => itemtype
2021: ,itemkey => itemkey
2040: END IF;
2041:
2042: IF ( l_mgr_withdraw = true ) THEN
2043: --
2044: hr_utility.set_location(l_proc, 30);
2045:
2046: open csr_send_apl_resp(l_offer_id);
2047: fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;
2048: close csr_send_apl_resp;
2066: open csr_get_user_employee_id(l_creator_id);
2067: fetch csr_get_user_employee_id into l_creator_id;
2068: close csr_get_user_employee_id;
2069:
2070: hr_utility.set_location(l_proc, 40);
2071:
2072: -- get the person id
2073: open csr_get_employee_id(l_applicant_id);
2074: fetch csr_get_employee_id into l_applicant_id;
2073: open csr_get_employee_id(l_applicant_id);
2074: fetch csr_get_employee_id into l_applicant_id;
2075: close csr_get_employee_id;
2076:
2077: hr_utility.set_location(l_proc, 50);
2078:
2079: open csr_offer_notes(l_offer_status_history_id);
2080: fetch csr_offer_notes into l_note;
2081: if csr_offer_notes%found then
2086: DBMS_LOB.READ(l_note, l_begining, l_end, l_note_text);
2087: end if;
2088: close csr_offer_notes;
2089:
2090: hr_utility.set_location(l_proc, 55);
2091: -- Send notification to the candidate only when the offer has already been extended
2092:
2093: if l_prev_offer_status='EXTENDED' or l_prev_offer_status='PENDING_EXTENDED_DURATION' or l_prev_change_reason='APL_ACCEPTED' then
2094:
2109: l_subject := fnd_message.get;
2110: --
2111:
2112: --
2113: hr_utility.set_location(l_proc, 60);
2114:
2115: -- build html body for applicant
2116: fnd_message.set_name('PER','IRC_412573_WITHDRAWAL_APL_HTML');
2117: fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2117: fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2118: fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2119: l_html_body := fnd_message.get || '
' || l_note_text;
2120:
2121: hr_utility.set_location(l_proc, 70);
2122:
2123: -- build text body
2124: fnd_message.set_name('PER','IRC_412574_WITHDRAWAL_APL_TEXT');
2125: fnd_message.set_token('JOB_TITLE',l_job_posting_title, false);
2127:
2128: l_text_body := fnd_message.get || '\n' || l_note_text;
2129:
2130: --
2131: hr_utility.set_location(l_proc, 80);
2132:
2133: -- send notification
2134: if l_applicant_id is not null then
2135: l_id := irc_notification_helper_pkg.send_notification
2139: , p_text_body => l_text_body
2140: , p_from_role => l_creator_name
2141: );
2142: end if;
2143: hr_utility.set_location(l_proc, 90);
2144:
2145: end if;
2146:
2147: --send notification to manager
2146:
2147: --send notification to manager
2148: IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
2149:
2150: hr_utility.set_location(l_proc, 100);
2151:
2152: --fetch the resp. Manager pref. language
2153: open get_user_lang(l_manager_id);
2154: fetch get_user_lang into l_mgr_lang;
2159: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_mgr_lang||'''');
2160: l_current_set_lang := l_mgr_lang;
2161: end if;
2162:
2163: hr_utility.set_location('l_last_updated_by' || l_last_updated_by, 20);
2164:
2165: open csr_get_user_employee_id(l_last_updated_by);
2166: fetch csr_get_user_employee_id into l_last_updated_emp_id;
2167: close csr_get_user_employee_id;
2165: open csr_get_user_employee_id(l_last_updated_by);
2166: fetch csr_get_user_employee_id into l_last_updated_emp_id;
2167: close csr_get_user_employee_id;
2168:
2169: hr_utility.set_location('l_last_updated_emp_id' || l_last_updated_emp_id, 20);
2170:
2171: open csr_get_name(l_last_updated_emp_id);
2172: fetch csr_get_name into l_action_performer;
2173: close csr_get_name;
2171: open csr_get_name(l_last_updated_emp_id);
2172: fetch csr_get_name into l_action_performer;
2173: close csr_get_name;
2174:
2175: hr_utility.set_location('l_action_performer' || l_action_performer, 20);
2176:
2177: -- get the decline reason
2178: l_withdrawal_reason:= irc_xml_util.valueOf(EventDocument,'/offer_status_history/decline_reason');
2179: open csr_get_withdrawal_reason(l_withdrawal_reason);
2200: open csr_get_apl_assignment_id (l_offer_id);
2201: fetch csr_get_apl_assignment_id into l_applicant_asg_id;
2202: close csr_get_apl_assignment_id;
2203: --
2204: hr_utility.set_location(l_proc, 110);
2205: --
2206: l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
2207: ,p_apl_asg_id =>l_applicant_asg_id);
2208: --
2205: --
2206: l_url := get_manager_view_offer_url( p_person_id =>l_applicant_id
2207: ,p_apl_asg_id =>l_applicant_asg_id);
2208: --
2209: hr_utility.set_location(l_proc, 120);
2210: --
2211: l_html_body := l_html_body
2212: || '
Click '
2214: || 'here'
2215: || ' to view details.'
2216: || '
';
2217: --
2218: hr_utility.set_location(l_proc, 115);
2219:
2220: -- build text body
2221: fnd_message.set_name('PER','IRC_412565_WITHDRAWAL_MGR_TEXT');
2222: fnd_message.set_token('ACTION_PERFORMER',l_action_performer, false);
2231: || '\n\n'||'View Details'
2232: || '\n'||l_url
2233: || '\n';
2234: --
2235: hr_utility.set_location(l_proc, 120);
2236:
2237: -- send notification to manager
2238: IF( l_manager_id is not null ) THEN
2239: l_id := irc_notification_helper_pkg.send_notification
2242: , p_html_body => l_html_body
2243: , p_text_body => l_text_body
2244: , p_from_role => l_creator_name
2245: );
2246: hr_utility.set_location(l_proc, 130);
2247: END IF;
2248: -- send notification to recruiter
2249: IF( l_recruiter_id is not null
2250: and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
2254: , p_html_body => l_html_body
2255: , p_text_body => l_text_body
2256: , p_from_role => l_creator_name
2257: );
2258: hr_utility.set_location(l_proc, 140);
2259: END IF;
2260: -- send notification to creator
2261: IF( l_creator_id is not null
2262: and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
2267: , p_html_body => l_html_body
2268: , p_text_body => l_text_body
2269: , p_from_role => l_creator_name
2270: );
2271: hr_utility.set_location(l_proc, 150);
2272:
2273: END IF;
2274:
2275: END IF;
2276: END IF;
2277: --
2278: END IF;
2279: resultout:='COMPLETE';
2280: hr_utility.set_location(' Leaving:'||l_proc, 160);
2281: --Reset applicantion's current session Language
2282: if l_intial_set_lang <> l_current_set_lang then
2283: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');
2284: end if;
2378: --
2379: BEGIN
2380: --
2381:
2382: hr_utility.set_location(l_proc, 10);
2383:
2384: --Fetch applicantion's current session Language
2385: open get_nls_lang(userenv('LANG'));
2386: fetch get_nls_lang into l_intial_set_lang;
2391: l_current_set_lang := l_intial_set_lang;
2392:
2393: IF (funcmode='RUN') THEN
2394:
2395: hr_utility.set_location(l_proc, 20);
2396:
2397: Event:=wf_engine.getActivityAttrEvent
2398: (itemtype => itemtype
2399: ,itemkey => itemkey
2413: END IF;
2414:
2415: IF ( l_apl_dec_acpt = true ) THEN
2416: --
2417: hr_utility.set_location(l_proc, 30);
2418:
2419: open csr_send_apl_resp(l_offer_id);
2420: fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_ipc_job_title,l_last_updated_by;
2421: close csr_send_apl_resp;
2439: open csr_get_user_employee_id(l_creator_id);
2440: fetch csr_get_user_employee_id into l_creator_id;
2441: close csr_get_user_employee_id;
2442:
2443: hr_utility.set_location(l_proc, 40);
2444:
2445: -- get the person id
2446: open csr_get_employee_id(l_applicant_id);
2447: fetch csr_get_employee_id into l_applicant_id;
2446: open csr_get_employee_id(l_applicant_id);
2447: fetch csr_get_employee_id into l_applicant_id;
2448: close csr_get_employee_id;
2449:
2450: hr_utility.set_location(l_proc, 50);
2451:
2452: open csr_offer_notes(l_offer_status_history_id);
2453: fetch csr_offer_notes into l_note;
2454: if csr_offer_notes%found then
2465: open csr_get_decline_reason(l_decline_reason);
2466: fetch csr_get_decline_reason into l_decline_reason_meaning;
2467: close csr_get_decline_reason;
2468:
2469: hr_utility.set_location(l_proc, 55);
2470:
2471: -- Fetch applicant pref language
2472: open get_user_lang(l_applicant_id);
2473: fetch get_user_lang into l_apl_lang;
2486: l_subject := fnd_message.get;
2487: --
2488:
2489: --
2490: hr_utility.set_location(l_proc, 60);
2491:
2492: -- build html body for applicant
2493: fnd_message.set_name('PER','IRC_412567_DEC_ACCEPT_APL_HTML');
2494: fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2494: fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2495: fnd_message.set_token('HIRING_MANAGER',l_hiring_manager, false);
2496: l_html_body := fnd_message.get || '
' || l_note_text;
2497:
2498: hr_utility.set_location(l_proc, 70);
2499:
2500: -- build text body
2501: fnd_message.set_name('PER','IRC_412568_DEC_ACCEPT_APL_TEXT');
2502: fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2504:
2505: l_text_body := fnd_message.get || '\n' || l_note_text;
2506:
2507: --
2508: hr_utility.set_location(l_proc, 80);
2509:
2510: -- send notification
2511: if l_applicant_id is not null then
2512: l_id := irc_notification_helper_pkg.send_notification
2516: , p_text_body => l_text_body
2517: , p_from_role => l_creator_name
2518: );
2519: end if;
2520: hr_utility.set_location(l_proc, 90);
2521:
2522: end if;
2523:
2524: --send notification to manager
2523:
2524: --send notification to manager
2525: IF( l_manager_id is not null or l_recruiter_id is not null or l_creator_id is not null) THEN
2526:
2527: hr_utility.set_location(l_proc, 100);
2528:
2529: --fetch the resp. Manager pref. language
2530: open get_user_lang(l_manager_id);
2531: fetch get_user_lang into l_mgr_lang;
2565: || 'here'
2566: || ' to view details.'
2567: || '
';
2568: --
2569: hr_utility.set_location(l_proc, 110);
2570: -- build text body
2571: fnd_message.set_name('PER','IRC_412571_DEC_ACCEPT_MGR_TEXT');
2572: fnd_message.set_token('DECLINE_REASON',l_decline_reason_meaning, false);
2573: fnd_message.set_token('APPLICANT_NAME',l_applicant_name, false);
2580: || '\n\n'||'View Details'
2581: || '\n'||l_url
2582: || '\n';
2583: --
2584: hr_utility.set_location(l_proc, 120);
2585:
2586: -- send notification to manager
2587: IF( l_manager_id is not null ) THEN
2588: l_id := irc_notification_helper_pkg.send_notification
2591: , p_html_body => l_html_body
2592: , p_text_body => l_text_body
2593: , p_from_role => l_creator_name
2594: );
2595: hr_utility.set_location(l_proc, 130);
2596: END IF;
2597: -- send notification to recruiter
2598: IF( l_recruiter_id is not null
2599: and (l_manager_id is null or l_recruiter_id <> l_manager_id) ) THEN
2603: , p_html_body => l_html_body
2604: , p_text_body => l_text_body
2605: , p_from_role => l_creator_name
2606: );
2607: hr_utility.set_location(l_proc, 140);
2608: END IF;
2609: -- send notification to creator
2610: IF( l_creator_id is not null
2611: and (l_recruiter_id is null or l_creator_id <> l_recruiter_id )
2616: , p_html_body => l_html_body
2617: , p_text_body => l_text_body
2618: , p_from_role => l_creator_name
2619: );
2620: hr_utility.set_location(l_proc, 150);
2621:
2622: END IF;
2623:
2624: END IF;
2624: END IF;
2625: --
2626: END IF;
2627: resultout:='COMPLETE';
2628: hr_utility.set_location(' Leaving:'||l_proc, 160);
2629:
2630: --Reset applicantion's current session Language
2631: if l_intial_set_lang <> l_current_set_lang then
2632: DBMS_SESSION.SET_NLS('NLS_LANGUAGE',''''||l_intial_set_lang||'''');