1 Package OKC_COPY_CONTRACT_PVT AUTHID CURRENT_USER AS
2 /*$Header: OKCRCPYS.pls 120.3.12020000.2 2012/08/10 09:13:06 spingali ship $*/
3
4
5 SUBTYPE chrv_rec_type IS OKC_CONTRACT_PUB.chrv_rec_type;
6 SUBTYPE chrv_tbl_type IS OKC_CONTRACT_PUB.chrv_tbl_type;
7 SUBTYPE clev_rec_type IS OKC_CONTRACT_PUB.clev_rec_type;
8 SUBTYPE clev_tbl_type IS OKC_CONTRACT_PUB.clev_tbl_type;
9 SUBTYPE cacv_rec_type IS OKC_CONTRACT_PUB.cacv_rec_type;
10 SUBTYPE cacv_tbl_type IS OKC_CONTRACT_PUB.cacv_tbl_type;
11 SUBTYPE cpsv_rec_type IS OKC_CONTRACT_PUB.cpsv_rec_type;
12 SUBTYPE cpsv_tbl_type IS OKC_CONTRACT_PUB.cpsv_tbl_type;
13 SUBTYPE rulv_rec_type IS OKC_RULE_PUB.rulv_rec_type;
14 SUBTYPE rulv_tbl_type IS OKC_RULE_PUB.rulv_tbl_type;
15 SUBTYPE rgpv_rec_type IS OKC_RULE_PUB.rgpv_rec_type;
16 SUBTYPE rgpv_tbl_type IS OKC_RULE_PUB.rgpv_tbl_type;
17 SUBTYPE rmpv_rec_type IS OKC_RULE_PUB.rmpv_rec_type;
18 SUBTYPE rmpv_tbl_type IS OKC_RULE_PUB.rmpv_tbl_type;
19 SUBTYPE catv_rec_type IS OKC_K_ARTICLE_PUB.catv_rec_type;
20 SUBTYPE catv_tbl_type IS OKC_K_ARTICLE_PUB.catv_tbl_type;
21 SUBTYPE atnv_rec_type IS OKC_K_ARTICLE_PUB.atnv_rec_type;
22 SUBTYPE atnv_tbl_type IS OKC_K_ARTICLE_PUB.atnv_tbl_type;
23 SUBTYPE cnhv_rec_type IS OKC_CONDITIONS_PUB.cnhv_rec_type;
24 SUBTYPE cnhv_tbl_type IS OKC_CONDITIONS_PUB.cnhv_tbl_type;
25 SUBTYPE cnlv_rec_type IS OKC_CONDITIONS_PUB.cnlv_rec_type;
26 SUBTYPE cnlv_tbl_type IS OKC_CONDITIONS_PUB.cnlv_tbl_type;
27 SUBTYPE cimv_rec_type IS OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
28 SUBTYPE cimv_tbl_type IS OKC_CONTRACT_ITEM_PUB.cimv_tbl_type;
29 SUBTYPE cplv_rec_type IS OKC_CONTRACT_PARTY_PUB.cplv_rec_type;
30 SUBTYPE cplv_tbl_type IS OKC_CONTRACT_PARTY_PUB.cplv_tbl_type;
31 SUBTYPE cgcv_rec_type IS OKC_CONTRACT_GROUP_PUB.cgcv_rec_type;
32 SUBTYPE cgcv_tbl_type IS OKC_CONTRACT_GROUP_PUB.cgcv_tbl_type;
33 SUBTYPE ctcv_rec_type IS OKC_CONTRACT_PARTY_PUB.ctcv_rec_type;
34 SUBTYPE ctcv_tbl_type IS OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
35 SUBTYPE tvev_rec_type IS OKC_TIMEVALUES_V%ROWTYPE;
36
37 ---------------------------------------------------------------------------
38 -- GLOBAL MESSAGE CONSTANTS
39 ---------------------------------------------------------------------------
40 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
41 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
42 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_DELETED;
43 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_CHANGED;
44 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
45 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
46 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
47 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
48 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
49 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
50 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
51 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
52 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
53 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UPPERCASE_REQ';
54
55 ------------------------------------------------------------------------------------
56 -- GLOBAL EXCEPTION
57 ---------------------------------------------------------------------------
58 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
59
60 -- GLOBAL VARIABLES
61 ---------------------------------------------------------------------------
62 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_COPY_CONTRACT_PVT';
63 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
64 g_rulv_rec rulv_rec_type;
65 ---------------------------------------------------------------------------
66
67 TYPE api_components_rec IS RECORD(id NUMBER,
68 to_k NUMBER,
69 component_type VARCHAR2(30),
70 attribute1 VARCHAR2(100));
71 TYPE api_components_tbl IS TABLE OF api_components_rec
72 INDEX BY BINARY_INTEGER;
73
74 TYPE api_lines_rec IS RECORD(id NUMBER,
75 to_k NUMBER,
76 to_line NUMBER,
77 lse_id NUMBER,
78 line_exists_yn VARCHAR2(1),
79 line_exp_yn VARCHAR2(1)); --Bug 3990643
80 TYPE api_lines_tbl IS TABLE OF api_lines_rec
81 INDEX BY BINARY_INTEGER;
82
83 ---Commented the plsql table definition in package body and added in package spec for bug 14387486
84 ----------------------------------------------------------------------------
85 --PL/SQL Table to check the party has already copied.
86 --If Yes give the new cpl_id ----Begins
87 ----------------------------------------------------------------------------
88 TYPE party_rec_type IS RECORD (
89 old_cpl_id NUMBER := OKC_API.G_MISS_NUM,
90 new_cpl_id NUMBER := OKC_API.G_MISS_NUM);
91 TYPE party_tbl_type IS TABLE OF party_rec_type
92 INDEX BY BINARY_INTEGER;
93 g_party party_tbl_type;
94
95 FUNCTION update_target_contract(p_chr_id IN NUMBER) RETURN BOOLEAN;
96 FUNCTION is_copy_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN;
97 FUNCTION is_subcontract_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN;
98
99 PROCEDURE derive_line_style(p_old_lse_id IN NUMBER,
100 p_old_jtot_code IN VARCHAR2,
101 p_new_subclass IN VARCHAR2,
102 p_new_parent_lse IN NUMBER,
103 x_new_lse_count OUT NOCOPY NUMBER,
104 x_new_lse_ids OUT NOCOPY VARCHAR2);
105
106 PROCEDURE copy_components(
107 p_api_version IN NUMBER,
108 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_count OUT NOCOPY NUMBER,
111 x_msg_data OUT NOCOPY VARCHAR2,
112 p_from_chr_id IN NUMBER,
113 p_to_chr_id IN NUMBER,
114 p_contract_number IN VARCHAR2,
115 p_contract_number_modifier IN VARCHAR2,
116 p_to_template_yn IN VARCHAR2 DEFAULT 'N',
117 p_copy_reference IN VARCHAR2 DEFAULT 'COPY',
118 p_copy_line_party_yn IN VARCHAR2,
119 p_scs_code IN VARCHAR2,
120 p_intent IN VARCHAR2,
121 p_prospect IN VARCHAR2,
122 p_components_tbl IN api_components_tbl,
123 p_lines_tbl IN api_lines_tbl,
124 x_chr_id OUT NOCOPY NUMBER,
125 p_concurrent_request IN VARCHAR2 DEFAULT 'N',
126 p_include_cancelled_lines IN VARCHAR2 DEFAULT 'Y',
127 p_include_terminated_lines IN VARCHAR2 DEFAULT 'Y');
128 -- Bug 2950549 - Added parameter p_concurrent_request in the above procedure
129
130
131 PROCEDURE copy_contract(
132 p_api_version IN NUMBER,
133 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 p_commit IN VARCHAR2 DEFAULT 'F',
138 p_chr_id IN NUMBER,
139 p_contract_number IN VARCHAR2,
140 p_contract_number_modifier IN VARCHAR2,
141 p_to_template_yn IN VARCHAR2,
142 p_renew_ref_yn IN VARCHAR2,
143 p_copy_lines_yn IN VARCHAR2,
144 p_override_org IN VARCHAR2 DEFAULT 'N',
145 p_copy_from_history_yn IN VARCHAR2 DEFAULT 'N',
146 p_from_version_number IN NUMBER DEFAULT NULL,
147 p_copy_latest_articles IN VARCHAR2 DEFAULT 'Y',
148 p_calling_mode IN VARCHAR2 DEFAULT 'OKC',
149 x_chr_id OUT NOCOPY NUMBER);
150
151 PROCEDURE copy_contract_lines(
152 p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 p_from_cle_id IN NUMBER,
158 p_to_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
159 p_to_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
160 p_to_template_yn IN VARCHAR2,
161 p_copy_reference IN VARCHAR2,
162 p_copy_line_party_yn IN VARCHAR2,
163 p_renew_ref_yn IN VARCHAR2,
164 p_generate_line_number IN VARCHAR2 DEFAULT 'Y', -- Added for Bug 2489856
165 x_cle_id OUT NOCOPY NUMBER,
166 p_change_status IN VARCHAR2 DEFAULT 'Y'); -- LLC Added additional flag parameter to the call
167 -- to not allow change of status of sublines of the
168 -- topline during update service
169
170 PROCEDURE copy_contract_header(
171 p_api_version IN NUMBER,
172 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 p_from_chr_id IN NUMBER,
177 p_contract_number IN VARCHAR2,
178 p_contract_number_modifier IN VARCHAR2,
179 p_scs_code IN VARCHAR2,
180 p_intent IN VARCHAR2,
181 p_prospect IN VARCHAR2,
182 p_called_from IN VARCHAR2,
183 p_to_template_yn IN VARCHAR2,
184 p_renew_ref_yn IN VARCHAR2,
185 p_override_org IN VARCHAR2 DEFAULT 'N',
186 p_calling_mode IN VARCHAR2 DEFAULT 'OKC',
187 x_chr_id OUT NOCOPY NUMBER);
188
189 PROCEDURE copy_contract_line(
190 p_api_version IN NUMBER,
191 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2,
195 p_from_cle_id IN NUMBER,
196 p_from_chr_id IN NUMBER,
197 p_to_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
198 p_to_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
199 p_lse_id IN NUMBER,
200 p_to_template_yn IN VARCHAR2,
201 p_copy_reference IN VARCHAR2 DEFAULT 'COPY',
202 p_copy_line_party_yn IN VARCHAR2,
203 p_renew_ref_yn IN VARCHAR2,
204 p_generate_line_number IN VARCHAR2 DEFAULT 'Y', -- Added for Bug 2489856
205 x_cle_id OUT NOCOPY NUMBER,
206 p_change_status IN VARCHAR2 DEFAULT 'Y'); -- LLC Added additional flag parameter to the call
207 -- to not allow change of status of sublines of the
208 -- topline during update service
209
210 PROCEDURE copy_party_roles(
211 p_api_version IN NUMBER,
212 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2,
216 p_cpl_id IN NUMBER,
217 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
218 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
219 p_rle_code IN VARCHAR2,
220 x_cpl_id OUT NOCOPY NUMBER);
221
222 PROCEDURE copy_articles(
223 p_api_version IN NUMBER,
224 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2,
228 p_cat_id IN NUMBER,
229 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
230 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
231 p_sav_sav_release IN VARCHAR2 DEFAULT NULL,
232 x_cat_id OUT NOCOPY NUMBER);
233
234 PROCEDURE copy_latest_articles(
235 p_api_version IN NUMBER,
236 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 p_cat_id IN NUMBER,
241 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
242 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
243 x_cat_id OUT NOCOPY NUMBER);
244
245 PROCEDURE copy_events(
246 p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2,
251 p_cnh_id IN NUMBER,
252 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
253 p_to_template_yn IN VARCHAR2,
254 x_cnh_id OUT NOCOPY NUMBER);
255
256 PROCEDURE copy_rules(
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2,
262 p_rgp_id IN NUMBER,
263 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
264 p_chr_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
265 p_to_template_yn IN VARCHAR2,
266 x_rgp_id OUT NOCOPY NUMBER);
267
268 -- Bug 2950549 Added following 2 procedures add_concurrent and
269 -- copy_concurrent
270
271 PROCEDURE add_concurrent(p_components_tbl IN api_components_tbl,
272 p_lines_tbl IN api_lines_tbl,
273 p_from_chr_id IN NUMBER,
274 p_to_chr_id IN NUMBER,
275 p_contract_number IN VARCHAR2,
276 p_contract_number_modifier IN VARCHAR2,
277 p_to_template_yn IN VARCHAR2 DEFAULT 'N',
278 p_copy_reference IN VARCHAR2 DEFAULT 'COPY',
279 p_copy_line_party_yn IN VARCHAR2,
280 p_scs_code IN VARCHAR2,
281 p_intent IN VARCHAR2,
282 p_prospect IN VARCHAR,
283 p_include_cancelled_lines IN VARCHAR2 DEFAULT 'Y', p_include_terminated_lines IN VARCHAR2 DEFAULT 'Y');
284
285 PROCEDURE copy_concurrent(errbuf out NOCOPY VARCHAR2,
286 retcode out NOCOPY NUMBER,
287 p_id in NUMBER,
288 p_from_chr_id IN NUMBER,
289 p_to_chr_id IN NUMBER,
290 p_contract_number IN VARCHAR2,
291 p_contract_number_modifier IN VARCHAR2,
292 p_to_template_yn IN VARCHAR2,
293 p_copy_reference IN VARCHAR2,
294 p_copy_line_party_yn IN VARCHAR2,
295 p_scs_code IN VARCHAR2,
296 p_intent IN VARCHAR2,
297 p_prospect IN VARCHAR2,
298 p_copy_entire_k_yn IN VARCHAR2, /*hkamdar added new parameter for R12 copy enhancements.*/ p_include_cancelled_lines IN VARCHAR2 DEFAULT 'Y',
299 p_include_terminated_lines IN VARCHAR2 DEFAULT 'Y');
300 -- Bug 2950549 End of the procedures added
301
302 -- IKON ER 3819893
303 PROCEDURE UPDATE_TEMPLATE_CONTRACT (p_api_version IN NUMBER,
304 p_chr_id IN NUMBER,
305 p_start_date IN DATE,
306 p_end_date IN DATE,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2,
309 x_return_status OUT NOCOPY VARCHAR2) ;
310
311
312
313 END OKC_COPY_CONTRACT_PVT;
[Home] [Help]