[Home] [Help]
PACKAGE BODY: APPS.OKC_PHI_PVT
Source
1 PACKAGE BODY OKC_PHI_PVT AS
2 /* $Header: OKCRPHIB.pls 120.0 2005/05/25 19:47:34 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
7 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
8 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
9 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_PHI_PVT';
10 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
11
12 TYPE qp_relship_rec_typ IS RECORD
13 (
14 id okc_k_lines_b.id%TYPE, --Cle id, or line break id
15 object_version_number okc_k_lines_b.object_version_number%type,
16 line_type VARCHAR2(30),-- PRICE_HOLD,PH_LINE,PH_LINE_BREAK
17 modifier_tbl_index number
18 );
19
20
21
22 TYPE l_qp_ph_relship_tbl_type IS TABLE OF qp_relship_rec_typ INDEX BY BINARY_INTEGER;
23
24 l_qp_ph_relship_tbl l_qp_ph_relship_tbl_type ; -- table to keep relationship between modifier heaer/line to Contract line
25
26 l_clev_tbl okc_cle_pvt.clev_tbl_type ;
27 lx_clev_tbl okc_cle_pvt.clev_tbl_type ;
28 l_ph_line_breaks_tbl okc_phl_pvt.okc_ph_line_breaks_v_tbl_type;
29 lx_ph_line_breaks_tbl okc_phl_pvt.okc_ph_line_breaks_v_tbl_type;
30 l_call_qp_api boolean := false;
31 l_cust_found boolean := false;
32
33 --------------------------------------------------------------------------------------
34 --------------------------------------------------------------------------------------
35 /*
36 -------------------------------------------------------------------------------------------------------------------------
37 --Procedure : Process_price_hold
38 -- Input Parameter : p_chr_id Contract Id of contract which has price hold on it.
39 -- P_opreation_code Possible value UPDATE and TERMINATE
40 --
41 -- IF this API is called for a contract for the first time with operation UPDATE THEN it creates a Modifier in QP.
42 --
43 -- IF this API is called for a contract for the another time with operation UPDATE THEN it updates a Modifier .
44 -- in QP and creates new Modifier line IF new liens have been added to contract.
45 --
46 -- IF this API is called for a contract with operation TERMINATE THEN it de-activates a Modifier in QP.
47
48 -- This API will be called whenever a contract/contract line is activated or Terminated or cancelled.
49 ---------------------------------------------------------------------------------------------------------------------------------
50 */
51
52 PROCEDURE process_price_hold(p_api_version IN NUMBER
53 ,p_init_msg_list IN VARCHAR2
54 ,p_chr_id IN OKC_K_HEADERS_V.id%TYPE
55 ,p_operation_code IN VARCHAR2
56 ,p_termination_date IN DATE
57 ,p_unconditional_call IN varchar2
58 ,x_return_status OUT NOCOPY VARCHAR2
59 ,x_msg_count OUT NOCOPY NUMBER
60 ,x_msg_data OUT NOCOPY VARCHAR2
61 ) IS
62
63 -- Get informationm from Contract Header
64
65 Cursor c_k_header(b_chr_id Number) IS
66 SELECT chr.id ID,
67 chr.contract_number CONTRACT_NUMBER,
68 chr.short_description short_description,
69 chr.Currency_code currency_code,
70 chr.Contract_number_modifier contract_number_modifier,
71 chr.start_date start_date,
72 chr.end_date end_date,
73 chr.authoring_org_id authoring_org_id,
74 chr.inv_organization_id inv_organization_id
75 FROM okc_k_headers_V chr
76 WHERE chr.id=b_chr_id
77 and chr.application_id in (510,871)
78 AND chr.buy_or_sell='S'
79 AND chr.issue_or_receive='I'
80 AND chr.template_yn='N'
81 AND chr.deleted_yn='N';
82
83 -- Get informationm from price hold header( means from Price Hold top line)
84
85 Cursor c_ph_header(b_chr_id Number) IS
86 SELECT id ,
87 object_version_number,
88 ph_min_qty, -- Minimum Order Quantity
89 ph_min_amt, -- Minimum Order Amount
90 ph_qp_reference_id,
91 ph_enforce_price_list_yn,
92 decode(p_unconditional_call,'Y','N',ph_integrated_with_qp) ph_integrated_with_qp,
93 start_date,
94 end_date,
95 price_list_id,
96 date_terminated
97 FROM okc_k_lines_v cle
98 WHERE cle.dnz_chr_id=b_chr_id
99 AND cle.cle_id is null
100 AND cle.lse_id=61 -- Price Hold Line style.
101 AND nvl(cle.end_date,sysdate+1) > Sysdate
102 AND rownum=1;
103
104 -- Get price hold line information
105
106 Cursor c_ph_lines(b_cle_id Number) IS
107 SELECT cle.id id,
108 cle.object_version_number object_version_number,
109 cle.line_number line_number,
110 cle.lse_id lse_id,
111 ph_pricing_type,
112 ph_price_break_basis,
113 ph_min_qty, -- Minimum Line Quantity
114 ph_min_amt, -- Minimum Line Amount
115 ph_value,
116 ph_qp_reference_id,
117 ph_integrated_with_qp,
118 start_date,
119 end_date,
120 jtot_object1_code,
121 object1_id1,
122 object1_id2,
123 uom_code
124 FROM okc_k_lines_v cle,
125 okc_k_items_v cim
126 WHERE cle.cle_id=b_cle_id
127 AND cle.id=cim.cle_id
128 ORDER BY line_number;
129
130 -- Get price hold line break information
131
132 Cursor c_ph_line_break(b_ph_line_id Number) IS
133 SELECT id ,
134 object_version_number,
135 pricing_type,
136 value_from,
137 value_to,
138 value,
139 qp_reference_id,
140 integrated_with_qp
141 FROM okc_ph_line_breaks_v
142 WHERE cle_id=b_ph_line_id
143 AND nvl(integrated_with_qp,'N')='N'
144 order by id;
145
146 -- Header level qualifier info for a modifier
147 Cursor c_ph_header_qual(b_list_header_id number) IS
148 SELECT qualifier_id
149 FROM qp_qualifiers_v
150 WHERE list_header_id=b_list_header_id
151 AND list_line_id=-1;
152
153 -- Get Pricing attribute info of a modifier
154
155 CURSOR c_ph_get_pattr_id(b_list_line_id Number,b_list_header_id Number) IS
156 SELECT pricing_attribute_id
157 FROM qp_pricing_attributes
158 WHERE LIST_LINE_ID=b_list_line_id
159 AND LIST_HEADER_ID=b_list_header_id;
160
161 -- Get Price Hold rule info
162
163 CURSOR c_ph_rules(b_chr_id number) IS
164 SELECT
165 rgp.chr_id
166 ,rgp.cle_id
167 ,rul.object1_id1
168 ,rul.object1_id2
169 ,rul.jtot_object1_code
170 ,rul.object2_id1
171 ,rul.object2_id2
172 ,rul.jtot_object2_code
173 ,rul.object3_id1
174 ,rul.object3_id2
175 ,rul.jtot_object3_code
176 ,rul.rule_information_category
177 ,rul.rule_information1
178 FROM okc_rule_groups_b rgp
179 ,okc_rules_b rul
180 WHERE rgp.dnz_chr_id = b_chr_id
181 AND rgp.cle_id IS NULL
182 AND rul.rgp_id = rgp.id
183 AND rul.rule_information_category IN ( 'SMD', 'CAN', 'FRT','PTR');
184
185
186 l_k_header c_k_header%rowtype;
187 l_ph_header c_ph_header%rowtype;
188 l_ph_lines c_ph_lines%rowtype;
189 l_ph_line_breaks c_ph_line_break%rowtype;
190 l_ph_rules c_ph_rules%rowtype;
191 l_ph_header_qual c_ph_header_qual%rowtype;
192
193 l_control_rec QP_GLOBALS.Control_Rec_Type;
194 l_return_status VARCHAR2(1);
195 l_msg_index NUMBER;
196 l_cnt NUMBER := 0;
197 l_hdr_qual_cnt NUMBER := 0;
198 l_line_cnt NUMBER := 0;
199 l_line_pattr_cnt NUMBER := 0;
200 l_line_breaks_cnt NUMBER := 0;
201 l_line_breaks_pattr_cnt NUMBER := 0;
202 l_qp_rlship_cnt NUMBER := 0;
203 cle_cnt NUMBER := 0; -- count of lines being updated in okc_k_lines_b with new qp references;
204 phl_cnt NUMBER := 0; -- count of lines being updated in okc_ph_line_breaks with new qp references;
205 -- Modifier API related variables
206 l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
207 l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
208 l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
209 l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
210 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
211 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
212 l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
213 l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
214 l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
215 l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
216 l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
217 l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
218 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
219 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
220 l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
221 l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
222
223
224 BEGIN
225 Fnd_Msg_Pub.Initialize;
226 IF (l_debug = 'Y') THEN
227 okc_util.print_trace(1,'start process_price_hold');
228 END IF;
229
230 l_call_qp_api := false;
231 l_cust_found := false;
232 l_cnt := 0;
233 l_hdr_qual_cnt := 0;
234 l_line_cnt := 0;
235 l_line_breaks_cnt := 0;
236 l_line_breaks_pattr_cnt := 0;
237 l_line_pattr_cnt := 0;
238 l_qp_rlship_cnt := 0;
239 l_qp_ph_relship_tbl.delete;
240 l_clev_tbl.delete;
241 lx_clev_tbl.delete;
242 l_ph_line_breaks_tbl.delete;
243 lx_ph_line_breaks_tbl.delete;
244 cle_cnt := 0;
245 phl_cnt := 0;
246
247 OPEN c_k_header(p_chr_id);
248 FETCH c_k_header into l_k_header;
249
250 IF c_k_header%found then
251
252 IF (l_debug = 'Y') THEN
253 okc_util.print_trace(1,'Contract is a Sell contract and is not a template');
254 END IF;
255
256 OPEN c_ph_header(p_chr_id);
257
258
259 FETCH c_ph_header into l_ph_header;
260
261
262 IF c_ph_header%found
263 AND l_ph_header.DATE_TERMINATED IS NULL
264 AND p_operation_code='UPDATE' then
265
266 -- This is the case of creation and updation of Modfiers .
267
268 fnd_profile.put('QP_SOURCE_SYSTEM_CODE','OKC');
269 --Setting the QP system Profile Option to OKC.so that Modifier cannot be updated from QP screens.
270
271 IF (l_debug = 'Y') THEN
272 okc_util.print_trace(2,'Profile Option QP_SOURCE_SYSTEM_CODE set to OKC');
273 END IF;
274
275
276 IF nvl(l_ph_header.ph_integrated_with_qp,'N')='N' then
277
278 l_call_qp_api := true;
279 l_MODIFIER_LIST_rec.currency_code := l_k_header.currency_code;
280 l_MODIFIER_LIST_rec.list_type_code := 'DLT';
281 l_MODIFIER_LIST_rec.start_date_active := l_ph_header.start_date;
282 l_MODIFIER_LIST_rec.end_date_active := l_ph_header.end_date;
283 l_MODIFIER_LIST_rec.source_system_code := 'OKC';
284 l_MODIFIER_LIST_rec.active_flag := 'Y';
285 l_MODIFIER_LIST_rec.automatic_flag := 'Y';
286
287
288 IF l_ph_header.ph_qp_reference_id IS Null then
289 -- This is case of creation of Modifiers
290
291 IF (l_debug = 'Y') THEN
292 okc_util.print_trace(2,'Modifier needs to be created');
293 END IF;
294 l_MODIFIER_LIST_rec.comments := l_k_header.short_description;
295
296 OKC_API.set_message(p_app_name => g_app_name, --OKC
297 p_msg_name => 'OKC_PH_MODIFIER_NAME',
298 p_token1 => 'KNUMBER',
299 p_token1_value => l_k_header.contract_number,
300 p_token2 => 'KMODIFIER',
301 p_token2_value => nvl(l_k_header.contract_number_modifier,' '));
302
303 l_MODIFIER_LIST_rec.description := ltrim(rtrim(Fnd_Msg_Pub.Get( p_msg_index => Fnd_Msg_Pub.G_LAST, p_encoded => FND_API.G_FALSE ))); --Modifier Name Transalated
304 l_MODIFIER_LIST_rec.name := l_modifier_list_rec.description;
305
306 l_MODIFIER_LIST_rec.version_no := '0.1';
307 l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_CREATE;
308
309 IF (l_debug = 'Y') THEN
310
311 okc_util.print_trace(2,'Modifer Name : '|| l_MODIFIER_LIST_rec.name );
312 END IF;
313
314
315 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
316
317 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
318 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_header.id;
319 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_header.object_version_number;
320 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PRICE_HOLD';
321
322 ELSE
323
324 -- This is case of updation of Modifiers
325
326 l_MODIFIER_LIST_rec.list_header_id := l_ph_header.ph_qp_reference_id;
327 l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
328
329
330 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
331
332 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
333 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_header.id;
334 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_header.object_version_number;
335 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PRICE_HOLD';
336
337 END IF; -- IF l_ph_header.ph_qp_reference_id IS Null
338
339 IF l_ph_header.ph_qp_reference_id IS Not Null then
340
341 -- Checking if any qualifer was created when modifier was created for the first time. IF yes THEN deleting those qualifers.
342
343 OPEN c_ph_header_qual(l_ph_header.ph_qp_reference_id);
344
345 LOOP
346
347 FETCH c_ph_header_qual into l_ph_header_qual;
348 exit when c_ph_header_qual%notfound;
349 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
350 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_id := l_ph_header_qual.qualifier_id;
351 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_DELETE;
352
353 END LOOP;
354 CLOSE c_ph_header_qual;
355 END IF; -- IF l_ph_header.ph_qp_reference_id IS Not Null then
356
357
358 -- Creating Header level qualifier for Contract Number
359 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
360 l_QUALIFIERS_tbl(l_hdr_qual_cnt).excluder_flag := 'N';
361 l_QUALIFIERS_tbl(l_hdr_qual_cnt).comparison_operator_code := '=';
362 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_context := 'CUSTOMER';
363 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attribute := 'QUALIFIER_ATTRIBUTE31';
364 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value := p_chr_id;
365 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_grouping_no := -1;
366 l_QUALIFIERS_tbl(l_hdr_qual_cnt).start_date_active := l_ph_header.start_date;
367 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
368 -- Creating Header level qualifier for price list
369
370 IF nvl(l_ph_header.ph_enforce_price_list_yn,'N')='Y' then
371
372 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
373 l_QUALIFIERS_tbl(l_hdr_qual_cnt).excluder_flag := 'N';
374 l_QUALIFIERS_tbl(l_hdr_qual_cnt).comparison_operator_code := '=';
375 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_context := 'MODLIST';
376 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attribute := 'QUALIFIER_ATTRIBUTE4';
377 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value := l_ph_header.price_list_id;
378 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_grouping_no := -1;
379 -- l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_precedence := 1;
380 l_QUALIFIERS_tbl(l_hdr_qual_cnt).start_date_active := l_ph_header.start_date;
381 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
382 IF (l_debug = 'Y') THEN
383 okc_util.print_trace(2,'Qualifier Enforce Price List id '||l_ph_header.price_list_id);
384 END IF;
385
386 END IF; -- IF nvl(l_ph_header.ph_enforce_price_list_yn,'N')='Y' then
387
388 -- Creating Header level qualifier to enforce Minimum Order Amount Condition
389
390 IF l_ph_header.ph_min_amt is Not Null then
391
392 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
393 l_QUALIFIERS_tbl(l_hdr_qual_cnt).excluder_flag := 'N';
394 l_QUALIFIERS_tbl(l_hdr_qual_cnt).comparison_operator_code := 'BETWEEN';
395 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_context := 'VOLUME';
396 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attribute := 'QUALIFIER_ATTRIBUTE10';
397 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value := l_ph_header.ph_min_amt;
398 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value_to := 999999999999999999999;
399 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_grouping_no := -1;
400 -- l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_precedence := 1;
401 l_QUALIFIERS_tbl(l_hdr_qual_cnt).start_date_active := l_ph_header.start_date;
402 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
403 IF (l_debug = 'Y') THEN
404 okc_util.print_trace(2,'Qualifier Minimum Purchase Order Amount '||l_ph_header.ph_min_amt);
405 END IF;
406
407 END IF; -- IF l_ph_header.ph_min_amt is Not Null then
408
409
410
411 -- Creating Header level qualifier to enforce Minimum Order qty Condition
412
413 IF l_ph_header.ph_min_qty is Not Null then
414
415 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
416 l_QUALIFIERS_tbl(l_hdr_qual_cnt).excluder_flag := 'N';
417 l_QUALIFIERS_tbl(l_hdr_qual_cnt).comparison_operator_code := 'BETWEEN';
418 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_context := 'VOLUME';
419 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attribute := 'QUALIFIER_ATTRIBUTE17';
420 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value := l_ph_header.ph_min_qty;
421 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value_to := 999999999999999999999;
422 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_grouping_no := -1;
423 -- l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_precedence := 1;
424 l_QUALIFIERS_tbl(l_hdr_qual_cnt).start_date_active := l_ph_header.start_date;
425 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
426 IF (l_debug = 'Y') THEN
427 okc_util.print_trace(2,'Qualifier Minimum Purchase Order Qty '||l_ph_header.ph_min_qty);
428 END IF;
429
430 END IF; -- IF l_ph_header.ph_min_qty is Not Null then
431
432 OPEN c_ph_rules(p_chr_id);
433
434 LOOP
435 FETCH c_ph_rules into l_ph_rules;
436 exit when c_ph_rules%notfound;
437
438 IF l_ph_rules.rule_information_category='CAN' THEN
439
440 l_cust_found := true;
441 l_hdr_qual_cnt := l_hdr_qual_cnt +1;
442 l_QUALIFIERS_tbl(l_hdr_qual_cnt).excluder_flag := 'N';
443 l_QUALIFIERS_tbl(l_hdr_qual_cnt).comparison_operator_code := '=';
444 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_context := 'CUSTOMER';
445 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attribute := 'QUALIFIER_ATTRIBUTE2';
446 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_attr_value := l_ph_rules.object1_id1;
447 l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_grouping_no := -1;
448 -- l_QUALIFIERS_tbl(l_hdr_qual_cnt).qualifier_precedence := 1;
449 l_QUALIFIERS_tbl(l_hdr_qual_cnt).start_date_active := l_ph_header.start_date;
450 l_QUALIFIERS_tbl(l_hdr_qual_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
451 IF (l_debug = 'Y') THEN
452 okc_util.print_trace(2,'Qualifier Customer Account '||l_ph_rules.object1_id1);
453 END IF;
454
455
456 END IF;
457
458 END LOOP;
459
460 CLOSE c_ph_rules;
461
462 IF not l_cust_found THEN
463
464 OKC_API.set_message(p_app_name => g_app_name, --OKC
465 p_msg_name => 'OKC_NO_PRICE_HOLD_CAN');
466
467 IF (l_debug = 'Y') THEN
468 okc_util.print_trace(2,'No Customer Account found');
469 END IF;
470
471 RAISE OKC_API.G_EXCEPTION_ERROR;
472
473 END IF;
474
475 END IF; -- IF nvl(l_ph_header.ph_integrated_with_qp,'N')='N' then
476
477 -- Start Creating Modifier Lines
478 OPEN c_ph_lines(l_ph_header.id);
479
480 LOOP
481 FETCH c_ph_lines INTO l_ph_lines;
482 EXIT WHEN c_ph_lines%NOTFOUND;
483
484 IF nvl(l_ph_lines.ph_integrated_with_qp,'N')='N' THEN
485
486 l_call_qp_api := true;
487 l_line_cnt := l_line_cnt + 1;
488
489 IF l_ph_lines.ph_qp_reference_id IS NULL THEN
490
491 l_MODIFIERS_tbl(l_line_cnt).automatic_flag:= 'Y';
492 l_MODIFIERS_tbl(l_line_cnt).modifier_level_code := 'LINE';
493 l_MODIFIERS_tbl(l_line_cnt).pricing_phase_id := 2;
494 l_MODIFIERS_tbl(l_line_cnt).product_precedence := -9999;
495 l_MODIFIERS_tbl(l_line_cnt).list_line_type_code := 'DIS';
496 l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
497
498
499 /* IF Modifer has already been created before and new line is being added.In that case Modifier line table needs List header id */
500
501 IF l_ph_header.ph_qp_reference_id IS NOT NULL THEN
502
503 l_MODIFIERS_tbl(l_line_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
504
505 END IF;
506
507 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
508
509 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
510 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_lines.id;
511 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_lines.object_version_number;
512 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PH_LINE';
513 l_qp_ph_relship_tbl(l_qp_rlship_cnt).modifier_tbl_index := l_line_cnt;
514
515 ELSE
516 l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
517 l_MODIFIERS_tbl(l_line_cnt).list_line_id := l_ph_lines.ph_qp_reference_id;
518 l_MODIFIERS_tbl(l_line_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
519
520 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
521
522 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
523 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_lines.id;
524 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_lines.object_version_number;
525 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PH_LINE';
526 l_qp_ph_relship_tbl(l_qp_rlship_cnt).modifier_tbl_index := l_line_cnt;
527
528 END IF; --IF l_ph_lines.ph_qp_reference_id IS NULL then
529
530 l_MODIFIERS_tbl(l_line_cnt).start_date_active := l_ph_lines.start_date;
531 l_MODIFIERS_tbl(l_line_cnt).end_date_active := l_ph_lines.end_date;
532 l_MODIFIERS_tbl(l_line_cnt).incompatibility_grp_code := fnd_profile.value('OKC_PH_LINE_INCOMPATIBILITY_GROUP');
533
534 IF (l_debug = 'Y') THEN
535 okc_util.print_trace(3,'Incompatibility Group : '||l_MODIFIERS_tbl(l_line_cnt).incompatibility_grp_code );
536 END IF;
537
538 IF l_ph_lines.ph_qp_reference_id IS NULL then
539
540 -- Creation of Pricing Attribute
541
542 IF l_ph_lines.lse_id=62 THEN -- Line style is Item Number
543
544 l_line_pattr_cnt := l_line_pattr_cnt + 1;
545
546 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE1';
547 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attr_value:= l_ph_lines.object1_id1; -- Inventory Item Id
548
549 ELSIF l_ph_lines.lse_id=63 THEN -- Line style is Item category
550
551 l_line_pattr_cnt := l_line_pattr_cnt + 1;
552 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE2';
553 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attr_value:= l_ph_lines.object1_id1; -- Category Id
554
555 ELSIF l_ph_lines.lse_id=64 THEN -- Line style is All Items
556
557 l_line_pattr_cnt := l_line_pattr_cnt + 1;
558 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE3';
559 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attr_value:= 'ALL'; -- Inventory Item Id
560
561 END IF; -- IF l_ph_lines.lse_id=62 then
562
563 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_attribute_context:= 'ITEM';
564 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_uom_code := l_ph_lines.uom_code;
565 l_PRICING_ATTR_tbl(l_line_pattr_cnt).excluder_flag:= 'N';
566 l_PRICING_ATTR_tbl(l_line_pattr_cnt).MODIFIERS_index:=l_line_cnt;
567 l_PRICING_ATTR_tbl(l_line_pattr_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
568 ELSE
569
570 -- Updation of Pricing Attribute
571 l_line_pattr_cnt := l_line_pattr_cnt + 1;
572 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_uom_code := l_ph_lines.uom_code;
573
574 -- get pricing attribute id to be updated
575
576 OPEN c_ph_get_pattr_id(l_ph_lines.ph_qp_reference_id,l_ph_header.ph_qp_reference_id);
577
578 FETCH c_ph_get_pattr_id INTO l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute_id;
579
580 CLOSE c_ph_get_pattr_id;
581
582 l_PRICING_ATTR_tbl(l_line_pattr_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
583
584 END IF; --IF l_ph_lines.ph_qp_reference_id IS NULL then
585
586 IF l_ph_lines.ph_pricing_type='DISCOUNT_PERCENT' then
587
588 l_MODIFIERS_tbl(l_line_cnt).operand := l_ph_lines.ph_value;
589 l_MODIFIERS_tbl(l_line_cnt).arithmetic_operator := '%';
590
591 ELSIF l_ph_lines.ph_pricing_type='DISCOUNT_AMOUNT' then
592 l_MODIFIERS_tbl(l_line_cnt).operand := l_ph_lines.ph_value;
593 l_MODIFIERS_tbl(l_line_cnt).arithmetic_operator := 'AMT';
594 ELSIF l_ph_lines.ph_pricing_type='NEW_PRICE' then
595 l_MODIFIERS_tbl(l_line_cnt).operand := l_ph_lines.ph_value;
596 l_MODIFIERS_tbl(l_line_cnt).arithmetic_operator := 'NEWPRICE';
597 ELSIF l_ph_lines.ph_pricing_type='PRICE_BREAK' then
598
599 IF l_ph_lines.ph_qp_reference_id IS NULL then
600 l_MODIFIERS_tbl(l_line_cnt).list_line_type_code := 'PBH';
601 l_MODIFIERS_tbl(l_line_cnt).price_break_type_code := 'POINT';
602 l_MODIFIERS_tbl(l_line_cnt).modifier_parent_index := l_line_cnt;
603 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute_context:= 'VOLUME';
604 l_PRICING_ATTR_tbl(l_line_pattr_cnt).comparison_operator_code:= 'BETWEEN';
605 ELSE
606
607 l_MODIFIERS_tbl(l_line_cnt).modifier_parent_index := l_line_cnt;
608 END IF; -- IF l_ph_lines.ph_qp_reference_id IS NULL then
609
610 IF l_ph_lines.ph_price_break_basis='ITEM_QUANTITY' then
611 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE10';
612
613 ELSIF l_ph_lines.ph_price_break_basis='ITEM_AMOUNT' then
614 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE12';
615
616 END IF; -- IF l_ph_lines.ph_price_break_basis='ITEM_QUANTITY' then
617
618
619 l_line_breaks_cnt := l_line_cnt;
620 l_line_breaks_pattr_cnt := l_line_pattr_cnt;
621
622 OPEN c_ph_line_break(l_ph_lines.id);
623 LOOP
624 FETCH c_ph_line_break INTO l_ph_line_breaks;
625 EXIT WHEN c_ph_line_break%NOTFOUND;
626
627 l_line_breaks_cnt := l_line_breaks_cnt + 1;
628
629 IF l_ph_line_breaks.qp_reference_id IS NULL then
630 l_MODIFIERS_tbl(l_line_breaks_cnt).automatic_flag:= 'Y';
631 l_MODIFIERS_tbl(l_line_breaks_cnt).modifier_level_code := 'LINE';
632 l_MODIFIERS_tbl(l_line_breaks_cnt).accrual_flag := 'N';
633 l_MODIFIERS_tbl(l_line_breaks_cnt).pricing_group_sequence := 1;
634 l_MODIFIERS_tbl(l_line_breaks_cnt).pricing_phase_id := 2;
635 l_MODIFIERS_tbl(l_line_breaks_cnt).product_precedence := 1;
636 l_MODIFIERS_tbl(l_line_breaks_cnt).price_break_type_code := 'POINT';
637 l_MODIFIERS_tbl(l_line_breaks_cnt).modifier_parent_index := l_line_cnt;
638 l_MODIFIERS_tbl(l_line_breaks_cnt).rltd_modifier_grp_no := 10;
639 l_MODIFIERS_tbl(l_line_breaks_cnt).rltd_modifier_grp_type := 'PRICE BREAK';
640 l_MODIFIERS_tbl(l_line_breaks_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
641
642 /* IF Modifer has already been created before and new line break is being added.In that case Modifier line table needs List header id */
643
644 IF l_ph_header.ph_qp_reference_id IS NOT NULL THEN
645
646 l_MODIFIERS_tbl(l_line_breaks_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
647
648 END IF;
649
650 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
651
652 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
653 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_line_breaks.id;
654 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_line_breaks.object_version_number;
655 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PH_LINE_BREAK';
656 l_qp_ph_relship_tbl(l_qp_rlship_cnt).modifier_tbl_index := l_line_breaks_cnt;
657
658 ELSE
659 l_MODIFIERS_tbl(l_line_breaks_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
660 l_MODIFIERS_tbl(l_line_breaks_cnt).list_line_id := l_ph_line_breaks.qp_reference_id;
661 l_MODIFIERS_tbl(l_line_breaks_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
662 /* Updating qp-okc relationship . This table will be used to update okc table with qp_refrence */
663
664 l_qp_rlship_cnt := l_qp_rlship_cnt +1;
665 l_qp_ph_relship_tbl(l_qp_rlship_cnt).id := l_ph_line_breaks.id;
666 l_qp_ph_relship_tbl(l_qp_rlship_cnt).object_version_number := l_ph_line_breaks.object_version_number;
667 l_qp_ph_relship_tbl(l_qp_rlship_cnt).line_type := 'PH_LINE_BREAK';
668 l_qp_ph_relship_tbl(l_qp_rlship_cnt).modifier_tbl_index := l_line_breaks_cnt;
669
670 END IF; -- IF l_ph_line_breaks.qp_reference_id IS NULL then
671 l_MODIFIERS_tbl(l_line_breaks_cnt).list_line_type_code := 'DIS';
672 l_MODIFIERS_tbl(l_line_breaks_cnt).operand := l_ph_line_breaks.value;
673 l_MODIFIERS_tbl(l_line_breaks_cnt).start_date_active := l_ph_lines.start_date;
674 l_MODIFIERS_tbl(l_line_breaks_cnt).end_date_active := l_ph_lines.end_date;
675
676 IF l_ph_line_breaks.pricing_type='DISCOUNT_PERCENT' then
677 l_MODIFIERS_tbl(l_line_breaks_cnt).arithmetic_operator := '%';
678 ELSIF l_ph_line_breaks.pricing_type ='DISCOUNT_AMOUNT' then
679 l_MODIFIERS_tbl(l_line_breaks_cnt).arithmetic_operator := 'AMT';
680
681 ELSIF l_ph_line_breaks.pricing_type ='NEW_PRICE' then
682 l_MODIFIERS_tbl(l_line_breaks_cnt).arithmetic_operator := 'NEWPRICE';
683 END IF; --IF l_ph_line_breaks.pricing_type='DISCOUNT_PERCENT' then
684
685 l_line_breaks_pattr_cnt := l_line_breaks_pattr_cnt + 1;
686 IF l_ph_line_breaks.qp_reference_id IS NULL then
687 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).MODIFIERS_index:= l_line_breaks_cnt;
688 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).operation := QP_GLOBALS.G_OPR_CREATE;
689
690 IF l_ph_lines.lse_id=62 THEN -- to be changed with actual lse id of subline Item Number from seed
691
692 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE1';
693 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attr_value:= l_ph_lines.object1_id1; -- Inventory Item Id
694 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute_context:= 'ITEM';
695
696 ELSIF l_ph_lines.lse_id=63 THEN -- to be changed with actual lse id of subline Item category
697
698 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE2';
699 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attr_value:= l_ph_lines.object1_id1; -- Category Id
700 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute_context:= 'ITEM';
701
702 ELSIF l_ph_lines.lse_id=64 THEN -- to be changed with actual lse id of subline All Items
703
704 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute:= 'PRICING_ATTRIBUTE3';
705 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attr_value:= 'ALL'; -- Inventory Item Id
706 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_attribute_context:= 'ITEM';
707 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).excluder_flag:= 'N';
708
709 END IF; -- IF l_ph_lines.lse_id=62 then
710
711 ELSE
712 -- get pricing attribute id to be updated
713 OPEN c_ph_get_pattr_id(l_ph_line_breaks.qp_reference_id,l_ph_header.ph_qp_reference_id);
714 FETCH c_ph_get_pattr_id INTO l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attribute_id;
715 CLOSE c_ph_get_pattr_id;
716
717 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
718
719 END IF; --IF l_ph_line_breaks.qp_reference_id IS NULL then
720
721
722 IF l_ph_lines.ph_price_break_basis='ITEM_QUANTITY' then
723 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE10';
724
725 ELSIF l_ph_lines.ph_price_break_basis='ITEM_AMOUNT' then
726 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE12';
727
728 END IF; -- IF l_ph_lines.ph_price_break_basis='ITEM_QUANTITY' then
729
730
731 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attribute_context:= 'VOLUME';
732
733 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attr_value_from:= l_ph_line_breaks.value_from;
734 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).pricing_attr_value_to:= l_ph_line_breaks.value_to;
735 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).comparison_operator_code:= 'BETWEEN';
736 l_PRICING_ATTR_tbl(l_line_breaks_pattr_cnt).product_uom_code:= l_ph_lines.uom_code;
737
738 END LOOP;
739
740 CLOSE c_ph_line_break;
741
742 l_line_cnt := l_line_breaks_cnt;
743 l_line_pattr_cnt := l_line_breaks_pattr_cnt;
744 l_line_breaks_pattr_cnt:=0;
745 END IF; -- IF l_ph_lines.ph_pricing_type='DISCOUNT_PERCENT' then
746
747
748 /* Line Purchase Minimum Condition */
749
750 IF l_ph_lines.ph_pricing_type <> 'PRICE_BREAK' THEN
751
752 IF l_ph_lines.ph_min_qty is NOT NULL THEN
753
754 l_modifiers_tbl(l_line_cnt).price_break_type_code :='POINT';
755 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute_context:= 'VOLUME';
756
757 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE10';
758
759 l_PRICING_ATTR_tbl(l_line_pattr_cnt).comparison_operator_code:= 'BETWEEN';
760
761 l_PRICING_ATTR_tbl(l_line_pattr_cnt).product_uom_code:= l_ph_lines.uom_code;
762
763 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_from:= l_ph_lines.ph_min_qty;
764
765 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_to:= '999999999999999999999';
766
767 ELSIF l_ph_lines.ph_min_amt IS NOT NULL THEN
768
769 l_modifiers_tbl(l_line_cnt).price_break_type_code :='POINT';
770 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute_context:= 'VOLUME';
771
772 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute:= 'PRICING_ATTRIBUTE12';
773
774 l_PRICING_ATTR_tbl(l_line_pattr_cnt).comparison_operator_code:= 'BETWEEN';
775
776 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_from:= l_ph_lines.ph_min_amt;
777
778 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_to:= '999999999999999999999';
779
780 ELSIF l_ph_lines.ph_min_amt IS NULL AND l_ph_lines.ph_min_qty IS NULL THEN
781
782 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute_context:= Null;
783
784 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attribute:= Null;
785
786 l_PRICING_ATTR_tbl(l_line_pattr_cnt).comparison_operator_code:= Null;
787 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_from:=Null;
788 l_PRICING_ATTR_tbl(l_line_pattr_cnt).pricing_attr_value_to:= Null;
789
790 END IF; -- IF l_ph_lines.ph_min_qty is NOT NULL then
791
792
793 END IF; -- IF l_ph_lines.ph_pricing_type <> 'PRICE_BREAK' then
794
795 /* End Line Purchase Minimum */
796
797
798 END IF; --- IF nvl(l_ph_lines.ph_integrated_with_qp,'N')='N' then
799 END LOOP;
800 CLOSE c_ph_lines;
801
802
803
804 IF l_call_qp_api THEN
805
806 IF (l_debug = 'Y') THEN
807 okc_util.print_trace(2,'Calling QP_Modifiers_PUB.proces_modifiers');
808 END IF;
809
810 QP_Modifiers_PUB.Process_Modifiers(p_api_version_number => 1.0
811 ,p_init_msg_list => FND_API.G_FALSE
812 ,p_return_values => FND_API.G_FALSE
813 ,p_commit => FND_API.G_FALSE
814 ,x_return_status => l_return_status
815 ,x_msg_count => x_msg_count
816 ,x_msg_data => x_msg_data
817 ,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
818 ,p_MODIFIERS_tbl => l_MODIFIERS_tbl
819 ,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
820 ,p_PRICING_ATTR_tbl=> l_PRICING_ATTR_tbl
821 ,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
822 ,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
823 ,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
824 ,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
825 ,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
826 ,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
827 ,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
828 ,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
829 );
830
831 IF (l_debug = 'Y') THEN
832 okc_util.print_trace(2,'Exited QP_Modifiers_proces_modifiers, Status '||l_return_status);
833 END IF;
834
835 IF (l_debug = 'Y') THEN
836 okc_util.print_trace(2,'List Header Id '||l_x_MODIFIER_LIST_rec.list_header_id);
837 END IF;
838
839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842
843 END IF;
844
845
846 END IF; -- IF l_call_qp_api THEN
847 /* Build PL/SQL table to update Lines and line breaks with qp_refrence_id */
848
849 IF l_qp_ph_relship_tbl.first IS NOT NULL and l_call_qp_api THEN
850
851 FOR i IN l_qp_ph_relship_tbl.first..l_qp_ph_relship_tbl.last LOOP
852 IF l_qp_ph_relship_tbl(i).line_type ='PRICE_HOLD' then
853
854 cle_cnt := cle_cnt + 1;
855 l_clev_tbl(cle_cnt).id := l_qp_ph_relship_tbl(i).id;
856 l_clev_tbl(cle_cnt).object_version_number := l_qp_ph_relship_tbl(i).object_version_number;
857 l_clev_tbl(cle_cnt).ph_integrated_with_qp := 'Y';
858 l_clev_tbl(cle_cnt).ph_qp_reference_id := l_x_MODIFIER_LIST_rec.list_header_id;
859 IF (l_debug = 'Y') THEN
860 okc_util.print_trace(3,' id '|| l_clev_tbl(cle_cnt).id ||' ph_qp_reference_id '||l_clev_tbl(cle_cnt).ph_qp_reference_id);
861 END IF;
862
863 ELSIF l_qp_ph_relship_tbl(i).line_type ='PH_LINE' then
864
865 cle_cnt := cle_cnt + 1;
866 l_clev_tbl(cle_cnt).id := l_qp_ph_relship_tbl(i).id;
867 l_clev_tbl(cle_cnt).object_version_number := l_qp_ph_relship_tbl(i).object_version_number;
868 l_clev_tbl(cle_cnt).ph_integrated_with_qp := 'Y';
869 l_clev_tbl(cle_cnt).ph_qp_reference_id := l_x_MODIFIERS_tbl(l_qp_ph_relship_tbl(i).modifier_tbl_index).list_line_id;
870
871 IF (l_debug = 'Y') THEN
872 okc_util.print_trace(3,' id '|| l_clev_tbl(cle_cnt).id ||' ph_qp_reference_id '||l_clev_tbl(cle_cnt).ph_qp_reference_id);
873 END IF;
874
875 ELSIF l_qp_ph_relship_tbl(i).line_type ='PH_LINE_BREAK' then
876
877 phl_cnt := phl_cnt + 1;
878 l_ph_line_breaks_tbl(phl_cnt).id := l_qp_ph_relship_tbl(i).id;
879 l_ph_line_breaks_tbl(phl_cnt).object_version_number := l_qp_ph_relship_tbl(i).object_version_number;
880 l_ph_line_breaks_tbl(phl_cnt).integrated_with_qp := 'Y';
881 l_ph_line_breaks_tbl(phl_cnt).qp_reference_id := l_x_MODIFIERS_tbl(l_qp_ph_relship_tbl(i).modifier_tbl_index).list_line_id;
882 IF (l_debug = 'Y') THEN
883 okc_util.print_trace(3,' break id '|| l_ph_line_breaks_tbl(phl_cnt).id ||' ph_qp_reference_id '||l_ph_line_breaks_tbl(phl_cnt).qp_reference_id);
884 END IF;
885 END IF;
886 END LOOP;
887
888 IF l_clev_tbl.count > 0 then
889 IF (l_debug = 'Y') THEN
890 okc_util.print_trace(2,'Start OKC_CONTRACT_PUB.update_contract_line');
891 END IF;
892
893 OKC_CONTRACT_PUB.update_contract_line( p_api_version => 1.0,
894 p_init_msg_list => FND_API.G_FALSE,
895 x_return_status => l_return_status,
896 x_msg_count => x_msg_count,
897 x_msg_data => x_msg_data,
898 p_restricted_update => OKC_API.G_TRUE,
899 p_clev_tbl => l_clev_tbl,
900 x_clev_tbl => lx_clev_tbl);
901
902 IF (l_debug = 'Y') THEN
903 okc_util.print_trace(2,'End OKC_CONTRACT_PUB.update_contract_line:Status'||l_return_status);
904 END IF;
905
906
907 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
908
909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
910
911 END IF;
912
913 END IF;
914
915 IF l_ph_line_breaks_tbl.count > 0 then
916
917 IF (l_debug = 'Y') THEN
918 okc_util.print_trace(2,'Start OKC_PHL_PVT.update_row');
919 END IF;
920 OKC_PHL_PVT.update_row( p_api_version => 1.0,
921 p_init_msg_list => FND_API.G_FALSE,
922 x_return_status => l_return_status,
923 x_msg_count => x_msg_count,
924 x_msg_data => x_msg_data,
925 p_okc_ph_line_breaks_v_tbl =>l_ph_line_breaks_tbl,
926 x_okc_ph_line_breaks_v_tbl =>lx_ph_line_breaks_tbl);
927 IF (l_debug = 'Y') THEN
928 okc_util.print_trace(2,'End OKC_PHL_PVT.update_row:Status'||l_return_status);
929 END IF;
930
931
932 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
933
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
935
936 END IF;
937
938 END IF;
939
940 END IF; -- IF l_qp_ph_relship_tbl.first IS NOT NULL THEN
941
942 ELSIF c_ph_header%FOUND
943 AND p_operation_code='TERMINATE'
944 AND l_ph_header.ph_qp_reference_id IS NOT NULL THEN
945
946
947 IF p_termination_date IS NULL THEN
948
949 OKC_API.set_message(p_app_name => g_app_name, --OKC
950 p_msg_name => 'OKC_NO_PH_TERMINATE_DATE');
951
952 RAISE OKC_API.G_EXCEPTION_ERROR;
953
954 END IF;
955
956 l_call_qp_api := true ;
957
958 -- case of end date modifier.
959 fnd_profile.put('QP_SOURCE_SYSTEM_CODE','OKC');
960
961 --Setting the QP system Profile Option to OKC.so that Modifer cannot be updated from QP screens.
962
963 IF (l_debug = 'Y') THEN
964 okc_util.print_trace(2,'Profile Option QP_SOURCE_SYSTEM_CODE set to OKC');
965 END IF;
966
967 l_MODIFIER_LIST_rec.end_date_active := p_termination_date;
968 l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
969 l_MODIFIER_LIST_rec.list_header_id := l_ph_header.ph_qp_reference_id;
970
971 IF l_call_qp_api THEN
972
973
974 IF (l_debug = 'Y') THEN
975 okc_util.print_trace(2,'Calling QP_Modifiers_proces_modifiers');
976 END IF;
977
978 QP_Modifiers_PUB.Process_Modifiers (p_api_version_number => 1.0
979 ,p_init_msg_list => FND_API.G_FALSE
980 ,p_return_values => FND_API.G_FALSE
981 ,p_commit => FND_API.G_FALSE
982 ,x_return_status => l_return_status
983 ,x_msg_count =>x_msg_count
984 ,x_msg_data => x_msg_data
985 ,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
986 ,p_MODIFIERS_tbl => l_MODIFIERS_tbl
987 ,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
988 ,p_PRICING_ATTR_tbl=> l_PRICING_ATTR_tbl
989 ,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
990 ,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
991 ,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
992 ,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
993 ,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
994 ,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
995 ,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
996 ,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
997 );
998 IF (l_debug = 'Y') THEN
999 okc_util.print_trace(2,'Exited QP_Modifiers_proces_modifiers, Status '||l_return_status);
1000 okc_util.print_trace(2,'List Header Id '||l_x_MODIFIER_LIST_rec.list_header_id);
1001 END IF;
1002
1003 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005 END IF;
1006 END IF;
1007
1008 END IF;
1009
1010
1011 CLOSE c_ph_header;
1012
1013 END IF;
1014
1015 CLOSE c_k_header;
1016
1017 x_return_status := FND_API.G_RET_STS_SUCCESS;
1018
1019 EXCEPTION
1020
1021 WHEN FND_API.G_EXC_ERROR THEN
1022 -- transferring error messages from QP to FND error stack
1023 FOR i in 1..x_msg_count LOOP
1024 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1025 p_encoded => 'F'
1026 );
1027
1028 FND_MSG_PUB.Add_Exc_Msg (
1029 p_pkg_name => 'OKC_PHI_PVT',
1030 p_procedure_name => 'process_price_hold',
1031 p_error_text => x_msg_data
1032 );
1033 END LOOP;
1034
1035 IF c_k_header%ISOPEN THEN
1036 CLOSE c_k_header;
1037 END IF;
1038
1039 IF c_ph_header%ISOPEN THEN
1040 CLOSE c_ph_header;
1041 END IF;
1042
1043 IF c_ph_rules%ISOPEN THEN
1044 CLOSE c_ph_rules;
1045 END IF;
1046
1047 IF c_ph_lines%ISOPEN THEN
1048 CLOSE c_ph_lines;
1049 END IF;
1050
1051 IF c_ph_line_break%ISOPEN THEN
1052 CLOSE c_ph_line_break;
1053 END IF;
1054
1055 IF c_ph_header_qual%ISOPEN THEN
1056 CLOSE c_ph_header_qual;
1057 END IF;
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061
1062 FOR i in 1..x_msg_count LOOP
1063 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1064 p_encoded => 'F'
1065 );
1066
1067 FND_MSG_PUB.Add_Exc_Msg (
1068 p_pkg_name => 'OKC_PHI_PVT',
1069 p_procedure_name => 'process_price_hold',
1070 p_error_text => x_msg_data
1071 );
1072 END LOOP;
1073
1074 IF c_k_header%ISOPEN THEN
1075 CLOSE c_k_header;
1076 END IF;
1077
1078 IF c_ph_header%ISOPEN THEN
1079 CLOSE c_ph_header;
1080 END IF;
1081
1082 IF c_ph_rules%ISOPEN THEN
1083 CLOSE c_ph_rules;
1084 END IF;
1085
1086 IF c_ph_lines%ISOPEN THEN
1087 CLOSE c_ph_lines;
1088 END IF;
1089
1090 IF c_ph_line_break%ISOPEN THEN
1091 CLOSE c_ph_line_break;
1092 END IF;
1093
1094 IF c_ph_header_qual%ISOPEN THEN
1095 CLOSE c_ph_header_qual;
1096 END IF;
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1098
1099
1100 WHEN OTHERS THEN
1101
1102 FOR i in 1..x_msg_count LOOP
1103 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1104 p_encoded => 'F'
1105 );
1106
1107 FND_MSG_PUB.Add_Exc_Msg (
1108 p_pkg_name => 'OKC_PHI_PVT',
1109 p_procedure_name => 'process_price_hold',
1110 p_error_text => x_msg_data
1111 );
1112 END LOOP;
1113
1114 if c_k_header%ISOPEN then
1115 CLOSE c_k_header;
1116 end if;
1117
1118 if c_ph_header%ISOPEN then
1119 CLOSE c_ph_header;
1120 end if;
1121
1122 if c_ph_rules%ISOPEN then
1123 CLOSE c_ph_rules;
1124 end if;
1125 if c_ph_lines%ISOPEN then
1126 CLOSE c_ph_lines;
1127 end if;
1128
1129 if c_ph_line_break%ISOPEN then
1130 CLOSE c_ph_line_break;
1131 end if;
1132
1133 if c_ph_header_qual%ISOPEN then
1134 CLOSE c_ph_header_qual;
1135 end if;
1136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1137
1138 END;
1139
1140
1141
1142 /*
1143 -------------------------------------------------------------------------------------------------------------------------
1144 --Procedure : extend_price_hold
1145 -- Input Parameter : p_cle_id Contract Line Id of price hold topline.
1146 --
1147 -- This API is called whenever a price hold line is extended.What it does is that it extends
1148 -- modifier and modifier line in QP
1149 --
1150 ---------------------------------------------------------------------------------------------------------------------------------
1151 */
1152
1153 PROCEDURE extend_price_hold(p_api_version IN NUMBER
1154 ,p_init_msg_list IN VARCHAR2
1155 ,p_cle_id IN OKC_K_LINES_V.id%TYPE -- Price Hold Topline Id
1156 ,x_return_status OUT NOCOPY VARCHAR2
1157 ,x_msg_count OUT NOCOPY NUMBER
1158 ,x_msg_data OUT NOCOPY VARCHAR2
1159 ) IS
1160
1161 Cursor c_k_header(b_cle_id Number) IS
1162 SELECT chr.id ID
1163 FROM okc_k_headers_V chr,
1164 okc_k_lines_V cle
1165 WHERE cle.id=b_cle_id
1166 AND chr.id=cle.dnz_chr_id
1167 AND chr.application_id in (510,871)
1168 AND chr.buy_or_sell='S'
1169 AND chr.issue_or_receive='I'
1170 AND chr.template_yn='N'
1171 AND chr.deleted_yn='N'
1172 AND chr.date_terminated IS NULL
1173 AND chr.datetime_cancelled IS NULL;
1174
1175 Cursor c_ph_header(b_cle_id Number) IS
1176 SELECT id ,
1177 dnz_chr_id,
1178 ph_qp_reference_id,
1179 ph_integrated_with_qp,
1180 start_date,
1181 end_date
1182 FROM okc_k_lines_v cle
1183 WHERE cle.id=b_cle_id
1184 AND cle.cle_id is null
1185 AND cle.lse_id=61 -- Price Hold Line style.
1186 AND cle.date_terminated IS NULL
1187 AND cle.ph_qp_reference_id is not null
1188 AND rownum=1;
1189
1190
1191 Cursor c_ph_lines(b_cle_id Number) IS
1192 SELECT cle.id id,
1193 cle.line_number line_number,
1194 cle.lse_id lse_id,
1195 ph_qp_reference_id,
1196 ph_integrated_with_qp,
1197 start_date,
1198 end_date
1199 FROM okc_k_lines_v cle
1200 WHERE cle.cle_id=b_cle_id
1201 AND cle.ph_qp_reference_id is not null
1202 ORDER BY line_number;
1203
1204 Cursor c_ph_line_break(b_ph_line_id Number) IS
1205 SELECT id ,
1206 qp_reference_id,
1207 integrated_with_qp
1208 FROM okc_ph_line_breaks_v
1209 WHERE cle_id=b_ph_line_id
1210 AND qp_reference_id is not null
1211 order by id;
1212
1213
1214 l_k_header c_k_header%rowtype;
1215 l_ph_header c_ph_header%rowtype;
1216 l_ph_lines c_ph_lines%rowtype;
1217 l_ph_line_breaks c_ph_line_break%rowtype;
1218 l_line_cnt NUMBER := 0;
1219
1220 l_control_rec QP_GLOBALS.Control_Rec_Type;
1221 l_return_status VARCHAR2(1);
1222 l_msg_index NUMBER;
1223
1224 l_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
1225 l_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
1226 l_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
1227 l_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
1228 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1229 l_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1230 l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
1231 l_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
1232 l_x_MODIFIER_LIST_rec QP_Modifiers_PUB.Modifier_List_Rec_Type;
1233 l_x_MODIFIER_LIST_val_rec QP_Modifiers_PUB.Modifier_List_Val_Rec_Type;
1234 l_x_MODIFIERS_tbl QP_Modifiers_PUB.Modifiers_Tbl_Type;
1235 l_x_MODIFIERS_val_tbl QP_Modifiers_PUB.Modifiers_Val_Tbl_Type;
1236 l_x_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
1237 l_x_QUALIFIERS_val_tbl QP_Qualifier_Rules_PUB.Qualifiers_Val_Tbl_Type;
1238 l_x_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
1239 l_x_PRICING_ATTR_val_tbl QP_Modifiers_PUB.Pricing_Attr_Val_Tbl_Type;
1240
1241 BEGIN
1242 Fnd_Msg_Pub.Initialize;
1243 l_call_qp_api := false;
1244 OPEN c_k_header(p_cle_id);
1245 fetch c_k_header into l_k_header;
1246 IF c_k_header%FOUND then
1247 OPEN c_ph_header(p_cle_id);
1248 FETCH c_ph_header into l_ph_header;
1249 IF c_ph_header%FOUND THEN
1250 l_call_qp_api := true;
1251 l_MODIFIER_LIST_rec.start_date_active := l_ph_header.start_date;
1252 l_MODIFIER_LIST_rec.end_date_active := l_ph_header.end_date;
1253 l_MODIFIER_LIST_rec.active_flag := 'N';
1254 l_MODIFIER_LIST_rec.automatic_flag := 'N';
1255 l_MODIFIER_LIST_rec.list_header_id := l_ph_header.ph_qp_reference_id;
1256 l_MODIFIER_LIST_rec.operation := QP_GLOBALS.G_OPR_UPDATE;
1257 IF (l_debug = 'Y') THEN
1258 okc_util.print_trace(1,'List Header Id'||l_ph_header.ph_qp_reference_id||' Start Date '||l_ph_header.start_date||' End Date '||l_ph_header.end_date);
1259 END IF;
1260
1261 OPEN c_ph_lines(p_cle_id);
1262 LOOP
1263 FETCH c_ph_lines into l_ph_lines;
1264 EXIT WHEN c_ph_lines%NOTFOUND ;
1265 l_call_qp_api := true;
1266 l_line_cnt := l_modifiers_tbl.count + 1;
1267 l_MODIFIERS_tbl(l_line_cnt).automatic_flag:= 'N';
1268 l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
1269 l_MODIFIERS_tbl(l_line_cnt).list_line_id := l_ph_lines.ph_qp_reference_id;
1270 l_MODIFIERS_tbl(l_line_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
1271 l_MODIFIERS_tbl(l_line_cnt).start_date_active := l_ph_lines.start_date;
1272 l_MODIFIERS_tbl(l_line_cnt).end_date_active := l_ph_lines.end_date;
1273 IF (l_debug = 'Y') THEN
1274 okc_util.print_trace(2,'List Line Id'||l_ph_lines.ph_qp_reference_id||' Start Date '||l_ph_lines.start_date||' End Date '||l_ph_lines.end_date);
1275 END IF;
1276 OPEN c_ph_line_break(p_cle_id);
1277 LOOP
1278 FETCH c_ph_line_break into l_ph_line_breaks;
1279 EXIT WHEN c_ph_line_break%NOTFOUND ;
1280 l_line_cnt := l_modifiers_tbl.count + 1;
1281 l_MODIFIERS_tbl(l_line_cnt).automatic_flag:= 'N';
1282 l_MODIFIERS_tbl(l_line_cnt).operation := QP_GLOBALS.G_OPR_UPDATE;
1283 l_MODIFIERS_tbl(l_line_cnt).list_line_id := l_ph_line_breaks.qp_reference_id;
1284 l_MODIFIERS_tbl(l_line_cnt).list_header_id := l_ph_header.ph_qp_reference_id;
1285 l_MODIFIERS_tbl(l_line_cnt).start_date_active := l_ph_lines.start_date;
1286 l_MODIFIERS_tbl(l_line_cnt).end_date_active := l_ph_lines.end_date;
1287 IF (l_debug = 'Y') THEN
1288 okc_util.print_trace(3,'List Line Id'||l_ph_line_breaks.qp_reference_id||' Start Date '||l_ph_lines.start_date||' End Date '||l_ph_lines.end_date);
1289 END IF;
1290 END LOOP;
1291 CLOSE c_ph_line_break;
1292 END LOOP;
1293 CLOSE c_ph_lines;
1294
1295
1296 IF l_call_qp_api THEN
1297
1298 fnd_profile.put('QP_SOURCE_SYSTEM_CODE','OKC');
1299 --Setting the QP system Profile Option to OKC.so that Modifer cannot be updated from QP screens.
1300
1301 IF (l_debug = 'Y') THEN
1302 okc_util.print_trace(2,'Calling QP_Modifiers_proces_modifiers');
1303 END IF;
1304
1305
1306 QP_Modifiers_PUB.Process_Modifiers (p_api_version_number => 1.0
1307 ,p_init_msg_list => FND_API.G_FALSE
1308 ,p_return_values => FND_API.G_FALSE
1309 ,p_commit => FND_API.G_FALSE
1310 ,x_return_status => l_return_status
1311 ,x_msg_count =>x_msg_count
1312 ,x_msg_data => x_msg_data
1313 ,p_MODIFIER_LIST_rec => l_MODIFIER_LIST_rec
1314 ,p_MODIFIERS_tbl => l_MODIFIERS_tbl
1315 ,p_QUALIFIERS_tbl => l_QUALIFIERS_tbl
1316 ,p_PRICING_ATTR_tbl=> l_PRICING_ATTR_tbl
1317 ,x_MODIFIER_LIST_rec => l_x_MODIFIER_LIST_rec
1318 ,x_MODIFIER_LIST_val_rec => l_x_MODIFIER_LIST_val_rec
1319 ,x_MODIFIERS_tbl => l_x_MODIFIERS_tbl
1320 ,x_MODIFIERS_val_tbl => l_x_MODIFIERS_val_tbl
1321 ,x_QUALIFIERS_tbl => l_x_QUALIFIERS_tbl
1322 ,x_QUALIFIERS_val_tbl => l_x_QUALIFIERS_val_tbl
1323 ,x_PRICING_ATTR_tbl => l_x_PRICING_ATTR_tbl
1324 ,x_PRICING_ATTR_val_tbl => l_x_PRICING_ATTR_val_tbl
1325 );
1326
1327 IF (l_debug = 'Y') THEN
1328 okc_util.print_trace(2,'Exited QP_Modifiers_proces_modifiers, Status '||l_return_status);
1329 okc_util.print_trace(2,'List Header Id '||l_x_MODIFIER_LIST_rec.list_header_id);
1330 END IF;
1331
1332 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 END IF;
1335 END IF;
1336 END IF; -- IF c_ph_header%FOUND Then
1337 CLOSE c_ph_header;
1338
1339 END IF; -- IF c_k_header%FOUND then
1340
1341 close c_k_header;
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 EXCEPTION
1345
1346 WHEN FND_API.G_EXC_ERROR THEN
1347
1348 FOR i in 1..x_msg_count LOOP
1349 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1350 p_encoded => 'F'
1351 );
1352
1353 FND_MSG_PUB.Add_Exc_Msg (
1354 p_pkg_name => 'OKC_PHI_PVT',
1355 p_procedure_name => 'extend_price_hold',
1356 p_error_text => x_msg_data
1357 );
1358 END LOOP;
1359
1360 if c_k_header%ISOPEN then
1361 CLOSE c_k_header;
1362 end if;
1363
1364 if c_ph_header%ISOPEN then
1365 CLOSE c_ph_header;
1366 end if;
1367
1368 if c_ph_lines%ISOPEN then
1369 CLOSE c_ph_lines;
1370 end if;
1371
1372 if c_ph_line_break%ISOPEN then
1373 CLOSE c_ph_line_break;
1374 end if;
1375
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377
1378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1379
1380 FOR i in 1..x_msg_count LOOP
1381 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1382 p_encoded => 'F'
1383 );
1384
1385 FND_MSG_PUB.Add_Exc_Msg (
1386 p_pkg_name => 'OKC_PHI_PVT',
1387 p_procedure_name => 'extend_price_hold',
1388 p_error_text => x_msg_data
1389 );
1390 END LOOP;
1391
1392 if c_k_header%ISOPEN then
1393 CLOSE c_k_header;
1394 end if;
1395
1396 if c_ph_header%ISOPEN then
1397 CLOSE c_ph_header;
1398 end if;
1399
1400 if c_ph_lines%ISOPEN then
1401 CLOSE c_ph_lines;
1402 end if;
1403
1404 if c_ph_line_break%ISOPEN then
1405 CLOSE c_ph_line_break;
1406 end if;
1407
1408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1409
1410
1411 WHEN OTHERS THEN
1412
1413 FOR i in 1..x_msg_count LOOP
1414 x_msg_data := oe_msg_pub.get(p_msg_index => i,
1415 p_encoded => 'F'
1416 );
1417
1418 FND_MSG_PUB.Add_Exc_Msg (
1419 p_pkg_name => 'OKC_PHI_PVT',
1420 p_procedure_name => 'extend_price_hold',
1421 p_error_text => x_msg_data
1422 );
1423 END LOOP;
1424
1425 if c_k_header%ISOPEN then
1426 CLOSE c_k_header;
1427 end if;
1428
1429 if c_ph_header%ISOPEN then
1430 CLOSE c_ph_header;
1431 end if;
1432
1433 if c_ph_lines%ISOPEN then
1434 CLOSE c_ph_lines;
1435 end if;
1436
1437 if c_ph_line_break%ISOPEN then
1438 CLOSE c_ph_line_break;
1439 end if;
1440
1441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1442
1443 END;
1444
1445
1446 -- This procedure copies Not Price Hold contract lines as sublines for Price Hold TopLine
1447 PROCEDURE COPY_LINES(
1448 p_api_version IN NUMBER,
1449 p_init_msg_list IN VARCHAR2,
1450 x_return_status OUT NOCOPY VARCHAR2,
1451 x_msg_count OUT NOCOPY NUMBER,
1452 x_msg_data OUT NOCOPY VARCHAR2,
1453 p_chr_id IN NUMBER, -- Contract Header ID
1454 p_cle_id in number, -- Price Hold TopLine ID
1455 p_restricted_update in VARCHAR2,
1456 p_delete_before_yn in VARCHAR2 , -- delete current lines before copying
1457 p_commit_changes_yn in VARCHAR2 , -- commit changes after copying
1458 x_recs_copied OUT NOCOPY NUMBER) IS
1459 l_cnt NUMBER := 1;
1460 m_cnt NUMBER := 0;
1461 top_line_id NUMBER := p_cle_id;
1462 top_PRICE_LIST_ID NUMBER;
1463 top_PH_PRICING_TYPE VARCHAR2(50);
1464 top_PH_ADJUSTMENT NUMBER;
1465 l_unit_price NUMBER;
1466 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1467
1468 CURSOR c_cur_ph_lines(p_par_id NUMBER) IS
1469 select id
1470 from okc_k_lines_v
1471 where cle_id = p_par_id;
1472
1473 CURSOR c_top_lines(p_chr_id NUMBER) IS
1474 select id
1475 from okc_k_lines_v
1476 where chr_id=p_chr_id and item_to_price_yn='Y'
1477 order by DISPLAY_SEQUENCE;
1478
1479 CURSOR c_top_ph_line(p_cle_id NUMBER) IS
1480 select
1481 PRICE_LIST_ID,
1482 PH_PRICING_TYPE,
1483 PH_ADJUSTMENT
1484 from okc_k_lines_v
1485 where id=p_cle_id;
1486
1487 x_clev_rec OKC_CONTRACT_PUB.clev_rec_type;
1488 n_clev_rec OKC_CONTRACT_PUB.clev_rec_type;
1489 xn_clev_rec OKC_CONTRACT_PUB.clev_rec_type;
1490
1491 x_cimv_rec OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1492 n_cimv_rec OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1493 xn_cimv_rec OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1494
1495 l_data_found BOOLEAN := TRUE;
1496
1497 CURSOR c_clev_rec (p_cle_id number) IS
1498 SELECT '' ID,
1499 SFWT_FLAG,
1500 '' CHR_ID,
1501 DNZ_CHR_ID,
1502 CLE_ID,
1503 '' LINE_NUMBER,
1504 62 LSE_ID,
1505 STS_CODE,
1506 '' DISPLAY_SEQUENCE,
1507 TRN_CODE,
1508 COMMENTS,
1509 ITEM_DESCRIPTION,
1510 OKE_BOE_DESCRIPTION,
1511 COGNOMEN,
1512 HIDDEN_IND,
1513 PRICE_UNIT,
1514 PRICE_UNIT_PERCENT,
1515 '' PRICE_NEGOTIATED,
1516 PRICE_LEVEL_IND,
1517 INVOICE_LINE_LEVEL_IND,
1518 DPAS_RATING,
1519 BLOCK23TEXT,
1520 EXCEPTION_YN,
1521 TEMPLATE_USED,
1522 DATE_TERMINATED,
1523 NAME,
1524 START_DATE,
1525 END_DATE,
1526 DATE_RENEWED,
1527 REQUEST_ID,
1528 PROGRAM_APPLICATION_ID,
1529 PROGRAM_ID,
1530 PROGRAM_UPDATE_DATE,
1531 PRICE_LIST_ID,
1532 PRICING_DATE,
1533 PRICE_LIST_LINE_ID,
1534 LINE_LIST_PRICE,
1535 ITEM_TO_PRICE_YN,
1536 PRICE_BASIS_YN,
1537 CONFIG_HEADER_ID,
1538 CONFIG_REVISION_NUMBER,
1539 CONFIG_COMPLETE_YN,
1540 CONFIG_VALID_YN,
1541 CONFIG_TOP_MODEL_LINE_ID,
1542 CONFIG_ITEM_TYPE,
1543 CONFIG_ITEM_ID,
1544 ATTRIBUTE_CATEGORY,
1545 ATTRIBUTE1,
1546 ATTRIBUTE2,
1547 ATTRIBUTE3,
1548 ATTRIBUTE4,
1549 ATTRIBUTE5,
1550 ATTRIBUTE6,
1551 ATTRIBUTE7,
1552 ATTRIBUTE8,
1553 ATTRIBUTE9,
1554 ATTRIBUTE10,
1555 ATTRIBUTE11,
1556 ATTRIBUTE12,
1557 ATTRIBUTE13,
1558 ATTRIBUTE14,
1559 ATTRIBUTE15,
1560 PRICE_TYPE,
1561 CURRENCY_CODE,
1562 SERVICE_ITEM_YN,
1563 -- new columns for price hold
1564 PH_PRICING_TYPE,
1565 PH_ADJUSTMENT,
1566 PH_PRICE_BREAK_BASIS,
1567 PH_MIN_QTY,
1568 PH_MIN_AMT,
1569 PH_QP_REFERENCE_ID,
1570 PH_VALUE,
1571 PH_ENFORCE_PRICE_LIST_YN,
1572 PH_INTEGRATED_WITH_QP
1573 FROM OKC_K_LINES_V
1574 WHERE id = p_cle_id;
1575
1576 CURSOR c_cimv_rec(p_cle_id NUMBER) IS
1577 SELECT ID,
1578 CLE_ID,
1579 CHR_ID,
1580 CLE_ID_FOR,
1581 DNZ_CHR_ID,
1582 OBJECT1_ID1,
1583 OBJECT1_ID2,
1584 'OKX_SYSITEM' JTOT_OBJECT1_CODE,
1585 UOM_CODE,
1586 EXCEPTION_YN,
1587 1 NUMBER_OF_ITEMS,
1588 'N' PRICED_ITEM_YN
1589 FROM OKC_K_ITEMS_V
1590 WHERE CLE_ID = p_cle_id;
1591
1592 BEGIN
1593 Fnd_Msg_Pub.Initialize;
1594
1595 IF Nvl(p_delete_before_yn,'N') = 'Y' THEN
1596 FOR crec IN c_cur_ph_lines( top_line_id ) LOOP
1597 okc_contract_pub.delete_contract_line (
1598 p_api_version => p_api_version,
1599 p_init_msg_list => p_init_msg_list,
1600 x_return_status => l_return_status,
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data,
1603 p_line_id => crec.id
1604 );
1605 END LOOP;
1606 END IF;
1607
1608 OPEN c_top_ph_line( top_line_id ) ;
1609 FETCH c_top_ph_line
1610 INTO top_PRICE_LIST_ID, top_PH_PRICING_TYPE, top_PH_ADJUSTMENT;
1611 l_data_found := c_top_ph_line%FOUND;
1612 CLOSE c_top_ph_line;
1613
1614 select Greatest(Nvl(Max(DISPLAY_SEQUENCE),0),Nvl(Max(LINE_NUMBER),0))
1615 INTO m_cnt
1616 from okc_k_lines_v
1617 where cle_id = top_line_id;
1618
1619 FOR crec IN c_top_lines( p_chr_id ) LOOP
1620 x_clev_rec := n_clev_rec;
1621 OPEN c_clev_rec ( crec.id );
1622 FETCH c_clev_rec
1623 INTO x_clev_rec.ID,
1624 x_clev_rec.SFWT_FLAG,
1625 x_clev_rec.CHR_ID,
1626 x_clev_rec.DNZ_CHR_ID,
1627 x_clev_rec.CLE_ID,
1628 x_clev_rec.LINE_NUMBER,
1629 x_clev_rec.LSE_ID,
1630 x_clev_rec.STS_CODE,
1631 x_clev_rec.DISPLAY_SEQUENCE,
1632 x_clev_rec.TRN_CODE,
1633 x_clev_rec.COMMENTS,
1634 x_clev_rec.ITEM_DESCRIPTION,
1635 x_clev_rec.OKE_BOE_DESCRIPTION,
1636 x_clev_rec.COGNOMEN,
1637 x_clev_rec.HIDDEN_IND,
1638 x_clev_rec.PRICE_UNIT,
1639 x_clev_rec.PRICE_UNIT_PERCENT,
1640 x_clev_rec.PRICE_NEGOTIATED,
1641 x_clev_rec.PRICE_LEVEL_IND,
1642 x_clev_rec.INVOICE_LINE_LEVEL_IND,
1643 x_clev_rec.DPAS_RATING,
1644 x_clev_rec.BLOCK23TEXT,
1645 x_clev_rec.EXCEPTION_YN,
1646 x_clev_rec.TEMPLATE_USED,
1647 x_clev_rec.DATE_TERMINATED,
1648 x_clev_rec.NAME,
1649 x_clev_rec.START_DATE,
1650 x_clev_rec.END_DATE,
1651 x_clev_rec.DATE_RENEWED,
1652 x_clev_rec.REQUEST_ID,
1653 x_clev_rec.PROGRAM_APPLICATION_ID,
1654 x_clev_rec.PROGRAM_ID,
1655 x_clev_rec.PROGRAM_UPDATE_DATE,
1656 x_clev_rec.PRICE_LIST_ID,
1657 x_clev_rec.PRICING_DATE,
1658 x_clev_rec.PRICE_LIST_LINE_ID,
1659 x_clev_rec.LINE_LIST_PRICE,
1660 x_clev_rec.ITEM_TO_PRICE_YN,
1661 x_clev_rec.PRICE_BASIS_YN,
1662 x_clev_rec.CONFIG_HEADER_ID,
1663 x_clev_rec.CONFIG_REVISION_NUMBER,
1664 x_clev_rec.CONFIG_COMPLETE_YN,
1665 x_clev_rec.CONFIG_VALID_YN,
1666 x_clev_rec.CONFIG_TOP_MODEL_LINE_ID,
1667 x_clev_rec.CONFIG_ITEM_TYPE,
1668 x_clev_rec.CONFIG_ITEM_ID,
1669 x_clev_rec.ATTRIBUTE_CATEGORY,
1670 x_clev_rec.ATTRIBUTE1,
1671 x_clev_rec.ATTRIBUTE2,
1672 x_clev_rec.ATTRIBUTE3,
1673 x_clev_rec.ATTRIBUTE4,
1674 x_clev_rec.ATTRIBUTE5,
1675 x_clev_rec.ATTRIBUTE6,
1676 x_clev_rec.ATTRIBUTE7,
1677 x_clev_rec.ATTRIBUTE8,
1678 x_clev_rec.ATTRIBUTE9,
1679 x_clev_rec.ATTRIBUTE10,
1680 x_clev_rec.ATTRIBUTE11,
1681 x_clev_rec.ATTRIBUTE12,
1682 x_clev_rec.ATTRIBUTE13,
1683 x_clev_rec.ATTRIBUTE14,
1684 x_clev_rec.ATTRIBUTE15,
1685 x_clev_rec.PRICE_TYPE,
1686 x_clev_rec.CURRENCY_CODE,
1687 x_clev_rec.SERVICE_ITEM_YN,
1688 x_clev_rec.PH_PRICING_TYPE,
1689 x_clev_rec.PH_ADJUSTMENT,
1690 x_clev_rec.PH_PRICE_BREAK_BASIS,
1691 x_clev_rec.PH_MIN_QTY,
1692 x_clev_rec.PH_MIN_AMT,
1693 x_clev_rec.PH_QP_REFERENCE_ID,
1694 x_clev_rec.PH_VALUE,
1695 x_clev_rec.PH_ENFORCE_PRICE_LIST_YN,
1696 x_clev_rec.PH_INTEGRATED_WITH_QP;
1697 CLOSE c_clev_rec;
1698 -- retrieving line item
1699 x_cimv_rec := n_cimv_rec;
1700 OPEN c_cimv_rec( crec.ID );
1701 FETCH c_cimv_rec
1702 INTO x_cimv_rec.ID,
1703 x_cimv_rec.CLE_ID,
1704 x_cimv_rec.CHR_ID,
1705 x_cimv_rec.CLE_ID_FOR,
1706 x_cimv_rec.DNZ_CHR_ID,
1707 x_cimv_rec.OBJECT1_ID1,
1708 x_cimv_rec.OBJECT1_ID2,
1709 x_cimv_rec.JTOT_OBJECT1_CODE,
1710 x_cimv_rec.UOM_CODE,
1711 x_cimv_rec.EXCEPTION_YN,
1712 x_cimv_rec.NUMBER_OF_ITEMS,
1713 x_cimv_rec.PRICED_ITEM_YN;
1714 l_data_found := c_cimv_rec%FOUND;
1715 CLOSE c_cimv_rec;
1716
1717 IF l_data_found THEN
1718 x_clev_rec.PRICE_LIST_ID := top_PRICE_LIST_ID;
1719 x_clev_rec.PH_PRICING_TYPE := top_PH_PRICING_TYPE;
1720 x_clev_rec.PH_ADJUSTMENT := top_PH_ADJUSTMENT;
1721
1722 l_unit_price := OKC_PRICE_PUB.GET_UNIT_PRICE(
1723 p_price_list_id => top_PRICE_LIST_ID,
1724 p_inventory_item_id => x_cimv_rec.OBJECT1_ID1,
1725 p_uom_code => x_cimv_rec.UOM_CODE,
1726 p_cur_code => x_clev_rec.CURRENCY_CODE,
1727 p_qty => 1
1728 );
1729 IF l_unit_price IS NOT NULL THEN
1730 x_clev_rec.PRICE_UNIT := l_unit_price ;
1731 END IF;
1732
1733 If x_clev_rec.PH_PRICING_TYPE = 'DISCOUNT_PERCENT' THEN
1734 x_clev_rec.PH_VALUE := x_clev_rec.PH_ADJUSTMENT ;
1735 ELSIf x_clev_rec.PH_PRICING_TYPE = 'NEW_PRICE' THEN
1736 x_clev_rec.PH_VALUE := OKC_PRICE_PUB.ROUND_PRICE(
1737 x_clev_rec.PRICE_UNIT*(1-x_clev_rec.PH_ADJUSTMENT/100),
1738 x_clev_rec.CURRENCY_CODE
1739 ) ;
1740 ELSIf x_clev_rec.PH_PRICING_TYPE = 'DISCOUNT_AMOUNT' THEN
1741 x_clev_rec.PH_VALUE := OKC_PRICE_PUB.ROUND_PRICE(
1742 x_clev_rec.PRICE_UNIT*x_clev_rec.PH_ADJUSTMENT/100,
1743 x_clev_rec.CURRENCY_CODE
1744 ) ;
1745 END IF;
1746 x_clev_rec.cle_id := top_line_id ;
1747 x_clev_rec.line_number := m_cnt + l_cnt ;
1748 x_clev_rec.display_sequence := m_cnt + l_cnt ;
1749
1750 okc_contract_pub.create_contract_line (
1751 p_api_version => p_api_version,
1752 p_init_msg_list => p_init_msg_list,
1753 x_return_status => l_return_status,
1754 x_msg_count => x_msg_count,
1755 x_msg_data => x_msg_data,
1756 p_restricted_update => p_restricted_update,
1757 p_clev_rec => x_clev_rec,
1758 x_clev_rec => xn_clev_rec
1759 );
1760 IF( l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1761 x_cimv_rec.cle_id := xn_clev_rec.id;
1762 okc_contract_item_pub.create_contract_item (
1763 p_api_version => p_api_version,
1764 p_init_msg_list => p_init_msg_list,
1765 x_return_status => l_return_status,
1766 x_msg_count => x_msg_count,
1767 x_msg_data => x_msg_data,
1768 p_cimv_rec => x_cimv_rec,
1769 x_cimv_rec => xn_cimv_rec
1770 );
1771 END IF;
1772 l_cnt := l_cnt + 1 ;
1773 END IF;
1774
1775 IF( Nvl(p_commit_changes_yn, 'N') = 'Y' ) THEN
1776 IF( l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1777 commit;
1778 ELSE
1779 rollback;
1780 END IF;
1781 END IF;
1782
1783 END LOOP;
1784 x_recs_copied := l_cnt-1;
1785 x_msg_count := Fnd_Msg_Pub.Count_Msg;
1786 x_msg_data := Fnd_Msg_Pub.Get( p_msg_index => Fnd_Msg_Pub.G_FIRST, p_encoded => FND_API.G_FALSE );
1787 IF x_msg_count>0 THEN x_return_status := OKC_API.G_RET_STS_ERROR; END IF;
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 -- store SQL error message on message stack for caller
1791 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
1792 -- notify caller of an UNEXPECTED error
1793 x_msg_count := Fnd_Msg_Pub.Count_Msg;
1794 x_msg_data := Fnd_Msg_Pub.Get( p_msg_index => Fnd_Msg_Pub.G_FIRST, p_encoded => FND_API.G_FALSE );
1795 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1796 END COPY_LINES;
1797
1798 END OKC_PHI_PVT;