[Home] [Help]
PACKAGE: APPS.OE_CREDIT_CHECK_UTIL
Source
1 PACKAGE OE_CREDIT_CHECK_UTIL AUTHID CURRENT_USER AS
2 -- $Header: OEXUCRCS.pls 120.6 2012/01/01 18:29:04 slagiset ship $
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OEXUCRCS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package Spec of OE_CREDIT_CHECK_UTIL |
13 --| |
14 --| PROCEDURE LIST |
15 --| Get_Credit_Check_Rule_ID |
16 --| Get_Credit_Check_Rule |
17 --| Get_Order_Exposure |
18 --| Get_Limit_Info |
19 --| Get_System_Parameters |
20 --| Get_External_Trx_Amount |
21 --| |
22 --| HISTORY |
23 --| Oct-30-2001 Global for curent order exposure |
24 --| Feb-04-2002 Multi org |
25 --| Feb-13-2002 Check External Credit API changes |
26 --| FEB-21-2001 added days_honor_manual release to |
27 --| OE_credit_rules_rec_type |
28 --| Mar-15-2002 Modified to support external exposure |
29 --| May-23-2002 rajkrish Bug2388454 |
30 --| Nov-04-2002 |
34 --| rule type |
31 --| Mar-30-2003 vto Bug 2846473.2878410. Added parameters to |
32 --| Send_Credit_Hold_NTF procedure |
33 --| Jul-18-2003 tsimmond added include_returns_flag to the credit check|
35 --| Jan-09-2004 vto 3364726/3327637:Set G_crmgmt_installed to NULL|
36 --|=======================================================================+
37
38 --------------------------------
39 --- Globals
40 -------------------------------
41 g_current_order_value NUMBER ;
42 G_excl_curr_list VARCHAR2(2000);
43 G_hierarchy_type VARCHAR2(100) :=
44 FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE');
45 G_crmgmt_installed BOOLEAN;
46 G_org_id NUMBER ; /* MOAC CREDIT CHECK CHANGE */
47 --ER 12363706 start
48 TYPE G_CC_Invoice_Rec
49 IS
50 RECORD
51 (
52 new_invoice_to_org_id NUMBER,
53 old_invoice_to_org_id NUMBER,
54 line_id NUMBER);
55 TYPE G_CC_Invoice_tab_typ
56 IS
57 TABLE OF G_CC_Invoice_Rec INDEX BY BINARY_INTEGER;
58 G_CC_Invoice_tab G_CC_Invoice_tab_typ;
59 --ER 12363706 end
60 ---------------------------
61 --- Type Declaration
62 ----------------------------
63
64
65 TYPE lines_Rectype IS RECORD
66 ( grouping_id NUMBER
67 , item_category_id NUMBER
68 , line_id OE_ORDER_LINES_ALL.line_id%TYPE
69 , ordered_quantity NUMBER
70 , tax_value NUMBER
71 , unit_selling_price NUMBER
72 );
73
74
75 TYPE lines_Rec_tbl_type IS TABLE OF lines_Rectype
76 INDEX BY BINARY_INTEGER;
77
78
79 TYPE Items_Limit_Rec IS RECORD
80 ( grouping_id NUMBER
81 , item_category_id HZ_CREDIT_PROFILES.item_category_id%type
82 , limit_curr_code HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE
83 , item_limit HZ_CREDIT_PROFILE_AMTS.trx_credit_limit%TYPE
84 , ctg_line_amount NUMBER
85 );
86
87
88 TYPE ITEM_LIMITS_TBL_TYPE IS TABLE OF Items_Limit_Rec
89 INDEX BY BINARY_INTEGER;
90
91
92 TYPE Usage_Curr_Rec IS RECORD
93 ( usage_curr_code HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE );
94
95
96 TYPE CURR_TBL_TYPE IS TABLE OF Usage_Curr_Rec
97 INDEX BY BINARY_INTEGER;
98
99 -- Start Sys Parama Change
100 /*
101 TYPE OE_systems_param_rec_type IS RECORD
102 ( org_id oe_system_parameters_ALL.org_id%TYPE
103 , master_organization_id
104 oe_system_parameters_ALL.master_organization_id%TYPE
105 , customer_relationships_flag
106 oe_system_parameters_ALL.customer_relationships_flag%TYPE
107 );
108 */
109 TYPE OE_systems_param_rec_type IS RECORD
110 ( org_id NUMBER
111 , master_organization_id NUMBER
112 , customer_relationships_flag VARCHAR2(240)
113 );
114
115 -- End Sys Param Change
116
117 TYPE OE_credit_rules_rec_type IS RECORD
118 ( credit_check_rule_id
119 OE_Credit_Check_Rules.credit_check_rule_id%TYPE
120 , name OE_Credit_Check_Rules.name%TYPE
121 , failure_result_code
122 OE_Credit_Check_Rules.failure_result_code%TYPE
123 , open_ar_balance_flag
124 OE_Credit_Check_Rules.open_ar_balance_flag%TYPE
125 , uninvoiced_orders_flag
126 OE_Credit_Check_Rules.uninvoiced_orders_flag%TYPE
127 , orders_on_hold_flag
128 OE_Credit_Check_Rules.orders_on_hold_flag%TYPE
129 , shipping_interval
130 OE_Credit_Check_Rules.shipping_interval%TYPE
131 , open_ar_days
132 OE_Credit_Check_Rules.open_ar_days%TYPE
133 , start_date_active
134 OE_Credit_Check_Rules.start_date_active%TYPE
135 , end_date_active
136 OE_Credit_Check_Rules.end_date_active%TYPE
137 , include_payments_at_risk_flag
138 OE_Credit_Check_Rules.include_payments_at_risk_flag%TYPE
139 , include_tax_flag
140 OE_Credit_Check_Rules.include_tax_flag%TYPE
141 , maximum_days_past_due
142 OE_Credit_Check_Rules.maximum_days_past_due%TYPE
143 , quick_cr_check_flag
144 OE_Credit_Check_Rules.QUICK_CR_CHECK_FLAG%TYPE
145 , incl_freight_charges_flag
146 OE_Credit_Check_Rules.incl_freight_charges_flag%TYPE
147 , shipping_horizon DATE
148 , credit_check_level_code
149 OE_Credit_Check_Rules.credit_check_level_code%TYPE
150 , credit_hold_level_code
151 OE_Credit_Check_Rules.credit_hold_level_code%TYPE
152 , conversion_type
153 OE_Credit_Check_Rules.conversion_type%TYPE
154 , user_conversion_type
155 GL_DAILY_CONVERSION_TYPES.user_conversion_type%TYPE
156 , check_item_categories_flag
157 OE_Credit_Check_Rules.check_item_categories_flag%TYPE
158 , send_hold_notifications_flag
159 OE_Credit_Check_Rules.send_hold_notifications_flag%TYPE
160 , days_honor_manual_release
161 OE_Credit_Check_Rules.days_honor_manual_release%TYPE
162 , include_external_exposure_flag
163 OE_Credit_Check_Rules.include_external_exposure_flag%TYPE
164 , include_returns_flag
165 OE_Credit_Check_Rules.include_returns_flag%TYPE
166 --ER 12363706 start
167 , tolerance_percentage
168 OE_Credit_Check_Rules.Tolerance_Percentage%TYPE
169 , tolerance_curr_code
170 OE_Credit_Check_Rules.Tolerance_Curr_Code%TYPE
171 , tolerance_amount
172 OE_Credit_Check_Rules.Tolerance_Amount%TYPE
173 --ER 12363706 end
174 );
175 FUNCTION check_debug_flag
176 RETURN VARCHAR2 ;
177
178 PROCEDURE get_limit_info (
179 p_header_id IN NUMBER := NULL
180 , p_entity_type IN VARCHAR2
181 , p_entity_id IN NUMBER
185 HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE
182 , p_cust_account_id IN NUMBER
183 , p_party_id IN NUMBER
184 , p_trx_curr_code IN
186 , p_suppress_unused_usages_flag IN VARCHAR2 := 'N'
187 , p_navigate_to_next_level IN VARCHAR2 := 'Y'
188 , p_precalc_exposure_used IN VARCHAR2 := 'N'
189 , x_limit_curr_code OUT NOCOPY
190 HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE
191 , x_trx_limit OUT NOCOPY NUMBER
192 , x_overall_limit OUT NOCOPY NUMBER
193 , x_include_all_flag OUT NOCOPY VARCHAR2
194 , x_usage_curr_tbl OUT NOCOPY
195 OE_CREDIT_CHECK_UTIL.curr_tbl_type
196 , x_default_limit_flag OUT NOCOPY VARCHAR2
197 , x_global_exposure_flag OUT NOCOPY VARCHAR2
198 , x_credit_limit_entity_id OUT NOCOPY NUMBER
199 , x_credit_check_level OUT NOCOPY VARCHAR2
200 )
201 ;
202
203
204
205 ------------------------------------------------------------------------------
206 -- PROCEDURE : Get_Usages PUBLIC
207 -- COMMENT : Returns the MCC Usages associated with a profile
208 --
209 ------------------------------------------------------------------------------
210 PROCEDURE Get_Usages (
211 p_entity_type IN VARCHAR2
212 , p_entity_id IN NUMBER
213 , p_limit_curr_code IN
214 HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE
215 , p_suppress_unused_usages_flag IN VARCHAR2 := 'N'
216 , p_default_limit_flag IN VARCHAR2 := 'N'
217 , p_global_exposure_flag IN VARCHAR2 := 'N'
218 , x_include_all_flag OUT NOCOPY VARCHAR2
219 , x_usage_curr_tbl OUT NOCOPY
220 OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE
221 );
222
223
224 -----------------------------------------------------------------------------
225 -- PROCEDURE : GET_Item_Limit PUBLIC
226 -- COMMENT : Returns the limit associated with the Item categories.
227 --
228 ------------------------------------------------------------------------------
229 PROCEDURE GET_Item_Limit
230 ( p_header_id IN NUMBER
231 , p_trx_curr_code IN VARCHAR2
232 , p_site_use_id IN NUMBER
233 , p_include_tax_flag IN VARCHAR2
234 , x_item_limits_tbl OUT NOCOPY
235 OE_CREDIT_CHECK_UTIL.item_limits_tbl_type
236 , x_lines_tbl OUT NOCOPY
237 OE_CREDIT_CHECK_UTIL.lines_Rec_tbl_type
238 );
239
240 -----------------------------------------------------------------------------
241 -- PROCEDURE: GET_System_parameters PUBLIC
242 -- COMMENT : Returns the OE system parameter info for the current org
243 --
244 ------------------------------------------------------------------------------
245 PROCEDURE GET_System_parameters
246 ( x_system_parameter_rec OUT NOCOPY
247 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
248 );
249
250
251
252 -----------------------------------------------------------------------------
253 -- PROCEDURE: GET_credit_check_rules PUBLIC
254 -- COMMENT : Returns the OE credit check rules info for the current org
255 --
256 ------------------------------------------------------------------------------
257 PROCEDURE GET_credit_check_rule
258 ( p_header_id IN NUMBER := NULL
259 , p_credit_check_rule_id IN NUMBER
260 , x_credit_check_rules_rec OUT NOCOPY
261 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
262 );
263
264 -----------------------------------------------------------------------------
265 -- PROCEDURE : Rounded_Amount PUBLIC
266 -- COMMENT : Returns the rounded amount
267 -- FPBUG 4320650
268 ---------------------------------------------------------------------------
269
270 PROCEDURE Rounded_Amount
271 ( p_currency_code IN VARCHAR2
272 , p_unrounded_amount IN NUMBER
273 , x_rounded_amount OUT NOCOPY NUMBER
274 );
275
276 -----------------------------------------------------------------------------
277 -- PROCEDURE: GET_transaction_amount PUBLIC
278 -- COMMENT : Returns the transaction amount for a given order. If the
279 -- p_site_use_id IS null, the entire order is considered
280 -- x_conversion_status proviees any currency conversion
281 -- error.
282 ------------------------------------------------------------------------------
283 PROCEDURE GET_transaction_amount
284 ( p_header_id IN NUMBER
285 , p_transaction_curr_code IN VARCHAR2
286 , p_credit_check_rule_rec IN
287 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
288 , p_system_parameter_rec IN
289 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
290 , p_customer_id IN NUMBER
291 , p_site_use_id IN NUMBER
292 , p_limit_curr_code IN VARCHAR2
293 , p_all_lines IN VARCHAR2 := 'N' --ER 12363706
294
298 );
295 , x_amount OUT NOCOPY NUMBER
296 , x_conversion_status OUT NOCOPY OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE
297 , x_return_status OUT NOCOPY VARCHAR2
299
300 --========================================================================
301 -- PROCEDURE : Get_Past_Due_Invoice
302 -- Comments : Returns Yes, if Invoices with past due date exist
303 --========================================================================
304 PROCEDURE Get_Past_Due_Invoice
305 ( p_customer_id IN NUMBER
306 , p_site_use_id IN NUMBER
307 , p_party_id IN NUMBER
308 , p_credit_check_rule_rec IN
309 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
310 , p_system_parameter_rec IN
311 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
312 , p_credit_level IN VARCHAR2
313 , p_usage_curr IN oe_credit_check_util.curr_tbl_type
314 , p_include_all_flag IN VARCHAR2
315 , p_global_exposure_flag IN VARCHAR2 := 'N'
316 , x_exist_flag OUT NOCOPY VARCHAR2
317 , x_return_status OUT NOCOPY VARCHAR2
318 );
319
320
321 --========================================================================
322 -- PROCEDURE : Get_order_exposure
323 -- Comments : Retrun the overall exposure ,
324 -- by calculating directly from the
325 -- transaction tables
326 --========================================================================
327 PROCEDURE Get_order_exposure
328 ( p_header_id IN NUMBER
329 , p_transaction_curr_code IN VARCHAR2
330 , p_customer_id IN NUMBER
331 , p_site_use_id IN NUMBER
332 , p_credit_check_rule_rec IN
333 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
334 , p_system_parameter_rec IN
335 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
336 , p_credit_level IN VARCHAR2
337 , p_limit_curr_code IN VARCHAR2
338 , p_usage_curr IN oe_credit_check_util.curr_tbl_type
339 , p_include_all_flag IN VARCHAR2
340 , p_global_exposure_flag IN VARCHAR2 := 'N'
341 , p_need_exposure_details IN VARCHAR2 := 'N'
342 , x_total_exposure OUT NOCOPY NUMBER
343 , x_ar_amount OUT NOCOPY NUMBER
344 , x_order_amount OUT NOCOPY NUMBER
345 , x_order_hold_amount OUT NOCOPY NUMBER
346 , x_conversion_status OUT NOCOPY CURR_TBL_TYPE
347 , x_return_status OUT NOCOPY VARCHAR2
348 )
349 ;
350
351
352 --========================================================================
353 -- PROCEDURE : Currency_List
354 -- Comments : This procedure is used by the credit snapshot report to derive
355 -- a comma delimited string of currencies defined in credit usage
356 -- Parameters: c_entity_type IN 'CUSTOMER' or 'SITE'
357 -- c_entity_id IN Customer_Id or Site_Id
358 -- c_trx_curr_code IN Transaction Currency
359 -- l_limit_curr_code OUT Currency Limit used for credit checking
360 -- Curr_list OUT Comma delimited string of currencies
361 -- covered by limit currency code
362 --========================================================================
363 PROCEDURE Currency_List(
364 c_entity_type IN VARCHAR2
365 , c_entity_id IN NUMBER
366 , c_trx_curr_code IN VARCHAR2
367 , l_limit_curr_code OUT NOCOPY VARCHAR2
368 , l_default_limit_flag OUT NOCOPY VARCHAR2
369 , Curr_list OUT NOCOPY VARCHAR2
370 );
371
372
373 --========================================================================
374 -- PROCEDURE : CONVERT_CURRENCY_AMOUNT
375 -- Comments : Returns the converted amount in the limit curr
376 -- The conversion will also attempt to triangulate if
377 -- no exchange rate is found between transactional curr
378 -- and limit currency
379 --========================================================================
380 FUNCTION CONVERT_CURRENCY_AMOUNT
381 ( p_amount IN NUMBER := 0
382 , p_transactional_currency IN VARCHAR2
383 , p_limit_currency IN VARCHAR2
384 , p_functional_currency IN VARCHAR2
385 , p_conversion_date IN DATE := SYSDATE
386 , p_conversion_type IN VARCHAR2 := 'Corporate'
387 ) RETURN NUMBER ;
388
389 --========================================================================
390 -- PROCEDURE : SEND_CREDIT_HOLD_NTF
391 -- Comments : Set message attributes and send workflow notification
392 -- on all the credit holds for the order.
393 --========================================================================
394 PROCEDURE Send_Credit_Hold_NTF
395 ( p_header_rec IN oe_order_pub.header_rec_type
396 , p_credit_hold_level IN OE_CREDIT_CHECK_RULES.credit_hold_level_code%TYPE
397 , p_cc_hold_comment IN OE_HOLD_SOURCES.hold_comment%TYPE
398 , x_return_status OUT NOCOPY VARCHAR2
399 );
400
401
402 -----------------------------------------------------------------------------
403 -- PROCEDURE: GET_credit_check_level
404 -- COMMENT : Returns ORDER or LINE
405 -- BUG 2114156
406 ------------------------------------------------------------------------------
407 FUNCTION GET_credit_check_level
408 ( p_calling_action IN VARCHAR2
409 , p_order_type_id IN NUMBER
410 ) RETURN VARCHAR2 ;
411
412 ---------------------------------------------------------------------------
413 --PROCEDURE: Get_Credit_Check_Rule_ID
414 --COMMENT: Returns the credit check rule id attached with
415 -- the order trn type
416 ---------------------------------------------------------------------------
417 PROCEDURE Get_Credit_Check_Rule_ID
418 ( p_calling_action IN VARCHAR2
419 , p_order_type_id IN OE_ORDER_HEADERS.order_type_id%TYPE
420 , x_credit_rule_id OUT NOCOPY
421 OE_Credit_check_rules.credit_check_rule_id%TYPE
422 );
423
424
425
426
427 ---------------------------------------------------------------------------
428 --FUNCTION GET_GL_currency
429 --COMMENT: Returns the SOB currency
430
431 ---------------------------------------------------------------------------
432 FUNCTION GET_GL_currency
433 RETURN VARCHAR2 ;
434
435 ---------------------------------------------------------------------------
436 --FUNCTION: Get_global_exposure_flag
437 --COMMENTS: Returns the global exposure flag for a given
438 -- entity ID and limit currency
439 -- used by the credit exposure report
440 -- Multi org enhancement
441 -- Entity type is accepted but not used for validation
442 ---------------------------------------------------------------i-----------
443 FUNCTION Get_global_exposure_flag
444 ( p_entity_type IN VARCHAR2
445 , p_entity_id IN NUMBER
446 , p_limit_curr_code IN VARCHAR2
447 ) RETURN VARCHAR2;
448
449 ----------------------------------------------------------------------------
450 -- PROCEDURE: GET_external_trx_amount PUBLIC
451 -- COMMENT : Returns the transaction amount in the limit currency given the
452 -- amount in the transaction currency. If the
453 -- p_site_use_id IS null, the entire order is considered
454 -- x_conversion_status provides any currency conversion
455 -- error.
456 ----------------------------------------------------------------------------
457 PROCEDURE GET_external_trx_amount
458 ( p_transaction_curr_code IN VARCHAR2
459 , p_transaction_amount IN NUMBER
460 , p_credit_check_rule_rec IN
461 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
462 , p_system_parameter_rec IN
463 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
464 , p_limit_curr_code IN VARCHAR2
465 , x_amount OUT NOCOPY NUMBER
466 , x_conversion_status OUT NOCOPY OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE
467 , x_return_status OUT NOCOPY VARCHAR2
468 );
469
470 FUNCTION Check_drawee_exists
471 ( p_cust_account_id IN NUMBER )
472 RETURN VARCHAR2 ;
473
474 FUNCTION get_drawee_site_use_id
475 ( p_site_use_id IN NUMBER
476 ) RETURN NUMBER ;
477
478 FUNCTION Get_CC_Lookup_Meaning
479 (p_lookup_type IN VARCHAR2,
480 p_lookup_code IN VARCHAR2
481 )
482 RETURN VARCHAR2;
483
484 --ER 12363706 start
485 PROCEDURE Update_Released_Amount(
486 p_header_id NUMBER,
487 p_hold_release_id NUMBER);
488 PROCEDURE Update_Credit_Profile_Level(
489 p_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type);
490 --ER 12363706 end
491
492 END OE_CREDIT_CHECK_UTIL;