eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_CLAIM_PUB SQL Statements

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

Line: 77


    SELECT a.uom_code
    FROM   mtl_units_of_measure a,
	   mtl_system_items c
    WHERE c.primary_unit_of_measure = a.unit_of_measure
    AND   c.organization_id         = p_org_id
    AND   c.inventory_item_id       = p_inventory_item_id
    AND   uom_code                  = p_item_uom;

Line: 118


   l_pvt_claim_rec.last_update_date:=l_claim_rec.last_update_date;

Line: 119


   l_pvt_claim_rec.last_updated_by:=l_claim_rec.last_updated_by;

Line: 122


   l_pvt_claim_rec.last_update_login:=l_claim_rec.last_update_login;

Line: 125


   l_pvt_claim_rec.program_update_date:=l_claim_rec.program_update_date;

Line: 466


     l_pvt_claim_line_tbl(i).last_update_date            := l_claim_line_tbl(i).last_update_date;

Line: 467


     l_pvt_claim_line_tbl(i).last_updated_by             := l_claim_line_tbl(i).last_updated_by ;

Line: 470


     l_pvt_claim_line_tbl(i).last_update_login           := l_claim_line_tbl(i).last_update_login;

Line: 473


     l_pvt_claim_line_tbl(i).program_update_date         := l_claim_line_tbl(i).program_update_date ;

Line: 538


     l_pvt_claim_line_tbl(i).update_from_tbl_flag        := l_claim_line_tbl(i).update_from_tbl_flag;

Line: 646


PROCEDURE Update_Claim(
   p_api_version_number         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_claim_rec                  IN    claim_rec_type,
   p_claim_line_tbl             IN    claim_line_tbl_type,
   x_object_version_number      OUT NOCOPY   NUMBER
)
IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Update_Claim';

Line: 673


SELECT NVL(source_object_class,'NULL')
FROM ozf_claims_all
WHERE claim_id = p_claim_id;

Line: 681


SELECT a.uom_code
FROM   mtl_units_of_measure a,
   mtl_system_items c
WHERE c.primary_unit_of_measure = a.unit_of_measure
AND   c.organization_id         = p_org_id
AND   c.inventory_item_id       = p_inventory_item_id
AND   uom_code                  = p_item_uom;

Line: 694


   SAVEPOINT SAVE_UPDATE_CLAIM_PUB;

Line: 722


   l_pvt_claim_rec.last_update_date:=l_claim_rec.last_update_date;

Line: 723


   l_pvt_claim_rec.last_updated_by:=l_claim_rec.last_updated_by;

Line: 726


   l_pvt_claim_rec.last_update_login:=l_claim_rec.last_update_login;

Line: 729


   l_pvt_claim_rec.program_update_date:=l_claim_rec.program_update_date;

Line: 860


   OZF_Claim_PVT.Update_Claim(
   p_api_version      => p_api_version_number,
   p_init_msg_list    => FND_API.G_FALSE,
   p_commit           => FND_API.G_FALSE,
   P_Validation_Level   => p_Validation_Level,
   x_return_status              => x_return_status,
   x_msg_count                  => x_msg_count,
   x_msg_data                   => x_msg_data,
   p_claim                      => l_pvt_claim_rec,
   p_event                      => 'UPDATE',
   p_mode                       => OZF_claim_Utility_pvt.G_AUTO_MODE,
   x_object_version_number      => x_object_version_number );

Line: 874


      OZF_UTILITY_PVT.DEBUG_MESSAGE('RETURN STATUS FOR UPDATE CLAIM =>'||X_RETURN_STATUS);

Line: 930


	    OZF_UTILITY_PVT.DEBUG_MESSAGE('Before Calling Update_Claim_Line_Tbl ');

Line: 931


	   Update_Claim_Line_Tbl(
	    p_api_version      => p_api_version_number
	   ,p_init_msg_list    => FND_API.G_FALSE
	   ,p_commit           => FND_API.G_FALSE
	   ,P_Validation_Level   => p_Validation_Level
	   ,x_return_status          => x_return_status
	   ,x_msg_data               => x_msg_data
	   ,x_msg_count              => x_msg_count
	   ,p_claim_line_tbl         => l_claim_line_tbl
	   ,p_change_object_version  => FND_API.g_false -- Added For Fix
	   ,x_error_index            => x_error_index);

