APPS.PN_NORM_RENORM_PKG SQL Statements

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

Line: 71

    SELECT NVL(SUM(actual_amount), 0) total_cash_amount
    FROM   pn_payment_items_all
    WHERE  payment_term_id = p_term_id
    AND    payment_item_type_lookup_code = 'CASH';

Line: 78

    SELECT NVL(SUM(actual_amount), 0) total_norm_amount
    FROM   pn_payment_items_all item,
           pn_payment_schedules_all schedule
    WHERE  schedule.lease_id = p_lease_id
    AND    item.payment_schedule_id = schedule.payment_schedule_id
    AND    item.payment_term_id = p_term_id
    AND    item.payment_item_type_lookup_code = 'NORMALIZED'
    AND    schedule.payment_status_lookup_code IN  ('APPROVED','ON_HOLD');

Line: 117

SELECT org_id
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;

Line: 124

SELECT 1 FROM dual
WHERE  exists
       (SELECT 1
        FROM   pn_payment_items_all ppi
        WHERE  ppi.payment_schedule_id = b_schedule_id
        AND    ppi.payment_item_type_lookup_code = 'CASH'
        AND    ppi.payment_term_id = b_term_id
       );

Line: 143

SELECT pn_schedules_items.FIRST_DAY(change_commencement_date)
INTO l_amd_comn_date
FROM
  (SELECT *
   FROM pn_lease_changes_all
   WHERE lease_id = p_lease_id
   ORDER BY lease_change_id DESC)
WHERE rownum < 2;

Line: 154

     SELECT lease_commencement_date
     INTO l_amd_comn_date
     FROM pn_lease_details_all
     WHERE lease_id = p_lease_id;

Line: 170

       SELECT NVL(SUM(ppi.actual_amount),0)   /* 6893609 */
       INTO l_app_amt
       FROM   pn_payment_items_all ppi,
              pn_payment_schedules_all pps
       WHERE  ppi.payment_term_id =  p_term_id
       AND    ppi.payment_item_type_lookup_code = 'NORMALIZED'
       AND    pps.payment_schedule_id = ppi.payment_schedule_id
       AND    pps.payment_status_lookup_code <> 'DRAFT'
       AND    schedule_date >=  pn_schedules_items.FIRST_DAY(l_amd_comn_date_tmp); /* 8491119 */

Line: 186

       SELECT NVL(SUM(ppi.actual_amount),0)
       INTO l_dft_amt
       FROM   pn_payment_items_all ppi,
              pn_payment_schedules_all pps
       WHERE  ppi.payment_term_id =  p_term_id
       AND    ppi.payment_item_type_lookup_code = 'NORMALIZED'
       AND    pps.payment_schedule_id = ppi.payment_schedule_id
       AND    pps.payment_status_lookup_code = 'DRAFT'
       AND    schedule_date < pn_schedules_items.FIRST_DAY(l_amd_comn_date_tmp);

Line: 198

Select NVL(SUM(ppi.actual_amount),0)
into l_term_amt
FROM   pn_payment_items_all ppi,
       pn_payment_schedules_all pps
WHERE  ppi.payment_term_id =  p_term_id
AND    ppi.payment_item_type_lookup_code = 'NORMALIZED'
AND    pps.payment_schedule_id = ppi.payment_schedule_id
AND    pps.payment_status_lookup_code <> 'DRAFT'
AND    to_char(schedule_date,'MON-YY')  =  to_char(l_amd_comn_date,'MON-YY'); /* 8491119 */

Line: 390

  /* start to create/update normalized items */
  FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP

      begin -- ver6
    	    SELECT SUM(actual_amount) /* Bug 6893609*/
    	    into   l_act_amt
            FROM   pn_payment_items_all ppi,
                   pn_payment_schedules_all pps
            WHERE ppi.payment_item_type_lookup_code = 'NORMALIZED'
            AND   pps.payment_schedule_id = ppi.payment_schedule_id
            AND   ppi.payment_term_id =  p_term_id
            AND   pps.payment_status_lookup_code <>  'DRAFT'
            AND   due_date = g_norm_item_tbl(i).schedule_date;

Line: 412

    /* first try to update */
    UPDATE pn_payment_items_all ppi
    SET    ppi.actual_amount = g_norm_item_tbl(i).normalized_amount,
           ppi.export_currency_amount = g_norm_item_tbl(i).normalized_amount,
           ppi.last_update_date = SYSDATE,
           ppi.last_updated_by  = NVL(fnd_profile.value('USER_ID'),0),
           ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE  ppi.payment_schedule_id = g_norm_item_tbl(i).schedule_id
    AND    ppi.payment_item_type_lookup_code = 'NORMALIZED'
    AND    ppi.payment_term_id = p_term_id;

