Query GL & SLA

SELECT b.NAME batch, h.NAME journal_entry, h.je_source journal_source,

       h.currency_code entered_currency, h.je_category CATEGORY,

       sla.description, lines.je_line_num line,

       lines.entered_dr entered_dr_account_inquery,

       lines.entered_cr entered_cr_account_inquery, sla.gl_date,

       sla.ae_line_num line_num, sla.accounting_class_code, sla.currency_code,

       sla.entered_dr, sla.entered_cr, sla.accounted_dr, sla.accounted_cr

  FROM gl_period_statuses ps,

       gl_je_lines lines,

       gl_je_headers h,

       gl_je_batches b,

       gl_ledger_relationships lr,

       gl_import_references gir,

       gl_code_combinations_kfv gcc,

       (SELECT xal.gl_sl_link_table, xal.gl_sl_link_id,

               xal.accounting_date gl_date, xal.ae_line_num,

               gcc1.concatenated_segments ACCOUNT, xal.accounting_class_code,

               xal.currency_code, xal.entered_dr, xal.entered_cr,

               xal.accounted_dr, accounted_cr, xah.description

          FROM xla_ae_headers xah,

               xla_ae_lines xal,

               xla_events xe,

               gl_code_combinations_kfv gcc1

         WHERE xah.ae_header_id = xal.ae_header_id

           AND xah.application_id = xal.application_id

           AND xah.event_id = xe.event_id

           AND xah.application_id = xe.application_id

           AND xal.code_combination_id = gcc1.code_combination_id) sla

 WHERE lr.source_ledger_id = lr.target_ledger_id

   AND b.je_batch_id = h.je_batch_id

   AND h.je_header_id = lines.je_header_id

   AND h.ledger_id = lr.source_ledger_id

   AND lines.period_name = ps.period_name

   AND ps.ledger_id = lines.ledger_id

   AND gir.je_batch_id = b.je_batch_id

   AND gir.je_header_id = h.je_header_id

   AND gir.je_line_num = lines.je_line_num

   AND gcc.code_combination_id = lines.code_combination_id

   AND gir.gl_sl_link_id = sla.gl_sl_link_id
   AND gir.gl_sl_link_table = sla.gl_sl_link_table

Create Bank Branch Oracle EBS (Calling API)

DECLARE

   CURSOR c1

   IS

      SELECT bank_name, branch_name, branch_type, ID

        FROM ce_bank_branch_tmp;

   v_return_status   VARCHAR2 (50);

   v_branch_id       NUMBER;

   v_bank_id         NUMBER;

   v_msg_count       NUMBER;

   v_msg_data        VARCHAR2 (200);

BEGIN

   FOR r1 IN c1

   LOOP

      BEGIN

         SELECT hp.party_id

           INTO v_bank_id

           FROM ar.hz_parties hp, ar.hz_party_usg_assignments hpua

          WHERE hp.party_name = r1.bank_name

            AND hp.party_id = hpua.party_id

            AND hpua.party_usage_code = 'BANK';

      EXCEPTION

         WHEN OTHERS

         THEN

            v_bank_id := NULL;

      END;

      ce_bank_pub.create_bank_branch

                 (p_bank_id            => v_bank_id,

                  p_branch_name        => r1.branch_name,

                  p_branch_type        => r1.branch_type,

                  x_branch_id          => v_branch_id,

                  x_return_status      => v_return_status,

                  x_msg_count          => v_msg_count,

                  x_msg_data           => v_msg_data

                                     );

      DBMS_OUTPUT.put_line (   r1.branch_name

                            || ' has branch id '

                            || v_branch_id

                            || ' status '

                            || v_return_status

                            || ' error '

                            || v_msg_data

                           );

      UPDATE ce_bank_branch_tmp

         SET flag = v_return_status,

             note = v_msg_data

       WHERE ID = r1.ID;

   END LOOP;

END;

ce_bank_branch_tmp is staging table which stored bank branch data before upload to oracle bank branch table.

Create Internal Bank Oracle EBS (Calling API)

DECLARE

   CURSOR c1

   IS

      SELECT country_code, bank_name, ID

        FROM ce_bank_tmp;

   v_return_status   VARCHAR2 (50);

   v_bank_id         NUMBER;

   v_msg_count       NUMBER;

   v_msg_data        VARCHAR2 (200);

BEGIN

   FOR r1 IN c1

   LOOP

      ce_bank_pub.create_bank (p_country_code       => r1.country_code,

                               p_bank_name          => r1.bank_name,

                               x_bank_id            => v_bank_id,

                               x_return_status      => v_return_status,

                               x_msg_count          => v_msg_count,

                               x_msg_data           => v_msg_data

                              );

      DBMS_OUTPUT.put_line (   r1.bank_name

                            || ' has bank id '

                            || v_bank_id

                            || ' status '

                            || v_return_status

                            || ' error '

                            || v_msg_data

                           );

      UPDATE ce_bank_tmp

         SET flag = v_return_status,

             note = v_msg_data

       WHERE ID = r1.ID;

   END LOOP;

END;

CE_BANK_TMP is staging table which stored bank data before upload to oracle bank table.