RUNNING SHELL SCRIPT IN ORACLE

RUNNING SHELL SCRIPT IN ORACLE

It’s simple!!Especially with 10g,
it is even more simpler.
Here is an example:

Step 1: Create a shell script and grant execute priv to oracle user.

I created a simple one:bash-3.00$ cat /tmp/test.sh#!/usr/bin/kshecho "Test succeeded `date`" >> /tmp/test.log

Note: Do not forget to start your shell script with the shell you are using.

>> #!/usr/bin/ksh should be present in your shell script otherwise, the job will fail with ORA-27369 job of type EXECUTABLE failed with bash-3.00$ chmod 755 /tmp/test.sh

Step 2: Create the job:Remember user doing the following should have “CREATE JOB” privilege.

SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB
(3 job_name => 'TEST_SHELL',
4 job_type => 'EXECUTABLE',
5 job_action => '/tmp/test.sh',
6 start_date => sysdate,
7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', /* every one hour */
8 enabled => TRUE,
9 comments => 'Testing shell script from Oracle');
10 END;
11 /PL/SQL procedure successfully completed.

Step 3: Check if the job is running:

bash-3.00$ cat /tmp/test.logTest succeeded Wed Jan 31 01:02:23 PST 2007Test succeeded Wed Jan 31 01:03:23 PST 2007Test succeeded Wed Jan 31 01:04:23 PST 2007

FROM ORACLE TO UNIX SHELL ENVIRONMENT

HOW TO PASS A VALUE FROM ORACLE TO UNIX SHELL ENVIRONMENT VARIABLE


Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.

Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0
{{SELECT STMT}};
exit
EOF`

For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" <<>
set echo off verify off feed off termout off pages 0

> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8>
from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes>
from dba_data_files> group by tablespace_name) tot,> dba_tablespaces tbs>
where tot.tablespace_name = tbs.tablespace_name>
and fre.tablespace_name(+) = tbs.tablespace_name>
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes> /> exit> EOF`

-bash-3.00$ echo $DB_USED4189795.79This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.



HOW TO RENAMED MATERIALIZED VIEW? ORA-32318


Now - this is what you call a long break .. almost a year.

Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl.

We are still searching a name for her.

That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.

I hope to be more punctual in writing my experiences here on the blog.

Today, I have something on materialized views.

How to rename them?Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to ORA32318.

ORA-32318: cannot rename a materialized view

Cause: Renaming a materialized view or its base table is not supported.

Action: Do not rename the base table of a materialized view.

Now, this is what I dont expect Oracle to do.

when rename of table is possible, then why not rename of mat view possible?

If any one knows a logical reason, please share.Anyways, I gave it a further thought on how to accomplish this and an idea struck me.I'm giving the information of our system but with changed names.DB1 - where base table exists - 10.2.0.2DB2 - where mview exists - 10.2.0.2T1 - name of the table on DB1M1 - name of the mview - Fast/Incremental refresh on demandNow as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).Here is some test setup scripts:On DB1:

SQL> create table t1 as select * from all_objects;Table created.

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.On DB2:

SQL> create materialized view m1 refresh fast on demand as select * from t1@db1;

- db1 is database linkMaterialized view created.

SQL> select count(*) from m1;

COUNT(*)----------62551Now steps to change the name:

Step1: On DB1- Stop DML/DDL activities on the base table T1Step2: On DB2- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.

- Create another table with new name. This new name should be the name of mview intended.

SQL> create table m2 as select * from m1;

Table created.>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview

Step3: On DB2Create new materialized view using "prebuilt" option.

SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from T1@DB1;

Materialized view created.

Step4: On DB2Drop the old materialized view.

SQL> drop materialized view m1;

Materialized view dropped.

Thats it!Now to check if thats working.

Delete few rows on t1@db1

SQL> delete from t1 where object_id in (2,3);

2 rows deleted.

SQL> commit;

Commit complete.

>> And now refresh the new mview.

SQL> exec dbms_mview.refresh('M2')

PL/SQL procedure successfully completed.

>> Check the no of rows:SQL> select count(*) from m2;COUNT(*)----------62549Finally check the way oracle refreshed this mview.

SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';

LAST_REF
------------
FAST

Well that's just a confirmation :)

Note: I havent tested other functionalities of this test.

So please do a thorough testing of this code before pushing into production database system.BTW - we are doing the same.

WHEN USING MVIEW AND PARTITION TABLE

ORA-14097 -- WHEN USING MATERIAZED VIEW AND PARTITION TABLE :

This one was an interesting issue which came up few days back.

I spent quite sometime before I solved it.

Issue was -

A developer came upto me and told that he is getting "ORA-14097 - :

column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.
This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.

Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables.

We could not have directly exchanged partitions of fact table as they might not be partitioned by day.Now the above is all background.

While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select ..

the mview where 1=2" and while doing exchange partition he was getting ORA-14097.

Let me give you a simple test case:

SQL> create table test(a number);

Table created.

SQL> create materialized view log on test with rowid including new values;

Materialized view log created.

SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;

Materialized view created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('test_mv','F')

PL/SQL procedure successfully completed.

Now both table and mview have on erow each.

Let's try and exchange partition of this mview with another table.

SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';

PARTITION_NAME
------------------------------
SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;

alter table test_part exchange partition SYS_P3446 with table test_mv*ERROR at line 1:

ORA-14097:

column type or size mismatch in

ALTER TABLE EXCHANGE PARTITIONI

used CTAS, there is no question abt data type and column order.

SQL> desc test_partName Null?

Type

----------------------------------------- -------- ----------------------------
A NUMBER

SQL> desc test_mvName Null?

Type
----------------------------------------- -------- ----------------------------
A NUMBERAfter doing some research,

