[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_INVOICE_CREATE
Source
1 PACKAGE BODY JL_ZZ_INVOICE_CREATE as
2 /* $Header: jlzzricb.pls 120.7 2007/10/24 10:29:39 hbalijep ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PUBLIC FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 /*----------------------------------------------------------------------------*
9 | FUNCTION |
10 | validate_gdf_inv_api |
11 | |
12 | DESCRIPTION |
13 | |
14 | PARAMETERS |
15 | INPUT |
16 | p_request_id Number -- Concurrent Request_id |
17 | |
18 | RETURNS |
19 | 0 Number -- Validation Fails, if there is any |
20 | exceptional case which is handled |
21 | in WHEN OTHERS |
22 | 1 Number -- Validation Succeeds |
23 | |
24 *----------------------------------------------------------------------------*/
25 -- PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
26 -- Bugfix# 3259701
27 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 t_interface_line_tbl R_interface_line;
30 l_org_id NUMBER;
31
32 ------------------------------------------------------------
33 ----- Tax validation function.
34 ----------------------------------------------------------
35
36 FUNCTION validate_gdf_inv_api (p_request_id IN NUMBER) RETURN NUMBER IS
37
38 return_code NUMBER (1);
39 l_tax_method VARCHAR2(30);
40 l_country_code VARCHAR2(2);
41 l_index binary_integer;
42
43
44 CURSOR trx_lines_cursor (c_request_id NUMBER) IS
45 SELECT ar_trx_lines_gt.trx_line_id
46 , ar_trx_header_gt.cust_trx_type_id trx_type
47 , ar_trx_header_gt.trx_date
48 , nvl(ship_to_address_id , bill_to_address_id)
49 ship_to_address_id
50 , ar_trx_lines_gt.line_type
51 , ar_trx_lines_gt.memo_line_id
52 , ar_trx_lines_gt.inventory_item_id
53 , ar_trx_header_gt.global_attribute1
54 , ar_trx_header_gt.global_attribute2
55 , ar_trx_header_gt.global_attribute3
56 , ar_trx_header_gt.global_attribute4
57 , ar_trx_header_gt.global_attribute5
58 , ar_trx_header_gt.global_attribute6
59 , ar_trx_header_gt.global_attribute7
60 , ar_trx_header_gt.global_attribute8
61 , ar_trx_header_gt.global_attribute9
62 , ar_trx_header_gt.global_attribute10
63 , ar_trx_header_gt.global_attribute11
64 , ar_trx_header_gt.global_attribute12
65 , ar_trx_header_gt.global_attribute13
66 , ar_trx_header_gt.global_attribute14
67 , ar_trx_header_gt.global_attribute15
68 , ar_trx_header_gt.global_attribute16
69 , ar_trx_header_gt.global_attribute17
70 , ar_trx_lines_gt.global_attribute1
71 , ar_trx_lines_gt.global_attribute4
72 , ar_trx_lines_gt.global_attribute5
73 , ar_trx_lines_gt.global_attribute6
74 , ar_trx_lines_gt.global_attribute7
75 , ar_trx_lines_gt.global_attribute8
76 , ar_trx_lines_gt.global_attribute9
77 , ar_trx_lines_gt.global_attribute10
78 , ar_trx_lines_gt.global_attribute11
79 , ar_trx_lines_gt.global_attribute12
80 , warehouse_id
81 , rbs.name
82 , ar_trx_header_gt.trx_number
83 FROM ar_trx_lines_gt,ar_trx_header_gt,ra_batch_sources_all rbs
84 WHERE ar_trx_lines_gt.request_id = c_request_id
85 AND ar_trx_header_gt.trx_header_id = ar_trx_lines_gt.trx_header_id
86 AND ar_trx_header_gt.batch_source_id = rbs.batch_source_id
87 ORDER BY ar_trx_header_gt.trx_date;
88
89 ------------------------------------------------------------
90 -- Main function body. --
91 ------------------------------------------------------------
92 BEGIN
93
94 ------------------------------------------------------------
95 -- Let's assume everything is OK --
96 ------------------------------------------------------------
97 --arp_standard.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()+');
98 IF PG_DEBUG = 'Y' THEN
99 arp_util_tax.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()+');
100 END IF;
101
102 return_code := 1;
103 --bug 5562805
104 --l_country_code := fnd_profile.value ('JGZZ_COUNTRY_CODE');
105 l_org_id := MO_GLOBAL.get_current_org_id;
106 l_country_code := JG_ZZ_SHARED_PKG.get_country(l_org_id,null,null);
107
108
109 --arp_standard.debug('-- Country Code: '||l_country_code);
110 --arp_standard.debug('-- Request Id: '||to_char(p_request_id));
111 IF PG_DEBUG = 'Y' THEN
112 arp_util_tax.debug('validate_gdff: ' || '-- Country Code: '||l_country_code);
113 arp_util_tax.debug('validate_gdff: ' || '-- Request Id: '||to_char(p_request_id));
114 END IF;
115
116 ------------------------------------------------------------
117 -- Validate all the rows for this concurrent request --
118 ------------------------------------------------------------
119 Open trx_lines_cursor(p_request_id);
120 --Bug 6486460
121 --LOOP
122
123 Fetch trx_lines_cursor BULK COLLECT INTO
124 t_interface_line_tbl.interface_line_id,
125 t_interface_line_tbl.cust_trx_type_id,
126 t_interface_line_tbl.trx_date,
127 t_interface_line_tbl.orig_system_address_id,
128 t_interface_line_tbl.line_type,
129 t_interface_line_tbl.memo_line_id,
130 t_interface_line_tbl.inventory_item_id,
131 t_interface_line_tbl.header_gdf_attribute1,
132 t_interface_line_tbl.header_gdf_attribute2,
133 t_interface_line_tbl.header_gdf_attribute3,
134 t_interface_line_tbl.header_gdf_attribute4,
135 t_interface_line_tbl.header_gdf_attribute5,
136 t_interface_line_tbl.header_gdf_attribute6,
137 t_interface_line_tbl.header_gdf_attribute7,
138 t_interface_line_tbl.header_gdf_attribute8,
139 t_interface_line_tbl.header_gdf_attribute9,
140 t_interface_line_tbl.header_gdf_attribute10,
141 t_interface_line_tbl.header_gdf_attribute11,
142 t_interface_line_tbl.header_gdf_attribute12,
143 t_interface_line_tbl.header_gdf_attribute13,
144 t_interface_line_tbl.header_gdf_attribute14,
145 t_interface_line_tbl.header_gdf_attribute15,
146 t_interface_line_tbl.header_gdf_attribute16,
147 t_interface_line_tbl.header_gdf_attribute17,
148 t_interface_line_tbl.line_gdf_attribute1,
149 t_interface_line_tbl.line_gdf_attribute4,
150 t_interface_line_tbl.line_gdf_attribute5,
151 t_interface_line_tbl.line_gdf_attribute6,
152 t_interface_line_tbl.line_gdf_attribute7,
153 t_interface_line_tbl.line_gdf_attribute8,
154 t_interface_line_tbl.line_gdf_attribute9,
155 t_interface_line_tbl.line_gdf_attribute10,
156 t_interface_line_tbl.line_gdf_attribute11,
157 t_interface_line_tbl.line_gdf_attribute12,
158 t_interface_line_tbl.warehouse_id,
159 t_interface_line_tbl.batch_source_name,
160 t_interface_line_tbl.trx_number
161 ;
162
163 --END LOOP;
164 CLOSE trx_lines_cursor;
165
166
167 FOR l_index IN 1..t_interface_line_tbl.warehouse_id.LAST
168 LOOP
169
170 IF l_country_code IN ('BR','AR','CO') THEN
171
172 l_org_id := mo_global.get_current_org_id;
173
174 l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
175
176 --arp_standard.debug('-- Tax Method: '||l_tax_method);
177 IF PG_DEBUG = 'Y' THEN
178 arp_util_tax.debug('validate_gdff: ' || '-- Tax Method: '||l_tax_method);
179 END IF;
180
181 IF l_tax_method = 'LTE' THEN
182 IF NOT JL_ZZ_AUTO_INVOICE.validate_tax_attributes (t_interface_line_tbl.interface_line_id(l_index)
183 , t_interface_line_tbl.line_type(l_index)
184 , t_interface_line_tbl.memo_line_id(l_index)
185 , t_interface_line_tbl.inventory_item_id(l_index)
186 , t_interface_line_tbl.line_gdf_attribute2(l_index)
187 , t_interface_line_tbl.line_gdf_attribute3(l_index)
188 , NULL
189 , t_interface_line_tbl.line_gdf_attribute11(l_index)
190 , t_interface_line_tbl.line_gdf_attribute12(l_index)
191 , t_interface_line_tbl.orig_system_address_id(l_index)
192 , t_interface_line_tbl.warehouse_id(l_index)) THEN
193
194 --arp_standard.debug('-- validate_tax_attributes routine failed');
195 IF PG_DEBUG = 'Y' THEN
196 arp_util_tax.debug('validate_gdff: ' || '-- validate_tax_attributes routine failed');
197 END IF;
198 return_code := 0;
199 END IF; -- Validate tax
200 END IF; -- l_tax_method check
201 END IF; -- Tax method check
202
203 IF l_country_code = 'BR' THEN
204 IF NOT JL_ZZ_AUTO_INVOICE.validate_interest_attributes
205 ( t_interface_line_tbl.interface_line_id(l_index)
206 , t_interface_line_tbl.line_type(l_index)
207 , t_interface_line_tbl.header_gdf_attribute1(l_index)
208 , t_interface_line_tbl.header_gdf_attribute2(l_index)
209 , t_interface_line_tbl.header_gdf_attribute3(l_index)
210 , t_interface_line_tbl.header_gdf_attribute4(l_index)
211 , t_interface_line_tbl.header_gdf_attribute5(l_index)
212 , t_interface_line_tbl.header_gdf_attribute6(l_index)
213 , t_interface_line_tbl.header_gdf_attribute7(l_index)) THEN
214
215 --arp_standard.debug('-- validate_interest_attributes routine failed');
216 IF PG_DEBUG = 'Y' THEN
217 arp_util_tax.debug('validate_gdff: ' || '-- validate_interest_attributes routine failed');
218 END IF;
219
220 return_code := 0;
221 END IF; -- Validate interest
222
223 IF NOT JL_ZZ_AUTO_INVOICE.validate_billing_attributes (t_interface_line_tbl.interface_line_id(l_index)
224 , t_interface_line_tbl.line_type(l_index)
225 , t_interface_line_tbl.memo_line_id(l_index)
226 , t_interface_line_tbl.inventory_item_id(l_index)
227 , t_interface_line_tbl.header_gdf_attribute9(l_index)
228 , t_interface_line_tbl.header_gdf_attribute10(l_index)
229 , t_interface_line_tbl.header_gdf_attribute11(l_index)
230 , t_interface_line_tbl.header_gdf_attribute13(l_index)
231 , t_interface_line_tbl.header_gdf_attribute15(l_index)
232 , t_interface_line_tbl.header_gdf_attribute16(l_index)
233 , t_interface_line_tbl.header_gdf_attribute17(l_index)
234 , t_interface_line_tbl.line_gdf_attribute1(l_index)
235 , t_interface_line_tbl.line_gdf_attribute4(l_index)
236 , t_interface_line_tbl.line_gdf_attribute5(l_index)
237 , t_interface_line_tbl.line_gdf_attribute6(l_index)
238 , t_interface_line_tbl.line_gdf_attribute7(l_index)) THEN
239
240 --arp_standard.debug('-- validate_billing_attributes routine failed');
241 IF PG_DEBUG = 'Y' THEN
242 arp_util_tax.debug('validate_gdff: ' || '-- validate_billing_attributes routine failed');
243 END IF;
244
245 return_code := 0;
246 END IF; -- Validate billing
247
248 ELSIF l_country_code = 'AR' THEN
249 ------------------------------------------------------------
250 -- Validate all the rows for this concurrent request --
251 ------------------------------------------------------------
252 IF NOT JL_AR_DOC_NUMBERING_PKG.validate_interface_lines
253 ( p_request_id
254 , t_interface_line_tbl.interface_line_id(l_index)
255 , t_interface_line_tbl.cust_trx_type_id(l_index)
256 , t_interface_line_tbl.inventory_item_id(l_index)
257 , t_interface_line_tbl.memo_line_id(l_index)
258 , t_interface_line_tbl.trx_date(l_index)
259 , t_interface_line_tbl.orig_system_address_id(l_index)
260 , t_interface_line_tbl.warehouse_id(l_index)
261 ) THEN
262
263 --arp_standard.debug('-- JL_AR_DOC_NUMBERING_PKG.'||'validate_interface_lines routine failed');
264 IF PG_DEBUG = 'Y' THEN
265 arp_util_tax.debug('validate_gdff: ' || '-- JL_AR_DOC_NUMBERING_PKG.'||'validate_interface_lines routine failed');
266 END IF;
267
268 return_code := 0;
269 END IF; -- Validate interface lines
270 END IF;
271
272 END LOOP;
273
274 --arp_standard.debug('-- Return Code: '||to_char(return_code));
275 --arp_standard.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()-');
276 IF PG_DEBUG = 'Y' THEN
277 arp_util_tax.debug('validate_gdff: ' || '-- Return Code: '||to_char(return_code));
278 arp_util_tax.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()-');
279 END IF;
280
281 RETURN return_code;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285
286 --arp_standard.debug('-- Return From Exception when others');
287 --arp_standard.debug('-- Return Code: 0');
288 --arp_standard.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()-');
289 IF PG_DEBUG = 'Y' THEN
290 arp_util_tax.debug('validate_gdff: ' || '-- Return From Exception when others');
291 arp_util_tax.debug('validate_gdff: ' || '-- Return Code: 0');
292 arp_util_tax.debug('JL_ZZ_INVOICE_CREATE.validate_gdff()-');
293 END IF;
294
295 RETURN 0;
296
297 END validate_gdf_inv_api;
298
299 END JL_ZZ_INVOICE_CREATE;