[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
WDJ.WIP_ENTITY_ID
BULK COLLECT INTO G_WIP_ENTITY_ID_PL_TBL
FROM
WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE
WHERE WDJ.ORGANIZATION_ID = P_ORGANIZATION_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 5
AND WDJ.WIP_ENTITY_ID in (
SELECT
WIP_ENTITY_ID
FROM
WSM_LOT_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND STATUS <> 4
UNION ALL
SELECT
WIP_ENTITY_ID
FROM
WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
UNION ALL
SELECT
WIP_ENTITY_ID
FROM
WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
UNION ALL
SELECT
TRANSACTION_SOURCE_ID
FROM
MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRANSACTION_SOURCE_TYPE_ID = 5
UNION ALL
SELECT
TRANSACTION_SOURCE_ID
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE COSTED_FLAG in ( 'N' , 'E' )
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND ORGANIZATION_ID = P_ORGANIZATION_ID
UNION ALL
SELECT
WIP_ENTITY_ID
FROM
WIP_OPERATION_YIELDS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND STATUS in ( 1 , 3 )
UNION ALL
SELECT
SJ.WIP_ENTITY_ID
FROM
WSM_SM_STARTING_JOBS SJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND ( WMT.STATUS <> 4
OR NVL(WMT.COSTED
,1) <> 4 )
UNION ALL
SELECT
RJ.WIP_ENTITY_ID
FROM
WSM_SM_RESULTING_JOBS RJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND ( WMT.STATUS <> 4
OR NVL(WMT.COSTED
,1) <> 4 )
UNION ALL
SELECT
PD.WIP_ENTITY_ID
FROM
PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PL
WHERE PD.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
AND PD.PO_LINE_ID is not null
AND PD.LINE_LOCATION_ID is not null
AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND pr.po_release_id (+) = PD.PO_RELEASE_ID
AND NVL(PR.CANCEL_FLAG
,'N') = 'N'
AND ( PL.QUANTITY_RECEIVED < ( PL.QUANTITY - PL.QUANTITY_CANCELLED ) )
UNION ALL
SELECT
PRL.WIP_ENTITY_ID
FROM
PO_REQUISITION_LINES_ALL PRL
WHERE PRL.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
AND NVL(PRL.CANCEL_FLAG
,'N') = 'N'
AND PRL.LINE_LOCATION_ID is null
UNION ALL
SELECT
PRI.WIP_ENTITY_ID
FROM
PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID );
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
UNION ALL
SELECT
1
FROM
WSM_LOT_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND STATUS <> 4 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending Move Transactions');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND WIP_ENTITY_ID = WIP_ENTITY_ID_1 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending Resource Transactions');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRANSACTION_SOURCE_ID = WIP_ENTITY_ID_1
AND TRANSACTION_SOURCE_TYPE_ID = 5 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending Material Transactions');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND TRANSACTION_SOURCE_ID = WIP_ENTITY_ID_1
AND COSTED_FLAG IN ( 'N' , 'E' )
AND TRANSACTION_SOURCE_TYPE_ID = 5 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Uncosted Material Transactions');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
WIP_OPERATION_YIELDS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
AND WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND STATUS in ( 1 , 3 ) );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending Operation Yield');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
WSM_SM_STARTING_JOBS SJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE SJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND WMT.STATUS <> 4
UNION
SELECT
1
FROM
WSM_SM_RESULTING_JOBS RJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE RJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND WMT.STATUS <> 4 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending WIP Lot Transactions');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
WSM_SM_STARTING_JOBS SJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE SJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND NVL(WMT.COSTED
,1) <> 4
UNION
SELECT
1
FROM
WSM_SM_RESULTING_JOBS RJ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE RJ.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND WMT.ORGANIZATION_ID = P_ORGANIZATION_ID
AND NVL(WMT.COSTED
,1) <> 4 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Uncosted WIP Lot Transactions ');
SELECT
1
INTO L_COUNT
FROM
DUAL
WHERE exists (
SELECT
1
FROM
PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PL
WHERE PD.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
AND PD.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND PD.PO_LINE_ID is not null
AND PD.LINE_LOCATION_ID is not null
AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND pr.po_release_id (+) = PD.PO_RELEASE_ID
AND NVL(PR.CANCEL_FLAG
,'N') = 'N'
AND ( PL.QUANTITY_RECEIVED < ( PL.QUANTITY - PL.QUANTITY_CANCELLED ) ) )
OR exists (
SELECT
1
FROM
PO_REQUISITION_LINES_ALL PRL
WHERE PRL.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
AND PRL.WIP_ENTITY_ID = WIP_ENTITY_ID_1
AND NVL(PRL.CANCEL_FLAG
,'N') = 'N'
AND PRL.LINE_LOCATION_ID is null )
OR exists (
SELECT
1
FROM
PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.DESTINATION_ORGANIZATION_ID = P_ORGANIZATION_ID
AND PRI.WIP_ENTITY_ID = WIP_ENTITY_ID_1 );
INSERT INTO WSM_PEND_TXN_REP_TMP
VALUES (WIP_ENTITY_ID_1
,'Pending PO Requisitions');
FOR SELECT
*
FROM
WSM_PEND_TXN_REP_TMP
WHERE WIP_ENTITY_ID = WIP_ENTITY_ID_1;*\
\*SELECT wip_entity_id , ptxn_table
BULK COLLECT INTO G_WSM_PEND_TXN_REP_PL_TBL
FROM WSM_PEND_TXN_REP_TMP
WHERE WIP_ENTITY_ID = WIP_ENTITY_ID_1;*\
RETURN ('and not exists (select 1 from WIP_RESERVATIONS_V wrv where wrv.wip_entity_id = wdj.wip_entity_id)');
SELECT
ORGANIZATION_NAME
INTO ORG_NAME
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
SELECT
MEANING
INTO ENTITY_TYPE
FROM
MFG_LOOKUPS
WHERE LOOKUP_TYPE = 'WIP_ENTITY'
AND LOOKUP_CODE = P_ENTITY_TYPE;