PO RECEIPT OPEN INTERFACE SCRIPT
/* Formatted on 2011/07/21 16:17 (Formatter Plus v4.8.8) */
DECLARE
l_reqid NUMBER;
l_formatconversion DATE;
l_validate_flag VARCHAR2 (1000);
l_validate_msg VARCHAR2 (1000);
l_actionvalidate_msg VARCHAR2 (1000);
l_actionvalidate_flag VARCHAR2 (1000);
d_actionvalidate_msg VARCHAR2 (1000);
d_actionvalidate_flag VARCHAR2 (1000);
d_actionvalidate_value DATE;
l_sqlerrm VARCHAR2 (2000);
lv_rowid VARCHAR2 (1000);
l_condt_flag VARCHAR2 (10);
l_interfaceflag VARCHAR2 (10);
l_h_iface_header_id NUMBER;
l_h_group_id NUMBER;
l_h_validation_flag VARCHAR2 (2000);
l_h_last_update_date DATE;
l_h_creation_date DATE;
l_h_created_by NUMBER;
l_h_last_updated_by NUMBER;
l_h_processing_status VARCHAR2 (2000);
l_h_transaction_type VARCHAR2 (2000);
l_h_vendor_name VARCHAR2 (2000);
l_h_auto_trx_code VARCHAR2 (2000);
l_h_receipt_source VARCHAR2 (2000);
l_h_ship_to_organization VARCHAR2 (2000);
l_h_expected_receipt_date DATE;
l_h_reference VARCHAR2 (2000);
l_h_waybill_airbill VARCHAR2 (2000);
l_h_bill_of_lading VARCHAR2 (2000);
l_l_iface_header_id NUMBER;
l_l_group_id NUMBER;
l_l_iface_transaction_id NUMBER;
l_l_last_updated_by NUMBER;
l_l_created_by NUMBER;
l_l_creation_date DATE;
l_l_last_update_date DATE;
l_l_po_line_id NUMBER;
l_l_validation_flag VARCHAR2 (2000);
l_l_shipment_hdr_id NUMBER;
l_l_location VARCHAR2 (2000);
l_l_processing_status VARCHAR2 (2000);
l_l_transaction_status VARCHAR2 (2000);
l_l_processing_mode VARCHAR2 (2000);
l_l_line_location VARCHAR2 (2000);
l_l_auto_trx_code VARCHAR2 (2000);
l_l_source_document VARCHAR2 (2000);
l_l_transaction_type VARCHAR2 (2000);
l_l_transaction_date DATE;
l_l_quantity NUMBER;
l_l_uom VARCHAR2 (2000);
l_l_item_num VARCHAR2 (2000);
l_l_to_organization VARCHAR2 (2000);
l_l_item_description VARCHAR2 (2000);
l_l_shipment_num VARCHAR2 (2000);
l_l_ship_to_location VARCHAR2 (2000);
l_l_po_number VARCHAR2 (2000);
l_l_destination_type VARCHAR2 (2000);
l_l_deliver_to_location VARCHAR2 (2000);
l_l_subinventory VARCHAR2 (2000);
l_l_reference VARCHAR2 (2000);
l_l_po_line_num NUMBER;
l_l_category VARCHAR2 (2000);
l_l_project VARCHAR2 (2000);
CURSOR rcv_hdr
IS
SELECT DISTINCT rcv_hdr_po_receipt5.ROWID,
rcv_hdr_po_receipt5.oal_exec_id,
rcv_hdr_po_receipt5.oal_loader_id,
rcv_hdr_po_receipt5.oal_line_no,
rcv_hdr_po_receipt5.h_iface_header_id,
rcv_hdr_po_receipt5.h_group_id,
rcv_hdr_po_receipt5.h_validation_flag,
rcv_hdr_po_receipt5.h_last_update_date,
rcv_hdr_po_receipt5.h_creation_date,
rcv_hdr_po_receipt5.h_created_by,
rcv_hdr_po_receipt5.h_last_updated_by,
rcv_hdr_po_receipt5.h_processing_status,
rcv_hdr_po_receipt5.h_transaction_type,
rcv_hdr_po_receipt5.h_vendor_name,
rcv_hdr_po_receipt5.h_auto_trx_code,
rcv_hdr_po_receipt5.h_receipt_source,
rcv_hdr_po_receipt5.h_ship_to_organization,
rcv_hdr_po_receipt5.h_expected_receipt_date,
rcv_hdr_po_receipt5.h_reference,
rcv_hdr_po_receipt5.h_waybill_airbill,
rcv_hdr_po_receipt5.h_bill_of_lading
FROM rcv_hdr_po_receipt5@sunr12toapp_222_qa
WHERE NVL (rcv_hdr_po_receipt5.status_code, 'NULL') LIKE
NVL (p_status_code, 'NULL');
CURSOR rcv_trx (p_h_reference VARCHAR2)
IS
SELECT DISTINCT rcv_trx_po_receipt5.ROWID,
rcv_trx_po_receipt5.oal_exec_id,
rcv_trx_po_receipt5.oal_loader_id,
rcv_trx_po_receipt5.oal_line_no,
rcv_trx_po_receipt5.l_deliver_to_location,
rcv_trx_po_receipt5.l_iface_header_id,
rcv_trx_po_receipt5.l_group_id,
rcv_trx_po_receipt5.l_iface_transaction_id,
rcv_trx_po_receipt5.l_last_updated_by,
rcv_trx_po_receipt5.l_created_by,
rcv_trx_po_receipt5.l_creation_date,
rcv_trx_po_receipt5.l_last_update_date,
rcv_trx_po_receipt5.l_po_line_id,
rcv_trx_po_receipt5.l_validation_flag,
rcv_trx_po_receipt5.l_shipment_hdr_id,
rcv_trx_po_receipt5.l_location,
rcv_trx_po_receipt5.l_processing_status,
rcv_trx_po_receipt5.l_transaction_status,
rcv_trx_po_receipt5.l_processing_mode,
rcv_trx_po_receipt5.l_line_location,
rcv_trx_po_receipt5.l_auto_trx_code,
rcv_trx_po_receipt5.l_source_document,
rcv_trx_po_receipt5.l_transaction_type,
rcv_trx_po_receipt5.l_transaction_date,
rcv_trx_po_receipt5.l_quantity,
rcv_trx_po_receipt5.l_uom,
rcv_trx_po_receipt5.l_item_num,
rcv_trx_po_receipt5.l_to_organization,
rcv_trx_po_receipt5.l_item_description,
rcv_trx_po_receipt5.l_shipment_num,
rcv_trx_po_receipt5.l_ship_to_location,
rcv_trx_po_receipt5.l_po_number,
rcv_trx_po_receipt5.l_destination_type,
rcv_trx_po_receipt5.l_subinventory,
rcv_trx_po_receipt5.l_reference,
rcv_trx_po_receipt5.l_po_line_num,
rcv_trx_po_receipt5.l_category,
rcv_trx_po_receipt5.l_project
FROM rcv_trx_po_receipt5@sunr12toapp_222_qa
WHERE NVL (rcv_trx_po_receipt5.status_code, 'NULL') LIKE
NVL (p_status_code, 'NULL')
AND NVL (rcv_trx_po_receipt5.l_reference, '-123') =
NVL (p_h_reference, '-123');
PROCEDURE validate_query_new (
qry IN VARCHAR2,
validate_flag OUT VARCHAR2,
validate_msg OUT VARCHAR2
)
IS
l_qry VARCHAR2 (5000);
column_names VARCHAR2 (1000);
multi_column_flag NUMBER (10) := 0;
l_tname VARCHAR2 (1000);
l_datatype VARCHAR2 (1000);
l_start NUMBER;
l_end NUMBER;
BEGIN
l_qry := qry;
validate_flag := 'S';
validate_msg := NULL;
column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);
IF multi_column_flag <> 0
THEN
validate_flag := 'F';
validate_msg := 'Multi Column';
ELSE
l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
l_end := INSTR (UPPER (qry), 'WHERE', 1);
IF (l_end > 0)
THEN
l_tname := SUBSTR (qry, l_start, (l_end - l_start));
ELSE
l_tname := SUBSTR (qry, l_start);
END IF;
IF NVL (INSTR (l_tname, ' '), 0) = 0
THEN
BEGIN
SELECT data_type
INTO l_datatype
FROM all_tab_columns
WHERE table_name = UPPER (TRIM (l_tname))
AND column_name = UPPER (TRIM (column_names));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END;
END IF;
IF l_datatype LIKE 'DATE'
THEN
l_qry :=
'SELECT TO_CHAR('
|| column_names
|| ',''DD-MON-RRRR HH24:MI:SS'') '
|| SUBSTR (qry, INSTR (UPPER (qry), 'FROM'));
END IF;
BEGIN
EXECUTE IMMEDIATE l_qry
INTO validate_msg;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
validate_flag := 'S';
validate_msg := NULL;
WHEN TOO_MANY_ROWS
THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM ('
|| l_qry
|| ') WHERE ROWNUM = 1'
INTO validate_msg;
EXCEPTION
WHEN OTHERS
THEN
validate_flag := 'F';
validate_msg := 'Err in Too Many Rows...' || SQLERRM;
END;
WHEN OTHERS
THEN
validate_flag := 'F';
validate_msg := 'Err in Qry : ' || SQLERRM;
END;
END IF;
END validate_query_new;
PROCEDURE validate_query_date (
qry IN VARCHAR2,
date_validate_flag OUT VARCHAR2,
date_validate_msg OUT VARCHAR2,
date_validate_msg1 OUT DATE
)
IS
l_qry VARCHAR2 (5000);
column_names VARCHAR2 (1000);
multi_column_flag NUMBER (10) := 0;
l_tname VARCHAR2 (1000);
l_datatype VARCHAR2 (1000);
l_start NUMBER;
l_end NUMBER;
BEGIN
l_qry := qry;
date_validate_flag := 'S';
date_validate_msg := NULL;
date_validate_msg1 := NULL;
column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);
IF multi_column_flag <> 0
THEN
date_validate_flag := 'F';
date_validate_msg := 'Multi Column';
ELSE
l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
l_end := INSTR (UPPER (qry), 'WHERE', 1);
IF (l_end > 0)
THEN
l_tname := SUBSTR (qry, l_start, (l_end - l_start));
ELSE
l_tname := SUBSTR (qry, l_start);
END IF;
IF NVL (INSTR (l_tname, ' '), 0) = 0
THEN
BEGIN
SELECT data_type
INTO l_datatype
FROM all_tab_columns
WHERE table_name = UPPER (TRIM (l_tname))
AND column_name = UPPER (TRIM (column_names));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END;
END IF;
BEGIN
EXECUTE IMMEDIATE l_qry
INTO date_validate_msg1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
date_validate_flag := 'S';
date_validate_msg1 := NULL;
WHEN TOO_MANY_ROWS
THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM ('
|| l_qry
|| ') WHERE ROWNUM = 1'
INTO date_validate_msg1;
EXCEPTION
WHEN OTHERS
THEN
date_validate_flag := 'F';
date_validate_msg :=
'Err in Too Many Rows...' || SQLERRM;
END;
WHEN OTHERS
THEN
date_validate_flag := 'F';
date_validate_msg := 'Err in Qry : ' || SQLERRM;
END;
END IF;
END validate_query_date;
BEGIN
apps.fnd_global.apps_initialize (##ebs_user_id##,
##ebs_resp_id##,
##ebs_appl_id##,
NULL,
NULL
);
FOR cur_rcv_hdr IN rcv_hdr
LOOP
l_h_iface_header_id := cur_rcv_hdr.h_iface_header_id;
IF (l_h_iface_header_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_iface_header_id := l_actionvalidate_msg;
cur_rcv_hdr.h_iface_header_id := l_actionvalidate_msg;
END IF;
l_h_group_id := cur_rcv_hdr.h_group_id;
IF (l_h_group_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_group_id := l_actionvalidate_msg;
cur_rcv_hdr.h_group_id := l_actionvalidate_msg;
END IF;
l_h_validation_flag := cur_rcv_hdr.h_validation_flag;
IF (l_h_validation_flag IS NULL)
THEN
l_h_validation_flag := 'Y';
END IF;
l_h_last_update_date := cur_rcv_hdr.h_last_update_date;
IF (l_h_last_update_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_h_last_update_date := d_actionvalidate_value;
cur_rcv_hdr.h_last_update_date := d_actionvalidate_value;
END IF;
l_h_creation_date := cur_rcv_hdr.h_creation_date;
IF (l_h_creation_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_h_creation_date := d_actionvalidate_value;
cur_rcv_hdr.h_creation_date := d_actionvalidate_value;
END IF;
l_h_created_by := cur_rcv_hdr.h_created_by;
IF (l_h_created_by IS NULL)
THEN
l_h_created_by := fnd_global.user_id;
END IF;
l_h_last_updated_by := cur_rcv_hdr.h_last_updated_by;
IF (l_h_last_updated_by IS NULL)
THEN
l_h_last_updated_by := fnd_global.user_id;
END IF;
l_h_processing_status := cur_rcv_hdr.h_processing_status;
IF (l_h_processing_status IS NULL)
THEN
l_h_processing_status := 'PENDING';
END IF;
l_h_transaction_type := cur_rcv_hdr.h_transaction_type;
IF (l_h_transaction_type IS NULL)
THEN
l_h_transaction_type := 'NEW';
END IF;
l_h_vendor_name := cur_rcv_hdr.h_vendor_name;
IF (l_h_vendor_name IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT VENDOR_ID FROM PO_VENDORS WHERE ENABLED_FLAG = ''Y'' AND ((END_DATE_ACTIVE IS NULL)
OR (END_DATE_ACTIVE > SYSDATE)) AND VENDOR_NAME = '''
|| cur_rcv_hdr.h_vendor_name
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_vendor_name := l_actionvalidate_msg;
cur_rcv_hdr.h_vendor_name := l_actionvalidate_msg;
END IF;
l_h_auto_trx_code := cur_rcv_hdr.h_auto_trx_code;
IF (l_h_auto_trx_code IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''AUTO TRANSACT CODE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_hdr.h_auto_trx_code
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_auto_trx_code := l_actionvalidate_msg;
cur_rcv_hdr.h_auto_trx_code := l_actionvalidate_msg;
END IF;
l_h_receipt_source := cur_rcv_hdr.h_receipt_source;
IF (l_h_receipt_source IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''SHIPMENT SOURCE TYPE''
AND ENABLED_FLAG = ''Y'' AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE))
AND DISPLAYED_FIELD = '''
|| cur_rcv_hdr.h_receipt_source
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_receipt_source := l_actionvalidate_msg;
cur_rcv_hdr.h_receipt_source := l_actionvalidate_msg;
END IF;
l_h_ship_to_organization := cur_rcv_hdr.h_ship_to_organization;
IF (l_h_ship_to_organization IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
|| cur_rcv_hdr.h_ship_to_organization
|| ''' AND ((DISABLE_DATE IS NULL)
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_ship_to_organization := l_actionvalidate_msg;
cur_rcv_hdr.h_ship_to_organization := l_actionvalidate_msg;
END IF;
l_h_expected_receipt_date := cur_rcv_hdr.h_expected_receipt_date;
l_h_reference := cur_rcv_hdr.h_reference;
l_h_waybill_airbill := cur_rcv_hdr.h_waybill_airbill;
l_h_bill_of_lading := cur_rcv_hdr.h_bill_of_lading;
BEGIN
INSERT INTO rcv_headers_interface
(header_interface_id, GROUP_ID,
validation_flag, last_update_date,
creation_date, created_by,
last_updated_by, processing_status_code,
transaction_type, vendor_id,
auto_transact_code, receipt_source_code,
ship_to_organization_id, expected_receipt_date,
attribute15, waybill_airbill_num, bill_of_lading
)
VALUES (l_h_iface_header_id, l_h_group_id,
l_h_validation_flag, l_h_last_update_date,
l_h_creation_date, l_h_created_by,
l_h_last_updated_by, l_h_processing_status,
l_h_transaction_type, l_h_vendor_name,
l_h_auto_trx_code, l_h_receipt_source,
l_h_ship_to_organization, l_h_expected_receipt_date,
l_h_reference, l_h_waybill_airbill, l_h_bill_of_lading
);
BEGIN
SELECT DISTINCT ROWID
INTO lv_rowid
FROM rcv_headers_interface
WHERE attribute15 = l_h_reference;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line ('Err Msg : ' || 'row id is not available'
);
END;
INSERT INTO oal_line_mapping@sunr12toapp_222_qa
(oal_exec_id, oal_line_no,
oal_row_id, oal_loader_id,
interface_table_name
)
VALUES (cur_rcv_hdr.oal_exec_id, cur_rcv_hdr.oal_line_no,
lv_rowid, cur_rcv_hdr.oal_loader_id,
'RCV_HEADERS_INTERFACE'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
l_sqlerrm := SQLERRM;
UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
SET error_message = l_sqlerrm,
status_code = 'E'
WHERE oal_line_no = cur_rcv_hdr.oal_line_no
AND oal_exec_id = cur_rcv_hdr.oal_exec_id;
END;
IF l_sqlerrm IS NULL
THEN
l_interfaceflag := 'Y';
ELSE
l_interfaceflag := 'N';
END IF;
UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
SET int_exec_success_flag = l_interfaceflag
WHERE oal_line_no = cur_rcv_hdr.oal_line_no
AND oal_exec_id = cur_rcv_hdr.oal_exec_id;
FOR cur_rcv_trx IN rcv_trx (cur_rcv_hdr.h_reference)
LOOP
l_l_iface_header_id := cur_rcv_trx.l_iface_header_id;
IF (l_l_iface_header_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_HEADERS_INTERFACE_S.CURRVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_iface_header_id := l_actionvalidate_msg;
cur_rcv_trx.l_iface_header_id := l_actionvalidate_msg;
END IF;
l_l_group_id := cur_rcv_trx.l_group_id;
IF (l_l_group_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_INTERFACE_GROUPS_S.CURRVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_group_id := l_actionvalidate_msg;
cur_rcv_trx.l_group_id := l_actionvalidate_msg;
END IF;
l_l_iface_transaction_id := cur_rcv_trx.l_iface_transaction_id;
IF (l_l_iface_transaction_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_iface_transaction_id := l_actionvalidate_msg;
cur_rcv_trx.l_iface_transaction_id := l_actionvalidate_msg;
END IF;
l_l_last_updated_by := cur_rcv_trx.l_last_updated_by;
IF (l_l_last_updated_by IS NULL)
THEN
l_l_last_updated_by := fnd_global.user_id;
END IF;
l_l_created_by := cur_rcv_trx.l_created_by;
IF (l_l_created_by IS NULL)
THEN
l_l_created_by := fnd_global.user_id;
END IF;
l_l_creation_date := cur_rcv_trx.l_creation_date;
IF (l_l_creation_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_creation_date := d_actionvalidate_value;
cur_rcv_trx.l_creation_date := d_actionvalidate_value;
END IF;
l_l_last_update_date := cur_rcv_trx.l_last_update_date;
IF (l_l_last_update_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_last_update_date := d_actionvalidate_value;
cur_rcv_trx.l_last_update_date := d_actionvalidate_value;
END IF;
l_l_po_line_id := cur_rcv_trx.l_po_line_id;
IF (l_l_po_line_id IS NULL)
THEN
BEGIN
validate_query_new
( 'SELECT POL.PO_LINE_ID FROM PO_LINES_ALL POL, MTL_PARAMETERS MP, PO_HEADERS_ALL POH WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.ORG_ID = POL.ORG_ID AND MP.ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| ''' AND POL.ORG_ID = ##EBS_ORG_ID## AND POH.SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| ''' AND POL.LINE_NUM = '''
|| cur_rcv_trx.l_po_line_num
|| ''' AND POH.ENABLED_FLAG = ''Y'' AND POH.AUTHORIZATION_STATUS = ''APPROVED'' AND ((POH.END_DATE IS NULL) OR (POH.END_DATE > SYSDATE)) AND POH.TYPE_LOOKUP_CODE NOT IN (''RFQ'', ''QUOTATION'')',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_po_line_id := l_actionvalidate_msg;
cur_rcv_trx.l_po_line_id := l_actionvalidate_msg;
END IF;
l_l_validation_flag := cur_rcv_trx.l_validation_flag;
IF (l_l_validation_flag IS NULL)
THEN
l_l_validation_flag := 'Y';
END IF;
l_l_shipment_hdr_id := cur_rcv_trx.l_shipment_hdr_id;
l_l_location := cur_rcv_trx.l_location;
l_l_processing_status := cur_rcv_trx.l_processing_status;
IF (l_l_processing_status IS NULL)
THEN
l_l_processing_status := 'PENDING';
END IF;
l_l_transaction_status := cur_rcv_trx.l_transaction_status;
IF (l_l_transaction_status IS NULL)
THEN
l_l_transaction_status := 'PENDING';
END IF;
l_l_processing_mode := cur_rcv_trx.l_processing_mode;
IF (l_l_processing_mode IS NULL)
THEN
l_l_processing_mode := 'BATCH';
END IF;
l_l_line_location := cur_rcv_trx.l_line_location;
IF (l_l_line_location IS NULL)
THEN
BEGIN
validate_query_new
( 'SELECT POLL.LINE_LOCATION_ID FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL, PO_HEADERS_ALL POH
WHERE POLL.PO_LINE_ID = POL.PO_LINE_ID AND POL.PO_LINE_ID = '''
|| cur_rcv_trx.l_po_line_id
|| '''
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.ORG_ID = POL.ORG_ID
AND POLL.ORG_ID = ##EBS_ORG_ID## AND POLL.PO_HEADER_ID = POL.PO_HEADER_ID
AND POLL.SHIPMENT_NUM = '''
|| cur_rcv_trx.l_shipment_num
|| ''' AND POH.SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_line_location := l_actionvalidate_msg;
cur_rcv_trx.l_line_location := l_actionvalidate_msg;
END IF;
l_l_auto_trx_code := cur_rcv_trx.l_auto_trx_code;
IF (l_l_destination_type = 'INVENTORY')
THEN
l_l_auto_trx_code := 'DELIVER';
END IF;
l_l_source_document := cur_rcv_trx.l_source_document;
IF (l_l_source_document IS NULL)
THEN
l_l_source_document := 'PO';
END IF;
l_l_transaction_type := cur_rcv_trx.l_transaction_type;
IF (l_l_transaction_type IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV TRANSACTION TYPE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_trx.l_transaction_type
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_transaction_type := l_actionvalidate_msg;
cur_rcv_trx.l_transaction_type := l_actionvalidate_msg;
END IF;
l_l_transaction_date := cur_rcv_trx.l_transaction_date;
IF (l_l_transaction_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_transaction_date := d_actionvalidate_value;
cur_rcv_trx.l_transaction_date := d_actionvalidate_value;
END IF;
l_l_quantity := cur_rcv_trx.l_quantity;
l_l_uom := cur_rcv_trx.l_uom;
IF (l_l_uom IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CODE = '''
|| cur_rcv_trx.l_uom
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_uom := l_actionvalidate_msg;
cur_rcv_trx.l_uom := l_actionvalidate_msg;
END IF;
l_l_item_num := cur_rcv_trx.l_item_num;
IF (l_l_item_num IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT MSI.INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B MSI,MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.SEGMENT1 = '''
|| cur_rcv_trx.l_item_num
|| ''' AND MP.ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_item_num := l_actionvalidate_msg;
cur_rcv_trx.l_item_num := l_actionvalidate_msg;
END IF;
l_l_to_organization := cur_rcv_trx.l_to_organization;
IF (l_l_to_organization IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| ''' AND ((DISABLE_DATE IS NULL)
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_to_organization := l_actionvalidate_msg;
cur_rcv_trx.l_to_organization := l_actionvalidate_msg;
END IF;
l_l_item_description := cur_rcv_trx.l_item_description;
l_l_shipment_num := cur_rcv_trx.l_shipment_num;
l_l_ship_to_location := cur_rcv_trx.l_ship_to_location;
IF (l_l_ship_to_location IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
|| cur_rcv_trx.l_ship_to_location
|| ''' AND SHIP_TO_SITE_FLAG = ''Y''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_ship_to_location := l_actionvalidate_msg;
cur_rcv_trx.l_ship_to_location := l_actionvalidate_msg;
END IF;
l_l_po_number := cur_rcv_trx.l_po_number;
IF (l_l_po_number IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| ''' AND ORG_ID = ##EBS_ORG_ID## AND ENABLED_FLAG = ''Y''
AND AUTHORIZATION_STATUS = ''APPROVED'' AND ((END_DATE IS NULL) OR (END_DATE > SYSDATE))
AND TYPE_LOOKUP_CODE NOT IN (''RFQ'',''QUOTATION'')',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_po_number := l_actionvalidate_msg;
cur_rcv_trx.l_po_number := l_actionvalidate_msg;
END IF;
l_l_destination_type := cur_rcv_trx.l_destination_type;
IF (l_l_destination_type IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV DESTINATION TYPE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_trx.l_destination_type
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_destination_type := l_actionvalidate_msg;
cur_rcv_trx.l_destination_type := l_actionvalidate_msg;
END IF;
l_l_deliver_to_location := cur_rcv_trx.l_deliver_to_location;
IF (l_l_deliver_to_location IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
|| cur_rcv_trx.l_deliver_to_location
|| ''' AND SHIP_TO_SITE_FLAG = ''Y''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_deliver_to_location := l_actionvalidate_msg;
cur_rcv_trx.l_deliver_to_location := l_actionvalidate_msg;
END IF;
l_l_subinventory := cur_rcv_trx.l_subinventory;
l_l_reference := cur_rcv_trx.l_reference;
l_l_po_line_num := cur_rcv_trx.l_po_line_num;
l_l_category := cur_rcv_trx.l_category;
l_l_project := cur_rcv_trx.l_project;
IF (l_l_project IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT PP.PROJECT_ID FROM PA_PROJECT_TYPES_ALL PT, PA_PROJECTS_ALL PP WHERE PT.PROJECT_TYPE = PP.PROJECT_TYPE
AND NVL (PP.TEMPLATE_FLAG, ''N'') NOT IN (''Y'') AND PA_SECURITY.ALLOW_QUERY (PP.PROJECT_ID) = ''Y''
AND PT.PROJECT_TYPE NOT IN (''AWARD_PROJECT'') AND PT.ORG_ID = PP.ORG_ID AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (PP.PROJECT_STATUS_CODE,''NEW_TXNS'') = ''Y''
AND PP.SEGMENT1 = '''
|| cur_rcv_trx.l_project
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_project := l_actionvalidate_msg;
cur_rcv_trx.l_project := l_actionvalidate_msg;
END IF;
BEGIN
INSERT INTO rcv_transactions_interface
(header_interface_id, GROUP_ID,
interface_transaction_id, last_updated_by,
created_by, creation_date,
last_update_date, po_line_id,
validation_flag, shipment_header_id,
location_id, processing_status_code,
transaction_status_code, processing_mode_code,
po_line_location_id, auto_transact_code,
source_document_code, transaction_type,
transaction_date, quantity, unit_of_measure,
item_id, to_organization_id,
item_description, shipment_num,
ship_to_location_id, po_header_id,
destination_type_code, deliver_to_location_id,
subinventory, attribute15, attribute14,
category_id, project_id
)
VALUES (l_l_iface_header_id, l_l_group_id,
l_l_iface_transaction_id, l_l_last_updated_by,
l_l_created_by, l_l_creation_date,
l_l_last_update_date, l_l_po_line_id,
l_l_validation_flag, l_l_shipment_hdr_id,
l_l_location, l_l_processing_status,
l_l_transaction_status, l_l_processing_mode,
l_l_line_location, l_l_auto_trx_code,
l_l_source_document, l_l_transaction_type,
l_l_transaction_date, l_l_quantity, l_l_uom,
l_l_item_num, l_l_to_organization,
l_l_item_description, l_l_shipment_num,
l_l_ship_to_location, l_l_po_number,
l_l_destination_type, l_l_deliver_to_location,
l_l_subinventory, l_l_reference, l_l_po_line_num,
l_l_category, l_l_project
);
BEGIN
SELECT DISTINCT ROWID
INTO lv_rowid
FROM rcv_transactions_interface
WHERE attribute15 = l_l_reference;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line ( 'Err Msg : '
|| 'row id is not available'
);
END;
INSERT INTO oal_line_mapping@sunr12toapp_222_qa
(oal_exec_id, oal_line_no,
oal_row_id, oal_loader_id,
interface_table_name
)
VALUES (cur_rcv_trx.oal_exec_id, cur_rcv_trx.oal_line_no,
lv_rowid, cur_rcv_trx.oal_loader_id,
'RCV_TRANSACTIONS_INTERFACE'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
l_sqlerrm := SQLERRM;
UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
SET error_message = l_sqlerrm,
status_code = 'E'
WHERE oal_line_no = cur_rcv_trx.oal_line_no
AND oal_exec_id = cur_rcv_trx.oal_exec_id;
END;
IF l_sqlerrm IS NULL
THEN
l_interfaceflag := 'Y';
ELSE
l_interfaceflag := 'N';
END IF;
UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
SET int_exec_success_flag = l_interfaceflag
WHERE oal_line_no = cur_rcv_trx.oal_line_no
AND oal_exec_id = cur_rcv_trx.oal_exec_id;
END LOOP;
COMMIT;
END LOOP;
END;
DECLARE
l_reqid NUMBER;
l_formatconversion DATE;
l_validate_flag VARCHAR2 (1000);
l_validate_msg VARCHAR2 (1000);
l_actionvalidate_msg VARCHAR2 (1000);
l_actionvalidate_flag VARCHAR2 (1000);
d_actionvalidate_msg VARCHAR2 (1000);
d_actionvalidate_flag VARCHAR2 (1000);
d_actionvalidate_value DATE;
l_sqlerrm VARCHAR2 (2000);
lv_rowid VARCHAR2 (1000);
l_condt_flag VARCHAR2 (10);
l_interfaceflag VARCHAR2 (10);
l_h_iface_header_id NUMBER;
l_h_group_id NUMBER;
l_h_validation_flag VARCHAR2 (2000);
l_h_last_update_date DATE;
l_h_creation_date DATE;
l_h_created_by NUMBER;
l_h_last_updated_by NUMBER;
l_h_processing_status VARCHAR2 (2000);
l_h_transaction_type VARCHAR2 (2000);
l_h_vendor_name VARCHAR2 (2000);
l_h_auto_trx_code VARCHAR2 (2000);
l_h_receipt_source VARCHAR2 (2000);
l_h_ship_to_organization VARCHAR2 (2000);
l_h_expected_receipt_date DATE;
l_h_reference VARCHAR2 (2000);
l_h_waybill_airbill VARCHAR2 (2000);
l_h_bill_of_lading VARCHAR2 (2000);
l_l_iface_header_id NUMBER;
l_l_group_id NUMBER;
l_l_iface_transaction_id NUMBER;
l_l_last_updated_by NUMBER;
l_l_created_by NUMBER;
l_l_creation_date DATE;
l_l_last_update_date DATE;
l_l_po_line_id NUMBER;
l_l_validation_flag VARCHAR2 (2000);
l_l_shipment_hdr_id NUMBER;
l_l_location VARCHAR2 (2000);
l_l_processing_status VARCHAR2 (2000);
l_l_transaction_status VARCHAR2 (2000);
l_l_processing_mode VARCHAR2 (2000);
l_l_line_location VARCHAR2 (2000);
l_l_auto_trx_code VARCHAR2 (2000);
l_l_source_document VARCHAR2 (2000);
l_l_transaction_type VARCHAR2 (2000);
l_l_transaction_date DATE;
l_l_quantity NUMBER;
l_l_uom VARCHAR2 (2000);
l_l_item_num VARCHAR2 (2000);
l_l_to_organization VARCHAR2 (2000);
l_l_item_description VARCHAR2 (2000);
l_l_shipment_num VARCHAR2 (2000);
l_l_ship_to_location VARCHAR2 (2000);
l_l_po_number VARCHAR2 (2000);
l_l_destination_type VARCHAR2 (2000);
l_l_deliver_to_location VARCHAR2 (2000);
l_l_subinventory VARCHAR2 (2000);
l_l_reference VARCHAR2 (2000);
l_l_po_line_num NUMBER;
l_l_category VARCHAR2 (2000);
l_l_project VARCHAR2 (2000);
CURSOR rcv_hdr
IS
SELECT DISTINCT rcv_hdr_po_receipt5.ROWID,
rcv_hdr_po_receipt5.oal_exec_id,
rcv_hdr_po_receipt5.oal_loader_id,
rcv_hdr_po_receipt5.oal_line_no,
rcv_hdr_po_receipt5.h_iface_header_id,
rcv_hdr_po_receipt5.h_group_id,
rcv_hdr_po_receipt5.h_validation_flag,
rcv_hdr_po_receipt5.h_last_update_date,
rcv_hdr_po_receipt5.h_creation_date,
rcv_hdr_po_receipt5.h_created_by,
rcv_hdr_po_receipt5.h_last_updated_by,
rcv_hdr_po_receipt5.h_processing_status,
rcv_hdr_po_receipt5.h_transaction_type,
rcv_hdr_po_receipt5.h_vendor_name,
rcv_hdr_po_receipt5.h_auto_trx_code,
rcv_hdr_po_receipt5.h_receipt_source,
rcv_hdr_po_receipt5.h_ship_to_organization,
rcv_hdr_po_receipt5.h_expected_receipt_date,
rcv_hdr_po_receipt5.h_reference,
rcv_hdr_po_receipt5.h_waybill_airbill,
rcv_hdr_po_receipt5.h_bill_of_lading
FROM rcv_hdr_po_receipt5@sunr12toapp_222_qa
WHERE NVL (rcv_hdr_po_receipt5.status_code, 'NULL') LIKE
NVL (p_status_code, 'NULL');
CURSOR rcv_trx (p_h_reference VARCHAR2)
IS
SELECT DISTINCT rcv_trx_po_receipt5.ROWID,
rcv_trx_po_receipt5.oal_exec_id,
rcv_trx_po_receipt5.oal_loader_id,
rcv_trx_po_receipt5.oal_line_no,
rcv_trx_po_receipt5.l_deliver_to_location,
rcv_trx_po_receipt5.l_iface_header_id,
rcv_trx_po_receipt5.l_group_id,
rcv_trx_po_receipt5.l_iface_transaction_id,
rcv_trx_po_receipt5.l_last_updated_by,
rcv_trx_po_receipt5.l_created_by,
rcv_trx_po_receipt5.l_creation_date,
rcv_trx_po_receipt5.l_last_update_date,
rcv_trx_po_receipt5.l_po_line_id,
rcv_trx_po_receipt5.l_validation_flag,
rcv_trx_po_receipt5.l_shipment_hdr_id,
rcv_trx_po_receipt5.l_location,
rcv_trx_po_receipt5.l_processing_status,
rcv_trx_po_receipt5.l_transaction_status,
rcv_trx_po_receipt5.l_processing_mode,
rcv_trx_po_receipt5.l_line_location,
rcv_trx_po_receipt5.l_auto_trx_code,
rcv_trx_po_receipt5.l_source_document,
rcv_trx_po_receipt5.l_transaction_type,
rcv_trx_po_receipt5.l_transaction_date,
rcv_trx_po_receipt5.l_quantity,
rcv_trx_po_receipt5.l_uom,
rcv_trx_po_receipt5.l_item_num,
rcv_trx_po_receipt5.l_to_organization,
rcv_trx_po_receipt5.l_item_description,
rcv_trx_po_receipt5.l_shipment_num,
rcv_trx_po_receipt5.l_ship_to_location,
rcv_trx_po_receipt5.l_po_number,
rcv_trx_po_receipt5.l_destination_type,
rcv_trx_po_receipt5.l_subinventory,
rcv_trx_po_receipt5.l_reference,
rcv_trx_po_receipt5.l_po_line_num,
rcv_trx_po_receipt5.l_category,
rcv_trx_po_receipt5.l_project
FROM rcv_trx_po_receipt5@sunr12toapp_222_qa
WHERE NVL (rcv_trx_po_receipt5.status_code, 'NULL') LIKE
NVL (p_status_code, 'NULL')
AND NVL (rcv_trx_po_receipt5.l_reference, '-123') =
NVL (p_h_reference, '-123');
PROCEDURE validate_query_new (
qry IN VARCHAR2,
validate_flag OUT VARCHAR2,
validate_msg OUT VARCHAR2
)
IS
l_qry VARCHAR2 (5000);
column_names VARCHAR2 (1000);
multi_column_flag NUMBER (10) := 0;
l_tname VARCHAR2 (1000);
l_datatype VARCHAR2 (1000);
l_start NUMBER;
l_end NUMBER;
BEGIN
l_qry := qry;
validate_flag := 'S';
validate_msg := NULL;
column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);
IF multi_column_flag <> 0
THEN
validate_flag := 'F';
validate_msg := 'Multi Column';
ELSE
l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
l_end := INSTR (UPPER (qry), 'WHERE', 1);
IF (l_end > 0)
THEN
l_tname := SUBSTR (qry, l_start, (l_end - l_start));
ELSE
l_tname := SUBSTR (qry, l_start);
END IF;
IF NVL (INSTR (l_tname, ' '), 0) = 0
THEN
BEGIN
SELECT data_type
INTO l_datatype
FROM all_tab_columns
WHERE table_name = UPPER (TRIM (l_tname))
AND column_name = UPPER (TRIM (column_names));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END;
END IF;
IF l_datatype LIKE 'DATE'
THEN
l_qry :=
'SELECT TO_CHAR('
|| column_names
|| ',''DD-MON-RRRR HH24:MI:SS'') '
|| SUBSTR (qry, INSTR (UPPER (qry), 'FROM'));
END IF;
BEGIN
EXECUTE IMMEDIATE l_qry
INTO validate_msg;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
validate_flag := 'S';
validate_msg := NULL;
WHEN TOO_MANY_ROWS
THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM ('
|| l_qry
|| ') WHERE ROWNUM = 1'
INTO validate_msg;
EXCEPTION
WHEN OTHERS
THEN
validate_flag := 'F';
validate_msg := 'Err in Too Many Rows...' || SQLERRM;
END;
WHEN OTHERS
THEN
validate_flag := 'F';
validate_msg := 'Err in Qry : ' || SQLERRM;
END;
END IF;
END validate_query_new;
PROCEDURE validate_query_date (
qry IN VARCHAR2,
date_validate_flag OUT VARCHAR2,
date_validate_msg OUT VARCHAR2,
date_validate_msg1 OUT DATE
)
IS
l_qry VARCHAR2 (5000);
column_names VARCHAR2 (1000);
multi_column_flag NUMBER (10) := 0;
l_tname VARCHAR2 (1000);
l_datatype VARCHAR2 (1000);
l_start NUMBER;
l_end NUMBER;
BEGIN
l_qry := qry;
date_validate_flag := 'S';
date_validate_msg := NULL;
date_validate_msg1 := NULL;
column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);
IF multi_column_flag <> 0
THEN
date_validate_flag := 'F';
date_validate_msg := 'Multi Column';
ELSE
l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
l_end := INSTR (UPPER (qry), 'WHERE', 1);
IF (l_end > 0)
THEN
l_tname := SUBSTR (qry, l_start, (l_end - l_start));
ELSE
l_tname := SUBSTR (qry, l_start);
END IF;
IF NVL (INSTR (l_tname, ' '), 0) = 0
THEN
BEGIN
SELECT data_type
INTO l_datatype
FROM all_tab_columns
WHERE table_name = UPPER (TRIM (l_tname))
AND column_name = UPPER (TRIM (column_names));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
NULL;
END;
END IF;
BEGIN
EXECUTE IMMEDIATE l_qry
INTO date_validate_msg1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
date_validate_flag := 'S';
date_validate_msg1 := NULL;
WHEN TOO_MANY_ROWS
THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM ('
|| l_qry
|| ') WHERE ROWNUM = 1'
INTO date_validate_msg1;
EXCEPTION
WHEN OTHERS
THEN
date_validate_flag := 'F';
date_validate_msg :=
'Err in Too Many Rows...' || SQLERRM;
END;
WHEN OTHERS
THEN
date_validate_flag := 'F';
date_validate_msg := 'Err in Qry : ' || SQLERRM;
END;
END IF;
END validate_query_date;
BEGIN
apps.fnd_global.apps_initialize (##ebs_user_id##,
##ebs_resp_id##,
##ebs_appl_id##,
NULL,
NULL
);
FOR cur_rcv_hdr IN rcv_hdr
LOOP
l_h_iface_header_id := cur_rcv_hdr.h_iface_header_id;
IF (l_h_iface_header_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_iface_header_id := l_actionvalidate_msg;
cur_rcv_hdr.h_iface_header_id := l_actionvalidate_msg;
END IF;
l_h_group_id := cur_rcv_hdr.h_group_id;
IF (l_h_group_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_group_id := l_actionvalidate_msg;
cur_rcv_hdr.h_group_id := l_actionvalidate_msg;
END IF;
l_h_validation_flag := cur_rcv_hdr.h_validation_flag;
IF (l_h_validation_flag IS NULL)
THEN
l_h_validation_flag := 'Y';
END IF;
l_h_last_update_date := cur_rcv_hdr.h_last_update_date;
IF (l_h_last_update_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_h_last_update_date := d_actionvalidate_value;
cur_rcv_hdr.h_last_update_date := d_actionvalidate_value;
END IF;
l_h_creation_date := cur_rcv_hdr.h_creation_date;
IF (l_h_creation_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_h_creation_date := d_actionvalidate_value;
cur_rcv_hdr.h_creation_date := d_actionvalidate_value;
END IF;
l_h_created_by := cur_rcv_hdr.h_created_by;
IF (l_h_created_by IS NULL)
THEN
l_h_created_by := fnd_global.user_id;
END IF;
l_h_last_updated_by := cur_rcv_hdr.h_last_updated_by;
IF (l_h_last_updated_by IS NULL)
THEN
l_h_last_updated_by := fnd_global.user_id;
END IF;
l_h_processing_status := cur_rcv_hdr.h_processing_status;
IF (l_h_processing_status IS NULL)
THEN
l_h_processing_status := 'PENDING';
END IF;
l_h_transaction_type := cur_rcv_hdr.h_transaction_type;
IF (l_h_transaction_type IS NULL)
THEN
l_h_transaction_type := 'NEW';
END IF;
l_h_vendor_name := cur_rcv_hdr.h_vendor_name;
IF (l_h_vendor_name IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT VENDOR_ID FROM PO_VENDORS WHERE ENABLED_FLAG = ''Y'' AND ((END_DATE_ACTIVE IS NULL)
OR (END_DATE_ACTIVE > SYSDATE)) AND VENDOR_NAME = '''
|| cur_rcv_hdr.h_vendor_name
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_vendor_name := l_actionvalidate_msg;
cur_rcv_hdr.h_vendor_name := l_actionvalidate_msg;
END IF;
l_h_auto_trx_code := cur_rcv_hdr.h_auto_trx_code;
IF (l_h_auto_trx_code IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''AUTO TRANSACT CODE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_hdr.h_auto_trx_code
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_auto_trx_code := l_actionvalidate_msg;
cur_rcv_hdr.h_auto_trx_code := l_actionvalidate_msg;
END IF;
l_h_receipt_source := cur_rcv_hdr.h_receipt_source;
IF (l_h_receipt_source IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''SHIPMENT SOURCE TYPE''
AND ENABLED_FLAG = ''Y'' AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE))
AND DISPLAYED_FIELD = '''
|| cur_rcv_hdr.h_receipt_source
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_receipt_source := l_actionvalidate_msg;
cur_rcv_hdr.h_receipt_source := l_actionvalidate_msg;
END IF;
l_h_ship_to_organization := cur_rcv_hdr.h_ship_to_organization;
IF (l_h_ship_to_organization IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
|| cur_rcv_hdr.h_ship_to_organization
|| ''' AND ((DISABLE_DATE IS NULL)
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_h_ship_to_organization := l_actionvalidate_msg;
cur_rcv_hdr.h_ship_to_organization := l_actionvalidate_msg;
END IF;
l_h_expected_receipt_date := cur_rcv_hdr.h_expected_receipt_date;
l_h_reference := cur_rcv_hdr.h_reference;
l_h_waybill_airbill := cur_rcv_hdr.h_waybill_airbill;
l_h_bill_of_lading := cur_rcv_hdr.h_bill_of_lading;
BEGIN
INSERT INTO rcv_headers_interface
(header_interface_id, GROUP_ID,
validation_flag, last_update_date,
creation_date, created_by,
last_updated_by, processing_status_code,
transaction_type, vendor_id,
auto_transact_code, receipt_source_code,
ship_to_organization_id, expected_receipt_date,
attribute15, waybill_airbill_num, bill_of_lading
)
VALUES (l_h_iface_header_id, l_h_group_id,
l_h_validation_flag, l_h_last_update_date,
l_h_creation_date, l_h_created_by,
l_h_last_updated_by, l_h_processing_status,
l_h_transaction_type, l_h_vendor_name,
l_h_auto_trx_code, l_h_receipt_source,
l_h_ship_to_organization, l_h_expected_receipt_date,
l_h_reference, l_h_waybill_airbill, l_h_bill_of_lading
);
BEGIN
SELECT DISTINCT ROWID
INTO lv_rowid
FROM rcv_headers_interface
WHERE attribute15 = l_h_reference;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line ('Err Msg : ' || 'row id is not available'
);
END;
INSERT INTO oal_line_mapping@sunr12toapp_222_qa
(oal_exec_id, oal_line_no,
oal_row_id, oal_loader_id,
interface_table_name
)
VALUES (cur_rcv_hdr.oal_exec_id, cur_rcv_hdr.oal_line_no,
lv_rowid, cur_rcv_hdr.oal_loader_id,
'RCV_HEADERS_INTERFACE'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
l_sqlerrm := SQLERRM;
UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
SET error_message = l_sqlerrm,
status_code = 'E'
WHERE oal_line_no = cur_rcv_hdr.oal_line_no
AND oal_exec_id = cur_rcv_hdr.oal_exec_id;
END;
IF l_sqlerrm IS NULL
THEN
l_interfaceflag := 'Y';
ELSE
l_interfaceflag := 'N';
END IF;
UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
SET int_exec_success_flag = l_interfaceflag
WHERE oal_line_no = cur_rcv_hdr.oal_line_no
AND oal_exec_id = cur_rcv_hdr.oal_exec_id;
FOR cur_rcv_trx IN rcv_trx (cur_rcv_hdr.h_reference)
LOOP
l_l_iface_header_id := cur_rcv_trx.l_iface_header_id;
IF (l_l_iface_header_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_HEADERS_INTERFACE_S.CURRVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_iface_header_id := l_actionvalidate_msg;
cur_rcv_trx.l_iface_header_id := l_actionvalidate_msg;
END IF;
l_l_group_id := cur_rcv_trx.l_group_id;
IF (l_l_group_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_INTERFACE_GROUPS_S.CURRVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_group_id := l_actionvalidate_msg;
cur_rcv_trx.l_group_id := l_actionvalidate_msg;
END IF;
l_l_iface_transaction_id := cur_rcv_trx.l_iface_transaction_id;
IF (l_l_iface_transaction_id IS NULL)
THEN
BEGIN
validate_query_new
('SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL FROM DUAL',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_iface_transaction_id := l_actionvalidate_msg;
cur_rcv_trx.l_iface_transaction_id := l_actionvalidate_msg;
END IF;
l_l_last_updated_by := cur_rcv_trx.l_last_updated_by;
IF (l_l_last_updated_by IS NULL)
THEN
l_l_last_updated_by := fnd_global.user_id;
END IF;
l_l_created_by := cur_rcv_trx.l_created_by;
IF (l_l_created_by IS NULL)
THEN
l_l_created_by := fnd_global.user_id;
END IF;
l_l_creation_date := cur_rcv_trx.l_creation_date;
IF (l_l_creation_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_creation_date := d_actionvalidate_value;
cur_rcv_trx.l_creation_date := d_actionvalidate_value;
END IF;
l_l_last_update_date := cur_rcv_trx.l_last_update_date;
IF (l_l_last_update_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_last_update_date := d_actionvalidate_value;
cur_rcv_trx.l_last_update_date := d_actionvalidate_value;
END IF;
l_l_po_line_id := cur_rcv_trx.l_po_line_id;
IF (l_l_po_line_id IS NULL)
THEN
BEGIN
validate_query_new
( 'SELECT POL.PO_LINE_ID FROM PO_LINES_ALL POL, MTL_PARAMETERS MP, PO_HEADERS_ALL POH WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.ORG_ID = POL.ORG_ID AND MP.ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| ''' AND POL.ORG_ID = ##EBS_ORG_ID## AND POH.SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| ''' AND POL.LINE_NUM = '''
|| cur_rcv_trx.l_po_line_num
|| ''' AND POH.ENABLED_FLAG = ''Y'' AND POH.AUTHORIZATION_STATUS = ''APPROVED'' AND ((POH.END_DATE IS NULL) OR (POH.END_DATE > SYSDATE)) AND POH.TYPE_LOOKUP_CODE NOT IN (''RFQ'', ''QUOTATION'')',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_po_line_id := l_actionvalidate_msg;
cur_rcv_trx.l_po_line_id := l_actionvalidate_msg;
END IF;
l_l_validation_flag := cur_rcv_trx.l_validation_flag;
IF (l_l_validation_flag IS NULL)
THEN
l_l_validation_flag := 'Y';
END IF;
l_l_shipment_hdr_id := cur_rcv_trx.l_shipment_hdr_id;
l_l_location := cur_rcv_trx.l_location;
l_l_processing_status := cur_rcv_trx.l_processing_status;
IF (l_l_processing_status IS NULL)
THEN
l_l_processing_status := 'PENDING';
END IF;
l_l_transaction_status := cur_rcv_trx.l_transaction_status;
IF (l_l_transaction_status IS NULL)
THEN
l_l_transaction_status := 'PENDING';
END IF;
l_l_processing_mode := cur_rcv_trx.l_processing_mode;
IF (l_l_processing_mode IS NULL)
THEN
l_l_processing_mode := 'BATCH';
END IF;
l_l_line_location := cur_rcv_trx.l_line_location;
IF (l_l_line_location IS NULL)
THEN
BEGIN
validate_query_new
( 'SELECT POLL.LINE_LOCATION_ID FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL, PO_HEADERS_ALL POH
WHERE POLL.PO_LINE_ID = POL.PO_LINE_ID AND POL.PO_LINE_ID = '''
|| cur_rcv_trx.l_po_line_id
|| '''
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.ORG_ID = POL.ORG_ID
AND POLL.ORG_ID = ##EBS_ORG_ID## AND POLL.PO_HEADER_ID = POL.PO_HEADER_ID
AND POLL.SHIPMENT_NUM = '''
|| cur_rcv_trx.l_shipment_num
|| ''' AND POH.SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_line_location := l_actionvalidate_msg;
cur_rcv_trx.l_line_location := l_actionvalidate_msg;
END IF;
l_l_auto_trx_code := cur_rcv_trx.l_auto_trx_code;
IF (l_l_destination_type = 'INVENTORY')
THEN
l_l_auto_trx_code := 'DELIVER';
END IF;
l_l_source_document := cur_rcv_trx.l_source_document;
IF (l_l_source_document IS NULL)
THEN
l_l_source_document := 'PO';
END IF;
l_l_transaction_type := cur_rcv_trx.l_transaction_type;
IF (l_l_transaction_type IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV TRANSACTION TYPE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_trx.l_transaction_type
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_transaction_type := l_actionvalidate_msg;
cur_rcv_trx.l_transaction_type := l_actionvalidate_msg;
END IF;
l_l_transaction_date := cur_rcv_trx.l_transaction_date;
IF (l_l_transaction_date IS NULL)
THEN
BEGIN
validate_query_date ('SELECT SYSDATE FROM DUAL',
d_actionvalidate_flag,
d_actionvalidate_msg,
d_actionvalidate_value
);
END;
IF d_actionvalidate_flag = 'F'
THEN
d_actionvalidate_value := NULL;
END IF;
l_l_transaction_date := d_actionvalidate_value;
cur_rcv_trx.l_transaction_date := d_actionvalidate_value;
END IF;
l_l_quantity := cur_rcv_trx.l_quantity;
l_l_uom := cur_rcv_trx.l_uom;
IF (l_l_uom IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CODE = '''
|| cur_rcv_trx.l_uom
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_uom := l_actionvalidate_msg;
cur_rcv_trx.l_uom := l_actionvalidate_msg;
END IF;
l_l_item_num := cur_rcv_trx.l_item_num;
IF (l_l_item_num IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT MSI.INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B MSI,MTL_PARAMETERS MP
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.SEGMENT1 = '''
|| cur_rcv_trx.l_item_num
|| ''' AND MP.ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_item_num := l_actionvalidate_msg;
cur_rcv_trx.l_item_num := l_actionvalidate_msg;
END IF;
l_l_to_organization := cur_rcv_trx.l_to_organization;
IF (l_l_to_organization IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
|| cur_rcv_trx.l_to_organization
|| ''' AND ((DISABLE_DATE IS NULL)
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_to_organization := l_actionvalidate_msg;
cur_rcv_trx.l_to_organization := l_actionvalidate_msg;
END IF;
l_l_item_description := cur_rcv_trx.l_item_description;
l_l_shipment_num := cur_rcv_trx.l_shipment_num;
l_l_ship_to_location := cur_rcv_trx.l_ship_to_location;
IF (l_l_ship_to_location IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
|| cur_rcv_trx.l_ship_to_location
|| ''' AND SHIP_TO_SITE_FLAG = ''Y''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_ship_to_location := l_actionvalidate_msg;
cur_rcv_trx.l_ship_to_location := l_actionvalidate_msg;
END IF;
l_l_po_number := cur_rcv_trx.l_po_number;
IF (l_l_po_number IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 = '''
|| cur_rcv_trx.l_po_number
|| ''' AND ORG_ID = ##EBS_ORG_ID## AND ENABLED_FLAG = ''Y''
AND AUTHORIZATION_STATUS = ''APPROVED'' AND ((END_DATE IS NULL) OR (END_DATE > SYSDATE))
AND TYPE_LOOKUP_CODE NOT IN (''RFQ'',''QUOTATION'')',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_po_number := l_actionvalidate_msg;
cur_rcv_trx.l_po_number := l_actionvalidate_msg;
END IF;
l_l_destination_type := cur_rcv_trx.l_destination_type;
IF (l_l_destination_type IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV DESTINATION TYPE'' AND ENABLED_FLAG = ''Y''
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
|| cur_rcv_trx.l_destination_type
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_destination_type := l_actionvalidate_msg;
cur_rcv_trx.l_destination_type := l_actionvalidate_msg;
END IF;
l_l_deliver_to_location := cur_rcv_trx.l_deliver_to_location;
IF (l_l_deliver_to_location IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
|| cur_rcv_trx.l_deliver_to_location
|| ''' AND SHIP_TO_SITE_FLAG = ''Y''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_deliver_to_location := l_actionvalidate_msg;
cur_rcv_trx.l_deliver_to_location := l_actionvalidate_msg;
END IF;
l_l_subinventory := cur_rcv_trx.l_subinventory;
l_l_reference := cur_rcv_trx.l_reference;
l_l_po_line_num := cur_rcv_trx.l_po_line_num;
l_l_category := cur_rcv_trx.l_category;
l_l_project := cur_rcv_trx.l_project;
IF (l_l_project IS NOT NULL)
THEN
BEGIN
validate_query_new
( 'SELECT PP.PROJECT_ID FROM PA_PROJECT_TYPES_ALL PT, PA_PROJECTS_ALL PP WHERE PT.PROJECT_TYPE = PP.PROJECT_TYPE
AND NVL (PP.TEMPLATE_FLAG, ''N'') NOT IN (''Y'') AND PA_SECURITY.ALLOW_QUERY (PP.PROJECT_ID) = ''Y''
AND PT.PROJECT_TYPE NOT IN (''AWARD_PROJECT'') AND PT.ORG_ID = PP.ORG_ID AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (PP.PROJECT_STATUS_CODE,''NEW_TXNS'') = ''Y''
AND PP.SEGMENT1 = '''
|| cur_rcv_trx.l_project
|| '''',
l_actionvalidate_flag,
l_actionvalidate_msg
);
END;
IF l_actionvalidate_flag = 'F'
THEN
l_actionvalidate_msg := NULL;
END IF;
l_l_project := l_actionvalidate_msg;
cur_rcv_trx.l_project := l_actionvalidate_msg;
END IF;
BEGIN
INSERT INTO rcv_transactions_interface
(header_interface_id, GROUP_ID,
interface_transaction_id, last_updated_by,
created_by, creation_date,
last_update_date, po_line_id,
validation_flag, shipment_header_id,
location_id, processing_status_code,
transaction_status_code, processing_mode_code,
po_line_location_id, auto_transact_code,
source_document_code, transaction_type,
transaction_date, quantity, unit_of_measure,
item_id, to_organization_id,
item_description, shipment_num,
ship_to_location_id, po_header_id,
destination_type_code, deliver_to_location_id,
subinventory, attribute15, attribute14,
category_id, project_id
)
VALUES (l_l_iface_header_id, l_l_group_id,
l_l_iface_transaction_id, l_l_last_updated_by,
l_l_created_by, l_l_creation_date,
l_l_last_update_date, l_l_po_line_id,
l_l_validation_flag, l_l_shipment_hdr_id,
l_l_location, l_l_processing_status,
l_l_transaction_status, l_l_processing_mode,
l_l_line_location, l_l_auto_trx_code,
l_l_source_document, l_l_transaction_type,
l_l_transaction_date, l_l_quantity, l_l_uom,
l_l_item_num, l_l_to_organization,
l_l_item_description, l_l_shipment_num,
l_l_ship_to_location, l_l_po_number,
l_l_destination_type, l_l_deliver_to_location,
l_l_subinventory, l_l_reference, l_l_po_line_num,
l_l_category, l_l_project
);
BEGIN
SELECT DISTINCT ROWID
INTO lv_rowid
FROM rcv_transactions_interface
WHERE attribute15 = l_l_reference;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line ( 'Err Msg : '
|| 'row id is not available'
);
END;
INSERT INTO oal_line_mapping@sunr12toapp_222_qa
(oal_exec_id, oal_line_no,
oal_row_id, oal_loader_id,
interface_table_name
)
VALUES (cur_rcv_trx.oal_exec_id, cur_rcv_trx.oal_line_no,
lv_rowid, cur_rcv_trx.oal_loader_id,
'RCV_TRANSACTIONS_INTERFACE'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
l_sqlerrm := SQLERRM;
UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
SET error_message = l_sqlerrm,
status_code = 'E'
WHERE oal_line_no = cur_rcv_trx.oal_line_no
AND oal_exec_id = cur_rcv_trx.oal_exec_id;
END;
IF l_sqlerrm IS NULL
THEN
l_interfaceflag := 'Y';
ELSE
l_interfaceflag := 'N';
END IF;
UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
SET int_exec_success_flag = l_interfaceflag
WHERE oal_line_no = cur_rcv_trx.oal_line_no
AND oal_exec_id = cur_rcv_trx.oal_exec_id;
END LOOP;
COMMIT;
END LOOP;
END;
No comments:
Post a Comment