1 PACKAGE pay_cn_audit_xmlgen AUTHID CURRENT_USER AS
2 /* $Header: pycnauxml.pkh 120.4 2010/10/27 11:12:06 mdubasi noship $ */
3
4 /*
5 ===========================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +============================================================================+
10 Name
11 pay_cn_audit_xmlgen
12 File
13 pycnauxml.pkh
14 Purpose
15
16 The purpose of this package is to support the generation of XML for the process
17 China Payroll Data Export.
18
19 Date Author Verion Bug Details
20 ============================================================================
21 30-MAR-2010 LNAGARAJ 1.0 9469668 Initial file created
22 13-APR-2010 DDUVVURI 1.1 9469668 Resolved GSCC errors.
23 26-MAY-2010 DDUVVURI 1.2 9743013,9742923,9742617 - Modified function get_message_text
24 and used payr_name in TYPE asg_record
25 02-JUN-2010 DDUVVURI 1.3 9742617,9679498 Removed usage of 2 procedures OPEN_CSR_ASG_QUERY_DP and OPEN_CSR_ASG_QUERY_DE
26 These are not used now.
27 27-Oct-2010 MDUBASI 1.5 10115998 Modified function get_cost_alloc_key_flex
28 ============================================================================
29 */
30
31 -- 'level_cnt' will allow the cursors to select function results,
32 -- whether it is a standard fuction such as to_char or a function
33 -- defined in a package (with the correct pragma restriction).
34
35 level_cnt NUMBER;
36 g_year VARCHAR2(20);
37 g_start_date DATE;
38 g_end_date DATE;
39 g_bg_id NUMBER;
40 g_package VARCHAR2(20);
41 g_payroll_action_id number;
42 g_start_period number;
43 g_end_period number;
44
45 PROCEDURE gen_xml_header_pay;
46
47 function get_message_text(p_act_info1 VARCHAR2,
48 p_act_info2 VARCHAR2,
49 p_act_info3 VARCHAR2,
50 p_act_info4 VARCHAR2,
51 p_act_info5 VARCHAR2
52 ) return varchar2 ;
53
54 PROCEDURE generate_xml;
55
56 PROCEDURE sort_action (
57 payactid in varchar2,
58 sqlstr in out nocopy varchar2,
59 len out nocopy number
60 ) ;
61
62 PROCEDURE gen_xml_footer;
63
64 PROCEDURE range_cursor (
65 p_pactid IN NUMBER,
66 p_sqlstr OUT nocopy VARCHAR2
67 );
68
69
70 PROCEDURE action_creation(
71 p_pactid IN NUMBER,
72 p_stperson IN NUMBER,
73 p_endperson IN NUMBER,
74 p_chunk IN NUMBER );
75
76 PROCEDURE assact_xml(p_assignment_action_id IN NUMBER);
77
78 --
79
80 PROCEDURE initialization_code
81 (
82 p_payroll_action_id IN NUMBER
83 );
84
85
86 FUNCTION get_employee_number (p_person_id in number,
87 p_effective_date in date)
88 return Varchar2 ;
89
90 FUNCTION get_employee_name(p_person_id in number,
91 p_effective_date in date)
92 RETURN VARCHAR2;
93
94 FUNCTION get_cost_alloc_key_flex(p_element_type_id IN NUMBER)
95 RETURN VARCHAR2;
96
97 PROCEDURE load_xml_internal ( p_node_type IN VARCHAR2
98 ,p_node IN VARCHAR2
99 ,p_data IN VARCHAR2);
100
101 PROCEDURE set_globals;
102
103 TYPE XMLRec IS RECORD(xmlstring VARCHAR2(4000));
104 --TYPE XMLRec IS RECORD(xmlstring RAW(4000));
105 TYPE tXMLTable IS TABLE OF XMLRec INDEX BY BINARY_INTEGER;
106 vXMLTable tXMLTable;
107
108 TYPE pay_prd_rec IS RECORD
109 (p_code fnd_lookup_values.lookup_code%type,
110 p_meaning fnd_lookup_values.meaning%type);
111
112 TYPE tab_pay_prd is TABLE OF pay_prd_rec INDEX BY BINARY_INTEGER;
113 g_pay_prd tab_pay_prd;
114 g_pay_ele tab_pay_prd;
115 g_ind_asg tab_pay_prd;
116 g_ind_detail tab_pay_prd;
117
118
119 TYPE ptp_rec IS table of varchar2(1000) index by binary_integer;
120
121 TYPE pay_prd_dis_rec IS RECORD
122 (p_num VARCHAR2(1000),
123 p_start VARCHAR2(1000),
124 p_end VARCHAR2(1000),
125 p_year VARCHAR2(1000)
126 );
127 TYPE tab_pay_prd_dis is TABLE OF pay_prd_dis_rec INDEX BY BINARY_INTEGER;
128
129 TYPE asg_record IS RECORD
130 (
131 start_date DATE,
132 end_date DATE,
133 asg_id NUMBER,
134 payr_name VARCHAR2(1000)
135 );
136
137 TYPE tab_asg_rec is TABLE OF asg_record INDEX BY BINARY_INTEGER;
138
139 type t_new_type_rec is record (eno VARCHAR2(100),
140 asg_cat VARCHAR2(100),
141 emp_name VARCHAR2(100),
142 asg_org_id VARCHAR2(100),
143 pname VARCHAR2(100),
144 yr VARCHAR2(100),
145 pno VARCHAR2(100),
146 acct_yr VARCHAR2(100),
147 accnt_prd VARCHAR2(100),
148 currency VARCHAR2(100));
149 --
150 type t_new_type_cur is ref cursor return t_new_type_rec;
151 --
152
153 CURSOR c_header
154 IS
155 SELECT pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
156 FROM DUAL;
157
158 CURSOR c_asg_det
159 IS
160 SELECT 1
161 FROM DUAL ;
162
163 CURSOR c_body
164 IS
165 SELECT 'TRANSFER_ACT_ID=P',
166 assignment_action_id
167 FROM pay_assignment_actions
168 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
169 ORDER BY action_sequence;
170
171 PROCEDURE deinitialization_code (p_pactid IN NUMBER);
172
173 END pay_cn_audit_xmlgen;