SELECT RECORD_TYPE,
MODEL,
YEAR,
DECLARING_NIF,
DECLARED_NIF,
LEGAL_REP_NIF,
DECLARED_NAME,
COUNTRY_CODE,
KEY_ID,
FISCAL_ID,
BOOK_TYPE,
TRANSACTION_CODE,
ISSUE_DATE,
TRANSACTION_DATE,
TAX_RATE ,
DECODE(SIGN(SUM(TAXABLE_AMT_ORIG)), -1, 'N', ' ')
|| SUBSTR(TO_CHAR(SUM(TAXABLE_AMT_ORIG), '00000000000D00'), 2, 11)
|| substrb(TO_CHAR(SUM(TAXABLE_AMT_ORIG), '00000000000D00'), 14, 2) TAXABLE_AMT,
decode(SIGN(sum(TAX_AMT_ORIG)), -1, 'N', ' ') || substr(to_char(sum(TAX_AMT_ORIG), '00000000000D00'), 2, 11)
|| substrb(to_char(sum(TAX_AMT_ORIG), '00000000000D00'), 14, 2) TAX_AMT,
decode(SIGN(sum(INV_TOTAL_AMT_ORIG)), -1, 'N', ' ') || substr(to_char(sum(INV_TOTAL_AMT_ORIG), '00000000000D00'), 2, 11)
|| substrb(to_char(sum(INV_TOTAL_AMT_ORIG), '00000000000D00'), 14, 2)INV_TOTAL_AMT,
decode(SIGN(nvl(sum(COST_TAX_AMT1),0)), -1, 'N', ' ') || substr(to_char(nvl(sum(COST_TAX_AMT1),0), '00000000000D00'), 2, 11)
|| substrb(to_char(nvl(sum(COST_TAX_AMT1),0), '00000000000D00'), 14, 2) COST_TAX_AMT,
INVOICE_IDENT,
REGISTER_NUMBER,
NUM_OF_INVOICES,
NUM_OF_RECORDS,
ACCUM_INT_IDENT,
decode(SIGN(nvl(sum(DEDUCTABLE_AMT),0)), -1, 'N', ' ') || substr(to_char(sum(DEDUCTABLE_AMT), '00000000000D00'), 2, 11)
|| substrb(to_char(sum(DEDUCTABLE_AMT), '00000000000D00'), 14, 2) DEDUCTABLE_AMT,
TRANSACTION_DATE_ORIG,
ISSUE_DATE_ORIG,
sum(TAXABLE_AMT_ORIG) TAXABLE_AMT_ORIG,
sum(TAX_AMT_ORIG) TAX_AMT_ORIG,
sum(INV_TOTAL_AMT_ORIG) INV_TOTAL_AMT_ORIG,
sum(TAX_RATE_ORIG) TAX_RATE_ORIG
, SUBSTR(TO_CHAR(SUM(PAYMENT_AMT_ORIG), '00000000000D00'), 2, 11)
|| substrb(TO_CHAR(SUM(PAYMENT_AMT_ORIG), '00000000000D00'), 14, 2) PAYMENT_AMT
,sum(PAYMENT_AMT_ORIG) PAYMENT_AMT_ORIG
,PAYMENT_DATE_ORIG
,PAYMENT_DATE
,PAYMENT_REFERENCE
,PAYMENT_METHOD_CODE
,DEFERRED_TRX
FROM
(SELECT '2' RECORD_TYPE
,jg_info_v1 MODEL
,jg_info_v20 YEAR
,jg_info_v2 DECLARING_NIF
,jg_info_v4 DECLARED_NIF
,null LEGAL_REP_NIF
,jg_info_v5 DECLARED_NAME
,jg_info_v6 COUNTRY_CODE
,jg_info_v7 KEY_ID
,jg_info_v8 FISCAL_ID
,jg_info_v9 BOOK_TYPE
,jg_info_v10 TRANSACTION_CODE
,to_char(jg_info_d1, 'YYYYMMDD') ISSUE_DATE
,to_char(jg_info_d2, 'YYYYMMDD') TRANSACTION_DATE
,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2) TAX_RATE
,sum(jg_info_n5) COST_TAX_AMT1
,jg_info_v11 INVOICE_IDENT
,jg_info_v12 REGISTER_NUMBER
,'1' NUM_OF_INVOICES
,decode (jg_info_v10,'C',row_number () over (partition by jg_info_v1
,jg_info_v20
,jg_info_v2
,jg_info_v4
,jg_info_v5
,jg_info_v6
,jg_info_v7
,jg_info_v8
,jg_info_v9
-- ,jg_info_v10
,to_char(jg_info_d1, 'YYYYMMDD')
,to_char(jg_info_d2, 'YYYYMMDD')
-- ,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2)
,jg_info_v11
,jg_info_v12
,to_char(jg_info_d2,'YYYY-MM-DD')
,to_char(jg_info_d1,'YYYY-MM-DD')
order by jg_info_v5,jg_info_v11,jg_info_v31 nulls first) ,1) NUM_OF_RECORDS -- Bug 16236159
,null ACCUM_INT_IDENT
, sum(jg_info_n7) DEDUCTABLE_AMT
,to_char(jg_info_d2,'YYYY-MM-DD') TRANSACTION_DATE_ORIG
,to_char(jg_info_d1,'YYYY-MM-DD') ISSUE_DATE_ORIG
,sum(jg_info_n2) TAXABLE_AMT_ORIG
,sum(jg_info_n3) TAX_AMT_ORIG
,sum(jg_info_n4) INV_TOTAL_AMT_ORIG
,jg_info_n1 TAX_RATE_ORIG
,to_char(jg_info_d3,'YYYY-MM-DD') PAYMENT_DATE_ORIG
,to_char(jg_info_d3, 'YYYYMMDD') PAYMENT_DATE
,jg_info_v33 PAYMENT_REFERENCE
,sum(nvl(jg_info_n11,0)) PAYMENT_AMT_ORIG
,jg_info_v35 PAYMENT_METHOD_CODE
,jg_info_v36 DEFERRED_TRX
FROM JG_ZZ_VAT_TRX_GT
WHERE NVL(jg_info_v30, 'X') <> 'H'
AND jg_info_v1 = '340'
AND jg_info_v9 in ('R','S')
AND :P_MODELO = '340'
GROUP BY '2'
,jg_info_v1
,jg_info_v20
,jg_info_v2
,jg_info_v4
,null
,jg_info_v5
,jg_info_v6
,jg_info_v7
,jg_info_v8
,jg_info_v9
,jg_info_v10
,to_char(jg_info_d1, 'YYYYMMDD')
,to_char(jg_info_d2, 'YYYYMMDD')
,substr(to_char(jg_info_n1, '000D00'), 2, 3) || substr(to_char(jg_info_n1, '000D00'), 6, 2)
,jg_info_v11
,jg_info_v31 -- Bug 16236159
,jg_info_v32 -- Bug 16236159
,jg_info_v12
,'1'
,null
,to_char(jg_info_d2,'YYYY-MM-DD')
,to_char(jg_info_d1,'YYYY-MM-DD')
,jg_info_n1 --19350438
,to_char(jg_info_d3, 'YYYYMMDD')
,to_char(jg_info_d3,'YYYY-MM-DD')
,jg_info_v33
,jg_info_v35
,jg_info_v36
HAVING (sum(jg_info_n2) <> 0 OR sum(jg_info_n3) <> 0 OR sum(jg_info_n4) <> 0 OR sum(nvl(jg_info_n11,0)) <> 0 )
ORDER BY jg_info_v11 desc
) T_TABLE
group by
RECORD_TYPE,
MODEL,
YEAR,
DECLARING_NIF,
DECLARED_NIF,
LEGAL_REP_NIF,
DECLARED_NAME,
COUNTRY_CODE,
KEY_ID,
FISCAL_ID,
BOOK_TYPE,
TRANSACTION_CODE,
ISSUE_DATE,
TRANSACTION_DATE,
TAX_RATE,
INVOICE_IDENT,
REGISTER_NUMBER,
NUM_OF_INVOICES,
NUM_OF_RECORDS,
ACCUM_INT_IDENT,
TRANSACTION_DATE_ORIG,
ISSUE_DATE_ORIG
,PAYMENT_DATE_ORIG
,PAYMENT_DATE
,PAYMENT_REFERENCE
,PAYMENT_METHOD_CODE
,DEFERRED_TRX
HAVING (sum(TAXABLE_AMT_ORIG) <> 0 OR sum(TAX_AMT_ORIG) <> 0 OR sum(INV_TOTAL_AMT_ORIG) <> 0 OR sum(PAYMENT_AMT_ORIG) <> 0) |
