WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
VIS	210556022	VIS	1	10.2.0.3.0	NO	db1.oasb.cisco

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	9	09-Feb-11 10:44:46	102	26.4
End Snap:	15	09-Feb-11 10:49:12	103	27.2
Elapsed:		4.42 (mins)
DB Time:		24.58 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	22,400M	22,400M	Std Block Size:	8K
Shared Pool Size:	2,048M	2,048M	Log Buffer:	63,464K

Load Profile

	Per Second	Per Transaction
Redo size:	2,841,391.36	47,270.97
Logical reads:	791,659.23	13,170.48
Block changes:	18,913.40	314.65
Physical reads:	9.04	0.15
Physical writes:	3.27	0.05
User calls:	3,732.31	62.09
Parses:	2,948.30	49.05
Hard parses:	4.05	0.07
Sorts:	3,050.74	50.75
Logons:	0.78	0.01
Executes:	44,723.90	744.05
Transactions:	60.11

% Blocks changed per Read:	2.39	Recursive Call %:	95.71
Rollback per transaction %:	0.53	Rows per Sort:	6.42

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.95	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	100.20	Soft Parse %:	99.86
Execute to Parse %:	93.41	Latch Hit %:	94.43
Parse CPU to Parse Elapsd %:	67.19	% Non-Parse CPU:	99.22

Shared Pool Statistics

	Begin	End
Memory Usage %:	28.50	30.62
% SQL with executions>1:	74.68	93.72
% Memory for SQL w/exec>1:	77.62	91.71

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		1,436		97.3
db file sequential read	2,690	10	4	.7	User I/O
log file parallel write	13,963	6	0	.4	System I/O
log file sync	8,532	4	0	.3	Commit
latch: cache buffers chains	93,870	3	0	.2	Concurrency

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• init.ora Parameters

Wait Events Statistics

• Time Model Statistics
• Wait Class
• Wait Events
• Background Wait Events
• Operating System Statistics
• Service Statistics
• Service Wait Class Stats

Time Model Statistics

• Total time in database user-calls (DB Time): 1474.8s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Back to Wait Events Statistics
Back to Top

Wait Class

• s - second
• cs - centisecond - 100th of a second
• ms - millisecond - 1000th of a second
• us - microsecond - 1000000th of a second
• ordered by wait time desc, waits desc

Back to Wait Events Statistics
Back to Top

Wait Events

