[Home] [Help]
PACKAGE BODY: APPS.CSTPACIR
Source
1 PACKAGE BODY CSTPACIR AS
2 /* $Header: CSTPACIB.pls 115.4 2003/07/29 22:35:36 lsoo 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 BEGIN
32
33 err_num := 0;
34 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
35 stmt_num := 10;
36
37
38 /***********************************************************
39 * 1. If a new cost element exists in cst_layer_cost_details*
40 * which did not exist during the last update, or *
41 * 2. This is the first issue being costed for the job, then*
42 * INSERT is required. *
43 ***********************************************************/
44
45
46 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
47 (WIP_ENTITY_ID,
48 OPERATION_SEQ_NUM,
49 ORGANIZATION_ID,
50 INVENTORY_ITEM_ID,
51 COST_ELEMENT_ID,
52 APPLIED_MATL_VALUE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 CREATION_DATE,
56 CREATED_BY,
57 LAST_UPDATE_LOGIN,
58 REQUEST_ID,
59 PROGRAM_APPLICATION_ID,
60 PROGRAM_ID,
61 PROGRAM_UPDATE_DATE)
62 SELECT
63 i_wip_entity_id,
64 i_op_seq_num,
65 i_org_id,
66 i_inv_item_id,
67 c.COST_ELEMENT_ID,
68 0,
69 i_user_id,
70 SYSDATE,
71 SYSDATE,
72 i_user_id,
73 i_login_id,
74 i_request_id,
75 i_prog_id,
76 i_prog_appl_id,
77 SYSDATE
78 from CST_LAYER_COST_DETAILS c
79 WHERE
80 c.LAYER_ID = i_layer_id AND
81 NOT EXISTS
82 (
83 SELECT 'X'
84 FROM
85 WIP_REQ_OPERATION_COST_DETAILS W2
86 WHERE
87 W2.COST_ELEMENT_ID = C.COST_ELEMENT_ID AND
88 W2.WIP_ENTITY_ID = i_wip_entity_id AND
89 W2.ORGANIZATION_ID = i_org_id AND
90 W2.INVENTORY_ITEM_ID = i_inv_item_id AND
91 W2.OPERATION_SEQ_NUM = i_op_seq_num
92 )
93 group by
94 c.COST_ELEMENT_ID;
95
96 stmt_num := 15;
97 CST_Utility_PUB.get_zeroCostIssue_flag (
98 p_api_version => 1.0,
99 x_return_status => l_return_status,
100 x_msg_count => l_msg_count,
101 x_msg_data => l_msg_data,
102 p_txn_id => i_trx_id,
103 x_zero_cost_flag => l_zero_cost_flag
104 );
105
106 if (l_return_status <> fnd_api.g_ret_sts_success) then
107 FND_FILE.put_line(FND_FILE.log, l_msg_data);
108 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
109 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
110 FND_MESSAGE.set_token('TEXT', l_api_message);
111 FND_MSG_pub.add;
112 raise fnd_api.g_exc_unexpected_error;
113 end if;
114
115 if (l_debug = 'Y') then
116 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag:'|| to_char(l_zero_cost_flag));
117 end if;
118
119 stmt_num := 20;
120
121 /**************************************************
122 * update wip_req_op_cost_details,per cost element *
123 * for the item that has been issued/returned. *
124 **************************************************/
125
126 UPDATE WIP_REQ_OPERATION_COST_DETAILS w
127 SET (LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
128 PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
129 applied_matl_value )=
130 (SELECT
131 i_user_id, i_login_id,
132 i_prog_appl_id,
133 i_prog_id,
134 SYSDATE,
135 nvl(w.applied_matl_value,0)+
136 (-1*i_txn_qty)*SUM(decode(l_zero_cost_flag, 1, 0, ITEM_COST))
137 from
138 CST_LAYER_COST_DETAILS c
139 WHERE
140 c.LAYER_ID = i_layer_id AND
141 c.COST_ELEMENT_ID=w.COST_ELEMENT_ID
142 GROUP BY c.COST_ELEMENT_ID
143 )
144 WHERE
145 w.WIP_ENTITY_ID=i_wip_entity_id and
146 w.INVENTORY_ITEM_ID=i_inv_item_id and
147 w.ORGANIZATION_ID=i_org_id and
148 w.OPERATION_SEQ_NUM=i_op_seq_num
149 AND exists
150 (select 'layer exists' from
151 CST_LAYER_COST_DETAILS c2
152 where c2.LAYER_ID = i_layer_id
153 and c2.cost_element_id = w.cost_element_id);
154
155
156 /****************************************************
157 * Insert into cst_txn_cst_details ... *
158 * is not required since this occurs at current avg *
159 * cost. *
160 *****************************************************/
161
162
163
164 EXCEPTION
165
166 WHEN OTHERS THEN
167 err_num := SQLCODE;
168 err_msg := 'CSTPACIR:' || to_char(stmt_num) || substr(SQLERRM,1,150);
169
170
171 END issue;
172
173
174 END CSTPACIR;