[Home] [Help]
PACKAGE BODY: APPS.OKS_SUBSCRIPTION_PVT
Source
1 PACKAGE BODY OKS_SUBSCRIPTION_PVT As
2 /* $Header: OKSRSUBB.pls 120.4 2006/03/31 13:24:44 skekkar noship $*/
3
4 Procedure create_default_schedule
5 ( p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY Number,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 p_cle_id IN NUMBER,
11 p_intent IN VARCHAR2
12 ) IS
13 Cursor kl_cur Is
14 Select KL.dnz_chr_id, KL.start_date, NVL(KL.date_terminated - 1,KL.end_date) end_date,
15 KI.number_of_items, KI.uom_code,
16 NVL(MTL.contract_item_type_code,'NON-SUB'), MTL.coverage_schedule_id, MTL.comms_nl_trackable_flag
17 From okc_k_lines_b KL,
18 okc_k_items KI,
19 mtl_system_items MTL
20 Where KL.id = p_cle_id
21 and KI.cle_id = p_cle_id
22 and MTL.inventory_item_id = TO_NUMBER(KI.object1_id1)
23 and MTL.organization_id = TO_NUMBER(KI.object1_id2);
24
25 Cursor osh_cur(p_template_id In Number) Is
26 Select name,
27 description,
28 cle_id,
29 dnz_chr_id,
30 subscription_type,
31 media_type,
32 frequency,
33 fulfillment_channel,
34 comments,
35 status,
36 item_type
37 From oks_subscr_header_v
38 Where id = p_template_id;
39
40 l_hdr_tbl_in OKS_SUBSCR_HDR_PVT.schv_tbl_type;
41 l_hdr_tbl_out OKS_SUBSCR_HDR_PVT.schv_tbl_type;
42 l_ptrns_tbl_in OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
43 l_ptrns_tbl_out OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
44 l_elems_tbl_in OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
45 l_elems_tbl_out OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
46 l_pattern_tbl OKS_SUBSCRIPTION_SCH_PVT.pattern_tbl;
47 l_delivery_tbl OKS_SUBSCRIPTION_SCH_PVT.del_tbl;
48 -- Pricing Parameters
49 l_price_details_in OKS_QP_PKG.INPUT_DETAILS;
50 x_price_details_out OKS_QP_PKG.PRICE_DETAILS;
51 x_mo_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
52 x_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
53 -- Pricing Parameters
54 g_chr_id Number;
55 l_start_date Date;
56 l_end_date Date;
57 l_qty Number;
58 l_uom Varchar2(10);
59 l_template_id Number;
60 l_instance_id Number := NULL;
61 l_status Varchar2(10);
62 l_itype Varchar2(30);
63 l_tangible Varchar2(1);
64 l_return_status Varchar2(20);
65 l_msg_count Number;
66 l_msg_data Varchar2(2000);
67 i Number;
68 idx Number;
69 tot_qty Number;
70 gen_exit EXCEPTION;
71 Begin
72 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
73 fnd_log.string(FND_LOG.LEVEL_PROCEDURE ,G_MODULE_CURRENT||'.create_default_schedule.begin'
74 ,'p_cle_id = '||p_cle_id||' ,p_intent = '||p_intent);
75 END IF;
76 x_return_status := 'S';
77 OKC_API.init_msg_list(p_init_msg_list);
78 l_status := 'I';
79 Open kl_cur;
80 Fetch kl_cur Into g_chr_id, l_start_date, l_end_date, l_qty, l_uom, l_itype, l_template_id, l_tangible;
81 IF kl_cur%NotFound THEN
82 Close kl_cur;
83 x_return_status := OKC_API.G_RET_STS_ERROR;
84 OKC_API.set_message
85 ( p_app_name => 'OKS',
86 p_msg_name => 'OKS_SUB_INVAL_LINE',
87 p_token1 => 'LINEID',
88 p_token1_value => p_cle_id
89 );
90 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
91 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.create_default_schedule.ERROR','Invalid Line');
92 END IF;
93 Raise gen_exit;
94 END IF;
95 Close kl_cur;
96 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
97 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.line_details',
98 'Header Id = '||g_chr_id||' ,Start Date = '||to_char(l_start_date,'DD-MON-YYYY')
99 ||' ,End Date = '||to_char(l_end_date,'DD-MON-YYYY')||' ,Quantity = '||l_qty
100 ||' ,UOM = '||l_uom||', Item Type Code = '||l_itype||' , Template Id = '||l_template_id
101 ||' ,NL Trackable(For Non-Subscription Items) = '||l_tangible
102 );
103 END IF;
104 IF l_itype = 'SUBSCRIPTION' Or l_tangible = 'Y' THEN
105 -- For Subscription Items, get the subscription header details from the template
106 If l_itype = 'SUBSCRIPTION' Then -- Tangible or Intangible Subscription Item
107 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
108 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.subs','it is a subscription item');
109 END IF;
110 if l_template_id is null then
111 x_return_status := OKC_API.G_RET_STS_ERROR;
112 OKC_API.set_message
113 ( p_app_name => 'OKS',
114 p_msg_name => 'OKS_SUB_NO_TMPL'
115 );
116 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
117 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.create_default_schedule.ERROR','No Template');
118 END IF;
119 Raise gen_exit;
120 end if;
121 For osh_rec In osh_cur(l_template_id) Loop
122 l_hdr_tbl_in(1).name := osh_rec.name;
123 l_hdr_tbl_in(1).description := osh_rec.description;
124 l_hdr_tbl_in(1).cle_id := p_cle_id;
125 l_hdr_tbl_in(1).dnz_chr_id := g_chr_id;
126 l_hdr_tbl_in(1).subscription_type := osh_rec.subscription_type;
127 l_hdr_tbl_in(1).media_type := osh_rec.media_type;
128 l_hdr_tbl_in(1).frequency := osh_rec.frequency;
129 l_hdr_tbl_in(1).fulfillment_channel := osh_rec.fulfillment_channel;
130 l_hdr_tbl_in(1).comments := osh_rec.comments;
131 l_hdr_tbl_in(1).item_type := osh_rec.item_type;
132 l_status := osh_rec.status;
133 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
134 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.template_details',
135 'Name = '||osh_rec.name||', Description = '||osh_rec.description
136 ||', Subs. Type = '||osh_rec.subscription_type||', Media Type = '||osh_rec.media_type
137 ||', Frequency = '||osh_rec.frequency||', Fulfill. Channel = '||osh_rec.fulfillment_channel
138 ||', Item Type = '||osh_rec.item_type||', Status = '||osh_rec.status
139 ||', Comments = '||osh_rec.comments);
140 END IF;
141 Exit;
142 End Loop;
143 if NVL(l_status, 'A') <> 'A' then
144 x_return_status := OKC_API.G_RET_STS_ERROR;
145 OKC_API.set_message
146 ( p_app_name => 'OKS',
147 p_msg_name => 'OKS_SUB_INACT_TMPL',
148 p_token1 => 'TMPL',
149 p_token1_value => l_hdr_tbl_in(1).name
150 );
151 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
152 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.create_default_schedule.ERROR','Inactive Template');
153 END IF;
154 Raise gen_exit;
155 end if;
156 -- Create Item Instance in the Installed Base --
157 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
158 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
159 'oks_auth_util_pub.create_cii_for_subscription(p_cle_id = '||p_cle_id||')');
160 END IF;
161 OKS_AUTH_UTIL_PUB.CREATE_CII_FOR_SUBSCRIPTION
162 (
163 p_api_version => p_api_version,
164 p_init_msg_list => p_init_msg_list,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 p_cle_id => p_cle_id,
169 x_instance_id => l_instance_id
170 );
171 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
172 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
173 'oks_auth_util_pub.create_cii_for_subscription(x_return_status = '||x_return_status
174 ||', x_instance_id = '||l_instance_id||')');
175 END IF;
176 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
177 Raise gen_exit;
178 End If;
179 l_hdr_tbl_in(1).instance_id := l_instance_id;
180 Else -- Tangible Non-Subscription Item
181 -- Create dummy subscription header for Tangible Non-Subscription Items
182 l_hdr_tbl_in(1).name := 'Non-Sub Item'; -- Dummy Hard Coded Value, change this
183 l_hdr_tbl_in(1).description := 'Non-Subscription Item (Tangible)'; -- Dummy Hard Coded Value, change this
184 l_hdr_tbl_in(1).cle_id := p_cle_id;
185 l_hdr_tbl_in(1).dnz_chr_id := g_chr_id;
186 l_hdr_tbl_in(1).subscription_type := 'JRNL'; -- Dummy Hard Coded Value, make this a nullable column
187 l_hdr_tbl_in(1).frequency := 'D';
188 l_hdr_tbl_in(1).fulfillment_channel := 'OM';
189 l_hdr_tbl_in(1).item_type := 'NT';
190 End If;
191 l_hdr_tbl_in(1).status := 'A';
192 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
193 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
194 'oks_subscr_hdr_pub.insert_row');
195 END IF;
196 OKS_SUBSCR_HDR_PUB.insert_row
197 (
198 p_api_version => p_api_version,
199 p_init_msg_list => p_init_msg_list,
200 x_return_status => x_return_status,
201 x_msg_count => x_msg_count,
202 x_msg_data => x_msg_data,
203 p_schv_tbl => l_hdr_tbl_in,
204 x_schv_tbl => l_hdr_tbl_out
205 );
206 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
207 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
208 'oks_subscr_hdr_pub.insert_row(x_return_status = '||x_return_status||')');
209 END IF;
210 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
211 Raise gen_exit;
212 End If;
213
214 -- FOR TANGIBLE ITEMS(SUBSCRIPTION OR NON-SUB), CREATE DEFAULT PATTERN --
215 If l_hdr_tbl_out(1).fulfillment_channel <> 'NONE' then
216 -- CREATE DEFAULT SCHEDULE PATTERN --
217 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
218 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.tangible','it is a tangible item');
219 END IF;
220 l_ptrns_tbl_in(1).osh_id := l_hdr_tbl_out(1).id;
221 l_ptrns_tbl_in(1).dnz_chr_id := g_chr_id;
222 l_ptrns_tbl_in(1).dnz_cle_id := p_cle_id;
223 l_ptrns_tbl_in(1).seq_no := 1;
224 if l_itype = 'SUBSCRIPTION' then
225 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
226 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.tansub',
227 'it is a tangible subscription item. creating default pattern');
228 END IF;
229 l_ptrns_tbl_in(1).year := '*';
230 if l_hdr_tbl_out(1).frequency = 'M' then
231 l_ptrns_tbl_in(1).month := '*';
232 elsif l_hdr_tbl_out(1).frequency = 'W' then
233 l_ptrns_tbl_in(1).month := '*';
234 l_ptrns_tbl_in(1).week := '*';
235 elsif l_hdr_tbl_out(1).frequency = 'D' then
236 l_ptrns_tbl_in(1).month := '*';
237 l_ptrns_tbl_in(1).day := '*';
238 end if;
239 else -- non-subscription item, it will be shippable
240 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
241 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.tannonsub',
242 'it is a tangible non-subscription item. creating one-time schedule pattern');
243 END IF;
244 -- CREATE PATTERN FOR ONE-TIME SCHEDULE IF SHIPPABLE NON-SUBSCRIPTION ITEM
245 l_ptrns_tbl_in(1).year := to_char(l_start_date,'YYYY');
246 l_ptrns_tbl_in(1).month := to_char(l_start_date,'MM');
247 l_ptrns_tbl_in(1).day := to_char(l_start_date,'DD');
248 end if;
249 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
250 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
251 'oks_subscr_ptrns_pub.insert_row');
252 END IF;
253 OKS_SUBSCR_PTRNS_PUB.insert_row
254 (
255 p_api_version => p_api_version,
256 p_init_msg_list => p_init_msg_list,
257 x_return_status => x_return_status,
258 x_msg_count => x_msg_count,
259 x_msg_data => x_msg_data,
260 p_scpv_tbl => l_ptrns_tbl_in,
261 x_scpv_tbl => l_ptrns_tbl_out
262 );
263 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
264 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
265 'oks_subscr_ptrns_pub.insert_row(x_return_status = '||x_return_status||')');
266 END IF;
267 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
268 Raise gen_exit;
269 end if;
270
271 -- CALCULATE DEFAULT DELIVERY SCHEDULE --
272 l_pattern_tbl(1).yr_pattern := l_ptrns_tbl_out(1).year;
273 l_pattern_tbl(1).mth_pattern := l_ptrns_tbl_out(1).month;
274 l_pattern_tbl(1).week_pattern := l_ptrns_tbl_out(1).week;
275 l_pattern_tbl(1).wday_pattern := l_ptrns_tbl_out(1).week_day;
276 l_pattern_tbl(1).day_pattern := l_ptrns_tbl_out(1).day;
277 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
278 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
279 'oks_subscription_sch_pvt.calc_delivery_date');
280 END IF;
281 OKS_SUBSCRIPTION_SCH_PVT.calc_delivery_date
282 (
283 p_start_dt => l_start_date,
284 p_end_dt => l_end_date,
285 p_offset_dy => NULL,
286 p_freq => l_hdr_tbl_out(1).frequency,
287 p_pattern_tbl => l_pattern_tbl,
288 x_delivery_tbl => l_delivery_tbl,
289 x_return_status => x_return_status
290 );
291 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
292 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
293 'oks_subscription_sch_pvt.calc_delivery_date(x_return_status = '||x_return_status||')');
294 END IF;
295 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
296 Raise gen_exit;
297 end if;
298
299 -- CREATE SCHEDULE ELEMENTS --
300 if l_delivery_tbl.COUNT > 0 then
301 idx := l_delivery_tbl.FIRST;
302 i := 1;
303 LOOP
304 l_elems_tbl_in(i).osh_id := l_hdr_tbl_out(1).id;
305 l_elems_tbl_in(i).dnz_chr_id := g_chr_id;
306 l_elems_tbl_in(i).dnz_cle_id := p_cle_id;
307 l_elems_tbl_in(i).seq_no := 1;
308 l_elems_tbl_in(i).om_interface_date := l_delivery_tbl(idx).delivery_date;
309 l_elems_tbl_in(i).start_date := l_delivery_tbl(idx).start_date;
310 l_elems_tbl_in(i).end_date := l_delivery_tbl(idx).end_date;
311 l_elems_tbl_in(i).quantity := l_qty;
312 l_elems_tbl_in(i).uom_code := l_uom;
313 Exit When idx = l_delivery_tbl.LAST;
314 idx := l_delivery_tbl.NEXT(idx);
315 i := i + 1;
316 END LOOP;
317 tot_qty := i * l_qty;
318 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
319 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
320 'oks_subscr_elems_pub.insert_row');
321 END IF;
322 OKS_SUBSCR_ELEMS_PUB.insert_row
323 (
324 p_api_version => p_api_version,
325 p_init_msg_list => p_init_msg_list,
326 x_return_status => x_return_status,
327 x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data,
329 p_scev_tbl => l_elems_tbl_in,
330 x_scev_tbl => l_elems_tbl_out
331 );
332 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
333 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
334 'oks_subscr_elems_pub.insert_row(x_return_status = '||x_return_status||')');
335 END IF;
336 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
337 Raise gen_exit;
338 end if;
339 else
340 Null; -- No Schedule for this line. Handle exception here if needed
341 end if; -- l_delivery_tbl.COUNT
342 Else
343 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
344 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.intansub',
345 'it is an intangible subscription item. only header is created');
346 END IF;
347 End If;
348 ELSE
349 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
350 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.create_default_schedule.intannonsub',
351 'it is an intangible non-subscription item. nothing created (only pricing will be called)');
352 END IF;
353 END IF;
354 -- PRICE THE TOP LINE
355 l_price_details_in.line_id := p_cle_id;
356 l_price_details_in.intent := NVL(p_intent,'SB_P');
357 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
358 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.before',
359 'oks_qp_int_pvt.compute_price(p_detail_rec.line_id = '||l_price_details_in.line_id||
360 ', p_detail_rec.intent = '||l_price_details_in.intent||')');
361 END IF;
362 OKS_QP_INT_PVT.compute_price
363 (
364 p_api_version => p_api_version,
365 p_init_msg_list => p_init_msg_list,
366 x_return_status => x_return_status,
367 x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data,
369 p_detail_rec => l_price_details_in,
370 x_price_details => x_price_details_out,
371 x_modifier_details => x_mo_details,
372 x_price_break_details => x_pb_details
373 );
374 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
375 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.create_default_schedule.external_call.after',
376 'oks_qp_int_pvt.compute_price(x_return_status = '||x_return_status||')');
377 END IF;
378 IF NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
379 Raise gen_exit;
380 END IF;
381 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
382 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.create_default_schedule.end',' ');
383 END IF;
384 Exception
385 When gen_exit Then
386 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
387 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE_CURRENT||'.create_default_schedule.EXCEPTION','gen_exit');
388 END IF;
389 When OTHERS Then
390 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
391 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.create_default_schedule.UNEXPECTED',
392 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
393 END IF;
394 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
395 OKC_API.set_message
396 ( p_app_name => 'OKS',
397 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
398 p_token1 => 'ERROR_CODE',
399 p_token1_value => sqlcode,
400 p_token2 => 'ERROR_MESSAGE',
401 p_token2_value => sqlerrm
402 );
403 End create_default_schedule;
404
405 Procedure recreate_schedule
406 ( p_api_version IN NUMBER,
407 p_init_msg_list IN VARCHAR2,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY Number,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 p_cle_id IN NUMBER,
412 p_intent IN VARCHAR2,
413 x_quantity OUT NOCOPY NUMBER
414 ) IS
415 Type tel_rec_type Is Record
416 ( id Number,
417 start_date Date,
418 end_date Date,
419 del_date Date,
420 qty Number,
421 uom Varchar2(10),
422 order_id Number,
423 o_v_num Number
424 );
425 Type tel_tbl_type is Table Of tel_rec_type Index By Binary_Integer;
426 Cursor kl_cur Is
427 Select KL.dnz_chr_id, KL.start_date, NVL(KL.date_terminated - 1,KL.end_date) end_date,
428 KL.price_negotiated, KI.number_of_items, KI.uom_code,
429 NVL(MTL.contract_item_type_code,'NON-SUB')
430 From okc_k_lines_b KL,
431 okc_k_items KI,
432 mtl_system_items MTL
433 Where KL.id = p_cle_id
434 and KI.cle_id = p_cle_id
435 and MTL.inventory_item_id = TO_NUMBER(KI.object1_id1)
436 and MTL.organization_id = TO_NUMBER(KI.object1_id2);
437
438 Cursor osh_cur Is
439 Select id,
440 instance_id,
441 frequency,
442 fulfillment_channel,
443 offset
444 From oks_subscr_header_b
445 Where cle_id = p_cle_id;
446
447 Cursor unitprice_cur Is
448 Select NVL(amount,0)/quantity unitprice, uom_code
449 From oks_subscr_elements
450 Where dnz_cle_id = p_cle_id
451 And rownum < 2;
452
453 Cursor ptrn_cur Is
454 Select id, object_version_number, year, month, week, week_day, day
455 From oks_subscr_patterns
456 Where dnz_cle_id = p_cle_id;
457
458 Cursor elem_cur Is
459 Select id, start_date, end_date, quantity, uom_code,
460 om_interface_date, order_header_id, object_version_number
461 From oks_subscr_elements
462 Where dnz_cle_id = p_cle_id;
463
464 Cursor tot_qty_cur Is
465 Select Sum(NVL(quantity,0))
466 From oks_subscr_elements
467 Where dnz_cle_id = p_cle_id;
468
469 l_ptrns_tbl_in OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
470 l_ptrns_tbl_out OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
471 l_elems_tbl_ins_in OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
472 l_elems_tbl_upd_in OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
473 l_elems_tbl_del_in OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
474 l_elems_tbl_out OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
475 l_pattern_tbl OKS_SUBSCRIPTION_SCH_PVT.pattern_tbl;
476 l_delivery_tbl OKS_SUBSCRIPTION_SCH_PVT.del_tbl;
477 l_hdr_tbl_in OKS_SUBSCR_HDR_PVT.schv_tbl_type;
478 l_hdr_tbl_out OKS_SUBSCR_HDR_PVT.schv_tbl_type;
479 tmp_elem_tbl tel_tbl_type;
480 -- Pricing Parameters
481 l_price_details_in OKS_QP_PKG.INPUT_DETAILS;
482 x_price_details_out OKS_QP_PKG.PRICE_DETAILS;
483 x_mo_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
484 x_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
485 -- Pricing Parameters
486 g_chr_id Number;
487 l_start_date Date;
488 l_end_date Date;
489 l_price_nego Number;
490 l_qty Number;
491 l_uom Varchar2(10);
492 l_itype Varchar2(30);
493 l_uom1 Varchar2(10);
494 l_unitprice Number;
495 l_elem_amount Number;
496 l_osh_id Number;
497 l_offset Number;
498 l_frequency Varchar2(10);
499 l_fulfill_chnl Varchar2(20);
500 l_ptrn_id Number;
501 l_ptrn_ovn Number;
502 i Number;
503 idx Number;
504 ins_idx Number := 0;
505 upd_idx Number := 0;
506 del_idx Number := 0;
507 l Number;
508 l_sch_found Boolean;
509 gen_exit EXCEPTION;
510
511 procedure create_insert_rec Is
512 begin
513 ins_idx := ins_idx + 1;
514 l_elems_tbl_ins_in(ins_idx).osh_id := l_osh_id;
515 l_elems_tbl_ins_in(ins_idx).dnz_chr_id := g_chr_id;
516 l_elems_tbl_ins_in(ins_idx).dnz_cle_id := p_cle_id;
517 l_elems_tbl_ins_in(ins_idx).seq_no := 1;
518 l_elems_tbl_ins_in(ins_idx).om_interface_date := l_delivery_tbl(i).delivery_date;
519 l_elems_tbl_ins_in(ins_idx).start_date := l_delivery_tbl(i).start_date;
520 l_elems_tbl_ins_in(ins_idx).end_date := l_delivery_tbl(i).end_date;
521 l_elems_tbl_ins_in(ins_idx).quantity := l_qty;
522 l_elems_tbl_ins_in(ins_idx).uom_code := l_uom;
523 If p_intent Is Null Then
524 l_elems_tbl_ins_in(ins_idx).amount := l_elem_amount;
525 End If;
526 end create_insert_rec;
527
528 procedure create_update_rec Is
529 begin
530 upd_idx := upd_idx + 1;
531 l_elems_tbl_upd_in(upd_idx).id := tmp_elem_tbl(idx).id;
532 l_elems_tbl_upd_in(upd_idx).osh_id := OKC_API.G_MISS_NUM;
533 l_elems_tbl_upd_in(upd_idx).dnz_chr_id := OKC_API.G_MISS_NUM;
534 l_elems_tbl_upd_in(upd_idx).dnz_cle_id := OKC_API.G_MISS_NUM;
535 l_elems_tbl_upd_in(upd_idx).seq_no := 1;
536 l_elems_tbl_upd_in(upd_idx).om_interface_date := l_delivery_tbl(i).delivery_date;
537 l_elems_tbl_upd_in(upd_idx).start_date := l_delivery_tbl(i).start_date;
538 l_elems_tbl_upd_in(upd_idx).end_date := l_delivery_tbl(i).end_date;
539 l_elems_tbl_upd_in(upd_idx).quantity := l_qty;
540 l_elems_tbl_upd_in(upd_idx).uom_code := l_uom;
541 l_elems_tbl_upd_in(upd_idx).order_header_id := OKC_API.G_MISS_NUM;
542 l_elems_tbl_upd_in(upd_idx).order_line_id := OKC_API.G_MISS_NUM;
543 l_elems_tbl_upd_in(upd_idx).object_version_number := tmp_elem_tbl(idx).o_v_num;
544 l_elems_tbl_upd_in(upd_idx).created_by := OKC_API.G_MISS_NUM;
545 l_elems_tbl_upd_in(upd_idx).creation_date := OKC_API.G_MISS_DATE;
546 l_elems_tbl_upd_in(upd_idx).last_updated_by := OKC_API.G_MISS_NUM;
547 l_elems_tbl_upd_in(upd_idx).last_update_date := OKC_API.G_MISS_DATE;
548 l_elems_tbl_upd_in(upd_idx).last_update_login := OKC_API.G_MISS_NUM;
549 If p_intent Is Null Then
550 l_elems_tbl_upd_in(ins_idx).amount := l_elem_amount;
551 End If;
552 end create_update_rec;
553
554 procedure create_delete_rec Is
555 begin
556 del_idx := del_idx + 1;
557 l_elems_tbl_del_in(del_idx).id := tmp_elem_tbl(i).id;
558 end create_delete_rec;
559
560 Begin
561 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
562 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.recreate_schedule.begin','p_cle_id = '||p_cle_id||
563 ' ,p_intent = '||p_intent);
564 END IF;
565 x_return_status := OKC_API.G_RET_STS_SUCCESS;
566 Open kl_cur;
567 Fetch kl_cur Into g_chr_id, l_start_date, l_end_date, l_price_nego, l_qty, l_uom, l_itype;
568 IF kl_cur%NotFound THEN
569 Close kl_cur;
570 x_return_status := OKC_API.G_RET_STS_ERROR;
571 OKC_API.set_message
572 ( p_app_name => 'OKS',
573 p_msg_name => 'OKS_SUB_INVAL_LINE',
574 p_token1 => 'LINEID',
575 p_token1_value => p_cle_id
576 );
577 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
578 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.recreate_schedule.ERROR','Invalid Subscription Line');
579 END IF;
580 Raise gen_exit;
581 END IF;
582 Close kl_cur;
583 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
584 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.line_details',
585 'Header Id = '||g_chr_id||' ,Start Date = '||to_char(l_start_date,'DD-MON-YYYY')
586 ||' ,End Date = '||to_char(l_end_date,'DD-MON-YYYY')||' ,Quantity = '||l_qty
587 ||' ,UOM = '||l_uom||', Item Type Code = '||l_itype
588 );
589 END IF;
590 -- If it is an intangible non-subscription item, then the cursor will not contain any records
591 -- and the value of l_fulfill_chnl will remain NULL
592 For osh_rec In osh_cur Loop
593 l_osh_id := osh_rec.id;
594 l_frequency := osh_rec.frequency;
595 l_fulfill_chnl := osh_rec.fulfillment_channel;
596 l_offset := osh_rec.offset;
597 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
598 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.subs_header_details',
599 'Id = '||osh_rec.id||', Fulfill. Channel = '||osh_rec.fulfillment_channel
600 ||', Frequency = '||osh_rec.frequency||', Offset = '||osh_rec.offset);
601 END IF;
602 Exit;
603 End Loop;
604
605 -- RECREATE DELIVERY SCHEDULE ONLY FOR TANGIBLE ITEMS(SUBSCRIPTION OR NON-SUB) --
606 IF l_fulfill_chnl <> 'NONE' THEN
607 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
608 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.tan',
609 'it is an tangible item');
610 END IF;
611 -- Need the unit price for populating the elements table if pricing is not called
612 -- (The procedure is called with p_intent=NULL while copying subscription, to skip pricing call)
613 If p_intent Is Null Then
614 Open unitprice_cur;
615 Fetch unitprice_cur Into l_unitprice, l_uom1;
616 l_sch_found := unitprice_cur%FOUND;
617 Close unitprice_cur;
618 If l_sch_found Then
619 l_elem_amount := l_unitprice * l_qty;
620 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
621 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.unitprice',
622 'Unit Price = '||l_unitprice||' ,UOM Code = '||l_uom1||
623 ' ,Elem. Amount = '||l_elem_amount
624 );
625 END IF;
626 End If;
627 End If;
628 -- CALCULATE NEW DELIVERY SCHEDULE --
629 idx := 1;
630 For ptrn_rec in ptrn_cur Loop
631 l_pattern_tbl(idx).yr_pattern := ptrn_rec.year;
632 l_pattern_tbl(idx).mth_pattern := ptrn_rec.month;
633 l_pattern_tbl(idx).week_pattern := ptrn_rec.week;
634 l_pattern_tbl(idx).wday_pattern := ptrn_rec.week_day;
635 l_pattern_tbl(idx).day_pattern := ptrn_rec.day;
636 If l_itype <> 'SUBSCRIPTION' Then
637 l_ptrn_id := ptrn_rec.id;
638 l_ptrn_ovn := ptrn_rec.object_version_number;
639 Exit; -- Only one pattern record for Non-Sub lines
640 End If;
641 idx := idx + 1;
642 End Loop;
643 If l_itype <> 'SUBSCRIPTION' Then
644 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
645 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.tannonsub',
646 'it is a tangible non-subscription item');
647 END IF;
648 if NVL(l_pattern_tbl(1).yr_pattern,'!') <> to_char(l_start_date,'YYYY') or
649 NVL(l_pattern_tbl(1).mth_pattern,'!') <> to_char(l_start_date,'MM') or
650 NVL(l_pattern_tbl(1).day_pattern,'!') <> to_char(l_start_date,'DD') then
651 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
652 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.updptrn',
653 'start date changed. the single record pattern will be updated');
654 END IF;
655 l_ptrns_tbl_in(1).id := l_ptrn_id;
656 l_ptrns_tbl_in(1).object_version_number := l_ptrn_ovn;
657 l_ptrns_tbl_in(1).osh_id := l_osh_id;
658 l_ptrns_tbl_in(1).dnz_chr_id := g_chr_id;
659 l_ptrns_tbl_in(1).dnz_cle_id := p_cle_id;
660 l_ptrns_tbl_in(1).seq_no := 1;
661 l_ptrns_tbl_in(1).year := to_char(l_start_date,'YYYY');
662 l_ptrns_tbl_in(1).month := to_char(l_start_date,'MM');
663 l_ptrns_tbl_in(1).day := to_char(l_start_date,'DD');
664 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
665 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
666 'oks_subscr_ptrns_pub.update_row(p_scpv_tbl(1).id = '||l_ptrns_tbl_in(1).id||')');
667 END IF;
668 OKS_SUBSCR_PTRNS_PUB.update_row
669 (
670 p_api_version => p_api_version,
671 p_init_msg_list => p_init_msg_list,
672 x_return_status => x_return_status,
673 x_msg_count => x_msg_count,
674 x_msg_data => x_msg_data,
675 p_scpv_tbl => l_ptrns_tbl_in,
676 x_scpv_tbl => l_ptrns_tbl_out
677 );
678 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
679 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
680 'oks_subscr_ptrns_pub.update_row(x_return_status = '||x_return_status||')');
681 END IF;
682 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
683 Raise gen_exit;
684 end if;
685 l_pattern_tbl(1).yr_pattern := to_char(l_start_date,'YYYY');
686 l_pattern_tbl(1).mth_pattern := to_char(l_start_date,'MM');
687 l_pattern_tbl(1).day_pattern := to_char(l_start_date,'DD');
688 end if;
689 End If;
690 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
691 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
692 'oks_subscription_sch_pvt.calc_delivery_date');
693 END IF;
694 OKS_SUBSCRIPTION_SCH_PVT.calc_delivery_date
695 (
696 p_start_dt => l_start_date,
697 p_end_dt => l_end_date,
698 p_offset_dy => l_offset,
699 p_freq => l_frequency,
700 p_pattern_tbl => l_pattern_tbl,
701 x_delivery_tbl => l_delivery_tbl,
702 x_return_status => x_return_status
703 );
704 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
705 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
706 'oks_subscription_sch_pvt.calc_delivery_date(x_return_status = '||x_return_status||')');
707 END IF;
708 If nvl(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
709 Raise gen_exit;
710 End If;
711 If l_delivery_tbl.COUNT <> 0 And p_intent Is Null And l_sch_found = FALSE Then
712 l_elem_amount := l_price_nego / l_delivery_tbl.COUNT;
713 End If;
714 -- GET THE EXISTING SCHEDULE --
715 For elem_rec in elem_cur Loop
716 idx := to_char(elem_rec.start_date,'YYYYMMDD');
717 tmp_elem_tbl(idx).id := elem_rec.id;
718 tmp_elem_tbl(idx).start_date := elem_rec.start_date;
719 tmp_elem_tbl(idx).end_date := elem_rec.end_date;
720 tmp_elem_tbl(idx).del_date := elem_rec.om_interface_date;
721 tmp_elem_tbl(idx).qty := elem_rec.quantity;
722 tmp_elem_tbl(idx).uom := elem_rec.uom_code;
723 tmp_elem_tbl(idx).order_id := elem_rec.order_header_id;
724 tmp_elem_tbl(idx).o_v_num := elem_rec.object_version_number;
725 End Loop;
726 If l_delivery_tbl.COUNT <> 0 Then
727 i := l_delivery_tbl.FIRST;
728 LOOP
729 idx := to_char(l_delivery_tbl(i).start_date,'YYYYMMDD');
730 if tmp_elem_tbl.EXISTS(idx) then
731 if tmp_elem_tbl(idx).order_id Is Null then
732 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
733 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.updelem',
734 'marking element for update: period start date - '
735 ||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
736 );
737 END IF;
738 create_update_rec;
739 tmp_elem_tbl.DELETE(idx);
740 else
741 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
742 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.noupdelem',
743 'not marking element for update since shipped: period start date - '
744 ||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
745 );
746 END IF;
747 end if;
748 else
749 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
750 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.inselem',
751 'marking element for insert: period start date - '
752 ||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
753 );
754 END IF;
755 create_insert_rec;
756 end if;
757 Exit When i = l_delivery_tbl.LAST;
758 i := l_delivery_tbl.NEXT(i);
759 END LOOP;
760 End If;
761 If tmp_elem_tbl.COUNT <> 0 Then
762 i := tmp_elem_tbl.FIRST;
763 LOOP
764 if tmp_elem_tbl(i).order_id Is Null then
765 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
766 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.delelem',
767 'marking element for delete: period start date - '
768 ||to_char(tmp_elem_tbl(i).start_date,'DD-MON-YYYY')
769 );
770 END IF;
771 create_delete_rec;
772 else
773 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
774 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_schedule.nodelelem',
775 'not marking element for delete since shipped: period start date - '
776 ||to_char(tmp_elem_tbl(i).start_date,'DD-MON-YYYY')
777 );
778 END IF;
779 end if;
780 Exit When i = tmp_elem_tbl.LAST;
781 i := tmp_elem_tbl.NEXT(i);
782 END LOOP;
783 End If;
784
785 -- DELETE UNWANTED SCHEDULE ELEMENTS --
786 If l_elems_tbl_del_in.COUNT <> 0 Then
787 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
788 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
789 'oks_subscr_elems_pub.delete_row');
790 END IF;
791 OKS_SUBSCR_ELEMS_PUB.delete_row
792 (
793 p_api_version => p_api_version,
794 p_init_msg_list => p_init_msg_list,
795 x_return_status => x_return_status,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data,
798 p_scev_tbl => l_elems_tbl_del_in
799 );
800 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
801 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
802 'oks_subscr_elems_pub.delete_row(x_return_status = '||x_return_status||')');
803 END IF;
804 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
805 Raise gen_exit;
806 end if;
807 End If;
808
809 -- UPDATE CHANGED SCHEDULE ELEMENTS --
810 If l_elems_tbl_upd_in.COUNT <> 0 Then
811 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
812 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
813 'oks_subscr_elems_pub.update_row');
814 END IF;
815 OKS_SUBSCR_ELEMS_PUB.update_row
816 (
817 p_api_version => p_api_version,
818 p_init_msg_list => p_init_msg_list,
819 x_return_status => x_return_status,
820 x_msg_count => x_msg_count,
821 x_msg_data => x_msg_data,
822 p_scev_tbl => l_elems_tbl_upd_in,
823 x_scev_tbl => l_elems_tbl_out
824 );
825 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
826 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
827 'oks_subscr_elems_pub.update_row(x_return_status = '||x_return_status||')');
828 END IF;
829 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
830 Raise gen_exit;
831 end if;
832 End If;
833
834 -- INSERT NEW SCHEDULE ELEMENTS --
835 If l_elems_tbl_ins_in.COUNT <> 0 Then
836 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
837 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
838 'oks_subscr_elems_pub.insert_row');
839 END IF;
840 OKS_SUBSCR_ELEMS_PUB.insert_row
841 (
842 p_api_version => p_api_version,
843 p_init_msg_list => p_init_msg_list,
844 x_return_status => x_return_status,
845 x_msg_count => x_msg_count,
846 x_msg_data => x_msg_data,
847 p_scev_tbl => l_elems_tbl_ins_in,
848 x_scev_tbl => l_elems_tbl_out
849 );
850 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
851 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
852 'oks_subscr_elems_pub.insert_row(x_return_status = '||x_return_status||')');
853 END IF;
854 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
855 Raise gen_exit;
856 end if;
857 End If;
858 Open tot_qty_cur;
859 Fetch tot_qty_cur into x_quantity;
860 Close tot_qty_cur;
861 ELSE -- Intangible Item (Subscription or Non-Subscription)
862 x_quantity := l_qty;
863 END IF;
864 -- Reprice the line if intention is good
865 IF p_intent Is Not NULL THEN
866 l_price_details_in.line_id := p_cle_id;
867 l_price_details_in.intent := p_intent;
868 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
869 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.before',
870 'oks_qp_int_pvt.compute_price(p_detail_rec.line_id = '||l_price_details_in.line_id||
871 ', p_detail_rec.intent = '||l_price_details_in.intent||')');
872 END IF;
873 OKS_QP_INT_PVT.compute_price
874 (
875 p_api_version => p_api_version,
876 p_init_msg_list => p_init_msg_list,
877 x_return_status => x_return_status,
878 x_msg_count => x_msg_count,
879 x_msg_data => x_msg_data,
880 p_detail_rec => l_price_details_in,
881 x_price_details => x_price_details_out,
882 x_modifier_details => x_mo_details,
883 x_price_break_details => x_pb_details
884 );
885 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
886 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_schedule.external_call.after',
887 'oks_qp_int_pvt.compute_price(x_return_status = '||x_return_status||')');
888 END IF;
889 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
890 Raise gen_exit;
891 End If;
892 END IF;
893 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
894 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.recreate_schedule.end','x_quantity = '||x_quantity);
895 END IF;
896 Exception
897 When gen_exit Then
898 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
899 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE_CURRENT||'.recreate_schedule.EXCEPTION','gen_exit');
900 END IF;
901 When OTHERS Then
902 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
903 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
904 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.recreate_schedule.UNEXPECTED',
905 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
906 END IF;
907 OKC_API.set_message
908 ( p_app_name => 'OKS',
909 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
910 p_token1 => 'ERROR_CODE',
911 p_token1_value => sqlcode,
912 p_token2 => 'ERROR_MESSAGE',
913 p_token2_value => sqlerrm
914 );
915 End recreate_schedule;
916
917 Procedure recreate_instance
918 ( p_api_version IN NUMBER,
919 p_init_msg_list IN VARCHAR2,
920 x_return_status OUT NOCOPY VARCHAR2,
921 x_msg_count OUT NOCOPY Number,
922 x_msg_data OUT NOCOPY VARCHAR2,
923 p_cle_id IN NUMBER,
924 p_custacct_id IN NUMBER
925 ) IS
926 l_osh_id Number;
927 l_instance_id Number;
928 l_item_type Varchar2(10);
929 CURSOR osh_cur IS
930 SELECT id,item_type
931 FROM oks_subscr_header_b
932 WHERE cle_id = p_cle_id;
933 gen_exit EXCEPTION;
934 Begin
935 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
936 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.recreate_instance.begin','p_cle_id = '||p_cle_id||
937 ' ,p_custacct_id = '||p_custacct_id);
938 END IF;
939 OPEN osh_cur;
940 FETCH osh_cur INTO l_osh_id,l_item_type;
941 IF osh_cur%NOTFOUND THEN
942 CLOSE osh_cur;
943 x_return_status := OKC_API.G_RET_STS_SUCCESS;
944 RAISE gen_exit;
945 END IF;
946 CLOSE osh_cur;
947 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
948 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_instance.osh_details',
949 'Osh Id = '||l_osh_id||' ,Item Type = '||l_item_type);
950 END IF;
951 IF l_item_type IN ('ST','SI') THEN
952 -- Create Item Instance in the Installed Base --
953 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
954 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_instance.external_call.before',
955 'oks_auth_util_pub.create_cii_for_subscription(p_cle_id = '||p_cle_id||')');
956 END IF;
957 OKS_AUTH_UTIL_PUB.CREATE_CII_FOR_SUBSCRIPTION
958 (
959 p_api_version => p_api_version,
960 p_init_msg_list => p_init_msg_list,
961 x_return_status => x_return_status,
962 x_msg_count => x_msg_count,
963 x_msg_data => x_msg_data,
964 p_cle_id => p_cle_id,
965 x_instance_id => l_instance_id
966 );
967 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
968 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.recreate_instance.external_call.after',
969 'oks_auth_util_pub.create_cii_for_subscription(x_return_status = '||x_return_status
970 ||', x_instance_id = '||l_instance_id||')');
971 END IF;
972 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
973 Raise gen_exit;
974 End If;
975 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
976 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_instance.updhdr.before',
977 'oks_subscr_header_b(id = '||l_osh_id||', instance_id = '||l_instance_id||')');
978 END IF;
979 UPDATE oks_subscr_header_b
980 SET instance_id = l_instance_id
981 WHERE id = l_osh_id;
982 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
983 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.recreate_instance.updhdr.after',' ');
984 END IF;
985 END IF;
986 x_return_status := OKC_API.G_RET_STS_SUCCESS;
987 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
988 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.recreate_instance.end',' ');
989 END IF;
990 EXCEPTION
991 When gen_exit Then
992 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
993 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE_CURRENT||'.recreate_instance.EXCEPTION','gen_exit');
994 END IF;
995 When OTHERS Then
996 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
997 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
998 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.recreate_instance.UNEXPECTED',
999 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
1000 END IF;
1001 OKC_API.set_message
1002 ( p_app_name => 'OKS',
1003 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
1004 p_token1 => 'ERROR_CODE',
1005 p_token1_value => sqlcode,
1006 p_token2 => 'ERROR_MESSAGE',
1007 p_token2_value => sqlerrm
1008 );
1009 End recreate_instance;
1010
1011 Procedure copy_subscription
1012 ( p_api_version IN NUMBER,
1013 p_init_msg_list IN VARCHAR2,
1014 x_return_status OUT NOCOPY VARCHAR2,
1015 x_msg_count OUT NOCOPY NUMBER,
1016 x_msg_data OUT NOCOPY VARCHAR2,
1017 p_source_cle_id IN NUMBER,
1018 p_target_cle_id IN NUMBER,
1019 p_intent IN VARCHAR2
1020 ) IS
1021 Cursor tgt_chr_cur Is
1022 Select dnz_chr_id
1023 From okc_k_lines_b
1024 Where id = p_target_cle_id
1025 And lse_id = 46;
1026 Cursor src_line_cur Is
1027 Select id
1028 From okc_k_lines_b
1029 Where id = p_source_cle_id
1030 And lse_id = 46;
1031 -- Pricing Parameters
1032 l_price_details_in OKS_QP_PKG.INPUT_DETAILS;
1033 x_price_details_out OKS_QP_PKG.PRICE_DETAILS;
1034 x_mo_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
1035 x_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
1036 -- Pricing Parameters
1037 g_chr_id Number;
1038 g_osh_id Number;
1039 l_instance_id Number;
1040 l_dummy Number;
1041 l_intent Varchar2(20);
1042 gen_exit EXCEPTION;
1043 Procedure copy_osh Is
1044 l_schv_tbl_in OKS_SUBSCR_HDR_PUB.schv_tbl_type;
1045 l_schv_tbl_out OKS_SUBSCR_HDR_PUB.schv_tbl_type;
1046 Cursor src_osh_cur Is
1047 select name, description, instance_id, subscription_type, media_type, frequency,
1048 fulfillment_channel, offset, comments, item_type
1049 from oks_subscr_header_v
1050 where cle_id = p_source_cle_id;
1051 Begin
1052 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1053 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.copy_subscription.copy_osh.begin',' ');
1054 END IF;
1055 For src_osh_rec In src_osh_cur
1056 Loop
1057 l_schv_tbl_in(1).name := src_osh_rec.name;
1058 l_schv_tbl_in(1).description := src_osh_rec.description;
1059 l_schv_tbl_in(1).cle_id := p_target_cle_id;
1060 l_schv_tbl_in(1).dnz_chr_id := g_chr_id;
1061 l_schv_tbl_in(1).instance_id := src_osh_rec.instance_id; -- If not renewing this will be overwritten later
1062 l_schv_tbl_in(1).subscription_type := src_osh_rec.subscription_type;
1063 l_schv_tbl_in(1).media_type := src_osh_rec.media_type;
1064 l_schv_tbl_in(1).frequency := src_osh_rec.frequency;
1065 l_schv_tbl_in(1).fulfillment_channel := src_osh_rec.fulfillment_channel;
1066 l_schv_tbl_in(1).offset := src_osh_rec.offset;
1067 l_schv_tbl_in(1).comments := src_osh_rec.comments;
1068 l_schv_tbl_in(1).status := 'A';
1069 l_schv_tbl_in(1).item_type := src_osh_rec.item_type;
1070 IF p_intent = 'COPY' THEN
1071 -- Create Item Instance in the Installed Base if called from subscription is getting copied
1072 If src_osh_rec.item_type In ('ST','SI') Then
1073 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1074 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osh.external_call.before',
1075 'oks_auth_util_pub.create_cii_for_subscription(p_cle_id = '||p_target_cle_id||')');
1076 END IF;
1077 OKS_AUTH_UTIL_PUB.CREATE_CII_FOR_SUBSCRIPTION
1078 (
1079 p_api_version => p_api_version,
1080 p_init_msg_list => p_init_msg_list,
1081 x_return_status => x_return_status,
1082 x_msg_count => x_msg_count,
1083 x_msg_data => x_msg_data,
1084 p_cle_id => p_target_cle_id,
1085 x_instance_id => l_instance_id
1086 );
1087 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1088 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osh.external_call.after',
1089 'oks_auth_util_pub.create_cii_for_subscription(x_return_status = '||x_return_status
1090 ||', x_instance_id = '||l_instance_id||')');
1091 END IF;
1092 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
1093 Raise gen_exit;
1094 end if;
1095 l_schv_tbl_in(1).instance_id := l_instance_id;
1096 End If;
1097 END IF;
1098 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1099 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osh.external_call.before',
1100 'oks_subscr_hdr_pub.insert_row');
1101 END IF;
1102 OKS_SUBSCR_HDR_PUB.insert_row
1103 ( p_api_version => p_api_version,
1104 p_init_msg_list => p_init_msg_list,
1105 x_return_status => x_return_status,
1106 x_msg_count => x_msg_count,
1107 x_msg_data => x_msg_data,
1108 p_schv_tbl => l_schv_tbl_in,
1109 x_schv_tbl => l_schv_tbl_out);
1110 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1111 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osh.external_call.after',
1112 'oks_subscr_hdr_pub.insert_row(x_return_status = '||x_return_status||')');
1113 END IF;
1114 If x_return_status = OKC_API.G_RET_STS_SUCCESS Then
1115 g_osh_id := l_schv_tbl_out(1).id;
1116 End If;
1117 End Loop;
1118 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1119 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.copy_subscription.copy_osh.end',' ');
1120 END IF;
1121 End copy_osh;
1122
1123 Procedure copy_osp Is
1124 l_scpv_tbl_in OKS_SUBSCR_PTRNS_PUB.scpv_tbl_type;
1125 l_scpv_tbl_out OKS_SUBSCR_PTRNS_PUB.scpv_tbl_type;
1126 Cursor src_osp_cur Is
1127 select year, month, week, week_day, day
1128 from oks_subscr_patterns
1129 where dnz_cle_id = p_source_cle_id;
1130 i Number := 0;
1131 Begin
1132 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1133 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.copy_subscription.copy_osp.begin',' ');
1134 END IF;
1135 For src_osp_rec In src_osp_cur
1136 Loop
1137 i := i + 1;
1138 l_scpv_tbl_in(i).osh_id := g_osh_id;
1139 l_scpv_tbl_in(i).dnz_cle_id := p_target_cle_id;
1140 l_scpv_tbl_in(i).dnz_chr_id := g_chr_id;
1141 l_scpv_tbl_in(i).seq_no := 1;
1142 l_scpv_tbl_in(i).year := src_osp_rec.year;
1143 l_scpv_tbl_in(i).month := src_osp_rec.month;
1144 l_scpv_tbl_in(i).week := src_osp_rec.week;
1145 l_scpv_tbl_in(i).week_day := src_osp_rec.week_day;
1146 l_scpv_tbl_in(i).day := src_osp_rec.day;
1147 End Loop;
1148 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1149 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osp.external_call.before',
1150 'oks_subscr_ptrns_pub.insert_row');
1151 END IF;
1152 OKS_SUBSCR_PTRNS_PUB.insert_row
1153 ( p_api_version => p_api_version,
1154 p_init_msg_list => p_init_msg_list,
1155 x_return_status => x_return_status,
1156 x_msg_count => x_msg_count,
1157 x_msg_data => x_msg_data,
1158 p_scpv_tbl => l_scpv_tbl_in,
1159 x_scpv_tbl => l_scpv_tbl_out
1160 );
1161 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1162 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.copy_osp.external_call.after',
1163 'oks_subscr_ptrns_pub.insert_row(x_return_status = '||x_return_status||')');
1164 END IF;
1165 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1166 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.copy_subscription.copy_osh.end',' ');
1167 END IF;
1168 End copy_osp;
1169 Begin
1170 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1171 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.copy_subscription.begin','p_source_cle_id = '||p_source_cle_id||
1172 ', p_target_cle_id = '||p_target_cle_id||' ,p_intent = '||p_intent);
1173 END IF;
1174 Open tgt_chr_cur;
1175 Fetch tgt_chr_cur Into g_chr_id;
1176 If tgt_chr_cur%NOTFOUND Then
1177 Close tgt_chr_cur;
1178 x_return_status := OKC_API.G_RET_STS_ERROR;
1179 OKC_API.set_message
1180 ( p_app_name => 'OKS',
1181 p_msg_name => 'OKS_SUB_INVAL_TGT',
1182 p_token1 => 'TARGET',
1183 p_token1_value => p_target_cle_id
1184 );
1185 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1186 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.copy_subscription.ERROR','Invalid Target Line');
1187 END IF;
1188 Raise gen_exit;
1189 End If;
1190 Close tgt_chr_cur;
1191 Open src_line_cur;
1192 Fetch src_line_cur Into l_dummy;
1193 If src_line_cur%NOTFOUND Then
1194 Close src_line_cur;
1195 x_return_status := OKC_API.G_RET_STS_ERROR;
1196 OKC_API.set_message
1197 ( p_app_name => 'OKS',
1198 p_msg_name => 'OKS_SUB_INVAL_SRC',
1199 p_token1 => 'SOURCE',
1200 p_token1_value => p_source_cle_id
1201 );
1202 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1203 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.copy_subscription.ERROR','Invalid Source Line');
1204 END IF;
1205 Raise gen_exit;
1206 End If;
1207 Close src_line_cur;
1208 copy_osh;
1209 If p_intent in ('COPY','RENEW') Then
1210 l_intent := Null;
1211 Else
1212 l_intent := p_intent;
1213 End If;
1214 If g_osh_id Is Not Null Then
1215 copy_osp;
1216 recreate_schedule
1217 ( p_api_version => p_api_version,
1218 p_init_msg_list => p_init_msg_list,
1219 x_return_status => x_return_status,
1220 x_msg_count => x_msg_count,
1221 x_msg_data => x_msg_data,
1222 p_cle_id => p_target_cle_id,
1223 p_intent => l_intent,
1224 x_quantity => l_dummy
1225 );
1226 Else -- No OSH Id => The item must be Non-Subscription Intangible
1227 -- PRICE THE NEW SUBSCRIPTION LINE
1228 If l_intent is Not Null Then
1229 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1230 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.copy_subscription.intannonsubprice',
1231 'intangible non-subscription item with pricing intent');
1232 END IF;
1233 l_price_details_in.line_id := p_target_cle_id;
1234 l_price_details_in.intent := l_intent;
1235 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1236 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.external_call.before',
1237 'oks_qp_int_pvt.compute_price(p_detail_rec.line_id = '||l_price_details_in.line_id||
1238 ', p_detail_rec.intent = '||l_price_details_in.intent||')');
1239 END IF;
1240 OKS_QP_INT_PVT.compute_price
1241 (
1242 p_api_version => p_api_version,
1243 p_init_msg_list => p_init_msg_list,
1244 x_return_status => x_return_status,
1245 x_msg_count => x_msg_count,
1246 x_msg_data => x_msg_data,
1247 p_detail_rec => l_price_details_in,
1248 x_price_details => x_price_details_out,
1249 x_modifier_details => x_mo_details,
1250 x_price_break_details => x_pb_details
1251 );
1252 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1253 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.copy_subscription.external_call.after',
1254 'oks_qp_int_pvt.compute_price(x_return_status = '||x_return_status||')');
1255 END IF;
1256 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
1257 Raise gen_exit;
1258 end if;
1259 End If;
1260 End If;
1261
1262 Exception
1263 When gen_exit Then
1264 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1265 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE_CURRENT||'.copy_subscription.EXCEPTION','gen_exit');
1266 END IF;
1267 When OTHERS Then
1268 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1269 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1270 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.create_default_schedule.UNEXPECTED',
1271 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
1272 END IF;
1273 OKC_API.set_message
1274 ( p_app_name => 'OKS',
1275 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
1276 p_token1 => 'ERROR_CODE',
1277 p_token1_value => sqlcode,
1278 p_token2 => 'ERROR_MESSAGE',
1279 p_token2_value => sqlerrm
1280 );
1281 End copy_subscription;
1282
1283 Procedure undo_subscription
1284 ( p_api_version IN NUMBER,
1285 p_init_msg_list IN VARCHAR2,
1286 x_return_status OUT NOCOPY VARCHAR2,
1287 x_msg_count OUT NOCOPY Number,
1288 x_msg_data OUT NOCOPY VARCHAR2,
1289 p_cle_id IN NUMBER
1290 ) IS
1291
1292 l_osh_id Number;
1293 l_ff_chan Varchar2(30);
1294
1295 Procedure delete_cii Is
1296 Cursor schv_cur Is
1297 Select id, instance_id, fulfillment_channel
1298 From oks_subscr_header_b
1299 Where cle_id = p_cle_id;
1300 /* No Item instance deletion for release 11.5.9 and 11.5.10
1301 l_instance_id Number;
1302 Cursor other_osh_inst Is
1303 Select Null
1304 From oks_subscr_header_b
1305 Where instance_id = l_instance_id
1306 And cle_id <> p_cle_id;
1307 l_dummy Number;
1308 */
1309 Begin
1310 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1311 For schv_rec in schv_cur
1312 Loop
1313 l_osh_id := schv_rec.id;
1314 l_ff_chan := schv_rec.fulfillment_channel;
1315 --l_instance_id := schv_rec.instance_id;
1316 Exit; -- There will always be only one record, if at all
1317 End Loop;
1318 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1319 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.undo_subscription.delete_cii.osh_details',
1320 'Osh Id = '||l_osh_id||' ,Fulfillment Channel = '||l_ff_chan);
1321 END IF;
1322 /* No Item instance deletion for release 11.5.9
1323 -- Uncomment when the procedure OKS_AUTH_UTIL_PUB.delete_cii_for_subscription is ready
1324 IF l_instance_id is Not Null THEN
1325 Open other_osh_inst;
1326 Fetch other_osh_inst Into l_dummy;
1327 -- If more subscription lines are referring to the same item instance,
1328 -- then don't delete the item instance;
1329 If other_osh_inst%Found Then
1330 Close other_osh_inst;
1331 Return;
1332 End If;
1333 Close other_osh_inst;
1334 -- Delete the item instance;
1335 OKS_AUTH_UTIL_PUB.delete_cii_for_subscription
1336 ( p_api_version => p_api_version,
1337 p_init_msg_list => p_init_msg_list,
1338 x_return_status => x_return_status,
1339 x_msg_count => x_msg_count,
1340 x_msg_data => x_msg_data,
1341 p_cle_id => p_cle_id
1342 );
1343 END IF;
1344 */
1345 End delete_cii;
1346
1347 Procedure delete_osh Is
1348 l_schv_tbl OKS_SUBSCR_HDR_PUB.schv_tbl_type;
1349 Begin
1350 l_schv_tbl(1).id := l_osh_id;
1351 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1352 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osh.external_call.before',
1353 'oks_subscr_hdr_pub.delete_row(p_schv_tbl(1).id = '||l_schv_tbl(1).id||')');
1354 END IF;
1355 OKS_SUBSCR_HDR_PUB.delete_row
1356 ( p_api_version => p_api_version,
1357 p_init_msg_list => p_init_msg_list,
1358 x_return_status => x_return_status,
1359 x_msg_count => x_msg_count,
1360 x_msg_data => x_msg_data,
1361 p_schv_tbl => l_schv_tbl
1362 );
1363 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1364 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osh.external_call.after',
1365 'oks_subscr_hdr_pub.delete_row(x_return_status = '||x_return_status||')');
1366 END IF;
1367 End delete_osh;
1368
1369 Procedure delete_osp Is
1370 Cursor scpv_cur Is
1371 Select id
1372 From oks_subscr_patterns
1373 Where dnz_cle_id = p_cle_id;
1374 l_scpv_tbl OKS_SUBSCR_PTRNS_PUB.scpv_tbl_type;
1375 i Number := 0;
1376 Begin
1377 For scpv_rec In scpv_cur
1378 Loop
1379 i := i + 1;
1380 l_scpv_tbl(i).id := scpv_rec.id;
1381 End Loop;
1382 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1383 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osp.external_call.before',
1384 'oks_subscr_ptrns_pub.delete_row(p_scpv_tbl.COUNT = '||i||')');
1385 END IF;
1386 OKS_SUBSCR_PTRNS_PUB.delete_row
1387 ( p_api_version => p_api_version,
1388 p_init_msg_list => p_init_msg_list,
1389 x_return_status => x_return_status,
1390 x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data,
1392 p_scpv_tbl => l_scpv_tbl
1393 );
1394 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1395 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osp.external_call.after',
1396 'oks_subscr_ptrns_pub.delete_row(x_return_status = '||x_return_status||')');
1397 END IF;
1398 End delete_osp;
1399
1400 Procedure delete_ose Is
1401 Cursor scev_cur Is
1402 Select id
1403 From oks_subscr_elements
1404 Where dnz_cle_id = p_cle_id;
1405 l_scev_tbl OKS_SUBSCR_ELEMS_PUB.scev_tbl_type;
1406 i Number := 0;
1407 Begin
1408 For scev_rec In scev_cur
1409 Loop
1410 i := i + 1;
1411 l_scev_tbl(i).id := scev_rec.id;
1412 End Loop;
1413 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1414 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_ose.external_call.before',
1415 'oks_subscr_elems_pub.delete_row(p_scev_tbl.COUNT = '||i||')');
1416 END IF;
1417 OKS_SUBSCR_ELEMS_PUB.delete_row
1418 ( p_api_version => p_api_version,
1419 p_init_msg_list => p_init_msg_list,
1420 x_return_status => x_return_status,
1421 x_msg_count => x_msg_count,
1422 x_msg_data => x_msg_data,
1423 p_scev_tbl => l_scev_tbl
1424 );
1425 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1426 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_ose.external_call.after',
1427 'oks_subscr_elems_pub.delete_row(x_return_status = '||x_return_status||')');
1428 END IF;
1429 End delete_ose;
1430 Begin
1431 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1432 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.undo_subscription.begin','p_cle_id = '||p_cle_id);
1433 END IF;
1434 delete_cii; -- (Delete the item instance if one exists and) Get the OSH.id and fulfillment channel
1435 IF l_osh_id Is Null Or NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
1436 return;
1437 END IF;
1438 IF l_ff_chan <> 'NONE' THEN -- If the item is tangible then ...
1439 delete_ose; -- delete the subscription elements
1440 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
1441 return;
1442 End If;
1443 delete_osp; -- delete the subscription patterns
1444 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
1445 return;
1446 End If;
1447 END IF;
1448 delete_osh; -- delete the subscription header (tangible or intangible)
1449 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1450 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.undo_subscription.end',' ');
1451 END IF;
1452 Exception
1453 When OTHERS Then
1454 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1455 OKC_API.set_message
1456 ( p_app_name => 'OKS',
1457 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
1458 p_token1 => 'ERROR_CODE',
1459 p_token1_value => sqlcode,
1460 p_token2 => 'ERROR_MESSAGE',
1461 p_token2_value => sqlerrm
1462 );
1463 End undo_subscription;
1464
1465 Procedure validate_pattern
1466 ( p_api_version IN NUMBER,
1467 p_init_msg_list IN VARCHAR2,
1468 x_return_status OUT NOCOPY VARCHAR2,
1469 x_msg_count OUT NOCOPY Number,
1470 x_msg_data OUT NOCOPY VARCHAR2,
1471 p_instring IN VARCHAR2,
1472 p_lowval IN NUMBER,
1473 p_highval IN NUMBER,
1474 x_outstring OUT NOCOPY VARCHAR2,
1475 x_outtab OUT NOCOPY rangetab) IS
1476 l_tab rangetab;
1477 i NUMBER;
1478 len NUMBER;
1479 l_index NUMBER;
1480 commaloc NUMBER;
1481 hyphenloc NUMBER;
1482 lowval NUMBER;
1483 highval NUMBER;
1484 l_string VARCHAR2(2000);
1485 l_modifstr VARCHAR2(2000);
1486 range1 VARCHAR2(2000);
1487 gen_exit EXCEPTION;
1488 Procedure MERGER IS
1489 i NUMBER;
1490 j NUMBER;
1491 rlow NUMBER;
1492 rhigh NUMBER;
1493 Begin
1494 i := l_tab.FIRST;
1495 Loop
1496 Exit When i=l_tab.LAST;
1497 rlow := l_tab(i).low;
1498 rhigh:= l_tab(i).high;
1499 j := l_tab.NEXT(i);
1500 loop
1501 If l_tab(j).high <= rhigh Then
1502 l_tab.delete(j);
1503 Elsif l_tab(j).low <= rhigh+1 Then
1504 l_tab(i).high := l_tab(j).high;
1505 rhigh := l_tab(j).high;
1506 l_tab.delete(j);
1507 End If;
1508 Exit When j >= l_tab.LAST;
1509 j := l_tab.NEXT(j);
1510 end loop;
1511 Exit When i = l_tab.LAST;
1512 i := l_tab.NEXT(i);
1513 End Loop;
1514 i := l_tab.FIRST;
1515 l_modifstr := NULL;
1516 Loop
1517 If l_modifstr IS NOT NULL Then
1518 l_modifstr := l_modifstr||',';
1519 End If;
1520 If l_tab(i).low = l_tab(i).high Then
1521 l_modifstr := l_modifstr||l_tab(i).low;
1522 Else
1523 l_modifstr := l_modifstr||l_tab(i).low||'-'||l_tab(i).high;
1524 End If;
1525 Exit When i = l_tab.LAST;
1526 i := l_tab.NEXT(i);
1527 End Loop;
1528 If l_modifstr = p_lowval||'-'||p_highval Then
1529 l_modifstr := '*';
1530 End If;
1531 End MERGER;
1532 Begin
1533 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1534 OKC_API.init_msg_list(p_init_msg_list);
1535 l_string := p_instring;
1536 l_string := translate(l_string,'0 ','0');
1537 IF l_string = '*' THEN
1538 x_outtab(1).low := p_lowval;
1539 x_outtab(1).high := p_highval;
1540 l_modifstr := '*';
1541 ELSIF l_string IS NOT NULL THEN
1542 If translate(l_string,' 0123456789,-',' ') IS NOT NULL Then
1543 x_return_status := OKC_API.G_RET_STS_ERROR;
1544 OKC_API.set_message
1545 ( p_app_name => 'OKS',
1546 p_msg_name => 'OKS_SUB_INVAL_CHRS',
1547 p_token1 => 'CHRS',
1548 p_token1_value => translate(l_string,' 0123456789,-',' ')
1549 );
1550 Raise gen_exit;
1551 End If;
1552 If instr(l_string,',,') <> 0 or
1553 instr(l_string,'--') <> 0 or
1554 instr(l_string,',-') <> 0 or
1555 instr(l_string,'-,') <> 0 Then
1556 x_return_status := OKC_API.G_RET_STS_ERROR;
1557 OKC_API.set_message
1558 ( p_app_name => 'OKS',
1559 p_msg_name => 'OKS_SUB_CONT_DELIM'
1560 );
1561 Raise gen_exit;
1562 End If;
1563 If substr(l_string,1,1) in (',','-') or
1564 substr(l_string,length(l_string),1) in (',','-') then
1565 x_return_status := OKC_API.G_RET_STS_ERROR;
1566 OKC_API.set_message
1567 ( p_app_name => 'OKS',
1568 p_msg_name => 'OKS_SUB_SE_DELIM'
1569 );
1570 Raise gen_exit;
1571 End If;
1572 LOOP
1573 EXIT When l_string IS NULL;
1574 commaloc := instr(l_string,',');
1575 If commaloc = 0 Then
1576 range1 := l_string;
1577 l_string := NULL;
1578 Else
1579 range1 := substr(l_string,1,commaloc-1);
1580 l_string := substr(l_string,commaloc+1);
1581 End If;
1582 If instr(range1,'-',1,2) <> 0 Then
1583 x_return_status := OKC_API.G_RET_STS_ERROR;
1584 OKC_API.set_message
1585 ( p_app_name => 'OKS',
1586 p_msg_name => 'OKS_SUB_INVAL_RANGE',
1587 p_token1 => 'RANGE',
1588 p_token1_value => range1
1589 );
1590 Raise gen_exit;
1591 End If;
1592 hyphenloc := instr(range1,'-');
1593 If hyphenloc = 0 Then
1594 lowval := range1;
1595 highval := range1;
1596 Else
1597 lowval := substr(range1,1,hyphenloc-1);
1598 highval := substr(range1,hyphenloc+1);
1599 End If;
1600 If lowval < p_lowval or highval < p_lowval or
1601 lowval > p_highval or highval > p_highval Then
1602 x_return_status := OKC_API.G_RET_STS_ERROR;
1603 OKC_API.set_message
1604 ( p_app_name => 'OKS',
1605 p_msg_name => 'OKS_SUB_RANGE',
1606 p_token1 => 'LOW',
1607 p_token1_value => p_lowval,
1608 p_token2 => 'HIGH',
1609 p_token2_value => p_highval
1610 );
1611 Raise gen_exit;
1612 End If;
1613 If lowval > highval Then
1614 x_return_status := OKC_API.G_RET_STS_ERROR;
1615 OKC_API.set_message
1616 ( p_app_name => 'OKS',
1617 p_msg_name => 'OKS_SUB_INVAL_RANGE',
1618 p_token1 => 'RANGE',
1619 p_token1_value => range1
1620 );
1621 Raise gen_exit;
1622 End If;
1623 If NOT l_tab.EXISTS(lowval) Then
1624 l_tab(lowval).low := lowval;
1625 l_tab(lowval).high:= highval;
1626 Elsif l_tab(lowval).high < highval Then
1627 l_tab(lowval).high := highval;
1628 End If;
1629 END LOOP;
1630 MERGER;
1631 i := 1;
1632 l_index := l_tab.FIRST;
1633 LOOP
1634 x_outtab(i) := l_tab(l_index);
1635 EXIT WHEN l_index=l_tab.LAST;
1636 l_index := l_tab.NEXT(l_index);
1637 i := i+1;
1638 END LOOP;
1639 END IF;
1640 x_outstring := l_modifstr;
1641 Exception
1642 When gen_exit Then
1643 Null;
1644 When OTHERS Then
1645 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1646 OKC_API.set_message
1647 ( p_app_name => 'OKS',
1648 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
1649 p_token1 => 'ERROR_CODE',
1650 p_token1_value => sqlcode,
1651 p_token2 => 'ERROR_MESSAGE',
1652 p_token2_value => sqlerrm
1653 );
1654 End validate_pattern;
1655
1656 Procedure get_subs_qty
1657 ( p_cle_id IN NUMBER,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_quantity OUT NOCOPY NUMBER,
1660 x_uom_code OUT NOCOPY VARCHAR2
1661 ) IS
1662 Cursor k_item_cur Is
1663 Select number_of_items, uom_code
1664 From okc_k_items
1665 Where cle_id = p_cle_id;
1666 Cursor subs_hdr Is
1667 Select item_type, frequency
1668 From oks_subscr_header_b
1669 Where cle_id = p_cle_id;
1670 Cursor subs_qty Is
1671 Select sum(quantity)
1672 From oks_subscr_elements
1673 Where dnz_cle_id = p_cle_id;
1674 Cursor k_line_cur Is
1675 Select start_date, end_date
1676 From okc_k_lines_b
1677 Where id = p_cle_id;
1678 CURSOR l_get_hdrid_csr IS
1679 SELECT dnz_chr_id
1680 FROM okc_k_lines_b
1681 WHERE id = p_cle_id;
1682 l_quantity Number;
1683 l_uom_code Varchar2(10);
1684 l_cal_uom Varchar2(10);
1685 l_frequency Varchar2(3);
1686 l_item_type Varchar2(10);
1687 l_act_start Date;
1688 l_act_end Date;
1689 l_start_date Date;
1690 l_end_date Date;
1691 l_multiplier Number;
1692 l_pricing_method Varchar2(30);
1693 --New variables for partial periods
1694 l_period_type VARCHAR2(30);
1695 l_period_start VARCHAR2(30);
1696 l_price_uom VARCHAR2(30);
1697 l_chr_id NUMBER;
1698 INVALID_HDR_ID_EXCEPTION EXCEPTION;
1699 EXC_ERROR EXCEPTION;
1700 Begin
1701 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1702 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.get_subs_qty.begin','p_cle_id = '||p_cle_id);
1703 END IF;
1704 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1705 l_pricing_method:=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
1706 Open k_item_cur;
1707 Fetch k_item_cur Into l_quantity, l_uom_code;
1708 Close k_item_cur;
1709 Open subs_hdr;
1710 Fetch subs_hdr Into l_item_type, l_frequency;
1711 Close subs_hdr;
1712 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1713 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.get_subs_qty.line_details',
1714 'Qty per period = '||l_quantity||' ,UOM = '||l_uom_code
1715 ||', Item Type = '||l_item_type||', Frequency = '||l_frequency
1716 ||', Intangible Pricing Method = '||l_pricing_method);
1717 END IF;
1718 If l_item_type In ('ST','NT') Then
1719 Open subs_qty;
1720 Fetch subs_qty Into l_quantity;
1721 Close subs_qty;
1722 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1723 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.get_subs_qty.tanqty','Tangible Qty = '||l_quantity);
1724 END IF;
1725 Elsif l_item_type = 'SI' Then
1726 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1727 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.get_subs_qty.intan','intangible item');
1728 END IF;
1729 Open k_line_cur;
1730 Fetch k_line_cur Into l_act_start, l_act_end;
1731 Close k_line_cur;
1732 if l_pricing_method = 'EFFECTIVITY' then
1733 l_start_date := l_act_start;
1734 l_end_date := l_act_end;
1735 else
1736 stretch_effectivity
1737 (l_act_start, l_act_end, l_frequency, l_start_date, l_end_date);
1738 end if;
1739 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1740 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.get_subs_qty.stretch_eff',
1741 'New Start Date = '||to_char(l_start_date,'DD-MON-YYYY')
1742 ||', New End Date = '||to_char(l_end_date,'DD-MON-YYYY'));
1743 END IF;
1744 l_cal_uom := map_freq_uom(l_frequency);
1745 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1746 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.get_subs_qty.freq_uom','Frequency UOM = '||l_cal_uom);
1747 END IF;
1748 if l_cal_uom is Null then
1749 x_return_status := OKC_API.G_RET_STS_ERROR;
1750 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1751 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.get_subs_qty.ERROR','Invalid UOM Code');
1752 END IF;
1753 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
1754 p_msg_name => 'OKS_INVD_UOM_CODE',
1755 p_token1 => 'OKS_API_NAME',
1756 p_token1_value => 'OKS_SUBSCRIPTION_PVT.get_subs_qty',
1757 p_token2 => 'UOM_CODE',
1758 p_token2_value => p_cle_id);
1759 else
1760 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1761 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.get_subs_qty.external_call.before',
1762 'oks_time_measures_pub.get_quantity');
1763 END IF;
1764 -- Begin new logic for Partial periods
1765 OPEN l_get_hdrid_csr;
1766 FETCH l_get_hdrid_csr INTO l_chr_id;
1767 CLOSE l_get_hdrid_csr;
1768 IF l_chr_id IS NOT NULL
1769 THEN
1770 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1771 p_org_id => NULL,
1772 x_period_type => l_period_type,
1773 x_period_start => l_period_start,
1774 x_price_uom => l_price_uom,
1775 x_return_status => x_return_status);
1776 IF x_return_status <> 'S'
1777 THEN
1778 RAISE EXC_ERROR;
1779 END IF;
1780 ELSE
1781 RAISE INVALID_HDR_ID_EXCEPTION;
1782 END IF;
1783 IF l_pricing_method = 'EFFECTIVITY'
1784 -- Only effectivity based, then only partial period logic
1785 THEN
1786 --added by mchoudha for bug#4729856
1787 IF l_period_start is not null then
1788 l_period_start := 'SERVICE';
1789 END IF;
1790 l_multiplier := OKS_TIME_MEASURES_PUB.get_quantity
1791 (p_start_date => l_start_date,
1792 p_end_date => l_end_date,
1793 p_source_uom => l_cal_uom,
1794 p_period_type => l_period_type, --new param
1795 p_period_start => l_period_start); --new param
1796
1797 ELSE
1798 l_multiplier := OKS_TIME_MEASURES_PUB.get_quantity
1799 (p_start_date => l_start_date,
1800 p_end_date => l_end_date,
1801 p_source_uom => l_cal_uom);
1802 END IF;
1803 --End new logic for partial periods
1804 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1805 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.get_subs_qty.external_call.after',
1806 'oks_time_measures_pub.get_quantity(return = '||l_multiplier||')');
1807 END IF;
1808 l_quantity := l_quantity * l_multiplier;
1809 end if;
1810 End If;
1811 x_quantity := l_quantity;
1812 x_uom_code := l_uom_code;
1813 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1814 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.get_subs_qty.end',
1815 'x_quantity = '||x_quantity||' ,x_uom_code = '||x_uom_code);
1816 END IF;
1817 Exception
1818 WHEN EXC_ERROR THEN
1819 NULL;
1820 WHEN INVALID_HDR_ID_EXCEPTION THEN
1821 OKC_API.set_message(
1822 p_app_name => G_APP_NAME,
1823 p_msg_name => G_INVALID_VALUE,
1824 p_token1 => G_COL_NAME_TOKEN,
1825 p_token1_value => 'Header ID');
1826 When Others Then
1827 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1828 End get_subs_qty;
1829
1830 Procedure stretch_effectivity
1831 ( p_start_date IN DATE,
1832 p_end_date IN DATE,
1833 p_frequency IN VARCHAR2, -- 'Y','M','W','D' Only
1834 x_new_start_dt OUT NOCOPY DATE,
1835 x_new_end_dt OUT NOCOPY DATE
1836 ) IS
1837 Begin
1838 IF p_frequency = 'Y' THEN
1839 x_new_start_dt := to_date(to_char(p_start_date,'YYYY')||'0101','YYYYMMDD');
1840 x_new_end_dt := to_date(to_char(p_end_date,'YYYY')||'1231','YYYYMMDD');
1841 ELSIF p_frequency = 'M' THEN
1842 x_new_start_dt := to_date(to_char(p_start_date,'YYYYMM')||'01','YYYYMMDD');
1843 x_new_end_dt := add_months(to_date(to_char(p_end_date,'YYYYMM')||'01','YYYYMMDD'),1) - 1;
1844 ELSIF p_frequency = 'W' THEN
1845 x_new_start_dt := p_start_date - to_number(to_char(p_start_date,'D')) + 1;
1846 x_new_end_dt := p_end_date - to_number(to_char(p_end_date,'D')) + 7;
1847 ELSE
1848 x_new_start_dt := p_start_date;
1849 x_new_end_dt := p_end_date;
1850 END IF;
1851 End stretch_effectivity;
1852
1853 Function subs_termn_amount
1854 ( p_cle_id IN NUMBER,
1855 p_termn_date IN DATE
1856 ) Return NUMBER IS
1857 Cursor k_line_cur Is
1858 Select start_date, end_date, price_negotiated
1859 From okc_k_lines_b
1860 Where id = p_cle_id
1861 And lse_id = 46;
1862 Cursor osh_cur Is
1863 Select item_type, frequency
1864 From oks_subscr_header_b
1865 Where cle_id = p_cle_id;
1866 Cursor trmn_amt_cur Is
1867 Select sum(amount)
1868 From oks_subscr_elements
1869 Where dnz_cle_id = p_cle_id
1870 And (start_date < p_termn_date Or order_header_id Is Not Null);
1871 CURSOR l_get_hdrid_csr IS
1872 SELECT dnz_chr_id
1873 FROM okc_k_lines_b
1874 WHERE id = p_cle_id;
1875 l_act_start Date;
1876 l_act_end Date;
1877 l_start_date Date;
1878 l_end_date Date;
1879 l_tmn_date Date;
1880 l_orig_price Number;
1881 l_divisor Number;
1882 l_multiplier Number;
1883 l_item_type Varchar2(10);
1884 l_frequency Varchar2(10);
1885 l_uom Varchar2(10);
1886 l_trmn_amount Number;
1887 l_pricing_method Varchar2(30);
1888 --New variables for partial periods
1889 l_period_type OKS_K_HEADERS_B.PERIOD_TYPE%TYPE;
1890 l_period_start OKS_K_HEADERS_B.PERIOD_START%TYPE;
1891 l_price_uom OKS_K_HEADERS_B.PRICE_UOM%TYPE;
1892 l_chr_id NUMBER;
1893 l_return_status VARCHAR2(1);
1894 INVALID_HDR_ID_EXCEPTION EXCEPTION;
1895 EXC_ERROR EXCEPTION;
1896 Begin
1897
1898
1899 -------------------------------------------------------------------------
1900 -- Begin partial period computation logic
1901 -- Developer Mani Choudhary
1902 -- Date 06-JUN-2005
1903 -- Fetch the period start and period type stored at the contract level.
1904 -------------------------------------------------------------------------
1905 OPEN l_get_hdrid_csr;
1906 FETCH l_get_hdrid_csr INTO l_chr_id;
1907 CLOSE l_get_hdrid_csr;
1908 IF l_chr_id IS NOT NULL
1909 THEN
1910 OKS_RENEW_UTIL_PUB.get_period_defaults(p_hdr_id => l_chr_id,
1911 p_org_id => NULL,
1912 x_period_type => l_period_type,
1913 x_period_start => l_period_start,
1914 x_price_uom => l_price_uom,
1915 x_return_status => l_return_status);
1916 IF l_return_status <> 'S' THEN
1917 RAISE EXC_ERROR;
1918 END IF;
1919 ELSE
1920 RAISE INVALID_HDR_ID_EXCEPTION;
1921 END IF;
1922 -------------------------------------------------------------------------
1923 -- End partial period computation logic
1924 -------------------------------------------------------------------------
1925
1926
1927 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1928 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.subs_termn_amount.begin',
1929 'p_cle_id = '||p_cle_id||' ,p_termn_date = '||to_char(p_termn_date,'DD-MON-YYYY')
1930 ||', Intangible Pricing Method = '||l_pricing_method);
1931 END IF;
1932 l_pricing_method :=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
1933 Open k_line_cur;
1934 Fetch k_line_cur Into l_act_start, l_act_end, l_orig_price;
1935 If k_line_cur%NotFound Then
1936 Close k_line_cur;
1937 Return Null;
1938 End If;
1939 If p_termn_date = l_act_start Then
1940 Close k_line_cur;
1941 Return 0;
1942 End If;
1943 Close k_line_cur;
1944 Open osh_cur;
1945 Fetch osh_cur Into l_item_type, l_frequency;
1946 If osh_cur%NotFound Then
1947 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1948 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.NI',
1949 'intangible non-subscription, no refund');
1950 END IF;
1951 l_trmn_amount := l_orig_price;
1952 End If;
1953 Close osh_cur;
1954 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1955 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.details',
1956 'Start Date = '||to_char(l_act_start,'DD-MON-YYYY')
1957 ||', End Date = '||to_char(l_act_end,'DD-MON-YYYY')
1958 ||', Orig. Price = '||l_orig_price
1959 ||', Item Type = '||l_item_type||', Frequency = '||l_frequency);
1960 END IF;
1961 If l_item_type = 'SI' Then
1962 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1963 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.SI','intangible subscription');
1964 END IF;
1965 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1966 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.calcvals',
1967 'Divisor = '||l_divisor||', Multiplier = '||l_multiplier);
1968 END IF;
1969 if l_pricing_method = 'EFFECTIVITY' then
1970 l_start_date := l_act_start;
1971 l_end_date := l_act_end;
1972 l_tmn_date := p_termn_date - 1;
1973 else
1974 stretch_effectivity(l_act_start, l_act_end, l_frequency, l_start_date, l_end_date);
1975 stretch_effectivity(l_act_start, p_termn_date - 1, l_frequency, l_start_date, l_tmn_date);
1976 end if;
1977 if l_end_date = l_tmn_date then
1978 l_trmn_amount := l_orig_price;
1979 else
1980
1981 -------------------------------------------------------------------------
1982 -- Begin partial period computation logic
1983 -- Developer Mani Choudhary
1984 -- Date 06-JUN-2005
1985 -- if the profile OKS: Intangible Subscription Pricing Method is set to
1986 -- 'EFFECTIVITY' then follow the partial period method
1987 -------------------------------------------------------------------------
1988 IF l_pricing_method = 'EFFECTIVITY' AND l_period_start is NOT NULL THEN
1989 -- Only effectivity based, then only partial period logic
1990
1991 l_period_start := 'SERVICE';
1992
1993 l_uom := map_freq_uom(l_frequency);
1994
1995 --mchoudha Fix for bug#4729993
1996 --Calculate the price from term date to end date
1997 l_divisor := OKS_TIME_MEASURES_PUB.get_quantity
1998 (p_start_date => l_start_date,
1999 p_end_date => l_end_date,
2000 p_source_uom => l_uom,
2001 p_period_type => l_period_type, --new param
2002 p_period_start => l_period_start); --new param
2003
2004 l_multiplier := OKS_TIME_MEASURES_PUB.get_quantity
2005 (p_start_date => l_tmn_date+1,
2006 p_end_date => l_end_date,
2007 p_source_uom => l_uom,
2008 p_period_type => l_period_type, --new param
2009 p_period_start => l_period_start); --new param
2010
2011 l_trmn_amount := l_orig_price - (l_orig_price * l_multiplier / l_divisor);
2012
2013 ELSE
2014 --existing logic
2015 l_uom := map_freq_uom(l_frequency);
2016 l_divisor := OKS_TIME_MEASURES_PUB.get_quantity
2017 (p_start_date => l_start_date,
2018 p_end_date => l_end_date,
2019 p_source_uom => l_uom);
2020
2021 l_multiplier := OKS_TIME_MEASURES_PUB.get_quantity
2022 (p_start_date => l_start_date,
2023 p_end_date => l_tmn_date ,
2024 p_source_uom => l_uom);
2025
2026
2027 l_trmn_amount := l_orig_price * l_multiplier / l_divisor;
2028
2029
2030
2031 END IF;
2032 -------------------------------------------------------------------------
2033 -- End partial period computation logic
2034 -------------------------------------------------------------------------
2035
2036 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2037 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.calcvals',
2038 'Divisor = '||l_divisor||', Multiplier = '||l_multiplier);
2039 END IF;
2040
2041 end if;
2042 Else
2043 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2044 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.subs_termn_amount.tang',
2045 'tangible item, fetch amount from elements');
2046 END IF;
2047 Open trmn_amt_cur;
2048 Fetch trmn_amt_cur Into l_trmn_amount;
2049 If trmn_amt_cur%Found Then
2050 l_trmn_amount := NVL(l_trmn_amount,0);
2051 End If;
2052 Close trmn_amt_cur;
2053 End If;
2054 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2055 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.recreate_schedule.end','return = '||l_trmn_amount);
2056 END IF;
2057 Return l_trmn_amount;
2058 Exception
2059 WHEN EXC_ERROR THEN
2060 Return NULL;
2061 WHEN INVALID_HDR_ID_EXCEPTION THEN
2062 OKC_API.set_message(
2063 p_app_name => G_APP_NAME,
2064 p_msg_name => G_INVALID_VALUE,
2065 p_token1 => G_COL_NAME_TOKEN,
2066 p_token1_value => 'Header ID');
2067 Return NULL;
2068 When Others Then
2069 Return Null;
2070 End subs_termn_amount;
2071
2072 Function is_subs_tangible
2073 ( p_cle_id IN NUMBER
2074 ) Return BOOLEAN IS
2075 Cursor subs_hdr Is
2076 Select item_type From oks_subscr_header_b Where cle_id = p_cle_id;
2077 l_type Varchar2(240);
2078 l_tangible Boolean := FALSE;
2079 Begin
2080 Open subs_hdr;
2081 Fetch subs_hdr into l_type;
2082 IF l_type like '%T' THEN
2083 l_tangible := TRUE;
2084 END IF;
2085 Close subs_hdr;
2086 Return l_tangible;
2087 Exception
2088 When others then
2089 Return l_tangible;
2090 End is_subs_tangible;
2091
2092 Function map_freq_uom
2093 ( p_frequency IN VARCHAR2
2094 ) Return VARCHAR2 IS
2095 Cursor uom_cur(p_tce In Varchar2, p_qty Number) Is
2096 Select uom_code
2097 From okc_time_code_units_v
2098 Where tce_code = p_tce
2099 And quantity = p_qty
2100 And active_flag = 'Y';
2101 l_tce_code Varchar2(10);
2102 l_quantity Number;
2103 l_uom_code Varchar2(10);
2104 Begin
2105 l_quantity := 1;
2106 IF p_frequency = 'Y' THEN
2107 l_tce_code := 'YEAR';
2108 ELSIF p_frequency = 'M' THEN
2109 l_tce_code := 'MONTH';
2110 ELSIF p_frequency = 'W' THEN
2111 l_tce_code := 'DAY';
2112 l_quantity := 7;
2113 ELSIF p_frequency = 'D' THEN
2114 l_tce_code := 'DAY';
2115 END IF;
2116 Open uom_cur(l_tce_code,l_quantity);
2117 Fetch uom_cur Into l_uom_code;
2118 Close uom_cur;
2119 Return l_uom_code;
2120 Exception
2121 When Others Then
2122 Return Null;
2123 End map_freq_uom;
2124
2125 Procedure db_commit Is
2126 Begin
2127 Commit;
2128 End db_commit;
2129
2130 END OKS_SUBSCRIPTION_PVT;
2131