1 PACKAGE PER_MX_SS_AFFILIATION AUTHID CURRENT_USER as
2 /* $Header: permxssaffiltion.pkh 120.3.12020000.1 2012/06/29 02:09:20 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 vvijayku 07-Nov-2008 115.3 Modified the transaction_rec with the
35 variable reporting_option.
36 ***************************************************************************/
37
38
39
40 -- Global declarations
41 TYPE transaction_rec IS RECORD (
42 act_info_id NUMBER,
43 tran_type VARCHAR2(2),
44 tran_date VARCHAR2(30),
45 idw NUMBER,
46 reporting_option VARCHAR2(4));
47 TYPE transactions IS TABLE OF transaction_rec INDEX BY BINARY_INTEGER;
48
49 /****************************************************************************
50 Name : GET_START_DATE
51 Description : This procedure fetches start date of reporting period.
52 *****************************************************************************/
53 FUNCTION GET_START_DATE
54 (
55 P_TRANS_GRE number
56 ) RETURN VARCHAR2;
57
58 /************************************************************
59 Name : DERIVE_GRE_FROM_LOC_SCL
60 Purpose : This function derives the gre from the parmeters
61 location, BG and soft-coded keyflex.
62 ************************************************************/
63 FUNCTION DERIVE_GRE_FROM_LOC_SCL(
64 P_LOCATION_ID NUMBER,
65 P_BUSINESS_GROUP_ID NUMBER,
66 P_SOFT_CODING_KEYFLEX_ID NUMBER,
67 P_EFFECTIVE_DATE DATE)
68 RETURN NUMBER;
69
70 /****************************************************************************
71 Name : RANGE_CURSOR
72 Description : This procedure prepares range of persons to be processed.
73 *****************************************************************************/
74 PROCEDURE RANGE_CURSOR
75 (
76 P_PAYROLL_ACTION_ID number,
77 P_SQLSTR OUT NOCOPY varchar2
78 );
79
80 /****************************************************************************
81 Name : ACTION_CREATION
82 Description : This procedure creates assignment actions.
83 *****************************************************************************/
84 PROCEDURE ACTION_CREATION
85 (
86 P_PAYROLL_ACTION_ID number,
87 P_START_PERSON_ID number,
88 P_END_PERSON_ID number,
89 P_CHUNK number
90 );
91
92
93 /****************************************************************************
94 Name : INIT
95 Description : Initialization code.
96 *****************************************************************************/
97 PROCEDURE INIT
98 (
99 P_PAYROLL_ACTION_ID number
100 );
101
102
103 /****************************************************************************
104 Name : PROCESS_TRANSACTIONS
105 Description : This procedures runs through transactions to eliminate
106 redundant ones as explained below: -
107 08 - Hire transactions are always reported unless followed
108 by a termination transaction (02) within the reporting
109 period.
110 07 - Salary modification transaction will be reported only
111 if there has been a change in IDW amount since the
112 previous salary modification. Salary modification
113 transactions archived with hire/re-hire will be
114 suppressed.
115 02 - Termination transactions are always reported unless
116 preceeded by a hire transaction within the reporting
117 period.
118 *****************************************************************************/
119 PROCEDURE PROCESS_TRANSACTIONS
120 (
121 P_PERSON_ID NUMBER,
122 P_GRE_ID NUMBER,
123 P_END_DATE DATE,
124 P_REPORT_TYPE VARCHAR2,
125 P_REPORT_QUALIFIER VARCHAR2,
126 P_REPORT_CATEGORY VARCHAR2,
127 P_TRANSACTIONS IN OUT NOCOPY transactions
128 );
129
130 /****************************************************************************
131 Name : GEN_XML_HEADER
132 Description : This procedure generates XML header information to XML BLOB
133 *****************************************************************************/
134 PROCEDURE GEN_XML_HEADER;
135
136
137 /****************************************************************************
138 Name : GENERATE_XML
139 Description : This procedure fetches archived data, converts it to XML
140 format and appends to pay_mag_tape.g_blob_value.
141 *****************************************************************************/
142 PROCEDURE GENERATE_XML;
143
144
145 /****************************************************************************
146 Name : GEN_XML_FOOTER
147 Description : This procedure generates XML footer.
148 *****************************************************************************/
149 PROCEDURE GEN_XML_FOOTER;
150
151
152 level_cnt number;
153
154
155 CURSOR GET_CURR_ACT_ID IS
156 SELECT 'TRANSFER_ACT_ID=P',
157 pay_magtape_generic.get_parameter_value(
158 'TRANSFER_ACT_ID')
159 FROM DUAL;
160
161 CURSOR GET_XML_VER IS
162 SELECT 'ROOT_XML_TAG=P',
163 '<MX_SS_AFFL>',
164 'PAYROLL_ACTION_ID=P',
165 pay_magtape_generic.get_parameter_value(
166 'TRANSFER_PAYROLL_ACTION_ID')
167 FROM dual;
168
169 CURSOR GET_MAG_ASG_ACT IS
170 SELECT 'TRANSFER_ACT_ID=P',
171 assignment_action_id
172 FROM pay_assignment_actions
173 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value(
174 'TRANSFER_PAYROLL_ACTION_ID');
175
176
177 END PER_MX_SS_AFFILIATION;
[Home] [Help]