Credit Card Validation Oracle EBS R12

Introduction

Have you ever encountered the below validation failure for credit card in EBS R12.

• Validation failed for the field - Credit Card
• Unable to set up a Credit Card Bank Account for the Customer. Credit Card information on the order is not valid.

Below is the sample block you can use to validate your credit card in Oracle EBS.


We will use standard Oracle Package and Lookup to validate them

• Package : iby_cc_validate
• Oe Lookup : Credit Card

Sample Block

SET serveroutput ON;

DECLARE
  v_fill_chars VARCHAR (3)  := '*-#';
  v_cc_number  VARCHAR (50) := '1234567812345678';
  --'4111*1111 1111-1111#';
  v_api_version   NUMBER           := 1.0;
  v_init_msg_list VARCHAR2 (20000) := ' ';
  v_return_status VARCHAR2 (20000);
  v_msg_count     NUMBER;
  v_msg_data      VARCHAR2 (20000);
  v_clean_cc      VARCHAR (50);
  v_cc_type iby_cc_validate.cctype;
  v_cc_valid   BOOLEAN;
  v_expr_date  DATE        := SYSDATE ();
  v_error_flag VARCHAR2(1) := 'N';
  
BEGIN
  -- Procedure Call to strip unwanted and special characters
  -- checks that illegal characters were not found
  
  iby_cc_validate.stripcc (v_api_version ,
                           v_init_msg_list,
                           v_cc_number,
                           v_fill_chars,
                           v_return_status,
                           v_msg_count,
                           v_msg_data,
                           v_clean_cc );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error THEN
    v_error_flag    := 'Y';
  END IF;
  
  
  -- Procedure Call to validate credit Card Type
  iby_cc_validate.getcctype (v_api_version ,
                             v_init_msg_list ,
                             v_clean_cc ,
                             v_return_status ,
                             v_msg_count ,
                             v_msg_data ,
                             v_cc_type );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error AND v_cc_type = iby_cc_validate.c_invalidcc THEN
    dbms_output.put_line ('Credit card number type is invalid');
    v_error_flag := 'Y';
  ELSE
    dbms_output.put_line ('Credit card Type is Valid.');
  END IF;
  
  
    -- Procedure Call to validate credit card structure
  iby_cc_validate.validatecc (v_api_version ,
                              v_init_msg_list ,
                              v_clean_cc ,
                              v_expr_date ,
                              v_return_status ,
                              v_msg_count ,
                              v_msg_data ,
                              v_cc_valid );
                              
  IF v_cc_valid AND v_return_status != fnd_api.g_ret_sts_unexp_error THEN
    dbms_output.put_line ('Credit card structure is valid.');
  ELSE
    dbms_output.put_line ('Credit card structure is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
 -- Functional Call to returns Credit Card Validation Results using Luhn Algorithm
  IF iby_cc_validate.CheckCCDigits(v_clean_cc) = 0 THEN
    dbms_output.put_line ('Credit card  is valid.');
  ELSE
    dbms_output.put_line ('Credit card  is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
  
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;

Read More

Create Person Party and Org Contact for a Organization Pary in Oracle R12

 --It is always a good idea to initialize apps and set global policy context before using standard API

CREATE OR REPLACE PROCEDURE create_person_party (
   p_in_first_name                IN       VARCHAR2,
   p_in_org_party_id              IN       VARCHAR2,
   p_in_party_number              IN       VARCHAR2,
   p_in_cdh_person_party_number   IN       VARCHAR2,
   x_ou_ebs_per_party_id          OUT      VARCHAR2,
   x_ou_org_contact_id            OUT      VARCHAR2
)
IS
   v_person_rec         hz_party_v2pub.person_rec_type;
   v_org_contact_rec    hz_party_contact_v2pub.org_contact_rec_type;
   x_ou_party_rel_id    NUMBER;
   x_ou_party_id        NUMBER;
   x_ou_party_number    VARCHAR2 (2000);
   x_ou_profile_id      NUMBER;
   x_ou_return_status   VARCHAR2 (2000);
   x_ou_msg_count       NUMBER;
   x_ou_msg_data        VARCHAR2 (2000);
   v_message            VARCHAR2 (5000);
   v_err_message        VARCHAR2 (5000);
   v_ebs_per_party_id   NUMBER                                      := NULL;  --Party ID of the relationship
   v_org_contact_id     NUMBER                                      := NULL;
BEGIN
   fnd_msg_pub.delete_msg (NULL);
   fnd_msg_pub.initialize;
   v_person_rec.person_first_name := p_in_first_name;
   v_person_rec.created_by_module := 'TCA_V2_API';
   hz_party_v2pub.create_person (p_init_msg_list      => 'T',
                                 p_person_rec         => v_person_rec,
                                 x_party_id           => x_ou_party_id,
                                 x_party_number       => x_ou_party_number,
                                 x_profile_id         => x_ou_profile_id,
                                 x_return_status      => x_ou_return_status,
                                 x_msg_count          => x_ou_msg_count,
                                 x_msg_data           => x_ou_msg_data
                                );

   IF x_ou_return_status <> 'S'
   THEN
      FOR j IN 1 .. x_ou_msg_count
      LOOP
         fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
         v_err_message :=
              v_err_message
              || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
      END LOOP;

      DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
   ELSE
      v_ebs_per_party_id := x_ou_party_id;
      v_org_contact_rec.created_by_module := 'TCA_V2_API';
      v_org_contact_rec.party_rel_rec.subject_id := v_ebs_per_party_id;
      v_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
      v_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.object_id := p_in_org_party_id;
      v_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
      v_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
      v_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
      v_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
      v_org_contact_rec.party_rel_rec.start_date := SYSDATE;
      v_org_contact_rec.party_rel_rec.status := 'A';
      hz_party_contact_v2pub.create_org_contact ('T',
                                                 v_org_contact_rec,
                                                 x_ou_org_contact_id,
                                                 x_ou_party_rel_id,
                                                 x_ou_party_id,
                                                 x_ou_party_number,
                                                 x_ou_return_status,
                                                 x_ou_msg_count,
                                                 x_ou_msg_data
                                                );
      COMMIT;

      IF x_ou_return_status <> 'S'
      THEN
         FOR j IN 1 .. x_ou_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
            v_err_message :=
                  v_err_message
               || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
         END LOOP;

         DBMS_OUTPUT.put_line ('v_err_message: ' || v_err_message);
      ELSE
         DBMS_OUTPUT.put_line ('Org Contact ID = ' || x_ou_org_contact_id);
         DBMS_OUTPUT.put_line (' Party Relationship id = ' || x_ou_party_id);
         v_ebs_per_party_id := x_ou_party_id;
         v_org_contact_id := x_ou_org_contact_id;
      END IF;
   END IF;

   x_ou_org_contact_id := v_org_contact_id;
   x_ou_ebs_per_party_id := v_ebs_per_party_id;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END create_person_party;

Read More

Example : get_service_duration API in Oracle R12 Order Management

--It is always a good practice to initialize apps and set global policy context

PROCEDURE get_service_duration (
   p_serv_period          IN              VARCHAR2,
   p_end_date             IN              DATE,
   p_start_date           IN              DATE,
   p_serv_duration        IN              NUMBER,
   p_out_serv_duration    OUT NOCOPY      NUMBER,
   p_out_service_period   OUT NOCOPY      VARCHAR2,
   p_out_end_date         OUT NOCOPY      VARCHAR2,
   p_out_dur_return_sts   OUT NOCOPY      VARCHAR2
)
IS
   v_ou_dur_return_sts   VARCHAR2 (1)            := fnd_api.g_ret_sts_success;
   v_dur_line_rec        oe_oe_form_line.line_rec_type;
   v_dur_null_line_rec   oe_oe_form_line.line_rec_type;
   v_dur_line_rec        oe_oe_form_line.line_rec_type;
BEGIN
   v_dur_line_rec := v_dur_null_line_rec;
   v_dur_line_rec.service_duration := p_serv_duration;
   v_dur_line_rec.service_start_date := p_start_date;
   v_dur_line_rec.service_period := p_serv_period;
   v_dur_line_rec.service_end_date := p_end_date;
   oe_service_util.get_service_duration (x_return_status      => v_dur_return_sts,
                                         p_line_rec           => v_dur_line_rec,
                                         x_line_rec           => v_dur_line_rec
                                        );
   p_out_service_period := v_dur_line_rec.service_period;
   p_out_serv_duration := v_dur_line_rec.service_duration;
   p_out_dur_return_sts := v_dur_return_sts;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END get_service_duration;

Read More

API to create Contact Point ID for a Party in Oracle R12 Example

 -- Make Sure you initialize apps and set mo_global.set_policy_context  

PROCEDURE create_contact_point_id (
      p_in_contact_point_type   IN       VARCHAR2,
      p_in_contact_point        IN       VARCHAR2,
      p_in_party_id             IN       VARCHAR2,
      x_out_cont_point_id       OUT      VARCHAR2
   )
   IS
      v_cpr                   hz_contact_point_v2pub.contact_point_rec_type;
      v_edi_rec               hz_contact_point_v2pub.edi_rec_type;
      v_email_rec             hz_contact_point_v2pub.email_rec_type;
      v_phone_rec             hz_contact_point_v2pub.phone_rec_type;
      v_telex_rec             hz_contact_point_v2pub.telex_rec_type;
      v_web_rec               hz_contact_point_v2pub.web_rec_type;
      x_ou_return_status      VARCHAR2 (2000);
      x_ou_msg_count          NUMBER;
      x_ou_contact_point_id   NUMBER;
      x_ou_msg_data           VARCHAR2 (2000);
      x_ou_output_message     VARCHAR2 (2000)                          := ' ';
      v_message               VARCHAR2 (5000);
      v_err_message           VARCHAR2 (5000);
   BEGIN
 

      IF p_in_contact_point_type = 'PHONE'
      THEN
         v_phone_rec.phone_number := p_in_contact_point;
         v_phone_rec.phone_line_type := 'GEN';
      ELSIF p_in_contact_point_type = 'EMAIL'
      THEN
         v_email_rec.email_format := 'MAILTEXT';
         v_email_rec.email_address := p_in_contact_point;
      END IF;

      /* build contact point rec */
      v_cpr.contact_point_id := NULL;
      v_cpr.contact_point_type := p_in_contact_point_type;
      v_cpr.status := 'A';
      v_cpr.owner_table_name := 'HZ_PARTIES';
      v_cpr.owner_table_id := p_in_party_id;
      v_cpr.primary_flag := 'N';
      v_cpr.created_by_module := 'TCA_V2_API';

      /* call api to create rec */
      IF p_in_contact_point IS NOT NULL
      THEN
         hz_contact_point_v2pub.create_contact_point
                                (p_init_msg_list          => fnd_api.g_true,
                                 p_contact_point_rec      => v_cpr,
                                 p_edi_rec                => v_edi_rec,
                                 p_email_rec              => v_email_rec,
                                 p_phone_rec              => v_phone_rec,
                                 p_telex_rec              => v_telex_rec,
                                 p_web_rec                => v_web_rec,
                                 x_contact_point_id       => x_ou_contact_point_id,
                                 x_return_status          => x_ou_return_status,
                                 x_msg_count              => x_ou_msg_count,
                                 x_msg_data               => x_ou_msg_data
                                );
         COMMIT;
         dbms_output.put_line (x_ou_msg_data);
         dbms_output.put_line (x_ou_return_status);
         dbms_output.put_line (x_ou_msg_count);

         IF x_ou_return_status <> 'S'
         THEN
         
            FOR j IN 1 .. x_ou_msg_count
            LOOP
               fnd_msg_pub.get (j, fnd_api.g_false, x_ou_msg_data, v_message);
               v_err_message :=
                     v_err_message
                  || ('Msg' || TO_CHAR (j) || ': ' || x_ou_msg_data);
            END LOOP;

            dbms_output.put_line ('v_err_message: ' || v_err_message);

         ELSE
            x_out_cont_point_id := x_ou_contact_point_id;
         END IF;
      ELSE
         dbms_output.put_line ('No Valid Value for Contact Point Creation: ');
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         dbms_output.put_line (SQLERRM);
   END create_contact_point_id;

Read More

API to Reprice Order Total in Oracle R12.

 -- Make Sure you initialize apps and set mo_global.set_policy_context   


PROCEDURE reprice_order (p_in_header_id IN NUMBER)
   IS
      v_header_count    NUMBER;
      v_header_list     VARCHAR2 (32000);
      v_line_count      NUMBER;
      v_line_list       VARCHAR2 (32000);
      v_price_level     VARCHAR2 (32000);
      v_return_status   VARCHAR2 (32000);
      v_msg_count       NUMBER;
      v_msg_data        VARCHAR2 (32000);
   BEGIN

      v_header_count := 1;
      v_header_list := TO_CHAR (p_in_header_id);
      v_price_level := 'ORDER';

      BEGIN
         oe_order_adj_pvt.price_action (p_header_count       => v_header_count,
                                        p_header_list        => v_header_list,
                                        p_line_count         => v_line_count,
                                        p_line_list          => v_line_list,
                                        p_price_level        => v_price_level,
                                        x_return_status      => v_return_status,
                                        x_msg_count          => v_msg_count,
                                        x_msg_data           => v_msg_data
                                       );
         COMMIT;

         IF (v_return_status != fnd_api.g_ret_sts_success)
         THEN
            dbms_output.put_line(v_msg_data);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            dbms_output.put_line(sqlerrm);
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
        dbms_output.put_line(sqlerrm);
   END reprice_order;

Read More

Update OSR in Oracle R12 for HZ_PARTY_SITES table

 -- Make Sure you initialize apps and set mo_global.set_policy_context   


set serveroutput on;
DECLARE
      v_xref_rec                hz_orig_system_ref_pub.orig_sys_reference_rec_type;
      v_object_version_number   NUMBER :=1 ;
      v_return_status           VARCHAR2 (240);
      v_msg_count               NUMBER;
      v_msg_data                VARCHAR2 (4000);
      v_error_message           VARCHAR2 (4000) := NULL;
BEGIN
             v_error_message                        := NULL;
             v_return_status                        := NULL;
             v_msg_count                            := NULL;
             v_msg_data                             := NULL;
           
              v_xref_rec.orig_system           := 'Legacy System';
              v_xref_rec.orig_system_reference := '00138888';  --New OSR
              v_xref_rec.owner_table_name      := 'HZ_PARTY_SITES';
              v_xref_rec.owner_table_id        := '3578'; --Party Site ID
              v_xref_rec.old_orig_system_reference := '00138AA2'; --Old OSR
              v_xref_rec.created_by_module         := 'HZ_IMPORT';
           
               hz_orig_system_ref_pub.update_orig_system_reference (
                              p_init_msg_list            => fnd_api.g_true,
                              p_orig_sys_reference_rec   => v_xref_rec,
                              p_object_version_number    => v_object_version_number,
                              x_return_status            => v_return_status,
                              x_msg_count                => v_msg_count,
                              x_msg_data                 => v_msg_data
                            );
               commit;
               IF v_return_status <> fnd_api.g_ret_sts_success
               THEN
                     IF v_msg_count > 1
                     THEN
                            FOR i IN 1 .. v_msg_count
                            LOOP
                                v_error_message      :=    (v_error_message ||fnd_msg_pub.get (p_encoded => fnd_api.g_true));
                            END LOOP;
                     ELSE
                            v_error_message   := v_msg_data;
                     END IF;    
               END IF;

            dbms_output.put_line(v_return_status||' '||'OSR Process failed with error: ' || v_error_message);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More

Creating a Price Adjustment to an Sales Order in Oracle R12

------------------------------------------------------------------------------------------------
--Query to Fetch ID's


select * from fnd_user where user_name like 'ANU%'; --16705
select * from fnd_responsibility_vl where responsibility_name=' XX OM ORDER ENTRY'; --51546  
select * from fnd_application_vl where application_name like '%Order%'; --660
select header_id from oe_order_headers_all;
select line_id from oe_order_lines_all;
select list_header_id from qp_list_headers_tl;
select list_line_id from qp_list_lines;

------------------------------------------------------------------------------------------------
--Call to MAIN API

SET serveroutput on;

DECLARE
   v_api_version_number          NUMBER                                   := 1.0;
   v_init_msg_list               VARCHAR2 (10)                            := fnd_api.g_false;
   v_return_values               VARCHAR2 (10)                            := fnd_api.g_false;
   v_action_commit               VARCHAR2 (10)                            := fnd_api.g_false;
   x_ou_header_val_rec           oe_order_pub.header_val_rec_type;
   x_ou_header_adj_tbl           oe_order_pub.header_adj_tbl_type;
   x_ou_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;
   x_ou_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;
   x_ou_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;
   x_ou_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;
   x_ou_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;
   x_ou_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;
   x_ou_line_val_tbl             oe_order_pub.line_val_tbl_type;
   x_ou_line_adj_tbl             oe_order_pub.line_adj_tbl_type;
   x_ou_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;
   x_ou_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;
   x_ou_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;
   x_ou_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;
   x_ou_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;
   x_ou_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;
   x_ou_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;
   x_ou_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;
   x_ou_action_request_tbl       oe_order_pub.request_tbl_type;
   x_debug_file                  VARCHAR2 (100);
   v_return_status               VARCHAR2 (1);
   v_msg_count                   NUMBER;
   v_msg_data                    VARCHAR2 (100);
   v_line_tbl_index              NUMBER;
   v_header_rec                  oe_order_pub.header_rec_type;
   v_line_tbl                    oe_order_pub.line_tbl_type;
   v_line_adj_tbl                oe_order_pub.line_adj_tbl_type;
   v_action_request_tbl          oe_order_pub.request_tbl_type;
   v_msg_index_out               NUMBER (10);
BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
   mo_global.set_policy_context ('S', 103);
   fnd_global.apps_initialize (16705
                              ,21623
                              ,660
                              );   -- pass in user_id, responsibility_id, and application_id
   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
--   x_debug_file := oe_debug_pub.set_debug_mode ('FILE');
  -- oe_debug_pub.setdebuglevel (5);   -- Use 5 for the most debugging output, I warn  you its a lot of data
   DBMS_OUTPUT.put_line ('START OF NEW DEBUG');
--This is to UPDATE order line
   v_line_tbl_index := 1;
-- Changed attributes
   v_header_rec := oe_order_pub.g_miss_header_rec;
   v_header_rec.header_id := 728232;
   v_header_rec.operation := oe_globals.g_opr_update;
 
   v_line_tbl (v_line_tbl_index) := oe_order_pub.g_miss_line_rec;
   v_line_tbl (v_line_tbl_index).header_id := 728232;
   v_line_tbl (v_line_tbl_index).line_id := 1813117;
   v_line_tbl (v_line_tbl_index).operation := oe_globals.g_opr_update;
 
   v_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
   v_line_adj_tbl (1).operation := oe_globals.g_opr_create;
   v_line_adj_tbl (1).header_id := 728232;   --header_id of the sales order
   v_line_adj_tbl (1).line_id := 1813117;   --line_id of the sales order line
--v_Line_Adj_Tbl(1).price_adjustment_id := 3419268;
--price_adjustment_id is required for update/delete
   v_line_adj_tbl (1).line_index := 1;
   v_line_adj_tbl (1).automatic_flag := 'N';
   v_line_adj_tbl (1).applied_flag := 'Y';
   v_line_adj_tbl (1).updated_flag := 'Y';   --Optional, this is the fixed flag.
   v_line_adj_tbl (1).list_header_id := 330069;   --list_header_id of the adjustment
   v_line_adj_tbl (1).list_line_id := 322476;   --list_line_id of the adjustment
   v_line_adj_tbl (1).list_line_type_code := 'DIS';
   v_line_adj_tbl (1).adjusted_amount := -500;
   v_line_adj_tbl (1).operand := 0;

   -- CALL TO PROCESS ORDER
   oe_order_pub.process_order (p_api_version_number =>          1.0
                              ,p_init_msg_list =>               fnd_api.g_false
                              ,p_return_values =>               fnd_api.g_false
                              ,p_action_commit =>               fnd_api.g_false
                              ,x_return_status =>               v_return_status
                              ,x_msg_count =>                   v_msg_count
                              ,x_msg_data =>                    v_msg_data
                              ,p_header_rec =>                  v_header_rec
                              ,p_line_tbl =>                    v_line_tbl
                              ,p_line_adj_tbl =>                v_line_adj_tbl
                              ,p_action_request_tbl =>          v_action_request_tbl
-- OUT PARAMETERS
   ,                           x_header_rec =>                  v_header_rec
                              ,x_header_val_rec =>              x_ou_header_val_rec
                              ,x_header_adj_tbl =>              x_ou_header_adj_tbl
                              ,x_header_adj_val_tbl =>          x_ou_header_adj_val_tbl
                              ,x_header_price_att_tbl =>        x_ou_header_price_att_tbl
                              ,x_header_adj_att_tbl =>          x_ou_header_adj_att_tbl
                              ,x_header_adj_assoc_tbl =>        x_ou_header_adj_assoc_tbl
                              ,x_header_scredit_tbl =>          x_ou_header_scredit_tbl
                              ,x_header_scredit_val_tbl =>      x_ou_header_scredit_val_tbl
                              ,x_line_tbl =>                    v_line_tbl
                              ,x_line_val_tbl =>                x_ou_line_val_tbl
                              ,x_line_adj_tbl =>                x_ou_line_adj_tbl
                              ,x_line_adj_val_tbl =>            x_ou_line_adj_val_tbl
                              ,x_line_price_att_tbl =>          x_ou_line_price_att_tbl
                              ,x_line_adj_att_tbl =>            x_ou_line_adj_att_tbl
                              ,x_line_adj_assoc_tbl =>          x_ou_line_adj_assoc_tbl
                              ,x_line_scredit_tbl =>            x_ou_line_scredit_tbl
                              ,x_line_scredit_val_tbl =>        x_ou_line_scredit_val_tbl
                              ,x_lot_serial_tbl =>              x_ou_lot_serial_tbl
                              ,x_lot_serial_val_tbl =>          x_ou_lot_serial_val_tbl
                              ,x_action_request_tbl =>          v_action_request_tbl
                              );
   COMMIT;
   DBMS_OUTPUT.put_line (   v_msg_count
                         || 'OM Debug file: '
                         || oe_debug_pub.g_dir
                         || '/'
                         || oe_debug_pub.g_file);
   DBMS_OUTPUT.put_line (v_header_rec.header_id);

   --DBMS_OUTPUT.put_line ('adjustment.return_status IS: ' || x_line_adj_tbl(1).return_status);

   -- Retrieve messages
   FOR i IN 1 .. v_msg_count
   LOOP
      oe_msg_pub.get (p_msg_index =>          i
                     ,p_encoded =>            fnd_api.g_false
                     ,p_data =>               v_msg_data
                     ,p_msg_index_out =>      v_msg_index_out
                     );
      DBMS_OUTPUT.put_line ('message is: ' || v_msg_data);
      DBMS_OUTPUT.put_line ('message index is: ' || v_msg_index_out);
   END LOOP;

-- Check the return status
   IF v_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('Line Quantity Update Successful');
   ELSE
      DBMS_OUTPUT.put_line ('Line Quantity update Failed');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Read More

Create a Salesrep in Oracle R12 from API

set serveroutput on;            
DECLARE
v_api_version            varchar2(100) := '1.0';
v_sales_credit_type_id   number        := 1;
v_salesrep_num             number        := '10129';
v_resource_id             varchar2(100) := '1006433';
v_return_status             varchar2(100);
v_msg_count                 varchar2(100);
v_msg_data                 varchar2(100);
v_salesrep_id            number;
BEGIN
    mo_global.set_policy_context('S',80);    --Always set if running from backend otherwise Salesrep wont be created
   
    jtf_rs_salesreps_pub.create_salesrep       (p_api_version            =>     v_api_version
                                              ,p_resource_id           =>      v_resource_id
                                              ,p_sales_credit_type_id  =>      v_sales_credit_type_id
                                              ,p_salesrep_number       =>      v_salesrep_num
                                              ,x_return_status         =>      v_return_status
                                              ,x_msg_count             =>      v_msg_count
                                              ,x_msg_data              =>      v_msg_data
                                              ,x_salesrep_id           =>      v_salesrep_id
                                              );
                                             
    dbms_output.put_line('Salesrep ID : '||v_salesrep_id);                                         
    dbms_output.put_line(SubStr('x_return_status = '||v_return_status,1,255));
    dbms_output.put_line('x_msg_count = '||TO_CHAR(v_msg_count));
    dbms_output.put_line(SubStr('x_msg_data = '||v_msg_data,1,255));
   
    IF v_msg_count >1 THEN
    FOR I IN 1..v_msg_count
    LOOP
    dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
    END LOOP;
    END IF;                             
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Read More