1 PACKAGE PA_PWP_NOTIFICATION as
2 /* $Header: PAPWPWFS.pls 120.0.12010000.4 2008/11/19 14:24:29 atshukla noship $ */
3
4 /* This funtion will get a call from ar bussiness event
5 (oracle.apps.ar.applications.CashApp.apply) and will then start the Workflow. */
6 FUNCTION Receive_BE ( p_subscription_guid IN RAW
7 ,p_event IN OUT NOCOPY WF_EVENT_T ) RETURN VARCHAR2;
8
9
10 PROCEDURE START_AR_NOTIFY_WF (p_receivable_application_id IN NUMBER,
11 x_err_stack IN OUT NOCOPY VARCHAR2,
12 x_err_stage IN OUT NOCOPY VARCHAR2,
13 x_err_code OUT NOCOPY NUMBER);
14
15 PROCEDURE Select_Project_Manager (itemtype IN VARCHAR2,
16 itemkey IN VARCHAR2,
17 actid IN NUMBER,
18 funcmode IN VARCHAR2,
19 resultout OUT NOCOPY VARCHAR2 );
20
21 /* This will add HTML content to CLOB */
22 PROCEDURE APPEND_VARCHAR_TO_CLOB(p_varchar IN varchar2
23 ,p_clob IN OUT NOCOPY CLOB);
24
25 /* This Subprogram will Fetch the generated page from pa_page_contents, called from Workflow Message */
26 PROCEDURE SHOW_PWP_NOTIFY_PREVIEW (document_id IN VARCHAR2
27 ,display_type IN VARCHAR2
28 ,document IN OUT NOCOPY CLOB
29 ,document_type IN OUT NOCOPY VARCHAR2);
30
31
32
33 CURSOR c_inv_info ( l_receivable_application_id NUMBER )
34 IS
35 SELECT RA.CASH_RECEIPT_ID Receipt_id
36 ,to_char(NVL(ra.amount_applied,0),fnd_currency.get_format_mask(rcr.currency_code, 20)) amount_applied
37 ,RCR.RECEIPT_NUMBER Receipt_number
38 ,RCR.RECEIPT_DATE Receipt_Date
39 ,to_char(NVL(rcr.amount,0),fnd_currency.get_format_mask(rcr.currency_code, 20)) receipt_amount
40 ,RCR.CURRENCY_CODE Receipt_Currency_Code
41 ,RA.receivable_application_id RA_ID
42 ,pdi.ra_invoice_number AR_Invoice_No
43 ,to_char(NVL(rpsa.amount_due_original, 0), fnd_currency.get_format_mask(rpsa.invoice_currency_code, 20)) ar_invoice_amount
44 ,trunc(RPSA.TRX_DATE) AR_Invoice_Date
45 ,RPSA.INVOICE_CURRENCY_CODE AR_Invoice_Currency_Code
46 ,RCTRX.interface_header_attribute1 Project_Number
47 ,trunc(sysdate) Date_of_notification
48 ,pdi.draft_invoice_num draft_invoice_number
49 ,RPSA.Status Invoice_Status
50 FROM ar_receivable_applications RA
51 ,AR_CASH_RECEIPTS RCR
52 ,ra_customer_trx RCTRX
53 ,ar_payment_schedules RPSA
54 ,pa_draft_invoices PDI
55 WHERE RA.receivable_application_id = l_receivable_application_id and
56 RA.STATUS = 'APP' and
57 RA.CASH_RECEIPT_ID = RCR.CASH_RECEIPT_ID AND
58 RA.applied_customer_trx_id = RCTRX.customer_trx_id and
59 RPSA.customer_trx_id = RCTRX.customer_trx_id and
60 pdi.system_reference = rctrx.customer_trx_id;
61
62 CURSOR c_proj_info ( p_project_num VARCHAR2 )
63 IS
64 SELECT project_id Project_id
65 ,segment1 Project_Number
66 ,name Project_Name
67 ,start_date Start_Date
68 ,completion_date End_Date
69 ,project_type Project_Type
70 ,carrying_out_organization_id Organization_Id
71 ,project_status_code Project_Status
72 FROM pa_projects
73 WHERE segment1 = p_project_num;
74
75 /* This subprogram will generate the html page for notification message */
76 PROCEDURE Generate_PWP_Notify_Page (p_item_type IN VARCHAR2,
77 p_item_Key IN VARCHAR2,
78 p_inv_info_rec IN c_inv_info%ROWTYPE,
79 p_proj_info_rec IN c_proj_info%ROWTYPE,
80 x_content_id OUT NOCOPY NUMBER);
81
82 /* Declaration of cursors for selecting Project Manager.
83 Used in Sub Programs : Generate_PWP_Notify_Page
84 Select_Project_Manager
85 */
86
87 CURSOR c_manager( p_manager_id NUMBER )
88 IS
89 SELECT f.user_id user_id
90 ,f.user_name user_name
91 ,e.first_name||' '||e.last_name full_name
92 FROM fnd_user f
93 ,pa_employees e
94 WHERE f.employee_id = p_manager_id
95 AND f.employee_id = e.person_id;
96
97 Cursor c_proj_manager (l_project_id NUMBER)
98 IS
99 SELECT ppp.resource_source_id manager_employee_id
100 FROM pa_project_parties ppp
101 ,per_all_people_f pe
102 WHERE ppp.project_id = l_project_id
103 AND ppp.project_role_id = 1
104 AND ppp.resource_type_id = 101
105 AND ppp.resource_source_id = pe.person_id
106 AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date AND pe.effective_end_date
107 AND ppp.object_type = 'PA_PROJECTS'
108 AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);
109
110 END PA_PWP_NOTIFICATION;
[Home] [Help]