[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
294: G_LOG_PREFIX||l_api_name||': Within the API',
295: 'Immediately before inserting record');
296: END IF;
297: --Do inserts
298: INSERT INTO ahl_prd_disp_mtl_txns (
299: DISP_MTL_TXN_ID,
300: OBJECT_VERSION_NUMBER,
301: LAST_UPDATE_DATE,
302: LAST_UPDATED_BY,
323: ATTRIBUTE13,
324: ATTRIBUTE14,
325: ATTRIBUTE15
326: ) VALUES (
327: AHL_PRD_DISP_MTL_TXNS_S.nextval,
328: 1,
329: sysdate,
330: fnd_global.user_id,
331: sysdate,
470: END IF;
471: END IF; -- p_module flag: JSP
472:
473: --UPDATE existing record
474: UPDATE ahl_prd_disp_mtl_txns SET
475: OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
476: LAST_UPDATE_DATE = sysdate,
477: LAST_UPDATED_BY = fnd_global.USER_ID,
478: LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
714: 0, --default is 0. will populate this field inside the loop
715: disp.uom assoc_uom,
716: disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
717: disp.uom
718: FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
719: WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
720: AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
721: OR (disp.trackable_flag = p_trackable_flag
722: AND disp.workorder_id = p_workorder_id
749: disp.uom assoc_uom,
750: disp.quantity-calculate_txned_qty(disp.disposition_id),
751: disp.uom
752: FROM ahl_prd_dispositions_vl disp,
753: ahl_prd_disp_mtl_txns assoc,
754: mtl_system_items_kfv mtl,
755: ahl_item_groups_b grp,
756: fnd_lookup_values flvt1,
757: fnd_lookup_values flvt2,
811: 0,
812: disp.uom assoc_uom,
813: disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
814: disp.uom
815: FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
816: WHERE (disp.disposition_id = assoc.disposition_id --Either match on existing reln
817: AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
818: OR (disp.trackable_flag = p_trackable_flag
819: AND disp.workorder_id = p_workorder_id
843: disp.uom assoc_uom,
844: disp.quantity-calculate_txned_qty(disp.disposition_id),
845: disp.uom
846: FROM ahl_prd_dispositions_vl disp,
847: ahl_prd_disp_mtl_txns assoc,
848: mtl_system_items_kfv mtl,
849: ahl_item_groups_b grp,
850: fnd_lookup_values flvt1,
851: fnd_lookup_values flvt2,
883: --added by peter
884: -- get the total associated quantity based on the disposition id and material transaction id
885: CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
886: IS
887: SELECT quantity, uom FROM AHL_PRD_DISP_MTL_TXNS
888: WHERE DISPOSITION_ID = p_disp_id -- 11016 -- 11044
889: AND WORKORDER_MTL_TXN_ID = p_workorder_txn_id;
890:
891: --Added by Jerry for fixing bug 4092624
1066: --
1067: CURSOR get_remain_qty_csr(p_disp_id IN NUMBER)
1068: IS
1069: SELECT sum (assoc.quantity)
1070: FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1071: AHL_WORKORDER_MTL_TXNS mtxn
1072: WHERE disp.disposition_id = p_disp_id
1073: AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
1074: AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
1106: --
1107: CURSOR get_assoc_qty_csr(p_disp_id IN NUMBER, p_workorder_txn_id IN NUMBER)
1108: IS
1109: SELECT sum (assoc.quantity)
1110: FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
1111: AHL_WORKORDER_MTL_TXNS mtxn
1112: WHERE disp.disposition_id = p_disp_id
1113: AND assoc.workorder_mtl_txn_id = p_workorder_txn_id
1114: AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id