Lookup or query the table in Master Data

[Former Member]

Hi experts,

I have an table named Pricing Table in 'system setup->Master Data'. The table has 2 columes ,Product ID and Price,.So i go to 'Queries and Reports -> Browse Full Schema' to see the schema(FCI_SA_CUSTOM_MD3) to write SQL statement but i can not see these 2 fields on the schema. I would like to write script to get Product ID from Product tab and lookup for the price. Can you please suggest me how to do that.

Below is the schema that i can see.

1 OBJECTID NUMBER(10) N  
2 CONTEXTID NUMBER(10) N  
3 REVISION_COUNT NUMBER(10) Y  
4 UNIQUE_DOC_NAME VARCHAR2(96) N  
5 DISPLAY_NAME VARCHAR2(120) Y  
6 COLLN_METADATA VARCHAR2(3000) Y  
7 RESERVATION_LOCK NUMBER(1) Y  
8 DOCUMENT_DESCRIPTION VARCHAR2(768) Y  
9 CREATED_BY_USER_OBJECT_ID NUMBER(10) N  
10 CREATED_BY_USER_CLASS_ID NUMBER(10) N  
11 CREATED_BY_USER_OBJECT_NAME VARCHAR2(120) Y  
12 MODIFIED_BY_USER_OBJECT_ID NUMBER(10) Y  
13 MODIFIED_BY_USER_CLASS_ID NUMBER(10) Y  
14 MODIFIED_BY_USER_OBJECT_NAME VARCHAR2(120) Y  
15 EDIT_BY_USER_OBJECT_ID NUMBER(10) Y  
16 EDIT_BY_USER_CLASS_ID NUMBER(10) Y  
17 EDIT_BY_USER_OBJECT_NAME VARCHAR2(120) Y  
18 CREATED_AT DATE N  
19 MODIFIED_AT DATE Y  
20 EDIT_AT_DATETIME DATE Y  
21 INACTIVE NUMBER(1) Y  
22 TRACK_CHANGES NUMBER(1) Y  
23 COLLABORATION_POLICY NUMBER(10) N  
24 HAS_PUBLIC_DISC NUMBER(1) Y  
25 PARENT_OBJECT_ID NUMBER(10) Y  
26 PARENT_CLASS_ID NUMBER(10) Y  
27 PARENT_OBJECT_NAME VARCHAR2(120) Y  
28 EXTERNAL_ID VARCHAR2(150) N

Thank you in advance,

Noppong

[Former Member · Accepted solution]

Hi

What you are refering to is custom master data component which is created as per specific business requirement and is not standard master data. It usually has the following fields:

External ID

Display Name

Description

Parent ID

In your case we have two possibilities:

1. Extension fields with labels Product ID and Price is created: go to Set up-> Extension Definition, find out the Extended Class for custom master data,go to attributes tab. Here table would be listed in Attribute Database Table.

2. Page Customization is done to change the labels: go to Set up-> User Interface-> Page Customization, find out object for custom master data, in overrides check which field has been assigned labels Product ID and Price.

Regards

Mudit Saini

Edited by: Mudit_UCB on Nov 18, 2011 10:26 AM

[Former Member · Accepted solution]

Hi ,

Thank you so much for helps ,Baski and Mudit,

I went to 'setup->Extension Definition', i have found Pricing table as the extension collection. So i went to the Attribut tab, i have found table like following :

The first line is colume and second line is data in colume.

Attribute Internal Name  | Inactive(Status) | Attribute Display Name | Collection Internal Name | Attribute Database Table
'price_1'		 | Check box	    |  	'Price ($/T)'	     | 	'COLL_PRICE_TBL'	| 'FCI_DYN_$2147483442'

Could you please help calrify my questions :

1. I can use Attribute Database Table ('FCI_DYN_$2147483442') as a table name for the SQL statement, right?.

2. And i can use Attribute Internal Name ('price_1') as the attribute in the table for SQL statement ,right?

3. I have tried to click on the reference button to see class reference, but there is no IAPI available here. How can i use IAPI to lookup/find the Extension Collection? I can not use the following code to get the collection because the extension collection is not in the Class Collections of the document.It returns 'null';

pricingTable = doc.getExtensionCollection("COLL_PRICE_TBL");

Thank you in advance

Noppong ,

Edited by: Noppong Jinbunluphol on Nov 21, 2011 5:38 AM

