CREATE OR REPLACE package APPS.cux_po_detail_pkg as
/******************************************************************************
name: emrsn_po_detail_pkg
purpose:
revisions: 0
ver 1
date MAR-02-2009
author Randolph
description
******************************************************************************/
TYPE cur_po IS REF CURSOR;
procedure get_po_header_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_org_code IN varchar2 default null);
procedure get_po_line_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_line_num in number default null,p_shipment_num in number default null,p_org_code IN varchar2 default null);
procedure get_po_osp_header_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_org_code IN varchar2 default null);
procedure get_po_osp_line_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_line_num in number default null,p_shipment_num in number default null,p_org_code IN varchar2 default null);
procedure get_po_bpa_header_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_release_num IN number default null,p_org_code IN varchar2 default null);
procedure get_po_bpa_line_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_release_num IN number default null,p_line_num in number default null,p_shipment_num in number default null,p_org_code IN varchar2 default null);
procedure get_po_bpa_osp_header_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_release_num IN number default null,p_org_code IN varchar2 default null);
procedure get_po_bpa_osp_line_data(o_po_cursor OUT cur_po,p_po_no IN varchar2 default null,p_release_num IN number default null,p_line_num in number default null,p_shipment_num in number default null,p_org_code IN varchar2 default null);
function get_uom_conversion(p_org_id in number,p_item_id in number,p_source_uom in varchar2) return number;
procedure get_rcv_receiving_data(o_rcv_cursor OUT cur_po,p_rcv_no IN number default null,p_org_id in number default null);
procedure get_rcv_inspection_data(o_rcv_cursor OUT cur_po,p_rcv_no_f IN number default null,p_rcv_no_t IN number default null,p_org_id in number default null,p_vendor_id in number default null,p_po_number_f in number default null,p_po_number_t in number default null);
end ;
/CREATE OR REPLACE PACKAGE BODY APPS.cux_po_detail_pkg
AS
PROCEDURE get_po_header_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
--po_header_id, revision_num, po_number,0 RELEASE_NUM,
SELECT 0 header_interface_id, currency_code, comments, bill_to,
ship_to, vendor_id, vendor_number, vendor, employee_number,
buyer, '' user_id, waybill_airbill_num, bill_of_lading,
organization_code, organization_name,
reference_invoice_number, ' ' succ_flag
FROM apps.cux_po_detail_view
WHERE NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND organization_code = NVL (p_org_code, organization_code)
GROUP BY --po_header_id,
--po_number,
--revision_num,
currency_code,
comments,
bill_to,
ship_to,
vendor_id,
vendor_number,
vendor,
buyer,
employee_number,
waybill_airbill_num,
bill_of_lading,
organization_code,
organization_name,
reference_invoice_number;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
function get_uom_conversion(p_org_id in number,p_item_id in number,p_source_uom in varchar2) return number
is
l_item_base_uom varchar2(240);
l_item_base_code varchar2(3);
l_item_uom_class varchar2(240);
l_source_uom_class varchar2(240);
l_rate number;
begin
begin
SELECT msi.PRIMARY_UOM_CODE,msi.PRIMARY_UNIT_OF_MEASURE,UOM_CLASS
into l_item_base_code,l_item_base_uom,l_item_uom_class
FROM apps.mtl_system_items msi
,apps.mtl_units_of_measure_vl muom
where msi.PRIMARY_UOM_CODE=muom.UOM_CODE
and msi.inventory_item_id =p_item_id
and msi.organization_id=p_org_id;
exception
when no_data_found then
l_item_base_code :='';
l_item_base_uom:='';
l_item_uom_class:='-1';
l_rate:=-1;
end;
begin
select UOM_CLASS
into l_source_uom_class
from apps.mtl_units_of_measure_vl uom
where uom.UNIT_OF_MEASURE=p_source_uom
and LANGUAGE=userenv('LANG');
exception
when no_data_found then
l_source_uom_class:='-1';
l_rate:=-1;
end;
if l_source_uom_class<>'-1' and l_item_uom_class<>'-1' then
if l_item_uom_class=l_source_uom_class and l_item_base_uom=p_source_uom then
l_rate:=1;
elsif l_item_uom_class=l_source_uom_class then
begin
select CONVERSION_RATE
into l_rate
from apps.MTL_UOM_CONVERSIONS muc
where muc.INVENTORY_ITEM_ID=p_item_id
and UNIT_OF_MEASURE=p_source_uom;
exception
when no_data_found then
l_rate:=-1;
end;
else
begin
select CONVERSION_RATE
into l_rate
from apps.MTL_UOM_CLASS_CONVERSIONS
where TO_UNIT_OF_MEASURE=p_source_uom
and FROM_UOM_CODE=l_item_base_code
and INVENTORY_ITEM_ID=p_item_id;
exception
when no_data_found then
l_rate:=-1;
end;
end if;
else
l_rate:=-1;
end if;
return l_rate;
end;
PROCEDURE get_po_line_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_line_num IN NUMBER DEFAULT NULL,
p_shipment_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
SELECT 0 header_interface_id, cpdv.vendor_id, cpdv.vendor_number,
cpdv.vendor, cpdv.po_header_id, 0 po_release_id,
cpdv.po_number, 0 release_num, cpdv.po_line_id,
cpdv.po_line_location_id, cpdv.item_id, cpdv.line_num,
cpdv.line_type_id, cpdv.line_type, cpdv.item_number,
cpdv.po_item_reversion, cpdv.item_rev, item_category_id,
cpdv.item_description, cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
GET_UOM_CONVERSION(cpdv.SHIP_TO_ORGANIZATION_ID,cpdv.ITEM_ID,cpdv.po_uom) conversion_rate,
cpdv.ship_to_quantity, cpdv.quantity_received,
cpdv.balance_qty, cpdv.need_by_date,
cpdv.ship_to_organization_id, cpdv.vendor_product_num,
'' subinventory, '' user_id, cpdv.shelf_life_days,
NVL (pas.attribute1, 0) min_shelf_life_rem_days,
cpdv.days_early_receipt_allowed,
cpdv.days_late_receipt_allowed, cpdv.qty_rcv_tolerance,
cpdv.shipment_num, cpdv.receiving_routing, cpdv.msl,
cpdv.temperature_ctl, cpdv.coc_required, cpdv.rohs,
cpdv.sample_size, cpdv.conversion_factor,
cpdv.receiving_routing_id, pas.attribute6 pull_method,
pas.attribute14 hub_type, ' ' succ_flag,'' return_message,cpdv.DESTINATION_SUBINVENTORY
FROM apps.cux_po_detail_view cpdv, apps.po_asl_suppliers_v pas
WHERE cpdv.item_id = pas.item_id(+)
AND cpdv.vendor_id = pas.vendor_id(+)
AND cpdv.inventory_organization_id = pas.owning_organization_id(+)
AND NVL (pas.disable_flag(+), 'N') = 'N'
AND NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND line_num = NVL (p_line_num, line_num)
AND shipment_num = NVL (p_shipment_num, shipment_num)
AND organization_code = NVL (p_org_code, organization_code)
GROUP BY cpdv.vendor_id,
vendor_number,
vendor,
po_header_id,
po_number,
po_line_id,
po_line_location_id,
cpdv.item_id,
line_num,
cpdv.line_type_id,
line_type,
item_number,
item_rev,
item_category_id,
item_description,
cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
ship_to_quantity,
quantity_received,
balance_qty,
need_by_date,
ship_to_organization_id,
vendor_product_num,
shelf_life_days,
NVL (pas.attribute1, 0),
days_early_receipt_allowed,
days_late_receipt_allowed,
qty_rcv_tolerance,
shipment_num,
po_item_reversion,
receiving_routing,
msl,
temperature_ctl,
coc_required,
rohs,
sample_size,
conversion_factor,
receiving_routing_id,
pas.attribute6,
pas.attribute14,cpdv.DESTINATION_SUBINVENTORY;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_osp_header_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
--po_header_id, revision_num, po_number,0 RELEASE_NUM,
SELECT 0 header_interface_id, currency_code, comments, bill_to,
ship_to, vendor_id, vendor_number, vendor, employee_number,
buyer, '' user_id, waybill_airbill_num, bill_of_lading,
organization_code, organization_name,
reference_invoice_number, ' ' succ_flag
FROM apps.cux_po_osp_detail_view
WHERE NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND organization_code = NVL (p_org_code, organization_code)
GROUP BY --po_header_id,
--po_number,
--revision_num,
currency_code,
comments,
bill_to,
ship_to,
vendor_id,
vendor_number,
vendor,
buyer,
employee_number,
waybill_airbill_num,
bill_of_lading,
organization_code,
organization_name,
reference_invoice_number;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_osp_line_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_line_num IN NUMBER DEFAULT NULL,
p_shipment_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
SELECT 0 header_interface_id, cpdv.vendor_id, cpdv.vendor_number,
cpdv.vendor, cpdv.po_header_id, 0 po_release_id,
cpdv.po_number, 0 release_num, cpdv.po_line_id,
cpdv.po_line_location_id, cpdv.item_id, cpdv.line_num,
cpdv.line_type_id, cpdv.line_type, cpdv.item_number,
cpdv.po_item_reversion, cpdv.item_rev, item_category_id,
cpdv.item_description, cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
GET_UOM_CONVERSION(cpdv.SHIP_TO_ORGANIZATION_ID,cpdv.ITEM_ID,cpdv.po_UOM) conversion_rate,
cpdv.ship_to_quantity, cpdv.quantity_received,
cpdv.balance_qty, cpdv.need_by_date,
cpdv.ship_to_organization_id, cpdv.vendor_product_num,
'' subinventory, '' user_id, cpdv.shelf_life_days,
cpdv.days_early_receipt_allowed,
cpdv.days_late_receipt_allowed, cpdv.qty_rcv_tolerance,
cpdv.shipment_num, cpdv.receiving_routing, cpdv.msl,
cpdv.temperature_ctl, cpdv.coc_required, cpdv.rohs,
cpdv.sample_size, cpdv.conversion_factor,
cpdv.receiving_routing_id,wip_entity_id, wip_entity_name,
wip_primary_item_id, wip_assembly, wip_description,WIP_REVERSION,
wip_start_quantity, wip_quantity_completed,
wip_quantity_remaining, wip_quantity_scrapped,
wip_status_type, ' ' succ_flag,'' return_message,'' rcv_message
FROM apps.cux_po_osp_detail_view cpdv
WHERE NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND line_num = NVL (p_line_num, line_num)
AND shipment_num = NVL (p_shipment_num, shipment_num)
AND organization_code = NVL (p_org_code, organization_code)
GROUP BY cpdv.vendor_id,
vendor_number,
vendor,
po_header_id,
po_number,
po_line_id,
po_line_location_id,
cpdv.item_id,
line_num,
cpdv.line_type_id,
line_type,
item_number,
item_rev,
item_category_id,
item_description,
cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
ship_to_quantity,
quantity_received,
balance_qty,
need_by_date,
ship_to_organization_id,
vendor_product_num,
shelf_life_days,
days_early_receipt_allowed,
days_late_receipt_allowed,
qty_rcv_tolerance,
shipment_num,
po_item_reversion,
receiving_routing,
msl,
temperature_ctl,
coc_required,
rohs,
sample_size,
conversion_factor,
receiving_routing_id,
wip_entity_id,
wip_entity_name,
wip_primary_item_id,
wip_assembly,
wip_description,
WIP_REVERSION,
wip_start_quantity,
wip_quantity_completed,
wip_quantity_remaining,
wip_quantity_scrapped,
wip_status_type;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_bpa_header_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_release_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
--po_header_id, po_number, revision_num, pbdv.RELEASE_NUM ,
SELECT 0 header_interface_id, currency_code, comments, bill_to,
ship_to, vendor_id, vendor_number, vendor, employee_number,
buyer, '' user_id, waybill_airbill_num, bill_of_lading,
organization_code, organization_name,
reference_invoice_number, ' ' succ_flag
FROM apps.cux_po_bpa_detail_view pbdv
WHERE NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND organization_code = NVL (p_org_code, organization_code)
AND release_num = NVL (p_release_num, release_num)
GROUP BY po_header_id,
po_number,
revision_num,
release_num,
currency_code,
comments,
bill_to,
ship_to,
vendor_id,
vendor_number,
vendor,
buyer,
employee_number,
waybill_airbill_num,
bill_of_lading,
organization_code,
organization_name,
reference_invoice_number;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_bpa_line_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_release_num IN NUMBER DEFAULT NULL,
p_line_num IN NUMBER DEFAULT NULL,
p_shipment_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
SELECT 0 header_interface_id, cpdv.vendor_id, cpdv.vendor_number,
cpdv.vendor, cpdv.po_header_id, cpdv.po_release_id,
cpdv.po_number, cpdv.release_num, cpdv.po_line_id,
cpdv.po_line_location_id, cpdv.item_id, cpdv.line_num,
cpdv.line_type_id, cpdv.line_type, cpdv.item_number,
cpdv.po_item_reversion, cpdv.item_rev, item_category_id,
cpdv.item_description, cpdv.po_uom, cpdv.base_uom,cpdv.base_uom_code,
GET_UOM_CONVERSION(cpdv.SHIP_TO_ORGANIZATION_ID,cpdv.ITEM_ID,cpdv.po_UOM) conversion_rate,
cpdv.ship_to_quantity, cpdv.quantity_received,
cpdv.balance_qty, cpdv.need_by_date,
cpdv.ship_to_organization_id, cpdv.vendor_product_num,
'' subinventory, '' user_id, cpdv.shelf_life_days,
NVL (pas.attribute1, 0) min_shelf_life_rem_days,
cpdv.days_early_receipt_allowed,
cpdv.days_late_receipt_allowed, cpdv.qty_rcv_tolerance,
cpdv.shipment_num, cpdv.receiving_routing, cpdv.msl,
cpdv.temperature_ctl, cpdv.coc_required, cpdv.rohs,
cpdv.sample_size, cpdv.conversion_factor,
cpdv.receiving_routing_id, pas.attribute6 pull_method,
pas.attribute14 hub_type, ' ' succ_flag,'' return_message,cpdv.DESTINATION_SUBINVENTORY
FROM apps.cux_po_bpa_detail_view cpdv,
apps.po_asl_suppliers_v pas
WHERE cpdv.item_id = pas.item_id(+)
AND cpdv.vendor_id = pas.vendor_id(+)
AND cpdv.inventory_organization_id = pas.owning_organization_id(+)
AND NVL (pas.disable_flag(+), 'N') = 'N'
AND NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND line_num = NVL (p_line_num, line_num)
AND shipment_num = NVL (p_shipment_num, shipment_num)
AND organization_code = NVL (p_org_code, organization_code)
AND cpdv.release_num = NVL (p_release_num, cpdv.release_num)
GROUP BY cpdv.vendor_id,
vendor_number,
vendor,
po_header_id,
cpdv.po_release_id,
po_number,
cpdv.release_num,
po_line_id,
po_line_location_id,
cpdv.item_id,
line_num,
cpdv.line_type_id,
line_type,
item_number,
item_rev,
item_category_id,
item_description,
cpdv.po_uom, cpdv.base_uom,cpdv.base_uom_code,
ship_to_quantity,
quantity_received,
balance_qty,
need_by_date,
ship_to_organization_id,
vendor_product_num,
shelf_life_days,
NVL (pas.attribute1, 0),
days_early_receipt_allowed,
days_late_receipt_allowed,
qty_rcv_tolerance,
shipment_num,
po_item_reversion,
receiving_routing,
msl,
temperature_ctl,
coc_required,
rohs,
sample_size,
conversion_factor,
receiving_routing_id,
pas.attribute6,
pas.attribute14,cpdv.DESTINATION_SUBINVENTORY;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_bpa_osp_header_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_release_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
--po_header_id, po_number, revision_num, pbdv.RELEASE_NUM ,
SELECT 0 header_interface_id, currency_code, comments, bill_to,
ship_to, vendor_id, vendor_number, vendor, employee_number,
buyer, '' user_id, waybill_airbill_num, bill_of_lading,
organization_code, organization_name,
reference_invoice_number, ' ' succ_flag
FROM apps.cux_po_bpa_osp_view pbdv
WHERE NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND organization_code = NVL (p_org_code, organization_code)
AND release_num = NVL (p_release_num, release_num)
GROUP BY po_header_id,
po_number,
revision_num,
release_num,
currency_code,
comments,
bill_to,
ship_to,
vendor_id,
vendor_number,
vendor,
buyer,
employee_number,
waybill_airbill_num,
bill_of_lading,
organization_code,
organization_name,
reference_invoice_number;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_po_bpa_osp_line_data (
o_po_cursor OUT cur_po,
p_po_no IN VARCHAR2 DEFAULT NULL,
p_release_num IN NUMBER DEFAULT NULL,
p_line_num IN NUMBER DEFAULT NULL,
p_shipment_num IN NUMBER DEFAULT NULL,
p_org_code IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
OPEN o_po_cursor FOR
SELECT 0 header_interface_id, cpdv.vendor_id, cpdv.vendor_number,
cpdv.vendor, cpdv.po_header_id, cpdv.po_release_id,
cpdv.po_number, cpdv.release_num, cpdv.po_line_id,
cpdv.po_line_location_id, cpdv.item_id, cpdv.line_num,
cpdv.line_type_id, cpdv.line_type, cpdv.item_number,
cpdv.po_item_reversion, cpdv.item_rev, item_category_id,
cpdv.item_description, cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
cpdv.ship_to_quantity, cpdv.quantity_received,
cpdv.balance_qty, cpdv.need_by_date,
cpdv.ship_to_organization_id, cpdv.vendor_product_num,
'' subinventory, '' user_id, cpdv.shelf_life_days,
NVL (pas.attribute1, 0) min_shelf_life_rem_days,
cpdv.days_early_receipt_allowed,
cpdv.days_late_receipt_allowed, cpdv.qty_rcv_tolerance,
cpdv.shipment_num, cpdv.receiving_routing, cpdv.msl,
cpdv.temperature_ctl, cpdv.coc_required, cpdv.rohs,
cpdv.sample_size, cpdv.conversion_factor,
cpdv.receiving_routing_id, pas.attribute6 pull_method,
pas.attribute14 hub_type, wip_entity_id,wip_entity_name,
wip_primary_item_id, wip_assembly, wip_description,
WIP_REVERSION,
wip_start_quantity, wip_quantity_completed,
wip_quantity_remaining, wip_quantity_scrapped,
wip_status_type,wip_COMPLETION_SUBINVENTORY,wip_COMPLETION_LOCATOR,
'' GR,'' rcv_message, ' ' succ_flag,'' return_message
FROM apps.cux_po_bpa_osp_view cpdv, apps.po_asl_suppliers_v pas
WHERE cpdv.item_id = pas.item_id(+)
AND cpdv.vendor_id = pas.vendor_id(+)
AND cpdv.inventory_organization_id = pas.owning_organization_id(+)
AND NVL (pas.disable_flag(+), 'N') = 'N'
AND NVL (closed_code, 'OPEN') = 'OPEN'
AND NVL (cancel_flag_location, 'N') = 'N'
AND NVL (closed_code_location, 'OPEN') NOT IN
('CLOSED', 'CLOSED FOR RECEIVING')
AND po_number = TO_CHAR (p_po_no)
AND line_num = NVL (p_line_num, line_num)
AND shipment_num = NVL (p_shipment_num, shipment_num)
AND organization_code = NVL (p_org_code, organization_code)
AND cpdv.release_num = NVL (p_release_num, cpdv.release_num)
GROUP BY cpdv.vendor_id,
vendor_number,
vendor,
po_header_id,
cpdv.po_release_id,
po_number,
cpdv.release_num,
po_line_id,
po_line_location_id,
cpdv.item_id,
line_num,
cpdv.line_type_id,
line_type,
item_number,
item_rev,
item_category_id,
item_description,
cpdv.po_uom,cpdv.BASE_UOM, cpdv.BASE_UOM_CODE,
ship_to_quantity,
quantity_received,
balance_qty,
need_by_date,
ship_to_organization_id,
vendor_product_num,
shelf_life_days,
NVL (pas.attribute1, 0),
days_early_receipt_allowed,
days_late_receipt_allowed,
qty_rcv_tolerance,
shipment_num,
po_item_reversion,
receiving_routing,
msl,
temperature_ctl,
coc_required,
rohs,
sample_size,
conversion_factor,
receiving_routing_id,
pas.attribute6,
pas.attribute14,
wip_entity_id,
wip_entity_name,
wip_primary_item_id,
wip_assembly,
wip_description,
WIP_REVERSION,
wip_start_quantity,
wip_quantity_completed,
wip_quantity_remaining,
wip_quantity_scrapped,
wip_status_type,
wip_COMPLETION_SUBINVENTORY,
wip_COMPLETION_LOCATOR;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_rcv_receiving_data (
o_rcv_cursor OUT cur_po,
p_rcv_no IN NUMBER DEFAULT NULL,
p_org_id IN NUMBER DEFAULT NULL
)
IS
BEGIN
OPEN o_rcv_cursor FOR
SELECT erv.*
FROM apps.cux_inv_rcv_view erv
WHERE erv.transaction_type = 'RECEIVE'
AND receipt_num = NVL (p_rcv_no, receipt_num)
-- and nvl(p_rcv_no_t,RECEIPT_NUM)
AND organization_id = NVL (p_org_id, organization_id)
--and vendor_id =nvl(p_vendor_id,vendor_id)
--and po_number between nvl(p_po_number_f,po_number) and nvl(p_po_number_t,po_number)
;
EXCEPTION
WHEN OTHERS
THEN
NULL;
--p_errorcode:= SQLCODE;
END;
PROCEDURE get_rcv_inspection_data (
o_rcv_cursor OUT cur_po,
p_rcv_no_f IN NUMBER DEFAULT NULL,
p_rcv_no_t IN NUMBER DEFAULT NULL,
p_org_id IN NUMBER DEFAULT NULL,
p_vendor_id IN NUMBER DEFAULT NULL,
p_po_number_f IN NUMBER DEFAULT NULL,
p_po_number_t IN NUMBER DEFAULT NULL
)
IS
BEGIN
OPEN o_rcv_cursor FOR
SELECT erv.*
FROM apps.cux_inv_rcv_view erv
WHERE erv.transaction_type = 'ACCEPT'
AND receipt_num BETWEEN NVL (p_rcv_no_f, receipt_num)
AND NVL (p_rcv_no_t, receipt_num)
AND organization_id = NVL (p_org_id, organization_id)
AND vendor_id = NVL (p_vendor_id, vendor_id)
AND po_number BETWEEN NVL (p_po_number_f, po_number)
AND NVL (p_po_number_t, po_number);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/11536986/viewspace-620205/ ，如需转载，请注明出处，否则将追究法律责任。
