APPS.PA_SEARCH_PVT dependencies on PA_COMPETENCE_CRITERIA_TEMP

Line 23: TYPE mandatory_flag IS TABLE OF pa_competence_criteria_temp.mandatory_flag%TYPE

19:
20: l_competence_id_tbl number_tbl;
21: l_rating_level_tbl number_tbl;
22:
23: TYPE mandatory_flag IS TABLE OF pa_competence_criteria_temp.mandatory_flag%TYPE
24: INDEX BY BINARY_INTEGER;
25: l_mandatory_flag_tbl mandatory_flag;
26:
27: TYPE competence_name IS TABLE OF per_competences.name%TYPE

Line 139: FROM PA_COMPETENCE_CRITERIA_TEMP

135: --Bug Ref 9212362
136: CURSOR cur_competence_id_search
137: IS
138: SELECT COUNT(1)
139: FROM PA_COMPETENCE_CRITERIA_TEMP
140: WHERE COMPETENCE_ID = -1;
141:
142: l_comp_id_num NUMBER := 0;
143: --Bug Ref #6144255

Line 769: INSERT INTO pa_competence_criteria_temp(competence_id,

765: -- insert competence critieria into a global temporary table.
766: -- that way I can join to the temp table to do the competence match
767: -- instead of doing the match once per competence.
768: FORALL i IN l_competence_id_tbl.FIRST .. l_competence_id_tbl.LAST
769: INSERT INTO pa_competence_criteria_temp(competence_id,
770: competence_name,
771: rating_level,
772: mandatory_flag)
773: VALUES(l_competence_id_tbl(i),

Line 788: -- FROM pa_competence_criteria_temp pct,

784: -- FORALL i IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
785: -- UPDATE pa_search_results_temp
786: -- SET (mandatory_competence_match,optional_competence_match)=
787: -- (SELECT /*+ ORDERED */nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0), nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0)
788: -- FROM pa_competence_criteria_temp pct,
789: -- per_competence_elements pce,
790: -- per_rating_levels prl
791: -- WHERE pce.competence_id = pct.competence_id
792: -- AND pce.person_id = l_person_id_tbl(i)

Line 817: FROM pa_competence_criteria_temp pct,

813: FORALL i IN l_person_id_tbl.FIRST .. l_person_id_tbl.LAST
814: UPDATE pa_search_results_temp
815: SET (mandatory_competence_match,optional_competence_match)=
816: (SELECT /*+ leading(PCE PCT ) use_nl(PCT) */nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0), nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0) --Bug 12701655
817: FROM pa_competence_criteria_temp pct,
818: per_competence_elements pce,
819: per_rating_levels prl
820: WHERE (pct.competence_id <> -1 AND
821: pce.competence_id = pct.competence_id)

Line 872: FROM pa_competence_criteria_temp pct

868: SET (mandatory_competence_match,optional_competence_match)=
869: (SELECT /*+ ORDERED */
870: (nvl(sum(decode(pct.mandatory_flag,'Y',1,0)),0) + nvl(mandatory_competence_match, 0)),
871: (nvl(sum(decode(pct.mandatory_flag,'N',1,0)),0) + nvl(optional_competence_match, 0))
872: FROM pa_competence_criteria_temp pct
873: WHERE pct.competence_id = -1
874: AND EXISTS ( SELECT 'Y'
875: FROM per_competence_elements pce,
876: per_competences pc,

Line 1713: DELETE FROM pa_competence_criteria_temp;

1709: fnd_file.put_line(FND_FILE.LOG,'about to clear the global temp tables');
1710: END IF;
1711:
1712: DELETE FROM pa_search_results_temp;
1713: DELETE FROM pa_competence_criteria_temp;
1714:
1715: IF p_debug_mode = 'Y' THEN
1716: fnd_file.put_line(FND_FILE.LOG,'done clearing the global temp tables');
1717: fnd_file.put_line(FND_FILE.LOG,'about to call run search API');