XML File Creation Thru PL/SQL

CREATE OR REPLACE PROCEDURE gl_inter_company_trans

(retcode OUT VARCHAR2,

errbuf OUT VARCHAR2,

p_period VARCHAR2,

p_operating_unit VARCHAR2,

p_status1 VARCHAR2)

IS

CURSOR gl_detail

(p_status IN VARCHAR2,

p_period VARCHAR2,

p_operating_unit VARCHAR2)

IS

SELECT receiver, sender, transaction_number, period, entered_date,

description, note, amount, NAME, attribute10, status, gl_date,

sendor_gl_transfer, receiver_gl_transfer, CONTEXT

FROM (SELECT rsub.NAME receiver, ssub.NAME sender,

gl_.transaction_number, gl_.sender_period_name period,

gl_.entered_date, gl_.description, gl_.note,

( NVL (gl_.sender_running_total_dr, 0)

- NVL (gl_.sender_running_total_cr, 0)

) amount,

REPLACE (typ.NAME, '&', '') NAME, gl_.attribute10,

DECODE (gl_.status, 'R', 'Review', 'Approved') status,

gl_.gl_date,

DECODE (gl_.sender_transfer_flag, 'Y', 'Yes', 'No') sendor_gl_transfer,

DECODE (gl_.receiver_transfer_flag, 'Y', 'Yes', 'No') receiver_gl_transfer,

gl_.CONTEXT

FROM gl.gl_iea_transactions gl_,

gl.gl_iea_transaction_types typ,

gl.gl_iea_subsidiaries ssub,

gl.gl_iea_subsidiaries rsub

WHERE typ.transaction_type_id = gl_.transaction_type_id

AND ssub.subsidiary_id = gl_.sending_subsidiary_id

AND rsub.subsidiary_id = gl_.receiving_subsidiary_id

AND gl_.status = 'R'

AND gl_.status = NVL (p_status, gl_.status)

AND gl_.sender_period_name = NVL (p_period, gl_.sender_period_name)

AND ( ssub.NAME = NVL (p_operating_unit, ssub.NAME)

OR rsub.NAME = NVL (p_operating_unit, rsub.NAME)));

p_status VARCHAR2 (100);

v_transaction_num_prev VARCHAR2 (50) := '00000';

v_transaction_num_curr VARCHAR2 (50) := '11111';

BEGIN

BEGIN

IF p_status1 = 'Approved'

THEN

p_status := 'A';

ELSIF p_status1 = 'Review'

THEN

p_status := 'R';

ELSIF p_status1 = 'ALL'

THEN

p_status := NULL;

END IF;

END;

fnd_file.put_line (fnd_file.output, '(?xml version="1.0" encoding="UTF-8"?)');

fnd_file.put_line (fnd_file.output, '(Pending_Transac)'); -- Main Tag

fnd_file.put_line (fnd_file.output, '(PERIOD)' || p_period || '');

fnd_file.put_line (fnd_file.output, '(OPERATING_UNIT)' || p_operating_unit || '(/OPERATING_UNIT)');

fnd_file.put_line (fnd_file.output, '(STATUS)' || p_status1 || '(/STATUS)');

FOR rec_gl_detail IN gl_detail (p_status, p_period, p_operating_unit)

LOOP

v_transaction_num_curr := rec_gl_detail.transaction_number;

IF p_operating_unit IS NOT NULL

THEN

fnd_file.put_line (fnd_file.output, '(G_GL_DETAIL)'); -- Masters tag

fnd_file.put_line (fnd_file.output, '(STATUS)' || rec_gl_detail.status || '(/STATUS)');

fnd_file.put_line (fnd_file.output, '(TRANSACTION_NUMBER)' || rec_gl_detail.transaction_number

'(/TRANSACTION_NUMBER)');

fnd_file.put_line (fnd_file.output, '(ENTERED_DATE)' || rec_gl_detail.entered_date ||

'(/ENTERED_DATE)');

fnd_file.put_line (fnd_file.output, '(GL_DATE)' || rec_gl_detail.gl_date || '(/GL_DATE)');

fnd_file.put_line (fnd_file.output, '(PERIOD)' || rec_gl_detail.period || '(/PERIOD)');

fnd_file.put_line (fnd_file.output, '(NAME)' || rec_gl_detail.NAME || '(/NAME)');

fnd_file.put_line (fnd_file.output, '(SENDER)'  || rec_gl_detail.sender || '(/SENDER)');

