APPS.OZF_CLAIM_GRP SQL Statements

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

Line: 26

G_UPDATE_EVENT             CONSTANT  VARCHAR2(30) := 'UPDATE';

Line: 217

PROCEDURE update_parent_amounts(
    x_return_status              OUT NOCOPY VARCHAR2,
    x_msg_count                  OUT NOCOPY NUMBER,
    x_msg_data                   OUT NOCOPY VARCHAR2,
    p_deduction_rec              IN  DEDUCTION_REC_TYPE
) IS
l_api_version    CONSTANT NUMBER       := 1.0;

Line: 224

l_api_name       CONSTANT VARCHAR2(30) := 'update_parent_amounts';

Line: 236

   SELECT root_claim_id
   ,      claim_id
   ,      object_version_number
   ,      claim_number
   ,      amount
   ,      amount_adjusted
   ,      amount_remaining
   ,      amount_settled
   ,      status_code
   ,      receipt_number
   ,      claim_class
   ,      split_from_claim_id
   FROM   ozf_claims
   WHERE  split_from_claim_id IS NULL
   AND    root_claim_id = p_claim_id
   ORDER BY claim_id desc;

Line: 254

   SELECT SUM(amount)
   FROM   ozf_claims
   WHERE  split_from_claim_id = p_claim_id;

Line: 290

                   OZF_claim_PVT.Update_claim(
                    p_api_version            => 1.0,
                    p_init_msg_list          => FND_API.G_FALSE,
                    p_commit                 => FND_API.G_FALSE,
                    p_validation_level       => FND_API.G_VALID_LEVEL_FULL,
                    x_return_status          => l_return_status,
                    x_msg_count              => x_msg_count,
                    x_msg_data               => x_msg_data,
                    p_claim                  => l_pvt_claim_Rec,
                    p_event                  => G_SUBSEQUENT_APPLY_EVENT,  --G_UPDATE_EVENT
                    p_mode                   => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
                    x_object_version_number  => l_object_version_number
                  );

Line: 321

                      ,p_last_updated_by    => FND_GLOBAL.user_id
                      ,x_jtf_note_id        => l_x_note_id
                      ,p_note_type          => 'AMS_JUSTIFICATION'
                      ,p_last_update_date   => SYSDATE
                      ,p_creation_date      => SYSDATE
                  );

Line: 359

END update_parent_amounts;

Line: 502

  SELECT receipt_number
  FROM ar_cash_receipts
  WHERE cash_receipt_id = cv_cash_receipt_id;

Line: 507

  SELECT trx_number
  FROM ra_customer_trx
  WHERE customer_trx_id = cv_customer_trx_id;

Line: 512

  SELECT type
  FROM ra_cust_trx_types
  WHERE cust_trx_type_id = cv_cust_trx_type_id;

Line: 517

  SELECT c.claim_number
  ,      c.reason_code_id
  ,      r.name
  FROM ozf_claims c
  ,    ozf_reason_codes_vl r
  WHERE c.claim_id = cv_claim_id
  AND c.reason_code_id = r.reason_code_id;

Line: 942

PROCEDURE Check_Update_Allowed(
      p_deduction_rec          IN  DEDUCTION_REC_TYPE,
      x_applicable_claims_tbl  OUT NOCOPY DEDUCTION_TBL_TYPE,
      x_notes_tbl              OUT NOCOPY CLAIM_NOTES_TBL_TYPE,
      x_update_allowed_flag    OUT NOCOPY VARCHAR2,
      x_return_status          OUT NOCOPY VARCHAR2
)
IS
l_api_version    CONSTANT NUMBER       := 1.0;

Line: 951

l_api_name       CONSTANT VARCHAR2(30) := 'Check_Update_Allowed';

Line: 970

   SELECT root_claim_id
   ,      claim_id
   ,      object_version_number
   ,      claim_number
   ,      amount
   ,      amount_adjusted
   ,      amount_remaining
   ,      amount_settled
   ,      status_code
   ,      receipt_number
   ,      claim_class
   ,      appr_wf_item_key
   ,      split_from_claim_id
    FROM   ozf_claims
   WHERE  root_claim_id = p_claim_id
   ORDER BY claim_id desc;

Line: 1000

   x_update_allowed_flag := 'Y';

Line: 1161

END Check_Update_Allowed;

Line: 1218

l_update_allowed_flag     VARCHAR2(1);

