[Home] [Help]
PACKAGE BODY: APPS.CSTPACIR
Source
1 PACKAGE BODY CSTPACIR AS
2 /* $Header: CSTPACIB.pls 120.1 2010/10/30 02:08:44 fayang ship $ */
3
4 PROCEDURE issue (
5 i_trx_id IN NUMBER,
6 i_layer_id IN NUMBER,
7 i_inv_item_id IN NUMBER,
8 i_org_id IN NUMBER,
9 i_wip_entity_id IN NUMBER,
10 i_txn_qty IN NUMBER,
11 i_op_seq_num IN NUMBER,
12 i_user_id IN NUMBER,
13 i_login_id IN NUMBER,
14 i_request_id IN NUMBER,
15 i_prog_id IN NUMBER,
16 i_prog_appl_id IN NUMBER,
17 err_num OUT NOCOPY NUMBER,
18 err_code OUT NOCOPY VARCHAR2,
19 err_msg OUT NOCOPY VARCHAR2)
20 is
21 stmt_num NUMBER;
22
23 /* EAM Acct Enh Project */
24 l_debug VARCHAR2(80);
25 l_zero_cost_flag NUMBER := -1;
26 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
27 l_msg_count NUMBER := 0;
28 l_msg_data VARCHAR2(8000) := '';
29 l_api_message VARCHAR2(8000);
30
31 --bug 10162275
32 l_clcd_count NUMBER := 0;
33 l_wrocd_count NUMBER := 0;
34
35 BEGIN
36
37 err_num := 0;
38 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
39 stmt_num := 10;
40
41
42 /***********************************************************
43 * 1. If a new cost element exists in cst_layer_cost_details*
44 * which did not exist during the last update, or *
45 * 2. This is the first issue being costed for the job, then*
46 * INSERT is required. *
47 ***********************************************************/
48
49
50 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
51 (WIP_ENTITY_ID,
52 OPERATION_SEQ_NUM,
53 ORGANIZATION_ID,
54 INVENTORY_ITEM_ID,
55 COST_ELEMENT_ID,
56 APPLIED_MATL_VALUE,
57 LAST_UPDATED_BY,
58 LAST_UPDATE_DATE,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_LOGIN,
62 REQUEST_ID,
63 PROGRAM_APPLICATION_ID,
64 PROGRAM_ID,
65 PROGRAM_UPDATE_DATE)
66 SELECT
67 i_wip_entity_id,
68 i_op_seq_num,
69 i_org_id,
70 i_inv_item_id,
71 c.COST_ELEMENT_ID,
72 0,
73 i_user_id,
74 SYSDATE,
75 SYSDATE,
76 i_user_id,
77 i_login_id,
78 i_request_id,
79 i_prog_id,
80 i_prog_appl_id,
81 SYSDATE
82 from CST_LAYER_COST_DETAILS c
83 WHERE
84 c.LAYER_ID = i_layer_id AND
85 NOT EXISTS
86 (
87 SELECT 'X'
88 FROM
89 WIP_REQ_OPERATION_COST_DETAILS W2
90 WHERE
91 W2.COST_ELEMENT_ID = C.COST_ELEMENT_ID AND
92 W2.WIP_ENTITY_ID = i_wip_entity_id AND
93 W2.ORGANIZATION_ID = i_org_id AND
94 W2.INVENTORY_ITEM_ID = i_inv_item_id AND
95 W2.OPERATION_SEQ_NUM = i_op_seq_num
96 )
97 group by
98 c.COST_ELEMENT_ID;
99
100
101 --begin changes for bug 10162275
102 --If an item does not have clcd, the wrocd will not be created, then
103 --this item will be missing in the Discrete Job Value Report. Adding
104 --the following logic so that, if an item does not have clcd, and the
105 --corresponding wrocd is not yet created, then we create a dummy wrocd
106 --entry for this item with cost_element_id 1 and APPLIED_MATL_VALUE 0.
107 stmt_num := 12;
108 select count(*)
109 into l_clcd_count
110 from CST_LAYER_COST_DETAILS c
111 WHERE c.LAYER_ID = i_layer_id
112 and rownum = 1;
113
114 select count(*)
115 into l_wrocd_count
116 from WIP_REQ_OPERATION_COST_DETAILS W2
117 WHERE
118 W2.WIP_ENTITY_ID = i_wip_entity_id AND
119 W2.ORGANIZATION_ID = i_org_id AND
120 W2.INVENTORY_ITEM_ID = i_inv_item_id AND
121 W2.OPERATION_SEQ_NUM = i_op_seq_num AND
122 rownum = 1;
123
124 if(l_clcd_count = 0 and l_wrocd_count = 0) then
125 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
126 (WIP_ENTITY_ID,
127 OPERATION_SEQ_NUM,
128 ORGANIZATION_ID,
129 INVENTORY_ITEM_ID,
130 COST_ELEMENT_ID,
131 APPLIED_MATL_VALUE,
132 LAST_UPDATED_BY,
133 LAST_UPDATE_DATE,
134 CREATION_DATE,
135 CREATED_BY,
136 LAST_UPDATE_LOGIN,
137 REQUEST_ID,
138 PROGRAM_APPLICATION_ID,
139 PROGRAM_ID,
140 PROGRAM_UPDATE_DATE)
141 VALUES
142 (i_wip_entity_id,
143 i_op_seq_num,
144 i_org_id,
145 i_inv_item_id,
146 1,
147 0,
148 i_user_id,
149 SYSDATE,
150 SYSDATE,
151 i_user_id,
152 i_login_id,
153 i_request_id,
154 i_prog_id,
155 i_prog_appl_id,
156 SYSDATE);
157 end if;
158 --end changes for bug 10162275
159
160 stmt_num := 15;
161 CST_Utility_PUB.get_zeroCostIssue_flag (
162 p_api_version => 1.0,
163 x_return_status => l_return_status,
164 x_msg_count => l_msg_count,
165 x_msg_data => l_msg_data,
166 p_txn_id => i_trx_id,
167 x_zero_cost_flag => l_zero_cost_flag
168 );
169
170 if (l_return_status <> fnd_api.g_ret_sts_success) then
171 FND_FILE.put_line(FND_FILE.log, l_msg_data);
172 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
173 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
174 FND_MESSAGE.set_token('TEXT', l_api_message);
175 FND_MSG_pub.add;
176 raise fnd_api.g_exc_unexpected_error;
177 end if;
178
179 if (l_debug = 'Y') then
180 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag:'|| to_char(l_zero_cost_flag));
181 end if;
182
183 stmt_num := 20;
184
185 /**************************************************
186 * update wip_req_op_cost_details,per cost element *
187 * for the item that has been issued/returned. *
188 **************************************************/
189
190 UPDATE WIP_REQ_OPERATION_COST_DETAILS w
191 SET (LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
192 PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
193 applied_matl_value )=
194 (SELECT
195 i_user_id, i_login_id,
196 i_prog_appl_id,
197 i_prog_id,
198 SYSDATE,
199 nvl(w.applied_matl_value,0)+
200 (-1*i_txn_qty)*SUM(decode(l_zero_cost_flag, 1, 0, ITEM_COST))
201 from
202 CST_LAYER_COST_DETAILS c
203 WHERE
204 c.LAYER_ID = i_layer_id AND
205 c.COST_ELEMENT_ID=w.COST_ELEMENT_ID
206 GROUP BY c.COST_ELEMENT_ID
207 )
208 WHERE
209 w.WIP_ENTITY_ID=i_wip_entity_id and
210 w.INVENTORY_ITEM_ID=i_inv_item_id and
211 w.ORGANIZATION_ID=i_org_id and
212 w.OPERATION_SEQ_NUM=i_op_seq_num
213 AND exists
214 (select 'layer exists' from
215 CST_LAYER_COST_DETAILS c2
216 where c2.LAYER_ID = i_layer_id
217 and c2.cost_element_id = w.cost_element_id);
218
219
220 /****************************************************
221 * Insert into cst_txn_cst_details ... *
222 * is not required since this occurs at current avg *
223 * cost. *
224 *****************************************************/
225
226
227
228 EXCEPTION
229
230 WHEN OTHERS THEN
231 err_num := SQLCODE;
232 err_msg := 'CSTPACIR:' || to_char(stmt_num) || substr(SQLERRM,1,150);
233
234
235 END issue;
236
237
238 END CSTPACIR;