Oracle PLSQL Script - How to Generate File and Place it on Oracle File Server Using Oracle Utility

 DECLARE

  -- Declare file handler
  file_handle UTL_FILE.FILE_TYPE;

  -- Declare variables for data to be written
  v_line VARCHAR2(100);
  v_filename VARCHAR2(50) := 'handlingunits'||TO_CHAR(SYSDATE,'MMDDRRHH24MISS')||'.csv';  -- Name of the file
  v_dir VARCHAR2(50) := 'XX_CAO_OUTBOUND';  -- Directory path (ensure it's accessible by Oracle)

 

BEGIN
  -- Open the file for writing. If the file does not exist, it will be created.
  file_handle := UTL_FILE.FOPEN(v_dir, v_filename, 'w');  -- 'w' mode means write

  -- Example data to write to the file
  FOR xxtlm_rec IN (SELECT DISTINCT ';;;'||item||';'||vendor_lot||';1;1;1;' line
                    FROM xxtlm.xxtlm_rcv_transactions xrt
                    WHERE xrt.organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = 'TIF')
                        AND xrt.status_id = 2
                        AND transaction_type = 'RECEIVE'
                    UNION
                    SELECT '99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999'
                    FROM dual
                    ORDER BY 1 DESC) 
    LOOP    
    -- Write the data to the file
    UTL_FILE.PUT_LINE(file_handle, xxtlm_rec.line);
  END LOOP;

  -- Close the file after writing
  UTL_FILE.FCLOSE(file_handle);

 

  -- Success message
  DBMS_OUTPUT.PUT_LINE('Data written successfully to ' || v_dir || '/' || v_filename);

 

EXCEPTION
  WHEN OTHERS THEN
    -- Handle any exceptions
    DBMS_OUTPUT.PUT_LINE('Exception: '||SQLERRM);
    IF UTL_FILE.IS_OPEN(file_handle) THEN
      UTL_FILE.FCLOSE(file_handle);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

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.