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.

Oracle apps R12 creating fnd users from HR New Hired for SSHR users

We have 11 payrolls, and for each payroll different user name prefix and responsibility differs.
 so we created lookup in HR named FUJ_USER_PREFIX_PAYROLL and
captured
lookup_code payroll_id
Meaning payroll_Name
Description prefix for username
Tag Resp_key

Declare
v_description VARCHAR2 (100) := '';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 V_ERROR VARCHAR2(2000);
 V_COUNT NUMBER ;
 V_COUNT_ALL NUMBER := 0;

 CURSOR get_emp is

SELECT DISTINCT PAPF.PERSON_ID PERSON_ID,
DECODE(PAAF.PAYROLL_ID, 61,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX','LOOKUP_CODE',PAAF.ORGANIZATION_ID,'DESCRIPTION','US')
, 82,''
,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX_PAYROLL','LOOKUP_CODE',PAAF.PAYROLL_ID,'DESCRIPTION','US')
)||PAPF.EMPLOYEE_NUMBER
USER_NAME
,XX_FUJ_ENTRIES_ALLOWANCES.DECODE_FND_LOOKUP_AR ('FUJ_USER_PREFIX_PAYROLL','LOOKUP_CODE',PAAF.PAYROLL_ID,'TAG','US') responsibility_key
--,'PER'
FROM
PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
--AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
--AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND TRUNC(PAPF.CREATION_DATE) = TRUNC(SYSDATE)
AND PAAF.PERSON_ID NOT IN
(
SELECT EMPLOYEE_ID FROM FND_USER
WHERE EMPLOYEE_ID IS NOT NULL
)
 ;

get_emp_rec get_emp%ROWTYPE; 
BEGIN
OPEN get_emp;
loop
 FETCH get_emp INTO get_emp_rec;
 EXIT WHEN get_emp%NOTFOUND;
 fnd_user_pkg.createuser
(x_user_name => get_emp_rec.user_name,
 x_owner => NULL,
 x_unencrypted_password => '12345',
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => get_emp_rec.PERSON_ID,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;

commit;
fnd_user_pkg.addresp(username => UPPER(get_emp_rec.user_name)
 ,resp_app => 'PER'
 ,resp_key => get_emp_rec.responsibility_key
 ,security_group => 'STANDARD'
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);

                --commit;
                V_COUNT_ALL := V_COUNT_ALL +1;
 
  END LOOP;
CLOSE get_emp;
DBMS_OUTPUT.PUT_LINE(V_COUNT_ALL);
EXCEPTION WHEN OTHERS THEN
V_ERROR := SQLERRM;


END;

Oracle Apps R12 making Duration auto calculate in SSHR Leave Absence screen

Update the profile option 
HR: Absence Duration Auto Overwrite 
to 'Yes'.
 

Oracle Apps R12 How to Save attachments from SSHR to HR for Absence

How to Save attachments from SSHR to HR for Absence:

1.

Go to review Page> Click Personalize 'Attachments'

2.

3.

Custom Approver Group for Approval by the first supervisor, at most.SSHR Oracle Apps R12

Requirement:

Approval by the first supervisor, at most. If the employee applies, it works fine.

But if the supervisor applies for employee, and supervisor has supervisor attached is assignment screen, then it goes to his supervisor, which is not correct in this case.

Then, we need to create custom approver group.

SELECT TYR_GET_SUPERVISOR(:transactionId) FROM DUAL

FUNCTION TYR_GET_SUPERVISOR(p_transaction_id IN NUMBER

)

---Returns the person_id of first supervisor, if supervisor applies for the employeee no need to bring the supervisor's supervisor------

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 paaf.supervisor_id

INTO l_person_id

FROM per_all_people_f papf,

per_all_assignments_f paaf

WHERE papf.person_id=paaf.person_id

and papf.person_id=l_selected_person_id

AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.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_SUPERVISOR;