Topics: Recurring Journals, Revaluation, Mass Allocation, Tax journals & CVR

1.Why my cross validation rules not working?

·         The desired combination may be already used.

·        The combination entered manually in account combination.

·        If you uncheck the cross validate segments in key flex field segment form.

·        If we uncheck enable check box at define cross validation rule.

         

2.List any six-validation reports of calendar.

• The following periods have a date gap.
• The following periods overlap.

·         The following periods number are greater then the maximum period number for this period.           

·         The following periods number are missing.

·         The following periods number are not in sequential order by date.

·         The following quarters are missing.

·         The following quarters are not in sequential order by period.

·         The following periods start or end rates are more than one year before or after its fiscal year.

3.Briefly explain the steps for Mass Allocation.

Allocation of account balance to the range of segment values for specific period.

Steps for mass allocation.

·         Define parent Segment and assign child ranges.

(N): Setup/Financial/Flex fields/Validation

·         Enter stat journals.

·         Define mass allocation Formula. (N): Journal/Define/Allocation

·          Validate the formula.

·         Generate Mass Allocation.

·         Post the entry.

Q4.Difference between Translation & Revaluation

Translation	Revaluation
Translation is done from functional currency to Reporting currency.	Revaluation is done to know the actual balance on specific date with respect the prevailing foreign exchange rate of a particular account.
In translation Period end rate & Average rates are used.	In Revaluation Only Period end rate is used.
Translation is Optional.	Revaluation is mandatory.
Difference in Translation will be transferred to “Translation adjustment account”.	Difference in Revaluation will be transferred to “Unrealized Gain/loss account”.
Translation should satisfy the conditions, namely, 1. Prior & following period should be opened; 2. It cannot perform for the first period	No Condition is required for revaluation.

5.How do I enable Cross Validation?

Check the Cross validate Segments and Check the Freeze flexfield defination and compile in Key Flexfield segments.

6.Can you define cross validation rules for a set of books?

No, Cross validation rules are specific to chart of account.

7.Can you run revaluation again in a previous period?

Yes, we can run revaluation again in a previous period. But the revaluation will effect the transaction, which are entered after the First revaluation.

8. On what basis auto post criteria set will be created.

On the basis of Priority, Source, Category, Balance type & Period the auto post criteria set will be created.

9.Briefely explain the purpose of Revaluation.

Revaluation is done to know the actual balance on specific date with respect the prevailing foreign exchange rate of a particular account.

10. Is it possible to do Translation for more than one segment at a time?

Yes. In translation you having the option “All” under balancing segment TAB by selecting it we can do the translation for more than one segment.

11. Can you generate Recurring journal more than once in one period?

Yes, recurring journal means Journal, which are repetitive in nature. In a particular period a journal may be expected for more than once .So we can generate Recurring Journal more than once in one period.

12.What are the steps for revaluation?

Steps for Revaluation:

·         Define the daily rate using the conversion rate and type.

·         Define the period end rate.

·         Define unrealized gain /loss account

·         Perform Revaluation for the particular account.

13. Can you allocate part of your cost pool account by using Mass Allocation? If yes, how will you define formula?

Yes, by entering desired amount in the amount column of cost pool A/c. or by entering base number in the amount column of sum of usage factor.

14 What are the available segment types in mass allocation?

Three segment types are available in mass allocation.

Constant (C), Looping   (L), Summing (S)

15.Explain the concept of recurring journals.

Recurring journals means journals, which are repetitive nature in a year. On the basis of amount recurring journals are three types.

·         Skeleton journal

·         Standard journal

·         Formula journal

Skeleton journal means journals have same account but have different posting amounts.

Standard journals means journal entries use the same accounts and amounts in each period.

Formula journal means journal entries use formulas to create journal amounts that vary from period to period.

16.Do you think Translation and Revaluation are mutually exclusive?

Yes, The translation and revaluation independent with each other, there is no           relation between translation and Revaluation. We can have both in a single set of book.  

17.Can I use stat journal of previous period for mass allocation.

Yes, you can use stat journal of previous period by Using amount type as PTD and period type as previous period in mass allocation or by using amount type as YTD and period type is current period you can use the stat journal for the entire year.

18.What is the difference between full and incremental allocation.

  

Full Allocation	Incremental Allocation
Full allocation method to generate journals that reverse previous allocations or to post new allocation amounts.	Incremental allocation method when you want to update allocated balances without reversing the previous allocation batches.
Recommend that you use this method only if you are allocating amounts for the first time, or only once.	Recommend that you do not use the incremental method the first time you generate a Misallocation entry.

