[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
, assa.city CITY
, SUBSTR(assa.address_line1,1,35)||' '||
SUBSTR(assa.address_line2,1,35)||' '||
SUBSTR(assa.address_line3,1,35) ADDRESS_DETAIL
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 DECODE(HL.country
, 'ES', SUBSTR(HL.postal_code,1,2)||'000'
, '99'||FT.eu_code) postal_code
, SUBSTR(HL.town_or_city,1,24) city
, SUBSTR(HL.address_line_1,1,2) ||
SUBSTR(HL.address_line_2,1,23) ||
LPAD(SUBSTR(HL.address_line_3,1,length(HL.address_line_3) -
NVL(LENGTH(LTRIM(TRANSLATE(HL.address_line_3, '123456789','000000000'),'0')),0)),5,'0') address_detail
FROM hz_cust_acct_sites_all HCAS
, hz_party_sites HPS
, hr_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 DECODE(HL.country
, 'ES', SUBSTR(HL.postal_code,1,2)||' '
, '99'||FT.territory_code||' ') codigo_postal
, SUBSTR(HL.city,1,24) ref_catastral
, 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 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
SUM( NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt ) )
+ SUM( NVL(JZVTD.tax_amt_funcl_curr,JZVTD.tax_amt ) ) trx_line_amt
-- SUM( NVL(JZVTD.trx_line_amt,0 ) ) trx_line_amt
, NVL(JZVTD.tax_rate_id,0) tax_rate_id
, DECODE(SUBSTR(HL.country, 1, 25)
, 'ES',SUBSTR(HL.postal_code,1,5)
, '99'||FT.eu_code) postcode
, SUBSTR(HL.global_attribute2,1,2) global_attribute2
, 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.global_attribute3,1,2) global_attribute3
, SUBSTR(HL.global_attribute4,1,2) global_attribute4
, SUBSTR(HL.global_attribute5,1,2) global_attribute5
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
, hz_cust_site_uses_all HCSU
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
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.trx_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
AND JZVTD.invoice_report_type IN ( -- 415 -- AR
'347'
, '415_347'
, '415_347PR'
, '347PR'
)
GROUP BY NVL(JZVTD.tax_rate_id,0)
, DECODE(SUBSTR(HL.country, 1, 25)
, 'ES',SUBSTR(HL.postal_code,1,5)
, '99'||FT.eu_code)
, SUBSTR(HL.global_attribute2,1,2)
, 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.global_attribute3,1,2)
, SUBSTR(HL.global_attribute4,1,2)
, SUBSTR(HL.global_attribute5,1,2);
SELECT DECODE( G_CURRENCY_CODE, 'EUR', (arr_rec.trx_line_amt*100), arr_rec.trx_line_amt)
INTO arr_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_n2 -- importe -- sum_trx_line_amt
, jg_info_v11 -- nombre -- cust name
, jg_info_v12 -- nif -- cust tax ref
, jg_info_v25 -- sigla -- address1
, jg_info_v2 -- municipio -- global_attribute2
, 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 --
, jg_info_v21 -- flag_arrenda -- 'Y'
, jg_info_v3 -- escalera -- global_attribute3
, jg_info_v4 -- piso -- global_attribute4
, jg_info_v5 -- puerta -- global_attribute5
, jg_info_v19 -- ref_catastral -- city
, jg_info_v22 -- p_print_year --p_tax_calender_year
, jg_info_v23 -- legal entity name
)
VALUES
('3'
, arr_rec.trx_line_amt
, p_customer_name
, p_cust_tax_reg_num
, arr_rec.address1 --v_sg
, arr_rec.global_attribute2 --v_municipio
, arr_rec.postcode --v_codigo_postal,
, 'S' --fin_ind
, arr_rec.address2 --v_via_publica,
, arr_rec.address3 --v_numero,
, NULL --comentario,
,'Y' --flag_arrenda,
, arr_rec.global_attribute3 --v_escalera,
, arr_rec.global_attribute4 --v_piso,
, arr_rec.global_attribute5 --v_puerta,
, arr_rec.city --v_ref_catastral,
, 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 = P_DISPLAY_PERIOD
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 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
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 SUM(ROUND(DECODE( NVL(JVGT.jg_info_n8,0)
,0 , JVGT.jg_info_n6
, JVGT.jg_info_n8
)
, g_cur_precision)
)
INTO V_PREPAY_APPLIED
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 SUM(ROUND( NVL(JVGT.taxable_amt_funcl_curr,JVGT.taxable_amt ) , 2) )
+ SUM(ROUND( NVL(JVGT.tax_amt_funcl_curr,JVGT.tax_amt ) , 2) ) arrenda_amount
-- SUM(ROUND(NVL(JVGT.trx_line_amt,0), 2)) arrenda_amount
INTO V_PREPAY_APPLIED
FROM JG_ZZ_VAT_TRX_DETAILS JVGT, JG_ZZ_VAT_REP_STATUS JZVRS
--, hz_cust_acct_sites_all HCAS
--, hz_party_sites HPS
--, hz_locations HL
--, fnd_territories FT
, hz_cust_site_uses_all HCSU
WHERE JVGT.billing_trading_partner_id = p_customer_id
--AND HCAS.cust_acct_site_id = JVGT.billing_tp_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 = JVGT.billing_tp_address_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 JVGT.extract_source_ledger = 'AR'
AND JVGT.event_class_code not in ('DEBIT_MEMO', 'APP', 'ADJ')
-- AND JVGT.accounting_date IS NOT NULL --bug5557860
-- AND JVGT.posted_flag = 'Y' /** author: brathod; removed posted_flag checking as it is not relevent in R12*/
SELECT /*+ NO_REWRITE */
$TAX_REGISTRATION_NUM$ TAX_REGISTRATION_NUM
, substr(billing_tp_name,1,80) CUSTOMER_NAME
, $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'',DECODE(JZVTD.tax_recoverable_flag
,''N'',DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
, 0 ,JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr)),0)
, DECODE(NVL(JZVTD.taxable_amt_funcl_curr , 0)
, 0 ,JZVTD.taxable_amt
, JZVTD.taxable_amt_funcl_curr ))
)
+
SUM(DECODE(JZVTD.extract_source_ledger
,''AP'', DECODE(NVL(JZVTD.tax_amt_funcl_curr,0)
, 0, JZVTD.tax_amt,
JZVTD.tax_amt_funcl_curr) ) )
SUM_TAXABLE_AMT
--, SUM( NVL(trx_line_amt,0)) SUM_TRX_LINE_AMT
, SUM( NVL(JZVTD.taxable_amt_funcl_curr,JZVTD.taxable_amt ) )
+ SUM( 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_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 )
$CORRECTION_TRX_GRP_COL$ $PROPERTY_FLAG_GRP_COL$ $MODELO_SPECIFIC_GRP_FILTER$ ';
/* Updated filter to include P_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 )) >= $P_MIN_VALUE$ '; --0
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
,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 -- P_MIN_VALUE
,jg_info_d1 -- ld_period_start_date
,jg_info_d2 -- ld_period_end_date
,jg_info_v30 -- Header record indicator
)
VALUES
(lc_func_curr_code
,lc_rep_legal_entity
,lc_taxpayer_id
,lc_company_name
,lc_trx_num
,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_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
,P_MIN_VALUE
,ld_period_start_date
,ld_period_end_date
,'H');
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.billing_tp_name,JZVTD.merchant_party_name) CUSTOMER_NAME
, APSS.VENDOR_SITE_ID CUSTOMER_ADDRESS_ID
, JZVTD.bill_from_party_id BILLING_TRADING_PARTNER_ID
, SUM (ROUND (DECODE (JZVTD.taxable_amt_funcl_curr,
0, JZVTD.taxable_amt,
NULL, JZVTD.taxable_amt,
JZVTD.taxable_amt_funcl_curr)
/** author:brathod
+
NVL (DECODE (alc.base_amount,
0, alc.amount,
NULL, alc.amount,
alc.base_amount), 0)
*/
)) SUM_TAXABLE_AMT
, 0 SUM_TRX_LINE_AMT
, 0 NCORRECTION_AMOUNT
, 0 CORRECTION_AMOUNT
, NULL
, NULL
, NULL
, NULL
, DECODE (JZVTD.invoice_report_type
, ''347'', ''N''
, ''347PR'', ''Y''
, ''415_347'', ''N''
, ''415_347PR'',''Y'') PROPERTY_RENTAL
FROM jg_zz_vat_rep_status JZVRS
, jg_zz_vat_trx_details JZVTD
, ap_suppliers aps
, ap_supplier_sites_all apss
/**
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';
-- 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_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_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_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 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';
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_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
)
VALUES
( lc_clave_operation
, ln_arrenda_amount
, 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
) ;
INSERT INTO JG_ZZ_VAT_TRX_GT
( jg_info_v1 -- lc_clave_operation
, jg_info_n2 -- ln_sum_trx_line_amt
, 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
)
VALUES
( lc_clave_operation
, ln_sum_trx_line_amt
, 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
) ;
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'));
/* 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'));
SELECT COUNT(*)
INTO P_REC_COUNT
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H';
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_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_v30,'X') <> 'H' ) -- CP_TOTAL_DEC_I
, jg_info_n13 = ( SELECT DECODE(G_CURRENCY_CODE
, 'EUR' ,(SUM(jg_info_n2)*100)
, SUM(jg_info_n2))
FROM JG_ZZ_VAT_TRX_GT
WHERE jg_info_v1 <> '3') -- 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_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' ) -- 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
) =
( SELECT SUM( DECODE( jg_info_v1
, 'V', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_VENTAS
, SUM( DECODE( jg_info_v1
, 'V', 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
, 'C', NVL(jg_info_n1, jg_info_n2)
, 0 ) ) CP_IMP_COMPRAS
, SUM( DECODE( jg_info_v1
, 'C', 1
, 0 ) ) CF_NUMERO_COMPRAS
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30,'X') <> 'H'
)
WHERE jg_info_v30 = 'H';