APPS.XTR_SETTLEMENT_SUMMARY_P SQL Statements

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

Line: 37

            Select 'Y', settlement_summary_id
            Into v_exists, v_settlement_id
            From Xtr_Settlement_Summary
            Where settlement_number = p_settlement_number;

Line: 46

           Select Xtr_Settlement_Summary_S.Nextval
           Into v_settlement_ID
           From Dual;

Line: 50

           Insert into Xtr_Settlement_Summary(Settlement_Summary_Id, Settlement_Number, Company, Currency,
           Settlement_Amount, Settlement_Date, Company_Acct_No, Cparty_Acct_No, Net_ID, Status,
           Created_By, Creation_Date, Last_Updated_By, Last_Update_Date, Last_Update_Login, cparty_code,
           external_source)
           Values(v_settlement_ID, p_settlement_number, p_company, p_currency, p_settlement_amount,
           p_settlement_date, p_company_acct_no, p_cparty_acct_no, p_net_ID, p_status,
           p_created_by, p_creation_date, p_created_by,
p_creation_date,null,p_cparty_code,p_external_source);  -- bug 3832387

Line: 61

           Update Xtr_Settlement_Summary
           set settlement_date = p_settlement_date,
            cparty_code = p_cparty_code                  -- bug 3832387
           Where settlement_number = p_settlement_number;

Line: 78

        Select settlement_number, deal_number, transaction_number
        From Xtr_Deal_Date_Amounts
        Where netoff_number = p_netoff_number;

Line: 85

                 Update Xtr_Settlement_Summary  -- bug 3076732
                 Set status = 'I',
                 net_ID = p_settlement_ID
                 Where (settlement_number = C1_Rec.SETTLEMENT_number) or
		       (settlement_summary_id in (select net_id
				from XTR_SETTLEMENT_SUMMARY
				where settlement_number = C1_REC.settlement_number));

Line: 101

/*  set to 'I' for original entries). Also update the existing netted         */
/*  trnasaction with new netted amount.                                       */
/******************************************************************************/
Procedure UPD_Settlement_Summary(p_flag IN Char, p_netoff_number IN Number, p_settlement_ID IN Number, p_amount IN Number)
     Is
        Cursor C1 is
        Select settlement_number, deal_number, transaction_number
        From Xtr_Deal_Date_Amounts
        Where netoff_number = p_netoff_number;

Line: 113

	Delete from XTR_SETTLEMENT_SUMMARY
	where settlement_summary_id in (select net_id
				from XTR_SETTLEMENT_SUMMARY
				where settlement_number = C1_rec.SETTLEMENT_NUMBER)
        and settlement_summary_id <> p_settlement_id;

Line: 119

         Update Xtr_Settlement_Summary
         Set status = 'I',
         net_ID = p_settlement_ID
         Where settlement_number = C1_Rec.SETTLEMENT_number;

Line: 126

    Update Xtr_Settlement_Summary
    Set settlement_amount =  nvl(p_amount, 0)
    Where Settlement_Summary_ID = p_settlement_ID;

Line: 136

/* transaction need to be updated with the new netted amount                  */
/******************************************************************************/
Procedure UPD_Settlement_Summary(p_settlement_number IN Number, p_amount IN Number)
     Is
          v_net_ID Xtr_Settlement_Summary.net_ID%Type;

Line: 143

          Select net_ID
          Into v_net_ID
          From Xtr_Settlement_Summary
          Where settlement_number = p_settlement_number;

Line: 148

          Update Xtr_Settlement_Summary
          Set status = 'A',
          net_ID = null
          Where settlement_number = p_settlement_number;

Line: 153

          Select count(*)
          Into v_no_of_netted_recs
          From Xtr_Settlement_Summary
          Where net_ID = v_net_ID;

Line: 159

             Update Xtr_Settlement_Summary
             Set settlement_amount = nvl(settlement_amount, 0) - nvl(p_amount,0)
             Where settlement_summary_ID = v_net_ID;

Line: 164

             Delete From Xtr_Settlement_Summary
             Where settlement_Summary_ID = v_net_ID;

Line: 173

/* in Xtr_Settlement_Summary should be deleted. Also update the status to 'A' */
/* for original entries.                                                      */
/******************************************************************************/

Procedure DEL_SETTLEMENT_SUMMARY(p_settle_date IN Date,
                                 p_currency IN Varchar2,
                                 p_acct_no IN Varchar2,
                                 p_cpacct_no IN Varchar2,
                                 p_company_code IN Varchar2,
                                 p_flag IN Char,
                                 p_return OUT  NOCOPY Char)
     Is
       v_net_ID Number;

