[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
can_delete NUMBER := 0;
SELECT mtrh.header_id
, mtrl.line_id
, mtrh.move_order_type
, mtrl.line_status
, mtrl.quantity quantity
, NVL(mtrl.quantity_detailed, 0) quantity_detailed
, NVL(mtrl.quantity_delivered, 0) quantity_delivered
, NVL(required_quantity, 0) required_quantity
, mtrl.txn_source_line_id
, mtrl.txn_source_id
, mtrl.organization_id
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_status IN (5, 6, 9)
AND ( p_organization_id IS NULL
OR mtrh.organization_id = p_organization_id )
AND ( p_organization_id IS NULL
OR mtrl.organization_id = p_organization_id )
AND ( p_mo_type_id IS NULL
OR mtrh.move_order_type = p_mo_type_id )
AND ( l_date_from IS NULL
OR TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
>= TO_DATE ( TO_CHAR ( l_date_from , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' ) )
AND TO_DATE ( TO_CHAR ( mtrl.creation_date , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
<= TO_DATE ( TO_CHAR ( l_date_to , 'DD-MM-YYYY' ) , 'DD-MM-YYYY' )
ORDER BY mtrh.header_id;
can_delete := 0;
can_delete := 1;
/* Delete MTRL if inventory interface has completed successfully and
order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y')
or is cancelled (CANCELLED_FLAG = 'Y')*/
wdd_exists := 'Y'; -- Added for bug # 6469970
SELECT 1
INTO can_delete
FROM wsh_delivery_details wsd
WHERE wsd.move_order_line_id = mo_line.line_id
AND wsd.inv_interfaced_flag = 'Y'
AND ROWNUM < 2;
IF (can_delete = 1) THEN
SELECT 1
INTO can_delete
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
AND (oola.open_flag <> 'Y'
OR oola.cancelled_flag = 'Y'
)
AND ROWNUM < 2;
can_delete := 0;
IF (can_delete = 0) THEN
BEGIN
SELECT 1
INTO can_delete
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
AND ( (NVL(open_flag, 'Y') = 'N') OR cancelled_flag = 'Y')
AND NOT EXISTS (
SELECT 1
FROM wsh_delivery_details wdd
WHERE wdd.source_line_id = oola.line_id
AND wdd.inv_interfaced_flag IN ('N', 'P')
AND wdd.released_status <> 'D')
AND ROWNUM =1;
can_delete := 0;
END IF; --END IF delete canceled SO lines
IF (can_delete = 0 AND mo_line.line_status = 5 AND
((mo_line.quantity = mo_line.quantity_delivered
AND
mo_line.quantity_detailed = mo_line.quantity_delivered)
OR
(mo_line.quantity > 0 AND mo_line.quantity_delivered = 0))) THEN
BEGIN
SELECT 1
INTO can_delete
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
AND (NVL(open_flag,'Y') ='Y' and cancelled_flag='N')
AND NOT EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mo_line.line_id)
AND NOT EXISTS (
SELECT 1
FROM wsh_delivery_details wdd
WHERE wdd.source_line_id = oola.line_id
AND wdd.source_code = 'OE'
AND wdd.move_order_line_id = mo_line.line_id)
AND ROWNUM = 1;
can_delete := 0;
END IF; --END IF delete completely backordered lines
IF (can_delete = 0) THEN
BEGIN
SELECT distinct wdd.inv_interfaced_flag into inv_int_flag
FROM wsh_delivery_details wdd
WHERE wdd.move_order_line_id = mo_line.line_id;
can_delete := 1;
can_delete := 0;
can_delete := 1; -- No corresponding wdd record
can_delete := 0; -- At least one inv_int_flag is set to 'N' or 'P' in wdd
IF (can_delete = 1 and wdd_exists = 'N') THEN
BEGIN
SELECT 0
INTO can_delete
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
AND (oola.open_flag = 'Y' AND NVL(oola.CANCELLED_FLAG,'N')='N');
can_delete := 0;
can_delete := 1;
IF (can_delete = 0) THEN
BEGIN
SELECT 'Y'
INTO oel_exists
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
and rownum < 2;
can_delete := 0;
can_delete := 1;
can_delete := 1; -- there is no oel record
/* Delete MTRL if allocations doesn't exist
(MMTT should not exist for this line id, MMTT.move_order_line_id = MTRL.line_id)
and order_line is not open (OE_ORDER_LINES_ALL.OPEN_FLAG <> 'Y') */
BEGIN
SELECT 1
INTO can_delete
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mo_line.line_id
AND ROWNUM < 2;
can_delete := 0;
SELECT 1
INTO can_delete
FROM oe_order_lines_all oola
WHERE oola.line_id = mo_line.txn_source_line_id
AND (oola.open_flag <> 'Y'
OR oola.cancelled_flag = 'Y'
)
AND ROWNUM < 2;
can_delete := 0;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = mo_line.txn_source_id
and organization_id = mo_line.organization_id;
inv_trx_util_pub.TRACE('other exc.when getting entity_type setting can_delete as 0 '|| sqlerrm);
can_delete := 1;
select 0
into can_delete
from wip_discrete_jobs
where wip_entity_id = mo_line.txn_source_id
and organization_id = mo_line.organization_id
and status_type <> 12;
inv_trx_util_pub.TRACE('other exc. when l_entity_type in (3,7,8) setting can_delete as 0 '|| sqlerrm);
can_delete := 1;
select 0
into can_delete
from wip_repetitive_schedules
where wip_entity_id = mo_line.txn_source_id
and organization_id = mo_line.organization_id
and status_type <> 5;
inv_trx_util_pub.TRACE('other exc. when l_entity_type = 2 setting can_delete as 0 '|| sqlerrm);
can_delete := 1;
select 0
into can_delete
from wip_flow_schedules
where wip_entity_id = mo_line.txn_source_id
and organization_id = mo_line.organization_id
and status <> 2;
inv_trx_util_pub.TRACE('other exc. when l_entity_type = 4 setting can_delete as 0 '|| sqlerrm);
can_delete := 1;
SELECT 0
INTO can_delete
FROM wip_discrete_jobs
WHERE wip_entity_id = mo_line.txn_source_id
AND organization_id = mo_line.organization_id
AND status_type not in (12,7);--closed or cancelled
inv_trx_util_pub.TRACE('other exc. when l_entity_type = 6 setting can_delete as 0 '|| sqlerrm);
can_delete := 1;
SELECT 1
INTO can_delete
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mo_line.line_id
AND ROWNUM < 2;
can_delete := 0;
can_delete := 1;
IF (can_delete = 1) THEN
-- inv_trx_util_pub.TRACE('DELETED lines---mo_line.line_id= ' ||mo_line.line_id ,'INVMOPG',9);
DELETE FROM mtl_txn_request_lines
WHERE line_id = mo_line.line_id;
can_delete := 0;
DELETE FROM mtl_txn_request_headers
WHERE header_id = l_prev_header_id
AND NOT EXISTS( SELECT 1
FROM mtl_txn_request_lines
WHERE header_id = l_prev_header_id);
can_delete := 0;
DELETE FROM mtl_txn_request_headers
WHERE header_id = mo_line.header_id
AND NOT EXISTS( SELECT 1
FROM mtl_txn_request_lines
WHERE header_id = mo_line.header_id);
can_delete := 0;
INSERT INTO mtl_purge_header
(
purge_id
, last_update_date
, last_updated_by
, last_update_login
, creation_date
, created_by
, purge_date
, move_order_type
, archive_flag
, purge_name
, organization_id
, creation_date_from
, creation_date_to
, lines_purged
)
VALUES (
mtl_material_transactions_s.NEXTVAL
, SYSDATE
, fnd_global.user_id
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, p_mo_type_id
, NULL
, p_purge_name
, p_organization_id
, l_date_from
, l_date_to
, l_count
);