[Home] [Help]
PACKAGE: APPS.PAY_CA_T4A_XML
Source
1 package pay_ca_t4a_xml AUTHID CURRENT_USER as
2 /* $Header: pycat4axml.pkh 120.2.12020000.2 2012/07/06 09:20:17 vmaripal ship $ */
3 /*
4
5 rem +======================================================================+
6 rem | Copyright (c) 1993 Oracle Corporation |
7 rem | Redwood Shores, California, USA |
8 rem | All rights reserved. |
9 rem +======================================================================+
10 Change List
11 -----------
12 Date Name Vers Description
13 ----------- ---------- ----- -----------------------------------
14 25-AUG-2009 sapalani 115.0 Created.
15 09-sep-2010 rgottipa 115.1 Modified
16 23-sep-2010 rgottipa 115.2 Modified the cursor 'transfer_block' to get
17 employees in sortedly for YY action param
18 06-Jun-2012 rgottipa 115.3 Created the new function 'get_final_xml' which
19 is used to call from online.
20 Created new function fetch_t4a_xml also.
21 */
22
23 -- Added by sneelapa for bug 6456662
24
25 type other_info_rec is record (code varchar2(3),
26 amount varchar2(50));
27
28 type other_info_tab is table of other_info_rec index by binary_integer;
29
30 g_other_info_list other_info_tab;
31
32 procedure store_other_information(p_assignment_action_id in number);
33
34 procedure get_other_information(p_index in number,
35 p_code in out nocopy varchar2,
36 p_amount in out nocopy varchar2);
37
38 FUNCTION get_final_xml (p_assignment_action_id Number)
39 RETURN BLOB;
40
41 procedure fetch_t4a_xml(p_aa_id in number,
42 p_pa_id in number,
43 p_print in varchar2,
44 called_from in varchar2,
45 p_xml_blob out NOCOPY BLOB
46 );
47
48 -- Added by sneelapa for bug 6456662
49
50 procedure get_asg_xml;
51 procedure get_header_xml;
52 procedure get_trailer_xml;
53
54 cursor main_block is
55 select 'Version_Number=X' ,'Version 1.1'
56 from sys.dual;
57
58 --commented by rgottipa for 10136689
59 /*
60 cursor transfer_block is
61 select 'TRANSFER_ACT_ID=P', assignment_action_id
62 from pay_assignment_actions
63 where payroll_action_id =
64 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
65 */
66
67 -- cursor transfer_block modified by rgottipa for bug 10136689
68
69 cursor transfer_block is
70 select 'TRANSFER_ACT_ID=P', paa1.assignment_action_id
71 from hr_all_organization_units hou,
72 hr_all_organization_units hou1,
73 hr_locations_all loc,
74 per_all_people_f ppf,
75 per_all_assignments_f paf,
76 pay_assignment_actions paa1,
77 pay_payroll_actions ppa1
78 where paa1.payroll_action_id =
79 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
80 and paa1.payroll_action_id = ppa1.payroll_action_id
81 and paa1.assignment_id = paf.assignment_id
82 and paf.effective_start_date =
83 (select max(paf2.effective_start_date)
84 from per_all_assignments_f paf2
85 where paf2.assignment_id= paf.assignment_id
86 and paf2.effective_start_date
87 <= ppa1.effective_date)
88 and paf.effective_end_date >= ppa1.start_date
89 and paf.assignment_type = 'E'
90 and hou1.organization_id = paa1.tax_unit_id
91 and hou.organization_id = paf.organization_id
92 and loc.location_id = paf.location_id
93 and ppf.person_id = paf.person_id
94 and ppf.effective_start_date =
95 (select max(ppf2.effective_start_date)
96 from per_all_people_f ppf2
97 where ppf2.person_id= paf.person_id
98 and ppf2.effective_start_date
99 <= ppa1.effective_date)
100 and ppf.effective_end_date >= ppa1.start_date
101 order by
102
103 decode(pay_ca_t4_reg.get_parameter
104 ('P_S1',ppa1.legislative_parameters),
105 'GRE',hou1.name,
106 'ORGANIZATION',hou.name,
107 'LOCATION',loc.location_code,null),
108 decode(pay_ca_t4_reg.get_parameter('P_S2',
109 ppa1.legislative_parameters),
110 'GRE',hou1.name,
111 'ORGANIZATION',hou.name,
112 'LOCATION',loc.location_code,null),
113 decode(pay_ca_t4_reg.get_parameter('P_S3',
114 ppa1.legislative_parameters),
115 'GRE',hou1.name,
116 'ORGANIZATION',hou.name,
117 'LOCATION',loc.location_code,null),
118 ppf.last_name,ppf.first_name;
119
120
121 cursor assignment_block is
122 select 'TRANSFER_ACT_ID=P',pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
123 from pay_assignment_actions
124 where assignment_action_id=pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
125 and substr(serial_number,29,1)='N';
126
127 level_cnt NUMBER :=0;
128 g_err_emp varchar2(1);
129 g_aa_id number;
130 g_pa_id number;
131
132 end pay_ca_t4a_xml;