[Home] [Help]
109: -- remove the carriage return character i.e remove chr(13)
110: -- fix for bug 2104571
111: l_request_log := replace(p_request_log,fnd_global.local_chr(13)||fnd_global.local_chr(10),fnd_global.local_chr(10));
112:
113: SELECT wip_eam_work_requests_s.nextval
114: INTO l_work_request_id FROM DUAL;
115: x_request_id := l_work_request_id;
116: l_stmt_num := 60;
117: BEGIN
219: FND_MSG_PUB.ADD;
220: RAISE FND_API.G_EXC_ERROR;
221: END IF;
222:
223: INSERT INTO wip_eam_work_requests(
224: work_request_id,
225: work_request_number,
226: last_update_date,
227: last_updated_by,
814: l_old_attribute12,
815: l_old_attribute13,
816: l_old_attribute14,
817: l_old_attribute15
818: FROM wip_eam_work_requests
819: WHERE work_request_id = p_request_id;
820:
821: l_stmt_num := 45;
822:
948: END IF;
949: END IF;
950: END IF;
951:
952: UPDATE wip_eam_work_requests SET
953: last_update_date = sysdate,
954: last_updated_by = NVL(p_user_id,FND_GLOBAL.user_id),
955: last_update_login = NVL(p_user_id,FND_GLOBAL.login_id),
956: work_request_priority_id = l_new_priority_id,
1615: END IF;
1616:
1617: IF (isdifferent(l_new_attribute1,l_old_attribute1) = 1) THEN
1618: l_dffprompt := NULL;
1619: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE1',l_new_attribute_category);
1620: IF (l_dffprompt IS NOT NULL) THEN
1621: l_dff_notes := get_dff_notes(l_new_attribute1,l_old_attribute1,l_dffprompt,l_null);
1622: SELECT wip_eam_work_req_notes_s.nextval
1623: INTO l_work_request_note_id
1650: END IF;
1651:
1652: IF (isdifferent(l_new_attribute2,l_old_attribute2) = 1) THEN
1653: l_dffprompt := NULL;
1654: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE2',l_new_attribute_category);
1655: IF (l_dffprompt IS NOT NULL) THEN
1656: l_dff_notes := get_dff_notes(l_new_attribute2,l_old_attribute2,l_dffprompt,l_null);
1657: SELECT wip_eam_work_req_notes_s.nextval
1658: INTO l_work_request_note_id
1685: END IF;
1686:
1687: IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1688: l_dffprompt := NULL;
1689: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1690: IF (l_dffprompt IS NOT NULL) THEN
1691: l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1692: SELECT wip_eam_work_req_notes_s.nextval
1693: INTO l_work_request_note_id
1720: END IF;
1721:
1722: IF (isdifferent(l_new_attribute3,l_old_attribute3) = 1) THEN
1723: l_dffprompt := NULL;
1724: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE3',l_new_attribute_category);
1725: IF (l_dffprompt IS NOT NULL) THEN
1726: l_dff_notes := get_dff_notes(l_new_attribute3,l_old_attribute3,l_dffprompt,l_null);
1727: SELECT wip_eam_work_req_notes_s.nextval
1728: INTO l_work_request_note_id
1755: END IF;
1756:
1757: IF (isdifferent(l_new_attribute4,l_old_attribute4) = 1) THEN
1758: l_dffprompt := NULL;
1759: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE4',l_new_attribute_category);
1760: IF (l_dffprompt IS NOT NULL) THEN
1761: l_dff_notes := get_dff_notes(l_new_attribute4,l_old_attribute4,l_dffprompt,l_null);
1762: SELECT wip_eam_work_req_notes_s.nextval
1763: INTO l_work_request_note_id
1790: END IF;
1791:
1792: IF (isdifferent(l_new_attribute5,l_old_attribute5) = 1) THEN
1793: l_dffprompt := NULL;
1794: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE5',l_new_attribute_category);
1795: IF (l_dffprompt IS NOT NULL) THEN
1796: l_dff_notes := get_dff_notes(l_new_attribute5,l_old_attribute5,l_dffprompt,l_null);
1797: SELECT wip_eam_work_req_notes_s.nextval
1798: INTO l_work_request_note_id
1825: END IF;
1826:
1827: IF (isdifferent(l_new_attribute6,l_old_attribute6) = 1) THEN
1828: l_dffprompt := NULL;
1829: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE6',l_new_attribute_category);
1830: IF (l_dffprompt IS NOT NULL) THEN
1831: l_dff_notes := get_dff_notes(l_new_attribute6,l_old_attribute6,l_dffprompt,l_null);
1832: SELECT wip_eam_work_req_notes_s.nextval
1833: INTO l_work_request_note_id
1860: END IF;
1861:
1862: IF (isdifferent(l_new_attribute7,l_old_attribute7) = 1) THEN
1863: l_dffprompt := NULL;
1864: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE7',l_new_attribute_category);
1865: IF (l_dffprompt IS NOT NULL) THEN
1866: l_dff_notes := get_dff_notes(l_new_attribute7,l_old_attribute7,l_dffprompt,l_null);
1867: SELECT wip_eam_work_req_notes_s.nextval
1868: INTO l_work_request_note_id
1895: END IF;
1896:
1897: IF (isdifferent(l_new_attribute8,l_old_attribute8) = 1) THEN
1898: l_dffprompt := NULL;
1899: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE8',l_new_attribute_category);
1900: IF (l_dffprompt IS NOT NULL) THEN
1901: l_dff_notes := get_dff_notes(l_new_attribute8,l_old_attribute8,l_dffprompt,l_null);
1902: SELECT wip_eam_work_req_notes_s.nextval
1903: INTO l_work_request_note_id
1930: END IF;
1931:
1932: IF (isdifferent(l_new_attribute9,l_old_attribute9) = 1) THEN
1933: l_dffprompt := NULL;
1934: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE9',l_new_attribute_category);
1935: IF (l_dffprompt IS NOT NULL) THEN
1936: l_dff_notes := get_dff_notes(l_new_attribute9,l_old_attribute9,l_dffprompt,l_null);
1937: SELECT wip_eam_work_req_notes_s.nextval
1938: INTO l_work_request_note_id
1965: END IF;
1966:
1967: IF (isdifferent(l_new_attribute10,l_old_attribute10) = 1) THEN
1968: l_dffprompt := NULL;
1969: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE10',l_new_attribute_category);
1970: IF (l_dffprompt IS NOT NULL) THEN
1971: l_dff_notes := get_dff_notes(l_new_attribute10,l_old_attribute10,l_dffprompt,l_null);
1972: SELECT wip_eam_work_req_notes_s.nextval
1973: INTO l_work_request_note_id
2000: END IF;
2001:
2002: IF (isdifferent(l_new_attribute11,l_old_attribute11) = 1) THEN
2003: l_dffprompt := NULL;
2004: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE11',l_new_attribute_category);
2005: IF (l_dffprompt IS NOT NULL) THEN
2006: l_dff_notes := get_dff_notes(l_new_attribute11,l_old_attribute11,l_dffprompt,l_null);
2007: SELECT wip_eam_work_req_notes_s.nextval
2008: INTO l_work_request_note_id
2035: END IF;
2036:
2037: IF (isdifferent(l_new_attribute12,l_old_attribute12) = 1) THEN
2038: l_dffprompt := NULL;
2039: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE12',l_new_attribute_category);
2040: IF (l_dffprompt IS NOT NULL) THEN
2041: l_dff_notes := get_dff_notes(l_new_attribute12,l_old_attribute12,l_dffprompt,l_null);
2042: SELECT wip_eam_work_req_notes_s.nextval
2043: INTO l_work_request_note_id
2070: END IF;
2071:
2072: IF (isdifferent(l_new_attribute13,l_old_attribute13) = 1) THEN
2073: l_dffprompt := NULL;
2074: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE13',l_new_attribute_category);
2075: IF (l_dffprompt IS NOT NULL) THEN
2076: l_dff_notes := get_dff_notes(l_new_attribute13,l_old_attribute13,l_dffprompt,l_null);
2077: SELECT wip_eam_work_req_notes_s.nextval
2078: INTO l_work_request_note_id
2105: END IF;
2106:
2107: IF (isdifferent(l_new_attribute14,l_old_attribute14) = 1) THEN
2108: l_dffprompt := NULL;
2109: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE14',l_new_attribute_category);
2110: IF (l_dffprompt IS NOT NULL) THEN
2111: l_dff_notes := get_dff_notes(l_new_attribute14,l_old_attribute14,l_dffprompt,l_null);
2112: SELECT wip_eam_work_req_notes_s.nextval
2113: INTO l_work_request_note_id
2140: END IF;
2141:
2142: IF (isdifferent(l_new_attribute15,l_old_attribute15) = 1) THEN
2143: l_dffprompt := NULL;
2144: l_dffprompt := dff_prompt_name('WIP','WIP_EAM_WORK_REQUESTS','ATTRIBUTE15',l_new_attribute_category);
2145: IF (l_dffprompt IS NOT NULL) THEN
2146: l_dff_notes := get_dff_notes(l_new_attribute15,l_old_attribute15,l_dffprompt,l_null);
2147: SELECT wip_eam_work_req_notes_s.nextval
2148: INTO l_work_request_note_id
2180: -- check if description field is null for this work request id
2181: BEGIN
2182: SELECT count(*)
2183: INTO l_counter
2184: FROM wip_eam_work_requests
2185: WHERE work_request_id = p_request_id And Description Is Null;
2186: EXCEPTION
2187: WHEN OTHERS THEN
2188: l_counter := 0;
2188: l_counter := 0;
2189: END;
2190: IF l_counter <> 0 then
2191: l_stmt_num := 300;
2192: UPDATE wip_eam_work_requests
2193: SET description = p_request_log
2194: WHERE work_request_id = p_request_id;
2195: END IF;
2196: -- end check
2234: -- get the old notification details
2235: begin
2236: select decode(wf_item_type,null,'EAMWRAP',wf_item_type),decode(wf_item_key,null,work_request_id,wf_item_key)
2237: into l_old_wf_item_type,l_old_wf_item_key
2238: from wip_eam_work_requests
2239: where work_request_id = p_request_id;
2240:
2241: exception
2242: when others then
2280:
2281: -- if status is Addn Info OR Awaiting Work Order then change status back to Open
2282: if (l_new_status_id = 3 Or l_new_status_id = 2) then
2283: --set Work Request status back to Open
2284: update wip_eam_work_requests
2285: set work_request_status_id = 1
2286: where work_request_id = p_request_id;
2287: end if;
2288:
2812: --validate descriptive flex fields
2813: if (validate_desc_flex_field
2814: (
2815: p_app_short_name => 'WIP'
2816: ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
2817: ,p_ATTRIBUTE_CATEGORY => p_attribute_category
2818: ,p_ATTRIBUTE1 => p_attribute1
2819: ,p_ATTRIBUTE2 => p_attribute2
2820: ,p_ATTRIBUTE3 => p_attribute3
2904: -- check if the request actually exists
2905: BEGIN
2906: 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
2907: 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
2908: FROM wip_eam_work_requests
2909: WHERE work_request_id = p_request_id;
2910:
2911: EXCEPTION
2912: WHEN NO_DATA_FOUND THEN
3163: --need to add code to validate flexfield
3164: if (validate_desc_flex_field
3165: (
3166: p_app_short_name => 'WIP'
3167: ,p_desc_flex_name => 'WIP_EAM_WORK_REQUESTS'
3168: ,p_attribute_category => p_attribute_category
3169: ,p_attribute1 => p_attribute1
3170: ,p_attribute2 => p_attribute2
3171: ,p_attribute3 => p_attribute3