Calling R12 AR Autoinvoice from PL/SQL

DECLARE

v_req_id NUMBER;

v_current_action VARCHAR2 (50);

v_responsibility_appl_id NUMBER;

v_responsibility_id NUMBER;

v_user_id NUMBER;

v_rtn NUMBER;

arg1 NUMBER := 1;

arg2 VARCHAR2 (50) := :p_org_id;

arg26 VARCHAR2 (50) := 'Y';

v_error_count NUMBER;

l_batch_source_id NUMBER;

l_batch_source_name VARCHAR2 (100);

l_temp_varchar VARCHAR2 (30) := '1';

dphase VARCHAR2 (30);

rphase VARCHAR2 (30);

rstatus VARCHAR2 (30);

dstatus VARCHAR2 (30);

MESSAGE VARCHAR2 (2000);

v_submit_status BOOLEAN;

BEGIN

v_current_action := 'apps initialize';

v_responsibility_appl_id := apps.fnd_profile.VALUE ('RESP_APPL_ID');

v_responsibility_id := apps.fnd_profile.VALUE ('RESP_ID');

DBMS_OUTPUT.put_line ('RESP_ID-' || v_responsibility_id);

DBMS_OUTPUT.put_line ('RESP_APPL_ID-' || v_responsibility_appl_id);

apps.fnd_global.apps_initialize (:p_user_id, v_responsibility_id, v_responsibility_appl_id);

apps.fnd_client_info.set_org_context (:p_org_id);

BEGIN

SELECT batch_source_id, NAME

INTO l_batch_source_id, l_batch_source_name

FROM ra_batch_sources_all

WHERE NAME = :p_source AND org_id = :p_org_id;

-- vm changing this be count from interface lines & increasing limit from 3000 to 8000

SELECT TO_CHAR (LEAST (10, (CEIL ((COUNT (*)) / 8000))))

INTO l_temp_varchar

FROM ra_interface_lines_all

WHERE batch_source_name = :p_source AND org_id = :p_org_id;

EXCEPTION

WHEN OTHERS

THEN

RAISE;

END;

DBMS_OUTPUT.put_line ( l_temp_varchar || ',' || l_batch_source_id || ',' || l_batch_source_name);

IF (l_temp_varchar != '0') THEN

v_rtn :=

apps.FND_REQUEST.SUBMIT_REQUEST

( 'AR'

,'RAXMTR'

,''

,''

,FALSE

, arg1 --arg1

, arg2 --arg2

, l_batch_source_id --arg3

, l_batch_source_name --arg4

,to_char( sysdate , 'YYYY/MM/DD HH:MI:SS' ) --arg5

,'' --arg6

,'' --arg7

,'' --arg8

,'' --arg9

,'' --arg10

,'' --arg11

,'' --arg12

,'' --arg13

,'' --arg14

,'' --arg15

,'' --arg16

,'' --arg17

,'' --arg18

,'' --arg19

,'' --arg20

,'' --arg21

,'' --arg22

,'' --arg23

,'' --arg24

,'' --arg25

,arg26 --arg26

,'' --arg27

,CHR(0) --arg28

,CHR(0) --arg29

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg37

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg47

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg57

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg67

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg77

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg87

,CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0),CHR(0) --arg97

,CHR(0),CHR(0),CHR(0));

COMMIT;

IF v_rtn = 0 THEN

DBMS_OUTPUT.put_line ('Submit request ended with error');

DBMS_OUTPUT.put_line ('Error message : ' || MESSAGE);

ELSE

DBMS_OUTPUT.put_line ('Submit request submitted successfully');

v_submit_status :=

fnd_concurrent.get_request_status

(v_rtn,

'',

'',

rphase,

rstatus,

dphase,

dstatus,

MESSAGE

);

IF dphase = 'COMPLETE'

THEN

DBMS_OUTPUT.put_line ('Sub Request completed successfully');

ELSE

LOOP

--- Check the status

v_submit_status :=

fnd_concurrent.wait_for_request

(v_rtn,

100,

80,

rphase,

rstatus,

dphase,

dstatus,

MESSAGE

);

EXIT WHEN dphase = 'COMPLETE';

END LOOP;

END IF;

END IF;

END IF;

END;

/