[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 1. Update records in mtl_demand that meet criteria with a group_id |
| 2. Insert records into wip_entities_interface for mtl_demands records |
| marked with group_id |
| 3. Read wip_entities_interface records and inform OE of sales order |
| lines that have been linked to WIP |
| 4. Call mass load routine to create jobs from wip_entities_interface |
| records |
| 5. Do feedback: |
| 1. Update mtl_demand for jobs successfully loaded |
| 2. Create records in wip_so_allocations |
| 3. Read wip_entities_interface and inform OE of sales order |
| lines that should be unlinked from WIP |
| 4. Update mtl_demand for jobs that failed load so they can be |
| picked up again |
| 6. Launch report of what occurred in process |
| 7. Delete records from interface table |
| |
| CALLED BY: Concurrent Program |
| |
|
| Date Fixed by FIX
| 06-Feb-2006 Kiran Konada bugfix#4865485
| When no orders are loaded AFAS will
| complete with warning
+===========================================================================*/
--Global Vars
G_PKG_NAME CONSTANT VARCHAR2(30):='WIP_ATO_JOBS_PRIV';
select userenv('SESSIONID') into l_audsid from dual;
| records that need to be loaded and inserts records into |
| wip_job_schedule_interface. Just perform the feedback loop. |
+===============================================================+*/
if (L_FAILED_REQ_ID = -1) then
if (l_logLevel <= wip_constants.full_logging) then
wip_logger.log('Dbg: OM Installed: Enter get_order_lines.', l_returnStatus);
select 1
into l_line_count
from dual
where exists
( select source_line_id
from wip_job_schedule_interface
where group_id = l_wip_group_id
and process_phase = WIP_CONSTANTS.ML_COMPLETE
and process_status in (WIP_CONSTANTS.COMPLETED,WIP_CONSTANTS.WARNING)
);
| - inserting records into WIP_SO_ALLOCATIONS |
| - informing OE to unlink any sales order whose job failed |
| to load |
| - delete records from the interface table |
| - setting the supply_group_id in MTL_DEMAND back to null |
| |
| Feedback is executed regardless of failed_request_id value. |
| |
| If failed_request_id <> 0, the program is being run in |
| 'cleanup mode' -- see comment earlier in program for details |
| on what happens in 'cleanup' mode, If this is the case, |
| the group_ids used for feedback must be retrieved from |
| MTL_DEMAND and WIP_JOB_SCHEDULE_INTERFACE. |
+===============================================================+*/
if (L_FAILED_REQ_ID <> -1) THEN
/* OM Installed - No Clean-up Mode Code Yet */
if (l_logLevel <= wip_constants.full_logging) then
wip_logger.log(
'Dbg: OM Installed: No Clean-Up Mode', l_returnStatus);
SELECT COUNT(*) INTO num_error_records
FROM wip_job_schedule_interface
WHERE GROUP_ID = wjsi_group_id
AND (PROCESS_STATUS <> WCOMPLETED
OR PROCESS_PHASE <> WIP_ML_COMPLETE);
| defaulted param that is selected from the sequence as then the |
| conc program would always use the same group id value when |
| re-submitted. |
+--------------------------------------------------------------------+*/
precision_profile := fnd_profile.value('REPORT_QUANTITY_PRECISION');
if ( delete_interface_orders(p_wei_group_id => l_wip_group_id) = false )
THEN
if (l_logLevel <= wip_constants.full_logging) then
wip_logger.log(
'Dbg:Failed in delete_interface_orders', l_returnStatus);
Function delete_interface_orders(p_wei_group_id NUMBER)
return boolean
IS
wei_group_id NUMBER;
n_undeleted_records NUMBER ;
wip_logger.entryPoint(p_procName => 'WIP_ATO_JOBS_PRIV.DELETE_INTERFACE_ORDERS',
p_params => l_params,
x_returnStatus => x_return_status);
'Dbg: In delete_interface_orders function', l_returnStatus);
* When running in debug mode, we never delete from the errors table,
* and we delete only those interface records which have been successfully
* processed. When running in debug mode, we always delete all records
* for the current group from both tables -- we rely on the WIPDJATO
* report to have communicated any errors.
*
* This compromise between deleting everything and deleting only
* non-problem records while in debug mode hopefully represents the
* final fix for bug 775437 and its predecessors.
*/
--Bugfix 10636184: Controlling the delete by MRP profile instead of FND profile
--if (l_logLevel <= wip_constants.full_logging) then
IF (NOT DEBUG_FLAG) THEN
--Bugfix 10636184: If MRP: Debug is OFF, then remove only successfully completed records
--from the interface table.
/* bugfix 4289455 : Remove the records from WIE since we are deleting WJSI record
with process phase and process status COMPLETE else these may remain orphan */
DELETE FROM WIP_INTERFACE_ERRORS
WHERE INTERFACE_ID IN (
SELECT INTERFACE_ID
FROM WIP_JOB_SCHEDULE_INTERFACE
WHERE GROUP_ID = wei_group_id
AND PROCESS_PHASE = WIP_ML_COMPLETE
AND PROCESS_STATUS = WCOMPLETED);
fnd_file.put_line(which => fnd_file.log, buff => 'Deleted from wie:' || sql%rowcount);
DELETE FROM WIP_JOB_SCHEDULE_INTERFACE I
WHERE I.GROUP_ID = wei_group_id
AND I.PROCESS_PHASE = WIP_ML_COMPLETE
AND I.PROCESS_STATUS = WCOMPLETED;
fnd_file.put_line(which => fnd_file.log, buff => 'Deleted from wjsi:' || sql%rowcount);
AND 0 = (SELECT COUNT(*)
FROM WIP_INTERFACE_ERRORS E
WHERE E.INTERFACE_ID = I.INTERFACE_ID) ;
DELETE FROM WIP_INTERFACE_ERRORS
WHERE INTERFACE_ID IN
(SELECT INTERFACE_ID
FROM WIP_JOB_SCHEDULE_INTERFACE
WHERE GROUP_ID = wei_group_id);
DELETE FROM wip_job_schedule_interface wei
WHERE group_id = wei_group_id;
wip_logger.log('Dbg:'||SQL%ROWCOUNT||' records deleted from interface', l_returnStatus);
SELECT COUNT(*) INTO n_undeleted_records
FROM WIP_JOB_SCHEDULE_INTERFACE WHERE GROUP_ID = wei_group_id ;
if(n_undeleted_records <> 0) THEN
wip_logger.log('Dbg:Note: '||n_undeleted_records||' error/unprocessed records remain', l_returnStatus);
'Dbg:Success in delete_interface_orders', l_returnStatus);
wip_logger.exitPoint(p_procName => 'WIP_ATO_JOBS_PRIV.DELETE_INTERFACE_ORDERS',
p_procReturnStatus => l_returnStatus,
p_msg => 'PROCEDURE COMPLETE.',
x_returnStatus => l_returnStatus);
wip_logger.log( 'Dbg:SQL error in delete_interface_orders', l_returnStatus);
/* end of delete_interface_orders */
END delete_interface_orders;
| End DELETE_INTERFACE_ORDERS |
+-------------------------------------------------------------------*/
END WIP_ATO_JOBS_PRIV;