1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
Vinay Thakur

AP_Invoices Interface Package 2015-12-02

AP_Invoices Interface Package

  1. Vinay Thakur
    CREATE OR REPLACE PACKAGE BODY APPS.kvap_inv_pkg IS
    /*
    Package Name : kvap_inv_pkg
    Purpose : Package for populating the AP Invoice Information to interface tables
    and to submit the Payables invoice import program
    Procedure Name : kv_main
    Author : Vidyasagar Juturu
    Date : <original release date>
    Description : Main procedure to Validates the Records of staging tables
    and import data into interface tables and submit Payables invoice import program
    Usage : <how to run/execute/deploy this file>
    Parameters :
    Change History
    ------------------
    Version Date Author Description
    -------- ----------- ----------------- ---------------
    1.0 10-JAN-2011 Vidyasagar Juturu Original Code
    1.1 20-Feb-2011 Bala Added runtype parameter
    1.2 06-Mar-2011 Bala Added Process as "AP_Invoices"
    1.3 10-Mar-2011 Bala Description added to the lines table, vendor tax field removed in both table, and ctl file and also in the package
    1.4 28-Mar-2011 Bala corrected the Mapping for WR COA mapping derivation
    1.5 31-Mar-2011 Bala Formatted Output Message, Corrected for if lines are not in Staging, then headers to be errored
    */
    PROCEDURE kv_main(errbuf VARCHAR2,
    retcode NUMBER,
    p_run_type VARCHAR2) IS
    v_request_id NUMBER := fnd_global.conc_request_id;
    CURSOR Cur_Inv_hdr IS
    SELECT kihs.ROWID rid , kihs.*
    FROM kvap_inv_headers_stg kihs
    WHERE 1=1
    --And process_id = 201104943201
    AND NVL(KIHS.process_flag,'N') = 'N';
    CURSOR Cur_Inv_lines (p_inv_num VARCHAR2,p_legacy_supp_num VARCHAR2) IS
    SELECT kils.ROWID rid , kils.*
    FROM kvap_inv_lines_stg kils
    WHERE 1=1
    AND NVL(kils.process_flag,'N') = 'N'
    AND kils.invoice_num = p_inv_num
    AND kils.legacy_supplier_number = p_legacy_supp_num;
    CURSOR kv_inv_cur1 IS
    SELECT DISTINCT KII.process_id
    FROM KVAP_INV_HEADERS_STG KII
    WHERE 1=1
    --And process_id = 201104943201
    AND KII.process_flag = 'V';
    CURSOR kv_inv_cur2(p_process_id NUMBER) IS
    SELECT DISTINCT KII.process_id,KII.batch_name
    FROM KVAP_INV_HEADERS_STG KII
    WHERE 1=1
    AND KII.PROCESS_ID=p_process_id
    AND KII.process_flag = 'V';
    CURSOR kv_inv_cur3(p_process_id NUMBER,p_batch_name VARCHAR2) IS
    SELECT KII.ROWID RID, KII.*
    FROM KVAP_INV_HEADERS_STG KII
    WHERE 1=1
    AND KII.process_flag = 'V'
    AND KII.process_id= p_process_id
    AND KII.batch_name=p_batch_name;
    CURSOR kv_inv_cur4(p_inv_num VARCHAR2,p_legacy_supp_num VARCHAR2) IS
    SELECT KIL.ROWID RID,KIL.*
    FROM KVAP_INV_LINES_STG KIL
    WHERE 1=1
    AND KIL.process_flag = 'V'
    AND KIL.INVOICE_NUM = p_inv_num
    AND KIL.legacy_supplier_number = p_legacy_supp_num ;
    CURSOR c_kv_inv_validate(p_process_id NUMBER,p_batch_name VARCHAR2) IS
    SELECT KII.ROWID RID, KII.*
    FROM KVAP_INV_HEADERS_STG KII
    WHERE KII.PROCESS_FLAG = 'P'
    AND KII.process_id=p_process_id
    AND KII.batch_name=p_batch_name;
    CURSOR kv_inv_cur5 IS
    SELECT DISTINCT invoice_num, batch_name
    FROM KVAP_INV_HEADERS_STG
    WHERE PROCESS_FLAG = 'S';
    CURSOR kv_inv_cur6 IS
    SELECT DISTINCT process_id,request_id
    FROM KVAP_INV_HEADERS_STG
    WHERE request_id = v_request_id
    ORDER BY process_id;
    CURSOR kv_inv_sum_cur7(p_process_id NUMBER,p_req_id NUMBER) IS
    SELECT *
    FROM KVAP_INV_HEADERS_STG KIHS
    WHERE KIHS.PROCESS_FLAG = 'E'
    AND Process_id=p_process_id
    AND request_id=p_req_id
    ORDER BY process_id, invoice_num;
    CURSOR kv_inv_sum_cur8(p_process_id NUMBER,p_invoice_number varchar2) IS
    SELECT *
    FROM KVAP_INV_LINES_STG KIHS
    WHERE KIHS.PROCESS_FLAG = 'E'
    AND Process_id=p_process_id
    AND invoice_num=p_invoice_number
    ORDER BY process_id, line_number;
    v_pgrid NUMBER(10);
    v_process_id NUMBER;
    v_line_process_id NUMBER;
    v_source_name VARCHAR2(25);
    v_err_flag VARCHAR2(1) := 'N';
    v_err_msg VARCHAR2(4000) := NULL;
    v_batch_name VARCHAR2(50);
    v_lookup_code VARCHAR2(50);
    v_leg_ven_num NUMBER;
    v_vendor_id NUMBER;
    v_vendor_site_id NUMBER;
    v_vendor_site_code VARCHAR2(25);
    v_term_id NUMBER;
    v_inv_curr_code VARCHAR2(25);
    v_pay_curr_code VARCHAR2(25);
    v_org_id NUMBER;
    v_gl_date VARCHAR2(10);
    v_closing_status VARCHAR2(2);
    v_exchane_rate NUMBER(22);
    v_exchange_rate_type VARCHAR2(25);
    v_pay_method_code VARCHAR2(25);
    v_pay_group VARCHAR2(25);
    v_inv_id NUMBER;
    v_line_lookup_code VARCHAR2(25);
    v_orgnz_id NUMBER;
    --v_gl_code varchar2(50);
    v_inv_line_id NUMBER;
    record_total NUMBER;
    record_error NUMBER;
    record_success NUMBER;
    total_amount NUMBER;
    total_amount_err NUMBER;
    amount_currency VARCHAR2(25);
    amount_success NUMBER;
    line_record_total NUMBER;
    line_record_error NUMBER;
    line_record_success NUMBER;
    line_total_amount NUMBER;
    line_total_amount_err NUMBER;
    line_amount_currency VARCHAR2(25);
    line_amount_succes NUMBER;
    v_rec_tot NUMBER;
    v_rec_err NUMBER;
    v_rec_succ NUMBER;
    v_amt_tot NUMBER;
    v_amt_err NUMBER;
    v_amt_curr VARCHAR2(10);
    v_amt_succ NUMBER;
    l_kvap_group_seq NUMBER;
    v_cnt NUMBER;
    l_phase VARCHAR2(2000) := '' ;
    l_status VARCHAR2(2000) := '' ;
    l_dev_phase VARCHAR2(2000) := '' ;
    l_dev_status VARCHAR2(2000) := '' ;
    l_message VARCHAR2(2000) := '' ;
    l_get_request_status BOOLEAN ;
    h_cnt NUMBER :=0;
    l_cnt NUMBER :=0;
    v_val_count NUMBER :=0;
    v_req_val_id NUMBER :=0;
    v_code_comb_id NUMBER :=0;
    v_batch_id NUMBER :=0;
    v_req_acc_id NUMBER :=0;
    l_get_val_request_status BOOLEAN;
    l_dev_val_phase VARCHAR2(2000) := '' ;
    l_dev_val_status VARCHAR2(2000) := '' ;
    l_get_acc_request_status BOOLEAN;
    l_dev_acc_phase VARCHAR2(2000) := '' ;
    l_dev_acc_status VARCHAR2(2000) := '' ;
    l_lines_error VARCHAR2(1) := 'N';
    v_int_cnt NUMBER := 0;
    v_int_inv_num VARCHAR2(50) := NULL;
    v_process_name VARCHAR2(50) := NULL;
    l_line_number NUMBER;
    l_period_end_date DATE;
    v_req_id NUMBER;
    l_error_cnt Number;
    l_error_line_cnt Number;
    l_line_cnt Number;
    BEGIN
    fnd_file.put_line(fnd_file.LOG,'user id....'||fnd_global.user_id);
    fnd_file.put_line(fnd_file.LOG,'resp id....'||fnd_global.resp_id);
    fnd_file.put_line(fnd_file.LOG,'resp app id....'||fnd_global.resp_appl_id);
    v_pgrid:=fnd_global.CONC_PROGRAM_ID;
    fnd_file.put_line(fnd_file.LOG,'CONC_PROGRAM_ID....'||v_pgrid);
    fnd_file.put_line(fnd_file.LOG,'Request Id...'||v_request_id);
    fnd_global.apps_initialize(
    fnd_global.user_id,
    fnd_global.resp_id,
    fnd_global.resp_appl_id);
    mo_global.init('SQLAP');
    IF p_run_type = 'LOADER' OR p_run_type = 'COMPLETE' THEN
    fnd_file.put_line(fnd_file.LOG,'calling loader program');
    kv_comm_Interface_pkg.kv_common_lookup_proc(v_pgrid);
    fnd_file.put_line(fnd_file.LOG,'completion of loader program');
    /*
    Update all the Errored records (status flag = E) in previous run to 'N' so as
    reprocess the records in the current run
    */
    UPDATE KVAP_INV_HEADERS_STG
    SET process_flag='N',
    error_message=NULL
    WHERE process_flag='E';
    UPDATE KVAP_INV_LINES_STG
    SET process_flag='N',
    error_message=NULL
    WHERE process_flag='E';
    COMMIT;
    END IF;
    IF p_run_type = 'COMPLETE' OR p_run_type = 'IMPORT' THEN
    -- Start validating the records in staging table
    UPDATE KVAP_INV_HEADERS_STG
    SET request_id = v_request_id
    WHERE process_flag='N';
    --WHERE request_id IS NULL;
    COMMIT;
    ----------------------------------------------------------------------
    FOR c_inv_hdr IN cur_inv_hdr LOOP
    v_lookup_code := NULL;
    v_vendor_site_id := NULL;
    v_vendor_site_code := NULL;
    v_vendor_id := NULL;
    v_org_id := NULL;
    v_exchange_rate_type := NULL;
    v_err_msg := NULL;
    v_err_flag := 'N';
    ----------- checking the control count null or not -------------
    BEGIN
    IF c_inv_hdr.control_count IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Control Count is null';
    END IF;
    END;
    ----------- checking the control total null or not -------------
    BEGIN
    IF c_inv_hdr.control_total IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Control Total is null';
    END IF;
    END;
    ----------- checking the invoice number null or not -------------
    BEGIN
    IF c_inv_hdr.invoice_num IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Number is null';
    END IF;
    END;
    ---------- validation for the invoice type ------------------
    BEGIN
    IF c_inv_hdr.invoice_type_lookup_code IS NOT NULL THEN
    SELECT flv.lookup_code
    INTO v_lookup_code
    FROM fnd_lookup_values flv,
    fnd_application fa
    WHERE flv.view_application_id= fa.application_id
    AND fa.application_short_name='SQLAP'
    AND lookup_type = 'INVOICE TYPE'
    AND flv.meaning = c_inv_hdr.invoice_type_lookup_code;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Type Lookup Code is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Invoice Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In no data found of Invoice Type ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Invoice Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Invoice Type ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Invoice Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Invoice Type ';
    END;
    ----------- checking the invoice date null or not -------------
    BEGIN
    IF c_inv_hdr.invoice_date IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Date is null';
    END IF;
    END;
    ----------- validation for the operating unit name -----------
    BEGIN
    IF c_inv_hdr.operating_unit IS NOT NULL THEN
    SELECT organization_id
    INTO v_org_id
    FROM hr_operating_units
    WHERE NAME = c_inv_hdr.operating_unit;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||'Operating Unit is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In no data found of Operating Unit ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Operating Unit ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Operating Unit ';
    END;
    ----------- validation for the legacy supplier number -----------
    BEGIN
    SELECT ass.vendor_site_id,
    ass.vendor_site_code,aps.vendor_id
    INTO v_vendor_site_id,
    v_vendor_site_code,v_vendor_id
    FROM ap_supplier_sites_all ass,
    ap_suppliers aps
    WHERE aps.vendor_id = ass.vendor_id
    AND NVL(ass.inactive_date ,SYSDATE) > = SYSDATE
    AND ass.attribute1 = c_inv_hdr.legacy_supp_number
    AND ass.org_id = v_org_id;
    fnd_file.put_line(fnd_file.LOG,'In deriving the vendor site id');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Deriving the Vendor Site ID');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Supplier not available in Oracle ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Deriving the Vendor Site ID');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to the Vendor Site ID ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Deriving the Vendor Site ID');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of the Vendor Site ID ';
    END;
    ----------- checking the invoice amount is null or not -------
    BEGIN
    IF c_inv_hdr.invoice_amount IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Amount is null';
    END IF;
    END;
    ---------- validation for the invoice currency code ------------------
    BEGIN
    IF c_inv_hdr.invoice_currency_code IS NOT NULL THEN
    SELECT currency_code
    INTO v_inv_curr_code
    FROM fnd_currencies
    WHERE currency_code = c_inv_hdr.invoice_currency_code;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Currency Code is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Invoice Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In no data found of Invoice Currency Code ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Invoice Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Invoice Currency Code ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Invoice Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Invoice Currency Code ';
    END;
    ---------- validation for the payment currency code ------------------
    BEGIN
    IF c_inv_hdr.payment_currency_code IS NOT NULL THEN
    SELECT currency_code
    INTO v_pay_curr_code
    FROM fnd_currencies
    WHERE currency_code = c_inv_hdr.payment_currency_code;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Payment Currency Code is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Payment Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In no data found of Payment Currency Code ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Payment Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Payment Currency Code ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Payment Currency Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Payment Currency Code ';
    END;
    ----------- checking the invoice source is null or not -------
    BEGIN
    IF c_inv_hdr.SOURCE IS NOT NULL THEN
    SELECT flv.lookup_code
    INTO v_source_name
    FROM fnd_lookup_values flv,fnd_application fa
    WHERE flv.view_application_id= fa.application_id
    AND fa.application_short_name='SQLAP'
    AND lookup_type = 'SOURCE'
    AND flv.lookup_code = c_inv_hdr.SOURCE;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Invoice Source is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of invoice source validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In no data found of invoice source ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of invoice source validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to invoice source ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of invoice source validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of invoice source ';
    END;
    ----------- checking the gl date is null or not -------
    /*BEGIN
    IF TO_CHAR(TO_DATE(c_inv_hdr.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY') IS NOT NULL THEN
    SELECT closing_status
    INTO v_closing_status
    FROM gl_period_statuses
    WHERE closing_status='O'
    AND TO_CHAR(TO_DATE(c_inv_hdr.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY') BETWEEN START_DATE AND END_DATE
    AND application_id IN (SELECT DISTINCT application_id
    FROM fnd_application_vl
    WHERE application_name LIKE 'Payables%')
    AND set_of_books_id = (SELECT org_information3
    FROM hr_organization_information_v
    WHERE organization_id = v_org_id
    AND org_information_context = 'Operating Unit Information');
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.PUT_LINE(fnd_file.LOG, 'STATUS INVALID for GL_DATE');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||CHR(10)|| 'GL Date not in an Open Period :' || TO_CHAR(TO_DATE(c_inv_hdr.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY');
    WHEN TOO_MANY_ROWS THEN
    fnd_file.PUT_LINE(fnd_file.LOG, 'TOO MANY VALUES for GL_DATE');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||CHR(10)|| 'TOO MANY VALUES for GL_DATE :' || TO_CHAR(TO_DATE(c_inv_hdr.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY');
    WHEN OTHERS THEN
    fnd_file.PUT_LINE(fnd_file.LOG, 'Unexpected Error while validating GL_DATE');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||CHR(10)|| 'Unexpected Error while validating GL_DATE' || TO_CHAR(TO_DATE(c_inv_hdr.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY');
    END;*/
    ---------- validation for the exchange rate --------------
    BEGIN
    IF trim(c_inv_hdr.exchange_rate) IS NOT NULL THEN
    SELECT DISTINCT conversion_rate,conversion_type
    INTO v_exchane_rate,v_exchange_rate_type
    FROM gl_daily_rates
    WHERE ROUND(conversion_rate,2) = ROUND(c_inv_hdr.exchange_rate,2)
    AND trim(conversion_date) = trim(TO_DATE(c_inv_hdr.exchange_date,'DD-MON-YYYY'))
    AND from_currency = 'USD';
    fnd_file.put_line(fnd_file.LOG,'In deriving the Exchange Rate');
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Exchange Rate validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' no data found of Exchange Rate ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Exchange Rate validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Exchange Rate ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Exchange Rate validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Exchange Rate ';
    END;
    ---------- validation for the payment method --------------
    BEGIN
    IF c_inv_hdr.payment_method IS NOT NULL THEN
    SELECT flv.lookup_code
    INTO v_pay_method_code
    FROM fnd_lookup_values flv,
    fnd_application fa
    WHERE flv.view_application_id= fa.application_id
    AND fa.application_short_name='SQLAP'
    AND lookup_type = 'PAYMENT METHOD'
    AND lookup_code = c_inv_hdr.payment_method;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Payment Method validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' no data found of Payment Method ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Payment Method validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Payment Method ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Payment Method validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Payment Method ';
    END;
    IF v_err_flag = 'N' THEN
    BEGIN
    UPDATE kvap_inv_headers_stg kii
    SET invoice_type = v_lookup_code,
    vendor_id = v_vendor_id,
    vendor_site_id = v_vendor_site_id,
    vendor_site_code = v_vendor_site_code,
    exchange_rate_type = v_exchange_rate_type,
    org_id = v_org_id,
    error_message = 'validated',
    process_flag = 'V'
    WHERE kii.ROWID = c_inv_hdr.rid;
    END;
    l_lines_error := 'N';
    l_cnt := 0;
    FOR c_inv_lines IN cur_inv_lines(c_inv_hdr.invoice_num,c_inv_hdr.LEGACY_SUPP_NUMBER) LOOP
    ---------- validation for the line type lookup code -----------
    l_cnt := l_cnt+1;
    v_err_flag := 'N';
    v_err_msg := Null;
    v_orgnz_id := Null;
    v_line_lookup_code := null;
    v_code_comb_id := Null;
    BEGIN
    IF c_inv_lines.line_type_lookup_code IS NOT NULL THEN
    SELECT lookup_code
    INTO v_line_lookup_code
    FROM ap_lookup_codes
    WHERE lookup_type = 'INVOICE LINE TYPE'
    AND lookup_code = c_inv_lines.line_type_lookup_code;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Line Type Lookup Code is null ';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Invoice Line Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' No data found of Invoice Line Type Lookup Code ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Invoice Line Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Invoice Line Type Lookup Code ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Invoice Line Type Lookup Code validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Invoice Line Type Lookup Code ';
    END;
    ---------- validation for the operating unit -----------------------
    BEGIN
    IF c_inv_lines.operating_unit IS NOT NULL THEN
    SELECT organization_id
    INTO v_orgnz_id
    FROM hr_operating_units
    WHERE NAME = c_inv_lines.operating_unit;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||'Operating Unit is null';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' No data found of Operating Unit ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Operating Unit ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Operating Unit validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In others of Operating Unit ';
    END;
    ---------- checking the amount is null or not ------------------
    BEGIN
    IF c_inv_lines.amount IS NULL THEN
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||'Amount is null';
    END IF;
    END;
    ---------- validation for the dist code combination id -------------
    BEGIN
    IF c_inv_lines.ACCOUNT IS NOT NULL THEN
    IF c_inv_lines.operating_unit = 'KV Workrite USA' THEN
    SELECT to_code_combination_id
    INTO v_code_comb_id
    FROM GL_CONS_FLEXFIELD_MAP gcf,
    gl_coa_mappings_v gcm
    WHERE gcf.coa_mapping_id = gcm.mapping_id
    AND gcm.NAME = 'Oracle to Syteline'
    --AND gcf.segment1_low||'.'||gcf.segment2_low = c_inv_lines.ACCOUNT;
    AND gcf.segment1_low||'.'||gcf.segment2_low = substr( c_inv_lines.ACCOUNT, instr( c_inv_lines.ACCOUNT,'.',1,2)+1,length(c_inv_lines.ACCOUNT) );
    ELSIF c_inv_lines.operating_unit = 'KV Canada OU' THEN
    -- For Conversion of Canada invoices there will be only clearing account which need to be passed.
    Select code_combination_id
    INTO v_code_comb_id
    FROM gl_code_combinations_kfv
    Where concatenated_segments = '103.1001.40999.00000.000.0000.0000';
    ELSE
    SELECT to_code_combination_id
    INTO v_code_comb_id
    FROM GL_CONS_FLEXFIELD_MAP gcf,
    gl_coa_mappings_v gcm
    WHERE gcf.coa_mapping_id = gcm.mapping_id
    AND gcm.NAME = 'Oracle to Great Plains'
    AND gcf.segment1_low||'.'||gcf.segment2_low||'.'||gcf.segment3_low||'.'||gcf.segment4_low = c_inv_lines.ACCOUNT;
    END IF;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Dist Account is null ';
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Invoice Dist Account validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||'COA Mapping Validation Failed for account ';
    WHEN TOO_MANY_ROWS THEN
    fnd_file.put_line(fnd_file.LOG,'In too many rows of Invoice Dist Account validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Too Many Rows to Invoice Dist Account ';
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Invoice Dist Account validation');
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' In Others Then of Invoice Dist Account ';
    END;
    IF v_err_flag = 'N' THEN
    UPDATE KVAP_INV_LINES_STG
    SET org_id = v_orgnz_id,
    code_combination_id = v_code_comb_id,
    error_message = 'validated',
    process_flag = 'V'
    WHERE ROWID = c_inv_lines.rid;
    ELSE
    l_lines_error := 'Y';
    UPDATE KVAP_INV_LINES_STG
    SET error_message = v_err_msg,
    process_flag = 'E'
    WHERE ROWID = c_inv_lines.rid;
    END IF;
    END LOOP; -- c_inv_lines
    IF l_lines_error = 'Y' THEN
    BEGIN
    UPDATE kvap_inv_headers_stg kii
    SET error_message = 'Lines Validation Failed',
    process_flag = 'E'
    WHERE kii.ROWID = c_inv_hdr.rid;
    END;
    END IF;
    If l_cnt = 0 Then
    BEGIN
    UPDATE kvap_inv_headers_stg kii
    SET error_message = 'No Line Records available',
    process_flag = 'E'
    WHERE kii.ROWID = c_inv_hdr.rid;
    END;
    END IF;
    ELSE
    BEGIN
    UPDATE kvap_inv_headers_stg
    SET error_message = v_err_msg,
    process_flag = 'E'
    WHERE ROWID = c_inv_hdr.rid;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    END IF; -- v_err_flag
    END LOOP; -- c_inv_hdr
    l_period_end_date := NULL;
    BEGIN
    SELECT MAX(end_date)
    INTO l_period_end_date
    FROM gl_period_statuses
    WHERE closing_status='O'
    --AND sysdate BETWEEN START_DATE AND END_DATE
    AND application_id IN (SELECT DISTINCT application_id
    FROM fnd_application_vl
    WHERE application_name LIKE 'Payables%')
    AND set_of_books_id = ( SELECT org_information3
    FROM hr_organization_information_v
    WHERE organization_id = v_org_id
    AND org_information_context = 'Operating Unit Information');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    IF l_period_end_date > SYSDATE THEN
    l_period_end_date := SYSDATE;
    END IF;
    FOR inv_prid IN kv_inv_cur1 LOOP -- Distinct Processid
    FOR inv_prec IN kv_inv_cur2(inv_prid.process_id)
    LOOP -- Distinct Batches for that processid
    h_cnt :=h_cnt +1;
    SELECT kvap_group_seq.NEXTVAL
    INTO l_kvap_group_seq
    FROM dual;
    FOR inv_rec IN kv_inv_cur3(inv_prec.process_id,inv_prec.batch_name)
    LOOP -- All the headers for that specific batch
    BEGIN
    SELECT ap_invoices_interface_s.NEXTVAL
    INTO v_inv_id
    FROM DUAL;
    fnd_file.put_line(fnd_file.LOG,'In IF section of the Headers Update and Insersion');
    INSERT INTO ap_invoices_interface
    ( invoice_id,
    invoice_num,
    invoice_type_lookup_code,
    attribute1,
    vendor_id,
    vendor_site_id,
    vendor_site_code,
    invoice_amount,
    invoice_currency_code,
    payment_currency_code,
    SOURCE,
    gl_date,
    invoice_date,
    org_id,
    exchange_rate,
    exchange_rate_type,
    exchange_date,
    payment_method_lookup_code,
    pay_group_lookup_code,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    group_id
    )
    VALUES
    ( v_inv_id,
    inv_rec.invoice_num,
    inv_rec.invoice_type,
    inv_rec.legacy_supp_number,
    inv_rec.vendor_id,
    inv_rec.vendor_site_id,
    inv_rec.vendor_site_code,
    inv_rec.invoice_amount,
    inv_rec.invoice_currency_code,
    inv_rec.payment_currency_code,
    inv_rec.SOURCE,
    l_period_end_date, --TO_CHAR(TO_DATE(inv_rec.gl_date,'MM/DD/YYYY'),'DD-MON-YYYY'), --inv_rec.gl_date, -- trim(to_date(inv_rec.gl_date,'MM/DD/YYYY'))
    TO_CHAR(TO_DATE(inv_rec.invoice_date,'MM/DD/YYYY'),'DD-MON-YYYY'), --inv_rec.invoice_date, -- trim(to_date(inv_rec.invoice_date,'MM/DD/YYYY'))
    inv_rec.org_id,
    inv_rec.exchange_rate,
    inv_rec.exchange_rate_type,
    inv_rec.exchange_date,
    inv_rec.payment_method,
    inv_rec.pay_group,
    SYSDATE,
    fnd_global.user_id,
    SYSDATE,
    fnd_global.user_id,
    fnd_global.user_id,
    l_kvap_group_seq
    );
    UPDATE kvap_inv_headers_stg kii
    SET error_message = 'PROCESSED',
    process_flag = 'P'
    WHERE kii.rowid = inv_rec.rid;
    EXCEPTION
    WHEN OTHERS THEN
    fnd_file.PUT_LINE(fnd_file.LOG, 'Exception while inserting Headers staging table '||SQLERRM);
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Exception while inserting Headers staging table '||SQLERRM;
    END;
    l_line_number :=0;
    FOR inv_lines_rec IN kv_inv_cur4(inv_rec.invoice_num,inv_rec.LEGACY_SUPP_NUMBER) LOOP
    --l_line_number := nvl(l_line_number,0) +1;
    BEGIN
    SELECT ap_invoice_lines_interface_s.NEXTVAL
    INTO v_inv_line_id
    FROM dual;
    INSERT INTO ap_invoice_lines_interface
    (invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    accounting_date,
    quantity_invoiced,
    unit_price,
    amount,
    org_id,
    dist_code_combination_id,
    attribute1,
    attribute2,
    attribute3,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    description
    )
    VALUES
    (v_inv_id,
    v_inv_line_id,
    inv_lines_rec.line_number,
    inv_lines_rec.line_type_lookup_code,
    l_period_end_date , --TO_CHAR(TO_DATE(inv_lines_rec.accounting_date,'MM/DD/YYYY'),'DD-MON-YYYY'),--inv_lines_rec.accounting_date, --trim(to_date(inv_lines_rec.accounting_date,'MM/DD/YYYY'))
    inv_lines_rec.quantity_invoiced,
    inv_lines_rec.unit_price,
    inv_lines_rec.amount,
    inv_lines_rec.org_id,
    inv_lines_rec.code_combination_id,
    inv_lines_rec.legacy_po_num,
    inv_lines_rec.legacy_po_line_num,
    inv_lines_rec.self_assmnt_tax,
    SYSDATE,
    fnd_global.user_id,
    SYSDATE,
    fnd_global.user_id,
    fnd_global.user_id,
    inv_lines_rec.description
    );
    /*If inv_lines_rec.vendor_tax_amt>0 Then
    l_line_number := nvl(l_line_number,0) +1;
    SELECT ap_invoice_lines_interface_s.NEXTVAL
    INTO v_inv_line_id
    FROM dual;
    INSERT INTO ap_invoice_lines_interface
    (invoice_id,
    invoice_line_id,
    line_number,
    line_type_lookup_code,
    accounting_date,
    quantity_invoiced,
    unit_price,
    amount,
    org_id,
    dist_code_combination_id,
    attribute1,
    attribute2,
    attribute3,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    description
    )
    VALUES
    (v_inv_id,
    v_inv_line_id,
    l_line_number , --inv_lines_rec.line_number,
    inv_lines_rec.line_type_lookup_code,
    l_period_end_date , -- TO_CHAR(TO_DATE(inv_lines_rec.accounting_date,'MM/DD/YYYY'),'DD-MON-YYYY'),--inv_lines_rec.accounting_date, --trim(to_date(inv_lines_rec.accounting_date,'MM/DD/YYYY'))
    inv_lines_rec.quantity_invoiced,
    inv_lines_rec.unit_price,
    inv_lines_rec.vendor_tax_amt,
    inv_lines_rec.org_id,
    inv_lines_rec.code_combination_id,
    inv_lines_rec.legacy_po_num,
    inv_lines_rec.legacy_po_line_num,
    inv_lines_rec.self_assmnt_tax,
    SYSDATE,
    fnd_global.user_id,
    SYSDATE,
    fnd_global.user_id,
    fnd_global.user_id,
    'Tax Line'
    );
    End If;*/
    UPDATE kvap_inv_lines_stg kil
    SET error_message = 'PROCESSED',
    process_flag = 'P'
    WHERE kil.ROWID = inv_lines_rec.rid;
    EXCEPTION
    WHEN OTHERS THEN
    fnd_file.PUT_LINE(fnd_file.LOG, 'Exception while inserting Lines staging table '||SQLERRM);
    END;
    END LOOP; -- kv_inv_cur4 Lines
    COMMIT;
    v_source_name := inv_rec.SOURCE;
    END LOOP; -- kv_inv_cur3(p_process_id, p_batch_name )
    ------------- Calling the Standard Payables Program ----------
    BEGIN
    v_req_id := FND_REQUEST.SUBMIT_REQUEST( 'SQLAP',
    'APXIIMPT',
    'Payables Open Interface Import',
    SYSDATE,
    FALSE,
    NULL,
    v_source_name,
    l_kvap_group_seq, -- Group_id
    inv_prec.batch_name,
    NULL,NULL,NULL,
    NULL,NULL,NULL,
    NULL,NULL,NULL,NULL
    );
    COMMIT;
    IF v_req_id > 0 THEN
    fnd_file.put_line(fnd_file.LOG,v_request_id);
    fnd_file.put_line(fnd_file.LOG,'In Submitting the Standard Concurrent Program');
    ELSE
    fnd_file.put_line(fnd_file.LOG,'In Submitting the Standard Concurrent Program not successfully');
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of submitting the Standard Concurrent Program');
    END;
    l_get_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(
    phase => l_phase,
    request_id => v_req_id,
    INTERVAL => 10,
    max_wait => 0 ,
    STATUS => l_status,
    dev_phase => l_dev_phase,
    dev_status => l_dev_status,
    MESSAGE => l_message);
    fnd_file.put_line(fnd_file.LOG,'Program Phase :'||l_dev_phase);
    fnd_file.put_line(fnd_file.LOG,'Program Status :'||l_dev_status);
    COMMIT;
    FOR cur_inv_validate IN c_kv_inv_validate (inv_prec.process_id,inv_prec.batch_name)
    LOOP
    v_cnt :=0;
    SELECT COUNT(*)
    INTO v_cnt
    FROM ap_invoices_all
    WHERE invoice_num= cur_inv_validate.invoice_num
    AND vendor_site_id IN (SELECT vendor_site_id
    FROM ap_supplier_sites_all
    WHERE attribute1 = cur_inv_validate.legacy_supp_number);
    IF v_cnt >0 THEN
    UPDATE KVAP_INV_HEADERS_STG
    SET Process_flag = 'S',ERROR_MESSAGE='Success'
    WHERE ROWID = cur_inv_validate.RID;
    ELSE
    NULL;
    /*UPDATE KVAP_INV_HEADERS_STG
    SET Process_flag = 'E',ERROR_MESSAGE='Not Imported'
    WHERE ROWID = cur_inv_validate.RID;*/
    END IF;
    END LOOP; --cur_inv_validate
    /*IF l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL' THEN
    fnd_file.put_line(fnd_file.LOG,'In Calling the Invoice Validation');
    BEGIN
    IF inv_prec.batch_name IS NOT NULL THEN
    SELECT ab.batch_id
    INTO v_batch_id
    FROM ap_batches_all ab
    WHERE ab.batch_name = inv_prec.batch_name;
    ELSE
    v_err_flag := 'Y';
    v_err_msg := v_err_msg||' '||' Batch Name is null';
    END IF;
    fnd_file.put_line(fnd_file.LOG,'Count to call invoice validation : '||v_val_count);
    v_req_val_id := FND_REQUEST.SUBMIT_REQUEST(
    'SQLAP',
    'APPRVL',
    'Invoice Validation',
    SYSDATE,
    FALSE,
    NULL,
    'ALL', -- Option
    v_batch_id
    );
    fnd_file.put_line(fnd_file.LOG,'Count to call invoice validation : '||v_val_count);
    fnd_file.put_line(fnd_file.LOG,v_req_val_id);
    fnd_file.put_line(fnd_file.LOG,'In Submitting the Validation Program');
    EXCEPTION
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of submitting the Validation Program');
    END;
    l_get_val_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(
    request_id => v_request_id,
    INTERVAL => 10,
    max_wait => 0 ,
    phase => l_phase,
    STATUS => l_status,
    dev_phase => l_dev_val_phase,
    dev_status => l_dev_val_status,
    MESSAGE => l_message);
    fnd_file.put_line(fnd_file.LOG,'Program Phase :'||l_dev_phase);
    fnd_file.put_line(fnd_file.LOG,'Program Status :'||l_dev_status);
    END IF; -- l_dev_phase
    */
    END LOOP; -- kv_inv_cur2 End of Batch id,Process id Loop
    COMMIT;
    END LOOP; -- kv_inv_cur1
    fnd_file.PUT_LINE(fnd_file.OUTPUT,' Summary Information Report ');
    fnd_file.PUT_LINE(fnd_file.OUTPUT,'****************************** ');
    FOR sum_rec IN kv_inv_cur6 LOOP
    BEGIN
    v_process_id := sum_rec.process_id;
    v_rec_tot := 0;
    v_rec_err := 0;
    v_amt_succ := 0;
    v_rec_succ := 0;
    v_amt_tot := 0;
    v_amt_err := 0;
    SELECT DISTINCT
    ( SELECT NVL(COUNT(1),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id AND request_id = v_request_id) record_total,
    ( SELECT NVL(COUNT(1),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND process_flag='E'
    AND request_id = v_request_id) record_error,
    ( SELECT NVL(COUNT(1),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND process_flag = 'S'
    AND request_id = v_request_id) record_success,
    ( SELECT NVL(SUM(invoice_amount),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND request_id = v_request_id) total_amount,
    ( SELECT NVL(SUM(invoice_amount),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND process_flag='E'
    AND request_id = v_request_id) total_amount_err,
    ( SELECT DISTINCT NVL(INVOICE_CURRENCY_CODE,0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND request_id = v_request_id
    AND ROWNUM<2) amount_currency,
    ( SELECT DISTINCT NVL(SUM(invoice_amount),0)
    FROM KVAP_INV_HEADERS_STG
    WHERE process_id=v_process_id
    AND process_flag = 'S'
    AND request_id = v_request_id) amount_success,
    'AP_Invoices' Process_name
    INTO v_rec_tot,v_rec_err,v_rec_succ,v_amt_tot,v_amt_err,v_amt_curr,v_amt_succ,v_process_name
    FROM KVAP_INV_HEADERS_STG KVIH
    WHERE KVIH.PROCESS_ID=v_process_id
    AND request_id = v_request_id ;
    fnd_file.put_line(fnd_file.LOG,'In Headers Common Program Block');
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    fnd_file.put_line(fnd_file.LOG,'In no data found of Common Program for Invoice Headers');
    WHEN OTHERS THEN
    fnd_file.put_line(fnd_file.LOG,'In others of Common Program for Invoice Headers');
    END;
    fnd_file.PUT_LINE(fnd_file.OUTPUT, Null);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, Null);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Process ID : '||v_process_id);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Record Total : '||v_rec_tot );
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Error Total : '||v_rec_err );
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Amount Success : '||v_amt_succ);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Success Total : '||v_rec_succ);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Amount Total : '||v_amt_tot);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Amount Error : '||v_amt_err);
    fnd_file.put_line(fnd_file.LOG,'Calling the Headers Common Program');
    kv_comm_Interface_pkg.kv_common_proc(v_request_id,
    v_process_id,
    'AP_Invoices',
    v_rec_tot,
    v_rec_err,
    v_rec_succ,
    v_amt_tot,
    v_amt_err,
    v_amt_curr,
    v_amt_succ
    );
    -- Error Messages display
    --fnd_file.put_line(fnd_file.output,RPAD( '-',160,'-'));
    fnd_file.PUT_LINE(fnd_file.OUTPUT, RPAD( 'Process ID',20,' ' )||'|' ||
    RPAD( 'Invoice Number',20,' ' )||'|' ||
    RPAD( 'Legacy Supplier',20,' ' )||'|' ||
    RPAD( 'Invoice amount',20,' ' )||'|' ||
    RPAD( 'Operating Unit',20,' ' )||'|' ||
    RPAD( 'Batch Name',20,' ' )||'|' ||
    RPAD( 'Error Details',20,' ' )
    );
    fnd_file.put_line(fnd_file.output,RPAD( '-',160,'-'));
    l_error_cnt :=0;
    FOR sumry_rec IN kv_inv_sum_cur7(sum_rec.process_id,sum_rec.request_id) LOOP
    l_error_cnt := l_error_cnt + 1;
    fnd_file.PUT_LINE(fnd_file.OUTPUT, RPAD( sumry_rec.process_id,20,' ' )||'|' ||
    RPAD( sumry_rec.invoice_num,20,' ' )||'|' ||
    RPAD( sumry_rec.legacy_supp_number,20,' ' )||'|' ||
    RPAD( sumry_rec.invoice_amount,20,' ' )||'|'||
    RPAD( sumry_rec.operating_unit,20,' ' )||'|' ||
    RPAD( sumry_rec.batch_name,20,' ' )||'|' ||
    RPAD( sumry_rec.error_message,1000,' ' )
    );
    --fnd_file.put_line(fnd_file.output,RPAD( '-',160,'-'));
    l_line_cnt :=0;
    SELECT count(*)
    INTO l_line_cnt
    FROM kvap_inv_lines_stg kihs
    WHERE 1=1
    AND Process_id=sum_rec.process_id
    AND invoice_num=sumry_rec.invoice_num;
    If l_error_cnt =1 and l_line_cnt >0 Then
    fnd_file.put_line(fnd_file.output,Null);
    fnd_file.PUT_LINE(fnd_file.OUTPUT, RPAD( 'Invoice Number',20,' ' )||'|' ||
    RPAD( 'Line Number',20,' ' )||'|' ||
    RPAD( 'Quantity',20,' ' )||'|' ||
    RPAD( 'Amount',20,' ' )||'|' ||
    RPAD( 'Legacy Po Number',20,' ' )||'|' ||
    RPAD( 'Account',40,' ' )||'|' ||
    RPAD( 'Operating Unit',20,' ' )||'|' ||
    RPAD( 'Error Details',20,' ' )
    );
    fnd_file.put_line(fnd_file.output,RPAD( '-',160,'-'));
    End If;
    l_error_line_cnt := 0;
    For sumry_line_rec IN kv_inv_sum_cur8(sum_rec.process_id,sumry_rec.invoice_num) loop
    l_error_line_cnt := l_error_line_cnt +1;
    fnd_file.PUT_LINE(fnd_file.OUTPUT,
    RPAD( sumry_line_rec.invoice_num,20,' ' )||'|' ||
    RPAD( sumry_line_rec.line_number,20,' ' )||'|' ||
    RPAD( sumry_line_rec.quantity_invoiced,20,' ' )||'|' ||
    RPAD( sumry_line_rec.amount,20,' ' )||'|' ||
    RPAD( sumry_line_rec.LEGACY_PO_NUM,20,' ' )||'|' ||
    RPAD( sumry_line_rec.ACCOUNT,40,' ' )||'|' ||
    RPAD( sumry_line_rec.operating_unit,20,' ' )||'|' ||
    RPAD( sumry_line_rec.error_message,1000,' ' )
    );
    End Loop;
    If l_error_line_cnt = 0 and l_line_cnt >0 Then
    fnd_file.put_line(fnd_file.output,'No Error in Lines');
    End If;
    END LOOP;
    END LOOP; ---kv_inv_cur6
    ----------------------------------------------------------------------
    END IF; -- p_run_type
    END;
    END kvap_inv_pkg;
    /