1 PACKAGE GMS_TRANSACTIONS AUTHID CURRENT_USER AS
2 /* $Header: GMSTRANS.pls 120.1 2005/07/26 14:38:47 appldev ship $ */
3
4 /*----------------------------------------------------------------------------
5 The following two PL/SQL tables are used by the triggers on
6 PA_TRANSACTION_INTERFACE in order to workaround the "mutating table"
7 restriction for triggers. These triggers are part of the Transaction
8 Import utility. For more information on exactly how TI uses these
9 PL/SQL tables and how the workaround functions, refer to the following
10 document:
11 $pa/designs/transaction/trx_import/mutating_table_trigger_workaround.doc
12 ------------------------------------------------------------------------------*/
13 TrxSrcTab pa_utils.Char30TabTyp;
14 BatchNameTab pa_utils.Char30TabTyp;
15
16
17 /*----------------------------------------------------------------------------
18 The following PL/SQL tables are used by several programs including
19 Transaction Import and the Transfer prodedure of the PA Adjustments
20 package. These PL/SQL tables are loaded with VALIDATED expenditure items
21 and then inserted into PA_EXPENDITURE_ITEMS using the
22 gms_transactions.InsItems procedure. This method allows the programs to
23 store validated items one at a time until all items are validated. The
24 items can then either be inserted into PA_EXPENDITURE_ITEMS with one call
25 to gms_transactions.InsItems or can be rolled back using the
26 gms_transactions.FlushEiTabs procedure without having to execute any DDL
27 statements.
28 ------------------------------------------------------------------------------*/
29
30 EiIdTab pa_utils.IdTabTyp;
31 EIdTab pa_utils.IdTabTyp;
32 ProjIdTab pa_utils.IdTabtyp;
33 TskIdTab pa_utils.IdTabTyp;
34 EiDateTab pa_utils.DateTabTyp;
35 ETypTab pa_utils.Char30TabTyp;
36 NlRscTab pa_utils.Char20TabTyp;
37 NlRscOrgTab pa_utils.IdTabTyp;
38 BillFlagTab pa_utils.Char1TabTyp;
39 BillHoldTab pa_utils.Char1TabTyp;
40 QtyTab pa_utils.AmtTabTyp;
41 RawCostTab pa_utils.AmtTabTyp;
42 RawRateTab pa_utils.AmtTabTyp;
43 OvrOrgTab pa_utils.IdTabTyp;
44 AdjEiTab pa_utils.IdTabTyp;
45 TrxRefTab pa_utils.Char30TabTyp;
46 EiTrxSrcTab pa_utils.Char30TabTyp;
47 AttCatTab pa_utils.Char30TabTyp;
48 Att1Tab pa_utils.Char150TabTyp;
49 Att2Tab pa_utils.Char150TabTyp;
50 Att3Tab pa_utils.Char150TabTyp;
51 Att4Tab pa_utils.Char150TabTyp;
52 Att5Tab pa_utils.Char150TabTyp;
53 Att6Tab pa_utils.Char150TabTyp;
54 Att7Tab pa_utils.Char150TabTyp;
55 Att8Tab pa_utils.Char150TabTyp;
56 Att9Tab pa_utils.Char150TabTyp;
57 Att10Tab pa_utils.Char150TabTyp;
58 SrcEiTab pa_utils.IdTabTyp;
59 EiCommentTab pa_utils.Char240TabTyp;
60 TfrEiTab pa_utils.IdTabTyp;
61 JobIdTab pa_utils.IdTabTyp;
62 OrgIdTab pa_utils.IdTabTyp;
63 LCMTab pa_utils.Char20TabTyp ;
64 DrccidIdTab pa_utils.IdTabTyp ;
65 CrccidIdTab pa_utils.IdTabTyp ;
66 Cdlsr1Tab pa_utils.Char30TabTyp ;
67 Cdlsr2Tab pa_utils.Char30TabTyp ;
68 Cdlsr3Tab pa_utils.Char30TabTyp ;
69 GldateTab pa_utils.DateTabTyp ;
70 BCostTab pa_utils.AmtTabTyp;
71 BCostRateTab pa_utils.AmtTabTyp;
72 EtypeClassTab pa_utils.Char30TabTyp ;
73 BurdenDestId pa_utils.IdTabTyp;
74 BurdenCompSetId pa_utils.IdTabTyp;
75 ReceiptCurrAmt pa_utils.NewAmtTabTyp;
76 ReceiptCurrCode pa_utils.Char15TabTyp;
77 ReceiptExRate pa_utils.NewAmtTabTyp;
78 DenomCurrCode pa_utils.Char15TabTyp;
79 DenomRawCost pa_utils.NewAmtTabTyp;
80 DenomBurdenCost pa_utils.NewAmtTabTyp;
81 AcctCurrCode pa_utils.Char15TabTyp;
82 AcctRateDate pa_utils.DateTabTyp;
83 AcctRateType pa_utils.Char30TabTyp;
84 AcctExRate pa_utils.NewAmtTabTyp;
85 AcctRawCost pa_utils.NewAmtTabTyp;
86 AcctBurdenCost pa_utils.NewAmtTabTyp;
87 AcctRoundLmt pa_utils.NewAmtTabTyp;
88 ProjCurrCode pa_utils.Char15TabTyp;
89 ProjRateType pa_utils.Char30TabTyp;
90 ProjRateDate pa_utils.DateTabTyp;
91 ProjExRate pa_utils.NewAmtTabTyp;
92
93 -- IC Changes
94 CrossChargeTypeTab pa_utils.Char10TabTyp;
95 CrossChargeCodeTab pa_utils.Char1TAbTyp;
96 PrvdrOrganizationTab pa_utils.IdTabTyp;
97 RecvOrganizationTab pa_utils.IdTabTyp;
98 RecvOperUnitTab pa_utils.IdTabTyp;
99 IcProcessedCodeTab pa_utils.Char1TAbTyp;
100 BorrowLentCodeTab pa_utils.Char1TabTyp;
101 DenomTpCurrCodeTab pa_utils.Char15TabTyp;
102 DenomTransferPriceTab pa_utils.NewAmtTabTyp;
103 AcctTpRateTypeTab pa_utils.Char30TabTyp;
104 AcctTpRateDateTab pa_utils.DateTabTyp;
105 AcctTpExchangeRateTab pa_utils.NewAmtTabTyp;
106 AcctTransferPriceTab pa_utils.NewAmtTabTyp;
107 ProjacctTransferPriceTab pa_utils.NewAmtTabTyp;
108 CcMarkupBaseCodeTab pa_utils.Char1TAbTyp;
109 TpBaseAmountTab pa_utils.NewAmtTabTyp;
110 TpIndCompiledSetIdTab pa_utils.IdTabTyp;
111 TpBillRateTab pa_utils.NewAmtTabTyp;
112 TpBillMarkupPercentageTab pa_utils.AmtTabTyp;
113 TpSchLinePercentageTab pa_utils.AmtTabTyp;
114 TpRulePercentageTab pa_utils.AmtTabTyp;
115 -- END IC Changes
116
117
118 -- ========================================================================
119 -- PROCEDURE LoadEi
120 -- ========================================================================
121
122 PROCEDURE LoadEi( X_expenditure_item_id IN NUMBER
123 , X_expenditure_id IN NUMBER
124 , X_expenditure_item_date IN DATE
125 , X_project_id IN NUMBER
126 , X_task_id IN NUMBER
127 , X_expenditure_type IN VARCHAR2
128 , X_non_labor_resource IN VARCHAR2
129 , X_nl_resource_org_id IN NUMBER
130 , X_quantity IN NUMBER
131 , X_raw_cost IN NUMBER
132 , X_raw_cost_rate IN NUMBER
133 , X_override_to_org_id IN NUMBER
134 , X_billable_flag IN VARCHAR2
135 , X_bill_hold_flag IN VARCHAR2
136 , X_orig_transaction_ref IN VARCHAR2
137 , X_transferred_from_ei IN NUMBER
138 , X_adj_expend_item_id IN NUMBER
139 , X_attribute_category IN VARCHAR2
140 , X_attribute1 IN VARCHAR2
141 , X_attribute2 IN VARCHAR2
142 , X_attribute3 IN VARCHAR2
143 , X_attribute4 IN VARCHAR2
144 , X_attribute5 IN VARCHAR2
145 , X_attribute6 IN VARCHAR2
146 , X_attribute7 IN VARCHAR2
147 , X_attribute8 IN VARCHAR2
148 , X_attribute9 IN VARCHAR2
149 , X_attribute10 IN VARCHAR2
150 , X_ei_comment IN VARCHAR2
151 , X_transaction_source IN VARCHAR2
152 , X_source_exp_item_id IN NUMBER
153 , i IN BINARY_INTEGER
154 , X_job_id IN NUMBER default null
155 , X_org_id IN NUMBER default null
156 , X_labor_cost_multiplier_name IN VARCHAR2
157 , X_drccid IN NUMBER
158 , X_crccid IN NUMBER
159 , X_cdlsr1 IN VARCHAR2
160 , X_cdlsr2 IN VARCHAR2
161 , X_cdlsr3 IN VARCHAR2
162 , X_gldate IN DATE
163 , X_bcost IN NUMBER
164 , X_bcostrate IN NUMBER
165 , X_etypeclass IN VARCHAR2
166 , X_burden_sum_dest_run_id IN NUMBER default null
167 , X_burden_compile_set_id IN NUMBER default null
168 , X_receipt_currency_amount IN NUMBER default null
169 , X_receipt_currency_code IN VARCHAR2 default NULL
170 , X_receipt_exchange_rate IN NUMBER default NULL
171 , X_denom_currency_code IN VARCHAR2 default NULL
172 , X_denom_raw_cost IN NUMBER default NULL
173 , X_denom_burdened_cost IN NUMBER default NULL
174 , X_acct_currency_code IN VARCHAR2 default NULL
175 , X_acct_rate_date IN DATE default NULL
176 , X_acct_rate_type IN VARCHAR2 default NULL
177 , X_acct_exchange_rate IN NUMBER default NULL
178 , X_acct_raw_cost IN NUMBER default NULL
179 , X_acct_burdened_cost IN NUMBER default NULL
180 , X_acct_exchange_rounding_limit IN NUMBER default NULL
181 , X_project_currency_code IN VARCHAR2 default NULL
182 , X_project_rate_date IN DATE default NULL
183 , X_project_rate_type IN VARCHAR2 default NULL
184 , X_project_exchange_rate IN NUMBER default NULL
185 , X_Cross_Charge_Type IN Varchar2 default 'NO'
186 , X_Cross_Charge_Code IN Varchar2 default 'P'
187 , X_Prvdr_organization_id IN Number default NULL
188 , X_Recv_organization_id IN Number default NULL
189 , X_Recv_Operating_Unit IN Number default NULL
190 , X_Borrow_Lent_Dist_Code IN Varchar2 default 'X'
191 , X_Ic_Processed_Code IN Varchar2 default 'X'
192 , X_Denom_Tp_Currency_Code IN Varchar2 default NULL
193 , X_Denom_Transfer_Price IN Number default NULL
194 , X_Acct_Tp_Rate_Type IN Varchar2 default NULL
195 , X_Acct_Tp_Rate_Date IN DATE default NULL
196 , X_Acct_Tp_Exchange_Rate IN Number default NULL
197 , X_ACCT_TRANSFER_PRICE IN Number default NULL
198 , X_PROJACCT_TRANSFER_PRICE IN Number default NULL
199 , X_CC_MARKUP_BASE_CODE IN Varchar2 default NULL
200 , X_TP_BASE_AMOUNT IN Number default NULL
201 , X_Tp_Ind_Compiled_Set_Id IN Number default NULL
202 , X_Tp_Bill_Rate IN Number default NULL
203 , X_Tp_Bill_Markup_Percentage IN Number default NULL
204 , X_Tp_Schedule_Line_Percentage IN Number default NULL
205 , X_Tp_Rule_Percentage IN Number default NULL );
206
207
208
209 -- ========================================================================
210 -- PROCEDURE FlushEiTabs
211 -- ========================================================================
212
213 PROCEDURE FlushEiTabs;
214
215
216 -- ========================================================================
217 -- PROCEDURE InsItemComment
218 -- ========================================================================
219 /*
220 PROCEDURE InsItemComment( X_ei_id IN NUMBER
221 , X_ei_comment IN VARCHAR2
222 , X_user IN NUMBER
223 , X_login IN NUMBER
227 -- ========================================================================
224 , X_status OUT NOCOPY NUMBER );
225
226 */
228 -- PROCEDURE InsItems
229 -- ========================================================================
230
231 PROCEDURE InsItems( X_user IN NUMBER
232 , X_login IN NUMBER
233 , X_module IN VARCHAR2
234 , X_calling_process IN VARCHAR2
235 , Rows IN BINARY_INTEGER
236 , X_status OUT NOCOPY NUMBER
237 , X_gl_flag IN VARCHAR2 );
238
239
240 -- ========================================================================
241 -- PROCEDURE InsertExp
242 -- ========================================================================
243 -- Added new multi-Currency parameters to GMS_ENCUMBRANCES
244 --
245
246 PROCEDURE InsertExp( X_expenditure_id IN NUMBER
247 , X_expend_status IN VARCHAR2
248 , X_expend_ending IN DATE
249 , X_expend_class IN VARCHAR2
250 , X_inc_by_person IN NUMBER
251 , X_inc_by_org IN NUMBER
252 , X_expend_group IN VARCHAR2
253 , X_entered_by_id IN NUMBER
254 , X_created_by_id IN NUMBER
255 , X_attribute_category IN VARCHAR2 DEFAULT NULL
256 , X_attribute1 IN VARCHAR2 DEFAULT NULL
257 , X_attribute2 IN VARCHAR2 DEFAULT NULL
258 , X_attribute3 IN VARCHAR2 DEFAULT NULL
259 , X_attribute4 IN VARCHAR2 DEFAULT NULL
260 , X_attribute5 IN VARCHAR2 DEFAULT NULL
261 , X_attribute6 IN VARCHAR2 DEFAULT NULL
262 , X_attribute7 IN VARCHAR2 DEFAULT NULL
263 , X_attribute8 IN VARCHAR2 DEFAULT NULL
264 , X_attribute9 IN VARCHAR2 DEFAULT NULL
265 , X_attribute10 IN VARCHAR2 DEFAULT NULL
266 , X_description IN VARCHAR2 DEFAULT NULL
267 , X_control_total IN NUMBER DEFAULT NULL
268 , X_denom_currency_code IN VARCHAR2 DEFAULT NULL
269 , X_acct_currency_code IN VARCHAR2 DEFAULT NULL
270 , X_acct_rate_type IN VARCHAR2 DEFAULT NULL
271 , X_acct_rate_date IN DATE DEFAULT NULL
272 , X_acct_exchange_rate IN NUMBER DEFAULT NULL
273 -- Trx_import enhancement:
274 -- New parameters used to populate GMS_ENCUMBRANCES_ALL
275 -- table's new columns
276 , X_orig_exp_txn_reference1 IN VARCHAR2 DEFAULT NULL
277 , X_orig_user_exp_txn_reference IN VARCHAR2 DEFAULT NULL
278 , X_vendor_id IN NUMBER DEFAULT NULL
279 , X_orig_exp_txn_reference2 IN VARCHAR2 DEFAULT NULL
280 , X_orig_exp_txn_reference3 IN VARCHAR2 DEFAULT NULL
281 );
282
283 -- ========================================================================
284 -- PROCEDURE InsertExpGroup
285 -- ========================================================================
286
287 PROCEDURE InsertExpGroup(
288 X_expenditure_group IN VARCHAR2
289 , X_exp_group_status_code IN VARCHAR2
290 , X_ending_date IN DATE
291 , X_system_linkage IN VARCHAR2
292 , X_created_by IN NUMBER
293 , X_transaction_source IN VARCHAR2 );
294
295
296 -- ========================================================================
297 -- PROCEDURE CreateRelatedItem
298 -- ========================================================================
299 -- Changed the parameter name from raw_cost to denom_raw_cost and
300 -- raw_cost_rate to denom_cost_rate
301 --
302 PROCEDURE CreateRelatedItem( X_source_exp_item_id IN NUMBER
303 , X_project_id IN NUMBER DEFAULT NULL
304 , X_task_id IN NUMBER DEFAULT NULL
305 , X_expenditure_type IN VARCHAR2
306 , X_denom_raw_cost IN NUMBER
307 , X_denom_raw_cost_rate IN NUMBER
308 , X_override_to_org_id IN NUMBER
309 , X_userid IN NUMBER
310 , X_attribute_category IN VARCHAR2
311 , X_attribute1 IN VARCHAR2
312 , X_attribute2 IN VARCHAR2
313 , X_attribute3 IN VARCHAR2
314 , X_attribute4 IN VARCHAR2
315 , X_attribute5 IN VARCHAR2
316 , X_attribute6 IN VARCHAR2
317 , X_attribute7 IN VARCHAR2
318 , X_attribute8 IN VARCHAR2
319 , X_attribute9 IN VARCHAR2
320 , X_attribute10 IN VARCHAR2
321 , X_comment IN VARCHAR2
322 , X_status OUT NOCOPY NUMBER
323 , X_outcome OUT NOCOPY VARCHAR2 );
324
325
326 -- ========================================================================
327 -- PROCEDURE UpdateRelatedItem
328 -- ========================================================================
329 -- Changed the parameter name from raw_cost to denom_raw_cost and
330 -- raw_cost_rate to denom_cost_rate
331 --
332 PROCEDURE UpdateRelatedItem( X_expenditure_item_id IN NUMBER
336
333 , X_denom_raw_cost IN NUMBER
334 , X_denom_raw_cost_rate IN NUMBER
335 , X_status OUT NOCOPY NUMBER );
337 END GMS_TRANSACTIONS;
[Home] [Help]