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