El Blogcito de Ricardo Dufoo

Archivo del Autor

Consulta para conocer el histórico de ejecuciones de los programas concurrentes en Oracle EBS

por en Sep.20, 2012, archivado en EBS, Oracle

SELECT DISTINCT
fcpt.user_concurrent_program_name,
frg.request_group_name,
fcp.concurrent_program_name,
fat.application_name,
fa.application_short_name,
fa.basepath,
fcpoi.last_run_date,
fcpoi.last_run_request_id,
fcpoi.outcome,
fcpoi.successful_completion,
fcpoi.warning_completion,
fcpoi.error_completion,
fcpoi.min_run_time,
fcpoi.max_run_time,
fcpoi.avg_run_time
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_request_groups frg,
fnd_executables fe,
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_application fa,
fnd_conc_prog_onsite_info fcpoi
WHERE 1 = 1
AND fcpoi.concurrent_program_id = fcp.concurrent_program_id
AND fat.application_id = frgu.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id = fr.request_group_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id

Escribir un Comentario mas...

Procedimiento para crear cuentas contables desde PL/SQL

por en Feb.13, 2012, archivado en EBS, Oracle

Muchas veces es necesario crear cuentas contables desde PL/SQL porque las interfaces estandard y APIS de Oracle EBS no generan cuentas contables de manera automática. A continuación una función genérica que nos puede aydar a realizar esta tarea.

FUNCTION create_ccid (p_concat_segs IN VARCHAR2)
RETURN VARCHAR2
IS
-- pragma autonomous_transaction; -- if you need autonomy!
l_keyval_status BOOLEAN;
l_coa_id NUMBER;
BEGIN
BEGIN
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID'
);
DBMS_OUTPUT.put_line
('Try setting up your environment with fnd_global.apps_initialize'
);
RAISE;
END;

-- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
-- create will only work if dynamic inserts on and cross validation rules not broken
l_keyval_status :=
fnd_flex_keyval.validate_segs ('CREATE_COMBINATION',
'SQLGL',
'GL#',
l_coa_id,
p_concat_segs,
'V',
SYSDATE,
'ALL',
NULL,
NULL,
NULL,
NULL,
FALSE,
FALSE,
NULL,
NULL,
NULL
);

IF l_keyval_status
THEN
RETURN 'S';
ELSE
RETURN 'F';
END IF;
END create_ccid;

Escribir un Comentario :, , mas...

Cambiar la contraseña de un usuario de Oracle EBS desde la Base de datos

por en Nov.15, 2011, archivado en EBS, Oracle

Si se necesita modificar por cualquier motivo la contraseña de algún usuario de Oracle EBS desde la base de datos, es posible usando el siguiente código:
DECLARE
v_pwdchng BOOLEAN;
BEGIN
v_pwdchng := fnd_user_pkg.ChangePassword('USER','PASSWORD'); END;


Se puede validar el cambio con esto.

Escribir un Comentario mas...

Objetos Bloqueados en Oracle

por en Nov.10, 2011, archivado en Oracle

Con la siguiente consulta podemos obtener el listado de los objetos bloqueados en la base de datos Oracle:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;

2 Comentarios : mas...

Obtener estado de validación de facturas de AP

por en May.09, 2011, archivado en EBS, Oracle

Como bien es sabido, el estado de validación de las facturas de AP no se guarda directamente en una tabla y no existe un API para hacer una cosulta de este dato directamente. Navegando por internet, me topé con la siguiente función:

FUNCTION get_invoice_validation_status(l_invoice_id IN NUMBER) RETURN VARCHAR2
IS
l_status VARCHAR2(50);
l_validation_status VARCHAR2(50);
l_inv_sel_count NUMBER;
l_step VARCHAR2(10);

