[Home] [Help]
PACKAGE BODY: APPS.CS_CALCULATE_CREDIT
Source
1 PACKAGE BODY CS_CALCULATE_CREDIT as
2 /* $Header: csxstsvb.pls 115.2 99/07/16 09:09:11 porting ship $ */
3
4 procedure CS_CALCULATE_CREDIT(x_cp_service_id IN NUMBER,
5 x_terminate_effective_date IN DATE,
6 x_first_cp_service_txn_id IN OUT NUMBER,
7 x_first_amount IN OUT NUMBER,
8 x_total_credit_amount IN OUT NUMBER,
9 x_total_credit_percent IN OUT NUMBER,
10 x_multi_txns In OUT VARCHAR2) IS
11
12 days_in_original_service NUMBER;
13 days_in_terminated_service NUMBER;
14 first_txn_credit NUMBER;
15 first_cp_service_txn_id NUMBER;
16 txn_end DATE;
17 current_end DATE;
18 available_credit NUMBER;
19 term_cp_serv_txn_id NUMBER;
20 remain_credit_amount NUMBER;
21 first_amount NUMBER;
22 total_serv_amount NUMBER;
23 x_service_start_date DATE;
24 x_precision NUMBER;
25 x_ext_precision NUMBER;
26 x_Min_acct_unit NUMBER;
27
28 BEGIN
29
30 /* Why not INVOICED transactions only ? */
31
32 SELECT MAX(t.cp_service_transaction_id)
33 INTO x_first_cp_service_txn_id
34 FROM cs_cp_service_transactions t
35 WHERE t.cp_service_id = x_cp_service_id
36 AND t.effective_start_date <= x_terminate_effective_date
37 AND t.effective_end_date >= x_terminate_effective_date
38 AND t.transaction_type_code in ('RENEW', 'ORDER');
39
40 SELECT (t.current_end_date) - (t.effective_start_date),
41 (t.current_end_date) - (x_terminate_effective_date),
42 t.effective_end_date, t.current_end_date
43 INTO days_in_original_service,
44 days_in_terminated_service,
45 txn_end,
46 current_end
47 FROM cs_cp_service_transactions t
48 WHERE t.cp_service_transaction_id = x_first_cp_service_txn_id;
49
50 SELECT (MAX(t.service_selling_price * cp.quantity) -
51 SUM(NVL(t2.credit_amount,0)))
52 INTO available_credit
53 FROM cs_cp_service_transactions t,
54 cs_cp_service_transactions t2,
55 cs_cp_services s,
56 cs_customer_products cp
57 WHERE t.cp_service_transaction_id = x_first_cp_service_txn_id
58 AND t.cp_service_transaction_id = t2.terminated_transaction_id(+)
59 AND t.cp_service_id = s.cp_service_id
60 AND s.customer_product_id = cp.customer_product_id;
61
62 IF (current_end IS NOT NULL) THEN
63 term_cp_serv_txn_id := x_first_cp_service_txn_id;
64 ELSE
65 SELECT MAX(cp_service_transaction_id)
66 INTO term_cp_serv_txn_id
67 FROM cs_cp_service_transactions
68 WHERE transaction_type_code = 'TERMINATE'
69 AND terminated_transaction_id = x_first_cp_service_txn_id
70 AND cp_service_id = x_cp_service_id;
71 END IF;
72
73 SELECT NVL(SUM(NVL(t.service_selling_price * cp.quantity , 0)) -
74 SUM(NVL(t.credit_amount, 0)),0)
75 INTO remain_credit_amount
76 FROM cs_cp_service_transactions t,
77 cs_cp_services s,
78 cs_customer_products cp
79 WHERE t.cp_service_transaction_id > term_cp_serv_txn_id
80 AND t.cp_service_id = s.cp_service_id
81 AND t.cp_service_id = x_cp_service_id
82 AND t.ra_interface_status = 'INV'
83 AND t.transaction_type_code in ('ORDER', 'RENEW', 'TERMINATE')
84 AND s.customer_product_id = cp.customer_product_id;
85
86 IF (remain_credit_amount = 0) THEN
87 x_multi_txns := 'N';
88 ELSE
89 x_multi_txns := 'Y';
90 END IF;
91
92 IF (days_in_original_service <> 0) THEN
93 x_total_credit_amount := (available_credit *
94 (days_in_terminated_service /
95 days_in_original_service)) +
96 (remain_credit_amount);
97
98 x_first_amount := (available_credit *
99 (days_in_terminated_service /
100 days_in_original_service));
101
102 ELSE
103 x_total_credit_amount := 0;
104 END IF;
105
106 total_serv_amount := available_credit + remain_credit_amount;
107
108 IF (total_serv_amount <> 0) THEN
109 x_total_credit_percent := (((x_first_amount +
110 remain_credit_amount) * 100) /
111 total_serv_amount);
112 ELSE
113 x_total_credit_percent := 0 ;
114 END IF ;
115
116
117 EXCEPTION
118 when NO_DATA_FOUND then
119 FND_MESSAGE.Set_Name('CS', 'CS_CALCULATE_CREDIT_FAILED');
120 APP_EXCEPTION.Raise_Exception;
121
122 END;
123
124
125 PROCEDURE CREATE_INTERACTION_FROM_FORM(control_user_id IN NUMBER,
126 cp_cp_service_id IN NUMBER,
127 parent_interaction_id IN VARCHAR2,
128 cp_last_update_login IN NUMBER,
129 cp_bill_to_contact_id IN NUMBER,
130 return_status OUT VARCHAR2,
131 return_msg OUT VARCHAR2) IS
132
133 l_ret_status VARCHAR2(1);
134 l_msg_count NUMBER;
135 l_interaction_id NUMBER;
136 l_msg_data VARCHAR2(1000);
137 l_ineraction_id NUMBER;
138 l_customer_id NUMBER;
139 l_employee_id NUMBER;
140 l_validation_level NUMBER;
141
142
143 BEGIN
144
145 SELECT employee_id
146 INTO l_employee_id
147 FROM FND_USER
148 WHERE user_id = control_user_id;
149
150
151 SELECT customer_id
152 INTO l_customer_id
153 FROM cs_customer_products
154 WHERE customer_product_id = (SELECT customer_product_id
155 FROM cs_cp_services
156 WHERE cp_service_id = cp_cp_service_id);
157
158 return_status := NULL;
159 return_msg := NULL;
160 IF l_customer_id IS NOT NULL THEN
161
162 CS_Interaction_PVT.Create_Interaction
163 (p_api_version => 1.0,
164 p_init_msg_list => FND_API.G_TRUE,
165 p_commit => FND_API.G_FALSE,
166 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
167 x_return_status => l_ret_status,
168 x_msg_count => l_msg_count,
169 x_msg_data => l_msg_data,
170 p_resp_appl_id => NULL,
171 p_resp_id => NULL,
172 p_user_id => control_user_id,
173 p_login_id => cp_last_update_login,
174 p_org_id => FND_PROFILE.Value('ORG_ID'),
175 p_customer_id => l_customer_id,
176 p_contact_id => cp_bill_to_contact_id,
177 p_contact_lastname => NULL,
178 p_contact_firstname => NULL,
179 p_phone_area_code => NULL,
180 p_phone_number => NULL,
181 p_phone_extension => NULL,
182 p_fax_area_code => NULL,
183 p_fax_number => NULL,
184 p_email_address => NULL,
185 p_interaction_type_code => 'SRV_TER',
186 p_interaction_category_code => 'CS',
187 p_interaction_method_code => 'SYSTEM',
188 p_interaction_date => SYSDATE,
189 p_interaction_document_code => NULL,
190 p_source_document_id => NULL,
191 p_source_document_name => NULL,
192 p_reference_form => NULL,
193 p_source_document_status => NULL,
194 p_employee_id => l_employee_id,
195 p_public_flag => NULL,
196 p_follow_up_action => NULL,
197 p_notes => NULL,
198 p_parent_interaction_id => parent_interaction_id,
199 p_attribute1 => NULL,
200 p_attribute2 => NULL,
201 p_attribute3 => NULL,
202 p_attribute4 => NULL,
203 p_attribute5 => NULL,
204 p_attribute6 => NULL,
205 p_attribute7 => NULL,
206 p_attribute8 => NULL,
207 p_attribute9 => NULL,
208 p_attribute10 => NULL,
209 p_attribute11 => NULL,
210 p_attribute12 => NULL,
211 p_attribute13 => NULL,
212 p_attribute14 => NULL,
213 p_attribute15 => NULL,
214 p_attribute_category => NULL,
215 x_interaction_id => l_interaction_id);
216 return_status := l_ret_status;
217 return_msg := l_msg_data;
218 END IF;
219
220 IF (return_status = FND_API.G_RET_STS_ERROR OR
221 return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
222 -- 1 meaning error, 0 meaning OK
223 return_status := '1';
224 END IF;
225
226
227 END CREATE_INTERACTION_FROM_FORM;
228
229 END CS_CALCULATE_CREDIT;
230