2010年12月17日星期五

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...

Oracle AP Payment API

There is no public Oracle API for the AP payments. We have developed a custom interface by using the standard procedure : ap_checks_pkg.insert_row.

Suppose that we have the following informations at the beginning :

siaxp2.org_id,
siaxp2.invoice_currency_code,
siaxp2.invoice_amount,
siaxp.reference_id,
siaxp.currency_code,
DECODE(siaxp2.invoice_currency_code,'XOF',siaxp2.invoice_amount,siaxp.payment_amount) payment_amount,
siaxp.base_amount,
siaxp.payment_date,
siaxp.transaction_code,
siaxp.internal_bank_account,
siaxp.vendor_id,
siaxp.vendor_site_id

-- Calculate bank account

SELECT abaa2.bank_account_id,
abaa2.currency_code
INTO n_internal_bank_account_id,
n_currency_code
FROM ap_bank_accounts_all abaa,
ap_bank_accounts_all abaa2
WHERE abaa.attribute1 = r_payment_xrt.internal_bank_account
AND abaa.org_id = abaa2.org_id
AND abaa.bank_account_name = abaa2.bank_account_name
AND abaa.bank_branch_id = abaa2.bank_branch_id
AND abaa2.org_id = r_payment_xrt.org_id;

-- Calculate payment document

SELECT ACS.LAST_DOCUMENT_NUM+1,
acs.check_format_id,
acs.check_stock_id,
apaa.bank_account_num,
apaa.bank_account_name,
apaa.account_type,
acs.doc_category_code,
fdsa.doc_sequence_id
INTO n_check_number,
n_check_format_id,
n_check_stock_id,
n_bank_account_num,
n_bank_account_name,
n_account_type,
v_doc_category_code,
n_doc_sequence_id
FROM ap_check_stocks_all acs,
ap_check_formats acf,
ap_bank_accounts_all apaa,
fnd_doc_sequence_assignments fdsa,
ap_bank_branches abb
WHERE abb.bank_branch_id = apaa.bank_branch_id
AND apaa.bank_account_id = n_internal_bank_account_id
AND apaa.set_of_books_id = r_societe.set_of_books_id
AND apaa.bank_account_id = acs.bank_account_id
AND NVL(acs.inactive_date, SYSDATE+1) > SYSDATE
AND acs.check_format_id = acf.check_format_id
AND acf.payment_method_lookup_code = 'EFT'
AND acs.doc_category_code = fdsa.category_code
AND acs.name = DECODE(r_payment_xrt.currency_code,n_currency_code,'VIREMENT','VIRDEV')
AND fdsa.method_code= 'A'
AND fdsa.set_of_books_id = r_societe.set_of_books_id
AND ROWNUM = 1;

-- Calculate supplier info

SELECT pv.vendor_name,
pvsa.vendor_site_code,
pv.vendor_id,
pvsa.vendor_site_id,
pvsa.address_line1,
pvsa.address_line2,
pvsa.address_line3,
pvsa.city,
pvsa.country
INTO v_vendor_name,
v_vendor_site_code,
n_vendor_id,
n_vendor_site_id,
v_address_line1,
v_address_line2,
v_address_line3,
v_city,
v_country
FROM po_vendor_sites_all pvsa,
po_vendors pv
WHERE pvsa.vendor_site_id = r_payment_xrt.vendor_site_id
AND pv.vendor_id = pvsa.vendor_id;

-- Check id

SELECT ap_checks_s.NEXTVAL
INTO n_check_id
FROM dual;

-- Sequence

SELECT db_sequence_name
INTO v_sequence
FROM fnd_document_sequences
WHERE doc_sequence_id = n_doc_sequence_id;

-- Doc sequence value

v_proc_stmt := 'select ' || v_sequence || '.nextval into :next_val from sys.dual';
EXECUTE IMMEDIATE v_proc_stmt INTO n_doc_sequence_value;

-- Final Insertion

