[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT OZF_SD_BATCH_HEADERS_ALL.Creation_Date
INTO l_conv_date
FROM OZF_SD_BATCH_HEADERS_ALL
WHERE batch_id = p_batch_id;
SELECT code.external_code
INTO l_vendor_product_id
FROM OZF_SUPP_CODE_CONVERSIONS_ALL code, OZF_SUPP_TRD_PRFLS_ALL prf
WHERE internal_code = l_internal_code and
code.supp_trade_profile_id = prf.supp_trade_profile_id and
prf.supplier_site_id = p_supplier_site_id and
trunc(sysdate) between code.start_date_active and
nvl(code.end_date_active, sysdate + 1);
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
SELECT sites.vendor_site_id
FROM ap_supplier_sites_all sites,
ozf_supp_trd_prfls_all trprf
WHERE sites.vendor_id = c_vendor_id AND
sites.org_id = c_org_id AND
nvl(sites.inactive_date, sysdate) >= trunc(sysdate) AND
trprf.cust_account_id is not null AND
sites.vendor_id=trprf.supplier_id AND
sites.vendor_site_id=trprf.supplier_site_id;
SELECT claim_currency_code
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = c_supplier_site_id AND
org_id = c_org_id;
SELECT claim_frequency, claim_frequency_unit, last_paid_date
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = c_supplier_site_id AND
org_id = c_org_id;
Select vendor_name
into l_supplier_name
From ap_suppliers
Where vendor_id = l_supplier_id;
Select vendor_site_code
into l_sup_site_name
From ap_supplier_sites_all
Where vendor_site_id = l_supplier_site_id;
l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
' WHERE supplier_id=' || p_supplier_id ||
' AND supplier_site_id=' || p_supplier_site_id;
l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
' WHERE supplier_site_id =' || p_supplier_site_id;
SELECT add_months(l_last_run_date, l_freq)
INTO l_last_run_date
FROM dual;
SELECT add_months(l_last_run_date, l_freq * 12)
INTO l_last_run_date
FROM dual;
UPDATE ozf_supp_trd_prfls_all
SET last_paid_date = sysdate
WHERE supplier_site_id = l_supplier_site_id;
l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
' WHERE supplier_site_id=' || site_rec.vendor_site_id;
l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
' WHERE supplier_site_id =' || site_rec.vendor_site_id;
SELECT add_months(l_last_run_date, l_freq)
INTO l_last_run_date
FROM dual;
SELECT add_months(l_last_run_date, l_freq * 12)
INTO l_last_run_date
FROM dual;
UPDATE ozf_supp_trd_prfls_all
SET last_paid_date = sysdate
WHERE supplier_site_id = l_supplier_site_id;
SELECT min_claim_amt, min_claim_amt_line_lvl, auto_debit
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = c_supplier_site_id;
SELECT sum(batch_curr_claim_amount)
INTO l_batch_sum
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id;
UPDATE_AMOUNTS(l_batch_id, l_batch_threshold);
UPDATE ozf_sd_batch_headers_all
SET claim_number = l_claim_number
WHERE batch_id = l_batch_id;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'APPROVED'
WHERE batch_id = l_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_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_batch_id;
'Claim process returned errors, could not update batch with ID :' || l_batch_id);
SELECT cont.vendor_contact_id,
decode(cont.last_name,null,null,'','',cont.last_name || ', ') || nvl(cont.middle_name, '')|| ' '|| cont.first_name fullname,
cont.email_address,
decode(cont.phone ,NULL, NULL, cont.area_code || '-' || cont.phone) phone,
decode(cont.fax,NULL, NULL, cont.fax_area_code || '-' || cont.fax) fax
FROM PO_VENDOR_CONTACTS cont
WHERE cont.vendor_site_id = c_supplier_site_id
AND NVL(inactive_date, sysdate+1) > sysdate;
select SSD_IMD_CLAIM_FLAG
from OZF_SUPP_TRD_PRFLS_ALL
where supplier_site_id = c_supplier_site_id
and org_id = c_org_id;
select SSD_IMD_CLAIM_FLAG
from OZF_SYS_PARAMETERS_ALL
where org_id = c_org_id;
SELECT ozf_sd_batch_headers_all_s.nextval INTO l_batch_id FROM dual;
INSERT INTO ozf_sd_batch_headers_all
(batch_id,
object_version_number,
batch_number,
claim_number,
claim_minor_version,
vendor_id,
vendor_site_id,
vendor_contact_id,
vendor_contact_name,
vendor_email,
vendor_phone,
vendor_fax,
batch_line_amount_threshold,
batch_amount_threshold,
currency_code,
credit_code,
status_code,
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,
transfer_type,
org_id,
parent_batch_id,
batch_type,
owner_id,
imd_claim_flag)
VALUES
(l_batch_id,
1,
l_batch_id,
l_claim_number,
l_claim_minor_version,
l_supplier_id, --supplier_party_id
l_supplier_site_id, --supplier site
l_supplier_contact_id,
l_supplier_contact_name,
l_supplier_contact_email,
l_supplier_contact_phone,
l_supplier_contact_fax,
l_batch_threshold, -- From TP
l_line_threshold, -- From TP
l_batch_currency, -- From TP
'D', -- Value can be Debit or Credit. defaulted to Credit
p_batch_status, --NEW for new batch and APPROVED for child batch
sysdate,
sysdate,
FND_GLOBAL.USER_ID, --las_updated_by
FND_GLOBAL.CONC_REQUEST_ID, --? conc program id
FND_GLOBAL.USER_ID, --created by
null, --created from --??
FND_GLOBAL.CONC_LOGIN_ID, -- last_update_login
FND_GLOBAL.PROG_APPL_ID, -- program app id
sysdate,
FND_GLOBAL.CONC_PROGRAM_ID, --program id
null, --l_transfer_type to be updated when batch is exported
l_org_id, --default batch Org ID
p_parent_batch_id,
p_batch_type,
NVL(p_owner,OZF_UTILITY_PVT.get_resource_id(FND_GLOBAL.USER_ID)),
l_ssd_imd_claim_flag -- added for ER 13245462
);
SELECT start_date, end_date
FROM gl_periods
WHERE period_name = c_period and
period_set_name =
fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
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;
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
AND CTLA.LINE_TYPE = ''LINE''
AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
AND ROWNUM = 1) TRX_NUMBER,');
FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_LINE_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
AND CTLA.LINE_TYPE = ''LINE''
AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
AND ROWNUM = 1) LINE_NUMBER, ');
FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: start time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
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
)
VALUES
(l_batch_line_id,
1,
l_batch_id,
l_batch_line_number,
l_utilization_id,
l_agreement_number,
l_ship_to_org_id,
l_ship_to_contact_id,
l_sold_to_customer_id,
l_SOLD_TO_CONTACT_ID,
l_SOLD_TO_SITE_USE_ID,
l_end_customer_id,
l_end_customer_contact_id,
l_order_header_id,
l_order_line_number,
l_invoice_number,
l_invoice_line_number,
l_resale_price_currency_code, -- from orders
l_resales_price,
l_list_price_currency_code, --purchase price from sdr
l_list_price,
l_agreement_currency_code, --agreement price from sdr
l_agreement_price,
'NEW',
l_claim_amount, --claim amount from funds accrual
l_claim_amount_currency_code,
l_batch_curr_claim_amount,
l_claim_amount, -- for original_claim_amount
l_batch_curr_claim_amount, -- for batch_curr_orig_claim_amount
l_item_id,
get_vendor_item_id(l_item_id, p_supplier_site_id),
l_shipped_quantity_uom,
null,
l_acct_amount_remaining,
l_univ_curr_amount_remaining,
l_fund_req_amount_remaining,
l_amount_remaining,
l_quantity_shipped,
'N', -- l_active_flag
l_order_date, -- from OE order lines/header
sysdate, --l_creation_date,
sysdate, --l_last_update_date,
FND_GLOBAL.USER_ID, --l_last_updated_by,
FND_GLOBAL.CONC_REQUEST_ID, --l_request_id,
FND_GLOBAL.USER_ID, --l_created_by,
--l_created_from,
FND_GLOBAL.CONC_LOGIN_ID, --l_last_update_login,
FND_GLOBAL.PROG_APPL_ID, --l_program_application_id,
null, --l_program_update_date,
FND_GLOBAL.CONC_PROGRAM_ID, --l_program_id,
l_inv_org_id,
'Y',
l_approved_discount_type,
l_approved_discount_value,
l_approved_discount_currency,
l_adjustment_type_id ,
p_order_source
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: end time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted INTO ozf_sd_batch_lines_all');
SELECT start_date, end_date
FROM gl_periods
WHERE period_name = c_period and
period_set_name =
fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
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;
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: start time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
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
)
VALUES
(l_batch_line_id,
1,
l_batch_id,
l_batch_line_number,
l_utilization_id,
l_agreement_number,
l_ship_to_org_id,
l_ship_to_contact_id,
l_sold_to_customer_id,
l_SOLD_TO_CONTACT_ID,
l_SOLD_TO_SITE_USE_ID,
l_end_customer_id,
l_end_customer_contact_id,
l_order_header_id,
l_order_line_number,
l_invoice_number,
l_invoice_line_number,
l_resale_price_currency_code, -- from orders
l_resales_price,
l_list_price_currency_code, --purchase price from sdr
l_list_price,
l_agreement_currency_code, --agreement price from sdr
l_agreement_price,
'NEW',
l_claim_amount, --claim amount from funds accrual
l_claim_amount_currency_code,
l_batch_curr_claim_amount,
l_claim_amount, -- for original_claim_amount
l_batch_curr_claim_amount, -- for batch_curr_orig_claim_amount
l_item_id,
get_vendor_item_id(l_item_id, p_supplier_site_id),
l_shipped_quantity_uom,
null,
l_acct_amount_remaining,
l_univ_curr_amount_remaining,
l_fund_req_amount_remaining,
l_amount_remaining,
l_quantity_shipped,
'N', -- l_active_flag
l_order_date, -- from OE order lines/header
sysdate, --l_creation_date,
sysdate, --l_last_update_date,
FND_GLOBAL.USER_ID, --l_last_updated_by,
FND_GLOBAL.CONC_REQUEST_ID, --l_request_id,
FND_GLOBAL.USER_ID, --l_created_by,
--l_created_from,
FND_GLOBAL.CONC_LOGIN_ID, --l_last_update_login,
FND_GLOBAL.PROG_APPL_ID, --l_program_application_id,
null, --l_program_update_date,
FND_GLOBAL.CONC_PROGRAM_ID, --l_program_id,
l_inv_org_id,
'Y',
l_approved_discount_type,
l_approved_discount_value,
l_approved_discount_currency,
l_adjustment_type_id ,
p_order_source
);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: end time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted INTO ozf_sd_batch_lines_all');
PROCEDURE UPDATE_AMOUNTS(p_batch_id IN NUMBER,
p_batch_threshold IN NUMBER) is
l_batch_id NUMBER;
'--- Start of UPDATE_AMOUNTS ---');
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = 0,
acctd_amount_remaining = 0,
plan_curr_amount_remaining = 0,
univ_curr_amount_remaining = 0,
fund_request_amount_remaining = 0,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
object_version_number = object_version_number + 1
WHERE utilization_id in
(SELECT utilization_id
FROM ozf_sd_batch_lines_all
WHERE batch_id = l_batch_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in UPDATE_AMOUNTS:' || SQLERRM);
END UPDATE_AMOUNTS;
SELECT days_before_claiming_debit
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = c_supplier_site_id;
l_sql := 'SELECT HDR.BATCH_ID, HDR.vendor_site_id, HDR.BATCH_SUBMISSION_DATE '
|| ' FROM ozf_sd_batch_headers_all HDR, ozf_sd_batch_lines_all BLN '
|| ' WHERE HDR.batch_id = BLN.batch_id'
|| ' AND HDR.status_code = ''SUBMITTED'' ';
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_batch_id;
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
SELECT sites.vendor_id, sites.vendor_site_id
FROM ap_supplier_sites_all sites,
ozf_supp_trd_prfls_all trprf
WHERE sites.org_id = NVL(c_org_id,sites.org_id) AND
sites.vendor_id = NVL(c_vendor_id,sites.vendor_id) AND
sites.vendor_site_id = NVL(c_vendor_site_id,sites.vendor_site_id) AND
nvl(sites.inactive_date, sysdate) >= trunc(sysdate) AND
trprf.cust_account_id is not null AND
sites.vendor_id=trprf.supplier_id AND
sites.vendor_site_id=trprf.supplier_site_id;
SELECT HDR.BATCH_ID batch_id, HDR.CURRENCY_CODE currency_code
FROM ozf_sd_batch_headers_all HDR , ozf_sd_batch_lines_all BLN
WHERE HDR.batch_id = BLN.batch_id
AND HDR.status_code = 'PENDING_CLAIM'
AND HDR.org_id = NVL(c_org_id,HDR.org_id)
AND HDR.vendor_id = NVL(c_vendor_id,HDR.vendor_id)
AND HDR.vendor_site_id = NVL(c_vendor_site_id,HDR.vendor_site_id)
AND HDR.batch_id = NVL(c_batch_id,HDR.batch_id)
GROUP BY HDR.BATCH_ID, HDR.CURRENCY_CODE
HAVING sum(BLN.batch_curr_claim_amount) > 0;
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 = batch_rec.BATCH_ID;
UPDATE ozf_sd_batch_lines_all BLN
SET batch_curr_claim_amount = (
CASE
WHEN ((BLN.claim_amount_currency_code = l_func_currency) AND (l_func_currency <> batch_rec.currency_code)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(BLN.claim_amount_currency_code,
batch_rec.currency_code,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = BLN.utilization_id
AND BLN.batch_id = batch_rec.batch_id),
NULL,
sysdate,
BLN.CLAIM_AMOUNT)
WHEN ((BLN.claim_amount_currency_code <> l_func_currency) AND (l_func_currency <> batch_rec.currency_code)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(BLN.claim_amount_currency_code,
batch_rec.currency_code,
l_func_currency,
(SELECT fu.exchange_rate_type
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = BLN.utilization_id
AND BLN.batch_id = batch_rec.batch_id),
NULL,
(SELECT fu.exchange_rate_date
FROM ozf_funds_utilized_all_b fu
WHERE fu.utilization_id = BLN.utilization_id
AND BLN.batch_id = batch_rec.batch_id),
BLN.CLAIM_AMOUNT)
END
),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE batch_id = batch_rec.batch_id
AND batch_line_number IN (SELECT BLN.BATCH_LINE_NUMBER
FROM ozf_sd_batch_lines_all BLN,
ozf_funds_utilized_all_b FU
WHERE BLN.batch_id = batch_rec.BATCH_ID
AND BLN.utilization_id = FU.utilization_id
AND ((batch_rec.currency_code <> BLN.claim_amount_currency_code) AND (batch_rec.currency_code <> l_func_currency))
);
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 = batch_rec.BATCH_ID
AND status_code = 'PENDING_CLAIM';
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 = batch_rec.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 = batch_rec.BATCH_ID;