WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
EBSDB	716424792	ebsdb	1	20-Feb-18 21:02	11.2.0.4.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
bmora11t	Linux x86 64-bit	16	8	2	503.53

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	695	13-Mar-18 20:13:28	739	6.3
End Snap:	699	13-Mar-18 21:48:35	738	6.2
Elapsed:		95.10 (mins)
DB Time:		687.23 (mins)

Report Summary

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	7.2	0.2	0.00	0.00
DB CPU(s):	6.8	0.2	0.00	0.00
Redo size (bytes):	4,026,780.2	92,303.7
Logical read (blocks):	907,159.4	20,794.3
Block changes:	25,191.3	577.5
Physical read (blocks):	1,102.1	25.3
Physical write (blocks):	467.7	10.7
Read IO requests:	756.6	17.3
Write IO requests:	467.4	10.7
Read IO (MB):	8.6	0.2
Write IO (MB):	3.7	0.1
User calls:	4,402.2	100.9
Parses (SQL):	2,378.5	54.5
Hard parses (SQL):	0.0	0.0
SQL Work Area (MB):	5.7	0.1
Logons:	0.1	0.0
Executes (SQL):	52,839.5	1,211.2
Rollbacks:	0.6	0.0
Transactions:	43.6

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.96	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	101.10	Soft Parse %:	100.00
Execute to Parse %:	95.50	Latch Hit %:	99.39
Parse CPU to Parse Elapsd %:	53.71	% Non-Parse CPU:	99.72

Top 10 Foreground Events by Total Wait Time

Wait Classes by Total Wait Time

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
16	8	2	2.07	2.47	45.8	4.0	0.0	50.0

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
42.7	85.3	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	1,306.0	759.3	546.7
Database Requests:	1,224.0	756.6	467.4
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	78.6	0.0	78.6
Total (MB):	16.3	8.7	7.7
Database (MB):	12.3	8.6	3.7
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	4.0	0.0	4.0
Database (blocks):	1,569.8	1,102.1	467.7
Via Buffer Cache (blocks):	488.8	22.0	466.8
Direct (blocks):	1,081.1	1,080.1	0.9

Memory Statistics

	Begin	End
Host Mem (MB):	515,617.1	515,617.1
SGA use (MB):	30,720.0	30,720.0
PGA use (MB):	2,066.1	2,064.9
% Host Mem used for SGA+PGA:	6.36	6.36

Cache Sizes

	Begin	End
Buffer Cache:	24,768M	24,768M	Std Block Size:	8K
Shared Pool Size:	4,404M	4,403M	Log Buffer:	53,948K

Shared Pool Statistics

	Begin	End
Memory Usage %:	54.16	54.54
% SQL with executions>1:	99.05	99.07
% Memory for SQL w/exec>1:	98.50	98.67

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
• Shared Server Statistics
• init.ora Parameters

Wait Events Statistics

• Time Model Statistics
• Operating System Statistics
• Operating System Statistics - Detail
• Foreground Wait Class
• Foreground Wait Events
• Background Wait Events
• Wait Event Histogram
• Wait Event Histogram Detail (64 msec to 2 sec)
• Wait Event Histogram Detail (4 sec to 2 min)
• Wait Event Histogram Detail (4 min to 1 hr)
• Service Statistics
• Service Wait Class Stats

Time Model Statistics

