Monday, August 30, 2010

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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.