DBA Data[Home] [Help]

APPS.OZF_SETTLEMENT_DOC_PVT SQL Statements

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

Line: 30

  SELECT claim_tax_incl_flag
   FROM  ozf_sys_parameters_all
  WHERE  org_id = cv_org_id;
Line: 75

SELECT claim_class
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
Line: 80

SELECT name
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cv_settlement_type_id;
Line: 85

SELECT trx_number
FROM ar_payment_schedules
WHERE payment_schedule_id = cv_settlement_type_id;
Line: 90

SELECT name
FROM ar_receivables_trx
WHERE receivables_trx_id = cv_settlement_type_id;
Line: 95

SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
AND lookup_code = cv_payment_method;
Line: 198

 |    Update_Payment_Detail
 |
 | NOTES
 |
 | HISTORY
 |    20-Apr-2005 Sahana  Bug4308188: Overloaded Procedure
 *=======================================================================*/
PROCEDURE Update_Payment_Detail(
    p_api_version            IN    NUMBER
   ,p_init_msg_list          IN    VARCHAR2
   ,p_commit                 IN    VARCHAR2
   ,p_validation_level       IN    NUMBER

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

   ,p_claim_id               IN    NUMBER
   ,p_payment_method         IN    VARCHAR2
   ,p_deduction_type         IN    VARCHAR2
   ,p_cash_receipt_id        IN    NUMBER
   ,p_customer_trx_id        IN    NUMBER
   ,p_adjust_id              IN    NUMBER
   ,p_settlement_doc_id      IN    NUMBER

   ,p_settlement_mode        IN    VARCHAR2
) IS
BEGIN

   Update_Payment_Detail(
            p_api_version            => p_api_version
           ,p_init_msg_list          => p_init_msg_list
           ,p_commit                 => p_commit
           ,p_validation_level       => p_validation_level
           ,x_return_status          => x_return_status
           ,x_msg_data               => x_msg_data
           ,x_msg_count              => x_msg_count
           ,p_claim_id               => p_claim_id
           ,p_payment_method         => p_payment_method
           ,p_deduction_type         => p_deduction_type
           ,p_cash_receipt_id        => p_cash_receipt_id
           ,p_customer_trx_id        => p_customer_trx_id
           ,p_adjust_id              => p_adjust_id
           ,p_settlement_doc_id      => p_settlement_doc_id
           ,p_settlement_mode        => p_settlement_mode
           ,p_settlement_amount      => NULL);
Line: 245

END Update_Payment_Detail;
Line: 250

 |    Update_Payment_Detail
 |
 | NOTES
 |
 | HISTORY
 |    15-MAR-2002  mchang  Create.
 *=======================================================================*/
PROCEDURE Update_Payment_Detail(
    p_api_version            IN    NUMBER
   ,p_init_msg_list          IN    VARCHAR2
   ,p_commit                 IN    VARCHAR2
   ,p_validation_level       IN    NUMBER

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

   ,p_claim_id               IN    NUMBER
   ,p_payment_method         IN    VARCHAR2
   ,p_deduction_type         IN    VARCHAR2
   ,p_cash_receipt_id        IN    NUMBER
   ,p_customer_trx_id        IN    NUMBER
   ,p_adjust_id              IN    NUMBER
   ,p_settlement_doc_id      IN    NUMBER

   ,p_settlement_mode        IN    VARCHAR2
   ,p_settlement_amount      IN    NUMBER --Bug4308188
)
IS
   l_api_version          CONSTANT NUMBER       := 1.0;
Line: 280

   l_api_name             CONSTANT VARCHAR2(30) := 'Update_Payment_Detail';
Line: 294

      SELECT pay.customer_trx_id
      ,      pay.cust_trx_type_id
      ,      pay.trx_number
      ,      rec.apply_date
      ,      rec.amount_applied
      ,      pay.status
      FROM ar_receivable_applications rec
      ,    ar_payment_schedules pay
      WHERE rec.cash_receipt_id = cv_cash_receipt_id
      AND rec.applied_payment_schedule_id = pay.payment_schedule_id
      AND pay.customer_trx_id = cv_customer_trx_id
      AND rec.display = 'Y';
Line: 309

      SELECT pay.customer_trx_id
      ,      pay.cust_trx_type_id
      ,      pay.trx_number
      ,      pay.trx_date --?? verify
      ,      pay.amount_applied
      ,      pay.status
      FROM ar_payment_schedules pay
      WHERE pay.customer_trx_id = cv_customer_trx_id;
Line: 320

     SELECT cust.customer_trx_id            --"settlement_id"
     ,      cust.cust_trx_type_id           --"settlement_type_id"
     ,      cust.trx_number                 --"settlement_number"
     ,      cust.trx_date                   --"settlement_date"
     ,      sum(cust_lines.extended_amount) --"settlement_amount"
     ,      pay.status                      --"status_code"
     FROM ra_customer_trx cust
     ,   ra_customer_trx_lines cust_lines
     ,   ar_payment_schedules pay
     WHERE cust.customer_trx_id = cv_customer_trx_id
     AND cust.customer_trx_id = pay.customer_trx_id
     AND cust.customer_trx_id = cust_lines.customer_trx_id
     AND pay.customer_trx_id = cust_lines.customer_trx_id  -- fix for bug 4897546
     AND cust.complete_flag = 'Y'
    GROUP BY cust.customer_trx_id
           , cust.cust_trx_type_id
           , cust.trx_number
           , cust.trx_date
           , pay.status;
Line: 343

      SELECT pay.customer_trx_id
      ,      pay.payment_schedule_id
      ,      pay.trx_number
      ,      rec.apply_date
      ,      rec.amount_applied
      ,      pay.status
      FROM ar_receivable_applications rec
      ,    ar_payment_schedules pay
      WHERE rec.cash_receipt_id = cv_cash_receipt_id
      AND rec.applied_payment_schedule_id = pay.payment_schedule_id
      AND pay.payment_schedule_id = -1
      AND rec.display='Y'
      ORDER BY rec.receivable_application_id desc;
Line: 359

      SELECT pay.customer_trx_id
      ,      pay.payment_schedule_id
      ,      pay.trx_number
      ,      rec.apply_date
      ,      rec.amount_applied
      ,      pay.status
      FROM ar_receivable_applications rec
      ,    ar_payment_schedules pay
      WHERE rec.cash_receipt_id = cv_cash_receipt_id
      AND rec.applied_payment_schedule_id = pay.payment_schedule_id
      AND pay.payment_schedule_id = -3
      AND rec.display='Y'
      ORDER BY rec.receivable_application_id desc;
Line: 375

      SELECT adj.adjustment_id        --"settlement_id"
      , adj.receivables_trx_id        --"settlement_type_id"
      , adj.adjustment_number         --"settlement_number"
      , adj.apply_date                --"settlement_date"
      , adj.amount                    --"settlement_amount"
      , pay.status                    --"status_code"
      FROM ar_adjustments adj
      , ar_payment_schedules pay
      WHERE adj.payment_schedule_id = pay.payment_schedule_id
      AND adj.adjustment_id = cv_adjust_id;
Line: 388

      SELECT object_version_number
      FROM ozf_claims
      WHERE claim_id = cv_claim_id;
Line: 394

      SELECT pay.customer_trx_id
      ,      pay.cust_trx_type_id
      ,      pay.trx_number
      ,      sysdate
      ,      (oc.amount_settled * -1)
      ,      pay.status
      FROM ar_payment_schedules pay
      ,    ozf_claims oc
      WHERE pay.customer_trx_id = oc.payment_reference_id
      AND oc.claim_id = cv_claim_id;
Line: 406

      SELECT object_version_number
      FROM ozf_settlement_docs_all
      WHERE settlement_doc_id = cv_settlement_doc_id;
