1 PACKAGE AR_CALC_LATE_CHARGE AUTHID CURRENT_USER AS
2 /* $Header: ARCALATS.pls 120.2.12010000.1 2008/07/24 16:22:08 appldev ship $ */
3
4 /*========================================================================+
5 The wraper to parallelize the late charge document generation
6 ========================================================================*/
7 PROCEDURE generate_late_charge
8 (errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER,
10 p_operating_unit_id IN VARCHAR2,
11 p_customer_id_from IN VARCHAR2,
12 p_customer_id_to IN VARCHAR2,
13 p_customer_num_from IN VARCHAR2,
14 p_customer_num_to IN VARCHAR2,
15 p_cust_site_use_id IN VARCHAR2,
16 p_gl_date IN VARCHAR2,
17 p_fin_charge_date IN VARCHAR2,
18 p_currency_code IN VARCHAR2,
19 p_mode IN VARCHAR2,
20 p_disputed_items IN VARCHAR2,
21 p_called_from IN VARCHAR2,
22 p_enable_debug IN VARCHAR2,
23 p_total_workers IN VARCHAR2);
24
25 /*========================================================================+
26 The main procedure for the late charge computation engine
27 ========================================================================*/
28 PROCEDURE create_late_charge_document
29 (errbuf OUT NOCOPY VARCHAR2,
30 retcode OUT NOCOPY NUMBER,
31 p_operating_unit_id IN VARCHAR2,
32 p_customer_name_from IN VARCHAR2,
33 p_customer_name_to IN VARCHAR2,
34 p_customer_num_from IN VARCHAR2,
35 p_customer_num_to IN VARCHAR2,
36 p_cust_site_use_id IN VARCHAR2,
37 p_gl_date IN VARCHAR2,
38 p_fin_charge_date IN VARCHAR2,
39 p_currency_code IN VARCHAR2,
40 p_mode IN VARCHAR2,
41 p_disputed_items IN VARCHAR2,
42 p_called_from IN VARCHAR2,
43 p_enable_debug IN VARCHAR2,
44 p_worker_number IN VARCHAR2,
45 p_total_workers IN VARCHAR2,
46 p_master_request_id IN VARCHAR2);
47
48 /*========================================================================+
49 Procedure which inserts Interest Batches
50 ========================================================================*/
51
52 PROCEDURE insert_int_batches(p_operating_unit_id IN NUMBER,
53 p_batch_name IN VARCHAR2,
54 p_fin_charge_date IN DATE,
55 p_batch_status IN VARCHAR2,
56 p_gl_date IN DATE,
57 p_request_id IN NUMBER);
58
59 /*========================================================================+
60 Function which calculates the interest amount
61 ========================================================================*/
62
63 Function Calculate_Interest (p_amount IN NUMBER,
64 p_formula IN VARCHAR2,
65 p_days_late IN NUMBER,
66 p_interest_rate IN NUMBER,
67 p_days_in_period IN NUMBER,
68 p_currency IN VARCHAR2,
69 p_payment_schedule_id IN NUMBER DEFAULT NULL) return NUMBER;
70
71
72 /*========================================================================+
73 Returns the site_use_id of a Late Charge Site associated with the
74 customers address if present else return NULL.
75 ========================================================================*/
76 FUNCTION get_late_charge_site (
77 p_customer_id IN NUMBER,
78 p_org_id IN NUMBER) RETURN NUMBER;
79
80
81 /*=======================================================================+
82 If a given site is defined as a Bill To and a Late Charges site, the
83 site_use_id associated with the Bill To Site use will be stored in
84 hz_customer_profiles. Otherwise, the site_use_id associated with the
85 Late Charges site use will be stored in hz_customer_profiles. This
86 function returns the appropriate site_use_id to be joined with the
87 hz_customer_profiles to get the profile set up
88 =======================================================================*/
89 Function get_profile_class_site_use_id(
90 p_site_use_id IN NUMBER,
91 p_org_id IN NUMBER) RETURN NUMBER;
92
93 /*========================================================================+
94 Function which returns the site_use_id corresponding to the bill_to site
95 ========================================================================*/
96 FUNCTION get_bill_to_site_use_id(p_customer_id IN NUMBER,
97 p_site_use_id IN NUMBER,
98 p_org_id IN NUMBER) RETURN NUMBER;
99
100 /*========================================================================+
101 Function which rounds the input amount as per the currency
102 ========================================================================*/
103 FUNCTION Currency_Round( p_amount IN NUMBER,
104 p_currency_code IN VARCHAR2) RETURN NUMBER;
105
106 /*=======================================================================+
107 Function which returns the next date on which a debit or a credit item
108 is created for a customer, site, currency, org combination. This is with
109 respect to the input as_of_date. If it doesn't find any, it returns the
110 finance charge date. This is used in calculating the average daily balance
111 =======================================================================*/
112 Function get_next_activity_date(p_customer_id IN NUMBER,
113 p_site_use_id IN NUMBER,
114 p_currency_code IN VARCHAR2,
115 p_org_id IN NUMBER,
116 p_post_bill_debit IN VARCHAR2,
117 p_as_of_date IN DATE,
118 p_fin_charge_date IN DATE) RETURN DATE ;
119
120 /*=======================================================================+
121 This fuction retrieves the receivables_trx_id that should be used for
122 creating adjustments for the Interest portion of the late charges. The
123 heirarchy used is Ship To, Bill To and System Options.
124 +=======================================================================*/
125 FUNCTION get_int_rec_trx_id(p_customer_trx_id IN NUMBER,
126 p_fin_charge_date IN DATE,
127 p_org_id IN NUMBER) RETURN NUMBER;
128
129 /*=======================================================================+
130 This fuction retrieves the receivables_trx_id that should be used for
131 creating adjustments for the Penalty portion of the late charges. The
132 heirarchy used is Ship To, Bill To and System Options.
133 +=======================================================================*/
134 FUNCTION get_penalty_rec_trx_id(p_fin_charge_date IN DATE,
135 p_org_id IN NUMBER) RETURN NUMBER;
136
137 /*=======================================================================+
138 Function which calculates the balance due of a transaction. If the formula
139 is COMPOUND, it will consider the finance charge type adjustments that
140 were already created against this transaction
141 =======================================================================*/
142 Function get_balance_as_of(p_payment_schedule_id IN NUMBER,
143 p_as_of_date IN DATE,
144 p_class IN VARCHAR2,
145 p_formula IN VARCHAR2) RETURN NUMBER;
146
147 /*=======================================================================+
148 Function which returns the balance of the customer by adding or subtracting
149 the debit or credit items from the balance forward bill
150 =======================================================================*/
151 Function get_cust_balance(p_customer_id IN NUMBER,
152 p_site_use_id IN NUMBER,
153 p_currency_code IN VARCHAR2,
154 p_org_id IN NUMBER,
155 p_post_billing_debit IN VARCHAR2,
156 p_as_of_date IN DATE) return NUMBER;
157
158 /*=======================================================================+
159 Function which checks whethers a particular customer, site and currency
160 combination is eligible for charge calculation. It returns 'Y' or 'N'. This
161 is used for applying the customer level tolerances in Average Daily Balance
162 scenario
163 =======================================================================*/
164
165 FUNCTION check_adb_eligibility( p_customer_id IN NUMBER,
166 p_site_use_id IN NUMBER,
167 p_currency_code IN VARCHAR2,
168 p_org_id IN VARCHAR2,
169 p_receipt_grace_days IN NUMBER,
170 p_min_fc_bal_overdue_type IN VARCHAR2,
171 p_min_fc_bal_amount IN NUMBER,
172 p_min_fc_bal_percent IN NUMBER,
173 p_fin_charge_date IN DATE) RETURN VARCHAR2 ;
174
175 /*=======================================================================+
176 Function which returns the first date on which the activity started for
177 a customer. This is for calculating the average daily balance even before
178 creating a Balance Forward Bill
179 =======================================================================*/
180
181 FUNCTION get_first_activity_date(p_customer_id IN NUMBER,
182 p_site_use_id IN NUMBER,
183 p_currency_code IN VARCHAR2,
184 p_org_id IN NUMBER) return DATE;
185
186 /*=======================================================================+
187 Function which returns the first day of the month corresponding to the
188 input date. This is used when the calculation period is MONTHLY
189 =======================================================================*/
190 Function first_day(p_calculation_date IN DATE ) RETURN DATE;
191
192 /*=======================================================================+
193 Function which returns the next id to be populated into the
194 ar_interest_headers table. This is required as sequences can not be used
195 in subqueries
196 =======================================================================*/
197 FUNCTION get_next_hdr_id RETURN NUMBER;
198
199
200 /*======================================================================+
201 Procedure to update the late charge amount for all customers profile
202 per site per currency to distribute late charge amount of tier over
203 all invoices in tier.Enhacement 6469663
204 =====================================================================*/
205 Procedure update_interest_amt(p_line_type in VARCHAR2);
206 END AR_CALC_LATE_CHARGE;