i got through this metalink article: 72332.1

According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail.

Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference.

"SQL> select col#, name2 from sys.col$3 where obj# in4 (select object_id from user_objects where object_name = 'TEST_MV');

COL# NAME
---------- ------------------------------
0 M_ROW$$1 ANow there you go - this M_ROW$$

was creating the problem for me.

Old saying - identifying is problem is 80% of tak that entails solving it.

Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

Though it was trivial issue and solution. it kept me thinking for some time!

INDEX USAGE WITH LIKE OPERATOR

INDEX USAGE WITH LIKE OPERATOR


I have seen many developers getting confused on index usage with like operator.

Few are of the feeling that index will be used and few are against this feeling.

Let’s see this with example:

SQL> create table sac as select * from all_objects;

Table created.

SQL> create index sac_indx on sac(object_type);I

ndex created.

SQL> set autotrace trace explain

SQL> select * from sac where object_type='TAB%';

Execution Plan

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=128)1

0 TABLE ACCESS (BY INDEX ROWID) OF 'SAC' (TABLE) (Cost=1 Card=1 Bytes=128)

2 1 INDEX (RANGE SCAN) OF 'SAC_INDX' (INDEX) (Cost=1 Card=1)

Above example shows that using % wild card character towards end probe an Index search.

But if it is used towards end, it will not be used. And sensibly so, because Oracle doesn’t know which data to search, it can start from ‘A to Z’ or ‘a to z’ or even 1 to any number.See this.

SQL> select * from sac where object_type like '%ABLE';

Execution Plan
----------------------------------------------------------0
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=148 Card=1004 Bytes=128512)1
0 TABLE ACCESS (FULL) OF 'SAC' (TABLE) (Cost=148 Card=1004 Bytes=128512)

Now how to use the index if you are using Like operator searches. The answer is Domain Indexes.

See the following example:

SQL> connect / as sysdba

Connected.

SQL> grant execute on ctx_ddl to public;

Grant succeeded.

SQL> connect sac/******

Connected.

SQL> begin2 ctx_ddl.

create_preference('SUBSTRING_PREF',3 'BASIC_WORDLIST');

4 ctx_ddl.set_attribute('SUBSTRING_PREF',

5 'SUBSTRING_INDEX','TRUE');

6 end;

78 /PL/SQL procedure successfully completed.

SQL>SQL> drop index sac_indx;Index dropped.

SQL> create index sac_indx on sac(object_type)

indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF memory 50m');

Index created.

SQL> set autotrace trace exp

SQL> select * from sac where contains (OBJECT_TYPE,'%PACK%')

> 02 /Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=19 Bytes=1786)1

0 TABLE ACCESS (BY INDEX ROWID) OF 'SAC' (TABLE) (Cost=8 Card=19 Bytes=1786)

2 1 DOMAIN INDEX OF 'SAC_INDX' (INDEX (DOMAIN)) (Cost=4)

In this case the index is getting used.

Conclusion=============For proximity, soundex and fuzzy searchs, use domain indexes.

OPTIMIZER_MODE - ALL_ROWS (OR) FIRST_ROWS

OPTIMIZER_MODE - ALL_ROWS (OR) FIRST_ROWS


Out of all Oracle RDBMS modules, optimizer code is actually the most complicated code and different optimizer modes seem like jack while lifting your car in case of a puncture.

This paper focuses on how optimizer behaves differently when you have optimizer mode set to ALL_ROWS or FIRST_ROWS.

Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
By default, the value of optimizer_mode is CHOOSE which basically means ALL_ROWS (if statistics on underlying tables exist) else RULE (if there are no statistics on underlying tables).

So it is very important to have statistics collected on your tables on regular intervals or else you are living in Stone Age.

FIRST_ROWS and ALL_ROWS are both cost based optimizer features. You may use them according to their requirement.


FIRST_ROWS/ FIRST_ROWS[n]

In simple terms it ensures best response time of first few rows (n rows).

This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.

Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running.

Ok, let us simulate this on SQLPLUS

Create a table and index over it:

SQL> create table test as select * from all_objects;

Table created.
SQL> create index test_in on test(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SAC’,'TEST')

PL/SQL procedure successfully completed.

SQL> select count(*) from test;COUNT(*)----------37944

SQL> select count(*) from test where object_type='JAVA CLASS';

COUNT(*)----------14927


You see out of almost 38k records, 15k are of JAVA class.

And now if you select the rows having object_type=’JAVA_CLASS’, it should not use index as almost half of the rows are JAVA_CLASS.

It will be foolish of optimizer to read the index first and then go to table.


Check out the Explain plans

SQL> set autotrace traceonly exp

SQL> select * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------

0 SELECT STATEMENT 1001 94094 10 (0) 00:00:01 * 1 TABLE ACCESS FULL TEST 1001 94094 10 (0) 00:00:01

--------------------------------------------------------------------------
As you see above, optimizer has not used Index we created on this table.

Now use FIRST_ROWS hint:

SQL> select /*+ FIRST_ROWS*/ * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3548301374
---------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------
0 SELECT STATEMENT 14662 1345K 536 (1) 00:00:07 1 TABLE ACCESS BY INDEX ROWID TEST 14662 1345K 536 (1) 00:00:07 * 2 INDEX RANGE SCAN TEST_IN 14662 43 (3) 00:00:01
---------------------------------------------------------------------------------------
In this case, optimizer has used the index.

Q> Why?

Ans> Because you wanted to see first few rows quickly. So, following your instructions oracle delivered you first few rows quickly using index and later delivering the rest.
See the difference in cost, although the response time (partial) of second query was faster but resource consumption was high.

