[Home] [Help]
108: -- remove the carriage return character i.e remove chr(13)
109: -- fix for bug 2104571
110: l_request_log := replace(p_request_log,fnd_global.local_chr(13)||fnd_global.local_chr(10),fnd_global.local_chr(10));
111:
112: SELECT wip_eam_work_requests_s.nextval
113: INTO l_work_request_id FROM DUAL;
114: x_request_id := l_work_request_id;
115: l_stmt_num := 60;
116: BEGIN
218: FND_MSG_PUB.ADD;
219: RAISE FND_API.G_EXC_ERROR;
220: END IF;
221:
222: INSERT INTO wip_eam_work_requests(
223: work_request_id,
224: work_request_number,
225: last_update_date,
226: last_updated_by,
796: l_old_attribute12,
797: l_old_attribute13,
798: l_old_attribute14,
799: l_old_attribute15
800: FROM wip_eam_work_requests
801: WHERE work_request_id = p_request_id;
802:
803: l_stmt_num := 45;
804:
919: END IF;
920: END IF;
921: END IF;
922:
923: UPDATE wip_eam_work_requests SET
924: last_update_date = sysdate,
925: last_updated_by = FND_GLOBAL.user_id,
926: last_update_login = FND_GLOBAL.login_id,
927: work_request_priority_id = l_new_priority_id,
971: -- get the old notification details
972: begin
973: select decode(wf_item_type,null,'EAMWRAP',wf_item_type),decode(wf_item_key,null,work_request_id,wf_item_key)
974: into l_old_wf_item_type,l_old_wf_item_key
975: from wip_eam_work_requests
976: where work_request_id = p_request_id;
977:
978: exception
979: when others then
1016:
1017: -- if status is Addn Info OR Awaiting Work Order then change status back to Open
1018: if (l_new_status_id = 3 Or l_new_status_id = 2) then
1019: --set Work Request status back to Open
1020: update wip_eam_work_requests
1021: set work_request_status_id = 1
1022: where work_request_id = p_request_id;
1023: end if;
1024:
1636: END IF;
1637:
1638: IF (isdifferent(l_new_attribute1,l_old_attribute1) = 1) THEN
1639: l_dffprompt := NULL;
1640: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE1',l_new_attribute_category);
1641: IF (l_dffprompt IS NOT NULL) THEN
1642: l_dff_notes := get_dff_notes(l_new_attribute1,l_old_attribute1,l_dffprompt,l_null);
1643: SELECT wip_eam_work_req_notes_s.nextval
1644: INTO l_work_request_note_id
1671: END IF;
1672:
1673: IF (isdifferent(l_new_attribute2,l_old_attribute2) = 1) THEN
1674: l_dffprompt := NULL;
1675: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE2',l_new_attribute_category);
1676: IF (l_dffprompt IS NOT NULL) THEN
1677: l_dff_notes := get_dff_notes(l_new_attribute2,l_old_attribute2,l_dffprompt,l_null);
1678: SELECT wip_eam_work_req_notes_s.nextval
1679: INTO l_work_request_note_id
1706: END IF;
1707:
1708: IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1709: l_dffprompt := NULL;
1710: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1711: IF (l_dffprompt IS NOT NULL) THEN
1712: l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1713: SELECT wip_eam_work_req_notes_s.nextval
1714: INTO l_work_request_note_id
1741: END IF;
1742:
1743: IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1744: l_dffprompt := NULL;
1745: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1746: IF (l_dffprompt IS NOT NULL) THEN
1747: l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1748: SELECT wip_eam_work_req_notes_s.nextval
1749: INTO l_work_request_note_id
1776: END IF;
1777:
1778: IF (isdifferent(l_new_attribute4,l_old_attribute4) = 1) THEN
1779: l_dffprompt := NULL;
1780: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE4',l_new_attribute_category);
1781: IF (l_dffprompt IS NOT NULL) THEN
1782: l_dff_notes := get_dff_notes(l_new_attribute4,l_old_attribute4,l_dffprompt,l_null);
1783: SELECT wip_eam_work_req_notes_s.nextval
1784: INTO l_work_request_note_id
1811: END IF;
1812:
1813: IF (isdifferent(l_new_attribute5,l_old_attribute5) = 1) THEN
1814: l_dffprompt := NULL;
1815: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE5',l_new_attribute_category);
1816: IF (l_dffprompt IS NOT NULL) THEN
1817: l_dff_notes := get_dff_notes(l_new_attribute5,l_old_attribute5,l_dffprompt,l_null);
1818: SELECT wip_eam_work_req_notes_s.nextval
1819: INTO l_work_request_note_id
1846: END IF;
1847:
1848: IF (isdifferent(l_new_attribute6,l_old_attribute6) = 1) THEN
1849: l_dffprompt := NULL;
1850: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE6',l_new_attribute_category);
1851: IF (l_dffprompt IS NOT NULL) THEN
1852: l_dff_notes := get_dff_notes(l_new_attribute6,l_old_attribute6,l_dffprompt,l_null);
1853: SELECT wip_eam_work_req_notes_s.nextval
1854: INTO l_work_request_note_id
1881: END IF;
1882:
1883: IF (isdifferent(l_new_attribute7,l_old_attribute7) = 1) THEN
1884: l_dffprompt := NULL;
1885: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE7',l_new_attribute_category);
1886: IF (l_dffprompt IS NOT NULL) THEN
1887: l_dff_notes := get_dff_notes(l_new_attribute7,l_old_attribute7,l_dffprompt,l_null);
1888: SELECT wip_eam_work_req_notes_s.nextval
1889: INTO l_work_request_note_id
1916: END IF;
1917:
1918: IF (isdifferent(l_new_attribute8,l_old_attribute8) = 1) THEN
1919: l_dffprompt := NULL;
1920: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE8',l_new_attribute_category);
1921: IF (l_dffprompt IS NOT NULL) THEN
1922: l_dff_notes := get_dff_notes(l_new_attribute8,l_old_attribute8,l_dffprompt,l_null);
1923: SELECT wip_eam_work_req_notes_s.nextval
1924: INTO l_work_request_note_id
1951: END IF;
1952:
1953: IF (isdifferent(l_new_attribute9,l_old_attribute9) = 1) THEN
1954: l_dffprompt := NULL;
1955: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE9',l_new_attribute_category);
1956: IF (l_dffprompt IS NOT NULL) THEN
1957: l_dff_notes := get_dff_notes(l_new_attribute9,l_old_attribute9,l_dffprompt,l_null);
1958: SELECT wip_eam_work_req_notes_s.nextval
1959: INTO l_work_request_note_id
1986: END IF;
1987:
1988: IF (isdifferent(l_new_attribute10,l_old_attribute10) = 1) THEN
1989: l_dffprompt := NULL;
1990: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE10',l_new_attribute_category);
1991: IF (l_dffprompt IS NOT NULL) THEN
1992: l_dff_notes := get_dff_notes(l_new_attribute10,l_old_attribute10,l_dffprompt,l_null);
1993: SELECT wip_eam_work_req_notes_s.nextval
1994: INTO l_work_request_note_id
2021: END IF;
2022:
2023: IF (isdifferent(l_new_attribute11,l_old_attribute11) = 1) THEN
2024: l_dffprompt := NULL;
2025: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE11',l_new_attribute_category);
2026: IF (l_dffprompt IS NOT NULL) THEN
2027: l_dff_notes := get_dff_notes(l_new_attribute11,l_old_attribute11,l_dffprompt,l_null);
2028: SELECT wip_eam_work_req_notes_s.nextval
2029: INTO l_work_request_note_id
2056: END IF;
2057:
2058: IF (isdifferent(l_new_attribute12,l_old_attribute12) = 1) THEN
2059: l_dffprompt := NULL;
2060: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE12',l_new_attribute_category);
2061: IF (l_dffprompt IS NOT NULL) THEN
2062: l_dff_notes := get_dff_notes(l_new_attribute12,l_old_attribute12,l_dffprompt,l_null);
2063: SELECT wip_eam_work_req_notes_s.nextval
2064: INTO l_work_request_note_id
2091: END IF;
2092:
2093: IF (isdifferent(l_new_attribute13,l_old_attribute13) = 1) THEN
2094: l_dffprompt := NULL;
2095: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE13',l_new_attribute_category);
2096: IF (l_dffprompt IS NOT NULL) THEN
2097: l_dff_notes := get_dff_notes(l_new_attribute13,l_old_attribute13,l_dffprompt,l_null);
2098: SELECT wip_eam_work_req_notes_s.nextval
2099: INTO l_work_request_note_id
2126: END IF;
2127:
2128: IF (isdifferent(l_new_attribute14,l_old_attribute14) = 1) THEN
2129: l_dffprompt := NULL;
2130: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE14',l_new_attribute_category);
2131: IF (l_dffprompt IS NOT NULL) THEN
2132: l_dff_notes := get_dff_notes(l_new_attribute14,l_old_attribute14,l_dffprompt,l_null);
2133: SELECT wip_eam_work_req_notes_s.nextval
2134: INTO l_work_request_note_id
2161: END IF;
2162:
2163: IF (isdifferent(l_new_attribute15,l_old_attribute15) = 1) THEN
2164: l_dffprompt := NULL;
2165: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE15',l_new_attribute_category);
2166: IF (l_dffprompt IS NOT NULL) THEN
2167: l_dff_notes := get_dff_notes(l_new_attribute15,l_old_attribute15,l_dffprompt,l_null);
2168: SELECT wip_eam_work_req_notes_s.nextval
2169: INTO l_work_request_note_id
2201: -- check if description field is null for this work request id
2202: BEGIN
2203: SELECT count(*)
2204: INTO l_counter
2205: FROM wip_eam_work_requests
2206: WHERE work_request_id = p_request_id And Description Is Null;
2207: EXCEPTION
2208: WHEN OTHERS THEN
2209: l_counter := 0;
2209: l_counter := 0;
2210: END;
2211: IF l_counter <> 0 then
2212: l_stmt_num := 300;
2213: UPDATE wip_eam_work_requests
2214: SET description = p_request_log
2215: WHERE work_request_id = p_request_id;
2216: END IF;
2217: -- end check
2765: --validate descriptive flex fields
2766: if (validate_desc_flex_field
2767: (
2768: p_app_short_name => 'WIP'
2769: ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
2770: ,p_ATTRIBUTE_CATEGORY => p_attribute_category
2771: ,p_ATTRIBUTE1 => p_attribute1
2772: ,p_ATTRIBUTE2 => p_attribute2
2773: ,p_ATTRIBUTE3 => p_attribute3
2848: -- check if the request actually exists
2849: BEGIN
2850: SELECT organization_id, asset_group, asset_number,work_request_priority_id, work_request_status_id, expected_resolution_date, work_request_owning_dept, work_request_type_id, created_for
2851: INTO l_org_id, l_asset_group_id, l_asset_number, l_priority_id, l_status_id, l_request_by_date, l_owning_dept_id, l_work_request_type_id, l_created_for
2852: FROM wip_eam_work_requests
2853: WHERE work_request_id = p_request_id;
2854:
2855: EXCEPTION
2856: WHEN NO_DATA_FOUND THEN
3102: --need to add code to validate flexfield
3103: if (validate_desc_flex_field
3104: (
3105: p_app_short_name => 'WIP'
3106: ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
3107: ,p_attribute_category => p_attribute_category
3108: ,p_attribute1 => p_attribute1
3109: ,p_attribute2 => p_attribute2
3110: ,p_attribute3 => p_attribute3