[Home] [Help]
PACKAGE BODY: APPS.CSTPIDIC
Source
1 PACKAGE BODY CSTPIDIC AS
2 /* $Header: CSTIDIOB.pls 120.1 2005/06/15 07:52:08 appldev $ */
3 PROCEDURE CSTPIDIO (
4
5 I_INVENTORY_ITEM_ID IN NUMBER,
6 I_ORGANIZATION_ID IN NUMBER,
7 I_LAST_UPDATED_BY IN NUMBER,
8 I_COST_TYPE_ID IN NUMBER,
9 I_ITEM_TYPE IN NUMBER,
10 I_LOT_SIZE IN NUMBER,
11 I_SHRINKAGE_RATE IN NUMBER,
12
13 O_RETURN_CODE OUT NOCOPY NUMBER,
14 O_RETURN_ERR OUT NOCOPY VARCHAR2) AS
15
16 p_def_matl_cost_code_id NUMBER;
17 p_dummy NUMBER;
18 p_location NUMBER;
19 l_avg_costing_option VARCHAR2(10);
20 l_avg_rates_cost_type_id NUMBER;
21 l_return_code NUMBER;
22 l_return_err VARCHAR2(80);
23
24 -- OPM INVCONV umoogala Skip inserting into CICD for Process Orgs.
25 l_process_enabled_flag VARCHAR2(1);
26
27 BEGIN
28
29 O_RETURN_ERR := ' ';
30
31 /*------------------------------------------------------------+
32 | Begin OPM INVCONV umoogala Process/discrete Xfer changes.
33 | Following query will return:
34 | 1 for process/discrete xfer
35 | 0 for discrete/discrete xfer
36 +------------------------------------------------------------*/
37 SELECT NVL(process_enabled_flag, 'N')
38 INTO l_process_enabled_flag
39 FROM mtl_parameters
40 WHERE organization_id = i_organization_id;
41
42 -- Skip inserting into CICD for process orgs
43 IF l_process_enabled_flag = 'Y'
44 THEN
45 RETURN;
46 END IF;
47 -- End OPM INVCONV umoogala
48
49 /* Assume that the inventory_asset_flag is set to 'Y'. */
50 IF I_COST_TYPE_ID = 1 THEN /* frozen */
51 /* Insert default material overhead */
52
53 p_location := 2;
54
55 l_return_code := 0;
56
57 CSTPACOV.ins_overhead (I_INVENTORY_ITEM_ID,
58 I_ORGANIZATION_ID,
59 I_LAST_UPDATED_BY,
60 I_COST_TYPE_ID,
61 I_ITEM_TYPE,
62 I_LOT_SIZE,
63 I_SHRINKAGE_RATE,
64 l_return_code,
65 l_return_err);
66
67 IF l_return_code <> 0 then
68 O_RETURN_CODE := l_return_code;
69 O_RETURN_ERR := l_return_err;
70 ELSE
71 O_RETURN_CODE := 0;
72 END IF;
73
74 END IF;
75
76 IF I_COST_TYPE_ID in (2,5,6) THEN /* Average,FIFO,LIFO */
77
78 p_location := 4;
79
80 SELECT
81 MP.DEFAULT_MATERIAL_COST_ID
82 INTO
83 p_def_matl_cost_code_id
84 FROM mtl_parameters MP
85 WHERE MP.organization_id = I_ORGANIZATION_ID;
86
87 /* Insert into CICD only if no rows exist now. */
88 p_location := 5;
89 select count(*)
90 into p_dummy
91 from cst_item_cost_details
92 where inventory_item_id = I_INVENTORY_ITEM_ID
93 AND organization_id = I_ORGANIZATION_ID
94 AND cost_type_id = I_COST_TYPE_ID;
95
96 p_location := 6;
97 /* Create a TL matl cost row in CST_ITEM_COST_DETAILS */
98 IF p_dummy = 0 THEN
99 INSERT INTO CST_ITEM_COST_DETAILS
100 ( INVENTORY_ITEM_ID,
101 ORGANIZATION_ID,
102 COST_TYPE_ID,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 CREATION_DATE,
106 CREATED_BY,
107 LEVEL_TYPE,
108 RESOURCE_ID,
109 USAGE_RATE_OR_AMOUNT,
110 BASIS_TYPE,
111 BASIS_FACTOR,
112 NET_YIELD_OR_SHRINKAGE_FACTOR,
113 ITEM_COST,
114 COST_ELEMENT_ID,
115 ROLLUP_SOURCE_TYPE)
116 VALUES (I_INVENTORY_ITEM_ID,
117 I_ORGANIZATION_ID,
118 I_COST_TYPE_ID,
119 sysdate,
120 I_LAST_UPDATED_BY,
121 sysdate,
122 I_LAST_UPDATED_BY,
123 1, /* TL */
124 p_def_matl_cost_code_id,
125 0,
126 1, /* item basis */
127 1,
128 DECODE(NVL(I_SHRINKAGE_RATE,-9),-9,1,1,0,
129 1 / (1 - I_SHRINKAGE_RATE)),
130 0,
131 1,
132 1);
133 END IF;
134
135 /* Get average costing profile and call CSTPACOV if profile option is set */
136 /* as 'Inventory and Work in Process' */
137
138 /*
139 gwu@us: this profile option is obsolete now in 11i. Forcing
140 it to 2 (INV/WIP)
141 FND_PROFILE.GET('CST_AVG_COSTING_OPTION', l_avg_costing_option);
142 */
143 l_avg_costing_option := 2;
144
145
146 IF l_avg_costing_option = '2' THEN
147
148 SELECT AVG_RATES_COST_TYPE_ID
149 INTO l_avg_rates_cost_type_id
150 FROM mtl_parameters
151 WHERE organization_id = I_ORGANIZATION_ID;
152
153 p_location := 7;
154
155 l_return_code := 0;
156
157 CSTPACOV.ins_overhead (I_INVENTORY_ITEM_ID,
158 I_ORGANIZATION_ID,
159 I_LAST_UPDATED_BY,
160 l_avg_rates_cost_type_id,
161 I_ITEM_TYPE,
162 I_LOT_SIZE,
163 I_SHRINKAGE_RATE,
164 l_return_code,
165 l_return_err);
166
167 IF l_return_code <> 0 then
168 O_RETURN_CODE := l_return_code;
169 O_RETURN_ERR := l_return_err;
170 ELSE
171 O_RETURN_CODE := 0;
172 END IF;
173
174 END IF;
175
176 END IF;
177
178 RETURN;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND THEN
182 O_RETURN_CODE := 0;
183 WHEN DUP_VAL_ON_INDEX THEN
184 O_RETURN_CODE := SQLCODE;
185 O_RETURN_ERR := 'CSTPIDIO(' || to_char(p_location) ||'):' || substrb(SQLERRM,1,68);
186 WHEN OTHERS THEN
187 O_RETURN_CODE := SQLCODE;
188 O_RETURN_ERR := 'CSTPIDIO(' || to_char(p_location) ||'):' || substrb(SQLERRM,1,68);
189 END CSTPIDIO;
190 END CSTPIDIC;