UTL file for AR invoice Outbound to EBS
CREATE OR REPLACE PROCEDURE APPS.xx_mintifi_sales_inv_ext (
p_from_date IN DATE,
p_to_date IN DATE,
p_org_id IN NUMBER
)
AS
x_id UTL_FILE.file_type;
x_file VARCHAR2(4000);
l_count NUMBER;
CURSOR c1
IS
SELECT rct.org_id, hou.NAME company,
EXTRACT (YEAR FROM rct.trx_date) inv_year,
EXTRACT (MONTH FROM rct.trx_date) inv_month,
rct.trx_date invoice_date, rct.trx_number invoice_number,
rctl.description item_desc, rctl.inventory_item_id,
rct.bill_to_customer_id, rctl.extended_amount line_amount,
arps.due_date, rac.customer_number, rcd.code_combination_id,
rtt.NAME inv_type, rcd.gl_date, rat.NAME payment_term,
rct.invoice_currency_code,
EXTRACT (YEAR FROM rcd.gl_date) fiscal_year,
rct.ct_reference inv_reference,
EXTRACT (MONTH FROM rcd.gl_date) period,
gck.segment1
|| ‘-‘
|| gck.segment2
|| ‘-‘
|| gck.segment3
|| ‘-‘
|| gck.segment4
|| ‘-‘
|| gck.segment5 inv_account
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rcd,
ar_payment_schedules_all arps,
ra_customers rac,
gl_code_combinations_kfv gck,
ra_cust_trx_types_all rtt,
ra_terms rat,
hr_operating_units hou
WHERE 1 = 1
AND rct.customer_trx_id = rctl.customer_trx_id
AND rct.customer_trx_id = rcd.customer_trx_id
AND rct.customer_trx_id = arps.customer_trx_id
AND rct.bill_to_customer_id = arps.customer_id
AND rct.bill_to_site_use_id = arps.customer_site_use_id
AND rct.bill_to_customer_id = rac.customer_id
AND rcd.customer_trx_line_id = rctl.customer_trx_line_id
AND rcd.code_combination_id = gck.code_combination_id
AND rct.cust_trx_type_id = rtt.cust_trx_type_id
AND rtt.end_date IS NULL
AND rct.term_id = rat.term_id
AND rtt.legal_entity_id = rct.org_id –204
AND hou.organization_id = rct.org_id
— and rct.BILL_TO_CUSTOMER_ID=1001
AND rcd.gl_date BETWEEN p_from_date AND p_to_date –204
AND rct.org_id = p_org_id;
BEGIN
/*
SELECT description
INTO l_dir
FROM fnd_lookup_values_vl
WHERE lookup_type = ‘XX_FUSION_LOOKUPS’ AND lookup_code = ‘JVI_DIR’;
SELECT description
INTO l_ledger_id
FROM fnd_lookup_values_vl
WHERE lookup_type = ‘XX_FUSION_LOOKUPS’ AND lookup_code = ‘LEDGER_ID’;
*/
x_file:= ‘ARInterface’||xx_mintifi_s.nextval;
x_id := UTL_FILE.fopen (‘UTL_DIR’, x_file||’.csv’, ‘W’);
DBMS_OUTPUT.put_line (‘START’);
FOR x1 IN c1
LOOP
UTL_FILE.put_line (x_id,
x1.company
|| ‘,’
|| x1.inv_year
|| ‘,’
|| x1.invoice_date
|| ‘,’
|| x1.invoice_number
|| ‘,’
|| x1.item_desc
|| ‘,’
|| x1.line_amount
|| ‘,’
|| x1.due_date
|| ‘,’
|| x1.customer_number
|| ‘,’
|| x1.inv_type
|| ‘,’
|| x1.gl_date
|| ‘,’
|| x1.payment_term
|| ‘,’
|| x1.invoice_currency_code
|| ‘,’
|| x1.fiscal_year
|| ‘,’
|| x1.inv_reference
|| ‘,’
|| x1.period
|| ‘,’
|| x1.inv_account
);
l_count := c1%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.put_line (l_count);
fnd_file.put_line (fnd_file.output, ‘TOTAL RECORD:’ || l_count);
UTL_FILE.fclose (x_id);
EXCEPTION
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line (‘invalid operation’);
fnd_file.put_line (fnd_file.LOG, ‘invalid operation’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line (‘invalid path’);
fnd_file.put_line (fnd_file.LOG, ‘invalid path’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_mode
THEN
DBMS_OUTPUT.put_line (‘invalid mode’);
fnd_file.put_line (fnd_file.LOG, ‘invalid mode’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.invalid_filehandle
THEN
DBMS_OUTPUT.put_line (‘invalid filehandle’);
fnd_file.put_line (fnd_file.LOG, ‘invalid filehandle’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.put_line (‘read error’);
fnd_file.put_line (fnd_file.LOG, ‘read error’);
UTL_FILE.fclose_all;
RAISE;
WHEN UTL_FILE.internal_error
THEN
DBMS_OUTPUT.put_line (‘internal error’);
fnd_file.put_line (fnd_file.LOG, ‘internal error’);
UTL_FILE.fclose_all;
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘other error’);
fnd_file.put_line (fnd_file.LOG, ‘other error’);
UTL_FILE.fclose_all;
RAISE;
END xx_mintifi_sales_inv_ext;
/