Line: 191

       Select settlement_number
       From Xtr_Deal_Date_Amounts
       where ACTUAL_SETTLEMENT_DATE = p_settle_DATE
       and CASHFLOW_AMOUNT <> 0
       and CURRENCY = p_CURRENCY
       and ACCOUNT_NO = p_ACCT_NO
       and nvl(BENEFICIARY_ACCOUNT_NO,CPARTY_ACCOUNT_NO) = nvl(p_CPACCT_NO,NULL)
       and COMPANY_CODE = p_COMPANY_CODE
       and SETTLEMENT_ACTIONED is NULL
       and RECONCILED_PASS_CODE is NULL
       and RECONCILED_REFERENCE is NULL
       and NETOFF_NUMBER is NULL   -- jhung
       and AMOUNT_TYPE <> 'FXOBUY'
       and AMOUNT_TYPE <> 'FXOSELL'
/********* code below modified by Ilavenil for 2344133 ********/
       and (nvl(EXP_SETTLE_REQD, 'Y') = 'Y' or DEAL_TYPE <> 'EXP')
/*********/
       and nvl(MULTIPLE_SETTLEMENTS, 'N') = 'N'
       and DEAL_SUBTYPE <> 'INDIC'
       and DEAL_TYPE  <> 'CA';

Line: 220

       Select net_ID
       Into v_net_ID
       From Xtr_Settlement_Summary
       Where Settlement_Number = C2_Rec.Settlement_Number;

Line: 225

       Delete from Xtr_Settlement_Summary
       Where Settlement_Summary_ID = v_net_ID;

Line: 228

/* resultant record has been deleted */
          v_del_resultant := 'Y';

Line: 234

       Update Xtr_Settlement_Summary
       Set Status = 'A',
       Net_ID = null
       Where Settlement_Number = C2_Rec.Settlement_Number;

Line: 239

/* individual records contributing the resultant figure are updated as AVAILABLE */
           v_upd_single := 'Y';

Line: 266

          Select net_ID
          Into v_net_ID
          From Xtr_Settlement_Summary
          Where settlement_number = p_settlement_number;

Line: 277

/* remove the un-authorized settlement, update resultant record for the amount */

           Delete From Xtr_Settlement_Summary
           Where settlement_number = p_settlement_number;

Line: 282

           Select count(*)
           Into v_no_of_netted_Recs
           From Xtr_Settlement_Summary
           Where net_ID = v_net_ID;

Line: 288

              Update Xtr_Settlement_summary
              Set settlement_amount = nvl(settlement_amount, 0) - nvl(p_settlement_amount, 0)
              Where settlement_summary_ID = v_net_ID;

Line: 293

              Delete From Xtr_Settlement_Summary
              Where settlement_summary_ID = v_net_ID;

Line: 298

           Delete from Xtr_Settlement_Summary
           Where Settlement_Number = p_settlement_number;

Line: 328

           Select 'Y'
           Into v_exists
           From Xtr_Settlement_Summary
           Where settlement_number = p_settlement_number;

Line: 337

         Select settlement_number
         Into v_settlement_number
         From Xtr_Deal_Date_Amounts
         Where netoff_number = p_netoff_number
         And rownum < 2;

Line: 343

         Select net_ID
         Into v_net_ID
         From Xtr_Settlement_Summary
         Where settlement_number = v_settlement_number;

Line: 349

             Update Xtr_Settlement_Summary
             Set status = 'I',
                 net_ID = v_net_ID
             Where settlement_number = p_settlement_number;

Line: 354

             Select Xtr_Settlement_Summary_S.Nextval
             Into v_settlement_ID
             From Dual;

Line: 358

             Insert into Xtr_Settlement_Summary(Settlement_Summary_Id, Settlement_Number, Company, Currency,
             Settlement_Amount, Settlement_Date, Company_Acct_No, Cparty_Acct_No, Net_ID, Status,
             Created_By, Creation_Date, Last_Updated_By, Last_Update_Date, Last_Update_Login, cparty_code)
             Values(v_settlement_ID, p_settlement_number, p_company, p_currency, p_settlement_amount,
             p_settlement_date, p_company_acct_no, p_cparty_acct_no, v_net_ID, 'I',
             p_created_by, p_creation_date, p_created_by, p_creation_date,null,p_cparty_code); -- bug 3832387

Line: 366

         Update Xtr_Settlement_Summary
         Set settlement_amount = nvl(settlement_amount, 0) + nvl(p_settlement_amount, 0)
         Where settlement_summary_ID = v_net_ID;