[Home] [Help]
PACKAGE BODY: APPS.CSTPIICC
Source
1 PACKAGE BODY CSTPIICC AS
2 /* $Header: CSTIICIB.pls 120.1 2005/06/15 07:53:14 appldev $ */
3 PROCEDURE CSTPIICI (
4
5 i_item_id IN NUMBER,
6 i_org_id IN NUMBER,
7 i_user_id IN NUMBER,
8
9 o_return_code OUT NOCOPY NUMBER,
10 o_return_err OUT NOCOPY VARCHAR2) AS
11
12 l_planning_make_buy_code NUMBER;
13 l_shrinkage_rate NUMBER;
14 l_costing_method NUMBER;
15 l_cost_org NUMBER;
16 l_std_lot_size NUMBER;
17 l_inv_asset_flag NUMBER;
18 l_def_matl_cost_code_id NUMBER;
19
20 l_cost_type_id NUMBER;
21 l_item_type NUMBER;
22 l_return_code NUMBER;
23 l_return_err VARCHAR2(80);
24
25 l_temp NUMBER;
26
27 -- OPM INVCONV umoogala Skip inserting into CICD for Process Orgs.
28 l_process_enabled_flag VARCHAR2(1);
29
30 BEGIN
31
32 o_return_code := 0;
33 o_return_err := ' ';
34
35 /*------------------------------------------------------------+
36 | Begin OPM INVCONV sschinch/umoogala Process/discrete Xfer changes.
37 | Following query will return:
38 | 1 for process/discrete xfer
39 | 0 for discrete/discrete xfer
40 +------------------------------------------------------------*/
41 SELECT NVL(process_enabled_flag, 'N')
42 INTO l_process_enabled_flag
43 FROM mtl_parameters
44 WHERE organization_id = i_org_id;
45
46 -- Skip inserting into CICD for process orgs
47 IF l_process_enabled_flag = 'Y'
48 THEN
49 RETURN;
50 END IF;
51 -- End OPM INVCONV umoogala
52
53
54 SELECT
55 MP.primary_cost_method,
56 MP.cost_organization_id,
57 DECODE(DECODE(MSI.planning_make_buy_code,
58 1,MSI.planning_make_buy_code,
59 2,MSI.planning_make_buy_code,
60 2),
61 1,NVL(MSI.shrinkage_rate,0),
62 0),
63 NVL(MSI.std_lot_size,1),
64 DECODE(MSI.planning_make_buy_code,
65 1,MSI.planning_make_buy_code,
66 2,MSI.planning_make_buy_code,
67 2),
68 DECODE(MSI.inventory_asset_flag,
69 'Y', 1,
70 2),
71 MP.DEFAULT_MATERIAL_COST_ID
72 INTO l_costing_method,
73 l_cost_org,
74 l_shrinkage_rate,
75 l_std_lot_size,
76 l_planning_make_buy_code,
77 l_inv_asset_flag,
78 l_def_matl_cost_code_id
79 from mtl_system_items MSI,
80 mtl_parameters MP
81 WHERE MSI.inventory_item_id = i_item_id
82 AND MSI.organization_id = i_org_id
83 AND MSI.costing_enabled_flag = 'Y'
84 AND MP.organization_id = i_org_id;
85
86 IF l_cost_org = i_org_id THEN
87
88 l_cost_type_id := l_costing_method;
89
90 /*---------------------------------------------------------------------+
91 |
92 | man: It so happens that for planning_make_buy_code 1 => Make
93 | 2 => Buy
94 | for item_type 1 => Make
95 | 2 => Buy
96 | 3 => All
97 | for based_on_rollup 1 => Yes (Make)
98 | 2 => No (Buy)
99 |
100 +---------------------------------------------------------------------*/
101 l_item_type := l_planning_make_buy_code;
102
103 INSERT INTO cst_item_costs
104 (
105 inventory_item_id,
106 organization_id,
107 cost_type_id,
108 last_update_date,
109 last_updated_by,
110 creation_date,
111 created_by,
112 defaulted_flag,
113 shrinkage_rate,
114 lot_size,
115 based_on_rollup_flag,
116 inventory_asset_flag,
117 item_cost)
118 VALUES
119 (
120 i_item_id,
121 i_org_id,
122 l_cost_type_id,
123 sysdate,
124 i_user_id,
125 sysdate,
126 i_user_id,
127 2,
128 l_shrinkage_rate,
129 l_std_lot_size,
130 l_planning_make_buy_code,
131 l_inv_asset_flag,
132 0 );
133
134 l_return_code := 0;
135
136 IF l_inv_asset_flag = 1 THEN
137 /* insert default material overhead or TL matl cost row
138 in CST_ITEM_COST_DETAILS depending on cost method*/
139 CSTPIDIC.CSTPIDIO(i_item_id,
140 i_org_id,
141 i_user_id,
142 l_cost_type_id,
143 l_item_type,
144 l_std_lot_size,
145 l_shrinkage_rate,
146 l_return_code,
147 l_return_err);
148 END IF;
149
150 IF l_return_code <> 0 then
151 o_return_code := l_return_code;
152 o_return_err := l_return_err;
153 ELSE
154 o_return_code := 0;
155 END IF;
156
157 END IF;
158
159 EXCEPTION
160 WHEN DUP_VAL_ON_INDEX THEN
161 o_return_code := 0;
162 WHEN NO_DATA_FOUND THEN
163 BEGIN
164 select 0
165 into l_temp
166 from mtl_system_items msi
167 where msi.inventory_item_id = i_item_id
168 and msi.organization_id = i_org_id
169 and msi.costing_enabled_flag = 'N';
170
171 o_return_code := 0;
172
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 o_return_code := SQLCODE;
176 o_return_err := 'CSTPIICI:' || substrb(SQLERRM,1,70);
177 WHEN OTHERS THEN
178 o_return_code := SQLCODE;
179 o_return_err := 'CSTPIICI:' || substrb(SQLERRM,1,70);
180 END;
181 WHEN OTHERS THEN
182 o_return_code := SQLCODE;
183 o_return_err := 'CSTPIICI:' || substrb(SQLERRM,1,70);
184 END;
185 END CSTPIICC;