Retroactive billing report in oracle order management:-

/* Formatted on 11/18/2016 3:59:06 PM (QP5 v5.114.809.3010) */
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;