19.Is there any difference between definition of period rates and historical rates?

Period rate	Historical rate
The daily exchange rate on the last day of an accounting period	A weighted-average rate for transactions that occur at different times
In translation period end rate is used to translate the assets and liability account balances.	Historical rates to translate owner's equity accounts

Interview Questions on Oracle AR (Account Receivables) 

Questions in Accounts Receivables

1. How do you create a Chargeback Invoice ?
A. Using Receipts window

2. How do you adjust the amount of advance already received against an Invoice ?
A. Create an Invoice for Deposit and receive an amount against this deposit Invoice. Then Create the
regular invoice and in the column of Commitments enter the number of Deposit Invoice. The Regular
Invoice gets matched with the Deposit Invoice for the amount of Regular invoice or deposit invoice which
ever is lower
3. How do you write off small amounts while accounting the receipt against an invoice?
A. Using Receipts window. You have the button for write offs.
4. How do you Account for bank charges deducted from amount received against an invoice?
A. Using Quick cash window also you can enter receipts. Here you have the option of accounting the
bank charges deducted on receipt. However, this has to be enabled by putting the Value in profile option
AR: Create Bank Charges = YES.
5. How do you create a credit note against an invoice ?
A. You have separate window to create such credit note. Navigation Transactions => Credit Transactions.
6. How do you adjust a regular Invoice with a Credit Note ? Enter the amount 0 in receipt window and in
invoice matching window select the invoice as well as the credit note. This will knock off the invoice
against the credit note.
7. What are different types of Receipt Reversals ? What is the difference between them ? What are the
accounting entries ?
A. Standard Reversal and Debit Note Reversal. Standard Reversal reopens the invoice matched in that
receipt. You can match a new receipt against this invoice.
Debit Note Reversal does not reopens the earlier matched invoice but it creates a new debit note which
can be matched with another receipt.
8. When are the following accounts are used
Unbilled Receivable
Unearned Revenue
A. When you use Invoicing Rule, the receivables are accounted on different dates as defined in the rule.
Till such time receivables are accounted, the amount is debited to Unbilled Receivables.
If you use Accounting Rule, the revenue is accounted on different dates. Till such time revenue is
accounted, the amount is credited to Unearned Revenue..
9. What is the difference between Unidentified Receipt, Unapplied Receipts and On Account Receipts ?
What are the Accounting Entries for each of this ?
A. Unidentified Receipts: The Customer is yet to be identified and so receipt is not matched.
Unapplied Receipts: The Customer is identified and entered but the amount is not matched with any of his
invoice.

