[Home] [Help]
PACKAGE BODY: APPS.FUN_NET_EXTRACT_PKG
Source
1 PACKAGE BODY FUN_NET_EXTRACT_PKG AS
2 /* $Header: funntdeb.pls 120.1 2006/01/20 07:56:47 vgadde noship $ */
3
4 PROCEDURE extract_data
5 (errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY VARCHAR2,
7 p_batch_id IN fun_net_batches_all.batch_id%TYPE)
8 IS
9
10 CURSOR c_get_batch_details IS
11 SELECT batch_number,
12 batch_name,
13 agreement_id,
14 transaction_due_date,
15 settlement_date,
16 org_id,
17 batch_currency,
18 total_netted_amt
19 FROM fun_net_batches
20 WHERE batch_id = p_batch_id;
21
22 CURSOR c_get_agreement_details(cp_agreement_id fun_net_agreements_all.agreement_id%TYPE) IS
23 SELECT agreement_name,
24 agreement_start_date,
25 agreement_end_date
26 FROM fun_net_agreements
27 WHERE agreement_id = cp_agreement_id;
28
29 CURSOR c_get_functional_currency(cp_org_id fun_net_batches_all.org_id%TYPE) IS
30 SELECT l.currency_code
31 FROM gl_ledgers l,hr_operating_units ou
32 WHERE ou.organization_id = cp_org_id
33 AND ou.set_of_books_id = l.ledger_id;
34
35 CURSOR c_get_ap_invoices(cp_agreement_id fun_net_agreements_all.agreement_id%TYPE) IS
36 SELECT pv.vendor_name supplier ,
37 pv.segment1 supplier_number ,
38 pvs.vendor_site_code supplier_site ,
39 ai.invoice_num invoice_number ,
40 ai.invoice_date invoice_date ,
41 ai.invoice_currency_code invoice_currency,
42 ai.invoice_amount invoice_amount ,
43 ai.base_amount ledger_amount,
44 nai.netted_amt netted_amount
45 FROM fun_net_ap_invs nai,
46 ap_invoices_all ai,
47 po_vendors pv,
48 po_vendor_sites_all pvs,
49 fun_net_suppliers_all ns
50 WHERE nai.batch_id = p_batch_id
51 AND nai.invoice_id = ai.invoice_id
52 AND ai.vendor_id = pv.vendor_id
53 AND ai.vendor_site_id = pvs.vendor_site_id
54 AND ns.agreement_id = cp_agreement_id
55 AND ns.supplier_id = ai.vendor_id
56 AND nvl(ns.supplier_site_id,ai.vendor_site_id) = ai.vendor_site_id
57 AND ns.org_id = ai.org_id
58 AND nai.org_id = ai.org_id
59 AND ai.org_id = pvs.org_id
60 AND nai.netted_amt <> 0
61 ORDER BY ns.supplier_priority, nai.ap_txn_rank;
62
63 CURSOR c_get_ar_transactions(cp_agreement_id fun_net_agreements_all.agreement_id%TYPE) IS
64 SELECT hp.party_name customer ,
65 hca.account_number customer_number ,
66 hcs.location location ,
67 rct.trx_number transaction_number ,
68 rct.trx_date transaction_date ,
69 rct.invoice_currency_code transaction_currency,
70 rctl.amount transaction_amount,
71 rctl.acctd_amount ledger_amount,
72 nat.netted_amt netted_amount
73 FROM fun_net_ar_txns nat,
74 ra_customer_trx_all rct,
75 hz_parties hp,
76 hz_cust_accounts_all hca,
77 hz_cust_site_uses_all hcs,
78 fun_net_customers_all nc,
79 ra_cust_trx_line_gl_dist_all rctl
80 WHERE nat.batch_id = p_batch_id
81 AND nat.customer_trx_id = rct.customer_trx_id
82 AND rct.bill_to_customer_id = hca.cust_account_id
83 AND hca.party_id = hp.party_id
84 AND rct.bill_to_site_use_id = hcs.site_use_id
85 AND nc.agreement_id = cp_agreement_id
86 AND nc.cust_account_id = rct.bill_to_customer_id
87 AND nvl(nc.cust_site_use_id,rct.bill_to_site_use_id) = rct.bill_to_site_use_id
88 AND nc.org_id = rct.org_id
89 AND nat.org_id = rct.org_id
90 AND nat.org_id = hcs.org_id
91 AND nat.netted_amt <> 0
92 AND rct.customer_trx_id = rctl.customer_trx_id
93 AND rctl.account_class = 'REC'
94 AND nvl(latest_rec_flag,'N') = 'Y'
95 AND rct.org_id = rctl.org_id
96 ORDER BY nc.cust_priority, nat.ar_txn_rank;
97
98 l_batch_details c_get_batch_details%ROWTYPE;
99 l_agreement_details c_get_agreement_details%ROWTYPE;
100 l_customer hz_parties.party_name%TYPE;
101 l_customer_number hz_cust_accounts_all.account_number%TYPE;
102 l_location hz_cust_site_uses_all.location%TYPE;
103 l_supplier po_vendors.vendor_name%TYPE;
104 l_supplier_number po_vendors.segment1%TYPE;
105 l_supplier_site po_vendor_sites_all.vendor_site_code%TYPE;
106 l_invoice_count NUMBER;
107 l_transaction_count NUMBER;
108 l_output_string VARCHAR2(2000);
109 l_func_currency gl_ledgers.currency_code%TYPE;
110 l_ledger_id gl_ledgers.ledger_id%TYPE;
111 l_ledger_name gl_ledgers.name%TYPE;
112
113 BEGIN
114
115 OPEN c_get_batch_details;
116 FETCH c_get_batch_details INTO l_batch_details;
117 CLOSE c_get_batch_details;
118
119 OPEN c_get_agreement_details(l_batch_details.agreement_id);
120 FETCH c_get_agreement_details INTO l_agreement_details;
121 CLOSE c_get_agreement_details;
122
123 MO_Utils.Get_Ledger_Info(
124 l_batch_details.org_id,
125 l_ledger_id,
126 l_ledger_name);
127
128 SELECT currency_code
129 INTO l_func_currency
130 FROM gl_ledgers
131 WHERE ledger_id = l_ledger_id;
132
133 fnd_file.put_line(fnd_file.output,'Data Extract File');
134
135 l_output_string := 'Netting Agreement:,,'||l_agreement_details.agreement_name;
136 fnd_file.put_line(fnd_file.output,l_output_string);
137
138 l_output_string := 'Start Date:,,'||to_char(l_agreement_details.agreement_start_date,'DD-MON-YY');
139 fnd_file.put_line(fnd_file.output,l_output_string);
140
141 l_output_string := 'End Date:,,';
142
143 IF l_agreement_details.agreement_end_date IS NOT NULL THEN
144 l_output_string := l_output_string||to_char(l_agreement_details.agreement_end_date,'DD-MON-YY');
145 END IF;
146
147 fnd_file.put_line(fnd_file.output,l_output_string);
148
149 l_output_string := 'Transaction Due Date:,,'||to_char(l_batch_details.transaction_due_date,'DD-MON-YY');
150 fnd_file.put_line(fnd_file.output,l_output_string);
151
152 l_output_string := 'Settlement Date:,,'||to_char(l_batch_details.settlement_date,'DD-MON-YY');
153 fnd_file.put_line(fnd_file.output,l_output_string);
154
155 l_output_string := 'Deploying Company Currency:,,'|| l_batch_details.batch_currency;
156 fnd_file.put_line(fnd_file.output,l_output_string);
157
158 l_output_string := 'Supplier Netted Amount:,,'||l_batch_details.total_netted_amt;
159 fnd_file.put_line(fnd_file.output,l_output_string);
160
161 l_output_string := 'Customer Netted Amount:,,'||l_batch_details.total_netted_amt;
162 fnd_file.put_line(fnd_file.output,l_output_string);
163
164 l_output_string := ' ';
165 fnd_file.put_line(fnd_file.output,l_output_string);
166 fnd_file.put_line(fnd_file.output,l_output_string);
167
168 l_output_string := 'Transactions Netted per Batch Number:,'|| l_batch_details.batch_number||',';
169 l_output_string := l_output_string || 'Batch Name:,'|| l_batch_details.batch_name;
170 fnd_file.put_line(fnd_file.output,l_output_string);
171
172 l_invoice_count := 0;
173 FOR l_invoice IN c_get_ap_invoices(l_batch_details.agreement_id) LOOP
174 l_invoice_count := l_invoice_count + 1;
175
176 IF l_invoice_count = 1 THEN
177 l_supplier := l_invoice.supplier;
178 l_supplier_number := l_invoice.supplier_number;
179 l_supplier_site := l_invoice.supplier_site;
180
181 l_output_string := ' ';
182 fnd_file.put_line(fnd_file.output,l_output_string);
183
184 l_output_string := 'Supplier:,'|| l_supplier;
185 fnd_file.put_line(fnd_file.output,l_output_string);
186
187 l_output_string := 'Supplier Number:,'|| l_supplier_number;
188 fnd_file.put_line(fnd_file.output,l_output_string);
189
190 l_output_string := 'Site:,'|| l_supplier_site;
191 fnd_file.put_line(fnd_file.output,l_output_string);
192
193 l_output_string := ' ';
194 fnd_file.put_line(fnd_file.output,l_output_string);
195
196 l_output_string := 'Invoice Number,Invoice Date,Invoice Currency,Invoice Amount,Ledger Currency,Ledger Amount, Netted Amount ('||l_batch_details.batch_currency||')';
197 fnd_file.put_line(fnd_file.output,l_output_string);
198
199 END IF;
200
201 IF l_supplier <> l_invoice.supplier OR
202 l_supplier_number <> l_invoice.supplier_number OR
203 l_supplier_site <> l_invoice.supplier_site THEN
204
205 l_supplier := l_invoice.supplier;
206 l_supplier_number := l_invoice.supplier_number;
207 l_supplier_site := l_invoice.supplier_site;
208
209 l_output_string := ' ';
210 fnd_file.put_line(fnd_file.output,l_output_string);
211
212 l_output_string := 'Supplier:,'|| l_supplier;
213 fnd_file.put_line(fnd_file.output,l_output_string);
214
215 l_output_string := 'Supplier Number:,'|| l_supplier_number;
216 fnd_file.put_line(fnd_file.output,l_output_string);
217
218 l_output_string := 'Site:,'|| l_supplier_site;
219 fnd_file.put_line(fnd_file.output,l_output_string);
220
221 l_output_string := ' ';
222 fnd_file.put_line(fnd_file.output,l_output_string);
223
224 l_output_string := 'Invoice Number,Invoice Date,Invoice Currency,Invoice Amount,Ledger Currency,Ledger Amount, Netted Amount ('||l_batch_details.batch_currency||')';
225 fnd_file.put_line(fnd_file.output,l_output_string);
226
227 END IF;
228
229 l_output_string := l_invoice.invoice_number||',';
230 l_output_string := l_output_string || to_char(l_invoice.invoice_date,'DD-MON-YY') || ',';
231 l_output_string := l_output_string || l_invoice.invoice_currency || ',';
232 l_output_string := l_output_string || to_char(l_invoice.invoice_amount) || ',';
233 l_output_string := l_output_string || l_func_currency || ',';
234 IF l_invoice.ledger_amount IS NULL THEN
235 l_invoice.ledger_amount := l_invoice.invoice_amount;
236 END IF;
237 l_output_string := l_output_string || to_char(l_invoice.ledger_amount) || ',';
238 l_output_string := l_output_string || to_char(l_invoice.netted_amount);
239 fnd_file.put_line(fnd_file.output,l_output_string);
240 END LOOP;
241
242
243 l_transaction_count := 0;
244 FOR l_transaction IN c_get_ar_transactions(l_batch_details.agreement_id) LOOP
245 l_transaction_count := l_transaction_count + 1;
246
247 IF l_transaction_count = 1 THEN
248 l_customer := l_transaction.customer;
249 l_customer_number := l_transaction.customer_number;
250 l_location := l_transaction.location;
251
252 l_output_string := ' ';
253 fnd_file.put_line(fnd_file.output,l_output_string);
254
255 l_output_string := 'Customer:,'|| l_customer;
256 fnd_file.put_line(fnd_file.output,l_output_string);
257
258 l_output_string := 'Customer Account Number:,'|| l_customer_number;
259 fnd_file.put_line(fnd_file.output,l_output_string);
260
261 l_output_string := 'Location:,'|| l_location;
262 fnd_file.put_line(fnd_file.output,l_output_string);
263
264 l_output_string := ' ';
265 fnd_file.put_line(fnd_file.output,l_output_string);
266
267 l_output_string := 'Transaction Number,Transaction Date,Transaction Currency,Transaction Amount,Ledger Currency,Ledger Amount,Netted Amount ('||l_batch_details.batch_currency||')';
268 fnd_file.put_line(fnd_file.output,l_output_string);
269
270 END IF;
271
272 IF l_customer <> l_transaction.customer OR
273 l_customer_number <> l_transaction.customer_number OR
274 l_location <> l_transaction.location THEN
275
276 l_customer := l_transaction.customer;
277 l_customer_number := l_transaction.customer_number;
278 l_location := l_transaction.location;
279
280 l_output_string := ' ';
281 fnd_file.put_line(fnd_file.output,l_output_string);
282
283 l_output_string := 'Customer:,'|| l_customer;
284 fnd_file.put_line(fnd_file.output,l_output_string);
285
286 l_output_string := 'Customer Number:,'|| l_customer_number;
287 fnd_file.put_line(fnd_file.output,l_output_string);
288
289 l_output_string := 'Location:,'|| l_location;
290 fnd_file.put_line(fnd_file.output,l_output_string);
291
292 l_output_string := ' ';
293 fnd_file.put_line(fnd_file.output,l_output_string);
294
295 l_output_string := 'Transaction Number,Transaction Date,Transaction Currency,Transaction Amount,Ledger Currency,Ledger Amount,Netted Amount ('||l_batch_details.batch_currency||')';
296 fnd_file.put_line(fnd_file.output,l_output_string);
297
298 END IF;
299
300 l_output_string := l_transaction.transaction_number||',';
301 l_output_string := l_output_string || to_char(l_transaction.transaction_date,'DD-MON-YY') || ',';
302 l_output_string := l_output_string || l_transaction.transaction_currency || ',';
303 l_output_string := l_output_string || to_char(l_transaction.transaction_amount) || ',';
304 l_output_string := l_output_string || l_func_currency || ',';
305 l_output_string := l_output_string || to_char(l_transaction.ledger_amount) || ',';
306 l_output_string := l_output_string || to_char(l_transaction.netted_amount);
307 fnd_file.put_line(fnd_file.output,l_output_string);
308 END LOOP;
309
310 retcode := 0;
311 EXCEPTION
312 WHEN OTHERS THEN
313 retcode := 2 ;
314 errbuf := sqlerrm;
315 END extract_data;
316
317 END FUN_NET_EXTRACT_PKG;