[Home] [Help]
4:
5: PROCEDURE UPD_PA_DETAILS_SUPPLIER_MERGE
6: ( p_old_vendor_id IN po_vendors.vendor_id%type,
7: p_new_vendor_id IN po_vendors.vendor_id%type,
8: p_paid_inv_flag IN ap_invoices_all.PAYMENT_STATUS_FLAG%type,
9: p_invoice_id IN ap_invoices_all.invoice_id%TYPE DEFAULT NULL, /* Bug# 8845025 */
10: x_stage OUT NOCOPY VARCHAR2,
11: x_status OUT NOCOPY VARCHAR2)
12:
5: PROCEDURE UPD_PA_DETAILS_SUPPLIER_MERGE
6: ( p_old_vendor_id IN po_vendors.vendor_id%type,
7: p_new_vendor_id IN po_vendors.vendor_id%type,
8: p_paid_inv_flag IN ap_invoices_all.PAYMENT_STATUS_FLAG%type,
9: p_invoice_id IN ap_invoices_all.invoice_id%TYPE DEFAULT NULL, /* Bug# 8845025 */
10: x_stage OUT NOCOPY VARCHAR2,
11: x_status OUT NOCOPY VARCHAR2)
12:
13: IS
36: ,last_update_login =fnd_global.login_id
37: WHERE e.vendor_id = p_old_vendor_id and
38: orig_exp_txn_reference1 = nvl(p_invoice_id, orig_exp_txn_reference1) and -- Added nvl for the Bug 14387738
39: exists (
40: select 1 from ap_invoices_all i
41: where invoice_id = nvl(p_invoice_id, invoice_id) -- added nvl for the Bug 14387738
42: and to_char(invoice_id) = orig_exp_txn_reference1
43: and vendor_id = p_new_vendor_id
44: and payment_status_flag = DECODE (NVL (p_paid_inv_flag, 'Y'), 'N', 'N', i.payment_status_flag)
58: SELECT ---- /*+ LEADING(ei)
59: ei.expenditure_id
60: FROM pa_cost_distribution_lines_all c,
61: pa_expenditure_items_all ei,
62: ap_invoices_all i
63: WHERE TO_CHAR (i.invoice_id) = c.system_reference2
64: AND c.expenditure_item_id = ei.expenditure_item_id
65: -- AND ei.expenditure_id = e.expenditure_id
66: AND c.system_reference1 = TO_CHAR(p_old_vendor_id)
79: Where Vendor_Id = p_old_vendor_id
80: and exists
81: (select /*+ no_unnest */ 1
82: from pa_cost_distribution_lines_all c,
83: ap_invoices_all i
84: where i.invoice_id = to_number(c.system_reference2)
85: and c.expenditure_item_id = ei.expenditure_item_id
86: and c.system_reference1 = p_old_vendor_id
87: and i.vendor_id = p_new_vendor_id
109:
110: --Code change for 7125912
111: Declare Cursor c1 is
112: Select c.rowid row_id, c.expenditure_item_id, c.line_num
113: from pa_cost_distribution_lines_all c, ap_invoices_all i
114: where to_char(i.invoice_id) = c.system_reference2
115: --and i.vendor_id = to_number(c.system_reference1) --Vendor_ID on Invoice is already changed...so this is not needed
116: and c.system_reference1 = to_char(p_old_vendor_id)
117: and i.vendor_id = p_new_vendor_id
147: And system_reference1 is not null
148: And system_reference2 is not null
149: And system_reference3 is not null
150: and exists (select 1 -- added this for bug8562065
151: from ap_invoices_all inv
152: where to_char(inv.invoice_id) = cdl.system_reference2
153: and inv.vendor_id = p_new_vendor_id
154: );
155:
832: from po_distributions_all
833: where po_distribution_id = p_po_distribution_id ;
834:
835:
836: /* The cursor for getting all the variables to be passed to the patc.get_status proc. The use of ap_invoices_all is not done here as we dont have the invoice id being passed by AP */
837: cursor patc_cursor is
838: select
839: POD.project_id PROJECT_ID,
840: POD.task_id TASK_ID,