WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
PORTALDB	663718102	portaldb1	1	22-Feb-14 05:02	11.2.0.3.0	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
ptdb1	AIX-Based Systems (64-bit)	64	16		250.25

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	9521	18-Jul-14 08:00:08	645	12.8
End Snap:	9522	18-Jul-14 08:59:59	747	12.9
Elapsed:		59.86 (mins)
DB Time:		832.96 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	78,592M	78,592M	Std Block Size:	16K
Shared Pool Size:	15,872M	15,872M	Log Buffer:	82,068K

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	13.9	1.2	0.01	0.01
DB CPU(s):	4.6	0.4	0.00	0.00
Redo size:	18,445,715.1	1,564,008.3
Logical reads:	145,508.4	12,337.6
Block changes:	56,352.9	4,778.2
Physical reads:	960.7	81.5
Physical writes:	4,049.4	343.4
User calls:	1,250.8	106.1
Parses:	76.4	6.5
Hard parses:	32.4	2.8
W/A MB processed:	22.4	1.9
Logons:	0.7	0.1
Executes:	1,860.5	157.8
Rollbacks:	0.5	0.0
Transactions:	11.8

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:		Redo NoWait %:	100.00
Buffer Hit %:	99.39	In-memory Sort %:	100.00
Library Hit %:	95.39	Soft Parse %:	57.60
Execute to Parse %:	95.89	Latch Hit %:	99.26
Parse CPU to Parse Elapsd %:	59.12	% Non-Parse CPU:	91.37

Shared Pool Statistics

	Begin	End
Memory Usage %:	95.64	96.09
% SQL with executions>1:	95.16	81.82
% Memory for SQL w/exec>1:	96.20	83.29

Top 5 Timed Foreground Events

Host CPU (CPUs: 64 Cores: 16 Sockets: )

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
1.05	11.37	12.3	1.2	4.3	86.5

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
7.5	55.3	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	256,256.0	256,256.0
SGA use (MB):	96,338.3	96,338.3
PGA use (MB):	8,673.8	11,217.1
% Host Mem used for SGA+PGA:	40.98	41.97

RAC Statistics

	Begin	End
Number of Instances:	2	2

Global Cache Load Profile

	Per Second	Per Transaction
Global Cache blocks received:	231.79	19.65
Global Cache blocks served:	126.32	10.71
GCS/GES messages received:	580.68	49.24
GCS/GES messages sent:	921.58	78.14
DBWR Fusion writes:	6.05	0.51
Estd Interconnect traffic (KB)	6,023.12

Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %:	99.23
Buffer access - remote cache %:	0.16
Buffer access - disk %:	0.61

Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms):	0.0
Avg global cache cr block receive time (ms):	1.0
Avg global cache current block receive time (ms):	1.6
Avg global cache cr block build time (ms):	0.0
Avg global cache cr block send time (ms):	0.0
Global cache log flushes for cr blocks served %:	17.9
Avg global cache cr block flush time (ms):	3.7
Avg global cache current block pin time (ms):	0.3
Avg global cache current block send time (ms):	0.0
Global cache log flushes for current blocks served %:	0.2
Avg global cache current block flush time (ms):	20.5

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms):	0.1
Avg message sent queue time on ksxp (ms):	0.7
Avg message received queue time (ms):	0.0
Avg GCS message process time (ms):	0.0
Avg GES message process time (ms):	0.0
% of direct sent messages:	28.52
% of indirect sent messages:	70.28
% of flow controlled messages:	1.20

Cluster Interconnect

• if IP/Public/Source at End snap is different a '*' is displayed

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

More RAC Statistics