done-Unix-sysadmin-scripts.txt
*********Abbreviations******************
<CT>Means Customer of Custome Schema name,<PROD> means product short code eg IEX for collections
*******************Download Functions*******************************
#!/bin/sh
FNDLOAD $1 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct $2_FN.ldt FUNCTION
FUNCTION_NAME="$2"
****Usage
download_function.sh $1 <CT>_INT_RENT_FUN
download_function.sh $1 <CT>_<PROD>_LA_CONTRACT_TERMSCOND
download_function.sh $1 <CT>_<PROD>_AST_ADDL_INFO
********************Download Lookups********************************
#
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct $2_LT.ldt FND_LOOKUP_TYPE
APPLICATION_SHORT_NAME="$3" LOOKUP_TYPE="$2"
*****Usage
download_lookup.sh $1 <CT>_<PROD>_TC_LINKS <PROD>
download_lookup.sh $1 <CT>_VSC_ROLE_ALLOCATION
*********************Download Message*****************************
#!/bin/sh
FNDLOAD $1 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct $2_MSG.ldt FND_NEW_MESSAGES
APPLICATION_SHORT_NAME="$3" MESSAGE_NAME="$2"
****Usage
download_message.sh $1 <CT>_<PROD>_EXIST_ACCOUNT_NUM <PROD>
download_message.sh $1 <CT>_<PROD>_VALID_ACCOUNT_NUM <PROD>
download_message.sh $1 <CT>_<PROD>_VALID_BUSIN_NAME <PROD>
download_message.sh $1 <CT>_INT_RENT_AMT_MSG BOLINF
download_message.sh $1 <CT>_INT_RENT_PERIOD BOLINF
download_message.sh $1 <CT>_INT_RENT_REQ_MSG BOLINF
************************Download Profiles*************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct $2_PO.ldt PROFILE
PROFILE_NAME="$2" APPLICATION_SHORT_NAME="$3"
*****Usage
#download_profile.sh $1 <CT>_<PROD>_LA_ASSET_RG BOLINF
download_profile.sh $1 <CT>_<PROD>_VSC_UPDATABLE BOLINF
download_profile.sh $1 <CT>_VENDOR_COMM_STREAM BOLINF
download_profile.sh $1 <CT>_VENDOR_COMM_STREAM2 BOLINF
*****************************Download Programs*******************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct $2_CP.ldt PROGRAM
Page 1done-Unix-sysadmin-scripts.txt
APPLICATION_SHORT_NAME="$3" CONCURRENT_PROGRAM_NAME="$2"
*****Usage
#download_program.sh $1 <CT>_<PROD>_PAY_EVRGRN_DISB BOLINF
#download_program.sh $1 <CT>_EVERGREEN_BILLING BOLINF
#download_program.sh $1 <CT>_PAY_SERVICE_PASSTHROUGH
***************************Download Regions************************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
java oracle.apps.ak.akload apps $3 THIN "$database_name" DOWNLOAD $1.jlt GET CUSTOM_REGION $2 $1
******Usage
download_region.sh <CT>_<PROD>_RGP <PROD> $1
download_region.sh <CT>_SEARCH <PROD> $1
download_region.sh <CT>_CASES_ALL_R BOLINF $1
****************************Download Rule*************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $2_DFF.ldt DESC_FLEX
P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="<PROD>"
DESCRIPTIVE_FLEXFIELD_NAME="<PROD> Rule Developer DF" P_CONTEXT_CODE="$2"
****Usage
# interim rent
download_rule.sh $1 <CT>CNTINTRENT
download_rule.sh $1 <CT>VENINTRENT
***************************Download Value set************************
#!/bin/sh
FNDLOAD $1 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct $2_VS.ldt VALUE_SET
FLEX_VALUE_SET_NAME="$2"
*****Usage
download_valueset.sh $1 <CT>_NUMBER_38_2
#download_valueset.sh $1 <CT>_<PROD>_EGS_STY_TYPE
#download_valueset.sh $1 <CT>_PREPAYMENT_OFFSET
*******************************Replace strings in files*************
#!/bin/sh
# Perform a global search for string 'aaaaa' application ID and replace on each of several files
# File names listed explicitly
echo "Enter Application ID : "
read APPL_ID
for text_file in <CT>_ALL_CASES.jlt <CT>_EXISTING_CASES.jlt <CT>_UNASSIGNED_CASES.jlt
do
 echo "Editing file $text_file"
 sed -e s/aaaaa/$APPL_ID/g $text_file > temp
 mv -f temp $text_file
