1 package CTO_MSUTIL_PUB as
2 /* $Header: CTOMSUTS.pls 120.4 2006/04/28 14:13:14 rekannan noship $*/
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 -- rkaza. 11/03/2005. bug 4524248.
46 bom_batch_id number := 0;
47
48
49
50 /**************************************************************************
51 Procedure: get_other_orgs
52 Parameters: pModelLineId IN Model Line id
53 xOrgLst OUT NOCOPY CTO_MSUTIL_PUB.Org_list,
54 x_return_status OUT NOCOPY Return Status
55 x_msg_count OUT NOCOPY Msg Count
56 x_msg_data OUT NOCOPY Msg data
57
58 Description:
59 *****************************************************************************/
60
61
62 Procedure get_other_orgs (
63 pModelLineId IN NUMBER,
64 p_mode IN varchar2 default 'ACC',
65 xOrgLst OUT NOCOPY CTO_MSUTIL_PUB.Org_list,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_msg_count OUT NOCOPY NUMBER,
68 x_msg_data OUT NOCOPY VARCHAR2
69 ) ;
70
71
72
73
74
75 /*--------------------------------------------------------------------------+
76 This function identifies the model items for which configuration items need
77 to be created and populates the temporary table bom_cto_src_orgs with all the
78 organizations that each configuration item needs to be created in.
79 +-------------------------------------------------------------------------*/
80 FUNCTION Populate_Src_Orgs( pTopAtoLineId in number,
81 x_return_status OUT NOCOPY varchar2,
82 x_msg_count OUT NOCOPY number,
83 x_msg_data OUT NOCOPY varchar2)
84 RETURN integer;
85
86
87 /*--------------------------------------------------------------------------+
88 This function identifies the model items for which configuration items need
89 to be created and populates the temporary table bom_cto_src_orgs with all the
90 organizations that each configuration item needs to be created in.
91 It is called by the upgrade program.
92 +-------------------------------------------------------------------------*/
93 FUNCTION Populate_Src_Orgs_Upg(pTopAtoLineId in number,
94 x_return_status OUT NOCOPY varchar2,
95 x_msg_count OUT NOCOPY number,
96 x_msg_data OUT NOCOPY varchar2)
97 RETURN integer;
98
99
100
101 /*--------------------------------------------------------------------------+
102 This function populates the table bom_cto_src_orgs with all the organizations
103 in which a configuration item needs to be created.
104 The organizations include all potential sourcing orgs, receiving orgs,
105 OE validation org and PO validation org.
106 The line_id, rcv_org_id, organization_id combination is unique.
107 It is called by Populate_Src_Orgs.
108 +-------------------------------------------------------------------------*/
109 FUNCTION Get_All_Item_Orgs( pLineId in number,
110 pModelItemId in number,
111 pRcvOrgId in number,
112 x_return_status OUT NOCOPY varchar2,
113 x_msg_count OUT NOCOPY number,
114 x_msg_data OUT NOCOPY varchar2,
115 p_mode in varchar2 default 'AUTOCONFIG',
116 p_config_item_id in number default null )
117
118 RETURN integer;
119
120
121 PROCEDURE insert_val_into_bcso( p_top_ato_line_id in NUMBER
122 , p_model_line_id in NUMBER
123 , p_model_item_id in NUMBER
124 , p_t_org_list in CTO_MSUTIL_PUB.org_list
125 , p_config_item_id in number default null );
126
127
128 procedure insert_all_into_bcso( p_top_ato_line_id in NUMBER
129 , p_model_line_id in NUMBER
130 , p_model_item_id in NUMBER
131 , p_config_item_id in number default null) ;
132
133 PROCEDURE query_sourcing_org_ms(
134 p_inventory_item_id NUMBER
135 , p_organization_id NUMBER
136 , p_sourcing_rule_exists OUT NOCOPY varchar2
137 , p_source_type OUT NOCOPY NUMBER -- Added by Renga Kannan on 08/21/01
138 , p_t_sourcing_info OUT NOCOPY SOURCING_INFO
139 , x_exp_error_code OUT NOCOPY NUMBER
140 , x_return_status OUT NOCOPY varchar2
141 ) ;
142
143 PROCEDURE initialize_assignment_set ( x_return_status OUT NOCOPY varchar2 );
144
145
146 PROCEDURE Create_Sourcing_Rules(pModelItemId in number,
147 pConfigId in number,
148 pRcvOrgId in number,
149 x_return_status OUT NOCOPY varchar2,
150 x_msg_count OUT NOCOPY number,
151 x_msg_data OUT NOCOPY varchar2,
152 p_mode in varchar2 default 'AUTOCONFIG' );
153
154
155 PROCEDURE Create_TYPE3_Sourcing_Rules(pModelItemId in number,
156 pConfigId in number,
157 pRcvOrgId in number,
158 x_return_status OUT NOCOPY varchar2,
159 x_msg_count OUT NOCOPY number,
160 x_msg_data OUT NOCOPY varchar2,
161 p_mode in varchar2 default 'AUTOCONFIG' );
162
163
164
165 --- Added by Renga Kannan on 15-Sep-2005
166 --- Added for R12 ATG Performance Project
167
168
169 /*--------------------------------------------------------------------------+
170 This procedure will get the model line id as input to give the list of
171 master orgs where the item needs to be enabled.
172 This will look the bcso tables to identify the list of orgs where the config
173 item needs to be enabled due to sourcing and derive the master orgs for these organization
174 and return them in pl/sql record struct.
175 +-------------------------------------------------------------------------*/
176
177 PROCEDURE Get_Master_Orgs(
178 p_model_line_id IN Number,
179 x_orgs_list OUT NOCOPY CTO_MSUTIL_PUB.org_list,
180 x_msg_count OUT NOCOPY Number,
181 x_msg_data OUT NOCOPY varchar2,
182 x_return_status OUT NOCOPY varchar2);
183
184
185
186
187
188 -- rkaza. 11/03/2005. 11/03/2005. bug 4524248
189 -- bom structure import enhancements
190 -- Start of comments
191 -- API name : get_bom_batch_id
192 -- Type : Public
193 -- Pre-reqs : wrapper around bom_import_pub.get_batchid
194 -- Function : Returns a new batch id from its sequence.
195 -- Parameters:
196 -- IN : None
197 -- Version :
198 -- Initial version 115.1
199 -- End of comment
200
201 Procedure set_bom_batch_id(x_return_status OUT NOCOPY varchar2);
202
203 -- Added by Renga Kannan 03/30/06
204 -- This is a wrapper API to call PLM team's to sync up item media index
205 -- With out this sync up the item cannot be searched in Simple item search page
206 -- This is fixed for bug 4656048
207 Procedure syncup_item_media_index;
208
209
210 -- Added by Renga Kannan on 04/28/06
211 -- Utility API to Switch CONTEXT TO ORDER LINE CONTEXT
212 -- For Bug Fix 5122923
213
214 Procedure Switch_to_oe_Context(
215 p_oe_org_id IN Number,
216 x_current_mode OUT NOCOPY Varchar2,
217 x_current_org OUT NOCOPY Number,
218 x_context_switch_flag OUT NOCOPY Varchar2);
219
220 -- Added by Renga Kannan on 04/28/06
221 -- For bug fix 5122923
222
223 Procedure Switch_context_back(
224 p_old_mode IN Varchar2,
225 p_old_org IN varchar2);
226
227 END CTO_MSUTIL_PUB;