• RAC Report Summary
• Global Messaging Statistics
• Global CR Served Stats
• Global CURRENT Served Stats
• Global Cache Transfer Stats
• Interconnect Stats
• Dynamic Remastering Statistics

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): 49977.4s
• 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 80.1% of Total DB time 49,977.42 (s)
• Total FG Wait Time: 23,481.16 (s) DB CPU time: 16,571.23 (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
• SQL ordered by Cluster Wait Time
• 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 38.7% of Total DB Time (s): 49,977
• Captured PL/SQL account for 3.2% of Total DB Time (s): 49,977

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 58.4% of Total CPU Time (s): 16,571
• Captured PL/SQL account for 2.6% of Total CPU Time (s): 16,571

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 8.8% of Total User I/O Wait Time (s): 21,546
• Captured PL/SQL account for 3.0% of Total User I/O Wait Time (s): 21,546

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: 522,584,764
• Captured SQL account for 53.7% 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: 3,450,262
• Captured SQL account for 8.4% 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: 3,273,954
• Captured SQL account for 4.5% of Total
• Total UnOptimized Read Requests: 3,273,954
• Captured SQL account for 4.5% 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: 6,681,680
• Captured SQL account for 70.7% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 274,449
• Captured SQL account for 6.1% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

• Only Statements with Version Count greater than 20 are displayed

Back to SQL Statistics
Back to Top

SQL ordered by Cluster Wait Time

• %Total - Cluster Time as a percentage of Total Cluster Wait Time
• %Clu - Cluster Time as a percentage of Elapsed Time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Only SQL with Cluster Wait Time > .005 seconds is reported
• Total Cluster Wait Time (s): 917
• Captured SQL account for 38.4% of Total

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
08cbsx27yyjfd	INSERT INTO M_TRANSFERITEM (ID, AD_CLIENT_ID, AD_ORG_ID, M_TRANSFER_ID, BOXNO, STORAGENO, M_PRODUCTALIAS_ID, M_PRODUCT_ID, QTYAPPLY, QTY, DESCRIPTION, STATUS, ORDERNO, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19)
09gnpt5k762zc	INSERT INTO RP_SALE001 (ID, AD_CLIENT_ID, SALEBILLTYPE, BILLDATE, DOCNO, DOCTYPE, B_SO_ID, C_CUSTOMERUP_ID, C_STORE_ID, C_CUSTOMER_ID, C_DEST_ID, DESCRIPTION, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, AMTSO, QTYORDERED, QTYSALE, QTYSALEOUT, QTYRETSALE, QTYRETSALEIN, PRICELIST, AMTLISTSALE, AMTLISTSALEOUT, AMTLISTRETSALE, AMTLISTRETSALEIN, PRICEACTUAL, AMTSALE, AMTSALEOUT, AMTRETSALE, AMTRETSALEIN, DISCOUNTSALE, DISCOUNTRETSALE, OUTERID, INERID, M_PRODUCT_ALIAS_ID, C_SALEDISTYPE_ID, AD_PI_ID, MXREMARK, C_SALETYPE_ID, PICKERID, C_RETSALETYPE_ID) SELECT 1, 37, A.SALEBILLTYPE, A.DATEOUT AS BILLDATE, A.DOCNO, A.DOCTYPE, A.B_SO_ID, A.C_CUSTOMERUP_ID, A.C_STORE_ID, A.C_CUSTOMER_ID, A.C_DEST_ID, A.DESCRIPTION, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, 0 AS AMTSO, 0 AS QTYORDERED, B.QTY AS QTYSALE, B.QTYOUT AS QTYSALEOUT, 0 AS QTYRETSALE, 0 AS QTYRETSALEIN, B.PRICELIST, B.TOT_AMT_LIST AS AMTLISTSALE, B.TOT_AMTOUT_LIST AS AMTLISTSALEOUT, 0 AS AMTLISTRETSALE, 0 AS AMTLISTRETSALEIN, B.PRICEACTUAL, B.TOT_AMT_ACTUAL AS AMTSALE, B.TOT_AMTOUT_ACTUAL AS AMTSALEOUT, 0 AS AMTRETSALE, 0 AS AMTRETSALEIN, B.DISCOUNT AS DISCOUNTSALE, 0 AS DISCOUNTRETSALE, A.OUTERID, NULL AS INERID, B.M_PRODUCTALIAS_ID AS M_PRODUCT_ALIAS_ID, A.C_SALEDISTYPE_ID, :B8 , B.DESCRIPTION, A.C_SALETYPE_ID, A.PICKERID, NULL FROM M_SALE A, M_SALEITEM B, M_PRODUCT_ALIAS N, TABLE(:B1 ) RSD, TABLE(:B2 ) RS, TABLE(:B3 ) RD, TABLE(:B4 ) RC, TABLE(:B5 ) RP WHERE A.ID = B.M_SALE_ID AND A.C_SALEDISTYPE_ID = RSD.ID AND A.C_STORE_ID = RS.ID AND A.C_DEST_ID = RD.ID AND A.C_CUSTOMER_ID = RC.ID AND B.M_PRODUCT_ID = RP.ID AND B.QTYOUT != 0 AND A.STATUS = 2 AND A.OUT_STATUS = 2 AND A.DATEOUT BETWEEN :B7 AND :B6 UNION ALL SELECT 1, 37, NULL AS SALEBILLTYPE, A.DATEIN AS BILLDATE, A.DOCNO, A.DOCTYPE, NULL AS B_SO_ID, A.C_CUSTOMERUP_ID, A.C_STORE_ID, A.C_CUSTOMER_ID, A.C_ORIG_ID AS C_DEST_ID, A.DESCRIPTION, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, 0 AS AMTSO, 0 AS QTYORDERED, 0 AS QTYSALE, 0 AS QTYSALEOUT, B.QTY AS QTYRETSALE, B.QTYIN AS QTYRETSALEIN, B.PRICELIST, 0 AS AMTLISTSALE, 0 AS AMTLISTSALEOUT, B.TOT_AMT_LIST AS AMTLISTRETSALE, B.TOT_AMTIN_LIST AS AMTLISTRETSALEIN, B.PRICEACTUAL, 0 AS AMTSALE, 0 AS AMTSALEOUT, B.TOT_AMT_ACTUAL AS AMTRETSALE, B.TOT_AMTIN_ACTUAL AS AMTRETSALEIN, 0 AS DISCOUNTSALE, B.DISCOUNT AS DISCOUNTRETSALE, NULL AS OUTERID, A.INERID, B.M_PRODUCTALIAS_ID AS M_PRODUCT_ALIAS_ID, A.C_SALEDISTYPE_ID, :B8 , B.DESCRIPTION, NULL, A.PICKERID, A.C_RET_SALETYPE_ID FROM M_RET_SALE A, M_RET_SALEITEM B, TABLE(:B1 ) RSD, TABLE(:B2 ) RS, TABLE(:B3 ) RD, TABLE(:B4 ) RC, TABLE(:B5 ) RP WHERE A.ID = B.M_RET_SALE_ID AND A.C_SALEDISTYPE_ID = RSD.ID AND A.C_STORE_ID = RS.ID AND A.C_ORIG_ID = RD.ID AND A.C_CUSTOMER_ID = RC.ID AND B.M_PRODUCT_ID = RP.ID AND B.QTYOUT != 0 AND A.STATUS = 2 AND A.IN_STATUS = 2 AND A.DATEIN BETWEEN :B7 AND :B6
0y915smz5fa5f	UPDATE B_RECEIVABLE T SET T.M_DIM1_ID = (SELECT MAX(B.M_DIM1_ID) FROM M_SALEITEM A, M_PRODUCT B WHERE A.M_PRODUCT_ID = B.ID AND A.M_SALE_ID = T.M_SALE_ID) WHERE T.M_SALE_ID IS NOT NULL AND T.ID = :B1
103hjntt928hq	SELECT T.C_CUSTOMERUP_ID, ISCREFUND, IS_SALE_PCK FROM C_CUSTOMER T WHERE T.ID = :B1
14m1h2g5f2tqx	Select Seq_AD_PINSTANCE.NextVal From Dual
1anrmrtutudx5	UPDATE B_RETAILDISSKU SET TOT_LINES = (SELECT COUNT(1) FROM B_RETAILDISSKUITEM WHERE B_RETAILDISSKU_ID = :B1 ) WHERE ID = :B1
1frv9drq8npdm	INSERT INTO M_INVENTORYITEM (ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE, M_INVENTORY_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, QTYBOOK, QTYCOUNT, QTYDIFF, PRICELIST, TOT_AMTBOOK_LIST, TOT_AMTCOUNT_LIST, TOT_AMTDIFF_LIST, tot_amtdiff_precost) SELECT get_sequences('M_INVENTORYITEM'), b.ad_client_id, b.ad_org_id, b.modifierid, b.modifierid, SYSDATE, SYSDATE, 'Y', 48546, a.m_product_id, a.m_attributesetinstance_id, qty, 0, 0 - qty, c.pricelist, c.pricelist * qty, 0, c.pricelist * (0 - qty), NVL(d.percost, c.precost) * (0 - qty) FROM (SELECT t.ad_client_id, t.ad_org_id, t.c_store_id, t.m_product_id, t.m_attributesetinstance_id, SUM(t.qtychange) AS qty FROM fa_storage_ftp t WHERE t.c_store_id = 5519AND t.changedate <= 20140717AND t.m_product_id in(select t.id from m_product t where t.id IN(SELECT M_PRODUCT.ID FROM M_PRODUCT WHERE ( (M_PRODUCT.AD_CLIENT_ID=37) ) AND ( ( (M_PRODUCT.M_DIM4_ID IN(SELECT M_DIM.ID FROM M_DIM WHERE ( (M_DIM.AD_CLIENT_ID=37) ) AND ( (M_DIM.ID IN(20)) ))) AND (M_PRODUCT.ISACTIVE = 'Y') ) ))) GROUP BY t.ad_client_id, t.ad_org_id, t.c_store_id, t.m_product_id, t.m_attributesetinstance_id) a, m_inventory b, m_product c, fa_product_cost d WHERE a.c_store_id = b.c_store_id AND a.m_product_id = c.id AND c.id = d.m_product_id(+) AND b.id = 48546
1g3wj77gqyx3v	select t.info from ad_pinstance_para t where t.name=:name and t.ad_pinstance_id=:pid
1nx86q2n961ct	UPDATE RP_DAILYBALANCE T SET T.QTYEND = NVL(T.QTYBEGIN, 0) + NVL(T.QTYPURCHASE, 0) - NVL(T.QTYRETPUR, 0) - NVL(T.QTYSALE, 0) + NVL(T.QTYRETSALE, 0) - NVL(T.QTYRETAIL, 0) - NVL(T.QTYTRANSFEROUT, 0) + NVL(T.QTYTRANSFERIN, 0) + NVL(T.QTYOTHERINOUT, 0) + NVL(T.QTYINVENTORY, 0), (T.PRICELIST, T.AMTBEGIN, T.AMTPURCHASE, T.AMTRETPUR, T.AMTSALE, T.AMTRETSALE, T.AMTRETAIL, T.AMTTRANSFEROUT, T.AMTTRANSFERIN, T.AMTOTHERINOUT, T.AMTEND, T.AMTINVENTORY) = (SELECT A.PRICELIST, T.QTYBEGIN * A.PRICELIST, T.QTYPURCHASE * A.PRICELIST, T.QTYRETPUR * A.PRICELIST, T.QTYSALE * A.PRICELIST, T.QTYRETSALE * A.PRICELIST, T.QTYRETAIL * A.PRICELIST, T.QTYTRANSFEROUT * A.PRICELIST, T.QTYTRANSFERIN * A.PRICELIST, T.AMTOTHERINOUT * A.PRICELIST, T.QTYEND * A.PRICELIST, T.QTYINVENTORY * A.PRICELIST FROM M_PRODUCT A WHERE A.ID = T.M_PRODUCT_ID) WHERE T.AD_PI_ID = :B1
1rs7agb629yuu	SELECT ISENDACCOUNT FROM C_PERIOD WHERE YEARMONTH = :B3 AND C_CUSTOMER_ID = :B2 AND AD_CLIENT_ID = :B1
1ytn12kygkrzg	INSERT INTO RP_NC_HISSTORAGE01 (ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, AD_PI_ID, C_STORE_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, M_PRODUCTALIAS_ID, PRICELIST, TOTAL, AMT_LIST) WITH PERIOD_AS AS(SELECT /*+materialize*/ B.ID, B.DATEEND FROM (SELECT T.C_CUSTOMER_ID, MAX(T.YEARMONTH) YEARMONTH FROM TABLE(:B1 ) RS, C_STORE S, C_PERIOD T WHERE RS.ID = S.ID AND S.C_CUSTOMER_ID = T.C_CUSTOMER_ID AND T.ISMONTHSUM = 'Y' AND T.ISENDACCOUNT = 'Y' AND T.DATEEND <= :B2 GROUP BY T.C_CUSTOMER_ID) A JOIN C_PERIOD B ON (A.C_CUSTOMER_ID = B.C_CUSTOMER_ID AND A.YEARMONTH = B.YEARMONTH)) SELECT 1, A.AD_CLIENT_ID, A.AD_ORG_ID, 'Y', :B5 , :B5 , SYSDATE, SYSDATE, :B4 , A.C_STORE_ID, A.M_PRODUCT_ID, A.M_ATTRIBUTESETINSTANCE_ID, D.ID, C.PRICELIST, A.QTYEND, C.PRICELIST * A.QTYEND AS AMT_LIST FROM FA_MONTHSTORE A, TABLE(:B1 ) RS, TABLE(:B3 ) RP, M_PRODUCT C, M_PRODUCT_ALIAS D, PERIOD_AS M WHERE RS.ID = A.C_STORE_ID AND A.C_PERIOD_ID = M.ID AND RP.ID = C.ID AND A.M_PRODUCT_ID = RP.ID AND A.M_PRODUCT_ID = D.M_PRODUCT_ID AND A.M_ATTRIBUTESETINSTANCE_ID = D.M_ATTRIBUTESETINSTANCE_ID
20t3hcm37fjx0	Select Seq_C_SYSLOG.NextVal From Dual
27y0q76xnjgx6	MERGE INTO FA_STORAGE G USING (SELECT A.AD_CLIENT_ID, A.AD_ORG_ID, A.C_STORE_ID, A.MODIFIERID, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, SUM(B.QTYOUT) AS QTYOUT, SUM(B.QTYIN) AS QTYIN, C.ID AS M_PRODUCTALIAS_ID FROM M_RET_SALE A, M_RET_SALEITEM B, M_PRODUCT_ALIAS C WHERE A.ID = B.M_RET_SALE_ID AND B.M_PRODUCT_ID = C.M_PRODUCT_ID AND B.M_ATTRIBUTESETINSTANCE_ID = C.M_ATTRIBUTESETINSTANCE_ID AND B.M_RET_SALE_ID = :B1 GROUP BY A.AD_CLIENT_ID, A.AD_ORG_ID, A.C_STORE_ID, A.MODIFIERID, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, C.ID) W ON (G.C_STORE_ID = W.C_STORE_ID AND G.M_PRODUCT_ID = W.M_PRODUCT_ID AND G.M_ATTRIBUTESETINSTANCE_ID = W.M_ATTRIBUTESETINSTANCE_ID AND G.AD_CLIENT_ID = W.AD_CLIENT_ID) WHEN MATCHED THEN UPDATE SET G.QTYPREIN = G.QTYPREIN - W.QTYOUT, G.MODIFIERID = W.MODIFIERID, G.MODIFIEDDATE = SYSDATE WHEN NOT MATCHED THEN INSERT (G.ID, G.AD_CLIENT_ID, G.AD_ORG_ID, G.ISACTIVE, G.CREATIONDATE, G.MODIFIEDDATE, G.OWNERID, G.MODIFIERID, G.C_STORE_ID, G.M_PRODUCT_ID, G.M_ATTRIBUTESETINSTANCE_ID, G.QTYPREIN, G.QTYVALID, G.M_PRODUCTALIAS_ID) VALUES (GET_SEQUENCES('FA_STORAGE'), W.AD_CLIENT_ID, W.AD_ORG_ID, 'Y', SYSDATE, SYSDATE, W.MODIFIERID, W.MODIFIERID, W.C_STORE_ID, W.M_PRODUCT_ID, W.M_ATTRIBUTESETINSTANCE_ID, -W.QTYOUT, -W.QTYOUT, W.M_PRODUCTALIAS_ID)
292db9rjqkb2v	Select Seq_AD_PINSTANCE_LOG.NextVal From Dual
2g0tnqbx7vjfp	INSERT INTO C_VIP (ID, AD_CLIENT_ID, AD_ORG_ID, CARDNO, PASS_WORD, C_VIPTYPE_ID, C_CUSTOMER_ID, C_STORE_ID, OPENCARDDATE, C_CUSTOMERUP_ID, HR_EMPLOYEE_ID, VIPNAME, MOBIL, IDNO, VIPENAME, SEX, BIRTHDAY, RETAIL_FILTER, VALIDDATE, ISUP, ISOFF, C_INTEGRALAREA_ID, CREDITREMAIN, INTEGRAL, COUNTRY, C_CITY_ID, POST, PHONE, EMAIL, C_VIPATTRIB2_ID, C_VIPATTRIB3_ID, BUILDING, ADDRESS, DESCRIPTION, DESCRIPTION2, IMAGEURL, OPENCARD_STATUS, ENTERDATE, IS_FIRST_INTER, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44)
2jbwqxkm41rur	UPDATE M_TRANSFERAPPLYITEM p SET (M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID)=(SELECT n.M_PRODUCT_ID, n.M_ATTRIBUTESETINSTANCE_ID FROM M_PDT_ALIAS_CON c, M_PRODUCT_ALIAS n, M_PRODUCT_ALIAS o WHERE n.ID=c.M_PDA_NEW_ID AND o.ID=c.M_PDA_OLD_ID and o.M_PRODUCT_ID=p.M_PRODUCT_ID and o.M_ATTRIBUTESETINSTANCE_ID=p.M_ATTRIBUTESETINSTANCE_ID) WHERE p.ID=:1 AND EXISTS(SELECT 1 FROM M_PDT_ALIAS_CON c, M_PRODUCT_ALIAS o WHERE o.ID=c.M_PDA_OLD_ID and o.M_PRODUCT_ID=p.M_PRODUCT_ID and o.M_ATTRIBUTESETINSTANCE_ID=p.M_ATTRIBUTESETINSTANCE_ID)
36yp6saywn3b0	INSERT INTO V_FA_STORAGE_TS (AD_PI_ID, ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE, C_STORE_ID, M_PRODUCT_ID, M_PRODUCTALIAS_ID, M_ATTRIBUTESETINSTANCE_ID, QTY, QTYPREOUT, QTYPREIN, QTY_FREEZE, QTYVALID, QTYCAN, PRICELIST) WITH GS AS (SELECT A.C_STORE_ID, A.M_PRODUCT_ID FROM V_FA_STORAGE_CAN A, TABLE(:B1 ) RS, TABLE(:B2 ) RP WHERE A.C_STORE_ID = RS.ID AND A.M_PRODUCT_ID = RP.ID AND (A.QTY <> 0 OR A.QTYPREOUT <> 0 OR A.QTYPREIN <> 0) GROUP BY A.C_STORE_ID, A.M_PRODUCT_ID) SELECT :B4 , 1, 37, 27, 893, 893, SYSDATE, SYSDATE, 'Y', T.C_STORE_ID, T.M_PRODUCT_ID, T.M_PRODUCTALIAS_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.QTY, T.QTYPREOUT, T.QTYPREIN, T.QTY_FREEZE, T.QTYVALID, T.QTYCAN, TT.PRICELIST FROM V_FA_STORAGE_CAN T, (SELECT GS.C_STORE_ID, T.C_CUSTOMER_ID, GS.M_PRODUCT_ID, CASE WHEN T.C_CUSTOMER_ID = 176 THEN A.SALEPRICE WHEN B.IS_PRICETYPE = 'N' THEN NVL((SELECT MAX(L.PRICE) FROM B_SALEPRICE L, C_CUSTOMER B WHERE L.C_CUSTOMER_ID = B.ID AND B.C_CUSTOMERUP_ID = T.C_CUSTOMER_ID AND L.M_PRODUCT_ID = A.ID), 0) WHEN B.IS_PRICETYPE = 'Y' THEN NVL((SELECT MAX(PRICE) FROM B_PRICE_SALE L WHERE L.C_SALEAREA_ID = :B3 AND M_PRODUCT_ID = A.ID), 0) END AS PRICELIST FROM C_STORE T, M_PRODUCT A, C_CUSTOMER B, GS WHERE T.ID = GS.C_STORE_ID AND T.C_CUSTOMER_ID = B.ID AND A.ID = GS.M_PRODUCT_ID) TT WHERE T.C_STORE_ID = TT.C_STORE_ID AND T.M_PRODUCT_ID = TT.M_PRODUCT_ID AND (T.QTY <> 0 OR T.QTYPREOUT <> 0 OR T.QTYPREIN <> 0)
3h7p2stnhara6	merge into ad_cxtab_stat g using(select :1 ad_cxtab_id, id user_ID, ad_client_id, ad_org_id from users where id=:2 )b on(b.ad_cxtab_id=g.ad_cxtab_id and b.user_id=g.user_id) when matched then update set g.cnt=g.cnt+1, g.tot_time=g.tot_time+:3, g.tot_rows=g.tot_rows+:4, g.max_time=GREATEST(g.max_time, :5), g.max_rows=GREATEST(g.max_rows, :6), last_duration=:7, last_rows=:8, last_time=sysdate when not matched then insert (g.id, g.ad_client_id, g.ad_org_id, g.user_id, g.ad_cxtab_id, cnt, tot_time, tot_rows, max_time, max_rows, last_duration, last_rows, last_time) values( get_sequences('ad_cxtab_stat'), b.ad_client_id, b.ad_org_id, b.user_ID, b.ad_cxtab_id, 1, :9, :10, :11, :12, :13, :14, sysdate)
3p1c799xurvxy	insert into ad_pinstance_para(id, ad_client_id, ad_org_id, ad_pinstance_id, name, P_STRING, P_NUMBER, P_DATE, info, isactive, orderno) values (get_sequences('ad_pinstance_para'), :1, :2, :3, :4, :5, :6, :7, :8, 'Y', :9)
3p92bm1djg1wh	MERGE INTO RP_DAILYBALANCE G USING (SELECT T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCTALIAS_ID, SUM(DECODE(T.BILLTYPE, 'M_SALEIN', T.QTY, 0)) AS QTYPURCHASE, SUM(DECODE(T.BILLTYPE, 'M_RET_SALEOUT', T.QTY, 0)) AS QTYRETPUR, SUM(DECODE(T.BILLTYPE, 'M_SALEOUT', T.QTY, 0)) AS QTYSALE, SUM(DECODE(T.BILLTYPE, 'M_RET_SALEIN', T.QTY, 0)) AS QTYRETSALE, SUM(DECODE(T.BILLTYPE, 'M_RETAIL', T.QTY, 0)) AS QTYRETAIL, SUM(DECODE(T.BILLTYPE, 'M_TRANSFEROUT', T.QTY, 0)) AS QTYTRANSFEROUT, SUM(DECODE(T.BILLTYPE, 'M_TRANSFERIN', T.QTY, 0)) AS QTYTRANSFERIN, SUM(DECODE(T.BILLTYPE, 'M_OTHER_INOUT', T.QTY, 0)) AS QTYOTHERINOUT, SUM(DECODE(T.BILLTYPE, 'M_INVENTORY', T.QTY, 0)) AS QTYINVENTORY FROM FA_STORAGE_FTP T, TABLE(:B1 ) RS, TABLE(:B2 ) RP WHERE T.C_STORE_ID = RS.ID AND T.M_PRODUCT_ID = RP.ID AND T.CHANGEDATE BETWEEN :B4 AND :B3 GROUP BY T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCTALIAS_ID) W ON (G.C_STORE_ID = W.C_STORE_ID AND G.M_PRODUCT_ID = W.M_PRODUCT_ID AND G.M_ATTRIBUTESETINSTANCE_ID = W.M_ATTRIBUTESETINSTANCE_ID AND G.AD_PI_ID = :B5 ) WHEN MATCHED THEN UPDATE SET G.QTYPURCHASE = W.QTYPURCHASE, G.QTYRETPUR = W.QTYRETPUR, G.QTYSALE = W.QTYSALE, G.QTYRETSALE = W.QTYRETSALE, G.QTYRETAIL = W.QTYRETAIL, G.QTYTRANSFEROUT = W.QTYTRANSFEROUT, G.QTYTRANSFERIN = W.QTYTRANSFERIN, G.QTYOTHERINOUT = W.QTYOTHERINOUT, G.QTYINVENTORY = W.QTYINVENTORY WHEN NOT MATCHED THEN INSERT (G.ID, G.AD_CLIENT_ID, G.AD_ORG_ID, G.ISACTIVE, G.CREATIONDATE, G.MODIFIEDDATE, G.OWNERID, G.MODIFIERID, G.AD_PI_ID, G.C_STORE_ID, G.M_PRODUCT_ID, G.M_ATTRIBUTESETINSTANCE_ID, G.M_PRODUCT_ALIAS_ID, G.QTYPURCHASE, G.QTYRETPUR, G.QTYSALE, G.QTYRETSALE, G.QTYRETAIL, G.QTYTRANSFEROUT, G.QTYTRANSFERIN, G.QTYOTHERINOUT, G.QTYINVENTORY) VALUES (1, 37, 27, 'Y', SYSDATE, SYSDATE, 893, 893, :B5 , W.C_STORE_ID, W.M_PRODUCT_ID, W.M_ATTRIBUTESETINSTANCE_ID, W.M_PRODUCTALIAS_ID, W.QTYPURCHASE, W.QTYRETPUR, W.QTYSALE, W.QTYRETSALE, W.QTYRETAIL, W.QTYTRANSFEROUT, W.QTYTRANSFERIN, W.QTYOTHERINOUT, W.QTYINVENTORY)
3v1j6nq4j85jn	Select Seq_M_RETAIL.NextVal From Dual
3xn0hnacfjj9r	SELECT T.QTY, NVL(T.QTYOUT, 0) + ROUND(:B1 ) FROM M_SALEITEM T WHERE T.M_PRODUCT_ID = :B4 AND T.M_ATTRIBUTESETINSTANCE_ID = :B3 AND T.M_SALE_ID = :B2 AND ROWNUM = 1
3yxwbkxup9k1f	SELECT ADDRESS FROM C_STORE A WHERE ID = :B1
41gukfhh02hub	UPDATE RP_STOREDAILY R SET R.QTY_BEGIN = (SELECT NVL(RP.QTY_END, 0) FROM RP_STOREDAILY RP WHERE RP.CHANGEDATE = :B5 AND RP.C_STORE_ID = :B3 AND RP.M_PRODUCT_ID = :B2 AND RP.M_ATTRIBUTESETINSTANCE_ID = :B1 ) WHERE R.CHANGEDATE = :B4 AND R.C_STORE_ID = :B3 AND R.M_PRODUCT_ID = :B2 AND R.M_ATTRIBUTESETINSTANCE_ID = :B1
423sjdtkhf2b0	BEGIN M_RETAIL_PRICEQUERYITEM_ACM (:1, :2, :3); END;
4dnpwxq4t9a6k	INSERT INTO RP_SALE2 (ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATIONDATE, OWNERID, MODIFIEDDATE, MODIFIERID, SALEBILLTYPE, BILLDATE, DOCNO, DOCTYPE, B_SO_ID, C_CUSTOMERUP_ID, C_STORE_ID, C_CUSTOMER_ID, C_DEST_ID, DESCRIPTION, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, AMTSO, QTYORDERED, QTYSALE, QTYSALEOUT, PRICELIST, AMTLISTSALE, AMTLISTSALEOUT, PRICEACTUAL, AMTSALE, AMTSALEOUT, DISCOUNTSALE, OUTERID, INERID, M_PRODUCT_ALIAS_ID, C_SALEDISTYPE_ID, AD_PI_ID, QTYSALEIN, PRECOST, TOT_AMT_PRECOST, TOT_INAMT_PRECOST, IS_DIR, PICKERID, MXREMARK) SELECT 1, B.AD_CLIENT_ID, B.AD_ORG_ID, B.ISACTIVE, B.CREATIONDATE, B.OWNERID, B.MODIFIEDDATE, B.MODIFIERID, A.SALEBILLTYPE, A.DATEOUT AS DATEOUT, A.DOCNO, A.DOCTYPE, A.B_SO_ID, A.C_CUSTOMERUP_ID, A.C_STORE_ID, A.C_CUSTOMER_ID, A.C_DEST_ID, A.DESCRIPTION, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, C.TOT_AMT_ACTUAL AS AMTSO, C.QTY AS QTYORDERED, B.QTY AS QTYSALE, B.QTYOUT AS QTYSALEOUT, B.PRICELIST, B.TOT_AMT_LIST AS AMTLISTSALE, B.TOT_AMTOUT_LIST AS AMTLISTSALEOUT, B.PRICEACTUAL, B.TOT_AMT_ACTUAL AS AMTSALE, B.TOT_AMTOUT_ACTUAL AS AMTSALEOUT, B.DISCOUNT AS DISCOUNTSALE, A.OUTERID, A.INERID, N.ID AS M_PRODUCT_ALIAS_ID, A.C_SALEDISTYPE_ID, :B6 , DECODE(A.IN_STATUS, 2, B.QTYIN, 0), P.PRECOST, P.PRECOST * B.QTYOUT, P.PRECOST * DECODE(A.IN_STATUS, 2, B.QTYIN, 0), A.IS_DIR, A.PICKERID, B.DESCRIPTION FROM M_SALE A INNER JOIN M_SALEITEM B ON A.ID = B.M_SALE_ID INNER JOIN TABLE(:B1 ) RC ON A.C_CUSTOMER_ID = RC.ID INNER JOIN TABLE(:B2 ) RP ON B.M_PRODUCT_ID = RP.ID INNER JOIN TABLE(:B3 ) RS ON A.C_STORE_ID = RS.ID INNER JOIN M_PRODUCT P ON P.ID = B.M_PRODUCT_ID INNER JOIN M_PRODUCT_ALIAS N ON B.M_PRODUCT_ID = N.M_PRODUCT_ID AND B.M_ATTRIBUTESETINSTANCE_ID = N.M_ATTRIBUTESETINSTANCE_ID LEFT JOIN B_SOITEM C ON A.B_SO_ID = C.B_SO_ID AND B.M_PRODUCT_ID = C.M_PRODUCT_ID AND B.M_ATTRIBUTESETINSTANCE_ID = C.M_ATTRIBUTESETINSTANCE_ID WHERE A.OUT_STATUS = 2 AND A.DATEOUT BETWEEN :B5 AND :B4
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
4w58v83fy164c	SELECT COUNT(1) FROM M_RET_SALE A, M_RET_SALEITEM B WHERE A.ID = :B1 AND A.ID = B.M_RET_SALE_ID
56u5zb6uytjpn	SELECT BILLDATE, AD_CLIENT_ID, C_CUSTOMER_ID, IS_DIFFDEAL FROM M_OTHER_INOUT WHERE ID = :B1
5jn7gs6smwswk	SELECT IS_WMSSTORE FROM C_STORE A WHERE A.ID = :B1
63jg9nmyk1bsp	select sqlfilter, filterdesc, t.name, SEC_FKFILTER_IDS from groupperm, directory, ad_table t where (t.id=directory.ad_table_id) and groupid in (select groupid from groupuser where userid=:1 ) and directoryid in (select id from directory where upper(name)=:2) and bitand(permission, :3)+0=:4 and directory.id=directoryid
6cuq4r5t050s9	UPDATE M_INVENTORYITEM T SET T.PERCOST = NVL(PERCOST_ANALYSE(SUBSTR(:B4 , 1, 6), :B3 , :B1 ), 0) WHERE T.M_INVENTORY_ID = :B2 AND T.M_PRODUCT_ID = :B1
6k7mtvf39y151	SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS'
6xtpamjc7725h	BEGIN RP_NC_HISSTORAGE_GENERATE (:1); END;
7cb55avndtzwa	SELECT PRICE FROM (SELECT A.PRICE FROM B_RTPADJ T, B_RTPADJITEM A WHERE T.ID = A.B_RTPADJ_ID AND T.STATUS = 2 AND T.ISACTIVE = 'Y' AND T.BEGINDATE <= :B3 AND T.CLOSEDATE >= :B3 AND A.M_PRODUCT_ID = :B2 AND A.C_PRICEAREA_ID = :B1 ORDER BY PRIORITY DESC, T.STATUSTIME DESC) K WHERE ROWNUM = 1
7h35uxf5uhmm1	select sysdate from dual
7j4acvm9akcd1	INSERT INTO RP_NC_HISSTORAGE01 (ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, AD_PI_ID, C_STORE_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, M_PRODUCTALIAS_ID, PRICELIST, TOTAL, AMT_LIST) SELECT 1, T.AD_CLIENT_ID, T.AD_ORG_ID, 'Y', :B5 , :B5 , SYSDATE, SYSDATE, :B4 , T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, C.ID AS M_PRODUCTALIAS_ID, A.PRICELIST, T.TOTAL, A.PRICELIST * T.TOTAL AS AMT_LIST FROM (SELECT SUM(T.QTYCHANGE) AS TOTAL, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.C_STORE_ID, T.AD_CLIENT_ID, T.AD_ORG_ID FROM TABLE(:B1 ) RS, FA_STORAGE_FTP T, TABLE(:B2 ) RP WHERE RS.ID = T.C_STORE_ID AND T.M_PRODUCT_ID = RP.ID AND T.CHANGEDATE <= :B3 GROUP BY T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.C_STORE_ID, T.AD_CLIENT_ID, T.AD_ORG_ID UNION ALL SELECT SUM(B.QTYBEGIN) AS TOTAL, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, A.C_STORE_ID, A.AD_CLIENT_ID, A.AD_ORG_ID FROM TABLE(:B1 ) RS, C_BEGIINNING A, C_BEGIINNINGITEM B, TABLE(:B2 ) RP WHERE RS.ID = A.C_STORE_ID AND A.ID = B.C_BEGIINNING_ID AND B.M_PRODUCT_ID = RP.ID GROUP BY B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, A.C_STORE_ID, A.AD_CLIENT_ID, A.AD_ORG_ID) T, M_PRODUCT A, M_PRODUCT_ALIAS C WHERE T.M_PRODUCT_ID = A.ID AND T.M_PRODUCT_ID = C.M_PRODUCT_ID AND T.M_ATTRIBUTESETINSTANCE_ID = C.M_ATTRIBUTESETINSTANCE_ID
7mku2nv3jwumy	/* OracleOEM */ SELECT TO_CHAR(CAST(md.end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, md.user_wait_time_pct, md.db_time_ps db_time_users, md.cpu_time_ps db_cpu_users, NVL(md.host_cpu_usage_pct, 0)/inst.instCount host_cpu_usage_pct, wcd.users userio_users, md.load_average load_average, p.cpu max_cpu_cnt FROM (SELECT DISTINCT wait_class_id FROM v$event_name, v$parameter p WHERE wait_class = 'User I/O' AND p.name='timed_statistics' and p.value='TRUE') wcn, (SELECT wait_class_id, min(end_time) end_time, sum(time_waited / intsize_csec) users FROM gv$waitclassmetric WHERE intsize_csec > 5900 GROUP BY wait_class_id ) wcd, (SELECT sum(value) cpu FROM gv$parameter WHERE name='cpu_count' AND inst_id in (SELECT min(inst_id) FROM gv$instance GROUP BY host_name)) p, (SELECT count(*) instCount from gv$instance) inst, (SELECT min(end_time) end_time, SUM(CASE WHEN metric_name = 'Database Wait Time Ratio' THEN value ELSE 0 END) user_wait_time_pct, SUM(CASE WHEN metric_name = 'Database Time Per Sec' THEN value / 100 ELSE 0 END) db_time_ps, SUM(CASE WHEN metric_name = 'CPU Usage Per Sec' THEN value / 100 ELSE 0 END) cpu_time_ps, SUM(CASE WHEN metric_name = 'Host CPU Utilization (%)' THEN value ELSE 0 END) host_cpu_usage_pct, SUM(CASE WHEN metric_name = 'Current OS Load' THEN value else 0 END) load_average FROM gv$sysmetric WHERE metric_name IN ('Database Wait Time Ratio', 'Database Time Per Sec', 'CPU Usage Per Sec', 'Host CPU Utilization (%)', 'Current OS Load') AND group_id = 2 AND intsize_csec > 5900 ) md, v$parameter pp WHERE wcn.wait_class_id = wcd.wait_class_id AND pp.name='statistics_level' and pp.value!='BASIC'
7muxk6uj3w0w5	INSERT INTO B_RECEIVABLE_ITEM (ID, AD_CLIENT_ID, AD_ORG_ID, B_RECEIVABLE_ID, M_PRODUCTALIAS_ID, M_PRODUCT_ID, QTY, AMTCHECK, DESCRIPTION, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE) SELECT GET_SEQUENCES('b_receivable_item'), AD_CLIENT_ID, AD_ORG_ID, :B2 , M_PRODUCTALIAS_ID, M_PRODUCT_ID, QTYOUT, TOT_AMTOUT_ACTUAL, NULL, M_ATTRIBUTESETINSTANCE_ID, OWNERID, MODIFIERID, SYSDATE, SYSDATE, ISACTIVE FROM M_SALEITEM WHERE M_SALE_ID = :B1
7tgudb89vpxwq	BEGIN RP_STOREDAILY_GENERATE (:1); END;
7xp8dswf9mspx	SELECT t.info FROM ad_pinstance_para t WHERE t.name = :name AND t.ad_pinstance_id = :p_pi_id
8fmjrq8ywckgg	select p.id, asi.id, p.name, p.value, asi.value1_code, asi.value2_code from m_product_alias a, m_product p, m_attributesetinstance asi where (a.no=:1 or a.INTSCODE=:2 or a.FORCODE=:3) and p.id=a.m_product_id and asi.id=a.m_attributesetinstance_id
8jgh7ppxhmn3x	INSERT INTO RP_RETAIL001 (ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, ISACTIVE, BILLDATE, DOCNO, RETAILBILLTYPE, CREATER, C_STORE_ID, DESCRIPTION, C_VIP_ID, SALESREP_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, QTY, PRICELIST, TOT_AMT_LIST, PRICEACTUAL, TOT_AMT_ACTUAL, DISCOUNT, TYPE, REFNO, PERCOST, M_PRODUCT_ALIAS_ID, MARKDISAMT, ITEM_DESCRIPTION, C_MARKBALTYPE_ID, STATUSTIME, AD_PI_ID, C_RETAILTYPE_ID, KKK) SELECT B.ID, B.AD_CLIENT_ID, B.AD_ORG_ID, B.OWNERID, B.ISACTIVE, A.BILLDATE, A.DOCNO, A.RETAILBILLTYPE, A.OWNERID AS CREATER, A.C_STORE_ID, A.DESCRIPTION, A.C_VIP_ID, B.SALESREP_ID, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, B.QTY, B.PRICELIST, B.TOT_AMT_LIST, B.PRICEACTUAL, B.TOT_AMT_ACTUAL, B.DISCOUNT, B.TYPE, A.REFNO, (SELECT K.PERCOST FROM FA_PRODUCT_CUSCOST K WHERE K.C_CUSTOMER_ID = A.C_CUSTOMER_ID AND K.M_PRODUCT_ID = B.M_PRODUCT_ID) AS PERCOST, B.M_PRODUCTALIAS_ID AS M_PRODUCT_ALIAS_ID, B.TOT_AMT_ACTUAL * 0, B.DESCRIPTION ITEM_DESCRIPTION, B.C_MARKBALTYPE_ID, A.STATUSTIME, :B5 , A.C_RETAILTYPE_ID, (SELECT DECODE(K.DISTYPE, 1, '´òÕÛ', 2, 'ÌؼÛ', 3, 'ÓÅ»Ý', K.DISTYPE) || K.CONTENT FROM B_RETAILDISSKUITEM K WHERE K.B_RETAILDISSKU_ID = B.B_RETAILDISSKU_ID AND K.M_PRODUCT_ID = B.M_PRODUCT_ID) FROM M_RETAIL A, M_RETAILITEM B, TABLE(:B1 ) RS, TABLE(:B2 ) RP WHERE A.ID = B.M_RETAIL_ID AND A.C_STORE_ID = RS.ID AND B.M_PRODUCT_ID = RP.ID AND A.STATUS = 2 AND A.BILLDATE BETWEEN :B4 AND :B3
8sk9r1c93tys5	BEGIN RP_DAILYBALANCE_GENERATE (:1); END;
8skcwxuxvdyy5	UPDATE M_RET_SALE T SET T.DATEIN = :B13 , T.DESCRIPTION = :B12 , T.DIFFREASON = :B11 , T.MODIFIERID = :B10 , T.OPERATOERID = :B9 , T.OPER_STATUS = :B8 , T.C_CARGOINTYPE_ID = :B7 , T.C_STORE_LOCATION_ID = :B6 , T.PRINTINTIMES = :B5 , T.REALDATE = :B4 , T.IN_STATUS = :B3 , T.IN_AU_STATE = :B2 , T.IN_AU_PI_ID = :B1 WHERE T.ID = :B14
8tuwrath3mmh4	select GetUserPermission(:1, :2) from dual
907mnbrsun42k	DELETE M_SALEITEM WHERE ID = :B1
9grkvk12rcmh2	SELECT C_CUSTOMER_ID FROM C_STORE WHERE ID = :B1
a9a39dk8k023q	SELECT COUNT(*) FROM B_SOITEM WHERE B_SO_ID = :B4 AND M_PRODUCT_ID = :B3 AND M_ATTRIBUTESETINSTANCE_ID = :B2 AND ID <> :B1
agdj8m2uswqp9	UPDATE M_INVENTORYITEM A SET PRICELIST = RTL_PRICE_ANALYSIS(A.M_PRODUCT_ID, (SELECT C_PRICEAREA_ID FROM C_STORE WHERE ID = :B2 )) WHERE NVL(A.PRICELIST, 0) = 0 AND ID = :B1
akcjru0bq94hd	select /*+USE_CONCAT*/id, name, value, m_attributeset_id, pricelist from m_product where (name=:1 or VALUE=:2) and isactive='Y' and ad_client_id=:3
aw7tt3mypdjtu	SELECT C.NAME, D.VALUE1, D.VALUE2, B.QTY, B.QTYCAN FROM M_SALEITEM B, M_PRODUCT C, M_ATTRIBUTESETINSTANCE D WHERE B.M_PRODUCT_ID = C.ID AND B.M_ATTRIBUTESETINSTANCE_ID = D.ID AND NVL(B.QTYCAN, 0) < B.QTY AND B.QTY > 0 AND B.M_SALE_ID = :B1
b0pt9p1vh5f17	SELECT COUNT(*) FROM M_IN_QCITEM WHERE M_IN_ID = (SELECT ID FROM M_IN WHERE DOCNO = (SELECT DOCNO FROM M_TRANSFER WHERE ID = :B1 ))
b2gnxm5z6r51n	lock table sys.col_usage$ in exclusive mode nowait
b8at7v1hg5zbh	INSERT INTO FA_STORAGE_FTP (ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE, BILLTYPE, BILLDATE, CHANGEDATE, DOCNO, DOCTYPE, C_STORE_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, PERCOST, QTY, STATE, QTYCHANGE, QTYAMOUNT, COSTAMOUNT, M_PRODUCTALIAS_ID) SELECT GET_SEQUENCES('FA_STORAGE_FTP'), A.AD_CLIENT_ID, A.AD_ORG_ID, A.OWNERID, A.MODIFIERID, SYSDATE, SYSDATE, 'Y', 'M_RETAIL', A.BILLDATE, A.DATEOUT, A.DOCNO, A.DOCTYPE, A.C_STORE_ID, B.M_PRODUCT_ID, B.M_ATTRIBUTESETINSTANCE_ID, 0, B.QTY, -1, -B.QTY, B.TOT_AMT_ACTUAL, 0, C.ID FROM M_RETAIL A, M_RETAILITEM B, M_PRODUCT_ALIAS C WHERE A.ID = B.M_RETAIL_ID AND B.M_PRODUCT_ID = C.M_PRODUCT_ID AND B.M_ATTRIBUTESETINSTANCE_ID = C.M_ATTRIBUTESETINSTANCE_ID AND A.ID = :B1
bhtpdj0kvdnsu	INSERT INTO M_INVENTORYITEM (ID, AD_CLIENT_ID, AD_ORG_ID, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, ISACTIVE, M_INVENTORY_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, QTYBOOK, QTYCOUNT, QTYDIFF, PRICELIST, TOT_AMTBOOK_LIST, TOT_AMTCOUNT_LIST, TOT_AMTDIFF_LIST, TOT_AMTDIFF_PRECOST, M_PRODUCTALIAS_ID) SELECT GET_SEQUENCES('M_INVENTORYITEM'), C.AD_CLIENT_ID, C.AD_ORG_ID, :B4 , :B4 , SYSDATE, SYSDATE, 'Y', :B3 , A.M_PRODUCT_ID, A.M_ATTRIBUTESETINSTANCE_ID, A.QTYEND, 0, 0 - A.QTYEND, C.PRICELIST, C.PRICELIST * A.QTYEND, 0, C.PRICELIST * (0 - A.QTYEND), NVL(E.PERCOST, C.PRECOST) * (0 - A.QTYEND), D.ID FROM FA_MONTHSTORE A, C_PERIOD B, M_PRODUCT C, M_PRODUCT_ALIAS D, FA_PRODUCT_COST E WHERE A.C_PERIOD_ID = B.ID AND B.ID = :B2 AND A.C_STORE_ID = :B1 AND A.M_PRODUCT_ID = C.ID AND C.ID = D.M_PRODUCT_ID AND C.ID = E.M_PRODUCT_ID(+) AND A.M_ATTRIBUTESETINSTANCE_ID = D.M_ATTRIBUTESETINSTANCE_ID
bsa0wjtftg3uw	select file# from file$ where ts#=:1
bwaammgh93frr	SELECT * FROM M_SALE T WHERE T.ID = :B1
c0hr107m2r152	SELECT VALUE FROM AD_PARAM WHERE NAME = :B2 AND AD_CLIENT_ID = :B1
cfkqjy49j4vbn	SELECT COUNT(1) FROM M_INVENTORYITEM WHERE M_INVENTORY_ID = :B4 AND M_PRODUCT_ID = :B3 AND M_ATTRIBUTESETINSTANCE_ID = :B2 AND ID <> :B1
cwk28f4zwa22m	MERGE INTO RP_NC_HISSTORAGE01 G USING (SELECT H.AD_CLIENT_ID, H.AD_ORG_ID, H.M_PRODUCT_ID, H.C_STORE_ID, H.M_ATTRIBUTESETINSTANCE_ID, H.SUMQTY, K.PRICELIST, PA.ID AS M_PRODUCTALIAS_ID FROM (SELECT ST.AD_CLIENT_ID, ST.AD_ORG_ID, ST.C_STORE_ID, ST.M_PRODUCT_ID, ST.M_ATTRIBUTESETINSTANCE_ID, SUM(ST.QTYCHANGE) AS SUMQTY FROM TABLE(:B1 ) RS, FA_STORAGE_FTP ST, TABLE(:B3 ) RP, C_STORE C, (SELECT B.ID, B.C_CUSTOMER_ID, B.DATEEND FROM (SELECT T.C_CUSTOMER_ID, MAX(T.YEARMONTH) YEARMONTH FROM TABLE(:B1 ) RS, C_STORE S, C_PERIOD T WHERE RS.ID = S.ID AND S.C_CUSTOMER_ID = T.C_CUSTOMER_ID AND T.ISMONTHSUM = 'Y' AND T.ISENDACCOUNT = 'Y' AND T.DATEEND <= :B2 GROUP BY T.C_CUSTOMER_ID) A JOIN C_PERIOD B ON (A.C_CUSTOMER_ID = B.C_CUSTOMER_ID AND A.YEARMONTH = B.YEARMONTH)) M WHERE RS.ID = ST.C_STORE_ID AND ST.M_PRODUCT_ID = RP.ID AND ST.CHANGEDATE <= :B2 AND RS.ID = C.ID AND C.C_CUSTOMER_ID = M.C_CUSTOMER_ID AND ST.CHANGEDATE > M.DATEEND GROUP BY ST.AD_CLIENT_ID, ST.AD_ORG_ID, ST.C_STORE_ID, ST.M_PRODUCT_ID, ST.M_ATTRIBUTESETINSTANCE_ID) H, M_PRODUCT K, M_PRODUCT_ALIAS PA WHERE H.M_PRODUCT_ID = K.ID AND H.SUMQTY <> 0 AND H.M_PRODUCT_ID = PA.M_PRODUCT_ID AND H.M_ATTRIBUTESETINSTANCE_ID = PA.M_ATTRIBUTESETINSTANCE_ID) W ON (G.C_STORE_ID = W.C_STORE_ID AND G.M_PRODUCT_ID = W.M_PRODUCT_ID AND G.M_ATTRIBUTESETINSTANCE_ID = W.M_ATTRIBUTESETINSTANCE_ID AND G.AD_PI_ID = :B4 ) WHEN MATCHED THEN UPDATE SET G.TOTAL = NVL(G.TOTAL, 0) + W.SUMQTY, G.AMT_LIST = G.PRICELIST * (NVL(G.TOTAL, 0) + W.SUMQTY) WHEN NOT MATCHED THEN INSERT (ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, OWNERID, MODIFIERID, CREATIONDATE, MODIFIEDDATE, AD_PI_ID, C_STORE_ID, M_PRODUCT_ID, M_ATTRIBUTESETINSTANCE_ID, M_PRODUCTALIAS_ID, PRICELIST, TOTAL, AMT_LIST) VALUES (1, W.AD_CLIENT_ID, W.AD_ORG_ID, 'Y', :B5 , :B5 , SYSDATE, SYSDATE, :B4 , W.C_STORE_ID, W.M_PRODUCT_ID, W.M_ATTRIBUTESETINSTANCE_ID, W.M_PRODUCTALIAS_ID, W.PRICELIST, W.SUMQTY, W.PRICELIST * W.SUMQTY )
d2brk2vvpjt7w	SELECT /*+USE_CONCAT*/ID FROM M_PRODUCT M_PRODUCT WHERE ISACTIVE='Y' AND (NAME = :1 OR VALUE=:2) and AD_CLIENT_ID=37
djykd5049rxpd	BEGIN :1 := M_RETAIL_$r_insert_save (:2, :3); END;
dy2su7904dw1x	SELECT COUNT(1) FROM M_OUT T WHERE T.ID = :B1 AND (T.DATEOUT < T.BILLDATE OR T.DATEOUT > TO_CHAR(SYSDATE, 'yyyyMMdd'))
faw0bn06tbxgt	UPDATE M_IN SET DATEIN=:1, OPER_STATUS=:2, DESCRIPTION=:3, DIFFREASON=:4, ISACTIVE=:5, MODIFIERID=:6, MODIFIEDDATE=:7 WHERE ID=:8 AND STATUS<>2
fny596txpwm55	Select Seq_WEBPOS_LOG.NextVal From Dual
fqd9c2r9g5gxv	MERGE INTO M_INVENTORYITEM G USING (SELECT H.AD_CLIENT_ID, H.AD_ORG_ID, H.M_PRODUCT_ID, H.M_ATTRIBUTESETINSTANCE_ID, H.SUMQTY, K.PRICELIST, NVL(J.PERCOST, K.PRECOST) PRECOST FROM (SELECT T.AD_CLIENT_ID, T.AD_ORG_ID, T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, SUM(T.QTYCHANGE) AS SUMQTY FROM FA_STORAGE_FTP T WHERE T.C_STORE_ID = :B3 AND T.CHANGEDATE > :B2 AND T.CHANGEDATE <= :B1 GROUP BY T.AD_CLIENT_ID, T.AD_ORG_ID, T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID) H, M_PRODUCT K, FA_PRODUCT_COST J WHERE H.M_PRODUCT_ID = K.ID AND K.ID = J.M_PRODUCT_ID(+) AND H.SUMQTY <> 0) W ON (G.M_INVENTORY_ID = :B4 AND G.M_PRODUCT_ID = W.M_PRODUCT_ID AND G.M_ATTRIBUTESETINSTANCE_ID = W.M_ATTRIBUTESETINSTANCE_ID AND G.AD_CLIENT_ID = W.AD_CLIENT_ID) WHEN MATCHED THEN UPDATE SET G.QTYBOOK = G.QTYBOOK + W.SUMQTY, G.QTYDIFF = 0 - (G.QTYBOOK + W.SUMQTY), G.TOT_AMTBOOK_LIST = G.PRICELIST * (G.QTYBOOK + W.SUMQTY), G.TOT_AMTDIFF_LIST = G.PRICELIST * (0 - (G.QTYBOOK + W.SUMQTY)), G.TOT_AMTDIFF_PRECOST = W.PRECOST * (0 - (G.QTYBOOK + W.SUMQTY)) WHEN NOT MATCHED THEN INSERT (G.ID, G.AD_CLIENT_ID, G.AD_ORG_ID, G.ISACTIVE, G.CREATIONDATE, G.MODIFIEDDATE, G.OWNERID, G.MODIFIERID, G.M_INVENTORY_ID, G.M_PRODUCT_ID, G.M_ATTRIBUTESETINSTANCE_ID, G.QTYBOOK, G.QTYCOUNT, G.QTYDIFF, G.PRICELIST, G.TOT_AMTBOOK_LIST, G.TOT_AMTCOUNT_LIST, G.TOT_AMTDIFF_LIST, G.TOT_AMTDIFF_PRECOST) VALUES (GET_SEQUENCES('M_INVENTORYITEM'), W.AD_CLIENT_ID, W.AD_ORG_ID, 'Y', SYSDATE, SYSDATE, :B5 , :B5 , :B4 , W.M_PRODUCT_ID, W.M_ATTRIBUTESETINSTANCE_ID, W.SUMQTY, 0, 0 - W.SUMQTY, W.PRICELIST, W.PRICELIST * W.SUMQTY, 0, W.PRICELIST * (0 - W.SUMQTY), W.PRECOST * (0 - W.SUMQTY))
fz5ccx6rr10cf	SELECT MAX(ID) FROM C_PERIOD WHERE YEARMONTH = :B3 AND C_CUSTOMER_ID = :B2 AND AD_CLIENT_ID = :B1 AND ROWNUM < 2
g7cagsv9utra6	Select Seq_AD_PINSTANCE_PARA.NextVal From Dual
gfk81xzjhn0x8	MERGE INTO RP_DAILYBALANCE G USING (SELECT T.AD_CLIENT_ID, T.AD_ORG_ID, T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCTALIAS_ID, SUM(T.QTYCHANGE) AS SUMQTY FROM TABLE(:B1 ) RS, FA_STORAGE_FTP T, TABLE(:B2 ) RP WHERE RS.ID = T.C_STORE_ID AND T.M_PRODUCT_ID = RP.ID AND T.CHANGEDATE > RS.BILLDATE AND T.CHANGEDATE < :B3 GROUP BY T.AD_CLIENT_ID, T.AD_ORG_ID, T.C_STORE_ID, T.M_PRODUCT_ID, T.M_ATTRIBUTESETINSTANCE_ID, T.M_PRODUCTALIAS_ID HAVING SUM(T.QTYCHANGE) <> 0) W ON (G.C_STORE_ID = W.C_STORE_ID AND G.M_PRODUCT_ID = W.M_PRODUCT_ID AND G.M_ATTRIBUTESETINSTANCE_ID = W.M_ATTRIBUTESETINSTANCE_ID AND G.AD_PI_ID = :B4 ) WHEN MATCHED THEN UPDATE SET G.QTYBEGIN = G.QTYBEGIN + W.SUMQTY WHEN NOT MATCHED THEN INSERT (G.ID, G.AD_CLIENT_ID, G.AD_ORG_ID, G.ISACTIVE, G.CREATIONDATE, G.MODIFIEDDATE, G.OWNERID, G.MODIFIERID, G.AD_PI_ID, G.C_STORE_ID, G.M_PRODUCT_ID, G.M_ATTRIBUTESETINSTANCE_ID, M_PRODUCT_ALIAS_ID, G.QTYBEGIN) VALUES (1, W.AD_CLIENT_ID, W.AD_ORG_ID, 'Y', SYSDATE, SYSDATE, :B5 , :B5 , :B4 , W.C_STORE_ID, W.M_PRODUCT_ID, W.M_ATTRIBUTESETINSTANCE_ID, W.M_PRODUCTALIAS_ID, W.SUMQTY)
gh2nma4540963	select status from M_IN_BATCH where id=:1
gtf042jhudm94	UPDATE M_TRANSFERAPPLYITEM SET PRICELIST = (SELECT NVL(PRICELIST, 0) PRICELIST FROM M_PRODUCT WHERE AD_CLIENT_ID = :B3 AND ID = :B2 ) WHERE ID = :B1
gv9kwghw8v3s2	SELECT T.NAME FROM M_PRODUCT T WHERE T.ID = :B1

Back to SQL Statistics
Back to Top

Instance Activity Statistics

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