But that does not mean that this optimizer mode is bad. As I said this mode may be good for interactive client-server model. In most of OLTP systems, where users want to see data fast on their screen, this mode of optimizer is very handy.

Important facts about FIRST_ROWS

It gives preference to Index scan Vs Full scan (even when index scan is not good).
It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes one first input in hash table which takes time.

Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.

It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.

ALL_ROWS

In simple terms, it means better throughput
While FIRST_ROWS may be good in returning first few rows,

ALL_ROWS ensures the optimum resource consumption and throughput of the query.

In other words, ALL_ROWS is better to retrieve the last row first.

In above example while explaining FIRST_ROWS, you have already seen how efficient ALL_ROWS is.

Important facts about ALL_ROWS

ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.

Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)
Likes hash joins over nested loop for larger data sets. ConclusionCost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.

TEMPLATE FORM IN ORACLE APPS 11i

TEMPLATE FORM IN ORACLE APPS 11i

This is an overview of the template form.

This form derives its importance from the fact that this form is the starting point of all development involving forms. The document highlights the importance of Template.fmb in forms development and also provides a detailed explanation of the various components of the Template form.

Overview of the Template FormThe TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it.

The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics.

The components of the template form are:·

References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form

(STANDARD_PC_AND_VA,STANDARD_TOOLBAR, and STANDARD_CALENDAR).·

Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK).·

Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form.

· Predefined Program Units:

The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events.·

Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle applications.· Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on.

· The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from the form later as they are only examples and are not required.

The following objects are the samples and can be removed:

Blocks: BLOCKNAME, DETAILBLOCK

Window: BLOCKNAME

Canvas–view: BLOCKNAME

Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make t easier to develop forms with consistent and standard functionality.

Libraries in the Template formAs stated above, the template form contains platform–independent attachments of several libraries.

These libraries are used while running the form as a part of Oracle Applications. Hence, these libraries should not be changed or modified.

There are three main libraries that are attached to the template form:
APPCORE
APPDAYPK
FNDSQF

Each of these libraries is explained in detail below.

APPDAYPK

The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle apps provide to pick the dates for a date type field.

FNDSQF

FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing.

It also has various other utilities for navigation, multicurrency, WHO, etc. 23–4 Oracle Applications Developer’s Guide Procedures and functions in FNDSQF typically have names beginning with ”FND”.

Other LibrariesThe template form also contains a few other libraries that are not linked directly to the template form, but are linked to the three libraries listed above. Although, while using the form it makes no difference whether the library is linked directly to template or to another library that is linked to template.

These are discussed below.

CUSTOM library:

The CUSTOM library (CUSTOM.pll) is probably the most widely used and customized in the libraries attached to the template form. This library allows extension of Oracle Applications forms without modification of Oracle Applications code.

Any form goes to the CUSTOM.pll whenever any event fires on the form.

Code can be written in the CUSTOM.pll with the logic branching based on the form, block and trigger on which you want it to run.You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.GLOBE:

The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can take effect based on these events.

The GLOBE library calls routines in the JA, JE, and JL libraries.

VERT:The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library.

Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.JAThe JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.

JEThe JE library contains code specific to the EMEA (Europe/MiddleEast/Africa) region and is called by the GLOBE library.JLThe JL library contains code specific to the Latin America region and is called by the GLOBE library.

PROJECT ACCOUNTING ( PA )

PROJECT ACCOUNTING

Costing

Items not costedThe output of the labor cost distribution cost distribution shows the error as Missing Cost Rate.

This happens for Sub-Contractors when they do not have a Cost Rate setup.

For employees the costing is based on the Job Grade but for Sub-contractors the costing is based on the Cost Rate setup. All Sub-contractors should have a Cost Rate set up.

Labor Cost Distribution Program does not pick up itemsWhen giving a PA Date for the Cost distribution program, the date always has to be a Friday, if otherwise, the process will not pick up any items. This is applicable even if the week is a split week, i.e., the month end falls in midweek and the costing needs to be done for that month.

Items wrongly costedThe costing for employees is based on the business rule that ‘Maximum available hours per person per week unless the total hours that are billable are greater than the available hours’.

This implies that the Costing for Billable Tasks will be as per the Cost Rate and any non-billable cost will be pro-rated as per the billable time entered and the maximum available hours.E.g.If an employee has coded 40 hours on billable task and 10 hours on non-billable task, then the non-billable time will be zero-costed (assuming that the maximum available hours for the week is 40)If an employee has coded 30 hours on billable task and 15 hours on non-billable task, then the 15 hours on non-billable will be pro-rated for 10 hours.

This will result in the Cost Rate for these items being less than the Standard Cost Rate.Signal-11 error when running the Distribute Labor Cost programThis happens sometimes when the number of items that are being picked for distribution is very high.

To avoid this, run the cost distribution in batches for one week at a time or one project at a time.Reversed Time Items zero costedThe original items are cost distributed and the Project burden shows the correct value but the reversed items shows zero cost.This happens when the items are reversed before they are cost distributed.

Hence the reversed items will be zero-costed, as there is no cost rate attached to the original items. To rectify this, negative items have to be created through pre-approved expenditure batch, then before costing this batch has to be reversed.

Finally, the cost should be distributed. This will correct the cost and also the hours charged so that the URVE is not affected.Expense Report / Supplier Invoice items not costedExpense Report / Supplier Invoice items are already costed when they are imported into PA. But when these items are adjusted i.e. transferred or reversed, the adjusted items will not be cost distributed.

o do this, the Distribute Expense Report Cost process or the Distribute Supplier Invoice Adjustment Costs should be executed.

Agreements, Funding and Budgets

Budget cannot be baselined - Funding not equal to budgetThe Funding amount is not the same as the budget. Specify the correct amount as in funding.

