PO Receipt WEB ADI Interface: This is a interface, in Oracle EBS using web ADI, push raw data in custom staging table and then custom program does all validation and submits Receiving Transaction Process.
CREATE OR REPLACE package APPS.XXDS_PO_RCV_INT0401_PKG
is
G_GROUP_ID NUMBER;
procedure XXDS_LOAD_RCV_DATA(
p_po_number VARCHAR2,
p_po_line_no NUMBER,
p_release_no NUMBER,
p_Batch_Mode VARCHAR2,
p_Batch_Source VARCHAR2,
p_receipt_source_code VARCHAR2,
p_transaction_type VARCHAR2,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_shipment_num VARCHAR2,
p_receipt_num VARCHAR2,
p_vendor_name VARCHAR2,
p_vendor_site_code VARCHAR2,
p_expected_receipt_date DATE,
p_currency_code VARCHAR2,
p_h_attribute_category VARCHAR2,
p_h_attribute1 VARCHAR2,
p_h_transaction_date DATE,
p_h_operating_unit VARCHAR2,
p_transaction_date DATE,
p_quantity NUMBER,
p_unit_of_measure VARCHAR2,
p_item_revision VARCHAR2,
p_uom_code VARCHAR2,
p_subinventory VARCHAR2,
p_comments VARCHAR2,
p_lot_number VARCHAR2,
p_lot_expiration_date DATE,
p_fm_serial_number VARCHAR2,
p_to_serial_number VARCHAR2
);
——
procedure XXDS_RCV_INTERFACE_INSERT(error_buff varchar2, retcode number);
END XXDS_PO_RCV_INT0401_PKG;
/
CREATE OR REPLACE package body APPS.XXDS_PO_RCV_INT0401_PKG
is
procedure XXDS_LOAD_RCV_DATA(
p_po_number VARCHAR2,
p_po_line_no NUMBER,
p_release_no NUMBER,
p_Batch_Mode VARCHAR2,
p_Batch_Source VARCHAR2,
p_receipt_source_code VARCHAR2,
p_transaction_type VARCHAR2,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_shipment_num VARCHAR2,
p_receipt_num VARCHAR2,
p_vendor_name VARCHAR2,
p_vendor_site_code VARCHAR2,
p_expected_receipt_date DATE,
p_currency_code VARCHAR2,
p_h_attribute_category VARCHAR2,
p_h_attribute1 VARCHAR2,
p_h_transaction_date DATE,
p_h_operating_unit VARCHAR2,
p_transaction_date DATE,
p_quantity NUMBER,
p_unit_of_measure VARCHAR2,
p_item_revision VARCHAR2,
p_uom_code VARCHAR2,
p_subinventory VARCHAR2,
p_comments VARCHAR2,
p_lot_number VARCHAR2,
p_lot_expiration_date DATE,
p_fm_serial_number VARCHAR2,
p_to_serial_number VARCHAR2
)
is
begin
—
insert into PO.XXDS_PO_RECEIPT_IFACE_INT0401(po_number,
po_line_no,
release_no,
Batch_Mode,
Batch_Source,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
shipment_num,
receipt_num,
vendor_name,
vendor_site_code,
expected_receipt_date,
currency_code,
h_attribute_category,
h_attribute1,
h_transaction_date,
h_operating_unit,
transaction_date,
quantity,
unit_of_measure,
item_revision,
uom_code,
subinventory,
comments,
lot_number,
lot_expiration_date,
fm_serial_number,
to_serial_number,
PROCESSING_STATUS_CODE
)
values(p_po_number,
p_po_line_no,
p_release_no,
p_Batch_Mode,
p_Batch_Source,
p_receipt_source_code,
p_transaction_type,
p_last_update_date,
p_last_updated_by,
p_last_update_login,
p_creation_date,
p_created_by,
p_shipment_num,
p_receipt_num,
p_vendor_name,
p_vendor_site_code,
p_expected_receipt_date,
p_currency_code,
p_h_attribute_category,
p_h_attribute1,
p_h_transaction_date,
p_h_operating_unit,
p_transaction_date,
p_quantity,
p_unit_of_measure,
p_item_revision,
p_uom_code,
p_subinventory,
p_comments,
p_lot_number,
p_lot_expiration_date,
p_fm_serial_number,
p_to_serial_number,
‘NEW’);
commit;
end XXDS_LOAD_RCV_DATA;
—
procedure XXDS_RCV_INTERFACE_INSERT(error_buff varchar2, retcode number)
is
—-
cursor cur_rcv_header_rec(p_group_id number)
is
SELECT po_number, receipt_num, expected_receipt_date, transaction_type, h_operating_unit, batch_mode
FROM PO.XXDS_PO_RECEIPT_IFACE_INT0401
where processing_status_code = ‘In Progress’
and group_id = p_group_id
group by po_number, receipt_num, expected_receipt_date, transaction_type, h_operating_unit, batch_mode ;
—-
cursor cur_rcv_trans_rec(x_po_number varchar2, x_receipt_num varchar2, p_group_id number)
is
SELECT po_line_no, quantity, unit_of_measure, currency_code, subinventory, lot_number, fm_serial_number, to_serial_number, release_no, group_id
FROM PO.XXDS_PO_RECEIPT_IFACE_INT0401
where processing_status_code = ‘In Progress’
and group_id = p_group_id
and po_number = x_po_number
and receipt_num = x_receipt_num
group by quantity, unit_of_measure, currency_code, subinventory, lot_number, fm_serial_number, to_serial_number, po_line_no, release_no, group_id;
—
ln_org_id number;
ln_vendor_id number;
ln_vendor_site_id number;
ln_po_header_id number;
ln_po_line_id number;
ln_line_location_id number;
ln_po_distribution_id number;
ln_po_release_id number;
ln_ship_to_organization_id number;
ln_item_id number;
LN_REQUEST_ID number;
ln_serial_num_control_code number;
ln_lot_control_code number;
ln_location_control_code number;
ln_serial_trx_id number;
lv_subinventory po_distributions_all.destination_subinventory%type;
lv_organization_code rcv_transactions_interface.subinventory%type;
LV_ORG_NAME varchar2(250);
V_REQUEST_STATUS BOOLEAN;
V_PHASE VARCHAR2(2000);
V_WAIT_STATUS VARCHAR2(2000);
V_DEV_PHASE VARCHAR2(2000);
V_DEV_STATUS VARCHAR2(2000);
V_MESSAGE1 VARCHAR2(2000);
lv_iface_error_message po_interface_errors.error_message%TYPE;
lv_trx_status_code rcv_transactions_interface.transaction_status_code%TYPE;
lv_iface_status_code rcv_transactions_interface.processing_status_code%TYPE;
lv_ou_name varchar2(250):=NULL;
lv_subinventory_code varchar2(250):=NULL;
—
begin
IF g_group_id is null then
g_group_id := rcv_interface_groups_s.nextval;
end if;
fnd_file.put_line(fnd_file.log, G_GROUP_ID);
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set PROCESSING_STATUS_CODE = ‘In Progress’, group_id = g_group_id
where nvl(PROCESSING_STATUS_CODE, ‘NEW’) = ‘NEW’;
commit;
for h in cur_rcv_header_rec(g_group_id) loop
lv_ou_name:=NULL;
if upper(h.batch_mode) = ‘Y’ then
begin
select attribute1
into lv_ou_name
from FND_LOOKUP_VALUES_VL
where 1 = 1
and lookup_type = ‘XXDS_PO_RECEIPT_0401_LOOKKP’
and enabled_flag = ‘Y’
and nvl(end_date_active, sysdate + 1) > sysdate
and upper(meaning) = upper( h.h_operating_unit)
and tag=’OU’;
exception when others then
lv_ou_name:=NULL;
end;
end if;
begin
select pha.po_header_id, pha.org_id, vendor_id, vendor_site_id, hou.name
into ln_po_header_id, ln_org_id, ln_vendor_id, ln_vendor_site_id, lv_org_name
from po_headers_all pha, hr_operating_units hou
where segment1 = h.po_number
and upper(hou.name) = upper(nvl(lv_ou_name, h.h_operating_unit))
and hou.organization_id = pha.org_id;
exception when others then
NULL;
end;
INSERT INTO rcv_headers_interface
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
vendor_id,
vendor_site_id,
expected_receipt_date,
validation_flag)
VALUES
(rcv_headers_interface_s.NEXTVAL,
g_group_id,
‘PENDING’,
‘VENDOR’,
‘NEW’,
SYSDATE,
fnd_global.user_id,
ln_vendor_id,
ln_vendor_site_id,
SYSDATE,
‘Y’
);
for t in cur_rcv_trans_rec(h.po_number, h.receipt_num, g_group_id) loop
—
begin
select pha.po_header_id, pla.po_line_id, plla.line_location_id, pda.po_distribution_id, plla.po_release_id,
plla.ship_to_organization_id, pla.item_id, pda.destination_subinventory
into ln_po_header_id, ln_po_line_id, ln_line_location_id, ln_po_distribution_id, ln_po_release_id,
ln_ship_to_organization_id, ln_item_id, lv_subinventory
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
po_releases_all pra
where pha.po_header_id = ln_po_header_id
and pha.po_header_id = pla.po_header_id
and pla.po_line_id = plla.po_line_id
and plla.line_location_id = pda.line_location_id
and plla.po_release_id = pra.po_release_id(+)
and nvl(pra.release_num, -1) = nvl(t.release_no, -1)
and pla.line_num = t.po_line_no;
exception when others then
dbms_output.put_line(‘ln_po_header_id ‘||ln_po_header_id);
dbms_output.put_line(‘t.release_num ‘||nvl(t.release_no, -1));
dbms_output.put_line(‘t.line_number ‘||t.po_line_no);
end;
if upper(h.batch_mode) = ‘Y’ then
begin
select attribute1
into lv_subinventory_code
from FND_LOOKUP_VALUES_VL
where 1 = 1
and lookup_type = ‘XXDS_PO_RECEIPT_0401_LOOKKP’
and enabled_flag = ‘Y’
and nvl(end_date_active, sysdate + 1) > sysdate
and meaning = ‘Store’
and tag=’SUB_INV’;
exception when others then
BEGIN
SELECT subinventory_code
INTO lv_subinventory_code
FROM mtl_item_sub_defaults misd
WHERE misd.inventory_item_id = ln_item_id
AND misd.organization_id = ln_ship_to_organization_id
AND misd.default_type = 2;
EXCEPTION WHEN OTHERS THEN
lv_subinventory_code := NULL;
END;
end;
end if;
begin
select organization_code
into lv_organization_code
from org_organization_definitions
where organization_id = ln_ship_to_organization_id;
exception when others then
lv_organization_code := NULL;
end;
—–
INSERT INTO rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
subinventory)
VALUES
(rcv_transactions_interface_s.NEXTVAL,
g_group_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
‘RECEIVE’,
SYSDATE,
‘PENDING’,
‘BATCH’,
‘PENDING’,
ln_po_header_id,
ln_po_line_id,
ln_item_id,
t.quantity,
t.unit_of_measure,
ln_line_location_id,
‘DELIVER’,
‘VENDOR’,
lv_organization_code,
‘PO’,
rcv_headers_interface_s.CURRVAL,
‘Y’,
nvl(lv_subinventory, t.subinventory)
);
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_transaction_id = rcv_transactions_interface_s.currval
where group_id = g_group_id
and po_number = h.po_number
and po_line_no = t.po_line_no
and interface_transaction_id is null;
begin
SELECT msib.serial_number_control_code,
msib.lot_control_code,
msib.location_control_code
INTO ln_serial_num_control_code,
ln_lot_control_code,
ln_location_control_code
FROM po_lines_all pla,
mtl_parameters mp,
mtl_system_items_b msib
WHERE 1 = 1
AND msib.inventory_item_id = pla.item_id
AND msib.organization_id = mp.organization_id
AND PLA.ITEM_ID = ln_item_id
and pla.po_header_id = ln_po_header_id
AND mp.organization_id = ln_ship_to_organization_id;
exception when others then
ln_serial_num_control_code := NULL;
ln_lot_control_code := NULL;
ln_location_control_code := NULL;
end;
IF ln_serial_num_control_code IN (2, 5) THEN
—
BEGIN
—
ln_serial_trx_id := mtl_material_transactions_s.NEXTVAL;
FND_FILE.PUT_LINE(FND_FILE.LOG,’S No. ln_serial_num_control_code : ‘ ||ln_serial_num_control_code );
FND_FILE.PUT_LINE(FND_FILE.LOG,’S No. ln_serial_trx_id : ‘ ||ln_serial_trx_id );
FND_FILE.PUT_LINE(FND_FILE.LOG,’S No. product_transaction_id : ‘ || rcv_transactions_interface_s.CURRVAL);
—
INSERT INTO mtl_serial_numbers_interface
(transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
fm_serial_number,
to_serial_number,
product_code,
product_transaction_id)
VALUES
(ln_serial_trx_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
t.fm_serial_number,
t.to_serial_number,
‘RCV’,
rcv_transactions_interface_s.CURRVAL);
—
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END IF;
—-
IF ln_lot_control_code = 2 THEN
—
BEGIN
—
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
lot_number,
transaction_quantity,
primary_quantity,
serial_transaction_temp_id,
product_code,
product_transaction_id)
VALUES
(mtl_material_transactions_s.NEXTVAL,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
t.lot_number,
t.quantity,
t.quantity,
ln_serial_trx_id,
‘RCV’,
rcv_transactions_interface_s.CURRVAL);
FND_FILE.PUT_LINE(FND_FILE.LOG,’LOT IFACE product_transaction_id : ‘ ||mtl_material_transactions_s.CURRVAL );
FND_FILE.PUT_LINE(FND_FILE.LOG,’LOT IFACE ln_serial_trx_id : ‘ ||ln_serial_trx_id );
FND_FILE.PUT_LINE(FND_FILE.LOG,’LOT IFACE product_transaction_id : ‘ ||rcv_transactions_interface_s.CURRVAL);
—
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
end loop;
end loop;
commit;
LOOP
LN_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => ‘PO’
,PROGRAM => ‘RVCTP’
,DESCRIPTION => ‘Receiving Transaction Processor’
,START_TIME => NULL
,SUB_REQUEST => FALSE
,ARGUMENT1 => ‘BATCH’ –Transaction Processing Mode
,ARGUMENT2 => G_GROUP_ID –Transaction group id
,ARGUMENT3 => LV_ORG_NAME –Operating Unit Name
);
commit;
—
IF LN_REQUEST_ID = 0 THEN
fnd_file.put_line(fnd_file.LOG,’Request Set could not submitted’);
ELSE
—
V_REQUEST_STATUS := FND_CONCURRENT.WAIT_FOR_REQUEST(REQUEST_ID => LN_REQUEST_ID
,INTERVAL => 5
,MAX_WAIT => 0
,PHASE => V_PHASE
,STATUS => V_WAIT_STATUS
,DEV_PHASE => V_DEV_PHASE
,DEV_STATUS => V_DEV_STATUS
,MESSAGE => V_MESSAGE1);
—
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Phase =>’ || V_PHASE);
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Status =>’ || V_WAIT_STATUS);
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Dev_phase =>’ || V_DEV_PHASE);
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Dev_status =>’ || V_DEV_STATUS);
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Message =>’ || V_MESSAGE1);
END IF;
EXIT
WHEN UPPER (V_PHASE) = ‘COMPLETED’ OR UPPER (V_WAIT_STATUS) IN (‘CANCELLED’, ‘ERROR’, ‘TERMINATED’);
END LOOP;
—
FOR u IN (SELECT *
FROM PO.XXDS_PO_RECEIPT_IFACE_INT0401
WHERE group_id = g_group_id) LOOP
—
BEGIN
—
SELECT processing_status_code, pie.error_message, transaction_status_code
INTO lv_iface_status_code, lv_iface_error_message, lv_trx_status_code
FROM rcv_transactions_interface rti, po_interface_errors pie
WHERE rti.group_id = g_group_id
AND rti.interface_transaction_id = u.interface_transaction_id
AND rti.group_id = pie.batch_id(+)
AND rti.interface_transaction_id = pie.interface_line_id(+);
—
IF lv_iface_status_code = ‘ERROR’ OR lv_trx_status_code = ‘ERROR’
THEN
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_status_message = ‘Transaction Error: ‘ || lv_iface_error_message
where group_id = g_group_id
and interface_transaction_id = u.interface_transaction_id;
ELSE
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_status_message =’Transaction Completed Successfuly: PO Number ‘|| u.po_number || ‘ PO Line No ‘|| u.po_line_No
where group_id = g_group_id
and interface_transaction_id = u.interface_transaction_id;
END IF;
EXCEPTION
WHEN no_data_found THEN
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_status_message =’Transaction Completed Successfuly: PO Number ‘|| u.po_number || ‘ PO Line No ‘|| u.po_line_No
where group_id = g_group_id
and interface_transaction_id = u.interface_transaction_id;
WHEN too_many_rows THEN
–Single transaction can have multiple errors
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_status_message = ‘Transaction Error: Single transaction may have multiple errors.’
where group_id = g_group_id
and interface_transaction_id = u.interface_transaction_id;
WHEN OTHERS THEN
update PO.XXDS_PO_RECEIPT_IFACE_INT0401
set interface_status_message = ‘Transaction Error: Unknown Error. ‘
where group_id = g_group_id
and interface_transaction_id = u.interface_transaction_id;
END;
END LOOP;
commit;
end XXDS_RCV_INTERFACE_INSERT;
—
END XXDS_PO_RCV_INT0401_PKG;
/