Line: 412

      SELECT payment_method,
      amount
      FROM ozf_claims
      WHERE claim_id = cv_claim_id;
Line: 419

      SELECT claim_class
      FROM ozf_claims_all
      WHERE claim_id = cv_claim_id;
Line: 424

      SELECT name
      FROM ra_cust_trx_types
      WHERE cust_trx_type_id = cv_settlement_type_id;
Line: 429

      SELECT trx_number
      FROM ar_payment_schedules
      WHERE payment_schedule_id = cv_settlement_type_id;
Line: 434

      SELECT name
      FROM ar_receivables_trx
      WHERE receivables_trx_id = cv_settlement_type_id;
Line: 439

      SELECT meaning
      FROM ozf_lookups
      WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
      AND lookup_code = cv_payment_method;
Line: 867

               OZF_SETTLEMENT_DOC_PVT.Update_Settlement_Doc(
                     p_api_version_number => l_api_version,
                     p_init_msg_list      => p_init_msg_list,
                     p_commit             => p_commit,
                     p_validation_level   => p_validation_level,
                     x_return_status      => l_return_status,
                     x_msg_count          => x_msg_count,
                     x_msg_data           => x_msg_data,
                     p_settlement_doc_rec => l_settlement_doc_tbl(j),
                     x_object_version_number  => l_dummy_number
               );
Line: 907

END Update_Payment_Detail;
Line: 912

 |    Update_Claim_Tax_Amount
 |
 | NOTES
 |
 | HISTORY
 |    16-MAY-2002  mchang  Create.
 *=======================================================================*/
PROCEDURE Update_Claim_Tax_Amount(
      p_claim_rec         IN  OZF_CLAIM_PVT.claim_rec_type
     ,x_return_status     OUT NOCOPY VARCHAR2
     ,x_msg_data          OUT NOCOPY VARCHAR2
     ,x_msg_count         OUT NOCOPY NUMBER
)
IS
   l_api_version          CONSTANT NUMBER       := 1.0;
Line: 927

   l_api_name             CONSTANT VARCHAR2(30) := 'Update_Claim_Tax_Amount';
Line: 939

  SELECT gs.currency_code
  FROM   gl_sets_of_books gs
  ,      ozf_sys_parameters org
  WHERE  org.set_of_books_id = gs.set_of_books_id
  AND    org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
Line: 1059

       UPDATE ozf_claims_all
       SET tax_amount = NVL(p_claim_rec.tax_amount,0)
       ,   acctd_tax_amount = NVL(l_acctd_tax_amount,0)
       ,   amount_settled = p_claim_rec.amount_settled
       ,   acctd_amount_settled = l_acctd_amount_settled
       ,   amount_remaining = l_amount_remaining
       ,   acctd_amount_remaining = l_acctd_amount_remaining
       WHERE claim_id = p_claim_rec.claim_id;
Line: 1098

END Update_Claim_Tax_Amount;
Line: 1103

 |    Update_Claim_Line_Status
 |
 | NOTES
 |
 | HISTORY
 |    14-NOV-2002  mchang  Create.
 *=======================================================================*/
PROCEDURE Update_Claim_Line_Status(
      p_claim_line_id     IN  NUMBER

     ,x_return_status     OUT NOCOPY VARCHAR2
     ,x_msg_data          OUT NOCOPY VARCHAR2
     ,x_msg_count         OUT NOCOPY NUMBER
)
IS
   l_api_version          CONSTANT NUMBER       := 1.0;
Line: 1119

   l_api_name             CONSTANT VARCHAR2(30) := 'Update_Claim_Line_Status';
Line: 1133

       UPDATE ozf_claim_lines_all
       SET payment_status = 'PAID'
       WHERE claim_line_id = p_claim_line_id;
Line: 1162

END Update_Claim_Line_Status;
Line: 1196

  SELECT  party_name
    FROM  hz_parties party,
          hz_cust_accounts_all cust
   WHERE  party.party_id = cust.party_id
     AND  cust.cust_account_id = cv_cust_account_id;
Line: 1219

 UPDATE ozf_claims
        SET status_code      = 'OPEN',
            user_status_id   = ozf_utility_pvt.get_default_user_status( 'OZF_CLAIM_STATUS', 'OPEN'),
            amount_remaining = NVL(amount_remaining,0) + amount_settled,
            acctd_amount_remaining = NVL(acctd_amount_remaining,0)+acctd_amount_settled,
            amount_settled   = 0,
            acctd_amount_settled = 0,
            payment_status   = null,
            payment_reference_id = DECODE(payment_method,'RMA',null,payment_reference_id),
            payment_reference_date = null,
            payment_reference_number = DECODE(payment_method,'RMA',null,payment_reference_number),
            last_updated_by = NVL(FND_GLOBAL.user_id,-1),
            last_update_login = NVL(FND_GLOBAL.conc_login_id,-1),
            last_update_date  = l_sys_date,
            settled_date      = null,
            settled_by        = null
     WHERE  claim_id = p_claim_id;
Line: 1259

          ,p_event          =>  'UPDATE'
          ,x_need_to_create => l_need_to_create
          ,x_claim_history_id => l_claim_history_id
    );
Line: 1275

        UPDATE ozf_settlement_docs_all stlmnt_docs
           SET   payment_status  = (SELECT  SUBSTRB(lkp.displayed_field,1,30)
                      FROM ap_lookup_codes lkp
                      where lkp.lookup_type = 'CHECK STATE'
                      and lkp.lookup_code IN (select status_lookup_code
                                      FROM  ap_invoice_payments pay, ap_checks chk
                                      WHERE pay.check_id = chk.check_id
                                     AND   pay.invoice_payment_id  = stlmnt_docs.settlement_id)),
                 last_updated_by   = NVL(FND_GLOBAL.user_id,-1),
                 last_update_login = NVL(FND_GLOBAL.conc_login_id,-1),
                 last_update_date  = l_sys_date
                 WHERE   claim_id = p_claim_id;
Line: 1331

          ,p_last_updated_by    => FND_GLOBAL.user_id
          ,x_jtf_note_id        => l_note_id
          ,p_note_type          => 'AMS_JUSTIFICATION'
          ,p_last_update_date   => l_sys_date
          ,p_creation_date      => l_sys_date);
Line: 1434

  SELECT payment_method,payment_reference_number
  FROM ozf_claims
  WHERE claim_id = cv_claim_id;
Line: 1439

  SELECT --pay.invoice_payment_id     --"settlement_id"
    chk.check_id                    --"settlement_id"
  , chk.payment_method_code         --"settlement_type"
  , chk.check_id                    --"settlement_type_id"
  , chk.check_number                --"settlement_number"
  , chk.check_date                  --"settlement_date"
  , sum(pay.amount)                      --"settlement_amount"
  , chk.status_lookup_code          --"status_code"
  , oc.payment_method               -- "payment_method"
  , sum(NVL(pay.discount_taken,0))
  , inv.invoice_amount
  FROM ap_invoice_payments_all pay
  ,    ap_checks_all chk
  ,    ap_invoices_all inv
  ,    ozf_claims_all  oc
  WHERE pay.check_id =  chk.check_id
  AND inv.invoice_id =  pay.invoice_id
  AND oc.claim_id    =  TO_NUMBER(inv.reference_key1) --//Fix for bug 13557215
  AND oc.claim_id     = cv_claim_id
  --AND oc.payment_reference_number = inv.invoice_num --//Fix for bug 13976621
  AND oc.vendor_id    = inv.vendor_id
  AND oc.vendor_site_id = inv.vendor_site_id
  GROUP BY chk.check_id , chk.payment_method_code , chk.check_number
  , chk.check_date, chk.status_lookup_code, oc.payment_method, inv.invoice_amount
  ORDER BY chk.check_id;
