1 PACKAGE BODY JG_ZZ_AERL_DT_PKG AS
2 /*$Header: jgzzaerlb.pls 120.0.12000000.4 2007/07/11 12:17:08 ashdas ship $*/
3
4 function cf_batch_name(trx_id in number, source in varchar2) return varchar2 as
5 /* This function will fetch batch name depending upon transaction source */
6 cursor c_get_ar_batch_name
7 is
8 select rab.name
9 from ra_batches rab
10 ,ra_customer_trx ract
11 where rab.batch_id = ract.batch_id
12 and ract.customer_trx_id = trx_id;
13
14 cursor c_get_ap_batch_name
15 is
16 select apb.batch_name
17 from ap_batches apb
18 ,ap_invoices api
19 where apb.batch_id = api.batch_id
20 and api.invoice_id = trx_id;
21
22 cursor c_get_gl_batch_name
23 is
24 select gjb.name
25 from gl_je_batches gjb
26 ,gl_je_headers gljh
27 where gjb.je_batch_id = gljh.je_batch_id
28 and gljh.je_header_id = trx_id;
29
30 lv_batch_name varchar2 (240);
31
32 begin
33
34 if source = 'AR' then
35 open c_get_ar_batch_name;
36 fetch c_get_ar_batch_name into lv_batch_name;
37 close c_get_ar_batch_name;
38 elsif source = 'AP' then
39 open c_get_ap_batch_name;
40 fetch c_get_ap_batch_name into lv_batch_name;
41 close c_get_ap_batch_name;
42 elsif source = 'GL' then
43 open c_get_gl_batch_name;
44 fetch c_get_gl_batch_name into lv_batch_name;
45 close c_get_gl_batch_name;
46 end if;
47 return (lv_batch_name);
48
49 end cf_batch_name;
50 function get_financial_document_type(pn_trx_id in number,pn_trx_type_id in number, pv_source in varchar2 , pv_entity_code in varchar2) return varchar2 as
51 /* This function will fetch Financial Document type to solve the bug 5550600*/
52 lv_fin_doc_type varchar2(20);
53 lv_sl_trx_type ce_statement_lines.trx_type%type;
54 lv_cr_reversal_category ar_cash_receipts_all.reversal_category%type;
55 begin
56 fnd_file.put_line(fnd_file.log, '**Input Params:** ');
57 fnd_file.put_line(fnd_file.log, '1. Trx_id: '||pn_trx_id);
58 fnd_file.put_line(fnd_file.log, '2. Trx_type_id: '||pn_trx_type_id);
59 fnd_file.put_line(fnd_file.log, '3. Source: '||pv_source);
60 fnd_file.put_line(fnd_file.log, '4. Entity Code '||pv_entity_code);
61 begin
62 if pv_source = 'AP' then
63 select invoice_type_lookup_code
64 into lv_fin_doc_type
65 from ap_invoices_all
66 where invoice_id = pn_trx_id;
67 elsif pv_source = 'AR' then
68 if pv_entity_code = 'RECEIPTS' then
69 select sl.trx_type into lv_sl_trx_type
70 from ar_cash_receipt_history_all crh, ce_statement_reconcils_all sr,ce_statement_lines sl
71 where crh.cash_receipt_id = pn_trx_id
72 and crh.cash_receipt_history_id = sr.reference_id
73 and sr.statement_line_id = sl.statement_line_id;
74 select cr.reversal_category into lv_cr_reversal_category
75 from ar_cash_receipts_all cr
76 where cr.cash_receipt_id = pn_trx_id
77 and cr.type = 'MISC';
78 if lv_sl_trx_type in ('CREDIT','MISC_CREDIT') then
79 if lv_cr_reversal_category = 'REV' then
80 lv_fin_doc_type := 'MISCREVR';
81 elsif lv_cr_reversal_category is null then
82 lv_fin_doc_type := 'MISCREC';
83 end if;
84 elsif lv_sl_trx_type in ('DEBIT','MISC_DEBIT') then
85 if lv_cr_reversal_category = 'REV' then
86 lv_fin_doc_type := 'MISCREVP';
87 elsif lv_cr_reversal_category is null then
88 lv_fin_doc_type := 'MISCPAY';
89 end if;
90 end if;
91 elsif pv_entity_code = 'TRANSACTIONS' then
92 select type
93 into lv_fin_doc_type
94 from ra_cust_trx_types --Modified the query for bug 6156524 Used ra_cust_trx_types table instead of ra_cust_trx_types_all table.
95 where cust_trx_type_id = pn_trx_type_id;
96 end if;
97 elsif pv_source = 'GL' then
98 lv_fin_doc_type := 'N/A';
99 end if;
100 exception
101 when others then
102 -- Don't Error Out (Possible excp are Too_many_rows or No_data_Found)
103 lv_fin_doc_type := null;
104 fnd_file.put_line(fnd_file.log,'Logging the Error Message encountered: '||sqlerrm);
105 end;
106 fnd_file.put_line(fnd_file.log,'Document Type Returned: '||lv_fin_doc_type);
107 return (lv_fin_doc_type);
108 end get_financial_document_type;
109 END JG_ZZ_AERL_DT_PKG ;
[Home] [Help]