PL/SQL Script of Purchase Order Interface

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