Expenditure Items - and Batches

Expenditure Items in project

For some reason, probably to do with indexes, it's a lot faster to search through the table holding expenditure items via expenditure_item_id than via creation dates, though other things are also pretty fast, like searching by project number.
There's another piece of SQL for a different page... the SQL on this page is more basic, with no link to AP, as not all expenditure items are linked to AP invoices.

-- Basic items checking

SELECT peia.expenditure_item_id trans_id

     , pea.expenditure_group batch

     , pet.expenditure_category expend_type

     , pet.expenditure_type

     , peia.transaction_source tx_src

     , peia.expenditure_item_date exp_date

     , peia.creation_date

     , peia.raw_cost

     , ppa.segment1 project

     , pt.task_number task

     , pec.expenditure_comment comment_

  FROM pa.pa_expenditure_items_all peia

     , pa.pa_expenditures_all pea

     , pa.pa_projects_all ppa

     , pa.pa_tasks pt

     , pa.pa_expenditure_types pet

     , pa.pa_expenditure_comments pec

 WHERE peia.expenditure_id = pea.expenditure_id(+)

   AND peia.project_id = ppa.project_id

   AND peia.task_id = pt.task_id

   AND peia.expenditure_type = pet.expenditure_type

   AND peia.expenditure_item_id = pec.expenditure_item_id

   AND ppa.project_id = pt.project_id

   AND peia.expenditure_item_id > :transid

--   AND ppa.segment1 = :projectnum

ORDER BY peia.expenditure_item_date desc, pt.task_number desc;

/*##############################################################################

#      TOTAL EXPENDITURES FOR A PROJECT

/*############################################################################*/

  SELECT ppa.segment1 project

       , pt.task_number task

       , pt.task_name

       , SUM (peia.raw_cost) total

       , ppa.description      

    FROM pa.pa_expenditure_items_all peia

       , pa.pa_projects_all ppa

       , pa.pa_tasks pt

   WHERE peia.project_id = ppa.project_id

     AND peia.task_id = pt.task_id

     AND ppa.segment1 = '123456'

GROUP BY ppa.segment1

       , pt.task_number

       , pt.task_name

       , ppa.description

ORDER BY ppa.segment1

       , pt.task_number;

/*##############################################################################

#      EXPENDITURE ITEMS LINKED TO COST ITEMS

/*############################################################################*/

SELECT peia.expenditure_item_id trans_id

     , ppa.segment1 project

     , pt.task_number task

     , pps.project_status_name status

     , pea.expenditure_group batch

     , pet.expenditure_category expend_type

     , pet.expenditure_type exp_type

     , peia.transaction_source tx_src

     , peia.expenditure_item_date exp_date

     , peia.creation_date

     , peia.raw_cost

     , peia.quantity

     , peia.request_id

     , peia.request_id exp_item_request_id

     , '### system-references ###'

     , pcdla.*

  FROM pa.pa_expenditure_items_all peia

     , pa.pa_cost_distribution_lines_all pcdla

     , pa.pa_expenditures_all pea

     , pa.pa_projects_all ppa

     , pa.pa_tasks pt

     , pa.pa_expenditure_types pet

     , pa.pa_expenditure_comments pec

     , pa.pa_project_statuses pps

     , applsys.fnd_user fu

 WHERE peia.expenditure_item_id = pcdla.expenditure_item_id

   AND peia.expenditure_id = pea.expenditure_id(+)

   AND peia.project_id = ppa.project_id(+)

   AND peia.task_id = pt.task_id(+)

   AND peia.expenditure_type = pet.expenditure_type(+)

   AND peia.expenditure_item_id = pec.expenditure_item_id(+)

   AND ppa.project_id = pt.project_id

   AND ppa.project_status_code = pps.project_status_code

   AND peia.created_by = fu.user_id(+)

   AND peia.creation_date > '15-MAY-2013'

   AND 1 = 1;

Expenditure Batches

 -- basic batches info

  SELECT *

    FROM pa.pa_expenditure_groups_all pega

   WHERE pega.creation_date >= '22-JAN-2005'

ORDER BY pega.creation_date DESC;

-- batch count per transaction source

  SELECT pega.transaction_source

       , MAX (pega.creation_date) max_date

       , COUNT (*) ct

    FROM pa.pa_expenditure_groups_all pega

   WHERE pega.transaction_source = 'PAYROLL'

GROUP BY transaction_source

ORDER BY 1;