Purchase order getting stuck with "in process" Status

API to Cancel Workflow
STEP1:
DECLARE
   CURSOR c1
   IS
      SELECT item_type, item_key, process_name
        FROM hr_api_transactions hat
       WHERE     status = 'Y'
             AND process_name = 'HR_GENERIC_APPROVAL_PRC'
             AND item_key = '254954';
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         wf_engine.
          abortprocess (itemtype   => i.item_type,
                        itemkey    => i.item_key,
                        process    => i.process_name);

         COMMIT;
         DBMS_OUTPUT.put_line ('Item Key has been Aborted/Cancelled: ' || i.item_key);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.
             put_line ('Inner Exception: ' || SQLERRM || ' - ' || i.item_key);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Step2: Query Purchase order data with correct po number:

 SELECT wf_item_key, wf_item_type, authorization_status,
org_id, last_update_date, segment1 po_no
FROM po_headers_all
WHERE authorization_status = ‘IN PROCESS’
AND org_id = 337
AND segment1 in (‘po_no’,’po_no2′)
ORDER BY last_update_date desc


Step3:
update purchase order authorizing status to " REQUIRES REAPPROVAL"

update po_headers_all
set AUTHORIZATION_STATUS='REQUIRES REAPPROVAL'
where AUTHORIZATION_STATUS='IN PROCESS'
and poh.segment1=PO_NUMBER>;
commit;

Query to Find Scheduled Concurrent Requests

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

API to cancel po in oracle apps R12

DECLARE
 v_return_status   VARCHAR2 (1000);
 v_msg_data        VARCHAR2 (1000);
 v_po_header_id    NUMBER        := 1692230;
 v_doc_subtype     VARCHAR2(10)  := 'STANDARD';
 v_doc_type        VARCHAR2(10)  := 'PO';
 v_org_id          NUMBER         := 2767;
 v_action          VARCHAR2(10)  := 'CANCEL';
 v_action_date     DATE          := SYSDATE;
 l_user_id     number;
 l_resp_id     number;
 l_resp_appl_id  number;
BEGIN
SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
  INTO l_user_id, l_resp_id, l_resp_appl_id
  FROM fnd_user fnd,
  fnd_responsibility_tl fresp
  WHERE fnd.user_name = 'XXXXXX'
 AND fresp.responsibility_name like 'XXXXX Purchasing Super User'

fnd_global.APPS_INITIALIZE(user_id      => 173683,
                           resp_id      => 52543,
                           resp_appl_id => 201
                          );
 COMMIT;
MO_GLOBAL.INIT ('PO');
--- context done ------------
DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT
   ( p_api_version      => 1.0,
     p_init_msg_list    => fnd_api.g_true,
     p_commit           => fnd_api.g_false,
     x_return_status    => v_return_status,
     p_doc_type         => v_doc_type,
     p_doc_subtype      => v_doc_subtype,
     p_doc_id           => v_po_header_id,
     p_doc_num          => NULL,
     p_release_id       => NULL,
     p_release_num      => NULL,
     p_doc_line_id      => NULL,
     p_doc_line_num     => NULL,
     p_doc_line_loc_id  => NULL,
     p_doc_shipment_num => NULL,
     p_action           => v_action,
     p_action_date      => v_action_date,
     p_cancel_reason    => NULL,
     p_cancel_reqs_flag => 'N',
     p_print_flag       => NULL,
     p_note_to_vendor   => NULL,
     p_use_gldate       => NULL,
     p_org_id           => v_org_id
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('The Return Status of the API : '|| v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success THEN
   COMMIT;
   DBMS_OUTPUT.PUT_LINE ('Cancellation of PO is Sucessfull : '||v_po_header_id);
ELSE
   DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
   ROLLBACK;
   FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG
   LOOP
      v_msg_data := FND_MSG_PUB.GET( p_msg_index => i, p_encoded => 'F');
      DBMS_OUTPUT.PUT_LINE( i|| ') '|| v_msg_data);
   END LOOP;
END IF;
END;

Query to get Details of Discoverer Report by sql query

SELECT DISTINCT disco_docs.doc_name "Discoverer Workbook",
                TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 5))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_users.eu_username,
                CASE
                   WHEN INSTR
                          (disco_users.eu_username,
                           '#'
                          ) = 0
                      THEN disco_users.eu_username
                   WHEN INSTR (disco_users.eu_username, '#') > 0
                   AND INSTR (disco_users.eu_username, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                   ELSE (SELECT resp.responsibility_name
                           FROM fnd_responsibility_tl resp
                          WHERE resp.responsibility_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                END AS "Shared Name / Responsibility"
           FROM eul4_us.eul4_documents disco_docs,
                eul4_us.eul4_access_privs disco_shares,
                eul4_us.eul4_eul_users disco_users
          WHERE disco_docs.doc_id = disco_shares.gd_doc_id
            AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
            AND disco_users.eu_id(+) = disco_shares.ap_eu_id
            --AND disco_docs.doc_name LIKE '%Account%';

Query to find KEY FLEX FIELDS (KFF)

select  FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM    ,
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

query to find users who have a responsibility

PROMPT Find users who have a responsibility
select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;

Define Statement Cycles in Receivables

Define Statement Cycles


Define statement cycles to determine when to send statements to your customers. You enter statement cycles when you define or modify individual customer and site profile classes in the Customer Profile Classes window.


Navigation:
Receivables Manager Resp==>Setup>Print>Statement Cycles