Line: 1467

  SELECT inv.invoice_id     --"settlement_id"
  , inv.invoice_type_lookup_code  --"settlement_type"
  , null                            --"settlement_type_id"
  , inv.invoice_num                  --"settlement_number"
  , inv.invoice_date                --"settlement_date"
  , inv.invoice_amount               --"settlement_amount"
  , AP_INVOICES_PKG.GET_APPROVAL_STATUS( INV.INVOICE_ID, INV.INVOICE_AMOUNT, INV.PAYMENT_STATUS_FLAG, INV.INVOICE_TYPE_LOOKUP_CODE) --"status_code"
  , 'AP_DEBIT'                      --"payment_method"
  , NULL
  , inv.invoice_amount
  FROM ap_invoices_all inv
  ,    ozf_claims_all oc
  WHERE oc.claim_id     = cv_claim_id
  --AND   oc.payment_reference_number = inv.invoice_num --//Fix for Bug 13976621
  AND oc.claim_id    =  TO_NUMBER(inv.reference_key1) --//Fix for bug 13557215
  AND oc.vendor_id    = inv.vendor_id
  AND oc.vendor_site_id = inv.vendor_site_id;
Line: 1488

   SELECT inv.invoice_num
     FROM ozf_claims_all  oc
         ,ap_invoices_all inv
   WHERE oc.claim_id    = TO_NUMBER(inv.reference_key1)
     --AND oc.payment_reference_number = inv.invoice_num --//Fix for Bug 13976621
     AND oc.claim_id    = cv_claim_id;
Line: 1520

     UPDATE ozf_claims_all
     SET payment_reference_number  = l_invoice_number
     WHERE claim_id = p_claim_id;
Line: 1551

     x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
Line: 1570

     x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
Line: 1621

  SELECT DISTINCT cust.customer_trx_id       --"settlement_id"
  , cust.cust_trx_type_id           --"settlement_type_id"
  , cust.trx_number                 --"settlement_number"
  , cust.trx_date                   --"settlement_date"
  , pay.amount_due_original      --"settlement_amount"
  , pay.status                      --"status_code"
  FROM ra_customer_trx cust,
  ra_customer_trx_lines lines,
  ar_payment_schedules pay
  where cust.customer_trx_id = lines.customer_trx_id
  and cust.customer_trx_id = pay.customer_trx_id
  and cust.complete_flag = 'Y'
  AND lines.interface_line_attribute2 = cv_claim_id
  AND lines.interface_line_attribute1 = cv_claim_number;
Line: 1639

 select pay.customer_trx_id                      --"settlement_id"
   , pay.cust_trx_type_id                          --"settlement_type_id"
   , pay.trx_number                                --"settlement_number"
   , pay.trx_date                                  --"settlement_date"
   , sum(pay.amount_due_original)                  --"settlement_amount"
 from  ar_payment_schedules pay ,  ( select  distinct customer_trx_id
         from  ozf_claim_lines ln ,    ra_customer_trx_lines cm_line
         where cm_line.line_type = 'LINE'
         and   cm_line.interface_line_context = 'ORDER ENTRY' --added filter for 4940650
         and   cm_line.interface_line_attribute6 = to_char(ln.payment_reference_id) -- order line id
         and   ln.claim_id = cv_claim_id) cla
  where pay.customer_trx_id = cla.customer_trx_id
  group by pay.customer_trx_id, pay.cust_trx_type_id,pay.trx_number, pay.trx_date;
Line: 1655

      SELECT claim_class, payment_method
      FROM ozf_claims_all
      WHERE claim_id = cv_claim_id;
Line: 1660

      SELECT name
      FROM ra_cust_trx_types
      WHERE cust_trx_type_id = cv_settlement_type_id;
Line: 1665

      SELECT trx_number
      FROM ar_payment_schedules
      WHERE payment_schedule_id = cv_settlement_type_id;
Line: 1670

      SELECT name
      FROM ar_receivables_trx
      WHERE receivables_trx_id = cv_settlement_type_id;
Line: 1675

      SELECT meaning
      FROM ozf_lookups
      WHERE lookup_type = 'OZF_CUSTOMIZED_OBJECT_CLASS'
      AND lookup_code = cv_payment_method;
Line: 1799

  SELECT settlement_id
  ,      settlement_type_id
  ,      settlement_number
  ,      settlement_date
  ,      settlement_amount
  ,      claim_id
  ,      claim_line_id
  ,      payment_method
  FROM ozf_settlement_docs
  WHERE claim_id = cv_claim_id;
Line: 1834

   x_settlement_doc_tbl.DELETE(l_counter); -- Last Record has junk
Line: 1862

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

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

    p_claim_id                   IN NUMBER,
    p_object_version_number      IN NUMBER,
    p_status_code                IN VARCHAR2,
    p_payment_status             IN VARCHAR2
)
IS
l_api_version_number    CONSTANT NUMBER   := 1.0;
Line: 1879

l_api_name              CONSTANT VARCHAR2(30) := 'Update_Claim_From_Settlement';
Line: 1907

l_claim_update_data		CLOB;
Line: 1908

l_item_key_update		VARCHAR2(50);
Line: 1909

l_event_name_update		VARCHAR2(80);
Line: 1910

l_parameter_list_update		wf_parameter_list_t;
Line: 1913

SELECT payment_status
FROM   ozf_claims_all
where  claim_id= p_claim_id;
Line: 1918

  SELECT user_status_id
  FROM ams_user_statuses_vl
  WHERE system_status_type = 'OZF_CLAIM_STATUS'
  AND  default_flag = 'Y'
  AND  system_status_code = cv_status_code;
Line: 1925

  SELECT   fu.fund_id,
                 fu.component_type,
                 fu.component_id,
                 SUM(lu.acctd_amount),
                 SUM(lu.plan_curr_amount),
                 SUM(lu.univ_curr_amount),
                 SUM(lu.util_curr_amount) -- 8710054
  FROM      ozf_claim_lines_util_all lu
                ,ozf_claim_lines_all l
                ,ozf_funds_utilized_all_b fu
  WHERE  l.claim_line_id = lu.claim_line_id
  AND       fu.utilization_id = lu.utilization_id
  AND       l.claim_id = cv_claim_id
  GROUP BY fu.fund_id,fu.component_type,fu.component_id;
Line: 1941

  SELECT object_version_number
  ,      paid_amt
  /* BEGIN OF BUG2740879 FIXING 01/21/2003 */
  --FROM ozf_funds
  FROM ozf_funds_all_b
  /* END OF BUG2740879 FIXING 01/21/2003 */
  WHERE fund_id = cv_fund_id;
Line: 1951

  SELECT gs.currency_code
  FROM   gl_sets_of_books gs
  ,      ozf_sys_parameters org
  WHERE  org.set_of_books_id = gs.set_of_books_id
  AND    org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
Line: 1958

  SELECT close_status_id
  FROM   ozf_claims_all oc,
         ams_user_statuses_vl us
  WHERE  oc.claim_id = p_claim_id
    AND  oc.close_status_id = us.user_status_id
    AND  us.system_status_type = 'OZF_CLAIM_STATUS'
    AND  us.system_status_code = p_status_code;
Line: 1971

  SELECT source_object_class,
         created_by
  FROM   ozf_claims_all
  WHERE  claim_id = p_claim_id;