Also, the budget should be at the same level as the funding, e.g. if the funding is at project level then the budget should also be at project level or if the funding is at task level then the budget should also be at task levelAlso, sometimes when a funding line is created and then deleted, the budget amount will not match the funds.

In these cases, the funding lines will have to be checked in the database and the amount equal to funding will have to be entered in the budget.

Note: When the Project start or end date is changed, the budget has to be re-baselined.Revenue GenerationItems are not picked up for revenueThe time and expense items coded to a Billable task will only be picked up for Revenue Generation.

All items coded to Non-Billable Task will not generate revenue. The billable flag for an item can be checked in the expenditure inquiry screen by choosing the Billable_Flag field from Show Field in the Folder Menu. This has to be checked for the item to be eligible for revenue generation.The items have to be Cost distributed before they can generate revenue.

Also, the item date should be before the “accrue through date” when the generate revenue process is executed.Events are not generatedThe event date should be before the “accrue through date” when the generate revenue process is executed.No FundingAn agreement and funding are not created for this project.

Before generating revenue for a project, an agreement and funding have to be created and an approved revenue budget should be baselined.Revenue has reached hard limitRevenue generation is based on the agreement.

If the agreement has a hard limit defined (the hard limit check box will be ticked), then revenue cannot be generated beyond the amount specified. To rectify this, either remove the hard limit or increase the funding.Invoice GenerationNo active event or expenditure itemsThe time and expense items coded to a Billable task will only be picked up for Invoice Generation.

All items coded to Non-Billable Task will not be picked up for invoicing. The billable flag for an item can be checked in the expenditure inquiry screen by choosing the Billable_Flag field from Show Field in the Folder Menu.

The items have to be Cost distributed before they can generate revenue.Also, the item date should be before the “bill through date” when the generate invoice process is executed.

For an event, the event date should be before the “ bill through date “ and should not have a bill hold.Project not picked up when the Invoices are generated for a Range of ProjectsWhen generating invoices for a range of projects, a project will not be picked for invoicing if the “next billing date” is after the current date.

This can be checked in the

Project Options -> Billing Set up -> Billing Assignments.

Interfacing Cost to GLNo Open PeriodThe GL period to which the items belong is not open in GL. Open the period and run the interface program again.Interfacing Revenue to GLNo Open PeriodThe GL period to which the revenue lines belong is not open in GL.

Open the period and run the interface program again.Interfacing Invoices to ARNo Output Tax CodeThe Tax Code in the Invoice lines is not available. Attach a tax code to the invoice lines and then run the interface program again.No Open PeriodThe GL period to which the invoice lines belong is not open in GL and/or AR.

Open the period and run the interface program again.Interfacing Expense Report / Supplier Invoice Adjustments from and to APProject Status does not allow transactionsThe project to which an expense item belongs should be ACTIVE.

All items charged to Project, which is CLOSED or PENDING CLOSE would not be picked up by the interface program. To interface these items to PA, the project statuses have to be changed to ACTIVE and then the interface program needs to be run.

Transaction ExceptionsNo Open PeriodThe GL Period in which the cost or revenue items belong might be closed. In case of invoices, the GL Period and / or the AR Period might be closed.Costing ExceptionsUndefinedThe journal import for the cost items is not done.

The journal import in GL should be executed and then the tieback labor cost from GL program in PA has to be executed.Revenue ExceptionsUndefinedThe journal import for the revenue items is not done.

The journal import in GL should be executed and then the tieback revenue from GL program in PA has to be executed.Invoicing ExceptionsNew Line not Processed in PAThe supplier invoice or expense reports in AP have not been interfaced to PA.

To get these invoices / expense reports into PA, they have to approved and accounted and then the interface program in PA has to be executed.No AssignmentThe employee who has created an expense report might have been end dated. Hence these items will not be interfaced to PA from AP.

To rectify this error, change the Supplier Type in AP of the employee from employee to supplier and then run the interface supplier invoice interface program.

ReportsThe Project related reports – PDR, PCR and WIP & AR Reports will not pick a project if a Project Manager or Account Manager is not attached to the report.The project related reports – PDR, PCR and WIP & AR Reports will pick data as per the GL Date of the Expenditure Items and not as per the Expenditure Item Date.

Hence the reports might not match with the extract taken from Expenditure Inquiry screen if the item date has a different GL Date.The Emailing program will not run for Internal Projects. The reports will be emailed to the concerned employee only if the person is set up as the Project / Account Manager / Project Controller of the Project.

SOME USEFUL INFORMATION (PERFORMANCE TUNING)

Some Useful Information (Performance Tuning)

1)What is the difference between truncate and delete

2)if the synonym is exists for the table , if we re-create the table the existing synonym will work?

3)select count(1) from dual will it goes to NO_DATA_FOUND exception when data would not match the criteria.

4)union all and union which one is better

5) INSERT INTO /*+ APPEND PARALLEL(AEXP_FCH_BALANCES_FACT_TEMP1,DEFAULT) */ aexp_fch_balances_fact_temp1

6) SELECT /*+ ALL_ROWS NO_MERGE */ from table_name

7)while inserting the data into one table into another table Better mention column list

8)fnd_request.submit_request It will kicked off with the sysadmin user, instead of sysadmin we want the program kicked off user name.

9)Gather stats table schema (table name) available in sysadmin responsibility

10) UPDATE /*+ INDEX(FACT AEXP_FCH_BALANCES_FACT_IND25) */ AEXP_FCH_BALANCES_FACT FACT SET user_dim6_id = l_geo_id, user_dim5_id = l_lob_id, user_dim7_id = l_seg_id WHERE user_dim1_id = rec_geo_lob.user_dim1_id --and enabled_flag = 'Y' -- AND report_to_use_flag = 'Y' and category_code = rec_category_dim.category_code and cal_period_id = p_cal_period_id and entity_cons_id = p_hierarchy_id;

