[Home] [Help]
1: PACKAGE BODY PA_PWP_SUMM_PKG AS
2: -- $Header: PAPWPSMB.pls 120.19 2011/06/28 09:56:53 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)
1: PACKAGE BODY PA_PWP_SUMM_PKG AS
2: -- $Header: PAPWPSMB.pls 120.19 2011/06/28 09:56:53 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
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'
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
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
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
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
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: =
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
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
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;
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)
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 ;
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)
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
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: =
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
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 ,
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: )