Friday, December 30, 2011
Below diagram will give you the details of the tables involved in attachment functionality of Oracle Applications and its diagrammatic representation with an example.
Query:
SELECT t.short_text
FROM fnd_documents_short_text t,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_document_entities_tl det
WHERE 1
= 1
AND d.media_id = t.media_id
AND ad.document_id = d.document_id
AND dct.category_id = d.category_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND ad.entity_name = det.data_object_code
AND det.LANGUAGE = USERENV ('LANG')
AND dct.LANGUAGE = USERENV ('LANG')
AND det.user_entity_name = 'RA_CUSTOMER_TRX'
AND dcu.enabled_flag = 'Y'
AND dct.user_name = 'AR Header Note - Bottom'
AND af.function_name = 'ARXTWMAI'
AND ad.pk1_value = :customer_trx_id;
For more details:
Friday, December 30, 2011 by Team search · 0
Below query will gather
details like Responsibility name, Application details, Submitted User,
Concurrent manager completion status and text. Etc…
I hope it will be helpful to
you..
SELECT r.request_id,
DECODE (r.description,
NULL, pt.user_concurrent_program_name,
r.description || ' ( ' || pt.user_concurrent_program_name
|| ' ) '
) program,
r.phase_code,
r.status_code,
fnd_amp_private.get_phase (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) phase,
fnd_amp_private.get_status (r.phase_code,
r.status_code,
r.hold_flag,
p.enabled_flag,
r.requested_start_date,
r.request_id
) status,
u.user_name,
rt.responsibility_name,
AT.application_name,
r.requested_start_date,
r.actual_start_date,
r.actual_completion_date,
r.request_date,
r.argument_text,
r.oracle_session_id,
r.completion_text,
r.controlling_manager,
r.requested_by,
r.program_application_id,
r.concurrent_program_id,
r.is_sub_request,
r.parent_request_id,
r.queue_method_code,
r.cd_id,
r.hold_flag,
p.enabled_flag,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.os_process_id,
r.nls_language,
r.nls_territory,
r.nls_numeric_characters,
r.description
FROM fnd_concurrent_programs_tl pt,
fnd_responsibility_tl rt,
fnd_application_tl AT,
fnd_concurrent_programs_vl p,
fnd_user u,
fnd_concurrent_requests r
WHERE r.program_application_id = p.application_id
AND r.concurrent_program_id = p.concurrent_program_id
AND pt.concurrent_program_id = p.concurrent_program_id
AND pt.application_id = p.application_id
AND pt.LANGUAGE = USERENV ('LANG')
AND u.user_id = r.requested_by
AND rt.application_id = r.responsibility_application_id
AND rt.responsibility_id = r.responsibility_id
AND rt.LANGUAGE = USERENV ('LANG')
AND AT.application_id = r.program_application_id
AND AT.LANGUAGE = USERENV ('LANG')
AND
r.request_id =
:request_id;
by Team search · 0
Friday, December 9, 2011
API to get the formatted contact details of a Party in oracle apps R12 (HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY)
Below script will help you to fetch the formatted primary
phone number of a Party.
Test Instance: R12
API Used: HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY
Script:
SET serveroutput on;
DECLARE
v_party_id NUMBER := 81251; -- Party id
v_telno VARCHAR2 (100);
v_faxno VARCHAR2 (100);
v_contact_id NUMBER;
v_status VARCHAR2 (100);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (100);
BEGIN
SELECT contact_point_id
INTO v_contact_id
FROM hz_contact_points hcp, hz_parties hp
WHERE 1 = 1
AND hcp.owner_table_name
= 'HZ_PARTIES'
AND hcp.owner_table_id
= hp.party_id
AND hcp.contact_point_type
= 'PHONE'
AND hcp.status = 'A'
AND hcp.phone_line_type
= 'GEN'
AND hp.party_id = v_party_id
AND hcp.primary_flag
= 'Y'
AND ROWNUM = 1;
BEGIN
hz_format_phone_v2pub.phone_display
(p_init_msg_list
=> 'T',
p_contact_point_id => v_contact_id,
x_formatted_phone_number => v_faxno,
x_return_status => v_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('x_msg_data ' || v_msg_data);
DBMS_OUTPUT.put_line ('x_msg_count ' || v_msg_count);
END;
DBMS_OUTPUT.put_line ('FAX NUMBER:
' || v_faxno);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('NO
TELEPHONE NUMBER --> OTHER EXCEPTION ');
END;
Friday, December 9, 2011 by Team search · 1
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.