Sunday, August 17, 2014
In my recent project, we have a requirement
to account the payment of the AP Invoices via PLSQL.
Hence, We have used the API
AP_DRILLDOWN_PUB_PKG.PAYMENT_ONLINE_ACCOUNTING
Script:
---------------------------------------------------------------------- -- PROCEDURE account_payments -- This procedure is to create Accounting for payments of the -- invoices in AP ---------------------------------------------------------------------- PROCEDURE account_payments (pin_invoice_id IN NUMBER, pov_err_message OUT VARCHAR2 ) IS CURSOR cur_imp_invoices IS SELECT DISTINCT aca.check_id, aca.check_number FROM ap_invoices_all xlpi, ap_invoice_payments_all aipa, ap_checks_all aca WHERE xlpi.invoice_id = aipa.invoice_id AND xlpi.invoice_id = pin_invoice_id AND aipa.check_id = aca.check_id AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';
-- This condition is to pick invoices which are paid and unaccounted ln_processed_cnt NUMBER DEFAULT 0; ln_failed_cnt NUMBER DEFAULT 0; lv_error_buf VARCHAR2(500); ln_retcode NUMBER; BEGIN FOR rec_imp_inv IN cur_imp_invoices LOOP BEGIN DBMS_OUTPUT.put_line(rec_imp_inv.check_id); ln_retcode := NULL; lv_error_buf := NULL; ap_drilldown_pub_pkg.payment_online_accounting ( p_check_id => rec_imp_inv.check_id, p_accounting_mode => 'F', p_errbuf => lv_error_buf, p_retcode => ln_retcode, p_calling_sequence => 'xxyour_package.account_payments' ); IF ln_retcode = 0 THEN DBMS_OUTPUT.put_line('Invoice Check id: ' ||rec_imp_inv.check_id ||'. Payment (' ||rec_imp_inv.check_number ||') Accounted Sucessfully' ); ln_processed_cnt := ln_processed_cnt+1; ELSIF ln_retcode = 1 THEN DBMS_OUTPUT.put_line('Invoice Check id: ' ||rec_imp_inv.check_id ||'. Payment (' ||rec_imp_inv.check_number ||') Accounting ended in WARNING. Errbuf : ' ||lv_error_buf ); ln_processed_cnt := ln_processed_cnt+1; ELSIF ln_retcode = 2 THEN DBMS_OUTPUT.put_line('Invoice Check id: ' ||rec_imp_inv.check_id ||'. Payment (' ||rec_imp_inv.check_number ||') Accounting ended in ERROR. Errbuf : ' ||lv_error_buf ); ln_failed_cnt := ln_failed_cnt +1; ELSE DBMS_OUTPUT.put_line('Invoice Check id: ' ||rec_imp_inv.check_id ||'. Payment (' ||rec_imp_inv.check_number ||') Accounting ended in ERROR. Errbuf : ' ||lv_error_buf ||' Retcode: ' ||ln_retcode ); ln_failed_cnt := ln_failed_cnt +1; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Invoice Check id: '
||rec_imp_inv.check_id
||'. Payment Accounting failed with unhandled exception.Error:'
||SQLERRM); ln_failed_cnt := ln_failed_cnt + 1; END; END LOOP; pov_err_message := 'PROCESSED: '||ln_processed_cnt||' FAILED: '||ln_failed_cnt; END account_payments;
Hope this helps!!!
Sunday, August 17, 2014 by Team search · 2
In my recent project, we have a requirement
to account the AP Invoices via PLSQL.
Hence, We have used the API AP_DRILLDOWN_PUB_PKG.INVOICE_ONLINE_ACCOUNTING
Script:
---------------------------------------------------------------------- -- PROCEDURE account_imported_invoices -- This procedure is to create Accounting for imported invoices in AP ---------------------------------------------------------------------- PROCEDURE account_imported_invoices(pin_invoice_id IN NUMBER, pov_err_message OUT VARCHAR2 ) IS CURSOR cur_imp_invoices IS SELECT * FROM ap_invoices_all xlpi WHERE xlpi.invoice_id = pin_invoice_id AND ap_invoices_pkg.get_posting_status(xlpi.invoice_id) = 'N'; -- This condition is to pick invoices which are un accounted ln_processed_cnt NUMBER DEFAULT 0; ln_failed_cnt NUMBER DEFAULT 0; lv_error_buf VARCHAR2(500); ln_retcode NUMBER; BEGIN FOR rec_imp_inv IN cur_imp_invoices LOOP BEGIN ln_retcode := NULL; lv_error_buf := NULL; ap_drilldown_pub_pkg.invoice_online_accounting ( p_invoice_id => rec_imp_inv.invoice_id, p_accounting_mode => 'F', p_errbuf => lv_error_buf, p_retcode => ln_retcode, p_calling_sequence => 'XXyour_package.account_imported_invoices' ); IF ln_retcode = 0 THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
||'- Invoice Accounted Sucessfully'
); ln_processed_cnt := ln_processed_cnt+1; ELSIF ln_retcode = 1 THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
||'- Invoice Accounting ended in WARNING. Errbuf : ' ||lv_error_buf
); ln_processed_cnt := ln_processed_cnt+1; ELSIF ln_retcode = 2 THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
||'- Invoice Accounting ended in ERROR. Errbuf : ' ||lv_error_buf
); ln_failed_cnt := ln_failed_cnt +1; ELSE DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
||'- Invoice Accounting ended in ERROR. Errbuf : ' ||lv_error_buf
||' Retcode: '
||ln_retcode
); ln_failed_cnt := ln_failed_cnt +1; END IF; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num ||' Invoice Validation failed with unhandled exception.Error:' ||SQLERRM ); ln_failed_cnt := ln_failed_cnt + 1; END; END LOOP; pov_err_message := 'PROCESSED: '||ln_processed_cnt||' FAILED: '||ln_failed_cnt; END account_imported_invoices;
by Team search · 0
In my recent project, we have a requirement
to initiate the validation of the AP invoice via PLSQL. We have
used the private API AP_APPROVAL_PKG.BATCH_APPROVAL
Script:
---------------------------------------------------------------------- -- PROCEDURE validate_imported_invoices -- This procedure is to validate imported invoices in AP ---------------------------------------------------------------------- PROCEDURE validate_imported_invoices(pin_group_id IN NUMBER, pov_err_message OUT VARCHAR2 ) IS CURSOR cur_imp_invoices IS SELECT ai.invoice_id ap_invoice_id, ai.approval_iteration FROM ap_invoices_all ai WHERE ap_invoices_pkg.get_approval_status(ai.invoice_id, ai.invoice_amount, ai.payment_status_flag, ai.invoice_type_lookup_code ) NOT IN ( 'APPROVED','UNPAID'); -- This condition is pickup invoices eligible for validation only ln_processed_cnt NUMBER DEFAULT 0; ln_failed_cnt NUMBER DEFAULT 0; ln_holds_cnt NUMBER; lv_approval_status VARCHAR2(100); lv_funds_return_code VARCHAR2(100); BEGIN FOR rec_imp_inv IN cur_imp_invoices LOOP BEGIN lv_approval_status := NULL; lv_funds_return_code := NULL; ln_holds_cnt := NULL; IF (AP_APPROVAL_PKG.BATCH_APPROVAL (p_run_option => NULL, p_sob_id => 1234, p_inv_start_date => NULL, p_inv_end_date => NULL, p_inv_batch_id => NULL, p_vendor_id => NULL, p_pay_group => NULL, p_invoice_id => rec_imp_inv.invoice_id, p_entered_by => NULL, p_debug_switch => 'N', p_conc_request_id => fnd_profile.VALUE('CONC_REQUEST_ID'), p_commit_size => NULL, p_org_id => 123, p_report_holds_count => ln_holds_cnt, p_transaction_num => NULL)) THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num ||' Invoice Validated.Status(holds count) : ' ||ln_holds_cnt); ELSE DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num ||' Invoice Validation Failed '); END IF; ln_processed_cnt := ln_processed_cnt+1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num ||' Invoice Validation failed with unhandled exception.Error:'||SQLERRM); ln_failed_cnt := ln_failed_cnt + 1; END; END LOOP; pov_err_message := 'PROCESSED: '||ln_processed_cnt||' FAILED: '||ln_failed_cnt; END;
by Team search · 0
In my recent project, we have a requirement
to display the invoice Accounted status in a custom form. We have used the API AP_INVOICES_PKG.GET_POSTING_STATUS
Script:
------------------------------------------------------------------------- -- Function get_inv_accounted_status -- This function returns the accounting status of the given ap invoice id ------------------------------------------------------------------------- FUNCTION get_inv_accounted_status (pin_invoice_id IN NUMBER) RETURN VARCHAR2 IS lv_return_status VARCHAR2(2) DEFAULT NULL; BEGIN RETURN (ap_invoices_pkg.get_posting_status(pin_invoice_id)); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'N'; WHEN OTHERS THEN RETURN 'N'; END get_inv_accounted_status;
by Team search · 1
Sunday, August 10, 2014
In this post, i tried to create a Fixed asset via standard Oracle API FA_ADDITION_PUB.DO_ADDITION. I tested the script in Oracle Apps R12.
Hope this helps.
Script:
set serveroutput on; DECLARE l_trans_rec FA_API_TYPES.trans_rec_type; l_dist_trans_rec FA_API_TYPES.trans_rec_type; l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type; l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type; l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type; l_asset_type_rec FA_API_TYPES.asset_type_rec_type; l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type; l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type; l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type; l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type; l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type; l_inv_tbl FA_API_TYPES.inv_tbl_type; l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type; l_return_status VARCHAR2(1); l_mesg_count number; l_mesg varchar2(4000); BEGIN dbms_output.enable(10000000); FA_SRVR_MSG.Init_Server_Message; -- desc info -- l_asset_desc_rec.asset_number := '1234567'; l_asset_desc_rec.tag_number := 'TEAM12345-1'; l_asset_desc_rec.serial_number := 'TEAM3567-1'; l_asset_desc_rec.in_use_flag := 'YES'; l_asset_desc_rec.new_used := 'NEW'; l_asset_desc_rec.owned_leased := 'OWNED'; l_asset_desc_rec.current_units := 1; l_asset_desc_rec.description := 'Shareoracleapps Test Asset'; l_asset_desc_rec.asset_key_ccid := 1; -- cat info -- Valid Value in FA_CATEGORIES l_asset_cat_rec.category_id := '332'; --type info l_asset_type_rec.asset_type := 'CAPITALIZED'; -- Asset Financial Information -- l_asset_fin_rec.set_of_books_id := 2243; l_asset_fin_rec.date_placed_in_service := TO_DATE('01-JUN-2014','DD-MON-RRRR'); l_asset_fin_rec.deprn_start_date := TO_DATE('01-JUN-2014','DD-MON-RRRR'); l_asset_fin_rec.deprn_method_code := 'STL'; l_asset_fin_rec.life_in_months := 240; l_asset_fin_rec.original_cost := 50000; l_asset_fin_rec.cost := 50000; l_asset_fin_rec.prorate_convention_code := 'SAME MONTH'; l_asset_fin_rec.salvage_type := 'AMT'; -- PCT - for Percentage l_asset_fin_rec.salvage_value := 1000; l_asset_fin_rec.percent_salvage_value := NULL; l_asset_fin_rec.depreciate_flag := 'YES'; l_asset_fin_rec.orig_deprn_start_date := TO_DATE('01-AUG-2010','DD-MON-RRRR'); -- deprn info l_asset_deprn_rec.set_of_books_id := 2243; l_asset_deprn_rec.ytd_deprn := 20000; l_asset_deprn_rec.deprn_reserve := 20000; l_asset_deprn_rec.bonus_ytd_deprn := 0; l_asset_deprn_rec.bonus_deprn_reserve := 0; -- book / trans info -- Valid value in FA_BOOK_CONTROLS l_asset_hdr_rec.book_type_code := 'SHARE BOOK'; -- distribution info l_asset_dist_rec.units_assigned := 1; -- Valid Record from GL Code cominations with record type = 'E' (Expense) l_asset_dist_rec.expense_ccid := 12345; -- Valid Value in FA Locations l_asset_dist_rec.location_ccid := 1881098; l_asset_dist_rec.assigned_to := NULL; l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned; l_asset_dist_tbl(1) := l_asset_dist_rec; -- call the api fa_addition_pub.do_addition( -- std parameters p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_FALSE, p_validation_level => FND_API.G_VALID_LEVEL_FULL, p_calling_fn => null, x_return_status => l_return_status, x_msg_count => l_mesg_count, x_msg_data => l_mesg, -- api parameters px_trans_rec => l_trans_rec, px_dist_trans_rec => l_dist_trans_rec, px_asset_hdr_rec => l_asset_hdr_rec, px_asset_desc_rec => l_asset_desc_rec, px_asset_type_rec => l_asset_type_rec, px_asset_cat_rec => l_asset_cat_rec, px_asset_hierarchy_rec => l_asset_hierarchy_rec, px_asset_fin_rec => l_asset_fin_rec, px_asset_deprn_rec => l_asset_deprn_rec, px_asset_dist_tbl => l_asset_dist_tbl, px_inv_tbl => l_inv_tbl ); --dump messages l_mesg_count := fnd_msg_pub.count_msg; if l_mesg_count > 0 then l_mesg := chr(10) || substr(fnd_msg_pub.get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE), 1, 250); dbms_output.put_line(l_mesg); for i in 1..(l_mesg_count - 1) loop l_mesg := substr(fnd_msg_pub.get (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE), 1, 250); dbms_output.put_line(l_mesg); end loop; fnd_msg_pub.delete_msg(); end if; if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then dbms_output.put_line('FAILURE'); else dbms_output.put_line('SUCCESS'); dbms_output.put_line('ASSET_ID :' || to_char(l_asset_hdr_rec.asset_id)); dbms_output.put_line('ASSET_NUMBER :' || l_asset_desc_rec.asset_number); end if; end; /
Sunday, August 10, 2014 by Team search · 3
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.