[Home] [Help]
15:
16: /*** Globals ***/
17: g_mtl_sys_items_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC';
18: g_mtl_sys_items_table_name CONSTANT VARCHAR2(30) := 'CS_MTL_SYSTEM_ITEMS';
19: g_mtl_sys_items_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC_S' ;
20: g_mtl_sys_items_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
21: g_mtl_sys_items_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22: g_mtl_sys_items_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
23: CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SYSTEM_ITEMS');
51: -- Both category and cat set are null
52: CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
53: b_changed VARCHAR2, b_last_run_date DATE)
54: IS
55: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
56: FROM MTL_SYSTEM_ITEMS_B msi
57: WHERE msi.organization_id = b_organization_id
58: AND NOT EXISTS
59: ( SELECT 1
65: -- Category is not null and Cat set is null
66: CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
67: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
68: IS
69: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
70: itemcat.organization_id, b_user_id
71: FROM mtl_item_categories itemcat
72: WHERE itemcat.category_id = b_category_id
73: AND itemcat.organization_id = b_organization_id
82: -- Category is null and Cat Set is not null
83: CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
84: b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
85: IS
86: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
87: itemcat.organization_id, b_user_id
88: FROM mtl_item_categories itemcat
89: WHERE itemcat.category_set_id = b_category_set_id
90: AND itemcat.organization_id = b_organization_id
99: -- Both Category and Category set are not null
100: CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
101: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
102: IS
103: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
104: itemcat.organization_id, b_user_id
105: FROM mtl_item_categories itemcat
106: WHERE itemcat.category_set_id = b_category_set_id
107: AND itemcat.category_id = b_category_id
136:
137: ---items to download for insert
138: CURSOR c_all_items_for_org (b_organization_id NUMBER)
139: IS
140: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id, organization_id
141: FROM MTL_SYSTEM_ITEMS_B msi
142: WHERE msi.organization_id = b_organization_id;
143:
144:
141: FROM MTL_SYSTEM_ITEMS_B msi
142: WHERE msi.organization_id = b_organization_id;
143:
144:
145: --csm_mtl_system_items_acc_s.NEXTVAL,
146:
147: l_check_org VARCHAR2(10) := 'N';
148: l_profile_value VARCHAR2(10);
149: l_number NUMBER := -1;
187:
188: --Do a direct into into the ACC table for user -1
189: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
190: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
191: SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,
192: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
193: FROM MTL_SYSTEM_ITEMS_B b
194: WHERE b.ORGANIZATION_ID = p_organization_id
195: AND b.enabled_flag = 'Y'
700:
701: --Do a direct into into the ACC table for user -2
702: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
703: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
704: SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,
705: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
706: FROM MTL_SYSTEM_ITEMS_B msi
707: WHERE msi.enabled_flag = 'Y'
708: AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
1086: IS
1087:
1088: CURSOR c_htm5_mtl_items
1089: IS
1090: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id,org_id ,user_id
1091: FROM ( select c.inventory_item_id,d.destination_organization_id org_id ,a.user_id
1092: from csm_req_lines_acc a, csp_req_line_details b ,
1093: oe_order_lines_all c,csp_requirement_headers d
1094: ,csp_requirement_lines e ,asg_user au
1189: and inc.org_id=a.organization_id); --sr item
1190:
1191: CURSOR c_get_ins_items(b_ret_acty_date DATE,
1192: b_max_mtl_items NUMBER) IS
1193: select csm_mtl_system_items_acc_s.NEXTVAL,item_id,msi.org_id,au.user_id
1194: from (select item_id,org_id from
1195: (select inventory_item_id as item_id,
1196: nvl(receiving_inventory_org_id,issuing_inventory_org_id) as org_id,
1197: sum(quantity) tot from csf_debrief_lines b