CREATE OR REPLACE procedure p_name
is
    cursor c1 is select rowid,p.* from xx_po_vendor p where err_flag is null;
    v_agent_id          po_agents_name_v.BUYER_ID%type;
    v_id                po_vendors.VENDOR_ID%type;
    v_site_code         po_vendor_sites_all.VENDOR_SITE_CODE%type;
    v_site_id           po_vendor_sites_all.VENDOR_SITE_ID%type;
    v_currency_code     fnd_currencies.CURRENCY_CODE%type;
    v_org_id            hr_operating_units.ORGANIZATION_ID%type;
    v_item_code         mtl_system_items_b.segment1%type;
    v_uom_code          mtl_system_items_b.PRIMARY_UOM_CODE%type;
    v_quantity          po_lines_all.QUANTITY%type;
    v_orgid             org_organization_definitions.ORGANIZATION_ID%type;
    v_sec_name          mtl_secondary_inventories.SECONDARY_INVENTORY_NAME%type;
    v_errflag           varchar2(1);
    v_errmsg            varchar2(2000);
begin
    for r1 in c1
        loop
        v_errflag:=null;
        v_errmsg:=null;
     ------validation of agent_id------------
         if  r1.AGENT_ID is null then
            v_errflag:='Y';
            v_errmsg:='AGENT_ID is null';
        else
            begin
                select  BUYER_ID
                into    v_agent_id
                from    po_agents_name_v
                where   FULL_NAME=r1.AGENT_ID;
                dbms_output.put_line(v_agent_id);
            exception
                when others then
                v_errflag:='Y';
                v_errmsg:='buyer_id is not in system';
                dbms_output.put_line(sqlcode||''||sqlerrm||'buyer_id is not in system');
        end;
        end if;
    -------validaion of vendor_id------------
        if  r1.vendor_name is null then
            v_errflag:='Y';
            v_errmsg:='vendor_name is null';
        else
            begin
                select  VENDOR_ID
                into    v_id
                from    po_vendors
                where   VENDOR_NAME=r1.VENDOR_NAME;
            exception
                when others then
                v_errflag:='Y';
                v_errmsg:='vendor_id is not in system';
                dbms_output.put_line(sqlcode||''||sqlerrm||'vendor_id is not in system');
        end;
        end if;
    -----------validation of vendor_site_code------------------
        if  r1.VENDOR_SITE is null then
            v_errflag:='Y';
            v_errmsg:=v_errmsg||'vendor_site_code is null';
        else
            begin
                select  VENDOR_SITE_CODE
                into    v_site_code
                from    po_vendor_sites_all
                where   VENDOR_SITE_CODE=r1.VENDOR_SITE;
            exception
                when others then
                v_errflag:='Y';
                v_errmsg:=v_errmsg||'vendor_site_code is not in system';
                dbms_output.put_line(sqlcode||''||sqlerrm||'vendor_site_code is not in system');
        end;
        end if;
     -------------validation of vendor_site_id-----------
         if  r1.VENDOR_SITE is null then
            v_errflag:='Y';
            v_errmsg:=v_errmsg||'vendor_site_code is null';
        else
            begin
                select  VENDOR_SITE_ID
                into    v_site_id
                from    po_vendor_sites_all
                where   VENDOR_SITE_CODE=r1.VENDOR_SITE;
            exception
                when others then
                v_errflag:='Y';
                v_errmsg:=v_errmsg||'vendor_site_id is not in system';
                dbms_output.put_line(sqlcode||''||sqlerrm||'vendor_site_id is not in system');
        end;
        end if;
     -------------validation of currency code-----------------
        if  r1.CURRENCY_CODE is null then
            v_errflag:='Y';
            v_errmsg:=v_errmsg||'CURRENCY_CODE is null';
        else
            begin
                select  CURRENCY_CODE
                into    v_currency_code
                from    fnd_currencies
                where   CURRENCY_CODE=r1.CURRENCY_CODE;
            exception
                when others then
                v_errflag:='Y';
                v_errmsg:=v_errmsg||'currency_code is invalid';
                dbms_output.put_line(sqlcode||''||sqlerrm||'v_currency_code is invalid');
        end;
        end if;
        -------validation of item_code-----------------
                if r1.ITEM_CODE is null then
                    v_errflag:='Y';
                    v_errmsg:=v_errmsg||'item_code is null';
                else
                    begin
                        select  SEGMENT1
                        into    v_item_code
                        from    mtl_system_items_b
                        where segment1=r1.ITEM_CODE;
                    exception
                        when others then
                        v_errflag:='Y';
                        v_errmsg:=v_errmsg||'item_code is invalid';
                        dbms_output.put_line(sqlcode||''||sqlerrm||'item_code is invalid');
                end; 
                end if;
        ----------------validation of uom-------------
                 if r1.UNIT_MEAS_LOOKUP_CODE is null then
                    v_errflag:='Y';
                    v_errmsg:=v_errmsg||'uom_code is null';
                else
                    begin
                        select  PRIMARY_UOM_CODE
                        into    v_uom_code
                        from    mtl_system_items_b
                        where PRIMARY_UOM_CODE=r1.UNIT_MEAS_LOOKUP_CODE;
                    exception
                        when others then
                        v_errflag:='Y';
                        v_errmsg:=v_errmsg||'uom_code is invalid';
                        dbms_output.put_line(sqlcode||''||sqlerrm||'uom_code is invalid');
                end; 
                end if;      
        -------------------validation of quantity--------------------
                 if r1.QUANTITY is null then
                    v_errflag:='Y';
                    v_errmsg:=v_errmsg||'QUANTITY is null';
                else
                    begin
                        select  QUANTITY
                        into    v_quantity
                        from    po_lines_all
                        where QUANTITY=r1.QUANTITY;
                    exception
                        when others then
                        v_errflag:='Y';
                        v_errmsg:=v_errmsg||'QUANTITY is invalid';
                        dbms_output.put_line(sqlcode||''||sqlerrm||'QUANTITY is invalid');
                end; 
                end if;
        ----------------------validation of destination_organization------------------
                if r1.DESTINATION_ORGANIZATION_ID is null then
                    v_errflag:='Y';
                    v_errmsg:=v_errmsg||'DESTINATION_ORGANIZATION_ID is null';
                else
                    begin
                        select  ORGANIZATION_ID
                        into    v_orgid
                        from    org_organization_definitions
                        where ORGANIZATION_NAME=r1.DESTINATION_ORGANIZATION_ID;
                    exception
                        when others then
                        v_errflag:='Y';
                        v_errmsg:=v_errmsg||'DESTINATION_ORGANIZATION_ID is invalid';
                        dbms_output.put_line(sqlcode||''||sqlerrm||'DESTINATION_ORGANIZATION_ID is invalid');
                end; 
                end if;
       -------------------validation of Destination Sub inventory --------------
                if r1.DESTINATION_SUBINVENTORY is null then
                    v_errflag:='Y';
                    v_errmsg:=v_errmsg||'DESTINATION_SUBINVENTORY is null';
                else
                    begin
                        select  SECONDARY_INVENTORY_NAME
                        into    v_orgid
                        from    mtl_secondary_inventories
                        where SECONDARY_INVENTORY_NAME=r1.DESTINATION_SUBINVENTORY;
                    exception
                        when others then
                        v_errflag:='Y';
                        v_errmsg:=v_errmsg||'DESTINATION_SUBINVENTORY is invalid';
                        dbms_output.put_line(sqlcode||''||sqlerrm||'DESTINATION_SUBINVENTORY is invalid');
                end; 
                end if;                                           
                   -----------updating stage tab---------------------
                if v_errflag is not null then
                    update xx_po_vendor set err_flag=v_errflag,err_msg=v_errmsg
                    where rowid=r1.rowid;
                    end if;
        ---------inserting interface table--------------------
                if v_errflag is null then               
                insert into po_headers_interface( INTERFACE_HEADER_ID
                                                            ,BATCH_ID
                                                            ,ORG_ID
                                                            ,DOCUMENT_TYPE_CODE
                                                            ,CURRENCY_CODE                                                          
                                                            ,VENDOR_NAME
                                                            ,VENDOR_SITE_CODE
                                                            ,SHIP_TO_LOCATION
                                                            ,BILL_TO_LOCATION
                                                            ,creation_date)  values(po_headers_interface_s.nextval,
                                                            v_agent_id,
                                                            204,
                                                            r1.PO_TYPE,
                                                            v_currency_code,
                                                            r1.VENDOR_NAME,
                                                            v_site_code,
                                                            r1.SHIP_TO_LOCATION_ID,
                                                            r1.BILL_TO_LOCATION_ID,
                                                            sysdate
                                                            );
                insert into po_lines_interface(
                                                INTERFACE_LINE_ID
                                                ,INTERFACE_HEADER_ID
                                                ,LINE_NUM                                       
                                                ,ITEM                                                
                                                ,item_id                                                
                                                ,QUANTITY
                                                ,UNIT_PRICE                                                
                                                ,SHIP_TO_LOCATION
                                                ,NEED_BY_DATE
                                                ,PROMISED_DATE
                                                )
                                                values(po_lines_interface_s.nextval,
                                                po_headers_interface_s.currval,
                                                r1.LINE_NUM,
                                                r1.ITEM_CODE,
                                                r1.ITEM_ID,
                                                r1.QUANTITY,
                                                r1.UNIT_PRICE,
                                                r1.SHIP_TO_LOCATION_ID,
                                                r1.NEED_BY_DATE,
                                                r1.PROMISED_DATE);
                INSERT INTO PO_DISTRIBUTIONS_INTERFACE(
                                                        INTERFACE_HEADER_ID,
                                                        INTERFACE_LINE_ID,
                                                        INTERFACE_DISTRIBUTION_ID,                                                        
                                                        DESTINATION_ORGANIZATION_ID,
                                                        ORG_ID
                                                        )
                                                        values(po_headers_interface_s.currval,
                                                              po_lines_interface_s.currval,
                                                              PO_DISTRIBUTIONS_INTERFACE_s.nextval,
                                                              r1.DESTINATION_ORGANIZATION_ID,
                                                              204);                                                                                                                                   
                                                end if;                                                                                                                                                                                 
end loop;
commit;
end;