APPS.AHL_PRD_DISP_MTL_TXN_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 37

SELECT disp.disposition_id, disp.object_version_number,
disp.inventory_item_id, disp.organization_id,
disp.immediate_disposition_code, disp.quantity, disp.UOM, disp.workorder_id
FROM AHL_PRD_DISPOSITIONS_B disp
WHERE disp.disposition_id = p_disposition_id;

Line: 46

SELECT 'x', fnd.meaning from ahl_workorders wo, fnd_lookup_values_vl fnd
WHERE workorder_id = p_workorder_id
  AND wo.status_code IN ('12','7','17','22','5')
  and fnd.lookup_type = 'AHL_JOB_STATUS' and fnd.lookup_code = wo.status_code;

Line: 56

SELECT mt.transaction_type_id, mt.inventory_item_id, mt.organization_id,
   mt.quantity, mt.uom,  wop.workorder_id
FROM AHL_WORKORDER_MTL_TXNS mt, AHL_WORKORDER_OPERATIONS wop
WHERE mt.WORKORDER_MTL_TXN_ID = p_mtl_txn_id
AND wop.workorder_operation_id = mt.workorder_operation_id;

Line: 64

SELECT *
FROM AHL_PRD_DISP_MTL_TXNS
WHERE DISPOSITION_ID = p_disposition_id
AND WORKORDER_MTL_TXN_ID = p_mtl_txn_id;

Line: 70

SELECT quantity, uom
FROM AHL_PRD_DISP_MTL_TXNS
WHERE WORKORDER_MTL_TXN_ID = p_mtl_txn_id;

Line: 75

SELECT inventory_item_id, quantity, uom
FROM AHL_WORKORDER_MTL_TXNS
WHERE WORKORDER_MTL_TXN_ID = p_mtl_txn_id;

Line: 80

SELECT disp.quantity
FROM AHL_PRD_DISPOSITIONS_B disp
WHERE disp.disposition_id = p_disposition_id;

Line: 96

l_update_disp_tbl DISP_ID_TBL_TYPE;    --table indexed by disposition id that store disposition object version number

Line: 97

l_disp_id_index NUMBER;   -- use as both index for l_update_disp_tbl and as disposition_id.

Line: 98

l_update_disp_rec AHL_PRD_DISPOSITION_PVT.disposition_rec_type;

Line: 164

       FND_MESSAGE.Set_Name('AHL','AHL_PRD_DIS_ASSOC_WO_STATUS');   --Cannot update disposition because of current workorder's status

Line: 174

    l_update_disp_tbl(l_disp_rec.disposition_id) := l_disp_rec.object_version_number;

Line: 298

			       'Immediately before inserting record');

Line: 301

    INSERT INTO ahl_prd_disp_mtl_txns (
        DISP_MTL_TXN_ID,
     	OBJECT_VERSION_NUMBER,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        DISPOSITION_ID,
        WORKORDER_MTL_TXN_ID,
        QUANTITY,
        UOM,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15
        ) VALUES (
        AHL_PRD_DISP_MTL_TXNS_S.nextval,
      	1,
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        fnd_global.login_id,
        l_disp_mx_rec.disposition_id,
        l_disp_mx_rec.wo_mtl_txn_id,
        l_disp_mx_rec.quantity,
        l_disp_mx_rec.uom,
       	l_disp_mx_rec.attribute_category ,
  	    l_disp_mx_rec.attribute1 ,
        l_disp_mx_rec.attribute2 ,
	    l_disp_mx_rec.attribute3 ,
	    l_disp_mx_rec.attribute4 ,
	   l_disp_mx_rec.attribute5 ,
	   l_disp_mx_rec.attribute6 ,
	   l_disp_mx_rec.attribute7 ,
	   l_disp_mx_rec.attribute8 ,
	   l_disp_mx_rec.attribute9 ,
	   l_disp_mx_rec.attribute10 ,
	   l_disp_mx_rec.attribute11 ,
	   l_disp_mx_rec.attribute12 ,
	   l_disp_mx_rec.attribute13 ,
	   l_disp_mx_rec.attribute14 ,
	   l_disp_mx_rec.attribute15
        )
        returning DISP_MTL_TXN_ID INTO p_x_disp_mtl_txn_tbl(i).disp_mtl_txn_id;