l_NLS_TRANSLATION varchar2(15) := ‘NLS TRANSLATION’;
l_QUICKPRO varchar2(8) := ‘QUICKPRO’;
l_POSTING_STATUS varchar2(14) := ‘POSTING STATUS’;
l_P varchar2(1) := ‘P’;
l_S varchar2(1) := ‘S’;
l_ANY_MULTIPLE varchar2(12) := ‘ANY MULTIPLE’;
l_UNMATCHED varchar2(9) := ‘UNMATCHED’;
l_SYSTEM varchar2(6) := ‘SYSTEM’;
l_APPROVED varchar2(8) := ‘APPROVED’;
l_CANCELLED varchar2(9) := ‘CANCELLED’;
l_NEEDS_REAPPROVAL varchar2(16) := ‘NEEDS REAPPROVAL’;
l_NEVER_APPROVED varchar2(14) := ‘NEVER APPROVED’;
l_INVOICE_DISTRIBUTION_TYPE varchar2(25) := ‘INVOICE DISTRIBUTION TYPE’;
l_ITEM varchar2(4) := ‘ITEM’;
l_INVOICE_TYPE varchar2(12) := ‘INVOICE TYPE’;
l_STANDARD varchar2(8) := ‘STANDARD’;
l_MISC_PHRASES varchar2(12) := ‘MISC PHRASES’;
l_B varchar2(1) := ‘B’;
l_SELECTED_FOR_PAYMENT varchar2(20) := ‘SELECTED FOR PAYMENT’;

l_YES_NO_ALL varchar2(10) := ‘YES_NO_ALL’;
l_Y varchar2(1) := ‘Y’;
l_N varchar2(1) := ‘N’;

l_NLS_REPORT_PARAMETER varchar2(20) := ‘NLS REPORT PARAMETER’;
l_ALL varchar2(3) := ‘ALL’;
l_PREPAY_STATUS varchar2(13) := ‘PREPAY STATUS’;
l_AVAILABLE varchar2(9) := ‘AVAILABLE’;
l_FULL varchar2(4) := ‘FULL’;
l_UNAPPROVED varchar2(10) := ‘UNAPPROVED’;
l_UNPAID varchar2(6) := ‘UNPAID’;
l_PERMANENT varchar2(9) := ‘PERMANENT’;

l_AP_WFAPPROVAL_STATUS varchar2(20) := ‘AP_WFAPPROVAL_STATUS’;
l_NOT_REQUIRED varchar2(12) := ‘NOT REQUIRED’;
l_REQUIRED varchar2(8) := ‘REQUIRED’;
l_INITIATED varchar2(9) := ‘INITIATED’;
l_WFAPPROVED varchar2(10) := ‘WFAPPROVED’;
l_REJECTED varchar2(8) := ‘REJECTED’;
l_MANUALLY_APPROVED varchar2(17) := ‘MANUALLY APPROVED’;
l_NEEDS_WFREAPPROVAL varchar2(18) := ‘NEEDS WFREAPPROVAL’;

l_dummy fnd_lookup_values.meaning%TYPE;
l_nls_partial fnd_lookup_values.meaning%TYPE;
l_nls_selected fnd_lookup_values.meaning%TYPE;
l_nls_approved fnd_lookup_values.meaning%TYPE;
l_nls_cancelled fnd_lookup_values.meaning%TYPE;
l_nls_needs_reapproval fnd_lookup_values.meaning%TYPE;
l_nls_never_approved fnd_lookup_values.meaning%TYPE;
l_nls_selected_for_payment fnd_lookup_values.meaning%TYPE;
l_nls_all fnd_lookup_values.meaning%TYPE;
l_nls_available fnd_lookup_values.meaning%TYPE;
l_nls_full fnd_lookup_values.meaning%TYPE;
l_nls_unapproved fnd_lookup_values.meaning%TYPE;
l_nls_unpaid fnd_lookup_values.meaning%TYPE;
l_nls_permanent fnd_lookup_values.meaning%TYPE;
l_nls_not_required fnd_lookup_values.meaning%TYPE;
l_nls_required fnd_lookup_values.meaning%TYPE;
l_nls_initiated fnd_lookup_values.meaning%TYPE;
l_nls_wfapproved fnd_lookup_values.meaning%TYPE;
l_nls_rejected fnd_lookup_values.meaning%TYPE;
l_nls_manually_approved fnd_lookup_values.meaning%TYPE;
l_nls_wf_needs_reapproval fnd_lookup_values.meaning%TYPE;

