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.IEX_PROFILE SQL Statements

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

Line: 45


        SELECT  SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
                SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
                SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
        INTO    p_unpaid_inv,
                p_ontime_inv,
                p_late_inv
        from    ar_payment_schedules aps,
                hz_cust_accounts     hzca
        where  aps.customer_id = hzca.cust_account_id
        and    aps.class IN ('INV', 'DM', 'CB')
        and    aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
        and    hzca.party_id IN
                            (SELECT p_filter_id FROM dual
                              UNION
                             SELECT ar.related_party_id
                               FROM ar_paying_relationships_v ar
                              WHERE ar.party_id = p_filter_id
                                AND TRUNC(sysdate) BETWEEN
                                    TRUNC(NVL(ar.effective_start_date,sysdate)) AND
                                    TRUNC(NVL(ar.effective_end_date,sysdate))  );

Line: 66


        SELECT  SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
                SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
                SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
        INTO    p_unpaid_inv,
                p_ontime_inv,
                p_late_inv
        from    ar_payment_schedules aps,
                hz_cust_accounts     hzca
        where  aps.customer_id = hzca.cust_account_id
        and    aps.class IN ('INV', 'DM', 'CB')
        and    aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
        and    hzca.party_id = p_filter_id ;

Line: 91


      SELECT  SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
      INTO    p_unpaid_inv,
              p_ontime_inv,
              p_late_inv
      from    ar_payment_schedules aps,
              hz_cust_accounts     hzca
      where   aps.customer_id = hzca.cust_account_id
      and     aps.class IN ('INV', 'DM', 'CB')
      and     aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
      and     hzca.cust_account_id = p_filter_id  ;

Line: 115


      SELECT  SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
      INTO    p_unpaid_inv,
              p_ontime_inv,
              p_late_inv
      from    ar_payment_schedules aps,
              iex_delinquencies del
      where   aps.class IN ('INV', 'DM', 'CB')
      and     aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
      and     del.payment_schedule_id = aps.payment_schedule_id
      and     del.delinquency_id = p_filter_id;

Line: 138


      SELECT  SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
              SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
      INTO    p_unpaid_inv,
              p_ontime_inv,
              p_late_inv
      from    ar_payment_schedules aps
      where   aps.class IN ('INV', 'DM', 'CB')
      and     aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
      and     aps.customer_site_use_id = p_filter_id;

Line: 248


  SELECT JIOV.short_description outcome,
   jii.start_date_time s_date,
   JRREV.resource_Name res_name,
   JIRV.short_description result
  FROM   JTF_IH_INTERACTIONS JII,
         JTF_RS_RESOURCE_EXTNS_TL JRREV,
         JTF_IH_OUTCOMES_TL JIOV,
         JTF_IH_RESULTS_TL JIRV
  WHERE   JII.resource_id    = JRREV.resource_id
  AND JRREV.LANGUAGE (+)= USERENV('LANG')
  AND JIOV.Outcome_id    = JII.outcome_id
  AND JIOV.LANGUAGE (+)= USERENV('LANG')
  AND     JIRV.Result_id(+)  = JII.result_id
  AND     JIRV.LANGUAGE (+)= USERENV('LANG')
  AND     JII.Party_Id     = p_party_id
  AND     JII.start_date_time =  (select Max(i.start_date_time)
                                  from   jtf_ih_interactions i
                                  where  i.party_id = jii.party_id) ;

Line: 273


 SELECT
         /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
         MAX(i.start_date_time)
         FROM    jtf_ih_interactions i,
                 jtf_ih_activities a
         WHERE  a.cust_account_id = cust_acct_id
 AND a.interaction_id  = i.interaction_id
 and i.start_date_time between (trunc(sysdate)-30) and sysdate;

Line: 284


 SELECT
         /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
         MAX(i.start_date_time)
         FROM    jtf_ih_interactions i,
                 jtf_ih_activities a
         WHERE  a.cust_account_id = cust_acct_id
 AND a.interaction_id  = i.interaction_id
 and i.start_date_time between (trunc(sysdate)-365) and sysdate;

