| SQL Id | SQL Text |
|---|
| 0k8522rmdzg4k | select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 |
| 0r1zf55mxaujd |
/* OracleOEM */ declare lv_sql_stmt varchar2(10000); finalQry varchar2(2048); fromClause varchar2(512); lc_var1 varchar2(512); lc_var2 varchar2(30); lc_var3 varchar2(1024); lc_var4 number; lc_var5 number; fullQName varchar2(512); avg_msg_age number; qtable varchar2(512); qry1 varchar2(32767); receivedB number := 0; processedB number := 0; receivedP number := 0; processedP number := 0; totalReceived number := 0; totalProcessed number := 0; finalPerc number := 0; pk varchar2(32767); subs_id number; subs_name varchar2(512); subs_address varchar2(1000); TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; test_cursor sys_refcursor; v_error_code number; v_error_message varchar2(255); begin begin begin EXECUTE IMMEDIATE 'SELECT * FROM AQ_MNTR_MSGS_SUBS where rownum <=1'; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); -- If table does not exist before then create it IF
v_error_code = -942 THEN -- ORA-00942: table or view does not exist EXECUTE IMMEDIATE 'CREATE TABLE AQ_MNTR_MSGS_SUBS (ID varchar2(2048) NOT NULL PRIMARY KEY, schema varchar2(256), queue_name varchar2(256), subs_name varchar2(256), subs_address varchar2(512), average_msg_age_subs number, totalProcessed number, totalReceived number, proc_perc number)'; END IF; end; --If no exception means table exists already. Clean it up before reusing EXECUTE IMMEDIATE 'DELETE FROM AQ_MNTR_MSGS_SUBS'; exception WHEN OTHERS THEN null; end; commit; -- Review comment => Do not include sys/system/sysman/dbsnmp schema as these might contain system queues for rec in (select owner, name, queue_table from dba_queues where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') ) LOOP dbms_output.put_line('+++++ Begin Processing for: ' ||rec.owner||' , '||rec.name|| ', '||rec.queue_table); begin -- Calculate the average message age per queue and store them in the AQ_MNTR_MSGS_SUBS table begin from
Clause := rec.owner||'.AQ$'||rec.queue_table; finalQry := 'select queue, consumer_name, address, avg(sysdate-ENQ_TIME) * 86400 AVERAGE_MSG_AGE from ' || fromClause ||' group by queue, consumer_name, address' ; DBMS_OUTPUT.PUT_LINE(finalQry); begin open test_cursor for finalQry; loop lc_var1 := ''; lc_var2 := ''; lc_var3 := ''; lc_var4 := 0; pk := ''; fetch test_cursor into lc_var1 , lc_var2, lc_var3, lc_var4; pk := rec.owner||':'||lc_var1||':'||lc_var2||':'||lc_var3; DBMS_OUTPUT.PUT_LINE('pk1: '||pk); begin IF lc_var4 != 0 THEN totalProcessed := null; totalReceived := null; finalPerc := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, lc_var1, lc_var2, lc_var3, lc_var4, totalProcessed, totalReceived, finalPerc; commit; END IF; exception WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE(' ***** Unique constaint error ***** '); end; exit when test_cursor %notfound; dbms_output.put_line('FETCHED D
ATA: ' ||lc_var1||' , '||lc_var2|| ', '||lc_var3||', '||lc_var4||', '||pk); end loop; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Could not open cursor ***** '); end; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating average age ***** '); end; -- Average message age per queue end -- Messages processed % per queue per subscriber. Calculate and update the AQ_MNTR_MSGS_SUBS table begin qtable := rec.owner||'.AQ$_'||rec.queue_table||'_S'; qry1 := 'select subscriber_id, name, address from ' || qtable || ' where queue_name='''||rec.name||''''; DBMS_OUTPUT.PUT_LINE(qry1); open test_cursor for qry1; loop subs_id := 0; subs_name := ''; subs_address := ''; fetch test_cursor into subs_id, subs_na
me, subs_address; pk := rec.owner||':'||rec.name||':'||subs_name||':'||subs_address; DBMS_OUTPUT.PUT_LINE('pk2: '||pk); begin begin execute immediate 'SELECT ENQUEUED_MSGS, DEQUEUED_MSGS from V$PERSISTENT_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedP, processedP using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** FIRST QUERY RETURNED NO DATA***** '); end; begin execute immediate 'SELECT CNUM_MSGS, (CNUM_MSGS-EXPIRED_MSGS-NUM_MSGS) from V$BUFFERED_SUBSCRIBERS where QUEUE_SCHEMA = :1 and QUEUE_NAME = :2 and SUBSCRIBER_ID = :3 ' into receivedB, processedB using rec.owner, rec.name, subs_id; exception WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(' ***** SECOND QUERY RETURNED NO DATA***** '); end; totalProcessed := processedB + processedP; totalReceived := receivedB + receivedP; IF totalReceived != 0 THEN finalPerc := (totalProcessed/totalReceived) * 100; execute immediate 'select count
(*) from AQ_MNTR_MSGS_SUBS where schema = :1 and queue_name = :2' into lc_var5 using rec.owner, rec.name; IF lc_var5 = 0 THEN avg_msg_age := null; execute immediate 'insert into AQ_MNTR_MSGS_SUBS values (:1, :2, :3, :4, :5, :6, :7, :8, :9)' using pk, rec.owner, rec.name, subs_name, subs_address, avg_msg_age, totalProcessed, totalReceived, finalPerc; ELSE execute immediate 'update AQ_MNTR_MSGS_SUBS set totalProcessed = :1, totalReceived = :2, proc_perc = :3 where ID = :4 ' using totalProcessed, totalReceived, finalPerc, pk; END IF; commit; END IF; -- Reset after using pk := ''; receivedB := 0; processedB := 0; receivedP := 0; processedP := 0; totalReceived := 0; totalProcessed := 0; finalPerc := 0; exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Insert into AQ_MNTR_MSGS_SUBS failed might be due to unique key constraint ***** '); en
d; exit when test_cursor %notfound; end loop; close test_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in calculating processed % ***** '); end; -- Messages processed % per queue end exception WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBMS_OUTPUT.PUT_LINE(' ***** Exception in main for loop ***** '); end; dbms_output.put_line('+++++ END Processing +++++ '); end loop; lv_sql_stmt:='select schema, queue_name, subs_name, subs_address, average_msg_age_subs, totalProcessed, totalReceived, proc_perc from AQ_MNTR_MSGS_SUBS'; open data_cursor for lv_sql_stmt; :1 := data_cursor; EXCEPTION WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SQLERRM; DBMS_OUTPUT.PUT_LINE(v_error_code); DBMS_OUTPUT.PUT_LINE(v_error_message); DBM
S_OUTPUT.PUT_LINE('>>>>>> Exception in main begin block <<<<<<<'); end; |
| 0wmwsjy9kd92f | SELECT PROFILE_OPTION_ID, APPLICATION_ID, SITE_ENABLED_FLAG , APP_ENABLED_FLAG , RESP_ENABLED_FLAG , USER_ENABLED_FLAG, ORG_ENABLED_FLAG , SERVER_ENABLED_FLAG, SERVERRESP_ENABLED_FLAG, HIERARCHY_TYPE, USER_CHANGEABLE_FLAG FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = :B1 AND START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE |
| 16614jtsr33r0 | begin fnd_date.initialize_with_calendar(:1, null, :2); end; |
| 1hfnkr719tvn4 |
SELECT /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53, iv PAY_INPUT_VALUES_F_N50 )*/ FND_NUMBER.CANONICAL_TO_NUMBER(PEV.SCREEN_ENTRY_VALUE) AMOUNT, NCR.ADD_OR_SUBTRACT ADD_OR_SUBTRACT FROM PAY_ACCRUAL_PLANS PAP, PAY_NET_CALCULATION_RULES NCR, PAY_ELEMENT_ENTRIES_F PEE, PAY_ELEMENT_ENTRY_VALUES_F PEV, PAY_INPUT_VALUES_F IV WHERE PAP.ACCRUAL_PLAN_ID = :B4 AND PEE.ASSIGNMENT_ID = :B3 AND PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID AND PEV.INPUT_VALUE_ID = NCR.INPUT_VALUE_ID AND PAP.ACCRUAL_PLAN_ID = NCR.ACCRUAL_PLAN_ID AND NCR.INPUT_VALUE_ID NOT IN (PAP.CO_INPUT_VALUE_ID, PAP.PTO_INPUT_VALUE_ID) AND PEV.SCREEN_ENTRY_VALUE IS NOT NULL AND ((:B2 IS NOT NULL AND :B2 = NCR.INPUT_VALUE_ID) OR :B2 IS NULL) AND PEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE AND PEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE AND IV.INPUT_VALUE_ID = NCR.INPUT_VALUE_ID AND :B1 BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE AND PEE.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID AND EXISTS (SELECT /*+ index(piv2
PAY_INPUT_VALUES_F_N50)*/ NULL FROM PAY_ELEMENT_ENTRY_VALUES_F PEV1, PAY_INPUT_VALUES_F PIV2 WHERE PEV1.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID AND PEV1.INPUT_VALUE_ID = NCR.DATE_INPUT_VALUE_ID AND PEV1.EFFECTIVE_START_DATE = PEV.EFFECTIVE_START_DATE AND PEV1.EFFECTIVE_END_DATE = PEV.EFFECTIVE_END_DATE AND NCR.DATE_INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID AND PEE.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID AND :B1 BETWEEN PIV2.EFFECTIVE_START_DATE AND PIV2.EFFECTIVE_END_DATE AND FND_DATE.CANONICAL_TO_DATE(DECODE(SUBSTR(PIV2.UOM, 1, 1), 'D', PEV1.SCREEN_ENTRY_VALUE, NULL)) BETWEEN :B5 AND :B1 ) |
| 1zr2ujm5sa5gc | begin fnd_oam_collection.refresh_app_sys_status; end; |
| 1zzm9z5n3bkd0 | SELECT o.name object_name, DECODE(o.TYPE#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY') object_type, DECODE(o.status, 1, 'V', 'I') status, o.mtime last_ddl_time, o.obj# object_id, o.ctime created FROM sys.obj$ o, sys.USER$ u WHERE o.owner# = u.USER# AND o.linkname IS NULL AND o.TYPE# IN (7, 8, 9, 11) AND U.name = :own order by 2, 1 |
| 2060dmj8nhqdb | SELECT EMPLOYEE_ID FROM FND_USER WHERE USER_ID = :B1 |
| 2xucgknahj256 | DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid; credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN begin if prvt_advisor.is_pack_enabled('DIAGNOSTIC') then dbsnmp.bsln_internal.maintain_statistics; end if; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
| 350f5yrnnmshs | lock table sys.mon_mods$ in exclusive mode nowait |
| 37kp7bjxfn4u5 | SELECT NLS_DATE_LANGUAGE FROM (SELECT UTF8_DATE_LANGUAGE NLS_DATE_LANGUAGE FROM FND_LANGUAGES WHERE NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) IN ('UTF8', 'AL32UTF8') AND INSTALLED_FLAG <>'D' AND NLS_LANGUAGE = :B1 UNION SELECT LOCAL_DATE_LANGUAGE NLS_DATE_LANGUAGE FROM FND_LANGUAGES WHERE NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) NOT IN ('UTF8', 'AL32UTF8') AND INSTALLED_FLAG <>'D' AND NLS_LANGUAGE = :B1 ) |
| 38bvus3wn71n6 | BEGIN portal_login (ora_login_user) ; END; |
| 39m4sx9k63ba2 | select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| 3g2ugdp1af2h8 | SELECT COUNT(*) FROM GV$SESSION GV, FND_CONCURRENT_PROCESSES P WHERE GV.INST_ID = P.INSTANCE_NUMBER AND GV.AUDSID = P.SESSION_ID AND (PROCESS_STATUS_CODE NOT IN ('S', 'K', 'U')) AND ( QUEUE_APPLICATION_ID = :B2 AND CONCURRENT_QUEUE_ID = :B1 ) |
| 3ktacv9r56b51 | select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# |
| 3mk9k6w2vqxv0 | SELECT * FROM (select task.task_number tasknumber, task.task_name, task_details taskdetails, task.task_id taskid, task.billable_flag, task.project_id, task.start_date, task.completion_date, task.chargeable_flag, proj.project_number from pa_online_tasks_v task , pa_online_projects_v proj where proj.project_id = task.project_id) QRSLT WHERE (project_id = :1) ORDER BY tasknumber |
| 3qnz8ru8bwzb3 | SELECT * FROM (select task.task_number tasknumber, task.task_name, task_details taskdetails, task.task_id taskid, task.billable_flag, task.project_id, task.start_date, task.completion_date, task.chargeable_flag, proj.project_number from pa_online_tasks_v task , pa_online_projects_v proj where proj.project_id = task.project_id) QRSLT WHERE (TaskId = :1 and project_id = 3067) ORDER BY tasknumber |
| 45jy2xbuaakfh | SELECT SEGMENT1 FROM PA_PROJECTS P WHERE P.PROJECT_ID = :B1 |
| 4u5zq7r9y690a |
WITH NONTIMEGROUPED_RAWDATA AS (SELECT MD.METRIC_ID , SH.END_TIME AS OBS_TIME , SH.VALUE AS OBS_VALUE FROM DBA_HIST_SNAPSHOT SN , DBA_HIST_DATABASE_INSTANCE DI , SYS.WRH$_SYSMETRIC_HISTORY SH , BSLN_METRIC_DEFAULTS MD WHERE SN.DBID = :B5 AND SN.SNAP_ID BETWEEN :B4 AND :B3 AND DI.DBID = SN.DBID AND DI.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND DI.STARTUP_TIME = SN.STARTUP_TIME AND DI.INSTANCE_NAME = :B2 AND SH.SNAP_ID = SN.SNAP_ID AND SH.DBID = SN.DBID AND SH.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND SH.GROUP_ID = 2 AND SH.METRIC_ID = MD.METRIC_ID AND MD.STATUS = :B1 ) SELECT BSLN_STATISTICS_T (:B12 , METRIC_ID , :B11 , :B10 , :B9 ||':'||:B9 , SAMPLE_COUNT , AVERAGE , MINIMUM , MAXIMUM , SDEV , PCTILE_25 , PCTILE_50 , PCTILE_75 , PCTILE_90 , PCTILE_95 , PCTILE_99 , EST_SAMPLE_COUNT , EST_SLOPE , EST_INTERCEPT , CASE WHEN EST_SLOPE = 0 THEN 0 ELSE GREATEST(0, NVL(100-(25*POWER((1-EST_MU1/EST_SLOPE), 2)*(EST_SAMPLE_COUNT-1) ), 0)) END , LN( 1000) * EST_SLOPE + EST_INTERCEPT , LN(10000) * ES
T_SLOPE + EST_INTERCEPT ) FROM (SELECT METRIC_ID , EST_MU AS EST_SLOPE , EST_MU * LN(ALPHA) + X_M AS EST_INTERCEPT , TO_NUMBER(NULL) AS EST_FIT_QUALITY , CASE WHEN COUNT_BELOW_X_J > 0 THEN (SUM_BELOW_X_J + (N-M+1)*(X_J-X_M))/COUNT_BELOW_X_J - X_J ELSE TO_NUMBER(NULL) END AS EST_MU1 , EST_SAMPLE_COUNT , N AS SAMPLE_COUNT , AVERAGE , MINIMUM , MAXIMUM , SDEV , PCTILE_25 , PCTILE_50 , PCTILE_75 , PCTILE_90 , PCTILE_95 , PCTILE_99 FROM (SELECT METRIC_ID , MAX(N) AS N , COUNT(RRANK) AS EST_SAMPLE_COUNT , CASE WHEN COUNT(RRANK) > 3 THEN ( SUM(OBS_VALUE) + ( MAX(N) - MAX(RRANK) ) * MAX(OBS_VALUE) - (MAX(N) - MIN(RRANK) + 1) * MIN(OBS_VALUE) ) / (COUNT(RRANK)-1) ELSE TO_NUMBER(NULL) END AS EST_MU , (MAX(N) - MIN(RRANK) + 1) / (MAX(N) + 1) AS ALPHA , MIN(OBS_VALUE) AS X_M , MAX(OBS_VALUE) AS X_L , MAX(RRANK) AS L , MIN(RRANK) AS M , MAX(MID_TAIL_VALUE) AS X_J , SUM(CASE WHEN OBS_VALUE < MID_TAIL_VALUE THEN OBS_VALUE ELSE 0 END ) AS SUM_BELOW_X_J , SUM(CASE WHEN CUME_DIST < :B6 THEN
1 ELSE 0 END ) AS COUNT_BELOW_X_J , MAX(MAX_VAL) AS MAXIMUM , MAX(MIN_VAL) AS MINIMUM , MAX(AVG_VAL) AS AVERAGE , MAX(SDEV_VAL) AS SDEV , MAX(PCTILE_25) AS PCTILE_25 , MAX(PCTILE_50) AS PCTILE_50 , MAX(PCTILE_75) AS PCTILE_75 , MAX(PCTILE_90) AS PCTILE_90 , MAX(PCTILE_95) AS PCTILE_95 , MAX(PCTILE_99) AS PCTILE_99 FROM (SELECT METRIC_ID , OBS_VALUE AS OBS_VALUE , CUME_DIST () OVER (PARTITION BY METRIC_ID ORDER BY OBS_VALUE ) AS CUME_DIST , COUNT(1) OVER (PARTITION BY METRIC_ID ) AS N , ROW_NUMBER () OVER (PARTITION BY METRIC_ID ORDER BY OBS_VALUE) AS RRANK , PERCENTILE_DISC(:B6 ) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS MID_TAIL_VALUE , MAX(OBS_VALUE) OVER (PARTITION BY METRIC_ID ) AS MAX_VAL , MIN(OBS_VALUE) OVER (PARTITION BY METRIC_ID ) AS MIN_VAL , AVG(OBS_VALUE) OVER (PARTITION BY METRIC_ID ) AS AVG_VAL , STDDEV(OBS_VALUE) OVER (PARTITION BY METRIC_ID ) AS SDEV_VAL , PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC
_ID) AS PCTILE_25 , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS PCTILE_50 , PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS PCTILE_75 , PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS PCTILE_90 , PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS PCTILE_95 , PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID) AS PCTILE_99 FROM NONTIMEGROUPED_RAWDATA D ) X WHERE X.CUME_DIST >= :B8 AND X.CUME_DIST <= :B7 GROUP BY METRIC_ID )) |
| 58swanyc6yya8 |
Select null as object_id, null as class_code, 'Notification' as class_name, null as type, null as object_name, null as object_number, null as reference, null as subject, decode(NtfEO.LANGUAGE, userenv('LANG'), NtfEO.SUBJECT, wf_notification.GetSubject(NtfEO.NOTIFICATION_ID)) as subject_restricted, ppl.name as project_name, null as project_number, null as project_id, null as project, null as task_name, null as task_number, null as task_id, null as owner_name, null as owner_party_id, null as status_name, null as status_icon, null as system_status_code, fnd_message.get_string('FND', decode(sign(NtfEO.PRIORITY-34), -1, 'WF_PRIORITY_HIGH', decode(sign(NtfEO.PRIORITY-67), -1, 'WF_PRIORITY_MEDIUM', 'WF_PRIORITY_LOW'))) AS PRIORITY, null as priority_sort, NtfEO.DUE_DATE as due_date, null as days_until_due, null as progress_status_name, null as progress_status_icon, null as progress_overview, null as as_of_date, null as percent_complete, null as start_date
, null as completion_date, null as update_icon, null as update_progress_icon, to_number(null) as object_version_id, to_number(null) as structure_version_id, null as task_progress_image, null as DvlrOrTask, null as TasksForDeliverables, null as percent_complete_id, null as progress_rollup_id, NtfEO.NOTIFICATION_ID, NtfEO.BEGIN_DATE, WUR.user_name as user_name, WUR.user_orig_system, WUR.user_orig_system_id, 'N' as w_or_n From WF_NOTIFICATIONS NtfEO, PA_WF_PROCESSES pa, WF_ITEM_ACTIVITY_STATUSES wfItems, WF_USER_ROLES WUR, pa_projects_all ppl Where NtfEO.recipient_role = WUR.role_name and NtfEO.STATUS='OPEN' and wfItems.NOTIFICATION_ID = NtfEO.GROUP_ID and pa.item_type = wfItems.item_type and pa.item_key = wfItems.item_key and WUR.user_name = :1 and WUR.user_orig_system = :2 and WUR.user_orig_system_id = :3 and pa.entity_key1 = ppl.project_id union Select palp.object_id, palp.class_code, palp.class_name, palp.type, palp.object_name, palp.object_number, palp.objec
t_name || ' (' || palp.object_number || ')' reference, palp.subject, decode(greatest(length(nvl(palp.subject, ' ')), 180), 180, palp.subject, substr(palp.subject, 1, 180)|| '...') subject_restricted, palp.project_name, palp.project_number, palp.project_id, palp.project_name || '(' || palp.project_number || ')' project, palp.task_name, palp.task_number, palp.task_id, palp.owner_name, palp.owner_party_id, palp.status_name, palp.status_icon, palp.system_status_code, palp.priority, palp.priority_sort, palp.due_date, palp.days_until_due, palp.progress_status_name, palp.progress_status_icon, palp.progress_overview, palp.as_of_date, palp.percent_complete, palp.start_date, palp.completion_date, decode(palp.class_code, 'PA_TASKS', decode(PA_PROJ_ELEMENTS_UTILS.CHECK_USER_VIEW_TASK_PRIVILEGE(palp.project_id), 'T', 'EDIT', 'VIEW'), 'PA_DELIVERABLES', decode(PA_DELIVERABLE_UTILS.CHECK_USER_VIEW_DLV_PRIVILEGE(palp.project_id), 'T', 'EDIT', 'VIEW'), decode(pa_ci_security
_pkg.check_view_access(palp.object_id), 'T', 'EDIT', 'VIEW') )update_icon, decode(PA_PROGRESS_UTILS.is_object_progressable(palp.project_id, palp.object_id, null, palp.class_code), 'Y', 'PROGEDIT', 'PROGVIEW') as update_progress_icon, palp.object_version_id, palp.structure_version_id, decode(palp.progress_status_icon, NULL, 'NoProgStatIcon', 'ProgStatIcon') as task_progress_image, decode(palp.class_code, 'PA_DELIVERABLES' , 'Deliverables', 'PA_TASKS', 'Tasks', 'ControlItems') as DvlrOrTask, palp.tasks_for_deliverables TasksForDeliverables, 0 as percent_complete_id, 0 as progress_rollup_id, to_number(null) as NOTIFICATION_ID, to_date(null) as BEGIN_DATE , null as user_name, null as user_orig_system, null as user_orig_system_id, 'W' as w_or_n from PA_LAUNCH_PAGE_OBJECTS_TM_V palp, PA_PROJECTS_ALL ppa WHERE ppa.project_id = palp.project_id AND ppa.project_status_code NOT IN (select project_status_code from pa_project_statuses where status_type = 'PROJECT' and project
_system_status_code = 'CLOSED') AND palp.owner_party_id = :4 |
| 5ptu63k5gu7f3 | BEGIN FFP1101_01011950.FORMULA;END; |
| 5yv7yvjgjxugg | select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO Pool Memory' |
| 67mmmbx5hb3p0 | BEGIN :1 := FND_AOLJ_UTIL.is_Valid_ICX(:2, NULL, 'Y', TRUE, TRUE, NULL, NULL, NULL, NULL, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, true);:19 := fnd_session_management.g_enc_key;:20 := fnd_session_management.g_mac_key;:21 := fnd_session_management.g_proxy_user_id; :22 := fnd_session_management.g_mode_code; END; |
| 6d9gv6u9vn1mf | INSERT INTO MGMT_METRICS_1HOUR ( TARGET_GUID, METRIC_GUID, ROLLUP_TIMESTAMP, KEY_VALUE, SAMPLE_COUNT, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV ) VALUES ( :B9 , :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 ) |
| 6gvch1xu9ca3g | DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
| 6vtxunqng57nu | BEGIN :1 := jdr_mds_internal.exportDocumentAsXML(:2, :3, :4, :5, :6); END; |
| 7qs7fx89194u1 | SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = :B2 AND LEVEL_VALUE = :B1 AND PROFILE_OPTION_VALUE IS NOT NULL |
| 8apzhx22jzx69 |
SELECT * FROM (select decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'), 'G', ppf.global_name, ppf.local_name) full_name, paf.assignment_number, ppf.employee_number, ppf.npw_number , hr_person_type_usage_info.get_user_person_type(ppf.effective_end_date, ppf.person_id) person_type , assignment_type , HR_GENERAL.DECODE_LOOKUP('YES_NO', paf.primary_flag) primary_flag , ppf.original_date_of_hire, ppf.start_date , bus.name business_group, org.name organization , hr_util_misc_ss.getObjectName('JOB', paf.job_id, ppf.business_group_id, j.name) job , hr_util_misc_ss.getObjectName('GRADE', paf.grade_id, ppf.business_group_id, g.name) grade , hr_util_misc_ss.getObjectName('POSITION', paf.position_id, ppf.business_group_id, p.name) position , l.location_code, decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'), 'G', sup.global_name, sup.local_name) supervisor , Decode(paf.assignment_type, 'C', hr_general.decode_lookup('CWK_ASG_CATEGORY', paf.employment_category)
, hr_general.decode_lookup('EMP_CAT', paf.employment_category) )employment_category , paf.assignment_category , ppf.email_address, (select ph.phone_number from per_phones ph where ppf.person_id = ph.parent_id and ph.parent_table = 'PER_ALL_PEOPLE_F' and ph.phone_type = 'W1' and fs.effective_date between ph.date_from and nvl(ph.date_to, fs.effective_date)) work_telephone , payroll.payroll_name, hr_util_misc_ss.get_employee_salary(paf.assignment_id, fs.effective_date) annual_salary , pb.name pay_basis, null currency_code , HR_GENERAL.DECODE_LOOKUP('FREQUENCY', paf.perf_review_period_frequency) perf_freq , paf.perf_review_period, ppr.review_date , ppr.next_perf_review_date , appr.overall_performance_level_id , decode(ppr.performance_rating, null, null, ppr.performance_rating||' - '||hr_general.decode_lookup('PERFORMANCE_RATING', ppr.performance_rating)) last_performance_rating , hr_mee_views_gen.getYOS(ppf.person_id) years_of_service , hr_mee_views_gen.get_total_absences(ppf.pe
rson_id) total_number_of_absences , hr_mee_views_gen.get_total_absence_days(ppf.person_id) total_absence_days , hr_mee_views_gen.get_total_absence_hours(ppf.person_id) total_absence_hours , nvl(paf.effective_start_date, ppf.effective_start_date) asgn_start_date , decode(nvl(paf.effective_end_date, ppf.effective_end_date), to_date('31/12/4712', 'DD/MM/RRRR'), to_date(null), nvl(paf.effective_end_date, ppf.effective_end_date)) asgn_end_date, nvl(pos.date_start, pop.date_start) latest_hire_date, decode(paf.assignment_type, 'E', 1, 0) render_emp, decode(paf.assignment_type, 'C', 1, 0) render_cwk, hr_mee_views_gen.getTrngDays(ppf.person_id) total_training_days, hr_mee_views_gen.getTrngHrs(ppf.person_id) total_training_hours, (select hr_general.decode_lookup('YES_NO', nvl(max('Y'), 'N')) from per_applications a where a.person_id = ppf.person_id and fs.effective_date between a.date_received and nvl(a.date_end, to_date('31/12/4712', 'DD/MM/RRRR'))) applications_exist, paf.a
ssignment_id, paf.primary_flag prim_flag, (select hr_util_misc_ss.get_in_preferred_currency_str(hr_util_misc_ss.get_employee_salary(paf.assignment_id, fs.effective_date, ppp.proposed_salary_n, pay_annualization_factor, pb.pay_basis) , petf.input_currency_code, trunc(sysdate)) from pay_input_values_f ivf, pay_element_types_f petf where pb.input_value_id = ivf.input_value_id and ppp.change_date between ivf.effective_start_date and ivf.effective_end_date and ivf.element_type_id = petf.element_type_id and ppp.change_date between petf.effective_start_date and petf.effective_end_date) multi_currency, pos.adjusted_svc_date, hr_mee_views_gen.getAYOS(ppf.person_id) adjusted_years_of_service, hr_mee_views_gen.get_grade_details(paf.assignment_id, 'MIN') minimum, hr_mee_views_gen.get_grade_details(paf.assignment_id, 'MID') mid_value, hr_mee_views_gen.get_grade_details(paf.assignment_id, 'MAX') maximum, hr_mee_views_gen.get_grade_details(paf.assignment_id, 'COMPARATIO') comparatio fr
om per_all_people_f ppf, per_all_assignments_f paf , hr_all_organization_units_tl bus, hr_all_organization_units_tl org , per_jobs_tl j, per_grades_tl g, hr_all_positions_f_tl p, hr_locations_all_tl l , per_all_people_f sup, pay_all_payrolls_f payroll , per_pay_proposals ppp, per_pay_bases pb, per_performance_reviews ppr , per_events pe, per_appraisals appr , per_periods_of_service pos, per_periods_of_placement pop, fnd_sessions fs where (paf.person_id=:1 or paf.person_id=(select from_person_id from hr_person_deployments dep where dep.from_person_id=:2 and permanent='Y')) and fs.session_id = userenv('sessionid') and ppf.person_id = paf.person_id and paf.assignment_type in ('E', 'C') and paf.period_of_service_id = pos.period_of_service_id(+) and paf.period_of_placement_date_start = pop.date_start(+) and paf.person_id = pop.person_id(+) and paf.pay_basis_id = pb.pay_basis_id(+) and paf.assignment_id = ppp.assignment_id(+) and ppp.pay_proposal_id(+) = hr_mee_views_gen.getAsgPro
posalId(paf.assignment_id) and ppp.approved(+) = 'Y' and ppf.person_id = ppr.person_id(+) and ppr.performance_review_id(+) = hr_mee_views_gen.getPrsnPerformanceId(ppf.person_id) and paf.payroll_id = payroll.payroll_id(+) and paf.effective_start_date between payroll.effective_start_date(+) and payroll.effective_end_date(+) and paf.supervisor_id = sup.person_id(+) and paf.effective_end_date between sup.effective_start_date(+) and sup.effective_end_date(+) and ppf.business_group_id = bus.organization_id and bus.language = userenv('LANG') and paf.organization_id = org.organization_id and org.language = userenv('LANG') and paf.job_id = j.job_id(+) and j.language(+) = userenv('LANG') and paf.grade_id = g.grade_id(+) and g.language(+) = userenv('LANG') and paf.position_id = p.position_id(+) and p.language(+) = userenv('LANG') and paf.location_id = l.location_id(+) and l.language(+) = userenv('LANG') and fs.effective_date between ppf.effective_start_date and ppf.effective_end_date and fs.effec
tive_date between paf.effective_start_date and paf.effective_end_date and ppr.event_id = pe.event_id(+) and pe.event_id = appr.event_id(+)) QRSLT WHERE (prim_flag = 'Y') |
| 8nvf8d416jaz8 | declare session_id number; transaction_id number; begin session_id := FND_SESSION_UTILITIES.XSID_to_SessionID(:1); transaction_id := FND_SESSION_MANAGEMENT.createTransaction(session_id); :2 := transaction_id; end; |
| 8swypbbr0m372 | select order#, columns, types from access$ where d_obj#=:1 |
| 8w57t3xaxs89k | SELECT NAME, VERSION FROM FND_CACHE_VERSIONS |
| 95v065jvvprj6 | SELECT HR_GENERAL.DECODE_LOOKUP('PQH_SS_TRANSACTION_STATUS', DECODE(STATUS, 'RI', 'C', 'RIS', 'S', 'RO', 'Y', 'ROS', 'Y', 'YS', 'Y', STATUS)) FROM HR_API_TRANSACTIONS WHERE TRANSACTION_ID=:B1 |
| 96g93hntrzjtr | select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 |
| 9m58xu91nzqy2 |
SELECT V.PROFILE_OPTION_VALUE, V.LEVEL_ID FROM FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V WHERE O.PROFILE_OPTION_NAME = :1 AND O.START_DATE_ACTIVE <= SYSDATE AND (NVL(O.END_DATE_ACTIVE, SYSDATE) >= SYSDATE) AND (V.LEVEL_ID = 10001 AND V.LEVEL_VALUE = 0) AND O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND O.APPLICATION_ID = V.APPLICATION_ID UNION ALL SELECT V.PROFILE_OPTION_VALUE, V.LEVEL_ID FROM FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V WHERE O.PROFILE_OPTION_NAME = :2 AND O.START_DATE_ACTIVE <= SYSDATE AND (NVL(O.END_DATE_ACTIVE, SYSDATE) >= SYSDATE) AND (V.LEVEL_ID = 10002 AND V.LEVEL_VALUE = :3) AND O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND O.APPLICATION_ID = V.APPLICATION_ID UNION ALL SELECT V.PROFILE_OPTION_VALUE, V.LEVEL_ID FROM FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V WHERE O.PROFILE_OPTION_NAME = :4 AND O.START_DATE_ACTIVE <= SYSDATE AND (NVL(O.END_DATE_ACTIVE, SYSDATE) >= SYSDATE) AND (V.LEVEL_ID = 10003 AND V.LEVEL_VALUE_AP
PLICATION_ID = :5 AND V.LEVEL_VALUE = :6) AND O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND O.APPLICATION_ID = V.APPLICATION_ID UNION ALL SELECT V.PROFILE_OPTION_VALUE, V.LEVEL_ID FROM FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V WHERE O.PROFILE_OPTION_NAME = :7 AND O.START_DATE_ACTIVE <= SYSDATE AND (NVL(O.END_DATE_ACTIVE, SYSDATE) >= SYSDATE) AND (V.LEVEL_ID = 10004 AND V.LEVEL_VALUE = :8) AND O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID AND O.APPLICATION_ID = V.APPLICATION_ID ORDER BY 2 DESC |
| 9vfvgsk7mtkr4 |
SELECT BSLN_VARIANCE_T( SS.METRIC_ID , B.BSLN_GUID , (CASE WHEN HOUR_OF_DAY IS NULL THEN (CASE WHEN DAY_NIGHT IS NULL THEN :B13 ELSE :B15 END) ELSE :B14 END)|| (CASE WHEN DAY_OF_WEEK IS NULL THEN (CASE WHEN WEEKDAY_WEEKEND IS NULL THEN :B13 ELSE :B12 END) ELSE :B11 END) , (CASE WHEN HOUR_OF_DAY IS NULL THEN (CASE WHEN DAY_NIGHT IS NULL THEN 24 ELSE 12 END) ELSE 1 END)* (CASE WHEN DAY_OF_WEEK IS NULL THEN (CASE WEEKDAY_WEEKEND WHEN :B10 THEN 2 WHEN :B9 THEN 5 ELSE 7 END) ELSE 1 END) , SUM(NUM_INTERVAL) , CASE WHEN SUM(SS.NUM_INTERVAL) > 1 THEN (SUM(SS.SUM_SQUARES) - (SUM(SS.NUM_INTERVAL*SS.AVERAGE)*SUM(SS.NUM_INTERVAL*SS.AVERAGE))/SUM(SS.NUM_INTERVAL))/ (SUM(SS.NUM_INTERVAL)-1) ELSE 0 END ) FROM BSLN_BASELINES B , DBA_HIST_BASELINE_METADATA AB , DBA_HIST_SNAPSHOT SN , DBA_HIST_DATABASE_INSTANCE DI , DBA_HIST_SYSMETRIC_SUMMARY SS , (SELECT HOUR AS HOUR_OF_WEEK , TRUNC(HOUR/24) AS DAY_OF_WEEK , MOD(HOUR, 24) AS HOUR_OF_DAY , INTRADAY AS DAY_NIGHT , EXTRADAY AS WEEKDAY_WEEKEND FROM BSLN
_TIMEGROUPS) TG WHERE B.DBID = :B8 AND B.AUTO_TIMEGROUP = :B7 AND AB.BASELINE_ID = B.BASELINE_ID AND AB.DBID = B.DBID AND AB.BASELINE_TYPE = :B6 AND SN.DBID = B.DBID AND SN.BEGIN_INTERVAL_TIME >= :B1 - LEAST(AB.MOVING_WINDOW_SIZE, :B2 ) AND DI.DBID = SN.DBID AND DI.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND DI.STARTUP_TIME = SN.STARTUP_TIME AND DI.INSTANCE_NAME = B.INSTANCE_NAME AND SS.SNAP_ID = SN.SNAP_ID AND SS.DBID = SN.DBID AND SS.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND SS.GROUP_ID = 2 AND SS.METRIC_ID IN (:B5 , :B4 , :B3 ) AND SS.BEGIN_TIME + (SS.END_TIME - SS.BEGIN_TIME)/2 BETWEEN :B1 - LEAST(AB.MOVING_WINDOW_SIZE, :B2 ) AND :B1 AND TG.HOUR_OF_WEEK = BSLN_INTERNAL.HOUR_OF_WEEK(SS.BEGIN_TIME + (SS.END_TIME - SS.BEGIN_TIME)/2) GROUP BY GROUPING SETS ((SS.METRIC_ID, B.BSLN_GUID, HOUR_OF_DAY, DAY_OF_WEEK), (SS.METRIC_ID, B.BSLN_GUID, HOUR_OF_DAY, WEEKDAY_WEEKEND), (SS.METRIC_ID, B.BSLN_GUID, HOUR_OF_DAY), (SS.METRIC_ID, B.BSLN_GUID, DAY_NIGHT, DAY_OF_WEEK), (SS.METRIC_ID, B.BSL
N_GUID, DAY_NIGHT, WEEKDAY_WEEKEND), (SS.METRIC_ID, B.BSLN_GUID, DAY_NIGHT), (SS.METRIC_ID, B.BSLN_GUID, DAY_OF_WEEK), (SS.METRIC_ID, B.BSLN_GUID, WEEKDAY_WEEKEND), (SS.METRIC_ID, B.BSLN_GUID)) |
| a2pyvpayj0nam |
INSERT INTO PJI_FP_AGGR_PJP1_T ( WORKER_ID, RECORD_TYPE, PRG_LEVEL, LINE_TYPE, PROJECT_ID, PROJECT_ORG_ID, PROJECT_ORGANIZATION_ID, PROJECT_ELEMENT_ID, TIME_ID, PERIOD_TYPE_ID, CALENDAR_TYPE, RBS_AGGR_LEVEL, WBS_ROLLUP_FLAG, PRG_ROLLUP_FLAG, CURR_RECORD_TYPE_ID, CURRENCY_CODE, RBS_ELEMENT_ID, RBS_VERSION_ID, PLAN_VERSION_ID, PLAN_TYPE_ID, PLAN_TYPE_CODE, RAW_COST, BRDN_COST, REVENUE, BILL_RAW_COST, BILL_BRDN_COST, BILL_LABOR_RAW_COST, BILL_LABOR_BRDN_COST, BILL_LABOR_HRS, EQUIPMENT_RAW_COST, EQUIPMENT_BRDN_COST, CAPITALIZABLE_RAW_COST, CAPITALIZABLE_BRDN_COST, LABOR_RAW_COST, LABOR_BRDN_COST, LABOR_HRS, LABOR_REVENUE, EQUIPMENT_HOURS, BILLABLE_EQUIPMENT_HOURS, SUP_INV_COMMITTED_COST, PO_COMMITTED_COST, PR_COMMITTED_COST, OTH_COMMITTED_COST, ACT_LABOR_HRS, ACT_EQUIP_HRS, ACT_LABOR_BRDN_COST, ACT_EQUIP_BRDN_COST, ACT_BRDN_COST, ACT_RAW_COST, ACT_REVENUE, ACT_LABOR_RAW_COST, ACT_EQUIP_RAW_COST, ETC_LABOR_HRS, ETC_EQUIP_HRS, ETC_LABOR_B
RDN_COST, ETC_EQUIP_BRDN_COST, ETC_BRDN_COST, ETC_RAW_COST, ETC_LABOR_RAW_COST, ETC_EQUIP_RAW_COST, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4, CUSTOM5, CUSTOM6, CUSTOM7, CUSTOM8, CUSTOM9, CUSTOM10, CUSTOM11, CUSTOM12, CUSTOM13, CUSTOM14, CUSTOM15 ) SELECT PJP1_I.WORKER_ID, PJP1_I.RECORD_TYPE, PJP1_I.PRG_LEVEL, PJP1_I.LINE_TYPE, PJP1_I.PROJECT_ID, PJP1_I.PROJECT_ORG_ID, PJP1_I.PROJECT_ORGANIZATION_ID, PJP1_I.PROJECT_ELEMENT_ID, PJP1_I.TIME_ID, PJP1_I.PERIOD_TYPE_ID, PJP1_I.CALENDAR_TYPE, PJP1_I.RBS_AGGR_LEVEL, PJP1_I.WBS_ROLLUP_FLAG, PJP1_I.PRG_ROLLUP_FLAG, PJP1_I.CURR_RECORD_TYPE_ID, PJP1_I.CURRENCY_CODE, PJP1_I.RBS_ELEMENT_ID, PJP1_I.RBS_VERSION_ID, PJP1_I.PLAN_VERSION_ID, PJP1_I.PLAN_TYPE_ID, PJP1_I.PLAN_TYPE_CODE, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' ||
PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.RAW_COST)) RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BRDN_COST)) BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.REVENUE)) REVENUE, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '
_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILL_RAW_COST)) BILL_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILL_BRDN_COST)) BILL_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_
' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILL_LABOR_RAW_COST)) BILL_LABOR_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILL_LABOR_BRDN_COST)) BILL_LABOR_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILL_LABOR_HRS)) BILL_LABOR_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_'
|| PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.EQUIPMENT_RAW_COST)) EQUIPMENT_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.EQUIPMENT_BRDN_COST)) EQUIPMENT_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATI
ONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.CAPITALIZABLE_RAW_COST)) CAPITALIZABLE_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.CAPITALIZABLE_BRDN_COST)) CAPITALIZABLE_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.LABOR_RAW_C
OST)) LABOR_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.LABOR_BRDN_COST)) LABOR_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.LABOR_HRS)) LABOR_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO
_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.LABOR_REVENUE)) LABOR_REVENUE, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.EQUIPMENT_HOURS)) EQUIPMENT_HOURS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.BILLABLE_
EQUIPMENT_HOURS)) BILLABLE_EQUIPMENT_HOURS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.SUP_INV_COMMITTED_COST)) SUP_INV_COMMITTED_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.PO_COMMITTED_COST)) PO_COMMITTED_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_COD
E || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.PR_COMMITTED_COST)) PR_COMMITTED_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUB_STATUS_CODE || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y__', TO_NUMBER(NULL), DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_VER_ENABLED, 'LW_N_Y_Y', TO_NUMBER(NULL), PJP1_I.OTH_COMMITTED_COST)) OTH_COMMITTED_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_LABOR_HRS) ACT_LABOR_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJ
P1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_EQUIP_HRS) ACT_EQUIP_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_BRDN_COST) ACT_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_RAW_COST) ACT_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.P
RG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_REVENUE) ACT_REVENUE, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_LABOR_HRS) ETC_LABOR_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_EQUIP_HRS) ETC_EQUIP_HRS, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FL
AG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_BRDN_COST) ETC_BRDN_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_RAW_COST) ETC_RAW_COST, DECODE(PJP1_I.RELATIONSHIP_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST, DECODE(PJP1_I.RELATIONSHI
P_TYPE || '_' || PJP1_I.WBS_ROLLUP_FLAG || '_' || PJP1_I.PRG_ROLLUP_FLAG || '_' || PJP1_I.SUP_STATUS_CODE, 'LW_N_Y_', TO_NUMBER(NULL), PJP1_I.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST, PJP1_I.CUSTOM1, PJP1_I.CUSTOM2, PJP1_I.CUSTOM3, PJP1_I.CUSTOM4, PJP1_I.CUSTOM5, PJP1_I.CUSTOM6, PJP1_I.CUSTOM7, PJP1_I.CUSTOM8, PJP1_I.CUSTOM9, PJP1_I.CUSTOM10, PJP1_I.CUSTOM11, PJP1_I.CUSTOM12, PJP1_I.CUSTOM13, PJP1_I.CUSTOM14, PJP1_I.CUSTOM15 FROM ( SELECT DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.INSERT_FLAG, 'Y') INSERT_FLAG, PJP.RELATIONSHIP_TYPE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUB_VER.STATUS_CODE) SUB_STATUS_CODE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUP_VER.STATUS_CODE) SUP_STATUS_CODE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUP_WPA.WP_ENABLE_VERSION_FLAG)SUP_VER_ENABLED, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECOD
E(PJP.PLAN_VERSION_ID, -1, PRG.SUP_ID, -3, PRG.SUP_ID, -4, PRG.SUP_ID, NULL)) SUP_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECODE(PJP.PLAN_VERSION_ID, -1, PRG.SUP_EMT_ID, -3, PRG.SUP_EMT_ID, -4, PRG.SUP_EMT_ID, NULL)) SUP_EMT_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECODE(PJP.PLAN_VERSION_ID, -1, PRG.WP_FLAG, -3, PRG.WP_FLAG, -4, PRG.WP_FLAG, NULL)) SUP_WP_FLAG, 1 WORKER_ID, 'W' RECORD_TYPE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), :B1 , PRG.SUP_LEVEL) PRG_LEVEL, PJP.LINE_TYPE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ID, PRG.SUP_PROJECT_ID) PROJECT_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ORG_ID, PRG.SUP_PROJECT_ORG_ID) PROJECT_ORG_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ORGANIZATION_ID, PRG.SUP_PROJECT_ORGANIZATION_ID) PROJECT_ORGANIZATION_ID, DEC
ODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ELEMENT_ID, PRG.SUB_ROLLUP_ID) PROJECT_ELEMENT_ID, PJP.TIME_ID, PJP.PERIOD_TYPE_ID, PJP.CALENDAR_TYPE, PJP.RBS_AGGR_LEVEL, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.WBS_ROLLUP_FLAG, 'N') WBS_ROLLUP_FLAG, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PRG_ROLLUP_FLAG, 'Y') PRG_ROLLUP_FLAG, PJP.CURR_RECORD_TYPE_ID, PJP.CURRENCY_CODE, PJP.RBS_ELEMENT_ID, PJP.RBS_VERSION_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PLAN_VERSION_ID, DECODE(PJP.PLAN_VERSION_ID, -1, PJP.PLAN_VERSION_ID, -2, PJP.PLAN_VERSION_ID, -3, PJP.PLAN_VERSION_ID, -4, PJP.PLAN_VERSION_ID, WBS_HDR.PLAN_VERSION_ID)) PLAN_VERSION_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PLAN_TYPE_ID, DECODE(PJP.PLAN_VERSION_ID, -1, PJP.PLAN_TYPE_ID, -2, PJP.PLAN_TYPE_ID, -3, PJP.PLAN_TYPE_ID, -4, PJP.PLAN_TYPE_ID, WBS_HDR.PLAN_TYPE
_ID)) PLAN_TYPE_ID, PJP.PLAN_TYPE_CODE, SUM(PJP.RAW_COST) RAW_COST, SUM(PJP.BRDN_COST) BRDN_COST, SUM(PJP.REVENUE) REVENUE, SUM(PJP.BILL_RAW_COST) BILL_RAW_COST, SUM(PJP.BILL_BRDN_COST) BILL_BRDN_COST, SUM(PJP.BILL_LABOR_RAW_COST) BILL_LABOR_RAW_COST, SUM(PJP.BILL_LABOR_BRDN_COST) BILL_LABOR_BRDN_COST, SUM(PJP.BILL_LABOR_HRS) BILL_LABOR_HRS, SUM(PJP.EQUIPMENT_RAW_COST) EQUIPMENT_RAW_COST, SUM(PJP.EQUIPMENT_BRDN_COST) EQUIPMENT_BRDN_COST, SUM(PJP.CAPITALIZABLE_RAW_COST) CAPITALIZABLE_RAW_COST, SUM(PJP.CAPITALIZABLE_BRDN_COST) CAPITALIZABLE_BRDN_COST, SUM(PJP.LABOR_RAW_COST) LABOR_RAW_COST, SUM(PJP.LABOR_BRDN_COST) LABOR_BRDN_COST, SUM(PJP.LABOR_HRS) LABOR_HRS, SUM(PJP.LABOR_REVENUE) LABOR_REVENUE, SUM(PJP.EQUIPMENT_HOURS) EQUIPMENT_HOURS, SUM(PJP.BILLABLE_EQUIPMENT_HOURS) BILLABLE_EQUIPMENT_HOURS, SUM(PJP.SUP_INV_COMMITTED_COST) SUP_INV_COMMITTED_COST, SUM(PJP.PO_COMMITTED_COST) PO_COMMITTED_COST, SUM(PJP.PR_COMMITTED_COST) PR_COMMITTED_COST, SUM(PJP.OTH_COMMITTED
_COST) OTH_COMMITTED_COST, SUM(PJP.ACT_LABOR_HRS) ACT_LABOR_HRS, SUM(PJP.ACT_EQUIP_HRS) ACT_EQUIP_HRS, SUM(PJP.ACT_LABOR_BRDN_COST) ACT_LABOR_BRDN_COST, SUM(PJP.ACT_EQUIP_BRDN_COST) ACT_EQUIP_BRDN_COST, SUM(PJP.ACT_BRDN_COST) ACT_BRDN_COST, SUM(PJP.ACT_RAW_COST) ACT_RAW_COST, SUM(PJP.ACT_REVENUE) ACT_REVENUE, SUM(PJP.ACT_LABOR_RAW_COST) ACT_LABOR_RAW_COST, SUM(PJP.ACT_EQUIP_RAW_COST) ACT_EQUIP_RAW_COST, SUM(PJP.ETC_LABOR_HRS) ETC_LABOR_HRS, SUM(PJP.ETC_EQUIP_HRS) ETC_EQUIP_HRS, SUM(PJP.ETC_LABOR_BRDN_COST) ETC_LABOR_BRDN_COST, SUM(PJP.ETC_EQUIP_BRDN_COST) ETC_EQUIP_BRDN_COST, SUM(PJP.ETC_BRDN_COST) ETC_BRDN_COST, SUM(PJP.ETC_RAW_COST) ETC_RAW_COST, SUM(PJP.ETC_LABOR_RAW_COST) ETC_LABOR_RAW_COST, SUM(PJP.ETC_EQUIP_RAW_COST) ETC_EQUIP_RAW_COST, SUM(PJP.CUSTOM1) CUSTOM1, SUM(PJP.CUSTOM2) CUSTOM2, SUM(PJP.CUSTOM3) CUSTOM3, SUM(PJP.CUSTOM4) CUSTOM4, SUM(PJP.CUSTOM5) CUSTOM5, SUM(PJP.CUSTOM6) CUSTOM6, SUM(PJP.CUSTOM7) CUSTOM7, SUM(PJP.CUSTOM8) CUSTOM8, SUM(PJP.CUSTO
M9) CUSTOM9, SUM(PJP.CUSTOM10) CUSTOM10, SUM(PJP.CUSTOM11) CUSTOM11, SUM(PJP.CUSTOM12) CUSTOM12, SUM(PJP.CUSTOM13) CUSTOM13, SUM(PJP.CUSTOM14) CUSTOM14, SUM(PJP.CUSTOM15) CUSTOM15 FROM ( SELECT /*+ ordered index(wbs PA_XBS_DENORM_N2) */ TO_CHAR(NULL) LINE_TYPE, WBS_HDR.WBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE, DECODE(WBS_HDR.WP_FLAG || '_' || TO_CHAR(SIGN(PJP1.PLAN_VERSION_ID)) || '_' || NVL(FIN_PLAN.INVERT_ID, 'PRJ'), 'N_1_PRJ', 'N', 'N_-1_PRG', 'N', DECODE(TOP_SLICE.INVERT_ID, 'PRJ', 'Y', DECODE(WBS.SUB_LEVEL, 1, 'Y', 'N'))) PUSHUP_FLAG, DECODE(PJP1.RBS_AGGR_LEVEL, 'L', 'N', DECODE(WBS_HDR.WP_FLAG || '_' || TO_CHAR(SIGN(PJP1.PLAN_VERSION_ID)) || '_' || FIN_PLAN.INVERT_ID, 'N_1_PRG', DECODE(TOP_SLICE.INVERT_ID, 'PRJ', 'Y', DECODE(WBS.SUB_LEVEL, 1, 'Y', 'N')), 'N_-1_PRG', 'N', DECODE(WBS_HDR.WP_FLAG || '_' || FIN_PLAN.INVERT_ID || '_' || FIN_PLAN.CB || '_' || FIN_PLAN.CO || '_' || TO_CHAR(FIN_PLAN.PLAN_VERSION_ID)
, 'N_PRJ_Y_Y_-4', 'N', 'Y')) ) INSERT_FLAG, PJP1.PROJECT_ID, PJP1.PROJECT_ORG_ID, PJP1.PROJECT_ORGANIZATION_ID, DECODE(TOP_SLICE.INVERT_ID, 'PRJ', PRG.SUP_EMT_ID, DECODE(WBS.SUB_LEVEL, 1, PRG.SUP_EMT_ID, WBS.SUP_EMT_ID)) PROJECT_ELEMENT_ID, PJP1.TIME_ID, PJP1.PERIOD_TYPE_ID, PJP1.CALENDAR_TYPE, PJP1.RBS_AGGR_LEVEL, 'Y' WBS_ROLLUP_FLAG, PJP1.PRG_ROLLUP_FLAG, PJP1.CURR_RECORD_TYPE_ID, PJP1.CURRENCY_CODE, PJP1.RBS_ELEMENT_ID, PJP1.RBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG || '_' || FIN_PLAN.INVERT_ID, 'N_PRG', FIN_PLAN.PLAN_VERSION_ID, PJP1.PLAN_VERSION_ID) PLAN_VERSION_ID, PJP1.PLAN_TYPE_ID, PJP1.PLAN_TYPE_CODE, PJP1.RAW_COST, PJP1.BRDN_COST, PJP1.REVENUE, PJP1.BILL_RAW_COST, PJP1.BILL_BRDN_COST, PJP1.BILL_LABOR_RAW_COST, PJP1.BILL_LABOR_BRDN_COST, PJP1.BILL_LABOR_HRS, PJP1.EQUIPMENT_RAW_COST, PJP1.EQUIPMENT_BRDN_COST, PJP1.CAPITALIZABLE_RAW_COST, PJP1.CAPITALIZABLE_BRDN_COST, PJP1.LABOR_RAW_COST, PJP1.LABOR_BRDN_COST, PJP1.LABOR_HRS, PJP1.LABOR_
REVENUE, PJP1.EQUIPMENT_HOURS, PJP1.BILLABLE_EQUIPMENT_HOURS, PJP1.SUP_INV_COMMITTED_COST, PJP1.PO_COMMITTED_COST, PJP1.PR_COMMITTED_COST, PJP1.OTH_COMMITTED_COST, PJP1.ACT_LABOR_HRS, PJP1.ACT_EQUIP_HRS, PJP1.ACT_LABOR_BRDN_COST, PJP1.ACT_EQUIP_BRDN_COST, PJP1.ACT_BRDN_COST, PJP1.ACT_RAW_COST, PJP1.ACT_REVENUE, PJP1.ACT_LABOR_RAW_COST, PJP1.ACT_EQUIP_RAW_COST, PJP1.ETC_LABOR_HRS, PJP1.ETC_EQUIP_HRS, PJP1.ETC_LABOR_BRDN_COST, PJP1.ETC_EQUIP_BRDN_COST, PJP1.ETC_BRDN_COST, PJP1.ETC_RAW_COST, PJP1.ETC_LABOR_RAW_COST, PJP1.ETC_EQUIP_RAW_COST, PJP1.CUSTOM1, PJP1.CUSTOM2, PJP1.CUSTOM3, PJP1.CUSTOM4, PJP1.CUSTOM5, PJP1.CUSTOM6, PJP1.CUSTOM7, PJP1.CUSTOM8, PJP1.CUSTOM9, PJP1.CUSTOM10, PJP1.CUSTOM11, PJP1.CUSTOM12, PJP1.CUSTOM13, PJP1.CUSTOM14, PJP1.CUSTOM15 FROM PJI_FP_AGGR_PJP1_T PJP1, PJI_PJP_WBS_HEADER WBS_HDR, PA_XBS_DENORM WBS, PJI_XBS_DENORM PRG, ( SELECT 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'N
' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL ) FIN_PLAN, ( SELECT 1 WBS_SUP_LEVEL, 1 WBS_SUB_LEVEL, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 1 WBS_SUP_LEVEL, 1 WBS_SUB_LEVEL, 'WBS' INVERT_ID FROM DUAL ) TOP_SLICE WHERE PRG.STRUCT_TYPE = 'PRG' AND PRG.SUP_LEVEL = :B1 AND PRG.SUB_LEVEL = :B1 AND WBS.STRUCT_TYPE = 'WBS' AND ((WBS.SUP_LEVEL = 1 AND WBS.SUB_LEVEL = 1) OR (WBS.SUP_LEVEL <> WBS.SUB_LEVEL)) AND WBS.STRUCT_VERSION_ID = PRG.SUP_ID AND WBS.SUP_PROJECT_ID = PRG.SUP_PROJECT_ID
AND PJP1.PRG_LEVEL IN (0, :B1 ) AND PJP1.RBS_AGGR_LEVEL IN ('T', 'L') AND PJP1.WBS_ROLLUP_FLAG = 'N' AND PJP1.PRG_ROLLUP_FLAG IN ('Y', 'N') AND PJP1.PROJECT_ID = WBS_HDR.PROJECT_ID AND PJP1.PLAN_VERSION_ID = WBS_HDR.PLAN_VERSION_ID AND PJP1.PLAN_TYPE_CODE = WBS_HDR.PLAN_TYPE_CODE AND DECODE(PJP1.PLAN_VERSION_ID, -3, PJP1.PLAN_TYPE_ID, -4, PJP1.PLAN_TYPE_ID, -1) = DECODE(PJP1.PLAN_VERSION_ID, -3, WBS_HDR.PLAN_TYPE_ID, -4, WBS_HDR.PLAN_TYPE_ID, -1) AND WBS.STRUCT_VERSION_ID = WBS_HDR.WBS_VERSION_ID AND PJP1.PROJECT_ELEMENT_ID = WBS.SUB_EMT_ID AND WBS_HDR.CB_FLAG = FIN_PLAN.CB (+) AND WBS_HDR.CO_FLAG = FIN_PLAN.CO (+) AND WBS.SUP_LEVEL = TOP_SLICE.WBS_SUP_LEVEL (+) AND WBS.SUB_LEVEL <> TOP_SLICE.WBS_SUB_LEVEL (+) UNION ALL SELECT /*+ ordered */ TO_CHAR(NULL) LINE_TYPE, WBS_HDR.WBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE, 'Y' PUSHUP_FLAG, DECODE(PJP1.RBS_AGGR_LEVEL, 'L', 'N', DECODE(FIN_PLAN.PLAN_VERSION_ID, NULL, 'N', 'Y')) IN
SERT_FLAG, PJP1.PROJECT_ID, PJP1.PROJECT_ORG_ID, PJP1.PROJECT_ORGANIZATION_ID, PJP1.PROJECT_ELEMENT_ID, PJP1.TIME_ID, PJP1.PERIOD_TYPE_ID, PJP1.CALENDAR_TYPE, PJP1.RBS_AGGR_LEVEL, 'Y' WBS_ROLLUP_FLAG, PJP1.PRG_ROLLUP_FLAG, PJP1.CURR_RECORD_TYPE_ID, PJP1.CURRENCY_CODE, PJP1.RBS_ELEMENT_ID, PJP1.RBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG, 'N', DECODE(PJP1.PLAN_VERSION_ID, -1, PJP1.PLAN_VERSION_ID, -2, PJP1.PLAN_VERSION_ID, -3, PJP1.PLAN_VERSION_ID, -4, PJP1.PLAN_VERSION_ID, FIN_PLAN.PLAN_VERSION_ID), PJP1.PLAN_VERSION_ID) PLAN_VERSION_ID, PJP1.PLAN_TYPE_ID, PJP1.PLAN_TYPE_CODE, PJP1.RAW_COST, PJP1.BRDN_COST, PJP1.REVENUE, PJP1.BILL_RAW_COST, PJP1.BILL_BRDN_COST, PJP1.BILL_LABOR_RAW_COST, PJP1.BILL_LABOR_BRDN_COST, PJP1.BILL_LABOR_HRS, PJP1.EQUIPMENT_RAW_COST, PJP1.EQUIPMENT_BRDN_COST, PJP1.CAPITALIZABLE_RAW_COST, PJP1.CAPITALIZABLE_BRDN_COST, PJP1.LABOR_RAW_COST, PJP1.LABOR_BRDN_COST, PJP1.LABOR_HRS, PJP1.LABOR_REVENUE, PJP1.EQUIPMENT_HOURS, PJP
1.BILLABLE_EQUIPMENT_HOURS, PJP1.SUP_INV_COMMITTED_COST, PJP1.PO_COMMITTED_COST, PJP1.PR_COMMITTED_COST, PJP1.OTH_COMMITTED_COST, PJP1.ACT_LABOR_HRS, PJP1.ACT_EQUIP_HRS, PJP1.ACT_LABOR_BRDN_COST, PJP1.ACT_EQUIP_BRDN_COST, PJP1.ACT_BRDN_COST, PJP1.ACT_RAW_COST, PJP1.ACT_REVENUE, PJP1.ACT_LABOR_RAW_COST, PJP1.ACT_EQUIP_RAW_COST, PJP1.ETC_LABOR_HRS, PJP1.ETC_EQUIP_HRS, PJP1.ETC_LABOR_BRDN_COST, PJP1.ETC_EQUIP_BRDN_COST, PJP1.ETC_BRDN_COST, PJP1.ETC_RAW_COST, PJP1.ETC_LABOR_RAW_COST, PJP1.ETC_EQUIP_RAW_COST, PJP1.CUSTOM1, PJP1.CUSTOM2, PJP1.CUSTOM3, PJP1.CUSTOM4, PJP1.CUSTOM5, PJP1.CUSTOM6, PJP1.CUSTOM7, PJP1.CUSTOM8, PJP1.CUSTOM9, PJP1.CUSTOM10, PJP1.CUSTOM11, PJP1.CUSTOM12, PJP1.CUSTOM13, PJP1.CUSTOM14, PJP1.CUSTOM15 FROM PJI_FP_AGGR_PJP1_T PJP1, PJI_PJP_WBS_HEADER WBS_HDR, PJI_XBS_DENORM PRG, ( SELECT 'Y' CB_FLAG, 'N' CO_FLAG, -3 PLAN_VERSION_ID FROM DUAL UNION ALL SELECT 'N' CB_FLAG, 'Y' CO_FLAG, -4 PLAN_VERSION_ID FROM DUAL UNION ALL SELECT '
Y' CB_FLAG, 'Y' CO_FLAG, -3 PLAN_VERSION_ID FROM DUAL UNION ALL SELECT 'Y' CB_FLAG, 'Y' CO_FLAG, -4 PLAN_VERSION_ID FROM DUAL ) FIN_PLAN WHERE PRG.STRUCT_TYPE = 'PRG' AND PRG.SUP_LEVEL = :B1 AND PRG.SUB_LEVEL = :B1 AND PJP1.PROJECT_ID = PRG.SUP_PROJECT_ID AND PJP1.PROJECT_ELEMENT_ID = PRG.SUP_EMT_ID AND PJP1.PRG_LEVEL = 0 AND PJP1.RBS_AGGR_LEVEL IN ('T', 'L') AND PJP1.WBS_ROLLUP_FLAG = 'N' AND PJP1.PRG_ROLLUP_FLAG = 'N' AND WBS_HDR.PROJECT_ID = PJP1.PROJECT_ID AND WBS_HDR.PLAN_VERSION_ID = PJP1.PLAN_VERSION_ID AND WBS_HDR.PLAN_TYPE_CODE = PJP1.PLAN_TYPE_CODE AND DECODE(WBS_HDR.WP_FLAG, 'N', DECODE(PJP1.PLAN_VERSION_ID, -1, 'Y', -2, 'Y', -3, 'Y', -4, 'Y', DECODE(WBS_HDR.CB_FLAG || '_' || WBS_HDR.CO_FLAG, 'Y_Y', 'Y', 'N_Y', 'Y', 'Y_N', 'Y', 'N')), 'Y') = 'Y' AND WBS_HDR.WBS_VERSION_ID = PRG.SUP_ID AND WBS_HDR.CB_FLAG = FIN_PLAN.CB_FLAG (+) AND WBS_HDR.CO_FLAG = FIN_PLAN.CO_FLAG (+) UNION ALL SELECT /*+ ordered index(log PA_PJI_PROJ_EVENTS_LOG_N2) index(fpr PJI_FP_
XBS_ACCUM_F_N1) */ TO_CHAR(NULL) LINE_TYPE, WBS_HDR.WBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG, 'Y', 'LW', 'LF') RELATIONSHIP_TYPE, DECODE(LOG.EVENT_TYPE, 'WBS_CHANGE', 'Y', 'WBS_PUBLISH', 'N') PUSHUP_FLAG, DECODE(WBS_HDR.WP_FLAG || '_' || FIN_PLAN.INVERT_ID, 'N_PRG', DECODE(TOP_SLICE.INVERT_ID, 'PRJ', 'Y', DECODE(WBS.SUB_LEVEL, 1, 'Y', 'N')), DECODE(WBS_HDR.WP_FLAG || '_' || FIN_PLAN.INVERT_ID || '_' || FIN_PLAN.CB || '_' || FIN_PLAN.CO || '_' || TO_CHAR(FIN_PLAN.PLAN_VERSION_ID), 'N_PRJ_Y_Y_-4', 'N', 'Y')) INSERT_FLAG, FPR.PROJECT_ID, FPR.PROJECT_ORG_ID, FPR.PROJECT_ORGANIZATION_ID, DECODE(TOP_SLICE.INVERT_ID, 'PRJ', PRG.SUP_EMT_ID, DECODE(WBS.SUB_LEVEL, 1, PRG.SUP_EMT_ID, WBS.SUP_EMT_ID)) PROJECT_ELEMENT_ID, FPR.TIME_ID, FPR.PERIOD_TYPE_ID, FPR.CALENDAR_TYPE, FPR.RBS_AGGR_LEVEL, 'Y' WBS_ROLLUP_FLAG, FPR.PRG_ROLLUP_FLAG, FPR.CURR_RECORD_TYPE_ID, FPR.CURRENCY_CODE, FPR.RBS_ELEMENT_ID, FPR.RBS_VERSION_ID, DECODE(WBS_HDR.WP_FLAG || '_' || FIN_PLAN
.INVERT_ID, 'N_PRG', FIN_PLAN.PLAN_VERSION_ID, FPR.PLAN_VERSION_ID) PLAN_VERSION_ID, FPR.PLAN_TYPE_ID, FPR.PLAN_TYPE_CODE, WBS.SIGN * FPR.RAW_COST RAW_COST, WBS.SIGN * FPR.BRDN_COST BRDN_COST, WBS.SIGN * FPR.REVENUE REVENUE, WBS.SIGN * FPR.BILL_RAW_COST BILL_RAW_COST, WBS.SIGN * FPR.BILL_BRDN_COST BILL_BRDN_COST, WBS.SIGN * FPR.BILL_LABOR_RAW_COST BILL_LABOR_RAW_COST, WBS.SIGN * FPR.BILL_LABOR_BRDN_COST BILL_LABOR_BRDN_COST, WBS.SIGN * FPR.BILL_LABOR_HRS BILL_LABOR_HRS, WBS.SIGN * FPR.EQUIPMENT_RAW_COST EQUIPMENT_RAW_COST, WBS.SIGN * FPR.EQUIPMENT_BRDN_COST EQUIPMENT_BRDN_COST, WBS.SIGN * FPR.CAPITALIZABLE_RAW_COST CAPITALIZABLE_RAW_COST, WBS.SIGN * FPR.CAPITALIZABLE_BRDN_COST CAPITALIZABLE_BRDN_COST, WBS.SIGN * FPR.LABOR_RAW_COST LABOR_RAW_COST, WBS.SIGN * FPR.LABOR_BRDN_COST LABOR_BRDN_COST, WBS.SIGN * FPR.LABOR_HRS LABOR_HRS, WBS.SIGN * FPR.LABOR_REVENUE LABOR_REVENUE, WBS.SIGN * FPR.EQUIPMENT_HOURS EQUIPMENT_HOURS, WBS.SIGN * FPR.BILLABLE_EQUIPMENT_HOURS BILL
ABLE_EQUIPMENT_HOURS, WBS.SIGN * FPR.SUP_INV_COMMITTED_COST SUP_INV_COMMITTED_COST, WBS.SIGN * FPR.PO_COMMITTED_COST PO_COMMITTED_COST, WBS.SIGN * FPR.PR_COMMITTED_COST PR_COMMITTED_COST, WBS.SIGN * FPR.OTH_COMMITTED_COST OTH_COMMITTED_COST, WBS.SIGN * FPR.ACT_LABOR_HRS ACT_LABOR_HRS, WBS.SIGN * FPR.ACT_EQUIP_HRS ACT_EQUIP_HRS, WBS.SIGN * FPR.ACT_LABOR_BRDN_COST ACT_LABOR_BRDN_COST, WBS.SIGN * FPR.ACT_EQUIP_BRDN_COST ACT_EQUIP_BRDN_COST, WBS.SIGN * FPR.ACT_BRDN_COST ACT_BRDN_COST, WBS.SIGN * FPR.ACT_RAW_COST ACT_RAW_COST, WBS.SIGN * FPR.ACT_REVENUE ACT_REVENUE, WBS.SIGN * FPR.ACT_LABOR_RAW_COST ACT_LABOR_RAW_COST, WBS.SIGN * FPR.ACT_EQUIP_RAW_COST ACT_EQUIP_RAW_COST, WBS.SIGN * FPR.ETC_LABOR_HRS ETC_LABOR_HRS, WBS.SIGN * FPR.ETC_EQUIP_HRS ETC_EQUIP_HRS, WBS.SIGN * FPR.ETC_LABOR_BRDN_COST ETC_LABOR_BRDN_COST, WBS.SIGN * FPR.ETC_EQUIP_BRDN_COST ETC_EQUIP_BRDN_COST, WBS.SIGN * FPR.ETC_BRDN_COST ETC_BRDN_COST, WBS.SIGN * FPR.ETC_RAW_COST ETC_RAW_COST, WBS.SIGN * FPR.ETC
_LABOR_RAW_COST ETC_LABOR_RAW_COST, WBS.SIGN * FPR.ETC_EQUIP_RAW_COST ETC_EQUIP_RAW_COST, WBS.SIGN * FPR.CUSTOM1 CUSTOM1, WBS.SIGN * FPR.CUSTOM2 CUSTOM2, WBS.SIGN * FPR.CUSTOM3 CUSTOM3, WBS.SIGN * FPR.CUSTOM4 CUSTOM4, WBS.SIGN * FPR.CUSTOM5 CUSTOM5, WBS.SIGN * FPR.CUSTOM6 CUSTOM6, WBS.SIGN * FPR.CUSTOM7 CUSTOM7, WBS.SIGN * FPR.CUSTOM8 CUSTOM8, WBS.SIGN * FPR.CUSTOM9 CUSTOM9, WBS.SIGN * FPR.CUSTOM10 CUSTOM10, WBS.SIGN * FPR.CUSTOM11 CUSTOM11, WBS.SIGN * FPR.CUSTOM12 CUSTOM12, WBS.SIGN * FPR.CUSTOM13 CUSTOM13, WBS.SIGN * FPR.CUSTOM14 CUSTOM14, WBS.SIGN * FPR.CUSTOM15 CUSTOM15 FROM PA_PJI_PROJ_EVENTS_LOG LOG, PJI_PJP_WBS_HEADER WBS_HDR, PJI_XBS_DENORM_DELTA_T WBS, PJI_XBS_DENORM PRG, PJI_FP_XBS_ACCUM_F FPR, ( SELECT 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'N' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'N' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'N' CB, '
Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -3 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRG' INVERT_ID FROM DUAL UNION ALL SELECT 'Y' CB, 'Y' CO, -4 PLAN_VERSION_ID, 'PRJ' INVERT_ID FROM DUAL ) FIN_PLAN, ( SELECT 1 WBS_SUP_LEVEL, 1 WBS_SUB_LEVEL, 'PRJ' INVERT_ID FROM DUAL UNION ALL SELECT 1 WBS_SUP_LEVEL, 1 WBS_SUB_LEVEL, 'WBS' INVERT_ID FROM DUAL ) TOP_SLICE WHERE PRG.STRUCT_TYPE = 'PRG' AND PRG.SUP_LEVEL = :B1 AND PRG.SUB_LEVEL = :B1 AND WBS.STRUCT_TYPE = 'WBS' AND WBS.SUP_PROJECT_ID = PRG.SUP_PROJECT_ID AND LOG.EVENT_ID = :B2 AND LOG.EVENT_TYPE IN ('WBS_CHANGE', 'WBS_PUBLISH') AND WBS_HDR.PROJECT_ID = LOG.ATTRIBUTE1 AND WBS_HDR.PLAN_VERSION_ID = LOG.ATTRIBUTE3 AND WBS_HDR.WBS_VERSION_ID = WBS.STRUCT_VERSION_ID AND WBS_HDR.PROJECT_ID = PRG.SUP_PROJECT_ID AND WBS_HDR.WBS_VERSION_ID = PRG.SUP
_ID AND FPR.RBS_AGGR_LEVEL = 'T' AND FPR.WBS_ROLLUP_FLAG = 'N' AND FPR.PRG_ROLLUP_FLAG IN ('Y', 'N') AND FPR.PROJECT_ID = WBS.SUP_PROJECT_ID AND FPR.PROJECT_ELEMENT_ID = WBS.SUB_EMT_ID AND FPR.PROJECT_ID = WBS_HDR.PROJECT_ID AND FPR.PLAN_VERSION_ID = WBS_HDR.PLAN_VERSION_ID AND FPR.PLAN_TYPE_CODE = WBS_HDR.PLAN_TYPE_CODE AND DECODE(FPR.PLAN_VERSION_ID, -3, FPR.PLAN_TYPE_ID, -4, FPR.PLAN_TYPE_ID, -1) = DECODE(FPR.PLAN_VERSION_ID, -3, WBS_HDR.PLAN_TYPE_ID, -4, WBS_HDR.PLAN_TYPE_ID, -1) AND WBS_HDR.CB_FLAG = FIN_PLAN.CB (+) AND WBS_HDR.CO_FLAG = FIN_PLAN.CO (+) AND WBS.SUP_LEVEL = TOP_SLICE.WBS_SUP_LEVEL (+) AND WBS.SUB_LEVEL <> TOP_SLICE.WBS_SUB_LEVEL (+) AND (WBS.SUP_LEVEL <> WBS.SUB_LEVEL OR (WBS.SUP_LEVEL = 1 AND WBS.SUB_LEVEL = 1)) UNION ALL SELECT /*+ ordered index(log PA_PJI_PROJ_EVENTS_LOG_N2) index(fpr PJI_FP_XBS_ACCUM_F_N1) */ TO_CHAR(NULL) LINE_TYPE, WBS_HDR.WBS_VERSION_ID, 'LF' RELATIONSHIP_TYPE, 'Y' PUSHUP_FLAG, DECODE(FPR.RBS_AGGR_LEVEL, 'L', 'N'
, 'Y') INSERT_FLAG, FPR.PROJECT_ID, FPR.PROJECT_ORG_ID, FPR.PROJECT_ORGANIZATION_ID, PRG.SUP_EMT_ID PROJECT_ELEMENT_ID, FPR.TIME_ID, FPR.PERIOD_TYPE_ID, FPR.CALENDAR_TYPE, FPR.RBS_AGGR_LEVEL, 'Y' WBS_ROLLUP_FLAG, FPR.PRG_ROLLUP_FLAG, FPR.CURR_RECORD_TYPE_ID, FPR.CURRENCY_CODE, FPR.RBS_ELEMENT_ID, FPR.RBS_VERSION_ID, DECODE(LOG.EVENT_TYPE, 'PLAN_BASELINE', -3, 'PLAN_ORIGINAL', -4) PLAN_VERSION_ID, FPR.PLAN_TYPE_ID, FPR.PLAN_TYPE_CODE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.RAW_COST RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BRDN_COST BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.REVENUE REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_RAW_COST BILL_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LO
G.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_BRDN_COST BILL_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_RAW_COST BILL_LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_BRDN_COST BILL_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_HRS BILL_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_RAW_COST EQUIPMENT_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_BRDN_COST EQUIPMENT_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CAPITALIZABLE_RAW_COST CAPITALIZABLE_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -
1, -4, -1) * FPR.CAPITALIZABLE_BRDN_COST CAPITALIZABLE_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_RAW_COST LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_BRDN_COST LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_HRS LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_REVENUE LABOR_REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_HOURS EQUIPMENT_HOURS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILLABLE_EQUIPMENT_HOURS BILLABLE_EQUIPMENT_HOURS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.SUP_INV_COMMITTED_COST SUP_INV_COM
MITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.PO_COMMITTED_COST PO_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.PR_COMMITTED_COST PR_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.OTH_COMMITTED_COST OTH_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_HRS ACT_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_HRS ACT_EQUIP_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_BRDN_COST ACT_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_BRDN_COST ACT_EQUIP_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.
ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_BRDN_COST ACT_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_RAW_COST ACT_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_REVENUE ACT_REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_RAW_COST ACT_LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_RAW_COST ACT_EQUIP_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_HRS ETC_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_HRS ETC_EQUIP_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_BRDN_CO
ST ETC_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_BRDN_COST ETC_EQUIP_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_BRDN_COST ETC_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_RAW_COST ETC_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_RAW_COST ETC_LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_RAW_COST ETC_EQUIP_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM1 CUSTOM1, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM2 CUSTOM2, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJEC
T, 1, -3, -1, -4, -1) * FPR.CUSTOM3 CUSTOM3, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM4 CUSTOM4, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM5 CUSTOM5, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM6 CUSTOM6, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM7 CUSTOM7, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM8 CUSTOM8, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM9 CUSTOM9, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM10 CUSTOM10, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM11 CUSTOM11, DECODE(FP
R.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM12 CUSTOM12, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM13 CUSTOM13, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM14 CUSTOM14, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM15 CUSTOM15 FROM PA_PJI_PROJ_EVENTS_LOG LOG, PJI_PJP_WBS_HEADER WBS_HDR, PJI_FP_XBS_ACCUM_F FPR, PJI_XBS_DENORM PRG WHERE PRG.STRUCT_TYPE = 'PRG' AND PRG.SUP_LEVEL = :B1 AND PRG.SUB_LEVEL = :B1 AND LOG.EVENT_ID = :B2 AND LOG.EVENT_TYPE IN ('PLAN_BASELINE', 'PLAN_ORIGINAL') AND FPR.PROJECT_ID = LOG.ATTRIBUTE1 AND FPR.PROJECT_ID = PRG.SUP_PROJECT_ID AND FPR.PLAN_TYPE_ID = LOG.ATTRIBUTE2 AND FPR.PLAN_VERSION_ID IN (DECODE(LOG.EVENT_TYPE, 'PLAN_BASELINE', -3, 'PLAN_ORIGINAL', -4), LOG.EVENT_OBJECT) AND (FPR.RBS_AGGR_LEVEL, FPR.WBS_ROLL
UP_FLAG, FPR.PRG_ROLLUP_FLAG) IN (('T', 'Y', 'N'), ('T', 'N', 'N'), ('L', 'N', 'N')) AND FPR.PROJECT_ELEMENT_ID = PRG.SUB_EMT_ID AND WBS_HDR.PROJECT_ID = FPR.PROJECT_ID AND WBS_HDR.PROJECT_ID = LOG.ATTRIBUTE1 AND WBS_HDR.PLAN_VERSION_ID IN (LOG.ATTRIBUTE3, LOG.EVENT_OBJECT) AND WBS_HDR.PLAN_VERSION_ID = DECODE(SIGN(FPR.PLAN_VERSION_ID), -1, LOG.ATTRIBUTE3, LOG.EVENT_OBJECT) AND WBS_HDR.PLAN_TYPE_ID = LOG.ATTRIBUTE2 AND WBS_HDR.PLAN_TYPE_CODE = FPR.PLAN_TYPE_CODE AND WBS_HDR.WBS_VERSION_ID = PRG.SUP_ID AND PRG.STRUCT_VERSION_ID IS NULL UNION ALL SELECT /*+ ordered index(log PA_PJI_PROJ_EVENTS_LOG_N2) index(fpr PJI_FP_XBS_ACCUM_F_N1) */ TO_CHAR(NULL) LINE_TYPE, WBS_HDR.WBS_VERSION_ID, 'LF' RELATIONSHIP_TYPE, 'Y' PUSHUP_FLAG, DECODE(FPR.RBS_AGGR_LEVEL, 'L', 'N', 'Y') INSERT_FLAG, FPR.PROJECT_ID, FPR.PROJECT_ORG_ID, FPR.PROJECT_ORGANIZATION_ID, PRG.SUP_EMT_ID PROJECT_ELEMENT_ID, FPR.TIME_ID, FPR.PERIOD_TYPE_ID, FPR.CALENDAR_TYPE, FPR.RBS_AGGR_LEVEL, 'Y' WBS_ROL
LUP_FLAG, FPR.PRG_ROLLUP_FLAG, FPR.CURR_RECORD_TYPE_ID, FPR.CURRENCY_CODE, FPR.RBS_ELEMENT_ID, FPR.RBS_VERSION_ID, DECODE(LOG.EVENT_TYPE, 'PLAN_BASELINE', -3, 'PLAN_ORIGINAL', -4) PLAN_VERSION_ID, FPR.PLAN_TYPE_ID, FPR.PLAN_TYPE_CODE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.RAW_COST RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BRDN_COST BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.REVENUE REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_RAW_COST BILL_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_BRDN_COST BILL_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_RAW_COST BILL_LABOR_RAW_C
OST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_BRDN_COST BILL_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILL_LABOR_HRS BILL_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_RAW_COST EQUIPMENT_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_BRDN_COST EQUIPMENT_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CAPITALIZABLE_RAW_COST CAPITALIZABLE_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CAPITALIZABLE_BRDN_COST CAPITALIZABLE_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_RAW_COST LABOR_RAW_COST, DECODE(FPR.PL
AN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_BRDN_COST LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_HRS LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.LABOR_REVENUE LABOR_REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.EQUIPMENT_HOURS EQUIPMENT_HOURS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.BILLABLE_EQUIPMENT_HOURS BILLABLE_EQUIPMENT_HOURS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.SUP_INV_COMMITTED_COST SUP_INV_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.PO_COMMITTED_COST PO_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_O
BJECT, 1, -3, -1, -4, -1) * FPR.PR_COMMITTED_COST PR_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.OTH_COMMITTED_COST OTH_COMMITTED_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_HRS ACT_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_HRS ACT_EQUIP_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_BRDN_COST ACT_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_BRDN_COST ACT_EQUIP_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_BRDN_COST ACT_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_RAW_COST ACT_R
AW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_REVENUE ACT_REVENUE, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_LABOR_RAW_COST ACT_LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ACT_EQUIP_RAW_COST ACT_EQUIP_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_HRS ETC_LABOR_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_HRS ETC_EQUIP_HRS, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_BRDN_COST ETC_LABOR_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_BRDN_COST ETC_EQUIP_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -
1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_BRDN_COST ETC_BRDN_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_RAW_COST ETC_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_LABOR_RAW_COST ETC_LABOR_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.ETC_EQUIP_RAW_COST ETC_EQUIP_RAW_COST, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM1 CUSTOM1, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM2 CUSTOM2, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM3 CUSTOM3, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM4 CUSTOM4, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3,
-1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM5 CUSTOM5, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM6 CUSTOM6, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM7 CUSTOM7, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM8 CUSTOM8, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM9 CUSTOM9, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM10 CUSTOM10, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM11 CUSTOM11, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM12 CUSTOM12, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUST
OM13 CUSTOM13, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM14 CUSTOM14, DECODE(FPR.PLAN_VERSION_ID, LOG.ATTRIBUTE3, -1, LOG.EVENT_OBJECT, 1, -3, -1, -4, -1) * FPR.CUSTOM15 CUSTOM15 FROM PA_PJI_PROJ_EVENTS_LOG LOG, PJI_PJP_WBS_HEADER WBS_HDR, PJI_FP_XBS_ACCUM_F FPR, PJI_XBS_DENORM PRG WHERE PRG.STRUCT_TYPE = 'PRG' AND PRG.SUP_LEVEL = :B1 AND PRG.SUB_LEVEL = :B1 AND LOG.EVENT_ID = :B2 AND LOG.EVENT_TYPE IN ('PLAN_BASELINE', 'PLAN_ORIGINAL') AND FPR.PROJECT_ID = LOG.ATTRIBUTE1 AND FPR.PROJECT_ID = PRG.SUP_PROJECT_ID AND FPR.PLAN_TYPE_ID = LOG.ATTRIBUTE2 AND FPR.PLAN_VERSION_ID IN (LOG.ATTRIBUTE3, LOG.EVENT_OBJECT) AND (FPR.RBS_AGGR_LEVEL, FPR.WBS_ROLLUP_FLAG, FPR.PRG_ROLLUP_FLAG) IN (('L', 'N', 'N')) AND FPR.PROJECT_ELEMENT_ID <> PRG.SUB_EMT_ID AND WBS_HDR.PROJECT_ID = FPR.PROJECT_ID AND WBS_HDR.PROJECT_ID = LOG.ATTRIBUTE1 AND WBS_HDR.PLAN_VERSION_ID IN (LOG.ATTRIBUTE3, LOG.EVENT_OBJECT) AND WBS_HDR.PLA
N_VERSION_ID = DECODE(SIGN(FPR.PLAN_VERSION_ID), -1, LOG.ATTRIBUTE3, LOG.EVENT_OBJECT) AND WBS_HDR.PLAN_TYPE_ID = LOG.ATTRIBUTE2 AND WBS_HDR.PLAN_TYPE_CODE = FPR.PLAN_TYPE_CODE AND WBS_HDR.WBS_VERSION_ID = PRG.SUP_ID AND PRG.STRUCT_VERSION_ID IS NULL ) PJP, ( SELECT /*+ ordered index(prg PJI_XBS_DENORM_N3) index(prj PA_PROJECTS_U1) */ PRG.SUP_PROJECT_ID, PRJ.ORG_ID SUP_PROJECT_ORG_ID, PRJ.CARRYING_OUT_ORGANIZATION_ID SUP_PROJECT_ORGANIZATION_ID, PRG.SUP_ID, PRG.SUP_EMT_ID, PRG.SUP_LEVEL, PRG.SUB_ID, PRG.SUB_EMT_ID, PRG.SUB_ROLLUP_ID, INVERT.INVERT_VALUE RELATIONSHIP_TYPE, DECODE(PRG.RELATIONSHIP_TYPE, 'LW', 'Y', 'LF', 'N') WP_FLAG, 'Y' PUSHUP_FLAG FROM PJI_XBS_DENORM PRG, PA_PROJECTS_ALL PRJ, ( SELECT 'LF' INVERT_ID, 'LF' INVERT_VALUE FROM DUAL UNION ALL SELECT 'LW' INVERT_ID, 'LW' INVERT_VALUE FROM DUAL UNION ALL SELECT 'A' INVERT_ID, 'LF' INVERT_VALUE FROM DUAL UNION ALL SELECT 'A' INVERT_ID, 'LW' INVERT_VALUE FROM DUAL ) INVERT WHERE :B1 > 1 AND PRG.STRUC
T_TYPE = 'PRG' AND PRG.SUB_ROLLUP_ID IS NOT NULL AND PRG.SUB_LEVEL = :B1 AND PRJ.PROJECT_ID = PRG.SUP_PROJECT_ID AND DECODE(PRG.SUB_LEVEL, PRG.SUP_LEVEL, 'A', PRG.RELATIONSHIP_TYPE) = INVERT.INVERT_ID ) PRG, PJI_PJP_WBS_HEADER WBS_HDR, PA_PROJ_ELEM_VER_STRUCTURE SUB_VER, PA_PROJ_ELEM_VER_STRUCTURE SUP_VER, PA_PROJ_WORKPLAN_ATTR SUP_WPA WHERE PJP.PROJECT_ID = SUB_VER.PROJECT_ID (+) AND PJP.WBS_VERSION_ID = SUB_VER.ELEMENT_VERSION_ID (+) AND 'STRUCTURE_PUBLISHED' = SUB_VER.STATUS_CODE (+) AND PJP.WBS_VERSION_ID = PRG.SUB_ID (+) AND PJP.RELATIONSHIP_TYPE = PRG.RELATIONSHIP_TYPE (+) AND PJP.PUSHUP_FLAG = PRG.PUSHUP_FLAG (+) AND PRG.SUP_PROJECT_ID = WBS_HDR.PROJECT_ID (+) AND PRG.SUP_ID = WBS_HDR.WBS_VERSION_ID (+) AND PRG.WP_FLAG = WBS_HDR.WP_FLAG (+) AND 'Y' = WBS_HDR.WP_FLAG (+) AND WBS_HDR.PROJECT_ID = SUP_VER.PROJECT_ID (+) AND WBS_HDR.WBS_VERSION_ID = SUP_VER.ELEMENT_VERSION_ID (+) AND 'STRUCTURE_PUBLISHED' = SUP_VER.STATUS_CODE (+) AND 'Y' = SUP_VER.LATEST_EFF_PUBLISHED_FLAG (
+) AND PRG.SUP_EMT_ID = SUP_WPA.PROJ_ELEMENT_ID (+) GROUP BY DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.INSERT_FLAG, 'Y'), PJP.RELATIONSHIP_TYPE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUB_VER.STATUS_CODE), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUP_VER.STATUS_CODE), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, SUP_WPA.WP_ENABLE_VERSION_FLAG), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECODE(PJP.PLAN_VERSION_ID, -1, PRG.SUP_ID, -3, PRG.SUP_ID, -4, PRG.SUP_ID, NULL)), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECODE(PJP.PLAN_VERSION_ID, -1, PRG.SUP_EMT_ID, -3, PRG.SUP_EMT_ID, -4, PRG.SUP_EMT_ID, NULL)), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), NULL, DECODE(PJP.PLAN_VERSION_ID, -1, PRG.WP_FLAG, -3, PRG.WP_FLAG, -4, PRG.WP_FLAG, NULL)), DECODE(NVL(PRG.SUB_EMT_ID, -
1), NVL(PRG.SUB_ROLLUP_ID, -1), :B1 , PRG.SUP_LEVEL), PJP.LINE_TYPE, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ID, PRG.SUP_PROJECT_ID), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ORG_ID, PRG.SUP_PROJECT_ORG_ID), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ORGANIZATION_ID, PRG.SUP_PROJECT_ORGANIZATION_ID), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PROJECT_ELEMENT_ID, PRG.SUB_ROLLUP_ID), PJP.TIME_ID, PJP.PERIOD_TYPE_ID, PJP.CALENDAR_TYPE, PJP.RBS_AGGR_LEVEL, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.WBS_ROLLUP_FLAG, 'N'), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PRG_ROLLUP_FLAG, 'Y'), PJP.CURR_RECORD_TYPE_ID, PJP.CURRENCY_CODE, PJP.RBS_ELEMENT_ID, PJP.RBS_VERSION_ID, DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PLAN_VERSION_ID, DECODE(PJP.PLAN_VERSION_ID, -1, PJ
P.PLAN_VERSION_ID, -2, PJP.PLAN_VERSION_ID, -3, PJP.PLAN_VERSION_ID, -4, PJP.PLAN_VERSION_ID, WBS_HDR.PLAN_VERSION_ID)), DECODE(NVL(PRG.SUB_EMT_ID, -1), NVL(PRG.SUB_ROLLUP_ID, -1), PJP.PLAN_TYPE_ID, DECODE(PJP.PLAN_VERSION_ID, -1, PJP.PLAN_TYPE_ID, -2, PJP.PLAN_TYPE_ID, -3, PJP.PLAN_TYPE_ID, -4, PJP.PLAN_TYPE_ID, WBS_HDR.PLAN_TYPE_ID)), PJP.PLAN_TYPE_CODE ) PJP1_I, PA_PROJ_ELEM_VER_STRUCTURE SUP_FIN_VER, PA_PROJ_WORKPLAN_ATTR SUP_WPA WHERE PJP1_I.INSERT_FLAG = 'Y' AND PJP1_I.PROJECT_ID = SUP_FIN_VER.PROJECT_ID (+) AND PJP1_I.SUP_ID = SUP_FIN_VER.ELEMENT_VERSION_ID (+) AND 'STRUCTURE_WORKING' = SUP_FIN_VER.STATUS_CODE (+) AND PJP1_I.SUP_EMT_ID = SUP_WPA.PROJ_ELEMENT_ID (+) AND 'N' = SUP_WPA.WP_ENABLE_VERSION_FLAG (+) AND (PJP1_I.SUP_ID IS NULL OR (PJP1_I.SUP_ID IS NOT NULL AND (SUP_FIN_VER.PROJECT_ID IS NOT NULL OR SUP_WPA.PROJ_ELEMENT_ID IS NOT NULL))) |
| a7mr7amcd8ysy | SELECT * FROM (select task.task_number tasknumber, task.task_name, task_details taskdetails, task.task_id taskid, task.billable_flag, task.project_id, task.start_date, task.completion_date, task.chargeable_flag, proj.project_number from pa_online_tasks_v task , pa_online_projects_v proj where proj.project_id = task.project_id) QRSLT WHERE (project_id = :1 AND ( UPPER(TASK_NAME) like UPPER(:2) AND (TASK_NAME like :3 OR TASK_NAME like :4 OR TASK_NAME like :5 OR TASK_NAME like :6))) ORDER BY tasknumber |
| andxjs0c8n7yd |
SELECT * FROM (SELECT NVL(paa.date_start, paa.date_projected_start) start_date , NVL(paa.date_end, paa.date_projected_end) end_date , paattl.name absence_type , paat.absence_attendance_type_id absence_attendance_type_id , fcl.meaning absence_category , paat.absence_category absence_category_code , paa.absence_hours , (SELECT meaning from hr_lookups where 'A' = lookup_code(+) and 'LEAVE_STATUS' = lookup_type(+)) approval_status , 'A' approval_status_code , decode(paa.date_start, null, (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='PLANNED' and language = userenv('LANG')), (SELECT meaning from fnd_lookup_values where lookup_type ='ABSENCE_STATUS' and lookup_code ='CONFIRMED' and language = userenv('LANG'))) absence_status , decode(paa.date_start, null, 'PLANNED', 'CONFIRMED') absence_status_code , (nvl((SELECT 'Y' from fnd_attached_documents where entity_name='PER_ABSENCE_ATTENDANCES' and pk1_value = to_char(paa.absence_attendance_id) and
rownum = 1), 'N')) supporting_documents , decode ( paa.date_start, null, decode(paa.date_projected_start, null, 'HrCancelDisabled', 'HrCancelEnabled'), decode( sign(trunc(paa.date_start)-trunc(sysdate)), 1, 'HrCancelEnabled', decode(paa.date_end, null, 'HrCancelEnabled', 'HrCancelDisabled') ) ) cancel_icon , decode(paa.date_end, NULL, decode(paa.date_start, Null, 'HrConfirmEnabled', 'HrConfirmDisabled'), 'HrConfirmDisabled' ) confirm_icon , decode ( paa.date_start, null, 'HrUpdateEnabled', decode(sign(nvl(paa.date_end, sysdate+1)-sysdate), 1, 'HrUpdateEnabled', 'HrUpdateDisabled')) update_icon , NULL details_icon , paa.absence_attendance_id , null transaction_id , to_char(paa.absence_attendance_id) supportingDocKey , paa.absence_days , decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'), 'G', p.global_name, p.local_name) full_name FROM per_absence_attendances paa , per_absence_attendance_types paat , per_abs_attendance_types_tl paattl , hr_lookups fcl , per_all_peop
le_f p WHERE paa.person_id = :1 and (nvl(start_date, nvl(date_projected_start, nvl(date_notification, sysdate))) between p.effective_start_date and p.effective_end_date) and paa.person_id = p.person_id and paa.business_group_id+0 = :2 and paa.absence_attendance_type_id = paat.absence_attendance_type_id and paat.absence_attendance_type_id = paattl.absence_attendance_type_id and paattl.language = userenv('LANG') and fcl.lookup_type(+) = 'ABSENCE_CATEGORY' and paat.absence_category = fcl.lookup_code(+) and ((hr_api.return_legislation_code(paat.business_group_id) = 'GB' and nvl(paat.absence_category, '#') not in ('M', 'GB_PAT_ADO', 'GB_PAT_BIRTH', 'GB_ADO')) or (hr_api.return_legislation_code(paat.business_group_id) <> 'GB' and nvl(paat.absence_category, '#') not in ('GB_PAT_ADO', 'GB_PAT_BIRTH', 'GB_ADO'))) and not exists ( select 'e' from hr_api_transactions t WHERE t.selected_person_id = paa.person_id and t.CREATOR_PERSON_ID = nvl(:3, t.CREATOR_PERSON_ID) and t.transaction_ref_ta
ble='PER_ABSENCE_ATTENDANCES' and t.transaction_ref_id = paa.absence_attendance_id and not(hr_absutil_ss.getabsencetype(t.transaction_id, NULL) IS NULL and t.status = 'W') and t.status not in ('D', 'E', 'AC') ) union all select hr_absutil_ss.getStartDate(hat.transaction_id, null) start_date , hr_absutil_ss.getEndDate(hat.transaction_id, null) end_date , hr_absutil_ss.getAbsenceType(hat.transaction_id, null) absence_type , to_number(hats.Information5) absence_attendance_type_id , hr_absutil_ss.getAbsenceCategory(hat.transaction_id, null) absence_category , hats.Information6 absence_category_code , hr_absutil_ss.getAbsenceHoursDuration(hat.transaction_id, null) absence_hours , hr_absutil_ss.getApprovalStatus(hat.transaction_id, null) approval_status , hr_absutil_ss.getApprovalStatusCode(hat.transaction_id, null) approval_status_code , hr_absutil_ss.getAbsenceStatus(hat.transaction_id, null) absence_status , hats.Information9 absence_status_code , hr_absutil_ss.hasSupportingDocuments(
hat.transaction_id, hat.TRANSACTION_REF_ID) supporting_documents , hr_absutil_ss.isCancelAllowed(hat.transaction_id, null, hat.status) cancel_icon , hr_absutil_ss.isConfirmAllowed(hat.transaction_id, null) confirm_icon , hr_absutil_ss.isUpdateAllowed(hat.transaction_id, null, hat.status) update_icon , null details_icon , hat.TRANSACTION_REF_ID absence_attendance_id , hat.transaction_id transaction_id , to_char(hat.TRANSACTION_REF_ID||'_'||hat.transaction_id) supportingDocKey , hr_absutil_ss.getAbsenceDaysDuration(hat.transaction_id, null) absence_days , decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'), 'G', p.global_name, p.local_name) full_name from hr_api_transactions hat , hr_api_transaction_steps hats , per_all_people_f p where hat.TRANSACTION_REF_TABLE='PER_ABSENCE_ATTENDANCES' and hat.TRANSACTION_GROUP='ABSENCE_MGMT' and hat.TRANSACTION_IDENTIFIER='ABSENCES' and hat.TRANSACTION_REF_ID is not null and hat.SELECTED_PERSON_ID =:4 and hat.CREATOR_PERSON_ID = nvl(:5, hat.CRE
ATOR_PERSON_ID) and (nvl(start_date, sysdate) between p.effective_start_date and p.effective_end_date) and hat.SELECTED_PERSON_ID = p.person_id and hat.transaction_id=hats.transaction_id(+) and hat.status not in ('D', 'E', 'AC') and not (hr_absutil_ss.getabsencetype(hat.transaction_id, null) is null and hat.status='W')) QRSLT ORDER BY START_DATE DESC |
| anxyyx2tjjz3g |
WITH TIMEGROUPED_RAWDATA AS (SELECT /*+ LEADING(SN DI MD SH OT) CARDINALITY(OT 1000) */ SH.METRIC_ID AS METRIC_ID , OT.BSLN_GUID AS BSLN_GUID , OT.TIMEGROUP AS TIMEGROUP , SH.VALUE AS OBS_VALUE FROM DBA_HIST_SNAPSHOT SN , DBA_HIST_DATABASE_INSTANCE DI , SYS.WRH$_SYSMETRIC_HISTORY SH , BSLN_METRIC_DEFAULTS MD , TABLE(:B1 ) OT WHERE SN.DBID = :B6 AND SN.SNAP_ID BETWEEN :B5 AND :B4 AND DI.DBID = SN.DBID AND DI.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND DI.STARTUP_TIME = SN.STARTUP_TIME AND DI.INSTANCE_NAME = :B3 AND SH.SNAP_ID = SN.SNAP_ID AND SH.DBID = SN.DBID AND SH.INSTANCE_NUMBER = SN.INSTANCE_NUMBER AND SH.GROUP_ID = 2 AND SH.METRIC_ID = MD.METRIC_ID AND MD.STATUS = :B2 AND OT.OBS_TIME = TRUNC(SH.END_TIME, 'HH24') ) (SELECT BSLN_STATISTICS_T (BSLN_GUID , METRIC_ID , :B11 , :B10 , TIMEGROUP , SAMPLE_COUNT , AVERAGE , MINIMUM , MAXIMUM , SDEV , PCTILE_25 , PCTILE_50 , PCTILE_75 , PCTILE_90 , PCTILE_95 , PCTILE_99 , EST_SAMPLE_COUNT , EST_SLOPE , EST_INTERCEPT , CASE WHEN EST_SLOPE = 0 T
HEN 0 ELSE GREATEST(0, NVL(100-(25*POWER((1-EST_MU1/EST_SLOPE), 2)*(EST_SAMPLE_COUNT-1) ), 0)) END , LN( 1000) * EST_SLOPE + EST_INTERCEPT , LN(10000) * EST_SLOPE + EST_INTERCEPT ) FROM (SELECT METRIC_ID , BSLN_GUID , TIMEGROUP , EST_MU AS EST_SLOPE , EST_MU * LN(ALPHA) + X_M AS EST_INTERCEPT , TO_NUMBER(NULL) AS EST_FIT_QUALITY , CASE WHEN COUNT_BELOW_X_J > 0 THEN (SUM_BELOW_X_J + (N-M+1)*(X_J-X_M))/COUNT_BELOW_X_J - X_J ELSE TO_NUMBER(NULL) END AS EST_MU1 , EST_SAMPLE_COUNT , N AS SAMPLE_COUNT , AVERAGE , MINIMUM , MAXIMUM , SDEV , PCTILE_25 , PCTILE_50 , PCTILE_75 , PCTILE_90 , PCTILE_95 , PCTILE_99 FROM (SELECT METRIC_ID , BSLN_GUID , TIMEGROUP , MAX(N) AS N , COUNT(RRANK) AS EST_SAMPLE_COUNT , CASE WHEN COUNT(RRANK) > 3 THEN ( SUM(OBS_VALUE) + ( MAX(N) - MAX(RRANK) ) * MAX(OBS_VALUE) - (MAX(N) - MIN(RRANK) + 1) * MIN(OBS_VALUE) ) / (COUNT(RRANK)-1) ELSE TO_NUMBER(NULL) END AS EST_MU , (MAX(N) - MIN(RRANK) + 1) / (MAX(N) + 1) AS ALPHA , MIN(OBS_VALUE) AS X_M , MAX(OBS_VALUE)
AS X_L , MAX(RRANK) AS L , MIN(RRANK) AS M , MAX(MID_TAIL_VALUE) AS X_J , SUM(CASE WHEN OBS_VALUE < MID_TAIL_VALUE THEN OBS_VALUE ELSE 0 END ) AS SUM_BELOW_X_J , SUM(CASE WHEN CUME_DIST < :B7 THEN 1 ELSE 0 END ) AS COUNT_BELOW_X_J , MAX(MAX_VAL) AS MAXIMUM , MAX(MIN_VAL) AS MINIMUM , MAX(AVG_VAL) AS AVERAGE , MAX(SDEV_VAL) AS SDEV , MAX(PCTILE_25) AS PCTILE_25 , MAX(PCTILE_50) AS PCTILE_50 , MAX(PCTILE_75) AS PCTILE_75 , MAX(PCTILE_90) AS PCTILE_90 , MAX(PCTILE_95) AS PCTILE_95 , MAX(PCTILE_99) AS PCTILE_99 FROM (SELECT METRIC_ID , BSLN_GUID , TIMEGROUP , OBS_VALUE AS OBS_VALUE , CUME_DIST () OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ORDER BY OBS_VALUE ) AS CUME_DIST , COUNT(1) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS N , ROW_NUMBER () OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ORDER BY OBS_VALUE) AS RRANK , PERCENTILE_DISC(:B7 ) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS MID_TAIL_VALUE , M
AX(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS MAX_VAL , MIN(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS MIN_VAL , AVG(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS AVG_VAL , STDDEV(OBS_VALUE) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP ) AS SDEV_VAL , PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_25 , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_50 , PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_75 , PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_90 , PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_95 , PERCENTILE_CONT(0.99)
WITHIN GROUP (ORDER BY OBS_VALUE ASC) OVER (PARTITION BY METRIC_ID, BSLN_GUID, TIMEGROUP) AS PCTILE_99 FROM TIMEGROUPED_RAWDATA D ) X WHERE X.CUME_DIST >= :B9 AND X.CUME_DIST <= :B8 GROUP BY METRIC_ID , BSLN_GUID , TIMEGROUP ) ) ) |
| arx2rgss6k33m | SELECT s.inst_id, s.name, i.instance_name FROM gv$active_services s, gv$instance i WHERE s.name = :p1 AND s.inst_id = i.inst_id |
| atz1gs35q62h1 | SELECT USER_NAME, DESCRIPTION FROM FND_USER WHERE USER_ID = :1 |
| b2gnxm5z6r51n | lock table sys.col_usage$ in exclusive mode nowait |
| b4xyzm6kbypcz | select tabs.table_name, 'APPS', tabs.cluster_name , partitioned, iot_type , TEMPORARY, table_type, table_type_owner , tablespace_name , NESTED , last_analyzed , Dropped , decode(nvl(buffer_pool, 'x') || upper(partitioned) || nvl(iot_type, 'x'), 'xNOx', 'YES', 'NO') is_External , NUM_ROWS from sys.user_All_tables tabs where 1=1 order by table_name |
| b9x8f5knaddzp | BEGIN HR_LOA_SS.calculate_absence_duration ( p_absence_attendance_type_id =>:1 , p_business_group_id =>:2 , p_effective_date =>:3 , p_person_id =>:4 , p_date_start =>:5 , p_date_end =>:6 , p_time_start =>:7 , p_time_end =>:8 , p_abs_information_category =>:9 , p_abs_Information1 =>:10 , p_abs_Information2 =>:11 , p_abs_Information3 =>:12 , p_abs_Information4 =>:13 , p_abs_Information5 =>:14 , p_abs_Information6 =>:15 , p_absence_days =>:16 , p_absence_hours =>:17 , p_use_formula =>:18 , p_min_max_failure =>:19 , p_warning_or_error =>:20 , p_page_error_msg =>:21 );END; |
| bqzutt5g7maz0 | select distinct furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id, furg.start_date, furg.end_date from fnd_user_resp_groups furg, fnd_responsibility fr where furg.user_id = :1 and furg.responsibility_id = fr.responsibility_id and furg.responsibility_application_id = fr.application_id and furg.start_date <= sysdate and (furg.end_date is null or furg.end_date >= sysdate) and fr.start_date <= sysdate and (fr.end_date is null or fr.end_date >= sysdate) order by furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id |
| bvyqm6268u2as | SELECT NODE_NAME FROM FND_NODES WHERE NODE_ID = :B1 |
| c6jh1wx0gpzuj | SELECT 'Y' FROM ICX_TRANSACTIONS WHERE XTID = :B1 |
| cm5vu20fhtnq1 | select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 |
| ctm05f1807rry |
SELECT * FROM (SELECT * FROM ( select distinct ppa.project_id, nvl(ppa.sys_program_flag, 'N') as program_flag, null select_checkbox, ppa.name project_name, ppa.segment1 project_number, ppa.name || ' ( ' || ppa.segment1 || ') ' project_name_number, ppa.project_type, ppa.project_status_code, pps.project_status_name, pps.project_system_status_code, nvl(ppa.start_date, decode(ppa.actual_start_date, '', decode(ppa.scheduled_start_date, '', ppa.target_start_date, ppa.scheduled_start_date), ppa.actual_start_date)) project_start_date, nvl(ppa.completion_date, decode(ppa.actual_finish_date, '', decode(ppa.scheduled_finish_date, '', ppa.target_finish_date, ppa.scheduled_finish_date), ppa.actual_finish_date)) project_end_date, ppa.carrying_out_organization_id project_carrying_out_org_id, hou.name project_carrying_out_org_name, PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJECT_MANAGER(ppa.project_id) project_manager_id, PA_PROJECT_PARTIES_UTILS.GET_CURRENT_PROJ_MANAGER_NAME(
ppa.project_id) project_manager_name, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(ppa.project_id) project_customer_name, PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER(ppa.project_id)project_customer_id, ppa.record_version_number, ppa.template_flag, to_number(null) manager_resource_id, ppa.probability_member_id, pro.probability_percentage project_probability_percentage, ppa.description project_description, FL.meaning public_sector, ppa.public_sector_flag, ftv.territory_code country_code, ftv.territory_short_name country_name, pl.region, pl.city, ppt.service_type_code, null progress_status_code, null progress_status_name, to_date(null) progress_report_end_date, to_date(null) progress_date, null progress_status, null progress_status_active, null progress_icon, null progress_icon_active, null view_psi_columns, ppoa.opportunity_value, ppoa.opp_value_currency_code, ppoa.projfunc_opp_value, ppa.projfunc_currency_code, ppoa.project_opp_value, ppa.project_curr
ency_code, null report_type_name, null report_type_id, ppa.security_level security_level, pal.meaning security_level_name, ppa.long_name project_long_name, null role_name, null person_name, null class_category, null class_code, null project_set_name, ppa.actual_start_date, ppa.actual_finish_date, ppa.baseline_start_date, ppa.baseline_finish_date, ppa.scheduled_start_date, ppa.scheduled_finish_date, ppa.target_start_date, ppa.target_finish_date, ppa.start_date transaction_start_date, ppa.completion_date transaction_finish_date, priority.meaning project_priority, null display_role, null display_class_cat, null display_report, null as PPF_MSR_CBLH_PTD, null as PPF_MSR_CBEH_PTD, null as PPF_MSR_CBLH_ITD, null as PPF_MSR_CBEH_ITD, null as PPF_MSR_TLH_PTD, null as PPF_MSR_THE_PTD, null as PPF_MSR_TLH_ITD, null as PPF_MSR_THE_ITD, null as PPF_MSR_CBRC_PTD, null as PPF_MSR_CBBC_PTD, null as PPF_MSR_CBRC_ITD, null as PPF_MSR_CBBC_ITD, null as PPF_MSR_ETC, n
ull as PPF_MSR_FC, null as PPF_MSR_RC_PTD, null as PPF_MSR_TC_PTD, null as PPF_MSR_RC_ITD, null as PPF_MSR_TC_ITD, null as PPF_MSR_TCC, null as PPF_MSR_LRC_ITD, null as PPF_MSR_TLC_ITD, null as PPF_MSR_BCPOTC_ITD, null as PPF_MSR_CCPTC_ITD, null as PPF_MSR_PSE_PTD, null as PPF_MSR_PS_PTD, null as PPF_MSR_PSE_ITD, null as PPF_MSR_PS_ITD, null as PPF_MSR_PSLH_ITD, null as PPF_MSR_PSEH_ITD, null as PPF_MSR_FCV, null as PPF_MSR_FCVOB, null as PPF_MSR_CBCVOB, null as PPF_MSR_CBR_PTD, null as PPF_MSR_OBR_PTD, null as PPF_MSR_CBR_ITD, null as PPF_MSR_OBR_ITD, null as PPF_MSR_R_PTD, null as PPF_MSR_R_ITD, null as PPF_MSR_M_PTD, null as PPF_MSR_MP_PTD, null as PPF_MSR_M_ITD, null as PPF_MSR_MP_ITD, null as PPF_MSR_RV_ITD, null as PPF_MSR_FRV, null as PPF_MSR_MV_ITD, null as PPF_MSR_MPV_ITD, null as PPF_MSR_FMV, null as PPF_MSR_FMPV, null as PPF_MSR_PC, null as PPF_MSR_PPC, null as PPF_MSR_BA, null as PPF_MSR_BCWP, null as PPF_MSR_SPI_ITD, null as PPF_MSR_
CPI_ITD, null as PPF_MSR_EVSV_ITD, null as PPF_MSR_EVCV_ITD, null as PPF_MSR_B, null as PPF_MSR_RR, null as PPF_MSR_OR, null as PPF_MSR_PDR, null as PPF_MSR_FP_CUST1_PTD, null as PPF_MSR_FP_CUST2_PTD, null as PPF_MSR_FP_CUST3_PTD, null as PPF_MSR_FP_CUST4_PTD, null as PPF_MSR_FP_CUST5_PTD, null as PPF_MSR_FP_CUST1_ITD, null as PPF_MSR_FP_CUST2_ITD, null as PPF_MSR_FP_CUST3_ITD, null as PPF_MSR_FP_CUST4_ITD, null as PPF_MSR_FP_CUST5_ITD, null as PPF_MSR_AC_CUST1_PTD, null as PPF_MSR_AC_CUST2_PTD, null as PPF_MSR_AC_CUST3_PTD, null as PPF_MSR_AC_CUST4_PTD, null as PPF_MSR_AC_CUST5_PTD, null as PPF_MSR_AC_CUST1_ITD, null as PPF_MSR_AC_CUST2_ITD, null as PPF_MSR_AC_CUST3_ITD, null as PPF_MSR_AC_CUST4_ITD, null as PPF_MSR_AC_CUST5_ITD, null as kpa_1, null as kpa_2, null as kpa_3, null as kpa_4, null as kpa_5, null as status, null as PPF_MSR_CBLH_PTD_VAL, null as PPF_MSR_CBEH_PTD_VAL, null as PPF_MSR_CBLH_ITD_VAL, null as PPF_MSR_CBEH_ITD_VAL, null as PPF
_MSR_TLH_PTD_VAL, null as PPF_MSR_THE_PTD_VAL, null as PPF_MSR_TLH_ITD_VAL, null as PPF_MSR_THE_ITD_VAL, null as PPF_MSR_CBRC_PTD_VAL, null as PPF_MSR_CBBC_PTD_VAL, null as PPF_MSR_CBRC_ITD_VAL, null as PPF_MSR_CBBC_ITD_VAL, null as PPF_MSR_ETC_VAL, null as PPF_MSR_FC_VAL, null as PPF_MSR_RC_PTD_VAL, null as PPF_MSR_TC_PTD_VAL, null as PPF_MSR_RC_ITD_VAL, null as PPF_MSR_TC_ITD_VAL, null as PPF_MSR_TCC_VAL, null as PPF_MSR_LRC_ITD_VAL, null as PPF_MSR_TLC_ITD_VAL, null as PPF_MSR_BCPOTC_ITD_VAL, null as PPF_MSR_CCPTC_ITD_VAL, null as PPF_MSR_PSE_PTD_VAL, null as PPF_MSR_PS_PTD_VAL, null as PPF_MSR_PSE_ITD_VAL, null as PPF_MSR_PS_ITD_VAL, null as PPF_MSR_PSLH_ITD_VAL, null as PPF_MSR_PSEH_ITD_VAL, null as PPF_MSR_FCV_VAL, null as PPF_MSR_FCVOB_VAL, null as PPF_MSR_CBCVOB_VAL, null as PPF_MSR_CBR_PTD_VAL, null as PPF_MSR_OBR_PTD_VAL, null as PPF_MSR_CBR_ITD_VAL, null as PPF_MSR_OBR_ITD_VAL, null as PPF_MSR_R_PTD_VAL, null as PPF_MSR_R_ITD_VAL, null as PP
F_MSR_M_PTD_VAL, null as PPF_MSR_MP_PTD_VAL, null as PPF_MSR_M_ITD_VAL, null as PPF_MSR_MP_ITD_VAL, null as PPF_MSR_RV_ITD_VAL, null as PPF_MSR_FRV_VAL, null as PPF_MSR_MV_ITD_VAL, null as PPF_MSR_MPV_ITD_VAL, null as PPF_MSR_FMV_VAL, null as PPF_MSR_FMPV_VAL, null as PPF_MSR_PC_VAL, null as PPF_MSR_PPC_VAL, null as PPF_MSR_BA_VAL, null as PPF_MSR_BCWP_VAL, null as PPF_MSR_SPI_ITD_VAL, null as PPF_MSR_CPI_ITD_VAL, null as PPF_MSR_EVSV_ITD_VAL, null as PPF_MSR_EVCV_ITD_VAL, null as PPF_MSR_B_VAL, null as PPF_MSR_RR_VAL, null as PPF_MSR_OR_VAL, null as PPF_MSR_PDR_VAL, null as PPF_MSR_FP_CUST1_PTD_VAL, null as PPF_MSR_FP_CUST2_PTD_VAL, null as PPF_MSR_FP_CUST3_PTD_VAL, null as PPF_MSR_FP_CUST4_PTD_VAL, null as PPF_MSR_FP_CUST5_PTD_VAL, null as PPF_MSR_FP_CUST1_ITD_VAL, null as PPF_MSR_FP_CUST2_ITD_VAL, null as PPF_MSR_FP_CUST3_ITD_VAL, null as PPF_MSR_FP_CUST4_ITD_VAL, null as PPF_MSR_FP_CUST5_ITD_VAL, null as PPF_MSR_AC_CUST1_PTD_VAL, null as PPF_MSR_AC_
CUST2_PTD_VAL, null as PPF_MSR_AC_CUST3_PTD_VAL, null as PPF_MSR_AC_CUST4_PTD_VAL, null as PPF_MSR_AC_CUST5_PTD_VAL, null as PPF_MSR_AC_CUST1_ITD_VAL, null as PPF_MSR_AC_CUST2_ITD_VAL, null as PPF_MSR_AC_CUST3_ITD_VAL, null as PPF_MSR_AC_CUST4_ITD_VAL, null as PPF_MSR_AC_CUST5_ITD_VAL, null as kpa_1_VAL, null as kpa_2_VAL, null as kpa_3_VAL, null as kpa_4_VAL, null as kpa_5_VAL, null as status_VAL, opr.name as Operating_Unit, ppa.org_id operating_unit_id, null as PPF_MSR_LRC_PTD, null as PPF_MSR_TLC_PTD, null as PPF_MSR_BCPOTC_PTD, null as PPF_MSR_CCPTC_PTD, null as PPF_MSR_PSLH_PTD, null as PPF_MSR_PSEH_PTD, null as PPF_MSR_RV_PTD, null as PPF_MSR_MV_PTD, null as PPF_MSR_MPV_PTD, null as PPF_MSR_SPI_PTD, null as PPF_MSR_CPI_PTD, null as PPF_MSR_CBLH_QTD, null as PPF_MSR_CBEH_QTD, null as PPF_MSR_TLH_QTD, null as PPF_MSR_THE_QTD, null as PPF_MSR_CBRC_QTD, null as PPF_MSR_CBBC_QTD, null as PPF_MSR_RC_QTD, null as PPF_MSR_TC_QTD, null as PPF_MSR_LRC_QT
D, null as PPF_MSR_TLC_QTD, null as PPF_MSR_BCPOTC_QTD, null as PPF_MSR_CCPTC_QTD, null as PPF_MSR_PSE_QTD, null as PPF_MSR_PS_QTD, null as PPF_MSR_PSLH_QTD, null as PPF_MSR_PSEH_QTD, null as PPF_MSR_CBR_QTD, null as PPF_MSR_OBR_QTD, null as PPF_MSR_R_QTD, null as PPF_MSR_M_QTD, null as PPF_MSR_MP_QTD, null as PPF_MSR_RV_QTD, null as PPF_MSR_MV_QTD, null as PPF_MSR_MPV_QTD, null as PPF_MSR_SPI_QTD, null as PPF_MSR_CPI_QTD, null as PPF_MSR_CBLH_YTD, null as PPF_MSR_CBEH_YTD, null as PPF_MSR_TLH_YTD, null as PPF_MSR_THE_YTD, null as PPF_MSR_CBRC_YTD, null as PPF_MSR_CBBC_YTD, null as PPF_MSR_RC_YTD, null as PPF_MSR_TC_YTD, null as PPF_MSR_LRC_YTD, null as PPF_MSR_TLC_YTD, null as PPF_MSR_BCPOTC_YTD, null as PPF_MSR_CCPTC_YTD, null as PPF_MSR_PSE_YTD, null as PPF_MSR_PS_YTD, null as PPF_MSR_PSLH_YTD, null as PPF_MSR_PSEH_YTD, null as PPF_MSR_CBR_YTD, null as PPF_MSR_OBR_YTD, null as PPF_MSR_R_YTD, null as PPF_MSR_M_YTD, null as PPF_MSR_MP_YTD, null a
s PPF_MSR_RV_YTD, null as PPF_MSR_MV_YTD, null as PPF_MSR_MPV_YTD, null as PPF_MSR_SPI_YTD, null as PPF_MSR_CPI_YTD, null as PPF_MSR_FP_CUST1_QTD, null as PPF_MSR_FP_CUST2_QTD, null as PPF_MSR_FP_CUST3_QTD, null as PPF_MSR_FP_CUST4_QTD, null as PPF_MSR_FP_CUST5_QTD, null as PPF_MSR_FP_CUST1_YTD, null as PPF_MSR_FP_CUST2_YTD, null as PPF_MSR_FP_CUST3_YTD, null as PPF_MSR_FP_CUST4_YTD, null as PPF_MSR_FP_CUST5_YTD, null as PPF_MSR_AC_CUST1_QTD, null as PPF_MSR_AC_CUST2_QTD, null as PPF_MSR_AC_CUST3_QTD, null as PPF_MSR_AC_CUST4_QTD, null as PPF_MSR_AC_CUST5_QTD, null as PPF_MSR_AC_CUST1_YTD, null as PPF_MSR_AC_CUST2_YTD, null as PPF_MSR_AC_CUST3_YTD, null as PPF_MSR_AC_CUST4_YTD, null as PPF_MSR_AC_CUST5_YTD, null as PPF_MSR_LRC_PTD_VAL, null as PPF_MSR_TLC_PTD_VAL, null as PPF_MSR_BCPOTC_PTD_VAL, null as PPF_MSR_CCPTC_PTD_VAL, null as PPF_MSR_PSLH_PTD_VAL, null as PPF_MSR_PSEH_PTD_VAL, null as PPF_MSR_RV_PTD_VAL, null as PPF_MSR_MV_PTD_VAL, null as PPF_M
SR_MPV_PTD_VAL, null as PPF_MSR_SPI_PTD_VAL, null as PPF_MSR_CPI_PTD_VAL, null as PPF_MSR_CBLH_QTD_VAL, null as PPF_MSR_CBEH_QTD_VAL, null as PPF_MSR_TLH_QTD_VAL, null as PPF_MSR_THE_QTD_VAL, null as PPF_MSR_CBRC_QTD_VAL, null as PPF_MSR_CBBC_QTD_VAL, null as PPF_MSR_RC_QTD_VAL, null as PPF_MSR_TC_QTD_VAL, null as PPF_MSR_LRC_QTD_VAL, null as PPF_MSR_TLC_QTD_VAL, null as PPF_MSR_BCPOTC_QTD_VAL, null as PPF_MSR_CCPTC_QTD_VAL, null as PPF_MSR_PSE_QTD_VAL, null as PPF_MSR_PS_QTD_VAL, null as PPF_MSR_PSLH_QTD_VAL, null as PPF_MSR_PSEH_QTD_VAL, null as PPF_MSR_CBR_QTD_VAL, null as PPF_MSR_OBR_QTD_VAL, null as PPF_MSR_R_QTD_VAL, null as PPF_MSR_M_QTD_VAL, null as PPF_MSR_MP_QTD_VAL, null as PPF_MSR_RV_QTD_VAL, null as PPF_MSR_MV_QTD_VAL, null as PPF_MSR_MPV_QTD_VAL, null as PPF_MSR_SPI_QTD_VAL, null as PPF_MSR_CPI_QTD_VAL, null as PPF_MSR_CBLH_YTD_VAL, null as PPF_MSR_CBEH_YTD_VAL, null as PPF_MSR_TLH_YTD_VAL, null as PPF_MSR_THE_YTD_VAL, null as PPF_MSR_CBRC
_YTD_VAL, null as PPF_MSR_CBBC_YTD_VAL, null as PPF_MSR_RC_YTD_VAL, null as PPF_MSR_TC_YTD_VAL, null as PPF_MSR_LRC_YTD_VAL, null as PPF_MSR_TLC_YTD_VAL, null as PPF_MSR_BCPOTC_YTD_VAL, null as PPF_MSR_CCPTC_YTD_VAL, null as PPF_MSR_PSE_YTD_VAL, null as PPF_MSR_PS_YTD_VAL, null as PPF_MSR_PSLH_YTD_VAL, null as PPF_MSR_PSEH_YTD_VAL, null as PPF_MSR_CBR_YTD_VAL, null as PPF_MSR_OBR_YTD_VAL, null as PPF_MSR_R_YTD_VAL, null as PPF_MSR_M_YTD_VAL, null as PPF_MSR_MP_YTD_VAL, null as PPF_MSR_RV_YTD_VAL, null as PPF_MSR_MV_YTD_VAL, null as PPF_MSR_MPV_YTD_VAL, null as PPF_MSR_SPI_YTD_VAL, null as PPF_MSR_CPI_YTD_VAL, null as PPF_MSR_FP_CUST1_QTD_VAL, null as PPF_MSR_FP_CUST2_QTD_VAL, null as PPF_MSR_FP_CUST3_QTD_VAL, null as PPF_MSR_FP_CUST4_QTD_VAL, null as PPF_MSR_FP_CUST5_QTD_VAL, null as PPF_MSR_FP_CUST1_YTD_VAL, null as PPF_MSR_FP_CUST2_YTD_VAL, null as PPF_MSR_FP_CUST3_YTD_VAL, null as PPF_MSR_FP_CUST4_YTD_VAL, null as PPF_MSR_FP_CUST5_YTD_VAL, null as PPF
_MSR_AC_CUST1_QTD_VAL, null as PPF_MSR_AC_CUST2_QTD_VAL, null as PPF_MSR_AC_CUST3_QTD_VAL, null as PPF_MSR_AC_CUST4_QTD_VAL, null as PPF_MSR_AC_CUST5_QTD_VAL, null as PPF_MSR_AC_CUST1_YTD_VAL, null as PPF_MSR_AC_CUST2_YTD_VAL, null as PPF_MSR_AC_CUST3_YTD_VAL, null as PPF_MSR_AC_CUST4_YTD_VAL, null as PPF_MSR_AC_CUST5_YTD_VAL FROM pa_projects_all PPA, HR_ALL_ORGANIZATION_UNITS HOU , PA_PROJECT_STATUSES PPS , PA_PROBABILITY_MEMBERS PRO , PA_LOCATIONS PL , FND_TERRITORIES_TL FTV , PA_PROJECT_TYPES_ALL PPT , FND_LOOKUPS FL , pa_project_opp_attrs ppoa , pa_lookups PAL , pa_lookups priority , hr_all_organization_units_vl opr, (select PA_SECURITY_PVT.get_grantee_key grantee_key, decode(employee_id, null, PA_UTILS.GET_PARTY_ID(user_id), employee_id) resource_source_id, decode(employee_id, null, 112, 101) resource_type_id from fnd_user where user_id = FND_GLOBAL.user_id) login_user , pa_project_parties ppp WHERE PPA.TEMPLATE_FLAG = 'N' AND PPA.CARRYING_OUT_ORGANIZATION_
ID = HOU.ORGANIZATION_ID AND PPA.ORG_ID = opr.ORGANIZATION_ID AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE AND PPA.PROBABILITY_MEMBER_ID = PRO.PROBABILITY_MEMBER_ID (+) AND PPA.LOCATION_ID = PL.LOCATION_ID (+) AND PL.COUNTRY_CODE = FTV.TERRITORY_CODE (+) AND USERENV('LANG') = FTV.LANGUAGE (+) AND PPA.PROJECT_TYPE = PPT.project_type AND PPA.ORG_ID = PPT.ORG_ID AND PPA.PUBLIC_SECTOR_FLAG = FL.LOOKUP_CODE AND FL.LOOKUP_TYPE = 'YES_NO' AND PPA.project_id = ppoa.project_id AND PAL.lookup_type = 'PA_PROJECT_ACCESS_LEVEL' AND PAL.lookup_code = to_char(PPA.security_level) AND priority.lookup_type (+) = 'PA_PROJECT_PRIORITY_CODE' AND priority.lookup_code (+) = ppa.priority_code AND ( 'SUPER_USER' =:1 OR (PPA.security_level = 1 and login_user.resource_type_id= 101) OR exists( select '1' from pa_project_parties where project_id = PPA.project_id and resource_source_id = login_user.resource_source_id and resource_type_id = login_user.resource_type_id) OR exists( select '1' from fnd_grants
fg, fnd_objects fo, pa_project_role_types_b ppr where FG.GRANTEE_KEY = login_user.grantee_key AND FG.GRANTEE_TYPE = 'USER' AND FG.MENU_ID = PPR.MENU_ID AND PPR.PROJECT_ROLE_ID = 3 AND FG.INSTANCE_TYPE = 'INSTANCE' AND sysdate between fg.start_date and nvl( fg.end_date, sysdate + 1 ) AND FG.OBJECT_ID = FO.OBJECT_ID AND FO.OBJ_NAME = 'ORGANIZATION' AND PPA.CARRYING_OUT_ORGANIZATION_ID = to_number(FG.INSTANCE_PK1_VALUE) )) AND ppp.project_role_id=:2 AND ppp.project_id = ppa.project_id AND ppp.resource_source_id =:3 AND ppp.resource_type_id =:4 ) QRSLT WHERE (PROJECT_SYSTEM_STATUS_CODE <> :5 AND PROJECT_SYSTEM_STATUS_CODE <> :6 AND PROJECT_SYSTEM_STATUS_CODE <> :7 AND PROJECT_SYSTEM_STATUS_CODE <> :8)) QRSLT ORDER BY PROJECT_NAME ASC |
| cvn54b7yz0s8u | select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# |
| damp74n7s5bpq |
select /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/ WN.NOTIFICATION_ID, WN.FROM_USER, DECODE(WN.MORE_INFO_ROLE, NULL, WN.TO_USER, wf_directory.GetRoleDisplayName(WN.MORE_INFO_ROLE)) AS TO_USER, DECODE(WN.MORE_INFO_ROLE, NULL, WN.SUBJECT, FND_MESSAGE.GET_STRING('FND', 'FND_MORE_INFO_REQUESTED')||' '||WN.SUBJECT) AS SUBJECT, WN.LANGUAGE, WN.BEGIN_DATE, WN.DUE_DATE, WN.STATUS, WN.PRIORITY, 'P' AS PRIORITY_F, WN.RECIPIENT_ROLE, WN.END_DATE, WIT.DISPLAY_NAME AS TYPE, WN.MORE_INFO_ROLE, WN.FROM_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.MAIL_STATUS, WN.ORIGINAL_RECIPIENT from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_TL WIT, WF_LOOKUPS_TL WL where WN.STATUS = 'OPEN' and WN.message_type = WIT.name and WIT.language = userenv('LANG') and WL.lookup_type = 'WF_NOTIFICATION_STATUS' and WN.status = WL.lookup_code and WL.language = userenv('LANG') and WN.recipient_role in (select WUR.role_name from WF_USER_ROLES WUR where WUR.user_name = :1 and WUR.user
_orig_system = :2 and WUR.user_orig_system_id = :3) and more_info_role is null union all select /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N6)*/ WN.NOTIFICATION_ID, WN.FROM_USER, DECODE(WN.MORE_INFO_ROLE, NULL, WN.TO_USER, wf_directory.GetRoleDisplayName(WN.MORE_INFO_ROLE)) AS TO_USER, DECODE(WN.MORE_INFO_ROLE, NULL, WN.SUBJECT, FND_MESSAGE.GET_STRING('FND', 'FND_MORE_INFO_REQUESTED')||' '||WN.SUBJECT) AS SUBJECT, WN.LANGUAGE, WN.BEGIN_DATE, WN.DUE_DATE, WN.STATUS, WN.PRIORITY, 'P' AS PRIORITY_F, WN.RECIPIENT_ROLE, WN.END_DATE, WIT.DISPLAY_NAME AS TYPE, WN.MORE_INFO_ROLE, WN.FROM_ROLE, WN.MESSAGE_TYPE, WN.MESSAGE_NAME, WN.MAIL_STATUS, WN.ORIGINAL_RECIPIENT from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_TL WIT, WF_LOOKUPS_TL WL where WN.STATUS = 'OPEN' and WN.message_type = WIT.name and WIT.language = userenv('LANG') and WL.lookup_type = 'WF_NOTIFICATION_STATUS' and WN.status = WL.lookup_code and WL.language = userenv('LANG') and WN.more_info_r
ole in (select WUR.role_name from WF_USER_ROLES WUR where WUR.user_name = :4 and WUR.user_orig_system = :5 and WUR.user_orig_system_id= :6) |
| dawf7hvg1ym94 | select V.PROFILE_OPTION_VALUE from FND_PROFILE_OPTIONS O, FND_PROFILE_OPTION_VALUES V where O.PROFILE_OPTION_NAME = :1 and V.LEVEL_ID = :2 and O.START_DATE_ACTIVE <= SYSDATE and nvl(O.END_DATE_ACTIVE, SYSDATE) >= SYSDATE and O.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID and O.APPLICATION_ID = V.APPLICATION_ID and V.LEVEL_VALUE = :3 |
| dbp7qgbgvmqgz | SELECT mt.inst_id, service_name, TO_CHAR(CAST(begin_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, TO_CHAR(CAST(end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, intsize_csec, group_id, cpupercall, dbtimepercall, callspersec, dbtimepersec FROM gv$servicemetric_history mt , gv$active_services ast WHERE service_name = :p1 AND end_time >= SYSDATE - 5/(60*24) AND group_id = 6 and mt.service_name = ast.name and mt.inst_id = ast.inst_id ORDER BY mt.inst_id asc, end_time DESC |
| dfqmntkyba7sa | BEGIN FFP1103_01011950.FORMULA;END; |
| dsphq1qsnymv8 | BEGIN WF_EVENT_OJMSTEXT_QH.DEQUEUE(:1, :2, :3); END; |
| dzqkd6cnw2w0n | SELECT 'Y' FROM ICX_TRANSACTIONS WHERE TRANSACTION_ID = :B2 AND SESSION_ID = :B1 AND DISABLED_FLAG <> 'Y' |
| fcdqc1hb05rk8 |
SELECT * FROM (select PROJ_ELEMENT_ID , ELEMENT_VERSION_ID , PEV_STRUCTURE_ID , PROJECT_ID , STRUCTURE_NUMBER , STRUCTURE_NAME , STRUCTURE_VERSION_NUMBER , STRUCTURE_VERSION_NAME , DESCRIPTION , SCHEDULED_START_DATE , SCHEDULED_FINISH_DATE , ESTIMATED_START_DATE , ESTIMATED_FINISH_DATE , ACTUAL_START_DATE , ACTUAL_FINISH_DATE , EFFECTIVE_DATE , STATUS_CODE , STATUS_NAME , LOCK_STATUS_CODE , LOCK_STATUS_NAME , LOCKED_DATE , LOCKED_BY_PERSON_ID , LOCKED_BY_NAME , PUBLISHED_DATE , PUBLISHED_BY_PERSON_ID , PUBLISHED_NAME , ORIGINAL_FLAG , ORIGINAL_BASELINE_MEANING , CURRENT_FLAG , CURRENT_BASELINE_MEANING , STRUCTURE_TYPE , LATEST_EFF_PUBLISHED_FLAG , LATEST_EFF_PUBLISHED_MEANING , STRUCTURE_REC_VERSION_NUMBER , STRUC_VER_REC_VERSION_NUMBER , PEV_STRUC_REC_VERSION_NUMBER , PEV_SCHE_REC_VERSION_NUMBER , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('PUBLISH', ELEMENT_VERSION_ID, STATUS_CODE), 'T', (DECODE(PA_PROJECT_STRUCTURE_UTILS.GET_PROCESS_STATUS_CODE(project_id, structure_typ
e), null, 'PubBtn', 'DisbPubBtn')), 'NoPubBtn') publish_switcher , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('REWORK', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'RwkBtn', 'NoRwkBtn') rework_switcher , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('EDIT_TASK_STRUCT', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'EditImg', 'NoEditImg') edit_switcher , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('EDIT_TASK', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'EditImg', 'NoEditImg') edit_task_switcher , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('UNLOCK', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'UnlockBtn', (DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('LOCK', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'LockBtn', 'NoBtn'))) unlock_switcher , DECODE(PA_PROJECT_STRUCTURE_PUB1.check_action_allowed('UNLOCK', ELEMENT_VERSION_ID, STATUS_CODE), 'T', 'NoName', (DECODE(LOCKED_BY_NAME, NULL, 'NoName', '', 'NoName', 'LockedName'))) label_switcher , COMPLETED
_PERCENTAGE , PERCENT_COMPLETE_ID , STATUS_ICON_IND , PROGRESS_STATUS_CODE , PROGRESS_STATUS_MEANING , PROG_ESTIMATED_START_DATE , PROG_ESTIMATED_FINISH_DATE , PROG_ACTUAL_START_DATE , PROG_ACTUAL_FINISH_DATE , AS_OF_DATE , decode(progress_status_code, NULL, '', '<B><IMG ALT="'||FND_CSS_PKG.ENCODE(progress_status_meaning)||'" SRC="/OA_MEDIA/'||status_icon_ind||'" Border="0"> '||FND_CSS_PKG.ENCODE(progress_status_meaning)||'</B>') progress_status_icon_text , baseline_start_date , baseline_finish_date , CHANGE_REASON_CODE , CHANGE_REASON_MEANING , SCHEDULE_AS_OF_DATE , BASELINE_AS_OF_DATE , ESTIMATE_AS_OF_DATE , ACTUAL_AS_OF_DATE , PLANNED_EFFORT , CALENDAR_ID , CALENDAR_NAME , SCHEDULED_DURATION_DAYS , BASELINE_DURATION_DAYS , ESTIMATED_DURATION_DAYS , ACTUAL_DURATION_DAYS , LIFECYCLE_VERSION_ID , LIFECYCLE_VERSION_NAME , SHORT_LIFECYCLE_VER_NAME , CURRENT_PHASE_VERSION_ID , CURRENT_PHASE_NAME , CURRENT_SHORT_PHASE_NAME , decode(CURRENT
_SHORT_PHASE_NAME, null, CURRENT_PHASE_NAME || CURRENT_SHORT_PHASE_NAME, CURRENT_PHASE_NAME||' (' || CURRENT_SHORT_PHASE_NAME||')') as DISPLAY_PHASE_NAME , decode(SHORT_LIFECYCLE_VER_NAME, null, LIFECYCLE_VERSION_NAME || SHORT_LIFECYCLE_VER_NAME, LIFECYCLE_VERSION_NAME||' (' || SHORT_LIFECYCLE_VER_NAME||')') as DISPLAY_LIFECYCLE_NAME , decode(CURRENT_WORKING_FLAG, 'Y', 'isCurrent', 'notCurrent') as CURRENT_SWITCHER , decode(SCHEDULE_DIRTY_FLAG, 'Y', 'isScheduled', 'notScheduled') as SCHEDULED_SWITCHER , RAW_COST , BURDENED_COST , PLANNED_COST , ACTUAL_EFFORT , PLANNED_EQUIP_EFFORT , ACTUAL_EQUIP_EFFORT , 'USD' as currency_code , Date_Prog_Applied_On_Wver as ProgressLastApplied , Time_Last_Progress_Published as LatestSubmittedProgress , (nvl(PLANNED_EFFORT, 0)- nvl(PLANNED_EQUIP_EFFORT, 0)) as PlannedPeopEffort from pa_structure_versions_v) QRSLT WHERE (element_version_id = :1 and structure_type = :2) |
| fujjgkrpgrw6v | UPDATE BNE_DOCUMENTS SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1, LAST_USED_DATE = SYSDATE, LAST_UPDATED_BY = :1, LAST_UPDATE_DATE = SYSDATE WHERE DOCUMENT_ID = :2 |
| g22cn4nvvn83b | begin HXC_APPROVAL_WF_PKG.process_appl_periods (:v1, :v2, :v3, :v4, :v5); end; |
| g8cfwyffrqusr |
INSERT INTO PA_STRUCTURES_TASKS_TMP ( PARENT_PROJECT_ID , ELEMENT_NUMBER , ELEMENT_NAME , DESCRIPTION , OBJECT_TYPE , ELEMENT_VERSION_ID , PROJ_ELEMENT_ID , PROJECT_ID , DISPLAY_SEQUENCE , MILESTONE_FLAG , MILESTONE_FLAG_MEANING , CRITICAL_FLAG , CRITICAL_FLAG_MEANING , PARENT_ELEMENT_VERSION_ID , PARENT_OBJECT_TYPE , RELATIONSHIP_TYPE , RELATIONSHIP_SUBTYPE , SUMMARY_ELEMENT_FLAG , PROGRESS_STATUS_CODE , PROGRESS_STATUS_MEANING , PROGRESS_COMMENTS , PROGRESS_BRIEF_OVERVIEW , SCHEDULED_START_DATE , SCHEDULED_FINISH_DATE , TASK_MANAGER_ID , TASK_MANAGER , PARENT_STRUCTURE_VERSION_ID , WBS_LEVEL , WBS_NUMBER , ELEM_REC_VER_NUMBER , ELEM_VER_REC_VER_NUMBER , ELEM_VER_SCH_REC_VER_NUMBER , PARENT_VER_REC_VER_NUMBER , STATUS_ICON_ACTIVE_IND , PERCENT_COMPLETE_ID , STATUS_ICON_IND , STATUS_CODE , STATUS_CODE_MEANING , PRIORITY_CODE , PRIORITY_DESCRIPTION , ORGANIZATION_ID , ORGANIZATION_NAME , INCLUDE_IN_PROJ_PROG_RPT , ESTIMATED_START_DATE , ESTIMA
TED_FINISH_DATE , ACTUAL_START_DATE , ACTUAL_FINISH_DATE , COMPLETED_PERCENTAGE , OBJECT_RELATIONSHIP_ID , OBJECT_REC_VER_NUMBER , PEV_SCHEDULE_ID , LATEST_EFF_PUBLISHED_FLAG , PROJECT_NUMBER , PROJECT_NAME , PARENT_ELEMENT_ID , STRUCTURE_TYPE_CLASS_CODE , PUBLISHED_DATE , LINK_TASK_FLAG , DISPLAY_PARENT_VERSION_ID , AS_OF_DATE , REPORT_VERSION_ID , BASELINE_START_DATE , BASELINE_FINISH_DATE , SCH_BSL_START_VAR , SCH_BSL_FINISH_VAR , EST_SCH_START_VAR , EST_SCH_FINISH_VAR , ACT_SCH_START_VAR , ACT_SCH_FINISH_VAR , PM_SOURCE_NAME , PM_SOURCE_CODE , PM_SOURCE_REFERENCE , ACTIVE_TASK_FLAG , ACTIVE_TASK_MEANING , DAYS_TO_SCH_START , DAYS_TO_SCH_FINISH , WORK_TELEPHONE , SERVICE_TYPE_MEANING , SERVICE_TYPE_CODE , WORK_TYPE_NAME , WORK_TYPE_ID , CHARGEABLE_MEANING , CHARGEABLE_FLAG , BILLABLE_MEANING , BILLABLE_FLAG , RECEIVE_PROJECT_INVOICE_M , RECEIVE_PROJECT_INVOICE_FLAG , TRANSACTION_CTRL_START_DATE , TRANSACTION_CTRL_FINISH_DATE , PRIOR_PERCE
NT_COMPLETE , SCHEDULE_AS_OF_DATE , TRANSACTION_AS_OF_DATE , BASELINE_AS_OF_DATE , ESTIMATE_AS_OF_DATE , ACTUAL_AS_OF_DATE , FINANCIAL_TASK_FLAG , DAYS_TO_ESTIMATE_START , DAYS_TO_ESTIMATE_FINISH , DAYS_SINCE_ACT_START , DAYS_SINCE_ACT_FINISH , FINISHED_TASK_FLAG , FINISHED_TASK_MEANING , TASK_CREATION_DATE , LOWEST_TASK_MEANING , TASK_TYPE_ID , TASK_TYPE , TASK_STATUS_CODE , TASK_STATUS_MEANING , PHASE_CODE , PHASE_CODE_MEANING , PLANNED_EFFORT , WEIGHTING_PERCENTAGE , SCHEDULED_DURATION_DAYS , BASELINE_DURATION_DAYS , ESTIMATED_DURATION_DAYS , ACTUAL_DURATION_DAYS , ADDRESS_ID , ADDRESS1 , ADDRESS2 , ADDRESS3 , ADDRESS4 , WQ_ITEM_CODE , WQ_ITEM_MEANING , WQ_UOM_CODE , WQ_UOM_MEANING , WQ_PLANNED_QUANTITY , ACTUAL_WQ_ENTRY_CODE , ACTUAL_WQ_ENTRY_MEANING , PROG_ENTRY_ENABLE_FLAG , PERCENT_COMP_ENABLE_FLAG , REMAIN_EFFORT_ENABLE_FLAG , TASK_PROGRESS_ENTRY_PAGE_ID , PAGE_NAME , BASE_PERCENT_COMP_DERIV_CODE , BASE_PERCENT_COMP_DERIV_M , WQ_ENA
BLE_FLAG , PROG_ENTRY_REQ_FLAG , ESTIMATED_REMAINING_EFFORT , STRUCT_PUBLISHED_FLAG , ACTUAL_WORK_QUANTITY , VERSIONING_ENABLED_FLAG , PHASE_VERSION_ID , PHASE_NAME , SHORT_PHASE_NAME , ATTRIBUTE_CATEGORY , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , LIFECYCLE_VERSION_ID , TASK_UNPUB_VER_STATUS_CODE , OPEN_ISSUES , OPEN_CHANGE_DOCUMENTS , CHILD_ELEMENT_FLAG , DAYS_UNTIL_SCHEDULED_FINISH , CURRENT_PHASE_NAME , OPEN_CHANGE_REQUESTS , OPEN_CHANGE_ORDERS , PLANNED_EQUIP_EFFORT , RAW_COST , BURDENED_COST , PLANNED_COST , ACTUAL_EFFORT , ACTUAL_EQUIP_EFFORT , PREDECESSORS , PERCENT_SPENT_EFFORT , PERCENT_SPENT_COST , PERCENT_COMPLETE_EFFORT , PERCENT_COMPLETE_COST , ACTUAL_DURATION , REMAINING_DURATION , CONSTRAINT_TYPE , CONSTRAINT_TYPE_CODE , CONSTRAINT_DATE , EARLY_START_DATE , EARLY_FINISH_DATE , LATE_START_DATE , LATE_FINISH_DATE , FREE_SLACK , TOTAL_S
LACK , LOWEST_TASK , ESTIMATED_BASELINE_START , ESTIMATED_BASELINE_FINISH , PLANNED_BASELINE_START , PLANNED_BASELINE_FINISH , BASELINE_EFFORT , ETC_EFFORT , ESTIMATE_AT_COMPLETION_EFFORT , VARIANCE_AT_COMPLETION_EFFORT , EFFORT_VARIANCE , EFFORT_VARIANCE_PERCENT , ACTUAL_COST , BASELINE_COST , ESTIMATE_AT_COMPLETION_COST , COST_VARIANCE , COST_VARIANCE_PERCENT , ETC_WORK_QUANTITY , PLANNED_COST_PER_UNIT , ACTUAL_COST_PER_UNIT , WORK_QUANTITY_VARIANCE , WORK_QUANTITY_VARIANCE_PERCENT , EARNED_VALUE , SCHEDULE_VARIANCE , EARNED_VALUE_COST_VARIANCE , EARNED_VALUE_SCHEDULE_VARIANCE , VARIANCE_AT_COMPLETION_COST , TO_COMPLETE_PERFORMANCE_INDEX , BUDGETED_COST_OF_WORK_SCH , SCHEDULE_PERFORMANCE_INDEX , COST_PERFORMANCE_INDEX , MAPPED_FINANCIAL_TASK , DELIVERABLES , ETC_SOURCE_CODE , ETC_SOURCE_NAME , WF_ITEM_TYPE , WF_PROCESS , WF_START_LEAD_DAYS , ENABLE_WF_FLAG , MAPPED_FIN_TASK_NAME , ETC_COST , PROGRESS_ROLLUP_ID , BASE_PERCENT_COMPLETE , PLANN
ED_BASELINE_EFFORT_VAR , PLANNED_BASELINE_COST_VAR ) SELECT /*+ INDEX(pfxat pji_fm_xbs_accum_tmp1_n1)*/ :B7 , DECODE( PPE.OBJECT_TYPE, 'PA_TASKS', PPE.ELEMENT_NUMBER, 'PA_STRUCTURES', TO_CHAR( PPVS.VERSION_NUMBER ) ) , DECODE( PPE.OBJECT_TYPE, 'PA_TASKS', PPE.NAME, 'PA_STRUCTURES', PPVS.NAME ) , PPE.DESCRIPTION , PPE.OBJECT_TYPE , PPV.ELEMENT_VERSION_ID , PPE.PROJ_ELEMENT_ID , PPA.PROJECT_ID , PPV.DISPLAY_SEQUENCE + :B6 , PPVSCH.MILESTONE_FLAG , DECODE(PPVSCH.MILESTONE_FLAG, 'Y', :B5 , :B4 ) , PPVSCH.CRITICAL_FLAG , DECODE(PPVSCH.CRITICAL_FLAG, 'Y', :B5 , :B4 ) , POR.OBJECT_ID_FROM1 , POR.OBJECT_TYPE_FROM , POR.RELATIONSHIP_TYPE , POR.RELATIONSHIP_SUBTYPE , DECODE(PPE.OBJECT_TYPE , 'PA_STRUCTURES', 'Y' , 'PA_TASKS', PA_PROJ_ELEMENTS_UTILS.IS_SUMMARY_TASK_OR_STRUCTURE(PPV.ELEMENT_VERSION_ID)) SUMMARY_ELEMENT_FLAG , NVL( PPRU.PROGRESS_STATUS_CODE, PPRU.EFF_ROLLUP_PROG_STAT_CODE) , PPS.PROJECT_STATUS_NAME , NULL , NULL , PPVSCH.SCHEDULED_START_DATE , PPVSCH.SCHEDULED_FINISH_
DATE , PPE.MANAGER_PERSON_ID , PAPF.FULL_NAME , PPV.PARENT_STRUCTURE_VERSION_ID , PPV.WBS_LEVEL , PPV.WBS_NUMBER , PPE.RECORD_VERSION_NUMBER , PPV.RECORD_VERSION_NUMBER , PPVSCH.RECORD_VERSION_NUMBER , PPV2.RECORD_VERSION_NUMBER , PPS.STATUS_ICON_ACTIVE_IND , PPRU.PERCENT_COMPLETE_ID , PPS.STATUS_ICON_IND , PPE.STATUS_CODE , PPS2.PROJECT_STATUS_NAME , PPE.PRIORITY_CODE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_TASK_PRIORITY_CODE' , PPE.PRIORITY_CODE) , PPE.CARRYING_OUT_ORGANIZATION_ID , HOU.NAME , PPE.INC_PROJ_PROGRESS_FLAG , PPVSCH.ESTIMATED_START_DATE , PPVSCH.ESTIMATED_FINISH_DATE , PPVSCH.ACTUAL_START_DATE , PPVSCH.ACTUAL_FINISH_DATE , NVL( PPRU.COMPLETED_PERCENTAGE, PPRU.EFF_ROLLUP_PERCENT_COMP ) , POR.OBJECT_RELATIONSHIP_ID , POR.RECORD_VERSION_NUMBER , PPVSCH.PEV_SCHEDULE_ID , PPVS.LATEST_EFF_PUBLISHED_FLAG , PPA.SEGMENT1 , PPA.NAME , PPV2.PROJ_ELEMENT_ID , PST.STRUCTURE_TYPE_CLASS_CODE , PPVS.PUBLISHED_DATE , PPE.LINK_TASK_FLAG , POR.OBJECT_ID_FROM1 , PPRU.AS_OF_DATE
, TO_NUMBER(NULL) , PPE.BASELINE_START_DATE , PPE.BASELINE_FINISH_DATE , PPVSCH.SCHEDULED_START_DATE - PPE.BASELINE_START_DATE , PPVSCH.SCHEDULED_FINISH_DATE - PPE.BASELINE_FINISH_DATE , PPVSCH.ESTIMATED_START_DATE - PPVSCH.SCHEDULED_START_DATE , PPVSCH.ESTIMATED_FINISH_DATE - PPVSCH.SCHEDULED_FINISH_DATE , PPVSCH.ACTUAL_START_DATE - PPVSCH.SCHEDULED_START_DATE , PPVSCH.ACTUAL_FINISH_DATE - PPVSCH.SCHEDULED_FINISH_DATE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PM_PRODUCT_CODE', PPE.PM_SOURCE_CODE) , PPE.PM_SOURCE_CODE , PPE.PM_SOURCE_REFERENCE , PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(PPV.ELEMENT_VERSION_ID, PPV.OBJECT_TYPE) , DECODE(PA_PROJ_ELEMENTS_UTILS.IS_ACTIVE_TASK(PPV.ELEMENT_VERSION_ID, PPV.OBJECT_TYPE), 'Y', :B5 , :B4 ) , DECODE(PPV.OBJECT_TYPE, 'PA_STRUCTURES', NULL, (TRUNC(PPVSCH.SCHEDULED_START_DATE) - TRUNC(SYSDATE))) , DECODE(PPV.OBJECT_TYPE, 'PA_STRUCTURES', NULL, (TRUNC(PPVSCH.SCHEDULED_FINISH_DATE) - TRUNC(SYSDATE))) , PAPF.WORK_TELEPHONE , PA_PROJ_ELEME
NTS_UTILS.GET_PA_LOOKUP_MEANING('SERVICE TYPE', PT.SERVICE_TYPE_CODE) , PT.SERVICE_TYPE_CODE , PWT.NAME , PT.WORK_TYPE_ID , DECODE(PT.CHARGEABLE_FLAG, 'Y', :B5 , :B4 ) , PT.CHARGEABLE_FLAG , DECODE(PT.BILLABLE_FLAG, 'Y', :B5 , :B4 ) , PT.BILLABLE_FLAG , DECODE(PT.RECEIVE_PROJECT_INVOICE_FLAG, 'Y', :B5 , :B4 ) , PT.RECEIVE_PROJECT_INVOICE_FLAG , DECODE(PPE.TASK_STATUS, NULL, PT.START_DATE, PPVSCH.SCHEDULED_START_DATE) START_DATE , DECODE(PPE.TASK_STATUS, NULL, PT.COMPLETION_DATE, PPVSCH.SCHEDULED_FINISH_DATE) COMPLETION_DATE , PA_PROGRESS_UTILS.GET_PRIOR_PERCENT_COMPLETE(PPA.PROJECT_ID, PPE.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE) , PPVSCH.LAST_UPDATE_DATE , TO_DATE(NULL) , PPA.BASELINE_AS_OF_DATE , PPRU.LAST_UPDATE_DATE , PPRU.LAST_UPDATE_DATE , DECODE(PPE.PROJ_ELEMENT_ID, PT.TASK_ID, 'Y', 'N') , TRUNC(PPVSCH.ESTIMATED_START_DATE) - TRUNC(SYSDATE) , TRUNC(PPVSCH.ESTIMATED_FINISH_DATE) - TRUNC(SYSDATE) , TRUNC(SYSDATE) - TRUNC(PPVSCH.ACTUAL_START_DATE) , TRUNC(SYSDATE) - TRUNC(PPVSCH.ACTUA
L_FINISH_DATE) , DECODE(PPVSCH.ACTUAL_FINISH_DATE, NULL, 'N', 'Y') , DECODE(PPVSCH.ACTUAL_FINISH_DATE, NULL, :B4 , :B5 ) , PPE.CREATION_DATE , DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPV.ELEMENT_VERSION_ID), 'Y', :B5 , :B4 ) , PPE.TYPE_ID , TT.TASK_TYPE , PPE.STATUS_CODE , PPS3.PROJECT_STATUS_NAME , PPE5.PHASE_CODE , PPS5.PROJECT_STATUS_NAME , PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS, PFXAT.EQUIPMENT_HOURS) PLANNED_EFFORT , POR.WEIGHTING_PERCENTAGE , PPVSCH.DURATION , PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(PPE.BASELINE_DURATION) , PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(PPVSCH.ESTIMATED_DURATION) , PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(PPVSCH.ACTUAL_DURATION) , PT.ADDRESS_ID , ADDR.ADDRESS1 , ADDR.ADDRESS2 , ADDR.ADDRESS3 , ADDR.ADDRESS4|| DECODE(ADDR.ADDRESS4, NULL, NULL, ', ')|| ADDR.CITY||', '||NVL(ADDR.STATE, ADDR.PROVINCE)||', ' ||ADDR.COUNTY , PPE.WQ_ITEM_CODE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_WQ_WORK_ITEMS', PPE.WQ_ITEM_CODE) , PPE.WQ_U
OM_CODE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT', PPE.WQ_UOM_CODE) , PPVSCH.WQ_PLANNED_QUANTITY , PPE.WQ_ACTUAL_ENTRY_CODE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_ACTUAL_WQ_ENTRY_CODE', PPE.WQ_ACTUAL_ENTRY_CODE) , TT.PROG_ENTRY_ENABLE_FLAG , DECODE(PPPA.PERCENT_COMP_ENABLE_FLAG, 'Y', TT.PERCENT_COMP_ENABLE_FLAG, 'N') , DECODE(PPPA.REMAIN_EFFORT_ENABLE_FLAG, 'Y', TT.REMAIN_EFFORT_ENABLE_FLAG, 'N') , PPE.TASK_PROGRESS_ENTRY_PAGE_ID , PPL.PAGE_NAME , NVL(PPE.BASE_PERCENT_COMP_DERIV_CODE, TT.BASE_PERCENT_COMP_DERIV_CODE) , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_PERCENT_COMP_DERIV_CODE', NVL(PPE.BASE_PERCENT_COMP_DERIV_CODE, TT.BASE_PERCENT_COMP_DERIV_CODE)) , TT.WQ_ENABLE_FLAG , TT.PROG_ENTRY_REQ_FLAG , PA_PROGRESS_UTILS.CALC_WETC(PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS, PFXAT.EQUIPMENT_HOURS, NULL) , PPRU.PPL_ACT_EFFORT_TO_DATE , PPRU.EQPMT_ACT_EFFORT_TO_DATE , NULL , NULL , NULL , NULL , NULL) ESTIMATED_REMAINING_EFFORT , DECODE(PPV
S.STATUS_CODE, 'STRUCTURE_PUBLISHED', 'Y', 'N') , PPRU.CUMULATIVE_WORK_QUANTITY , :B3 , PPE.PHASE_VERSION_ID , PPE5.NAME , PPE5.ELEMENT_NUMBER , PT.ATTRIBUTE_CATEGORY , PT.ATTRIBUTE1 , PT.ATTRIBUTE2 , PT.ATTRIBUTE3 , PT.ATTRIBUTE4 , PT.ATTRIBUTE5 , PT.ATTRIBUTE6 , PT.ATTRIBUTE7 , PT.ATTRIBUTE8 , PT.ATTRIBUTE9 , PT.ATTRIBUTE10 , PPWA.LIFECYCLE_VERSION_ID , PPV.TASK_UNPUB_VER_STATUS_CODE , PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID, PPE.OBJECT_TYPE, PPE.PROJ_ELEMENT_ID, 'ISSUE') , TO_NUMBER(NULL) , PA_PROJ_ELEMENTS_UTILS.CHECK_CHILD_ELEMENT_EXIST(PPV.ELEMENT_VERSION_ID) , TRUNC(PPVSCH.SCHEDULED_FINISH_DATE) - TRUNC(SYSDATE) , PPEPH.NAME , PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID, PPE.OBJECT_TYPE, PPE.PROJ_ELEMENT_ID, 'CHANGE_REQUEST') , PA_CONTROL_ITEMS_UTILS.GET_OPEN_CONTROL_ITEMS(PPE.PROJECT_ID, PPE.OBJECT_TYPE, PPE.PROJ_ELEMENT_ID, 'CHANGE_ORDER') , PFXAT.EQUIPMENT_HOURS PLANNED_EQUIP_EFFORT , PFXAT.PRJ_RAW_COST RAW_COST , PFXAT.PRJ_BRDN_COST
BURDENED_COST , PFXAT.PRJ_BRDN_COST PLANNED_COST , PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE, PPRU.EQPMT_ACT_EFFORT_TO_DATE, NULL , NULL, NULL, NULL) ACTUAL_EFFORT , PPRU.EQPMT_ACT_EFFORT_TO_DATE ACTUAL_EQUIP_EFFORT , PA_RELATIONSHIP_UTILS.DISPLAY_PREDECESSORS(PPV.ELEMENT_VERSION_ID) PREDECESSORS , PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE ((NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)), (NVL(PFXAT.LABOR_HOURS, 0)+NVL(PFXAT.EQUIPMENT_HOURS, 0)) ) PERCENT_SPENT_EFFORT , PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE ((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)), NVL(PFXAT.PRJ_BRDN_COST, 0) ) PERCENT_SPENT_COST , PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE , PPRU.EQPMT_ACT_EFFORT_TO_DATE , NULL , NULL , NULL , NULL) , PA_PROGRESS_UTILS.CALC_ETC(PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS , PFXAT.EQUIPMENT_HOURS , NULL) ,
PPRU.ESTIMATED_REMAINING_EFFORT , PPRU.EQPMT_ETC_EFFORT , NULL , NULL , NULL , NULL , NULL , PA_PROGRESS_UTILS.CALC_ACT (PPRU.PPL_ACT_EFFORT_TO_DATE , PPRU.EQPMT_ACT_EFFORT_TO_DATE , NULL , NULL , NULL , NULL))) PERCENT_COMPLETE_EFFORT , PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_COST_TO_DATE_PC , PPRU.EQPMT_ACT_COST_TO_DATE_PC , PPRU.OTH_ACT_COST_TO_DATE_PC , NULL , NULL , NULL) , PA_PROGRESS_UTILS.CALC_ETC(PFXAT.PRJ_BRDN_COST , PPRU.PPL_ETC_COST_PC , PPRU.EQPMT_ETC_COST_PC , PPRU.OTH_ETC_COST_PC , NULL , NULL , NULL , NULL , PA_PROGRESS_UTILS.CALC_ACT (PPRU.PPL_ACT_COST_TO_DATE_PC , PPRU.EQPMT_ACT_COST_TO_DATE_PC , PPRU.OTH_ACT_COST_TO_DATE_PC , NULL , NULL , NULL))) PERCENT_COMPLETE_COST , TRUNC(PPRU.ACTUAL_FINISH_DATE) - TRUNC(PPRU.ACTUAL_START_DATE) ACTUAL_DURATION , TRUNC(PPVSCH.SCHEDULED_FINISH_DATE) - TRUNC(SYSDATE) REMAINING_DURATION , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING ( 'PA_SCHEDULE_CONSTRAINT_TY
PE', PPVSCH.CONSTRAINT_TYPE_CODE ) CONSTRAINT_TYPE , PPVSCH.CONSTRAINT_TYPE_CODE , PPVSCH.CONSTRAINT_DATE , PPVSCH.EARLY_START_DATE , PPVSCH.EARLY_FINISH_DATE , PPVSCH.LATE_START_DATE , PPVSCH.LATE_FINISH_DATE , PPVSCH.FREE_SLACK , PPVSCH.TOTAL_SLACK , DECODE(PA_PROJ_ELEMENTS_UTILS.IS_SUMMARY_TASK_OR_STRUCTURE(PPV.ELEMENT_VERSION_ID), 'Y', 'N', 'N', 'Y') LOWEST_TASK , (PPVSCH.ESTIMATED_START_DATE - PPE.BASELINE_START_DATE) ESTIMATED_BASELINE_START , (PPVSCH.ESTIMATED_FINISH_DATE - PPE.BASELINE_FINISH_DATE) ESTIMATED_BASELINE_FINISH , TO_NUMBER ( NULL ) PLANNED_BASELINE_START , TO_NUMBER ( NULL ) PLANNED_BASELINE_FINISH , PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.BASE_EQUIP_HOURS, PFXAT.BASE_LABOR_HOURS, NULL) BASELINE_EFFORT , PA_PROGRESS_UTILS.CALC_ETC(PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS, PFXAT.EQUIPMENT_HOURS, NULL) , PPRU.ESTIMATED_REMAINING_EFFORT , PPRU.EQPMT_ETC_EFFORT , NULL , PPRU.SUBPRJ_PPL_ETC_EFFORT , PPRU.SUBPRJ_EQPMT_ETC_EFFORT , NULL , NULL , PA_PROGRESS_
UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE , PPRU.EQPMT_ACT_EFFORT_TO_DATE , NULL , PPRU.SUBPRJ_PPL_ACT_EFFORT , PPRU.SUBPRJ_EQPMT_ACT_EFFORT , NULL)) ETC_EFFORT , (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS, 0)+NVL(PFXAT.EQUIPMENT_HOURS, 0)) , PPRU.ESTIMATED_REMAINING_EFFORT, PPRU.EQPMT_ETC_EFFORT, NULL , PPRU.SUBPRJ_PPL_ETC_EFFORT, PPRU.SUBPRJ_EQPMT_ETC_EFFORT, NULL, NULL , (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT, 0)), 'WORKING')) ESTIMATE_AT_COMPLETION_EFFORT , ((NVL(PFXAT.BASE_LABOR_HOURS, 0)+NVL(PFXAT.BASE_EQUIP_HOURS, 0)) -(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS, 0)+NVL(PFXAT.EQUIPMENT_HOURS, 0)) , PPRU.ESTIMATED_REMAINING_EFFORT, PPRU.EQPMT_ETC_EFFORT, NULL , PPRU.SUBPRJ_PPL_ETC_EFFORT, PPRU.S
UBPRJ_EQPMT_ETC_EFFORT, NULL, NULL , (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT, 0)), 'WORKING'))) VARIANCE_AT_COMPLETION_EFFORT , ((PPRU.EARNED_VALUE)-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+ NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0))) , ROUND((((PPRU.EARNED_VALUE)-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+ NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)))/(DECODE(PPRU.EARNED_VALUE, 0, 1, PPRU.EARNED_VALUE))), 2) , PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_COST_TO_DATE_PC , PPRU.EQPMT_ACT_COST_TO_DATE_PC , PPRU.OTH_ACT_COST_TO_DATE_PC , NULL , NULL , NULL) ACTUAL_COST , PFXAT.PRJ_BASE_BRDN_COST BASELINE_COST , (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST , PPRU.PPL_ETC_COST_PC , PPRU.EQPMT_ETC_COST_PC , PPRU.OTH_ETC_COST_PC , PPRU.SUBPRJ_PPL_ETC_COST_PC, PPRU.SUBPRJ_EQPMT_ETC_COST_
PC , PPRU.SUBPRJ_OTH_ETC_COST_PC, NULL , (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC, 0)), 'WORKING')) ESTIMATE_AT_COMPLETION_COST , ((NVL(PPRU.EARNED_VALUE, 0))-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0))) , ROUND((((NVL(PPRU.EARNED_VALUE, 0))-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)))/(DECODE(NVL(PPRU.EARNED_VALUE, 0), 0, 1, NVL(PPRU.EARNED_VALUE, 0)))), 2) , ROUND((NVL(PPVSCH.WQ_PLANNED_QUANTITY, 0) - NVL(CUMULATIVE_WORK_QUANTITY, 0)), 5) ETC_WORK_QUANTITY , PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((NVL(PFXAT.PRJ_BRDN_COST, 0)/DECODE(NVL(CUMULATIVE_WORK_QUANTITY, 0), 0, 1, NVL(CUMULATIVE_WORK_QUANTITY, 0))), PPA.PROJECT_CURRENCY_CODE) PLANNED_COST_PER_UNIT ,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((NVL((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)), 0)/DECODE(NVL(PPRU.CUMULATIVE_WORK_QUANTITY, 0), 0, 1, PPRU.CUMULATIVE_WORK_QUANTITY)), PPA.PROJECT_CURRENCY_CODE) ACTUAL_COST_PER_UNIT , ROUND((NVL(NVL(PPRU.CUMULATIVE_WORK_QUANTITY, 0)-NVL(PPVSCH.WQ_PLANNED_QUANTITY, 0), 0)), 5) WORK_QUANTITY_VARIANCE , ROUND((((PPRU.CUMULATIVE_WORK_QUANTITY-PPVSCH.WQ_PLANNED_QUANTITY)/DECODE(NVL(PPVSCH.WQ_PLANNED_QUANTITY, 0), 0, 1, PPVSCH.WQ_PLANNED_QUANTITY))*100), 2) WORK_QUANTITY_VARIANCE_PERCENT , PPRU.EARNED_VALUE EARNED_VALUE , (NVL(PPRU.EARNED_VALUE, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID, PPRU.OBJECT_ID, PPV.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE, PPV.PARENT_STRUCTURE_VERSION_ID, PPPA.TASK_WEIGHT_BASIS_CODE, PPE.BASELINE_START_DATE, PPE.BASELINE_FINISH_DATE, PPA.PROJECT_CURRENCY_CODE), 0)) SCHEDULE_VARIANCE , (NVL(PPRU.EARNED_VALUE, 0)-NVL((NVL(PPRU.OTH_ACT_COST_TO_DATE_
PC, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)), 0)) EARNED_VALUE_COST_VARIANCE , (NVL(PPRU.EARNED_VALUE, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID, PPRU.OBJECT_ID, PPE.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE, PPV.PARENT_STRUCTURE_VERSION_ID, PPPA.TASK_WEIGHT_BASIS_CODE, PPE.BASELINE_START_DATE, PPE.BASELINE_FINISH_DATE, PPA.PROJECT_CURRENCY_CODE), 0)) EARNED_VALUE_SCHEDULE_VARIANCE , ((NVL(PFXAT.PRJ_BASE_BRDN_COST, 0)) -(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST , PPRU.PPL_ETC_COST_PC , PPRU.EQPMT_ETC_COST_PC , PPRU.OTH_ETC_COST_PC , PPRU.SUBPRJ_PPL_ETC_COST_PC, PPRU.SUBPRJ_EQPMT_ETC_COST_PC , PPRU.SUBPRJ_OTH_ETC_COST_PC, NULL , (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_P
C, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC, 0)), 'WORKING'))) VARIANCE_AT_COMPLETION_COST , ROUND( DECODE (PPRU.TASK_WT_BASIS_CODE, 'EFFORT', (((NVL(PFXAT.BASE_LABOR_HOURS, 0) +NVL(PFXAT.BASE_EQUIP_HOURS, 0))-PPRU.EARNED_VALUE)/DECODE(((NVL(PFXAT.BASE_LABOR_HOURS, 0)+NVL(PFXAT.BASE_EQUIP_HOURS, 0)) -(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)) ) , 0, 1, (NVL(PFXAT.BASE_LABOR_HOURS, 0)+NVL(PFXAT.BASE_EQUIP_HOURS, 0)) -(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)) ) ) , (NVL(PFXAT.PRJ_BASE_BRDN_COST, 0)-PPRU.EARNED_VALUE)/DECODE(NVL(PFXAT.PRJ_BASE_BRDN_COST, 0) -(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)) , 0, 1, NVL(PFXAT.PRJ_BASE_BRDN_COST, 0) -(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)) ) ) , 2) TO_COMPLETE_PERFORMANCE_INDEX , (NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID, PPRU.OBJE
CT_ID, PPE.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE, PPV.PARENT_STRUCTURE_VERSION_ID, PPPA.TASK_WEIGHT_BASIS_CODE, PPE.BASELINE_START_DATE, PPE.BASELINE_FINISH_DATE, PPA.PROJECT_CURRENCY_CODE), 0)) BUDGETED_COST_OF_WORK_SCH , ROUND((NVL(PPRU.EARNED_VALUE, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID, PPRU.OBJECT_ID, PPE.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE, PPV.PARENT_STRUCTURE_VERSION_ID, PPPA.TASK_WEIGHT_BASIS_CODE, PPE.BASELINE_START_DATE, PPE.BASELINE_FINISH_DATE, PPA.PROJECT_CURRENCY_CODE), 0), 0, 1, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID, PPRU.OBJECT_ID, PPE.PROJ_ELEMENT_ID, PPRU.AS_OF_DATE, PPV.PARENT_STRUCTURE_VERSION_ID, PPPA.TASK_WEIGHT_BASIS_CODE, PPE.BASELINE_START_DATE, PPE.BASELINE_FINISH_DATE, PPA.PROJECT_CURRENCY_CODE), 0))), 2) SCHEDULE_PERFORMANCE_INDEX , ROUND(DECODE(PPRU.TASK_WT_BASIS_CODE, 'EFFORT', (NVL(PPRU.EARNED_VALUE, 0)/DECODE((NVL(PPRU.PPL_ACT_EFFORT_TO_DATE, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)), 0, 1, (NVL(PPRU.PPL_ACT_EFFORT
_TO_DATE, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE, 0)))) , (NVL(PPRU.EARNED_VALUE, 0)/DECODE((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0)), 0, 1, (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC, 0))))), 2) COST_PERFORMANCE_INDEX , DECODE(PPA.STRUCTURE_SHARING_CODE, 'SPLIT_MAPPING', PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_ID(PPV.ELEMENT_VERSION_ID, PPA.STRUCTURE_SHARING_CODE)) MAPPED_FINANCIAL_TASK , PA_DELIVERABLE_UTILS.GET_ASSOCIATED_DELIVERABLES (PPE.PROJ_ELEMENT_ID) , PT.GEN_ETC_SOURCE_CODE , PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_TASK_LVL_ETC_SRC', PT.GEN_ETC_SOURCE_CODE) , PPE.WF_ITEM_TYPE , PPE.WF_PROCESS , PPE.WF_START_LEAD_DAYS , PPE.ENABLE_WF_FLAG , PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_NAME(PPV.ELEMENT_VERSION_ID, PPA.STRUCTURE_SHARING_CODE) , PA_PROGRESS_UTILS.CALC_ETC(PFXAT.PRJ_BRDN_COST , PPRU.PPL_ETC_COST_PC , PP
RU.EQPMT_ETC_COST_PC , PPRU.OTH_ETC_COST_PC , PPRU.SUBPRJ_PPL_ETC_COST_PC , PPRU.SUBPRJ_EQPMT_ETC_COST_PC , PPRU.SUBPRJ_OTH_ETC_COST_PC , NULL , PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_COST_TO_DATE_PC , PPRU.EQPMT_ACT_COST_TO_DATE_PC , PPRU.OTH_ACT_COST_TO_DATE_PC , PPRU.SUBPRJ_PPL_ACT_COST_PC , PPRU.SUBPRJ_EQPMT_ACT_COST_PC , PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC)) ETC_COST , PPRU.PROGRESS_ROLLUP_ID , PPRU.BASE_PERCENT_COMPLETE , NVL(PFXAT.LABOR_HOURS, 0)+NVL(PFXAT.EQUIPMENT_HOURS, 0) - (NVL(PFXAT.BASE_LABOR_HOURS, 0)+NVL(PFXAT.BASE_EQUIP_HOURS, 0)) PLANNED_BASELINE_EFFORT_VAR , NVL(PFXAT.PRJ_BRDN_COST, 0) - NVL(PFXAT.PRJ_BASE_BRDN_COST, 0) PLANNED_BASELINE_COST_VAR FROM PA_PROJ_ELEM_VER_STRUCTURE PPVS , HZ_CUST_ACCT_SITES_ALL S , HZ_PARTY_SITES PS , HZ_LOCATIONS ADDR , PA_PROJ_ELEM_VER_SCHEDULE PPVSCH , PER_ALL_PEOPLE_F PAPF , PA_PROJECT_STATUSES PPS2 , HR_ALL_ORGANIZATION_UNITS_TL HOU , PA_PROJECTS_ALL PPA , PA_PROJ_STRUCTURE_TYPES PPST , PA_STRUCTURE_TYPES PST , PA_WORK_T
YPES_TL PWT , PA_TASK_TYPES TT , PA_PROJECT_STATUSES PPS3 , PA_PAGE_LAYOUTS PPL , PA_PROGRESS_ROLLUP PPRU , PA_PROJECT_STATUSES PPS , PA_PROJECT_STATUSES PPS5 , PA_PROJ_ELEMENTS PPE5 , PA_PROJ_ELEMENT_VERSIONS PPV5 , PA_PROJ_WORKPLAN_ATTR PPWA , PA_PROJ_ELEMENT_VERSIONS PPEV6 , PA_PROJ_PROGRESS_ATTR PPPA , PA_PROJ_ELEMENT_VERSIONS PPV2 , PA_TASKS PT , PA_PROJ_ELEMENTS PPE , PA_PROJ_ELEMENT_VERSIONS PPV , PA_OBJECT_RELATIONSHIPS POR , PA_PROJ_ELEMENTS PPEPH , PA_PROJ_ELEMENT_VERSIONS PPEVPH , PJI_FM_XBS_ACCUM_TMP1 PFXAT WHERE PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID AND PPV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID AND PPV.PROJECT_ID = PPVS.PROJECT_ID AND PPVS.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND PPV.ELEMENT_VERSION_ID = PPVSCH.ELEMENT_VERSION_ID (+) AND PPV.PROJECT_ID = PPVSCH.PROJECT_ID (+) AND PPV.ELEMENT_VERSION_ID = POR.OBJECT_ID_TO1 AND POR.OBJECT_TYPE_TO IN ('PA_STRUCTURES', 'PA_TASKS') AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+) AND PPE.OBJECT_TYPE = 'P
A_TASKS' AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+) AND PAPF.EFFECTIVE_END_DATE (+) AND PPE.STATUS_CODE = PPS2.PROJECT_STATUS_CODE(+) AND PPE.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID (+) AND USERENV('LANG') = HOU.LANGUAGE (+) AND PPE.PROJECT_ID = PPA.PROJECT_ID AND POR.OBJECT_TYPE_FROM IN ('PA_STRUCTURES', 'PA_TASKS') AND POR.OBJECT_ID_FROM1 = PPV2.ELEMENT_VERSION_ID(+) AND PPE.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID(+) AND PST.STRUCTURE_TYPE_ID(+) = PPST.STRUCTURE_TYPE_ID AND POR.RELATIONSHIP_TYPE = 'S' AND (PPE.LINK_TASK_FLAG <> 'Y' OR PPE.TASK_STATUS IS NOT NULL) AND PPV.PROJ_ELEMENT_ID = PT.TASK_ID (+) AND PT.WORK_TYPE_ID = PWT.WORK_TYPE_ID (+) AND PWT.LANGUAGE (+) = USERENV('lang') AND TT.TASK_TYPE_ID = PPE.TYPE_ID AND TT.OBJECT_TYPE = 'PA_TASKS' AND PPE.STATUS_CODE = PPS3.PROJECT_STATUS_CODE (+) AND PPS3.STATUS_TYPE (+) = 'TASK' AND PT.ADDRESS_ID = S.CUST_ACCT_SITE_ID(+) AND PS.PARTY_SITE_ID(+) = S.PARTY_SITE_ID AND ADDR.LOCATION_ID(+) = PS.LOCATION_ID AND P
PE.TASK_PROGRESS_ENTRY_PAGE_ID = PPL.PAGE_ID (+) AND PPV.PROJECT_ID = PPRU.PROJECT_ID(+) AND PPV.PROJ_ELEMENT_ID = PPRU.OBJECT_ID(+) AND PPV.OBJECT_TYPE = PPRU.OBJECT_TYPE (+) AND PPRU.STRUCTURE_TYPE (+) = 'WORKPLAN' AND PPV.PARENT_STRUCTURE_VERSION_ID = PPRU.STRUCTURE_VERSION_ID (+) AND NVL(PPRU.PROGRESS_STATUS_CODE, PPRU.EFF_ROLLUP_PROG_STAT_CODE) = PPS.PROJECT_STATUS_CODE(+) AND 'PA_TASKS' = PPRU.OBJECT_TYPE (+) AND PPE.PHASE_VERSION_ID = PPV5.ELEMENT_VERSION_ID (+) AND PPV5.PROJ_ELEMENT_ID = PPE5.PROJ_ELEMENT_ID (+) AND PPE5.PHASE_CODE = PPS5.PROJECT_STATUS_CODE (+) AND PPE.PROJECT_ID <> 0 AND PPV.PARENT_STRUCTURE_VERSION_ID = PPEV6.ELEMENT_VERSION_ID (+) AND PPEV6.PROJ_ELEMENT_ID = PPWA.PROJ_ELEMENT_ID (+) AND PPEV6.PROJECT_ID = PPPA.PROJECT_ID (+) AND 'PA_STRUCTURES' = PPPA.OBJECT_TYPE (+) AND PPEV6.PROJ_ELEMENT_ID = PPPA.OBJECT_ID (+) AND PPWA.CURRENT_PHASE_VERSION_ID = PPEVPH.ELEMENT_VERSION_ID (+) AND PPEVPH.PROJ_ELEMENT_ID = PPEPH.PROJ_ELEMENT_ID (+) AND PFXAT.PROJECT_
ID (+)= PPV.PROJECT_ID AND PFXAT.PROJECT_ELEMENT_ID (+)=PPV.PROJ_ELEMENT_ID AND PFXAT.STRUCT_VERSION_ID (+)=PPV.PARENT_STRUCTURE_VERSION_ID AND PFXAT.CALENDAR_TYPE(+) = 'A' AND PFXAT.PLAN_VERSION_ID (+)> 0 AND PFXAT.TXN_CURRENCY_CODE(+) IS NULL AND PPPA.STRUCTURE_TYPE(+) = 'WORKPLAN' AND PPA.PROJECT_ID = :B2 AND PPV.PARENT_STRUCTURE_VERSION_ID = :B1 |
| gnmb6j8qadhmj |
begin hr_person_absence_swi.create_person_absence ( p_validate => :1 , p_effective_date => :2 , p_person_id => :3 , p_business_group_id => :4 , p_absence_attendance_type_id => :5 , p_abs_attendance_reason_id => :6 , p_comments => :7 , p_date_notification => :8 , p_date_projected_start => :9 , p_time_projected_start => :10 , p_date_projected_end => :11 , p_time_projected_end => :12 , p_date_start => :13 , p_time_start => :14 , p_date_end => :15 , p_time_end => :16 , p_absence_days => :17 , p_absence_hours => :18 , p_authorising_person_id => :19 , p_replacement_person_id => :20 , p_attribute_category => :21 , p_attribute1 => :22 , p_attribute2 => :23 , p_attribute3 => :24 , p_attribute4 => :25 , p_attribute5 => :26 , p_attribute6 => :27 , p_attribute7 => :28 , p_attribute8 => :29 , p_attribute9 => :30 , p_attribute10 => :31 , p_attribute11 => :32 , p_attribute12 => :33 , p_attribute13 =
> :34 , p_attribute14 => :35 , p_attribute15 => :36 , p_attribute16 => :37 , p_attribute17 => :38 , p_attribute18 => :39 , p_attribute19 => :40 , p_attribute20 => :41 , p_period_of_incapacity_id => :42 , p_ssp1_issued => :43 , p_maternity_id => :44 , p_sickness_start_date => :45 , p_sickness_end_date => :46 , p_pregnancy_related_illness => :47 , p_reason_for_notification_dela => :48 , p_accept_late_notification_fla => :49 , p_linked_absence_id => :50 , p_batch_id => :51 , p_create_element_entry => :52 , p_abs_information_category => :53 , p_abs_information1 => :54 , p_abs_information2 => :55 , p_abs_information3 => :56 , p_abs_information4 => :57 , p_abs_information5 => :58 , p_abs_information6 => :59 , p_abs_information7 => :60 , p_abs_information8 => :61 , p_abs_information9 => :62 , p_abs_information10 => :63 , p_abs_information11 => :64 , p_abs_information12 => :65 , p_abs_information13
=> :66 , p_abs_information14 => :67 , p_abs_information15 => :68 , p_abs_information16 => :69 , p_abs_information17 => :70 , p_abs_information18 => :71 , p_abs_information19 => :72 , p_abs_information20 => :73 , p_abs_information21 => :74 , p_abs_information22 => :75 , p_abs_information23 => :76 , p_abs_information24 => :77 , p_abs_information25 => :78 , p_abs_information26 => :79 , p_abs_information27 => :80 , p_abs_information28 => :81 , p_abs_information29 => :82 , p_abs_information30 => :83 , p_absence_attendance_id => :84 , p_object_version_number => :85 , p_occurrence => :86 , p_return_status => :87 , p_absence_case_id => :88 ); end; |