CST Miscellaneous Accrual Reconciliation

Description
Categories: Enginatics
Repository: Github
Imported from Concurrent Program
Application: Bills of Material
Source: Miscellaneous Accrual Reconciliation Report
Short Name: CSTACRMI
DB package:
Run CST Miscellaneous Accrual Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit
cmr.operating_unit = :p_operating_unit
LOV
Balancing Segment From
cmr.balancing_segment >= :p_bal_segment_from
LOV
Balancing Segment To
cmr.balancing_segment <= :p_bal_segment_to
LOV
Accrual Account
cmr.account = :p_accrual_account
LOV
Date From
cmr.transaction_date >= :p_from_date
Date
Date To
cmr.transaction_date < :p_to_date + 1
Date
Minimum Amount
cmr.accounted_amount >= :p_from_amount
Number
Maximum Amount
cmr.accounted_amount <= :p_to_amount
Number
Item From
cmr.item >= :p_from_item
LOV
Item To
cmr.item <= :p_to_item
LOV
Invoice Number
cmr.invoice_number = :p_invoice_num
LOV
Invoice Line
cmr.invoice_line = :p_invoice_line
Number
PO Number
cmr.po_number = :p_po_num
LOV
PO Release
cmr.po_release = :p_release_num
LOV
PO Line
cmr.po_line = :p_po_line_num
Number
PO Shipment
cmr.po_shipment = :p_po_shipment_num
Number
Transaction Type
cmr.transaction_type = :p_trx_type
LOV
Inventory Transaction ID
cmr.inventory_transaction_id = :p_inv_trx_id
Number
Sort By
 
LOV Oracle