1 package OKC_RENEW_PVT as
2 /* $Header: OKCRRENS.pls 120.0 2005/05/26 09:30:10 appldev noship $ */
3
4 -- OKC-OC integration changes, added p_context variable. This variable
5 -- should be used to perform validation specific to K renewal from Quote
6 -- like, prevent renewal if a Quote for K renewal exists.
7
8 Type Renew_in_parameters_rec is RECORD
9 (
10 p_contract_id number,
11 p_contract_number okc_k_headers_v.contract_number%type,
12 p_contract_version varchar2(9),
13 p_contract_modifier okc_k_headers_v.contract_number_modifier%type,
14 p_object_version_number number,
15 p_new_contract_number okc_k_headers_v.contract_number%type,
16 p_new_contract_modifier okc_k_headers_v.contract_number_modifier%type,
17 p_start_date date,
18 p_end_date date,
19 p_orig_start_date date,
20 p_orig_end_date date,
21 p_uom_code okx_units_of_measure_v.uom_code%type,
22 p_duration number,
23 p_context varchar2(30) DEFAULT OKC_API.G_MISS_CHAR,
24 p_perpetual_flag varchar2(1) DEFAULT OKC_API.G_FALSE
25 );
26
27
28 Type Renew_in_parameters_tbl is table of Renew_in_parameters_rec
29 INDEX BY BINARY_INTEGER;
30
31 Type Renew_out_parameters_rec is RECORD
32 (
33 p_old_Contract_id number,
34 p_new_Contract_id number
35 );
36
37 Type Renew_out_parameters_tbl is table of Renew_out_parameters_rec
38 INDEX BY BINARY_INTEGER;
39
40 -- GLOBAL VARIABLES
41 ---------------------------------------------------------------------------
42 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
43 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
44 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
45 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
46 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_RENEW_PVT';
47 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
48 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
49 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
50 G_DNR_MSG CONSTANT VARCHAR2(200) := 'OKC_DNR_MSG';
51
52 ---------------------------------------------------------------------------
53
54 g_rulv_rec OKC_RULE_PUB.rulv_rec_type;
55
56 --------------------------------------------------------------------------------------------
57 -- Auto renew is a concurrent program initiated to renew the contracts automatically for
58 -- which auto_renew_days is given
59 --------------------------------------------------------------------------------------------
60 /* Procedure Auto_Renew( errbuf out nocopy varchar2,
61 ** retcode out nocopy varchar2
62 ** ); */
63 Procedure Auto_Renew(errbuf out nocopy varchar2,
64 retcode out nocopy varchar2,
65 p_chr_id IN Number Default Null,
66 p_duration IN Number Default Null,
67 p_uom_code IN Varchar2 Default Null,
68 p_renewal_called_from_ui IN VARCHAR2 DEFAULT 'N', /* added for bugfix 2093117 */
69 p_contract_number IN Varchar2 DEFAULT NULL,
70 p_contract_number_modifier IN VARCHAR2 Default Null
71 );
72
73
74 --------------------------------------------------------------------------------------------
75 --called from launchpad, it runs through certain validations and returns status
76 --------------------------------------------------------------------------------------------
77 FUNCTION is_renew_allowed(p_chr_id IN NUMBER,p_sts_code IN VARCHAR2) RETURN BOOLEAN;
78
79 --------------------------------------------------------------------------------------------
80 --called from renew procedure in public, this procedure actually creates a copy of
81 --of the contract to be renewed and adjust the dates on the various components of this
82 --copy
83 --------------------------------------------------------------------------------------------
84 Procedure Create_Renewed_Contract( p_api_version IN NUMBER,
85 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2,
89 x_contract_id OUT NOCOPY NUMBER,
90 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
91 x_renew_in_parameters_rec OUT NOCOPY Renew_in_parameters_rec,
92 p_ren_type IN varchar2 DEFAULT OKC_API.G_MISS_CHAR,
93 p_renewal_called_from_ui IN VARCHAR2 DEFAULT 'Y' /* added for bugfix 2093117 */
94 );
95
96 --function to validate the line
97 FUNCTION Validate_line(p_contract_id IN NUMBER) RETURN VARCHAR2;
98
99 --------------------------------------------------------------------------------------------
100 --called from renew procedure in public, this procedure sends the notifiactions
101 -- and a few other things depending on the renew rule defined on the contract
102 --------------------------------------------------------------------------------------------
103 PROCEDURE post_renewed_contract(
104 p_api_version IN NUMBER,
105 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2,
109 p_renew_chr_id IN number,
110 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
111 p_ren_type IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
112 p_contact IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR);
113
114 --------------------------------------------------------------------------------------------
115 -- this procedure runs the validations on the contract to be renewed
116 --------------------------------------------------------------------------------------------
117 Procedure validate( p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 p_renew_in_parameters_rec IN Renew_in_parameters_rec,
123 p_renewal_called_from_ui IN VARCHAR2 DEFAULT 'Y' /* added for bugfix 2093117 */
124 );
125
126 --------------------------------------------------------------------------------------------
127 -- sets the notification message to be sent after the renewal
128 --------------------------------------------------------------------------------------------
129 PROCEDURE set_notify(
130 x_return_status OUT NOCOPY VARCHAR2 ,
131 p_old_k IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
132 p_old_mod IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
133 p_new_k IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
134 p_new_mod IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
135 p_qa_stat IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
136 p_wf_found IN VARCHAR2 DEFAULT 'T',
137 p_subj_msg IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR,
138 p_ren_type IN VARCHAR2 DEFAULT OKC_API.G_MISS_CHAR);
139
140
141 -- this procedure is called in the OKCRRENB.pls by OKC but exposed
142 --outside to be called by OKS renew consolidation
143 PROCEDURE update_renewal_dates(
144 p_api_version IN NUMBER,
145 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 p_chr_id IN number,
150 p_parent_cle_id IN number DEFAULT OKC_API.G_MISS_NUM,
151 p_parent_new_st_dt IN DATE,
152 p_parent_new_end_dt IN DATE,
153 p_parent_old_st_dt IN DATE,
154 p_cle_id IN NUMBER DEFAULT OKC_API.G_MISS_NUM,
155 p_rencon_yn IN VARCHAR2 DEFAULT 'N');
156
157 --------------------------------------------------------------------------------
158 -- This procedure updates Date_Renewed field in headers and lines table
159 -- The Date_Renewed field for a line is updated when the Date_Renewed field
160 -- of all its sublines is not null and is updated by the maximum Date_Renewed
161 -- of all its sublines. True for all the levels. The contract header
162 -- Date_Renewed is updated when all it's top lines Date_Renewed is not null
163 -- and is updated by by the maximum Date_Renewed of all the top lines
164 --------------------------------------------------------------------------------
165
166 Procedure Update_Parents_Date_Renewed( p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 p_chr_id IN NUMBER
172 );
173
174 /* This function is_already_not_renewed added for bugfix 2093117 */
175
176 --Bug 3386577 Addedd out parameter
177 --Bug 3560988 Added p_renewal_called_from_ui with default value as 'Y'
178
179 FUNCTION is_already_not_renewed(p_chr_id IN NUMBER, p_contract_number IN VARCHAR2, x_msg_name OUT NOCOPY VARCHAR2, p_renewal_called_from_ui IN VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2;
180
181 end OKC_RENEW_PVT;