eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IBY_DBCCARD_PVT SQL Statements

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

Line: 233


      SELECT  CurrencyNameCode currency,
              InstrType,
              Status,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (2,3)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
      ORDER BY INSTRTYPE,STATUS ASC;

Line: 248


      SELECT  CurrencyNameCode currency,
              InstrType,
              Status,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (3,8,9)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
      ORDER BY INSTRTYPE,STATUS ASC;

Line: 263


      SELECT  CurrencyNameCode currency,
              InstrType,
              Status,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (5,10,11)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      GROUP BY INSTRTYPE,CurrencyNameCode, STATUS, TRUNC(updatedate)
      ORDER BY INSTRTYPE,STATUS ASC;

Line: 278


      SELECT CurrencyNameCode currency,
             COUNT(*) total_trxn,
             SUM(amount) total_amt,
             TRUNC(updatedate) trxndate
	FROM iby_trxn_summaries_all
	WHERE transactionid IN
            (
            SELECT transactionid
            FROM iby_trxn_summaries_all
            WHERE instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
            AND status = 0
            GROUP BY transactionid
            HAVING COUNT(*) = 1
            )
      AND TRUNC(updatedate) >= l_date
      AND trxntypeid = 2
      AND payeeid LIKE l_payeeid
      GROUP BY CurrencyNamecode, TRUNC(updatedate)
      ORDER BY CurrencyNameCode;

Line: 348


   l_updatedate DATE;

Line: 372


   l_updatedate := get_date(period);

Line: 381


   FOR t_auths IN auth_summary_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);

Line: 427


   FOR t_capts IN capt_summary_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_capts.currency, C_TO_CURRENCY, t_capts.trxndate, t_capts.total_amt, NULL);

Line: 477


   FOR t_creds IN cred_summary_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_creds.currency, C_TO_CURRENCY, t_creds.trxndate, t_creds.total_amt, NULL);

Line: 522


   FOR t_outs IN load_auth_outstand_csr( l_updatedate,l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_outs.currency, C_TO_CURRENCY, t_outs.trxndate, t_outs.total_amt, NULL);

Line: 623


      SELECT  CurrencyNameCode currency,
              Status,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (2,3)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
      GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
      ORDER BY status ASC;

Line: 638


      SELECT  CurrencyNameCode currency,
              Status,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (3,8,9)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      AND status IN (-99,1,2,4,5,8,15,16,17,19,20,21,9999)
      GROUP BY STATUS, CurrencyNameCode, TRUNC(updatedate)
      ORDER BY status ASC;

Line: 653


   l_updatedate DATE;

Line: 683


   l_updatedate := get_date(period);

Line: 697


   FOR t_auths IN get_authFail_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);

Line: 771


   FOR t_auths IN get_settFail_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);

Line: 856


      SELECT  CurrencyNameCode currency,
              instrsubtype,
              -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
	      -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
	      DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
              COUNT(*) total_trxn,
              SUM(amount) total_amt,
              TRUNC(updatedate) trxndate
      FROM iby_trxn_summaries_all
      WHERE TRUNC(updatedate) >= l_date
      AND trxntypeid IN (2,3,5,8,9,10,11)
      AND instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND payeeid LIKE l_payeeId
      AND instrsubtype IS NOT NULL
      AND status IN
          (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
      GROUP BY instrsubtype,
               -- DECODE(trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
	       -- DECODE(trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
	       DECODE(trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
               CurrencyNameCode, TRUNC(updatedate)
      ORDER BY UPPER(instrsubtype) ASC;

Line: 880


   l_updatedate DATE;

Line: 907


   l_updatedate := get_date(period);

Line: 921


   FOR t_auths IN get_CardType_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);

Line: 979


      SELECT  b.name,
              a.CurrencyNameCode currency,
              -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1) factor, -- Bug 3306449
	      -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1) factor, -- Bug 3458221
	      DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1) factor,
              COUNT(*) total_trxn,
              SUM(a.amount) total_amt,
              TRUNC(a.updatedate) trxndate
      FROM    iby_trxn_summaries_all a,
              iby_bepinfo b
      WHERE TRUNC(updatedate) >= l_date
      AND a.trxntypeid IN (2,3,5,8,9,10,11)
      AND a.instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND a.payeeid LIKE l_payeeId
      AND b.bepid = a.bepid
      -- AND b.activestatus = 'Y'
      AND a.status IN
          (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
      GROUP BY b.name,
               -- DECODE(a.trxntypeid, 3, 2, 5,-1, 10, -1, 11, -1, 1), -- Bug 3306449
	       -- DECODE(a.trxntypeid, 5, -1, 10, -1, 11, -1, 2, 0, 1), -- Bug 3458221
	       DECODE(a.trxntypeid, 5, -1, 8, 0, 9, 0, 10, -1, 11, -1, 1),
               a.CurrencyNameCode, TRUNC(a.updatedate)
      ORDER BY b.name ASC;

Line: 1005


   l_updatedate DATE;

Line: 1032


   l_updatedate := get_date(period);

Line: 1046


   FOR t_auths IN get_Processor_csr( l_updatedate, l_payeeId) LOOP

      -- Bug 3714173: reporting currency is from the profile option
      -- l_amount := Convert_Amount( t_auths.currency, C_TO_CURRENCY, t_auths.trxndate, t_auths.total_amt, NULL);

Line: 1108


      SELECT DECODE(a.overall_score - b.threshold, 0,0,a.overall_score - b.threshold ) value,
             -- DECODE(a.trxntypeid, 3, 2, 1) factor, -- Bug 3306449
	     -- DECODE(a.trxntypeid, 2, 0, 1) factor, -- Bug 3458221
	     DECODE(a.trxntypeid, 8, 0, 9, 0, 1) factor,
             COUNT(*) total_trxn
      FROM   iby_trxn_summaries_all a,
	       iby_payee b
      WHERE  TRUNC(updatedate) >= l_date
      AND    trxntypeid IN (2,3,5,8,9,10,11)
      AND    instrtype IN (C_INSTRTYPE_CREDITCARD,C_INSTRTYPE_PURCHASECARD)
      AND    a.payeeid = b.payeeid
      AND    b.payeeid LIKE l_payeeId
      -- AND    b.activestatus = 'Y'
      AND    b.threshold IS NOT NULL
      AND    a.overall_score IS NOT NULL
      AND    status IN (-99,0,1,2,4,5,8,15,16,17,19,20,21,100,109,111,9999)
      GROUP BY DECODE(a.overall_score - b.threshold,0,0,a.overall_score - b.threshold ),
	         -- DECODE(a.trxntypeid, 3, 2, 1) -- Bug 3306449
		 -- DECODE(a.trxntypeid, 2, 0, 1) -- Bug 3458221
		 DECODE(a.trxntypeid, 8, 0, 9, 0, 1)
      ORDER BY value ASC;

Line: 1131


   l_updatedate DATE;

Line: 1144


   l_updatedate := get_date(period);

Line: 1156


   FOR t_risk IN get_risk_csr( l_updatedate, l_payeeId) LOOP
      total_screened := total_screened + (t_risk.total_trxn * t_risk.factor);