Line: 1228

   SELECT count(claim_id)
   FROM   ozf_claims
   WHERE  root_claim_id = p_claim_id;

Line: 1234

   SELECT status_code
   ,      amount
   ,      receipt_number
   ,      claim_class
   ,      source_object_id
   FROM ozf_claims
   WHERE claim_id = p_claim_id;

Line: 1244

   SELECT root_claim_id
   ,      claim_id
   ,      object_version_number
   ,      claim_number
   ,      amount,amount_adjusted
   ,      amount_remaining
   ,      amount_settled
   ,      receipt_number
   ,      status_code
   FROM ozf_claims
   WHERE root_claim_id = p_claim_id
   AND status_code <> 'CLOSED'
   ORDER BY claim_id;

Line: 1260

   SELECT root_claim_id
   ,      claim_id
   ,      object_version_number
   ,      claim_number
   ,      amount
   ,      amount_adjusted
   ,      amount_remaining
   ,      amount_settled
   ,      status_code
   ,      receipt_number
   ,      claim_class
   ,      split_from_claim_id
   FROM   ozf_claims
   WHERE  split_from_claim_id IS NULL
   AND    root_claim_id = p_claim_id
   ORDER BY claim_id desc;

Line: 1278

   SELECT SUM(amount)
   FROM   ozf_claims
   WHERE  split_from_claim_id = p_claim_id;

Line: 1470

         OZF_claim_PVT.Update_claim(
              p_api_version           => 1.0,
              p_init_msg_list         => FND_API.G_FALSE,
              p_commit                => FND_API.G_FALSE,
              p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
              x_return_status         => l_return_status,
              x_msg_count             => x_msg_count,
              x_msg_data              => x_msg_data,
              p_claim                 => l_pvt_claim_rec,
              p_event                 => g_subsequent_apply_event, --g_update_event
              p_mode                  => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
              x_object_version_number => l_object_version_number
         );

Line: 1516

          ,p_last_updated_by    => FND_GLOBAL.user_id
          ,x_jtf_note_id        => l_x_note_id
          ,p_note_type          => 'AMS_JUSTIFICATION'
          ,p_last_update_date   => SYSDATE
          ,p_creation_date      => SYSDATE
      );

Line: 1634

            OZF_claim_PVT.Update_claim(
                 p_api_version            => 1.0,
                 p_init_msg_list          => FND_API.G_FALSE,
                 p_commit                 => FND_API.G_FALSE,
                 p_validation_level       => FND_API.G_VALID_LEVEL_FULL,
                 x_return_status          => l_return_status,
                 x_msg_count              => x_msg_count,
                 x_msg_data               => x_msg_data,
                 p_claim                  => l_pvt_claim_Rec,
                 p_event                  => G_SUBSEQUENT_APPLY_EVENT,  --G_UPDATE_EVENT
                 p_mode                   => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
                 x_object_version_number  => l_object_version_number
            );

Line: 1667

                ,p_last_updated_by    => FND_GLOBAL.user_id
                ,x_jtf_note_id        => l_x_note_id
                ,p_note_type          => 'AMS_JUSTIFICATION'
                ,p_last_update_date   => SYSDATE
                ,p_creation_date      => SYSDATE
            );

Line: 1681

         Check_Update_Allowed(
                p_deduction_rec         => l_deduction_rec
               ,x_applicable_claims_tbl => l_Applicable_Claims_Tbl
               ,x_notes_tbl             => l_notes_tbl
               ,x_update_allowed_flag   => l_update_allowed_flag
               ,x_return_status         => l_return_status
         );

Line: 1732

               OZF_claim_PVT.Update_claim(
                    p_api_version            => 1.0,
                    p_init_msg_list          => FND_API.G_FALSE,
                    p_commit                 => FND_API.G_FALSE,
                    p_validation_level       => FND_API.G_VALID_LEVEL_FULL,
                    x_return_status          => l_return_status,
                    x_msg_count              => x_msg_count,
                    x_msg_data               => x_msg_data,
                    p_claim                  => l_pvt_claim_Rec,
                    p_event                  => G_SUBSEQUENT_APPLY_EVENT,  --G_UPDATE_EVENT
                    p_mode                   => OZF_CLAIM_UTILITY_PVT.G_AUTO_MODE,
                    x_object_version_number  => l_object_version_number
               );

