1 PACKAGE PAAP_PWP_PKG AUTHID CURRENT_USER AS
2 -- /* $Header: PAAPPWPS.pls 120.1.12010000.7 2009/07/31 11:11:29 jravisha noship $ */
3
4 Type InvoiceId Is Table Of AP_INVOICES_ALL.INVOICE_ID%Type Index By Binary_Integer;
5
6 ProjFunc_Currency PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE;
7 Proj_Currency PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
8 Pa_Curr_Code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE;
9 ProjFunc_Cst_Rate_Date PA_PROJECTS_ALL.PROJFUNC_BIL_RATE_DATE%TYPE;
10 ProjFunc_Cst_Rate_Type PA_PROJECTS_ALL.PROJFUNC_BIL_RATE_TYPE%TYPE;
11 ProjFunc_Cst_Rate NUMBER;
12 Proj_Cst_Rate_Date PA_PROJECTS_ALL.PROJECT_BIL_RATE_DATE%TYPE;
13 Proj_Cst_Rate_Type PA_PROJECTS_ALL.PROJECT_BIL_RATE_TYPE%TYPE;
14 Proj_Cst_Rate NUMBER;
15 Acct_Cst_Rate_Date PA_PROJECTS_ALL.PROJECT_BIL_RATE_DATE%TYPE;
16 Acct_Cst_Rate_Type PA_PROJECTS_ALL.PROJECT_BIL_RATE_TYPE%TYPE;
17 Acct_Cst_Rate NUMBER;
18 G_Project_Id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
19 G_Invoice_Id AP_INVOICES_ALL.INVOICE_ID%TYPE;
20 G_From_Curr PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE; /*Bug# 7830751*/
21
22 G_Task_Id PA_TASKS.TASK_ID%TYPE;
23 G_Expenditure_Item_Date PA_EXPENDITURE_ITEMS_ALL.EXPENDITURE_ITEM_DATE%TYPE;
24
25 G_Draft_Inv_Num PA_CUST_REV_DIST_LINES.DRAFT_INVOICE_NUM%TYPE;
26 G_InvId_Tab InvoiceId;
27
28 /*---------------------------------------------------------------------------------------------------------
29 -- This procedure populates pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl tables by processing all the supplier
30 -- invoices pertaining to the project_id being passed. Returns Success/Failure to the calling module.
31 -- Input parameters
32 -- Parameters Type Required Description
33 -- p_project_id NUMBER YES It stores the project_id
34 -- Out parameters
35 -- Parameters Type Required Description
36 -- X_return_status VARCHAR2 YES The return status of the APIs.
37 -- Valid values are:
38 -- S (API completed successfully),
39 -- E (business rule violation error) and
40 -- U(Unexpected error, such as an Oracle error.
41 -- X_msg_count NUMBER YES Holds the number of messages in the global message
42 table. Calling programs should use this as the
43 basis to fetch all the stored messages.
44 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
45 one error/warning message Otherwise the column is
46 left blank.
47 ----------------------------------------------------------------------------------------------------------*/
48 Procedure Process_SuppInv_Dtls1 (P_Project_Id IN NUMBER
49 ,X_return_status OUT NOCOPY VARCHAR2
50 ,X_msg_count OUT NOCOPY NUMBER
51 ,X_msg_data OUT NOCOPY VARCHAR2);
52
53
54
55 ---------------------------------------------------------------------------------------------------------
56 -- This procedure in turn calls Process_SuppInv_Dtls1, to populate pa_pwp_ap_inv_hdr, pa_pwp_ap_inv_dtl
57 -- tables by processing all the supplier invoices pertaining to the project_id being passed.
58 -- This is being called from the Summary Page of Subcontractor tab.
59 -- Input parameters
60 -- Parameters Type Required Description
61 -- p_project_id NUMBER YES It stores the project_id
62 -- Out parameters
63 ----------------------------------------------------------------------------------------------------------
64 Procedure Process_SuppInv_Dtls (P_Project_Id IN NUMBER,P_Draft_Inv_Num IN Number :='');
65
66
67 ---------------------------------------------------------------------------------------------------------
68 -- This function derives the all the conversion attributes from any currency passed as a parameter to
69 -- Project Functional/Project/Acct Currencies. This function returns any of the Currency /Exchange Rate/
70 -- Exchange Rate Date/Exchange Rate Type/Amount values based on the parameter p_ret_atr value.
71 -- Input parameters
72 -- Parameters Type Required Description
73 -- p_project_id NUMBER YES It stores the project_id
74 -- p_task_id NUMBER YES It stores the task_Id
75 -- p_ei_date DATE YES It stores the expenditure item date
76 -- p_from_currency VARCHAR2 NO If not passed, this will be same as the
77 -- Functional Currency
78 -- p_ret_atr VARCHAR2 NO Default value is 'ProjFunc_Rate'
79 -- Valid Values are:
80 -- ProjFunc_Rate
81 -- ProjFunc_Rate_Type
82 -- ProjFunc_Rate_Date
83 -- ProjFunc_Amt
84 -- Proj_Rate
85 -- Proj_Rate_Type
86 -- Proj_Rate_Date
87 -- Proj_Amt
88 -- Proj_Curr
89 -- ProjFunc_Curr
90 -- p_amt NUMBER NO Amount to be converted
91 -- Out parameters
92 ----------------------------------------------------------------------------------------------------------
93 Function Get_Proj_Curr_Amt (P_Project_Id IN NUMBER,
94 P_Task_Id IN NUMBER,
95 P_EI_Date IN DATE := SYSDATE,
96 P_FromCurrency IN VARCHAR2 :='',
97 P_RET_ATR IN VARCHAR2 :='ProjFunc_Rate',
98 P_Amt IN NUMBER := 0) RETURN VARCHAR2;
99
100
101
102 /*--------------------------------------------------------------------------------------------------------
103 -- This procedure releases PWP Hold, DLV Hold for the supplier invoices passed as a pl/sql table
104 -- Input parameters
105 -- Parameters Type Required Description
106 -- P_Inv_Tbl PL/SQL Tbl YES It stores a list of invoice_id's for which
107 -- the PWP/DLV Hold needs to be released.
108 -- Out parameters
109 -- Parameters Type Required Description
110 -- X_return_status VARCHAR2 YES The return status of the APIs.
111 Valid values are:
112 S (API completed successfully),
113 E (business rule violation error) and
114 U(Unexpected error, such as an Oracle error.
115 -- X_msg_count NUMBER YES Holds the number of messages in the global message
116 table. Calling programs should use this as the
117 basis to fetch all the stored messages.
118 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
119 one error/warning message Otherwise the column is
120 left blank.
121 ----------------------------------------------------------------------------------------------------------*/
122 Procedure Paap_Release_Hold (P_Inv_Tbl IN InvoiceId
123 ,p_rel_option IN VARCHAR2 := 'REL_ALL_HOLD'
124 ,X_return_status OUT NOCOPY VARCHAR2
125 ,X_msg_count OUT NOCOPY NUMBER
126 ,X_msg_data OUT NOCOPY VARCHAR2);
127
128 /*--------------------------------------------------------------------------------------------------------
129 -- This procedure applies Project Hold for the supplier invoices passed as a pl/sql table
130 -- Input parameters
131 -- Parameters Type Required Description
132 -- P_Inv_Tbl PL/SQL Tbl YES It stores a list of invoice_id's for which
133 -- the PWP/DLV Hold needs to be released.
134 -- Out parameters
135 -- Parameters Type Required Description
136 -- X_return_status VARCHAR2 YES The return status of the APIs.
137 Valid values are:
138 S (API completed successfully),
139 E (business rule violation error) and
140 U(Unexpected error, such as an Oracle error.
141 -- X_msg_count NUMBER YES Holds the number of messages in the global message
142 table. Calling programs should use this as the
143 basis to fetch all the stored messages.
144 -- x_msg_data VARCHAR2 YES Holds the message code, if the API returned only
145 one error/warning message Otherwise the column is
146 left blank.
147 ----------------------------------------------------------------------------------------------------------*/
148 Procedure Paap_Apply_Hold (P_Inv_Tbl IN InvoiceId
149 ,X_return_status OUT NOCOPY VARCHAR2
150 ,X_msg_count OUT NOCOPY NUMBER
151 ,X_msg_data OUT NOCOPY VARCHAR2);
152 END PAAP_PWP_PKG;