[Home] [Help]
PACKAGE BODY: APPS.AMS_PHYSICAL_FULFILLMENT_PUB
Source
1 PACKAGE BODY AMS_physical_fulfillment_PUB AS
2 /* $Header: amsvfulb.pls 120.2 2006/05/07 23:02:03 anchaudh ship $ */
3 PROCEDURE fulfill_collateral_request
4 (
5 p_init_msg_list IN VARCHAR2
6 ,p_api_version IN NUMBER
7 ,p_commit IN VARCHAR2
8 ,x_return_status OUT NOCOPY VARCHAR2
9 ,x_msg_count OUT NOCOPY NUMBER
10 ,x_msg_data OUT NOCOPY VARCHAR2
11 ,p_order_header_rec IN ORDER_HEADER_REC_TYPE
12 ,p_order_line_tbl IN ORDER_LINE_tbl_TYPE
13 ,x_order_header_rec OUT NOCOPY ASO_ORDER_INT.order_header_rec_type
14 ) IS
15
16 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC;
17 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_MISS_QTE_LINE_TBL;
18 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
19 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
20
21 l_control_rec ASO_ORDER_INT.control_rec_type;
22
23 x_order_line_tbl ASO_ORDER_INT.Order_Line_tbl_type;
24
25 -- Assuming a valid collateral ID is passed
26 -----Collateral existing in the OM's inventory organization
27 -----Collateral has a inventory item created for it.
28 -----Collateral is available and active
29 -----Is a physical collateral
30
31 -- Kit containing Kits is not supported.
32 -- Only physical collaterals which are not kits with an inventory_item_id are picked for
33 -- the original kit.
34
35 CURSOR cur_get_collateral_items(p_collateral_id NUMBER) IS
36 SELECT delv.INVENTORY_ITEM_ID
37 FROM ams_deliverables_all_b delv
38 WHERE delv.deliverable_id = p_collateral_id
39 UNION
40 SELECT delv.INVENTORY_ITEM_ID
41 FROM ams_deliverables_all_b delv,
42 mtl_system_items_b msi
43 WHERE delv.deliverable_id in (select deliverable_kit_part_id
44 FROM ams_deliv_kit_items
45 WHERE deliverable_kit_id = p_collateral_id)
46 AND delv.inventory_item_id is not null
47 AND nvl(delv.kit_flag,'N')='N'
48 AND delv.status_code='AVAILABLE'
49 AND delv.active_flag = 'Y'
50 AND delv.can_fulfill_physical_flag = 'Y'
51 AND delv.actual_avail_to_date > sysdate
52 AND msi.inventory_item_id = delv.inventory_item_id
53 AND msi.organization_id = p_order_header_rec.inv_organization_id
54 and msi.customer_order_flag = 'Y'
55 and msi.shippable_item_flag = 'Y';
56
57 line_index number := 0;
58 shipment_index number := 0;
59
60 CURSOR cur_get_primary_uom (p_item_id NUMBER) IS
61 SELECT msi.primary_uom_code
62 FROM mtl_system_items_b msi
63 WHERE msi.inventory_item_id = p_item_id
64 AND msi.organization_id = p_order_header_rec.inv_organization_id;
65
66 BEGIN
67
68 SAVEPOINT fulfill_collateral_request;
69
70 l_control_rec.book_flag := FND_API.G_TRUE;
71 l_control_rec.calculate_price := FND_API.G_FALSE;
72
73 FND_MSG_PUB.Initialize;
74
75 -- Process Order Header
76
77 l_qte_header_rec.party_id := p_order_header_rec.cust_party_id;
78 l_qte_header_rec.quote_source_code := p_order_header_rec.quote_source_code;
79
80 IF p_order_header_rec.cust_account_id IS NOT NULL THEN
81 l_qte_header_rec.cust_account_id := p_order_header_rec.cust_account_id;
82 END IF;
83
84 IF p_order_header_rec.sold_to_contact_id IS NOT NULL THEN
85 l_qte_header_rec.org_contact_id := p_order_header_rec.sold_to_contact_id;
86 END IF;
87
88 l_qte_header_rec.invoice_to_party_id := p_order_header_rec.inv_party_id;
89 l_qte_header_rec.invoice_to_party_site_id := p_order_header_rec.inv_party_site_id;
90 --l_qte_header_rec.order_type_id := fnd_profile.value('AMS_ORDER_TYPE');
91 --anchaudh added the following line for bug fix #5137205.
92 l_qte_header_rec.order_type_id := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_ORDER_TYPE,p_order_header_rec.organization_id) ;
93 l_qte_header_rec.marketing_source_code_id := p_order_header_rec.marketing_source_code_id;
94 l_qte_header_rec.employee_person_id := p_order_header_rec.employee_id;
95
96 -- Process Order Header Shipment information
97
98 l_hd_shipment_tbl(1).ship_to_party_site_id := p_order_header_rec.ship_party_site_id;
99
100 --
101 -- SHIP_FROM_ORG_ID added by ABHOLA
102 --
103 l_hd_shipment_tbl(1).SHIP_FROM_ORG_ID := p_order_header_rec.SHIP_FROM_ORG_ID;
104
105
106 -- Process Order Lines
107 FOR coll_rec IN cur_get_collateral_items(p_order_header_rec.collateral_id) LOOP
108 IF coll_rec.inventory_item_id is NOT NULL THEN
109 line_index := line_index + 1;
110 l_qte_line_tbl(line_index).inventory_item_id := coll_rec.inventory_item_id;
111 l_qte_line_tbl(line_index).line_quote_price := 0;
112 l_qte_line_tbl(line_index).line_list_price := 0;
113
114 OPEN cur_get_primary_uom(coll_rec.inventory_item_id);
115 FETCH cur_get_primary_uom INTO l_qte_line_tbl(line_index).UOM_code;
116 CLOSE cur_get_primary_uom;
117
118 l_qte_line_tbl(line_index).line_category_code := p_order_header_rec.line_category_code;
119
120 -- Process Shipment information for each order line
121 FOR i IN P_ORDER_LINE_TBL.first..P_ORDER_LINE_TBL.last LOOP
122
123 IF p_order_line_tbl.exists(i) THEN
124 shipment_index := shipment_index + 1;
125 l_ln_shipment_tbl(shipment_index).qte_line_index := line_index;
126 l_ln_shipment_tbl(shipment_index).quantity := p_order_line_tbl(i).quantity;
127 l_ln_shipment_tbl(shipment_index).ship_to_party_id := p_order_line_tbl(i).ship_party_id;
128 l_ln_shipment_tbl(shipment_index).ship_to_party_site_id := p_order_line_tbl(i).ship_party_site_id;
129 l_ln_shipment_tbl(shipment_index).ship_method_code := p_order_line_tbl(i).ship_method_code;
130 --
131 -- SHIP_FROM_ORG_ID added by ABHOLA
132 --
133 l_ln_shipment_tbl(shipment_index).SHIP_FROM_ORG_ID := p_order_line_tbl(i).SHIP_FROM_ORG_ID;
134
135 END IF;
136
137 END LOOP;
138 END IF;
139 END LOOP;
140 --by: musman: MOAC uptake bug:4421261 Fix
141 MO_GLOBAL.SET_POLICY_CONTEXT ('S',p_order_header_rec.organization_id);
142
143 ASO_ORDER_INT.create_order(
144 p_api_version_number => 1.0
145 ,p_init_msg_list => FND_API.g_false
146 ,p_commit => FND_API.g_false
147 ,p_qte_rec => l_qte_header_rec
148 ,p_qte_line_tbl => l_qte_line_tbl
149 ,p_header_shipment_tbl => l_hd_shipment_tbl
150 ,p_line_shipment_tbl => l_ln_shipment_tbl
151 ,p_control_rec => l_control_rec
152 ,x_order_header_rec => x_order_header_rec
153 ,x_order_line_tbl => x_order_line_tbl
154 ,x_return_status => x_return_status
155 ,x_msg_count => x_msg_count
156 ,x_msg_data => x_msg_data
157 );
158
159 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
160
161 INSERT INTO AMS_REQUEST_HISTORY
162 ( REQUEST_HISTORY_ID
163 ,ORDER_ID
164 ,COLLATERAL_ID
165 ,COVER_LETTER_ID
166 ,CREATED_BY
167 ,CREATION_DATE
168 ,LAST_UPDATED_BY
169 ,LAST_UPDATE_DATE
170 ,LAST_UPDATE_LOGIN
171 )
172 VALUES
173 (
174 ams_request_history_s.nextval
175 ,x_order_header_rec.order_header_id
176 ,p_order_header_rec.collateral_id
177 ,p_order_header_rec.cover_letter_id
178 ,fnd_global.user_id
179 ,sysdate
180 ,fnd_global.user_id
181 ,sysdate
182 ,fnd_global.conc_login_id
183 );
184
185 IF p_commit = Fnd_Api.g_true THEN
186 COMMIT WORK;
187 END IF;
188 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
189 RAISE Fnd_Api.G_EXC_ERROR;
190 ELSE
191 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
192 END IF;
193 EXCEPTION
194 WHEN Fnd_Api.G_EXC_ERROR THEN
195 x_return_status := Fnd_Api.g_ret_sts_error ;
196 ROLLBACK TO fulfill_collateral_request;
197 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
198 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
199 ROLLBACK TO fulfill_collateral_request;
200 WHEN OTHERS THEN
201 x_return_status := Fnd_Api.g_ret_sts_unexp_erroR ;
202 ROLLBACK TO fulfill_collateral_request;
203 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
204 THEN
205 Fnd_Msg_Pub.Add_Exc_Msg( 'AMS_PHYSICAL_FULFILLMENT_PUB','fulfill_collateral_request');
206 END IF;
207 Fnd_Msg_Pub.Count_AND_Get
208 ( p_count => x_msg_count,
209 p_data => x_msg_data,
210 p_encoded => Fnd_Api.G_FALSE
211 );
212 END fulfill_collateral_request;
213
214 END AMS_physical_fulfillment_PUB;