CURSOR c_invoices is
SELECT i.invoice_id
,i.invoice_amount
,i.payment_status_flag
,i.invoice_type_lookup_code, i.invoice_num
FROM ap_invoices_all i
WHERE invoice_id = l_invoice_id;

BEGIN
l_step := ‘1’;
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field,
l13.displayed_field,
l8.displayed_field,
l9.displayed_field,
l10.displayed_field,
l11.displayed_field,
l12.displayed_field,
l14.displayed_field
INTO l_dummy, — l_quickpro_prefix,
l_nls_partial,
l_nls_selected,
l_dummy, — l_nls_multiple,
l_dummy, — l_nls_unmatched,
l_dummy, — l_nls_system,
l_nls_approved,
l_nls_cancelled,
l_nls_needs_reapproval,
l_nls_never_approved,
l_dummy, — l_nls_item,
l_dummy, — l_nls_standard,
l_dummy, — l_nls_not_applicable,
l_nls_selected_for_payment
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7,
ap_lookup_codes l8,
ap_lookup_codes l9,
ap_lookup_codes l10,
ap_lookup_codes l11,
ap_lookup_codes l12,
ap_lookup_codes l13,
ap_lookup_codes l14
WHERE l1.lookup_type = l_NLS_TRANSLATION
AND l1.lookup_code = l_QUICKPRO
AND l2.lookup_type = l_POSTING_STATUS
AND l2.lookup_code = l_P
AND l3.lookup_type = l_POSTING_STATUS
AND l3.lookup_code = l_S
AND l4.lookup_type = l_NLS_TRANSLATION
AND l4.lookup_code = l_ANY_MULTIPLE
AND l5.lookup_type = l_NLS_TRANSLATION
AND l5.lookup_code = l_UNMATCHED
AND l6.lookup_type = l_NLS_TRANSLATION
AND l6.lookup_code = l_SYSTEM
AND l7.lookup_type = l_NLS_TRANSLATION
AND l7.lookup_code = l_APPROVED
AND l13.lookup_type = l_NLS_TRANSLATION
AND l13.lookup_code = l_CANCELLED
AND l8.lookup_type = l_NLS_TRANSLATION
AND l8.lookup_code = l_NEEDS_REAPPROVAL
AND l9.lookup_type = l_NLS_TRANSLATION
AND l9.lookup_code = l_NEVER_APPROVED
AND l10.lookup_type = l_INVOICE_DISTRIBUTION_TYPE
AND l10.lookup_code = l_ITEM
AND l11.lookup_type = l_INVOICE_TYPE
AND l11.lookup_code = l_STANDARD
AND l12.lookup_type = l_MISC_PHRASES
AND l12.lookup_code = l_B
AND l14.lookup_type = l_NLS_TRANSLATION
AND l14.lookup_code = l_SELECTED_FOR_PAYMENT;
l_step := ‘2’;
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field
INTO l_nls_all,
l_nls_available,
l_nls_full,
l_nls_unapproved,
l_nls_unpaid,
l_nls_permanent
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6
WHERE l1.lookup_type = l_NLS_REPORT_PARAMETER
AND l1.lookup_code = l_ALL
AND l2.lookup_type = l_PREPAY_STATUS
AND l2.lookup_code = l_AVAILABLE
AND l3.lookup_type = l_PREPAY_STATUS
AND l3.lookup_code = l_FULL
AND l4.lookup_type = l_PREPAY_STATUS
AND l4.lookup_code = l_UNAPPROVED
AND l5.lookup_type = l_PREPAY_STATUS
AND l5.lookup_code = l_UNPAID
AND l6.lookup_type = l_PREPAY_STATUS
AND l6.lookup_code = l_PERMANENT;