Line: 1764

                      ,p_last_updated_by    => FND_GLOBAL.user_id
                      ,x_jtf_note_id        => l_x_note_id
                      ,p_note_type          => 'AMS_JUSTIFICATION'
                      ,p_last_update_date   => SYSDATE
                      ,p_creation_date      => SYSDATE
                  );

Line: 1775

	   update_parent_amounts(
		     x_return_status      => l_return_status
		    ,x_msg_count          => x_msg_count
		    ,x_msg_data           => x_msg_data
		    ,p_deduction_rec      => l_deduction_rec);

Line: 1887

l_update_allowed_flag     VARCHAR2(1);

Line: 1906

SELECT count(*)
FROM   ozf_claims_all
WHERE  root_claim_id = p_claim_id;

Line: 1912

SELECT status_code,amount,receipt_number,claim_class,
       amount_remaining+amount_settled amount_remaining,
       source_object_id
FROM   ozf_claims_all
WHERE  claim_id = p_claim_id;

Line: 1920

SELECT claim_class,
       sum(amount_remaining+amount_settled) amount_remaining,
       source_object_id
FROM   ozf_claims_all
WHERE  root_claim_id = p_claim_id
GROUP  BY claim_class, source_object_id;

Line: 1929

SELECT root_claim_id,
       claim_id,
       object_version_number,
       claim_number,
       receipt_number,
       status_code,
       amount,
       amount_adjusted,
       amount_remaining,
       amount_settled
FROM   ozf_claims_all
WHERE  root_claim_id = p_claim_id
ORDER  BY claim_id;

Line: 1945

SELECT claim_class,reason_code_id,object_version_number,claim_type_id
FROM   ozf_claims_all
WHERE  claim_id = p_claim_id;

Line: 1951

SELECT claim_id,claim_number
FROM   ozf_claims_all
WHERE  root_claim_id = p_claim_id
AND    claim_id      = (Select max(claim_id)
                       from ozf_claims_all
                       where root_claim_id = p_claim_id)
AND    trunc(creation_date) = trunc(sysdate);

Line: 1961

SELECT amount_due_original
FROM   ar_payment_schedules a, ozf_claims_all b
WHERE  customer_trx_id = b.source_object_id
AND    b.claim_id      = p_claim_id;

Line: 1973

SELECT distinct applied_payment_schedule_id
 FROM  ar_receivable_applications_all
WHERE  cash_receipt_id = cv_receipt_id
  AND  applied_customer_trx_id = cv_customer_trx_id
  AND  application_ref_type = 'CLAIM'
  AND  secondary_application_ref_id = cv_claim_id
  AND  status = 'APP';

Line: 1985

SELECT COUNT(*)
  FROM ar_receivable_applications_all
 WHERE applied_payment_schedule_id = cv_ps_id
  AND  cash_receipt_id <> cv_receipt_id
  AND  application_ref_type = 'CLAIM'
  AND  secondary_application_ref_id = cv_claim_id
  AND  status = 'APP'
  AND  display = 'Y';

Line: 2165

      OZF_claim_PVT.Update_claim(
        P_Api_Version           => 1.0,
        P_Init_Msg_List         => FND_API.G_FALSE,
        P_Commit                => FND_API.G_FALSE,
        P_Validation_Level      => FND_API.G_VALID_LEVEL_FULL,
        X_Return_Status         => x_return_status,
        X_Msg_Count             => x_msg_count,
        X_Msg_Data              => x_msg_data,
        P_claim                 => l_pvt_claim_Rec,
        p_event                 => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
        p_mode                  => OZF_claim_Utility_pvt.G_AUTO_MODE,
        X_Object_Version_Number => l_object_version_number );

Line: 2210

       ,p_last_updated_by=> FND_GLOBAL.user_id
       ,x_jtf_note_id=> l_x_note_id
       ,p_note_type=> 'AMS_JUSTIFICATION'  --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification

Line: 2213

       ,p_last_update_date=> SYSDATE
       ,p_creation_date=> SYSDATE
       );

Line: 2255

      l_update_allowed_flag := 'Y';

Line: 2258

          l_update_allowed_flag := 'N';

Line: 2262

      l_update_allowed_flag := 'Y';

Line: 2265

    Write_Log(l_full_name, 'Update Allowed ? ' || l_update_allowed_flag );

Line: 2267

    IF l_update_allowed_flag = 'N' THEN

      IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
        FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_STATUS_PENDING_CLOSE');

