create or replace package apps.pkg_ap_open_invoice_import as procedure p_validate_aii(errbuf out varchar2, retcode out number); end pkg_ap_open_invoice_import; / CREATE OR REPLACE package body apps.pkg_ap_open_invoice_import as procedure p_validate_aii(errbuf out varchar2, retcode out number) is cursor c_aii is select * from apps.ap_invoices_interface_stg; cursor c_aili(v_inv_id ap_invoices_interface.invoice_id%type) is select * from ap_invoice_lines_interface_stg where invoice_id = v_inv_id; v_vendor_id AP_VENDORS_V.vendor_id%type; v_currency_cd FND_CURRENCIES.currency_code%type; v_lookup_code1 FND_LOOKUP_VALUES_VL.lookup_code%type; v_lookup_code2 ap_lookup_codes_old.lookup_code%type; v_CODE_COMBINATION_ID GL_CODE_COMBINATIONS_KFV.CODE_COMBINATION_ID%type; v_status_flg char(1); begin -- **************************************************************************** -- header -- **************************************************************************** for v_aii_rec in c_aii loop v_status_flg := 'Y'; -- ------------------------------------------- -- Check Supplier ID -- ------------------------------------------- begin select vendor_id into v_vendor_id from AP_VENDORS_V where vendor_id = v_aii_rec.vendor_id; exception when no_data_found then --dbms_output.put_line('Invalid Supplier ID'); fnd_file.put_line(fnd_file.log, 'Invalid Supplier ID'); v_status_flg := 'N'; end; -- ------------------------------------------- -- Check Invoice Amt (for STANDARD Invoices only) -- ------------------------------------------- -- ------------------------------------------- -- Check Currency Code -- ------------------------------------------- begin select currency_code into v_currency_cd from FND_CURRENCIES where currency_code = v_aii_rec.INVOICE_CURRENCY_CODE; exception when no_data_found then --dbms_output.put_line('Invalid Currency Code'); fnd_file.put_line(fnd_file.log, 'Invalid Currency Code'); v_status_flg := 'N'; end; /************************************************ -- ------------------------------------------- -- Check Source -- ------------------------------------------- begin SELECT DISTINCT LOOKUP_CODE into v_lookup_code1 FROM FND_LOOKUP_VALUES_VL WHERE LOOKUP_TYPE='SOURCE' and lookup_code = v_aii_rec.SOURCE; exception when no_data_found then --dbms_output.put_line('Invalid SOURCE'); fnd_file.put_line(fnd_file.log, 'Invalid Source'); v_status_flg := 'N'; end; ************************************************/ --dbms_output.put_line('invoice_id '||v_aii_rec.INVOICE_ID); insert into ap_invoices_interface(INVOICE_ID , INVOICE_NUM , INVOICE_TYPE_LOOKUP_CODE , INVOICE_DATE , VENDOR_ID , VENDOR_SITE_ID , INVOICE_AMOUNT , INVOICE_CURRENCY_CODE , EXCHANGE_RATE , EXCHANGE_DATE , TERMS_ID , DESCRIPTION , CREATION_DATE , CREATED_BY , STATUS , SOURCE , GROUP_ID , REQUEST_ID , GOODS_RECEIVED_DATE , INVOICE_RECEIVED_DATE , GL_DATE , PREPAY_GL_DATE , REQUESTER_ID) values(v_aii_rec.INVOICE_ID , v_aii_rec.INVOICE_NUM , v_aii_rec.INVOICE_TYPE_LOOKUP_CODE , v_aii_rec.INVOICE_DATE , v_aii_rec.VENDOR_ID , v_aii_rec.VENDOR_SITE_ID , v_aii_rec.INVOICE_AMOUNT , v_aii_rec.INVOICE_CURRENCY_CODE , v_aii_rec.EXCHANGE_RATE , v_aii_rec.EXCHANGE_DATE , v_aii_rec.TERMS_ID , v_aii_rec.DESCRIPTION , v_aii_rec.CREATION_DATE , v_aii_rec.CREATED_BY , v_aii_rec.STATUS , v_aii_rec.SOURCE , v_aii_rec.GROUP_ID , v_aii_rec.REQUEST_ID , v_aii_rec.GOODS_RECEIVED_DATE , v_aii_rec.INVOICE_RECEIVED_DATE , v_aii_rec.GL_DATE , v_aii_rec.PREPAY_GL_DATE , v_aii_rec.REQUESTER_ID); --dbms_output.put_line('Header details Inserted ...'); fnd_file.put_line(fnd_file.log, 'Header details Inserted ...'); -- **************************************************************************** -- lines table -- **************************************************************************** for rec in c_aili(v_aii_rec.invoice_id) loop -- ------------------------------------------- -- Check Line Type Lookup Codes -- ------------------------------------------- begin select lookup_code into v_lookup_code2 from ap_lookup_codes_old where lookup_type = 'INVOICE DISTRIBUTION TYPE' and lookup_code = rec.LINE_TYPE_LOOKUP_CODE; exception when no_data_found then --dbms_output.put_line('Invalid Line Type Lookup code'); fnd_file.put_line(fnd_file.log, 'Invalid Line type Lookup code'); v_status_flg := 'N'; end; -- ------------------------------------------- -- Check Distribution code combination -- ------------------------------------------- begin select CODE_COMBINATION_ID into v_CODE_COMBINATION_ID from GL_CODE_COMBINATIONS_KFV where CODE_COMBINATION_ID = rec.DIST_CODE_COMBINATION_ID and ENABLED_FLAG ='Y'; exception when no_data_found then --dbms_output.put_line('Invalid Distribution code combination'); fnd_file.put_line(fnd_file.log, 'Invalid Distribution code combination'); v_status_flg := 'N'; end; if v_status_flg = 'Y' then insert into ap_invoice_lines_interface(INVOICE_ID , INVOICE_LINE_ID , LINE_NUMBER , LINE_TYPE_LOOKUP_CODE , AMOUNT , ACCOUNTING_DATE , DESCRIPTION , TAX_CODE , PO_HEADER_ID , PO_LINE_ID , PO_LINE_LOCATION_ID , PO_DISTRIBUTION_ID , QUANTITY_INVOICED , UNIT_PRICE , ASSETS_TRACKING_FLAG , EXTERNAL_DOC_LINE_REF , DISTRIBUTION_SET_ID , DISTRIBUTION_SET_NAME , DIST_CODE_CONCATENATED , DIST_CODE_COMBINATION_ID) VALUES(rec.INVOICE_ID , rec.INVOICE_LINE_ID , rec. LINE_NUMBER , rec. LINE_TYPE_LOOKUP_CODE , rec. AMOUNT , rec. ACCOUNTING_DATE , rec. DESCRIPTION , rec. TAX_CODE , rec. PO_HEADER_ID , rec. PO_LINE_ID , rec. PO_LINE_LOCATION_ID , rec. PO_DISTRIBUTION_ID , rec. QUANTITY_INVOICED , rec. UNIT_PRICE , rec. ASSETS_TRACKING_FLAG , rec. EXTERNAL_DOC_LINE_REF , rec.DISTRIBUTION_SET_ID , rec.DISTRIBUTION_SET_NAME , rec.DIST_CODE_CONCATENATED , rec.DIST_CODE_COMBINATION_ID); --dbms_output.put_line('Lines details Inserted ...'); fnd_file.put_line(fnd_file.log, 'Line details Inserted...'); end if; end loop; -- lines table end loop; -- header table -- ------------------------------------------- -- Insert into Interface Tables -- ------------------------------------------- if v_status_flg = 'Y' then --commit; fnd_file.put_line(fnd_file.log, 'Successfull Commit ...'); else rollback; end if; end p_validate_aii; end pkg_ap_open_invoice_import ; /