done
******************************Upload dff*****************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $2
Page 2done-Unix-sysadmin-scripts.txt
******************************Upload form****************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $2
*****************************Upload Functions************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct $2
*****************************Upload jtf grids************************
FNDLOAD $1 0 Y UPLOAD $JTF_TOP/patch/115/import/jtfgrid.lct $2
*****************************Upload Lookup***************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct $2
*****************************Upload Messages*************************
FNDLOAD $1 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct $2
*****************************Upload Profiles*************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct $2
*****************************Upload programs*************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $2
*****************************Upload Region***************************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
java oracle.apps.ak.akload apps $1 THIN "$database_name" UPLOAD $2 UPDATE
AMERICAN_AMERICA.WE8ISO8859P1
*****************************Upload Valueset***************************
FNDLOAD $1 O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $2
*****************************Upload WorkFlow Notification**************
#!/bin/sh
p=`tnsping $TWO_TASK| grep Attempting|awk '{print $4}'`
database_name='(DESCRIPTION='$p'(CONNECT_DATA=(SID='$TWO_TASK')))'
pp=`echo $p| awk '{gsub(/=/, " "); print}'| awk '{gsub(/)/, " "); print}'`
DB_HOST=`echo $pp|awk '{print $5}'`
DB_PORT=`echo $pp|awk '{print $7}'`
java oracle.apps.fnd.wf.WFXLoad -u apps $1 $DB_HOST:$DB_PORT:$TWO_TASK thin US $2
*****Usage
WFLOAD $1 0 Y FORCE $2
*********************SQL Loader Sample*************************************
LOAD DATA
INFILE '$XBOL_TOP/bin/TD9246.csv'
BADFILE '$XBOL_TOP/bin/TD9246.bad'
INSERT
INTO TABLE <CT>_9185_MJE_CRE
fields terminated by ',' trailing nullcols
(
REC_ID recnum,            
Contract_Number,  
Transaction_Number,
Description,      
Amount ,          
Line_Number,      
line_Description,  
Template_Name,    
Page 3done-Unix-sysadmin-scripts.txt
Line_Amount,      
Stream_Type,      
Purpose )
***********saved in csv format with following sample data.*******************
001.60000656.0001 EVG001 Correct Evergreen Erroneous    Expiry -101.56 1 Correct  
Evergreen Erroneous Expiry Termination <CT> Evergreen Rent Accrual -101.56
******************Other sample of SQL loader script************************
LOAD DATA
INFILE '$XBOL_TOP/bin/TD8657.csv'
REPLACE
INTO TABLE <CT>_TD8657_STAGE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
( CONTRACT_NUMBER CHAR
, MLA_CONTRACT_NUMBER CHAR NULLIF MLA_CONTRACT_NUMBER   = BLANKS
"TRIM(:MLA_CONTRACT_NUMBER)"
, PRIVATE_LABEL CHAR NULLIF PRIVATE_LABEL = BLANKS "T    RIM(:PRIVATE_LABEL)"
, STATUS_CODE CONSTANT 'L'
)
LOAD DATA
INFILE '$XBOL_TOP/bin/TD8982.csv'
REPLACE
INTO TABLE <CT>_VENDOR_AMENDMENT_TBL
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(
CUR_VENDOR_NO                   NULLIF (CUR_VENDOR_NO="NULL"),
CUR_VENDOR_NAME            CHAR,
CUST_POST_CODE_FROM        CHAR NULLIF CUST_POST_CODE_FROM = BLANKS,  
CUST_POST_CODE_TO          CHAR NULLIF CUST_POST_CODE_TO = BLANKS,
CONTRACT_NUMBER            CHAR,
NEW_VENDOR_NO                   NULLIF (NEW_VENDOR_NO="NULL"),
NEW_VENDOR_NAME            CHAR,
NEW_VENDOR_SITE            CHAR,
NEW_VPA                    CHAR NULLIF NEW_VPA = BLA    NKS,
CUSTOMER                   CHAR NULLIF CUSTOMER = BL    ANKS,
CUSTOMER_ACCOUNT           CHAR NULLIF CUSTOMER_ACCOUNT = BLANKS,
CUSTOMER_BILL_TO_SITE_ID NULLIF (CUSTOMER_BILL_TO_SITE_ID="NULL"),
PAYMENT_METHOD             CHAR NULLIF PAYMENT_METHOD           = BLANKS,
BANK_ACCOUNT_NAME          CHAR NULLIF BANK_ACCOUNT_NAME        = BLANKS,
INVOICE_FORMAT             CHAR NULLIF INVOICE_FORMAT           = BLANKS,
REVIEW_INVOICE             CHAR NULLIF REVIEW_INVOICE           = BLANKS,
REASON_FOR_REVIEW          CHAR NULLIF REASON_FOR_REVIEW        = BLANKS,
REVIEW_UNTIL_DATE          CHAR NULLIF REVIEW_UNTIL_DATE        = BLANKS,
STATUS CONSTANT 'L'                
)
*********************************************Patch level***********************
select decode(bug_number,
2194408, '11i.<PROD>.A',
2269974, '11i.<PROD>.B',
2375622, '11i.<PROD>.C',
2420911, '11i.<PROD>.D',
2698797, '11i.<PROD>.E',
Page 4done-Unix-sysadmin-scripts.txt
3101250, '11i.<PROD>.F',
3981693, '11i.<PROD>.G',
4143569, 'GRUP.1',
4298372, 'GRUP.2',
4487651, 'GRUP.3',
4956746, 'GRUP.4',
4551977, '11i.<PROD>.H',
5088908, 'HRUP.1',
5196112, 'HRUP.2',
5350898, 'HRUP.3',
5701628, 'HRUP.4',
6370848, 'HRUP.5'),
to_char(bug_number)
, creation_date
from apps.ad_bugs
where bug_number in
('2194408','2269974','2375622','2420911','2698797','3101250','3981693','4143569','4298372','4487651','4956746','
4551977','5088908'
,'5196112','5350898','5701628','6370848')
order by creation_date;
*******************************************Fulfilment*********************
SELECT a.server_instance_id "Server Instance Id",
       a.server_group_id "Server Group Id", a.dns_name "DNS Name",
       a.status_code "statusCode",c.name "groupName"
