[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_CONC_GRP_DENORM_PUB
Source
1 PACKAGE BODY jtf_rs_conc_grp_denorm_pub AS
2 /* $Header: jtfrsbdb.pls 120.0 2005/05/11 08:19:16 appldev ship $ */
3
4
5 /*****************************************************************************************
6 This is a concurrent program to fetch all the records which are avaialble in
7 in JTF_RS_CHGD_GRP_RELATIONS Table. This is the intermidiate table which will keep all
8 the records which have to be updated / deleted / inserted in JTF_RS_GROUP_RELATIONS.
9 After successful processing the row will be deleted from JTF_RS_CHGD_GRP_RELATIONS.
10
11 ******************************************************************************************/
12
13
14 /* Package variables. */
15
16 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_CONC_GRP_DENORM_PUB';
17
18
19 PROCEDURE sync_grp_denorm
20 (ERRBUF OUT NOCOPY VARCHAR2,
21 RETCODE OUT NOCOPY VARCHAR2
22 )
23 IS
24 l_operation_flag VARCHAR2(1);
25 l_group_id NUMBER;
26 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
27 l_return_status VARCHAR2(200);
28 l_msg_count NUMBER;
29 l_msg_data VARCHAR2(200);
30 x_return_status VARCHAR2(200);
31 i NUMBER;
32 halt_operation EXCEPTION;
33 l_commit VARCHAR2(10) := FND_API.G_TRUE;
34
35
36 CURSOR c_get_grp IS
37 SELECT group_relate_id,
38 group_id,
39 related_group_id,
40 relation_type,
41 operation_flag,
42 rowid row_id
43 FROM JTF_RS_CHGD_GRP_RELATIONS
44 ORDER BY creation_date;
45 -- FOR UPDATE OF group_relate_id;
46
47 BEGIN
48
49 FOR l_grp IN c_get_grp LOOP
50
51 BEGIN
52
53 SAVEPOINT CONC_GROUP_SP;
54
55 IF l_grp.operation_flag = 'I' THEN
56
57 JTF_RS_GROUP_DENORM_PVT.INSERT_GROUPS
58 ( P_API_VERSION => 1.0,
59 P_INIT_MSG_LIST => l_init_msg_list,
60 P_COMMIT => 'F',
61 P_GROUP_ID => l_grp.group_id,
62 X_RETURN_STATUS => l_return_status,
63 X_MSG_COUNT => l_msg_count,
64 X_MSG_DATA => l_msg_data);
65
66 IF(l_return_status <> fnd_api.g_ret_sts_success)
67 THEN
68 FOR i in 1..l_msg_count
69 LOOP
70 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
71 p_encoded => fnd_api.g_false)));
72
73 END LOOP;
74
75 RAISE halt_operation;
76
77 END IF;
78
79
80 --call to insert records in jtf_rs_rep_managers
81 -- commented out on 04/25/2001 because now rep_managers will now be populated by
82 -- JTF_RS_GROUP_DENORM_PVT.INSERT_GROUPS api (called above)
83
84 /* JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_RELATIONS
85 ( P_API_VERSION => 1.0,
86 P_INIT_MSG_LIST => l_init_msg_list,
87 P_COMMIT => l_commit,
88 P_GROUP_RELATE_ID => l_grp.group_relate_id,
89 X_RETURN_STATUS => l_return_status,
90 X_MSG_COUNT => l_msg_count,
91 X_MSG_DATA => l_msg_data);
92
93 IF(l_return_status <> fnd_api.g_ret_sts_success)
94 THEN
95 FOR i in 1..l_msg_count
96 LOOP
97 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
98 p_encoded => fnd_api.g_false)));
99
100 END LOOP;
101
102 RAISE halt_operation;
103 END IF;
104 */
105
106 END IF;
107
108 IF l_grp.operation_flag = 'U' THEN
109
110 JTF_RS_GROUP_DENORM_PVT.UPDATE_GROUPS
111 ( P_API_VERSION => 1.0,
112 P_INIT_MSG_LIST => l_init_msg_list,
113 P_COMMIT => 'F',
114 P_GROUP_ID => l_grp.group_id,
115 X_RETURN_STATUS => l_return_status,
116 X_MSG_COUNT => l_msg_count,
117 X_MSG_DATA => l_msg_data);
118
119 IF(l_return_status <> fnd_api.g_ret_sts_success)
120 THEN
121 FOR i in 1..l_msg_count
122 LOOP
123 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
124 p_encoded => fnd_api.g_false)));
125
126 END LOOP;
127
128 RAISE halt_operation;
129 END IF;
130 END IF;
131
132 IF l_grp.operation_flag = 'D' THEN
133
134 JTF_RS_GROUP_DENORM_PVT.DELETE_GRP_RELATIONS
135 ( P_API_VERSION => 1.0,
136 P_INIT_MSG_LIST => l_init_msg_list,
137 P_COMMIT => 'F',
138 P_group_relate_id => l_grp.group_relate_id,
139 P_GROUP_ID => l_grp.group_id,
140 P_RELATED_GROUP_ID => l_grp.related_group_id,
141 X_RETURN_STATUS => l_return_status,
142 X_MSG_COUNT => l_msg_count,
143 X_MSG_DATA => l_msg_data);
144 IF(l_return_status <> fnd_api.g_ret_sts_success)
145 THEN
146 FOR i in 1..l_msg_count
147 LOOP
148 fnd_file.put_line(fnd_file.log, (fnd_msg_pub.get(i,
149 p_encoded => fnd_api.g_false)));
150
151 END LOOP;
152
153 RAISE halt_operation;
154 END IF;
155 END IF;
156
157 -- After sucessful completion delete the row
158
159 DELETE FROM JTF_RS_CHGD_GRP_RELATIONS
160 WHERE group_relate_id = l_grp.group_relate_id
161 AND rowid = l_grp.row_id ;
162 -- WHERE CURRENT OF c_get_grp ;
163
164
165 COMMIT;
166
167 EXCEPTION WHEN halt_operation THEN
168 ROLLBACK TO CONC_GROUP_SP;
169 END;
170
171 END LOOP;
172
173 EXCEPTION
174 WHEN fnd_api.g_exc_unexpected_error
175 THEN
176 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
177 ROLLBACK TO CONC_GROUP_SP;
178 WHEN OTHERS
179 THEN
180 fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
181 ROLLBACK TO CONC_GROUP_SP;
182
183 END sync_grp_denorm;
184
185 END jtf_rs_conc_grp_denorm_pub;