[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,
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)
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_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);
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;
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
INTO l_batch_id, l_batch_status
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';
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);
FOR v_vendor_ref IN (SELECT batch_line_number, credit_reference_id
FROM ozf_sd_batch_lines_int_all
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N')
LOOP
UPDATE ozf_sd_batch_lines_all
SET vendor_ref_id = v_vendor_ref.credit_reference_id
WHERE batch_id = l_batch_id
AND batch_line_number = v_vendor_ref.batch_line_number
AND purge_flag <> 'Y';
FOR v_batch_lines IN (SELECT batch_id, batch_line_number, batch_line_id, status_code
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id
AND purge_flag <> 'Y')
LOOP
IF v_batch_lines.status_code <> 'SUBMITTED' THEN
UPDATE ozf_sd_batch_lines_int_all
SET validation_txt = 'Batch Line not in SUBMITTED status',
GBL_CLAIM_REJ_CODE10 = 'OZF_SD_DATA_ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE ship_frm_sd_claim_request_id = v_batch_lines.batch_id
AND batch_line_number = v_batch_lines.batch_line_number
AND processed_flag = 'N';
FOR v_missing_invalid_sts_lines IN (SELECT batch_line_number,
vendor_auth_cost_monetary_amt, vendor_auth_cost_currency_code
FROM ozf_sd_batch_lines_int_all
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') ) )
)
LOOP
UPDATE ozf_sd_batch_lines_int_all
SET gbl_claim_rej_code10 = 'OZF_SD_NO_RESPONSE',
validation_txt = 'STATUS CODE IS EITHER MISSING OR NOT IN APPROVED/REJECTED',
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 = v_missing_invalid_sts_lines.batch_line_number;
UPDATE ozf_sd_batch_lines_all
SET status_code = 'REJECTED',
approved_amount = v_missing_invalid_sts_lines.vendor_auth_cost_monetary_amt,
approved_currency_code = v_missing_invalid_sts_lines.vendor_auth_cost_currency_code,
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 batch_line_number = v_missing_invalid_sts_lines.batch_line_number
AND purge_flag <> 'Y' ;
FOR v_reject_lines IN (SELECT batch_line_number, vendor_auth_cost_monetary_amt,
vendor_auth_cost_currency_code
FROM ozf_sd_batch_lines_int_all
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N'
AND gbl_claim_disposition_code = 'REJECTED')
LOOP
UPDATE ozf_sd_batch_lines_all
SET status_code = 'REJECTED',
approved_amount = v_reject_lines.vendor_auth_cost_monetary_amt,
approved_currency_code = v_reject_lines.vendor_auth_cost_currency_code,
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 batch_line_number = v_reject_lines.batch_line_number
AND purge_flag <> 'Y';
FOR v_accepted_lines IN (SELECT batch_line_number
FROM ozf_sd_batch_lines_int_all
WHERE ship_frm_sd_claim_request_id = p_batch_number
AND processed_flag = 'N'
AND gbl_claim_disposition_code = 'APPROVED')
LOOP
UPDATE ozf_sd_batch_lines_all
SET status_code = 'APPROVED',
approved_amount = agreement_price,
approved_currency_code = agreement_currency_code,
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 batch_line_number = v_accepted_lines.batch_line_number
AND purge_flag <> 'Y';
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_rejected_line_data(p_batch_number, l_batch_id, l_batch_id, x_return_status , x_msg_data );
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 -- claim_number = substr(claim_number,1,instr(claim_number,'_')-1) ,
claim_minor_version = NULL
where batch_id = l_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
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_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
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
FROM ozf_sd_batch_headers_all
WHERE batch_id = p_batch_id;
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
-- child_batch_id = l_new_batch_id
WHERE batch_id = p_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED',
parent_batch_id = p_batch_id,
claim_number = l_claim_number||'_'||l_claim_minor_version,
claim_minor_version = l_claim_minor_version,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = l_new_batch_id ;
update_rejected_line_data(l_batch_number, p_batch_id, l_new_batch_id, x_return_status, x_msg_data);
FOR v_update_batch IN (SELECT batch_id, batch_line_number, batch_line_id
FROM ozf_sd_batch_lines_all
WHERE batch_id = p_batch_id
AND status_code = 'APPROVED'
AND purge_flag <> 'Y')
LOOP
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_line_number = v_update_batch.batch_line_number AND
batch_id = v_update_batch.batch_id;
PROCEDURE update_dispute_data(p_batch_number varchar2,p_batch_id number,new_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|| ' New Batch Id -> '||new_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 bl.status_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;
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;
PROCEDURE update_rejected_line_data(p_batch_number VARCHAR2,
p_batch_id NUMBER,
new_batch_id NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_data OUT nocopy VARCHAR2) IS
l_currency_code VARCHAR2(15);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_rejected_line_data',
'Procedure Starts: Batch number -> '||p_batch_number||' Batch Id -> '||p_batch_id|| ' New Batch Id -> '||new_batch_id);
FOR v_rejected_lines IN (SELECT intr.*
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) LOOP
IF v_rejected_lines.gbl_claim_rej_code1 IS NULL AND
v_rejected_lines.gbl_claim_rej_code2 IS NULL AND
v_rejected_lines.gbl_claim_rej_code3 IS NULL AND
v_rejected_lines.gbl_claim_rej_code4 IS NULL AND
v_rejected_lines.gbl_claim_rej_code5 IS NULL AND
v_rejected_lines.gbl_claim_rej_code6 IS NULL AND
v_rejected_lines.gbl_claim_rej_code7 IS NULL AND
v_rejected_lines.gbl_claim_rej_code8 IS NULL AND
v_rejected_lines.gbl_claim_rej_code9 IS NULL AND
v_rejected_lines.gbl_claim_rej_code10 IS NULL THEN
UPDATE ozf_sd_batch_lines_int_all
SET gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
validation_txt = 'REJECTION CODE MISSING',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_line_number = v_rejected_lines.batch_line_number
AND ship_frm_sd_claim_request_id = p_batch_number;
SELECT claim_amount_currency_code
INTO l_currency_code
FROM ozf_sd_batch_lines_all
WHERE batch_id = p_batch_id
AND batch_line_number = v_rejected_lines.batch_line_number;
UPDATE ozf_sd_batch_lines_int_all
SET validation_txt = 'CURRENCY CODE NOT MATCHING',
gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
gbl_claim_disposition_code = 'REJECTED',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_line_number = v_rejected_lines.batch_line_number AND
ship_frm_sd_claim_request_id = p_batch_number;
UPDATE ozf_sd_batch_lines_int_all
SET validation_txt = 'VENDOR_AUTH_COST_MONETARY_AMT CAN NOT BE NEGATIVE',
gbl_claim_rej_code10 = 'OZF_SD_DATA_ERROR',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_line_number = v_rejected_lines.batch_line_number AND
ship_frm_sd_claim_request_id = p_batch_number;
UPDATE ozf_sd_batch_lines_all
SET approved_amount = v_rejected_lines.vendor_auth_cost_monetary_amt,
approved_currency_code = v_rejected_lines.vendor_auth_cost_currency_code,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_line_number = v_rejected_lines.batch_line_number AND
batch_id = p_batch_id;
update_dispute_data(p_batch_number,p_batch_id,new_batch_id);
ozf_utility_pvt.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'OZF_SD_BATCH_FEED_PVT::update_rejected_line_data',
'Procedure Ends');
END update_rejected_line_data;