FROM amf_server_instances_b a, amf_server_instances_tl b , amf_server_groups_tl c
WHERE (a.server_instance_id = b.server_instance_id
AND c.server_group_id = a.server_group_id
AND c.language(+) = userenv('lang')
AND b.language(+) = userenv('lang') AND a.status_code <> 'E' )
*********************Long Running*****************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
sqlplus -s <<!
olmuser/olmuser
spool longrun.txt
set feedback on
set linesize 1000
col RequestName format a35
select request_id,(d.user_concurrent_program_name) RequestName,
round((a.actual_completion_date - a.actual_start_date)* 86400/60)"Minutes",
to_char(actual_start_date,'DD-Mon-YYYY HH24:MI:SS')"Start Date",
to_char(actual_completion_date,'DD-Mon-YYYY HH24:MI:SS')"End
Date",status_code,a.phase_code,c.user_name
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_user
c,apps.fnd_concurrent_programs_vl d
where a.concurrent_program_id =b.concurrent_program_id
and (a.concurrent_program_id,a.request_id,a.actual_completion_date)
in(select concurrent_program_id,request_id,max(actual_completion_date) from apps.fnd_concurrent_requests
where to_char(actual_completion_date,'DD-Mon-YYYY HH24:MI:SS')
between to_char(sysdate-12/24,'DD-Mon-YYYY HH24:MI:SS')
and to_char(sysdate,'DD-Mon-YYYY HH24:MI:SS')
group by parent_request_id,concurrent_program_id,request_id)
and a.actual_start_date is not null
and a.actual_completion_date is not null
and a.requested_by = c.user_id
and b.concurrent_program_name = d.concurrent_program_name
and ((a.actual_completion_date - a.actual_start_date)* 86400/60) > 90
Page 5done-Unix-sysadmin-scripts.txt
order by actual_start_date;
spool off
exit;
!
if [ `cat longrun.txt|wc -l` -gt 0 ]
then
       mailx -s "Requests Running for more than 1 1/2 hr" vaseem.mohammed@ge.com < longrun.txt
fi
rm longrun.txt
*****************************Profile changes***********************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
spool profile_values.txt
set feed off
set linesize 150
set pagesize 1000
col ProfileLevel format A10
col Non-SiteDesc format A15
col UserName   format A10
col ProfileOptionName format A30
col Value format A5
col LastUpdatedBy format A13
col LastUpdatedOn format A15
SELECT DECODE(level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'USER') "ProfileLevel"
           ,
DECODE(level_id,10001,NULL,10002,fa.application_name,10003,fr.responsibility_name,10004,fu.user_name)
"Non-SiteDesc"
                , fu.user_name                  "UserName"
                , fpov.user_profile_option_name "ProfileOptionName"
                , fpova.profile_option_value    "Value"
                , fu2.user_name                 "LastUpdatedBy"
                , to_char(fpova.LAST_UPDATE_DATE,'mm/dd/yy hh24:mi')       "LastUpdatedOn"
FROM apps.fnd_profile_options_vl fpov
        , apps.fnd_profile_option_values fpova
        , apps.fnd_application_tl fa  -- table inclusion when looking at application joins
        , apps.fnd_responsibility_tl fr  -- table inclusion when looking at responsibility joins
        , apps.fnd_user fu  -- table inclusion when looking at user joins
        , apps.fnd_user fu2
WHERE fpov.application_id = fpova.application_id
AND fpov.profile_option_id = fpova.profile_option_id
AND start_date_active <= SYSDATE
AND NVL(end_date_active,SYSDATE) >= SYSDATE
AND (site_enabled_flag = 'Y' OR app_enabled_flag = 'Y'
     OR resp_enabled_flag = 'Y' OR user_enabled_flag = 'Y')
AND fpova.level_value = fa.application_id (+) -- join for application values
AND fpova.level_value = fr.responsibility_id (+)  -- join for responsibility values
AND fpova.level_value = fu.user_id (+) -- join for user values
AND fpova.last_updated_by = fu2.user_id (+)  -- join for update by user values
and trunc(fpova.LAST_UPDATE_DATE) = trunc(sysdate-1)
ORDER BY "ProfileOptionName", "ProfileLevel";
Page 6done-Unix-sysadmin-scripts.txt
spool off
exit
!
   if [ `cat profile_values.txt|wc -l`  -gt 0 ]
