SELECT det.trx_id trx_id,
det.trx_number Invoice_ID,
NVL(det.tax_rate, 0) Tax_Rate,
DECODE(ext.ATTRIBUTE4, NULL, ' ',
LPAD(ext.ATTRIBUTE4, 1, ' ')) DGI_Transaction_code ,
sum(NVL(ext.numeric9,0)) VAT_TAX_AMT ,
sum(NVL(ext.NUMERIC10,0)) taxable_amt,
sum(NVL(ext.NUMERIC3,0)) vat_per_amt,
sum(NVL(ext.NUMERIC7,0)) fed_per_amt,
sum(NVL(ext.NUMERIC2,0)) exmpt_amt,
sum(NVL(ext.numeric6,0)) excs_amt,
sum(NVL(ext.numeric8,0)) non_taxable_amount,
sum(NVL(ext.numeric12,0)) doc_amt_non,
TO_CHAR(det.trx_date, 'YYYYMMDD') Doc_Date,
LPAD(substr(nvl(ext.gdf_ap_invoices_att13,'0'),1,2),2, '0') DGI_doc_Type_code,
rpad(to_char(det.gl_date,'YYYYMMDD'),8,' ')
||lpad(nvl(det.port_of_entry_code,'0'),3,'0')
||' ' Cust_issue_dtl,
det.tax_invoice_number Custom_issue_num,
lpad(NVL(ext.attribute21,'0'),2,'0')
||LPAD(det.BILLING_TP_TAXPAYER_ID,11,'0')
||RPAD(det.BILLING_TP_NAME, 30, ' ') Supplier_dtl,
NVL(SUBSTR(det.GDF_FND_CURRENCIES_ATT1, 1, 3), ' ') Currency_Code,
nvl(det.CURRENCY_CONVERSION_RATE,1) Exchange_Rate ,
DECODE(ext.attribute19, NULL, '00000000000000',
LPAD(ext.attribute19, 14, '0'))
||lpad(NVL(TO_CHAR(TO_DATE(ext.attribute23,
'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD'),' '),8,' ') CAI_INFO,
LPAD(NVL(ext.attribute22,'00'), 2, '0') Sup_VAT_Regn_status_code ,
sum(NVL(ext.NUMERIC5,0)) mun_amt,
sum(abs(NVL(ext.NUMERIC9,0))) tot_vat,
decode(ext.attribute20,'Y', 'C',' ') fisc_prn,
NVL(det.cancel_flag,'N') status,
sum(NVL(ext.NUMERIC4,0)) pro_amt,
det.tax_type_code tax_type_code,
JL_JLARPPFF_XMLP_PKG.cf_vat_amtformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) )) CF_vat_amt,
JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) )) CF_tot_doc_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_exmpt_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC2 , 0 ) )) CF_exmpt_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amt_numformula0004(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) )) CF_fed_per_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula0006(JL_JLARPPFF_XMLP_PKG.cf_fed_per_amt_numformula0004(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) ))) CF_fed_per_amt,
JL_JLARPPFF_XMLP_PKG.cf_taxable_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC10 , 0 ) )) CF_taxable_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_vat_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) )) CF_VAT_per_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_exc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_mun_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) )) CF_mun_per_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_pro_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) )) CF_pro_per_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) ))) CF_vat_tax_amt,
JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_num,
JL_JLARPPFF_XMLP_PKG.cf_exmpt_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exmpt_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC2 , 0 ) ))) CF_exmpt_amt,
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) ))) CF_vat_per_amt,
JL_JLARPPFF_XMLP_PKG.cf_mun_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_mun_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) ))) CF_mun_per_amt,
JL_JLARPPFF_XMLP_PKG.cf_pro_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_pro_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) ))) CF_pro_per_amt,
JL_JLARPPFF_XMLP_PKG.cf_exc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) ))) CF_exc_amt,
JL_JLARPPFF_XMLP_PKG.cf_taxable_amtformula(JL_JLARPPFF_XMLP_PKG.cf_taxable_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC10 , 0 ) ))) CF_taxable_amt,
JL_JLARPPFF_XMLP_PKG.cf_blank_chrformula() CF_blank_chr,
JL_JLARPPFF_XMLP_PKG.cf_vat_rate_qtyformula(det.trx_id) CF_vat_rate_qty,
JL_JLARPPFF_XMLP_PKG.cf_rec_type1formula(sum ( NVL ( ext.numeric8 , 0 ) ),
JL_JLARPPFF_XMLP_PKG.cf_rec_countformula(),
NVL ( det.cancel_flag , 'N' ), TO_CHAR ( det.trx_date , 'YYYYMMDD' ), LPAD ( substr ( nvl ( ext.gdf_ap_invoices_att13 , '0' ) , 1 , 2 ) , 2 , '0' ), decode ( ext.attribute20 , 'Y' , 'C' , ' ' ),
JL_JLARPPFF_XMLP_PKG.cf_point_of_saleformula(det.trx_number),
JL_JLARPPFF_XMLP_PKG.cf_doc_numformula(det.trx_number),
rpad ( to_char ( det.gl_date , 'YYYYMMDD' ) , 8 , ' ' ) || lpad ( nvl ( det.port_of_entry_code , '0' ) , 3 , '0' ) || ' ', :CF_cust_num_valdigit,
lpad ( NVL ( ext.attribute21 , '0' ) , 2 , '0' ) || LPAD ( det.BILLING_TP_TAXPAYER_ID , 11 , '0' ) || RPAD ( det.BILLING_TP_NAME , 30 , ' ' ), DECODE ( ext.attribute19 , NULL , '00000000000000' , LPAD ( ext.attribute19 , 14 , '0' ) ) || lpad ( NVL ( TO_CHAR ( TO_DATE ( ext.attribute23 , 'YYYY/MM/DD HH24:MI:SS' ) , 'YYYYMMDD' ) , ' ' ) , 8 , ' ' ), NVL ( det.tax_rate , 0 ),
JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_exmpt_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exmpt_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC2 , 0 ) ))),
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) ))),
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amtformula0006(JL_JLARPPFF_XMLP_PKG.cf_fed_per_amt_numformula0004(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) ))),
JL_JLARPPFF_XMLP_PKG.cf_pro_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_pro_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_mun_per_amtformula(JL_JLARPPFF_XMLP_PKG.cf_mun_per_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_exc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_exc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) ))),
LPAD ( NVL ( ext.attribute22 , '00' ) , 2 , '0' ), NVL ( SUBSTR ( det.GDF_FND_CURRENCIES_ATT1 , 1 , 3 ) , ' ' ),
JL_JLARPPFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.CURRENCY_CONVERSION_RATE , 1 )),
JL_JLARPPFF_XMLP_PKG.cf_vat_rate_qtyformula(det.trx_id),
DECODE ( ext.ATTRIBUTE4 , NULL , ' ' , LPAD ( ext.ATTRIBUTE4 , 1 , ' ' ) ),
JL_JLARPPFF_XMLP_PKG.cf_taxable_amtformula(JL_JLARPPFF_XMLP_PKG.cf_taxable_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC10 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_vat_rateformula(NVL ( det.tax_rate , 0 )),
JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amtformula(JL_JLARPPFF_XMLP_PKG.cf_vat_tax_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric9 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_vat_countformula(det.trx_number),
JL_JLARPPFF_XMLP_PKG.cf_doc_amt_chrformula(JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) ,
JL_JLARPPFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )),
JL_JLARPPFF_XMLP_PKG.cf_rate_qtyformula(det.trx_number),
JL_JLARPPFF_XMLP_PKG.cf_dgi_tran_codeformula(det.trx_number)) CF_rec_type1,
JL_JLARPPFF_XMLP_PKG.cf_vat_rateformula(NVL ( det.tax_rate , 0 )) CF_vat_rate,
JL_JLARPPFF_XMLP_PKG.cf_doc_amt_chrformula(JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) CF_doc_amt_chr,
JL_JLARPPFF_XMLP_PKG.cf_non_blankformula() CF_non_blank,
JL_JLARPPFF_XMLP_PKG.cf_point_of_saleformula(det.trx_number) CF_point_of_sale,
JL_JLARPPFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.CURRENCY_CONVERSION_RATE , 1 )) CF_exchange_rate,
JL_JLARPPFF_XMLP_PKG.cf_doc_numformula(det.trx_number) CF_doc_num,
JL_JLARPPFF_XMLP_PKG.cf_fiscal_printerformula() CF_fiscal_printer,
JL_JLARPPFF_XMLP_PKG.cf_cust_num_valdigitformula(det.tax_invoice_number) CF_cust_num_valdigit,
JL_JLARPPFF_XMLP_PKG.cf_tot_vat_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( abs ( NVL ( ext.NUMERIC9 , 0 ) ) )) CF_tot_vat_amount,
JL_JLARPPFF_XMLP_PKG.cf_non_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amount,
JL_JLARPPFF_XMLP_PKG.cf_excise_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric6 , 0 ) )) CF_excise_amount,
JL_JLARPPFF_XMLP_PKG.cf_pro_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC4 , 0 ) )) CF_pro_amount,
JL_JLARPPFF_XMLP_PKG.cf_mun_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC5 , 0 ) )) CF_mun_amount,
JL_JLARPPFF_XMLP_PKG.cf_fed_per_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC7 , 0 ) )) CF_FED_PER_AMOUNT,
JL_JLARPPFF_XMLP_PKG.cf_vat_per_amountformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.NUMERIC3 , 0 ) )) CF_vat_per_amount,
JL_JLARPPFF_XMLP_PKG.cf_exempt_amountformula(sum ( NVL ( ext.NUMERIC2 , 0 ) )) CF_exempt_amount,
JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amountformula() CF_tot_doc_amount,
JL_JLARPPFF_XMLP_PKG.cf_rate_qtyformula(det.trx_number) CF_rate_qty,
JL_JLARPPFF_XMLP_PKG.cf_vat_countformula(det.trx_number) CF_vat_count,
JL_JLARPPFF_XMLP_PKG.cf_dgi_tran_codeformula(det.trx_number) CF_DGI_TRAN_CODE,
JL_JLARPPFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_chr,
JL_JLARPPFF_XMLP_PKG.cf_doc_amt_nonformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) )) CF_doc_amt_non,
JL_JLARPPFF_XMLP_PKG.cf_rec_countformula() CF_rec_count,
JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amtformula(JL_JLARPPFF_XMLP_PKG.cf_tot_doc_amt_numformula(NVL ( det.cancel_flag , 'N' ), sum ( NVL ( ext.numeric12 , 0 ) ))) CF_tot_doc_amt
FROM zx_rep_trx_jx_ext_t ext,
zx_rep_context_t con,
zx_rep_trx_detail_t det
WHERE con.request_id = :p_conc_request_id
AND det.request_id = con.request_id
AND det.rep_context_id = con.rep_context_id
AND ext.detail_tax_line_id = det.detail_tax_line_id
group by det.trx_id,
det.trx_number,
det.tax_rate,
ext.ATTRIBUTE4,
det.trx_date,
det.gl_date,
det.tax_invoice_number,
ext.attribute19,
det.port_of_entry_code,
ext.gdf_ap_invoices_att13,
det.BILLING_TP_TAXPAYER_ID,
det.BILLING_TP_NAME,
det.GDF_FND_CURRENCIES_ATT1,
det.CURRENCY_CONVERSION_RATE,
det.SUPPLIER_TAX_INVOICE_NUMBER,
det.SUPPLIER_TAX_INVOICE_DATE,
det.REGISTRATION_STATUS_CODE,
ext.attribute20,
ext.attribute21,
ext.attribute22,
ext.attribute23,
det.cancel_flag,
det.tax_type_code
order by trx_number,tax_rate |