1 package body OE_DEFAULT_SOURCE_TYPE_PVT as
2 /* $Header: OEXVDSTB.pls 115.2 99/07/16 08:16:54 porting shi $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_DEFAULT_SOURCE_TYPE';
5
6 PROCEDURE GET_DEFAULT_SOURCE_TYPE(P_API_Version In Number,
7 P_Return_Status Out Varchar2,
8 P_Msg_Count Out Number,
9 P_MSG_Data Out Varchar2,
10 p_item in number,
11 p_cycle_id in number,
12 p_linetype in varchar2,
13 p_option_flag in varchar2,
14 p_item_type in varchar2,
15 p_source_type_svrid out number,
16 p_source_type out varchar2,
17 p_warehouse in number default null) is
18 purchase_action number;
19 pick_release_action number;
20 purchasable varchar2(1);
21 PURCHASERELEASE number := 17;
22 PICKRELEASE number := 2;
23 org_specified varchar2(1) := 'N';
24 item_in_org varchar2(1) := 'N';
25 c_source_type varchar2(80);
26 c_source_type_svrid number;
27 CP_API_Version Constant number := 1;
28 CP_Return_Status varchar2(30);
29 CP_Msg_Count number;
30 CP_MSG_Data varchar2(80);
31 L_API_Name Constant Varchar2(30) := 'GET_DEFAULT_SOURCE_TYPE';
32 L_API_Version Constant Number := 1.0;
33 L_Warehouse Number;
34 msgcount number := 0;
35
36 begin
37
38 /* notes:
39
40 p_msg_data = 'itemcycle' and source_type is null implies something
41 fishy!!
42 if p_msg_data = 'internal or 'external' , source_type_code_svrid = 1
43 which means source_type has to be INTERNAL or EXTERNAL
44
45 */
46
47 IF Not FND_API.Compatible_API_Call (L_API_Version,
48 P_API_Version,
49 L_API_Name,
50 G_PKG_Name) Then
51 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
52 End If;
53
54
55 if ((p_item is null) or (p_cycle_id is null) or (p_linetype is null) or (p_option_flag is null) or (p_item_type is null))
56
57 then
58 P_Return_Status := FND_API.G_RET_STS_ERROR;
59 Return;
60 end if;
61
62 l_warehouse := p_warehouse;
63
64 if l_warehouse = -99 then
65 l_warehouse := Null;
66 end if;
67
68 if l_warehouse is not null then
69 org_specified := 'Y';
70 end if;
71
72 if org_specified = 'Y' then
73 begin
74 select 'Y' into item_in_org
75 from mtl_system_items
76 where inventory_item_id = p_item
77 and organization_id = l_warehouse;
78 exception
79 when no_data_found then null;
80 end;
81 end if;
82
83 begin
84 select msi.purchasing_enabled_flag
85 into purchasable
86 from mtl_system_items msi
87 where msi.inventory_item_id = p_item
88 and msi.organization_id = FND_PROFILE.VALUE_SPECIFIC('SO_ORGANIZATION_ID');
89 exception
90 when no_data_found then P_Return_Status := FND_API.G_RET_STS_ERROR;
91 Return;
92 end;
93
94
95 /* check whether purchasing release is an action in the given cycle */
96 begin
97 select CA.action_id into
98 purchase_action
99 from
100 so_cycle_actions CA
101 where CA.action_id = PURCHASERELEASE
102 and CA.cycle_id = p_cycle_id
103 and Nvl(CA.CHANGE_CODE,'ADDED') <> 'DELETED';
104 exception
105 when no_data_found then null;
106 end;
107
108 /* check whether pick release is an action in the given cycle */
109 begin
110 select CA.action_id into
111 pick_release_action
112 from
113 so_cycle_actions CA
114 where CA.action_id = PICKRELEASE
115 and CA.cycle_id = p_cycle_id
116 and Nvl(CA.CHANGE_CODE,'ADDED') <> 'DELETED';
117 exception
118 when no_data_found then null;
119 end;
120
121
122 /* if item is standard and line is not an option and line is not a service line
123 then only allow the line to be externally sourced */
124
125 if ( (p_item_type = 'STANDARD')
126 and ( (p_linetype = 'REGULAR') or (p_linetype = 'DETAIL')
127 or (p_linetype = 'PARENT') )
128 and (p_option_flag = 'N')) then
129
130 if (purchase_action is null) then /* no purchase release in cycle */
131
132 p_source_type_svrid := 1;
133 p_source_type := 'INTERNAL';
134 p_msg_data := 'internal';
135 p_msg_count := msgcount + 1;
136
137 elsif (pick_release_action is null) then
138
139 /* purchase action is not null and pick release is null */
140
141 if (purchasable = 'Y') then
142 p_source_type := 'EXTERNAL';
143 p_source_type_svrid := 1;
144 p_msg_data := 'itemcycle';
145 p_msg_count := msgcount + 1;
146 else
147 p_source_type := ''; /* needs discussion */
148 p_msg_data := 'itemcycle';
149 p_msg_count := msgcount + 1;
150 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
151 Return;
152
153 end if;
154
155 else /* purchase action and pick release action both are not null */
156
157 oe_get_default_source_type_pub.get_custom_default_source_type(
158 CP_API_Version,
159 CP_Return_Status,
160 CP_Msg_Count,
161 CP_MSG_Data,
162 p_item,
163 p_cycle_id,
164 p_linetype,
165 p_option_flag,
166 p_item_type,
167 c_source_type_svrid,
168 c_source_type,
169 l_warehouse);
170
171 p_source_type := c_source_type;
172 p_source_type_svrid := c_source_type_svrid;
173
174 end if;
175
176
177 else /* not a standard item or regular line or an option */
178
179 p_source_type_svrid := 1;
180 p_source_type := 'INTERNAL';
181 p_msg_data := 'internal';
182 p_msg_count := msgcount + 1;
183
184 end if; /* standard item ; not an option or service line */
185
186 P_Return_Status := FND_API.G_RET_STS_SUCCESS;
187
188 end GET_DEFAULT_SOURCE_TYPE;
189
190 end OE_DEFAULT_SOURCE_TYPE_PVT;
[Home] [Help]