APPS.DPP_COVEREDINVENTORY_PVT SQL Statements

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

Line: 28

PROCEDURE Select_CoveredInventory
     (p_api_Version       IN NUMBER,
      p_Init_msg_List     IN VARCHAR2 := fnd_api.g_False,
      p_Commit            IN VARCHAR2 := fnd_api.g_False,
      p_Validation_Level  IN NUMBER := fnd_api.g_Valid_Level_Full,
      x_Return_Status     OUT NOCOPY VARCHAR2,
      x_msg_Count         OUT NOCOPY NUMBER,
      x_msg_Data          OUT NOCOPY VARCHAR2,
      p_Inv_hdr_rec       IN DPP_INV_HDR_REC_TYPE,
      p_Covered_Inv_Tbl   IN OUT NOCOPY DPP_INV_COV_TBL_TYPE)
IS
l_api_name              CONSTANT VARCHAR2(30) := 'Select_CoveredInventory';

Line: 59

     SELECT sum(case when ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
                          AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date))
     --BETWEEN p_effective_start_date and p_effective_end_date)
            then moqd.transaction_quantity else 0 end) covered_qty,
            sum(moqd.transaction_quantity) onhand_qty,
            moqd.transaction_uom_code
       FROM mtl_onhand_quantities_detail moqd,
            org_organization_definitions ood,
            mtl_parameters mp
      WHERE moqd.organization_id = ood.organization_id
        AND moqd.inventory_item_id = p_inventory_item_id
        AND mp.organization_id = ood.organization_id
        AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
        AND ood.operating_unit = p_org_id
        AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
        AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
        AND moqd.PLANNING_TP_TYPE = 2
        AND moqd.OWNING_TP_TYPE = 2
        AND moqd.IS_CONSIGNED = 2
        GROUP BY moqd.transaction_uom_code;

Line: 85

	SELECT
	  SUM(moqd.transaction_quantity) sum,
	  ood.organization_name warehouse,
	  ood.organization_id warehouse_id
	FROM
	  mtl_onhand_quantities_detail moqd,
	  org_organization_definitions ood,
	  mtl_parameters mp
	WHERE moqd.organization_id = ood.organization_id
          AND moqd.inventory_item_id = p_inventory_item_id
          AND ood.operating_unit = p_org_id
          AND mp.organization_id = ood.organization_id
          AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
          AND (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
              AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date)
          --BETWEEN p_effective_start_date and p_effective_end_date
	  AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
          AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
          AND moqd.PLANNING_TP_TYPE = 2
          AND moqd.OWNING_TP_TYPE = 2
          AND moqd.IS_CONSIGNED = 2
          GROUP BY ood.organization_name,ood.organization_id;

Line: 109

	SELECT
	  (NVL(moqd.orig_date_received,moqd.date_received)) date_received,
	  SUM(moqd.transaction_quantity) sum
	FROM
	  mtl_onhand_quantities_detail moqd,
	  org_organization_definitions ood,
	  mtl_parameters mp
	WHERE
	  moqd.organization_id = ood.organization_id  AND
	  moqd.inventory_item_id = p_inventory_item_id AND
	  ood.operating_unit = p_org_id AND
	  mp.organization_id = ood.organization_id  AND
	  NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
    moqd.organization_id = p_warehouse_id AND
		moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
        AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
        AND moqd.PLANNING_TP_TYPE = 2
        AND moqd.OWNING_TP_TYPE = 2
        AND moqd.IS_CONSIGNED = 2
    GROUP BY (NVL(moqd.orig_date_received,moqd.date_received));

Line: 133

    SAVEPOINT  Select_CoveredInventory_PVT;

Line: 195

               l_covered_inv_wh_tbl.delete;

Line: 224

                 SELECT primary_uom_code
                   INTO l_primary_uom_code
                   FROM mtl_system_items msi,
                        mtl_parameters mp
                  WHERE inventory_item_id = l_covered_inv_tbl(i).inventory_item_id
                    AND mp.organization_id = msi.organization_id
                    AND mp.organization_id = mp.master_organization_id
                    AND rownum = 1;

Line: 245

     DPP_UTILITY_pvt.debug_message ('select_coveredinventory(): x_return_status: ' || x_return_status);

Line: 273

   ROLLBACK TO Select_CoveredInventory_PVT;

