[Home] [Help]
PACKAGE BODY: APPS.PA_RATE_SCHEDULES_PKG
Source
1 PACKAGE BODY PA_Rate_Schedules_PKG AS
2 -- $Header: PARTSCHB.pls 120.1 2005/08/19 17:01:28 mwasowic noship $
3
4 PROCEDURE Copy_Rate_Schedules (
5 P_Source_Organization_ID IN NUMBER,
6 P_Source_Rate_Schedule IN VARCHAR2,
7 P_Organization_ID IN NUMBER,
8 P_Rate_Schedule IN VARCHAR2,
9 P_Rate_Schedule_Desc IN VARCHAR2,
10 P_Rate_Sch_Currency_Code IN VARCHAR2,
11 P_Share_Across_OU_Flag IN VARCHAR2,
12 P_Escalated_Rate_Perc IN NUMBER DEFAULT 0,
13 P_Escalated_Markup_Perc IN NUMBER DEFAULT 0,
14 X_Return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
15 X_Msg_Data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
16 )
17 IS
18 l_Bill_Rate_Sch_ID NUMBER;
19 l_Org_ID NUMBER;
20 l_Exists_Flag NUMBER;
21 l_Duplicate_Flag NUMBER;
22
23 BEGIN
24
25 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
26
27 -- Bill_Rate_Sch_ID should be from the sequence PA_STD_BILL_SCH_S next value
28 SELECT PA_STD_BILL_SCH_S.NextVal
29 INTO l_Bill_Rate_Sch_ID
30 FROM DUAL;
31
32 -- Derive the Org_ID value from PA_Implemenatations table
33 SELECT Org_ID
34 INTO l_Org_ID
35 FROM PA_Implementations;
36
37 -- Check for existence of source rate schedule
38 BEGIN
39 SELECT 1 INTO l_Exists_Flag
40 FROM
41 PA_STD_BILL_RATE_SCHEDULES_ALL
42 WHERE
43 ORG_ID = l_Org_ID
44 AND STD_BILL_RATE_SCHEDULE = P_Source_Rate_Schedule
45 AND Organization_ID = P_Source_Organization_ID;
46 EXCEPTION
47 WHEN others THEN
48 Raise;
49 X_Return_Status := FND_API.G_RET_STS_ERROR;
50 X_Msg_Data := 'PA_UNEXPECTED_ERROR';
51 RETURN;
52 END;
53
54 IF l_Exists_Flag = 1 THEN
55
56 INSERT INTO
57 PA_STD_BILL_RATE_SCHEDULES_ALL (
58 ORGANIZATION_ID,
59 STD_BILL_RATE_SCHEDULE,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 CREATION_DATE,
63 CREATED_BY,
64 LAST_UPDATE_LOGIN,
65 SCHEDULE_TYPE,
66 DESCRIPTION,
67 ATTRIBUTE_CATEGORY,
68 ATTRIBUTE1,
69 ATTRIBUTE2,
70 ATTRIBUTE3,
71 ATTRIBUTE4,
72 ATTRIBUTE5,
73 ATTRIBUTE6,
74 ATTRIBUTE7,
75 ATTRIBUTE8,
76 ATTRIBUTE9,
77 ATTRIBUTE10,
78 ORG_ID,
79 BILL_RATE_SCH_ID,
80 JOB_GROUP_ID,
81 RATE_SCH_CURRENCY_CODE,
82 SHARE_ACROSS_OU_FLAG )
83 SELECT
84 P_ORGANIZATION_ID,
85 P_RATE_SCHEDULE,
86 sysdate, -- LAST_UPDATE_DATE,
87 FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
88 sysdate, -- CREATION_DATE,
89 FND_GLOBAL.User_ID, -- CREATED_BY,
90 FND_GLOBAL.User_ID, -- LAST_UPDATE_LOGIN,
91 SCHEDULE_TYPE,
92 P_Rate_Schedule_Desc,
93 ATTRIBUTE_CATEGORY,
94 ATTRIBUTE1,
95 ATTRIBUTE2,
96 ATTRIBUTE3,
97 ATTRIBUTE4,
98 ATTRIBUTE5,
99 ATTRIBUTE6,
100 ATTRIBUTE7,
101 ATTRIBUTE8,
102 ATTRIBUTE9,
103 ATTRIBUTE10,
104 ORG_ID,
105 l_Bill_Rate_Sch_ID,
106 JOB_GROUP_ID,
107 P_Rate_Sch_Currency_Code,
108 P_Share_Across_OU_Flag
109 FROM
110 PA_STD_BILL_RATE_SCHEDULES_ALL
111 WHERE
112 ORG_ID = l_Org_ID
113 AND STD_BILL_RATE_SCHEDULE = P_Source_Rate_Schedule
114 AND Organization_ID = P_Source_Organization_ID;
115 END IF;
116 --
117 -- Insert the records into detail table PA_BILL_RATES_ALL
118 --
119 INSERT INTO
120 PA_BILL_RATES_ALL (
121 BILL_RATE_ORGANIZATION_ID,
122 STD_BILL_RATE_SCHEDULE,
123 LAST_UPDATE_DATE,
124 LAST_UPDATED_BY,
125 CREATION_DATE,
126 CREATED_BY,
127 LAST_UPDATE_LOGIN,
128 START_DATE_ACTIVE,
129 PERSON_ID,
130 JOB_ID,
131 EXPENDITURE_TYPE,
132 NON_LABOR_RESOURCE,
133 RATE,
134 BILL_RATE_UNIT,
135 MARKUP_PERCENTAGE,
136 END_DATE_ACTIVE,
137 ORG_ID,
138 BILL_RATE_SCH_ID,
139 JOB_GROUP_ID,
140 RATE_CURRENCY_CODE,
141 Resource_Class_Code,
142 Res_Class_Organization_Id
143 )
144 SELECT
145 P_Organization_ID,
146 P_Rate_Schedule,
147 sysdate, -- LAST_UPDATE_DATE,
148 FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
149 Sysdate, -- CREATION_DATE,
150 FND_GLOBAL.User_ID, -- CREATED_BY,
151 FND_GLOBAL.User_ID, -- LAST_UPDATE_LOGIN,
152 START_DATE_ACTIVE,
153 PERSON_ID,
154 JOB_ID,
155 EXPENDITURE_TYPE,
156 NON_LABOR_RESOURCE,
157 RATE*(1 + NVL(P_Escalated_Rate_Perc,0)/100),
158 BILL_RATE_UNIT,
159 MARKUP_PERCENTAGE*(1 + NVL(P_Escalated_Markup_Perc,0)/100),
160 END_DATE_ACTIVE,
161 ORG_ID,
162 l_Bill_Rate_Sch_ID,
163 JOB_GROUP_ID,
164 P_Rate_Sch_Currency_Code,
165 Resource_Class_Code,
166 Res_Class_Organization_Id
167 FROM
168 PA_BILL_RATES_ALL
169 WHERE
170 ORG_ID = l_Org_ID
171 AND STD_BILL_RATE_SCHEDULE = P_Source_Rate_Schedule
172 AND Bill_Rate_Organization_ID = P_Source_Organization_ID;
173 --
174 EXCEPTION WHEN OTHERS THEN
175 ROLLBACK;
176 raise;
177 X_Return_Status := FND_API.G_RET_STS_ERROR;
178 -- X_Msg_Data := 'PA_UNEXPECTED_ERROR';
179
180 END Copy_Rate_Schedules;
181
182
183 END PA_Rate_Schedules_PKG;
184