Line: 1977

  SELECT ln.claim_line_id
  ,      ln.object_version_number
  ,      ln.last_update_date
  ,      ln.last_updated_by
  ,      ln.creation_date
  ,      ln.created_by
  ,      ln.last_update_login
  ,      ln.request_id
  ,      ln.program_application_id
  ,      ln.program_update_date
  ,      ln.program_id
  ,      ln.created_from
  ,      ln.claim_id
  ,      ln.line_number
  ,      ln.split_from_claim_line_id
  ,      ln.amount
  ,      ln.claim_currency_amount
  ,      ln.acctd_amount
  ,      ln.currency_code
  ,      ln.exchange_rate_type
  ,      ln.exchange_rate_date
  ,      ln.exchange_rate
  ,      ln.set_of_books_id
  ,      ln.valid_flag
  ,      ln.source_object_id
  ,      ln.source_object_class
  ,      ln.source_object_type_id
  ,      ln.source_object_line_id
  ,      ln.plan_id
  ,      ln.offer_id
  ,      ln.utilization_id
  ,      ln.payment_method
  ,      ln.payment_reference_id
  ,      ln.payment_reference_number
  ,      ln.payment_reference_date
  ,      ln.voucher_id
  ,      ln.voucher_number
  ,      ln.payment_status
  ,      ln.approved_flag
  ,      ln.approved_date
  ,      ln.approved_by
  ,      ln.settled_date
  ,      ln.settled_by
  ,      ln.performance_complete_flag
  ,      ln.performance_attached_flag
  ,      ln.item_id
  ,      ln.item_description
  ,      ln.quantity
  ,      ln.quantity_uom
  ,      ln.rate
  ,      ln.activity_type
  ,      ln.activity_id
  ,      ln.related_cust_account_id
  ,      ln.relationship_type
  ,      ln.earnings_associated_flag
  ,      ln.comments
  ,      ln.tax_code
  ,      ln.attribute_category
  ,      ln.attribute1
  ,      ln.attribute2
  ,      ln.attribute3
  ,      ln.attribute4
  ,      ln.attribute5
  ,      ln.attribute6
  ,      ln.attribute7
  ,      ln.attribute8
  ,      ln.attribute9
  ,      ln.attribute10
  ,      ln.attribute11
  ,      ln.attribute12
  ,      ln.attribute13
  ,      ln.attribute14
  ,      ln.attribute15
  ,      ln.org_id
  ,      ln.sale_date
  ,      ln.item_type
  ,      ln.tax_amount
  ,      ln.claim_curr_tax_amount
  ,      ln.activity_line_id
  ,      ln.offer_type
  ,      ln.prorate_earnings_flag
  ,      ln.earnings_end_date
  ,      ln.dpp_cust_account_id
  FROM ozf_claim_lines ln
  WHERE ln.claim_id = cv_claim_id;
Line: 2070

  SELECT status_code
        ,amount
	,cust_account_id
	,claim_class
	,payment_method
	,split_from_claim_id
  FROM ozf_claims_all
 WHERE claim_id=cv_claim_id;
Line: 2081

  SAVEPOINT Update_Claim_From_Settlement;
Line: 2127

    UPDATE ozf_claims_all
    SET payment_status = p_payment_status
    ,   status_code = p_status_code
    ,   user_status_id = l_claim_rec.user_status_id
    ,   last_update_date = SYSDATE  -- hbandi added this line of code for resolving the Bug # 9793391 .
    ,   last_updated_by  =  NVL(FND_GLOBAL.user_id,-1)  -- hbandi added this line of code for resolving the Bug # 9793391 .
    WHERE claim_id = p_claim_id;
Line: 2201

                     OZF_Utility_PVT.debug_message('Updated Fund Paid Amount = '||l_fund_rec.paid_amt);
Line: 2205

                  OZF_Funds_PVT.Update_Fund(
                      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_fund_rec          => l_fund_rec
                     ,p_mode              => 'SETTLE' --Bug 13529250, Change p_mode to 'SETTLE' from 'ADJUST' to avoid budget paid amount validation
                  );
Line: 2257

       p_event                     => 'UPDATE',
       x_history_event             => l_history_event,
       x_history_event_description => l_history_event_description,
       x_needed_to_create          => l_needed_to_create,
       x_return_status             => l_return_status
     );
Line: 2331

      l_item_key_update       := l_claim_rec.claim_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
Line: 2332

      l_parameter_list_update := WF_PARAMETER_LIST_T();