• Total time in database user-calls (DB Time): 41234s
• 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

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond - 1000th of a second
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 97.7% of Total DB time 41,233.97 (s)
• Total FG Wait Time: 1,663.65 (s) DB CPU time: 38,607.43 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond - 1000th of a second
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
• % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

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 seconds

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• 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
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 62.7% of Total DB Time (s): 41,234
• Captured PL/SQL account for 166.2% of Total DB Time (s): 41,234

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 - CPU Time as a percentage of Total DB CPU
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 62.6% of Total CPU Time (s): 38,607
• Captured PL/SQL account for 166.5% of Total CPU Time (s): 38,607

Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - User I/O Time as a percentage of Total User I/O Wait time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 96.1% of Total User I/O Wait Time (s): 515
• Captured PL/SQL account for 112.2% of Total User I/O Wait Time (s): 515

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 as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 5,176,307,882
• Captured SQL account for 100.1% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• %Total - Physical Reads as a percentage of Total Disk Reads
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Disk Reads: 6,288,712
• Captured SQL account for 77.0% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 4,317,382
• Captured SQL account for 96.6% of Total
• Total UnOptimized Read Requests: 4,317,382
• Captured SQL account for 96.6% of Total
• Total Optimized Read Requests: 1
• Captured SQL account for 0.0% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Executions: 301,505,417
• Captured SQL account for 70.5% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 13,571,734
• Captured SQL account for 70.8% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

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
020tmbt6m630f	SELECT /*+ ORDERED index(pa2 PAY_PAYROLL_ACTIONS_N5) index(as1 PER_ASSIGNMENTS_F_N4) index(as2 PER_ASSIGNMENTS_F_PK) index(opm PAY_ORG_PAYMENT_METHODS_F_PK) USE_NL(pop pos as1 as2) */ ACT.ASSIGNMENT_ACTION_ID, ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID, PPP.PRE_PAYMENT_ID, PA1.ASSIGNMENT_SET_ID, AS1.PAYROLL_ID FROM PAY_PAYROLL_ACTIONS PA1, PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL, PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F AS1, PAY_ASSIGNMENT_ACTIONS ACT, PAY_PRE_PAYMENTS PPP, PER_ALL_ASSIGNMENTS_F AS2, PAY_ORG_PAYMENT_METHODS_F OPM WHERE PA1.PAYROLL_ACTION_ID = :B3 AND PA2.CONSOLIDATION_SET_ID +0 = PA1.CONSOLIDATION_SET_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND ACT.ACTION_STATUS = 'C' AND PCL.CLASSIFICATION_NAME = :B4 AND PA2.ACTION_TYPE = PCL.ACTION_TYPE AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_D ATE AND AS1.EFFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA1.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_START_DATE AND AS2.EFFECTIVE_END_DATE AND AS2.PAYROLL_ID + 0 = AS1.PAYROLL_ID + 0 AND POS.PERIOD_OF_SERVICE_ID = AS1.PERIOD_OF_SERVICE_ID AND POP.PAYROLL_ACTION_ID = :B3 AND POP.CHUNK_NUMBER = :B2 AND POS.PERSON_ID = POP.PERSON_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR PA1.PAYROLL_ID IS NULL) AND PPP.ASSIGNMENT_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID AND PPP.ORGANIZATION_ID IS NULL AND PA1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND OPM.PAYMENT_TYPE_ID +0 = :B1 AND (OPM.ORG_PAYMENT_METHOD_ID = PA1.ORG_PAYMENT_METHOD_ID OR PA1.ORG_PAYMENT_METHOD_ID IS NULL) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PAY_ACTION_INTERLOCKS INT, PAY_ASSIGNMENT_ACTIONS AC2 WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND AC2.PRE_PAYMEN T_ID = PPP.PRE_PAYMENT_ID AND NOT EXISTS ( SELECT NULL FROM PAY_ASSIGNMENT_ACTIONS PAA_VOID, PAY_ACTION_INTERLOCKS PAI_VOID, PAY_PAYROLL_ACTIONS PPA_VOID WHERE PAI_VOID.LOCKED_ACTION_ID = AC2.ASSIGNMENT_ACTION_ID AND PAI_VOID.LOCKING_ACTION_ID = PAA_VOID.ASSIGNMENT_ACTION_ID AND PAA_VOID.PAYROLL_ACTION_ID = PPA_VOID.PAYROLL_ACTION_ID AND PPA_VOID.ACTION_TYPE = 'D') ) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PER_ALL_ASSIGNMENTS_F AS3, PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B5 = 'N' AND AC3.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ('C', 'S') AND AS3.ASSIGNMENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS3.EFFECTIVE_START_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID = AS2.PERSON_ID) ORDER BY ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID, POS.PERIOD_OF_SERVICE_ID
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
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')
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
2dqtvtdcud8mb	SELECT ACTION_INFORMATION_ID, EFFECTIVE_DATE, OBJECT_VERSION_NUMBER FROM PAY_ACTION_INFORMATION WHERE ACTION_CONTEXT_ID = :B2 AND ACTION_CONTEXT_TYPE = 'AAP' AND ASSIGNMENT_ID = :B1 AND ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
2n263t3yy8vr7	SELECT ORG_INFORMATION9 FROM HR_ORGANIZATION_INFORMATION WHERE ORG_INFORMATION_CONTEXT = 'Business Group Information' AND ORGANIZATION_ID = :B1
2s63x4739jgqf	select /*+ INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK, EE PAY_ELEMENT_ENTRIES_F_N50, ET PAY_ELEMENT_TYPES_F_PK, EEV PAY_ELEMENT_ENTRY_VALUES_F_N50) +*/ EE.entry_type , to_char(EE.element_entry_id) , decode(:b0, 'T', decode(EE.entry_type, 'R', to_char(EE.target_entry_id), 'A', to_char(EE.target_entry_id), to_char(EE.original_entry_id)), to_char(EE.original_entry_id)) , EE.element_link_id , to_char(EE.target_entry_id) , EE.subpriority , ET.element_type_id , ET.processing_priority , ET.processing_type , EEV.input_value_id , EEV.screen_entry_value , to_char(null ) , to_char(null ) , 0 , to_char(null ) , to_char(null ) , decode(EE.entry_type, 'S', 1, 'E', 2, 'R', 2, 'A', 2, 'D', 3) , nvl(EE.target_entry_id, EE.element_entry_id) , decode(EE.entry_type, 'E', 2, 1) , to_char(EE.effective_start_date, :b1) , to_char(EE.effective_end_date, :b1) , ET.proration_group_id , pay_retro_pkg.is_retro_entry(EE.creator_type) , to_char(null ) , 'RECALC_NON_RETRO' , 'E' , PEPD.tax_unit_id , to_char(null ) , PEPD .time_definition_id , nvl(ET.time_definition_type, 'N') , to_char(decode(ET.processing_type, 'R', to_date(null , :b1), EE.date_earned), :b1) , decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ) , decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ) , EE.label_identifier , decode(ACT.action_status, 'B', decode(EE.creator_type, 'EE', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b10, :b11:b12, EE.creator_id, ACT.action_sequence), 'RR', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b14, :b11:b16, EE.creator_id, ACT.action_sequence), 1), 1) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'AE', to_char(EE.source_start_date, :b1), 'AD', to_char(EE.source_start_date, :b1), null ) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.s ource_end_date, :b1), null ), 'AE', to_char(EE.source_end_date, :b1), 'AD', to_char(EE.source_end_date, :b1), null ) from pay_assignment_actions ACT , pay_element_entries_f EE , pay_element_types_f ET , pay_element_entry_values_f EEV , pay_entry_process_details PEPD where (((((((((((((((ACT.assignment_action_id=:b29 and EE.assignment_id=:b30) and ((:b31='N' and to_date(:b32, :b1) between EE.effective_start_date and EE.effective_end_date) or ((:b31='Y' and EE.effective_start_date<=to_date(:b32, :b1)) and EE.effective_end_date>=decode(ET.proration_group_id, null , decode(nvl(ET.time_definition_type, 'N'), 'N', to_date(:b32, :b1), pay_core_utils.get_time_period_start(ACT.payroll_action_id)), pay_interpreter_pkg.prorate_start_date(:b29, ET.proration_group_id))))) and EE.element_type_id=ET.element_type_id) and EE.element_entry_id=PEPD.element_entry_id(+)) and to_date(:b32, :b1) between ET.effective_start_date and ET.effective_end_date) and EEV.element_entry_id(+)=EE.element_entry_id) and EEV.screen_entry_value(+) is not null ) and EE.effective_start_date=EEV.effective_start_date(+)) and EE.effective_end_date=EEV.effective_end_date(+)) and EE.entry_type<>'B') and ET.process_in_run_flag<>'N') and ((:b31='N' or ET.proration_group_id is null ) or (:b31='Y' and exists (select '' from pay_payroll_actions ppa , per_time_periods ptp , pay_element_entries_f pee where (((((pee.element_entry_id=EE.element_entry_id and ppa.payroll_action_id=ACT.payroll_action_id) and ppa.payroll_id=ptp.payroll_id) and to_date(:b32, :b1) between ptp.start_date and ptp.end_date) and pee.effective_start_date<=ptp.end_date) and pee.effective_end_date>=ptp.start_date))))) and not (ACT.action_status='B' and EE.creator_type='P')) and not (ACT.action_status='B' and EE.creator_type='R')) and ((((ET.processing_type='N' or EE.entry_type='D') and nvl(EE.date_earned, to_date('01/01/0001', 'DD/MM/YYYY'))<=to_date(:b32, :b1)) and not exists (select '' from pay_run_results prr where (((pr r.source_id=EE.element_entry_id and prr.source_type='E') and nvl(prr.status, 'U') not in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or ((ET.processing_type<>'N' and EE.entry_type<>'D') and ((nvl(ET.once_each_period_flag, 'N')='Y' and not exists (select '' from pay_run_results prr , pay_assignment_actions paa , pay_payroll_actions ppa , per_time_periods ptp , per_time_periods ptp1 , pay_payroll_actions ppa1 where (((((((((((prr.source_id=EE.element_entry_id and prr.source_type='E') and paa.assignment_action_id=prr.assignment_action_id) and ppa.payroll_action_id=paa.payroll_action_id) and ptp.payroll_id=ppa.payroll_id) and ppa.date_earned between ptp.start_date and ptp.end_date) and ppa1.payroll_action_id=ACT.payroll_action_id) and ptp1.payroll_id=ppa1.payroll_id) and ppa1.date_earned between ptp1.start_date and ptp1.end_date) and ptp.time_period_id=ptp1.time_period_id) and nvl(prr.status, 'U') n ot in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or nvl(ET.once_each_period_flag, 'N')='N')))) order by 8 asc , 6 asc , 7 asc , 17 asc , 18 asc , 19 asc , 20 asc
2tr0101sa3jh2	BEGIN FFP1893_01010001.FORMULA( :N1, :N2, :T1, :I1, :D1, :T2, :I2, :T5, :I5, :T4, :I4, :T6, :I6, :N5, :T8, :I8, :N4, :N3, :T3, :I3, :T7, :I7, :FFERLN, :FFERCD, :FFERMT); END;
2vs0kkjhmg51p	SELECT RP.SEQUENCE_NO, RPV.VALUE, RP.PARAMETER_NAME 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
36z3h9rgkd8aa	begin hrassact.update_action_sequence (:aa_id, :itpflg); end;
3kzzd3gfzz66a	SELECT DISTINCT PEC.CLASSIFICATION_NAME, PET.PROCESSING_PRIORITY, DECODE(PEC.CLASSIFICATION_NAME, 'Tax Deductions', NVL(PETL.REPORTING_NAME, PETL.ELEMENT_NAME) || ' Withheld', NVL(PETL.REPORTING_NAME, PETL.ELEMENT_NAME)) REPORTING_NAME, DECODE(PEC.CLASSIFICATION_NAME, 'Tax Deductions', NULL, PRR.ELEMENT_TYPE_ID) ELEMENT_TYPE_ID, NVL(DECODE(PEC.CLASSIFICATION_NAME, 'Tax Deductions', PRR.JURISDICTION_CODE), '00-000-0000'), PET.ELEMENT_INFORMATION10, PET.ELEMENT_INFORMATION12 FROM PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PAY_PAYROLLS_F PPF, PAY_RUN_RESULTS PRR, PAY_ELEMENT_TYPES_F PET , PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_TYPES_F_TL PETL WHERE PPA.ACTION_TYPE IN ('R', 'Q', 'B') AND PPA.EFFECTIVE_DATE > :B4 AND PPA.EFFECTIVE_DATE <= :B3 AND PPA.PAYROLL_ID = PPF.PAYROLL_ID AND PPF.PAYROLL_ID >= 0 AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PAA.A SSIGNMENT_ID = :B2 AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID AND PET.ELEMENT_INFORMATION10 IS NOT NULL AND PPA.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND PETL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PETL.LANGUAGE = :B1 AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID AND PEC.CLASSIFICATION_NAME IN ('Earnings', 'Alien/Expat Earnings', 'Supplemental Earnings', 'Imputed Earnings', 'Taxable Benefits', 'Pre-Tax Deductions', 'Involuntary Deductions', 'Voluntary Deductions', 'Non-payroll Payments', 'Tax Deductions' ) AND PET.ELEMENT_NAME NOT LIKE '%Calculator' AND PET.ELEMENT_NAME NOT LIKE '%Special Inputs' AND PET.ELEMENT_NAME NOT LIKE '%Special Features' AND PET.ELEMENT_NAME NOT LIKE '%Special Features 2' AND PET.ELEMENT_NAME NOT LIKE '%Verifier' AND PET.ELEMENT_NAME NOT LIKE '%Priority' ORDER BY 1, 3, 4
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_PR T.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 = A SSACT.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;
47g6xx472xzud	BEGIN FFP51704_01010001.FORMULA( :N3, :I7, :N2, :T1, :I1, :T5, :I5, :T4, :I4, :N8, :I13, :D2, :I8, :T3, :I3, :T8, :I15, :N6, :I11, :T7, :I14, :N4, :I9, :D1, :N12, :I19, :N10, :I17, :N7, :I12, :N11, :I18, :T6, :I6, :N1, :T2, :I2, :N5, :I10, :N9, :I16, :FFERLN, :FFERCD, :FFERMT); END;
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;
5a1vy8432twd3	SELECT COUNT(*) FROM OE_ORDER_LINES_ALL WHERE ORDER_SOURCE_ID = 0 AND TO_CHAR(CREATION_DATE, 'YYYYMMDDHH24MI') BETWEEN TO_CHAR(TO_DATE(:B2 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND TO_CHAR(TO_DATE(:B1 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI')
5f9spvysgkr1c	INSERT INTO FND_CONCURRENT_REQUESTS ( REQUEST_ID, SECURITY_GROUP_ID, PHASE_CODE, STATUS_CODE, PRIORITY, PARENT_REQUEST_ID, PRIORITY_REQUEST_ID, DESCRIPTION, REQ_INFORMATION, IS_SUB_REQUEST, HAS_SUB_REQUEST, UPDATE_PROTECTED, HOLD_FLAG, ENFORCE_SERIALITY_FLAG, SINGLE_THREAD_FLAG, ARGUMENT_INPUT_METHOD_CODE, IMPLICIT_CODE, REQUEST_DATE, REQUESTED_START_DATE, REQUESTED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ORACLE_ID, CONC_LOGIN_ID, RESPONSIBILITY_ID, RESPONSIBILITY_APPLICATION_ID, NLS_LANGUAGE, NLS_TERRITORY, NLS_NUMERIC_CHARACTERS, NLS_SORT, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID, QUEUE_METHOD_CODE, REQUEST_CLASS_APPLICATION_ID, CONCURRENT_REQUEST_CLASS_ID, PRINT_GROUP, PRINTER, PRINT_STYLE, NUMBER_OF_COPIES, SAVE_OUTPUT_FLAG, CRM_THRSHLD, RESUBMIT_TIME, RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE, RESUBMIT_END_DATE, RESUBMITTED, CRITICAL, REQUEST_TYPE, RELEASE_CLASS_APP_ID, RELE ASE_CLASS_ID, STALE_DATE, CANCEL_OR_HOLD, OUTPUT_FILE_TYPE, NLS_COMPLIANT, CD_ID, REQUEST_LIMIT, INCREMENT_DATES, ENABLE_TRACE, OPS_INSTANCE, ORG_ID, NODE_NAME1, CONNSTR1, RECALC_PARAMETERS, EDITION_NAME, ARGUMENT_TEXT, NUMBER_OF_ARGUMENTS, ARGUMENT1, ARGUMENT2, ARGUMENT3, ARGUMENT4, ARGUMENT5, ARGUMENT6, ARGUMENT7, ARGUMENT8, ARGUMENT9, ARGUMENT10, ARGUMENT11, ARGUMENT12, ARGUMENT13, ARGUMENT14, ARGUMENT15, ARGUMENT16, ARGUMENT17, ARGUMENT18, ARGUMENT19, ARGUMENT20, ARGUMENT21, ARGUMENT22, ARGUMENT23, ARGUMENT24, ARGUMENT25) SELECT :B79 , :B78 , :B77 , DECODE (:B76 , 'A', 'A', 'Z', 'Z', DECODE(FCP.QUEUE_METHOD_CODE, 'B', 'Q', DECODE (:B57 , 'Y', 'Q', DECODE(:B38 , 'Y', 'Q', 'I')))), DECODE (FCP.QUEUE_CONTROL_FLAG, 'Y', NVL(FCP.REQUEST_PRIORITY, 0), DECODE (:B75 , NULL, 50, :B75 )), DECODE (:B67 , 0, NULL, :B67 ), :B74 , :B73 , NULL, :B72 , 'N', :B71 , :B70 , 'Y', :B57 , 'S', :B69 , SYSDATE, DECODE (:B68 , '', (SYSDATE - :B66 ), GREATEST (:B68 , DECODE (:B67 , 0, (SYSDATE - :B66 ), FND_CONC_DATE.STRING_TO_DATE ( '01-01-0001')))), :B65 , SYSDATE, :B65 , :B64 , ORACLE_ID, :B64 , :B63 , :B62 , :B61 , :B60 , :B59 , :B58 , FA.APPLICATION_ID, FCP.CONCURRENT_PROGRAM_ID, DECODE(:B38 , 'Y', 'B', DECODE(:B57 , 'N', FCP.QUEUE_METHOD_CODE, 'B')), FCP.CLASS_APPLICATION_ID, FCP.CONCURRENT_CLASS_ID, :B56 , :B55 , :B54 , :B53 , :B52 , :B51 , DECODE (:B50 , '', NULL, TO_CHAR (FND_CONC_DATE.STRING_TO_DATE ( '01-01-0001' || :B50 ), 'HH24:MI:SS')), DECODE (:B49 , '', NULL, :B49 ), :B48 , :B47 , :B46 , 'N', :B45 , :B44 , :B43 , :B42 , :B41 , :B40 , FCP.OUTPUT_FILE_TYPE, FCP.NLS_COMPLIANT, :B39 , :B38 , :B37 , DECODE(:B36 , 'Y', 'Y', FCP.ENABLE_TRACE), :B35 , :B34 , :B33 , :B32 , :B31 , SYS_CONTEXT('userenv', 'current_edition_name'), :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 FROM FND_APPLICATION FA, FND_CONCURRENT_PROGRAMS FCP, FND_ORACLE_USERID WHERE ORACLE_USERNAME = :B3 AND FCP.ENABLED_FLAG = 'Y' AND (FCP.APPLICATION_ID = FA.APPLICATION_ID AND CONCURRENT_PROGRAM_NAME = UPPER (:B2 )) AND FA.APPLICATION_SHORT_NAME = UPPER (:B1 )
5gu31grmnvk65	BEGIN :start_time := '201803131300'; :end_time := '201803131400'; :group_num := 10; :payroll_date := '16-MAY-15'; -- --:payroll_model_cnt := '250000'; --750000 := 750000; --:o2c_inv_mtl_trx := ; --:o2c_gl_model_cnt := 300000; -- :elapsed_time := (to_date(:end_time, 'yyyymmddhh24mi') - to_date(:start_time, 'yyyymmddhh24mi')) * 24; ------------------------------------------------------------ -- PAYROLL BATCH ------------------------------------------------------- SELECT round((max(actual_completion_date) - min(actual_start_date)) *24*60) INTO :payroll_process_elapse FROM fnd_concurrent_requests WHERE parent_request_id=(SELECT max(request_id) FROM pay_payroll_actions WHERE date_earned=:payroll_date AND action_type='R' ); SELECT round((max(actual_completion_date) - min(actual_start_date)) *24*60) INTO :prepayment_elapse FROM fnd_concurrent_requests WHERE parent_request_id=(SELECT max(request_id) FROM pay_payroll_actions WHERE start_date=:payroll_date AND action_type='P' ); SELECT round((m ax(actual_completion_date) - min(actual_start_date)) *24*60) INTO :nacha_elapse FROM fnd_concurrent_requests WHERE parent_request_id=(SELECT max(request_id) FROM pay_payroll_actions WHERE start_date=:payroll_date AND action_type='M' ); SELECT round((max(actual_completion_date) - min(actual_start_date)) *24*60) INTO :checkwriter_elapse FROM fnd_concurrent_requests WHERE parent_request_id=(SELECT max(request_id) FROM pay_payroll_actions WHERE start_date=:payroll_date AND action_type='H' ); SELECT round((max(actual_completion_date) - min(actual_start_date)) *24*60) INTO :costing_elapse FROM fnd_concurrent_requests WHERE parent_request_id=(SELECT max(request_id) FROM pay_payroll_actions WHERE start_date=:payroll_date AND action_type='C' ); SELECT count(assignment_id) INTO :payroll_process_row FROM pay_assignment_actions assgn, pay_payroll_actions payroll WHERE payroll.payroll_action_id = assgn.payroll_action_id AND payroll.date_earned = :payroll_date AND payroll.action_type = 'R' AND assg n.action_status = 'C'; SELECT count(assignment_id) INTO :prepayment_row FROM pay_assignment_actions assgn, pay_payroll_actions payroll WHERE payroll.payroll_action_id = assgn.payroll_action_id AND payroll.start_date = :payroll_date AND payroll.action_type = 'P' AND assgn.action_status = 'C'; SELECT count(assignment_id) INTO :nacha_row FROM pay_assignment_actions assgn, pay_payroll_actions payroll WHERE payroll.payroll_action_id = assgn.payroll_action_id AND payroll.start_date = :payroll_date AND payroll.action_type = 'M' AND assgn.action_status = 'C'; SELECT count(assignment_id) INTO :checkwriter_row FROM pay_assignment_actions assgn, pay_payroll_actions payroll WHERE payroll.payroll_action_id = assgn.payroll_action_id AND payroll.start_date = :payroll_date AND payroll.action_type = 'H' AND assgn.action_status = 'C'; SELECT count(assignment_id) INTO :costing_row FROM pay_assignment_actions assgn, pay_payroll_actions payroll WHERE payroll.payroll_action_id = assgn.payroll_action_id AND payroll.start_date = :payroll_date AND payroll.action_type = 'C' AND assgn.action_status = 'C'; ------------------------------------------------------------------------------ --check "AP Create Payables invoice" ------------------------------------------------------------------------------ select count(*) "AP Invoices Entered" into :ap_inv_count from ap_invoices_all where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check online je lines entered ------------------------------------------------------------------------------ select count(*) "GL Lines Imported" into :gl_misc_count from gl_je_lines gjl, gl_je_headers gjh where to_char(gjl.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(gjl.creation_date, 'yyyymmddhh24mi') <= :end_time and gjh.je_source = 'Manual' and gjh.je_header_ id = gjl.je_header_id ; ------------------------------------------------------------------------------ --check "SS Enter Cash Expense" ------------------------------------------------------------------------------ select count(*) "Cash/CC Exp" into :ss_ap_cash_exp_count from ap_expense_report_headers_all where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time and amt_due_ccard_company=0; ------------------------------------------------------------------------------ --check "SS Enter Credit Card Expense" ------------------------------------------------------------------------------ select count(*) "Cash/CC Exp" into :ss_ap_credit_exp_count from ap_expense_report_headers_all where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time and amt_due_ccard_company>0; ------------------------------------------------ ------------------------------ -- check "SS Enter Time Card (5 rows in Database per TimeCard) " ------------------------------------------------------------------------------ select count(*) "Time Cards" into :ss_time_card_count from hxc_time_building_blocks where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time --where trunc(creation_date) = trunc(sysdate) and scope='TIMECARD'; ------------------------------------------------------------------------------ --check "SS Enter Purchase Requisition" ------------------------------------------------------------------------------ select count(*) "Web Reqs" into :ss_po_req_count --from po_requisition_headers from po_requisition_headers_all where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; --------------------------------------------------------------------- --------- --check "Service - Create SR" ------------------------------------------------------------------------------ select count(*) "CS Service Request Created" into :cs_sr_count from cs_incidents_all_b where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "Service - Update SR" ------------------------------------------------------------------------------ select count(*) "CS Service Request Created" into :cs_sr_update_count from cs_incidents_all_b where to_char(LAST_UPDATE_DATE, 'yyyymmddhh24mi') > :start_time and to_char(LAST_UPDATE_DATE, 'yyyymmddhh24mi') <= :end_time and incident_status_id in (103, 2); ------------------------------------------------------------------------------ --check "Service - Close SR" --------------------------------------------------------- --------------------- select count(*) "CS Service Request Created" into :cs_sr_close_count from cs_incidents_all_b where to_char(close_date, 'yyyymmddhh24mi') > :start_time and to_char(close_date, 'yyyymmddhh24mi') <= :end_time and status_flag='C'; ------------------------------------------------------------------------------ --check "PO Insert Purchase Order" purchase order lines ( 5 lines per PO) ------------------------------------------------------------------------------ select count(*) "PO lines Entered" into :po_count from po_lines_all where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "FA Create Asset" lines ( 5 lines per asset) ------------------------------------------------------------------------------ select count(*) "FA assets entered" into :fa_count fro m fa_additions_b where to_char(creation_date, 'yyyymmddhh24mi') > :start_time and to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "INV Insert Misc Transaction" (6 items per trx) ------------------------------------------------------------------------------ select count(*) "INV Transaction" into :inv_count from mtl_material_transactions a, mtl_transaction_types b where b.transaction_type_id = a.transaction_type_id and to_char(a.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(a.creation_date, 'yyyymmddhh24mi') <= :end_time and b.transaction_type_name='Miscellaneous receipt'; ------------------------------------------------------------------------------ --check "Sales and Marketing" lead2proposal ------------------------------------------------------------------------------ SELECT count(*) into :lead2proposal FROM prp_proposals pp, prp_pr oposal_objects ppo WHERE ppo.object_type = 'SALES_LEAD' AND ppo.proposal_id = pp.proposal_id AND to_char(pp.creation_date, 'yyyymmddhh24mi') > :start_time AND to_char(pp.creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "Sales and Marketing" lead2opportunity ------------------------------------------------------------------------------ SELECT count(*) into :lead2opportunity FROM as_sales_lead_opportunity WHERE to_char(creation_date, 'yyyymmddhh24mi') > :start_time AND to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "Sales and Marketing" opportunity2proposal ------------------------------------------------------------------------------ SELECT count(*) into :opportunity2proposal FROM prp_proposals pp, prp_proposal_objects ppo WHERE ppo.object_type = 'OPPORTUNITY' AND ppo.propo sal_id = pp.proposal_id AND to_char(pp.creation_date, 'yyyymmddhh24mi') > :start_time AND to_char(pp.creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "Sales and Marketing" opportunity2quote ------------------------------------------------------------------------------ SELECT count(*) into :opportunity2quote FROM aso_quote_related_objects WHERE object_type_code='LDID' AND relationship_type_code = 'OPP_QUOTE' AND quote_object_type_code = 'HEADER' AND to_char(creation_date, 'yyyymmddhh24mi') > :start_time AND to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------------ --check "Sales and Marketing" opportunity2order ------------------------------------------------------------------------------ SELECT count(*) into :opportunity2order FROM aso_quote_related_objects WHERE object_type_code='ORDER' AND relationship_type_code = 'QUOTE_ORDER' AND quote_object_type_code = 'HEADER' AND to_char(creation_date, 'yyyymmddhh24mi') > :start_time AND to_char(creation_date, 'yyyymmddhh24mi') <= :end_time; ------------------------------------------------------------------------- --Batch Audit start ----------------------------------------------------------------------------- ------------------------------------------------------------------------------ --check total # of online order lines ------------------------------------------------------------------------------ SELECT count(*) INTO :om_online_line FROM oe_order_lines_all WHERE order_source_id = 0 AND to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') ; ------------------------------------------------------------------------------ --check total # of batch order lines ------------------------------------- ----------------------------------------- SELECT count(*) INTO :om_batch_line FROM oe_order_lines_all WHERE order_source_id = 1 AND to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') ; ------------------------------------------------------------------------------ --check total # of order headers ------------------------------------------------------------------------------ SELECT count(*) "Total # of order Headers " INTO :om_total_header FROM oe_order_headers_all WHERE to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI'); ------------------------------------------------------------------------------ --check "step #2-OE HVOP" plus online omio -------------------------------------------------------------------- ---------- SELECT count(*) "Total # of Order Lines" INTO :om_total_line FROM oe_order_lines_all WHERE to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI'); SELECT count(*) "Total # of mtl lines" INTO :om_total_mtl_line FROM mtl_sales_orders WHERE segment1 in (select TO_CHAR(ORDER_NUMBER) from oe_order_headers_all WHERE to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') ); SELECT /*+ parallel(st, 4) parallel(wpa, 4) */ count(*) "Total WF" INTO :book_header FROM apps.wf_item_activity_statuses st, apps.wf_process_activities wpa WHERE wpa.instance_id = st.process_activity and st.item_type = 'OEOH' and wpa.activity_name = 'BOOK_ORDER' and st.activity_status = 'COMPLETE' and st.item_key in (select head er_id FROM oe_order_headers_all WHERE to_char(creation_date, 'YYYYMMDDHH24MI') BETWEEN to_char(to_date(:start_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND to_char(to_date(:end_time, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') ); ------------------------------------------------------------------------------ --check "step #3-OE Pick Release" ------------------------------------------------------------------------------ select count(*) into :om_pick_release_count from wsh_delivery_details wdd, oe_order_headers_all oh where to_char(wdd.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(wdd.last_update_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wdd.source_header_id and wdd.released_status = 'Y'; ------------------------------------------------------------------------------ --check "step #4-OE Process Delivery" ------------------------------------------------------------------------------ select count(*) into :om_process_delivery from wsh_new_d eliveries wnd, oe_order_headers_all oh where to_char(wnd.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(wnd.creation_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wnd.source_header_id and oh.order_source_id = 1; ------------------------------------------------------------------------------ --check "step #5.1-OE online Ship Confirm" ------------------------------------------------------------------------------ select count(*) into :om_online_ship_confirm_count from wsh_delivery_details wdd, oe_order_headers_all oh where to_char(wdd.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(wdd.last_update_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wdd.source_header_id and wdd.released_status = 'C' and oh.order_source_id = 0 ; ------------------------------------------------------------------------------ --check "step #5-OE Batch Ship Confirm" ---------------------------------------------------------------------- -------- select count(*) into :om_ship_confirm_count from wsh_delivery_details wdd, oe_order_headers_all oh where to_char(wdd.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(wdd.last_update_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wdd.source_header_id and wdd.released_status = 'C' and oh.order_source_id = 1; ------------------------------------------------------------------------------ --check "step #6.1 - OE online ITS" sales order lines ------------------------------------------------------------------------------ select count(*) into :om_online_its_count from wsh_delivery_details wdd, oe_order_headers_all oh where to_char(wdd.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(wdd.last_update_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wdd.source_header_id and wdd.oe_interfaced_flag ='Y' and wdd.inv_interfaced_flag = 'Y' and oh.order_source_id = 0 ; -------------------------------------------------------- ---------------------- --check "step #6 - OE batch ITS" sales order lines ------------------------------------------------------------------------------ select count(*) into :om_its_count from wsh_delivery_details wdd, oe_order_headers_all oh where to_char(wdd.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(wdd.last_update_date, 'yyyymmddhh24mi') <= :end_time and oh.header_id = wdd.source_header_id and wdd.oe_interfaced_flag ='Y' and wdd.inv_interfaced_flag = 'Y' and oh.order_source_id = 1; ------------------------------------------------------------------------------ --check "step #6.5 - INV Material Transactions" ------------------------------------------------------------------------------ select count(*) "INV Transaction" into :inv_mtl_trx_count from mtl_material_transactions a, mtl_transaction_types b where b.transaction_type_id = a.transaction_type_id and to_char(a.creation_date, 'yyyymmddhh24mi') > :start_time and to_char (a.creation_date, 'yyyymmddhh24mi') <= :end_time and b.transaction_type_name='Sales Order Pick'; ------------------------------------------------------------------------------ --check "step #7.1-AR online Auto Invoice" ------------------------------------------------------------------------------ select count(*) "AR Invoice lines Entered" into :ar_online_count from RA_CUSTOMER_TRX_LINES_ALL rctla, ra_customer_trx_all rcta where to_char(rctla.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(rctla.creation_date, 'yyyymmddhh24mi') <= :end_time and rctla.customer_trx_id = rcta.customer_trx_id and rcta.batch_source_id = -1 ; ------------------------------------------------------------------------------ --check "step #7-AR batch Auto Invoice" ------------------------------------------------------------------------------ select count(*) "AR Invoice lines Entered" into :ar_count from RA_CUSTOMER_TRX_LINES_ALL rctla, ra_customer_tr x_all rcta where to_char(rctla.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(rctla.creation_date, 'yyyymmddhh24mi') <= :end_time and rctla.customer_trx_id = rcta.customer_trx_id and rcta.batch_source_id = 1001; ------------------------------------------------------------------------------ --check "step #8-AR batch Revenue Recognition" ------------------------------------------------------------------------------ select count(*) into :ar_rev_recog from ra_cust_trx_line_gl_dist_all where to_char(creation_date, 'YYYYMMDDHH24MI') > :start_time and to_char(creation_date, 'YYYYMMDDHH24MI') <= :end_time and account_class = 'REV' and account_set_flag = 'Y'; ------------------------------------------------------------------------------ --check "step #9-GL Journal Import" je lines entered ------------------------------------------------------------------------------ select count(*) "GL Lines Imported" into :gl_count from gl_je_lines gjl, gl_je_headers gjh where to_char(gjl.creation_date, 'yyyymmddhh24mi') > :start_time and to_char(gjl.creation_date, 'yyyymmddhh24mi') <= :end_time and gjh.je_source = 'Receivables' and gjh.je_header_id = gjl.je_header_id; ------------------------------------------------------------------------------ --check "step #10-GL Posting" je lines posted ------------------------------------------------------------------------------ select count(*) "GL Lines Posted" into :gl_post_count from gl_je_lines gjl, gl_je_headers gjh where to_char(gjl.last_update_date, 'yyyymmddhh24mi') > :start_time and to_char(gjl.last_update_date, 'yyyymmddhh24mi') <= :end_time and gjh.je_source = 'Receivables' and gjh.je_header_id = gjl.je_header_id and gjl.status = 'P'; :expected_total_om_header := 75000 + :group_num * 4 * 12 + :group_num * 6 * 2; :expected_online_om_header := :group_num * 4 * 12 + :group_num * 6 * 2; :expected_om_line := 750000 + :group_num *4*12*5; :expected_om_he ader := 750000 + :group_num * 4 * 12; :expected_gl_line := 300000; select round(100*(:gl_misc_count/ (:group_num *8*2*10)), 2) into :gl_rate from dual; select round(100*(:ap_inv_count/ (:group_num *2*20)), 2) into :ap_inv_rate from dual; select round(100*(:ss_ap_cash_exp_count/ (:group_num *2*10*2)), 2) into :ss_ap_cash_exp_rate from dual; select round(100*(:ss_ap_credit_exp_count/ (:group_num *2*10*2)), 2) into :ss_ap_credit_exp_rate from dual; select round(100*(:ss_time_card_count/ (:group_num *3*10*2)), 2) into :ss_time_card_rate from dual; select round(100*(:ss_po_req_count/ (:group_num*2*20)), 2) into :ss_po_req_rate from dual; select round(100*(:ar_online_count/ (:group_num*2*24*5)), 2) into :ar_rate from dual; select round(100*(:om_online_line/ (:group_num*4*5*12)), 2) into :om_rate from dual; select round(100*(:cs_sr_count/ (:group_num *16*15)), 2) into :cs_sr_rate from dual; select round(100*(:cs_sr_update_count/ (:group_num *16*15)), 2) into :cs_sr_update_rate from dual; sele ct round(100*(:cs_sr_close_count/ (:group_num *8*15)), 2) into :cs_sr_close_rate from dual; select round(100*(:om_online_ship_confirm_count/(24*2*5*:group_num)), 2) into :om_ship_confirm_rate from dual; select round(100*(:om_online_its_count/ (24*2*5*:group_num)), 2) into :om_its_rate from dual; select round(100*(:po_count/ (:group_num *2*20*5)), 2) into :po_rate from dual ; select round(100*(:inv_count/ (:group_num*8*2*5)), 2) into :inv_rate from dual; select round(100*(:fa_count/ (:group_num *8*2)), 2) into :fa_rate from dual; select round(100*(:lead2proposal/ (:group_num*4*20)), 2) into :lead2proposal_rate from dual; select round(100*(:lead2opportunity/ (:group_num*4*20)), 2) into :lead2opportunity_rate from dual; select round(100*(:opportunity2proposal/ (:group_num*6*2)), 2) into :opportunity2proposal_rate from dual; select round(100*(:opportunity2quote/ (:group_num*6*2*2)), 2) into :opportunity2quote_rate from dual; select round(100*(:opportunity2order/(:group_num*6*2)), 2) into : opportunity2order_rate from dual; select round(100*(:om_batch_line/750000), 2) into :om_batch_line_rate from dual; select round(100*(:om_total_mtl_line/75000), 2) into :om_batch_mtl_rate from dual; select round(100*(:book_header/75000), 2) into :om_batch_header_rate from dual; select round(100*(:om_process_delivery/75000), 2) into :om_batch_pd_rate from dual; select round(100*(:om_ship_confirm_count/750000), 2) into :om_batch_sc_rate from dual; select round(100*(:om_its_count/750000), 2) into :om_batch_its_rate from dual; select round(100*(:inv_mtl_trx_count/(:expected_om_line*2)), 2) into :om_batch_inv_rate from dual; select round(100*(:ar_count/750000), 2) into :om_batch_ar_rate from dual; select round(100*(:ar_rev_recog/750000), 2) into :om_batch_ar_rev_rate from dual; select round(100*(:gl_count/:expected_gl_line), 2) into :om_batch_gl_rate from dual; select round(100*(:gl_post_count/:expected_gl_line), 2) into :om_batch_gp_rate from dual; ------------------------------------------ -- --Concurrent Programs -------------------------------------------- select count(*) into :ar_completed from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'ARXAGE'; select count(*) into :glppos_completed from gl_je_batches glb where glb.request_id in (select request_id from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'GLPPOS'); select count(*) int o :glautopost_completed from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'GLPAUTOP'; select count(*) into :inv_report_completed from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'INVISMMX'; select count(*) into :om_completed from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLIC ATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'OEXOEIOS'; select count(*) into :po_completed from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and actual_completion_date between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'POXPRPOP'; select count(*) into :glppos_submitted from gl_je_batches glb where glb.request_id in (select request_id from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'GLPPOS'); select count(*) into :glautopost_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'GLPAUTOP'; select count(*) into :ar_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONC URRENT_PROGRAM_NAME = 'ARXAGE'; select count(*) into :inv_report_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'INVISMMX'; select count(*) into :inv_misctrans_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'INVMBTRX'; select count(*) into :om_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_ PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'OEXOEIOS'; select count(*) into :po_submitted from APPLSYS.FND_CONCURRENT_REQUESTS fcr, APPLSYS.FND_CONCURRENT_PROGRAMS fcp where fcp.APPLICATION_ID = fcr.PROGRAM_APPLICATION_ID and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID and fcr.STATUS_CODE in ('C', 'G') and fcr.REQUESTED_START_DATE between to_date(:start_time, 'YYYYMMDDHH24MI') and to_date(:end_time, 'YYYYMMDDHH24MI') and fcp.CONCURRENT_PROGRAM_NAME = 'POXPRPOP'; select round(:group_num*:elapsed_time*8*4* 2) into :glppos_reqrate from dual; select round(100*(:glautopost_completed/60), 2) into :glautopost_reqrate from dual; select round(100*(:ar_completed/(:group_num*4*4)), 2) into :ar_reqrate from dual; select ro und(100*(:inv_report_completed/(:group_num*4*2)), 2) into :inv_report_reqrate from dual; select round(100*(:om_completed/(:group_num*4*2)), 2) into :om_reqrate from dual; select round(100*(:po_completed/(:group_num*4*2)), 2) into :po_reqrate from dual; END;
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
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))
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
67jm20vdbcvh1	SELECT /*+ ORDERED INDEX(pa2 PAY_PAYROLL_ACTIONS_N5) INDEX(pos PER_PERIODS_OF_SERVICE_N3) INDEX(as1 PER_ASSIGNMENTS_N4) INDEX(as2 PER_ASSIGNMENTS_F_PK) INDEX(act PAY_ASSIGNMENT_ACTIONS_N51) index(opm PAY_ORG_PAYMENT_METHODS_F_PK) USE_NL(pop pos ppp opm as1 act as2) */ ACT.ASSIGNMENT_ACTION_ID, ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID, PPP.PRE_PAYMENT_ID FROM PAY_PAYROLL_ACTIONS PA1, PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL, PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F AS1, PAY_ASSIGNMENT_ACTIONS ACT, PER_ALL_ASSIGNMENTS_F AS2, PAY_PRE_PAYMENTS PPP, PAY_ORG_PAYMENT_METHODS_F OPM WHERE PA1.PAYROLL_ACTION_ID = :B3 AND PA2.CONSOLIDATION_SET_ID +0 = PA1.CONSOLIDATION_SET_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND ACT.ACTION_STATUS = 'C' AND PCL.CLASSIFICATION_NAME = 'MAGTAPE' AND PA2.ACTION_TYPE = PCL.ACTION_TYPE AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AN D PA2.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_DATE AND AS1.EFFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA1.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_START_DATE AND AS2.EFFECTIVE_END_DATE AND AS2.PAYROLL_ID + 0 = AS1.PAYROLL_ID + 0 AND POS.PERIOD_OF_SERVICE_ID = AS1.PERIOD_OF_SERVICE_ID AND POP.PAYROLL_ACTION_ID = :B3 AND POP.CHUNK_NUMBER = :B2 AND POS.PERSON_ID = POP.PERSON_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR PA1.PAYROLL_ID IS NULL) AND PPP.ASSIGNMENT_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID AND PA1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND OPM.PAYMENT_TYPE_ID +0 = :B1 AND (OPM.ORG_PAYMENT_METHOD_ID = PA1.ORG_PAYMENT_METHOD_ID OR PA1.ORG_PAYMENT_METHOD_ID IS NULL) AND NOT EXISTS ( SELECT NULL FROM PER_ALL_ASSIGNMENTS_F AS3, PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B4 = 'N' AND AC3.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ('C', 'S') AND AS3.ASSIGN MENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS3.EFFECTIVE_START_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID = AS2.PERSON_ID) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PAY_ACTION_INTERLOCKS INT, PAY_ASSIGNMENT_ACTIONS AC2 WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND AC2.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID AND NOT EXISTS ( SELECT NULL FROM PAY_ASSIGNMENT_ACTIONS PAA_VOID, PAY_ACTION_INTERLOCKS PAI_VOID, PAY_PAYROLL_ACTIONS PPA_VOID WHERE PAI_VOID.LOCKED_ACTION_ID = AC2.ASSIGNMENT_ACTION_ID AND PAI_VOID.LOCKING_ACTION_ID = PAA_VOID.ASSIGNMENT_ACTION_ID AND PAA_VOID.PAYROLL_ACTION_ID = PPA_VOID.PAYROLL_ACTION_ID AND PPA_VOID.ACTION_TYPE = 'D') ) ORDER BY ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID, POS.PERIOD_OF_SERVICE_ID
6twak6npd0r0h	SELECT COUNT(*) "Total # of Order Lines" FROM OE_ORDER_LINES_ALL WHERE TO_CHAR(CREATION_DATE, 'YYYYMMDDHH24MI') BETWEEN TO_CHAR(TO_DATE(:B2 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND TO_CHAR(TO_DATE(:B1 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI')
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 )
771qbm8b25jdf	SELECT /*+ ORDERED index(pa2 PAY_PAYROLL_ACTIONS_N5) index(as1 PER_ASSIGNMENTS_F_N4) index(as2 PER_ASSIGNMENTS_F_PK) USE_NL(pop pos as1) */ ACT.ASSIGNMENT_ACTION_ID, ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID, AS1.PERSON_ID, AS1.EFFECTIVE_START_DATE, AS1.PRIMARY_FLAG FROM PAY_PAYROLL_ACTIONS PA1, PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL, PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F AS1, PAY_ASSIGNMENT_ACTIONS ACT, PER_ALL_ASSIGNMENTS_F AS2 WHERE PA1.PAYROLL_ACTION_ID = :B2 AND PA2.PAYROLL_ID = PA1.PAYROLL_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND ACT.ACTION_STATUS IN ('C', 'S') AND PCL.CLASSIFICATION_NAME = :B3 AND PA2.CONSOLIDATION_SET_ID = PA1.CONSOLIDATION_SET_ID AND PA2.ACTION_TYPE = PCL.ACTION_TYPE AND NVL(PA2.FUTURE_PROCESS_MODE, 'Y') = 'Y' AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_DATE AND AS1.E FFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA1.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_START_DATE AND AS2.EFFECTIVE_END_DATE AND AS2.PAYROLL_ID = AS1.PAYROLL_ID AND POS.PERIOD_OF_SERVICE_ID = AS1.PERIOD_OF_SERVICE_ID AND POP.PAYROLL_ACTION_ID = :B2 AND POP.CHUNK_NUMBER = :B1 AND POS.PERSON_ID = POP.PERSON_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR PA1.PAYROLL_ID IS NULL) AND NOT EXISTS ( SELECT NULL FROM PAY_ASSIGNMENT_ACTIONS AC2, PAY_PAYROLL_ACTIONS PA3, PAY_ACTION_INTERLOCKS INT WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND PA3.PAYROLL_ACTION_ID = AC2.PAYROLL_ACTION_ID AND PA3.ACTION_TYPE IN ('P', 'U')) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PER_ALL_ASSIGNMENTS_F AS3, PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B4 = 'N' AND AC3.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ( 'C', 'S') AND AS3.ASSIGNMENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS3.EFFECTIVE_ST ART_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID = AS2.PERSON_ID) ORDER BY AS1.PERSON_ID, AS1.PRIMARY_FLAG DESC , AS1.EFFECTIVE_START_DATE, ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID, POS.PERIOD_OF_SERVICE_ID
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 ;
7dm9q8dwzhxnp	SELECT PAI.EFFECTIVE_DATE, PAI.ACTION_CONTEXT_ID FROM PAY_ACTION_INFORMATION PAI WHERE PAI.ACTION_CONTEXT_TYPE = 'AAP' AND PAI.ASSIGNMENT_ID = :B4 AND PAI.ACTION_INFORMATION_CATEGORY = :B3 AND PAI.ACTION_CONTEXT_ID <> :B2 AND PAI.EFFECTIVE_DATE <= :B1 ORDER BY PAI.EFFECTIVE_DATE DESC , PAI.ACTION_CONTEXT_ID DESC
7y9t68kb4mc6q	BEGIN pay_us_action_arch.action_action_creation(:pactid, :stperson, :endperson, :chunk); END;
83kbt99vg4svq	begin hr_nonrun_asact.reinterlock (:assactid, :pactype); end;
89uy0gfk5f13y	SELECT CITYTAX.CITY_TAX FROM PAY_US_CITY_TAX_INFO_F CITYTAX, FND_SESSIONS SES WHERE CITYTAX.JURISDICTION_CODE = :B1 AND SES.SESSION_ID = USERENV('SESSIONID') AND SES.EFFECTIVE_DATE BETWEEN CITYTAX.EFFECTIVE_START_DATE AND CITYTAX.EFFECTIVE_END_DATE
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)
8uvn9f4h4qcbb	SELECT RCU.CONTEXT_ID, RCU.SEQUENCE_NO, C.CONTEXT_NAME FROM FF_ROUTE_CONTEXT_USAGES RCU, FF_CONTEXTS C WHERE RCU.ROUTE_ID = :B1 AND C.CONTEXT_ID = RCU.CONTEXT_ID ORDER BY SEQUENCE_NO
97mt0g20bcfc3	begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9tzv9mkcdu1zg	SELECT ROW_ID, PAYROLL_ID, PAYROLL_ACTION_ID, EFFECTIVE_DATE, PERIOD_NAME, MEANING, NAME, ACTION_TYPE, PAYROLL_NAME, STATUS_CODE, ACTION_STATUS, ACTION_SEQUENCE, MESSAGES_EXIST, REQUEST_ID FROM PAY_PAYROLL_ACTIONS_V6 WHERE /*******************************************/ /* CUSTOMIZATION */ /*******************************************/((:1 IS NOT NULL AND ((EXISTS (SELECT 1 FROM PAY_RESTRICTION_VALUES PRV1 WHERE PRV1.RESTRICTION_CODE = 'ACTION_TYPE' AND PRV1.CUSTOMIZED_RESTRICTION_ID = :2 AND PRV1.VALUE = PAY_PAYROLL_ACTIONS_V6.ACTION_TYPE)) OR (NOT EXISTS (SELECT 1 FROM PAY_RESTRICTION_VALUES PRV1 WHERE PRV1.RESTRICTION_CODE = 'ACTION_TYPE' AND PRV1.CUSTOMIZED_RESTRICTION_ID = :3) ))) OR :4 IS NULL) order by effective_date desc , action_sequence desc
acc06krjt5dkx	SELECT /*+ ORDERED index(pa2 PAY_PAYROLL_ACTIONS_N5) index(as1 PER_ASSIGNMENTS_F_N4) index(as2 PER_ASSIGNMENTS_F_PK) index(act PAY_ASSIGNMENT_ACTIONS_N51) USE_NL(pos pop as1) */ ACT.ASSIGNMENT_ACTION_ID, ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID FROM PAY_PAYROLL_ACTIONS PA1, PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL, PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F AS1, PAY_ASSIGNMENT_ACTIONS ACT, PER_ALL_ASSIGNMENTS_F AS2 WHERE PA1.PAYROLL_ACTION_ID = :B2 AND PA2.CONSOLIDATION_SET_ID = PA1.CONSOLIDATION_SET_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND ACT.ACTION_STATUS IN ('C', 'S') AND PCL.CLASSIFICATION_NAME = :B3 AND PA2.ACTION_TYPE = PCL.ACTION_TYPE AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_DATE AND AS1.EFFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA1.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_ START_DATE AND AS2.EFFECTIVE_END_DATE AND POP.PAYROLL_ACTION_ID = :B2 AND POP.CHUNK_NUMBER = :B1 AND POS.PERSON_ID = POP.PERSON_ID AND POS.PERIOD_OF_SERVICE_ID = AS1.PERIOD_OF_SERVICE_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR PA1.PAYROLL_ID IS NULL) AND NOT EXISTS ( SELECT NULL FROM PAY_ASSIGNMENT_ACTIONS AC2, PAY_PAYROLL_ACTIONS PA3, PAY_ACTION_INTERLOCKS INT WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND PA3.PAYROLL_ACTION_ID = AC2.PAYROLL_ACTION_ID AND PA3.ACTION_TYPE IN ('C', 'S')) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PER_ALL_ASSIGNMENTS_F AS3, PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B4 = 'N' AND AC3.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ('C', 'S') AND AS3.ASSIGNMENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS3.EFFECTIVE_START_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID = AS2.PERSON_ID) ORDER BY ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID, POS.PERIOD_ OF_SERVICE_ID
avj9kvsw52cv5	SELECT COUNT(*) FROM OE_ORDER_LINES_ALL WHERE ORDER_SOURCE_ID = 1 AND TO_CHAR(CREATION_DATE, 'YYYYMMDDHH24MI') BETWEEN TO_CHAR(TO_DATE(:B2 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI') AND TO_CHAR(TO_DATE(:B1 , 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI')
b11pwmvp5tx97	SELECT /*+ leading(ppr, paf, paa, ppa) */ PAA.ASSIGNMENT_ID, PAA.TAX_UNIT_ID, PPA.EFFECTIVE_DATE, PPA.DATE_EARNED, PPA.ACTION_TYPE, PAA.ASSIGNMENT_ACTION_ID, PAA.PAYROLL_ACTION_ID FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA, PER_ASSIGNMENTS_F PAF, PAY_POPULATION_RANGES PPR WHERE PPR.PAYROLL_ACTION_ID = :B7 AND PPR.CHUNK_NUMBER = :B6 AND PAF.PERSON_ID = PPR.PERSON_ID AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID AND PPA.CONSOLIDATION_SET_ID = :B5 AND PAA.ACTION_STATUS = 'C' AND PPA.PAYROLL_ID = :B4 AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.BUSINESS_GROUP_ID = :B3 AND PPA.EFFECTIVE_DATE BETWEEN :B2 AND :B1 AND PPA.ACTION_TYPE IN ('U', 'P', 'B', 'V') AND DECODE(PPA.ACTION_TYPE, 'B', NVL(PPA.FUTURE_PROCESS_MODE, 'Y'), 'N') = 'N' AND PAA.SOURCE_ACTION_ID IS NULL AND NOT EXISTS (SELECT 'x' FROM PAY_ACTION_INTERLOCKS PAI1, PAY_ASSIGNMENT_ACTIONS PAA1, PAY_PAYROLL_ACTIONS PPA1 WHERE PAI1.LOCKED_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA1.ASSIGNMENT_ACTION_ID = PAI1.LOCKING_ACTION_ID AND PPA1.PAYROLL_ACTION_ID = PAA1.PAYROLL_ACTION_ID AND PPA1.ACTION_TYPE ='X' AND PPA1.REPORT_TYPE = 'XFR_INTERFACE') ORDER BY 1, 2, 3, 5, 6
b5vu7wwd4np6v	select /*+ INDEX(pay_pos PER_PERIODS_OF_SERVICE_PK) INDEX(pay_asg PER_ASSIGNMENTS_F_N12) */ 1, pay_asg.assignment_id, pay_pac.payroll_action_id, 'U', :chunk_number, 1, 1, hr_dynsql.get_tax_unit(pay_asg.assignment_id, pay_pac.effective_date), 'U', null from per_periods_of_service pay_pos, per_all_assignments_f pay_asg, per_all_assignments_f pay_as2, pay_payroll_actions pay_pac where pay_pac.payroll_action_id = :payroll_action_id and pay_asg.payroll_id = pay_pac.payroll_id and pay_pac.effective_date between pay_asg.effective_start_date and pay_asg.effective_end_date and pay_as2.assignment_id = pay_asg.assignment_id and pay_pac.date_earned between pay_as2.effective_start_date and pay_as2.effective_end_date and pay_pos.period_of_service_id = pay_asg.period_of_service_id and pay_as2.period_of_service_id = pay_asg.period_of_service_id and pay_asg.person_id in ( select pay_pop.person_id from pay_population_ranges pay_pop where pay_pop.payroll_action_id = pay_pac.payroll_action_i d and pay_pop.chunk_number = :chunk) and hr_dynsql.person_sequence_locked(pay_pos.period_of_service_id + decode(pay_pos.person_id, null, 0, 0), pay_pac.effective_date) = 'N' order by pay_asg.person_id, decode(pay_asg.primary_flag, 'Y', 1, 2), pay_asg.assignment_id, 1 for update of pay_asg.assignment_id, pay_pos.period_of_service_id
bqnf41vhv041n	select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV PERA ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, EE PAY_ELEMENT_ENTRIES_F_PK, IV PAY_INPUT_VALUES_F_N1, PERA +*/ EL2.element_link_id , EL2.costable_type , EL2.cost_allocation_keyflex_id , EL2.balancing_keyflex_id , EL2.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , decode(:b0, 'A', decode(EE.entry_type, 'A', EE.target_entry_id, 'R', EE.target_entry_id, RR.source_id), RR.source_id) , decode(:b0, 'A', nvl(EE.entry_type, 'N'), 'N') , decode(RR.source_type, 'E', nvl(EE.creator_type, 'X'), 'I', decode(EE.creator_type, null , 'X', 'N')) , EE.cost_allocation_keyflex_id , RR.element_type_id , RRV.input_value_id , RRV.result_value , IV.uom , decode(EL2.costable_type, 'D', decode(decode(RR.source_type, 'E', EE.creator_type, 'N'), 'RR', 2, 'EE', 2, 'NR', 2, 'PR', 2, 3), 1) , RR.jurisdiction_code , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b2, 0, PA.date_earned, decode(RR.end_date, null , decode(substr(:b3, 1, 1), 'Y', nvl(EE.date_earned, PA.date_earned), PA.date_earned), hr_cost.get_cost_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned))), 'YYYY/MM/DD') , EL2.transfer_to_gl_flag , decode(:b4, null , null , hr_cost.get_context_value(:b4, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b6, null , null , hr_cost.get_context_value(:b6, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b8, null , null , hr_cost.get_context_value(:b8, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b10, null , null , hr_cost.get_context_value(:b10, RR.run_result_id, RR.element_type_id, PA.date_earned)) from pay_assignment_actions AA , pay_payroll_actions PA , per_all_assignments_f PERA , pay_run_results RR , pay_element_entries_f EE , pay_element_types_f ET , p ay_element_classifications EC , fnd_currencies FC , pay_input_values_f IV , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_element_links_f EL , pay_element_links_f EL2 where (((((((((((((((((((((((((((((((AA.assignment_action_id=:b12 and AA.payroll_action_id=PA.payroll_action_id) and RR.assignment_action_id=:b12) and RR.status like 'P%') and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between IV.effective_start_date and IV.effective_end_date) and IV.element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b14, 'Y', 1, fnd_number.canonical_to_number(RRV.result_value))+decode(LIV.DEFAULT_VALUE, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.sourc e_type, RR.end_date, PA.date_earned)) between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and decode(RR.entry_type, 'B', nvl(RR.element_entry_id, RR.source_id), RR.source_id)=EE.element_entry_id(+)) and decode(RR.end_date, null , hr_cost.get_rr_date_earn(RR.source_id, RR.source_type, to_date(:b15, 'YYYY/MM/DD')), hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, to_date(:b15, 'YYYY/MM/DD'))) between EE.effective_start_date(+) and EE.effective_end_date(+)) and RR.element_type_id=ET.element_type_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between ET.effective_start_date and ET.effective_end_date) and ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and (ET.element_type_id+0)=(EL.element_type_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source _id, RR.source_type, RR.end_date, PA.date_earned)) between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and ((PA.action_type='B' and hr_cost.cost_bal_adj(nvl(RR.element_entry_id, RR.source_id), PA.effective_date)='Y') or PA.action_type<>'B')) and PERA.assignment_id=:b17) and (PERA.business_group_id+0)=(EL.business_group_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PERA.effective_start_date and PERA.effective_end_date) and (((EE.element_entry_id is not null and RR.source_type='E') and EL.element_link_id=EE.element_link_id) or ((((((((((EE.element_entry_id is null or (EE.element_entry_id is not null and RR.source_type<>'E')) and (((EL.payroll_id is not null and EL.payroll_id=PERA.payroll_id) or (EL.link_to_all_payrolls_flag='Y' and PERA.payroll_id is not null )) or EL.payroll_id is null )) and (EL.organization_id=PERA.organization_id or EL.or ganization_id is null )) and (EL.position_id=PERA.position_id or EL.position_id is null )) and (EL.job_id=PERA.job_id or EL.job_id is null )) and (EL.grade_id=PERA.grade_id or EL.grade_id is null )) and (EL.location_id=PERA.location_id or EL.location_id is null )) and (EL.pay_basis_id=PERA.pay_basis_id or EL.pay_basis_id is null )) and (EL.employment_category=PERA.employment_category or EL.employment_category is null )) and (EL.people_group_id is null or exists (select 1 from pay_assignment_link_usages_f PAL where ((PAL.assignment_id=:b17 and PAL.element_link_id=EL.element_link_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PAL.effective_start_date and PAL.effective_end_date)))))) and EL2.element_link_id=EL.element_link_id) and decode(:b2, 0, PA.date_earned, decode(EL.costable_type, 'D', PA.date_earned, decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)))) between EL2.effective_start_date and EL2.effective_end_date) and (:b20=0 or (:b20<>0 and exists (select null from pay_ele_classification_rules ECR where ((ECR.element_set_id=:b20 and ET.classification_id=ECR.classification_id) and not exists (select null from pay_element_type_rules ETR where ((ETR.element_set_id=:b20 and ETR.element_type_id=ET.element_type_id) and ETR.include_or_exclude='E'))) union select null from pay_element_type_rules ETR where ((ETR.element_set_id=:b20 and ETR.element_type_id=ET.element_type_id) and ETR.include_or_exclude='I'))))) order by 17, 8, 14
bu7n1scnadudx	SELECT ORG.LEGISLATION_CODE FROM PAY_ASSIGNMENT_ACTIONS ACT, PAY_PAYROLL_ACTIONS PACT, PER_BUSINESS_GROUPS_PERF ORG WHERE ACT.ASSIGNMENT_ACTION_ID=:B1 AND PACT.PAYROLL_ACTION_ID=ACT.PAYROLL_ACTION_ID AND ORG.BUSINESS_GROUP_ID=PACT.BUSINESS_GROUP_ID
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;
d4y3b0qsr13x1	SELECT DISTINCT 'Y', 'FF ROUTE:USER_TIME' FROM PAY_ELEMENT_ENTRIES_F PEE_CURRENT, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_CLASSIFICATIONS PEC, HR_LOOKUPS HL WHERE PEE_CURRENT.ASSIGNMENT_ID = :B3 AND PEE_CURRENT.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID AND PEE_CURRENT.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND (( EXISTS (SELECT 1 FROM PAY_ELEMENT_ENTRIES_F PEE WHERE PEE.ELEMENT_ENTRY_ID = :B4 AND PEE.ASSIGNMENT_ID = PEE_CURRENT.ASSIGNMENT_ID AND :B2 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND :B2 BETWEEN PEE_CURRENT.EFFECTIVE_START_DATE AND PEE_CURRENT.EFFECTIVE_END_DATE AND PEE.ELEMENT_TYPE_ID <> PEE_CURRENT.ELEMENT_TYPE_ID AND NVL(PEE.CREATOR_TYPE, 'Z') NOT IN ('P', 'R', 'EE', 'RR', 'PR', 'NR') AND NVL(PEE_CURRENT.CREATOR_TYPE, 'Z') NOT IN ('P', 'R', 'EE', 'RR', 'PR', 'NR') ) ) OR ( EXISTS (SELECT 1 FROM PAY_ELEMENT_ENTRIES_F PEE , PAY_ELEMENT_ENTRIES_F PEE_RETRO , PAY_ASSIGNMENT _ACTIONS PAA , PAY_PAYROLL_ACTIONS PPA , PAY_RUN_RESULTS PRR WHERE PEE.ELEMENT_ENTRY_ID = :B4 AND PEE.ASSIGNMENT_ID = PEE_CURRENT.ASSIGNMENT_ID AND PEE.CREATOR_TYPE IN ('P', 'R', 'EE', 'RR', 'PR', 'NR') AND PEE.ELEMENT_ENTRY_ID = PRR.SOURCE_ID AND PRR.SOURCE_TYPE IN ('E', 'I') AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) > :B2 AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND PEE.ELEMENT_TYPE_ID = PEE_RETRO.ELEMENT_TYPE_ID AND PEE.ASSIGNMENT_ID = PEE_RETRO.ASSIGNMENT_ID AND :B2 BETWEEN PEE_RETRO.EFFECTIVE_START_DATE AND PEE_RETRO.EFFECTIVE_END_DATE AND PEE.ELEMENT_ENTRY_ID <> PEE_CURRENT.ELEMENT_ENTRY_ID ) )) AND :B2 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND NVL(PET.BUSINESS_GROUP_ID, :B1 ) = :B1 AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID AND PEC.CLASSIFICATION_NAME IN ('Earning s', 'Supplemental Earnings') AND PEC.BUSINESS_GROUP_ID IS NULL AND PET.ELEMENT_INFORMATION9 = 'T' AND HL.LOOKUP_CODE = PET.ELEMENT_INFORMATION1 AND HL.LOOKUP_TYPE IN ('US_EARNINGS', 'CA_REGULAR_EARNINGS', 'CA_SUPPLEMENTAL_EARNINGS') AND HL.LOOKUP_CODE = DECODE(PEC.LEGISLATION_CODE, 'US', 'REG', PET.ELEMENT_INFORMATION1)
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.INP UT_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;
f87337d0g8ps6	SELECT DISTINCT SIT_EXISTS FROM PAY_US_STATE_TAX_INFO_F WHERE STATE_CODE = SUBSTR(:B2 , 1, 2) AND TO_DATE(:B1 , 'dd-mm-yyyy') BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
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 )
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

Back to SQL Statistics
Back to Top

Instance Activity Statistics

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

Key Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

• Ordered by statistic name

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

• IOStat by Function summary
• IOStat by Filetype summary
• IOStat by Function/Filetype summary
• Tablespace IO Stats
• File IO Stats

IOStat by Function summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Small Read and Large Read are average service times, in milliseconds
• Ordered by (Data Read + Write) desc

Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

• 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
• Ordered by (Data Read + Write) desc for each function

Back to IO Stats
Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File