Tuesday, 8 July 2014

Project Accounting Queries


/* Formatted on 7/8/2014 9:44:50 AM (QP5 v5.115.810.9015) */
SELECT ppa1.project_id,
       ppa1.segment1 "ACCOUNT_VALUE",
       ppa2.segment1 "INDIAN PROJECTS"
FROM pa_tasks pt,
     pa_projects_all ppa1,
     pa_projects_all ppa2,
     pa_project_customers ppc,
     pa_project_types_all ppt
WHERE     pt.task_id = ppc.receiver_task_id
      AND pt.project_id = ppa1.project_id
      AND ppt.project_type = ppa2.project_type
      AND ppc.project_id = ppa2.project_id
      AND ppt.project_type_class_code = 'CONTRACT'
      AND ppa2.project_status_code = 'APPROVED'
      AND ppa1.template_flag = 'N'
UNION
SELECT ppa.project_id, ppa.segment1, ppa.segment1
FROM pa_projects_all ppa, pa_project_types_all ppt
WHERE     ppt.project_type = ppa.project_type
      AND ppt.project_type_class_code <> 'CONTRACT'
      AND ppa.template_flag = 'N'
UNION
SELECT ppa1.project_id,
       ppa1.segment1 "ACCOUNT_VALUE",
       ppa1.segment1 "INDIAN PROJECTS"
FROM pa_projects_all ppa1, pa_project_customers ppc, pa_project_types_all ppt
WHERE     ppt.project_type = ppa1.project_type
      AND ppc.project_id = ppa1.project_id
      AND ppt.project_type_class_code = 'CONTRACT'
      AND ppc.bill_another_project_flag = 'N'
      AND ppa1.project_status_code = 'APPROVED'
      AND ppa1.template_flag = 'N';
/

/* Formatted on 7/8/2014 9:45:04 AM (QP5 v5.115.810.9015) */
SELECT ppa.project_id,
       ppa.name "PROJECT NAME",
       ppa.long_name "PROJECT ALIAS",
       ppa.description "PROJECT DESCRIPTION",
       ppa.start_date "START DATE",
       ppa.completion_date "END DATE",
       prc.customer_name "PRIMARY CUSTOMER",
       prc1.customer_name "SECONDARY CUSTOMER",
       hou.name "BUSINESS UNIT",
       lob.class_code "LINE OF BUSINESS",
       pra.class_code "PRACTICE",
       prloc.class_code "PROGRAM LOCATION",
       so.class_code "SERVICE OFFERING",
       bm.class_code "BUSINESS MODEL",
       sb.class_code "SETUP BILLING",
       0 "PARENT PROJECT ID",
       ppa.segment1 "PROJECT NUMBER",
       '0' "TASK NUMBER",
       ppa.project_type "PROJECT TYPE",
       pm."PROJECT MANAGER",
       pgm."PROGRAM MANAGER",
       vpp."VICE PRESIDENT",
       prc.project_relationship_code "PARENT RELATIONSHIP"
FROM pa_projects_all ppa,
     hr_all_organization_units hou,
     pa_project_customers_v prc,
     disc_pa_sec_customers prc1,
     pa_project_classes_v lob,
     pa_project_classes_v pra,
     pa_project_classes_v prloc,
     pa_project_classes_v so,
     pa_project_classes_v sb,
     disc_pa_biz_model bm,
     --PA_PROJECT_CLASSES_V BIL,
     disc_pa_pm pm,
     disc_pa_pgm pgm,
     disc_pa_vpp vpp
