[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select UTILIZATION_ID,CLAIM_AMOUNT_CURRENCY_CODE into l_util_id,l_from_currency from OZF_SD_BATCH_LINES_ALL where batch_line_id=p_batch_line_id;
select currency_code into l_to_currency from ozf_sd_batch_headers_all
where batch_id=(select batch_id from ozf_sd_batch_lines_all
where batch_line_id = p_batch_line_id);
select UTILIZATION_ID ,claim_amount,acctd_amount_remaining,univ_curr_amount_remaining,fund_request_amount_remaining,amount_remaining
into l_util_id, l_claim_amount ,l_acctd_amount_remaining,l_univ_curr_amount_remaining,l_fund_req_amount_remaining,l_amount_remaining
from OZF_SD_BATCH_LINES_ALL
where batch_line_id=p_batch_line_id;
UPDATE OZF_FUNDS_UTILIZED_ALL_B
SET amount_remaining=amount_remaining+l_amount_remaining,
PLAN_CURR_AMOUNT_REMAINING=PLAN_CURR_AMOUNT_REMAINING+l_claim_amount,
acctd_amount_remaining=acctd_amount_remaining+l_acctd_amount_remaining,
univ_curr_amount_remaining=univ_curr_amount_remaining+l_amount_remaining,
fund_request_amount_remaining=fund_request_amount_remaining+l_fund_req_amount_remaining
WHERE UTILIZATION_ID=l_util_id;
DELETE FROM OZF_SD_BATCH_LINES_ALL WHERE batch_line_id=p_batch_line_id;
PROCEDURE UPDATE_SD_REQ_PRICES(p_request_number IN VARCHAR2,p_request_line_id IN NUMBER)
IS
l_req_header_id NUMBER ;
UPDATE OZF_SD_RES_DIST_PRICES_INTF SET REQUEST_LINE_ID=p_request_line_id
WHERE REQUEST_NUMBER=p_request_number
AND REQUEST_LINE_ID IS NULL;
END UPDATE_SD_REQ_PRICES;
SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
PRODINTF.PROD_TYPE INF_PROD_TYPE,
PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
PROD.CONCATENATED_SEGMENTS LINE_PROD_CODE,
PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
FROM OZF_SD_RES_PROD_INTF PRODINTF,
OZF_SD_REQUEST_LINES_ALL PRODLINES,
mtl_system_items_b_kfv PROD
where NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
PRODINTF.REQUEST_NUMBER=p_request_number
AND PRODLINES.REQUEST_HEADER_ID=request_header_id
AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
AND PRODLINES.PRODUCT_CONTEXT = 'PRODUCT'
AND PRODLINES.ORG_ID=PROD.ORGANIZATION_ID
AND PRODLINES.INVENTORY_ITEM_ID=PROD.INVENTORY_ITEM_ID
UNION
SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
PRODINTF.PROD_TYPE INF_PROD_TYPE,
PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
NVL(D.CATEGORY_DESC, 'NA') PROD_CODE,
PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
FROM OZF_SD_RES_PROD_INTF PRODINTF,
OZF_SD_REQUEST_LINES_ALL PRODLINES,
ENI_PROD_DEN_HRCHY_PARENTS_V D
WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
PRODINTF.REQUEST_NUMBER=p_request_number
AND PRODLINES.REQUEST_HEADER_ID=request_header_id
AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
AND PRODLINES.PRODUCT_CONTEXT ='PRODUCT_CATEGORY'
AND PRODLINES.PROD_CATG_ID=D.CATEGORY_ID
AND PRODLINES.PRODUCT_CAT_SET_ID = D.CATEGORY_SET_ID
UNION
SELECT PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
PRODINTF.PROD_TYPE INF_PROD_TYPE,
PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
'ALL' PROD_CODE,
PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
FROM OZF_SD_RES_PROD_INTF PRODINTF,
OZF_SD_REQUEST_LINES_ALL PRODLINES
WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
PRODINTF.REQUEST_NUMBER=p_request_number
AND PRODLINES.REQUEST_HEADER_ID=request_header_id
AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
AND PRODLINES.PRODUCT_CONTEXT ='ALL_ITEMS';
Select count(*) into l_req_number_count from OZF_SD_REQUEST_HEADERS_ALL_B
where REQUEST_NUMBER = p_request_number ;
-- DO THE MASS UPDATE FOR THE INTERFACE TABLES
UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;
Select REQ.request_header_id,STATUS.SYSTEM_STATUS_CODE,REQ.request_currency_code into l_req_id,l_req_status,l_curr_code
from OZF_SD_REQUEST_HEADERS_ALL_B REQ,AMS_USER_STATUSES_VL STATUS
where REQ.REQUEST_NUMBER = p_request_number
AND REQ.USER_STATUS_ID=STATUS.USER_STATUS_ID ;
-- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
WHERE REQUEST_HEADER_ID=request_header_id
AND REQUEST_LINE_ID IN ( SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
WHERE REQUEST_HEADER_ID=request_header_id
MINUS
SELECT REQUEST_LINE_ID FROM OZF_SD_RES_PROD_INTF
WHERE REQUEST_NUMBER=p_request_number
AND NVL(PROCESSED_FLAG,'N') <>'Y') ;
UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
AND NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Discount type mismatch'
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
SELECT count(*) INTO l_currency_count
FROM FND_CURRENCIES
WHERE currency_code = l_curr_code;
UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Currency code mismatch'
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
-- Check the validation status of line : If an valid line then update the base table data
IF l_line_status <> 'E' THEN
-- Update the status for product line as 'Rejected'
IF PROD_REC.INF_REJ_CODE IS NOT NULL THEN
UPDATE OZF_SD_REQUEST_LINES_ALL SET REJECTION_CODE=PROD_REC.INF_REJ_CODE,VENDOR_APPROVED_FLAG='N'
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
-- Update the approved amount,approved currency and type in the base line table
UPDATE OZF_SD_REQUEST_LINES_ALL SET APPROVED_DISCOUNT_TYPE=PROD_REC.LINE_DISCOUNT_TYPE,
APPROVED_DISCOUNT_VALUE=PROD_REC.INTF_APPROVED_DISCOUNT_VALUE,
APPROVED_MAX_QTY=PROD_REC.INTF_APPROVED_QUANTITY,
APPROVED_DISCOUNT_CURRENCY=l_curr_code
WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
-- UPDATE THE REQUEST STATUS IN HEADER TABLE : OZF_SD_REQUEST_HEADERS_ALL_B
SELECT COUNT(*) INTO l_approved_lines FROM OZF_SD_REQUEST_LINES_ALL
WHERE REQUEST_HEADER_ID=l_req_id
AND VENDOR_APPROVED_FLAG = 'Y' ;
SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
where system_status_TYPE='OZF_SD_REQUEST_STATUS'
and SYSTEM_STATUS_CODE='SUPPLIER_APPROVED'
and default_flag='Y'
and enabled_flag='Y' ;
SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
where system_status_TYPE='OZF_SD_REQUEST_STATUS'
and SYSTEM_STATUS_CODE='SUPPLIER_REJECTED'
and default_flag='Y'
and enabled_flag='Y' ;
SELECT AUTH_NUMBER INTO l_authorization_number FROM OZF_SD_RES_HEADER_INTF WHERE request_number=p_request_number
AND NVL(PROCESSED_FLAG,'N') <>'Y' ;
UPDATE OZF_SD_REQUEST_HEADERS_ALL_B SET user_status_id= l_request_status,AUTHORIZATION_NUMBER=l_authorization_number
WHERE REQUEST_HEADER_ID=l_req_id ;
UPDATE OZF_SD_RES_HEADER_INTF SET ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number
and NVL(PROCESSED_FLAG,'N') <>'Y';
-- DO THE MASS UPDATE FOR THE INTERFACE TABLES
UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
-- DO THE MASS UPDATE FOR THE INTERFACE TABLES
UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';
PROCEDURE UPDATE_SD_REQ_STALE_DATA(p_request_number IN VARCHAR2)
IS
l_req_number_count NUMBER :=0;
UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;
END UPDATE_SD_REQ_STALE_DATA;
SELECT status_code,imd_claim_flag into l_status_code,l_imd_claim_flag
FROM OZF_SD_BATCH_HEADERS_ALL
WHERE BATCH_ID=p_batch_header_id ;
UPDATE OZF_SD_BATCH_LINES_ALL
SET status_code ='APPROVED'
WHERE BATCH_ID = p_batch_header_id
AND COMPLETE_FLAG ='Y'
AND PURGE_FLAG <>'Y'
AND ORIGINAL_CLAIM_AMOUNT > 0
AND TOTAL_APPROVED_AMT IS NOT NULL
AND status_code NOT IN ('APPROVED','COMPLETED');
select sum(approved_unit_claim_amount) INTO l_total_app_claim_amt from (
select
CASE
WHEN((approved_amount is null and QUANTITY_APPROVED is null) OR ( approved_currency_code <> CLAIM_AMOUNT_CURRENCY_CODE ) OR approved_amount < 0 OR QUANTITY_APPROVED < 0) then null
WHEN (approved_amount is null and QUANTITY_APPROVED IS NOT NULL AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price-agreement_price) )
WHEN (approved_amount is null and QUANTITY_APPROVED IS NOT NULL AND DISCOUNT_TYPE IN ('AMT')) THEN discount_value
WHEN (QUANTITY_APPROVED is null and approved_amount is not null AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) )
WHEN (QUANTITY_APPROVED is null and approved_amount is not null AND DISCOUNT_TYPE IN ('AMT')) THEN approved_amount
WHEN (QUANTITY_APPROVED is not null and quantity_approved <> 0 and approved_amount is not null and approved_amount <> 0 AND DISCOUNT_TYPE IN ('NEWPRICE','%')) THEN ((list_price - approved_amount) )
WHEN (QUANTITY_APPROVED is not null and quantity_approved <> 0 and approved_amount is not null and approved_amount <> 0 AND DISCOUNT_TYPE IN ('AMT')) THEN (approved_amount)
END approved_unit_claim_amount
from OZF_SD_BATCH_LINES_ALL line
WHERE line.complete_flag ='Y'
AND line.batch_id = p_batch_header_id
AND line.purge_flag <>'Y'
AND line.ORIGINAL_CLAIM_AMOUNT >0
AND (line.ORIGINAL_CLAIM_AMOUNT-line.CLAIM_AMOUNT)<>0
AND line.status_code NOT IN ('APPROVED','COMPLETED') ) ;
SELECT app_lines.app_count,
all_lines.total_count
INTO l_orig_app_line_count, l_orig_total_line_count
FROM
(SELECT COUNT(1) total_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
) all_lines,
(SELECT COUNT(1) app_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
AND status_code IN ('APPROVED')
) app_lines ;
UPDATE OZF_SD_BATCH_LINES_ALL
SET status_code ='APPROVED'
WHERE batch_id = p_batch_header_id
AND batch_line_id IN
(
select line.batch_line_id
from OZF_SD_BATCH_LINES_ALL line
WHERE line.complete_flag ='Y'
AND line.batch_id = p_batch_header_id
AND line.purge_flag <>'Y'
AND line.ORIGINAL_CLAIM_AMOUNT >0
AND (line.ORIGINAL_CLAIM_AMOUNT-line.CLAIM_AMOUNT)<>0
AND line.status_code NOT IN ('APPROVED','COMPLETED')
minus
select batch_line_id
from ozf_sd_batch_line_disputes
where dispute_code in('OZF_SD_CURR_CODE_MISMATCH', 'OZF_SD_VENDOR_AUTH_AMT_NGTVE' ,'OZF_SD_VENDOR_AUTH_QTY_NGTVE','OZF_SD_AUTH_AMT_QTY_NULL','OZF_SD_NO_RESPONSE')
and batch_id = p_batch_header_id
group by batch_line_id,dispute_code
having count(dispute_code)>0
) ;
SELECT (all_lines.total_count - app_lines.app_count),
app_lines.app_count,
all_lines.total_count
INTO l_unapproved_line_count, l_approved_line_count, l_total_line_count
FROM
(SELECT COUNT(1) total_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
) all_lines,
(SELECT COUNT(1) app_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
AND status_code IN ('APPROVED')
) app_lines ;
-- Update the Batch as closed based on the all lines status
SELECT (all_lines.total_count - (app_lines.app_count + com_lines.com_count + rma_lines.rma_count))
INTO l_unapproved_line_count
FROM
(SELECT COUNT(1) total_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
) all_lines,
(SELECT COUNT(1) app_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
AND status_code = 'APPROVED'
) app_lines,
(SELECT COUNT(1) com_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
AND status_code = 'COMPLETED'
) com_lines,
(SELECT COUNT(1) rma_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE batch_id = p_batch_header_id
AND ORIGINAL_CLAIM_AMOUNT < 0
) rma_lines ;
UPDATE ozf_sd_batch_headers_all
SET status_code = 'CLOSED'
WHERE BATCH_ID = p_batch_header_id ;
SELECT lines.batch_line_id,
lines.utilization_id ,
lines.ADJUSTMENT_TYPE_ID,
CASE WHEN (lines.status_code IN ('APPROVED','PARTIALLY_APPROVED') AND TOTAL_APPROVED_AMT IS NULL) THEN (lines.ORIGINAL_CLAIM_AMOUNT - lines.CLAIM_AMOUNT)
WHEN (lines.status_code IN ('APPROVED','PARTIALLY_APPROVED') AND TOTAL_APPROVED_AMT IS NOT NULL ) THEN (lines.ORIGINAL_CLAIM_AMOUNT - lines.TOTAL_APPROVED_AMT)
ELSE lines.ORIGINAL_CLAIM_AMOUNT
END adj_amount ,
lines.agreement_currency_code line_curr_code,
adj.adjustment_type adj_type_name,
util.cust_account_id,
util.billto_cust_account_id,
util.bill_to_site_use_id,
util.product_level_type,
util.product_id,
util.object_type,
util.object_id,
util.order_line_id,
util.org_id,
util.fund_id,
util.currency_code,
util.plan_currency_code,
util.plan_type,
util.plan_id,
util.exchange_rate_date
FROM OZF_SD_BATCH_LINES_ALL lines ,
ozf_funds_utilized_all_b util,
ozf_claim_types_all_vl adj
WHERE lines.batch_id = p_batch_header_id
AND (lines.status_code = 'APPROVED'
OR
lines.COMPLETE_FLAG='Y')
and lines.ADJUSTMENT_TYPE_ID = adj.claim_type_id
AND lines.utilization_id = util.utilization_id
AND lines.ORIGINAL_CLAIM_AMOUNT >0
AND lines.ADJ_UTILIZATION_ID is null;
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = 0,
acctd_amount_remaining = 0,
plan_curr_amount_remaining = 0,
univ_curr_amount_remaining = 0
WHERE utilization_id = l_new_util_id;
-- If complete write off, then change the line status to COMPLETE and update the new utlization iD
-- Else update the utilization id but the line status
IF (p_comp_wrt_off = fnd_api.g_true) THEN
UPDATE OZF_SD_BATCH_LINES_ALL
SET ADJ_UTILIZATION_ID = l_new_util_id,
status_code = 'COMPLETED'
WHERE batch_line_id = adj_rec.batch_line_id ;
UPDATE OZF_SD_BATCH_LINES_ALL
SET ADJ_UTILIZATION_ID = l_new_util_id
WHERE batch_line_id = adj_rec.batch_line_id;
select bh.status_code, cr.request_id, cr.phase_code
from OZF_SD_BATCH_HEADERS_ALL bh, FND_CONCURRENT_PROGRAMS cp , FND_CONCURRENT_REQUESTS cr
where cp.concurrent_program_name = 'OZFSDPBPPRG'
and cp.concurrent_program_id = bh.program_id
and cr.request_id(+) = bh.request_id
and bh.batch_id = p_batch_id;
update ozf_sd_batch_headers_all
set request_id = FND_GLOBAL.CONC_REQUEST_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
object_version_number = object_version_number + 1
where batch_id = p_batch_id;
UPDATE OZF_SD_BATCH_LINES_ALL
SET complete_flag='Y'
WHERE batch_id=p_batch_header_id ;
SELECT COUNT(1) INTO l_incomplete_count
FROM OZF_SD_BATCH_LINES_ALL
WHERE status_code IN( 'REJECTED','SUBMITTED')
AND NVL(complete_flag,'N')<>'Y'
AND batch_id=p_batch_header_id ;