DBA Data[Home] [Help]

APPS.PN_MODIFY_PMT_SCHED SQL Statements

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

Line: 23

   SELECT payment_term_id,
          start_date,
          end_date,
          actual_amount,
          estimated_amount,
          payment_term_type_code,
          frequency_code,
          lease_id,
          set_of_books_id,
          currency_code
   FROM   pn_payment_terms_all               /*sdm14jul*/
   WHERE  lease_id = p_lease_id
   FOR    UPDATE OF end_date;
Line: 80

          SELECT pn_leases.lease_commencement_date,
                 pn_leases.lease_termination_date,
                 nvl(pn_leases.payment_term_rule,365),
                 pn_leases.lease_change_id
          INTO   lease_start_date, lease_end_date, proration_rule, pn_lease_change_id
          FROM   pn_leases_v pn_leases                   /*sdm??should form view be replaced*/
          WHERE  pn_leases.lease_id  = pt_rec.lease_id;
Line: 106

            SELECT count(*)
            INTO   items_paid
            FROM   pn_payment_schedules_all ps,             /*sdm14jul*/
                   pn_payment_items_all     pi              /*sdm14jul*/
            WHERE  ps.payment_schedule_id           = pi.payment_schedule_id
            AND    pi.payment_term_id               = pt_rec.payment_term_id
            AND    ps.payment_status_lookup_code   <> 'DRAFT'
            AND    pi.payment_item_type_lookup_code = 'CASH';
Line: 117

              delete from pn_payment_items_all              /*sdm14jul*/
              where  payment_term_id = pt_rec.payment_term_id;
Line: 121

              delete from pn_lease_milestones
              where  payment_term_id = pt_rec.payment_term_id
              and    RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM';*/
Line: 125

              delete from pn_payment_terms_all              /*sdm14jul*/
              where  current of pt_cur;
Line: 130

              SELECT min(due_date)
              INTO   first_payment_date
              FROM   pn_payment_items_all             /*sdm14jul*/
              WHERE  payment_term_id               = pt_rec.payment_term_id
              AND    payment_item_type_lookup_code = 'CASH';
Line: 136

              SELECT nvl(max(due_date),first_payment_date)
              INTO   item_to_change_date
              FROM   pn_payment_items_all             /*sdm14jul*/
              WHERE  payment_term_id = pt_rec.payment_term_id
              AND    due_date       <= greatest(lease_end_date, pt_rec.start_date);
Line: 147

                SELECT ps.payment_status_lookup_code, pi.payment_item_id
                INTO   sched_status, pi_id
                FROM   pn_payment_schedules_all ps,            /*sdm14jul*/
                       pn_payment_items_all     pi          /*sdm14jul*/
                WHERE  pi.payment_schedule_id           = ps.payment_schedule_id
                AND    pi.due_date                      = item_to_change_date
                AND    pi.payment_term_id               = pt_rec.payment_term_id
                AND    pi.payment_item_type_lookup_code = 'CASH';
Line: 194

                UPDATE pn_payment_items_all           /*sdm14jul*/
                SET   actual_amount    = decode(pt_rec.actual_amount,
                                                null,null, round(amount,2)),
                      estimated_amount = decode(pt_rec.actual_amount,
                                                null,round(amount,2),null),
                      period_fraction  = amount / nvl(pt_rec.actual_amount,
                                                      pt_rec.estimated_amount),
                      last_update_date = sysdate,
                      last_updated_by  = pn_user_id
                WHERE payment_item_id               = pi_id
                AND   payment_item_type_lookup_code = 'CASH';
Line: 208

              DELETE from pn_payment_items_all              /*sdm14jul*/
              WHERE  payment_term_id               = pt_rec.payment_term_id
              AND    due_date                      > item_to_change_date
              AND    payment_item_type_lookup_code = 'CASH'
              AND    payment_schedule_id in (SELECT payment_schedule_id
                                             FROM   pn_payment_schedules_all     /*sdm14jul*/
                                             WHERE  payment_status_lookup_code = 'DRAFT'
                                             AND    lease_id                   = pt_rec.lease_id
                                            );
Line: 220

                update pn_payment_terms_all           /*sdm14jul*/
                set    end_date = lease_end_date,
                       last_update_date = sysdate,
                       last_updated_by = pn_user_id
                where  payment_term_id = pt_rec.payment_term_id;
Line: 227

                update pn_lease_milestones
                set    milestone_date = lease_end_date,
                       last_update_date = sysdate,
                       last_updated_by = pn_user_id
                where  payment_term_id = pt_rec.payment_term_id
                and    RESPONSIBILITY_LOOKUP_CODE = 'PAYMENT_TERM'
                and    (description like 'End%'
                                      or description like 'Security Deposit refund due');*/
Line: 238

                update pn_payment_terms_all           /*sdm14jul*/
                set    end_date = pt_rec.start_date,
                       last_update_date = sysdate,
                       last_updated_by = pn_user_id
                where  payment_term_id = pt_rec.payment_term_id;
Line: 245

                update pn_lease_milestones
                set    milestone_date = pt_rec.start_date,
                       last_update_date = sysdate,
                       last_updated_by = pn_user_id
                where  payment_term_id = pt_rec.payment_term_id
                and    responsibility_lookup_code = 'PAYMENT_TERM'
                and    (description like 'End%'
                or description like 'Security Deposit refund due');*/
