RMAN Restore on another machine with different file system - same database name In Oracle Apps

Please Visit http://www.conacent.com/?page_id=218

RMAN Restoration on different Server as well as different Mount Point for Oracle APPS

1. Copy db backup file system from source server to target server as well as rman backup files.

2. Clone the database technology Stack.
    $su - oracln
    $cd $ORACLE_HOME/appsutil/clone/bin
    $perl adcfgclone.pl dbTechStack

3. Connect to RMAN utility.
    $RMAN target /


     Recovery Manager: Release 11.1.0.7.0 - Production on Fri Oct 5 14:03:49 2012
     Copyright (c) 1982, 2007, Oracle.  All rights reserved.
     connected to target database (not started)


4. Startup Database to nomount state.
    RMAN>startup nomount;


    Oracle instance started
    Total System Global Area    1068937216 bytes
    Fixed Size                                 2166536 bytes
    Variable Size                         427819256 bytes
    Database Buffers                   624951296 bytes
    Redo Buffers                           14000128 bytes


5. Restore controlfile from the rman backup file system. Before changing the path of the RMAN backup change the owner of the directory.

    RMAN> restore controlfile from '/data2/rman_archive/autobackup_control_filec-147839628-20120930-00';  [Latest control file]


                  Starting restore at 05-OCT-12
                  using channel ORA_DISK_1
                  channel ORA_DISK_1: restoring control file
                  channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
                  output file name=/d01/CLONE/db/apps_st/data/cntrl01.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl02.dbf
                  output file name=/d01/CLONE/db/apps_st/data/cntrl03.dbf
                  Finished restore at 05-OCT-12


6. Database need to be startup to mount state.
    RMAN> alter database mount;

                using target database control file instead of recovery catalog
               database mounted

7. if mount point is different then start with catalog command
    RMAN> catalog start with '/d01/CLONE/db';

                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.2
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.3
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/conf/ons.config.ouibak.5
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/ons.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/opmn/lib/onc.jar
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old.1
                   File Name: /d01/CLONE/db/tech_st/11.1.0/root.sh.old

                   Do you really want to catalog the above files (enter YES or NO)? YES
                   cataloging files...


8. if redo logfile is in different mount point
    $ sqlplus '/as sysdba'
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03a.dbf' to '/d01/CLONE/db/apps_st/data/log03a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log03b.dbf' to '/d01/CLONE/db/apps_st/data/log03b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04a.dbf' to '/d01/CLONE/db/apps_st/data/log04a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log04b.dbf' to '/d01/CLONE/db/apps_st/data/log04b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05a.dbf' to '/d01/CLONE/db/apps_st/data/log05a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log05b.dbf' to '/d01/CLONE/db/apps_st/data/log05b.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06a.dbf' to '/d01/CLONE/db/apps_st/data/log06a.dbf';
    SQL> alter database rename file '/oracle/OBA/obaora/db/apps_st/data/log06b.dbf' to '/d01/CLONE/db/apps_st/data/log06b.dbf';


9. if tempfile is in different mount point
    RMAN> run {
    2> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp01.dbf' to '/d01/CLONE /db/apps_st/data/temp01.dbf';
     3> set newname for tempfile '/oracle/OBA/obaora/db/apps_st/data/temp02.dbf' to '/d01/CLONE/db/apps_st/data/temp02.dbf';
     4> switch tempfile all;
     5> }

      executing command: SET NEWNAME
      using target database control file instead of recovery catalog

       executing command: SET NEWNAME

        renamed tempfile 1 to /d01/CLONE/db/apps_st/data/temp01.dbf in control file
        renamed tempfile 2 to /d01/CLONE/db/apps_st/data/temp02.dbf in control file

  

