[Home] [Help]
PACKAGE: APPS.PAY_MX_PAYREG_PKG
Source
1 PACKAGE pay_mx_payreg_pkg AUTHID CURRENT_USER AS
2 /* $Header: paymxreg.pkh 120.0.12020000.3 2013/02/21 12:02:59 nkjaladi noship $ */
3 /*************************************************************************
4 +======================================================================+
5 | Copyright (c) 1997 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 Package Body Name : pay_mx_payreg_pkg
10 Package File Name : pymxpreg.pkh
11 Description : This package declares functions which are used in Mexico
12 Payroll Register Report.
13
14 Change List:
15 ------------
16
17 Name Date Version Bug Text
18 ----------- ------------ ------ --------- ------------------------------
19 nkjaladi 06-Feb-2013 120.0 14830976 Created
20 nkjaladi 07-Feb-2013 120.1 14830976 Enabled Dual Maintainance
21 nkjaladi 20-Feb-2013 120.2 16364431 Modified Cursor GET_GRE and
22 Cursor GET_PR_ASG_ACT for
23 sorting the data.
24 *************************************************************************/
25
26 g_mag_gre_id NUMBER;
27 g_mag_mode VARCHAR2(10);
28 g_mag_start_date VARCHAR2(25);
29 g_mag_end_date VARCHAR2(25);
30 level_cnt NUMBER;
31
32 PROCEDURE range_cursor ( pactid IN NUMBER
33 ,sqlstr OUT NOCOPY VARCHAR2
34 );
35
36 PROCEDURE action_creation ( pactid IN NUMBER
37 ,stperson IN NUMBER
38 ,endperson IN NUMBER
39 ,chunk IN NUMBER
40 );
41
42 PROCEDURE sort_action ( payactid IN VARCHAR2
43 ,sqlstr IN OUT NOCOPY VARCHAR2
44 ,len OUT NOCOPY NUMBER
45 );
46
47 FUNCTION get_parameter(name IN VARCHAR2
48 ,parameter_list IN VARCHAR2) RETURN VARCHAR2;
49
50 FUNCTION sort_option (c_option_name IN VARCHAR2
51 ,c_assignment_id IN NUMBER
52 ,c_effective_date IN DATE
53 ,c_tax_unit_id IN NUMBER) RETURN VARCHAR2;
54
55 PROCEDURE generate_xml;
56
57 PROCEDURE generate_xml_header;
58
59 PROCEDURE generate_xml_footer;
60
61 PROCEDURE generate_gre_xml_header;
62
63 PROCEDURE generate_gre_xml_footer;
64
65 PRAGMA RESTRICT_REFERENCES(get_parameter, WNDS, WNPS);
66
67 CURSOR GET_CURR_ACT_ID IS
68 SELECT 'TRANSFER_ACT_ID=P'
69 ,pay_magtape_generic.get_parameter_value(
70 'TRANSFER_ACT_ID')
71 FROM DUAL;
72
73 CURSOR GET_GRE IS
74 SELECT DISTINCT 'ROOT_GRE_TAG=P'
75 ,'<GRE>'
76 ,'TRANSFER_GRE_ID=P'
77 ,tax_unit_id
78 ,hr_general.decode_organization(tax_unit_id)
79 ,'PAYROLL_ACTION_ID=P'
80 ,pay_magtape_generic.get_parameter_value(
81 'TRANSFER_PAYROLL_ACTION_ID')
82 FROM pay_assignment_actions
83 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value(
84 'TRANSFER_PAYROLL_ACTION_ID')
85 ORDER BY hr_general.decode_organization(tax_unit_id);
86
87 CURSOR GET_XML_VER IS
88 SELECT 'ROOT_XML_TAG=P'
89 ,'<PAYROLL_REGISTER>'
90 ,'PAYROLL_ACTION_ID=P'
91 ,pay_magtape_generic.get_parameter_value(
92 'TRANSFER_PAYROLL_ACTION_ID')
93 FROM dual;
94
95 CURSOR GET_PR_ASG_ACT IS
96 SELECT 'TRANSFER_ACT_ID=P'
97 ,assignment_action_id
98 ,'TRANSFER_GRE_ID=P'
99 ,tax_unit_id
100 FROM pay_assignment_actions paa,
101 pay_payroll_actions ppa
102 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value(
103 'TRANSFER_PAYROLL_ACTION_ID')
104 AND ppa.payroll_action_id = paa.payroll_action_id
105 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value(
106 'TRANSFER_GRE_ID')
107 ORDER BY (decode(pay_mx_payreg_pkg.get_parameter('P_S1', ppa.legislative_parameters),
108 null, null,
109 pay_mx_payreg_pkg.sort_option (
110 pay_mx_payreg_pkg.get_parameter('P_S1', ppa.legislative_parameters),
111 paa.assignment_id,
112 ppa.effective_date,
113 paa.tax_unit_id))),
114 (SELECT ppa2.effective_date
115 FROM pay_payroll_actions ppa2,
116 pay_assignment_actions paa2
117 WHERE paa2.assignment_action_id = to_number(substr(paa.serial_number,2))
118 and paa2.payroll_action_id = ppa2.payroll_action_id
119 and ppa2.action_type in ('R', 'Q', 'V', 'B')
120 );
121
122 TYPE xml_rec IS RECORD
123 (name VARCHAR2(80)
124 ,value VARCHAR2(240)
125 );
126
127 TYPE xml_tbl IS TABLE OF xml_rec INDEX BY BINARY_INTEGER;
128 --
129 END pay_mx_payreg_pkg;