[Home] [Help]
PACKAGE BODY: APPS.PA_PWP_SUMM_PKG
Source
1 PACKAGE BODY PA_PWP_SUMM_PKG AS
2 -- $Header: PAPWPSMB.pls 120.0.12010000.16 2009/02/05 06:01:26 jjgeorge noship $
3
4 -- This procedure populates data in PA_PWP_CUSTOMER_SUMM_ALL table.
5 -- The data populated in PA_PWP_CUSTOMER_SUMM_ALL table is used in subcontractor
6 --(supplier) workbench.
7 PROCEDURE Populate_summary
8 (P_Project_Id IN NUMBER)
9 IS
10 l_start_date DATE ;
11 l_end_date DATE ;
12 l_user_id number(15);
13 l_date date ;
14 l_org_id number(15);
15 BEGIN
16
17 DELETE
18 FROM PA_PWP_CUSTOMER_SUMM WHERE project_id = P_Project_Id ;
19
20 SELECT org_id
21 INTO l_org_id
22 FROM pa_implementations;
23
24
25 --Insert the Invoice level attributes to the temp Table
26 INSERT
27 INTO PA_PWP_CUSTOMER_SUMM
28 ( ORG_ID
29 , PROJECT_ID
30 , draft_invoice_num
31 , RA_INVOICE_NUMBER
32 , DRAFT_INVOICE_NUM_CREDITED
33 , SYSTEM_REFERENCE
34 , TRANSFER_STATUS_CODE
35 , CUSTOMER_ID
36 , CUSTOMER_NAME
37 , CUSTOMER_NUMBER
38 , INVOICE_DATE
39 , INVOICE_STATUS
40 , INVOICE_CLASS
41 , AGREEMENT_NUM
42 , BILL_THROUGH_DATE
43 , PROJFUNC_INVTRANS_RATE_TYPE
44 , PROJFUNC_INVTRANS_RATE_DATE
45 , INV_CURRENCY_CODE
46 , CREATED_BY
47 , CREATION_DATE
48 , LAST_UPDATED_BY
49 , LAST_UPDATE_DATE
50 )
51
52 SELECT l_org_id,I.PROJECT_ID
53 , I.draft_invoice_num
54 , I.RA_INVOICE_NUMBER
55 , I.DRAFT_INVOICE_NUM_CREDITED
56 , I.SYStem_reference
57 , I.TRANSFER_STATUS_CODE
58 , I.CUSTOMER_ID
59 , C.CUSTOMER_NAME
60 , C.CUSTOMER_NUMBER
61 , I.INVOICE_DATE
62 , LK.MEANING INVOICE_STATUS_M
63 ,(select LK3.MEANING FROM PA_LOOKUPS LK3 WHERE LK3.LOOKUP_TYPE = 'INVOICE_CLASS'
64 AND LK3.LOOKUP_CODE = DECODE(ORG_INV.CANCELED_FLAG, 'Y', 'CANCEL',
65 DECODE(I.WRITE_OFF_FLAG, 'Y', 'WRITE_OFF',
66 DECODE(I.concession_flag, 'Y', 'CONCESSION',
67 DECODE(NVL(I.DRAFT_INVOICE_NUM_CREDITED, 0), 0, 'INVOICE',
68 'CREDIT_MEMO'))))
69 AND LK3.ENABLED_FLAG = 'Y'
70 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LK3.START_DATE_ACTIVE, SYSDATE- 1))
71 AND TRUNC(NVL(LK3.END_DATE_ACTIVE, SYSDATE)))
72 , A.AGREEMENT_NUM
73 , I.BILL_THROUGH_DATE
74 , PA_MULTI_CURRENCY.GET_USER_CONVERSION_TYPE(I.PROJFUNC_INVTRANS_RATE_TYPE) -- Bug 8205105
75 , I.PROJFUNC_INVTRANS_RATE_DATE
76 , I.INV_CURRENCY_CODE
77 , l_user_id
78 , l_date
79 , l_user_id
80 , l_date
81 FROM PA_DRAFT_INVOICES_ALL I
82 , PA_DRAFT_INVOICES_ALL ORG_INV
83 , PA_CUSTOMERS_V C
84 ,PA_LOOKUPS LK
85 ,PA_AGREEMENTS_ALL A
86 WHERE I.AGREEMENT_ID = A.AGREEMENT_ID
87 and I.PROJECT_ID = P_Project_Id
88 AND C.CUSTOMER_ID = I.CUSTOMER_ID
89 AND C.CUSTOMER_ID = A.CUSTOMER_ID
90 AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID
91 AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED
92 AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
93 AND LK.LOOKUP_CODE = DECODE(I.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR',
94 DECODE(I.APPROVED_DATE, NULL, 'UNAPPROVED',
95 DECODE(I.RELEASED_DATE, NULL, 'APPROVED',
96 DECODE(I.TRANSFER_STATUS_CODE,
97 'P', 'RELEASED',
98 'X', 'REJECTED IN TRANSFER',
99 'T', 'TRANSFERRED',
100 'A', 'ACCEPTED',
101 'R', 'REJECTED' ) ) ) ) ;
102
103 --Update the Bill Amount /Line Amounts for PFC,PC,INV
104
105 UPDATE PA_PWP_CUSTOMER_SUMM pwp
106 SET
107 ( PFC_BILL_AMOUNT -- Bug 7707807 re-ordered the columns
108 ,PFC_LINE_AMOUNT
109 ,PFC_OUTSTANDING_AMOUNT --Bug 8200941 Outstanding amount columns added in the query
110 ,PC_BILL_AMOUNT
111 ,PC_LINE_AMOUNT
112 ,PC_OUTSTANDING_AMOUNT
113 ,INV_BILL_AMOUNT
114 ,INV_LINE_AMOUNT
115 ,INV_OUTSTANDING_AMOUNT
116
117
118 )
119 =
120 (SELECT
121 SUM(PROJFUNC_BILL_AMOUNT) a
122 ,SUM(PROJFUNC_BILL_AMOUNT) b
123 ,SUM(PROJFUNC_BILL_AMOUNT)c
124 ,SUM(PROJECT_BILL_AMOUNT)d
125 ,SUM(PROJECT_BILL_AMOUNT)e
126 ,SUM(PROJECT_BILL_AMOUNT)f
127 ,SUM(INV_AMOUNT)g
128 ,SUM(INV_AMOUNT)h
129 ,SUM(INV_AMOUNT) i
130 FROM pa_draft_invoice_items pdii
131 WHERE pdii .project_id = pwp.project_id
132 AND pdii.draft_invoice_num = pwp.draft_invoice_num
133 )
134 WHERE project_id = P_Project_Id;
135
136 --Update columns with AR amounts .
137 --Only records of interfaced invoices will get updated
138
139 UPDATE PA_PWP_CUSTOMER_SUMM pwp
140 SET
141 (
142 INV_BILL_AMOUNT
143 , INV_OUTSTANDING_AMOUNT
144 , INV_RECIEPT_AMOUNT
145 , INV_ADJUSTMENT_AMOUNT
146 , INV_TAX_AMOUNT
147 )
148 =
149 (SELECT SUM(ARP.amount_due_original )
150 , SUM(ARP.amount_due_remaining)
151 , SUM(ARP.amount_applied)
152 , SUM(nvl(ARP.amount_credited,0)) + SUM(nvl(ARP.amount_adjusted,0)) -- Bug 7785173 Added NVL
153 , SUM(ARP.TAX_ORIGINAL) Tax
154 FROM AR_PAYMENT_SCHEDULES ARP
155 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
156 )
157 WHERE project_id = P_Project_Id
158 AND TRANSFER_STATUS_CODE = 'A';
159
160 -- Get the start and end dates of the Latest Open GL Period.
161 -- This will be the open period with highest start date
162
163
164 SELECT start_date
165 , end_date
166 INTO l_start_date
167 ,l_end_date
168 FROM gl_period_statuses GL1
169 , pa_implementations pa
170 WHERE GL1.set_of_books_id = pa.set_of_books_id
171 AND GL1.APPLICATION_ID = 101 --bug 8208525
172 AND GL1.CLOSING_STATUS = 'O'
173 AND start_date =
174 (SELECT MAX(GL2.start_date)
175 FROM gl_period_statuses GL2
176 WHERE GL2.set_of_books_id = GL1.set_of_books_id
177 AND GL2.APPLICATION_ID = 101
178 AND GL2.CLOSING_STATUS = 'O'
179 );
180
181 --Update current period Amounts
182 /*
183 UPDATE PA_PWP_CUSTOMER_SUMM pwp
184 SET
185 (
186 INV_CP_BILL_AMOUNT
187 ,INV_CP_RECIEPT_AMOUNT
188 )
189 =
190 (SELECT SUM(ARP.amount_due_original )
191 , SUM(ARP.amount_applied)
192 FROM AR_PAYMENT_SCHEDULES ARP
193 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
194 AND GL_DATE BETWEEN l_start_date AND l_end_date
195 )
196 WHERE project_id = P_Project_Id; */ -- bug 8208525 commented the code .
197
198 -- FOR UNINTERFACED INVOCIES...
199
200 UPDATE PA_PWP_CUSTOMER_SUMM pwp
201 SET ( INV_CP_BILL_AMOUNT
202 ,PFC_CP_BILL_AMOUNT
203 ,PC_CP_BILL_AMOUNT)
204 =
205 (SELECT SUM(INV_AMOUNT ) -- bug 8225160
206 ,SUM(PROJFUNC_BILL_AMOUNT)
207 ,SUM(PROJECT_BILL_AMOUNT)
208 FROM pa_draft_invoice_items pdii,
209 pa_draft_invoices_all pda
210 WHERE pwp.project_id = pda.project_id
211 AND pwp.draft_invoice_num = pda.draft_invoice_num
212 AND pda.gl_date between l_start_date AND l_end_date
213 AND pdii.project_id = pwp.project_id
214 AND pdii.draft_invoice_num = pwp.draft_invoice_num
215 AND pda.project_id = pdii.project_id
216 )
217 WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE <> 'A';
218
219 -- FOR INTERFACED INVOICES...
220
221
222 UPDATE PA_PWP_CUSTOMER_SUMM pwp
223 SET INV_CP_BILL_AMOUNT = (SELECT SUM(ARP.amount_due_original )
224
225 FROM AR_PAYMENT_SCHEDULES ARP,
226 PA_DRAFT_INVOICES PDA
227 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
228 AND ARP.CUSTOMER_TRX_ID = PDA.SYSTEM_REFERENCE
229 AND PDA.GL_DATE BETWEEN l_start_date AND l_end_date
230 )
231 WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE = 'A';
232
233 --FOR RECEIPT AMOUNTS
234
235 UPDATE PA_PWP_CUSTOMER_SUMM PWP
236 SET INV_CP_RECIEPT_AMOUNT =
237 (SELECT SUM(AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
238 WHERE
239 PWP.SYSTEM_REFERENCE = ARA.APPLIED_CUSTOMER_TRX_ID
240 AND ARA.GL_DATE BETWEEN l_start_date AND l_end_date
241 )
242 WHERE PWP.PROJECT_ID = P_PROJECT_ID AND PWP.TRANSFER_STATUS_CODE = 'A';
243
244
245
246
247 UPDATE PA_PWP_CUSTOMER_SUMM pwp
248 SET (PROJFUNC_CURRENCY_CODE ,PROJECT_CURRENCY_CODE)
249 = (select PROJFUNC_CURRENCY_CODE, PROJECT_CURRENCY_CODE
250 from pa_projects_all pa where pwp.project_id = pa.project_id )
251 Where project_id = P_Project_Id;
252
253
254 -- Updating the PC/PFC amounts by convertig the INV amount fetched from AR
255
256
257 UPDATE PA_PWP_CUSTOMER_SUMM
258 SET PFC_BILL_AMOUNT = INV_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
259 , PFC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
260 , PFC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
261 , PFC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
262 , PFC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
263 , PFC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
264 , PFC_TAX_AMOUNT = INV_TAX_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
265 , PFC_LINE_AMOUNT = INV_LINE_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
266 , PC_BILL_AMOUNT = INV_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
267 , PC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
268 , PC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
269 , PC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
270 , PC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
271 , PC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
272 , PC_TAX_AMOUNT = INV_TAX_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
273 , PC_LINE_AMOUNT = INV_LINE_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
274 WHERE project_id = P_project_id
275 AND TRANSFER_STATUS_CODE = 'A';
276
277 /* --Bug 8200941 commented
278 UPDATE PA_PWP_CUSTOMER_SUMM
279 SET PFC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
280 ,PC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
281 ,INV_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0))
282 WHERE project_id = P_project_id ;
283
284 */
285 /* bug 8200961 updating receipt amounts to 0 for credit memos. */
286
287 UPDATE PA_PWP_CUSTOMER_SUMM
288 SET PFC_RECIEPT_AMOUNT = 0
289 ,PFC_CP_RECIEPT_AMOUNT = 0
290 ,PC_RECIEPT_AMOUNT = 0
291 ,PC_CP_RECIEPT_AMOUNT = 0
292 ,INV_RECIEPT_AMOUNT = 0
293 ,INV_CP_RECIEPT_AMOUNT = 0
294 WHERE
295 DRAFT_INVOICE_NUM_CREDITED IS NOT NULL
296 AND PROJECT_ID = P_project_id ;
297
298
299
300 commit;
301
302 END Populate_summary;
303
304
305 --The function gets the AR invoice number of a Project's Draft invoice.
306 FUNCTION GET_RAINVOICE_NUM
307 (
308 P_PROJECT_ID IN NUMBER ,
309 P_DRAFT_INVOICE_NUM IN NUMBER )
310 RETURN VARCHAR2
311 IS
312 L_INVOICE_NUM VARCHAR2(30);
313 BEGIN
314 SELECT ra_invoice_number
315 INTO l_invoice_num
316 FROM pa_draft_invoices
317 WHERE project_id = p_project_id
318 AND draft_invoice_num = p_draft_invoice_num;
319 RETURN l_invoice_num;
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 RETURN NULL;
323 WHEN OTHERS THEN
324 RAISE;
325 RETURN NULL;
326 END GET_RAINVOICE_NUM;
327
328
329 --The function gets the Invoice Date of a Project's Draft invoice.
330 FUNCTION GET_LAST_INVOICE_DATE
331 (
332 P_PROJECT_ID IN NUMBER ,
333 P_DRAFT_INVOICE_NUM IN NUMBER )
334 RETURN DATE
335 IS
336 L_INVOICE_DATE DATE;
337 BEGIN
338 SELECT invoice_date
339 INTO L_INVOICE_DATE
340 FROM pa_draft_invoices
341 WHERE project_id = p_project_id
342 AND draft_invoice_num = p_draft_invoice_num;
343 RETURN L_INVOICE_DATE;
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 RETURN NULL;
347 WHEN OTHERS THEN
348 RAISE;
349 RETURN NULL;
350 END GET_LAST_INVOICE_DATE;
351
352 END PA_PWP_SUMM_PKG;
353