PL/SQL Script to upload data from staging table to interface table
CREATE OR REPLACE PROCEDURE xxx_po_api
AS
---To Import data from Interface to Base Tables - Import Standard Purchase Orders
-- please do the following: to see the errors
-- Run the program - Purchasing Interface Errors Report
-- choose parameter : PO_DOCS_OPEN_INTERFACE
-- po_interface_errors
l_currency_code fnd_currencies_vl.currency_code%type;
l_verify_flag Char(1);
l_error_message varchar2(5000);
l_vendor_id po_vendors.vendor_id%type;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_ship_to hr_locations.location_id%type;
l_bill_to hr_locations.location_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_legacy_ponum number(20):=0;
l_batch_id number(3);
CURSOR C_PO_HEADER IS
select distinct legacy_ponum,
currency_code,
vendor_name,
vendor_site_code,
ship_to,
bill_to,
status
from xxx_purchase_order_stg;
CURSOR C_PO_LINES(l_legacy_ponum NUMBER) IS
select *
from xxx_purchase_order_stg
where trim(legacy_ponum) = trim(l_legacy_ponum);
BEGIN
FOR H1 IN C_PO_HEADER LOOP
l_verify_flag := 'Y' ;
l_error_message := NULL;
BEGIN
select currency_code
into l_currency_code
from fnd_currencies_vl
where enabled_flag = 'Y'
and currency_flag = 'Y'
and upper(currency_code) = upper(trim(H1.currency_code));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Currency Code is not Valid...';
END;
BEGIN
select vendor_id
into l_vendor_id
from po_vendors
where upper(vendor_name) = upper(trim(H1.vendor_name)) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor is not Existing...';
END;
BEGIN
select vendor_site_id
into l_vendor_site_id
from po_vendor_sites_all
where vendor_id = l_vendor_id
and vendor_site_code = upper(trim(H1.vendor_site_code)) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Vendor Site is not Existing...';
END;
BEGIN
select location_id
into l_ship_to
from hr_locations
where location_code = upper(trim(H1.ship_to));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Ship To Location is not Existing...';
END;
BEGIN
select location_id
into l_bill_to
from hr_locations
where location_code = upper(trim(H1.bill_to));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Bill To Location is not Existing...';
END;
If H1.status = 'Approved' then
l_batch_id := 100 ;
elsif H1.status = 'Incomplete' then
l_batch_id := 101 ;
else
l_verify_flag := 'N' ;
l_error_message := l_error_message||'Status is not valid...';
end if;
l_legacy_ponum := trim(H1.legacy_ponum) ;
IF l_verify_flag <> 'N' THEN
insert into po_headers_interface
(interface_header_id,
batch_id,
action,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id
)
values
(po_headers_interface_s.nextval,
l_batch_id,
'ORIGINAL',
'STANDARD',
l_currency_code,
5479,
l_vendor_id,
l_vendor_site_id,
l_ship_to,
l_bill_to );
update xxx_purchase_order_stg
set h_verify_flag = 'Y'
where legacy_ponum = l_legacy_ponum;
COMMIT;
FOR L1 IN C_PO_LINES(l_legacy_ponum) LOOP
BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where segment1||'.'||segment2||'.'||segment3||'.'||segment4=L1.item
and organization_id = (select inventory_organization_id
from hr_locations
where location_id = l_ship_to ) ;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Inventory Item is not Existing...';
END;
IF L1.unit_price IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Unit Price is not Existing...';
ELSIF L1.quantity IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Quantity is not Existing...';
ELSIF L1.need_by_date IS NULL THEN
l_verify_flag := 'N' ;
l_error_message := l_error_message ||'Need By Date is not Existing...';
END IF;
IF l_verify_flag <> 'N' THEN
insert into po_lines_interface
(interface_line_id,
interface_header_id,
action,
line_num,
item_id,
unit_price,
quantity,
Need_By_Date)
values
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
'ORIGINAL',
L1.line_num,
l_inventory_item_id,
L1.unit_price,
L1.quantity,
L1.need_by_date);
update xxx_purchase_order_stg
set l_verify_flag = 'Y'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;
ELSE
update xxx_purchase_order_stg
set l_error_message = l_error_message,
l_verify_flag = 'N'
where legacy_ponum = L1.legacy_ponum
and line_num = L1.line_num;
END IF;
COMMIT;
END LOOP;
ELSE
update xxx_purchase_order_stg
set h_error_message = l_error_message,
h_verify_flag = 'N'
where legacy_ponum = H1.legacy_ponum;
END IF;
COMMIT;
END LOOP;
end xxx_po_api;
/
Purchase Order Interface