WHERE     ppa.carrying_out_organization_id = hou.organization_id
      AND ppa.template_flag != 'Y'
      AND ppa.project_id = prc.project_id(+)
      AND ppa.project_id = prc1.project_id(+)
      AND ppa.project_id = lob.project_id(+)
      AND lob.class_category = 'LINE OF BUSINESS'
      AND ppa.project_id = pra.project_id(+)
      AND pra.class_category = 'PRACTICE'
      AND ppa.project_id = prloc.project_id(+)
      AND prloc.class_category = 'PROGRAM LOCATION'
      AND ppa.project_id = so.project_id(+)
      AND so.class_category = 'SERVICE OFFERING'
      AND ppa.project_id = sb.project_id(+)
      AND sb.class_category = 'SETUP BILLING'
      AND ppa.project_id = bm.project_id(+)
      --AND BM.CLASS_CATEGORY = 'BUSINESS MODEL'
      --AND PPA.PROJECT_ID = BIL.PROJECT_ID (+) AND BIL.CLASS_CATEGORY (+) =
      --'FINAL PROJECTS FOR GO LIVE' AND BIL.CLASS_CODE = 'YES'
      AND ppa.project_id = pm.project_id(+)
      -- AND UPPER (prm.ROLE) = 'PROJECT MANAGER'
      --AND UPPER(PPA.PROJECT_TYPE) LIKE 'PROGRAM%'
      --AND PRC.PROJECT_RELATIONSHIP_CODE IN ('PARENT','INTERNAL');
      AND ppa.project_id = pgm.project_id(+)
      -- AND UPPER (pm.ROLE) = 'PROGRAM MANAGER'
      AND ppa.project_id = vpp.project_id(+)
-- AND UPPER (vpp.ROLE) = 'VICE PRESIDENT-PROGRAM'
-- and ppa.segment1 = '10242'
/

PA Task Master


/* Formatted on 7/8/2014 9:45:15 AM (QP5 v5.115.810.9015) */
SELECT ppa.project_id,
       ppa.segment1 project_number,
       ppa.name project_name,
       pt.task_id,
       pt.task_number,
       pt.task_name,
       pt.service_type_code task_service,
       ptop.task_id top_task_id,
       ptop.task_number top_task_number,
       ptop.task_name top_task_name,
       --PTOP.START_DATE,
       ptop.service_type_code top_task_service,
       sb.class_code setup_billing,
       pexc.start_date execution_start_date
--PT.ATTRIBUTE1
FROM pa_projects_all ppa,
     pa_tasks pt,
     pa_tasks ptop,
     pa_tasks pexc,
     pa_project_classes_v sb
WHERE     ppa.project_id = pt.project_id
      --AND PT.BILLABLE_FLAG='Y'
      AND ppa.template_flag = 'N'
      AND pt.top_task_id <> pt.task_id
      --AND UPPER(PPA.PROJECT_TYPE)='PROGRAM-US'
      AND ptop.task_id = pt.top_task_id
      AND ptop.top_task_id = ptop.task_id
      AND ppa.project_id = sb.project_id(+)
      AND sb.class_category = 'SETUP BILLING'
      AND pexc.project_id = ppa.project_id
      AND pexc.service_type_code = 'EXECUTION PHASE'
      AND pexc.top_task_id = pexc.task_id
--AND PEXC.TASK_NUMBER = '2.0'
--and PPA.SEGMENT1 = '10189';

3 comments:

  1. PMP Certification is highly respected within both IT & non-IT communities where strong project management skills are required. If you plan on a long term career as a project manager, then yes, even with your level of experience, I would suggest getting your PMP. You can prepare yourself for the exam in one of the PMP trainingproviders like http://www.pmstudy.com/. You can do minimal prep-work to get 40 PMI® Contact Hours and apply to PMI for PMP Exam before the class begins.

    ReplyDelete
  2. I recently came accross your blog and have been reading along. I thought I would leave my first
    comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep
    visiting this blog very often. I am an PMP Training Centers in Chennai.
    I feel great after reading this information. Please make update I will be regular rss to this site.
    PMI REP Chennai
    pmp training institute chennai
    PMP REP Chennai
    Project Management Training Chennai
    PMP Exam Preparation Training Chennai
    PMP Training Chennai

    ReplyDelete
  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete