AP to XLA GL Query

Account Payables to XLA sunledger tables ...

Xla_Distribution_Links table

AP Invoices:-

Select distinct api.invoice_num ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,gcc.segment1 --,xdl.accounting_line_code
from gl_import_references gir ,xla_ae_lines xal ,xla_ae_headers xah ,xla_distribution_links xdl ,ap_invoices_all api ,ap_invoice_lines_all apl ,ap_invoice_distributions_all apd ,gl_je_lines gjl ,gl_je_headers gjh ,gl_code_combinations gcc where api.invoice_id = apl.invoice_id and apl.invoice_id = apd.invoice_id and apl.line_number = apd.invoice_line_number and gir.gl_sl_link_table = xal.gl_sl_link_table and gir.gl_sl_link_id = xal.gl_sl_link_id and xal.ae_header_id = xah.ae_header_id and xdl.ae_header_id = xah.ae_header_id and gjl.je_header_id = gjh.je_header_id and gjl.je_header_id = gir.je_header_id and gjl.je_line_num = gir.je_line_num and xdl.event_id = apd.accounting_event_id and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id and xdl.source_distribution_type = 'AP_INV_DIST' and xal.gl_sl_link_table = 'XLAJEL' and xah.event_type_code <> 'MANUAL' and gjh.je_source = 'Payables' and gjh.je_category = 'Purchase Invoices' and gcc.code_combination_id = gjl.code_combination_id --and api.invoice_num = 'IE-365010' ;

AP (Prepayment Lines) :-

select distinct api.invoice_num ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,gcc.segment1 --,xdl.accounting_line_code from gl_import_references gir ,xla_ae_lines xal ,xla_ae_headers xah ,xla_distribution_links xdl ,ap_invoices_all api ,ap_invoice_lines_all apl ,ap_invoice_distributions_all apd ,gl_je_lines gjl ,gl_je_headers gjh ,gl_code_combinations gcc where api.invoice_id = apl.invoice_id and apl.invoice_id = apd.invoice_id and apl.line_number = apd.invoice_line_number and gir.gl_sl_link_table = xal.gl_sl_link_table and gir.gl_sl_link_id = xal.gl_sl_link_id and xal.ae_header_id = xah.ae_header_id and xdl.ae_header_id = xah.ae_header_id and gjl.je_header_id = gjh.je_header_id and gjl.je_header_id = gir.je_header_id and gjl.je_line_num = gir.je_line_num and xdl.event_id = apd.accounting_event_id and xdl.applied_to_dist_id_num_1 = apd.prepay_distribution_id and xdl.source_distribution_type = 'AP_PREPAY' and xal.gl_sl_link_table = 'XLAJEL' and xah.event_type_code <> 'MANUAL' and gjh.je_source = 'Payables' and gjh.je_category = 'Purchase Invoices' and gcc.code_combination_id = gjl.code_combination_id --and api.invoice_num = 'IE-365010' ;

AP (Payment) :-

select distinct aca.checkrun_name ,aca.check_number ,gjl.entered_cr ,gjl.entered_dr ,gjl.accounted_cr ,gjl.accounted_dr ,gjh.name journal_name ,gjh.creation_date journal_creation_date ,gcc.segment1 --,xdl.accounting_line_code from ap_payment_hist_dists aphd ,ap_payment_history_all aph ,ap_checks_all aca ,xla_distribution_links xdl ,xla_ae_lines xal ,xla_ae_headers xah ,gl_import_references gir ,gl_je_lines gjl ,gl_je_headers gjh ,gl_je_batches gjb ,gl_code_combinations gcc where xal.ae_header_id = xah.ae_header_id and xdl.ae_header_id = xah.ae_header_id and xdl.ae_line_num = xal.ae_line_num and xdl.ae_header_id = xal.ae_header_id and xal.gl_sl_link_table = gir.gl_sl_link_table and xal.gl_sl_link_id = gir.gl_sl_link_id and gir.je_header_id = gjl.je_header_id and gir.je_line_num = gjl.je_line_num and gjl.code_combination_id = gcc.code_combination_id and gjl.je_header_id = gjh.je_header_id and gjh.je_batch_id = gjb.je_batch_id and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1 and xdl.source_distribution_type = 'AP_PMT_DIST' and gjh.je_source = 'Payables' and gjh.je_category = 'Payments' and xah.event_type_code <> 'MANUAL' and xal.gl_sl_link_table = 'XLAJEL' and aphd.payment_history_id = aph.payment_history_id and aph.check_id = aca.check_id --and aca.check_number = '25467' ;

