Oracle EBS - How to Enable Debug log on user's session

  1) As System Administrator responsibility

       Navigation: Profile -> System

           a) In the FIND window,

                  Enter User Name: User submitting the request
                  Search Profile: FND: Diag%

           b) Set the following profile option at User level:
                  FND: Diagnostics -> Yes

           c) Click the FIND flashlight and search Profile: FND: Debug%

               Set the following profile options at USER level:

                  FND: Debug Log Enabled =  Yes
                  FND: Debug Log Module = %
                  FND: Debug Log Level = Statement

                  FND: Debug Log Mode = Asynchronous with Cross-Tier Sequencing (SITE level)

  2) Run the following query to get the current log sequence
          
      SELECT MAX(log_sequence)
      FROM FND_LOG_MESSAGES;
    
  3) Perform the action that is giving problems.

  4) Run the following query to obtain the FND Messages while replicating the error in step 3:

      SELECT *
      FROM fnd_log_messages
      WHERE log_sequence > &max_log_sequence_from_step_2
      ORDER by log_sequence;

  5) Upload to the SR results of step #4 in an EXCEL spreadsheet, formatted with column headings for readability.

   6) After completion, reset step 1c "FND: Debug Log Enabled" back to "No" to avoid impact on performance.

Oracle EBS - Script to Update the Purchase Order Buyer using Oracle Pubic API

Requirement:

Need script which can update buyer of standard purchase order. This is pretty much useful when buyer of current purchase has to be changed due to various business reasons like buyer left organization or moving to another department 

PLSQL:

SET SERVEROUTPUT ON;

DECLARE

  lv_msg_data          VARCHAR2(100);

  ln_msg_count         NUMBER;

  lv_return_status     VARCHAR2(1); 

  ln_org_id            NUMBER;

  ln_vendor_id         NUMBER;

  ln_changed_buyer_id  NUMBER;

  ln_current_buyer_id  NUMBER;

  ln_new_buyer_id      NUMBER DEFAULT '6148';

  ln_valid_buyer       NUMBER;

  lv_document_no       VARCHAR2(200) DEFAULT '600338'; 

  le_custom_exception  EXCEPTION;

BEGIN 

  -- check whether the given person is a valid buyer

  SELECT COUNT(1)

    INTO ln_valid_buyer

    FROM po_buyers_val_v

   WHERE employee_id = ln_new_buyer_id;

   

  IF ln_valid_buyer = 0

  THEN

    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is not a valid buyer');

    RAISE le_custom_exception;

  END IF;

  SELECT org_id,

         vendor_id,

         agent_id        

    INTO ln_org_id,

         ln_vendor_id,

         ln_current_buyer_id

    FROM po_headers_all pha 

   WHERE pha.segment1 = lv_document_no;

  

  -- check whether the given person is already a buyer of the document

  IF ln_current_buyer_id = ln_new_buyer_id

  THEN

    DBMS_OUTPUT.PUT_LINE('Error: The "to person id" is already a buyer of document');

    RAISE le_custom_exception;

  END IF; 

  

  fnd_global.apps_initialize(user_id => 26406, 

                              resp_id => 50769, 

                              resp_appl_id => 201);

                            

  mo_global.init('PO'); 

  fnd_client_info.set_org_context(ln_org_id);

  mo_global.set_policy_context ('S', ln_org_id);

  DBMS_OUTPUT.PUT_LINE('--API Call--');

  PO_MASS_UPDATE_PO_GRP.UPDATE_PERSONS

           (

            p_update_person       => 'BUYER',

            p_old_personid        => ln_current_buyer_id,

            p_new_personid        => ln_new_buyer_id,

            p_document_type       => 'STANDARD',

            p_document_no_from    => lv_document_no,

            p_document_no_to      => lv_document_no,

            p_date_from           => NULL,

            p_date_to             => NULL,

            p_supplier_id         => NULL,

            p_include_close_po    => 'YES',

            p_commit_interval     => 1,

            p_msg_data            => lv_msg_data,

            p_msg_count           => ln_msg_count,

            p_return_status       => lv_return_status

          );

          

   SELECT agent_id 

     INTO ln_changed_buyer_id

     FROM po_headers_all pha 

    WHERE pha.segment1 = lv_document_no;

  

  IF ln_new_buyer_id <> ln_changed_buyer_id

  THEN 

    DBMS_OUTPUT.PUT_LINE('msg_data'|| lv_msg_data);

    DBMS_OUTPUT.PUT_LINE('msg_count'|| ln_msg_count);

    DBMS_OUTPUT.PUT_LINE('return_status'|| lv_return_status);                            

  ELSE 

    DBMS_OUTPUT.PUT_LINE('Successfully Updated');

  END IF;

EXCEPTION 

  WHEN le_custom_exception THEN 

    DBMS_OUTPUT.PUT_LINE('Custom Error. Hence, action Aborted');

  WHEN OTHERS THEN 

    DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);

END; 

Oracle EBS - SQL Query to get the Purchase Requisition Number List which we need to Delegate to other user while employee is on leave

 SELECT

    segment1 pr,
    wf_item_key,
    wf_item_type,
    authorization_status
FROM
    apps.po_requisition_headers_all
WHERE
    authorization_status NOT IN ( 'APPROVED', 'CANCELLED' )
    AND requisition_header_id IN (
        SELECT
            object_id
        FROM
            apps.po_action_history pah, apps.per_all_people_f  ppf, apps.fnd_user          fu
        WHERE
                1 = 1
            AND pah.object_type_code = 'REQUISITION'
            AND pah.action_code IS NULL
            AND pah.employee_id = fu.employee_id
            AND nvl(fu.end_date, sysdate) >= sysdate
            AND fu.employee_id = ppf.person_id
            AND ppf.effective_end_date > sysdate
--and pah.employee_id=18581
            AND ppf.full_name LIKE '%Lien%Fung%' --user full name who is on leave
    );