1 PACKAGE BODY JAI_AP_IL_ORG_PKG AS
2 --$Header: jaiaporgb.pls 120.2.12010000.1 2008/07/29 10:16:06 appldev ship $
3
4 --+=======================================================================+
5 --| Copyright (c) 2007 Oracle Corporation
6 --| Redwood Shores, CA, USA
7 --| All rights reserved.
8 --+=======================================================================
9 --| FILENAME
10 --| jaiaporgb.pls
11 --|
12 --| DESCRIPTION
13 --|
14 --| This package contains the PL/SQL tables/procedures/functions
15 --| used by the APXINWKB.fmb form for calling India Localization form
16 --|
17 --|
18 --|
19 --|
20 --|
21 --| TYPE LIEST
22 --|
23 --|
24 --| PROCEDURE LIST
25 --|
26 --|
27 --|
28 --| HISTORY
29 --| 15-Feb-2008 Eric changed the function FUN_TDS_INVOICE for the bug#6787209
30 --|
31 --+======================================================================*/
32
33
34
35 --==========================================================================
36 -- FUNCTION NAME:
37 --
38 -- FUN_IL_ORG
39 -- Returns true if IL is installed at customer instance
40
41 --==========================================================================
42
43
44
45 FUNCTION FUN_IL_ORG (P_CURRENCY VARCHAR2) RETURN boolean IS
46 BEGIN
47 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done( p_Owner => 'JA',p_Event_Name => 'JAI_EXISTENCE_OF_TABLES' ) = TRUE ) AND P_CURRENCY ='INR' Then
48 Return True;
49 Else
50 Return False;
51 End if;
52 END FUN_IL_ORG;
53
54
55
56
57 --==========================================================================
58 -- FUNCTION NAME:
59 --
60 -- FUN_TDS_INVOICE
61 -- /*Returns true if invoice is a TDS invoice */ --bug#6787209
62 -- Returns false if invoice is a TDS invoice
63 --
64 -- HISTORY
65 -- 15-Feb-2008 Eric changed the function FUN_TDS_INVOICE for the bug#6787209
66
67 --==========================================================================
68 FUNCTION FUN_TDS_INVOICE( P_INVOICE_ID NUMBER) RETURN BOOLEAN IS
69 l_invoice_id number;
70 Begin
71 Begin
72 --deleted by eric for bug#6787209 on Feb 15,2008 begin
73 ------------------------------------------------------------------
74 /*
75 select 1 into l_invoice_id from dual
76 where not exists ( select invoice_id from jai_ap_tds_invoices where invoice_id = P_INVOICE_ID ); -- :inv_sum_folder.invoice_id
77 */
78 ------------------------------------------------------------------
79 --deleted by eric for bug#6787209 on Feb 15,2008 end
80
81 --added by eric for bug#6787209 on Feb 15,2008 begin
82 ------------------------------------------------------------------
83 select
84 0
85 into
86 l_invoice_id
87 from
88 ap_invoices_all
89 where source = 'INDIA TDS'
90 and invoice_num like '%TDS%'
91 and invoice_id = P_INVOICE_ID;
92 ------------------------------------------------------------------
93 --added by eric for bug#6787209 on Feb 15,2008 end
94
95 Exception
96 When no_data_found then
97 --l_invoice_id :=0;bug#6787209
98 l_invoice_id :=1;
99 End;
100 if l_invoice_id = 1 then
101 Return True;
102 else
103 Return False;
104 end if;
105 End;
106
107
108
109 --==========================================================================
110 -- FUNCTION NAME:
111 --
112 -- FUN_MISC_LINE
113 -- Returns true if invoice has MISC lines created by IL
114
115 --==========================================================================
116
117
118 FUNCTION FUN_MISC_LINE (P_INVOICE_ID NUMBER,P_LOOKUP_CODE VARCHAR2,P_LINE_NUMBER NUMBER ) RETURN BOOLEAN IS
119 v_misc_line number(10);
120 BEGIN
121 Begin
122 select 1 into v_misc_line from dual
123 where exists (select 1 from ap_invoice_lines ap, jai_ap_invoice_lines jap
124 where jap.invoice_id = P_INVOICE_ID --:INV_SUM_FOLDER.INVOICE_ID
125 and jap.line_type_lookup_code = P_LOOKUP_CODE --:LINE_SUM_FOLDER.LINE_TYPE_LOOKUP_CODE
126 and jap.invoice_line_number = P_LINE_NUMBER --:LINE_SUM_FOLDER.LINE_NUMBER
127 and ap.invoice_id = jap.invoice_id
128 and jap.line_type_lookup_code ='MISCELLANEOUS'
129 and jap.invoice_line_number = ap.line_number);
130 Exception
131 When no_data_found then
132 v_misc_line := 0;
133 End;
134 If v_misc_line = 1 then
135 Return True;
136 Else
137 Return False;
138 End If;
139 END FUN_MISC_LINE;
140
141
142
143 --==========================================================================
144 -- FUNCTION NAME:
145 --
146 -- FUN_MISC_PO
147 -- Returns true if invoice has MISC lines
148
149 --==========================================================================
150
151
152 FUNCTION FUN_MISC_PO (P_INVOICE_ID NUMBER) RETURN BOOLEAN IS
153 v_check number(10);
154 BEGIN
155 Begin
156 select 1 into v_check from dual
157 where exists ( select 1 from jai_ap_invoice_lines jp where jp.invoice_id = P_INVOICE_ID and line_type_lookup_code ='MISCELLANEOUS' );
158 Exception
159 When no_data_found then
160 v_check := 0;
161 End;
162 IF v_check = 1 then
163 Return True;
164 Else
165 Return False;
166 End If;
167 END FUN_MISC_PO;
168
169
170
171
172 --==========================================================================
173 -- FUNCTION NAME:
174 --
175 -- fun_tax_cat_id
176 -- Returns the tax category id for a supplier
177
178 --==========================================================================
179
180 FUNCTION fun_tax_cat_id ( p_supplier_id number ,
181 p_supplier_site_id number ,
182 p_invoice_id NUMBER ,
183 p_line_number NUMBER
184 )return number as
185 -- when user changes the tax_category_id
186 cursor get_trx_tax_ctg_id ( p_invoice_id NUMBER , p_line_number NUMBER) is
187 select tax_category_id from jai_ap_invoice_lines
188 where invoice_id =p_invoice_id
189 and invoice_line_number =p_line_number
190 and LINE_TYPE_LOOKUP_CODE = 'ITEM';
191
192 -- when the new invoice is created or supplier is changed
193 cursor get_setup_tax_category_id ( p_supplier_id number , p_supplier_site_id number) is
194 select tax_category_id from jai_cmn_vendor_sites where vendor_id =p_supplier_id
195 and vendor_site_id = p_supplier_site_id;
196
197
198 l_tax_cat_id number;
199
200 begin
201
202
203 open get_trx_tax_ctg_id ( p_invoice_id , p_line_number);
204 fetch get_trx_tax_ctg_id into l_tax_cat_id;
205 close get_trx_tax_ctg_id;
206
207 jai_cmn_utils_pkg.print_log('AP_STAND.log',' in JAI_AP_IL_ORG_PKG l_tax_cat_id :'||l_tax_cat_id);
208
209 if l_tax_cat_id is null then
210
211 open get_setup_tax_category_id ( p_supplier_id , p_supplier_site_id );
212 fetch get_setup_tax_category_id into l_tax_cat_id;
213 close get_setup_tax_category_id;
214
215 end if;
216
217
218 return l_tax_cat_id;
219
220 end fun_tax_cat_id;
221
222 END JAI_AP_IL_ORG_PKG;
[Home] [Help]