1 PACKAGE pay_kr_wg_report_pkg AUTHID CURRENT_USER AS
2 /* $Header: pykrwgrp.pkh 115.4 2003/05/30 07:26:49 nnaresh noship $ */
3
4 ---------------------------------------------------------------------------------
5 /* FUNCTION processing_type */
6 -- ------------------------
7 -- This function returns the Processing Type of a court order.
8 --
9 ---------------------------------------------------------------------------------
10 FUNCTION processing_type (p_element_entry_id IN NUMBER)
11 RETURN VARCHAR2;
12 --====================================================================================
13
14
15
16 -------------------------------------------------------------------------------
17 /* FUNCTION Obligation_exists */
18 -- --------------------------
19 -- This function returns TRUE if Obligation Release exists for a court ordery.
20 --
21 -------------------------------------------------------------------------------
22 FUNCTION Obligation_exists (p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
23 ,p_effective_date IN DATE DEFAULT NULL)
24 RETURN BOOLEAN;
25 --====================================================================================
26
27
28
29 ------------------------------------------------------------------------------------
30 /* FUNCTION get_element_entry_id */
31 -- -----------------------------
32 -- This function returns the element_entry_id for a attachment sequence number.
33 -- Bug 2856663 : Added parameter p_assignment_id
34 ------------------------------------------------------------------------------------
35 FUNCTION get_element_entry_id (p_assignment_id IN per_assignments_f.assignment_id%type
36 ,p_attachment_seq_no IN VARCHAR2)
37 RETURN NUMBER;
38 --====================================================================================
39
40
41
42 ------------------------------------------------------------------------------------
43 /* FUNCTION get_attach_seq_no */
44 -- ---------------------------
45 -- This function returns the attachment sequence number for a element_entry_id.
46 --
47 ------------------------------------------------------------------------------------
48 FUNCTION get_attach_seq_no(p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE)
49 RETURN VARCHAR2;
50 --====================================================================================
51
52
53
54 ---------------------------------------------------------------------------------
55 /* FUNCTION prev_case_attachment_seq_no */
56 -- ------------------------------------
57 -- This function returns the attachment sequence number of the previous case for
58 -- a Actual Attachment court order.
59 --
60 ---------------------------------------------------------------------------------
61 FUNCTION prev_case_attachment_seq_no (p_element_entry_id IN NUMBER)
62 RETURN VARCHAR2;
63 --====================================================================================
64
65
66
67 --------------------------------------------------------------------------------------
68 /* Function wage_garnishment_exists */
69 -- --------------------------------
70 -- returns 'TRUE' if there are any active 'Court Orders' against this assignment
71 -- returns 'FALSE' if there are no active 'Court Orders' against this assignment
72 --
73 --------------------------------------------------------------------------------------
74 FUNCTION wage_garnishment_exists (p_assignment_id IN per_assignments_f.assignment_id%TYPE
75 ,p_effective_date IN date DEFAULT NULL)
76 RETURN BOOLEAN;
77 --====================================================================================
78
79
80
81 --------------------------------------------------------------------------------------
82 /* Function paid_amount_this_run (for single creditor) */
83 -- ----------------------------------------------------
84 -- returns the amount that was deducted from payroll in this payroll run for a creditor
85 --
86 --------------------------------------------------------------------------------------
87 FUNCTION paid_amount_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
88 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE )
89 RETURN NUMBER;
90 --====================================================================================
91
92
93
94
95 --------------------------------------------------------------------------------------
96 /* Function paid_amount_this_run (for all creditors) */
97 -- ----------------------------------------------------
98 -- returns the total amount that was deducted from the payroll for this assignment
99 -- in this payroll run
100 -- total_paid_amount = Balance WG_DEDUCTIONS_ASG_RUN
101 --
102 --------------------------------------------------------------------------------------
103 FUNCTION paid_amount_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
104 RETURN NUMBER;
105 --====================================================================================
106
107
108
109
110 --------------------------------------------------------------------------------------
111 /* Function attachment_total_base */
112 -- ------------------------------
113 -- This function will return Attachment Total Base for a court order.
114 --
115 --------------------------------------------------------------------------------------
116 FUNCTION attachment_total_base (p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
117 ,p_effective_date IN DATE DEFAULT NULL)
118 RETURN NUMBER;
119 --====================================================================================
120
121
122
123
124 --------------------------------------------------------------------------------------
125 /* Function real_attachment_total */
126 -- ------------------------------
127 -- This function will return Real Attachment Total for a case number.
128 --
129 --------------------------------------------------------------------------------------
130 FUNCTION real_attachment_total ( p_assignment_id IN per_assignments_f.assignment_id%TYPE
131 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
132 ,p_effective_date IN DATE DEFAULT NULL)
133 RETURN NUMBER;
134 --====================================================================================
135
136
137
138
139 --------------------------------------------------------------------------------------
140 /* Function unpaid_debt */
141 -- --------------------
142 -- This function will return unpaid debt amount for a court order.
143 -- unpaid debt = real_attachment_total - paid_amount
144 --
145 --------------------------------------------------------------------------------------
146 FUNCTION unpaid_debt(p_assignment_id IN per_assignments_f.assignment_id%TYPE
147 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
148 ,p_effective_date IN DATE DEFAULT NULL)
149 RETURN NUMBER;
150 --====================================================================================
151
152
153
154
155 --------------------------------------------------------------------------------------
156 /* Function paid_amount (for single creditor) */
157 -- ------------------------------------------
158 -- This function will return total paid amount for a creditor.
159 --
160 -- For Provisional attachment :
161 -- IF obligation_exists THEN
162 -- paid_amount = 0
163 -- ELSE
164 -- paid_amount = Balance WG_DEDUCTIONS_ASG_WG_ITD
165 --
166 --
167 -- For Actual Attachments :
168 -- paid_amount = paid_amount for this court order
169 -- + paid_amount for previous case court order
170 --
171 --------------------------------------------------------------------------------------
172 FUNCTION paid_amount (p_assignment_id IN per_assignments_f.assignment_id%TYPE
173 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
174 ,p_effective_date IN DATE DEFAULT NULL)
175 RETURN NUMBER;
176 --====================================================================================
177
178
179
180
181 --------------------------------------------------------------------------------------
182 /* Function paid_interest_this_run (for single creditor) */
183 -- -----------------------------------------------------
184 -- This function returns the Interest Amount this run for a creditor.
185 --
186 --------------------------------------------------------------------------------------
187 FUNCTION paid_interest_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
188 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE)
189 RETURN NUMBER;
190 --====================================================================================
191
192
193
194
195 --------------------------------------------------------------------------------------
196 /* Function paid_interest_this_run (for all creditors) */
197 -- ---------------------------------------------------
198 -- This function returns the total Interest Amount this run for all creditors.
199 -- paid_interest_this_run = WG_PAID_INTEREST_ASG_RUN
200 --
201 --------------------------------------------------------------------------------------
202 FUNCTION paid_interest_this_run (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
203 RETURN NUMBER;
204 --====================================================================================
205
206
207
208
209 --------------------------------------------------------------------------------------
210 /* Function paid_interest (for single creditor) */
211 -- --------------------------------------------
212 -- This function returns the total Interets Paid for this creditor till date.
213 -- paid_interest = WG_PAID_INTEREST_ASG_WG_ITD
214 --
215 --------------------------------------------------------------------------------------
216 FUNCTION paid_interest (p_assignment_id IN per_assignments_f.assignment_id%TYPE
217 ,p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
218 ,p_effective_date IN DATE DEFAULT NULL)
219 RETURN NUMBER;
220 --====================================================================================
221
222 END pay_kr_wg_report_pkg;