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