[Home] [Help]
PACKAGE BODY: APPS.CSL_MTL_UNIT_TRANS_ACC_PKG
Source
1 PACKAGE BODY CSL_MTL_UNIT_TRANS_ACC_PKG AS
2 /* $Header: cslutacb.pls 120.0 2005/05/24 18:15:19 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_MTL_UNIT_TRANS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('MTL_UNIT_TRANSACTIONS');
8 g_table_name CONSTANT VARCHAR2(30) := 'MTL_UNIT_TRANSACTIONS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'TRANSACTION_ID';
10 g_pk2_name CONSTANT VARCHAR2(30) := 'SERIAL_NUMBER';
11 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
12 g_debug_level NUMBER; -- debug level
13
14 PROCEDURE Insert_MTL_Unit_Trans(
15 p_resource_id NUMBER,
16 p_transaction_id NUMBER,
17 p_inventory_item_id NUMBER,
18 p_organization_id NUMBER,
19 p_subinventory_code VARCHAR2
20 )
21 IS
22
23 CURSOR c_mtl_unit_trans(b_transaction_id NUMBER
24 ,b_inventory_item_id NUMBER
25 ,b_organization_id NUMBER
26 ,b_subinventory_code VARCHAR2)
27 IS
28 SELECT TRANSACTION_ID, SERIAL_NUMBER
29 FROM MTL_UNIT_TRANSACTIONS
30 WHERE TRANSACTION_ID = b_transaction_id
31 AND INVENTORY_ITEM_ID = b_inventory_item_id
32 AND ORGANIZATION_ID = b_organization_id
33 AND SUBINVENTORY_CODE = b_subinventory_code;
34
35 r_mtl_unit_trans c_mtl_unit_trans%ROWTYPE;
36
37 BEGIN
38 /*** get debug level ***/
39 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
40
41 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
42 jtm_message_log_pkg.Log_Msg
43 ( v_object_id => p_transaction_id
44 , v_object_name => g_table_name
45 , v_message => 'Entering Insert_MTL_Unit_Trans'
46 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
47 END IF;
48
49 /*** Retreive record assigned by Hook ***/
50 OPEN c_mtl_unit_trans( p_transaction_id, p_inventory_item_id, p_organization_id, p_subinventory_code);
51 FETCH c_mtl_unit_trans INTO r_mtl_unit_trans;
52 IF c_mtl_unit_trans%NOTFOUND THEN
53 /*** could not find assignment record -> exit ***/
54 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
55 jtm_message_log_pkg.Log_Msg
56 ( v_object_id => p_transaction_id
57 , v_object_name => g_table_name
58 , v_message => 'Insert_MTL_Unit_Trans could not find records for transaction :' || p_transaction_id ||
59 ' , ' || p_inventory_item_id || p_organization_id ||
60 ' , ' || p_subinventory_code || ' for resource id ' || p_resource_id
61 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
62 END IF;
63
64 CLOSE c_mtl_unit_trans;
65 -- RETURN FALSE;
66 ELSE
67 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
68 jtm_message_log_pkg.Log_Msg
69 ( v_object_id => p_transaction_id
70 , v_object_name => g_table_name
71 , v_message => 'Inserting ACC record :' || p_transaction_id ||
72 ' , ' || p_inventory_item_id || p_organization_id ||
73 ' , ' || p_subinventory_code || ' for resource id ' || p_resource_id
74 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
75 END IF;
76
77 /*** Loop over all available records and put them in the acc table ***/
78 WHILE c_mtl_unit_trans%FOUND LOOP
79 /*** Call common package to insert record into ACC table ***/
80 JTM_HOOK_UTIL_PKG.Insert_Acc
81 ( p_publication_item_names => g_publication_item_name
82 ,p_acc_table_name => g_acc_table_name
83 ,p_resource_id => p_resource_id
84 ,p_pk1_name => g_pk1_name
85 ,p_pk1_num_value => r_mtl_unit_trans.TRANSACTION_ID
86 ,p_pk2_name => g_pk2_name
87 ,p_pk2_char_value => r_mtl_unit_trans.SERIAL_NUMBER
88 );
89 FETCH c_mtl_unit_trans INTO r_mtl_unit_trans;
90 END LOOP;
91 END IF;
92
93 END Insert_MTL_Unit_Trans;
94
95
96 PROCEDURE Update_MTL_Unit_Trans(
97 p_resource_id NUMBER,
98 p_transaction_id NUMBER,
99 p_inventory_item_id NUMBER,
100 p_organization_id NUMBER,
101 p_subinventory_code VARCHAR2
102 )
103 IS
104
105 BEGIN
106
107 /*** Not necassery because it will be an insert or delete updates will not be done from the csp_inv_loc_ass package ***/
108 /*** get debug level ***/
109 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
110
111 END Update_MTL_Unit_Trans;
112
113
114 PROCEDURE Delete_MTL_Unit_Trans(
115 p_resource_id NUMBER,
116 p_transaction_id NUMBER,
117 p_inventory_item_id NUMBER,
118 p_organization_id NUMBER,
119 p_subinventory_code VARCHAR2
120 )
121 IS
122
123 CURSOR c_mtl_unit_trans(b_transaction_id NUMBER
124 ,b_inventory_item_id NUMBER
125 ,b_organization_id NUMBER
126 ,b_subinventory_code VARCHAR2)
127 IS
128 SELECT TRANSACTION_ID, SERIAL_NUMBER
129 FROM MTL_UNIT_TRANSACTIONS
130 WHERE TRANSACTION_ID = b_transaction_id
131 AND INVENTORY_ITEM_ID = b_inventory_item_id
132 AND ORGANIZATION_ID = b_organization_id
133 AND SUBINVENTORY_CODE = b_subinventory_code;
134
135 r_mtl_unit_trans c_mtl_unit_trans%ROWTYPE;
136
137 BEGIN
138
139 /*** get debug level ***/
140 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
141
142 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
143 jtm_message_log_pkg.Log_Msg
144 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
145 , v_object_name => g_table_name
146 , v_message => 'Entering Delete_MTL_trans_lot_num'
147 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
148 END IF;
149
150 /*** Retreive record assigned by Hook ***/
151 OPEN c_mtl_unit_trans( p_transaction_id, p_inventory_item_id, p_organization_id, p_subinventory_code);
152 FETCH c_mtl_unit_trans INTO r_mtl_unit_trans;
153 IF c_mtl_unit_trans%NOTFOUND THEN
154 /*** could not find assignment record -> exit ***/
155 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
156 jtm_message_log_pkg.Log_Msg
157 ( v_object_id => p_transaction_id || ' , ' || p_inventory_item_id
158 , v_object_name => g_table_name
159 , v_message => 'Delete_MTL_trans_lot_num could not find transaction records for transaction :' || p_transaction_id ||
160 ' , ' || p_inventory_item_id || p_organization_id ||
161 ' , ' || p_subinventory_code || ' for resource id ' || p_resource_id
162 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
163 END IF;
164
165 CLOSE c_mtl_unit_trans;
166 -- RETURN FALSE;
167 ELSE
168 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
169 jtm_message_log_pkg.Log_Msg
170 ( v_object_id => p_organization_id
171 , v_object_name => g_table_name
172 , v_message => 'Deleting ACC record :' || p_transaction_id ||
173 ' , ' || p_inventory_item_id || p_organization_id ||
174 ' , ' || p_subinventory_code || ' for resource id ' || p_resource_id
175 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
176 END IF;
177
178 /*** Loop over all available records and put them in the acc table ***/
179 WHILE c_mtl_unit_trans%FOUND LOOP
180 /*** Call common package to insert record into ACC table ***/
181 JTM_HOOK_UTIL_PKG.Delete_Acc
182 ( p_publication_item_names => g_publication_item_name
183 ,p_acc_table_name => g_acc_table_name
184 ,p_resource_id => p_resource_id
185 ,p_pk1_name => g_pk1_name
186 ,p_pk1_num_value => r_mtl_unit_trans.TRANSACTION_ID
187 ,p_pk2_name => g_pk2_name
188 ,p_pk2_char_value => r_mtl_unit_trans.SERIAL_NUMBER
189 );
190 FETCH c_mtl_unit_trans INTO r_mtl_unit_trans;
191 END LOOP;
192 END IF;
193
194 END Delete_MTL_Unit_Trans;
195
196 /*Delete all records for non-existing user ( e.g user was deleted )*/
197 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
198 , x_return_status OUT NOCOPY VARCHAR2 )
199 IS
200 BEGIN
201 /*** get debug level ***/
202 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
203 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
204 jtm_message_log_pkg.Log_Msg
205 ( p_resource_id
206 , g_table_name
207 , 'Entering DELETE_ALL_ACC_RECORDS'
208 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
209 );
210 END IF;
211
212 DELETE JTM_MTL_UNIT_TRANS_ACC
213 WHERE RESOURCE_ID = p_resource_id;
214
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
218 jtm_message_log_pkg.Log_Msg
219 ( p_resource_id
220 , g_table_name
221 , 'Leaving DELETE_ALL_ACC_RECORDS'
222 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
223 );
224 END IF;
225 EXCEPTION WHEN OTHERS THEN
226 /*** hook failed -> log error ***/
227 -- x_return_status := FND_API.G_RET_STS_ERROR;
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
230 jtm_message_log_pkg.Log_Msg
231 ( v_object_id => 1
232 , v_object_name => g_table_name
233 , v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
234 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
235 END IF;
236 fnd_msg_pub.Add_Exc_Msg('CSL_MTL_UNIT_TRANS_ACC_PKG','PROCESS_ACC',sqlerrm);
237 END DELETE_ALL_ACC_RECORDS;
238
239
240 END CSL_MTL_UNIT_TRANS_ACC_PKG;