10. if datafile is in different mount point

    RMAN> run {
          set newname for datafile 1 to '/d01/CLONE/db/apps_st/data/system01.dbf';
          set newname for datafile 2 to '/d01/CLONE/db/apps_st/data/system02.dbf';
          set newname for datafile 3 to '/d01/CLONE/db/apps_st/data/system03.dbf';
          set newname for datafile 4 to '/d01/CLONE/db/apps_st/data/system04.dbf';
          set newname for datafile 5 to '/d01/CLONE/db/apps_st/data/system05.dbf';
          set newname for datafile 6 to '/d01/CLONE/db/apps_st/data/ctxd01.dbf';
          set newname for datafile 7 to '/d01/CLONE/db/apps_st/data/owad01.dbf';
          set newname for datafile 8 to '/d01/CLONE/db/apps_st/data/a_queue02.dbf';
          set newname for datafile 9 to '/d01/CLONE/db/apps_st/data/odm.dbf';
          set newname for datafile 10 to '/d01/CLONE/db/apps_st/data/olap.dbf';
          set newname for datafile 11 to '/d01/CLONE/db/apps_st/data/sysaux01.dbf';
          set newname for datafile 12 to '/d01/CLONE/db/apps_st/data/apps_ts_tools01.dbf';
          set newname for datafile 13 to '/d01/CLONE/db/apps_st/data/system12.dbf';
          set newname for datafile 14 to '/d01/CLONE/db/apps_st/data/a_txn_data04.dbf';
          set newname for datafile 15 to '/d01/CLONE/db/apps_st/data/a_txn_ind06.dbf';
          set newname for datafile 16 to '/d01/CLONE/db/apps_st/data/a_ref03.dbf';
          set newname for datafile 17 to '/d01/CLONE/db/apps_st/data/a_int02.dbf';
          set newname for datafile 18 to '/d01/CLONE/db/apps_st/data/sysaux02.dbf';
          set newname for datafile 19 to '/d01/CLONE/db/apps_st/data/sysaux03.dbf';
          set newname for datafile 20 to '/d01/CLONE/db/apps_st/data/a_txn_data05.dbf';
          set newname for datafile 21 to '/d01/CLONE/db/apps_st/data/a_txn_data06.dbf';
          set newname for datafile 22 to '/d01/CLONE/db/apps_st/data/a_txn_ind07.dbf';
          set newname for datafile 23 to '/d01/CLONE/db/apps_st/data/XXILL01.dbf';
          set newname for datafile 288 to '/d01/CLONE/db/apps_st/data/system10.dbf';
          set newname for datafile 295 to '/d01/CLONE/db/apps_st/data/system06.dbf';
          set newname for datafile 314 to '/d01/CLONE/db/apps_st/data/portal01.dbf';
          set newname for datafile 351 to '/d01/CLONE/db/apps_st/data/system07.dbf';
          set newname for datafile 352 to '/d01/CLONE/db/apps_st/data/system09.dbf';
          set newname for datafile 353 to '/d01/CLONE/db/apps_st/data/system08.dbf';
          set newname for datafile 354 to '/d01/CLONE/db/apps_st/data/system11.dbf';
          set newname for datafile 379 to '/d01/CLONE/db/apps_st/data/undo01.dbf';
          set newname for datafile 392 to '/d01/CLONE/db/apps_st/data/a_txn_data01.dbf';
          set newname for datafile 393 to '/d01/CLONE/db/apps_st/data/a_txn_ind01.dbf';
          set newname for datafile 394 to '/d01/CLONE/db/apps_st/data/a_ref01.dbf';
          set newname for datafile 395 to '/d01/CLONE/db/apps_st/data/a_int01.dbf';
          set newname for datafile 396 to '/d01/CLONE/db/apps_st/data/a_summ01.dbf';
          set newname for datafile 397 to '/d01/CLONE/db/apps_st/data/a_nolog01.dbf';
          set newname for datafile 398 to '/d01/CLONE/db/apps_st/data/a_archive01.dbf';
          set newname for datafile 399 to '/d01/CLONE/db/apps_st/data/a_queue01.dbf';
          set newname for datafile 400 to '/d01/CLONE/db/apps_st/data/a_media01.dbf';
          set newname for datafile 401 to '/d01/CLONE/db/apps_st/data/a_txn_data02.dbf';
          set newname for datafile 402 to '/d01/CLONE/db/apps_st/data/a_txn_data03.dbf';
          set newname for datafile 403 to '/d01/CLONE/db/apps_st/data/a_txn_ind02.dbf';
          set newname for datafile 404 to '/d01/CLONE/db/apps_st/data/a_txn_ind03.dbf';
          set newname for datafile 405 to '/d01/CLONE/db/apps_st/data/a_txn_ind04.dbf';
          set newname for datafile 406 to '/d01/CLONE/db/apps_st/data/a_txn_ind05.dbf';
          set newname for datafile 407 to '/d01/CLONE/db/apps_st/data/a_ref02.dbf';
          switch datafile all;
}

