1 PACKAGE PA_PWP_NOTIFICATION AUTHID CURRENT_USER as
2 /* $Header: PAPWPWFS.pls 120.6 2011/11/01 05:57:37 vdharman ship $ */
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_from,ra.amount_applied),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; commented and added below for bug 8716284 */
61 pdi.ra_invoice_number = rctrx.trx_number;
62
63 CURSOR c_proj_info ( p_project_num VARCHAR2 )
64 IS
65 SELECT project_id Project_id
66 ,segment1 Project_Number
67 ,name Project_Name
68 ,start_date Start_Date
69 ,completion_date End_Date
70 ,project_type Project_Type
71 ,carrying_out_organization_id Organization_Id
72 ,ps.project_status_name Project_Status
73 FROM pa_projects p, pa_project_statuses ps
74 WHERE segment1 = p_project_num
75 and ps.project_status_code = p.project_status_code
76 and ps.status_type = 'PROJECT';
77
78 /* This subprogram will generate the html page for notification message */
79 PROCEDURE Generate_PWP_Notify_Page (p_item_type IN VARCHAR2,
80 p_item_Key IN VARCHAR2,
81 p_inv_info_rec IN c_inv_info%ROWTYPE,
82 p_proj_info_rec IN c_proj_info%ROWTYPE,
83 x_content_id OUT NOCOPY NUMBER);
84
85 /* Declaration of cursors for selecting Project Manager.
86 Used in Sub Programs : Generate_PWP_Notify_Page
87 Select_Project_Manager
88 */
89
90 CURSOR c_manager( p_manager_id NUMBER )
91 IS
92 SELECT f.user_id user_id
93 ,f.user_name user_name
94 ,e.first_name||' '||e.last_name full_name
95 FROM fnd_user f
96 ,pa_employees e
97 WHERE f.employee_id = p_manager_id
98 AND f.employee_id = e.person_id;
99
100 Cursor c_proj_manager (l_project_id NUMBER)
101 IS
102 SELECT ppp.resource_source_id manager_employee_id
103 FROM pa_project_parties ppp
104 ,per_all_people_f pe
105 WHERE ppp.project_id = l_project_id
106 AND ppp.project_role_id = 1
107 AND ppp.resource_type_id = 101
108 AND ppp.resource_source_id = pe.person_id
109 AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date AND pe.effective_end_date
110 AND ppp.object_type = 'PA_PROJECTS'
111 AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);
112
113 END PA_PWP_NOTIFICATION;
[Home] [Help]