Oracle - API to get AP invoice validation status via SQL Query

The AP invoice validation status is determinated in AP invoice Forms. There is only one API to get invoice approval status, but no API available to get the validation status.

We developped the following function to return the AP invoice validation status.

SQL:

FUNCTION get_invoice_validation_status(l_invoice_id IN NUMBER) RETURN VARCHAR2
IS
l_status VARCHAR2(50);
l_validation_status VARCHAR2(50);
l_inv_sel_count NUMBER;
l_step VARCHAR2(10);


l_NLS_TRANSLATION varchar2(15) := 'NLS TRANSLATION';
l_QUICKPRO varchar2(8) := 'QUICKPRO';
l_POSTING_STATUS varchar2(14) := 'POSTING STATUS';
l_P varchar2(1) := 'P';
l_S varchar2(1) := 'S';
l_ANY_MULTIPLE varchar2(12) := 'ANY MULTIPLE';
l_UNMATCHED varchar2(9) := 'UNMATCHED';
l_SYSTEM varchar2(6) := 'SYSTEM';
l_APPROVED varchar2(8) := 'APPROVED';
l_CANCELLED varchar2(9) := 'CANCELLED';
l_NEEDS_REAPPROVAL varchar2(16) := 'NEEDS REAPPROVAL';
l_NEVER_APPROVED varchar2(14) := 'NEVER APPROVED';
l_INVOICE_DISTRIBUTION_TYPE varchar2(25) := 'INVOICE DISTRIBUTION TYPE';
l_ITEM varchar2(4) := 'ITEM';
l_INVOICE_TYPE varchar2(12) := 'INVOICE TYPE';
l_STANDARD varchar2(8) := 'STANDARD';
l_MISC_PHRASES varchar2(12) := 'MISC PHRASES';
l_B varchar2(1) := 'B';
l_SELECTED_FOR_PAYMENT varchar2(20) := 'SELECTED FOR PAYMENT';


l_YES_NO_ALL varchar2(10) := 'YES_NO_ALL';
l_Y varchar2(1) := 'Y';
l_N varchar2(1) := 'N';

l_NLS_REPORT_PARAMETER varchar2(20) := 'NLS REPORT PARAMETER';
l_ALL varchar2(3) := 'ALL';
l_PREPAY_STATUS varchar2(13) := 'PREPAY STATUS';
l_AVAILABLE varchar2(9) := 'AVAILABLE';
l_FULL varchar2(4) := 'FULL';
l_UNAPPROVED varchar2(10) := 'UNAPPROVED';
l_UNPAID varchar2(6) := 'UNPAID';
l_PERMANENT varchar2(9) := 'PERMANENT';


l_AP_WFAPPROVAL_STATUS varchar2(20) := 'AP_WFAPPROVAL_STATUS';
l_NOT_REQUIRED varchar2(12) := 'NOT REQUIRED';
l_REQUIRED varchar2(8) := 'REQUIRED';
l_INITIATED varchar2(9) := 'INITIATED';
l_WFAPPROVED varchar2(10) := 'WFAPPROVED';
l_REJECTED varchar2(8) := 'REJECTED';
l_MANUALLY_APPROVED varchar2(17) := 'MANUALLY APPROVED';
l_NEEDS_WFREAPPROVAL varchar2(18) := 'NEEDS WFREAPPROVAL';

