[Home] [Help]
PACKAGE BODY: APPS.CS_EXP_TERMINATE_PVT
Source
1 package body cs_exp_terminate_pvt as
2 /* $Header: csctexpb.pls 115.4 99/07/16 08:52:11 porting ship $ */
3 TYPE RContract_Rec_Type IS RECORD (
4 CONTRACT_ID CS_CONTRACTS_ALL.CONTRACT_ID%TYPE := NULL,
5 CONTRACT_NUMBER CS_CONTRACTS_ALL.CONTRACT_NUMBER%TYPE := NULL,
6 PRICE_LIST_ID CS_CONTRACTS_ALL.PRICE_LIST_ID%TYPE := NULL,
7 CURRENCY_CODE CS_CONTRACTS_ALL.CURRENCY_CODE%TYPE := NULL,
8 INVOICING_RULE_ID CS_CONTRACTS_ALL.INVOICING_RULE_ID%TYPE := NULL,
9 ACCOUNTING_RULE_ID CS_CONTRACTS_ALL.ACCOUNTING_RULE_ID%TYPE := NULL,
10 BILL_TO_SITE_USE_ID CS_CONTRACTS_ALL.BILL_TO_SITE_USE_ID%TYPE := NULL,
11 CONTRACT_STATUS_ID CS_CONTRACTS_ALL.CONTRACT_STATUS_ID%TYPE := NULL,
12 CONTRACT_TYPE_ID CS_CONTRACTS_ALL.CONTRACT_TYPE_ID%TYPE := NULL,
13 CUSTOMER_ID CS_CONTRACTS_ALL.CUSTOMER_ID%TYPE := NULL,
14 DURATION CS_CONTRACTS_ALL.DURATION%TYPE := NULL,
15 PERIOD_CODE CS_CONTRACTS_ALL.PERIOD_CODE%TYPE := NULL,
16 START_DATE_ACTIVE CS_CONTRACTS_ALL.START_DATE_ACTIVE%TYPE := NULL,
17 END_DATE_ACTIVE CS_CONTRACTS_ALL.END_DATE_ACTIVE%TYPE := NULL,
18 AGREEMENT_ID CS_CONTRACTS_ALL.AGREEMENT_ID%TYPE := NULL,
19 BILLING_FREQUENCY_PERIOD CS_CONTRACTS_ALL.BILLING_FREQUENCY_PERIOD%TYPE := NULL,
20 BILL_ON CS_CONTRACTS_ALL.BILL_ON%TYPE := NULL,
21 FIRST_BILL_DATE CS_CONTRACTS_ALL.FIRST_BILL_DATE%TYPE := NULL,
22 NEXT_BILL_DATE CS_CONTRACTS_ALL.NEXT_BILL_DATE%TYPE := NULL,
23 SALESPERSON_ID CS_CONTRACTS_ALL.SALESPERSON_ID%TYPE := NULL,
24 ORDERED_BY_CONTACT_ID CS_CONTRACTS_ALL.ORDERED_BY_CONTACT_ID%TYPE := NULL,
25 CONTRACT_TEMPLATE_ID CS_CONTRACTS_ALL.CONTRACT_TEMPLATE_ID%TYPE := NULL,
26 CONTRACT_GROUP_ID CS_CONTRACTS_ALL.CONTRACT_GROUP_ID%TYPE := NULL,
27 WORKFLOW CS_CONTRACTS_ALL.WORKFLOW%TYPE := NULL,
28 WORKFLOW_PROCESS_ID CS_CONTRACTS_ALL.WORKFLOW_PROCESS_ID%TYPE := NULL,
29 CREATE_SALES_ORDER CS_CONTRACTS_ALL.CREATE_SALES_ORDER%TYPE := NULL,
30 SHIP_TO_SITE_USE_ID CS_CONTRACTS_ALL.SHIP_TO_SITE_USE_ID%TYPE := NULL,
31 RENEWAL_RULE CS_CONTRACTS_ALL.RENEWAL_RULE%TYPE := NULL,
32 TERMINATION_RULE CS_CONTRACTS_ALL.TERMINATION_RULE%TYPE := NULL,
33 CONVERSION_TYPE_CODE CS_CONTRACTS_ALL.CONVERSION_TYPE_CODE%TYPE := NULL,
34 CONVERSION_RATE CS_CONTRACTS_ALL.CONVERSION_RATE%TYPE := NULL,
35 CONVERSION_DATE CS_CONTRACTS_ALL.CONVERSION_DATE%TYPE := NULL,
36 SOURCE_CODE CS_CONTRACTS_ALL.SOURCE_CODE%TYPE := NULL,
37 SOURCE_REFERENCE CS_CONTRACTS_ALL.SOURCE_REFERENCE%TYPE := NULL,
38 TERMS_ID CS_CONTRACTS_ALL.TERMS_ID%TYPE := NULL,
39 PO_NUMBER CS_CONTRACTS_ALL.PO_NUMBER%TYPE := NULL,
40 TAX_HANDLING CS_CONTRACTS_ALL.TAX_HANDLING%TYPE := NULL,
41 TAX_EXEMPT_NUM CS_CONTRACTS_ALL.TAX_EXEMPT_NUM%TYPE := NULL,
42 TAX_EXEMPT_REASON_CODE CS_CONTRACTS_ALL.TAX_EXEMPT_REASON_CODE%TYPE := NULL,
43 CONTRACT_AMOUNT CS_CONTRACTS_ALL.CONTRACT_AMOUNT%TYPE := NULL,
44 AUTO_RENEWAL_FLAG CS_CONTRACTS_ALL.AUTO_RENEWAL_FLAG%TYPE := NULL,
45 ORIGINAL_END_DATE CS_CONTRACTS_ALL.ORIGINAL_END_DATE%TYPE := NULL,
46 TERMINATE_REASON_CODE CS_CONTRACTS_ALL.TERMINATE_REASON_CODE%TYPE := NULL,
47 DISCOUNT_ID CS_CONTRACTS_ALL.DISCOUNT_ID%TYPE := NULL,
48 ATTRIBUTE1 CS_CONTRACTS_ALL.ATTRIBUTE1%TYPE := NULL,
49 ATTRIBUTE2 CS_CONTRACTS_ALL.ATTRIBUTE2%TYPE := NULL,
50 ATTRIBUTE3 CS_CONTRACTS_ALL.ATTRIBUTE3%TYPE := NULL,
51 ATTRIBUTE4 CS_CONTRACTS_ALL.ATTRIBUTE4%TYPE := NULL,
52 ATTRIBUTE5 CS_CONTRACTS_ALL.ATTRIBUTE5%TYPE := NULL,
53 ATTRIBUTE6 CS_CONTRACTS_ALL.ATTRIBUTE6%TYPE := NULL,
54 ATTRIBUTE7 CS_CONTRACTS_ALL.ATTRIBUTE7%TYPE := NULL,
55 ATTRIBUTE8 CS_CONTRACTS_ALL.ATTRIBUTE8%TYPE := NULL,
56 ATTRIBUTE9 CS_CONTRACTS_ALL.ATTRIBUTE9%TYPE := NULL,
57 ATTRIBUTE10 CS_CONTRACTS_ALL.ATTRIBUTE10%TYPE := NULL,
58 ATTRIBUTE11 CS_CONTRACTS_ALL.ATTRIBUTE11%TYPE := NULL,
59 ATTRIBUTE12 CS_CONTRACTS_ALL.ATTRIBUTE12%TYPE := NULL,
60 ATTRIBUTE13 CS_CONTRACTS_ALL.ATTRIBUTE13%TYPE := NULL,
61 ATTRIBUTE14 CS_CONTRACTS_ALL.ATTRIBUTE14%TYPE := NULL,
62 ATTRIBUTE15 CS_CONTRACTS_ALL.ATTRIBUTE15%TYPE := NULL,
63 CONTEXT CS_CONTRACTS_ALL.CONTEXT%TYPE := NULL,
64 OBJECT_VERSION_NUMBER CS_CONTRACTS_ALL.OBJECT_VERSION_NUMBER%TYPE := NULL,
65 PO_REQUIRED_TO_SERVICE CS_CONTRACTS_ALL.PO_REQUIRED_TO_SERVICE%TYPE := NULL,
66 PRE_PAYMENT_REQUIRED CS_CONTRACTS_ALL.PRE_PAYMENT_REQUIRED%TYPE := NULL );
67 G_MISS_rcontract_rec RContract_Rec_Type;
68
69
70 PROCEDURE Populate_Contracts(p_from IN RContract_Rec_Type ,
71 P_to OUT CS_CONTRACT_PVT.Contract_Val_Rec_Type ,
72 p_status_id IN NUMBER) IS
73 BEGIN
74 p_to.contract_id := p_from.contract_id;
75 p_to.contract_number := p_from.contract_number;
76 p_to.workflow := p_from.workflow;
77 p_to.workflow_process_id := p_from.workflow_process_id;
78 p_to.agreement_id := p_from.agreement_id;
79 p_to.price_list_id := p_from.price_list_id;
80 p_to.currency_code := p_from.currency_code;
81 p_to.conversion_type_code := p_from.conversion_type_code;
82 p_to.conversion_rate := p_from.conversion_rate;
83 p_to.conversion_date := p_from.conversion_date;
84 p_to.invoicing_rule_id := p_from.invoicing_rule_id;
85 p_to.accounting_rule_id := p_from.accounting_rule_id;
86 p_to.billing_frequency_period := p_from.billing_frequency_period;
87 p_to.bill_on := p_from.bill_on;
88 p_to.first_bill_date := p_from.first_bill_date;
89 p_to.next_bill_date := p_from.next_bill_date;
90 p_to.create_sales_order := p_from.create_sales_order;
91 p_to.renewal_rule := p_from.renewal_rule;
92 p_to.termination_rule := p_from.termination_rule;
93 p_to.bill_to_site_use_id := p_from.bill_to_site_use_id;
94 p_to.contract_status_id := p_status_id;
95 p_to.contract_type_id := p_from.contract_type_id;
96 p_to.contract_template_id := p_from.contract_template_id;
97 p_to.contract_group_id := p_from.contract_group_id;
98 p_to.customer_id := p_from.customer_id;
99 p_to.duration := p_from.duration;
100 p_to.period_code := p_from.period_code;
101 p_to.ship_to_site_use_id := p_from.ship_to_site_use_id;
102 p_to.salesperson_id := p_from.salesperson_id;
103 p_to.ordered_by_contact_id := p_from.ordered_by_contact_id;
104 p_to.source_code := p_from.source_code;
105 p_to.source_reference := p_from.source_reference;
106 p_to.terms_id := p_from.terms_id;
107 p_to.po_number := p_from.po_number;
108 p_to.tax_handling := p_from.tax_handling;
109 p_to.tax_exempt_num := p_from.tax_exempt_num;
110 p_to.tax_exempt_reason_code := p_from.tax_exempt_reason_code;
111 p_to.contract_amount := p_from.contract_amount;
112 p_to.auto_renewal_flag := p_from.auto_renewal_flag;
113 p_to.original_end_date := p_from.original_end_date;
114 p_to.terminate_reason_code := p_from.terminate_reason_code;
115 p_to.discount_id := p_from.discount_id;
116 p_to.start_date_active := p_from.start_date_active;
117 p_to.end_date_active := p_from.end_date_active;
118 p_to.attribute1 := p_from.attribute1;
119 p_to.attribute2 := p_from.attribute2;
120 p_to.attribute3 := p_from.attribute3;
121 p_to.attribute4 := p_from.attribute4;
122 p_to.attribute5 := p_from.attribute5;
123 p_to.attribute6 := p_from.attribute6;
124 p_to.attribute7 := p_from.attribute7;
125 p_to.attribute8 := p_from.attribute8;
126 p_to.attribute9 := p_from.attribute9;
127 p_to.attribute10 := p_from.attribute10;
128 p_to.attribute11 := p_from.attribute11;
129 p_to.attribute12 := p_from.attribute12;
130 p_to.attribute13 := p_from.attribute13;
131 p_to.attribute14 := p_from.attribute14;
132 p_to.attribute15 := p_from.attribute15;
133 p_to.context := p_from.context;
134 p_to.object_version_number := p_from.object_version_number;
135 p_to.po_required_to_service := p_from.po_required_to_service;
136 p_to.pre_payment_required := p_from.pre_payment_required;
137
138 TAPI_DEV_KIT.Get_Who_Info (
139 p_to.Creation_Date,
140 p_to.Created_By,
141 p_to.Last_Update_Date,
142 p_to.Last_Updated_By,
143 p_to.Last_Update_Login);
144 END Populate_Contracts;
145
146
147 PROCEDURE exp_terminate_contract
148 (
149 exp_term_date in DATE
150 )IS
151
152 ---variables declared here
153
154 contract_rec cs_contract_pvt.Contract_Val_Rec_Type := cs_contract_pvt.G_MISS_CONTRACT_VAL_REC;
155 lcontract_rec RContract_Rec_Type;
156
157
158
159 LOOPING BOOLEAN := TRUE;
160 v_return_status VARCHAR2(1);
161 v_msg_count NUMBER;
162 v_msg_data VARCHAR2(2000);
163 v_contract_id NUMBER;
164 v_object_version_number NUMBER;
165 x_object_version_number NUMBER;
166 msg1 VARCHAR2(240);
167 msg2 VARCHAR2(240);
168 v_counter NUMBER := 0;
169
170
171
172
173 v_status_id NUMBER;
174 v_expire_status_id NUMBER;
175 v_terminate_status_id NUMBER;
176 -- Global var holding the User Id
177 user_id NUMBER;
178
179 -- Global var to hold the ERROR value.
180 ERROR NUMBER := 1;
181
182 -- Global var to hold the SUCCESS value.
183 SUCCESS NUMBER := 0;
184
185 -- Global var holding the Current Error code for the error encountered
186 Current_Error_Code Varchar2(20) := NULL;
187
188 -- Global var to hold the Concurrent Process return values
189 conc_ret_code NUMBER := SUCCESS;
190 v_retcode NUMBER := SUCCESS;
191 CONC_STATUS BOOLEAN;
192
193
194
195
196 CURSOR C1 IS
197 SELECT
198 CONTRACT_ID ,
199 CONTRACT_NUMBER ,
200 PRICE_LIST_ID ,
201 CURRENCY_CODE ,
202 INVOICING_RULE_ID ,
203 ACCOUNTING_RULE_ID ,
204 BILL_TO_SITE_USE_ID ,
205 CONTRACT_STATUS_ID ,
206 CONTRACT_TYPE_ID ,
207 CUSTOMER_ID ,
208 DURATION ,
209 PERIOD_CODE ,
210 START_DATE_ACTIVE ,
214 BILL_ON ,
211 END_DATE_ACTIVE ,
212 AGREEMENT_ID ,
213 BILLING_FREQUENCY_PERIOD ,
215 FIRST_BILL_DATE ,
216 NEXT_BILL_DATE ,
217 SALESPERSON_ID ,
218 ORDERED_BY_CONTACT_ID ,
219 CONTRACT_TEMPLATE_ID ,
220 CONTRACT_GROUP_ID ,
221 WORKFLOW ,
222 WORKFLOW_PROCESS_ID ,
223 CREATE_SALES_ORDER ,
224 SHIP_TO_SITE_USE_ID ,
225 RENEWAL_RULE ,
226 TERMINATION_RULE ,
227 CONVERSION_TYPE_CODE ,
228 CONVERSION_RATE ,
229 CONVERSION_DATE ,
230 SOURCE_CODE ,
231 SOURCE_REFERENCE ,
232 TERMS_ID ,
233 PO_NUMBER ,
234 TAX_HANDLING ,
235 TAX_EXEMPT_NUM ,
236 TAX_EXEMPT_REASON_CODE ,
237 CONTRACT_AMOUNT ,
238 AUTO_RENEWAL_FLAG ,
239 ORIGINAL_END_DATE ,
240 TERMINATE_REASON_CODE ,
241 DISCOUNT_ID ,
242 ATTRIBUTE1 ,
243 ATTRIBUTE2 ,
244 ATTRIBUTE3 ,
245 ATTRIBUTE4 ,
246 ATTRIBUTE5 ,
247 ATTRIBUTE6 ,
248 ATTRIBUTE7 ,
249 ATTRIBUTE8 ,
250 ATTRIBUTE9 ,
251 ATTRIBUTE10 ,
252 ATTRIBUTE11 ,
253 ATTRIBUTE12 ,
254 ATTRIBUTE13 ,
255 ATTRIBUTE14 ,
256 ATTRIBUTE15 ,
257 CONTEXT ,
258 OBJECT_VERSION_NUMBER ,
259 PO_REQUIRED_TO_SERVICE ,
260 PRE_PAYMENT_REQUIRED
261 FROM CS_CONTRACTS
262 where exp_term_date < cs_contracts.end_date_active
263 AND cs_contracts.next_bill_date IS NULL;
264
265
266
267
268 BEGIN
269 FND_FILE.PUT_NAMES('exp_term.log','expterm.out','/sqlcom/log');
270 user_id := FND_GLOBAL.USER_ID;
271 FND_FILE.PUT_LINE(FND_FILE.LOG, 'User_Id ='||
272 to_char(user_id));
273
274 OPEN C1;
275 While LOOPING LOOP
276 FETCH C1 INTO lcontract_rec;
277
278
279 IF C1%NOTFOUND THEN
280 LOOPING := FALSE;
281 ELSE
282 select count(*) into v_counter
283 from cs_cp_services_all, cs_cp_service_transactions
284 where lcontract_rec.contract_id = cs_cp_services_all.contract_id
285 and cs_cp_services_all.cp_service_id = cs_cp_service_transactions.cp_service_id;
286
287
288 IF v_counter = 0 THEN
289 v_status_id := FND_PROFILE.VALUE('CS_CONTRACTS_EXPIRED_STATUS');
290 else
291 v_status_id := FND_PROFILE.VALUE('CS_CONTRACTS_TERMINATED_STATUS');
292 END IF;
293
294
295 IF v_status_id IS NULL THEN
296 v_retcode := ERROR;
297 Current_error_Code := to_Char(SQLCODE);
298 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update unsuccessful : The v_return_status is NULL');
299 FND_FILE.PUT_LINE( FND_FILE.LOG, 'CONTRACT_ID = ' || TO_CHAR(lcontract_rec.contract_id));
300 ELSE
301
302 msg1 := 'StatusID = ' || TO_CHAR(v_status_id);
303 msg2 := 'Contract_ID = ' || TO_CHAR(lcontract_rec.contract_id);
304
305
306 Populate_Contracts(lcontract_rec,
307 contract_rec,
308 v_status_id
309 );
310
311
312 CS_CONTRACT_PVT.Update_Row
313 (
314 p_api_version => 1.0,
315 p_init_msg_list => 'T',
316 p_validation_level => 0,
317 p_commit => 'T',
318 x_return_status => v_return_status,
319 x_msg_count => v_msg_count,
320 x_msg_data => v_msg_data,
321 p_contract_val_rec => contract_rec,
322 x_object_version_number => v_object_version_number
323 );
324
325
326 --dbms_output.put_line(v_status_id);
327
328
329 IF v_return_status <> 'S' AND v_msg_count >= 1 THEN
330 v_retcode := ERROR;
331 Current_error_Code := to_Char(SQLCODE);
332 FND_FILE.PUT_LINE(FND_FILE.LOG,'Update unsuccessful : The v_return_status is' || v_return_status);
333 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLCODE );
334 FND_FILE.PUT_LINE( FND_FILE.LOG, SQLERRM );
335 ELSE
336 v_retcode := SUCCESS;
337 Current_error_Code := to_Char(SQLCODE);
338 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Update successfully completed' );
339 FND_FILE.PUT_LINE( FND_FILE.LOG, 'CONTRACT_ID = ' || TO_CHAR(lcontract_rec.contract_id));
340 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Status Changed to ' || v_status_id );
341 END IF;
342
343 END IF;
344 END IF;
345
346
347 END LOOP;
348
349 CLOSE C1;
350
351 IF v_retcode = SUCCESS THEN
352 COMMIT;
353 CONC_STATUS :=
354 FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',
355 Current_Error_Code);
359 END IF;
356 ELSE
357 CONC_STATUS :=
358 FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
360
361 FND_FILE.CLOSE;
362
363
364
365 END exp_terminate_contract;
366
367 END cs_exp_terminate_pvt;