4 Replies Latest reply: Oct 1, 2013 5:39 AM by rajkonds RSS

    The column(s), PERSON_ID, must exist when using AS_OPPORTUNITY_PUB

    731556
      Hello,

      When using the AS_OPPORTUNITY_PUB.create_opp_header, I always receive the followning error:
      The column(s), PERSON_ID, must exist. The missing column is either required by the database or required based on the data that is being imported.

      I've no idea why I receive this error. The person_id is not in the parameters of the porcedure nor in the record types used in this procedure.

      Any help woulf be appreciated.

      Many thanks for your help.

      Best regards,

      Nicolas

      Here is the code I use:
      CREATE OR REPLACE PACKAGE Xxgs_Opportunity_Interface AS
      PROCEDURE CREATE_OPPORTUNITY(party_id IN NUMBER);
      END Xxgs_Opportunity_Interface;
      /

      -- ########################################################################################
      -- # Name : XXGS_OPPORTUNITY_INTERFACE
      -- # Description: Package will call AS_OPPORTUNITY_PUB
      -- #
      -- # History:
      -- # Date Author Ver Comments
      -- # ----------- ---------------- ---- ---------------------------
      -- # 26-Oct-2009 N. Debusschere 1.0 Created
      -- #########################################################################################
      CREATE OR REPLACE PACKAGE BODY Xxgs_Opportunity_Interface AS

      PROCEDURE CREATE_OPPORTUNITY(party_id IN NUMBER) AS

      -- Opportunity Header
      l_header_rec      As_Opportunity_Pub.HEADER_REC_TYPE;
      l_profile_tbl                As_Utility_Pub.Profile_Tbl_Type;
      l_profile_rec               As_Utility_Pub.profile_rec_type;
      l_admin_group_id          NUMBER;
      l_identity_salesforce_id NUMBER;
      l_salesgroup_id               NUMBER;
      l_partner_cont_party_id     NUMBER;
      x_lead_id                    NUMBER;
      -- ***** Add logic to get the id's from tables ******
      l_customer_id               NUMBER := 87298;
      l_address_id               NUMBER := 29030;
      l_sales_methodology_id     NUMBER := 10026;
      l_owner_salesforce_id     NUMBER := 100009063;
      l_owner_sales_group_id     NUMBER := 100000060;
      l_sales_stage_id          NUMBER := 20;
      l_price_list_id               NUMBER := 31074;
      l_description                VARCHAR2(50) := 'TEST NDE';

      -- Opportunity Line
      l_line_tbl                     As_Opportunity_Pub.Line_Tbl_Type;
      l_line_rec                    As_Opportunity_Pub.line_rec_type;
      x_line_out_tbl                As_Opportunity_Pub.Line_Out_Tbl_Type;
      -- ***** Add logic to get the id's from tables ******
      -- Cursor to get Item properties
      CURSOR cur_inv_item(item_name IN VARCHAR2) -- Get inventory_item_id from Master items
      IS
      SELECT DISTINCT inventory_item_id, primary_uom_code, description, tax_code, service_item_flag, serviceable_product_flag
      FROM MTL_SYSTEM_ITEMS_FVL
      WHERE segment1 = item_name;--'PW25'
      l_inventory_item_id          NUMBER := 17314;
      l_uom_code VARCHAR2(5) := 'pc';
      l_item_description VARCHAR2(240);
      l_tax_code VARCHAR2(10);
      l_service_item_flag VARCHAR2(1);
      l_serviceable_item_flag VARCHAR2(1);

      x_Return_Status VARCHAR2(25);
      x_Msg_Count NUMBER;
      x_Msg_Data VARCHAR2(2000) := '';
      l_step                         VARCHAR2(50);
      l_msg_index_out      NUMBER;
      l_total_error           VARCHAR2(2000) := '';

      l_user_id                    NUMBER := 2411; -- APPS user Fnd_Global.user_id;
      l_resp_id                    NUMBER := 25162; -- ASO_SALES_MANAGER = 21710 / SALES_USER = 25162 / GS SALES MGR = 50863
      l_appl_id                    NUMBER := 697; -- Order Capture (ASO)
      l_appl_short_name           VARCHAR2(10) := 'ASO'; -- Order Capture
      l_org_id                    NUMBER := 103; -- Secretariat Social
      l_organization_id           NUMBER := 103; -- Org for item validation

      CURSOR cur_customer--(p_cust_ref IN VARCHAR2)
      IS
      SELECT hp.party_id, hps.party_site_id
      FROM hz_parties hp
      , hz_party_sites hps
      WHERE hp.party_id = hps.party_id;
      rec_customer cur_customer%ROWTYPE;

      CURSOR cur_org(org_name IN VARCHAR2)
      IS
      SELECT organization_id
      FROM HR_ALL_ORGANIZATION_UNITS
      WHERE NAME = org_name;


      BEGIN
      -- Init
      DBMS_APPLICATION_INFO.SET_CLIENT_INFO('103');
      Fnd_Global.APPS_INITIALIZE(l_user_id, l_resp_id, l_appl_id);
      Mo_Global.INIT;--(l_appl_short_name);

      l_header_rec.last_update_date := SYSDATE;
      l_header_rec.last_updated_by := l_user_id;
      l_header_rec.creation_date := SYSDATE;
      l_header_rec.created_by := l_user_id;
      l_header_rec.description := l_description;
      l_header_rec.customer_id := l_customer_id;
      l_header_rec.address_id := l_address_id;
      l_header_rec.sales_stage_id := l_sales_stage_id;
      l_header_rec.win_probability := 0;
      l_header_rec.status_code := 'NEW_OPPORTUNITY';
      l_header_rec.channel_code := 'DIRECT';
      l_header_rec.currency_code := 'EUR';
      l_header_rec.to_currency_code := 'EUR';
      l_header_rec.owner_salesforce_id := l_owner_salesforce_id;
      l_header_rec.owner_sales_group_id := l_owner_sales_group_id;
      l_header_rec.Auto_Assignment_Type := 'TAP';
      l_header_rec.Sales_Methodology_Id := l_Sales_Methodology_Id;
      l_header_rec.ORG_ID := l_org_id;

      l_step := 'Create header';

      As_Opportunity_Pub.create_opp_header(p_api_version_number => 2.0,
                                                   p_init_msg_list          => Fnd_Api.G_FALSE,
                                              p_commit      => Fnd_Api.G_FALSE,
                                              p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
                                              p_header_rec => l_header_rec,
                                              p_check_access_flag => 'Y',
                                              p_admin_flag          => 'N',
                                              p_admin_group_id     => l_admin_group_id,
                                              p_identity_salesforce_id => l_identity_salesforce_id,
                                              p_salesgroup_id          => l_salesgroup_id,
                                              p_partner_cont_party_id => NULL,--l_partner_cont_party_id,
                                              p_profile_tbl          => l_profile_tbl,
                                              x_return_status => x_return_status,
                                              x_msg_count => x_msg_count,
                                              x_msg_data => x_msg_data,
                                              x_lead_id => x_lead_id);
      l_step := 'After API create header';
      -- Check result...
      IF x_return_status <> 'S'
      THEN
      FOR i IN 1 .. x_msg_count
      LOOP
      Fnd_Msg_Pub.get ( p_msg_index => i
      ,p_encoded => 'F'
      ,p_data => x_msg_data
      ,p_msg_index_out => l_msg_index_out
      );
      l_total_error := SUBSTR(l_total_error || x_msg_data,1,2000);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Header not created - ' || x_return_status || ' - ' || l_total_error);
      DBMS_OUTPUT.PUT_LINE('Step = ' || l_step );
      END IF;

      EXCEPTION
      WHEN OTHERS
      THEN
      DBMS_OUTPUT.PUT_LINE('Error while creating opportunity at step ' || l_step || ' - ' || SQLERRM);
      END CREATE_OPPORTUNITY;

      END;