Line: 2304

               OZF_claim_PVT.Update_claim(
                   p_api_version           => 1.0,
                   p_init_msg_list         => FND_API.G_FALSE,
                   p_commit                => FND_API.G_FALSE,
                   p_validation_level      => FND_API.G_VALID_LEVEL_FULL,
                   x_return_status         => x_return_status,
                   x_msg_count             => x_msg_count,
                   x_msg_data              => x_msg_data,
                   p_claim                 => l_pvt_claim_Rec,
                   p_event                 => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
                   p_mode                  => OZF_claim_Utility_pvt.G_AUTO_MODE,
                   x_object_version_number => l_object_version_number
               );

Line: 2337

                   ,p_last_updated_by       => FND_GLOBAL.user_id
                   ,x_jtf_note_id           => l_x_note_id
                   ,p_note_type             => 'AMS_JUSTIFICATION'  --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification

Line: 2340

                   ,p_last_update_date      => SYSDATE
                   ,p_creation_date         => SYSDATE
               );

Line: 2423

             OZF_claim_PVT.Update_claim(
                P_Api_Version           => 1.0,
                P_Init_Msg_List         => FND_API.G_FALSE,
                P_Commit                => FND_API.G_FALSE,
                P_Validation_Level      => FND_API.G_VALID_LEVEL_FULL,
                X_Return_Status         => x_return_status,
                X_Msg_Count             => x_msg_count,
                X_Msg_Data              => x_msg_data,
                P_claim                 => l_pvt_claim_Rec,
                p_event                 => G_SUBSEQUENT_UNAPPLY_EVENT, --G_UPDATE_EVENT
                p_mode                  => OZF_claim_Utility_pvt.G_AUTO_MODE,
                X_Object_Version_Number => l_object_version_number );

Line: 2456

                ,p_last_updated_by=> FND_GLOBAL.user_id
                ,x_jtf_note_id=> l_x_note_id
                ,p_note_type=> 'AMS_JUSTIFICATION'  --'AMS_DEDU' Deduction Notes; use'AMS_JUSTIFICATION' for Justification

Line: 2459

                ,p_last_update_date=> SYSDATE
                ,p_creation_date=> SYSDATE
                );

Line: 2464

              update_parent_amounts(
                    x_return_status      => l_return_status
                   ,x_msg_count          => x_msg_count
                   ,x_msg_data           => x_msg_data
                   ,p_deduction_rec      => l_deduction_rec);

Line: 2549

PROCEDURE Update_Deduction(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2,
    p_validation_level           IN   NUMBER,
    p_commit                     IN   VARCHAR2,

    x_return_status              OUT  NOCOPY  VARCHAR2,
    x_msg_count                  OUT  NOCOPY  NUMBER,
    x_msg_data                   OUT  NOCOPY  VARCHAR2,

    p_deduction                  IN   DEDUCTION_REC_TYPE,
    x_object_version_number      OUT  NOCOPY  NUMBER
)
IS
x_claim_reason_code_id     NUMBER;

Line: 2572

   OZF_Claim_GRP.Update_Deduction(
             p_api_version_number,
             p_init_msg_list,
             p_validation_level,
             p_commit,
             x_return_status,
             x_msg_count,
             x_msg_data,
             p_deduction,
             x_object_version_number,
             x_claim_reason_code_id,
             x_claim_reason_name,
             x_claim_id,
             x_claim_number
   );

Line: 2600

End Update_Deduction;

Line: 2630

PROCEDURE Update_Deduction(
    p_api_version_number         IN   NUMBER,
    p_init_msg_list              IN   VARCHAR2     := FND_API.G_FALSE,
    p_validation_level           IN   NUMBER       := FND_API.G_VALID_LEVEL_FULL,
    p_commit                     IN   VARCHAR2     := FND_API.G_FALSE,

    x_return_status              OUT  NOCOPY  VARCHAR2,
    x_msg_count                  OUT  NOCOPY  NUMBER,
    x_msg_data                   OUT  NOCOPY  VARCHAR2,

    p_deduction                  IN   DEDUCTION_REC_TYPE,
    x_object_version_number      OUT  NOCOPY  NUMBER,
    x_claim_reason_code_id       OUT  NOCOPY  NUMBER,
    x_claim_reason_name          OUT  NOCOPY  VARCHAR2,
    x_claim_id                   OUT  NOCOPY  NUMBER,
    x_claim_number               OUT  NOCOPY  VARCHAR2
)
IS
l_api_version    CONSTANT NUMBER       := 1.0;

