1 PACKAGE BODY QOT_UTILITY_PVT AS
2 /* $Header: qotvutlb.pls 120.0.12010000.2 2009/08/14 12:23:38 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : QOT_UTILITY_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 FUNCTION GET_CONTRACT_TERMS_ACCESS
11 (
12 P_QUOTE_HEADER_ID IN NUMBER,
13 P_USER_ID IN NUMBER
14 ) RETURN VARCHAR2
15 AS
16 CURSOR C_user_resource_id (pc_user_id NUMBER) IS
17 SELECT resource_id
18 FROM jtf_rs_resource_extns
19 WHERE user_id = pc_user_id
20 AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);
21
22 CURSOR C_quote_header (pc_quote_header_id IN NUMBER)
23 IS
24 SELECT max_version_flag, price_request_id, quote_status_id, quote_number
25 FROM ASO_QUOTE_HEADERS_ALL
26 WHERE quote_header_id = pc_quote_header_id;
27
28 CURSOR C_quote_status (pc_status_id IN NUMBER)
29 IS
30 SELECT update_allowed_flag
31 FROM ASO_QUOTE_STATUSES_B
32 WHERE quote_status_id = pc_status_id;
33
34 l_max_ver_flag VARCHAR2(1);
35 l_upd_allowed_flag VARCHAR2(1);
36 l_qot_sec_enabled VARCHAR2(1);
37 l_qot_access VARCHAR2(10);
38 l_status_override VARCHAR2(1);
39 l_resource_id NUMBER;
40 l_price_request_id NUMBER;
41 l_status_id NUMBER;
42 l_qot_num NUMBER;
43
44 l_debug VARCHAR2(1);
45
46 BEGIN
47 l_debug := ASO_QUOTE_UTIL_PVT.is_debug_enabled;
48 -- IF l_debug = 'Y' THEN
49 -- ASO_QUOTE_UTIL_PVT.Enable_Debug_Pvt;
50 -- ASO_QUOTE_UTIL_PVT.Debug('QOT_CONTRACTS_ACCESS.Get_Contracts_Acess Begins');
51 -- END IF;
52
53 l_qot_sec_enabled := nvl(fnd_profile.value('ASO_ENABLE_SECURITY_CHECK'),'N');
54
55 OPEN C_user_resource_id(p_user_id);
56 FETCH C_user_resource_id INTO l_resource_id;
57 CLOSE C_user_resource_id;
58
59 --If security is ON and resource_id is NULL, return 'N'
60 IF(l_qot_sec_enabled = 'Y' AND l_resource_id IS NULL) THEN
61 --dbms_output.put_line('Security is ON but resource Id is NULL..so NO Access');
62 RETURN 'N';
63 END IF;
64
65 OPEN C_quote_header(p_quote_header_id);
66 FETCH C_quote_header INTO l_max_ver_flag, l_price_request_id, l_status_id, l_qot_num;
67 CLOSE C_quote_header;
68
69 --If quote is NOT highest version or is submitted for batch pricing then return 'N'
70 IF(l_max_ver_flag <> 'Y' OR l_price_request_id IS NOT NULL) THEN
71 --dbms_output.put_line('Not highest version or submitted for batch pricing..so NO Access');
72 RETURN 'N';
73 END IF;
74
75 OPEN C_quote_status(l_status_id);
76 FETCH C_quote_status INTO l_upd_allowed_flag;
77 CLOSE C_quote_status;
78
79 l_status_override := nvl(fnd_profile.value('ASO_STATUS_OVERRIDE'),'N');
80
81 --dbms_output.put_line('ASO_STATUS_OVERRIDE ' || l_status_override);
82
83 IF(l_upd_allowed_flag = 'N') THEN
84 --dbms_output.put_line('Quote is in Read-only Status');
85 IF (l_qot_sec_enabled = 'Y') THEN
86 --dbms_output.put_line('Security Enabled..');
87 l_qot_access := ASO_SECURITY_INT.Get_Quote_Access(l_resource_id, l_qot_num);
88 --dbms_output.put_line('Quote Access ' || l_qot_access);
89 IF (l_status_override <> 'Y' AND (l_qot_access = 'UPDATE' OR l_qot_access = 'READ')) THEN
90 --dbms_output.put_line('Quote Access is Update or Read and STatus Override is No..View Access');
91 RETURN 'V';
92 END IF;
93 IF (l_status_override <> 'Y' AND l_qot_access = 'NONE') THEN
94 --dbms_output.put_line('Quote Access is NONE..so return N');
95 RETURN 'N';
96 END IF;
97 ELSE
98 --dbms_output.put_line('Quote is read-only status but security is off..so return V');
99 -- Added for bug 8717880
100 if l_status_override ='Y' then
101 RETURN 'U';
102 else
103 RETURN 'V';
104 END IF;
105 END IF;
106 ELSE
107 IF (l_upd_allowed_flag = 'Y') THEN
108 --dbms_output.put_line('Quote is in Update Status');
109 IF (l_qot_sec_enabled = 'Y') THEN
110 l_qot_access := ASO_SECURITY_INT.Get_Quote_Access(l_resource_id, l_qot_num);
111 --dbms_output.put_line('Quote Access ' || l_qot_access);
112 IF (l_status_override <> 'Y' AND l_qot_access = 'UPDATE') THEN
113 --dbms_output.put_line('Quote Access is Update and STatus Override is No..Update Access');
114 RETURN 'U';
115 END IF;
116 IF (l_qot_access = 'READ') THEN
117 --dbms_output.put_line('Quote Access is Read..View Access');
118 RETURN 'V'; --??
119 END IF;
120 IF (l_qot_access = 'NONE') THEN
121 --dbms_output.put_line('Quote Access is NONE..so return N');
122 RETURN 'N';
123 END IF;
124 ELSE
125 --dbms_output.put_line('Quote is in Update status but security is off..so return U');
126 RETURN 'U';
127 END IF;
128 END IF;
129 END IF;
130 END GET_CONTRACT_TERMS_ACCESS;
131
132 END QOT_UTILITY_PVT;
[Home] [Help]