Edited by: Noppong Jinbunluphol on Nov 21, 2011 5:49 AM

[Former Member · Accepted solution]

Hi

1. Yes, you need to use 'FCI_DYN_$2147483442' as a table name for the SQL statement. You can make use of PARENT_OBJECT_ID field in this table for join operation.

2. Yes, you can use Attribute Internal Name ('price_1') as the attribute in the table for SQL statement.

3. pricingTable = doc.getExtensionCollection("COLL_PRICE_TBL") should not return Null. getExtensionCollection() is the method one use to get access to extension collection, in your case, for master agreement. You can use iterator to get to extension fields within the collection. Script would look something like this:

PriceColl = doc.getExtensionCollection("COLL_PRICE_TBL");

if (PriceColl.size() > 0)

{

PriceCollIter=PriceColl.iterator();

while (PriceCollIter.hasNext())

{

PriceCollMember=PriceCollIter.next();

Price= PriceCollMember.getExtensionField("price_1").get();

}

}

Regards

Mudit Saini

Edited by: Mudit_UCB on Nov 21, 2011 7:43 AM

[Former Member · Accepted solution]

Thank you Mudit, for you reply,

The code 'doc.getExtensionCollection("COLL_PRICE_TBL");' return null since the collection is not the extension collection in the class (Project Document). The pricing table is a extension collection created separately from Project's extention collection. I mean the pricing table is not the extension collection of the Project document. How can i get those collection.

Thank you so much,

Noppong

[Former Member · Accepted solution]

Hi

In order to reach Price custom master data from current project document, you have to use the following functions:

PriceMasterData = IBeanHomeLocator.lookup(session,com.frictionless.api.common.types.ObjectReferenceIfc objRef);

PriceCollRef = PriceMasterData.find(com.frictionless.api.common.types.ObjectReferenceIfc objRef);

PriceColl = PriceCollRef.getExtensionCollection("COLL_PRICE_TBL");

Here com.frictionless.api.common.types.ObjectReferenceIfc objRef refers to IBean reference to the custom master data.Hope this would clarify.

Regards

Mudit Saini

[Former Member · Accepted solution]

Thank a lot Mudit,

But the problem is i could not get the object reference of those extension collection(pricing table) since the the extension collection doesn't have any relationship with the document(Project Document) which i'm working for. It is not a Class Collections in the Project Document.

Thank you,

Noppong

[Former Member · Accepted solution]

Hi All,

I'd like to share the code which works for looking up extension collection which is not the class collection of the document.

tableHome = IBeanHomeLocator.lookup(session,SACustomMD1IBeanHomeIfc.sHOME_NAME); 
table = tableHome.findAll();
tableItr = table.iterator();
for(member : tableItr){
	if(hasValue(member) @and member.getDisplayName().equals("Approver Table")){
		approvalColln = member.getExtensionCollection("APPROV_TBL_TEST");
		approvalCollnItr = approvalColln.iterator();
		for( subMember: approvalCollnItr){		
			logInfo("       Var chem_contacts : "+subMember.getExtensionField("contacts").get());
		}		
	}
}

Thank you for all your help,

Noppong

[vikram_shanmugasundaram]

Hi Noppong,

Just wanted to point out that usage of table names for extension tables (starting with FCI_DYN) is not recommended. In a multi landscape scenario, there is not guarantee that the table name will be the same across the landscapes and as a result your query might not work in all the landscapes.

The correct way to reference an extension table is to use the EXT_TABLE token as suggested by Baski earlier in this thread.

Regards,

Vikram

[Former Member]

Hi,

Looks like you are dealing with field extensions.

if that is the case, the extension will be stored in a different table altogether from the table with standard fields. You can join the standad and extension table to see your values together..

Try using <%EXT_TABLE(logicalName of your class)%> for the extension table name or find the respective extension table defnition table to know the list of fields exactly....

(e.g Select T1.a, T2.b from Standard_Table T1, <%EXT_TABLE(logicalName of your class)%> T2 where T1.C= T2.D and T1.X=?)

Good luck.

Baski

[Former Member]

Thank you Baski and Vikram,

I've tried to use the syntax from your suggest, i've realized that it is work for query befinition but not for scripting. When use the syntax on scripting the error shown like follwing :

Error: java.sql.SQLException: ORA-00903: invalid table name

So i have to use the table name like FCI_DYN_$2147483xxx which SAP doesn't recommended.

Thanks you,

Noppong