[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_PAY_FILE
Source
1 package body pay_fr_pay_file as
2 /* $Header: pyfrpfcr.pkb 115.2 2002/10/16 15:30:37 srjadhav noship $
3 **
4 ** Copyright (c) 2000 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** French Payment Output File
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Version Bug Description
13 ** -----------+---------+-------+----------+------------------
14 ** 10-Oct-2002 srjadhav 115.0 2610927 Created stub version
15 ** 16-Oct-2002 srjadhav 115.1 2610927 Created the body
16 ** 16-Oct-2002 srjadhav 115.2 Removed GSCC warning
17 */
18 -- private globals for caching used by get_payers_id and valid_org
19
20 TYPE g_estab_rec IS RECORD (
21 estab_id hr_all_organization_units.ORGANIZATION_ID%TYPE,
22 company_id hr_all_organization_units.ORGANIZATION_ID%TYPE);
23 TYPE g_estab_typ IS TABLE OF g_estab_rec Index by BINARY_INTEGER;
24 g_estab_tbl g_estab_typ;
25 --
26 g_org_id hr_all_organization_units.organization_id%TYPE;
27 g_org_class hr_organization_information.org_information1%TYPE;
28
29 -- end of private globals for caching used by get_payers_id and valid_org
30
31
32 FUNCTION get_payers_id (p_opm_id in number,
33 P_bg_id in number,
34 P_date_earned in date)
35 return varchar2
36 IS
37
38 L_opm_id_chr varchar2(20);
39 L_payers_id varchar2(14);
40 BEGIN
41 L_opm_id_chr := fnd_number.number_to_canonical(p_opm_id);
42 --
43 Select distinct org.organization_id,
44 ori_class.org_information1,
45 decode(ori_class.org_information1,
46 'FR_SOCIETE', ori_info.org_information15, -- new segment
47 'FR_ETABLISSEMENT', ori_info.org_information2) -- SIRET
48 into g_org_id,
49 g_org_class,
50 L_payers_id
51 From hr_organization_information ori_opm,
52 hr_organization_information ori_class,
53 hr_organization_information ori_info,
54 hr_all_organization_units org
55 Where ori_opm.org_information1 = L_opm_id_chr
56 And ori_opm.org_information_context = 'FR_DYN_PAYMETH_MAPPING_INFO'
57 And ori_class.organization_id = ori_opm.organization_id
58 And ori_class.org_information_context = 'CLASS'
59 And ori_class.org_information1 in ('FR_SOCIETE', 'FR_ETABLISSEMENT')
60 And ori_info.organization_id = ori_opm.organization_id
61 And ori_info.org_information_context in ('FR_COMP_INFO',
62 'FR_ESTAB_INFO')
63 And org.organization_id = ori_opm.organization_id
64 And org.business_group_id = p_bg_id
65 And P_date_earned between org.date_from
66 And nvl(org.date_to, hr_general.end_of_time);
67 --
68 if L_payers_id is null then
69 return lpad(' ',16);
70 elsif g_org_class = 'FR_SOCIETE' then
71 return ')2'|| L_payers_id;
72 else
73 return ')1'|| L_payers_id;
74 end if;
75 EXCEPTION
76 WHEN TOO_MANY_ROWS THEN
77 Return 'PAY_75037_TOO_MANY_SPEC_OPMS';
78 WHEN NO_DATA_FOUND THEN
79 return lpad(' ',16);
80 END get_payers_id;
81 --
82 FUNCTION valid_org (p_estab_id in number) return varchar2 IS
83 L_estab_tbl_ind BINARY_INTEGER;
84 --
85 cursor csr_get_company is
86 select fnd_number.canonical_to_number(hoi.ORG_INFORMATION1)
87 from hr_organization_information hoi
88 where hoi.organization_id = p_estab_id
89 AND hoi.org_information_context = 'FR_ESTAB_INFO';
90 BEGIN
91 If g_org_class = 'FR_ETABLISSEMENT' THEN
92 Return hr_general.bool_to_char(p_estab_id = g_org_id);
93 ElsIf g_org_class = 'FR_SOCIETE' THEN
94 l_estab_tbl_ind := DBMS_UTILITY.get_hash_value(p_estab_id,1,1048576);
95 if not g_estab_tbl.exists(l_estab_tbl_ind)
96 or g_estab_tbl(l_estab_tbl_ind).estab_id <> p_estab_id
97 then
98 g_estab_tbl(l_estab_tbl_ind).estab_id := p_estab_id;
99 Open csr_get_company;
100 Fetch csr_get_company into g_estab_tbl(l_estab_tbl_ind).Company_id;
101 Close csr_get_company;
102 end if;
103 Return hr_general.bool_to_char
104 (g_estab_tbl(l_estab_tbl_ind).Company_id = g_org_id);
105 else
106 return 'TRUE';
107 end if;
108 END valid_org;
109
110
111 end pay_fr_pay_file;