[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_VIOLATIONS_PKG
Source
1 PACKAGE BODY AP_WEB_DB_VIOLATIONS_PKG AS
2 /* $Header: apwdbvib.pls 120.4.12010000.3 2010/06/22 10:51:19 rveliche ship $ */
3
4 --------------------------------------------------------------------------------
5 PROCEDURE deleteViolationEntry(
6 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE) IS
7 -------------------------------------------------------------------------------
8 l_debug_info VARCHAR2(200);
9
10 BEGIN
11 DELETE
12 FROM ap_pol_violations
13 WHERE report_header_id = p_report_header_id;
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 AP_WEB_DB_UTIL_PKG.RaiseException('deleteViolationEntry');
18 APP_EXCEPTION.RAISE_EXCEPTION;
19 END deleteViolationEntry;
20
21 -------------------------------------------------------------------------------
22 FUNCTION updateViolationsHeaderId(
23 p_ReportHeaderID IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE,
24 p_newReportHeaderID IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE)
25 RETURN BOOLEAN IS
26 --------------------------------------------------------------------------------
27 BEGIN
28 -- set the report_header_id for the line
29
30 UPDATE ap_pol_violations
31 SET report_header_id = p_newReportHeaderID
32 WHERE report_header_id = p_ReportHeaderID
33 AND distribution_line_number IN(
34 SELECT distribution_line_number
35 FROM ap_expense_report_lines
36 WHERE report_header_id = p_newReportHeaderID);
37
38 return TRUE;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 return FALSE;
42 WHEN OTHERS THEN
43 AP_WEB_DB_UTIL_PKG.RaiseException('updateViolationsHeaderId');
44 APP_EXCEPTION.RAISE_EXCEPTION;
45 return FALSE;
46 END updateViolationsHeaderId;
47
48 --------------------------------------------------------------------------------
49 PROCEDURE SetVioPolicyShortpaidReportID(
50 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
51 p_new_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE)IS
52 -------------------------------------------------------------------------------
53 l_debug_info VARCHAR2(200);
54
55 BEGIN
56
57 UPDATE ap_pol_violations
58 SET report_header_id = p_new_report_header_id
59 WHERE distribution_line_number IN
60 (select distribution_line_number
61 from ap_expense_report_lines
62 where report_header_id = p_new_report_header_id
63 and ( nvl(policy_shortpay_flag,'N') = 'Y'
64 or
65 (itemization_parent_id in
66 (select report_line_id
67 from ap_expense_report_lines
68 where report_header_id = p_new_report_header_id
69 and nvl(policy_shortpay_flag,'N') = 'Y'
70 and itemization_parent_id = -1
71 )
72 )
73 ))
74 AND report_header_id = p_orig_expense_report_id;
75
76
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 NULL;
80 WHEN OTHERS THEN
81 AP_WEB_DB_UTIL_PKG.RaiseException('SetVioPolicyShortpaidReportID');
82 APP_EXCEPTION.RAISE_EXCEPTION;
83 END SetVioPolicyShortpaidReportID;
84 --------------------------------------------------------------------------------
85 PROCEDURE SetVioReceiptShortpaidReportID(
86 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
87 p_new_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE)IS
88 -------------------------------------------------------------------------------
89 l_debug_info VARCHAR2(200);
90
91 BEGIN
92 UPDATE ap_pol_violations
93 SET report_header_id = p_new_report_header_id
94 WHERE distribution_line_number IN
95 (select distribution_line_number
96 from ap_expense_report_lines
97 where report_header_id = p_new_report_header_id
98 and (((receipt_required_flag = 'Y' OR image_receipt_required_flag = 'Y')
99 and nvl(receipt_verified_flag,'N') = 'N'
100 and nvl(policy_shortpay_flag, 'N') = 'N'
101 )
102 or
103 (itemization_parent_id in
104 (select report_line_id
105 from ap_expense_report_lines
106 where report_header_id = p_new_report_header_id
107 and (receipt_required_flag = 'Y' OR image_receipt_required_flag = 'Y')
108 and nvl(receipt_verified_flag,'N') = 'N'
109 and nvl(policy_shortpay_flag, 'N') = 'N'
110 and itemization_parent_id = -1
111 )
112 )
113 ))
114 AND report_header_id = p_orig_expense_report_id;
115
116
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 NULL;
120 WHEN OTHERS THEN
121 AP_WEB_DB_UTIL_PKG.RaiseException('SetVioReceiptShortpaidReportID');
122 APP_EXCEPTION.RAISE_EXCEPTION;
123 END SetVioReceiptShortpaidReportID;
124 --------------------------------------------------------------------------------
125
126 PROCEDURE SetViolationBothpaidReportID(
127 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
128 p_new_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE)IS
129 -------------------------------------------------------------------------------
130 l_debug_info VARCHAR2(200);
131
132 BEGIN
133 UPDATE ap_pol_violations
134 SET report_header_id = p_new_report_header_id
135 WHERE distribution_line_number IN
136 (SELECT distribution_line_number
137 FROM ap_expense_report_lines
138 WHERE report_header_id = p_orig_expense_report_id
139 AND credit_card_trx_id IS NOT NULL)
140 AND report_header_id = p_orig_expense_report_id;
141
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145 NULL;
146 WHEN OTHERS THEN
147 AP_WEB_DB_UTIL_PKG.RaiseException('SetViolationBothpaidReportID');
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END SetViolationBothpaidReportID;
150 --------------------------------------------------------------------------------
151
152 PROCEDURE deleteDupViolationEntry(
153 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE) IS
154 --------------------------------------------------------------------------------
155
156 l_report_prefix VARCHAR2(10);
157 l_report_header_id VARCHAR2(30);
158
159 BEGIN
160
161 FND_PROFILE.GET('AP_WEB_REPNUM_PREFIX', l_report_prefix);
162 l_report_header_id := l_report_prefix || To_Char(p_report_header_id);
163
164 DELETE FROM ap_pol_violations_all
165 WHERE dup_report_header_id = l_report_header_id;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 AP_WEB_DB_UTIL_PKG.RaiseException('deleteDupViolationEntry');
170 APP_EXCEPTION.RAISE_EXCEPTION;
171
172 END deleteDupViolationEntry;
173 --------------------------------------------------------------------------------
174
175 END AP_WEB_DB_VIOLATIONS_PKG;