[Home] [Help]
PACKAGE BODY: APPS.OKC_OC_INT_CONFIG_PVT
Source
1 PACKAGE BODY OKC_OC_INT_CONFIG_PVT AS
2 /* $Header: OKCRCFGB.pls 120.0 2005/05/25 23:00:21 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ------------------------------------------------------------------------------
7 -- Procedure: create_config_sublines
8 -- Purpose: To create the sublines in contract corresponding to child lines of a Model Item in Order/quote.
9 -- One Model will have many Option class as child Item and Each Option classes have many standard items as there child.
10 -- All these Item will be maped to recursive line style which was choosen for Model line.
11 -- This procedure will call itself recursively untill all the Items in order/Contract are covered
12
13 -- In Parameters: P_source_inf_rec This Consists of 1.o_flag Flag to determine If Child needs to be created for Order Model line.
14 -- 2.q_flag Flag to determine If Child needs to be created for quote Model Line .
15 -- 3.line_id Line Id of Top Model Line from Order/Quote.
16 -- 4.line_numer Line number of quote/order Line
17 -- 5.object_number Order/Quote Number.
18 -- p_parent_clev_rec K Line record of Parent Line
19 -- p_parent_cimv_rec K Line Item Record of Prent Line.
20 -- p_line_inf_tab PL/SQL table to store Relationship of Contract Line and Order/quote line.
21
22 -- Out Parameters: x_return_status return Status
23 -------------------------------------------------------------------------------
24 PROCEDURE create_config_sublines(p_source_inf_rec IN SOURCE_INF_REC_TYPE,
25 p_parent_clev_rec IN OUT NOCOPY okc_contract_pub.clev_rec_type,
26 p_parent_cimv_rec IN okc_contract_item_pub.cimv_rec_type,
27 p_line_inf_tab IN OUT NOCOPY line_inf_tbl_type,
28 x_return_status OUT NOCOPY VARCHAR2
29 ) IS
30
31 -- Cursor to get Child reecord for one line in Order or Quote.This cursor only gets child just one level below the line.
32 CURSOR c_child_line_info(b_o_flag VARCHAR2,b_q_flag VARCHAR2,b_line_id NUMBER) IS
33 SELECT id1 line_id
34 ,header_id
35 ,ship_from_org_id organization_id -- Bug 2225305
36 ,line_number line_number
37 ,order_quantity_uom uom
38 ,ordered_quantity quantity
39 ,inventory_item_id
40 ,unit_list_price
41 ,price_list_id
42 ,unit_selling_price
43 ,config_header_id
44 ,config_rev_nbr
45 ,to_number(NULL) config_item_id
46 ,'Y' config_valid_yn
47 ,'Y' config_complete_yn
48 ,nvl(to_number(sort_order),0) seq -- Bug 2087912
49 FROM OKX_ORDER_LINES_V
50 WHERE b_o_flag = OKC_API.G_TRUE
51 AND link_to_line_id = b_line_id
52 AND item_type_code in ('OPTION','CLASS')
53 UNION ALL
54 SELECT a.id1 line_id
55 ,a.quote_header_id header_id
56 ,a.organization_id organization_id -- Bug 2225305
57 ,a.line_number line_number
58 ,uom_code uom
59 ,quantity quantity
60 ,inventory_item_id
61 ,a.line_list_price/decode(quantity,0,1,quantity) unit_list_price
62 ,a.price_list_id price_list_id
63 ,a.line_quote_price/decode(quantity,0,1,quantity) unit_selling_price
64 ,c.config_header_id config_header_id
65 ,c.config_revision_num config_rev_nbr
66 ,c.config_item_id config_item_id
67 ,c.valid_configuration_flag config_valid_yn
68 ,c.complete_configuration_flag config_complete_yn
69 ,a.line_number seq -- for sorting Bug 2087912
70 -- Changed refrence to ASO tables to OKX views
71 FROM OKX_QUOTE_LINES_V a,OKX_QTE_LINE_RLSHPS_V b,OKX_QUOTE_LINE_DETAIL_V c
72 WHERE b_q_flag = OKC_API.G_TRUE
73 AND a.id1 = b.related_quote_line_id
74 AND a.id1 = c.quote_line_id
75 AND b.quote_line_id= b_line_id
76 AND b.relationship_type_code ='CONFIG'
77 AND a.item_type_code = 'CFG'
78 order by 16;
79
80 l_msg_count NUMBER;
81 l_msg_data VARCHAR2(2000);
82 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
83 child_line_rec c_child_line_info%ROWTYPE;
84 l_child_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
85 l_child_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
86 lx_child_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
87 lx_child_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
88 temp_child_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
89 temp_child_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
90 l_source_inf_rec SOURCE_INF_REC_TYPE;
91 l_subline_num NUMBER :=0;
92 line_inf_tab_counter NUMBER :=0;
93
94
95 no_child_left EXCEPTION;
96
97 BEGIN
98 IF (l_debug = 'Y') THEN
99 okc_util.print_trace(0, 'CREATE CHILD LINE FOR THE MODEL LINE ');
100 okc_util.print_trace(0, '===================================================');
101 okc_util.print_trace(1, ' ');
102 okc_util.print_trace(1, '>START - OKO_OC_INT_CONFGI_PVT.create_config_sublines - create Sublines');
103 okc_util.print_trace(1, ' ');
104 END IF;
105 x_return_status := OKC_API.G_RET_STS_SUCCESS;
106 l_child_cimv_rec := temp_child_cimv_rec;
107 l_child_clev_rec := temp_child_clev_rec;
108 l_subline_num := 0;
109 OPEN c_child_line_info(p_source_inf_rec.o_flag,p_source_inf_rec.q_flag,p_source_inf_rec.line_id);
110 LOOP
111 FETCH c_child_line_info into child_line_rec;
112 IF c_child_line_info%NOTFOUND then
113 close c_child_line_info;
114 -- No child are present at level below this line
115 raise no_child_left;
116 END IF;
117
118 l_subline_num := l_subline_num+1;
119 -- build up contract line
120 l_child_clev_rec.dnz_chr_id := P_PARENT_CLEV_REC.dnz_chr_id;
121 l_child_clev_rec.cle_id := P_PARENT_CLEV_REC.id;
122 l_child_clev_rec.lse_id := P_PARENT_CLEV_REC.lse_id;
123 --Bug 2222830
124 l_child_clev_rec.price_level_ind := P_PARENT_CLEV_REC.price_level_ind;
125 l_child_clev_rec.item_to_price_yn := P_PARENT_CLEV_REC.item_to_price_yn;
126 l_child_clev_rec.price_basis_yn := P_PARENT_CLEV_REC.price_basis_yn;
127 --End Bug 2222830
128 l_child_clev_rec.price_list_id := child_line_rec.price_list_id;
129 l_child_clev_rec.price_unit := child_line_rec.unit_list_price;
130 l_child_clev_rec.price_negotiated := nvl(child_line_rec.unit_selling_price,0)*nvl(child_line_rec.quantity,0);
131 l_child_clev_rec.line_list_price := nvl(child_line_rec.unit_list_price,0)*nvl(child_line_rec.quantity,0);
132 l_child_clev_rec.display_sequence := l_subline_num;
133 l_child_clev_rec.line_number := to_char(l_subline_num);
134 l_child_clev_rec.hidden_ind := 'N';
135 l_child_clev_rec.EXCEPTION_yn := 'N';
136 l_child_clev_rec.sts_code := P_PARENT_CLEV_REC.sts_code;
137 l_child_clev_rec.start_date := P_PARENT_CLEV_REC.start_date;
138 l_child_clev_rec.end_date := P_PARENT_CLEV_REC.end_date;
139 l_child_clev_rec.currency_code := P_PARENT_CLEV_REC.currency_code;
140 l_child_clev_rec.config_header_id := child_line_rec.config_header_id;
141 l_child_clev_rec.config_revision_number := child_line_rec.config_rev_nbr;
142 l_child_clev_rec.config_complete_yn := child_line_rec.config_complete_yn;
143 -- l_child_clev_rec.config_complete_yn := 'Y'; --GF: bugs#2440369,2440237
144 l_child_clev_rec.config_valid_yn := child_line_rec.config_valid_yn;
145 -- l_child_clev_rec.config_valid_yn := 'Y'; --GF: bugs#2440369,2440237
146 l_child_clev_rec.config_top_model_line_id := P_PARENT_CLEV_REC.config_top_model_line_id;
147 l_child_clev_rec.config_item_type := G_NORMAL_LINE;
148 l_child_clev_rec.config_item_id := child_line_rec.config_item_id; --BUG 1958006
149 l_child_clev_rec.last_update_login:=UID;
150 l_child_clev_rec.orig_system_id1 := child_line_rec.line_id;
151 l_child_clev_rec.orig_system_source_code := p_parent_clev_rec.orig_system_source_code;
152
153 -- Add prices to update final prices at TOP_MODEL_LINE level
154
155 -- rolledup_line_list_price := rolledup_line_list_price + l_child_clev_rec.price_negotiated ;
156 -- rolledup_price_negotiated := rolledup_price_negotiated + l_child_clev_rec.line_list_price ;
157
158 --2007583 Problem was becouse of price negoiated was getting added to list price and list price to proce negotiated.See above.Its being correcetd now
159 rolledup_line_list_price := rolledup_line_list_price + l_child_clev_rec.line_list_price;
160 rolledup_price_negotiated := rolledup_price_negotiated + l_child_clev_rec.price_negotiated;
161
162 IF (l_debug = 'Y') THEN
163 okc_util.print_trace(3, 'INPUT RECORD - Contract Line Record(Configurator Lines):');
164 okc_util.print_trace(3, '========================================');
165 okc_util.print_trace(4, 'Contract Line Number = '||l_child_clev_rec.line_number);
166 okc_util.print_trace(4, 'Contract Header Id = '||l_child_clev_rec.dnz_chr_id);
167 okc_util.print_trace(4, 'Contract Line Id = '||l_child_clev_rec.cle_id);
168 okc_util.print_trace(4, 'Line Style Id = '||l_child_clev_rec.lse_id);
169 okc_util.print_trace(4, 'Display Sequence = '||l_child_clev_rec.display_sequence);
170 okc_util.print_trace(4, 'Currency code = '||l_child_clev_rec.currency_code);
171 okc_util.print_trace(4, 'Price Level Ind. = '||l_child_clev_rec.price_level_ind);
172 okc_util.print_trace(4, 'Unit Price = '||l_child_clev_rec.price_unit);
173 okc_util.print_trace(4, 'Negotiated price = '||l_child_clev_rec.price_negotiated);
174 okc_util.print_trace(4, 'Hidden Ind. = '||l_child_clev_rec.hidden_ind);
175 okc_util.print_trace(4, 'EXCEPTION Y/N = '||l_child_clev_rec.exception_yn);
176 okc_util.print_trace(4, 'Status Code = '||l_child_clev_rec.sts_code);
177 okc_util.print_trace(4, 'Start Date = '||l_child_clev_rec.start_date);
178 okc_util.print_trace(4, 'End Date = '||l_child_clev_rec.END_date);
179 okc_util.print_trace(1, '----------------------------');
180 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
181 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
182 okc_util.print_trace(1, '----------------------------');
183 okc_util.print_trace(1, '--------------------------------------------------------');
184 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
185 END IF;
186
187 okc_contract_pub.create_contract_line(p_api_version => 1
188 ,p_init_msg_list => OKC_API.G_FALSE
189 ,x_return_status => l_return_status
190 ,x_msg_count => l_msg_count
191 ,x_msg_data => l_msg_data
192 ,p_clev_rec => l_child_clev_rec
193 ,x_clev_rec => lx_child_clev_rec
194 );
195
196 IF (l_debug = 'Y') THEN
197 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
198 okc_util.print_trace(1, '----------------------------');
199 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
200 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
201 okc_util.print_trace(1, '----------------------------');
202 END IF;
203
204 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
205
206 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
207 p_msg_name => 'OKC_CONFIGLINE',
208 p_token1 => 'ONUMBER',
209 p_token1_value => p_source_inf_rec.object_number,
210 p_token2 => 'OLNUMBER',
211 p_token2_value => p_source_inf_rec.line_number,
212 p_token3 => 'ITEM',
213 p_token3_value => child_line_rec.inventory_item_id,
214 p_token4 => 'LINETYPE',
215 p_token4_value => l_child_clev_rec.config_item_type
216 );
217 END IF;
218
219 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
220 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
221 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
222 RAISE OKC_API.G_EXCEPTION_ERROR;
223 END IF;
224
225 IF (l_debug = 'Y') THEN
226 okc_util.print_trace(3, 'OUTPUT RECORD - Contract Line Record:');
227 okc_util.print_trace(3, '=========================================');
228 okc_util.print_trace(3, 'Contract Header Id = '||lx_child_clev_rec.dnz_chr_id);
229 okc_util.print_trace(4, 'Contract Line Id = '||lx_child_clev_rec.id);
230 okc_util.print_trace(4, 'Line Number = '||lx_child_clev_rec.line_number);
231 okc_util.print_trace(4, 'Line Style Id = '||lx_child_clev_rec.lse_id);
232 END IF;
233
234
235 -- record mapping of contract line to Order/quote line for contract relationship
236
237 line_inf_tab_counter := p_line_inf_tab.count+1;
238 p_line_inf_tab(line_inf_tab_counter).object1_id1 := child_line_rec.line_id;
239 p_line_inf_tab(line_inf_tab_counter).line_type := G_NORMAL_LINE;
240 p_line_inf_tab(line_inf_tab_counter).cle_id := lx_child_clev_rec.id;
241 p_line_inf_tab(line_inf_tab_counter).line_num := lx_child_clev_rec.line_number;
242 p_line_inf_tab(line_inf_tab_counter).subline := 0;
243 p_line_inf_tab(line_inf_tab_counter).lse_id := lx_child_clev_rec.lse_id;
244 p_line_inf_tab(line_inf_tab_counter).line_qty := child_line_rec.quantity;
245 p_line_inf_tab(line_inf_tab_counter).line_uom := child_line_rec.uom;
246
247
248
249 IF (l_debug = 'Y') THEN
250 okc_util.print_trace(0, ' ');
251 okc_util.print_trace(0, '================================================');
252 okc_util.print_trace(0, 'CREATE CONTRACT LINE ITEM ');
253 okc_util.print_trace(0, '================================================');
254 okc_util.print_trace(0, ' ');
255 END IF;
256
257 -- create contract item
258 l_child_cimv_rec.cle_id := lx_child_clev_rec.id;
259 l_child_cimv_rec.dnz_chr_id := lx_child_clev_rec.dnz_chr_id;
260 l_child_cimv_rec.jtot_object1_code := p_parent_cimv_rec.jtot_object1_code;
261 l_child_cimv_rec.number_of_items := child_line_rec.quantity;
262 l_child_cimv_rec.uom_code := child_line_rec.uom;
263 l_child_cimv_rec.priced_item_yn := 'Y';
264 l_child_cimv_rec.EXCEPTION_yn := 'N';
265 l_child_cimv_rec.object1_id1 := child_line_rec.inventory_item_id;
266 l_child_cimv_rec.object1_id2 := child_line_rec.organization_id;
267
268 IF (l_debug = 'Y') THEN
269 okc_util.print_trace(3, 'INPUT RECORD - Contract Top Line Item Record:');
270 okc_util.print_trace(3, '=============================================');
271 okc_util.print_trace(4, 'Contract Line Id = '||l_child_cimv_rec.cle_id);
272 okc_util.print_trace(4, 'Dnz Contract Header Id= '||l_child_cimv_rec.dnz_chr_id);
273 okc_util.print_trace(4, 'Object1 Id1 = '||l_child_cimv_rec.object1_id1);
274 okc_util.print_trace(4, 'Object1 Id2 = '||l_child_cimv_rec.object1_id2);
275 okc_util.print_trace(4, 'Object Code = '||l_child_cimv_rec.jtot_object1_code);
276 okc_util.print_trace(4, 'No. of Items = '||l_child_cimv_rec.number_of_items);
277 okc_util.print_trace(4, 'UoM Code = '||l_child_cimv_rec.uom_code);
278 okc_util.print_trace(4, 'Item Priced = '||l_child_cimv_rec.priced_item_yn);
279 END IF;
280
281 -- insert contract item
282 IF (l_debug = 'Y') THEN
283 okc_util.print_trace(1, '----------------------------');
284 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
285 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
286 okc_util.print_trace(1, '----------------------------');
287 END IF;
288
289 IF (l_debug = 'Y') THEN
290 okc_util.print_trace(1, '--------------------------------------------------------');
291 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
292 END IF;
293
294 okc_contract_item_pub.create_contract_item(p_api_version => 1
295 ,p_init_msg_list => OKC_API.G_FALSE
296 ,x_return_status => l_return_status
297 ,x_msg_count => l_msg_count
298 ,x_msg_data => l_msg_data
299 ,p_cimv_rec => l_child_cimv_rec
300 ,x_cimv_rec => lx_child_cimv_rec
301 );
302
303 IF (l_debug = 'Y') THEN
304 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
305 okc_util.print_trace(1, '--------------------------------------------------------');
306 END IF;
307
308 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
309
310 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
311 p_msg_name => 'OKC_CONFIGITEM',
312 p_token1 => 'ONUMBER',
313 p_token1_value => p_source_inf_rec.object_number,
314 p_token2 => 'OLNUMBER',
315 p_token2_value => p_source_inf_rec.line_number,
316 p_token3 => 'ITEM',
317 p_token3_value => l_child_cimv_rec.object1_id1,
318 p_token4 => 'INVORG',
319 p_token4_value => l_child_cimv_rec.object1_id2,
320 p_token5 => 'LINETYPE',
321 p_token5_value => l_child_clev_rec.config_item_type
322 );
323 END IF;
324 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
325 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
326 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
327 RAISE OKC_API.G_EXCEPTION_ERROR;
328 END IF;
329
330 IF (l_debug = 'Y') THEN
331 okc_util.print_trace(3, 'OUTPUT RECORD - Contract Top Line Item Record:');
332 okc_util.print_trace(3, '==============================================');
333 okc_util.print_trace(3, 'Id = '||to_char(lx_child_cimv_rec.id));
334 okc_util.print_trace(3, 'Contract Line Id = '||lx_child_cimv_rec.cle_id);
335 okc_util.print_trace(3, 'Contract Header Id = '||to_char(lx_child_cimv_rec.chr_id));
336 okc_util.print_trace(3, 'Dnz Contract Header Id= '||lx_child_cimv_rec.dnz_chr_id);
337 okc_util.print_trace(3, 'Object1 Id1 = '||lx_child_cimv_rec.object1_id1);
338 okc_util.print_trace(3, 'Object1 Id2 = '||lx_child_cimv_rec.object1_id2);
339 okc_util.print_trace(3, 'Object Code = '||lx_child_cimv_rec.jtot_object1_code);
340 okc_util.print_trace(3, 'No. of Items = '||lx_child_cimv_rec.number_of_items);
341 okc_util.print_trace(3, 'UoM Code = '||lx_child_cimv_rec.uom_code);
342 okc_util.print_trace(3, 'EXCEPTION Y/N = '||lx_child_cimv_rec.exception_yn);
343 okc_util.print_trace(3, 'Priced Item Y/N = '||lx_child_cimv_rec.priced_item_yn);
344 END IF;
345
346 l_source_inf_rec := p_source_inf_rec;
347
348 -- This will ensure that in next call to this procedure child of this line are created
349 l_source_inf_rec.line_id := child_line_rec.line_id;
350
351 l_source_inf_rec.line_number := child_line_rec.line_number;
352
353 -- Call to Create Remaining childs of line being processed right now
354 create_config_sublines( p_source_inf_rec => l_source_inf_rec,
355 p_parent_clev_rec => lx_child_clev_rec,
356 p_parent_cimv_rec => lx_child_cimv_rec,
357 p_line_inf_tab => p_line_inf_tab,
358 x_return_status => l_return_status);
359
360 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
361 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
362 p_msg_name => 'OKC_CONFIGCREATE',
363 p_token1 => 'ONUMBER',
364 p_token1_value => p_source_inf_rec.object_number,
365 p_token2 => 'LINENUMBER',
366 p_token2_value => p_source_inf_rec.line_number,
367 p_token3 => 'ITEM',
368 p_token3_value => child_line_rec.inventory_item_id,
369 p_token4 => 'LINETYPE',
370 p_token4_value => G_NORMAL_LINE
371 );
372 END IF;
373
374 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
375 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
376 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
377 RAISE OKC_API.G_EXCEPTION_ERROR;
378 END IF;
379
380 END LOOP;
381
382 EXCEPTION
383 WHEN no_child_left THEN
384 NULL;
385 WHEN OTHERS THEN
386 -- store SQL error message on message stack for caller
387 OKC_API.set_message(OKC_API.G_APP_NAME,
388 'OKC_CONTRACTS_UNEXP_ERROR',
389 'SQLCODE',
390 SQLCODE,
391 'SQLERRM',
392 SQLERRM);
393 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
394 END create_config_sublines;
395
396
397 ------------------------------------------------------------------------------
398 -- Procedure: create_k_config_lines
399 -- Purpose: To create the lines in contract corresponding to lines of a Model Item in Order or Quote.
400 -- For Each Model Item line in quote and Order 2 Contract lines will be created.One Contract Line will be
401 -- having config_item_type_code ='TOP_MODEL_LINE' and other one which is child of this one will have
402 -- having config_item_type_code ='TOP_BASE_LINE'
403 --
404 -- This Procedure then calls create_config_sublines to transfer Option class and Option Item lines
405 -- from order or quote to Contract.
406
407 -- In Parameters: p_source_inf_rec It will contain info like order/quote header id and Flag stating that If this
408 -- proceedure is being called for Order or Contract.
409 -- p_clev_rec Contract Line rec of Top line
410 -- p_cimv_rec Contract Item rec of Top line
411 -- p_line_inf_tab PL/sql table to return relationship between K Line and Quote/Order Line
412 --
413 -- Out Parameters: x_return_status return Status
414 -- x_clev_rec Contract Line rec of Top line
415 --------------------------------------------------------------------------------
416 Procedure create_k_config_lines( p_source_inf_rec IN SOURCE_INF_REC_TYPE,
417 p_clev_rec IN OKC_CONTRACT_PUB.CLEV_REC_TYPE,
418 p_cimv_rec IN OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE,
419 p_line_inf_tab IN OUT NOCOPY LINE_INF_TBL_TYPE,
420 x_clev_rec OUT NOCOPY OKC_CONTRACT_PUB.CLEV_REC_TYPE,
421 x_return_status OUT NOCOPY VARCHAR2
422 ) IS
423
424 l_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
425 model_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
426 xmodel_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
427 lx_clev_rec OKC_CONTRACT_PUB.CLEV_REC_TYPE;
428 l_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
429 model_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
430 lx_cimv_rec OKC_CONTRACT_ITEM_PUB.CIMV_REC_TYPE;
431 line_inf_tab_counter NUMBER := 0;
432 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
433 l_msg_count NUMBER;
434 l_msg_data VARCHAR2(2000);
435
436 BEGIN
437
438 x_return_status := OKC_API.G_RET_STS_SUCCESS;
439
440 IF (l_debug = 'Y') THEN
441 okc_util.print_trace(0, 'Starting Configurator API');
442 okc_util.print_trace(0, ' ');
443 okc_util.print_trace(0, '>START - OKO_OC_INT_CONFGI_PVT.create_config_sublines - create Sublines');
444 okc_util.print_trace(1, 'CREATE TOP MODEL LINE');
445 okc_util.print_trace(1, '=====================');
446 END IF;
447
448 model_clev_rec := p_clev_rec;
449 model_cimv_rec := p_cimv_rec;
450
451 model_clev_rec.config_item_type := G_MODEL_LINE;
452 model_clev_rec.price_unit := NULL;
453 model_clev_rec.line_List_price := NULL;
454 model_clev_rec.price_negotiated := NULL;
455 IF (l_debug = 'Y') THEN
456 okc_util.print_trace(1, ' ');
457 okc_util.print_trace(1, 'INPUT RECORD - Contract Top Line Record:');
458 okc_util.print_trace(1, '========================================');
459 okc_util.print_trace(1, 'Contract Line Number = '||model_clev_rec.line_number);
460 okc_util.print_trace(1, 'Contract Header Id = '||model_clev_rec.chr_id);
461 okc_util.print_trace(1, 'Dnz Contract Header Id = '||model_clev_rec.dnz_chr_id);
462 okc_util.print_trace(1, 'Display Sequence = '||model_clev_rec.display_sequence);
463 okc_util.print_trace(1, 'Line Style Id = '||model_clev_rec.lse_id);
464 okc_util.print_trace(1, 'Currency code = '||model_clev_rec.currency_code);
465 okc_util.print_trace(1, 'Price Level Ind. = '||model_clev_rec.price_level_ind);
466 okc_util.print_trace(1, 'Unit Price = '||model_clev_rec.price_unit);
467 okc_util.print_trace(1, 'Negotiated price = '||model_clev_rec.price_negotiated);
468 okc_util.print_trace(1, 'Hidden Ind. = '||model_clev_rec.hidden_ind);
469 okc_util.print_trace(1, 'EXCEPTION Y/N = '||model_clev_rec.exception_yn);
470 okc_util.print_trace(1, 'Status Code = '||model_clev_rec.sts_code);
471 okc_util.print_trace(1, 'Start Date = '||model_clev_rec.start_date);
472 okc_util.print_trace(1, 'End Date = '||model_clev_rec.END_date);
473 okc_util.print_trace(1, 'Orig system source code = '||model_clev_rec.orig_system_source_code);
474 okc_util.print_trace(1, 'Orig system id = '||model_clev_rec.orig_system_id1);
475 okc_util.print_trace(1, 'Orig system reference = '||model_clev_rec.orig_system_reference1);
476 okc_util.print_trace(1, 'Config Header Id = '||model_clev_rec.config_header_id);
477 okc_util.print_trace(1, 'Config Revison Number = '||model_clev_rec.config_revision_number);
478 okc_util.print_trace(1, 'Config Item Type = '||model_clev_rec.config_item_type);
479 okc_util.print_trace(1, 'Config Complete = '||model_clev_rec.config_complete_yn);
480 okc_util.print_trace(1, 'Config valid = '||model_clev_rec.config_valid_yn);
481 okc_util.print_trace(1, '----------------------------');
482 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
483 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
484 okc_util.print_trace(1, '----------------------------');
485 okc_util.print_trace(1, '--------------------------------------------------------');
486 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
487 END IF;
488
489 okc_contract_pub.create_contract_line(p_api_version => 1
490 ,p_init_msg_list => OKC_API.G_FALSE
491 ,x_return_status => l_return_status
492 ,x_msg_count => l_msg_count
493 ,x_msg_data => l_msg_data
494 ,p_clev_rec => model_clev_rec
495 ,x_clev_rec => lx_clev_rec
496 );
497 xmodel_clev_rec := lx_clev_rec;
498 IF (l_debug = 'Y') THEN
499 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
500 okc_util.print_trace(1, '----------------------------');
501 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
502 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
503 okc_util.print_trace(1, '----------------------------');
504 END IF;
505
506 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
507
508 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
509 p_msg_name => 'OKC_CONFIGLINE',
510 p_token1 => 'ONUMBER',
511 p_token1_value => p_source_inf_rec.object_number,
512 p_token2 => 'OLNUMBER',
513 p_token2_value => p_source_inf_rec.line_number,
514 p_token3 => 'ITEM',
515 p_token3_value => p_cimv_rec.object1_id1,
516 p_token4 => 'LINETYPE',
517 p_token4_value => model_clev_rec.config_item_type
518 );
519 END IF;
520 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
521 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
522 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
523 RAISE OKC_API.G_EXCEPTION_ERROR;
524 END IF;
525
526 line_inf_tab_counter := p_line_inf_tab.count+1;
527 p_line_inf_tab(line_inf_tab_counter).object1_id1 := lx_clev_rec.orig_system_id1;
528 p_line_inf_tab(line_inf_tab_counter).line_type := G_MODEL_LINE;
529 p_line_inf_tab(line_inf_tab_counter).cle_id := lx_clev_rec.id;
530 p_line_inf_tab(line_inf_tab_counter).line_num := lx_clev_rec.line_number;
531 p_line_inf_tab(line_inf_tab_counter).subline := 0;
532 p_line_inf_tab(line_inf_tab_counter).lse_id := lx_clev_rec.lse_id;
533 p_line_inf_tab(line_inf_tab_counter).line_qty := model_cimv_rec.number_of_items;
534 p_line_inf_tab(line_inf_tab_counter).line_uom := model_cimv_rec.uom_code;
535
536 IF (l_debug = 'Y') THEN
537 okc_util.print_trace(1, 'OUTPUT RECORD - Contract Top Line Record:');
538 okc_util.print_trace(1, '=========================================');
539 okc_util.print_trace(1, 'Contract Header Id = '||to_char(lx_clev_rec.chr_id));
540 okc_util.print_trace(1, 'Contract DNZ Header Id= '||to_char(lx_clev_rec.dnz_chr_id));
541 okc_util.print_trace(1, 'Contract Line Id = '||lx_clev_rec.cle_id);
542 okc_util.print_trace(1, 'Line Number = '||lx_clev_rec.line_number);
543 okc_util.print_trace(1, 'Line Style Id = '||lx_clev_rec.lse_id);
544 okc_util.print_trace(1, '-->Order Line Id = '||lx_clev_rec.orig_system_id1);
545 END IF;
546
547 IF (l_debug = 'Y') THEN
548 okc_util.print_trace(1, ' ');
549 okc_util.print_trace(1, ' ');
550 okc_util.print_trace(1, '================================================');
551 okc_util.print_trace(1, 'CREATE CONTRACT LINE ITEM ');
552 okc_util.print_trace(1, '================================================');
553 okc_util.print_trace(1, ' ');
554 END IF;
555
556 -- create contract item
557 model_cimv_rec.cle_id := lx_clev_rec.id;
558 model_cimv_rec.dnz_chr_id := lx_clev_rec.chr_id;
559
560 IF (l_debug = 'Y') THEN
561 okc_util.print_trace(1, 'INPUT RECORD - Contract Top Line Item Record:');
562 okc_util.print_trace(1, '=============================================');
563 okc_util.print_trace(1, 'Contract Line Id = '||model_cimv_rec.cle_id);
564 okc_util.print_trace(1, 'Dnz Contract Header Id= '||model_cimv_rec.dnz_chr_id);
565 okc_util.print_trace(1, 'Object1 Id1 = '||model_cimv_rec.object1_id1);
566 okc_util.print_trace(1, 'Object1 Id2 = '||model_cimv_rec.object1_id2);
567 okc_util.print_trace(1, 'Object Code = '||model_cimv_rec.jtot_object1_code);
568 okc_util.print_trace(1, 'No. of Items = '||model_cimv_rec.number_of_items);
569 okc_util.print_trace(1, 'UoM Code = '||model_cimv_rec.uom_code);
570 okc_util.print_trace(1, 'Item Priced = '||model_cimv_rec.priced_item_yn);
571 END IF;
572
573 -- insert contract item
574 IF (l_debug = 'Y') THEN
575 okc_util.print_trace(1, '----------------------------');
576 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
577 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
578 okc_util.print_trace(1, '----------------------------');
579 END IF;
580
581 IF (l_debug = 'Y') THEN
582 okc_util.print_trace(1, '--------------------------------------------------------');
583 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
584 END IF;
585
586 okc_contract_item_pub.create_contract_item(p_api_version => 1
587 ,p_init_msg_list => OKC_API.G_FALSE
588 ,x_return_status => l_return_status
589 ,x_msg_count => l_msg_count
590 ,x_msg_data => l_msg_data
591 ,p_cimv_rec => model_cimv_rec
592 ,x_cimv_rec => lx_cimv_rec
593 );
594
595 IF (l_debug = 'Y') THEN
596 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
597 okc_util.print_trace(1, '--------------------------------------------------------');
598 okc_util.print_trace(1, '----------------------------');
599 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
600 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
601 okc_util.print_trace(1, '----------------------------');
602 END IF;
603
604 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
605 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
606 p_msg_name => 'OKC_CONFIGLINE',
607 p_token1 => 'ONUMBER',
608 p_token1_value => p_source_inf_rec.object_number,
609 p_token2 => 'OLNUMBER',
610 p_token2_value => p_source_inf_rec.line_number,
611 p_token3 => 'INVITEMID',
612 p_token3_value => model_cimv_rec.object1_id1,
613 p_token4 => 'INVORG',
614 p_token4_value => model_cimv_rec.object1_id2,
615 p_token5 => 'LINETYPE',
616 p_token5_value => model_clev_rec.config_item_type
617 );
618 END IF;
619 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
620 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
621 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
622 RAISE OKC_API.G_EXCEPTION_ERROR;
623 END IF;
624
625 IF (l_debug = 'Y') THEN
626 okc_util.print_trace(1, 'OUTPUT RECORD - Contract Top Line Item Record:');
627 okc_util.print_trace(1, '==============================================');
628 okc_util.print_trace(1, 'Id = '||to_char(lx_cimv_rec.id));
629 okc_util.print_trace(1, 'Contract Line Id = '||lx_cimv_rec.cle_id);
630 okc_util.print_trace(1, 'Contract Header Id = '||to_char(lx_cimv_rec.chr_id));
631 okc_util.print_trace(1, 'Dnz Contract Header Id= '||lx_cimv_rec.dnz_chr_id);
632 okc_util.print_trace(1, 'Contract Line Id For = '||to_char(lx_cimv_rec.cle_id_for));
633 okc_util.print_trace(1, 'Object1 Id1 = '||lx_cimv_rec.object1_id1);
634 okc_util.print_trace(1, 'Object1 Id2 = '||lx_cimv_rec.object1_id2);
635 okc_util.print_trace(1, 'Object Code = '||lx_cimv_rec.jtot_object1_code);
636 okc_util.print_trace(1, 'No. of Items = '||lx_cimv_rec.number_of_items);
637 okc_util.print_trace(1, 'UoM Code = '||lx_cimv_rec.uom_code);
638 okc_util.print_trace(1, 'EXCEPTION Y/N = '||lx_cimv_rec.exception_yn);
639 okc_util.print_trace(1, 'Priced Item Y/N = '||lx_cimv_rec.priced_item_yn);
640 END IF;
641
642 IF (l_debug = 'Y') THEN
643 okc_util.print_trace(1, ' ');
644 okc_util.print_trace(1, '================================================');
645 END IF;
646
647 -- Creating TOP_BASE_LINE which will be child of TOP MODEL LINE
648 IF (l_debug = 'Y') THEN
649 okc_util.print_trace(1, 'CREATE TOP BASE LINE');
650 okc_util.print_trace(1, '=====================');
651 END IF;
652
653 l_clev_rec := p_clev_rec;
654 l_cimv_rec := p_cimv_rec;
655
656 rolledup_price_negotiated := nvl(p_clev_rec.price_negotiated,0);
657 rolledup_line_list_price := nvl(p_clev_rec.line_list_price,0);
658
659 l_clev_rec.config_item_type := G_BASE_LINE;
660 l_clev_rec.config_top_model_line_id := lx_clev_rec.id;
661 l_clev_rec.chr_id := NULL;
662 -- l_clev_rec.dnz_chr_id := lx_clev_rec.dnz_chr_id;
663 l_clev_rec.cle_id := lx_clev_rec.id;
664 IF (l_debug = 'Y') THEN
665 okc_util.print_trace(1, ' ');
666 okc_util.print_trace(1, 'INPUT RECORD - Contract Top Line Record:');
667 okc_util.print_trace(1, '========================================');
668 okc_util.print_trace(1, 'Contract Line Number = '||l_clev_rec.line_number);
669 okc_util.print_trace(1, 'Contract Header Id = '||l_clev_rec.chr_id);
670 okc_util.print_trace(1, 'Dnz Contract Header Id = '||l_clev_rec.dnz_chr_id);
671 okc_util.print_trace(1, 'Display Sequence = '||l_clev_rec.display_sequence);
672 okc_util.print_trace(1, 'Line Style Id = '||l_clev_rec.lse_id);
673 okc_util.print_trace(1, 'Currency code = '||l_clev_rec.currency_code);
674 okc_util.print_trace(1, 'Price Level Ind. = '||l_clev_rec.price_level_ind);
675 okc_util.print_trace(1, 'Unit Price = '||l_clev_rec.price_unit);
676 okc_util.print_trace(1, 'Negotiated price = '||l_clev_rec.price_negotiated);
677 okc_util.print_trace(1, 'Hidden Ind. = '||l_clev_rec.hidden_ind);
678 okc_util.print_trace(1, 'EXCEPTION Y/N = '||l_clev_rec.exception_yn);
679 okc_util.print_trace(1, 'Status Code = '||l_clev_rec.sts_code);
680 okc_util.print_trace(1, 'Start Date = '||l_clev_rec.start_date);
681 okc_util.print_trace(1, 'End Date = '||l_clev_rec.END_date);
682 okc_util.print_trace(1, 'Orig system source code = '||l_clev_rec.orig_system_source_code);
683 okc_util.print_trace(1, 'Orig system id = '||l_clev_rec.orig_system_id1);
684 okc_util.print_trace(1, 'Orig system reference = '||l_clev_rec.orig_system_reference1);
685 okc_util.print_trace(1, 'Config Header Id = '||l_clev_rec.config_header_id);
686 okc_util.print_trace(1, 'Config Revison Number = '||l_clev_rec.config_revision_number);
687 okc_util.print_trace(1, 'Config Item Type = '||l_clev_rec.config_item_type);
688 okc_util.print_trace(1, 'Config Complete = '||l_clev_rec.config_complete_yn);
689 okc_util.print_trace(1, 'Config valid = '||l_clev_rec.config_valid_yn);
690 okc_util.print_trace(1, '----------------------------');
691 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
692 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
693 okc_util.print_trace(1, '----------------------------');
694 okc_util.print_trace(1, '--------------------------------------------------------');
695 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
696 END IF;
697
698 okc_contract_pub.create_contract_line(p_api_version => 1
699 ,p_init_msg_list => OKC_API.G_FALSE
700 ,x_return_status => l_return_status
701 ,x_msg_count => l_msg_count
702 ,x_msg_data => l_msg_data
703 ,p_clev_rec => l_clev_rec
704 ,x_clev_rec => lx_clev_rec
705 );
706
707 IF (l_debug = 'Y') THEN
708 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_PUB.CREATE_CONTRACT_LINE -');
709 okc_util.print_trace(1, '----------------------------');
710 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
711 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
712 okc_util.print_trace(1, '----------------------------');
713 END IF;
714
715 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
716
717 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
718 p_msg_name => 'OKC_CONFIGLINE',
719 p_token1 => 'ONUMBER',
720 p_token1_value => p_source_inf_rec.object_number,
721 p_token2 => 'OLNUMBER',
722 p_token2_value => p_source_inf_rec.line_number,
723 p_token3 => 'ITEM',
724 p_token3_value => l_cimv_rec.object1_id1,
725 p_token4 => 'LINETYPE',
726 p_token4_value => l_clev_rec.config_item_type
727 );
728 END IF;
729 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
731 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
732 RAISE OKC_API.G_EXCEPTION_ERROR;
733 END IF;
734
735 line_inf_tab_counter := p_line_inf_tab.count+1;
736 p_line_inf_tab(line_inf_tab_counter).object1_id1 := lx_clev_rec.orig_system_id1;
737 p_line_inf_tab(line_inf_tab_counter).line_type := G_BASE_LINE;
738 p_line_inf_tab(line_inf_tab_counter).cle_id := lx_clev_rec.id;
739
740 IF (l_debug = 'Y') THEN
741 okc_util.print_trace(1, 'OUTPUT RECORD - Contract Top Line Record:');
742 okc_util.print_trace(1, '=========================================');
743 okc_util.print_trace(1, 'Contract Header Id = '||to_char(lx_clev_rec.chr_id));
744 okc_util.print_trace(1, 'Contract DNZ Header Id= '||to_char(lx_clev_rec.dnz_chr_id));
745 okc_util.print_trace(1, 'Contract Line Id = '||lx_clev_rec.cle_id);
746 okc_util.print_trace(1, 'Line Number = '||lx_clev_rec.line_number);
747 okc_util.print_trace(1, 'Line Style Id = '||lx_clev_rec.lse_id);
748 okc_util.print_trace(1, '-->Order Line Id = '||lx_clev_rec.orig_system_id1);
749 END IF;
750
751 IF (l_debug = 'Y') THEN
752 okc_util.print_trace(1, ' ');
753 okc_util.print_trace(1, ' ');
754 okc_util.print_trace(1, '================================================');
755 okc_util.print_trace(1, 'CREATE CONTRACT LINE ITEM ');
756 okc_util.print_trace(1, '================================================');
757 okc_util.print_trace(1, ' ');
758 END IF;
759
760 -- create contract item
761 l_cimv_rec.cle_id := lx_clev_rec.id;
762 l_cimv_rec.dnz_chr_id := lx_clev_rec.dnz_chr_id;
763
764 IF (l_debug = 'Y') THEN
765 okc_util.print_trace(1, 'INPUT RECORD - Contract Top Line Item Record:');
766 okc_util.print_trace(1, '=============================================');
767 okc_util.print_trace(1, 'Contract Line Id = '||l_cimv_rec.cle_id);
768 okc_util.print_trace(1, 'Dnz Contract Header Id= '||l_cimv_rec.dnz_chr_id);
769 okc_util.print_trace(1, 'Object1 Id1 = '||l_cimv_rec.object1_id1);
770 okc_util.print_trace(1, 'Object1 Id2 = '||l_cimv_rec.object1_id2);
771 okc_util.print_trace(1, 'Object Code = '||l_cimv_rec.jtot_object1_code);
772 okc_util.print_trace(1, 'No. of Items = '||l_cimv_rec.number_of_items);
773 okc_util.print_trace(1, 'UoM Code = '||l_cimv_rec.uom_code);
774 okc_util.print_trace(1, 'Item Priced = '||l_cimv_rec.priced_item_yn);
775 END IF;
776
777 -- insert contract item
778 IF (l_debug = 'Y') THEN
779 okc_util.print_trace(1, '----------------------------');
780 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
781 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
782 okc_util.print_trace(1, '----------------------------');
783 END IF;
784
785 IF (l_debug = 'Y') THEN
786 okc_util.print_trace(1, '--------------------------------------------------------');
787 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
788 END IF;
789
790 okc_contract_item_pub.create_contract_item(p_api_version => 1
791 ,p_init_msg_list => OKC_API.G_FALSE
792 ,x_return_status => l_return_status
793 ,x_msg_count => l_msg_count
794 ,x_msg_data => l_msg_data
795 ,p_cimv_rec => l_cimv_rec
796 ,x_cimv_rec => lx_cimv_rec
797 );
798
799 IF (l_debug = 'Y') THEN
800 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_ITEM_PUB.CREATE_CONTRACT_ITEM -');
801 okc_util.print_trace(1, '--------------------------------------------------------');
802 okc_util.print_trace(1, '----------------------------');
803 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
804 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
805 okc_util.print_trace(1, '----------------------------');
806 END IF;
807
808 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
809 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
810 p_msg_name => 'OKC_CONFIGLINE',
811 p_token1 => 'ONUMBER',
812 p_token1_value => p_source_inf_rec.object_number,
813 p_token2 => 'OLNUMBER',
814 p_token2_value => p_source_inf_rec.line_number,
815 p_token3 => 'INVITEMID',
816 p_token3_value => l_cimv_rec.object1_id1,
817 p_token4 => 'INVORG',
818 p_token4_value => l_cimv_rec.object1_id2,
819 p_token5 => 'LINETYPE',
820 p_token5_value => l_clev_rec.config_item_type
821 );
822 END IF;
823 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
824 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
825 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
826 RAISE OKC_API.G_EXCEPTION_ERROR;
827 END IF;
828
829 IF (l_debug = 'Y') THEN
830 okc_util.print_trace(1, 'OUTPUT RECORD - Contract Top Line Item Record:');
831 okc_util.print_trace(1, '==============================================');
832 okc_util.print_trace(1, 'Id = '||to_char(lx_cimv_rec.id));
833 okc_util.print_trace(1, 'Contract Line Id = '||lx_cimv_rec.cle_id);
834 okc_util.print_trace(1, 'Contract Header Id = '||to_char(lx_cimv_rec.chr_id));
835 okc_util.print_trace(1, 'Dnz Contract Header Id= '||lx_cimv_rec.dnz_chr_id);
836 okc_util.print_trace(1, 'Contract Line Id For = '||to_char(lx_cimv_rec.cle_id_for));
837 okc_util.print_trace(1, 'Object1 Id1 = '||lx_cimv_rec.object1_id1);
838 okc_util.print_trace(1, 'Object1 Id2 = '||lx_cimv_rec.object1_id2);
839 okc_util.print_trace(1, 'Object Code = '||lx_cimv_rec.jtot_object1_code);
840 okc_util.print_trace(1, 'No. of Items = '||lx_cimv_rec.number_of_items);
841 okc_util.print_trace(1, 'UoM Code = '||lx_cimv_rec.uom_code);
842 okc_util.print_trace(1, 'EXCEPTION Y/N = '||lx_cimv_rec.exception_yn);
843 okc_util.print_trace(1, 'Priced Item Y/N = '||lx_cimv_rec.priced_item_yn);
844 END IF;
845
846 IF (l_debug = 'Y') THEN
847 okc_util.print_trace(1, ' ');
848 okc_util.print_trace(1, '================================================');
849 END IF;
850
851
852 -- Call to create Child Items for TOP_BASE_LINE
853 IF (l_debug = 'Y') THEN
854 okc_util.print_trace(1, ' ');
855 okc_util.print_trace(1, ' ');
856 okc_util.print_trace(1, '>START CREATING CHILD RECORDS FOR TOP BASE LINE');
857 okc_util.print_trace(1, '===============================================');
858 END IF;
859 create_config_sublines(p_source_inf_rec => p_source_inf_rec,
860 p_parent_clev_rec => lx_clev_rec ,
861 p_parent_cimv_rec => lx_cimv_rec,
862 p_line_inf_tab => p_line_inf_tab,
863 x_return_status => l_return_status
864 ) ;
865 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
866 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
867 p_msg_name => 'OKC_CONFIGCREATE',
868 p_token1 => 'ONUMBER',
869 p_token1_value => p_source_inf_rec.object_number,
870 p_token2 => 'LINENUMBER',
871 p_token2_value => p_source_inf_rec.line_number,
872 p_token3 => 'ITEM',
873 p_token3_value => lx_cimv_rec.object1_id1,
874 p_token4 => 'LINETYPE',
875 p_token4_value => G_NORMAL_LINE
876 );
877 END IF;
878 IF (l_debug = 'Y') THEN
879 okc_util.print_trace(1, '<END CREATING CHILD RECORDS FOR TOP BASE LINE');
880 okc_util.print_trace(1, '=============================================');
881 okc_util.print_trace(1, ' ');
882 okc_util.print_trace(1, ' ');
883 END IF;
884
885 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
886 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
887 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
888 RAISE OKC_API.G_EXCEPTION_ERROR;
889 END IF;
890
891 -- Call to Update Rolledup List Price and Rolled up negotiated Price
892 xmodel_clev_rec.line_list_price := rolledup_line_list_price;
893 xmodel_clev_rec.price_negotiated := rolledup_price_negotiated;
894 -- TO Update CONFIG_TOP_MODEL_LINE_ID on TOP_MODEL_LINE
895 xmodel_clev_rec.config_top_model_line_id := lx_clev_rec.config_top_model_line_id;
896 IF (l_debug = 'Y') THEN
897 okc_util.print_trace(1, ' ');
898 okc_util.print_trace(1, ' ');
899 okc_util.print_trace(1, '>START UPDATING TOP_MODEL_LINE ROLLEDUP PRICE');
900 okc_util.print_trace(1, '=============================================');
901 okc_util.print_trace(1, '-----------------------------------------------------------------');
902 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
903 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
904 okc_util.print_trace(1, '-----------------------------------------------------------------');
905 okc_util.print_trace(1, 'Rolled Up Line List Price : '||model_clev_rec.line_list_price );
906 okc_util.print_trace(1, 'Rolled Up Price Negotiated : '||model_clev_rec.price_negotiated);
907 okc_util.print_trace(1, '--------------------------------------------------------');
908 okc_util.print_trace(1, '>START - ******* OKC_CONTRACT_PUB.UPDATE_CONTRACT_LINE -');
909 END IF;
910
911 okc_contract_pub.update_contract_line(p_api_version => 1
912 ,p_init_msg_list => OKC_API.G_FALSE
913 ,x_return_status => l_return_status
914 ,x_msg_count => l_msg_count
915 ,x_msg_data => l_msg_data
916 ,p_clev_rec => xmodel_clev_rec
917 ,x_clev_rec => x_clev_rec
918 );
919
920
921 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR l_return_status = OKC_API.G_RET_STS_ERROR THEN
922
923 okc_api.set_message(p_app_name => OKC_API.G_APP_NAME,
924 p_msg_name => 'OKC_UPD_PRICE',
925 p_token1 => 'ONUMBER',
926 p_token1_value => p_source_inf_rec.object_number,
927 p_token2 => 'OLNUMBER',
928 p_token2_value => p_source_inf_rec.line_number
929 );
930 END IF;
931 IF (l_debug = 'Y') THEN
932 okc_util.print_trace(1, '<END - ******* OKC_CONTRACT_PUB.UPDATE_CONTRACT_LINE -');
933 okc_util.print_trace(1, '----------------------------');
934 okc_util.print_trace(1, 'Context org_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID'));
935 okc_util.print_trace(1, 'Context organization_id = '|| SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID'));
936 okc_util.print_trace(1, '----------------------------');
937 END IF;
938 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
941 RAISE OKC_API.G_EXCEPTION_ERROR;
942 END IF;
943
944 IF (l_debug = 'Y') THEN
945 okc_util.print_trace(0, 'Normal Exit From Configurator API');
946 END IF;
947 EXCEPTION
948 WHEN OTHERS THEN
949 -- store SQL error message on message stack for caller
950 OKC_API.set_message(OKC_API.G_APP_NAME,
951 'OKC_CONTRACTS_UNEXP_ERROR',
952 'SQLCODE',
953 SQLCODE,
954 'SQLERRM',
955 SQLERRM);
956 IF (l_debug = 'Y') THEN
957 okc_util.print_trace(0, 'Abnormal Exit From Configurator API');
958 END IF;
959 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
960 END create_k_config_lines;
961
962
963
964 -- =====================================================================================
965 --
966 -- Purpose: To build the relationship between quote lines which is
967 -- populated in the aso_line_relationships table
968 --
969 -- IN parameters: px_k2q_line_tbl - holds k to q relation
970 -- px_qte_line_tbl - holds quote line information
971 -- px_qte_line_dtl_tbl - holds quote line detail information
972 --
973 -- OUT parameters: x_line_rltship_tab - holds the information about relationship
974 -- between quote lines.
975 -- x_return_status - Return status of the procedure executed.
976 --
977 -- =====================================================================================
978
979 PROCEDURE quote_line_relationship( px_k2q_line_tbl IN line_rel_tab_type
980 ,px_qte_line_tbl IN ASO_QUOTE_PUB.qte_line_tbl_type
981 ,px_qte_line_dtl_tbl IN ASO_QUOTE_PUB.qte_line_dtl_tbl_type
982 ,x_line_rltship_tab OUT NOCOPY ASO_QUOTE_PUB.line_rltship_tbl_type
983 ,x_return_status OUT NOCOPY VARCHAR2
984 ) IS
985
986 i BINARY_INTEGER := 0;
987 k BINARY_INTEGER;
988 p BINARY_INTEGER := 0;
989
990 e_exit EXCEPTION;
991
992 l_config_rltship_code CONSTANT VARCHAR2(30) := 'CONFIG';
993 l_service_rltship_code CONSTANT VARCHAR2(30) := 'SERVICE';
994
995 CURSOR c_qte_line_rlt(b_rlted_qle_id IN NUMBER) IS
996 SELECT
997 quote_line_id,
998 line_relationship_id
999 FROM
1000 okx_qte_line_rlshps_v
1001 -- aso_line_relationships
1002 WHERE
1003 related_quote_line_id = b_rlted_qle_id
1004 AND relationship_type_code = 'CONFIG';
1005
1006 l_qte_line_rlt c_qte_line_rlt%ROWTYPE;
1007
1008 l_line_rltship_tab ASO_QUOTE_PUB.line_rltship_tbl_type;
1009
1010 BEGIN
1011
1012 IF (l_debug = 'Y') THEN
1013 okc_util.print_trace(2,' ');
1014 okc_util.print_trace(2,' START: OKC_OC_INT_CONFIG_PVT.quote_line_relationship ');
1015 okc_util.print_trace(2,' ');
1016 END IF;
1017
1018
1019 --
1020 -- housekeeping
1021 --
1022
1023 x_line_rltship_tab.DELETE;
1024 l_line_rltship_tab.DELETE;
1025
1026 --
1027
1028 IF px_k2q_line_tbl.FIRST IS NOT NULL THEN
1029 FOR i IN px_k2q_line_tbl.FIRST.. px_k2q_line_tbl.LAST LOOP
1030 IF (l_debug = 'Y') THEN
1031 okc_util.print_trace(2,' px_k2q_line_tbl('||i||').q_item_type_code = '||px_k2q_line_tbl(i).q_item_type_code);
1032 END IF;
1033
1034 IF px_k2q_line_tbl(i).q_item_type_code = g_aso_model_item THEN -- 'MDL'
1035 IF (l_debug = 'Y') THEN
1036 okc_util.print_trace(2,'Inside item type code = MDL check ');
1037 END IF;
1038 NULL; -- No relation has to be created for the Top model line
1039 ELSIF
1040 px_k2q_line_tbl(i).q_item_type_code = g_aso_config_item THEN -- 'CFG'
1041
1042 IF (l_debug = 'Y') THEN
1043 okc_util.print_trace(2,'Inside item type code = CFG check ');
1044 END IF;
1045 --
1046 -- Need to check the operation code of the config item
1047 --
1048 IF px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code = g_aso_op_code_create THEN
1049
1050 IF (l_debug = 'Y') THEN
1051 okc_util.print_trace(2,'Oper code of config item = '||px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code);
1052 END IF;
1053
1054 p := p + 1;
1055 l_line_rltship_tab(p).operation_code := g_aso_op_code_create;
1056 l_line_rltship_tab(p).related_qte_line_index := px_k2q_line_tbl(i).q_line_idx;
1057 l_line_rltship_tab(p).relationship_type_code := l_config_rltship_code; -- CONFIG
1058
1059 -- Need to get the parent line id by
1060 -- Looping through px_k2q_line_tbl to get the corresponding index.
1061 --
1062 FOR k in px_k2q_line_tbl.FIRST..px_k2q_line_tbl.LAST LOOP
1063
1064 IF px_k2q_line_tbl(k).k_line_id = px_k2q_line_tbl(i).k_parent_line_id THEN
1065
1066 -- Need to check on the operation code of the quote line of the parent line
1067
1068 IF (l_debug = 'Y') THEN
1069 okc_util.print_trace(2,'Checking on the oper code of parent line');
1070 END IF;
1071
1072 IF px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx).operation_code = g_aso_op_code_create THEN
1073
1074 l_line_rltship_tab(p).qte_line_index := px_k2q_line_tbl(k).q_line_idx;
1075
1076 IF (l_debug = 'Y') THEN
1077 okc_util.print_trace(2,'Oper code of parent line is CREATE - q_line_idx found');
1078 END IF;
1079 ELSE
1080 l_line_rltship_tab(p).quote_line_id := px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx).quote_line_id;
1081 IF (l_debug = 'Y') THEN
1082 okc_util.print_trace(2,'Oper code of parent line is UPDATE - q_line_id found');
1083 END IF;
1084
1085 END IF; -- IF px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx
1086
1087 EXIT;
1088 END IF; -- IF px_k2q_line_tbl(k).k_line_id
1089 END LOOP; -- FOR k in px_k2q_line_tbl.FIRST..
1090
1091 IF (l_debug = 'Y') THEN
1092 okc_util.print_trace(2,' ');
1093 okc_util.print_trace(2,' Values in the l_line_rltship_tab for the item type code CFG and operation code create is ');
1094 okc_util.print_trace(2,' ');
1095 okc_util.print_trace(2,'Operation code = '||l_line_rltship_tab(p).operation_code);
1096 okc_util.print_trace(2,'Rltd qte line idx= '||l_line_rltship_tab(p).related_qte_line_index);
1097 okc_util.print_trace(2,'Rltshp type code = '||l_line_rltship_tab(p).relationship_type_code);
1098 okc_util.print_trace(2,'Qte line index = '||l_line_rltship_tab(p).qte_line_index);
1099 okc_util.print_trace(2,'Qte line id = '||l_line_rltship_tab(p).quote_line_id);
1100 END IF;
1101
1102 ELSE -- px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code ( UPDATE )
1103
1104 IF (l_debug = 'Y') THEN
1105 okc_util.print_trace(2,'Oper code of config item UPDATE');
1106 END IF;
1107
1108 p := p + 1;
1109 --
1110 -- The operation code is not required here as it's been identified as an update
1111 --
1112 -- l_line_rltship_tab(p).operation_code := g_aso_op_code_create;
1113 l_line_rltship_tab(p).related_quote_line_id := px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).quote_line_id;
1114 l_line_rltship_tab(p).relationship_type_code := l_config_rltship_code;
1115
1116 --
1117 -- Need to get the parent line id by
1118 -- Looping through px_k2q_line_tbl to get the corresponding index.
1119 --
1120 FOR k in px_k2q_line_tbl.FIRST..px_k2q_line_tbl.LAST LOOP
1121
1122 IF px_k2q_line_tbl(k).k_line_id = px_k2q_line_tbl(i).k_parent_line_id THEN
1123
1124 -- Need to check on the operation code of the quote line of the parent line
1125
1126 IF px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx).operation_code = g_aso_op_code_create THEN
1127
1128 l_line_rltship_tab(p).qte_line_index := px_k2q_line_tbl(k).q_line_idx;
1129
1130 IF (l_debug = 'Y') THEN
1131 okc_util.print_trace(2,'Oper code of parent line is CREATE - q_line_idx found');
1132 END IF;
1133 ELSE
1134 l_line_rltship_tab(p).quote_line_id := px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx).quote_line_id;
1135 IF (l_debug = 'Y') THEN
1136 okc_util.print_trace(2,'Oper code of parent line is UPDATE - q_line_id found');
1137 END IF;
1138
1139 END IF; -- IF px_qte_line_tbl(px_k2q_line_tbl(k).q_line_idx
1140
1141 EXIT;
1142 END IF; -- IF px_k2q_line_tbl(k).k_line_id
1143 END LOOP; -- FOR k in px_k2q_line_tbl.FIRST..
1144
1145 IF (l_debug = 'Y') THEN
1146 okc_util.print_trace(2,' ');
1147 okc_util.print_trace(2,' Values in the l_line_rltship_tab for the item type code CFG and operation code update is ');
1148 okc_util.print_trace(2,' ');
1149 okc_util.print_trace(2,'Rltd qte line id = '||l_line_rltship_tab(p).related_quote_line_id);
1150 okc_util.print_trace(2,'Rltshp type code = '||l_line_rltship_tab(p).relationship_type_code);
1151 okc_util.print_trace(2,'Qte line index = '||l_line_rltship_tab(p).qte_line_index);
1152 okc_util.print_trace(2,'Qte line id = '||l_line_rltship_tab(p).quote_line_id);
1153 END IF;
1154 --
1155 -- Need to check the existance of a relationship in the ASO_LINE_RELATIONSHIPS table
1156 -- (i.e. okx_line_relationships_v)
1157 --
1158 OPEN c_qte_line_rlt(l_line_rltship_tab(p).related_quote_line_id);
1159 FETCH c_qte_line_rlt INTO l_qte_line_rlt;
1160 IF c_qte_line_rlt%FOUND THEN
1161
1162 IF l_qte_line_rlt.quote_line_id = l_line_rltship_tab(p).related_quote_line_id THEN
1163 --
1164 -- Delete the constructed record,because the entry has been found in the table
1165 -- and doesnot require any processing.
1166 --
1167 IF (l_debug = 'Y') THEN
1168 okc_util.print_trace(2,'Validated the existing relationship and deleting the ');
1169 okc_util.print_trace(2,'constructed entry, as no further processing is required');
1170 END IF;
1171
1172 l_line_rltship_tab.DELETE(p);
1173 ELSE
1174 --
1175 -- The record is in the table and it is for a different quote id
1176 -- and since it is an update,it is not valid and so raise an exception
1177 --
1178 IF (l_debug = 'Y') THEN
1179 okc_util.print_trace(2,'Rltn exists for a diff quote id, raising an exception');
1180 END IF;
1181
1182 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
1183 p_msg_name => 'OKC_UPD_LINERLTN',
1184 p_token1 => 'QUOTELINEID',
1185 p_token1_value => l_qte_line_rlt.quote_line_id,
1186 p_token2 => 'RLTDQTELINEID',
1187 p_token2_value => l_line_rltship_tab(p).related_quote_line_id,
1188 p_token3 => 'RLTSHPTYPECODE',
1189 p_token3_value => l_config_rltship_code
1190 );
1191 x_return_status := OKC_API.G_RET_STS_ERROR;
1192 RAISE e_exit;
1193 END IF;
1194 ELSE -- IF c_qte_line_rlt%FOUND
1195 --
1196 -- This is the case of an update and the record is not found
1197 -- Set an error message and raise an exception
1198 --
1199 IF (l_debug = 'Y') THEN
1200 okc_util.print_trace(2,'No rec found while trying to validate the rltnshp, raising an exception');
1201 END IF;
1202 OKC_API.set_message(p_app_name => OKC_API.G_APP_NAME,
1203 p_msg_name => 'OKC_UPD_LINERLTNOTFOUND',
1204 p_token1 => 'RLTDQTELINEID',
1205 p_token1_value => l_line_rltship_tab(p).related_quote_line_id,
1206 p_token2 => 'RLTSHPTYPECODE',
1207 p_token2_value => l_config_rltship_code
1208 );
1209 x_return_status := OKC_API.G_RET_STS_ERROR;
1210 RAISE e_exit;
1211 END IF; -- IF c_qte_line_rlt%FOUND
1212
1213 CLOSE c_qte_line_rlt;
1214
1215 END IF; -- px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code ( create or update )
1216
1217 ELSIF -- px_k2q_line_tbl(i).q_item_type_code -- CFG
1218
1219 px_k2q_line_tbl(i).q_item_type_code = g_aso_service_item THEN -- 'SRV' -- Bug 1970133
1220 IF (l_debug = 'Y') THEN
1221 okc_util.print_trace(2,'px_qte_line_tbl - operation code = '||px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code);
1222 END IF;
1223
1224 IF px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code = g_aso_op_code_create THEN
1225
1226 --
1227 -- Need to check the quote detail line to ensure that there is a covered line
1228 --
1229 -- Loop through the px_qte_line_dtl_tbl to retrieve the attached index k
1230 -- using px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx)
1231 --
1232 IF (l_debug = 'Y') THEN
1233 okc_util.print_trace(2,'Looping through through the px_qte_line_dtl_tbl to retrieve the attached QDL');
1234 END IF;
1235 IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
1236 FOR k IN px_qte_line_dtl_tbl.FIRST..px_qte_line_dtl_tbl.LAST LOOP
1237
1238 IF px_qte_line_dtl_tbl(k).qte_line_index = px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).line_number THEN
1239 --
1240 -- Need to ensure that qdl(k) has a covered line
1241 --
1242 IF px_qte_line_dtl_tbl(k).service_ref_type_code = okc_oc_int_ktq_pvt.g_qte_ref_quote THEN
1243 -- Now this is a covered line and hence the relationship needs to be created
1244 -- Need to use the index of the related quote line (service line).
1245
1246 IF NVL(px_qte_line_dtl_tbl(k).service_ref_qte_line_index,OKC_API.G_MISS_NUM) <>
1247 OKC_API.G_MISS_NUM THEN
1248 IF (l_debug = 'Y') THEN
1249 okc_util.print_trace(2,'Create the relationship for the cov line using index');
1250 END IF;
1251 p := p+1;
1252 l_line_rltship_tab(p).operation_code := g_aso_op_code_create;
1253 l_line_rltship_tab(p).related_qte_line_index := px_k2q_line_tbl(i).q_line_idx;
1254 l_line_rltship_tab(p).relationship_type_code := l_service_rltship_code; -- SERVICE
1255 l_line_rltship_tab(p).qte_line_index := px_qte_line_dtl_tbl(k).service_ref_qte_line_index;
1256
1257 IF (l_debug = 'Y') THEN
1258 okc_util.print_trace(2,'Quote line index = '||l_line_rltship_tab(p).qte_line_index);
1259 END IF;
1260
1261 ELSE -- ( update )need to use the id of the covered line
1262
1263 IF (l_debug = 'Y') THEN
1264 okc_util.print_trace(2,'Create the relationship for the cov line using id');
1265 END IF;
1266
1267 p := p+1;
1268 l_line_rltship_tab(p).operation_code := g_aso_op_code_create;
1269 l_line_rltship_tab(p).related_qte_line_index := px_k2q_line_tbl(i).q_line_idx;
1270 l_line_rltship_tab(p).relationship_type_code := l_service_rltship_code; -- SERVICE
1271 l_line_rltship_tab(p).quote_line_id := px_qte_line_dtl_tbl(k).service_ref_line_id;
1272
1273 IF (l_debug = 'Y') THEN
1274 okc_util.print_trace(2,'Quote line id = '||l_line_rltship_tab(p).quote_line_id);
1275 END IF;
1276
1277 END IF; -- IF NVL(px_qte_line_dtl_tbl...
1278
1279 IF (l_debug = 'Y') THEN
1280 okc_util.print_trace(2,'Operation code = '||l_line_rltship_tab(p).operation_code);
1281 okc_util.print_trace(2,'Rltd qte line idx = '||l_line_rltship_tab(p).related_qte_line_index);
1282 okc_util.print_trace(2,'rltshp type code = '||l_line_rltship_tab(p).relationship_type_code);
1283 END IF;
1284
1285 END IF; -- IF px_qte_line_dtl_tbl(k).service_ref_type_code = okc_oc_int_ktq_pvt.g_qte_ref_quote
1286
1287 EXIT;
1288
1289 END IF; -- px_qte_line_dtl_tbl(k).qte_line_index = px_qte_line_tbl(px
1290
1291 END LOOP; -- FOR k IN px_qte_line_dtl_tbl.FIRST..px_
1292
1293 END IF; -- IF px_qte_line_dtl_tbl.FIRST
1294
1295 ELSE -- px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code = g (update)
1296 --
1297 -- Need to check the quote detail line to ensure that there is a covered line
1298 --
1299 -- Loop through the px_qte_line_dtl_tbl to retrieve the attached index k
1300 -- using px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx)
1301 --
1302 IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
1303 FOR k IN px_qte_line_dtl_tbl.FIRST..px_qte_line_dtl_tbl.LAST LOOP
1304
1305 IF px_qte_line_dtl_tbl(k).qte_line_index = px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).line_number THEN
1306 --
1307 -- Need to ensure that qdl(k) has a covered line
1308 --
1309 IF px_qte_line_dtl_tbl(k).service_ref_type_code = okc_oc_int_ktq_pvt.g_qte_ref_quote THEN
1310
1311 -- Need to use the id of the related quote line (service line).
1312
1313 IF NVL(px_qte_line_dtl_tbl(k).service_ref_qte_line_index,OKC_API.G_MISS_NUM) <>
1314 OKC_API.G_MISS_NUM THEN
1315 p := p + 1;
1316 l_line_rltship_tab(p).qte_line_index := px_qte_line_dtl_tbl(k).service_ref_qte_line_index;
1317 IF (l_debug = 'Y') THEN
1318 okc_util.print_trace(2,'Update the relationship for the cov line using index');
1319 END IF;
1320 ELSE
1321 p := p + 1;
1322 l_line_rltship_tab(p).quote_line_id := px_qte_line_dtl_tbl(k).service_ref_line_id;
1323 IF (l_debug = 'Y') THEN
1324 okc_util.print_trace(2,'Update the relationship for the cov line using id');
1325 END IF;
1326 END IF;
1327
1328 --
1329 -- Need to check the existance of any relationship for the service line id
1330 --
1331 IF (l_debug = 'Y') THEN
1332 okc_util.print_trace(2,'Checking the existance of any relationship in okx_line_relationships_v ');
1333 END IF;
1334 OPEN c_qte_line_rlt(px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).quote_line_id);
1335 FETCH c_qte_line_rlt INTO l_qte_line_rlt;
1336 IF c_qte_line_rlt%NOTFOUND THEN
1337 l_line_rltship_tab(p).operation_code := g_aso_op_code_create;
1338 IF (l_debug = 'Y') THEN
1339 okc_util.print_trace(2,'Didnot find any relationship in okx_line_relationships_v, creating one ');
1340 END IF;
1341 ELSE
1342 l_line_rltship_tab(p).operation_code := g_aso_op_code_update;
1343 l_line_rltship_tab(p).line_relationship_id := l_qte_line_rlt.line_relationship_id;
1344 IF (l_debug = 'Y') THEN
1345 okc_util.print_trace(2,'Found a relationship in okx_line_relationships_v, updating');
1346 okc_util.print_trace(2,'l_line_rltship_tab('||p||').line_relationship_id = '||l_line_rltship_tab(p).line_relationship_id);
1347 END IF;
1348 END IF;
1349 CLOSE c_qte_line_rlt;
1350
1351 l_line_rltship_tab(p).related_quote_line_id := px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).quote_line_id;
1352 l_line_rltship_tab(p).relationship_type_code := l_service_rltship_code; -- SERVICE
1353 IF (l_debug = 'Y') THEN
1354 okc_util.print_trace(2,'l_line_rltship_tab('||p||').related_quote_line_id = '||l_line_rltship_tab(p).related_quote_line_id);
1355 okc_util.print_trace(2,'l_line_rltship_tab('||p||').relationship_type_code = '||l_line_rltship_tab(p).relationship_type_code);
1356 END IF;
1357
1358
1359 ELSE -- delete
1360 --
1361 -- Need to check the existance of any relationship for the service line id
1362 --
1363 OPEN c_qte_line_rlt(px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).quote_line_id);
1364 FETCH c_qte_line_rlt INTO l_qte_line_rlt;
1365 IF c_qte_line_rlt%FOUND THEN
1366 p := p+1;
1367 l_line_rltship_tab(p).operation_code := g_aso_op_code_delete;
1368 l_line_rltship_tab(p).line_relationship_id := l_qte_line_rlt.line_relationship_id;
1369 IF (l_debug = 'Y') THEN
1370 okc_util.print_trace(2,'Deleting the relationship ');
1371 okc_util.print_trace(2,'l_line_rltship_tab('||p||').line_relationship_id = '||l_line_rltship_tab(p).line_relationship_id);
1372 END IF;
1373
1374 END IF;
1375 CLOSE c_qte_line_rlt;
1376
1377 END IF; -- IF px_qte_line_dtl_tbl(k).service_ref_type_code = okc_oc_int_ktq_pvt.g_qte_ref_quote THEN
1378
1379 END IF; -- px_qte_line_dtl_tbl(k).qte_line_index = px_qte_li
1380
1381 END LOOP; -- FOR k IN px_qte_line_dtl_tbl.FIRST..px_q
1382
1383 END IF; -- IF px_qte_line_dtl_tbl.FIRST IS NOT NULL THEN
1384
1385 END IF; -- ELSE -- px_qte_line_tbl(px_k2q_line_tbl(i).q_line_idx).operation_code = g (update)
1386
1387 END IF; -- px_k2q_line_tbl(i).q_item_type_code -- MDL
1388 END LOOP; -- FOR i IN px_k2q_line_tbl.FIRST
1389 END IF; -- IF px_k2q_line_tbl.FIRST IS NOT NULL
1390
1391
1392 x_line_rltship_tab := l_line_rltship_tab;
1393
1394
1395 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1396
1397 EXCEPTION
1398
1399 WHEN e_exit THEN
1400 IF c_qte_line_rlt%ISOPEN THEN
1401 CLOSE c_qte_line_rlt;
1402 END IF;
1403
1404 WHEN OTHERS THEN
1405
1406 IF c_qte_line_rlt%ISOPEN THEN
1407 CLOSE c_qte_line_rlt;
1408 END IF;
1409
1410 OKC_API.set_message(G_APP_NAME,
1411 G_UNEXPECTED_ERROR,
1412 G_SQLCODE_TOKEN,
1413 SQLCODE,
1414 G_SQLERRM_TOKEN,
1415 SQLERRM);
1416 -- notify caller of an UNEXPECTED error
1417
1418 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1419
1420 END quote_line_relationship;
1421
1422 END OKC_OC_INT_CONFIG_PVT;