1 PACKAGE BODY GML_OP_CUST_MST_PKG AS
2 /* $Header: GMLUPDB.pls 115.13 2002/10/18 20:55:00 gmangari ship $ */
3
4
5 /*#############################################################################
6 # PROCEDURE
7 #
8 # update_customer_balance
9 #
10 # DESCRIPTION
11 #
12 #
13 # To update the customer balances in op_cust_mst and op_updt_bal_wk
14 #
15 #
16 # MODIFICATION HISTORY
17 #
18 # 02-APR-99 Srinivas Somayajula Created.
19 # 02-DEC-99 Rajender Nalla Getting the user_id from
20 # fnd_global.user_id.
21 # 29-FEB-00 Rajender Nalla Changed the parameters to cust_no
22 # instead of CUST_ID
23 # V_from_cust_id,V_from_cust_no
24 # V_to_cust_id,V_to_cust_no
25 # 13-Sep-2002 Piyush K. Mishra Bug#2521042
26 # Modified the UPDATE statement to update the Customer's Open
27 # Balance correctly.
28 # 17-Oct-2002 Piyush K. Mishra Bug#2611290
29 # Modified the Cursor Cur_get_cust_details, since it was not
30 # working if V_from_cust_no and V_to_cust_no is being passed as
31 # NULL. Modified Update statement setting open_balance to 0
32 # and added condition so this update will be done only once for
33 # each customer.
34 ##########################################################################*/
35 PROCEDURE update_cust_balance
36 (
37 V_session_id NUMBER,
38 V_co_code VARCHAR2,
39 V_from_cust_no VARCHAR2,
40 V_to_cust_no VARCHAR2
41 ) IS
42 X_max_cust_id NUMBER;
43 X_currency VARCHAR2(10);
44 X_base_currency VARCHAR2(10);
45 X_type VARCHAR2(4);
46 X_user_id NUMBER;
47 X_exch_rate NUMBER;
48 --Begin Bug#2611290 Piyush K. Mishra
49 X_prvs_cust_id NUMBER := 0;
50 --End Bug#2611290
51
52 CURSOR Cur_max_cust_id IS
53 SELECT MAX(cust_id)
54 FROM op_cust_mst;
55
56 /*Begin Bug#2611290 Piyush K. Mishra
57 Changed the NVL used in below query, previously it was
58 NVL(V_from_cust_no, 'X') and NVL(V_from_cust_no, 'X').
59 Also commented the hdr.billcust_id = cus.cust_id as it was twice */
60 CURSOR Cur_get_cust_details IS
61 SELECT cus.cust_id,
62 cus.cust_no,
63 cus.cust_name,
64 cus.cust_currency,
65 hdr.order_id,
66 hdr.order_date,
67 hdr.billing_currency,
68 SUM(hdr.total_open_amount) total_open_amount
69 FROM op_cust_mst cus, op_ordr_hdr hdr
70 WHERE hdr.billcust_id = cus.cust_id and
71 hdr.completed_ind <> -1 and
72 hdr.delete_mark = 0 and
73 hdr.order_status < 20 and
74 -- hdr.billcust_id = cus.cust_id and
75 (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no >= NVL(V_from_cust_no, cust_no)) and
76 (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no <= NVL(V_to_cust_no, cust_no)))) and
77 cus.co_code = V_co_code
78 GROUP BY cus.cust_id,
79 cus.cust_no,
80 cus.cust_name,
81 cus.cust_currency,
82 hdr.order_id,
83 hdr.order_date,
84 hdr.billing_currency;
85 --End Bug#2611290
86
87 CURSOR Cur_get_base_curr IS
88 SELECT base_currency_code
89 FROM gl_plcy_mst
90 WHERE set_of_books_name IS NOT NULL and
91 co_code = V_co_code and
92 delete_mark = 0;
93 X_cust_details Cur_get_cust_details%ROWTYPE;
94
95
96 CURSOR Cur_get_exchange_rate(V_currency VARCHAR2) IS
97 SELECT ex.exchange_rate, ex.mul_div_sign,
98 ex.exchange_rate_date
99 FROM gl_xchg_rte ex,
100 gl_srce_mst src
101 WHERE ex.to_currency_code = X_cust_details.billing_currency and
102 ex.from_currency_code = V_currency and
103 ex.exchange_rate_date <= X_cust_details.order_date and
104 ex.rate_type_code = src.rate_type_code and
105 src.trans_source_code = 'OP' and
106 ex.delete_mark=0;
107 X_exchange_rate_rec Cur_get_exchange_rate%rowtype;
108 BEGIN
109
110 /* Bug Id 1080909 fixed. */
111 X_user_id := FND_GLOBAL.USER_ID;
112 /* End of bug 1080909. */
113 OPEN Cur_max_cust_id;
114 FETCH Cur_max_cust_id INTO X_max_cust_id;
115 CLOSE Cur_max_cust_id;
116 OPEN Cur_get_base_curr;
117 FETCH Cur_get_base_curr INTO X_base_currency;
118 CLOSE Cur_get_base_curr;
119 OPEN Cur_get_cust_detailS;
120 LOOP
121 FETCH Cur_get_cust_details INTO X_cust_details;
122 IF(Cur_get_cust_details%NOTFOUND) THEN
123 EXIT;
124 END IF;
125 IF(X_cust_details.cust_currency IS NOT NULL ) THEN
126 X_currency := X_cust_details.cust_currency;
127 ELSE
128 X_currency := X_base_currency;
129 END IF;
130 IF(X_currency IS NULL) THEN
131 INSERT INTO op_updt_bal_wk (session_id, cust_no, cust_name, error_message,
132 created_by, creation_date, last_update_date, last_updated_by,
133 last_update_login) VALUES
134 (
135 V_session_id,
136 X_cust_details.cust_no,
137 X_cust_details.cust_name,
138 'Base currency not available',
139 X_user_id,
140 sysdate,
141 sysdate,
142 X_user_id,
143 -1
144 );
145 ELSE
146 IF (X_currency = X_cust_details.billing_currency) THEN
147 X_exch_rate := 1;
148 ELSE
149 OPEN Cur_get_exchange_rate(X_currency);
150 FETCH Cur_get_exchange_rate INTO X_exchange_rate_rec;
151 CLOSE Cur_get_exchange_rate;
152 X_exch_rate := X_exchange_rate_rec.exchange_rate;
153 END IF;
154 IF(X_exch_rate IS NULL) THEN
155 INSERT INTO op_updt_bal_wk (session_id, cust_no, cust_name, error_message,
156 created_by, creation_date, last_update_date, last_updated_by,
157 last_update_login) VALUES
158 (
159 V_session_id,
160 X_cust_details.cust_no,
161 X_cust_details.cust_name,
162 'Exchange rate does not exist for this customer',
163 X_user_id,
164 sysdate,
165 sysdate,
166 X_user_id,
167 -1
168 );
169 ELSE
170 IF(X_exchange_rate_rec.mul_div_sign = '1') THEN
171 X_cust_details.total_open_amount :=
172 X_cust_details.total_open_amount/X_exch_rate;
173 ELSE
174 X_cust_details.total_open_amount := X_cust_details.total_open_amount*
175 X_exch_rate;
176 END IF;
177 END IF;
178
179 /*Begin Bug#2611290 Piyush K. Mishra
180 Added IF condition and modified the where condition, it was wrong.
181 This update statement updates the open balance of the customers (fetched
182 in the loop for whom the open balances exist) to zero. This is required
183 as the balances should be calculated and updated every time the program
184 is executed. This should be executed only once per customer before updating
185 the open balance with the open amounts from sales orders */
186
187 IF X_prvs_cust_id <> X_cust_details.cust_id THEN
188 UPDATE op_cust_mst
189 SET open_balance = 0
190 WHERE bill_ind = 1
191 AND co_code = V_co_code
192 AND cust_id = X_cust_details.cust_id;
193 /*Commented following conditions
194 AND cust_id NOT IN
195 (SELECT cust_id
196 FROM op_cust_mst cus,op_ordr_hdr hdr
197 WHERE hdr.billcust_id = cus.cust_id
198 AND hdr.completed_ind <> -1
199 AND hdr.delete_mark = 0
200 AND hdr.order_status < 20
201 AND hdr.billcust_id = cus.cust_id
202 AND (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no >= NVL(V_from_cust_no,'X'))
203 AND (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no <= NVL(V_to_cust_no,'X')))));*/
204 X_prvs_cust_id := X_cust_details.cust_id;
205 END IF;
206 /*End Bug#2611290*/
207
208 --Begin Bug#2521042 Piyush K. Mishra
209 --Open_balance should be updated with open_balance + X_cust_details.total_open_amount
210 UPDATE op_cust_mst
211 SET open_balance = Open_balance + X_cust_details.total_open_amount
212 WHERE cust_id = X_cust_details.cust_id
213 AND co_code = V_co_code;
214 --End Bug#2521042
215
216 END IF;
217 END LOOP;
218 CLOSE Cur_get_cust_details;
219 /* Begin Bug#2611290 Piyush K. Mishra */
220 /* This update statement updates the open balances to zero, for the customers for whom no
221 open amounts exist and are within the range criteria. The customers fetched in the above
222 loop are excluded in this update. */
223 UPDATE op_cust_mst
224 SET open_balance = 0
225 WHERE bill_ind = 1
226 AND co_code = V_co_code
227 AND cust_id NOT IN
228 (SELECT cust_id
229 FROM op_cust_mst cus,op_ordr_hdr hdr
230 WHERE hdr.billcust_id = cus.cust_id
231 AND hdr.completed_ind <> -1
232 AND hdr.delete_mark = 0
233 AND hdr.order_status < 20
234 AND (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no >= NVL(V_from_cust_no,cust_no))
235 AND (cus.cust_id IN(SELECT cust_id from op_cust_mst where cust_no <= NVL(V_to_cust_no,cust_no)))))
236 AND (cust_id IN(SELECT cust_id from op_cust_mst where cust_no >= NVL(V_from_cust_no, cust_no))
237 AND (cust_id IN(SELECT cust_id from op_cust_mst where cust_no <= NVL(V_to_cust_no, cust_no))));
238 /* End Bug#2611290 */
239
240 END UPDATE_CUST_BALANCE;
241
242 END GML_OP_CUST_MST_PKG;
[Home] [Help]