[Home] [Help]
61: --
62: CURSOR get_disp_mtl_txn_rec_csr (p_disposition_id IN NUMBER,
63: p_mtl_txn_id IN NUMBER) IS
64: SELECT *
65: FROM AHL_PRD_DISP_MTL_TXNS
66: WHERE DISPOSITION_ID = p_disposition_id
67: AND WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
68: --
69: CURSOR get_disp_mtx_qty_csr (p_mtl_txn_id IN NUMBER) IS
67: AND WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
68: --
69: CURSOR get_disp_mtx_qty_csr (p_mtl_txn_id IN NUMBER) IS
70: SELECT quantity, uom
71: FROM AHL_PRD_DISP_MTL_TXNS
72: WHERE WORKORDER_MTL_TXN_ID = p_mtl_txn_id;
73: --
74: CURSOR get_mtl_txn_qty_csr (p_mtl_txn_id IN NUMBER) IS
75: SELECT inventory_item_id, quantity, uom
297: G_LOG_PREFIX||l_api_name||': Within the API',
298: 'Immediately before inserting record');
299: END IF;
300: --Do inserts
301: INSERT INTO ahl_prd_disp_mtl_txns (
302: DISP_MTL_TXN_ID,
303: OBJECT_VERSION_NUMBER,
304: LAST_UPDATE_DATE,
305: LAST_UPDATED_BY,
326: ATTRIBUTE13,
327: ATTRIBUTE14,
328: ATTRIBUTE15
329: ) VALUES (
330: AHL_PRD_DISP_MTL_TXNS_S.nextval,
331: 1,
332: sysdate,
333: fnd_global.user_id,
334: sysdate,
473: END IF;
474: END IF; -- p_module flag: JSP
475:
476: --UPDATE existing record
477: UPDATE ahl_prd_disp_mtl_txns SET
478: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
479: LAST_UPDATE_DATE = sysdate,
480: LAST_UPDATED_BY = fnd_global.USER_ID,
481: LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
726: 0, --default is 0. will populate this field inside the loop
727: disp.uom assoc_uom,
728: disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
729: disp.uom
730: FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
731: WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
732: AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
733: OR (disp.trackable_flag = p_trackable_flag
734: AND disp.workorder_id = p_workorder_id
761: disp.uom assoc_uom,
762: disp.quantity-calculate_txned_qty(disp.disposition_id),
763: disp.uom
764: FROM ahl_prd_dispositions_vl disp,
765: ahl_prd_disp_mtl_txns assoc,
766: mtl_system_items_kfv mtl,
767: ahl_item_groups_b grp,
768: fnd_lookup_values flvt1,
769: fnd_lookup_values flvt2,
823: 0,
824: disp.uom assoc_uom,
825: disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
826: disp.uom
827: FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
828: WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
829: AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
830: OR (disp.trackable_flag = p_trackable_flag
831: AND disp.workorder_id = p_workorder_id
855: disp.uom assoc_uom,
856: disp.quantity-calculate_txned_qty(disp.disposition_id),
857: disp.uom
858: FROM ahl_prd_dispositions_vl disp,
859: ahl_prd_disp_mtl_txns assoc,
860: mtl_system_items_kfv mtl,
861: ahl_item_groups_b grp,
862: fnd_lookup_values flvt1,
863: fnd_lookup_values flvt2,
895: --added by peter
896: -- get the total associated quantity based on the disposition id and material transaction id
897: CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
898: IS
899: SELECT quantity, uom FROM AHL_PRD_DISP_MTL_TXNS
900: WHERE DISPOSITION_ID = p_disp_id -- 11016 -- 11044
901: AND WORKORDER_MTL_TXN_ID = p_workorder_txn_id;
902:
903: --Added by Jerry for fixing bug 4092624
1078: --
1079: CURSOR get_remain_qty_csr(p_disp_id IN NUMBER)
1080: IS
1081: SELECT sum (assoc.quantity)
1082: FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1083: AHL_WORKORDER_MTL_TXNS mtxn
1084: WHERE disp.disposition_id = p_disp_id
1085: AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
1086: AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
1118: --
1119: CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
1120: IS
1121: SELECT sum (assoc.quantity)
1122: FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1123: AHL_WORKORDER_MTL_TXNS mtxn
1124: WHERE disp.disposition_id = p_disp_id
1125: AND assoc.workorder_mtl_txn_id = p_workorder_txn_id
1126: AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id