Since Nov 2008(patch ID 262093.1), Oracle provides the possibility to create local Approved Supplier List (ASL) through the PO Open Interface via the concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement".
With this concurrent program, we can create the ASLs from the blankets already imported into Oracle PO. It's useful in the case of we use blanket agreements. If we don't use blanket but only use quotation, it's impossible for us to mass create ASLs.
So we duplicated the standard concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement" and created another new specific concurrent program "XX : Generate Sourcing Rules and ASLs from Quotations".
The executable file is always the same : POXSRASL.sql
This program requires 12 parameters that 5 need to be modified :
Parameter 1 - Supplier
LOV 1 - PO_ASL_VENDOR
Parameter 2 - Blanket Agreement Number
LOV 2 - SE_PO_ASL_BLANKET_NUMBER
Define LOV -
Table : PO_HEADERS_ALL POH,PO_LOOKUP_CODES PLC
Valeur : POH.segment1
ID : POH.po_header_id
WHERE : POH.vendor_id=:$FLEX$.PO_ASL_VENDOR
and NVL(POH.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
and POH.type_lookup_code='QUOTATION'
and
(
poh.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
OR
(
POH.global_agreement_flag='Y' and
EXISTS (
select pgoa.purchasing_org_id
from po_ga_org_assignments pgoa
where pgoa.po_header_id=poh.po_header_id and
pgoa.purchasing_org_id
IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
)
)
)
AND PLC.LOOKUP_TYPE='YES/NO'
AND PLC.LOOKUP_CODE=NVL(POH.GLOBAL_AGREEMENT_FLAG,'N')
ORDER BY : PLC.DISPLAYED_FIELD "Global Agreement"
Parameter 3 - Global Agreement Flag
LOV 3 - PO_ASL_ENABLE_FLAG
Default val - select decode(nvl(poh.global_agreement_flag,'N'),'N',null,'Y') from po_headers_all poh where poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
Parameter 4 - Purchasing Organization
LOV 4 - SE_PO_ASL_PURCHASING_ORG
Default val - SELECT name FROM hr_all_organization_units_tl houtl WHERE houtl.organization_id= decode(nvl(:$FLEX$.PO_ASL_ENABLE_FLAG:NULL,'N'),'Y',decode ( ( SELECT count(*) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=:$PROFILES$.ORG_ID AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, :$PROFILES$.ORG_ID, null ) ,null) AND language=USERENV('LANG')
Define LOV - Table : PO_HEADERS_ALL POH,PO_GA_ORG_ASSIGNMENTS PGOA, HR_ALL_ORGANIZATION_UNITS HOU,HR_ALL_ORGANIZATION_UNITS HOU1, HR_ALL_ORGANIZATION_UNITS HOU2
Where : POH.global_agreement_flag=:$FLEX$.PO_ASL_ENABLE_FLAG
and POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.po_header_id = POH.po_header_id
and ((POH.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS))
or (PGOA.purchasing_org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)))
and HOU.organization_id=PGOA.purchasing_org_id
and HOU1.organization_id=POH.org_id
and HOU2.organization_id=PGOA.organization_id
Order by : hou1.name as "Owning Organization",
hou2.name as "Requesting Organization"
Parameter 5 - Supplier Site
LOV 5 - SE_PO_ASL_VENDOR_SITE
Default val - SELECT pvs.vendor_site_code FROM po_vendor_sites pvs, po_headers_all poh WHERE pvs.vendor_site_id = decode( nvl(poh.global_agreement_flag,'N'),'N',poh.vendor_site_id, decode ( ( SELECT count(DISTINCT(pgoa.vendor_site_id)) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, ( SELECT DISTINCT (pgoa.vendor_site_id) FROM po_ga_org_assignments pgoa WHERE pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER AND pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) ) , poh.vendor_site_id ) ) AND poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
Define LOV - Table : PO_VENDOR_SITES_ALL POV,PO_GA_ORG_ASSIGNMENTS PGOA,PO_HEADERS_ALL POH
Where : PGOA.po_header_id(+)=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
AND POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.purchasing_org_id(+)=
decode(nvl(POH.global_agreement_flag,'N'),'N',-999,
nvl(:$FLEX$.PO_ASL_PURCHASING_ORG:NULL,(:$PROFILES$.ORG_ID)))
and POV.vendor_site_id=decode(nvl(POH.global_agreement_flag,'N'),'N',
POH.vendor_site_id,
PGOA.vendor_site_id)
Parameter 6 - Select Agreement Lines
LOV 6 - PO_ASL_LINES_SELECTION
Parameter 7 - Assignment Set
LOV 7 - PO_ASL_ASSIGNMENT_SET
Default val - select assignment_set_name from mrp_assignment_sets where assignment_set_id=:$PROFILES$.MRP_DEFAULT_ASSIGNMENT_SET
Parameter 8 - Sourcing Level
LOV 8 - PO_ASL_SOURCING_LEVEL
Parameter 9 - Inv Org Enable
LOV 9 - PO_ASL_ENABLE_FLAG
Default val - select decode(:$FLEX$.PO_ASL_SOURCING_LEVEL,'ITEM',NULL,'Y') from dual
Parameter 10 - Inventory Organization
LOV 10 - PO_ASL_INV_ORG
Parameter 11 - Sourcing Rule Name
LOV 11 - SE_PO_ASL_SR_RULE_NAME
Define LOV - Table : MRP_SOURCING_RULES MSR , org_organization_definitions oog
Where : oog.organization_id = msr.organization_id
AND EXISTS
(
SELECT
1
FROM MRP_SR_RECEIPT_ORG MSRO,
MRP_SR_SOURCE_ORG MSSO
WHERE MSR.SOURCING_RULE_ID=MSRO.SOURCING_RULE_ID
AND MSRO.SR_RECEIPT_ID=msso.SR_RECEIPT_ID
AND msso.vendor_id=:$FLEX$.PO_ASL_VENDOR
AND msso.vendor_site_id=:$FLEX$.SE_PO_ASL_VENDOR_SITE
)
and oog.organization_id=:$FLEX$.PO_ASL_INV_ORG
Order By : oog.organization_name "Organization"
Parameter 12 - Release Generation Method
LOV 12 - PO_PDOI_REL_GEN_METHOD
This concurrent program allows users to create ASLs from one specific or more quotations. We can also customize this program by adding a new parameter request_id so that only quotations with this request_id (probably the request_id of program Import of price catalog) will be treated.
2011年3月29日星期二
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...
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'
);
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.
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.
订阅：
博文 (Atom)
