[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_REQUEST_PVT
Source
1 PACKAGE BODY HZ_CREDIT_REQUEST_PVT AS
2 -- $Header: OEXCRRQB.pls 120.0.12010000.2 2008/08/04 14:58:46 amallik ship $
3
4 --------------------
5 -- TYPE DECLARATIONS
6 --------------------
7
8 ------------
9 -- CONSTANTS
10 ------------
11
12 -------------------------
13 -- PUBLIC VARIABLES
14 -------------------------
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_CREDIT_REQUEST_PVT';
16 ----------------------
17 -- PRIVATE VARIABLES
18 ----------------------
19 ---------------------------
20 -- PROCEDURES AND FUNCTIONS
21 ---------------------------
22
23 ----------------------------------------------------------------
24 --This is rule function, that is subscribed to the Oracle Workflow
25 -- Business Event CreditRequest.Recommendation.implement
26 --to implement recomendations of the AR CRedit Management Review
27 ----------------------------------------------------------------
28 FUNCTION Rule_Credit_Recco_Impl
29 ( p_subscription_guid In RAW
30 , p_event IN OUT NOCOPY WF_EVENT_T
31 )
32 RETURN VARCHAR2
33 IS
34 l_key VARCHAR2(240);
35 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type;
36 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
37 l_credit_check_rule_id NUMBER;
38 l_limit_currency AR_CMGT_CREDIT_REQUESTS.limit_currency%TYPE;
39 l_trx_amount NUMBER;
40 l_trx_currency AR_CMGT_CREDIT_REQUESTS.trx_currency%TYPE;
41 l_source_column1 NUMBER;
42 l_source_column2 NUMBER;
43 l_source_column3 VARCHAR2(30);
44 l_party_id NUMBER;
45 l_cust_account_id NUMBER;
46 l_site_use_id NUMBER;
47
48 l_new_amount NUMBER;
49
50 l_cc_result_out VARCHAR2(30);
51 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE ;
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54 l_return_status VARCHAR2(30);
55 l_credit_request_id NUMBER;
56 l_user_id NUMBER;
57 l_resp_id NUMBER;
58 l_resp_appl_id NUMBER;
59 l_security_group_id NUMBER;
60 l_count NUMBER;
61 l_source_org_id NUMBER; -- bug 7120635
62
63 -- note: This cursor does not take payment type into account for 11.5.10
64 CURSOR billto_lines_csr(p_site_use_id NUMBER,p_header_id NUMBER)
65 IS
66 SELECT
67 l.line_id
68 , l.line_number
69 FROM
70 oe_order_lines_all l
71 , ra_terms_b t
72 WHERE l.invoice_to_org_id = p_site_use_id
73 AND l.header_id = p_header_id
74 AND l.open_flag = 'Y'
75 AND l.booked_flag = 'Y'
76 AND NVL(l.invoiced_quantity,0) = 0
77 AND NVL(l.shipped_quantity,0) = 0
78 AND l.line_category_code = 'ORDER'
79 AND l.payment_term_id = t.term_id
80 AND t.credit_check_flag = 'Y'
81 ORDER BY l.line_id;
82
83 -- 4299254
84 CURSOR lines_csr(p_header_id NUMBER)
85 IS
86 SELECT
87 l.line_id
88 , l.line_number
89 FROM
90 oe_order_lines_all l
91 , ra_terms_b t
92 WHERE l.header_id = p_header_id
93 AND l.open_flag = 'Y'
94 AND l.booked_flag = 'Y'
95 AND NVL(l.invoiced_quantity,0) = 0
96 AND NVL(l.shipped_quantity,0) = 0
97 AND l.line_category_code = 'ORDER'
98 AND l.payment_term_id = t.term_id
99 AND t.credit_check_flag = 'Y'
100 ORDER BY l.line_id;
101
102 BEGIN
103
104 l_key := p_event.GetEventKey();
105 l_credit_request_id := p_event.GetValueForParameter('CREDIT_REQUEST_ID');
106 l_source_column1 := p_event.GetValueForParameter('SOURCE_COLUMN1');
107 l_source_column2 := p_event.GetValueForParameter('SOURCE_COLUMN2');
108 l_source_column3 := p_event.GetValueForParameter('SOURCE_COLUMN3');
109
110 --check that the recomendation exist
111
112 SELECT COUNT(1)
113 INTO l_count
114 FROM ar_cmgt_cf_recommends
115 WHERE credit_request_id = l_credit_request_id
116 AND credit_recommendation = 'REMOVE_ORDER_HOLD'
117 AND status = 'I';
118
119 -- proceed if recommendation is to release order
120 IF l_count > 0 THEN
121 -- Get the credit management information required to release the hold
122 BEGIN
123 SELECT
124 limit_currency
125 , credit_check_rule_id
126 , trx_currency
127 , trx_amount
128 , party_id
129 , cust_account_id
130 , site_use_id --4299254
131 , source_user_id
132 , source_resp_id
133 , source_resp_appln_id
134 , source_security_group_id
135 , source_org_id -- Bug 7120635
136 INTO l_limit_currency
137 , l_credit_check_rule_id
138 , l_trx_currency
139 , l_trx_amount
140 , l_party_id
141 , l_cust_account_id
142 , l_site_use_id
143 , l_user_id
144 , l_resp_id
145 , l_resp_appl_id
146 , l_security_group_id
147 , l_source_org_id -- Bug 7120635
148 FROM ar_cmgt_credit_requests
149 WHERE credit_request_id = l_credit_request_id;
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END;
154
155 -- set security context
156 FND_GLOBAL.apps_initialize
157 ( l_user_id
158 , l_resp_id
159 , l_resp_appl_id
160 , l_security_group_id
161 );
162
163 -- for 7120635
164 MO_GLOBAL.set_policy_context('S', l_source_org_id);
165 --MO_GLOBAL.INIT('ONT');
166
167 --Populate credit check rule record
168 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
169 ( p_header_id => l_source_column1
170 , p_credit_check_rule_id => l_credit_check_rule_id
171 , x_credit_check_rules_rec => l_credit_check_rule_rec
172 );
173
174 -- for order level hold we will call
175 IF l_source_column3 = 'ORDER' THEN
176 -- Get new transactional amount
177 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
178 ( p_header_id => l_source_column1
179 , p_transaction_curr_code => l_trx_currency
180 , p_credit_check_rule_rec => l_credit_check_rule_rec
181 , p_system_parameter_rec => NULL
182 , p_customer_id => l_cust_account_id
183 , p_site_use_id => l_site_use_id
184 , p_limit_curr_code => l_limit_currency
185 , x_amount => l_new_amount
186 , x_conversion_status => l_conversion_status
187 , x_return_status => l_return_status
188 );
189
190 IF l_return_status = FND_API.G_RET_STS_ERROR
191 THEN
192 RAISE FND_API.G_EXC_ERROR;
193 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
194 THEN
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 --check if the order_amount has been changed
199 --if amount has not been changed, release the hold
200 --should raise message indicating that amount has changed and hold is not release.
201
202 IF l_new_amount = l_trx_amount THEN
203 OE_CREDIT_CHECK_ORDER_PVT.Release_Order_CC_Hold
204 ( p_header_id => l_source_column1
205 , p_order_number => l_source_column2
206 , p_calling_action => NULL
207 , p_system_parameter_rec=> NULL
208 , x_cc_result_out => l_cc_result_out
209 );
210 END IF;
211 ELSE --for line level hold
212 --4299254: If the order goes on hold using site level limits, release hold
213 --for the lines corresponding to that bill to site, otherwise release the hold
214 --for all the lines.
215 -- Credit Management inserts site_use_id as -99,if OM pass it as NULL.
216 IF nvl(l_site_use_id,-99) > 0 THEN
217 --Get new transactional amount for this bill to site..
218 --Bug 4377933: If customer_id is passed as NULL,then this API returns the transaction
219 --amount for site..
220 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
221 ( p_header_id => l_source_column1
222 , p_transaction_curr_code => l_trx_currency
223 , p_credit_check_rule_rec => l_credit_check_rule_rec
224 , p_system_parameter_rec => NULL
225 , p_customer_id => NULL
226 , p_site_use_id => l_site_use_id
227 , p_limit_curr_code => l_limit_currency
228 , x_amount => l_new_amount
229 , x_conversion_status => l_conversion_status
230 , x_return_status => l_return_status
231 );
232 IF l_return_status = FND_API.G_RET_STS_ERROR
233 THEN
234 RAISE FND_API.G_EXC_ERROR;
235 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
236 THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239 --Check if amount per bill-to site has been changed
240 --if amount has not been changed, release holds for bill_to site
241 IF l_new_amount = l_trx_amount THEN
242 --OPEN the cursor for sites
243 FOR c_line IN billto_lines_csr(p_site_use_id =>l_site_use_id
244 ,p_header_id =>l_source_column1)
245 LOOP
246 OE_CREDIT_CHECK_LINES_PVT.Release_Line_CC_Hold
247 ( p_header_id => l_source_column1
248 , p_order_number => l_source_column2
249 , p_line_id => c_line.line_id
250 , p_line_number => c_line.line_number
251 , p_calling_action => NULL
252 , p_credit_hold_level => 'LINE'
253 , x_cc_result_out => l_cc_result_out
254 );
255 END LOOP;
256 END IF;
257 ELSE --If the order goes on hold using customer credit limits
258 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
259 ( p_header_id => l_source_column1
260 , p_transaction_curr_code => l_trx_currency
261 , p_credit_check_rule_rec => l_credit_check_rule_rec
262 , p_system_parameter_rec => NULL
263 , p_customer_id => l_cust_account_id
264 , p_site_use_id => l_site_use_id
265 , p_limit_curr_code => l_limit_currency
266 , x_amount => l_new_amount
267 , x_conversion_status => l_conversion_status
268 , x_return_status => l_return_status
269 );
270 IF l_return_status = FND_API.G_RET_STS_ERROR
271 THEN
272 RAISE FND_API.G_EXC_ERROR;
273 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277 IF l_new_amount = l_trx_amount THEN
278 --OPEN the cursor for all lines
279 FOR c_line IN lines_csr(p_header_id =>l_source_column1)
280 LOOP
281 OE_CREDIT_CHECK_LINES_PVT.Release_Line_CC_Hold
282 ( p_header_id => l_source_column1
283 , p_order_number => l_source_column2
284 , p_line_id => c_line.line_id
285 , p_line_number => c_line.line_number
286 , p_calling_action => NULL
287 , p_credit_hold_level => 'LINE'
288 , x_cc_result_out => l_cc_result_out
289 );
290 END LOOP;
291 END IF;
292 END IF; -- end of check if site_use_id is passed.
293 END IF; --end of check if hold is line level or order level
294 END IF; --end of check if recommendation is to release hold
295
296 RETURN 'SUCCESS';
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
301 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
302 FND_MSG_PUB.ADD;
303 WF_CORE.CONTEXT('HZ_CREDIT_REQUEST_PVT',
304 'Rule_Credit_Recco_Impl',
305 p_event.getEventName(),
306 p_subscription_guid);
307 WF_EVENT.setErrorInfo(p_event, 'ERROR');
308
309 RETURN 'ERROR';
310 END Rule_Credit_Recco_Impl;
311
312
313
314 END HZ_CREDIT_REQUEST_PVT ;