[Home] [Help]
72: function chk_gre_archive (p_payroll_action_id number) return boolean;
73: function get_user_entity_id (p_dbi_name in varchar2) return number;
74:
75: /****************************************************************************
76: Name : HR_UTILITY_TRACE
77: Description : This procedure prints debug messages.
78: *****************************************************************************/
79:
80: PROCEDURE HR_UTILITY_TRACE
76: Name : HR_UTILITY_TRACE
77: Description : This procedure prints debug messages.
78: *****************************************************************************/
79:
80: PROCEDURE HR_UTILITY_TRACE
81: (
82: P_TRC_DATA varchar2
83: ) AS
84: BEGIN
82: P_TRC_DATA varchar2
83: ) AS
84: BEGIN
85: IF g_debug THEN
86: hr_utility.trace(p_trc_data);
87: END IF;
88: END HR_UTILITY_TRACE;
89:
90: /*
84: BEGIN
85: IF g_debug THEN
86: hr_utility.trace(p_trc_data);
87: END IF;
88: END HR_UTILITY_TRACE;
89:
90: /*
91: -------------------------------------------------------------------------------------------------------
92: -- Name : bal_db_item
112: AND UE.legislation_code = 'US';
113:
114: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
115: BEGIN
116: hr_utility.set_location
117: ('pay_us_psd_xml.bal_db_item - opening cursor', 10);
118: -- Open the cursor
119: OPEN csr_defined_balance;
120: -- Fetch the value
121: FETCH csr_defined_balance
122: INTO l_defined_balance_id;
123: IF csr_defined_balance%NOTFOUND THEN
124: CLOSE csr_defined_balance;
125: hr_utility.set_location
126: ('pay_us_psd_xml.bal_db_item - no rows found from cursor', 20);
127: hr_utility.raise_error;
128: ELSE
129: hr_utility.set_location
123: IF csr_defined_balance%NOTFOUND THEN
124: CLOSE csr_defined_balance;
125: hr_utility.set_location
126: ('pay_us_psd_xml.bal_db_item - no rows found from cursor', 20);
127: hr_utility.raise_error;
128: ELSE
129: hr_utility.set_location
130: ('pay_us_psd_xml.bal_db_item - fetched from cursor', 30);
131: CLOSE csr_defined_balance;
125: hr_utility.set_location
126: ('pay_us_psd_xml.bal_db_item - no rows found from cursor', 20);
127: hr_utility.raise_error;
128: ELSE
129: hr_utility.set_location
130: ('pay_us_psd_xml.bal_db_item - fetched from cursor', 30);
131: CLOSE csr_defined_balance;
132: END IF;
133: -- Return the value to the call
154: return l_user_entity_id;
155:
156: exception
157: when others then
158: hr_utility.trace('Error while getting the user_entity_id'
159: || to_char(sqlcode));
160: raise hr_utility.hr_error;
161:
162: end get_user_entity_id;
156: exception
157: when others then
158: hr_utility.trace('Error while getting the user_entity_id'
159: || to_char(sqlcode));
160: raise hr_utility.hr_error;
161:
162: end get_user_entity_id;
163:
164: -------------------------------------------------------------------------
245:
246: ln_defined_balance_id NUMBER;
247:
248: BEGIN
249: hr_utility.trace('opened c_get_defined_balance');
250: open c_get_defined_balance_id(p_business_group_id,
251: p_balance_name,
252: p_balance_dimension);
253:
252: p_balance_dimension);
253:
254: fetch c_get_defined_balance_id into ln_defined_balance_id;
255: if c_get_defined_balance_id%notfound then
256: hr_utility.trace('Defined balance Id not found');
257: -- Do not error out if the defined_balance_id does not exist
258: -- Pass Null instead.
259: end if;
260: close c_get_defined_balance_id;
257: -- Do not error out if the defined_balance_id does not exist
258: -- Pass Null instead.
259: end if;
260: close c_get_defined_balance_id;
261: hr_utility.trace('ln_defined_balance_id = ' ||
262: to_char(ln_defined_balance_id));
263:
264: return (ln_defined_balance_id);
265:
370: l_state_code pay_us_states.state_code%type;
371: l_period_start varchar2(200);
372: l_period_end varchar2(200);
373: BEGIN
374: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 10);
375: hr_utility.trace('Payroll_Action_Id '|| to_char(p_pactid));
376:
377: SELECT ppa.start_date,
378: ppa.effective_date,
371: l_period_start varchar2(200);
372: l_period_end varchar2(200);
373: BEGIN
374: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 10);
375: hr_utility.trace('Payroll_Action_Id '|| to_char(p_pactid));
376:
377: SELECT ppa.start_date,
378: ppa.effective_date,
379: ppa.business_group_id,
420: p_period_start:= trunc(p_period_end,'YY');
421: end if;
422:
423:
424: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
421: end if;
422:
423:
424: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
422:
423:
424: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
423:
424: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
424: hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
425: hr_utility.trace('Parameter Values ');
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
426: hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
427: hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
428: hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436:
429: hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436:
437: hr_utility.set_location
430: hr_utility.trace('p_state_code '|| p_state_code);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436:
437: hr_utility.set_location
438: ('PAY_US_PSD_XML.get_report_parameters', 40);
431: hr_utility.trace('p_report_type '|| p_report_type);
432: hr_utility.trace('p_period_type '|| p_period_type);
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436:
437: hr_utility.set_location
438: ('PAY_US_PSD_XML.get_report_parameters', 40);
439: EXCEPTION
433: hr_utility.trace('p_period_start '|| p_period_start);
434: hr_utility.trace('p_period_end '|| p_period_end);
435: hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436:
437: hr_utility.set_location
438: ('PAY_US_PSD_XML.get_report_parameters', 40);
439: EXCEPTION
440: WHEN OTHERS THEN
441: hr_utility.trace('get_report_parameters procedure Raised Exception ');
437: hr_utility.set_location
438: ('PAY_US_PSD_XML.get_report_parameters', 40);
439: EXCEPTION
440: WHEN OTHERS THEN
441: hr_utility.trace('get_report_parameters procedure Raised Exception ');
442: hr_utility.trace('ERROR '||substr(SQLERRM,1,40));
443: hr_utility.trace(substr(SQLERRM,41,90));
444: END get_report_parameters;
445: --
438: ('PAY_US_PSD_XML.get_report_parameters', 40);
439: EXCEPTION
440: WHEN OTHERS THEN
441: hr_utility.trace('get_report_parameters procedure Raised Exception ');
442: hr_utility.trace('ERROR '||substr(SQLERRM,1,40));
443: hr_utility.trace(substr(SQLERRM,41,90));
444: END get_report_parameters;
445: --
446: -- Follwing Procedure is used for Submitter Record
439: EXCEPTION
440: WHEN OTHERS THEN
441: hr_utility.trace('get_report_parameters procedure Raised Exception ');
442: hr_utility.trace('ERROR '||substr(SQLERRM,1,40));
443: hr_utility.trace(substr(SQLERRM,41,90));
444: END get_report_parameters;
445: --
446: -- Follwing Procedure is used for Submitter Record
447: --
476: ,p_state_code
477: ,p_state_abbreviation
478: ,status
479: ,p_final_xml_string);
480: HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
481: || p_final_xml_string);
482: WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
483: --}
484: END transmitter_record_start;
489: l_is_temp_final_xml VARCHAR2(2);
490:
491: BEGIN
492: l_final_xml_string := '';
493: HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
494: || l_final_xml_string);
495: WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
496: -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
497: END;
535: ,p_state_code
536: ,p_state_abbreviation
537: ,status
538: ,p_final_xml_string);
539: HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
540: || p_final_xml_string);
541: WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
542: --}
543: END psd_xml_employer_start;
548: l_is_temp_final_xml VARCHAR2(2);
549:
550: BEGIN
551: l_final_xml_string := '';
552: HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
553: || l_final_xml_string);
554: WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
555: -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
556: END psd_xml_employer_end;
661: l_tax_coll_dist_code VARCHAR2(2);
662: BEGIN
663: --{
664: -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
665: HR_UTILITY_TRACE('Constructing XML for Employee ->');
666: HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
667: HR_UTILITY_TRACE('TRANSFER_PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')));
668: HR_UTILITY_TRACE('PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')));
669:
662: BEGIN
663: --{
664: -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
665: HR_UTILITY_TRACE('Constructing XML for Employee ->');
666: HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
667: HR_UTILITY_TRACE('TRANSFER_PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')));
668: HR_UTILITY_TRACE('PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')));
669:
670: --Added for Bug 14799833
663: --{
664: -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
665: HR_UTILITY_TRACE('Constructing XML for Employee ->');
666: HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
667: HR_UTILITY_TRACE('TRANSFER_PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')));
668: HR_UTILITY_TRACE('PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')));
669:
670: --Added for Bug 14799833
671:
664: -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
665: HR_UTILITY_TRACE('Constructing XML for Employee ->');
666: HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
667: HR_UTILITY_TRACE('TRANSFER_PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')));
668: HR_UTILITY_TRACE('PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')));
669:
670: --Added for Bug 14799833
671:
672: OPEN c_get_report_params_ytd;
674: p_reporting_year,
675: p_state_abbreviation,
676: p_period_type;
677: IF(c_get_report_params_ytd%NOTFOUND) then
678: HR_UTILITY_TRACE('No data in from the cursor c_get_report_params_ytd');
679: END IF;
680:
681: CLOSE c_get_report_params_ytd;
682:
685:
686: open c_get_yepp_act_id;
687: fetch c_get_yepp_act_id into yepp_act_id;
688: if c_get_yepp_act_id%notfound then
689: HR_UTILITY_TRACE('Yepp action id not found');
690: end if;
691: p_assignment_action_id := yepp_act_id;
692: close c_get_yepp_act_id;
693:
702: p_assignment_action_id,
703: p_assignment_id,
704: p_date_earned;
705: IF(c_get_params%NOTFOUND) then
706: HR_UTILITY_TRACE('No data in from the cursor c_get_params');
707: END IF;
708:
709: CLOSE c_get_params;
710:
717: p_assignment_action_id,
718: p_assignment_id,
719: p_date_earned;
720: IF(c_get_params_ytd%NOTFOUND) then
721: HR_UTILITY_TRACE('No data in from the cursor c_get_params_ytd');
722: END IF;
723:
724: CLOSE c_get_params_ytd;
725: END IF;
743: select pay_magtape_generic.get_parameter_value('TRANSFER_EE_JD_CODE')
744: into p_jurisdiction_code
745: from dual;
746:
747: HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
744: into p_jurisdiction_code
745: from dual;
746:
747: HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
745: from dual;
746:
747: HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
746:
747: HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
747: HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
748: HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
749: HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
757:
750: HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
757:
758: get_report_parameters( to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')),--Modified for Bug#14799833
751: HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
757:
758: get_report_parameters( to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')),--Modified for Bug#14799833
759: l_year_start,
752: HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753: HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754: HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755: HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
757:
758: get_report_parameters( to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')),--Modified for Bug#14799833
759: l_year_start,
760: l_year_end,
771: open c_get_jurisdiction( p_assignment_id, p_tax_unit_id );
772: fetch c_get_jurisdiction into p_jurisdiction_code;
773: close c_get_jurisdiction;*/
774:
775: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
772: fetch c_get_jurisdiction into p_jurisdiction_code;
773: close c_get_jurisdiction;*/
774:
775: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
773: close c_get_jurisdiction;*/
774:
775: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
774:
775: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
775: HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
776: HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
784: HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
777: HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
784: HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
785:
778: HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
784: HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
785:
786: --
779: HR_UTILITY_TRACE('State Code :'||l_state_code);
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
784: HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
785:
786: --
787: -- Following Procedure Call will form the RE Record Structure in XML format
780: HR_UTILITY_TRACE('Report Type :'||l_report_type);
781: HR_UTILITY_TRACE('Period Type :'||l_period_type);
782: HR_UTILITY_TRACE('Period Start :'||l_period_start);
783: HR_UTILITY_TRACE('Period End :'||l_period_end);
784: HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
785:
786: --
787: -- Following Procedure Call will form the RE Record Structure in XML format
788: --
801: ,status
802: ,p_final_xml_string
803: );
804:
805: --HR_UTILITY_TRACE('end_of_file p_final_xml_string = '
806: -- || p_final_xml_string);
807: WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
808: --}
809: EXCEPTION
807: WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
808: --}
809: EXCEPTION
810: WHEN OTHERS THEN
811: HR_UTILITY_TRACE('Error Encountered in psd_xml_employee_build');
812: HR_UTILITY_TRACE(sqlerrm);
813: END psd_xml_employee_build;
814:
815: --Name
808: --}
809: EXCEPTION
810: WHEN OTHERS THEN
811: HR_UTILITY_TRACE('Error Encountered in psd_xml_employee_build');
812: HR_UTILITY_TRACE(sqlerrm);
813: END psd_xml_employee_build;
814:
815: --Name
816: -- preprocess_check
956: loop
957: fetch c_get_gre into l_tax_unit_id, l_gre_name;
958: exit when c_get_gre%NOTFOUND;
959:
960: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
961: hr_utility.set_location( 'preprocess_check', 10);
962:
963: -- Check whether 'PSD Reporting Rules' are defined for the GRE.
964: open c_check_psd_rules_for_gre(l_tax_unit_id);
957: fetch c_get_gre into l_tax_unit_id, l_gre_name;
958: exit when c_get_gre%NOTFOUND;
959:
960: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
961: hr_utility.set_location( 'preprocess_check', 10);
962:
963: -- Check whether 'PSD Reporting Rules' are defined for the GRE.
964: open c_check_psd_rules_for_gre(l_tax_unit_id);
965: FETCH c_check_psd_rules_for_gre INTO l_psd_rules_exist;
962:
963: -- Check whether 'PSD Reporting Rules' are defined for the GRE.
964: open c_check_psd_rules_for_gre(l_tax_unit_id);
965: FETCH c_check_psd_rules_for_gre INTO l_psd_rules_exist;
966: hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
967:
968: if c_check_psd_rules_for_gre%FOUND then
969: /*--Archiving the Employer data.
970: hr_utility.set_location( 'preprocess_check', 20);
966: hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
967:
968: if c_check_psd_rules_for_gre%FOUND then
969: /*--Archiving the Employer data.
970: hr_utility.set_location( 'preprocess_check', 20);
971: archive_gre_data(p_payroll_action_id => p_pactid,
972: p_tax_unit_id => l_tax_unit_id);
973: */
974: hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
970: hr_utility.set_location( 'preprocess_check', 20);
971: archive_gre_data(p_payroll_action_id => p_pactid,
972: p_tax_unit_id => l_tax_unit_id);
973: */
974: hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
975:
976: --Commenting the below code as it is sufficient to display GRE name
977:
978: /*open c_get_psd_codes_for_gre(l_tax_unit_id);
978: /*open c_get_psd_codes_for_gre(l_tax_unit_id);
979: loop
980: fetch c_get_psd_codes_for_gre into l_asg_id, l_psd_code;
981: exit when c_get_psd_codes_for_gre%NOTFOUND;
982: hr_utility.trace('l_asg_id: '||l_asg_id);
983:
984: --Check the WAH flag for the employee assignment.
985:
986: open c_get_wah_flag(l_asg_id , l_effective_date);
986: open c_get_wah_flag(l_asg_id , l_effective_date);
987: fetch c_get_wah_flag into l_wah_flag;
988: close c_get_wah_flag;
989:
990: hr_utility.trace('l_wah_flag '||l_wah_flag);
991:
992: if(l_wah_flag='Y') then
993: --If the employee is Working at home then we dont need to check
994: --his work PSD code under GRE level PSD Reporting Rules.
1005: if (l_psd_code = l_prev_psd_code) then
1006: --Do not print the message for same PSD code under same GRE again.
1007: null;
1008: else
1009: hr_utility.trace('Pre process rules: WARNING');
1010: message_preprocess := 'PSD Periodic Wage Listing Report - One or more Employer PSD codes are missing in PSD Reporting Rules for a GRE. ';
1011: message_text := 'Please define the PSD Reporting rules for the PSD Code '||l_psd_code || ' for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1012: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1013: pay_core_utils.push_token('record_name',message_preprocess);
1023: close c_get_psd_codes_for_gre;
1024: l_prev_psd_code:=null;*/
1025: else
1026: --
1027: hr_utility.trace('Pre process rules returned false');
1028: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules are missing for at least one GRE. ';
1029: message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1030: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
1031: pay_core_utils.push_token('record_name',message_preprocess);
1037: close c_get_gre;
1038:
1039: ELSIF l_period_type = 'Yearly' then
1040:
1041: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1042: hr_utility.set_location( 'preprocess_check', 10);
1043:
1044: /* Check if Archiver has been run for Transmitter */
1045: OPEN c_gre_payroll_action (l_transmitter);
1038:
1039: ELSIF l_period_type = 'Yearly' then
1040:
1041: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1042: hr_utility.set_location( 'preprocess_check', 10);
1043:
1044: /* Check if Archiver has been run for Transmitter */
1045: OPEN c_gre_payroll_action (l_transmitter);
1046: FETCH c_gre_payroll_action INTO l_payroll_action_id;
1045: OPEN c_gre_payroll_action (l_transmitter);
1046: FETCH c_gre_payroll_action INTO l_payroll_action_id;
1047:
1048: IF c_gre_payroll_action%NOTFOUND THEN
1049: hr_utility.trace('Transmitter not Archvied ');
1050: CLOSE c_gre_payroll_action;
1051: /* message to user -- Transmitter has not been archived */
1052: message_preprocess := 'PSD Periodic Wage Listing Report - ';
1053: message_text := 'Transmitter not Archived';
1050: CLOSE c_gre_payroll_action;
1051: /* message to user -- Transmitter has not been archived */
1052: message_preprocess := 'PSD Periodic Wage Listing Report - ';
1053: message_text := 'Transmitter not Archived';
1054: hr_utility.trace('Transmitter has not been archived');
1055: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1056: pay_core_utils.push_token('record_name',message_preprocess);
1057: pay_core_utils.push_token('description',message_text);
1058: -- hr_utility.raise_error;
1054: hr_utility.trace('Transmitter has not been archived');
1055: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1056: pay_core_utils.push_token('record_name',message_preprocess);
1057: pay_core_utils.push_token('description',message_text);
1058: -- hr_utility.raise_error;
1059: END IF;
1060: CLOSE c_gre_payroll_action;
1061:
1062: /* end of Transmitter Checking */
1060: CLOSE c_gre_payroll_action;
1061:
1062: /* end of Transmitter Checking */
1063:
1064: hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 20);
1065: open c_get_gre;
1066: loop
1067: fetch c_get_gre into l_tax_unit_id, l_gre_name;
1068: exit when c_get_gre%NOTFOUND;
1075: /* Check for the Gre That have been Archived */
1076:
1077: IF c_gre_payroll_action%FOUND THEN
1078:
1079: hr_utility.trace('GRE:' || TO_CHAR(l_tax_unit_id));
1080: hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
1081: hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
1082:
1083: l_no_of_gres_picked := l_no_of_gres_picked + 1;
1076:
1077: IF c_gre_payroll_action%FOUND THEN
1078:
1079: hr_utility.trace('GRE:' || TO_CHAR(l_tax_unit_id));
1080: hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
1081: hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
1082:
1083: l_no_of_gres_picked := l_no_of_gres_picked + 1;
1084:
1077: IF c_gre_payroll_action%FOUND THEN
1078:
1079: hr_utility.trace('GRE:' || TO_CHAR(l_tax_unit_id));
1080: hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
1081: hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
1082:
1083: l_no_of_gres_picked := l_no_of_gres_picked + 1;
1084:
1085: -- Check whether 'PSD Reporting Rules' are defined for the GRE.
1084:
1085: -- Check whether 'PSD Reporting Rules' are defined for the GRE.
1086: open c_check_psd_rules_for_gre(l_tax_unit_id);
1087: FETCH c_check_psd_rules_for_gre INTO l_psd_rules_exist;
1088: hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
1089:
1090: if c_check_psd_rules_for_gre%FOUND then
1091:
1092: hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
1088: hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
1089:
1090: if c_check_psd_rules_for_gre%FOUND then
1091:
1092: hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
1093: else
1094: --
1095: --hr_utility.trace('Pre process rules returned false');
1096: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules are missing for at least one GRE. ';
1091:
1092: hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
1093: else
1094: --
1095: --hr_utility.trace('Pre process rules returned false');
1096: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules are missing for at least one GRE. ';
1097: message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1098: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
1099: pay_core_utils.push_token('record_name',message_preprocess);
1118:
1119:
1120: IF l_package_error_status = 'Y' THEN
1121:
1122: hr_utility.trace('Error Condition Found');
1123: message_text := 'Error Condition detected ' ;
1124: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1125: pay_core_utils.push_token('record_name',message_preprocess);
1126: pay_core_utils.push_token('description',message_text);
1128: message_text := 'Pay Message line and log have more Details' ;
1129: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1130: pay_core_utils.push_token('record_name',message_preprocess);
1131: pay_core_utils.push_token('description',message_text);
1132: hr_utility.raise_error;
1133: END IF;
1134:
1135: IF l_no_of_gres_picked = 0 THEN
1136: --It means that no archived GRE was
1139: message_text := 'No GRE was picked for Magnetic Tape';
1140: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1141: pay_core_utils.push_token('record_name',message_preprocess);
1142: pay_core_utils.push_token('description',message_text);
1143: hr_utility.raise_error;
1144: END IF;
1145:
1146: END IF;
1147: return (TRUE);
1218: AND org_information_context = '1099R Magnetic Report Rules');
1219:
1220:
1221: BEGIN
1222: -- hr_utility.trace_on(null,'LOCALXML');
1223: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 10);
1224:
1225:
1226:
1219:
1220:
1221: BEGIN
1222: -- hr_utility.trace_on(null,'LOCALXML');
1223: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 10);
1224:
1225:
1226:
1227: get_report_parameters( p_pactid,
1236: l_period_end,
1237: l_tax_coll_dist_code
1238: );
1239:
1240: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 20);
1241:
1242: update pay_payroll_actions
1243: set effective_date = l_period_end
1244: WHERE payroll_action_id = p_pactid;
1247: INTO l_trans_legal_co_id
1248: FROM pay_payroll_actions
1249: WHERE payroll_action_id = p_pactid;
1250:
1251: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1252:
1253: SELECT name
1254: INTO l_gre_name
1255: FROM hr_all_organization_units
1254: INTO l_gre_name
1255: FROM hr_all_organization_units
1256: WHERE organization_id = l_trans_legal_co_id;
1257:
1258: hr_utility.trace('Transmitter Name l_gre_name: '||l_gre_name);
1259:
1260: SELECT pay_us_payroll_utils.get_parameter('TRANSFER_MONTH/QUARTER',legislative_parameters)
1261: INTO l_transfer_month_quarter
1262: FROM pay_payroll_actions
1261: INTO l_transfer_month_quarter
1262: FROM pay_payroll_actions
1263: WHERE payroll_action_id = p_pactid;
1264:
1265: hr_utility.trace('l_transfer_month_quarter: '||l_transfer_month_quarter);
1266:
1267: SELECT decode(l_tax_coll_dist_code,NULL,'ALL','0'||l_tax_coll_dist_code)
1268: INTO l_tax_coll_dist_code_or_all
1269: FROM dual;
1267: SELECT decode(l_tax_coll_dist_code,NULL,'ALL','0'||l_tax_coll_dist_code)
1268: INTO l_tax_coll_dist_code_or_all
1269: FROM dual;
1270:
1271: hr_utility.trace('l_tax_coll_dist_code_or_all: '||l_tax_coll_dist_code_or_all);
1272:
1273: SELECT report_file_name
1274: INTO l_report_file_name
1275: FROM pay_payroll_actions
1282: WHERE TCD Code = ALL or 12
1283: */
1284:
1285: l_report_file_name:=l_report_file_name||upper(substr(replace(translate(l_gre_name,'`~!@#$%^&*-_=+{}()[]\|:;"'',.<>/?', ' '),' '),1,5))||l_tax_coll_dist_code_or_all||substr(l_period_type,1,1)||replace(to_char(l_period_end,'MM-DD-YYYY'),'-','');
1286: hr_utility.trace('l_report_file_name: '||l_report_file_name);
1287:
1288: UPDATE pay_payroll_actions
1289: SET report_file_name = l_report_file_name
1290: WHERE payroll_action_id = p_pactid;
1292: UPDATE pay_payroll_actions
1293: SET magnetic_file_name = l_report_file_name
1294: WHERE payroll_action_id = p_pactid;
1295:
1296: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 30);
1297:
1298: hr_utility.trace('Checking pre process rules');
1299: if(pay_us_psd_xml.preprocess_check(p_pactid,l_business_group_id, l_tax_coll_dist_code)) then
1300: hr_utility.trace('Pre process rules returned true');
1294: WHERE payroll_action_id = p_pactid;
1295:
1296: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 30);
1297:
1298: hr_utility.trace('Checking pre process rules');
1299: if(pay_us_psd_xml.preprocess_check(p_pactid,l_business_group_id, l_tax_coll_dist_code)) then
1300: hr_utility.trace('Pre process rules returned true');
1301: else
1302: hr_utility.trace('Pre process rules returned false');
1296: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 30);
1297:
1298: hr_utility.trace('Checking pre process rules');
1299: if(pay_us_psd_xml.preprocess_check(p_pactid,l_business_group_id, l_tax_coll_dist_code)) then
1300: hr_utility.trace('Pre process rules returned true');
1301: else
1302: hr_utility.trace('Pre process rules returned false');
1303: NULL;
1304: /* hr_utility.trace('Pre process rules returned false');
1298: hr_utility.trace('Checking pre process rules');
1299: if(pay_us_psd_xml.preprocess_check(p_pactid,l_business_group_id, l_tax_coll_dist_code)) then
1300: hr_utility.trace('Pre process rules returned true');
1301: else
1302: hr_utility.trace('Pre process rules returned false');
1303: NULL;
1304: /* hr_utility.trace('Pre process rules returned false');
1305: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing for at least one GRE.';
1306: message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_trans_legal_co_id;
1300: hr_utility.trace('Pre process rules returned true');
1301: else
1302: hr_utility.trace('Pre process rules returned false');
1303: NULL;
1304: /* hr_utility.trace('Pre process rules returned false');
1305: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing for at least one GRE.';
1306: message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_trans_legal_co_id;
1307: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1308: pay_core_utils.push_token('record_name',message_preprocess);
1308: pay_core_utils.push_token('record_name',message_preprocess);
1309: pay_core_utils.push_token('description',message_text);*/
1310: end if; -- End of pre process rules check
1311:
1312: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 40);
1313: hr_utility.trace( 'Value of the l_period_type is: '||l_period_type);
1314:
1315: IF l_report_type = 'PSD_MAG_XML' THEN
1316:
1309: pay_core_utils.push_token('description',message_text);*/
1310: end if; -- End of pre process rules check
1311:
1312: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 40);
1313: hr_utility.trace( 'Value of the l_period_type is: '||l_period_type);
1314:
1315: IF l_report_type = 'PSD_MAG_XML' THEN
1316:
1317: IF l_period_type <> 'Yearly' then
1338: AND HOI2.ORG_INFORMATION2 is not null)
1339: AND ASG.payroll_id is not null
1340: AND :payroll_action_id is not null
1341: ORDER BY ASG.person_id';
1342: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 50);
1343: else
1344: --Modified for the Bug 15944697
1345: p_sqlstr := 'SELECT DISTINCT to_number(paa.serial_number)
1346: FROM pay_assignment_actions paa,
1373: WHERE hoi.organization_id = paa.tax_unit_id
1374: AND hoi.org_information_context = ''PSD Reporting Rules''
1375: )
1376: ORDER BY to_number(paa.serial_number)';
1377: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 50);
1378: end if;
1379: END IF;
1380: hr_utility.trace( p_sqlstr);
1381:
1376: ORDER BY to_number(paa.serial_number)';
1377: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 50);
1378: end if;
1379: END IF;
1380: hr_utility.trace( p_sqlstr);
1381:
1382: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1383:
1384: open c_w2_reporting_rules(l_trans_legal_co_id);
1378: end if;
1379: END IF;
1380: hr_utility.trace( p_sqlstr);
1381:
1382: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1383:
1384: open c_w2_reporting_rules(l_trans_legal_co_id);
1385:
1386: fetch c_w2_reporting_rules into l_w2_reporting_rules_exist;
1383:
1384: open c_w2_reporting_rules(l_trans_legal_co_id);
1385:
1386: fetch c_w2_reporting_rules into l_w2_reporting_rules_exist;
1387: hr_utility.trace('inside c_w2_reporting_rules ');
1388: if c_w2_reporting_rules%NOTFOUND then
1389:
1390: message_preprocess := 'PSD Periodic Wage Listing Report - W2 Reporting Rules Missing';
1391: message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1390: message_preprocess := 'PSD Periodic Wage Listing Report - W2 Reporting Rules Missing';
1391: message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1392:
1393:
1394: hr_utility.trace('W2 Reporting rules have not been setup');
1395:
1396: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1397: pay_core_utils.push_token('record_name',message_preprocess);
1398: pay_core_utils.push_token('description',message_text);
1399:
1400: end if;
1401: close c_w2_reporting_rules;
1402:
1403: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 60);
1404: hr_utility.trace('Finished with W2 Reporting Rules check ');
1405:
1406: open c_psd_reporting_rules(l_trans_legal_co_id);
1407:
1400: end if;
1401: close c_w2_reporting_rules;
1402:
1403: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 60);
1404: hr_utility.trace('Finished with W2 Reporting Rules check ');
1405:
1406: open c_psd_reporting_rules(l_trans_legal_co_id);
1407:
1408: fetch c_psd_reporting_rules into l_psd_reporting_rules_exist;
1405:
1406: open c_psd_reporting_rules(l_trans_legal_co_id);
1407:
1408: fetch c_psd_reporting_rules into l_psd_reporting_rules_exist;
1409: hr_utility.trace('inside c_psd_reporting_rules ');
1410: if c_psd_reporting_rules%NOTFOUND then
1411:
1412: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing';
1413: message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1412: message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing';
1413: message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1414:
1415:
1416: hr_utility.trace('PSD Reporting rules have not been setup');
1417:
1418: pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1419: pay_core_utils.push_token('record_name',message_preprocess);
1420: pay_core_utils.push_token('description',message_text);
1421:
1422: end if;
1423: close c_psd_reporting_rules;
1424:
1425: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 70);
1426: hr_utility.trace('Finished with PSD Reporting Rules check ');
1427:
1428:
1429: l_archive := chk_gre_archive(p_pactid);
1422: end if;
1423: close c_psd_reporting_rules;
1424:
1425: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 70);
1426: hr_utility.trace('Finished with PSD Reporting Rules check ');
1427:
1428:
1429: l_archive := chk_gre_archive(p_pactid);
1430:
1427:
1428:
1429: l_archive := chk_gre_archive(p_pactid);
1430:
1431: hr_utility.trace('after gre archive ');
1432:
1433: hr_utility.trace('g_archive_flag: '||g_archive_flag);
1434:
1435:
1429: l_archive := chk_gre_archive(p_pactid);
1430:
1431: hr_utility.trace('after gre archive ');
1432:
1433: hr_utility.trace('g_archive_flag: '||g_archive_flag);
1434:
1435:
1436: if g_archive_flag = 'N' then
1437:
1434:
1435:
1436: if g_archive_flag = 'N' then
1437:
1438: hr_utility.trace('range_cursor archiving transmitter data');
1439: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1440: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1441:
1442: archive_gre_data(p_payroll_action_id => p_pactid,
1435:
1436: if g_archive_flag = 'N' then
1437:
1438: hr_utility.trace('range_cursor archiving transmitter data');
1439: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1440: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1441:
1442: archive_gre_data(p_payroll_action_id => p_pactid,
1443: p_tax_unit_id => l_trans_legal_co_id);
1436: if g_archive_flag = 'N' then
1437:
1438: hr_utility.trace('range_cursor archiving transmitter data');
1439: hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1440: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1441:
1442: archive_gre_data(p_payroll_action_id => p_pactid,
1443: p_tax_unit_id => l_trans_legal_co_id);
1444:
1441:
1442: archive_gre_data(p_payroll_action_id => p_pactid,
1443: p_tax_unit_id => l_trans_legal_co_id);
1444:
1445: hr_utility.trace('range_cursor archived transmitter data for trans_legal_co_id: '||l_trans_legal_co_id);
1446:
1447: IF l_period_type <> 'Yearly' then --Added for the Bug 14799833, skip the employer archiver for Yearly
1448:
1449: open c_get_gre;
1453: if (l_tax_unit_id=l_trans_legal_co_id) then
1454: --Transmitter data is already archived so we dont need to archive it again.
1455: null;
1456: else
1457: hr_utility.trace('range_cursor archiving employer data');
1458: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1459: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1460:
1461: archive_gre_data(p_payroll_action_id => p_pactid,
1454: --Transmitter data is already archived so we dont need to archive it again.
1455: null;
1456: else
1457: hr_utility.trace('range_cursor archiving employer data');
1458: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1459: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1460:
1461: archive_gre_data(p_payroll_action_id => p_pactid,
1462: p_tax_unit_id => l_tax_unit_id);
1455: null;
1456: else
1457: hr_utility.trace('range_cursor archiving employer data');
1458: hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1459: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1460:
1461: archive_gre_data(p_payroll_action_id => p_pactid,
1462: p_tax_unit_id => l_tax_unit_id);
1463:
1460:
1461: archive_gre_data(p_payroll_action_id => p_pactid,
1462: p_tax_unit_id => l_tax_unit_id);
1463:
1464: hr_utility.trace('range_cursor archived employer data for tax_unit_id: '||l_tax_unit_id);
1465: end if;
1466: end loop;
1467: close c_get_gre;
1468:
1467: close c_get_gre;
1468:
1469: END IF;
1470:
1471: hr_utility.trace('range_cursor done with archiving employer data ');
1472:
1473: end if;
1474: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 90);
1475:
1470:
1471: hr_utility.trace('range_cursor done with archiving employer data ');
1472:
1473: end if;
1474: hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 90);
1475:
1476:
1477: END range_cursor;
1478:
1754: /* Get the report type, report qualifier, business group id and the
1755: gre for which the report has to be run */
1756:
1757:
1758: hr_utility.trace('In action_creation');
1759: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1755: gre for which the report has to be run */
1756:
1757:
1758: hr_utility.trace('In action_creation');
1759: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763:
1756:
1757:
1758: hr_utility.trace('In action_creation');
1759: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763:
1764:
1757:
1758: hr_utility.trace('In action_creation');
1759: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763:
1764:
1765: hr_utility.trace('getting payroll action data');
1758: hr_utility.trace('In action_creation');
1759: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763:
1764:
1765: hr_utility.trace('getting payroll action data');
1766:
1761: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763:
1764:
1765: hr_utility.trace('getting payroll action data');
1766:
1767: get_report_parameters( p_pactid,
1768: l_year_start,
1769: l_year_end,
1783: ,p_report_qualifier => 'LOCAL'
1784: ,p_report_category => 'XML');
1785:
1786: if l_range_person then
1787: hr_utility.trace('l_range_person : TRUE');
1788:
1789: if l_period_type <> 'Yearly' then --Added for Bug#14799833
1790: open c_action_person_on;
1791: else
1791: else
1792: open c_action_ytd_person_on;
1793: end if;
1794: else
1795: hr_utility.trace('l_range_person : FALSE');
1796:
1797: if l_period_type <> 'Yearly' then --Added for Bug#14799833
1798: open c_action;
1799: else
1801: end if;
1802:
1803: end if;
1804:
1805: hr_utility.trace('In action_creation');
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1802:
1803: end if;
1804:
1805: hr_utility.trace('In action_creation');
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1803: end if;
1804:
1805: hr_utility.trace('In action_creation');
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1804:
1805: hr_utility.trace('In action_creation');
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1805: hr_utility.trace('In action_creation');
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1806: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1807: hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815: hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1808: hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815: hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1816:
1809: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815: hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1816:
1817: loop
1810: hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815: hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1816:
1817: loop
1818: if l_range_person then
1811: hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812: hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813: hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814: hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815: hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1816:
1817: loop
1818: if l_range_person then
1819:
1817: loop
1818: if l_range_person then
1819:
1820: if l_period_type <> 'Yearly' then --Added for Bug#14799833
1821: hr_utility.trace('cursor1');
1822: fetch c_action_person_on INTO l_person_id,
1823: l_assignment_id,
1824: l_tax_unit_id,
1825: l_effective_end_date;
1823: l_assignment_id,
1824: l_tax_unit_id,
1825: l_effective_end_date;
1826:
1827: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1828:
1829: exit when c_action_person_on%NOTFOUND;
1830:
1831: else
1828:
1829: exit when c_action_person_on%NOTFOUND;
1830:
1831: else
1832: hr_utility.trace('cursor2');
1833: fetch c_action_ytd_person_on INTO l_person_id,
1834: l_assignment_id,
1835: l_tax_unit_id,
1836: l_effective_end_date,
1835: l_tax_unit_id,
1836: l_effective_end_date,
1837: l_assignment_action_id;
1838:
1839: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1840: hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1841:
1842: exit when c_action_ytd_person_on%NOTFOUND;
1843: end if;
1836: l_effective_end_date,
1837: l_assignment_action_id;
1838:
1839: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1840: hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1841:
1842: exit when c_action_ytd_person_on%NOTFOUND;
1843: end if;
1844:
1844:
1845: else
1846:
1847: if l_period_type <> 'Yearly' then --Added for Bug#14799833
1848: hr_utility.trace('cursor3');
1849: fetch c_action INTO l_person_id,
1850: l_assignment_id,
1851: l_tax_unit_id,
1852: l_effective_end_date;
1850: l_assignment_id,
1851: l_tax_unit_id,
1852: l_effective_end_date;
1853:
1854: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1855:
1856: exit when c_action%NOTFOUND;
1857:
1858: else
1855:
1856: exit when c_action%NOTFOUND;
1857:
1858: else
1859: hr_utility.trace('cursor4');
1860: fetch c_action_ytd INTO l_person_id,
1861: l_assignment_id,
1862: l_tax_unit_id,
1863: l_effective_end_date,
1862: l_tax_unit_id,
1863: l_effective_end_date,
1864: l_assignment_action_id;
1865:
1866: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1867: hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1868:
1869: exit when c_action_ytd%NOTFOUND;
1870:
1863: l_effective_end_date,
1864: l_assignment_action_id;
1865:
1866: hr_utility.trace('Person ID = '|| to_char(l_person_id));
1867: hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1868:
1869: exit when c_action_ytd%NOTFOUND;
1870:
1871: end if;
1873: end if;
1874:
1875: open c_get_latest_asg(l_person_id );
1876: fetch c_get_latest_asg into l_aaid, l_eff_date;
1877: hr_utility.trace('l_aaid in action creation '||to_char(l_aaid));
1878: close c_get_latest_asg;
1879:
1880: open c_get_jurisdiction( l_person_id, l_tax_unit_id ); -- Modified for Bug 14640336
1881: loop -- To consider all the jurisdiction codes for given asg_id in the table pay_us_asg_reporting
1886: pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1887:
1888: pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction_code);
1889:
1890: hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction_code));
1891:
1892: --Archiving this balance to get the value of PSD Withheld
1893:
1894: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1890: hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction_code));
1891:
1892: --Archiving this balance to get the value of PSD Withheld
1893:
1894: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1895:
1896: --check if PSD Withheld balances are <>0
1897:
1898: --Archiving the balance value according to the period type
1896: --check if PSD Withheld balances are <>0
1897:
1898: --Archiving the balance value according to the period type
1899: if (l_period_type='Monthly') then
1900: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1901: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
1902: 'PSD Withheld',
1903: '_PER_JD_GRE_MONTH');
1904: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
1903: '_PER_JD_GRE_MONTH');
1904: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
1905: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1906: ,p_balcall_aaid => l_aaid);
1907: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1908: elsif (l_period_type='Quarterly') then
1909: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1910: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
1911: 'PSD Withheld',
1905: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1906: ,p_balcall_aaid => l_aaid);
1907: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1908: elsif (l_period_type='Quarterly') then
1909: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1910: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
1911: 'PSD Withheld',
1912: '_PER_JD_GRE_QTD');
1913: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
1912: '_PER_JD_GRE_QTD');
1913: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
1914: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1915: ,p_balcall_aaid => l_aaid);
1916: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1917: elsif (l_period_type='Yearly') then --Added for the Bug 14799833
1918: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1919: l_psd_user_entity_id:= get_user_entity_id('A_PSD_WITHHELD_PER_JD_GRE_YTD');
1920: l_psd_balance_value := hr_us_w2_rep.get_w2_arch_bal (l_assignment_action_id
1914: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1915: ,p_balcall_aaid => l_aaid);
1916: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1917: elsif (l_period_type='Yearly') then --Added for the Bug 14799833
1918: hr_utility.trace('l_period_type in action creation: '||l_period_type);
1919: l_psd_user_entity_id:= get_user_entity_id('A_PSD_WITHHELD_PER_JD_GRE_YTD');
1920: l_psd_balance_value := hr_us_w2_rep.get_w2_arch_bal (l_assignment_action_id
1921: ,'A_PSD_WITHHELD_PER_JD_GRE_YTD'
1922: ,l_tax_unit_id
1926: Fetch c_get_ytd_values into l_psd_balance_value;
1927: close c_get_ytd_values;
1928: Commented for improving the performance*/
1929:
1930: hr_utility.trace('l_assignment_action_id in action creation: '||l_assignment_action_id);
1931: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1932: end if;
1933:
1934: if (l_psd_balance_value=0) then
1927: close c_get_ytd_values;
1928: Commented for improving the performance*/
1929:
1930: hr_utility.trace('l_assignment_action_id in action creation: '||l_assignment_action_id);
1931: hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1932: end if;
1933:
1934: if (l_psd_balance_value=0) then
1935: NULL;--continue;
1941:
1942: --Creating assignment actions only when PSD Withheld <>0
1943:
1944: if (l_psd_balance_value=0 or l_psd_balance_value is null) then
1945: hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1946: else
1947: hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1948: -- If the new row is the same as the previous row according to the way
1949: -- the rows are grouped then discard the row ie. grouping by GRE
1943:
1944: if (l_psd_balance_value=0 or l_psd_balance_value is null) then
1945: hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1946: else
1947: hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1948: -- If the new row is the same as the previous row according to the way
1949: -- the rows are grouped then discard the row ie. grouping by GRE
1950: -- requires a single row for each person / GRE combination.
1951: --
1948: -- If the new row is the same as the previous row according to the way
1949: -- the rows are grouped then discard the row ie. grouping by GRE
1950: -- requires a single row for each person / GRE combination.
1951: --
1952: hr_utility.set_location ('action_creation',4);
1953: l_group_by_gre :=TRUE;
1954: if (l_group_by_gre and
1955: l_person_id = l_prev_person_id and
1956: l_tax_unit_id = l_prev_tax_unit_id
1957: ) then
1958: --
1959: -- Do nothing.
1960: --
1961: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965: hr_utility.set_location ('action_creation',5);
1958: --
1959: -- Do nothing.
1960: --
1961: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965: hr_utility.set_location ('action_creation',5);
1966: null;
1959: -- Do nothing.
1960: --
1961: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965: hr_utility.set_location ('action_creation',5);
1966: null;
1967: --
1960: --
1961: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965: hr_utility.set_location ('action_creation',5);
1966: null;
1967: --
1968: -- If the balance is non zero then an assignment action is created to
1961: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965: hr_utility.set_location ('action_creation',5);
1966: null;
1967: --
1968: -- If the balance is non zero then an assignment action is created to
1969: -- indicate their inclusion in the magnetic tape report.
1968: -- If the balance is non zero then an assignment action is created to
1969: -- indicate their inclusion in the magnetic tape report.
1970: --
1971: else
1972: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976: hr_utility.set_location ('action_creation',6);
1969: -- indicate their inclusion in the magnetic tape report.
1970: --
1971: else
1972: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976: hr_utility.set_location ('action_creation',6);
1977: --
1970: --
1971: else
1972: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976: hr_utility.set_location ('action_creation',6);
1977: --
1978: -- If the payroll action has not been created yet i.e. this is the
1971: else
1972: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976: hr_utility.set_location ('action_creation',6);
1977: --
1978: -- If the payroll action has not been created yet i.e. this is the
1979: -- first assignment action then create it.
1972: hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976: hr_utility.set_location ('action_creation',6);
1977: --
1978: -- If the payroll action has not been created yet i.e. this is the
1979: -- first assignment action then create it.
1980: --
1984: --
1985: select pay_assignment_actions_s.nextval
1986: into lockingactid
1987: from dual;
1988: hr_utility.set_location ('action_creation',8);
1989:
1990: /*SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
1991: INTO l_transfer_gre_id
1992: FROM pay_payroll_actions
1990: /*SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
1991: INTO l_transfer_gre_id
1992: FROM pay_payroll_actions
1993: WHERE payroll_action_id = p_pactid;
1994: hr_utility.trace('In action_creation, l_transfer_gre_id= '||l_transfer_gre_id);*/
1995:
1996: hr_utility.set_location ('action_creation',9);
1997: -- insert into pay_assignment_actions.
1998:
1992: FROM pay_payroll_actions
1993: WHERE payroll_action_id = p_pactid;
1994: hr_utility.trace('In action_creation, l_transfer_gre_id= '||l_transfer_gre_id);*/
1995:
1996: hr_utility.set_location ('action_creation',9);
1997: -- insert into pay_assignment_actions.
1998:
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
1995:
1996: hr_utility.set_location ('action_creation',9);
1997: -- insert into pay_assignment_actions.
1998:
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1996: hr_utility.set_location ('action_creation',9);
1997: -- insert into pay_assignment_actions.
1998:
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
2004:
1997: -- insert into pay_assignment_actions.
1998:
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
2004:
2005: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
1998:
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
2004:
2005: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
2006: p_pactid,p_chunk,l_tax_unit_id);
1999: hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000: hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001: hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002: hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003: hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
2004:
2005: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
2006: p_pactid,p_chunk,l_tax_unit_id);
2007:
2004:
2005: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
2006: p_pactid,p_chunk,l_tax_unit_id);
2007:
2008: hr_utility.set_location ('action_creation',10);
2009:
2010: -- Addded for Bug 14640336
2011: update pay_assignment_actions
2012: set serial_number=l_person_id
2011: update pay_assignment_actions
2012: set serial_number=l_person_id
2013: where assignment_action_id=lockingactid;
2014:
2015: hr_utility.set_location ('action_creation',11);
2016:
2017: --Creating the Interlock if report type is Yearly Bug#14799833
2018:
2019: IF (l_period_type='Yearly') then
2018:
2019: IF (l_period_type='Yearly') then
2020:
2021: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
2022: hr_utility.set_location(
2023: 'pay_us_mmref_reporting.create_assignement_act', 90);
2024: hr_utility.trace('Interlock Created - ');
2025: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2026: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2020:
2021: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
2022: hr_utility.set_location(
2023: 'pay_us_mmref_reporting.create_assignement_act', 90);
2024: hr_utility.trace('Interlock Created - ');
2025: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2026: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2027:
2028: END IF;
2021: hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
2022: hr_utility.set_location(
2023: 'pay_us_mmref_reporting.create_assignement_act', 90);
2024: hr_utility.trace('Interlock Created - ');
2025: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2026: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2027:
2028: END IF;
2029:
2022: hr_utility.set_location(
2023: 'pay_us_mmref_reporting.create_assignement_act', 90);
2024: hr_utility.trace('Interlock Created - ');
2025: hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2026: hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2027:
2028: END IF;
2029:
2030: --
2032: --
2033: l_prev_person_id := l_person_id;
2034: l_prev_asg_id := l_assignment_id;
2035: l_prev_tax_unit_id := l_tax_unit_id;
2036: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
2037: hr_utility.trace('In action_creation, l_prev_asg_id= '||l_prev_asg_id);
2038: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
2039: --
2040:
2033: l_prev_person_id := l_person_id;
2034: l_prev_asg_id := l_assignment_id;
2035: l_prev_tax_unit_id := l_tax_unit_id;
2036: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
2037: hr_utility.trace('In action_creation, l_prev_asg_id= '||l_prev_asg_id);
2038: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
2039: --
2040:
2041: end if; /* l_person and l_tax_unit are different from the previous values */
2034: l_prev_asg_id := l_assignment_id;
2035: l_prev_tax_unit_id := l_tax_unit_id;
2036: hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
2037: hr_utility.trace('In action_creation, l_prev_asg_id= '||l_prev_asg_id);
2038: hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
2039: --
2040:
2041: end if; /* l_person and l_tax_unit are different from the previous values */
2042: end if; /*l_psd_balance_value= 0 check*/
2065: where fai.context1 = p_payroll_action_id
2066: and archive_type = 'PPA');
2067: begin
2068:
2069: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2070:
2071: if g_archive_flag = 'Y' then
2072: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2073: return (TRUE);
2068:
2069: hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2070:
2071: if g_archive_flag = 'Y' then
2072: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2073: return (TRUE);
2074: else
2075:
2076: hr_utility.trace('chk_gre_archive - opening cursor');
2072: hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2073: return (TRUE);
2074: else
2075:
2076: hr_utility.trace('chk_gre_archive - opening cursor');
2077:
2078: open c_chk_payroll_action;
2079: fetch c_chk_payroll_action into l_flag;
2080: if c_chk_payroll_action%FOUND then
2077:
2078: open c_chk_payroll_action;
2079: fetch c_chk_payroll_action into l_flag;
2080: if c_chk_payroll_action%FOUND then
2081: hr_utility.trace('chk_gre_archive - found in cursor');
2082: g_archive_flag := 'Y';
2083: else
2084: hr_utility.trace('chk_gre_archive - not found in cursor');
2085: g_archive_flag := 'N';
2080: if c_chk_payroll_action%FOUND then
2081: hr_utility.trace('chk_gre_archive - found in cursor');
2082: g_archive_flag := 'Y';
2083: else
2084: hr_utility.trace('chk_gre_archive - not found in cursor');
2085: g_archive_flag := 'N';
2086: end if;
2087:
2088: hr_utility.trace('chk_gre_archive - closing cursor');
2084: hr_utility.trace('chk_gre_archive - not found in cursor');
2085: g_archive_flag := 'N';
2086: end if;
2087:
2088: hr_utility.trace('chk_gre_archive - closing cursor');
2089: close c_chk_payroll_action;
2090: if g_archive_flag = 'Y' then
2091: hr_utility.trace('chk_gre_archive - returning true');
2092: return (TRUE);
2087:
2088: hr_utility.trace('chk_gre_archive - closing cursor');
2089: close c_chk_payroll_action;
2090: if g_archive_flag = 'Y' then
2091: hr_utility.trace('chk_gre_archive - returning true');
2092: return (TRUE);
2093: else
2094: hr_utility.trace('chk_gre_archive - returning false');
2095: return(FALSE);
2090: if g_archive_flag = 'Y' then
2091: hr_utility.trace('chk_gre_archive - returning true');
2092: return (TRUE);
2093: else
2094: hr_utility.trace('chk_gre_archive - returning false');
2095: return(FALSE);
2096: end if;
2097: end if;
2098: end chk_gre_archive;
2226: when no_data_found then
2227: l_value1 := null;
2228: l_value2 := null;
2229: l_value3 := null;
2230: hr_utility.trace('Error in archive_gre_data at step : ' ||
2231: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2232: end;
2233: l_arch_gre_step := 50;
2234:
2330: --l_value15 := null; /* Job Development Fee (AL) */
2331: l_value16 := null;
2332: l_value19 := null; -- Bug 6928011 access code ( PR)
2333: l_value20 := null;
2334: hr_utility.trace('Error in archive_gre_data at step :' ||
2335: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2336: end;
2337:
2338: l_arch_gre_step := 90;
2402: exception
2403: when no_data_found then
2404: l_value1 := null;
2405: l_value2 := null;
2406: hr_utility.trace('Error in archive_gre_data at step : ' ||
2407: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2408: end;
2409:
2410: /* Initialise the PL/SQL tables */
2462: when no_data_found then
2463: l_value1 := null;
2464: l_value2 := null;
2465: l_value3 := null;
2466: hr_utility.trace('Error in archive_gre_data at step : ' ||
2467: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2468: end;
2469:
2470: /* Initialise the PL/SQL tables */
2527:
2528: if c_get_psd_rules%notfound then
2529: l_value1 := null;
2530: l_value2 := null;
2531: hr_utility.trace('Error in archive_gre_data at step :' ||
2532: to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2533: end if;
2534:
2535:
2650:
2651:
2652: BEGIN
2653:
2654: hr_utility.set_location ('action_archinit',1);
2655:
2656:
2657: hr_utility.trace ('action_archinit getting report parameters ');
2658:
2653:
2654: hr_utility.set_location ('action_archinit',1);
2655:
2656:
2657: hr_utility.trace ('action_archinit getting report parameters ');
2658:
2659: get_report_parameters( p_payroll_action_id,
2660: l_year_start,
2661: l_year_end,
2668: l_period_end,
2669: l_tax_coll_dist_code
2670: );
2671:
2672: hr_utility.trace ('action_archinit got report parameters ');
2673:
2674: l_jurisdiction_code := '99-999-9999';
2675:
2676: hr_utility.set_location ('action_archinit getting min chunk number',10);
2672: hr_utility.trace ('action_archinit got report parameters ');
2673:
2674: l_jurisdiction_code := '99-999-9999';
2675:
2676: hr_utility.set_location ('action_archinit getting min chunk number',10);
2677:
2678: open c_get_min_chunk;
2679: fetch c_get_min_chunk into g_min_chunk;
2680: l_step := 10;
2683: raise_application_error(-20001,'action_archinit: Assignment actions not created!!!');
2684: end if;
2685: close c_get_min_chunk;
2686:
2687: hr_utility.set_location ('action_archinit min chunk is ' || to_char(g_min_chunk),12);
2688:
2689:
2690: l_archive := chk_gre_archive(p_payroll_action_id);
2691:
2689:
2690: l_archive := chk_gre_archive(p_payroll_action_id);
2691:
2692:
2693: hr_utility.trace ('action_archinit g_archive_flag is ' || g_archive_flag);
2694:
2695: open c_get_psd_balances;
2696: loop
2697: fetch c_get_psd_balances into lv_balance_name, ln_balance_type_id;
2695: open c_get_psd_balances;
2696: loop
2697: fetch c_get_psd_balances into lv_balance_name, ln_balance_type_id;
2698: if c_get_psd_balances%notfound then
2699: hr_utility.set_location('action_archinit c_get_psd_balances not found', 170);
2700: exit;
2701: end if;
2702: hr_utility.set_location('action_archinit', 180);
2703:
2698: if c_get_psd_balances%notfound then
2699: hr_utility.set_location('action_archinit c_get_psd_balances not found', 170);
2700: exit;
2701: end if;
2702: hr_utility.set_location('action_archinit', 180);
2703:
2704: ln_psd_count := ln_psd_count + 1;
2705:
2706: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name
2708:
2709:
2710: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id
2711: := ln_balance_type_id;
2712: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2713: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2714: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2715: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2716: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2709:
2710: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id
2711: := ln_balance_type_id;
2712: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2713: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2714: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2715: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2716: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2717: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2711: := ln_balance_type_id;
2712: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2713: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2714: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2715: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2716: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2717: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2718:
2719:
2712: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2713: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2714: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2715: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2716: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2717: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2718:
2719:
2720: IF (instr(lv_balance_name, 'MONTH') <> 0) THEN
2745: where creator_id= (pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).qtd_def_bal_id)
2746: and CREATOR_TYPE='B';
2747: END IF;
2748:
2749: hr_utility.set_location('action_archinit', 190);
2750: hr_utility.trace('PSD DBI Name= ' ||
2751: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2746: and CREATOR_TYPE='B';
2747: END IF;
2748:
2749: hr_utility.set_location('action_archinit', 190);
2750: hr_utility.trace('PSD DBI Name= ' ||
2751: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2748:
2749: hr_utility.set_location('action_archinit', 190);
2750: hr_utility.trace('PSD DBI Name= ' ||
2751: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2755: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2756: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2749: hr_utility.set_location('action_archinit', 190);
2750: hr_utility.trace('PSD DBI Name= ' ||
2751: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2755: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2756: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2757: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2751: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2755: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2756: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2757: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2758:
2759: end loop;
2752: hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2755: hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2756: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2757: pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2758:
2759: end loop;
2760: close c_get_psd_balances;
2758:
2759: end loop;
2760: close c_get_psd_balances;
2761:
2762: hr_utility.trace('PSD Balance Loop Count = ' ||
2763: pay_us_psd_xml.ltr_psd_tax_bal.count);
2764:
2765: END action_archinit;
2766:
2923: and l_date_earned BETWEEN PEOPLE.effective_start_date
2924: AND PEOPLE.effective_end_date;
2925:
2926: begin
2927: hr_utility.set_location ('archive_data',1);
2928: hr_utility.trace('p_assactid is : ' || to_char(p_assactid));
2929:
2930:
2931: --Added by skchalla for PSD year end reporting bug 14799833
2924: AND PEOPLE.effective_end_date;
2925:
2926: begin
2927: hr_utility.set_location ('archive_data',1);
2928: hr_utility.trace('p_assactid is : ' || to_char(p_assactid));
2929:
2930:
2931: --Added by skchalla for PSD year end reporting bug 14799833
2932: IF pay_magtape_generic.get_parameter_value
2944: l_payroll_action_id
2945: FROM pay_assignment_actions aa
2946: WHERE aa.assignment_action_id = p_assactid;
2947:
2948: hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949: hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2945: FROM pay_assignment_actions aa
2946: WHERE aa.assignment_action_id = p_assactid;
2947:
2948: hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949: hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953:
2946: WHERE aa.assignment_action_id = p_assactid;
2947:
2948: hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949: hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953:
2954: hr_utility.trace ('archive_data getting report parameters ');
2947:
2948: hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949: hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953:
2954: hr_utility.trace ('archive_data getting report parameters ');
2955:
2948: hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949: hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953:
2954: hr_utility.trace ('archive_data getting report parameters ');
2955:
2956: get_report_parameters( l_payroll_action_id,
2950: hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951: hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952: hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953:
2954: hr_utility.trace ('archive_data getting report parameters ');
2955:
2956: get_report_parameters( l_payroll_action_id,
2957: l_year_start,
2958: l_year_end,
2965: l_period_end,
2966: l_tax_coll_dist_code
2967: );
2968:
2969: hr_utility.trace ('archive_data got report parameters ');
2970:
2971: hr_utility.trace('g_min_chunk is : ' || to_char(g_min_chunk));
2972: hr_utility.trace('g_archive_flag is : ' || to_char(g_archive_flag));
2973: /* if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2967: );
2968:
2969: hr_utility.trace ('archive_data got report parameters ');
2970:
2971: hr_utility.trace('g_min_chunk is : ' || to_char(g_min_chunk));
2972: hr_utility.trace('g_archive_flag is : ' || to_char(g_archive_flag));
2973: /* if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2974:
2975: hr_utility.trace('archive_data archiving employer data');
2968:
2969: hr_utility.trace ('archive_data got report parameters ');
2970:
2971: hr_utility.trace('g_min_chunk is : ' || to_char(g_min_chunk));
2972: hr_utility.trace('g_archive_flag is : ' || to_char(g_archive_flag));
2973: /* if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2974:
2975: hr_utility.trace('archive_data archiving employer data');
2976:
2971: hr_utility.trace('g_min_chunk is : ' || to_char(g_min_chunk));
2972: hr_utility.trace('g_archive_flag is : ' || to_char(g_archive_flag));
2973: /* if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2974:
2975: hr_utility.trace('archive_data archiving employer data');
2976:
2977: archive_gre_data(
2978: p_payroll_action_id => l_payroll_action_id,
2979: p_tax_unit_id => l_taxunitid);
2977: archive_gre_data(
2978: p_payroll_action_id => l_payroll_action_id,
2979: p_tax_unit_id => l_taxunitid);
2980:
2981: hr_utility.trace('archive_data archived employer data');
2982: end if;*/
2983:
2984: hr_utility.set_location ('archive_data',2);
2985:
2980:
2981: hr_utility.trace('archive_data archived employer data');
2982: end if;*/
2983:
2984: hr_utility.set_location ('archive_data',2);
2985:
2986: hr_utility.trace('assignment '|| to_char(l_asgid));
2987: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2988: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2982: end if;*/
2983:
2984: hr_utility.set_location ('archive_data',2);
2985:
2986: hr_utility.trace('assignment '|| to_char(l_asgid));
2987: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2988: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2989:
2990: open c_get_latest_asg(l_asgid );
2983:
2984: hr_utility.set_location ('archive_data',2);
2985:
2986: hr_utility.trace('assignment '|| to_char(l_asgid));
2987: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2988: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2989:
2990: open c_get_latest_asg(l_asgid );
2991: fetch c_get_latest_asg into l_aaid, l_eff_date;
2984: hr_utility.set_location ('archive_data',2);
2985:
2986: hr_utility.trace('assignment '|| to_char(l_asgid));
2987: hr_utility.trace('date_earned '|| to_char(l_date_earned));
2988: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2989:
2990: open c_get_latest_asg(l_asgid );
2991: fetch c_get_latest_asg into l_aaid, l_eff_date;
2992: hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
2988: hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2989:
2990: open c_get_latest_asg(l_asgid );
2991: fetch c_get_latest_asg into l_aaid, l_eff_date;
2992: hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
2993: close c_get_latest_asg;
2994:
2995: -- Added for Bug 14640336
2996: /* select person_id into l_person_id
3011: pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
3012:
3013: pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction);
3014:
3015: hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction));
3016:
3017: --Archiving this balance to get the value of PSD Withheld
3018:
3019: hr_utility.trace('l_period_type in archive data: '||l_period_type);
3015: hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction));
3016:
3017: --Archiving this balance to get the value of PSD Withheld
3018:
3019: hr_utility.trace('l_period_type in archive data: '||l_period_type);
3020:
3021: --check if PSD Withheld balances are <>0
3022:
3023: --Archiving the balance value according to the period type
3021: --check if PSD Withheld balances are <>0
3022:
3023: --Archiving the balance value according to the period type
3024: if (l_period_type='Monthly') then
3025: hr_utility.trace('l_period_type in archive data: '||l_period_type);
3026: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
3027: 'PSD Withheld',
3028: '_PER_JD_GRE_MONTH');
3029: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
3028: '_PER_JD_GRE_MONTH');
3029: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
3030: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
3031: ,p_balcall_aaid => l_aaid);
3032: hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
3033: elsif (l_period_type='Quarterly') then
3034: hr_utility.trace('l_period_type in archive data: '||l_period_type);
3035: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
3036: 'PSD Withheld',
3030: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
3031: ,p_balcall_aaid => l_aaid);
3032: hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
3033: elsif (l_period_type='Quarterly') then
3034: hr_utility.trace('l_period_type in archive data: '||l_period_type);
3035: l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
3036: 'PSD Withheld',
3037: '_PER_JD_GRE_QTD');
3038: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
3037: '_PER_JD_GRE_QTD');
3038: l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
3039: l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
3040: ,p_balcall_aaid => l_aaid);
3041: hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
3042: end if; --period type
3043:
3044: if (l_psd_balance_value<>0) then
3045:
3055: pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id;
3056: ln_month_def_bal_id :=
3057: pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id;
3058:
3059: hr_utility.trace('k ='||k);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3056: ln_month_def_bal_id :=
3057: pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id;
3058:
3059: hr_utility.trace('k ='||k);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3057: pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id;
3058:
3059: hr_utility.trace('k ='||k);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3058:
3059: hr_utility.trace('k ='||k);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3059: hr_utility.trace('k ='||k);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3060: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068: hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3061: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068: hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3069:
3062: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068: hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3069:
3070: --if p_action_type in ('U', 'P') then
3063: hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068: hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3069:
3070: --if p_action_type in ('U', 'P') then
3071:
3064: hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065: hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066: hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067: hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068: hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3069:
3070: --if p_action_type in ('U', 'P') then
3071:
3072: if ln_qtd_def_bal_id is NULL then
3072: if ln_qtd_def_bal_id is NULL then
3073: ln_bal_value := get_balance_value(
3074: p_defined_balance_id => ln_month_def_bal_id
3075: ,p_balcall_aaid => l_aaid);
3076: hr_utility.trace('ln_bal_value (in month) ='||ln_bal_value);
3077: elsif ln_month_def_bal_id is NULL then
3078: ln_bal_value := get_balance_value(
3079: p_defined_balance_id => ln_qtd_def_bal_id
3080: ,p_balcall_aaid => l_aaid);
3077: elsif ln_month_def_bal_id is NULL then
3078: ln_bal_value := get_balance_value(
3079: p_defined_balance_id => ln_qtd_def_bal_id
3080: ,p_balcall_aaid => l_aaid);
3081: hr_utility.trace('ln_bal_value (in qtd) ='||ln_bal_value);
3082: end if;
3083: hr_utility.trace('ln_bal_value = '|| ln_bal_value);
3084:
3085: l_user_entity_id:= get_user_entity_id('A_'||(pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name));
3079: p_defined_balance_id => ln_qtd_def_bal_id
3080: ,p_balcall_aaid => l_aaid);
3081: hr_utility.trace('ln_bal_value (in qtd) ='||ln_bal_value);
3082: end if;
3083: hr_utility.trace('ln_bal_value = '|| ln_bal_value);
3084:
3085: l_user_entity_id:= get_user_entity_id('A_'||(pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name));
3086:
3087: ff_archive_api.create_archive_item(
3163:
3164: begin
3165:
3166:
3167: hr_utility.trace('Initialised Pl/SQL table');
3168: l_user_entity_id_tab.delete;
3169: l_value_tab.delete;
3170:
3171: -- l_index := 0;
3207: end loop;
3208:
3209: exception
3210: when others then
3211: hr_utility.trace('Problem in inserting employee data');
3212: end;
3213:
3214: end if;
3215: exception
3213:
3214: end if;
3215: exception
3216: when others then
3217: hr_utility.trace('Problem in archive_data');
3218:
3219:
3220: end archive_data;
3221:
3221:
3222: BEGIN
3223:
3224: g_proc_name := 'PAY_US_PSD_XML.';
3225: g_debug := hr_utility.debug_enabled;
3226: g_document_type := 'LOCAL_PSD_XML';
3227:
3228: END pay_us_psd_xml;