Line: 289

   ROLLBACK TO Select_CoveredInventory_PVT;

Line: 304

   ROLLBACK TO Select_CoveredInventory_PVT;

Line: 307

			fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Select_CoveredInventory');

Line: 324

  END Select_CoveredInventory;

Line: 401

     UPDATE DPP_EXECUTION_DETAILS
	   SET execution_end_date = sysdate
              ,execution_status 	= DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
              ,last_update_date 	= sysdate
              ,last_updated_by 	= l_inv_hdr_rec.Last_Updated_By
              ,last_update_login 	= l_inv_hdr_rec.Last_Updated_By
              ,provider_process_id = l_inv_hdr_rec.Provider_Process_Id
              ,provider_process_instance_id = l_inv_hdr_rec.Provider_Process_Instance_id
              ,output_xml 		= XMLTYPE(l_inv_hdr_rec.Output_XML)
        WHERE execution_detail_id 	= l_inv_hdr_rec.Execution_Detail_ID;

Line: 426

DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory(
    p_api_version   	  => l_api_version
   ,p_init_msg_list	    => FND_API.G_FALSE
   ,p_commit	          => FND_API.G_FALSE
   ,p_validation_level	=> FND_API.G_VALID_LEVEL_FULL
   ,x_return_status	    => l_return_status
   ,x_msg_count	        => l_msg_count
   ,x_msg_data	        => l_msg_data
   ,p_inv_hdr_rec	      => l_inv_hdr_rec
   ,p_covered_inv_tbl	  => l_covered_inv_tbl
   );

Line: 525

PROCEDURE Update_CoveredInventory(
    p_api_version   	 IN 	  NUMBER
   ,p_init_msg_list	     IN 	  VARCHAR2     := FND_API.G_FALSE
   ,p_commit	         IN 	  VARCHAR2     := FND_API.G_FALSE
   ,p_validation_level	 IN 	  NUMBER       := FND_API.G_VALID_LEVEL_FULL
   ,x_return_status	     OUT 	NOCOPY  VARCHAR2
   ,x_msg_count	         OUT 	NOCOPY  NUMBER
   ,x_msg_data	         OUT 	NOCOPY  VARCHAR2
   ,p_inv_hdr_rec	     IN    dpp_inv_hdr_rec_type
   ,p_covered_inv_tbl	 IN    dpp_inv_cov_tbl_type
)
IS
l_api_name              CONSTANT VARCHAR2(30) := 'Update_CoveredInventory';

Line: 563

    SAVEPOINT  Update_CoveredInventory_PVT;

Line: 587

  IF l_inv_hdr_rec.Last_Updated_By IS NULL THEN
     FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');

Line: 589

     FND_MESSAGE.set_token('ID', 'Last Updated By');

Line: 624

	        UPDATE DPP_TRANSACTION_LINES_ALL
             SET covered_inventory 	= NVL(l_covered_inv_tbl(i).Covered_quantity,0),
	               onhand_inventory 	= NVL(l_covered_inv_tbl(i).Onhand_Quantity,0),
	               UOM             	   = l_covered_inv_tbl(i).UOM_Code,
                 last_update_date    = l_sysdate,
                 last_updated_by     = l_inv_hdr_rec.Last_Updated_By,
                 last_calculated_by  = l_inv_hdr_rec.Last_Updated_By,
                 last_update_login   = FND_GLOBAL.LOGIN_ID,
                 last_calculated_date = l_sysdate
           WHERE transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;

Line: 642

             DPP_UTILITY_PVT.debug_message(substr(('Error in Updating DPP_TRANSACTION_LINES_ALL: ' || SQLERRM || ' from Update Covered Inventory API'),1,4000));

Line: 652

 l_txn_lines_tbl(i).last_update_date          := l_sysdate;

Line: 653

 l_txn_lines_tbl(i).last_updated_by           := l_inv_hdr_rec.Last_Updated_By;

Line: 655

 l_txn_lines_tbl(i).created_by                := l_inv_hdr_rec.Last_Updated_By;

Line: 656

 l_txn_lines_tbl(i).last_update_login         := FND_GLOBAL.LOGIN_ID;

Line: 658

 l_txn_lines_tbl(i).last_calculated_by        := l_inv_hdr_rec.Last_Updated_By;

