Projects

-
1. List of all active projects

-------------------------------------------
SELECT    proj.Project_id,
          proj.name project_name,
          proj.segment1 projec_number,
          status.project_status_name project_status,
          proj.project_type,
          proj.start_date project_start_date,
          proj.completion_date project_finish_date
FROM      apps.pa_projects_all proj,
          apps.pa_project_statuses status
WHERE     proj.project_status_code=status.project_status_code
AND       status.status_type='PROJECT'

AND       status.project_status_code not in ('CLOSED','PENDING_CLOSE')
AND       proj.template_flag='N';

 2 : Active projects , tasks and their expenditures data.
------------------------------------------------------------------------
SELECT    proj.Project_id,
          proj.name project_name,
          proj.segment1 projec_number,
          status.project_status_name project_status,
          proj.project_type,
          proj.start_date project_start_date,
          proj.completion_date project_finish_date,
          pt.task_number,
          pt.task_name,
          pt.start_date task_start_date,
          pt.completion_date task_completion_date,
          (SELECT name 
                  FROM  apps.hr_all_organization_units hou 
                  WHERE exp.incurred_by_organization_id=hou.organization_id
                  )expenditure_organization,
          ei.expenditure_type,
          ei.expenditure_item_date,
          ei.raw_cost,
          ei.burden_cost
FROM      apps.pa_projects_all proj,
          apps.pa_project_statuses status,
          apps.pa_tasks pt,
          apps.pa_expenditure_items_all ei,
          apps.pa_expenditures_all exp
WHERE     proj.project_status_code=status.project_status_code
AND       proj.project_id=pt.project_id
AND       ei.project_id=proj.project_id
AND       ei.task_id= pt.task_id
AND       ei.expenditure_id=exp.expenditure_id
AND       status.status_type='PROJECT'
AND       status.project_status_code not in ('CLOSED','PENDING_CLOSE')
AND       proj.template_flag='N'

AND       exp.expenditure_status_code='APPROVED'
AND       Proj.project_id= &project_id;  
--AND       Proj.project_id=30221;  

Comments

Post a Comment

Popular posts from this blog

AP Invoice Rejection - Errors

-
Account Required.  Accounting information is missing for this line. Acct Date Beyond Enc Year.  Accounting date (ACCOUNTING_DATE) is beyond the encumbrance year and the Use PO Encumbrance Financials option is enabled. Acct Date Not In Open Pd.  Accounting date (ACCOUNTING_DATE) is not in an open period. Amount Billed Would Be Below Zero.  This transaction would reduce the amount billed on the purchase order to below zero. Amount Unpaid Too Small.  The prepayment amount that you are trying to apply (PREPAY_APPLY_AMOUNT) exceeds the invoice amount. Note:  For invoices that include prepayments (that is, the invoice amount has already been reduced by the amount of the prepayment) the prepayment amount cannot exceed the unpaid invoice amount. Applying More Than Avail.  You are applying a prepayment to the invoice and the amount you are applying is greater than the prepayment amount available for application. You can see a prepayment’s available a...
Read more

AP Invoice Rejections

-
Select aii.Invoice_num , air.PARENT_TABLE, air.REJECT_LOOKUP_CODE, air.parent_id From apps. AP_INTERFACE_REJECTIONS air, apps. AP_INVOICE_LINES_INTERFACE aili, apps. AP_INVOICES_INTERFACE aii WHere aii.invoice_id=aili.invoice_id AND  aili.invoice_id=air.parent_id AND aii.Invoice_num = ‘Invoice Number’ aii.INVOICE_ID, aii.SOURCE, aii.invoice_num, (select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all where org_id = aii.ORG_ID and invoice_num = aii.INVOICE_NUM) Inv_in_Base, aii.CREATION_DATE, aii.invoice_date,  aii.GL_DATE, ail.LINE_NUMBER, aii.REQUEST_ID, aii.invoice_currency_code, nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason, (select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus from ap_invoice_lines_interface ail, AP_INTERFACE_REJECTIONS airh, AP_INTERFACE_REJECTIONS airl, ap_invoices_interface aii,...
Read more

GL Interface

-
STATUS NOT NULL VARCHAR2(50)   LEDGER_ID   NUMBER(15)   ACCOUNTING_DATE NOT NULL DATE   CURRENCY_CODE NOT NULL VARCHAR2(15)   DATE_CREATED NOT NULL DATE   CREATED_BY NOT NULL NUMBER(15)   ACTUAL_FLAG NOT NULL VARCHAR2(1)   USER_JE_CATEGORY_NAME NOT NULL VARCHAR2(25)   USER_JE_SOURCE_NAME NOT NULL VARCHAR2(25)   CURRENCY_CONVERSION_DATE   DATE   ENCUMBRANCE_TYPE_ID   NUMBER   BUDGET_VERSION_ID   NUMBER   USER_CURRENCY_CONVERSION_TYPE   VARCHAR2(30)   CURRENCY_CONVERSION_RATE   NUMBER   AVERAGE_JOURNAL_FLAG   VARCHAR2(1)   ORIGINATING_BAL_SEG_VALUE   VARCHAR2(25)   SEGMENT1   VARCHAR2(25) ...
Read more