[Home] [Help]
120: -- ------------------------------------------------------------------------
121: -- This procedure was added as assignment_actions are spilt across
122: -- various chunks when the values CHUNK_SIZE and the THREADS in
123: -- pay_action_parameters are more than 1 and there may be more than one
124: -- record in the pay_us_rpt_totals for an assignment and we need to
125: -- consider the sum of all the records for a given pay_mode and assignment_id
126: -- so see if there any -ve balances being reported, which would be reported
127: -- in the exception report. This procedure would be called in the report
128: -- PAYUSTIM.rdf after-param report trigger.
133: SELECT DISTINCT
134: attribute5
135: ,attribute12
136: ,value9
137: FROM pay_us_rpt_totals
138: WHERE tax_unit_id = p_payroll_action_id
139: AND attribute14 = '999'
140: AND attribute15 = 'NEGATIVE BALANCE'
141: AND attribute1 <> 'TIAA-CREF';
149: ,SUM(value4) value4
150: ,SUM(value5) value5
151: ,SUM(value6) value6
152: ,SUM(value7) value7
153: FROM pay_us_rpt_totals
154: WHERE tax_unit_id = c_payroll_action_id
155: AND attribute1 <> 'TIAA-CREF'
156: AND attribute5 = c_assignment_id
157: AND value9 = c_payroll_id
175: ,c_assignment_id => rpt_rec.attribute5
176: ,c_payroll_id => rpt_rec.value9);
177: FETCH csr_asg INTO csr_asg_rec;
178: IF csr_asg%NOTFOUND THEN
179: UPDATE pay_us_rpt_totals
180: SET attribute14 = NULL
181: ,attribute15 = NULL
182: WHERE tax_unit_id = p_payroll_action_id
183: AND attribute5 = rpt_rec.attribute5
198: -- ---------------------------------------------------------------------
199: -- |-----------------------< insert_rpt_data >--------------------------|
200: -- ---------------------------------------------------------------------
201: -- Insert_Rpt_Data procedure inserts the records from the PL/SQL table
202: -- into pay_us_rpt_totals table only if there exists at least one balance
203: -- value which is <> 0, i.e. if all the six balances for the TIAA-CREF
204: -- are zero for the assignment then that record from the PL/SQL table is
205: -- ignored. The PL/SQL record for the assignment is deleted from the PL/SQL
206: -- table once the insert is done(or not).
242:
243: hr_utility.set_location('..After the check if atleast one balance is <> 0', 15);
244: IF l_insert_valid THEN
245: hr_utility.set_location('..Valid for Assignment : '||p_assignment_id, 20);
246: INSERT INTO pay_us_rpt_totals
247: (tax_unit_id,
248: gre_name,
249: organization_name,
250: location_name,
321: ins_val_t(i).payroll_id ); --value9
322: hr_utility.set_location('..Inserted for assignment :'||p_assignment_id, 25);
323: END IF; -- IF l_insert_valid Then
324: -- Delete all the records from the PL/SQL table for the assignment id
325: hr_utility.set_location('..After Inserting into pay_us_rpt_totals ', 70);
326:
327: IF ins_val_t.EXISTS(i) THEN
328: ins_val_t.DELETE(i);
329: hr_utility.set_location('..Deleting for Assg ID : '||p_assignment_id,75);
846: RAISE;
847: END;
848:
849: IF chnkno = 1 THEN
850: INSERT INTO pay_us_rpt_totals
851: ( tax_unit_id, attribute1, organization_id,
852: attribute2, attribute3, attribute4,
853: attribute5
854: )
907: hr_utility.set_location('..ORG Name = '||l_org_name,50);
908: hr_utility.set_location('..Loc ID = '||TO_CHAR(l_location_id),50);
909: hr_utility.set_location('..Loc Code = '||l_location_code,50);
910:
911: -- If its diff. assign. Id then insert into pay_us_rpt_totals for that
912: -- assignment id.
913: IF l_prev_assignment_id IS NOT NULL AND
914: (l_assignment_id <> l_prev_assignment_id OR
915: l_ld_payroll_id <> l_prev_payroll_id ) THEN