then
mailx -s " Profile Values updated in PGECAI on `date -d yesterday +%d-%m-%y` " glmoperationsteam@ge.com  <
profile_values.txt
rm profile_values.txt
fi
**************************************Connected Users***********************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
spool invalid_1.alert
select count(distinct(d.USER_NAME)) "users connected to production", to_char(sysdate,'MM-DD-YYYY
HH:MM:SS') "Time"
 from apps.fnd_logins a,
gv\$session b, gv\$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
spool off
exit;
!
   if [`cat invalid_1.alert|wc -l`  -gt 0 ]
then
                    cat invalid_1.alert >> invalid_2.alert
            #    mailx -s  "Users information "  vaseem.mohammed@ge.com < invalid_2.alert
fi
********************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool invalid_1.alert
select count(distinct(d.USER_NAME)) "users connected to production",TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY
HH24:MI:SS')"Current Time" from apps.fnd_logins a,gv\$session b, gv\$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
Page 7done-Unix-sysadmin-scripts.txt
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);
spool off
exit;
!
   if [`cat invalid_1.alert|wc -l`  -gt 0 ]
then
cat invalid_1.alert >> invalid_2.alert              
rm invalid_1.alert
#mailx -s  "Users information "  glmgenpactappsdba@ge.com < invalid_1.alert
fi
*************************************
. /pgecai/oracle/product/920/PGECAI_auohsgeca34.env
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool invalid_1.alert
select count(distinct user_id) "users",TO_CHAR(SYSDATE, 'Dy DD-Mon-YYYY HH24:MI:SS')"Current Time"
 from apps.icx_sessions where  last_connect > sysdate - 2/24 and user_id != '-1';
spool off
exit;
!
   if [`cat invalid_1.alert|wc -l`  -gt 0 ]
then
cat invalid_1.alert >> invalid_2.alert              
rm invalid_1.alert
#mailx -s  "Users information "  glmgenpactappsdba@ge.com < invalid_1.alert
fi
**************************************Temp TableSpace usage************************************
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
rm temp_tablespace.alert
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name from v\$database;
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
Page 8done-Unix-sysadmin-scripts.txt
exit;
!
   if [`cat temp_tablespace.alert|wc -l`  -gt 0 ]
then
cp temp_tablespace.alert /pgecai/backup/temp_tablespace.log
mailx -s  "Temp TABLE SPACE USAGE report  IN PGECAI in all nodes" balaji.s@ge.com  <
/pgecai/backup/temp_tablespace.log
rm /pgecai/backup/temp_tablespace.log
fi
*************************************Tablespace usage******************************************
#!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name "Database Name"  from v\$database;
column used_space format 99,999,999,999
column free_space format 99,999,999,999
column total_space format 99,999,999,999
column largest_extent format 99,999,999,999
column tablespace_name format a20
column pctfull format 999
select t.tablespace_name,t.total_space,
        nvl(f.free_space,0) free_space,
        nvl(f.largest_extent,0) largest_extent,
        total_space-nvl(f.free_space,0) used_space,
        (total_space-nvl(f.free_space,0))*100/total_space pctfull
  from
   (select tablespace_name, sum(bytes)/(1024*1024) total_space
        from dba_data_files group by tablespace_name ) t,
   (select tablespace_name, sum(bytes)/(1024*1024) free_space,
        max(bytes)/(1024*1024) largest_extent
        from dba_free_space group by tablespace_name)  f
        where t.tablespace_name=f.tablespace_name (+);
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') "Date " from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
   if [`cat temp_tablespace.alert|wc -l`  -gt 0 ]
then
Page 9done-Unix-sysadmin-scripts.txt
mailx -s  " TABLE SPACE USAGE report -  IN PGECAI " glmgenpactappsdba@ge.com martin.hodge@ge.com <
temp_tablespace.alert
rm temp_tablespace.alert
fi
*************************************Misc*************************************
tablespace_usage.sh                                                                                 0100700 0073471 0001747 00000003230
10604431157 013755  0                                                                                                    ustar   c_bsanth              
      g999                                                                                                                                                                      
                                          #!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name "Database Name"  from v\$database;