Line: 429

      INSERT INTO pn_payment_items_all
      (
       payment_item_id,
       last_update_date,
       last_updated_by,
       creation_date,
       created_by,
       last_update_login,
       actual_amount,
       estimated_amount,
       due_date,
       payment_item_type_lookup_code,
       payment_term_id,
       payment_schedule_id,
       period_fraction,
       vendor_id,
       customer_id,
       vendor_site_id,
       customer_site_use_id,
       cust_ship_site_id,
       set_of_books_id,
       currency_code,
       export_currency_code,
       export_currency_amount,
       rate,
       org_id
       )
        VALUES
       (
       PN_PAYMENT_ITEMS_S.NEXTVAL,
       SYSDATE,
       NVL(fnd_profile.value('USER_ID'),0),
       SYSDATE,
       NVL(fnd_profile.value('USER_ID'),0),
       NVL(fnd_profile.value('LOGIN_ID'),0),
       g_norm_item_tbl(i).normalized_amount,
       NULL,
       g_norm_item_tbl(i).schedule_date,
       'NORMALIZED',
       p_term_id,
       g_norm_item_tbl(i).schedule_id,
       1,
       p_vendor_id,
       p_cust_id,
       p_vendor_site_id,
       p_cust_site_use_id,
       p_cust_ship_site_id,
       p_sob_id,
       p_curr_code,
       p_curr_code,
       g_norm_item_tbl(i).normalized_amount,
       p_rate,
       l_org_id
       );

Line: 518

  /* update the table */
  UPDATE pn_payment_terms_all
  SET    event_type_code = p_lease_context,
         norm_start_date = p_norm_str_dt,
         norm_end_date   = p_norm_end_dt
  WHERE  normalize ='Y'
  AND    payment_term_id = p_term_id;

Line: 561

    SELECT NVL(SUM(actual_amount), 0) total_cash_amount
    FROM   pn_payment_items_all
    WHERE  payment_term_id = p_term_id
    AND    payment_item_type_lookup_code = 'CASH';

Line: 567

    SELECT NVL(SUM(actual_amount), 0) total_norm_amount
    FROM   pn_payment_items_all item,
           pn_payment_schedules_all schedule
    WHERE  schedule.lease_id = p_lease_id
    AND    item.payment_schedule_id = schedule.payment_schedule_id
    AND    item.payment_term_id = p_term_id
    AND    item.payment_item_type_lookup_code = 'NORMALIZED'
    AND    schedule.payment_status_lookup_code  <> 'DRAFT'; /*= 'APPROVED'; for bug# 7149537*/

Line: 577

    SELECT MAX(schedule.schedule_date) last_apprv_sch
    FROM   pn_payment_schedules_all schedule,
           pn_payment_items_all item
    WHERE  schedule.lease_id = p_lease_id
    AND    item.payment_schedule_id = schedule.payment_schedule_id
    AND    schedule.payment_status_lookup_code = 'APPROVED'
    AND    item.payment_term_id = p_term_id;

Line: 588

    SELECT MIN(schedule.schedule_date) first_draft_sch
    FROM   pn_payment_schedules_all schedule,
           pn_payment_items_all item
    WHERE  schedule.lease_id = p_lease_id
    AND    item.payment_schedule_id = schedule.payment_schedule_id
    AND    schedule.payment_status_lookup_code = 'DRAFT'
    AND    item.payment_term_id = p_term_id
    AND    item.last_adjustment_type_code IS NULL
    AND    schedule.schedule_date > p_last_apprv_sch;

Line: 600

    SELECT MIN(schedule.schedule_date) first_draft_sch
    FROM   pn_payment_schedules_all schedule,
           pn_payment_items_all item
    WHERE  schedule.lease_id = p_lease_id
    AND    item.payment_schedule_id = schedule.payment_schedule_id
    AND    schedule.payment_status_lookup_code = 'DRAFT'
    AND    item.payment_term_id = p_term_id
    AND    item.last_adjustment_type_code IS NULL;

Line: 611

   SELECT MIN(schedule.schedule_date) first_draft_sch,
          schedule.payment_schedule_id pay_schd_id
   FROM   pn_payment_schedules_all schedule,
          pn_payment_items_all item
   WHERE  item.payment_schedule_id = schedule.payment_schedule_id
   AND    schedule.payment_status_lookup_code = 'DRAFT'
   AND    item.payment_term_id = p_term_id
   GROUP BY schedule.payment_schedule_id;

Line: 643

    SELECT org_id
    FROM pn_payment_terms_all
    WHERE payment_term_id = p_term_id;

Line: 650

  SELECT 1 FROM dual
  WHERE  exists
         (SELECT 1
          FROM   pn_payment_items_all ppi
          WHERE  ppi.payment_schedule_id = b_schedule_id
          AND    ppi.payment_item_type_lookup_code = 'CASH'
          AND    ppi.payment_term_id = b_term_id
         );