Line: 943


	   OZF_UTILITY_PVT.DEBUG_MESSAGE('After Call Update_Claim_Line_Tbl ');

Line: 947


      OZF_UTILITY_PVT.DEBUG_MESSAGE('Return Status for Update claim line =>'||x_return_status);

Line: 971


   ROLLBACK TO SAVE_UPDATE_CLAIM_PUB;

Line: 980


   ROLLBACK TO SAVE_UPDATE_CLAIM_PUB;

Line: 989


   ROLLBACK TO SAVE_UPDATE_CLAIM_PUB;

Line: 998


   ROLLBACK TO SAVE_UPDATE_CLAIM_PUB;

Line: 1010


End Update_Claim;

Line: 1029


PROCEDURE Update_Claim_Line_Tbl(
   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_data               OUT NOCOPY   VARCHAR2
   ,x_msg_count              OUT NOCOPY   NUMBER
   ,p_claim_line_tbl         IN    claim_line_tbl_type
   ,p_change_object_version  IN    VARCHAR2 := FND_API.g_false
   ,x_error_index            OUT NOCOPY   NUMBER
)
IS
   L_API_NAME               CONSTANT VARCHAR2(30) := 'Update_Claim_Line';

Line: 1051


  SELECT claim_id
	,line_number
	,split_from_claim_line_id
	,amount
	,claim_currency_amount
	,acctd_amount
	,currency_code
	,exchange_rate_type
	,exchange_rate_date
	,exchange_rate
	,set_of_books_id
	,valid_flag
	,source_object_id
	,source_object_line_id
	,source_object_class
	,source_object_type_id
	,plan_id
	,offer_id
	,utilization_id
	,payment_method
	,payment_reference_id
	,payment_reference_number
	,payment_reference_date
	,voucher_id
	,voucher_number
	,payment_status
	,approved_flag
	,approved_date
	,approved_by
	,settled_date
	,settled_by
	,performance_complete_flag
	,performance_attached_flag
	,select_cust_children_flag
	,item_id
	--,item_description
	,quantity
	,quantity_uom
	,rate
	,activity_type
	,activity_id
	,related_cust_account_id
	,buy_group_cust_account_id
	,relationship_type
	,earnings_associated_flag
	,comments
	,tax_code
	,credit_to
	,attribute_category
	,attribute1
	,attribute2
	,attribute3
	,attribute4
	,attribute5
	,attribute6
	,attribute7
	,attribute8
	,attribute9
	,attribute10
	,attribute11
	,attribute12
	,attribute13
	,attribute14
	,attribute15
	,org_id
	,sale_date
	,item_type
	,tax_amount
	,claim_curr_tax_amount
	,activity_line_id
	,offer_type
	,prorate_earnings_flag
	,earnings_end_date
	,buy_group_party_id
	,acctd_tax_amount
	,dpp_cust_account_id
	,batch_line_id
FROM ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;

Line: 1132


    SELECT inventory_item_id,NVL(description,concatenated_segments)
    FROM mtl_system_items_kfv
    WHERE inventory_item_id = p_inventory_item_id
    AND   organization_id   = p_org_id;

Line: 1138


    SELECT category_id,description
    FROM  mtl_categories_v
    WHERE category_id = p_category_id;

Line: 1143


   SELECT memo_line_id,description
   FROM ar_memo_lines_vl
   WHERE memo_line_id = p_memo_line_id;

Line: 1148


   SELECT channel_id,description
   FROM ams_media_channels_vl
   WHERE channel_id = p_channel_id;

Line: 1156


   SAVEPOINT Update_Claim_Line_Tbl;

