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