[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(p_modelo,'415',DECODE(SUBSTR(assa.country, 1, 25)
, 'ES',SUBSTR(assa.zip,1,5)
, '99'||FT.eu_code)
, DECODE(assa.country
, 'ES',SUBSTR(assa.zip,1,2)||' '
, '99'||FT.territory_code||' ') ) POSTCODE
, SUBSTR(assa.city,1,25) CITY
, SUBSTR(assa.address_line1,1,35)||' '||
SUBSTR(assa.address_line2,1,35)||' '||
SUBSTR(assa.address_line3,1,35) ADDRESS_DETAIL
, assa.country
FROM ap_supplier_sites_all assa
, fnd_territories FT
WHERE assa.vendor_site_id = p_party_site_id
AND assa.country(+) = FT.territory_code;
select SUM(DECODE(JZVTD.OFFSET_FLAG,
'N',nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100)
,0))
+
SUM(DECODE(NVL(JZVTD.tax_amt_funcl_curr,0)
, 0, JZVTD.tax_amt,
JZVTD.tax_amt_funcl_curr)) SUM_TAXABLE_AMT_QTR,
decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, ZX_LINES_DET_FACTORS ZXDF ,AP_INVOICES_ALL API
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZRS.source = 'AP')
AND JZVTD.extract_source_ledger ='AP'
AND JZVRS.source = 'AP'
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
AND JZVRS.TAX_CALENDAR_YEAR = P_TAX_YEAR
AND JZVTD.trx_line_type <>'AWT'
AND JZVTD.trx_line_class <> 'EXPENSE REPORTS'
AND JZVTD.applied_from_line_id IS NULL
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
AND SUBSTR(zxdf.trx_business_category, INSTR(zxdf.trx_business_category,'MOD')+3,
DECODE(INSTR(zxdf.trx_business_category,'/',1,3),0,length(zxdf.trx_business_category),
INSTR(zxdf.trx_business_category,'/',1,3) - (INSTR(zxdf.trx_business_category,'MOD')+3)))
IN ( '347', '415_347', '347PR', '415_347PR')
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'A',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND DECODE( INSTR(NVL(ZXDF.trx_business_category,'N'),'PR'), 0, 'N', 'Y') = p_property_flag
AND JZVTD.trx_id = API.invoice_id
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_line_id = ZXDF.trx_line_id
AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
AND nvl(JZVTD.bill_from_party_id,1) = nvl(p_vendor_id,1)
AND nvl(JZVTD.billing_tp_taxpayer_id,1) = nvl(p_tax_registration_num,1)
AND nvl(substr(JZVTD.billing_tp_name,1,80),1) = nvl(p_customer_name,1)
AND APSS.vendor_site_id = p_customer_address_id
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.FEDERAL_REPORTABLE_FLAG,'Y') = 'Y'
AND APSS.TAX_REPORTING_SITE_FLAG = 'Y'
AND APSS.ORG_ID = P_ORG_ID
GROUP BY decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T')
UNION
SELECT
SUM (ROUND (DECODE (JZVTD.taxable_amt_funcl_curr,
0, JZVTD.taxable_amt,
NULL, JZVTD.taxable_amt,
JZVTD.taxable_amt_funcl_curr)
)) sum_taxable_amt_qtr,
decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, ap_suppliers aps
, ap_supplier_sites_all apss
, zx_lines_det_factors zxdf
, ap_invoices_all api
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVRS.source = 'AP'
AND JZVTD.extract_source_ledger = 'AP'
AND JZVTD.merchant_party_name IS NOT NULL
AND JZVTD.trx_line_type NOT IN ('AWT','TAX','PREPAY')
AND JZVTD.trx_line_class = 'EXPENSE REPORTS'
AND JZVTD.applied_from_line_id IS NULL
AND JZVRS.tax_calendar_year = P_TAX_YEAR
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
AND SUBSTR(zxdf.trx_business_category, INSTR(zxdf.trx_business_category,'MOD')+3,
DECODE(INSTR(zxdf.trx_business_category,'/',1,3),0,length(zxdf.trx_business_category),
INSTR(zxdf.trx_business_category,'/',1,3) - (INSTR(zxdf.trx_business_category,'MOD')+3)))
IN ( '347', '347PR', '415_347', '415_347PR')
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'A',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND DECODE( INSTR(NVL(ZXDF.trx_business_category,'N'),'PR'), 0, 'N', 'Y') = p_property_flag
AND jzvtd.trx_id = zxdf.trx_id
AND JZVTD.trx_id = API.invoice_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
AND nvl(JZVTD.bill_from_party_id,1) = nvl(p_vendor_id,1)
AND NVL(JZVTD.merchant_party_name,nvl(JZVTD.billing_tp_name,1)) = nvl(p_customer_name,1)
AND NVL(JZVTD.merchant_party_taxpayer_id,nvl(JZVTD.billing_tp_taxpayer_id,1)) = nvl(p_tax_registration_num,1)
AND APSS.vendor_site_id = p_customer_address_id
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.FEDERAL_REPORTABLE_FLAG ,'Y') = 'Y'
AND APSS.TAX_REPORTING_SITE_FLAG = 'Y'
AND APSS.ORG_ID = P_ORG_ID
group by decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T');
SELECT SUM(ROUND(DECODE( NVL(JZVTD.taxable_amt_funcl_curr,0)
,0 , JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr
)
, g_cur_precision)
) prepay_applied_Qtr,
decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,ap_invoice_distributions AID
,ap_invoice_distributions PRE
,hz_parties HP
,ap_invoices_all API
,zx_lines_det_factors ZXDF
WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.billing_trading_partner_id = p_vendor_id
AND JZVTD.trx_type_mng = 'PREPAYMENT'
AND AID.invoice_Id = JZVTD.trx_id
AND PRE.invoice_distribution_id = AID.invoice_distribution_id
AND PRE.posted_flag IN ('P','Y')
AND PRE.line_type_lookup_code <> 'AWT'
AND HP.party_id = JZVTD.billing_trading_partner_id
AND NVL(hp.party_type, 'XXX') <> 'EMPLOYEE'
AND DECODE( INSTR(NVL(ZXDF.trx_business_category,'N'),'PR'), 0, 'N', 'Y') = p_property_flag
AND SUBSTR(ZXDF.trx_business_category, INSTR(ZXDF.trx_business_category,'MOD')+3,
DECODE(INSTR(ZXDF.trx_business_category,'/',1,3),0,length(ZXDF.trx_business_category),
INSTR(ZXDF.trx_business_category,'/',1,3) - (INSTR(ZXDF.trx_business_category,'MOD')+3)))
IN ( '347', '347PR', '415_347', '415_347PR')
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'A',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND JZVTD.trx_id = API.invoice_id
AND jzvtd.trx_id = zxdf.trx_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
AND JZVRS.tax_calendar_year = P_TAX_YEAR
Group by decode(to_char(JZVTD.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T');
select SUM(DECODE(JZVTD.TAX_LINE_NUMBER, '1', NVL(JZVTD.taxable_amt_funcl_curr, JZVTD.taxable_amt),0))
+
SUM(NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)) Sum_Trx_Line_Amt_Qtr
,SUM(DECODE(NVL(trx.global_attribute12, 'N'),'Y', DECODE(JZVTD.TAX_LINE_NUMBER, '1', NVL(JZVTD.taxable_amt_funcl_curr, 0),0),0))
+
SUM(DECODE(NVL(trx.global_attribute12, 'N'),'Y', NVL(JZVTD.tax_amt_funcl_curr, 0),0)) TRANSMISSION_PROP_AMT_QTR
,decode(to_char(jzvtd.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, hz_cust_site_uses_all hzcsu
, hz_cust_acct_sites_all hzcas
, hz_cust_accounts hzca
, ra_customer_trx_all trx
, zx_lines_det_factors zxdf
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZRS.source = 'AR')
AND JZVTD.extract_source_ledger = 'AR'
AND JZVRS.source = 'AR'
AND JZVTD.trx_line_class <> 'DEBIT'
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
AND SUBSTR(ZXDF.trx_business_category, INSTR(zxdf.trx_business_category,'MOD')+3,
DECODE(INSTR(zxdf.trx_business_category,'/',1,3),0,length(zxdf.trx_business_category),
INSTR(zxdf.trx_business_category,'/',1,3) - (INSTR(zxdf.trx_business_category,'MOD')+3)))
IN ('347','415_347','347PR','415_347PR')
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'B',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_line_id = ZXDF.trx_line_id
AND JZVTD.trx_id = TRX.customer_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND JZVRS.tax_calendar_year = P_TAX_YEAR
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
AND nvl(JZVTD.billing_trading_partner_id,1) = nvl(p_customer_id,1)
AND JZVTD.BILLING_TRADING_PARTNER_ID = hzca.cust_account_id
AND hzca.cust_account_id = hzcas.cust_account_id
AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
AND upper(hzcsu.site_use_code) = 'LEGAL'
AND hzcsu.primary_flag = 'Y'
AND hzcsu.status = 'A'
AND hzcsu.ORG_ID = P_ORG_ID
group by decode(to_char(jzvtd.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') ;
select SUM(DECODE(JZVTD.TAX_LINE_NUMBER, '1', NVL(JZVTD.taxable_amt_funcl_curr, 0),0))
+ SUM(NVL(JZVTD.tax_amt_funcl_curr, 0)) arrenda_amt_qtr
,decode(to_char(jzvtd.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T') QUARTER_NUM
FROM jg_zz_vat_trx_details JZVTD
, jg_zz_vat_rep_status JZVRS
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF
, ra_customer_trx_all trx
WHERE nvl(JZVTD.billing_trading_partner_id,1) = nvl(p_customer_id,1)
AND HCSU.cust_acct_site_id = JZVTD.billing_tp_address_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND JZVTD.extract_source_ledger = 'AR'
AND JZVTD.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
AND HCSU.primary_flag = 'Y'
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
AND SUBSTR(ZXDF.trx_business_category, INSTR(ZXDF.trx_business_category,'MOD')+3,
DECODE(INSTR(ZXDF.trx_business_category,'/',1,3),0,length(ZXDF.trx_business_category),
INSTR(ZXDF.trx_business_category,'/',1,3) - (INSTR(ZXDF.trx_business_category,'MOD')+3)))
IN ( '347PR', '415_347PR')
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_id = trx.customer_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND JZVTD.trx_line_id = ZXDF.trx_line_id
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'B',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.source = 'AR'
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVRS.tax_calendar_year = P_TAX_YEAR
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
group by decode(to_char(jzvtd.gl_date,'MM'),'01','1T','02','1T','03','1T','04','2T','05','2T','06','2T','07','3T','08','3T','09','3T','10','4T','11','4T','12','4T');
SELECT DECODE(HL.country
, 'ES', SUBSTR(HL.postal_code,1,2)||'000'
, '99'||FT.eu_code) postal_code
, SUBSTR(HL.city,1,24) city
, SUBSTR(HL.ADDRESS1,1,2) ||
SUBSTR(HL.ADDRESS2,1,23) ||
LPAD(SUBSTR(HL.ADDRESS3,1,length(HL.ADDRESS3) -
NVL(LENGTH(LTRIM(TRANSLATE(HL.ADDRESS3, '123456789','000000000'),'0')),0)),5,'0') address_detail
FROM hz_cust_acct_sites_all HCAS
, hz_party_sites HPS
, hz_locations HL --12656346
, fnd_territories FT
, hz_cust_site_uses_all HCSU
WHERE HCAS.cust_acct_site_id = p_customer_address_id
AND HPS.party_site_id = HCAS.party_site_id
AND HL.location_id = HPS.location_id
AND HL.country(+) = FT.territory_code
AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND HCAS.bill_to_flag IN ('P','Y')
AND HCAS.status = 'A'
AND HCSU.primary_flag = 'Y' ;
SELECT DECODE(HL.country
, 'ES', SUBSTR(HL.postal_code,1,2)||' '
, '99'||FT.territory_code||' ') codigo_postal
, SUBSTR(HL.postal_code,1,5) post_code
, SUBSTR(HL.city,1,24) city
, SUBSTR(HL.address1,1,2) sigla
, SUBSTR(HL.address2,1,25) via_publica
, SUBSTR(HL.address3||'Z',1,INSTR(HL.address3||'Z',
LTRIM(HL.address3||'Z','1234567890')) - 1) numero
, HL.country
FROM hz_cust_acct_sites_all HCAS
, hz_party_sites HPS
, hz_locations HL
, fnd_territories FT
, hz_cust_site_uses_all HCSU
WHERE HCAS.cust_acct_site_id = p_customer_address_id
AND HPS.party_site_id = HCAS.party_site_id
AND HL.location_id = HPS.location_id
AND HL.country(+) = FT.territory_code
AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND HCAS.bill_to_flag IN ('P','Y')
AND HCAS.status = 'A'
AND HCSU.primary_flag = 'Y' ;
select SUBSTR(HL.global_attribute2,1,25) land_registry
, DECODE (HL.country,
'ES', DECODE(NVL(HL.global_attribute2, 'N'),
-- 3 Property in Spain without a land registry reference
'N', 3,
-- 2 Property in the Basque or Navarra Community
DECODE (SUBSTR(HL.postal_code,1,2),
'01', 2,
'48', 2,
'20', 2,
'31', 2,
-- 1 Property with land registry in Spain except 2
1)),
-- 4 Property located outside Spain
4) property_location_code
FROM
jg_zz_vat_trx_details JZVTD
, hr_locations HL
, ra_customer_trx TRX
, zx_lines_det_factors ZXDF
where JZVTD.trx_id = p_trx_id
AND JZVTD.trx_id = TRX.customer_trx_id
AND HL.location_id = TO_NUMBER(TRX.global_attribute2)
AND (SUBSTR(ZXDF.trx_business_category, INSTR(zxdf.trx_business_category,'MOD')+3,
DECODE(INSTR(zxdf.trx_business_category,'/',1,3),0,length(zxdf.trx_business_category),
INSTR(zxdf.trx_business_category,'/',1,3) - (INSTR(zxdf.trx_business_category,'MOD')+3)))
IN ('347PR','415_347PR')
OR substr(zxdf.document_sub_type,1,6) = 'MOD340')
AND nvl(zxdf.document_sub_type,'X') <> 'MOD340_EXCL'
AND JZVTD.trx_id = ZXDF.trx_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND TRX.global_attribute_category in ('JE.ES.ARXTWMAI.MODELO415_347PR','JE.ES.ARXTWMAI.MODELO347PR')
group by SUBSTR(HL.global_attribute2,1,25)
, DECODE (HL.country,
'ES', DECODE(NVL(HL.global_attribute2, 'N'),
-- 3 Property in Spain without a land registry reference
'N', 3,
-- 2 Property in the Basque or Navarra Community
DECODE (SUBSTR(HL.postal_code,1,2),
'01', 2,
'48', 2,
'20', 2,
'31', 2,
-- 1 Property with land registry in Spain except 2
1)),
-- 4 Property located outside Spain
4) ;
SELECT SUM(DECODE(JZVTD.TAX_LINE_NUMBER, '1', NVL(JZVTD.taxable_amt_funcl_curr, 0),0)) +
SUM(NVL(JZVTD.tax_amt_funcl_curr, 0)) trx_line_amt
/* total transaction amount was wrong, trx_line_amt calculation changed to consider
the total transaction amount only once, not for each tax line
SUM( NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt ) )
+ SUM( NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt ) ) trx_line_amt */
/* removed, causing wrong number of records retrieved
, NVL(JZVTD.tax_rate_id,0) tax_rate_id */
, SUBSTR(HL.postal_code,1,5) postcode
, SUBSTR(HL.global_attribute2,1,25) land_registry
, HL.town_or_city city
, SUBSTR(HL.address_line_1,1,2) address1
, SUBSTR(HL.address_line_2,1,25) address2
, SUBSTR(HL.address_line_3||'Z',1,INSTR(HL.address_line_3||'Z',
LTRIM(HL.address_line_3||'Z','1234567890')) - 1) address3
, SUBSTR(hl.loc_information15,1,3) stairs
, SUBSTR(hl.loc_information16,1,3) floor
, SUBSTR(hl.loc_information17,1,3) door
, SUBSTR(hl.loc_information13,1,3) number_type
, SUBSTR(hl.loc_information18,1,3) qualifier
, SUBSTR(hl.loc_information14,1,3) block
, SUBSTR(hl.loc_information19,1,3) portal
, SUBSTR(hl.loc_information20,1,40) complement
, SUBSTR(hl.town_or_city,1,30) locality
, SUBSTR(hl.postal_code,1,5) municipality_code
-- Property location code:
, DECODE (HL.country,
'ES', DECODE(NVL(HL.global_attribute2, 'N'),
-- 3 Property in Spain without a land registry reference
'N', 3,
-- 2 Property in the Basque or Navarra Community
DECODE (SUBSTR(HL.postal_code,1,2),
'01', 2,
'48', 2,
'20', 2,
'31', 2,
-- 1 Property with land registry in Spain except 2
1)),
-- 4 Property located outside Spain
4) property_location_code
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, hz_cust_acct_sites_all HCAS
, hz_party_sites HPS
, hr_locations HL
-- , fnd_territories FT -- Bug 8485057: not necessary
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF
, ra_customer_trx TRX
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.extract_source_ledger = 'AR'
AND JZVRS.source = 'AR'
AND HCAS.cust_acct_site_id = JZVTD.billing_tp_address_id
AND HPS.party_site_id = HCAS.party_site_id
/* Bug 8485057 section commented out, location is not the same as the customer's legal site, this validation can be done at the LOV level */
-- AND HL.location_id = HPS.location_id
-- AND HL.country(+) = FT.territory_code
AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND HCAS.bill_to_flag IN ('P','Y')
AND HCAS.status = 'A'
AND HCSU.primary_flag = 'Y'
AND JZVTD.billing_trading_partner_id = p_customer_id
AND JZVTD.gl_date BETWEEN G_FROM_DATE AND G_TO_DATE
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQ'),1,3) <> 'RET_AR' -- tax_rate_vat_trx_type_code
-- Bug 8485057 verify invoice_report_type directly from Trx Tax Lines
AND SUBSTR(ZXDF.trx_business_category, INSTR(zxdf.trx_business_category,'MOD')+3,
DECODE(INSTR(zxdf.trx_business_category,'/',1,3),0,length(zxdf.trx_business_category),
INSTR(zxdf.trx_business_category,'/',1,3) - (INSTR(zxdf.trx_business_category,'MOD')+3)))
IN ('347PR','415_347PR')
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_line_id = ZXDF.trx_line_id
AND JZVTD.trx_id = TRX.customer_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND HL.location_id = TO_NUMBER(TRX.global_attribute2)
AND TRX.global_attribute_category in ('JE.ES.ARXTWMAI.MODELO415_347PR','JE.ES.ARXTWMAI.MODELO347PR')
-- Bug 8485057 transaction code (tipo) filter
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'B',
SUBSTR(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
GROUP BY -- NVL(JZVTD.tax_rate_id,0),
SUBSTR(HL.postal_code,1,5)
, SUBSTR(HL.global_attribute2,1,25)
, HL.town_or_city
, SUBSTR(HL.address_line_1,1,2)
, SUBSTR(HL.address_line_2,1,25)
, SUBSTR(HL.address_line_3||'Z',1,INSTR(HL.address_line_3||'Z',
LTRIM(HL.address_line_3||'Z','1234567890')) - 1)
, SUBSTR(hl.loc_information15,1,3)
, SUBSTR(hl.loc_information16,1,3)
, SUBSTR(hl.loc_information17,1,3)
, SUBSTR(hl.loc_information13,1,3)
, SUBSTR(hl.loc_information18,1,3)
, SUBSTR(hl.loc_information14,1,3)
, SUBSTR(hl.loc_information19,1,3)
, SUBSTR(hl.loc_information20,1,40)
, SUBSTR(hl.town_or_city,1,30)
, SUBSTR(hl.postal_code,1,5)
, DECODE (HL.country,
'ES', DECODE(NVL(HL.global_attribute2, 'N'),
'N', 3,
DECODE (SUBSTR(HL.postal_code,1,2),
'01', 2,
'48', 2,
'20', 2,
'31', 2,
1)),
4);
SELECT DECODE( G_CURRENCY_CODE, 'EUR', (arrenda_rec.trx_line_amt*100), arrenda_rec.trx_line_amt)
INTO arrenda_rec.trx_line_amt
FROM DUAL;
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;
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- tipo
, jg_info_v6 -- p_tipo to link the arrenda record to its 'tipo' pair
, jg_info_n2 -- importe -- sum_trx_line_amt
, jg_info_v11 -- nombre -- cust name (Bug 8485057 switched nif to v11)
, jg_info_v12 -- nif -- cust tax ref (Bug 8485057 switched nombre to v12)
, jg_info_v25 -- sigla -- address1
, jg_info_v2 -- municipio -- city (corrected)
, jg_info_v18 -- codigo_postal -- postcode
, jg_info_v27 -- fin_ind -- 'S'
, jg_info_v26 -- via_publica -- address2,
, jg_info_v20 -- numero -- address3
, jg_info_n4 -- comentario -- Bug 8485057: 1 marks if record should not be listed in the magnetic format
, jg_info_v21 -- flag_arrenda -- 'Y'
, jg_info_v3 -- escalera -- loc_information15
, jg_info_v4 -- piso -- loc_information16
, jg_info_v5 -- puerta -- loc_information17
, jg_info_v8 -- number type -- loc_information13
, jg_info_v9 -- number qualifier -- loc_information18
, jg_info_v10 -- block -- loc_information14
, jg_info_v28 -- portal -- loc_information19
, jg_info_v29 -- complement -- loc_information20
, jg_info_v19 -- ref_catastral -- land_registry (corrected)
, jg_info_v7 -- property location code
, jg_info_v22 -- p_print_year -- p_tax_calender_year
, jg_info_v23 -- legal entity name
)
VALUES
('3'
, p_tipo -- to link the arrenda record to its pair
, arrenda_rec.trx_line_amt -- importe
, p_cust_tax_reg_num -- Bug 8485057 switch nif to jg_info_v11 to make delete procedure work
, p_customer_name -- Bug 8485057 switch nombre to jg_info_v12
, arrenda_rec.address1 --v_sg
, arrenda_rec.city --v_municipio
, arrenda_rec.postcode --v_codigo_postal,
, 'S' --fin_ind
, arrenda_rec.address2 --v_via_publica,
, arrenda_rec.address3 --v_numero,
, NULL --comentario
,'Y' --flag_arrenda,
, arrenda_rec.stairs --v_escalera,
, arrenda_rec.floor --v_piso,
, arrenda_rec.door --v_puerta,
, arrenda_rec.number_type
, arrenda_rec.qualifier
, arrenda_rec.block
, arrenda_rec.portal
, arrenda_rec.complement
, arrenda_rec.land_registry --v_ref_catastral
, arrenda_rec.property_location_code
, p_tax_year
, g_le_trn -- legal entity name
);
/*SELECT SUBSTR(jg_info_v11,1,2) C_CODIGO_PAIS -- TAX REG NUM
, RPAD(SUBSTR(jg_info_v11,3,12),15,' ') C_NIF_OPERADOR -- -do-
, RPAD(jg_info_v12,40,' ') C_NOMBRE -- cust name
, UPPER(jg_info_v1) C_CLAVE_OPERACION
, jg_info_n12 C_BASE_IMPONIBLE
, jg_info_n10 C_RUNNING_TOTAL
, jg_info_n11 C_FORMERLY_DECL_AMOUNT
, jg_info_v15 C_YEAR
, jg_info_v16 C_PERIOD
, jg_info_v17 C_OPERACION_TRIANGULAR
, rowid row_id
FROM JG_ZZ_VAT_TRX_GT
ORDER BY rpad(jg_info_v12,40,'A')
, jg_info_v1,jg_info_v15, jg_info_v16 desc;*/ -- Bug 5525421
SELECT jg_info_v22 YEAR_CAB
, jg_info_v26 LE_TRN
, SUBSTR(jg_info_v11,1,2) C_CODIGO_PAIS -- TAX REG NUM
, RPAD(SUBSTR(jg_info_v11,3,12),15,' ') C_NIF_OPERADOR -- -do-
, RPAD(jg_info_v12,40,' ') C_NOMBRE -- cust name
, UPPER(jg_info_v1) C_CLAVE_OPERACION
, jg_info_v15 C_YEAR
, jg_info_v16 C_PERIOD
, SUM(NVL(jg_info_n12,0)) C_BASE_IMPONIBLE
, SUM(jg_info_n11) C_FORMERLY_DECL_AMOUNT
, SUM(jg_info_n10) C_RUNNING_TOTAL
, jg_info_v23 SIGN
, jg_info_v18 POST_CODE
, jg_info_v17 OPERACION_TRIANGULAR
FROM JG_ZZ_VAT_TRX_GT JZVTG
WHERE NVL(JZVTG.jg_info_v30, 'X') <>'H'
AND jg_info_v27 = DECODE(P_DISPLAY_PERIOD,'OA',jg_info_v27,P_DISPLAY_PERIOD) -- Modified for Bug 7486406
GROUP BY jg_info_v21
, jg_info_v22
, SUBSTR(jg_info_v11,1,2)
, RPAD(SUBSTR(jg_info_v11,3,12),15,' ')
, Jg_info_v12
, jg_info_v1
, jg_info_v15
, jg_info_v16
, jg_info_v17
, jg_info_v18
, jg_info_v23
, jg_info_v26
ORDER BY rpad(jg_info_v12,40,'A')
, jg_info_v1
, NVL(jg_info_v15,0)
, NVL(jg_info_v16,'A');
SELECT DECODE(curr.derive_type,'EURO','343','349')
INTO lc_cur_derive_type
FROM fnd_currencies curr
WHERE currency_code = g_currency_code;
UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_N1 = ln_grp_cuenta_1
, jg_info_N2 = ln_grp_imp_1
, jg_info_N3 = ln_grp_cuenta_3_4
, jg_info_N4 = ln_grp_imp_2
WHERE jg_info_v30 = 'H' ;
SELECT SUM(ROUND(DECODE( NVL(JZVTD.taxable_amt_funcl_curr,0)
,0 , JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr
))
- NVL(AID.prepay_amount_remaining, 0 )* -1
)
INTO V_PREPAY_APPLIED
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,ap_invoice_distributions AID
WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.billing_trading_partner_id = p_vendor_id
AND JZVTD.trx_type_mng = 'PREPAYMENT'
--AND JZVTD.posted_flag IN ('P','Y') /** author: brathod; Removed posted_flag checking as it is not relevent in R12 */
SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period
))
OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR )
)
;
SELECT SUM(ROUND(DECODE( NVL(JZVTD.taxable_amt_funcl_curr,0)
,0 , JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr
)
, g_cur_precision)
)
INTO V_PREPAY_APPLIED
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,ap_invoice_distributions AID
,ap_invoice_distributions PRE
,hz_parties HP
,ap_invoices_all API
,zx_lines_det_factors ZXDF
WHERE JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.billing_trading_partner_id = p_vendor_id
AND JZVTD.trx_type_mng = 'PREPAYMENT'
--AND JZVTD.posted_flag IN ('P','Y') /** author:brathod; Removed posted_flag checking as it is not relevent in R12 */
SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period
))
OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR )
);
SELECT SUM(ROUND(DECODE( NVL(JVGT.jg_info_n8,0)
,0 , JVGT.jg_info_n6
, JVGT.jg_info_n8
)
, g_cur_precision)
)
INTO v_arrenda_amount
FROM JG_ZZ_VAT_TRX_GT JVGT
, hz_cust_acct_sites_all HCAS
, hz_party_sites HPS
, hz_locations HL
, fnd_territories FT
, hz_cust_site_uses_all HCSU
WHERE JVGT.jg_info_n3 = p_customer_id
AND HCAS.cust_acct_site_id = JVGT.jg_info_n2
AND HPS.party_site_id = HCAS.party_site_id
AND HL.location_id = HPS.location_id
AND HL.country(+) = FT.territory_code
AND HCSU.cust_acct_site_id = HCAS.cust_acct_site_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND HCAS.bill_to_flag = 'Y'
AND HCAS.status = 'A'
AND JVGT.jg_info_d5 IS NOT NULL
AND NVL(JVGT.jg_info_v25, 'N') = 'N' -- posted_flag
AND JVGT.jg_info_v31 <> 'DEBIT' -- invoice_type_lookup_code
AND HCSU.primary_flag = 'Y'
AND SUBSTR(NVL(JVGT.jg_info_v31,'QQQQQQ'),1,3) <> 'RET' -- tax_rate_vat_trx_type_code
AND JVGT.jg_info_v11 IN ('347'
,'347PR');*/
SELECT /* Bug 8485057: total transaction amount was wrong, trx_line_amt calculation changed to consider
the total transaction amount only once, not for each tax line */
SUM(DECODE(JZVTD.TAX_LINE_NUMBER, '1', NVL(JZVTD.taxable_amt_funcl_curr, 0),0))
+ SUM(NVL(JZVTD.tax_amt_funcl_curr, 0))
INTO v_arrenda_amount
FROM jg_zz_vat_trx_details JZVTD, jg_zz_vat_rep_status JZVRS
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF -- Bug 8485057
, ra_customer_trx_all trx
WHERE JZVTD.billing_trading_partner_id = p_customer_id
AND HCSU.cust_acct_site_id = JZVTD.billing_tp_address_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND JZVTD.extract_source_ledger = 'AR'
AND JZVTD.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
-- AND JZVTD.accounting_date IS NOT NULL --bug5557860
-- AND JZVTD.posted_flag = 'Y' /** author: brathod; removed posted_flag checking as it is not relevent in R12*/
SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period
))
OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR ));
/* Bug 8485057 Calculate the cash amount received for the selected transaction.
The cash receipt must have the payment method specified by the customer in the profile option. */
FUNCTION AR347_CASH_RECEIVEDFormula ( p_customer_id IN NUMBER,
p_tipo IN VARCHAR,
p_property_rental_flag IN VARCHAR,
p_year_of_amts_rec_cash IN NUMBER)
RETURN NUMBER is
v_cash_received_amount number;
/* Note to developers: the following select statements are identical, except for the ZXDF.trx_business_category
values ('347PR','415_347PR') or ('347','415_347'). All changes must be applied to both statements. */
IF p_property_rental_flag = 'Y'
THEN
SELECT SUM(NVL(DECODE(CR.type,'MISC', 0
,DECODE(RA.status,'APP',RA.AMOUNT_APPLIED,0)),0))
/ COUNT(JZVTD.trx_id)
INTO v_cash_received_amount
FROM jg_zz_vat_trx_details JZVTD, jg_zz_vat_rep_status JZVRS
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF
, ar_receivable_applications_all RA
, ar_cash_receipts_all CR
, ra_customer_trx_all trx
WHERE JZVTD.billing_trading_partner_id = p_customer_id
AND HCSU.cust_acct_site_id = JZVTD.billing_tp_address_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND JZVTD.extract_source_ledger = 'AR'
AND JZVTD.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
AND HCSU.primary_flag = 'Y'
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
-- Bug 8485057 verify invoice_report_type directly from Trx Tax Lines
AND SUBSTR(ZXDF.trx_business_category, INSTR(ZXDF.trx_business_category,'MOD')+3,
DECODE(INSTR(ZXDF.trx_business_category,'/',1,3),0,length(ZXDF.trx_business_category),
INSTR(ZXDF.trx_business_category,'/',1,3) - (INSTR(ZXDF.trx_business_category,'MOD')+3)))
IN ('347PR','415_347PR')
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_id = trx.customer_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND JZVTD.trx_line_id = ZXDF.trx_line_id
-- Bug 8485057 filter by tipo (transaction code)
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'B',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.source = 'AR'
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND ( (P_REPORT_NAME = 'JEESPMOR' AND JZVRS.tax_calendar_period in (
SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period
))
OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR ))
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND JZVTD.trx_id = RA.APPLIED_CUSTOMER_TRX_ID
AND CR.RECEIPT_METHOD_ID = fnd_profile.value('JEES_MOD347_RECEIPT_METHOD');
SELECT SUM(NVL(DECODE(CR.type,'MISC', 0
,DECODE(RA.status,'APP',RA.AMOUNT_APPLIED,0)),0))
/ COUNT(JZVTD.trx_id)
INTO v_cash_received_amount
FROM jg_zz_vat_trx_details JZVTD, jg_zz_vat_rep_status JZVRS
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF
, ar_receivable_applications_all RA
, ar_cash_receipts_all CR
, ra_customer_trx_all trx
WHERE JZVTD.billing_trading_partner_id = p_customer_id
AND HCSU.cust_acct_site_id = JZVTD.billing_tp_address_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND JZVTD.extract_source_ledger = 'AR'
AND JZVTD.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
AND HCSU.primary_flag = 'Y'
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
-- Bug 8485057 verify invoice_report_type directly from Trx Tax Lines
AND SUBSTR(ZXDF.trx_business_category, INSTR(ZXDF.trx_business_category,'MOD')+3,
DECODE(INSTR(ZXDF.trx_business_category,'/',1,3),0,length(ZXDF.trx_business_category),
INSTR(ZXDF.trx_business_category,'/',1,3) - (INSTR(ZXDF.trx_business_category,'MOD')+3)))
IN ('347','415_347')
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_id = trx.customer_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND JZVTD.trx_line_id = ZXDF.trx_line_id
-- Bug 8485057 filter by tipo (transaction code)
AND DECODE( INSTR(ZXDF.trx_business_category,'/',1,3),0,'B',
substr(ZXDF.trx_business_category,length(ZXDF.trx_business_category),1)) = p_tipo
AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.source = 'AR'
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND ( (P_REPORT_NAME = 'JEESPMOR' AND JZVRS.tax_calendar_period in (
SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period
))
OR (P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR ))
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND JZVTD.trx_id = RA.APPLIED_CUSTOMER_TRX_ID
AND CR.RECEIPT_METHOD_ID = fnd_profile.value('JEES_MOD347_RECEIPT_METHOD');
SELECT SUM(NVL(DECODE(CR.type,'MISC', 0
,DECODE(RA.status,'APP',RA.AMOUNT_APPLIED,0)),0))
/ COUNT(JZVTD.trx_id)
INTO v_cash_received_amount
FROM jg_zz_vat_trx_details JZVTD, jg_zz_vat_rep_status JZVRS
, hz_cust_site_uses_all HCSU
, zx_lines_det_factors ZXDF
, ar_receivable_applications_all RA
, ar_cash_receipts_all CR
, ra_customer_trx_all trx
WHERE JZVTD.billing_tp_taxpayer_id = p_declared_nif
AND HCSU.cust_acct_site_id = JZVTD.billing_tp_address_id
AND UPPER(HCSU.site_use_code) = 'LEGAL'
AND JZVTD.extract_source_ledger = 'AR'
AND JZVTD.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
AND HCSU.primary_flag = 'Y'
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,'QQQQQQ'),1,3) <> 'RET'
AND (SUBSTR(ZXDF.trx_business_category, INSTR(ZXDF.trx_business_category,'MOD')+3,
DECODE(INSTR(ZXDF.trx_business_category,'/',1,3),0,length(ZXDF.trx_business_category),
INSTR(ZXDF.trx_business_category,'/',1,3) - (INSTR(ZXDF.trx_business_category,'MOD')+3)))
IN ('347PR','415_347PR','347','415_347')
OR substr(zxdf.document_sub_type,1,6) = 'MOD340')
AND nvl(zxdf.document_sub_type,'X') <> 'MOD340_EXCL'
AND JZVTD.trx_id = ZXDF.trx_id
AND JZVTD.trx_id = trx.customer_trx_id
AND JZVTD.trx_id = p_trx_id
AND nvl(trx.global_attribute15,1) = nvl(p_year_of_amts_rec_cash,1)
AND JZVTD.trx_line_id = ZXDF.trx_line_id
AND decode( instr(zxdf.user_defined_fisc_class,'NONE',7,1),7,' ', substr(zxdf.user_defined_fisc_class,7,1) ) = p_transaction_code
AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.source = 'AR'
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND P_REPORT_NAME = 'JEESAMOR' AND JZVRS.tax_calendar_year = P_TAX_YEAR
AND RA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND JZVTD.trx_id = RA.APPLIED_CUSTOMER_TRX_ID
AND CR.RECEIPT_METHOD_ID = fnd_profile.value('JEES_MOD347_RECEIPT_METHOD');
that should also be deleted */
PROCEDURE MOD347_MIN_AMOUNT ( p_tipo IN VARCHAR2 ) IS
BEGIN
fnd_file.put_line(FND_FILE.LOG,'G_MIN_VALUE1 :'||G_MIN_VALUE);
UPDATE JG_ZZ_VAT_TRX_GT JZVTG
SET JZVTG.jg_info_n4 = 1
WHERE JZVTG.jg_info_v11 IN (
SELECT JZVTG1.jg_info_v11
FROM JG_ZZ_VAT_TRX_GT JZVTG1
WHERE JZVTG1.jg_info_v1= p_tipo
AND NVL(JZVTG.jg_info_n4,0) <> 1 -- record was not 'marked'
GROUP BY JZVTG1.jg_info_v11,JZVTG1.jg_info_v1
HAVING SUM(NVL(NVL(JZVTG1.jg_info_n2,JZVTG1.jg_info_n1),0)) <= NVL(G_MIN_VALUE,0)) -- 347-AP,415-AP ln_sum_trx_line_amt
AND (JZVTG.jg_info_v1 = p_tipo OR (JZVTG.jg_info_v1 = '3' AND JZVTG.jg_info_v6 = p_tipo))
AND NVL(JZVTG.jg_info_n4,0) <> 1;
/* Bug 8485057 update records that do not satisfy the minimum cash amount received parameter
Transaction codes(Tipos) B, F are subject to minimum amount. Tipos A,G are not. */
PROCEDURE MOD347_MIN_CASH_AMOUNT ( p_tipo IN VARCHAR2 ) IS
BEGIN
UPDATE JG_ZZ_VAT_TRX_GT JZVTG
SET JZVTG.jg_info_n6 = 0, JZVTG.jg_info_n28 = 0 -- Cash amount received does not meet the minimum -- bug 14725974
WHERE JZVTG.jg_info_v11 IN
(SELECT JZVTG1.jg_info_v11
FROM JG_ZZ_VAT_TRX_GT JZVTG1
WHERE JZVTG1.jg_info_v1= p_tipo -- Tipo
AND NVL(JZVTG1.jg_info_n4,0) <> 1
GROUP BY JZVTG1.jg_info_v11,JZVTG1.jg_info_v1
HAVING SUM(NVL(JZVTG1.jg_info_n6,0)) <= NVL(P_MIN_CASH_AMOUNT_VALUE,0))
AND JZVTG.jg_info_v1 = p_tipo
AND NVL(JZVTG.jg_info_n4,0) <> 1
AND NVL(JZVTG.jg_info_n6,0) <> 0;
UPDATE JG_ZZ_VAT_TRX_GT JZVTG
SET JZVTG.jg_info_n6 = 0 -- Cash amount received does not meet the minimum
WHERE JZVTG.jg_info_v4 IN
(SELECT JZVTG1.jg_info_v4
FROM JG_ZZ_VAT_TRX_GT JZVTG1
WHERE JZVTG1.jg_info_v10= p_transaction_code
AND JZVTG1.jg_info_v9 in ('E','F')
GROUP BY JZVTG1.jg_info_v4,JZVTG1.jg_info_v10
HAVING SUM(NVL(JZVTG1.jg_info_n6,0)) <= NVL(P_MIN_CASH_AMOUNT_VALUE,0))
AND JZVTG.jg_info_v10 = p_transaction_code
AND JZVTG.jg_info_v9 in ('E','F')
AND NVL(JZVTG.jg_info_n6,0) <> 0;
SELECT to_date ('01'||l_from||p_tax_year, 'DDMMYYYY'),
last_day(to_date ('01'||l_to||p_tax_year, 'DDMMYYYY'))
INTO p_340_start_date,
p_340_end_date
FROM dual;
SELECT /*+ NO_REWRITE */
$TAX_REGISTRATION_NUM$ TAX_REGISTRATION_NUM
, substr(billing_tp_name,1,80) CUSTOMER_NAME
-- , JZVTD.billing_tp_address_id CUSTOMER_ADDRESS_ID
, $ADDRESS_ID$ CUSTOMER_ADDRESS_ID
, DECODE( JZVTD.extract_source_ledger
, ''AP'' , JZVTD.bill_from_party_id
, JZVTD.billing_trading_partner_id ) BILLING_TRADING_PARTNER_ID
, SUM(DECODE(JZVTD.extract_source_ledger
,''AP'',DECODE(JZVTD.OFFSET_FLAG,
''N'',nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100)
,0)
---- ,''AR'',DECODE(JZVTD.TAX_LINE_NUMBER, ''1'', NVL(JZVTD.taxable_amt_funcl_curr, 0),0) Bug 12378790
,''AR'',DECODE(JZVTD.TAX_LINE_NUMBER, ''1'', NVL(JZVTD.taxable_amt_funcl_curr, JZVTD.taxable_amt),0)
))
+
SUM(DECODE(JZVTD.extract_source_ledger
,''AP'', DECODE(NVL(JZVTD.tax_amt_funcl_curr,0)
, 0, JZVTD.tax_amt,
JZVTD.tax_amt_funcl_curr)
--- , ''AR'', NVL(JZVTD.tax_amt_funcl_curr, 0) Bug 12379705
, ''AR'', NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)
) )
SUM_TAXABLE_AMT
, SUM(DECODE(JZVTD.extract_source_ledger
,''AP'',DECODE(JZVTD.OFFSET_FLAG,
''N'',nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100)
,0)
--- , ''AR'',DECODE(JZVTD.TAX_LINE_NUMBER, ''1'', NVL(JZVTD.taxable_amt_funcl_curr, 0),0) Bug 12378790
, ''AR'',DECODE(JZVTD.TAX_LINE_NUMBER, ''1'', NVL(JZVTD.taxable_amt_funcl_curr, JZVTD.taxable_amt),0)
))
+
SUM(DECODE(JZVTD.extract_source_ledger
,''AP'', DECODE(NVL(JZVTD.tax_amt_funcl_curr,0)
, 0, JZVTD.tax_amt,
JZVTD.tax_amt_funcl_curr)
--- , ''AR'', NVL(JZVTD.tax_amt_funcl_curr, 0) Bug 12379705
, ''AR'', NVL(JZVTD.tax_amt_funcl_curr, JZVTD.tax_amt)
) )
SUM_TRX_LINE_AMT
/**
author: brathod;
AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZRS.source IN ( ''AP'', ''AR'' ))
AND JZVTD.extract_source_ledger IN ( ''AP'', ''AR'' )
AND JZVRS.source IN ( ''AP'', ''AR'' )
/**
author: brathod
date : 18/5/2006
Commented date based filtering and introduced conditional filtering based on report type.
AND JZVTD.tax_invoice_date BETWEEN $PERIOD_FROM_DATE$
AND $PERIOD_TO_DATE$
*/
AND $FILTER_KEY$ $FILTER_OPER$ $FILTER_VALUE$
$MODELO_SPECIFIC_FILTERS$
GROUP BY $TAX_REGISTRATION_NUM$
, substr(billing_tp_name,1,80)
, $ADDRESS_ID$
, DECODE( JZVTD.extract_source_ledger
, ''AP'' , JZVTD.bill_from_party_id
, JZVTD.billing_trading_partner_id )
--,NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)
$CORRECTION_TRX_GRP_COL$ $PROPERTY_FLAG_GRP_COL$ $TRANS_CODE_GRP_COL$ $YEAR_OF_AMTS_REC_CASH_GRP_COL$ $MODELO_SPECIFIC_GRP_FILTER$
';
/* Updated filter to include G_MIN_VALUE parameter instead of hardcoded 0 value */
C_MOD347415_GRP_FILTER CONSTANT VARCHAR2(1000) :=
'HAVING SUM(DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
, 0 ,JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr )) >= $G_MIN_VALUE$ '; --0
Select max(JZVRS.period_end_date) period_end_date
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_registration_number = cp_tax_registration
AND JZVRS.tax_calendar_period = p_tax_period_to
Group by JZVRS.tax_registration_number;
SELECT RPS1.tax_calendar_period tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_tax_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period;
IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'SELECT precision INTO G_CUR_PRECISION'); END IF;
SELECT precision
INTO G_CUR_PRECISION
FROM fnd_currencies curr
WHERE currency_code = g_currency_code;
IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'BEFORE INSERT HEADER'); END IF;
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1 -- curr_code
,jg_info_v2 -- entity_name
,jg_info_v3 -- taxpayer_id
,jg_info_v4 -- company_name
,jg_info_v5 -- registration_number
,jg_info_v26 -- country
,jg_info_v27 -- address1
,jg_info_v28 -- address2
,jg_info_v29 -- address3
,jg_info_v31 -- address4
,jg_info_v32 -- city
,jg_info_v6 -- postal_code
,jg_info_v7 -- contact
,jg_info_v33 -- phone_number
,jg_info_v8 -- reporting mode
,jg_info_v9 -- P_TAX_YEAR
,jg_info_v10 -- P_TAX_PERIOD
,jg_info_v11 -- P_MODELO
,jg_info_v35 -- P_SOURCE
,jg_info_v12 -- P_CONTACT_TEL
,jg_info_v13 -- P_CONTACT_NAME
,jg_info_v14 -- P_TAX_OFFICE -- Bug 8485057 Note: not printed in Modelo 347 magnetic format
,jg_info_v15 -- P_CONTACT_TEL_CODE
,jg_info_v16 -- P_REFERENCE_NUMBER
,jg_info_v17 -- P_MAIN_ACTIVITY
,jg_info_v18 -- P_MAIN_ACTIVITY_CD
,jg_info_v19 -- P_SECOND_ACTIVITY
,jg_info_v20 -- P_SECOND_ACTIVITY_CD
,jg_info_v21 -- P_TOTAL_PURCHASES
,jg_info_v22 -- P_TOTAL_SALES
,jg_info_v23 -- P_TAX_OFF_REG_CODES
,jg_info_v24 -- P_MEDIUM
,jg_info_v25 -- P_FORMAT_TYPE
,jg_info_v34 -- P_PRV_REFERENCE_NUMBER
,jg_info_n6 -- G_MIN_VALUE
,jg_info_d1 -- ld_period_start_date
,jg_info_d2 -- ld_period_end_date
,jg_info_v30 -- Header record indicator
,jg_info_v36 -- P_PERIOD
,jg_info_v37 -- P_ELEC_CODE
,jg_info_v38 -- P_SUBSTITUTION
,jg_info_n7 -- P_MIN_CASH_AMOUNT_VALUE Bug 8485057
)
VALUES
(lc_func_curr_code
,lc_rep_legal_entity
,lc_taxpayer_id
,lc_company_name
,lc_taxpayer_id --lc_trx_num Bug 13986531
,lc_country
,lc_address1
,lc_address2
,lc_address3
,lc_address4
,lc_city
,lc_postal_code
,lc_contact
,lc_phone_number
,lc_reporting_mode
,P_TAX_YEAR
,P_TAX_PERIOD || ' - ' || P_TAX_PERIOD_TO
,P_MODELO
,P_SOURCE
,P_CONTACT_TEL
,P_CONTACT_NAME
,P_TAX_OFFICE
,P_CONTACT_TEL_CODE
,P_REFERENCE_NUMBER
,P_MAIN_ACTIVITY
,P_MAIN_ACTIVITY_CD
,P_SECOND_ACTIVITY
,P_SECOND_ACTIVITY_CD
,P_TOTAL_PURCHASES
,P_TOTAL_SALES
,P_TAX_OFF_REG_CODES
,P_MEDIUM
,P_FORMAT_TYPE
,P_PRV_REFERENCE_NUMBER
,G_MIN_VALUE
,ld_period_start_date
,ld_period_end_date
,'H'
,P_340_PERIOD
,P_ELEC_CODE
,P_SUBSTITUTION
,P_MIN_CASH_AMOUNT_VALUE);
IF G_DEBUG THEN fnd_file.put_line(FND_FILE.LOG,'AFTER INSERT HEADER'); END IF;
SELECT /*+ NO_REWRITE */
NVL(JZVTD.merchant_party_taxpayer_id,
JZVTD.billing_tp_taxpayer_id) TAX_REGISTRATION_NUM
, NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name) CUSTOMER_NAME
, APSS.VENDOR_SITE_ID CUSTOMER_ADDRESS_ID
, JZVTD.bill_from_party_id BILLING_TRADING_PARTNER_ID
, SUM (ROUND (nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100) -- Bug 16502438
/** author:brathod
+
NVL (DECODE (alc.base_amount,
0, alc.amount,
NULL, alc.amount,
alc.base_amount), 0)
*/
,2)) SUM_TAXABLE_AMT
, 0 SUM_TRX_LINE_AMT
, 0 NCORRECTION_AMOUNT
, 0 CORRECTION_AMOUNT
, NULL
, NULL
, NULL
, NULL
, DECODE (INSTR(NVL(API.trx_business_category,''N''),''PR''),
0,''N'',''Y'') PROPERTY_RETAIL_FLAG
--bug10282300
--bug10282300 , NULL
, DECODE( INSTR(zxdf.trx_business_category,''/'',1,3),0,''A'',
substr(zxdf.trx_business_category,length(zxdf.trx_business_category),1)) TIPO
, NULL TRANSMISSION_PROPERTY_AMT
, NULL YEAR_OF_AMTS_RECEIVED_CASH
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, ap_suppliers aps
, ap_supplier_sites_all apss
, ap_invoices_all API
, zx_lines_det_factors zxdf
/**
author:brathod
, ap_invoice_distributions_all AID
, ap_invoice_distributions_all ALC
WHERE AID.invoice_distribution_id = JZVTD.trx_line_id
AND ALC.invoice_distribution_id = AID.charge_applicable_to_dist_id
*/
WHERE JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVRS.source = ''AP''
AND JZVTD.extract_source_ledger = ''AP''
AND JZVTD.merchant_party_name IS NOT NULL
AND JZVTD.trx_line_type NOT IN (''AWT'',''TAX'',''PREPAY'')
--AND JZVTD.posted_flag IN (''P'',''Y'') /** author:brathod; Removed posted_flag checking as it is not relevent in R12 */
SELECT NVL(jg_info_n10,0) -- running total
INTO ln_formerly_decl_amt
FROM JG_ZZ_VAT_TRX_GT M349
WHERE RTRIM(jg_info_v11) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v12) = RTRIM(lc_customer_name)
AND jg_info_n13 = lc_correction_year -- correction year
AND jg_info_v27 = lc_correction_period -- correction period
-- AND RTRIM(jg_info_v21) = RTRIM(lc_correction_year) --Bug 5525421
-- AND UPPER(jg_info_v1) ='A'; -- BUG 9765740
-- As per R11i logic this update should happend after the insert. Hence moving this to after insert.
/* UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year) --RTRIM(SUBSTR(lc_correction_year,3,2))
AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
AND jg_info_v14 IS NOT NULL
AND jg_info_v15 IS NOT NULL
AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
AND UPPER(jg_info_v11) ='A'; */ --Bug 5525421
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- lc_clave_operation lc_clave_operation
, jg_info_v6 -- lc_country Bug 8485057
, jg_info_v11 -- c_modelo_rec.tax_registration_number lc_tax_registration_number
, jg_info_v12 -- c_modelo_rec.customer_name lc_customer_name
, jg_info_v13 -- c_modelo_rec.customer_address_id lc_customer_address_id
, jg_info_v14 -- c_modelo_rec.billing_trading_partner_id lc_billing_trading_partner
, jg_info_n1 -- c_modelo_rec.sum_taxable_amt -- 347-AP, 415-AP ln_sum_taxable_amt
, jg_info_n16 -- ln_sum_taxable_amt_q1 -347-AP
, jg_info_n18 -- ln_sum_taxable_amt_q2 -347-AP
, jg_info_n20 -- ln_sum_taxable_amt_q3 -347-AP
, jg_info_n22 -- ln_sum_taxable_amt_q4 -347-AP
, jg_info_n2 -- c_modelo_rec.sum_trx_line_amt -- 347-AR,415-AR ln_sum_trx_line_amt
, jg_info_n3 -- c_modelo_rec.ncorrection_amount -- 349 ln_ncorrection_amount
, jg_info_n4 -- c_modelo_rec.correction_amount -- 349 ln_correction_amount
, jg_info_v15 -- c_modelo_rec.correction_year lc_correction_year
, jg_info_v16 -- c_modelo_rec.correction_period lc_correction_period
, jg_info_v17 -- c_modelo_rec.triangulation lc_triangulation
, jg_info_v21 -- c_modelo_rec.property_retail_flag lc_property_retail_flag
, jg_info_v18 -- lc_postal_code lc_postal_code
, jg_info_v19 -- lc_city lc_city
, jg_info_v20 -- lc_address_detail lc_address_detail
, jg_info_n10 -- ln_running_total ln_running_total
, jg_info_n11 -- ln_formerly_decl_amt ln_formerly_decl_amt
, jg_info_n12 -- ln_base_imponiable ln_base_imponiable
, jg_info_v22 -- p_print_year --p_tax_calender_year P_TAX_YEAR
, jg_info_v23 -- legal entity name G_LE_NAME
, jg_info_v26 -- G_LE_TRN -- 347 G_LE_TRN
, jg_info_v24 -- lc_sign lc_sign
, jg_info_v25 -- p_tax_office -- 347 p_tax_office
, jg_info_n13 -- lc_print_year --Bug 5525421
, jg_info_v27 -- lc_print_period --Bug 5525421
)
VALUES
( lc_clave_operation
, lc_country
, lc_tax_registration_number
, lc_customer_name
, lc_customer_address_id
, lc_billing_trading_partner_id
, ln_sum_taxable_amt
, ln_sum_taxable_amt_qtr1
, ln_sum_taxable_amt_qtr2
, ln_sum_taxable_amt_qtr3
, ln_sum_taxable_amt_qtr4
, ln_sum_trx_line_amt
, ln_ncorrection_amount
, ln_correction_amount
, lc_correction_year
, lc_correction_period
, lc_triangulation
, lc_property_retail_flag
, lc_postal_code
, lc_city
, lc_address_detail
, ln_running_total
, ln_formerly_decl_amt
, ln_base_imponiable
, P_TAX_YEAR
, G_LE_NAME
, G_LE_TRN
, lc_sign
, p_tax_office
, lc_print_year --Bug 5525421
, lc_print_period --Bug 5525421
);
----Bug 5525421 Updation should haapend only after insertintg the correction reocrd.
IF P_MODELO = '349' THEN
IF P_REPORT_NAME = 'JEESAMOR' THEN
IF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year) --RTRIM(SUBSTR(lc_correction_year,3,2))
AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
AND jg_info_v14 IS NOT NULL
AND jg_info_v15 IS NOT NULL
AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
-- AND UPPER(jg_info_v11) ='A'; Modified from V11 to V1 BUG 9765740
select tax_calendar_name
from jg_zz_vat_rep_entities
where vat_reporting_entity_id = p_vat_rep_entity_id;
select min(start_date), max(end_date)
from gl_periods
where period_set_name = lv_tax_calendar
and period_name in (P_FROM_PERIOD, P_TO_PERIOD);
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- lc_clave_operation
, jg_info_n2 -- ln_arrenda_amount
, jg_info_n17 -- ln_arrenda_amt_q1
, jg_info_n19 -- ln_arrenda_amt_q2
, jg_info_n21 -- ln_arrenda_amt_q3
, jg_info_n23 -- ln_arrenda_amt_q4
, jg_info_n6 -- ln_cash_received_amount
, jg_info_n28 -- ln_year_of_amts_received_cash
, jg_info_v11 -- lc_tax_registration_number
, jg_info_v12 -- c_modelo_rec.customer_name
, jg_info_v13 -- c_modelo_rec.customer_address_id
, jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
, jg_info_v21 -- c_modelo_rec.property_retail_flag (flag_arrenda)
, jg_info_v18 -- lc_postal_code
, jg_info_v19 -- lc_city
, jg_info_v20 -- lc_number
, jg_info_v25 -- lc_street_type
, jg_info_v26 -- lc_street
, jg_info_v27 -- fin_id
, jg_info_v22 -- p_print_year --p_tax_calendar_year
, jg_info_v23 -- legal entity TRN
, jg_info_v6 -- lc_country
, jg_info_v28 -- lc_postal_code1
)
VALUES
( lc_clave_operation
, ln_arrenda_amount
, ln_arrenda_amt_q1
, ln_arrenda_amt_q2
, ln_arrenda_amt_q3
, ln_arrenda_amt_q4
, ln_cash_received_amount
, ln_year_of_amts_received_cash
, lc_tax_registration_number
, lc_customer_name
, lc_customer_address_id
, lc_billing_trading_partner_id
, 'Y'
, lc_postal_code
, lc_city
, lc_number
, lc_street_type
, lc_street
, 'S'
, p_tax_year
, G_LE_TRN
, lc_country
, lc_postal_code1
) ;
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- lc_clave_operation
, jg_info_n2 -- ln_sum_trx_line_amt
, jg_info_n17 -- ln_sum_trx_line_amt_q1
, jg_info_n19 -- ln_sum_trx_line_amt_q2
, jg_info_n21 -- ln_sum_trx_line_amt_q3
, jg_info_n23 -- ln_sum_trx_line_amt_q4
, jg_info_n5 -- ln_transmission_property_amt
, jg_info_n24 -- ln_transmission_prop_amt_q1
, jg_info_n25 -- ln_transmission_prop_amt_q2
, jg_info_n26 -- ln_transmission_prop_amt_q3
, jg_info_n27 -- ln_transmission_prop_amt_q4
, jg_info_n6 -- ln_cash_received_amount
, jg_info_n28 -- ln_year_of_amts_received_cash
, jg_info_v11 -- lc_tax_registration_number
, jg_info_v12 -- c_modelo_rec.customer_name
, jg_info_v13 -- c_modelo_rec.customer_address_id
, jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
, jg_info_v21 -- c_modelo_rec.property_retail_flag -- flag_arrenda
, jg_info_v18 -- lc_postal_code
, jg_info_v19 -- lc_city -- x_ref_catastral
, jg_info_v20 -- lc_number -- x_numero
, jg_info_v25 -- lc_street_type
, jg_info_v26 -- lc_street
, jg_info_v27 -- fin_id -- 'S'
, jg_info_v22 -- p_print_year --p_tax_calender_year
, jg_info_v23 -- legal entity TRN
, jg_info_v6 -- lc_country
, jg_info_v28 -- lc_postal_code1
)
VALUES
( lc_clave_operation
, ln_sum_trx_line_amt
, ln_sum_trx_line_amt_q1
, ln_sum_trx_line_amt_q2
, ln_sum_trx_line_amt_q3
, ln_sum_trx_line_amt_q4
, ln_transmission_property_amt
, ln_transmission_prop_amt_q1
, ln_transmission_prop_amt_q2
, ln_transmission_prop_amt_q3
, ln_transmission_prop_amt_q4
, ln_cash_received_amount
, ln_year_of_amts_received_cash
, lc_tax_registration_number
, lc_customer_name
, lc_customer_address_id
, lc_billing_trading_partner_id
, 'N'
, lc_postal_code
, lc_city
, lc_number
, lc_street_type
, lc_street
, 'S'
, p_tax_year
, G_LE_TRN
, lc_country
, lc_postal_code1
) ;
SELECT DECODE(lc_triangulation,'X','T','E')
INTO lc_clave_operation
FROM DUAL ;
SELECT DECODE(lc_triangulation,'X','t','e') -- Bug 5525421
INTO lc_clave_operation
FROM DUAL ;
SELECT NVL(jg_info_n10,0) -- running total
INTO ln_formerly_decl_amt
FROM JG_ZZ_VAT_TRX_GT M349
WHERE RTRIM(jg_info_v11) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v12) = RTRIM(lc_customer_name)
AND jg_info_n13 = lc_correction_year -- correction year -- Bug 5525421
AND jg_info_v27 = lc_correction_period -- correction period -- Bug 5525421
-- AND RTRIM(jg_info_v21) = RTRIM(lc_correction_year) -- Bug 5525421
-- AND UPPER(jg_info_v1) = upper(DECODE(lc_triangulation,'X','T','E')) -- BUG 9765740
AND UPPER(jg_info_v1) = upper(lc_triangulation) -- BUG 9765740
AND jg_info_v15 is NULL -- Added as part of Bug fix for BUG 9765740
AND jg_info_v16 is NULL; -- Added as part of Bug fix for BUG 9765740
/* UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year)--RTRIM(SUBSTR(lc_correction_year,3,2))
AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
AND jg_info_v15 IS NOT NULL
AND jg_info_v16 IS NOT NULL
AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
AND UPPER(jg_info_v11) = upper(DECODE(lc_triangulation,'X','T','E')); */
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- lc_clave_operation
, jg_info_v11 -- c_modelo_rec.tax_registration_number
, jg_info_v12 -- c_modelo_rec.customer_name
, jg_info_v13 -- c_modelo_rec.customer_address_id
, jg_info_v14 -- c_modelo_rec.billing_trading_partner_id
, jg_info_n1 -- c_modelo_rec.sum_taxable_amt -- 347-AP, 415-AP
, jg_info_n2 -- c_modelo_rec.sum_trx_line_amt -- 347-AR, 415-AR
, jg_info_n3 -- c_modelo_rec.ncorrection_amount -- 349
, jg_info_n4 -- c_modelo_rec.correction_amount -- 349
, jg_info_v15 -- c_modelo_rec.correction_year
, jg_info_v16 -- c_modelo_rec.correction_period
, jg_info_v17 -- c_modelo_rec.triangulation
, jg_info_v21 -- c_modelo_rec.property_retail_flag
, jg_info_v18 -- lc_postal_code
, jg_info_v19 -- lc_city -- x_ref_catastral
, jg_info_v20 -- lc_address_detail -- x_numero
, jg_info_n10 -- ln_running_total
, jg_info_n11 -- ln_formerly_decl_amt
, jg_info_n12 -- ln_base_imponiable
, jg_info_v22 -- p_print_year --p_tax_calender_year
, jg_info_v23 -- legal entity name
, jg_info_v26 -- G_LE_TRN -- 347
, jg_info_v24 -- lc_sign
, jg_info_v25 -- p_tax_office -- 347
, jg_info_n13 -- lc_prtint_year -- Bug 5525421
, jg_info_v27 -- lc_print_period -- Bug 5525421
)
VALUES
( lc_clave_operation
, lc_tax_registration_number
, lc_customer_name
, lc_customer_address_id
, lc_billing_trading_partner_id
, ln_sum_taxable_amt
, ln_sum_trx_line_amt
, ln_ncorrection_amount
, ln_correction_amount
, lc_correction_year
, lc_correction_period
, lc_triangulation
, lc_property_retail_flag
, lc_postal_code
, lc_city
, lc_address_detail
, ln_running_total
, ln_formerly_decl_amt
, ln_base_imponiable
, P_TAX_YEAR
, G_LE_NAME
, G_LE_TRN
, lc_sign
, p_tax_office
, lc_print_year -- Bug 5525421
, lc_print_period -- Bug 5525421
);
-- Bug 5525421 The updating should happend only after inserting the correction record.
IF P_MODELO = '349' THEN
IF P_REPORT_NAME = 'JEESAMOR' THEN
IF lc_correction_year IS NOT NULL AND lc_correction_period IS NOT NULL THEN --IS a Correction
UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n10 = ln_formerly_decl_amt + ln_ncorrection_amount
WHERE RTRIM(jg_info_v15) = RTRIM(lc_correction_year)--RTRIM(SUBSTR(lc_correction_year,3,2))
AND RTRIM(jg_info_v16) = RTRIM(lc_correction_period)
AND jg_info_v15 IS NOT NULL
AND jg_info_v16 IS NOT NULL
AND RTRIM(jg_info_v12) = RTRIM(lc_tax_registration_number)
AND RTRIM(jg_info_v13) = RTRIM(lc_customer_name)
-- AND UPPER(jg_info_v11) = upper(DECODE(lc_triangulation,'X','T','E')); -- BUG 9765740
'SELECT decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
jzvtd.billing_tp_taxpayer_id) DECLARED_NIF,
substrb(jzvtd.billing_tp_name,1,40) DECLARED_NAME,
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)) KEY_ID, -- BUG 8946271
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)) FISCAL_ID,
decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) BOOK_TYPE,
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ) TRANSACTION_CODE,
jzvtd.trx_date ISSUE_DATE,
jzvtd.tax_invoice_date TRANSACTION_DATE,
jzvtd.trx_number INVOICE_IDENT,
jzvtd.doc_seq_value REGISTER_NUMBER,
decode(substr(api.document_sub_type,1,8),''MOD340/U'',
substr(api.document_sub_type,9,1)) INTRA_TYPE,
decode(substr(api.document_sub_type,1,8), ''MOD340/U'',''D''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''D'')) KEY_OF_DECLARED,
api.global_attribute10 TRANSACTION_DEADLINE,
decode(substr(api.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))) DESC_OF_GOODS,
SUM(DECODE(jzvtd.OFFSET_FLAG,''N'',
nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100)
,0)) TAXABLE_AMOUNT,
SUM(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt )) TAX_AMOUNT,
SUM(DECODE(jzvtd.OFFSET_FLAG,''N'',
nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100),0)) +
SUM(nvl(DECODE(jzvtd.tax_amt_funcl_curr,
0, jzvtd.tax_amt ,
NULL, jzvtd.tax_amt,
jzvtd.tax_amt_funcl_curr), 0)) INV_TOTAL_AMOUNT,
SUM(decode(jzvtd.tax_recoverable_flag, ''Y'',
nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt ))) DEDUCTABLE_AMOUNT,
jzvtd.tax_rate TAX_RATE,
APSS.VENDOR_SITE_ID
FROM jg_zz_vat_trx_details jzvtd, jg_zz_vat_rep_status jzvrs
, AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, AP_INVOICES_ALL API,
zx_lines_det_factors zxdf
WHERE (substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3) in (''347'',''349'',''415'')
OR substr(jzvtd.document_sub_type,1,6) = ''MOD340'')
AND nvl(jzvtd.document_sub_type,''X'') <> ''MOD340_EXCL''
AND JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZRS.source = ''AP'' )
AND $FILTER_KEY$ $FILTER_OPER$ $FILTER_VALUE$
$340_PERIOD_KEY$ $340_PERIOD_KEY_FROM$ $340_PERIOD_OPER$ $340_PERIOD_KEY_TO$
AND JZVTD.trx_line_type <> ''AWT''
AND JZVTD.trx_line_class <> ''EXPENSE REPORTS''
AND JZVTD.applied_from_line_id IS NULL
AND JZVTD.extract_source_ledger = ''AP''
AND JZVRS.source = ''AP''
AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.FEDERAL_REPORTABLE_FLAG,''Y'')= ''Y''
AND APSS.TAX_REPORTING_SITE_FLAG = ''Y''
AND APSS.ORG_ID = $P_ORG_ID$
AND jzvtd.trx_id = api.invoice_id
AND jzvtd.trx_id = zxdf.trx_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND ( (decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) <> ''R'')
OR (decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) = ''R''
AND decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '',
substr(zxdf.user_defined_fisc_class,7,1) ) NOT IN (''P'',''I'') )
OR ((decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) = ''R''
AND decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '',
substr(zxdf.user_defined_fisc_class,7,1) ) IN (''P'',''I'') )
AND jzvtd.reporting_code <> ''OFFSET'' ))
GROUP BY decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
jzvtd.billing_tp_taxpayer_id),
substrb(jzvtd.billing_tp_name,1,40),
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)),
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)),
decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')),
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ),
jzvtd.trx_date, jzvtd.tax_invoice_date, jzvtd.trx_number, jzvtd.doc_seq_value,
decode(substr(api.document_sub_type,1,8),''MOD340/U'',
substr(api.document_sub_type,9,1)),
decode(substr(api.document_sub_type,1,8), ''MOD340/U'',''D''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''D'')),
api.global_attribute10,
decode(substr(api.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))),
jzvtd.tax_rate, APSS.VENDOR_SITE_ID ';
'UNION SELECT decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
NVL(JZVTD.merchant_party_taxpayer_id, JZVTD.billing_tp_taxpayer_id)) DECLARED_NIF,
substrb(NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name),1,40) DECLARED_NAME,
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)) KEY_ID, --BUG 8946271
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)) FISCAL_ID,
decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) BOOK_TYPE,
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ) TRANSACTION_CODE,
jzvtd.trx_date ISSUE_DATE,
jzvtd.tax_invoice_date TRANSACTION_DATE,
jzvtd.trx_number INVOICE_IDENT,
jzvtd.doc_seq_value REGISTER_NUMBER,
decode(substr(api.document_sub_type,1,8),''MOD340/U'',
substr(api.document_sub_type,9,1)) INTRA_TYPE,
decode(substr(api.document_sub_type,1,8), ''MOD340/U'',''D''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''D'')) KEY_OF_DECLARED,
api.global_attribute10 TRANSACTION_DEADLINE,
decode(substr(api.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))) DESC_OF_GOODS,
SUM(DECODE(jzvtd.OFFSET_FLAG,''N'',
nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100),0)) TAXABLE_AMOUNT,
SUM(nvl(DECODE(jzvtd.tax_amt_funcl_curr,
0, jzvtd.tax_amt ,
NULL, jzvtd.tax_amt,
jzvtd.tax_amt_funcl_curr), 0)) TAX_AMOUNT,
SUM(DECODE(jzvtd.OFFSET_FLAG,''N'',
nvl(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt)*(JZVTD.tax_recovery_rate/100)
,0)) +
SUM(nvl(DECODE(jzvtd.tax_amt_funcl_curr,
0, jzvtd.tax_amt ,
NULL, jzvtd.tax_amt,
jzvtd.tax_amt_funcl_curr), 0)) INV_TOTAL_AMOUNT,
SUM(decode(jzvtd.tax_recoverable_flag, ''Y'',
nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt ))) DEDUCTABLE_AMOUNT,
jzvtd.tax_rate TAX_RATE,
APSS.VENDOR_SITE_ID
FROM jg_zz_vat_trx_details jzvtd, jg_zz_vat_rep_status jzvrs
, AP_SUPPLIER_SITES_ALL APSS, AP_SUPPLIERS APS, AP_INVOICES_ALL API,
zx_lines_det_factors zxdf
WHERE (substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3) in (''347'',''349'',''415'')
OR substr(jzvtd.document_sub_type,1,6) = ''MOD340'')
AND nvl(jzvtd.document_sub_type,''X'') <> ''MOD340_EXCL''
AND JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZVRS.reporting_status_id = JZVTD.reporting_status_id
AND JZVTD.extract_source_ledger = ''AP''
AND JZVRS.source = ''AP''
AND $FILTER_KEY$ $FILTER_OPER$ $FILTER_VALUE$
$340_PERIOD_KEY$ $340_PERIOD_KEY_FROM$ $340_PERIOD_OPER$ $340_PERIOD_KEY_TO$
AND JZVTD.trx_line_type NOT IN (''AWT'',''TAX'',''PREPAY'')
AND JZVTD.trx_line_class = ''EXPENSE REPORTS''
AND JZVTD.applied_from_line_id IS NULL
AND JZVTD.extract_source_ledger = ''AP''
AND JZVRS.source = ''AP''
AND JZVTD.BILLING_TRADING_PARTNER_ID = APS.VENDOR_ID
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND NVL(APS.FEDERAL_REPORTABLE_FLAG,''Y'')= ''Y''
AND APSS.TAX_REPORTING_SITE_FLAG = ''Y''
AND APSS.ORG_ID = $P_ORG_ID$
AND jzvtd.trx_id = api.invoice_id
AND jzvtd.trx_id = zxdf.trx_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND ( (decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) <> ''R'')
OR (decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) = ''R''
AND decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '',
substr(zxdf.user_defined_fisc_class,7,1) ) NOT IN (''P'',''I'') )
OR ((decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')) = ''R''
AND decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '',
substr(zxdf.user_defined_fisc_class,7,1) ) IN (''P'',''I'') )
AND jzvtd.reporting_code <> ''OFFSET'' ))
GROUP BY decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
NVL(JZVTD.merchant_party_taxpayer_id,
JZVTD.billing_tp_taxpayer_id)),
substrb(NVL(JZVTD.merchant_party_name,JZVTD.billing_tp_name),1,40),
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)),
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)),
decode(substr(api.document_sub_type,1,6), ''MOD340'', substr(api.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''R'',''415'',''S'',''349'',''R'')),
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ),
jzvtd.trx_date, jzvtd.tax_invoice_date, jzvtd.trx_number, jzvtd.doc_seq_value,
decode(substr(api.document_sub_type,1,8),''MOD340/U'', substr(api.document_sub_type,9,1)),
decode(substr(api.document_sub_type,1,8), ''MOD340/U'',''D''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''D'')),
api.global_attribute10,
decode(substr(api.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))),
jzvtd.tax_rate, APSS.VENDOR_SITE_ID ';
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1
, jg_info_v20 -- p_tax_year
, jg_info_v2 -- lc_taxpayer_id
, jg_info_v3 -- lc_company_name
, jg_info_v4 -- c_modelo_340.lc_tax_registration_number
, jg_info_v5 -- c_modelo_340.lc_customer_name
, jg_info_v6 -- lc_country
, jg_info_v7 -- c_modelo_340.lc_key_id
, jg_info_v8 -- c_modelo_340.lc_foreign_taxpayer_id
, jg_info_v9 -- c_modelo_340.lc_book_type
, jg_info_v10 -- c_modelo_340.lc_transaction_code
, jg_info_d1 -- c_modelo_340.ld_invoice_date
, jg_info_d2 -- c_modelo_340.ld_trx_date
, jg_info_n1 -- c_modelo_340.ln_tax_rate
, jg_info_n2 -- c_modelo_340.ln_taxable_amt
, jg_info_n3 -- c_modelo_340.ln_tax_amt
, jg_info_n4 -- c_modelo_340.ln_inv_total_amt
, jg_info_v11 -- c_modelo_340.lc_trx_num
, jg_info_v12 -- c_modelo_340.lc_doc_seq
, jg_info_n7 -- c_modelo_340.ln_deductable_amt
, jg_info_v13 -- c_modelo_340.c_intra_type
, jg_info_v14 -- c_modelo_340.lc_key_declared
, jg_info_v15 -- lc_country
, jg_info_v16 -- lc_trx_deadline
, jg_info_v17 -- c_modelo_340.lc_desc_of_goods
, jg_info_v21 -- lc_address_detail
, jg_info_v22 -- lc_city
, jg_info_v23 -- lc_postal_code
)
VALUES
( '340'
, p_tax_year
, lc_taxpayer_id
, lc_company_name
, lc_tax_registration_number
, lc_customer_name
, lc_country
, lc_key_id
, lc_foreign_taxpayer_id
, lc_book_type
, lc_transaction_code
, ld_invoice_date
, ld_trx_date
, ln_tax_rate
, ln_taxable_amt
, ln_tax_amt
, ln_inv_total_amt
, lc_trx_num
, lc_doc_seq
, ln_deductable_amt
, lc_intra_type
, lc_key_declared
, lc_country
, lc_trx_deadline
, lc_desc_of_goods
, lc_address_detail
, lc_city
, lc_postal_code
);
'SELECT decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
jzvtd.billing_tp_taxpayer_id) DECLARED_NIF,
substrb(jzvtd.billing_tp_name,1,40) DECLARED_NAME,
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)) KEY_ID, --BUG 8946271
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)) FISCAL_ID,
decode(substr(zxdf.document_sub_type,1,6), ''MOD340'', substr(zxdf.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''E'',''415'',''F'',''349'',''E'')) BOOK_TYPE,
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ) TRANSACTION_CODE,
jzvtd.trx_date ISSUE_DATE,
to_date(trx.global_attribute13,''YYYY/MM/DD HH24:MI:SS'') TRANSACTION_DATE,
jzvtd.trx_number INVOICE_IDENT,
jzvtd.doc_seq_value REGISTER_NUMBER,
decode(substr(zxdf.document_sub_type,1,8),''MOD340/U'',
substr(zxdf.document_sub_type,9,1)) INTRA_TYPE,
decode(substr(zxdf.document_sub_type,1,8), ''MOD340/U'',''R''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''R'')) KEY_OF_DECLARED,
trx.global_attribute10 TRANSACTION_DEADLINE,
decode(substr(zxdf.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(jzvtd.invoice_report_type,1,3),
''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))) DESC_OF_GOODS,
jzvtd.trx_line_number,
jzvtd.trx_line_id,
jzvtd.trx_id,
jzvtd.reporting_status_id,
DECODE(NVL(jzvtd.taxable_amt_funcl_curr , 0)
, 0 , jzvtd.taxable_amt
, jzvtd.taxable_amt_funcl_curr) TAXABLE_AMOUNT,
hzcsu.cust_acct_site_id
$YEAR_AMT_RECEIVED_CASH$
$TRANSMISSION_PROPERTY$
FROM jg_zz_vat_trx_details jzvtd, jg_zz_vat_rep_status jzvrs
, hz_cust_site_uses_all hzcsu ,hz_cust_acct_sites_all hzcas ,hz_cust_accounts hzca
, ZX_LINES_DET_FACTORS zxdf, ra_customer_trx_all trx
WHERE (substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3) in (''347'',''349'',''415'')
OR substr(zxdf.document_sub_type,1,6) = ''MOD340'')
AND nvl(zxdf.document_sub_type,''X'') <> ''MOD340_EXCL''
AND JZVRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = $P_VAT_REP_ENTITY_ID$
AND JZRS.source = ''AR'' )
AND $FILTER_KEY$ $FILTER_OPER$ $FILTER_VALUE$
$340_PERIOD_KEY$ $340_PERIOD_KEY_FROM$ $340_PERIOD_OPER$ $340_PERIOD_KEY_TO$
AND JZVTD.extract_source_ledger = ''AR''
AND JZVRS.source = ''AR''
AND JZVTD.trx_line_class <> ''DEBIT''
AND SUBSTR(NVL(JZVTD.tax_rate_vat_trx_type_code,''QQQQQQ''),1,3) <> ''RET''
AND JZVTD.BILLING_TRADING_PARTNER_ID = hzca.cust_account_id
AND hzca.cust_account_id = hzcas.cust_account_id
AND hzcsu.cust_acct_site_id = hzcas.cust_acct_site_id
AND upper(hzcsu.site_use_code) = ''LEGAL''
AND hzcsu.primary_flag = ''Y''
AND hzcsu.status = ''A''
AND hzcsu.ORG_ID = $P_ORG_ID$
AND jzvtd.trx_id = zxdf.trx_id
AND jzvtd.trx_line_id = zxdf.trx_line_id
AND jzvtd.trx_id = trx.customer_trx_id
GROUP BY decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',
jzvtd.billing_tp_taxpayer_id),
substrb(jzvtd.billing_tp_name,1,40),
je_es_modelo_ext_pkg.getKeyID(substrb(jzvtd.billing_tp_tax_reg_num,1,2)),
decode(substrb(jzvtd.billing_tp_tax_reg_num,1,2),''ES'',null,
substrb(jzvtd.billing_tp_taxpayer_id,1,17)),
decode(substr(zxdf.document_sub_type,1,6), ''MOD340'', substr(zxdf.document_sub_type,8,1),
decode(substr(zxdf.trx_business_category,(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3)
,''347'',''E'',''415'',''F'',''349'',''E'')) ,
decode( instr(zxdf.user_defined_fisc_class,''NONE'',7,1),7,'' '', substr(zxdf.user_defined_fisc_class,7,1) ),
jzvtd.trx_date,
to_date(trx.global_attribute13,''YYYY/MM/DD HH24:MI:SS''),
jzvtd.trx_number,
jzvtd.doc_seq_value,
decode(substr(zxdf.document_sub_type,1,8),''MOD340/U'',
substr(zxdf.document_sub_type,9,1)),
decode(substr(zxdf.document_sub_type,1,8), ''MOD340/U'',''R''
,decode(substr(zxdf.trx_business_category,
(instr(zxdf.trx_business_category,''MOD'',1,1)+3),3),''349'',''R'')),
trx.global_attribute10,
decode(substr(zxdf.document_sub_type,1,8)
,''MOD340/U'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35)
,decode(substr(jzvtd.invoice_report_type,1,3),
''349'',substrb(jzvtd.TRX_LINE_DESCRIPTION,1,35))),
jzvtd.trx_line_number,
jzvtd.trx_line_id,
jzvtd.trx_id,
jzvtd.reporting_status_id,
DECODE(NVL(jzvtd.taxable_amt_funcl_curr , 0)
, 0 , jzvtd.taxable_amt
, jzvtd.taxable_amt_funcl_curr),
hzcsu.cust_acct_site_id
$YEAR_AMT_RECEIVED_CASH_GRP$ ';
'SELECT sum(nvl(DECODE(tax_amt_funcl_curr,
0, tax_amt ,
NULL, tax_amt,
tax_amt_funcl_curr), 0)) TAX_AMOUNT,
tax_rate
FROM jg_zz_vat_trx_details
WHERE trx_line_id = :p_trx_line_id
AND trx_id = :p_trx_id
AND extract_source_ledger = ''AR''
AND reporting_status_id = :p_reporting_status_id
AND SUBSTR(NVL(tax_rate_vat_trx_type_code,''QQQQQQ''),1,3) <> ''RET''
GROUP BY tax_rate
ORDER BY tax_rate desc ';
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1
, jg_info_v20 -- p_tax_year
, jg_info_v2 -- lc_taxpayer_id
, jg_info_v3 -- lc_company_name
, jg_info_v4 -- c_modelo_340.lc_tax_registration_number
, jg_info_v5 -- c_modelo_340.lc_customer_name
, jg_info_v6 -- lc_country
, jg_info_v7 -- c_modelo_340.lc_key_id
, jg_info_v8 -- c_modelo_340.lc_foreign_taxpayer_id
, jg_info_v9 -- c_modelo_340.lc_book_type
, jg_info_v10 -- c_modelo_340.lc_transaction_code
, jg_info_d1 -- c_modelo_340.ld_invoice_date
, jg_info_d2 -- c_modelo_340.ld_trx_date
, jg_info_n1 -- c_modelo_340.ln_tax_rate
, jg_info_n2 -- c_modelo_340.ln_taxable_amt
, jg_info_n3 -- c_modelo_340.ln_tax_amt
, jg_info_n4 -- ln_inv_total_amt
, jg_info_v11 -- c_modelo_340.lc_trx_num
, jg_info_v12 -- c_modelo_340.lc_doc_seq
, jg_info_n8 -- ln_surcharge_rate
, jg_info_n9 -- ln_surcharge_amount
, jg_info_v13 -- c_modelo_340.c_intra_type
, jg_info_v14 -- c_modelo_340.lc_key_declared
, jg_info_v15 -- lc_country
, jg_info_v16 -- lc_trx_deadline
, jg_info_v17 -- c_modelo_340.lc_desc_of_goods
, jg_info_v21 -- lc_address_detail
, jg_info_v22 -- lc_city
, jg_info_v23 -- lc_postal_code
, jg_info_v39 -- ln_property_location_code
, jg_info_v19 -- l_ref_catastral
, jg_info_n6 --ln_cash_ received_amount
, jg_info_n28 --c_modelo_340.ln_year_of_amts_received_cash
, jg_info_n5 --c_modelo-340.ln_transmission_property_amt
)
VALUES
( '340'
, p_tax_year
, lc_taxpayer_id
, lc_company_name
, lc_tax_registration_number
, lc_customer_name
, lc_country
, lc_key_id
, lc_foreign_taxpayer_id
, lc_book_type
, lc_transaction_code
, ld_invoice_date
, ld_trx_date
, ln_tax_rate
, ln_taxable_amt
, ln_tax_amt
, ln_inv_total_amt
, lc_trx_num
, lc_doc_seq
, ln_surcharge_rate
, ln_surcharge_amount
, lc_intra_type
, lc_key_declared
, lc_country
, lc_trx_deadline
, lc_desc_of_goods
, lc_address_detail
, lc_city
, lc_postal_code
, ln_property_location_code
, l_ref_catastral
, ln_cash_received_amount
, ln_year_of_amts_received_cash
, ln_transmission_property_amt
);
SELECT sum(jg_info_n2) TAXABLE_AMT,
sum(jg_info_n3) TAX_AMT,
sum(jg_info_n4) INV_TOTAL_AMT
INTO ln_taxable_amt,
ln_tax_amt,
ln_inv_total_amt
FROM JG_ZZ_VAT_TRX_GT
WHERE nvl(jg_info_v30,'X') <> 'H';
UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n2 = ln_taxable_amt,
jg_info_n3 = ln_tax_amt,
jg_info_n4 = ln_inv_total_amt
WHERE jg_info_v30 = 'H';
SELECT SUM(count_group) INTO P_REC_COUNT FROM (
SELECT 1 count_group
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H'
GROUP BY jg_info_v11, jg_info_n1, jg_info_n8);
SELECT COUNT(*)
INTO P_REC_COUNT
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H';
/* Bug 8485057 apply minimum amounts to the selected transactions */
IF P_MODELO = '347'
THEN
IF G_DEBUG THEN
fnd_file.put_line(FND_FILE.LOG,'Count of detail records in JG_ZZ_VAT_TRX_GT, before applying minimum amount='||P_REC_COUNT);
SELECT COUNT(*)
INTO P_REC_COUNT
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H'
AND NVL(jg_info_n4,0) <> 1;
UPDATE JG_ZZ_VAT_TRX_GT SET JG_INFO_N30= P_REC_COUNT WHERE jg_info_v30='H';
UPDATE JG_ZZ_VAT_TRX_GT
SET jg_info_n11 = ( SELECT count(*)
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v1 <> '3'
AND NVL(jg_info_n4,0) <> 1
AND nvl(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_D
, jg_info_n12 = ( SELECT count(*)
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v1 = '3'
AND NVL(jg_info_n4,0) <> 1
AND nvl(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_I
, jg_info_n13 = ( Select Decode(G_Currency_Code,'EUR',
(Round(Sum(Nvl(Jg_Info_N2,Jg_Info_N1)),G_Cur_Precision)*100)
,round(SUM(nvl(jg_info_n2,jg_info_n1)),G_Cur_Precision)) ----rounded by ratnadeep for bug:16169794
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v1 <> '3'
AND NVL(jg_info_n4,0) <> 1) -- CP_TOTAL_AMT_DEC_D
, jg_info_n14 = ( SELECT SUM(jg_info_n2)
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v1 = '3'
AND NVL(jg_info_n4,0) <> 1
AND NVL(jg_info_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_I
, jg_info_n15 = ( SELECT count(*)
FROM JG_ZZ_VAT_TRX_GT
WHERE nvl(jg_info_v30,'X') <> 'H'
AND NVL(jg_info_n4,0) <> 1) -- CP_NO_OF_TYPE2
WHERE jg_info_v30 = 'H';
UPDATE JG_ZZ_VAT_TRX_GT
SET ( jg_info_n11 -- CS_IMP_VENTAS
, jg_info_n12 -- CS_NUMERO_VENTAS
, jg_info_n13 -- CS_IMP_MEDIACION
, jg_info_n14 -- CS_NUMERO_MEDIACION
, jg_info_n15 -- CS_IMP_COMPRAS
, jg_info_n16 -- CS_NUMERO_COMPRAS
-- FH: Added for all transaction codes
, jg_info_n17 -- CS_IMP_THIRD
, jg_info_n18 -- CS_NUMERO_THIRD
, jg_info_n19 -- CS_IMP_BIS
, jg_info_n20 -- CS_NUMERO_BIS
, jg_info_n21 -- CS_IMP_PUB
, jg_info_n22 -- CS_NUMERO_PUB
, jg_info_n23 -- CS_IMP_TAP
, jg_info_n24 -- CS_NUMERO_TAP
, jg_info_n25 -- CS_IMP_TAS
, jg_info_n26 -- CS_NUMERO_TAS
) =
( SELECT SUM( DECODE( jg_info_v1
, 'A', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_VENTAS
, SUM( DECODE( jg_info_v1
, 'A', 1
, 0 ) ) CP_NUMERO_VENTAS
, SUM( DECODE( jg_info_v1
, 'M', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_MEDIACION
, SUM( DECODE( jg_info_v1
, 'M', 1
, 0 ) ) CP_NUMERO_MEDIACION
, SUM( DECODE( jg_info_v1
, 'B', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_COMPRAS
, SUM( DECODE( jg_info_v1
, 'B', 1
, 0 ) ) CF_NUMERO_COMPRAS
-- FH: Added for all transaction codes
, SUM( DECODE( jg_info_v1
, 'C', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_THIRD
, SUM( DECODE( jg_info_v1
, 'C', 1
, 0 ) ) CP_NUMERO_THIRD
, SUM( DECODE( jg_info_v1
, 'D', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_BIS
, SUM( DECODE( jg_info_v1
, 'D', 1
, 0 ) ) CP_NUMERO_BIS
, SUM( DECODE( jg_info_v1
, 'E', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_PUB
, SUM( DECODE( jg_info_v1
, 'E', 1
, 0 ) ) CF_NUMERO_PUB
, SUM( DECODE( jg_info_v1
, 'F', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_TAS
, SUM( DECODE( jg_info_v1
, 'F', 1
, 0 ) ) CP_NUMERO_TAS
, SUM( DECODE( jg_info_v1
, 'G', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_TAP
, SUM( DECODE( jg_info_v1
, 'G', 1
, 0 ) ) CP_NUMERO_TAP
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H'
)
WHERE jg_info_v30 = 'H';