[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 OZF_FUNDS_UTILIZED_ALL_B.PLAN_CURR_AMOUNT_REMAINING,
OZF_OFFERS.TRANSACTION_CURRENCY_CODE
INTO l_from_amount, l_from_currency
FROM OZF_FUNDS_UTILIZED_ALL_B, OZF_OFFERS
WHERE utilization_id = p_UTILIZATION_ID and
plan_id = OZF_OFFERS.QP_LIST_HEADER_ID;
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 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;
'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 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)
VALUES
(l_batch_id,
1,
l_batch_id,
l_claim_number,
1,
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
'NEW', --default status for new batches
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
);
SELECT start_date, end_date
FROM gl_periods
WHERE period_name = c_period and
period_set_name =
fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
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_ATTRIBUTE1 = TO_CHAR(OEH.ORDER_NUMBER)
AND CTLA.SALES_ORDER_LINE = TO_CHAR(OEL.LINE_NUMBER)
AND CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
AND CTLA.LINE_TYPE = ''LINE''
AND ROWNUM = 1) TRX_NUMBER,');
FND_DSQL.add_text('(SELECT CTLA.SALES_ORDER_LINE FROM RA_CUSTOMER_TRX_LINES_ALL CTLA WHERE CTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(OEH.ORDER_NUMBER)
AND CTLA.SALES_ORDER_LINE = TO_CHAR(OEL.LINE_NUMBER)
AND CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
AND CTLA.LINE_TYPE = ''LINE''
AND ROWNUM = 1) LINE_NUMBER, ');
(SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DEN_HRCHY_PARENTS_V P,
ENI_PROD_DENORM_HRCHY_V H,
MTL_SYSTEM_ITEMS_B_KFV B
WHERE P.CATEGORY_ID = MIC.CATEGORY_ID AND
MIC.ORGANIZATION_ID = B.ORGANIZATION_ID AND
P.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID AND
MIC.CATEGORY_SET_ID = H.CATEGORY_SET_ID AND
MIC.CATEGORY_ID = H.CHILD_ID AND
(P.DISABLE_DATE is null OR P.DISABLE_DATE > SYSDATE)
AND
H.PARENT_ID = RL.prod_catg_id )) AND ');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
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,
item_id,
vendor_item_id,
shipped_quantity_uom,
last_sub_claim_amount,
acctd_amount_remaining,
univ_curr_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
)
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_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_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');
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,
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;