[Home] [Help]
PACKAGE BODY: APPS.WSMPLCVA
Source
1 PACKAGE BODY WSMPLCVA AS
2 /* $Header: WSMLCVAB.pls 120.1 2006/03/27 20:42:37 mprathap noship $ */
3
4 --**********************************************************************************************
5 PROCEDURE load_org_table IS
6
7 -- BUG 3934661
8 -- when calling dbms_utility.get_hash_value use larger seed number
9 -- OLD: dbms_utility.get_hash_value(str, 1000, 5625);
10 -- NEW: dbms_utility.get_hash_value(str, 37, 1073741824);
11
12 --abb H optional scrap accounting added ESTIMATED_SCRAP_ACCOUNTING
13
14 cursor c_wsm_param is
15 select wsm.ORGANIZATION_ID,
16 wsm.COPRODUCTS_SUPPLY_DEFAULT,
17 wsm.DEFAULT_ACCT_CLASS_CODE,
18 wsm.OP_SEQ_NUM_INCREMENT,
19 wsm.LAST_OPERATION_SEQ_NUM,
20 wsm.ESTIMATED_SCRAP_ACCOUNTING,
21 NULL,
22 NULL,
23 wp.po_creation_time
24 --from MTL_PARAMETERS MP , WSM_PARAMETERS WSM, ORG_ORGANIZATION_DEFINITIONS ORG
25 from MTL_PARAMETERS MP , WSM_PARAMETERS WSM, HR_ALL_ORGANIZATION_UNITS ORG, WIP_PARAMETERS WP
26 where MP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
27 and ORG.ORGANIZATION_ID = WSM.ORGANIZATION_ID
28 and WP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
29 and UPPER(MP.WSM_ENABLED_FLAG)='Y'
30 and TRUNC(SYSDATE) <= NVL(ORG.DATE_TO, SYSDATE+1);
31
32 v_index NUMBER;
33
34 l_error_code number := 0; -- BUG3126650
35 l_error_msg varchar2(2000) := null; -- BUG3126650
36
37 BEGIN
38
39 open c_wsm_param;
40 loop
41 fetch c_wsm_param into v_rec_wsm_param;
42 v_org(v_rec_wsm_param.ORGANIZATION_ID) := v_rec_wsm_param;
43 exit when c_wsm_param%notfound;
44 end loop;
45 close c_wsm_param;
46
47 v_index := v_org.first;
48 while v_index <= v_org.last
49 loop
50 -- BEGIN: BUG3126650
51 --SELECT max(acct_period_id)
52 --INTO v_org(v_index).MAX_ORG_ACC_PERIODS
53 --FROM ORG_ACCT_PERIODS
54 --WHERE organization_id = v_index
55 --AND period_start_date <= trunc(SYSDATE)
56 --AND open_flag = 'Y';
57 v_org(v_index).MAX_ORG_ACC_PERIODS := WSMPUTIL.GET_INV_ACCT_PERIOD(
58 x_err_code => l_error_code,
59 x_err_msg => l_error_msg,
60 p_organization_id => v_index,
61 p_date => sysdate);
62 -- END: BUG3126650
63
64 SELECT max(stock_locator_control_code)
65 INTO v_org(v_index).MAX_STK_LOC_CNTRL
66 FROM mtl_parameters
67 WHERE organization_id = v_index;
68
69 v_index := v_org.next(v_index);
70 end loop;
71
72 END load_org_table;
73
74 --**********************************************************************************************
75
76 PROCEDURE load_class_code IS
77
78 type t_wlji_org2 is table of wip_accounting_classes.organization_id%type;
79 type t_wlji_cc is table of wip_accounting_classes.class_code%type;
80
81 v_wlji_org2 t_wlji_org2 := t_wlji_org2();
82 v_wlji_cc t_wlji_cc := t_wlji_cc();
83
84 cursor c_wsm_wac is
85 select organization_id,
86 class_code
87 from wip_accounting_classes
88 where nvl(disable_date, sysdate+2) > sysdate
89 and class_type = 5 ;
90
91
92 str VARCHAR2(100); -- assuming that to_char(org_id)||class_code has length < 100
93 hash_value NUMBER;
94 v_index NUMBER;
95
96 BEGIN
97
98 open c_wsm_wac;
99 loop
100 fetch c_wsm_wac bulk collect into v_wlji_org2, v_wlji_cc;
101 exit when c_wsm_wac%notfound;
102 end loop;
103 close c_wsm_wac;
104
105 v_index := v_wlji_org2.first;
106 while v_index <= v_wlji_org2.last
107 loop
108 str := to_char(v_wlji_org2(v_index))||v_wlji_cc(v_index);
109 hash_value := dbms_utility.get_hash_value(str, 37, 1073741824);
110 v_class_code(hash_value) := v_wlji_cc(v_index);
111
112 v_index := v_wlji_org2.next(v_index);
113 end loop;
114
115 END load_class_code;
116
117 --**********************************************************************************************
118
119 PROCEDURE load_subinventory IS
120
121 type t_wlji_org1 is table of wsm_subinventory_extensions.organization_id%type;
122 type t_wlji_compl_subinv is table of wsm_subinventory_extensions.secondary_inventory_name%type;
123
124 v_wlji_org1 t_wlji_org1 := t_wlji_org1();
125 v_wlji_compl_subinv t_wlji_compl_subinv := t_wlji_compl_subinv();
126
127 cursor c_wsm_subinv is
128 select organization_id,
129 secondary_inventory_name
130 from wsm_subinventory_extensions;
131
132 str VARCHAR2(100); -- assuming that to_char(org_id)||subinventory has length < 100
133 hash_value NUMBER;
134 v_index NUMBER;
135
136 BEGIN
137
138 open c_wsm_subinv;
139 loop
140 fetch c_wsm_subinv bulk collect into v_wlji_org1, v_wlji_compl_subinv;
141 exit when c_wsm_subinv%notfound;
142 end loop;
143 close c_wsm_subinv;
144
145 v_index := v_wlji_org1.first;
146 while v_index <= v_wlji_org1.last
147 loop
148 str := to_char(v_wlji_org1(v_index))||v_wlji_compl_subinv(v_index);
149 hash_value := dbms_utility.get_hash_value(str, 37, 1073741824);
150 v_subinv(hash_value) := v_wlji_compl_subinv(v_index);
151
152 v_index := v_wlji_org1.next(v_index);
153 end loop;
154
155 END load_subinventory;
156
157 --**********************************************************************************************
158
159 END WSMPLCVA;