[Home] [Help]
22: IS
23: CURSOR c0
24: IS
25: SELECT rank
26: FROM hz_dss_groups_b
27: WHERE dss_group_code= p_dss_group_code ;
28: result NUMBER ;
29: BEGIN
30: OPEN c0;
41: --------------------------------------------
42:
43: FUNCTION return_max_rank
44: -- Return the maximum rank in hz_dss_group_b , if number of rows is greater than 0.
45: -- 1 if no rows exist in hz_dss_groups_b
46: RETURN NUMBER
47: IS
48: CURSOR c0
49: IS
46: RETURN NUMBER
47: IS
48: CURSOR c0
49: IS
50: SELECT NVL(MAX(RANK),0)+ 1 FROM HZ_DSS_GROUPS_B;
51: result NUMBER ;
52: BEGIN
53: OPEN c0;
54: FETCH c0 INTO result ;
67: -- resequence_ranks_to_create
68: --------------------------------------------
69:
70: PROCEDURE resequence_ranks_to_create ( p_insert_before_group_rank IN NUMBER )
71: -- Resequence ranks in hz_dss_groups_b in lieu of creating a new DSG
72: IS
73: BEGIN
74: update hz_dss_groups_b set rank = rank + 1
75: where rank >= p_insert_before_group_rank ;
70: PROCEDURE resequence_ranks_to_create ( p_insert_before_group_rank IN NUMBER )
71: -- Resequence ranks in hz_dss_groups_b in lieu of creating a new DSG
72: IS
73: BEGIN
74: update hz_dss_groups_b set rank = rank + 1
75: where rank >= p_insert_before_group_rank ;
76:
77: END resequence_ranks_to_create;
78:
84: PROCEDURE resequence_ranks_to_update ( p_group_to_be_updated_rank IN NUMBER,
85: p_insert_before_group_rank IN NUMBER,
86: p_to_be_upd_group_code IN VARCHAR2,
87: p_order_before_group_code IN VARCHAR2)
88: -- Resequence ranks in hz_dss_groups_b in lieu of updating the rank of a DSG
89: IS
90: BEGIN
91: -- NOTE: IN THIS PROCEDURE WE DO NOT UPDATE THE RANK OF THE GROUP WHOSE RANK
92: -- NEEDS TO BE UPDATED. WE RESEQUENCE EVERYTHING ELSE OTHER THAN THAT.
100: -- THE GROUP TO BE UPDATED IS LEFT ALONE
101: -----------------------------------------------------------------------------------
102: -- FREEZE THE RANK OF GROUP TO BE UPDATED AND INCREMENT EVERY RANK THAT IS BIGGER
103: -- THAN THE RANK THAT WE WOULD WANT OUR GROUP TO MOVE INTO, AFTER THE UPDATE.
104: update hz_dss_groups_b set rank = rank + 1
105: where rank >= p_insert_before_group_rank and dss_group_code <> p_to_be_upd_group_code ;
106:
107: ------------------------------------------------------------------------------------------
108: -- RESEQUENCE 2: STARTS FROM (EXCLUDING) ONE LEVEL BELOW GROUP TO BE UPDATED AND GOES DOWN.
110:
111: -- TO FILL THE HOLE CREATED BY RESEQUENCING THE RANKS, WE NEED TO DECREMENT
112: -- THE RANK OF ALL GROUP CODES THAT ARE HIGHER THAN THE RANK OF THE GROUP,
113: -- WHOSE RANK HAS TO BE UPDATED.
114: update hz_dss_groups_b set rank = rank - 1
115: where rank > p_group_to_be_updated_rank ;
116:
117: -- INSERT LAST
118: ELSIF p_order_before_group_code = FND_API.G_MISS_CHAR
116:
117: -- INSERT LAST
118: ELSIF p_order_before_group_code = FND_API.G_MISS_CHAR
119: THEN
120: update hz_dss_groups_b set rank = rank - 1
121: where rank > p_group_to_be_updated_rank ;
122: END IF;
123:
124:
407: -- check whether record has been updated by another user. If not, lock it.
408: BEGIN
409: SELECT object_version_number, rowid, rank, status
410: INTO l_object_version_number, l_rowid, l_rank, l_status
411: FROM hz_dss_groups_b
412: WHERE dss_group_code = p_dss_group.dss_group_code
413: FOR UPDATE NOWAIT;
414: EXCEPTION
415: WHEN NO_DATA_FOUND THEN