Purchase Order Interface

Interface Tables :-
-----------------
po_headers_interface
po_lines_interface


Import Concurrent Program :- Import Standard Purchase Orders


Staging Table :-
--------------
CREATE TABLE XXX_PURCHASE_ORDER_STG
(
LEGACY_PONUM NUMBER(20),
CURRENCY_CODE VARCHAR2(15 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE),
VENDOR_SITE_CODE VARCHAR2(15 BYTE),
BILL_TO VARCHAR2(60 BYTE),
SHIP_TO VARCHAR2(60 BYTE),
STATUS VARCHAR2(40 BYTE),
AGENT_NAME VARCHAR2(100 BYTE),
ITEM VARCHAR2(100 BYTE),
LINE_NUM NUMBER(3),
UNIT_PRICE NUMBER(10,2),
QUANTITY NUMBER(10),
NEED_BY_DATE DATE,
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1 BYTE),
H_ERROR_MESSAGE VARCHAR2(2500 BYTE),
L_ERROR_MESSAGE VARCHAR2(2500 BYTE)
)


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;
/