Line: 2334

      l_claim_update_data := DBMS_XMLGEN.getXml('select claim.claim_id ClaimID,
                                                  claim.status_code ClaimStatus,
                                                  (claim.cust_account_id || '':'' || cust.party_id)
                                                  ClaimCustAcctID,
                                                  claim.receipt_id ClaimRecID,
                                                  claim.receipt_number ClaimRecNum,
                                                  claim.gl_date ClaimGLDate,
                                                  claim.attribute_category ClaimAttCat,
                                                  claim.attribute1 ClaimAttr1,
                                                  claim.attribute2 ClaimAttr2,
                                                  claim.attribute3 ClaimAttr3,
                                                  claim.attribute4 ClaimAttr4,
                                                  claim.attribute5 ClaimAttr5,
                                                  claim.attribute6 ClaimAttr6,
                                                  claim.attribute7 ClaimAttr7,
                                                  claim.attribute8 ClaimAttr8,
                                                  claim.attribute9 ClaimAttr9,
                                                  claim.attribute10 ClaimAttr10,
                                                  claim.attribute11 ClaimAttr11,
                                                  claim.attribute12 ClaimAttr12,
                                                  claim.attribute13 ClaimAttr13,
                                                  claim.attribute14 ClaimAttr14,
                                                  claim.attribute15 ClaimAttr15,
                                                  claim.deduction_attribute_category CalimDedCat,
                                                  claim.deduction_attribute1 ClaimDedAttr1,
                                                  claim.deduction_attribute2 ClaimDedAttr2,
                                                  claim.deduction_attribute1 ClaimDedAttr3,
                                                  claim.deduction_attribute1 ClaimDedAttr4,
                                                  claim.deduction_attribute1 ClaimDedAttr5,
                                                  claim.deduction_attribute1 ClaimDedAttr6,
                                                  claim.deduction_attribute1 ClaimDedAttr7,
                                                  claim.deduction_attribute1 ClaimDedAttr8,
                                                  claim.deduction_attribute1 ClaimDedAttr9,
                                                  claim.deduction_attribute1 ClaimDedAttr10,
                                                  claim.deduction_attribute1 ClaimDedAttr11,
                                                  claim.deduction_attribute1 ClaimDedAttr12,
                                                  claim.deduction_attribute1 ClaimDedAttr13,
                                                  claim.deduction_attribute1 ClaimDedAttr14,
                                                  claim.deduction_attribute1 ClaimDedAttr15,
						  claim.org_id
                                                  from ozf_claims_all claim, hz_cust_accounts cust
                                                  Where
                                                  claim.cust_account_id = cust.cust_account_id AND
                                                  claim.claim_id = ' || l_claim_rec.claim_id);
Line: 2379

      l_event_name_update :=  'oracle.apps.ozf.bpel.claim';
Line: 2381

      wf_event.raise(p_event_name => l_event_name_update,
                     p_event_key  => l_item_key_update,
                     p_event_data => l_claim_update_data,
                     p_parameters => l_parameter_list_update,
                     p_send_date  => sysdate);
Line: 2403

              , l_line_detail_tbl(l_line_counter).last_update_date
              , l_line_detail_tbl(l_line_counter).last_updated_by
              , l_line_detail_tbl(l_line_counter).creation_date
              , l_line_detail_tbl(l_line_counter).created_by
              , l_line_detail_tbl(l_line_counter).last_update_login
              , l_line_detail_tbl(l_line_counter).request_id
              , l_line_detail_tbl(l_line_counter).program_application_id
              , l_line_detail_tbl(l_line_counter).program_update_date
              , l_line_detail_tbl(l_line_counter).program_id
              , l_line_detail_tbl(l_line_counter).created_from
              , l_line_detail_tbl(l_line_counter).claim_id
              , l_line_detail_tbl(l_line_counter).line_number
              , l_line_detail_tbl(l_line_counter).split_from_claim_line_id
              , l_line_detail_tbl(l_line_counter).amount
              , l_line_detail_tbl(l_line_counter).claim_currency_amount
              , l_line_detail_tbl(l_line_counter).acctd_amount
              , l_line_detail_tbl(l_line_counter).currency_code
              , l_line_detail_tbl(l_line_counter).exchange_rate_type
              , l_line_detail_tbl(l_line_counter).exchange_rate_date
              , l_line_detail_tbl(l_line_counter).exchange_rate
              , l_line_detail_tbl(l_line_counter).set_of_books_id
              , l_line_detail_tbl(l_line_counter).valid_flag
              , l_line_detail_tbl(l_line_counter).source_object_id
              , l_line_detail_tbl(l_line_counter).source_object_class
              , l_line_detail_tbl(l_line_counter).source_object_type_id
              , l_line_detail_tbl(l_line_counter).source_object_line_id
              , l_line_detail_tbl(l_line_counter).plan_id
              , l_line_detail_tbl(l_line_counter).offer_id
              , l_line_detail_tbl(l_line_counter).utilization_id
              , l_line_detail_tbl(l_line_counter).payment_method
              , l_line_detail_tbl(l_line_counter).payment_reference_id
              , l_line_detail_tbl(l_line_counter).payment_reference_number
              , l_line_detail_tbl(l_line_counter).payment_reference_date
              , l_line_detail_tbl(l_line_counter).voucher_id
              , l_line_detail_tbl(l_line_counter).voucher_number
              , l_line_detail_tbl(l_line_counter).payment_status
              , l_line_detail_tbl(l_line_counter).approved_flag
              , l_line_detail_tbl(l_line_counter).approved_date
              , l_line_detail_tbl(l_line_counter).approved_by
              , l_line_detail_tbl(l_line_counter).settled_date
              , l_line_detail_tbl(l_line_counter).settled_by
              , l_line_detail_tbl(l_line_counter).performance_complete_flag
              , l_line_detail_tbl(l_line_counter).performance_attached_flag
              , l_line_detail_tbl(l_line_counter).item_id
              , l_line_detail_tbl(l_line_counter).item_description
              , l_line_detail_tbl(l_line_counter).quantity
              , l_line_detail_tbl(l_line_counter).quantity_uom
              , l_line_detail_tbl(l_line_counter).rate
              , l_line_detail_tbl(l_line_counter).activity_type
              , l_line_detail_tbl(l_line_counter).activity_id
              , l_line_detail_tbl(l_line_counter).related_cust_account_id
              , l_line_detail_tbl(l_line_counter).relationship_type
              , l_line_detail_tbl(l_line_counter).earnings_associated_flag
              , l_line_detail_tbl(l_line_counter).comments
              , l_line_detail_tbl(l_line_counter).tax_code
              , l_line_detail_tbl(l_line_counter).attribute_category
              , l_line_detail_tbl(l_line_counter).attribute1
              , l_line_detail_tbl(l_line_counter).attribute2
              , l_line_detail_tbl(l_line_counter).attribute3
              , l_line_detail_tbl(l_line_counter).attribute4
              , l_line_detail_tbl(l_line_counter).attribute5
              , l_line_detail_tbl(l_line_counter).attribute6
              , l_line_detail_tbl(l_line_counter).attribute7
              , l_line_detail_tbl(l_line_counter).attribute8
              , l_line_detail_tbl(l_line_counter).attribute9
              , l_line_detail_tbl(l_line_counter).attribute10
              , l_line_detail_tbl(l_line_counter).attribute11
              , l_line_detail_tbl(l_line_counter).attribute12
              , l_line_detail_tbl(l_line_counter).attribute13
              , l_line_detail_tbl(l_line_counter).attribute14
              , l_line_detail_tbl(l_line_counter).attribute15
              , l_line_detail_tbl(l_line_counter).org_id
              , l_line_detail_tbl(l_line_counter).sale_date
              , l_line_detail_tbl(l_line_counter).item_type
              , l_line_detail_tbl(l_line_counter).tax_amount
              , l_line_detail_tbl(l_line_counter).claim_curr_tax_amount
              , l_line_detail_tbl(l_line_counter).activity_line_id
              , l_line_detail_tbl(l_line_counter).offer_type
              , l_line_detail_tbl(l_line_counter).prorate_earnings_flag
              , l_line_detail_tbl(l_line_counter).earnings_end_date
              , l_line_detail_tbl(l_line_counter).dpp_cust_account_id;
Line: 2525

    ROLLBACK TO Update_Claim_From_Settlement;
Line: 2534

    ROLLBACK TO Update_Claim_From_Settlement;
Line: 2543

    ROLLBACK TO Update_Claim_From_Settlement;
Line: 2553

END Update_Claim_From_Settlement;
Line: 2633

   Update_Claim_Tax_Amount(
        p_claim_rec         => l_claim_rec
       ,x_return_status     => l_return_status
       ,x_msg_data          => x_msg_data
       ,x_msg_count         => x_msg_count
   );
Line: 2705

   SELECT adj.adjustment_id        --"settlement_id"
   , adj.receivables_trx_id        --"settlement_type_id"
   , adj.adjustment_number         --"settlement_number"
   , adj.apply_date                --"settlement_date"
   , adj.amount                    --"settlement_amount"
   , pay.status                    --"status_code"
   FROM ar_adjustments adj
   , ar_payment_schedules pay
   WHERE adj.payment_schedule_id = pay.payment_schedule_id
   AND adj.adjustment_id = cv_adjust_id;
Line: 2746

   l_claim_line_rec.update_from_tbl_flag := FND_API.g_true;
Line: 2767

    | 2. Update Invoice Dispute Amount if source deduction
    *----------------------------*/
    IF OZF_DEBUG_HIGH_ON THEN
        OZF_Utility_PVT.debug_message(l_full_name||' : Create AR Write Off');
Line: 2788

        | Update Settled Amount|
        *----------------------*/
   l_claim_rec.amount_settled := l_claim_rec.amount_settled + p_difference_amount;
Line: 2793

   Update_Claim_Tax_Amount(
        p_claim_rec         => l_claim_rec
       ,x_return_status     => l_return_status
       ,x_msg_data          => x_msg_data
       ,x_msg_count         => x_msg_count
   );
Line: 2873

 select  sum(nvl(amount_line_items_original, 0))
  ,      sum(nvl(tax_original,0))
  ,      sum(nvl(freight_original,0))
  ,      previous_customer_trx_id
 from ar_payment_schedules ps, ra_customer_trx trx , ( select  distinct customer_trx_id
         from ozf_claim_lines ln ,    ra_customer_trx_lines cm_line
         where cm_line.line_type = 'LINE'
         and   cm_line.interface_line_context = 'ORDER ENTRY' --added filter for 4953844
         and   cm_line.interface_line_attribute6 = to_char(ln.payment_reference_id)
         and ln.claim_id = cv_claim_id) cla
 where ps.customer_trx_id  = trx.customer_trx_id
 and ps.customer_trx_id = cla.customer_trx_id
 group by previous_customer_trx_id;
Line: 2890

  SELECT SUM(claim_currency_amount)
  FROM ozf_claim_lines
  WHERE claim_id = cv_claim_id;
Line: 2988

      Update_Claim_Tax_Amount(
          p_claim_rec         => l_upd_claim_rec
         ,x_return_status     => l_return_status
         ,x_msg_data          => x_msg_data
         ,x_msg_count         => x_msg_count
      );
Line: 3106

      Update_Claim_From_Settlement(
            p_api_version_number    => l_api_version,
            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_id              => p_claim_setl_rec.claim_id,
            p_object_version_number => p_claim_setl_rec.object_version_number,
            p_status_code           => 'CLOSED',
            p_payment_status        => 'PAID'
      );
Line: 3242

  FND_DSQL.add_text('SELECT claim_id, claim_number, object_version_number, claim_class, amount_remaining, amount_settled, source_object_id, payment_method ');
Line: 3320

  SELECT COUNT(claim_line_id)
  FROM ozf_claim_lines
  WHERE claim_id = cv_claim_id;
Line: 3325

  SELECT COUNT(claim_line_id)
  FROM ozf_claim_lines
  WHERE claim_id = cv_claim_id
  AND payment_status = 'PAID';
Line: 3331

   SELECT claim_id
   FROM ozf_claims_all
   WHERE claim_id = cv_claim_id
   FOR UPDATE NOWAIT;
Line: 3374

  SELECT flow_status_code
    FROM oe_order_headers_all,
         ozf_claims_all
   WHERE claim_id = cv_claim_id
     AND payment_reference_id = header_id;
Line: 3487

                    FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
Line: 3557

                        /*Update_Claim_Line_Status(
                                  p_claim_line_id       => l_settlement_doc_tbl(j).claim_line_id
                                 ,x_return_status       => l_return_status
                                 ,x_msg_data            => x_msg_data
                                 ,x_msg_count           => x_msg_count
                        );
Line: 3585

                  UPDATE ozf_claims_all
                  SET payment_status = 'INTERFACED'
                  WHERE claim_id = l_claim_rma_fetch(i).claim_id;
Line: 3592

               UPDATE ozf_claims_all
                  SET payment_status = 'INTERFACED'
                WHERE claim_id = l_claim_rma_fetch(i).claim_id;
Line: 3596

               UPDATE ozf_claim_lines_all
                  SET payment_status = 'PAID'
                WHERE claim_id = l_claim_rma_fetch(i).claim_id;
Line: 3699

                    FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
Line: 3854

  SELECT NVL(SUM(amount_line_items_original), 0)
  ,      NVL(SUM(tax_original), 0)
  ,      NVL(SUM(freight_original), 0)
  FROM ar_payment_schedules
  WHERE customer_trx_id = cv_customer_trx_id;
Line: 3861

  SELECT SUM(claim_currency_amount)
  FROM ozf_claim_lines
  WHERE claim_id = cv_claim_id;
Line: 3866

   SELECT *
   FROM ozf_claims_all
   WHERE claim_id = cv_claim_id
   FOR UPDATE NOWAIT;
Line: 4014

                    FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
Line: 4141

                          Update_Claim_Tax_Amount(
                               p_claim_rec         => l_claim_rec
                              ,x_return_status     => l_return_status
                              ,x_msg_data          => x_msg_data
                              ,x_msg_count         => x_msg_count
                          );
Line: 4366

                       Update_Claim_From_Settlement(
                           p_api_version_number    => l_api_version,
                           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_id              => l_claim_id_ver(i).claim_id,
                           p_object_version_number => l_claim_id_ver(i).object_version_number,
                           p_status_code           => 'CLOSED',
                           p_payment_status        => 'PAID'
                       );
Line: 4514

  SELECT settlement_doc_id
  ,      object_version_number
  ,      settlement_amount
  ,      status_code
  FROM  ozf_settlement_docs
  WHERE settlement_id = cv_settlement_id
  AND   claim_id      = cv_claim_id;
Line: 4550

  SELECT invoice_id,
         cancelled_date
    FROM ap_invoices_all ap,
         ozf_claims_all oc
   WHERE claim_id = cv_claim_id
     --AND invoice_num = oc.payment_reference_number --//Fix for bug 13976621
     AND TO_NUMBER(ap.reference_key1) = oc.claim_id --//Fix for bug 13557215
     AND ap.vendor_id = oc.vendor_id
     AND ap.vendor_site_id = oc.vendor_site_id
     ORDER BY ap.creation_date desc; --//Fix for bug 13976621
Line: 4565

    SELECT invoice_amount
       FROM  ap_invoices
       WHERE invoice_id = cv_invoice_id;
Line: 4574

  SELECT sum(pay.amount + NVL(pay.discount_taken,0))
  FROM   ap_invoice_payments_all pay
  ,      ap_invoices_all inv
  WHERE  inv.invoice_id =  pay.invoice_id
  AND    inv.invoice_id = cv_invoice_id;
Line: 4715

                     DELETE FROM ozf_settlement_docs_all
                        WHERE claim_id = l_claim_id_ver(i).claim_id;
Line: 4815

                                          Update_Settlement_Doc(
                                           p_api_version_number     => 1.0,
                                           p_init_msg_list          => FND_API.g_false,
                                           p_commit                 => p_commit,
                                           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_settlement_doc_rec     => l_settlement_doc_tbl(j),
                                           x_object_version_number  => l_settlement_doc_tbl(j).object_version_number
                                         );
Line: 4832

                                         FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update settlement doc :: settlement_doc_id='||l_settlement_doc_tbl(j).settlement_id);
Line: 4882

                             Update_Claim_Tax_Amount(
                                  p_claim_rec         => l_claim_rec
                                 ,x_return_status     => l_return_status
                                 ,x_msg_data          => x_msg_data
                                 ,x_msg_count         => x_msg_count
                             );
Line: 4937

                     Update_Claim_From_Settlement(
                                p_api_version_number    => l_api_version_number,
                                 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_id             => l_claim_rec.claim_id,
                                 p_object_version_number => l_claim_rec.object_version_number,
                                 p_status_code           => 'CLOSED',
                                 p_payment_status        => 'PAID'
                             );
Line: 5097

SELECT MEANING
FROM OZF_LOOKUPS
WHERE lookup_type= 'OZF_CLAIM_CLASS'
AND lookup_code = p_lkup_code;
Line: 5103

SELECT MEANING
FROM OZF_LOOKUPS
WHERE lookup_type= 'OZF_PAYMENT_METHOD'
AND lookup_code = p_lkup_code;
Line: 5109

SELECT SUBSTRB(PARTY.PARTY_NAME,1,50) NAME
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES PARTY
WHERE CA.party_id = party.party_id
AND CA.CUST_ACCOUNT_ID = p_cust_act_id;
Line: 5115

SELECT NAME
FROM OZF_CLAIM_TYPES_VL
WHERE CLAIM_TYPE_ID = p_claim_type_id;
Line: 5120

SELECT NAME
FROM OZF_REASON_CODES_VL
WHERE REASON_CODE_ID = p_reason_code_id;
Line: 5126

    SELECT ou.organization_id   org_id
    FROM hr_operating_units ou
    WHERE mo_global.check_access(ou.organization_id) = 'Y';
Line: 5371

l_updated_by             NUMBER;
Line: 5372

l_last_update_login      NUMBER;
Line: 5379

  SELECT ozf_settlement_docs_all_s.NEXTVAL
  FROM DUAL;
Line: 5383

  SELECT  COUNT(settlement_doc_id)
  FROM  ozf_settlement_docs_all
  WHERE settlement_doc_id = cv_id;
Line: 5388

  SELECT set_of_books_id,
         currency_code,
         exchange_rate_date,
         exchange_rate_type,
         exchange_rate,
         org_id
  FROM ozf_claims_all
  WHERE claim_id = cv_claim_id;
Line: 5401

SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'INVOICE TYPE';
Line: 5407

SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'PAYMENT METHOD';
Line: 5436

  l_updated_by := NVL(FND_GLOBAL.user_id,-1);
Line: 5437

  l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
Line: 5525

    OZF_SETTLEMENT_DOCS_PKG.Insert_Row(
          px_settlement_doc_id      => l_settlement_doc_rec.settlement_doc_id,
          px_object_version_number  => l_settlement_doc_rec.object_version_number,
          p_last_update_date        => SYSDATE,
          p_last_updated_by         => FND_GLOBAL.USER_ID,
          p_creation_date           => SYSDATE,
          p_created_by              => FND_GLOBAL.USER_ID,
          p_last_update_login       => FND_GLOBAL.CONC_LOGIN_ID,
          p_request_id              => FND_GLOBAL.CONC_REQUEST_ID,
          p_program_application_id  => FND_GLOBAL.PROG_APPL_ID,
          p_program_update_date     => SYSDATE,
          p_program_id              => FND_GLOBAL.CONC_PROGRAM_ID,
          p_created_from            => l_settlement_doc_rec.created_from,
          p_claim_id                => l_settlement_doc_rec.claim_id,
          p_claim_line_id           => l_settlement_doc_rec.claim_line_id,
          p_payment_method          => l_settlement_doc_rec.payment_method,
          p_settlement_id           => l_settlement_doc_rec.settlement_id,
          p_settlement_type         => l_settlement_doc_rec.settlement_type,
          p_settlement_type_id      => l_settlement_doc_rec.settlement_type_id,
          p_settlement_number       => l_settlement_doc_rec.settlement_number,
          p_settlement_date         => l_settlement_doc_rec.settlement_date,
          p_settlement_amount       => l_settlement_doc_rec.settlement_amount,
          p_settlement_acctd_amount => l_settlement_doc_rec.settlement_acctd_amount,
          p_status_code             => l_settlement_doc_rec.status_code,
          p_attribute_category      => l_settlement_doc_rec.attribute_category,
          p_attribute1              => l_settlement_doc_rec.attribute1,
          p_attribute2              => l_settlement_doc_rec.attribute2,
          p_attribute3              => l_settlement_doc_rec.attribute3,
          p_attribute4              => l_settlement_doc_rec.attribute4,
          p_attribute5              => l_settlement_doc_rec.attribute5,
          p_attribute6              => l_settlement_doc_rec.attribute6,
          p_attribute7              => l_settlement_doc_rec.attribute7,
          p_attribute8              => l_settlement_doc_rec.attribute8,
          p_attribute9              => l_settlement_doc_rec.attribute9,
          p_attribute10             => l_settlement_doc_rec.attribute10,
          p_attribute11             => l_settlement_doc_rec.attribute11,
          p_attribute12             => l_settlement_doc_rec.attribute12,
          p_attribute13             => l_settlement_doc_rec.attribute13,
          p_attribute14             => l_settlement_doc_rec.attribute14,
          p_attribute15             => l_settlement_doc_rec.attribute15,
          px_org_id                 => l_claim_rec.org_id,
          p_payment_reference_id    => l_settlement_doc_rec.payment_reference_id,
          p_payment_reference_number => l_settlement_doc_rec.payment_reference_number,
          p_payment_status           => l_settlement_doc_rec.payment_status,
          p_group_claim_id           => l_settlement_doc_rec.group_claim_id,
          p_gl_date                  => TRUNC(l_settlement_doc_rec.gl_date),
          p_wo_rec_trx_id            => l_settlement_doc_rec.wo_rec_trx_id
    );
Line: 5643

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

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

    p_settlement_doc_rec     IN   settlement_doc_rec_type,
    x_object_version_number  OUT NOCOPY  NUMBER
)
IS
l_api_version   CONSTANT NUMBER       := 1.0;
Line: 5658

l_api_name      CONSTANT VARCHAR2(30) := 'Update_Settlement_Doc';
Line: 5665

l_last_updated_by        NUMBER;
Line: 5666

l_last_update_login      NUMBER;
Line: 5670

  SELECT object_version_number
  FROM ozf_settlement_docs_all
  WHERE settlement_doc_id = cv_settle_doc_id;
Line: 5675

  SELECT set_of_books_id,
         currency_code,
         exchange_rate_date,
         exchange_rate_type,
         exchange_rate,
         org_id
  FROM ozf_claims_all
  WHERE claim_id = cv_claim_id;
Line: 5688

SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'INVOICE TYPE';
Line: 5694

SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_code = cv_trx_type
AND lookup_type = 'PAYMENT METHOD';
Line: 5703

  SAVEPOINT Update_Settlement_Doc;
Line: 5725

  l_last_updated_by := NVL(FND_GLOBAL.user_id,-1);
Line: 5726

  l_last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
Line: 5764

         p_validation_mode      => JTF_PLSQL_API.g_update,
         x_return_status        => l_return_status
    );
Line: 5831

    OZF_SETTLEMENT_DOCS_PKG.Update_Row(
          p_settlement_doc_id      => l_complete_doc_rec.settlement_doc_id,
          p_object_version_number  => l_object_version_number,
          p_last_update_date       => SYSDATE,
          p_last_updated_by        => FND_GLOBAL.USER_ID,
          p_last_update_login      => FND_GLOBAL.CONC_LOGIN_ID,
          p_request_id             => l_complete_doc_rec.request_id,
          p_program_application_id => l_complete_doc_rec.program_application_id,
          p_program_update_date    => l_complete_doc_rec.program_update_date,
          p_program_id             => l_complete_doc_rec.program_id,
          p_created_from           => l_complete_doc_rec.created_from,
          p_claim_id               => l_complete_doc_rec.claim_id,
          p_claim_line_id          => l_complete_doc_rec.claim_line_id,
          p_payment_method         => l_complete_doc_rec.payment_method,
          p_settlement_id          => l_complete_doc_rec.settlement_id,
          p_settlement_type        => l_complete_doc_rec.settlement_type,
          p_settlement_type_id     => l_complete_doc_rec.settlement_type_id,
          p_settlement_number      => l_complete_doc_rec.settlement_number,
          p_settlement_date        => l_complete_doc_rec.settlement_date,
          p_settlement_amount      => l_complete_doc_rec.settlement_amount,
          p_settlement_acctd_amount=> l_complete_doc_rec.settlement_acctd_amount,
          p_status_code            => l_complete_doc_rec.status_code,
          p_attribute_category     => l_complete_doc_rec.attribute_category,
          p_attribute1             => l_complete_doc_rec.attribute1,
          p_attribute2             => l_complete_doc_rec.attribute2,
          p_attribute3             => l_complete_doc_rec.attribute3,
          p_attribute4             => l_complete_doc_rec.attribute4,
          p_attribute5             => l_complete_doc_rec.attribute5,
          p_attribute6             => l_complete_doc_rec.attribute6,
          p_attribute7             => l_complete_doc_rec.attribute7,
          p_attribute8             => l_complete_doc_rec.attribute8,
          p_attribute9             => l_complete_doc_rec.attribute9,
          p_attribute10            => l_complete_doc_rec.attribute10,
          p_attribute11            => l_complete_doc_rec.attribute11,
          p_attribute12            => l_complete_doc_rec.attribute12,
          p_attribute13            => l_complete_doc_rec.attribute13,
          p_attribute14            => l_complete_doc_rec.attribute14,
          p_attribute15            => l_complete_doc_rec.attribute15,
          p_org_id                 => l_claim_rec.org_id,
          p_payment_reference_id    => l_complete_doc_rec.payment_reference_id,
          p_payment_reference_number => l_complete_doc_rec.payment_reference_number,
          p_payment_status           => l_complete_doc_rec.payment_status,
          p_group_claim_id           => l_complete_doc_rec.group_claim_id,
          p_gl_date                  => TRUNC(l_complete_doc_rec.gl_date),
          p_wo_rec_trx_id            => l_complete_doc_rec.wo_rec_trx_id
    );
Line: 5888

  UPDATE ozf_settlement_docs_all SET
       object_version_number     = l_object_version_number,
       last_update_date          = SYSDATE,
       last_updated_by           = l_last_updated_by,
       last_update_login         = l_last_update_login,
       request_id                = FND_GLOBAL.CONC_REQUEST_ID,
       program_application_id    = FND_GLOBAL.PROG_APPL_ID,
       program_update_date       = SYSDATE,
       program_id                = FND_GLOBAL.CONC_PROGRAM_ID,
       created_from              = l_complete_doc_rec.created_from,
       claim_id                  = l_complete_doc_rec.claim_id,
       claim_line_id             = l_complete_doc_rec.claim_line_id,
       payment_method            = l_complete_doc_rec.payment_method,
       settlement_id             = l_complete_doc_rec.settlement_id,
       settlement_type           = l_complete_doc_rec.settlement_type,
       settlement_type_id        = l_complete_doc_rec.settlement_type_id,
       settlement_number         = l_complete_doc_rec.settlement_number,
       settlement_date           = l_complete_doc_rec.settlement_date,
       settlement_amount         = l_complete_doc_rec.settlement_amount,
       status_code               = l_complete_doc_rec.status_code,
       attribute_category        = l_complete_doc_rec.attribute_category,
       attribute1                = l_complete_doc_rec.attribute1,
       attribute2                = l_complete_doc_rec.attribute2,
       attribute3                = l_complete_doc_rec.attribute3,
       attribute4                = l_complete_doc_rec.attribute4,
       attribute5                = l_complete_doc_rec.attribute5,
       attribute6                = l_complete_doc_rec.attribute6,
       attribute7                = l_complete_doc_rec.attribute7,
       attribute8                = l_complete_doc_rec.attribute8,
       attribute9                = l_complete_doc_rec.attribute9,
       attribute10               = l_complete_doc_rec.attribute10,
       attribute11               = l_complete_doc_rec.attribute11,
       attribute12               = l_complete_doc_rec.attribute12,
       attribute13               = l_complete_doc_rec.attribute13,
       attribute14               = l_complete_doc_rec.attribute14,
       attribute15               = l_complete_doc_rec.attribute15,
       org_id                    = l_complete_doc_rec.org_id
  WHERE settlement_doc_id = p_settlement_doc_rec.settlement_doc_id
  AND object_version_number = p_settlement_doc_rec.object_version_number;
Line: 5949

    ROLLBACK TO Update_Settlement_Doc;
Line: 5958

    ROLLBACK TO Update_Settlement_Doc;
Line: 5967

    ROLLBACK TO Update_Settlement_Doc;
Line: 5978

End Update_Settlement_Doc;
Line: 5989

PROCEDURE Delete_Settlement_Doc(
    p_api_version_number      IN   NUMBER,
    p_init_msg_list           IN   VARCHAR2,
    p_commit                  IN   VARCHAR2,
    p_validation_level        IN   NUMBER,
    x_return_status           OUT NOCOPY  VARCHAR2,
    x_msg_count               OUT NOCOPY  NUMBER,
    x_msg_data                OUT NOCOPY  VARCHAR2,
    p_settlement_doc_id       IN  NUMBER,
    p_object_version_number   IN   NUMBER
)
IS
l_api_version CONSTANT NUMBER       := 1.0;
Line: 6002

l_api_name    CONSTANT VARCHAR2(30) := 'Delete_Settlement_Doc';
Line: 6008

  SELECT object_version_number
  FROM ozf_settlement_docs_all
  WHERE settlement_doc_id = cv_settle_doc_id;
Line: 6014

  SAVEPOINT Delete_Settlement_Doc;
Line: 6042

     OZF_Utility_PVT.debug_message(l_full_name ||': delete');
Line: 6047

      OZF_SETTLEMENT_DOCS_PKG.Delete_Row(
          p_settlement_doc_id  => p_settlement_doc_id
      );
Line: 6067

  DELETE FROM ozf_settlement_docs_all
    WHERE settlement_doc_id = p_settlement_doc_id
    AND   object_version_number = p_object_version_number;
Line: 6096

    ROLLBACK TO Delete_Settlement_Doc;
Line: 6105

    ROLLBACK TO Delete_Settlement_Doc;
Line: 6114

    ROLLBACK TO Delete_Settlement_Doc;
Line: 6124

End Delete_Settlement_Doc;
Line: 6155

   SELECT settlement_doc_id
   FROM ozf_settlement_docs_all
   WHERE settlement_doc_id = p_settlement_doc_id
   AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;
Line: 6325

    IF p_settlement_doc_rec.last_update_date = FND_API.g_miss_date OR
       p_settlement_doc_rec.last_update_date IS NULL THEN
      x_return_status := FND_API.g_ret_sts_error;
Line: 6331

    IF p_settlement_doc_rec.last_updated_by = FND_API.g_miss_num OR
       p_settlement_doc_rec.last_updated_by IS NULL THEN
      x_return_status := FND_API.g_ret_sts_error;
Line: 6365

    IF p_settlement_doc_rec.last_update_date IS NULL THEN
      x_return_status := FND_API.g_ret_sts_error;
Line: 6370

    IF p_settlement_doc_rec.last_updated_by IS NULL THEN
      x_return_status := FND_API.g_ret_sts_error;
Line: 6509

  SELECT *
  FROM ozf_settlement_docs_all
  WHERE settlement_doc_id = p_settlement_doc_rec.settlement_doc_id;
Line: 6539

  IF p_settlement_doc_rec.last_update_date = FND_API.g_miss_date THEN
    x_complete_rec.last_update_date := NULL;
Line: 6542

  IF p_settlement_doc_rec.last_update_date IS NULL THEN
    x_complete_rec.last_update_date := l_settlement_doc_rec.last_update_date;
Line: 6547

  IF p_settlement_doc_rec.last_updated_by = FND_API.g_miss_num THEN
    x_complete_rec.last_updated_by := NULL;
Line: 6550

  IF p_settlement_doc_rec.last_updated_by IS NULL THEN
    x_complete_rec.last_updated_by := l_settlement_doc_rec.last_updated_by;
Line: 6571

  IF p_settlement_doc_rec.last_update_login = FND_API.g_miss_num THEN
    x_complete_rec.last_update_login := NULL;
Line: 6574

  IF p_settlement_doc_rec.last_update_login IS NULL THEN
    x_complete_rec.last_update_login := l_settlement_doc_rec.last_update_login;
Line: 6595

  IF p_settlement_doc_rec.program_update_date = FND_API.g_miss_date THEN
    x_complete_rec.program_update_date := NULL;
Line: 6598

  IF p_settlement_doc_rec.program_update_date IS NULL THEN
    x_complete_rec.program_update_date := l_settlement_doc_rec.program_update_date;
Line: 7149

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

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

    p_settlement_doc_tbl     IN   settlement_doc_tbl_type
)
IS
l_api_name          CONSTANT VARCHAR2(30) := 'Update_Settlement_Doc_Tbl';
Line: 7173

   SAVEPOINT  Update_Settlement_Doc_Tbl;
Line: 7202

    OZF_SETTLEMENT_DOC_PVT.Update_Settlement_Doc(
        p_api_version_number     => l_api_version,
        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              => l_msg_count,
        x_msg_data               => l_msg_data,

        p_settlement_doc_rec     => l_settlement_doc,
        x_object_version_number  => l_object_version_number
        );
Line: 7236

        ROLLBACK TO  Update_Settlement_Doc_Tbl;
Line: 7245

        ROLLBACK TO  Update_Settlement_Doc_Tbl;
Line: 7254

        ROLLBACK TO  Update_Settlement_Doc_Tbl;
Line: 7262

END Update_Settlement_Doc_Tbl;