l_dummy fnd_lookup_values.meaning%TYPE;
l_nls_partial fnd_lookup_values.meaning%TYPE;
l_nls_selected fnd_lookup_values.meaning%TYPE;
l_nls_approved fnd_lookup_values.meaning%TYPE;
l_nls_cancelled fnd_lookup_values.meaning%TYPE;
l_nls_needs_reapproval fnd_lookup_values.meaning%TYPE;
l_nls_never_approved fnd_lookup_values.meaning%TYPE;
l_nls_selected_for_payment fnd_lookup_values.meaning%TYPE;
l_nls_all fnd_lookup_values.meaning%TYPE;
l_nls_available fnd_lookup_values.meaning%TYPE;
l_nls_full fnd_lookup_values.meaning%TYPE;
l_nls_unapproved fnd_lookup_values.meaning%TYPE;
l_nls_unpaid fnd_lookup_values.meaning%TYPE;
l_nls_permanent fnd_lookup_values.meaning%TYPE;
l_nls_not_required fnd_lookup_values.meaning%TYPE;
l_nls_required fnd_lookup_values.meaning%TYPE;
l_nls_initiated fnd_lookup_values.meaning%TYPE;
l_nls_wfapproved fnd_lookup_values.meaning%TYPE;
l_nls_rejected fnd_lookup_values.meaning%TYPE;
l_nls_manually_approved fnd_lookup_values.meaning%TYPE;
l_nls_wf_needs_reapproval fnd_lookup_values.meaning%TYPE;

CURSOR c_invoices is
SELECT i.invoice_id
,i.invoice_amount
,i.payment_status_flag
,i.invoice_type_lookup_code, i.invoice_num
FROM ap_invoices_all i
WHERE invoice_id = l_invoice_id;

BEGIN
l_step := '1';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field,
l13.displayed_field,
l8.displayed_field,
l9.displayed_field,
l10.displayed_field,
l11.displayed_field,
l12.displayed_field,
l14.displayed_field
INTO l_dummy, -- l_quickpro_prefix,
l_nls_partial,
l_nls_selected,
l_dummy, -- l_nls_multiple,
l_dummy, -- l_nls_unmatched,
l_dummy, -- l_nls_system,
l_nls_approved,
l_nls_cancelled,
l_nls_needs_reapproval,
l_nls_never_approved,
l_dummy, -- l_nls_item,
l_dummy, -- l_nls_standard,
l_dummy, -- l_nls_not_applicable,
l_nls_selected_for_payment
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7,
ap_lookup_codes l8,
ap_lookup_codes l9,
ap_lookup_codes l10,
ap_lookup_codes l11,
ap_lookup_codes l12,
ap_lookup_codes l13,
ap_lookup_codes l14
WHERE l1.lookup_type = l_NLS_TRANSLATION
AND l1.lookup_code = l_QUICKPRO
AND l2.lookup_type = l_POSTING_STATUS
AND l2.lookup_code = l_P
AND l3.lookup_type = l_POSTING_STATUS
AND l3.lookup_code = l_S
AND l4.lookup_type = l_NLS_TRANSLATION
AND l4.lookup_code = l_ANY_MULTIPLE
AND l5.lookup_type = l_NLS_TRANSLATION
AND l5.lookup_code = l_UNMATCHED
AND l6.lookup_type = l_NLS_TRANSLATION
AND l6.lookup_code = l_SYSTEM
AND l7.lookup_type = l_NLS_TRANSLATION
AND l7.lookup_code = l_APPROVED
AND l13.lookup_type = l_NLS_TRANSLATION
AND l13.lookup_code = l_CANCELLED
AND l8.lookup_type = l_NLS_TRANSLATION
AND l8.lookup_code = l_NEEDS_REAPPROVAL
AND l9.lookup_type = l_NLS_TRANSLATION
AND l9.lookup_code = l_NEVER_APPROVED
AND l10.lookup_type = l_INVOICE_DISTRIBUTION_TYPE
AND l10.lookup_code = l_ITEM
AND l11.lookup_type = l_INVOICE_TYPE
AND l11.lookup_code = l_STANDARD
AND l12.lookup_type = l_MISC_PHRASES
AND l12.lookup_code = l_B
AND l14.lookup_type = l_NLS_TRANSLATION
AND l14.lookup_code = l_SELECTED_FOR_PAYMENT;
l_step := '2';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field
INTO l_nls_all,
l_nls_available,
l_nls_full,
l_nls_unapproved,
l_nls_unpaid,
l_nls_permanent
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6
WHERE l1.lookup_type = l_NLS_REPORT_PARAMETER
AND l1.lookup_code = l_ALL
AND l2.lookup_type = l_PREPAY_STATUS
AND l2.lookup_code = l_AVAILABLE
AND l3.lookup_type = l_PREPAY_STATUS
AND l3.lookup_code = l_FULL
AND l4.lookup_type = l_PREPAY_STATUS
AND l4.lookup_code = l_UNAPPROVED
AND l5.lookup_type = l_PREPAY_STATUS
AND l5.lookup_code = l_UNPAID
AND l6.lookup_type = l_PREPAY_STATUS
AND l6.lookup_code = l_PERMANENT;