Line: 477

    UPDATE ahl_prd_disp_mtl_txns SET
	    OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
        LAST_UPDATE_DATE      = sysdate,
        LAST_UPDATED_BY       = fnd_global.USER_ID,
        LAST_UPDATE_LOGIN     = fnd_global.LOGIN_ID,
 	    QUANTITY             = l_disp_mx_rec.quantity,
 	    UOM                = l_disp_mx_rec.uom,
 	    ATTRIBUTE_CATEGORY = l_disp_mx_rec.ATTRIBUTE_CATEGORY,
        ATTRIBUTE1   = l_disp_mx_rec.ATTRIBUTE1,
        ATTRIBUTE2 = l_disp_mx_rec.ATTRIBUTE2,
        ATTRIBUTE3 = l_disp_mx_rec.ATTRIBUTE3,
        ATTRIBUTE4 = l_disp_mx_rec.ATTRIBUTE4,
        ATTRIBUTE5 = l_disp_mx_rec.ATTRIBUTE5,
        ATTRIBUTE6 = l_disp_mx_rec.ATTRIBUTE6,
        ATTRIBUTE7 = l_disp_mx_rec.ATTRIBUTE7,
        ATTRIBUTE8 = l_disp_mx_rec.ATTRIBUTE8,
        ATTRIBUTE9 = l_disp_mx_rec.ATTRIBUTE9,
        ATTRIBUTE10 = l_disp_mx_rec.ATTRIBUTE10,
        ATTRIBUTE11 = l_disp_mx_rec.ATTRIBUTE11,
        ATTRIBUTE12 = l_disp_mx_rec.ATTRIBUTE12,
        ATTRIBUTE13 = l_disp_mx_rec.ATTRIBUTE13,
        ATTRIBUTE14 = l_disp_mx_rec.ATTRIBUTE14,
        ATTRIBUTE15 = l_disp_mx_rec.ATTRIBUTE15
      WHERE DISP_MTL_TXN_ID =  l_disp_mx_rec.disp_mtl_txn_id;

Line: 569

    l_disp_id_index := l_update_disp_tbl.FIRST;

Line: 571

     l_update_disp_rec.disposition_id := l_disp_id_index;

Line: 572

     l_update_disp_rec.object_version_number :=  l_update_disp_tbl(l_disp_id_index);

Line: 573

     l_update_disp_rec.operation_flag := AHL_PRD_DISPOSITION_PVT.G_OP_UPDATE;

Line: 578

			       ' disposition_id' || l_update_disp_rec.disposition_id  ||
				   ' obj_ver_num: ' || l_update_disp_rec.object_version_number
				   ||' x_msg_data: ' || x_msg_data );

Line: 589

        p_x_disposition_rec    =>   l_update_disp_rec,
        -- Dummy parameter added by jaramana on Oct 11, 2007 for ER 5883257
        p_mr_asso_tbl          =>   l_mr_asso_tbl,
        x_return_status        =>   x_return_status,
        x_msg_count            =>   x_msg_count,
        x_msg_data             =>   x_msg_data);

Line: 597

      l_disp_id_index := l_update_disp_tbl.NEXT(l_disp_id_index);

Line: 600

      l_update_disp_rec := l_disp_rec_null;

Line: 689

 SELECT oper.workorder_id, txn.transaction_type_id,
        txn.inventory_item_id, txn.organization_id,
        txn.serial_number, txn.lot_number,
        NVL(mtl.comms_nl_trackable_flag,'N') trackable_flag
  FROM  AHL_WORKORDER_MTL_TXNS txn, AHL_WORKORDER_OPERATIONS oper,
        mtl_system_items_kfv mtl
  WHERE txn.workorder_mtl_txn_id = p_mtl_txn_id
    AND txn.workorder_operation_id = oper.workorder_operation_id
    AND txn.inventory_item_id = mtl.inventory_item_id
    AND txn.organization_id = mtl.organization_id;

Line: 710

SELECT distinct disp.disposition_id,
        disp.inventory_item_id,
        disp.organization_id,
        disp.item_number,
        disp.item_group_id,
        disp.item_group_name,
        disp.serial_number,
        disp.lot_number,
        disp.immediate_disposition_code,
        disp.immediate_type,
        disp.secondary_disposition_code,
        disp.secondary_type,
        disp.status_code,
        disp.status,
        disp.quantity,
        disp.uom,
        0, --default is 0.  will populate this field inside the loop
        disp.uom assoc_uom,
        disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
        disp.uom
  FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
 WHERE  (disp.disposition_id = assoc.disposition_id   --Either match on existing reln
         AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
     OR (disp.trackable_flag = p_trackable_flag
       AND disp.workorder_id = p_workorder_id
       AND (disp.status_code IS NULL OR disp.status_code <> 'TERMINATED')
       AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id) --Find untxned dispositions
       AND disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
       AND disp.inventory_item_id = p_txn_item_id
       AND disp.organization_id = p_txn_org_id
       AND (disp.serial_number IS NULL OR disp.serial_number = p_serial_number)
       AND (disp.lot_number IS NULL OR disp.lot_number = p_lot_number));

