SELECT JG.accounting_date ACCOUNTING_DATE
,JG.doc_seq_name ||'/'|| JG.doc_seq_value TRX_DOC_SEQUENCE_VALUE
,JG.trx_date TRX_DATE
,JG.trx_number TRX_NUMBER
,JG.billing_tp_name BILLING_TP_NAME
,JG.billing_tp_taxpayer_id BILLING_TP_TAXPAYER_ID
,SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) NET_AMOUNT_ORIG -- Modified for bug 7457763
,JG.tax_rate TAX_RATE
,JG.tax_rate_code TAX_CODE
,JG.tax_rate_code_description TAX_DESCRIPTION
,SUM(NVL(JG.tax_amt,JG.tax_amt_funcl_curr)) TAX_AMOUNT_ORIG
,JG.trx_line_class INVOICE_TYPE
,HZL.address1||' '||HZL.address2||' '||HZL.address3 C_DIRECC
,HZL.postal_code||' '||HZL.city C_CIUDAD
,HZL.country BILLING_TP_COUNTRY
,JG.territory_short_name TERRITORY_NAME
,JG.billing_tp_tax_reg_num BILLING_TP_TAX_REG_NUM
,JG.reporting_code OFFSET_TAX_CODE_ID
FROM jg_zz_vat_trx_details JG
,jg_zz_vat_rep_status JZVRS
,hz_locations HZL
,hz_party_sites HPS
WHERE JG.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')
--JZVRS.reporting_status_id = JG.reporting_status_id
AND JZVRS.vat_reporting_entity_id = :P_VAT_REP_ENTITY_ID
AND JG.billing_tp_site_id = HPS.party_site_id (+) --brathod, changed to outer join
AND HPS.location_id = HZL.location_id (+) --brathod, changed to outer join
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_PERIOD_NAME) 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_PERIOD_NAME_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)
AND JG.gl_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date
AND JZVRS.source = 'AP'
AND (:P_BAL_SEGMENT IS NULL OR JG.trx_tax_balancing_segment = :P_BAL_SEGMENT)
/* UT Bug#5308180 AND JG.tax_invoice_date BETWEEN JZVRS.period_start_date AND JZVRS.period_end_date */
AND nvl(JG.reporting_code, 'AZX') NOT LIKE 'OFFSET'
AND ( JG.offset_tax_rate_code IS NOT NULL or JG.self_assessed_flag ='Y')
/* UT Bug#5308180 AND JG.tax_type_code = NVL(:P_TAX_TYPE,JG.tax_type_code) */
AND (:P_TAX_TYPE is null or JG.reporting_code = :P_TAX_TYPE)
AND :P_REPORT_NAME = 'JEESDOCE'
AND JG.trx_id IN (SELECT JG1.trx_id
FROM jg_zz_vat_trx_details JG1
WHERE ( JG1.reporting_code LIKE 'OFFSET' OR JG1.self_assessed_flag ='Y')
AND JG.trx_id = JG1.trx_id
AND JG.application_id = JG1.application_id
AND JG.event_class_code = JG1.event_class_code
AND JG.entity_code = JG1.entity_code)
AND JG.extract_source_ledger ='AP'
GROUP BY
JG.accounting_date
,JG.doc_seq_name ||'/'|| JG.doc_seq_value
,JG.trx_date
,JG.trx_number
,JG.billing_tp_name
,JG.billing_tp_taxpayer_id
,JG.tax_rate
,JG.tax_rate_code
,JG.tax_rate_code_description
,JG.trx_line_class
,HZL.address1||' '||HZL.address2||' '||HZL.address3
,HZL.postal_code||' '||HZL.city
,HZL.country
,JG.territory_short_name
,JG.billing_tp_tax_reg_num
,JG.reporting_code
HAVING SUM((NVL(JG.taxable_amt, JG.taxable_amt_funcl_curr))*(NVL(TAX_RECOVERY_RATE,0)/100)) <> 0
ORDER BY
JG.trx_date
,JG.trx_number |