11) How to analyze the tables Analyze table schem_name.table_name compute STATISTICS;

ORACLE CRM SPARES MANAGEMENT OVERVIEW

ORACLE CRM SPARES MANAGEMENT OVERVIEW



Proposed Process:

TAC receives phone calls from customers and tries to resolve over phone. If they can’t resolve it, the call is escalated to Dispatch.

Dispatch looks at the call and assigns FE to the call.

The call information is pushed into an Oracle Interface. This interface data will be used to create SR, Task and assign the task to the FE.

There will be a process scheduled in Oracle to look at the Part Requirement Interface and create a service request (SR) for the remedy call.

A task will be created for the service request in Oracle automatically.

The task will be assigned to the Field Engineer (FE).

Parts requirements are entered by FE directly in Remedy or by Dispatch in Remedy.
Once the parts requirements are completed, it will be pushed to an Oracle interface with the following details:

Remedy Call#
Date & Time of the call
Scanner Part#
Scanner Serial #
Issue with the scanner
FE assigned to
Spare part requested
Quantity
Shipment Priority
Shipping Address
Schedule ship date

The parts required for the call will be created automatically in “Parts Requirements screens” for the SR.

The parts required will be created as mixed order in Order Management Module.

Based on the schedule date and inventory availability, the order will be pick-released.
Eagle/Sacramento will pick/pack/ship based on the pick slip.

Workflow for the order will be tailored to move the inventory from FGI to the respective FE’s subinventory with call number as part of the lot.

When the shipments are made, the details of the shipment will be sent to Remedy through an Oracle interface. Email notification will be sent to the FE from Oracle.

Remedy will use the shipment information to attach to the call, to help FE know the status of the spares ordered by him.

FE makes the customer visit and fixes the scanner.

Once he fixes the scanner, the spares used by the FE will be entered in Remedy.
FE needs to account for all the spares that were shipped for a particular call.

FE will distinguish whether he used it from his truck or the one shipped from the warehouse.
If he did not use any of the spares, he needs to mark it as Un-used and send it back to the warehouse.

Also, if the spare part is a high dollar value, the faulty spare part needs to be returned to the warehouse.

Remedy will push all the “spares used” information to Oracle through an interface.
This will populate Field Service – Debrief.

The Debrief will create charges for the particular SR. A sales order with appropriate price list will be created in Oracle.

The workflows in the sales order will be tailor made to pick inventory from the FE’s location and based on Warranty/Contract/T&M change the COGS.
Also, RMA line will be created with appropriate receiver-notes. This will enable Eagle/Sacramento to receive and do a put-away based if the part is un-used or faulty.

ORACLE CRM SPARES MANGMNT OVERVIEW

Oracle CRM Spares management Overview

Proposed Process:

TAC receives phone calls from customers and tries to resolve over phone. If they can’t resolve it, the call is escalated to Dispatch.





Dispatch looks at the call and assigns FE to the call.





The call information is pushed into an Oracle Interface. This interface data will be used to create SR, Task and assign the task to the FE.





There will be a process scheduled in Oracle to look at the Part Requirement Interface and create a service request (SR) for the remedy call.





A task will be created for the service request in Oracle automatically.





The task will be assigned to the Field Engineer (FE).





Parts requirements are entered by FE directly in Remedy or by Dispatch in Remedy.





Once the parts requirements are completed, it will be pushed to an Oracle interface with the following details:





Remedy Call#
Date & Time of the call
Scanner Part#
Scanner Serial #
Issue with the scanner
FE assigned to
Spare part requested
Quantity
Shipment Priority
Shipping Address
Schedule ship date





The parts required for the call will be created automatically in “Parts Requirements screens” for the SR.





The parts required will be created as mixed order in Order Management Module.
Based on the schedule date and inventory availability, the order will be pick-released.
Eagle/Sacramento will pick/pack/ship based on the pick slip.
Workflow for the order will be tailored to move the inventory from FGI to the respective FE’s subinventory with call number as part of the lot.





When the shipments are made, the details of the shipment will be sent to Remedy through an Oracle interface. Email notification will be sent to the FE from Oracle.
Remedy will use the shipment information to attach to the call, to help FE know the status of the spares ordered by him.





FE makes the customer visit and fixes the scanner.
Once he fixes the scanner, the spares used by the FE will be entered in Remedy.
FE needs to account for all the spares that were shipped for a particular call.
FE will distinguish whether he used it from his truck or the one shipped from the warehouse.
If he did not use any of the spares, he needs to mark it as Un-used and send it back to the warehouse.





Also, if the spare part is a high dollar value, the faulty spare part needs to be returned to the warehouse.
Remedy will push all the “spares used” information to Oracle through an interface.
This will populate Field Service – Debrief.
The Debrief will create charges for the particular SR. A sales order with appropriate price list will be created in Oracle.





The workflows in the sales order will be tailor made to pick inventory from the FE’s location and based on Warranty/Contract/T&M change the COGS.
Also, RMA line will be created with appropriate receiver-notes. This will enable Eagle/Sacramento to receive and do a put-away based if the part is un-used or faulty.

ITEM VALIDNS IN PO_LINES BLOCK(CUSTOM.pll)

Item validation in PO_LINES block using custom.pll

