Oracle SSHR R12 supervisor is in the Approval Group in the next level, then skip the Approval Group.

FUNCTION TYR_GET_HR_ROLE(p_transaction_id IN NUMBER, P_ROLE_NAME IN VARCHAR2
)
---case when supervisor is in the role of approver users in the next level, then skip the role approval, as already the supervisor approved it.
RETURN VARCHAR2
AS
L_person_id NUMBER(10);
L_selected_person_id hr_api_transactions.selected_person_id%TYPE;
L_creator_person_id hr_api_transactions.creator_person_id%TYPE;
l_business_group_id NUMBER;
BEGIN
fnd_profile.get ('PER_BUSINESS_GROUP_ID', l_business_group_id);
BEGIN
SELECT selected_person_id,creator_person_id
INTO L_selected_person_id,L_creator_person_id
FROM hr_api_transactions
WHERE transaction_id = p_transaction_id;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

BEGIN

select ppf.PERSON_ID
INTO l_person_id
from pqh_roles PR, per_people_extra_info pei, per_all_people_f ppf, fnd_user usr
where pei.PEI_INFORMATION3 = to_char(pr.role_id)
and ppf.person_id = pei.PERSON_ID
and usr.EMPLOYEE_ID = ppf.PERSON_ID
and sysdate between ppf.EFFECTIVE_START_DATE and ppf.EFFECTIVE_END_DATE
and pr.role_name= P_ROLE_NAME
and pei.INFORMATION_TYPE='PQH_ROLE_USERS'
and ppf.PERSON_ID <> (SELECT selected_person_id FROM hr_api_transactions WHERE transaction_id = p_transaction_id)
and ppf.PERSON_ID <> (SELECT nvl(paaf.supervisor_id,0) FROM per_all_assignments_f paaf
WHERE paaf.person_id=(SELECT selected_person_id FROM hr_api_transactions WHERE transaction_id = p_transaction_id)
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND PRIMARY_FLAG = 'Y');

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_person_id :=null;
END;
IF (L_selected_person_id = L_person_id) OR (l_creator_person_id = l_person_id) THEN
RETURN NULL;
ELSE
RETURN 'PER:'||to_char(L_person_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END TYR_GET_HR_ROLE;

Oracle Apps R12 SSHR Custom Notification Requirement to add Empno and Leave Name in the Notification Subject sent to the approver:

Requirement to add Empno and Leave Name in the Notification Subject sent to the approver:

1. Create package in database XXZAMEL_WF_SS.

Code of package is at the end of the post.

2. Identify the item_Key , message_name from the below query.

select item_key,message_name from wf_notifications

WHERE NOTIFICATION_ID = 9589048

Notification Message Name: HR_EMBD_NTFY_APPROVAL_FWD_MSG

Approval Message Name: HR_EMBED_RN_NTF_APPR_MSG

3. Get the process name from the below query.

select process_name, TRANSACTION_ID from hr_api_transactions where item_key = :item_key_step2

HR_GENERIC_APPROVAL_PRC

4. Open the workflow builder, and then open the process name mentioned in step3.

5. Create 2 attributes under Attributes.

1. SELECT_ABSENCE_NAME
2. SELECT_EMPLOYEE_NUMBER

6. Copy(right click copy) Under Messages ‘HR_EMBED_RN_NTF_APPR_MSG’

and paste (right click paste) with new name as ‘XX_HR_EMBED_RN_NTF_APPR_MSG’

Open the properties of Message and in Body Subject add our new attributes as below.

&HR_APPROVER_MSG_SUBJECT_ATTR -> &SELECT_EMPLOYEE_NUMBER -> &SELECT_ABSENCE_NAME

7. Copy/drag the new 2 attributes created in step 5, paste/drag under the new message created in step6.

8. Copy the notification ‘HR_APPROVER_NTF’ paste it with the new name ‘XX_HR_APPROVER_NTF’.

In the properties of Notification change the message to our new custom message created in step7.

9. Under Functions, create new function internal name as ‘XX_HR_APPR_SET_VALUES’, drag/paste the 2 new attributes under the new function as shown below.

Function Name:  give the package.procedure which we created in step1.

XXZAMEL_WF_SS.set_selected_person

10. Open the process ‘HR_APPROVAL_NTF_PRC’

Take the screenshot and save it, as we will delete the HR_APPROVER_NTF message selected in the above image.

Once deleted, drag the XX_ HR_APPROVER_NTF notification and connect the same as before.

The blue lines shows the connectivity is done twice, Example: Resubmit and then again Approve.

Don’t worry about the function, we are going to add in the next step.

11. Drag the custom function we created between new notification and OR function as below shown.

12. Save and test the solution.

dfd

Package code:


/*--------------------------------------- (XXZAMEL_WF_SS) Package ------------------------------------------------*/
CREATE OR REPLACE package APPS.XXZAMEL_WF_SS as

procedure set_selected_person(itemtype  in     varchar2
                              ,itemkey  in     varchar2
                              ,actid    in     number
                              ,funmode  in     varchar2
                              ,result      out nocopy varchar2) ;


end;
/

/*----------------------------------------- Package Body -------------------------------------*/
/*--------------------------------------------------------------------------------------------*/

CREATE OR REPLACE package body APPS.XXZAMEL_WF_SS as

---- preocedue set_selected_function gets the selected person in HRSS as per transaction_id from hr_api_transactions table
-- created by HNasr 0ctober-2015

procedure set_selected_person(itemtype  in     varchar2
                              ,itemkey  in     varchar2
                              ,actid    in     number
                              ,funmode  in     varchar2
                              ,result      out nocopy varchar2)
is
l_transaction_id   number;
l_selected_person  number;
l_sel_person_username varchar2(200);
l_selected_person_id number;
l_sel_function_name varchar2(200);
l_sel_absence_name varchar2(200);
l_sel_request_id varchar2(200);
l_sel_employee_num varchar2(200);
l_sel_leave_reason varchar2(200);

begin
    --get the transaction_id to use in getting the selected_person_id
    l_transaction_id := wf_engine.GetItemAttrNumber(ITEMTYPE => ITEMTYPE,
                                                    ITEMKEY  => ITEMKEY,
                                                    ANAME    => 'TRANSACTION_ID'
                                                    );
    -- get the selected_person_id
    BEGIN
    SELECT selected_person_id
    into l_selected_person
    FROM HR_API_TRANSACTIONS
    WHERE TRANSACTION_ID = l_transaction_id
    and selected_person_id <> creator_person_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN l_selected_person_id := 0 ;
    WHEN OTHERS THEN l_selected_person_id := 0 ;
    END;
    -- get the username for the selected_perosn to send him the notification
    begin
    select user_name
    into l_sel_person_username
    from fnd_user
    where employee_id = l_selected_person
    and rownum = 1
    and (trunc(SYSDATE) BETWEEN trunc(start_date) AND trunc(end_date) or end_date is null )
    ORDER BY LAST_UPDATE_DATE DESC;
    exception
      when no_data_found then l_sel_person_username := 'SYSADMIN';
      WHEN OTHERS THEN l_sel_person_username := 'SYSADMIN';
    end;
   
     begin
    select distinct fu.USER_FUNCTION_NAME
    into l_sel_function_name
    from hr_api_transactions ap,
     FND_FORM_FUNCTIONS_VL fu
where fu.function_id = ap.function_id
and ap.TRANSACTION_ID=  l_transaction_id;
    exception
      when no_data_found then null;
      WHEN OTHERS THEN null;
    end;
   
 
 
 begin
    select abs.name
    into l_sel_absence_name
    from hr_api_transaction_steps stp,
     hr_api_transactions tra,
     --per_absence_attendance_types abs
     per_abs_attendance_types_tl abs
   where stp.TRANSACTION_ID = tra.TRANSACTION_ID
     and abs.ABSENCE_ATTENDANCE_TYPE_ID = stp.INFORMATION5
     and tra.TRANSACTION_ID=  l_transaction_id
      and abs.language = 'AR';--userenv('LANG'); as its not working getting english in both instances
     
    exception
      when no_data_found then null;
      WHEN OTHERS THEN null;
    end;

     
   
 begin
    select val.VARCHAR2_VALUE
    into l_sel_request_id
    from hr_api_transaction_steps stp,
     hr_api_transaction_values val
    where stp.TRANSACTION_STEP_ID = val.TRANSACTION_STEP_ID
      and val.NAME = 'P_INFORMATION1_1'
     and stp.TRANSACTION_STEP_ID=  l_transaction_id;
    exception
      when no_data_found then null;
      WHEN OTHERS THEN null;
    end; 
   
   
  begin
    select DISTINCT per.EMPLOYEE_NUMBER
    into l_sel_employee_num
    from per_all_people_f per,
     hr_api_transactions tran
     where per.PERSON_ID = tran.SELECTED_PERSON_ID
       and tran.TRANSACTION_ID=  l_transaction_id
       AND ROWNUM = 1;
    exception
      when no_data_found then null;
      WHEN OTHERS THEN null;
    end; 
   
   
     begin
    select per.ABS_INFORMATION1
    into l_sel_leave_reason
    from  per_absence_attendances per,
             hr_api_transactions tran
     where per.PERSON_ID = tran.SELECTED_PERSON_ID
       and tran.TRANSACTION_ID=  l_transaction_id;
    exception
      when no_data_found then null;
      WHEN OTHERS THEN null;
    end;     
   
    
   /*
    wf_engine.SetItemAttrText(itemtype => itemtype,
                                itemkey  => itemkey,
                                aname    => 'SELECTED_FUNCTION_NAME',
                                avalue   => l_sel_function_name );
      
    -- Set the selected_person_id to the attribute selected_person_id
    wf_engine.SetItemAttrNumber(itemtype => itemtype,
                                itemkey  => itemkey,
                                aname    => 'SELECTED_PERSON_ID',
                                avalue   => l_selected_person );
                               
    wf_engine.SetItemAttrText(itemtype => itemtype,
                              itemkey  => itemkey,
                              aname    => 'SELECT_PERSON_USERNAME',
                              avalue   => l_sel_person_username );
                             

                           
     wf_engine.SetItemAttrText(itemtype => itemtype,
                              itemkey  => itemkey,
                              aname    => 'SELECT_REQUEST_ID',
                              avalue   => l_sel_request_id ); 


     wf_engine.SetItemAttrText(itemtype => itemtype,
                              itemkey  => itemkey,
                              aname    => 'SELECT_LEAVE_REASON',
                              avalue   => l_sel_leave_reason );
 */
     wf_engine.SetItemAttrText(itemtype => itemtype,
                              itemkey  => itemkey,
                              aname    => 'SELECT_ABSENCE_NAME',
                              avalue   => l_sel_absence_name );
                                  
    
     wf_engine.SetItemAttrText(itemtype => itemtype,
                              itemkey  => itemkey,
                              aname    => 'SELECT_EMPLOYEE_NUMBER',
                              avalue   => l_sel_employee_num );
  exception
     when others then
       wf_core.context('XXSWCC_WF_SS','set_selected_person',SQLCODE || SQLERRM);
       raise;
end set_selected_person;
end;
/
 

Oracle Apps r12 api to create po requisition and Purhcase Order Auto Create.

CREATE OR REPLACE PACKAGE FUJ_POREQ_PO_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION(                       ERRBUF      OUT NOCOPY VARCHAR2,
                                                RETCODE     OUT NOCOPY VARCHAR2,
                                                P_ABSENCE_ATTENDANCE_ID NUMBER) ;


procedure FUJ_PO_CREATION(
Errbuff out varchar2,
retcode out varchar2,
P_REQ_HEAD_ID IN number);

END;

CREATE OR REPLACE PACKAGE BODY FUJ_POREQ_PO_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION(                       ERRBUF      OUT NOCOPY VARCHAR2,
                                                RETCODE     OUT NOCOPY VARCHAR2,
                                                P_ABSENCE_ATTENDANCE_ID NUMBER)
IS
CURSOR C1 IS

SELECT PAA.PERSON_ID,ABSENCE_ATTENDANCE_ID,DATE_START, DATE_END, ABSENCE_DAYS
,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME, PAPF.PER_INFORMATION18
 FROM
 PER_ABSENCE_ATTENDANCES PAA,
 PER_ALL_PEOPLE_F PAPF
 WHERE
 --PAA.PERSON_ID = 26876
 ABSENCE_ATTENDANCE_ID = 3707187
 AND PAPF.PERSON_ID = PAA.PERSON_ID
 AND DATE_START BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;

        BEGIN

FOR I IN C1 LOOP

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           --PREPARER_NAME,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           CATEGORY_ID,
                                           UNIT_PRICE,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           HEADER_DESCRIPTION,
                                           ITEM_DESCRIPTION,
                                           SUGGESTED_VENDOR_ID,
                                           SUGGESTED_VENDOR_SITE_ID,
                                           --ACCRUAL_ACCOUNT_ID ,
                                           --VARIANCE_ACCOUNT_ID,
                                           --BUDGET_ACCOUNT_ID,
                                           HEADER_ATTRIBUTE1,
                                           HEADER_ATTRIBUTE2,
                                           HEADER_ATTRIBUTE3,
                                           HEADER_ATTRIBUTE4,
                                           GL_DATE,
                                           REQUISITION_HEADER_ID                                          
                                           )
     VALUES ('IMPORT_EXP',                                  --Interface Source
             102,                                             --Operating Unit
             'EXPENSE',                                     --Destination Type
            'INCOMPLETE',       -- 'APPROVED',--                                     --Status
             26876,--6439,                      --This comes from per_people_f.person_id
            -- '???? ??? ????? ????? ?????????',
             106847,                                      --Code Combination ID
             'VENDOR',                                           --Source Type
             'Quantity',                                                     --UOM ok??yes
             1,                                           --Line Type of Goods
             1123,                                           --MISC.MISC Category
             100,                                                       --Price
             1,                                                    --quantity
             108,--102                      --Represents Vision Operations Inv Org.
             142,--162,                                --Represents V1-New York City
             26876,--6439 ,                            --This is the Deliver to Requestor
             I.EMPLOYEE_NUMBER,                        --One Time Header Description
             I.EMPLOYEE_NUMBER,                        --One Time Item Description
             751527,
             768622,
             --17979,--Accrual
             --106847,--Varianlce
             --106847, --Budget
             'Y',--Header. ATT1
             I.PER_INFORMATION18,--Header 2
            'New Delhi',--Header 3
             I.PERSON_ID,--Header 4
             TO_DATE('01-JAN-2017'),
              PO_REQUISITION_HEADERS_S.NEXTVAL
                           );
                          
                          
                           COMMIT;

