[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE JTM_CON_REQUEST_DATA SET LAST_RUN_DATE = l_current_run_date
WHERE PRODUCT_CODE = 'CSL'
AND PACKAGE_NAME = 'CSL_MTL_ONHAND_QTY_ACC_PKG'
AND PROCEDURE_NAME = 'REFRESH_ONHAND_QTY';
/*** First UPDATE existing MV records that changed ***/
/*** Fetch all records in a Bulk recordset ***/
SELECT ohq.subinventory_code
, ohq.inventory_item_id
, ohq.organization_id
, ohq.revision
, ohq.locator_id
, ohq.lot_number
, SUM(ohq.transaction_quantity)
BULK COLLECT INTO sub_code, inv_id, org_id,rvision, loc_id,lot_num, qty
FROM mtl_onhand_quantities ohq
WHERE (ohq.subinventory_code, ohq.organization_id) IN
( SELECT secinv.secondary_inventory_name
, secinv.organization_id
FROM jtm_csp_sec_inv_acc secacc
, csp_sec_inventories secinv
WHERE secacc.secondary_inventory_id = secinv.secondary_inventory_id
AND condition_type = 'G'
)
GROUP BY ohq.subinventory_code,
ohq.inventory_item_id,
ohq.organization_id,
ohq.revision,
ohq.locator_id,
ohq.lot_number
HAVING SUM(ohq.transaction_quantity) <>
(
SELECT tot_txn_quantity
FROM csl_mtl_onhand_qty_mv ohqmv
WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
AND ohqmv.organization_id = ohq.organization_id
AND ohqmv.inventory_item_id = ohq.inventory_item_id
AND ohqmv.subinventory_code = ohq.subinventory_code
);
, v_message => 'Number of updated records for REFRESH_ONHAND_QTY : '
|| sub_code.COUNT
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
UPDATE CSL_MTL_ONHAND_QTY_MV SET LAST_UPDATE_DATE = l_current_run_date, TOT_TXN_QUANTITY = qty(i)
WHERE subinventory_code = sub_code(i)
AND inventory_item_id = inv_id(i)
AND organization_id = org_id(i)
AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))
AND (LOCATOR_ID IS NULL OR LOCATOR_ID = loc_id(i))
AND (REVISION IS NULL OR revision = rvision(i));
/*** INSERT all newly created records for existing mobile users ***/
INSERT INTO CSL_MTL_ONHAND_QTY_MV (subinventory_code
, inventory_item_id, organization_id, revision, locator_id
, lot_number, last_update_date, tot_txn_quantity) (
SELECT ohq.subinventory_code, ohq.inventory_item_id
, ohq.organization_id, ohq.revision, ohq.locator_id
, ohq.lot_number, l_current_run_date
, SUM(ohq.transaction_quantity) tot_txn_quantity
FROM mtl_onhand_quantities ohq
WHERE (subinventory_code, organization_id) IN
( SELECT csi.secondary_inventory_name
, csi.organization_id
FROM jtm_csp_sec_inv_acc secacc
, csp_sec_inventories csi
WHERE csi.SECONDARY_INVENTORY_ID = secacc.SECONDARY_INVENTORY_ID
AND csi.CONDITION_TYPE = 'G'
)
AND NOT EXISTS ( SELECT NULL
FROM CSL_MTL_ONHAND_QTY_MV ohqmv
WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
AND ohqmv.ORGANIZATION_ID = ohq.ORGANIZATION_ID
AND ohqmv.INVENTORY_ITEM_ID = ohq.INVENTORY_ITEM_ID
AND ohqmv.SUBINVENTORY_CODE = ohq.SUBINVENTORY_CODE
)
GROUP BY ohq.subinventory_code
, ohq.inventory_item_id
, ohq.organization_id
, ohq.revision
, ohq.locator_id
, ohq.lot_number);
/*** DELETE all records for inventories that are no longer replicated ***/
DELETE FROM CSL_MTL_ONHAND_QTY_MV
WHERE (subinventory_code, organization_id) not in (
SELECT csi.secondary_inventory_name
, csi.organization_id
FROM jtm_csp_sec_inv_acc secacc
, csp_sec_inventories csi
WHERE secacc.secondary_inventory_id = csi.secondary_inventory_id
AND csi.condition_type = 'G'
);
/*** DELETE all records that are no longer present in inventory ohq table ***/
DELETE CSL_MTL_ONHAND_QTY_MV oqv
WHERE (subinventory_code
, inventory_item_id, organization_id, revision, locator_id
, lot_number) NOT IN (
SELECT subinventory_code, inventory_item_id
, organization_id, revision, locator_id
, lot_number
FROM mtl_onhand_quantities
WHERE (subinventory_code, organization_id) IN (
SELECT csi.secondary_inventory_name
, csi.organization_id
FROM jtm_csp_sec_inv_acc secacc
, csp_sec_inventories csi
WHERE csi.SECONDARY_INVENTORY_ID = secacc.SECONDARY_INVENTORY_ID
AND csi.CONDITION_TYPE = 'G'
)
);
SELECT DISTINCT resource_id
FROM JTM_CSP_INV_LOC_ASS_ACC);
SELECT COMMS_NL_TRACKABLE_FLAG
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = b_inventory_item_id
AND ORGANIZATION_ID = b_organization_id;
/*** First retrieve access_id of updated records and push them ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => TO_CHAR(l_current_run_date,'MM-DD-YYYY HH24:MI:SS')
, v_object_name => g_table_name
, v_message => 'Checking updates and inserts for resource_id = ' || r_secinv_resources.resource_id
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
/*** Push updated record to client ***/
SELECT ohqacc.access_id
BULK COLLECT INTO acc_id
FROM csl_mtl_onhand_qty_mv ohqmv,
csl_mtl_onhand_qty_acc ohqacc
WHERE ((ohqmv.lot_number IS NULL AND ohqacc.lot_number IS NULL) OR (ohqmv.lot_number = ohqacc.lot_number))
AND ((ohqmv.locator_id IS NULL AND ohqacc.locator_id IS NULL) OR (ohqmv.locator_id = ohqacc.locator_id))
AND ((ohqmv.revision IS NULL AND ohqacc.revision IS NULL) OR (ohqmv.revision = ohqacc.revision))
AND ohqmv.organization_id = ohqacc.organization_id
AND ohqmv.inventory_item_id = ohqacc.inventory_item_id
AND ohqmv.subinventory_code = ohqacc.subinventory_code
AND ohqacc.resource_id = r_secinv_resources.resource_id
AND ohqmv.last_update_date = l_current_run_date;
, v_message => 'Pushing ' || acc_id.COUNT || ' updated record(s)'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
/*** Push inserted records to client ***/
acc_id.DELETE;
inv_id.DELETE;
org_id.DELETE;
sub_code.DELETE;
rvsion.DELETE;
loc_id.DELETE;
lot_num.DELETE;
acc_id.DELETE;
track_flag.DELETE;
SELECT ohqmv.INVENTORY_ITEM_ID
, ohqmv.ORGANIZATION_ID
, ohqmv.SUBINVENTORY_CODE
, ohqmv.REVISION,ohqmv.LOCATOR_ID
, ohqmv.LOT_NUMBER
, JTM_ACC_TABLE_S.NEXTVAL ACCESS_ID
, msi.COMMS_NL_TRACKABLE_FLAG
BULK COLLECT INTO inv_id, org_id, sub_code, rvsion, loc_id,lot_num, acc_id,track_flag
FROM csl_mtl_onhand_qty_mv ohqmv
, mtl_system_items msi
WHERE msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
AND (ohqmv.subinventory_code, ohqmv.organization_id) IN
( SELECT ila.subinventory_code, ila.organization_id
FROM csp_inv_loc_assignments ila
, jtm_csp_inv_loc_ass_acc ilaacc
WHERE ilaacc.resource_id = r_secinv_resources.resource_id
AND ilaacc.csp_inv_loc_assignment_id = ila.csp_inv_loc_assignment_id
AND SYSDATE BETWEEN NVL(ila.effective_date_start, SYSDATE)
AND NVL(ila.effective_date_end, SYSDATE)
)
AND NOT EXISTS (
SELECT NULL
FROM csl_mtl_onhand_qty_acc ohqacc
WHERE ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
AND ohqacc.organization_id = ohqmv.organization_id
AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
AND ohqacc.subinventory_code = ohqmv.subinventory_code
AND ohqacc.resource_id = r_secinv_resources.resource_id
);
, v_message => 'Pushing ' || acc_id.COUNT || ' inserted record(s)'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
INSERT INTO CSL_MTL_ONHAND_QTY_ACC (RESOURCE_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,
SUBINVENTORY_CODE,REVISION,LOCATOR_ID,LOT_NUMBER,ACCESS_ID,COUNTER,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY) VALUES (r_secinv_resources.resource_id,inv_id(i), org_id(i), sub_code(i),
rvsion(i), loc_id(i), lot_num(i), acc_id(i),1,SYSDATE,1,SYSDATE,1);
, v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.PRE_INSERT_CHILD(inv_id(i),org_id(i),r_secinv_resources.resource_id);
/*** Push deleted records to client ***/
acc_id.DELETE;
track_flag.DELETE;
inv_id.DELETE;
org_id.DELETE;
sub_code.DELETE;
SELECT ohqacc.ACCESS_ID
, ohqacc.inventory_item_id
, ohqacc.organization_id
, ohqacc.subinventory_code
, msi.COMMS_NL_TRACKABLE_FLAG
BULK COLLECT INTO acc_id,inv_id, org_id, sub_code, track_flag
FROM csl_mtl_onhand_qty_acc ohqacc
, mtl_system_items msi
WHERE msi.INVENTORY_ITEM_ID = ohqacc.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = ohqacc.ORGANIZATION_ID
AND ohqacc.resource_id = r_secinv_resources.resource_id
AND NOT EXISTS (
SELECT null
FROM csl_mtl_onhand_qty_mv ohqmv
WHERE ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
AND ohqacc.organization_id = ohqmv.organization_id
AND ohqacc.inventory_item_id = ohqmv.inventory_item_id
AND ohqacc.subinventory_code = ohqmv.subinventory_code
);
, v_message => 'Pushing ' || acc_id.COUNT || ' deleted record(s)'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
, v_message => 'Calling CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
CSL_MTL_SYSTEM_ITEMS_ACC_PKG.POST_DELETE_CHILD( inv_id(i)
, org_id(i)
, r_secinv_resources.resource_id);
DELETE CSL_MTL_ONHAND_QTY_ACC
WHERE ACCESS_ID = acc_id(i);
/** Call out to item instances program to insert the OHQ instances **/
CSL_CSI_ITEM_INSTANCES_ACC_PKG.CONC_ITEM_INSTANCES(l_current_run_date);
/*Delete all records for non-existing user ( e.g user was deleted )*/
PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2 )
IS
l_tab_access_id dbms_sql.Number_Table;
, 'Entering DELETE_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, v_module => 'csl_mtl_onhand_qty_acc_pkg'
);
/* DELETE CSL_MTL_ONHAND_QTY_ACC
WHERE RESOURCE_ID = p_resource_id
RETURNING ACCESS_ID BULK COLLECT INTO l_tab_access_id;*/
SELECT ACCESS_ID
BULK COLLECT INTO l_tab_access_id
FROM CSL_MTL_ONHAND_QTY_ACC
WHERE RESOURCE_ID = p_resource_id;
DELETE CSL_MTL_ONHAND_QTY_ACC
WHERE ACCESS_ID = l_tab_access_id(i);
, 'Leaving DELETE_ALL_ACC_RECORDS'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, v_module => 'csl_mtl_onhand_qty_acc_pkg'
);
, v_message => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, v_module => 'csl_mtl_onhand_qty_acc_pkg');
END DELETE_ALL_ACC_RECORDS;