RMAN> catalog start with '/s01/rman_archive';


RMAN>run {
          restore database  /  restore database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
           recover database  /  recover database until time "to_date('01-OCT-2012 23:45:00','dd-mon-rrrr hh24:mi:ss')";
          }

RMAN> alter database open resetlogs;



Please give your feedback if any thing is not correct. Your feedback is valuable.

"RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"

While Performing Receipt/Delviery Error:-
 "RVTTH-192: Subroutine ORA-20001: APP—20110:Encountered the error in trigger JAI_RCVRT_BRIUD_TI ORA-06508: PL/SQL: could not find program unit being called"

Solution: If India Localization is used then it is mandatory set the profile option RCV:Processing Mode to Immediate at site level.

Query For Account Hiererchy:

Step:1

create table XX_test_acc1
as select xx.parent_flex_value,xx.flex_value,xx.DESCRIPTION
from
(select DISTINCT ffvc.parent_flex_value,ffvc.flex_value,FFVC.DESCRIPTION
from
fnd_flex_value_children_v ffvc
where ffvc.summary_flag='N'
AND ffvc.flex_value_set_id=1014870
START WITH ffvc.parent_flex_value IS NOT NULL
CONNECT BY PRIOR ffvc.flex_value=ffvc.parent_flex_value
ORDER BY FFVC.PARENT_FLEX_VALUE) xx;

Step:2

select a.PARENT_FLEX_VALUE,a.DESCRIPTION,xx.parent_flex_value_low,xx.flex_value,xx.flex_value_meaning,xx.description
from
(SELECT   parent_flex_value_low,flex_value, flex_value_meaning, description
    FROM fnd_flex_values_vl
   WHERE (   ('' IS NULL)
          OR (structured_hierarchy_level IN (
                 SELECT hierarchy_id
                   FROM fnd_flex_hierarchies_vl h
                  WHERE h.flex_value_set_id = 1014871
                    AND h.hierarchy_name LIKE '')
             )
         )
     AND (flex_value_set_id = 1014871)
     AND enabled_flag='Y'
          AND (parent_flex_value_low in(select a.flex_value from cil_test_acc a))
ORDER BY parent_flex_value_low) xx ,XX_test_acc1 a
where xx.parent_flex_value_low=to_number(a.FLEX_VALUE);

Query to find responsibilty attached to user

SELECT fu.user_name,
       frv.responsibility_name,
       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND fu.start_date                   <= sysdate
AND frv.start_date                  <= sysdate;

AR 7 Bucket Ageing Query