Line: 2649

l_api_name       CONSTANT VARCHAR2(30) := 'Update_Deduction';

Line: 2658

  SELECT root_claim_id
  FROM ozf_claims
  WHERE source_object_id = cv_source_object_id;

Line: 2665

  SELECT claim_number
  ,      object_version_number
  FROM ozf_claims
  WHERE claim_id = cv_claim_id;

Line: 2671

  SELECT receipt_number
  FROM ar_cash_receipts
  WHERE cash_receipt_id = cv_receipt_id;

Line: 2676

  SELECT c.claim_id
  ,      c.claim_number
  ,      c.object_version_number
  ,      c.reason_code_id
  ,      r.name
  FROM ozf_claims c
  , ozf_reason_codes_vl r
  WHERE c.claim_id = cv_claim_id
  AND c.reason_code_id = r.reason_code_id;

Line: 2688

  SELECT amount ,
         due_date ,
         claim_date ,
         claim_type_id ,
         reason_code_id ,
         currency_code ,
         cust_account_id
  FROM ozf_claims
  WHERE claim_id = cv_claim_id;

Line: 2716

SELECT object_version_number,
       reason_code_id,
       claim_id,
       claim_number
FROM   ozf_claims_all
WHERE  claim_id = p_id;

Line: 2725

SELECT name
FROM   ozf_reason_codes_all_tl
WHERE  reason_code_id = p_reason_code_id;

Line: 2731

SELECT object_version_number
FROM   ozf_claims_all
WHERE  claim_id = p_id;

Line: 2737

SELECT claim_number
FROM   ozf_claims_all
WHERE  claim_id = p_id;

Line: 2743

SELECT root_claim_id,claim_number
FROM   ozf_claims_all
WHERE  source_object_id = p_source_object_id
AND    root_claim_id = claim_id;

Line: 2752

   SELECT  ra.cash_receipt_id,
           cr.receipt_number
   FROM  ar_receivable_applications_all ra,
         ar_cash_receipts_all cr
   WHERE ra.cash_receipt_id              =  cr.cash_receipt_id
   AND   ra.cash_receipt_id              <>  p_cash_receipt_id
   AND   ra.application_ref_type         = 'CLAIM'
   AND   ra.secondary_application_ref_id = p_claim_id
   AND   ra.status                       = 'APP'
   AND   ra.display                      = 'Y';

Line: 2768

   SAVEPOINT UPDATE_CLAIM_GRP;

Line: 3053

      ROLLBACK TO UPDATE_CLAIM_GRP;

Line: 3062

      ROLLBACK TO UPDATE_CLAIM_GRP;

Line: 3071

      ROLLBACK TO UPDATE_claim_GRP;

Line: 3083

END Update_Deduction;

Line: 3113

   l_sql_stmt := 'SELECT root_claim_id, status_code '||
                 'FROM ozf_claims  '||
                 'WHERE claim_id = :1 ';

Line: 3133

      l_sql_stmt := 'SELECT status_code, amount_remaining '||
                    'FROM ozf_claims '||
                    'WHERE root_claim_id = :1 ';

Line: 3189

      l_sql_stmt := 'SELECT COUNT(claim_id) '||
                    'FROM ozf_claims  '||
                    'WHERE source_object_id = :1 '||
                    'AND status_code <> ''CLOSED'' ';

Line: 3199

      l_sql_stmt := 'SELECT COUNT(claim_id) '||
                    'FROM ozf_claims  '||
                    'WHERE receipt_id = :1 '||
                    'AND status_code <> ''CLOSED'' ';

Line: 3246

   SELECT claim_id, claim_number, customer_ref_number FROM ozf_claims
     WHERE source_object_id = cv_customer_trx_id
     AND   claim_id = root_claim_id
     AND   root_claim_id IS NOT NULL
     AND   cust_account_id = (SELECT bill_to_customer_id from ra_customer_trx_all
                              WHERE customer_trx_id = cv_customer_trx_id);

Line: 3255

   l_sql_stmt := 'SELECT claim_id, claim_number, customer_ref_number '||
                 'FROM ozf_claims  '||
                 'WHERE source_object_id = :1 '||
                 'AND claim_id = root_claim_id ';