[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_stale_data_batch_line(p_batch_number IN VARCHAR2,
p_batch_line_number IN VARCHAR2) AS
BEGIN
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Procedure Starts');
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'S',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND batch_line_number = p_batch_line_number
AND processed_flag = 'N';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Procedure Ends');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_stale_data_batch_line', 'Exception: '||sqlerrm);
END update_stale_data_batch_line;
ozf_sd_batch_feed_pvt.update_stale_data_batch_line(p_batch_number, p_batch_line_number);
'WebADI Batch - before inserting into interface table');
INSERT INTO ozf_sd_batch_lines_int_all
(batch_line_int_id,
frm_contact1_name,
frm_contact1_email,
frm_contact1_fax,
frm_contact1_phone,
frm_gbl_ptnr_role_class_code,
frm_business_name,
frm_gbl_business_id,
frm_gbl_supply_chain_code,
frm_prop_business_id,
frm_prop_domain_id1,
frm_prop_id_authority1,
frm_gbl_partner_class_code,
frm_contact2_name,
frm_contact2_email,
frm_contact2_fax,
frm_contact2_phone,
frm_gbl_location_id,
frm_prop_domain_id2,
frm_prop_id_authority2,
frm_prop_location_id,
frm_address_line1,
frm_address_line2,
frm_address_line3,
frm_city_name,
frm_gbl_country_code,
frm_national_postal_code,
frm_pobox_id,
frm_region_name,
gbl_doc_function_code,
ship_frm_sd_authorization_id,
dist_by_business_name,
dist_by_gbl_business_id,
dist_by_gbl_supply_chain_code,
dist_by_prop_business_id,
dist_by_prop_domain_id1,
dist_by_prop_idauthority1,
dist_by_gbl_partner_class_code,
dist_by_contact1_name,
dist_by_contact1_email,
dist_by_contact1_fax,
dist_by_contact1_phone,
dist_by_gbl_location_id,
dist_by_prop_domain_id2,
dist_by_prop_idauthority2,
dist_by_prop_location_id,
dist_by_address_line1,
dist_by_address_line2,
dist_by_address_line3,
dist_by_city_name,
dist_by_gbl_country_code,
dist_by_national_postal_code,
dist_by_pobox_id,
dist_by_region_name,
ship_to_business_name,
ship_to_gbl_business_id,
ship_to_gbl_supply_chain_code,
ship_to_prop_business_id,
ship_to_prop_domain_id1,
ship_to_prop_id_authority1,
ship_to_gbl_partner_class_code,
ship_to_contact1_name,
ship_to_contact1_email,
ship_to_contact1_fax,
ship_to_contact1_phone,
ship_to_gbl_location_id,
ship_to_prop_domain_id2,
ship_to_prop_id_authority2,
ship_to_prop_location_id,
ship_to_address_line1,
ship_to_address_line2,
ship_to_address_line3,
ship_to_city_name,
ship_to_gbl_country_code,
ship_to_national_postal_code,
ship_to_pobox_id,
ship_to_region_name,
sold_to_business_name,
sold_to_gbl_business_id,
sold_to_gbl_supply_chain_code,
sold_to_prop_business_id,
sold_to_prop_domain_id1,
sold_to_prop_id_authority1,
sold_to_gbl_partner_class_code,
sold_to_contact1_name,
sold_to_contact1_email,
sold_to_contact1_fax,
sold_to_contact1_phone,
sold_to_gbl_location_id,
sold_to_prop_domain_id2,
sold_to_prop_id_authority2,
sold_to_prop_location_id,
sold_to_address_line1,
sold_to_address_line2,
sold_to_address_line3,
sold_to_city_name,
sold_to_gbl_country_code,
sold_to_national_postal_code,
sold_to_pobox_id,
sold_to_region_name,
used_by_business_name,
used_by_gbl_business_id,
used_by_gbl_supply_chain_code,
used_by_prop_business_id,
used_by_prop_domain_id1,
used_by_prop_id_authority1,
used_by_gbl_partner_class_code,
used_by_contact1_name,
used_by_contact1_email,
used_by_contact1_fax,
used_by_contact1_phone,
used_by_gbl_location_id,
used_by_prop_domain_id2,
used_by_prop_id_authority2,
used_by_prop_location_id,
used_by_address_line1,
used_by_address_line2,
used_by_address_line3,
used_by_city_name,
used_by_gbl_country_code,
used_by_national_postal_code,
used_by_pobox_id,
used_by_region_name,
ship_frm_sd_claim_request_date,
ship_frm_sd_claim_request_id,
credit_reference_id,
debit_reference_id,
batch_line_number,
order_date,
order_line_number,
order_number,
invoice_date,
invoice_line_number,
invoice_number,
cost_currency_code,
cost_monetary_amount,
auth_cost_currency_code,
auth_cost_monetary_amount,
resale_currency_code,
resale_monetary_amount,
gbl_uom,
gbl_claim_disposition_code,
vendor_product_id,
dist_product_id,
ship_date,
shipped_quantity,
vendor_auth_lineitem_line_nbr,
vendor_auth_cost_currency_code,
vendor_auth_cost_monetary_amt,
vendor_auth_quantity,
this_doc_generation_date,
this_document_id,
to_contact1_name,
to_contact1_email,
to_contact1_fax,
to_contact1_phone,
to_gbl_partner_role_class_code,
to_business_name1,
to_gbl_business_id,
to_gbl_supply_chain_code,
to_prop_business_id,
to_prop_domain_id1,
to_prop_id_authority1,
to_gbl_partner_class_code,
to_contact2_name,
to_contact2_email,
to_contact2_fax,
to_contact2_phone,
to_gbl_location_id,
to_prop_domain_id2,
to_prop_id_authority2,
to_prop_location_id,
to_address_line1,
to_address_line2,
to_address_line3,
to_city_name,
to_gbl_country_code,
to_national_postal_code,
to_pobox_id,
to_region_name,
creation_date,
last_update_date,
last_updated_by,
created_by,
processed_flag,
batch_id,
batch_line_id,
gbl_claim_rej_code1,
gbl_claim_rej_code2,
gbl_claim_rej_code3,
gbl_claim_rej_code4,
gbl_claim_rej_code5,
gbl_claim_rej_code6,
gbl_claim_rej_code7,
gbl_claim_rej_code8,
gbl_claim_rej_code9,
gbl_claim_rej_code10,
header_attribute_category,
header_attribute1,
header_attribute2,
header_attribute3,
header_attribute4,
header_attribute5,
header_attribute6,
header_attribute7,
header_attribute8,
header_attribute9,
header_attribute10,
header_attribute11,
header_attribute12,
header_attribute13,
header_attribute14,
header_attribute15,
header_attribute16,
header_attribute17,
header_attribute18,
header_attribute19,
header_attribute20,
header_attribute21,
header_attribute22,
header_attribute23,
header_attribute24,
header_attribute25,
header_attribute26,
header_attribute27,
header_attribute28,
header_attribute29,
header_attribute30,
line_attribute_category,
line_attribute1,
line_attribute2,
line_attribute3,
line_attribute4,
line_attribute5,
line_attribute6,
line_attribute7,
line_attribute8,
line_attribute9,
line_attribute10,
line_attribute11,
line_attribute12,
line_attribute13,
line_attribute14,
line_attribute15,
line_attribute16,
line_attribute17,
line_attribute18,
line_attribute19,
line_attribute20,
line_attribute21,
line_attribute22,
line_attribute23,
line_attribute24,
line_attribute25,
line_attribute26,
line_attribute27,
line_attribute28,
line_attribute29,
line_attribute30
)
VALUES
(ozf_sd_batch_lines_int_all_s.nextval,
p_to_cntct1_name,
p_to_cntct1_email,
p_to_cntct1_fax,
p_to_cntct1_phone,
p_to_gbl_ptnr_role_class_cd,
p_to_business_name,
p_to_gbl_business_id,
p_to_gbl_supply_chain_cd,
p_to_prop_business_id1,
p_to_prop_domain_id1,
p_to_auth_id1,
p_to_gbl_ptnr_class_cd,
p_to_cntct2_name,
p_to_cntct2_email,
p_to_cntct2_fax,
p_to_cntct2_phone,
p_to_gbl_loc_id,
p_to_prop_domain_id2,
p_to_prop_auth_id2,
p_to_prop_loc_id,
p_to_add_line1,
p_to_add_line2,
p_to_add_line3,
p_to_city,
p_to_country,
p_to_postal_code,
p_to_po_box_id,
p_to_region,
'RESPONSE',
p_ship_from_sd_auth_id,
p_dist_by_business_name,
p_dist_by_gbl_business_id,
'Electronic Components', --P_DIST_BY_GBL_SUPP_CHAIN_CD ,
p_dist_by_prop_business_id,
p_dist_by_prop_domain_id1,
p_dist_by_prop_auth_id1,
'Distributor', --P_DIST_BY_GBL_PTNR_CLASS_CD ,
p_dist_by_cntct_name,
p_dist_by_cntct_email,
p_dist_by_cntct_fax,
p_dist_by_cntct_phone,
p_dist_by_gbl_loc_id,
p_dist_by_prop_domain_id2,
p_dist_by_prop_auth_id2,
p_dist_by_prop_loc_id,
p_dist_by_add_line1,
p_dist_by_add_line2,
p_dist_by_add_line3,
p_dist_by_city,
p_dist_by_country,
p_dist_by_postal_code,
p_dist_by_po_box_id,
p_dist_by_region,
p_ship_to_business_name,
p_ship_to_gbl_business_id,
p_ship_to_gbl_supp_chain_cd,
p_ship_to_prop_business_id,
p_ship_to_prop_domain_id1,
p_ship_to_prop_auth_id1,
p_ship_to_gbl_ptnr_class_cd,
p_ship_to_cust_cntct_name,
p_ship_to_cust_cntct_email,
p_ship_to_cust_cntct_fax,
p_ship_to_cust_cntct_phone,
p_ship_to_cust_gbl_loc_id,
p_ship_to_prop_domain_id2,
p_ship_to_prop_auth_id2,
p_ship_to_cust_prop_loc_id,
p_ship_to_cust_add1,
p_ship_to_cust_add2,
p_ship_to_cust_add3,
p_ship_to_cust_city,
p_ship_to_cust_country,
p_ship_to_cust_postal_code,
p_ship_to_cust_po_box_id,
p_ship_to_cust_region,
p_sold_to_business_name,
p_sold_to_gbl_business_id,
p_sold_to_gbl_supp_chain_cd,
p_sold_to_prop_business_id,
p_sold_to_prop_domain_id1,
p_sold_to_prop_auth_id1,
p_sold_to_gbl_ptnr_class_cd,
p_sold_to_cust_cntct_name,
p_sold_to_cust_cntct_email,
p_sold_to_cust_cntct_fax,
p_sold_to_cust_cntct_phone,
p_sold_to_cust_gbl_loc_id,
p_sold_to_prop_domain_id2,
p_sold_to_prop_auth_id2,
p_sold_to_cust_prop_loc_id,
p_sold_to_cust_add1,
p_sold_to_cust_add2,
p_sold_to_cust_add3,
p_sold_to_cust_city,
p_sold_to_cust_country,
p_sold_to_cust_postal_code,
p_sold_to_cust_po_box_id,
p_sold_to_cust_region,
p_end_cust_business_name,
p_end_cust_gbl_business_id,
p_end_cust_gbl_supp_chain_cd,
p_end_cust_prop_business_id,
p_end_cust_prop_domain_id1,
p_end_cust_prop_auth_id1,
p_end_cust_gbl_ptnr_class_cd,
p_end_cust_cntct_name,
p_end_cust_cntct_email,
p_end_cust_cntct_fax,
p_end_cust_cntct_phone,
p_end_cust_gbl_loc_id,
p_end_cust_prop_domain_id2,
p_end_cust_prop_auth_id2,
p_end_cust_prop_loc_id,
p_end_cust_add1,
p_end_cust_add2,
p_end_cust_add3,
p_end_cust_city,
p_end_cust_country,
p_end_cust_postal_code,
p_end_cust_po_box_id,
p_end_cust_region,
p_ship_frm_sd_claim_req_date,
p_batch_number, --Inserting batch_number for SHIP_FRM_SD_CLAIM_REQ_ID to be in sync with xml
p_credit_ref_id,
p_debit_ref_id,
p_batch_line_number,
p_order_date,
p_order_line_number,
p_order_number,
p_invoice_date,
p_invoice_line_number,
p_invoice_number,
p_cost_price_curr_code,
p_cost_price,
p_auth_price_curr_code,
p_auth_price,
p_resale_price_curr_code,
p_resale_price,
p_uom,
p_line_status,
p_vendor_part_number,
p_dist_part_number,
p_date_shipped,
p_qty_shipped,
p_vendor_auth_line_item_no,
p_vendor_apprvd_amt_curr_cd,
p_vendor_apprvd_amt,
p_vendor_apprvd_qty,
p_batch_submission_date,
p_batch_number,
p_frm_cntct1_name,
p_frm_cntct1_email,
p_frm_cntct1_fax,
p_frm_cntct1_phone,
p_frm_gbl_ptnr_role_class_cd,
p_frm_business_name,
p_frm_gbl_business_id,
p_frm_gbl_supply_chain_cd,
p_frm_prop_business_id1,
p_frm_prop_domain_id1,
p_frm_auth_id1,
p_frm_gbl_ptnr_class_cd,
p_frm_cntct2_name,
p_frm_cntct2_email,
p_frm_cntct2_fax,
p_frm_cntct2_phone,
p_frm_gbl_loc_id,
p_frm_prop_domain_id2,
p_frm_prop_auth_id2,
p_frm_prop_loc_id,
p_frm_add_line1,
p_frm_add_line2,
p_frm_add_line3,
p_frm_city,
p_frm_country,
p_frm_postal_code,
p_frm_po_box_id,
p_frm_region,
sysdate,
sysdate,
0,
0,
'N',
p_batch_id,
p_batch_line_id,
p_disposition_code1,
p_disposition_code2,
p_disposition_code3,
p_disposition_code4,
p_disposition_code5,
p_disposition_code6,
p_disposition_code7,
p_disposition_code8,
p_disposition_code9,
p_disposition_code10,
P_HDR_ATTR_CATG,
P_HDR_ATTR1,
P_HDR_ATTR2,
P_HDR_ATTR3,
P_HDR_ATTR4,
P_HDR_ATTR5,
P_HDR_ATTR6,
P_HDR_ATTR7,
P_HDR_ATTR8,
P_HDR_ATTR9,
P_HDR_ATTR10,
P_HDR_ATTR11,
P_HDR_ATTR12,
P_HDR_ATTR13,
P_HDR_ATTR14,
P_HDR_ATTR15,
P_HDR_ATTR16,
P_HDR_ATTR17,
P_HDR_ATTR18,
P_HDR_ATTR19,
P_HDR_ATTR20,
P_HDR_ATTR21,
P_HDR_ATTR22,
P_HDR_ATTR23,
P_HDR_ATTR24,
P_HDR_ATTR25,
P_HDR_ATTR26,
P_HDR_ATTR27,
P_HDR_ATTR28,
P_HDR_ATTR29,
P_HDR_ATTR30,
P_LINE_ATTR_CATG,
P_LINE_ATTR1,
P_LINE_ATTR2,
P_LINE_ATTR3,
P_LINE_ATTR4,
P_LINE_ATTR5,
P_LINE_ATTR6,
P_LINE_ATTR7,
P_LINE_ATTR8,
P_LINE_ATTR9,
P_LINE_ATTR10,
P_LINE_ATTR11,
P_LINE_ATTR12,
P_LINE_ATTR13,
P_LINE_ATTR14,
P_LINE_ATTR15,
P_LINE_ATTR16,
P_LINE_ATTR17,
P_LINE_ATTR18,
P_LINE_ATTR19,
P_LINE_ATTR20,
P_LINE_ATTR21,
P_LINE_ATTR22,
P_LINE_ATTR23,
P_LINE_ATTR24,
P_LINE_ATTR25,
P_LINE_ATTR26,
P_LINE_ATTR27,
P_LINE_ATTR28,
P_LINE_ATTR29,
P_LINE_ATTR30);
PROCEDURE update_data(p_batch_number IN VARCHAR2,
x_return_status OUT nocopy VARCHAR2,
x_msg_data OUT nocopy VARCHAR2
) IS
l_cnt_hdr NUMBER;
SELECT batch_line_number,
auth_cost,
auth_curr_code,
stat_code,
credit_reference_id,
vendor_auth_quantity,
adjustment_type,
ozf_utility_pvt.currround(CASE
WHEN((auth_cost is null and vendor_auth_quantity is null) OR ( auth_curr_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR auth_cost < 0 OR (original_claim_amount > 0 and vendor_auth_quantity < 0) ) then 0
WHEN (auth_cost is null and vendor_auth_quantity IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * vendor_auth_quantity)
WHEN (auth_cost is null and vendor_auth_quantity IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * vendor_auth_quantity)
WHEN (vendor_auth_quantity is null and auth_cost is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - auth_cost) * QUANTITY_SHIPPED)
WHEN (vendor_auth_quantity is null and auth_cost is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (auth_cost * QUANTITY_SHIPPED)
WHEN (vendor_auth_quantity is not null and auth_cost is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - auth_cost) * vendor_auth_quantity)
WHEN (vendor_auth_quantity is not null and auth_cost is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((auth_cost) * vendor_auth_quantity)
END,claim_amount_currency_code) approved_claim_amount,
line_status_code,
computed_batch_curr_claim_amt,
claim_amount,
batch_line_id,
original_claim_amount,
claim_amount_currency_code
FROM(
SELECT bint.batch_line_number batch_line_number,
DECODE(gbl_claim_disposition_code,'APPROVED', DECODE(discount_type, 'AMT' , discount_value , agreement_price), vendor_auth_cost_monetary_amt) auth_cost,
DECODE(gbl_claim_disposition_code,'APPROVED', DECODE(discount_type, 'AMT' , discount_currency_code, Agreement_currency_code), vendor_auth_cost_currency_code) auth_curr_code,
DECODE(NVL(gbl_claim_disposition_code,'REJECTED'), 'APPROVED', 'APPROVED', 'REJECTED') stat_code,
credit_reference_id,
DECODE(gbl_claim_disposition_code,'APPROVED',quantity_shipped , vendor_auth_quantity) vendor_auth_quantity,
clm.adjustment_type,
discount_type,
discount_value,
list_price,
agreement_price,
claim_amount_currency_code,
quantity_shipped,
gbl_claim_disposition_code line_status_code,
lines.claim_amount,
original_claim_amount,
lines.batch_line_id,
CASE
WHEN (lines.claim_amount_currency_code = l_batch_currency) THEN lines.BATCH_CURR_CLAIM_AMOUNT
WHEN ((lines.claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(lines.claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = lines.utilization_id
AND lines.batch_id = p_batch_number),
NULL,
sysdate,
lines.claim_amount)
WHEN (lines.claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(lines.claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = lines.utilization_id
AND lines.batch_id = p_batch_number),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = lines.utilization_id
AND lines.batch_id = p_batch_number),
lines.claim_amount)
END computed_batch_curr_claim_amt
FROM ozf_sd_batch_lines_int_all bint, ozf_sd_batch_lines_all lines, ozf_claim_types_all_vl clm
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N'
AND bint.ship_frm_sd_claim_request_id = lines.batch_id
AND bint.batch_line_number = lines.batch_line_number
AND lines.adjustment_type_id = clm.claim_type_id(+)
) ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'Procedure Starts');
SELECT batch_id, status_code, claim_minor_version, org_id, currency_code,NVL(imd_claim_flag,'N')
INTO l_batch_id, l_batch_status, l_claim_minor_version, l_org_id, l_batch_currency,l_imd_claim_flag
FROM ozf_sd_batch_headers_all
WHERE batch_number = p_batch_number;
UPDATE ozf_sd_batch_lines_int_all
SET validation_txt = 'Batch ID does not exist',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
'Batch Id: '||l_batch_id||' and Batch Status: '||l_batch_status);
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'E',
validation_txt = 'Batch not in WIP or SUBMITTED status',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
SELECT COUNT(1)
INTO l_cnt_line
FROM ozf_sd_batch_lines_int_all
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
UPDATE ozf_sd_batch_lines_int_all
SET validation_txt = 'There are no Lines for this Batch ID',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
SELECT gs.currency_code
INTO l_func_currency
FROM gl_sets_of_books gs,
ozf_sys_parameters_all org,
ozf_sd_batch_headers_all bh
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = bh.org_id
AND bh.batch_number = p_batch_number;
DELETE ozf_sd_batch_line_disputes
WHERE batch_id = l_batch_id
AND batch_line_id IN (SELECT bl.batch_line_id
FROM ozf_sd_batch_lines_all bl, ozf_sd_batch_lines_int_all intr
WHERE bl.batch_id = l_batch_id
AND bl.batch_id = intr.ship_frm_sd_claim_request_id
AND bl.batch_line_number = intr.batch_line_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
'Exception Delete OZF_SD_BATCH_LINE_DISPUTES: '||sqlerrm);
INSERT INTO ozf_sd_batch_line_disputes
(batch_line_dispute_id,
batch_id,
batch_line_id,
object_version_number,
dispute_code,
review_flag,
creation_date,
last_update_date,
last_updated_by,
request_id,
created_by,
created_from,
last_update_login,
program_application_id,
program_update_date,
program_id,
security_group_id)
SELECT ozf_sd_batch_line_disputes_s.nextval,
a.batch_id,
a.batch_line_id,
1,
a.dispute_code,
'N',
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id,
fnd_global.user_id,
NULL, --created from
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
NULL, --l_program_update_date,
fnd_global.conc_program_id,
fnd_global.security_group_id
FROM -- No rejection code for rejected line
( SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_MISSING_REJ_CODE' dispute_code
FROM ozf_sd_batch_lines_int_all intr,
ozf_sd_batch_lines_all bl
WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
AND intr.gbl_claim_disposition_code = 'REJECTED'
AND intr.ship_frm_sd_claim_request_id = bl.batch_id
AND intr.batch_line_number = bl.batch_line_number
AND intr.gbl_claim_rej_code1 IS NULL
AND intr.gbl_claim_rej_code2 IS NULL
AND intr.gbl_claim_rej_code3 IS NULL
AND intr.gbl_claim_rej_code4 IS NULL
AND intr.gbl_claim_rej_code5 IS NULL
AND intr.gbl_claim_rej_code6 IS NULL
AND intr.gbl_claim_rej_code7 IS NULL
AND intr.gbl_claim_rej_code8 IS NULL
AND intr.gbl_claim_rej_code9 IS NULL
AND intr.gbl_claim_rej_code10 IS NULL
UNION ALL -- missing or invalid status for batch lines
SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_NO_RESPONSE' dispute_code
FROM ozf_sd_batch_lines_int_all intr
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N'
AND ( (gbl_claim_disposition_code IS NULL) OR ( gbl_claim_disposition_code NOT IN ('APPROVED', 'REJECTED') ) )
UNION ALL -- Currency code mismatch
SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_CURR_CODE_MISMATCH' dispute_code
FROM ozf_sd_batch_lines_int_all intr,
ozf_sd_batch_lines_all bl
WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
AND intr.gbl_claim_disposition_code = 'REJECTED'
AND intr.ship_frm_sd_claim_request_id = bl.batch_id
AND intr.batch_line_number = bl.batch_line_number
AND bl.claim_amount_currency_code <> intr.vendor_auth_cost_currency_code
UNION ALL -- VENDOR_AUTH_COST_MONETARY_AMT CAN NOT BE NEGATIVE
SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_VENDOR_AUTH_AMT_NGTVE' dispute_code
FROM ozf_sd_batch_lines_int_all intr,
ozf_sd_batch_lines_all bl
WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
AND intr.gbl_claim_disposition_code = 'REJECTED'
AND intr.ship_frm_sd_claim_request_id = bl.batch_id
AND intr.batch_line_number = bl.batch_line_number
AND intr.vendor_auth_cost_monetary_amt < 0
UNION ALL -- VENDOR_AUTH_QTY CAN NOT BE NEGATIVE
SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_VENDOR_AUTH_QTY_NGTVE' dispute_code
FROM ozf_sd_batch_lines_int_all intr,
ozf_sd_batch_lines_all bl
WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
AND intr.gbl_claim_disposition_code = 'REJECTED'
AND intr.ship_frm_sd_claim_request_id = bl.batch_id
AND intr.batch_line_number = bl.batch_line_number
AND bl.original_claim_amount > 0 -- only for non RMA lines
AND intr.vendor_auth_quantity < 0
UNION ALL -- VENDOR_AUTH_COST_MONETARY_AMT is NULL and VENDOR_AUTH_QTY is NULL
SELECT intr.batch_id,
intr.batch_line_id,
'OZF_SD_AUTH_AMT_QTY_NULL' dispute_code
FROM ozf_sd_batch_lines_int_all intr,
ozf_sd_batch_lines_all bl
WHERE intr.ship_frm_sd_claim_request_id = p_batch_number
AND intr.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
AND intr.gbl_claim_disposition_code = 'REJECTED'
AND intr.ship_frm_sd_claim_request_id = bl.batch_id
AND intr.batch_line_number = bl.batch_line_number
AND intr.vendor_auth_cost_monetary_amt is NULL
AND intr.vendor_auth_quantity is NULL
) a ;
update_dispute_data(p_batch_number,l_batch_id);
SELECT SSD_DEC_ADJ_TYPE_ID, SSD_INC_ADJ_TYPE_ID
INTO l_ssd_dec_adj_type_id, l_ssd_inc_adj_type_id
FROM OZF_SYS_PARAMETERS_ALL
WHERE org_id = l_org_id;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' SSD Batch batch id'||l_batch_id||' allow immediate claim flag '||l_imd_claim_flag);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.batch_line_number'||V_MISS_REJ_APP_REC.batch_line_number);
UPDATE ozf_sd_batch_lines_all
SET status_code = V_MISS_REJ_APP_REC.stat_code,
approved_amount = V_MISS_REJ_APP_REC.auth_cost,
approved_currency_code = V_MISS_REJ_APP_REC.auth_curr_code,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
vendor_ref_id = V_MISS_REJ_APP_REC.credit_reference_id,
quantity_approved = V_MISS_REJ_APP_REC.vendor_auth_quantity,
total_approved_amt = CASE WHEN V_MISS_REJ_APP_REC.stat_code ='REJECTED' THEN NVL(total_approved_amt,0) + V_MISS_REJ_APP_REC.approved_claim_amount
WHEN V_MISS_REJ_APP_REC.stat_code ='APPROVED' THEN total_approved_amt + V_MISS_REJ_APP_REC.approved_claim_amount
END,
total_approved_qty = CASE WHEN V_MISS_REJ_APP_REC.stat_code ='REJECTED' THEN NVL(total_approved_qty,0) + V_MISS_REJ_APP_REC.vendor_auth_quantity
WHEN V_MISS_REJ_APP_REC.stat_code ='APPROVED' THEN total_approved_qty + V_MISS_REJ_APP_REC.vendor_auth_quantity
END,
batch_curr_claim_amount = decode(V_MISS_REJ_APP_REC.stat_code,'APPROVED',V_MISS_REJ_APP_REC.computed_batch_curr_claim_amt, batch_curr_claim_amount),
adjustment_type_id = (case when ( (original_claim_amount > 0)
AND (V_MISS_REJ_APP_REC.approved_claim_amount > original_claim_amount)
AND V_MISS_REJ_APP_REC.adjustment_type <> 'STANDARD'
AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
THEN l_ssd_inc_adj_type_id
when ( (original_claim_amount > 0)
AND (V_MISS_REJ_APP_REC.approved_claim_amount < original_claim_amount)
AND V_MISS_REJ_APP_REC.adjustment_type <> 'DECREASE_EARNED'
AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
THEN l_ssd_dec_adj_type_id
else
adjustment_type_id
end)
WHERE batch_id = l_batch_id
AND batch_line_number = V_MISS_REJ_APP_REC.batch_line_number
AND purge_flag <> 'Y'
AND status_code = 'SUBMITTED';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.stat_code'||V_MISS_REJ_APP_REC.stat_code);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.auth_cost'||V_MISS_REJ_APP_REC.auth_cost);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.vendor_auth_quantity'||V_MISS_REJ_APP_REC.vendor_auth_quantity);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.claim_amount_currency_code'||V_MISS_REJ_APP_REC.claim_amount_currency_code);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.auth_curr_code'||V_MISS_REJ_APP_REC.auth_curr_code);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.approved_claim_amount'||V_MISS_REJ_APP_REC.approved_claim_amount);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In For loop for V_MISS_REJ_APP_REC.claim_amount'||V_MISS_REJ_APP_REC.claim_amount);
SELECT COUNT(1)
INTO l_cnt_tot_line
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND purge_flag <> 'Y';
SELECT COUNT(1)
INTO l_cnt_approved_lines
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND status_code = 'APPROVED'
AND purge_flag <> 'Y';
SELECT COUNT(1)
INTO l_cnt_rejected_lines
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND status_code = 'REJECTED'
AND purge_flag <> 'Y';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of total lines: '||l_cnt_tot_line);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of rejected lines: '||l_cnt_rejected_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of approved lines: '||l_cnt_approved_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of completely rejected lines: '||l_cnt_comp_rej_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of completely approved lines: '||l_cnt_comp_appr_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of under approved lines: '||l_cnt_under_appr_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', 'number of over approved lines: '||l_cnt_over_appr_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block All lines are rejected or if none approved');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block All lines are over approved or completely approved');
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
claim_minor_version = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
-- Update status_code,claim amount,batch_curr_claim_amount and quantity_shipped for over approved lines
--For batch Currency Claim Amount 2 step Conversion i.e accrual date to sysdate is taken care by OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY API
UPDATE ozf_sd_batch_lines_all
SET status_code = 'APPROVED',
claim_amount = ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
sysdate,
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
END,
quantity_shipped = NVL(quantity_approved,quantity_shipped),
discount_value = decode(discount_type,
'AMT',NVL(approved_amount,discount_value),
'NEWPRICE',NVL(approved_amount,agreement_price),
'%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id
AND purge_flag <> 'Y' ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if block atleast one approved or under approved line');
UPDATE ozf_sd_batch_headers_all
SET status_code = 'WIP',
object_version_number = object_version_number + 1,
claim_minor_version = l_claim_minor_version + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
--Update the following fields for the under approved batch lines
-- batch_curr_claim_amount,agreement_price(for discount type %,New Price) and discount value for discount type amount,processed_flag
-- Status code of the under approved line has to be set as partially approved
IF((l_batch_und_appr_lines_tbl IS NOT NULL) AND (l_batch_und_appr_lines_tbl.COUNT > 0)) THEN
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if l_batch_und_appr_lines_tbl is not null ');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data', ' In if l_batch_und_appr_lines_tbl(i) '||l_batch_und_appr_lines_tbl(i));
UPDATE ozf_sd_batch_lines_all
SET status_code = 'PARTIALLY_APPROVED',
claim_amount = claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ( claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
sysdate,
claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
END,
quantity_shipped = CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END,
agreement_price = OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC( CASE WHEN ( discount_type ='%' OR discount_type = 'NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))
ELSE agreement_price END,AGREEMENT_CURRENCY_CODE),
discount_value = CASE WHEN ( discount_type ='AMT') THEN ( (claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END))
WHEN ( discount_type ='NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))
WHEN ( discount_type ='%') THEN ROUND((100 - (((LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))/NVL(list_price,1)) *100)),2)
ELSE discount_value
END,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END
WHERE batch_id = l_batch_id
AND batch_line_id = l_batch_und_appr_lines_tbl(i)
AND purge_flag <> 'Y';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'WIP',
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
claim_minor_version = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
claim_minor_version = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
--Update completely approved batch lines
IF((l_batch_comp_appr_lines_tbl IS NOT NULL) AND (l_batch_comp_appr_lines_tbl.COUNT > 0)) THEN
FOR i IN 0..l_batch_comp_appr_lines_tbl.COUNT-1
LOOP
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
'In completely approved lines if loop for batch line id '||l_batch_comp_appr_lines_tbl(i));
UPDATE ozf_sd_batch_lines_all
SET status_code = 'APPROVED',
claim_amount = ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
sysdate,
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
END,
quantity_shipped = NVL(quantity_approved,quantity_shipped),
discount_value = decode(discount_type,
'AMT',NVL(approved_amount,discount_value),
'NEWPRICE',NVL(approved_amount,agreement_price),
'%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END
WHERE batch_id = l_batch_id
AND batch_line_id = l_batch_comp_appr_lines_tbl(i)
AND purge_flag <> 'Y';
-- If there are over approved lines,update the status of the lines to approved,batch currency claim amount to current approved amount
IF((l_batch_over_appr_lines_tbl IS NOT NULL) AND (l_batch_over_appr_lines_tbl.COUNT > 0)) THEN
FOR i IN 0..l_batch_over_appr_lines_tbl.COUNT-1
LOOP
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::PROCESS_CHILD_BATCH',
'In over approved lines if loop for batch line id '||l_batch_over_appr_lines_tbl(i));
UPDATE ozf_sd_batch_lines_all
SET status_code = 'APPROVED',
claim_amount = ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
sysdate,
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
END,
quantity_shipped = NVL(quantity_approved,quantity_shipped),
discount_value = decode(discount_type,
'AMT',NVL(approved_amount,discount_value),
'NEWPRICE',NVL(approved_amount,agreement_price),
'%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END
WHERE batch_id = l_batch_id
AND batch_line_id = l_batch_over_appr_lines_tbl(i)
AND purge_flag <> 'Y';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'WIP',
object_version_number = object_version_number + 1,
claim_minor_version = l_claim_minor_version + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
--Update the following fields for the under approved batch lines
-- batch_curr_claim_amount,agreement_price(for discount type %,New Price) and discount value for discount type amount,processed_flag
-- Status code of the under approved line has to be set as partially approved
IF((l_batch_und_appr_lines_tbl IS NOT NULL) AND (l_batch_und_appr_lines_tbl.COUNT > 0)) THEN
FOR i IN 0..l_batch_und_appr_lines_tbl.COUNT-1
LOOP
UPDATE ozf_sd_batch_lines_all
SET status_code = 'PARTIALLY_APPROVED',
claim_amount = claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ( claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
sysdate,
claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = l_batch_id),
claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))
END,
quantity_shipped = CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END,
agreement_price =OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC( CASE WHEN ( discount_type ='%' OR discount_type = 'NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))
ELSE agreement_price END ,agreement_currency_code),
discount_value = CASE WHEN ( discount_type ='AMT') THEN ( (claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END))
WHEN ( discount_type ='NEWPRICE') THEN (LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))
WHEN ( discount_type ='%') THEN ROUND((100 - (((LIST_PRICE - ((claim_amount - ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE))/(CASE WHEN (quantity_shipped - NVL(quantity_approved,0) = 0) THEN quantity_shipped
ELSE quantity_shipped - NVL(quantity_approved,0)
END)))/NVL(list_price,1)) *100)),2)
ELSE discount_value
END,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END
WHERE batch_id = l_batch_id
AND batch_line_id = l_batch_und_appr_lines_tbl(i)
AND purge_flag <> 'Y';
UPDATE ozf_sd_batch_lines_all
SET status_code = V_MISS_REJ_APP_REC.stat_code,
approved_amount = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.auth_cost
when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') then V_MISS_REJ_APP_REC.auth_cost end),
approved_currency_code = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.auth_curr_code
when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') THEN V_MISS_REJ_APP_REC.auth_curr_code end),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
vendor_ref_id = V_MISS_REJ_APP_REC.credit_reference_id,
quantity_approved = (case when (original_claim_amount > 0) THEN V_MISS_REJ_APP_REC.vendor_auth_quantity
when (original_claim_amount < 0 AND V_MISS_REJ_APP_REC.line_status_code = 'APPROVED') THEN V_MISS_REJ_APP_REC.vendor_auth_quantity end),
batch_curr_claim_amount = decode(V_MISS_REJ_APP_REC.stat_code,'APPROVED',V_MISS_REJ_APP_REC.computed_batch_curr_claim_amt, batch_curr_claim_amount),
adjustment_type_id = (case when ( (original_claim_amount > 0)
AND (V_MISS_REJ_APP_REC.approved_claim_amount > original_claim_amount)
AND V_MISS_REJ_APP_REC.adjustment_type <> 'STANDARD'
AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
THEN l_ssd_inc_adj_type_id
when ( (original_claim_amount > 0)
AND (V_MISS_REJ_APP_REC.approved_claim_amount < original_claim_amount)
AND V_MISS_REJ_APP_REC.adjustment_type <> 'DECREASE_EARNED'
AND V_MISS_REJ_APP_REC.line_status_code<>'APPROVED' )
THEN l_ssd_dec_adj_type_id
else
adjustment_type_id
end)
WHERE batch_id = l_batch_id
AND batch_line_number = V_MISS_REJ_APP_REC.batch_line_number
AND purge_flag <> 'Y'
AND status_code = 'SUBMITTED';
SELECT COUNT(1)
INTO l_cnt_tot_line
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND purge_flag <> 'Y';
SELECT COUNT(1)
INTO l_cnt_approved_lines
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND status_code = 'APPROVED'
AND purge_flag <> 'Y';
SELECT COUNT(1)
INTO l_cnt_rejected_lines
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND status_code = 'REJECTED'
AND purge_flag <> 'Y';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of total lines: '||l_cnt_tot_line);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of rejected lines: '||l_cnt_rejected_lines);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'number of approved lines: '||l_cnt_approved_lines);
UPDATE ozf_sd_batch_headers_all
SET status_code = 'WIP',
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
claim_minor_version = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
UPDATE ozf_sd_batch_headers_all
SET status_code = 'WIP',
object_version_number = object_version_number + 1,
claim_minor_version = l_claim_minor_version + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_lines_int_all
SET processed_flag = 'Y',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N';
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_data',
'Entered throwing exception' || x_msg_data);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'Entered throwing unexpected exception');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data', 'Procedure Ends');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF EXCEPTION G_EXC_ERROR: '||x_msg_data);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF done:' || x_msg_data || '::::');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF EXCEPTION G_EXC_UNEXPECTED_ERROR');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF done G_EXC_UNEXPECTED_ERROR:' || x_msg_data || '::::');
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF EXCEPTION OTHERS' || sqlerrm);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT:update_data',
'OZF done OTHERS:' || x_msg_data ||'::::');
END update_data;
SELECT batch_number, vendor_id, vendor_site_id, org_id,
currency_code, batch_amount_threshold, batch_line_amount_threshold, claim_number, claim_minor_version,batch_type,owner_id,currency_code
INTO l_batch_number, l_vendor_id, l_vendor_site_id, l_org_id,
l_par_batch_curr_code, l_batch_threshold, l_line_threshold, l_claim_number, l_claim_minor_version,l_batch_type,l_par_batch_owner_id,l_batch_currency
FROM ozf_sd_batch_headers_all
WHERE batch_id = p_batch_id;
SELECT gs.currency_code
INTO l_func_currency
FROM gl_sets_of_books gs,
ozf_sys_parameters_all org,
ozf_sd_batch_headers_all bh
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = bh.org_id
AND bh.batch_id = p_batch_id;
UPDATE ozf_sd_batch_lines_all
SET batch_id = l_new_batch_id,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = p_batch_id
AND status_code = 'APPROVED'
AND purge_flag <> 'Y';
UPDATE ozf_sd_batch_lines_all
SET batch_id = l_new_batch_id,
status_code = 'APPROVED',
claim_amount = ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
NULL,
sysdate,
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
END,
quantity_shipped = NVL(quantity_approved,quantity_shipped),
discount_value = decode(discount_type,
'AMT',NVL(approved_amount,discount_value),
'NEWPRICE',NVL(approved_amount,agreement_price),
'%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(quantity_approved,quantity_shipped)
ELSE total_approved_qty
END,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = p_batch_id
AND batch_line_id = p_batch_comp_appr_lines_tbl(i)
AND purge_flag <> 'Y';
SELECT * INTO l_batch_line_tbl_rec
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_id
AND batch_line_id =p_batch_undr_appr_lines_tbl(i);
SELECT CASE
WHEN (l_batch_line_tbl_rec.claim_amount_currency_code = l_batch_currency)
THEN ozf_utility_pvt.currround(CASE
WHEN((l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved is null)
OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
ELSE 0
END,l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((l_batch_line_tbl_rec.claim_amount_currency_code = l_func_currency)
AND (l_func_currency <> l_batch_currency))
THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(l_batch_line_tbl_rec.claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu,
ozf_sd_batch_lines_all
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
NULL,
sysdate,
CASE WHEN((l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved is null)
OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
ELSE 0
END)
WHEN (l_batch_line_tbl_rec.claim_amount_currency_code <> l_func_currency)
THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(l_batch_line_tbl_rec.claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu,
ozf_sd_batch_lines_all
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu,
ozf_sd_batch_lines_all
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_line_id =l_batch_line_tbl_rec.batch_line_id),
CASE WHEN((l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved is null)
OR ( l_batch_line_tbl_rec.approved_currency_code <> l_batch_line_tbl_rec.CLAIM_AMOUNT_CURRENCY_CODE )
OR l_batch_line_tbl_rec.approved_amount < 0 OR (l_batch_line_tbl_rec.original_claim_amount > 0
and l_batch_line_tbl_rec.quantity_approved < 0) ) then 0
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price-l_batch_line_tbl_rec.agreement_price) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.approved_amount is null
and l_batch_line_tbl_rec.quantity_approved IS NOT NULL
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.discount_value * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN (l_batch_line_tbl_rec.approved_amount * l_batch_line_tbl_rec.QUANTITY_SHIPPED)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('NEWPRICE','%'))
THEN ((l_batch_line_tbl_rec.list_price - l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
WHEN (l_batch_line_tbl_rec.quantity_approved is not null
and l_batch_line_tbl_rec.approved_amount is not null
AND l_batch_line_tbl_rec.DISCOUNT_TYPE IN ('AMT'))
THEN ((l_batch_line_tbl_rec.approved_amount) * l_batch_line_tbl_rec.quantity_approved)
ELSE 0
END)
END INTO l_line_batch_curr_clm_amt from dual;
INSERT INTO OZF_SD_BATCH_LINES_ALL
(batch_line_id,
object_version_number,
batch_id,
batch_line_number,
utilization_id,
agreement_number,
ship_to_org_id,
ship_to_contact_id,
sold_to_customer_id,
sold_to_contact_id,
sold_to_site_use_id,
end_customer_id,
end_customer_contact_id,
order_header_id,
order_line_id,
invoice_number,
invoice_line_number,
resale_price_currency_code,
resales_price,
list_price_currency_code,
list_price,
agreement_currency_code,
agreement_price,
status_code,
claim_amount,
claim_amount_currency_code,
batch_curr_claim_amount,
original_claim_amount,
batch_curr_orig_claim_amount,
item_id,
vendor_item_id,
shipped_quantity_uom,
last_sub_claim_amount,
acctd_amount_remaining,
univ_curr_amount_remaining,
fund_request_amount_remaining,
amount_remaining,
quantity_shipped,
purge_flag,
order_date,
creation_date,
last_update_date,
last_updated_by,
request_id,
created_by,
last_update_login,
program_application_id,
program_update_date,
program_id,
org_id,
transmit_flag,
discount_type,
discount_value,
discount_currency_code,
adjustment_type_id,
order_source,
parent_line_id,
total_approved_amt,
total_approved_qty,
vendor_ref_id,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
process_feed_flag
)values
(OZF_SD_BATCH_LINES_ALL_S.NEXTVAL,
1,
l_new_batch_id,
l_batch_line_tbl_rec.batch_line_number,
l_batch_line_tbl_rec.utilization_id,
l_batch_line_tbl_rec.agreement_number,
l_batch_line_tbl_rec.ship_to_org_id,
l_batch_line_tbl_rec.ship_to_contact_id,
l_batch_line_tbl_rec.sold_to_customer_id,
l_batch_line_tbl_rec.sold_to_contact_id,
l_batch_line_tbl_rec.sold_to_site_use_id,
l_batch_line_tbl_rec.end_customer_id,
l_batch_line_tbl_rec.end_customer_contact_id,
l_batch_line_tbl_rec.order_header_id,
l_batch_line_tbl_rec.order_line_id,
l_batch_line_tbl_rec.invoice_number,
l_batch_line_tbl_rec.invoice_line_number,
l_batch_line_tbl_rec.resale_price_currency_code,
l_batch_line_tbl_rec.resales_price,
l_batch_line_tbl_rec.list_price_currency_code,
l_batch_line_tbl_rec.list_price,
l_batch_line_tbl_rec.agreement_currency_code,
decode(l_batch_line_tbl_rec.discount_type,
'%',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
'NEWPRICE',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
l_batch_line_tbl_rec.agreement_price),
'APPROVED',--Status code
l_line_clm_amt, -- Current approved Claim Amount
l_batch_line_tbl_rec.claim_amount_currency_code,
l_line_batch_curr_clm_amt, -- batch currency claim amount
l_line_clm_amt, -- original claim amount in request currency
l_line_batch_curr_clm_amt, --original claim amount in batch currency
l_batch_line_tbl_rec.item_id,
l_batch_line_tbl_rec.vendor_item_id,
l_batch_line_tbl_rec.shipped_quantity_uom,
l_batch_line_tbl_rec.last_sub_claim_amount,
l_batch_line_tbl_rec.acctd_amount_remaining,
l_batch_line_tbl_rec.univ_curr_amount_remaining,
l_batch_line_tbl_rec.fund_request_amount_remaining,
l_batch_line_tbl_rec.amount_remaining,
NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped), --shipped quantity
l_batch_line_tbl_rec.purge_flag,
l_batch_line_tbl_rec.order_date,
l_batch_line_tbl_rec.creation_date,
l_batch_line_tbl_rec.last_update_date,
l_batch_line_tbl_rec.last_updated_by,
l_batch_line_tbl_rec.request_id,
l_batch_line_tbl_rec.created_by,
l_batch_line_tbl_rec.last_update_login,
l_batch_line_tbl_rec.program_application_id,
l_batch_line_tbl_rec.program_update_date,
l_batch_line_tbl_rec.program_id,
l_batch_line_tbl_rec.org_id,
l_batch_line_tbl_rec.transmit_flag,
l_batch_line_tbl_rec.discount_type,
decode(l_batch_line_tbl_rec.discount_type,
'AMT',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.discount_value),
'NEWPRICE',NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price),
'%',ROUND((100 - ((NVL(l_batch_line_tbl_rec.approved_amount,l_batch_line_tbl_rec.agreement_price)/NVL(l_batch_line_tbl_rec.list_price,1)) *100)),2)
,l_batch_line_tbl_rec.discount_value),
l_batch_line_tbl_rec.discount_currency_code,
l_batch_line_tbl_rec.adjustment_type_id,--need to review
l_batch_line_tbl_rec.order_source,
l_batch_line_tbl_rec.batch_line_id,
l_line_clm_amt, -- Total approved amount
NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped),-- Total approved quantity
l_batch_line_tbl_rec.vendor_ref_id,
l_batch_line_tbl_rec.ATTRIBUTE_CATEGORY,
l_batch_line_tbl_rec.ATTRIBUTE1,
l_batch_line_tbl_rec.ATTRIBUTE2,
l_batch_line_tbl_rec.ATTRIBUTE3,
l_batch_line_tbl_rec.ATTRIBUTE4,
l_batch_line_tbl_rec.ATTRIBUTE5,
l_batch_line_tbl_rec.ATTRIBUTE6,
l_batch_line_tbl_rec.ATTRIBUTE7,
l_batch_line_tbl_rec.ATTRIBUTE8,
l_batch_line_tbl_rec.ATTRIBUTE9,
l_batch_line_tbl_rec.ATTRIBUTE10,
l_batch_line_tbl_rec.ATTRIBUTE11,
l_batch_line_tbl_rec.ATTRIBUTE12,
l_batch_line_tbl_rec.ATTRIBUTE13,
l_batch_line_tbl_rec.ATTRIBUTE14,
l_batch_line_tbl_rec.ATTRIBUTE15,
l_batch_line_tbl_rec.ATTRIBUTE16,
l_batch_line_tbl_rec.ATTRIBUTE17,
l_batch_line_tbl_rec.ATTRIBUTE18,
l_batch_line_tbl_rec.ATTRIBUTE19,
l_batch_line_tbl_rec.ATTRIBUTE20,
l_batch_line_tbl_rec.ATTRIBUTE21,
l_batch_line_tbl_rec.ATTRIBUTE22,
l_batch_line_tbl_rec.ATTRIBUTE23,
l_batch_line_tbl_rec.ATTRIBUTE24,
l_batch_line_tbl_rec.ATTRIBUTE25,
l_batch_line_tbl_rec.ATTRIBUTE26,
l_batch_line_tbl_rec.ATTRIBUTE27,
l_batch_line_tbl_rec.ATTRIBUTE28,
l_batch_line_tbl_rec.ATTRIBUTE29,
l_batch_line_tbl_rec.ATTRIBUTE30,
l_batch_line_tbl_rec.process_feed_flag
);
UPDATE ozf_sd_batch_lines_all
SET batch_id = l_new_batch_id,
status_code = 'APPROVED',
claim_amount = ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE) ,
batch_curr_claim_amount = CASE
WHEN (claim_amount_currency_code = l_batch_currency) THEN ozf_utility_pvt.currround(CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
ELSE 0
END,CLAIM_AMOUNT_CURRENCY_CODE)
WHEN ((claim_amount_currency_code = l_func_currency) AND (l_func_currency <> l_batch_currency)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
NULL,
sysdate,
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
WHEN (claim_amount_currency_code <> l_func_currency) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(claim_amount_currency_code,
l_batch_currency,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = ozf_sd_batch_lines_all.utilization_id
AND ozf_sd_batch_lines_all.batch_id = p_batch_id),
CASE
WHEN((approved_amount is null and quantity_approved is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR (original_claim_amount > 0 and quantity_approved < 0) ) then 0
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) * quantity_approved)
WHEN (approved_amount is null and quantity_approved IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN (discount_value * quantity_approved)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * QUANTITY_SHIPPED)
WHEN (quantity_approved is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount * QUANTITY_SHIPPED)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) * quantity_approved)
WHEN (quantity_approved is not null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN ((approved_amount) * quantity_approved)
END)
END,
quantity_shipped = NVL(quantity_approved,quantity_shipped),
discount_value = decode(discount_type,
'AMT',NVL(approved_amount,discount_value),
'NEWPRICE',NVL(approved_amount,agreement_price),
'%',ROUND((100 - ((NVL(approved_amount,agreement_price)/NVL(list_price,1)) *100)),2),discount_value),
agreement_price = decode(discount_type,'%',NVL(approved_amount,agreement_price),'NEWPRICE',NVL(approved_amount,agreement_price),agreement_price),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
total_approved_qty = CASE WHEN total_approved_amt IS NOT NULL AND total_approved_qty IS NULL THEN NVL(l_batch_line_tbl_rec.quantity_approved,l_batch_line_tbl_rec.quantity_shipped)
ELSE total_approved_qty
END
WHERE batch_id = p_batch_id
AND batch_line_id = p_batch_over_appr_lines_tbl(i)
AND purge_flag <> 'Y';
SELECT batch_number, vendor_id, vendor_site_id, org_id,
currency_code, batch_amount_threshold, batch_line_amount_threshold, claim_number, claim_minor_version,batch_type,owner_id
INTO l_batch_number, l_vendor_id, l_vendor_site_id, l_org_id,
l_par_batch_curr_code, l_batch_threshold, l_line_threshold, l_claim_number, l_claim_minor_version,l_batch_type,l_par_batch_owner_id
FROM ozf_sd_batch_headers_all
WHERE batch_id = p_batch_id;
UPDATE ozf_sd_batch_lines_all
SET batch_id = l_new_batch_id,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = p_batch_id
AND status_code = 'APPROVED'
AND purge_flag <> 'Y';
select sum(batch_curr_claim_amount)
INTO l_tot_app_claim_amt
from ozf_sd_batch_lines_all
where batch_id=l_new_batch_id ;
UPDATE ozf_sd_batch_headers_all
SET claim_number = l_claim_number
WHERE batch_id = l_new_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'CLOSED',
claim_id = l_claim_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = l_new_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'PENDING_CLAIM',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = l_new_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'PENDING_CLAIM',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = l_new_batch_id;
select sum(batch_curr_claim_amount) INTO l_tot_app_claim_amt from ozf_sd_batch_lines_all
where batch_id=p_batch_id ;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
' l_tot_app_claim_amt = ' || l_tot_app_claim_amt);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
' l_custom_setup_id' || to_char(l_custom_setup_id));
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
' Negative Claim Number = ' || l_claim_number);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::UPDATE_DATA',
' l_new_batch_id = ' || p_batch_id);
UPDATE ozf_sd_batch_headers_all
SET claim_number = l_claim_number
WHERE batch_id = p_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'CLOSED',
claim_id = l_claim_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = p_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'PENDING_CLAIM',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = p_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'PENDING_CLAIM',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE batch_id = p_batch_id;
PROCEDURE update_dispute_data(p_batch_number varchar2,p_batch_id number) IS
type v_disputes IS TABLE OF VARCHAR2(50) INDEX BY binary_integer;
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
'Procedure Starts: Batch number -> '||p_batch_number||' Batch Id -> '||p_batch_id );
FOR v_dispute_code IN (SELECT bl.batch_id,
bl.batch_line_id,
bint.gbl_claim_rej_code1,
bint.gbl_claim_rej_code2,
bint.gbl_claim_rej_code3,
bint.gbl_claim_rej_code4,
bint.gbl_claim_rej_code5,
bint.gbl_claim_rej_code6,
bint.gbl_claim_rej_code7,
bint.gbl_claim_rej_code8,
bint.gbl_claim_rej_code9,
bint.gbl_claim_rej_code10
FROM ozf_sd_batch_lines_all bl,
ozf_sd_batch_lines_int_all bint
WHERE bl.batch_id = bint.ship_frm_sd_claim_request_id
AND bl.batch_line_number = bint.batch_line_number
AND bint.GBL_CLAIM_DISPOSITION_CODE = 'REJECTED'
AND bint.ship_frm_sd_claim_request_id = p_batch_number
AND bint.processed_flag = 'N'
AND bl.purge_flag <> 'Y'
)
LOOP
l_dispute_index := 0;
INSERT INTO ozf_sd_batch_line_disputes
(batch_line_dispute_id,
batch_id,
batch_line_id,
object_version_number,
dispute_code,
review_flag,
creation_date,
last_update_date,
last_updated_by,
request_id,
created_by,
created_from,
last_update_login,
program_application_id,
program_update_date,
program_id,
security_group_id)
VALUES
(ozf_sd_batch_line_disputes_s.nextval,
v_dispute_code.batch_id,
v_dispute_code.batch_line_id,
1,
l_disputes(i),
NULL, --review flag
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id,
fnd_global.user_id,
NULL, --created from
fnd_global.conc_login_id,
fnd_global.prog_appl_id, --l_program_application_id,
NULL, --l_program_update_date,
fnd_global.conc_program_id, -- p_Operating_Unit,
fnd_global.security_group_id);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_dispute_data',
'Procedure Ends');
END update_dispute_data;