PSB Pooled Position Report for Worksheet by Responsibility
Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Pooled Position Report for Worksheet by Responsibility (XML)
Short Name: PSBRRPPS_XML
DB package: PSB_PSBRPPPS_XMLP_PKG
Source: Pooled Position Report for Worksheet by Responsibility (XML)
Short Name: PSBRRPPS_XML
DB package: PSB_PSBRPPPS_XMLP_PKG
Run
PSB Pooled Position Report for Worksheet by Responsibility and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT WAL.CURRENCY_CODE "Currency Code" ,POS.NAME "Position" ,DECODE(WAL.POSITION_LINE_ID, NULL, NULL, DECODE(WEL.PAY_ELEMENT_NAME, NULL, DECODE( substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1), 'AMERICAN','Follow Salary Element', NULL), WEL.PAY_ELEMENT_NAME ) ) "Element Name" ,SP.SHORT_NAME "Service Package" ,GCC.CONCATENATED_SEGMENTS "GLAccount" ,SUM(WAL.YTD_AMOUNT)"Amount" ,BYTV.SEQUENCE_NUMBER ,BP.NAME "Year" FROM PSB_WS_ACCOUNT_LINES WAL , PSB_BUDGET_PERIODS BP, PSB_SERVICE_PACKAGES SP, GL_CODE_COMBINATIONS_KFV GCC, PSB_WS_ELEMENT_LINE_NAMES_V WEL, PSB_POSITIONS POS, PSB_BUDGET_YEAR_TYPES_VL BYTV, PSB_WS_POSITION_LINES WPL, PSB_WS_LINES_POSITIONS WLP, PSB_WORKSHEETS WS WHERE BP.BUDGET_YEAR_TYPE_ID = BYTV.BUDGET_YEAR_TYPE_ID AND WAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND ((:P_BUDGET_YEAR_ID IS NOT NULL AND BP.BUDGET_PERIOD_ID = :P_BUDGET_YEAR_ID) OR (:P_BUDGET_YEAR_ID IS NULL)) AND WAL.BUDGET_YEAR_ID = BP.BUDGET_PERIOD_ID AND WAL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID AND POS.HR_POSITION_ID IN (SELECT HR_POSITION_ID FROM PSB_POSITIONS WHERE DATA_EXTRACT_ID = WS.DATA_EXTRACT_ID GROUP BY HR_POSITION_ID HAVING COUNT(HR_POSITION_ID) > 1) AND WAL.POSITION_LINE_ID = WEL.POSITION_LINE_ID(+) AND WAL.ELEMENT_SET_ID = WEL.ELEMENT_SET_ID(+) AND WAL.BUDGET_YEAR_ID = WEL.BUDGET_YEAR_ID(+) AND WAL.END_STAGE_SEQ IS NULL AND WAL.TEMPLATE_ID IS NULL AND WAL.POSITION_LINE_ID = WLP.Position_Line_ID AND POS.Position_ID = WPL.Position_ID AND WPL.Position_Line_ID = WLP.Position_Line_ID AND WLP.WORKSHEET_ID = :P_WORKSHEET_ID AND WS.WORKSHEET_ID = :P_WORKSHEET_ID GROUP BY BYTV.SEQUENCE_NUMBER , BP.NAME , WAL.CURRENCY_CODE , POS.NAME , DECODE(WAL.POSITION_LINE_ID, NULL, NULL, DECODE(WEL.PAY_ELEMENT_NAME, NULL, DECODE( substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1), 'AMERICAN','Follow Salary Element', NULL), WEL.PAY_ELEMENT_NAME ) ) ,SP.SHORT_NAME ,GCC.CONCATENATED_SEGMENTS ORDER BY BYTV.SEQUENCE_NUMBER, "Position","GLAccount","Service Package","Element Name" |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Worksheet |
|
LOV Oracle | |
Budget Year |
|
LOV Oracle |