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

 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.

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
    );

Oracle EBS - SQL Query - Supplier Site associated Bank Details

Subject: Business need to extract the supplier site level associated bank details.

--SQL:

SELECT

    'Suppplier Site'    "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    iban,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv,

    apps.ap_supplier_sites_all   ass

WHERE

        1 = 1

    AND aps.vendor_id = ass.vendor_id

    AND ass.org_id = :p_org_id

    AND iepa.payee_party_id = aps.party_id

    AND iepa.party_site_id IS NOT NULL

    AND iepa.supplier_site_id IS NOT NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - SQL Query - Supplier Header associated Bank Details

Subject: Business required to get the supplier information where Supplier Header associated bank details.

--SQL:

 SELECT

    'Suppplier Header'  "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv

WHERE

        1 = 1

    AND aps.vendor_id IN (

        SELECT

            vendor_id

        FROM

            apps.ap_supplier_sites_all ass

        WHERE

            org_id = nvl(:p_org_id, org_id)

    )

    AND iepa.payee_party_id = aps.party_id

    AND party_site_id IS NULL

    AND supplier_site_id IS NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

    AND ieba.bank_account_num IS NOT NULL

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - R12 - Inventory - Oracle API to Update Lot Origin Date of Item Lot Numbers

 -- R12 - INV - Sample Script to Update Lot Number Lot Origin Date usinginv_lot_api_pub.update_inv_lot

DECLARE

    l_object_id      NUMBER;

    l_return_status  VARCHAR2(1);

    l_msg_count      NUMBER;

    l_msg_data       VARCHAR2(4000);

    l_expire_date    DATE;

    x_lot_rec        mtl_lot_numbers%rowtype;

    l_lot_rec        mtl_lot_numbers%rowtype;

    l_source         NUMBER;

    l_api_version    NUMBER := 1.0;

    l_init_msg_list  VARCHAR2(1000) := fnd_api.g_false;

    l_commit         VARCHAR2(1000) := fnd_api.g_false;

    CURSOR c_item_info IS

    SELECT

        inventory_item_id,

        organization_id,

        lot_number,

        creation_date,

        origination_date,

        c_attribute1

    FROM

        mtl_lot_numbers

    WHERE

            1 = 1

        AND trunc(origination_date) BETWEEN trunc(TO_DATE('03/03/24', 'MM/DD/YY')) AND trunc(TO_DATE('03/05/24', 'MM/DD/YY'))

        AND trunc(creation_date) != trunc(origination_date)

        AND organization_id IN ( 85, 97 )

--AND TRUNC(CREATION_DATE) >= SYSDATE-150

--and C_ATTRIBUTE1='WMOTCWRJAB'

--and c_attribute1 is not null

        AND lot_number = '1710010' --'1542009' --'1423007'

        ;

BEGIN

-- initialization required for R12

    mo_global.set_policy_context('S', 102);

    mo_global.init('INV');

-- Initialization for Organization_id

    inv_globals.set_org_id(102);

-- initialize environment

    fnd_global.apps_initialize(user_id => 11445,-----------------------------------> pass in user_id User Id=11445 ,

                              resp_id => 20634,

                              resp_appl_id => 401);

    

    FOR i IN c_item_info LOOP

    

    l_lot_rec.inventory_item_id:=i.inventory_item_id;

    l_lot_rec.organization_id:=i.organization_id;

    l_lot_rec.lot_number:=i.lot_number;

    l_lot_rec.origination_date:=i.creation_date;

    

        dbms_output.put_line('Calling inv_lot_api_pub.update_inv_lot API to Update Lot Numbers');

        dbms_output.put_line('*********************************************');

        inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status, x_msg_count => l_msg_count,

                                      x_msg_data => l_msg_data,

                                      x_lot_rec => x_lot_rec,

                                      p_lot_rec => l_lot_rec,

                                      p_source => l_source,

                                      p_api_version => 1.0,

                                      p_init_msg_list => l_init_msg_list,

                                      p_commit => l_commit);

        dbms_output.put_line('The Status Returned by the API is => ' || l_return_status);

        IF l_return_status = fnd_api.g_ret_sts_success THEN

            COMMIT;

        ELSE

            ROLLBACK;

        END IF;

        dbms_output.put_line('x_msg_count :' || l_msg_count);

        dbms_output.put_line('x_msg_data :' || l_msg_data);

    END LOOP;

END;

Oracle EBS - SQL Query to get the employee default expense account

 

SELECT

    concatenated_segments

FROM

    apps.gl_code_combinations_kfv

WHERE

    code_combination_id IN (

        SELECT DISTINCT

            default_code_comb_id

        FROM

            apps.per_all_assignments_f

            WHERE  effective_end_date >= sysdate

        AND

            person_id IN (

                SELECT

                    person_id

                FROM

                    apps.per_all_people_f

                WHERE

                    effective_end_date >= sysdate

                    and person_id LIKE '&person_id'

            )

    );

Oracle EBS - Employee - Manager Hierarchy SQL Query

Subject: SQL Query required to get the employee and Manager hierarchy

--SQL Query:--

SELECT

    papf.employee_number,

    papf.full_name,

    papf.person_id,

    ppg.segment1                 officer_flag, --executive flag for officers (Y is officer, N is not)

    nvl(spapf.full_name, 'NONE') supervisor_name,

    spapf.person_id              supv_person_id,

    level,

    pjobs.name                                         "Job"  

FROM

    per_people_x      papf,

    per_people_x      spapf,

    per_assignments_x paaf,

    pay_people_groups ppg,

    per_jobs                  pjobs

WHERE

        papf.person_id = paaf.person_id

    AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

    AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date

    AND sysdate BETWEEN spapf.effective_start_date AND spapf.effective_end_date

--AND_xxx_person_type.is_employee(:p_eff_date, papf.person_id)

    AND paaf.assignment_type IN ( 'E', 'C' )

    AND paaf.supervisor_id = spapf.person_id

    AND paaf.people_group_id = ppg.people_group_id

    AND paaf.primary_flag = 'Y'

    AND pjobs.job_id (+) = paaf.job_id

  --  AND pjobs.name like '%Senior Director%'

START WITH

    papf.employee_number = :p_emp_num --Enter emp num of employee whom Hierachy Need to Find

CONNECT BY

    PRIOR spapf.employee_number = papf.employee_number --AND LEVEL < :p_level

ORDER BY

    level DESC