Select MAIN_TAB.*
from
(select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
(nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) outstanding_amount_inr,
(nvl(AR_TAB.Fcy_Amt,0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then (AR_TAB.INR_AMT - AR_TAB.ar_acctd_amt_due)
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED* nvl(ps.EXCHANGE_RATE,1) ,0)),2)
from ar_receivable_applications_all a
where a.APPLIED_PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(a.AMOUNT_APPLIED,0)),2) from ar_receivable_applications_all a where a.APPLIED_PAYMENT_SCHEDULE_ID=ps.PAYMENT_SCHEDULE_ID
and a.DISPLAY='Y' and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.APPLIED_PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('INV','DM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(AR_TAB.INR_AMT,0) - nvl(AR_TAB.ar_acctd_amt_due,0)) <> 0
union all /*For credit Memo*/
select AR_TAB.account_number ,
AR_TAB.party_name,
AR_TAB.customer_trx_id,
AR_TAB.Trans_Type,
AR_TAB.trans_type_name,
AR_TAB.cust_trx_type_id,
AR_TAB.invoice_currency_code,
AR_TAB.location_code,
AR_TAB.trx_num,
AR_TAB.Trans_Date,
AR_TAB.gl_date,
AR_TAB.Due_Date,
AR_TAB.Fcy_Amt,
AR_TAB.Inr_Amt,
AR_TAB.exchange_rate,
AR_TAB.amount_due_remaining_FCY,
AR_TAB.Amount_Remaining_INR,
nvl(AR_TAB.ar_acctd_amt_due,0) ar_acctd_amt_due,
-(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) outstanding_amount_inr,
-(nvl(abs(AR_TAB.Fcy_Amt),0)-nvl(AR_TAB.ar_fcy_amt_due,0)) Outstanding_amount_fcy,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) <=0 then -(abs(AR_TAB.INR_AMT) - abs(AR_TAB.ar_acctd_amt_due))
else 0 end ) Current_bal
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 1 and 30 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 31 and 60 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 61 and 90 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_90_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 91 and 120 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_120_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 121 and 150 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_150_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) between 151 and 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) One_180_Days_Past_Due,
(case when (:p_to_date - trunc(AR_TAB.DUE_DATE)) > 180 then -(nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0))
else 0 end ) more_than_180_Days_Past_Due
from
(SELECT
hca.account_number account_number,
hp.PARTY_NAME party_name,
cta.CUSTOMER_TRX_ID customer_trx_id
--,acl.name collector_name
,decode(ps.class,'INV', 'Invoice', 'DM', 'Debit Memo','CM','Credit Memo') Trans_Type
,rctt.name trans_type_name,
rctt.CUST_TRX_TYPE_ID cust_trx_type_id
,cta.INVOICE_CURRENCY_CODE invoice_currency_code,
hou.LOCATION_CODE location_code,
ps.trx_number trx_num
,ps.trx_date Trans_Date,
ps.GL_DATE gl_date,
ps.due_date Due_Date,
ps.AMOUNT_DUE_ORIGINAL Fcy_Amt,
round((ps.AMOUNT_DUE_ORIGINAL * nvl(ps.EXCHANGE_RATE,1)),2) Inr_Amt ,
ps.EXCHANGE_RATE exchange_rate,
ps.AMOUNT_DUE_REMAINING amount_due_remaining_FCY
,ps.acctd_amount_due_remaining Amount_Remaining_INR,
(select round(sum(nvl(a.AMOUNT_APPLIED*nvl(ps.EXCHANGE_RATE,1),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_acctd_amt_due,
(select round(sum(nvl(abs(a.AMOUNT_APPLIED),0)),2)
from ar_receivable_applications_all a
where a.PAYMENT_SCHEDULE_ID = ps.PAYMENT_SCHEDULE_ID
-- a.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
and a.DISPLAY='Y'
and trunc(a.GL_DATE) between :p_from_date and :p_to_date
group by a.PAYMENT_SCHEDULE_ID) ar_fcy_amt_due
/*,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) <=0 then ps.acctd_amount_due_remaining
else 0 end ) Current_bal
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 1 and 30 then ps.acctd_amount_due_remaining
else 0 end ) One_30_Days_Past_Due ---1-30
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 31 and 60 then ps.acctd_amount_due_remaining
else 0 end ) ThirtyOne_60_Days_Past_Due ---31-60
,(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 61 and 90 then ps.acctd_amount_due_remaining
else 0 end ) One_90_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 91 and 120 then ps.acctd_amount_due_remaining
else 0 end ) One_120_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 121 and 150 then ps.acctd_amount_due_remaining
else 0 end ) One_150_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) between 151 and 180 then ps.acctd_amount_due_remaining
else 0 end ) One_180_Days_Past_Due,
(case when (trunc(sysdate) - trunc(ps.GL_DATE)) > 180 then ps.acctd_amount_due_remaining
else 0 end ) more_than_180_Days_Past_Due*/
FROM apps.ar_payment_schedules_all ps
,apps.hz_cust_accounts hca
,apps.hz_parties hp
--,apps.ar_collectors acl
,apps.ra_customer_trx_all cta
,apps.ra_cust_trx_types_all rctt
,hr_organization_units_v hou
--ar_receivable_applications_all ara
WHERE hca.cust_account_id = ps.CUSTOMER_ID
AND   ps.CUSTOMER_TRX_ID=cta.CUSTOMER_TRX_ID
--AND   ara.APPLIED_CUSTOMER_TRX_ID=ps.CUSTOMER_TRX_ID
--AND ara.DISPLAY='Y'
AND cta.cust_trx_type_id = rctt.cust_trx_type_id
--AND (ps.status = 'OP' or(ps.STATUS='CL' and ps.GL_DATE_CLOSED < :p_to_date))
AND ps.class <> 'PMT'
AND ps.CLASS = nvl(:p_class,ps.CLASS)
AND ps.CLASS in('CM')
--AND ps.customer_id > 0
--AND ps.AMOUNT_DUE_REMAINING <> 0
--AND trunc(ps.GL_DATE) < trunc(sysdate)
AND cta.INVOICE_CURRENCY_CODE <> 'INR'
AND hou.ORGANIZATION_ID=cta.ORG_ID
AND cta.ORG_ID = nvl(:p_org_id,cta.ORG_ID)
AND hp.PARTY_ID=hca.PARTY_ID
--AND hp.PARTY_NAME='Tea Promotors Export Pvt. Ltd.'
AND ps.INVOICE_CURRENCY_CODE<>'INR'
AND rctt.ORG_ID=cta.ORG_ID
AND ps.AMOUNT_ADJUSTED is null -- added on 10jul2012
--AND cta.TRX_NUMBER='511120003'
--AND trunc(ps.DUE_DATE) between  nvl(:p_from_date,trunc(ps.DUE_DATE))  and nvl(:p_to_date,trunc(ps.DUE_DATE))
--AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE)
AND trunc(ps.GL_DATE) between  nvl(:p_from_date,trunc(ps.GL_DATE))  and nvl(:p_to_date,trunc(ps.GL_DATE))) AR_TAB
--AND AR_TAB.Amount_Remaining_INR <>0
where (nvl(abs(AR_TAB.INR_AMT),0) - nvl(abs(AR_TAB.ar_acctd_amt_due),0)) <> 0) MAIN_TAB
order by MAIN_TAB.PARTY_NAME asc;

Call a procedure through personalization

SQL TO FIND MANUAL AP INVOICES WITH OUT PO AND RECEIPT MATCH

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID (+)
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID (+)
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE

SQL TO FIND PO MATCH INVOICES

SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.BASE_AMOUNT     AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS          RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NOT NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION ALL
SELECT POV.VENDOR_NAME
,      AIA.INVOICE_TYPE_LOOKUP_CODE AS INVOICE_TYPE
,      AIA.INVOICE_NUM
,      AIA.INVOICE_DATE
,      AID.ACCOUNTING_DATE AS GL_DATE
,      AIA.INVOICE_CURRENCY_CODE
,      PHA.SEGMENT1  AS PO_NUMBER
,      RSH.RECEIPT_NUM
,      NVL(AIA.EXCHANGE_RATE,1) AS EXCHANGE_RATE
,      GCC.CONCATENATED_SEGMENTS
,       AID.AMOUNT          AS ENTERED_AMOUNT
,       AID.AMOUNT          AS ACCOUNTED_AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_ALL   AID
,    AP_INVOICES_ALL                AIA
,    PO_VENDORS                     POV
,    GL_CODE_COMBINATIONS_KFV       GCC
,    PO_DISTRIBUTIONS_ALL           PDA
,    PO_HEADERS_ALL                 PHA
,    (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
      FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
,    RCV_SHIPMENT_HEADERS           RSH
WHERE AID.RCV_TRANSACTION_ID IS NULL
AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
AND   AID.BASE_AMOUNT IS NULL
AND   AID.INVOICE_ID = AIA.INVOICE_ID
AND   AIA.VENDOR_ID = POV.VENDOR_ID
AND   AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND   TRUNC(AID.ACCOUNTING_DATE) BETWEEN '01-APR-2011' AND '31-MAR-2012'
AND   AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND   PDA.PO_HEADER_ID  = PHA.PO_HEADER_ID
AND   AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND   RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
ORDER BY INVOICE_NUM, GL_DATE

SQL to find Inventory Misc. Transactions with Dist Account and Value

SELECT  CID.ORGANIZATION_CODE
,       HRO.NAME
,       MMT.TRANSACTION_ID
,       MSI.SEGMENT1
,       MSI.DESCRIPTION
,       MTY.TRANSACTION_TYPE_NAME
,       TRUNC(MMT.TRANSACTION_DATE) AS TRANSACTION_DATE
,       MMT.TRANSACTION_QUANTITY
,       MMT.TRANSACTION_UOM
,       MMT.TRANSACTION_SOURCE_NAME
,       CID.UNIT_COST
,       NVL(MMT.ACTUAL_COST,0) AS ACTUAL_COST
,       GCC.CONCATENATED_SEGMENTS   AS ACCOUNT_CODE_COMBINATION
,       CID.LINE_TYPE_NAME
,       CID.BASE_TRANSACTION_VALUE
FROM MTL_MATERIAL_TRANSACTIONS      MMT
,    MTL_SYSTEM_ITEMS_B             MSI
,    HR_ALL_ORGANIZATION_UNITS      HRO
,    MTL_TRANSACTION_TYPES          MTY
,    CST_INV_DISTRIBUTION_V         CID
,    GL_CODE_COMBINATIONS_KFV       GCC
WHERE MMT.TRANSACTION_TYPE_ID IN (32, 42, 100001, 100002)
AND   MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND   MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND   MMT.ORGANIZATION_ID = HRO.ORGANIZATION_ID
AND   MMT.TRANSACTION_TYPE_ID = MTY.TRANSACTION_TYPE_ID
AND   TRUNC(MMT.TRANSACTION_DATE) BETWEEN :FROM_DATE AND :TO_DATE
AND   MMT.TRANSACTION_ID =  CID.TRANSACTION_ID
AND   MMT.ORGANIZATION_ID = CID.ORGANIZATION_ID
AND   CID.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID
ORDER BY MMT.TRANSACTION_ID, BASE_TRANSACTION_VALUE DESC

run scp as background process

http://www.conacent.com

I had a problem to transfer big file from server A to server B and don't want to wait until transfer process is completed. How to keep scp running even terminal session is closed.

My solution is using scp with nohup.

1	$nohup scp *.tar user@server:/e01/backup/ &

But the problem with the above code is, scp require password to enter before you can use it. (other method, create ssh signed key)

to solve this problem. We can do the following step
1. run nohup

1	$nohup scp  *.tar user@server:/e01/backup/  > nohup.out 2>&1

by default, nohup is not running at background. wait until scp asking for the password.
2. enter your password.
3. press ctrl + z to temporary suspend the command.
4. enter bg command

1

$bg

now your scp is running at background. You can close your terminal