Permti Sales by Item ( ORDERS, RETURNS)

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,
  --rctp.gd_gl_date gl_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_CUSTOMER_TRX_PARTIAL_V rctp,
  ra_cust_trx_line_gl_dist rctgl,
  ra_batch_sources rbatchs--,
  --  RA_CUSTOMER_TRX_LINES_ALL rl
WHERE TO_CHAR(ooh.order_number)  = rct.ct_reference--interface_header_attribute1
AND ooh.header_id                = orl.header_id
--AND rct.interface_header_context = 'ORDER ENTRY'
  --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 rctgl.customer_trx_id   =rct.customer_trx_id
--AND rctp.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))
AND obh.order_number=ooh.blanket_number
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,
  --rctp.gd_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)
UNION
 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((orl.reference_line_id))SHIPMENT_DATE,
    rct.trx_date,
    --rctp.gd_gl_date gl_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_vl 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
  WHERE (ooh.order_number) = (rct.ct_reference)
  AND ooh.header_id        = orl.header_id
    ---       and ooh.blanket_number(+)=obh.order_number
  AND orl.reference_header_id IS NOT NULL
  AND rct.sold_to_customer_id  = hca.cust_account_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 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  = ('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))
order by  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21/*,
  hca.account_number,
  hca.account_name,
  msib.segment1 ,
  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,
  --rctp.gd_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)*/