Introduction: We have a Receipt with a “Claim Investigation” where the Claim # was not populated. The below script will help to resolve that issue.
w do we solve:
DECLARE
l_read_only_mode VARCHAR2 (1) := ‘&read_only_mode’ || ”;
l_bal_due_remaining NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_receipt_id NUMBER := &cash_receipt_id;
l_org_id NUMBER := &org_id;
CURSOR get_claim
IS
SELECT receivable_application_id, application_type, amount_applied,
apply_date
FROM ar_receivable_applications_all ra
WHERE ra.application_ref_type = ‘CLAIM’
AND ra.display = ‘Y’
AND ra.cash_receipt_id =
DECODE (l_receipt_id,
0, ra.cash_receipt_id,
l_receipt_id
)
AND ra.status = ‘OTHER ACC’
AND ( ra.application_ref_num IS NOT NULL
AND EXISTS (
SELECT 1
FROM ozf_claims_all clm
WHERE clm.claim_number = ra.application_ref_num
AND ra.secondary_application_ref_id = clm.claim_id
AND clm.status_code IN (‘CANCELED’, ‘CLOSED’))
)
ORDER BY ra.cash_receipt_id, ra.gl_date;
PROCEDURE DEBUG (s VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (s);
END DEBUG;
FUNCTION print_spaces (n IN NUMBER)
RETURN VARCHAR2
IS
l_return_string VARCHAR2 (100);
BEGIN
SELECT SUBSTR (‘ ‘,
1,
n
)
INTO l_return_string
FROM DUAL;
RETURN (l_return_string);
END print_spaces;
BEGIN
BEGIN
mo_global.init (‘AR’);
mo_global.set_policy_context (‘S’, l_org_id);
END;
FOR rec IN get_claim
LOOP
IF NVL (UPPER (l_read_only_mode), ‘Y’) = ‘N’
THEN
BEGIN
arp_process_application.REVERSE
(p_ra_id => rec.receivable_application_id,
p_reversal_gl_date => SYSDATE,
p_reversal_date => SYSDATE,
p_module_name => ‘RAPI’,
p_module_version => 1.0,
p_bal_due_remaining => l_bal_due_remaining
);
l_count := 0;
IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line (‘l_msg_data ‘ || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
IF NVL (l_count, 0) < l_msg_count
THEN
l_count := NVL (l_count, 0) + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
DBMS_OUTPUT.put_line ( ‘l_msg_data’
|| l_count
|| ‘ : ‘
|| l_msg_data
);
ELSE
EXIT;
END IF;
END LOOP;
END IF;
END;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DEBUG (‘Exception : ‘);
ROLLBACK;
RAISE;
END;
/
Conclusion: This code will fix that error and show the values in DBMS Output
