[Home] [Help]
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_PARAMETERS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSM_MTL_PARAMETERS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_PARAMETERS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSM_MTL_PARAMETERS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
20: g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS';
64: SELECT mtlp.organization_id
65: FROM mtl_parameters mtlp
66: WHERE NOT EXISTS
67: (SELECT 1
68: FROM csm_mtl_parameters_acc acc
69: WHERE acc.organization_id = mtlp.organization_id
70: );
71:
72: -- updates cur
73: CURSOR l_mtl_parameters_upd_csr(p_last_upd_date date)
74: IS
75: SELECT acc.access_id, mtlp.organization_id
76: FROM mtl_parameters mtlp,
77: csm_mtl_parameters_acc acc
78: WHERE (mtlp.creation_date < p_last_upd_date AND mtlp.last_update_date > p_last_upd_date)
79: AND acc.organization_id = mtlp.organization_id;
80:
81: -- deletes cur
81: -- deletes cur
82: CURSOR l_mtl_parameters_del_csr
83: IS
84: SELECT acc.access_id, acc.organization_id
85: FROM csm_mtl_parameters_acc acc
86: WHERE NOT EXISTS
87: (SELECT 1
88: FROM mtl_parameters mtlp
89: WHERE mtlp.organization_id = acc.organization_id
118: ASG_DOWNLOAD.DEL, sysdate);
119: END IF;
120:
121: -- delete from acc table
122: DELETE FROM csm_mtl_parameters_acc WHERE access_id = l_access_id;
123: END LOOP;
124:
125: -- process all updates
126: FOR r_mtl_parameters_upd_rec IN l_mtl_parameters_upd_csr(l_prog_update_date) LOOP
152: --get the users with this language
153: l_all_omfs_palm_resource_list := l_null_resource_list;
154: l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
155:
156: SELECT csm_mtl_parameters_acc_s.nextval
157: INTO l_access_id
158: FROM dual;
159:
160: --nullify the access list
169: l_single_access_id_list, l_all_omfs_palm_resource_list,
170: ASG_DOWNLOAD.INS, sysdate);
171: END IF;
172:
173: INSERT INTO csm_mtl_parameters_acc (access_id,
174: organization_id,
175: counter,
176: created_by,
177: creation_date,
237:
238: -- inserts cur
239: CURSOR c_mtl_parameters_ins_csr(b_user_id NUMBER,b_res_id NUMBER)
240: IS
241: SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,b_user_id as USER_ID, from_organization_id,to_organization_id
242: FROM ( SELECT distinct mtsn.from_organization_id,mtsn.to_organization_id
243: FROM MTL_INTERORG_PARAMETERS mtsn,csp_inv_loc_assignments inv
244: WHERE b_res_id =inv.resource_id
245: AND inv.organization_id in (mtsn.from_organization_id,mtsn.to_organization_id)
424:
425: -- inserts cur
426: CURSOR c_inv_org_freights_ins_csr
427: IS
428: SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,au.user_id, oft.organization_id,oft.freight_code,sm.ship_method_code
429: FROM ORG_FREIGHT_TL oft,asg_user au,WSH_CARRIER_SHIP_METHODS_V sm
430: WHERE oft.LANGUAGE=au.language
431: and nvl(oft.disable_date,sysdate+1) > sysdate
432: AND oft.organization_id=sm.organization_id(+)