APPS.PN_NORM_RENORM_PKG SQL Statements

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

Line: 68

    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: 75

    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: 105

    SELECT org_id
    FROM pn_payment_terms_all
    WHERE payment_term_id = p_term_id;

Line: 112

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: 285

  /* 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: 305

      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: 394

  /* 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: 437

    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: 443

    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 = 'APPROVED';

Line: 453

    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: 464

    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: 476

    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: 487

   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: 519

    SELECT org_id
    FROM pn_payment_terms_all
    WHERE payment_term_id = p_term_id;

Line: 526

  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: 744

  /* 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: 764

      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: 851

  /* 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: 904

    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 PN_SCHEDULES_ITEMS.FIRST_DAY(c_norm_str_dt)
                                 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: 921

    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: 933

    SELECT org_id
    FROM pn_payment_terms_all
    WHERE payment_term_id = p_term_id;

Line: 954

  g_norm_item_tbl.DELETE;