[Home] [Help]
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSF_M_ITEM_INSTANCES');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
12: -- Enter procedure, function bodies as shown below
13:
14: g_table_name1 CONSTANT VARCHAR2(30) := 'CSI_ITEM_INSTANCES';
15: g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC';
16: g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_ITEM_INSTANCES_ACC_S';
17: g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18: CSM_ACC_PKG.t_publication_item_list('CSF_M_ITEM_INSTANCES');
19: g_pk1_name1 CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
20: g_pub_item CONSTANT VARCHAR2(30) := 'CSF_M_ITEM_INSTANCES';
30: PROCEDURE ITEM_INSTANCE_MDIRTY_U_ECHUSER(p_instance_id IN NUMBER,
31: p_error_msg OUT NOCOPY VARCHAR2,
32: x_return_status IN OUT NOCOPY VARCHAR2)
33: IS
34: cursor l_instance_users_csr (p_instance_id csm_item_instances_acc.instance_id%TYPE) is
35: SELECT access_id, user_id
36: FROM csm_item_instances_acc
37: WHERE instance_id = p_instance_id;
38:
32: x_return_status IN OUT NOCOPY VARCHAR2)
33: IS
34: cursor l_instance_users_csr (p_instance_id csm_item_instances_acc.instance_id%TYPE) is
35: SELECT access_id, user_id
36: FROM csm_item_instances_acc
37: WHERE instance_id = p_instance_id;
38:
39: BEGIN
40: x_return_status := FND_API.G_RET_STS_SUCCESS;
155: OPEN c_parent_instance(p_instance_id );
156: FETCH c_parent_instance INTO l_parent_ins;
157: CLOSE c_parent_instance;
158:
159: UPDATE csm_item_instances_acc
160: SET PARENT_INSTANCE_ID = l_parent_ins,
161: VERSION_LABEL = l_label
162: WHERE USER_ID = p_user_id
163: AND INSTANCE_ID = p_instance_id;
217: IS
218: l_ref_exists NUMBER := 0 ;
219:
220: /** Check if any other SR refers given instance and user*/
221: CURSOR l_check_instance_ref(l_instance_id csm_item_instances_acc.instance_id%TYPE,
222: l_user_id csm_item_instances_acc.user_id%TYPE)
223: IS
224: SELECT 1
225: FROM csm_item_instances_acc a,
218: l_ref_exists NUMBER := 0 ;
219:
220: /** Check if any other SR refers given instance and user*/
221: CURSOR l_check_instance_ref(l_instance_id csm_item_instances_acc.instance_id%TYPE,
222: l_user_id csm_item_instances_acc.user_id%TYPE)
223: IS
224: SELECT 1
225: FROM csm_item_instances_acc a,
226: cs_incidents_all_b b,
221: CURSOR l_check_instance_ref(l_instance_id csm_item_instances_acc.instance_id%TYPE,
222: l_user_id csm_item_instances_acc.user_id%TYPE)
223: IS
224: SELECT 1
225: FROM csm_item_instances_acc a,
226: cs_incidents_all_b b,
227: csm_incidents_all_acc c
228: WHERE a.instance_id = l_instance_id
229: AND a.user_id = l_user_id
247: END IF ;
248: CLOSE l_check_instance_ref ;
249:
250: IF L_REF_EXISTS <> 1 THEN
251: -- delete from csm_item_instances_acc
252: CSM_ACC_PKG.Delete_Acc
253: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
254: ,P_ACC_TABLE_NAME => g_acc_table_name1
255: ,P_PK1_NAME => g_pk1_name1
304: IS
305: SELECT acc.access_id,
306: acc.user_ID
307: --acc.INSTANCE_ID
308: FROM csm_item_instances_acc acc
309: , csi_item_instances cii
310: , csi_instance_statuses iis
311: , asg_user asg
312: WHERE cii.instance_id = acc.instance_id
324: IS
325: SELECT acc.access_id,
326: acc.user_ID,
327: acc.INSTANCE_ID
328: FROM csm_item_instances_acc acc
329: , csi_item_instances cii
330: , asg_user asg
331: WHERE cii.instance_id = acc.instance_id
332: AND cii.location_type_code = 'INVENTORY'
349: ;
350: --bug 5184539
351: CURSOR l_iteminstances_upd_csr(p_last_run_date DATE)
352: IS
353: SELECT /* index (acc CSM_ITEM_INSTANCES_ACC_U1) */
354: acc.user_id,
355: --acc.instance_id,
356: acc.access_id
357: FROM csm_item_instances_acc acc,
353: SELECT /* index (acc CSM_ITEM_INSTANCES_ACC_U1) */
354: acc.user_id,
355: --acc.instance_id,
356: acc.access_id
357: FROM csm_item_instances_acc acc,
358: csi_item_instances cii,
359: asg_user asg
360: WHERE cii.instance_id = acc.instance_id
361: AND asg.user_id = asg.owner_id
365:
366: -- get all the trackable items that exists in the csm_mtl_onhand_acc table
367: CURSOR l_ins_item_instances_csr
368: IS
369: SELECT CSM_ITEM_INSTANCES_ACC_S.NEXTVAL as access_id,
370: cqa.user_id,
371: cii.instance_id,
372: cqa.user_id,
373: cii.instance_id,
401: AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
402: AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
403: AND NOT EXISTS
404: ( SELECT 1
405: FROM csm_item_instances_acc cia
406: WHERE cia.user_id = cqa.user_id
407: AND cia.instance_id = cii.instance_id
408: );
409:
417: csi_ii_relationships cii
418: WHERE cii.relationship_id = acc.relationship_id
419: AND NOT EXISTS
420: (SELECT 1
421: FROM csm_item_instances_acc ins_acc
422: WHERE ins_acc.user_id = acc.user_id
423: AND (ins_acc.instance_id = cii.object_id OR ins_acc.instance_id = cii.subject_id)
424: );
425:
463: WHERE ACC.user_id = b_useR_id
464: AND ACC.relationship_id = cii.relationship_id);
465:
466:
467: TYPE instance_idTab IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
468: TYPE rel_idTab IS TABLE OF csm_ii_relationships_acc.relationship_id%TYPE INDEX BY BINARY_INTEGER;
469: TYPE ver_lab_Tab IS TABLE OF csi_i_version_labels.version_label%TYPE INDEX BY BINARY_INTEGER;
470: l_rel_id_lst rel_idTab;
471: l_user_id_lst asg_download.user_list;
512: );
513:
514: -- do a bulk delete
515: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
516: DELETE FROM CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
517:
518: COMMIT;--IB Deletes are commited
519:
520: IF l_acc_id_lst.COUNT > 0 THEN
553: );
554:
555: -- do a bulk delete
556: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
557: DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
558: END IF;
559: */
560:
561: -- get the updates to item instances
616: CLOSE l_ins_item_instances_csr;
617:
618: IF l_acc_id_lst.COUNT > 0 THEN
619: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
620: INSERT INTO CSM_ITEM_INSTANCES_ACC (ACCESS_ID, USER_ID, INSTANCE_ID,
621: COUNTER,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
622: LAST_UPDATE_LOGIN,VERSION_LABEL, PARENT_INSTANCE_ID)
623: VALUES (l_acc_id_lst(i), l_user_id_lst(i), l_instance_id_lst(i),
624: 1, 1, l_current_run_date,1,l_current_run_date,
746: END IF;
747:
748: --If the relationship are updated then the instance should be updated with the correct parent
749: FORALL i in l_parent_inst_id_lst.FIRST..l_parent_inst_id_lst.LAST
750: UPDATE csm_item_instances_acc
751: SET PARENT_INSTANCE_ID = l_parent_inst_id_lst(i)
752: WHERE USER_ID = l_user_id_lst(i)
753: AND INSTANCE_ID = l_instance_id_lst(i)
754: RETURNING access_id BULK COLLECT INTO l_acc_id_lst ;
864: p_instance_id NUMBER,
865: p_parent_instance_id NUMBER )
866: IS
867: SELECT acc.instance_id
868: FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii
869: WHERE acc.instance_id = cii.instance_id
870: AND acc.user_id = p_user_id
871: AND owner_party_id = p_party_id
872: AND ( ( cii.location_id = p_party_site_id
878: )
879: AND acc.instance_id NOT IN
880: (
881: SELECT acc.instance_id
882: FROM CSM_ITEM_INSTANCES_ACC acc
883: WHERE acc.user_id = p_user_id
884: AND acc.instance_id IN (p_instance_id, p_parent_instance_id)
885: UNION
886: SELECT acc.instance_id
883: WHERE acc.user_id = p_user_id
884: AND acc.instance_id IN (p_instance_id, p_parent_instance_id)
885: UNION
886: SELECT acc.instance_id
887: FROM CSM_ITEM_INSTANCES_ACC acc
888: WHERE acc.user_id = p_user_id
889: AND acc.instance_id IN
890: (
891: SELECT subject_id
909: WHERE si.inventory_item_id = cii.inventory_item_id
910: AND si.organization_id = NVL( cii.inv_organization_id,
911: cii.inv_master_organization_id )
912: AND cii.instance_id NOT IN
913: ( SELECT acc.instance_id FROM CSM_ITEM_INSTANCES_ACC acc
914: WHERE acc.user_id = p_user_id
915: )
916: AND owner_party_id = p_party_id
917: AND ( ( cii.location_id = p_party_site_id
1021: AND cntr_grps.counter_group_id(+) = cntrs.group_id
1022: AND cas.source_object_id = p_instance_id
1023: -- get only records for the instance belonging to the user
1024: AND EXISTS (SELECT 1
1025: FROM csm_item_instances_acc acc
1026: WHERE acc.user_id = p_user_id
1027: AND acc.instance_id = cas.source_object_id) ;
1028:
1029: BEGIN
1068: p_location_id NUMBER,
1069: p_party_id NUMBER,
1070: p_incident_id NUMBER ) IS
1071: SELECT acc.instance_id
1072: FROM CSM_ITEM_INSTANCES_ACC acc, CSI_ITEM_INSTANCES cii,
1073: csm_incidents_all_acc iacc, cs_incidents_all_b cia
1074: WHERE acc.instance_id = cii.instance_id
1075: AND acc.user_id = p_user_id
1076: AND acc.counter <> 1 -- do not delete if there is just 1 instance of the IB item
1185: l_sqlerrmsg VARCHAR2(4000);
1186: l_error_msg VARCHAR2(4000);
1187: l_dummy BOOLEAN;
1188:
1189: TYPE instance_idTab IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
1190: l_instance_id_lst instance_idTab;
1191: l_user_id_lst asg_download.user_list;
1192: l_acc_id_lst asg_download.access_list;
1193:
1196: IS
1197: SELECT acc.access_id,
1198: acc.user_ID,
1199: acc.INSTANCE_ID
1200: FROM csm_item_instances_acc acc
1201: , csi_item_instances cii
1202: WHERE cii.instance_id = acc.instance_id
1203: AND cii.location_type_code = 'INVENTORY'
1204: AND cii.inventory_item_id = p_inv_item_id
1237: );
1238:
1239: -- do a bulk delete
1240: FORALL i IN l_acc_id_lst.FIRST..l_acc_id_lst.LAST
1241: DELETE CSM_ITEM_INSTANCES_ACC WHERE ACCESS_ID = l_acc_id_lst(i);
1242: END IF;
1243:
1244: CSM_UTIL_PKG.LOG('Leaving CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV ',
1245: 'CSM_ITEM_INSTANCE_EVENT_PKG.DELETE_IB_NOTIN_INV',FND_LOG.LEVEL_PROCEDURE);