[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
INTO l_result
FROM fii_change_log
WHERE log_item = 'AP_RESUMMARIZE';
SELECT 1
INTO l_count1
FROM fii_ap_inv_dist_f
WHERE ROWNUM = 1;
SELECT 1
INTO l_count2
FROM fii_ap_inv_dist_t
WHERE ROWNUM = 1;
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'AP_RESUMMARIZE';
g_state := 'Inserting records into FII_AP_INV_ID table from AP log table';
INSERT INTO FII_AP_INV_ID (INVOICE_ID, SEQUENCE_ID)
SELECT
invoice_id INVOICE_ID,
rownum SEQUENCE_ID
FROM
(SELECT distinct Invoice_ID
FROM (SELECT Key_Value1 INVOICE_ID
FROM ap_dbi_log log
WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')
AND Creation_Date >= g_timestamp2
AND Creation_Date < g_timestamp1
UNION
SELECT Key_Value1_ID Invoice_ID
FROM FII_AP_DBI_Log_Exp_T
WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')));
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT||' records in FII_AP_INV_ID');
INSERT INTO FII_AP_INV_DIST_T
(ACCOUNT_DATE,
INV_CURRENCY_CODE,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
INVOICE_TYPE,
COMPANY_ID,
COST_CENTER_ID,
PO_DISTRIBUTION_ID,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
POSTED_FLAG,
APPROVED_FLAG,
ORG_ID,
LINE_TYPE_LOOKUP_CODE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
SELECT /*+ ORDERED USE_NL(temp ai aid gsob glcc)*/
trunc(aid.accounting_date) ACCOUNT_DATE,
GSOB.Currency_Code INV_CURRENCY_CODE,
aid.invoice_id INVOICE_ID,
aid.invoice_distribution_id INVOICE_DISTRIBUTION_ID,
NVL(aid.base_amount, aid.amount) AMOUNT_B,
decode(aid.po_distribution_id, Null, 'N', 'Y') PO_MATCHED_FLAG,
ai.source SOURCE,
nvl(fnd.EMPLOYEE_ID,-1) INV_DIST_CREATED_BY,
NVL(ai.vendor_site_id, -1) SUPPLIER_SITE_ID,
trunc(aid.creation_date) INV_DIST_CREATION_DATE,
ai.vendor_id SUPPLIER_ID,
ai.invoice_type_lookup_code INVOICE_TYPE,
glcc.company_id,
glcc.cost_center_id,
aid.po_distribution_id,
decode(gsob.currency_code, g_prim_currency, 1,
fii_currency.get_global_rate_primary( gsob.currency_code,
trunc(least(aid.accounting_date,sysdate)))) PRIM_CONVERSION_RATE,
decode(gsob.currency_code, g_sec_currency, 1,
fii_currency.get_global_rate_secondary( gsob.currency_code,
trunc(least(aid.accounting_date,sysdate)))) SEC_CONVERSION_RATE,
NVL(aid.posted_flag, 'N') POSTED_FLAG,
NVL(aid.match_status_flag, 'N') APPROVED_FLAG,
NVL(ai.org_id, -1) ORG_ID,
aid.LINE_TYPE_LOOKUP_CODE,
AIL.Inventory_Item_ID,
AIL.Purchasing_Category_ID,
AIL.Item_Description
FROM fii_ap_inv_id temp,
ap_invoices_all ai,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
gl_ledgers_public_v gsob,
fii_gl_ccid_dimensions glcc,
fnd_user fnd,
fii_slg_assignments slga,
fii_source_ledger_groups fslg
WHERE ai.invoice_id = temp.invoice_id
AND ai.invoice_id = aid.invoice_id
and ai.invoice_id = ail.invoice_id
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
AND aid.set_of_books_id = gsob.ledger_id
AND aid.dist_code_combination_id=glcc.code_combination_id
AND glcc.chart_of_accounts_id = slga.chart_of_accounts_id
AND ( glcc.company_id = slga.bal_seg_value_id
OR slga.bal_seg_value_id = -1 )
AND aid.set_of_books_id = slga.ledger_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = g_usage_code
AND fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
AND temp.sequence_id >= p_start_range
AND temp.sequence_id <= p_end_range
AND aid.accounting_date >= g_start_date;
FII_UTIL.put_line('Inserted '||sql%rowcount||' rows INTO FII_AP_INV_DIST_T table');
SELECT max(sequence_id), min(sequence_id)
INTO l_max_number, l_start_number
FROM FII_AP_INV_ID;
INSERT INTO FII_AP_SUM_WORK_JOBS (
start_range,
end_range,
worker_number,
status)
VALUES (
l_start_number,
least(l_end_number, l_max_number),
0,
'UNASSIGNED');
FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_AP_SUM_WORK_JOBS table');
SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(prim_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(ACCOUNT_DATE, sysdate)) actg_dt
FROM FII_AP_INV_DIST_T stg
WHERE stg.prim_conversion_rate < 0 ;
SELECT DISTINCT INV_CURRENCY_CODE from_currency, decode(sec_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'),LEAST(ACCOUNT_DATE, sysdate)) actg_dt
FROM FII_AP_INV_DIST_T stg
WHERE stg.sec_conversion_rate < 0 ;
SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(prim_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
FROM FII_AP_INV_RATES_TEMP rates
WHERE rates.prim_conversion_rate < 0 ;
SELECT DISTINCT FUNCTIONAL_CURRENCY from_currency, decode(sec_conversion_rate,-3,
to_date('01/01/1999','MM/DD/RRRR'), LEAST(TRX_DATE, sysdate)) actg_dt
FROM FII_AP_INV_RATES_TEMP rates
WHERE rates.sec_conversion_rate < 0 ;
SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_RATES_TEMP rates WHERE rates.prim_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_RATES_TEMP rates WHERE rates.sec_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_prim FROM FII_AP_INV_DIST_T stg WHERE stg.prim_conversion_rate < 0;
SELECT 1 INTO l_miss_rates_sec FROM FII_AP_INV_DIST_T stg WHERE stg.sec_conversion_rate < 0;
SELECT FII_AP_INV_DIST_F_S.nextval INTO seq_id FROM dual;
USING (SELECT *
FROM FII_AP_INV_DIST_T
WHERE (prim_conversion_rate > 0 OR sec_conversion_rate > 0)
) stg
ON (bsum.INVOICE_DISTRIBUTION_ID = stg.INVOICE_DISTRIBUTION_ID)
WHEN MATCHED THEN UPDATE SET bsum.account_date = stg.account_date,
bsum.Inv_Currency_Code = stg.Inv_Currency_Code,
bsum.amount_b = stg.amount_b,
bsum.PO_Matched_Flag = stg.PO_Matched_Flag,
bsum.Source = stg.Source,
bsum.supplier_site_id = stg.supplier_site_id,
bsum.supplier_id = stg.supplier_id,
bsum.Invoice_Type = stg.Invoice_Type,
bsum.company_id = stg.company_id,
bsum.cost_center_id = stg.cost_center_id,
bsum.po_distribution_id = stg.po_distribution_id,
bsum.prim_amount_g = round((stg.amount_b*stg.prim_conversion_rate)/to_number(g_primary_mau))*
to_number(g_primary_mau),
bsum.sec_amount_g = round((stg.amount_b*stg.sec_conversion_rate)/to_number(g_secondary_mau))*
to_number(g_secondary_mau),
bsum.UPDATE_SEQUENCE = seq_id,
bsum.Posted_Flag = stg.Posted_Flag,
bsum.approved_flag = stg.approved_flag,
bsum.Org_ID = stg.Org_ID,
bsum.LAST_UPDATE_DATE = sysdate,
bsum.LAST_UPDATED_BY = g_fii_user_id,
bsum.LAST_UPDATE_LOGIN = g_fii_login_id,
bsum.dist_count = 1,
bsum.LINE_TYPE_LOOKUP_CODE = stg.LINE_TYPE_LOOKUP_CODE,
bsum.Inventory_Item_ID = stg.Inventory_Item_ID,
bsum.Purchasing_Category_ID = stg.Purchasing_Category_ID,
bsum.Item_Description = stg.Item_Description
WHEN NOT MATCHED THEN INSERT(bsum.ACCOUNT_DATE,
bsum.Inv_Currency_Code,
bsum.Invoice_ID,
bsum.AMOUNT_B,
bsum.PO_MATCHED_FLAG,
bsum.SOURCE,
bsum.INV_DIST_CREATED_BY,
bsum.SUPPLIER_SITE_ID,
bsum.INV_DIST_CREATION_DATE,
bsum.SUPPLIER_ID,
bsum.INVOICE_TYPE,
bsum.COMPANY_ID,
bsum.COST_CENTER_ID,
bsum.PO_DISTRIBUTION_ID,
bsum.Invoice_Distribution_ID,
bsum.PRIM_AMOUNT_G,
bsum.SEC_AMOUNT_G,
bsum.UPDATE_SEQUENCE,
bsum.Posted_Flag,
bsum.APPROVED_FLAG,
bsum.ORG_ID,
bsum.last_update_date,
bsum.last_updated_by,
bsum.creation_date,
bsum.created_by,
bsum.last_update_login,
bsum.dist_count,
bsum.LINE_TYPE_LOOKUP_CODE,
bsum.Inventory_Item_ID,
bsum.Purchasing_Category_ID,
bsum.Item_Description)
VALUES
(stg.ACCOUNT_DATE,
stg.Inv_Currency_Code,
stg.Invoice_ID,
stg.AMOUNT_B,
stg.PO_MATCHED_FLAG,
stg.SOURCE,
stg.INV_DIST_CREATED_BY,
stg.SUPPLIER_SITE_ID,
stg.INV_DIST_CREATION_DATE,
stg.SUPPLIER_ID,
stg.INVOICE_TYPE,
stg.COMPANY_ID,
stg.COST_CENTER_ID,
stg.PO_DISTRIBUTION_ID,
stg.Invoice_Distribution_ID,
round((stg.amount_b*stg.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
round((stg.amount_b*stg.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
seq_id,
stg.Posted_Flag,
stg.APPROVED_FLAG,
stg.org_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_user_id,
g_fii_login_id,
1,
stg.LINE_TYPE_LOOKUP_CODE,
stg.Inventory_Item_ID,
stg.Purchasing_Category_ID,
stg.Item_Description);
DELETE FROM FII_AP_INV_DIST_F bsum
WHERE (bsum.UPDATE_SEQUENCE <> seq_id OR bsum.posted_flag = 'Y')
AND bsum.invoice_id IN (SELECT id.invoice_id FROM FII_AP_INV_ID id);
FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_AP_INV_DIST_F');
INSERT INTO FII_AP_DBI_LOG_PS_T(
Table_Name,
Operation_Flag,
Key_Value1_ID,
Key_Value2_ID,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date)
SELECT Table_Name,
Operation_Flag,
Key_Value1,
Key_Value2,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date
FROM AP_DBI_LOG
WHERE Partition_ID = i
AND Creation_Date >= g_timestamp2
AND Creation_Date < g_timestamp1;
SELECT tablespace_name
INTO g_tablespace
FROM all_tables
WHERE table_name = g_table_name
AND owner = g_fii_schema;
select user_conversion_type into g_prim_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_prim_rate_type;
select user_conversion_type into g_sec_rate_type_name
from gl_daily_conversion_types
where conversion_type = g_sec_rate_type;
FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update
program');
g_phase := 'Calling CCID Dimension update program';
PROCEDURE INSERT_INTO_STG (p_parallel_query IN NUMBER,
p_sort_area_size IN NUMBER,
p_hash_area_size IN NUMBER) IS
l_stmt VARCHAR2(1000);
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_DIST_T F
(ACCOUNT_DATE,
INV_CURRENCY_CODE,
INVOICE_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
INVOICE_TYPE,
COMPANY_ID,
COST_CENTER_ID,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE,
Posted_Flag,
Approved_Flag,
ORG_ID,
INVOICE_DISTRIBUTION_ID,
PO_DISTRIBUTION_ID,
LINE_TYPE_LOOKUP_CODE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
select /*+ leading(v) no_expand full(aid) use_hash(aid, ai, fnd)
parallel(ai) parallel(fnd) parallel(aid)
pq_distribute(aid hash, hash) pq_distribute(ai hash, hash)
pq_distribute(fnd hash, hash) */
nvl(trunc(aid.accounting_date),trunc(aid.accounting_date)) account_date,
V.Currency_Code Inv_Currency_Code,
aid.invoice_id invoice_id,
nvl(aid.base_amount, aid.amount) amount_b,
nvl2(aid.po_distribution_id, 'Y', 'N') po_matched_flag,
ai.source source,
nvl(fnd.employee_id, -1) inv_dist_created_by,
nvl(ai.vendor_site_id, -1) supplier_site_id,
trunc(aid.creation_date) inv_dist_creation_date,
ai.vendor_id supplier_id,
ai.invoice_type_lookup_code invoice_type,
v.company_id company_id,
v.cost_center_id cost_center_id,
-1 prim_conversion_rate,
-1 sec_conversion_rate,
nvl(aid.posted_flag, 'N') posted_flag,
nvl(aid.match_status_flag, 'N') approved_flag,
nvl(ai.org_id, -1) org_id,
aid.invoice_distribution_id invoice_distribution_id,
aid.po_distribution_id po_distribution_id,
aid.line_type_lookup_code line_type_lookup_code,
ail.Inventory_Item_ID Inventory_Item_ID,
ail.Purchasing_Category_ID Purchasing_Category_ID,
ail.Item_Description Item_Description
from (
select /*+ no_merge no_expand parallel(glcc) */
gsob.ledger_id, glcc.code_combination_id,
gsob.currency_code, glcc.natural_account_id,
glcc.company_id, glcc.cost_center_id,
gsob.chart_of_accounts_id, glcc.user_dim1_id,
glcc.user_dim2_id
from fii_source_ledger_groups fslg, fii_slg_assignments slga,
gl_ledgers_public_v gsob, fii_gl_ccid_dimensions glcc
where slga.chart_of_accounts_id = glcc.chart_of_accounts_id
and slga.bal_seg_value_id in (glcc.company_id, -1)
and slga.source_ledger_group_id = fslg.source_ledger_group_id
and slga.ledger_id = gsob.ledger_id
and fslg.usage_code = g_usage_code) v,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail,
ap_invoices_all ai,
fnd_user fnd
where ai.invoice_id = aid.invoice_id
and ai.invoice_id = ail.invoice_id
AND AID.Invoice_ID = AIL.Invoice_ID
AND AID.Invoice_Line_Number = AIL.Line_Number
and nvl(aid.posted_flag, 'N') = 'N'
and fnd.user_id = nvl(aid.created_by, ai.cancelled_by)
and aid.set_of_books_id = v.ledger_id
and aid.dist_code_combination_id = v.code_combination_id
and aid.accounting_date >= g_start_date;
END INSERT_INTO_STG;
PROCEDURE INSERT_RATES IS
BEGIN
g_state := 'Loading data into rates table';
insert into fii_ap_inv_rates_temp
(FUNCTIONAL_CURRENCY,
TRX_DATE,
PRIM_CONVERSION_RATE,
SEC_CONVERSION_RATE)
select cc functional_currency,
dt trx_date,
decode(cc, g_prim_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(dt,sysdate))) PRIM_CONVERSION_RATE,
decode(cc, g_sec_currency, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(dt,sysdate))) SEC_CONVERSION_RATE
from (
select /*+ no_merge */ distinct
inv_currency_code cc,
account_date dt
from FII_AP_INV_DIST_T
);
END INSERT_RATES;
PROCEDURE INSERT_INTO_SUMMARY (p_parallel_query IN NUMBER) IS
l_stmt VARCHAR2(50);
SELECT FII_AP_INV_DIST_F_S.nextval INTO seq_id FROM dual;
INSERT /*+ APPEND PARALLEL(F) */ INTO FII_AP_INV_DIST_F F (
ACCOUNT_DATE,
INV_CURRENCY_CODE,
INVOICE_ID,
INVOICE_DISTRIBUTION_ID,
AMOUNT_B,
PO_MATCHED_FLAG,
SOURCE,
INV_DIST_CREATED_BY,
SUPPLIER_SITE_ID,
INV_DIST_CREATION_DATE,
SUPPLIER_ID,
INVOICE_TYPE,
COMPANY_ID,
COST_CENTER_ID,
PO_DISTRIBUTION_ID,
PRIM_AMOUNT_G,
SEC_AMOUNT_G,
UPDATE_SEQUENCE,
POSTED_FLAG,
APPROVED_FLAG,
ORG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DIST_COUNT,
LINE_TYPE_LOOKUP_CODE,
INVENTORY_ITEM_ID,
PURCHASING_CATEGORY_ID,
ITEM_DESCRIPTION)
SELECT /*+ PARALLEL(stg) PARALLEL(rates) */
stg.account_date,
stg.Inv_Currency_Code,
stg.invoice_id,
stg.invoice_distribution_id,
stg.amount_b amount_b,
stg.po_matched_flag,
stg.source,
stg.inv_dist_created_by,
stg.supplier_site_id,
stg.inv_dist_creation_date,
stg.supplier_id,
stg.invoice_type,
stg.company_id,
stg.cost_center_id,
stg.po_distribution_id,
round((stg.amount_b*rates.prim_conversion_rate)/to_number(g_primary_mau))*to_number(g_primary_mau),
round((stg.amount_b*rates.sec_conversion_rate)/to_number(g_secondary_mau))*to_number(g_secondary_mau),
seq_id,
stg.Posted_Flag,
stg.approved_flag,
stg.org_id,
sysdate,
g_fii_user_id,
sysdate,
g_fii_login_id,
g_fii_login_id,
1,
stg.LINE_TYPE_LOOKUP_CODE,
stg.Inventory_Item_ID,
stg.Purchasing_Category_ID,
stg.Item_Description
FROM FII_AP_INV_DIST_T stg, fii_ap_inv_rates_temp rates
where stg.account_date = rates.trx_date
and stg.inv_currency_code = rates.functional_currency;
INSERT INTO FII_AP_DBI_LOG_PS_T(
Table_Name,
Operation_Flag,
Key_Value1_ID,
Key_Value2_ID,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date)
SELECT Table_Name,
Operation_Flag,
Key_Value1,
Key_Value2,
Created_By,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Last_Update_Date
FROM AP_DBI_LOG
WHERE Partition_ID = i
AND Creation_Date >= g_timestamp2
AND Creation_Date < g_timestamp1;
END INSERT_INTO_SUMMARY;
UPDATE fii_change_log
SET item_value = 'N',
last_update_date = SYSDATE,
last_update_login = g_fii_login_id,
last_updated_by = g_fii_user_id
WHERE log_item = 'AP_RESUMMARIZE';
SELECT 1 INTO g_num FROM fii_ap_inv_dist_t where rownum = 1;
INSERT_INTO_STG (NVL(p_parallel_query, 1),
NVL(p_sort_area_size, 200000000),
NVL(p_hash_area_size, 200000000));
INSERT_RATES;
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'COMPLETED',1,0)),0),
nvl(sum(decode(status,'IN PROCESS',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
count(*)
INTO l_unassigned_cnt,
l_completed_cnt,
l_wip_cnt,
l_failed_cnt,
l_tot_cnt
FROM FII_AP_SUM_WORK_JOBS;
UPDATE FII_AP_INV_DIST_T stg
SET stg.PRIM_CONVERSION_RATE = DECODE(stg.inv_currency_code, g_prim_currency, 1,
fii_currency.get_global_rate_primary(stg.inv_currency_code,
least(stg.account_date,sysdate)))
WHERE stg.PRIM_CONVERSION_RATE < 0;
FII_UTIL.put_line('Updated ' || l_rowcount || ' records for primary conversion rate');
UPDATE FII_AP_INV_DIST_T stg
SET stg.SEC_CONVERSION_RATE = decode(stg.inv_currency_code, g_sec_currency, 1,
fii_currency.get_global_rate_secondary(stg.inv_currency_code,
least( stg.account_date,sysdate)))
WHERE stg.SEC_CONVERSION_RATE < 0;
FII_UTIL.put_line('Updated ' || l_rowcount || ' records for secondary conversion rate');
-- Initial Mode. This program will also update AP_DBI_LOG
------------------------------------------------------------
INSERT_INTO_SUMMARY(p_parallel_query);
SELECT nvl(sum(decode(status,'UNASSIGNED',1,0)),0),
nvl(sum(decode(status,'FAILED',1,0)),0),
nvl(sum(decode(status,'UNASSIGNED',1, 0)),0),
nvl(sum(decode(status,'COMPLETED', 1, 0)),0),
count(*)
INTO l_unassigned_cnt,
l_failed_cnt,
l_curr_unasgn_cnt,
l_curr_comp_cnt,
l_curr_tot_cnt
FROM FII_AP_SUM_WORK_JOBS;
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'IN PROCESS',
worker_number = p_worker_no
WHERE status = 'UNASSIGNED'
AND rownum < 2;
SELECT start_range,
end_range
INTO l_start_range,
l_end_range
FROM FII_AP_SUM_WORK_JOBS jobs
WHERE jobs.worker_number = p_worker_no
AND jobs.status = 'IN PROCESS';
UPDATE FII_AP_SUM_WORK_JOBS jobs
SET jobs.status = 'COMPLETED'
WHERE jobs.status = 'IN PROCESS'
AND jobs.worker_number = p_worker_no;
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';
UPDATE FII_AP_SUM_WORK_JOBS
SET status = 'FAILED'
WHERE worker_number = p_worker_no
AND status = 'IN PROCESS';