column used_space format 99,999,999,999
column free_space format 99,999,999,999
column total_space format 99,999,999,999
column largest_extent format 99,999,999,999
column tablespace_name format a20
column pctfull format 999
select t.tablespace_name,t.total_space,
        nvl(f.free_space,0) free_space,
        nvl(f.largest_extent,0) largest_extent,
        total_space-nvl(f.free_space,0) used_space,
        (total_space-nvl(f.free_space,0))*100/total_space pctfull
  from
   (select tablespace_name, sum(bytes)/(1024*1024) total_space
        from dba_data_files group by tablespace_name ) t,
   (select tablespace_name, sum(bytes)/(1024*1024) free_space,
        max(bytes)/(1024*1024) largest_extent
        from dba_free_space group by tablespace_name)  f
        where t.tablespace_name=f.tablespace_name (+);
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') "Date " from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
   if [`cat temp_tablespace.alert|wc -l`  -gt 0 ]
then
mailx -s  " TABLE SPACE USAGE report -  IN PGECAI " glmgenpactappsdba@ge.com martin.hodge@ge.com <
temp_tablespace.alert
Page 10done-Unix-sysadmin-scripts.txt
rm temp_tablespace.alert
fi
                                                                                                                                                                                     
                                                                                                                             
temp_tablespace_usage.sh                                                                            0100700 0073471 0001747
00000001655 10601711012 014777  0                                                                                                    ustar  
c_bsanth                        g999                                                                                                                                      
                                                                           #!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
rm temp_tablespace.alert
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool temp_tablespace.alert
select name from v\$database;
select current_users,TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
FREE_BLOCKS, free_extents,used_extents from v\$sort_segment;
select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;
select tablespace_name, bytes_used/(1024*1024) "MBUsed", bytes_free/(1024*1024) "MBFree"
from v\$temp_space_header;
exit;
!
   if [`cat temp_tablespace.alert|wc -l`  -gt 0 ]
then
cp temp_tablespace.alert /pgecai/backup/temp_tablespace.log
mailx -s  "Temp TABLE SPACE USAGE report  IN PGECAI in all nodes" balaji.s@ge.com  <
/pgecai/backup/temp_tablespace.log
rm /pgecai/backup/temp_tablespace.log
fi
                                                                                   test.sh                                                                                      
    0100700 0073471 0001747 00000000727 10607131650 011453  0                                                                      
                            ustar   c_bsanth                        g999                                                                                              
                                                                                                                  #!/bin/bash
ORACLE_HOME=/pgecai/oracle/product/920
TNS_ADMIN=/pgecai/oracle/product/920/network/admin/PGECAI_auohsgeca34
PATH="$PATH:$ORACLE_HOME/bin"
export ORACLE_HOME PATH TNS_ADMIN
sqlplus -s <<!
olmuser/olmuser@pgecai
set feed off
set linesize 100
set pagesize 200
spool test.alert
select name "Database Name"  from v\$database;
exit;
!
Page 11done-Unix-sysadmin-scripts.txt
   if [`cat test.alert|wc -l`  -gt 0 ]
then
mailx -s  " Test report -  IN PGECAI " balaji.s@ge.com < test.alert
rm test.alert
fi
/**************************Begin Create user & update responsibilities***********************************/
use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.In case a user does not exist, then new user will be
created with default password being oracle123.You can set FND_GLOBAL variables by running the script below,
or by running fnd_global.apps_initialize
DECLARE
v_session_id INTEGER := userenv('sessionid') ;
BEGIN
dbms_application_info.set_client_info ( 101 ) ; --put ur org_id here
fnd_global.initialize
(
SESSION_ID=>v_session_id
,USER_ID =>18594
,RESP_ID =>4650125
,RESP_APPL_ID =>8402
,SECURITY_GROUP_ID =>0
,SITE_ID =>NULL
,LOGIN_ID =>3115003
,CONC_LOGIN_ID =>NULL
,PROG_APPL_ID =>NULL
,CONC_PROGRAM_ID =>NULL
,CONC_REQUEST_ID =>NULL
,CONC_PRIORITY_REQUEST =>NULL
) ;
commit ;
END ;
/
DECLARE
  --By: First Last
  --When Jun-2007
  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');
  result    BOOLEAN;
  v_user_id INTEGER;
  FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
    p_check c_check%ROWTYPE;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    IF c_check%FOUND
    THEN
      /*Yes, it exists*/
Page 12done-Unix-sysadmin-scripts.txt
      CLOSE c_check;
      RETURN TRUE;
    END IF;
    CLOSE c_check;
    RETURN FALSE;
  END check_fu_name;
