JA India - Excise Duty Claim For RMA Receipts - draft

select distinct
  rma_lines.rma_number                          rma_num,
  rt.oe_order_line_id                           rma_line_id ,
  NVL(rma_lines.rma_line_number, 1)             rma_line_num,
  rt.creation_date                              rma_rcp_date,
  NVL(rt.attribute5, 'GOODS RETURN')               rma_type    ,
  rt.subinventory                               subinventory_code,
  hp.party_number                               customer_number,
  hp.party_name                                 customer_name,
  NVL(jrt.excise_invoice_no   , jrh.excise_invoice_no   ) excise_invoice_no ,
  NVL(jrt.excise_invoice_date , jrh.excise_invoice_date ) excise_invoice_date ,
  jai_om_rma_pkg.cal_excise_duty(rt.oe_order_line_id, rt.quantity) excise_duty_due ,
  jrt.organization_id ,
  jrt.location_id,
	JA_JAINEXCL_XMLP_PKG.cf_org_nameformula(jrt.organization_id) CF_org_name,
	JA_JAINEXCL_XMLP_PKG.cf_locformula(jrt.location_id) CF_loc,
	JA_JAINEXCL_XMLP_PKG.cf_sob_nameformula(jrt.organization_id) CF_sob_name,
	JA_JAINEXCL_XMLP_PKG.cf_1formula(rt.oe_order_line_id, NVL ( jrt.excise_invoice_no , jrh.excise_invoice_no ), NVL ( jrt.excise_invoice_date , jrh.excise_invoice_date )) CF_cess_amount
from
  jai_om_oe_rma_lines rma_lines,
  rcv_transactions    rt           ,
  hz_parties  hp                   ,
  hz_cust_accounts hca             ,
  jai_rcv_headers      jrh         ,
  jai_rcv_transactions jrt         ,
  oe_order_lines_all sl            ,
  oe_order_headers_all soh
where
  soh.order_number  = rma_lines.rma_number
  AND sl.header_id       = soh.header_id
  AND soh.sold_to_org_id   = hca.cust_account_id
  AND hca. party_id       =  hp.party_id
  and rma_lines.rma_header_id = rt.oe_order_header_id
  and rma_lines.rma_line_id = rt.oe_order_line_id
  AND rt.transaction_id =  jrt.transaction_id
  AND jrt.shipment_header_id = jrh.shipment_header_id
  AND rma_lines.rma_number = NVL(:p_rma_no, rma_lines.rma_number)
  AND TRUNC(rt.creation_date) BETWEEN NVL(TRUNC(:p_start_date), TRUNC(rt.creation_date))
  AND NVL(TRUNC(:p_end_date), TRUNC(rt.creation_date))
  AND hca.cust_account_id = NVL(:p_customer_id, hca.cust_account_id)
  AND hp.party_number = NVL(:p_customer_no, hp.party_number)
  AND jai_om_rma_pkg.cal_excise_duty(rt.oe_order_line_id, rt.quantity) is not null
   ORDER BY 12 ASC,13 ASC