API to Close Sales Order Where SO Lines are already closed in Oracle EBS R12

 --API to Close Sales Order Where SO Lines are already closed

DECLARE
    l_file_val VARCHAR2(300);
    l_result   VARCHAR2(30);
    CURSOR header_cur IS
      SELECT h.header_id
      FROM   wf_item_activity_statuses st,
             oe_order_headers_all h,
             wf_process_activities wpa
      WHERE  wpa.instance_id = st.process_activity
             AND st.item_type = 'OEOH'
             AND wpa.activity_name = 'CLOSE_WAIT_FOR_L'
             AND wpa.process_name = 'CLOSE_HEADER_PROCESS'
             AND st.activity_status = 'NOTIFIED'
             AND st.item_key = To_char(h.header_id)
             AND h.header_id = 3654189
             AND h.open_flag = 'Y'
             AND h.flow_status_code = 'BOOKED'
             AND NOT EXISTS(SELECT 1
                            FROM   oe_order_lines_all oel
                            WHERE  oel.header_id = h.header_id
                                   AND oel.open_flag = 'Y');
BEGIN
    oe_debug_pub.Setdebuglevel(5);
    oe_debug_pub.debug_on;
    oe_debug_pub.initialize;
    l_file_val := oe_debug_pub.Set_debug_mode('FILE');
    dbms_output.Put_line('Debug File : ' ||l_file_val);

    FOR header_rec IN header_cur LOOP

        dbms_output.Put_line('Closing Header:' ||header_rec.header_id);
        oe_debug_pub.ADD('Closing Header:'||header_rec.header_id);
        oe_standard_wf.Oeoh_selector (p_itemtype => 'OEOH',
        p_itemkey => To_char(header_rec.header_id), p_actid => 12345,
        p_funcmode => 'SET_CTX', p_result => l_result);

        wf_engine.Handleerror('OEOH', header_rec.header_id, 'CLOSE_HEADER_PROCESS:CLOSE_HEADER', 'RETRY', NULL);

    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
      oe_debug_pub.ADD(' Error is : '||SQLERRM);
      dbms_output.Put_line('Error : '||SQLERRM);
      NULL;
END;
/*
Metalink Docs:
All Order Lines Are Closed Yet Header Remains Open [ID 145010.1]
Sales Order Header Does Not Close After ALL Lines Have Closed [ID 133837.1]
*/

Query to find submitted Concurrent Request Details in Oracle EBS R12

 --Query to find submitted Concurrent Request Details

SELECT rq.parent_request_id                      "Parent Req. ID",
       rq.request_id                             "Req. ID",
       tl.user_concurrent_program_name           "Program Name",
       rq.actual_start_date                      "Start Date",
       rq.actual_completion_date                 "Completion Date",
       (SELECT meaning
        FROM   apps.fnd_lookups
        WHERE  lookup_type = 'CP_STATUS_CODE'
               AND lookup_code = rq.status_code) status_code,
       (SELECT meaning
        FROM   apps.fnd_lookups
        WHERE  lookup_type = 'CP_PHASE_CODE'
               AND lookup_code = rq.phase_code)  phase_code,
       rq.argument_text                          parameter,
       Round(( rq.actual_completion_date - rq.actual_start_date ) * 1440, 2)
                                                 "Runtime (in Minutes)",
       Round(( ( rq.actual_completion_date - rq.actual_start_date ) * 1440 ) /
             60, 2)
                                                 "Runtime (in Hrs)",
       (SELECT user_name
               ||' - '
               ||email_address
        FROM   fnd_user
        WHERE  user_id = rq.requested_by)        run_by
FROM   applsys.fnd_concurrent_programs_tl tl,
       applsys.fnd_concurrent_requests rq
WHERE  tl.application_id = rq.program_application_id
       AND tl.concurrent_program_id = rq.concurrent_program_id
       AND tl.LANGUAGE = Userenv('LANG')
       AND rq.actual_start_date IS NOT NULL
       --AND      rq.actual_completion_date IS NOT NULL 
       --AND      rq.status_code = 'E' --Status : C-Normal,E-Error,D-Cancelled 
       AND tl.user_concurrent_program_name IN ( '-----------------------' )
ORDER  BY 1 DESC;