1 PACKAGE mtl_related_items_pub AUTHID CURRENT_USER AS
2 /* $Header: INVPRITS.pls 120.3.12020000.2 2012/10/03 11:13:58 ccsingh ship $ */
3 /*#
4 * This package provides functionality to import Related Items in to
5 * Oracle System.
6 *
7 * <B>Constants: </B> All constants that are unqualified belong to
8 * the package MTL_RELATED_ITEMS_PUB.
9 *
10 * <B>Standard parameters:</B> Several standard parameters are
11 * used throughout the APIs below:
12 * <ul>
13 * <li>p_init_msg_list: A one-character flag indicating whether to initialize the
14 * FND_MSG_PUB package's message stack at the beginning of API processing(which
15 * removes any messages that may exist on the stack from prior processing in the
16 * same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.</li>
17 *
18 * <li>p_commit: A one-character flag indicating whether to commit work at the end
19 * of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.</li>
20 *
21 * <li>x_return_status: A one-character code indicating whether any errors occurred
22 * during processing (in which case error messages will be present on the
23 * FND_MSG_PUB package's message stack).Valid values are FND_API.G_RET_STS_SUCCESS,
24 * FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.</li>
25 *
26 * <li>x_msg_count: An integer indicating the number of messages on the FND_MSG_PUB
27 * package's message stack at the end of API processing. For information about
28 * how to retrieve messages from the message stack, refer to FND_MSG_PUB
29 * documentation.</li>
30 *
31 * <li>x_msg_list: List of error messages for the run. User can iterate this
32 * messsage list to get the exact error messages occured. </li>
33 * </ul>
34 *
35 * <B>G_MISS_* values:</B> In addition, three standard default values
36 * (FND_API.G_MISS_NUM, FND_API.G_MISS_CHAR,
37 * FND_API.G_MISS_DATE)
38 * are used throughout the APIs below. These default values are used
39 * to differentiate between a value not passed at all (represented
40 * by the G_MISS_* default value) and a value explicitly passed
41 * as NULL. This convention avoids unintentional nullification
42 * of values during update processing (because G_MISS_* values
43 * are never applied to the database; only explicit NULL values are).
44 * @rep:product EGO
45 * @rep:scope public
46 * @rep:lifecycle active
47 * @rep:displayname Import Related Items
48 * @rep:category BUSINESS_ENTITY EGO_ITEM
49 */
50 G_BO_Identifier CONSTANT VARCHAR2(30) := 'RelItem';
51
52 TYPE Rel_Item_Rec_Type IS RECORD
53 (
54 Transaction_Type VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
55 ,X_Return_Status VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR
56 -- Primary Key Columns
57 ,Inventory_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
58 ,Organization_Id NUMBER DEFAULT FND_API.G_MISS_NUM
59 ,Related_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
60 ,Relationship_Type_Id NUMBER DEFAULT FND_API.G_MISS_NUM
61 -- to take new values of rel item and type in update mode.
62 ,Related_Item_Id_Upd_Val NUMBER DEFAULT FND_API.G_MISS_NUM
63 ,Relationship_Type_Id_Upd_Val NUMBER DEFAULT FND_API.G_MISS_NUM
64
65 ,Reciprocal_Flag VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR
66 ,Start_Date DATE DEFAULT FND_API.G_MISS_DATE
67 ,End_Date DATE DEFAULT FND_API.G_MISS_DATE
68 -- DFF
69 ,ATTR_CONTEXT VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
70 ,ATTR_CHAR1 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
71 ,ATTR_CHAR2 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
72 ,ATTR_CHAR3 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
73 ,ATTR_CHAR4 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
74 ,ATTR_CHAR5 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
75 ,ATTR_CHAR6 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
76 ,ATTR_CHAR7 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
77 ,ATTR_CHAR8 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
78 ,ATTR_CHAR9 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
79 ,ATTR_CHAR10 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
80 ,ATTR_NUM1 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
81 ,ATTR_NUM2 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
82 ,ATTR_NUM3 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
83 ,ATTR_NUM4 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
84 ,ATTR_NUM5 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
85 ,ATTR_NUM6 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
86 ,ATTR_NUM7 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
87 ,ATTR_NUM8 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
88 ,ATTR_NUM9 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
89 ,ATTR_NUM10 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
90 ,ATTR_DATE1 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
91 ,ATTR_DATE2 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
92 ,ATTR_DATE3 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
93 ,ATTR_DATE4 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
94 ,ATTR_DATE5 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
95 ,ATTR_DATE6 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
96 ,ATTR_DATE7 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
97 ,ATTR_DATE8 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
98 ,ATTR_DATE9 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
99 ,ATTR_DATE10 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
100 ,PLANNING_ENABLED_FLAG VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR);
101 -- ,LAST_UPDATE_DATE DATE DEFAULT FND_API.G_MISS_DATE
102 -- ,LAST_UPDATED_BY NUMBER DEFAULT FND_API.G_MISS_NUM
103 -- ,CREATION_DATE DATE DEFAULT FND_API.G_MISS_DATE
104 -- ,CREATED_BY NUMBER DEFAULT FND_API.G_MISS_NUM
105 -- ,LAST_UPDATE_LOGIN NUMBER DEFAULT FND_API.G_MISS_NUM);
106
107 TYPE Rel_Item_Tbl_Type IS TABLE OF Rel_Item_Rec_Type INDEX BY BINARY_INTEGER;
108
109 TYPE Pln_Info_Rec_Type IS RECORD
110 (
111 Transaction_Type VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
112 ,Inventory_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
113 ,Organization_Id NUMBER DEFAULT FND_API.G_MISS_NUM
114 ,Related_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
115 ,Relationship_Type_Id NUMBER DEFAULT FND_API.G_MISS_NUM
116 ,Pln_Info_Id NUMBER DEFAULT FND_API.G_MISS_NUM
117 ,Substitution_Set VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
118 ,Partial_Fulfillment_Flag VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR
119 ,Start_Date DATE DEFAULT FND_API.G_MISS_DATE
120 ,End_Date DATE DEFAULT FND_API.G_MISS_DATE
121 ,All_Customers_Flag VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR);
122
123 TYPE Pln_Info_Tbl_Type IS TABLE OF Pln_Info_Rec_Type INDEX BY BINARY_INTEGER;
124
125 TYPE Cust_Ref_Rec_Type IS RECORD
126 (
127 Transaction_Type VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
128 ,Inventory_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
129 ,Organization_Id NUMBER DEFAULT FND_API.G_MISS_NUM
130 ,Related_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
131 ,Relationship_Type_Id NUMBER DEFAULT FND_API.G_MISS_NUM
132 ,Pln_Info_Id NUMBER DEFAULT FND_API.G_MISS_NUM
133 ,Customer_Id NUMBER DEFAULT FND_API.G_MISS_NUM
134 ,Site_Use_Id NUMBER DEFAULT FND_API.G_MISS_NUM
135 ,Start_Date DATE DEFAULT FND_API.G_MISS_DATE
136 ,End_Date DATE DEFAULT FND_API.G_MISS_DATE);
137
138 TYPE Cust_Ref_Tbl_Type IS TABLE OF Cust_Ref_Rec_Type INDEX BY BINARY_INTEGER;
139 /*#
140 * Use this API to insert,update and delete realated items associated with
141 * particular Items. The table type passed in x_msg_list is as follows:
142 *<code><pre>
143 TYPE Error_Tbl_Type IS TABLE OF Error_Rec_Type
144 INDEX BY BINARY_INTEGER;
145
146 TYPE Error_Rec_Type IS RECORD
147 ( organization_id NUMBER
148 , entity_id VARCHAR2(30)
149 , table_name VARCHAR2(30)
150 , message_name VARCHAR2(30)
151 , message_text VARCHAR2(2000)
152 , entity_index NUMBER
153 , message_type VARCHAR2(1)
154 , row_identifier NUMBER
155 , bo_identifier VARCHAR2(30) := 'ECO'
156 );
157
158 *</pre></code>
159 * @param p_commit A flag indicating whether to commit work at the
160 * end of API processing. Refer to the package description above
161 * for more information about this parameter and a list of valid
162 * values.
163 *
164 * @param P_Init_Msg_List A flag indicating whether to initialize
165 * the FND_MSG_PUB package's message stack. Refer to the package
166 * description above for more information about this parameter and
167 * a list of valid values.
168 *
169 * @param p_Rel_Item_Rec Each record in this PL/SQL table contain Related
170 * item information which Oracle system needs for processing.
171 * Transaction_Type(CREATE/UPDATE/DELETE),Inventory_Item_Id,Organization_Id,
172 * Related_Item_Id, Relationship_Type_Id,Reciprocal_Flag,Start_Date,End_Date,
173 * PLANNING_ENABLED_FLAG
174 * Rest are who colums and DFF attributes.
175 *
176 * @param p_Pln_Info_Tbl This is a pl/sql table to gather planning information.
177 * This is of type Pln_Info_Tbl_Type declared in the same package.
178 *
179 * @param p_Cust_Ref_Tbl This is a pl/sql table to gather customer ref
180 * information. This is of type Cust_Ref_Rec_Type declared in the same package.
181 *
182 * @param x_return_status A code indicating whether any errors
183 * occurred during processing. Refer to the package description
184 * above for more information about this parameter and a list of
185 * valid values.
186 *
187 * @param x_msg_count An integer indicating the number of messages
188 * on the FND_MSG_PUB package's message stack at the end of API
189 * processing. Refer to the package description above for more
190 * information about this parameter.
191 *
192 * @param x_msg_list List of error messages for the run. User can
193 * iterate this messsage list to get the exact error messages occured.
194 * @rep:scope public
195 * @rep:lifecycle active
196 * @rep:displayname Process Related Items
197 */
198 PROCEDURE Process_Rel_Item(
199 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
200 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
201 ,p_Rel_Item_Rec IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Rel_Item_Rec_Type
202 ,p_Pln_Info_Tbl IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Pln_Info_Tbl_Type
203 ,p_Cust_Ref_Tbl IN OUT NOCOPY MTL_RELATED_ITEMS_PUB.Cust_Ref_Tbl_Type
204 ,x_return_status OUT NOCOPY VARCHAR2
205 ,x_msg_count OUT NOCOPY NUMBER
206 ,x_msg_list OUT NOCOPY Error_Handler.Error_Tbl_Type);
207
208 END MTL_RELATED_ITEMS_PUB;