APPS.XTR_DNM_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 8

SELECT
     A.COMPANY_CODE
   , A.DEAL_NO
   , A.REVAL_CCY
   , SOB.CURRENCY_CODE SOB_CCY
   , A.PERIOD_TO
   , A.TRANSACTION_NO
   , A.BATCH_ID
   , decode(A.REALIZED_FLAG,'Y',A.REALISED_PL,A.UNREALISED_PL)  REAL_UNREAL_AMT -- replaced A.CUMM_GAIN_LOSS_AMOUNT
   , A.CURR_GAIN_LOSS_AMOUNT
   , A.REALIZED_FLAG
   , decode(A.DEAL_TYPE,'BOND',decode(A.AMOUNT_TYPE,'UNREAL','UNREAL' ,'REALAMC','REALAMC','REAL','REAL'),decode(A.REALIZED_FLAG,'Y','REAL','N','UNREAL'))   AMOUNT_TYPE
   , decode(A.DEAL_TYPE,'BOND',decode(A.AMOUNT_TYPE,'UNREAL','CCYUNRL','REALAMC','CCYAMRL','REAL','CCYREAL'),decode(A.REALIZED_FLAG,'Y','CCYREAL','N','CCYUNRL')) CURR_AMOUNT_TYPE
   , decode(sign(decode(A.REALIZED_FLAG,'Y',A.REALISED_PL,A.UNREALISED_PL)),-1,'LOSS','PROFIT') ACTION
   , decode(sign(A.CURR_GAIN_LOSS_AMOUNT),-1,'LOSS','PROFIT') CURR_ACTION
   , B.PERIOD_END
   , 'REVAL' DATE_TYPE
FROM
   XTR_REVALUATION_DETAILS A,
   XTR_BATCHES B,
   XTR_BATCH_EVENTS C,
   XTR_PARTIES_V P,
   GL_SETS_OF_BOOKS SOB
WHERE B.BATCH_ID 	     = p_batch_id
AND   B.BATCH_ID	     = A.BATCH_ID
AND   B.BATCH_ID	     = C.BATCH_ID
AND   C.EVENT_CODE	     = 'REVAL'
AND   P.PARTY_CODE	     = A.COMPANY_CODE
AND   P.CHART_OF_ACCOUNTS_ID = SOB.CHART_OF_ACCOUNTS_ID
AND   P.SET_OF_BOOKS_ID      = SOB.SET_OF_BOOKS_ID
AND   nvl(C.AUTHORIZED,'N')  <> 'Y'
AND   nvl(B.UPGRADE_BATCH,'N') <> 'Y'
AND  ( nvl(nvl(A.REALISED_PL,A.UNREALISED_PL),0) <> 0 OR nvl(A.CURR_GAIN_LOSS_AMOUNT,0) <> 0 )
--AND  ( nvl(A.CUMM_GAIN_LOSS_AMOUNT,0) <> 0 OR nvl(A.CURR_GAIN_LOSS_AMOUNT,0) <> 0 )
order by A.DEAL_NO;

Line: 45

SELECT COUNT(*)
FROM   XTR_REVALUATION_DETAILS
WHERE  batch_id = p_batch_id
AND    complete_flag = 'N';

Line: 73

      select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;

Line: 75

      XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
	row_id,
   	l_dnm_id ,
	dnm_rec.BATCH_ID,
	dnm_rec.COMPANY_CODE,
	dnm_rec.DEAL_NO,
	dnm_rec.TRANSACTION_NO,
	dnm_rec.date_type,
	abs(dnm_rec.real_unreal_amt),       -- replaced abs(dnm_rec.CUMM_GAIN_LOSS_AMOUNT),
	dnm_rec.amount_type,
	dnm_rec.action,
	dnm_rec.REVAL_CCY,
        dnm_rec.PERIOD_TO,
        'R',    -- Revaluation process flag
	sysdate,
	fnd_global.user_id,
	sysdate,
	fnd_global.user_id,
	fnd_global.login_id
	);

Line: 99

       select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;

Line: 101

      XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
	row_id,
   	l_dnm_id,
	dnm_rec.BATCH_ID,
	dnm_rec.COMPANY_CODE,
	dnm_rec.DEAL_NO,
	dnm_rec.TRANSACTION_NO,
	dnm_rec.date_type,
	abs(dnm_rec.CURR_GAIN_LOSS_AMOUNT),
	dnm_rec.curr_amount_type,
	dnm_rec.curr_action,
	dnm_rec.SOB_CCY,  -- replaced dnm_rec.REVAL_CCY with dnm_rec.SOB_CCY,
        dnm_rec.PERIOD_TO,
        'R',    -- Revaluation process flag
	sysdate,
	fnd_global.user_id,
	sysdate,
	fnd_global.user_id,
	fnd_global.login_id
	);

Line: 126

      Update XTR_BATCH_EVENTS
      set    AUTHORIZED 	= 'Y',
	     AUTHORIZED_BY = FND_GLOBAL.USER_ID,
	     AUTHORIZED_ON = SYSDATE,
             LAST_UPDATED_BY   = FND_GLOBAL.USER_ID,
	     LAST_UPDATE_DATE  = SYSDATE,
	     LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
      where  BATCH_ID = p_batch_id
      and    EVENT_CODE  = 'REVAL'
      and    nvl(AUTHORIZED,'N')  <> 'Y';

Line: 158

	   xtr_debug_pkg.debug('AUTHORIZE: ' || '>>EXCEPTION: Error in XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW');

Line: 169

   Select rowid
   From   XTR_GAIN_LOSS_DNM A
   where  A.BATCH_ID = p_batch_id
   and    EXISTS (select 'Dummy' from XTR_BATCH_EVENTS  B
	            where   B.BATCH_ID = A.BATCH_ID
		    and     B.EVENT_CODE = 'REVAL'
		    and     B.AUTHORIZED = 'Y')
   and    NOT EXISTS (select 'X' from XTR_BATCH_EVENTS  C
	   		    where   C.BATCH_ID = A.BATCH_ID
		  	    and     C.EVENT_CODE = 'JRNLGN')
   for update nowait;

Line: 182

   Select rowid
   From   XTR_BATCH_EVENTS
   Where  BATCH_ID = p_batch_id
     and  EVENT_CODE  = 'REVAL'
     and  AUTHORIZED  = 'Y'
     for update of batch_id nowait;

Line: 207

      Delete from XTR_GAIN_LOSS_DNM
      Where  rowid = l_rowid;

Line: 218

      Update XTR_BATCH_EVENTS
      set    AUTHORIZED = 'N',
             AUTHORIZED_BY = NULL,
	     AUTHORIZED_ON = NULL,
             LAST_UPDATED_BY   = FND_GLOBAL.USER_ID,
	     LAST_UPDATE_DATE  = SYSDATE,
	     LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
      where  rowid = l_rowid;