60: from per_competences_vl
61: where competence_id = p_competence_id;
62: cursor get_mode(p_transaction_step_id in number) is
63: select varchar2_value
64: from hr_api_transaction_values
65: where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
66: CURSOR get_prev_status(p_competence_element_id IN number) is
67: SELECT status
68: FROM per_competence_elements
324: l_proc varchar2(200) := g_package || 'comp_not_exists';
325: l_retStatus varchar2(1) := 'T';
326: cursor c1 is
327: select 'F' status
328: from hr_api_transaction_steps s, hr_api_transaction_values a,
329: hr_api_transaction_values b
330: Where s.item_type = p_item_type
331: and s.item_key = p_item_key
332: and s.transaction_step_id = a.transaction_step_id
325: l_retStatus varchar2(1) := 'T';
326: cursor c1 is
327: select 'F' status
328: from hr_api_transaction_steps s, hr_api_transaction_values a,
329: hr_api_transaction_values b
330: Where s.item_type = p_item_type
331: and s.item_key = p_item_key
332: and s.transaction_step_id = a.transaction_step_id
333: and s.transaction_step_id = b.transaction_step_id
333: and s.transaction_step_id = b.transaction_step_id
334: and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
335: and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
336: union
337: select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
338: where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
339: and ts.transaction_id = t.transaction_id
340: and t.selected_person_id = p_person_id and t.status = 'Y'
341: and exists (Select 'e' From hr_api_transaction_values c
337: select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
338: where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
339: and ts.transaction_id = t.transaction_id
340: and t.selected_person_id = p_person_id and t.status = 'Y'
341: and exists (Select 'e' From hr_api_transaction_values c
342: Where c.transaction_step_id = ts.transaction_step_id
343: and c.name = 'P_COMPETENCE_ID'
344: and c.number_value = p_competence_id);
345: begin
420: l_obj_ver_num := p_preupd_obj_vers_num;
421: if p_transaction_step_id is not null then
422: hr_utility.set_location(l_proc,10);
423: select varchar2_value into l_mode_value
424: from hr_api_transaction_values
425: where transaction_step_id = p_transaction_step_id and name = 'P_CHANGE_MODE';
426: end if;
427: hr_utility.set_location(l_proc,15);
428: If l_mode_value = 'UPGRADE' and p_transaction_step_id is not null then
593: if l_mode_value = 'UPGRADE' then
594: hr_utility.set_location(l_proc,75);
595: select number_value
596: into l_competence_element_id
597: from hr_api_transaction_values
598: where transaction_step_id = p_transaction_step_id
599: and NAME = 'P_UPGRADED_FROM_REC_ID';
600: select a.transaction_step_id
601: into l_transaction_step_id
598: where transaction_step_id = p_transaction_step_id
599: and NAME = 'P_UPGRADED_FROM_REC_ID';
600: select a.transaction_step_id
601: into l_transaction_step_id
602: from hr_api_transaction_values a,
603: hr_api_transaction_values b,
604: hr_api_transaction_steps steps
605: where steps.transaction_id = l_transaction_id
606: and steps.transaction_step_id = a.transaction_step_id
599: and NAME = 'P_UPGRADED_FROM_REC_ID';
600: select a.transaction_step_id
601: into l_transaction_step_id
602: from hr_api_transaction_values a,
603: hr_api_transaction_values b,
604: hr_api_transaction_steps steps
605: where steps.transaction_id = l_transaction_id
606: and steps.transaction_step_id = a.transaction_step_id
607: and a.name = 'P_CHANGE_MODE'
600: select a.transaction_step_id
601: into l_transaction_step_id
602: from hr_api_transaction_values a,
603: hr_api_transaction_values b,
604: hr_api_transaction_steps steps
605: where steps.transaction_id = l_transaction_id
606: and steps.transaction_step_id = a.transaction_step_id
607: and a.name = 'P_CHANGE_MODE'
608: and a.varchar2_value = 'UPGRADE'
609: and steps.transaction_step_id = b.transaction_step_id
610: and b.name = 'P_COMPETENCE_ELEMENT_ID'
611: and b.number_value = l_competence_element_id
612: and a.transaction_step_id <> l_competence_element_id;
613: update hr_api_transaction_values
614: set date_value = (x_eff_date_from - 1)
615: where transaction_step_id = l_transaction_step_id
616: and name = 'P_EFF_DATE_TO';
617: end if;
1013: l_upgraded_from_rec_id number DEFAULT Null;
1014: l_preupd_proficy_lvl_id number default NULL;
1015: l_competence_name per_competences_vl.name%type;
1016: l_step_value per_rating_levels.step_value%type;
1017: l_item_type hr_api_transaction_steps.item_type%type;
1018: l_item_key hr_api_transaction_steps.item_key%type;
1019: l_activity_id hr_api_transaction_steps.ACTIVITY_ID%type;
1020: l_cmp_element_id per_competence_elements.competence_element_id%type;
1021: l_achieved_date per_competence_elements.ACHIEVED_DATE%type;
1014: l_preupd_proficy_lvl_id number default NULL;
1015: l_competence_name per_competences_vl.name%type;
1016: l_step_value per_rating_levels.step_value%type;
1017: l_item_type hr_api_transaction_steps.item_type%type;
1018: l_item_key hr_api_transaction_steps.item_key%type;
1019: l_activity_id hr_api_transaction_steps.ACTIVITY_ID%type;
1020: l_cmp_element_id per_competence_elements.competence_element_id%type;
1021: l_achieved_date per_competence_elements.ACHIEVED_DATE%type;
1022: l_change_mode varchar2(80);
1015: l_competence_name per_competences_vl.name%type;
1016: l_step_value per_rating_levels.step_value%type;
1017: l_item_type hr_api_transaction_steps.item_type%type;
1018: l_item_key hr_api_transaction_steps.item_key%type;
1019: l_activity_id hr_api_transaction_steps.ACTIVITY_ID%type;
1020: l_cmp_element_id per_competence_elements.competence_element_id%type;
1021: l_achieved_date per_competence_elements.ACHIEVED_DATE%type;
1022: l_change_mode varchar2(80);
1023: l_status per_competence_elements.status%type;
1129: (p_transaction_step_id => p_transaction_step_id
1130: ,p_name => 'P_STATUS');
1131: Select ITEM_TYPE,ITEM_KEY,ACTIVITY_ID
1132: INTO l_item_type, l_item_key, l_activity_id
1133: FROM hr_api_transaction_steps
1134: WHERE transaction_step_id = p_transaction_step_id;
1135: l_cmp_element_rec.status := PerCompStatus.Get_Competence_Status(
1136: p_item_type =>l_item_type
1137: ,p_item_key =>l_item_key
1362: ,p_competence_element_id IN number
1363: ,p_competence_id IN number
1364: ,p_change_mode IN varchar2) is
1365: Select s.transaction_step_id
1366: FROM hr_api_transaction_steps s,
1367: hr_api_transaction_values c, hr_api_transaction_values d,
1368: hr_api_transaction_values e, hr_api_transaction_values f
1369: WHERE
1370: s.item_type = p_item_type
1363: ,p_competence_id IN number
1364: ,p_change_mode IN varchar2) is
1365: Select s.transaction_step_id
1366: FROM hr_api_transaction_steps s,
1367: hr_api_transaction_values c, hr_api_transaction_values d,
1368: hr_api_transaction_values e, hr_api_transaction_values f
1369: WHERE
1370: s.item_type = p_item_type
1371: and s.item_key = p_item_key
1364: ,p_change_mode IN varchar2) is
1365: Select s.transaction_step_id
1366: FROM hr_api_transaction_steps s,
1367: hr_api_transaction_values c, hr_api_transaction_values d,
1368: hr_api_transaction_values e, hr_api_transaction_values f
1369: WHERE
1370: s.item_type = p_item_type
1371: and s.item_key = p_item_key
1372: and s.activity_id = p_activity_id
1534: l_prev_start_date := l_prev_date;
1535: if p_transaction_step_id is not null then
1536: hr_utility.set_location(l_proc,150);
1537: select varchar2_value into l_mode_fetch
1538: from hr_api_transaction_values
1539: where transaction_step_id = p_transaction_step_id
1540: and name = 'P_CHANGE_MODE';
1541: if l_mode_fetch = 'UPGRADE' or l_mode_fetch = 'UPDATE_APPLY' then
1542: hr_utility.set_location(l_proc,155);
1549: end if;
1550: hr_utility.set_location(l_proc,165);
1551: if p_transaction_step_id is not null then
1552: hr_utility.set_location(l_proc,170);
1553: select varchar2_value into m_mode from hr_api_transaction_values
1554: where transaction_step_id = p_transaction_step_id
1555: and name = 'P_CHANGE_MODE';
1556: if m_mode = 'UPGRADE' then
1557: hr_utility.set_location(l_proc,175);
1555: and name = 'P_CHANGE_MODE';
1556: if m_mode = 'UPGRADE' then
1557: hr_utility.set_location(l_proc,175);
1558: select number_value into l_prev_step_value
1559: from hr_api_transaction_values
1560: where transaction_step_id = p_transaction_step_id
1561: and name = 'P_PREV_STEP_VALUE';
1562: select number_value into l_prev_prof_lvl_value
1563: from hr_api_transaction_values
1559: from hr_api_transaction_values
1560: where transaction_step_id = p_transaction_step_id
1561: and name = 'P_PREV_STEP_VALUE';
1562: select number_value into l_prev_prof_lvl_value
1563: from hr_api_transaction_values
1564: where transaction_step_id = p_transaction_step_id
1565: and name = 'P_PREUPD_PROFICY_LVL_ID';
1566: select date_value into l_prev_start_date
1567: from hr_api_transaction_values
1563: from hr_api_transaction_values
1564: where transaction_step_id = p_transaction_step_id
1565: and name = 'P_PREUPD_PROFICY_LVL_ID';
1566: select date_value into l_prev_start_date
1567: from hr_api_transaction_values
1568: where transaction_step_id = p_transaction_step_id
1569: and name = 'P_PREV_START_DATE';
1570: end if;
1571: end if;
1633: ,p_rows out nocopy number) is
1634: l_proc varchar2(200) := g_package || 'get_pending_addition_ids';
1635: cursor get_add_ids (p_transaction_id number) is
1636: select step.transaction_step_id
1637: from hr_api_transaction_steps step, hr_api_transaction_values val
1638: where step.transaction_id = p_transaction_id
1639: and val.transaction_step_id = step.transaction_step_id
1640: and val.varchar2_value = 'ADD';
1641: l_index number;
1659: ,p_item_key IN varchar2) is
1660: l_proc varchar2(200) := g_package || 'del_pen_currupd_ids';
1661: cursor get_upd_ids(p_transaction_id number) is
1662: select steps.transaction_step_id
1663: from hr_api_transaction_values val, hr_api_transaction_steps steps
1664: where steps.transaction_id = p_transaction_id
1665: and steps.transaction_step_id = val.transaction_step_id
1666: and val.name = 'P_CHANGE_MODE'
1667: and val.varchar2_value IN ('UPDATE_APPLY','UPDATE_HIST');
1677: -- Added for competence Qualification link enhancement
1678: -- HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
1679: -- p_transaction_step_id => I.transaction_step_id);
1680: -- End for competence Qualification link enhancement
1681: delete from hr_api_transaction_values
1682: where transaction_step_id = I.transaction_step_id;
1683: delete from hr_api_transaction_steps
1684: where transaction_step_id = I.transaction_step_id;
1685: end loop;
1679: -- p_transaction_step_id => I.transaction_step_id);
1680: -- End for competence Qualification link enhancement
1681: delete from hr_api_transaction_values
1682: where transaction_step_id = I.transaction_step_id;
1683: delete from hr_api_transaction_steps
1684: where transaction_step_id = I.transaction_step_id;
1685: end loop;
1686: del_add_ids(p_item_type, p_item_key);
1687: commit;
1692: ,p_item_key IN varchar2) is
1693: l_proc varchar2(200) := g_package || 'del_add_ids';
1694: cursor get_add_ids(p_transaction_id number) is
1695: select steps.transaction_step_id
1696: from hr_api_transaction_values val, hr_api_transaction_steps steps
1697: where steps.transaction_id = p_transaction_id
1698: and steps.transaction_step_id = val.transaction_step_id
1699: and val.varchar2_value = 'ADD';
1700: l_step_values number ;
1705: (p_item_type => p_item_type
1706: ,p_item_key => p_item_key);
1707: for I in get_add_ids(l_transaction_id) loop
1708: hr_utility.set_location(l_proc || 'LOOP' ,10);
1709: delete from hr_api_transaction_values
1710: where transaction_step_id = I.transaction_step_id;
1711: delete from hr_api_transaction_steps
1712: where transaction_id = l_transaction_id
1713: and transaction_step_id = I.transaction_step_id;
1707: for I in get_add_ids(l_transaction_id) loop
1708: hr_utility.set_location(l_proc || 'LOOP' ,10);
1709: delete from hr_api_transaction_values
1710: where transaction_step_id = I.transaction_step_id;
1711: delete from hr_api_transaction_steps
1712: where transaction_id = l_transaction_id
1713: and transaction_step_id = I.transaction_step_id;
1714: end loop ;
1715: hr_utility.set_location(' Leaving:' || l_proc,15);
1779: ,p_item_key in varchar2) is
1780: l_proc varchar2(200) := g_package || 'delete_all_ids';
1781: cursor get_all_ids (p_transaction_id number) is
1782: select transaction_step_id
1783: from hr_api_transaction_steps
1784: where transaction_id = p_transaction_id;
1785: l_result number ;
1786: l_transaction_id number ;
1787: begin
1790: (p_item_type => p_item_type
1791: ,p_item_key => p_item_key);
1792: for l_result in get_all_ids(p_transaction_id => l_transaction_id) loop
1793: hr_utility.set_location(l_proc || 'LOOP',10);
1794: delete from hr_api_transaction_values
1795: where transaction_step_id = l_result.transaction_step_id;
1796: delete from hr_api_transaction_steps
1797: where transaction_id = l_transaction_id
1798: and transaction_step_id = l_result.transaction_step_id;
1792: for l_result in get_all_ids(p_transaction_id => l_transaction_id) loop
1793: hr_utility.set_location(l_proc || 'LOOP',10);
1794: delete from hr_api_transaction_values
1795: where transaction_step_id = l_result.transaction_step_id;
1796: delete from hr_api_transaction_steps
1797: where transaction_id = l_transaction_id
1798: and transaction_step_id = l_result.transaction_step_id;
1799: end loop ;
1800: commit;
1812: BEGIN
1813: hr_utility.set_location(' Entering:' || l_proc,5);
1814: l_transaction_step_id := p_transaction_step_id;
1815: select transaction_id into l_transaction_id
1816: from hr_api_transaction_steps
1817: where transaction_step_id = l_transaction_step_id
1818: and rownum = 1;
1819: select varchar2_value
1820: into l_mode
1817: where transaction_step_id = l_transaction_step_id
1818: and rownum = 1;
1819: select varchar2_value
1820: into l_mode
1821: from hr_api_transaction_values
1822: where transaction_step_id = l_transaction_step_id
1823: and name = 'P_CHANGE_MODE';
1824: if l_mode = 'UPGRADE' then
1825: hr_utility.set_location(l_proc,10);
1824: if l_mode = 'UPGRADE' then
1825: hr_utility.set_location(l_proc,10);
1826: select number_value
1827: into l_competence_element_id
1828: from hr_api_transaction_values
1829: where transaction_step_id = l_transaction_step_id
1830: and NAME = 'P_UPGRADED_FROM_REC_ID';
1831: select a.transaction_step_id into l_txid
1832: from hr_api_transaction_values a,hr_api_transaction_values b,
1828: from hr_api_transaction_values
1829: where transaction_step_id = l_transaction_step_id
1830: and NAME = 'P_UPGRADED_FROM_REC_ID';
1831: select a.transaction_step_id into l_txid
1832: from hr_api_transaction_values a,hr_api_transaction_values b,
1833: hr_api_transaction_steps steps
1834: where steps.transaction_id = l_transaction_id
1835: and steps.transaction_step_id = a.transaction_step_id
1836: and a.name = 'P_CHANGE_MODE'
1829: where transaction_step_id = l_transaction_step_id
1830: and NAME = 'P_UPGRADED_FROM_REC_ID';
1831: select a.transaction_step_id into l_txid
1832: from hr_api_transaction_values a,hr_api_transaction_values b,
1833: hr_api_transaction_steps steps
1834: where steps.transaction_id = l_transaction_id
1835: and steps.transaction_step_id = a.transaction_step_id
1836: and a.name = 'P_CHANGE_MODE'
1837: and a.varchar2_value = 'UPGRADE'
1838: and steps.transaction_step_id = b.transaction_step_id
1839: and b.name = 'P_COMPETENCE_ELEMENT_ID'
1840: and b.number_value = l_competence_element_id
1841: and a.transaction_step_id <> l_transaction_step_id;
1842: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1843: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1839: and b.name = 'P_COMPETENCE_ELEMENT_ID'
1840: and b.number_value = l_competence_element_id
1841: and a.transaction_step_id <> l_transaction_step_id;
1842: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1843: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1840: and b.number_value = l_competence_element_id
1841: and a.transaction_step_id <> l_transaction_step_id;
1842: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1843: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1848: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1841: and a.transaction_step_id <> l_transaction_step_id;
1842: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1843: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1848: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1849: end if;
1843: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1848: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1849: end if;
1850: commit ;
1851: hr_utility.set_location(' Leaving:' || l_proc,15);
1844: delete from hr_api_transaction_values where transaction_step_id = l_txid ;
1845: delete from hr_api_transaction_steps where transaction_step_id = l_txid ;
1846: else
1847: delete from hr_api_transaction_values where transaction_step_id = l_transaction_step_id ;
1848: delete from hr_api_transaction_steps where transaction_step_id = l_transaction_step_id ;
1849: end if;
1850: commit ;
1851: hr_utility.set_location(' Leaving:' || l_proc,15);
1852: END;
2145: raise hr_comp_profile_ss.g_fatal_error;
2146: END IF;
2147: close get_prev_start_date;
2148: l_prev_start_date := l_prev_date;
2149: update hr_api_transaction_values val
2150: set val.varchar2_value = 'UPGRADE'
2151: where val.transaction_step_id in (select val1.transaction_step_id
2152: from hr_api_transaction_values val1,
2153: hr_api_transaction_steps steps
2148: l_prev_start_date := l_prev_date;
2149: update hr_api_transaction_values val
2150: set val.varchar2_value = 'UPGRADE'
2151: where val.transaction_step_id in (select val1.transaction_step_id
2152: from hr_api_transaction_values val1,
2153: hr_api_transaction_steps steps
2154: where steps.transaction_id = l_transaction_id
2155: and steps.transaction_step_id = val1.transaction_step_id
2156: and val1.name = 'P_COMPETENCE_ELEMENT_ID'
2149: update hr_api_transaction_values val
2150: set val.varchar2_value = 'UPGRADE'
2151: where val.transaction_step_id in (select val1.transaction_step_id
2152: from hr_api_transaction_values val1,
2153: hr_api_transaction_steps steps
2154: where steps.transaction_id = l_transaction_id
2155: and steps.transaction_step_id = val1.transaction_step_id
2156: and val1.name = 'P_COMPETENCE_ELEMENT_ID'
2157: and val1.number_value = p_competence_element_id)
2190: l_trans_tbl(l_count).param_name := 'P_STATUS';
2191: l_trans_tbl(l_count).param_value := p_competence_status ;
2192: l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
2193: select number_value into l_competence_element_id
2194: from hr_api_transaction_values
2195: where transaction_step_id = l_transaction_step_id
2196: and NAME = 'P_UPGRADED_FROM_REC_ID';
2197: select a.transaction_step_id into l_tx_step_id
2198: from hr_api_transaction_values a,hr_api_transaction_values b,
2194: from hr_api_transaction_values
2195: where transaction_step_id = l_transaction_step_id
2196: and NAME = 'P_UPGRADED_FROM_REC_ID';
2197: select a.transaction_step_id into l_tx_step_id
2198: from hr_api_transaction_values a,hr_api_transaction_values b,
2199: hr_api_transaction_steps steps
2200: where steps.transaction_id = l_transaction_id
2201: and steps.transaction_step_id = a.transaction_step_id
2202: and a.name = 'P_CHANGE_MODE'
2195: where transaction_step_id = l_transaction_step_id
2196: and NAME = 'P_UPGRADED_FROM_REC_ID';
2197: select a.transaction_step_id into l_tx_step_id
2198: from hr_api_transaction_values a,hr_api_transaction_values b,
2199: hr_api_transaction_steps steps
2200: where steps.transaction_id = l_transaction_id
2201: and steps.transaction_step_id = a.transaction_step_id
2202: and a.name = 'P_CHANGE_MODE'
2203: and a.varchar2_value = 'UPGRADE'
2206: and b.number_value = l_competence_element_id
2207: and a.transaction_step_id <> l_transaction_step_id;
2208: if l_tx_step_id is not null then
2209: hr_utility.set_location(l_proc,15);
2210: update hr_api_transaction_values
2211: set date_value = l_preupd_date_to
2212: where transaction_step_id = l_tx_step_id
2213: and name = 'P_EFF_DATE_TO';
2214: end if;
2542: END if;
2543: hr_utility.set_location(' Leaving:' || l_proc,10);
2544: -- added for competency start date > outcome start date
2545: Select count(*) INTO l_count
2546: FROM hr_api_transaction_steps S,
2547: hr_api_transaction_values A,
2548: hr_api_transaction_values C,
2549: hr_api_transaction_values D
2550: Where s.item_type = p_item_type
2543: hr_utility.set_location(' Leaving:' || l_proc,10);
2544: -- added for competency start date > outcome start date
2545: Select count(*) INTO l_count
2546: FROM hr_api_transaction_steps S,
2547: hr_api_transaction_values A,
2548: hr_api_transaction_values C,
2549: hr_api_transaction_values D
2550: Where s.item_type = p_item_type
2551: and s.item_key = p_item_key
2544: -- added for competency start date > outcome start date
2545: Select count(*) INTO l_count
2546: FROM hr_api_transaction_steps S,
2547: hr_api_transaction_values A,
2548: hr_api_transaction_values C,
2549: hr_api_transaction_values D
2550: Where s.item_type = p_item_type
2551: and s.item_key = p_item_key
2552: and s.activity_id = nvl((p_activity_id),s.activity_id)
2545: Select count(*) INTO l_count
2546: FROM hr_api_transaction_steps S,
2547: hr_api_transaction_values A,
2548: hr_api_transaction_values C,
2549: hr_api_transaction_values D
2550: Where s.item_type = p_item_type
2551: and s.item_key = p_item_key
2552: and s.activity_id = nvl((p_activity_id),s.activity_id)
2553: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2582: p_app_short_name => 'PER',
2583: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2584: else
2585: Select count(*) INTO l_count
2586: FROM hr_api_transaction_steps S,
2587: hr_api_transaction_values A,
2588: hr_api_transaction_values C,
2589: hr_api_transaction_values D
2590: Where s.item_type = p_item_type
2583: P_SINGLE_ERROR_MESSAGE => 'HR_COMP_INV_LEVEL_SS');
2584: else
2585: Select count(*) INTO l_count
2586: FROM hr_api_transaction_steps S,
2587: hr_api_transaction_values A,
2588: hr_api_transaction_values C,
2589: hr_api_transaction_values D
2590: Where s.item_type = p_item_type
2591: and s.item_key = p_item_key
2584: else
2585: Select count(*) INTO l_count
2586: FROM hr_api_transaction_steps S,
2587: hr_api_transaction_values A,
2588: hr_api_transaction_values C,
2589: hr_api_transaction_values D
2590: Where s.item_type = p_item_type
2591: and s.item_key = p_item_key
2592: and s.activity_id = nvl((p_activity_id),s.activity_id)
2585: Select count(*) INTO l_count
2586: FROM hr_api_transaction_steps S,
2587: hr_api_transaction_values A,
2588: hr_api_transaction_values C,
2589: hr_api_transaction_values D
2590: Where s.item_type = p_item_type
2591: and s.item_key = p_item_key
2592: and s.activity_id = nvl((p_activity_id),s.activity_id)
2593: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2661: where competence_id = p_competence_id;
2662: --bug 2946360 Fix
2663: cursor c1 is
2664: select 'F' status
2665: from hr_api_transaction_steps s, hr_api_transaction_values a,
2666: hr_api_transaction_values b, hr_api_transaction_values d
2667: Where s.item_type = p_item_type
2668: and s.item_key = p_item_key
2669: and s.transaction_step_id = a.transaction_step_id
2662: --bug 2946360 Fix
2663: cursor c1 is
2664: select 'F' status
2665: from hr_api_transaction_steps s, hr_api_transaction_values a,
2666: hr_api_transaction_values b, hr_api_transaction_values d
2667: Where s.item_type = p_item_type
2668: and s.item_key = p_item_key
2669: and s.transaction_step_id = a.transaction_step_id
2670: and s.transaction_step_id = b.transaction_step_id
2672: and a.name = 'P_COMPETENCE_ID' and a.number_value = p_competence_id
2673: and b.name = 'P_PERSON_ID' and b.number_value = p_person_id
2674: and d.name = 'P_CHANGE_MODE' and d.varchar2_value <> 'ADD'
2675: union
2676: select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
2677: where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
2678: and ts.transaction_id = t.transaction_id
2679: and t.selected_person_id = p_person_id and t.status = 'Y'
2680: and exists (Select 'e' From hr_api_transaction_values c
2676: select 'F' status from hr_api_transaction_steps ts, hr_api_transactions t
2677: where ts.api_name = 'HR_COMP_PROFILE_SS.PROCESS_API'
2678: and ts.transaction_id = t.transaction_id
2679: and t.selected_person_id = p_person_id and t.status = 'Y'
2680: and exists (Select 'e' From hr_api_transaction_values c
2681: Where c.transaction_step_id = ts.transaction_step_id
2682: and c.name = 'P_COMPETENCE_ID'
2683: and c.number_value = p_competence_id)
2684: union
2762:
2763: --FOR bug fix 4136402
2764: End If;
2765: Select count(*) INTO l_count
2766: FROM hr_api_transaction_steps S,
2767: hr_api_transaction_values A,
2768: hr_api_transaction_values C,
2769: hr_api_transaction_values D
2770: Where s.item_type = p_item_type
2763: --FOR bug fix 4136402
2764: End If;
2765: Select count(*) INTO l_count
2766: FROM hr_api_transaction_steps S,
2767: hr_api_transaction_values A,
2768: hr_api_transaction_values C,
2769: hr_api_transaction_values D
2770: Where s.item_type = p_item_type
2771: and s.item_key = p_item_key
2764: End If;
2765: Select count(*) INTO l_count
2766: FROM hr_api_transaction_steps S,
2767: hr_api_transaction_values A,
2768: hr_api_transaction_values C,
2769: hr_api_transaction_values D
2770: Where s.item_type = p_item_type
2771: and s.item_key = p_item_key
2772: and s.activity_id = nvl((p_activity_id),s.activity_id)
2765: Select count(*) INTO l_count
2766: FROM hr_api_transaction_steps S,
2767: hr_api_transaction_values A,
2768: hr_api_transaction_values C,
2769: hr_api_transaction_values D
2770: Where s.item_type = p_item_type
2771: and s.item_key = p_item_key
2772: and s.activity_id = nvl((p_activity_id),s.activity_id)
2773: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2787: END if;
2788:
2789: IF (p_eff_date_to IS NOT null) then
2790: Select count(*) INTO l_count
2791: FROM hr_api_transaction_steps S,
2792: hr_api_transaction_values A,
2793: hr_api_transaction_values C,
2794: hr_api_transaction_values D
2795: Where s.item_type = p_item_type
2788:
2789: IF (p_eff_date_to IS NOT null) then
2790: Select count(*) INTO l_count
2791: FROM hr_api_transaction_steps S,
2792: hr_api_transaction_values A,
2793: hr_api_transaction_values C,
2794: hr_api_transaction_values D
2795: Where s.item_type = p_item_type
2796: and s.item_key = p_item_key
2789: IF (p_eff_date_to IS NOT null) then
2790: Select count(*) INTO l_count
2791: FROM hr_api_transaction_steps S,
2792: hr_api_transaction_values A,
2793: hr_api_transaction_values C,
2794: hr_api_transaction_values D
2795: Where s.item_type = p_item_type
2796: and s.item_key = p_item_key
2797: and s.activity_id = nvl((p_activity_id),s.activity_id)
2790: Select count(*) INTO l_count
2791: FROM hr_api_transaction_steps S,
2792: hr_api_transaction_values A,
2793: hr_api_transaction_values C,
2794: hr_api_transaction_values D
2795: Where s.item_type = p_item_type
2796: and s.item_key = p_item_key
2797: and s.activity_id = nvl((p_activity_id),s.activity_id)
2798: and s.api_name = 'HR_COMP_OUTCOME_PROFILE_SS.PROCESS_API'
2889: hr_utility.set_location(l_proc , 15);
2890: -- Calling the procedure to delete related transaction step ids for outcome.
2891: /* HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
2892: p_transaction_step_id => to_number(j)); */
2893: delete from hr_api_transaction_values where transaction_step_id = to_number(j);
2894: delete from hr_api_transaction_steps where transaction_step_id = to_number(j);
2895: j := '' ;
2896: else
2897: hr_utility.set_location(l_proc , 20);
2890: -- Calling the procedure to delete related transaction step ids for outcome.
2891: /* HR_COMP_OUTCOME_PROFILE_SS.delete_add_page(
2892: p_transaction_step_id => to_number(j)); */
2893: delete from hr_api_transaction_values where transaction_step_id = to_number(j);
2894: delete from hr_api_transaction_steps where transaction_step_id = to_number(j);
2895: j := '' ;
2896: else
2897: hr_utility.set_location(l_proc , 20);
2898: j := j || substr(transaction_step_ids,i,1);
2910: hr_utility.set_location(' Entering:' || l_proc,5);
2911: l_transaction_id := hr_transaction_ss.get_transaction_id
2912: (p_item_type => p_item_type
2913: ,p_item_key => p_item_key);
2914: update hr_api_transaction_values val
2915: set val.varchar2_value = 'ADDITION'
2916: where transaction_step_id in (select transaction_step_id
2917: from hr_api_transaction_steps steps
2918: where steps.transaction_id = l_transaction_id)
2913: ,p_item_key => p_item_key);
2914: update hr_api_transaction_values val
2915: set val.varchar2_value = 'ADDITION'
2916: where transaction_step_id in (select transaction_step_id
2917: from hr_api_transaction_steps steps
2918: where steps.transaction_id = l_transaction_id)
2919: and val.name = 'P_CHANGE_MODE' and val.varchar2_value = 'ADD';
2920: hr_utility.set_location(' Leaving:' || l_proc,10);
2921: end add_to_addition;
3000: ,p_review_proc_call in varchar2) is
3001: l_proc varchar2(200) := g_package || 'write_proc_actid';
3002: cursor c1 is
3003: select transaction_step_id
3004: from hr_api_transaction_steps
3005: where item_type = p_item_type
3006: and item_key = p_item_key
3007: and activity_id = p_activity_id;
3008: l_step_values c1%rowtype;