Line: 744

      SELECT   distinct disp.disposition_id,
               disp.inventory_item_id,
               disp.organization_id,
               mtl.concatenated_segments item_number,
               disp.item_group_id ,
               grp.name item_group_name ,
               disp.serial_number,
               disp.lot_number,
               disp.immediate_disposition_code,
               flvt1.meaning immediate_type,
               disp.secondary_disposition_code,
               flvt2.meaning secondary_type,
               disp.STATUS_CODE ,
               flvt3.MEANING STATUS ,
               disp.quantity,
               disp.uom,
               0,
               disp.uom assoc_uom,
               disp.quantity-calculate_txned_qty(disp.disposition_id),
               disp.uom
      FROM     ahl_prd_dispositions_vl disp,
               ahl_prd_disp_mtl_txns assoc,
               mtl_system_items_kfv mtl,
               ahl_item_groups_b grp,
               fnd_lookup_values flvt1,
               fnd_lookup_values flvt2,
               fnd_lookup_values flvt3
      WHERE    (
                  (   disp.disposition_id = assoc.disposition_id   --Either match on existing reln
                     AND
                     assoc.workorder_mtl_txn_id = p_mtl_txn_id
                  )
                  OR
                  (  decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') = p_trackable_flag
                     AND disp.workorder_id = p_workorder_id
                     AND (disp.status_code IS NULL OR disp.status_code <> 'TERMINATED')
                     AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id) --Find untxned dispositions
                     AND disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
                     AND disp.inventory_item_id = p_txn_item_id
                     AND disp.organization_id = p_txn_org_id
                     AND (disp.serial_number IS NULL OR disp.serial_number = p_serial_number)
                     AND (disp.lot_number IS NULL OR disp.lot_number = p_lot_number)
                  )
               )
      AND      disp.inventory_item_id = mtl.inventory_item_id (+)
      AND      disp.organization_id = mtl.organization_id (+)
      AND      disp.ITEM_GROUP_ID = grp.item_group_id (+)
      AND      flvt1.lookup_type(+) = 'AHL_IMMED_DISP_TYPE'
      AND      flvt1.LOOKUP_CODE (+) = disp.immediate_disposition_code
      AND      flvt1.LANGUAGE(+) = userenv('LANG')
      AND      flvt2.lookup_type(+) = 'AHL_SECND_DISP_TYPE'
      AND      flvt2.lookup_code (+) = disp.secondary_disposition_code
      AND      flvt2.LANGUAGE(+) = userenv('LANG')
      AND      flvt3.lookup_type(+) = 'AHL_DISP_STATUS'
      AND      flvt3.lookup_code (+) = disp.STATUS_CODE
      AND      flvt3.LANGUAGE(+) = userenv('LANG') ;

Line: 807

SELECT distinct disp.disposition_id,
        disp.inventory_item_id,
        disp.organization_id,
        disp.item_number,
        disp.item_group_id,
        disp.item_group_name,
        disp.serial_number,
        disp.lot_number,
        disp.immediate_disposition_code,
        disp.immediate_type,
        disp.secondary_disposition_code,
        disp.secondary_type,
        disp.status_code,
        disp.status,
        disp.quantity,
		disp.uom,
        0,
        disp.uom assoc_uom,
        disp.quantity-Calculate_Txned_Qty(disp.disposition_id),
        disp.uom
  FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PRD_DISP_MTL_TXNS assoc
 WHERE (disp.disposition_id = assoc.disposition_id   --Either match on existing reln
       AND assoc.workorder_mtl_txn_id = p_mtl_txn_id)
     OR (disp.trackable_flag = p_trackable_flag
       AND disp.workorder_id = p_workorder_id
       AND disp.status_code <> 'TERMINATED'
       AND (disp.immediate_disposition_code NOT IN ('NOT_RECEIVED','NA','NOT_REMOVED')
          OR (disp.immediate_disposition_code = 'NOT_RECEIVED'
             AND disp.quantity > Calculate_Txned_Qty(disp.disposition_id))));

