1 PACKAGE MTL_CROSS_REFERENCES_PUB AUTHID CURRENT_USER AS
2 /* $Header: INVPXRFS.pls 120.7 2011/06/22 23:45:28 akbharga noship $ */
3 /*#
4 * This package provides functionality to import item cross references
5 * in to Oracle System.
6 *
7 * <B>Constants: </B> All constants that are unqualified belong to
8 * the package MTL_CROSS_REFERENCE_PUB.
9 *
10 * <B>Standard parameters:</B> Several standard parameters are
11 * used throughout the APIs below:
12 * <ul>
13 * <li>p_api_version: A decimal number indicating major and minor revisions to
14 * the API (where major revisions change the portion of the number before the
15 * decimal and minor revisions change the portion of the number after the decimal).
16 * Pass 1.0 unless otherwise indicated in the API parameter list.</li>
17 *
18 * <li>p_init_msg_list: A one-character flag indicating whether to initialize the
19 * FND_MSG_PUB package's message stack at the beginning of API processing(which
20 * removes any messages that may exist on the stack from prior processing in the
21 * same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.</li>
22 *
23 * <li>p_commit: A one-character flag indicating whether to commit work at the end
24 * of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.</li>
25 *
26 * <li>x_return_status: A one-character code indicating whether any errors occurred
27 * during processing (in which case error messages will be present on the
28 * FND_MSG_PUB package's message stack).Valid values are FND_API.G_RET_STS_SUCCESS,
29 * FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.</li>
30 *
31 * <li>x_msg_count: An integer indicating the number of messages on the FND_MSG_PUB
32 * package's message stack at the end of API processing. For information about
33 * how to retrieve messages from the message stack, refer to FND_MSG_PUB
34 * documentation.</li>
35 *
36 * <li>x_message_list: List of error messages for the run. User can iterate this
37 * messsage list to get the exact error messages occured. </li>
38 * </ul>
39 *
40 * <B>G_MISS_* values:</B> In addition, three standard default values
41 * (FND_API.G_MISS_NUM, FND_API.G_MISS_CHAR,
42 * FND_API.G_MISS_DATE)
43 * are used throughout the APIs below. These default values are used
44 * to differentiate between a value not passed at all (represented
45 * by the G_MISS_* default value) and a value explicitly passed
46 * as NULL. This convention avoids unintentional nullification
47 * of values during update processing (because G_MISS_* values
48 * are never applied to the database; only explicit NULL values are).
49 * @rep:product EGO
50 * @rep:scope public
51 * @rep:lifecycle active
52 * @rep:displayname Import Item Cross References
53 * @rep:category BUSINESS_ENTITY EGO_ITEM
54 */
55 G_BO_Identifier CONSTANT VARCHAR2(30) := 'XRef';
56
57 TYPE XRef_Rec_Type IS RECORD
58 (
59 Transaction_Type VARCHAR2(30) DEFAULT FND_API.G_MISS_CHAR
60 ,X_Return_Status VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR
61 -- Primary Key Columns
62 ,Inventory_Item_Id NUMBER DEFAULT FND_API.G_MISS_NUM
63 ,Organization_Id NUMBER DEFAULT FND_API.G_MISS_NUM
64 ,Cross_Reference_Type VARCHAR2(25) DEFAULT FND_API.G_MISS_CHAR
65 ,Cross_Reference VARCHAR2(255) DEFAULT FND_API.G_MISS_CHAR
66 -- As cross_reference_id is present,for uniquely identifying
67 -- the row we have to use this.(Must be populated for Update and Delete)
68 ,Cross_Reference_Id NUMBER DEFAULT FND_API.G_MISS_NUM
69 ,Description VARCHAR2(240) DEFAULT FND_API.G_MISS_CHAR
70 ,Org_Independent_Flag VARCHAR2(1) DEFAULT FND_API.G_MISS_CHAR
71 --Who Columns
72 ,Last_Update_Date DATE DEFAULT FND_API.G_MISS_DATE
73 ,Last_Updated_By NUMBER DEFAULT FND_API.G_MISS_NUM
74 ,Creation_Date DATE DEFAULT FND_API.G_MISS_DATE
75 ,Created_By NUMBER DEFAULT FND_API.G_MISS_NUM
76 ,Last_Update_Login NUMBER DEFAULT FND_API.G_MISS_NUM
77 ,Request_id NUMBER DEFAULT FND_API.G_MISS_NUM
78 ,Program_Application_Id NUMBER DEFAULT FND_API.G_MISS_NUM
79 ,Program_Id NUMBER DEFAULT FND_API.G_MISS_NUM
80 ,Program_Update_Date DATE DEFAULT FND_API.G_MISS_DATE
81 -- DFF
82 ,Attribute1 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
83 ,Attribute2 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
84 ,Attribute3 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
85 ,Attribute4 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
86 ,Attribute5 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
87 ,Attribute6 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
88 ,Attribute7 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
89 ,Attribute8 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
90 ,Attribute9 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
91 ,Attribute10 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
92 ,Attribute11 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
93 ,Attribute12 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
94 ,Attribute13 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
95 ,Attribute14 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
96 ,Attribute15 VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
97 ,Attribute_category VARCHAR2(150) DEFAULT FND_API.G_MISS_CHAR
98 ,Uom_Code VARCHAR2(3) DEFAULT FND_API.G_MISS_CHAR
99 ,Revision_Id NUMBER DEFAULT FND_API.G_MISS_NUM
100 ,EPC_Gtin_Serial NUMBER DEFAULT FND_API.G_MISS_NUM);
101
102 TYPE XRef_Tbl_Type IS TABLE OF XRef_Rec_Type INDEX BY BINARY_INTEGER;
103 /*#
104 * Use this API to create,update and delete cross references associated with
105 * particular Item. The table type passed in x_message_list is as follows:
106 *<code><pre>
107 TYPE Error_Tbl_Type IS TABLE OF Error_Rec_Type
108 INDEX BY BINARY_INTEGER;
109
110 TYPE Error_Rec_Type IS RECORD
111 ( organization_id NUMBER
112 , entity_id VARCHAR2(30)
113 , table_name VARCHAR2(30)
114 , message_name VARCHAR2(30)
115 , message_text VARCHAR2(2000)
116 , entity_index NUMBER
117 , message_type VARCHAR2(1)
118 , row_identifier NUMBER
119 , bo_identifier VARCHAR2(30) := 'ECO'
120 );
121
122 *</pre></code>
123 * @param p_api_version A decimal number indicating revisions to
124 * the API. Pass the number indicated in the package description
125 * above.
126 * @param p_init_msg_list A flag indicating whether to initialize
127 * the FND_MSG_PUB package's message stack. Refer to the package
128 * description above for more information about this parameter and
129 * a list of valid values.
130 * @param p_commit A flag indicating whether to commit work at the
131 * end of API processing. Refer to the package description above
132 * for more information about this parameter and a list of valid
133 * values.
134 * @param p_XRef_Tbl Each record in this PL/SQL table contain item cross
135 * ref information which Oracle system needs for processing.
136 * Transaction_Type(CREATE/UPDATE/DELETE),Inventory_Item_Id,Organization_Id,
137 * Cross_Reference_Type(Customer/vendor.. ),Cross_Reference(actual value of Xref),
138 * Cross_Reference_Id( need to be populated in case of update/delete),
139 * Description,Org_Independent_Flag(Y/N to say if this is org specific XREF or not).
140 * Rest are who colums and DFF attributes.
141 * epc_gtin_serial,start_date_active,end_date_active are GTIN cross ref specific.
142 * @param x_return_status A code indicating whether any errors
143 * occurred during processing. Refer to the package description
144 * above for more information about this parameter and a list of
145 * valid values.
146 * @param x_msg_count An integer indicating the number of messages
147 * on the FND_MSG_PUB package's message stack at the end of API
148 * processing. Refer to the package description above for more
149 * information about this parameter.
150 * @param x_message_list List of error messages for the run. User can
151 * iterate this messsage list to get the exact error messages occured.
152 * @rep:scope public
153 * @rep:lifecycle active
154 * @rep:displayname Process XRef
155 */
156 PROCEDURE Process_XRef(
157 p_api_version IN NUMBER
158 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
159 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
160 ,p_XRef_Tbl IN OUT NOCOPY MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type
161 ,x_return_status OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER
163 ,x_message_list OUT NOCOPY Error_Handler.Error_Tbl_Type);
164
165 END MTL_CROSS_REFERENCES_PUB;
166