WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
AFGPROD	2478170099	afgprod	1	04-Apr-18 07:04	11.2.0.3.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
DB-PROD01	Microsoft Windows x86 64-bit	32	4		32.00

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	31983	24-Apr-18 10:30:29	522	25.0
End Snap:	31984	24-Apr-18 10:39:47	521	25.7
Elapsed:		9.31 (mins)
DB Time:		70.18 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	5,736M	5,736M	Std Block Size:	8K
Shared Pool Size:	1,808M	1,808M	Log Buffer:	12,124K

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	7.5	1.1	0.00	0.01
DB CPU(s):	5.4	0.8	0.00	0.00
Redo size:	86,208.6	12,985.6
Logical reads:	144,172.4	21,716.7
Block changes:	496.8	74.8
Physical reads:	4,052.7	610.5
Physical writes:	50.6	7.6
User calls:	1,261.9	190.1
Parses:	186.7	28.1
Hard parses:	1.4	0.2
W/A MB processed:	14.0	2.1
Logons:	0.5	0.1
Executes:	2,963.4	446.4
Rollbacks:	1.2	0.2
Transactions:	6.6

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.99	In-memory Sort %:	100.00
Library Hit %:	99.93	Soft Parse %:	99.27
Execute to Parse %:	93.70	Latch Hit %:	99.70
Parse CPU to Parse Elapsd %:	76.24	% Non-Parse CPU:	99.79

Shared Pool Statistics

	Begin	End
Memory Usage %:	81.77	82.04
% SQL with executions>1:	92.09	95.40
% Memory for SQL w/exec>1:	89.19	92.99

Top 5 Timed Foreground Events

