[Home] [Help]
PACKAGE BODY: APPS.PA_PWP_SUMM_PKG
Source
4 -- This procedure populates data in PA_PWP_CUSTOMER_SUMM_ALL table.
1 PACKAGE BODY PA_PWP_SUMM_PKG AS
2 -- $Header: PAPWPSMB.pls 120.19 2011/06/28 09:56:53 jjgeorge noship $
3
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 ;
14 l_org_id number(15);
11 l_end_date DATE ;
12 l_user_id number(15);
13 l_date date ;
15
16 /* bug 10061846 Added cursor to get muliplier's for PFC and PC amounts on
17 each draft_invoice_num which is having SUM(INV_AMOUNT) as 0 */
18
19 CURSOR Recdraft IS
20 SELECT (PDII.PROJFUNC_BILL_AMOUNT/PDII.INV_AMOUNT) F_amount ,(PDII.PROJECT_BILL_AMOUNT/PDII.INV_AMOUNT) P_amount,
21 PDII.draft_invoice_num Inv_num FROM
22 PA_PWP_CUSTOMER_SUMM PPCS , PA_DRAFT_INVOICE_ITEMS PDII
23 WHERE PPCS.draft_invoice_num = PDII.draft_invoice_num
24 AND PPCS.INV_LINE_AMOUNT = 0
25 and PDII.project_id = P_Project_Id
26 AND TRANSFER_STATUS_CODE = 'A'
27 and PDII.LINE_NUM =
28 (SELECT min(PDII2.LINE_NUM) from PA_DRAFT_INVOICE_ITEMS PDII2 where
29 PDII.draft_invoice_num = PDII2.draft_invoice_num AND
30 PDII2.project_id = P_Project_Id AND PDII2.INV_AMOUNT <> 0
31 group by PDII2.project_id,PDII2.draft_invoice_num);
32
33
34
35 BEGIN
36
37 DELETE
38 FROM PA_PWP_CUSTOMER_SUMM WHERE project_id = P_Project_Id ;
39 /* Bug 12534894
40 SELECT org_id
41 INTO l_org_id
42 FROM pa_implementations;
43 */
44
45 --Insert the Invoice level attributes to the temp Table
46 INSERT
47 INTO PA_PWP_CUSTOMER_SUMM
48 ( ORG_ID
49 , PROJECT_ID
50 , draft_invoice_num
51 , RA_INVOICE_NUMBER
52 , DRAFT_INVOICE_NUM_CREDITED
53 , SYSTEM_REFERENCE
54 , TRANSFER_STATUS_CODE
55 , CUSTOMER_ID
56 , CUSTOMER_NAME
57 , CUSTOMER_NUMBER
58 , INVOICE_DATE
59 , INVOICE_STATUS
60 , INVOICE_CLASS
61 , AGREEMENT_NUM
62 , BILL_THROUGH_DATE
63 , PROJFUNC_INVTRANS_RATE_TYPE
64 , PROJFUNC_INVTRANS_RATE_DATE
65 , INV_CURRENCY_CODE
66 , CREATED_BY
67 , CREATION_DATE
68 , LAST_UPDATED_BY
69 , LAST_UPDATE_DATE
70 )
71
72 SELECT I.ORG_ID,I.PROJECT_ID /* Bug 12534894 */
73 , I.draft_invoice_num
74 , I.RA_INVOICE_NUMBER
75 , I.DRAFT_INVOICE_NUM_CREDITED
76 , I.SYStem_reference
77 , I.TRANSFER_STATUS_CODE
78 , I.CUSTOMER_ID
79 , C.CUSTOMER_NAME
80 , C.CUSTOMER_NUMBER
81 , I.INVOICE_DATE
82 , LK.MEANING INVOICE_STATUS_M
83 ,(select LK3.MEANING FROM PA_LOOKUPS LK3 WHERE LK3.LOOKUP_TYPE = 'INVOICE_CLASS'
84 AND LK3.LOOKUP_CODE = DECODE(ORG_INV.CANCELED_FLAG, 'Y', 'CANCEL',
85 DECODE(I.WRITE_OFF_FLAG, 'Y', 'WRITE_OFF',
86 DECODE(I.concession_flag, 'Y', 'CONCESSION',
87 DECODE(NVL(I.DRAFT_INVOICE_NUM_CREDITED, 0), 0, 'INVOICE',
88 'CREDIT_MEMO'))))
89 AND LK3.ENABLED_FLAG = 'Y'
90 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LK3.START_DATE_ACTIVE, SYSDATE- 1))
91 AND TRUNC(NVL(LK3.END_DATE_ACTIVE, SYSDATE)))
92 , A.AGREEMENT_NUM
93 , I.BILL_THROUGH_DATE
94 , PA_MULTI_CURRENCY.GET_USER_CONVERSION_TYPE(I.PROJFUNC_INVTRANS_RATE_TYPE) -- Bug 8205105
95 , I.PROJFUNC_INVTRANS_RATE_DATE
96 , I.INV_CURRENCY_CODE
97 , l_user_id
98 , l_date
99 , l_user_id
100 , l_date
101 FROM PA_DRAFT_INVOICES_ALL I
102 , PA_DRAFT_INVOICES_ALL ORG_INV
103 , PA_CUSTOMERS_V C
104 ,PA_LOOKUPS LK
105 ,PA_AGREEMENTS_ALL A
106 WHERE I.AGREEMENT_ID = A.AGREEMENT_ID
107 and I.PROJECT_ID = P_Project_Id
108 AND C.CUSTOMER_ID = I.CUSTOMER_ID
109 AND C.CUSTOMER_ID = A.CUSTOMER_ID
110 AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID
111 AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED
112 AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
113 AND LK.LOOKUP_CODE = DECODE(I.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR',
114 DECODE(I.APPROVED_DATE, NULL, 'UNAPPROVED',
115 DECODE(I.RELEASED_DATE, NULL, 'APPROVED',
116 DECODE(I.TRANSFER_STATUS_CODE,
117 'P', 'RELEASED',
118 'X', 'REJECTED IN TRANSFER',
119 'T', 'TRANSFERRED',
120 'A', 'ACCEPTED',
121 'R', 'REJECTED' ) ) ) ) ;
122
123 --Update the Bill Amount /Line Amounts for PFC,PC,INV
124
125 UPDATE PA_PWP_CUSTOMER_SUMM pwp
126 SET
127 ( PFC_BILL_AMOUNT -- Bug 7707807 re-ordered the columns
128 ,PFC_LINE_AMOUNT
129 ,PFC_OUTSTANDING_AMOUNT --Bug 8200941 Outstanding amount columns added in the query
130 ,PC_BILL_AMOUNT
131 ,PC_LINE_AMOUNT
132 ,PC_OUTSTANDING_AMOUNT
133 ,INV_BILL_AMOUNT
134 ,INV_LINE_AMOUNT
135 ,INV_OUTSTANDING_AMOUNT
136
137
138 )
139 =
140 (SELECT
141 SUM(PROJFUNC_BILL_AMOUNT) a
142 ,SUM(PROJFUNC_BILL_AMOUNT) b
143 ,SUM(PROJFUNC_BILL_AMOUNT)c
144 ,SUM(PROJECT_BILL_AMOUNT)d
145 ,SUM(PROJECT_BILL_AMOUNT)e
146 ,SUM(PROJECT_BILL_AMOUNT)f
147 ,SUM(INV_AMOUNT)g
148 ,SUM(INV_AMOUNT)h
149 ,SUM(INV_AMOUNT) i
150 FROM pa_draft_invoice_items pdii
151 WHERE pdii .project_id = pwp.project_id
152 AND pdii.draft_invoice_num = pwp.draft_invoice_num
153 )
154 WHERE project_id = P_Project_Id;
155
156 --Update columns with AR amounts .
157 --Only records of interfaced invoices will get updated
158
159 UPDATE PA_PWP_CUSTOMER_SUMM pwp
160 SET
161 (
162 INV_BILL_AMOUNT
163 , INV_OUTSTANDING_AMOUNT
164 , INV_RECIEPT_AMOUNT
165 , INV_ADJUSTMENT_AMOUNT
166 , INV_TAX_AMOUNT
167 )
168 =
169 (SELECT SUM(ARP.amount_due_original )
170 , SUM(ARP.amount_due_remaining)
171 , SUM(ARP.amount_applied)
172 , SUM(nvl(ARP.amount_credited,0)) + SUM(nvl(ARP.amount_adjusted,0)) -- Bug 7785173 Added NVL
173 , SUM(ARP.TAX_ORIGINAL) Tax
174 FROM AR_PAYMENT_SCHEDULES ARP
175 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
176 )
177 WHERE project_id = P_Project_Id
178 AND TRANSFER_STATUS_CODE = 'A';
179
180 -- Get the start and end dates of the Latest Open GL Period.
181 -- This will be the open period with highest start date
182
183
184 /* Rewrote this query below. Bug 12534894
185 SELECT start_date
186 , end_date
187 INTO l_start_date
188 ,l_end_date
189 FROM gl_period_statuses GL1
190 , pa_implementations pa
191 WHERE GL1.set_of_books_id = pa.set_of_books_id
192 AND GL1.APPLICATION_ID = 101 --bug 8208525
193 AND GL1.CLOSING_STATUS = 'O'
194 AND start_date =
195 (SELECT MAX(GL2.start_date)
196 FROM gl_period_statuses GL2
197 WHERE GL2.set_of_books_id = GL1.set_of_books_id
198 AND GL2.APPLICATION_ID = 101
199 AND GL2.CLOSING_STATUS = 'O'
200 );*/
201
202 SELECT GL1.start_date ,
203 GL1.end_date
204 INTO l_start_date ,
205 l_end_date
206 FROM gl_period_statuses GL1 ,
207 pa_implementations pa ,
208 pa_projects ppa
209 WHERE PPA.project_id = P_Project_Id
210 AND pa.org_id = ppa.org_id
211 AND GL1.set_of_books_id = pa.set_of_books_id
212 AND GL1.APPLICATION_ID = 101
213 AND GL1.CLOSING_STATUS = 'O'
214 AND GL1.ADJUSTMENT_PERIOD_FLAG = 'N'
215 AND GL1.EFFECTIVE_PERIOD_NUM =
216 (SELECT MAX(GL2.EFFECTIVE_PERIOD_NUM)
217 FROM gl_period_statuses GL2 ,
218 pa_implementations pa2 ,
219 pa_projects ppa2
220 WHERE PPA2.project_id = P_Project_Id
221 AND pa2.org_id = ppa2.org_id
222 AND GL2.set_of_books_id = pa2.set_of_books_id
223 AND GL2.APPLICATION_ID = 101
224 AND GL2.CLOSING_STATUS = 'O'
225 AND GL2.ADJUSTMENT_PERIOD_FLAG = 'N'
226 );
227
228
229
230 --Update current period Amounts
231 /*
232 UPDATE PA_PWP_CUSTOMER_SUMM pwp
233 SET
234 (
235 INV_CP_BILL_AMOUNT
236 ,INV_CP_RECIEPT_AMOUNT
237 )
238 =
239 (SELECT SUM(ARP.amount_due_original )
240 , SUM(ARP.amount_applied)
241 FROM AR_PAYMENT_SCHEDULES ARP
242 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
243 AND GL_DATE BETWEEN l_start_date AND l_end_date
244 )
245 WHERE project_id = P_Project_Id; */ -- bug 8208525 commented the code .
246
247 -- FOR UNINTERFACED INVOCIES...
248
249 UPDATE PA_PWP_CUSTOMER_SUMM pwp
250 SET ( INV_CP_BILL_AMOUNT
251 ,PFC_CP_BILL_AMOUNT
252 ,PC_CP_BILL_AMOUNT)
253 =
254 (SELECT SUM(INV_AMOUNT ) -- bug 8225160
255 ,SUM(PROJFUNC_BILL_AMOUNT)
256 ,SUM(PROJECT_BILL_AMOUNT)
257 FROM pa_draft_invoice_items pdii,
258 pa_draft_invoices_all pda
259 WHERE pwp.project_id = pda.project_id
260 AND pwp.draft_invoice_num = pda.draft_invoice_num
261 AND pda.gl_date between l_start_date AND l_end_date
262 AND pdii.project_id = pwp.project_id
263 AND pdii.draft_invoice_num = pwp.draft_invoice_num
264 AND pda.project_id = pdii.project_id
265 )
266 WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE <> 'A';
267
268 -- FOR INTERFACED INVOICES...
269
270
271 UPDATE PA_PWP_CUSTOMER_SUMM pwp
272 SET INV_CP_BILL_AMOUNT = (SELECT SUM(ARP.amount_due_original )
273
274 FROM AR_PAYMENT_SCHEDULES ARP,
275 PA_DRAFT_INVOICES PDA
276 WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
277 AND ARP.CUSTOMER_TRX_ID = PDA.SYSTEM_REFERENCE
278 AND PDA.GL_DATE BETWEEN l_start_date AND l_end_date
279 )
280 WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE = 'A';
281
282 --FOR RECEIPT AMOUNTS
283
284 UPDATE PA_PWP_CUSTOMER_SUMM PWP
285 SET INV_CP_RECIEPT_AMOUNT =
286 (SELECT SUM(AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
287 WHERE
288 PWP.SYSTEM_REFERENCE = ARA.APPLIED_CUSTOMER_TRX_ID
289 AND ARA.GL_DATE BETWEEN l_start_date AND l_end_date
290 )
291 WHERE PWP.PROJECT_ID = P_PROJECT_ID AND PWP.TRANSFER_STATUS_CODE = 'A';
292
293
294
295
296 UPDATE PA_PWP_CUSTOMER_SUMM pwp
297 SET (PROJFUNC_CURRENCY_CODE ,PROJECT_CURRENCY_CODE)
298 = (select PROJFUNC_CURRENCY_CODE, PROJECT_CURRENCY_CODE
299 from pa_projects_all pa where pwp.project_id = pa.project_id )
300 Where project_id = P_Project_Id;
301
302
303 -- Updating the PC/PFC amounts by convertig the INV amount fetched from AR
304
305 /* bug 10061846 updating PFC and PC amount with a multiplier. */
306
307 UPDATE PA_PWP_CUSTOMER_SUMM
308 SET PFC_BILL_AMOUNT = INV_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
309 , PFC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
310 , PFC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
311 , PFC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
312 , PFC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
313 , PFC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
314 , PFC_TAX_AMOUNT = INV_TAX_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
315 , PFC_LINE_AMOUNT = INV_LINE_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
316 , PC_BILL_AMOUNT = INV_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
317 , PC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
318 , PC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
319 , PC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
320 , PC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
321 , PC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
322 , PC_TAX_AMOUNT = INV_TAX_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
323 , PC_LINE_AMOUNT = INV_LINE_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
324 WHERE project_id = P_project_id
325 AND TRANSFER_STATUS_CODE = 'A'
326 AND INV_LINE_AMOUNT <> 0;
327
328 /* --Bug 8200941 commented
329 UPDATE PA_PWP_CUSTOMER_SUMM
330 SET PFC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
331 ,PC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
332 ,INV_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0))
333 WHERE project_id = P_project_id ;
334
335 */
336 /* bug 8200961 updating receipt amounts to 0 for credit memos. */
337
338 FOR Rec in Recdraft LOOP
339
340 UPDATE PA_PWP_CUSTOMER_SUMM
341 SET PFC_BILL_AMOUNT = INV_BILL_AMOUNT * (Rec.F_amount)
342 , PFC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (Rec.F_amount)
343 , PFC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (Rec.F_amount)
344 , PFC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (Rec.F_amount)
345 , PFC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (Rec.F_amount)
346 , PFC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (Rec.F_amount)
347 , PFC_TAX_AMOUNT = INV_TAX_AMOUNT * (Rec.F_amount)
348 , PFC_LINE_AMOUNT = INV_LINE_AMOUNT * (Rec.F_amount)
349 , PC_BILL_AMOUNT = INV_BILL_AMOUNT * (Rec.P_amount)
350 , PC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (Rec.P_amount)
351 , PC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (Rec.P_amount)
352 , PC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (Rec.P_amount)
353 , PC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (Rec.P_amount)
354 , PC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (Rec.P_amount)
355 , PC_TAX_AMOUNT = INV_TAX_AMOUNT * (Rec.P_amount)
356 , PC_LINE_AMOUNT = INV_LINE_AMOUNT * (Rec.P_amount)
357 WHERE project_id = P_project_id
358 AND TRANSFER_STATUS_CODE = 'A'
359 AND draft_invoice_num = Rec.Inv_num ;
360
361 END LOOP;
362
363 /* End of bug 10061846 changes. */
364
365 UPDATE PA_PWP_CUSTOMER_SUMM
366 SET PFC_RECIEPT_AMOUNT = 0
367 ,PFC_CP_RECIEPT_AMOUNT = 0
368 ,PC_RECIEPT_AMOUNT = 0
369 ,PC_CP_RECIEPT_AMOUNT = 0
370 ,INV_RECIEPT_AMOUNT = 0
371 ,INV_CP_RECIEPT_AMOUNT = 0
372 WHERE
373 DRAFT_INVOICE_NUM_CREDITED IS NOT NULL
374 AND PROJECT_ID = P_project_id ;
375
376
377 /*Bug#:7834036 sosharma added update the personalizable columns*/
378 UPDATE PA_PWP_CUSTOMER_SUMM pwp
379 SET (APPROVED_DATE
380 ,GL_DATE
381 ,RELEASED_DATE)
382 =
383 (SELECT pda.APPROVED_DATE
384 , pda.GL_DATE
385 , pda.RELEASED_DATE
386
387 FROM pa_draft_invoices_all pda
388
389 WHERE pwp.project_id = pda.project_id
390 AND pwp.draft_invoice_num = pda.draft_invoice_num
391
392 )
393 WHERE PWP.project_id = P_Project_Id ;
394
395 UPDATE PA_PWP_CUSTOMER_SUMM pwp
396 SET (BILL_TO_CUST_NUMBER,
397 BILL_TO_CUST_NAME,
398 SHIP_TO_CUST_NUMBER,
399 SHIP_TO_CUST_NAME
400 )
401 =
402 (select bill_c.customer_number,
403 bill_c.customer_name,
404 ship_c.customer_number,
405 ship_c.customer_name
406 from
407 pa_draft_invoices_all pda,
408 pa_customers_v bill_c,
409 pa_customers_v ship_c
410
411 where
412 bill_c.customer_id (+) = pda.bill_to_customer_id
413 AND ship_c.customer_id (+) = pda.ship_to_customer_id
414 AND pwp.project_id = pda.project_id
415 AND pwp.draft_invoice_num = pda.draft_invoice_num)
416
417 WHERE PWP.project_id = P_Project_Id ;
418
419 /* commented for bug 9671344 and new query added below .
420 UPDATE PA_PWP_CUSTOMER_SUMM pwp
421 set(bill_to_address, ship_to_address) =
422 (select
423 hz_format_pub.format_address_lov(
424 bill_p.address1 ,
425 bill_p.address2,
426 bill_p.address3,
427 bill_p.address4 ,
428 bill_p.city ,
429 bill_p.postal_code ,
430 bill_p.state,
431 bill_p.province,
432 bill_p.county,
433 bill_p.country,
434 bill_p.address_lines_phonetic
435 ) bill_to_address,
436 hz_format_pub.format_address_lov(
437 ship_p.address1 ,
438 ship_p.address2,
439 ship_p.address3,
440 ship_p.address4 ,
441 ship_p.city ,
442 ship_p.postal_code ,
443 ship_p.state,
444 ship_p.province,
445 ship_p.county,
446 ship_p.country,
447 ship_p.address_lines_phonetic
448 ) ship_to_address
449
450 from
451 pa_draft_invoices_all pda,
452 RA_ADDRESSES_ALL bill_p,
453 RA_ADDRESSES_ALL ship_p
454 where
455 bill_p.address_id(+) = pda.bill_to_address_id
456 AND ship_p.address_id (+) = pda.ship_to_address_id
457 AND pwp.project_id = pda.project_id
458 AND pwp.draft_invoice_num = pda.draft_invoice_num)
459 WHERE PWP.project_id = P_Project_Id ;
460
461 */
462
463 UPDATE PA_PWP_CUSTOMER_SUMM_ALL pwp
464 SET
465 ( bill_to_address,
466 ship_to_address
467 )
468 =
469 (SELECT ARP_ADDR_PKG.FORMAT_ADDRESS ( ba_loc.ADDRESS_STYLE ,ba_loc.ADDRESS1 ,ba_loc.ADDRESS2
470 ,ba_loc.ADDRESS3 ,ba_loc.ADDRESS4 ,ba_loc.CITY ,ba_loc.COUNTY ,ba_loc.STATE ,
471 ba_loc.PROVINCE ,ba_loc.POSTAL_CODE ,TERR_B.TERRITORY_SHORT_NAME )
472 BILL_CONCATENATED_ADDRESS,
473 ARP_ADDR_PKG.FORMAT_ADDRESS ( sa_loc.ADDRESS_STYLE ,sa_loc.ADDRESS1 ,sa_loc.ADDRESS2
474 ,sa_loc.ADDRESS3 ,sa_loc.ADDRESS4 ,sa_loc.CITY ,sa_loc.COUNTY ,sa_loc.STATE ,
475 sa_loc.PROVINCE ,sa_loc.POSTAL_CODE ,TERR_S.TERRITORY_SHORT_NAME )
476 SHIP_CONCATENATED_ADDRESS
477 FROM FND_TERRITORIES_TL TERR_B ,
478 FND_TERRITORIES_TL TERR_S ,
479 PA_DRAFT_INVOICES_ALL PDA ,
480 hz_cust_acct_sites sa_acct_site ,
481 hz_party_sites sa_party_site ,
482 hz_locations sa_loc ,
483 hz_locations ba_loc ,
484 hz_party_sites ba_party_site ,
485 hz_cust_acct_sites ba_acct_site
486 WHERE pda.project_id = pwp.project_id
487 AND pda.draft_invoice_num = pwp.draft_invoice_num
488 AND sa_loc.COUNTRY = TERR_S.TERRITORY_CODE(+)
489 AND ba_loc.COUNTRY = TERR_B.TERRITORY_CODE(+)
490 AND TERR_B.LANGUAGE =USERENV('LANG')
491 AND TERR_S.LANGUAGE =USERENV('LANG')
492 AND ba_acct_site.cust_acct_site_id(+) = pda.bill_to_address_id
493 AND ba_acct_site.party_site_id = ba_party_site.party_site_id(+)
494 AND ba_party_site.location_id = ba_loc.location_id(+)
495 AND sa_acct_site.cust_acct_site_id(+) = pda.Ship_to_address_id
496 AND sa_acct_site.party_site_id = sa_party_site.party_site_id (+)
497 AND sa_party_site.location_id = sa_loc.location_id(+)
498 )
499 WHERE PWP.project_id = P_Project_Id ;
500
501
502 /*sosharma end change*/
503 commit;
504
505 END Populate_summary;
506
507
508 --The function gets the AR invoice number of a Project's Draft invoice.
509 FUNCTION GET_RAINVOICE_NUM
510 (
511 P_PROJECT_ID IN NUMBER ,
512 P_DRAFT_INVOICE_NUM IN NUMBER )
513 RETURN VARCHAR2
514 IS
515 L_INVOICE_NUM VARCHAR2(30);
516 BEGIN
517 SELECT ra_invoice_number
518 INTO l_invoice_num
519 FROM pa_draft_invoices
520 WHERE project_id = p_project_id
521 AND draft_invoice_num = p_draft_invoice_num;
522 RETURN l_invoice_num;
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 RETURN NULL;
526 WHEN OTHERS THEN
527 RAISE;
528 RETURN NULL;
529 END GET_RAINVOICE_NUM;
530
531
532 --The function gets the Invoice Date of a Project's Draft invoice.
533 FUNCTION GET_LAST_INVOICE_DATE
534 (
535 P_PROJECT_ID IN NUMBER ,
536 P_DRAFT_INVOICE_NUM IN NUMBER )
537 RETURN DATE
538 IS
539 L_INVOICE_DATE DATE;
540 BEGIN
541 SELECT invoice_date
542 INTO L_INVOICE_DATE
543 FROM pa_draft_invoices
544 WHERE project_id = p_project_id
545 AND draft_invoice_num = p_draft_invoice_num;
546 RETURN L_INVOICE_DATE;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 RETURN NULL;
550 WHEN OTHERS THEN
551 RAISE;
552 RETURN NULL;
553 END GET_LAST_INVOICE_DATE;
554
555 END PA_PWP_SUMM_PKG;
556