1 PACKAGE BODY JTF_EBI_PUB AS
2 /* $Header: JTFEIPUB.pls 120.1 2011/03/23 16:39:55 agunnam noship $ */
3
4 PROCEDURE get_res_start_and_end_date(
5 p_resource_id IN NUMBER
6 ,x_start_date OUT NOCOPY DATE
7 ,x_end_date OUT NOCOPY DATE
8 )
9 IS
10 BEGIN
11
12 SELECT START_DATE_ACTIVE, END_DATE_ACTIVE into x_start_date, x_end_date
13 FROM JTF_RS_RESOURCE_EXTNS
14 WHERE RESOURCE_ID = p_resource_id;
15
16 EXCEPTION
17 WHEN OTHERS THEN
18 NULL;
19 END get_res_start_and_end_date;
20
21 PROCEDURE set_no_op_for_similar_rows(
22 p_batch_id IN NUMBER
23 ,p_interface_id IN NUMBER
24 ,p_resource_id IN NUMBER
25 ,p_org_id IN NUMBER
26 )
27 IS
28 BEGIN
29
30 -- set operation to NO-OP for other rows which has same ResourceId and OrgId
31
32 UPDATE JTF_RS_SALESREPS_INT
36 AND INTERFACE_ID <> p_interface_id
33 SET OPERATION = 'NO-OP'
34 WHERE RESOURCE_ID = p_resource_id
35 AND ORG_ID = p_org_id
37 AND BATCH_ID = p_batch_id;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 NULL;
42 END set_no_op_for_similar_rows;
43
44
45 PROCEDURE process_resource_intf(
46 p_api_version IN VARCHAR2
47 ,p_commit IN VARCHAR2
48 ,p_batch_id IN NUMBER
49 ,p_user_name IN VARCHAR2
50 ,p_responsibility IN VARCHAR2
51 ,x_return_status OUT NOCOPY VARCHAR2
52 ,x_msg_count OUT NOCOPY NUMBER
53 ,x_msg_data OUT NOCOPY VARCHAR2
54 )
55 IS
56 BEGIN
57
58 FND_MSG_PUB.initialize;
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 -- set apps context
62 IF p_user_name IS NOT NULL AND p_responsibility IS NOT NULL
63 THEN
64 JTF_EBI_UTIL.set_apps_context(p_user_name,p_responsibility);
65 END IF;
66
67 -- setting MOAC context : Updated by SINDUKUR on 16-SEP-2009
68 mo_global.init('JTF',null);
69
70 --Pre Processing Logic
71 UPDATE JTF_RS_RESOURCE_EXTNS_INT set start_date_active = null where operation = 'UPDATE' and batch_id = p_batch_id;
72
73 --Call ORM API
74 JTF_RS_INTERFACE_PVT.import_resource(
75 p_batch_id => p_batch_id
76 ,x_return_status => x_return_status
77 ,x_msg_count => x_msg_count
78 ,x_msg_data => x_msg_data
79 );
80
81 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
82 RAISE FND_API.g_exc_unexpected_error;
83 END IF;
84
85 IF( JTF_EBI_UTIL.is_resource_error_exists (p_batch_id => p_batch_id) = FND_API.g_true ) THEN
86 x_return_status := 'P';
87 ELSE
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89 END IF;
90
91 IF p_commit = FND_API.g_true THEN
92 COMMIT;
93 END IF;
94
95 EXCEPTION
96 WHEN FND_API.g_exc_unexpected_error THEN
97 x_return_status := FND_API.g_ret_sts_error;
98 IF(x_msg_data IS NULL) THEN
99 FND_MSG_PUB.count_and_get(
100 p_encoded => FND_API.g_false
101 ,p_count => x_msg_count
102 ,p_data => x_msg_data
103 );
104 END IF;
105 WHEN OTHERS THEN
106 x_return_status := FND_API.g_ret_sts_unexp_error;
107 x_msg_data := SQLERRM ||' in Package JTF_EBI_PUB Procedure process_resource_intf';
108 END process_resource_intf;
109
110 PROCEDURE process_salesrep_intf(
111 p_api_version IN VARCHAR2
112 ,p_commit IN VARCHAR2
113 ,p_batch_id IN NUMBER
114 ,p_user_name IN VARCHAR2
115 ,p_responsibility IN VARCHAR2
116 ,x_return_status OUT NOCOPY VARCHAR2
117 ,x_msg_count OUT NOCOPY NUMBER
118 ,x_msg_data OUT NOCOPY VARCHAR2
119 )
120 IS
121 l_start_date DATE := NULL;
122 l_end_date DATE := NULL;
123 l_operation VARCHAR2(10);
124 CURSOR c_salesrep_int (p_batch_id IN NUMBER) IS
125 SELECT
126 interface_id, resource_id, org_id, start_date_active, end_date_active, operation
127 FROM
128 jtf_rs_salesreps_int
129 WHERE
130 batch_id = p_batch_id
131 ORDER BY
132 interface_id;
133 BEGIN
134
135 FND_MSG_PUB.initialize;
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137
138 -- set apps context
139 IF p_user_name IS NOT NULL AND p_responsibility IS NOT NULL
140 THEN
141 JTF_EBI_UTIL.set_apps_context(p_user_name,p_responsibility);
142 END IF;
143
144 --Pre Processing Logic
145 FOR cur in c_salesrep_int(p_batch_id) LOOP
146 l_start_date := NULL;
147 l_end_date := NULL;
148
149 SELECT OPERATION INTO L_OPERATION FROM JTF_RS_SALESREPS_INT WHERE INTERFACE_ID = CUR.INTERFACE_ID;
150
151 IF (l_operation <> 'NO-OP') THEN
152
153 set_no_op_for_similar_rows(p_batch_id,cur.INTERFACE_ID, cur.RESOURCE_ID, cur.ORG_ID);
154
155 get_res_start_and_end_date(cur.RESOURCE_ID,l_start_date,l_end_date);
156
157 UPDATE JTF_RS_SALESREPS_INT
158 SET START_DATE_ACTIVE = l_start_date, END_DATE_ACTIVE = l_end_date
159 WHERE INTERFACE_ID = cur.INTERFACE_ID;
160
161 END IF;
162
163 END LOOP;
164
165 -- set org context
166 mo_global.init('JTF',null);
167
168 --call ORM API
169 JTF_RS_INTERFACE_PVT.import_salesreps(
170 p_batch_id => p_batch_id
171 ,x_return_status => x_return_status
172 ,x_msg_count => x_msg_count
173 ,x_msg_data => x_msg_data
174 );
175
176 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
177 RAISE FND_API.g_exc_unexpected_error;
178 END IF;
179
180 IF( JTF_EBI_UTIL.is_salesrep_error_exists (p_batch_id => p_batch_id) = FND_API.g_true ) THEN
181 x_return_status := 'P';
182 ELSE
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184 END IF;
185
186 IF p_commit = FND_API.g_true THEN
187 COMMIT;
188 END IF;
189
190 EXCEPTION
191 WHEN FND_API.g_exc_unexpected_error THEN
192 x_return_status := FND_API.g_ret_sts_error;
193 IF(x_msg_data IS NULL) THEN
194 FND_MSG_PUB.count_and_get(
195 p_encoded => FND_API.g_false
196 ,p_count => x_msg_count
197 ,p_data => x_msg_data
198 );
199 END IF;
200 WHEN OTHERS THEN
201 x_return_status := FND_API.g_ret_sts_unexp_error;
202 x_msg_data := SQLERRM ||' in Package JTF_EBI_PUB Procedure process_salesrep_intf';
203 END process_salesrep_intf;
204
205 END JTF_EBI_PUB;
[Home] [Help]