[Home] [Help]
2: /* $Header: PNSPEMPB.pls 120.7 2005/12/01 03:32:06 appldev ship $ */
3:
4: -------------------------------------------------------------------------------
5: -- PROCDURE : Insert_Row
6: -- DESCRIPTION : inserts a row in pn_space_assign_emp_all
7: -- SCOPE : PUBLIC
8: -- INVOKED FROM :
9: -- RETURNS : NONE
10: -- HISTORY :
55: IS
56:
57: CURSOR c IS
58: SELECT ROWID
59: FROM pn_space_assign_emp_all
60: WHERE emp_space_assign_id = x_emp_space_assign_id;
61:
62: l_err_msg VARCHAR2(30);
63: l_asgn_mode VARCHAR2(30);
119: INTO x_emp_space_assign_id
120: FROM DUAL;
121: END IF;
122:
123: INSERT INTO pn_space_assign_emp_all
124: (
125: attribute1,
126: attribute2,
127: attribute3,
249: IS
250:
251: CURSOR c1 IS
252: SELECT *
253: FROM pn_space_assign_emp_all
254: WHERE emp_space_assign_id = x_emp_space_assign_id
255: FOR UPDATE OF emp_space_assign_id NOWAIT;
256:
257: BEGIN
407: END Lock_Row;
408:
409: -------------------------------------------------------------------------------
410: -- PROCDURE : Update_Row
411: -- DESCRIPTION : updates a row in pn_space_assign_emp_all
412: -- SCOPE : PUBLIC
413: -- INVOKED FROM :
414: -- RETURNS : NONE
415: -- HISTORY :
512: SELECT pn_space_assign_emp_s.NEXTVAL
513: INTO l_emp_space_assign_id
514: FROM DUAL;
515:
516: INSERT INTO pn_space_assign_emp_all
517: (
518: emp_space_assign_id,
519: location_id,
520: person_id,
591: );
592:
593: END IF;
594:
595: UPDATE pn_space_assign_emp_all
596: SET attribute1 = x_attribute1,
597: attribute2 = x_attribute2,
598: attribute3 = x_attribute3,
599: attribute4 = x_attribute4,
646: END Update_Row;
647:
648: -------------------------------------------------------------------------------
649: -- PROCDURE : Delete_Row
650: -- DESCRIPTION : deletes a row from pn_space_assign_emp_all
651: -- SCOPE : PUBLIC
652: -- INVOKED FROM :
653: -- RETURNS : NONE
654: -- HISTORY :
667:
668: pnp_debug_pkg.debug ('PN_SPACE_ASSIGN_EMP_PKG.DELETE_ROW (+) SpcAsgnId: '
669: ||x_emp_space_assign_id);
670:
671: DELETE FROM pn_space_assign_emp_all
672: WHERE emp_space_assign_id = x_emp_space_assign_id;
673:
674:
675: IF (SQL%NOTFOUND) THEN
820: CURSOR check_emp_assignment IS
821: SELECT 'Y'
822: FROM DUAL
823: WHERE EXISTS (SELECT NULL
824: FROM pn_space_assign_emp_all
825: WHERE person_id = p_person_id
826: AND location_id = p_loc_id
827: AND cost_center_code = p_cost_cntr_code
828: AND emp_assign_start_date <= p_assgn_str_dt
832: CURSOR check_ccd_assignment IS
833: SELECT 'Y'
834: FROM DUAL
835: WHERE EXISTS (SELECT NULL
836: FROM pn_space_assign_emp_all
837: WHERE cost_center_code = p_cost_cntr_code
838: AND location_id = p_loc_id
839: AND emp_assign_start_date <= p_assgn_str_dt
840: AND person_id is null
872:
873: CURSOR get_person_assign_id( p_loc IN NUMBER
874: ,p_person IN NUMBER) IS
875: SELECT emp_space_assign_id
876: FROM pn_space_assign_emp_all
877: WHERE location_id = p_loc
878: AND person_id = p_person
879: AND emp_assign_start_date =
880: (SELECT MIN(emp_assign_start_date)
877: WHERE location_id = p_loc
878: AND person_id = p_person
879: AND emp_assign_start_date =
880: (SELECT MIN(emp_assign_start_date)
881: FROM pn_space_assign_emp_all
882: WHERE location_id = p_loc
883: AND person_id = p_person);
884:
885: CURSOR get_cc_assign_id( p_loc IN NUMBER
884:
885: CURSOR get_cc_assign_id( p_loc IN NUMBER
886: ,p_cc IN VARCHAR2) IS
887: SELECT emp_space_assign_id
888: FROM pn_space_assign_emp_all
889: WHERE location_id = p_loc
890: AND cost_center_code = p_cc
891: AND emp_assign_start_date =
892: (SELECT MIN(emp_assign_start_date)
889: WHERE location_id = p_loc
890: AND cost_center_code = p_cc
891: AND emp_assign_start_date =
892: (SELECT MIN(emp_assign_start_date)
893: FROM pn_space_assign_emp_all
894: WHERE location_id = p_loc
895: AND cost_center_code = p_cc);
896:
897: BEGIN