1 PACKAGE okl_vp_change_request_pvt AUTHID CURRENT_USER AS
2 /* $Header: OKLRVCRS.pls 120.1 2005/09/19 07:31:20 sjalasut noship $ */
3
4 SUBTYPE vcrv_rec_type IS okl_vcr_pvt.vcrv_rec_type;
5 SUBTYPE vrrv_rec_type IS okl_vrr_pvt.vrrv_rec_type;
6 SUBTYPE vrrv_tbl_type IS okl_vrr_pvt.vrrv_tbl_type;
7
8 ---------------------------------------------------------------------------
9 -- GLOBAL EXCEPTIONS
10 ---------------------------------------------------------------------------
11 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
12
13 ---------------------------------------------------------------------------
14 -- GLOBAL VARIABLES
15 ---------------------------------------------------------------------------
16 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_VP_CHANGE_REQUEST_PVT';
17 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
18 G_API_TYPE CONSTANT VARCHAR2(30) := '_PVT';
19 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
21
22 -------------------------------------------------------------------------------
23 -- PROCEDURE create_change_request_header
24 -------------------------------------------------------------------------------
25 -- Start of comments
26 --
27 -- Procedure Name : create_change_request_header
28 -- Description : handles creation of change request header for Operating Agreement and Program Agreement
29 -- Parameters : IN p_vcrv_rec vcrv_rec_type
30 -- Version : 1.0
31 -- History : 26-APR-2005 SJALASUT created
32 -- End of comments
33
34 PROCEDURE create_change_request_header(p_api_version IN NUMBER
35 ,p_init_msg_list IN VARCHAR2
36 ,x_return_status OUT NOCOPY VARCHAR2
37 ,x_msg_count OUT NOCOPY NUMBER
38 ,x_msg_data OUT NOCOPY VARCHAR2
39 ,p_vcrv_rec IN vcrv_rec_type
40 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
41 );
42
43 -------------------------------------------------------------------------------
44 -- PROCEDURE update_change_request_header
45 -------------------------------------------------------------------------------
46 -- Start of comments
47 --
48 -- Procedure Name : update_change_request_header
49 -- Description : handles updation of change request header for Operating Agreement and Program Agreement
50 -- Parameters : IN p_vcrv_rec vcrv_rec_type
51 -- Version : 1.0
52 -- History : 26-APR-2005 SJALASUT created
53 -- End of comments
54
55 PROCEDURE update_change_request_header(p_api_version IN NUMBER
56 ,p_init_msg_list IN VARCHAR2
57 ,x_return_status OUT NOCOPY VARCHAR2
58 ,x_msg_count OUT NOCOPY NUMBER
59 ,x_msg_data OUT NOCOPY VARCHAR2
60 ,p_vcrv_rec IN vcrv_rec_type
61 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
62 );
63
64 -------------------------------------------------------------------------------
65 -- PROCEDURE create_change_request_lines
66 -------------------------------------------------------------------------------
67 -- Start of comments
68 --
69 -- Procedure Name : create_change_request_lines
70 -- Description : handles creation of change request reasons for Operating Agreement and Program Agreement
71 -- Parameters : IN p_vrrv_tbl vrrv_tbl_type
72 -- OUT x_request_status is the status code of the change request header. if this api is being
73 -- called after the change is request passes validation, then this status is set to
74 -- INCOMPLETE. for all other cases the status from the database is returned
75 -- Version : 1.0
76 -- History : 26-APR-2005 SJALASUT created
77 -- End of comments
78
79 PROCEDURE create_change_request_lines(p_api_version IN NUMBER
80 ,p_init_msg_list IN VARCHAR2
81 ,x_return_status OUT NOCOPY VARCHAR2
82 ,x_msg_count OUT NOCOPY NUMBER
83 ,x_msg_data OUT NOCOPY VARCHAR2
84 ,p_vrrv_tbl IN vrrv_tbl_type
85 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
86 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
87 );
88
89 -------------------------------------------------------------------------------
90 -- PROCEDURE update_change_request_lines
91 -------------------------------------------------------------------------------
92 -- Start of comments
93 --
94 -- Procedure Name : update_change_request_lines
95 -- Description : handles updation of change request reasons for Operating Agreement and Program Agreement
96 -- Parameters : IN p_vrrv_tbl vrrv_tbl_type
97 -- OUT x_request_status is the status code of the change request header. if this api is being
98 -- called after the change is request passes validation, then this status is set to
99 -- INCOMPLETE. for all other cases the status from the database is returned
100 -- Version : 1.0
101 -- History : 26-APR-2005 SJALASUT created
102 -- End of comments
103
104 PROCEDURE update_change_request_lines(p_api_version IN NUMBER
105 ,p_init_msg_list IN VARCHAR2
106 ,x_return_status OUT NOCOPY VARCHAR2
107 ,x_msg_count OUT NOCOPY NUMBER
108 ,x_msg_data OUT NOCOPY VARCHAR2
109 ,p_vrrv_tbl IN vrrv_tbl_type
110 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
111 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
112 );
113
114 -------------------------------------------------------------------------------
115 -- PROCEDURE delete_change_request_lines
116 -------------------------------------------------------------------------------
117 -- Start of comments
118 --
119 -- Procedure Name : delete_change_request_lines
120 -- Description : handles deletion of change request reasons for Operating Agreement and Program Agreement
121 -- Parameters : IN p_vrrv_tbl vrrv_tbl_type
122 -- OUT x_request_status is the status code of the change request header. if this api is being
123 -- called after the change is request passes validation, then this status is set to
124 -- INCOMPLETE. for all other cases the status from the database is returned
125 -- Version : 1.0
126 -- History : 26-APR-2005 SJALASUT created
127 -- End of comments
128
129 PROCEDURE delete_change_request_lines(p_api_version IN NUMBER
130 ,p_init_msg_list IN VARCHAR2
131 ,x_return_status OUT NOCOPY VARCHAR2
132 ,x_msg_count OUT NOCOPY NUMBER
133 ,x_msg_data OUT NOCOPY VARCHAR2
134 ,p_vrrv_tbl IN vrrv_tbl_type
135 ,x_request_status OUT NOCOPY okl_vp_change_requests.status_code%TYPE
136 );
137
138 -------------------------------------------------------------------------------
139 -- PROCEDURE create_change_request
140 -------------------------------------------------------------------------------
141 -- Start of comments
142 --
143 -- Procedure Name : create_change_request
144 -- Description : handles creation of change request for Operating Agreement and Program Agreement
145 -- change request implies one header and multiple reason lines
146 -- Parameters : IN p_vcrv_rec vcrv_rec_type
147 -- IN p_vrrv_tbl vrrv_tbl_type
148 -- Version : 1.0
149 -- History : 26-APR-2005 SJALASUT created
150 -- End of comments
151
152 PROCEDURE create_change_request(p_api_version IN NUMBER
153 ,p_init_msg_list IN VARCHAR2
154 ,x_return_status OUT NOCOPY VARCHAR2
155 ,x_msg_count OUT NOCOPY NUMBER
156 ,x_msg_data OUT NOCOPY VARCHAR2
157 ,p_vcrv_rec IN vcrv_rec_type
158 ,p_vrrv_tbl IN vrrv_tbl_type
159 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
160 ,x_vrrv_tbl OUT NOCOPY vrrv_tbl_type
161 );
162
163 -------------------------------------------------------------------------------
164 -- PROCEDURE abandon_change_request
165 -------------------------------------------------------------------------------
166 -- Start of comments
167 --
168 -- Procedure Name : abandon_change_request
169 -- Description : procedure abandons change requests for Operating Agreement and Program Agreement
170 -- that have not been sent for approval
171 -- Parameters : IN p_vcrv_rec vcrv_rec_type
172 -- Version : 1.0
173 -- History : 26-APR-2005 SJALASUT created
174 -- End of comments
175
176 PROCEDURE abandon_change_request(p_api_version IN NUMBER
177 ,p_init_msg_list IN VARCHAR2
178 ,x_return_status OUT NOCOPY VARCHAR2
179 ,x_msg_count OUT NOCOPY NUMBER
180 ,x_msg_data OUT NOCOPY VARCHAR2
181 ,p_vcrv_rec IN vcrv_rec_type
182 ,x_vcrv_rec OUT NOCOPY vcrv_rec_type
183 );
184
185 -------------------------------------------------------------------------------
186 -- PROCEDURE set_change_request_status
187 -------------------------------------------------------------------------------
188 -- Start of comments
189 --
190 -- Procedure Name : set_change_request_status
191 -- Description : procedure updates change requests status for Operating Agreement and Program Agreement
192 -- the status change is required whenever the change request transitions to a state
193 -- For an AGREEMENT type of change request, the change request status is passed on to the
194 -- copied agreement
195 -- Parameters : IN p_vp_crq_id okl_vp_change_requests.id%TYPE
196 -- IN p_status_code okl_vp_change_requests.status_code%TYPE;
197 -- NO OUT parameters, if the x_return_status = OKL_API.G_RET_STS_SUCCESS then
198 -- the effective status of the change request is the passed in status code value
199 -- Version : 1.0
200 -- History : 26-APR-2005 SJALASUT created
201 -- End of comments
202
203 PROCEDURE set_change_request_status(p_api_version IN NUMBER
204 ,p_init_msg_list IN VARCHAR2
205 ,x_return_status OUT NOCOPY VARCHAR2
206 ,x_msg_count OUT NOCOPY NUMBER
207 ,x_msg_data OUT NOCOPY VARCHAR2
208 ,p_vp_crq_id IN okl_vp_change_requests.id%TYPE
209 ,p_status_code IN okl_vp_change_requests.status_code%TYPE
210 );
211
212 -------------------------------------------------------------------------------
213 -- PROCEDURE cascade_request_status_edit
214 -------------------------------------------------------------------------------
215 -- Start of comments
216 --
217 -- Procedure Name : cascade_request_status_edit
218 -- Description : procedure updates change requests header status for Operating Agreement and Program Agreement
219 -- to INCOMPLETE when a PASSED agreement is updated
220 -- Parameters : IN p_vp_crq_id change request header id
221 -- OUT x_status_code the effective status of the change request
222 -- Version : 1.0
223 -- History : 26-APR-2005 SJALASUT created
224 -- End of comments
225
226 PROCEDURE cascade_request_status_edit(p_api_version IN NUMBER
227 ,p_init_msg_list IN VARCHAR2
228 ,x_return_status OUT NOCOPY VARCHAR2
229 ,x_msg_count OUT NOCOPY NUMBER
230 ,x_msg_data OUT NOCOPY VARCHAR2
231 ,p_vp_crq_id IN okl_vp_change_requests.id%TYPE
232 ,x_status_code OUT NOCOPY okl_vp_change_requests.status_code%TYPE
233 );
234
235 -------------------------------------------------------------------------------
236 -- PROCEDURE submit_cr_for_approval
237 -------------------------------------------------------------------------------
238 -- Start of comments
239 --
240 -- Procedure Name : submit_cr_for_approval
241 -- Description : procedure raises business events for agreement change request approval.
242 -- if the profile option OKL: Change Request Approval Process is set to NULL or NONE
243 -- this API then also synchronizes the changes on the copied agreement on to the
244 -- original agreement and updates the change request
245 -- Parameters : IN p_chr_id agreement id which is tied to the change request
246 -- OUT x_status_code the effective status of the change request
247 -- Version : 1.0
248 -- History : 26-APR-2005 SJALASUT created
249 -- End of comments
250
251 PROCEDURE submit_cr_for_approval(p_api_version IN NUMBER
252 ,p_init_msg_list IN VARCHAR2
253 ,x_return_status OUT NOCOPY VARCHAR2
254 ,x_msg_count OUT NOCOPY NUMBER
255 ,x_msg_data OUT NOCOPY VARCHAR2
256 ,p_chr_id IN okc_k_headers_b.id%TYPE
257 ,x_status_code OUT NOCOPY okl_vp_change_requests.status_code%TYPE
258 );
259
260 -------------------------------------------------------------------------------
261 -- FUNCTION get_assoc_agr_number
262 -------------------------------------------------------------------------------
263 -- Start of comments
264 --
265 -- Procedure Name : get_assoc_agr_number
266 -- Description : function returns the assocaited agreement number with this change request
267 -- if the change request is of type AGREEMENT, then the agreement number is
268 -- determined by the okl_k_headers record that contains this change request id
269 -- Parameters : IN p_change_request_id id from okl_vp_change_requests table
270 -- returns contract_number of okc_k_headers_b
271 -- Version : 1.0
272 -- History : 18-SEP-2005 SJALASUT created
273 -- End of comments
274
275 FUNCTION get_assoc_agr_number(p_change_request_id IN okl_vp_change_requests.id%TYPE) RETURN VARCHAR2;
276
277 -------------------------------------------------------------------------------
278 -- FUNCTION get_assoc_agr_id
279 -------------------------------------------------------------------------------
280 -- Start of comments
281 --
282 -- Procedure Name : get_assoc_agr_id
283 -- Description : function returns the assocaited agreement id with this change request
284 -- if the change request is of type AGREEMENT, then the agreement id is
285 -- determined by the okl_k_headers record that contains this change request id
286 -- Parameters : IN p_change_request_id id from okl_vp_change_requests table
287 -- returns id of okc_k_headers_b
288 -- Version : 1.0
289 -- History : 18-SEP-2005 SJALASUT created
290 -- End of comments
291
292 FUNCTION get_assoc_agr_id(p_change_request_id IN okl_vp_change_requests.id%TYPE) RETURN NUMBER;
293
294 END okl_vp_change_request_pvt;
[Home] [Help]