1 PACKAGE PA_IC_INV_UTILS as
2 /* $Header: PAICUTLS.pls 120.1 2005/08/19 16:34:45 mwasowic noship $ */
3
4 G_LAST_UPDATE_LOGIN NUMBER;
5 G_REQUEST_ID NUMBER;
6 G_PROGRAM_APPLICATION_ID NUMBER;
7 G_PROGRAM_ID NUMBER;
8 G_LAST_UPDATED_BY NUMBER;
9 G_CREATED_BY NUMBER;
10 G_DEBUG_MODE VARCHAR2(1);
11
12 -- Package specification for utilities to be used in Intercompany
13 -- Invoice generation process
14 -- This procedure will initialize the global variables
15 -- Input paramaters
16 -- Parameter Type Required Description
17 -- P_LAST_UPDATE_LOGIN NUMBER Yes Standard Who column
18 -- P_REQUEST_ID NUMBER Yes
19 -- P_PROGRAM_APPLICATION_ID NUMBER Yes
20 -- P_PROGRAM_ID NUMBER Yes
21 -- P_LAST_UPDATED_BY NUMBER Yes
22 -- P_CREATED_BY NUMBER Yes
23 -- P_DEBUG_MODE VARCHAR2 Yes Debug mode
24 -- P_SOB NUMBER Yes Set of books id
25 -- P_ORG NUMBER Yes Org Id
26 -- P_FUNC_CURR VARCHAR2 Yes Functional currency code
27 PROCEDURE Init (
28 P_LAST_UPDATE_LOGIN NUMBER,
29 P_REQUEST_ID NUMBER,
30 P_PROGRAM_APPLICATION_ID NUMBER,
31 P_PROGRAM_ID NUMBER,
32 P_LAST_UPDATED_BY NUMBER,
33 P_CREATED_BY NUMBER,
34 P_DEBUG_MODE VARCHAR2,
35 P_SOB NUMBER,
36 P_ORG NUMBER,
37 P_FUNC_CURR VARCHAR2
38 ) ;
39 --
40 -- This procedure will return the next draft invoice number to be used
41 -- for creating a new invoice header.
42 --
43 -- Input parameters
44 -- Parameter Type Required Description
45 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
46 -- P_REQUEST_ID NUMBER Yes The current request id
47 --
48 -- Output Parameters
49 -- Parameter Type Description
50 -- X_NEW_INVOICE_NUM NUMBER Invoice number to be used for the new Invoice
51 --
52 PROCEDURE Get_Next_Draft_Inv_Num
53 ( P_project_id IN NUMBER,
54 P_request_id IN NUMBER,
55 X_new_invoice_num OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
56 -- This procedure will commit the invoice transaction
57 --
58 -- There are no parameters to this procedure
59 --
60 PROCEDURE Commit_Invoice ;
61
62 -- This procedure will update the summary project fundings with
63 -- the invoiced amounts
64 --
65 -- Input parameters
66 -- Parameter Type Required Description
67 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
68 -- P_AGREEMENT_ID NUMBER Yes Identifier of the Agreement
69 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
70 -- P_INVOICE_MODE VARCHAR2 Yes Identifier of the mode of
71 -- the invoice process
72 -- Values
73 -- 'CANCEL','INVOICE','CREDIT_MEMO',
74 -- 'DELETE'
75 PROCEDURE Update_SPF
76 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
77 P_AGREEMENT_ID IN NUMBER ,
78 P_PROJECT_ID IN NUMBER ,
79 P_INVOICE_MODE IN VARCHAR2);
80 --
81 -- This procedure will mark the generation error on the draft invoice
82 -- and insert the distribution warnings
83 --
84 -- Input parameters
85 -- Parameter Type Required Description
86 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
87 -- P_REJN_LOOKUP_TYPE VARCHAR Yes The lookup type to be used to
88 -- get the rejection reason
89 -- P_REJN_LOOKUP_CODE VARCHAR Yes The lookup type to be used to
90 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
91 -- get the rejection code
92 PROCEDURE Mark_Inv_Error
93 ( P_DRAFT_INVOICE_NUM IN NUMBER,
94 P_REJN_LOOKUP_TYPE IN VARCHAR,
95 P_REJN_LOOKUP_CODE IN VARCHAR,
96 P_PROJECT_ID IN NUMBER);
97 --
98 -- This procedure will mark the expenditure items billed on an invoice as
99 -- billed.
100 --
101 -- Input parameters
102 -- Parameter Type Required Description
103 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
104 -- P_REQUEST_ID NUMBER Yes The current request id
105 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
106 --
107 PROCEDURE Mark_EI_as_Billed
108 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
109 P_REQUEST_ID IN NUMBER ,
110 P_PROJECT_ID IN NUMBER);
111 --
112 -- This function will set and acquire the user lock
113 --
114 -- Input parameters
115 -- Parameter Type Required Description
116 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
117 --
118 -- Return Values
119 -- 0 Success
120 -- Other Unable to acquire lock
121 --
122 FUNCTION Set_User_Lock
123 (P_PROJECT_ID IN NUMBER) RETURN NUMBER ;
124 --
125 -- This procedure will release user lock
126 --
127 -- Input parameters
128 -- Parameter Type Required Description
129 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
130 --
131 -- Return Values
132 -- 0 Success
133 -- Other Unable to acquire lock
134 --
135 FUNCTION Release_User_Lock
136 (P_PROJECT_ID IN NUMBER) RETURN NUMBER ;
137 --
138 -- This Function will return 'Y' if unreleased invoices exist for a project
139 --
140 -- Input parameters
141 -- Parameter Type Required Description
142 -- P_BILL_BY_PROJECT VARCHAR Yes The draft invoice number
143 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
144 --
145 FUNCTION CHECK_IF_UNRELEASED_INVOICE
146 ( P_BILL_BY_PROJECT IN VARCHAR ,
147 P_PROJECT_ID IN NUMBER) RETURN VARCHAR;
148
149 --
150 -- This procedure will update the draft invoice to trigger MRC
151 --
152 -- Input parameters
153 -- Parameter Type Required Description
154 -- P_DRAFT_INVOICE_NUM NUMBER Yes The draft invoice number
155 -- P_REQUEST_ID NUMBER Yes The current request id
156 -- P_PROJECT_ID NUMBER Yes Identifier of the Project
157 --
158 PROCEDURE Update_DI_for_MRC
159 ( P_DRAFT_INVOICE_NUM IN NUMBER ,
160 P_REQUEST_ID IN NUMBER ,
161 P_PROJECT_ID IN NUMBER);
162 --
163 -- This procedure will return active bill_to_site_use_id and
164 -- ship_to_site_id
165 -- Input parameters
166 -- Parameter Type Required
167 -- P_BILL_TO_ADDRESS_ID NUMBER Yes
168 -- P_SHIP_TO_ADDRESS_ID NUMBER Yes
169 -- P_NO_OF_RECORDS NUMBER Yes
170 -- P_CUST_CREDIT_HOLD VARCHAR2 Yes
171 -- X_BILL_TO_SITE_USE_ID NUMBER
172 -- X_SHIP_TO_SITE_USE_ID NUMBER
173 -- X_BILL_TO_SITE_STATUS VARCHAR2
174 -- X_SHIP_TO_SITE_STATUS VARCHAR2
175 --
176 PROCEDURE Get_active_sites
177 ( P_BILL_TO_ADDRESS_ID IN PA_PLSQL_DATATYPES.IdTabTyp ,
178 P_SHIP_TO_ADDRESS_ID IN PA_PLSQL_DATATYPES.IdTabTyp ,
179 P_NO_OF_RECORDS IN NUMBER,
180 P_CUST_CREDIT_HOLD IN OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
181 X_BILL_TO_SITE_USE_ID OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
182 X_SHIP_TO_SITE_USE_ID OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
183 X_BILL_TO_SITE_STATUS OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp,
184 X_SHIP_TO_SITE_STATUS OUT NOCOPY PA_PLSQL_DATATYPES.Char1TabTyp);
185
186
187 -- This procedure will write the message in the PL SQL log file
188 -- p_log_msg VARCHAR2 Yes
189 PROCEDURE log_message (p_log_msg IN VARCHAR2);
190 --
191 end PA_IC_INV_UTILS;