Line: 838

      SELECT   distinct disp.disposition_id,
               disp.inventory_item_id,
               disp.organization_id,
               mtl.concatenated_segments item_number,
               disp.item_group_id ,
               grp.name item_group_name ,
               disp.serial_number,
               disp.lot_number,
               disp.immediate_disposition_code,
               flvt1.meaning immediate_type,
               disp.secondary_disposition_code,
               flvt2.meaning secondary_type,
               disp.STATUS_CODE ,
               flvt3.MEANING STATUS ,
               disp.quantity,
               disp.uom,
               0,
               disp.uom assoc_uom,
               disp.quantity-calculate_txned_qty(disp.disposition_id),
               disp.uom
      FROM     ahl_prd_dispositions_vl disp,
               ahl_prd_disp_mtl_txns assoc,
               mtl_system_items_kfv mtl,
               ahl_item_groups_b grp,
               fnd_lookup_values flvt1,
               fnd_lookup_values flvt2,
               fnd_lookup_values flvt3
      WHERE    (
                  (  disp.disposition_id = assoc.disposition_id
                     and assoc.workorder_mtl_txn_id = p_mtl_txn_id
                  )
                  or
                  (  decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') = p_trackable_flag
                     and disp.workorder_id = p_workorder_id
                     and disp.status_code <> 'TERMINATED'
                     and ( disp.immediate_disposition_code not in ('NOT_RECEIVED','NA','NOT_REMOVED')
                           or
                          (   disp.immediate_disposition_code = 'NOT_RECEIVED'
                              and
                              disp.quantity > calculate_txned_qty(disp.disposition_id)
                           )
                        )
                  )
               )
      AND      disp.inventory_item_id = mtl.inventory_item_id (+)
      AND      disp.organization_id = mtl.organization_id (+)
      AND      disp.ITEM_GROUP_ID = grp.item_group_id (+)
      AND      flvt1.lookup_type(+) = 'AHL_IMMED_DISP_TYPE'
      AND      flvt1.LOOKUP_CODE (+) = disp.immediate_disposition_code
      AND      flvt1.LANGUAGE(+) = userenv('LANG')
      AND      flvt2.lookup_type(+) = 'AHL_SECND_DISP_TYPE'
      AND      flvt2.lookup_code (+) = disp.secondary_disposition_code
      AND      flvt2.LANGUAGE(+) = userenv('LANG')
      AND      flvt3.lookup_type(+) = 'AHL_DISP_STATUS'
      AND      flvt3.lookup_code (+) = disp.STATUS_CODE
      AND      flvt3.LANGUAGE(+) = userenv('LANG') ;

Line: 899

 SELECT quantity, uom FROM AHL_PRD_DISP_MTL_TXNS
   WHERE DISPOSITION_ID = p_disp_id  -- 11016 -- 11044
   AND WORKORDER_MTL_TXN_ID = p_workorder_txn_id;

Line: 905

  SELECT 1
    FROM ahl_prd_dispositions_b A
   WHERE A.disposition_id = c_disposition_id
     AND ((A.inventory_item_id = c_inventory_item_id
           AND A.organization_id = c_organization_id)
           OR EXISTS
           (SELECT 1
              FROM ahl_item_associations_b B
             WHERE B.item_group_id = A.item_group_id
               AND B.inventory_item_id = c_inventory_item_id
               AND B.inventory_org_id = (SELECT master_organization_id
                                           FROM mtl_parameters
                                          WHERE organization_id = c_organization_id)
               AND B.interchange_type_code IN ('1-WAY INTERCHANGEABLE','2-WAY INTERCHANGEABLE')));

Line: 1081

  SELECT sum (assoc.quantity)
  FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
  AHL_WORKORDER_MTL_TXNS mtxn
  WHERE disp.disposition_id = p_disp_id
  AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
  AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
  AND disp.disposition_id = assoc.disposition_id
  GROUP BY disp.disposition_id, assoc.disposition_id;

Line: 1121

  SELECT sum (assoc.quantity)
  FROM AHL_PRD_DISPOSITIONS_B disp, AHL_PRD_DISP_MTL_TXNS assoc,
  AHL_WORKORDER_MTL_TXNS mtxn
  WHERE disp.disposition_id = p_disp_id
  AND assoc.workorder_mtl_txn_id = p_workorder_txn_id
  AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
  AND mtxn.transaction_type_id = decode (disp.immediate_disposition_code,'NOT_RECEIVED',WIP_CONSTANTS.ISSCOMP_TYPE,WIP_CONSTANTS.RETCOMP_TYPE)
  AND disp.disposition_id = assoc.disposition_id
  GROUP BY disp.disposition_id, assoc.disposition_id;