[Home] [Help]
PACKAGE: APPS.PAAPIMP_PKG
Source
1 PACKAGE PAAPIMP_PKG AS
2 /* $Header: PAAPIMPS.pls 120.45.12010000.2 2008/08/27 12:28:22 prabsing ship $ */
3
4 /* Main Procedure */
5
6 PROCEDURE PAAPIMP ( errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY VARCHAR2,
8 invoice_type IN VARCHAR2 DEFAULT NULL,
9 project_id IN NUMBER DEFAULT NULL,
10 batch_name IN VARCHAR2,
11 gl_date_arg IN VARCHAR2 DEFAULT NULL,
12 transaction_date_arg IN VARCHAR2 DEFAULT NULL,
13 debug_mode IN VARCHAR2 DEFAULT 'FALSE',
14 process_invoices IN VARCHAR2 DEFAULT 'Y',
15 process_receipts IN VARCHAR2 DEFAULT 'N',
16 process_discounts IN VARCHAR2 DEFAULT 'N',
17 output_type IN NUMBER DEFAULT 3
18 );
19
20
21 /* Init Phase --------------------------------------------------*/
22 PROCEDURE Initialize_Global ( p_project_id IN NUMBER,
23 p_batch_name IN VARCHAR2,
24 p_gl_date IN DATE,
25 p_transaction_date IN DATE,
26 p_debug_mode IN VARCHAR2,
27 p_output IN NUMBER,
28 p_invoice_source1 IN ap_invoices.source%TYPE DEFAULT NULL,
29 p_invoice_source2 IN ap_invoices.source%TYPE DEFAULT NULL,
30 p_invoice_source3 IN ap_invoices.source%TYPE DEFAULT NULL,
31 p_invoice_type IN ap_invoices.invoice_type_lookup_code%TYPE,
32 p_system_linkage IN pa_transaction_interface.system_linkage%TYPE,
33 p_process_receipts IN VARCHAR2 DEFAULT 'N');
34
35 PROCEDURE fetch_pf_var(p_process_receipts IN VARCHAR2 DEFAULT 'N');
36
37 FUNCTION create_new_batch_name
38 RETURN pa_transaction_interface.batch_name%TYPE;
39
40 PROCEDURE write_validate_param_log;
41
42 PROCEDURE write_log ( p_message_type IN NUMBER,
43 p_message IN VARCHAR2);
44
45
46 -- Global Record and Table Definitions
47
48 TYPE rsob IS RECORD ( rsob_id gl_alc_ledger_rships_v.ledger_id%TYPE,
49 rcurrency_code gl_alc_ledger_rships_v.currency_code%TYPE);
50
51
52 -- Variables to record set of books ID for AP and PA
53
54 G_PA_SOB NUMBER;
55 G_AP_SOB NUMBER;
56 G_PO_SOB NUMBER;
57
58
59 -- Global variable to indicate whether to call ins_mc_txn_interface_all
60
61 -- G_DO_MRC_FLAG VARCHAR2(2);
62
63 -- Global variables for the parameters
64
65 G_PROJECT_ID NUMBER;
66 G_GL_DATE DATE;
67 G_TRANSACTION_DATE DATE;
68 G_DEBUG_MODE VARCHAR2(10);
69
70 -- Global profile variables
71
72 G_ACCTNG_METHOD VARCHAR2(1);
73 G_USER_ID NUMBER;
74 G_REQUEST_ID NUMBER;
75 G_PROG_APPL_ID NUMBER;
76 G_PROG_ID NUMBER;
77 G_LOGIN_ID NUMBER;
78 G_COMMIT_SIZE NUMBER;
79 G_TRANS_DFF_AP VARCHAR2(10);
80
81 /* Count variables */
82 G_NUM_BATCHES_PROCESSED NUMBER;
83 G_NUM_INVOICES_PROCESSED NUMBER;
84 G_NUM_DISTRIBUTIONS_PROCESSED NUMBER;
85 G_DISTRIBUTIONS_MARKED NUMBER;
86 G_PAY_DISTRIBUTIONS_MARKED NUMBER;
87 G_DISC_DISTRIBUTIONS_MARKED NUMBER;
88 -- Count variables for AP Discounts
89 G_NUM_DISCOUNTS_PROCESSED NUMBER;
90
91 G_PROFILE_NAME VARCHAR2(128);
92 G_LOCK_NAME VARCHAR2(128);
93 G_LOCKHNDL VARCHAR2(128);
94 G_TRANSACTION_SOURCE pa_transaction_interface.transaction_source%TYPE;
95 G_USER_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
96 G_NRT_TRANSACTION_SOURCE pa_transaction_interface.transaction_source%TYPE;
97 G_NRT_USER_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
98 G_INVOICE_SOURCE1 ap_invoices.source%TYPE;
99 G_INVOICE_SOURCE2 ap_invoices.source%TYPE;
100 G_INVOICE_SOURCE3 ap_invoices.source%TYPE;
101 G_INVOICE_TYPE ap_invoices.invoice_type_lookup_code%TYPE;
102 G_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
103 G_NRT_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
104 G_INTERFACE_ID NUMBER;
105 G_NRT_INTERFACE_ID NUMBER;
106 G_SYSTEM_LINKAGE pa_transaction_interface.system_linkage%TYPE;
107 G_OUTPUT NUMBER; /* Indicate what kind of output procedure to use: dbms_output or PA_DEBUG */
108 G_TRANSACTION_STATUS_CODE pa_transaction_interface.transaction_status_code%TYPE;
109 G_TRANSACTION_REJECTION_CODE pa_transaction_interface.transaction_rejection_code%TYPE;
110
111 --AP Discounts
112 G_Discount_Method VARCHAR2(10);
113 G_DISC_TRANSACTION_SOURCE pa_transaction_interface.transaction_source%TYPE;
114 G_DISC_USER_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
115 G_DISC_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
116 G_DISC_INTERFACE_ID NUMBER;
117 G_Profile_Discount_Start_date DATE;
118
119 -- Receipt Accruals
120 G_RCV_TRANSACTIONS_MARKED_O NUMBER;
121 G_RCV_TRANSACTIONS_MARKED_J NUMBER;
122 G_RCV_TRANSACTIONS_MARKED_NULL NUMBER;
123 G_RCV_TRANSACTIONS_MARKED_G NUMBER;
124 G_NUM_RCV_TXN_PROCESSED NUMBER;
125 G_NUM_RCVTAX_PROCESSED NUMBER;
126 G_RCV_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
127 G_RCVTAX_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
128 G_RCV_INTERFACE_ID NUMBER;
129 G_RCVNRT_INTERFACE_ID NUMBER;
130 G_RCV_TRANSACTION_SOURCE pa_transaction_interface.transaction_source%TYPE;
131 G_RCVTAX_TRANSACTION_SOURCE pa_transaction_interface.transaction_source%TYPE;
132 G_RCV_USER_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
133 G_RCVTAX_USER_TRX_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
134
135 -- pricing changes
136 G_RCV_PRC_ADJ_TRX_SRC pa_transaction_interface.transaction_source%TYPE;
137 G_RCV_PRC_ADJ_USER_TRX_SRC pa_transaction_interface.user_transaction_source%TYPE;
138 G_RCVTAX_PRC_ADJ_TRX_SRC pa_transaction_interface.transaction_source%TYPE;
139 G_RCVTAX_PRC_ADJ_USER_TRX_SRC pa_transaction_interface.user_transaction_source%TYPE;
140
141 G_TRANS_DFF_PO VARCHAR2(10);
142 l_cdl_system_reference4 pa_transaction_interface.cdl_system_reference4%TYPE :=NULL;
143
144 --AP VARIANCE
145 G_AP_VAR_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
146 G_AP_VAR_INTERFACE_ID NUMBER;
147 G_AP_VAR_USER_TXN_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
148 G_AP_VAR_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
149 G_NUM_AP_VARIANCE_MARKED_W NUMBER;
150 G_NUM_AP_VARIANCE_PROCESSED NUMBER;
151
152 --AP ERV
153 G_AP_ERV_BATCH_NAME pa_transaction_interface.batch_name%TYPE;
154 G_AP_ERV_INTERFACE_ID NUMBER;
155 G_AP_ERV_USER_TXN_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
156 G_AP_ERV_TRANSACTION_SOURCE pa_transaction_interface.user_transaction_source%TYPE;
157
158 -- AP Freight and Misc
159 G_AP_FRT_BATCH_NAME pa_transaction_interface.batch_name%TYPE; --NEW
160 G_AP_FRT_INTERFACE_ID NUMBER; --NEW
161
162 --Prepayment
163 G_PREPAY_BATCH_NAME pa_transaction_interface.batch_name%TYPE; --NEW
164 G_PREPAY_INTERFACE_ID NUMBER; --NEW
165
166 G_UNIQUE_ID NUMBER;
167 G_err_stack VARCHAR2(630);
168 G_err_stage VARCHAR2(630);
169 G_err_code NUMBER;
170 G_err_stage_num NUMBER;
171
172 /* MC Upgrade variables */
173 G_ACCT_CURRENCY_CODE VARCHAR(15);
174 G_ORG_ID NUMBER;
175 E_DIFFERENT_SOB EXCEPTION;
176 L_pay_code_combination_id ap_invoices.accts_pay_code_combination_id%TYPE := NULL;
177
178 /* Used during insertion into pa_transaction_interface, this variable is set
179 to the weekending date of the maximum expenditure item date of an invoice */
180
181 G_EXPENDITURE_ENDING_DATE pa_transaction_interface.expenditure_ending_date%TYPE;
182 G_PER_BUS_GRP_ID pa_transaction_interface.person_business_group_id%TYPE;
183
184 /* Added for performance improvement*/
185 G_Assets_Addition_flag ap_invoice_distributions.Assets_Addition_Flag%TYPE;
186
187 /* Added to create pl/sql table columns in the same cursor*/
188 l_pay_cc_id pa_transaction_interface.dr_code_combination_id%TYPE;
189 l_quantity pa_transaction_interface.quantity%TYPE;
190 l_denom_raw_cost pa_transaction_interface.denom_raw_cost%TYPE;
191 l_acct_raw_cost pa_transaction_interface.acct_raw_cost%TYPE;
192 l_denom_cur_code pa_transaction_interface.denom_currency_code%TYPE;
193 l_acct_rate_date pa_transaction_interface.acct_rate_date%TYPE;
194 l_acct_rate_type pa_transaction_interface.acct_rate_type%TYPE;
195 l_acct_exch_rate pa_transaction_interface.acct_exchange_rate%TYPE;
196 l_txn_src pa_transaction_interface.transaction_source%TYPE;
197 l_user_txn_src pa_transaction_interface.user_transaction_source%TYPE;
198 l_batch_name pa_transaction_interface.batch_name%TYPE;
199 l_interface_id pa_transaction_interface.interface_id%TYPE;
200 l_exp_end_date pa_transaction_interface.EXPENDITURE_ENDING_DATE%TYPE;
201 l_txn_status_code pa_transaction_interface.transaction_status_code%TYPE;
202 l_txn_rej_code pa_transaction_interface.transaction_rejection_code%TYPE;
203 l_bus_grp_id pa_transaction_interface.person_business_group_id%TYPE;
204 l_record_type VARCHAR2(20);
205 l_insert_flag VARCHAR2(1);
206 l_cancel_flag VARCHAR2(1) := 'N';
207 l_sc_xfer_code VARCHAR2(1) := 'V';
208 l_net_zero_flag VARCHAR2(1):='N';
209 l_denom_amt_var NUMBER;
210 l_acct_amt_var NUMBER;
211 l_adj_exp_item_id NUMBER; --NEW
212 l_prev_cr_ccid NUMBER;
213 l_prev_dr_ccid NUMBER;
214 l_prev_exp_item_id NUMBER:=0;
215 l_rev_index NUMBER:=0;
216 l_pay_hist_id NUMBER;
217 l_prepay_dist_id NUMBER;
218
219
220 -- Initialize all PLSQL tables
221
222 --l_dist_line_num_tbl PA_PLSQL_DATATYPES.NumTabTyp;
223 l_accounted_cr_tbl PA_PLSQL_DATATYPES.NumTabTyp;
224 l_accounted_dr_tbl PA_PLSQL_DATATYPES.NumTabTyp;
225 l_accounted_nr_tax_tbl PA_PLSQL_DATATYPES.NumTabTyp;
226 l_acct_exch_rate_tbl PA_PLSQL_DATATYPES.NumTabTyp;
227 l_acct_pay_cc_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
228 l_acct_rate_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
229 l_acct_rate_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
230 l_acct_raw_cost_tbl PA_PLSQL_DATATYPES.NumTabTyp;
231 l_adj_exp_item_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;--NEW
232 l_amount_tbl PA_PLSQL_DATATYPES.NumTabTyp;
233 l_attribute10_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
234 l_attribute1_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
235 l_attribute2_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
236 l_attribute3_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
237 l_attribute4_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
238 l_attribute5_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
239 l_attribute6_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
240 l_attribute7_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
241 l_attribute8_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
242 l_attribute9_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
243 l_attribute_cat_tbl PA_PLSQL_DATATYPES.Char150TabTyp;
244 l_batch_name_tbl PA_PLSQL_DATATYPES.Char50TabTyp;
245 l_bus_grp_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
246 l_cancel_flag_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;--NEW
247 l_cc_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
248 l_cdl_sys_ref4_tbl PA_PLSQL_DATATYPES.Char15TabTyp;
249 l_cdl_sys_ref3_tbl PA_PLSQL_DATATYPES.Char15TabTyp;
250 l_created_by_tbl PA_PLSQL_DATATYPES.NumTabTyp;
251 l_cur_conv_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
252 l_cur_conv_rate_tbl PA_PLSQL_DATATYPES.NumTabTyp;
253 l_cur_conv_type_tbl PA_PLSQL_DATATYPES.CHAR30TabTyp;
254 l_denom_cur_code_tbl PA_PLSQL_DATATYPES.CHAR15TabTyp;
255 l_denom_raw_cost_tbl PA_PLSQL_DATATYPES.NumTabTyp;
256 l_description_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
257 l_dest_typ_code_tbl PA_PLSQL_DATATYPES.Char25TabTyp;
258 l_dist_cc_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
259 l_ei_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
260 l_employee_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
261 l_entered_cr_tbl PA_PLSQL_DATATYPES.NumTabTyp;
262 l_entered_dr_tbl PA_PLSQL_DATATYPES.NumTabTyp;
263 l_entered_nr_tax_tbl PA_PLSQL_DATATYPES.NumTabTyp;
264 l_exp_end_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
265 l_exp_org_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
266 l_exp_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
267 l_fc_document_type_tbl PA_PLSQL_DATATYPES.CHAR15TabTyp;--NEW
268 l_fc_enabled_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;--NEW
269 l_gl_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
270 l_insert_flag_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;
271 l_interface_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
272 l_inv_pay_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
273 -- l_inv_typ_code_tbl PA_PLSQL_DATATYPES.Char25TabTyp;
274 l_inv_type_code_tbl PA_PLSQL_DATATYPES.Char25TabTyp;
275 l_invoice_dist_id PA_PLSQL_DATATYPES.IdTabTyp; --NEw
276 l_invoice_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
277 l_invoice_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
278 l_invoice_num_tbl PA_PLSQL_DATATYPES.Char50TabTyp;
279 l_job_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
280 l_justification_tbl PA_PLSQL_DATATYPES.Char240TabTyp;
281 l_ln_type_lookup_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
282 l_org_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
283 l_pa_add_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
284 l_paid_emp_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
285 l_parent_pmt_id_tbl PA_PLSQL_DATATYPES.IdTabTyp; --NEW
286 l_parent_rcv_id_tbl PA_PLSQL_DATATYPES.IdTabTyp; -- NEW --added for full return reversal logic
287 l_rcv_amount_tbl PA_PLSQL_DATATYPES.NumTabTyp; -- NEW --added for full return reversal logic
288 l_parent_rev_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;--NEW
289 l_pay_cc_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
290 l_po_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
291 l_po_head_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
292 l_po_num_tbl PA_PLSQL_DATATYPES.Char20TabTyp;
293 l_proj_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
294 l_project_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
295 l_quantity_tbl PA_PLSQL_DATATYPES.NumTabTyp;
296 l_rcv_acct_evt_id_tbl PA_PLSQL_DATATYPES.IdTabTyp; -- pricing changes
297 l_rcv_acct_evt_typ_tbl PA_PLSQL_DATATYPES.Char30TabTyp; -- pricing changes
298 l_rcv_acct_rec_tax_tbl PA_PLSQL_DATATYPES.NumTabTyp; -- pricing changes
299 l_rcv_ent_rec_tax_tbl PA_PLSQL_DATATYPES.NumTabTyp; -- pricing changes
300 l_rcv_txn_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
301 l_rec_conv_rate_tbl PA_PLSQL_DATATYPES.NumTabTyp;
302 l_rec_cur_amt_tbl PA_PLSQL_DATATYPES.NumTabTyp;
303 l_rec_cur_code_tbl PA_PLSQL_DATATYPES.Char15TabTyp;
304 l_record_type_tbl PA_PLSQL_DATATYPES.CHAR20TabTyp;
305 l_reversal_flag_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp; --NEW
306 l_sort_var_tbl PA_PLSQL_DATATYPES.NumTabTyp;
307 l_source_tbl PA_PLSQL_DATATYPES.Char25TabTyp;
308 l_task_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
309 l_trx_type_tbl PA_PLSQL_DATATYPES.Char25TabTyp;
310 l_txn_rej_code_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
311 l_txn_src_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
312 l_txn_status_code_tbl PA_PLSQL_DATATYPES.Char2TabTyp;
313 l_user_txn_src_tbl PA_PLSQL_DATATYPES.Char80TabTyp;
314 l_vendor_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
315 l_si_assts_add_flg_tbl PA_PLSQL_DATATYPES.Char2TabTyp;
316 l_mrc_exchange_date_tbl PA_PLSQL_DATATYPES.DateTabTyp;
317 l_payment_status_flag_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
318 l_net_zero_flag_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;--NEW
319 l_sc_xfer_code_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;--NEW
320 l_rcv_sub_ledger_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
321 l_rev_parent_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
322 l_rev_child_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
323 l_rev_parent_dist_ind_tbl PA_PLSQL_DATATYPES.IdTabTyp;
324 l_hist_flag_tbl PA_PLSQL_DATATYPES.Char2TabTyp;
325 l_prepay_dist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
326 l_pay_hist_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
327 l_exp_cst_rt_flg_tbl PA_PLSQL_DATATYPES.CHAR1TabTyp;--NEW
328 l_po_tax_qty_tbl PA_PLSQL_DATATYPES.NumTabTyp;
329 /* Fixed value global variables */
330 G_OUTPUT_SQLPLUS NUMBER := 1;
331 G_OUTPUT_PADEBUG NUMBER := 2;
332 G_OUTPUT_FND NUMBER := 3;
333
334 /* Type of message */
335 LOG NUMBER := 1;
336 DEBUG NUMBER := 2;
337
338 /*================================================================================*/
339 /* Procedure Declarations*/
340
341 /* Cleanup Phase -------------------------------------------------*/
342 PROCEDURE cleanup;
343
344 /* Net Zero Adjustment Phase -------------------------------------*/
345 PROCEDURE net_zero_adjustment;
346
347 /* Mark Distributions Phase --------------------------*/
348 PROCEDURE mark_PAflag_O;
349
350 /* Populate Transaction Interface Phase --------------------------*/
351 FUNCTION Create_New_Org_Transref ( p_batch_name IN pa_transaction_interface.batch_name%TYPE,
352 p_invoice_id IN ap_invoices.invoice_id%TYPE,
353 p_invoice_distribution_id IN ap_invoice_distributions.invoice_distribution_id %TYPE)
354 RETURN pa_transaction_interface.orig_transaction_reference%TYPE;
355
356 PRAGMA RESTRICT_REFERENCES(create_new_org_transref, WNDS);
357
358 PROCEDURE print_stat_and_submit_report;
359
360 /* MRC Functions --------------------------------------------------*/
361
362 FUNCTION get_mrc_flag RETURN VARCHAR2;
363
364 /* Transaction Import Phase ---------------------------------------*/
365
366 PROCEDURE Trans_Import ( p_transaction_source IN pa_transaction_interface.transaction_source%TYPE,
367 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
368 p_interface_id IN pa_transaction_interface.interface_id%TYPE,
369 p_user_id IN NUMBER);
370
371 /*===================================*/
372 /*Declarations for invoice processing*/
373 /*===================================*/
374
375 CURSOR Invoice_Cur IS
376 SELECT /*+ leading(DIST) */ INV.Invoice_id invoice_id,
377 INV.created_by created_by,
378 DIST.Invoice_distribution_id Invoice_distribution_id, --NEW
379 DIST.Invoice_Line_Number Invoice_Line_Number, --NEW
380 DIST.Project_id project_id,
381 DIST.Task_id task_id,
382 DIST.line_type_lookup_code,
383 DIST.Expenditure_type expenditure_type,
384 to_char(DIST.Expenditure_Item_Date, 'DD-MON-RR') expenditure_item_date,
385 nvl(DIST.base_amount,DIST.amount) amount,
386 DIST.description description,
387 INVL.justification, /* Modified for Bug 6659770 */
388 DIST.dist_code_combination_id dist_code_combination_id,
389 DIST.expenditure_organization_id expenditure_organization_id,
390 decode(DIST.line_type_lookup_code,'ERV',null,
391 'IPV',null,
392 nvl(nvl(DECODE(TYPE.Cost_Rate_Flag,'Y', DIST.PA_Quantity,NVL(DIST.Amount,DIST.Base_Amount)),
393 nvl( DIST.amount,DIST.base_amount )),0)) quantity,
394 L_pay_code_combination_id accts_pay_code_combination_id,
395 DIST.accounting_date gl_date,
396 DIST.attribute_category,
397 DIST.attribute1,
398 DIST.attribute2,
399 DIST.attribute3,
400 DIST.attribute4,
401 DIST.attribute5,
402 DIST.attribute6,
403 DIST.attribute7,
404 DIST.attribute8,
405 DIST.attribute9,
406 DIST.attribute10, /* MC Upgrade */
407 DIST.receipt_currency_amount,
408 DIST.receipt_currency_code,
409 DIST.receipt_conversion_rate,
410 DIST.amount denom_raw_cost,
411 INV.invoice_currency_code denom_currency_code,
412 INV.exchange_date acct_rate_date,
413 INV.exchange_rate_type acct_rate_type,
414 INV.exchange_rate acct_exchange_rate,
415 Decode(nvl(VEND.employee_id, 0),0,NULL,
416 PA_UTILS.GetEmpJobId( VEND.employee_id,DIST.Expenditure_Item_Date)) Job_id,
417 VEND.employee_id employee_id,
418 VEND.vendor_id vendor_id,
419 INV.invoice_type_lookup_code,
420 INV.source,
421 INV.org_id ,
422 INV.invoice_num invoice_number ,
423 l_cdl_system_reference4 cdl_system_reference4 ,
424 DIST.po_distribution_id po_distribution_id /*added the following dummy columns for pl/sql table upgrade*/ ,
425 l_txn_src ,
426 l_user_txn_src ,
427 l_batch_name ,
428 l_interface_id ,
429 l_exp_end_date ,
430 l_txn_status_code ,
431 l_txn_rej_code ,
432 l_bus_grp_id ,
433 inv.paid_on_behalf_employee_id,
434 DECODE(INV.source, 'Inter-Project Invoices', 1, 'Projects Intercompany Invoices',2, 3) sort_c ,
435 nvl(DIST.reversal_flag,'N') reversal_flag --NEW ,
436 ,nvl(DIST.cancellation_flag,'N') cancellation_flag --NEW
437 ,DIST.parent_reversal_id parent_reversal_id --NEW
438 ,l_net_zero_flag
439 ,l_sc_xfer_code
440 ,l_adj_exp_item_id
441 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(DIST.project_id, 'STD') fc_enabled
442 ,nvl(DIST.exchange_date, INV.invoice_date) mrc_exchange_date
443 --R12 AP lines uptake : PO matched Prepayment commitments are not fundschecked
444 ,DECODE (inv.invoice_type_lookup_code,'PREPAYMENT',DECODE(DIST.po_distribution_id ,NULL,'ALL','ACT'),'ALL') fc_document_type
445 ,'Y' si_assets_addition_flag
446 ,'Y' insert_flag
447 ,DIST.historical_flag historical_flag
448 ,DIST.PRepay_distribution_id PRepay_distribution_id
449 FROM PO_Vendors VEND,
450 AP_Invoices INV,
451 AP_Invoice_Lines INVL,
452 PA_Transfer_AP_Invoices_View DIST,
453 PA_EXPENDITURE_TYPES TYPE
454 WHERE DIST.Invoice_Id = INV.Invoice_Id
455 AND INVL.Invoice_id = INV.Invoice_Id
456 AND INVL.Line_Number = DIST.Invoice_Line_Number
457 AND INVL.Invoice_Id = DIST.Invoice_Id
458 AND DIST.Pa_Addition_Flag = 'O'
459 AND INV.Vendor_Id = VEND.Vendor_Id
460 AND DIST.request_id = G_REQUEST_ID
461 AND DIST.expenditure_type = TYPE.expenditure_type
462 ORDER BY sort_c, INV.invoice_id,DIST.Invoice_distribution_id;
463
464 PROCEDURE transfer_inv_to_pa;
465
466 /*Tieback to AP Phase --------------------------------------------*/
467 PROCEDURE tieback_AP_ER (
468 p_transaction_source IN pa_transaction_interface.transaction_source%TYPE,
469 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
470 p_interface_id IN pa_transaction_interface.interface_id%TYPE);
471
472
473 PROCEDURE lock_rcv_txn (p_po_distribution_id IN ap_invoice_distributions.po_distribution_id%TYPE);
474
475 PROCEDURE tieback_locked_rcvtxn;
476
477 /*=====================================*/
478 /*Declarations for Discount Processing */
479 /*=====================================*/
480
481 --Begin Cursor for discounts
482
483 CURSOR DISCOUNT_Cur IS
484 SELECT pay.invoice_payment_id,
485 INV.Invoice_id invoice_id,
486 DIST.Invoice_distribution_id,
487 DIST.Invoice_Line_Number Invoice_Line_Number,
488 DIST.Project_id project_id,
489 DIST.Task_id task_id,
490 VEND.employee_id employee_id,
491 DIST.Expenditure_type expenditure_type,
492 PA_AP_INTEGRATION.get_si_cost_exp_item_date(
493 chk.check_date,
494 pay.accounting_date,
495 dist.expenditure_item_date,
496 pay.creation_date,
497 NULL,
498 'DISCOUNT'
499 ) expenditure_item_date,
500 VEND.vendor_id vendor_id,
501 INV.created_by created_by,
502 DIST.expenditure_organization_id expenditure_organization_id,
503 l_quantity,
504 Decode( nvl(VEND.employee_id, 0),0,NULL,
505 PA_UTILS.GetEmpJobId(VEND.employee_id,DIST.Expenditure_Item_Date)) Job_id,
506 DIST.description description,
507 DIST.dist_code_combination_id dist_code_combination_id,
508 l_pay_cc_id,
509 pay.accounting_date gl_date,
510 DIST.attribute_category,
511 DIST.attribute1,
512 DIST.attribute2,
513 DIST.attribute3,
514 DIST.attribute4,
515 DIST.attribute5,
516 DIST.attribute6,
517 DIST.attribute7,
518 DIST.attribute8,
519 DIST.attribute9,
520 DIST.attribute10,
521 INV.invoice_type_lookup_code,
522 INV.org_id,
523 INV.invoice_num invoice_number,
524 DIST.line_type_lookup_code
525 ,INV.source
526 ,nvl(PAYDIST.amount,0) amount
527 ,nvl(PAYDIST.paid_base_amount,nvl(PAYDIST.amount,0)) base_amount
528 ,inv.payment_currency_code --new
529 ,pay.exchange_date --new
530 ,pay.exchange_rate_type --new
531 ,pay.exchange_rate --new
532 ,l_cdl_system_reference4
533 ,l_txn_src
534 ,l_user_txn_src
535 ,l_batch_name
536 ,l_interface_id
537 ,l_exp_end_date
538 ,l_txn_status_code
539 ,l_txn_rej_code
540 ,l_bus_grp_id
541 -- ,nvl(pay.reversal_flag,'N') reversal_flag --NEW
542 ,DECODE(nvl(PAY.reversal_flag,'N'),'N',decode(nvl(DIST.reversal_flag,'N'),'Y','R','N'),PAY.reversal_flag) reversal_flag --Bug5408748
543 ,l_cancel_flag cancel_flag
544 -- ,pay.reversal_inv_pmt_id reversal_inv_pmt_id --NEW
545 ,DECODE(PAY.REVERSAL_INV_PMT_ID,null,decode(nvl(PAY.reversal_flag,'N'),'N',DIST.parent_reversal_id),PAY.REVERSAL_INV_PMT_ID) REVERSAL_INV_PMT_ID
546 ,l_net_zero_flag
547 ,l_sc_xfer_code
548 ,l_adj_exp_item_id
549 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(DIST.project_id, 'STD') fc_enabled
550 ,nvl(DIST.exchange_date, INV.invoice_date) mrc_exchange_date
551 ,'ACT' fc_document_type
552 ,'Y' si_assets_addition_flag
553 ,'Y' insert_flag
554 , paydist.payment_hist_dist_id
555 ,dist.pa_addition_flag pa_addition_flag --Bug# 5516855
556 from AP_Invoice_Payments_all pay,
557 po_vendors vend,
558 ap_invoices inv,
559 ap_payment_hist_dists paydist,
560 PA_Transfer_AP_Invoices_View DIST,
561 ap_checks chk
562 where pay.posted_flag='Y'
563 AND pay.invoice_payment_id = paydist.invoice_payment_id
564 AND pay.check_id = chk.check_id
565 AND paydist.pay_dist_lookup_code = 'DISCOUNT'
566 AND dist.line_type_lookup_code <> 'TAX' /*bug-6326262*/
567 AND DIST.invoice_id=pay.invoice_id
568 AND INV.vendor_id = VEND.vendor_id
569 AND DIST.invoice_distribution_id = paydist.invoice_distribution_id
570 AND dist.invoice_id=inv.invoice_id
571 AND paydist.pa_addition_flag = 'O'
572 AND PAYDIST.request_id = G_REQUEST_ID
573 ORDER BY INV.invoice_id, DIST.Invoice_Distribution_Id, PAY.invoice_payment_id;
574
575 --END cursor for discounts
576
577 -- Check profile set-up of cut-off date of Discounts to be pulled
578 FUNCTION return_profile_discount_date RETURN VARCHAR2;
579
580 -- Processing discounts phase
581 PROCEDURE mark_PA_Disc_flag_O;
582
583 PROCEDURE transfer_disc_to_pa;
584
585
586 /*===========================================*/
587 /*Declarations for receipt Accrual processing*/
588 /*===========================================*/
589
590 --The following cursors and procedures are added for Receipt Accrual processing
591 CURSOR Rcv_Receipts_Cur IS
592 SELECT rcv_txn.transaction_id rcv_transaction_id,
593 po_dist.po_distribution_id po_distribution_id,
594 po_dist.po_header_id po_header_id,
595 po_head.segment1 po_num,
596 nvl(rcv_txn.quantity,0) quantity, /* bug 3496492 */
597 nvl(rcv_txn.quantity,0) quantity, /* bug 5465098 */
598 nvl(rcv_sub.ENTERED_DR,0) entered_dr,
599 nvl(rcv_sub.entered_cr,0) entered_cr,
600 nvl(rcv_sub.ACCOUNTED_DR,0) accounted_dr,
601 nvl(rcv_sub.accounted_cr,0) accounted_cr,
602 nvl(rcv_sub.ENTERED_NR_TAX,0) ENTERED_NR_TAX,
603 nvl(rcv_sub.ACCOUNTED_NR_TAX,0) ACCOUNTED_NR_TAX,
604 l_denom_raw_cost denom_raw_cost,
605 l_acct_raw_cost acct_raw_cost,
606 l_record_type record_type,
607 rcv_sub.code_combination_id dr_cc_id,
608 rcv_txn.CURRENCY_CODE denom_currency_code ,
609 rcv_txn.CURRENCY_CONVERSION_DATE ,
610 rcv_txn.CURRENCY_CONVERSION_TYPE ,
611 rcv_txn.CURRENCY_CONVERSION_RATE ,
612 trunc(rcv_txn.TRANSACTION_DATE) GL_Date,
613 rcv_txn.DESTINATION_TYPE_CODE ,
614 rcv_sub.pa_addition_flag ,
615 rcv_txn.transaction_type trx_type,
616 nvl(rcv_txn.project_id , po_dist.project_id) project_id, /* Bug 6989609 : Added NVL */
617 nvl(rcv_txn.task_id , po_dist.task_id) task_id, /* Bug 6989609 : Added NVL */
618 VEND.employee_id employee_id, /* Removed NVL for 3297771 */
619 po_dist.expenditure_type,
620 PA_AP_INTEGRATION.get_si_cost_exp_item_date(
621 rcv_txn.transaction_date,
622 rcv_sub.accounting_date,
623 po_dist.expenditure_item_date,
624 rcv_txn.creation_date,
625 NULL,
626 'RECEIPT'
627 ) expenditure_item_date,
628 VEND.vendor_id vendor_id,
629 po_dist.EXPENDITURE_ORGANIZATION_ID expenditure_organization_id,
630 Decode( nvl(VEND.employee_id, 0),0,NULL, PA_UTILS.GetEmpJobId(VEND.employee_id,po_DIST.Expenditure_Item_Date)) Job_id,
631 po_line.ITEM_DESCRIPTION description,
632 po_dist.attribute_category,
633 po_dist.attribute1,
634 po_dist.attribute2,
635 po_dist.attribute3,
636 po_dist.attribute4,
637 po_dist.attribute5,
638 po_dist.attribute6,
639 po_dist.attribute7,
640 po_dist.attribute8,
641 po_dist.attribute9,
642 po_dist.attribute10,
643 po_dist.ORG_ID
644 ,l_cdl_system_reference4
645 ,l_txn_src
646 ,l_user_txn_src
647 ,l_batch_name
648 ,l_interface_id
649 ,l_exp_end_date
650 ,l_txn_status_code
651 ,l_txn_rej_code
652 ,l_bus_grp_id
653 ,l_insert_flag
654 ,rcv_sub.accounting_event_id acct_evt_id -- pricing changes
655 ,rcv_sub.accounted_rec_tax
656 ,rcv_sub.entered_rec_tax
657 ,rcv_txn.parent_transaction_id --NEW --added for the full retrn logic
658 ,l_net_zero_flag
659 ,l_sc_xfer_code
660 ,rcv_txn.amount --NEW --added for the full retrn logic
661 ,l_adj_exp_item_id
662 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(nvl(rcv_txn.project_id , po_dist.project_id), 'STD') fc_enabled /* Bug 6989609 : Added NVL */
663 , nvl(rcv_txn.currency_conversion_date, rcv_txn.transaction_date)
664 ,'ALL' fc_document_type
665 ,'Y' si_assets_addition_flag
666 ,'Y' insert_flag
667 ,rcv_sub.rcv_sub_ledger_id
668 ,type.cost_rate_flag
669 from rcv_transactions rcv_txn,
670 rcv_receiving_sub_ledger rcv_sub,
671 po_headers_all po_head,
672 po_lines_all po_line,
673 po_distributions_all po_dist,
674 po_vendors vend,
675 pa_expenditure_types type
676 where ((rcv_txn.destination_type_code ='EXPENSE' ) OR
677 (rcv_txn.destination_type_code='RECEIVING' AND
678 (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING'))
679 )
680 )
681 AND po_dist.CODE_COMBINATION_ID =rcv_sub.CODE_COMBINATION_ID
682 AND rcv_sub.ACTUAL_FLAG = 'A'
683 AND rcv_sub.pa_addition_flag IN ('O','J') -- pricing changes
684 AND nvl(po_dist.distribution_type,'XXX') <> 'PREPAYMENT' -- bug 7192304
685 AND rcv_sub.request_id=G_REQUEST_ID
686 AND po_dist.project_ID IS NOT NULL
687 AND po_dist.accrue_on_receipt_flag= 'Y'
688 AND rcv_txn.transaction_id=rcv_sub.rcv_transaction_id
689 AND rcv_txn.po_header_id=po_head.PO_HEADER_ID
690 AND po_head.po_header_id=po_line.po_header_id
691 AND po_line.po_line_id=po_dist.po_line_id
692 AND rcv_txn.PO_DISTRIBUTION_ID=po_dist.po_distribution_id
693 AND po_head.org_id=G_ORG_ID
694 AND po_head.vendor_id=VEND.Vendor_Id
695 AND po_dist.expenditure_type = type.expenditure_type
696 AND (pa_nl_installed.is_nl_installed = 'N'
697 OR ( pa_nl_installed.is_nl_installed = 'Y'
698 AND NOT EXISTS (SELECT 'X'
699 FROM mtl_system_items si,
700 po_lines_all pol
701 WHERE po_line.po_line_id = pol.po_line_id
702 AND si.inventory_item_id = pol.item_id
703 AND si.comms_nl_trackable_flag = 'Y')
704 ) )
705 order by rcv_txn.po_distribution_id;
706
707 PROCEDURE mark_RCV_PAflag;
708
709 PROCEDURE transfer_receipts_to_pa;
710
711 PROCEDURE tieback_rcv_Txn (
712 p_transaction_source IN pa_transaction_interface.transaction_source%TYPE,
713 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
714 p_interface_id IN pa_transaction_interface.interface_id%TYPE);
715
716 PROCEDURE check_failed_receipts (
717 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
718 p_interface_id IN pa_transaction_interface.interface_id%TYPE);
719
720 PROCEDURE lock_ap_invoice (p_po_distribution_id IN ap_invoice_distributions.po_distribution_id%TYPE);
721
722 PROCEDURE tieback_locked_invoice;
723
724 /*==========================================================*/
725 /*Declarations for Invoice Variance processing*/
726 /* This cursor is opened only for amount based variances */
727 /*==========================================================*/
728
729 CURSOR Invoice_Variance_Cur IS
730 SELECT INV.Invoice_id invoice_id,
731 DIST.Invoice_distribution_id,
732 DIST.Invoice_Line_Number Invoice_Line_Number,
733 DIST.Project_id project_id,
734 DIST.Task_id task_id,
735 DIST.line_type_lookup_code,
736 DIST.Expenditure_type expenditure_type,
737 to_char(DIST.Expenditure_Item_Date, 'DD-MON-RR') expenditure_item_date,
738 nvl(DIST.base_amount_variance,DIST.amount_variance) amount,
739 DIST.description description,
740 DIST.dist_code_combination_id dist_code_combination_id,
741 DIST.expenditure_organization_id expenditure_organization_id,
742 DIST.amount_variance quantity,
743 -- L_pay_code_combination_id accts_pay_code_combination_id,
744 DIST.accounting_date gl_date,
745 DIST.attribute_category,
746 DIST.attribute1,
747 DIST.attribute2,
748 DIST.attribute3,
749 DIST.attribute4,
750 DIST.attribute5,
751 DIST.attribute6,
752 DIST.attribute7,
753 DIST.attribute8,
754 DIST.attribute9,
755 DIST.attribute10,
756 DIST.amount_variance denom_raw_cost,
757 INV.invoice_currency_code denom_currency_code,
758 INV.exchange_date acct_rate_date,
759 INV.exchange_rate_type acct_rate_type,
760 INV.exchange_rate acct_exchange_rate,
761 Decode(nvl(VEND.employee_id, 0),0,NULL,
762 PA_UTILS.GetEmpJobId( VEND.employee_id,DIST.Expenditure_Item_Date)) Job_id,
763 VEND.employee_id employee_id,
764 VEND.vendor_id vendor_id,
765 INV.invoice_type_lookup_code,
766 INV.source,
767 INV.org_id,
768 INV.invoice_num invoice_number,
769 'IPV' cdl_system_reference4
770 -- 'IPV'
771 ,l_txn_src
772 ,l_user_txn_src
773 ,l_batch_name
774 ,l_interface_id
775 ,l_exp_end_date
776 ,l_txn_status_code
777 ,l_txn_rej_code
778 ,l_bus_grp_id
779 --,l_insert_flag
780 ,nvl(DIST.reversal_flag,'N') reversal_flag --NEW
781 ,nvl(DIST.cancellation_flag,'N') cancellation_flag --NEW
782 ,DIST.parent_reversal_id parent_reversal_id --NEW
783 ,l_net_zero_flag
784 ,l_sc_xfer_code
785 ,l_adj_exp_item_id
786 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(DIST.project_id, 'STD') fc_enabled
787 ,nvl(DIST.exchange_date, INV.invoice_date) mrc_exchange_date
788 ,'CMT' fc_document_type
789 ,'Y' si_assets_addition_flag
790 ,'Y' insert_flag
791 FROM PO_Vendors VEND,
792 AP_Invoices_all INV,
793 PA_Transfer_AP_Invoices_View DIST
794 WHERE DIST.Invoice_Id = INV.Invoice_Id
795 AND DIST.Pa_Addition_Flag = 'W'
796 AND INV.Vendor_Id = VEND.Vendor_Id
797 AND DIST.request_id = G_REQUEST_ID
798 ORDER BY INV.invoice_id
799 ,DIST.invoice_distribution_id
800 ;
801
802 PROCEDURE mark_inv_var_paflag;
803
804 PROCEDURE transfer_inv_var_to_pa;
805
806 PROCEDURE tieback_invoice_variances (
807 p_transaction_source IN pa_transaction_interface.transaction_source%TYPE,
808 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
809 p_interface_id IN pa_transaction_interface.interface_id%TYPE);
810
811 /* Added for Bug#3193740 */
812 FUNCTION increment_W_count(W_flag IN VARCHAR2)
813 RETURN VARCHAR2 ;
814
815 -- FUNCTION get_inv_amt_var(p_invoice_id in NUMBER,p_inv_dist_line_num in NUMBER)
816 -- RETURN NUMBER;
817
818 FUNCTION get_cdl_ccid(p_expenditure_item_id NUMBER, ccid_flag varchar2)
819 RETURN NUMBER;
820
821
822 /*===================================*/
823 /*Declarations for payment processing*/
824 /*===================================*/
825
826 CURSOR Payments_Cur IS
827 SELECT Pay.Invoice_Payment_Id,
828 INV.Invoice_id invoice_id,
829 INV.created_by created_by,
830 DIST.Invoice_distribution_id Invoice_distribution_id, --NEW
831 DIST.Invoice_Line_Number Invoice_Line_Number,
832 DIST.Project_id project_id,
833 DIST.Task_id task_id,
834 DIST.line_type_lookup_code,
835 DIST.Expenditure_type expenditure_type,
836 CASE WHEN inv.invoice_type_lookup_code = 'EXPENSE REPORT' THEN
837 DIST.Expenditure_Item_Date
838 ELSE
839 PA_AP_INTEGRATION.get_si_cost_exp_item_date(
840 chk.check_date,
841 pay.accounting_date,
842 dist.expenditure_item_date,
843 pay.creation_date,
844 NULL,
845 'PAYMENT'
846 )
847 END expenditure_item_date,
848 nvl(paydist.paid_base_amount,payDIST.amount) amount,
849 DIST.description description,
850 DIST.justification,
851 DIST.dist_code_combination_id dist_code_combination_id,
852 DIST.expenditure_organization_id expenditure_organization_id,
853 decode(DIST.line_type_lookup_code,'ERV',null,'IPV',null,
854 nvl(nvl(DECODE(TYPE.Cost_Rate_Flag, 'Y', (DIST.PA_Quantity * (PAYDIST.Paid_base_amount/PAYDIST.invoice_dist_base_amount)),
855 PAYDIST.Amount), PAYDIST.amount),0)) quantity,
856 L_pay_code_combination_id accts_pay_code_combination_id,
857 PAY.accounting_date gl_date,
858 DIST.attribute_category,
859 DIST.attribute1,
860 DIST.attribute2,
861 DIST.attribute3,
862 DIST.attribute4,
863 DIST.attribute5,
864 DIST.attribute6,
865 DIST.attribute7,
866 DIST.attribute8,
867 DIST.attribute9,
868 DIST.attribute10,
869 /* MC Upgrade */
870 DIST.receipt_currency_amount,
871 DIST.receipt_currency_code,
872 DIST.receipt_conversion_rate,
873 PAYDIST.amount denom_raw_cost,
874 INV.payment_currency_code denom_currency_code,
875 pay.exchange_date acct_rate_date,
876 pay.exchange_rate_type acct_rate_type,
877 pay.exchange_rate acct_exchange_rate,
878 Decode(nvl(VEND.employee_id, 0),0,NULL,
879 PA_UTILS.GetEmpJobId( VEND.employee_id,DIST.Expenditure_Item_Date)) Job_id,
880 VEND.employee_id employee_id,
881 VEND.vendor_id vendor_id,
882 /*DFF: Upgrade to call client extension*/
883 INV.invoice_type_lookup_code,
884 INV.source,
885 INV.org_id
886 ,INV.invoice_num invoice_number
887 ,l_cdl_system_reference4 cdl_system_reference4
888 ,DIST.po_distribution_id po_distribution_id
889 /*added the following dummy columns for pl/sql table upgrade*/
890 ,l_txn_src
891 ,l_user_txn_src
892 ,l_batch_name
893 ,l_interface_id
894 ,l_exp_end_date
895 ,l_txn_status_code
896 ,l_txn_rej_code
897 ,l_bus_grp_id
898 ,inv.paid_on_behalf_employee_id
899 /* IC Upgrade: Create a column which is used to group transactions
900 by their invoice sources */
901 ,DECODE(INV.source, 'Inter-Project Invoices', 1,
902 'Projects Intercompany Invoices',2, 3) sort_c
903 --,nvl(PAY.reversal_flag,'N') reversal_flag --NEW
904 ,DECODE(nvl(PAY.reversal_flag,'N'),'N',decode(nvl(DIST.reversal_flag,'N'),'Y','R','N'),PAY.reversal_flag) reversal_flag --Bug5408748
905 ,l_cancel_flag cancel_flag
906 --,PAY.REVERSAL_INV_PMT_ID REVERSAL_INV_PMT_ID --NEW
907 ,DECODE(PAY.REVERSAL_INV_PMT_ID,null,decode(nvl(PAY.reversal_flag,'N'),'N',DIST.parent_reversal_id),PAY.REVERSAL_INV_PMT_ID) REVERSAL_INV_PMT_ID --NEW
908 ,l_net_zero_flag
909 ,l_sc_xfer_code
910 ,l_adj_exp_item_id
911 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(DIST.project_id, 'STD') fc_enabled
912 ,nvl(DIST.exchange_date, INV.invoice_date) mrc_exchange_date
913 ,'ALL' fc_document_type
914 ,inv.PAYMENT_STATUS_FLAG
915 ,'Y' si_assets_addition_flag
916 ,'Y' insert_flag
917 , paydist.payment_hist_dist_id
918 , l_PRepay_dist_id
919 FROM AP_Invoice_Payments Pay,
920 ap_payment_hist_dists Paydist,
921 PO_Vendors VEND,
922 AP_Invoices INV,
923 Ap_Invoice_Distributions DIST,
924 PA_EXPENDITURE_TYPES TYPE,
925 ap_checks chk
926 WHERE DIST.Invoice_Id = INV.Invoice_Id
927 AND Pay.Invoice_Payment_Id = Paydist.Invoice_payment_id
928 AND Pay.check_id = chk.check_id
929 AND paydist.pay_dist_lookup_code = 'CASH'
930 AND Paydist.invoice_distribution_id = DIST.invoice_distribution_id
931 AND PayDIST.Pa_Addition_Flag = 'O'
932 AND INV.Vendor_Id = VEND.Vendor_Id
933 AND PAYDIST.request_id = G_REQUEST_ID
934 AND DIST.expenditure_type = TYPE.expenditure_type
935 ORDER BY INV.invoice_id, DIST.Invoice_Distribution_Id, PAY.invoice_payment_id;
936
937
938
939 /*==================================================================*/
940 /* Cursor for Prepayment Application Processing for Cash Basis Acctn*/
941 /*==================================================================*/
942
943 CURSOR prepay_cur IS
944 SELECT INV.Invoice_id invoice_id,
945 INV.created_by created_by,
946 PDIST.Invoice_distribution_id Invoice_distribution_id, --NEW
947 DIST1.Invoice_Line_Number Invoice_Line_Number,
948 DIST1.Project_id project_id,
949 DIST1.Task_id task_id,
950 decode(DIST1.line_type_lookup_code,'ITEM','PREPAY','ACCRUAL','PREPAY', DIST1.line_type_lookup_code) line_type_lookup_code,
951 --changed the source of line-type-lookup-code to process dta prorated for rec tax
952 -- DIST2.line_type_lookup_code, --PREPAY
953 DIST1.Expenditure_type expenditure_type,
954 to_char(DIST1.Expenditure_Item_Date, 'DD-MON-RR') expenditure_item_date,
955 (nvl(PDIST.base_amount,PDIST.amount) * -1) amount,
956 DIST2.description description,
957 DIST2.justification,
958 DIST1.dist_code_combination_id dist_code_combination_id,
959 DIST1.expenditure_organization_id expenditure_organization_id,
960 decode(DIST1.line_type_lookup_code,'ERV',null,'IPV',null,
961 nvl(nvl(DECODE(TYPE.Cost_Rate_Flag,'Y', (DIST1.PA_Quantity * (nvl(PDIST.amount,0)/nvl(DIST1.amount,0))),NVL(PDIST.Amount,PDIST.Base_Amount) * -1),
962 nvl( PDIST.amount,PDIST.base_amount ) * -1),0)) quantity, --removed the negitive amount for bug 5514129
963 L_pay_code_combination_id accts_pay_code_combination_id,
964 DIST2.accounting_date gl_date,
965 DIST1.attribute_category,
966 DIST1.attribute1,
967 DIST1.attribute2,
968 DIST1.attribute3,
969 DIST1.attribute4,
970 DIST1.attribute5,
971 DIST1.attribute6,
972 DIST1.attribute7,
973 DIST1.attribute8,
974 DIST1.attribute9,
975 DIST1.attribute10, /* MC Upgrade */
976 DIST1.receipt_currency_amount,
977 DIST1.receipt_currency_code,
978 DIST1.receipt_conversion_rate,
979 (nvl(PDIST.amount,0) * -1) denom_raw_cost,
980 INV.invoice_currency_code denom_currency_code,
981 INV.exchange_date acct_rate_date,
982 INV.exchange_rate_type acct_rate_type,
983 INV.exchange_rate acct_exchange_rate,
984 Decode(nvl(VEND.employee_id, 0),0,NULL,
985 PA_UTILS.GetEmpJobId( VEND.employee_id,DIST1.Expenditure_Item_Date)) Job_id,
986 VEND.employee_id employee_id,
987 VEND.vendor_id vendor_id,
988 INV.invoice_type_lookup_code,
989 INV.source,
990 INV.org_id ,
991 INV.invoice_num invoice_number ,
992 PDIST.prepay_app_dist_id cdl_system_reference4 ,
993 DIST1.po_distribution_id po_distribution_id /*added the following dummy columns for pl/sql table upgrade*/ ,
994 l_txn_src ,
995 l_user_txn_src ,
996 l_batch_name ,
997 l_interface_id ,
998 l_exp_end_date ,
999 l_txn_status_code ,
1000 l_txn_rej_code ,
1001 l_bus_grp_id ,
1002 inv.paid_on_behalf_employee_id,
1003 DECODE(INV.source, 'Inter-Project Invoices', 1, 'Projects Intercompany Invoices',2, 3) sort_c ,
1004 nvl(DIST2.reversal_flag,'N') reversal_flag
1005 ,nvl(DIST2.cancellation_flag,'N') cancellation_flag --NEW ??
1006 ,PDIST.reversed_prepay_app_dist_id parent_reversal_id --NEW
1007 ,l_net_zero_flag
1008 ,l_sc_xfer_code
1009 ,l_adj_exp_item_id
1010 ,Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(DIST1.project_id, 'STD') fc_enabled
1011 ,nvl(DIST2.exchange_date, INV.invoice_date) mrc_exchange_date
1012 ,'ALL' fc_document_type
1013 ,'Y' si_assets_addition_flag
1014 ,'Y' insert_flag
1015 ,l_pay_hist_id
1016 ,DIST2.PRepay_distribution_id PRepay_distribution_id
1017 FROM PO_Vendors VEND,
1018 AP_Invoices INV,
1019 AP_Prepay_APP_Dists PDIST,
1020 AP_INVOICE_DISTRIBUTIONS DIST1,
1021 AP_INVOICE_DISTRIBUTIONS DIST2,
1022 -- changed from view to table to process the rec tax part of prorated prepay appl for bug#5514129
1023 --PA_Transfer_AP_Invoices_View DIST1,
1024 -- PA_Transfer_AP_Invoices_View DIST2,
1025 PA_EXPENDITURE_TYPES TYPE
1026 WHERE DIST1.Invoice_Id = INV.Invoice_Id
1027 AND DIST1.invoice_distribution_id = PDIST.invoice_distribution_id -- Std inv line to which Prepay is applied
1028 AND DIST2.invoice_id = DIST1.invoice_id
1029 AND DIST2.invoice_distribution_id = PDIST.prepay_app_distribution_id --Prepay appl line
1030 --AND DIST2.line_type_lookup_code in ( 'PREPAY','NONREC_TAX')
1031 AND DIST1.line_type_lookup_code <> 'REC_TAX' --bug#5514129
1032 AND PDIST.Pa_Addition_Flag = 'O'
1033 AND INV.Vendor_Id = VEND.Vendor_Id
1034 AND PDIST.request_id = G_REQUEST_ID
1035 AND DIST1.expenditure_type = TYPE.expenditure_type
1036 ORDER BY sort_c, INV.invoice_id,DIST1.Invoice_distribution_id;
1037
1038
1039 PROCEDURE mark_PA_Pay_flag_O;
1040
1041
1042 PROCEDURE net_zero_pay_adjustment;
1043
1044 PROCEDURE transfer_pay_to_pa;
1045
1046 PROCEDURE tieback_payment_AP_ER (
1047 p_transaction_source IN pa_transaction_interface.transaction_source%TYPE,
1048 p_batch_name IN pa_transaction_interface.batch_name%TYPE,
1049 p_batch_type IN Varchar2,
1050 p_interface_id IN pa_transaction_interface.interface_id%TYPE);
1051
1052 FUNCTION check_prepay_fully_applied(p_prepay_dist_id in NUMBER)
1053 RETURN VARCHAR2;
1054
1055 PROCEDURE process_adjustments (p_record_type IN Varchar2,
1056 p_document_header_id IN number, /*Added this for 6945767 */
1057 p_document_distribution_id IN number,
1058 p_document_payment_id IN number DEFAULT NULL,
1059 p_current_index IN number,
1060 p_last_index IN OUT NOCOPY number) ;
1061
1062 -- Check discount method
1063 FUNCTION return_discount_method RETURN VARCHAR2;
1064
1065 END PAAPIMP_PKG;