[Home] [Help]
PACKAGE BODY: APPS.ARH_CPCA_PKG
Source
1 PACKAGE BODY arh_cpca_pkg as
2 /* $Header: ARHPCAB.pls 120.1 2005/06/16 21:13:17 jhuang ship $*/
3
4 PROCEDURE Insert_Negative_Class_Amt
5 ( X_Customer_Profile_Class_Id NUMBER,
6 X_Created_By NUMBER,
7 X_Creation_Date DATE,
8 X_Currency_Code VARCHAR2,
9 X_Cust_Prof_Class_Amount_Id NUMBER,
10 X_Last_Updated_By NUMBER,
11 X_Last_Update_Date DATE,
12 X_Auto_Rec_Min_Receipt_Amount NUMBER,
13 X_Last_Update_Login NUMBER,
14 X_Max_Interest_Charge NUMBER,
15 X_Min_Dunning_Amount NUMBER,
16 X_Min_Statement_Amount NUMBER,
17 X_Overall_Credit_Limit NUMBER,
18 X_Trx_Credit_Limit NUMBER,
19 X_Attribute_Category VARCHAR2,
20 X_Attribute1 VARCHAR2,
21 X_Attribute2 VARCHAR2,
22 X_Attribute3 VARCHAR2,
23 X_Attribute4 VARCHAR2,
24 X_Attribute5 VARCHAR2,
25 X_Attribute6 VARCHAR2,
26 X_Attribute7 VARCHAR2,
27 X_Attribute8 VARCHAR2,
28 X_Attribute9 VARCHAR2,
29 X_Attribute10 VARCHAR2,
30 X_Attribute11 VARCHAR2,
31 X_Attribute12 VARCHAR2,
32 X_Attribute13 VARCHAR2,
33 X_Attribute14 VARCHAR2,
34 X_Attribute15 VARCHAR2,
35 X_Interest_Rate NUMBER,
36 X_Min_Fc_Balance_Amount NUMBER,
37 X_Min_Fc_Invoice_Amount NUMBER,
38 X_Min_Dunning_Invoice_Amount NUMBER,
39 X_Jgzz_attribute_Category VARCHAR2,
40 X_Jgzz_attribute1 VARCHAR2,
41 X_Jgzz_attribute2 VARCHAR2,
42 X_Jgzz_attribute3 VARCHAR2,
43 X_Jgzz_attribute4 VARCHAR2,
44 X_Jgzz_attribute5 VARCHAR2,
45 X_Jgzz_attribute6 VARCHAR2,
46 X_Jgzz_attribute7 VARCHAR2,
47 X_Jgzz_attribute8 VARCHAR2,
48 X_Jgzz_attribute9 VARCHAR2,
49 X_Jgzz_attribute10 VARCHAR2,
50 X_Jgzz_attribute11 VARCHAR2,
51 X_Jgzz_attribute12 VARCHAR2,
52 X_Jgzz_attribute13 VARCHAR2,
53 X_Jgzz_attribute14 VARCHAR2,
54 X_Jgzz_attribute15 VARCHAR2
55 ) IS
56
57 BEGIN
58 INSERT INTO HZ_CUST_PROF_CLASS_AMTS
59 (
60 profile_class_id,
61 created_by,
62 creation_date,
63 currency_code,
64 profile_class_amount_id,
65 last_updated_by,
66 last_update_date,
67 auto_rec_min_receipt_amount,
68 last_update_login,
69 max_interest_charge,
70 min_dunning_amount,
71 min_statement_amount,
72 overall_credit_limit,
73 trx_credit_limit,
74 attribute_category,
75 attribute1,
76 attribute2,
77 attribute3,
78 attribute4,
79 attribute5,
80 attribute6,
81 attribute7,
82 attribute8,
83 attribute9,
84 attribute10,
85 attribute11,
86 attribute12,
87 attribute13,
88 attribute14,
89 attribute15,
90 interest_rate,
91 min_fc_balance_amount,
92 min_fc_invoice_amount,
93 min_dunning_invoice_amount,
94 Jgzz_attribute_Category,
95 Jgzz_attribute1,
96 Jgzz_attribute2,
97 Jgzz_attribute3,
98 Jgzz_attribute4,
99 Jgzz_attribute5,
100 Jgzz_attribute6,
101 Jgzz_attribute7,
102 Jgzz_attribute8,
103 Jgzz_attribute9,
104 Jgzz_attribute10,
105 Jgzz_attribute11,
106 Jgzz_attribute12,
107 Jgzz_attribute13,
108 Jgzz_attribute14,
109 Jgzz_attribute15
110 )
111 VALUES
112 (
113 X_Customer_Profile_Class_Id,
114 X_Created_By,
115 X_Creation_Date,
116 X_Currency_Code,
117 X_Cust_Prof_Class_Amount_Id,
118 X_Last_Updated_By,
119 X_Last_Update_Date,
120 X_Auto_Rec_Min_Receipt_Amount,
121 X_Last_Update_Login,
122 X_Max_Interest_Charge,
123 X_Min_Dunning_Amount,
124 X_Min_Statement_Amount,
125 X_Overall_Credit_Limit,
126 X_Trx_Credit_Limit,
127 X_Attribute_Category,
128 X_Attribute1,
129 X_Attribute2,
130 X_Attribute3,
131 X_Attribute4,
132 X_Attribute5,
133 X_Attribute6,
134 X_Attribute7,
135 X_Attribute8,
136 X_Attribute9,
137 X_Attribute10,
138 X_Attribute11,
139 X_Attribute12,
140 X_Attribute13,
141 X_Attribute14,
142 X_Attribute15,
143 X_Interest_Rate,
144 X_Min_Fc_Balance_Amount,
145 X_Min_Fc_Invoice_Amount,
146 X_Min_Dunning_Invoice_Amount,
147 X_Jgzz_attribute_Category,
148 X_Jgzz_attribute1,
149 X_Jgzz_attribute2,
150 X_Jgzz_attribute3,
151 X_Jgzz_attribute4,
152 X_Jgzz_attribute5,
153 X_Jgzz_attribute6,
154 X_Jgzz_attribute7,
155 X_Jgzz_attribute8,
156 X_Jgzz_attribute9,
157 X_Jgzz_attribute10,
158 X_Jgzz_attribute11,
159 X_Jgzz_attribute12,
160 X_Jgzz_attribute13,
161 X_Jgzz_attribute14,
162 X_Jgzz_attribute15
163 );
164
165 END Insert_Negative_Class_Amt;
166
167 PROCEDURE compute_negative_id
168 ( X_Cust_Prof_Class_Amount_Id NUMBER,
169 X_Negative_Id IN OUT NOCOPY NUMBER,
170 X_Notify_Flag IN OUT NOCOPY VARCHAR2
171 ) IS
172
173 number_in_update number;
174
175 BEGIN
176
177 --IDENTIFY EXISTING ROW WITH NEGATIVE ID IN HZ_CUST_PROF_CLASS_AMTS
178 --RETRIEVE THE MIN id WHERE id BETWEEN -100*ID-99 AND -100*ID-2
179
180 SELECT count(*), min(profile_class_amount_id) - 1
181 INTO number_in_update, X_Negative_Id
182 FROM HZ_CUST_PROF_CLASS_AMTS
183 WHERE profile_class_amount_id BETWEEN
184 (X_Cust_Prof_Class_Amount_Id) * (-100) - 99 AND
185 (X_Cust_Prof_Class_Amount_Id) * (-100) - 2;
186
187 if number_in_update > 0 then
188 X_Notify_Flag := 'W';
189 end if;
190
191 END compute_negative_id;
192 --
193 --
194 PROCEDURE old_amount_insert
195 ( X_Cust_Prof_Class_Amount_Id NUMBER,
196 X_Negative_Id NUMBER,
197 X_Customer_Profile_Class_Id NUMBER
198 ) IS
199
200 CURSOR C is
201 select *
202 from hz_cust_prof_class_amts
203 where profile_class_amount_id = X_Cust_Prof_Class_Amount_Id
204 FOR UPDATE of profile_class_amount_id NOWAIT;
205 Amountinfo C%ROWTYPE;
206
207 BEGIN
208 OPEN C;
209 FETCH C INTO Amountinfo;
210 if (C%NOTFOUND) then
211 CLOSE C;
212 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
213 APP_EXCEPTION.Raise_Exception;
214 end if;
215 CLOSE C;
216
217 Insert_Negative_Class_Amt
218 (
219 X_Customer_Profile_Class_Id,
220 Amountinfo.created_by,
221 Amountinfo.creation_date,
222 Amountinfo.currency_code,
223 X_Negative_Id,
224 Amountinfo.last_updated_by,
225 Amountinfo.last_update_date,
226 Amountinfo.auto_rec_min_receipt_amount,
227 Amountinfo.last_update_login,
228 Amountinfo.max_interest_charge,
229 Amountinfo.min_dunning_amount,
230 Amountinfo.min_statement_amount,
231 Amountinfo.overall_credit_limit,
232 Amountinfo.trx_credit_limit,
233 Amountinfo.attribute_category,
234 Amountinfo.attribute1,
235 Amountinfo.attribute2,
236 Amountinfo.attribute3,
237 Amountinfo.attribute4,
238 Amountinfo.attribute5,
239 Amountinfo.attribute6,
240 Amountinfo.attribute7,
241 Amountinfo.attribute8,
242 Amountinfo.attribute9,
243 Amountinfo.attribute10,
244 Amountinfo.attribute11,
245 Amountinfo.attribute12,
246 Amountinfo.attribute13,
247 Amountinfo.attribute14,
248 Amountinfo.attribute15,
249 Amountinfo.interest_rate,
250 Amountinfo.min_fc_balance_amount,
251 Amountinfo.min_fc_invoice_amount,
252 Amountinfo.min_dunning_invoice_amount,
253 Amountinfo.jgzz_attribute_category,
254 Amountinfo.jgzz_attribute1,
255 Amountinfo.jgzz_attribute2,
256 Amountinfo.jgzz_attribute3,
257 Amountinfo.jgzz_attribute4,
258 Amountinfo.jgzz_attribute5,
259 Amountinfo.jgzz_attribute6,
260 Amountinfo.jgzz_attribute7,
261 Amountinfo.jgzz_attribute8,
262 Amountinfo.jgzz_attribute9,
263 Amountinfo.jgzz_attribute10,
264 Amountinfo.jgzz_attribute11,
265 Amountinfo.jgzz_attribute12,
266 Amountinfo.jgzz_attribute13,
267 Amountinfo.jgzz_attribute14,
268 Amountinfo.jgzz_attribute15
269 );
270
271 END old_amount_insert;
272 --
273 --
274 END ARH_CPCA_PKG;