Query to get Permit Sales by Item

SELECT DISTINCT
  obh.attribute2 PERMIT_CLASS,
  hca.account_number,
  hca.account_name,
  msib.segment1 ITEM_CODE,
  msib.description ITEM_DESC,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) CDC,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id) CDC_DESC,
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) RMA,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id) RMA_DESC,
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) MILK_CLASS,
  obh.cust_po_number PERMIT,
  rct.trx_number TRX_NUMBER,
  rctype.name TRX_TYPE,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id))SHIPMENT_DATE,
  rct.trx_date,
  rctgl.gl_date gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price) amount,
  xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id)  lot_number
FROM ra_customer_trx rct,
  oe_order_headers ooh,
  oe_order_lines orl,
  hz_cust_accounts hca,
  mtl_system_items_b msib,
  ra_cust_trx_types rctype,
  oe_blanket_headers obh ,
  oe_blanket_lines obl ,
  oe_transaction_types_vl ottv,
  ra_cust_trx_line_gl_dist rctgl,
  ra_batch_sources rbatchs,
  RA_CUSTOMER_TRX_LINES_ALL rl
WHERE to_char(ooh.order_number)     = (rct.interface_header_attribute1)--ct_reference)
AND ooh.header_id            = orl.header_id
and rct.interface_header_context = 'ORDER ENTRY'
and rct.org_id=121
and rl.line_type = 'LINE'
and rl.interface_line_context = 'ORDER ENTRY'
and rctgl.customer_trx_line_id=rl.customer_trx_line_id
and rl.interface_line_attribute6 = to_char(orl.line_id)
and rl.interface_line_attribute1 = to_char(ooh.order_number)
and rl.sales_order = ooh.order_number
AND rct.sold_to_customer_id  = hca.cust_account_id
AND rl.inventory_item_id(+) = msib.inventory_item_id
AND msib.inventory_item_id   = orl.inventory_item_id
AND msib.organization_id     = orl.ship_from_org_id
AND rctype.cust_trx_type_id  = rct.cust_trx_type_id
AND rct.sold_to_customer_id  =obl.sold_to_org_id
AND obh.header_id            =obl.header_id
AND ottv.transaction_type_id = obh.order_type_id
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type  for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
AND rctgl.customer_trx_id   =rct.customer_trx_id
AND rct.batch_source_id     =rbatchs.batch_source_id
AND ottv.name               =ANY('CDC PERMIT (CCBU_CA)','PERMIS CCL (CCBU_CA)')
AND upper(rctype.name)      =ANY('INVOICE','DEBIT','CREDIT')
AND ooh.order_category_code = ANY('ORDER','RETURN')
AND rbatchs.name            ='ORDER MANAGEMENT'
  --Parameter  selection Criteria
AND ooh.order_category_code = DECODE(:p_order_type,'STANDARD','ORDER','RMA','RETURN','ALL',ooh.order_category_code) --:p_order_type  for standard or RMA
AND TRUNC(rctgl.gl_date) BETWEEN :p_from_date AND :p_to_date
AND NVL(hca.account_number,-1) = NVL(:p_customer,NVL(hca.account_number,-1))
AND NVL(obh.attribute2,    -1) = NVL(:p_permit_class,NVL(obh.attribute2,-1))
AND NVL(obh.cust_po_number,-1) = NVL(:p_permit_number,NVL(obh.cust_po_number,-1))
 group by
  obh.attribute2,
  hca.account_number,
  hca.account_name,
  msib.segment1 ,
  msib.description,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
  obh.cust_po_number ,
  rct.trx_number ,
  rctype.name ,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
  rct.trx_date,
  rctgl.gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price)
,   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id) 
ORDER BY obh.attribute2,
  hca.account_number,
  hca.account_name,
  msib.segment1 ,
  msib.description,
  -- CDC
  xxar_extract_permit_sales_pkg.get_cdc_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_cdc_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --RMA GROUP
  xxar_extract_permit_sales_pkg.get_rma_code(orl.inventory_item_id,orl.ship_from_org_id) ,
  xxar_extract_permit_sales_pkg.get_rma_code_desc(orl.inventory_item_id,orl.ship_from_org_id),
  --MILK CLASS based on RMA GROUP
  xxar_extract_permit_sales_pkg.get_milk_class(orl.inventory_item_id,orl.ship_from_org_id) ,
  obh.cust_po_number ,
  rct.trx_number ,
  rctype.name ,
  ooh.order_number,
  --- Actual shipment date for Order or Return as per FS for return  the shipment date will be the one in the original transaction referenced in the RMA transaction
  xxar_extract_permit_sales_pkg.get_shipment_date(DECODE(ooh.order_category_code,'ORDER',orl.line_id,'RETURN',orl.reference_line_id)),
  rct.trx_date,
  rctgl.gl_date,
  orl.ordered_quantity ,
  orl.unit_selling_price,
  (orl.ordered_quantity*orl.unit_selling_price),
   xxar_extract_permit_sales_pkg.get_lot_details(orl.inventory_item_id,orl.ship_from_org_id)