PA Refresh Transaction Summary Amounts

Description
Categories: BI Publisher
Application: Projects
Source: PRC: Refresh Transaction Summary Amounts (XML)
Short Name: PAXACRTA_XML
DB package: PA_PAXACRTA_XMLP_PKG
Run PA Refresh Transaction Summary Amounts and other Oracle EBS reports with Blitz Report™ on our demo environment
      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
From Project Number
 
Char
To Project Number
 
Char
From PA Period
 
LOV Oracle
To PA Period
 
LOV Oracle
Expenditure Type Class
 
LOV Oracle
Blitz Report™