Friday, December 30, 2011

Fnd attachments in oracle apps (Graphical Representation)



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

Concurrent Request Details in Oracle Apps R12 - Query


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

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.