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
begin
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;
exception when others then
l_amount_applied_late:=0;
end ;
END IF;
IF (p_amount_applied IS NULL)
THEN
IF (p_amount_credited IS NOT NULL)
THEN
begin
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
;
exception when others then
l_amount_applied_late :=0;
end;
END IF;
END IF;
l_amt_due_remaining := l_amount_applied_late;
IF p_amount_adjusted IS NOT NULL
THEN
begin
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';
exception when others then
l_adjustment_amount :=0;
end;
-- 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;
FUNCTION get_invoice_outstanding_amount (
p_project_id IN NUMBER,
--p_invoice_number IN VARCHAR2,
p_as_of_date IN DATE,
p_convert_flag IN VARCHAR2,
p_return_position IN NUMBER
)
RETURN NUMBER
IS
CURSOR c_outstanding_amount
IS
SELECT *
FROM (SELECT 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,
gsob.currency_code function_currency_code,
ps.acctd_amount_due_remaining acctd_amt_due_remaining,
ps.amount_line_items_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, proj.NAME project_name,
proj.segment1 project_number,
ps.cash_receipt_id cash_receipt_id, rct.org_id org_id,
rct.customer_trx_id
FROM hr_all_organization_units org,
pjf_projects_all_vl proj,
ra_customer_trx_all rct,
ar_payment_schedules_all ps,
ra_cust_trx_types_all rctt,
ra_terms_tl rtt,
ar_notes n1,
gl_sets_of_books gsob,
fnd_currencies fc
WHERE org.organization_id = proj.carrying_out_organization_id
AND proj.segment1 = TRIM (LEADING '0' FROM rct.interface_header_attribute1)
AND rct.interface_header_context = 'PROJECTS INVOICES'
AND proj.org_id = rct.org_id
AND rct.interface_header_attribute1 IS NOT NULL
AND rct.customer_trx_id = ps.customer_trx_id
AND rct.org_id = ps.org_id
AND ps.CUST_TRX_TYPE_SEQ_ID = rctt.CUST_TRX_TYPE_SEQ_ID
AND rct.term_id = rtt.term_id
AND rct.customer_trx_id = n1.customer_trx_id(+)
AND rct.set_of_books_id = gsob.set_of_books_id
AND gsob.currency_code = fc.currency_code) a
WHERE a.project_id = p_project_id
AND a.gl_date <= p_as_of_date;
o_amount NUMBER := 0;
BEGIN
FOR i in c_outstanding_amount
LOOP
o_amount := o_amount + get_outstanding_amount(i.payment_schedule_id,
p_as_of_date,
i.invoice_type,
i.amount_line_items_remaining,
i.amount_applied,
i.amount_adjusted,
i.amount_credited,
i.amount_in_dispute,
i.amount_adjusted_pending,
p_convert_flag,
i.due_date,
p_return_position
);
END LOOP;
RETURN o_amount;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
SELECT PPA.segment1 project_number
,OCTV.name contracty_type
,PPA.name project_name
,PPA.description
,HOU.name organization_name
,PPA.project_currency_code
,OKH.contract_number
,OKL.line_number
,ROUND((NVL(get_invoice_outstanding_amount(PPA.project_id
,:P_AS_OF_DATE
,'Y'
,1), 0)), 2) ar_open
,PBC.billing_cycle_name
,(SELECT PPM.resource_source_name FROM pjf_proj_all_members_v PPM
WHERE PPM.project_id = PPA.project_id
AND PPM.project_role_id = 1
AND :P_AS_OF_DATE BETWEEN PPM.start_date_active AND NVL(PPM.end_date_active, :P_AS_OF_DATE + 1)) project_manager
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code = 'A'
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV
,pjb_bill_plans_vl PBP
,pjf_billing_cycles_vl PBC
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = 'C' -- Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = 'C' -- Current
AND OKH.version_type = 'C' -- Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> 'DRAFT'
AND PPT.project_type NOT IN ('INTERCOMPANY', 'Intercompany')
AND INV.contract_id (+) = OKH.id
and INV.contract_line_id (+) = OKL.id
AND OKL.bill_plan_id = PBP.bill_plan_id (+)
AND OKL.major_version = PBP.major_version (+)
AND PBP.billing_cycle_id = PBC.billing_cycle_id (+)