• s - second
• cs - centisecond - 100th of a second
• ms - millisecond - 1000th of a second
• us - microsecond - 1000000th of a second
• ordered by wait time desc, waits desc (idle events last)

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in centisecond (100th of a second)

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 209,963,860
• Captured SQL account for 107.4% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• Total Disk Reads: 2,397
• Captured SQL account for 40.8% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• Total Executions: 11,861,672
• Captured SQL account for 80.5% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 781,948
• Captured SQL account for 89.6% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
00s9ssyu9pwhw	select count(*), count(*), NULL from dba_aws where AW_NUMBER >= 1000
02dvcwb36048p	UPDATE PAY_ACTION_INFORMATION SET ACTION_INFORMATION_ID = :B1 , OBJECT_VERSION_NUMBER = :B32 , ACTION_INFORMATION1 = :B31 , ACTION_INFORMATION2 = :B30 , ACTION_INFORMATION3 = :B29 , ACTION_INFORMATION4 = :B28 , ACTION_INFORMATION5 = :B27 , ACTION_INFORMATION6 = :B26 , ACTION_INFORMATION7 = :B25 , ACTION_INFORMATION8 = :B24 , ACTION_INFORMATION9 = :B23 , ACTION_INFORMATION10 = :B22 , ACTION_INFORMATION11 = :B21 , ACTION_INFORMATION12 = :B20 , ACTION_INFORMATION13 = :B19 , ACTION_INFORMATION14 = :B18 , ACTION_INFORMATION15 = :B17 , ACTION_INFORMATION16 = :B16 , ACTION_INFORMATION17 = :B15 , ACTION_INFORMATION18 = :B14 , ACTION_INFORMATION19 = :B13 , ACTION_INFORMATION20 = :B12 , ACTION_INFORMATION21 = :B11 , ACTION_INFORMATION22 = :B10 , ACTION_INFORMATION23 = :B9 , ACTION_INFORMATION24 = :B8 , ACTION_INFORMATION25 = :B7 , ACTION_INFORMATION26 = :B6 , ACTION_INFORMATION27 = :B5 , ACTION_INFORMATION28 = :B4 , ACTION_INFORMATION29 = :B3 , ACTION_INFORMATION30 = :B2 WHERE ACTION_INFORMATION_ID = :B1
02k10jzw8c495	SELECT INTLK.LOCKING_ACTION_ID FROM PAY_ACTION_INTERLOCKS INTLK, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE INTLK.LOCKED_ACTION_ID = :B1 AND INTLK.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('P', 'U') AND PAA.SOURCE_ACTION_ID IS NULL
05s9358mm6vrr	begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
165d2bgc2b9hr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment within a Government Reporting Entity for YTD based on run balances */ pay_run_balances prb, pay_defined_balances pdb, pay_payroll_actions ppa, pay_assignment_actions paa where paa.assignment_action_id = :ASSIGNMENT_ACTION_ID and prb.tax_unit_id = :TAX_UNIT_ID and ppa.payroll_action_id = paa.payroll_action_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id and paa.assignment_id = prb.assignment_id and prb.action_sequence <= paa.action_sequence and prb.effective_date >= trunc(ppa.effective_date, 'Y')
1krdnbh743rc6	DECLARE l_APPLICATION VARCHAR2(240); l_PROGRAM VARCHAR2(240); l_DESCRIPTION VARCHAR2(240); l_START_TIME VARCHAR2(240); l_SUB_REQUEST BOOLEAN := FALSE; l_ARGUMENT1 VARCHAR2(240); l_ARGUMENT2 VARCHAR2(240); l_ARGUMENT3 VARCHAR2(240); l_ARGUMENT4 VARCHAR2(240); l_ARGUMENT5 VARCHAR2(240); l_ARGUMENT6 VARCHAR2(240); l_ARGUMENT7 VARCHAR2(240); l_ARGUMENT8 VARCHAR2(240); l_ARGUMENT9 VARCHAR2(240); l_ARGUMENT10 VARCHAR2(240); l_ARGUMENT11 VARCHAR2(240); l_ARGUMENT12 VARCHAR2(240); l_payroll_name VARCHAR2(240):= '58Semi-Month'; l_user_name VARCHAR2(240):= '58BM_USER'; l_responsibility_name VARCHAR2(240):= '58BM_US SHRMS Manager'; l_start_date VARCHAR2(240):= '2007/09/01'; l_end_date VARCHAR2(240):= '2007/09/15'; l_threads VARCHAR2(240):= '24'; l_success BOOLEAN; l_boolean BOOLEAN; l_req_id number; l_snap_id number; l_errnum number; l_err VARCHAR2(2000); concurrent_program_failure EXCEPTION; terminate_program EXCEPTION; --Cursor to get Payroll Id CURSOR c_payroll_id(p_payroll_name IN VARCHAR2) IS SELECT payroll_id, business_group_id FROM pay_all_payrolls_f WHERE payroll_name = p_payroll_name; l_payroll_id pay_all_payrolls.payroll_id%TYPE; l_business_group_id pay_all_payrolls.business_group_id%TYPE; invalid_payroll_id EXCEPTION; --Cursor to get consolidation set id CURSOR c_consolidation_set_id(p_payroll_id IN NUMBER) IS SELECT c.consolidation_set_id FROM pay_consolidation_sets c , pay_all_payrolls_f p WHERE p.consolidation_set_id = c.consolidation_set_id AND p.payroll_id = p_payroll_id; l_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE; invalid_consolidation_set_id EXCEPTION; CURSOR c_run_type_id IS SELECT run_type_id FROM pay_run_types_f WHERE legislation_code='US' AND run_method = 'C' AND shortname='REGULAR'; l_run_type_id pay_run_types_f.run_type_id%TYPE; invalid_run_type_id EXCEPTION; --Cursor to get User ID CURSOR c_user_id(p_user_name IN VARCHAR2) IS SELECT user_id FROM fnd_user WHERE user_name = p_user_name; l_user_id fnd_user.user_id%TYPE; invalid_user EXCEPTION; --Cursor to get responsibility id CURSOR c_resp_id(p_responsibility_name IN VARCHAR2) IS SELECT responsibility_id, application_id FROM fnd_responsibility_tl WHERE responsibility_name = p_responsibility_name; l_application_id fnd_responsibility_tl.application_id%TYPE; l_responsibility_id fnd_responsibility_tl.responsibility_id%TYPE; invalid_responsibility EXCEPTION; --Cursor to get Org Payment Type id for NACHA(MT)/Check(CH) CURSOR c_payment_type_id(p_payment_type_name IN VARCHAR2, p_category IN VARCHAR2) IS SELECT payment_type_id FROM pay_payment_types WHERE territory_code = 'US' AND category = p_category AND payment_type_name = p_payment_type_name; l_payment_type_id pay_payme nt_types.payment_type_id%TYPE; invalid_payment_type_id EXCEPTION; --Cursor to get Org Payment Method for the NACHA, Business Group and Payroll CURSOR c_org_pay_method_id(p_payment_type_id IN NUMBER , p_business_group_id IN NUMBER , p_payroll_id IN NUMBER) IS SELECT popmf.org_payment_method_id FROM pay_org_payment_methods_f popmf , pay_org_pay_method_usages_f popmuf WHERE payment_type_id = p_payment_type_id AND business_group_id = p_business_group_id AND popmf.org_payment_method_id = popmuf.org_payment_method_id AND popmuf.payroll_id = p_payroll_id; l_org_payment_method_id pay_org_payment_methods_f.org_payment_method_id%TYPE; invalid_org_payment_method_id EXCEPTION; --Cursor to get Org Payment Method for the NACHA, Business Group, Payroll and Check CURSOR c_org_pay_method_id1(p_payment_type_id IN NUMBER , p_business_group_id IN NUMBER , p_payroll_id IN NUMBER , p_org_payment_method_name IN VARCHAR2) IS SELECT popmf.org_payment_method_id FROM pay_org_payment_methods_f popmf , pay_org_pay_method_usages_f popmuf WHERE payment_type_id = p_payment_type_id AND business_group_id = p_business_group_id AND popmf.org_payment_method_id = popmuf.org_payment_method_id AND popmuf.payroll_id = p_payroll_id AND popmf.org_payment_method_name = p_org_payment_method_name; l_org_payment_method_id1 pay_org_payment_methods_f.org_payment_method_id%TYPE; invalid_org_payment_method_id1 EXCEPTION; CURSOR c_payroll(p_reques t_id IN NUMBER) IS SELECT action_status FROM pay_payroll_actions WHERE request_id = p_request_id; l_action_status pay_payroll_actions.action_status%TYPE; PROCEDURE get_status(p_request_id IN NUMBER) IS CURSOR c_payroll(p_request_id IN NUMBER) IS SELECT action_status FROM pay_payroll_actions WHERE request_id = p_request_id; l_action_status pay_payroll_actions.action_status%TYPE; data_found exception; BEGIN -- dbms_output.put_line('begin get_status ...'); OPEN c_payroll(p_request_id); FETCH c_payroll INTO l_action_status; IF c_payroll%NOTFOUND THEN l_action_status := NULL; END IF; CLOSE c_payroll; IF l_action_status NOT IN ('C', 'E') OR l_action_status IS NULL THEN -- dbms_output.put_line('sleeping in get_status ...'); dbms_lock.sleep(10); get_status(p_request_id); ELSE dbms_output.put_line('l_action_status='||l_action_status); raise data_found; -- dbms_output.put_line('raise data_found ...'); END IF; EXCEPTION WHEN data_found THEN -- dbms_output.put_line('step complete ...'); -- dbms_output.put_line('data_found exception ...'); NULL; -- dbms_output.put_line('end get_status ...'); END get_status; BEGIN dbms_output.put_line('BEGIN submitpayroll.sql script ...'); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for submitpayroll.sql script -> '||to_char(l_snap_id)); OPEN c_user_id(l_user_name); FETCH c_user_id INTO l_user_id; IF c_user_id%NOTFOUND THEN RAISE invalid_user; END IF; CLOSE c_user_id; OPEN c_resp_id(l_responsibility_name); FETCH c_resp_id INTO l_responsibility_id, l_application_id; IF c_resp_id%NOTFOUND THEN RAISE invalid_responsibility; END IF; CLOSE c_resp_id; OPEN c_payroll_id(l_payroll_name); FETCH c_payroll_id INTO l_payroll_id, l_business_group_id; IF c_payroll_id%NOTFOUND THEN RAISE invalid_payroll_id; END IF; CLOSE c_payroll_id; OPEN c_consolidation_set_id(l_payroll_id); FETCH c_consolidation_set_id INTO l_consolidation_set_id; IF c_consolidation_set_id%NOTFOUND THEN RAISE invalid_consolidation_set_id; END IF; OPEN c_run_type_id; FETCH c_run_type_id INTO l_run_type_id; IF c_run_type_id%NOTFOUND THEN RAISE invalid_run_type_id; END IF; CLOSE c_run_type_id; --Initialize for concurrent program fnd_global.apps_initialize( user_id => l_user_id , resp_id => l_responsibility_id , resp_appl_id => l_application_id); --Start of Submit Payroll Process l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'RUNGEN' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'RUN' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date --Date Earned , ARGUMENT5 => l_start_date --Date Paid , ARGUMENT6 => l_ARGUMENT6 --Assignment Set , ARGUMENT7 => l_ARGUMENT7 --Element Set , ARGUMENT8 => l_run_type_id , ARGUMENT9 => l_ARGUMENT9 , ARGUMENT10 => l_ARGUMENT10 ); dbms_output.put_line('Request ID for Payroll Process -> '||to_char(l_req_id)); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for Payroll Step -> '||to_char(l_snap_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 1 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 2 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Payroll Step -> '||to_char(l_snap_id)); --End of Submit Payroll Process --Start of PrePayments l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' --l_APPLICATION , PROGRAM => 'PREPAY' --l_PROGRAM , DESCRIPTION => 'Test' --l_DESCRIPTION , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'PREPAY' --Process Name , ARGUMENT2 => l_payroll_id --Payroll , ARGUMENT3 => l_consolidation_set_id --Consolidation Set , ARGUMENT4 => l_start_date --Start Date , ARGUMENT5 => l_end_date --End Date , ARGUMENT6 => NULL --Payment Method Override , ARGUMENT7 => NULL --Action Parameter Group ); dbms_output.put_line('Request ID for PrePayments-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 2 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 3 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for PrePayments Step -> '||to_char(l_snap_id)); --End of PrePayments --Start of External Process Archive l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'PAYUSXFR' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_ REQUEST , ARGUMENT1 => 'ARCHIVE' , ARGUMENT2 => 'XFR_INTERFACE' , ARGUMENT3 => 'FED' , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => 'RT' , ARGUMENT7 => l_business_group_id , ARGUMENT8 => 'XFR_INTERFACE' , ARGUMENT9 => 'XFR_INTERFACE' , ARGUMENT10 => l_payroll_id , ARGUMENT11 => l_consolidation_set_id , ARGUMENT12 => '1' , ARGUMENT13 => 'TRANSFER_PAYROLL_ID='||l_payroll_id||'' , ARGUMENT14 => 'TRANSFER_CONSOLIDATION_SET_ID='||l_consolidation_set_id||'' ); dbms_output.put_line('Request ID for external process archive -> '||to_char(l_req_id)); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for Archive Step -> '||to_char(l_snap_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 3 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; get_status(l_req_id); --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Archive Step -> '||to_char(l_snap_id)); --End of External Process Archive --Start of NACHA OPEN c_payment_type_id('NACHA', 'MT '); FETCH c_payment_type_id INTO l_payment_type_id; IF c_payment_type_id%NOTFOUND THEN RAISE invalid_payment_type_id; END IF; CLOSE c_payment_type_id; OPEN c_org_pay_method_id(l_payment_type_id, l_business_group_id, l_payroll_id); FETCH c_org_pay_method_id INTO l_org_payment_method_id; CLOSE c_org_pay_method_id; l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'NACHA' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'MAGTAPE' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => l_payment_type_id --Payment Type Name , ARGUMENT7 => l_org_payment_method_id --Org Pay Method Id , ARGUMENT8 => NULL , ARGUMENT9 => '0' , ARGUMENT10 => '1' , ARGUMENT11 => '1' , ARGUMENT12 => 'FILE_ID_MODIFIER=0' , ARGUMENT13 => NULL , ARGUMENT14 => NULL , ARGUMENT15 => NULL ); dbms_output.put_line('Request ID for nacha-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 4 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 4 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for NACHA Step -> '||to_char(l_snap_id)); --End of NACHA --Start of Check Writer OPEN c_payment_type_id('Check', 'CH'); FETCH c_payment_type_id INTO l_payment_type_id; IF c_payment_type_id%NOTFOUND THEN RAISE invalid_payment_type_id; END IF; CLOSE c_payment_type_id; OPEN c_org_pay_method_id1(l_payment_type_id, l_business_group_id, l_payroll_id, 'Check'); FETCH c_org_pay_method_id1 INTO l_org_payment_method_id1; CLOSE c_org_pay_method_id1; l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'CHECK_WRITER' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'CHEQUE' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => l_payment_type_id , ARGUMENT7 => l_org_payment_method_id1 , ARGUMENT8 => 'DEFAULT' , ARGUMENT9 => 'PAYUSCDA' , ARGUMENT10 => '1' , ARGUMENT11 => NULL , ARGUMENT12 => NULL , ARGUMENT13 => NULL , ARGUMENT14 => NULL ); dbms_output.put_line('Re quest ID for check writer-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 5 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 5 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Check Writer Step -> '||to_char(l_snap_id)); --End of Check Writer --Start of Costing l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'COSTING' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'COSTING' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => NULL ); dbms_output.put_line('Request ID for costing-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 6 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 6 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('END Snap ID for submitpayroll.sql script -> '||to_char(l_snap_id)); dbms_output.put_line('END submitpayroll.sql script ...'); --End of Costing --Check for exceptions EXCEPTION WHEN terminate_program THEN dbms_output.put_line ('terminate_program exception encountered') ; WHEN concurrent_program_failure THEN dbms_output.put_line ('concurrent_program_failure exception encountered') ; WHEN invalid_user THEN dbms_output.put_line ('Invalid User ID supplied') ; WHEN invalid_responsibility THEN dbms_output.put_line ('Invalid Responsibility supplied') ; WHEN invalid_payroll_id THEN dbms_output.put_line ('Invalid Payroll ID supplied') ; WHEN invalid_consolidation_set_id THEN dbms_output.put_line ('Invalid consolidation SET ID supplied') ; WHEN invalid_run_type_id THEN dbms_output.put_line ('Invalid Run Type supplied') ; WHEN OTHERS THEN BEGIN dbms_output.put_line('Unknown EXCEPTION encountered ...'); l_errnum := SQLCODE; dbms_output.put_line(l_e rrnum); END ; IF c_payroll%ISOPEN THEN CLOSE c_payroll; END IF; END;
1m7zxctxm9bhq	select decode(cap + app + prop + msg + aq , 0, 0, 1), 0, NULL from (select decode (count(*), 0, 0, 1) cap from dba_capture), (select decode (count(*), 0, 0, 1) app from dba_apply), (select decode (count(*), 0, 0, 1) prop from dba_propagation), (select decode (count(*), 0, 0, 1) msg from dba_streams_message_consumers where streams_name != 'SCHEDULER_COORDINATOR' and streams_name != 'SCHEDULER_PICKUP'), (select decode (count(*), 0, 0, 1) aq from system.aq$_queue_tables)
264qzrka8fsps	SELECT NVL(SUM(prb.balance_value), 0) FROM /* US Assignment for Run based on run balances */ pay_run_balances prb, pay_defined_balances pdb where prb.assignment_action_id = :ASSIGNMENT_ACTION_ID and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id
2yjb88xutjfga	update pay_assignment_actions set action_status='C' where assignment_action_id=:b0
36z3h9rgkd8aa	begin hrassact.update_action_sequence (:aa_id, :itpflg); end;
3n5uwzsh3y8gr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N'), 'S ', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2
3rg5fyfgd5uwc	begin pay_US_rules.get_time_def_for_entry( :element_entry_id, :assignment_id, :assignment_action_id, :business_group_id, :time_def_id); end;
43w0r9122v7jm	select max(bytes) from dba_segments
4ds53shhfcynn	declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4f5mjr4hnaz3u	declare begin pay_archive.process_employee(:asg_action); end;
4gz4b2bh1fs14	SELECT CONTEXT_ID, SEQUENCE_NO FROM FF_ROUTE_CONTEXT_USAGES WHERE ROUTE_ID = :B1 ORDER BY SEQUENCE_NO
4pszqmyjx91xc	select tax_unit_id into :b0:b1 from pay_assignment_actions where assignment_action_id=:b2
5m04808f1ytsw	SELECT NVL(PRT.SHORTNAME, PRA.LEGISLATIVE_PARAMETERS), 'FF ROUTE:RUNTYPE' FROM PAY_PAYROLL_ACTIONS PRA, PAY_ASSIGNMENT_ACTIONS PAA, PAY_RUN_TYPES_F PRT WHERE PRA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = :B1 AND NVL(PAA.RUN_TYPE_ID, -1) = PRT.RUN_TYPE_ID (+)
5p88n2zhuqfd9	SELECT PPA.EFFECTIVE_DATE, PPA.BUSINESS_GROUP_ID, PPA.PAYROLL_ID, PPA.ACTION_TYPE FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA WHERE PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
5rxbazwmcdfaz	insert into sys.wri$_optstat_histhead_history (obj#, intcol#, savtime, flags) values (:1, :2, :3, 0)
5sfm6rppc4y8x	BEGIN pay_us_action_arch.action_archive_data(:aactid, :l_effective_date); END;
5ts0t88sz97xs	SELECT PDB.DEFINED_BALANCE_ID FROM PAY_DEFINED_BALANCES PDB, PAY_BALANCE_DIMENSIONS PBD WHERE PDB.BALANCE_TYPE_ID = :B3 AND PBD.DATABASE_ITEM_SUFFIX = :B2 AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID AND ((PBD.LEGISLATION_CODE = :B1 AND PBD.BUSINESS_GROUP_ID IS NULL) OR (PBD.LEGISLATION_CODE IS NULL AND PBD.BUSINESS_GROUP_ID IS NOT NULL))
5w5up6vdp2qyp	SELECT RP.SEQUENCE_NO, RPV.VALUE FROM FF_ROUTE_PARAMETER_VALUES RPV , FF_ROUTE_PARAMETERS RP WHERE RPV.USER_ENTITY_ID = :B2 AND RP.ROUTE_ID = :B1 AND RPV.ROUTE_PARAMETER_ID = RP.ROUTE_PARAMETER_ID ORDER BY RP.SEQUENCE_NO
6500rnmy4f88w	SELECT PAP.ACCRUAL_PLAN_ID, PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID FROM PAY_ACCRUAL_PLANS PAP, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_CATEGORY = :B3 AND PEL.ELEMENT_TYPE_ID= PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEE.ASSIGNMENT_ID = :B2 AND :B1 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
6gpzh0p0vbvq9	SELECT 'x' FROM HR_LEGISLATION_INSTALLATIONS WHERE :B2 =APPLICATION_SHORT_NAME AND NVL(:B1 , 'x')=NVL(LEGISLATION_CODE, 'x') AND (STATUS='I' OR ACTION IS NOT NULL)
6v07302134wyf	INSERT INTO PAY_ACTION_INFORMATION (ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 ) VALUES (:B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
77d28bpwm9svg	SELECT PBV.RUN_BALANCE_STATUS, PBV.BALANCE_LOAD_DATE FROM PAY_BALANCE_VALIDATION PBV WHERE PBV.DEFINED_BALANCE_ID = :B2 AND PBV.BUSINESS_GROUP_ID = :B1
7d66dw0vz7yaa	begin pay_cc_process_utils . reset_dates_for_run ( p_asg_id => :v_asg_id , p_sysdate => to_date ( :v_sysdate , :date_format ) , p_assact_id => :v_assact_id ) ; end ;
7mmdd7sypzu9x	BEGIN FFP1893_01010001.FORMULA( :N1, :N2, :T1, :I1, :D1, :T2, :I2, :T7, :I7, :T8, :I8, :T5, :I5, :N5, :T4, :I4, :N4, :N3, :T3, :I3, :T6, :I6, :FFERLN, :FFERCD, :FFERMT); END;
80wvw6gatjvb2	SELECT PTP_TD.START_DATE FROM PER_TIME_PERIODS PTP_TD, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP_PPA WHERE PTP_TD.TIME_DEFINITION_ID = :B2 AND PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.PAYROLL_ID = PTP_PPA.PAYROLL_ID AND PPA.DATE_EARNED BETWEEN PTP_PPA.START_DATE AND PTP_PPA.END_DATE AND PTP_TD.START_DATE > PTP_PPA.START_DATE AND PTP_TD.START_DATE <= PTP_PPA.END_DATE
83kbt99vg4svq	begin hr_nonrun_asact.reinterlock (:assactid, :pactype); end;
8a01qnnc9as1r	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment in JD Government Reporting Entity for YTD based on run balances */ pay_balance_types pbt, pay_run_balances prb, pay_defined_balances pdb, pay_payroll_actions ppa, pay_assignment_actions paa where paa.assignment_action_id = :ASSIGNMENT_ACTION_ID and prb.tax_unit_id = :TAX_UNIT_ID and ppa.payroll_action_id = paa.payroll_action_id and pbt.balance_type_id = :U1 and pdb.balance_type_id = pbt.balance_type_id and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id and paa.assignment_id = prb.assignment_id and prb.action_sequence <= paa.action_sequence and prb.effective_date >= trunc(ppa.effective_date, 'Y') and substr(prb.jurisdiction_code, 1, pbt.jurisdiction_level) = substr(:JURISDICTION_CODE, 1, pbt.jurisdiction_level)
95mpkn5xz9001	insert into hist_head$(obj#, intcol#, bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, spare1, spare2, avgcln, col#) values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18)
97mt0g20bcfc3	begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9jv68n0ctas75	begin pay_core_utils . get_prorated_dates ( :ee_id , :asg_act_id , :time_def_type , :time_def_id:time_def_ind , :pro_dates:prod_ind , :pro_types:prot_ind ) ; END ;
9wa06dzuu5g37	select count(*), count(*), NULL from DBA_OLAP2_CUBES where invalid != 'Y' and OWNER = 'SYS' and CUBE_NAME = 'STKPRICE_TBL'
a9pf25b5p69c6	declare cursor dp ( p_act in number ) is select distinct paf2 . assignment_id , paa . action_sequence from pay_assignment_actions paa , pay_payroll_actions ppa , per_all_assignments_f paf , per_periods_of_service pos , per_all_assignments_f paf2 where paa . assignment_action_id = p_act and paa . payroll_action_id = ppa . payroll_action_id and paf . assignment_id = paa . assignment_id and ppa . effective_date between paf . effective_start_date and paf . effective_end_date and paf . person_id = pos . person_id and pos . period_of_service_id = paf2 . period_of_service_id ; BEGIN for dprec in dp ( :assactid ) loop begin select 1 into :act_exists from sys . dual where exists ( select null from pay_assignment_actions paa , pay_payroll_actions ppa , pay_action_classifications pcl where paa . assignment_id = dprec . assignment_id and paa . payroll_action_id = ppa . payroll_action_id and ppa . action_type = pcl . action_type and pcl . classification_name = 'SEQUENCED' and paa . action_status not in ( 'C' , 'S' , 'B' ) and paa . action_sequence < dprec . action_sequence ) ; exception when no_data_found then null ; end ; end loop ; END ;
b92u4gf9av6ky	begin dbms_stats.gather_table_stats('SYS', :bind1, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE); end;
bm8gwn43tn8hd	SELECT PLB.LATEST_BALANCE_ID, PLB.ASSIGNMENT_ACTION_ID, PLB.VALUE, PLB.EXPIRY_DATE, ACT.ACTION_SEQUENCE CURRENT_ACTION_SEQ, OWN_ACT.ACTION_SEQUENCE OWNER_ACTION_SEQ, PACT.EFFECTIVE_DATE CURRENT_EFFECTIVE_DATE, PLB.EXPIRED_ASSIGNMENT_ACTION_ID, PLB.EXPIRED_VALUE, PLB.EXPIRED_DATE, PLB.PREV_ASSIGNMENT_ACTION_ID, PLB.PREV_BALANCE_VALUE, PLB.PREV_EXPIRY_DATE, PLB.TAX_UNIT_ID, PLB.JURISDICTION_CODE, PLB.ORIGINAL_ENTRY_ID, PLB.SOURCE_ID, PLB.SOURCE_TEXT, PLB.SOURCE_TEXT2, PLB.SOURCE_NUMBER, PLB.TAX_GROUP, PLB.PAYROLL_ID, PLB.LOCAL_UNIT_ID, PLB.ORGANIZATION_ID, PLB.SOURCE_NUMBER2 FROM PAY_LATEST_BALANCES PLB , PER_ALL_ASSIGNMENTS_F ASSIGN , PAY_ASSIGNMENT_ACTIONS ACT , PAY_PAYROLL_ACTIONS PACT , PAY_ASSIGNMENT_ACTIONS OWN_ACT WHERE PLB.DEFINED_BALANCE_ID = :B15 AND ACT.ASSIGNMENT_ACTION_ID = :B14 AND ASSIGN.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND EXISTS (SELECT '' FROM PAY_OBJECT_GROUPS POG WHERE POG.SOURCE_ID (+) = ACT.ASSIGNMENT_ID AND POG.SOURCE_TYPE (+) = 'PAF' AND NVL(PLB.PROCESS_GROUP_ID, NVL(POG.PARENT_OBJECT_GROUP_ID, -1)) = NVL(POG.PARENT_OBJECT_GROUP_ID, -1) ) AND PLB.PERSON_ID = ASSIGN.PERSON_ID AND NVL(PLB.ASSIGNMENT_ID, ACT.ASSIGNMENT_ID) = ACT.ASSIGNMENT_ID AND PACT.PAYROLL_ACTION_ID = ACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN ASSIGN.EFFECTIVE_START_DATE AND ASSIGN.EFFECTIVE_END_DATE AND OWN_ACT.ASSIGNMENT_ACTION_ID = PLB.ASSIGNMENT_ACTION_ID AND NVL(PLB.TAX_UNIT_ID, NVL(:B13 , -1)) = NVL(:B13 , -1) AND NVL(SUBSTR(NVL(PLB.JUR ISDICTION_CODE, NVL(:B12 , -1)), 1, :B11 ), -1) = NVL( SUBSTR(NVL(:B12 , -1), 1, :B11 ), -1) AND NVL(PLB.ORIGINAL_ENTRY_ID, NVL(:B10 , -1)) = NVL(:B10 , -1) AND NVL(PLB.SOURCE_ID, NVL(:B9 , -1)) = NVL(:B9 , -1) AND NVL(PLB.SOURCE_TEXT, NVL(:B8 , -1)) = NVL(:B8 , -1) AND NVL(PLB.SOURCE_TEXT2, NVL(:B7 , -1)) = NVL(:B7 , -1) AND NVL(PLB.SOURCE_NUMBER, NVL(:B6 , -1)) = NVL(:B6 , -1) AND NVL(PLB.TAX_GROUP, NVL(:B5 , -1)) = NVL(:B5 , -1) AND NVL(PLB.PAYROLL_ID, NVL(:B4 , -1)) = NVL(:B4 , -1) AND NVL(PLB.LOCAL_UNIT_ID, NVL(:B3 , -1)) = NVL(:B3 , -1) AND NVL(PLB.ORGANIZATION_ID, NVL(:B2 , -1)) = NVL(:B2 , -1) AND NVL(PLB.SOURCE_NUMBER2, NVL(:B1 , -1)) = NVL(:B1 , -1) ORDER BY PLB.LATEST_BALANCE_ID
cj64zg9gg7780	SELECT ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 FROM PAY_ACTION_INFORMATION WHERE ACTION_INFORMATION_ID = :B1 FOR UPDATE NOWAIT
cn5un9cxnah8w	SELECT NVL(PBD.PERIOD_TYPE, 'NULL'), PBD.START_DATE_CODE FROM PAY_BALANCE_DIMENSIONS PBD, PAY_DEFINED_BALANCES PDB WHERE PDB.DEFINED_BALANCE_ID = :B1 AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
cr74qtm56vcq6	BEGIN PAY_US_NACHA_TAPE.RUN_FORMULA; END;
d7shgtk3p7s4u	SELECT /*+ ordered index(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(BACT PAY_PAYROLL_ACTIONS_PK) index(ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PACT PAY_PAYROLL_ACTIONS_PK) index(INTLCK2 PAY_ACTION_INTERLOCKS_FK1) index(PPASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PPACT PAY_PAYROLL_ACTIONS_PK) index(RR PAY_RUN_RESULTS_N50) index(TARGET PAY_RUN_RESULT_VALUES_N50) index(FEED PAY_BALANCE_FEEDS_F_N50) */ fnd_number.number_to_canonical(/*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0) ) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_assignment_actions ASSACT , pay_payroll_actions PACT , pay_run_results RR , pay_run_result_values TARGET , pay_balance_feeds_f FEED where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action _id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and nvl(PACT.future_process_mode, 'Y') = 'Y' and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and pay_core_utils.include_action_in_payment(BACT.action_type, BAL_ASSACT.assignment_action_id, ASSACT.assignment_action_id) = 'Y' and NOT EXISTS (SELECT /*+ NO_PUSH_SUBQ */ NULL FROM pay_payroll_actions RPACT , pay_assignment_actions RASSACT , pay_action_interlocks RINTLK where ASSACT.assignment_action_id = RINTLK.locked_action_id and RINTLK.locking_action_id = RASSACT.assignment_action_id and RPACT.payroll_action_id = RASSACT.payroll_action_id and RPACT.action_type = 'V')
dab1prc9qyv3v	begin hr_nonrun_asact.asact(:pactid, :atype, :itpflg, :ptype, :lub, :lul, :use_pop_person); end;
f78ff26qcfjan	update PAY_ASSIGNMENT_ACTIONS set ACTION_STATUS='C' where (rowid=:b0 and ACTION_STATUS<>'S')
fdv98jawj03hg	SELECT FUE.USER_ENTITY_NAME , NVL(FUE.BUSINESS_GROUP_ID, -1) , NVL(FUE.LEGISLATION_CODE, ' ') FROM FF_USER_ENTITIES FUE , FF_USER_ENTITIES FUE_B WHERE FUE.CREATOR_ID = :B2 AND FUE_B.CREATOR_TYPE = 'B' AND FUE.CREATOR_ID = FUE_B.CREATOR_ID AND FUE.CREATOR_TYPE = 'RB' AND FUE.USER_ENTITY_NAME = FUE_B.USER_ENTITY_NAME||'_'||TO_CHAR(:B1 )
fh5m86a7xvv2k	SELECT 1 FROM PAY_UPGRADE_LEGISLATIONS PUL WHERE PUL.UPGRADE_DEFINITION_ID = :B2 AND PUL.LEGISLATION_CODE = :B1
fmysjzxwxjuwj	BEGIN DBMS_FEATURE_PARTITION_SYSTEM(:feature_boolean, :aux_cnt, :feature_info); END;
fnnymj5r1f0n8	SELECT PBG.LEGISLATION_CODE FROM PER_BUSINESS_GROUPS PBG WHERE PBG.BUSINESS_GROUP_ID = :B1
g3835fnqtvy06	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_balance_types pbt , pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N' ), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_dimension_id = :U2 and pbt.balance_type_id = :U1 and pdb.balance_type_id = pbt.balance_type_id and substr(prb.jurisdiction_code, 1, pbt.jurisdiction_level) = substr(:JURISDICTION_CODE, 1, pbt.jurisdiction_level)
g6tx1p7d69dxg	SELECT ENT.USER_ENTITY_ID, ENT.CREATOR_TYPE, ENT.NOTFOUND_ALLOWED_FLAG, RO.ROUTE_ID, RO.TEXT, RO.OPTIMIZER_HINT FROM FF_USER_ENTITIES ENT , FF_ROUTES RO WHERE ENT.USER_ENTITY_NAME = :B3 AND ( (ENT.LEGISLATION_CODE IS NULL AND ENT.BUSINESS_GROUP_ID IS NULL) OR (ENT.BUSINESS_GROUP_ID IS NULL AND :B2 = ENT.LEGISLATION_CODE ) OR ENT.BUSINESS_GROUP_ID + 0 = :B1 ) AND ENT.ROUTE_ID = RO.ROUTE_ID
gj45bp5w5anrf	SELECT DISTINCT PEE.ASSIGNMENT_ID , PET.PRORATION_GROUP_ID FROM PAY_ELEMENT_ENTRIES_F PEE, PAY_ELEMENT_TYPES_F PET WHERE PEE.ELEMENT_ENTRY_ID = :B2 AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PEE.EFFECTIVE_START_DATE <= :B1 AND PEE.EFFECTIVE_END_DATE >= PAY_INTERPRETER_PKG.TIME_FN(:B3 , PET.PRORATION_GROUP_ID , :B2 ) AND :B1 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
gmn54j007428y	select tax_unit_id into :b0:b1 from pay_assignment_actions paa where paa.assignment_action_id=:b2

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Instance Activity Stats

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Back to Instance Activity Statistics
Back to Top

IO Stats

• Tablespace IO Stats
• File IO Stats

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Back to Top

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Back to Advisory Statistics
Back to Top

SGA Target Advisory

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Back to Wait Statistics
Back to Top

Enqueue Activity

• only enqueues with waits are shown
• Enqueue stats gathered prior to 10g should not be compared with 10g data
• ordered by Wait Time desc, Waits desc

Back to Wait Statistics
Back to Top

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Undo Segment Summary

• Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
• STO - Snapshot Too Old count, OOS - Out of Space count
• Undo segment block stats:
• uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
• eS - expired Stolen, eR - expired Released, eU - expired reUsed

Back to Undo Statistics
Back to Top

Undo Segment Stats

• Most recent 35 Undostat rows, ordered by Time desc

Back to Undo Statistics
Back to Top

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0