[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_OI_PROCESSING_PKG
Source
1 package body JAI_AP_OI_PROCESSING_PKG as
2 /* $Header: jai_ap_oi_prc.plb 120.0.12020000.3 2013/05/09 06:38:54 cholei noship $ */
3
4 function auto_approval_inv
5 (
6 p_invoice_id in number
7 )return varchar2
8 is
9 cursor c_ap_invoices_all(p_invoice_id number) is
10 select vendor_id,
11 vendor_site_id,
12 org_id
13 from ap_invoices_all
14 where invoice_id = p_invoice_id;
15
16 cursor c_ja_in_po_vendor_sites(p_vendor_id number, p_vendor_site_id number) is
17 select nvl( approved_tds_flag, 'N' ) approved_invoice_flag
18 from JAI_CMN_VENDOR_SITES
19 where vendor_id = p_vendor_id
20 and vendor_site_id = p_vendor_site_id;
21
22 r_ap_invoices_all c_ap_invoices_all%rowtype;
23 lv_approved_invoice_flag JAI_CMN_VENDOR_SITES.approved_tds_flag%type;
24 begin
25
26 open c_ap_invoices_all(p_invoice_id);
27 fetch c_ap_invoices_all into r_ap_invoices_all;
28 close c_ap_invoices_all;
29
30 /* Check for vendor and site */
31 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, r_ap_invoices_all.vendor_site_id);
32 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
33 close c_ja_in_po_vendor_sites;
34
35
36 if nvl(lv_approved_invoice_flag, 'N') <> 'Y' then
37 /* Pre-approved TDS invoice is not set for vendor and site, Check for vendor and null site */
38 open c_ja_in_po_vendor_sites(r_ap_invoices_all.vendor_id, 0);
39 fetch c_ja_in_po_vendor_sites into lv_approved_invoice_flag;
40 close c_ja_in_po_vendor_sites;
41 end if;
42
43 return nvl(lv_approved_invoice_flag, 'N');
44
45 end auto_approval_inv;
46
47 PROCEDURE pre_data_for_ap_line
48 (
49 pn_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE,
50 pn_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE,
51 pv_return_code OUT NOCOPY VARCHAR2,
52 pv_return_message OUT NOCOPY VARCHAR2
53 ) IS
54
55 CURSOR c_inv_lines( cp_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE, cp_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE ) IS
56 select *
57 from ap_invoice_lines_all
58 where invoice_id = cp_invoice_id
59 and line_number = cp_line_number;
60
61 CURSOR c_invs( cp_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE) IS
62 Select *
63 from ap_invoices_all
64 where invoice_id = cp_invoice_id;
65
66 Cursor c_match_tax
67 ( cp_match_type VARCHAR2,
68 cp_trx_id VARCHAR2,
69 cp_vendor_id NUMBER) IS
70 select *
71 from JAI_PO_RCV_TAXES_V jpr
72 where jpr.match_type = cp_match_type
73 and jpr.trx_id = cp_trx_id
74 and jpr.vendor_id = cp_vendor_id;
75
76 cursor c_tax_info(cp_tax_id number) is
77 select tax_name, mod_cr_percentage, rounding_factor
78 from JAI_CMN_TAXES_ALL
79 where tax_id = cp_tax_id;
80
81
82 cursor c_rcv_tax_line(cp_tax_id number, cp_trx_id number) is
83
84 select lt.tax_rate, lt.uom, lt.qty_rate
85 from JAI_RCV_LINE_TAXES lt, RCV_TRANSACTIONS rt
86 where tax_id = cp_tax_id
87 and lt.shipment_line_id = rt.shipment_line_id
88 and rt.transaction_id = cp_trx_id;
89
90 cursor c_po_tax_line(cp_po_header_id number, cp_po_line_id number, cp_line_loc_id number, cp_tax_id number) is
91 select tax_rate, uom, qty_rate
92 from JAI_PO_TAXES
93 where line_location_id = cp_line_loc_id
94 and po_header_id = cp_po_header_id
95 and po_line_id = cp_po_line_id
96 and tax_id = cp_tax_id;
97
98 ln_invoice_id AP_INVOICE_LINES_ALL.INVOICE_ID%TYPE;
99 ln_line_number AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
100 lv_match_type VARCHAR2(20);
101 lv_trx_id VARCHAR2(30);
102 lv_receipt_code VARCHAR2(20);
103 ln_shipment_line_id NUMBER;
104 ln_shipment_header_id NUMBER;
105 lv_shipment_num rcv_headers_interface.shipment_num%type;
106 lb_result BOOLEAN := FALSE ;
107 invs_rec ap_invoices_all%rowtype;
108 inv_lines_rec ap_invoice_lines_all%rowtype;
109 ln_vendor_id NUMBER;
110 ln_index number := 1;
111 l_tax_line_tbl jai_ap_match_tax_proc_pkg.l_tax_line_tbl_type;
112 ln_vendor_id NUMBER;
113 ln_rounding_factor NUMBER;
114 ln_apported_tax_amount NUMBER;
115 ln_mod_cr_percentage NUMBER;
116 lv_tax_name JAI_CMN_TAXES_ALL.tax_name%TYPE;
117 BEGIN
118 ln_invoice_id := pn_invoice_id;
119 ln_line_number := pn_line_number;
120
121 OPEN c_invs(ln_invoice_id);
122 Fetch c_invs into invs_rec;
123 Close c_invs;
124
125 OPEN c_inv_lines(ln_invoice_id,ln_line_number);
126 Fetch c_inv_lines into inv_lines_rec;
127 Close c_inv_lines;
128 jai_ap_match_tax_proc_pkg.get_match_type(
129 pn_invoice_id => inv_lines_rec.invoice_id
130 ,pn_invoice_line_num => inv_lines_rec.line_number
131 ,pv_match_type => lv_match_type
132 ,pv_receipt_code => lv_receipt_code
133 ,pn_shipment_line_id => ln_shipment_line_id
134 ,pn_shipment_header_id => ln_shipment_header_id
135 );
136
137
138
139 IF lv_match_type = 'PO_MATCHING' THEN
140 lv_trx_id := inv_lines_rec.po_line_location_id;
141 --lv_tax_def_where :=' match_type= '''||:JAI_AP_INV_LINES.match_type ||''' AND trx_id =''' ||:JAI_AP_INV_LINES.trx_id||'''';
142 ELSIF lv_match_type = 'RCV_MATCHING' THEN
143 lv_trx_id := inv_lines_rec.rcv_transaction_id;
144 ELSIF lv_match_type = 'PAY_ON_RECEIPT' AND inv_lines_rec.rcv_transaction_id IS NOT NULL THEN
145 lv_trx_id := inv_lines_rec.po_line_location_id;
146 ELSIF lv_match_type = 'PAY_ON_RECEIPT' AND inv_lines_rec.rcv_transaction_id IS NULL THEN
147 IF invs_rec.source <> 'ASBN' THEN
148 lv_trx_id := inv_lines_rec.po_line_location_id ;
149 ELSE
150 Select shipment_num
151 INTO lv_shipment_num
152 From rcv_headers_interface
153 Where invoice_num =invs_rec.invoice_num;
154 lv_trx_id := inv_lines_rec.po_line_location_id ||'_'|| lv_shipment_num;
155
156 END IF;
157 END IF;
158
159 l_tax_line_tbl.Delete();
160 ln_index := 1;
161 for match_tax_rec in c_match_tax(lv_match_type,lv_trx_id,invs_rec.vendor_id) loop
162
163 open c_tax_info(match_tax_rec.tax_id);
164 fetch c_tax_info
165 into lv_tax_name, ln_mod_cr_percentage,ln_rounding_factor;
166 close c_tax_info;
167
168
169
170 ln_apported_tax_amount := jai_ap_match_tax_proc_pkg.tax_apportion_convert(
171 pn_invoice_id => pn_invoice_id
172 ,pn_invoice_line_num => pn_line_number
173 ,pv_adhoc_flag => match_tax_rec.adhoc_flag
174 ,pv_vat_flag => match_tax_rec.vat_flag
175 ,pv_tax_type => match_tax_rec.tax_type
176 ,pn_tax_id => match_tax_rec.tax_id
177 ,pn_tax_amount => match_tax_rec.tax_amount
178 );
179 if invs_rec.invoice_currency_code <> match_tax_rec.currency then
180 ln_apported_tax_amount := ln_apported_tax_amount/invs_rec.exchange_rate;
181 end if;
182
183 ln_index := c_match_tax%rowcount;
184 fnd_file.put_line(fnd_file.log,'In JAI_AP_TAX_PROCESSING.OPEN_INTERFACEIN at LOOP c_match_tax ln_index=' || to_char(ln_index));
185 l_tax_line_tbl(ln_index).tax_id := match_tax_rec.tax_id;
186 l_tax_line_tbl(ln_index).tax_line_no := match_tax_rec.TAX_LINE_NO;
187 l_tax_line_tbl(ln_index).rounding_factor := ln_rounding_factor;
188 l_tax_line_tbl(ln_index).tax_type := match_tax_rec.tax_type;
189 l_tax_line_tbl(ln_index).tax_amount := ln_apported_tax_amount;
190 l_tax_line_tbl(ln_index).modvat_flag := match_tax_rec.modvat_flag;
191 l_tax_line_tbl(ln_index).mod_cr_percentage := ln_mod_cr_percentage;
192 l_tax_line_tbl(ln_index).inc_tax_flag := match_tax_rec.INC_TAX_FLAG;
193 l_tax_line_tbl(ln_index).reverse_charge_flag := match_tax_rec.REVERSE_CHARGE_FLAG;
194
195 end loop;
196 jai_ap_match_tax_proc_pkg.copy_tax_from_po_rcv(pn_invoice_id => inv_lines_rec.INVOICE_ID,
197 pn_invoice_line_num => inv_lines_rec.LINE_NUMBER,
198 p_tax_line_tbl => l_tax_line_tbl,
199 pv_rematch => lv_match_type,
200 pn_shipment_hdr_id => ln_shipment_header_id,
201 pn_shipment_line_id => ln_shipment_line_id);
202 pv_return_code := jai_constants.successful;
203 EXCEPTION
204 WHEN OTHERS THEN
205 Pv_return_code := jai_constants.unexpected_error;
206 Pv_return_message := 'Encountered an error in JAI_AP_OI_PROCESSING_PKG.pre_data_for_ap_line ' ||
207 'SQLERRM : ' || substr(sqlerrm,1,1800);
208 fnd_file.put_line(fnd_file.log,Pv_return_message);
209
210 END pre_data_for_ap_line;
211
212
213 PROCEDURE PROCESS_INVOICE
214 (
215 pv_action VARCHAR2,
216 pr_rec AP_INVOICES_ALL%ROWTYPE,
217 pv_return_code OUT NOCOPY VARCHAR2,
218 pv_return_message OUT NOCOPY VARCHAR2
219 ) IS
220
221 lv_process_flag varchar2(20);
222 lv_process_message varchar2(2000);
223 lv_release_name VARCHAR2(30);
224 lv_other_release_info VARCHAR2(30);
225 lb_result BOOLEAN := FALSE ;
226 BEGIN
227
228 /* Update invoice Ids in TDS tables for TDS invoices */
229 if pv_action = jai_constants.inserting and pr_rec.source = 'INDIA TDS' then /* 'TDS' then --Ramanand for bug#4388958 */
230
231 --jai_ap_tds_generation_pkg.populate_tds_invoice_id
232 jai_ap_dtc_generation_pkg.populate_tds_invoice_id --Update to call new DTC generation package. bug#16778906 2013/05/09
233 (
234 p_invoice_id => pr_rec.invoice_id,
235 p_invoice_num => pr_rec.invoice_num,
236 p_vendor_id => pr_rec.vendor_id,
237 p_vendor_site_id => pr_rec.vendor_site_id,
238 p_process_flag => lv_process_flag,
239 p_process_message => lv_process_message
240 );
241
242 if nvl(lv_process_flag, 'N') = 'E' then
243 pv_return_code := jai_constants.expected_error ;
244 pv_return_message := 'Error - Encountered an error in JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE : ' || lv_process_message ;
245 fnd_file.put_line(fnd_file.log,Pv_return_message);
246 RETURN;
247 end if;
248
249 end if; /* Update invoice Ids in TDS tables for TDS invoices */
250
251 /* Update invoice Ids in Third Party tables for Third Party invoices */
252 if pv_action = jai_constants.inserting and pr_rec.source = 'INDIA TAX INVOICE' then /* 'RECEIPT' then --Ramanand for bug#4388958 */
253
254 jai_rcv_third_party_pkg.populate_tp_invoice_id
255 (
256 p_invoice_id => pr_rec.invoice_id,
257 p_invoice_num => pr_rec.invoice_num,
258 p_vendor_id => pr_rec.vendor_id,
259 p_vendor_site_id => pr_rec.vendor_site_id,
260 p_process_flag => lv_process_flag,
261 p_process_message => lv_process_message
262 );
263 if nvl(lv_process_flag, 'N') = jai_constants.unexpected_error then
264 pv_return_code := jai_constants.expected_error;
265 pv_return_message :='Error - Encountered an error in JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE : ' || lv_process_message ;
266 fnd_file.put_line(fnd_file.log,Pv_return_message);
267 return ;
268 end if;
269 end if;
270
271 lb_result := fnd_release.get_release(lv_release_name, lv_other_release_info);
272
273
274 IF ( pr_rec.source = 'INDIA TDS') THEN
275 IF pr_rec.attribute1 IS NOT NULL THEN
276 if ( auto_approval_inv(pr_rec.attribute1) = 'Y') then
277 update AP_INVOICES_ALL
278 SET WFAPPROVAL_STATUS = 'NOT REQUIRED'
279 WHERE INVOICE_ID = pr_rec.invoice_id;
280 end if;
281 END IF;
282 END IF;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 Pv_return_code := jai_constants.unexpected_error;
287 Pv_return_message := 'Encountered an error in JAI_AP_OI_PROCESSING_PKG.PROCESS_INVOICE ' ||
288 'SQLERRM : ' || substr(sqlerrm,1,1800);
289 fnd_file.put_line(fnd_file.log,Pv_return_message);
290 END PROCESS_INVOICE;
291 end JAI_AP_OI_PROCESSING_PKG;