Introduction
This Post illustrates the steps required to AR Aging 4 Bucket with project details in fusion application
Script to AR Aging 4 Bucket with project details in fusion
WITH FUNCTION get_outstanding_amount (
p_payment_schedule_id IN NUMBER,
p_as_of_date IN DATE,
p_invoice_type IN VARCHAR2,
p_amt_due_remaining IN NUMBER,
p_amount_applied IN NUMBER,
p_amount_adjusted IN NUMBER,
p_amount_credited IN NUMBER,
p_amount_in_dispute IN NUMBER,
p_amount_adjusted_pending IN NUMBER,
p_convert_flag IN VARCHAR2,
p_due_date IN DATE,
p_return_position IN NUMBER
)
RETURN NUMBER
IS
l_inv_type CHAR (4);
l_amount_applied_late NUMBER (24, 2) := 0;
l_adjustment_amount NUMBER (24, 2);
l_amt_due_remaining NUMBER (24, 2);
l_percent_remaining NUMBER (24, 2);
l_payment_meaning ar_lookups.meaning%TYPE;
l_risk_meaning ar_lookups.meaning%TYPE;
l_outstanding_inv NUMBER (14, 2);
l_custinv_b0 NUMBER;
l_custinv_b1 NUMBER;
l_custinv_b2 NUMBER;
l_custinv_b3 NUMBER;
l_bucket_category VARCHAR2 (10) := NULL;
l_bucket_days_from_0 NUMBER;
l_bucket_days_to_0 NUMBER;
l_bucket_line_type_0 VARCHAR2 (200);
l_bucket_days_from_1 NUMBER;
l_bucket_days_to_1 NUMBER;
l_bucket_line_type_1 VARCHAR2 (200);
l_bucket_days_from_2 NUMBER;
l_bucket_days_to_2 NUMBER;
l_bucket_line_type_2 VARCHAR2 (200);
l_bucket_days_from_3 NUMBER;
l_bucket_days_to_3 NUMBER;
l_bucket_line_type_3 VARCHAR2 (200);
l_b0_inv NUMBER;
l_b1_inv NUMBER;
l_b2_inv NUMBER;
l_b3_inv NUMBER;
BEGIN
l_adjustment_amount := 0;
l_amt_due_remaining := 0;
l_outstanding_inv := 0;
l_custinv_b0 := 0;
l_custinv_b1 := 0;
l_custinv_b2 := 0;
l_custinv_b3 := 0;
—
l_outstanding_inv := NVL (p_amt_due_remaining, 0);
— Get l_payment_meaning
SELECT meaning
INTO l_payment_meaning
FROM ar_lookups
WHERE lookup_type = ‘INV/CM/ADJ’ AND lookup_code = ‘PMT’;
— Get l_risk_meaning
SELECT meaning
INTO l_risk_meaning
FROM ar_lookups
WHERE lookup_type = ‘MISC_PHRASES’ AND lookup_code = ‘RISK’;
— Get bucket_line_type_0, bucket_days_start_0, bucket_days_to_0
SELECT lines.days_start, lines.days_to, lines.TYPE
INTO l_bucket_days_from_0, l_bucket_days_to_0, l_bucket_line_type_0
FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
WHERE lines.aging_bucket_id = buckets.aging_bucket_id
AND NVL (buckets.status, ‘A’) = ‘A’
AND buckets.aging_type = ‘4BUCKET’
AND buckets.bucket_name = ‘Standard’
AND lines.bucket_sequence_num = 0;
— Get bucket_line_type_1, bucket_days_start_1, bucket_days_to_1
SELECT lines.days_start, lines.days_to, lines.TYPE
INTO l_bucket_days_from_1, l_bucket_days_to_1, l_bucket_line_type_1
FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
WHERE lines.aging_bucket_id = buckets.aging_bucket_id
AND NVL (buckets.status, ‘A’) = ‘A’
AND buckets.aging_type = ‘4BUCKET’
AND buckets.bucket_name = ‘Standard’
AND lines.bucket_sequence_num = 1;
— Get bucket_line_type_2, bucket_days_start_2, bucket_days_to_2
SELECT lines.days_start, lines.days_to, lines.TYPE
INTO l_bucket_days_from_2, l_bucket_days_to_2, l_bucket_line_type_2
FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
WHERE lines.aging_bucket_id = buckets.aging_bucket_id
AND NVL (buckets.status, ‘A’) = ‘A’
AND buckets.aging_type = ‘4BUCKET’
AND buckets.bucket_name = ‘Standard’
AND lines.bucket_sequence_num = 2;
— Get bucket_line_type_3, bucket_days_start_3, bucket_days_to_3
SELECT lines.days_start, lines.days_to, lines.TYPE
INTO l_bucket_days_from_3, l_bucket_days_to_3, l_bucket_line_type_3
FROM ar_aging_bucket_lines_b lines, ar_aging_buckets buckets
WHERE lines.aging_bucket_id = buckets.aging_bucket_id
AND NVL (buckets.status, ‘A’) = ‘A’
AND buckets.aging_type = ‘4BUCKET’
AND buckets.bucket_name = ‘Standard’
AND lines.bucket_sequence_num = 3;
IF (p_invoice_type NOT IN (l_payment_meaning, l_risk_meaning))
THEN
IF (p_amount_applied IS NOT NULL)
THEN
SELECT NVL
(SUM
( DECODE
(p_convert_flag,
‘Y’, ( DECODE
(ps.CLASS,
‘CM’, DECODE
(ra.application_type,
‘CM’, ra.acctd_amount_applied_from,
ra.acctd_amount_applied_to
),
ra.acctd_amount_applied_to
)
+ NVL (ra.acctd_earned_discount_taken,
0
)
+ NVL
(ra.acctd_unearned_discount_taken,
0
)
),
( ra.amount_applied
+ NVL (ra.earned_discount_taken, 0)
+ NVL (ra.unearned_discount_taken, 0)
)
)
* DECODE (ps.CLASS,
‘CM’, DECODE (ra.application_type,
‘CM’, -1,
1
),
1
)
),
0
)
INTO l_amount_applied_late
FROM ar_receivable_applications_all ra,
ar_payment_schedules_all ps
WHERE ( ra.applied_payment_schedule_id = p_payment_schedule_id
OR ra.payment_schedule_id = p_payment_schedule_id
)
AND ra.status || ” = ‘APP’
AND NVL (ra.confirmed_flag, ‘Y’) = ‘Y’
AND ra.gl_date + 0 > TO_DATE (p_as_of_date, ‘DD-MON-YY’)
AND ps.payment_schedule_id = p_payment_schedule_id
— AND ra.receivable_application_id+0 < :rp_app_max_id
;
END IF;
IF (p_amount_applied IS NULL)
THEN
IF (p_amount_credited IS NOT NULL)
THEN
SELECT NVL
(SUM
( DECODE
(p_convert_flag,
‘Y’, ( DECODE
(ps.CLASS,
‘CM’, DECODE
(ra.application_type,
‘CM’, ra.acctd_amount_applied_from,
ra.acctd_amount_applied_to
),
ra.acctd_amount_applied_to
)
+ NVL
(ra.acctd_earned_discount_taken,
0
)
+ NVL
(ra.acctd_unearned_discount_taken,
0
)
),
( ra.amount_applied
+ NVL (ra.earned_discount_taken, 0)
+ NVL (ra.unearned_discount_taken, 0)
)
)
* DECODE (ps.CLASS,
‘CM’, DECODE (ra.application_type,
‘CM’, -1,
1
),
1
)
),
0
)
INTO l_amount_applied_late
FROM ar_receivable_applications_all ra,
ar_payment_schedules_all ps
WHERE ( ra.applied_payment_schedule_id =
p_payment_schedule_id
OR ra.payment_schedule_id = p_payment_schedule_id
)
AND ra.status || ” = ‘APP’
AND NVL (ra.confirmed_flag, ‘Y’) = ‘Y’
AND ra.gl_date + 0 > p_as_of_date
AND ps.payment_schedule_id = p_payment_schedule_id
— AND ra.receivable_application_id+0 < :rp_app_max_id
;
END IF;
END IF;
IF p_amount_adjusted IS NOT NULL
THEN
SELECT NVL (SUM (DECODE (p_convert_flag,
‘Y’, acctd_amount,
amount
)
),
0
)
INTO l_adjustment_amount
FROM ar_adjustments_all
WHERE gl_date > p_as_of_date
AND payment_schedule_id = p_payment_schedule_id
AND status = ‘A’;
— AND adjustment_id < :rp_adj_max_id;
l_amt_due_remaining :=
l_amt_due_remaining – NVL (l_adjustment_amount, 0);
END IF;
l_outstanding_inv := l_outstanding_inv + l_amt_due_remaining;
END IF;
— Get l_b0_inv
SELECT DECODE (l_bucket_line_type_0,
‘DISPUTE_ONLY’, DECODE (NVL (p_amount_in_dispute, 0),
0, 0,
1
),
‘PENDADJ_ONLY’, DECODE (NVL (p_amount_adjusted_pending,
0),
0, 0,
1
),
‘DISPUTE_PENDADJ’, DECODE
(NVL (p_amount_in_dispute, 0),
0, DECODE
(NVL (p_amount_adjusted_pending,
0
),
0, 0,
1
),
1
),
DECODE (GREATEST (l_bucket_days_from_0,
CEIL (p_as_of_date – p_due_date)
),
LEAST (l_bucket_days_to_0,
CEIL (p_as_of_date – p_due_date)
), 1,
0
)
* DECODE (NVL (p_amount_in_dispute, 0),
0, 1,
DECODE (l_bucket_category,
‘DISPUTE_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
* DECODE (NVL (p_amount_adjusted_pending, 0),
0, 1,
DECODE (l_bucket_category,
‘PENDADJ_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
)
INTO l_b0_inv
FROM DUAL;
— Get l_b1_inv
SELECT DECODE (l_bucket_line_type_1,
‘DISPUTE_ONLY’, DECODE (NVL (p_amount_in_dispute, 0),
0, 0,
1
),
‘PENDADJ_ONLY’, DECODE (NVL (p_amount_adjusted_pending,
0),
0, 0,
1
),
‘DISPUTE_PENDADJ’, DECODE
(NVL (p_amount_in_dispute, 0),
0, DECODE
(NVL (p_amount_adjusted_pending,
0
),
0, 0,
1
),
1
),
DECODE (GREATEST (l_bucket_days_from_1,
CEIL (p_as_of_date – p_due_date)
),
LEAST (l_bucket_days_to_1,
CEIL (p_as_of_date – p_due_date)
), 1,
0
)
* DECODE (NVL (p_amount_in_dispute, 0),
0, 1,
DECODE (l_bucket_category,
‘DISPUTE_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
* DECODE (NVL (p_amount_adjusted_pending, 0),
0, 1,
DECODE (l_bucket_category,
‘PENDADJ_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
)
INTO l_b1_inv
FROM DUAL;
— Get l_b2_inv
SELECT DECODE (l_bucket_line_type_2,
‘DISPUTE_ONLY’, DECODE (NVL (p_amount_in_dispute, 0),
0, 0,
1
),
‘PENDADJ_ONLY’, DECODE (NVL (p_amount_adjusted_pending,
0),
0, 0,
1
),
‘DISPUTE_PENDADJ’, DECODE
(NVL (p_amount_in_dispute, 0),
0, DECODE
(NVL (p_amount_adjusted_pending,
0
),
0, 0,
1
),
1
),
DECODE (GREATEST (l_bucket_days_from_2,
CEIL (p_as_of_date – p_due_date)
),
LEAST (l_bucket_days_to_2,
CEIL (p_as_of_date – p_due_date)
), 1,
0
)
* DECODE (NVL (p_amount_in_dispute, 0),
0, 1,
DECODE (l_bucket_category,
‘DISPUTE_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
* DECODE (NVL (p_amount_adjusted_pending, 0),
0, 1,
DECODE (l_bucket_category,
‘PENDADJ_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
)
INTO l_b2_inv
FROM DUAL;
— Get l_b3_inv
SELECT DECODE (l_bucket_line_type_3,
‘DISPUTE_ONLY’, DECODE (NVL (p_amount_in_dispute, 0),
0, 0,
1
),
‘PENDADJ_ONLY’, DECODE (NVL (p_amount_adjusted_pending,
0),
0, 0,
1
),
‘DISPUTE_PENDADJ’, DECODE
(NVL (p_amount_in_dispute, 0),
0, DECODE
(NVL (p_amount_adjusted_pending,
0
),
0, 0,
1
),
1
),
DECODE (GREATEST (l_bucket_days_from_3,
CEIL (p_as_of_date – p_due_date)
),
LEAST (l_bucket_days_to_3,
CEIL (p_as_of_date – p_due_date)
), 1,
0
)
* DECODE (NVL (p_amount_in_dispute, 0),
0, 1,
DECODE (l_bucket_category,
‘DISPUTE_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
* DECODE (NVL (p_amount_adjusted_pending, 0),
0, 1,
DECODE (l_bucket_category,
‘PENDADJ_ONLY’, 0,
‘DISPUTE_PENDADJ’, 0,
1
)
)
)
INTO l_b3_inv
FROM DUAL;
—
IF l_b0_inv != 0
THEN
l_custinv_b0 := l_outstanding_inv;
END IF;
IF l_b1_inv != 0
THEN
l_custinv_b1 := l_outstanding_inv;
END IF;
IF l_b2_inv != 0
THEN
l_custinv_b2 := l_outstanding_inv;
END IF;
IF l_b3_inv != 0
THEN
l_custinv_b3 := l_outstanding_inv;
END IF;
— Return value based on passed in position
IF p_return_position = 1
THEN
RETURN l_outstanding_inv;
ELSIF p_return_position = 2
THEN
RETURN l_custinv_b0;
ELSIF p_return_position = 3
THEN
RETURN l_custinv_b1;
ELSIF p_return_position = 4
THEN
RETURN l_custinv_b2;
ELSIF p_return_position = 5
THEN
RETURN l_custinv_b3;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
SELECT inv_tab.solution_name,
inv_tab.invoice_currency_code, inv_tab.function_currency_code,customer_name,invoice_date,
SUM (inv_tab.amt_due_original) amount_due_original,
SUM(get_outstanding_amount(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘N’,
inv_tab.due_date,
1
) ) Outstanding_amt_Inv_Currency
,SUM(get_outstanding_amount(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.acctd_amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘Y’,
inv_tab.due_date,
1)) outstand_amt_country_currency
,SUM(get_outstanding_amount
(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.acctd_amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘Y’,
inv_tab.due_date,
2
) ) Current_outstanding
,SUM(get_outstanding_amount
(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.acctd_amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘Y’,
inv_tab.due_date,
3
)) outstanding_1_30_days
,SUM(get_outstanding_amount
(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.acctd_amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘Y’,
inv_tab.due_date,
4
)) outstanding_31_60_days,
SUM(get_outstanding_amount
(inv_tab.payment_schedule_id,
:P_ASOF_DATE,
inv_tab.invoice_type,
inv_tab.acctd_amt_due_remaining,
inv_tab.amount_applied,
inv_tab.amount_adjusted,
inv_tab.amount_credited,
inv_tab.amount_in_dispute,
inv_tab.amount_adjusted_pending,
‘Y’,
inv_tab.due_date,
5
)) outstanding_60_plus_days
FROM (SELECT org.NAME solution_name,
org.NAME original_solution_name,
(SELECT DISTINCT org.NAME
FROM pjf_project_parties proj_dir,
pjf_proj_role_types_tl prt,
per_all_assignments_f paf,
hr_all_organization_units org
WHERE proj_dir.project_id = proj.project_id
AND proj_dir.project_role_id =
prt.project_role_id
AND prt.project_role_name =
‘Project Director’
–‘1000’– Project Director role type
AND proj_dir.resource_source_id =
paf.person_id
AND paf.organization_id =
org.organization_id
AND paf.assignment_status_type = ‘ACTIVE’
AND TRUNC (SYSDATE)
BETWEEN paf.effective_start_date
AND NVL
(paf.effective_end_date,
SYSDATE + 1
)
AND TRUNC (SYSDATE)
BETWEEN proj_dir.start_date_active
AND NVL
(proj_dir.end_date_active,
SYSDATE + 1
))
director_solution_name,
(SELECT DISTINCT org.NAME
FROM pjf_project_parties proj_dir,
pjf_proj_role_types_tl prt,
per_all_assignments_f paf,
hr_all_organization_units org
WHERE proj_dir.project_id = proj.project_id
AND proj_dir.project_role_id =
prt.project_role_id
AND prt.project_role_name =
‘Project Manager’
–‘1000’– Project Director role type
AND proj_dir.resource_source_id =
paf.person_id
AND paf.organization_id =
org.organization_id
AND paf.assignment_status_type = ‘ACTIVE’
AND TRUNC (SYSDATE)
BETWEEN paf.effective_start_date
AND NVL
(paf.effective_end_date,
SYSDATE + 1
)
AND TRUNC (SYSDATE)
BETWEEN proj_dir.start_date_active
AND NVL
(proj_dir.end_date_active,
SYSDATE + 1
)) manager_solution_name,
hca.cust_account_id customer_id,
RTRIM (RPAD (hp.party_name, 36)) customer_name,
RTRIM (RPAD (hca.account_number, 8)) customer_number,
ps.payment_schedule_id payment_schedule_id,
ps.trx_number invoice_number, ps.CLASS invoice_class,
rctt.NAME invoice_type, ps.due_date due_date,
ps.trx_date invoice_date, rtt.NAME term_name,
ps.invoice_currency_code invoice_currency_code,
gll.currency_code function_currency_code,
ps.acctd_amount_due_remaining acctd_amt_due_remaining,
ps.amount_due_remaining amt_due_remaining,
ROUND
((ps.amount_due_original * NVL (ps.exchange_rate, 1)
),
fc.PRECISION
) amt_due_functional_currency,
ps.amount_due_original amt_due_original,
ps.amount_adjusted amount_adjusted,
ps.amount_applied amount_applied,
ps.amount_credited amount_credited,
ps.amount_in_dispute amount_in_dispute,
ps.amount_adjusted_pending amount_adjusted_pending,
NVL (ps.exchange_rate, 1) exchange_rate,
ps.gl_date gl_date, ps.gl_date_closed gl_date_closed
,n1.text comments
, proj.project_id project_id,
projt.NAME project_name, proj.segment1 project_number,
ps.cash_receipt_id cash_receipt_id, rct.org_id org_id,
rct.customer_trx_id
FROM ra_customer_trx_all rct,
hr_all_organization_units org,
pjf_projects_all_b proj,
pjf_projects_all_tl projt,
ar_payment_schedules_all ps,
hz_cust_accounts hca,
hz_parties hp,
ra_cust_trx_types_all rctt,
gl_ledgers gll,
ar_notes n1,
fnd_currencies fc,
ra_terms_tl rtt
WHERE org.organization_id = proj.carrying_out_organization_id
AND proj.segment1 = rct.interface_header_attribute1
AND proj.org_id = rct.org_id
AND projt.project_id = proj.project_id
AND rct.customer_trx_id = ps.customer_trx_id
AND rct.org_id = ps.org_id
AND ps.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND ps.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
and rct.customer_trx_id=n1.customer_trx_id(+)
AND rct.set_of_books_id = gll.ledger_id
AND ( n1.note_id IS NULL
OR n1.note_id =
(SELECT MAX (note_id)
FROM ar_notes n2
WHERE n2.customer_trx_id = n1.customer_trx_id)
)
AND gll.currency_code = fc.currency_code
AND rct.term_id = rtt.term_id
) inv_tab
WHERE 1=1
AND (inv_tab.invoice_date) <= :P_ASOF_DATE
AND (inv_tab.gl_date_closed) >:p_asof_date
GROUP BY inv_tab.solution_name,
inv_tab.invoice_currency_code,
inv_tab.function_currency_code,
customer_name,invoice_date
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