R12 - How to link GL data to the subledger data or vice versa

gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)

gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)

xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)

xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)

xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)

xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id

For Example:
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.

xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

Note:
a) There is an index on xla.xla_transaction_entities on the following columns.
application_id
entity_code
ledger_id
nvl(source_id_int_1,-99)
nvl(source_id_int_2,-99)
nvl(source_id_int_3,-99)
nvl(source_id_int_4,-99)
nvl(source_id_char_1,' ')
...
nvl(source_id_char_4,' ')

b) Use application_id filter wherever it is possible, as above mentioned XLA tables are partitioned by that.

Query for Ledger, OU, Legal Enity

Query for Ledger, OU, Legal Enity, balancing segment:

SELECT hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units   tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

API to get Customer Credit Exposure Amount in Oracle Apps

DECLARE
   V_HOLD_ID                NUMBER          DEFAULT 50;
   L_CUST_TOTAL_EXPOSURE    NUMBER;
   P_CREDIT_CHECK_RULE_ID   NUMBER;
   V_MSG_DATA               VARCHAR2 (4000);
   L_RETURN_STATUS          VARCHAR2 (30);
BEGIN
   MO_GLOBAL.INIT ('ONT');
   FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 1110,
                               RESP_ID           => 50678,
                               RESP_APPL_ID      => 7000
                              );
   MO_GLOBAL.SET_POLICY_CONTEXT ('S', 83);
 
   DBMS_OUTPUT.PUT_LINE ('Calling the API to get customr exposure amount');
 
    OE_CREDIT_ENGINE_GRP.GET_CUSTOMER_EXPOSURE
       (P_CUSTOMER_ID                     => 1590,     ---- Customer Account ID
        P_SITE_ID                                   => NULL,  ---- Customer Site ID
        P_LIMIT_CURR_CODE             => 'INR',    ---- Currency Code
        P_CREDIT_CHECK_RULE_ID  => 1041,    ---- Credit_Check_Rule_ID from table OE_CREDIT_CHECK_RULES
        X_TOTAL_EXPOSURE              => L_CUST_TOTAL_EXPOSURE,
        X_RETURN_STATUS                 => L_RETURN_STATUS
       );

   IF L_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS
   THEN
      DBMS_OUTPUT.PUT_LINE ('success:');
      DBMS_OUTPUT.PUT_LINE (   'l_total_exposure_amount - '
                            || L_CUST_TOTAL_EXPOSURE
                           );
      COMMIT;
   ELSIF L_RETURN_STATUS IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE ('Status is null');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Failed: ' || V_MSG_DATA);

      FOR I IN 1 .. OE_MSG_PUB.COUNT_MSG
      LOOP
         V_MSG_DATA := OE_MSG_PUB.GET (P_MSG_INDEX => I, P_ENCODED => 'F');
         DBMS_OUTPUT.PUT_LINE (I || ') ' || V_MSG_DATA);
      END LOOP;

      ROLLBACK;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error is ' || SQLCODE || '---' || SQLERRM);
END;

Query to get Year Calendar

SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
         "Tue", "Wed", "Thu", "Fri", "Sat"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   TO_CHAR (dt + 1, 'iw') week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sun",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mon",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tue",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Wed",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Thu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fri",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sat"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

API to Create Item Cost in OPM

GMF_ITEMCOST_PUB.CREATE_ITEM_COST API is used to create item cost in Oracle Process Manufacturing.
This API creates a new item cost in the Cost Details table. [ Table = CM_CMPT_DTL]


The GMF_ITEMCOST_PUB package defines and uses the following custom PL/SQL
Record Types and INDEX BY table Types:
TYPE header_rec_type IS RECORD
(
period_id cm_cmpt_dtl.period_id%TYPE,
calendar_code cm_cldr_hdr_b.calendar_code%TYPE,
period_code cm_cldr_dtl.period_code%TYPE,
cost_type_id cm_cmpt_dtl.cost_type_id%TYPE,
cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE,
organization_id cm_cmpt_dtl.organization_id%TYPE,
organization_code mtl_parameters.organization_code%TYPE,
inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
item_number mtl_item_flexfields.item_number%TYPE,
user_name fnd_user.user_name%TYPE
);