Host CPU (CPUs: 32 Cores: 4 Sockets: )

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
		16.6	1.1		82.3

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
16.9	95.4	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	32,767.2	32,767.2
SGA use (MB):	7,648.0	7,648.0
PGA use (MB):	2,085.9	2,057.2
% Host Mem used for SGA+PGA:	29.71	29.62

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): 4210.7s
• 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 99.0% of Total DB time 4,210.66 (s)
• Total FG Wait Time: 1,153.89 (s) DB CPU time: 3,015.14 (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 69.3% of Total DB Time (s): 4,211
• Captured PL/SQL account for 2.0% of Total DB Time (s): 4,211

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 74.7% of Total CPU Time (s): 3,015
• Captured PL/SQL account for 2.8% of Total CPU Time (s): 3,015

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 69.0% of Total User I/O Wait Time (s): 107
• Captured PL/SQL account for 0.0% of Total User I/O Wait Time (s): 107

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: 80,547,361
• Captured SQL account for 95.5% 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: 2,264,180
• Captured SQL account for 99.8% 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: 23,202
• Captured SQL account for 79.4% of Total
• Total UnOptimized Read Requests: 23,202
• Captured SQL account for 79.4% 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: 1,655,624
• Captured SQL account for 59.2% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 104,311
• Captured SQL account for 30.3% 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

• Only Statements with Version Count greater than 20 are displayed

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
08ytr12xk17pu	BEGIN :1 := rw_server.remove_job(:2, :3); END;
0au6nkbj88n0q	SELECT ie . ird_emp_first_name , ie . ird_emp_last_name , ie . ird_employee_no , pck_util . get_ird_employee_name ( ie . IRD_EMPLOYEE_NO ) EMP_FL_NAME , ie . ird_login_id , tc . tax_centre_no , f_translate ( tc . tax_centre_desc , : p_lang_no , 'tax_centre' ) tax_centre_desc , f_translate ( ed . emp_department_desc , : p_lang_no , 'emp_department' ) emp_department , MAX ( TO_CHAR ( audit_timestamp , 'YYYY-MM-DD' ) ) last_login FROM ird_employee ie , tax_centre tc , emp_department ed , sigtas_audit_user au WHERE ie.tax_centre_no = tc.tax_centre_no AND ie.emp_department_no = ed.emp_department_no (+) and ie.ird_employee_no = au.ird_employee_no AND ( ie.ird_login_id = : p_ird_login_id or : p_ird_login_id is null ) AND ( ed.emp_department_no = : p_emp_department_no or : p_emp_department_no is null ) group by ie.ird_emp_first_name , ie.ird_emp_last_name , ie.ird_employee_no , pck_util.get_ird_employee_name ( ie.IRD_EMPLOYEE_NO ) , ie.ird_login_id , tc.tax_centre_no , f_translate ( tc.tax_centre_desc , : p_lang_no , 'tax_centre' ) , f_translate ( ed.emp_department_desc , : p_lang_no , 'emp_department' ) ORDER BY 6 ASC, 7 ASC , ie.ird_login_id asc
0kuu24nd5fj1p	SELECT DISTINCT dcmt.doc_no doc_no1, dcmt.ext_doc_no ext_doc_no, pck_util.get_ext_doc_no_for_bar_code(dcmt.ext_doc_no) ext_doc_no_bar_code, dcmt.created_date created_date1, dcmt.doc_tp_month || ' ' || to_char(dcmt.doc_tp_year, '9999') tax_period1, f_translate(decode(dcmt.doc_tp_week, 1, 'First Week of ', 2, 'Second Week of ', 3, 'Third Week of ', NULL), :p_lang_no) doc_tp_week1, to_char(dcmt.doc_tp_start_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_start_date1, to_char(dcmt.doc_tp_end_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_end_date1, to_char(dcmt.doc_tp_due_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_due_date1, to_char(dcmt.doc_tp_payment_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_payment_date1, to_char(sysdate, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) v_current_date, dcmt.doc_tp_month doc_tp_month, dcmt.doc_tp_year doc_tp_year, tper.tper_month tper_month, tper.tper_year tper_year, ass.tax_account_no, ass.reception_date reception_date1, ass.entry_date entry_date1, ass.last_calc_date last_calc_date1, ass.assess_comment ass_comment1, dcmt.tax_payer_no tax_payer_no1, dcmt.tax_type_no tax_type_no1, dcmt.lic_base_no lic_base_no1, dcmt.tax_centre_no tax_centre_no1, dcmt.receipt receipt1, dcmt.assess_no assess_no1, dcmt.tax_period_no tax_period_no1, dcmt.estab_no estab_no1, dcmt.personal_text personal_text1, dcmt.doc_tp_payment_date doc_tp_payment_date1, taxac.tax_type_no tax_type, taxac.rep_name taxac_rep_name1, taxac.mailing_address taxac_mailing_address1, tax_p.rep_tax_payer_no tax_p_rep_tax_payer_no1, tax_p.rep_taxr_name tax_p_rep_name1, tax_p.mailing_address tax_p_mailing_address1, tax_p.postal_code tax_p_postal_code, tax_p.parcel_no tax_p_parcel_no, f_translate(city1.city_name, :p_lang_no, 'city') taxac_city_name1, f_translate(city.city_name, :p_lang_no, 'city') tax_p_city_name1, f_translate(loc1.locality_desc, :p_lang_no, 'locality') loc1_locality_desc, f_translate(loc2.locality_desc, :p_lang_no, 'locality') loc2_locality_desc, f_translate(par1.parish_name, :p_lang_no, 'parish') taxac_parish_name1, f_translate(par.parish_name, :p_lang_no, 'parish') tax_p_parish_name1, post_code1.post_code_code taxac_post_code1, post_code.post_code_code tax_p_post_code1, f_translate(cntry1.country_name, :p_lang_no, 'COUNTRY') taxac_country_name1, f_translate(cntry.country_name, :p_lang_no, 'COUNTRY') tax_p_country_name1, pck_util.GET_TAXP_NAME_BY_LANG_TRANS(tax_p.tax_payer_no, 1, 'N', :p_lang_no) tax_payer_name1, -- fs jan 2012 dcmt.tax_payer_name tax_payer_name2, ind.tax_payer_no ind_tax_payer_no1, ind.work_phone work_phone, ind.phone_ext phone_ext, ind.fax_no fax_no, decode(tax_p.tp_type_no, 1, ind.mobile_phone, ent.mobile_phone) mobile_phone, ind.home_phone home_phone, estab.name estab_name1, estab.address estab_address1, estab.phone_no estab_phone_no1, ent.enterprise_no, nls_initcap(lic_base.based_on) based_on1, ass.letter_no ass_letter_no, decode_desc('LETTER', 'LETTER_TITLE', 'LETTER_NO', lett.letter_no, :p_lang_no) letter_title1, decode_desc('LETTER', 'LETTER_TEXT', 'LETTER_NO', lett.letter_no, :p_lang_no) letter_text1, lett.let_type_no letter_type_no1, taxac.locality_no taxac_loc_no1, taxac.tax_account_no taxac_tax_account_no, taxac.tax_type_no, tax_p.locality_no tax_p_loc_no1, tax_p.door_no tax_p_door_no, tax_p.street_no tax_p_street_no, tax_p.fiscal_no tax_p_fiscal_no, tax_p.use_local_date, taxac.street_no taxac_street_no, taxac.po_box taxac_po_box, taxac.kebele_desc taxac_kebele_desc, ' ' || f_translate(tax_centre_desc, :p_lang_no, 'TAX_CENTRE') tax_centre_desc, tax_p.po_box tax_p_po_box, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) tax_due1, - (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) int_due1, - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) pen_due1, - ((nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + NVL(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) total_due1, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) tax_to_pay1, nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) tax_applied1, - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) pen_to_pay1, nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) pen_applied1, - (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) int_to_pay1, nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) int_applied1, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) total_to_pay1, nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) total_applied1, -( ( ( - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) ))* 95/100 as pen_off95, -((- (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0 ) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) ))* 95/100 as int_off95, ( ( ( - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) ))* 5/100 as pen_in5, ((- (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) ))* 5/100 as int_in5, ((- ((nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)+ NVL(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) )) -( ( ( ( - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) )+( - (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) ))* 95/100 ) ) total_due3, ass.assess_type_no assess_type_no1, mv_man.desc_tx || ' ' || mv_model.model_desc mv_manufacturer_desc, f_translate(mv_type.desc_tx, :p_lang_no, 'vehicle_type ') mv_type_veh, mv.year || ' - ' || mv.chassis_no mv_year_chassis, mv_plate.plate_prefix || ' ' || mv_plate.plate_no || ' ' || mv_plate.plate_suffix mv_plate_no, ass.license_no, ass.taxable_object_no, dcmt.license_no license_no1, pck_util.get_cofi(1, tax_p.tax_payer_no) cofi, tax_p.send_corr_to_repr, tax_p.tp_type_no, ass.collection_location_no, taxac.control_unit_no, ass.assess_comment, ass.assess_no, ass.assess_new_assess_no reassess_no, tt.use_criteria, f_translate(tt.tax_type_desc, :p_lang_no, 'tax_type') tax_type_desc, pck_object.get_taxable_object_name(dcmt.taxable_object_no, :p_lang_no) taxable_object_id, dcmt.taxable_object_no, nvl(tt.tax_type_reference_no, 0) tax_type_reference_no, -- NO VALUE VERY IMPORTANT FS 2008-07-18 dt.is_payment_doc_fl is_payment_doc_fl, dcmt.doc_type_no, tax_p.CASE_MANAGEMENT_GROUP_NO, ind.address_e_mail, dcmt.form_no, dcmt.version_no, decode(tax_p.tp_type_no, 2, nvl2(ttr.mailing_address, ttr.mailing_address, tax_p.mailing_address), nvl2(itr.resident_address, itr.resident_address, tax_p.mailing_address) ) street -- fs mar 2012 , decode(tax_p.tp_type_no, 2, nvl2(ttr.area_village, ttr.area_village, tax_p.area_village), nvl2(itr.area_village, itr.area_village, tax_p.area_village) ) area_village -- fs mar 2012 FROM city city, tax_centre tax_centre, locality loc1, locality loc2, parish par, postal_code post_code, country cntry, city city1, parish par1, postal_code post _code1, country cntry1, corporation corp, individual ind, ENTERPRISE ENT, establishment estab, lic_base lic_base, letter lett, tax_account taxac, tax_payer tax_p, model_type mv_model, plate_no mv_plate, mv_reg_no mv_reg, vehicle_type mv_type, mv_manufacturer mv_man, motor_vehicle mv, taxable_object tax_obj, assessment ass, document dcmt, tax_period tper, tax_type tt, doc_type dt, taxpyr_transl ttr, indv_transl itr WHERE ( ind.tax_payer_no (+) = tax_p.tax_payer_no AND corp.tax_payer_no (+) = tax_p.tax_payer_no AND ENT.TAX_PAYER_NO (+) = TAX_P.TAX_PAYER_NO and tax_p.tax_payer_no = ttr.tax_payer_no (+) and tax_p.lang_no = ttr.lang_no (+) and tax_p.tax_payer_no = itr.tax_payer_no (+) and tax_p.lang_no = itr.lang_no (+) AND par.parish_no (+) = city.parish_no AND post_code.post_code_no (+) = tax_p.post_code_no AND loc2.locality_no (+) = tax_p.locality_no AND city.city_no (+) = tax_p.city_no AND cntry.country_no (+) = tax_p.country_no AND par1.parish_no (+) = city1.parish_no AND post_code1.post_code_no (+) = taxac.post_code_no AND loc1.locality_no (+) = taxac.locality_no AND city1.city_no (+) = taxac.city_no AND cntry1.country_no (+) = taxac.country_no AND lett.letter_no = dcmt.letter_no AND estab.estab_no (+) = dcmt.estab_no and dcmt.doc_tp_year <= 1396 and ( TT.TAX_BASIS_NO = 1 or ent.main_enterprise = upper ( 'Y' ) or tax_p.tp_type_no = 1 ) AND lic_base.lic_base_no (+) = dcmt.lic_base_no AND taxac.tax_centre_no (+) = dcmt.tax_centre_no AND mv.model_id = mv_model.model_id (+) AND mv_reg.plate_no_id = mv_plate.plate_no_id (+) AND ( ( mv_reg.mv_id IS NULL ) OR ( mv_reg.mv_id IS NOT NULL AND ( mv_reg.end_date >= dcmt.doc_tp_start_date OR mv_reg.end_date IS NULL ) ) OR ( mv_reg.mv_id IS NOT NULL AND dcmt.doc_tp_start_date IS NULL ) ) AND ( ( mv_reg.mv_id IS NULL ) OR ( mv_reg.mv_id IS NOT NULL AND mv_reg.start_date <= dcmt.doc_tp_start_date ) OR ( mv_reg.mv_id IS NOT NULL AND dcmt.doc_tp_start_date IS NULL ) ) AND mv.id = mv_reg.mv_id (+) AND mv.veh_typ_id = mv_type.id (+) AND mv.mv_man_id = mv_man.id (+) AND tax_obj.mv_id = mv.id (+) AND dcmt.taxable_object_no = tax_obj.taxable_object_no (+) AND tax_centre.tax_centre_no = dcmt.tax_centre_no AND taxac.tax_payer_no (+) = dcmt.tax_payer_no AND taxac.tax_type_no (+) = dcmt.tax_type_no AND ass.assess_no (+) = dcmt.assess_no AND tax_p.tax_payer_no (+) = dcmt.tax_payer_no AND tper.tax_period_no (+) = ass.range_start_tax_period_no AND tt.tax_type_no (+) = dcmt.tax_type_no AND dcmt.doc_type_no = dt.doc_type_no (+) and ( : p_user_sensitive_fl = upper ( 'y' ) or tax_p.sensitive = upper ( 'n' ) ) ) AND ( :DOC_NO = dcmt.doc_no) union SELECT DISTINCT dcmt.doc_no doc_no1, dcmt.ext_doc_no ext_doc_no, pck_util.get_ext_doc_no_for_bar_code(dcmt.ext_doc_no) ext_doc_no_bar_code, dcmt.created_date created_date1, dcmt.doc_tp_month || ' ' || to_char(dcmt.doc_tp_year, '9999') tax_period1, f_translate(decode(dcmt.doc_tp_week, 1, 'First Week of ', 2, 'Second Week of ', 3, 'Third Week of ', NULL), :p_lang_no) doc_tp_week1, to_char(dcmt.doc_tp_start_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_start_date1, to_char(dcmt.doc_tp_end_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_end_date1, to_char(dcmt.doc_tp_due_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_due_date1, to_char(dcmt.doc_tp_payment_date, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) doc_tp_payment_date1, to_char(sysdate, pck_util.F_GET_SIGTAS_RULES_VALUE('DATE_FORMAT_GENERAL')) v_current_date, dcmt.doc_tp_month doc_tp_month, dcmt.doc_tp_year doc_tp_year, tper.tper_month tper_month, tper.tper_year tper_year, ass.tax_account_no, ass.reception_date reception_date1, ass.entry_date entry_date1, ass.last_calc_date last_calc_date1, ass.assess_comment ass_comment1, dcmt.tax_payer_no tax_payer_no1, dcmt.tax_type_no tax_type_no1, dcmt.lic_base_no lic_base_no1, dcmt.tax_centre_no tax_centre_no1, dcmt.receipt rece ipt1, dcmt.assess_no assess_no1, dcmt.tax_period_no tax_period_no1, dcmt.estab_no estab_no1, dcmt.personal_text personal_text1, dcmt.doc_tp_payment_date doc_tp_payment_date1, taxac.tax_type_no tax_type, taxac.rep_name taxac_rep_name1, taxac.mailing_address taxac_mailing_address1, tax_p.rep_tax_payer_no tax_p_rep_tax_payer_no1, tax_p.rep_taxr_name tax_p_rep_name1, tax_p.mailing_address tax_p_mailing_address1, tax_p.postal_code tax_p_postal_code, tax_p.parcel_no tax_p_parcel_no, f_translate(city1.city_name, :p_lang_no, 'city') taxac_city_name1, f_translate(city.city_name, :p_lang_no, 'city') tax_p_city_name1, f_translate(loc1.locality_desc, :p_lang_no, 'locality') loc1_locality_desc, f_translate(loc2.locality_desc, :p_lang_no, 'locality') loc2_locality_desc, f_translate(par1.parish_name, :p_lang_no, 'parish') taxac_parish_name1, f_translate(par.parish_name, :p_lang_no, 'parish') tax_p_parish_name1, post_code1.post_code_code taxac_post_code1, post_code.post_code_code tax_p_post_code1, f_translate(cntry1.country_name, :p_lang_no, 'COUNTRY') taxac_country_name1, f_translate(cntry.country_name, :p_lang_no, 'COUNTRY') tax_p_country_name1, pck_util.GET_TAXP_NAME_BY_LANG_TRANS(tax_p.tax_payer_no, 1, 'N', :p_lang_no) tax_payer_name1, -- fs jan 2012 dcmt.tax_payer_name tax_payer_name2, ind.tax_payer_no ind_tax_payer_no1, ind.work_phone work_phone, ind.phone_ext phone_ext, ind.fax_no fax_no, decode(tax_p.tp_type_no, 1, ind.mobile_phone, ent.mobile_phone) mobile_phone, ind.home_phone home_phone, estab.name estab_name1, estab.address estab_address1, estab.phone_no estab_phone_no1, ent.enterprise_no, nls_initcap(lic_base.based_on) based_on1, ass.letter_no ass_letter_no, decode_desc('LETTER', 'LETTER_TITLE', 'LETTER_NO', lett.letter_no, :p_lang_no) letter_title1, decode_desc('LETTER', 'LETTER_TEXT', 'LETTER_NO', lett.letter_no, :p_lang_no) letter_text1, lett.let_type_no letter_type_no1, taxac.locality_no taxac_loc_no1, taxac.tax_account_no taxac_tax_account_no, taxac.tax_type_no, tax_p.locality_no tax_p_loc_no1, tax_p.door_no tax_p_door_no, tax_p.street_no tax_p_street_no, tax_p.fiscal_no tax_p_fiscal_no, tax_p.use_local_date, taxac.street_no taxac_street_no, taxac.po_box taxac_po_box, taxac.kebele_desc taxac_kebele_desc, ' ' || f_translate(tax_centre_desc, :p_lang_no, 'TAX_CENTRE') tax_centre_desc, tax_p.po_box tax_p_po_box, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) tax_due1, - (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) int_due1, - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) pen_due1, - ((nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (as s.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + NVL(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) total_due1, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) tax_to_pay1, nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) tax_applied1, - (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) pen_to_pay1, nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) pen_applied1, - (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) int_to_pay1, nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) int_applied1, - (nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) total_to_pay1, nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) total_applied1, 0 as pen_off95, 0 as int_off95, 0 as pen_in5, 0 as int_in5, - ((nvl(pck_transaction.TAX_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.TAX_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.INT_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + nvl(pck_transaction.INT_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0)) + (nvl(pck_transaction.PEN_DUE_ON_TAX_PERIOD (ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0) + NVL(pck_transaction.PEN_PAID_ON_TAX_PERIOD(ass.tax_account_no, ass.tax_period_no, ass.estab_no, ass.taxable_object_no, ass.license_no, NULL), 0))) total_due3, ass.assess_type_no assess_type_no1, mv_man.desc_tx || ' ' || mv_model.model_desc mv_manufacturer_desc, f_translate(mv_type.desc_tx, :p_lang_no, 'vehicle_type ') mv_type_veh, mv.year || ' - ' || mv.chassis_no mv_year_chassis, mv_plate.plate_prefix || ' ' || mv_plate.plate_no || ' ' || mv_plate.plate_suffix mv_plate_no, ass.license_no, ass.taxable_object_no, dcmt.license_no license_no1, pck_util.get_cofi(1, tax_p.tax_payer_no) cofi, tax_p.send_corr_to_repr, tax_p.tp_type_no, ass.collection_location_no, taxac.control_unit_no, ass.assess_comment, ass.assess_no, ass.assess_new_assess_no reassess_no, tt.use_criteria, f_translate(tt.tax_type_desc, :p_lang_no, 'tax_type') tax_type_desc, pck_object.get_taxable_object_name(dcmt.taxable_object_no, :p_lang_no) taxable_object_id, dcmt.taxable_object_no, nvl(tt.tax_type_reference_no, 0) tax_t ype_reference_no, -- NO VALUE VERY IMPORTANT FS 2008-07-18 dt.is_payment_doc_fl is_payment_doc_fl, dcmt.doc_type_no, tax_p.CASE_MANAGEMENT_GROUP_NO, ind.address_e_mail, dcmt.form_no, dcmt.version_no, decode(tax_p.tp_type_no, 2, nvl2(ttr.mailing_address, ttr.mailing_address, tax_p.mailing_address), nvl2(itr.resident_address, itr.resident_address, tax_p.mailing_address) ) street -- fs mar 2012 , decode(tax_p.tp_type_no, 2, nvl2(ttr.area_village, ttr.area_village, tax_p.area_village), nvl2(itr.area_village, itr.area_village, tax_p.area_village) ) area_village -- fs mar 2012 FROM city city, tax_centre tax_centre, locality loc1, locality loc2, parish par, postal_code post_code, country cntry, city city1, parish par1, postal_code post_code1, country cntry1, corporation corp, individual ind, ENTERPRISE ENT, establishment estab, lic_base lic_base, letter lett, tax_account taxac, tax_payer tax_p, model_type mv_model, plate_no mv_plate, mv_reg_no mv_reg, vehicle_type mv_type, mv_manufacturer mv_man, motor_vehicle mv, taxable_object tax_obj, assessment ass, document dcmt, tax_period tper, tax_type tt, doc_type dt, taxpyr_transl ttr, indv_transl itr WHERE ( ind.tax_payer_no (+) = tax_p.tax_payer_no AND corp.tax_payer_no (+) = tax_p.tax_payer_no AND ENT.TAX_PAYER_NO (+) = TAX_P.TAX_PAYER_NO and tax_p.tax_payer_no = ttr.tax_payer_no (+) and tax_p.lang_no = ttr.lang_no (+) and tax_p.tax_payer_no = itr.tax_payer_no (+) and tax_p.lang_no = itr.lang_no (+) AND par.parish_no (+) = city.parish_no AND post_code.post_code_no (+) = tax_p.post_code_no AND loc2.locality_no (+) = tax_p.locality_no AND city.city_no (+) = tax_p.city_no AND cntry.country_no (+) = tax_p.country_no AND par1.parish_no (+) = city1.parish_no AND post_code1.post_code_no (+) = taxac.post_code_no AND loc1.locality_no (+) = taxac.locality_no AND city1.city_no (+) = taxac.city_no AND cntry1.country_no (+) = taxac.country_no AND lett.letter_no = dcmt.letter_no AND estab.estab_no (+) = dcmt.estab_no and dcmt.doc_tp_year >= 1397 and ( TT.TAX_BASIS_NO = 1 or ent.main_enterprise = upper ( 'Y' ) or tax_p.tp_type_no = 1 ) AND lic_base.lic_base_no (+) = dcmt.lic_base_no AND taxac.tax_centre_no (+) = dcmt.tax_centre_no AND mv.model_id = mv_model.model_id (+) AND mv_reg.plate_no_id = mv_plate.plate_no_id (+) AND ( ( mv_reg.mv_id IS NULL ) OR ( mv_reg.mv_id IS NOT NULL AND ( mv_reg.end_date >= dcmt.doc_tp_start_date OR mv_reg.end_date IS NULL ) ) OR ( mv_reg.mv_id IS NOT NULL AND dcmt.doc_tp_start_date IS NULL ) ) AND ( ( mv_reg.mv_id IS NULL ) OR ( mv_reg.mv_id IS NOT NULL AND mv_reg.start_date <= dcmt.doc_tp_start_date ) OR ( mv_reg.mv_id IS NOT NULL AND dcmt.doc_tp_start_date IS NULL ) ) AND mv.id = mv_reg.mv_id (+) AND mv.veh_typ_id = mv_type.id (+) AND mv.mv_man_id = mv_man.id (+) AND tax_obj.mv_id = mv.id (+) AND dcmt.taxable_object_no = tax_obj.taxable_object_no (+) AND tax_centre.tax_centre_no = dcmt.tax_centre_no AND taxac.tax_payer_no (+) = dcmt.tax_payer_no AND taxac.tax_type_no (+) = dcmt.tax_type_no AND ass.assess_no (+) = dcmt.assess_no AND tax_p.tax_payer_no (+) = dcmt.tax_payer_no AND tper.tax_period_no (+) = ass.range_start_tax_period_no AND tt.tax_type_no (+) = dcmt.tax_type_no AND dcmt.doc_type_no = dt.doc_type_no (+) and ( : p_user_sensitive_fl = upper ( 'y' ) or tax_p.sensitive = upper ( 'n' ) ) ) AND ( :DOC_NO = dcmt.doc_no)
0pvck8sx0cz3g	( SELECT TTP.END_DATE PI_CHNG_DATE FROM TAX_TYPE_PEN TTP , PENALTY P WHERE P.PEN_NO = TTP.PEN_NO AND TRUNC(TTP.END_DATE) BETWEEN TRUNC(:B4 ) + 1 AND TRUNC(:B3 ) - 1 AND P.PEN_WHEN_NO = :B2 AND TTP.TAX_TYPE_NO = :B1 AND (P.PEN_WHEN_NO != 5 OR P.PEN_WHERE_NO != 5)) UNION (SELECT ADD_MONTHS(:B5 , NVL(P.MAXIMUM, 0)) PI_CHNG_DATE FROM TAX_TYPE_PEN TTP , PENALTY P WHERE P.PEN_NO = TTP.PEN_NO AND ADD_MONTHS(:B5 , NVL(P.MAXIMUM, 0)) BETWEEN TRUNC(:B4 ) + 1 AND TRUNC(:B3 ) - 1 AND P.PEN_WHEN_NO = 5 AND TTP.TAX_TYPE_NO = :B1 AND (P.PEN_MAX_TYPE_NO = 3)) UNION (SELECT DD.D PI_CHNG_DATE FROM TAX_TYPE_PEN TTP , PENALTY P , (SELECT (TRUNC(:B4 ) + ROWNUM) AS D FROM ALL_OBJECTS WHERE TRUNC(:B4 ) + ROWNUM <= TRUNC(:B3 ) - 1) DD WHERE P.PEN_NO = TTP.PEN_NO AND P.PEN_WHEN_NO = :B2 AND TTP.TAX_TYPE_NO = :B1 AND (P.PEN_WHEN_NO = 5 AND P.PEN_WHERE_NO = 5) AND (TTP.END_DATE IS NULL OR DD.D <= TTP.END_DATE) AND (P.PEN_MAX_TYPE_NO != 3 OR (P.PEN_MAX_TYPE_NO = 3 AND DD.D <= ADD_MONTHS(:B5 , NVL(P.MAXIMUM, 0))))) UNION (SELECT TTI.END_DATE FROM TAX_TYPE_INT TTI , INTEREST I WHERE I.INT_NO = TTI.INT_NO AND TRUNC(TTI.END_DATE) BETWEEN TRUNC(:B4 ) + 1 AND TRUNC(:B3 ) - 1 AND I.INT_WHEN_NO = :B6 AND TTI.TAX_TYPE_NO = :B1 AND (I.INT_WHEN_NO != 2 OR I.INT_WHERE_NO NOT IN (3, 5))) UNION (SELECT DD.D FROM TAX_TYPE_INT TTI , INTEREST I , (SELECT (TRUNC(:B4 ) + ROWNUM) AS D FROM ALL_OBJECTS WHERE TRUNC(:B4 ) + ROWNUM <= TRUNC(:B3 ) - 1) DD WHERE I.INT_NO = TTI.INT_NO AND I.INT_WHEN_NO = :B6 AND TTI.TAX_TYPE_NO = :B1 AND (I.INT_WHEN_NO = 2 AND I.INT_WHERE_NO IN (3, 5)) AND (TTI.END_DATE IS NULL OR DD.D <= TTI.END_DATE)) UNION (SELECT DISTINCT TT.TRANS_DATE FROM TAX_TRANSACTION TT , TAX_SUB_TRANS TST WHERE TT.TAX_TRANS_NO = TST.TAX_TRANS_NO AND TRUNC(TT.TRANS_DATE) < TRUNC(:B3 ) AND (:B4 IS NULL OR TRUNC(TT.TRANS_DATE) > TRUNC(:B4 )) AND TST.TAX_ACCOUNT_NO = :B8 AND TST.TAX_PERIOD_NO = :B7 ) UNION (SELECT START_DATE - 1 FROM TAXA_INACTIVE_PERIOD WHERE (ESTAB_NO = :B11 OR :B11 IS NULL) AND (LICENSE_NO = :B10 OR :B10 IS NULL) AND (TAXABLE_OBJECT_NO = :B9 OR :B9 IS NULL) AND (TAX_PERIOD_NO IS NULL OR TAX_PERIOD_NO = :B7 OR :B7 IS NULL) AND TRUNC(START_DATE) - 1 BETWEEN TRUNC(:B4 ) + 1 AND TRUNC(:B3 ) - 1 AND EXEMP_TYPE_NO = 2 AND TAX_ACCOUNT_NO = :B8 ) UNION (SELECT CLOSE_DATE - 1 FROM TAX_ACCOUNT WHERE TRUNC(CLOSE_DATE) - 1 BETWEEN TRUNC(:B4 ) + 1 AND TRUNC(:B3 ) - 1 AND TAX_ACCOUNT_NO = :B8 ) ORDER BY 1
0stb50vksmpk0	INSERT INTO TAX_TRANSACTION ( TAX_TRANS_NO , IRD_EMPLOYEE_NO , TRANS_DATE , ENTER_DATE , ENTER_TIME , AMOUNT , TAX_PAYER_NO , TAX_TYPE_NO , ESTAB_NO ) VALUES ( :1 , :1 , :1 , :1 , :1 , :1 , :1 , :1 , :1 )
0xrs3qyg61zmg	SELECT COUNT (*) FROM DOCUMENT WHERE EXT_DOC_NO = DOCUMENT.EXT_DOC_NO AND DOC_TYPE_NO = 1
11y1nsf0n0j2s	SELECT SENSITIVE FROM TAX_PAYER WHERE (TAX_PAYER_NO = :B1 OR :B1 IS NULL) AND (FISCAL_NO = :B2 OR :B2 IS NULL) AND NOT (:B2 IS NULL AND :B1 IS NULL)
12mu9kub9c0v0	SELECT SDO.DESCRIPTION, 1 ORDRE FROM SIGTAS_DICT SD, SIGTAS_DICT_OTHER SDO WHERE SD.SIGTAS_DICT_NO = SDO.SIGTAS_DICT_NO AND SD.DESCRIPTION_UPPER = :B3 AND SDO.LANG_NO = :B2 AND (SD.CONTEXT = :B1 AND SD.CONTEXT IS NOT NULL) AND SD.MESSAGE_CODE IS NULL UNION ALL SELECT SDO.DESCRIPTION, 2 ORDRE FROM SIGTAS_DICT SD, SIGTAS_DICT_OTHER SDO WHERE SD.SIGTAS_DICT_NO = SDO.SIGTAS_DICT_NO AND SD.DESCRIPTION_UPPER = :B3 AND SDO.LANG_NO = :B2 AND SD.CONTEXT IS NULL AND SD.MESSAGE_CODE IS NULL ORDER BY 2
18naypzfmabd6	INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1, 32), :B5 , SUBSTR(:B4 , 1, 128), SUBSTR(:B3 , 1, 128), SUBSTR(:B2 , 1, 128), SUBSTR(:B1 , 1, 256))
1c216gsabysz6	SELECT TAI.TIP_NO, TAI.INACT_TYPE_NO, TAI.START_DATE, TAI.END_DATE FROM TAXA_INACTIVE_PERIOD TAI WHERE (TAI.ESTAB_NO = :B9 OR :B9 IS NULL) AND (TAI.LICENSE_NO = :B8 OR :B8 IS NULL) AND (TAI.TAXABLE_OBJECT_NO = :B7 OR :B7 IS NULL) AND TRUNC(TAI.START_DATE) <= TRUNC(:B6 ) AND (TAI.END_DATE IS NULL OR TRUNC(TAI.END_DATE) >= TRUNC(:B5 )) AND (TAI.EXEMP_TYPE_NO = 1 OR (TAI.EXEMP_TYPE_NO = 2 AND TAI.TAX_PERIOD_NO IS NULL AND :B4 IS NULL) OR (TAI.EXEMP_TYPE_NO = 2 AND TAI.TAX_PERIOD_NO = :B4 )) AND (TAI.INACT_TYPE_NO IN (2, 3) OR (TAI.INACT_TYPE_NO = 1 AND :B3 = 0)) AND TAI.EXEMP_TYPE_NO = :B2 AND TAI.TAX_ACCOUNT_NO = :B1
1dx137mt6sb2t	DELETE FROM RW_SERVER_JOB_QUEUE WHERE JOB_ID = :B2 AND SERVER = :B1
1rcc0rft1jukv	SELECT 1 FROM INDIVIDUAL I , TAX_CENTRE TC , TAX_ACCOUNT A , TAX_PAYER P , TAX_TYPE TT , LICENSE L , TAX_ACCT_TAXABLE_OBJ TATO WHERE A.TAX_ACCOUNT_NO = :P_TAX_ACCOUNT_NO AND ROWNUM < 2 AND A.TAX_PAYER_NO = I.TAX_PAYER_NO (+) AND A.TAX_ACCOUNT_NO = L.TAX_ACCOUNT_NO (+) AND A.TAX_ACCOUNT_NO = TATO.TAX_ACCOUNT_NO(+) -- REFER TO 3 AND (NVL(:P_LICENSE_NO, 0) = 0 OR (L.LICENSE_NO = :P_LICENSE_NO1 AND TRUNC(L.REGIST_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE, 'DD-MM-YYYY')) AND (TRUNC(L.END_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE, 'DD-MM-YYYY')) OR L.END_DATE IS NULL) ) ) AND (NVL(:P_TAXABLE_OBJECT_NO, 0) = 0 OR (TATO.TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO1 AND TRUNC(TATO.START_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE1, 'DD-MM-YYYY')) AND (TRUNC(TATO.END_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE1, 'DD-MM-YYYY')) OR TATO.END_DATE IS NULL) ) ) AND P.TP_TYPE_NO = :P_TP_TYPE_NO AND (TRUNC(I.DECEASE_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE2, 'DD-MM-YYYY')) OR I.DECEASE_DATE IS NULL) -- JT OCT 2003 - ADDING A CHECK FOR COMPLIANCE FLAG -- AND ((NOT EXISTS (SELECT ASSESSMENT.TAX_ACCOUNT_NO FROM ASSESSMENT WHERE ASSESSMENT.TAX_PERIOD_NO = :P_TAX_PERIOD_NO AND ASSESSMENT.TAX_ACCOUNT_NO = A.TAX_ACCOUNT_NO AND ( :P_LICENSE_NO2 = 0 OR ASSESSMENT.LICENSE_NO = :P_LICENSE_NO3) AND ( NVL(:P_TAXABLE_OBJECT_NO2, 0) = 0 OR ASSESSMENT.TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO3) ) AND :P_CHECK_COMPLIANCE_FL = 1) OR :P_CHECK_COMPLIANCE_FL1 = 0) AND A.TAX_TYPE_NO = TT.TAX_TYPE_NO AND TT.TAX_BASIS_NO = :P_TAX_BASIS_NO AND A.TAX_CENTRE_NO = TC.TAX_CENTRE_NO AND A.TAX_PAYER_NO = P.TAX_PAYER_NO AND A.TAX_TYPE_NO = :P_TAX_TYPE_NO AND TRUNC(A.REG_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE2, 'DD-MM-YYYY')) AND NOT EXISTS (SELECT 1 FROM TAXA_INACTIVE_PERIOD WHERE TAX_ACCOUNT_NO = A.TAX_ACCOUNT_NO AND (NVL(:P_TAXABLE_OBJECT_NO4, 0) = 0 OR TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO5) AND ((TRUNC(TO_DATE(:V_TPER_START_DATE3, 'DD-MM-YYYY')) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)) OR(TRUNC(TO_DATE(:V_TPER_START_DATE4, 'DD-MM-YYYY')) >= TRUNC(START_DATE) AND END_DATE IS NULL)) -- ADD JOHANNA MAY 2007 AND ((TRUNC(TO_DATE(:V_TPER_END_DATE3, 'DD-MM-YYYY')) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)) OR(TRUNC(TO_DATE(:V_TPER_END_DATE4, 'DD-MM-YYYY'))>= TRUNC(START_DATE) AND END_DATE IS NULL)) -- ADD JOHANNA MAY 2007 AND EXEMP_TYPE_NO = 1 ) -- ADD BY JOHANNA 18 MAY 2007 , VERIFY IF THE ACCOUNT IS ACTIVE AND (TRUNC(A.CLOSE_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE5, 'DD-MM-YYYY')) OR A.CLOSE_DATE IS NULL)AND (NOT EXISTS (SELECT 1 FROM ASSESSMENT A, TAX_PERIOD TP, TAX_PERIOD TP2 WHERE A.TAX_ACCOUNT_NO = :ALL_P_TAX_ACCOUNT_NO AND ROWNUM < 2 AND A.RANGE_START_TAX_PERIOD_NO IS NOT NULL AND A.RANGE_START_TAX_PERIOD_NO = TP.TAX_PERIOD_NO AND A.TAX_PERIOD_NO = TP2.TAX_PERIOD_NO AND NVL(A.LICENSE_NO, 0) = :ALL_P_LICENSE_NO AND NVL(A.ESTAB_NO, 0) = :ALL_P_ESTAB_NO AND NVL(A.TAXABLE_OBJECT_NO, 0) = :ALL_P_TAXABLE_OBJECT_NO AND :ALL_P_TPER_YEAR || LTRIM(NVL(TO_CHAR( NVL(:ALL_P_TPER_MONTH, 0) , '09'), 0))|| LTRIM(NVL(TO_CHAR( NVL(:ALL_V_TPER_WEEK, 0) , '09'), 0)) BETWEEN TP.TPER_YEAR || LTRIM(NVL(TO_CHAR(TP.TPER_MONTH, '09'), 0))|| LTRIM(NVL(TO_CHAR(TP.TPER_WEEK, '09'), 0)) AND TP2.TPER_YEAR || LTRIM(NVL(TO_CHAR(TP2.TPER_MONTH, '09'), 0))|| LTRIM(NVL(TO_CHAR(TP2.TPER_WEEK, '09'), 0)) AND :ALL_P_CHECK_COMPLIANCE_FL = 1 ) OR :ALL_P_CHECK_COMPLIANCE_FL1 = 0) AND TC.TAX_CENTRE_NO = 26
2bcafk777j03t	select TTA.GLCODE_SECTION1 GL_CODE, null section, tax_trans.tax_type_no, substr(f_translate(tty.tax_type_desc, :p_lang_no, 'TAX_TYPE'), 1, 200) tax_type_desc, -- fs ard-759 tta.tax_trans_type_no, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_1, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_1, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT1, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_2, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_2, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT2, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_3, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_3, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT3, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_4, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_4, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT4, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_5, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_5, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT5, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_1, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_1, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_2, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_2, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_3, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_3, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_4, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_4, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_5, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_5, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT6 FROM tax_transaction tax_trans , tax_payer tax_p , individual ind , corporation corp , document , tax_sub_trans tax_strans , establishment estab , lic_base lic_base , license lic , tax_trans_type ttyp , charge_type ctyp , tax_period , tax_acct_base_imp tabi , payment_loc pay , tax_centre tc , assessment ass , payment_loc_detail pld , treasury_reg_office tro , tax_type tax_typ, tax_type tty, TAX_TYPE_ACCOUNT TTA WHERE tax_strans.charge_type_no = ctyp.charge_type_no and tax_strans.tax_type_no = tty.tax_type_no AND tta.tax_trans_type_no = tax_strans.tax_trans_type_no and tax_strans.TAX_TYPE_NO = TTA.TAX_TYPE_NO AND tax_strans.tax_type_no= tax_typ.tax_type_no AND tc.tax_centre_no(+) = tax_trans.tax_centre_no AND tax_strans.tax_trans_type_no = ttyp.tax_trans_type_no AND tax_strans.estab_no = estab.estab_no(+) AND tax_strans.tax_account_no = tabi.tax_account_no(+) AND tax_strans.tax_period_no = tax_period.tax_period_no(+) AND tax_strans.license_no = lic.license_no(+) AND lic_base.lic_base_no(+) = lic.lic_base_no AND tax_strans.tax_trans_no = tax_trans.tax_trans_no AND document.doc_no(+) = tax_trans.doc_no AND pay.payment_loc_no(+) = tax_trans.payment_loc_no AND tax_p.tax_payer_no(+) = tax_trans.tax_payer_no AND tax_p.tax_payer_no = corp.tax_payer_no(+) AND tax_p.tax_payer_no = ind.tax_payer_no(+) AND tax_strans.assess_no = ass.assess_no(+) AND pay.payment_loc_no = pld.payment_loc_no AND tax_strans.charge_type_no = 2 AND (tax_trans.payment_loc_no = :p_payment_loc_no OR :p_payment_loc_no IS NULL) AND (tax_strans.tax_centre_no = to_number(:p_tax_centre_no) OR :p_tax_centre_no IS NULL) and (tax_p.case_management_group_no = :p_case_man_group_no or :p_case_man_group_no is null) AND (TRUNC(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:p_date_from, 'DD-MM-YYYY') AND TO_DATE(:p_date_to, 'DD-MM-YYYY') ) AND ( TRUNC(tax_trans.TRANS_DATE) BETWEEN TRUNC(TTA.START_DATE) AND NVL(TTA.END_DATE, SYSDATE +9999) ) and (tabi.end_date is null or (tabi.end_date is not null and not exists (select 1 from tax_acct_base_imp tabi1, tax_account ta1 where tabi1.tax_account_no = tabi.tax_account_no and end_da te is null))) AND ( pld.treasury_reg_office_no = tro.treasury_reg_office_no (+) and tax_trans.trans_date between pld.start_date and nvl(pld.end_date, sysdate+99999) ) and tax_trans.reverse_trans_no is null and (tta.imposition_base_no = tabi.imposition_base_no or tta.imposition_base_no is null) and :p_group_by = upper('TT') GROUP BY TTA.GLCODE_SECTION1, tax_trans.tax_type_no, substr(f_translate(tty.tax_type_desc, :p_lang_no, 'TAX_TYPE'), 1, 200), tta.tax_trans_type_no union select TTA.GLCODE_SECTION1 GL_CODE, substr(tta.glcode_section1, 1, 3) section, null tax_type_no, null tax_type_desc, null tax_trans_type_no, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_1, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_1, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT1, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_2, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_2, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT2, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_3, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_3, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT3, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_4, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_4, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT4, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_5, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_5, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT5, SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_1, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_1, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_2, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_2, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_3, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_3, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_4, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_4, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end)+ SUM(case WHEN trunc(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:P_FROM_DATE_5, 'DD-MM-YYYY') AND TO_DATE(:P_TO_DATE_5, 'DD-MM-YYYY') then tax_strans.AMOUNT else 0 end) AMOUNT6 FROM tax_transaction tax_trans , tax_payer tax_p , individual ind , corporation corp , document , tax_sub_trans tax_strans , establishment estab , lic_base lic_base , license lic , tax_trans_type ttyp , charge_type ctyp , tax_period , tax_acct_base_imp tabi , payment_loc pay , tax_centre tc , assessment ass , payment_loc_detail pld , treasury_reg_office tro , tax_type tax_typ, tax_type tty, TAX_TYPE_ACCOUNT TTA WHERE tax_strans.charge_type_no = ctyp.charge_type_no and tax_strans.tax_type_no = tty.tax_type_no AND tta.tax_trans_type_no = tax_strans.tax_trans_type_no and tax_strans.TAX_TYPE_NO = TTA.TAX_TYPE_NO AND tax_strans.tax_type_no= tax_typ.tax_type_no AND tc.tax_centre_no(+) = tax_trans.tax_centre_no AND tax_strans.tax_trans_type_no = ttyp.tax_trans_type_no AND tax_strans.estab_no = estab.estab_no(+) AND tax_strans.tax_account_no = tabi.tax_account_no(+) AND tax_strans.tax_period_no = tax_period.tax_period_no(+) AND tax_strans.license_no = lic.license_no(+) AND lic_base.lic_base_no(+) = lic.lic_base_no AND tax_strans.tax_trans_no = tax_trans.tax_trans_no AND document.doc_no(+) = tax_trans.doc_no AND pay.payment_loc_no(+) = tax_trans.payment_loc_no AND tax_p.tax_payer_no(+) = tax_trans.tax_payer_no AND tax_p.tax_payer_no = corp.tax_payer_no(+) AND tax_p.tax_payer_no = ind.tax_payer_no(+) AND tax_strans.assess_no = ass.assess_no(+) AND pay.payment_loc_no = pld.payment_loc_no AND tax_strans.charge_type_no = 2 AND (tax_trans.payment_loc_no = :p_payment_loc_no OR :p_payment_loc_no IS NULL) AND (tax_strans.tax_centre_no = to_number(:p_tax_centre_no) OR :p_tax_centre_no IS NULL) and (tax_p.case_management_group_no = :p_case_man_group_no or :p_case_man_gr oup_no is null) AND (TRUNC(tax_trans.TRANS_DATE) BETWEEN TO_DATE(:p_date_from, 'DD-MM-YYYY') AND TO_DATE(:p_date_to, 'DD-MM-YYYY') ) AND ( TRUNC(tax_trans.TRANS_DATE) BETWEEN TRUNC(TTA.START_DATE) AND NVL(TTA.END_DATE, SYSDATE +9999) ) and (tabi.end_date is null or (tabi.end_date is not null and not exists (select 1 from tax_acct_base_imp tabi1, tax_account ta1 where tabi1.tax_account_no = tabi.tax_account_no and end_date is null))) AND ( pld.treasury_reg_office_no = tro.treasury_reg_office_no (+) and tax_trans.trans_date between pld.start_date and nvl(pld.end_date, sysdate+99999) ) and tax_trans.reverse_trans_no is null and (tta.imposition_base_no = tabi.imposition_base_no or tta.imposition_base_no is null) and :p_group_by = upper('GL') GROUP BY TTA.GLCODE_SECTION1 order by 2, 4, 1
2gvuy38wm7yrz	DELETE TAX_TRANSACTION WHERE TAX_TRANS_NO = :B1
376mm6hypbk8v	SELECT TRACE_MODE FROM SIGTAS_TRACE_MODE STM WHERE UPPER(LOGIN_ID) = USER AND STM.EXPIRATION_DATE >= TRUNC(SYSDATE)
3d43s8fbp8tpm	SELECT ROWID, T.TAX_PAYER_NO, T.tp_type_no, T.FISCAL_NO, T.TIN_APPLICATION_NO, T.NEED_ACCT_FOR_TIN_CERTIF_FL, T.TAX_CENTRE_NO, T.update_user, T.REGISTRATION_SYSTEM_DATE FROM TAX_PAYER T WHERE t.tin_application_no IN (SELECT tin_application_no FROM tin_application t, tax_centre tc WHERE tc.tax_centre_no = t.tax_centre_no /*AND t.tax_centre_no = :parameter.user_tax_centre_no*/ AND (t.request_status_type_no IN (9, 10, 11))) and (T.TIN_APPLICATION_NO=:1)
41s54vtbuj6zw	SELECT COUNT (1) FROM DOCUMENT DOC , DOC_TYPE DOCT , IRD_EMPLOYEE EMP , TAX_CENTRE TC WHERE DOC.ENTRY_USER = EMP.IRD_LOGIN_ID AND TC.TAX_CENTRE_NO = EMP.TAX_CENTRE_NO AND DOC.DOC_TYPE_NO = DOCT.DOC_TYPE_NO AND EMP.IRD_LOGIN_ID = :b1 AND ( TO_CHAR (DOC.CREATED_DATE , 'YYYY-MM-DD' ) BETWEEN TO_CHAR (:b2 , 'YYYY-MM-DD' ) AND TO_CHAR (:b3 , 'YYYY-MM-DD' ) ) AND NVL(:b4 , 'N' ) = 'Y'
42100f9uzs3m4	SELECT ta.tin_application_no, ta.request_status_type_no, substr(f_translate(REQUEST_STATUS_DESC, :1, 'REQUEST_STATUS_TYPE'), 1, 100) request_status_desc, ita.first_name, ita.last_name, ta.tax_centre_no , substr(F_TRANSLATE(TAX_CENTRE_DESC, :2, 'TAX_CENTRE'), 1, 100) tax_centre_desc FROM tin_application ta, indv_tin_appl ita , tax_centre t , REQUEST_STATUS_TYPE rst WHERE ta.tp_type_no = 1 /* individual*/ AND ta.application_form_type_no = 1 /* new tin application */ AND ta.tin_application_no = ita.tin_application_no and ta.tax_centre_no = t.TAX_CENTRE_NO and ta.request_status_type_no = rst.REQUEST_STATUS_TYPE_NO AND ta.request_status_type_no <> 11 AND ta.tax_centre_no IN(SELECT i.tax_centre_no FROM ird_employee i WHERE i.ird_login_id = USER UNION ALL SELECT tc.tax_centre_no FROM tax_centre tc, ird_employee i WHERE i.ird_login_id = USER AND tc.tax_centre_no = i.tax_centre_no) /* applications that have the status TIN ALLOCATED if the 'SIGTASAD' is TAXROLL */ AND (((ta.request_status_type_no = 9) OR (ta.request_status_type_no IN (9, 10) )) AND NOT EXISTS (SELECT 1 FROM tax_payer tp WHERE tp.tin_application_no = ta.tin_application_no) AND :3 <> 'ENTER-QUERY') union SELECT ta.tin_application_no, ta.request_status_type_no, substr(f_translate(REQUEST_STATUS_DESC, :4, 'REQUEST_STATUS_TYPE'), 1, 100) request_status_desc, ita.first_name, ita.last_name, ta.tax_centre_no , substr(F_TRANSLATE(TAX_CENTRE_DESC, :5, 'TAX_CENTRE'), 1, 100) tax_centre_desc FROM tin_application ta, indv_tin_appl ita , tax_centre t , REQUEST_STATUS_TYPE rst WHERE ta.tp_type_no = 1 /* individual*/ AND ta.application_form_type_no = 1 /* new tin application */ AND ta.tin_application_no = ita.tin_application_no and ta.tax_centre_no = t.TAX_CENTRE_NO and ta.request_status_type_no = rst.REQUEST_STATUS_TYPE_NO AND ta.request_status_type_no <> 11 AND ta.tax_centre_no IN(SELECT i.tax_centre_no FROM ird_employee i WHERE i.ird_login_id = USER UNION ALL SELECT tc.tax_centre_no FROM tax_centre tc, ird_employee i WHERE i.ird_login_id = USER AND tc.tax_centre_no = i.tax_centre_no) and (:6 = 'ENTER-QUERY' AND ta.tin_application_no IN (SELECT tin_application_no FROM tax_payer)) ORDER BY 1 DESC
42j1q2q8yymq6	SELECT 1 FROM INDIVIDUAL I , TAX_CENTRE TC , TAX_ACCOUNT A , TAX_PAYER P , TAX_TYPE TT , LICENSE L , TAX_ACCT_TAXABLE_OBJ TATO WHERE A.TAX_ACCOUNT_NO = :P_TAX_ACCOUNT_NO AND ROWNUM < 2 AND A.TAX_PAYER_NO = I.TAX_PAYER_NO (+) AND A.TAX_ACCOUNT_NO = L.TAX_ACCOUNT_NO (+) AND A.TAX_ACCOUNT_NO = TATO.TAX_ACCOUNT_NO(+) -- REFER TO 3 AND (NVL(:P_LICENSE_NO, 0) = 0 OR (L.LICENSE_NO = :P_LICENSE_NO1 AND TRUNC(L.REGIST_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE, 'DD-MM-YYYY')) AND (TRUNC(L.END_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE, 'DD-MM-YYYY')) OR L.END_DATE IS NULL) ) ) AND (NVL(:P_TAXABLE_OBJECT_NO, 0) = 0 OR (TATO.TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO1 AND TRUNC(TATO.START_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE1, 'DD-MM-YYYY')) AND (TRUNC(TATO.END_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE1, 'DD-MM-YYYY')) OR TATO.END_DATE IS NULL) ) ) AND P.TP_TYPE_NO = :P_TP_TYPE_NO AND (TRUNC(I.DECEASE_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE2, 'DD-MM-YYYY')) OR I.DECEASE_DATE IS NULL) -- JT OCT 2003 - ADDING A CHECK FOR COMPLIANCE FLAG -- AND ((NOT EXISTS (SELECT ASSESSMENT.TAX_ACCOUNT_NO FROM ASSESSMENT WHERE ASSESSMENT.TAX_PERIOD_NO = :P_TAX_PERIOD_NO AND ASSESSMENT.TAX_ACCOUNT_NO = A.TAX_ACCOUNT_NO AND ( :P_LICENSE_NO2 = 0 OR ASSESSMENT.LICENSE_NO = :P_LICENSE_NO3) AND ( NVL(:P_TAXABLE_OBJECT_NO2, 0) = 0 OR ASSESSMENT.TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO3) ) AND :P_CHECK_COMPLIANCE_FL = 1) OR :P_CHECK_COMPLIANCE_FL1 = 0) AND A.TAX_TYPE_NO = TT.TAX_TYPE_NO AND TT.TAX_BASIS_NO = :P_TAX_BASIS_NO AND A.TAX_CENTRE_NO = TC.TAX_CENTRE_NO AND A.TAX_PAYER_NO = P.TAX_PAYER_NO AND A.TAX_TYPE_NO = :P_TAX_TYPE_NO AND TRUNC(A.REG_DATE) <= TRUNC(TO_DATE(:V_TPER_END_DATE2, 'DD-MM-YYYY')) AND NOT EXISTS (SELECT 1 FROM TAXA_INACTIVE_PERIOD WHERE TAX_ACCOUNT_NO = A.TAX_ACCOUNT_NO AND (NVL(:P_TAXABLE_OBJECT_NO4, 0) = 0 OR TAXABLE_OBJECT_NO = :P_TAXABLE_OBJECT_NO5) AND ((TRUNC(TO_DATE(:V_TPER_START_DATE3, 'DD-MM-YYYY')) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)) OR(TRUNC(TO_DATE(:V_TPER_START_DATE4, 'DD-MM-YYYY')) >= TRUNC(START_DATE) AND END_DATE IS NULL)) -- ADD JOHANNA MAY 2007 AND ((TRUNC(TO_DATE(:V_TPER_END_DATE3, 'DD-MM-YYYY')) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)) OR(TRUNC(TO_DATE(:V_TPER_END_DATE4, 'DD-MM-YYYY'))>= TRUNC(START_DATE) AND END_DATE IS NULL)) -- ADD JOHANNA MAY 2007 AND EXEMP_TYPE_NO = 1 ) -- ADD BY JOHANNA 18 MAY 2007 , VERIFY IF THE ACCOUNT IS ACTIVE AND (TRUNC(A.CLOSE_DATE) >= TRUNC(TO_DATE(:V_TPER_START_DATE5, 'DD-MM-YYYY')) OR A.CLOSE_DATE IS NULL)AND (NOT EXISTS (SELECT 1 FROM ASSESSMENT A, TAX_PERIOD TP, TAX_PERIOD TP2 WHERE A.TAX_ACCOUNT_NO = :ALL_P_TAX_ACCOUNT_NO AND ROWNUM < 2 AND A.RANGE_START_TAX_PERIOD_NO IS NOT NULL AND A.RANGE_START_TAX_PERIOD_NO = TP.TAX_PERIOD_NO AND A.TAX_PERIOD_NO = TP2.TAX_PERIOD_NO AND NVL(A.LICENSE_NO, 0) = :ALL_P_LICENSE_NO AND NVL(A.ESTAB_NO, 0) = :ALL_P_ESTAB_NO AND NVL(A.TAXABLE_OBJECT_NO, 0) = :ALL_P_TAXABLE_OBJECT_NO AND :ALL_P_TPER_YEAR || LTRIM(NVL(TO_CHAR( NVL(:ALL_P_TPER_MONTH, 0) , '09'), 0))|| LTRIM(NVL(TO_CHAR( NVL(:ALL_V_TPER_WEEK, 0) , '09'), 0)) BETWEEN TP.TPER_YEAR || LTRIM(NVL(TO_CHAR(TP.TPER_MONTH, '09'), 0))|| LTRIM(NVL(TO_CHAR(TP.TPER_WEEK, '09'), 0)) AND TP2.TPER_YEAR || LTRIM(NVL(TO_CHAR(TP2.TPER_MONTH, '09'), 0))|| LTRIM(NVL(TO_CHAR(TP2.TPER_WEEK, '09'), 0)) AND :ALL_P_CHECK_COMPLIANCE_FL = 1 ) OR :ALL_P_CHECK_COMPLIANCE_FL1 = 0) AND TC.TAX_CENTRE_NO = 23
48qhdguxs97ya	SELECT COUNT(*) FROM WEEKEND WHERE START_DATE <= :B2 AND (END_DATE IS NULL OR END_DATE >= :B2 ) AND TRIM(UPPER(WEEKEND_DESC)) = :B1
4ajxk1yu22xpb	select LANG_NO from sigtas_rules
4bqsr5vp7f39k	INSERT /*+ IDX(0) */ INTO "SIGTASAD"."MLOG$_TAX_SUB_TRANS" (dmltype$$, old_new$$, snaptime$$, change_vector$$, xid$$, m_row$$) VALUES (:d, :o, to_date('4000-01-01:00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), :c, :x, :m)
4m7m0t6fjcs5x	update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1
4tq4zndbtty5g	select sid, username, serial#, process, nvl(sql_id, 0), sql_address, blocking_session, wait_class, event, p1, p2, p3, seconds_in_wait from v$session where blocking_session_status = 'VALID' OR sid IN (select blocking_session from v$session where blocking_session_status = 'VALID')
59f5657tg1028	SELECT SRN.VALUE FROM SIGTAS_RULES_NEW SRN WHERE UPPER(SRN.RULE_NAME) = :B1
5fk0v8km2f811	select propagation_name, 'BUFFERED', num_msgs ready, 0 from gv$buffered_subscribers b, dba_propagation p, dba_queues q, dba_queue_tables t where b.subscriber_name = p.propagation_name and b.subscriber_address = p.destination_dblink and b.queue_schema = p.source_queue_owner and b.queue_name = p.source_queue_name and p.source_queue_name = q.name and p.source_queue_owner = q.owner and q.queue_table = t.queue_table and b.inst_id=t.owner_instance
5sgfq85zytzax	UPDATE TAX_PAYER T SET T.FISCAL_NO=:1, T.REGISTRATION_SYSTEM_DATE=:2 WHERE ROWID=:3
645zvb9qc82wh	select distinct tf.form_ird_no, fv.version_no, tf.form_title, tf.form_no, to_char(fv.revision_date, 'DD-MM-YYYY') revision_date, to_char(fv.start_date, 'DD-MM-YYYY') fvstart_date, fv.print_only_header, fv.print_nothing_for_remittance, tp.tax_period_no , tp.tper_month as months, TO_CHAR(tp.TPER_START_DATE, 'DD-MON-YYYY') AS StartDate , TO_CHAR(tp.TPER_END_DATE, 'DD-MON-YYYY') AS EndDate, tp.tper_year as years from sigtasad.tax_form tf, sigtasad.form_version fv, sigtasad.tax_period tp, sigtasad.imposition_base impb , sigtasad.tax_acct_base_imp tabi where tf.form_no = fv.form_no and fv.form_no = tp.form_no and fv.version_no = tp.version_no and impb.imposition_base_no(+) = tp.imposition_base_no AND impb.imposition_base_no = tabi.imposition_base_no(+) and tf.tax_type_no =:tax_type_no and tp.form_no=:form_no and (tabi.tax_account_no=:tax_account_no) and ( (NOT EXISTS (select 1 from sigtasad.tax_acct_base_imp tabi1 where tabi1.tax_account_no=:tax_account_no ) and tp.imposition_base_no is null ) OR (EXISTS (select 1 from sigtasad.tax_acct_base_imp tabi1 where tabi1.tax_account_no=:tax_account_no ) and tabi.tax_account_no=:tax_account_no ) ) AND ( tabi.start_date IS NULL OR ( tp.tax_period_no in ( select tp.tax_period_no from tax_form tf, form_version fv, tax_period tp where tf.form_no = fv.form_no and fv.form_no = tp.form_no and fv.version_no = tp.version_no and tf.tax_type_no = :tax_type_no ) and trunc(tabi.start_date) = trunc(get_imp_base_valid(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :tax_payer_no, null, 2), PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :tax_payer_no, null, 1), :tax_account_no)) ) ) and not exists (select dd.* from sigtasad.document dd where dd.tax_PERIOD_NO=tp.TAX_period_No and dd.DOC_STATE_NO=2 and dd.tax_payer_no=:tax_payer_no and dd.DOC_TYPE_NO=1) ORDER BY years DESC, months
67dgzkjb6bk53	select TRANS_ALL_FREE_TEXT_FIELDS_FL from sigtas_rules
69k5bhm12sz98	SELECT dbin.instance_number, dbin.db_name, dbin.instance_name, dbin.host_name, dbin.version, CASE WHEN s1.startup_time = s2.startup_time THEN 0 ELSE 1 END as bounce, CAST(s1.end_interval_time AS DATE) as begin_time, CAST(s2.end_interval_time AS DATE) as end_time, ROUND((cast( (case when s2.end_interval_time > s1.end_interval_time then s2.end_interval_time else s1.end_interval_time end) as date) - cast(s1.end_interval_time as date)) * 86400) as int_secs, CASE WHEN (s1.status <> 0 OR s2.status <> 0) THEN 1 ELSE 0 END as err_detect, round( greatest( (extract(day from s2.flush_elapsed) * 86400) + (extract(hour from s2.flush_elapsed) * 3600) + (extract(minute from s2.flush_elapsed) * 60) + extract(second from s2.flush_elapsed), (extract(day from s1.flush_elapsed) * 86400) + (extract(hour from s1.flush_elapsed) * 3600) + (extract(minute from s1.flush_elapsed) * 60) + extract(second from s1.flush_elapsed), 0 )) as max_flush_secs FROM WRM$_SNAPSHOT s1 , WRM$_DATABASE_INSTANCE dbin , WRM$_SNAPSHOT s2 WHERE s1.dbid = :dbid AND s2.dbid = :dbid AND s1.instance_number = s2.instance_number AND dbin.instance_number = s1.instance_number AND s1.snap_id = :bid AND s2.snap_id = :eid AND dbin.dbid = s1.dbid AND dbin.startup_time = s1.startup_time and dbin.instance_number = :inst
6k2q7mpqagrwz	SELECT COUNT(*) FROM CALENDAR WHERE HOLIDAY_DATE = :B1
6k5agh28pr3wp	select propagation_name streams_name, 'PROPAGATION' streams_type, '"'||destination_queue_owner||'"."'||destination_queue_name||'"@'||destination_dblink address, queue_table, owner, source_queue_name from dba_queues, dba_propagation where owner=SOURCE_QUEUE_OWNER and SOURCE_QUEUE_NAME=name
6kdk9xfdwn5pu	SELECT SDO.DESCRIPTION FROM SIGTAS_DICT SD, SIGTAS_DICT_OTHER SDO WHERE SD.SIGTAS_DICT_NO = SDO.SIGTAS_DICT_NO AND SD.DESCRIPTION_UPPER = :B2 AND SDO.LANG_NO = :B1 AND SD.CONTEXT IS NULL AND SD.MESSAGE_CODE IS NULL
6mz0nsdkqm36f	SELECT SUM (NVL(TAX_STRANS.AMOUNT , 0 ) ) FROM TAX_SUB_TRANS TAX_STRANS WHERE TAX_STRANS.TAX_PERIOD_NO = :1 AND TAX_STRANS.TAX_TRANS_TYPE_NO IN ( 1 , 6 ) AND ( TAX_STRANS.TAX_ACCOUNT_NO = :1 OR :1 IS NULL ) AND ( TAX_STRANS.ESTAB_NO = :1 OR :1 IS NULL ) AND ( TAX_STRANS.LICENSE_NO = :1 OR :1 IS NULL ) AND ( TAX_STRANS.TAXABLE_OBJECT_NO = :1 OR :1 IS NULL ) AND EXISTS (SELECT 1 FROM TAX_TRANSACTION TAX_TRANS WHERE TAX_TRANS.TAX_PAYER_NO = :1 AND TAX_TRANS.TAX_TRANS_NO = TAX_STRANS.TAX_TRANS_NO )
6wm3n4d7bnddg	SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - et.etime) * 86400) as time_secs, 3 as operation, 0 as log_id FROM ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) et UNION ALL SELECT 0 as source, trunc((sysdate - bt.btime) * 86400) as time_secs, 2 as operation, 0 as log_id FROM ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) bt ) WHERE time_secs >= 0 ORDER BY source ASC, time_secs DESC, log_id ASC
6yj30vzrhusha	/* OracleOEM */ select CAPTURE_NAME, NUM_MSGS, SPILL_MSGS, CNUM_MSGS, CSPILL_MSGS from gv$buffered_queues b, dba_capture c, dba_queues q, dba_queue_tables t where c.queue_name = b.queue_name and c.queue_owner = b.queue_schema and c.queue_name = q.name and c.queue_owner = q.owner and q.queue_table=t.queue_table and q.owner=t.owner and b.inst_id = t.owner_instance
792bh5a60m6qp	SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY(I.FIRST_NAME||I.MIDDLE_NAME, :B2 ) SIMILARITY_SCORE, UTL_MATCH.JARO_WINKLER_SIMILARITY(I.MIDDLE_NAME||I.FIRST_NAME, :B2 ) SIMILARITY_SCORE_INV, I.TAX_PAYER_NO FROM INDIVIDUAL I WHERE (UTL_MATCH.JARO_WINKLER_SIMILARITY(I.FIRST_NAME||I.MIDDLE_NAME, :B2 ) > :B1 OR UTL_MATCH.JARO_WINKLER_SIMILARITY(I.MIDDLE_NAME||I.FIRST_NAME, :B2 ) > :B1 ) AND I.TAX_PAYER_NO NOT IN (SELECT MATCH_TAX_PAYER_NO FROM TP_MATCH WHERE TIN_APPLICATION_NO = :B3 )
7wt7phk4xns75	select a.capture_name streams_process_name, a.status streams_process_status, 'CAPTURE' streams_process_type, COUNT(a.error_message) from dba_capture a group by a.capture_name, a.status union all select a.propagation_name streams_process_name, a.status streams_process_status, 'PROPAGATION' streams_process_type, COUNT(a.error_message) from dba_propagation a group by a.propagation_name, a.status union all select a.apply_name streams_process_name, a.status streams_process_status, 'APPLY' streams_process_type, COUNT(a.error_message) from dba_apply a group by a.apply_name, a.status
7zjnap76wtj18	SELECT MAX (PCK_UTIL.GET_DATE_FORMATTED(AUDIT_TIMESTAMP , 12 , 'DATETIME_FORMAT_GENERAL' ) ) FROM SIGTAS_AUDIT_USER WHERE USERNAME = :b1
8vwv6hx92ymmm	UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B1 , VALUE = :B6 , STRING_VALUE = :B5 WHERE TARGET_GUID = :B4 AND METRIC_GUID = :B3 AND KEY_VALUE = :B2 AND COLLECTION_TIMESTAMP < :B1
9bdnapbkxjyj8	SELECT SUM (CONTA) FROM (SELECT COUNT (1) CONTA FROM INDIVIDUAL I , TAX_CENTRE TC , TAX_PAYER P , LICENSE L , TAX_TYPE TT , DOCUMENT DOC , ASSESSMENT ASS , CHANGE_ASSESS_STATUS CAS , TAX_PERIOD TP , CASE_MANAGEMENT_GROUP CMG WHERE ASS.TAX_PAYER_NO = I.TAX_PAYER_NO (+) AND ASS.TAX_ACCOUNT_NO = L.TAX_ACCOUNT_NO (+) AND TT.TAX_TYPE_NO = ASS.TAX_TYPE_NO AND P.TAX_CENTRE_NO = TC.TAX_CENTRE_NO AND ASS.TAX_PAYER_NO = P.TAX_PAYER_NO AND ASS.ASSESS_NO = DOC.ASSESS_NO AND ( DOC.TAX_PERIOD_NO = TP.TAX_PERIOD_NO OR DOC.TAX_PERIOD_NO IS NULL ) AND DOC.TAX_PAYER_NO = ASS.TAX_PAYER_NO AND DOC.TAX_TYPE_NO = ASS.TAX_TYPE_NO AND P.CASE_MANAGEMENT_GROUP_NO = CMG.CASE_MANAGEMENT_GROUP_NO (+) AND TT.TAX_BASIS_NO != 1 AND P.TP_TYPE_NO = 1 AND DOC_TYPE_NO = 1 AND DOC_STATE_NO = 2 AND PRINTED_DATE IS NOT NULL AND RECEIVED_DATE IS NOT NULL AND ( ASS.TAX_PAYER_NO = :1 OR :1 IS NULL ) AND ( ASS.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND ( TC.TAX_CENTRE_NO = :1 OR :1 IS NULL ) AND ( CMG.CASE_MANAGEMENT_GROUP_NO = :1 OR :1 IS NULL ) AND ( :1 IS NULL OR ( :1 IS NOT NULL AND DOC.TAX_PERIOD_NO IN (SELECT T.TAX_PERIOD_NO FROM TAX_PERIOD T , TAX_FORM F WHERE T.FORM_NO = F.FORM_NO AND ( F.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND T.TPER_YEAR BETWEEN :1 AND :1 AND NVL(T.TPER_MONTH , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) AND NVL(T.TPER_WEEK , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) )) ) AND ASS.ASSESS_NO = CAS.ASSESS_NO (+) AND ( TRUNC (DOC.RECEIVED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DOC.RECEIVED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( CAS.ASSESS_STATUS_NO = :1 OR :1 IS NULL ) UNION SELECT COUNT (1) FROM CORPORATION C , ENTERPRISE E , TAX_CENTRE TC , ESTABLISHMENT ES , TAX_PAYER P , LICENSE L , TAX_TYPE TT , DOCUMENT DOC , ASSESSMENT ASS , CHANGE_ASSESS_STATUS CAS , TAX_PERIOD TP , CASE_MANAGEMENT_GROUP CMG WHERE ASS.TAX_PAYER_NO = C.TAX_PAYER_NO (+) AND ASS.TAX_PAYER_NO = E.TAX_PAYER_NO (+) AND P.TAX_CENTRE_NO = TC.TAX_CENTRE_NO AND ASS.TAX_PAYER_NO = P.TAX_PAYER_NO AND ASS.TAX_TYPE_NO = TT.TAX_TYPE_NO AND E.ENTERPRISE_NO = ES.ENTERPRISE_NO (+) AND ASS.TAX_ACCOUNT_NO = L.TAX_ACCOUNT_NO (+) AND DOC.TAX_PAYER_NO = ASS.TAX_PAYER_NO AND DOC.TAX_TYPE_NO = ASS.TAX_TYPE_NO AND ASS.ASSESS_NO = DOC.ASSESS_NO AND ( DOC.TAX_PERIOD_NO = TP.TAX_PERIOD_NO OR DOC.TAX_PERIOD_NO IS NULL ) AND P.CASE_MANAGEMENT_GROUP_NO = CMG.CASE_MANAGEMENT_GROUP_NO (+) AND ES.HEAD_OFFICE = UPPER('y' ) AND TT.TAX_BASIS_NO != 1 AND P.TP_TYPE_NO = 2 AND DOC_TYPE_NO = 1 AND DOC_STATE_NO = 2 AND PRINTED_DATE IS NOT NULL AND RECEIVED_DATE IS NOT NULL AND ( ASS.TAX_PAYER_NO = :1 OR :1 IS NULL ) AND ( ASS.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND ( TC.TAX_CENTRE_NO = :1 OR :1 IS NULL ) AND ( CMG.CASE_MANAGEMENT_GROUP_NO = :1 OR :1 IS NULL ) AND ( :1 IS NULL OR ( :1 IS NOT NULL AND DOC.TAX_PERIOD_NO IN (SELECT T.TAX_PERIOD_NO FROM TAX_PERIOD T , TAX_FORM F WHERE T.FORM_NO = F.FORM_NO AND ( F.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND T.TPER_YEAR BETWEEN :1 AND :1 AND NVL(T.TPER_MONTH , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) AND NVL(T.TPER_WEEK , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) )) ) AND ASS.ASSESS_NO = CAS.ASSESS_NO (+) AND ( TRUNC (DOC.RECEIVED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DOC.RECEIVED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( CAS.ASSESS_STATUS_NO = :1 OR :1 IS NULL ) UNION SELECT COUNT (1) FROM TAX_CENTRE TC , ESTABLISHMENT ES , TAX_PAYER P , TAX_ESTAB TE , LICENSE L , CASE_MANAGEMENT_GROUP CMG , TAX_TYPE TT , (SELECT ASSESS_NO , TAX_PAYER_NO , TAX_TYPE_NO , TAX_CENTRE_NO , TAX_PERIOD_NO , EXT_DOC_NO , DOC_TP_MONTH , ESTAB_NO , DOC_TP_YEAR , BATCH_NO , RECEIVED_INCOMPLETE_DATE , SUSPICIOUS_REMITTANCE_FL , RECEIVED_DATE , DOC_TP_WEEK FROM DOCUMENT WHERE ASSESS_NO IS NOT NULL AND DOC_STATE_NO = 2 AND DOC_TYPE_NO = 1 AND PRINTED_DATE IS NOT NULL UNION SELECT DD.ASSESS_NO , DD.TAX_PAYER_NO , DD.TAX_TYPE_NO , DD.TAX_CENTRE_NO , DD.TAX_PERIOD_NO , DD.EXT_DOC_NO , DD.DOC_TP_MONTH , DD.ESTAB_NO , DD.DOC_TP_YEAR , DD.BATCH_NO , DD.RECEIVED_INCOMPLETE_DATE , DD.SUSPICIOUS_REMITTANCE_FL , NVL(DD.RECEIVED_DATE , DD.CREATED_DATE ) RECEIVED_DATE , DD.DOC_TP_WEEK FROM DOCUMENT DD WHERE DD.ASSESS_NO IS NOT NULL AND DD.DOC_TYPE_NO = 4 AND EXISTS (SELECT 1 FROM DOCUMENT WHERE DOC_TYPE_NO = 8 AND DOC_STATE_NO = 2 AND PRINTED_DATE IS NOT NULL AND ASSESS_NO = DD.ASSESS_NO ) ) DOC , ASSESSMENT ASS , CHANGE_ASSESS_STATUS CAS , ASSESS_LINE ASSL , FORM_LINE FL WHERE ASS.TAX_ACCOUNT_NO = TE.TAX_ACCOUNT_NO (+) AND ASS.TAX_ACCOUNT_NO = L.TAX_ACCOUNT_NO (+) AND ( ES.ESTAB_NO = TE.ESTAB_NO OR ES.ESTAB_NO = L.ESTAB_NO ) AND TT.TAX_TYPE_NO = ASS.TAX_TYPE_NO AND ASS.TAX_PAYER_NO = P.TAX_PAYER_NO AND TT.TAX_BASIS_NO = 1 AND DOC.TAX_PAYER_NO = ASS.TAX_PAYER_NO AND DOC.TAX_TYPE_NO = ASS.TAX_TYPE_NO AND DOC.TAX_CENTRE_NO = TC.TAX_CENTRE_NO AND ES.ESTAB_NO = NVL(DOC.ESTAB_NO , ASS.ESTAB_NO ) AND ASS.ASSESS_NO = DOC.ASSESS_NO AND ASS.ASSESS_NO = ASSL.ASSESS_NO AND ASSL.FORM_LINE_NO = FL.FORM_LINE_NO AND FL.FORM_NO = ASS.FORM_NO AND FL.VERSION_NO = ASS.VERSION_NO AND ( ( EXISTS (SELECT 1 FROM FORM_LINE SFL WHERE SFL.LINE_TOT_TYPE_NO = 9 AND SFL.FORM_NO = ASS.FORM_NO AND SFL.VERSION_NO = ASS.VERSION_NO ) AND FL.LINE_TOT_TYPE_NO = 9 ) OR ( NOT EXISTS (SELECT 1 FROM FORM_LINE SFL WHERE SFL.LINE_TOT_TYPE_NO = 9 AND SFL.FORM_NO = ASS.FORM_NO AND SFL.VERSION_NO = ASS.VERSION_NO ) AND FL.LINE_TOT_TYPE_NO = 1 ) ) AND ( ASS.TAX_PAYER_NO = :1 OR :1 IS NULL ) AND ( ASS.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND ( TC.TAX_CENTRE_NO = :1 OR :1 IS NULL ) AND ( :1 IS NULL OR ( :1 IS NOT NULL AND DOC.TAX_PERIOD_NO IN (SELECT T.TAX_PERIOD_NO FROM TAX_PERIOD T , TAX_FORM F WHERE T.FORM_NO = F.FORM_NO AND ( F.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND T.TPER_YEAR BETWEEN :1 AND :1 AND NVL(T.TPER_MONTH , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) AND NVL(T.TPER_WEEK , 0 ) BETWEEN NVL(:1 , 0 ) AND NVL(:1 , 0 ) )) ) AND ASS.ASSESS_NO = CAS.ASSESS_NO (+) AND ( P.CASE_MANAGEMENT_GROUP_NO = CMG.CASE_MANAGEMENT_GROUP_NO (+) ) AND ( TRUNC (DOC.RECEIVED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DOC.RECEIVED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( CAS.ASSESS_STATUS_NO = :1 OR :1 IS NULL ) AND ( CMG.CASE_MANAGEMENT_GROUP_NO = :1 OR :1 IS NULL ) )
9juw6s4yy5pzp	/* OracleOEM */ SELECT SUM(broken), SUM(failed) FROM (SELECT DECODE(STATE, 'BROKEN', 1, 0) broken, DECODE(STATE, 'FAILED', 1, 0) failed FROM DBA_SCHEDULER_JOBS )
9qsxdrhhckzpf	UPDATE TIN_APPLICATION SET TIN_APPLICATION_NO=:1, REQUEST_STATUS_TYPE_NO=:2, EXISTING_TIN=:3, PREV_SYSTEM_FISCAL_NO=:4, NAT_NO=:5, TAX_CENTRE_NO=:6, LANG_NO=:7, STREET_NO=:8, MAILING_ADDRESS=:9, AREA_VILLAGE=:10, DOOR_NO=:11, CITY_NO=:12, COUNTRY_NO=:13, PO_BOX=:14, PHONE_NO=:15, MOBILE_NO=:16, RESIDENT=:17, LOCALITY_NO=:18, POST_CODE_NO=:19, TP_TYPE_NO=:20, TP_STYPE_NO=:21, APPLICATION_FORM_TYPE_NO=:22, USER_COMMENT=:23 WHERE ROWID=:24
9xpj978rhdprv	select count(*) from all_objects " sys$alias1" where " sys$alias1".owner = :1 and " sys$alias1".object_name = :2
b2u9kspucpqwy	SELECT COUNT(*) FROM SYS.DBA_PROPAGATION WHERE ERROR_MESSAGE IS NOT NULL
b56s5x8w8fjkd	SELECT FY.FISCAL_YEAR_NO, LPAD(FY.FISC_YEAR_START, 4, '0') FISC_YEAR_START, LPAD(FY.FISC_YEAR_END, 4, '0') FISC_YEAR_END, FY.FISCAL_YEAR_FROM, FY.FISCAL_YEAR_TO, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_START, 4, '0'), 3, 2)) START_MONTH, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_END, 4, '0'), 3, 2)) END_MONTH, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_START, 4, '0'), 3, 2)) - TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_END, 4, '0'), 3, 2)) DIFF, UPPER(FY.TRANSITION_FL) TRANSITION_FL FROM TAX_PAYER TP, FISCAL_YEAR FY WHERE TP.TAX_PAYER_NO = :B4 AND FY.FISCAL_YEAR_NO <> :B3 AND FY.TAX_PAYER_NO = TP.TAX_PAYER_NO AND FY.FISCAL_YEAR_TO - :B2 <= 1 AND SUBSTR(LPAD(FY.FISC_YEAR_START, 4, '0'), 3, 2) - :B1 IN (1, -11) ORDER BY FISCAL_YEAR_FROM
b61jg6p9d6xvu	INSERT INTO TIN_APPLICATION(TIN_APPLICATION_NO, REQUEST_STATUS_TYPE_NO, EXISTING_TIN, PREV_SYSTEM_FISCAL_NO, NAT_NO, TAX_CENTRE_NO, LANG_NO, STREET_NO, MAILING_ADDRESS, AREA_VILLAGE, DOOR_NO, CITY_NO, COUNTRY_NO, PO_BOX, PHONE_NO, MOBILE_NO, RESIDENT, LOCALITY_NO, POST_CODE_NO, TP_TYPE_NO, TP_STYPE_NO, APPLICATION_FORM_TYPE_NO, USER_COMMENT) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23)
bd672828bxay9	SELECT I.TAX_PAYER_NO, I.HOME_PHONE PHONE_NO, I.MOBILE_PHONE MOBILE_NO, I.NAT_CARD_ID, I.REG_DOC_NO FROM INDIVIDUAL I
bzdu10hf4hpg9	SELECT MAX (TO_CHAR(AUDIT_TIMESTAMP , 'YYYY-MM-DD' ) ) FROM SIGTAS_AUDIT_USER WHERE USERNAME = :b1 ORDER BY MAX (TO_CHAR(AUDIT_TIMESTAMP , 'YYYY-MM-DD' ) )
bzqxrgx9mwh64	SELECT PAY_DISTRIB_ORDER_NO FROM SIGTAS_RULES
ckhj49b5drm9r	SELECT COUNT (1) FROM DOCUMENT DCMT , TAX_PAYER TAX_P , INDIVIDUAL IND , CORPORATION CORP , TAX_TYPE TAX_TYP , DOC_STATE DOC_STATE , DOC_TYPE DOC_TYP , LIC_BASE , LICENSE LIC , TAXABLE_OBJECT TAX_OBJ , EMP_DEPARTMENT ED WHERE TAX_TYP.TAX_TYPE_NO (+) = DCMT.TAX_TYPE_NO AND DOC_STATE.DOC_STATE_NO = DCMT.DOC_STATE_NO AND TAX_P.TAX_PAYER_NO (+) = DCMT.TAX_PAYER_NO AND CORP.TAX_PAYER_NO (+) = TAX_P.TAX_PAYER_NO AND IND.TAX_PAYER_NO (+) = TAX_P.TAX_PAYER_NO AND DOC_TYP.DOC_TYPE_NO = DCMT.DOC_TYPE_NO AND LIC.LIC_BASE_NO = LIC_BASE.LIC_BASE_NO (+) AND LIC.LICENSE_NO (+) = DCMT.LICENSE_NO AND DCMT.TAXABLE_OBJECT_NO = TAX_OBJ.TAXABLE_OBJECT_NO (+) AND DCMT.EMP_DEPARTMENT_NO = ED.EMP_DEPARTMENT_NO (+) AND ( :1 = UPPER('y' ) OR TAX_P.SENSITIVE = UPPER('n' ) ) AND ( ( LIC.LIC_BASE_NO = :1 ) OR :1 IS NULL ) AND ( DCMT.EMP_DEPARTMENT_NO = :1 OR :1 IS NULL ) AND ( ( TAX_OBJ.TAXABLE_OBJECT_NO = :1 ) OR :1 IS NULL ) AND ( DCMT.TAX_PAYER_NO = :1 OR :1 IS NULL ) AND ( TAX_P.FISCAL_NO = :1 OR :1 IS NULL ) AND ( DCMT.TAX_TYPE_NO = :1 OR :1 IS NULL ) AND ( DCMT.DOC_TYPE_NO = :1 OR :1 IS NULL ) AND ( DCMT.TAX_CENTRE_NO = :1 OR :1 IS NULL ) AND ( DCMT.DOC_TP_YEAR = :1 OR :1 IS NULL ) AND ( DCMT.DOC_TP_MONTH = :1 OR :1 IS NULL ) AND ( DCMT.DOC_TP_WEEK = :1 OR :1 IS NULL ) AND ( DCMT.DOC_STATE_NO = :1 OR :1 IS NULL ) AND ( DCMT.TAX_PAYER_NAME = :1 OR :1 IS NULL ) AND ( DCMT.ASSESS_NO = :1 OR :1 IS NULL ) AND ( DCMT.BANK_RECEIPT_NO = :1 OR :1 IS NULL ) AND ( TRUNC (DCMT.CREATED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.CREATED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.PRINTED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.PRINTED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.RECEIVED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.RECEIVED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.DELIVERED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.DELIVERED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.DISTRIBUTED_DATE ) >= TRUNC (:1 ) OR :1 IS NULL ) AND ( TRUNC (DCMT.DISTRIBUTED_DATE ) <= TRUNC (:1 ) OR :1 IS NULL ) AND ( TAX_P.CASE_MANAGEMENT_GROUP_NO = :1 OR :1 IS NULL ) AND ( ( :1 = UPPER('Y' ) AND ( DCMT.DOC_STATE_NO = 3 OR DCMT.DOC_STATE_NO = :1 ) ) OR ( :1 = UPPER('N' ) AND DCMT.DOC_STATE_NO = :1 OR :1 IS NULL ) ) AND ( :1 = UPPER('y' ) OR ( :1 = UPPER('N' ) AND DCMT.DOC_STATE_NO != 3 ) ) AND ( ( :1 = UPPER('Y' ) AND DCMT.RECEIVED_DATE IS NULL ) OR ( :1 = UPPER('N' ) ) )
cpptqy0tj3c16	SELECT TO_CHAR(:B1 , 'FMDAY', 'NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL
cryumnkf22vgu	SELECT DOC.DOC_NO , DOC.JOB_NO FROM DOCUMENT DOC, TAX_PERIOD PER, FORM_VERSION FV, TAX_PAYER TP WHERE DOC.TAX_PERIOD_NO = PER.TAX_PERIOD_NO(+) AND PER.FORM_NO = FV.FORM_NO(+) AND PER.VERSION_NO = FV.VERSION_NO(+) AND DOC.TAX_PAYER_NO = TP.TAX_PAYER_NO(+) -- AND DOC.DOC_STATE_NO = 4 AND (DOC.DOC_NO = :P_DOC_NO OR :P_DOC_NO IS NULL) AND (DOC.JOB_NO = :P_JOB_NO OR :P_JOB_NO IS NULL) AND (( DOC.DOC_NO BETWEEN :P_DOC_FROM AND :P_DOC_TO) OR (:P_DOC_FROM IS NULL AND :P_DOC_TO IS NULL )) AND (FV.ASSESS_NOTICE_TYPE_NO = :P_ASSESS_NOTICE_TYPE_NO OR :P_ASSESS_NOTICE_TYPE_NO IS NULL) AND (TP.LANG_NO = :P_TAXPAYER_LANG_NO OR :P_TAXPAYER_LANG_NO IS NULL)
cxjqbfn0d3yqq	SELECT COUNT(*) FROM SYS.DBA_PROPAGATION
d679f48ksupph	SELECT COUNT (*) FROM DOCUMENT WHERE TAX_TYPE_NO = :1 AND TAX_CENTRE_NO = :1 AND TAX_PAYER_NO = :1 AND TAX_PERIOD_NO = :1 AND NVL(ESTAB_NO , 0 ) = NVL(:1 , 0 ) AND FORM_NO IS NULL AND VERSION_NO IS NULL AND DOC_TYPE_NO = 1 AND DOC_STATE_NO = 2
dff47t0hbjqgg	SELECT * FROM TAX_PERIOD WHERE TAX_PERIOD_NO = :B1
fzbyntmv4tfmj	select distinct tp.tper_month, tp.tper_type_no, tp.tper_year, tp.tper_week, PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :1, null, 2) tper_start_date, PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :2, null, 1) tper_end_date, PCK_TAX_PERIOD.DUEDT_FILE(tp.tax_period_no, :3, :4, null, null, null) tper_due_date, PCK_TAX_PERIOD.DUEDT_PAY(tp.tax_period_no, :5, :6, null, null, null) tper_payment_date, tp.tp_date_base_no, tp.tper_due_date_interval, tp.tax_period_no, tp.version_no, tp.imposition_base_no, substr(F_TRANSLATE(impb.imposition_base_desc, :7, 'imposition_base'), 1, 100) imposition_base_desc, tp.tper_assess_days, tp.tax_on_roll_fl, substr(f_translate(PCK_DOCUMENT.F_GET_REM_PRINTED_BY_TAXPAYER(TP.TAX_PERIOD_NO, :8, :9), :10, NULL, 'S', 'N'), 1, 100) REMITTANCE from tax_period tp, imposition_base impb , tax_acct_base_imp tabi where impb.imposition_base_no(+) = tp.imposition_base_no AND impb.imposition_base_no = tabi.imposition_base_no(+) and form_no = :11 and version_no = :12 and ( (NOT EXISTS (select 1 from tax_acct_base_imp tabi1 where tabi1.tax_account_no=:13 ) and tp.imposition_base_no is null ) OR (EXISTS (select 1 from tax_acct_base_imp tabi1 where tabi1.tax_account_no=:14 ) and tabi.tax_account_no=:15 ) ) AND ( tabi.start_date IS NULL OR ( tabi.tax_account_no = :16 and /* trunc(tp.tper_start_date) BETWEEN trunc(tabi.start_date) AND trunc(nvl(tabi.end_date, SYSDATE+99999)) OR trunc(tabi.start_date) BETWEEN trunc(tp.tper_start_date) AND trunc(nvl(tp.tper_end_date, SYSDATE+99999))*/ trunc(tabi.start_date) = trunc(get_imp_base_valid(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :17, null, 2), PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :18, null, 1), :19)) ) ) and (trunc(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :20, null, 2)) BETWEEN (:21) AND nvl(:22, trunc(SYSDATE + 99999)) OR :23 BETWEEN trunc(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :24, null, 2)) AND trunc(nvl(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :25, null, 1), SYSDATE + 99999))) and not exists ( SELECT 1 FROM assessment ass WHERE tax_account_no = :26 AND ass.tax_period_no= tp.tax_period_no AND (ass.estab_no = :27 OR :28 IS NULL) ) /*active tax_account*/ and not exists (select 1 from TAXA_INACTIVE_PERIOD WHERE tax_account_no = :29 AND (estab_no = :30 OR :31 IS NULL) AND (taxable_object_no = :32 OR :33 IS NULL) AND (license_no = :34 OR :35 IS NULL) AND (start_date <=PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :36, null, 2)) AND (end_date IS NULL OR end_date >= PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no, :37, null, 1) ) AND exemp_type_no = 1 ) /*no printed remittence*/ and not exists (SELECT 1 FROM document doc WHERE doc.tax_period_no =tp.tax_period_no AND doc.tax_type_no = :38 AND doc.tax_payer_no = :39 AND doc.tax_centre_no = :40 AND doc.doc_type_no = 1 /* Remittance*/ AND doc.doc_state_no <> 3 /* DISCARDED */ AND (doc.estab_no = :41 OR :42 IS NULL) ) order by tper_year desc, tper_month desc
g0c26tu9utyck	select tc . tax_centre_desc , p . fiscal_no , pck_util . get_taxp_name_by_lang_trans ( i . tax_payer_no , 1 , 'n' , : p_lang_no ) name , doc . ext_doc_no , i . work_phone work_phone , p . tp_type_no , ass . tax_account_no , f_translate ( tax_type_desc , : p_lang_no , 'tax_type' ) tax_type_desc , doc_tp_month , doc_tp_year , doc . received_date , ass . tax_to_pay * ( - 1 ) + ass . pen_to_pay * ( - 1 ) + ass . int_to_pay * ( - 1 ) tax_to_pay , doc_tp_week , ass . assess_no assessment_no , ass . tax_period_no taxperiod , tt . tax_type_no , to_char ( tp . tper_month , '09' ) || '' || to_char ( tp . tper_year , '9999' ) || '' || to_char ( tp . tper_week , '09' ) tax_period , f_translate ( cmg . case_management_group_desc , : p_lang_no , 'case_management_group' ) case_management_group_desc from individual i , tax_centre tc , tax_payer p , license l , tax_type tt , document doc , assessment ass , change_assess_status cas , tax_period tp , case_management_group cmg where ass.tax_payer_no = i.tax_payer_no (+) and ass.tax_account_no = l.tax_account_no (+) and tt.tax_type_no = ass.tax_type_no and p.tax_centre_no = tc.tax_centre_no and ass.tax_payer_no = p.tax_payer_no and ass.assess_no = doc.assess_no and ( doc.tax_period_no = tp.tax_period_no or doc.tax_period_no is null ) and doc.tax_payer_no = ass.tax_payer_no and doc.tax_type_no = ass.tax_type_no and p.case_management_group_no = cmg.case_management_group_no (+) and tt.tax_basis_no <> 1 and p.tp_type_no = 1 and doc_type_no = 1 and doc_state_no = 2 and printed_date is not null and received_date is not null and ( : p_user_sensitive_fl = upper ( 'y' ) or p.sensitive = upper ( 'n' ) ) and ( ass.tax_payer_no = : p_tax_payer_no or : p_tax_payer_no is null ) and ( ass.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and ( tc.tax_centre_no = : p_tax_centre_no or : p_tax_centre_no is null ) and ( cmg.case_management_group_no = : p_case_management_group_no or : p_case_management_group_no is null ) and ( : p_tper_year is null or ( : p_tper_year is not null and doc.tax_period_no in ( select t.tax_period_no from tax_period t , tax_form f where t.form_no = f.form_no and ( f.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and t.tper_year between : p_tper_year and : p_tper_year_to and nvl ( t.tper_month , 0 ) between nvl ( : p_tper_month , 0 ) and nvl ( : p_tper_month_to , 0 ) and nvl ( t.tper_week , 0 ) between nvl ( : p_tper_week , 0 ) and nvl ( : p_tper_week_to , 0 ) ) ) ) and ass.assess_no = cas.assess_no (+) and ( trunc ( doc.received_date ) >= trunc ( : p_received_from ) or : p_received_from is null ) and ( trunc ( doc.received_date ) <= trunc ( : p_received_to ) or : p_received_to is null ) and ( cas.assess_status_no = : p_assess_status_no or : p_assess_status_no is null ) union select tax_centre_desc , p . fiscal_no , pck_util . get_taxpayer_name_by_lang ( c . tax_payer_no , 1 , 'n' , : p_lang_no ) name , doc . ext_doc_no , e . phone_no work_phone , p . tp_type_no , ass . tax_account_no , f_translate ( tax_type_desc , : p_lang_no , 'tax_type' ) tax_type_desc , doc_tp_month , doc_tp_year , doc . received_date , ass . tax_to_pay * ( - 1 ) + ass . pen_to_pay * ( - 1 ) + ass . int_to_pay * ( - 1 ) tax_to_pay , doc_tp_week , ass . assess_no assessment_no , ass . tax_period_no taxperiod , tt . tax_type_no , to_char ( tp . tper_month , '09' ) || '' || to_char ( tp . tper_year , '9999' ) || '' || to_char ( tp . tper_week , '09' ) tax_period , f_translate ( cmg . case_management_group_desc , : p_lang_no , 'case_management_group' ) case_management_group_desc from corporation c , enterprise e , tax_centre tc , establishment es , tax_payer p , license l , tax_type tt , document doc , assessment ass , change_assess_status cas , tax_period tp , case_management_group cmg where ass.tax_payer_no = c.tax_payer_no (+) and ass.tax_payer_no = e.tax_payer_no (+) and p.tax_centre_no = tc.tax_centre_no a nd ass.tax_payer_no = p.tax_payer_no and ass.tax_type_no = tt.tax_type_no and e.enterprise_no = es.enterprise_no (+) and ass.tax_account_no = l.tax_account_no (+) and doc.tax_payer_no = ass.tax_payer_no and doc.tax_type_no = ass.tax_type_no and ass.assess_no = doc.assess_no and ( doc.tax_period_no = tp.tax_period_no or doc.tax_period_no is null ) and p.case_management_group_no = cmg.case_management_group_no (+) and es.head_office = upper ( 'y' ) and tt.tax_basis_no <> 1 and p.tp_type_no = 2 and doc_type_no = 1 and doc_state_no = 2 and printed_date is not null and received_date is not null and ( : p_user_sensitive_fl = upper ( 'y' ) or p.sensitive = upper ( 'n' ) ) and ( ass.tax_payer_no = : p_tax_payer_no or : p_tax_payer_no is null ) and ( ass.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and ( tc.tax_centre_no = : p_tax_centre_no or : p_tax_centre_no is null ) and ( cmg.case_management_group_no = : p_case_management_group_no or : p_case_management_group_no is null ) and ( : p_tper_year is null or ( : p_tper_year is not null and doc.tax_period_no in ( select t.tax_period_no from tax_period t , tax_form f where t.form_no = f.form_no and ( f.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and t.tper_year between : p_tper_year and : p_tper_year_to and nvl ( t.tper_month , 0 ) between nvl ( : p_tper_month , 0 ) and nvl ( : p_tper_month_to , 0 ) and nvl ( t.tper_week , 0 ) between nvl ( : p_tper_week , 0 ) and nvl ( : p_tper_week_to , 0 ) ) ) ) and ass.assess_no = cas.assess_no (+) and ( trunc ( doc.received_date ) >= trunc ( : p_received_from ) or : p_received_from is null ) and ( trunc ( doc.received_date ) <= trunc ( : p_received_to ) or : p_received_to is null ) and ( cas.assess_status_no = : p_assess_status_no or : p_assess_status_no is null ) union SELECT tax_centre_desc , p . fiscal_no , pck_util . get_taxpayer_name_by_lang ( ass . tax_payer_no , 1 , 'N' , : p_lang_no ) NAME , doc . ext_doc_no , es . phone_no work_phone , p . tp_type_no , ass . tax_account_no , f_translate ( tax_type_desc , : p_lang_no , 'tax_type' ) tax_type_desc , doc . doc_tp_month , doc . doc_tp_year , doc . received_date , nvl ( pck_transaction . TAX_DUE_ON_TAX_PERIOD ( ass . tax_account_no , ass . tax_period_no , ass . estab_no , NULL , NULL , NULL ) * ( - 1 ) , 0 ) + nvl ( pck_transaction . PEN_DUE_ON_TAX_PERIOD ( ass . tax_account_no , ass . tax_period_no , ass . estab_no , NULL , NULL , NULL ) * ( - 1 ) , 0 ) + nvl ( pck_transaction . INT_DUE_ON_TAX_PERIOD ( ass . tax_account_no , ass . tax_period_no , ass . estab_no , NULL , NULL , NULL ) * ( - 1 ) , 0 ) tax_to_pay , doc . doc_tp_week , ass . assess_no assessment_no , ass . tax_period_no taxperiod , tt . tax_type_no , DECODE ( nvl ( doc . doc_tp_month , 0 ) , 0 , to_char ( doc . doc_tp_year , '9999' ) , to_char ( doc . doc_tp_month , '09' ) || '-' || to_char ( doc . doc_tp_year , '9999' ) ) tax_period , f_translate ( cmg . case_management_group_desc , : p_lang_no , 'case_management_group' ) case_management_group_desc FROM tax_centre tc , establishment es , tax_payer p , tax_estab te , license l , case_management_group cmg , tax_type tt , ( SELECT assess_no , tax_payer_no , tax_type_no , tax_centre_no , tax_period_no , ext_doc_no , doc_tp_month , estab_no , doc_tp_year , batch_no , received_incomplete_date , suspicious_remittance_fl , received_date , doc_tp_week FROM DOCUMENT WHERE assess_no IS NOT NULL AND doc_state_no = 2 AND doc_type_no = 1 AND printed_date IS NOT NULL UNION SELECT dd . assess_no , dd . tax_payer_no , dd . tax_type_no , dd . tax_centre_no , dd . tax_period_no , dd . ext_doc_no , dd . doc_tp_month , dd . estab_no , dd . doc_tp_year , dd . batch_no , dd . received_incomplete_date , dd . suspicious_remittance_fl , nvl ( dd . received_date , dd . created_date ) received_date , dd . doc_tp_week FROM DOCUMENT dd WHERE dd . assess_no IS NOT NULL AND dd . doc_typ e_no = 4 AND EXISTS ( SELECT 1 FROM DOCUMENT WHERE doc_type_no = 8 AND doc_state_no = 2 AND printed_date IS NOT NULL AND assess_no = dd . assess_no ) ) doc , assessment ass , change_assess_status cas , assess_line assl , form_line fl WHERE ass.tax_account_no = te.tax_account_no (+) AND ass.tax_account_no = l.tax_account_no (+) AND ( es.estab_no = te.estab_no OR es.estab_no = l.estab_no ) AND tt.tax_type_no = ass.tax_type_no AND ass.tax_payer_no = p.tax_payer_no AND tt.tax_basis_no = 1 AND doc.tax_payer_no = ass.tax_payer_no AND doc.tax_type_no = ass.tax_type_no AND doc.tax_centre_no = tc.tax_centre_no AND es.estab_no = nvl ( doc.estab_no , ass.estab_no ) AND ass.assess_no = doc.assess_no AND ass.assess_no = assl.assess_no AND assl.form_line_no = fl.form_line_no AND fl.form_no = ass.form_no and fl.version_no = ass.version_no AND ( ( EXISTS ( SELECT 1 FROM FORM_LINE SFL WHERE SFL.LINE_TOT_TYPE_NO = 9 AND SFL.FORM_NO = ASS.FORM_NO AND SFL.VERSION_NO = ASS.VERSION_NO ) AND fl.line_tot_type_no = 9 ) OR ( NOT EXISTS ( SELECT 1 FROM FORM_LINE SFL WHERE SFL.LINE_TOT_TYPE_NO = 9 AND SFL.FORM_NO = ASS.FORM_NO AND SFL.VERSION_NO = ASS.VERSION_NO ) AND fl.line_tot_type_no = 1 ) ) and ( : p_user_sensitive_fl = upper ( 'y' ) or p.sensitive = upper ( 'n' ) ) and ( ass.tax_payer_no = : p_tax_payer_no or : p_tax_payer_no is null ) and ( ass.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and ( tc.tax_centre_no = : p_tax_centre_no or : p_tax_centre_no is null ) and ( : p_tper_year is null or ( : p_tper_year is not null and doc.tax_period_no in ( select t.tax_period_no from tax_period t , tax_form f where t.form_no = f.form_no and ( f.tax_type_no = : p_tax_type_no or : p_tax_type_no is null ) and t.tper_year between : p_tper_year and : p_tper_year_to and nvl ( t.tper_month , 0 ) between nvl ( : p_tper_month , 0 ) and nvl ( : p_tper_month_to , 0 ) and nvl ( t.tper_week , 0 ) between nvl ( : p_tper_week , 0 ) and nvl ( : p_tper_week_to , 0 ) ) ) ) and ass.assess_no = cas.assess_no (+) and ( p.case_management_group_no = cmg.case_management_group_no (+) ) and ( trunc ( doc.received_date ) >= trunc ( : p_received_from ) or : p_received_from is null ) and ( trunc ( doc.received_date ) <= trunc ( : p_received_to ) or : p_received_to is null ) and ( cas.assess_status_no = : p_assess_status_no or : p_assess_status_no is null ) and ( cmg.case_management_group_no = : p_case_management_group_no or : p_case_management_group_no is null ) ORDER BY 1 ASC, 18 ASC
gg4tm70awdybg	SELECT TAX_PAYER_NO FROM TAX_ACCOUNT WHERE TAX_ACCOUNT_NO = :B1
ggcuw7tq205ft	SELECT FY.FISCAL_YEAR_NO, LPAD(FY.FISC_YEAR_START, 4, '0') FISC_YEAR_START, LPAD(FY.FISC_YEAR_END, 4, '0') FISC_YEAR_END, FY.FISCAL_YEAR_FROM, FY.FISCAL_YEAR_TO, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_START, 4, '0'), 3, 2)) START_MONTH, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_END, 4, '0'), 3, 2)) END_MONTH, TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_START, 4, '0'), 3, 2)) - TO_NUMBER(SUBSTR(LPAD(FY.FISC_YEAR_END, 4, '0'), 3, 2)) DIFF FROM TAX_PAYER TP, FISCAL_YEAR FY WHERE FY.TAX_PAYER_NO = TP.TAX_PAYER_NO AND TP.TAX_PAYER_NO = :B2 AND :B1 BETWEEN FY.FISCAL_YEAR_FROM AND NVL (FY.FISCAL_YEAR_TO, 9999) AND TRANSITION_FL = 'N' ORDER BY 1
gvk82zjtzzmp9	SELECT to_char ( TAX_TRANS . TRANS_DATE , 'YYYYMMDD' ) TRANS_DATE2 , TAX_STRANS . AMOUNT AMOUNT , TAX_TRANS . TAX_PAYER_NO TAX_PAYER_NO , DECODE ( TAX_TRANS . TAX_PAYER_NO , NULL , NVL ( PCK_UTIL . GET_TAXPAYER_NAME_IN_DOC ( REV_TRANS . DOC_NO , 'N' , : P_LANG_NO ) , TAX_TRANS . UNDIST_FIRST_NAME || ' ' || TAX_TRANS . UNDIST_LAST_NAME ) , PCK_UTIL . GET_TAXPAYER_NAME_BY_LANG ( TAX_P . TAX_PAYER_NO , 1 , 'N' , : P_LANG_NO ) ) TAXPAYER_NAME , ESTAB . NAME ESTAB_NAME , LIC_BASE . BASED_ON BASED_ON , TAX_TRANS . TRANS_DATE , TAX_TRANS . ENTER_DATE , TAX_TRANS . ENTER_DATE - TAX_TRANS . TRANS_DATE NO_DAYS_BETWEEN , TAX_TRANS . DOC_NO DOC_NO , DOCUMENT . EXT_DOC_NO , REV_TRANS . RECEIPT_NO RECEIPT_NO , TAX_P . FISCAL_NO , f_translate ( charge_type_desc , : p_lang_no , 'CHARGE_TYPE' ) charge_type_desc , f_translate ( payment_loc_desc , : p_lang_no , 'PAYMENT_LOC_DESC' ) payment_loc_desc , f_translate ( TAX_TRANS_TYPE_DESC , : P_LANG_NO , 'TAX_TRANS_TYPE' ) TAX_TRANS_TYPE_DESC , TAX_STRANS . TAX_PERIOD_NO , TAX_PERIOD . TPER_YEAR , TAX_PERIOD . TPER_MONTH , TAX_PERIOD . TPER_WEEK , DECODE ( tax_strans . taxable_object_no , NULL , NULL , ( SUBSTR ( pck_object . get_taxable_object_name_fn ( tax_strans . taxable_object_no , trunc ( sysdate ) ) , 0 , 500 ) ) ) taxable_object , DECODE ( : P_GROUP_BY_ASSESS_TC , 'Y' , ASS . TAX_CENTRE_NO , - 1 ) PAYMENT_LOC_NO FROM TAX_TRANSACTION TAX_TRANS , TAX_TRANSACTION REV_TRANS , TAX_PAYER TAX_P , DOCUMENT , TAX_SUB_TRANS TAX_STRANS , ESTABLISHMENT ESTAB , LIC_BASE LIC_BASE , LICENSE LIC , TAX_TRANS_TYPE TTYP , CHARGE_TYPE CTYP , TAX_PERIOD , ( select * from tax_acct_base_imp where end_date is null ) tabi , payment_loc pay , TAX_CENTRE TC , ASSESSMENT ASS , payment_loc_detail pld , treasury_reg_office tro WHERE 1 = 1 AND REV_TRANS.REVERSE_TRANS_NO (+) = TAX_TRANS.TAX_TRANS_NO AND TAX_STRANS.CHARGE_TYPE_NO = CTYP.CHARGE_TYPE_NO AND TC.TAX_CENTRE_NO (+) = TAX_TRANS.TAX_CENTRE_NO AND TAX_STRANS.TAX_TRANS_TYPE_NO = TTYP.TAX_TRANS_TYPE_NO AND TAX_STRANS.ESTAB_NO = ESTAB.ESTAB_NO (+) AND TAX_STRANS.tax_account_no = TABI.tax_account_no (+) AND TAX_STRANS.TAX_PERIOD_NO = TAX_PERIOD.TAX_PERIOD_NO (+) AND TAX_STRANS.LICENSE_NO = LIC.LICENSE_NO (+) AND LIC_BASE.LIC_BASE_NO (+) = LIC.LIC_BASE_NO AND TAX_STRANS.TAX_TRANS_NO = TAX_TRANS.TAX_TRANS_NO AND DOCUMENT.DOC_NO (+) = TAX_TRANS.DOC_NO AND pay.payment_loc_no (+) = TAX_TRANS.payment_loc_no AND TAX_P.TAX_PAYER_NO (+) = TAX_TRANS.TAX_PAYER_NO AND TAX_STRANS.ASSESS_NO = ASS.ASSESS_NO (+) AND tax_strans.amount between nvl ( : p_amt_from , tax_strans.amount + 10 ) and nvl ( : p_amt_to , tax_strans.amount - 10 ) AND pay.payment_loc_no = pld.payment_loc_no (+) AND ( pld.treasury_reg_office_no = tro.treasury_reg_office_no (+) and tax_trans.trans_date between pld.start_date and nvl ( pld.end_date , sysdate + 99999 ) ) AND TAX_STRANS.CHARGE_TYPE_NO = 4 and ( : p_user_sensitive_fl = upper ( 'y' ) or tax_p.sensitive = upper ( 'N' ) ) and ( TAX_STRANS.TAX_TYPE_NO = TO_NUMBER ( : PAY_TAX_TYPE_NO ) OR : PAY_TAX_TYPE_NO IS NULL ) AND ( tax_strans.tax_centre_no = to_number ( : p_tax_centre_no ) OR : p_tax_centre_no IS NULL ) and ( tax_p.case_management_group_no = : p_case_man_group_no or : p_case_man_group_no is null ) AND TAX_TRANS.TRANS_DATE >= TO_DATE ( : FROM_DATE , 'DD-MM-YYYY' ) AND TAX_TRANS.TRANS_DATE <= TO_DATE ( nvl ( : TO_DATE , '01-01-9999' ) , 'DD-MM-YYYY' ) and tax_strans.enter_date between : p_entered_from_date and : p_entered_to_date AND ( tax_trans.payment_loc_no = : p_payment_loc_no OR : p_payment_loc_no IS NULL ) AND ( ( trunc ( NVL ( REV_TRANS.ENTER_DATE , SYSDATE ) ) - trunc ( NVL ( REV_TRANS.TRANS_DATE , SYSDATE ) ) ) >= : P_MAX_NO_DAYS_BETWEEN OR : P_MAX_NO_DAYS_BETWEEN IS NULL ) AND ( tax_trans.ird_employee_no = : p_ird_employee_no OR : p_ird_employee_no IS NULL ) AND ( TAX_STRANS.COLLECTION_LOCATION_NO = : P_COLLECTION_LOC_NO OR : P_COLLECTION_LOC_NO IS NULL ) AND ( TABI.im position_base_no = : p_imposition_base_no OR : p_imposition_base_no IS NULL ) AND ( ( TAX_STRANS.TAX_PERIOD_NO = TO_NUMBER ( : PAY_TAX_PERIOD_NO ) ) OR : PAY_TAX_PERIOD_NO IS NULL ) AND ( lic.lic_base_no = to_number ( : pay_lic_based_on_no ) OR : pay_lic_based_on_no IS NULL ) AND ( lic_base.lic_base_no = to_number ( : pay_lic_based_on_no ) OR : pay_lic_based_on_no IS NULL ) AND ( tax_strans.taxable_object_no = : p_taxable_object_no OR : p_taxable_object_no IS NULL ) AND ( tro.treasury_reg_office_no = : P_tro_no or : P_tro_no is NULL ) AND ( ass.tax_centre_no = : p_ass_tax_centre_no OR : p_ass_tax_centre_no IS NULL ) ORDER BY 22 ASC , 1 ASC , pay.payment_loc_no , tax_p.fiscal_no , f_translate ( TAX_TRANS_TYPE_DESC , : P_LANG_NO , 'TAX_TRANS_TYPE' )

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

• 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

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Buffer Pool Statistics
• Checkpoint Activity

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 Buffer Pool Statistics
Back to Top

Checkpoint Activity

• Total Physical Writes: 28,245

Back to Buffer Pool Statistics
Back to Top

Advisory Statistics

• Instance Recovery Stats
• MTTR Advisory
• 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

MTTR Advisory

No data exists for this section of the report.

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

No data exists for this section of the report.

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

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
• Mutex Sleep Summary
• 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