Line: 295


 SELECT
         /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
         MAX(i.start_date_time)
         FROM    jtf_ih_interactions i,
                 jtf_ih_activities a
         WHERE  a.cust_account_id = cust_acct_id
 AND a.interaction_id  = i.interaction_id;

Line: 307


    SELECT
    /*+ index (jia JTF_IH_ACTIVITIES_N4) index(JII JTF_IH_INTERACTIONS_N6)*/   --Added for Bug 8200476 16-Jul-2009 barathsr
    DISTINCT JIOVT.short_description outcome,
                jii.start_date_time s_date     ,
                JRRES.source_Name res_name     ,
                JIRV.short_description result
 FROM            JTF_IH_INTERACTIONS JII    ,
                jtf_rs_resource_extns JRRES,
                JTF_IH_OUTCOMES_tl JIOVT   ,
                jtf_ih_outcomes_b jiovb    ,
                JTF_IH_RESULTS_tl JIRV     ,
                JTF_IH_ACTIVITIES JIA
 WHERE           JII.resource_id     = JRRES.resource_id
            AND jii.interaction_id  = jia.interaction_id
            AND jiovt.outcome_id    = jiovb.outcome_id
            AND JIOVB.Outcome_id(+) = JII.outcome_id
            AND JIRV.Result_id(+)   = JII.result_id
            AND jia.cust_account_id = cust_acct_id
            AND jiovt.language      = userenv('LANG')
            AND JII.start_date_time = date_time; --Added for Bug 8200476 16-Jul-2009 barathsr

