1 PACKAGE BODY OE_SO_ATO AS
2 /* $Header: oesoatob.pls 115.5 99/07/16 08:28:09 porting ship $ */
3
4
5 PROCEDURE QUERY_ATTRIBUTES(
6 x_header_id IN NUMBER,
7 x_line_id IN NUMBER,
8 x_warehouse_id IN NUMBER,
9 x_inventory_item_id IN NUMBER,
10 x_organization_id IN NUMBER,
11 x_order_number IN NUMBER,
12 x_order_type IN VARCHAR2,
13 x_s27 IN OUT NUMBER,
14 x_schato_allowed OUT VARCHAR2,
15 x_configuration_item_id IN OUT NUMBER,
16 x_config_item_description OUT VARCHAR2,
17 x_config_line_detail_id OUT NUMBER,
18 x_configured_quantity OUT NUMBER,
19 x_demand_source_header_id OUT NUMBER,
20 x_demand_source_delivery OUT NUMBER,
21 x_user_delivery OUT VARCHAR2,
22 x_reserved_quantity OUT NUMBER,
23 x_mfg_action OUT VARCHAR2,
24 x_mfg_result OUT VARCHAR2,
25 x_action_date OUT VARCHAR2,
26 x_creation_date_time OUT VARCHAR2) IS
27 l_number_of_config_details NUMBER := 0;
28 l_reserved_quantity NUMBER := 0;
29 l_supply_reserved_quantity NUMBER := 0;
30 l_schato_eligible NUMBER := 0;
31 l_configured_quantity NUMBER := 0;
32 l_demand_source_header_id NUMBER := 0;
33 LINE_UNDEMANDED EXCEPTION;
34 SCHATO_NOT_ALLOWED EXCEPTION;
35
36 BEGIN
37
38
39 SELECT COUNT(1),
40 SUM(DECODE(SCHEDULE_STATUS_CODE, 'RESERVED', QUANTITY, 0)),
41 SUM(DECODE(SCHEDULE_STATUS_CODE, 'SUPPLY RESERVED', QUANTITY, 0)),
42 SUM(Quantity)
43 INTO l_number_of_config_details,
44 l_reserved_quantity,
45 l_supply_reserved_quantity,
46 l_configured_quantity
47 FROM SO_LINE_DETAILS
48 WHERE LINE_ID = x_line_id
49 AND CONFIGURATION_ITEM_FLAG = 'Y';
50
51
52
53
54 IF l_number_of_config_details = 0 THEN
55
56 x_s27 := 4;
57
58 SELECT DECODE(COUNT(1), 0, 0, 4)
59 INTO x_s27
60 FROM SO_LINE_DETAILS
61 WHERE LINE_ID = x_line_id
62 AND SCHEDULE_STATUS_CODE = 'DEMANDED';
63
64 ELSIF l_reserved_quantity = 0 THEN
65
66 IF l_supply_reserved_quantity = 0 THEN
67 x_s27 := 23;
68 ELSE
69 x_s27 := 21;
70 END IF;
71
72 ELSE
73 x_s27 := 20;
74 END IF;
75
76
77 BEGIN
78
79 SELECT sales_order_id
80 INTO l_demand_source_header_id
81 FROM mtl_sales_orders mso
82 WHERE segment1 = To_Char(x_order_number)
83 AND segment2 = x_order_type
84 AND segment3 = FND_PROFILE.Value_Specific('SO_SOURCE_CODE');
85
86 EXCEPTION
87 When NO_DATA_FOUND then NULL;
88 When OTHERS then
89 NULL;
90 END;
91
92
93 x_demand_source_header_id := l_demand_source_header_id;
94
95
96
97 BEGIN
98
99 SELECT INVENTORY_ITEM_ID, MIN(LINE_DETAIL_ID)
100 INTO x_configuration_item_id, x_config_line_detail_id
101 FROM SO_LINE_DETAILS S
102 WHERE S.LINE_ID = x_line_id
103 AND CONFIGURATION_ITEM_FLAG = 'Y'
104 GROUP BY INVENTORY_ITEM_ID;
105
106 SELECT DESCRIPTION
107 INTO x_config_item_description
108 FROM MTL_SYSTEM_ITEMS M
109 WHERE M.INVENTORY_ITEM_ID = x_configuration_item_id
110 AND M.ORGANIZATION_ID = NVL(x_warehouse_id, x_organization_id);
111
112 EXCEPTION
113 When NO_DATA_FOUND then
114 NULL;
115 When OTHERS then
116 NULL;
117 END;
118
119 SELECT DEMAND_SOURCE_DELIVERY, USER_DELIVERY
120 INTO x_demand_source_delivery,
121 x_user_delivery
122 FROM MTL_DEMAND
123 WHERE INVENTORY_ITEM_ID = x_inventory_item_id
124 AND DEMAND_SOURCE_TYPE = 2
125 AND DEMAND_SOURCE_HEADER_ID = l_demand_source_header_id
126 AND DEMAND_SOURCE_LINE = x_line_id
127 AND ORGANIZATION_ID = NVL(x_warehouse_id, x_organization_id)
128 AND ROWNUM = 1;
129 /* Added the above line to fix the bug 891551/700134 */
130
131 BEGIN
132 SELECT sum(wip_completed_quantity)
133 into x_reserved_quantity
134 from so_line_details
135 where line_id = x_line_id
136 and inventory_item_id = x_configuration_item_id;
137 EXCEPTION
138 When NO_DATA_FOUND then
139 NULL;
140 When OTHERS then
141 NULL;
142 END;
143
144 SELECT TO_CHAR(CREATION_DATE,'YYYY/MM/DD HH24:MI')
145 INTO x_creation_date_time
146 FROM SO_LINES
147 WHERE LINE_ID = x_line_id;
148
149
150 SELECT a.name, r.name, to_char(l.s27_date, 'YYYY/MM/DD HH24:MI')
151 INTO x_mfg_action, x_mfg_result, x_action_date
152 FROM SO_ACTIONS A,
153 SO_RESULTS R,
154 SO_LINES L
155 WHERE
156 L.LINE_ID = x_line_id
157 AND A.RESULT_TABLE = 'SO_LINES'
158 AND R.RESULT_ID = L.S27
159 AND A.RESULT_COLUMN = 'S27';
160
161 IF x_s27 = 0 THEN
162 x_schato_allowed := 'NO_DEMAND';
163 Raise LINE_UNDEMANDED;
164 END IF;
165
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 NULL;
169 WHEN LINE_UNDEMANDED THEN
170 FND_MESSAGE.set_name('OE', 'SO_ATO_LINE_UNDEMANDED');
171 APP_EXCEPTION.Raise_Exception;
172 WHEN SCHATO_NOT_ALLOWED THEN
173 FND_MESSAGE.set_name ('OE', 'SO_ATO_LINE_UNDEMANDED');
177 END QUERY_ATTRIBUTES;
174 APP_EXCEPTION.Raise_Exception;
175 WHEN OTHERS THEN raise;
176
178
179 END OE_SO_ATO;
[Home] [Help]