[Home] [Help]
282: , eac.CHANGE_DOCUMENT_ID, eac.entity_name, eac.pk1_value, eac.pk2_value, eac.pk3_value
283: , decode(eac.action_type, 'ATTACH', eac.source_document_id,fad.document_id) document_id
284: , eac.FAMILY_ID, eac.REPOSITORY_ID, eac.PK4_VALUE, eac.PK5_VALUE, eac.NEW_FILE_NAME
285: , eac.DESCRIPTIOn, eac.NEW_DESCRIPTIOn, eac.NEW_CATEGORY_ID
286: FROM eng_attachment_changes eac, fnd_attached_documents fad
287: WHERE eac.change_id = p_change_id -- 4517503
288: AND eac.revised_item_sequence_id = cp_revised_item_sequence_id
289: AND eac.attachment_id = fad.attached_document_id(+)
290: AND (eac.entity_name = 'MTL_ITEM_REVISIONS'
300: AND ecpm.revised_line_id1 = eac.change_document_id
301: AND ecpm.entity_action_status = 3);
302:
303: CURSOR c_attachment_details(cp_attached_document_id IN NUMBER
304: , cp_attach_action_type IN eng_attachment_changes.action_type%TYPE
305: , cp_change_document_id IN NUMBER)
306: IS
307: SELECT fad.category_id, fad.status, fad.document_id
308: , fad.attached_document_id, fdv.file_name, fad.last_updated_by
314: UNION ALL
315: SELECT eac.category_id, eac.previous_status, eac.source_document_id
316: , eac.attachment_id, eac.file_name, eac.last_updated_by
317: , eac.dm_type, eac.datatype_id, eac.source_document_id
318: FROM eng_attachment_changes eac, fnd_documents_vl fdv
319: WHERE eac.change_document_id = cp_change_document_id
320: AND eac.source_document_id = fdv.document_id
321: AND cp_attach_action_type = 'ATTACH';
322:
357: Eng_Propagation_log_Util.Debug_Log(G_LOG_STMT, 'Value: p_revised_item_unexp_rec.new_item_revision_id'|| p_revised_item_unexp_rec.new_item_revision_id);
358: Eng_Propagation_log_Util.Debug_Log(G_LOG_STMT, 'Value: p_revised_item_unexp_rec.CURRENT_item_revision_id'|| p_revised_item_unexp_rec.CURRENT_item_revision_id);
359:
360: -- Bug : 5355614
361: -- For item attachment changes with new revisions we need to get the correct revision id (eng_attachment_changes.pk3_value)
362: BEGIN
363: select revision_id
364: into l_pk3_value from mtl_item_revisions
365: where inventory_item_id = (select revised_item_id
396: IF (ac.action_type = 'ATTACH' OR l_local_attachment_id <> -1)
397: THEN
398: FOR cad IN c_attachment_details(l_local_attachment_id, ac.action_type, ac.change_document_id)
399: LOOP
400: Eng_Propagation_log_Util.Debug_Log(G_LOG_STMT, 'Inserting into eng_attachment_changes..');
401: SELECT ENG_ATTACHMENT_CHANGES_S.nextval
402: INTO l_change_document_id
403: FROM dual;
404:
397: THEN
398: FOR cad IN c_attachment_details(l_local_attachment_id, ac.action_type, ac.change_document_id)
399: LOOP
400: Eng_Propagation_log_Util.Debug_Log(G_LOG_STMT, 'Inserting into eng_attachment_changes..');
401: SELECT ENG_ATTACHMENT_CHANGES_S.nextval
402: INTO l_change_document_id
403: FROM dual;
404:
405: INSERT INTO eng_attachment_changes (
401: SELECT ENG_ATTACHMENT_CHANGES_S.nextval
402: INTO l_change_document_id
403: FROM dual;
404:
405: INSERT INTO eng_attachment_changes (
406: CHANGE_ID
407: , REVISED_ITEM_SEQUENCE_ID
408: , ACTION_TYPE
409: , ATTACHMENT_ID
778: CURSOR c_attachment_changes IS
779: SELECT eac.action_type, eac.entity_name, eac.pk1_value, eac.pk2_value
780: , eac.pk3_value, eac.file_name, fad.status, eac.change_document_id
781: , decode(eac.action_type, 'ATTACH', eac.source_document_id,fad.document_id) document_id,eac.category_id
782: FROM eng_attachment_changes eac, fnd_attached_documents fad
783: WHERE eac.change_id = p_change_id
784: AND eac.revised_item_sequence_id = p_rev_item_sequence_id
785: AND eac.attachment_id = fad.attached_document_id(+)
786: AND (eac.entity_name = 'MTL_ITEM_REVISIONS'
797: AND ecpm.entity_action_status = 3);
798:
799: CURSOR c_check_item_level_changes IS
800: SELECT 1
801: FROM eng_attachment_changes eac
802: WHERE eac.change_id = p_change_id
803: AND eac.revised_item_sequence_id = p_rev_item_sequence_id
804: AND eac.entity_name = 'MTL_SYSTEM_ITEMS'
805: AND EXISTS(SELECT 1 FROM MTL_PARAMETERS MP