[Home] [Help]
PACKAGE: APPS.PAY_GB_WNU_EDI
Source
1 PACKAGE PAY_GB_WNU_EDI as
2 /* $Header: pygbwnu2.pkh 120.2 2006/12/18 10:49:40 kthampan noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================
9 Nae
10 PAY_GB_MOVDED_EDI
11 Purpose
12 Package to contol archiver process in the creation of assignment actions
13 and then the creation of EDI message files using the magtape process for
14 EDI Message Types : WNU
15 Notes
16
17 History
18 01-NOV-2000 ILeath 115.0 Date created.
19 19-JUN-2001 SRobinson 115.1 Enforce EDI Character validation.
20 23-JUN-2001 SRobinson 115.2 Always pass a value for all asg
21 level parameters to maintain edi
22 file formatting. Also include
23 middle names.
24 06-MAR-2002 GButler 115.3 Altered cursors for UTF8. Added
25 dbdrv comments
26 28-NOV-2002 GButler 115.4 11.5.9 performance enhancements to
27 csr_wnu_full_assign and
28 csr_wnu_update_assign. nocopy
29 qualifier added to range_cursor
30 18-DEC-2003 asengar 115.6 performance enhancements to
31 csr_wnu_full_assign and
32 csr_wnu_update_assign.
33 19-JAN-2004 ssekhar 115.7 Bug 3380271: Changed the value 1.0
34 to 3.0 for the parameter VERSION=P in
35 the cursor csr_wnu_header
36 09-MAR-2004 amills 115.8 3416212, added nvl to TRANSFER_
37 SENDER_ID to trap empty value in
38 formula.
39 08-SEP-2004 kthampan 115.9 Put NONE in the field where NI number
40 is not available
41 12-JAN-2005 Kthampan 115.10 Change version from 3.0 to 1.0
42 Also amended cursor header to return
43 effective_date.
44 21-JAN-2005 Kthampan 115.11 Remove effective_date from cursor
45 header
46 16-JUN-2006 Kthampan 115.12 Code change for EDI Rollback.
47 18-DEC-2006 Kthampan 115.13 Fix bug 5718900. Remapped the achive
48 column
49 ============================================================================*/
50 --
51 --
52 CURSOR csr_wnu_header IS
53 SELECT 'TRANSFER_SENDER_ID=P', nvl(upper(hoi.org_information11),' '),
54 'TRANSFER_RECEIVER_ID=P', 'INLAND REVENUE',
55 'TEST_INDICATOR=P', upper(decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1')),
56 'URGENT_MARKER=P', upper(decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1')),
57 'ICR=P', upper(fnd_number.number_to_canonical(pact.request_id)),
58 'FORM_TYPE=P', '5',
59 'FORM_TYPE_MEANING=P', 'WNU',
60 'FORMAT_TYPE=P', 'WNU',
61 'VERSION=P', '1.0',
62 'TAX_DIST_NO=P', upper(substr(hoi.org_information1,1,3)),
63 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10))
64 FROM pay_payroll_actions pact,
65 hr_organization_information hoi
66 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
67 AND hoi.org_information_context = 'Tax Details References'
68 AND NVL(hoi.org_information10,'UK') = 'UK'
69 AND hoi.organization_id = pact.business_group_id
70 AND substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
71 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters,'TAX_REF=')+8)
72 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
73 = hoi.org_information1;
74 --
75 --
76 cursor csr_wnu_full_assign IS
77 select /*+ ORDERED */
78 'CHARS_ALREADY_TESTED=P', 'N',
79 'ASSIGNMENT_NUMBER=P', upper(pai_emp.action_information11),
80 'OLD_ASSIGNMENT_NUMBER=P', nvl(upper(pai_wnu.action_information2),' '),
81 'FIRST_NAME=P', nvl(upper(substr(pai_emp.action_information6,1,35)),' '),
82 'LAST_NAME=P', nvl(upper(substr(pai_emp.action_information8,1,35)),' '),
83 'MIDDLE_NAME=P', nvl(substr(upper(pai_emp.action_information7),1,35),' '),
84 'NATIONAL_INSURANCE_NUMBER=P', nvl(upper(pai_emp.action_information12),'NONE'),
85 'TITLE=P', nvl(upper(pai_emp.action_information14),' ')
86 from pay_assignment_actions paa,
87 pay_action_information pai_emp,
88 pay_action_information pai_wnu
89 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
90 and paa.assignment_action_id = pai_emp.action_context_id
91 and pai_emp.action_information_category = 'GB EMPLOYEE DETAILS'
92 and pai_emp.action_context_type = 'AAP'
93 and paa.assignment_action_id = pai_wnu.action_context_id(+)
94 and pai_wnu.action_information_category(+) = 'GB WNU EDI'
95 and pai_wnu.action_context_type(+) = 'AAP';
96 --
97 --
98 CURSOR csr_wnu_update_assign IS
99 select /*+ ORDERED */
100 'CHARS_ALREADY_TESTED=P', 'N',
101 'ASSIGNMENT_NUMBER=P', upper(pai_emp.action_information11),
102 'OLD_ASSIGNMENT_NUMBER=P', nvl(upper(pai_wnu.action_information2),' '),
103 'FIRST_NAME=P', nvl(upper(substr(pai_emp.action_information6,1,35)),' '),
104 'LAST_NAME=P', nvl(upper(substr(pai_emp.action_information8,1,35)),' '),
105 'MIDDLE_NAME=P', nvl(substr(upper(pai_emp.action_information7),1,35),' '),
106 'NATIONAL_INSURANCE_NUMBER=P', nvl(upper(pai_emp.action_information12),'NONE'),
107 'TITLE=P', nvl(upper(pai_emp.action_information14),' ')
108 from pay_assignment_actions paa,
109 pay_action_information pai_emp,
110 pay_action_information pai_wnu
111 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
112 and paa.assignment_action_id = pai_emp.action_context_id
113 and pai_emp.action_information_category = 'GB EMPLOYEE DETAILS'
114 and pai_emp.action_context_type = 'AAP'
115 and paa.assignment_action_id = pai_wnu.action_context_id(+)
116 and pai_wnu.action_information_category(+) = 'GB WNU EDI'
117 and pai_wnu.action_context_type(+) = 'AAP';
118 --
119 --
120 level_cnt number;
121 --
122 PROCEDURE archinit ( p_payroll_action_id IN NUMBER);
123 --
124 PROCEDURE range_cursor (pactid IN NUMBER,
125 sqlstr OUT nocopy VARCHAR2);
126 --
127 PROCEDURE wnu_cleanse_act_creation(pactid IN NUMBER,
128 stperson IN NUMBER,
129 endperson IN NUMBER,
130 chunk IN NUMBER);
131 --
132 PROCEDURE wnu_update_action_creation(pactid IN NUMBER,
133 stperson IN NUMBER,
134 endperson IN NUMBER,
135 chunk IN NUMBER);
136 --
137 PROCEDURE archive_code (p_assactid IN NUMBER,
138 p_effective_date IN DATE);
139 --
140 PROCEDURE deinitialization_code(pactid IN NUMBER);
141 --
142 END PAY_GB_WNU_EDI;