Line: 264

        SELECT max(schedule_date)
        INTO   min_sched_date
        FROM   pn_payment_schedules_all               /*sdm14jul*/
        WHERE  lease_id                    = pt_rec.lease_id
        AND    payment_status_lookup_code <> 'DRAFT';
Line: 274

        DELETE from pn_payment_items_all           /*sdm14jul*/
        WHERE  payment_item_type_lookup_code = 'NORMALIZED'
        AND    payment_term_id               = pt_rec.payment_term_id;
Line: 278

        SELECT count(*)
        INTO   actual_items
        FROM   pn_payment_items_all             /*sdm14jul*/
        WHERE  payment_term_id               = pt_rec.payment_term_id
        AND    actual_amount                is not null
        AND    payment_item_type_lookup_code = 'CASH';
Line: 361

          SELECT nvl(sum(actual_amount),0)
          INTO   total_cash
          FROM   pn_payment_items_all                 /*sdm14jul*/
          WHERE  payment_term_id               = pt_rec.payment_term_id
          AND    payment_item_type_lookup_code = 'CASH';
Line: 415

            SELECT max(payment_schedule_id)
            INTO   ps_id
            FROM   pn_payment_schedules_all           /*sdm14jul*/
            WHERE  lease_id = pt_rec.lease_id
            AND    schedule_date = rent_sched_date;
Line: 423

              SELECT pn_payment_schedules_s.nextval
              INTO   ps_id
              FROM   dual;
Line: 430

              SELECT org_id INTO l_org_id
              FROM pn_payment_schedules_all
              WHERE payment_schedule_id = ps_id;
Line: 435

                INSERT INTO pn_payment_schedules_all        /*sdm14jul*/
                (
                  payment_schedule_id,
                  last_update_date,
                  last_updated_by,
                  creation_date,
                  created_by,
                  schedule_date,
                  lease_id,
                  lease_change_id,
                  payment_status_lookup_code,
                  org_id                                /*sdm14jul*/
                )
                VALUES
                ( ps_id,
                  sysdate,
                  pn_user_id,
                  sysdate,
                  pn_user_id,
                  rent_sched_date,
                  pt_rec.lease_id,
                  pn_lease_change_id,
                  'APPROVED',
                  l_org_id                              /*sdm14jul*/
                 );
Line: 463

                INSERT INTO pn_payment_schedules_all        /*sdm14jul*/
                (
                  payment_schedule_id,
                  last_update_date,
                  last_updated_by,
                  creation_date,
                  created_by,
                  schedule_date,
                  lease_id,
                  lease_change_id,
                  payment_status_lookup_code,
                  org_id                                /*sdm14jul*/
                )
                VALUES
                (
                  ps_id,
                  sysdate,
                  pn_user_id,
                  sysdate,
                  pn_user_id,
                  rent_sched_date,
                  pt_rec.lease_id,
                  pn_lease_change_id,
                  'DRAFT',
                  l_org_id                              /*sdm14jul*/
                );
Line: 534

              SELECT org_id INTO l_org_id
              FROM pn_payment_terms_all
              WHERE payment_term_id = pt_rec.payment_term_id;
Line: 538

              INSERT INTO pn_payment_items_all           /*sdm14jul*/
              (
                payment_item_id,
                last_update_date,
                last_updated_by,
                creation_date,
                created_by,
                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,
                set_of_books_id,
                currency_code,
                rate,
                org_id                          /*sdm14jul*/
              )
              VALUES
              (
                pn_payment_items_s.nextval,
                sysdate,
                pn_user_id,
                sysdate,
                pn_user_id,
                amount,
                null,
                payment_date,
                'NORMALIZED',
                pt_rec.payment_term_id,
                ps_id,
                amount/decode(amount,0,1,months),
                null,
                null,
                null,
                null,
                pt_rec.set_of_books_id,
                pt_rec.currency_code,
                1,
                l_org_id                        /*sdm14jul*/
              );
Line: 594

        INSERT INTO pn_payment_items_all           /*sdm14jul*/
        (
          payment_item_id,
          last_update_date,
          last_updated_by,
          creation_date,
          created_by,
          estimated_amount,
          due_date,
          payment_item_type_lookup_code,
          payment_term_id,
          payment_schedule_id,
          period_fraction,
          set_of_books_id,
          currency_code,
          rate,
          org_id
        )
        SELECT
                pn_payment_items_s.nextval,
                sysdate,
                pn_user_id,
                sysdate,
                pn_user_id,
                nvl(actual_amount, estimated_amount),
                due_date,
                'NORMALIZED',
                payment_term_id,
                payment_schedule_id,
                1,
                set_of_books_id,
                currency_code,
                rate,
                l_org_id                        /*sdm14jul*/
        FROM    pn_payment_items_all            /*sdm14jul*/
        WHERE   payment_term_id = pt_rec.payment_term_id
        AND     actual_amount  is null;
Line: 636

   DELETE FROM pn_payment_schedules_all ps         /*sdm14jul*/
   WHERE  ps.lease_id = pn_lease_id
   AND    NOT exists (SELECT 'x'
                      FROM   pn_payment_items_all pi     /*sdm14jul*/
                      WHERE  pi.payment_schedule_id = ps.payment_schedule_id);