[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WIP_DJ_CLOSE_TEMP_S.nextval
INTO x_group_id
FROM DUAL ;
/*For bug 8808014(FP 8674750), the following insert statement will not insert
CMRO related Work Order into WDCT. A separate insert statement will
responsible to insert CMRO related Work Order into WDCT. Insert
statement for WDCT was split for performance reason*/
SELECT RSLT.WIP_ENTITY_ID ,
RSLT.ORGANIZATION_ID ,
RSLT.WIP_ENTITY_NAME ,
RSLT.STATUS_TYPE ,
RSLT.PRIMARY_ITEM_ID ,
p_act_close_date ,
x_group_id
BULK COLLECT INTO l_close_temp_tbl1
FROM (
/* Changed SQL as per bug 10310132 for performance issue
* Changed the SQL to use Union ALL
* */
SELECT DJ.WIP_ENTITY_ID,
DJ.ORGANIZATION_ID,
WE.WIP_ENTITY_NAME,
DJ.PRIMARY_ITEM_ID,
DJ.STATUS_TYPE
FROM ORG_ACCT_PERIODS AP,
WIP_DISCRETE_JOBS DJ,
WIP_ENTITIES WE
WHERE DJ.ORGANIZATION_ID = p_organization_id
AND WE.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.OPEN_FLAG = 'Y'
AND WE.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND NOT EXISTS
(SELECT 'X'
FROM WIP_DJ_CLOSE_TEMP WDCT
WHERE WDCT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND DJ.STATUS_TYPE IN
-- (1,3,4,5,6,7,9,11,15)
(WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG ,
WIP_CONSTANTS.HOLD ,
WIP_CONSTANTS.CANCELLED ,
WIP_CONSTANTS.FAIL_BOM,
WIP_CONSTANTS.FAIL_ROUT,
WIP_CONSTANTS.FAIL_CLOSE
)
AND ( p_class_type IS NULL OR
DJ.CLASS_CODE IN ( SELECT CLASS_CODE
FROM WIP_ACCOUNTING_CLASSES
WHERE CLASS_TYPE = p_class_type
AND ORGANIZATION_ID = p_organization_id ))
AND ( p_from_class IS NULL OR DJ.CLASS_CODE >= p_from_class )
AND ( p_to_class IS NULL OR DJ.CLASS_CODE <= p_to_class )
AND ( p_from_job IS NULL )
AND ( WE.WIP_ENTITY_NAME <= NVL(p_to_job , WE.WIP_ENTITY_NAME ))
AND ( p_from_start_date IS NULL OR
DJ.SCHEDULED_START_DATE >= p_from_start_date )
AND ( p_to_start_date IS NULL OR
DJ.SCHEDULED_START_DATE < p_to_start_date +1) /*Bug#13426239- Include the to_start_date,to_completion_date and to_release_date. Added +1 to the dates*/
AND ( p_from_completion_date IS NULL OR
DJ.DATE_COMPLETED >= p_from_completion_date )
AND ( p_to_completion_date IS NULL OR
DJ.DATE_COMPLETED < p_to_completion_date +1 )
AND ( p_from_release_date IS NULL OR
DJ.DATE_RELEASED >= p_from_release_date )
AND ( p_to_release_date IS NULL OR
DJ.DATE_RELEASED < p_to_release_date +1)
AND ( p_status IS NULL OR DJ.STATUS_TYPE = p_status)
-- AND ( DJ.DATE_RELEASED <= p_act_close_date) /* Bug 5007538 */
AND ( p_exclude_reserved_jobs <> '1' OR NOT EXISTS
(SELECT 'X'FROM WIP_RESERVATIONS_V WRV
WHERE WRV.WIP_ENTITY_ID = WE.WIP_ENTITY_ID ))
AND ( p_uncompleted_jobs <> '1' or DJ.STATUS_TYPE IN (4,5)) /* Bug 15896253 */
AND ( p_exclude_pending_txn_jobs <> '1' OR ( NOT EXISTS
(SELECT 'X' FROM WIP_MOVE_TXN_INTERFACE WMTI
WHERE WMTI.ORGANIZATION_ID = p_organization_id
AND WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM WIP_COST_TXN_INTERFACE WCTI
WHERE WCTI.ORGANIZATION_ID = p_organization_id
AND WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE ORGANIZATION_ID = p_organization_id
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMTT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_n2) */ 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.COSTED_FLAG IN ('N','E')
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.ORGANIZATION_ID = p_organization_id
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM WIP_OPERATION_YIELDS WOY
WHERE WOY.ORGANIZATION_ID = p_organization_id
AND WOY.STATUS IN (1, 3)
AND WOY.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
/* FP for 11.5.10 bug 9411629. Added extra clause to ensure that
close jobs process avoids picking jobs for which LPN is loaded
but not yet dropped, for the option excluding pending transaction
bugs. */
AND NOT EXISTS
(SELECT 'X'
FROM WIP_LPN_COMPLETIONS WLC,
WMS_LICENSE_PLATE_NUMBERS LPN,
MTL_TXN_REQUEST_LINES MTRL
WHERE WLC.ORGANIZATION_ID = p_organization_id
AND WLC.LPN_ID = LPN.LPN_ID
AND MTRL.LPN_ID = LPN.LPN_ID
AND MTRL.TXN_SOURCE_ID = WLC.WIP_ENTITY_ID
AND MTRL.LINE_STATUS = 7
AND LPN.LPN_CONTEXT = 2
AND WLC.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND (WE.ENTITY_TYPE <> 5 OR
(WE.ENTITY_TYPE = 5 AND NOT EXISTS
(SELECT 'X'
FROM wsm_sm_starting_jobs sj,
wsm_split_merge_transactions wmt
WHERE sj.wip_entity_id = we.wip_entity_id
AND sj.transaction_id = wmt.transaction_id
AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))
AND (WE.ENTITY_TYPE <> 5 OR
(WE.ENTITY_TYPE = 5 AND NOT EXISTS
(SELECT 'X'
FROM wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions wmt
WHERE rj.wip_entity_id = we.wip_entity_id
AND rj.transaction_id = wmt.transaction_id
AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))))
UNION ALL
SELECT DJ.WIP_ENTITY_ID ,
DJ.ORGANIZATION_ID ,
WE.WIP_ENTITY_NAME ,
DJ.PRIMARY_ITEM_ID ,
DJ.STATUS_TYPE
FROM ORG_ACCT_PERIODS AP ,
WIP_DISCRETE_JOBS DJ ,
WIP_ENTITIES WE
WHERE DJ.ORGANIZATION_ID = p_organization_id
AND WE.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.OPEN_FLAG = 'Y'
AND WE.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND NOT EXISTS ( SELECT 'X'
FROM WIP_DJ_CLOSE_TEMP WDCT
WHERE WDCT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID )
AND DJ.STATUS_TYPE IN
-- (1,3,4,5,6,7,9,11,15)
(WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG ,
WIP_CONSTANTS.HOLD ,
WIP_CONSTANTS.CANCELLED ,
WIP_CONSTANTS.FAIL_BOM,
WIP_CONSTANTS.FAIL_ROUT,
WIP_CONSTANTS.FAIL_CLOSE
)
AND ( p_class_type IS NULL OR DJ.CLASS_CODE IN ( SELECT CLASS_CODE
FROM WIP_ACCOUNTING_CLASSES
WHERE CLASS_TYPE = p_class_type
AND ORGANIZATION_ID = p_organization_id))
AND ( p_from_class IS NULL OR DJ.CLASS_CODE >= p_from_class )
AND ( p_to_class IS NULL OR DJ.CLASS_CODE <= p_to_class )
AND ( p_from_job IS NOT NULL AND WE.WIP_ENTITY_NAME >= p_from_job )
AND ( WE.WIP_ENTITY_NAME <= NVL ( p_to_job , WE.WIP_ENTITY_NAME ) )
AND ( p_from_start_date IS NULL OR DJ.SCHEDULED_START_DATE >= p_from_start_date )
AND ( p_to_start_date IS NULL OR DJ.SCHEDULED_START_DATE < p_to_start_date +1 ) /*Bug#13426239 - To_Start_date,to_completion_date,to_release_date to be included. Added +1 to the to_dates */
AND ( p_from_completion_date IS NULL OR DJ.DATE_COMPLETED >= p_from_completion_date )
AND ( p_to_completion_date IS NULL OR DJ.DATE_COMPLETED < p_to_completion_date +1 )
AND ( p_from_release_date IS NULL OR DJ.DATE_RELEASED >= p_from_release_date )
AND ( p_to_release_date IS NULL OR DJ.DATE_RELEASED < p_to_release_date +1 )
AND ( p_status IS NULL OR DJ.STATUS_TYPE = p_status )
AND ( p_exclude_reserved_jobs <> '1'
OR NOT EXISTS ( SELECT 'X'
FROM WIP_RESERVATIONS_V WRV
WHERE WRV.WIP_ENTITY_ID = WE.WIP_ENTITY_ID ) )
AND ( p_uncompleted_jobs <> '1' or DJ.STATUS_TYPE IN (4,5)) /* Bug 15896253 */
AND ( p_exclude_pending_txn_jobs <> '1'
OR ( NOT EXISTS ( SELECT 'X'
FROM WIP_MOVE_TXN_INTERFACE WMTI
WHERE WMTI.ORGANIZATION_ID = p_organization_id
AND WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS ( SELECT 'X'
FROM WIP_COST_TXN_INTERFACE WCTI
WHERE WCTI.ORGANIZATION_ID = p_organization_id
AND WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS ( SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE ORGANIZATION_ID = p_organization_id
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMTT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS ( SELECT /*+ index ( mmt MTL_MATERIAL_TRANSACTIONS_n2) */ 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.COSTED_FLAG IN ( 'N' , 'E' )
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.ORGANIZATION_ID = p_organization_id
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS ( SELECT 'X'
FROM WIP_OPERATION_YIELDS WOY
WHERE WOY.ORGANIZATION_ID = p_organization_id
AND WOY.STATUS IN ( 1 , 3)
AND WOY.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND ( WE.ENTITY_TYPE <> 5
OR ( WE.ENTITY_TYPE = 5
AND NOT EXISTS ( SELECT 'X'
FROM WSM_SM_STARTING_JOBS SJ ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE SJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND SJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND ( WMT.STATUS <> 4 OR NVL ( WMT.COSTED , 1) <> 4))))
AND ( WE.ENTITY_TYPE <> 5
OR ( WE.ENTITY_TYPE = 5
AND NOT EXISTS ( SELECT 'X'
FROM WSM_SM_RESULTING_JOBS RJ ,
WSM_SPLIT_MERGE_TRANSACTIONS WMT
WHERE RJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND RJ.TRANSACTION_ID = WMT.TRANSACTION_ID
AND ( WMT.STATUS <> 4 OR NVL ( WMT.COSTED , 1) <> 4))))))
/*for bug 8808014(FP 8674750), exclude CMRO work order*/
AND NOT (WE.ENTITY_TYPE = 6 AND DJ.MAINTENANCE_OBJECT_SOURCE = 2)) RSLT
GROUP BY RSLT.WIP_ENTITY_ID, RSLT.ORGANIZATION_ID, RSLT.WIP_ENTITY_NAME,
RSLT.PRIMARY_ITEM_ID, RSLT.STATUS_TYPE ;
SELECT DJ.WIP_ENTITY_ID,
DJ.ORGANIZATION_ID,
WE.WIP_ENTITY_NAME,
DJ.STATUS_TYPE,
DJ.PRIMARY_ITEM_ID,
p_act_close_date,
x_group_id
BULK COLLECT INTO l_close_temp_tbl2
FROM ORG_ACCT_PERIODS AP,
WIP_DISCRETE_JOBS DJ,
WIP_ENTITIES WE
WHERE DJ.ORGANIZATION_ID = p_organization_id
AND WE.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND AP.OPEN_FLAG = 'Y'
AND WE.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND NOT EXISTS
(SELECT 'X'
FROM WIP_DJ_CLOSE_TEMP WDCT
WHERE WDCT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND DJ.STATUS_TYPE IN
-- (1,3,4,5,6,7,9,11,15)
(WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG,
WIP_CONSTANTS.COMP_NOCHRG ,
WIP_CONSTANTS.HOLD ,
WIP_CONSTANTS.CANCELLED ,
WIP_CONSTANTS.FAIL_BOM,
WIP_CONSTANTS.FAIL_ROUT,
WIP_CONSTANTS.FAIL_CLOSE
)
AND ( p_class_type IS NULL OR
DJ.CLASS_CODE IN ( SELECT CLASS_CODE
FROM WIP_ACCOUNTING_CLASSES
WHERE CLASS_TYPE = p_class_type
AND ORGANIZATION_ID = p_organization_id ))
AND ( p_from_class IS NULL OR DJ.CLASS_CODE >= p_from_class )
AND ( p_to_class IS NULL OR DJ.CLASS_CODE <= p_to_class )
AND ( p_from_job IS NULL OR WE.WIP_ENTITY_NAME >= p_from_job )
AND ( p_to_job IS NULL OR WE.WIP_ENTITY_NAME <= p_to_job )
AND ( p_from_start_date IS NULL OR
DJ.SCHEDULED_START_DATE >= p_from_start_date )
AND ( p_to_start_date IS NULL OR
DJ.SCHEDULED_START_DATE = p_from_completion_date )
AND ( p_to_completion_date IS NULL OR
DJ.DATE_COMPLETED < p_to_completion_date +1 )
AND ( p_from_release_date IS NULL OR
DJ.DATE_RELEASED >= p_from_release_date )
AND ( p_to_release_date IS NULL OR
DJ.DATE_RELEASED < p_to_release_date +1)
AND ( p_status IS NULL OR DJ.STATUS_TYPE = p_status)
-- AND ( DJ.DATE_RELEASED <= p_act_close_date) /* Bug 5007538 */
AND ( p_exclude_reserved_jobs <> '1' OR NOT EXISTS
(SELECT 'X'FROM WIP_RESERVATIONS_V WRV
WHERE WRV.WIP_ENTITY_ID = WE.WIP_ENTITY_ID ))
AND ( p_uncompleted_jobs <> '1' or DJ.STATUS_TYPE IN (4,5)) /* Bug 15896253 */
AND ( p_exclude_pending_txn_jobs <> '1' OR ( NOT EXISTS
(SELECT 'X' FROM WIP_MOVE_TXN_INTERFACE WMTI
WHERE WMTI.ORGANIZATION_ID = p_organization_id
AND WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM WIP_COST_TXN_INTERFACE WCTI
WHERE WCTI.ORGANIZATION_ID = p_organization_id
AND WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE ORGANIZATION_ID = p_organization_id
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMTT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_n2) */ 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.COSTED_FLAG IN ('N','E')
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.ORGANIZATION_ID = p_organization_id
AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
AND NOT EXISTS
(SELECT 'X'
FROM WIP_OPERATION_YIELDS WOY
WHERE WOY.ORGANIZATION_ID = p_organization_id
AND WOY.STATUS IN (1, 3)
AND WOY.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
AND (WE.ENTITY_TYPE <> 5 OR
(WE.ENTITY_TYPE = 5 AND NOT EXISTS
(SELECT 'X'
FROM wsm_sm_starting_jobs sj,
wsm_split_merge_transactions wmt
WHERE sj.wip_entity_id = we.wip_entity_id
AND sj.transaction_id = wmt.transaction_id
AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))
AND (WE.ENTITY_TYPE <> 5 OR
(WE.ENTITY_TYPE = 5 AND NOT EXISTS
(SELECT 'X'
FROM wsm_sm_resulting_jobs rj,
wsm_split_merge_transactions wmt
WHERE rj.wip_entity_id = we.wip_entity_id
AND rj.transaction_id = wmt.transaction_id
AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))))
/*Fix for 8808014(FP 8674750), added validation for CMRO, check whether the CMRO
item is in location_type_code = WIP*/
AND (WE.ENTITY_TYPE = 6 AND DJ.MAINTENANCE_OBJECT_SOURCE = 2 AND NOT EXISTS
(SELECT 'x'
FROM CSI_ITEM_INSTANCES CII
WHERE CII.WIP_JOB_ID = WE.WIP_ENTITY_ID
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE >= SYSDATE))
AND CII.LOCATION_TYPE_CODE = 'WIP'
AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE))))
GROUP BY DJ.WIP_ENTITY_ID, DJ.ORGANIZATION_ID, WE.WIP_ENTITY_NAME,
DJ.PRIMARY_ITEM_ID, DJ.STATUS_TYPE ;
INSERT INTO WIP_DJ_CLOSE_TEMP
VALUES l_close_final_tbl(indx);
wip_logger.log('Error in Insertion into WIP_DJ_CLOSE_TEMP',l_return_status);
l_close_temp_tbl1.delete;
l_close_temp_tbl2.delete;
l_close_final_tbl.delete;
SELECT count(*)
INTO l_number_temp
FROM WIP_DJ_CLOSE_TEMP
WHERE group_id = x_group_id ;
fnd_file.put_line(FND_FILE.LOG,'Records inserted in close temp '||to_char(l_number_temp));
DELETE FROM wip_dj_close_temp wdct
WHERE wdct.organization_id = p_organization_id AND
wdct.group_id = p_group_id AND
wdct.actual_close_date <
(SELECT wdj.date_released
FROM wip_discrete_jobs wdj
WHERE wdj.wip_entity_id = wdct.wip_entity_id AND
wdj.organization_id = p_organization_id)
RETURNING wdct.wip_entity_id
BULK COLLECT INTO l_failed_ids;
UPDATE wip_discrete_jobs
SET status_type = WIP_CONSTANTS.FAIL_CLOSE
WHERE organization_id = p_organization_id AND
wip_entity_id = l_failed_ids(i);
l_failed_ids.DELETE;
SELECT WIP_ENTITY_NAME
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID IN
(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 MMTT
WHERE ORGANIZATION_ID = p_organization_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID NOT IN
(SELECT TXN_SOURCE_ID
FROM MTL_TXN_REQUEST_LINES
WHERE TXN_SOURCE_ID = MMTT.TRANSACTION_SOURCE_ID
AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND LINE_STATUS = 9)
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 DISTINCT WIP_ENTITY_ID
FROM WIP_OPERATION_YIELDS
WHERE ORGANIZATION_ID = p_organization_id
AND STATUS IN (1, 3)
UNION ALL
SELECT WLC.WIP_ENTITY_ID
FROM WIP_LPN_COMPLETIONS WLC,
WMS_LICENSE_PLATE_NUMBERS LPN ,
MTL_TXN_REQUEST_LINES MTRL
WHERE WLC.ORGANIZATION_ID = p_organization_id
AND WLC.LPN_ID = LPN.LPN_ID
AND MTRL.LPN_ID = LPN.LPN_ID
AND MTRL.txn_source_id = WLC.wip_entity_id
AND MTRL.line_status = 7 /*Bugfix 6455522 added one condition for mtrl.line_status=7*/
AND LPN.LPN_CONTEXT = 2);
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_NAME = l_failed_jobs ;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE WIP_ENTITY_ID IN
(SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99;
select wdct.wip_entity_id,
we.organization_id
from wip_dj_close_temp wdct,
wip_entities we
where we.wip_entity_id = wdct.wip_entity_id
and we.organization_id = wdct.organization_id
and wdct.group_id = p_group_id
and wdct.organization_id = p_organization_id;
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
select wdct.wip_entity_id,
we.wip_entity_name
from wip_dj_close_temp wdct,
wip_entities we
where we.wip_entity_id = wdct.wip_entity_id
and we.organization_id = wdct.organization_id
and wdct.group_id = p_group_id
and wdct.organization_id = p_organization_id;
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE WIP_ENTITY_ID IN
(SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99;
select wdct.wip_entity_id,
we.entity_type,
we.wip_entity_name
from wip_dj_close_temp wdct,
wip_entities we
where we.wip_entity_id = wdct.wip_entity_id
and we.organization_id = wdct.organization_id
and wdct.group_id = p_group_id
and wdct.organization_id = p_organization_id;
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE WIP_ENTITY_ID IN
(SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99;
select wdct.wip_entity_id,
we.entity_type
from wip_dj_close_temp wdct,
wip_entities we
where we.wip_entity_id = wdct.wip_entity_id
and we.organization_id = wdct.organization_id
and wdct.group_id = p_group_id
and wdct.organization_id = p_organization_id;
select propagate_job_change_to_po
into l_propagate_job_change_to_po
from wip_parameters
where organization_id = p_organization_id;
SELECT WIP_ENTITY_NAME
FROM WIP_DJ_CLOSE_TEMP
WHERE WIP_ENTITY_ID IN
(SELECT wdct.WIP_ENTITY_ID
FROM WIP_TRANSACTIONS wt,
WIP_DJ_CLOSE_TEMP wdct
WHERE wdct.GROUP_ID = p_group_id
AND wdct.ORGANIZATION_ID = p_organization_id
AND wdct.WIP_ENTITY_ID = wt.WIP_ENTITY_ID
AND wt.ORGANIZATION_ID = p_organization_id
AND wt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE
UNION
SELECT wdct.WIP_ENTITY_ID
FROM MTL_MATERIAL_TRANSACTIONS mmt,
WIP_DJ_CLOSE_TEMP wdct
WHERE wdct.GROUP_ID = p_group_id
AND wdct.ORGANIZATION_ID = p_organization_id
AND wdct.WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID
AND mmt.TRANSACTION_SOURCE_TYPE_ID = 5
AND mmt.ORGANIZATION_ID = p_organization_id
AND mmt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE) ;
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_NAME = l_failed_jobs ;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE WIP_ENTITY_ID IN
(SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99;
SELECT WIP_ENTITY_NAME
FROM WIP_DJ_CLOSE_TEMP WDCT
WHERE wdct.GROUP_ID = p_group_id
AND wdct.ORGANIZATION_ID = p_organization_id
AND EXISTS
(SELECT '1'
FROM PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PL
WHERE PD.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
AND 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.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 (PL.CANCEL_FLAG IS NULL OR
PL.CANCEL_FLAG = 'N')
AND (PL.QUANTITY_RECEIVED<(PL.QUANTITY-PL.QUANTITY_CANCELLED))
AND NVL(PL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
)
--
OR EXISTS
(SELECT '1'
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
AND nvl(PRL.cancel_flag, 'N') = 'N'
AND PRL.LINE_LOCATION_ID is NULL
AND NVL(PRL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' /* BUG:13019044 Added condition of Finally Closed requisition lines */
)
OR EXISTS
(SELECT '1'
FROM PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
) ;
SELECT WIP_ENTITY_NAME
FROM WIP_DJ_CLOSE_TEMP WDCT
WHERE wdct.GROUP_ID = p_group_id
AND wdct.ORGANIZATION_ID = p_organization_id
AND EXISTS
(SELECT '1'
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PD1
WHERE PLL.LINE_LOCATION_ID = PD1.LINE_LOCATION_ID
AND PD1.WIP_ENTITY_ID = WDCT.WIP_ENTITY_ID
AND PD1.DESTINATION_ORGANIZATION_ID = p_organization_id
AND PLL.QUANTITY_RECEIVED >
(SELECT SUM(QUANTITY_DELIVERED)
FROM PO_DISTRIBUTIONS_ALL PD2
WHERE PD2.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PD2.LINE_LOCATION_ID IS NOT NULL
AND PD2.PO_LINE_ID IS NOT NULL)
);
UPDATE WIP_DJ_CLOSE_TEMP
SET STATUS_TYPE = 99
WHERE WIP_ENTITY_NAME = l_failed_jobs ;
UPDATE WIP_DISCRETE_JOBS
SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE WIP_ENTITY_ID IN
(SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = p_group_id
AND ORGANIZATION_ID = p_organization_id
AND STATUS_TYPE = 99;
procedure DELETE_RESERVATIONS
(
x_Returnstatus OUT NOCOPY VARCHAR2 ,
p_organization_id IN NUMBER ,
p_group_id IN NUMBER
)
IS
l_params wip_logger.param_tbl_t;
/* SELECT wrv.reservation_id
FROM wip_reservations_v wrv,
wip_dj_close_temp wdct
WHERE wdct.organization_id = p_organization_id
AND wdct.group_id = p_group_id
AND wdct.wip_entity_id = wrv.wip_entity_id;
select /*+ leading(wdct) index(mr MTL_RESERVATIONS_N9) */ -- otimizacao
mr.reservation_id
from
wip_dj_close_temp wdct, -- otimizacao
mtl_reservations mr, -- otimizacao
mtl_sales_orders mso, -- otimizacao
oe_order_lines_all ool -- otimizacao
where ((wdct.organization_id= p_organization_id and wdct.group_id= p_group_id)
and mr.supply_source_header_id=wdct.wip_entity_id)
and mr.demand_source_type_id in (2,8) -- otimizacao
and mr.supply_source_type_id = 5 -- otimizacao
and mso.sales_order_id = mr.demand_source_header_id -- otimizacao
and ool.line_id = mr.demand_source_line_id; -- otimizacao
fnd_file.put_line(FND_FILE.LOG,'delete Existing reservations');
wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.delete_reservation',
p_params => l_params,
x_returnStatus => l_return_Status);
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg
, p_rsv_rec => l_rsv
, p_serial_number => l_serialnumber -- no serial control
);
p_procName => 'wip_close_priv.delete_reservation',
p_procReturnStatus => x_returnStatus,
p_msg => 'procedure normal exit',
x_returnStatus => l_return_status);
wip_logger.exitPoint(p_procName=>'wip_jobclose_priv.delete_reservation',
p_procReturnStatus => x_returnStatus,
p_msg => l_msg,
x_returnStatus => l_return_Status);
END DELETE_RESERVATIONS ;
SELECT COUNT(*)
INTO l_std_asst_jobs
FROM WIP_DJ_CLOSE_TEMP TEMP,
WIP_DISCRETE_JOBS WDJ,
WIP_ACCOUNTING_CLASSES WAC
WHERE WDJ.WIP_ENTITY_ID = TEMP.WIP_ENTITY_ID
AND TEMP.ORGANIZATION_ID = p_organization_id
AND WDJ.ORGANIZATION_ID = TEMP.ORGANIZATION_ID
AND WAC.ORGANIZATION_ID = TEMP.ORGANIZATION_ID
AND WDJ.CLASS_CODE = WAC.CLASS_CODE
AND TEMP.GROUP_ID = p_group_id
AND WAC.CLASS_TYPE IN
--(1,3,5,6)
(WIP_CONSTANTS.DISC_CLASS,
WIP_CONSTANTS.NS_ASSET_CLASS,
WIP_CONSTANTS.LOT_CLASS,
WIP_CONSTANTS.EAM_CLASS ) ;
SELECT COUNT(*)
INTO l_expense_jobs
FROM WIP_DJ_CLOSE_TEMP TEMP,
WIP_DISCRETE_JOBS WDJ,
WIP_ACCOUNTING_CLASSES WAC
WHERE WDJ.WIP_ENTITY_ID = TEMP.WIP_ENTITY_ID
AND TEMP.ORGANIZATION_ID = p_organization_id
AND WDJ.ORGANIZATION_ID = p_organization_id
AND WAC.ORGANIZATION_ID = p_organization_id
AND WDJ.CLASS_CODE = WAC.CLASS_CODE
AND TEMP.GROUP_ID = p_group_id
AND WAC.CLASS_TYPE = WIP_CONSTANTS.NS_EXPENSE_CLASS ;
SELECT CHART_OF_ACCOUNTS_ID
INTO l_chart_of_accounts_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = p_organization_id ;
SELECT lgr.chart_of_accounts_id chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM hr_organization_information hoi,
gl_ledgers lgr
WHERE hoi.organization_id = p_organization_id
and hoi.org_information_context = 'Accounting Information'
and (ltrim(hoi.org_information1,'0123456789') is null
and hoi.org_information1 = lgr.ledger_id )
and lgr.object_type_code = 'L'
AND nvl(complete_flag, 'Y') = 'Y';
SELECT ACCT_PERIOD_ID,
to_char(PERIOD_START_DATE,'YYYY/MM/DD'),
to_char(SCHEDULE_CLOSE_DATE,'YYYY/MM/DD')
INTO l_acct_period_id ,
l_per_str_date,
l_per_cls_date
FROM ORG_ACCT_PERIODS
WHERE INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (SYSDATE, p_organization_id) >= TRUNC(PERIOD_START_DATE)
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (SYSDATE, p_organization_id) <= TRUNC(SCHEDULE_CLOSE_DATE)
AND ORGANIZATION_ID = p_organization_id;
SELECT COUNT(*)
INTO l_std_org_count
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id
AND PRIMARY_COST_METHOD = 1 ;
p_select_jobs IN NUMBER ,
p_exclude_reserved_jobs IN VARCHAR2 ,
p_uncompleted_jobs IN VARCHAR2,
p_exclude_pending_txn_jobs IN VARCHAR2 ,
p_report_type IN VARCHAR2 ,
p_act_close_date IN VARCHAR2 ,
x_warning OUT NOCOPY NUMBER ,
x_returnStatus OUT NOCOPY VARCHAR2
)
IS
l_group_id NUMBER ;
l_params(15).paramName := 'p_select_jobs';
l_params(15).paramValue := p_select_jobs;
SAVEPOINT wip_close; --Bug#14285578- Added a savepoint so that insertion into wip_dj_close_temp is not rolled back
IF ( p_select_jobs = l_at_submission_time) THEN
/***************************************************/
/* TIME ZONE CONVERSION */
/***************************************************/
TIME_ZONE_CONVERSIONS(
p_from_release_date => p_from_release_date ,
p_to_release_date => p_to_release_date ,
p_from_start_date => p_from_start_date ,
p_to_start_date => p_to_start_date ,
p_from_completion_date => p_from_completion_date ,
p_to_completion_date => p_to_completion_date ,
p_act_close_date => p_act_close_date ,
x_from_release_date => l_from_release_date ,
x_to_release_date => l_to_release_date ,
x_from_start_date => l_from_start_date ,
x_to_start_date => l_to_start_date ,
x_from_completion_date => l_from_completion_date ,
x_to_completion_date => l_to_completion_date ,
x_act_close_date => l_act_close_date ,
x_returnstatus => l_return_status
);
SELECT ACCT_PERIOD_ID
INTO l_acct_period_id
FROM ORG_ACCT_PERIODS
WHERE ORGANIZATION_ID = p_organization_id
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (l_act_close_date,p_organization_id)
BETWEEN PERIOD_START_DATE AND SCHEDULE_CLOSE_DATE
AND PERIOD_CLOSE_DATE IS NULL;
wip_logger.log(p_msg => 'error during eam update workflow to pending close' || l_errMsg,
x_returnStatus => l_return_Status);
UPDATE wip_discrete_jobs
SET status_type = WIP_CONSTANTS.PEND_CLOSE ,
request_id = fnd_global.conc_request_id ,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id
WHERE organization_id = p_organization_id
AND wip_entity_id in (SELECT wip_entity_id
FROM wip_dj_close_temp
WHERE group_id = l_group_id
AND organization_id = p_organization_id);
EAM_WorkOrderTransactions_PUB.Update_EWOD(
p_group_id => l_group_id,
p_organization_id => p_organization_id,
p_new_status => WIP_CONSTANTS.PEND_CLOSE,
ERRBUF => l_errMsg,
RETCODE => l_return_status
);
wip_logger.log(p_msg => 'eam update workoder error during pending close ' || l_errMsg,
x_returnStatus => l_return_Status);
SAVEPOINT wip_close; --Bug#13639508- Added a savepoint so that insertion into wip_dj_close_temp is not rolled back
SELECT COUNT(*)
INTO l_jobs_to_close
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = l_group_id
AND ROWNUM = 1;
IF ( p_select_jobs = l_at_submission_time) THEN
CHECK_OPEN_PO(
x_returnstatus => l_return_status,
p_organization_id => p_organization_id,
p_group_id => l_group_id);
* Cover routine for the inventory API delete_reservation. *
* *
**********************************************************/
DELETE_RESERVATIONS(
x_returnstatus => l_return_status,
p_organization_id => p_organization_id,
p_group_id => l_group_id );
wip_logger.log(p_msg => 'DELETE_RESERVATIONS procedure failed',
x_returnStatus => l_return_Status);
SELECT ACCT_PERIOD_ID
INTO l_acct_period_id
FROM ORG_ACCT_PERIODS
WHERE TRUNC(SYSDATE) >= TRUNC(PERIOD_START_DATE)
AND TRUNC(SYSDATE) <= TRUNC(SCHEDULE_CLOSE_DATE)
AND ORGANIZATION_ID = p_organization_id;
SELECT MAX(ACTUAL_CLOSE_DATE)
INTO l_acct_period_close_date
FROM WIP_DJ_CLOSE_TEMP
WHERE GROUP_ID = l_group_id;
SELECT ACCT_PERIOD_ID
INTO l_acct_period_id
FROM ORG_ACCT_PERIODS oap
WHERE
oap.ORGANIZATION_ID = p_organization_id
AND oap.PERIOD_CLOSE_DATE IS NULL
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(l_acct_period_close_date,to_date(p_act_close_date,'YYYY/MM/DD HH24:MI:SS')),p_organization_id)
BETWEEN oap.PERIOD_START_DATE and oap.SCHEDULE_CLOSE_DATE;
* Costing Function updates *
* *
******************************************/
SAVEPOINT wip_close; --Bug#13639508- Added a savepoint so that insertion into wip_dj_close_temp is not rolled back
SELECT WIP_TRANSACTIONS_S.nextval
INTO l_costing_group_id
FROM DUAL;
INSERT INTO WIP_COST_TXN_INTERFACE
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TRANSACTION_ID,
ACCT_PERIOD_ID,
GROUP_ID,
PROCESS_STATUS,
PROCESS_PHASE,
TRANSACTION_TYPE,
ORGANIZATION_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
TRANSACTION_DATE)
SELECT
SYSDATE,
fnd_global.user_id,
fnd_global.login_id ,
SYSDATE,
fnd_global.user_id,
fnd_global.conc_request_id ,
fnd_global.prog_appl_id,
fnd_global.conc_program_id ,
SYSDATE,
WIP_TRANSACTIONS_S.nextval,
oap.ACCT_PERIOD_ID,
l_costing_group_id,
2, -- PROCESS_STATUS
3, -- PROCESS_PHASE
6, -- TRANSACTION_TYPE
p_organization_id,
wdct.WIP_ENTITY_ID,
wdct.WIP_ENTITY_NAME,
we.ENTITY_TYPE,
wdct.ACTUAL_CLOSE_DATE
FROM WIP_DJ_CLOSE_TEMP wdct,
ORG_ACCT_PERIODS oap,
WIP_ENTITIES we
WHERE wdct.GROUP_ID = l_group_id
AND we.wip_entity_id = wdct.wip_entity_id
AND we.organization_id = p_organization_id
AND wdct.ORGANIZATION_ID = p_organization_id
AND oap.ORGANIZATION_ID = p_organization_id
AND oap.PERIOD_CLOSE_DATE IS NULL
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (wdct.ACTUAL_CLOSE_DATE, wdct.ORGANIZATION_ID)
BETWEEN oap.PERIOD_START_DATE and oap.SCHEDULE_CLOSE_DATE;
| CALL COSTING function to update variances |
|===============================================================*/
CST_JobCloseVar_GRP.Calculate_Job_Variance
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_user_id => fnd_global.user_id,
p_login_id => fnd_global.login_id,
p_prg_appl_id => fnd_global.prog_appl_id,
p_prg_id => fnd_global.conc_program_id,
p_req_id => fnd_global.conc_request_id,
p_wcti_group_id => l_costing_group_id,
p_org_id => p_organization_id
);
/*Bug 6908428: updating the status of eam workorders in eam_work_order_details to closed and workflow update*/
EAM_WorkOrderTransactions_PUB.RAISE_WORKFLOW_STATUS_PEND_CLS(
p_group_id => l_group_id,
p_new_status => WIP_CONSTANTS.CLOSED,
ERRBUF => l_errMsg,
RETCODE => l_return_Status
);
wip_logger.log(p_msg => 'error during eam update workflow to closed' || l_errMsg,
x_returnStatus => l_return_Status);
EAM_WorkOrderTransactions_PUB.Update_EWOD(
p_group_id => l_group_id,
p_organization_id => p_organization_id,
p_new_status => WIP_CONSTANTS.CLOSED,
ERRBUF => l_errMsg,
RETCODE => l_return_status
);
wip_logger.log(p_msg => 'eam update workoder error while job close' || l_errMsg,
x_returnStatus => l_return_Status);
UPDATE WIP_DISCRETE_JOBS wdj
SET DATE_CLOSED = (SELECT wdct.ACTUAL_CLOSE_DATE
FROM WIP_DJ_CLOSE_TEMP wdct
WHERE wdct.ORGANIZATION_ID = p_organization_id
AND wdj.ORGANIZATION_ID = p_organization_id
AND wdj.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
AND wdct.GROUP_ID = l_group_id),
LAST_UPDATE_DATE = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
STATUS_TYPE = WIP_CONSTANTS.CLOSED
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND GROUP_ID = l_group_id);
UPDATE WIP_ENTITIES
SET ENTITY_TYPE = --DECODE(entity_type,6,7,5,8,3),
DECODE(entity_type,
WIP_CONSTANTS.EAM,
WIP_CONSTANTS.CLOSED_EAM,
WIP_CONSTANTS.LOTBASED ,
WIP_CONSTANTS.CLOSED_OSFM ,
WIP_CONSTANTS.CLOSED_DISC),
LAST_UPDATE_DATE = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID IN (SELECT wdct.WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP wdct
WHERE wdct.ORGANIZATION_ID = p_organization_id
AND wdct.GROUP_ID = l_group_id);
SELECT COUNT(*)
INTO l_num_close
FROM WIP_DJ_CLOSE_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND GROUP_ID = l_group_id;
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND GROUP_ID = l_group_id;
rollback TO wip_close;--Bug#13639508- Rollback to the savepoint so that insertion into wip_dj_close_temp is not rolled back
/* Update jobs to Failed Close status */
/*Bug 6908428: Update the status of eam_work_order_details to failed close and proceed workflow notification*/
EAM_WorkOrderTransactions_PUB.RAISE_WORKFLOW_STATUS_PEND_CLS(
p_group_id => l_group_id,
p_new_status => WIP_CONSTANTS.FAIL_CLOSE,
ERRBUF => l_errMsg,
RETCODE => l_return_Status
);
wip_logger.log(p_msg => 'error during eam update workflow to fail closed' || l_errMsg,
x_returnStatus => l_return_Status);
EAM_WorkOrderTransactions_PUB.Update_EWOD(
p_group_id => l_group_id,
p_organization_id => p_organization_id,
p_new_status => WIP_CONSTANTS.FAIL_CLOSE,
ERRBUF => l_errMsg,
RETCODE => l_return_status
);
wip_logger.log(p_msg => 'eam update workoder error during fail close' || l_errMsg,
x_returnStatus => l_return_Status);
UPDATE WIP_DISCRETE_JOBS wdj
SET LAST_UPDATE_DATE = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID
FROM WIP_DJ_CLOSE_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND GROUP_ID = l_group_id);
DELETE FROM WIP_DJ_CLOSE_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND GROUP_ID = l_group_id;
p_select_jobs IN NUMBER ,
p_exclude_reserved_jobs IN VARCHAR2 ,
p_uncompleted_jobs IN VARCHAR2,
p_exclude_pending_txn_jobs IN VARCHAR2 ,
p_report_type IN VARCHAR2 ,
p_act_close_date IN VARCHAR2
)
IS
l_returnstatus VARCHAR2(1) ;
p_select_jobs => p_select_jobs ,
p_exclude_reserved_jobs => p_exclude_reserved_jobs ,
p_uncompleted_jobs => p_uncompleted_jobs ,
p_exclude_pending_txn_jobs => p_exclude_pending_txn_jobs ,
p_report_type => p_report_type ,
p_act_close_date => p_act_close_date ,
x_warning => l_warning ,
x_returnStatus => l_returnstatus
);