1 PACKAGE BODY AP_WEB_DB_VIOLATIONS_PKG AS
2 /* $Header: apwdbvib.pls 120.4 2006/03/16 01:40:51 skoukunt noship $ */
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'
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'
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 END AP_WEB_DB_VIOLATIONS_PKG;
[Home] [Help]