Line: 1218


				,l_pvt_claim_line_tbl(i).select_cust_children_flag
				,l_pvt_claim_line_tbl(i).item_id
				,l_pvt_claim_line_tbl(i).quantity
				,l_pvt_claim_line_tbl(i).quantity_uom
				,l_pvt_claim_line_tbl(i).rate
				,l_pvt_claim_line_tbl(i).activity_type
				,l_pvt_claim_line_tbl(i).activity_id
				,l_pvt_claim_line_tbl(i).related_cust_account_id
				,l_pvt_claim_line_tbl(i).buy_group_cust_account_id
				,l_pvt_claim_line_tbl(i).relationship_type
				,l_pvt_claim_line_tbl(i).earnings_associated_flag
				,l_pvt_claim_line_tbl(i).comments
				,l_pvt_claim_line_tbl(i).tax_code
				,l_pvt_claim_line_tbl(i).credit_to
				,l_pvt_claim_line_tbl(i).attribute_category
				,l_pvt_claim_line_tbl(i).attribute1
				,l_pvt_claim_line_tbl(i).attribute2
				,l_pvt_claim_line_tbl(i).attribute3
				,l_pvt_claim_line_tbl(i).attribute4
				,l_pvt_claim_line_tbl(i).attribute5
				,l_pvt_claim_line_tbl(i).attribute6
				,l_pvt_claim_line_tbl(i).attribute7
				,l_pvt_claim_line_tbl(i).attribute8
				,l_pvt_claim_line_tbl(i).attribute9
				,l_pvt_claim_line_tbl(i).attribute10
				,l_pvt_claim_line_tbl(i).attribute11
				,l_pvt_claim_line_tbl(i).attribute12
				,l_pvt_claim_line_tbl(i).attribute13
				,l_pvt_claim_line_tbl(i).attribute14
				,l_pvt_claim_line_tbl(i).attribute15
				,l_pvt_claim_line_tbl(i).org_id
				,l_pvt_claim_line_tbl(i).sale_date
				,l_pvt_claim_line_tbl(i).item_type
				,l_pvt_claim_line_tbl(i).tax_amount
				,l_pvt_claim_line_tbl(i).claim_curr_tax_amount
				,l_pvt_claim_line_tbl(i).activity_line_id
				,l_pvt_claim_line_tbl(i).offer_type
				,l_pvt_claim_line_tbl(i).prorate_earnings_flag
				,l_pvt_claim_line_tbl(i).earnings_end_date
				,l_pvt_claim_line_tbl(i).buy_group_party_id
				,l_pvt_claim_line_tbl(i).acctd_tax_amount
				,l_pvt_claim_line_tbl(i).dpp_cust_account_id
				,l_pvt_claim_line_tbl(i).batch_line_id;

Line: 1270


     IF l_claim_line_tbl(i).last_update_date is null then
        l_pvt_claim_line_tbl(i).last_update_date    := FND_API.G_MISS_DATE;

Line: 1273


        l_pvt_claim_line_tbl(i).last_update_date    :=l_claim_line_tbl(i).last_update_date;

Line: 1276


     IF l_claim_line_tbl(i).last_updated_by is null then
        l_pvt_claim_line_tbl(i).last_updated_by    := FND_API.G_MISS_NUM;

Line: 1279


        l_pvt_claim_line_tbl(i).last_updated_by    :=l_claim_line_tbl(i).last_updated_by;

Line: 1294


     IF l_claim_line_tbl(i).last_update_login is null then
        l_pvt_claim_line_tbl(i).last_update_login    := FND_API.G_MISS_NUM;

Line: 1297


        l_pvt_claim_line_tbl(i).last_update_login    :=l_claim_line_tbl(i).last_update_login;

Line: 1312


     IF l_claim_line_tbl(i).program_update_date is null then
        l_pvt_claim_line_tbl(i).program_update_date    := FND_API.G_MISS_DATE;

Line: 1315


        l_pvt_claim_line_tbl(i).program_update_date    :=l_claim_line_tbl(i).program_update_date;

Line: 1663


     IF l_claim_line_tbl(i).update_from_tbl_flag IS NOT NULL THEN
        l_pvt_claim_line_tbl(i).update_from_tbl_flag   :=l_claim_line_tbl(i).update_from_tbl_flag;

Line: 1707


   Ozf_Claim_Line_Pvt.Update_Claim_line_Tbl(
	    p_api_version		=> p_api_version
	   ,p_init_msg_list		=> FND_API.G_FALSE
	   ,p_commit			=> FND_API.G_FALSE
	   ,P_Validation_Level		=> p_Validation_Level
	   ,x_return_status		=> x_return_status
	   ,x_msg_data			=> x_msg_data
	   ,x_msg_count			=> x_msg_count
	   ,p_claim_line_tbl		=> l_pvt_claim_line_tbl
	   ,p_change_object_version	=> FND_API.g_false
	   ,x_error_index		=> l_error_index );

