CST Miscellaneous Accrual Reconciliation

with cmr as
(
  select
   gsob.name ledger,
   haou.name operating_unit,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bal_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') balancing_segment,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('c_acc_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') account_segment,
   fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cct_segment', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_center_segment,
   gcc.concatenated_segments account,
   decode(cmr.invoice_distribution_id, null, 'INV', 'AP') transaction_source,
   decode(cmr.invoice_distribution_id,
     null,(select mtt.transaction_type_name
           from   mtl_transaction_types mtt
           where  cmr.transaction_type_code = to_char(mtt.transaction_type_id)
          ),
          (select crc.displayed_field
           from   cst_reconciliation_codes crc
           where  crc.lookup_code = cmr.transaction_type_code and
           crc.lookup_type in ( 'ACCRUAL WRITE-OFF ACTION','ACCRUAL TYPE')
          )
         ) transaction_type,
   trunc(cmr.transaction_date) transaction_date,
   cmr.quantity quantity,
   decode(cmr.invoice_distribution_id, null, mmt.transaction_uom, pol.unit_meas_lookup_code) uom,
   cmr.amount accounted_amount,
   cmr.entered_amount entered_amount,
   cmr.currency_code entered_currency,
   pov.vendor_name vendor,
   apia.invoice_num invoice_number,
   aida.invoice_line_number invoice_line,
   nvl(poh.clm_document_number,poh.segment1) po_number,
   por.release_num po_release,
   nvl(POL.line_num_display, to_char(POL.line_num)) po_line,
   poll.shipment_num po_shipment,
   pod.distribution_num po_distribution,
   cmr.po_distribution_id po_distribution_id,
   ( select rsh.receipt_num
     from  rcv_shipment_headers rsh,
           rcv_transactions rt
     where rsh.shipment_header_id = rt.shipment_header_id and
           rt.transaction_id = mmt.rcv_transaction_id and
           mmt.source_code = 'RCV' and
           rownum=1
   ) receipt_number,
   cmr.inventory_transaction_id inventory_transaction_id,
   nvl2(cmr.inventory_item_id,
        (select msi.concatenated_segments
         from mtl_system_items_vl msi
         where inventory_item_id = cmr.inventory_item_id and
         rownum <2
        ),
        null) item,
   mp.organization_code inventory_organization
  from
   cst_misc_reconciliation cmr,
   ap_invoices_all apia,
   ap_invoice_distributions_all aida,
   po_vendors pov,
   mtl_parameters mp,
   gl_code_combinations_kfv gcc,
   po_distributions_all pod,
   po_line_locations_all poll,
   po_releases_all por,
   po_lines_all pol,
   po_headers_all poh,
   mtl_material_transactions mmt,
   cst_accrual_accounts caa,
   hr_all_organization_units haou,
   hr_organization_information hoi,
   gl_sets_of_books gsob
  where
   cmr.invoice_distribution_id = aida.invoice_distribution_id(+) and
   aida.invoice_id = apia.invoice_id(+) and
   cmr.vendor_id = pov.vendor_id(+) and
   cmr.inventory_organization_id = mp.organization_id(+) and
   cmr.accrual_account_id = gcc.code_combination_id and
   cmr.accrual_account_id = caa.accrual_account_id and
   caa.operating_unit_id = cmr.operating_unit_id and
   pod.po_distribution_id(+) = cmr.po_distribution_id and
   cmr.inventory_transaction_id = mmt.transaction_id (+) and
   poll.line_location_id(+) = pod.line_location_id and
   pod.po_release_id = por.po_release_id(+) and
   pol.po_line_id(+) = pod.po_line_id and
   poh.po_header_id(+) = pod.po_header_id and
   cmr.operating_unit_id = haou.organization_id and
   hoi.organization_id = haou.organization_id and
   hoi.org_information_context = 'Operating Unit Information' and
   gsob.set_of_books_id = to_number(hoi.org_information3)
)
--
-- Main Query Starts Here
select
  cmr.*,
  case when cmr.transaction_source = 'AP'
    then 'AP: ' || cmr.invoice_number
    else case when cmr.po_number is not null
    then 'PO: ' || cmr.po_number
    else case when cmr.receipt_number is not null
    then 'RCV: ' || cmr.receipt_number
    else 'INV: ' || cmr.inventory_transaction_id
    end end end transaction_ref,
  decode( :p_sort_by ,
          'ITEM', item,
          'AMOUNT', decode(sign(cmr.accounted_amount),-1, chr(0) || translate( to_char(abs(cmr.accounted_amount), '000000000999.999'), '0123456789', '9876543210'), to_char(cmr.accounted_amount, '000000000999.999' ) ),
          'DATE', to_char(cmr.transaction_date, 'yyyymmddhh24miss')
        ) sort_key
from
  cmr
where
  1=1
order by
  sort_key