Sample Call Auto invoice for Order number Range
PROCEDURE xx_bdg_ar_autoinvoice (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id NUMBER
)
AS
l_responsibility_id NUMBER;
l_application_id NUMBER;
l_user_id NUMBER;
l_request_id NUMBER;
l_batch_source VARCHAR2 (100);
l_batch_source_id NUMBER;
l_ou NUMBER;
lv_org_code VARCHAR2 (10);
l_onhand_qty number:=0;
lv_max_order_no number;
lv_min_order_no number;
CURSOR c2
IS
SELECT DISTINCT order_number
FROM xx_bdg_so_stg
WHERE status = ‘AR INV INTERFACED’
AND organization_id = p_org_id;
CURSOR c1
IS
SELECT max(order_number) max_order_no , min(order_number) min_order_no
FROM xx_bdg_so_stg
WHERE status = ‘AR INV INTERFACED’
AND organization_id = p_org_id;
BEGIN
—
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_responsibility_id, l_application_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND LOWER (fr.responsibility_name) LIKE
LOWER (‘India Local Receivables’)
AND NVL (TRUNC (frx.end_date), TRUNC (SYSDATE)) >=
TRUNC (SYSDATE);
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘XAVIER’;
apps.fnd_global.apps_initialize (l_user_id,
l_responsibility_id,
l_application_id
);
SELECT organization_code
INTO lv_org_code
FROM org_organization_definitions
WHERE organization_id = p_org_id;
DBMS_OUTPUT.put_line (‘Auto invoice Start’);
FOR c1_rec IN c1
LOOP
SELECT NAME, batch_source_id
INTO l_batch_source, l_batch_source_id
FROM ra_batch_sources_all
WHERE attribute_category = ‘Additional Info’
AND attribute1 = ‘Invoice’
AND attribute2 = lv_org_code
AND end_date IS NULL;
DBMS_OUTPUT.put_line (‘Batch Source Start’);
DBMS_OUTPUT.put_line (‘OU Start’);
l_request_id :=
fnd_request.submit_request
(application => ‘AR’,
program => ‘RAXTRX’,
description => ‘Autoinvoice Import Program Budget NC’,
start_time => SYSDATE,
sub_request => FALSE,
argument1 => ‘MAIN’,
argument2 => ‘T’,
argument3 => l_batch_source_id,
argument4 => l_batch_source,
argument5 => SYSDATE,
argument6 => NULL,
argument7 => NULL,
argument8 => NULL,
argument9 => NULL,
argument10 => NULL,
argument11 => NULL,
argument12 => NULL,
argument13 => NULL,
argument14 => NULL,
argument15 => NULL,
argument16 => NULL,
argument17 => NULL,
argument18 => c1_rec.min_order_no,
argument19 => c1_rec.max_order_no,
argument20 => NULL,
argument21 => NULL,
argument22 => NULL,
argument23 => NULL,
argument24 => NULL,
argument25 => NULL,
argument26 => ‘N’,
argument27 => ‘Y’,
argument28 => NULL,
argument29 => 82
);
—
COMMIT;
end loop;
FOR c2_rec IN c2
LOOP
—
IF l_request_id = 0
THEN
DBMS_OUTPUT.put_line (‘Concurrent request failed to submit’);
UPDATE xx_bdg_so_stg
SET error_message =
‘Error submitting Auto Invoice Import Program. Please resubmit’
WHERE status = ‘AR INV INTERFACED’
AND order_number = c2_rec.order_number
AND organization_id = p_org_id;
COMMIT;
DBMS_OUTPUT.put_line
( ‘Successfully Submitted the Concurrent Request.’
|| l_request_id
);
else
UPDATE xx_bdg_so_stg
SET auto_invoice_request_id = l_request_id,
ar_invoice_creation_date = SYSDATE,
error_message = NULL
WHERE status = ‘AR INV INTERFACED’
AND order_number = c2_rec.order_number
AND organization_id = p_org_id;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘Error While Submitting Concurrent Request ‘
|| TO_CHAR (SQLCODE)
|| ‘-‘
|| SQLERRM
);
END;
