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
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 |