[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_PMED
Source
1 PACKAGE BODY pay_ca_pmed AS
2 /* $Header: pycapmcl.pkb 115.1 2003/03/20 00:34:20 ssattini noship $ */
3
4 ----------------------------- get_source_id ------------------------------
5 /*
6 * NAME
7 * get_source_id
8 * DESCRIPTION
9 * This function find the correct Source Id for the assignment.
10 * PARAMETERS
11 * p_jurisdiction_code - the jurisdiction code context
12 * p_tax_unit_id - the tax unit id context
13 * p_different_jd - the default account number for the given jurisdiction
14 * is used to calculate the source id if no account
15 * number parameter has been provided.
16 * p_account_number - used to calculate the source id.
17 * RETURN
18 * NUMBER - Source Id
19 */
20 FUNCTION get_source_id(p_jurisdiction_code VARCHAR2,
21 p_tax_unit_id NUMBER,
22 p_business_group_id NUMBER,
23 p_different_jd VARCHAR2,
24 p_account_number IN OUT NOCOPY VARCHAR2)
25 RETURN NUMBER IS
26
27 CURSOR csr_get_src_id1(p_juris VARCHAR2,
28 p_ac_no VARCHAR2,
29 p_bg_no number) IS
30 SELECT pma.source_id
31 FROM pay_ca_pmed_accounts pma
32 WHERE pma.account_number = p_ac_no
33 AND pma.enabled = 'Y'
34 AND pma.business_group_id = p_bg_no;
35
36 CURSOR csr_get_src_id2 (p_juris VARCHAR2,
37 p_gre NUMBER, p_bg_no NUMBER) IS
38 SELECT pma.source_id,
39 pma.account_number
40 FROM hr_organization_information ogi1,
41 hr_organization_information ogi2,
42 pay_ca_pmed_accounts pma,
43 hr_organization_information ogi3,
44 hr_organization_information ogi4
45 WHERE ogi1.organization_id = p_gre
46 AND ogi1.org_information_context = 'CLASS'
47 AND ogi1.org_information1 = 'HR_LEGAL'
48 AND ogi1.org_information2 = 'Y'
49 AND ogi2.organization_id = p_gre
50 AND ogi2.org_information_context = 'Provincial Reporting Info.'
51 AND ogi2.org_information1 = p_juris
52 AND ogi2.org_information3 = pma.account_number
53 AND pma.enabled = 'Y'
54 AND pma.business_group_id = p_bg_no
55 AND ogi3.organization_id = pma.organization_id
56 AND ogi3.org_information_context = 'CLASS'
57 AND ogi3.org_information1 = 'CA_PMED'
58 AND ogi3.org_information2 = 'Y'
59 AND ogi4.organization_id = pma.organization_id
60 AND ogi4.org_information_context = 'Provincial Information'
61 AND ogi4.org_information1 = p_juris;
62
63 CURSOR csr_get_src_id3(p_juris VARCHAR2,
64 p_gre NUMBER, p_bg_no NUMBER) IS
65 SELECT pma.source_id,
66 pma.account_number
67 FROM hr_organization_information ogi1,
68 hr_organization_information ogi2,
69 pay_ca_pmed_accounts pma,
70 hr_organization_information ogi3,
71 hr_organization_information ogi4
72 WHERE ogi1.organization_id = p_gre
73 AND ogi1.org_information_context = 'CLASS'
74 AND ogi1.org_information1 = 'HR_LEGAL'
75 AND ogi1.org_information2 = 'Y'
76 AND ogi2.organization_id = p_gre
77 AND ogi2.org_information_context = 'Provincial Reporting Info.'
78 AND ogi2.org_information1 = p_juris
79 AND ogi2.org_information3 = pma.account_number
80 AND pma.enabled = 'Y'
81 AND pma.business_group_id = p_bg_no
82 AND ogi3.organization_id = pma.organization_id
83 AND ogi3.org_information_context = 'CLASS'
84 AND ogi3.org_information1 = 'CA_PMED'
85 AND ogi3.org_information2 = 'Y'
86 AND ogi4.organization_id = pma.organization_id
87 AND ogi4.org_information_context = 'Provincial Information'
88 AND ogi4.org_information1 = p_juris;
89
90 l_source_id NUMBER;
91
92 BEGIN
93
94 hr_utility.set_location('pay_ca_pmed', 10);
95 IF p_account_number IS NOT NULL AND
96 p_account_number <> 'NOT ENTERED' THEN
97 hr_utility.set_location('pay_ca_pmed', 20);
98 OPEN csr_get_src_id1(p_jurisdiction_code,
99 p_account_number,p_business_group_id);
100 FETCH csr_get_src_id1 INTO l_source_id;
101 CLOSE csr_get_src_id1;
102 ELSIF p_different_jd IS NOT NULL AND
103 p_different_jd <> 'NOT ENTERED' THEN
104 hr_utility.set_location('pay_ca_pmed', 30);
105 OPEN csr_get_src_id2(p_different_jd,
106 p_tax_unit_id,p_business_group_id);
107 FETCH csr_get_src_id2 INTO l_source_id,
108 p_account_number;
109 CLOSE csr_get_src_id2;
110 ELSE
111 hr_utility.set_location('pay_ca_pmed', 40);
112 OPEN csr_get_src_id3(p_jurisdiction_code,
113 p_tax_unit_id,p_business_group_id);
114 FETCH csr_get_src_id3 INTO l_source_id,
115 p_account_number;
116 CLOSE csr_get_src_id3;
117 END IF;
118
119 hr_utility.set_location('pay_ca_pmed', 50);
120 RETURN l_source_id;
121
122 END;
123
124 END pay_ca_pmed;