Line: 1719


      OZF_UTILITY_PVT.DEBUG_MESSAGE('sTATUS FOR Update claim line =>'||x_return_status);

Line: 1742


   ROLLBACK TO Update_Claim_Line_Tbl;

Line: 1751


   ROLLBACK TO Update_Claim_Line_Tbl;

Line: 1760


   ROLLBACK TO Update_Claim_Line_Tbl;

Line: 1769


   ROLLBACK TO Update_Claim_Line_Tbl;

Line: 1781


End Update_Claim_Line_tbl;

Line: 1782


/*End of Update Claim Line*/
---------------------------------------------------------------------
-- PROCEDURE
--   delete_claim
--
-- PURPOSE
--    This procedure deletes claim record when Claim_Id and Object Version Number are provided.
--
-- PARAMETERS
--
--    x_msg_count
--    x_object_version_number
--    x_return_status
--    p_claim_id
--    p_object_version_number
-- NOTES
---------------------------------------------------------------------
PROCEDURE Delete_Claim(
   p_api_version_number         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_claim_id                   IN  NUMBER,
   p_object_version_number      IN   NUMBER
)
IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Claim';

Line: 1817


   SAVEPOINT DELETE_Claim_PUB;

Line: 1840


   OZF_Claim_PVT.Delete_Claim(
    p_api_version_number     => p_api_version_number,
    p_init_msg_list          => FND_API.G_FALSE,
    p_commit                 => FND_API.G_FALSE,
    p_object_id              => l_claim_id,
    p_object_version_number  => l_object_version_number,
    x_return_status          => x_return_status,
    x_msg_count              => x_msg_count,
    x_msg_data               => x_msg_data
    );

Line: 1852


      OZF_UTILITY_PVT.DEBUG_MESSAGE('RETURN STATUS FOR DELETE_CLAIM =>'||X_RETURN_STATUS);

Line: 1872


   ROLLBACK TO DELETE_Claim_PUB;

Line: 1881


   ROLLBACK TO DELETE_Claim_PUB;

Line: 1890


   ROLLBACK TO DELETE_Claim_PUB;

Line: 1899


   ROLLBACK TO DELETE_Claim_PUB;

Line: 1911


   End Delete_Claim;

Line: 1929


PROCEDURE Delete_Claim_Line_Tbl(
   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_data               OUT NOCOPY   VARCHAR2
   ,x_msg_count              OUT NOCOPY   NUMBER
   ,p_claim_line_tbl         IN    claim_line_tbl_type
   ,p_change_object_version  IN    VARCHAR2 := FND_API.g_false
   ,x_error_index            OUT NOCOPY   NUMBER)
IS
   L_API_NAME                  CONSTANT VARCHAR2(30) := 'Delete_Claim_Line';

Line: 1948


   SAVEPOINT DELETE_Claim_Line_PUB;

Line: 1980


   OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
    p_api_version        => p_api_version
   ,p_init_msg_list      => FND_API.G_FALSE
   ,p_commit             => FND_API.G_FALSE
   ,P_Validation_Level   => p_Validation_Level
   ,x_return_status         =>    x_return_status
   ,x_msg_data              =>    x_msg_data
   ,x_msg_count             =>    x_msg_count
   ,p_claim_line_tbl        =>    l_pvt_claim_line_tbl
   ,p_change_object_version =>    FND_API.g_false
   ,x_error_index           =>    l_error_index);

Line: 1992


      OZF_UTILITY_PVT.DEBUG_MESSAGE('RETURN STATUS FOR DELETE_CLAIM_Line_Tbl =>'||X_RETURN_STATUS);

Line: 2019


   ROLLBACK TO DELETE_Claim_Line_PUB;

Line: 2029


   ROLLBACK TO DELETE_Claim_Line_PUB;

Line: 2038


   ROLLBACK TO DELETE_Claim_Line_PUB;

