Tuesday, October 12, 2010

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;

/

No comments:

Post a Comment