API to finally close Purchase Requisitions for Approved PR's but not created PO's through PR's :

DECLARE
    x_req_control_error_rc VARCHAR2 (500);
    l_org_id NUMBER := 28; -- Enter the Operating_Unit Here
    cnt      NUMBER := 0;
 CURSOR c_req_close 
    IS
        SELECT prha.requisition_header_id
               ,prha.segment1 requisition_num
               ,prla.line_num
               ,prla.requisition_line_id
               ,prha.preparer_id
               ,prha.type_lookup_code
               ,pdt.document_type_code
               ,prha.authorization_status
               ,prha.closed_code
          FROM po_requisition_headers_all prha
              ,po_requisition_lines_all prla
              ,po_req_distributions_all prda
              ,po_document_types_all pdt
          WHERE prha.authorization_status = 'APPROVED'
            AND prha.requisition_header_id = prla.requisition_header_id
            AND prla.requisition_line_id = prda.requisition_line_id
            AND prha.type_lookup_code = pdt.document_subtype
            AND prha.org_id = pdt.org_id
            AND prha.org_id = 28
            AND TRUNC(prha.creation_date) >= TRUNC(SYSDATE-160)
            AND prha.segment1 = '12161'
            AND NOT EXISTS( SELECT 1
                              FROM po_distributions_all pda
                                  ,po_line_locations_all  plla
                                  ,po_lines_all pla
                                  ,po_headers_all pha
                            WHERE prda.distribution_id = pda.req_distribution_id
                                AND pda.line_location_id = plla.line_location_id
                                AND plla.po_line_id = pla.po_line_id
                                AND pla.po_header_id = pha.po_header_id
                            )
        ORDER BY prha.creation_date, prla.line_num ;
BEGIN
fnd_global.apps_initialize(user_id         => 200082
                                         ,resp_id         => 7673
                                        ,resp_appl_id => 201
                                        );
mo_global.init ('PO');
    mo_global.set_policy_context ('S', l_org_id);
FOR i IN C_REQ_CLOSE
    LOOP
DBMS_OUTPUT.PUT_LINE ('Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>' ||i.requisition_num);
DBMS_OUTPUT.PUT_LINE ('=======================================================');
po_reqs_control_sv.update_reqs_status(x_req_header_id         => i.requisition_header_id
                                             ,x_req_line_id         => i.requisition_line_id
                                             ,x_agent_id             => i.preparer_id
                                             ,x_req_doc_type         => i.document_type_code
                                             ,x_req_doc_subtype     => i.type_lookup_code
                                             ,x_req_control_action     => 'FINALLY CLOSE'
                                             ,x_req_control_reason     => 'FINALLY CLOSED BY API'
                                             ,x_req_action_date     => SYSDATE
                                             ,x_encumbrance_flag     => 'N'
                                             ,x_oe_installed_flag     => 'Y'
                                             ,x_req_control_error_rc => x_req_control_error_rc
                                             );
DBMS_OUTPUT.PUT_LINE ( 'Status Found: ' ||X_req_control_error_rc);
DBMS_OUTPUT.PUT_LINE ('Requisition Number which is Finally Closed =>'|| i.Requisition_num);
cnt := cnt+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Count is :=>' ||cnt);
END;
--==============================================================================
SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND UPPER(fcpt.user_concurrent_program_name) = UPPER(:conc_prg_name)
    AND frt.responsibility_name LIKE 'KE%AP%'
    ORDER BY 1,2,3,4
    
select * from fnd_concurrent_programs_tl where UPPER(user_concurrent_program_name) like UPPER('%AP%ACC')

SELECT * FROM po_requisition_headers_all

SELECT prha.requisition_header_id
           ,prha.segment1
           ,prla.line_num
           ,prla.requisition_line_id
           ,prha.preparer_id
           ,prha.type_lookup_code
           ,pdt.document_type_code
           ,prha.authorization_status
           ,prha.closed_code
  FROM po_requisition_headers_all prha
          ,po_requisition_lines_all prla
          ,po_req_distributions_all prda
          ,po_document_types_all pdt
WHERE prha.authorization_status = 'APPROVED'
    AND prha.requisition_header_id = prla.requisition_header_id
    AND prla.requisition_line_id = prda.requisition_line_id
    AND prha.type_lookup_code = pdt.document_subtype
    AND prha.org_id = pdt.org_id
    AND prha.org_id = 28
    AND TRUNC(prha.creation_date) >= TRUNC(SYSDATE-160)
--    AND prha.segment1 = '12161'
    AND NOT EXISTS(SELECT 1
                                  FROM po_distributions_all pda
                                          ,po_line_locations_all  plla
                                          ,po_lines_all pla
                                          ,po_headers_all pha
                                WHERE prda.distribution_id = pda.req_distribution_id
                                    AND pda.line_location_id = plla.line_location_id
                                    AND plla.po_line_id = pla.po_line_id
                                    AND pla.po_header_id = pha.po_header_id
                                )
ORDER BY prha.creation_date, prla.line_num;

SELECT POH. PO_HEADER_ID, POH. SEGMENT1 "PO NO" , PRHA .SEGMENT1 
"REQUISTION NO" 
FROM PO_HEADERS_ALL POH, 
       PO_DISTRIBUTIONS_ALL PDA ,
       PO_REQ_DISTRIBUTIONS_ALL PRDA ,
       PO_REQUISITION_LINES_ALL PRLA ,
       PO_REQUISITION_HEADERS_ALL PRHA
WHERE POH. PO_HEADER_ID = PDA. PO_HEADER_ID 
AND    PDA. REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND    PRDA. REQUISITION_LINE_ID = PRLA. REQUISITION_LINE_ID
AND      PRLA. REQUISITION_HEADER_ID = PRHA. REQUISITION_HEADER_ID
--AND   POH.SEGMENT1 = '207655'  -- PO NUMBER 
--AND    PRHA. SEGMENT1 = '230617'   -- PO REQUISITION NUMBER
and poh.org_id = 28;