[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT jgzz_fiscal_code
FROM hz_parties
WHERE party_id = p_party_id;
SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_Score_histories
WHERE score_object_code = 'PARTY'
AND score_object_id = p_party_id)
AND a.score_object_code = 'PARTY'
AND a.score_object_id = p_party_id;
SELECT COUNT(ps.payment_schedule_id) cnt_inv
FROM ar_payment_schedules ps,
hz_cust_accounts ca
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND ps.class IN ('INV', 'DM', 'CB');
SELECT COUNT(del.delinquency_id) cnt_del
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies del
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND ps.class = 'INV'
AND del.status in ('DELINQUENT', 'PREDELINQUENT')
AND del.payment_schedule_id = ps.payment_schedule_id;
SELECT Count(1)
FROM iex_delinquencies del
WHERE del.party_cust_id = p_party_id
AND del.creation_date between sysdate - 365 and sysdate ;
SELECT object_id, select_column, entity_name
FROM iex_object_filters
WHERE object_filter_type = 'IEXCUST'
AND active_flag = 'Y';
SELECT rl.delinquency_status, rl.priority,
iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', rl.delinquency_status) meaning
FROM iex_cu_sts_rl_lines rl, iex_cust_status_rules r
WHERE rl.status_rule_id = l_status_rule_id
AND r.status_rule_id = rl.status_rule_id
AND trunc(sysdate) BETWEEN trunc(nvl(r.start_date,sysdate)) AND trunc(nvl(r.end_date,sysdate))
AND NVL(rl.enabled_flag, 'N') = 'Y'
ORDER BY rl.priority;
SELECT MIN(account_established_date)
FROM hz_cust_accounts
WHERE account_established_date IS NOT NULL
AND party_id = p_party_id;
l_sql_select VARCHAR2(1000);
l_sql_select := 'SELECT count(1) FROM ';
l_sql_stmt := 'SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM ' || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond || ')';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_bankruptcies' ||
' WHERE party_id = :party_id' ||
' AND close_date IS NULL ' ||
' AND NVL(DISPOSITION_CODE, '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT litigation_id' ||
' FROM iex_litigations ltg, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND ltg.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND ltg.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT litigation_id ' ||
' FROM iex_litigations ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND disposition_code IS NULL )';
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT repossession_id' ||
' FROM iex_repossessions rps, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND rps.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
' UNION ' ||
' SELECT repossession_id ' ||
' FROM iex_repossessions ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT writeoff_id' ||
' FROM iex_writeoffs wrf, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND wrf.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
--' AND wrf.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT writeoff_id ' ||
' FROM iex_writeoffs ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
' FROM iex_litigations ltg ' ||
' WHERE ltg.party_id = :party_id' ||
' AND ltg.disposition_code IS NULL ';
l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
' FROM iex_repossessions rps ' ||
' WHERE rps.party_id = :party_id' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
' FROM iex_writeoffs wrf ' ||
' WHERE wrf.party_id = :party_id' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_bankruptcies' ||
' WHERE party_id = :party_id' ||
' AND close_date IS NULL ' ||
' AND NVL(DISPOSITION_CODE , '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt_lsd := 'select count(1) from (' ||
' SELECT litigation_id' ||
' FROM iex_litigations ltg, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND ltg.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND ltg.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT litigation_id ' ||
' FROM iex_litigations ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND disposition_code IS NULL )';
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT repossession_id' ||
' FROM iex_repossessions rps, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND rps.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
--' AND rps.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT repossession_id ' ||
' FROM iex_repossessions ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT writeoff_id' ||
' FROM iex_writeoffs wrf, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND wrf.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
--' AND wrf.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT writeoff_id ' ||
' FROM iex_writeoffs ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
' FROM iex_litigations ltg ' ||
' WHERE ltg.party_id = :party_id' ||
' AND ltg.disposition_code IS NULL ' ;
l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
' FROM iex_repossessions rps ' ||
' WHERE rps.party_id = :party_id' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
' FROM iex_writeoffs wrf ' ||
' WHERE wrf.party_id = :party_id' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
SELECT count(1) into l_bkr_count
FROM iex_bankruptcies
WHERE party_id = x_customer_info_rec.party_id
AND close_date IS NULL
AND NVL(DISPOSITION_CODE, ' ') NOT IN ('DISMISSED','WITHDRAWN' );
SELECT payment_schedule_id
FROM iex_delinquencies
WHERE delinquency_id = x_delinquency_id
AND status not in ('CURRENT', 'CLOSE');
SELECT
SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
/* ROUND(
( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
/* 0), 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) dso */
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
-- Begin fix bug #5261855-jypark-06/16/2006-change to based table for performance
-- iex_delinquencies del,
iex_delinquencies_all del,
-- End fix bug #5261855-jypark-06/16/2006-change to based table for performance
ar_system_parameters sp
WHERE ca.party_id = l_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id
and sp.org_id=ps.org_id; --moac change
SELECT
SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies_all del,
ar_system_parameters sp
WHERE ca.party_id = l_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id
and sp.org_id=ps.org_id;
select nvl(sum(cm.total_amount * -1),0)
into l_amount_in_dispute
from ra_cm_requests cm
where cm.customer_trx_id in (select distinct ps.customer_trx_id
from ar_payment_schedules ps,
hz_cust_accounts ca
where ca.party_id = l_party_id
and ps.customer_id = ca.cust_account_id
and ps.status = 'OP')
and cm.status='PENDING_APPROVAL'
and not exists (select 1 from iex_delinquencies_all where status in ('CURRENT','CLOSE') -- bug 14499531
and transaction_id = cm.customer_trx_id);
SELECT sum(acctd_amount_due_remaining) amount
FROM ar_payment_schedules ps, iex_delinquencies del
WHERE ps.customer_id = p_object_id
-- fix bug #3561828 AND ps.due_date < sysdate
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- AND TRUNC(ps.due_date) < TRUNC(sysdate)
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
AND ps.status = 'OP'
AND del.payment_schedule_id = ps.payment_schedule_id
AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
select nvl(sum(cm.total_amount * -1),0)
into l_amount_in_dispute
from ra_cm_requests cm
where cm.customer_trx_id in (select distinct ps.customer_trx_id
from ar_payment_schedules ps,
ar_system_parameters parm
where ps.customer_id = p_object_id
and ps.org_id = parm.org_id
and ps.status = 'OP')
and cm.status='PENDING_APPROVAL'
and not exists (select 1 from iex_delinquencies_all where status in ('CURRENT','CLOSE') -- bug 14499531
and transaction_id = cm.customer_trx_id);
SELECT SUM(NVL(acctd_amount_due_remaining,0))
FROM ar_payment_schedules ps,
-- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
ar_system_parameters parm
WHERE customer_id = p_object_id
AND ps.org_id = parm.org_id
-- End fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
AND ps.status = 'OP';
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
/* 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
/* FROM ar_system_parameters sp,
ar_payment_schedules ps
WHERE ps.customer_id = l_cust_account_id
AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.org_id=sp.org_id; --added for moac change
SELECT ps.acctd_amount_due_remaining
FROM ar_payment_schedules ps, iex_delinquencies del
WHERE del.delinquency_id = p_object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
select nvl(sum(cm.total_amount * -1),0)
into l_amount_in_dispute
from ra_cm_requests cm
where cm.customer_trx_id in
(
SELECT distinct ps.customer_trx_id
FROM ar_payment_schedules ps, iex_delinquencies del
WHERE del.delinquency_id = p_object_id
AND ps.payment_schedule_id = del.payment_schedule_id
AND ps.status = 'OP'
AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
and cm.status='PENDING_APPROVAL';
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
FROM ar_system_parameters sp,
ar_payment_schedules ps
WHERE NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.payment_schedule_id = l_payment_schedule_id
AND ps.org_id=sp.org_id; --added for moac change
SELECT sum(acctd_amount_due_remaining) amount
FROM ar_payment_schedules ps, iex_delinquencies del
WHERE ps.customer_site_use_id = p_object_id
--BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
-- AND ps.due_date < sysdate
--END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
AND ps.status = 'OP'
AND del.payment_schedule_id = ps.payment_schedule_id
AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
select nvl(sum(cm.total_amount * -1),0)
into l_amount_in_dispute
from ra_cm_requests cm
where cm.customer_trx_id in (select distinct ps.customer_trx_id
FROM ar_payment_schedules ps,
ar_system_parameters parm
WHERE ps.customer_site_use_id = p_object_id
AND ps.org_id = parm.org_id
AND ps.status = 'OP')
and cm.status='PENDING_APPROVAL';
SELECT SUM(NVL(acctd_amount_due_remaining,0))
FROM ar_payment_schedules ps,
-- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
ar_system_parameters parm
WHERE customer_site_use_id = p_object_id
AND ps.org_id = parm.org_id
AND status = 'OP';
SELECT
ROUND(
( (SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
/* 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) /* DSO */
/* FROM ar_system_parameters sp,
ar_payment_schedules ps
WHERE ps.customer_site_use_id = l_customer_site_use_id
AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
AND ps.org_id=sp.org_id; --added for moac change
l_pk_query := 'select ca.party_id, TRX_SUM.CUST_ACCOUNT_ID, TRX_SUM.SITE_USE_ID, TRX_SUM.ORG_ID, TRX_SUM.CURRENCY, max(TRX_SUM.LAST_PAYMENT_DATE) pay_date ';
SELECT acr.receipt_date,
acr.cash_receipt_id,
decode(apsa.payment_schedule_id, -1, null, APSA.due_date),
ara.amount_applied amount,
apsa.invoice_currency_Code currency_code ,
acr.receipt_number,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', acr.status)
FROM ar_payment_schedules apsa,
ar_cash_receipts acr,
ar_receivable_applications ara,
ar_cash_receipt_history acrh,
iex_delinquencies del
WHERE ara.cash_receipt_id = acr.cash_receipt_id
AND ara.applied_payment_schedule_id = apsa.payment_schedule_id
AND apsa.payment_schedule_id = del.payment_schedule_id
AND del.delinquency_id = p_object_id
AND acr.cash_receipt_id = acrh.cash_receipt_id
AND nvl(acr.confirmed_flag, 'Y') = 'Y'
AND acr.reversal_date is null
AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
'N', ' '), 'REVERSED')
AND acrh.current_record_flag = 'Y'
ORDER BY 1 DESC, 2 DESC, 3 ASC;
l_data_query := 'SELECT TRX_SUM.LAST_PAYMENT_DATE, CR.CASH_RECEIPT_ID, ';
l_data_query := 'SELECT TRX_SUM.LAST_PAYMENT_DATE, null, ';
SELECT trx_sum.last_payment_date,
cr.cash_receipt_id,
trx_sum.last_payment_amount,
trx_sum.currency,
trx_sum.last_payment_number,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
FROM ar_trx_bal_summary trx_sum,
hz_cust_accounts ca, ar_cash_receipts cr
WHERE trx_sum.cust_account_id = ca.cust_account_id
AND ca.party_id = p_object_id
AND cr.receipt_number = trx_sum.last_payment_number
ORDER BY 1 DESC, 2 DESC;
SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
SELECT trx_sum.last_payment_date,
cr.cash_receipt_id,
trx_sum.last_payment_amount,
trx_sum.currency,
trx_sum.last_payment_number,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
FROM ar_trx_bal_summary trx_sum,
ar_cash_receipts cr
WHERE trx_sum.cust_account_id = p_object_id
AND cr.receipt_number = trx_sum.last_payment_number
ORDER BY 1 DESC, 2 DESC;
SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
SELECT acr.receipt_date,
acr.cash_receipt_id,
decode(apsa.payment_schedule_id, -1, null, APSA.due_date),
ara.amount_applied amount,
apsa.invoice_currency_Code currency_code ,
acr.receipt_number,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', acr.status)
FROM ar_payment_schedules apsa,
ar_cash_receipts acr,
ar_receivable_applications ara,
ar_cash_receipt_history acrh,
iex_delinquencies del
WHERE ara.cash_receipt_id = acr.cash_receipt_id
AND ara.applied_payment_schedule_id = apsa.payment_schedule_id
AND apsa.payment_schedule_id = del.payment_schedule_id
AND del.delinquency_id = p_object_id
AND acr.cash_receipt_id = acrh.cash_receipt_id
AND nvl(acr.confirmed_flag, 'Y') = 'Y'
AND acr.reversal_date is null
AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
'N', ' '), 'REVERSED')
AND acrh.current_record_flag = 'Y'
-- Begin fix bug #4932926-JYPARK-02/07/2006-remove unecesaary query for performance
--AND ACR.receipt_date =
-- (SELECT max(a.receipt_date)
-- FROM ar_cash_receipts a,
-- ar_receivable_applications b,
-- ar_cash_receipt_history c
-- WHERE a.cash_receipt_id = b.cash_receipt_id
-- AND b.applied_payment_schedule_id = apsa.payment_schedule_id
-- AND a.reversal_date is null
-- AND nvl(a.confirmed_flag, 'Y') = 'Y'
-- AND a.cash_receipt_id = c.cash_receipt_id
-- AND c.status not in (decode (C.factor_flag, 'Y', 'RISK_ELIMINATED',
-- 'N', ' '), 'REVERSED')
-- AND c.current_record_flag = 'Y'
-- )
-- End fix bug #4932926-JYPARK-02/07/2006-remove unecesaary query for performance
ORDER BY 1 DESC, 2 DESC, 3 ASC;
SELECT trx_sum.last_payment_date,
cr.cash_receipt_id,
trx_sum.last_payment_amount,
trx_sum.currency,
trx_sum.last_payment_number,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
FROM ar_trx_bal_summary trx_sum,
ar_cash_receipts cr
WHERE trx_sum.site_use_id = p_object_id
AND cr.receipt_number = trx_sum.last_payment_number
ORDER BY 1 DESC, 2 DESC;
SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
SELECT DECODE(CONTACT_POINT_PURPOSE, 'COLLECTIONS', 1, 2) C1,
DECODE(PRIMARY_BY_PURPOSE, 'Y', 1, 2) C2,
DECODE(PRIMARY_FLAG, 'Y', 1, 2) C3,
contact_point_id, phone_country_code, phone_area_code, phone_number, phone_extension,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('PHONE_LINE_TYPE', phone_line_type) phone_line_type_meaning,
email_address, contact_point_type
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_party_id
AND ((contact_point_type = 'EMAIL') OR
(contact_point_type = 'PHONE' AND phone_line_type NOT IN ('PAGER', 'FAX')))
AND NVL(do_not_use_flag, 'N') = 'N'
AND status = 'A'
ORDER BY 1,2,3;
select location_id, address2, address3, address4, party_id,last_update_date,
party_site_id,party_site_number,site_last_update_date, LAST_UPDATED_BY ,LAST_UPDATE_LOGIN , CREATED_BY, CREATION_DATE, address1, city, state, province,
postal_code, county, country_name, country_code, address_lines_phonetic,
po_box_number, house_number, street_suffix, street,
street_number, floor, suite, time_zone,time_zone_meaning, timezone_id, object_version_number, site_object_version_number,created_by_module, application_id
from ast_locations_v
where party_id = x_party_id
and primary_flag = x_primary_flag;
x_location_info_rec.last_update_date := l_location_row.last_update_date;
x_location_info_rec.last_updated_by := l_location_row.last_updated_by;
x_location_info_rec.last_update_login := l_location_row.last_update_login;
x_location_info_rec.site_last_update_date := l_location_row.site_last_update_date;
SELECT jgzz_fiscal_code
FROM hz_parties
WHERE party_id = p_party_id;
SELECT a.score_value
FROM iex_score_histories a
WHERE a.creation_date =
(SELECT MAX(creation_date)
FROM iex_Score_histories
WHERE score_object_code = 'PARTY'
AND score_object_id = p_party_id)
AND a.score_object_code = 'PARTY'
AND a.score_object_id = p_party_id;
SELECT COUNT(DECODE(ps.class, 'INV', ps.payment_schedule_id,
'DM', ps.payment_schedule_id,
'CB', ps.payment_schedule_id, NULL)) cnt_inv,
COUNT(DECODE(ps.class, 'INV', DECODE(del.status, 'DELINQUENT', del.delinquency_id,
'PREDELINQUENT', del.delinquency_id,NULL), NULL)) cnt_del,
SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt,
ROUND(
( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, *//* 22-JUL-2000 J Rautiainen BR Implementation */
/* 0), 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) dso
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies del,
ar_system_parameters sp
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id
and ps.org_id=sp.org_id; --added for MOAC change
SELECT COUNT(DECODE(ps.class, 'INV', ps.payment_schedule_id, 'DM', ps.payment_schedule_id, 'CB', ps.payment_schedule_id, NULL)) cnt_inv,
COUNT(DECODE(ps.class, 'INV', DECODE(del.status, 'DELINQUENT', del.delinquency_id, 'PREDELINQUENT', del.delinquency_id, NULL), NULL)) cnt_del
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies del
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id;
SELECT SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt,
ROUND(
( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'CB', 1,
'DEP', 1,
'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
0), 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING
) * MAX(SP.CER_DSO_DAYS)
)
/ DECODE(
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0)) ,
0, 1,
SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
'INV', 1,
'DM', 1,
'DEP', 1,
0), 0)
* DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
-1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
0) )
)
), 0) dso
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies del,
ar_system_parameters sp
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id
AND ps.org_id = sp.org_id;
SELECT SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
FROM ar_payment_schedules ps,
hz_cust_accounts ca,
iex_delinquencies del,
ar_system_parameters sp
WHERE ca.party_id = p_party_id
AND ps.customer_id = ca.cust_account_id
AND ps.status = 'OP'
AND del.payment_schedule_id(+) = ps.payment_schedule_id
AND ps.org_id = sp.org_id;
SELECT Count(1)
FROM iex_delinquencies del
WHERE del.party_cust_id = p_party_id
AND del.creation_date between sysdate - 365 and sysdate ;
SELECT object_id, select_column, entity_name
FROM iex_object_filters
WHERE object_filter_type = 'IEXCUST'
AND active_flag = 'Y';
SELECT rl.delinquency_status, rl.priority,
iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', rl.delinquency_status) meaning
FROM iex_cu_sts_rl_lines rl, iex_cust_status_rules r
WHERE rl.status_rule_id = l_status_rule_id
AND r.status_rule_id = rl.status_rule_id
AND trunc(sysdate) BETWEEN trunc(nvl(r.start_date,sysdate)) AND trunc(nvl(r.end_date,sysdate))
AND NVL(rl.enabled_flag, 'N') = 'Y'
ORDER BY rl.priority;
SELECT MIN(account_established_date)
FROM hz_cust_accounts
WHERE account_established_date IS NOT NULL
AND party_id = p_party_id;
l_sql_select VARCHAR2(1000);
l_sql_select := 'SELECT count(1) FROM ';
select nvl(sum(cm.total_amount * -1),0)
into l_amount_in_dispute
from ra_cm_requests cm
where cm.customer_trx_id in (select distinct customer_trx_id
from ar_payment_schedules ps,
hz_cust_accounts ca
where ca.party_id = p_party_id
and ps.customer_id = ca.cust_account_id
and ps.status = 'OP')
and cm.status='PENDING_APPROVAL';
l_sql_stmt := 'SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM ' || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond || ')';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_bankruptcies' ||
' WHERE party_id = :party_id' ||
' AND close_date IS NULL ' ||
' AND NVL(DISPOSITION_CODE, '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt_lsd := 'select count(1) from (' ||
' SELECT litigation_id' ||
' FROM iex_litigations ltg, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND ltg.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND ltg.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT litigation_id ' ||
' FROM iex_litigations ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND disposition_code IS NULL )';
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT repossession_id' ||
' FROM iex_repossessions rps, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND rps.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
--' AND rps.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT repossession_id ' ||
' FROM iex_repossessions ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W'')) ' ;
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT writeoff_id' ||
' FROM iex_writeoffs wrf, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND wrf.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
-- ' AND wrf.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT writeoff_id ' ||
' FROM iex_writeoffs ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
' FROM iex_litigations ltg ' ||
' WHERE ltg.party_id = :party_id' ||
' AND ltg.disposition_code IS NULL ' ;
l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
' FROM iex_repossessions rps ' ||
' WHERE rps.party_id = :party_id' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
' FROM iex_writeoffs wrf ' ||
' WHERE wrf.party_id = :party_id' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_bankruptcies' ||
' WHERE party_id = :party_id' ||
' AND close_date IS NULL ' ||
' AND NVL(DISPOSITION_CODE , '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
-- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
l_sql_stmt_lsd := 'select count(1) from (' ||
' SELECT litigation_id' ||
' FROM iex_litigations ltg, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND ltg.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND ltg.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT litigation_id ' ||
' FROM iex_litigations ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND disposition_code IS NULL )';
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT repossession_id' ||
' FROM iex_repossessions rps, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND rps.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
--' AND rps.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT repossession_id ' ||
' FROM iex_repossessions ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W'')) ' ;
l_sql_stmt_lsd := ' select count(1) from (' ||
' SELECT writeoff_id' ||
' FROM iex_writeoffs wrf, iex_delinquencies del' ||
' WHERE del.party_cust_id = :party_id' ||
' AND wrf.delinquency_id = del.delinquency_id' ||
' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
-- ' AND wrf.disposition_code IS NULL ' ||
' UNION ' ||
' SELECT writeoff_id ' ||
' FROM iex_writeoffs ' ||
' WHERE party_id= :party_id ' ||
' AND contract_number IS NOT NULL ' ||
' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
' FROM iex_litigations ltg ' ||
' WHERE ltg.party_id = :party_id' ||
' AND ltg.disposition_code IS NULL ' ;
l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
' FROM iex_repossessions rps ' ||
' WHERE rps.party_id = :party_id' ||
' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
' FROM iex_writeoffs wrf ' ||
' WHERE wrf.party_id = :party_id' ||
' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
l_sql_stmt := 'SELECT count(1)' ||
' FROM iex_delinquencies' ||
' WHERE party_cust_id = :party_id' ||
' AND status = ''PREDELINQUENT''';
SELECT count(1) into l_bkr_count
FROM iex_bankruptcies
WHERE party_id = x_customer_info_rec.party_id
AND close_date IS NULL
AND NVL(DISPOSITION_CODE, ' ') NOT IN ('DISMISSED','WITHDRAWN' );
l_party_rel_update_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
l_org_contact_update_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
l_last_update_date date;
SELECT *
FROM hz_relationships
WHERE (subject_id = l_party_id
AND relationship_code = p_type
AND status = 'A');
SELECT org_contact_id, object_version_number, application_id -- Bug 12359416
FROM hz_org_contacts
WHERE party_relationship_id = p_party_relationship_id;
SELECT object_version_number
FROM hz_parties
WHERE party_id = p_party_id;
SELECT *
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id;
SELECT *
FROM hz_contact_points
WHERE owner_table_id=( SELECT owner_table_id
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id)
AND contact_point_purpose=p_type
AND contact_point_type ='PHONE'
--Begin Bug 8322090 07-Apr-2009 barathsr
UNION
select *
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id;
SELECT party_site_id,party_site_number
FROM HZ_PARTY_SITES
where party_id = p_partyid
AND location_id = p_location_id;
l_party_rel_update_rec := AST_API_RECORDS_V2PKG.INIT_HZ_PARTY_REL_REC_TYPE_V2;
l_org_contact_update_rec := AST_API_RECORDS_V2PKG.INIT_HZ_ORG_CONTACT_REC_V2;
l_party_rel_update_rec.relationship_id := r_exist_rel.relationship_id;
l_party_rel_update_rec.subject_id := r_exist_rel.subject_id;
l_party_rel_update_rec.object_id := r_exist_rel.object_id;
l_party_rel_update_rec.status := 'I';
l_party_rel_update_rec.start_date := r_exist_rel.start_date;
l_party_rel_update_rec.end_date := sysdate;
l_party_rel_update_rec.relationship_type := r_exist_rel.relationship_type;
l_party_rel_update_rec.relationship_code := r_exist_rel.relationship_code;
l_party_rel_update_rec.subject_table_name := r_exist_rel.subject_table_name;
l_party_rel_update_rec.object_table_name := r_exist_rel.object_table_name;
l_party_rel_update_rec.subject_type := r_exist_rel.subject_type;
l_party_rel_update_rec.object_type := r_exist_rel.object_type;
l_party_rel_update_rec.application_id := r_exist_rel.application_id;
l_party_rel_update_rec.party_rec.status := 'I';
l_org_contact_update_rec.org_contact_id := l_org_contact_id;
l_org_contact_update_rec.party_rel_rec := l_party_rel_update_rec;
l_org_contact_update_rec.application_id := l_application_id; -- 625; Bug 12359416
iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact...');
HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact(
p_init_msg_list => 'F',
p_org_contact_rec => l_org_contact_update_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cont_object_version_number => l_cont_object_version_number,
p_rel_object_version_number => l_rel_object_version_number,
p_party_object_version_number => l_party_object_version_number);
SELECT hz_parties_s.nextval
INTO l_party_rel_create_rec.party_rec.party_number
FROM dual;
select location_id,party_site_id into l_location_id,l_party_site_id from hz_party_sites
where identifying_address_flag = 'Y'
and party_id = p_org_party_id;
select cust_acct_site_id,cust_account_id into l_cust_acct_site_id,t_cust_account_id from hz_cust_acct_sites
where party_site_id = l_party_site_id
and status = 'A' and rownum = 1;
select cust_acct_site_id into l_cust_acct_site_id from hz_cust_acct_sites
where party_site_id = l_party_site_id
and cust_account_id = p_cust_account_id;
select cust_account_id into t_cust_account_id from hz_cust_accounts
where party_id = p_org_party_id
and status = 'A' and rownum = 1 order by account_number;
select cust_account_id into t_cust_account_id from hz_cust_acct_sites
where cust_acct_site_id = (select cust_acct_site_id from hz_cust_site_uses_all where site_use_id = :IEXCUOVW_HEADER.customer_site_use_id);
update hz_cust_account_roles set status = 'I'
where status = 'A'
and created_by_module = l_org_contact_create_rec.created_by_module
and cust_account_id = t_cust_account_id;
SELECT hz_party_sites_s.nextval
INTO l_Party_Site_Create_rec.Party_Site_Number
FROM dual;