[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT description
INTO l_description
FROM ar_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code ;
PROCEDURE INSERT_HDR IS
i NUMBER;
log( message => 'INSERT_HEADER +');
INSERT INTO ar_inv_api_headers_gt (
trx_header_id ,
trx_date ,
trx_currency ,
trx_class ,
cust_trx_type_id ,
gl_date ,
bill_to_customer_id ,
bill_to_site_use_id ,
term_id ,
exchange_rate_type ,
exchange_date ,
exchange_rate ,
comments )
VALUES
(iv_trx_header_tbl(i).trx_header_id,
iv_trx_header_tbl(i).trx_date ,
iv_trx_header_tbl(i).trx_currency ,
iv_trx_header_tbl(i).trx_class ,
iv_trx_header_tbl(i).cust_trx_type_id,
iv_trx_header_tbl(i).gl_date ,
iv_trx_header_tbl(i).bill_to_customer_id,
iv_trx_header_tbl(i).bill_to_site_use_id,
iv_trx_header_tbl(i).term_id ,
iv_trx_header_tbl(i).exchange_rate_type,
iv_trx_header_tbl(i).exchange_date,
iv_trx_header_tbl(i).exchange_rate,
iv_trx_header_tbl(i).comments);
log( message => 'INSERT_HEADER -');
PROCEDURE INSERT_LINE IS
i NUMBER;
log( message => 'INSERT_LINE +');
INSERT INTO ar_inv_api_lines_gt(
trx_header_id ,
trx_line_id ,
LINE_NUMBER ,
DESCRIPTION ,
QUANTITY_ORDERED ,
QUANTITY_INVOICED ,
UNIT_STANDARD_PRICE ,
UNIT_SELLING_PRICE ,
LINE_TYPE ,
AMOUNT )
VALUES
(iv_trx_lines_tbl(i).trx_header_id,
iv_trx_lines_tbl(i).trx_line_id ,
iv_trx_lines_tbl(i).LINE_NUMBER ,
iv_trx_lines_tbl(i).DESCRIPTION ,
iv_trx_lines_tbl(i).QUANTITY_ORDERED,
iv_trx_lines_tbl(i).QUANTITY_INVOICED,
iv_trx_lines_tbl(i).UNIT_STANDARD_PRICE,
iv_trx_lines_tbl(i).UNIT_SELLING_PRICE,
iv_trx_lines_tbl(i).LINE_TYPE ,
iv_trx_lines_tbl(i).AMOUNT);
log( message => 'INSERT_LINE -');
PROCEDURE INSERT_DIST IS
I NUMBER;
log( message => 'INSERT_DIST +');
INSERT INTO ar_inv_api_dist_gt(
trx_dist_id ,
trx_header_id ,
trx_LINE_ID ,
ACCOUNT_CLASS ,
PERCENT ,
CODE_COMBINATION_ID )
VALUES (
iv_trx_dist_tbl(i).trx_dist_id,
iv_trx_dist_tbl(i).trx_header_id,
iv_trx_dist_tbl(i).trx_LINE_ID ,
iv_trx_dist_tbl(i).ACCOUNT_CLASS,
iv_trx_dist_tbl(i).PERCENT ,
iv_trx_dist_tbl(i).CODE_COMBINATION_ID);
log( message => 'INSERT_DIST -');
SELECT trx_number
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id;
SELECT receipt_number
FROM ar_cash_receipts
WHERE cash_receipt_id = p_recp_id;
SELECT ssct.name,
ssct.sales_credit_type_id
INTO iv_sales_credit_name,
iv_sales_credit_id
FROM so_sales_credit_types ssct,
ra_salesreps ras
WHERE ras.salesrep_id = iv_salesrep_id(i)
AND ras.sales_credit_type_id = ssct.sales_credit_type_id;
UPDATE ar_late_charge_doc_gt
SET execution_status = 'R' --Ready
WHERE interest_header_id = iv_curr_header_id;
SELECT TRX_LINE_ID ,
ERROR_MESSAGE||':'||INVALID_VALUE
FROM ar_trx_errors_gt
WHERE TRX_LINE_ID IS NOT NULL
ORDER BY TRX_HEADER_ID, TRX_LINE_ID;
SELECT TRX_HEADER_ID ,
ERROR_MESSAGE||':'||INVALID_VALUE
FROM ar_trx_errors_gt
WHERE TRX_LINE_ID IS NULL
ORDER BY TRX_HEADER_ID;
SELECT count(TRX_HEADER_ID)
FROM ar_trx_errors_gt
GROUP BY TRX_HEADER_ID;
SELECT a.interest_line_id,
--{HYU update late_charge_Date
a.payment_schedule_id
--}
FROM ar_late_charge_doc_gt a
WHERE a.interest_batch_id = g_interest_batch_id
AND a.execution_status = 'R'
AND NOT EXISTS
(SELECT NULL
FROM ar_late_charge_doc_gt b
WHERE b.interest_header_id = a.interest_header_id
AND b.interest_batch_id = g_interest_batch_id
AND b.execution_status = 'E');
INSERT_HDR;
INSERT_LINE;
INSERT_DIST;
UPDATE ar_late_charge_doc_gt
SET execution_status = 'E',
hdr_err_msg = l_header_text(i)
WHERE interest_header_id = l_header_upg(i)
AND interest_batch_id = g_interest_batch_id;
UPDATE ar_interest_headers
SET process_status = 'E',
process_message = l_header_text(i)
WHERE interest_header_id = l_header_upg(i)
AND interest_batch_id = g_interest_batch_id;
UPDATE ar_late_charge_doc_gt
SET execution_status = 'E',
line_err_msg = l_line_text(i)
WHERE interest_line_id = l_line_upg(i);
UPDATE ar_interest_lines
SET process_status = 'E',
process_message= l_line_text(i)
WHERE interest_line_id = l_line_upg(i);
UPDATE ar_late_charge_doc_gt
SET execution_status = 'S'
WHERE execution_status = 'R'
AND interest_batch_id = g_interest_batch_id
AND interest_line_id = l_sucess_line_id(i);
UPDATE ar_payment_schedules
SET last_charge_date = g_int_cal_date
WHERE payment_schedule_id = l_success_ps_id(i);
SELECT LATE_CHARGE_DM_TYPE_ID,
LATE_CHARGE_INV_TYPE_ID,
ALLOW_LATE_CHARGES,
PENALTY_REC_TRX_ID,
FINCHRG_RECEIVABLES_TRX_ID
FROM ar_system_parameters;
SELECT NULL
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND header_type IN ('INV','DM')
AND NVL(p_worker_num,-9) = NVL(worker_num, -9)
AND execution_status = 'I';
INSERT INTO ar_late_charge_doc_gt
(interest_header_id ,
CURRENCY_CODE ,
HEADER_TYPE ,
cust_trx_type_id ,
CUSTOMER_ID ,
CUSTOMER_SITE_USE_ID ,
LATE_CHARGE_TERM_ID ,
EXCHANGE_RATE_TYPE ,
EXCHANGE_RATE ,
org_id ,
legal_entity_id ,
LATE_CHARGE_CALCULATION_TRX,
interest_line_id ,
DAYS_OF_INTEREST ,
DAYS_OVERDUE_LATE ,
DAILY_INTEREST_CHARGE ,
PAYMENT_SCHEDULE_ID ,
INTEREST_CHARGED ,
type ,
salesrep_required_flag ,
salesrep_id ,
salesrep_number ,
GL_ID_REC ,
execution_status ,
interest_batch_id ,
original_trx_id ,
original_trx_class ,
DUE_DATE ,
OUTSTANDING_AMOUNT ,
PAYMENT_DATE ,
LAST_CHARGE_DATE ,
INTEREST_RATE ,
gl_id_rev ,
worker_num )
SELECT h.INTEREST_HEADER_ID,
h.CURRENCY_CODE,
h.HEADER_TYPE,
--{
-- h.cust_trx_type_id,
DECODE(h.HEADER_TYPE,'INV',l_sp.LATE_CHARGE_INV_TYPE_ID,l_sp.LATE_CHARGE_DM_TYPE_ID),
--}
h.CUSTOMER_ID,
h.CUSTOMER_SITE_USE_ID,
h.LATE_CHARGE_TERM_ID,
h.EXCHANGE_RATE_TYPE,
h.EXCHANGE_RATE,
h.org_id,
h.legal_entity_id,
h.LATE_CHARGE_CALCULATION_TRX,
l.interest_line_id,
l.DAYS_OF_INTEREST,
l.DAYS_OVERDUE_LATE,
l.DAILY_INTEREST_CHARGE,
l.PAYMENT_SCHEDULE_ID,
l.INTEREST_CHARGED,
l.type,
sp.salesrep_required_flag,
-3,
'-3',
tty.GL_ID_REC,
'I',
g_interest_batch_id,
l.original_trx_id,
l.original_trx_class,
l.DUE_DATE ,
l.OUTSTANDING_AMOUNT,
l.PAYMENT_DATE ,
l.LAST_CHARGE_DATE ,
l.INTEREST_RATE ,
tty.GL_ID_REV,
p_worker_num
FROM ar_interest_headers h,
ar_interest_lines l,
ar_system_parameters sp,
ra_cust_trx_types tty
WHERE h.interest_batch_id = g_interest_batch_id
AND h.INTEREST_HEADER_ID = l.INTEREST_HEADER_ID
AND tty.cust_trx_type_id(+) = h.cust_trx_type_id
AND h.HEADER_TYPE IN ('INV','DM')
AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND NVL(l.interest_charged,0) <> 0
AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9)
AND h.PROCESS_STATUS = 'N'
ORDER BY h.INTEREST_HEADER_ID,
l.interest_line_id;
SELECT COUNT(*)
FROM ar_late_charge_doc_gt
WHERE execution_status = 'R'
AND interest_batch_id = g_interest_batch_id;
SELECT DISTINCT interest_header_id
FROM ar_late_charge_doc_gt
WHERE execution_status = 'I'
AND interest_batch_id = g_interest_batch_id
AND NVL(p_worker_num,-9) = NVL(worker_num,-9)
AND header_type IN ('INV','DM');
SELECT
lgt.interest_header_id ,
lgt.CURRENCY_CODE ,
lgt.HEADER_TYPE ,
lgt.cust_trx_type_id ,
lgt.CUSTOMER_ID ,
lgt.CUSTOMER_SITE_USE_ID ,
lgt.LATE_CHARGE_TERM_ID ,
lgt.EXCHANGE_RATE_TYPE ,
lgt.EXCHANGE_RATE ,
lgt.org_id ,
lgt.legal_entity_id ,
lgt.LATE_CHARGE_CALCULATION_TRX,
lgt.interest_line_id ,
lgt.DAYS_OF_INTEREST ,
lgt.DAYS_OVERDUE_LATE ,
lgt.DAILY_INTEREST_CHARGE ,
lgt.PAYMENT_SCHEDULE_ID ,
lgt.INTEREST_CHARGED ,
lgt.type ,
lgt.salesrep_required_flag ,
lgt.salesrep_id ,
lgt.salesrep_number ,
lgt.GL_ID_REC ,
lgt.original_trx_id ,
lgt.original_trx_class ,
lgt.DUE_DATE ,
lgt.OUTSTANDING_AMOUNT,
lgt.PAYMENT_DATE ,
lgt.LAST_CHARGE_DATE ,
lgt.INTEREST_RATE ,
lgt.gl_id_rev,
su.contact_id,
su.cust_acct_site_id
FROM ar_late_charge_doc_gt lgt,
hz_cust_site_uses_all su
WHERE interest_header_id = p_header_id
AND interest_batch_id = g_interest_batch_id
AND execution_status = p_exec_status
AND header_type IN ('INV','DM')
AND su.site_use_id= CUSTOMER_SITE_USE_ID;
SELECT gl_date,
calculate_interest_to_date
FROM ar_interest_batches
WHERE interest_batch_id = g_interest_batch_id;
log( message => ' update ar_interest_headers for successfull headers');
UPDATE ar_interest_headers SET
process_status = 'S',
process_message = NULL
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'N'
AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND DECODE(p_worker_num,NULL,NVL(worker_num,-9),p_worker_num)=NVL(worker_num,-9)
AND interest_header_id IN
(SELECT MAX(interest_header_id)
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND execution_status = 'S'
AND NVL(p_worker_num,-9)= NVL(worker_num,-9)
AND header_type IN ('INV','DM')
GROUP BY interest_header_id);
log( message => ' update ar_interest_headers for error headers');
UPDATE ar_interest_headers SET
process_status = 'E'
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'N'
AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND DECODE(p_worker_num,NULL,NVL(worker_num,-9),p_worker_num) = NVL(worker_num,-9)
AND header_type IN ('INV','DM')
RETURN interest_batch_id BULK COLLECT INTO l_list_header_in_error;
log( message => ' update ar_payment_schedule for successfull headers');
UPDATE ar_payment_schedules
SET last_charge_date = g_int_cal_date
WHERE payment_schedule_id IN
(SELECT l.PAYMENT_SCHEDULE_ID
FROM ar_interest_headers h,
ar_interest_lines l
WHERE h.interest_batch_id = g_interest_batch_id
AND h.process_status = 'S'
AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9)
AND h.interest_header_id = l.interest_header_id
AND h.header_type IN ('INV','DM'));
PROCEDURE insert_adj_process
(x_nb_adj OUT NOCOPY NUMBER,
p_worker_num IN NUMBER DEFAULT NULL)
IS
CURSOR csp IS
SELECT LATE_CHARGE_DM_TYPE_ID,
LATE_CHARGE_INV_TYPE_ID,
ALLOW_LATE_CHARGES,
PENALTY_REC_TRX_ID,
FINCHRG_RECEIVABLES_TRX_ID
FROM ar_system_parameters;
SELECT COUNT(*) FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND NVL(p_worker_num,-9) = NVL(p_worker_num,worker_num)
AND execution_status = 'I'
AND header_type = 'ADJ';
log( message => 'insert_adj_process +');
INSERT INTO ar_late_charge_doc_gt
( INTEREST_CHARGED,
PAYMENT_SCHEDULE_ID,
TYPE,
ORIGINAL_TRX_ID,
INTEREST_HEADER_ID,
INTEREST_LINE_ID,
receivables_trx_id,
receivables_trx_name,
interest_batch_id,
execution_status,
header_type,
worker_num)
SELECT l.INTEREST_CHARGED,
l.PAYMENT_SCHEDULE_ID,
l.TYPE,
l.ORIGINAL_TRX_ID,
l.INTEREST_HEADER_ID,
l.INTEREST_LINE_ID,
--{
decode(l.receivables_TRX_ID,null,
DECODE(l.type,'PENALTY',l_sp.PENALTY_REC_TRX_ID,
l_sp.FINCHRG_RECEIVABLES_TRX_ID),
-1,DECODE(l.type,'PENALTY',l_sp.PENALTY_REC_TRX_ID,
l_sp.FINCHRG_RECEIVABLES_TRX_ID)
, l.receivables_TRX_ID),
-- rtrx.receivables_trx_id,
--}
rtrx.name,
g_interest_batch_id,
'I',
header_type,
p_worker_num
FROM ar_interest_lines l,
ar_interest_headers h,
ar_interest_batches b,
ar_receivables_trx rtrx,
ar_payment_schedules psch
WHERE b.interest_batch_id = g_interest_batch_id
AND h.interest_batch_id = b.interest_batch_id
AND l.INTEREST_HEADER_ID = h.INTEREST_HEADER_ID
AND h.HEADER_TYPE = 'ADJ'
AND h.display_flag = 'Y' --HYU CDI Only adjustment generatable documents need to be considered
AND rtrx.receivables_trx_id(+) = l.receivables_trx_id
AND psch.payment_schedule_id(+) = l.PAYMENT_SCHEDULE_ID
AND psch.customer_trx_id(+) = l.ORIGINAL_TRX_ID
AND NVL(l.INTEREST_CHARGED,0) <> 0
AND l.PROCESS_STATUS = 'N'
AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num) =NVL(h.worker_num,-9);
log( message => 'insert_adj_process -');
SELECT gl_date,
calculate_interest_to_date
FROM ar_interest_batches
WHERE interest_batch_id = g_interest_batch_id;
SELECT INTEREST_CHARGED,
PAYMENT_SCHEDULE_ID,
TYPE,
ORIGINAL_TRX_ID,
INTEREST_HEADER_ID,
INTEREST_LINE_ID,
receivables_trx_id,
receivables_trx_name,
g_interest_batch_id,
worker_num
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND header_type = 'ADJ'
AND execution_status = 'I'
AND NVL(p_worker_num,-9) = NVL(worker_num,-9);
insert_adj_process(x_nb_adj,p_worker_num);
UPDATE ar_payment_schedules
SET last_charge_date = s_cal_int_date
WHERE payment_schedule_id = l_adjusted_ps(i);
UPDATE ar_late_charge_doc_gt
SET execution_status = l_process_status(i),
LINE_ERR_MSG = l_process_msg(i)
WHERE interest_line_id = l_interest_line_id(i)
AND interest_batch_id = g_interest_batch_id;
UPDATE ar_interest_lines
SET PROCESS_STATUS = l_process_status(i),
PROCESS_MESSAGE = l_process_msg(i)
WHERE interest_line_id = l_interest_line_id(i);
UPDATE ar_interest_headers a
SET a.process_status = 'E'
WHERE a.interest_batch_id = g_interest_batch_id
AND DECODE(p_worker_num,NULL,NVL(a.worker_num,-9),p_worker_num)=NVL(a.worker_num,-9)
AND a.header_type = 'ADJ'
AND a.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND EXISTS
(SELECT NULL
FROM ar_interest_lines b
WHERE b.interest_header_id = a.interest_header_id
AND b.process_status = 'E');
UPDATE ar_interest_headers a
SET a.process_status = 'S',
a.process_message= NULL
WHERE a.interest_batch_id = g_interest_batch_id
AND a.header_type = 'ADJ'
AND a.process_status = 'N'
AND a.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND DECODE(p_worker_num,NULL,NVL(a.worker_num,-9),p_worker_num)=NVL(a.worker_num,-9);
UPDATE hz_cust_site_uses
SET LAST_ACCRUE_CHARGE_DATE = s_cal_int_date
WHERE SITE_USE_ID IN
(SELECT DISTINCT customer_site_use_id
FROM ar_interest_headers h
WHERE h.process_status = 'S'
AND h.interest_batch_id = g_interest_batch_id
AND h.header_type = 'ADJ'
AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND DECODE(p_worker_num,NULL,NVL(h.worker_num,-9),p_worker_num)=NVL(h.worker_num,-9));
SELECT count(*) nb,
execution_status status
FROM (select interest_header_id interest_header_id,
MIN(execution_status) execution_status
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND header_type = 'INV'
AND execution_status IN ('E','S')
GROUP BY interest_header_id) b
GROUP BY execution_status;
SELECT count(*) nb,
execution_status status
FROM (select interest_header_id interest_header_id,
MIN(execution_status) execution_status
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND header_type = 'DM'
AND execution_status IN ('E','S')
GROUP BY interest_header_id) b
GROUP BY execution_status;
SELECT count(*) nb,
execution_status status
FROM ar_late_charge_doc_gt
WHERE interest_batch_id = g_interest_batch_id
AND header_type = 'ADJ'
GROUP BY execution_status;
SELECT NULL
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'E'
AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND header_type IN ('INV','DM');
SELECT NULL
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'S'
AND display_flag = 'Y'; -- Document generating a Late Charges Document
DELETE FROM ar_late_charge_doc_gt;
UPDATE ar_interest_headers a
SET a.process_message = ''
WHERE a.interest_batch_id = p_batch_id
AND a.display_flag = 'Y'; --HYU CDI only document generating the Late Charge s Doc
UPDATE ar_interest_lines a
SET a.process_message = ''
WHERE a.interest_header_id IN
(SELECT interest_header_id
FROM ar_interest_headers
WHERE interest_batch_id = p_batch_id
AND display_flag = 'Y'); --HYU CDI only document generating the Late Charge s Doc
UPDATE ar_payment_schedules
SET last_charge_date = g_int_cal_date
WHERE payment_schedule_id IN
(SELECT l.PAYMENT_SCHEDULE_ID
FROM ar_interest_headers h,
ar_interest_lines l
WHERE h.interest_batch_id = g_interest_batch_id
AND h.display_flag = 'N' -- Document included in Late Charges Calculation
AND h.process_status = 'N'
AND h.interest_header_id = l.interest_header_id);
UPDATE ar_interest_headers
SET process_status = 'S'
WHERE interest_batch_id = g_interest_batch_id
AND display_flag = 'N' -- Document included in Late Charges Calculation
AND process_status = 'N';
INSERT INTO ar_submission_ctrl_gt
(worker_id , --p_batch_source_id
batch_id , --
script_name , --script_name
status , --
order_num , --order helper number
request_id , --request_id
table_name ) --table_name
VALUES
(p_batch_source_id,
NULL,
'ARLCPS',
'SUBMITTED',
1,
x_out_request_id,
'ARLC');
SELECT request_id
FROM ar_submission_ctrl_gt
WHERE status <> 'COMPLETE'
AND script_name = p_sub_name;
UPDATE ar_submission_ctrl_gt
SET status = 'COMPLETE'
WHERE request_id = l_req_id;
SELECT request_id
FROM ar_submission_ctrl_gt
WHERE status = 'COMPLETE'
AND script_name = p_sub_name;
SELECT transferred_status,
object_version_number,
CALCULATE_INTEREST_TO_DATE,
BATCH_STATUS,
GL_DATE
FROM ar_interest_batches
WHERE interest_batch_id = g_interest_batch_id;
SELECT DISTINCT customer_site_use_id
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'N';
SELECT NULL
FROM ra_batch_sources
WHERE BATCH_SOURCE_ID = p_batch_source_id;
SELECT NULL
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id;
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_status => 'P',
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_STATUS => l_transferred_status,
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_STATUS => 'E',
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT NULL
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'E'
AND display_flag = 'Y' -- Document generating a Late Charges Document
AND p_worker_num = worker_num
AND header_type IN ('INV','DM');
SELECT 'Y'
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'S'
AND display_flag = 'Y' -- Document generating a Late Charges Document
AND p_worker_num = worker_num;
SELECT 'Y'
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'N'
AND display_flag = 'N'; -- Document included in the calculation without generating late charges
DELETE FROM ar_late_charge_doc_gt;
UPDATE ar_interest_headers a
SET a.process_message = ''
WHERE a.interest_batch_id = p_batch_id
AND a.worker_num = p_worker_num
AND a.display_flag = 'Y'; -- Document generating a Late Charges Document
UPDATE ar_interest_lines a
SET a.process_message = ''
WHERE a.interest_header_id IN
(SELECT interest_header_id
FROM ar_interest_headers
WHERE interest_batch_id = p_batch_id
AND display_flag = 'Y' -- Document generating a Late Charges Document
AND worker_num = p_worker_num);
UPDATE ar_payment_schedules
SET last_charge_date = g_int_cal_date
WHERE payment_schedule_id IN
(SELECT l.PAYMENT_SCHEDULE_ID
FROM ar_interest_headers h,
ar_interest_lines l
WHERE h.interest_batch_id = g_interest_batch_id
AND h.display_flag = 'N' -- Document included in Late Charges Calculation
AND h.process_status = 'N'
AND h.interest_header_id = l.interest_header_id);
UPDATE ar_interest_headers
SET process_status = 'S'
WHERE interest_batch_id = g_interest_batch_id
AND display_flag = 'N' -- Document included in Late Charges Calculation
AND process_status = 'N';
SELECT MAX(b.request_id) request_id,
b.interest_batch_id,
b.org_id,
b.gl_date,
b.calculate_interest_to_date,
s.late_charge_batch_source_id,
lg.currency_code
FROM ar_interest_batches_all b,
ar_interest_headers_all h,
ar_system_parameters_all s,
gl_ledgers lg
WHERE b.request_id = p_request_id
AND b.org_id = s.org_id
AND b.interest_batch_id = h.interest_batch_id
AND h.worker_num = p_worker_num
AND h.display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND lg.ledger_id = s.set_of_books_id
GROUP BY
b.interest_batch_id,
b.org_id,
b.gl_date,
b.calculate_interest_to_date,
s.late_charge_batch_source_id,
lg.currency_code;
SELECT NULL
FROM ar_interest_headers_all
WHERE request_id = p_request_id
AND worker_num = p_worker_num
AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
AND header_type IN ('INV','DM');
SELECT worker_num,
COUNT(interest_header_id)
FROM ar_interest_headers
WHERE interest_batch_id = p_interest_batch_id
AND process_status = 'N'
AND display_flag = 'Y' --HYU CDI only document generating the Late Charge s Doc
GROUP BY worker_num;
UPDATE ar_interest_headers
SET worker_num = mod(rownum, p_max_workers) + 1
WHERE interest_batch_id = p_interest_batch_id
AND process_status = 'N'
AND display_flag = 'Y'; --HYU CDI only document generating the Late Charge s Doc
SELECT transferred_status,
object_version_number,
CALCULATE_INTEREST_TO_DATE,
BATCH_STATUS,
GL_DATE
FROM ar_interest_batches
WHERE interest_batch_id = g_interest_batch_id;
SELECT interest_header_id
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status = 'N';
SELECT NULL
FROM ra_batch_sources
WHERE BATCH_SOURCE_ID = p_batch_source_id;
SELECT NULL
FROM ar_interest_headers
WHERE interest_batch_id = g_interest_batch_id
AND process_status <> 'S';
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_STATUS => 'P',
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_status => l_transferred_status,
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_status => 'E',
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
AR_INTEREST_BATCHES_PKG.update_batch
(p_init_msg_list => 'T',
P_INTEREST_BATCH_ID => g_interest_batch_id,
P_BATCH_STATUS => 'F',
P_TRANSFERRED_status => 'S',
p_updated_by_program => 'ARLCSM',
x_OBJECT_VERSION_NUMBER => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
INSERT INTO ar_submission_ctrl_gt
(worker_id , --p_batch_source_id
batch_id , --
script_name , --script_name
status , --
order_num , --order helper number
request_id , --request_id
table_name ) --table_name
VALUES
(p_batch_source_id,
NULL,
'ARLCPW',
'SUBMITTED',
1,
x_out_request_id,
'ARLC');
SELECT lg.currency_code,
sysp.org_id,
sysp.LATE_CHARGE_BATCH_SOURCE_ID
FROM ar_system_parameters sysp,
gl_ledgers lg
WHERE lg.ledger_id = sysp.set_of_books_id;