Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple.
Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips by upgrading to "Premium Membership".
Oracle Training

Thread Rating:
  • 15 Vote(s) - 2.93 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Project Expenditure type/category wise details with material
#1
/* Expenditure type/category wise project expenditure details */
 
SELECT
       ppa.segment1 project_code
       , ppa.name project_name
       , pt.task_number task_No
       , pt.task_name
       , pea.expenditure_group batch
       , pet.expenditure_category exp_Category
       , pet.expenditure_type exp_type
       , peia.transaction_source tran_source
       , msib.SEGMENT1 ||'.'|| msib.SEGMENT2 ||'.'|| msib.SEGMENT3 ||':' || msib.Description ITEM_DESC
       , msib.PRIMARY_UOM_CODE 
       , peia.quantity
       , peia.raw_cost
    FROM pa.pa_projects_all ppa
       , pa.pa_expenditure_items_all peia
       , pa.pa_expenditures_all pea
       , pa.pa_tasks pt
       , pa.pa_expenditure_types pet
       , MTL_SYSTEM_ITEMS_B msib
       , mtl_material_transactions mmt
   WHERE ppa.segment1= '3000004'
        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 ppa.project_id = pt.project_id
--     AND msib.INVENTORY_ITEM_ID = '11008'
AND mmt.source_project_id = '6001' --ppa.project_id
AND mmt.source_task_id = peia.task_id
AND mmt.inventory_item_id = msib.INVENTORY_ITEM_ID
ORDER BY peia.transaction_source
       , peia.creation_date DESC
       , pt.task_number DESC;
 
 
1. I need help of the experts about the above query. When executing the query it is giving more rows (redundent) than expected. Whats wrong with my query.
2. How could I determine whether the task is parent task or child task?
3. Any Suggestion or betterment of that query/information.
 
Thanks
-Shahidul.
Reply
Thanks given by:


Possibly Related Threads...
Thread Author Replies Views Last Post
  WIP Completion and Material Account Anumeha Manish 2 1,194 02-10-2017, 03:41 AM
Last Post: rofymarten
  R12 API to upload Project Budget Lines Ryan Adam 1 2,456 07-05-2014, 08:00 AM
Last Post: Sara Khan
  Oracle Discreet manufacturing module Training and implementation material waqar 1 1,099 07-02-2014, 03:34 PM
Last Post: mpolacza
  Turning on Project Budgetary Control OracleEBS 0 986 06-17-2014, 05:09 AM
Last Post: OracleEBS
  Raw Material and Finished Goods Items admin 2 5,246 05-06-2012, 06:14 PM
Last Post: inayat_md
  Project Contract admin 1 3,713 12-03-2010, 03:54 PM
Last Post: Sahil
  Mutiple currency via Pre-approved expenditure batches admin 0 1,611 10-23-2010, 04:57 PM
Last Post: admin



Users browsing this thread: 1 Guest(s)
Get Premium Membership Get Oracle Tutorials, Tips! Post a Question!