BEGIN
  IF NOT (check_fu_name(p_user_name => v_user_name))
  THEN
    fnd_user_pkg.createuser(x_user_name                  => v_user_name
                           ,x_owner                      => ''
                           ,x_unencrypted_password       => 'oracle123'
                           ,x_session_number             => v_session_id
                           ,x_start_date                 => SYSDATE - 10
                           ,x_end_date                   => SYSDATE + 100
                           ,x_last_logon_date            => SYSDATE - 10
                           ,x_description                => 'Anil Passi'
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000
                           ,x_employee_id                => NULL /*Change this id by running below SQL*/
                            /*  
                             SELECT person_id,full_name
                             FROM   per_all_people_f
                             WHERE  upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
                             GROUP  BY person_id,full_name
                                                             */
                           ,x_email_address => ' last.first@gmail.com'
                           ,x_fax           => ''
                           ,x_customer_id   => ''
                           ,x_supplier_id   => '');
   dbms_output.put_line ( 'FND_USER Created' ) ;
  ELSE
    fnd_user_pkg.updateuser(x_user_name                  => v_user_name
                           ,x_owner                      => 'CUST'
                           ,x_end_date                   => fnd_user_pkg.null_date
                           ,x_password_date              => SYSDATE - 10
                           ,x_password_accesses_left     => 10000
                           ,x_password_lifespan_accesses => 10000
                           ,x_password_lifespan_days     => 10000);
   dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;                          
  END IF;
  SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = v_user_name;
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FND_FUNC_ADMIN'
                      ,security_group => 'STANDARD'
                      ,description    => 'last.first@gmail.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
Page 13done-Unix-sysadmin-scripts.txt
                      ,resp_app       => 'SYSADMIN'
                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                      ,description    => 'last.first@gmail.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'FNDWF_ADMIN_WEB'
                      ,security_group => 'STANDARD'
                      ,description    => 'last.first@gmail.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => 'FND'
                      ,resp_key       => 'APPLICATION_DEVELOPER'
                      ,security_group => 'STANDARD'
                      ,description    => 'last.first@gmail.com'
                      ,start_date     => SYSDATE - 1
                      ,end_date       => SYSDATE + 10000);
    fnd_user_pkg.addresp(username       => v_user_name,
                       resp_app       => 'ICX',
                       resp_key       => 'PREFERENCES',
                       security_group => 'STANDARD',
                       description    => 'last.first@gmail.com',
                       start_date     => sysdate - 1,
                       end_date       => null);
 result := fnd_profile.save(x_name        => 'APPS_SSO_LOCAL_LOGIN'
                            ,x_value       => 'BOTH'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);
 result := fnd_profile.save(x_name        => 'FND_CUSTOM_OA_DEFINTION'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);
 result := fnd_profile.save(x_name        => 'FND_DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);
 result := fnd_profile.save(x_name        => 'DIAGNOSTICS'
                            ,x_value       => 'Y'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);
 result := fnd_profile.save(x_name        => 'FND_HIDE_DIAGNOSTICS'
                            ,x_value       => 'N'
                            ,x_level_name  => 'USER'
                            ,x_level_value => v_user_id);
                           
  COMMIT;
Page 14done-Unix-sysadmin-scripts.txt
END;
/
/**************************End Create user & update responsibilities***********************************/

Defining user profiles

We all know the we can refer system profiles by using FND_PROFILE.VALUE(profile_name). There are already existing system profiles. We can also define and set up profiles not provided by Oracle, for example: FTP_TO_TARGET_SERVER_NAME. In this case, we can refer it in PL/SQL by FND_PROFILE.VALUE('FTP_TO_TARGET_SERVER_NAME'). The below are the steps to define user profiles.

1) Log in as Application Developer responsibility, click on ‘Profile' from menu. when the form opened, input all necessary information.

Name: FTP_TO_TARGET_SERVER_NAME

Application: XX

User Profile Name: Target Server Name for FTP

Hierarchy Type: Security

Hierarchy Type Access Level: Check site and Application.

2) Switch to System Administrator responsibility

Menu: Profile -> System

Search for Profile: Target%FTP% on site level, the defined profile will be showed in the opened ‘System Profile Values' Window. (‘Profile Option Name' will be ‘User Profile Name ‘defined in step 1).

3) Set up the value for this profile in this window, then you can refer it by using FND_PROFILE.VALUE('FTP_TO_TARGET_SERVER_NAME').

Note: FND_PROFILE.VALUE(Target Server Name for FTP'). To avoid confusion, you can give the same name for Name and User Profile Name is step one.

This voids us to hard code the server path in the code. We can set up the different values in test and production environment without code change.