Oracle EBS FND_REQUEST.SUBMIT_REQUEST Code Generator

SET SERVEROUTPUT ON
DECLARE
  l_req_id      NUMBER;
  l_layout_ok   BOOLEAN;
  l_done        BOOLEAN;
  l_phase       VARCHAR2(80);
  l_status      VARCHAR2(80);
  l_dev_phase   VARCHAR2(30);
  l_dev_status  VARCHAR2(30);
  l_message     VARCHAR2(2000);
BEGIN
  -- 1. Apps context: user, responsibility, responsibility application
  FND_GLOBAL.APPS_INITIALIZE(1318, 20420, 1);

  -- 2. Operating unit (delete both lines for programs that are not multi-org)
  MO_GLOBAL.SET_POLICY_CONTEXT('S', 204);
  FND_REQUEST.SET_ORG_ID(204);

  -- 3. XML Publisher layout (delete this block if the program has no template)
  l_layout_ok := FND_REQUEST.ADD_LAYOUT('XXCUST', 'XX_OPEN_ORDERS_CSV', 'en', 'US', 'PDF');
  IF NOT l_layout_ok THEN
    RAISE_APPLICATION_ERROR(-20001, 'ADD_LAYOUT failed: ' || FND_MESSAGE.GET);
  END IF;

  -- 4. Submit. Arguments are positional VARCHAR2 values in the order of the program parameters.
  l_req_id := FND_REQUEST.SUBMIT_REQUEST(
                application => 'XXCUST',
                program     => 'XX_OPEN_ORDERS_CSV',
                description => 'Submitted from PL/SQL',
                start_time  => '[Start time (blank = now)]',
                sub_request => FALSE,
                argument1   => '204',
                argument2   => FND_DATE.DATE_TO_CANONICAL(TRUNC(SYSDATE)),
                argument3   => 'Y',
                argument4   => CHR(0),
                argument5   => CHR(0),
                argument6   => CHR(0));

  IF l_req_id = 0 THEN
    l_message := FND_MESSAGE.GET;
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20002, 'SUBMIT_REQUEST failed: ' || l_message);
  END IF;

  -- 5. COMMIT is required: the managers only see the FND_CONCURRENT_REQUESTS row after commit
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Request ID: ' || l_req_id);

  -- 6. Optional wait
  IF 'Y' = 'Y' THEN
    l_done := FND_CONCURRENT.WAIT_FOR_REQUEST(
                request_id => l_req_id,
                interval   => 15,
                max_wait   => 1800,
                phase      => l_phase,
                status     => l_status,
                dev_phase  => l_dev_phase,
                dev_status => l_dev_status,
                message    => l_message);
    DBMS_OUTPUT.PUT_LINE('Phase/Status: ' || l_phase || ' / ' || l_status);
    DBMS_OUTPUT.PUT_LINE('Dev phase/status: ' || l_dev_phase || ' / ' || l_dev_status);
    IF NOT l_done THEN
      DBMS_OUTPUT.PUT_LINE('Stopped waiting before completion: ' || l_message);
    ELSIF l_dev_status <> 'NORMAL' THEN
      DBMS_OUTPUT.PUT_LINE('Completed with ' || l_dev_status || ': ' || l_message);
    END IF;
  END IF;
END;
/

-- Lists the Journal Import parameters in submission order on YOUR instance.
-- The argumentN positions in the block below must follow column_seq_num order.
SELECT u.column_seq_num,
       u.end_user_column_name,
       u.form_left_prompt,
       u.required_flag,
       u.default_value
FROM   fnd_descr_flex_col_usage_vl u
WHERE  u.application_id = (SELECT application_id FROM fnd_application WHERE application_short_name = 'SQLGL')
AND    u.descriptive_flexfield_name = '$SRS$.GLLEZL'
ORDER  BY u.column_seq_num;

-- Example only: verify for your release against the query above before use.
-- l_run_id is GL_INTERFACE_CONTROL.INTERFACE_RUN_ID for your source and group.
-- l_access_set_id is the data access set (profile GL: Data Access Set, GL_ACCESS_SET_ID).
-- l_req_id := FND_REQUEST.SUBMIT_REQUEST(
--               application => 'SQLGL',
--               program     => 'GLLEZL',
--               description => NULL,
--               start_time  => NULL,
--               sub_request => FALSE,
--               argument1   => TO_CHAR(l_run_id),         -- interface run id (verify)
--               argument2   => TO_CHAR(l_access_set_id),  -- data access set id (verify)
--               argument3   => 'N',                       -- post errors to suspense (verify)
--               argument4   => NULL,                      -- start date (verify)
--               argument5   => NULL,                      -- end date (verify)
--               argument6   => 'N',                       -- create summary journals (verify)
--               argument7   => 'N');                      -- import descriptive flexfields (verify)
-- COMMIT;