eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_CLAIM_AGING_PVT SQL Statements

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

Line: 7


PROCEDURE insert_aging_dates(
   p_bucket_id           IN  NUMBER,
   p_bucket_line_id      IN  NUMBER,
   p_bucket_sequence     IN  NUMBER,
   p_bucket_type         IN  VARCHAR2,
   p_bucket_date         IN  DATE,
   p_condition_type      IN  VARCHAR2,
   x_return_status       OUT NOCOPY VARCHAR2
)
IS
BEGIN
   x_return_status := FND_API.g_ret_sts_success;

Line: 20


   INSERT INTO ozf_aging_bucket_dates (
      aging_bucket_id,
      aging_bucket_line_id,
      bucket_sequence_num,
      bucket_type,
      bucket_date,
      condition_type
   ) VALUES (
      p_bucket_id,
      p_bucket_line_id,
      p_bucket_sequence,
      p_bucket_type,
      p_bucket_date,
      p_condition_type
   );

Line: 92


         insert_aging_dates(
            p_bucket_id       => p_bucket_id,
            p_bucket_line_id  => p_bucket_line_id,
            p_bucket_sequence => p_bucket_sequence,
            p_bucket_type     => p_bucket_type,
            p_bucket_date     => l_bucket_date,
            p_condition_type  => l_type,
            x_return_status   => l_return_status
         );

Line: 117


         insert_aging_dates(
            p_bucket_id       => p_bucket_id,
            p_bucket_line_id  => p_bucket_line_id,
            p_bucket_sequence => p_bucket_sequence,
            p_bucket_type     => p_bucket_type,
            p_bucket_date     => l_bucket_date,
            p_condition_type  => l_type,
            x_return_status   => l_return_status
         );

Line: 143


         insert_aging_dates(
            p_bucket_id       => p_bucket_id,
            p_bucket_line_id  => p_bucket_line_id,
            p_bucket_sequence => p_bucket_sequence,
            p_bucket_type     => p_bucket_type,
            p_bucket_date     => l_bucket_date,
            p_condition_type  => l_type,
            x_return_status   => l_return_status
         );

Line: 185


SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.claim_date, 'DD') = TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'EQ'
AND    b.bucket_type = 'CURRENT'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.claim_date, 'DD') <= TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'LT'
AND    b.bucket_type = 'CURRENT'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.claim_date, 'DD') >= TRUNC(b.bucket_date , 'DD')
AND    b.condition_type = 'GT'
AND    b.bucket_type = 'CURRENT'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') = TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'EQ'
AND    b.bucket_type = 'PAST'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') <= TRUNC(b.bucket_date , 'DD')
AND    b.condition_type = 'LT'
AND    b.bucket_type = 'PAST'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') >= TRUNC(b.bucket_date , 'DD')
AND    b.condition_type = 'GT'
AND    b.bucket_type = 'PAST'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') = TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'EQ'
AND    b.bucket_type = 'FUTURE'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') <= TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'LT'
AND    b.bucket_type = 'FUTURE'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id
UNION ALL
SELECT c.cust_account_id cust_account_id
,      b.aging_bucket_id aging_bucket_id
,      b.aging_bucket_line_id aging_bucket_line_id
,      b.bucket_sequence_num bucket_sequence_num
,      c.org_id
,      SUM(c.acctd_amount) amount
FROM   ozf_claims c
,      ozf_aging_bucket_dates b
WHERE  TRUNC(c.due_date, 'DD') >= TRUNC(b.bucket_date, 'DD')
AND    b.condition_type = 'GT'
AND    b.bucket_type = 'FUTURE'
--AND    c.status_code IN ('NEW', 'OPEN', 'PENDING_APPROVAL', 'COMPLETE', 'APPROVED')
AND    c.status_code IN ('NEW', 'OPEN', 'COMPLETE', 'REJECTED')
GROUP BY c.cust_account_id
,      b.aging_bucket_id
,      b.aging_bucket_line_id
,      b.bucket_sequence_num
,      c.org_id;

Line: 357


SELECT aging_bucket_id aging_bucket_id
,      aging_bucket_line_id aging_bucket_line_id
,      bucket_sequence_num bucket_sequence_num
,      org_id
,      SUM(amount) amount
FROM   ozf_aging_summary_all
GROUP BY aging_bucket_id
,        aging_bucket_line_id
,        bucket_sequence_num
,        org_id;

Line: 380


           INSERT INTO ozf_aging_summary_all (
                 cust_account_id,
                 aging_bucket_id,
                 aging_bucket_line_id,
                 bucket_sequence_num,
                 amount,
                 org_id
           ) VALUES (
                 l_aging_summary_rec.cust_account_id,
                 l_aging_summary_rec.aging_bucket_id,
                 l_aging_summary_rec.aging_bucket_line_id,
                 l_aging_summary_rec.bucket_sequence_num,
                 l_aging_summary_rec.amount,
                 l_aging_summary_rec.org_id
           );

Line: 404


         INSERT INTO ozf_aging_summary_all (
               cust_account_id,
               aging_bucket_id,
               aging_bucket_line_id,
               bucket_sequence_num,
               amount,
               org_id
         ) VALUES (
               -1,
               l_aging_col_total_rec.aging_bucket_id,
               l_aging_col_total_rec.aging_bucket_line_id,
               l_aging_col_total_rec.bucket_sequence_num,
               l_aging_col_total_rec.amount,
               l_aging_col_total_rec.org_id
         );

Line: 459


SELECT bucket_name
FROM ozf_x_aging_buckets
WHERE aging_bucket_id = p_id;

Line: 464


SELECT aging_bucket_id
,      aging_bucket_line_id
,      days_start
,      days_to
,      type
,      report_heading1
,      report_heading2
FROM   ozf_x_aging_bucket_lns
WHERE  aging_bucket_id = p_id
ORDER BY bucket_sequence_num;

Line: 528


    DELETE FROM ozf_aging_summary_all WHERE org_id = l_org_id;

Line: 537


   DELETE FROM ozf_aging_bucket_dates;

Line: 607


   FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total number of Bucket lines selected: ' || to_char(l_bucket_lines_tbl.count));