Question: form_name = 'POXPOEPO'
block_name = 'PO_LINES' in purchase orders form..
I have a PO Lines form for purchase orders, There in the ITEMS tabbed form, The block name for ITEMS is PO_LINES, you can see items like NUM,TYPE,ITEM,REV,CATEGORY AND etc.. Suppose user enters a new record, The REV field should be validated against ITEM field when user clicks the save button on the top. ( validaton is REV field should not be null when item field is filled up)
Can you help me on this issue??
I am little confused about what trigger I am supposed to use in the custom.pll, When I use "event_name = 'WHEN_VALIDATE_RECORD'" , this is not getting fired.I am not sure about whether PRE_COMMIT event would be appropriate one for this kinda validation.. The following I have written in custom.pll
--item_revision check PO_ITEM_REVISION_CHECK in PO_FORM
procedure po_item_revision_check is
l_error_message varchar2(150);
l_item_number number;
l_ship_to_org_id number;
l_item_revision varchar2(3);
l_revision_code number;
BEGIN
IF (form_name = 'POXPOEPO') AND (block_name = 'PO_LINES') THEN
-- IF (event_name = 'WHEN_VALIDATE_RECORD') THEN
IF (event_name = 'PRE_COMMIT') THEN
l_item_number := name_in('PO_LINES.ITEM_NUMBER');
l_item_revision := name_in('PO_LINES.ITEM_REVISION');
GO_BLOCK('PO_HEADERS');
l_ship_to_org_id := name_in('PO_HEADERS.SHIP_TO_ORG_ID'); IF (l_item_number IS NOT NULL) THEN
SELECT MSI.REVISION_QTY_CONTROL_CODE
INTO l_revision_code FROM MTL_SYSTEM_ITEMS MSI WHERE MSI.SEGMENT1 = l_item_number
AND MSI.ORGANIZATION_ID = l_ship_to_org_id; FND_MESSAGE.SET_STRING('Item Number: 'l_item_number' Revision Code: 'l_revision_code);
FND_MESSAGE.SHOW;
IF (l_revision_code = 2 AND l_item_revision IS NULL) THEN FND_MESSAGE.SET_STRING('Please enter Item Revision Number'); FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
END IF;
END IF;
END IF;
EXCEPTION
when others then
l_error_message := SUBSTR(SQLERRM,1,140);
COPY (l_error_message,'PO_LINES.ATTRIBUTE15'); END po_item_revision_check;

MULTI - ORG CONCEPTS

MULTI - ORG CONCEPTS

Multi-Org is a server-side (applications and database) enhancement that enables multiple business units in an enterprise to use a single installation of Oracle Applications products while keeping transaction data separate and secure. The Multi-Org enhancement uses native database views to build a security layer on top of a single installation of Oracle Applications. In Oracle Applications Release 11i, the following products support

Multi-Org capabilities:

• Cash Management
• Order Management, Shipping Execution and Release Management
• Payables
• Property Manager
• Projects
• Purchasing
• Receivables
• Sales Compensation
• Sales and Marketing
• Service

Basic Business Needs

The Multi-Org enhancement to Oracle Applications provides features necessary to satisfy the following basic business needs. You should be able to:
• Use a single installation of any Oracle Applications product to support any number of business units, even if those business units use different sets of books.
• Support any number of business units within a single installation of Oracle Applications.
• Secure access to data so that users can access only information that is relevant to them.
• Procure products from an operating unit that uses one set of book, but receive them from another operating unit using a different set of books.
• Sell products from an operating unit that uses one set of books, but ship them from another operating unit using a different set of books, automatically recording the appropriate intercompany sales by posting intercompany accounts payable and accounts receivable invoices.
• Report at any level of the organizational structure.

By running the following SQL statement we can know
select multi_org_flag from fnd_product_groups;
The result 'Y' means your database is setup for multiorg.

Limitations Of Multi Org

1.With the exception of data that is shared across organizations,all data is secured and striped by operating unit level.
2.Multiple Organizations enabled products do not support viewing secured data across operating units.
For example you can take Supplier defination ,We can see header information only from other operating unit.It's not possible to share site information.Bank defination also.....But in R12 Bank defination has been changed.Now we can maintain one bank account for N number of operating units under one legal entity.
3.There is no additional support for centralization/decentralization of business functions.

ORACLE 9I COMMANDS

Oracle 9i Commands


To create table
Create table (value1 datatype,……);

To insert to table
Insert into values(data1,……);

To view the all the data table
Select * from ;

To view some column
Select from ;

To update data in the table
Update set =data to update where =data to find;

To delete from table
Delete from where =data to find;

To drop the table;
Drop table ;

