SELECT det.trx_id customer_trx_id,
to_char(det.trx_date,'RRRRMMDD') doc_date ,
decode(det.event_class_code,'INVOICE','01','CREDIT_MEMO','03','DEBIT_MEMO','04') dgi_codes,
decode(substr(ext.gdf_ra_batch_sources_att7,1,1), 'Y', 'C',' ') fiscal_printer,
lpad(substr(det.trx_number,3,4),4,'0') ||
lpad(substr(det.trx_number,8,20),20,'0') ||
lpad(substr(det.trx_number,8,20),20,'0') ||
rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att10,det.gdf_ra_cust_ship_att10),1,2),'0'),2,'0') ||
decode(NVL(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),NULL,'0000000000',
rpad(substr(nvl(det.billing_tp_taxpayer_id,det.shipping_tp_taxpayer_id),1,10),10,'0')) ||
rpad(nvl(substr(nvl(det.gdf_ra_cust_bill_att12,det.gdf_ra_cust_ship_att12),1,1),'0'), 1,'0') ||
rpad(substr(nvl(det.billing_tp_name,det.shipping_tp_name),1,30),30,' ') string1,
sum(nvl(ext.numeric12,0)) doc_amt,
sum(NVL(ext.numeric8,0)) non_txbl_amt,
SUM(NVL(ext.numeric10,0)) txbl_amt,
det.tax_rate vat_rate,
SUM(NVL(ext.numeric9,0)) vat_amount,
SUM(NVL(ext.numeric1,0)) non_reg_amt,
SUM(NVL(ext.numeric2,0)) exmpt_amt,
SUM(NVL(ext.numeric3,0)) fed_percep_amt,
SUM(NVL(ext.numeric4,0)) prov_precep_amt,
SUM(NVL(ext.numeric5,0)) munic_percep_amt,
SUM(NVL(ext.numeric6,0)) excise_amt,
nvl(lpad(substr(ext.Attribute8,1,2),2,'0'),'00') cust_vat_reg_code,
nvl(rpad(nvl(substr(det.gdf_fnd_currencies_att1,1,3),' '),3,' '),' ') cur_code,
nvl(det.currency_conversion_rate,1) exchange_rate,
nvl(ext.numeric11,1) vat_rate_qty,
nvl(ext.attribute4,' ') dgi_trx_code,
lpad(nvl(substr(ext.attribute19,1,14),'0'),14,'0')||
DECODE(ext.attribute23, NULL,'00000000',
TO_CHAR(TO_DATE(ext.attribute23,
'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD')) CAI_info,
decode(ext.gdf_ra_cust_trx_att19,NULL,' ',
to_char(ext.gdf_ra_cust_trx_att19,'YYYYMMDD')) doc_void_date,
decode(det.application_doc_status, 'VD','VD',NULL) void_trx,
JL_JLARTSFF_XMLP_PKG.cf_doc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )) CF_doc_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate) CF_vat_rate,
JL_JLARTSFF_XMLP_PKG.cf_non_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )) CF_non_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )) CF_taxable_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )) CF_vat_tax_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )) CF_non_reg_tax_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )) CF_exmpt_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )) CF_fed_per_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )) CF_pro_per_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )) CF_mun_per_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_exc_amt_numformula(decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_num,
JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) )) CF_exc_amt_chr,
JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )) CF_exchange_rate,
JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id) CF_rec_count,
JL_JLARTSFF_XMLP_PKG.cf_vat_non_recformula(sum ( NVL ( ext.numeric8 , 0 ) ), decode ( det.application_doc_status , 'VD' , 'VD' , NULL ), to_char ( det.trx_date , 'RRRRMMDD' ), decode ( det.event_class_code , 'INVOICE' , '01' , 'CREDIT_MEMO' , '03' , 'DEBIT_MEMO' , '04' ), decode ( substr ( ext.gdf_ra_batch_sources_att7 , 1 , 1 ) , 'Y' , 'C' , ' ' ), lpad ( substr ( det.trx_number , 3 , 4 ) , 4 , '0' ) || lpad ( substr ( det.trx_number , 8 , 20 ) , 20 , '0' ) || lpad ( substr ( det.trx_number , 8 , 20 ) , 20 , '0' ) || rpad ( nvl ( substr ( nvl ( det.gdf_ra_cust_bill_att10 , det.gdf_ra_cust_ship_att10 ) , 1 , 2 ) , '0' ) , 2 , '0' ) || decode ( NVL ( det.billing_tp_taxpayer_id , det.shipping_tp_taxpayer_id ) , NULL , '0000000000' , rpad ( substr ( nvl ( det.billing_tp_taxpayer_id , det.shipping_tp_taxpayer_id ) , 1 , 10 ) , 10 , '0' ) ) || rpad ( nvl ( substr ( nvl ( det.gdf_ra_cust_bill_att12 , det.gdf_ra_cust_ship_att12 ) , 1 , 1 ) , '0' ) , 1 , '0' ) || rpad ( substr ( nvl ( det.billing_tp_name , det.shipping_tp_name ) , 1 , 30 ) , 30 , ' ' ), lpad ( nvl ( substr ( ext.attribute19 , 1 , 14 ) , '0' ) , 14 , '0' ) || DECODE ( ext.attribute23 , NULL , '00000000' , TO_CHAR ( TO_DATE ( ext.attribute23 , 'YYYY/MM/DD HH24:MI:SS' ) , 'YYYYMMDD' ) ), decode ( ext.gdf_ra_cust_trx_att19 , NULL , ' ' , to_char ( ext.gdf_ra_cust_trx_att19 , 'YYYYMMDD' ) ), JL_JLARTSFF_XMLP_PKG.cf_rec_countformula(det.trx_id), JL_JLARTSFF_XMLP_PKG.cf_doc_amt_chrformula(sum ( nvl ( ext.numeric12 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_amt_chrformula(sum ( NVL ( ext.numeric8 , 0 ) )), nvl ( lpad ( substr ( ext.Attribute8 , 1 , 2 ) , 2 , '0' ) , '00' ), nvl ( rpad ( nvl ( substr ( det.gdf_fnd_currencies_att1 , 1 , 3 ) , ' ' ) , 3 , ' ' ) , ' ' ), JL_JLARTSFF_XMLP_PKG.cf_exchange_rateformula(nvl ( det.currency_conversion_rate , 1 )), nvl ( ext.numeric11 , 1 ), nvl ( ext.attribute4 , ' ' ), det.tax_rate, JL_JLARTSFF_XMLP_PKG.cf_exmpt_amt_chrformula(SUM ( NVL ( ext.numeric2 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_taxable_amt_chrformula(SUM ( NVL ( ext.numeric10 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_vat_rateformula(det.tax_rate), JL_JLARTSFF_XMLP_PKG.cf_vat_tax_amt_chrformula(SUM ( NVL ( ext.numeric9 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_non_reg_tax_amt_chrformula(SUM ( NVL ( ext.numeric1 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_fed_per_amt_chrformula(SUM ( NVL ( ext.numeric3 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_pro_per_amt_chrformula(SUM ( NVL ( ext.numeric4 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_mun_per_amt_chrformula(SUM ( NVL ( ext.numeric5 , 0 ) )), JL_JLARTSFF_XMLP_PKG.cf_exc_amt_chrformula(SUM ( NVL ( ext.numeric6 , 0 ) ))) CF_vat_non_rec,
JL_JLARTSFF_XMLP_PKG.cf_blank_chrformula() CF_blank_chr
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_date ,
det.event_class_code,
ext.gdf_ra_batch_sources_att7,
det.trx_number,
det.gdf_ra_cust_bill_att10,
det.gdf_ra_cust_ship_att10,
det.billing_tp_taxpayer_id,
det.shipping_tp_taxpayer_id,
det.billing_tp_taxpayer_id,
det.shipping_tp_taxpayer_id,
det.gdf_ra_cust_bill_att12,
det.gdf_ra_cust_ship_att12,
det.billing_tp_name,
det.shipping_tp_name,
det.tax_rate,
ext.Attribute8,
det.gdf_fnd_currencies_att1,
det.currency_conversion_rate,
ext.numeric11,
ext.attribute4,
ext.attribute19,
ext.attribute23,
gdf_ra_cust_trx_att19,
det.application_doc_status,
det.trx_type_id
ORDER BY det.trx_date, det.trx_type_id, det.trx_number |