1 package CTO_MSUTIL_PUB AUTHID CURRENT_USER as
2 /* $Header: CTOMSUTS.pls 120.4.12010000.2 2009/03/11 12:46:56 rvalsan ship $*/
3 /*----------------------------------------------------------------------------+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
5 | All rights reserved.
6 | Oracle Manufacturing
7 |
8 |FILE NAME : CTOMSUTS.pls
9 |
10 |DESCRIPTION : Contains modules to :
11 | 1. Populate temporary tables bom_cto_order_lines and
12 | bom_cto_src_orgs, used for intermediate CTO processing
13 | 2. Update these tables with the config_item_id
14 | 3. Copy sourcing rule assignments from model to config item
15 |
16 |HISTORY : Created on 04-OCT-2003 by Sushant Sawant
17 |
18 | Modified on 15-Sep-2005 by Renga Kannan
19 | Added a new procedure get_master_orgs to get the
20 | msater organizations where the config items will be enabled
21 | This is done as part of ATG performance fix project for R12
22 +-----------------------------------------------------------------------------*/
23
24
25
26
27
28
29
30 TYPE NUMTAB is TABLE of number(10) index by binary_integer;
31
32 TYPE SOURCING_INFO is RECORD
33 (
34 source_organization_id NUMTAB ,
35 sourcing_rule_id NUMTAB ,
36 source_type NUMTAB ,
37 rank NUMTAB ,
38 assignment_id NUMTAB ,
39 assignment_type NUMTAB
40 );
41
42
43 TYPE Org_list IS TABLE OF NUMBER index by BINARY_INTEGER;
44
45 --Bugfix 8305535
46 type cfg_tbl is table of number index by binary_integer;
47 cfg_tbl_var cfg_tbl;
48 --Bugfix 8305535
49
50 -- rkaza. 11/03/2005. bug 4524248.
51 bom_batch_id number := 0;
52
53
54
55 /**************************************************************************
56 Procedure: get_other_orgs
57 Parameters: pModelLineId IN Model Line id
58 xOrgLst OUT NOCOPY CTO_MSUTIL_PUB.Org_list,
59 x_return_status OUT NOCOPY Return Status
60 x_msg_count OUT NOCOPY Msg Count
61 x_msg_data OUT NOCOPY Msg data
62
63 Description:
64 *****************************************************************************/
65
66
67 Procedure get_other_orgs (
68 pModelLineId IN NUMBER,
69 p_mode IN varchar2 default 'ACC',
70 xOrgLst OUT NOCOPY CTO_MSUTIL_PUB.Org_list,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2
74 ) ;
75
76
77
78
79
80 /*--------------------------------------------------------------------------+
81 This function identifies the model items for which configuration items need
82 to be created and populates the temporary table bom_cto_src_orgs with all the
83 organizations that each configuration item needs to be created in.
84 +-------------------------------------------------------------------------*/
85 FUNCTION Populate_Src_Orgs( pTopAtoLineId in number,
86 x_return_status OUT NOCOPY varchar2,
87 x_msg_count OUT NOCOPY number,
88 x_msg_data OUT NOCOPY varchar2)
89 RETURN integer;
90
91
92 /*--------------------------------------------------------------------------+
93 This function identifies the model items for which configuration items need
94 to be created and populates the temporary table bom_cto_src_orgs with all the
95 organizations that each configuration item needs to be created in.
96 It is called by the upgrade program.
97 +-------------------------------------------------------------------------*/
98 FUNCTION Populate_Src_Orgs_Upg(pTopAtoLineId in number,
99 x_return_status OUT NOCOPY varchar2,
100 x_msg_count OUT NOCOPY number,
101 x_msg_data OUT NOCOPY varchar2)
102 RETURN integer;
103
104
105
106 /*--------------------------------------------------------------------------+
107 This function populates the table bom_cto_src_orgs with all the organizations
108 in which a configuration item needs to be created.
109 The organizations include all potential sourcing orgs, receiving orgs,
110 OE validation org and PO validation org.
111 The line_id, rcv_org_id, organization_id combination is unique.
112 It is called by Populate_Src_Orgs.
113 +-------------------------------------------------------------------------*/
114 FUNCTION Get_All_Item_Orgs( pLineId in number,
115 pModelItemId in number,
116 pRcvOrgId in number,
117 x_return_status OUT NOCOPY varchar2,
118 x_msg_count OUT NOCOPY number,
119 x_msg_data OUT NOCOPY varchar2,
120 p_mode in varchar2 default 'AUTOCONFIG',
121 p_config_item_id in number default null )
122
123 RETURN integer;
124
125
126 PROCEDURE insert_val_into_bcso( p_top_ato_line_id in NUMBER
127 , p_model_line_id in NUMBER
128 , p_model_item_id in NUMBER
129 , p_t_org_list in CTO_MSUTIL_PUB.org_list
130 , p_config_item_id in number default null );
131
132
133 procedure insert_all_into_bcso( p_top_ato_line_id in NUMBER
134 , p_model_line_id in NUMBER
135 , p_model_item_id in NUMBER
136 , p_config_item_id in number default null) ;
137
138 PROCEDURE query_sourcing_org_ms(
139 p_inventory_item_id NUMBER
140 , p_organization_id NUMBER
141 , p_sourcing_rule_exists OUT NOCOPY varchar2
142 , p_source_type OUT NOCOPY NUMBER -- Added by Renga Kannan on 08/21/01
143 , p_t_sourcing_info OUT NOCOPY SOURCING_INFO
144 , x_exp_error_code OUT NOCOPY NUMBER
145 , x_return_status OUT NOCOPY varchar2
146 ) ;
147
148 PROCEDURE initialize_assignment_set ( x_return_status OUT NOCOPY varchar2 );
149
150
151 PROCEDURE Create_Sourcing_Rules(pModelItemId in number,
152 pConfigId in number,
153 pRcvOrgId in number,
154 x_return_status OUT NOCOPY varchar2,
155 x_msg_count OUT NOCOPY number,
156 x_msg_data OUT NOCOPY varchar2,
157 p_mode in varchar2 default 'AUTOCONFIG' );
158
159
160 PROCEDURE Create_TYPE3_Sourcing_Rules(pModelItemId in number,
161 pConfigId in number,
162 pRcvOrgId in number,
163 x_return_status OUT NOCOPY varchar2,
164 x_msg_count OUT NOCOPY number,
165 x_msg_data OUT NOCOPY varchar2,
166 p_mode in varchar2 default 'AUTOCONFIG' );
167
168
169
170 --- Added by Renga Kannan on 15-Sep-2005
171 --- Added for R12 ATG Performance Project
172
173
174 /*--------------------------------------------------------------------------+
175 This procedure will get the model line id as input to give the list of
176 master orgs where the item needs to be enabled.
177 This will look the bcso tables to identify the list of orgs where the config
178 item needs to be enabled due to sourcing and derive the master orgs for these organization
179 and return them in pl/sql record struct.
180 +-------------------------------------------------------------------------*/
181
182 PROCEDURE Get_Master_Orgs(
183 p_model_line_id IN Number,
184 x_orgs_list OUT NOCOPY CTO_MSUTIL_PUB.org_list,
185 x_msg_count OUT NOCOPY Number,
186 x_msg_data OUT NOCOPY varchar2,
187 x_return_status OUT NOCOPY varchar2);
188
189
190
191
192
193 -- rkaza. 11/03/2005. 11/03/2005. bug 4524248
194 -- bom structure import enhancements
195 -- Start of comments
196 -- API name : get_bom_batch_id
197 -- Type : Public
198 -- Pre-reqs : wrapper around bom_import_pub.get_batchid
199 -- Function : Returns a new batch id from its sequence.
200 -- Parameters:
201 -- IN : None
202 -- Version :
203 -- Initial version 115.1
204 -- End of comment
205
206 Procedure set_bom_batch_id(x_return_status OUT NOCOPY varchar2);
207
208 -- Added by Renga Kannan 03/30/06
209 -- This is a wrapper API to call PLM team's to sync up item media index
210 -- With out this sync up the item cannot be searched in Simple item search page
211 -- This is fixed for bug 4656048
212 Procedure syncup_item_media_index;
213
214
215 -- Added by Renga Kannan on 04/28/06
216 -- Utility API to Switch CONTEXT TO ORDER LINE CONTEXT
217 -- For Bug Fix 5122923
218
219 Procedure Switch_to_oe_Context(
220 p_oe_org_id IN Number,
221 x_current_mode OUT NOCOPY Varchar2,
222 x_current_org OUT NOCOPY Number,
223 x_context_switch_flag OUT NOCOPY Varchar2);
224
225 -- Added by Renga Kannan on 04/28/06
226 -- For bug fix 5122923
227
228 Procedure Switch_context_back(
229 p_old_mode IN Varchar2,
230 p_old_org IN varchar2);
231
232 --Bug 8305535
233 Procedure Raise_event_for_seibel;
234
235 END CTO_MSUTIL_PUB;