ap_checks_pkg.insert_row (
x_rowid => v_rowid,
x_amount => r_payment_xrt.payment_amount,
x_bank_account_id => n_internal_bank_account_id,
x_bank_account_name => n_bank_account_name,
x_check_date => r_payment_xrt.payment_date,
x_check_id => n_check_id,
x_check_number => n_check_number,
x_currency_code => r_payment_xrt.invoice_currency_code,
x_last_updated_by => l_user_id,
x_last_update_date => SYSDATE,
x_payment_method_lookup_code => 'EFT',
x_payment_type_flag => 'Q',
x_address_line1 => v_address_line1,
x_address_line2 => v_address_line2,
x_address_line3 => v_address_line3,
x_checkrun_name => 'Règlement express : ID='||TO_CHAR(n_check_id),
x_check_format_id => n_check_format_id,
x_check_stock_id => n_check_stock_id,
x_city => v_city,
x_country => v_country,
x_created_by => l_user_id,
x_creation_date => SYSDATE,
x_last_update_login => l_user_id,
x_status_lookup_code => 'NEGOTIABLE',
x_vendor_name => v_vendor_name,
x_vendor_site_code => v_vendor_site_code,
x_external_bank_account_id => n_external_bank_account_id,
x_bank_account_num => n_external_bank_account_num,
x_doc_category_code => v_doc_category_code,
x_doc_sequence_id => n_doc_sequence_id,
x_doc_sequence_value => n_doc_sequence_value,
x_org_id => n_org_id,
x_vendor_id => n_vendor_id,
x_vendor_site_id => n_vendor_site_id,
x_exchange_rate => n_exchange_rate, --r_payment_xrt.base_amount/r_payment_xrt.payment_amount,
x_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
x_exchange_rate_type => n_exchange_rate_type, --'User',
x_base_amount => n_base_amount, -- r_payment_xrt.base_amount, 1.3
x_calling_sequence => v_sequence
);

ap_pay_invoice_pkg.ap_pay_invoice (
p_invoice_id => r.invoice_id,
p_check_id => n_check_id,
p_payment_num => r.payment_num,
p_invoice_payment_id => n_invoice_payment_id,
p_period_name => n_period_name,
p_accounting_date => r_payment_xrt.payment_date,
p_amount => r.amount_remaining,
p_discount_taken => 0,
p_invoice_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- 1.3 n_invoice_base_amount, -- A confirmer
p_payment_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- A confirmer
p_accrual_posted_flag => 'N',
p_cash_posted_flag => 'N',
p_posted_flag => 'N',
p_set_of_books_id => n_sob_id,
p_last_updated_by => l_user_id,
p_last_update_login => l_user_id,
p_currency_code => r_payment_xrt.invoice_currency_code,
p_exchange_rate => n_exchange_rate,
p_exchange_rate_type => n_exchange_rate_type, --'User',
p_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
p_replace_flag => 'N',
p_bank_account_id => n_external_bank_account_id,
p_bank_account_num => n_external_bank_account_num,
p_payment_mode => 'PAY'
);

Oracle PO API to mass close PO

The following procedure has to be executed in Oracle environnement, but not in TOAD or SQL Developper with contexualization.

PROCEDURE Mass_close_PO(
PR_po_header_id IN NUMBER,
PR_po_line_id IN NUMBER,
PR_po_shipment_id IN NUMBER,
PR_action IN VARCHAR2,
PR_reason IN VARCHAR2,
PR_result OUT BOOLEAN,
PR_return_code OUT VARCHAR2
) IS
-- =====================================================================
-- P R O C E D U R E Mass_close_PO
-- =====================================================================
-- Description : Mass close or finally close PO
-- =====================================================================
-- I N P U T
-- =====================================================================
-- Nom Description
-- PR_po_header_id po_header_id
-- PR_po_line_id po_line_id
-- PR_po_shipment_id po_shipment_id
-- PR_action po_action
-- PR_reason po_closed_reason
-- =====================================================================
-- O U T P U T
-- =====================================================================
-- Nom Description
-- PR_result TRUE or FALSE
-- PR_return_code Error message
-- =====================================================================


BEGIN
g_step := 'Mass_close_PO - PR_po_header_id = '|| PR_po_header_id || ' PR_po_line_id = ' || PR_po_line_id || ' PR_po_shipment_id = ' || PR_po_shipment_id;
fnd_file.put_line(fnd_file.log, to_char(SYSDATE,'DD-MM-RRRR HH24:MI:SS : ') || g_step);

PR_result := PO_ACTIONS.CLOSE_PO(
P_DOCID => PR_po_header_id,
P_DOCTYP => 'PO',
P_DOCSUBTYP => 'STANDARD',
P_LINEID => PR_po_line_id,
P_SHIPID => PR_po_shipment_id,
P_ACTION => PR_action,
P_REASON => PR_reason,
P_CALLING_MODE => 'PO',
P_CONC_FLAG => 'N',
P_RETURN_CODE => PR_return_code,
P_AUTO_CLOSE => 'N' ,
P_ACTION_DATE => SYSDATE ,
P_ORIGIN_DOC_ID => NULL
);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END Mass_close_PO;


Create a cursor to select eligible shipment lines in po_line_locations_all. Specify the action (CLOSE or FINALLY CLOSE) and the closed reason, call the below procedure to mass close PO.

The standard logic of this API will be the following : after closing the shipment line, the program will control the related po line, if all shipment line are closed or finally closed, api will close this po line. Item for the po header, if all related po lines are closed or finally closed, api will close the po header.