END LOOP;

EXCEPTION WHEN OTHERS THEN NULL;

        END;
                          
procedure FUJ_PO_CREATION(
Errbuff out varchar2,retcode out varchar2,
P_REQ_HEAD_ID IN number)
IS
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(5000);
l_num_lines_processed number;
l_document_number po_headers_all.segment1%TYPE;
l_document_id number;
l_msg  varchar2(5000);
l_msg_index_out number;
CURSOR po_interface_headers_rec IS

SELECT  distinct prl.requisition_header_id
       ,prl.vendor_id
       ,prl.vendor_site_id
       ,prl.attribute14
       --,prl.unit_meas_lookup_code
       ,prl.destination_organization_id
       --,prl.org_id
       ,prh.org_id
       --,prl.deliver_to_location_id
       ,prh.preparer_id
       --,prh.authorization_status
       , 142 ship_to_location
       , 142  bill_to_location
       --,xprp.item_name
        ,SUGGESTED_VENDOR_NAME--,xprp.suggested_vendor_name  751527
       ,SUGGESTED_VENDOR_LOCATION--,xprp.suggested_vendor_site    768622
       ,prh.description
 FROM  po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd
       --xxspanv_po_req_preinteface xprp     
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.type_lookup_code = 'PURCHASE'
   --AND to_char(xprp.batch_id)=substr(prl.attribute14,12)
   --AND to_char(xprp.record_id)=(prl.ATTRIBUTE13)
   AND prh.authorization_status='APPROVED'
   --AND prh.closed_code<>'FINALLY CLOSED'
   AND prd.requisition_line_id = prl.requisition_line_id
   and prh.REQUISITION_HEADER_ID = P_REQ_HEAD_ID
   AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
   --AND prl.attribute14='REQ_WRAPPER'||p_batch_id
   ;