Line: 665

				SELECT
					inventory_details_id
				BULK COLLECT INTO
					inventory_details_ids
				FROM
					dpp_inventory_details_all
				WHERE
					org_id = l_inv_hdr_rec.org_id
				 AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;

Line: 676

   DELETE
	  FROM DPP_INVENTORY_DETAILS_ADJ_ALL
	 WHERE INVENTORY_DETAILS_ID = inventory_details_ids(indx);

Line: 681

	DELETE
	  FROM DPP_INVENTORY_DETAILS_ALL
	 WHERE org_id = l_inv_hdr_rec.org_id
	   AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;

Line: 692

						fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory - Delete rows');

Line: 702

           SELECT DPP_INVENTORY_DETAILS_SEQ.nextval
             INTO l_inv_details_id
             FROM DUAL;

Line: 706

         INSERT INTO DPP_INVENTORY_DETAILS_ALL(
                inventory_details_id,
                transaction_line_id,
                quantity,
                uom,
                include_flag,
                creation_date,
                created_by,
                last_update_date,
                last_updated_by,
                last_update_login,
                inventory_item_id,
                org_id,
                organization_id,
                object_version_number)
        VALUES(	l_inv_details_id,
                l_covered_inv_tbl(i).Transaction_Line_Id,
                NVL(l_covered_inv_tbl(i).wh_line_tbl(j).Covered_quantity,0),
                l_covered_inv_tbl(i).UOM_Code,
                'N',
                l_sysdate,
                l_inv_hdr_rec.Last_Updated_By,
                l_sysdate,
                l_inv_hdr_rec.Last_Updated_By,
                l_inv_hdr_rec.Last_Updated_By,
                l_covered_inv_tbl(i).inventory_item_id,
                l_inv_hdr_rec.org_id,
                l_covered_inv_tbl(i).wh_line_tbl(j).Warehouse_id,
                1);

Line: 757

          INSERT INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
						inv_details_adj_id,
						inventory_details_id,
						date_received,
						days_out,
						quantity,
						uom,
						comments,
						include_flag,
						creation_date,
						created_by,
						last_update_date,
						last_updated_by,
						last_update_login,
						org_id,
						object_version_number)
					VALUES(dpp_inv_details_adj_id_seq.nextval,
						l_inv_details_id,
						l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received,
						l_days_out,
						NVL(l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).Onhand_quantity,0),
						l_covered_inv_tbl(i).UOM_Code,
						null,
						l_include_flag,
						l_sysdate,
						l_inv_hdr_rec.Last_Updated_By,
						l_sysdate,
						l_inv_hdr_rec.Last_Updated_By,
						l_inv_hdr_rec.Last_Updated_By,
						l_inv_hdr_rec.org_id,
						1
						);

Line: 792

             UPDATE DPP_INVENTORY_DETAILS_ALL
                SET include_flag = 'Y',
                    object_version_number = object_version_number + 1,
                    last_update_date = l_sysdate,
                    last_updated_by = l_inv_hdr_rec.Last_Updated_By,
                    last_update_login = l_inv_hdr_rec.Last_Updated_By
              WHERE inventory_details_id = l_inv_details_id;

Line: 806

  DPP_LOG_PVT.Insert_LinesLog(p_api_version      => 1.0
                             ,p_init_msg_list    => FND_API.G_FALSE
                             ,p_commit	         => FND_API.G_FALSE
                             ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
                             ,x_return_status	 		=> l_return_status
                             ,x_msg_count	 				=> l_msg_count
                             ,x_msg_data	 				=> l_msg_data
                             ,p_txn_lines_tbl	 		=> l_txn_lines_tbl
                             );

Line: 816

   dpp_utility_pvt.debug_message('return status for Insert_LinesLog =>'||l_return_status);

Line: 818

      dpp_utility_pvt.debug_message(substr(('Message dat for the DPP Insert_LinesLog API =>'||l_msg_data),1,4000));

Line: 840

      ROLLBACK TO Update_CoveredInventory_PVT;

Line: 855

		 ROLLBACK TO Update_CoveredInventory_PVT;

Line: 870

		 ROLLBACK TO Update_CoveredInventory_PVT;

Line: 885

		 ROLLBACK TO Update_CoveredInventory_PVT;

Line: 888

				fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory');

Line: 905

END Update_CoveredInventory;