1 PACKAGE OE_CONTRACTS_UTIL AS
2 /* $Header: OEXUOKCS.pls 120.3.12010000.1 2008/07/25 07:56:52 appldev ship $ */
3
4
5 G_BSA_DOC_TYPE CHAR(1) := 'B'; --global constant for document type of Blanket Sales Agreement.
6
7 G_SO_DOC_TYPE CHAR(1) := 'O'; --global constant for document type of Sales Order
8
9 G_CNTR_LICENSED CHAR(1); /* global constant to store the result of API check OE_CONTRACTS_UTIL.check_license
10 i.e. whether the user is licensed to use the contractual option. */
11
12
13 SUBTYPE qa_result_tbl_type IS OKC_TERMS_QA_GRP.qa_result_tbl_type;
14
15 SUBTYPE sys_var_value_tbl_type IS OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
16
17 SUBTYPE doc_tbl_type IS OKC_TERMS_UTIL_GRP.doc_tbl_type;
18
19 TYPE line_var_tbl_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
20
21
22
23 /* this function is used to simply return the value of G_BSA_DOC_TYPE
24 used within forms libraries to access G_BSA_DOC_TYPE as the PL/SQL implementation of
25 the PL/SQL version used in forms does not allow direct reference to G_BSA_DOC_TYPE */
26 FUNCTION get_G_BSA_DOC_TYPE
27 RETURN VARCHAR2;
28
29 /* this function is used to simply return the value of G_SO_DOC_TYPE
30 used within forms libraries to access G_SO_DOC_TYPE as the PL/SQL implementation of
31 the PL/SQL version used in forms does not allow direct reference to G_SO_DOC_TYPE */
32 FUNCTION get_G_SO_DOC_TYPE
33 RETURN VARCHAR2;
34
35
36 --Check if user is licensed to use contracts
37 FUNCTION check_license
38 RETURN VARCHAR2;
39
40
41 --Copy Document Articles
42 PROCEDURE copy_articles
43 (
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
46 p_commit IN VARCHAR2 := FND_API.G_FALSE,
47
48 p_doc_type IN VARCHAR2,
49 p_copy_from_doc_id IN NUMBER,
50 p_version_number IN VARCHAR2 DEFAULT NULL,
51 p_copy_to_doc_id IN NUMBER,
52 p_copy_to_doc_start_date IN DATE := SYSDATE,
53 p_keep_version IN VARCHAR2 := 'N',
54 p_copy_to_doc_number IN NUMBER DEFAULT NULL,
55
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2
59 );
60
61
62
63
64
65 --Version articles of BSA or Sales Order
66 PROCEDURE version_articles
67 (
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
70 p_commit IN VARCHAR2 := FND_API.G_FALSE,
71
72 p_doc_type IN VARCHAR2,
73 p_doc_id IN NUMBER,
74 p_version_number IN VARCHAR2,
75 p_clear_amendment IN VARCHAR2 := 'Y',
76
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2
80 );
81
82
83 --perform QA checks upon the articles belonging to a BSA
84 PROCEDURE qa_articles
85 (
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89
90 p_qa_mode IN VARCHAR2 := OKC_TERMS_QA_GRP.G_NORMAL_QA,
91
92 p_doc_type IN VARCHAR2,
93 p_doc_id IN NUMBER,
94
95 x_qa_return_status OUT NOCOPY VARCHAR2,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2
99 );
100
101
102 --to determine whether any non standard articles exists for the BSA or Sales Order
103 --called from the approval workflow to determine whether non standard articles exist for the BSA or Sales Order being approved
104 FUNCTION non_standard_article_exists
105 (
106
107 p_api_version IN NUMBER,
108 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
109 p_commit IN VARCHAR2 := FND_API.G_FALSE,
110 p_doc_type IN VARCHAR2,
111 p_doc_id IN NUMBER,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2
115 )
116 RETURN VARCHAR2;
117
118
119 --workflow wrapper procedure for non_standard_articles_exists()
120 PROCEDURE WF_non_stndrd_article_exists (
121 itemtype IN VARCHAR2,
122 itemkey IN VARCHAR2,
123 actid IN NUMBER,
124 funcmode IN VARCHAR2,
125 resultout OUT NOCOPY VARCHAR2);
126
127
128
129 /* During the BSA or Sales Order approval workflow process, the notification sent by workflow
130 has a link that points to the attachment representing the BSA or Sales Order.
131 This procedure is used by that link (by a specialized item attribute) to point
132 to the OM entity or contract entity attachment representing the BSA or Sales Order */
133 PROCEDURE attachment_location
134 (
135 p_api_version IN NUMBER,
136 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
137
138 p_doc_type IN VARCHAR2,
139 p_doc_id IN NUMBER,
140
141 x_workflow_string OUT NOCOPY VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2) ;
145
146
147
148 /* Check if Blanket or Sales Order has any terms and conditions instantiated against it i.e. if
149 an article template exists for the Blanket or Sales Order or not.
150 This just translates the output of the already existing procedure 'get_terms_template'
151 into a 'Y' or 'N' */
152 -- needed and requested by the preview print application
153 FUNCTION terms_exists (
154 p_doc_type IN VARCHAR2,
155 p_doc_id IN NUMBER
156 )
157 RETURN VARCHAR2;
158
159
160
161 --delete articles belonging to the BSA or Sales Order
162 PROCEDURE delete_articles
163 (
164 p_api_version IN NUMBER,
165 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
166 p_commit IN VARCHAR2 := FND_API.G_FALSE,
167
168 p_doc_type IN VARCHAR2,
169 p_doc_id IN NUMBER,
170
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2
174 );
175
176
177
178 --purge articles belonging to the BSA's or Sales Orders
179 PROCEDURE purge_articles
180 (
181 p_api_version IN NUMBER,
182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
183 p_commit IN VARCHAR2 := FND_API.G_FALSE,
184
185 p_doc_tbl IN doc_tbl_type,
186
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 );
191
192
193
194
195 --this is called from the Articles QA
196 PROCEDURE get_article_variable_values
197 (
198 p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
200 p_commit IN VARCHAR2 := FND_API.G_FALSE,
201
202 p_doc_type IN VARCHAR2,
203 p_doc_id IN NUMBER,
204 p_sys_var_value_tbl IN OUT NOCOPY sys_var_value_tbl_type,
205
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 );
210
211 --this overloaded signature is called from the contract expert
212 PROCEDURE get_article_variable_values
213 (
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
216 p_commit IN VARCHAR2 := FND_API.G_FALSE,
217
218 p_doc_type IN VARCHAR2,
219 p_doc_id IN NUMBER,
220 p_line_var_tbl IN line_var_tbl_type,
221
222 x_line_var_value_tbl OUT NOCOPY sys_var_value_tbl_type,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2
226 );
227
228 --to return details about an article template being used by a particular BSA or Sales Order
229 PROCEDURE get_terms_template
230 (
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
233 p_commit IN VARCHAR2 := FND_API.G_FALSE,
234
235 p_doc_type IN VARCHAR2,
236 p_doc_id IN NUMBER,
237
238 x_template_id OUT NOCOPY NUMBER,
239 x_template_name OUT NOCOPY VARCHAR2,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2
243 );
244
245
246 --to return the name of a contract template
247 FUNCTION Get_Template_Name(
248 p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
250 p_template_id IN NUMBER,
251
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER
255 ) RETURN VARCHAR2;
256
257
258
259 --to instantiate T's/C's from a Terms template to a BSA or Sales Order
260 PROCEDURE instantiate_terms
261 (
262 p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
265
266 p_template_id IN NUMBER,
267 p_doc_type IN VARCHAR2,
268 p_doc_id IN NUMBER,
269 p_doc_start_date IN DATE ,
270 p_doc_number IN VARCHAR2,
271
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2
275 );
276
277
278
279 --to instantiate T's/C's from a Terms template to a BSA or Sales Order when after saving the BSA/Sales Order
280 --the contract template id is defaulted for a new BSA or Sales Order
281 PROCEDURE instantiate_doc_terms
282 (
283 p_api_version IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
285 p_commit IN VARCHAR2 := FND_API.G_FALSE,
286
287 p_template_id IN NUMBER,
288 p_doc_type IN VARCHAR2,
289 p_doc_id IN NUMBER,
290 p_doc_start_date IN DATE ,
291 p_doc_number IN VARCHAR2,
292
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2
296 );
297
298 --ETR
299 --This function is to check whether or not the given order has already been
300 --accepted (i.e signed). Returns 'Y' if accepted, and 'N' otherwise.
301 FUNCTION Is_order_signed(
302 p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
304
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308
309 p_doc_id IN NUMBER
310 ) RETURN VARCHAR2;
311 --ETR
312
313
314 --This function will be called from process order to copy terms and coditions
315 --from quote to order(terms instantiated on quote)
316 --from quote to order(terms not instantiated on quote) ,get terms from template
317 -- from sales order to sales order
318 --instantiate from template to sales order
319
320 PROCEDURE copy_doc
321 (
322 p_api_version IN NUMBER,
323 p_init_msg_list IN VARCHAR2,
324 p_commit IN VARCHAR2,
325 p_source_doc_type IN VARCHAR2,
326 p_source_doc_id IN NUMBER,
327 p_target_doc_type IN VARCHAR2,
328 p_target_doc_id IN NUMBER,
329 p_contract_template_id IN NUMBER,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2);
333
334
335
336 -- This function is a wrapper on top of oe_line_util.get_item_info
337 -- procedure. This is used to get the value and description for the products
338 -- in the blanket sales lines.
339 -- This will return the internal item and description for all but customer items
340 -- for which it returns the customer product and description
341 -- This function is used in the oe_blktprt_lines_v view, for the printing solution
342
343 FUNCTION GET_ITEM_INFO
344 ( p_item_or_desc IN VARCHAR2
345 , p_item_identifier_type IN VARCHAR2
346 , p_inventory_item_id IN Number
347 , p_ordered_item_id IN Number
351 ) RETURN VARCHAR2;
348 , p_sold_to_org_id IN Number
349 , p_ordered_item IN VARCHAR2
350 , p_org_id IN Number DEFAULT NULL
352
353 --FP word integration
354
355 --get the default template name, source and authoring party for the template id
356 PROCEDURE get_contract_defaults
357 (
358 p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
360 p_doc_type IN VARCHAR2,
361 p_template_id IN NUMBER,
362 x_authoring_party OUT NOCOPY VARCHAR2,
363 x_contract_source OUT NOCOPY VARCHAR2,
364 x_template_name OUT NOCOPY VARCHAR2,
365 x_return_status OUT NOCOPY VARCHAR2,
366 x_msg_count OUT NOCOPY NUMBER,
367 x_msg_data OUT NOCOPY VARCHAR2
368 );
369
370 --get the template name, id, source and authoring party for the doc id
371 PROCEDURE get_contract_details_all
372 (
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
375 p_doc_type IN VARCHAR2,
376 p_doc_id IN NUMBER,
377 p_document_version IN NUMBER := NULL,
378 x_template_id OUT NOCOPY NUMBER,
379 x_authoring_party OUT NOCOPY VARCHAR2,
380 x_contract_source OUT NOCOPY VARCHAR2,
381 x_contract_source_code OUT NOCOPY VARCHAR2,
382 x_has_primary_doc OUT NOCOPY VARCHAR2,
383 x_template_name OUT NOCOPY VARCHAR2,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2
387 );
388
389 --check if template attached to order type is valid or not
390 Function Is_Terms_Template_Valid
391 (
392 p_api_version IN NUMBER,
393 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397 p_doc_type IN VARCHAR2,
398 p_template_id IN NUMBER,
399 p_org_id IN NUMBER
400 ) RETURN VARCHAR2;
401
402
403 --Function to check if the Authoring Party is Internal, required by Preview and Print
404 Function Is_Auth_Party_Internal
405 (
406 p_doc_type IN VARCHAR2,
407 p_doc_id IN NUMBER
408 )
409 RETURN VARCHAR2;
410
411 Function Is_RChg_Enabled
412 (
413 p_doc_id IN NUMBER
414 )
415 RETURN VARCHAR2;
416
417 END OE_CONTRACTS_UTIL;