Inventory Outbound Interface
CREATE OR REPLACE PROCEDURE bhaskar_inv_out1 (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
f_id IN NUMBER,
t_id IN VARCHAR2
)
AS
CURSOR c1
IS
SELECT msi.segment1 item, msi.inventory_item_id itemid,
msi.description itemdesc, msi.primary_uom_code uom,
ood.organization_name NAME, ood.organization_id ID,
mc.segment1 || ',' || mc.segment2 CATEGORY
FROM mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
WHERE msi.organization_id = ood.organization_id
AND msi.inventory_item_id = mic.inventory_item_id
AND msi.organization_id = mic.organization_id
AND mic.category_id = mc.category_id
AND msi.purchasing_item_flag = 'Y'
AND msi.organization_id BETWEEN f_id AND t_id;
x_id UTL_FILE.file_type;
l_count NUMBER (5) DEFAULT 0;
BEGIN
x_id :=UTL_FILE.fopen ('d:\oracle\proddb\8.1.7\plsql\temp',
'invoutdata.dat',
'W'
);
--select * from v$parameter where name like '%utl_file%'
FOR x1 IN c1
LOOP
l_count := l_count + 1;
UTL_FILE.put_line (x_id,
x1.item
|| '-'
|| x1.itemid
|| '-'
|| x1.itemdesc
|| '-'
|| x1.uom
|| '-'
|| x1.NAME
|| '-'
|| x1.ID
|| '-'
|| x1.CATEGORY
);
END LOOP;
UTL_FILE.fclose (x_id);
fnd_file.put_line (fnd_file.output,
'No of Records transfered to the data file :' || l_count
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output,
'Submitted User name '
|| fnd_profile.VALUE ('USERNAME')
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output,
'Submitted Responsibility name '
|| fnd_profile.VALUE ('RESP_NAME')
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, 'Submission Date :' || SYSDATE);
EXCEPTION
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid operation');
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid path');
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid mode');
UTL_FILE.fclose_all;
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid filehandle');
UTL_FILE.fclose_all;
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, 'read error');
UTL_FILE.fclose_all;
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG, 'internal error');
UTL_FILE.fclose_all;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'other error');
UTL_FILE.fclose_all;
END bhaskar_inv_out1;
No comments:
Post a Comment