Line: 2047


   ROLLBACK TO DELETE_Claim_PUB;

Line: 2059


End Delete_Claim_Line_Tbl;

Line: 2060


/* End of Delete claim line*/

---------------------------------------------------------------------
-- PROCEDURE
--   asso_accruals_to_claim
--
-- PURPOSE
--    This procedure associates accruals based on the given fund
--    utilization criteria.
--
-- PARAMETERS
--    p_api_version
--    p_init_msg_list
--    p_commit
--    p_validation_level
--    p_claim_id
--    p_funds_util_flt
--    x_return_status
--    x_msg_count
--    x_msg_data
-- NOTES
---------------------------------------------------------------------
PROCEDURE Asso_Accruals_To_Claim(
    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_data               OUT NOCOPY   VARCHAR2
   ,x_msg_count              OUT NOCOPY   NUMBER
   ,p_claim_id               IN    NUMBER
   ,p_funds_util_flt         IN    funds_util_flt_type
)
IS
l_api_version                CONSTANT NUMBER       := 1.0;

Line: 2144


   l_funds_util_flt.select_cust_children_flag   := p_funds_util_flt.select_cust_children_flag;

Line: 2511


   l_funds_util_flt.select_cust_children_flag   := p_funds_util_flt.select_cust_children_flag;

Line: 2753


   l_funds_util_flt.select_cust_children_flag   := p_funds_util_flt.select_cust_children_flag;

Line: 2895


   SELECT claim_id,object_version_number,status_code,amount_remaining
   FROM   ozf_claims_all
   WHERE  claim_id = cv_parent_claim_id;

Line: 2900


   SELECT claim_type_id
   FROM   ozf_claim_types_vl
   WHERE  claim_type_id = cv_claim_type_id
   AND NVL(end_date,SYSDATE) >= SYSDATE;

Line: 2906


   SELECT reason_code_id
   FROM   ozf_reason_codes_vl
   WHERE  reason_code_id = cv_reason_code_id
   AND NVL(end_date_active,SYSDATE) >= SYSDATE;

Line: 2913


   SELECT claim_line_id,amount
     FROM ozf_claim_lines_all
    WHERE claim_id      = cv_parent_claim_id
      AND claim_line_id = cv_claim_line_id;

Line: 3227


   SELECT event_type_code
   FROM xla_event_types_vl
   WHERE application_id    = 682
   AND accounting_flag     ='Y'
   AND enabled_flag        ='Y'
   AND event_type_code     = cv_event_type_code;

Line: 3235


   SELECT claim_id
	 ,status_code
	 ,payment_method
   FROM ozf_claims_all
   WHERE claim_id = cv_claim_id;

Line: 3242


  SELECT COUNT(clu.claim_line_util_id)
   FROM ozf_claim_lines_util_all clu,
	ozf_claim_lines_all cli
  WHERE clu.claim_line_id = cli.claim_line_id
    AND cli.claim_id      =  cv_claim_id;

Line: 3249


   SELECT count(event_id),
	  NVL(reversal_flag,'N')
   FROM ozf_xla_claim_headers
   WHERE claim_id = cv_claim_id
   GROUP BY reversal_flag;

Line: 3256


   SELECT tax_for
   FROM ozf_claim_sttlmnt_methods_all csm,ozf_claims_all c
   WHERE csm.settlement_method = c.payment_method
   AND c.claim_id =p_claim_id
   AND csm.org_id = c.org_id;

Line: 3263


   SELECT evl.event_class_code
     FROM xla_event_types_vl evl
    WHERE evl.application_id   = 682
      AND evl.event_type_code  = cv_event_type_code;

Line: 3270


   SELECT 1
     FROM xla_event_types_vl evl,
          ozf_xla_claim_headers clh
    WHERE evl.application_id   = 682
      AND evl.event_type_code  = clh.event_type_code
      AND clh.claim_id         = cv_claim_id
      AND evl.event_class_code = cv_event_class_code;

Line: 3279


SELECT NVL(osp.post_to_gl, 'F')
FROM   ozf_sys_parameters_all osp
,      ozf_claims_all oc
WHERE  osp.org_id = oc.org_id
AND    oc.claim_id = p_id;