1 PACKAGE OE_CREDIT_EXPOSURE_PVT AS
2 -- $Header: OEXVCRXS.pls 120.1.12010000.1 2008/07/25 07:59:20 appldev ship $
3
4 --+=======================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+=======================================================================+
9 --| FILENAME |
10 --| OEXVCRXS.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Spec. of OE_CREDIT_EXPOSURE_PVT |
14 --| |
15 --| HISTORY |
16 --| 06/05/2001 Rene Schaub Created |
17 --| 01-29-2002 rajkrish updated for Multi org project OM H |
18 --| ontdev => 115.3 2001/08/25 |
19 --| 03/15/2002 vto Added G_EXTERNAL_EXPOSURE balance type |
20 --| 06/18/2002 vto renee logic init summary only |
21 --| oct-30 2002 updates |
22 --| 12/06/2002 vto Added NOCOPY to OUT variables |
23 --| 07/02/2003 tsimmond Added new balance type globals for the |
24 --| Returns project |
25 --+======================================================================*/
26
27 --===================
28 -- TYPES
29 --===================
30
31 TYPE Binary_rec_type IS RECORD
32 ( bucket NUMBER
33 , bucket_length NUMBER
34 );
35
36 TYPE Binary_tbl_type IS TABLE OF Binary_rec_type
37 INDEX BY BINARY_INTEGER;
38
39 --===================
40 -- CONSTANTS
41 --===================
42
43 G_HEADER_UNINVOICED_ORDERS CONSTANT NUMBER := 1;
44 G_LINE_UNINVOICED_ORDERS CONSTANT NUMBER := 2;
45 G_HEADER_UNINVOICED_ORDERS_TAX CONSTANT NUMBER := 3;
46 G_LINE_UNINVOICED_ORDERS_TAX CONSTANT NUMBER := 4;
47 G_HEADER_UNINVOICED_FREIGHT CONSTANT NUMBER := 5;
48 G_LINE_UNINVOICED_FREIGHT CONSTANT NUMBER := 6;
49 G_HEADER_AND_LINE_FREIGHT CONSTANT NUMBER := 7;
50 G_INVOICES CONSTANT NUMBER := 8;
51 G_PAYMENTS_AT_RISK CONSTANT NUMBER := 9;
52 G_ORDER_HOLDS CONSTANT NUMBER := 10;
53 G_LINE_HOLDS CONSTANT NUMBER := 11;
54 G_ORDER_TAX_HOLDS CONSTANT NUMBER := 13;
55 G_LINE_TAX_HOLDS CONSTANT NUMBER := 14;
56 G_ORDER_FREIGHT_HOLDS CONSTANT NUMBER := 15;
57 G_LINE_FREIGHT_HOLDS CONSTANT NUMBER := 16;
58 G_HEADER_LINE_FREIGHT_HOLDS CONSTANT NUMBER := 17;
59 G_EXTERNAL_EXPOSURE CONSTANT NUMBER := 18;
60 G_PAST_DUE_INVOICES CONSTANT NUMBER := 20;
61 G_BR_INVOICES CONSTANT NUMBER := 21;
62 G_BR_PAYMENTS_AT_RISK CONSTANT NUMBER := 22;
63
64 -------added for the Returns project (FPJ)
65 G_HEAD_RETURN_UNINV_ORDERS CONSTANT NUMBER := 23;
66 G_LINE_RETURN_UNINV_ORDERS CONSTANT NUMBER := 24;
67 G_HEAD_RETURN_UNINV_ORD_TAX CONSTANT NUMBER := 25;
68 G_LINE_RETURN_UNINV_ORD_TAX CONSTANT NUMBER := 26;
69 G_HEAD_RETURN_UNINV_FREIGHT CONSTANT NUMBER := 27;
70 G_LINE_RETURN_UNINV_FREIGHT CONSTANT NUMBER := 28;
71 G_HEAD_LINE_RETURN_FREIGHT CONSTANT NUMBER := 29;
72 G_ORDER_RETURN_HOLDS CONSTANT NUMBER := 30;
73 G_LINE_RETURN_HOLDS CONSTANT NUMBER := 31;
74 G_ORDER_RETURN_TAX_HOLDS CONSTANT NUMBER := 32;
75 G_LINE_RETURN_TAX_HOLDS CONSTANT NUMBER := 33;
76 G_ORDER_RETURN_FREIGHT_HOLDS CONSTANT NUMBER := 34;
77 G_LINE_RETURN_FREIGHT_HOLDS CONSTANT NUMBER := 35;
78 G_H_L_RETURN_FREIGHT_HOLDS CONSTANT NUMBER := 36;
79
80 --===================
81 -- GLOBAL VARIABLES
82 --===================
83
84 G_MAX_BUCKET_LEVEL NUMBER := 8;
85 G_MAX_BUCKET_LENGTH NUMBER := 256; -- 2^8
86 G_COMPLETE BOOLEAN := TRUE;
87
88
89 --========================================================================
90 -- PROCEDURE : Init_Summary_Table PUBLIC
91 -- PARAMETERS: x_retcode 0 success, 1 warning, 2 error
92 -- x_errbuf error buffer
93 -- p_lock_tables 'Y' or 'N' for all transaction tables
94 ---
95 -- COMMENT : This is the concurrent program specification for
96 -- Initialize Credit Summaries Table
97 -- which will repopulate oe_credit_summaries table with summarized
98 -- credit exposure information.
99 -- The p_lock_tables flag specifies if
100 -- the oe_order_lines_all, oe_order_headers_all,
101 -- oe_cash_adjustments, ar_payment_schedules_all,
102 -- ar_cash_receipts_all tables should all be locked in exclusive mode
103 -- until all of the summary data is obtained.
104 -- If the flag is not set to 'Y', none of the tables is locked.
105 --=======================================================================--
106
107
108 PROCEDURE Init_Summary_Table
109 ( x_retcode OUT NOCOPY VARCHAR2
110 , x_errbuf OUT NOCOPY VARCHAR2
111 , p_lock_tables IN VARCHAR2 DEFAULT 'N'
112 );
113
114
115 --========================================================================
116 -- PROCEDURE : Get_Exposure PUBLIC
117 -- PARAMETERS: x_retcode 0 success, 1 warning, 2 error
118 -- x_errbuf error buffer
119 -- p_customer_id not null
120 -- p_site_use_id can be null
121 -- p_header_id order header
122 -- p_credit_check_rule_rec
123 -- p_system_parameters_rec
124 -- p_limit_curr_code currency in which to show the exposure
125 -- p_usage_curr_tbl only include transactions in these currencies
126 -- p_include_all_flag include transactions in any currency
127 -- x_total_exposure
128 -- x_return_status
129 -- x_error_curr_tbl contains currencies with no rates
130
131 ---
132 -- COMMENT : This returns the total exposure for a customer or customer site
133 -- using precalculated data.
134 --=======================================================================--
135 PROCEDURE Get_Exposure
136 ( p_customer_id IN NUMBER
137 , p_site_use_id IN NUMBER
138 , p_party_id IN NUMBER := NULL
139 , p_header_id IN NUMBER
140 , p_credit_check_rule_rec IN
141 OE_CREDIT_CHECK_UTIL.oe_credit_rules_rec_type
142 , p_system_parameters_rec IN
143 OE_CREDIT_CHECK_UTIL.oe_systems_param_rec_type
144 , p_limit_curr_code IN
145 HZ_CREDIT_PROFILE_AMTS.currency_code%TYPE
146 , p_usage_curr_tbl IN OE_CREDIT_CHECK_UTIL.curr_tbl_type
147 , p_include_all_flag IN VARCHAR2
148 , p_global_exposure_flag IN VARCHAR2 := 'N'
149 , p_need_exposure_details IN VARCHAR2 := 'N'
150 , x_total_exposure OUT NOCOPY NUMBER
151 , x_order_amount OUT NOCOPY NUMBER
152 , x_order_hold_amount OUT NOCOPY NUMBER
153 , x_ar_amount OUT NOCOPY NUMBER
154 , x_return_status OUT NOCOPY VARCHAR2
155 , x_error_curr_tbl OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
156 );
157
158
159
160 PROCEDURE get_invoices_over_duedate
161 ( p_customer_id IN NUMBER
162 , p_site_use_id IN NUMBER
163 , p_party_id IN NUMBER
164 , p_credit_check_rule_rec IN
165 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
166 , p_credit_level IN VARCHAR2
167 , p_usage_curr IN oe_credit_check_util.curr_tbl_type
168 , p_include_all_flag IN VARCHAR2
169 , p_global_exposure_flag IN VARCHAR2 := 'N'
170 , p_org_id IN NUMBER
171 , x_exist_flag OUT NOCOPY VARCHAR2
172 )
173 ;
174
175
176
177 END OE_CREDIT_EXPOSURE_PVT;