[Home] [Help]
PACKAGE BODY: APPS.JTF_FULFILLMENT_PUB
Source
1 PACKAGE BODY JTF_Fulfillment_PUB AS
2 /* $Header: jtfgfmpb.pls 120.0 2005/05/11 08:14:40 appldev ship $ */
3 ------------------------------------------------------------------
4 -- Copyright (c) 1999 Oracle Corporation --
5 -- Redwood Shores, California, USA --
6 -- All rights reserved. --
7 ------------------------------------------------------------------
8 -- PACKAGE
9 -- JTF_Fulfillment_PUB
10 --
11 -- PURPOSE
12 -- Private API for Physical fulfillment.
13 --
14 -- HISTORY
15
16 -- PROCEDURE
17 -- create_fulfill_physical
18 --
19 -- PURPOSE
20 -- This procedure inserts physical fulfillment.
21 --
22 -- PARAMETERS
23 --
24 -- DESCRIPTION
25 -- This procedure create one order for physical fulfillment
26 -- and insert into JTF_FM_request_history. Then inserts info of each deliverable
27 -- into JTF_FM_request_content. One order can have multiple collaterals.
28
29 ------------------------------------------------------------
30 g_pkg_name CONSTANT VARCHAR2(30):='JTF_Fulfillment_PUB';
31
32
33 PROCEDURE create_fulfill_physical
34 (
35 p_init_msg_list IN VARCHAR2
36 ,p_api_version IN NUMBER
37 ,p_commit IN VARCHAR2
38 ,x_return_status OUT NOCOPY VARCHAR2
39 ,x_msg_count OUT NOCOPY NUMBER
40 ,x_msg_data OUT NOCOPY VARCHAR2
41 ,p_order_header_rec IN ORDER_HEADER_REC_TYPE
42 ,p_order_line_tbl IN ORDER_LINE_TBL_TYPE
43 ,x_order_header_rec OUT NOCOPY ASO_ORDER_INT.order_header_rec_type
44 ,x_request_history_id OUT NOCOPY NUMBER
45 )
46 IS
47
48 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type :=ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC;
49 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL;
50 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
51 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
52
53 l_request_history_rec JTF_Request_History_PVT.request_history_rec_type;
54
55 l_control_rec ASO_ORDER_INT.control_rec_type;
56
57 x_order_line_tbl ASO_ORDER_INT.Order_Line_tbl_type;
58
59 -- Assuming a valid collateral ID is passed
60 -----Collateral existing in the OM's inventory organization
61 -----Collateral has a inventory item created for it.
62 -----Collateral is available and active
63 -----Is a physical collateral
64
65 -- Kit containing Kits is not supported.
66 -- Only physical collaterals which are not kits with an inventory_item_id are picked for
67 -- the original kit.
68
69 CURSOR cur_get_collateral_items(p_collateral_id NUMBER) IS
70 SELECT delv.INVENTORY_ITEM_ID
71 FROM ams_deliverables_all_b delv
72 WHERE delv.deliverable_id = p_collateral_id
73 UNION
74 SELECT delv.INVENTORY_ITEM_ID
75 FROM ams_deliverables_all_b delv,
76 mtl_system_items_b msi
77 WHERE delv.deliverable_id in (select deliverable_kit_part_id
78 FROM ams_deliv_kit_items
79 WHERE deliverable_kit_id = p_collateral_id)
80 AND delv.inventory_item_id is not null
81 AND nvl(delv.kit_flag,'N')='N'
82 AND delv.status_code='AVAILABLE'
83 AND delv.active_flag = 'Y'
84 AND delv.can_fulfill_physical_flag = 'Y'
85 AND delv.actual_avail_to_date > sysdate
86 AND msi.inventory_item_id = delv.inventory_item_id
87 AND msi.organization_id = p_order_header_rec.inv_organization_id
88 and msi.customer_order_flag = 'Y'
89 and msi.shippable_item_flag = 'Y';
90
91 line_index number := 0;
92 shipment_index number := 0;
93
94 CURSOR cur_get_primary_uom (p_item_id NUMBER) IS
95 SELECT msi.primary_uom_code
96 FROM mtl_system_items_b msi
97 WHERE msi.inventory_item_id = p_item_id
98 AND msi.organization_id = p_order_header_rec.inv_organization_id;
99
100 BEGIN
101
102 SAVEPOINT fulfill_collateral_request;
103
104 l_control_rec.book_flag := FND_API.G_TRUE;
105 l_control_rec.calculate_price := FND_API.G_FALSE;
106
107 FND_MSG_PUB.Initialize;
108
109 -- Process Order Header
110
111 l_qte_header_rec.party_id := p_order_header_rec.cust_party_id;
112 l_qte_header_rec.quote_source_code := p_order_header_rec.quote_source_code;
113
114 IF p_order_header_rec.cust_account_id IS NOT NULL THEN
115 l_qte_header_rec.cust_account_id := p_order_header_rec.cust_account_id;
116 END IF;
117
118 IF p_order_header_rec.sold_to_contact_id IS NOT NULL THEN
119 l_qte_header_rec.org_contact_id := p_order_header_rec.sold_to_contact_id;
120 END IF;
121
122 l_qte_header_rec.invoice_to_party_id := p_order_header_rec.inv_party_id;
123 l_qte_header_rec.invoice_to_party_site_id := p_order_header_rec.inv_party_site_id;
124 l_qte_header_rec.order_type_id := fnd_profile.value('AMS_ORDER_TYPE');
125 l_qte_header_rec.marketing_source_code_id := p_order_header_rec.marketing_source_code_id;
126 l_qte_header_rec.employee_person_id := p_order_header_rec.employee_id;
127
128 -- Process Order Header Shipment information
129
130 l_hd_shipment_tbl(1).ship_to_party_site_id := p_order_header_rec.ship_party_site_id;
131
132
133 -- Process Order Lines
134 FOR coll_rec IN cur_get_collateral_items(p_order_header_rec.collateral_id) LOOP
135 IF coll_rec.inventory_item_id is NOT NULL THEN
136 line_index := line_index + 1;
137 l_qte_line_tbl(line_index).inventory_item_id := coll_rec.inventory_item_id;
138 l_qte_line_tbl(line_index).line_quote_price := 0;
139 l_qte_line_tbl(line_index).line_list_price := 0;
140
141 OPEN cur_get_primary_uom(coll_rec.inventory_item_id);
142 FETCH cur_get_primary_uom INTO l_qte_line_tbl(line_index).UOM_code;
143 CLOSE cur_get_primary_uom;
144
145 l_qte_line_tbl(line_index).line_category_code := p_order_header_rec.line_category_code;
146
147 -- Process Shipment information for each order line
148 FOR i IN P_ORDER_LINE_TBL.first..P_ORDER_LINE_TBL.last LOOP
149
150 IF p_order_line_tbl.exists(i) THEN
151 shipment_index := shipment_index + 1;
152 l_ln_shipment_tbl(shipment_index).qte_line_index := line_index;
153 l_ln_shipment_tbl(shipment_index).quantity := p_order_line_tbl(i).quantity;
154 l_ln_shipment_tbl(shipment_index).ship_to_party_id := p_order_line_tbl(i).ship_party_id;
155 l_ln_shipment_tbl(shipment_index).ship_to_party_site_id := p_order_line_tbl(i).ship_party_site_id;
156 l_ln_shipment_tbl(shipment_index).ship_method_code := p_order_line_tbl(i).ship_method_code;
157
158 END IF;
159
160 END LOOP;
161 END IF;
162 END LOOP;
163
164 ASO_ORDER_INT.create_order(
165 p_api_version_number => 1.0
166 ,p_init_msg_list => FND_API.g_false
167 ,p_commit => FND_API.g_false
168 ,p_qte_rec => l_qte_header_rec
169 ,p_qte_line_tbl => l_qte_line_tbl
170 ,p_header_shipment_tbl => l_hd_shipment_tbl
171 ,p_line_shipment_tbl => l_ln_shipment_tbl
172 ,p_control_rec => l_control_rec
173 ,x_order_header_rec => x_order_header_rec
174 ,x_order_line_tbl => x_order_line_tbl
175 ,x_return_status => x_return_status
176 ,x_msg_count => x_msg_count
177 ,x_msg_data => x_msg_data
178 );
179
180 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
181
182
183
184
185
186 l_request_history_rec.app_info := 690;
187 l_request_history_rec.order_id := x_order_header_rec.order_header_id;
188
189 JTF_Request_History_PVT.Create_Request_History(
190 p_api_version_number => p_api_version,
191 p_init_msg_list => p_init_msg_list,
192 p_commit => p_commit,
193 p_validation_level => 1,
194 x_return_status => x_return_status,
195 x_msg_count => x_msg_count,
196 x_msg_data => x_msg_data,
197 p_request_history_rec => l_request_history_rec,
198 x_request_history_id => x_request_history_id
199 );
200
201
202
203
204 IF p_commit = Fnd_Api.g_true THEN
205 COMMIT WORK;
206 END IF;
207 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
208 RAISE Fnd_Api.G_EXC_ERROR;
209 ELSE
210 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212 EXCEPTION
213 WHEN Fnd_Api.G_EXC_ERROR THEN
214 x_return_status := Fnd_Api.g_ret_sts_error ;
215 ROLLBACK TO fulfill_collateral_request;
216 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
217 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
218 ROLLBACK TO fulfill_collateral_request;
219 WHEN OTHERS THEN
220 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
221 ROLLBACK TO fulfill_collateral_request;
222 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
223 THEN
224 Fnd_Msg_Pub.Add_Exc_Msg( 'JTF_PHYSICAL_FULFILLMENT_PUB','fulfill_collateral_request');
225 END IF;
226 Fnd_Msg_Pub.Count_AND_Get
227 ( p_count => x_msg_count,
228 p_data => x_msg_data,
229 p_encoded => Fnd_Api.G_FALSE
230 );
231 END create_fulfill_physical;
232 END JTF_Fulfillment_PUB;