APPS.CSL_MTL_ONHAND_QTY_ACC_PKG dependencies on CSL_MTL_ONHAND_QTY_MV

Line 85: FROM csl_mtl_onhand_qty_mv ohqmv

81: ohq.lot_number
82: HAVING SUM(ohq.transaction_quantity) <>
83: (
84: SELECT tot_txn_quantity
85: FROM csl_mtl_onhand_qty_mv ohqmv
86: WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
87: AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
88: AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
89: AND ohqmv.organization_id = ohq.organization_id

Line 106: UPDATE CSL_MTL_ONHAND_QTY_MV SET LAST_UPDATE_DATE = l_current_run_date, TOT_TXN_QUANTITY = qty(i)

102: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
103: , v_module => 'csl_mtl_onhand_qty_acc_pkg');
104: END IF;
105: FORALL i IN sub_code.FIRST..sub_code.LAST
106: UPDATE CSL_MTL_ONHAND_QTY_MV SET LAST_UPDATE_DATE = l_current_run_date, TOT_TXN_QUANTITY = qty(i)
107: WHERE subinventory_code = sub_code(i)
108: AND inventory_item_id = inv_id(i)
109: AND organization_id = org_id(i)
110: AND (LOT_NUMBER IS NULL OR LOT_NUMBER = lot_num(i))

Line 116: INSERT INTO CSL_MTL_ONHAND_QTY_MV (subinventory_code

112: AND (REVISION IS NULL OR revision = rvision(i));
113: END IF;
114:
115: /*** INSERT all newly created records for existing mobile users ***/
116: INSERT INTO CSL_MTL_ONHAND_QTY_MV (subinventory_code
117: , inventory_item_id, organization_id, revision, locator_id
118: , lot_number, last_update_date, tot_txn_quantity) (
119: SELECT ohq.subinventory_code, ohq.inventory_item_id
120: , ohq.organization_id, ohq.revision, ohq.locator_id

Line 133: FROM CSL_MTL_ONHAND_QTY_MV ohqmv

129: WHERE csi.SECONDARY_INVENTORY_ID = secacc.SECONDARY_INVENTORY_ID
130: AND csi.CONDITION_TYPE = 'G'
131: )
132: AND NOT EXISTS ( SELECT NULL
133: FROM CSL_MTL_ONHAND_QTY_MV ohqmv
134: WHERE ((ohqmv.LOT_NUMBER IS NULL AND ohq.LOT_NUMBER IS NULL) OR (ohqmv.LOT_NUMBER = ohq.LOT_NUMBER))
135: AND ((ohqmv.LOCATOR_ID IS NULL AND ohq.LOCATOR_ID IS NULL) OR (ohqmv.LOCATOR_ID = ohq.LOCATOR_ID))
136: AND ((ohqmv.REVISION IS NULL AND ohq.REVISION IS NULL) OR (ohqmv.REVISION = ohq.REVISION))
137: AND ohqmv.ORGANIZATION_ID = ohq.ORGANIZATION_ID

Line 149: DELETE FROM CSL_MTL_ONHAND_QTY_MV

145: , ohq.locator_id
146: , ohq.lot_number);
147:
148: /*** DELETE all records for inventories that are no longer replicated ***/
149: DELETE FROM CSL_MTL_ONHAND_QTY_MV
150: WHERE (subinventory_code, organization_id) not in (
151: SELECT csi.secondary_inventory_name
152: , csi.organization_id
153: FROM jtm_csp_sec_inv_acc secacc

Line 160: DELETE CSL_MTL_ONHAND_QTY_MV oqv

156: AND csi.condition_type = 'G'
157: );
158:
159: /*** DELETE all records that are no longer present in inventory ohq table ***/
160: DELETE CSL_MTL_ONHAND_QTY_MV oqv
161: WHERE (subinventory_code
162: , inventory_item_id, organization_id, revision, locator_id
163: , lot_number) NOT IN (
164: SELECT subinventory_code, inventory_item_id

Line 275: FROM csl_mtl_onhand_qty_mv ohqmv,

271:
272: /*** Push updated record to client ***/
273: SELECT ohqacc.access_id
274: BULK COLLECT INTO acc_id
275: FROM csl_mtl_onhand_qty_mv ohqmv,
276: csl_mtl_onhand_qty_acc ohqacc
277: WHERE ((ohqmv.lot_number IS NULL AND ohqacc.lot_number IS NULL) OR (ohqmv.lot_number = ohqacc.lot_number))
278: AND ((ohqmv.locator_id IS NULL AND ohqacc.locator_id IS NULL) OR (ohqmv.locator_id = ohqacc.locator_id))
279: AND ((ohqmv.revision IS NULL AND ohqacc.revision IS NULL) OR (ohqmv.revision = ohqacc.revision))

Line 336: FROM csl_mtl_onhand_qty_mv ohqmv

332: , ohqmv.LOT_NUMBER
333: , JTM_ACC_TABLE_S.NEXTVAL ACCESS_ID
334: , msi.COMMS_NL_TRACKABLE_FLAG
335: BULK COLLECT INTO inv_id, org_id, sub_code, rvsion, loc_id,lot_num, acc_id,track_flag
336: FROM csl_mtl_onhand_qty_mv ohqmv
337: , mtl_system_items msi
338: WHERE msi.INVENTORY_ITEM_ID = ohqmv.INVENTORY_ITEM_ID
339: AND msi.ORGANIZATION_ID = ohqmv.ORGANIZATION_ID
340: AND (ohqmv.subinventory_code, ohqmv.organization_id) IN

Line 431: FROM csl_mtl_onhand_qty_mv ohqmv

427: AND msi.ORGANIZATION_ID = ohqacc.ORGANIZATION_ID
428: AND ohqacc.resource_id = r_secinv_resources.resource_id
429: AND NOT EXISTS (
430: SELECT null
431: FROM csl_mtl_onhand_qty_mv ohqmv
432: WHERE ((ohqacc.lot_number IS NULL AND ohqmv.lot_number IS NULL) OR (ohqacc.lot_number = ohqmv.lot_number))
433: AND ((ohqacc.locator_id IS NULL AND ohqmv.locator_id IS NULL) OR (ohqacc.locator_id = ohqmv.locator_id))
434: AND ((ohqacc.revision IS NULL AND ohqmv.revision IS NULL) OR (ohqacc.revision = ohqmv.revision))
435: AND ohqacc.organization_id = ohqmv.organization_id