1 PACKAGE PER_MX_SS_AFFILIATION AUTHID CURRENT_USER as
2 /* $Header: permxssaffiltion.pkh 120.2.12010000.1 2008/07/28 05:44:45 appldev ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2004, Oracle India Pvt. Ltd., Hyderabad *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19 Package Name : PER_MX_SS_AFFILIATION
20 Package File Name : permxssaffiltion.pkh
21
22 Description : Used for Social Security Affiliation report.
23
24 Change List:
25 ------------
26
27 Name Date Version Bug Text
28 ------------- ----------- ------- ------- ------------------------------
29 sdahiya 28-Jan-2007 115.0 Created.
30 sdahiya 22-Apr-2007 115.1 Procedure process_transactions
31 added.
32 sdahiya 16-May-2007 115.2 Version uprev after establishing
33 dual maintenance.
34 ***************************************************************************/
35
36
37
38 -- Global declarations
39 TYPE transaction_rec IS RECORD (
40 act_info_id NUMBER,
41 tran_type VARCHAR2(2),
42 tran_date VARCHAR2(30),
43 idw NUMBER);
44 TYPE transactions IS TABLE OF transaction_rec INDEX BY BINARY_INTEGER;
45
46 /****************************************************************************
47 Name : GET_START_DATE
48 Description : This procedure fetches start date of reporting period.
49 *****************************************************************************/
50 FUNCTION GET_START_DATE
51 (
52 P_TRANS_GRE number
53 ) RETURN VARCHAR2;
54
55 /************************************************************
56 Name : DERIVE_GRE_FROM_LOC_SCL
57 Purpose : This function derives the gre from the parmeters
58 location, BG and soft-coded keyflex.
59 ************************************************************/
60 FUNCTION DERIVE_GRE_FROM_LOC_SCL(
61 P_LOCATION_ID NUMBER,
62 P_BUSINESS_GROUP_ID NUMBER,
63 P_SOFT_CODING_KEYFLEX_ID NUMBER,
64 P_EFFECTIVE_DATE DATE)
65 RETURN NUMBER;
66
67 /****************************************************************************
68 Name : RANGE_CURSOR
69 Description : This procedure prepares range of persons to be processed.
70 *****************************************************************************/
71 PROCEDURE RANGE_CURSOR
72 (
73 P_PAYROLL_ACTION_ID number,
74 P_SQLSTR OUT NOCOPY varchar2
75 );
76
77 /****************************************************************************
78 Name : ACTION_CREATION
79 Description : This procedure creates assignment actions.
80 *****************************************************************************/
81 PROCEDURE ACTION_CREATION
82 (
83 P_PAYROLL_ACTION_ID number,
84 P_START_PERSON_ID number,
85 P_END_PERSON_ID number,
86 P_CHUNK number
87 );
88
89
90 /****************************************************************************
91 Name : INIT
92 Description : Initialization code.
93 *****************************************************************************/
94 PROCEDURE INIT
95 (
96 P_PAYROLL_ACTION_ID number
97 );
98
99
100 /****************************************************************************
101 Name : PROCESS_TRANSACTIONS
102 Description : This procedures runs through transactions to eliminate
103 redundant ones as explained below: -
104 08 - Hire transactions are always reported unless followed
105 by a termination transaction (02) within the reporting
106 period.
107 07 - Salary modification transaction will be reported only
108 if there has been a change in IDW amount since the
109 previous salary modification. Salary modification
110 transactions archived with hire/re-hire will be
111 suppressed.
112 02 - Termination transactions are always reported unless
113 preceeded by a hire transaction within the reporting
114 period.
115 *****************************************************************************/
116 PROCEDURE PROCESS_TRANSACTIONS
117 (
118 P_PERSON_ID NUMBER,
119 P_GRE_ID NUMBER,
120 P_END_DATE DATE,
121 P_REPORT_TYPE VARCHAR2,
122 P_REPORT_QUALIFIER VARCHAR2,
123 P_REPORT_CATEGORY VARCHAR2,
124 P_TRANSACTIONS IN OUT NOCOPY transactions
125 );
126
127 /****************************************************************************
128 Name : GEN_XML_HEADER
129 Description : This procedure generates XML header information to XML BLOB
130 *****************************************************************************/
131 PROCEDURE GEN_XML_HEADER;
132
133
134 /****************************************************************************
135 Name : GENERATE_XML
136 Description : This procedure fetches archived data, converts it to XML
137 format and appends to pay_mag_tape.g_blob_value.
138 *****************************************************************************/
139 PROCEDURE GENERATE_XML;
140
141
142 /****************************************************************************
143 Name : GEN_XML_FOOTER
144 Description : This procedure generates XML footer.
145 *****************************************************************************/
146 PROCEDURE GEN_XML_FOOTER;
147
148
149 level_cnt number;
150
151
152 CURSOR GET_CURR_ACT_ID IS
153 SELECT 'TRANSFER_ACT_ID=P',
154 pay_magtape_generic.get_parameter_value(
155 'TRANSFER_ACT_ID')
156 FROM DUAL;
157
158 CURSOR GET_XML_VER IS
159 SELECT 'ROOT_XML_TAG=P',
160 '<MX_SS_AFFL>',
161 'PAYROLL_ACTION_ID=P',
162 pay_magtape_generic.get_parameter_value(
163 'TRANSFER_PAYROLL_ACTION_ID')
164 FROM dual;
165
166 CURSOR GET_MAG_ASG_ACT IS
167 SELECT 'TRANSFER_ACT_ID=P',
168 assignment_action_id
169 FROM pay_assignment_actions
170 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value(
171 'TRANSFER_PAYROLL_ACTION_ID');
172
173
174 END PER_MX_SS_AFFILIATION;
[Home] [Help]