Line: 868

  /* start to create/update normalized items */
  FOR i IN 0 .. g_norm_item_tbl.COUNT - 1 LOOP

    /* first try to update */
    UPDATE pn_payment_items_all ppi
    SET    ppi.actual_amount = g_norm_item_tbl(i).normalized_amount,
           ppi.export_currency_amount = g_norm_item_tbl(i).normalized_amount,
           ppi.last_update_date = SYSDATE,
           ppi.last_updated_by  = NVL(fnd_profile.value('USER_ID'),0),
           ppi.last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
    WHERE  ppi.payment_schedule_id = g_norm_item_tbl(i).schedule_id
    AND    ppi.payment_item_type_lookup_code = 'NORMALIZED'
    AND    ppi.payment_term_id = p_term_id;

Line: 888

      INSERT INTO pn_payment_items_all
        (payment_item_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login,
         actual_amount,
         estimated_amount,
         due_date,
         payment_item_type_lookup_code,
         payment_term_id,
         payment_schedule_id,
         period_fraction,
         vendor_id,
         customer_id,
         vendor_site_id,
         customer_site_use_id,
         cust_ship_site_id,
         set_of_books_id,
         currency_code,
         export_currency_code,
         export_currency_amount,
         rate,
         org_id)
      VALUES
        (PN_PAYMENT_ITEMS_S.NEXTVAL,
         SYSDATE,
         NVL(fnd_profile.value('USER_ID'),0),
         SYSDATE,
         NVL(fnd_profile.value('USER_ID'),0),
         NVL(fnd_profile.value('LOGIN_ID'),0),
         g_norm_item_tbl(i).normalized_amount,
         NULL,
         g_norm_item_tbl(i).schedule_date,
         'NORMALIZED',
         p_term_id,
         g_norm_item_tbl(i).schedule_id,
         1,
         p_vendor_id,
         p_cust_id,
         p_vendor_site_id,
         p_cust_site_use_id,
         p_cust_ship_site_id,
         p_sob_id,
         p_curr_code,
         p_curr_code,
         g_norm_item_tbl(i).normalized_amount,
         p_rate,
         l_org_id
         );

Line: 975

  /* update the table */
  UPDATE pn_payment_terms_all
  SET    event_type_code = p_lease_context,
         norm_start_date = p_norm_str_dt,
         norm_end_date   = p_norm_end_dt
  WHERE  normalize ='Y'
  AND    payment_term_id = p_term_id;

Line: 1032

    SELECT pps.schedule_date schedule_date,
           pps.payment_schedule_id payment_schedule_id
    FROM   pn_payment_schedules_all pps,
           pn_payment_items_all ppi
    WHERE  pps.lease_id = p_lease_id
    AND    pps.schedule_date BETWEEN
	NVL(PN_SCHEDULES_ITEMS.FIRST_DAY(l_amd_comn_date),PN_SCHEDULES_ITEMS.FIRST_DAY(c_norm_str_dt)) /*6838211 */
                                 AND LAST_DAY(g_new_lea_term_dt) --AND LAST_DAY(p_norm_end_dt) /*Bug4956314*/
    AND    pps.payment_status_lookup_code = 'DRAFT' /* bug 6737971 removed
ON_HOLD */
    AND    TO_CHAR(pps.schedule_date,'DD') = p_sch_day
    AND    ppi.PAYMENT_SCHEDULE_ID(+) = pps.PAYMENT_SCHEDULE_ID /*Bug4956314*/
    AND    ppi.PAYMENT_TERM_ID(+) = p_term_id                   /*Bug4956314*/
    AND    ppi.PAYMENT_ITEM_TYPE_LOOKUP_CODE(+) = 'CASH'        /*Bug4956314*/
    AND    ppi.LAST_ADJUSTMENT_TYPE_CODE IS NULL
    ORDER BY pps.schedule_date;

Line: 1050

    SELECT pld.lease_commencement_date lease_commencement_date,
              pld.lease_termination_date new_lease_term_date,
           pl.payment_term_proration_rule pr_rule
    FROM   pn_leases_all pl,
           pn_lease_details_all pld
    WHERE  pl.lease_id = p_lease_id
    AND    pld.lease_id = pl.lease_id;

Line: 1062

    SELECT org_id
    FROM pn_payment_terms_all
    WHERE payment_term_id = p_term_id;

Line: 1077

SELECT change_commencement_date
INTO l_amd_comn_date
FROM
  (SELECT *
   FROM pn_lease_changes_all
   WHERE lease_id = p_lease_id
   ORDER BY lease_change_id DESC)
WHERE rownum < 2;

Line: 1088

     SELECT lease_commencement_date
     INTO l_amd_comn_date
     FROM pn_lease_details_all
     WHERE lease_id = p_lease_id;

Line: 1105

  g_norm_item_tbl.DELETE;