[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 ;
INSERT INTO WIP_DJ_CLOSE_TEMP
( wip_entity_id ,
organization_id ,
wip_entity_name ,
primary_item_id ,
status_type ,
group_id ,
actual_close_date )
SELECT DJ.WIP_ENTITY_ID,
DJ.ORGANIZATION_ID,
WE.WIP_ENTITY_NAME,
DJ.PRIMARY_ITEM_ID,
DJ.STATUS_TYPE,
x_group_id,
p_act_close_date
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_to_start_date )
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 )
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 )
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_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))))))
GROUP BY DJ.WIP_ENTITY_ID, DJ.ORGANIZATION_ID, WE.WIP_ENTITY_NAME,
DJ.PRIMARY_ITEM_ID, DJ.STATUS_TYPE ;
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
)
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
) ;
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;
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 ;
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);
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;
* Costing Function updates *
* *
******************************************/
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;
/* 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
);