1 package body INVATTR as
2 /* $Header: INVATTRB.pls 120.1 2005/07/01 12:11:45 appldev ship $ */
3
4 PROCEDURE correct_attr(master_org_id number,
5 item_id NUMBER
6 ) is
7 CURSOR cc is
8 select
9 ORGANIZATION_ID,
10 INVENTORY_ITEM_FLAG,
11 STOCK_ENABLED_FLAG,
12 PURCHASING_ITEM_FLAG,
13 INVOICEABLE_ITEM_FLAG,
14 BOM_ITEM_TYPE,
15 CUSTOMER_ORDER_FLAG,
16 INTERNAL_ORDER_FLAG
17 from mtl_system_items
18 where inventory_item_id = item_id
19 and organization_id in ( select organization_id
20 from mtl_parameters where master_organization_id =master_org_id
21 and organization_id <> master_org_id);
22
23 -- Attributes that are Item level (can't be different from master org's value)
24
25 CURSOR ee is
26 select attribute_name,
27 control_level
28 from MTL_ITEM_ATTRIBUTES
29 where control_level = 1
30 order by attribute_name desc ;
31
32 A_STOCK_ENABLED number := 2;
33 A_MTL_TRANSACTIONS_ENABLED number := 2;
34 A_PURCHASING_ENABLED number := 2;
35 A_INVOICE_ENABLED number := 2;
36 A_BUILD_IN_WIP number := 2;
37 A_CUSTOMER_ORDER_ENABLED number := 2;
38 A_BOM_ENABLED number := 2;
39 A_INTERNAL_ORDER_ENABLED number := 2;
40
41 B_STOCK_ENABLED varchar2(1);
42 B_MTL_TRANSACTIONS_ENABLED varchar2(1);
43 B_PURCHASING_ENABLED varchar2(1);
44 B_INVOICE_ENABLED varchar2(1);
45 B_BUILD_IN_WIP varchar2(1);
46 B_CUSTOMER_ORDER_ENABLED varchar2(1);
47 B_BOM_ENABLED varchar2(1);
48 B_INTERNAL_ORDER_ENABLED varchar2(1);
49 begin
50
51 for att in ee loop
52
53 if substr(att.attribute_name,18) = 'STOCK_ENABLED_FLAG' then
54 A_STOCK_ENABLED := att.control_level;
55 end if;
56 if substr(att.attribute_name,18) = 'MTL_TRANSACTIONS_ENABLED_FLAG' then
57 A_MTL_TRANSACTIONS_ENABLED := att.control_level;
58 end if;
59 if substr(att.attribute_name,18) = 'PURCHASING_ENABLED_FLAG' then
60 A_PURCHASING_ENABLED := att.control_level;
61 end if;
62 if substr(att.attribute_name,18) = 'INVOICE_ENABLED_FLAG' then
63 A_INVOICE_ENABLED := att.control_level;
64 end if;
65 if substr(att.attribute_name,18) = 'CUSTOMER_ORDER_ENABLED_FLAG' then
66 A_CUSTOMER_ORDER_ENABLED := att.control_level;
67 end if;
68 if substr(att.attribute_name,18) = 'INTERNAL_ORDER_ENABLED_FLAG' then
69 A_INTERNAL_ORDER_ENABLED := att.control_level;
70 end if;
71 if substr(att.attribute_name,18) = 'BOM_ENABLED_FLAG' then
72 A_BOM_ENABLED := att.control_level;
73 end if;
74 if substr(att.attribute_name,18) = 'BUILD_IN_WIP_FLAG' then
75 A_BUILD_IN_WIP := att.control_level;
76 end if;
77
78 end loop;
79
80 -- validate the records
81
82 for cr in cc loop
83
84 if (cr.inventory_item_flag ='N' and A_STOCK_ENABLED=1) then
85 B_STOCK_ENABLED := 'N' ;
86 else
87 B_STOCK_ENABLED := 'Z' ;
88 end if ;
89
90 if ((cr.stock_enabled_flag ='N' or cr.inventory_item_flag ='N')
91 and A_MTL_TRANSACTIONS_ENABLED=1) then
92 B_MTL_TRANSACTIONS_ENABLED := 'N' ;
93 else
94 B_MTL_TRANSACTIONS_ENABLED := 'Z' ;
95 end if ;
96
97
98 if (cr.customer_order_flag ='N' and A_CUSTOMER_ORDER_ENABLED=1) then
99 B_CUSTOMER_ORDER_ENABLED := 'N' ;
100 else
101 B_CUSTOMER_ORDER_ENABLED := 'Z' ;
102 end if ;
103
104 if (cr.inventory_item_flag ='N' and A_BOM_ENABLED=1) then
105 B_BOM_ENABLED := 'N' ;
106 else
107 B_BOM_ENABLED := 'Z' ;
108 end if ;
109
110 if (cr.internal_order_flag ='N' and A_INTERNAL_ORDER_ENABLED=1) then
111 B_INTERNAL_ORDER_ENABLED := 'N' ;
112 else
113 B_INTERNAL_ORDER_ENABLED := 'Z' ;
114 end if ;
115
116
117 if (cr.invoiceable_item_flag ='N' and A_INVOICE_ENABLED=1) then
118 B_INVOICE_ENABLED := 'N' ;
119 else
120 B_INVOICE_ENABLED := 'Z' ;
121 end if ;
122
123
124 if (cr.purchasing_item_flag ='N' and A_PURCHASING_ENABLED=1) then
125 B_PURCHASING_ENABLED := 'N' ;
126 else
127 B_PURCHASING_ENABLED := 'Z' ;
128 end if ;
129
130
131 if ((cr.inventory_item_flag ='N' or cr.bom_item_type <> 4 ) and A_BUILD_IN_WIP=1) then
132 B_BUILD_IN_WIP := 'N' ;
133 else
134 B_BUILD_IN_WIP := 'Z' ;
135 end if ;
136
137 update mtl_system_items
138 set
139 stock_enabled_flag = decode(B_STOCK_ENABLED,'N','N',stock_enabled_flag),
140 mtl_transactions_enabled_flag = decode(B_MTL_TRANSACTIONS_ENABLED,'N','N',mtl_transactions_enabled_flag),
141 purchasing_enabled_flag = decode(B_PURCHASING_ENABLED,'N','N',purchasing_enabled_flag),
142 invoice_enabled_flag = decode(B_INVOICE_ENABLED,'N','N',invoice_enabled_flag),
143 customer_order_enabled_flag = decode(B_CUSTOMER_ORDER_ENABLED,'N','N',customer_order_enabled_flag),
144 internal_order_enabled_flag = decode(B_INTERNAL_ORDER_ENABLED,'N','N',internal_order_enabled_flag),
145 bom_enabled_flag = decode(B_BOM_ENABLED,'N','N',bom_enabled_flag),
146 build_in_wip_flag = decode(B_BUILD_IN_WIP,'N','N',build_in_wip_flag)
147 where inventory_item_id = item_id
148 and organization_id = cr.organization_id;
149
150 end loop;
151 exception
152 when others then
153 rollback;
154 end correct_attr;
155
156
157 end INVATTR;
[Home] [Help]