Since 2010, OraERP is a Oracle Forums, Community of Oracle Professionals including Fusion/Cloud Application Consultants, Enterprise Architects, ERP Cloud, HCM Cloud, CX Cloud and OCI Experts, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Digital Architect, PaaS Experts, IaaS, OCI Architects, 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 .
Thread Rating:
  • 49 Vote(s) - 3.39 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Project Expenditure type/category wise details with material
06-22-2015, 06:21 PM,
#1
Project Expenditure type/category wise details with material
/* 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


Possibly Related Threads…
Thread Author Replies Views Last Post
  Oracle EBS Project Accounting Abdelrahman.Fouad 0 2,636 06-21-2020, 11:07 PM
Last Post: Abdelrahman.Fouad
  Turning on Project Budgetary Control OracleEBS 1 3,709 07-02-2018, 09:46 PM
Last Post: webleads
  WIP Completion and Material Account Anumeha Manish 2 3,952 02-10-2017, 04:41 AM
Last Post: rofymarten
  R12 API to upload Project Budget Lines Ryan Adam 1 8,219 07-05-2014, 09:00 AM
Last Post: Sara Khan
  Oracle Discreet manufacturing module Training and implementation material waqar 1 3,920 07-02-2014, 04:34 PM
Last Post: mpolacza
  Raw Material and Finished Goods Items admin 2 10,129 05-06-2012, 07:14 PM
Last Post: inayat_md
  Project Contract admin 1 8,859 12-03-2010, 04:54 PM
Last Post: Sahil
  Mutiple currency via Pre-approved expenditure batches admin 0 3,315 10-23-2010, 05:57 PM
Last Post: admin



Users browsing this thread: 1 Guest(s)