fnd_file.put_line (fnd_file.output, '(SENDOR_GL_TRANSFER)' || rec_gl_detail.sendor_gl_transfer ||

'(/SENDOR_GL_TRANSFER)');

fnd_file.put_line (fnd_file.output, '(RECEIVER)' || rec_gl_detail.receiver || '(/RECEIVER)');

fnd_file.put_line (fnd_file.output, '(RECEIVER_GL_TRANSFER)' || rec_gl_detail.receiver_gl_transfer ||

'(/RECEIVER_GL_TRANSFER)');

fnd_file.put_line (fnd_file.output, '(AMOUNT)' || rec_gl_detail.amount || '(/AMOUNT)');

fnd_file.put_line (fnd_file.output, '(DESCRIPTION)' || rec_gl_detail.description || '(/DESCRIPTION)');

fnd_file.put_line (fnd_file.output, '(NOTE)' || rec_gl_detail.note || '(/NOTE)');

fnd_file.put_line (fnd_file.output, '(ATTRIBUTE10)' || rec_gl_detail.attribute10 || '(/ATTRIBUTE10)');

fnd_file.put_line (fnd_file.output, '(CONTEXT)' || rec_gl_detail.CONTEXT || '(/CONTEXT)');

fnd_file.put_line (fnd_file.output, '(/G_GL_DETAIL)');

ELSIF p_operating_unit IS NULL

THEN

IF ( ( v_transaction_num_curr <> v_transaction_num_prev

AND rec_gl_detail.status = 'Approved')

OR rec_gl_detail.status = 'Review')

THEN

fnd_file.put_line (fnd_file.output, '(G_GL_DETAIL)'); -- Masters tag

fnd_file.put_line (fnd_file.output, '(STATUS)' || rec_gl_detail.status || '(/STATUS)');

fnd_file.put_line (fnd_file.output, '(TRANSACTION_NUMBER)' || rec_gl_detail.transaction_number ||

'(/TRANSACTION_NUMBER)');

fnd_file.put_line (fnd_file.output, '(ENTERED_DATE)' || rec_gl_detail.entered_date ||

'(/ENTERED_DATE)');

fnd_file.put_line (fnd_file.output, '(GL_DATE)' || rec_gl_detail.gl_date || '(/GL_DATE)');

fnd_file.put_line (fnd_file.output, '(PERIOD)' || rec_gl_detail.period || '(/PERIOD)');

fnd_file.put_line (fnd_file.output, '(NAME)' || rec_gl_detail.NAME || '(/NAME)');

fnd_file.put_line (fnd_file.output, '(SENDER)' || rec_gl_detail.sender || '(/SENDER)');

fnd_file.put_line (fnd_file.output, '(SENDOR_GL_TRANSFER)' || rec_gl_detail.sendor_gl_transfer ||

'(/SENDOR_GL_TRANSFER)');

fnd_file.put_line (fnd_file.output, '(RECEIVER)' || rec_gl_detail.receiver || '(/RECEIVER)');

fnd_file.put_line (fnd_file.output, '(RECEIVER_GL_TRANSFER)' || rec_gl_detail.receiver_gl_transfer ||

'(/RECEIVER_GL_TRANSFER)');

fnd_file.put_line (fnd_file.output, '(AMOUNT)' || rec_gl_detail.amount || '(/AMOUNT)');

fnd_file.put_line (fnd_file.output, '(DESCRIPTION)' || rec_gl_detail.description || '(/DESCRIPTION)');

fnd_file.put_line (fnd_file.output, '(NOTE)' || rec_gl_detail.note || '(/NOTE)');

fnd_file.put_line (fnd_file.output, '(ATTRIBUTE10)' || rec_gl_detail.attribute10 || '(/ATTRIBUTE10)');

fnd_file.put_line (fnd_file.output, '(CONTEXT)' || rec_gl_detail.CONTEXT || '(/CONTEXT)');

fnd_file.put_line (fnd_file.output, '(/G_GL_DETAIL)');

END IF;

v_transaction_num_prev := v_transaction_num_curr;

END IF;

END LOOP;

fnd_file.put_line (fnd_file.output, '(/Pending_Transac)'); -- End Main Tag

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'Entered into Exception');

END gl_inter_company_trans;

Note: Replace symbol '(' with '<' and ')' with '>' in all above xml Tags

/