[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure InsertIntoRHI;
procedure InsertIntoRTI;
select 1
from rcv_headers_interface
where group_id = x_group_id
and processing_status_code = 'ERROR';
select 1
from rcv_transactions_interface
where group_id = x_group_id
and (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR');
select 1
from rcv_headers_interface
where group_id = x_group_id
and processing_status_code in ('PENDING','RUNNING');
select 1
from rcv_transactions_interface
where group_id = x_group_id
and (processing_status_code in ('PENDING','RUNNING') or transaction_status_code = 'ERROR');
select 1
from rcv_headers_interface
where group_id = x_group_id;
select 1
from po_interface_errors pie1,
rcv_headers_interface rhi
where rhi.group_id = x_group_id
and rhi.header_interface_id = pie1.interface_header_id
union all
select 1
from po_interface_errors pie2,
rcv_transactions_interface rti
where rti.group_id = x_group_id
and rti.interface_transaction_id = pie2.interface_line_id;
select plpn.line_number
from pos_exasn_lpns plpn
where parent_lpn is null
and quantity is null;
select plnt.line_number
from pos_exasn_lines plnt, pos_exasn_lots plot
where plnt.line_id = plot.line_id
group by plnt.line_number, plnt.quantity
having plnt.quantity <> sum(plot.quantity)
and sum(plot.quantity) > 0;
select plnt.line_number
from pos_exasn_lines plnt, pos_exasn_serials pst
where plnt.line_id = pst.line_id
group by plnt.line_number, plnt.quantity
having plnt.quantity <> sum(pst.quantity)
and sum(pst.quantity) > 0;
select plnt.line_number
from pos_exasn_lines plnt, pos_exasn_lpns plpn
where plnt.line_id = plpn.line_id
group by plnt.line_number, plnt.quantity
having plnt.quantity <> sum(plpn.quantity)
and sum(plpn.quantity) > 0;
select plot.line_number
from pos_exasn_lots plot, pos_exasn_serials pst
where plot.lot_id = pst.lot_id
group by plot.line_number, plot.quantity
having plot.quantity <> sum(pst.quantity);
select line_number, lot_number, license_plate_number, po_line_loc_id,
lot_attribute_category, cattribute1, cattribute2, cattribute3,
cattribute4, cattribute5, cattribute6, cattribute7, cattribute8,
cattribute9, cattribute10, cattribute11, cattribute12, cattribute13,
cattribute14, cattribute15, cattribute16, cattribute17, cattribute18,
cattribute19, cattribute20,
dattribute1, dattribute2, dattribute3, dattribute4, dattribute5,
dattribute6, dattribute7, dattribute8, dattribute9, dattribute10,
nattribute1, nattribute2, nattribute3, nattribute4, nattribute5,
nattribute6, nattribute7, nattribute8, nattribute9, nattribute10,
grade_code, origination_date, date_code, status_id, change_date,
age, retest_date, maturity_date, item_size, color, volume,
volume_uom, place_of_origin, best_by_date, length, length_uom,
recycled_content, thickness, thickness_uom, width, width_uom,
territory_code, supplier_lot_number, vendor_name
from pos_exasn_lots;
select line_number, from_serial, to_serial, quantity, license_plate_number, po_line_loc_id, lot_number
from pos_exasn_serials;
select line_number, license_plate_number, parent_lpn, po_line_loc_id
from pos_exasn_lpns;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
--Update lot info of Serial Records which are children of Lot Records
update pos_exasn_serials pst
set pst.lot_number = (select plot.lot_number
from pos_exasn_lots plot
where plot.header_id = pst.header_id
and plot.line_id = pst.line_id
and plot.lot_id = pst.lot_id);
update pos_exasn_lpns plpn
set (plpn.po_line_loc_id) = (select plnt.po_line_location_id
from pos_exasn_lines plnt
where plnt.header_id = plpn.header_id
and plnt.line_id = plpn.line_id);
update pos_exasn_lots plot
set (plot.po_line_loc_id) = (select plnt.po_line_location_id
from pos_exasn_lines plnt
where plnt.header_id = plot.header_id
and plnt.line_id = plot.line_id);
update pos_exasn_serials pst
set (pst.po_line_loc_id) = (select plnt.po_line_location_id
from pos_exasn_lines plnt
where plnt.header_id = pst.header_id
and plnt.line_id = pst.line_id);
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Unexpected error when validating Lot Number at line: '||l_lot_ln||':'||l_lot_return_msg, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Error while validating Serial Range/Quantity at line: '||l_ser_ln||':'||l_ser_return_msg, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Error while validating License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, 'Error while validating Parent License Plate Number at line: '||l_lpn_ln||':'||l_lpn_return_msg, x_error_pointer)=1) then
raise l_25errors;
select plnt.line_number, plnt.uom
from pos_exasn_lines plnt
where not exists
(select 1 from por_unit_of_measure_lov_v puomv
where puomv.unit_of_measure = plnt.uom);
select plnt.line_number, plnt.country_of_origin
from pos_exasn_lines plnt
where plnt.country_of_origin is not null
and not exists
(select 1 from fnd_territories_vl ftv
where ftv.territory_code = plnt.country_of_origin);
select line_number, operating_unit
from pos_exasn_lines
where org_id is null;
select
plnt.line_number,
plnt.po_number,
plnt.po_revision,
plnt.po_line,
plnt.po_shipment,
plnt.operating_unit
from pos_exasn_lines plnt
where plnt.po_header_id is null
and plnt.po_release_num is null;
select
plnt.line_number,
plnt.po_number,
plnt.po_release_num,
plnt.po_revision,
plnt.po_shipment,
plnt.operating_unit
from pos_exasn_lines plnt
where plnt.po_header_id is null
and plnt.po_release_num is not null;
select line_number,vendor_id
from pos_exasn_lines;
select line_number
from pos_exasn_lines plnt
where not exists(
select 1
from ak_web_user_sec_attr_values
WHERE web_user_id = x_user_id
AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
AND attribute_application_id = 177
and number_value = plnt.vendor_site_id);
select line_number
from pos_exasn_lines plnt
where not exists(
select 1
from ak_web_user_sec_attr_values
WHERE web_user_id = x_user_id
AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
AND attribute_application_id = 177
and number_value = plnt.vendor_contact_id);
select line_number, quantity, uom, po_line_location_id, item_id, header_id
from pos_exasn_lines;
select line_number, shipment_number
from
(
select pht.line_number, pht.shipment_number
from pos_exasn_lines plnt,
rcv_headers_interface rhi,
pos_exasn_headers pht
where pht.header_id = plnt.header_id
and pht.shipment_number = rhi.shipment_num
and plnt.vendor_id = rhi.vendor_id
and nvl(plnt.vendor_site_id, -9999) = nvl(rhi.vendor_site_id, -9999)
union
select plnt.line_number, pht.shipment_number
from pos_exasn_lines plnt,
rcv_shipment_headers rsh,
pos_exasn_headers pht
where pht.header_id = plnt.header_id
and pht.shipment_number = rsh.shipment_num
and plnt.vendor_id = rsh.vendor_id
and nvl(plnt.vendor_site_id, -9999) = nvl(rsh.vendor_site_id, -9999))
order by line_number;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
update pos_exasn_lines plnt
set plnt.org_id = (
select hou.organization_id
from hr_operating_units hou
where hou.name = plnt.operating_unit);
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
update pos_exasn_lines plnt
set (
po_header_id,
po_line_id,
po_line_location_id,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_org_id,
vendor_name,
vendor_site_code,
rate_type,
rate,
rate_date,
item_id,
item_revision,
unit_price,
vendor_product_num,
currency_code,
primary_uom,
ship_to_location_code,
ship_to_location_id,
item_description
)
=
(
select
pha.po_header_id,
pla.po_line_id,
plla.line_location_id,
pv.vendor_id,
pvs.vendor_site_id,
pha.vendor_contact_id,
plla.ship_to_organization_id,
pv.vendor_name,
pvs.vendor_site_code,
pha.rate_type,
pha.rate,
pha.rate_date,
pla.item_id,
pla.item_revision,
pla.unit_price,
pla.vendor_product_num,
pha.currency_code,
pla.unit_meas_lookup_code,
nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
plla.ship_to_location_id,
pla.item_description
from
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_vendors pv,
po_vendor_sites_all pvs,
hr_locations_all_tl hrl,
hz_locations hz
where pha.segment1 = plnt.po_number
and pha.org_id = plnt.org_id
and pha.revision_num = plnt.po_revision
and pha.po_header_id = pla.po_header_id
and pla.line_num = plnt.po_line
and pla.po_line_id = plla.po_line_id
and plla.shipment_num = plnt.po_shipment
and pv.vendor_id = pha.vendor_id
and pvs.vendor_site_id = pha.vendor_site_id
and hrl.location_id(+) = plla.ship_to_location_id
and hrl.LANGUAGE(+) = USERENV('LANG')
and hz.location_id(+) = plla.ship_to_location_id
and pha.type_lookup_code = 'STANDARD'
and pha.AUTHORIZATION_STATUS = 'APPROVED'
and NVL(plla.approved_flag, 'N') = 'Y'
and NVL(plla.CANCEL_FLAG, 'N') = 'N'
and NVL(pha.FROZEN_FLAG, 'N') = 'N'
and NVL(pha.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
)
where org_id is not null
and po_release_num is null;
select count(1) into l_error_ln from pos_exasn_lines where po_line_location_id is null;
update pos_exasn_lines plnt
set (
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_org_id,
vendor_name,
vendor_site_code,
rate_type,
rate,
rate_date,
item_id,
item_revision,
unit_price,
vendor_product_num,
currency_code,
primary_uom,
ship_to_location_code,
ship_to_location_id,
item_description
)
=
(
select
pha.po_header_id,
pra.po_release_id,
pla.po_line_id,
plla.line_location_id,
pv.vendor_id,
pvs.vendor_site_id,
pha.vendor_contact_id,
plla.ship_to_organization_id,
pv.vendor_name,
pvs.vendor_site_code,
pha.rate_type,
pha.rate,
pha.rate_date,
pla.item_id,
pla.item_revision,
pla.unit_price,
pla.vendor_product_num,
pha.currency_code,
pla.unit_meas_lookup_code,
nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)),
plla.ship_to_location_id,
pla.item_description
from
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
po_line_locations_all plla,
po_vendors pv,
po_vendor_sites_all pvs,
hr_locations_all_tl hrl,
hz_locations hz
where pha.segment1 = plnt.po_number
and pha.org_id = plnt.org_id
and pha.po_header_id = pra.po_header_id
and pra.release_num = plnt.po_release_num
and pra.revision_num = plnt.po_revision
and pra.po_release_id = plla.po_release_id
and plla.shipment_num = plnt.po_shipment
and pha.vendor_id = pv.vendor_id
and pha.vendor_site_id = pvs.vendor_site_id
and hrl.location_id(+) = plla.ship_to_location_id
and hrl.LANGUAGE(+) = USERENV('LANG')
and hz.location_id(+) = plla.ship_to_location_id
and pla.po_line_id = plla.po_line_id
and pra.AUTHORIZATION_STATUS = 'APPROVED'
and NVL(plla.approved_flag, 'N') = 'Y'
and NVL(plla.CANCEL_FLAG, 'N') = 'N'
and NVL(pra.FROZEN_FLAG, 'N') = 'N'
and NVL(pra.CONSIGNED_CONSUMPTION_FLAG, 'N') <> 'Y'
and NVL(plla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED')
)
where org_id is not null
and po_release_num is not null;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
select
PLL.qty_rcv_exception_code,
PLL.receipt_days_exception_code,
PLL.DAYS_EARLY_RECEIPT_ALLOWED,
PLL.DAYS_LATE_RECEIPT_ALLOWED,
NVL(PLL.PROMISED_DATE,PLL.NEED_BY_DATE),
PLL.outsourced_assembly
into
l_qty_rcv_exception_code,
l_receipt_days_exception_code,
l_days_early,
l_days_late,
l_due_date,
l_outsourced_assembly
from po_line_locations_all PLL
where line_location_id = l_po_line_location_id;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
select
expected_receipt_date,
line_number
into
l_exp_rec_date,
l_header_line_number
from pos_exasn_headers
where header_id = l_header_id;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) then
raise l_25errors;
select pht.line_number, pht.gross_weight_uom
from pos_exasn_headers pht
where pht.gross_weight_uom is not null
and not exists
(select 1 from por_unit_of_measure_lov_v puomv
where puomv.unit_of_measure = pht.gross_weight_uom);
select pht.line_number, pht.net_weight_uom
from pos_exasn_headers pht
where pht.net_weight_uom is not null
and not exists
(select 1 from por_unit_of_measure_lov_v puomv
where puomv.unit_of_measure = pht.net_weight_uom);
select pht.line_number, pht.tar_weight_uom
from pos_exasn_headers pht
where pht.tar_weight_uom is not null
and not exists
(select 1 from por_unit_of_measure_lov_v puomv
where puomv.unit_of_measure = pht.tar_weight_uom);
select pht.line_number, pht.freight_terms
from pos_exasn_headers pht
where pht.freight_terms is not null
and not exists
(select 1 from po_lookup_codes plc
where plc.lookup_type = 'FREIGHT TERMS'and sysdate < nvl(plc.inactive_date, sysdate + 1)
and plc.lookup_code = pht.freight_terms);
select pht.line_number, shipment_date
from pos_exasn_headers pht
where shipment_date > sysdate;
select pht.line_number, pht.payment_terms
from pos_exasn_headers pht
where pht.payment_terms is not null
and not exists
(select 1 from ap_terms_val_v av
where av.name = pht.payment_terms);
select pht.line_number
from pos_exasn_headers pht
where expected_receipt_date < shipment_date;
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
update pos_exasn_headers
set gross_weight_uom =
(select puomv.uom_code
from por_unit_of_measure_lov_v puomv
where gross_weight_uom is not null
and gross_weight_uom = puomv.unit_of_measure);
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
update pos_exasn_headers
set net_weight_uom =
(select puomv.uom_code
from por_unit_of_measure_lov_v puomv
where net_weight_uom is not null
and net_weight_uom = puomv.unit_of_measure);
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
update pos_exasn_headers
set tar_weight_uom =
(select puomv.uom_code
from por_unit_of_measure_lov_v puomv
where tar_weight_uom is not null
and tar_weight_uom = puomv.unit_of_measure);
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
if(InsertError(p_error_tbl, fnd_message.get, p_error_pointer)=1) then
raise l_25errors;
procedure UpdateLinesAndLls(x_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
l_error_pointer in out NOCOPY number)
IS
l_uom pos_exasn_lines.uom%type;
select line_number, quantity, uom, po_line_location_id, item_id, line_id
from pos_exasn_lines;
-- Update All lines with Primary Quantity and Invoiced Quantity
open l_allLines_csr;
if(InsertError(x_error_tbl, 'Unexpected Error while finding primary quantity for new lines', l_error_pointer)=1) then
null;
update pos_exasn_lines
set primary_quantity = l_primary_qty
where line_id = l_line_id;
update pos_exasn_lines
set invoiced_quantity = POS_QUANTITIES_S.get_invoice_qty
(l_po_line_location_id,
l_uom,
l_item_id,
l_quantity)
where line_id = l_line_id;
update pos_exasn_lines plnt
set lpn_group_id = (select pht.lpn_group_id
from pos_exasn_headers pht
where pht.header_id = plnt.header_id)
where
(
plnt.lls_code in ('LAS','LOT')
and exists(
select 1 from pos_exasn_lots plot
where plot.line_id = plnt.line_id
and plot.license_plate_number is not null))
or
(
plnt.lls_code = 'LPN'
and exists(
select 1 from pos_exasn_lpns plpn
where plpn.line_id = plnt.line_id
and plpn.quantity is not null))
or
(
plnt.lls_code = 'SER'
and exists(
select 1 from pos_exasn_serials pst
where pst.line_id = plnt.line_id
and pst.license_plate_number is not null));
update pos_exasn_lots plot
set (plot.product_transaction_id, plot.uom) =(select plnt.interface_transaction_id, plnt.uom
from pos_exasn_lines plnt
where plnt.header_id = plot.header_id
and plnt.line_id = plot.line_id);
update pos_exasn_serials pst
set (pst.product_transaction_id) = (select plnt.interface_transaction_id
from pos_exasn_lines plnt
where plnt.header_id = pst.header_id
and plnt.line_id = pst.line_id);
END UpdateLinesAndLls;
select
distinct
license_plate_number
from pos_exasn_serials
where header_id = x_header_id
and line_id = x_line_id
and lot_id = 0;
select
plnt.header_id,
plnt.line_id,
plnt.quantity
from pos_exasn_lines plnt
where lls_code = 'SER';
and (select count(distinct nvl(license_plate_number,'null'))
from pos_exasn_serials pst
where pst.line_id = plnt.line_id) >1;
select max(line_id)+1 into l_new_line from pos_exasn_lines;
update pos_exasn_lines
set license_plate_number = l_ser_lpn
where line_id = l_line_id;
update pos_exasn_lines plnt
set plnt.quantity =
(select sum(quantity)
from pos_exasn_serials pst
where pst.header_id = plnt.header_id
and pst.line_id = plnt.line_id
and pst.license_plate_number is null)
where plnt.line_id = l_line_id;
update pos_exasn_lines plnt
set plnt.quantity =
(select sum(quantity)
from pos_exasn_serials pst
where pst.header_id = plnt.header_id
and pst.line_id = plnt.line_id
and pst.license_plate_number = l_ser_lpn)
where plnt.line_id = l_line_id;
select sum(quantity)
into l_ser_lpn_sum
from pos_exasn_serials
where header_id = l_ln_header_id
and line_id = l_line_id
and license_plate_number is null;
select sum(quantity)
into l_ser_lpn_sum
from pos_exasn_serials
where header_id = l_ln_header_id
and line_id = l_line_id
and license_plate_number = l_ser_lpn;
update pos_exasn_serials
set line_id = l_new_line
where line_id = l_line_id
and license_plate_number is null;
update pos_exasn_serials
set line_id = l_new_line
where line_id = l_line_id
and license_plate_number = l_ser_lpn;
select sum(quantity)
into l_total_ser_qty
from pos_exasn_serials
where header_id = l_ln_header_id
and line_id >= l_line_id;
update pos_exasn_lines plnt
set license_plate_number = (select distinct pst2.license_plate_number
from pos_exasn_serials pst2
where pst2.line_id = plnt.line_id
and pst2.license_plate_number is not null)
where lls_code = 'SER'
and (select count(distinct pst.license_plate_number)
from pos_exasn_serials pst
where pst.line_id = plnt.line_id
and pst.license_plate_number is not null) = 1;
select
distinct
license_plate_number
from pos_exasn_lpns
where header_id = x_header_id
and line_id = x_line_id
and quantity is not null;
select
plnt.header_id,
plnt.line_id,
plnt.quantity
from pos_exasn_lines plnt
where lls_code = 'LPN';
and (select count(distinct nvl(license_plate_number,'null'))
from pos_exasn_lpns plpn
where plpn.line_id = plnt.line_id
and quantity is null) >1;
select max(line_id)+1 into l_new_line from pos_exasn_lines;
update pos_exasn_lines
set license_plate_number = l_lpn
where line_id = l_line_id;
update pos_exasn_lines plnt
set plnt.quantity = (select plpn.quantity
from pos_exasn_lpns plpn
where plpn.header_id = plnt.header_id
and plpn.line_id = plnt.line_id
and plpn.license_plate_number = l_lpn
and plpn.quantity is not null)
where plnt.line_id = l_line_id;
select plpn.quantity into l_lpn_sum
from pos_exasn_lpns plpn
where plpn.license_plate_number = l_lpn
and plpn.header_id = l_ln_header_id
and plpn.line_id = l_line_id
and plpn.quantity is not null;
update pos_exasn_lpns
set line_id = l_new_line
where line_id = l_line_id
and license_plate_number = l_lpn;
select sum(quantity)
into l_total_lpn_qty
from pos_exasn_lpns
where header_id = l_ln_header_id
and line_id >= l_line_id;
update pos_exasn_lines plnt
set license_plate_number = (select distinct plpn2.license_plate_number
from pos_exasn_lpns plpn2
where plpn2.line_id = plnt.line_id
and plpn2.license_plate_number is not null)
where lls_code = 'LPN'
and (select count(distinct plpn.license_plate_number)
from pos_exasn_lpns plpn
where plpn.line_id = plnt.line_id
and plpn.license_plate_number is not null) = 1;
select
distinct
license_plate_number
from pos_exasn_lots
where header_id = x_header_id
and line_id = x_line_id;
select
plnt.header_id,
plnt.line_id,
plnt.quantity
from pos_exasn_lines plnt
where lls_code in ('LOT','LAS');
and (select count(distinct nvl(license_plate_number,'null'))
from pos_exasn_lots plot
where plot.line_id = plnt.line_id) >1;
select max(line_id)+1 into l_new_line from pos_exasn_lines;
update pos_exasn_lines
set license_plate_number = l_lot_lpn
where line_id = l_line_id;
update pos_exasn_lines plnt
set plnt.quantity =
(select sum(quantity)
from pos_exasn_lots plot
where plot.header_id = plnt.header_id
and plot.line_id = plnt.line_id
and plot.license_plate_number is null)
where plnt.line_id = l_line_id;
update pos_exasn_lines plnt
set plnt.quantity =
(select sum(quantity)
from pos_exasn_lots plot
where plot.header_id = plnt.header_id
and plot.line_id = plnt.line_id
and plot.license_plate_number = l_lot_lpn)
where plnt.line_id = l_line_id;
select sum(quantity)
into l_lot_lpn_sum
from pos_exasn_lots
where header_id = l_ln_header_id
and line_id = l_line_id
and license_plate_number is null;
select sum(quantity)
into l_lot_lpn_sum
from pos_exasn_lots
where header_id = l_ln_header_id
and line_id = l_line_id
and license_plate_number = l_lot_lpn;
update pos_exasn_lots
set line_id = l_new_line
where line_id = l_line_id
and license_plate_number is null;
update pos_exasn_serials
set line_id = l_new_line
where lot_id in(select lot_id
from pos_exasn_lots
where line_id = l_new_line
and license_plate_number is null);
update pos_exasn_lots
set line_id = l_new_line
where line_id = l_line_id
and license_plate_number = l_lot_lpn;
update pos_exasn_serials
set line_id = l_new_line
where lot_id in (select lot_id
from pos_exasn_lots
where line_id = l_new_line
and license_plate_number = l_lot_lpn);
select sum(quantity)
into l_total_lot_qty
from pos_exasn_lots
where header_id = l_ln_header_id
and line_id >= l_line_id;--All lines created at this moment, with line_id > l_line_id is for l_line_id
update pos_exasn_lines plnt
set license_plate_number = (select distinct plot2.license_plate_number
from pos_exasn_lots plot2
where plot2.line_id = plnt.line_id
and plot2.license_plate_number is not null)
where lls_code = 'LOT'
and (select count(distinct plot.license_plate_number)
from pos_exasn_lots plot
where plot.line_id = plnt.line_id
and plot.license_plate_number is not null) = 1;
procedure InsertIntoLLS(x_return_status out nocopy varchar2,
p_error_tbl in out nocopy POS_EXCELASN_ERROR_TABLE,
p_error_pointer in out nocopy number)
is
cursor l_allLots_csr
is
select
plot.lot_id,
plot.transaction_interface_id,
plot.lot_number,
plot.quantity,
plot.po_line_loc_id,
plot.uom,
plot.license_plate_number,
plnt.lpn_group_id,
plot.line_number,
plot.product_transaction_id,
plot.vendor_id,
plot.grade_code,
plot.origination_date,
plot.date_code,
plot.status_id,
plot.change_date,
plot.age,
plot.retest_date,
plot.maturity_date,
plot.item_size,
plot.color,
plot.volume,
plot.volume_uom,
plot.place_of_origin,
plot.best_by_date,
plot.length,
plot.length_uom,
plot.recycled_content,
plot.thickness,
plot.thickness_uom,
plot.width,
plot.width_uom,
plot.curl_wrinkle_fold,
plot.supplier_lot_number,
plot.territory_code,
plot.vendor_name,
plot.LOT_ATTRIBUTE_CATEGORY,
plot.CATTRIBUTE1,
plot.CATTRIBUTE2,
plot.CATTRIBUTE3,
plot.CATTRIBUTE4,
plot.CATTRIBUTE5,
plot.CATTRIBUTE6,
plot.CATTRIBUTE7,
plot.CATTRIBUTE8,
plot.CATTRIBUTE9,
plot.CATTRIBUTE10,
plot.CATTRIBUTE11,
plot.CATTRIBUTE12,
plot.CATTRIBUTE13,
plot.CATTRIBUTE14,
plot.CATTRIBUTE15,
plot.CATTRIBUTE16,
plot.CATTRIBUTE17,
plot.CATTRIBUTE18,
plot.CATTRIBUTE19,
plot.CATTRIBUTE20,
plot.DATTRIBUTE1,
plot.DATTRIBUTE2,
plot.DATTRIBUTE3,
plot.DATTRIBUTE4,
plot.DATTRIBUTE5,
plot.DATTRIBUTE6,
plot.DATTRIBUTE7,
plot.DATTRIBUTE8,
plot.DATTRIBUTE9,
plot.DATTRIBUTE10,
plot.NATTRIBUTE1,
plot.NATTRIBUTE2,
plot.NATTRIBUTE3,
plot.NATTRIBUTE4,
plot.NATTRIBUTE5,
plot.NATTRIBUTE6,
plot.NATTRIBUTE7,
plot.NATTRIBUTE8,
plot.NATTRIBUTE9,
plot.NATTRIBUTE10
from pos_exasn_lots plot,
pos_exasn_lines plnt
where plot.line_id = plnt.line_id;
select
pst.from_serial,
pst.to_serial,
pst.po_line_loc_id,
pst.transaction_interface_id,
pst.license_plate_number,
plnt.lpn_group_id,
pst.line_number,
pst.product_transaction_id,
pst.origination_date,
pst.status_id,
pst.territory_code,
pst.SERIAL_ATTRIBUTE_CATEGORY,
pst.CATTRIBUTE1,
pst.CATTRIBUTE2,
pst.CATTRIBUTE3,
pst.CATTRIBUTE4,
pst.CATTRIBUTE5,
pst.CATTRIBUTE6,
pst.CATTRIBUTE7,
pst.CATTRIBUTE8,
pst.CATTRIBUTE9,
pst.CATTRIBUTE10,
pst.CATTRIBUTE11,
pst.CATTRIBUTE12,
pst.CATTRIBUTE13,
pst.CATTRIBUTE14,
pst.CATTRIBUTE15,
pst.CATTRIBUTE16,
pst.CATTRIBUTE17,
pst.CATTRIBUTE18,
pst.CATTRIBUTE19,
pst.CATTRIBUTE20,
pst.DATTRIBUTE1,
pst.DATTRIBUTE2,
pst.DATTRIBUTE3,
pst.DATTRIBUTE4,
pst.DATTRIBUTE5,
pst.DATTRIBUTE6,
pst.DATTRIBUTE7,
pst.DATTRIBUTE8,
pst.DATTRIBUTE9,
pst.DATTRIBUTE10,
pst.NATTRIBUTE1,
pst.NATTRIBUTE2,
pst.NATTRIBUTE3,
pst.NATTRIBUTE4,
pst.NATTRIBUTE5,
pst.NATTRIBUTE6,
pst.NATTRIBUTE7,
pst.NATTRIBUTE8,
pst.NATTRIBUTE9,
pst.NATTRIBUTE10
from pos_exasn_serials pst,
pos_exasn_lines plnt
where pst.line_id = plnt.line_id;
select
plpn.license_plate_number,
plpn.po_line_loc_id,
plpn.parent_lpn,
plnt.lpn_group_id,
plpn.line_number
from pos_exasn_lpns plpn,
pos_exasn_lines plnt
where plnt.line_id = plpn.line_id;
pos_asn_create_pvt.insert_mtli(
p_api_version => 1.0
, x_return_status => l_lot_status
, x_msg_count => l_lot_msg_count
, x_msg_data => l_lot_msg_data
, p_transaction_interface_id => l_txn_intf_id
, p_lot_number => l_lot_number
, p_transaction_quantity => l_qty
, p_transaction_uom => l_txn_uom
, p_po_line_loc_id => l_po_line_loc_id
, x_serial_transaction_temp_id => l_serial_txn_temp_id
, p_product_transaction_id => l_pdt_txn_id
, p_vendor_id => l_vendor_id
, p_grade_Code => l_grade_code
, p_origination_date => l_origination_date
, p_date_code => l_date_code
, p_status_id => l_status_id
, p_change_date => l_change_date
, p_age => l_age
, p_retest_date => l_retest_date
, p_maturity_date => l_maturity_date
, p_item_size => l_item_size
, p_color => l_color
, p_volume => l_volume
, p_volume_uom => l_volume_uom
, p_place_of_origin => l_place_of_origin
, p_best_by_date => l_best_by_date
, p_length => l_length
, p_length_uom => l_length_uom
, p_recycled_content => l_recycled_content
, p_thickness => l_thickness
, p_thickness_uom => l_thickness_uom
, p_width => l_width
, p_width_uom => l_width_uom
, p_curl_wrinkle_fold => l_curl_wrinkle_fold
, p_supplier_lot_number => l_supplier_lot_number
, p_territory_code => l_territory_code
, p_vendor_name => l_vendor_name
, p_lot_attribute_category => l_LOT_ATTRIBUTE_CATEGORY
, p_c_attribute1 => l_CATTRIBUTE1
, p_c_attribute2 => l_CATTRIBUTE2
, p_c_attribute3 => l_CATTRIBUTE3
, p_c_attribute4 => l_CATTRIBUTE4
, p_c_attribute5 => l_CATTRIBUTE5
, p_c_attribute6 => l_CATTRIBUTE6
, p_c_attribute7 => l_CATTRIBUTE7
, p_c_attribute8 => l_CATTRIBUTE8
, p_c_attribute9 => l_CATTRIBUTE9
, p_c_attribute10 => l_CATTRIBUTE10
, p_c_attribute11 => l_CATTRIBUTE11
, p_c_attribute12 => l_CATTRIBUTE12
, p_c_attribute13 => l_CATTRIBUTE13
, p_c_attribute14 => l_CATTRIBUTE14
, p_c_attribute15 => l_CATTRIBUTE15
, p_c_attribute16 => l_CATTRIBUTE16
, p_c_attribute17 => l_CATTRIBUTE17
, p_c_attribute18 => l_CATTRIBUTE18
, p_c_attribute19 => l_CATTRIBUTE19
, p_c_attribute20 => l_CATTRIBUTE20
, p_d_attribute1 => l_DATTRIBUTE1
, p_d_attribute2 => l_DATTRIBUTE2
, p_d_attribute3 => l_DATTRIBUTE3
, p_d_attribute4 => l_DATTRIBUTE4
, p_d_attribute5 => l_DATTRIBUTE5
, p_d_attribute6 => l_DATTRIBUTE6
, p_d_attribute7 => l_DATTRIBUTE7
, p_d_attribute8 => l_DATTRIBUTE8
, p_d_attribute9 => l_DATTRIBUTE9
, p_d_attribute10 => l_DATTRIBUTE10
, p_n_attribute1 => l_NATTRIBUTE1
, p_n_attribute2 => l_NATTRIBUTE2
, p_n_attribute3 => l_NATTRIBUTE3
, p_n_attribute4 => l_NATTRIBUTE4
, p_n_attribute5 => l_NATTRIBUTE5
, p_n_attribute6 => l_NATTRIBUTE6
, p_n_attribute7 => l_NATTRIBUTE7
, p_n_attribute8 => l_NATTRIBUTE8
, p_n_attribute9 => l_NATTRIBUTE9
, p_n_attribute10 => l_NATTRIBUTE10
);
if(InsertError(p_error_tbl, 'Error while inserting Lot at line '||l_lot_ln, p_error_pointer)=1) then
raise l_25errors;
update pos_exasn_lots
set TRANSACTION_INTERFACE_ID = l_txn_intf_id, serial_transaction_temp_id = l_serial_txn_temp_id
where lot_id = l_lot_id;
pos_asn_create_pvt.insert_wlpni
( p_api_version => 1.0
, x_return_status => l_lpn_status
, x_msg_count => l_lpn_msg_count
, x_msg_data => l_lpn_msg_data
, p_po_line_loc_id => l_po_line_loc_id
, p_license_plate_number => l_lpn
, p_LPN_GROUP_ID => l_lpn_group_id
, p_PARENT_LICENSE_PLATE_NUMBER => null
);
if(InsertError(p_error_tbl, 'Error while inserting LPN at line '||l_lot_ln, p_error_pointer)=1) then
raise l_25errors;
--update
update pos_exasn_serials pst
set pst.transaction_interface_id =
(select plot.serial_transaction_temp_id
from pos_exasn_lots plot
where plot.lot_id = pst.lot_id);
pos_asn_create_pvt.insert_msni (
p_api_version => 1.0
, x_return_status => l_ser_status
, x_msg_count => l_ser_msg_count
, x_msg_data => l_ser_msg_data
, p_transaction_interface_id => l_ser_intf_id
, p_fm_serial_number => l_fm_serial
, p_to_serial_number => l_to_serial
, p_po_line_loc_id => l_po_line_loc_id
, p_product_transaction_id => l_pdt_txn_id
, p_origination_date => l_ser_origination_date
, p_status_id => l_ser_status_id
, p_territory_code => l_ser_territory_code
, p_serial_attribute_category => l_SERIAL_ATTRIBUTE_CATEGORY
, p_c_attribute1 => l_CATTRIBUTE1
, p_c_attribute2 => l_CATTRIBUTE2
, p_c_attribute3 => l_CATTRIBUTE3
, p_c_attribute4 => l_CATTRIBUTE4
, p_c_attribute5 => l_CATTRIBUTE5
, p_c_attribute6 => l_CATTRIBUTE6
, p_c_attribute7 => l_CATTRIBUTE7
, p_c_attribute8 => l_CATTRIBUTE8
, p_c_attribute9 => l_CATTRIBUTE9
, p_c_attribute10 => l_CATTRIBUTE10
, p_c_attribute11 => l_CATTRIBUTE11
, p_c_attribute12 => l_CATTRIBUTE12
, p_c_attribute13 => l_CATTRIBUTE13
, p_c_attribute14 => l_CATTRIBUTE14
, p_c_attribute15 => l_CATTRIBUTE15
, p_c_attribute16 => l_CATTRIBUTE16
, p_c_attribute17 => l_CATTRIBUTE17
, p_c_attribute18 => l_CATTRIBUTE18
, p_c_attribute19 => l_CATTRIBUTE19
, p_c_attribute20 => l_CATTRIBUTE20
, p_d_attribute1 => l_DATTRIBUTE1
, p_d_attribute2 => l_DATTRIBUTE2
, p_d_attribute3 => l_DATTRIBUTE3
, p_d_attribute4 => l_DATTRIBUTE4
, p_d_attribute5 => l_DATTRIBUTE5
, p_d_attribute6 => l_DATTRIBUTE6
, p_d_attribute7 => l_DATTRIBUTE7
, p_d_attribute8 => l_DATTRIBUTE8
, p_d_attribute9 => l_DATTRIBUTE9
, p_d_attribute10 => l_DATTRIBUTE10
, p_n_attribute1 => l_NATTRIBUTE1
, p_n_attribute2 => l_NATTRIBUTE2
, p_n_attribute3 => l_NATTRIBUTE3
, p_n_attribute4 => l_NATTRIBUTE4
, p_n_attribute5 => l_NATTRIBUTE5
, p_n_attribute6 => l_NATTRIBUTE6
, p_n_attribute7 => l_NATTRIBUTE7
, p_n_attribute8 => l_NATTRIBUTE8
, p_n_attribute9 => l_NATTRIBUTE9
, p_n_attribute10 => l_NATTRIBUTE10
);
if(InsertError(p_error_tbl, 'Error while inserting Serial at line '||l_ser_ln, p_error_pointer)=1) then
raise l_25errors;
pos_asn_create_pvt.insert_wlpni
( p_api_version => 1.0
, x_return_status => l_lpn_status
, x_msg_count => l_lpn_msg_count
, x_msg_data => l_lpn_msg_data
, p_po_line_loc_id => l_po_line_loc_id
, p_license_plate_number => l_lpn
, p_LPN_GROUP_ID => l_lpn_group_id
, p_PARENT_LICENSE_PLATE_NUMBER => null
);
if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_ser_ln, p_error_pointer)=1) then
raise l_25errors;
pos_asn_create_pvt.insert_wlpni
( p_api_version => 1.0
, x_return_status => l_lpn_status
, x_msg_count => l_lpn_msg_count
, x_msg_data => l_lpn_msg_data
, p_po_line_loc_id => l_po_line_loc_id
, p_license_plate_number => l_lpn
, p_LPN_GROUP_ID => l_lpn_group_id
, p_PARENT_LICENSE_PLATE_NUMBER => l_parent_lpn
);
if(InsertError(p_error_tbl, 'Error while inserting Lpn at line '||l_lpn_ln, p_error_pointer)=1) then
raise l_25errors;
if(InsertError(p_error_tbl, 'Unexpected Error in InsertIntoLLS', p_error_pointer)=1) then
null;
end InsertIntoLLS;
function InsertError(p_error_tbl in out NOCOPY POS_EXCELASN_ERROR_TABLE,
p_error_msg in varchar2,
p_error_index in out NOCOPY number)
return number
is
begin
p_error_tbl.extend(1);
end InsertError;
select HEADER_ID from
(
select
count(1), pht.header_id HEADER_ID, pht.shipment_number SHIPMENT_NUMBER, plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code
from pos_exasn_headers pht,
pos_exasn_lines plnt
where pht.header_id = plnt.header_id
group by pht.header_id, pht.shipment_number, plnt.vendor_id, plnt.ship_to_org_id, nvl(plnt.vendor_site_id, -9999), plnt.currency_code
)
group by HEADER_ID
having count(1) > 1;
select distinct plnt.vendor_id VENDOR_ID, plnt.ship_to_org_id SHIP_TO_ORG_ID, nvl(plnt.vendor_site_id, -9999) VENDOR_SITE_ID, plnt.currency_code CURRENCY_CODE
from pos_exasn_lines plnt
where header_id = x_header_Id;
select peh.line_number
from pos_exasn_headers peh,
po_vendor_sites_all pvsa
where pvsa.vendor_site_id = peh.vendor_site_id
AND getvendorpaysiteid(peh.vendor_id,nvl(peh.vendor_site_id, -9999),peh.currency_code) IS null;
select pht.line_number, pht.freight_carrier_code
from pos_exasn_headers pht
where pht.freight_carrier_code is not null
and 0=
(select count(*) from org_freight oft
where nvl(oft.disable_date, sysdate) >= sysdate
and oft.freight_code = pht.freight_carrier_code
and organization_id = pht.ship_to_org_id
);
select pht.line_number,pht.ship_from_location_code
from pos_exasn_headers pht
where pht.ship_from_location_code is not null
and not exists (
select 1 from hz_party_sites ps,hz_party_site_uses psu,po_vendors pov
where ps.party_site_id = psu.party_site_id
and psu.site_use_type = 'SUPPLIER_SHIP_FROM'
and ps.party_id = pov.party_id
and pov.vendor_id= pht.vendor_id
and substr(ps.party_site_number,1,instr(ps.party_site_number,'|')-1) = pht.ship_from_location_code);
select max(header_id) into l_asn_header_id from pos_exasn_headers;
select decode(count(1),0,'ASN','ASBN')
into l_asn_asbn
from pos_exasn_headers
where invoice_number is not null;
select line_number
into l_error_ln
from pos_exasn_headers
where header_id = l_ex_header_id;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_early_exit;
update pos_exasn_lines
set header_id = l_asn_header_id
where header_id = l_ex_header_id
and vendor_id = l_ex_vendor_id
and ship_to_org_id = l_ex_ship_to_org_id
and vendor_site_id = l_ex_vendor_site_id;
update pos_exasn_headers pht
set (
pht.vendor_id,
pht.ship_to_org_id,
pht.vendor_site_id,
pht.currency_code,
pht.rate,
pht.rate_type,
pht.rate_date
)
=
(select
plnt.vendor_id,
plnt.ship_to_org_id,
plnt.vendor_site_id,
plnt.currency_code,
plnt.rate,
plnt.rate_type,
plnt.rate_date
from pos_exasn_lines plnt
where plnt.header_id = pht.header_id
and plnt.line_id =
(select min(plnt2.line_id)
from pos_exasn_lines plnt2
where plnt2.header_id = pht.header_id)
);
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_early_exit;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_early_exit;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_early_exit;
-- Update Payment Term ID, if any
update pos_exasn_headers pht
set payment_term_id =
(select atv.term_id
from ap_terms_val_v atv
where atv.name = pht.payment_terms)
where payment_terms is not null;
update pos_exasn_lines plnt
set (header_interface_id, group_id, expected_receipt_date) = (select pht.header_interface_id, pht.group_id, pht.expected_receipt_date
from pos_exasn_headers pht
where pht.header_id = plnt.header_id);
insert into pos_exasn_lines(line_id, quantity, license_plate_number) values(p_line_id, p_qty, p_lpn );
select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_intf_txn_id from dual;
update pos_exasn_lines
set (
PRIMARY_UOM,
LPN_GROUP_ID,
EXPECTED_RECEIPT_DATE,
HEADER_INTERFACE_ID,
INTERFACE_TRANSACTION_ID,
GROUP_ID,
HEADER_ID,
OPERATING_UNIT,
PO_NUMBER,
PO_REVISION,
PO_RELEASE_NUM,
PO_LINE,
PO_SHIPMENT,
ORG_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
SHIP_TO_ORG_ID,
VENDOR_NAME,
VENDOR_SITE_CODE,
RATE_TYPE,
RATE,
RATE_DATE,
ITEM_ID,
ITEM_REVISION,
UNIT_PRICE,
CURRENCY_CODE,
VENDOR_PRODUCT_NUM,
UOM,
BILL_OF_LADING,
PACKING_SLIP,
NUM_OF_CONTAINERS,
WAYBILL_NUM,
BARCODE_LABEL,
COUNTRY_OF_ORIGIN,
CONTAINER_NUMBER,
TRUCK_NUMBER,
VENDOR_LOT,
COMMENTS,
LINE_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SH_ATTRIBUTE_CATEGORY,
SH_ATTRIBUTE1,
SH_ATTRIBUTE2,
SH_ATTRIBUTE3,
SH_ATTRIBUTE4,
SH_ATTRIBUTE5,
SH_ATTRIBUTE6,
SH_ATTRIBUTE7,
SH_ATTRIBUTE8,
SH_ATTRIBUTE9,
SH_ATTRIBUTE10,
SH_ATTRIBUTE11,
SH_ATTRIBUTE12,
SH_ATTRIBUTE13,
SH_ATTRIBUTE14,
SH_ATTRIBUTE15,
SL_ATTRIBUTE_CATEGORY,
SL_ATTRIBUTE1,
SL_ATTRIBUTE2,
SL_ATTRIBUTE3,
SL_ATTRIBUTE4,
SL_ATTRIBUTE5,
SL_ATTRIBUTE6,
SL_ATTRIBUTE7,
SL_ATTRIBUTE8,
SL_ATTRIBUTE9,
SL_ATTRIBUTE10,
SL_ATTRIBUTE11,
SL_ATTRIBUTE12,
SL_ATTRIBUTE13,
SL_ATTRIBUTE14,
SL_ATTRIBUTE15,
SHIP_TO_LOCATION_CODE,
SHIP_TO_LOCATION_ID,
LLS_CODE,
ITEM_DESCRIPTION
)
= (
select
PRIMARY_UOM,
LPN_GROUP_ID,
EXPECTED_RECEIPT_DATE,
HEADER_INTERFACE_ID,
l_intf_txn_id,
GROUP_ID,
HEADER_ID,
OPERATING_UNIT,
PO_NUMBER,
PO_REVISION,
PO_RELEASE_NUM,
PO_LINE,
PO_SHIPMENT,
ORG_ID,
PO_HEADER_ID,
PO_RELEASE_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
SHIP_TO_ORG_ID,
VENDOR_NAME,
VENDOR_SITE_CODE,
RATE_TYPE,
RATE,
RATE_DATE,
ITEM_ID,
ITEM_REVISION,
UNIT_PRICE,
CURRENCY_CODE,
VENDOR_PRODUCT_NUM,
UOM,
BILL_OF_LADING,
PACKING_SLIP,
NUM_OF_CONTAINERS,
WAYBILL_NUM,
BARCODE_LABEL,
COUNTRY_OF_ORIGIN,
CONTAINER_NUMBER,
TRUCK_NUMBER,
VENDOR_LOT,
COMMENTS,
LINE_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SH_ATTRIBUTE_CATEGORY,
SH_ATTRIBUTE1,
SH_ATTRIBUTE2,
SH_ATTRIBUTE3,
SH_ATTRIBUTE4,
SH_ATTRIBUTE5,
SH_ATTRIBUTE6,
SH_ATTRIBUTE7,
SH_ATTRIBUTE8,
SH_ATTRIBUTE9,
SH_ATTRIBUTE10,
SH_ATTRIBUTE11,
SH_ATTRIBUTE12,
SH_ATTRIBUTE13,
SH_ATTRIBUTE14,
SH_ATTRIBUTE15,
SL_ATTRIBUTE_CATEGORY,
SL_ATTRIBUTE1,
SL_ATTRIBUTE2,
SL_ATTRIBUTE3,
SL_ATTRIBUTE4,
SL_ATTRIBUTE5,
SL_ATTRIBUTE6,
SL_ATTRIBUTE7,
SL_ATTRIBUTE8,
SL_ATTRIBUTE9,
SL_ATTRIBUTE10,
SL_ATTRIBUTE11,
SL_ATTRIBUTE12,
SL_ATTRIBUTE13,
SL_ATTRIBUTE14,
SL_ATTRIBUTE15,
SHIP_TO_LOCATION_CODE,
SHIP_TO_LOCATION_ID,
LLS_CODE,
ITEM_DESCRIPTION
from pos_exasn_lines
where line_id = p_old_ln)
where line_id = p_line_id;
select rcv_interface_groups_s.nextval into l_lpn_group_id from dual;
select rcv_headers_interface_s.nextval into l_header_interface_id from dual;
insert into pos_exasn_headers(
header_id,
lpn_group_id,
vendor_id,
ship_to_org_id,
vendor_site_id,
header_interface_id
)
values(
p_asn_header_id,
l_lpn_group_id,
p_ex_vendor_id,
p_ex_ship_to_org_id,
p_ex_vendor_site_id,
l_header_interface_id);
update pos_exasn_headers
set (
PAYMENT_TERM_ID,
CURRENCY_CODE,
RATE,
RATE_TYPE,
RATE_DATE,
ASN_REQUEST_ID ,
GROUP_ID,
SHIPMENT_NUMBER,
SHIPMENT_DATE,
EXPECTED_RECEIPT_DATE,
BILL_OF_LADING,
PACKING_SLIP,
FREIGHT_CARRIER_CODE,
NUM_OF_CONTAINERS,
WAYBILL_NUM,
GROSS_WEIGHT_UOM,
GROSS_WEIGHT,
NET_WEIGHT_UOM,
NET_WEIGHT,
TAR_WEIGHT_UOM,
TAR_WEIGHT,
PACKAGING_CODE,
CARRIER_METHOD,
SPECIAL_HANDLING_CODE,
HAZARD_CODE,
HAZARD_CLASS,
FREIGHT_TERMS,
COMMENTS,
INVOICE_NUMBER,
INVOICE_DATE,
INVOICE_AMOUNT ,
TAX_AMOUNT,
FREIGHT_AMOUNT ,
PAYMENT_TERMS,
LINE_NUMBER,
ATTRIBUTE_CATEGORY,
SHIP_FROM_LOCATION_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) = (select
PAYMENT_TERM_ID,
CURRENCY_CODE,
RATE,
RATE_TYPE,
RATE_DATE,
ASN_REQUEST_ID ,
GROUP_ID,
SHIPMENT_NUMBER,
SHIPMENT_DATE,
EXPECTED_RECEIPT_DATE,
BILL_OF_LADING,
PACKING_SLIP,
FREIGHT_CARRIER_CODE,
NUM_OF_CONTAINERS,
WAYBILL_NUM,
GROSS_WEIGHT_UOM,
GROSS_WEIGHT,
NET_WEIGHT_UOM,
NET_WEIGHT,
TAR_WEIGHT_UOM,
TAR_WEIGHT,
PACKAGING_CODE,
CARRIER_METHOD,
SPECIAL_HANDLING_CODE,
HAZARD_CODE,
HAZARD_CLASS,
FREIGHT_TERMS,
COMMENTS,
INVOICE_NUMBER,
INVOICE_DATE,
INVOICE_AMOUNT ,
TAX_AMOUNT,
FREIGHT_AMOUNT ,
PAYMENT_TERMS,
LINE_NUMBER,
ATTRIBUTE_CATEGORY,
SHIP_FROM_LOCATION_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
from pos_exasn_headers
where header_id = p_ex_header_id)
where header_id = p_asn_header_id;
select distinct plnt.line_number
from pos_exasn_lines plnt,
pos_exasn_lots plot
where plnt.lls_code not in ('LOT','LAS')
and plnt.line_id = plot.line_id;
select distinct plnt.line_number
from pos_exasn_lines plnt,
pos_exasn_serials pst
where plnt.lls_code not in ('SER','LAS')
and plnt.line_id = pst.line_id;
select
LPN, LINE_NUM from
(
select
a1.license_plate_number LPN,
a1.line_number LINE_NUM
from
pos_exasn_lpns a1,
pos_exasn_lines ln
where a1.line_id = ln.line_id
and ln.lls_code in ('LOT','LAS')
and not exists
( select 1
from pos_exasn_lots t
where t.line_id = a1.line_id
and t.license_plate_number = a1.license_plate_number)
and not exists
( select 1
from pos_exasn_lpns a2
where a2.line_id = a1.line_id
and a2.parent_lpn = a1.license_plate_number)
union all
select
a1.license_plate_number LPN,
a1.line_number LINE_NUM
from
pos_exasn_lpns a1,
pos_exasn_lines ln
where a1.line_id = ln.line_id
and ln.lls_code = 'SER'
and not exists
( select 1
from pos_exasn_serials s
where s.line_id = a1.line_id
and s.license_plate_number = a1.license_plate_number)
and not exists
( select 1
from pos_exasn_lpns a2
where a2.line_id = a1.line_id
and a2.parent_lpn = a1.license_plate_number)
union all
-- if there is quantity defined, the LPN must not be defined as parent on some other line
select
a1.license_plate_number LPN,
a1.line_number LINE_NUM
from
pos_exasn_lpns a1,
pos_exasn_lines ln
where a1.line_id = ln.line_id
and ln.lls_code = 'LPN'
and a1.quantity is not null
and exists ( select 1
from pos_exasn_lpns p
where p.line_id = a1.line_id
and a1.quantity is not null
and p.parent_lpn = a1.license_plate_number)
union all
--If there is no quantity defined, the LPN line must define child-parent relationship
select
a1.license_plate_number LPN,
a1.line_number LINE_NUM
from
pos_exasn_lpns a1,
pos_exasn_lines ln
where a1.line_id = ln.line_id
and ln.lls_code = 'LPN'
and a1.quantity is null
and not exists ( select 1
from pos_exasn_lpns a2
where a2.line_id = a1.line_id
and a1.quantity is null
and a2.parent_lpn = a1.license_plate_number)
);
update pos_exasn_lines plnt
set plnt.lls_Code = 'LAS'
where exists(
select /*+ INDEX (msi, mtl_system_items_b_u1) */
1 from mtl_system_items msi
where msi.inventory_item_id = plnt.item_id
and msi.organization_id = plnt.org_id
and msi.lot_control_code = 2
and msi.serial_number_control_code in (2,5));
update pos_exasn_lines plnt
set plnt.lls_Code = 'LOT'
where plnt.lls_code is null
and exists(
select /*+ INDEX (msi, mtl_system_items_b_u1) */
1 from mtl_system_items msi
where msi.inventory_item_id = plnt.item_id
and msi.organization_id = plnt.org_id
and msi.lot_control_code = 2
and msi.serial_number_control_code not in (2,5));
update pos_exasn_lines plnt
set plnt.lls_Code = 'SER'
where plnt.lls_code is null
and exists(
select /*+ INDEX (msi, mtl_system_items_b_u1) */
1 from mtl_system_items msi
where msi.inventory_item_id = plnt.item_id
and msi.organization_id = plnt.org_id
and msi.lot_control_code = 1
and msi.serial_number_control_code in (2,5));
update pos_exasn_lines plnt
set plnt.lls_Code = 'LPN'
where plnt.lls_code is null;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_25errors;
procedure InsertIntoRTI
is
begin
insert into rcv_transactions_interface
( INTERFACE_TRANSACTION_ID ,
HEADER_INTERFACE_ID ,
GROUP_ID ,
TRANSACTION_TYPE ,
TRANSACTION_DATE ,
PROCESSING_STATUS_CODE ,
PROCESSING_MODE_CODE ,
TRANSACTION_STATUS_CODE ,
AUTO_TRANSACT_CODE ,
RECEIPT_SOURCE_CODE ,
SOURCE_DOCUMENT_CODE ,
PO_HEADER_ID ,
PO_LINE_ID ,
PO_LINE_LOCATION_ID ,
QUANTITY ,
PRIMARY_QUANTITY ,
UNIT_OF_MEASURE ,
PRIMARY_UNIT_OF_MEASURE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ITEM_ID ,
ITEM_REVISION ,
EXPECTED_RECEIPT_DATE ,
COMMENTS ,
BARCODE_LABEL ,
CONTAINER_NUM ,
COUNTRY_OF_ORIGIN_CODE ,
VENDOR_ITEM_NUM ,
VENDOR_LOT_NUM ,
TRUCK_NUM ,
NUM_OF_CONTAINERS ,
PACKING_SLIP ,
VALIDATION_FLAG ,
WIP_ENTITY_ID ,
WIP_LINE_ID ,
WIP_OPERATION_SEQ_NUM ,
PO_DISTRIBUTION_ID ,
DOCUMENT_LINE_NUM ,
DOCUMENT_SHIPMENT_LINE_NUM ,
VENDOR_ID ,
VENDOR_SITE_ID ,
QUANTITY_INVOICED ,
SHIP_TO_LOCATION_CODE ,
SHIP_TO_LOCATION_ID ,
PO_RELEASE_ID,
license_plate_number,
lpn_group_id,
document_num,
item_description)
select
interface_transaction_id,
header_interface_id,
group_id,
'SHIP',
sysdate,
'PENDING',
'BATCH',
'PENDING',
'SHIP',
'VENDOR',
'PO',
po_header_id,
po_line_id,
po_line_location_id,
quantity,
primary_quantity,
uom,
primary_uom,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
item_id,
item_revision,
expected_receipt_date,
comments,
barcode_label,
container_number,
country_of_origin,
vendor_product_num,
vendor_lot,
truck_number,
num_of_containers,
packing_slip,
'Y',
null,--wip stuff ???
null,--wip stuff
null,--wip stuff
null,--wip stuff
po_line,
po_shipment,
vendor_id,
vendor_site_id,
null, -- invoiced amount???
ship_to_location_code,
ship_to_location_id,
po_release_id,
license_plate_number,
lpn_group_id,
po_number,
item_description
from pos_exasn_lines;
end InsertIntoRTI;
procedure InsertIntoRHI
is
begin
insert into rcv_headers_interface
(HEADER_INTERFACE_ID ,
GROUP_ID ,
PROCESSING_STATUS_CODE ,
RECEIPT_SOURCE_CODE ,
TRANSACTION_TYPE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
SHIP_TO_ORGANIZATION_ID ,
VENDOR_ID ,
VENDOR_SITE_ID ,
SHIPPED_DATE ,
ASN_TYPE ,
SHIPMENT_NUM ,
EXPECTED_RECEIPT_DATE ,
PACKING_SLIP ,
WAYBILL_AIRBILL_NUM ,
BILL_OF_LADING ,
FREIGHT_CARRIER_CODE ,
FREIGHT_TERMS ,
NUM_OF_CONTAINERS ,
COMMENTS ,
CARRIER_METHOD ,
CARRIER_EQUIPMENT ,
PACKAGING_CODE ,
SPECIAL_HANDLING_CODE ,
INVOICE_NUM ,
INVOICE_DATE ,
TOTAL_INVOICE_AMOUNT ,
FREIGHT_AMOUNT ,
TAX_NAME ,
TAX_AMOUNT ,
CURRENCY_CODE ,
CONVERSION_RATE_TYPE ,
CONVERSION_RATE ,
CONVERSION_RATE_DATE ,
PAYMENT_TERMS_ID ,
PAYMENT_TERMS_NAME ,
VALIDATION_FLAG,
GROSS_WEIGHT_UOM_CODE,
GROSS_WEIGHT,
TAR_WEIGHT_UOM_CODE,
TAR_WEIGHT,
NET_WEIGHT_UOM_CODE,
NET_WEIGHT,
REMIT_TO_SITE_ID,
SHIP_FROM_LOCATION_CODE
)
select
header_interface_id,
group_id,
'PENDING',
'VENDOR',
'NEW',
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
ship_to_org_id,
vendor_id,
vendor_site_id,
shipment_date,
decode(INVOICE_NUMBER,null,'ASN','ASBN'),
shipment_number,
expected_receipt_date,
packing_slip,
waybill_num,
bill_of_lading,
freight_carrier_code,
freight_terms,
num_of_containers,
comments,
carrier_method,
null,
packaging_code,
special_handling_code,
invoice_number,
invoice_date,
invoice_amount,
freight_amount,
null,
tax_amount,
currency_code,
rate_type,
rate,
rate_date,
payment_term_id,
payment_terms,
'Y',
gross_weight_uom,
gross_weight,
tar_weight_uom,
tar_weight,
net_weight_uom,
net_weight,
decode(invoice_number,null,null,getvendorpaysiteid(vendor_id,nvl(vendor_site_id,-9999),currency_code)),
ship_from_location_code
from pos_exasn_headers;
end InsertIntoRHI;
SELECT NUMBER_VALUE
FROM AK_WEB_USER_SEC_ATTR_VALUES
WHERE WEB_USER_ID = FND_GLOBAL.USER_ID
AND ATTRIBUTE_CODE = 'ICX_SUPPLIER_ORG_ID'
AND ATTRIBUTE_APPLICATION_ID = 177;
select 1
from ak_resp_security_attributes arsa
WHERE arsa.responsibility_id = p_resp_id
AND arsa.attribute_application_id = 177
and arsa.attribute_code = 'ICX_SUPPLIER_ORG_ID';
select 1
from ak_resp_security_attributes arsa
WHERE arsa.responsibility_id = p_resp_id
AND arsa.attribute_application_id = 177
and arsa.attribute_code = 'ICX_SUPPLIER_SITE_ID';
select 1
from ak_resp_security_attributes arsa
WHERE arsa.responsibility_id = p_resp_id
AND arsa.attribute_application_id = 177
and arsa.attribute_code = 'ICX_SUPPLIER_CONTACT_ID';
SELECT number_value
FROM ak_web_user_sec_attr_values
WHERE web_user_id = p_user_id
AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
AND attribute_application_id = 177;
SELECT number_value
FROM ak_web_user_sec_attr_values
WHERE web_user_id = p_user_id
AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
AND attribute_application_id = 177;
IF(InsertError(x_error_tbl, fnd_message.get, x_error_pointer)=1) THEN
null;
SELECT number_value
into l_num
FROM AK_RESP_SECURITY_ATTR_VALUES
WHERE responsibility_id = fnd_global.resp_id
AND attribute_application_id = 177
AND attribute_code = 'ICX_SUPPLIER_SITE_ID'
AND number_value = -9999;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer) = 1) then
null;
SELECT number_value
into l_num
FROM AK_RESP_SECURITY_ATTR_VALUES
WHERE responsibility_id = fnd_global.resp_id
AND attribute_application_id = 177
AND attribute_code = 'ICX_SUPPLIER_CONTACT_ID'
AND number_value = -9999;
if(InsertError(x_error_tbl, fnd_message.get, x_error_pointer) = 1) then
null;
if(InsertError(x_error_tbl, 'Unknown exception when checking Securing Attributes:'||sqlerrm, x_error_pointer) = 1) then
null;
l_insertlls_status varchar2(1);
select plpn1.line_number, plpn2.line_number
from pos_exasn_lpns plpn1, pos_exasn_lpns plpn2,
pos_exasn_lines plnt1, pos_exasn_lines plnt2
where plpn1.license_plate_number = plpn2.license_plate_number
and plpn1.parent_lpn <> plpn2.parent_lpn
and plnt1.line_id = plpn1.line_id
and plnt2.line_id = plpn2.line_id
and plnt1.org_id = plnt2.org_id ;
select SHIPMENT_NUMBER
from pos_Exasn_headers
group by SHIPMENT_NUMBER, nvl(VENDOR_ID,-9999), nvl(VENDOR_SITE_ID,-9999)
having count(1) > 1;
select SHIPMENT_NUMBER, VENDOR_ID, VENDOR_SITE_ID
from pos_exasn_headers;
Select /*+ USE_NL(pht,plt,ps) LEADING(pht) */
pht.header_id,
plt.po_line_id,
plt.po_line_location_id,
pht.ship_from_location_code,
ps.location_id as ship_from_location_id
from pos_exasn_headers pht,
pos_exasn_lines plt,
hz_party_sites ps
where pht.header_id = plt.header_id
and pht.ship_from_location_code is not null
and ps.party_site_number = pht.ship_from_location_code||'|'||pht.vendor_id
order by pht.header_id;
SELECT FND_GLOBAL.USER_NAME INTO l_user_name FROM DUAL;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl,l_err_tbl(l_error_pointer), l_error_pointer)=1) then
raise l_25errors;
if(InsertError(x_error_tbl, fnd_message.get, l_error_pointer)=1) then
raise l_25errors;
UpdateLinesAndLls(x_error_tbl,l_error_pointer);
--For LAS, need to update Child Serial in many ways ==> CreateRTI4Lot
if(x_error_tbl.count > 0) then
raise l_early_exp;
InsertIntoRHI;
InsertIntoRTI;
InsertIntoLLS(l_insertlls_status, x_error_tbl, l_error_pointer);
if(InsertError(x_error_tbl, 'Unexpected error in ProcessExcelAsn:'||x_progress||':'||sqlerrm, l_error_pointer)=1) then
null;
SELECT org_id
INTO l_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id= p_vendor_site_id;
select vendor_site_id
into l_vendor_site_id
from PO_VENDOR_SITES_ALL PVS
where
SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
AND PVS.org_id = l_org_id
AND PVS.vendor_id = p_vendor_id
AND PVS.primary_pay_site_flag = 'Y'
AND ROWNUM=1;
select vendor_site_id
into l_vendor_site_id
from PO_VENDOR_SITES_ALL PVS
WHERE
SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
AND PVS.org_id = l_org_id
AND PVS.vendor_id = p_vendor_id
AND PVS.vendor_site_id = p_vendor_site_id
AND PVS.pay_site_flag = 'Y'
AND ROWNUM=1;
select default_pay_site_id
into l_DEFAULT_PAY_SITE_ID
from PO_VENDOR_SITES_ALL PVS
where PVS.org_id = l_org_id
AND PVS.vendor_id = p_vendor_id
AND PVS.vendor_site_id = p_vendor_site_id
AND ROWNUM=1;
select vendor_site_id
into l_vendor_site_id
from PO_VENDOR_SITES_ALL PVS
where org_id = l_org_id
AND PVS.vendor_id = p_vendor_id
AND PVS.vendor_site_id = l_DEFAULT_PAY_SITE_ID
AND PVS.pay_site_flag = 'Y'
AND ROWNUM=1;
SELECT VENDOR_SITE_ID
into l_vendor_site_id
FROM (select
ABA.CURRENCY_CODE,
PVS.VENDOR_ID,
PVS.VENDOR_SITE_ID,
PVS.VENDOR_SITE_CODE,
PVS.PAYMENT_METHOD_LOOKUP_CODE,
NVL(PVS.ORG_ID, -99) ORG_ID
FROM
AP_BANK_ACCOUNT_USES_ALL ABAU,
AP_BANK_ACCOUNTS_ALL ABA,
AP_BANK_BRANCHES ABB,
PO_VENDOR_SITES_ALL PVS
WHERE
ABAU.EXTERNAL_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID(+) AND
ABA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID(+) AND
ABAU.VENDOR_ID(+) = PVS.VENDOR_ID AND
ABAU.VENDOR_SITE_ID(+) = PVS.VENDOR_SITE_ID AND
NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y' AND
(NVL(ABAU.PRIMARY_FLAG, 'N') = 'Y' OR ABAU.BANK_ACCOUNT_USES_ID is null) AND
PVS.PAYMENT_METHOD_LOOKUP_CODE = 'EFT' AND
SYSDATE < NVL(ABB.END_DATE, SYSDATE+1) AND
SYSDATE < NVL(ABA.INACTIVE_DATE, SYSDATE+1)
UNION
select
NULL CURRENCY_CODE,
PVS.VENDOR_ID,
PVS.VENDOR_SITE_ID,
PVS.VENDOR_SITE_CODE,
PVS.PAYMENT_METHOD_LOOKUP_CODE,
NVL(PVS.ORG_ID, -99) ORG_ID
FROM
PO_VENDOR_SITES_ALL PVS
WHERE
SYSDATE < NVL(PVS.INACTIVE_DATE, SYSDATE+1)
AND NVL(PVS.PAY_SITE_FLAG, 'N') = 'Y' AND
PVS.PAYMENT_METHOD_LOOKUP_CODE <> 'EFT') QRSLT WHERE (ORG_ID = NVL(l_org_id, -99) AND VENDOR_ID = p_vendor_id AND
DECODE(PAYMENT_METHOD_LOOKUP_CODE, 'EFT', CURRENCY_CODE, p_currency_code) = p_currency_code AND ROWNUM=1);