l_step := '3';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field
INTO l_nls_not_required,
l_nls_required,
l_nls_initiated,
l_nls_wfapproved,
l_nls_rejected,
l_nls_manually_approved,
l_nls_wf_needs_reapproval
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7
WHERE l1.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l1.lookup_code = l_NOT_REQUIRED
AND l2.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l2.lookup_code = l_REQUIRED
AND l3.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l3.lookup_code = l_INITIATED
AND l4.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l4.lookup_code = l_WFAPPROVED
AND l5.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l5.lookup_code = l_REJECTED
AND l6.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l6.lookup_code = l_MANUALLY_APPROVED
AND l7.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l7.lookup_code = l_NEEDS_WFREAPPROVAL;


FOR r in c_invoices loop
l_step := '4';
l_status := AP_INVOICES_PKG.GET_APPROVAL_STATUS(r.INVOICE_ID
,r.INVOICE_AMOUNT
,r.PAYMENT_STATUS_FLAG
,r.INVOICE_TYPE_LOOKUP_CODE );
l_step := '5';
--dbms_output.put_line(r.invoice_num || ' - ' || l_status);

if (l_status =
'NEVER APPROVED') then
l_validation_status :=
l_nls_never_approved;
elsif (l_status = 'APPROVED') then
l_validation_status := l_nls_approved;
elsif (l_status = 'CANCELLED') then
l_validation_status := l_nls_cancelled;
elsif (l_status =
'NEEDS REAPPROVAL') then
l_validation_status :=
l_nls_needs_reapproval;
elsif (l_status = 'AVAILABLE') then
l_validation_status := l_nls_available;
elsif (l_status = 'FULL') then
l_validation_status := l_nls_full;
elsif (l_status = 'UNAPPROVED') then
l_validation_status := l_nls_unapproved;
elsif (l_status = 'UNPAID') then
l_validation_status := l_nls_unpaid;
elsif (l_status = 'PERMANENT') then
l_validation_status := l_nls_permanent;
elsif (l_status = 'NOT REQUIRED') then
l_validation_status := l_nls_not_required;
elsif (l_status = 'REQUIRED') then
l_validation_status := l_nls_required;
elsif (l_status = 'INITIATED') then
l_validation_status := l_nls_initiated;
elsif (l_status = 'WFAPPROVED') then
l_validation_status := l_nls_wfapproved;
elsif (l_status = 'REJECTED') then
l_validation_status := l_nls_rejected;
elsif (l_status = 'MANUALLY APPROVED') then
l_validation_status := l_nls_manually_approved;
elsif (l_status = 'NEEDS WFREAPPROVAL') then
l_validation_status := l_nls_wf_needs_reapproval;
end if;

-- Added for Payment Process Enhancements.
if (l_status
IN ('APPROVED','UNPAID')) then
SELECT count(*)
INTO l_inv_sel_count
FROM ap_payment_schedules
WHERE invoice_id = r.invoice_id
AND checkrun_id IS NOT NULL
AND rownum =1; --bug5691219

if (l_inv_sel_count > 0) then
l_validation_status :=
l_nls_selected_for_payment;
end if;
end if;

--dbms_output.put_line(r.invoice_num || ' - ' || l_validation_status);

END LOOP;
RETURN(l_validation_status);

EXCEPTION
WHEN OTHERS THEN
RETURN ('l_step = ' || l_step || ' ' || SQLERRM);
END get_invoice_validation_status;

This function should be used in Oracle application environnement, it will not work in TOAD or SQL Developper even if after contexualization...