10: -- Person Date Comments
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_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_ITEM_LOCATIONS');
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_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_ITEM_LOCATIONS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_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_ITEM_LOCATIONS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
20: g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS_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_ITEM_LOCATIONS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
20: g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
21: g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
22:
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSM_MTL_ITEM_LOCATIONS');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_LOCATION_ID';
20: g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
21: g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
22:
23: PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
24: p_message OUT NOCOPY VARCHAR2)
25: IS
26: PRAGMA AUTONOMOUS_TRANSACTION;
27: l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
28: l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
29: l_dsql varchar2(2048);
30: l_pub_item VARCHAR2(30) := 'CSM_MTL_ITEM_LOCATIONS';
31: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
32: l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
33: l_mark_dirty boolean;
34: l_all_omfs_palm_resource_list asg_download.user_list;
54: mil.inventory_location_id,
55: mil.organization_id
56: FROM csm_inv_loc_ass_acc acc,
57: csp_inv_loc_assignments cila,
58: mtl_item_locations mil
59: WHERE cila.csp_inv_loc_assignment_id = acc.csp_inv_loc_assignment_id
60: AND mil.subinventory_code = cila.subinventory_code
61: AND mil.organization_id = cila.organization_id
62: AND NOT EXISTS
60: AND mil.subinventory_code = cila.subinventory_code
61: AND mil.organization_id = cila.organization_id
62: AND NOT EXISTS
63: (SELECT 1
64: FROM csm_mtl_item_locations_acc mil_acc
65: WHERE mil_acc.user_id = acc.user_id
66: AND mil_acc.inventory_location_id = mil.inventory_location_id
67: AND mil_acc.organization_id = mil.organization_id
68: );
71: CURSOR l_mtl_item_loc_upd_csr(p_last_upd_date DATE)
72: IS
73: SELECT acc.access_id,
74: acc.user_id
75: FROM csm_mtl_item_locations_acc acc,
76: mtl_item_locations mil
77: WHERE acc.inventory_location_id = mil.inventory_location_id
78: AND acc.organization_id = mil.organization_id
79: AND mil.last_update_date > p_last_upd_date;
72: IS
73: SELECT acc.access_id,
74: acc.user_id
75: FROM csm_mtl_item_locations_acc acc,
76: mtl_item_locations mil
77: WHERE acc.inventory_location_id = mil.inventory_location_id
78: AND acc.organization_id = mil.organization_id
79: AND mil.last_update_date > p_last_upd_date;
80:
84: SELECT acc.access_id,
85: acc.inventory_location_id,
86: acc.organization_id,
87: acc.user_id
88: FROM csm_mtl_item_locations_acc acc
89: WHERE NOT EXISTS
90: (SELECT 1
91: FROM csm_inv_loc_ass_acc cila_acc,
92: csp_inv_loc_assignments cila,
89: WHERE NOT EXISTS
90: (SELECT 1
91: FROM csm_inv_loc_ass_acc cila_acc,
92: csp_inv_loc_assignments cila,
93: mtl_item_locations mil
94: WHERE cila_acc.user_id = acc.user_id
95: AND cila_acc.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
96: AND mil.inventory_location_id = acc.inventory_location_id
97: AND mil.organization_id = acc.organization_id