To set primary key
When you create the table
Create table (value1 datatype primary key,…..);
After creating the table
Alter table add constraint primary key(column-name>;

To set foreign key
When creating the table
Create table (value1 datatype, constraint foreign key(column-name) references );
After creating the table
Alter table add constraint foreign key(column-name) \
References ;

To set the check constraint
When creating the table
1-column type
Create table (column1 datatype constraint
check(column1 operator rule));
2-table type
Create table (column1 datatype, column2 datatype,constraint
check(column1 rule column2 );

after the table created
alter table add constraint check(column-name rule);

to set not null
when creating the table
create table (column1 datatype not null);
to set unique key
when create the table
create table (column1 datatype unique);
after creating the table
alter table add constraint unique(column-name);

to use joins
1-inner join
select column1-table1,column1-table2 from ,
where =;
2-right join
select column1-table1,column1-table2 from ,
where (+)=;
3-left join
select column1-table1,column1-table2 from ,
where =(+);
3-self join
select a.column1,b.column1 from a, b where
a.column1 > b.column1 order by ;

to view the sum
select sum(column-name) from ;

to view the average
select avg(column-name) from ;

to view max
select max(column-name) from ;

to view min
select min(column-name> from ;

to view some character from the column
select substr(column-name,1,3) from ;

to view in upper case
select upper(column name) from ;

to view in lower case
select lower(column-name) from ;

to view the first latter in capital
select initcap(column-name) from ;

to remove spaces from left side of the column
select * from where ltrim(column-name);
to remove spaces from right sode of the column
select * from where rtrim(column-name);

to view the system date
select sysdate from dual;

to view the structure of the table
desc

to add new column to the table
alter table add datatype;

to modify the column in table
alter table modify datatype

to view data using in operator
select from where in(‘value1’,’value2’);
to view data using not in operator
select from where not in(‘value1’,’value2’);

to create sequence
create sequence
increment by
start with
maxvalue ;
/
you can use cycle after the maxvalue so when its finished it can repeat

to modify the sequence
alter sequence
increment by
start with
maxvalue ;
/

to use sequence
insert into (sequence-name.nextval);

to create synonyms
create synonym for username.table-name;

to drop the synonym
drop synonym ;

to drop the constrain
alter table drop constraint ;

to drop the primary key
alter table drop primary key
but if the primary key was referenced to a foreign key you cant drop

to make the constraint enabled or disabled
alter table enable constraint ;
alter table disable constraint ;

to put the up title on the report
ttitle ‘the data you want to write’
to set it off
ttitle off

to put the bottom title
btitle ‘the data you want to write’
to set it of
btitle off

to let the sql print report
set serveroutput on

to save to the tables
set autocommit on

to set the line size for the table
set linesize value

to set how many rows to be displayed
set pagesize value

to set the number of the column width
set numwidth value

to set the format of the column
column format a
to set break between the rows
break on skip on report

to set for average for report
before to set average it must have break on
the on-value must match the compute on value
break on skip
compute avg of on ;

to set for sum for report
before to set sum it must have break on
break on skip
compute sum of on ;

HRMS REPORTS

FORMS GENERATION

FORMS GENERATION

goal: How to Regenerate a Form, Library or Menu
fact: Oracle Forms
fact: Oracle Application Object Library

fix:
Oracle Forms executable used to generate forms depends of platform andOracle Applications versions :
Unix Windows 95 Windows NT
10.7SC N/A f45gen.exe f45gen32.exe
10.7NCA f45gen N/A f45gen32.exe
11.0.x f45gen N/A f45gen32.exe
11.5.x f60gen N/A f60gen.exe
Commande line is :
module= userid=APPS/output_file= module_type=form batch=yes compile_all=special
Examples :
f45gen32.exe module=APXINWKB.fmb userid=apps/apps output_file=APXINWKB.fmxmodule_type=form batch=yes compile_all=special

/TEST/testora/8.0.6/bin/f60genmodule=/TEST/testappl/au/11.5.0/forms/F/ARXTWMAI.fmb userid=APPS/APPSoutput_file=/TEST/testappl/ar/11.5.0/forms/F/ARXTWMAI.fmxmodule_type=form batch=yes compile_all=special

Notes :1. If you have to generate many forms in Oracle Applications you can runadadmin, menu 'Maintain Applications Files' => 'Generate form files'.2. You could also run Forms Designer to generate a particular form, you needto be connected to the database and must verify that you have access to dependentmodules where objects may be referenced.3. If you have
errors like:

FRM-10054: Cannot attach library ...FRM-10083: Cannot Open ...FRM-18108: Failed to load the following objects...

check FORMS45_PATH or FORMS60_PATH variable, it must contain at leastresource paths where reside libraries (*.pll, *.plx files) and directorieswhere reside form source files (*.fmb).
GENERATION OF LIBRARY
Same executables used to generate forms but command line changes :
apps/module_type =library compile_all=special
Examples :
f45gen ARXTWMAI.pll apps/apps module_type =library compile_all=special

GENERATION OF MENU
It is unusual to generate a forms menu as there is only one in OracleApplications and few patches bring new version.

Syntax is:
module= userid=apps/output_file= module_type=menu batch=yes compile_all=special

Example:
f60gen module=FNDMENU.mmb userid=APPS/APPS output_file=FNDMENU.mmxmodule_type=menu batch=yes compile_all=special

goal: How to manually generate an 11.5 form on Unix - f60gen
fact: Generic Unix
fact: Oracle Application Object Library 11.5

fix:
1. Add $AU_TOP/resource to the FORMS60_PATH: $ FORMS60_PATH=$AU_TOP/resource;$FORMS60_PATH$ export FORMS60_PATH
2. Issue the following from the command prompt:
$ <8.0.6>/bin/f60gen \ module=$AU_TOP/forms//form name.fmb \ userid=APPS/ \ output_file=/forms//form name.fmx \ module_type=form \ batch=yes \ compile_all=special

BUSINESS GROUP & ORGANIZATION

MULTI OR IMPLEMENTATION SETUPS:

MULTI OR IMPLEMENTATION SETUPS:

1.Login as sysadmin responsibility

2.Ddefine required responsibilities Navigation:security->responsibility->define

3.Define user and assign responsibilities to the user. N:Security->user->define.

4.Login as GL Responsibility

5.Define accounting flexfield N:setup->financials->flexfield->key->segments

6.Give values for your segments N:setup->financials->flexfield->key->values.

7.Define Currency N:setup->Curriencies->define

8.Define Calender.N:Setup->financials->calender->Type/Accounting

9.Create SOB N:Setup->financials->book->define

10.Login as HRMS responsibility.

11.Define a location N:Work Structure->Location

12.Define a Business Group N:Works Structure->organization->description

13.Set the following Profile Options to all your responsibilitiesHR:securityHR:bisiness groupHR:User TypeGL:Set of books name

14.Login As Inventoruy responsibility

15.Create legal entity N:Setup->organizations->organizations

16.Create Operatiing unit N:Setup->organizations->organizations

17.Set Profile option Mo:Operating unit for all responsibilites which is worked at operating unit level.

18.Create Work day calender

19.Create inventory Organization. N:Setup->organizations->organizations

20.Login as sysadmin and run replicate seed data program.

XML PUBLISHER

XML Publisher

Overview: Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output in PDF, HTML, RTF, EXCEL (HTML), or even TEXT for use with EFT and EDI transmissions

Basic Need for XML: Consider the following scenarios

We have a RDF report with tabular layout which prints in English

New Requirements:

1. User1 wants the same Report needs to be printed in Spanish
2. User2 wants the Same Report needs to be printed in chart format
3. User3 wants the Same Report output in Excel
4. User4 wants the Same Report output to be published on intranet or internet
5. User5 wants the Same Report output eliminating few columns and adding few other

A new RDF needs to be created for each requirement stated above or an existing RDF needs to be modified with huge amount of effort but where as with XML Publisher it can be done very easily.

XML Publisher separates a report’s data, layout and translation components into three manageable pieces at design time; at runtime all the three pieces are brought back together by XML Publisher to generate the final formatted, translated outputs like PDF, HTML, XLS and RTF. In future, if any there is any change in layout we just need to add/modify the Layout file

Data Logic – Data extracted from database and converted into an XML string.

Layout - The layout templates to be used for the final output are stored and managed in the Template Manager.

Translation -The translation handler will manage the translation that is required at runtime

In brief the steps are as follows:-
a. Create a procedure and register it as Concurrent Program so that we write XML tags into output file.
b. Build a Data Definition & XML Template using XML Publisher.
c. Create a relation between XML Template & Concurrent Program and run the concurrent program

Requirements for XML Data Object Reports

1. Oracle XML Publisher Release 5.5 patch 4206181
2. Template Builder 5.5

Template builder is used to create template/layout for your report. Usually Template builder 5.5 is available in Oracle XML Publisher patch itself but you can also download it from http://edelivery.oracle.com. First select Oracle Application Server Products then select your platform and then locate the Oracle® XML Publisher Release 5.6.2 Media Pack v1 for Microsoft Windows, as below:

Download the Desktop edition from the below:

When you download the XML Publisher Desktop edition you get a Zip file containing setup for XML Publisher Desktop Install Shield, this installs some components into Microsoft Word.

After installing, the Word Add-Ins is attached to the menu bar for the word document. This menu lets you attach an XML data source document, add the XML data to your template, set preferences and preview the output.

In detail along with screenshots:-

A concurrent program is written that spit out an XML file as output Such concurrent program can be of type SQL or PL/SQL or Oracle Report or any other supportable type, provided it can produce a XML output.

1.Here I have a very simple PL/SQL procedure, which fetch the records from AR tables and write the output in xml tags.

CREATE OR REPLACE PROCEDURE APPS.Demo_XML_Publisher(errbuf VARCHAR2,retcode NUMBER,v_customer_id VARCHAR2)

AS

/*Cursor to fetch Customer Records*/

CURSOR xml_parent

IS

SELECT customer_name , customer_id

FROM ra_customers

WHERE customer_id = to_number(v_customer_id);

/*Cursor to fetch customer invoice records*/

CURSOR xml_detail(p_customer_id1 NUMBER)

IS

SELECT ra.customer_trx_id customer_trx_id, ra.ship_to_customer_id ship_to_customer_id, ra.trx_number trx_number,aps.amount_due_original ams

FROM ra_customer_trx_all ra, ar_payment_schedules_all aps

WHERE ra.ship_to_customer_id = p_customer_id1

AND aps.customer_trx_id = ra.customer_trx_id

AND ROWNUM<4;

BEGIN

/*First line of XML data should be ‘’*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

FOR v_customer IN xml_parent

LOOP

/*For each record create a group tag at the start*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

/*Embed data between XML tags for ex:- ABCD*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'' || v_customer.customer_name

|| '');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'' || v_customer.customer_id ||

'');

FOR v_details IN xml_detail(v_customer.customer_id)

LOOP

/*For customer invoices create a group tag at the

start*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'' ||

v_details.customer_trx_id || '');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'' ||

v_details.ship_to_customer_id || '');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,''||

v_details.trx_number||'');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,''||

v_details.trx_number||'');

/*Close the group tag at the end of customer invoices*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

END LOOP;

/*Close the group tag at the end of customer record*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

END LOOP;

/*Finally Close the starting Report tag*/

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'');

exception when others then

FND_FILE.PUT_LINE(FND_FILE.log,'Entered into exception');

END Demo_XML_Publisher;

/

2. Create an executable SampleXmlReport for the above procedure Demo_XMML_Publisher.

Go to Application Developer Responsibility->Concurrent->Executable

3. Create a new concurrent program SampleXmlReport that will call the SampleXmlReport executable declared above. Make sure that output format is placed as XML.

Go to Application Developer Responsibility -> Concurrent ->Program

4. Make sure we declare the parameters for the procedure.

5. Add this new concurrent program with Receivables request group. Either using the following code or through below application screen.

DECLARE
BEGIN
FND_PROGRAM.add_to_group
(
PROGRAM_SHORT_NAME =>'CUST_XML_SAMPLE'
,PROGRAM_APPLICATION =>'AR'
,REQUEST_GROUP => 'Receivables All'
,GROUP_APPLICATION =>'AR'
) ;
commit;
exception
when others then
dbms_output.put_line('Object already exists');
END ;
/

Page 1|2 Next>>

XML Publisher Fuse

Allows community to build bookmarks on XML Publisher.