Line: 328


	       -- (SELECT
             --           /*+ index(I JTF_IH_ACTIVITIES_N3)*/
            /*             MAX(i.start_date_time)
                FROM    jtf_ih_interactions i,
                        jtf_ih_activities a
                WHERE  a.cust_account_id = cust_acct_id
 AND a.interaction_id  = i.interaction_id);

Line: 373


/*SELECT  DISTINCT JIOV.short_description outcome,
    jii.start_date_time s_date,
   JRREV.resource_Name res_name,
   JIRV.short_description result
  FROM   JTF_IH_INTERACTIONS JII,
   JTF_RS_RESOURCE_EXTNS_TL JRREV,
   JTF_IH_OUTCOMES_TL JIOV,
   JTF_IH_RESULTS_B JIRB,
   JTF_IH_RESULTS_TL JIRV,
   JTF_IH_ACTIVITIES JIA
  WHERE   JII.resource_id    = JRREV.resource_id
  AND JRREV.LANGUAGE (+)= USERENV('LANG')
  AND     jii.interaction_id = jia.interaction_id
  AND JIOV.Outcome_id    = JII.outcome_id
  AND JIOV.LANGUAGE (+)= USERENV('LANG')
  AND     JIRB.Result_id(+)  = JII.result_id
  AND     JIRV.Result_id(+)    =JIRB.Result_id
  AND     JIRV.LANGUAGE (+)= USERENV('LANG')
  AND     jia.cust_account_id = cust_acct_id
  AND     JII.start_date_time =
     (select Max(i.start_date_time)
     from    jtf_ih_interactions i,
      jtf_ih_activities a
     where   a.cust_account_id = cust_acct_id
     AND     a.interaction_id = i.interaction_id);*/

Line: 429


  SELECT JIOV.short_description outcome,
          jii.start_date_time s_date,
          JRREV.resource_Name res_name,
          JIRV.short_description result
  FROM    JTF_IH_INTERACTIONS JII,
          JTF_RS_RESOURCE_EXTNS_TL JRREV,
          JTF_IH_OUTCOMES_TL JIOV,
          JTF_IH_RESULTS_TL JIRV
  WHERE   JII.resource_id    = JRREV.resource_id
  AND     JRREV.LANGUAGE (+)= USERENV('LANG')
  AND     JIOV.Outcome_id    = JII.outcome_id
  AND     JIOV.LANGUAGE (+)= USERENV('LANG')
  AND     JIRV.Result_id(+)  = JII.result_id
  AND     JIRV.LANGUAGE (+)= USERENV('LANG')
  AND     JII.Party_Id       IN
                      (SELECT p_party_id FROM dual
                        UNION
                       SELECT ar.related_party_id
                         FROM ar_paying_relationships_v ar
                        WHERE ar.party_id = p_party_id
                          AND TRUNC(sysdate) BETWEEN
                              TRUNC(NVL(ar.effective_start_date,sysdate)) AND
                              TRUNC(NVL(ar.effective_end_date,sysdate))  )
  AND     JII.start_date_time =  (select Max(i.start_date_time)
                                  from   jtf_ih_interactions i
                                  where  i.party_id = jii.party_id) ;

Line: 461


  SELECT coll.name,
         arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
  FROM hz_customer_profiles cust_prof, ar_collectors coll
  WHERE cust_prof.party_id = p_party_id
  AND coll.collector_id(+) = cust_prof.collector_id
  AND cust_prof.cust_account_id = -1;

Line: 470


  SELECT coll.name,
         arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
  FROM hz_customer_profiles cust_prof, ar_collectors coll
  WHERE cust_prof.cust_account_id = p_cust_account_id
  AND coll.collector_id(+) = cust_prof.collector_id
  AND cust_prof.site_use_id IS NULL;

Line: 479


  SELECT coll.name,
         arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
  FROM hz_customer_profiles cust_prof, ar_collectors coll
  WHERE cust_prof.site_use_id = p_customer_site_use_id
  AND coll.collector_id(+) = cust_prof.collector_id;

Line: 487


  SELECT cust_account_id, customer_site_use_id
  FROM iex_delinquencies
  WHERE delinquency_id = p_delinquency_id;

Line: 493


  SELECT cust_account_id
  FROM hz_cust_site_uses site_use, hz_cust_acct_sites acct_site
  WHERE site_use.site_use_id = p_customer_site_use_id
  AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id;

Line: 508


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
          --  sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
	      sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
 -- start for bug 13583293 by sunagesh
          -- DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
          -- DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
          NULL,
	  NULL, -- end for bug 13583293 by sunagesh
	  g_curr_rec.base_currency
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
       --  ar_cmgt_setup_options cm_opt
     WHERE prof.party_id = p_party_id
       AND prof.site_use_id IS NULL
       AND prof.status = 'A'
       -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
       -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       -- AND prof_amt.cust_account_id = prof.cust_account_id
       AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
       AND prof_amt.cust_account_id(+) = prof.cust_account_id
       -- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
       AND prof_amt.site_use_id IS NULL
       --Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
       AND prof.cust_account_id = -1;

Line: 538


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
           --   sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
                sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
	  -- start for bug 13583293 by sunagesh
	  -- DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
          -- DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
           NULL,
	   NULL, -- end for bug 13583293 by sunagesh
	   g_curr_rec.base_currency
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
       --    ar_cmgt_setup_options cm_opt
     WHERE prof.cust_account_id = p_cust_account_id
       AND prof.site_use_id IS NULL
       AND prof.status = 'A'
       -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
       -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       -- AND prof_amt.cust_account_id = p_cust_account_id
       AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
       AND prof_amt.cust_account_id(+) = p_cust_account_id
       -- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
       AND prof_amt.site_use_id IS NULL;

Line: 565


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
          --    sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
                sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
 -- start for bug 13583293 by sunagesh
	   --DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
           --DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'),  ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
           NULL,
	   NULL, -- end for bug 13583293 by sunagesh
	   g_curr_rec.base_currency
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
        --  ar_cmgt_setup_options cm_opt
     WHERE prof.site_use_id = p_customer_site_use_id
       AND prof.status = 'A'
       -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
       -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       -- AND prof_amt.site_use_id = p_customer_site_use_id;

Line: 589


SELECT
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
FROM hz_customer_profiles prof
WHERE prof.party_id =p_party_id
AND prof.cust_account_id = -1
AND prof.site_use_id IS NULL
AND prof.status = 'A';

Line: 600


SELECT
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
FROM hz_customer_profiles prof
WHERE prof.cust_account_id = p_cust_account_id
AND prof.site_use_id IS NULL
AND prof.status = 'A';

Line: 610


SELECT
DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
FROM hz_customer_profiles prof
WHERE prof.site_use_id = p_customer_site_use_id
AND prof.status = 'A';

Line: 652


  SELECT distinct sob.currency_code
    INTO   g_curr_rec.base_currency
    FROM   ar_system_parameters   sysp,
           gl_sets_of_books     sob
   WHERE  sob.set_of_books_id = sysp.set_of_books_id;

Line: 662


  SELECT  TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
          TRUNC(sysdate) pastYearTo
    INTO  g_curr_rec.past_year_from,
          g_curr_rec.past_year_to
    FROM  dual;

Line: 687


        SELECT  COUNT(1),
           SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
           SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
        Into     l_profile_rec.Total_Promises,
                  l_profile_rec.Broken_Promises,
                  l_profile_rec.Open_Promises
        FROM     IEX_PROMISE_DETAILS  IPD,
                  HZ_CUST_ACCOUNTS   HZCA,
		  IEX_DELINQUENCIES  DEL -- Moac Change Added Delinquency
        WHERE    IPD.cust_account_id = HZCA.cust_Account_id
	AND      IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
        AND      trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
        AND      HZCA.Party_id IN
                           (SELECT p_party_id FROM dual
                             UNION
                            SELECT ar.related_party_id
                              FROM ar_paying_relationships_v ar
                             WHERE ar.party_id = p_party_id
                               AND TRUNC(sysdate) BETWEEN
                                   TRUNC(NVL(ar.effective_start_date,sysdate)) AND
                                   TRUNC(NVL(ar.effective_end_date,sysdate))  );

Line: 709


        SELECT  COUNT(1),
           SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
           SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
        Into     l_profile_rec.Total_Promises,
                  l_profile_rec.Broken_Promises,
                  l_profile_rec.Open_Promises
        FROM     IEX_PROMISE_DETAILS  IPD,
                  HZ_CUST_ACCOUNTS   HZCA,
  		  IEX_DELINQUENCIES  DEL
        WHERE    IPD.cust_account_id = HZCA.cust_Account_id
	AND      IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
        AND      trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
        AND      HZCA.Party_id = p_party_id ;

Line: 725


      SELECT  COUNT(1),
           SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
           SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
      Into     l_profile_rec.Total_Promises,
               l_profile_rec.Broken_Promises,
               l_profile_rec.Open_Promises
      FROM     IEX_PROMISE_DETAILS  IPD,
               HZ_CUST_ACCOUNTS   HZCA,
    	       IEX_DELINQUENCIES  DEL -- Moac Change Added Delinquency
      WHERE    IPD.cust_account_id = HZCA.cust_Account_id
      AND      IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
      AND      trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
      AND      HZCA.cust_account_id = p_cust_account_id ;

Line: 740


      SELECT  COUNT(1),
           SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
           SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
      Into     l_profile_rec.Total_Promises,
               l_profile_rec.Broken_Promises,
               l_profile_rec.Open_Promises
      FROM     IEX_PROMISE_DETAILS  IPD
      WHERE    trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
        AND IPD.delinquency_id = p_delinquency_id;

Line: 751


      SELECT  COUNT(1),
           SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
           SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
      Into     l_profile_rec.Total_Promises,
               l_profile_rec.Broken_Promises,
               l_profile_rec.Open_Promises
      FROM     IEX_PROMISE_DETAILS        IPD,
               IEX_DELINQUENCIES DEL
      WHERE    trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
      AND IPD.delinquency_id = DEL.delinquency_id
      AND DEL.customer_site_use_id = p_customer_site_use_id;

Line: 1036


/*  SELECT sob.currency_code
    INTO   g_curr_rec.base_currency
    FROM   ar_system_parameters   sysp,
           gl_sets_of_books     sob
   WHERE  sob.set_of_books_id = sysp.set_of_books_id;

Line: 1043


  SELECT  TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
          TRUNC(sysdate) pastYearTo
    INTO  g_curr_rec.past_year_from,
          g_curr_rec.past_year_to
    FROM  dual; */