[Home] [Help]
PACKAGE BODY: APPS.JE_ES_MODELO_EXT_PKG
Source
1 PACKAGE BODY je_es_modelo_ext_pkg
2 -- $Header: jeesmodeloextb.pls 120.18.12010000.11 2009/01/05 11:52:39 vkejriwa ship $
3 AS
4
5 G_LE_TRN VARCHAR2(150);
6 G_LE_NAME VARCHAR2(150);
7 G_CURRENCY_CODE VARCHAR2(150);
8 G_CUR_PRECISION NUMBER;
9 G_FROM_DATE DATE;
10 G_TO_DATE DATE;
11
12 G_DEBUG BOOLEAN := TRUE;
13 G_LINENO VARCHAR2 (20) ; -- DEBUG LINE NO
14
15 PROCEDURE get_vendor_address ( p_party_site_id IN NUMBER
16 , x_postal_code OUT NOCOPY VARCHAR2
17 , x_city OUT NOCOPY VARCHAR2
18 , x_address_detail OUT NOCOPY VARCHAR2
19 )
20 IS
21
22 /** author: brathod
23 Modified following cursor to refer ap_supplier_sites_all to retrieve supplier address.
24 Cursor was wrongly refering to hz_party_sites_all and hz_locations as
25 jg_zz_vat_trx_details.billing_tp_address_id (value passed in parameter p_party_site_id) is actually a
26 supplier_site_id and not party_site_id. Please refer bug#5031773
27 */
28 CURSOR c_get_address IS
29 SELECT DECODE(p_modelo,'415',DECODE(SUBSTR(assa.country, 1, 25)
30 , 'ES',SUBSTR(assa.zip,1,5)
31 , '99'||FT.eu_code)
32 , DECODE(assa.country
33 , 'ES',SUBSTR(assa.zip,1,2)||' '
34 , '99'||FT.territory_code||' ') ) POSTCODE
35 , assa.city CITY
36 , SUBSTR(assa.address_line1,1,35)||' '||
37 SUBSTR(assa.address_line2,1,35)||' '||
38 SUBSTR(assa.address_line3,1,35) ADDRESS_DETAIL
39 FROM ap_supplier_sites_all assa
40 , fnd_territories FT
41 WHERE assa.vendor_site_id = p_party_site_id
42 AND assa.country(+) = FT.territory_code;
43
44 BEGIN
45 OPEN c_get_address ;
46 FETCH c_get_address INTO x_postal_code, x_city, x_address_detail ;
47 CLOSE c_get_address ;
48 END;
49
50
51 PROCEDURE get_customer_address ( p_customer_address_id IN NUMBER
52 , x_postal_code OUT NOCOPY VARCHAR2
53 , x_city OUT NOCOPY VARCHAR2
54 , x_address_detail OUT NOCOPY VARCHAR2
55 )
56 IS
57
58 CURSOR c_get_address IS
59 SELECT DECODE(HL.country
60 , 'ES', SUBSTR(HL.postal_code,1,2)||'000'
61 , '99'||FT.eu_code) postal_code
62 , SUBSTR(HL.town_or_city,1,24) city
63 , SUBSTR(HL.address_line_1,1,2) ||
64 SUBSTR(HL.address_line_2,1,23) ||
65 LPAD(SUBSTR(HL.address_line_3,1,length(HL.address_line_3) -
66 NVL(LENGTH(LTRIM(TRANSLATE(HL.address_line_3, '123456789','000000000'),'0')),0)),5,'0') address_detail
67 FROM hz_cust_acct_sites_all HCAS
68 , hz_party_sites HPS
69 , hr_locations HL
70 , fnd_territories FT
71 , hz_cust_site_uses_all HCSU
72 WHERE HCAS.cust_acct_site_id = p_customer_address_id
73 AND HPS.party_site_id = HCAS.party_site_id
74 AND HL.location_id = HPS.location_id
75 AND HL.country(+) = FT.territory_code
76 AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
77 AND UPPER(HCSU.site_use_code) = 'LEGAL'
78 AND HCAS.bill_to_flag IN ('P','Y')
79 AND HCAS.status = 'A'
80 AND HCSU.primary_flag = 'Y' ;
81
82 BEGIN
83 OPEN c_get_address ;
84 FETCH c_get_address INTO x_postal_code, x_city, x_address_detail ;
85 CLOSE c_get_address ;
86 END;
87 --
88 --
89 PROCEDURE get_customer_address2 ( p_customer_address_id IN NUMBER
90 , x_postal OUT NOCOPY VARCHAR2
91 , x_city OUT NOCOPY VARCHAR2
92 , x_street_type OUT NOCOPY VARCHAR2
93 , x_street OUT NOCOPY VARCHAR2
94 , x_number OUT NOCOPY VARCHAR2
95 )
96 IS
97
98 CURSOR c_get_address IS
99 SELECT DECODE(HL.country
100 , 'ES', SUBSTR(HL.postal_code,1,2)||' '
101 , '99'||FT.territory_code||' ') codigo_postal
102 , SUBSTR(HL.city,1,24) ref_catastral
103 , SUBSTR(HL.address1,1,2) sigla
104 , SUBSTR(HL.address2,1,25) via_publica
105 , SUBSTR(HL.address3||'Z',1,INSTR(HL.address3||'Z',
106 LTRIM(HL.address3||'Z','1234567890')) - 1) numero FROM hz_cust_acct_sites_all HCAS
107 , hz_party_sites HPS
108 , hz_locations HL
109 , fnd_territories FT
110 , hz_cust_site_uses_all HCSU
111 WHERE HCAS.cust_acct_site_id = p_customer_address_id
112 AND HPS.party_site_id = HCAS.party_site_id
113 AND HL.location_id = HPS.location_id
114 AND HL.country(+) = FT.territory_code
115 AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
116 AND UPPER(HCSU.site_use_code) = 'LEGAL'
117 AND HCAS.bill_to_flag IN ('P','Y')
118 AND HCAS.status = 'A'
119 AND HCSU.primary_flag = 'Y' ;
120
121 BEGIN
122 OPEN c_get_address ;
123 FETCH c_get_address
124 INTO x_postal
125 , x_city
126 , x_street_type
127 , x_street
128 , x_number;
129 CLOSE c_get_address ;
130 END get_customer_address2 ;
131
132 -- Modelo 347
133
134
135 PROCEDURE arrenda ( p_vat_rep_entity_id IN VARCHAR2
136 , p_customer_id IN NUMBER
137 , p_customer_name IN VARCHAR2
138 , p_cust_tax_reg_num IN VARCHAR2)
139 IS
140 CURSOR arrenda
141 IS
142 SELECT
143 SUM( NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt ) )
144 + SUM( NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt ) ) trx_line_amt
145 -- SUM( NVL(JZVTD.trx_line_amt,0 ) ) trx_line_amt
146 , NVL(JZVTD.tax_rate_id,0) tax_rate_id
147 , DECODE(SUBSTR(HL.country, 1, 25)
148 , 'ES',SUBSTR(HL.postal_code,1,5)
149 , '99'||FT.eu_code) postcode
150 , SUBSTR(HL.global_attribute2,1,2) global_attribute2
151 , HL.town_or_city city
152 , SUBSTR(HL.address_line_1,1,2) address1
153 , SUBSTR(HL.address_line_2,1,25) address2
154 , SUBSTR(HL.address_line_3||'Z',1,INSTR(HL.address_line_3||'Z',
155 LTRIM(HL.address_line_3||'Z','1234567890')) - 1) address3
156 , SUBSTR(HL.global_attribute3,1,2) global_attribute3
157 , SUBSTR(HL.global_attribute4,1,2) global_attribute4
158 , SUBSTR(HL.global_attribute5,1,2) global_attribute5
159 FROM jg_zz_vat_rep_status JZVRS
160 , jg_zz_vat_trx_details JZVTD
161 , hz_cust_acct_sites_all HCAS
162 , hz_party_sites HPS
163 , hr_locations HL
164 , fnd_territories FT
165 , hz_cust_site_uses_all HCSU
166 WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
167 AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
168 AND JZVTD.extract_source_ledger = 'AR'
169 AND JZVRS.source = 'AR'
170 AND HCAS.cust_acct_site_id = JZVTD.billing_tp_address_id
171 AND HPS.party_site_id = HCAS.party_site_id
172 AND HL.location_id = HPS.location_id
173 AND HL.country(+) = FT.territory_code
174 AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
175 AND UPPER(HCSU.site_use_code) = 'LEGAL'
176 AND HCAS.bill_to_flag IN ('P','Y')
177 AND HCAS.status = 'A'
178 AND HCSU.primary_flag = 'Y'
179 AND JZVTD.billing_trading_partner_id = p_customer_id
180 AND JZVTD.trx_date BETWEEN G_FROM_DATE AND G_TO_DATE
181 AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQ'),1,3) <> 'RET_AR' -- tax_rate_vat_trx_type_code
182 AND JZVTD.invoice_report_type IN ( -- 415 -- AR
183 '347'
184 , '415_347'
185 , '415_347PR'
186 , '347PR'
187 )
188 GROUP BY NVL(JZVTD.tax_rate_id,0)
189 , DECODE(SUBSTR(HL.country, 1, 25)
190 , 'ES',SUBSTR(HL.postal_code,1,5)
191 , '99'||FT.eu_code)
192 , SUBSTR(HL.global_attribute2,1,2)
193 , HL.town_or_city
194 , SUBSTR(HL.address_line_1,1,2)
195 , SUBSTR(HL.address_line_2,1,25)
196 , SUBSTR(HL.address_line_3||'Z',1,INSTR(HL.address_line_3||'Z',
197 LTRIM(HL.address_line_3||'Z','1234567890')) - 1)
198 , SUBSTR(HL.global_attribute3,1,2)
199 , SUBSTR(HL.global_attribute4,1,2)
200 , SUBSTR(HL.global_attribute5,1,2);
201
202 BEGIN
203 -- dbms_output.put_line ('Inside arrenda');
204
205 FOR arr_rec IN arrenda LOOP
206
207 IF LENGTH(arr_rec.postcode) = 2 /* i.e. the code is '99' only */
208 THEN
209 arr_rec.postcode := '99958';
210 END IF;
211
212 /**
213 author:Brathod
214 As no such currency conversion found in R11i code
215 SELECT DECODE( G_CURRENCY_CODE, 'EUR', (arr_rec.trx_line_amt*100), arr_rec.trx_line_amt)
216 INTO arr_rec.trx_line_amt
217 FROM DUAL;
218 */
219
220 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'inserting with tipo 3: p_cust_tax_reg_num=' ||p_cust_tax_reg_num); END IF;
221
222 -- dbms_output.put_line ('inserting ');
223 INSERT INTO JG_ZZ_VAT_TRX_GT
224 ( jg_info_v1 -- tipo
225 , jg_info_n2 -- importe -- sum_trx_line_amt
226 , jg_info_v11 -- nombre -- cust name
227 , jg_info_v12 -- nif -- cust tax ref
228 , jg_info_v25 -- sigla -- address1
229 , jg_info_v2 -- municipio -- global_attribute2
230 , jg_info_v18 -- codigo_postal -- postcode
231 , jg_info_v27 -- fin_ind -- 'S'
232 , jg_info_v26 -- via_publica -- address2,
233 , jg_info_v20 -- numero -- address3
234 , jg_info_n4 -- comentario --
235 , jg_info_v21 -- flag_arrenda -- 'Y'
236 , jg_info_v3 -- escalera -- global_attribute3
237 , jg_info_v4 -- piso -- global_attribute4
238 , jg_info_v5 -- puerta -- global_attribute5
239 , jg_info_v19 -- ref_catastral -- city
240 , jg_info_v22 -- p_print_year --p_tax_calender_year
241 , jg_info_v23 -- legal entity name
242 )
243 VALUES
244 ('3'
245 , arr_rec.trx_line_amt
246 , p_customer_name
247 , p_cust_tax_reg_num
248 , arr_rec.address1 --v_sg
249 , arr_rec.global_attribute2 --v_municipio
250 , arr_rec.postcode --v_codigo_postal,
251 , 'S' --fin_ind
252 , arr_rec.address2 --v_via_publica,
253 , arr_rec.address3 --v_numero,
254 , NULL --comentario,
255 ,'Y' --flag_arrenda,
256 , arr_rec.global_attribute3 --v_escalera,
257 , arr_rec.global_attribute4 --v_piso,
258 , arr_rec.global_attribute5 --v_puerta,
259 , arr_rec.city --v_ref_catastral,
260 , p_tax_year
261 , g_le_trn -- legal entity name
262 );
263
264 END LOOP;
265
266
267 END;
268
269 -- Modelo 349
270
271 Procedure JGZZVEFT
272 IS
273
274 CURSOR C_Spa_Pay_Mag_Ext
275 IS
276 /*SELECT SUBSTR(jg_info_v11,1,2) C_CODIGO_PAIS -- TAX REG NUM
277 , RPAD(SUBSTR(jg_info_v11,3,12),15,' ') C_NIF_OPERADOR -- -do-
278 , RPAD(jg_info_v12,40,' ') C_NOMBRE -- cust name
279 , UPPER(jg_info_v1) C_CLAVE_OPERACION
280 , jg_info_n12 C_BASE_IMPONIBLE
281 , jg_info_n10 C_RUNNING_TOTAL
282 , jg_info_n11 C_FORMERLY_DECL_AMOUNT
283 , jg_info_v15 C_YEAR
284 , jg_info_v16 C_PERIOD
285 , jg_info_v17 C_OPERACION_TRIANGULAR
286 , rowid row_id
287 FROM JG_ZZ_VAT_TRX_GT
288 ORDER BY rpad(jg_info_v12,40,'A')
289 , jg_info_v1,jg_info_v15, jg_info_v16 desc;*/ -- Bug 5525421
290 /** This query is used to find out the report level information of the following 1. summ of correction amount
291 2. sum non correction amount 3. number of correction records and 4. number of non correction records
292 This information will be displayed on the header line of the report. */
293 SELECT jg_info_v22 YEAR_CAB
294 , jg_info_v26 LE_TRN
295 , SUBSTR(jg_info_v11,1,2) C_CODIGO_PAIS -- TAX REG NUM
296 , RPAD(SUBSTR(jg_info_v11,3,12),15,' ') C_NIF_OPERADOR -- -do-
297 , RPAD(jg_info_v12,40,' ') C_NOMBRE -- cust name
298 , UPPER(jg_info_v1) C_CLAVE_OPERACION
299 , jg_info_v15 C_YEAR
300 , jg_info_v16 C_PERIOD
301 , SUM(NVL(jg_info_n12,0)) C_BASE_IMPONIBLE
302 , SUM(jg_info_n11) C_FORMERLY_DECL_AMOUNT
303 , SUM(jg_info_n10) C_RUNNING_TOTAL
304 , jg_info_v23 SIGN
305 , jg_info_v18 POST_CODE
306 , jg_info_v17 OPERACION_TRIANGULAR
307 FROM JG_ZZ_VAT_TRX_GT JZVTG
308 WHERE NVL(JZVTG.jg_info_v30, 'X') <>'H'
309 -- AND jg_info_v27 = P_DISPLAY_PERIOD
310 AND jg_info_v27 = DECODE(P_DISPLAY_PERIOD,'OA',jg_info_v27,P_DISPLAY_PERIOD) -- Modified for Bug 7486406
311 GROUP BY jg_info_v21
312 , jg_info_v22
313 , SUBSTR(jg_info_v11,1,2)
314 , RPAD(SUBSTR(jg_info_v11,3,12),15,' ')
315 , Jg_info_v12
319 , jg_info_v17
316 , jg_info_v1
317 , jg_info_v15
318 , jg_info_v16
320 , jg_info_v18
321 , jg_info_v23
322 , jg_info_v26
323 ORDER BY rpad(jg_info_v12,40,'A')
324 , jg_info_v1
325 , NVL(jg_info_v15,0)
326 , NVL(jg_info_v16,'A');
327
328 ln_base_imponible NUMBER := 0;
329 ln_base_imponible_dup NUMBER := 0;
330 ln_base_imponible_imp NUMBER := 0;
331 ln_running_total NUMBER := 0;
332 ln_formerly_decl_amount NUMBER := 0;
333 ln_corr_taxable_amount NUMBER := 0;
334 ln_imp_1 NUMBER := 0;
335 ln_imp_2 NUMBER := 0;
336 ln_cuenta_1 NUMBER := 0;
337 ln_cuenta_2 NUMBER := 0;
338 ln_cuenta_3_4 NUMBER := 0;
339
340 ln_grp_imp_1 NUMBER := 0;
341 ln_grp_imp_2 NUMBER := 0;
342 ln_grp_cuenta_1 NUMBER := 0;
343 ln_grp_cuenta_2 NUMBER := 0;
344 ln_grp_cuenta_3_4 NUMBER := 0;
345
346 lc_clave_operacion VARCHAR2(5);
347 lc_cur_derive_type VARCHAR2(5);
348 lc_source_ledger VARCHAR2(30);
349
350 BEGIN
351
352 -- Call to common routine to fetch legal entity info.
353 BEGIN
354 SELECT DECODE(curr.derive_type,'EURO','343','349')
355 INTO lc_cur_derive_type
356 FROM fnd_currencies curr
357 WHERE currency_code = g_currency_code;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 lc_cur_derive_type := '349' ;
361 END;
362
363 FOR c_spa_mag_rec IN C_Spa_Pay_Mag_Ext LOOP
364
365
366 -- Note that while inserting the correction we insert a small 'a','e' or 't'
367 -- so that the order by in the report works fine */
368
369 lc_clave_operacion := c_spa_mag_rec.c_clave_operacion;
370
371 --
372 -- Computation logic for running total and formerly_declared_amount should be inserted here
373 --
374 ln_running_total := c_spa_mag_rec.c_running_total;
375 ln_formerly_decl_amount := c_spa_mag_rec.c_formerly_decl_amount;
376 ln_base_imponible := c_spa_mag_rec.c_base_imponible;
377
378 --
379 ln_corr_taxable_amount := ln_formerly_decl_amount + ln_base_imponible ;
380 --
381 -- CF_BASE_IMPON_DUPFormula return Number is
382 -- and CF_BASE_IMPONIBLEFormula return VARCHAR2 is
383 --
384 BEGIN
385
386 IF lc_cur_derive_type = '343' /* i.e. functional Currency = EURO */
387 THEN
388 ln_base_imponible_imp := ln_base_imponible * 100;
389 ln_base_imponible_dup := (ln_base_imponible + ln_formerly_decl_amount) * 100;
390 ELSE
391 ln_base_imponible_imp := ln_base_imponible;
392 ln_base_imponible_dup := ln_base_imponible + ln_formerly_decl_amount;
393 END IF;
394 --srw.message('1000', 'CP Base Imponible...' || to_char(:cp_base_imponible));
395
396 END;
397
398 -- End of CF_BASE_IMPON_DUPFormula
399
400 -- CF_IMP_1Formula
401
402 IF c_spa_mag_rec.c_year IS NOT NULL AND c_spa_mag_rec.c_period IS NOT NULL THEN
403 ln_cuenta_3_4 := 1;
404 ln_cuenta_1 := 0;
405 ln_imp_2 := ln_base_imponible_dup;
406 ln_imp_1 := 0;
407 END IF;
408
409 IF ( lc_clave_operacion = 'E' OR
410 lc_clave_operacion = 'T' OR
411 lc_clave_operacion = 'A') THEN
412
413 IF (c_spa_mag_rec.c_year IS NULL AND c_spa_mag_rec.c_period IS NULL ) THEN -- Not a correction record
414 ln_cuenta_1 := 1;
415 ln_cuenta_3_4 := 0;
416 ln_imp_2 := 0;
417 ln_imp_1 := ln_base_imponible_dup;
418 ELSE
419 ln_imp_1 := 0;
420 END IF;
421 END IF;
422
423 -- End of CF_IMP_1Formula
424
425 ln_grp_imp_1 := ln_grp_imp_1 + ln_imp_1 ;
426 ln_grp_imp_2 := ln_grp_imp_2 + ln_imp_2 ;
427 ln_grp_cuenta_1 := ln_grp_cuenta_1 + ln_cuenta_1;
428 ln_grp_cuenta_2 := ln_grp_cuenta_2 + ln_cuenta_2;
429 ln_grp_cuenta_3_4 := ln_grp_cuenta_3_4 + ln_cuenta_3_4;
430
431 ln_imp_1 := 0;
432 ln_imp_1 := 0;
433 ln_cuenta_1 := 0;
434 ln_cuenta_2 := 0;
435 ln_cuenta_3_4 := 0;
436
437 END LOOP;
438
439 UPDATE JG_ZZ_VAT_TRX_GT
440 SET jg_info_N1 = ln_grp_cuenta_1
441 , jg_info_N2 = ln_grp_imp_1
442 , jg_info_N3 = ln_grp_cuenta_3_4
443 , jg_info_N4 = ln_grp_imp_2
444 WHERE jg_info_v30 = 'H' ;
445
446 END;
447
448 function format_amount ( p_amount IN NUMBER ) return Char is
449 lv_decimal_separator varchar2 (1);
450 begin
451
455 -- lv_decimal_separator := '.';
452 /* Get the decimal separator from the profile */
453 lv_decimal_separator := substr(fnd_profile.value('ICX_NUMERIC_CHARACTERS'),1,1); -- Bug 5525421
454
456 /** author: brathod
457 date : 22/05/2006
458 Forward porting bug: 4771261 for 11i bug:4748029
459 */
460 /* If c_base_imponible has no decimals */ -- Bug 4748029
461
462 if instr(to_char(p_amount),lv_decimal_separator,1,1) = 0 then -- Bug 4748029
463 RETURN((lpad(to_char(p_amount),11,'0')||'00')); -- Bug 4748029
464 end if;
465
466 /* If c_base_imponible has decimals */ -- Bug 4748029
467 if instr(p_amount,lv_decimal_separator,1,1) >0 then -- Bug 4748029
468 RETURN(lpad(substr(p_amount ,1,instr(to_char(p_amount),lv_decimal_separator,1,1)-1),11,'0')||
469 rpad(substr(p_amount,instr(to_char(p_amount),lv_decimal_separator,1,1)+1,2),2,'0')) ; -- Bug 4748029
470 end if;
471 end;
472
473 -- Modelo 415
474
475 FUNCTION CF_POSTCODEFormula ( p_postcode IN VARCHAR2 ) return VARCHAR2 is
476 BEGIN
477
478 IF NVL(LENGTH(p_postcode), 0) = 2 /* i.e. '99' with no country code as territory unknown */
479 THEN
480 RETURN(p_postcode||'958'); /* 958 is default European country code for 'unknown' */
481 ELSE
482 RETURN(p_postcode);
483 END IF;
484
485 RETURN NULL;
486 END;
487
488 FUNCTION AP415_GROSS_AMOUNTFormula ( p_vendor_id IN NUMBER
489 , p_gross_amount IN NUMBER )
490 return Number is
491 v_prepay_applied number;
492
493 BEGIN
494
495 SELECT SUM(ROUND(DECODE( NVL(JZVTD.taxable_amt_funcl_curr,0)
496 ,0 , JZVTD.taxable_amt
497 , JZVTD.taxable_amt_funcl_curr
498 ))
499 - NVL(AID.prepay_amount_remaining, 0 )* -1
500 )
501 INTO V_PREPAY_APPLIED
502 FROM jg_zz_vat_trx_details JZVTD
503 ,jg_zz_vat_rep_status JZVRS
504 ,ap_invoice_distributions AID
505 WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
506 AND JZVTD.billing_trading_partner_id = p_vendor_id
507 AND JZVTD.trx_type_mng = 'PREPAYMENT'
508 --AND JZVTD.posted_flag IN ('P','Y') /** author: brathod; Removed posted_flag checking as it is not relevent in R12 */
509 AND JZVTD.trx_line_type = 'ITEM'
510 AND NVL(JZVTD.reverse_flag, 'N') <> 'Y'
511 AND AID.invoice_distribution_id = JZVTD.trx_line_id
512 AND AID.invoice_id = JZVTD.trx_id
513 AND AID.prepay_amount_remaining IS NOT NULL
514 AND JZVTD.invoice_report_type IN ('415'
515 ,'415_347'
516 ,'415_347PR')
517 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
518 /**
519 author: brathod
520 date: 18/05/2006
521 Commented only period based filtering and added AND condition to also consider year
522 filtering for ANNUAL reports
523 -- JZVRS.tax_calendar_period = P_TAX_PERIOD
524 */
525 AND ( (P_REPORT_NAME = 'JEESPMOR' AND JZVRS.tax_calendar_period = P_TAX_PERIOD)
526 OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR )
527 )
528 ;
529 --Bug 1064230 - prepayment amount already accounted for
530 RETURN( p_gross_amount - NVL(v_prepay_applied,0));
531
532
533 RETURN NULL; EXCEPTION
534 WHEN OTHERS THEN
535 RETURN(p_gross_amount);
536
537 END;
538
539 FUNCTION AP347_GROSS_AMOUNTFormula ( p_vendor_id IN NUMBER
540 , p_property_flag IN VARCHAR2
541 , p_gross_amt IN NUMBER )
542 return Number is
543 v_prepay_applied number;
544
545 BEGIN
546
547 SELECT SUM(ROUND(DECODE( NVL(JZVTD.taxable_amt_funcl_curr,0)
548 ,0 , JZVTD.taxable_amt
549 , JZVTD.taxable_amt_funcl_curr
550 )
551 , g_cur_precision)
552 )
553 INTO V_PREPAY_APPLIED
554 FROM jg_zz_vat_trx_details JZVTD
555 ,jg_zz_vat_rep_status JZVRS
556 ,ap_invoice_distributions AID
557 ,ap_invoice_distributions PRE
558 ,hz_parties HP
559 WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
560 AND JZVTD.billing_trading_partner_id = p_vendor_id
561 AND JZVTD.trx_type_mng = 'PREPAYMENT'
562 --AND JZVTD.posted_flag IN ('P','Y') /** author:brathod; Removed posted_flag checking as it is not relevent in R12 */
563 AND AID.invoice_Id = JZVTD.trx_id
564 AND PRE.invoice_distribution_id = AID.invoice_distribution_id
565 AND PRE.posted_flag IN ('P','Y')
566 AND PRE.line_type_lookup_code <> 'AWT'
567 AND HP.party_id = JZVTD.billing_trading_partner_id
568 AND NVL(hp.party_type, 'XXX') <> 'EMPLOYEE'
569 AND DECODE(JZVTD.invoice_report_type
573 , '415_347PR','Y') = p_property_flag
570 , '347', 'N'
571 , '347PR', 'Y'
572 , '415_347','N'
574 AND JZVTD.invoice_report_type IN ('347'
575 ,'347PR'
576 ,'415_347'
577 ,'415_347PR')
578 AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
579 /**
580 author: brathod
581 date : 18/05/2006
582 Commented only period based filtering and added AND condition to also consider year
583 filtering for ANNUAL reports
584 -- JZVRS.tax_calendar_period = P_TAX_PERIOD
585 */
586 AND ( (P_REPORT_NAME = 'JEESPMOR' AND JZVRS.tax_calendar_period = P_TAX_PERIOD)
587 OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR )
588 );
589 --Bug 1064230 - prepayment amount already accounted for
590 RETURN( p_gross_amt - NVL(v_prepay_applied,0));
591
592
593 RETURN NULL; EXCEPTION
594 WHEN OTHERS THEN
595 RETURN( p_gross_amt );
596
597 END;
598
599 FUNCTION AR347_GROSS_AMOUNTFormula ( p_customer_id IN NUMBER
600 )
601 RETURN NUMBER is
602 v_prepay_applied number;
603
604 BEGIN
605
606 /**
607 author: brathod
608 Commented to use jg_zz_vat_trx_detail instead of JG_ZZ_VAT_TRX_GT
609 SELECT SUM(ROUND(DECODE( NVL(JVGT.jg_info_n8,0)
610 ,0 , JVGT.jg_info_n6
611 , JVGT.jg_info_n8
612 )
613 , g_cur_precision)
614 )
615 INTO V_PREPAY_APPLIED
616 FROM JG_ZZ_VAT_TRX_GT JVGT
617 , hz_cust_acct_sites_all HCAS
618 , hz_party_sites HPS
619 , hz_locations HL
620 , fnd_territories FT
621 , hz_cust_site_uses_all HCSU
622 WHERE JVGT.jg_info_n3 = p_customer_id
623 AND HCAS.cust_acct_site_id = JVGT.jg_info_n2
624 AND HPS.party_site_id = HCAS.party_site_id
625 AND HL.location_id = HPS.location_id
626 AND HL.country(+) = FT.territory_code
627 AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
628 AND UPPER(HCSU.site_use_code) = 'LEGAL'
629 AND HCAS.bill_to_flag = 'Y'
630 AND HCAS.status = 'A'
631 AND JVGT.jg_info_d5 IS NOT NULL
632 AND NVL(JVGT.jg_info_v25, 'N') = 'N' -- posted_flag
633 AND JVGT.jg_info_v31 <> 'DEBIT' -- invoice_type_lookup_code
634 AND HCSU.primary_flag = 'Y'
635 AND SUBSTR(NVL(JVGT.jg_info_v31,'QQQQQQ'),1,3) <> 'RET' -- tax_rate_vat_trx_type_code
636 AND JVGT.jg_info_v11 IN ('347'
637 ,'347PR');*/
638
639 /**
640 author: brathod
641 1. Add code below to use jg_zz_vat_trx_detail instead of JG_ZZ_VAT_TRX_GT
642 2. Commented TCA related tables as they are not required to fetch the amount and directly joined HCSU with JVGT*
643 */
644
645 SELECT SUM(ROUND( NVL(JVGT.taxable_amt_funcl_curr,JVGT.taxable_amt ) , 2) )
646 + SUM(ROUND( NVL(JVGT.tax_amt_funcl_curr,JVGT.tax_amt ) , 2) ) arrenda_amount
647 -- SUM(ROUND(NVL(JVGT.trx_line_amt,0), 2)) arrenda_amount
648 INTO V_PREPAY_APPLIED
649 FROM JG_ZZ_VAT_TRX_DETAILS JVGT, JG_ZZ_VAT_REP_STATUS JZVRS
650 --, hz_cust_acct_sites_all HCAS
651 --, hz_party_sites HPS
652 --, hz_locations HL
653 --, fnd_territories FT
654 , hz_cust_site_uses_all HCSU
655 WHERE JVGT.billing_trading_partner_id = p_customer_id
656 --AND HCAS.cust_acct_site_id = JVGT.billing_tp_address_id
657 --AND HPS.party_site_id = HCAS.party_site_id
658 --AND HL.location_id = HPS.location_id
659 --AND HL.country(+) = FT.territory_code
660 AND HCSU.cust_acct_site_id = JVGT.billing_tp_address_id -- HCAS.cust_acct_site_id
661 AND UPPER(HCSU.site_use_code) = 'LEGAL'
662 --AND HCAS.bill_to_flag IN ('P','Y')
663 --AND HCAS.status = 'A'
664 AND JVGT.extract_source_ledger = 'AR'
665 AND JVGT.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
666 -- AND JVGT.accounting_date IS NOT NULL --bug5557860
667 -- AND JVGT.posted_flag = 'Y' /** author: brathod; removed posted_flag checking as it is not relevent in R12*/
668 AND HCSU.primary_flag = 'Y'
669 AND SUBSTR(NVL(JVGT.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET' -- tax_rate_vat_trx_type_code
670 AND JVGT.invoice_report_type IN ('415_347PR','347PR')
671 /**
672 author: brathod
673 date : 18/05/2006
674 Added AND condition to consider year and period based filtering
675 */
676 AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
677 AND JZVRS.source = 'AR'
678 AND JZVRS.reporting_status_id = JVGT.reporting_status_id
679 AND ( (P_REPORT_NAME = 'JEESPMOR' AND JZVRS.tax_calendar_period = P_TAX_PERIOD)
680 OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR )
681 );
685
682
683 --Bug 1064230 - prepayment amount already accounted for
684 RETURN( NVL(v_prepay_applied,0));
686
687 RETURN NULL; EXCEPTION
688 WHEN OTHERS THEN
689 RETURN( 0 );
690
691 END;
692
693 FUNCTION before_Report
694 RETURN BOOLEAN
695 IS
696
697
698 /**
699 author: brathod
700 Introduced place holder $MODELO_TABLE_LIST$ which can contain additional table required by specific modelo reports
701 For MODELO=415 and SOURCE=AP value will be AP_SUPPLIER_SITES APSS to add a filter in MODELO_415 AP report
702 For MODELO=347 and SOURCE=AP value will be AP_SUPPLIERS APS, AP_SUPPLIER_SITES APSS to add filter in MODELO_347 AP report
703 */
704
705 C_JGZZ_MODELO_GENRIC_QUERY CONSTANT VARCHAR2 (32000) :=
706 '
707 SELECT /*+ NO_REWRITE */
708 $TAX_REGISTRATION_NUM$ TAX_REGISTRATION_NUM
709 , substr(billing_tp_name,1,80) CUSTOMER_NAME
710 , $ADDRESS_ID$ CUSTOMER_ADDRESS_ID
711 , DECODE( JZVTD.extract_source_ledger
712 , ''AP'' , JZVTD.bill_from_party_id
713 , JZVTD.billing_trading_partner_id ) BILLING_TRADING_PARTNER_ID
714 , SUM(DECODE(JZVTD.extract_source_ledger
715 ,''AP'',DECODE(JZVTD.OFFSET_FLAG,''N'',DECODE(JZVTD.tax_recoverable_flag
716 ,''N'',DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
717 , 0 ,JZVTD.taxable_amt
718 , JZVTD.taxable_amt_funcl_curr)),0)
719 , DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
720 , 0 ,JZVTD.taxable_amt
721 , JZVTD.taxable_amt_funcl_curr ))
722 )
723 +
724 SUM(DECODE(JZVTD.extract_source_ledger
725 ,''AP'', DECODE(NVL(JZVTD.tax_amt_funcl_curr,0)
726 , 0, JZVTD.tax_amt,
727 JZVTD.tax_amt_funcl_curr) ) )
728 SUM_TAXABLE_AMT
729 --, SUM( NVL(trx_line_amt,0)) SUM_TRX_LINE_AMT
730 , SUM( NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt ) )
731 + SUM( NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt ) ) SUM_TRX_LINE_AMT
732 /**
733 author: brathod;
734 Commented as correction_flag is not relevent for Spain transactions
735 , SUM(DECODE(NVL( JZVTD.correction_flag,''N'')
736 ,''N'', NVL( JZVTD.taxable_amt, 0)
737 , 0)) NCORRECTION_AMOUNT
738 , SUM(DECODE(NVL( JZVTD.correction_flag,''N'')
739 ,''Y'', NVL( JZVTD.taxable_amt, 0)
740 , 0)) CORRECTION_AMOUNT
741 */
742 , SUM(DECODE(JZVTD.es_correction_period,null,
743 DECODE(JZVTD.extract_source_ledger,''AP'',
744 DECODE(JZVTD.OFFSET_FLAG,''N'',
745 DECODE(JZVTD.tax_recoverable_flag,''N'',
746 DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0), 0 ,JZVTD.taxable_amt,JZVTD.taxable_amt_funcl_curr)
747 )
748 ,0),
749 NVL( JZVTD.taxable_amt, 0)
750 )
751 ,0)
752 ) NCORRECTION_AMOUNT
753 , SUM(DECODE(JZVTD.es_correction_period , null,
754 0,
755 DECODE(JZVTD.extract_source_ledger,''AP'',
756 DECODE(JZVTD.OFFSET_FLAG,''N'',
757 DECODE(JZVTD.tax_recoverable_flag,''N'',
758 DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0), 0 ,JZVTD.taxable_amt, JZVTD.taxable_amt_funcl_curr)
759 )
760 ,0)
761 ,NVL( JZVTD.taxable_amt, 0)
762 )
763 )
764 ) CORRECTION_AMOUNT
765 $CORRECTION_TRX_SEL_COL$
766 $PROPERTY_FLAG_SEL_COL$
767 FROM jg_zz_vat_rep_status JZVRS
768 , jg_zz_vat_trx_details JZVTD
769 $MODELO_TABLE_LIST$
770 WHERE JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
771 --AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
772 AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
773 FROM jg_zz_vat_rep_status JZRS
774 WHERE JZRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
775 AND JZRS.source IN ( ''AP'', ''AR'' ))
776 AND JZVTD.extract_source_ledger IN ( ''AP'', ''AR'' )
777 AND JZVRS.source IN ( ''AP'', ''AR'' )
778 /**
779 author: brathod
780 date : 18/5/2006
781 Commented date based filtering and introduced conditional filtering based on report type.
782 AND JZVTD.tax_invoice_date BETWEEN $PERIOD_FROM_DATE$
783 AND $PERIOD_TO_DATE$
784
785 */
786 AND $FILTER_KEY$ = ''$FILTER_VALUE$''
787 $MODELO_SPECIFIC_FILTERS$
788 GROUP BY $TAX_REGISTRATION_NUM$
789 , substr(billing_tp_name,1,80)
790 , $ADDRESS_ID$
791 , DECODE( JZVTD.extract_source_ledger
792 , ''AP'' , JZVTD.bill_from_party_id
796
793 , JZVTD.billing_trading_partner_id )
794 $CORRECTION_TRX_GRP_COL$ $PROPERTY_FLAG_GRP_COL$ $MODELO_SPECIFIC_GRP_FILTER$ ';
795
797 C_MOD415_AP_FILTER CONSTANT VARCHAR2(4000) :=
798 ' AND JZVTD.extract_source_ledger = ''AP''
799 AND JZVRS.source = ''AP''
800 AND JZVTD.trx_line_type <> ''AWT''
801 /** author:brathod; removed posted_flag checking as it is not relevent in R12*/
802 --AND JZVTD.posted_flag IN (''P'',''Y'')
803 --AND JZVTD.tax_rate_vat_trx_type_code <> ''DEBIT''
804 AND JZVTD.trx_line_class <> ''DEBIT''
805 --AND NVL(JZVTD.billing_tp_tax_reporting_flag, ''N'') = ''Y''
806 AND JZVTD.invoice_report_type IN ( ''415''
807 , ''415_347''
808 , ''415_347PR''
809 )
810 /** author: brathod
811 Added following conditions to check for TAX_REPORTING_SITE_FLAG
812 */
813 AND JZVTD.BILLING_TRADING_PARTNER_ID = APSS.VENDOR_ID
814 AND APSS.TAX_REPORTING_SITE_FLAG = ''Y''
815 AND APSS.ORG_ID = $P_ORG_ID$';
816
817 C_MOD415_AR_FILTER constant VARCHAR2(4000) :=
818 ' AND JZVTD.extract_source_ledger = ''AR''
819 AND JZVRS.source = ''AR''
820 AND JZVTD.trx_line_type <> ''AWT''
821 -- AND JZVTD.accounting_date IS NOT NULL --bug5557860
822 --AND JZVTD.posted_flag = ''N'' /** author:brathod; removed posted_flag checking as it is not relevent in R12 */
823 --AND JZVTD.tax_rate_vat_trx_type_code <> ''DEBIT''
824 AND JZVTD.trx_line_class <> ''DEBIT''
825 AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,''QQQQQQ''),1,6) <> ''RET_AR''
826 AND JZVTD.invoice_report_type IN ( ''415''
827 , ''415_347''
828 , ''415_347PR''
829 )
830 AND JZVTD.BILLING_TRADING_PARTNER_ID = hzca.cust_account_id
831 AND hzca.cust_account_id = hzcas.cust_account_id
832 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
833 AND upper(hzcsu.site_use_code) = ''LEGAL''
834 AND hzcsu.primary_flag = ''Y''
835 AND hzcsu.status = ''A''
836 AND hzcsu.ORG_ID = $P_ORG_ID$';
837
838
839 C_MOD347_AP_FILTER CONSTANT VARCHAR2(4000) :=
840 'AND JZVTD.extract_source_ledger = ''AP''
841 AND JZVRS.source = ''AP''
842 AND JZVTD.trx_line_type <> ''AWT''
843 /** author:brathod; removed posted_flag checking as it is not relevent in R12 */
844 -- AND JZVTD.posted_flag IN (''P'',''Y'')
845 AND JZVTD.trx_line_class <> ''EXPENSE REPORT''
846 --AND JZVTD.tax_rate_vat_trx_type_code <> ''EXPENSE REPORT''
847 --AND NVL(JZVTD.billing_tp_tax_reporting_flag, ''N'') = ''Y''
848 AND JZVTD.applied_from_line_id IS NULL
849 AND JZVTD.invoice_report_type IN( ''347''
850 , ''347PR''
851 , ''415_347''
852 , ''415_347PR''
853 )
854 /** author: brathod
855 Added following conditions to check for FEDERAL_REPORTABLE_FLAG and TAX_REPORTING_SITE_FLAG
856 */
857 AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
858 AND APS.VENDOR_ID = APSS.VENDOR_ID
859 AND NVL(APS.FEDERAL_REPORTABLE_FLAG,''Y'') = ''Y''
860 AND APSS.TAX_REPORTING_SITE_FLAG = ''Y''
861 AND APSS.ORG_ID = $P_ORG_ID$';
862
863 C_MOD347_AR_FILTER CONSTANT VARCHAR2(4000) :=
864 ' AND JZVTD.extract_source_ledger = ''AR''
865 AND JZVRS.source = ''AR''
866 -- AND JZVTD.accounting_date IS NOT NULL --bug5557860
867 -- AND JZVTD.posted_flag = ''N'' /** author:brathod; removed posted_flag checking as it is not relevent in R12*/
868 -- AND JZVTD.tax_rate_vat_trx_type_code <> ''DEBIT''
869 AND JZVTD.trx_line_class <> ''DEBIT''
870 -- AND NVL(JZVTD.billing_tp_tax_reporting_flag, ''N'') = ''Y''
871 AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,''QQQQQQ''),1,3) <> ''RET''
872 AND JZVTD.invoice_report_type IN ( ''347''
873 , ''415_347''
874 , ''415_347PR''
875 , ''347PR''
876 )
877 AND JZVTD.BILLING_TRADING_PARTNER_ID = hzca.cust_account_id
878 AND hzca.cust_account_id = hzcas.cust_account_id
879 AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
880 AND upper(hzcsu.site_use_code) = ''LEGAL''
881 AND hzcsu.primary_flag = ''Y''
882 AND hzcsu.status = ''A''
883 AND hzcsu.ORG_ID = $P_ORG_ID$';
884
885
886 C_MOD349_AP_FILTER CONSTANT VARCHAR2(4000) :=
887 ' AND JZVTD.extract_source_ledger = ''AP''
888 AND JZVRS.source = ''AP''
892
889 --AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = ''TAX'' --JZVTD.tax_rate_vat_trx_type_code
890 --AND JZVTD.tax_recoverable_flag = ''Y''
891 AND JZVTD.invoice_report_type IN ( ''349'')';
893 C_MOD349_AR_FILTER VARCHAR2(4000) :=
894 ' AND JZVTD.extract_source_ledger = ''AR''
895 AND JZVRS.source = ''AR''
896 AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = ''TAX'' --JZVTD.tax_rate_vat_trx_type_code
897 --AND JZVTD.tax_recoverable_flag = ''Y'' /** author:brathod; commented as no such check found in R11i */
898 AND JZVTD.invoice_report_type IN ( ''349'')';
899
900 C_CORRECTION_TRX_SEL_COLS CONSTANT VARCHAR2(4000) :=
901 ', JZVTD.es_correction_year CORRECTION_YEAR
902 , JZVTD.es_correction_period CORRECTION_PERIOD
903 , DECODE( JZVTD.triangulation,''Y'',''X'',NULL) TRIANGULATION
904 , DECODE(TO_CHAR(JZVTD.TRX_DATE, ''MM''),''01'',''1T'',''02'',''1T'',''03'',''1T'',
905 ''04'',''2T'',''05'',''2T'',''06'',''2T'',
906 ''07'',''3T'',''08'',''3T'',''09'',''3T'',
907 ''10'',''4T'',''11'',''4T'',''12'',''4T'') TRX_PERIOD '; -- Bug 5525421
908
909 C_CORRECTION_TRX_GRP_COLS CONSTANT VARCHAR2(4000) :=
910 ', JZVTD.es_correction_year
911 , JZVTD.es_correction_period
912 , DECODE( JZVTD.triangulation,''Y'',''X'',NULL)
913 , DECODE(TO_CHAR(JZVTD.TRX_DATE, ''MM''),''01'',''1T'',''02'',''1T'',''03'',''1T'',
914 ''04'',''2T'',''05'',''2T'',''06'',''2T'',
915 ''07'',''3T'',''08'',''3T'',''09'',''3T'',
916 ''10'',''4T'',''11'',''4T'',''12'',''4T'')
917 order by TRX_PERIOD'; -- Bug 5525421
918
919 C_CORRECTION_TRX_NULL_COLS CONSTANT VARCHAR2(4000) :=
920 ', NULL correction_year
921 , NULL correction_period
922 , NULL triangulation
923 , NULL trx_period ';
924
925 C_347_SEL_COL VARCHAR2(4000) :=
926 ', DECODE (JZVTD.invoice_report_type
927 , ''347'', ''N''
928 , ''347PR'', ''Y''
929 , ''415_347'', ''N''
930 , ''415_347PR'',''Y'') PROPERTY_RETAIL_FLAG';
931
932 C_NULL_SEL_COL VARCHAR2(4000) :=
933 ', NULL PROPERTY_RETAIL_FLAG ';
934
935 /* Updated filter to include P_MIN_VALUE parameter instead of hardcoded 0 value */
936 C_MOD347415_GRP_FILTER CONSTANT VARCHAR2(1000) :=
937 'HAVING SUM(DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
938 , 0 ,JZVTD.taxable_amt
939 , JZVTD.taxable_amt_funcl_curr )) >= $P_MIN_VALUE$ '; --0
940
941 --
942 lc_vat_code_details VARCHAR2(240);
943 lc_func_curr_code VARCHAR2(240);
944 lc_rep_legal_entity VARCHAR2(240);
945 lc_trx_num VARCHAR2(240);
946 ln_rep_legal_entity_id NUMBER;
947 ld_period_start_date DATE;
948 ld_period_end_date DATE;
949 lc_prev_vat_code VARCHAR2(400) :='';
950 lc_count NUMBER := 0;
951 lc_reporting_mode VARCHAR2(240);
952 --
953 lc_taxpayer_id jg_zz_vat_trx_details.billing_tp_taxpayer_id%TYPE;
954 lc_company_name xle_registrations.registered_name%TYPE;
955 lc_registration_number xle_registrations.registration_number%TYPE;
956 lc_country hr_locations.country%TYPE;
957 lc_address1 hr_locations.address_line_1%TYPE;
958 lc_address2 hr_locations.address_line_2%TYPE;
959 lc_address3 hr_locations.address_line_3%TYPE;
960 lc_address4 hz_locations.address4%TYPE;
961 lc_city hr_locations.town_or_city%TYPE;
962 lc_postal_code hr_locations.postal_code%TYPE;
963 lc_contact hz_parties.party_name%TYPE;
964 lc_phone_number hz_contact_points.phone_number%TYPE;
965 -- Added for Glob-006 ER
966 l_province VARCHAR2(120);
967 l_comm_num VARCHAR2(30);
968 l_vat_reg_num VARCHAR2(50);
969
970 --
971 lc_jgzz_modelo_query VARCHAR2(32000);
972 lc_jgzz_modelo_query1 VARCHAR2(32000);
973 --
974 lc_clave_operation JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
975 lc_tax_registration_number JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
976 lc_customer_name JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
977 lc_customer_address_id JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
978 lc_billing_trading_partner_id JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
979 ln_sum_taxable_amt JG_ZZ_VAT_TRX_GT.jg_info_n11%TYPE;
980 ln_sum_trx_line_amt JG_ZZ_VAT_TRX_GT.jg_info_n11%TYPE;
981 ln_ncorrection_amount JG_ZZ_VAT_TRX_GT.jg_info_n11%TYPE;
982 ln_correction_amount JG_ZZ_VAT_TRX_GT.jg_info_n11%TYPE;
983 lc_correction_year JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
984 lc_correction_period JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
985 lc_triangulation JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
986 lc_trx_period VARCHAR2(2);
987 lc_print_year NUMBER;
991 --
988 lc_print_period VARCHAR2(2);
989 lc_property_retail_flag JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
990 lc_address_detail JG_ZZ_VAT_TRX_GT.jg_info_v11%TYPE;
992 ln_base_imponiable NUMBER;
993 ln_running_total NUMBER;
994 ln_formerly_decl_amt NUMBER;
995 ln_arrenda_amount NUMBER;
996 --
997 lc_sign VARCHAR2(1);
998 --
999 lc_codigo_postal VARCHAR2(150);
1000 lc_ref_catastral VARCHAR2(150);
1001 lc_street_type VARCHAR2(150);
1002 lc_street VARCHAR2(150);
1003 lc_number VARCHAR2(150);
1004 lc_escalera VARCHAR2(150);
1005 lc_piso VARCHAR2(150);
1006 lc_puerta VARCHAR2(150);
1007 --
1008 TYPE c_modelo_ext_type IS REF CURSOR ;
1009 c_modelo_ext c_modelo_ext_type;
1010 --
1011 lc_misc VARCHAR2(1000);
1012 --
1013 BEGIN
1014 -- dbms_output.put_line ('Executing before_report');
1015 fnd_file.put_line(FND_FILE.LOG,'Executing before_report');
1016 -- dbms_output.put_line ('Calling funct_curr_legal');
1017
1018 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'jg_zz_common_pkg.funct_curr_legal'); END IF;
1019
1020 jg_zz_common_pkg.funct_curr_legal( lc_func_curr_code
1021 , lc_rep_legal_entity
1022 , ln_rep_legal_entity_id
1023 , lc_taxpayer_id
1024 , p_vat_rep_entity_id
1025 , p_tax_period
1026 , p_tax_year
1027 );
1028 -- dbms_output.put_line ('jg_zz_common_pkg.tax_registration');
1029 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'jg_zz_common_pkg.tax_registration'); END IF;
1030
1031 BEGIN
1032 jg_zz_common_pkg.tax_registration( x_tax_registration => lc_trx_num
1033 , x_period_start_date => ld_period_start_date
1034 , x_period_end_date => ld_period_end_date
1035 , x_status => lc_reporting_mode
1036 , pn_vat_rep_entity_id => p_vat_rep_entity_id
1037 , pv_period_name => p_tax_period
1038 , pn_period_year => p_tax_year
1039 , pv_source => P_SOURCE
1040 );
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 NULL;
1044 END;
1045 -- dbms_output.put_line ('jg_zz_common_pkg.tax_registration');
1046 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'jg_zz_common_pkg.company_detail'); END IF;
1047
1048 jg_zz_common_pkg.company_detail(x_company_name => lc_company_name
1049 ,x_registration_number => lc_registration_number
1050 ,x_country => lc_country
1051 ,x_address1 => lc_address1
1052 ,x_address2 => lc_address2
1053 ,x_address3 => lc_address3
1054 ,x_address4 => lc_address4
1055 ,x_city => lc_city
1056 ,x_postal_code => lc_postal_code
1057 ,x_contact => lc_contact
1058 ,x_phone_number => lc_phone_number
1059 ,x_province => l_province
1060 ,x_comm_number => l_comm_num
1061 ,x_vat_reg_num => l_vat_reg_num
1062 ,pn_legal_entity_id => ln_rep_legal_entity_id
1063 ,p_vat_reporting_entity_id => P_VAT_REP_ENTITY_ID);
1064
1065
1066
1067 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'JG_ZZ_VAT_REP_UTILITY.get_period_status'); END IF;
1068
1069 -- Fetch the reporting mode only if the report name is not null
1070 IF P_REPORT_NAME IS NOT NULL THEN
1071 lc_reporting_mode := JG_ZZ_VAT_REP_UTILITY.get_period_status(pn_vat_reporting_entity_id => p_vat_rep_entity_id
1072 ,pv_tax_calendar_period => p_tax_period
1073 ,pv_tax_calendar_year => p_tax_year
1074 ,pv_source => P_SOURCE
1075 ,pv_report_name => P_REPORT_NAME||':'||P_MODELO);
1076 ELSE
1077 lc_reporting_mode := NULL;
1078 END IF;
1079
1080 G_CURRENCY_CODE := lc_func_curr_code;
1081 /* bug 5729082 start */
1082 IF P_REPORT_NAME = 'JEESAMOR' AND P_MODELO = '347' THEN
1083 G_LE_TRN := lc_taxpayer_id;
1084 ELSE
1085 G_LE_TRN := lc_trx_num;
1086 End IF;
1087 /* end bug 5729082 */
1088
1089 G_LE_NAME := lc_rep_legal_entity;
1090 G_FROM_DATE := ld_period_start_date;
1091 G_TO_DATE := ld_period_end_date;
1092 -- dbms_output.put_line ('SELECT precision INTO G_CUR_PRECISION');
1093 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'SELECT precision INTO G_CUR_PRECISION'); END IF;
1094
1095 BEGIN
1096 SELECT precision
1097 INTO G_CUR_PRECISION
1098 FROM fnd_currencies curr
1099 WHERE currency_code = g_currency_code;
1100 EXCEPTION
1104 -- dbms_output.put_line ('P_REPORT_NAME:'||P_REPORT_NAME);
1101 WHEN NO_DATA_FOUND THEN
1102 NULL;
1103 END;
1105 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'P_REPORT_NAME:'||P_REPORT_NAME); END IF;
1106
1107 IF P_REPORT_NAME = 'JEESAMOR' AND P_MODELO = '347' THEN
1108 lc_address1 := SUBSTR( lc_address1, 1,2);
1109 lc_address2 := SUBSTR( lc_address2, 1,20);
1110 lc_address3 := LPAD(SUBSTR(lc_address3,1,
1111 NVL(LENGTH(lc_address3), 0) -
1112 NVL(NVL(LENGTH(LTRIM(TRANSLATE(lc_address3,'123456789','000000000'),'0')), 0),0)),5,'0');
1113 lc_city := SUBSTR(lc_city,1,12);
1114 lc_postal_code := SUBSTR(lc_postal_code,1,5);
1115 lc_company_name := SUBSTR(lc_company_name,1,40);
1116
1117 ELSIF P_REPORT_NAME = 'JEESAMOR' AND P_MODELO = '415' THEN
1118 lc_address1 := SUBSTR( lc_address1, 1,2);
1119 lc_address2 := SUBSTR( lc_address2, 1,25);
1120 lc_address3 := LPAD(SUBSTR(lc_address3,1,
1121 NVL(LENGTH(lc_address3), 0) -
1122 NVL(NVL(LENGTH(LTRIM(TRANSLATE(lc_address3,'123456789','000000000'),'0')), 0),0)),5,'0');
1123 lc_postal_code := SUBSTR(lc_postal_code,1,5);
1124 lc_company_name := SUBSTR(lc_company_name,1,40);
1125 lc_city := SUBSTR(lc_city,1,24);
1126 END IF;
1127 -- dbms_output.put_line ('BEFORE INSERT HEADER');
1128 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'BEFORE INSERT HEADER'); END IF;
1129
1130 INSERT INTO JG_ZZ_VAT_TRX_GT
1131 (jg_info_v1 -- curr_code
1132 ,jg_info_v2 -- entity_name
1133 ,jg_info_v3 -- taxpayer_id
1134 ,jg_info_v4 -- company_name
1135 ,jg_info_v5 -- registration_number
1136 ,jg_info_v26 -- country
1137 ,jg_info_v27 -- address1
1138 ,jg_info_v28 -- address2
1139 ,jg_info_v29 -- address3
1140 ,jg_info_v31 -- address4
1141 ,jg_info_v32 -- city
1142 ,jg_info_v6 -- postal_code
1143 ,jg_info_v7 -- contact
1144 ,jg_info_v33 -- phone_number
1145 ,jg_info_v8 -- reporting mode
1146 ,jg_info_v9 -- P_TAX_YEAR
1147 ,jg_info_v10 -- P_TAX_PERIOD
1148 ,jg_info_v11 -- P_MODELO
1149 ,jg_info_v35 -- P_SOURCE
1150 ,jg_info_v12 -- P_CONTACT_TEL
1151 ,jg_info_v13 -- P_CONTACT_NAME
1152 ,jg_info_v14 -- P_TAX_OFFICE
1153 ,jg_info_v15 -- P_CONTACT_TEL_CODE
1154 ,jg_info_v16 -- P_REFERENCE_NUMBER
1155 ,jg_info_v17 -- P_MAIN_ACTIVITY
1156 ,jg_info_v18 -- P_MAIN_ACTIVITY_CD
1157 ,jg_info_v19 -- P_SECOND_ACTIVITY
1158 ,jg_info_v20 -- P_SECOND_ACTIVITY_CD
1159 ,jg_info_v21 -- P_TOTAL_PURCHASES
1160 ,jg_info_v22 -- P_TOTAL_SALES
1161 ,jg_info_v23 -- P_TAX_OFF_REG_CODES
1162 ,jg_info_v24 -- P_MEDIUM
1163 ,jg_info_v25 -- P_FORMAT_TYPE
1164 ,jg_info_v34 -- P_PRV_REFERENCE_NUMBER
1165 ,jg_info_n6 -- P_MIN_VALUE
1166 ,jg_info_d1 -- ld_period_start_date
1167 ,jg_info_d2 -- ld_period_end_date
1168 ,jg_info_v30 -- Header record indicator
1169 )
1170 VALUES
1171 (lc_func_curr_code
1172 ,lc_rep_legal_entity
1173 ,lc_taxpayer_id
1174 ,lc_company_name
1175 ,lc_trx_num
1176 ,lc_country
1177 ,lc_address1
1178 ,lc_address2
1179 ,lc_address3
1180 ,lc_address4
1181 ,lc_city
1182 ,lc_postal_code
1183 ,lc_contact
1184 ,lc_phone_number
1185 ,lc_reporting_mode
1186 ,P_TAX_YEAR
1187 ,P_TAX_PERIOD
1188 ,P_MODELO
1189 ,P_SOURCE
1190 ,P_CONTACT_TEL
1191 ,P_CONTACT_NAME
1192 ,P_TAX_OFFICE
1193 ,P_CONTACT_TEL_CODE
1194 ,P_REFERENCE_NUMBER
1195 ,P_MAIN_ACTIVITY
1196 ,P_MAIN_ACTIVITY_CD
1197 ,P_SECOND_ACTIVITY
1198 ,P_SECOND_ACTIVITY_CD
1199 ,P_TOTAL_PURCHASES
1200 ,P_TOTAL_SALES
1201 ,P_TAX_OFF_REG_CODES
1202 ,P_MEDIUM
1203 ,P_FORMAT_TYPE
1204 ,P_PRV_REFERENCE_NUMBER
1205 ,P_MIN_VALUE
1206 ,ld_period_start_date
1207 ,ld_period_end_date
1208 ,'H');
1209 -- dbms_output.put_line ('AFTER INSERT HEADER');
1210 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'AFTER INSERT HEADER'); END IF;
1211
1212 /**
1213 author: brathod
1214 date : 23/05/2006
1215 User has requested "Generic Extract". Hence no need to apply report specific filters.
1216 Only reporting entity related information is populated in JG_ZZ_VAT_TRX_GT global temp table.
1217 Returning control from here to continue with xml data template query processing
1218 */
1219
1220 IF (P_REPORT_NAME IS NULL) THEN
1221 return (true);
1222 END IF;
1223
1224 /**
1225 Control will not come here if GENERIC EXTRACT is executed
1226 */
1227
1228 IF ( P_REPORT_NAME = 'JEESPMOR' AND P_SOURCE = 'AP')
1229 OR P_REPORT_NAME = 'JEESAMOR' THEN
1230
1231 --
1232 -- Build query to fetch ap extract for modelo from JG
1233 --
1234
1235 lc_jgzz_modelo_query := C_JGZZ_MODELO_GENRIC_QUERY;
1236 --
1237 /**
1238 author : brathod
1239 date : 18/5/2006
1240 Commented following code to remove date filtering.
1244 , 'TO_DATE('''||TO_CHAR(ld_period_start_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1241 lc_jgzz_modelo_query :=
1242 REPLACE( lc_jgzz_modelo_query
1243 , '$PERIOD_FROM_DATE$'
1245
1246 lc_jgzz_modelo_query :=
1247 REPLACE( lc_jgzz_modelo_query
1248 , '$PERIOD_TO_DATE$'
1249 , 'TO_DATE('''||TO_CHAR(ld_period_end_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1250
1251 :: Introduced conditional filtering ::
1252 Based on report type, FILTER_KEY and FILTER_VALUE will have following values
1253 For, Periodic Report Filter_Key = TAX_CALENDAR_PERIOD and Filter_Value = P_TAX_PERIOD
1254 Annual Report Filter_Key = TAX_CALENDAR_YEAR and Filter_Value = P_TAX_YEAR
1255
1256 */
1257 declare
1258 lv_filter_key varchar2 (150) ;
1259 lv_filter_value varchar2 (30);
1260 begin
1261 if p_report_name = 'JEESPMOR' then
1262 lv_filter_key := ' JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1263 AND JZVRS.TAX_CALENDAR_PERIOD'; --5444803
1264 -- lv_filter_key := ' JZVRS.TAX_CALENDAR_PERIOD'; --5444803
1265 lv_filter_value := p_tax_period;
1266 elsif p_report_name = 'JEESAMOR' then
1267 lv_filter_key := 'JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1268 AND JZVRS.TAX_CALENDAR_YEAR'; --Bug 5525421
1269 lv_filter_value := p_tax_year;
1270 end if;
1271
1272 lc_jgzz_modelo_query :=
1273 REPLACE( lc_jgzz_modelo_query
1274 , '$FILTER_KEY$'
1275 , lv_filter_key
1276 );
1277
1278 lc_jgzz_modelo_query :=
1279 REPLACE( lc_jgzz_modelo_query
1280 , '$FILTER_VALUE$'
1281 , lv_filter_value
1282 );
1283 end;
1284
1285 lc_jgzz_modelo_query :=
1286 REPLACE( lc_jgzz_modelo_query
1287 , '$P_VAT_REP_ENTITY_ID$'
1288 , TO_CHAR(P_VAT_REP_ENTITY_ID)
1289 );
1290
1291 IF p_modelo = '347' THEN
1292
1293 --
1294 -- Replace Place Holder with Modelo 347 - AP Specific Values
1295 --
1296 /** author: brathod
1297 $MODELO_TABLE_LIST$ will have value ", AP_SUPPLIER_SITES APSS , AP_SUPPLIERS APS" [please note comma (,)at begining]
1298 as modelo 347 report needs to filter records based on FEDERAL_REPORTABLE_FLAG for suppliers
1299 and TAX_REPORTING_SITE_FLAG for supplier sites
1300
1301 */
1302 lc_jgzz_modelo_query :=
1303 REPLACE( lc_jgzz_modelo_query
1304 , '$MODELO_TABLE_LIST$'
1305 ,', AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS' );
1306
1307 lc_jgzz_modelo_query :=
1308 REPLACE( lc_jgzz_modelo_query
1309 , '$ADDRESS_ID$'
1310 ,'APSS.VENDOR_SITE_ID' );
1311
1312 lc_jgzz_modelo_query :=
1313 REPLACE( lc_jgzz_modelo_query
1314 , '$TAX_REGISTRATION_NUM$'
1315 -- ,'JZVTD.billing_tp_tax_reg_num' ); /* Commented for bug 5729082 */
1316 ,'JZVTD.billing_tp_taxpayer_id'); /* added for bug 5729082 */
1317
1318 lc_jgzz_modelo_query :=
1319 REPLACE( lc_jgzz_modelo_query
1320 , '$CORRECTION_TRX_SEL_COL$'
1321 , C_CORRECTION_TRX_NULL_COLS );
1322
1323 lc_jgzz_modelo_query :=
1324 REPLACE( lc_jgzz_modelo_query
1325 , '$CORRECTION_TRX_GRP_COL$'
1326 , '' );
1327
1328 lc_jgzz_modelo_query :=
1329 REPLACE( lc_jgzz_modelo_query
1330 , '$MODELO_SPECIFIC_FILTERS$'
1331 , C_MOD347_AP_FILTER );
1332
1333 lc_jgzz_modelo_query :=
1334 REPLACE( lc_jgzz_modelo_query
1335 , '$P_ORG_ID$'
1336 , P_ORG_ID );
1337
1338 lc_jgzz_modelo_query :=
1339 REPLACE( lc_jgzz_modelo_query
1340 , '$MODELO_SPECIFIC_GRP_FILTER$'
1341 , '');
1342 -- , C_MOD347415_GRP_FILTER );
1343
1344 lc_jgzz_modelo_query :=
1345 REPLACE( lc_jgzz_modelo_query
1346 , '$PROPERTY_FLAG_SEL_COL$'
1347 , C_347_SEL_COL );
1348
1349 lc_misc := C_347_SEL_COL ;
1350
1351 lc_misc := REPLACE( lc_misc, 'PROPERTY_RETAIL_FLAG','');
1352
1353 lc_jgzz_modelo_query :=
1354 REPLACE( lc_jgzz_modelo_query
1355 , '$PROPERTY_FLAG_GRP_COL$'
1356 , lc_misc );
1357
1358 IF G_DEBUG THEN
1359 fnd_file.put_line(FND_FILE.LOG,'1.3. bEFORE Merge subquery to inclue expense reports');
1360 END IF;
1361
1362 --
1363 -- Remove semi colon to merge union select
1364 --
1365 lc_jgzz_modelo_query := REPLACE( lc_jgzz_modelo_query, ';', '');
1366
1367 --
1368 -- Merge subquery to inclue expense reports
1369 --
1370
1371 lc_jgzz_modelo_query1 :=
1375 as modelo 347 report needs to filter records based on FEDERAL_REPORTABLE_FLAG for suppliers
1372 'UNION
1373 /** author: brathod
1374 added two tables AP_SUPPLIER_SITES APSS , AP_SUPPLIERS APS
1376 and TAX_REPORTING_SITE_FLAG for supplier sites
1377 */
1378 SELECT /*+ NO_REWRITE */
1379 NVL(JZVTD.merchant_party_taxpayer_id,
1380 JZVTD.billing_tp_taxpayer_id) TAX_REGISTRATION_NUM
1381 , NVL(JZVTD.billing_tp_name,JZVTD.merchant_party_name) CUSTOMER_NAME
1382 , APSS.VENDOR_SITE_ID CUSTOMER_ADDRESS_ID
1383 , JZVTD.bill_from_party_id BILLING_TRADING_PARTNER_ID
1384 , SUM (ROUND (DECODE (JZVTD.taxable_amt_funcl_curr,
1385 0, JZVTD.taxable_amt,
1386 NULL, JZVTD.taxable_amt,
1387 JZVTD.taxable_amt_funcl_curr)
1388 /** author:brathod
1389 +
1390 NVL (DECODE (alc.base_amount,
1391 0, alc.amount,
1392 NULL, alc.amount,
1393 alc.base_amount), 0)
1394 */
1395 )) SUM_TAXABLE_AMT
1396 , 0 SUM_TRX_LINE_AMT
1397 , 0 NCORRECTION_AMOUNT
1398 , 0 CORRECTION_AMOUNT
1399 , NULL
1400 , NULL
1401 , NULL
1402 , NULL
1403 , DECODE (JZVTD.invoice_report_type
1404 , ''347'', ''N''
1405 , ''347PR'', ''Y''
1406 , ''415_347'', ''N''
1407 , ''415_347PR'',''Y'') PROPERTY_RENTAL
1408 FROM jg_zz_vat_rep_status JZVRS
1409 , jg_zz_vat_trx_details JZVTD
1410 , ap_suppliers aps
1411 , ap_supplier_sites_all apss
1412 /**
1413 author:brathod
1414 , ap_invoice_distributions_all AID
1415 , ap_invoice_distributions_all ALC
1416 WHERE AID.invoice_distribution_id = JZVTD.trx_line_id
1417 AND ALC.invoice_distribution_id = AID.charge_applicable_to_dist_id
1418 */
1419 WHERE JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
1420 AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
1421 AND JZVRS.source = ''AP''
1422 AND JZVTD.extract_source_ledger = ''AP''
1423 AND JZVTD.merchant_party_name IS NOT NULL
1424 AND JZVTD.trx_line_type NOT IN (''AWT'',''TAX'',''PREPAY'')
1425 --AND JZVTD.posted_flag IN (''P'',''Y'') /** author:brathod; Removed posted_flag checking as it is not relevent in R12 */
1426 --AND JZVTD.tax_rate_vat_trx_type_code = ''EXPENSE REPORT''
1427 AND JZVTD.trx_line_class = ''EXPENSE REPORT''
1428 --AND NVL(JZVTD.billing_tp_tax_reporting_flag, ''N'') = ''Y''
1429 AND JZVTD.applied_from_line_id IS NULL
1430 /* Commented and added period based filtering
1431 AND JZVTD.start_expense_date BETWEEN $PERIOD_FROM_DATE$ AND $PERIOD_TO_DATE$
1432 */
1433 AND $FILTER_KEY$ = ''$FILTER_VALUE$''
1434 AND JZVTD.invoice_report_type IN ( ''347''
1435 , ''347PR''
1436 , ''415_347''
1437 , ''415_347PR''
1438 )
1439 AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
1440 AND APS.VENDOR_ID = APSS.VENDOR_ID
1441 AND NVL(APS.FEDERAL_REPORTABLE_FLAG ,''Y'') = ''Y''
1442 AND APSS.TAX_REPORTING_SITE_FLAG = ''Y''
1443 AND APSS.ORG_ID = $P_ORG_ID$
1444 GROUP BY NVL(JZVTD.merchant_party_taxpayer_id,
1445 JZVTD.billing_tp_taxpayer_id)
1446 , NVL(JZVTD.billing_tp_name,JZVTD.merchant_party_name)
1447 , APSS.VENDOR_SITE_ID
1448 , JZVTD.bill_from_party_id
1449 , DECODE (JZVTD.invoice_report_type
1450 , ''347'', ''N''
1451 , ''347PR'', ''Y''
1452 , ''415_347'', ''N''
1453 , ''415_347PR'',''Y'')
1454 ';
1455
1456 /**
1457 author : brathod
1458 date : 18/5/2006
1459 Commented following code to remove date filtering.
1460 lc_jgzz_modelo_query :=
1461 REPLACE( lc_jgzz_modelo_query
1462 , '$PERIOD_FROM_DATE$'
1463 , 'TO_DATE('''||TO_CHAR(ld_period_start_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1464
1465 lc_jgzz_modelo_query :=
1466 REPLACE( lc_jgzz_modelo_query
1467 , '$PERIOD_TO_DATE$'
1468 , 'TO_DATE('''||TO_CHAR(ld_period_end_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1469
1470 :: Introduced conditional filtering ::
1471 Based on report type, FILTER_KEY and FILTER_VALUE will have following values
1475 */
1472 For, Periodic Report Filter_Key = TAX_CALENDAR_PERIOD and Filter_Value = P_TAX_PERIOD
1473 Annual Report Filter_Key = TAX_CALENDAR_YEAR and Filter_Value = P_TAX_YEAR
1474
1476 declare
1477 lv_filter_key varchar2 (150) ;
1478 lv_filter_value varchar2 (50);
1479 begin
1480 if p_report_name = 'JEESPMOR' then
1481 lv_filter_key := ' JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1482 AND JZVRS.TAX_CALENDAR_PERIOD'; --5444803
1483 lv_filter_value := p_tax_period;
1484 elsif p_report_name = 'JEESAMOR' then
1485 lv_filter_key := 'JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1486 AND JZVRS.TAX_CALENDAR_YEAR'; --Bug 5525421
1487 lv_filter_value := p_tax_year;
1488 end if;
1489 fnd_file.put_line(FND_FILE.LOG,'lv_filter_key :='||lv_filter_key);
1490 lc_jgzz_modelo_query1 :=
1491 REPLACE( lc_jgzz_modelo_query1
1492 , '$FILTER_KEY$'
1493 , lv_filter_key );
1494
1495 lc_jgzz_modelo_query1 :=
1496 REPLACE( lc_jgzz_modelo_query1
1497 , '$FILTER_VALUE$'
1498 , lv_filter_value );
1499 end;
1500
1501 lc_jgzz_modelo_query1 :=
1502 REPLACE( lc_jgzz_modelo_query1
1503 , '$P_VAT_REP_ENTITY_ID$'
1504 , TO_CHAR(P_VAT_REP_ENTITY_ID) );
1505
1506 lc_jgzz_modelo_query1 :=
1507 REPLACE( lc_jgzz_modelo_query1
1508 , '$P_ORG_ID$'
1509 , P_ORG_ID);
1510
1511 lc_jgzz_modelo_query := lc_jgzz_modelo_query || lc_jgzz_modelo_query1;
1512
1513 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'1.4. After Merge subquery to inclue expense reports'); END IF;
1514
1515
1516 ELSIF p_modelo = '415' THEN
1517
1518 --
1519 -- Replace Place Holder with Modelo 415 - AP Specific Values
1520 --
1521 -- dbms_output.put_line ('Applying palce holders for 415');
1522 /** author: brathod
1523 $MODELO_TABLE_LIST$ will have value ", AP_SUPPLIER_SITES APSS" [please note comma (,)at begining]
1524 as modelo 415 report needs to filter records based on TAX_REPORTING_SITE_FLAG for supplier sites only
1525 */
1526 lc_jgzz_modelo_query :=
1527 REPLACE( lc_jgzz_modelo_query
1528 , '$MODELO_TABLE_LIST$'
1529 ,', AP_SUPPLIER_SITES_ALL APSS' );
1530
1531
1532 lc_jgzz_modelo_query :=
1533 REPLACE( lc_jgzz_modelo_query
1534 , '$ADDRESS_ID$'
1535 ,'APSS.VENDOR_SITE_ID' );
1536
1537 lc_jgzz_modelo_query :=
1538 REPLACE( lc_jgzz_modelo_query
1539 , '$TAX_REGISTRATION_NUM$'
1540 ,'JZVTD.billing_tp_tax_reg_num' );
1541
1542 lc_jgzz_modelo_query :=
1543 REPLACE( lc_jgzz_modelo_query
1544 , '$CORRECTION_TRX_SEL_COL$'
1545 , C_CORRECTION_TRX_NULL_COLS );
1546
1547 lc_jgzz_modelo_query :=
1548 REPLACE( lc_jgzz_modelo_query
1549 , '$CORRECTION_TRX_GRP_COL$'
1550 , '' );
1551
1552 lc_jgzz_modelo_query :=
1553 REPLACE( lc_jgzz_modelo_query
1554 , '$MODELO_SPECIFIC_FILTERS$'
1555 , C_MOD415_AP_FILTER );
1556
1557 lc_jgzz_modelo_query :=
1558 REPLACE( lc_jgzz_modelo_query
1559 , '$P_ORG_ID$'
1560 , P_ORG_ID );
1561
1562 lc_jgzz_modelo_query :=
1563 REPLACE( lc_jgzz_modelo_query
1564 , '$MODELO_SPECIFIC_GRP_FILTER$'
1565 , C_MOD347415_GRP_FILTER );
1566
1567 /** author:brathod
1568 Added following code to use p_min_value parameter */
1569 lc_jgzz_modelo_query :=
1570 REPLACE( lc_jgzz_modelo_query
1571 , '$P_MIN_VALUE$'
1572 , nvl(p_min_value,0));
1573
1574 lc_jgzz_modelo_query :=
1575 REPLACE( lc_jgzz_modelo_query
1576 , '$PROPERTY_FLAG_SEL_COL$'
1577 , C_NULL_SEL_COL );
1578
1579 lc_jgzz_modelo_query :=
1580 REPLACE( lc_jgzz_modelo_query
1581 , '$PROPERTY_FLAG_GRP_COL$'
1582 , '' );
1583 -- dbms_output.put_line ('Completed applying place holders for 415');
1584
1585 ELSIF p_modelo = '349' THEN
1586
1587 --
1588 -- Replace Place Holder with Modelo 349 - AP Specific Values
1589 --
1590 /**
1591 author: brathod
1592 Relacing place holder $MODELO_TABLE_LIST$ with blank space as for 349 AP report it is not used
1593 */
1594 lc_jgzz_modelo_query :=
1595 REPLACE( lc_jgzz_modelo_query
1596 , '$MODELO_TABLE_LIST$'
1597 ,' ' );
1598
1599 lc_jgzz_modelo_query :=
1600 REPLACE( lc_jgzz_modelo_query
1601 , '$ADDRESS_ID$'
1602 ,'NULL' );
1603
1604 lc_jgzz_modelo_query :=
1605 REPLACE( lc_jgzz_modelo_query
1606 , '$TAX_REGISTRATION_NUM$'
1610 REPLACE( lc_jgzz_modelo_query
1607 ,'JZVTD.billing_tp_taxpayer_id' ) ;
1608
1609 lc_jgzz_modelo_query :=
1611 , '$CORRECTION_TRX_SEL_COL$'
1612 , C_CORRECTION_TRX_SEL_COLS );
1613
1614 lc_jgzz_modelo_query :=
1615 REPLACE( lc_jgzz_modelo_query
1616 , '$CORRECTION_TRX_GRP_COL$'
1617 , C_CORRECTION_TRX_GRP_COLS );
1618
1619 lc_jgzz_modelo_query :=
1620 REPLACE( lc_jgzz_modelo_query
1621 , '$MODELO_SPECIFIC_FILTERS$'
1622 , C_MOD349_AP_FILTER );
1623
1624 lc_jgzz_modelo_query :=
1625 REPLACE( lc_jgzz_modelo_query
1626 , '$MODELO_SPECIFIC_GRP_FILTER$'
1627 , '' );
1628
1629 lc_jgzz_modelo_query :=
1630 REPLACE( lc_jgzz_modelo_query
1631 , '$PROPERTY_FLAG_SEL_COL$'
1632 , C_NULL_SEL_COL );
1633
1634 lc_jgzz_modelo_query :=
1635 REPLACE( lc_jgzz_modelo_query
1636 , '$PROPERTY_FLAG_GRP_COL$'
1637 , '' );
1638
1639
1640 END IF;
1641
1642 --
1643 -- Insert AP Transactions in Global Temporary Table
1644 --
1645
1646 -- INSERT INTO JG_TEMP_TABLE values (Lc_jgzz_modelo_query); -- debug
1647
1648 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'1.5. Open the Dynamic Query: '||lc_jgzz_modelo_query ); END IF;
1649
1650 OPEN c_modelo_ext FOR lc_jgzz_modelo_query ; -- opencur
1651 LOOP
1652
1653 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'1.6. Fetch from the Dynamic Query'); END IF;
1654 G_LINENO := '1.6.1';
1655 FETCH c_modelo_ext
1656 INTO lc_tax_registration_number
1657 ,lc_customer_name
1658 ,lc_customer_address_id
1659 ,lc_billing_trading_partner_id
1660 ,ln_sum_taxable_amt
1661 ,ln_sum_trx_line_amt
1662 ,ln_ncorrection_amount
1663 ,ln_correction_amount
1664 ,lc_correction_year
1665 ,lc_correction_period
1666 ,lc_triangulation
1667 ,lc_trx_period -- bug 5525421
1668 ,lc_property_retail_flag
1669 ;
1670
1671 EXIT WHEN c_modelo_ext%NOTFOUND;
1672
1673 G_LINENO := '1.6.2';
1674 IF P_MODELO = '415' THEN
1675
1676 -- ln_sum_taxable_amt will have the gross amt for AP
1677 -- Assign NULL to ln_sum_trx_line_amt
1678 ln_sum_trx_line_amt := NULL;
1679
1680 get_vendor_address ( p_party_site_id => lc_customer_address_id
1681 , x_postal_code => lc_postal_code
1682 , x_city => lc_city
1683 , x_address_detail => lc_address_detail
1684 );
1685
1686
1687
1688 lc_clave_operation := 'C';
1689 lc_tax_registration_number := SUBSTR( lc_tax_registration_number,1,9);
1690 lc_customer_name := SUBSTR(lc_customer_name,1,40);
1691 lc_address_detail := SUBSTR(lc_address_detail,1,32);
1692
1693 G_LINENO := '1.6.2.1';
1694
1695 IF G_DEBUG THEN
1696 fnd_file.put_line(FND_FILE.LOG,'lc_address_detail='||lc_address_detail);
1697 fnd_file.put_line(FND_FILE.LOG,'lc_postal_code='||lc_postal_code);
1698 fnd_file.put_line(FND_FILE.LOG,'lc_city='||lc_city);
1699 END IF;
1700
1701 G_LINENO := '1.6.2.2';
1702
1703 ln_sum_taxable_amt :=
1704 AP415_GROSS_AMOUNTFormula ( p_vendor_id => lc_billing_trading_partner_id
1705 , p_gross_amount => ln_sum_taxable_amt );
1706
1707
1708 ELSIF P_MODELO = '347' THEN
1709
1710 G_LINENO := '1.6.2.3';
1711 -- ln_sum_taxable_amt will have the gross amt for AP
1712 -- Assign NULL to ln_sum_trx_line_amt
1713 ln_sum_trx_line_amt := NULL;
1714
1715 lc_clave_operation := 'A' ;
1716 G_LINENO := '1.6.2.4';
1717 get_vendor_address ( p_party_site_id => lc_customer_address_id
1718 , x_postal_code => lc_postal_code
1719 , x_city => lc_city
1720 , x_address_detail => lc_address_detail
1721 );
1722
1723 G_LINENO := '1.6.2.5';
1724 ln_sum_taxable_amt :=
1725 AP347_GROSS_AMOUNTFormula ( p_vendor_id => lc_billing_trading_partner_id
1726 , p_property_flag => lc_property_retail_flag
1727 , p_gross_amt => ln_sum_taxable_amt);
1728 G_LINENO := '1.6.2.6';
1729
1730 ELSIF P_MODELO = '349' THEN
1731
1732 G_LINENO := '1.6.2.7';
1733 IF P_REPORT_NAME = 'JEESAMOR' THEN
1734 G_LINENO := '1.6.2.7.1';
1735 ln_running_total := 0;
1736 ln_formerly_decl_amt := 0;
1737 lc_tax_registration_number := SUBSTR( lc_tax_registration_number,1,14);
1741 G_LINENO := '1.6.2.7.2';
1738 lc_customer_name := SUBSTR(lc_customer_name,1,40);
1739 lc_print_year := P_TAX_YEAR; -- Bug 5525421
1740 lc_print_period := lc_trx_period; -- Bug 5525421
1742
1743 -- Bug 5525421
1744
1745
1746 IF lc_correction_year IS NULL AND lc_correction_period IS NULL THEN --Not a Correction
1747
1748 G_LINENO := '1.6.2.7.2.1';
1749 lc_clave_operation := 'A';
1750 ln_base_imponiable := ln_ncorrection_amount;
1751 ln_running_total := ln_ncorrection_amount;
1752
1753
1754
1755 ELSIF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
1756 G_LINENO := '1.6.2.7.2.2';
1757 lc_clave_operation := 'a';
1758 lc_correction_year := lc_correction_year; --SUBSTR(lc_correction_year,3,2);
1759 ln_base_imponiable := ln_correction_amount;
1760 ln_formerly_decl_amt := 0;
1761 G_LINENO := '1.6.2.7.2.3';
1762 -- SELECT DECODE(lc_triangulation,'X','T','E') --Bug 5525421: No traingulation for AP.
1763 -- INTO lc_clave_operation
1764 -- FROM DUAL ;
1765 G_LINENO := '1.6.2.7.2.4';
1766 IF lc_clave_operation IN ('1','2') THEN
1767 lc_sign := ' ';
1768 ELSE
1769 IF ln_base_imponiable < 0 THEN
1770 lc_sign := '-' ;
1771 ELSE
1772 lc_sign := '+' ;
1773 END IF;
1774 END IF;
1775 G_LINENO := '1.6.2.7.2.5';
1776
1777 begin
1778 SELECT NVL(jg_info_n10,0) -- running total
1779 INTO ln_formerly_decl_amt
1780 FROM JG_ZZ_VAT_TRX_GT M349
1781 WHERE RTRIM(jg_info_v11) = RTRIM(lc_tax_registration_number)
1782 AND RTRIM(jg_info_v12) = RTRIM(lc_customer_name)
1783 AND jg_info_n13 = lc_correction_year -- correction year
1784 AND jg_info_v27 = lc_correction_period -- correction period
1785 -- AND RTRIM(jg_info_v21) = RTRIM(lc_correction_year) --Bug 5525421
1786 AND UPPER(jg_info_v1) ='A';
1787
1788 exception
1789 when no_data_found then
1790 ln_formerly_decl_amt :=0;
1791 end;
1792
1793 G_LINENO := '1.6.2.7.2.6';
1794 --
1795 -- update running total
1796 --
1797 -- As per R11i logic this update should happend after the insert. Hence moving this to after insert.
1798
1799 /* UPDATE JG_ZZ_VAT_TRX_GT
1800 SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
1801 WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year) --RTRIM(SUBSTR(lc_correction_year,3,2))
1802 AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
1803 AND jg_info_v14 IS NOT NULL
1804 AND jg_info_v15 IS NOT NULL
1805 AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
1806 AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
1807 AND UPPER(jg_info_v11) ='A'; */ --Bug 5525421
1808
1809 G_LINENO := '1.6.2.7.2.7';
1810 END IF;
1811
1812 G_LINENO := '1.6.2.7.3';
1813 -- JGZZVEFT(); --Bug 5525421 This procedure should call only once i.e. after inserting AP and AR data in to temp table.
1814 G_LINENO := '1.6.2.7.4';
1815
1816 END IF;
1817 G_LINENO := '1.6.2.8';
1818 END IF;
1819 G_LINENO := '1.6.3';
1820
1821 INSERT INTO JG_ZZ_VAT_TRX_GT
1822 ( jg_info_v1 -- lc_clave_operation lc_clave_operation
1823 , jg_info_v11 -- c_modelo_rec.tax_registration_number lc_tax_registration_number
1824 , jg_info_v12 -- c_modelo_rec.customer_name lc_customer_name
1825 , jg_info_v13 -- c_modelo_rec.customer_address_id lc_customer_address_id
1826 , jg_info_v14 -- c_modelo_rec.billing_trading_partner_id lc_billing_trading_partner
1827 , jg_info_n1 -- c_modelo_rec.sum_taxable_amt -- 347-AP, 415-AP ln_sum_taxable_amt
1828 , jg_info_n2 -- c_modelo_rec.sum_trx_line_amt -- 347-AR,415-AR ln_sum_trx_line_amt
1829 , jg_info_n3 -- c_modelo_rec.ncorrection_amount -- 349 ln_ncorrection_amount
1830 , jg_info_n4 -- c_modelo_rec.correction_amount -- 349 ln_correction_amount
1831 , jg_info_v15 -- c_modelo_rec.correction_year lc_correction_year
1832 , jg_info_v16 -- c_modelo_rec.correction_period lc_correction_period
1833 , jg_info_v17 -- c_modelo_rec.triangulation lc_triangulation
1834 , jg_info_v21 -- c_modelo_rec.property_retail_flag lc_property_retail_flag
1835 , jg_info_v18 -- lc_postal_code lc_postal_code
1839 , jg_info_n11 -- ln_formerly_decl_amt ln_formerly_decl_amt
1836 , jg_info_v19 -- lc_city lc_city
1837 , jg_info_v20 -- lc_address_detail lc_address_detail
1838 , jg_info_n10 -- ln_running_total ln_running_total
1840 , jg_info_n12 -- ln_base_imponiable ln_base_imponiable
1841 , jg_info_v22 -- p_print_year --p_tax_calender_year P_TAX_YEAR
1842 , jg_info_v23 -- legal entity name G_LE_NAME
1843 , jg_info_v26 -- G_LE_TRN -- 347 G_LE_TRN
1844 , jg_info_v24 -- lc_sign lc_sign
1845 , jg_info_v25 -- p_tax_office -- 347 p_tax_office
1846 , jg_info_n13 -- lc_prtint_year --Bug 5525421
1847 , jg_info_v27 -- lc_print_period --Bug 5525421
1848 )
1849 VALUES
1850 ( lc_clave_operation
1851 , lc_tax_registration_number
1852 , lc_customer_name
1853 , lc_customer_address_id
1854 , lc_billing_trading_partner_id
1855 , ln_sum_taxable_amt
1856 , ln_sum_trx_line_amt
1857 , ln_ncorrection_amount
1858 , ln_correction_amount
1859 , lc_correction_year
1860 , lc_correction_period
1861 , lc_triangulation
1862 , lc_property_retail_flag
1863 , lc_postal_code
1864 , lc_city
1865 , lc_address_detail
1866 , ln_running_total
1867 , ln_formerly_decl_amt
1868 , ln_base_imponiable
1869 , P_TAX_YEAR
1870 , G_LE_NAME
1871 , G_LE_TRN
1872 , lc_sign
1873 , p_tax_office
1874 , lc_print_year --Bug 5525421
1875 , lc_print_period --Bug 5525421
1876 );
1877
1878 ----Bug 5525421 Updation should haapend only after insertintg the correction reocrd.
1879
1880 IF P_MODELO = '349' THEN
1881 IF P_REPORT_NAME = 'JEESAMOR' THEN
1882 IF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
1883
1884 UPDATE JG_ZZ_VAT_TRX_GT
1885 SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
1886 WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year) --RTRIM(SUBSTR(lc_correction_year,3,2))
1887 AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
1888 AND jg_info_v14 IS NOT NULL
1889 AND jg_info_v15 IS NOT NULL
1890 AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
1891 AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
1892 AND UPPER(jg_info_v11) ='A';
1893
1894 END IF;
1895 END IF;
1896 END IF;
1897
1898 G_LINENO := '1.6.4';
1899 END LOOP;
1900 G_LINENO := '1.7';
1901 CLOSE c_modelo_ext; -- closecur
1902 G_LINENO := '1.8';
1903
1904 END IF;
1905
1906
1907 G_LINENO := '2';
1908
1909 IF ( P_REPORT_NAME = 'JEESPMOR' AND P_SOURCE = 'AR')
1910 OR P_REPORT_NAME = 'JEESAMOR' THEN
1911
1912 G_LINENO := '2.1';
1913
1914 --
1918 --
1915 -- Build query to fetch ap extract for modelo from JG
1916 --
1917 lc_jgzz_modelo_query := C_JGZZ_MODELO_GENRIC_QUERY;
1919 /**
1920 author : brathod
1921 date : 18/5/2006
1922 Commented following code to remove date filtering.
1923 REPLACE( lc_jgzz_modelo_query
1924 , '$PERIOD_FROM_DATE$'
1925 , 'TO_DATE('''||TO_CHAR(ld_period_start_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1926
1927 lc_jgzz_modelo_query :=
1928 REPLACE( lc_jgzz_modelo_query
1929 , '$PERIOD_TO_DATE$'
1930 , 'TO_DATE('''||TO_CHAR(ld_period_end_date, 'DD/MM/YYYY')||''',''DD/MM/YYYY'')' );
1931
1932 :: Introduced conditional filtering ::
1933 Based on report type, FILTER_KEY and FILTER_VALUE will have following values
1934 For, Periodic Report Filter_Key = TAX_CALENDAR_PERIOD and Filter_Value = P_TAX_PERIOD
1935 Annual Report Filter_Key = TAX_CALENDAR_YEAR and Filter_Value = P_TAX_YEAR
1936
1937 */
1938 G_LINENO := '2.2';
1939
1940 declare
1941 lv_filter_key varchar2 (150) ;
1942 lv_filter_value varchar2 (50) ;
1943 begin
1944 if p_report_name = 'JEESPMOR' then
1945 lv_filter_key := ' JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1946 AND JZVRS.TAX_CALENDAR_PERIOD'; --5444803
1947 -- lv_filter_key := 'JZVRS.TAX_CALENDAR_PERIOD'; --5444803
1948 lv_filter_value := p_tax_period;
1949 elsif p_report_name = 'JEESAMOR' then
1950 lv_filter_key := 'JZVTD.trx_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
1951 AND JZVRS.TAX_CALENDAR_YEAR'; -- Bug 5525421
1952 lv_filter_value := p_tax_year;
1953 end if;
1954
1955 lc_jgzz_modelo_query :=
1956 REPLACE( lc_jgzz_modelo_query
1957 , '$FILTER_KEY$'
1958 , lv_filter_key
1959 );
1960
1961 lc_jgzz_modelo_query :=
1962 REPLACE( lc_jgzz_modelo_query
1963 , '$FILTER_VALUE$'
1964 , lv_filter_value
1965 );
1966 end;
1967
1968 lc_jgzz_modelo_query :=
1969 REPLACE( lc_jgzz_modelo_query
1970 , '$P_VAT_REP_ENTITY_ID$'
1971 , TO_CHAR(P_VAT_REP_ENTITY_ID) );
1972
1973 G_LINENO := '2.3';
1974
1975 /**
1976 author: brathod
1977 Relacing place holder $MODELO_TABLE_LIST$ with blank space as for AR report its not in use
1978 */
1979
1980 IF P_MODELO = '347' THEN -- Modelo 347
1981
1982 --
1983 -- Replace Place Holder with Modelo 347 - AR Specific Values
1984 --
1985
1986
1987 G_LINENO := '2.3.1';
1988
1989 lc_jgzz_modelo_query :=
1990 REPLACE( lc_jgzz_modelo_query
1991 , '$MODELO_TABLE_LIST$'
1992 ,', hz_cust_site_uses_all hzcsu ,hz_cust_acct_sites_all hzcas ,hz_cust_accounts hzca ' );
1993
1994
1995 lc_jgzz_modelo_query :=
1996 REPLACE( lc_jgzz_modelo_query
1997 , '$ADDRESS_ID$'
1998 ,'hzcsu.cust_acct_site_id');
1999
2000
2001 lc_jgzz_modelo_query :=
2002 REPLACE( lc_jgzz_modelo_query
2003 , '$TAX_REGISTRATION_NUM$'
2004 -- ,'JZVTD.billing_tp_tax_reg_num' ); /* Commented for bug 5729082 */
2005 , 'JZVTD.billing_tp_taxpayer_id'); /* Added for bug 5729082 */
2006
2007 lc_jgzz_modelo_query :=
2008 REPLACE( lc_jgzz_modelo_query
2009 , '$CORRECTION_TRX_SEL_COL$'
2010 , C_CORRECTION_TRX_NULL_COLS );
2011
2012 lc_jgzz_modelo_query :=
2013 REPLACE( lc_jgzz_modelo_query
2014 , '$CORRECTION_TRX_GRP_COL$'
2015 , '' );
2016
2017 lc_jgzz_modelo_query :=
2018 REPLACE( lc_jgzz_modelo_query
2019 , '$MODELO_SPECIFIC_FILTERS$'
2020 , C_MOD347_AR_FILTER );
2021
2022 lc_jgzz_modelo_query :=
2023 REPLACE( lc_jgzz_modelo_query
2024 ,'$P_ORG_ID$'
2025 ,P_ORG_ID );
2026
2027 lc_jgzz_modelo_query :=
2028 REPLACE( lc_jgzz_modelo_query
2029 , '$MODELO_SPECIFIC_GRP_FILTER$'
2030 , C_MOD347415_GRP_FILTER );
2031
2032 G_LINENO := '2.3.2';
2033 /**
2034 author:brathod
2035 Added following code to use p_min_value parameter
2036 */
2037 lc_jgzz_modelo_query :=
2038 REPLACE( lc_jgzz_modelo_query
2039 , '$P_MIN_VALUE$'
2040 , nvl(p_min_value,0));
2041
2042 lc_jgzz_modelo_query :=
2043 REPLACE( lc_jgzz_modelo_query
2044 , '$PROPERTY_FLAG_SEL_COL$'
2045 , C_NULL_SEL_COL );
2046
2047 lc_jgzz_modelo_query :=
2048 REPLACE( lc_jgzz_modelo_query
2049 , '$PROPERTY_FLAG_GRP_COL$'
2050 , '' );
2051
2052 ELSIF P_MODELO = '415' THEN -- Modelo 415
2056 --
2053
2054 --
2055 -- Replace Place Holder with Modelo 415 - AR Specific Values
2057
2058 lc_jgzz_modelo_query :=
2059 REPLACE( lc_jgzz_modelo_query
2060 , '$MODELO_TABLE_LIST$'
2061 ,', hz_cust_site_uses_all hzcsu ,hz_cust_acct_sites_all hzcas ,hz_cust_accounts hzca ' );
2062
2063
2064 lc_jgzz_modelo_query :=
2065 REPLACE( lc_jgzz_modelo_query
2066 , '$ADDRESS_ID$'
2067 ,'hzcsu.cust_acct_site_id');
2068
2069 G_LINENO := '2.3.3';
2070 lc_jgzz_modelo_query :=
2071 REPLACE( lc_jgzz_modelo_query
2072 , '$TAX_REGISTRATION_NUM$'
2073 ,'JZVTD.billing_tp_tax_reg_num' );
2074
2075 lc_jgzz_modelo_query :=
2076 REPLACE( lc_jgzz_modelo_query
2077 , '$CORRECTION_TRX_SEL_COL$'
2078 , C_CORRECTION_TRX_NULL_COLS );
2079
2080 lc_jgzz_modelo_query :=
2081 REPLACE( lc_jgzz_modelo_query
2082 , '$CORRECTION_TRX_GRP_COL$'
2083 , '' );
2084
2085 lc_jgzz_modelo_query :=
2086 REPLACE( lc_jgzz_modelo_query
2087 , '$MODELO_SPECIFIC_FILTERS$'
2088 , C_MOD415_AR_FILTER );
2089
2090 lc_jgzz_modelo_query :=
2091 REPLACE( lc_jgzz_modelo_query
2092 ,'$P_ORG_ID$'
2093 ,P_ORG_ID );
2094
2095 lc_jgzz_modelo_query :=
2096 REPLACE( lc_jgzz_modelo_query
2097 , '$MODELO_SPECIFIC_GRP_FILTER$'
2098 , C_MOD347415_GRP_FILTER);
2099
2100 /**
2101 author:brathod
2102 Added following code to use p_min_value parameter
2103 */
2104 lc_jgzz_modelo_query :=
2105 REPLACE( lc_jgzz_modelo_query
2106 , '$P_MIN_VALUE$'
2107 , nvl(p_min_value,0));
2108
2109 lc_jgzz_modelo_query :=
2110 REPLACE( lc_jgzz_modelo_query
2111 , '$PROPERTY_FLAG_SEL_COL$'
2112 , C_NULL_SEL_COL );
2113
2114 lc_jgzz_modelo_query :=
2115 REPLACE( lc_jgzz_modelo_query
2116 , '$PROPERTY_FLAG_GRP_COL$'
2117 , '' );
2118 G_LINENO := '2.3.4';
2119 ELSIF P_MODELO = '349' THEN -- Modelo 349
2120
2121 --
2122 -- Replace Place Holder with Modelo 349 - AR Specific Values
2123 --
2124 G_LINENO := '2.3.5';
2125
2126 lc_jgzz_modelo_query :=
2127 REPLACE( lc_jgzz_modelo_query
2128 , '$MODELO_TABLE_LIST$'
2129 ,'' );
2130
2131
2132 lc_jgzz_modelo_query :=
2133 REPLACE( lc_jgzz_modelo_query
2134 , '$ADDRESS_ID$'
2135 ,'NULL');
2136
2137
2138 lc_jgzz_modelo_query :=
2139 REPLACE( lc_jgzz_modelo_query
2140 , '$TAX_REGISTRATION_NUM$'
2141 ,'JZVTD.billing_tp_taxpayer_id' ) ;
2142
2143 lc_jgzz_modelo_query :=
2144 REPLACE( lc_jgzz_modelo_query
2145 , '$CORRECTION_TRX_SEL_COL$'
2146 , C_CORRECTION_TRX_SEL_COLS );
2147
2148 lc_jgzz_modelo_query :=
2149 REPLACE( lc_jgzz_modelo_query
2150 , '$CORRECTION_TRX_GRP_COL$'
2151 , C_CORRECTION_TRX_GRP_COLS );
2152
2153 /**
2154 author: brathod
2155 If the report is Annual Modelo AR (349) - JEESVEFT, then adding extract filtering
2156 conditions to support multi period reporting.
2157 */
2158 if p_report_name = 'JEESAMOR' then -- Annual Report
2159
2160 declare
2161
2162 ld_start_date gl_periods.start_date%type;
2163 ld_end_date gl_periods.end_date%type;
2164 lv_tax_calendar jg_zz_vat_rep_entities.tax_calendar_name%type;
2165
2166 /** Fetch the tax accounting calendar from Reporting Entity Setup */
2167 cursor c_get_tax_calendar
2168 is
2169 select tax_calendar_name
2170 from jg_zz_vat_rep_entities
2171 where vat_reporting_entity_id = p_vat_rep_entity_id;
2172
2173 /**
2174 Derrive start date and end date using period range parameters P_FROM_PERIOD and P_TO_PERIOD
2175 */
2176 cursor c_get_start_end_dates
2177 is
2178 select min(start_date), max(end_date)
2179 from gl_periods
2180 where period_set_name = lv_tax_calendar
2181 and period_name in (P_FROM_PERIOD, P_TO_PERIOD);
2182
2183 begin
2184
2185 open c_get_tax_calendar;
2186 fetch c_get_tax_calendar into lv_tax_calendar;
2187 close c_get_tax_calendar;
2188
2189 open c_get_start_end_dates;
2190 fetch c_get_start_end_dates into ld_start_date, ld_end_date;
2191 close c_get_start_end_dates;
2192
2196 || ' AND JZVRS.PERIOD_END_DATE <= ''' || ld_end_date || ''''
2193 /** Append the filter conditions for multi period reporting */
2194 --Bug 5525421 Commented. Not required these filters.
2195 /** C_MOD349_AR_FILTER := C_MOD349_AR_FILTER || ' AND JZVRS.PERIOD_START_DATE >= ''' || ld_start_date || ''''
2197 || ' AND JZVRS.TAX_CALENDAR_NAME = ''' || lv_tax_calendar || ''''; */
2198 end;
2199
2200 end if;
2201
2202 lc_jgzz_modelo_query :=
2203 REPLACE( lc_jgzz_modelo_query
2204 , '$MODELO_SPECIFIC_FILTERS$'
2205 , C_MOD349_AR_FILTER );
2206
2207 lc_jgzz_modelo_query :=
2208 REPLACE( lc_jgzz_modelo_query
2209 , '$MODELO_SPECIFIC_GRP_FILTER$'
2210 , '' );
2211
2212 lc_jgzz_modelo_query :=
2213 REPLACE( lc_jgzz_modelo_query
2214 , '$PROPERTY_FLAG_SEL_COL$'
2215 , C_NULL_SEL_COL );
2216
2217 lc_jgzz_modelo_query :=
2218 REPLACE( lc_jgzz_modelo_query
2219 , '$PROPERTY_FLAG_GRP_COL$'
2220 , '' );
2221 G_LINENO := '2.3.6';
2222
2223 END IF;
2224 G_LINENO := '2.4';
2225 -- Insert AR Transations into Global Temp Table
2226 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'Modelo SQL Statement: '||lc_jgzz_modelo_query); END IF;
2227
2228 OPEN c_modelo_ext FOR lc_jgzz_modelo_query ;
2229 LOOP
2230 FETCH c_modelo_ext
2231 INTO lc_tax_registration_number
2232 ,lc_customer_name
2233 ,lc_customer_address_id
2234 ,lc_billing_trading_partner_id
2235 ,ln_sum_taxable_amt
2236 ,ln_sum_trx_line_amt
2237 ,ln_ncorrection_amount
2238 ,ln_correction_amount
2239 ,lc_correction_year
2240 ,lc_correction_period
2241 ,lc_triangulation
2242 ,lc_trx_period -- Bug 5525421
2243 ,lc_property_retail_flag
2244 ;
2245 IF G_DEBUG THEN
2246 fnd_file.put_line(FND_FILE.LOG,'lc_customer_name='||lc_customer_name||',lc_customer_address_id='||lc_customer_address_id);
2247 END IF;
2248
2249 EXIT WHEN c_modelo_ext%NOTFOUND;
2250
2251 IF P_MODELO = '415' THEN
2252 G_LINENO := '2.4.1';
2253 -- ln_sum_trx_line_amt will have the gross amt for AR
2254 -- Assign NULL to ln_sum_taxable_amt
2255 ln_sum_taxable_amt := NULL;
2256 G_LINENO := '2.4.2';
2257 get_customer_address ( p_customer_address_id => lc_customer_address_id
2258 , x_postal_code => lc_postal_code
2259 , x_city => lc_city
2260 , x_address_detail => lc_address_detail
2261 );
2262
2263 G_LINENO := '2.4.3';
2264 lc_clave_operation := 'V';
2265 lc_tax_registration_number := SUBSTR( lc_tax_registration_number,1,9);
2266 lc_customer_name := SUBSTR(lc_customer_name,1,40);
2267 lc_address_detail := SUBSTR(lc_address_detail,1,32);
2268 G_LINENO := '2.4.4';
2269
2270 ELSIF P_MODELO = '347' THEN
2271
2272 lc_clave_operation := 'B' ;
2273 G_LINENO := '2.4.5';
2274 get_customer_address2
2275 ( p_customer_address_id => lc_customer_address_id
2276 , x_postal => lc_postal_code
2277 , x_city => lc_city
2278 , x_street_type => lc_street_type -- sigla
2279 , x_street => lc_street --via_publica
2280 , x_number => lc_number
2281 );
2282 G_LINENO := '2.4.6';
2283 ln_arrenda_amount := AR347_GROSS_AMOUNTFormula
2284 ( p_customer_id => lc_billing_trading_partner_id
2285 );
2286 fnd_file.put_line(FND_FILE.LOG,'2.4.6 ln_arrenda_amount :='||ln_arrenda_amount);
2287
2288 G_LINENO := '2.4.7';
2289 -- dbms_output.put_line ('ln_sum_trx_line_amt='||ln_sum_trx_line_amt||', ln_arrenda_amount =' || ln_arrenda_amount );
2290 IF nvl(ln_arrenda_amount,0) <> 0 THEN
2291
2292 G_LINENO := '2.4.7.1';
2293 ln_sum_trx_line_amt := ln_sum_trx_line_amt - ln_arrenda_amount ;
2294
2295 INSERT INTO JG_ZZ_VAT_TRX_GT
2296 ( jg_info_v1 -- lc_clave_operation
2297 , jg_info_n2 -- ln_arrenda_amount
2298 , jg_info_v11 -- lc_tax_registration_number
2299 , jg_info_v12 -- c_modelo_rec.customer_name
2300 , jg_info_v13 -- c_modelo_rec.customer_address_id
2301 , jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
2302 , jg_info_v21 -- c_modelo_rec.property_retail_flag -- flag_arrenda
2303 , jg_info_v18 -- lc_postal_code
2304 , jg_info_v19 -- lc_city -- x_ref_catastral
2308 , jg_info_v27 -- fin_id -- 'S'
2305 , jg_info_v20 -- lc_number -- x_numero
2306 , jg_info_v25 -- lc_street_type
2307 , jg_info_v26 -- lc_street
2309 , jg_info_v22 -- p_print_year --p_tax_calender_year
2310 )
2311 VALUES
2312 ( lc_clave_operation
2313 , ln_arrenda_amount
2314 , lc_tax_registration_number
2315 , lc_customer_name
2316 , lc_customer_address_id
2317 , lc_billing_trading_partner_id
2318 , 'Y'
2319 , lc_postal_code
2320 , lc_city
2321 , lc_number
2322 , lc_street_type
2323 , lc_street
2324 , 'S'
2325 , p_tax_year
2326 ) ;
2327 G_LINENO := '2.4.7.2';
2328 arrenda ( p_vat_rep_entity_id => p_vat_rep_entity_id
2329 , p_customer_id => lc_billing_trading_partner_id
2330 , p_customer_name => lc_customer_name
2331 , p_cust_tax_reg_num => lc_tax_registration_number
2332 );
2333 G_LINENO := '2.4.7.3';
2334 END IF;
2335 G_LINENO := '2.4.8';
2336 if ln_sum_trx_line_amt >0 then
2337 G_LINENO := '2.4.8.1';
2338 INSERT INTO JG_ZZ_VAT_TRX_GT
2339 ( jg_info_v1 -- lc_clave_operation
2340 , jg_info_n2 -- ln_sum_trx_line_amt
2341 , jg_info_v11 -- lc_tax_registration_number
2342 , jg_info_v12 -- c_modelo_rec.customer_name
2343 , jg_info_v13 -- c_modelo_rec.customer_address_id
2344 , jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
2345 , jg_info_v21 -- c_modelo_rec.property_retail_flag -- flag_arrenda
2346 , jg_info_v18 -- lc_postal_code
2347 , jg_info_v19 -- lc_city -- x_ref_catastral
2348 , jg_info_v20 -- lc_number -- x_numero
2349 , jg_info_v25 -- lc_street_type
2350 , jg_info_v26 -- lc_street
2351 , jg_info_v27 -- fin_id -- 'S'
2352 , jg_info_v22 -- p_print_year --p_tax_calender_year
2353 , jg_info_v23 -- legal entity TRN
2354 )
2355 VALUES
2356 ( lc_clave_operation
2357 , ln_sum_trx_line_amt
2358 , lc_tax_registration_number
2359 , lc_customer_name
2360 , lc_customer_address_id
2361 , lc_billing_trading_partner_id
2362 , 'N'
2363 , lc_postal_code
2364 , lc_city
2365 , lc_number
2366 , lc_street_type
2367 , lc_street
2368 , 'S'
2369 , p_tax_year
2370 , G_LE_TRN
2371 ) ;
2372 G_LINENO := '2.4.8.2';
2373 end if;
2374 G_LINENO := '2.4.9';
2375 ELSIF P_MODELO = '349' THEN
2376 G_LINENO := '2.4.10';
2377 IF P_REPORT_NAME = 'JEESAMOR' THEN
2378 G_LINENO := '2.4.10.1';
2379 ln_running_total := 0;
2380 ln_formerly_decl_amt := 0;
2381 lc_tax_registration_number := SUBSTR( lc_tax_registration_number,1,14);
2382 lc_customer_name := SUBSTR(lc_customer_name,1,40);
2383 lc_print_year := P_TAX_YEAR; --Bug 5525421
2384 lc_print_period := lc_trx_period; --Bug 5525421
2385 G_LINENO := '2.4.10.2';
2386
2387 IF lc_correction_year IS NULL AND lc_correction_period IS NULL THEN --Not a Correction
2388
2389 ln_base_imponiable := ln_ncorrection_amount;
2390 ln_running_total := ln_ncorrection_amount;
2391 G_LINENO := '2.4.10.2.1';
2392 SELECT DECODE(lc_triangulation,'X','T','E')
2393 INTO lc_clave_operation
2394 FROM DUAL ;
2395 G_LINENO := '2.4.10.2.2';
2396
2397 ELSIF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
2398 G_LINENO := '2.4.10.2.3';
2399 ln_formerly_decl_amt := 0;
2400 lc_correction_year := lc_correction_year; --SUBSTR(lc_correction_year,3,2);
2401 ln_base_imponiable := ln_correction_amount;
2402 G_LINENO := '2.4.10.2.4';
2403 SELECT DECODE(lc_triangulation,'X','t','e') -- Bug 5525421
2404 INTO lc_clave_operation
2405 FROM DUAL ;
2406 G_LINENO := '2.4.10.2.5';
2407 IF lc_clave_operation IN ('1','2') THEN
2408 lc_sign := ' ';
2409 ELSE
2410 IF ln_base_imponiable < 0 THEN
2411 lc_sign := '-' ;
2412 ELSE
2413 lc_sign := '+' ;
2414 END IF;
2418 begin
2415 END IF;
2416 G_LINENO := '2.4.10.2.6';
2417
2419 SELECT NVL(jg_info_n10,0) -- running total
2420 INTO ln_formerly_decl_amt
2421 FROM JG_ZZ_VAT_TRX_GT M349
2422 WHERE RTRIM(jg_info_v11) = RTRIM(lc_tax_registration_number)
2423 AND RTRIM(jg_info_v12) = RTRIM(lc_customer_name)
2424 AND jg_info_n13 = lc_correction_year -- correction year -- Bug 5525421
2425 AND jg_info_v27 = lc_correction_period -- correction period -- Bug 5525421
2426 -- AND RTRIM(jg_info_v21) = RTRIM(lc_correction_year) -- Bug 5525421
2427 AND UPPER(jg_info_v1) = upper(DECODE(lc_triangulation,'X','T','E'));
2428
2429 exception
2430 when no_data_found then
2431 ln_formerly_decl_amt :=0;
2432 end;
2433 G_LINENO := '2.4.10.2.7';
2434 --
2435 -- update running total
2436 -- -- Bug 5525421 As per R11i logic this update should happend after the insert. Hence moving this to after insert.
2437 /* UPDATE JG_ZZ_VAT_TRX_GT
2438 SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
2439 WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year)--RTRIM(SUBSTR(lc_correction_year,3,2))
2440 AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
2441 AND jg_info_v15 IS NOT NULL
2442 AND jg_info_v16 IS NOT NULL
2443 AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
2444 AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
2445 AND UPPER(jg_info_v11) = upper(DECODE(lc_triangulation,'X','T','E')); */
2446 G_LINENO := '2.4.10.2.8';
2447 END IF;
2448 G_LINENO := '2.4.10.3 ';
2449 -- JGZZVEFT(); -- Bug 5525421
2450 G_LINENO := '2.4.10.4';
2451 END IF;
2452 G_LINENO := '2.4.11';
2453 END IF;
2454 G_LINENO := '2.5';
2455
2456 IF P_MODELO <> '347' THEN
2457 G_LINENO := '2.5.1';
2458 INSERT INTO JG_ZZ_VAT_TRX_GT
2459 ( jg_info_v1 -- lc_clave_operation
2460 , jg_info_v11 -- c_modelo_rec.tax_registration_number
2461 , jg_info_v12 -- c_modelo_rec.customer_name
2462 , jg_info_v13 -- c_modelo_rec.customer_address_id
2463 , jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
2464 , jg_info_n1 -- c_modelo_rec.sum_taxable_amt -- 347-AP, 415-AP
2465 , jg_info_n2 -- c_modelo_rec.sum_trx_line_amt -- 347-AR, 415-AR
2466 , jg_info_n3 -- c_modelo_rec.ncorrection_amount -- 349
2467 , jg_info_n4 -- c_modelo_rec.correction_amount -- 349
2468 , jg_info_v15 -- c_modelo_rec.correction_year
2469 , jg_info_v16 -- c_modelo_rec.correction_period
2470 , jg_info_v17 -- c_modelo_rec.triangulation
2471 , jg_info_v21 -- c_modelo_rec.property_retail_flag
2472 , jg_info_v18 -- lc_postal_code
2473 , jg_info_v19 -- lc_city -- x_ref_catastral
2474 , jg_info_v20 -- lc_address_detail -- x_numero
2475 , jg_info_n10 -- ln_running_total
2476 , jg_info_n11 -- ln_formerly_decl_amt
2477 , jg_info_n12 -- ln_base_imponiable
2478 , jg_info_v22 -- p_print_year --p_tax_calender_year
2479 , jg_info_v23 -- legal entity name
2480 , jg_info_v26 -- G_LE_TRN -- 347
2481 , jg_info_v24 -- lc_sign
2482 , jg_info_v25 -- p_tax_office -- 347
2483 , jg_info_n13 -- lc_prtint_year -- Bug 5525421
2484 , jg_info_v27 -- lc_print_period -- Bug 5525421
2485 )
2486 VALUES
2487 ( lc_clave_operation
2488 , lc_tax_registration_number
2489 , lc_customer_name
2490 , lc_customer_address_id
2491 , lc_billing_trading_partner_id
2492 , ln_sum_taxable_amt
2493 , ln_sum_trx_line_amt
2494 , ln_ncorrection_amount
2495 , ln_correction_amount
2496 , lc_correction_year
2497 , lc_correction_period
2498 , lc_triangulation
2499 , lc_property_retail_flag
2500 , lc_postal_code
2501 , lc_city
2502 , lc_address_detail
2503 , ln_running_total
2504 , ln_formerly_decl_amt
2505 , ln_base_imponiable
2506 , P_TAX_YEAR
2507 , G_LE_NAME
2508 , G_LE_TRN
2509 , lc_sign
2510 , p_tax_office
2511 , lc_print_year -- Bug 5525421
2512 , lc_print_period -- Bug 5525421
2513 );
2514
2515 -- Bug 5525421 The updating should happend only after inserting the correction record.
2516
2517 IF P_MODELO = '349' THEN
2518 IF P_REPORT_NAME = 'JEESAMOR' THEN
2519 IF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
2520
2521 UPDATE JG_ZZ_VAT_TRX_GT
2522 SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
2523 WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year)--RTRIM(SUBSTR(lc_correction_year,3,2))
2524 AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
2525 AND jg_info_v15 IS NOT NULL
2526 AND jg_info_v16 IS NOT NULL
2527 AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
2528 AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
2529 AND UPPER(jg_info_v11) = upper(DECODE(lc_triangulation,'X','T','E'));
2530
2531 END IF;
2532 END IF;
2533 END IF;
2534
2535 END IF;
2536 G_LINENO := '2.5.2';
2537 END LOOP;
2538 G_LINENO := '2.5.3';
2539 CLOSE c_modelo_ext; -- closecur
2540 G_LINENO := '2.5.4';
2541 END IF;
2542
2543 -- Bug 5525421 This procedure should call only once i.e after iserting all AP and AR records in to temp table.
2544 IF P_MODELO = '349' THEN
2545 IF P_REPORT_NAME = 'JEESAMOR' THEN
2546 JGZZVEFT();
2547 END IF;
2548 END IF;
2549 G_LINENO := '2.6';
2550
2554 WHERE NVL(jg_info_v30,'X') <> 'H';
2551 SELECT COUNT(*)
2552 INTO P_REC_COUNT
2553 FROM JG_ZZ_VAT_TRX_GT
2555
2556 UPDATE JG_ZZ_VAT_TRX_GT SET JG_INFO_N30= P_REC_COUNT WHERE jg_info_v30='H';
2557
2558 G_LINENO := '2.7';
2559 IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'Count of detail records in JG_ZZ_VAT_TRX_GT='||P_REC_COUNT); END IF;
2560 IF P_REPORT_NAME = 'JEESAMOR' THEN
2561 G_LINENO := '2.7.1';
2562
2563 IF P_MODELO = '347' THEN
2564 G_LINENO := '2.7.1.1';
2565 UPDATE JG_ZZ_VAT_TRX_GT
2566 SET jg_info_n11 = ( SELECT count(*)
2567 FROM JG_ZZ_VAT_TRX_GT
2568 WHERE jg_info_v1 <> '3'
2569 AND nvl(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_D
2570 , jg_info_n12 = ( SELECT count(*)
2571 FROM JG_ZZ_VAT_TRX_GT
2572 WHERE jg_info_v1 = '3'
2573 AND nvl(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_I
2574 , jg_info_n13 = ( SELECT DECODE(G_CURRENCY_CODE
2575 , 'EUR' ,(SUM(jg_info_n2)*100)
2576 , SUM(jg_info_n2))
2577 FROM JG_ZZ_VAT_TRX_GT
2578 WHERE jg_info_v1 <> '3') -- CP_TOTAL_AMT_DEC_D
2579 , jg_info_n14 = ( SELECT SUM(jg_info_n2)
2580 FROM JG_ZZ_VAT_TRX_GT
2581 WHERE jg_info_v1 = '3'
2582 AND NVL(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_I
2583 , jg_info_n15 = ( SELECT count(*)
2584 FROM JG_ZZ_VAT_TRX_GT
2585 WHERE nvl(jg_info_v30,'X') <> 'H' ) -- CP_NO_OF_TYPE2
2586 WHERE jg_info_v30 = 'H';
2587 G_LINENO := '2.7.1.2';
2588
2589 ELSIF P_MODELO = '415' THEN /** author:brathod; Modified for condition from P_MODELO='347' */
2590 G_LINENO := '2.7.1.3';
2591 UPDATE JG_ZZ_VAT_TRX_GT
2592 SET ( jg_info_n11 -- CS_IMP_VENTAS
2593 , jg_info_n12 -- CS_NUMERO_VENTAS
2594 , jg_info_n13 -- CS_IMP_MEDIACION
2595 , jg_info_n14 -- CS_NUMERO_MEDIACION
2596 , jg_info_n15 -- CS_IMP_COMPRAS
2597 , jg_info_n16 -- CS_NUMERO_COMPRAS
2598 ) =
2599 ( SELECT SUM( DECODE( jg_info_v1
2600 , 'V', NVL(jg_info_n1, jg_info_n2)
2601 , 0 ) ) CP_IMP_VENTAS
2602 , SUM( DECODE( jg_info_v1
2603 , 'V', 1
2604 , 0 ) ) CP_NUMERO_VENTAS
2605 , SUM( DECODE( jg_info_v1
2606 , 'M', NVL(jg_info_n1, jg_info_n2)
2607 , 0 ) ) CP_IMP_MEDIACION
2608 , SUM( DECODE( jg_info_v1
2609 , 'M', 1
2610 , 0 ) ) CP_NUMERO_MEDIACION
2611 , SUM( DECODE( jg_info_v1
2612 , 'C', NVL(jg_info_n1, jg_info_n2)
2613 , 0 ) ) CP_IMP_COMPRAS
2614 , SUM( DECODE( jg_info_v1
2615 , 'C', 1
2616 , 0 ) ) CF_NUMERO_COMPRAS
2617
2618 FROM JG_ZZ_VAT_TRX_GT
2619 WHERE NVL(jg_info_v30,'X') <> 'H'
2620 )
2621 WHERE jg_info_v30 = 'H';
2622 G_LINENO := '2.7.1.4';
2623 END IF;
2624 G_LINENO := '2.7.2';
2625 END IF;
2626 G_LINENO := '2.8';
2627 RETURN (TRUE);
2628 EXCEPTION
2629 WHEN OTHERS THEN
2630 fnd_file.put_line(FND_FILE.LOG,'Error while processing Before Report Trigger. Statement No = ' || G_LINENO||' Err:'|| SQLCODE || SUBSTR(SQLERRM,1,200));
2631 RETURN (FALSE);
2632 END BEFORE_REPORT;
2633
2634 FUNCTION after_Report RETURN BOOLEAN
2635 IS
2636 BEGIN
2637 NULL;
2638 RETURN (TRUE);
2639 END after_Report;
2640
2641 END je_es_modelo_ext_pkg;