[Home] [Help]
PACKAGE BODY: APPS.INV_3PL_BILLING_COUNTER_PVT
Source
1 PACKAGE BODY INV_3PL_BILLING_COUNTER_PVT AS
2 /* $Header: INVVBLCB.pls 120.1 2010/05/25 11:10:42 damahaja noship $ */
3
4
5 g_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_3PL_BILLING_COUNTER_PVT';
7
8 PROCEDURE debug( p_message IN VARCHAR2 )
9 IS
10 BEGIN
11 inv_log_util.trace(p_message, G_PKG_NAME , 10 );
12 EXCEPTION
13 WHEN OTHERS THEN
14 NULL;
15 END debug;
16
17 FUNCTION get_top_counter_details (p_contract_id NUMBER, p_cle_id NUMBER)
18 RETURN NUMBER
19 AS
20 l_counter_item_id NUMBER;
21
22 CURSOR c_top_cntr IS
23 SELECT counter_details.counter_item_id,
24 counter_details.counter_name
25 FROM (
26 SELECT cnt_itm.jtot_object1_code, cont_headers.id contract_id,
27 cont_headers.contract_number, cnt_itm.object1_id1 counter_item_id,
28 counters.counter_name,line_Details.cle_id parent_service_line_id,
29 cnt_itm.id cntr_id , cnt_itm.cle_id
30 FROM okc_k_items cnt_itm
31 , csi_counters_v counters
32 , okc_k_lines_b line_details
33 , okc_k_headers_all_b cont_headers
34 WHERE cnt_itm.jtot_object1_code = 'OKX_COUNTER'
35 AND cont_headers.id = line_Details.dnz_chr_id
36 AND line_details.lse_id=13
37 AND cnt_itm.dnz_chr_id = line_Details.dnz_chr_id
38 AND cnt_itm.cle_id = line_Details.id
39 and cnt_itm.object1_id1 = counters.counter_id
40 AND cont_headers.id = p_contract_id
41 AND line_details.cle_id = p_cle_id
42 ORDER BY cnt_itm.id ASC
43 ) counter_details
44 WHERE ROWNUM <2 ;
45
46 BEGIN
47
48 FOR counter_rec IN c_top_cntr
49 LOOP
50 l_counter_item_id := counter_rec.counter_item_id ;
51 END LOOP;
52
53 RETURN l_counter_item_id;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 RETURN -99;
58
59 END get_top_counter_details;
60
61
62 PROCEDURE inv_insert_readings_using_api ( p_counter_id NUMBER,
63 p_count_date DATE,
64 p_new_reading NUMBER,
65 p_net_reading NUMBER,
66 p_transaction_id NUMBER
67 )
68 AS
69 l_return_status VARCHAR2(30);
70 l_msg_count NUMBER;
71 l_msg_data VARCHAR2(2000);
72 l_c_ind_txn BINARY_INTEGER := 0;
73 l_c_ind_rdg BINARY_INTEGER := 0;
74 l_c_ind_prop BINARY_INTEGER := 0;
75 l_transaction_tbl csi_datastructures_pub.transaction_tbl;
76 l_counter_readings_tbl csi_ctr_datastructures_pub.counter_readings_tbl;
77 l_ctr_property_readings_tbl csi_ctr_datastructures_pub.ctr_property_readings_tbl;
78 l_transaction_type_id NUMBER;
79 l_source_transaction_id NUMBER;
80 l_transaction_id NUMBER; /* Added for bug 9657044 */
81
82 BEGIN
83 IF g_debug = 1 THEN
84 debug('In INV_3PL_BILLING_COUNTER_PVT.inv_insert_readings_using_api ');
85 END IF;
86
87 l_transaction_tbl(l_c_ind_txn) := NULL;
88
89
90 SELECT cii.instance_id reference_number
91 INTO l_source_transaction_id
92 FROM okx_system_items_v it,
93 csi_item_instances cii,
94 cs_csi_counter_groups cg,
95 csi_counter_associations cca,
96 csi_counters_b ct,
97 csi_counter_readings cv
98 WHERE it.id1 = cii.inventory_item_id
99 AND it.organization_id = okc_context.get_okc_organization_id
100 AND cca.source_object_id = cii.instance_id
101 AND cca.source_object_code = 'CP'
102 AND ct.counter_id = cca.counter_id
103 AND ct.group_id = cg.counter_group_id
104 AND cv.counter_id (+) = ct.counter_id
105 AND cv.counter_value_id (+) = oks_auth_util_pvt.get_net_reading(ct.counter_id)
106 AND ct.counter_id = p_counter_id;
107
108 IF g_debug = 1 THEN
109 debug('Got item instance number for counter -> '||l_source_transaction_id);
110 END IF;
111
112 l_transaction_type_id := 80;
113 /* Added for bug 9657044 */
114 SELECT csi_transactions_s.NEXTVAL
115 INTO l_transaction_id
116 FROM dual;
117
118 -- ------ Starting Building Readings tables --------------
119
120 l_transaction_tbl(l_c_ind_txn).TRANSACTION_ID := NULL;
121 l_transaction_tbl(l_c_ind_txn).TRANSACTION_DATE := SYSDATE;
122 l_transaction_tbl(l_c_ind_txn).SOURCE_TRANSACTION_DATE := SYSDATE;
123 l_transaction_tbl(l_c_ind_txn).TRANSACTION_TYPE_ID := l_transaction_type_id;
124 l_transaction_tbl(l_c_ind_txn).TXN_SUB_TYPE_ID := NULL;
125 l_transaction_tbl(l_c_ind_txn).SOURCE_GROUP_REF_ID := NULL;
126 l_transaction_tbl(l_c_ind_txn).SOURCE_GROUP_REF := NULL;
127 l_transaction_tbl(l_c_ind_txn).SOURCE_HEADER_REF_ID := l_source_transaction_id;
128 l_transaction_tbl(l_c_ind_txn).SOURCE_HEADER_REF := NULL;
129 l_transaction_tbl(l_c_ind_txn).SOURCE_LINE_REF_ID := NULL;
130 l_transaction_tbl(l_c_ind_txn).SOURCE_LINE_REF := NULL;
131 l_transaction_tbl(l_c_ind_txn).SOURCE_DIST_REF_ID1 := NULL;
132 l_transaction_tbl(l_c_ind_txn).SOURCE_DIST_REF_ID2 := NULL;
133 l_transaction_tbl(l_c_ind_txn).INV_MATERIAL_TRANSACTION_ID := NULL;
134 l_transaction_tbl(l_c_ind_txn).TRANSACTION_QUANTITY := NULL;
135 l_transaction_tbl(l_c_ind_txn).TRANSACTION_UOM_CODE := NULL;
136 l_transaction_tbl(l_c_ind_txn).TRANSACTED_BY := NULL;
137 l_transaction_tbl(l_c_ind_txn).TRANSACTION_STATUS_CODE := NULL;
138 l_transaction_tbl(l_c_ind_txn).TRANSACTION_ACTION_CODE := NULL;
139 l_transaction_tbl(l_c_ind_txn).MESSAGE_ID := NULL;
140 l_transaction_tbl(l_c_ind_txn).CONTEXT := NULL;
141 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE1 := NULL;
142 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE2 := NULL;
143 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE3 := NULL;
144 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE4 := NULL;
145 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE5 := NULL;
146 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE6 := NULL;
147 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE7 := NULL;
148 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE8 := NULL;
149 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE9 := NULL;
150 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE10 := NULL;
151 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE11 := NULL;
152 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE12 := NULL;
153 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE13 := NULL;
154 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE14 := NULL;
155 l_transaction_tbl(l_c_ind_txn).ATTRIBUTE15 := NULL;
156 l_transaction_tbl(l_c_ind_txn).OBJECT_VERSION_NUMBER := NULL;
157 l_transaction_tbl(l_c_ind_txn).SPLIT_REASON_CODE := NULL;
158 l_transaction_tbl(l_c_ind_txn).SRC_TXN_CREATION_DATE := NULL;
159
160 IF g_debug = 1 THEN
161 debug('After L_TRANSACTION_TBL ');
162 END IF;
163
164 l_counter_readings_tbl(l_c_ind_rdg).COUNTER_VALUE_ID := NULL;
165 l_counter_readings_tbl(l_c_ind_rdg).COUNTER_ID := p_counter_id;
166 l_counter_readings_tbl(l_c_ind_rdg).VALUE_TIMESTAMP := p_count_date;
167 l_counter_readings_tbl(l_c_ind_rdg).COUNTER_READING := p_new_reading;
168 l_counter_readings_tbl(l_c_ind_rdg).RESET_MODE := NULL;
169 l_counter_readings_tbl(l_c_ind_rdg).RESET_REASON := NULL;
170 l_counter_readings_tbl(l_c_ind_rdg).ADJUSTMENT_TYPE := NULL;
171 l_counter_readings_tbl(l_c_ind_rdg).ADJUSTMENT_READING := NULL;
172 l_counter_readings_tbl(l_c_ind_rdg).OBJECT_VERSION_NUMBER := NULL;
173 l_counter_readings_tbl(l_c_ind_rdg).LAST_UPDATE_DATE := SYSDATE;
174 l_counter_readings_tbl(l_c_ind_rdg).LAST_UPDATED_BY := fnd_global.user_id;
175 l_counter_readings_tbl(l_c_ind_rdg).CREATION_DATE := SYSDATE;
176 l_counter_readings_tbl(l_c_ind_rdg).CREATED_BY := fnd_global.user_id;
177 l_counter_readings_tbl(l_c_ind_rdg).LAST_UPDATE_LOGIN := fnd_global.login_id;
178 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE1 := NULL;
179 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE2 := NULL;
180 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE3 := NULL;
181 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE4 := NULL;
182 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE5 := NULL;
183 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE6 := NULL;
184 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE7 := NULL;
185 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE8 := NULL;
186 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE9 := NULL;
187 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE10 := NULL;
188 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE11 := NULL;
189 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE12 := NULL;
190 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE13 := NULL;
191 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE14 := NULL;
192 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE15 := NULL;
193 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE16 := NULL;
194 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE17 := NULL;
195 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE18 := NULL;
196 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE19 := NULL;
197 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE20 := NULL;
198 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE21 := NULL;
199 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE22 := NULL;
200 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE23 := NULL;
201 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE24 := NULL;
202 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE25 := NULL;
203 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE26 := NULL;
204 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE27 := NULL;
205 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE28 := NULL;
206 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE29 := NULL;
207 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE30 := NULL;
208 l_counter_readings_tbl(l_c_ind_rdg).ATTRIBUTE_CATEGORY := NULL;
209 l_counter_readings_tbl(l_c_ind_rdg).MIGRATED_FLAG := 'N';
210 l_counter_readings_tbl(l_c_ind_rdg).COMMENTS := NULL;
211 l_counter_readings_tbl(l_c_ind_rdg).LIFE_TO_DATE_READING := NULL;
212 /* Added l_transaction_id for bug 9657044 */
213 l_counter_readings_tbl(l_c_ind_rdg).TRANSACTION_ID := l_transaction_id;
214 l_counter_readings_tbl(l_c_ind_rdg).AUTOMATIC_ROLLOVER_FLAG := NULL;
215 l_counter_readings_tbl(l_c_ind_rdg).INCLUDE_TARGET_RESETS := NULL;
216 l_counter_readings_tbl(l_c_ind_rdg).SOURCE_COUNTER_VALUE_ID := NULL;
217 l_counter_readings_tbl(l_c_ind_rdg).NET_READING := NULL;
218 l_counter_readings_tbl(l_c_ind_rdg).DISABLED_FLAG := 'N';
219 l_counter_readings_tbl(l_c_ind_rdg).SOURCE_CODE := NULL;
220 l_counter_readings_tbl(l_c_ind_rdg).SOURCE_LINE_ID := NULL;
221 l_counter_readings_tbl(l_c_ind_rdg).SECURITY_GROUP_ID := NULL;
222 l_counter_readings_tbl(l_c_ind_rdg).PARENT_TBL_INDEX := l_c_ind_txn;
223
224
225 IF g_debug = 1 THEN
226 debug('After L_COUNTER_READINGS_TBL ');
227 END IF;
228
229 l_ctr_property_readings_tbl(l_c_ind_prop).COUNTER_PROP_VALUE_ID := NULL;
230 l_ctr_property_readings_tbl(l_c_ind_prop).COUNTER_VALUE_ID := NULL;
231 l_ctr_property_readings_tbl(l_c_ind_prop).COUNTER_PROPERTY_ID := NULL;
232 l_ctr_property_readings_tbl(l_c_ind_prop).PROPERTY_VALUE := NULL;
233 l_ctr_property_readings_tbl(l_c_ind_prop).VALUE_TIMESTAMP := SYSDATE;
234 l_ctr_property_readings_tbl(l_c_ind_prop).OBJECT_VERSION_NUMBER := NULL;
235 l_ctr_property_readings_tbl(l_c_ind_prop).LAST_UPDATE_DATE := SYSDATE;
236 l_ctr_property_readings_tbl(l_c_ind_prop).LAST_UPDATED_BY := fnd_global.user_id;
237 l_ctr_property_readings_tbl(l_c_ind_prop).CREATION_DATE := SYSDATE;
238 l_ctr_property_readings_tbl(l_c_ind_prop).CREATED_BY := fnd_global.user_id;
239 l_ctr_property_readings_tbl(l_c_ind_prop).LAST_UPDATE_LOGIN := fnd_global.login_id;
240 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE1 := NULL;
241 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE2 := NULL;
242 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE3 := NULL;
243 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE4 := NULL;
244 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE5 := NULL;
245 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE6 := NULL;
246 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE7 := NULL;
247 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE8 := NULL;
248 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE9 := NULL;
249 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE10 := NULL;
250 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE11 := NULL;
251 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE12 := NULL;
252 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE13 := NULL;
253 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE14 := NULL;
254 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE15 := NULL;
255 l_ctr_property_readings_tbl(l_c_ind_prop).ATTRIBUTE_CATEGORY := NULL;
256 l_ctr_property_readings_tbl(l_c_ind_prop).MIGRATED_FLAG := 'N';
257 l_ctr_property_readings_tbl(l_c_ind_prop).SECURITY_GROUP_ID := NULL;
258 l_ctr_property_readings_tbl(l_c_ind_prop).PARENT_TBL_INDEX := NULL;
259
260 IF g_debug = 1 THEN
261 debug('After L_CTR_PROPERTY_READINGS_TBL ');
262 END IF;
263
264 csi_counter_readings_pub.capture_counter_reading(
265 p_api_version => 1.0,
266 p_commit => 'F',
267 p_init_msg_list => 'T',
268 p_validation_level => 10,
269 p_txn_tbl => l_transaction_tbl,
270 p_ctr_rdg_tbl => l_counter_readings_tbl,
271 p_ctr_prop_rdg_tbl => l_ctr_property_readings_tbl,
272 x_return_status => l_return_status,
273 x_msg_count => l_msg_count,
274 x_msg_data => l_msg_data
275 );
276
277 -- If API returns error, display the error message
278 -- otherwise commit the transaction.
279
280 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
281 IF g_debug = 1 THEN
282 debug('Error from IB api');
283 END IF;
284 RAISE fnd_api.g_exc_unexpected_error;
285 ELSE
286 COMMIT;
287 IF g_debug = 1 THEN
288 debug('Reading updated in IB counter table');
289 END IF;
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 RAISE;
295 END inv_insert_readings_using_api;
296
297 END INV_3PL_BILLING_COUNTER_PVT;