[Home] [Help]
PACKAGE BODY: APPS.CSM_CSP_REQ_LINES_EVENT_PKG
Source
1 PACKAGE BODY CSM_CSP_REQ_LINES_EVENT_PKG
2 /* $Header: csmerlb.pls 120.1.12010000.3 2008/11/18 08:02:39 trajasek ship $*/
3 AS
4 --
5 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
6 -- directory of SQL Navigator
7 --
8 -- Purpose: Briefly explain the functionality of the package body
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 g_table_name1 CONSTANT VARCHAR2(30) := 'CSP_REQUIREMENT_LINES';
16 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_LINES_ACC';
17 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_REQ_LINES_ACC_S';
18 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSM_REQ_LINES');
20 g_pk1_name1 CONSTANT VARCHAR2(30) := 'REQUIREMENT_LINE_ID';
21
22 PROCEDURE CSP_REQ_LINES_MDIRTY_I(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
23 IS
24 l_sqlerrno VARCHAR2(20);
25 l_sqlerrmsg VARCHAR2(4000);
26 l_error_msg VARCHAR2(4000);
27 l_return_status VARCHAR2(2000);
28
29 BEGIN
30 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_I for requirement_line_id: ' || p_requirement_line_id,
31 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
32
33 CSM_ACC_PKG.Insert_Acc
34 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
35 ,P_ACC_TABLE_NAME => g_acc_table_name1
36 ,P_SEQ_NAME => g_acc_sequence_name1
37 ,P_PK1_NAME => g_pk1_name1
38 ,P_PK1_NUM_VALUE => p_requirement_line_id
39 ,P_USER_ID => p_user_id
40 );
41
42 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_I for requirement_line_id: ' || p_requirement_line_id,
43 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
44 EXCEPTION
45 WHEN OTHERS THEN
46 l_sqlerrno := to_char(SQLCODE);
47 l_sqlerrmsg := substr(SQLERRM, 1,2000);
48 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_I for requirement_line_id:'
49 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
50 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
51 RAISE;
52 END CSP_REQ_LINES_MDIRTY_I;
53
54 PROCEDURE CSP_REQ_LINES_MDIRTY_D(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
55 IS
56 l_sqlerrno VARCHAR2(20);
57 l_sqlerrmsg VARCHAR2(4000);
58 l_error_msg VARCHAR2(4000);
59 l_return_status VARCHAR2(2000);
60
61 BEGIN
62 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_D for requirement_line_id: ' || p_requirement_line_id,
63 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
64
65 CSM_ACC_PKG.Delete_Acc
66 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
67 ,P_ACC_TABLE_NAME => g_acc_table_name1
68 ,P_PK1_NAME => g_pk1_name1
69 ,P_PK1_NUM_VALUE => p_requirement_line_id
70 ,P_USER_ID => p_user_id
71 );
72
73 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_D for requirement_line_id: ' || p_requirement_line_id,
74 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
75 EXCEPTION
76 WHEN OTHERS THEN
77 l_sqlerrno := to_char(SQLCODE);
78 l_sqlerrmsg := substr(SQLERRM, 1,2000);
79 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_D for requirement_line_id:'
80 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
81 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
82 RAISE;
83 END CSP_REQ_LINES_MDIRTY_D;
84
85 PROCEDURE CSP_REQ_LINES_MDIRTY_U(p_requirement_line_id IN NUMBER, p_user_id IN NUMBER)
86 IS
87 l_sqlerrno VARCHAR2(20);
88 l_sqlerrmsg VARCHAR2(4000);
89 l_error_msg VARCHAR2(4000);
90 l_return_status VARCHAR2(2000);
91 l_access_id NUMBER;
92
93 BEGIN
94 CSM_UTIL_PKG.LOG('Entering CSP_REQ_LINES_MDIRTY_U for requirement_line_id: ' || p_requirement_line_id,
95 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
96
97 l_access_id := CSM_ACC_PKG.Get_Acc_Id
98 ( P_ACC_TABLE_NAME => g_acc_table_name1
99 ,P_PK1_NAME => g_pk1_name1
100 ,P_PK1_NUM_VALUE => p_requirement_line_id
101 ,P_USER_ID => p_user_id
102 );
103
104 IF l_access_id <> -1 THEN
105 CSM_ACC_PKG.Update_Acc
106 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
107 ,P_ACC_TABLE_NAME => g_acc_table_name1
108 ,P_ACCESS_ID => l_access_id
109 ,P_USER_ID => p_user_id
110 );
111 END IF;
112
113 CSM_UTIL_PKG.LOG('Leaving CSP_REQ_LINES_MDIRTY_U for requirement_line_id: ' || p_requirement_line_id,
114 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_PROCEDURE);
115 EXCEPTION
116 WHEN OTHERS THEN
117 l_sqlerrno := to_char(SQLCODE);
118 l_sqlerrmsg := substr(SQLERRM, 1,2000);
119 l_error_msg := ' Exception in CSP_REQ_LINES_MDIRTY_U for requirement_line_id:'
120 || to_char(p_requirement_line_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
121 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CSP_REQ_LINES_EVENT_PKG.CSP_REQ_LINES_MDIRTY_U',FND_LOG.LEVEL_EXCEPTION);
122 RAISE;
123 END CSP_REQ_LINES_MDIRTY_U;
124
125
126 PROCEDURE CONC_ORDER_UPDATE(p_status OUT NOCOPY VARCHAR2,
127 p_message OUT NOCOPY VARCHAR2)
128 IS
129
130
131 /*** get the last run date of the concurent program ***/
132 CURSOR c_LastRundate
133 IS
134 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
135 FROM JTM_CON_REQUEST_DATA
136 WHERE package_name = 'CSM_CSP_REQ_LINES_EVENT_PKG'
137 AND procedure_name = 'CONC_ORDER_UPDATE';
138
139 r_LastRundate c_LastRundate%ROWTYPE;
140
141 CURSOR c_order_info (b_last_run_date DATE)
142 IS
143 SELECT acc.user_id
144 , acc.access_id
145 FROM csm_req_lines_acc acc
146 , CSP_REQ_LINE_DETAILS crld
147 , OE_ORDER_LINES_ALL ol
148 WHERE acc.requirement_line_id = crld.requirement_line_id
149 AND crld.source_id = ol.line_id
150 AND ol.LAST_UPDATE_DATE >= b_last_run_date ;
151
152
153 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
154 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
155 g_debug_level NUMBER;
156 l_dummy BOOLEAN;
157 l_current_run_date DATE;
158
159 BEGIN
160 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
161 jtm_message_log_pkg.Log_Msg
162 ( 1
163 , g_table_name1
164 , 'Entering CONC_ORDER_UPDATE hook'
165 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
166 END IF;
167
168 l_current_run_date := sysdate;
169
170 /*** First retrieve last run date of the conccurent program ***/
171 OPEN c_LastRundate;
172 FETCH c_LastRundate INTO r_LastRundate;
173 CLOSE c_LastRundate;
174
175 l_tab_access_id.DELETE;
176 l_tab_user_id.DELETE;
177
178
179 OPEN c_order_info(r_LastRundate.LAST_RUN_DATE);
180 FETCH c_order_info BULK COLLECT INTO l_tab_user_id, l_tab_access_id;
181 CLOSE c_order_info;
182
183 IF l_tab_access_id.COUNT > 0 THEN
184 /*** 1 or more acc rows retrieved -> push to resource ***/
185 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
186 jtm_message_log_pkg.Log_Msg
187 ( l_tab_access_id(1)
188 , g_table_name1
189 , 'Updating Order Lines'
190 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
191 END IF;
192
193 FOR i IN l_tab_access_id.FIRST..l_tab_access_id.LAST
194 LOOP
195 -- mark dirty the record
196 l_dummy := asg_download.mark_dirty(
197 p_pub_item => g_publication_item_name1(1)
198 , p_accessid => l_tab_access_id(i)
199 , p_userid => l_tab_user_id(i)
200 , p_dml => 'U'
201 , p_timestamp => l_current_run_date
202 );
203
204 END LOOP;
205 END IF;--IF l_tab_access_id.COUNT > 0
206
207
208 UPDATE JTM_CON_REQUEST_DATA
209 SET LAST_RUN_DATE = l_current_run_date
210 WHERE package_name = 'CSM_CSP_REQ_LINES_EVENT_PKG'
211 AND procedure_name = 'CONC_ORDER_UPDATE';
212
213 COMMIT;
214
215 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
216 jtm_message_log_pkg.Log_Msg
217 ( 1
218 , g_table_name1
219 , 'Leaving CONC_ORDER_UPDATE hook'
220 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
221 END IF;
222
223 p_status := 'FINE';
224 p_message := 'CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE Executed successfully';
225
226 RETURN;
227 EXCEPTION WHEN OTHERS THEN
228 p_status := 'ERROR';
229 p_message := 'Error in CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE: ' || substr(SQLERRM, 1, 2000);
230 /*** hook failed -> log error ***/
231 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
232 jtm_message_log_pkg.Log_Msg
233 ( 1
234 , g_table_name1
235 , 'Caught exception in CONC_ORDER_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
236 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
237 END IF;
238 fnd_msg_pub.Add_Exc_Msg('CSM_CSP_REQ_LINES_EVENT_PKG','CONC_ORDER_UPDATE',sqlerrm);
239 END CONC_ORDER_UPDATE;
240
241 END CSM_CSP_REQ_LINES_EVENT_PKG;