This discussion is archived
4 Replies Latest reply: Oct 1, 2013 3:39 AM by rajkonds RSS

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

731556 Newbie
Currently Being Moderated
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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points