Retroactive billing report in oracle order management

SELECT DECODE (:p_sort_by, 'O', orig_head.order_number) sort_by_order_number,

       DECODE (

          :p_sort_by,

          'T', oe_retrobill_pvt.invoice_number (orig_head.order_number,

                                                orig_lin.line_id,

                                                orig_head.order_type_id),

          NULL)

          sort_by_invoice_no,

       DECODE (

          SUBSTR (UPPER (:p_item_display), 1, 1),

          'P', NVL (orig_lin.user_item_description,

                    item_info.item_description),

          'D', NVL (orig_lin.user_item_description, si_tl.description),

          'O', item_info.item,

          'F', item_info.inventory_item,

          'C', item_info.item || ' - '

               || NVL (orig_lin.user_item_description,

                       item_info.item_description),

          'I',    item_info.inventory_item

               || ' - '

               || NVL (orig_lin.user_item_description, si_tl.description),

          NVL (orig_lin.user_item_description, item_info.item_description))

          item_name,

       oe_order_misc_pub.get_concat_line_number (orig_lin.line_id) line_no,

       party.party_name customer_name,

       site.location invoice_to_name,

       orig_head.order_number,

       orig_head.ordered_date ordered_date,

       orig_head.transactional_curr_code currency,

       orig_lin.ordered_quantity Ordered,

       orig_lin.shipped_quantity Shipped,

       orig_lin.invoiced_quantity Invoiced,

       TO_CHAR (orig_lin.line_id) line_id,

       TO_CHAR (orig_lin.header_id) header_id,

       orig_lin.unit_selling_price orig_price,

       orig_head.order_type_id,

       orig_head.org_id,

       orig_lin.inventory_item_id

  FROM oe_order_lines_all orig_lin,

       oe_order_lines_all retro_lin,

       hz_cust_accounts cust_info,

       hz_parties party,

       oe_items_v item_info,

       mtl_system_items_tl si_tl,

       oe_order_headers_all orig_head,

       hz_cust_site_uses_all site,

       hz_cust_acct_sites_all acct_site,

       oe_order_headers_all retro_head

 WHERE     orig_head.header_id = orig_lin.header_id

       AND NVL (orig_head.org_id, 0) = NVL (:p_organization_id, 0)

       AND retro_lin.order_source_id = 27

       AND retro_lin.orig_sys_line_ref = orig_lin.line_id

       AND retro_head.header_id = retro_lin.header_id

       AND retro_head.order_source_id = 27

       AND retro_head.orig_sys_document_ref = TO_CHAR (:p_request_id)

       AND retro_lin.retrobill_request_id = :p_request_id

       AND orig_lin.invoiced_quantity > 0

       AND cust_info.cust_account_id = orig_head.sold_to_org_id

       AND cust_info.party_id = party.party_id

       AND orig_lin.inventory_item_id = item_info.inventory_item_id

       AND NVL (orig_lin.ordered_item, '-99') =

              DECODE (orig_lin.item_identifier_type,

                      'INT', NVL (orig_lin.ordered_item, '-99'),

                      'CUST', NVL (orig_lin.ordered_item, '-99'),

                      item_info.item)

       AND NVL (orig_lin.sold_to_org_id, -99) =

              NVL (item_info.sold_to_org_id,

                   NVL (orig_lin.sold_to_org_id, -99))

       AND NVL (orig_lin.item_identifier_type, 'INT') =

              item_info.item_identifier_type

       AND NVL (orig_lin.ordered_item_id, -99) = NVL (item_info.item_id, -99)

       AND si_tl.inventory_item_id = item_info.inventory_item_id

       AND si_tl.organization_id = item_info.organization_id

       AND si_tl.language = USERENV ('LANG')

       AND site.site_use_code = 'BILL_TO'

       AND site.site_use_id = orig_lin.invoice_to_org_id

       AND acct_site.cust_account_id = cust_info.cust_account_id

       AND site.cust_acct_site_id = acct_site.cust_acct_site_id;