PA Refresh Transaction Summary Amounts

Description
Categories: BI Publisher, Financials
Application: Projects
Source: PRC: Refresh Transaction Summary Amounts (XML)
Short Name: PAXACRTA_XML
DB package: PA_PAXACRTA_XMLP_PKG
      SELECT
      PP.PROJECT_ID,
      PP.SEGMENT1,
      PT.PROJECT_TYPE_CLASS_CODE,
      NVL(SUM(TOT_REVENUE),0) + NVL(SUM(I_TOT_REVENUE),0) TXN_REVENUE,
      NVL(SUM(TOT_RAW_COST),0)+ NVL(SUM(I_TOT_RAW_COST),0) TXN_RAW_COST,
      NVL(SUM(TOT_BURDENED_COST),0)+ NVL(SUM(I_TOT_BURDENED_COST),0) TXN_BURDENED_COST,
      NVL(SUM(TOT_QUANTITY),0)+ NVL(SUM(I_TOT_QUANTITY),0) TXN_QUANTITY,
      NVL(SUM(TOT_LABOR_HOURS),0) + NVL(SUM(I_TOT_LABOR_HOURS),0) TXN_LABOR_HOURS,
      NVL(SUM(TOT_BILLABLE_RAW_COST),0)
        + NVL(SUM(I_TOT_BILLABLE_RAW_COST),0) TXN_BILLABLE_RAW_COST,
      NVL(SUM(TOT_BILLABLE_BURDENED_COST),0)
        + NVL(SUM(I_TOT_BILLABLE_BURDENED_COST),0) TXN_BILLABLE_BURDENED_COST,
      NVL(SUM(TOT_BILLABLE_QUANTITY),0)
        + NVL(SUM(I_TOT_BILLABLE_QUANTITY),0) TXN_BILLABLE_QUANTITY,
      NVL(SUM(TOT_BILLABLE_LABOR_HOURS),0)
        + NVL(SUM(I_TOT_BILLABLE_LABOR_HOURS),0) TXN_BILLABLE_LABOR_HOURS,
     PP.PROJECT_CURRENCY_CODE,
     PA_PAXACRTA_XMLP_PKG.cf_corbformula(PT.PROJECT_TYPE_CLASS_CODE) CF_CorB, 
 	 PA_PAXACRTA_XMLP_PKG.cf_notcorbformula(PT.PROJECT_TYPE_CLASS_CODE) CF_NotCorB
     FROM
      PA_TXN_ACCUM PTA,
      PA_PROJECT_ACCUM_HEADERS PAH, PA_PROJECTS PP,
      PA_PROJECT_TYPES PT
     WHERE
      PTA.PROJECT_ID = PAH.PROJECT_ID
     AND PT.PROJECT_TYPE = PP.PROJECT_TYPE
     AND PP.PROJECT_ID = PAH.PROJECT_ID
     AND PAH.REQUEST_ID = :P_CONC_REQUEST_ID
     AND PAH.RESOURCE_LIST_MEMBER_ID = 0
     AND PAH.TASK_ID = 0
     AND exists
          ( SELECT 'Yes'
            FROM   pa_txn_accum_details ptad
            WHERE  pta.txn_accum_id = ptad.txn_accum_id
            AND    ptad.line_type IN ('C','R','E')
          )
     GROUP BY PP.PROJECT_ID,PP.SEGMENT1, PT.PROJECT_TYPE_CLASS_CODE,
	        PP.PROJECT_CURRENCY_CODE
UNION
      SELECT
      PP.PROJECT_ID,
      PP.SEGMENT1,
      PT.PROJECT_TYPE_CLASS_CODE,
      0 TXN_REVENUE,
      0 TXN_RAW_COST,
      0 TXN_BURDENED_COST,
      0 TXN_QUANTITY,
      0 TXN_LABOR_HOURS,
      0 TXN_BILLABLE_RAW_COST,
      0 TXN_BILLABLE_BURDENED_COST,
      0 TXN_BILLABLE_QUANTITY,
      0 TXN_BILLABLE_LABOR_HOURS,
      PP.PROJECT_CURRENCY_CODE,
      PA_PAXACRTA_XMLP_PKG.cf_corbformula(PT.PROJECT_TYPE_CLASS_CODE) CF_CorB, 
  	  PA_PAXACRTA_XMLP_PKG.cf_notcorbformula(PT.PROJECT_TYPE_CLASS_CODE) CF_NotCorB
    FROM
      PA_PROJECTS PP, PA_PROJECT_TYPES PT
    WHERE PT.PROJECT_TYPE = PP.PROJECT_TYPE 
AND NOT EXISTS (SELECT PTA.PROJECT_ID FROM PA_TXN_ACCUM PTA WHERE PTA.PROJECT_ID = PP.PROJECT_ID)
     AND     NOT EXISTS (SELECT PAH.PROJECT_ID FROM PA_PROJECT_ACCUM_HEADERS PAH
                                                         WHERE PP.PROJECT_ID  = PAH.PROJECT_ID 
			   AND      PAH.REQUEST_ID  = :P_CONC_REQUEST_ID
     			   AND      PAH.RESOURCE_LIST_MEMBER_ID = 0
     			   AND      PAH.TASK_ID = 0)
    AND PP.SEGMENT1 BETWEEN NVL(:P_PROJECT_NUM_FROM, PP.SEGMENT1) 
    AND NVL(:P_PROJECT_NUM_TO, PP.SEGMENT1)
    GROUP BY PP.PROJECT_ID,PP.SEGMENT1, PT.PROJECT_TYPE_CLASS_CODE,
                         PP.PROJECT_CURRENCY_CODE
Parameter Name SQL text Validation
Expenditure Type Class
 
LOV Oracle
To PA Period
 
LOV Oracle
From PA Period
 
LOV Oracle
To Project Number
 
From Project Number