Oracle Fusion - SQL Query to get User Need to Approve pending approval Purchase Requestion and Purchase Order Transaction Details

 SELECT

    'Requisition'                             type,

    prha.document_status                      approval_status,

    to_char(prha.creation_date, 'DD-MM-YYYY') po_date,

    prha.requisition_number                   requisition_number,

    replace(replace(prha.description,

                    CHR(13),

                    ''),

            CHR(10),

            '')                               pr_po_desc,

    (

        SELECT

            SUM((nvl(unit_price, amount) * nvl(quantity, 1)))

        FROM

            por_requisition_lines_all

        WHERE

            requisition_header_id = prha.requisition_header_id

    )                                         pr_po_amount,

    (

        SELECT

            prla.currency_code

        FROM

            por_requisition_lines_all prla

        WHERE

                prla.requisition_header_id = prha.requisition_header_id

            AND ROWNUM = 1

    )                                         currency,

    prha.requisition_header_id,

    NULL                                      supplier_name,

    wf.assigneesdisplayname                   pending_with_whom,

    to_char(wf.assigneddate, 'DD-MM-YYYY')    submit_for_appr_date,

    email.*,

    wf.identificationkey,

    wf.PROCESSNAME,

    wf.STATE

FROM

    po_action_history           pah,

    por_requisition_headers_all prha,

    fa_fusion_soainfra.wftask   wf,

-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

    hr_operating_units          d,

    (

        SELECT

            panf.full_name,

            fu.username,

            pea.email_address

        FROM

            per_users           fu,

            per_all_people_f    papf,

            per_person_names_f  panf,

            per_email_addresses pea

        WHERE

                fu.person_id = papf.person_id

            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

            AND panf.person_id = papf.person_id

            AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date

            AND panf.name_type = 'GLOBAL'

            AND pea.person_id = papf.person_id

            AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)

    )                           email

WHERE

        pah.object_id = prha.requisition_header_id

    AND prha.req_bu_id = d.organization_id

    AND pah.object_type_code = 'REQ'

    AND prha.document_status IN ( 'PENDING APPROVAL' )

    AND wf.compositeinstanceid = prha.approval_instance_id

    AND wf.assignees IS NOT NULL

    AND wf.tasknumber = (

        SELECT

            MAX(tasknumber)

        FROM

            fa_fusion_soainfra.wftask wf1

        WHERE

                wf.correlationid = wf1.correlationid -- and wf1.state = 'ASSIGNED'

            AND wf1.compositeinstanceid = prha.approval_instance_id

    )

-- AND WASSS_V.taskid(+) = wf.taskid

    AND email.username (+) = upper(substr(wf.assignees,

                                          1,

                                          instr(wf.assignees, ',') - 1)) ----WASSS_V.ASSIGNEE

    AND pah.sequence_num = (

        SELECT

            MAX(pah1.sequence_num)

        FROM

            po_action_history pah1

        WHERE

                pah1.object_id = pah.object_id -----rEQ---

            AND pah1.object_type_code = 'REQ'

            AND prha.document_status IN ( 'PENDING APPROVAL' ) -- AND pah1.action_code = nvl(:p_status,pah1.action_code)

    )

---Parameters

    -- AND prha.requisition_header_id = nvl(:por_number, prha.requisition_header_id)

    -- AND decode(:p_bu, NULL, 1, d.organization_id) = nvl(:p_bu, 1)

-- AND NVL (:PO_Number,1) = 1

UNION ALL

SELECT

    'Purchase Order',

    pv.change_order_status                  approval_status,

    to_char(ph.creation_date, 'DD-MM-YYYY') po_date,

--------------

    ph.segment1                             po_number,

    replace(replace(ph.comments,

                    CHR(13),

                    ''),

            CHR(10),

            '')                             pr_po_desc,

    (

        SELECT

            SUM(nvl(pl1.unit_price, pl1.amount) * decode(pl1.purchase_basis, 'SERVICES', 1, pl1.quantity))

        FROM

            po_lines_all pl1

        WHERE

            pl1.po_header_id = ph.po_header_id

    )                                       pr_po_amount,

    ph.currency_code                        currency,

    ph.po_header_id,

    pv.vendor_name                          supplier_name,

    wf.assigneesdisplayname                 pending_with_whom,

    to_char(wf.assigneddate, 'DD-MM-YYYY')  submit_for_appr_date,

    email.*,

    wf.identificationkey,

    wf.PROCESSNAME,

    wf.STATE

FROM

    po_action_history         poh,

    fa_fusion_soainfra.wftask wf,

-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

    po_headers_all            ph,

    po_versions               pv,

    poz_suppliers_v           pv,

    hr_operating_units        d,

    (

        SELECT

            panf.full_name,

            fu.username,

            pea.email_address

        FROM

            per_users           fu,

            per_all_people_f    papf,

            per_person_names_f  panf,

            per_email_addresses pea

        WHERE

                fu.person_id = papf.person_id

            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

            AND panf.person_id = papf.person_id

            AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date

            AND panf.name_type = 'GLOBAL'

            AND pea.person_id = papf.person_id

            AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)

    )                         email

WHERE

        poh.correlation_id = wf.correlationid

    AND poh.object_id = ph.po_header_id

    AND ph.po_header_id = pv.po_header_id

    AND wf.compositeinstanceid = pv.approval_instance_id

--AND wf.state = 'ASSIGNED'

    AND assignees IS NOT NULL

-- AND ph.segment1 = 'ISG0000048'

    AND poh.object_type_code = 'PO'

-- AND poh.ACTION_CODE = 'SUBMIT'

-- AND ph.DOCUMENT_STATUS IN ('PENDING APPROVAL')

-- ('WITHDRAWN', 'REJECTED', 'PENDING APPROVAL')

    AND pv.change_order_status = 'PENDING APPROVAL'

    AND ph.vendor_id = pv.vendor_id

    AND ph.prc_bu_id = d.organization_id

    AND wf.tasknumber = (

        SELECT

            MAX(tasknumber)

        FROM

            fa_fusion_soainfra.wftask wf1

        WHERE

                wf.correlationid = wf1.correlationid --

            AND wf1.state = 'ASSIGNED'

            AND wf1.compositeinstanceid = pv.approval_instance_id

    )

-- AND WASSS_V.taskid(+) = wf.taskid

    AND email.username (+) = upper(substr(wf.assignees,

                                          1,

                                          instr(wf.assignees, ',') - 1))

    AND poh.sequence_num = (

        SELECT

    max ( pah1.sequence_num ) FROM po_action_history pah1 WHERE pah1.object_id = poh.object_id -----Po_header_id

                                                                AND pah1.object_type_code = 'PO' -- AND pah1.action_code = nvl(:p_status,pah1.action_code)

    )

---Parameters

    -- AND ph.po_header_id = nvl(:po_number, ph.po_header_id)

    -- AND decode(:p_bu, NULL, 1, ph.prc_bu_id) = nvl(:p_bu, 1)

-- AND NVL (:POR_Number,1) =1

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;