Search: The Web Angelfire
Report Abuse « Previous | Top 100 | Next »
share: del.icio.us | digg | reddit | furl | facebook
ORACLE SQL optimization
Step 1: Find the statements that consume the most resources. You can use the view V$SORT_USAGE to see the session and the SQL associated with a temporary segment which is still in the SQL area.
The statements with the most potential to improve performance if tuned include:
1) The queries that consume the most resources overall.
2) The queries that consume the most resources per row.
3) The queries that are executed most frequently
In the V$SQLAREA you can find the statements which are still in cache and have done a great deal of I/O and buffer gets.
Step 2: Tune those statements to use fewer resources
1) Get the statement to use fewer resources
2) Use the statement less often.
Approaches to tuning SQL statements
1) Restructuring indexes - An index plays an important part in the complete database design. Good index design is just as important as good table design. One of the main reasons for using indexes is so that the database can access the correct data quickly. However too many indexes or have useless indexes can be a burden to the system as they require maintenance when using DML, also the optimizer may choose the wrong index to use if there are too many to choose from, especially in a complex SQL select statement.
a) Remove non selective indexes to speed up DML
b) Index performance critical paths
c) Consider hash clusters, but what out for uniqueness, hashing unique data can produce very large hash indexes.
d) Consider index clusters only if the cluster keys are similar in size.
2) Restructure the statement
a) Consider alternative SQL syntax
e.g. SELECT dname, deptno
FROM dept
WHERE deptno NOT IN (SELCT deptno FROM emp)
A faster version of this statement would be
SELECT dname, deptno
FROM dept
WHERE NOT EXIST
( SELECT deptno FROM emp WHERE dept.deptno = emp.deptno)
b) Compose predicates using AND and =. Using equijoins without exception, statements that perform equijoins on untransformed columns are fastest and easiest to tune.
c) Choose an advantageous join order. Join orders can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work, which can slow down the performance of the statement as well as confusing the optimizer to select the wrong method of optimization.
i) Avoid using a full table scan if it is more efficient to get the data through an index.
ii) Avoid using indexes which select too many rows (more than 2-4% of the table number of rows in the table).
iii) Choose a join order so as to join fewer rows.
For example:
SELECT info
FROM taba a, tabb b, tabc c
WHERE
Acol BETWEEN :alow AND :ahigh
AND bcol BETWEEN :blow AND :bhigh
AND ccol BETWEEN :clow AND :chigh
AND a.key1 = b.key1
AND a.key2 = b.key2
i) Choose a driving table and a driving index. The first three rows of the WHERE clause are filter conditions and the last two are join conditions. Filter conditions dominate the choice of the driving table and the driving index. In general the driving table should be the table containing the filter condition which eliminates the most rows.
ii) Choose the right index. Once you have established the driving table, you must choose the most selective index available to drive the table. Alternative is to use a full table scan if that is more efficient; sometimes if there are no effective indexes it is better to do a full table scan as full scans a sequential rather than random access. From there, the joins should all happen through the joined indexes, the index on primary or foreign keys used to connect the table to a earlier table in the join tree. Rarely should you use the indexes on non-join conditions, except for driving the table. In the above example you should use the indexes on b.key1 and c.key2 to drive into tabb and tabc respectively.
iii) Choose the best join order, driving the best unused filters earliest. The work of following a join can be reduced by first joining to the table with the best still unused filter. Thus if 'bcol BETWEEN ¡K' is more restrictive than 'ccol BETWEEN ¡K.', the last join can be made easier if tabb is joined before tabc.
d) Use untransformed column values. Avoid using functions in the WHERE clause of the statement unless it is really necessary. Useful functions such as to_date and to_char and to_number can slow down execution as well as making the optimizer skip useful indexes on the transformed columns.
e) Avoid using mixed expressions and implicit conversions. E.g. < char_val > = < num_val >. In this case the SQL interpreter will perform an implied conversion of to_number = . This may cause two problems; firstly, the optimizer may ignore a good index on char_val and secondly errors from char to num may occur causing the statement to fail. To avoid this problem use explicit conversions e.g. char_val = to_char(num_val).
f) Write separate SQL statements for specific values. SQL is not a procedural language and using it to do many things in one statement is not a good idea. Optimizations (determining the execution plan) takes place before the database knows of the values used as parameters. An execution plan should therefore not depend in what those values are for.
For example:
SELECT info FROM tables WHERE ....
AND acol BETWEEN DECODE(:loval,'ALL',acol,:loval)
AND DECODE(:hival,'ALL',acol,:hival)
Here the optimizer cannot use the index on acol because the column acol appears on both sides of the BETWEEN expression. In these cases you have to change the structure of the SQL statement.
SELECT info FROM tables WHERE ...
AND acol BETWEEN :loval AND :hival
AND (:loval !='ALL' AND :hival !='ALL')
UNION ALL
SELECT info FROM tables WHERE ...
AND (:loval ='ALL' OR :hival !='ALL')
When you run the explain plan on the new SQL statement you will get the desired result and the undesired result in the same plan. One will use an index and the other will uses a full table scan. In the case of both parameters not equal to 'ALL' you would wish to use an index, and in the other case a full table scan will be faster. Although both the desired and the undesired part exist in the execution plan, one part of the UNION ALL will be eliminated upon execution as :hival and :loval can either be 'ALL' or not 'ALL'. This will eliminate the undesired part upon execution. Note also the use of UNION ALL and not just UNION. The purpose is to eliminate the costly SORT (and deleting duplicates) associated with a UNION and not with a UNION ALL.
g) Use control hints. Although the optimizer does a good job getting the best execution plan, it is sometimes not as aware of the overall picture and require a little hint from the developer. Regardless of why the optimizer choose the wrong execution plan, the use of hints can produce better results if used correctly, especially to force the optimizer to choose a certain path such as a full table scan rather than an index.
h) Use care when using IN and NOT IN in a subquery. Remember that WHERE (NOT) EXISTS can be a useful alternative.
i) Use care when embedding value lists. Examine carefully the contents of embedded value list and try to find an alternative if possible.
For example,
SELECT info FROM table
WHERE transport IN ('BMW','HONDA','FORD')
It may be possible instead to use:
SELECT info FROM table
WHERE transport_type = 'CAR'
j) Minimize the use of DISTINCT or GROUP BY unless necessary as they will always create a sort.
k) Reduce the number of calls to the database. Instead of calling an INSERT, UPADTE or DELETE then a SELECT to get the new record from the database, use INSERT¡KRETURNING, UPDATE¡KRETURNING or DELETE¡KRETURNING to perform both tasks in one call.
l) Avoid using views in joins, unless really necessary. Joins take time and also processing the view also tasks time, so avoid putting them together especially in outer joins.
3) Modify or disable triggers. In most cases using triggers can make life a lot easier for the DBA and developer and help to maintain data integrity. However triggers consume resources, and too many or inappropriate triggers can affect performance.
Approaches to creating indexes
Index plays an important part in database design, the efficiency of the optimizer and the reduction of workload on the main tables (especially if most of the information can be found on the index). However, indexes can also be a burden to the system, requiring extra processing from each DML statement issued to the table with an index. Therefore, good index design is essential.
Oracle provides a variety of index types, each with different properties and each with a different use. The following section is some guidelines to creating useful indexes.
1) When creating an index, the target is that the index would help to query less than 2% - 4% of the tables total data.
2) Building indexes just in case is not a bright idea.
3) Avoid building too many indexes. Indexes processing consumes extra resources per INSERT, UPDATE and DELETE.
4) Do not index columns which are frequently modified. For example, order_id, order_number, customer_id may be good indexes, but order_status, last_modification_date are not good indexes even if they may be used in one or two forms and reports.
5) ALTER INDEX ¡K. REBUILD is faster than dropping and recreating the index.
6) Compact indexes. Using ALTER INDEX¡KCOALESCE can be used to rebuild indexes online.
7) Creating index based upon commonly used functions can be a good aid, rather than burden to system if used properly. For example:
CREATE INDEX idx ON table (a+b*(c-1),a,b) can aid
SELECT info FROM table WHERE a+b*(c-1) BETWEEN 25 AND 50
Or
CREATE INDEX ind ON table(UPPER(empname))
8) Create indexes used in commonly used GROUP and ORDER BY clauses.
9) For large indexes which cover more than 5% of the tables data, but are necessary, consider using a BITMAP INDEX. Bitmap indexes use up less space for indexes with not a lot of distinct values and can be faster for this kind of index. However bitmap indexes cannot be declared unique. Another good use of bitmap indexes is the fact that it can index null values, whilst normal indexes do not.
Approaches to using clusters
Cluster
For tables which are joined regularly such as master detail tables, consider a CLUSTER.
Don't use cluster indexes on non frequent joins or fields which are update frequently.
Don't use cluster on tables which regularly performs full index scans. Clustering actually slows down access on individual records in a table.
Consider cluster on master detail tables where the master table is accessed first then the detail table. Clustering actually places the detail table right after the master table, so access is faster.
Consider a detail cluster alone if you select many details from the same master record.
Don't consider clustering if the data from all the tables in the same cluster key uses more than two blocks.
Hash Clusters
Hash clusters are similar to clusters, except that they perform an hash function to each rows cluster key values.
Consider a hash cluster if the WHERE clause of a SQL statement uses conditions that use the same column or combination of columns regularly.
Consider a hash cluster if you can determine the space required to hold all values in the cluster keys. Note: this means that cluster keys are useful for look tables but not for tables updated or change regularly.
Do not use when space is scarce, hash clusters can consume a lot of space.
As mentioned above do not use on a constantly growing table.
Don't use cluster on tables which regularly performs full index scans. Clustering actually slows down access on individual records in a table.
As mentioned above do not use on a regularly modified table.
Approaches to using hints
In the above section, it is mentioned that hints can aid or force the optimizer to use a certain execution plan. This section will describe in more detail the different types of hints that can be used and how they affect the execution plan outcome. Oracle four key optimization plans; they are as follows:
ALL_ROWS - This is used to get the best total performance for the statement. Best used for reports where all the rows must be obtained.
FIRST_ROWS - This is used to get the best response time. Best used for forms and queries where faster response time is required. This mode cannot be used with DML statements, UNIONs, INTERSECTs, MINUS, GROUP BY, FOR UPDATE, DISTINCT.
RULE - This is based on rule based optimizations where predefined optimization rules are used to determine the execution plan.
CHOOSE - Let the optimizer choose the method from the above three rules.
Hints for access method
Hints for optimization (above) control the overall execution plan, however oracle provides hints for control over individual tables and indexes. These are group as access method hints.
FULL(table) - Use full scan on the table specified.
ROWID(table) - Use scan by rowid on the table specified.
CLUSTER(table) - Use a cluster scan on the table specified.
HASH(table) - Use a hash scan on the table specified.
HASH_AJ - use to force optimizer to change all NOT IN into a hash outer join.
HASH_SJ - use to force the optimizer to EXISTS into a hash semi join.
INDEX( table index) - consider the index specified to be used first.
INDEX_ASC(table index) - specifies ascending range of scan of index explicitly.
INDEX_DESC(table index) - specifies descending range of scan of index explicitly.
INDEX_COMBINE(table index) - Chooses a bitmap access path for the table using the index specified.
INDEX_JOIN(table index) - Using index to join explicitly.
INDEX_FFS(table index) - Use a fast full index scan for the table and index specified.
NO_INDEX(table index) - Tells the optimizer to ignore certain indexes.
MERGE_AJ - Transforms a NOT IN into a merged anti join.
MERGE_SJ - transforms a EXISTS subquery into a merged semi join.
AND_EQUAL(table index index) - merges scans on several single column indexes.
USE_CONCAT - forces OR conditions in the WHERE clause into a compound UNION ALL set.
NO_EXPAND - prevents IN list to be expanded into OR conditions.
REWRITE(view) - Rewrite the statement using the view specified instead.
NO_REWRITE - prevents statement from being rewritten with a view.
Hints on join order
ORDERED - causes the optimizer to join the tables in the query in the order they appear in the FROM clause.
STAR - Forces the star query plan. The star query plan has the largest table last in the join order and joins with a nested loop. Star hint applies when there are at least three tables.
Hints on join operators
USE_NL(table) - force to use nested loops for table
USE_MERGE(table) - force to use SORT MERGE for the table.
USE-HASH(table) - force to use hash join for the table.
DRIVING_SITE(table) - force query to be done at a different site.
Examples
The times we taken from a IBM S8a server with 6 CPUs, running Oracle 8i and Oracle Applications 11i.
The important thing is not the time, but the time difference frfom the original statement.
Use of descending indexes (INDEX_DESC)
Original statement
SELECT * FROM ar_payment_schedules_all apsa
WHERE trx_date > sysdate -7
OEDER BY apsa.trx_date desc
PLAN
SELECT STATEMENT, GOAL = RULE
SORT ORDER BY
TABLE ACCESS BY TABLE ROWID AR_PAYMENT_SCHEDULES_ALL
INDEX RANGE SCAN AR_PAYMENT_SCHEDULES_N1
Execution time : 12.897 secs*
Modified
SELECT --+INDEX_DESC(apsa,ar_payment_schedules_n1)
* FROM ar_payment_schedules_all apsa
WHERE apsa.trx_date > sysdate - 7
ORDER BY apsa.trx_date DESC
PLAN
SELECT STATEMENT, GOAL = RULE
SORT ORDER BY
TABLE ACCESS BY TABLE ROWID AR_PAYMENT_SCHEDULES_ALL
INDEX RANGE SCAN DESCENDING AR_PAYMENT_SCHEDULES_N1
Execution time : 3.311 secs*
Fast Full Index scan
Using the above example, we can modify it so that the optimizer uses a fast full index scan. To do this we only select the fields from the index, so that it does not need to use the table, just the index.
SELECT --+INDEX_DESC(apsa,ar_payment_schedules_n1)
trx_date FROM ar_payment_schedules_all apsa
WHERE apsa.trx_date > sysdate - 7
ORDER BY apsa.trx_date DESC
PLAN
SELECT STATEMENT, GOAL = RULE
INDEX RANGE SCAN DESCENDING AR_PAYMENT_SCHEDULES_N1
Merging indexes (AND_EQUAL)
Sometimes adding filter conditions can result in the using of merged indexes which can greatly increase the speed of a query, even if the condition seems irrelevant.
Original
SELECT * FROM mtl_system_items msi
WHERE inventory_item_id > 50000 AND
organization_id = 1 AND
container_item_flag = 'N'
and segment1 like 'A%'
PLAN
SELECT STATEMENT, GOAL = RULE
TABLE ACCESS BY TABLE ROWID MTL_SYSTEM_ITEMS
INDEX RANGE SCAN CUX_XIAO_MTL_SYSTEM_ITEMS_N1
Result: 17 Rows, 74.957 secs*
Modified
SELECT * FROM mtl_system_items msi
WHERE inventory_item_id > 50000 AND
organization_id = 1 AND
item_type = 'STL' AND
container_item_flag = 'N'
AND segment1 like 'A%'
PLAN
SELECT STATEMENT, GOAL = RULE
TABLE ACCESS BY TABLE ROWID MTL_SYSTEM_ITEMS
AND-EQUAL
INDEX RANGE SCAN CUX_MTL_SYSTEM_ITEMS_N1
INDEX RANGE SCAN CUX_XIAO_MTL_SYSTEM_ITEMS_N1
Result: 17 Rows, 2.784 secs*
Results are dependant on server activity and results may differ with different servers and different workloads.
BAD SQL
Many speed problems are caused by bad SQL statements is not related to how the optimizer sets the execution plan. A badly written SQL will still be bad, even when you optimized them with hints. There main things to check for in debugging speed problems are as follows:
Are all the tables joined? Make sure all the tables are joined, and joined with the best fields to eliminate the most records.
Tables joined in proper order? Generally joined from parent to child.
Are all tables used? Make sure the table is used, but be careful as some tables are used for filtering.
Are there easier or faster ways to join the tables? Find the join path to a table using the least number of join conditions.
What is wrong with this statement?
select sha.org_id,
sha.packing_instructions,
sha.purchase_order_num,
sha.header_id,
so.name||'-'||sha.order_number,
sha.attribute2 picture,
sha.attribute15 invoceremark,
sha.attribute5 memo,
sha.attribute10 dan,
sha.attribute12 hkorder,
decode(sha.warehouse_id,1,'DG',4,'HY',3,'GZ') orga,
sha.warehouse_id warehouse,
decode(sha.attribute3,'I',' ','M',' ','U',' ','DI',' ','DM',' ','DU',' ','D',' ','S',' ','N',' ') la,
decode(sha.attribute4,'I',' ','M',' ','U',' ','DI',' ','DM',' ','DU',' ','D',' ','S',' ','N',' ') lo,
sla.schedule_date,
sla.creation_date s1_date,
sla.schedule_date,
sla.ordered_quantity allquantity,
decode(sla.attribute5,'I',' ','O',' ') water,
rsua.location||'-'||raa.address1,
rt.attribute1 route1,
msi.segment1,
msi.description,
fu.description,
he.full_name
from so_headers_all sha,
so_lines_all sla,
ar_customers ac,
fnd_user fu,
ra_site_uses_all rsua,
ra_addresses_all raa,
ra_site_uses_all rsua1,
ra_addresses_all raa1,
ra_territories rt,
mtl_system_items msi,
so_order_types_all so,
fnd_descr_flex_contexts fdfc,
hr_employees he
where sha.header_id=sla.header_id
and sha.customer_id=ac.customer_id
and sha.org_id=so.org_id
and sha.created_by=fu.user_id
and sla.inventory_item_id=msi.inventory_item_id
and sla.option_flag='N'
and sha.ship_to_site_use_id=rsua1.site_use_id(+)
and sha.invoice_to_site_use_id=rsua.site_use_id(+)
and rsua.address_id=raa.address_id
and rsua1.address_id=raa1.address_id
and rsua1.territory_id=rt.territory_id(+)
and sha.order_type_id=so.order_type_id
and sha.attribute1=he.employee_id(+)
and sla.warehouse_id=msi.organization_id
and fdfc.application_id=300
and fdfc.descriptive_flexfield_name='SO_HEADERS'
and msi.segment1 not like 'G%'
and sha.org_id=:p_org_id
and sha.order_type_id=:p_order_type_id
and sha.order_number between :p_order_number_lo and :p_order_number_hi
and sha.warehouse_id=nvl(:p_warehouse_id,sha.warehouse_id)
and sha.created_by=nvl(:p_created_by,sha.created_by)
and sha.attribute10=nvl(:p_attribute10,sha.attribute10)
order by sha.order_number
Notice
ar_customers ac,
ra_addresses_all raa1,
Both these tables are linked but are not used. The table and the joins associated with these tables can be safely removed without affecting the results.
Also the table fnd_descr_flex_contexts(fdfc) uses filter conditions but in not linked in any way. This is a bug and we should have added
and fdfc.descriptive_flex_context_code='M'
To the SQL statement
CHECK SQL FIRST!
Use of Nested loops
Nested loops out performs all other join methods when there are a number of large tables which are joined to narrow down the number of records. In the following example you will see that the optimizer opted to use HASH and MERGE JOINS, It has been modified to return one row.
Original statement
select oof.organization_code||'HI'|| RCT.TRX_NUMBER TRX_NUMBER11,
ra.address_id, rc.customer_id, RCT.SET_OF_BOOKS_ID SOB_ID, RSU.LOCATION,
SLA1.SELLING_PRICE*(SLA1.ordered_quantity-nvl(sla1.cancelled_quantity,0)) AMOUNT_TEMP,
RCT.INVOICE_CURRENCY_CODE CURRENCY_CODE, RCT.CREATION_DATE, RCT.CUSTOMER_TRX_ID,
RC.CUSTOMER_NAME, RC.CUSTOMER_NUMBER, RCT.WAYBILL_NUMBER, RCT.PURCHASE_ORDER,
RA.ADDRESS1, ra.ADDRESS2, ra.ADDRESS3, ra.ADDRESS4,
RCT.TRX_NUMBER, RCT.TRX_DATE,
decode(rt.name,' ',rt.name, RT.NAME||' ') TERM_NAME,
RCTL.SALES_ORDER, MSI.ATTRIBUTE4 DESCRIPTION_INTERFACE,
rctl.attribute_category, RCTL.ATTRIBUTE1, RCTL.ATTRIBUTE2, RCTL.ATTRIBUTE3,
rctl.attribute4||' ' DESCRIPTION , rctl.attribute4 STL_NUM,
(SLA1.ordered_quantity-nvl(sla1.cancelled_quantity,0))/1000 quantity_invoiced,
sla1.LIST_PRICE*1000 unit_selling_price,
SLA1.SELLING_PRICE*1000 BO_PRICE, RCTL.EXTENDED_AMOUNT,
sum(SPA.PERCENT) PERCENT, RCTL.LINE_NUMBER,
sla.original_system_line_reference so_line_id, SHA.ORDER_NUMBER,
DECODE(LENGTH(RCTL.INTERFACE_LINE_ATTRIBUTE2),4,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,1,4),7,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,4,7),8,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,5,8),RCTL.INTERFACE_LINE_ATTRIBUTE2)||'-'||RCTL.SALES_ORDER||'-'||RCT.ATTRIBUTE10 SHIP_NO,
SPA.HEADER_ID
from
RA_CUSTOMER_TRX_ALL RCT, RA_CUSTOMER_TRX_LINES_ALL RCTL,
RA_CUSTOMERS RC, RA_SITE_USES_ALL RSU, RA_ADDRESSES_ALL RA,
RA_TERMS RT, MTL_SYSTEM_ITEMS MSI, org_organization_definitions oof,
SO_PRICE_ADJUSTMENTS SPA, so_LINEs_all sla,
so_LINEs_all sla1, SO_HEADERS_ALL SHA
where
RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID(+)
AND RCT.bill_TO_CUSTOMER_ID=RC.CUSTOMER_ID(+)
AND RCT.bill_TO_SITE_USE_ID=RSU.SITE_USE_ID(+)
AND RSU.ADDRESS_ID=RA.ADDRESS_ID(+)
AND RCT.TERM_ID=RT.TERM_ID(+)
AND rct.org_id=oof.organization_id(+)
AND RCTL.INTERFACE_LINE_ATTRIBUTE2 LIKE '%051'
AND RCTL.INTERFACE_LINE_ATTRIBUTE6=SPA.LINE_ID(+)
and RCTL.INTERFACE_LINE_ATTRIBUTE6=sla.line_id(+)
AND nvl(SLA.original_system_LINE_reference,sla.line_id)=SLA1.LINE_ID(+)
AND SLA1.HEADER_ID=SHA.HEADER_ID(+)
AND RCTL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND RCT.ORG_ID=MSI.ORGANIZATION_ID
and rctl.line_type = 'LINE'
and rct.trx_number='10003789'
AND RCT.org_ID=5
and RC.CUSTOMER_NUMBER='H188'
and rctl.description not like '% %' and rctl.description not like '% %'--AND --NVL(RSU.ATTRIBUTE1,RCT.INVOICE_CURRENCY_CODE)=NVL(:P_CURRENCY_CODE,GSOB.CURRENCY_CODE)
GROUP BY
oof.organization_code||'HI'|| RCT.TRX_NUMBER,
ra.address_id,
rc.customer_id,
RCT.SET_OF_BOOKS_ID,
RSU.LOCATION,
SLA1.SELLING_PRICE*(SLA1.ordered_quantity-nvl(sla1.cancelled_quantity,0)),
RCT.INVOICE_CURRENCY_CODE,
RCT.CREATION_DATE,
RCT.CUSTOMER_TRX_ID,
RC.CUSTOMER_NAME,
RC.CUSTOMER_NUMBER,
RCT.WAYBILL_NUMBER,
RCT.PURCHASE_ORDER,
RA.ADDRESS1,ra.ADDRESS2,ra.ADDRESS3,ra.ADDRESS4 ,
RCT.TRX_NUMBER,
RCT.TRX_DATE,
decode(rt.name,' ',rt.name, RT.NAME||' '),
RCTL.SALES_ORDER,
MSI.ATTRIBUTE4,
rctl.attribute_category,
RCTL.ATTRIBUTE1,
RCTL.ATTRIBUTE2,
RCTL.ATTRIBUTE3,
rctl.attribute4||' ',
rctl.attribute4,
(SLA1.ordered_quantity-nvl(sla1.cancelled_quantity,0))/1000,
sla1.LIST_PRICE*1000,
SLA1.SELLING_PRICE*1000,
RCTL.EXTENDED_AMOUNT,
RCTL.LINE_NUMBER,
sla.original_system_line_reference,
SHA.ORDER_NUMBER,
DECODE(LENGTH(RCTL.INTERFACE_LINE_ATTRIBUTE2),4,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,1,4),7,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,4,7),8,SUBSTR(RCTL.INTERFACE_LINE_ATTRIBUTE2,5,8),RCTL.INTERFACE_LINE_ATTRIBUTE2)||'-'||RCTL.SALES_ORDER||'-'||RCT.ATTRIBUTE10,
SPA.HEADER_ID
ORDER BY RCT.TRX_NUMBER
PLAN (for STAR)
SELECT STATEMENT, GOAL = RULE
SORT GROUP BY
HASH JOIN OUTER
HASH JOIN OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
HASH JOIN
TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS
INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N1
HASH OUTER JOIN
NESTED LOOPS OUTER
HASH JOIN
TABLE ACCESS FULL RA_CUSTOMER_TRX_LINES_ALL
NESTED LOOPS OUTER
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID RA_CUSTOMERS
INDEX RANGE SCAN RA_CUSTOMERS_U3
TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_ALL
INDEX RANGE SCAN RA_CUSTOMER_TRX_N11
VIEW ORG_ORGANIZATION_DEFINITIONS
HASH JOIN
TABLE ACCESS FULL GL_SETS_OF_BOOKS
MERGE JOIN CARTESIAN
HASH JOIN
HASH JOIN
TABLE ACCESS FULL HR_ORGANIZATION_INFORMATION
NESTED LOOP
TABLE ACCESS FULL MTL_PARAMETERS
INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK
TABLE ACCESS FULL HR_ORGANIZATION_INFORMATION
SORT JOIN
TABLE ACCESS FULL FND_PRODUCT_GROUPS
TABLE ACCESS BY ROWID RA_SITE_USES_ALL
INDEX UNIQUE SCAN RA_SITE_USES_U1
TABLE ACCESS FULL RA_TERMS
TABLE ACCESS BY INDEX ROWID RA_ADDRESSES_ALL
INDEX UNIQUE SCAN RA_ADDRESSES_U1
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX UNIQUE SCAN SO_LINES_U1
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX UNIQUE SCAN SO_LINES_U1
TABLE ACCESS FULL SO_HEADERS_ALL
TABLE ACCESS FULL SO_PRICE_ADJUSTMENTS
Execution time (using --+ORDERED): 91.795 secs*
Execution time (using --+STAR): 59.137 secs*
Modified
All we added was to force the optimizer to use nested loops (which made a large difference), and hinted a few indexes (which made slight differences)
select --+USE_NL INDEX (sha,so_headers_u1) INDEX (rctl,ra_customer_trx_lines_n2)
etc...
PLAN
SELECT STATEMENT, GOAL = RULE
SORT GROUP BY
MERGE JOIN OUTER
SORT JOIN
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_ALL
INDEX RANGE SCAN RA_CUSTOMER_TRX_N1
TABLE ACCESS BY INDEX ROWID RA_TERMS
INDEX UNIQUE SCAN RA_TERMS_U1
TABLE ACCESS BY INDEX ROWID RA_SITE_USES_ALL
INDEX UNIQUE SCAN RA_SITE_USES_U1
TABLE ACCESS BY INDEX ROWID RA_ADDRESSES_ALL
INDEX UNIQUE SCAN RA_ADDRESSES_U1
TABLE ACCESS BY INDEX ROWID RA_CUSTOMERS
INDEX UNIQUE SCAN RA_CUSTOMERS_U1
TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS
INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N4
TABLE ACCESS BY INDEX ROWID RA_CUSTOMER_TRX_LINES_ALL
INDEX RANGE SCAN RA_CUSTOMER_TRX_LINES_N2
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX UNIQUE SCAN SO_LINES_U1
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX UNIQUE SCAN SO_LINES_U1
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX UNIQUE SCAN SO_HEADERS_U1
TABLE ACCESS BY INDEX ROWID SO_PRICE_ADJUSTMENTS
INDEX RANGE SCAN SO_PRICE_ADJUSTMENTS_N2
SORT JOIN
VIEW
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
MERGE JOIN
SORT JOIN
NESTED LOOPS
TABLE ACCESS FULL FND_PRODUCT_GROUPS
TABLE ACCESS FULL GL_SETS_OF_BOOKS
SORT JOIN
TABLE ACCESS FULL HR_ORGANIZATION_INFORMATION
INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK
TABLE ACCESS BY INDEX ROWID MTL_PARAMETERS
INDEX UNIQUE SCAN MTL_PARAMETERS_U1
TABLE ACCESS BY INDEX ROWID HR_ORGANIZATION_INFORMATION
INDEX RANGE SCAN HR_ORGANIZATION_INFORMATION_FK2
Execution time : 4.958 secs*
Final notes
The skill of optimization is not the knowledge of how it is done. This document can only provide the details of what can be done. The real skill of optimization is experience, to know when to use what to achieve the desired goal.
All optimizations depend on the data in the tables and the index available. As you seen above sometimes a what seems less efficient method is actually better, e.g. using a full table scan instead of the index, and splitting a statement to uses UNION ALL to force the optimizer to do different things for different parameters. In the end it is up to the developer to produce the best method to achieved the desired goal and not the optimizer itself.
Site Sponsors
Monday, January 31, 2011
GLOBAL TEMPORARY TABLE
DROP TABLE G_MIKE_PO_LINES
/
CREATE GLOBAL TEMPORARY TABLE GLB_MIKE_PO_LINES
(
AMOUNT NUMBER,
TAX_CODE VARCHAR2(15 BYTE),
ACCOUNT VARCHAR2(150 BYTE),
PO_NUMBER VARCHAR2(20 BYTE),
PO_LINE_NUM NUMBER,
SHIPMENT_NUM NUMBER,
DISTRIBUTION_NUM NUMBER,
SKU_NUMBER VARCHAR2(150 BYTE),
ITEM_DESCRIPTION VARCHAR2(240 BYTE),
QUANTITY_INVOICED NUMBER,
SHIP_TO_LOCATION VARCHAR2(60 BYTE),
UNIT_PRICE NUMBER,
PROJECT VARCHAR2(30 BYTE),
TASK VARCHAR2(20 BYTE),
EXPENDITURE_ORG VARCHAR2(3 BYTE),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
EXPENDITURE_ITEM_DATE DATE
)
ON COMMIT DELETE ROWS;
Bookmark Go to End
Doc ID: Note:68098.1
Subject: Overview of Temporary Tables
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 19-JAN-1999
Last Revision Date: 17-AUG-2004
Introduction
~~~~~~~~~~~~
This is an overview of TEMPORARY TABLES introduced in Oracle8i. This
new feature allows temporary tables to be created automatically in a
users temporary tablespace.
Syntax
~~~~~~
CREATE GLOBAL TEMPORARY TABLE tablename ( columns )
[ ON COMMIT PRESERVE | DELETE ROWS ]
The default option is to delete rows on commit.
What Happens
~~~~~~~~~~~~
When you create a GLOBAL TEMPORARY table a dictionary definition of
the table is created. As soon as the table gets populated (on the first
INSERT or at creation time for CTAS operations) a temporary segment is
created in the users default TEMPORARY tablespace location. This temporary
segments contents are just like a normal table.
Different sessions using the same GLOBAL TEMPORARY table get allocated
different temporary segments. The temporary segments are cleaned up
automatically at session end or transaction end depending on the specified
duration (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).
Apart from the data visibility temporary tables can be used like ordinary
tables for most operations.
@Internally TAB$ has FILE#/BLOCK#/TS# all set to 0 for temporary tables.
Characteristics
~~~~~~~~~~~~~~~
1. Data exists only for the duration of either the session or
transaction.
This can be specified in the create table command.
For example:
SQL> Create global temporary table emp_temp(eno number)
on commit delete rows;
- OR -
SQL> Create global temporary table emp_temp(eno number)
on commit preserve rows;
ON COMMIT DELETE ROWS indicates a transaction level duration and
PRESERVE indicates a session level duration.
2. Data is visible only at session or transaction level. Multiple
users using the same temporary table can see the definition
of the table and their own data segment and nothing else.
3. Indexes, triggers and views can be created on these tables.
4. If an Index is created on temporary tables then it MUST be created
when the table is empty - ie: When there are NO temporary segments for
incarnations of the table. Indexes are implemented as separate
temporary segments.
5. No redo is generated for operations on the temporary table itself BUT
undo is generated. Redo *IS* generated for the undo so temporary tables
do indirectly generate redo.
6. The keyword GLOBAL indicates the table definition can be viewed
by anybody with sufficient privileges - ie:using the same rules
that apply to normal user tables. Currently only GLOBAL TEMPORARY
tables are supported.
7. TRUNCATE operations truncate only the current session's incarnation
of the table.
8. You can only export or import the definition not the data.
9. Segments get created only on the first insert (or CTAS) operation.
Drawbacks
~~~~~~~~~
1. The table definition is not dropped automatically.
2. Only GLOBAL tables are supported right now, not local ones.
3. Can perform DDL only when no session is bound to it.
4. There is no underlying support for STATISTICS on GLOBAL
TEMPORARY tables so CBO (Cost Based Optimizer) has no statistical
information to help determine an execution plan.
NB: "ANALYZE TABLE COMPUTE/ESTIMATE STATISTICS" returns success
even though no statistics are gathered in 8i. 9i and higher
provides support for STATISTICS.
Constraints
~~~~~~~~~~~
Constraints can be implemented on the table either at the session or
transaction level depending on the scope of the temporary table and
are not for the entire table even though the constraint is defined
for the entire table.
If there is a primary key or unique key constraint, it is applicable only at
either session or transaction leve i.e. two users can enter the same values
into the table from different sessions even though you have a primary / unique
key in place for the entire table (if the scope is the session )
In the case of a transaction level temporary table, the same values
can be entered from different transactions.
/
CREATE GLOBAL TEMPORARY TABLE GLB_MIKE_PO_LINES
(
AMOUNT NUMBER,
TAX_CODE VARCHAR2(15 BYTE),
ACCOUNT VARCHAR2(150 BYTE),
PO_NUMBER VARCHAR2(20 BYTE),
PO_LINE_NUM NUMBER,
SHIPMENT_NUM NUMBER,
DISTRIBUTION_NUM NUMBER,
SKU_NUMBER VARCHAR2(150 BYTE),
ITEM_DESCRIPTION VARCHAR2(240 BYTE),
QUANTITY_INVOICED NUMBER,
SHIP_TO_LOCATION VARCHAR2(60 BYTE),
UNIT_PRICE NUMBER,
PROJECT VARCHAR2(30 BYTE),
TASK VARCHAR2(20 BYTE),
EXPENDITURE_ORG VARCHAR2(3 BYTE),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
EXPENDITURE_ITEM_DATE DATE
)
ON COMMIT DELETE ROWS;
Bookmark Go to End
Doc ID: Note:68098.1
Subject: Overview of Temporary Tables
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 19-JAN-1999
Last Revision Date: 17-AUG-2004
Introduction
~~~~~~~~~~~~
This is an overview of TEMPORARY TABLES introduced in Oracle8i. This
new feature allows temporary tables to be created automatically in a
users temporary tablespace.
Syntax
~~~~~~
CREATE GLOBAL TEMPORARY TABLE tablename ( columns )
[ ON COMMIT PRESERVE | DELETE ROWS ]
The default option is to delete rows on commit.
What Happens
~~~~~~~~~~~~
When you create a GLOBAL TEMPORARY table a dictionary definition of
the table is created. As soon as the table gets populated (on the first
INSERT or at creation time for CTAS operations) a temporary segment is
created in the users default TEMPORARY tablespace location. This temporary
segments contents are just like a normal table.
Different sessions using the same GLOBAL TEMPORARY table get allocated
different temporary segments. The temporary segments are cleaned up
automatically at session end or transaction end depending on the specified
duration (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).
Apart from the data visibility temporary tables can be used like ordinary
tables for most operations.
@Internally TAB$ has FILE#/BLOCK#/TS# all set to 0 for temporary tables.
Characteristics
~~~~~~~~~~~~~~~
1. Data exists only for the duration of either the session or
transaction.
This can be specified in the create table command.
For example:
SQL> Create global temporary table emp_temp(eno number)
on commit delete rows;
- OR -
SQL> Create global temporary table emp_temp(eno number)
on commit preserve rows;
ON COMMIT DELETE ROWS indicates a transaction level duration and
PRESERVE indicates a session level duration.
2. Data is visible only at session or transaction level. Multiple
users using the same temporary table can see the definition
of the table and their own data segment and nothing else.
3. Indexes, triggers and views can be created on these tables.
4. If an Index is created on temporary tables then it MUST be created
when the table is empty - ie: When there are NO temporary segments for
incarnations of the table. Indexes are implemented as separate
temporary segments.
5. No redo is generated for operations on the temporary table itself BUT
undo is generated. Redo *IS* generated for the undo so temporary tables
do indirectly generate redo.
6. The keyword GLOBAL indicates the table definition can be viewed
by anybody with sufficient privileges - ie:using the same rules
that apply to normal user tables. Currently only GLOBAL TEMPORARY
tables are supported.
7. TRUNCATE operations truncate only the current session's incarnation
of the table.
8. You can only export or import the definition not the data.
9. Segments get created only on the first insert (or CTAS) operation.
Drawbacks
~~~~~~~~~
1. The table definition is not dropped automatically.
2. Only GLOBAL tables are supported right now, not local ones.
3. Can perform DDL only when no session is bound to it.
4. There is no underlying support for STATISTICS on GLOBAL
TEMPORARY tables so CBO (Cost Based Optimizer) has no statistical
information to help determine an execution plan.
NB: "ANALYZE TABLE COMPUTE/ESTIMATE STATISTICS" returns success
even though no statistics are gathered in 8i. 9i and higher
provides support for STATISTICS.
Constraints
~~~~~~~~~~~
Constraints can be implemented on the table either at the session or
transaction level depending on the scope of the temporary table and
are not for the entire table even though the constraint is defined
for the entire table.
If there is a primary key or unique key constraint, it is applicable only at
either session or transaction leve i.e. two users can enter the same values
into the table from different sessions even though you have a primary / unique
key in place for the entire table (if the scope is the session )
In the case of a transaction level temporary table, the same values
can be entered from different transactions.
How to get XPLAIN PLAN for a SQL query
set lines 200
set pages 10000
delete plan_table where statement_id = 'MY_XPLAN'
/* INSERT BINDS HERE */
variable b1 number
variable b2 varchar2(100)
explain plan set statement_id = 'MY_XPLAN'
for
/* Replace the next statement with the statement you
want to explain
*/
select sal
from emp
where ename = :b2
/
set feedback off
select *
from table(dbms_xplan.display(null, 'MY_XPLAN'));
set feedback on
set lines 200
set pages 10000
EXPLAIN PLAN SET STATEMENT_ID = 'A9'
FOR
SELECT PPP.partner_id
,PPP.partner_party_id
,PPP.partner_level
,PAT.description
FROM apps.pv_partner_profiles PPP
,PV_ATTRIBUTE_CODES_TL PAT
WHERE PPP.partner_level = PAT.ATTR_CODE_ID
AND PAT.language = userenv('LANG')
AND PPP.partner_id = 3716072;
SELECT * FROM TABLE(DBMS_XPLAN.display(null, 'A9'));
Method 2
---------
EXPLAIN PLAN FOR
SELECT * FROM table_name WHERE where_clause;
SET LINESIZE 120
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
set pages 10000
delete plan_table where statement_id = 'MY_XPLAN'
/* INSERT BINDS HERE */
variable b1 number
variable b2 varchar2(100)
explain plan set statement_id = 'MY_XPLAN'
for
/* Replace the next statement with the statement you
want to explain
*/
select sal
from emp
where ename = :b2
/
set feedback off
select *
from table(dbms_xplan.display(null, 'MY_XPLAN'));
set feedback on
set lines 200
set pages 10000
EXPLAIN PLAN SET STATEMENT_ID = 'A9'
FOR
SELECT PPP.partner_id
,PPP.partner_party_id
,PPP.partner_level
,PAT.description
FROM apps.pv_partner_profiles PPP
,PV_ATTRIBUTE_CODES_TL PAT
WHERE PPP.partner_level = PAT.ATTR_CODE_ID
AND PAT.language = userenv('LANG')
AND PPP.partner_id = 3716072;
SELECT * FROM TABLE(DBMS_XPLAN.display(null, 'A9'));
Method 2
---------
EXPLAIN PLAN FOR
SELECT * FROM table_name WHERE where_clause;
SET LINESIZE 120
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SQL Useful tips.
FND_GLOBAL variables.
Request ID => fnd_global.conc_request_id
Program Application ID => fnd_global.prog_appl_id
Program Id => fnd_global.conc_program_id
Last Updated By / Created_By => fnd_global.user_id
Last Update Login => fnd_global.login_id
Organization ID => fnd_global.org_id
Script to run Gather Table Statistics
dbms_stats.gather_table_stats('OSM'
,'AS_TERR_RESOURCES_TMP'
,estimate_percent => 10
,degree => 8
,granularity => 'GLOBAL'
,cascade =>TRUE) ;
Query to get installed Oracle Products.
SELECT PRODUCT_GROUP_ID
,PRODUCT_GROUP_NAME
,RELEASE_NAME
,PRODUCT_GROUP_TYPE
,MULTI_ORG_FLAG
,MULTI_LINGUAL_FLAG
,MULTI_CURRENCY_FLAG
,APPLICATIONS_SYSTEM_NAME
FROM FND_PRODUCT_GROUPS;
SQL Query to find the instance patch set level:
select patch_level from fnd_product_installations where patch_level like '%QOT%'
select *
from fnd_product_installations a
,fnd_application_tl b
where a.application_id = b.application_id
and a.application_id = 170
Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;
Ref Cursor::
Reffering cursor declared in another package is called Ref Cursor.
Example:
DECLARE
v_cursor com_dir_details_report_pkg.repcode_ref_cur;
l_repcode varchar2(100);
BEGIN
com_dir_details_report_pkg.get_repcodes(
'12'
, '2005',16,v_cursor);
LOOP
FETCH v_cursor
INTO l_repcode;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_repcode);
END LOOP;
CLOSE v_cursor;
END;
Request ID => fnd_global.conc_request_id
Program Application ID => fnd_global.prog_appl_id
Program Id => fnd_global.conc_program_id
Last Updated By / Created_By => fnd_global.user_id
Last Update Login => fnd_global.login_id
Organization ID => fnd_global.org_id
Script to run Gather Table Statistics
dbms_stats.gather_table_stats('OSM'
,'AS_TERR_RESOURCES_TMP'
,estimate_percent => 10
,degree => 8
,granularity => 'GLOBAL'
,cascade =>TRUE) ;
Query to get installed Oracle Products.
SELECT PRODUCT_GROUP_ID
,PRODUCT_GROUP_NAME
,RELEASE_NAME
,PRODUCT_GROUP_TYPE
,MULTI_ORG_FLAG
,MULTI_LINGUAL_FLAG
,MULTI_CURRENCY_FLAG
,APPLICATIONS_SYSTEM_NAME
FROM FND_PRODUCT_GROUPS;
SQL Query to find the instance patch set level:
select patch_level from fnd_product_installations where patch_level like '%QOT%'
select *
from fnd_product_installations a
,fnd_application_tl b
where a.application_id = b.application_id
and a.application_id = 170
Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;
Ref Cursor::
Reffering cursor declared in another package is called Ref Cursor.
Example:
DECLARE
v_cursor com_dir_details_report_pkg.repcode_ref_cur;
l_repcode varchar2(100);
BEGIN
com_dir_details_report_pkg.get_repcodes(
'12'
, '2005',16,v_cursor);
LOOP
FETCH v_cursor
INTO l_repcode;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_repcode);
END LOOP;
CLOSE v_cursor;
END;
Session/Table Unlock script:
select object_name, object_id
from dba_objects
where object_id in (select object_id
from v$locked_object);
select substr(username, 1, 10),
sid,
serial#
from v$session
where sid in (select session_id
from v$locked_object
where object_id = 981549)
alter system kill session '175,3262';
from dba_objects
where object_id in (select object_id
from v$locked_object);
select substr(username, 1, 10),
sid,
serial#
from v$session
where sid in (select session_id
from v$locked_object
where object_id = 981549)
alter system kill session '175,3262';
BULK COLLECTION IN PL/SQL
BULK COLLECT
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;
Bulk Fetch with LIMIT - limits fetching rows to a specified number.
This number can be a variable or an expression. But it can't be negative.
You can use limit as described below:
open cursor;
loop
fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;
for i in 1 .. l_c1.count
loop
process....
end loop;
forall i in 1 .. l_c1.count
insert into ..... values ( L_c1(i), .... );
forall i in 1 .. l_c1.count
update emp set sal = sal * 1.2 where dept_no = l_c1(i);
exit when c%notfound;
end loop;
close cursor
1000 is already a big number.
With bigger number, we could meet problems with OS limits.
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;
Bulk Fetch with LIMIT - limits fetching rows to a specified number.
This number can be a variable or an expression. But it can't be negative.
You can use limit as described below:
open cursor;
loop
fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;
for i in 1 .. l_c1.count
loop
process....
end loop;
forall i in 1 .. l_c1.count
insert into ..... values ( L_c1(i), .... );
forall i in 1 .. l_c1.count
update emp set sal = sal * 1.2 where dept_no = l_c1(i);
exit when c%notfound;
end loop;
close cursor
1000 is already a big number.
With bigger number, we could meet problems with OS limits.
FNDLOAD Scripts for AOL Objects
Comment: Use FNDLOAD to Download and Upload data into a new instance.
----------------------------------------------------------------------------------
Concurrent Program:
----------------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct .ldt PROGRAM CONCURRENT_PROGRAM_NAME='' APPLICATION_SHORT_NAME='BOLINF'
Concurrent Program Upload
---------------------------
FNDLOAD $APPS_ID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $PATH/.ldt
DOWNLOAD Report
----------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $XBOL_TOP/patch/115/import/US/<..GECF_FN..>.ldt PROGRAM CONCURRENT_PROGRAM_NAME=<..GECF_FN..>
UPLOAD Report
-------------
FNDLOAD0 Y UPLOAD PARTIAL $FND_TOP/patch/115/import/afcpprog.lct <$path\filename>
DOWNLOAD Spreadtable Grid
-------------------------
FNDLOAD apps/or4cle50 0 Y DOWNLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $XBOL_TOP/patch/115/import/STDOKLTRX.ldt JTF_GRID_DATASOURCES GRID_DATASOURCE_NAME=<..SPD_NAME..>
UPLOAD Spreadtable Grid
-----------------------
FNDLOAD apps/apps 0 Y UPLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $XBOL_TOP/patch/115/import/US/<..GECF_FN..>.ldt
FNDLOAD for DESC_FLEX - Context specific
----------------------------------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $PATH/<..GECF_FN..>.ldt DESC_FLEX APPLICATION_SHORT_NAME = 'AR%' DESCRIPTIVE_FLEXFIELD_NAME=<..DESC_NAME..> P_CONTEXT_CODE=XYZ
Request Set Download
--------------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct $PATH/<..GECF_FN..>.ldt REQ_SET REQUEST_SET_NAME=""
Request Set Upload
------------------
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct $PATH/<..GECF_FN..>.ldt
Request Set Stage Links
-------------------------
FNDLOAD sqllogon 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct $PATH/<..GECF_FN..>.ldt REQ_SET_LINKS REQUEST_SET_NAME="<>"
Upload Request Set Stage links information
-------------------------------------------
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct $PATH/<..GECF_FN..>.ldt
*** All the required Concurrent Program definitions should exist before loading the Request Set on the Target Instance. ***
Profile Upload
---------------
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/admin/import/afscprof.lct $PATH/<..GECF_FN..>.plt
Profile Download
---------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
Responsibility Download
----------------------
FNDLOAD apps/ 0 Y DOWNLOAD_PARTIAL @FND:patch/115/import/afscursp.lct $PATH/<..GECF_FN..>.ldt FND_RESPONSIBILITY "responsibility name".
User Upload
-------------
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/admin/import/afscursp.lct $PATH/<..GECF_FN..>.ldt
Menu Upload
------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $XBOL_TOP/patch/115/import/.ldt MENU MENU_NAME=<..Menu_Name..>
Form Register Download
----------------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $XBOL_TOP/patch/115/import/.ldt FORM FORM_NAME=GECF_OKLCSHDR
Form Register Upload
--------------------
FNDLOAD apps/ 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XBOL_TOP/patch/115/import/.ldt FORM FORM_NAME=<..FORM_NAME..>
Form Function Register Download
-------------------------------
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $XBOL_TOP/patch/115/import/.ldt FUNCTION FUNCTION_NAME=<..FUNC_NAME..>
Form Function Register Upload
-------------------------------
FNDLOAD apps/ 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XBOL_TOP/patch/115/import/<..GECF_FN..>.ldt FUNCTION FUNCTION_NAME=<..FUNC_NAME..>
Form Function Register Upload With force Option
-----------------------------------------------
FNDLOAD $APPS_ID 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $<..FUNCTION_FILE_NAME..>.ldt FUNCTION FUNCTION_NAME=<..FUNCTION_NAME..> - WARNINGS=TRUE CUSTOM_MODE=FORCE
Workflow Event Download
-----------------------
jre oracle.apps.fnd.wf.WFXLoad -d apps or4cle50 mmfolmdb01.mmf.capital.ge.com:1553:mmfd50 thin US GECF_GAP2945839_LTR_E.wfx EVENTS oracle.apps.wf.gecf.ltr
Workflow Subscription Download
------------------------------
jre oracle.apps.fnd.wf.WFXLoad -d apps or4cle50 mmfolmdb01.mmf.capital.ge.com:1553:mmfd50 thin US GECF_GAP2945839_LTR_S.wfx SUBSCRIPTIONS oracle.apps.wf.gecf
Printer Styles
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
Lookups
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"
LOOKUP_TYPE="lookup name"
FNDLOAD $APPS_ID O Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct $XBOL_TOP/bin/XXMI_UTL_CDU_FLATCH_CATEGORY.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XBOL" LOOKUP_TYPE="XXMI_FILE_ATTACH_CATEGORY" - CUSTOM_MODE=FORCE
Descriptive Flexfield with all of specific Contexts
----------------------------------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
Key Flexfield Structures
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
Value Sets
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
Value Sets with values
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
Request Groups
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
Responsibilities
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
Menus
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
Others:
FNDLOAD commands for NLS Languages
---------------------------------
export NLS_LANG="DUTCH_THE NETHERLANDS.WE8ISO8859P1"
FNDLOAD APPS/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct \APACCENG.ldt PROGRAM CONCURRENT_PROGRAM_NAME=APACCENG APPLICATION_SHORT_NAME=SQLAP
FNDLOAD/ 0 Y UPLOAD \- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE
--------------------------------------------------------------------------------
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDI824_OUT.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDI824
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_FILEARC.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPARC
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_GL_ACCT_IN_REQ.ldt REQ_SET REQUEST_SET_NAME=FNDRSSUB725
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_GL_ACCT_IN_RLINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=FNDRSSUB725
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDI824_OUT.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDI824
FNDLOAD FOR DOWNLOAD OF MESAGES
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_OBJNOT_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_OBJ_NOT_FOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_OBJNOT_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_PROFILE_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_PROFILE_NOTSET
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_PROFILE_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_REJIQRY_VIEW_NOTFOUND_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_VIEW_NOTFOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_REJIQRY_VIEW_NOTFOUND_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_OBJECT_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_OBJECT_NOTFOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_OBJECT_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD FOR DOWNLOAD OF FUNCTIONS
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_FUNCT.ldt FUNCTION FUNCTION_NAME=XXJCP_REJINQRY_VIEW - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_FUNCT.ldt FUNCTION FUNCTION_NAME='XXJCP_REJINQRY_VIEW'
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU MENU_NAME=POS_ACCOUNT USER_MENU_NAME="Account" DESCRIPTION="Account" PROMPT="View Rejected Invoice" SUBMENU = "" FUNCTION =XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU PROMPT="View Rejected Invoice" SUBMENU = "" FUNCTION =XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT FUNCTION_NAME=XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL$FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT CUSTOM_MODE=FORCE
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afsload.lct PWRB_LITIGATION_MENU.ldt MENU MENU_NAME="WRB_PWD_NAVIGATE_GUI_G"
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_FORM.ldt FORM APPLICATION_SHORT_NAME=AP FORM_NAME=XXJCPTXHT
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_FUNC.ldt FUNCTION FUNCTION_NAME=XXJCP_AP_TXN_HIST - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_SUBMENU.ldt MENU MENU_NAME=JCP_TXNHIST_SUBMENU
FNDLOAD APPS/ASK4APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_MENU.ldt MENU MENU_NAME=JCP_CUSTOM_DEVELOPMENT
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_FORM.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_FUNC.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ASK4APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ASK4APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_IMP_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPTXNHISTIMP
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_PURGE_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPTXNHISTPURGE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET.ldt REQ_SET REQUEST_SET_NAME=FNDRSSUB752 APPLICATION_SHORT_NAME=XXJCP
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=FNDRSSUB752 APPLICATION_SHORT_NAME=XXJCP
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_IMP_PROG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_PURGE_PROG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET_LINK.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDITP_IN.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDITPIN
FNDLOAD APPS/APPS O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXJCP_NONPOINV_FLEXVAL_SET.ldt VALUE_SET FLEX_VALUE_SET_NAME="JCP_NON_STORE_LOCATIONS"
FNDLOAD APPS/APPS O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXJCP_NONPOINV_FLEXVAL_SET.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXJCP_NONPOINV_STORE_PROFILE.ldt PROFILE PROFILE_NAME="JCP_STORE_FENCE" APPLICATION_SHORT_NAME="XXJCP"
FNDLOAD APPS/APPS O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXJCP_NONPOINV_STORE_PROFILE.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ask4apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_MCH_CALC_DUE_DATE.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCP_MCH_CALC_DUE_DATE
FNDLOAD APPS/ask4apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_MCH_CALC_DUE_DATE.ldt - CUSTOM_MODE=FORCE
----------------------------------------------------------------------------------
Concurrent Program:
----------------------
FNDLOAD apps/
Concurrent Program Upload
---------------------------
FNDLOAD $APPS_ID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $PATH/
DOWNLOAD Report
----------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $XBOL_TOP/patch/115/import/US/<..GECF_FN..>.ldt PROGRAM CONCURRENT_PROGRAM_NAME=<..GECF_FN..>
UPLOAD Report
-------------
FNDLOAD
DOWNLOAD Spreadtable Grid
-------------------------
FNDLOAD apps/or4cle50 0 Y DOWNLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $XBOL_TOP/patch/115/import/STDOKLTRX.ldt JTF_GRID_DATASOURCES GRID_DATASOURCE_NAME=<..SPD_NAME..>
UPLOAD Spreadtable Grid
-----------------------
FNDLOAD apps/apps 0 Y UPLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $XBOL_TOP/patch/115/import/US/<..GECF_FN..>.ldt
FNDLOAD for DESC_FLEX - Context specific
----------------------------------------
FNDLOAD apps/
Request Set Download
--------------------
FNDLOAD apps/
Request Set Upload
------------------
FNDLOAD apps/
Request Set Stage Links
-------------------------
FNDLOAD sqllogon 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct $PATH/<..GECF_FN..>.ldt REQ_SET_LINKS REQUEST_SET_NAME="<>"
Upload Request Set Stage links information
-------------------------------------------
FNDLOAD apps/
*** All the required Concurrent Program definitions should exist before loading the Request Set on the Target Instance. ***
Profile Upload
---------------
FNDLOAD apps/
Profile Download
---------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
Responsibility Download
----------------------
FNDLOAD apps/
User Upload
-------------
FNDLOAD apps/
Menu Upload
------------
FNDLOAD apps/
Form Register Download
----------------------
FNDLOAD apps/
Form Register Upload
--------------------
FNDLOAD apps/
Form Function Register Download
-------------------------------
FNDLOAD apps/
Form Function Register Upload
-------------------------------
FNDLOAD apps/
Form Function Register Upload With force Option
-----------------------------------------------
FNDLOAD $APPS_ID 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $<..FUNCTION_FILE_NAME..>.ldt FUNCTION FUNCTION_NAME=<..FUNCTION_NAME..> - WARNINGS=TRUE CUSTOM_MODE=FORCE
Workflow Event Download
-----------------------
jre oracle.apps.fnd.wf.WFXLoad -d apps or4cle50 mmfolmdb01.mmf.capital.ge.com:1553:mmfd50 thin US GECF_GAP2945839_LTR_E.wfx EVENTS oracle.apps.wf.gecf.ltr
Workflow Subscription Download
------------------------------
jre oracle.apps.fnd.wf.WFXLoad -d apps or4cle50 mmfolmdb01.mmf.capital.ge.com:1553:mmfd50 thin US GECF_GAP2945839_LTR_S.wfx SUBSCRIPTIONS oracle.apps.wf.gecf
Printer Styles
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
Lookups
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"
LOOKUP_TYPE="lookup name"
FNDLOAD $APPS_ID O Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct $XBOL_TOP/bin/XXMI_UTL_CDU_FLATCH_CATEGORY.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XBOL" LOOKUP_TYPE="XXMI_FILE_ATTACH_CATEGORY" - CUSTOM_MODE=FORCE
Descriptive Flexfield with all of specific Contexts
----------------------------------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
Key Flexfield Structures
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
Value Sets
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
Value Sets with values
-----------------------
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
Request Groups
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
Responsibilities
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
Menus
-----------------------
FNDLOAD apps/apps\@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
Others:
FNDLOAD commands for NLS Languages
---------------------------------
export NLS_LANG="DUTCH_THE NETHERLANDS.WE8ISO8859P1"
FNDLOAD APPS/
FNDLOAD
--------------------------------------------------------------------------------
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDI824_OUT.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDI824
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_FILEARC.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPARC
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_GL_ACCT_IN_REQ.ldt REQ_SET REQUEST_SET_NAME=FNDRSSUB725
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_GL_ACCT_IN_RLINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=FNDRSSUB725
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDI824_OUT.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDI824
FNDLOAD FOR DOWNLOAD OF MESAGES
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_OBJNOT_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_OBJ_NOT_FOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_OBJNOT_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_PROFILE_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_PROFILE_NOTSET
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_PROFILE_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_REJIQRY_VIEW_NOTFOUND_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_VIEW_NOTFOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_REJIQRY_VIEW_NOTFOUND_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXJCP_EDI824_REJIQRY_OBJECT_MSG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=POS MESSAGE_NAME=XXJCP_REJIQRY_OBJECT_NOTFOUND
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_OBJECT_MSG.ldt - CUSTOM_MODE=FORCE
FNDLOAD FOR DOWNLOAD OF FUNCTIONS
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_FUNCT.ldt FUNCTION FUNCTION_NAME=XXJCP_REJINQRY_VIEW - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_FUNCT.ldt FUNCTION FUNCTION_NAME='XXJCP_REJINQRY_VIEW'
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU MENU_NAME=POS_ACCOUNT USER_MENU_NAME="Account" DESCRIPTION="Account" PROMPT="View Rejected Invoice" SUBMENU = "" FUNCTION =XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU PROMPT="View Rejected Invoice" SUBMENU = "" FUNCTION =XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT FUNCTION_NAME=XXJCP_REJINQRY_VIEW - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL$FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT - CUTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/iSPReject_Inqry/XXJCP_EDI824_REJIQRY_MAIN_MENU.ldt MENU PARENT_MENU_NAME=POS_ACCOUNT CUSTOM_MODE=FORCE
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afsload.lct PWRB_LITIGATION_MENU.ldt MENU MENU_NAME="WRB_PWD_NAVIGATE_GUI_G"
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_FORM.ldt FORM APPLICATION_SHORT_NAME=AP FORM_NAME=XXJCPTXHT
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_FUNC.ldt FUNCTION FUNCTION_NAME=XXJCP_AP_TXN_HIST - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_SUBMENU.ldt MENU MENU_NAME=JCP_TXNHIST_SUBMENU
FNDLOAD APPS/ASK4APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXJCP_TXN_HIST_MENU.ldt MENU MENU_NAME=JCP_CUSTOM_DEVELOPMENT
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_FORM.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_FUNC.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ASK4APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ASK4APPS 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afsload.lct $XXJCP_TOP/install/transaction_hist/XXJCP_TXN_HIST_MENU.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_IMP_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPTXNHISTIMP
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_PURGE_PROG.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPTXNHISTPURGE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET.ldt REQ_SET REQUEST_SET_NAME=FNDRSSUB752 APPLICATION_SHORT_NAME=XXJCP
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=FNDRSSUB752 APPLICATION_SHORT_NAME=XXJCP
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_IMP_PROG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_TXNHIST_PURGE_PROG.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXJCP_TXNHIST_IMP_REQSET_LINK.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_EDITP_IN.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCPEDITPIN
FNDLOAD APPS/APPS O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXJCP_NONPOINV_FLEXVAL_SET.ldt VALUE_SET FLEX_VALUE_SET_NAME="JCP_NON_STORE_LOCATIONS"
FNDLOAD APPS/APPS O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXJCP_NONPOINV_FLEXVAL_SET.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/APPS O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXJCP_NONPOINV_STORE_PROFILE.ldt PROFILE PROFILE_NAME="JCP_STORE_FENCE" APPLICATION_SHORT_NAME="XXJCP"
FNDLOAD APPS/APPS O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXJCP_NONPOINV_STORE_PROFILE.ldt - CUSTOM_MODE=FORCE
FNDLOAD APPS/ask4apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_MCH_CALC_DUE_DATE.ldt PROGRAM CONCURRENT_PROGRAM_NAME=XXJCP_MCH_CALC_DUE_DATE
FNDLOAD APPS/ask4apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXJCP_MCH_CALC_DUE_DATE.ldt - CUSTOM_MODE=FORCE
XDO Tables & FNDLOAD scripts
XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL
XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_LOBS
XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FONT_MAPPING_SETS_TL
select * from XDO_DS_DEFINITIONS_B where data_source_code = 'Data source short code'
select * from XDO_DS_DEFINITIONS_TL where rownum < 3
select * from XDO_TEMPLATES_B where TEMPLATE_CODE = 'Template Short code'
select * from XDO_TEMPLATES_TL where TEMPLATE_CODE = 'Template Short code'
select * from XDO_LOBS where lob_code = 'Template Short code'
how to move xml publisher reports from one instance to another instance using fndload.
The FNDLOAD utility is used to upload and download the data definition and Data template created via XML Publisher Administrator responsibility.
The XDOLoader utility is used to upload and download the physical files (RTF, PDF, XSL-FO, XML, and XSD).
Use the following commands to
1) Download all the data definitions and corresponding templates in an instance.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS
2) Download all the data definitions and the corresponding Templates in a specific product.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX
3) Download a particular data definitions and its corresponding Templates.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP DD
4) Upload the data definitions and its corresponding Templates to an instance.
FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt
XDOLOADER :
To download the RTF template and other files use
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-LOG_FILE
Use the below command to upload the rft template and other files
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-XDO_FILE_TYPE
-NLS_LANG
-FILE_CONTENT_TYPE
-FILE_NAME
-OWNER
-CUSTOM_MODE [FORCENOFORCE]
-LOG_FILE
XDO_DS_DEFINITIONS_TL
XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_LOBS
XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FONT_MAPPING_SETS_TL
select * from XDO_DS_DEFINITIONS_B where data_source_code = 'Data source short code'
select * from XDO_DS_DEFINITIONS_TL where rownum < 3
select * from XDO_TEMPLATES_B where TEMPLATE_CODE = 'Template Short code'
select * from XDO_TEMPLATES_TL where TEMPLATE_CODE = 'Template Short code'
select * from XDO_LOBS where lob_code = 'Template Short code'
how to move xml publisher reports from one instance to another instance using fndload.
The FNDLOAD utility is used to upload and download the data definition and Data template created via XML Publisher Administrator responsibility.
The XDOLoader utility is used to upload and download the physical files (RTF, PDF, XSL-FO, XML, and XSD).
Use the following commands to
1) Download all the data definitions and corresponding templates in an instance.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS
2) Download all the data definitions and the corresponding Templates in a specific product.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX
3) Download a particular data definitions and its corresponding Templates.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP DD
4) Upload the data definitions and its corresponding Templates to an instance.
FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt
XDOLOADER :
To download the RTF template and other files use
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-LOG_FILE
Use the below command to upload the rft template and other files
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD
-DB_USERNAME
-DB_PASSWORD
-JDBC_CONNECTION
-LOB_TYPE
-APPS_SHORT_NAME
-LOB_CODE
-LANGUAGE
-TERRITORY
-XDO_FILE_TYPE
-NLS_LANG
-FILE_CONTENT_TYPE
-FILE_NAME
-OWNER
-CUSTOM_MODE [FORCENOFORCE]
-LOG_FILE
Script to set NLS Parameters like 'NLS_LANGUAGE' in TOAD or SQLPlus
dbms_session.set_nls(param IN VARCHAR2, value IN VARCHAR2);
Equivalent to: ALTER SESSION SET = ;
---------------------------------------------------------------
exec dbms_session.set_nls('nls_date_format','''DD-MON-YY''');
---------------------------------------------------------------
select userenv('LANG') from dual;
SELECT LANGUAGE FROM wf_users WHERE NAME LIKE 'INATECH';
BEGIN
fnd_client_info.set_org_context('185');
DBMS_SESSION.set_nls ('NLS_LANGUAGE', 'AMERICAN');
END;
begin
dbms_session.set_nls('NLS_LANGUAGE','''SIMPLIFIED CHINESE''');
END;
select userenv('LANG') from dual;
More information on NLS_Parameter can be found here http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/FUNC22/Default.aspx
-- Equivalent to SQL "ALTER SESSION SET = "
-- Input arguments:
-- param
-- The NLS parameter. The parameter name must begin with 'NLS'.
-- value
-- The value to set the parameter to. If the parameter is a
-- text literal then it will need embedded single-quotes. For
-- example "set_nls('nls_date_format','''DD-MON-YY''')"
Equivalent to: ALTER SESSION SET
---------------------------------------------------------------
exec dbms_session.set_nls('nls_date_format','''DD-MON-YY''');
---------------------------------------------------------------
select userenv('LANG') from dual;
SELECT LANGUAGE FROM wf_users WHERE NAME LIKE 'INATECH';
BEGIN
fnd_client_info.set_org_context('185');
DBMS_SESSION.set_nls ('NLS_LANGUAGE', 'AMERICAN');
END;
begin
dbms_session.set_nls('NLS_LANGUAGE','''SIMPLIFIED CHINESE''');
END;
select userenv('LANG') from dual;
More information on NLS_Parameter can be found here http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/FUNC22/Default.aspx
-- Equivalent to SQL "ALTER SESSION SET
-- Input arguments:
-- param
-- The NLS parameter. The parameter name must begin with 'NLS'.
-- value
-- The value to set the parameter to. If the parameter is a
-- text literal then it will need embedded single-quotes. For
-- example "set_nls('nls_date_format','''DD-MON-YY''')"
Calling Subtemplate from main Template
Applies to:
BI Publisher (formerly XML Publisher) - Version: 5.6.2 to 5.6.3 [Release: 5 to 5]
Information in this document applies to any platform.
13-Jun-2010
Goal
Link a RTF subtemplate to the original template in XML/BI Publisher because only the original template is visible for viewing in the PDF output.
This feature is provided in the BI Publisher (formerly XML Publisher) but there are no precise information in the user guide itself regarding this topic.
Solution
These are the steps to enable subtemplates:
1. In the subtemplate file, add the following tag to the beginning of the section to be included:
<?template:TemplateIdentifier?>
where (TemplateIdentifier) is whatever you would like to call this section of the subtemplate.
Note that in a single subtemplate file, you can have multiple entries, marking different segments you would like to include in other files.
2. Register the subtemplate in Template Manager by selecting Subtemplate=Yes
3. In the main template, include the following tag in the beginning of the file:
<?import:xdo: (product_short_name).(subtemplate_code).(lang).(terr)="" ?="">
The first 2 fields are whatever you registered your subtemplate as.
The last two are the locale for your subtemplate file.
For example:
<?import:xdo: ?="" xdo.test.en.us="">
4. In the place in your main template file where you would like to use the sub template,
add the following tag:
<?call:TemplateIdentifier?>
References
BUG:4483295 - HOW TO INCLUDE SUBTEMPLATE WITH IN THE REPORT USING XML PUBLISHER
NOTE:362496.1 - How to Determine the Version of Oracle XML Publisher for Oracle E-Business Suite 11i and Release 12
BI Publisher (formerly XML Publisher) - Version: 5.6.2 to 5.6.3 [Release: 5 to 5]
Information in this document applies to any platform.
13-Jun-2010
Goal
Link a RTF subtemplate to the original template in XML/BI Publisher because only the original template is visible for viewing in the PDF output.
This feature is provided in the BI Publisher (formerly XML Publisher) but there are no precise information in the user guide itself regarding this topic.
Solution
These are the steps to enable subtemplates:
1. In the subtemplate file, add the following tag to the beginning of the section to be included:
<?template:TemplateIdentifier?>
where (TemplateIdentifier) is whatever you would like to call this section of the subtemplate.
Note that in a single subtemplate file, you can have multiple entries, marking different segments you would like to include in other files.
2. Register the subtemplate in Template Manager by selecting Subtemplate=Yes
3. In the main template, include the following tag in the beginning of the file:
<?import:xdo: (product_short_name).(subtemplate_code).(lang).(terr)="" ?="">
The first 2 fields are whatever you registered your subtemplate as.
The last two are the locale for your subtemplate file.
For example:
<?import:xdo: ?="" xdo.test.en.us="">
4. In the place in your main template file where you would like to use the sub template,
add the following tag:
<?call:TemplateIdentifier?>
References
BUG:4483295 - HOW TO INCLUDE SUBTEMPLATE WITH IN THE REPORT USING XML PUBLISHER
NOTE:362496.1 - How to Determine the Version of Oracle XML Publisher for Oracle E-Business Suite 11i and Release 12
Query to fetch profile option value at all levels.
/* Formatted on 06/12/2010 13:20:04 (QP5 v5.114.809.3010) */
SELECT DISTINCT
p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
DECODE (v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'UnDef')
"CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
and n.user_profile_option_name = 'ICX: Numeric characters'
--or n.user_profile_option_name like '%Password%Case%')
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
--AND v.profile_option_value LIKE '%PROD%'
AND n.LANGUAGE = 'US'
ORDER BY short_name, level_set
SELECT DISTINCT
p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
DECODE (v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'UnDef')
"CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
and n.user_profile_option_name = 'ICX: Numeric characters'
--or n.user_profile_option_name like '%Password%Case%')
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
--AND v.profile_option_value LIKE '%PROD%'
AND n.LANGUAGE = 'US'
ORDER BY short_name, level_set
Email string validation code in PL/SQL
Form validations for PL/SQL
--***PL/SQL code for Email validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;
mail_ch VARCHAR2(1);
result number; --:GR
BEGIN
t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;
-------------------------------------------------------------------------------------
IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;
WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;
IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN t_valid := 0; EXIT; END IF; END LOOP; --------------------------------------------------------------------------------------- --This is to check more than one '@' character present or not t_atpos := instr(:text_item_email,'@',1,2) ; IF t_atpos > 1 then
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present
t_atpos := instr(:text_item_email,'@',1) ;
IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one character is present between @ and '.' :GR
t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos-t_atpos<=1 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- --This is to check at least one character is present after '.' :GR t_pointpos := instr(:text_item_email,'.',1) ; IF t_totallen-t_pointpos<=0 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- END IF; if(t_valid=0) then message('Invalid Email'); result:=show_alert('alert_email'); raise form_trigger_failure; end if; END; ---***PL/SQL code for Name Validation***--- DECLARE t_valid NUMBER(1); t_totallen NUMBER(2); t_counter NUMBER(2):=0; i NUMBER(2) := 1; name_ch VARCHAR2(1); --name_ch_ascii NUMBER; result number; --:GR BEGIN t_totallen := LENGTH(:text_item_first_name); t_counter := t_totallen; i := 1; t_valid := 1; ------------------------------------------------------------------------------------- IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN t_valid := 0; ELSE --------------------------------------------------------------------------------------- --This is to check if each character in name lies in the valid ascii range t_counter := t_totallen; WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;
--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;
END LOOP;
---------------------------------------------------------------------------------------
END IF;
if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;
--***PL/SQL code for Email validation***---
DECLARE
t_valid NUMBER(1);
t_totallen NUMBER(2);
t_counter NUMBER(2):=0;
t_atpos NUMBER(2):= 1;
i NUMBER(2) := 1;
t_pointpos NUMBER(2):= 1;
mail_ch VARCHAR2(1);
result number; --:GR
BEGIN
t_totallen := LENGTH(:text_item_email);
t_counter := t_totallen;
i := 1;
t_valid := 1;
-------------------------------------------------------------------------------------
IF LENGTH(ltrim(rtrim(:text_item_email))) = 0 THEN
t_valid := 0;
ELSE
---------------------------------------------------------------------------------------
--This is to check special characters are present or not in the email ID
t_counter := t_totallen;
WHILE t_counter > 0
LOOP
mail_ch := substr(:text_item_email,i,1);
i := i+1;
t_counter := t_counter -1;
IF mail_ch IN (' ','!','#','$','%','^','&','*','(',')','-','','"',
'+','|','{','}','[',']',':','>','<','?','/','\','=') THEN t_valid := 0; EXIT; END IF; END LOOP; --------------------------------------------------------------------------------------- --This is to check more than one '@' character present or not t_atpos := instr(:text_item_email,'@',1,2) ; IF t_atpos > 1 then
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at minimum and at maximum only one '@' character present
t_atpos := instr(:text_item_email,'@',1) ;
IF t_atpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one '.' character present or not
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos IN (0,1) THEN
t_valid := 0;
END IF;
---------------------------------------------------------------------------------------
--This is to check at least one character is present between @ and '.' :GR
t_atpos := instr(:text_item_email,'@',1) ;
t_pointpos := instr(:text_item_email,'.',1) ;
IF t_pointpos-t_atpos<=1 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- --This is to check at least one character is present after '.' :GR t_pointpos := instr(:text_item_email,'.',1) ; IF t_totallen-t_pointpos<=0 THEN t_valid := 0; END IF; --------------------------------------------------------------------------------------- END IF; if(t_valid=0) then message('Invalid Email'); result:=show_alert('alert_email'); raise form_trigger_failure; end if; END; ---***PL/SQL code for Name Validation***--- DECLARE t_valid NUMBER(1); t_totallen NUMBER(2); t_counter NUMBER(2):=0; i NUMBER(2) := 1; name_ch VARCHAR2(1); --name_ch_ascii NUMBER; result number; --:GR BEGIN t_totallen := LENGTH(:text_item_first_name); t_counter := t_totallen; i := 1; t_valid := 1; ------------------------------------------------------------------------------------- IF LENGTH(ltrim(rtrim(:text_item_first_name))) = 0 THEN t_valid := 0; ELSE --------------------------------------------------------------------------------------- --This is to check if each character in name lies in the valid ascii range t_counter := t_totallen; WHILE t_counter > 0
LOOP
name_ch := upper(substr(:text_item_first_name,i,1));
--name_ch_ascii := convert(name_ch,'US7ASCII');
i := i+1;
t_counter := t_counter -1;
--IF name_ch_ascii not between 65 and 90 THEN
IF name_ch not between 'A' and 'Z' THEN
t_valid := 0;
EXIT;
END IF;
END LOOP;
---------------------------------------------------------------------------------------
END IF;
if(t_valid=0) then
message('Invalid First Name');
result:=show_alert('alert_first_name');
raise form_trigger_failure;
end if;
END;
Property to set in JDeveloper to make the VO substitution work locally.
In order for JDeveloper to recognize the vo substitution you need to proform below setup in JDEV.
Navaigation: Select the project, right click and go to project setting
> Configurations > Development > Runner
Add the following at the end of the "Java Options" strings
-Djbo.project=<Project Name with out .jpr extension>
eg: -Djbo.project=iSupplierPortal
Navaigation: Select the project, right click and go to project setting
> Configurations > Development > Runner
Add the following at the end of the "Java Options" strings
-Djbo.project=<Project Name with out .jpr extension>
eg: -Djbo.project=iSupplierPortal
How to personalize the embedded notification content sent via e-mail?
--------------------------------------------------------------------------------------------------
SSHR application generated approval notification with notification containing all the information of the transaction.
How to personalize the embedded notification content sent via e-mail?
---------------------------------------------------------------------
Solution:
To implement the solution, please execute the following steps:
1 - Make profile Personalize Self-Service Defn is set to Yes at user level for the user who is going to perform personalization. This profile need not be set for all other users.
2 - Login to the application , click "workflow user web applications" responsibility.
3 - Start Worklist/Notifications function.
3 - Access the notification.
4 - Perform a SITE level personalization.
Now, in all future email notifications, the personalized content is sent to the approver via email.
---------------------------------------------------------------------------------------------------
SSHR application generated approval notification with notification containing all the information of the transaction.
How to personalize the embedded notification content sent via e-mail?
---------------------------------------------------------------------
Solution:
To implement the solution, please execute the following steps:
1 - Make profile Personalize Self-Service Defn is set to Yes at user level for the user who is going to perform personalization. This profile need not be set for all other users.
2 - Login to the application , click "workflow user web applications" responsibility.
3 - Start Worklist/Notifications function.
3 - Access the notification.
4 - Perform a SITE level personalization.
Now, in all future email notifications, the personalized content is sent to the approver via email.
---------------------------------------------------------------------------------------------------
Extending CO to execute custom SQL queries for validation purpose
It is not recommended to use JDBC Connections from within Controllers.
However, sometimes from within our extended controllers, we have a need to fire some SQL Statements.
You have various options to achieve this, and these are discussed with code samples in this article below.
Option 1
Create a new view object and attach that view object to the Application Module of that page.
Then call the SQL by executing query on "custom view object" from "extended application module"
Cons for Option 1
In this case you will have to extend the Application Module, so as to attach it the Custom View Object.
If the Application Module is Root AM, then such AM may not be extensible.
Option 2
Get the database connection within controller and call SQL Statements or PL/SQL APIs by doing one of the two below
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)oapagecontext.getRootApplicationModule().getOADBTransaction();
CallableStatement callablestatement = oadbtransactionimpl.createCallableStatement("begin :1 := xx_get_Fnd_user_desc(:2); end; ", 1);
OR
OADBTransaction oadbtransaction = (OADBTransaction)oapagecontext.getApplicationModule(oawebbean).getTransaction();
java.sql.Connection connection = oadbtransaction.getJdbcConnection();
Cons for Option 2
Getting JDBC connection inside the controller is not recommended.
Option 3 - [ Recommened ]
Create the view object on the fly from SQL Statement. This view object will be automatically attached to the Application Module of the page.
In this article, we will see how you can do this.
Lets say our requirement is that, in the extended controller, for a given FND_USER.USER_NAME - we need to find FND_USER.DESCRIPTION
Overview Our steps will be [For Option 3]
1. Extend the controller
2. In the extended controller import these two classes as shown below
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;
3. Build the SQL Statement that you wish to execute in a String variable.
IMPORTANT- Ensure that you use Bind Variables as shown in this example below
4. Execute the Query on the View Object
5. Get the desired results from the rows of this view object
package oracle.apps.fnd.framework.toolbox.tutorial.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
//-----------------------------------------------------
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;
/**
* Controller for oracle.apps.fnd.framework.toolbox.tutorial.webui.HelloWorldPG
* page.
*/
public class HelloWorldMainCO extends OAControllerImpl
{
// Required for Applications source control
public static final String RCS_ID="$Header: HelloWorldMainCO.java 115.6 2004/01/19 10:14:57 atgopxxOnTheFlyVOQuery noship $";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "oracle.apps.fnd.framework.toolbox.tutorial.webui");
/**
* Layout and page setup logic for region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
//First get the Application Module
OAApplicationModule oam = pageContext.getApplicationModule(webBean);
//Lets say I need to get the description of FND_USER Names GUEST
String sWhereClauseValue = "GUEST" ;
//Build the select statement for this on the fly view object
String xxOnTheFlyVOQuery = "select description xxdesc from fnd_user WHERE user_"+
"_name = :1 ";
//First see if this VO is already attached to view object
ViewObject xxOnTheFlyViewObject = oam.findViewObject("xxFNDUserDescVO");
if(xxOnTheFlyViewObject == null)
xxOnTheFlyViewObject = oam.createViewObjectFromQueryStmt("xxFNDUserDescVO", xxOnTheFlyVOQuery);
//By now we are sure that the view object exists
xxOnTheFlyViewObject.setWhereClauseParams(null);
//Set the where clause
xxOnTheFlyViewObject.setWhereClauseParam(0, sWhereClauseValue);
xxOnTheFlyViewObject.executeQuery();
oracle.jbo.Row row = xxOnTheFlyViewObject.first();
//get the value of description column from View Object record returned
if(row != null)
{
String mSupHierarchyUsed = row.getAttribute(0).toString();
System.out.println("Result from Dynamic VO is =>" + mSupHierarchyUsed );
}
//Remove the view object, as this is no longer required
xxOnTheFlyViewObject.remove();
}
/**
* Procedure to handle form submissions for form elements in
* region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
if (pageContext.getParameter("Go") != null)
{
// NEVER hard-code a message like this in your application. This is just
// shown for the sake of simplicity in this first lesson. In the next
// lessons you'll learn how to define translateable messages.
String userContent = pageContext.getParameter("HelloName");
String message = "Hello, " + userContent + "!";
throw new OAException(message, OAException.INFORMATION);
}
}
}
Example for Option 2:
package oracle.apps.pos.isp.server;
import java.sql.*;
import java.util.Vector;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.functionSecurity.FunctionSecurity;
import oracle.jbo.server.DBTransaction;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OraclePreparedStatement;
public class PosServerUtil
{
public static final String RCS_ID = "$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $";
public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $", "oracle.apps.pos.isp.server");
public static final String WHERE_CLAUSE_BEG_DELIMITER = "###";
public static final String WHERE_CLAUSE_END_DELIMITER = "$$$";
public static String ifContractEnabled = null;
public static String ifEresEnabled = null;
public static final String securePOString = "SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1";
public static Object[] getParamArray(Vector vector)
{
int i = vector.size();
Object aobj[] = new Object[i];
for(int j = 0; j < s2 = "Y"> 0)
{
CallableStatement callablestatement = null;
ResultSet resultset = null;
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1");
try
{
Vector vector = new Vector();
vector.addElement(s);
String s3 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
String s4 = getSecureWhereClause(vector, oadbtransaction, s3);
if(s4 != null && s4.length() > 0)
{
stringbuffer.append(" AND ").append(s4);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" s2 = "Y" s3 =" new" s4 =" new" s5 = "VENDOR_ID, VENDOR_SITE_ID" s6 = "I"> 0 && s1 != null && s1.length() > 0)
{
if("ASN".equals(s1))
{
s6 = "S";
s3 = "SHIPMENT_NUM";
s4 = "POS_VIEW_ASN";
} else
if("INVOICE".equals(s1))
{
s3 = "INVOICE_ID";
s4 = "AP_INVOICES_ALL";
} else
if("PAYMENT".equals(s1))
{
s3 = "CHECK_ID";
s4 = "AP_CHECKS_ALL";
} else
if("RFQ".equals(s1))
{
s3 = "PO_HEADER_ID";
s4 = "POS_PO_RFQ_HEADERS_V";
s5 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
} else
if("RECEIPT".equals(s1))
{
s3 = "SHIPMENT_HEADER_ID";
s4 = "POS_RECEIPT_HEADERS_V";
s5 = "VENDOR_ID";
}
CallableStatement callablestatement = null;
ResultSet resultset = null;
try
{
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM ");
stringbuffer.append(s4).append(" WHERE ").append(s3);
stringbuffer.append(" = :1");
Vector vector = new Vector();
vector.addElement(s);
String s7 = getSecureWhereClause(vector, oadbtransaction, s5);
if(s7 != null && s7.length() > 0)
{
stringbuffer.append(" AND ").append(s7);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < j ="="" resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" functionsecurity =" oadbtransaction.getFunctionSecurity();" function =" functionsecurity.getFunction(" function1 =" functionsecurity.getFunction(" function2 =" functionsecurity.getFunction(" function3 =" functionsecurity.getFunction(" function4 =" functionsecurity.getFunction(" function5 =" functionsecurity.getFunction(" integer =" new" integer ="="" null =" null" s1 = "" s3 = "" i =" oadbtransaction.getUserId();" callablestatement =" null;" callablestatement1 =" null;" resultset =" null;" resultset1 =" null;" s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" responsibility_id =" ?" attribute_application_id =" 177" s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" web_user_id =" ?" e_code =" ?" attribute_application_id =" 177" responsibility_id =" ?" attribute_application_id =" 177" attribute_code =" ?" number_value =" -9999" callablestatement =" oadbtransaction.createCallableStatement(s4," resultset =" callablestatement.executeQuery();" s6 = "" s2 =" resultset.getString(" s6 = "VENDOR_ID" s6 = "VENDOR_CONTACT_ID" s6 = "VENDOR_SITE_ID" s7 = "" s8 = "" callablestatement1 =" oadbtransaction.createCallableStatement(s5," resultset1 =" callablestatement1.executeQuery();" flag =" true;" j =" 0;" vector1 =" new" s9 =" resultset1.getString(" flag =" false;">= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" s4 =" null;" s4 =" getDocumentSecurityClause(oadbtransaction," s5 =" null;" s5 =" getOperatingUnitSecureClause(oadbtransaction," s5 =" getInventoryOrgSecureClause(oadbtransaction," s4 ="="" s4 =" s5;" s4 =" s4" obj =" null;" obj1 =" null;" s2 =" null;" s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; " callablestatement =" null;" j =" oadbtransaction.getOrgId();" k =" oadbtransaction.getEmployeeId();" s4 =" Integer.toString(k);" callablestatement =" oadbtransaction.createCallableStatement(s3," s =" callablestatement.getString(6);" s1 =" callablestatement.getString(7);" s2 =" callablestatement.getString(8);" s4 =" null;" s5 = "a#$$#1" s7 =" getDocumentSecurityClause(oadbtransaction," k =" oadbtransaction.getEmployeeId();" s8 =" Integer.toString(k);" stringbuffer =" new" l =" 0;" i1 =" s7.indexOf(s5,"> 0; i1 = s7.indexOf(s5, l))
{
stringbuffer.append(s7.substring(l, i1));
stringbuffer.append(":" + Integer.toString(i++));
vector.addElement(s8);
l = i1 + s5.length();
}
stringbuffer.append(s7.substring(l));
s4 = stringbuffer.toString();
}
}
if(s2 != null && !"".equals(s2))
{
String s6 = null;
if("OU".equals(s2))
{
s6 = getOperatingUnitSecureClause(oadbtransaction, s3);
} else
if("INV".equals(s2))
{
s6 = getInventoryOrgSecureClause(oadbtransaction, s3);
}
if(s6 != null && !"".equals(s6))
{
if(s4 == null)
{
s4 = s6;
} else
{
s4 = s4 + " and " + s6;
}
}
}
return s4;
}
public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j)
{
Object obj = null;
Object obj1 = null;
String s2 = null;
if(j > 0)
{
String s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
int k = oadbtransaction.getEmployeeId();
String s4 = Integer.toString(k);
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s4);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s = callablestatement.getString(6);
String s1 = callablestatement.getString(7);
if(!"U".equals(s) && !"E".equals(s))
{
s2 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s2;
}
public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j, String s)
{
Object obj = null;
Object obj1 = null;
String s3 = null;
if(j > 0)
{
String s4 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s1 = callablestatement.getString(6);
String s2 = callablestatement.getString(7);
if(!"U".equals(s1) && !"E".equals(s1))
{
s3 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s3;
}
public static String getOperatingUnitSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from per_organization_list " + "where security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))";
}
return s1;
}
public static String getInventoryOrgSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception _ex) { }
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from " + "org_organization_definitions where operating_unit in (select organization_id fro" +
"m "
+ "per_organization_list where security_profile_id = " + "fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')))";
}
return s1;
}
public static String getSupplierView(OADBTransaction oadbtransaction)
{
String s = "N";
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
if(functionsecurity.testFunction(function) functionsecurity.testFunction(function1) functionsecurity.testFunction(function2) functionsecurity.testFunction(function3))
{
s = "Y";
}
}
catch(Exception _ex) { }
return s;
}
public static String getBuyerView(String s, OADBTransaction oadbtransaction)
{
String s1 = s;
if("Y".equals(s1))
{
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_INTERNAL_HOME");
if(functionsecurity.testFunction(function))
{
s1 = "Y";
} else
{
s1 = "N";
}
}
catch(Exception _ex) { }
} else
{
s1 = "N";
}
return s1;
}
public static String getSupplierSecureClause(Vector vector, OADBTransaction oadbtransaction, String s)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
oracle.apps.fnd.functionSecurity.Function function5 = functionsecurity.getFunction("POS_VIEW_ORDER");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4) && !functionsecurity.testFunction(function5))
{
return "null = null";
}
String s1 = "";
String s3 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECURI" +
"TY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id = 177 A" +
"ND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s6 = "";
String s2 = resultset.getString("attribute_code");
if(s2 != null)
{
if(s2.equals("ICX_SUPPLIER_ORG_ID") && s.indexOf("SUPPLIER_ID") != -1)
{
s6 = "SUPPLIER_ID";
} else
if(s2.equals("ICX_SUPPLIER_CONTACT_ID") && s.indexOf("VENDOR_CONTACT_ID") != -1)
{
s6 = "VENDOR_CONTACT_ID";
} else
if(s2.equals("ICX_SUPPLIER_SITE_ID") && s.indexOf("SUPPLIER_SITE_ID") != -1)
{
s6 = "SUPPLIER_SITE_ID";
}
if(s6 != "")
{
String s7 = "";
String s8 = "";
callablestatement1 = oadbtransaction.createCallableStatement(s5, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s2);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s2);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector1 = new Vector();
while(resultset1.next())
{
String s9 = resultset1.getString("sec_value");
if(s9 != null && s9 != "")
{
flag = false;
if(!s9.equals("-9999"))
{
vector1.addElement(s9);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" i =" 0;"> 0 && !s.equals("null"))
{
int j = Integer.parseInt(s);
String s2 = "select org_id from po_headers_all where po_header_id = :1";
if(s1 != null && s1.length() > 0 && !s1.equals("null"))
{
j = Integer.parseInt(s1);
s2 = "select org_id from po_releases_all where po_release_id = :1";
}
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
callablestatement.setInt(1, j);
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return i;
}
private static String isNumber(String s, String s1)
{
if(s1.equals("NUMBER"))
{
return s;
} else
{
return "'" + s + "'";
}
}
public static String wrapValue(String s)
{
return "###" + s + "$$$";
}
public static final String replaceNull(Object obj)
{
if(obj == null obj.equals("null"))
{
return "";
} else
{
return obj.toString();
}
}
public static final int replaceInteger(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0;
} else
{
return Integer.parseInt(obj.toString());
}
}
public static final float replaceFloat(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0F;
} else
{
Float float1 = new Float(obj.toString());
return float1.floatValue();
}
}
public static final double replaceDouble(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0D;
} else
{
Double double1 = new Double(obj.toString());
return double1.doubleValue();
}
}
public static String ifContractsInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
StringBuffer stringbuffer = new StringBuffer(" BEGIN");
stringbuffer.append(" :1 := PO_CONTERMS_UTL_GRP.is_contracts_enabled();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifContractEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifContractEnabled;
}
public static String ifEresInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
String s = "{call PO_ERECORDS_PVT.ERECORDS_ENABLED(:1) }";
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(s, 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifEresEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifEresEnabled;
}
public static String ifXdoInstalled(String s, OADBTransaction oadbtransaction)
throws SQLException
{
String s1 = null;
StringBuffer stringbuffer = new StringBuffer(" BEGIN ");
stringbuffer.append(" fnd_client_info.set_org_context(:1) ; ");
stringbuffer.append(" :2 := PO_COMMUNICATION_PVT.po_communication_profile();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(2, 12, 0, 1);
oraclecallablestatement.setString(1, s);
oraclecallablestatement.executeQuery();
s1 = oraclecallablestatement.getString(2);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return s1;
}
public static String[] checkNegotiations(OADBTransaction oadbtransaction, String s)
throws SQLException
{
String as[] = new String[2];
String s1 = "declare x boolean; BEGIN x := FND_INSTALLATION.GET_APP_INFO ( :1, :" +
"2, :3, :4 ); END; "
;
String s2 = "BEGIN pon_auction_interface_pkg.Get_PO_Negotiation_Link ( :1, :2, " +
" :3, :4, :5, :6, :7 ); END; "
;
String s3 = null;
String s4 = null;
String s5 = "";
try
{
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s1, 1);
callablestatement.setString(1, "PON");
callablestatement.registerOutParameter(2, 12);
callablestatement.registerOutParameter(3, 12);
callablestatement.registerOutParameter(4, 12);
callablestatement.execute();
s5 = callablestatement.getString(2);
callablestatement.close();
}
catch(Exception exception)
{
exception.printStackTrace();
}
CallableStatement callablestatement1 = null;
if(s5.equals("I"))
{
try
{
callablestatement1 = oadbtransaction.createCallableStatement(s2, 1);
callablestatement1.setInt(1, Integer.parseInt(s));
callablestatement1.registerOutParameter(2, 4);
callablestatement1.registerOutParameter(3, 12);
callablestatement1.registerOutParameter(4, 12);
callablestatement1.registerOutParameter(5, 4);
callablestatement1.registerOutParameter(6, 12);
callablestatement1.registerOutParameter(7, 12);
callablestatement1.execute();
String s6 = String.valueOf(callablestatement1.getInt(2));
s4 = callablestatement1.getString(3);
s3 = callablestatement1.getString(4);
callablestatement1.getInt(5);
callablestatement1.getString(6);
callablestatement1.getString(7);
s3 = s3 + "&AuctionId=" + "{!!" + oadbtransaction.encrypt(s6) + "&from=ISP";
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
as[0] = s3;
as[1] = s4;
return as;
}
public static String checkShipments(OADBTransaction oadbtransaction, String s, String s1)
throws SQLException
{
String s2 = "";
if(s1 != null && s1.length() > 0)
{
String s3 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_release_id= :2 ; end;"
;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s3, 1);
try
{
callablestatement.registerOutParameter(1, 4);
callablestatement.setInt(2, Integer.parseInt(s1));
callablestatement.execute();
s2 = String.valueOf(callablestatement.getInt(1));
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
callablestatement.close();
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
} else
if(s != null)
{
String s4 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_header_id= :2 ; end;"
;
CallableStatement callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
try
{
callablestatement1.registerOutParameter(1, 4);
callablestatement1.setInt(2, Integer.parseInt(s));
callablestatement1.execute();
s2 = String.valueOf(callablestatement1.getInt(1));
}
catch(Exception exception4)
{
exception4.printStackTrace();
}
finally
{
try
{
callablestatement1.close();
}
catch(Exception exception5)
{
exception5.printStackTrace();
}
}
}
return s2;
}
public static String getGASecureWhereClause(Vector vector, OADBTransaction oadbtransaction)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4))
{
return "null = null";
}
String s = "";
String s2 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s3 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s4 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECU" +
"RITY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id =" +
" 177 AND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s5 = "";
String s1 = resultset.getString("attribute_code");
if(s1 != null)
{
if(s1.equals("ICX_SUPPLIER_ORG_ID"))
{
s5 = "VENDOR_ID";
} else
if(s1.equals("ICX_SUPPLIER_CONTACT_ID"))
{
s5 = "VENDOR_CONTACT_ID";
} else
if(s1.equals("ICX_SUPPLIER_SITE_ID"))
{
s5 = "VENDOR_SITE_ID";
}
if(s5 != "")
{
String s6 = "";
String s7 = "";
Vector vector1 = new Vector();
callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s1);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s1);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector2 = new Vector();
while(resultset1.next())
{
String s8 = resultset1.getString("sec_value");
if(s8 != null && s8 != "")
{
flag = false;
if(!s8.equals("-9999"))
{
vector2.addElement(s8);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < 254)
{
for(int k = 0; k < vector2.size(); k++)
{
if(s5.equals("VENDOR_SITE_ID"))
{
vector1.addElement(vector2.elementAt(k));
}
vector.addElement(vector2.elementAt(k));
if(s6 == "")
{
s6 = s6 + ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}
if(flag)
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " = null";
}
if(s6 != "" && !s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s6 + ")";
} else
if(s6 != "" && s5.equals("VENDOR_SITE_ID"))
{
if(s2 != "")
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s6 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')";
s6 = "";
if(!vector1.isEmpty())
{
for(int l = 0; l < vector1.size(); l++)
{
vector.addElement(vector1.elementAt(l));
if(s6 == "")
{
s6 = ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}
s2 = s2 + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in ( select PGA.po_header_id from " +
"PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH where PGA.po_header_id = POH.po_h" +
"eader_id and PGA.enabled_flag = 'Y' and POH.global_agreement_flag = 'Y' an" +
"d PGA.vendor_site_id in ("
+ s6 + "))))";
}
}
} else
{
vector.addElement(Integer.toString(i));
vector.addElement(s1);
String s9 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" +
"M ak_web_user_sec_attr_values WHERE web_user_id = :"
+ (vector.size() - 1) + " AND attribute_code = :" + vector.size() + " AND attribute_application_id = 177 ";
if(s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s9 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')" + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in (" + " select PGA.po_header_id" + " from PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH" + " where PGA.po_header_id = POH.po_header_id" + " and PGA.enabled_flag = 'Y'" + " and POH.global_agreement_flag = 'Y'" + " and PGA.vendor_site_id in (" + s9 + "))))";
} else
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s9 + ")";
}
}
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(resultset != null)
{
resultset.close();
}
if(callablestatement != null)
{
callablestatement.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
}
if(s2 == null s2.trim().length() == 0)
{
return " null = null ";
} else
{
return s2;
}
}
public PosServerUtil()
{
}
}
However, sometimes from within our extended controllers, we have a need to fire some SQL Statements.
You have various options to achieve this, and these are discussed with code samples in this article below.
Option 1
Create a new view object and attach that view object to the Application Module of that page.
Then call the SQL by executing query on "custom view object" from "extended application module"
Cons for Option 1
In this case you will have to extend the Application Module, so as to attach it the Custom View Object.
If the Application Module is Root AM, then such AM may not be extensible.
Option 2
Get the database connection within controller and call SQL Statements or PL/SQL APIs by doing one of the two below
OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)oapagecontext.getRootApplicationModule().getOADBTransaction();
CallableStatement callablestatement = oadbtransactionimpl.createCallableStatement("begin :1 := xx_get_Fnd_user_desc(:2); end; ", 1);
OR
OADBTransaction oadbtransaction = (OADBTransaction)oapagecontext.getApplicationModule(oawebbean).getTransaction();
java.sql.Connection connection = oadbtransaction.getJdbcConnection();
Cons for Option 2
Getting JDBC connection inside the controller is not recommended.
Option 3 - [ Recommened ]
Create the view object on the fly from SQL Statement. This view object will be automatically attached to the Application Module of the page.
In this article, we will see how you can do this.
Lets say our requirement is that, in the extended controller, for a given FND_USER.USER_NAME - we need to find FND_USER.DESCRIPTION
Overview Our steps will be [For Option 3]
1. Extend the controller
2. In the extended controller import these two classes as shown below
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;
3. Build the SQL Statement that you wish to execute in a String variable.
IMPORTANT- Ensure that you use Bind Variables as shown in this example below
4. Execute the Query on the View Object
5. Get the desired results from the rows of this view object
package oracle.apps.fnd.framework.toolbox.tutorial.webui;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
//-----------------------------------------------------
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.jbo.ViewObject;
/**
* Controller for oracle.apps.fnd.framework.toolbox.tutorial.webui.HelloWorldPG
* page.
*/
public class HelloWorldMainCO extends OAControllerImpl
{
// Required for Applications source control
public static final String RCS_ID="$Header: HelloWorldMainCO.java 115.6 2004/01/19 10:14:57 atgopxxOnTheFlyVOQuery noship $";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "oracle.apps.fnd.framework.toolbox.tutorial.webui");
/**
* Layout and page setup logic for region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
//First get the Application Module
OAApplicationModule oam = pageContext.getApplicationModule(webBean);
//Lets say I need to get the description of FND_USER Names GUEST
String sWhereClauseValue = "GUEST" ;
//Build the select statement for this on the fly view object
String xxOnTheFlyVOQuery = "select description xxdesc from fnd_user WHERE user_"+
"_name = :1 ";
//First see if this VO is already attached to view object
ViewObject xxOnTheFlyViewObject = oam.findViewObject("xxFNDUserDescVO");
if(xxOnTheFlyViewObject == null)
xxOnTheFlyViewObject = oam.createViewObjectFromQueryStmt("xxFNDUserDescVO", xxOnTheFlyVOQuery);
//By now we are sure that the view object exists
xxOnTheFlyViewObject.setWhereClauseParams(null);
//Set the where clause
xxOnTheFlyViewObject.setWhereClauseParam(0, sWhereClauseValue);
xxOnTheFlyViewObject.executeQuery();
oracle.jbo.Row row = xxOnTheFlyViewObject.first();
//get the value of description column from View Object record returned
if(row != null)
{
String mSupHierarchyUsed = row.getAttribute(0).toString();
System.out.println("Result from Dynamic VO is =>" + mSupHierarchyUsed );
}
//Remove the view object, as this is no longer required
xxOnTheFlyViewObject.remove();
}
/**
* Procedure to handle form submissions for form elements in
* region.
* @param pageContext the current OA page context
* @param webBean the web bean corresponding to the region
*/
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
if (pageContext.getParameter("Go") != null)
{
// NEVER hard-code a message like this in your application. This is just
// shown for the sake of simplicity in this first lesson. In the next
// lessons you'll learn how to define translateable messages.
String userContent = pageContext.getParameter("HelloName");
String message = "Hello, " + userContent + "!";
throw new OAException(message, OAException.INFORMATION);
}
}
}
Example for Option 2:
package oracle.apps.pos.isp.server;
import java.sql.*;
import java.util.Vector;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.functionSecurity.FunctionSecurity;
import oracle.jbo.server.DBTransaction;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OraclePreparedStatement;
public class PosServerUtil
{
public static final String RCS_ID = "$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $";
public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion("$Header: PosServerUtil.java 115.45.11510.1 2004/09/28 09:35:34 amkalia ship $", "oracle.apps.pos.isp.server");
public static final String WHERE_CLAUSE_BEG_DELIMITER = "###";
public static final String WHERE_CLAUSE_END_DELIMITER = "$$$";
public static String ifContractEnabled = null;
public static String ifEresEnabled = null;
public static final String securePOString = "SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1";
public static Object[] getParamArray(Vector vector)
{
int i = vector.size();
Object aobj[] = new Object[i];
for(int j = 0; j < s2 = "Y"> 0)
{
CallableStatement callablestatement = null;
ResultSet resultset = null;
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM po_headers_all WHERE po_header_id = :1");
try
{
Vector vector = new Vector();
vector.addElement(s);
String s3 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
String s4 = getSecureWhereClause(vector, oadbtransaction, s3);
if(s4 != null && s4.length() > 0)
{
stringbuffer.append(" AND ").append(s4);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" s2 = "Y" s3 =" new" s4 =" new" s5 = "VENDOR_ID, VENDOR_SITE_ID" s6 = "I"> 0 && s1 != null && s1.length() > 0)
{
if("ASN".equals(s1))
{
s6 = "S";
s3 = "SHIPMENT_NUM";
s4 = "POS_VIEW_ASN";
} else
if("INVOICE".equals(s1))
{
s3 = "INVOICE_ID";
s4 = "AP_INVOICES_ALL";
} else
if("PAYMENT".equals(s1))
{
s3 = "CHECK_ID";
s4 = "AP_CHECKS_ALL";
} else
if("RFQ".equals(s1))
{
s3 = "PO_HEADER_ID";
s4 = "POS_PO_RFQ_HEADERS_V";
s5 = "VENDOR_ID, VENDOR_SITE_ID, VENDOR_CONTACT_ID";
} else
if("RECEIPT".equals(s1))
{
s3 = "SHIPMENT_HEADER_ID";
s4 = "POS_RECEIPT_HEADERS_V";
s5 = "VENDOR_ID";
}
CallableStatement callablestatement = null;
ResultSet resultset = null;
try
{
StringBuffer stringbuffer = new StringBuffer("SELECT COUNT(*) FROM ");
stringbuffer.append(s4).append(" WHERE ").append(s3);
stringbuffer.append(" = :1");
Vector vector = new Vector();
vector.addElement(s);
String s7 = getSecureWhereClause(vector, oadbtransaction, s5);
if(s7 != null && s7.length() > 0)
{
stringbuffer.append(" AND ").append(s7);
}
int i = -1;
callablestatement = oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
for(int j = 0; j < j ="="" resultset =" callablestatement.executeQuery();" i =" resultset.getInt(1);" i ="="" s2 = "N" s2 = "Y" functionsecurity =" oadbtransaction.getFunctionSecurity();" function =" functionsecurity.getFunction(" function1 =" functionsecurity.getFunction(" function2 =" functionsecurity.getFunction(" function3 =" functionsecurity.getFunction(" function4 =" functionsecurity.getFunction(" function5 =" functionsecurity.getFunction(" integer =" new" integer ="="" null =" null" s1 = "" s3 = "" i =" oadbtransaction.getUserId();" callablestatement =" null;" callablestatement1 =" null;" resultset =" null;" resultset1 =" null;" s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" responsibility_id =" ?" attribute_application_id =" 177" s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" web_user_id =" ?" e_code =" ?" attribute_application_id =" 177" responsibility_id =" ?" attribute_application_id =" 177" attribute_code =" ?" number_value =" -9999" callablestatement =" oadbtransaction.createCallableStatement(s4," resultset =" callablestatement.executeQuery();" s6 = "" s2 =" resultset.getString(" s6 = "VENDOR_ID" s6 = "VENDOR_CONTACT_ID" s6 = "VENDOR_SITE_ID" s7 = "" s8 = "" callablestatement1 =" oadbtransaction.createCallableStatement(s5," resultset1 =" callablestatement1.executeQuery();" flag =" true;" j =" 0;" vector1 =" new" s9 =" resultset1.getString(" flag =" false;">= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" s4 =" null;" s4 =" getDocumentSecurityClause(oadbtransaction," s5 =" null;" s5 =" getOperatingUnitSecureClause(oadbtransaction," s5 =" getInventoryOrgSecureClause(oadbtransaction," s4 ="="" s4 =" s5;" s4 =" s4" obj =" null;" obj1 =" null;" s2 =" null;" s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; " callablestatement =" null;" j =" oadbtransaction.getOrgId();" k =" oadbtransaction.getEmployeeId();" s4 =" Integer.toString(k);" callablestatement =" oadbtransaction.createCallableStatement(s3," s =" callablestatement.getString(6);" s1 =" callablestatement.getString(7);" s2 =" callablestatement.getString(8);" s4 =" null;" s5 = "a#$$#1" s7 =" getDocumentSecurityClause(oadbtransaction," k =" oadbtransaction.getEmployeeId();" s8 =" Integer.toString(k);" stringbuffer =" new" l =" 0;" i1 =" s7.indexOf(s5,"> 0; i1 = s7.indexOf(s5, l))
{
stringbuffer.append(s7.substring(l, i1));
stringbuffer.append(":" + Integer.toString(i++));
vector.addElement(s8);
l = i1 + s5.length();
}
stringbuffer.append(s7.substring(l));
s4 = stringbuffer.toString();
}
}
if(s2 != null && !"".equals(s2))
{
String s6 = null;
if("OU".equals(s2))
{
s6 = getOperatingUnitSecureClause(oadbtransaction, s3);
} else
if("INV".equals(s2))
{
s6 = getInventoryOrgSecureClause(oadbtransaction, s3);
}
if(s6 != null && !"".equals(s6))
{
if(s4 == null)
{
s4 = s6;
} else
{
s4 = s4 + " and " + s6;
}
}
}
return s4;
}
public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j)
{
Object obj = null;
Object obj1 = null;
String s2 = null;
if(j > 0)
{
String s3 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
int k = oadbtransaction.getEmployeeId();
String s4 = Integer.toString(k);
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s4);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s = callablestatement.getString(6);
String s1 = callablestatement.getString(7);
if(!"U".equals(s) && !"E".equals(s))
{
s2 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s2;
}
public static String getDocumentSecurityClause(OADBTransaction oadbtransaction, int i, int j, String s)
{
Object obj = null;
Object obj1 = null;
String s3 = null;
if(j > 0)
{
String s4 = "BEGIN pos_util_pkg.Retrieve_Doc_Security (:1,:2,:3,:4,:5,:6,:7,:8); END; ";
CallableStatement callablestatement = null;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setString(1, "QRSLT");
callablestatement.setString(2, "AGENT_ID");
callablestatement.setString(3, s);
callablestatement.setInt(4, i);
callablestatement.setInt(5, j);
callablestatement.registerOutParameter(6, 12);
callablestatement.registerOutParameter(7, 12);
callablestatement.registerOutParameter(8, 12);
callablestatement.execute();
String s1 = callablestatement.getString(6);
String s2 = callablestatement.getString(7);
if(!"U".equals(s1) && !"E".equals(s1))
{
s3 = callablestatement.getString(8);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return s3;
}
public static String getOperatingUnitSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from per_organization_list " + "where security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))";
}
return s1;
}
public static String getInventoryOrgSecureClause(OADBTransaction oadbtransaction, String s)
{
String s1 = null;
String s2 = "select count(*) from (select organization_id from per_organization_list where se" +
"curity_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))"
;
int i = 0;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception _ex) { }
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
if(i > 0)
{
s1 = s + " in (select organization_id from " + "org_organization_definitions where operating_unit in (select organization_id fro" +
"m "
+ "per_organization_list where security_profile_id = " + "fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')))";
}
return s1;
}
public static String getSupplierView(OADBTransaction oadbtransaction)
{
String s = "N";
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
if(functionsecurity.testFunction(function) functionsecurity.testFunction(function1) functionsecurity.testFunction(function2) functionsecurity.testFunction(function3))
{
s = "Y";
}
}
catch(Exception _ex) { }
return s;
}
public static String getBuyerView(String s, OADBTransaction oadbtransaction)
{
String s1 = s;
if("Y".equals(s1))
{
try
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_INTERNAL_HOME");
if(functionsecurity.testFunction(function))
{
s1 = "Y";
} else
{
s1 = "N";
}
}
catch(Exception _ex) { }
} else
{
s1 = "N";
}
return s1;
}
public static String getSupplierSecureClause(Vector vector, OADBTransaction oadbtransaction, String s)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
oracle.apps.fnd.functionSecurity.Function function5 = functionsecurity.getFunction("POS_VIEW_ORDER");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4) && !functionsecurity.testFunction(function5))
{
return "null = null";
}
String s1 = "";
String s3 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s4 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s5 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECURI" +
"TY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id = 177 A" +
"ND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s4, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s6 = "";
String s2 = resultset.getString("attribute_code");
if(s2 != null)
{
if(s2.equals("ICX_SUPPLIER_ORG_ID") && s.indexOf("SUPPLIER_ID") != -1)
{
s6 = "SUPPLIER_ID";
} else
if(s2.equals("ICX_SUPPLIER_CONTACT_ID") && s.indexOf("VENDOR_CONTACT_ID") != -1)
{
s6 = "VENDOR_CONTACT_ID";
} else
if(s2.equals("ICX_SUPPLIER_SITE_ID") && s.indexOf("SUPPLIER_SITE_ID") != -1)
{
s6 = "SUPPLIER_SITE_ID";
}
if(s6 != "")
{
String s7 = "";
String s8 = "";
callablestatement1 = oadbtransaction.createCallableStatement(s5, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s2);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s2);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector1 = new Vector();
while(resultset1.next())
{
String s9 = resultset1.getString("sec_value");
if(s9 != null && s9 != "")
{
flag = false;
if(!s9.equals("-9999"))
{
vector1.addElement(s9);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < k =" 0;" s7 ="="" s7 =" s7" s7 =" s7" s3 =" s3" s3 =" s3" s3 =" s3" s3 =" s3" s10 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" web_user_id =" :" attribute_code =" :" attribute_application_id =" 177" s3 =" s3" s3 =" s3" s3 ="="" null =" null" i =" 0;"> 0 && !s.equals("null"))
{
int j = Integer.parseInt(s);
String s2 = "select org_id from po_headers_all where po_header_id = :1";
if(s1 != null && s1.length() > 0 && !s1.equals("null"))
{
j = Integer.parseInt(s1);
s2 = "select org_id from po_releases_all where po_release_id = :1";
}
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s2, 1);
ResultSet resultset = null;
try
{
callablestatement.setInt(1, j);
resultset = callablestatement.executeQuery();
if(resultset.next())
{
i = resultset.getInt(1);
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset != null)
{
resultset.close();
}
}
catch(Exception _ex) { }
try
{
if(callablestatement != null)
{
callablestatement.close();
}
}
catch(Exception _ex) { }
}
}
return i;
}
private static String isNumber(String s, String s1)
{
if(s1.equals("NUMBER"))
{
return s;
} else
{
return "'" + s + "'";
}
}
public static String wrapValue(String s)
{
return "###" + s + "$$$";
}
public static final String replaceNull(Object obj)
{
if(obj == null obj.equals("null"))
{
return "";
} else
{
return obj.toString();
}
}
public static final int replaceInteger(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0;
} else
{
return Integer.parseInt(obj.toString());
}
}
public static final float replaceFloat(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0F;
} else
{
Float float1 = new Float(obj.toString());
return float1.floatValue();
}
}
public static final double replaceDouble(Object obj)
{
if(obj == null obj.equals("null"))
{
return 0.0D;
} else
{
Double double1 = new Double(obj.toString());
return double1.doubleValue();
}
}
public static String ifContractsInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
StringBuffer stringbuffer = new StringBuffer(" BEGIN");
stringbuffer.append(" :1 := PO_CONTERMS_UTL_GRP.is_contracts_enabled();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifContractEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifContractEnabled;
}
public static String ifEresInstalled(OADBTransaction oadbtransaction)
throws SQLException
{
String s = "{call PO_ERECORDS_PVT.ERECORDS_ENABLED(:1) }";
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(s, 1);
oraclecallablestatement.registerOutParameter(1, 12, 0, 1);
oraclecallablestatement.executeQuery();
ifEresEnabled = oraclecallablestatement.getString(1);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return ifEresEnabled;
}
public static String ifXdoInstalled(String s, OADBTransaction oadbtransaction)
throws SQLException
{
String s1 = null;
StringBuffer stringbuffer = new StringBuffer(" BEGIN ");
stringbuffer.append(" fnd_client_info.set_org_context(:1) ; ");
stringbuffer.append(" :2 := PO_COMMUNICATION_PVT.po_communication_profile();");
stringbuffer.append(" END;");
OracleCallableStatement oraclecallablestatement = null;
try
{
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(stringbuffer.toString(), 1);
oraclecallablestatement.registerOutParameter(2, 12, 0, 1);
oraclecallablestatement.setString(1, s);
oraclecallablestatement.executeQuery();
s1 = oraclecallablestatement.getString(2);
}
catch(Exception _ex) { }
finally
{
try
{
if(oraclecallablestatement != null)
{
oraclecallablestatement.close();
}
}
catch(Exception _ex) { }
}
return s1;
}
public static String[] checkNegotiations(OADBTransaction oadbtransaction, String s)
throws SQLException
{
String as[] = new String[2];
String s1 = "declare x boolean; BEGIN x := FND_INSTALLATION.GET_APP_INFO ( :1, :" +
"2, :3, :4 ); END; "
;
String s2 = "BEGIN pon_auction_interface_pkg.Get_PO_Negotiation_Link ( :1, :2, " +
" :3, :4, :5, :6, :7 ); END; "
;
String s3 = null;
String s4 = null;
String s5 = "";
try
{
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s1, 1);
callablestatement.setString(1, "PON");
callablestatement.registerOutParameter(2, 12);
callablestatement.registerOutParameter(3, 12);
callablestatement.registerOutParameter(4, 12);
callablestatement.execute();
s5 = callablestatement.getString(2);
callablestatement.close();
}
catch(Exception exception)
{
exception.printStackTrace();
}
CallableStatement callablestatement1 = null;
if(s5.equals("I"))
{
try
{
callablestatement1 = oadbtransaction.createCallableStatement(s2, 1);
callablestatement1.setInt(1, Integer.parseInt(s));
callablestatement1.registerOutParameter(2, 4);
callablestatement1.registerOutParameter(3, 12);
callablestatement1.registerOutParameter(4, 12);
callablestatement1.registerOutParameter(5, 4);
callablestatement1.registerOutParameter(6, 12);
callablestatement1.registerOutParameter(7, 12);
callablestatement1.execute();
String s6 = String.valueOf(callablestatement1.getInt(2));
s4 = callablestatement1.getString(3);
s3 = callablestatement1.getString(4);
callablestatement1.getInt(5);
callablestatement1.getString(6);
callablestatement1.getString(7);
s3 = s3 + "&AuctionId=" + "{!!" + oadbtransaction.encrypt(s6) + "&from=ISP";
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
as[0] = s3;
as[1] = s4;
return as;
}
public static String checkShipments(OADBTransaction oadbtransaction, String s, String s1)
throws SQLException
{
String s2 = "";
if(s1 != null && s1.length() > 0)
{
String s3 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_release_id= :2 ; end;"
;
CallableStatement callablestatement = oadbtransaction.createCallableStatement(s3, 1);
try
{
callablestatement.registerOutParameter(1, 4);
callablestatement.setInt(2, Integer.parseInt(s1));
callablestatement.execute();
s2 = String.valueOf(callablestatement.getInt(1));
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
finally
{
try
{
callablestatement.close();
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
} else
if(s != null)
{
String s4 = "Begin select count(distinct ship_to_location_id) into :1 from po_line_locations_" +
"all where po_header_id= :2 ; end;"
;
CallableStatement callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
try
{
callablestatement1.registerOutParameter(1, 4);
callablestatement1.setInt(2, Integer.parseInt(s));
callablestatement1.execute();
s2 = String.valueOf(callablestatement1.getInt(1));
}
catch(Exception exception4)
{
exception4.printStackTrace();
}
finally
{
try
{
callablestatement1.close();
}
catch(Exception exception5)
{
exception5.printStackTrace();
}
}
}
return s2;
}
public static String getGASecureWhereClause(Vector vector, OADBTransaction oadbtransaction)
throws SQLException
{
FunctionSecurity functionsecurity = oadbtransaction.getFunctionSecurity();
oracle.apps.fnd.functionSecurity.Function function = functionsecurity.getFunction("POS_HOME");
oracle.apps.fnd.functionSecurity.Function function1 = functionsecurity.getFunction("POS_IS_CP_HOME");
oracle.apps.fnd.functionSecurity.Function function2 = functionsecurity.getFunction("POS_ISP_CP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function3 = functionsecurity.getFunction("POS_ISP_SRC_HOME");
oracle.apps.fnd.functionSecurity.Function function4 = functionsecurity.getFunction("POS_INTERNAL_HOME");
Integer integer = new Integer(oadbtransaction.getResponsibilityId());
if(integer == null oadbtransaction.getResponsibilityId() == 0 !functionsecurity.testFunction(function) && !functionsecurity.testFunction(function1) && !functionsecurity.testFunction(function2) && !functionsecurity.testFunction(function3) && !functionsecurity.testFunction(function4))
{
return "null = null";
}
String s = "";
String s2 = "";
int i = oadbtransaction.getUserId();
CallableStatement callablestatement = null;
CallableStatement callablestatement1 = null;
ResultSet resultset = null;
ResultSet resultset1 = null;
String s3 = " SELECT distinct arsa.attribute_code FROM ak_resp_security_attributes arsa W" +
"HERE arsa.responsibility_id = ? AND arsa.attribute_application_id = 177"
;
String s4 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) sec_" +
"value FROM ak_web_user_sec_attr_values WHERE web_user_id = ? AND attribut" +
"e_code = ? AND attribute_application_id = 177 UNION SELECT nvl(to_char(numbe" +
"r_value), nvl(varchar2_value,to_char(date_value))) sec_value FROM AK_RESP_SECU" +
"RITY_ATTR_VALUES WHERE responsibility_id = ? AND attribute_application_id =" +
" 177 AND attribute_code = ? AND number_value = -9999 ORDER BY sec_value "
;
try
{
callablestatement = oadbtransaction.createCallableStatement(s3, 1);
callablestatement.setInt(1, oadbtransaction.getResponsibilityId());
for(resultset = callablestatement.executeQuery(); resultset.next();)
{
String s5 = "";
String s1 = resultset.getString("attribute_code");
if(s1 != null)
{
if(s1.equals("ICX_SUPPLIER_ORG_ID"))
{
s5 = "VENDOR_ID";
} else
if(s1.equals("ICX_SUPPLIER_CONTACT_ID"))
{
s5 = "VENDOR_CONTACT_ID";
} else
if(s1.equals("ICX_SUPPLIER_SITE_ID"))
{
s5 = "VENDOR_SITE_ID";
}
if(s5 != "")
{
String s6 = "";
String s7 = "";
Vector vector1 = new Vector();
callablestatement1 = oadbtransaction.createCallableStatement(s4, 1);
callablestatement1.setInt(1, i);
callablestatement1.setString(2, s1);
callablestatement1.setInt(3, oadbtransaction.getResponsibilityId());
callablestatement1.setString(4, s1);
resultset1 = callablestatement1.executeQuery();
boolean flag = true;
int j = 0;
Vector vector2 = new Vector();
while(resultset1.next())
{
String s8 = resultset1.getString("sec_value");
if(s8 != null && s8 != "")
{
flag = false;
if(!s8.equals("-9999"))
{
vector2.addElement(s8);
}
j++;
}
if(j >= 254)
{
break;
}
}
if(j < 254)
{
for(int k = 0; k < vector2.size(); k++)
{
if(s5.equals("VENDOR_SITE_ID"))
{
vector1.addElement(vector2.elementAt(k));
}
vector.addElement(vector2.elementAt(k));
if(s6 == "")
{
s6 = s6 + ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}
if(flag)
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " = null";
}
if(s6 != "" && !s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s6 + ")";
} else
if(s6 != "" && s5.equals("VENDOR_SITE_ID"))
{
if(s2 != "")
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s6 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')";
s6 = "";
if(!vector1.isEmpty())
{
for(int l = 0; l < vector1.size(); l++)
{
vector.addElement(vector1.elementAt(l));
if(s6 == "")
{
s6 = ":" + vector.size();
} else
{
s6 = s6 + ", :" + vector.size();
}
}
s2 = s2 + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in ( select PGA.po_header_id from " +
"PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH where PGA.po_header_id = POH.po_h" +
"eader_id and PGA.enabled_flag = 'Y' and POH.global_agreement_flag = 'Y' an" +
"d PGA.vendor_site_id in ("
+ s6 + "))))";
}
}
} else
{
vector.addElement(Integer.toString(i));
vector.addElement(s1);
String s9 = " SELECT nvl(to_char(number_value), nvl(varchar2_value,to_char(date_value))) FRO" +
"M ak_web_user_sec_attr_values WHERE web_user_id = :"
+ (vector.size() - 1) + " AND attribute_code = :" + vector.size() + " AND attribute_application_id = 177 ";
if(s5.equals("VENDOR_SITE_ID"))
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + " ((VENDOR_SITE_ID in (" + s9 + ")" + " AND NVL(global_agreement_flag, 'N') = 'N')" + " OR (PO_RELEASE_ID is null AND PO_HEADER_ID in (" + " select PGA.po_header_id" + " from PO_GA_ORG_ASSIGNMENTS PGA, PO_HEADERS_ALL POH" + " where PGA.po_header_id = POH.po_header_id" + " and PGA.enabled_flag = 'Y'" + " and POH.global_agreement_flag = 'Y'" + " and PGA.vendor_site_id in (" + s9 + "))))";
} else
{
if(!s2.equals(""))
{
s2 = s2 + " AND ";
}
s2 = s2 + s5 + " in (" + s9 + ")";
}
}
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception3)
{
exception3.printStackTrace();
}
}
}
}
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
try
{
if(resultset1 != null)
{
resultset1.close();
}
if(resultset != null)
{
resultset.close();
}
if(callablestatement != null)
{
callablestatement.close();
}
if(callablestatement1 != null)
{
callablestatement1.close();
}
}
catch(Exception exception2)
{
exception2.printStackTrace();
}
}
if(s2 == null s2.trim().length() == 0)
{
return " null = null ";
} else
{
return s2;
}
}
public PosServerUtil()
{
}
}
Subscribe to:
Posts (Atom)