The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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
);
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
);
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
);
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
);
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;
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;
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
);
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
);
SELECT bucket_name
FROM ozf_x_aging_buckets
WHERE aging_bucket_id = p_id;
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;
DELETE FROM ozf_aging_summary_all;
DELETE FROM ozf_aging_bucket_dates;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Total number of Bucket lines selected: ' || to_char(l_bucket_lines_tbl.count));