CURSOR po_interface_lines_rec (p_requistion_header_id number)
IS

SELECT  prl.requisition_header_id
       ,prl.quantity
       ,prl.requisition_line_id
       ,prl.item_id
       ,prl.unit_price
       ,prl.line_num
       --,prl.vendor_id
       --,prl.vendor_site_id
       ,prl.attribute14
       ,prl.unit_meas_lookup_code
       ,prl.destination_organization_id
       ,prl.org_id
       ,prl.deliver_to_location_id
       ,prh.preparer_id
       ,prh.authorization_status
 FROM  po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd     
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.type_lookup_code = 'PURCHASE'
   AND prh.authorization_status='APPROVED'
   --AND prh.closed_code<>'FINALLY CLOSED'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND prl.requisition_header_id=p_requistion_header_id
   AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
   and prh.REQUISITION_HEADER_ID = P_REQ_HEAD_ID
   --AND prl.attribute14='REQ_WRAPPER'||p_batch_id
   ;
L_USER_ID NUMBER:=1224;--2605;--fnd_profile.value('USER_ID');
L_RESP_ID NUMBER:= 50597;--fnd_profile.value('RESP_ID');
L_RESP_APPL_ID NUMBER:= 201;--fnd_profile.value('RESP_APPL_ID');
                   
 BEGIN
  DBMS_OUTPUT.PUT_LINE('CREATE PO PROCEDURE STARTS');
  fnd_file.put_line      (fnd_file.log,'PO CREATE Procedure starts here.');

   fnd_global.apps_initialize(L_USER_ID,L_RESP_ID,L_RESP_APPL_ID ,
                            null,
                            null
                            );
  mo_global.SET_POLICY_CONTEXT('S',102);  --change this
                        
  for i in po_interface_headers_rec
   loop
    fnd_file.put_line(fnd_file.log,'requistion_header_id = '||i.requisition_header_id);   
         INSERT INTO po.po_headers_interface
            (interface_header_id,
            batch_id,
            interface_source_code,
            process_code,
            action,
            org_id,
            document_type_code,
            document_subtype,
            currency_code,
            agent_id,
            vendor_id,
            vendor_site_id,
            ship_to_location_id,
            bill_to_location_id,--,
            group_code,
            style_id
            ,comments
            ,GL_ENCUMBERED_DATE
            )
            VALUES
            (apps.po_headers_interface_s.NEXTVAL,         
            apps.po_headers_interface_s.currval,
            'PO',
            'APPROVED',--'PENDING',
            'NEW',  --  Indicates this is a new document
            i.org_id, -- Org id for operating unit Vision Operations
            'PO',  -- Indicates a standard PO is being imported
            'STANDARD',
            'AED', -- The currency to be used in the PO
            i.preparer_id, -- The ID of the buyer
            i.vendor_id,  -- Supplier name
            i.vendor_site_id,  --  Supplier Site
            142,--i.ship_to_location, -- Ship to location name
            142,--i.bill_to_location,--, -- Bill to location name         
            'DEFAULT',--'TestPO'
            1,
            'Test from api ',--i.description
            to_date('01-jan-2017')
            );
    FOR j IN po_interface_lines_rec (i.requisition_header_id)
      LOOP
      
        INSERT INTO po_lines_interface
            (interface_line_id,
            interface_header_id,
            line_num,
            --shipment_num,
            line_type,
            item_id,--item,
            unit_of_measure,
            quantity,
            unit_price,
            --ship_to_organization_code,
            --ship_to_location,
            requisition_line_id
            )
            VALUES
            (po_lines_interface_s.nextval,
            po_headers_interface_s.currval,
            j.line_num,
            --1,
            'Goods',
            j.item_id,  -- Item to imported on the PO line
            j.unit_meas_lookup_code,  -- Unit of Measure
            j.quantity,
            j.unit_price,  --  Line price in specified currency
            j.requisition_line_id--'V1',  --  Inventory Organization which will receive the shipment
            --'V1- New York City'
            );
           dbms_output.put_line('insertion done in line interface');
           fnd_file.put_line(fnd_file.log,'insertion done in line interface');
      END LOOP;
     commit;
     dbms_output.put_line('api_calling');
    fnd_file.put_line(fnd_file.log,'api calling');
     FND_MSG_PUB.Initialize;
  
     dbms_output.put_line('po_headers_interface_s.currvalis'||po_headers_interface_s.currval);
     PO_INTERFACE_S.create_documents (
                                    p_api_version => 1.0,
                                    x_return_status => l_return_status,
                                    x_msg_count => l_msg_count,
                                    x_msg_data => l_msg_data,
                                    p_batch_id => po_headers_interface_s.currval,
                                    p_req_operating_unit_id => i.org_id,--–p_req_operating_unit_id,
                                    p_purch_operating_unit_id =>i.org_id,--–p_purch_operating_unit_id,
                                    x_document_id => l_document_id,
                                    x_number_lines => l_num_lines_processed,
                                    x_document_number => l_document_number,                                    --— <DBI FPJ>
                                    p_sourcing_k_doc_type => null,
                                    p_conterms_exist_flag => null,
                                    p_document_creation_method => 'AUTOCREATE',
                                    p_orig_org_id => null
                                    );
    end loop;
END;

            
       
       
END;                                         

if the po is created as INCOMPLETE status, then run the below code.

https://mogalafzal.blogspot.com/2023/04/apps-r12-po-approve-from-backend-from.html                              

Oracle EBS R12, how to Enable DFF for Supplier Bank Details.

DFF for Supplier Bank:

1. Identify the table and go to DFF Register to find the DFF Name.

2. Add Fields in the Attribute Category.
   
3. Go to functional Administrator and clear the cache. Its done.
4. 

   5.