[Home] [Help]
PACKAGE BODY: APPS.AST_INVOICES_PVT
Source
1 PACKAGE BODY ast_Invoices_PVT AS
2 /* $Header: astvinvb.pls 115.3 2002/02/06 11:43:53 pkm ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AST_Invoices_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) :='astvinvb.pls';
6 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
8 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
9 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
10 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
11
12 PROCEDURE Get_Invoices(
13 P_API_VERSION IN NUMBER,
14 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
15 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
16 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 X_RETURN_STATUS OUT VARCHAR2,
18 X_MSG_COUNT OUT NUMBER,
19 X_MSG_DATA OUT VARCHAR2,
20 P_TRANSACTION_IDS IN VARCHAR2,
21 X_INVOICE_ID_T OUT INVOICE_ID_TBL_TYPE,
22 X_INVOICE_NUMBER_T OUT INVOICE_NUMBER_TBL_TYPE,
23 X_INVOICE_STATUS_T OUT INVOICE_STATUS_TBL_TYPE,
24 X_INVOICE_CLASS_T OUT INVOICE_CLASS_TBL_TYPE,
25 X_INV_STATUS_CODE_T OUT INV_STATUS_CODE_TBL_TYPE,
26 X_INV_CLASS_CODE_T OUT INV_CLASS_CODE_TBL_TYPE,
27 X_INVOICE_DATE_T OUT INVOICE_DATE_TBL_TYPE,
28 X_ORIGINAL_AMOUNT_T OUT ORIGINAL_AMOUNT_TBL_TYPE,
29 X_REMAINING_AMOUNT_T OUT REMAINING_AMOUNT_TBL_TYPE,
30 X_INVOICE_CURRENCY_T OUT INVOICE_CURRENCY_TBL_TYPE,
31 X_FUN_REMAINING_AMOUNT_T OUT FUN_REMAINING_AMOUNT_TBL_TYPE)
32 AS
33 l_api_name CONSTANT VARCHAR2(30) := 'Get_Invoices';
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_return_status VARCHAR2(1);
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(32767);
38 l_trx_id_cond VARCHAR2(100) := p_transaction_ids;
39
40
41 l_cur_get_inv NUMBER;
42 l_select_cl VARCHAR2(2000) := '';
43 l_order_by_cl VARCHAR2(2000);
44 l_where_cl VARCHAR2(2000) := '';
45
46 TYPE trx_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
47 l_trx_id_tbl trx_id_tbl_type;
48 l_idx BINARY_INTEGER;
49 i NUMBER;
50 j NUMBER;
51
52 l_inv_id_tbl AST_Invoices_PVT.invoice_id_tbl_type;
53 l_inv_num_tbl AST_Invoices_PVT.invoice_number_tbl_type;
54 l_inv_status_tbl AST_Invoices_PVT.invoice_status_tbl_type;
55 l_inv_class_tbl AST_Invoices_PVT.invoice_class_tbl_type;
56 l_inv_status_code_tbl AST_Invoices_PVT.inv_status_code_tbl_type;
57 l_inv_class_code_tbl AST_Invoices_PVT.inv_class_code_tbl_type;
58 l_inv_date_tbl AST_Invoices_PVT.invoice_date_tbl_type;
59 l_original_amt_tbl AST_Invoices_PVT.original_amount_tbl_type;
60 l_remaining_amt_tbl AST_Invoices_PVT.remaining_amount_tbl_type;
61 l_inv_currency_tbl AST_Invoices_PVT.invoice_currency_tbl_type;
62 l_fun_remaining_amt_tbl AST_Invoices_PVT.fun_remaining_amount_tbl_type;
63
64 l_rows_processed NUMBER;
65
66 BEGIN
67 -- Standard start of API savepoint
68 SAVEPOINT Get_Invoices_PVT;
69
70 -- Standard call to check for call compatibility
71 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
73 END IF;
74
75 -- Initialize message list if p_init_msg_list is set to TRUE
76 IF FND_API.To_Boolean(p_init_msg_list) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79
80 -- Implementation of User Hooks
81 /* Copy all parameters to local variables to be passed to Pre, Post and Business APIs */
82 /* l_rec - will be used as In Out parameter in pre/post/Business API calls */
83 /* l_return_status - will be a out variable to get return code from called APIs */
84
85 /* Customer pre -processing section - Mandatory */
86 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'C' ) ) THEN
87 ast_INVOICES_CUHK.GET_INVOICES_PRE(p_api_version => l_api_version,
88 x_return_status => l_return_status,
89 x_msg_count => l_msg_count,
90 x_msg_data => l_msg_data,
91 p_transaction_ids => l_trx_id_cond);
92 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
93 RAISE FND_API.G_EXC_ERROR;
94 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 END IF;
98
99
100 /* Verticle industry pre- processing section - mandatory */
101 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'B', 'V' ) ) THEN
102 ast_INVOICES_VUHK.GET_INVOICES_PRE(p_api_version => l_api_version,
103 x_return_status => l_return_status,
104 x_msg_count => l_msg_count,
105 x_msg_data => l_msg_data,
106 p_transaction_ids => l_trx_id_cond);
107 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
108 RAISE FND_API.G_EXC_ERROR;
109 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112 END IF;
113
114 -- Initialize API return status to success
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116
117 -- API body begin
118
119 l_select_cl := 'SELECT customer_trx_id, '
120 || 'trx_number, '
121 || 'al_status_meaning, '
122 || 'al_class_meaning, '
123 || 'status, '
124 || 'class, '
125 || 'amount_due_remaining, '
126 || 'amount_due_original, '
127 || 'trx_date, '
128 || 'invoice_currency_code, '
129 || 'acctd_amount_due_remaining '
130 || 'FROM ar_payment_schedules_v ';
131
132 l_where_cl := 'WHERE customer_trx_id IN ( ';
133 l_order_by_cl := ' ORDER BY trx_number ';
134
135 i := 1;
136 l_idx := 1;
137 LOOP
138 j := instr(l_trx_id_cond, ',', i);
139
140 EXIT WHEN j = 0;
141 --dbms_output.put_line(substr(l_trx_id_cond, i, j - i));
142 l_trx_id_tbl(l_idx) := to_number(substr(l_trx_id_cond, i, j - i));
143
144 IF l_idx > 1 THEN
145 l_where_cl := l_where_cl || ' , ';
146 END IF;
147
148 l_where_cl := l_where_cl || ':x' || l_idx;
149 i := j + 1;
150 l_idx := l_idx + 1;
151
152 END LOOP;
153
154 l_where_cl := l_where_cl || ')';
155
156 --dbms_output.put_line('Where Clause > ' || l_where_cl);
157 --dbms_output.put_line('Trx ID Count > ' || l_trx_id_tbl.count);
158
159 l_select_cl := l_select_cl || l_where_cl || l_order_by_cl;
160 --dbms_output.put_line('Select Clause > ' || l_select_cl);
161
162 l_cur_get_inv := dbms_sql.open_cursor;
163
164 dbms_sql.parse(l_cur_get_inv, l_select_cl, dbms_sql.native);
165
166 FOR i IN 1..l_trx_id_tbl.count LOOP
167 --dbms_output.put_line('i > ' || i);
168 dbms_sql.bind_variable(l_cur_get_inv, ':x' || i, l_trx_id_tbl(i));
169 END LOOP;
170
171 l_inv_id_tbl(1) := null;
172 l_inv_num_tbl(1) := null;
173 l_inv_status_tbl(1) := null;
174 l_inv_class_tbl(1) := null;
175 l_inv_status_code_tbl(1) := null;
176 l_inv_class_code_tbl(1) := null;
177 l_remaining_amt_tbl(1) := null;
178 l_original_amt_tbl(1) := null;
179 l_inv_date_tbl(1) := null;
180 l_inv_currency_tbl(1) := null;
181 l_fun_remaining_amt_tbl(1) := null;
182
183 dbms_sql.define_column(l_cur_get_inv, 1, l_inv_id_tbl(1));
184 dbms_sql.define_column(l_cur_get_inv, 2, l_inv_num_tbl(1), 30);
185 dbms_sql.define_column(l_cur_get_inv, 3, l_inv_status_tbl(1), 80);
186 dbms_sql.define_column(l_cur_get_inv, 4, l_inv_class_tbl(1), 80);
187 dbms_sql.define_column(l_cur_get_inv, 5, l_inv_status_code_tbl(1), 30);
188 dbms_sql.define_column(l_cur_get_inv, 6, l_inv_class_code_tbl(1), 20);
189 dbms_sql.define_column(l_cur_get_inv, 7, l_remaining_amt_tbl(1));
190 dbms_sql.define_column(l_cur_get_inv, 8, l_original_amt_tbl(1));
191 dbms_sql.define_column(l_cur_get_inv, 9, l_inv_date_tbl(1));
192 dbms_sql.define_column(l_cur_get_inv, 10, l_inv_currency_tbl(1), 15);
193 dbms_sql.define_column(l_cur_get_inv, 11, l_fun_remaining_amt_tbl(1));
194
195 l_rows_processed := dbms_sql.execute(l_cur_get_inv);
196
197 --dbms_output.put_line ('l_rows_processed > ' || l_rows_processed);
198 l_idx := 0;
199 LOOP
200 l_idx := l_idx + 1;
201
202 l_rows_processed := dbms_sql.fetch_rows(l_cur_get_inv);
203 --dbms_output.put_line ('l_rows_processed > ' || l_rows_processed);
204 IF l_rows_processed <= 0 THEN
205 EXIT;
206 END IF;
207
208 dbms_sql.column_value(l_cur_get_inv, 1, l_inv_id_tbl(l_idx));
209 dbms_sql.column_value(l_cur_get_inv, 2, l_inv_num_tbl(l_idx));
210 dbms_sql.column_value(l_cur_get_inv, 3, l_inv_status_tbl(l_idx));
211 dbms_sql.column_value(l_cur_get_inv, 4, l_inv_class_tbl(l_idx));
212 dbms_sql.column_value(l_cur_get_inv, 5, l_inv_status_code_tbl(l_idx));
213 dbms_sql.column_value(l_cur_get_inv, 6, l_inv_class_code_tbl(l_idx));
214 dbms_sql.column_value(l_cur_get_inv, 7, l_remaining_amt_tbl(l_idx));
215 dbms_sql.column_value(l_cur_get_inv, 8, l_original_amt_tbl(l_idx));
216 dbms_sql.column_value(l_cur_get_inv, 9, l_inv_date_tbl(l_idx));
217 dbms_sql.column_value(l_cur_get_inv, 10, l_inv_currency_tbl(l_idx));
218 dbms_sql.column_value(l_cur_get_inv, 11, l_fun_remaining_amt_tbl(l_idx));
219
220 --dbms_output.put_line(l_inv_num_tbl(l_idx));
221 END LOOP;
222
223 dbms_sql.close_cursor(l_cur_get_inv);
224
225
226 x_invoice_id_t := l_inv_id_tbl;
227 x_invoice_number_t := l_inv_num_tbl;
228 x_invoice_status_t := l_inv_status_tbl;
229 x_invoice_class_t := l_inv_class_tbl;
230 x_inv_status_code_t := l_inv_status_code_tbl;
231 x_inv_class_code_t := l_inv_class_code_tbl;
232 x_remaining_amount_t := l_remaining_amt_tbl;
233 x_original_amount_t := l_original_amt_tbl;
234 x_invoice_date_t := l_inv_date_tbl;
235 x_invoice_currency_t := l_inv_currency_tbl;
236 x_fun_remaining_amount_t := l_fun_remaining_amt_tbl;
237
238 -- API body end
239
240 /* Customer Post Processing section - mandatory */
241 IF (JTF_USR_HKS.Ok_to_execute(G_PKG_NAME, l_api_name, 'A', 'C' ) ) THEN
242 ast_INVOICES_CUHK.GET_INVOICES_Post (p_api_version => l_api_version,
243 x_return_status => l_return_status,
244 x_msg_count => l_msg_count,
245 x_msg_data => l_msg_data,
246 p_transaction_ids => l_trx_id_cond);
247 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
248 RAISE FND_API.G_EXC_ERROR;
249 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252 END IF;
253
254 -- Standard check of p_commit
255 IF FND_API.To_Boolean(p_commit) THEN
256 COMMIT WORK;
257 END IF;
258
259 -- Standard call to get message count and if count is 1, get message info
260 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
261
262 EXCEPTION
263 WHEN FND_API.G_EXC_ERROR THEN
264 ROLLBACK TO Get_Invoices_PVT;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
267
268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
269 ROLLBACK TO Get_Invoices_PVT;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
272
273 WHEN OTHERS THEN
274 ROLLBACK TO Get_Invoices_PVT;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
277 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
278 END IF;
279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
280
281 END Get_Invoices;
282
283 END ast_Invoices_PVT;