SELECT GLP.period_name period_name
,GLP.period_num period_num
,GLP.period_year period_year
,JG_INFO_V22 doc_sequence_value
,TO_CHAR(jg_info_d1,'DD-Mon-RRRR') tax_date
,TO_CHAR(jg_info_d3,'DD-Mon-RRRR') accounting_date
,TO_CHAR(jg_info_d2,'DD-Mon-RRRR') invoice_date
,jg_info_v1 invoice_num
,jg_info_v2 vendor_name
,jg_info_n3 tax_rate
,jg_info_v3 name
,jg_info_n5 invoice_id
,jg_info_v4 invoice_type
,jg_info_v5 currency
,jg_info_v6 tax_code_desc
,SUM(jg_info_n2) taxable_amount
,SUM(jg_info_n4) tax
,SUM(jg_info_n6) rec_tax
,SUM(jg_info_n7) non_rec_tax
,SUM(jg_info_n8) functional_amount
,SUM(jg_info_n10) Transaction_amount
,SUM(jg_info_n13) Taxable_entered_amount
,jg_info_n9 acct_flexfield
,jg_info_v7 tax_code_vat_trx_type
,jg_info_v8 merchant_number
,jg_info_v10 doc_seq_name
,jg_info_v11 tax_reg_num
,jg_info_v13 tax_code_vat_trx_type_meaning
,jg_info_v12 tax_code_vat_trx_type_desc
,jg_info_v14 tax_code
/* UT TESTING ,jg_info_n11 . No value is getting populated into this field */
,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT') tax_code_type_code1
,JG_ZZ_SUMMARY_AP_PKG.CF_Tax_Code_Type_Code(decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')) Tax_Code_Type_Cod_Is_Offset
,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT') tax_code_type_code2
,jg_info_n14 non_rec_unpaid_amt
,jg_info_v17 offset_flag
,jg_info_v18 offset_tax_rate_code
,SUM(DECODE(jg_info_v17,'Y', jg_info_n13 , DECODE(jg_info_v15, 'OFFSET', jg_info_n13, DECODE(jg_info_n15,-9999,jg_info_n13,jg_info_n10) ))) CF_trans_VAT_or_Offset
,SUM(DECODE(jg_info_v17,'Y',jg_info_n2, DECODE(jg_info_v15, 'OFFSET',jg_info_n2, DECODE(jg_info_n15,-9999,jg_info_n2,jg_info_n8) ))) CF_func_VAT_or_Offset
FROM JG_ZZ_VAT_TRX_GT
,GL_PERIODS GLP
,jg_zz_vat_rep_status JZVRS
WHERE jg_info_v9 = 'M'
AND JZVRS.vat_reporting_entity_id = :P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = :P_TAX_CALENDAR_PERIOD
AND jg_info_v16 = jzvrs.tax_calendar_period
AND GLP.period_set_name = jzvrs.tax_calendar_name
AND GLP.adjustment_period_flag = 'N' /* Added for bug 5915657 */
AND jg_info_d1 between glp.start_date and glp.end_date
AND jzvrs.source = 'AP'
AND ((:P_CALLING_REPORT = 'JGZZAPVR' ) OR :P_CALLING_REPORT = 'JEHRVITR') /* UT TEST */
AND NVL(jg_info_v30,'#') <> 'H'
GROUP BY decode(jg_info_v15, 'OFFSET','XOFFSET','VAT') /* tax_code_type_code1 */
,period_num
,period_year
,period_name
,jg_info_v12 /*tax_code_vat_trx_type_desc*/
,jg_info_v13 /*tax_code_vat_trx_type_meaning*/
,jg_info_v14 /* tax_code */
,jg_info_v6 /* tax_code_desc */
,jg_info_v10 /* doc_seq_name */
,JG_INFO_V22 /* doc sequence value */
,jg_info_v4 /* invoice type */
,jg_info_n5 /* invoice id */
,jg_info_v5 /* currency */
,jg_info_d1 /* tax date */
,jg_info_d3 /*accounting date */
,jg_info_d2 /* invoice date */
,jg_info_v1 /* invoice num */
,jg_info_v2 /* vendor name */
,jg_info_n3 /* tax rate */
,jg_info_v3 /* name */
-- commented for bug 6884757,jg_info_n2 /* taxable_amount */
,jg_info_n9 /*acct_flexfield*/
,jg_info_v7 /*tax_code_vat_trx_type */
,jg_info_v8 /*merchant_number*/
/* UT Bug#5258868 ,jg_info_n11 */
,jg_info_v11 /*tax_reg_num */
/* UT Bug#5258868 ,jg_info_n11 */
,jg_info_n14 /* non_rec_unpaid_amt */
,JG_ZZ_SUMMARY_AP_PKG.CF_Tax_Code_Type_Code(decode(jg_info_v15, 'OFFSET','XOFFSET','VAT'))
,decode(jg_info_v15, 'OFFSET','XOFFSET','VAT')
,jg_info_v17 /* offset_flag */
,jg_info_v18 /*offset_tax_rate_code*/
ORDER BY tax_code_type_code1
,Tax_Code_Type_Cod_Is_Offset
,period_year desc
,period_num desc
,jg_info_v7
,tax_code_vat_trx_type_meaning
,tax_code_vat_trx_type_desc
,tax_code_desc
,tax_code
,tax_date
,doc_sequence_value
,invoice_num |
