[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
gs.currency_code
FROM
gl_sets_of_books gs,
ozf_sys_parameters_all os
WHERE
os.set_of_books_id = gs.set_of_books_id
AND os.org_id = p_org_id;
select rpad('Offer Name',40, ' ') ||
rpad('Status',10, ' ') ||
rpad('Transaction Currency',20, ' ') ||
rpad('Total Records',20, ' ') ||
rpad('Total Amount',20, ' ') ||
rpad('Fund Request Currency',20, ' ') column_val
from DUAL;
SELECT rpad(qpl.description,40, ' ')||
rpad(off.status_code,10, ' ')||
rpad(utiz.plan_currency_code,20, ' ')||
rpad(count(utiz.utilization_id) ,20, ' ')||
rpad(sum(utiz.fund_request_amount),20, ' ')||
rpad(utiz.fund_request_currency_code ,20, ' '), utiz.plan_id
FROM
ozf_offers off,
qp_list_headers_all qpl,
ozf_funds_utilized_all_b utiz
WHERE
off.qp_list_header_id = utiz.plan_id
AND off.transaction_currency_code IS NULL
AND off.fund_request_curr_code <> utiz.plan_currency_code
AND off.QP_LIST_HEADER_ID = qpl.list_header_id
AND NVL(utiz.plan_curr_amount,0) <> 0
AND plan_type='OFFR'
AND utiz.last_updated_by = -2
GROUP BY
qpl.description,
off.status_code,
utiz.fund_request_currency_code,
utiz.plan_currency_code,
utiz.plan_id;
SELECT 1
FROM OZF_MULTI_CURR_UTIL_BCK;
/*Update all 4 new columns. This will get executed only for the first time due to the where clause
1) fund_request_amount
2) fund_request_amount_remaining
3) fund_request_currency_code
4) plan_currency_code*/
UPDATE /* PARALLEL */ ozf_funds_utilized_all_b
SET fund_request_amount = plan_curr_amount,
fund_request_amount_remaining = plan_curr_amount_remaining,
fund_request_currency_code = DECODE(plan_type,'OFFR', (SELECT NVL(transaction_currency_code,fund_request_curr_code)
FROM ozf_offers
WHERE qp_list_header_id=plan_id)
,'CAMP', (SELECT transaction_currency_code
FROM ams_campaigns_vl
WHERE campaign_id = plan_id)
,'CSCH', (SELECT transaction_currency_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = plan_id)
,'DELV', (SELECT transaction_currency_code
FROM ams_deliverables_vl
WHERE deliverable_id = plan_id)
,'EVEH', (SELECT currency_code_tc
FROM ams_event_headers_vl
WHERE event_header_id = plan_id)
,'EVEO', (SELECT currency_code_tc
FROM ams_event_offers_vl
WHERE event_offer_id = plan_id)
,'PRIC',(SELECT currency_code
FROM qp_list_headers_all
WHERE list_header_id = plan_id)
),
plan_currency_code = DECODE(plan_type,'OFFR',
DECODE(object_type,'ORDER', (SELECT header.transactional_curr_code
FROM oe_order_headers_all header
WHERE header.header_id = object_id)
,'TP_ORDER', (SELECT line.currency_code
FROM ozf_resale_lines_all line
WHERE line.resale_line_id = object_id)
,'INVOICE', (SELECT invoice_currency_code
FROM ra_customer_trx_all
WHERE customer_trx_id = object_id)
,'PCHO', (SELECT currency_code
FROM po_headers_all
WHERE po_header_id = object_id)
,'CM', (SELECT invoice_currency_code
FROM ra_customer_trx_all
WHERE customer_trx_id = object_id)
,'DM', (SELECT invoice_currency_code
FROM ra_customer_trx_all
WHERE customer_trx_id = object_id)
,(SELECT NVL(transaction_currency_code,fund_request_curr_code)
FROM ozf_offers
WHERE qp_list_header_id=plan_id)
)
,'CAMP', (SELECT transaction_currency_code
FROM ams_campaigns_vl
WHERE campaign_id = plan_id)
,'CSCH', (SELECT transaction_currency_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = plan_id)
,'DELV', (SELECT transaction_currency_code
FROM ams_deliverables_vl
WHERE deliverable_id = plan_id)
,'EVEH', (SELECT currency_code_tc
FROM ams_event_headers_vl
WHERE event_header_id = plan_id)
,'EVEO',(SELECT currency_code_tc
FROM ams_event_offers_vl
WHERE event_offer_id = plan_id)
,'PRIC',(SELECT line.currency_code
FROM ozf_resale_lines_all line
WHERE line.resale_line_id = object_id)
),
last_updated_by = -2,
last_update_date = sysdate
WHERE fund_request_amount IS NULL
AND fund_request_amount_remaining IS NULL
AND fund_request_currency_code IS NULL
AND plan_currency_code IS NULL;
ozf_utility_pvt.write_conc_log (' <===> mandatory columns are updated for <===>'||l_row_count||' rows updated');
/* Check if any rows are updated by the first sql. If Yes (first time when conc. prog is run), then generate the
report and re-calculate the 3 columns for which the definition has been modified
1) plan_curr_amount
2) plan_curr_amount_remaining
3) exchange_rate */
IF l_row_count > 0 THEN
OPEN c_report_header;
INSERT INTO OZF_MULTI_CURR_UTIL_BCK (SELECT utilization_id,
plan_curr_amount,
plan_curr_amount_remaining,
exchange_rate
FROM ozf_funds_utilized_all_b
WHERE NVL(plan_curr_amount,0) <> 0
AND plan_currency_code <> (SELECT fund_request_curr_code FROM ozf_offers WHERE qp_list_header_id=plan_id AND transaction_currency_code IS NULL)
AND plan_type='OFFR'
AND last_updated_by = -2);
ozf_utility_pvt.write_conc_log (' <===> Backup Completed <===>'||sql%rowcount||' rows inserted');
UPDATE /* PARALLEL */ ozf_funds_utilized_all_b
SET plan_curr_amount = gl_currency_api.convert_closest_amount_sql(fund_request_currency_code,plan_currency_code,
exchange_rate_date,exchange_rate_type,NULL,fund_request_amount,-1),
plan_curr_amount_remaining = gl_currency_api.convert_closest_amount_sql(fund_request_currency_code,plan_currency_code,
exchange_rate_date,exchange_rate_type,NULL,fund_request_amount_remaining,-1),
exchange_rate = gl_currency_api.get_closest_rate(plan_currency_code,get_functional_curr(org_id),exchange_rate_date,exchange_rate_type,0),
last_updated_by = -2,
last_update_date = sysdate
WHERE plan_currency_code <> (SELECT fund_request_curr_code FROM ozf_offers WHERE qp_list_header_id=plan_id AND transaction_currency_code IS NULL)
AND NVL(plan_curr_amount,0) <> 0
AND plan_type='OFFR'
AND last_updated_by = -2;
ozf_utility_pvt.write_conc_log (' <===> Update for plan_curr_amount/remaining exchange_rate Completed <===>'||sql%rowcount||' rows updated');