1 PACKAGE OKC_OC_INT_QTK_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKCRQTKS.pls 120.1 2005/10/04 18:24:53 smallya noship $ */
3
4 --
5 -- Global constants
6 --
7
8 g_support CONSTANT VARCHAR2(1) := 'S';
9
10 g_q2k_terms CONSTANT OKC_K_REL_OBJS.RTY_CODE%TYPE := 'CONTRACTISTERMSFORQUOTE';
11 g_q2k_neg CONSTANT OKC_K_REL_OBJS.RTY_CODE%TYPE := 'CONTRACTNEGOTIATESQUOTE';
12 g_q2k_ren CONSTANT OKC_K_REL_OBJS.RTY_CODE%TYPE := 'CONTRACTRENEWSQUOTE';
13 g_k2q_ren CONSTANT OKC_K_REL_OBJS.RTY_CODE%TYPE := 'QUOTERENEWSCONTRACT';
14
15 g_ctrol_type fnd_lookups.lookup_type%TYPE := 'OKC_CONTACT_ROLE';
16 g_admin_ctrol fnd_lookups.lookup_code%TYPE := 'ADMIN';
17 g_salesrep_ctrol fnd_lookups.lookup_code%TYPE := 'SALESPERSON'; --ABHAY: new
18
19 g_custcont_ctrol fnd_lookups.lookup_code%TYPE := 'CUST_CONTACT';
20
21 g_relobj_type fnd_lookups.lookup_type%TYPE := 'OKC_REL_OBJ';
22
23 g_chrsrc_type fnd_lookups.lookup_type%TYPE := 'OKC_CONTRACT_SOURCES';
24 g_ibe_hsrc fnd_lookups.lookup_code%TYPE := 'IBE_HDR';
25 g_ibe_lsrc fnd_lookups.lookup_code%TYPE := 'IBE_LINE';
26 g_aso_hsrc fnd_lookups.lookup_code%TYPE := 'ASO_HDR';
27 g_aso_lsrc fnd_lookups.lookup_code%TYPE := 'ASO_LINE';
28
29 g_jtot_qte_hdr CONSTANT VARCHAR2(30) := 'OKX_QUOTEHEAD';
30 g_jtot_qte_line CONSTANT VARCHAR2(30) := 'OKX_QUOTELINE';
31 g_entered CONSTANT VARCHAR2(30) := 'ENTERED' ;
35 g_invalid_value CONSTANT VARCHAR2(200):= OKC_API.G_INVALID_VALUE ;
32 g_approved CONSTANT VARCHAR2(30) := 'CONTRACT_APPROVED';----'APPROVED';
33 g_cancelled CONSTANT VARCHAR2(30) := 'CONTRACT_CANCELED';-----'CANCELLED';
34 g_rejected CONSTANT VARCHAR2(30) := 'CONTRACT_REJECTED';----'REJECTED';
36 g_col_name_token CONSTANT VARCHAR2(200):= OKC_API.G_COL_NAME_TOKEN ;
37
38 g_aso_model_item CONSTANT VARCHAR2(30) := 'MDL';
39 g_aso_config_item CONSTANT VARCHAR2(30) := 'CFG';
40 --added for configuration
41
42 --
43 -- Global variables
44 --
45
46 l_contract_number okc_k_headers_b.contract_number%TYPE;
47 l_contract_number_modifier okc_k_headers_b.contract_number_modifier%TYPE;
48 l_quote_number okx_quote_headers_v.quote_number%TYPE;
49 l_order_number okx_order_headers_v.order_number%TYPE;
50
51
52 -- type declaration to be used in quote-to-contract scenario
53 -- helps figure out what line style to use
54
55 TYPE line_style_rec_type IS RECORD (lty_code okc_line_styles_v.lty_code%TYPE
56 ,priced_yn okc_line_styles_v.priced_yn%TYPE
57 ,service_item_yn okc_line_styles_v.service_item_yn%TYPE -- GF bug=2291968
58
59 --new pricing columns
60 ,item_to_price_yn okc_line_styles_v.item_to_price_yn%TYPE
61 ,price_basis_yn okc_line_styles_v.price_basis_yn%TYPE
62
63 ,lse_id okc_line_styles_v.id%TYPE
64 ,lse_name okc_line_styles_v.name%TYPE
65 ,object_code jtf_objects_b.object_code%TYPE
66 ,where_clause jtf_objects_b.where_clause%TYPE
67 ,from_table jtf_objects_b.from_table%TYPE --Added by RG 04/20/2000
68
69 ,recursive_yn okc_line_styles_v.recursive_yn%TYPE
70 --added for configurator model line
71 );
72
73 TYPE line_style_tab_type IS TABLE OF line_style_rec_type INDEX BY BINARY_INTEGER;
74
75 --
76 -- sub line style record definition
77
78 TYPE sub_line_style_rec_type is RECORD (lty_code okc_line_styles_v.lty_code%TYPE
79 ,priced_yn okc_line_styles_v.priced_yn%TYPE
80 ,service_item_yn okc_line_styles_v.service_item_yn%TYPE -- GF bug=2291968
81 ,lse_id okc_line_styles_v.id%TYPE
82 ,lse_name okc_line_styles_v.name%TYPE
83 ,object_code jtf_objects_b.object_code%TYPE
84 ,from_table jtf_objects_b.from_table%TYPE
85 ,where_clause jtf_objects_b.where_clause%TYPE );
86
87 -- create pl/ql table to hold all sub linestyles for template/contract
88
89 TYPE sub_line_style_tab_type IS TABLE OF sub_line_style_rec_type INDEX BY BINARY_INTEGER;
90
91
92 -- needed to keep track of what contract line points to what index entry
93 -- in the table of to-be quote lines
94 TYPE line_rel_rec_type IS RECORD (k_line_id okc_k_lines_b.id%TYPE
95 ,tab_idx BINARY_INTEGER
96 );
97
98 TYPE line_rel_tab_type IS TABLE OF line_rel_rec_type INDEX BY BINARY_INTEGER;
99
100 G_MISS_KL_REL_TAB line_rel_tab_type;
101
102
103
104 -- needed to keep track of what contract line points to what index entry
105 -- in the table of to-be quote lines
106
107 -- Bug : 1686001 Changed references aso_quote_lines_v.quote_line_id to okx_quote_lines_v.id1
108 TYPE line_inf_rec IS RECORD(-----line_id okc_k_lines_b.id%TYPE
109 cle_id okc_k_lines_b.id%TYPE
110 ,lse_id okc_k_lines_b.lse_id%TYPE
111 ,lty_code okc_line_styles_b.lty_code%TYPE
112 ------,qte_line_id aso_quote_lines_all.quote_line_id%TYPE
113 ,object1_id1 okx_quote_lines_v.id1%TYPE
114 ,line_num NUMBER
115 ,subline NUMBER
116 ,line_qty okx_quote_lines_v.quantity%TYPE
117 ,line_uom okx_quote_lines_v.uom_code%TYPE
118 ,line_type okc_k_lines_b.config_item_type%TYPE
119 );
120 TYPE line_inf_tab IS TABLE OF line_inf_rec INDEX BY BINARY_INTEGER;
121
122
123
124 -------------------------------------------------------------------------------
125 -- Procedure: create_k_from_quote
126 -- Version: 1.0
127 -- Purpose: Create a contract from a quote.
128 -- Create relationships from quote to contract
129 -- The quote to contract process should not be used to create a-- service contract (OKS).
130 -- It should be used to create a new (not renewed) Core
131 -- contract from a quote.
132 -- In Parameters: p_quote_id Quote for which to create contract
133 -- p_template_id Template contract to use in creating Sales K
134 -- p_rel_type Q-Sales K relationship type to be used
135 -- p_terms_agreed_flag Flag to indicate if contract has to be
136 -- created as signed (buyer agreed) or as
140 -------------------------------------------------------------------------------
137 -- entered
138 -- Out Parameters: x_contract_id Id of created contract
139 -- x_contract_number Number of created contract
141
142 -- Bug : 1686001 Changed references aso_quote_headers_v.quote_header_id to okx_quote_headers_v.id1
143 PROCEDURE create_k_from_quote(p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
144 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
145 ,p_quote_id IN OKX_QUOTE_HEADERS_V.id1%TYPE
146 ,p_template_id IN OKC_K_HEADERS_B.ID%TYPE
147 ,p_template_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
148 ,p_rel_type IN OKC_K_REL_OBJS.RTY_CODE%TYPE
149 ,p_terms_agreed_flag IN VARCHAR2 DEFAULT OKC_API.G_FALSE
150 ,x_contract_id OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE
151 ,x_contract_number OUT NOCOPY OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE
152 ,x_return_status OUT NOCOPY VARCHAR2
153 ,x_msg_count OUT NOCOPY NUMBER
154 ,x_msg_data OUT NOCOPY VARCHAR2
155 );
156
157 -------------------------------------------------------------------------------
158 -- Procedure: create_k_from_q
159 -- Version: 1.0
160 -- Purpose: Updates Contract Header
161 -- Create relationships from quote to contract
162 -- Creates or Updates Header Level Rule Groups and Rules
163 -- If K is new, create new lines using template linestyles
164 -- If K is renewal, update line prices and rules for matching
165 -- Q-K lines, else
166 -- create new K lines from Quote using matching
167 -- linestyles on the K and template, delete k lines that have
168 -- been deleted from quote.
169 --
170 -- In Parameters: p_context New or renewal( = rel type )
171 -- p_chr_id Contract id
172 --
173 -- Out parameters: x_chr_id Contract id of the new contract
174 -- ----------------------------------------------------------------------------
175
176 PROCEDURE create_k_from_q(x_return_status OUT NOCOPY VARCHAR2
177 ,p_context IN VARCHAR2
178 ,p_chr_id IN NUMBER
179 );
180
181
182 -------------------------------------------------------------------------------
183 -- Procedure: get_k_number
184 -- ...
185 -- ----------------------------------------------------------------------------
186
187 PROCEDURE get_k_number (p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
188 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
189 ,p_contract_id IN NUMBER
190 ,x_contract_number OUT NOCOPY OKC_K_HEADERS_B.contract_number%TYPE
191 ,x_contract_number_modifier OUT NOCOPY OKC_K_HEADERS_B.contract_number_modifier%TYPE
192 ,x_return_status OUT NOCOPY VARCHAR2
193 ,x_msg_count OUT NOCOPY NUMBER
194 ,x_msg_data OUT NOCOPY VARCHAR2);
195
196
197
198 -------------------------------------------------------------------------------
199 -- Procedure: set_notification_msg
200 -- Version: 1.0
201 -- ...
202 -------------------------------------------------------------------------------
203
204 PROCEDURE set_notification_msg (p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
205 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
206 ,p_application_name IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
207 ,p_message_subject IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT OKC_API.G_MISS_CHAR
208 ,p_message_body IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT OKC_API.G_MISS_CHAR
209 ,p_message_body_token1 IN VARCHAR2 DEFAULT NULL
210 ,p_message_body_token1_value IN VARCHAR2 DEFAULT NULL
211 ,p_message_body_token2 IN VARCHAR2 DEFAULT NULL
212 ,p_message_body_token2_value IN VARCHAR2 DEFAULT NULL
213 ,p_message_body_token3 IN VARCHAR2 DEFAULT NULL
214 ,p_message_body_token3_value IN VARCHAR2 DEFAULT NULL
215 ,x_return_status OUT NOCOPY VARCHAR2);
216
217 -------------------------------------------------------------------------------
218 -- Procedure: notify_k_admin
219 -- Version: 1.0
220 -- ...
221 -------------------------------------------------------------------------------
222
223 -- Modified by Igor Filimonov 10-04-2001
224 -- Bug : 1905226 OKC, ISTORE TESTING: K ALERT RESULTS GRID SHOULD POPULATE K# FIELD
225 -- Problem : Notifications in Launchpad's Inbox don't show KNUMBER in subject
226 -- and 'Contract Number' column
227 -- Fix: p_contract_id was added into parameter list of notify_k_adm procedure
228
229 PROCEDURE notify_k_adm(p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
230 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
231 ,p_application_name IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
232 ,p_message_subject IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT OKC_API.G_MISS_CHAR
233 ,p_message_body IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT OKC_API.G_MISS_CHAR
234 ,p_message_body_token1 IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
238 ,p_message_body_token3 IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
235 ,p_message_body_token1_value IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
236 ,p_message_body_token2 IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
237 ,p_message_body_token2_value IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
239 ,p_message_body_token3_value IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
240 ,p_contract_id IN OKC_K_HEADERS_B.ID%TYPE DEFAULT NULL
241 ,x_k_admin_user_name OUT NOCOPY VARCHAR2
242 ,x_return_status OUT NOCOPY VARCHAR2
243 ,x_msg_count OUT NOCOPY NUMBER
244 ,x_msg_data OUT NOCOPY VARCHAR2);
245
246
247 -------------------------------------------------------------------------------
248 -- Procedure: create_interaction_history
249 -- Version: 1.0
250 -- Purpose: 1. In the event of a new contract's terms and conditions
251 -- not being approved by the customer, fresh negotiations
252 -- of the terms and conditions is undertaken and the
253 -- contract administrator notified.
254 -- Following the fresh negotiations, the customer may or
255 -- may not approve. If the customer still does not approve,
256 -- the contract has to be set back to an ENTERED state.
257 --
258 -- This procedure records the information used for the
259 -- these negotiations.
260 --
261 -- In Parameters: p_api_version API version (to be initialized to 1)
262 -- p_init_msg_list Flag to reset the error message stack
263 -- p_commit Commit flag for the transaction
264 -- p_contract_id Contract header id of the contract
265 -- whose TsandCs need to be negotiated
266 -- p_party_id Initiator of the Interaction
267 -- history as party id of person type
268 -- or as party id of a 'contact of' or
269 -- 'employee of' relationship between
270 -- the customer and his contact or his
271 -- employee
272 -- p_interaction_subject Short message to introduce the
273 -- interaction, like
274 -- Terms and conditions of a contract
275 -- p_interaction_body Message body to be used to build
276 -- the interaction
277 -- p_trace_mode Trace mode option to generate a
278 -- trace file
279 --
280 -- Out Parameters: x_return_status Final status of notification
281 -- sending API:
282 -- -OKC_API.G_RET_STS_SUCCESS
283 -- -OKC_API.G_RET_STS_ERROR
284 -- -OKC_API.G_RET_STS_UNEXP_ERROR
285 -- x_msg_count Number of messages set on the stack
286 -- x_msg_data Message info id x_msg_count = 1
287 -------------------------------------------------------------------------------
288
289 PROCEDURE create_interaction_history(p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
290 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
291 ,p_contract_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM
292 ,p_party_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM
293 ,p_interaction_subject IN VARCHAR2
294 ,p_interaction_body IN VARCHAR2
295 ,x_return_status OUT NOCOPY VARCHAR2
296 ,x_msg_count OUT NOCOPY NUMBER
297 ,x_msg_data OUT NOCOPY VARCHAR2);
298
299
300 -------------------------------------------------------------------------------
301 -- Procedure: get_articles
302 -- Version: 1.0
303 -- Purpose: API is used to retrive Standard and Non-standard Articles
304 --
305 -- IN Parameters : p_contract_id Contract Id
306 --
307 -- OUT Parameters : x_articles
308 -------------------------------------------------------------------------------
309
310 PROCEDURE get_articles (p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
311 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
312 ,p_contract_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM
313 ,p_release_id IN NUMBER DEFAULT NULL
314 ,x_articles OUT NOCOPY OKC_K_ARTICLES_TL.TEXT%TYPE
315 ,x_return_status OUT NOCOPY VARCHAR2
316 ,x_msg_count OUT NOCOPY NUMBER
317 ,x_msg_data OUT NOCOPY VARCHAR2);
318
319
320 -------------------------------------------------------------------------------
321 -- Procedure: notify_sales_rep
322 -- Version: 1.0
323 -- ...
324 -------------------------------------------------------------------------------
325
326 PROCEDURE notify_sales_rep (p_api_version IN NUMBER DEFAULT OKC_API.G_MISS_NUM
327 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
328 ,p_contract_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM
329 ,p_contract_status IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR
330 ,x_return_status OUT NOCOPY VARCHAR2
331 ,x_msg_count OUT NOCOPY NUMBER
335
332 ,x_msg_data OUT NOCOPY VARCHAR2);
333
334
336 -------------------------------------------------------------------------------
337 -- Procedure: create_rule_group
338 -- Purpose: Create a rule group or Update an existing rule group.
339 -- In Parameters: p_level 'H' for header level, 'L' for line level
340 -- p_rgd_type A rule group definition code
341 -- p_chrv_rec Contract header record
342 -- Out Parameters: x_return_status Return status
343 -- x_rgpv_rec Output rule group record
344 -------------------------------------------------------------------------------
345 PROCEDURE create_rule_group (p_level IN VARCHAR2
346 ,p_rgd_type IN VARCHAR2
347 ,p_chrv_rec IN okc_contract_pub.chrv_rec_type
348 ,p_clev_rec IN okc_contract_pub.clev_rec_type
349 ,x_return_status OUT NOCOPY VARCHAR2
350 ,x_rgpv_rec OUT NOCOPY okc_rule_pub.rgpv_rec_type
351 );
352
353
354 -------------------------------------------------------------------------------
355 -- Procedure: Update_rule_group
356 -- Purpose: Update an existing rule group.
357 -- In Parameters: p_level 'H' for header level only
358 -- p_rgd_type A rule group definition code
359 -- p_chrv_rec Contract header record
360 -- Out Parameters: x_return_status Return status
361 -- x_rgpv_rec Output rule group record
362 -------------------------------------------------------------------------------
363 PROCEDURE update_rule_group (p_level IN VARCHAR2
364 ,p_rgd_type IN VARCHAR2
365 ,p_chrv_rec IN okc_contract_pub.chrv_rec_type
366 ,p_rgpv_rec IN okc_rule_pub.rgpv_rec_type
367 ,x_return_status OUT NOCOPY VARCHAR2
368 ,x_rgpv_rec OUT NOCOPY okc_rule_pub.rgpv_rec_type
369 );
370
371
372
373 -------------------------------------------------------------------------------
374 -- Procedure: create_rule
375 -- Purpose: Create a rule OR Update an existing rule.
376 -- In Parameters:
377 -- Out Parameters: x_return_status Return status
378 -- x_rgpv_rec Output rule group record
379 -------------------------------------------------------------------------------
380 PROCEDURE create_rule (p_rgpv_rec IN okc_rule_pub.rgpv_rec_type
381 ,p_chrv_rec IN okc_contract_pub.chrv_rec_type
382 ,p_rulv_rec IN okc_rule_pub.rulv_rec_type
383 ,x_return_status OUT NOCOPY VARCHAR2
384 ,x_rulv_rec OUT NOCOPY okc_rule_pub.rulv_rec_type
385 );
386
387
388
389 -------------------------------------------------------------------------------
390 -- Procedure: update_rule
391 -- Purpose: Update an existing rule.
392 -- In Parameters:
393 -- Out Parameters: x_return_status Return status
394 -- x_rgpv_rec Output rule group record
395 -------------------------------------------------------------------------------
396 PROCEDURE update_rule (p_rgpv_rec IN okc_rule_pub.rgpv_rec_type
397 ,p_chrv_rec IN okc_contract_pub.chrv_rec_type
398 ,p_rulv_rec IN okc_rule_pub.rulv_rec_type
399 ,x_return_status OUT NOCOPY VARCHAR2
400 ,x_rulv_rec OUT NOCOPY okc_rule_pub.rulv_rec_type
401 );
402
403
404 -------------------------------------------------------------------------------
405 -- Procedure: create_rg_party_roles
406 -- Purpose: Create a rule group party role.
407 -- In Parameters: p_chrv_rec_ Contract_header record
408 -- Out Parameters: x_return_status Return status
409 -- x_rmpv_tbl Output rule group party roles table
410 -------------------------------------------------------------------------------
411 PROCEDURE create_rg_party_roles (p_chrv_rec IN okc_contract_pub.chrv_rec_type
412 ,x_return_status OUT NOCOPY VARCHAR2
413 );
414
415
416
417 -------------------------------------------------------------------------------
418 -- Procedure: instantiate_counter_events
419 -- Purpose: Initializes Counters, Events and Coverage templates for a
420 -- service line
421 -- Used when creating a contract from a quote
422 -- In Parameters: p_start_date Start date of the service line
423 -- p_END_date End Date for the service line
424 -- p_cle_id Service line ID
425 --
426 -- Out Parameters: x_return_status Return status
427 -------------------------------------------------------------------------------
428 PROCEDURE instantiate_counters_events (x_return_status OUT NOCOPY VARCHAR2
429 ,p_start_date IN DATE
430 ,p_END_date IN DATE
431 ,p_inv_org_id IN NUMBER
432 ,p_cle_id IN NUMBER);
433
434
435 -------------------------------------------------------------------------------
436 -- Procedure: print_error
437 -- Returns:
438 -- Purpose: Print the last error which occured
439 -- In Parameters: pos position on the line to print the message
440 -- Out Parameters:
441 -------------------------------------------------------------------------------
442 PROCEDURE print_error(pos IN NUMBER);
443
444
445 END OKC_OC_INT_QTK_PVT;
[Home] [Help]