TYPE this_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
burden_ind NUMBER,
total_qty NUMBER,
costcalc_orig NUMBER,
rmcalc_type NUMBER,
delete_mark NUMBER,
attribute1 cm_cmpt_dtl.attribute1%TYPE,
attribute2 cm_cmpt_dtl.attribute2%TYPE,
attribute3 cm_cmpt_dtl.attribute3%TYPE,
attribute4 cm_cmpt_dtl.attribute4%TYPE,
attribute5 cm_cmpt_dtl.attribute5%TYPE,
attribute6 cm_cmpt_dtl.attribute6%TYPE,
attribute7 cm_cmpt_dtl.attribute7%TYPE,
attribute8 cm_cmpt_dtl.attribute8%TYPE,
attribute9 cm_cmpt_dtl.attribute9%TYPE,
attribute10 cm_cmpt_dtl.attribute10%TYPE,
attribute11 cm_cmpt_dtl.attribute11%TYPE,
attribute12 cm_cmpt_dtl.attribute12%TYPE,
attribute13 cm_cmpt_dtl.attribute13%TYPE,
attribute14 cm_cmpt_dtl.attribute14%TYPE,
attribute15 cm_cmpt_dtl.attribute15%TYPE,
attribute16 cm_cmpt_dtl.attribute16%TYPE,
attribute17 cm_cmpt_dtl.attribute17%TYPE,
attribute18 cm_cmpt_dtl.attribute18%TYPE,
attribute19 cm_cmpt_dtl.attribute19%TYPE,
attribute20 cm_cmpt_dtl.attribute20%TYPE,
attribute21 cm_cmpt_dtl.attribute21%TYPE,
attribute22 cm_cmpt_dtl.attribute22%TYPE,
attribute23 cm_cmpt_dtl.attribute23%TYPE,
attribute24 cm_cmpt_dtl.attribute24%TYPE,
attribute25 cm_cmpt_dtl.attribute25%TYPE,
attribute26 cm_cmpt_dtl.attribute26%TYPE,
attribute27 cm_cmpt_dtl.attribute27%TYPE,
attribute28 cm_cmpt_dtl.attribute28%TYPE,
attribute29 cm_cmpt_dtl.attribute29%TYPE,
attribute30 cm_cmpt_dtl.attribute30%TYPE,
attribute_category cm_cmpt_dtl.attribute_category%TYPE
);

TYPE this_level_dtl_tbl_type IS TABLE OF this_level_dtl_rec_type INDEX
BY BINARY_INTEGER;
TYPE lower_level_dtl_rec_type IS RECORD
(
cmpntcost_id NUMBER,
cost_cmpntcls_id NUMBER,
cost_cmpntcls_code cm_cmpt_mst.cost_cmpntcls_code%TYPE,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cmpnt_cost NUMBER,
delete_mark NUMBER
);
TYPE lower_level_dtl_tbl_type IS TABLE OF lower_level_dtl_rec_type
INDEX BY BINARY_INTEGER;


TYPE costcmpnt_ids_rec_type IS RECORD
(
cost_cmpntcls_id NUMBER,
cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
cost_level NUMBER,
cmpntcost_id NUMBER
);

TYPE costcmpnt_ids_tbl_type IS TABLE OF costcmpnt_ids_rec_type INDEX
BY BINARY_INTEGER;

PL - SQL Code for API:-


ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

DECLARE
   L_STATUS          VARCHAR2 (11);
   L_RETURN_STATUS   VARCHAR2 (11)               := FND_API.G_RET_STS_SUCCESS;
   L_COUNT           NUMBER (10);
   L_RECORD_COUNT    NUMBER (10)                               := 0;
   L_LOOP_CNT        NUMBER (10)                               := 0;
   L_DUMMY_CNT       NUMBER (10)                               := 0;
   L_DATA            VARCHAR2 (3000);
   L_MSG_INDEX_OUT   NUMBER;
   L_HEADER_REC      GMF_ITEMCOST_PUB.HEADER_REC_TYPE;
   L_THIS_LVL_TBL    GMF_ITEMCOST_PUB.THIS_LEVEL_DTL_TBL_TYPE;
   L_LOWER_LVL_TBL   GMF_ITEMCOST_PUB.LOWER_LEVEL_DTL_TBL_TYPE;
   L_COSTCMPNT_IDS   GMF_ITEMCOST_PUB.COSTCMPNT_IDS_TBL_TYPE;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE (USER_ID           => 1110,
                               RESP_ID           => 50687,
                               RESP_APPL_ID      => 555
                              );
                             
   L_HEADER_REC.PERIOD_ID           := 141;
   L_HEADER_REC.CALENDAR_CODE       := 'CST_CAL';
   L_HEADER_REC.PERIOD_CODE         := 'JUN 2012'; --NULL;
   L_HEADER_REC.COST_TYPE_ID        := 1000;
   L_HEADER_REC.COST_MTHD_CODE      := 'PMAC';   -- NULL;
   L_HEADER_REC.ORGANIZATION_ID     := 229;
   L_HEADER_REC.ORGANIZATION_CODE   := 'R02'; --NULL;
   L_HEADER_REC.INVENTORY_ITEM_ID   := 2005;
