PO to Project Link Query

 PO to Project Link Query

 

SELECT   DISTINCT   c.po_header_id,
                                                         b.po_line_id,
                                                         c.segment1,
                                                         b.line_location_id,
                                                         b.po_distribution_id,
                                                         b.distribution_num,
                                                         a.unit_price,
                                                         (a.quantity - b.quantity_billed),
                                                         NULL,
                                                         a.item_id,
                                                         g.SEGMENT1 "Project Number",
                                                         h.TASK_NAME,
                                                         h.TASK_ID,
                                                         b.EXPENDITURE_TYPE,
                                                         b.EXPENDITURE_ITEM_DATE,
                                                         b.PROJECT_ACCOUNTING_CONTEXT,
                                                         b.EXPENDITURE_ORGANIZATION_ID,
                                                         j.SHIPMENT_NUM
                                          FROM   po_lines_all a,
                                                 po_distributions_all b,
                                                 po_headers_all c,
                                                 pa_projects_all g,
                                                 pa_tasks h,
                                                 pa_project_statuses i,
                                                 po_line_locations_all j
                                         WHERE       1 = 1
                                                 AND  c.segment1 = 'PO_NUMBER HERE'
                                                 AND a.line_num = 'PO_LINE_NUMBER HERE'
                                                 AND c.po_header_id = a.po_header_id
                                                 AND a.po_header_id = b.po_header_id
                                                 AND a.po_line_id = b.po_line_id
                                                 AND b.PROJECT_ID = g.PROJECT_ID
                                                 AND b.TASK_ID = h.TASK_ID
                                                 AND g.project_status_code = i.project_status_code
                                                 AND j.po_line_id = a.po_line_id
                                                 AND j.line_location_id = b.line_location_id
                                                 AND (a.quantity - b.quantity_billed) > 0
                                                 AND NVL (a.cancel_flag, 'N') <> 'Y'
                                                 and i.project_system_status_code = ('APPROVED');

Key Tables in Oracle Projects in Oracle Apps R12

Key Tables in Oracle Projects

 

Table	Description
PA_PROJECTS_ALL	It stores the highest units of work defined in Oracle Projects.
PA_PROJECT_ASSETS_ALL	It contains assets information defined for capital projects.
PA_PROJECT_ASSIGNMENTS	It stores details of all Assignments for a project.
PA_PROJECT_CLASSES	It contains the class codes of class categories that are used to classify projects.
PA_PROJECT_ROLE_TYPES	Implementation-defined responsibilities or positions assigned to employees on projects are stored here.
PA_PROJECT_STATUSES	It stores valid project status codes.
PA_PROJECT_TYPES_ALL	It stores implementation-defined project classifications that supply default information and drive some project processing.
PA_TASKS	It contains user-defined subdivisions of project work.
PA_TASK_TYPES	It stores implementation-defined classifications of task.
PA_TRANSACTION_INTERFACE_ALL	It is an interface table to import transactions from external sources into Oracle Projects.
PA_TRANSACTION_SOURCES	It stores implementation-defined sources of imported transactions originating in an external system.
PA_IMPLEMENTATIONS_ALL	It contains information about the configuration of an Oracle Projects installation.
PA_ACTION_SETS	It stores action set templates as well as action sets belonging to an object, such as projects, requirements, etc.
PA_ACTION_SET_LINES	It stores action set lines that belong to an action set or an action set template.
PA_ACTION_SET_TYPES	It stores attributes of action set types.
PA_AGREEMENTS_ALL	It has customer contracts that serve as the basis for work authorization.
PA_AGREEMENT_TYPES	Implementation-defined classifications of customer agreements.
PA_BILL_RATES_ALL	Information about bill rates and markups of standard bill rate schedules.
PA_BUDGETS	It stores budgets information.
PA_BUDGET_LINES	It stores detail lines of project and task budgets.
PA_BUDGET_TYPES	It contains implementation-defined classifications of types of budgets used for different business purposes.
PA_CLASS_CATEGORIES	It stores implementation-defined categories for classifying projects.
PA_CLASS_CODES	It stores implementation-defined values within class categories that can be used to classify projects.
PA_EVENTS	It stores entries assigned to tasks that generate revenue and/or billing but are not directly related to expenditure items.
PA_EVENT_TYPES	It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL	Groups of expenditure items incurred by employees or organizations for an expenditure period.
PA_EXPENDITURE_CATEGORIES	Implementation-defined groupings of expenditure types by type of cost.
PA_EXPENDITURE_ITEMS_ALL	It contains the smallest units of expenditure charged to projects and tasks.
PA_EXPENDITURE_TYPES	Implementation-defined classifications of expenditures charged to projects and tasks.
PA_PERIODS_ALL	Implementation-defined periods against which project performance is measured.
PA_RBS_DENORM	This table stores normalized resource breakdown structure information.
PA_RBS_ELEMENTS	This table stores the RBS element information and the parent-child relationship.
PA_RESOURCES	It contains resources used in budgeting and project summary amounts.
PA_ROLE_LISTS	It stores lists of roles defined with the system.
PA_SCHEDULES	It displays the schedule details for requirements and assignments. It also displays calendar schedules.