JA India - Excise Duty Claim For RMA Receipts - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Excise Duty Claim For RMA Receipts (XML) - Not Supported: Reserved For Future Use
Short Name: JAINEXCL_XML
DB package: JA_JAINEXCL_XMLP_PKG
Run JA India - Excise Duty Claim For RMA Receipts - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Customer Name
 
LOV Oracle
Customer Number
 
LOV Oracle
RMA Number
 
LOV Oracle
Start Date
 
Date
End Date
 
Date
Blitz Report™