EBS-R12-检测发票状态

如题 ，有啥米问题，欢迎至QQ：285979593讨论。

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;

end CUX_PAYREQ_INVOICE_PKG;

When I tried to find the AP invoice status from backend, I found the column name APPROVAL_STATUS_LOOKUP_CODE under the view name AP_INVOICE_V. Since it is org based view, setting the context is mandatory. Please find my script to set the context here.

Below query will give you the status,

SELECT invoice_num

      ,approval_status_lookup_code

FROM   ap_invoices_v

WHERE  invoice_num =  'DM080310' ;

When I further traced to understand the source of the view column, I found that t here is no column in the AP_INVOICES_ALL table that stores the validation status. A n API named AP_INVOICES_PKG.GET_APPROVAL_STATUS is used by the view to finding the status.

Below query will give you the usage of the mentioned api,

SELECT  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS

            (

             I.INVOICE_ID

            ,I.INVOICE_AMOUNT

            ,I.PAYMENT_STATUS_FLAG

            ,I.INVOICE_TYPE_LOOKUP_CODE

            ) Approval_Status

      ,invoice_num

FROM    AP_INVOICES I

WHERE   invoice_num =  'DM080310' ;

    

When I went one more step deeper, I got the below,

Invoice distributions are validated individually and the status is stored at the invoice distribution level.  This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.

Valid values for the column are:

A  - Validated (it used to be called Approved)

N  or NULL - Never validated

T  - Tested but not validated

The invoice header form derives the invoice validation status based on the following:

'Validated'

-           If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'

'Never Validated'

-           If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'

'Needs Revalidation'

-           If there are any rows in AP_HOLDS that do not have a release code.

-           If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.

-           If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).

I hope this information helps you.