eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FV_SLA_AP_PROCESSING_PKG

Source


1 PACKAGE BODY fv_sla_ap_processing_pkg AS
2 --$Header: FVXLAAPB.pls 120.9.12020000.3 2013/02/13 14:40:17 snama ship $
3 
4 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6 
7   c_FAILURE   CONSTANT  NUMBER := -1;
8   c_SUCCESS   CONSTANT  NUMBER := 0;
9   C_GL_APPLICATION CONSTANT NUMBER := 101;
10   CRLF CONSTANT VARCHAR2(1) := FND_GLOBAL.newline;
11   g_path_name   CONSTANT VARCHAR2(200)  := 'fv.plsql.fvxlaapb.fv_sla_ap_processing_pkg';
12   C_STATE_LEVEL CONSTANT NUMBER       :=  FND_LOG.LEVEL_STATEMENT;
13   C_PROC_LEVEL  CONSTANT  NUMBER       :=  FND_LOG.LEVEL_PROCEDURE;
14 
15   PROCEDURE trace
16   (
17     p_level             IN NUMBER,
18     p_procedure_name    IN VARCHAR2,
19     p_debug_info        IN VARCHAR2
20   )
21   IS
22   BEGIN
23     fv_sla_utl_processing_pkg.trace
24     (
25       p_level             => p_level,
26       p_procedure_name    => p_procedure_name,
27       p_debug_info        => p_debug_info
28     );
29   END trace;
30 
31   PROCEDURE stack_error
32   (
33     p_program_name  IN VARCHAR2,
34     p_location      IN VARCHAR2,
35     p_error_message IN VARCHAR2
36   )
37   IS
38   BEGIN
39     fv_sla_utl_processing_pkg.stack_error
40     (
41       p_program_name  => p_program_name,
42       p_location      => p_location,
43       p_error_message => p_error_message
44     );
45   END;
46 
47   PROCEDURE init
48   IS
49     l_procedure_name       VARCHAR2(100) :='.init';
50   BEGIN
51     trace(C_STATE_LEVEL, l_procedure_name, 'Package Information');
52     trace(C_STATE_LEVEL, l_procedure_name, '$Header: FVXLAAPB.pls 120.9.12020000.3 2013/02/13 14:40:17 snama ship $');
53   END;
54 
55   PROCEDURE process_invoice
56   (
57     p_application_id    IN NUMBER,
58     p_fv_extract_detail IN OUT NOCOPY fv_sla_utl_processing_pkg.fv_ref_detail,
59     p_error_code        OUT NOCOPY NUMBER,
60     p_error_desc        OUT NOCOPY VARCHAR2
61   )
62   IS
63     l_debug_info                VARCHAR2(240);
64     l_procedure_name            VARCHAR2(100):='.process_invoice';
65     l_index                     NUMBER;
66     l_ledger_info               fv_sla_utl_processing_pkg.LedgerRecType;
67     l_fund_value                VARCHAR(30);
68     l_account_value             VARCHAR2(30);
69     l_bfy_value                 VARCHAR2(30);
70     l_treasury_symbol           fv_treasury_symbols.treasury_symbol%TYPE;
71     l_pya_type                  VARCHAR2(20);
72     l_ccid                      NUMBER;
73     l_po_code_combination_id    NUMBER;
74     l_no_pya_acct_flag          fv_treasury_symbols.no_pya_acct_flag%TYPE;
75     l_fund_type                 fv_treasury_symbols.fund_type%TYPE;
76     l_rec_attribute_cat         fv_ar_acc_category_map_dtl.transaction_category%TYPE;
77     l_fed_non_fed_ind           VARCHAR2(1);
78     l_advance_required          VARCHAR2(1);
79     l_advance_amount            NUMBER;
80     l_cust_or_vend              VARCHAR2(1);
81     l_cust_or_vend_id           NUMBER;
82 
83     CURSOR c_ap_invoice_details
84     IS
85     SELECT aid.invoice_id,
86            e.event_id,
87            e.event_type_code,
88            e.ledger_id,
89            e.entity_code,
90            aid.invoice_distribution_id,
91            aid.po_distribution_id,
92            aid.dist_code_combination_id,
93            aid.accounting_date,
94            aid.amount,
95            aid.base_amount,
96            aid.quantity_variance,
97            aid.base_quantity_variance,
98            aid.amount_variance,
99            aid.base_amount_variance,
100            aid.line_type_lookup_code,
101            ai.invoice_type_lookup_code,
102            ai.source,
103            ai.application_id ref_application_id,
104            ai.product_table ref_product_table,
105            ai.reference_key1 ref_key1,
106            ai.reference_key2 ref_key2,
107            ai.vendor_id,
108            aid.project_id,
109            aid.invoice_distribution_id line_number
110       FROM ap_invoice_distributions_all aid,
111            xla_events_gt e,
112            ap_invoices_all ai
113      WHERE aid.bc_event_id = e.event_id
114        AND e.application_id = p_application_id
115        AND e.entity_code = 'AP_INVOICES'
116        AND aid.line_type_lookup_code NOT IN ('ACCRUAL')
117        AND ai.invoice_id = aid.invoice_id
118        AND ((e.budgetary_control_flag = 'Y' AND
119              ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
120                             OR
121             (e.budgetary_control_flag = 'Y' AND
122              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
123              ai.source <> 'Receivables')
124                             OR
125             (e.budgetary_control_flag = 'N' AND
126              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
127              ai.source = 'Receivables'))
128        AND e.event_type_code IN ('INVOICE VALIDATED',
129                                  'INVOICE CANCELLED',
130                                  'INVOICE ADJUSTED',
131                                  'CREDIT MEMO VALIDATED',
132                                  'CREDIT MEMO CANCELLED',
133                                  'CREDIT MEMO ADJUSTED',
134                                  'DEBIT MEMO VALIDATED',
135                                  'DEBIT MEMO CANCELLED',
136                                  'DEBIT MEMO ADJUSTED')
137      UNION
138     SELECT asat.invoice_id,
139            e.event_id,
140            e.event_type_code,
141            e.ledger_id,
142            e.entity_code,
143            asat.invoice_distribution_id,
144            asat.po_distribution_id,
145            asat.dist_code_combination_id,
146            asat.accounting_date,
147            asat.amount,
148            asat.base_amount,
149            asat.quantity_variance,
150            asat.base_quantity_variance,
151            asat.amount_variance,
152            asat.base_amount_variance,
153            asat.line_type_lookup_code,
154            ai.invoice_type_lookup_code,
155            ai.source,
156            ai.application_id ref_application_id,
157            ai.product_table ref_product_table,
158            ai.reference_key1 ref_key1,
159            ai.reference_key2 ref_key2,
160            ai.vendor_id,
161            asat.project_id,
162            asat.invoice_distribution_id line_number
163       FROM ap_self_assessed_tax_dist_all asat,
164            xla_events_gt e,
165            ap_invoices_all ai
166      WHERE asat.bc_event_id = e.event_id
167        AND e.application_id = p_application_id
168        AND e.entity_code = 'AP_INVOICES'
169        AND asat.line_type_lookup_code NOT IN ('ACCRUAL')
170        AND ai.invoice_id = asat.invoice_id
171        AND ((e.budgetary_control_flag = 'Y' AND
172              ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
173                             OR
174             (e.budgetary_control_flag = 'Y' AND
175              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
176              ai.source <> 'Receivables')
177                             OR
178             (e.budgetary_control_flag = 'N' AND
179              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
180              ai.source = 'Receivables'))
181        AND e.event_type_code IN ('INVOICE VALIDATED',
182                                  'INVOICE CANCELLED',
183                                  'INVOICE ADJUSTED',
184                                  'CREDIT MEMO VALIDATED',
185                                  'CREDIT MEMO CANCELLED',
186                                  'CREDIT MEMO ADJUSTED',
187                                  'DEBIT MEMO VALIDATED',
188                                  'DEBIT MEMO CANCELLED',
189                                  'DEBIT MEMO ADJUSTED')
190     UNION
191     SELECT aid.invoice_id,
192            e.event_id,
193            e.event_type_code,
194            e.ledger_id,
195            e.entity_code,
196            aid.invoice_distribution_id,
197            aid.po_distribution_id,
198            aid.dist_code_combination_id,
199            aid.accounting_date,
200            apad.amount,
201            apad.base_amount,
202            aid.quantity_variance,
203            aid.base_quantity_variance,
204            aid.amount_variance,
205            aid.base_amount_variance,
206            aid.line_type_lookup_code,
207            ai.invoice_type_lookup_code,
208            ai.source,
209            ai.application_id ref_application_id,
210            ai.product_table ref_product_table,
211            ai.reference_key1 ref_key1,
212            ai.reference_key2 ref_key2,
213            ai.vendor_id,
214            aid.project_id,
215            apad.prepay_app_dist_id line_number
216       FROM ap_invoice_distributions_all aid,
217            ap_prepay_app_dists apad,
218            xla_events_gt e,
219            ap_invoices_all ai
220      WHERE aid.bc_event_id = e.event_id
221        AND e.application_id = p_application_id
222        AND e.entity_code = 'AP_INVOICES'
223        AND aid.line_type_lookup_code NOT IN ('ACCRUAL')
224        AND ai.invoice_id = aid.invoice_id
225        AND apad.prepay_app_distribution_id = aid.invoice_distribution_id
226        AND ((e.budgetary_control_flag = 'Y' AND
227              ai.invoice_type_lookup_code <> 'PAYMENT REQUEST')
228                             OR
229             (e.budgetary_control_flag = 'Y' AND
230              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
231              ai.source <> 'Receivables')
232                             OR
233             (e.budgetary_control_flag = 'N' AND
234              ai.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
235              ai.source = 'Receivables'))
236        AND e.event_type_code IN ('PREPAYMENT APPLIED', 'PREPAYMENT UNAPPLIED')
237      ORDER BY 1, 2;
238 
239   BEGIN
240     l_procedure_name := g_path_name || l_procedure_name;
241     p_error_code := c_SUCCESS;
242     -------------------------------------------------------------------------
243     l_debug_info := 'Begin of procedure '||l_procedure_name;
244     trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
245     -------------------------------------------------------------------------
246 
247     l_index := p_fv_extract_detail.COUNT;
248     FOR invoice_rec IN c_ap_invoice_details LOOP
249       fv_sla_utl_processing_pkg.g_CurrentEventId := invoice_rec.event_id; --For Error Handling
250       trace(C_STATE_LEVEL, l_procedure_name, 'event_id='||invoice_rec.event_id);
251       trace(C_STATE_LEVEL, l_procedure_name, 'line_number='||invoice_rec.line_number);
252       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_distribution_id='||invoice_rec.invoice_distribution_id);
253       trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||invoice_rec.event_type_code);
254       trace(C_STATE_LEVEL, l_procedure_name, 'po_distribution_id='||invoice_rec.po_distribution_id);
255       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_id='||invoice_rec.invoice_id);
256       trace(C_STATE_LEVEL, l_procedure_name, 'ledger_id='||invoice_rec.ledger_id);
257       trace(C_STATE_LEVEL, l_procedure_name, 'dist_code_combination_id='||invoice_rec.dist_code_combination_id);
258       trace(C_STATE_LEVEL, l_procedure_name, 'accounting_date='||invoice_rec.accounting_date);
259       trace(C_STATE_LEVEL, l_procedure_name, 'amount='||invoice_rec.amount);
260       trace(C_STATE_LEVEL, l_procedure_name, 'base_amount='||invoice_rec.base_amount);
261       trace(C_STATE_LEVEL, l_procedure_name, 'quantity_variance='||invoice_rec.quantity_variance);
262       trace(C_STATE_LEVEL, l_procedure_name, 'base_quantity_variance='||invoice_rec.base_quantity_variance);
263       trace(C_STATE_LEVEL, l_procedure_name, 'amount_variance='||invoice_rec.amount_variance);
264       trace(C_STATE_LEVEL, l_procedure_name, 'base_amount_variance='||invoice_rec.base_amount_variance);
265       trace(C_STATE_LEVEL, l_procedure_name, 'line_type_lookup_code='||invoice_rec.line_type_lookup_code);
266       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_type_lookup_code='||invoice_rec.invoice_type_lookup_code);
267       trace(C_STATE_LEVEL, l_procedure_name, 'source='||invoice_rec.source);
268       trace(C_STATE_LEVEL, l_procedure_name, 'ref_application_id='||invoice_rec.ref_application_id);
269       trace(C_STATE_LEVEL, l_procedure_name, 'ref_product_table='||invoice_rec.ref_product_table);
270       trace(C_STATE_LEVEL, l_procedure_name, 'ref_key1='||invoice_rec.ref_key1);
271       trace(C_STATE_LEVEL, l_procedure_name, 'ref_key2='||invoice_rec.ref_key2);
272 
273       IF (p_error_code = c_SUCCESS) THEN
274         l_index := l_index + 1;
275         p_fv_extract_detail(l_index).event_id := invoice_rec.event_id;
276         p_fv_extract_detail(l_index).line_number := invoice_rec.line_number;
277         p_fv_extract_detail(l_index).po_distribution_id := invoice_rec.po_distribution_id;
278         p_fv_extract_detail(l_index).prior_year_flag := 'N';
279         fv_sla_utl_processing_pkg.init_extract_record(p_application_id, p_fv_extract_detail(l_index));
280 
281         trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.get_ledger_info');
282         trace(C_STATE_LEVEL, l_procedure_name, 'ledger_id='||invoice_rec.ledger_id);
283         fv_sla_utl_processing_pkg.get_ledger_info
284         (
285           p_ledger_id  => invoice_rec.ledger_id,
286           p_ledger_rec => l_ledger_info,
287           p_error_code => p_error_code,
288           p_error_desc => p_error_desc
289         );
290         trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_ledger_info Returned');
291         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
292         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
293       END IF;
294 
295       IF (p_error_code = c_SUCCESS) THEN
296         l_po_code_combination_id := NULL;
297         IF (invoice_rec.po_distribution_id IS NOT NULL) THEN
298           trace(C_STATE_LEVEL, l_procedure_name, 'Selecting from po_distributions_all');
299           BEGIN
300             SELECT pod.code_combination_id
301               INTO l_po_code_combination_id
302               FROM po_distributions_all pod
303              WHERE pod.po_distribution_id = invoice_rec.po_distribution_id;
304             trace(C_STATE_LEVEL, l_procedure_name, 'l_po_code_combination_id='||l_po_code_combination_id);
305           EXCEPTION
306             WHEN OTHERS THEN
307               p_error_code := c_FAILURE;
308               p_error_desc := SQLERRM;
309               stack_error (l_procedure_name, 'po_distributions_all', p_error_desc);
310               trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_po_distributions_all:'||p_error_desc);
311           END;
312         END IF;
313       END IF;
314 
315       IF (p_error_code = C_SUCCESS) THEN
316         l_ccid := NVL(l_po_code_combination_id, invoice_rec.dist_code_combination_id);
317         trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_segment_values');
318         trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
319         trace(C_STATE_LEVEL, l_procedure_name, 'p_ccid='||l_ccid);
320         fv_sla_utl_processing_pkg.get_segment_values
321         (
322           p_ledger_id     => l_ledger_info.ledger_id,
323           p_ccid          => l_ccid,
324           p_fund_value    => l_fund_value,
325           p_account_value => l_account_value,
326           p_bfy_value     => l_bfy_value,
327           p_error_code    => p_error_code,
328           p_error_desc    => p_error_desc
329         );
330         trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_segment_values Returned');
331         trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_value='||l_fund_value);
332         trace(C_STATE_LEVEL, l_procedure_name, 'l_account_value='||l_account_value);
333         trace(C_STATE_LEVEL, l_procedure_name, 'l_bfy_value='||l_bfy_value);
334         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
335         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
336       END IF;
337 
338       l_cust_or_vend := 'V';
339       l_cust_or_vend_id := invoice_rec.vendor_id;
340 
341       IF (p_error_code = C_SUCCESS) THEN
342         IF (invoice_rec.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
343             invoice_rec.source = 'Receivables' AND
344             invoice_rec.ref_application_id = 222 AND
345             invoice_rec.ref_product_table = 'AR_RECEIVABLE_APPLICATIONS_ALL' AND
346             invoice_rec.ref_key1 IS NOT NULL) THEN
347           l_rec_attribute_cat := NULL;
348           trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_ar_transaction_category');
349           trace(C_STATE_LEVEL, l_procedure_name, 'ref_key1='||invoice_rec.ref_key1);
350           fv_sla_ar_processing_pkg.get_receipt_info_for_pr
351           (
352             p_transcation_id     => invoice_rec.ref_key1,
353             p_rec_attribute_cat  => l_rec_attribute_cat,
354             p_advance_required   => l_advance_required,
355             p_advance_amount     => l_advance_amount,
356             p_customer_id        => l_cust_or_vend_id,
357             p_error_code         => p_error_code,
358             p_error_desc         => p_error_desc
359           );
360           trace(C_STATE_LEVEL, l_procedure_name, 'get_ar_transaction_category Returned');
361           trace(C_STATE_LEVEL, l_procedure_name, 'l_rec_attribute_cat='||l_rec_attribute_cat);
362           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
363           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
364           l_cust_or_vend := 'C';
365           p_fv_extract_detail(l_index).ar_transaction_category := l_rec_attribute_cat;
366           p_fv_extract_detail(l_index).advance_required := l_advance_required;
367         END IF;
368       END IF;
369 
370       IF (p_error_code = C_SUCCESS) THEN
371         trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fed_non_fed_ind');
372         trace(C_STATE_LEVEL, l_procedure_name, 'p_cust_vend_id='||invoice_rec.vendor_id);
373         fv_sla_utl_processing_pkg.get_fed_non_fed_ind
374         (
375           p_cust_vend_id     => l_cust_or_vend_id,
376           p_cust_or_vend     => l_cust_or_vend,
377           p_fed_non_fed_ind  => l_fed_non_fed_ind,
378           p_error_code       => p_error_code,
379           p_error_desc       => p_error_desc
380         );
381         trace(C_STATE_LEVEL, l_procedure_name, 'get_fed_non_fed_ind Returned');
382         trace(C_STATE_LEVEL, l_procedure_name, 'l_fed_non_fed_ind='||l_fed_non_fed_ind);
383         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
384         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
385         p_fv_extract_detail(l_index).fed_non_fed_ind := l_fed_non_fed_ind;
386       END IF;
387 
388 
389       IF (p_error_code = C_SUCCESS) THEN
390         trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
391         trace(C_STATE_LEVEL, l_procedure_name, 'p_account_value='||l_account_value);
392         trace(C_STATE_LEVEL, l_procedure_name, 'p_bfy_value='||l_bfy_value);
393         p_fv_extract_detail(l_index).fund_value :=l_fund_value;
394 
395         trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fund_details');
396         trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
397         trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
398         trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
399         trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||invoice_rec.accounting_date);
400         fv_sla_utl_processing_pkg.get_fund_details
401         (
402           p_application_id     => p_application_id,
403           p_ledger_id          => l_ledger_info.ledger_id,
404           p_fund_value         => l_fund_value,
405           p_gl_date            => invoice_rec.accounting_date,
406           p_appor_category     => p_fv_extract_detail(l_index).fund_category,
407           p_direct_or_reimb    => p_fv_extract_detail(l_index).direct_or_reimb,
408           p_fund_status        => p_fv_extract_detail(l_index).fund_expired_status,
409           p_fund_time_frame    => p_fv_extract_detail(l_index).fund_time_frame,
410           p_treasury_symbol_id => p_fv_extract_detail(l_index).treasury_symbol_id,
411           p_treasury_symbol    => l_treasury_symbol,
412           p_no_pya_acct_flag   => l_no_pya_acct_flag,
413           p_fund_type          => l_fund_type,
414           p_error_code         => p_error_code,
415           p_error_desc         => p_error_desc
416         );
417         trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_fund_details Returned');
418         trace(C_STATE_LEVEL, l_procedure_name, 'fund_category='||p_fv_extract_detail(l_index).fund_category);
419         trace(C_STATE_LEVEL, l_procedure_name, 'fund_expired_status='||p_fv_extract_detail(l_index).fund_expired_status);
420         trace(C_STATE_LEVEL, l_procedure_name, 'fund_time_frame='||p_fv_extract_detail(l_index).fund_time_frame);
421         trace(C_STATE_LEVEL, l_procedure_name, 'treasury_symbol_id='||p_fv_extract_detail(l_index).treasury_symbol_id);
422         trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
423         trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_type='||l_fund_type);
424         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
425         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
426       END IF;
427 
428       IF (p_error_code = C_SUCCESS) THEN
429         trace(C_STATE_LEVEL, l_procedure_name, 'fund_category='||p_fv_extract_detail(l_index).fund_category);
430         trace(C_STATE_LEVEL, l_procedure_name, 'fund_expired_status='||p_fv_extract_detail(l_index).fund_expired_status);
431         trace(C_STATE_LEVEL, l_procedure_name, 'fund_time_frame='||p_fv_extract_detail(l_index).fund_time_frame);
432         trace(C_STATE_LEVEL, l_procedure_name, 'treasury_symbol_id='||p_fv_extract_detail(l_index).treasury_symbol_id);
433         trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
434         trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_type='||l_fund_type);
435         trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||invoice_rec.event_type_code);
436 
437         p_fv_extract_detail(l_index).acc_unpaid_exp_amount := NVL(invoice_rec.base_amount, invoice_rec.amount);
438         p_fv_extract_detail(l_index).ent_unpaid_exp_amount := invoice_rec.amount;
439         p_fv_extract_detail(l_index).acc_charge_amount := NVL(invoice_rec.base_amount, invoice_rec.amount);
440         p_fv_extract_detail(l_index).ent_charge_amount := invoice_rec.amount;
441         p_fv_extract_detail(l_index).fund_type := l_fund_type;
442 
443         IF (invoice_rec.po_distribution_id IS NULL) THEN
444           p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*NVL(invoice_rec.base_amount, invoice_rec.amount);
445           p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*invoice_rec.amount;
446         ELSE
447           IF (invoice_rec.line_type_lookup_code IN ('IPV', 'ERV')) THEN
448             p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*(invoice_rec.amount-(NVL(invoice_rec.quantity_variance,0)+NVL(invoice_rec.amount_variance,0)));
449             p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*(NVL(invoice_rec.base_amount, invoice_rec.amount)-
450                                                                       (NVL(NVL(invoice_rec.base_quantity_variance, invoice_rec.quantity_variance),0)+
451                                                                        NVL(NVL(invoice_rec.base_amount_variance, invoice_rec.amount_variance),0)));
452           ELSIF (invoice_rec.line_type_lookup_code IN ('PREPAY')) THEN
453             p_fv_extract_detail(l_index).acc_unpaid_obl_amount := NVL(invoice_rec.base_amount, invoice_rec.amount)-
454                                                                       (NVL(NVL(invoice_rec.base_quantity_variance, invoice_rec.quantity_variance),0)+
455                                                                        NVL(NVL(invoice_rec.base_amount_variance, invoice_rec.amount_variance),0));
456             p_fv_extract_detail(l_index).ent_unpaid_obl_amount := invoice_rec.amount-(NVL(invoice_rec.quantity_variance,0)+NVL(invoice_rec.amount_variance,0));
457           ELSE
458             p_fv_extract_detail(l_index).acc_unpaid_obl_amount := -1*(NVL(invoice_rec.base_amount, invoice_rec.amount)-
459                                                                       (NVL(NVL(invoice_rec.base_quantity_variance, invoice_rec.quantity_variance),0)+
460                                                                        NVL(NVL(invoice_rec.base_amount_variance, invoice_rec.amount_variance),0)));
461             p_fv_extract_detail(l_index).ent_unpaid_obl_amount := -1*(invoice_rec.amount-(NVL(invoice_rec.quantity_variance,0)+NVL(invoice_rec.amount_variance,0)));
462             p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*(NVL(NVL(invoice_rec.base_quantity_variance, invoice_rec.quantity_variance),0)+
463                                                                             NVL(NVL(invoice_rec.base_amount_variance, invoice_rec.amount_variance),0));
464             p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*(NVL(invoice_rec.quantity_variance,0)+NVL(invoice_rec.amount_variance,0));
465           END IF;
466         END IF;
467       END IF;
468 
469       IF (p_error_code = C_SUCCESS) THEN
470         p_fv_extract_detail(l_index).prior_year_flag := 'N';
471         IF (NVL(l_no_pya_acct_flag, 'N') = 'N') THEN
472           trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_prior_year_status');
473           trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
474           trace(C_STATE_LEVEL, l_procedure_name, 'l_bfy_value='||l_bfy_value);
475           trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||invoice_rec.accounting_date);
476           fv_sla_utl_processing_pkg.get_prior_year_status
477           (
478             p_application_id => C_GL_APPLICATION,
479             p_ledger_id      => l_ledger_info.ledger_id,
480             p_bfy_value      => l_bfy_value,
481             p_gl_date        => invoice_rec.accounting_date,
482             p_pya            => p_fv_extract_detail(l_index).prior_year_flag,
483             p_pya_type       => l_pya_type,
484             p_error_code     => p_error_code,
485             p_error_desc     => p_error_desc
486           );
487           trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_prior_year_status Returned');
488           trace(C_STATE_LEVEL, l_procedure_name, 'l_pya='||p_fv_extract_detail(l_index).prior_year_flag);
489           trace(C_STATE_LEVEL, l_procedure_name, 'l_pya_type='||l_pya_type);
490           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
491           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
492         END IF;
493       END IF;
494 
495       IF (p_error_code <> c_SUCCESS) THEN
496         EXIT;
497       END IF;
498     END LOOP;
499 
500   EXCEPTION
501     WHEN OTHERS THEN
502       p_error_code := c_FAILURE;
503       fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
504       fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
505       p_error_desc := fnd_message.get;
506       stack_error (l_procedure_name, 'FINAL', p_error_desc);
507       trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
508   END;
509 
510   PROCEDURE process_payments
511   (
512     p_application_id    IN xla_events_gt.application_id%TYPE,
513     p_fv_extract_detail IN OUT NOCOPY fv_sla_utl_processing_pkg.fv_ref_detail,
514     p_error_code        OUT NOCOPY NUMBER,
515     p_error_desc        OUT NOCOPY VARCHAR2
516   )
517   IS
518     l_debug_info                   VARCHAR2(240);
519     l_procedure_name               VARCHAR2(100):='.process_payments';
520 
521     l_index NUMBER;
522     l_ledger_info       fv_sla_utl_processing_pkg.LedgerRecType;
523     l_fund_value        VARCHAR(30);
524     l_account_value     VARCHAR2(30);
525     l_bfy_value         VARCHAR2(30);
526     l_treasury_symbol   fv_treasury_symbols.treasury_symbol%TYPE;
527     l_pya_type          VARCHAR2(20);
528     l_no_pya_acct_flag  fv_treasury_symbols.no_pya_acct_flag%TYPE;
529     l_org_info          fv_sla_utl_processing_pkg.OrgRecType;
530     l_bank_info         fv_sla_utl_processing_pkg.BankRecType;
531     l_process_row       BOOLEAN := TRUE;
532     l_fund_type         fv_treasury_symbols.fund_type%TYPE;
533     l_rec_attribute_cat fv_ar_acc_category_map_dtl.transaction_category%TYPE;
534     l_fed_non_fed_ind           VARCHAR2(1);
535     l_advance_required          VARCHAR2(1);
536     l_advance_amount            NUMBER;
537     l_cust_or_vend              VARCHAR2(1);
538     l_cust_or_vend_id           NUMBER;
539 
540     CURSOR c_ap_payment_details
541     IS
542     SELECT ai.invoice_id,
543            e.event_id,
544            e.event_type_code,
545            e.ledger_id,
546            e.entity_code,
547            aphd.payment_hist_dist_id,
548            aid.dist_code_combination_id,
549            aph.accounting_date,
550            aphd.amount,
551            aphd.paid_base_amount,
552            aphd.invoice_dist_amount,
553            aphd.invoice_dist_base_amount,
554            ai.invoice_type_lookup_code,
555            aid.po_distribution_id,
556            aphd.pay_dist_lookup_code,
557            aca.payment_type_flag,
558            aca.ce_bank_acct_use_id,
559            aca.org_id,
560            ai.source,
561            ai.application_id ref_application_id,
562            ai.product_table ref_product_table,
563            ai.reference_key1 ref_key1,
564            ai.reference_key2 ref_key2,
565            ai.vendor_id
566       FROM ap_checks_all aca,
567            ap_payment_hist_dists aphd,
568            ap_invoice_distributions_all aid,
569            ap_payment_history_all aph,
570            xla_events_gt e,
571            ap_invoices_all ai
572      WHERE aphd.accounting_event_id = e.event_id
573        AND e.application_id = p_application_id
574        AND aphd.invoice_distribution_id = aid.invoice_distribution_id
575        AND aphd.payment_history_id = aph.payment_history_id
576        AND aid.invoice_id = ai.invoice_id
577        AND aph.check_id = aca.check_id
578 --       AND (aca.payment_type_flag IN ('M', 'N', 'Q', 'R') OR
579 --            (aca.payment_type_flag IN ('A') AND
580 --             aphd.pay_dist_lookup_code <> 'CASH') OR
581 --             ai.invoice_type_lookup_code = 'INTEREST')
582        AND aphd.pay_dist_lookup_code <> 'EXCHANGE RATE VARIANCE'
583        AND e.entity_code = 'AP_PAYMENTS'
584        AND e.event_type_code IN ('MANUAL PAYMENT ADJUSTED',
585                                  'PAYMENT ADJUSTED',
586                                  'PAYMENT CANCELLED',
587                                  'PAYMENT CREATED',
588                                  'REFUND RECORDED',
589                                  'REFUND CANCELLED',
590                                  'REFUND ADJUSTED')
591      UNION
592     SELECT ai.invoice_id,
593            e.event_id,
594            e.event_type_code,
595            e.ledger_id,
596            e.entity_code,
597            aphd.payment_hist_dist_id,
598            aid.dist_code_combination_id,
599            aph.accounting_date,
600            DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.amount amount,
601            DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.paid_base_amount paid_base_amount,
602            DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.invoice_dist_amount invoice_dist_amount,
603            DECODE(e.event_type_code, 'TREASURY_BACKOUT', -1, 1)*aphd.invoice_dist_base_amount invoice_dist_base_amount,
604            ai.invoice_type_lookup_code,
605            aid.po_distribution_id,
606            aphd.pay_dist_lookup_code,
607            aca.payment_type_flag,
608            aca.ce_bank_acct_use_id,
609            aca.org_id,
610            ai.source,
611            ai.application_id ref_application_id,
612            ai.product_table ref_product_table,
613            ai.reference_key1 ref_key1,
614            ai.reference_key2 ref_key2,
615            ai.vendor_id
616       FROM ap_payment_hist_dists aphd,
617            ap_invoice_distributions_all aid,
618            ap_payment_history_all aph,
619            xla_events_gt e,
620            ap_invoices_all ai,
621            fv_treasury_confirmations_all ftc,
622            ap_checks_all aca
623      WHERE ftc.event_id = e.event_id
624        AND aca.payment_instruction_id = ftc.payment_instruction_id
625        AND aph.check_id = aca.check_id
626        AND aca.check_id  NOT IN (SELECT check_id
627                                    FROM fv_voided_checks fvc
628                                   WHERE fvc.payment_instruction_id = aca.payment_instruction_id)
629        AND e.application_id = p_application_id
630        AND aphd.invoice_distribution_id = aid.invoice_distribution_id
631        AND aphd.payment_history_id = aph.payment_history_id
632        AND aid.invoice_id = ai.invoice_id
633        AND aca.payment_type_flag IN ('A')
634        AND aphd.pay_dist_lookup_code = 'CASH'
635        AND e.entity_code = 'TREASURY_CONFIRMATION'
636        AND e.event_type_code IN ('TREASURY_CONFIRM', 'TREASURY_BACKOUT')
637        AND aca.status_lookup_code NOT IN ('VOIDED')
638      UNION
639     SELECT ai.invoice_id,
640            e.event_id,
641            e.event_type_code,
642            e.ledger_id,
643            e.entity_code,
644            aphd.payment_hist_dist_id,
645            aid.dist_code_combination_id,
646            aph.accounting_date,
647            aphd.amount amount,
648            aphd.paid_base_amount paid_base_amount,
649            aphd.invoice_dist_amount invoice_dist_amount,
650            aphd.invoice_dist_base_amount invoice_dist_base_amount,
651            ai.invoice_type_lookup_code,
652            aid.po_distribution_id,
653            aphd.pay_dist_lookup_code,
654            aca.payment_type_flag,
655            aca.ce_bank_acct_use_id,
656            aca.org_id,
657            ai.source,
658            ai.application_id ref_application_id,
659            ai.product_table ref_product_table,
660            ai.reference_key1 ref_key1,
661            ai.reference_key2 ref_key2,
662            ai.vendor_id
663       FROM ap_payment_hist_dists aphd,
664            ap_invoice_distributions_all aid,
665            ap_payment_history_all aph,
666            xla_events_gt e,
667            ap_invoices_all ai,
668            fv_voided_checks fvc,
669            ap_checks_all aca
670      WHERE fvc.event_id = e.event_id
671        AND aca.payment_instruction_id = fvc.payment_instruction_id
672        AND fvc.check_id = aca.check_id
673        AND aph.check_id = aca.check_id
674        AND aphd.invoice_distribution_id = aid.invoice_distribution_id
675        AND aphd.payment_history_id = aph.payment_history_id
676        AND aid.invoice_id = ai.invoice_id
677        AND aca.payment_type_flag IN ('A')
678        AND aphd.pay_dist_lookup_code = 'CASH'
679        AND aca.status_lookup_code IN ('VOIDED')
680        AND aph.transaction_type='PAYMENT CANCELLED'
681        AND e.application_id = p_application_id
682        AND e.entity_code = 'TREASURY_CONFIRMATION'
683        AND e.event_type_code IN ('TREASURY_VOID')
684      ORDER BY 1;
685 
686   BEGIN
687     l_procedure_name := g_path_name || l_procedure_name;
688     p_error_code := c_SUCCESS;
689     -------------------------------------------------------------------------
690     l_debug_info := 'Begin of procedure '||l_procedure_name;
691     trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
692     -------------------------------------------------------------------------
693 
694     l_index := p_fv_extract_detail.COUNT;
695     FOR payment_rec IN c_ap_payment_details LOOP
696       fv_sla_utl_processing_pkg.g_CurrentEventId := payment_rec.event_id; --For Error Handling
697       l_process_row := TRUE;
698       l_fund_type := NULL;
699       trace(C_STATE_LEVEL, l_procedure_name, 'org_id='||payment_rec.org_id);
700       trace(C_STATE_LEVEL, l_procedure_name, 'evetn_id='||payment_rec.event_id);
701       trace(C_STATE_LEVEL, l_procedure_name, 'payment_hist_dist_id='||payment_rec.payment_hist_dist_id);
702       trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||payment_rec.event_type_code);
703       trace(C_STATE_LEVEL, l_procedure_name, 'pay_dist_lookup_code='||payment_rec.pay_dist_lookup_code);
704       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_type_lookup_code='||payment_rec.invoice_type_lookup_code);
705       trace(C_STATE_LEVEL, l_procedure_name, 'paid_base_amount='||payment_rec.paid_base_amount);
706       trace(C_STATE_LEVEL, l_procedure_name, 'amount='||payment_rec.amount);
707       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_dist_amount='||payment_rec.invoice_dist_amount);
708       trace(C_STATE_LEVEL, l_procedure_name, 'invoice_dist_base_amount='||payment_rec.invoice_dist_base_amount);
709       trace(C_STATE_LEVEL, l_procedure_name, 'accounting_date='||payment_rec.accounting_date);
710       trace(C_STATE_LEVEL, l_procedure_name, 'dist_code_combination_id='||payment_rec.dist_code_combination_id);
711       trace(C_STATE_LEVEL, l_procedure_name, 'ce_bank_acct_use_id='||payment_rec.ce_bank_acct_use_id);
712       --Commented out for ER:11841305
713       /*
714       IF (p_error_code = C_SUCCESS) THEN
715         trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_org_info');
716         trace(C_STATE_LEVEL, l_procedure_name, 'p_org_id='||payment_rec.org_id);
717         fv_sla_utl_processing_pkg.get_org_info
718         (
719           p_org_id     => payment_rec.org_id,
720           p_org_rec    => l_org_info,
721           p_error_code => p_error_code,
722           p_error_desc => p_error_desc
723         );
724         trace(C_STATE_LEVEL, l_procedure_name, 'get_org_info Returned');
725         trace(C_STATE_LEVEL, l_procedure_name, 'l_org_info.dit_flag='||l_org_info.dit_flag);
726         trace(C_STATE_LEVEL, l_procedure_name, 'l_org_info.cash_account='||l_org_info.cash_account);
727         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
728         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
729       END IF;
730       */
731       IF (p_error_code = C_SUCCESS) THEN
732         trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_bank_account_info');
733         trace(C_STATE_LEVEL, l_procedure_name, 'p_bank_acct_use_id='||payment_rec.ce_bank_acct_use_id);
734         fv_sla_utl_processing_pkg.get_bank_account_info
735         (
736           p_bank_acct_use_id  => payment_rec.ce_bank_acct_use_id,
737           p_bank_rec          => l_bank_info,
738           p_error_code        => p_error_code,
739           p_error_desc        => p_error_desc
740         );
741         trace(C_STATE_LEVEL, l_procedure_name, 'get_bank_account_info Returned');
742         trace(C_STATE_LEVEL, l_procedure_name, 'l_bank_info.cash_bank_account_ccid='||l_bank_info.cash_bank_account_ccid);
743         trace(C_STATE_LEVEL, l_procedure_name, 'l_bank_info.l_bank_info='||l_bank_info.cash_bank_natural_account);
744         trace(C_STATE_LEVEL, l_procedure_name, 'l_bank_info.cash_clearing_account_ccid='||l_bank_info.cash_clearing_ccid);
745         trace(C_STATE_LEVEL, l_procedure_name, 'l_bank_info.cash_clearing_natural_account='||l_bank_info.cash_clearing_natural_account);
746         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
747         trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
748       END IF;
749 
750       IF ((p_error_code = C_SUCCESS) AND
751           (p_application_id = 200) AND
752           (payment_rec.payment_type_flag = 'A') AND
753           (payment_rec.pay_dist_lookup_code = 'CASH') AND
754           (payment_rec.invoice_type_lookup_code <> 'INTEREST')) THEN
755         l_process_row := FALSE;
756         --Modified for Bug11841305
757         --IF ((l_org_info.cash_account IS NOT NULL) AND
758         IF ((l_bank_info.cash_clearing_natural_account IS NOT NULL) AND
759             (l_bank_info.cash_bank_natural_account IS NOT NULL) AND
760             --(l_org_info.cash_account = l_bank_info.cash_bank_natural_account)) THEN
761             (l_bank_info.cash_clearing_natural_account = l_bank_info.cash_bank_natural_account)) THEN
762           l_process_row := TRUE;
763         END IF;
764       END IF;
765 
766       IF ((p_error_code = C_SUCCESS) AND l_process_row) THEN
767         IF (p_error_code = C_SUCCESS) THEN
768           l_index := l_index + 1;
769           p_fv_extract_detail(l_index).event_id := payment_rec.event_id;
770           p_fv_extract_detail(l_index).line_number := payment_rec.payment_hist_dist_id;
771           p_fv_extract_detail(l_index).prior_year_flag := 'N';
772           fv_sla_utl_processing_pkg.init_extract_record(200, p_fv_extract_detail(l_index));
773 
774           trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.get_ledger_info');
775           trace(C_STATE_LEVEL, l_procedure_name, 'ledger_id='||payment_rec.ledger_id);
776           fv_sla_utl_processing_pkg.get_ledger_info
777           (
778             p_ledger_id  => payment_rec.ledger_id,
779             p_ledger_rec => l_ledger_info,
780             p_error_code => p_error_code,
781             p_error_desc => p_error_desc
782           );
783           trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_ledger_info Returned');
784           trace(C_STATE_LEVEL, l_procedure_name, 'l_error_code='||p_error_code);
785           trace(C_STATE_LEVEL, l_procedure_name, 'l_error_desc='||p_error_desc);
786         END IF;
787 
788         IF (p_error_code = C_SUCCESS) THEN
789           trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_segment_values');
790           trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
791           trace(C_STATE_LEVEL, l_procedure_name, 'p_ccid='||payment_rec.dist_code_combination_id);
792           fv_sla_utl_processing_pkg.get_segment_values
793           (
794             p_ledger_id     => l_ledger_info.ledger_id,
795             p_ccid          => payment_rec.dist_code_combination_id,
796             p_fund_value    => l_fund_value,
797             p_account_value => l_account_value,
798             p_bfy_value     => l_bfy_value,
799             p_error_code    => p_error_code,
800             p_error_desc    => p_error_desc
801           );
802           trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_segment_values Returned');
803           trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_value='||l_fund_value);
804           trace(C_STATE_LEVEL, l_procedure_name, 'l_account_value='||l_account_value);
805           trace(C_STATE_LEVEL, l_procedure_name, 'l_bfy_value='||l_bfy_value);
806           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
807           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
808         END IF;
809 
810         l_cust_or_vend := 'V';
811         l_cust_or_vend_id := payment_rec.vendor_id;
812         IF (p_error_code = C_SUCCESS) THEN
813           IF (payment_rec.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
814               payment_rec.source = 'Receivables' AND
815               payment_rec.ref_application_id = 222 AND
816               payment_rec.ref_product_table = 'AR_RECEIVABLE_APPLICATIONS_ALL' AND
817               payment_rec.ref_key1 IS NOT NULL) THEN
818             l_rec_attribute_cat := NULL;
819             trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_ar_transaction_category');
820             trace(C_STATE_LEVEL, l_procedure_name, 'ref_key1='||payment_rec.ref_key1);
821             fv_sla_ar_processing_pkg.get_receipt_info_for_pr
822             (
823               p_transcation_id     => payment_rec.ref_key1,
824               p_rec_attribute_cat  => l_rec_attribute_cat,
825               p_advance_required   => l_advance_required,
826               p_advance_amount     => l_advance_amount,
827               p_customer_id        => l_cust_or_vend_id,
828               p_error_code         => p_error_code,
829               p_error_desc         => p_error_desc
830             );
831             trace(C_STATE_LEVEL, l_procedure_name, 'get_ar_transaction_category Returned');
832             trace(C_STATE_LEVEL, l_procedure_name, 'l_rec_attribute_cat='||l_rec_attribute_cat);
833             trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
834             trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
835             l_cust_or_vend := 'C';
836             p_fv_extract_detail(l_index).ar_transaction_category := l_rec_attribute_cat;
837             p_fv_extract_detail(l_index).advance_required := l_advance_required;
838           END IF;
839         END IF;
840 
841         IF (p_error_code = C_SUCCESS) THEN
842           trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fed_non_fed_ind');
843           trace(C_STATE_LEVEL, l_procedure_name, 'p_cust_vend_id='||payment_rec.vendor_id);
844           fv_sla_utl_processing_pkg.get_fed_non_fed_ind
845           (
846             p_cust_vend_id     => l_cust_or_vend_id,
847             p_cust_or_vend     => l_cust_or_vend,
848             p_fed_non_fed_ind  => l_fed_non_fed_ind,
849             p_error_code       => p_error_code,
850             p_error_desc       => p_error_desc
851           );
852           trace(C_STATE_LEVEL, l_procedure_name, 'get_fed_non_fed_ind Returned');
853           trace(C_STATE_LEVEL, l_procedure_name, 'l_fed_non_fed_ind='||l_fed_non_fed_ind);
854           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
855           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
856           p_fv_extract_detail(l_index).fed_non_fed_ind := l_fed_non_fed_ind;
857         END IF;
858 
859         IF (p_error_code = C_SUCCESS) THEN
860           trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
861           trace(C_STATE_LEVEL, l_procedure_name, 'p_account_value='||l_account_value);
862           trace(C_STATE_LEVEL, l_procedure_name, 'p_bfy_value='||l_bfy_value);
863           p_fv_extract_detail(l_index).fund_value :=l_fund_value;
864 
865           trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_fund_details');
866           trace(C_STATE_LEVEL, l_procedure_name, 'p_application_id='||p_application_id);
867           trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
868           trace(C_STATE_LEVEL, l_procedure_name, 'p_fund_value='||l_fund_value);
869           trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||payment_rec.accounting_date);
870           fv_sla_utl_processing_pkg.get_fund_details
871           (
872             p_application_id     => p_application_id,
873             p_ledger_id          => l_ledger_info.ledger_id,
874             p_fund_value         => l_fund_value,
875             p_gl_date            => payment_rec.accounting_date,
876             p_appor_category     => p_fv_extract_detail(l_index).fund_category,
877             p_direct_or_reimb    => p_fv_extract_detail(l_index).direct_or_reimb,
878             p_fund_status        => p_fv_extract_detail(l_index).fund_expired_status,
879             p_fund_time_frame    => p_fv_extract_detail(l_index).fund_time_frame,
880             p_treasury_symbol_id => p_fv_extract_detail(l_index).treasury_symbol_id,
881             p_treasury_symbol    => l_treasury_symbol,
882             p_no_pya_acct_flag   => l_no_pya_acct_flag,
883             p_fund_type          => l_fund_type,
884             p_error_code         => p_error_code,
885             p_error_desc         => p_error_desc
886           );
887           trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_fund_details Returned');
888           trace(C_STATE_LEVEL, l_procedure_name, 'fund_category='||p_fv_extract_detail(l_index).fund_category);
889           trace(C_STATE_LEVEL, l_procedure_name, 'fund_expired_status='||p_fv_extract_detail(l_index).fund_expired_status);
890           trace(C_STATE_LEVEL, l_procedure_name, 'fund_time_frame='||p_fv_extract_detail(l_index).fund_time_frame);
891           trace(C_STATE_LEVEL, l_procedure_name, 'treasury_symbol_id='||p_fv_extract_detail(l_index).treasury_symbol_id);
892           trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
893           trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_type='||l_fund_type);
894           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
895           trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
896         END IF;
897 
898         IF (p_error_code = C_SUCCESS) THEN
899           p_fv_extract_detail(l_index).prior_year_flag := 'N';
900           IF (NVL(l_no_pya_acct_flag, 'N') = 'N') THEN
901             trace(C_STATE_LEVEL, l_procedure_name, 'Calling get_prior_year_status');
902             trace(C_STATE_LEVEL, l_procedure_name, 'p_ledger_id='||l_ledger_info.ledger_id);
903             trace(C_STATE_LEVEL, l_procedure_name, 'l_bfy_value='||l_bfy_value);
904             trace(C_STATE_LEVEL, l_procedure_name, 'p_gl_date='||payment_rec.accounting_date);
905             fv_sla_utl_processing_pkg.get_prior_year_status
906             (
907               p_application_id => C_GL_APPLICATION,
908               p_ledger_id      => l_ledger_info.ledger_id,
909               p_bfy_value      => l_bfy_value,
910               p_gl_date        => payment_rec.accounting_date,
911               p_pya            => p_fv_extract_detail(l_index).prior_year_flag,
912               p_pya_type       => l_pya_type,
913               p_error_code     => p_error_code,
914               p_error_desc     => p_error_desc
915             );
916             trace(C_STATE_LEVEL, l_procedure_name, 'fv_sla_utl_processing_pkg.get_prior_year_status Returned');
917             trace(C_STATE_LEVEL, l_procedure_name, 'prior_year_flag='||p_fv_extract_detail(l_index).prior_year_flag);
918             trace(C_STATE_LEVEL, l_procedure_name, 'l_pya_type='||l_pya_type);
919             trace(C_STATE_LEVEL, l_procedure_name, 'p_error_code='||p_error_code);
920             trace(C_STATE_LEVEL, l_procedure_name, 'p_error_desc='||p_error_desc);
921           END IF;
922         END IF;
923 
924         IF (p_error_code = C_SUCCESS) THEN
925           trace(C_STATE_LEVEL, l_procedure_name, 'fund_category='||p_fv_extract_detail(l_index).fund_category);
926           trace(C_STATE_LEVEL, l_procedure_name, 'fund_expired_status='||p_fv_extract_detail(l_index).fund_expired_status);
927           trace(C_STATE_LEVEL, l_procedure_name, 'fund_time_frame='||p_fv_extract_detail(l_index).fund_time_frame);
928           trace(C_STATE_LEVEL, l_procedure_name, 'treasury_symbol_id='||p_fv_extract_detail(l_index).treasury_symbol_id);
929           trace(C_STATE_LEVEL, l_procedure_name, 'l_treasury_symbol='||l_treasury_symbol);
930           trace(C_STATE_LEVEL, l_procedure_name, 'event_type_code='||payment_rec.event_type_code);
931 
932           p_fv_extract_detail(l_index).fund_type := l_fund_type;
933           IF (payment_rec.invoice_type_lookup_code = 'PAYMENT REQUEST' AND
934               payment_rec.source = 'Receivables' AND
935               payment_rec.ref_application_id = 222 AND
936               payment_rec.ref_product_table = 'AR_RECEIVABLE_APPLICATIONS_ALL' AND
937               payment_rec.ref_key1 IS NOT NULL) THEN
938             trace(C_STATE_LEVEL, l_procedure_name, 'Processing Refund');
939             p_fv_extract_detail(l_index).acc_refund_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
940             p_fv_extract_detail(l_index).ent_refund_amount := payment_rec.amount;
941           ELSIF (payment_rec.invoice_type_lookup_code = 'PREPAYMENT') THEN
942             trace(C_STATE_LEVEL, l_procedure_name, 'Processing Prepayment');
943             p_fv_extract_detail(l_index).acc_paid_obl_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
944             p_fv_extract_detail(l_index).ent_paid_obl_amount := payment_rec.amount;
945             IF (payment_rec.po_distribution_id IS NULL) THEN
946               p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*NVL(payment_rec.invoice_dist_base_amount, payment_rec.invoice_dist_amount);
947               p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*payment_rec.invoice_dist_amount;
948               IF (p_fv_extract_detail(l_index).direct_or_reimb = 'D') THEN
949                 p_fv_extract_detail(l_index).acc_expended_approp_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
950                 p_fv_extract_detail(l_index).ent_expended_approp_amount := payment_rec.amount;
951               END IF;
952             ELSE
953               p_fv_extract_detail(l_index).acc_unpaid_obl_amount := -1*NVL(payment_rec.invoice_dist_base_amount, payment_rec.invoice_dist_amount);
954               p_fv_extract_detail(l_index).ent_unpaid_obl_amount := -1*payment_rec.invoice_dist_amount;
955             END IF;
956             p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := p_fv_extract_detail(l_index).acc_unanticipated_bud_amount-1*
957                                                                          (p_fv_extract_detail(l_index).acc_unpaid_obl_amount +
958                                                                           p_fv_extract_detail(l_index).acc_paid_obl_amount);
959             p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := p_fv_extract_detail(l_index).ent_unanticipated_bud_amount-1*
960                                                                          (p_fv_extract_detail(l_index).ent_unpaid_obl_amount +
961                                                                           p_fv_extract_detail(l_index).ent_paid_obl_amount);
962 
963           ELSIF (payment_rec.invoice_type_lookup_code = 'INTEREST' AND p_application_id = 200) THEN
964             trace(C_STATE_LEVEL, l_procedure_name, 'Processing Interest');
965             IF (p_fv_extract_detail(l_index).direct_or_reimb = 'D') THEN
966               p_fv_extract_detail(l_index).acc_expended_approp_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
967               p_fv_extract_detail(l_index).ent_expended_approp_amount := payment_rec.amount;
968             END IF;
969             p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*NVL(payment_rec.paid_base_amount, payment_rec.amount);
970             p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*payment_rec.amount;
971             IF ((l_bank_info.cash_clearing_natural_account IS NOT NULL) AND
972                 (l_bank_info.cash_bank_natural_account IS NOT NULL) AND
973                 (l_bank_info.cash_clearing_natural_account = l_bank_info.cash_bank_natural_account)) THEN
974               p_fv_extract_detail(l_index).acc_paid_exp_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
975               p_fv_extract_detail(l_index).ent_paid_exp_amount := payment_rec.amount;
976             ELSE
977               p_fv_extract_detail(l_index).acc_unpaid_exp_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
978               p_fv_extract_detail(l_index).ent_unpaid_exp_amount := payment_rec.amount;
979             END IF;
980           ELSE
981             p_fv_extract_detail(l_index).acc_unpaid_exp_amount := -1*NVL(payment_rec.invoice_dist_base_amount, payment_rec.invoice_dist_amount);
982             p_fv_extract_detail(l_index).ent_unpaid_exp_amount := -1*payment_rec.invoice_dist_amount;
983             IF (payment_rec.pay_dist_lookup_code = 'DISCOUNT') THEN
984               IF (p_fv_extract_detail(l_index).direct_or_reimb = 'D') THEN
985                 p_fv_extract_detail(l_index).acc_expended_approp_amount := -1*NVL(payment_rec.paid_base_amount, payment_rec.amount);
986                 p_fv_extract_detail(l_index).ent_expended_approp_amount := -1*payment_rec.amount;
987               END IF;
988               p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
989               p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := payment_rec.amount;
990             ELSE
991               p_fv_extract_detail(l_index).acc_paid_exp_amount := NVL(payment_rec.paid_base_amount, payment_rec.amount);
992               p_fv_extract_detail(l_index).ent_paid_exp_amount := payment_rec.amount;
993               p_fv_extract_detail(l_index).acc_unanticipated_bud_amount := -1*(p_fv_extract_detail(l_index).acc_unpaid_exp_amount +
994                                                                                p_fv_extract_detail(l_index).acc_paid_exp_amount);
995               p_fv_extract_detail(l_index).ent_unanticipated_bud_amount := -1*(p_fv_extract_detail(l_index).ent_unpaid_exp_amount +
996                                                                                p_fv_extract_detail(l_index).ent_paid_exp_amount);
997               IF (p_fv_extract_detail(l_index).acc_unanticipated_bud_amount IS NOT NULL) THEN
998                 IF (p_fv_extract_detail(l_index).direct_or_reimb = 'D') THEN
999                   p_fv_extract_detail(l_index).acc_expended_approp_amount := -1*p_fv_extract_detail(l_index).acc_unanticipated_bud_amount;
1000                   p_fv_extract_detail(l_index).ent_expended_approp_amount := -1*p_fv_extract_detail(l_index).ent_unanticipated_bud_amount;
1001                 END IF;
1002               END IF;
1003             END IF;
1004           END IF;
1005         END IF;
1006       END IF; --IF (l_process_row) THEN
1007 
1008       IF (p_error_code <> c_SUCCESS) THEN
1009         EXIT;
1010       END IF;
1011     END LOOP;
1012 
1013   EXCEPTION
1014     WHEN OTHERS THEN
1015       p_error_code := c_FAILURE;
1016       fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1017       fnd_message.set_token('MESSAGE' , 'System Error :'||SQLERRM);
1018       p_error_desc := fnd_message.get;
1019       stack_error (l_procedure_name, 'FINAL', p_error_desc);
1020       trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:FINAL:'||p_error_desc);
1021   END;
1022 
1023   PROCEDURE ap_extract
1024   (
1025     p_application_id               IN            NUMBER,
1026     p_accounting_mode              IN            VARCHAR2
1027   )
1028   IS
1029     l_debug_info                   VARCHAR2(240);
1030     l_procedure_name               VARCHAR2(100):='.ap_extract';
1031     l_error_code NUMBER;
1032     l_error_desc VARCHAR2(2000);
1033     l_gt_error_code NUMBER;
1034     l_fv_extract_detail fv_sla_utl_processing_pkg.fv_ref_detail;
1035 
1036   BEGIN
1037 
1038     l_procedure_name := g_path_name || l_procedure_name;
1039     l_error_code := c_SUCCESS;
1040     -------------------------------------------------------------------------
1041     l_debug_info := 'Begin of procedure '||l_procedure_name;
1042     trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
1043     -------------------------------------------------------------------------
1044 
1045     IF (p_application_id <> 200) THEN
1046       RETURN;
1047     END IF;
1048 
1049     IF (l_error_code = c_SUCCESS) THEN
1050       process_invoice
1051       (
1052         p_application_id    => p_application_id,
1053         p_fv_extract_detail => l_fv_extract_detail,
1054         p_error_code        => l_error_code,
1055         p_error_desc        => l_error_desc
1056       );
1057     END IF;
1058 
1059     IF (l_error_code = c_SUCCESS) THEN
1060       process_payments
1061       (
1062         p_application_id    => p_application_id,
1063         p_fv_extract_detail => l_fv_extract_detail,
1064         p_error_code        => l_error_code,
1065         p_error_desc        => l_error_desc
1066       );
1067     END IF;
1068 
1069     IF (l_error_code = C_SUCCESS) THEN
1070        FORALL l_index IN l_fv_extract_detail .first..l_fv_extract_detail.last
1071           INSERT INTO fv_extract_detail_gt VALUES l_fv_extract_detail(l_index);
1072     END IF;
1073 
1074     IF (l_error_code = C_SUCCESS) THEN
1075       trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.pya_processor');
1076       fv_sla_utl_processing_pkg.pya_processor
1077       (
1078         p_application_id => p_application_id,
1079         p_error_code     => l_error_code,
1080         p_error_desc     => l_error_desc
1081       );
1082     END IF;
1083 
1084     trace(C_STATE_LEVEL, l_procedure_name, 'Calling fv_sla_utl_processing_pkg.dump_gt_table');
1085     fv_sla_utl_processing_pkg.dump_gt_table
1086     (
1087       p_error_code        => l_gt_error_code,
1088       p_error_desc        => l_error_desc
1089     );
1090 
1091     IF (l_error_code <>  C_SUCCESS) OR (l_gt_error_code <> C_SUCCESS) THEN
1092        APP_EXCEPTION.RAISE_EXCEPTION;
1093     END IF;
1094 
1095     -------------------------------------------------------------------------
1096     l_debug_info := 'End of procedure'||l_procedure_name;
1097     trace(C_PROC_LEVEL, l_procedure_name, l_debug_info);
1098     -------------------------------------------------------------------------
1099 
1100   EXCEPTION
1101 
1102     WHEN OTHERS THEN
1103       l_debug_info := 'Error in Federal AP SLA processing ';
1104       trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
1105       FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
1106       FND_MESSAGE.SET_TOKEN('MESSAGE', 'Procedure :fv_sla_processing_pkg.ap_extract'|| CRLF||'Error     :'||SQLERRM);
1107       FND_MSG_PUB.ADD;
1108       APP_EXCEPTION.RAISE_EXCEPTION;
1109   END ap_extract;
1110 
1111   PROCEDURE extract
1112   (
1113     p_application_id               IN            NUMBER,
1114     p_accounting_mode              IN            VARCHAR2
1115   )
1116   IS
1117 
1118     l_debug_info                   VARCHAR2(240);
1119     l_procedure_name               VARCHAR2(100) :='.EXTRACT';
1120 
1121   BEGIN
1122 
1123     l_procedure_name := g_path_name || l_procedure_name;
1124     -------------------------------------------------------------------------
1125     l_debug_info := 'Begin of procedure '||l_procedure_name;
1126     trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
1127     -------------------------------------------------------------------------
1128     -------------------------------------------------------------------------
1129 
1130 
1131     IF (p_application_id = 200) THEN
1132         ap_extract(p_application_id, p_accounting_mode);
1133     ELSE
1134         RETURN;
1135     END IF;
1136 
1137     -------------------------------------------------------------------------
1138     l_debug_info := 'End of procedure '||l_procedure_name;
1139     trace(C_PROC_LEVEL, l_procedure_name, l_debug_info);
1140     -------------------------------------------------------------------------
1141 
1142   EXCEPTION
1143     WHEN OTHERS THEN
1144       l_debug_info := 'Error in Federal SLA Processing ' || SQLERRM;
1145       trace(C_STATE_LEVEL, l_procedure_name, l_debug_info);
1146       FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
1147       FND_MESSAGE.SET_TOKEN('MESSAGE' ,
1148           'Procedure :fv_sla_processing_pkg.extract'|| CRLF||
1149           'Error     :'||SQLERRM);
1150       FND_MSG_PUB.ADD;
1151       APP_EXCEPTION.RAISE_EXCEPTION;
1152 
1153   END extract;
1154 
1155   PROCEDURE preaccounting
1156   (
1157     p_application_id               IN            NUMBER,
1158     p_ledger_id                    IN            INTEGER,
1159     p_process_category             IN            VARCHAR2,
1160     p_end_date                     IN            DATE,
1161     p_accounting_mode              IN            VARCHAR2,
1162     p_valuation_method             IN            VARCHAR2,
1163     p_security_id_int_1            IN            INTEGER,
1164     p_security_id_int_2            IN            INTEGER,
1165     p_security_id_int_3            IN            INTEGER,
1166     p_security_id_char_1           IN            VARCHAR2,
1167     p_security_id_char_2           IN            VARCHAR2,
1168     p_security_id_char_3           IN            VARCHAR2,
1169     p_report_request_id            IN            INTEGER
1170   ) IS
1171   BEGIN
1172     NULL;
1173   END;
1174 
1175   PROCEDURE postprocessing
1176   (
1177     p_application_id               IN            NUMBER,
1178     p_accounting_mode              IN            VARCHAR2
1179   )
1180   IS
1181   BEGIN
1182     NULL;
1183   END;
1184 
1185 
1186   PROCEDURE postaccounting
1187   (
1188     p_application_id               IN            NUMBER,
1189     p_ledger_id                    IN            INTEGER,
1190     p_process_category             IN            VARCHAR2,
1191     p_end_date                     IN            DATE,
1192     p_accounting_mode              IN            VARCHAR2,
1193     p_valuation_method             IN            VARCHAR2,
1194     p_security_id_int_1            IN            INTEGER,
1195     p_security_id_int_2            IN            INTEGER,
1196     p_security_id_int_3            IN            INTEGER,
1197     p_security_id_char_1           IN            VARCHAR2,
1198     p_security_id_char_2           IN            VARCHAR2,
1199     p_security_id_char_3           IN            VARCHAR2,
1200     p_report_request_id            IN            INTEGER
1201   )
1202   IS
1203   BEGIN
1204     NULL;
1205   END;
1206 BEGIN
1207   init;
1208 END fv_sla_ap_processing_pkg;