--L_HEADER_REC.ITEM_NUMBER          := NULL;
   L_HEADER_REC.USER_NAME           := 'USER1';
 
   L_THIS_LVL_TBL (1).CMPNTCOST_ID          := 21188;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_ID      := 1;
   L_THIS_LVL_TBL (1).COST_CMPNTCLS_CODE    := 'MATERIAL';
   L_THIS_LVL_TBL (1).COST_ANALYSIS_CODE    := 'DIR';
   L_THIS_LVL_TBL (1).CMPNT_COST            := 60;
   L_THIS_LVL_TBL (1).BURDEN_IND            := 0;
   L_THIS_LVL_TBL (1).TOTAL_QTY             := 0;
   L_THIS_LVL_TBL (1).COSTCALC_ORIG         := 1;
   L_THIS_LVL_TBL (1).RMCALC_TYPE           := 1;
   L_THIS_LVL_TBL (1).DELETE_MARK           := 0;
 
   DBMS_OUTPUT.PUT_LINE ('Start');
   GMF_ITEMCOST_PUB.CREATE_ITEM_COST
                                    (P_API_VERSION              => 3.0,
                                     P_INIT_MSG_LIST            => FND_API.G_FALSE,
                                     P_COMMIT                   => FND_API.G_FALSE,
                                     X_RETURN_STATUS            => L_STATUS,
                                     X_MSG_COUNT                => L_COUNT,
                                     X_MSG_DATA                 => L_DATA,
                                     P_HEADER_REC               => L_HEADER_REC,
                                     P_THIS_LEVEL_DTL_TBL       => L_THIS_LVL_TBL,
                                     P_LOWER_LEVEL_DTL_TBL      => L_LOWER_LVL_TBL,
                                     X_COSTCMPNT_IDS            => L_COSTCMPNT_IDS
                                    );
   DBMS_OUTPUT.PUT_LINE ('Status:' || L_STATUS);
   DBMS_OUTPUT.PUT_LINE ('Debug:' || L_COUNT);

   IF L_STATUS = 'S'
   THEN
      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('success!!');
   ELSE
      IF L_COUNT = 1
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error 11111 ' || L_DATA);
      ELSE
         DBMS_OUTPUT.PUT_LINE (   'status: '
                               || L_STATUS
                               || ' Error Count '
                               || L_COUNT
                              );

         FOR I IN 1 .. L_COUNT
         LOOP
            FND_MSG_PUB.GET (P_MSG_INDEX          => I,
                             P_DATA               => L_DATA,
                             P_ENCODED            => FND_API.G_FALSE,
                             P_MSG_INDEX_OUT      => L_MSG_INDEX_OUT
                            );
            DBMS_OUTPUT.PUT_LINE ('Error 2222: ' || SUBSTR (L_DATA, 1, 255));
         END LOOP;
      END IF;
   END IF;

   DBMS_OUTPUT.PUT_LINE ('end of procedure');
END;

API for Bank Account Creation in Oracle Apps

CE_BANK_PUB.CREATE_BANK_ACCT API is used to create a bank account for the branch party id passed as an input parameter. The API uses the BANKACCT_REC_TYPE record to pass the input values. On successful creation of bank account, the API returns the bank account id along with the information /error messages. The API returns a null bank account id if the bank branch is not created.



DECLARE

   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (200);
   p_count           NUMBER;

BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;

   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'INR';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;

   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      => p_init_msg_list,
                   p_acct_rec           => p_acct_rec,
                   x_acct_id            => x_acct_id,
                   x_return_status      => x_return_status,
                   x_msg_count          => x_msg_count,
                   x_msg_data           => x_msg_data
                  );
                       
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;
END;