l_step := ‘3’;
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field
INTO l_nls_not_required,
l_nls_required,
l_nls_initiated,
l_nls_wfapproved,
l_nls_rejected,
l_nls_manually_approved,
l_nls_wf_needs_reapproval
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7
WHERE l1.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l1.lookup_code = l_NOT_REQUIRED
AND l2.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l2.lookup_code = l_REQUIRED
AND l3.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l3.lookup_code = l_INITIATED
AND l4.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l4.lookup_code = l_WFAPPROVED
AND l5.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l5.lookup_code = l_REJECTED
AND l6.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l6.lookup_code = l_MANUALLY_APPROVED
AND l7.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l7.lookup_code = l_NEEDS_WFREAPPROVAL;

FOR r in c_invoices loop
l_step := ‘4’;
l_status := AP_INVOICES_PKG.GET_APPROVAL_STATUS(r.INVOICE_ID
,r.INVOICE_AMOUNT
,r.PAYMENT_STATUS_FLAG
,r.INVOICE_TYPE_LOOKUP_CODE );
l_step := ‘5’;
–dbms_output.put_line(r.invoice_num || ‘ – ‘ || l_status);

if (l_status =
‘NEVER APPROVED’) then
l_validation_status :=
l_nls_never_approved;
elsif (l_status = ‘APPROVED’) then
l_validation_status := l_nls_approved;
elsif (l_status = ‘CANCELLED’) then
l_validation_status := l_nls_cancelled;
elsif (l_status =
‘NEEDS REAPPROVAL’) then
l_validation_status :=
l_nls_needs_reapproval;
elsif (l_status = ‘AVAILABLE’) then
l_validation_status := l_nls_available;
elsif (l_status = ‘FULL’) then
l_validation_status := l_nls_full;
elsif (l_status = ‘UNAPPROVED’) then
l_validation_status := l_nls_unapproved;
elsif (l_status = ‘UNPAID’) then
l_validation_status := l_nls_unpaid;
elsif (l_status = ‘PERMANENT’) then
l_validation_status := l_nls_permanent;
elsif (l_status = ‘NOT REQUIRED’) then
l_validation_status := l_nls_not_required;
elsif (l_status = ‘REQUIRED’) then
l_validation_status := l_nls_required;
elsif (l_status = ‘INITIATED’) then
l_validation_status := l_nls_initiated;
elsif (l_status = ‘WFAPPROVED’) then
l_validation_status := l_nls_wfapproved;
elsif (l_status = ‘REJECTED’) then
l_validation_status := l_nls_rejected;
elsif (l_status = ‘MANUALLY APPROVED’) then
l_validation_status := l_nls_manually_approved;
elsif (l_status = ‘NEEDS WFREAPPROVAL’) then
l_validation_status := l_nls_wf_needs_reapproval;
end if;

— Added for Payment Process Enhancements.
if (l_status
IN (‘APPROVED’,’UNPAID’)) then
SELECT count(*)
INTO l_inv_sel_count
FROM ap_payment_schedules
WHERE invoice_id = r.invoice_id
AND checkrun_id IS NOT NULL
AND rownum =1; –bug5691219

if (l_inv_sel_count > 0) then
l_validation_status :=
l_nls_selected_for_payment;
end if;
end if;

–dbms_output.put_line(r.invoice_num || ‘ – ‘ || l_validation_status);

END LOOP;
RETURN(l_validation_status);

EXCEPTION
WHEN OTHERS THEN
RETURN (‘l_step = ‘ || l_step || ‘ ‘ || SQLERRM);
END get_invoice_validation_status;

Esta función se puede meter directamente en un query y con esta podemos fácilmente encontrar el estado de validación de una factura de AP.

Escribir un Comentario mas...

Busca algo?

Escriba una palabra para buscar en el sitio:

Todavia no encontrando lo que usted esta buscando? Deje un comentario y entraremos en contacto con ud, para poderlo ayudar!

Visita a nuestros amigos!

Amigos recomendados...

Archivos

Entradas, ordenadas por mes...