Friday, July 15, 2016

COUNT FUNCTIONALITIES IN SQL

count(*):

It selects all the columns in the table and then count the rows in a table

Example:select  count(*) from emp;

count(1):

It selects the first column from the table and then count the rows and including null values also.

Example:select  count(1) from emp;

count(column name):

It select the column and then count all the rows except null values

Example:select  count(empno) from emp;

count(distict column name):

It will count the rows in  column with distinct values

Example: select  count( distinct empno) from emp;

Scheduling concurrent program in Oracle Apps

Now let us see how we schedule a concurrent program in Oracle Apps.
1. Go to the respective responsibility where the concurrent program to be scheduled exists. Then go to View > Requests and click on “Submit a New Request”
Sch1
2. Select “Single Request” and click on OK button
Sch2
3. Enter the concurrent program name to be scheduled and the respective parameters and click on Schedule button
Sch3
4. This will open up the Schedule window.
Sch4
5. Schedule options: The below are the available scheduling options, we will see what they mean.
(i) As Soon as Possible: Selecting this option will kick off the concurrent program immediately.
(ii) Once: Selecting this option lets you specify at what time and date you want to kick off the concurrent program. The concurrent program will be submitted only once at the specified time and date.
Sch5
(iii) Periodically: Lets you schedule the program at the specified interval like once a month/week/day/hour/minute.
Sch6
Description of the fields and their importance:
Start At: Enter the date and time when you want to start the schedule
End At: Enter the end date for the schedule. Leaving this blank will run indefinitely.
Re-run every: Specify the interval you want to run like for example – every 1 day or every 2 months.
Apply the interval: How do you want to apply the interval from the start of the prior run or from the completion of the prior run.
Increment date parameters each run: Use this when one of the parameters for your program is a date.
If you check this, the date parameter will be incremented for each run.
If not checked, the date given in the parameter will not increment, it will remain the same as entered while scheduling for every run.
Apply a Saved Schedule: If more than one program has to be scheduled in a similar fashion, instead of entering the schedule details for each and every program, you can save the schedule and apply the same schedule for the remaining programs.
(iv) On Specific Days: Use this if you want to run the program for example on 5th day of every month or on Tuesday of every week,. This option lets you choose on what days of every month you want to run a program.
Sch7
6. Once you specify the interval/frequency you want the program to run, click on OK button. (I have set it to run once a day at 2:00 P.M starting from 23-Feb-2014 till 26-Feb-2014 as seen in the below screenshot)
Sch8
7. Click on Submit button.
Sch9
8. Upon querying for the request in the requests window, you see our program with status scheduled. When it’s time, the program will be executed.
Sch10
This is how you schedule a program to run at a specified frequency or interval.
9. At any point of time, if you need to change the schedule, you can select the scheduled request and click on View Details button
Sch11
Then click on Schedule button. Here you can change the schedule as per your wish.
Sch12
10. If you wish to cancel a scheduled request, select the request in the Requests window and click on Cancel Request in Requests window.
Sch13
This will prevent the program from running. If you see the below screenshot, status is cancelled.
Sch14

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...