[Home] [Help]
PACKAGE BODY: APPS.UMX_REG_REQUESTS_PKG
Source
1 package body UMX_REG_REQUESTS_PKG as
2 /* $Header: UMXRGRQB.pls 115.5 2004/01/09 07:44:06 kching noship $ */
3 procedure INSERT_ROW (
4 X_REG_REQUEST_ID in out NOCOPY NUMBER,
5 X_REG_SERVICE_TYPE in VARCHAR2,
6 X_STATUS_CODE in VARCHAR2,
7 X_REQUESTED_BY_USER_ID in NUMBER DEFAULT NULL,
8 X_REQUESTED_FOR_USER_ID in NUMBER DEFAULT NULL,
9 X_REQUESTED_FOR_PARTY_ID in NUMBER DEFAULT NULL,
10 X_REQUESTED_USERNAME in VARCHAR2 DEFAULT NULL,
11 X_REQUESTED_START_DATE in DATE DEFAULT NULL,
12 X_REQUESTED_END_DATE in DATE DEFAULT NULL,
13 X_WF_ROLE_NAME in VARCHAR2 DEFAULT NULL,
14 X_REG_SERVICE_CODE in VARCHAR2 DEFAULT NULL,
15 X_AME_APPLICATION_ID in NUMBER DEFAULT NULL,
16 X_AME_TRANSACTION_TYPE_ID in VARCHAR2 DEFAULT NULL,
17 X_JUSTIFICATION in VARCHAR2 DEFAULT NULL) IS
18
19 cursor C is select ROWID from UMX_REG_REQUESTS
20 where REG_REQUEST_ID = X_REG_REQUEST_ID;
21
22 BEGIN
23
24 if (X_REG_REQUEST_ID is null) then
25 select UMX_REG_REQUESTS_S.nextval into X_REG_REQUEST_ID from dual;
26 end if;
27
28 -- do we need to raise a no data found exception??
29
30 insert into UMX_REG_REQUESTS (
31 REG_REQUEST_ID,
32 REG_SERVICE_TYPE,
33 STATUS_CODE,
34 CREATED_BY,
35 CREATION_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_DATE,
38 SECURITY_GROUP_ID,
39 REQUESTED_START_DATE,
40 REQUESTED_END_DATE,
41 REQUESTED_BY_USER_ID,
42 REQUESTED_FOR_USER_ID,
43 REQUESTED_FOR_PARTY_ID,
44 REQUESTED_USERNAME,
45 WF_ROLE_NAME,
46 REG_SERVICE_CODE,
47 AME_APPLICATION_ID,
48 AME_TRANSACTION_TYPE_ID,
49 JUSTIFICATION
50 ) values (
51 X_REG_REQUEST_ID,
52 x_reg_service_type,
53 x_status_code,
54 fnd_global.user_id,
55 sysdate,
56 fnd_global.user_id,
57 sysdate,
58 0,
59 x_requested_start_date,
60 x_requested_end_date,
61 fnd_global.user_id,
62 x_requested_for_user_id,
63 x_requested_for_party_id,
64 x_requested_username,
65 x_wf_role_name,
66 x_reg_service_code,
67 x_ame_application_id,
68 x_ame_transaction_type_id,
69 x_justification
70 ) returning REG_REQUEST_ID INTO x_reg_request_id;
71
72 open c;
73 if (c%notfound) then
74 close c;
75 raise no_data_found;
76 end if;
77 close c;
78
79 End INSERT_ROW;
80
81 procedure UPDATE_ROW (
82 X_REG_REQUEST_ID in NUMBER,
83 X_STATUS_CODE in VARCHAR2 DEFAULT NULL,
84 X_REQUESTED_BY_USER_ID in NUMBER DEFAULT NULL,
85 X_REQUESTED_FOR_USER_ID in NUMBER DEFAULT NULL,
86 X_REQUESTED_FOR_PARTY_ID in NUMBER DEFAULT NULL,
87 X_REQUESTED_USERNAME in VARCHAR2 DEFAULT NULL,
88 X_REQUESTED_START_DATE in DATE DEFAULT NULL,
89 X_REQUESTED_END_DATE in DATE DEFAULT NULL,
90 X_WF_ROLE_NAME in VARCHAR2 DEFAULT NULL,
91 X_REG_SERVICE_CODE in VARCHAR2 DEFAULT NULL,
92 X_AME_APPLICATION_ID in NUMBER DEFAULT NULL,
93 X_AME_TRANSACTION_TYPE_ID in VARCHAR2 DEFAULT NULL,
94 X_JUSTIFICATION in VARCHAR2 DEFAULT NULL) IS
95 BEGIN
96
97 UPDATE UMX_REG_REQUESTS SET
98 STATUS_CODE = decode (X_STATUS_CODE, NULL, STATUS_CODE, fnd_api.g_miss_char, NULL, X_STATUS_CODE),
99 LAST_UPDATED_BY = fnd_global.user_id,
100 LAST_UPDATE_DATE = sysdate,
101 REQUESTED_BY_USER_ID = decode (X_REQUESTED_BY_USER_ID, NULL, REQUESTED_BY_USER_ID, fnd_api.g_miss_num, NULL, X_REQUESTED_BY_USER_ID),
102 REQUESTED_FOR_USER_ID = decode (X_REQUESTED_FOR_USER_ID, NULL, REQUESTED_FOR_USER_ID, fnd_api.g_miss_num, NULL, X_REQUESTED_FOR_USER_ID),
103 REQUESTED_FOR_PARTY_ID = decode (X_REQUESTED_FOR_PARTY_ID, NULL, REQUESTED_FOR_PARTY_ID, fnd_api.g_miss_num, NULL, X_REQUESTED_FOR_PARTY_ID),
104 REQUESTED_USERNAME = decode (X_REQUESTED_USERNAME, NULL, REQUESTED_USERNAME, fnd_api.g_miss_char, NULL, X_REQUESTED_USERNAME),
105 REQUESTED_START_DATE = decode (X_REQUESTED_START_DATE, NULL, REQUESTED_START_DATE, fnd_api.g_miss_date, NULL, X_REQUESTED_START_DATE),
106 REQUESTED_END_DATE = decode (X_REQUESTED_END_DATE, NULL, REQUESTED_END_DATE, fnd_api.g_miss_date, NULL, X_REQUESTED_END_DATE),
107 WF_ROLE_NAME = decode (X_WF_ROLE_NAME, NULL, WF_ROLE_NAME, fnd_api.g_miss_char, NULL, X_WF_ROLE_NAME),
108 REG_SERVICE_CODE = decode (X_REG_SERVICE_CODE, NULL, REG_SERVICE_CODE, fnd_api.g_miss_char, NULL, X_REG_SERVICE_CODE),
109 AME_APPLICATION_ID = decode (X_AME_APPLICATION_ID, NULL, AME_APPLICATION_ID, fnd_api.g_miss_num, NULL, X_AME_APPLICATION_ID),
110 AME_TRANSACTION_TYPE_ID = decode (X_AME_TRANSACTION_TYPE_ID, NULL, AME_TRANSACTION_TYPE_ID, fnd_api.g_miss_char, NULL, X_AME_TRANSACTION_TYPE_ID),
111 JUSTIFICATION = decode (X_JUSTIFICATION, NULL, JUSTIFICATION, fnd_api.g_miss_char, NULL, X_JUSTIFICATION)
112 WHERE REG_REQUEST_ID = X_REG_REQUEST_ID;
113 if (sql%notfound) then
114 raise no_data_found;
115 end if;
116 END UPDATE_ROW;
117
118 procedure DELETE_ROW (X_REG_REQUEST_ID in NUMBER)IS
119 BEGIN
120
121 DELETE FROM UMX_REG_REQUESTS
122 WHERE REG_REQUEST_ID = X_REG_REQUEST_ID;
123 if (sql%notfound) then
124 raise no_data_found;
125 end if;
126
127 END DELETE_ROW;
128
129 end UMX_REG_REQUESTS_PKG;