[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Utility procedure to update the qp_limit_balances table as an autonomous
transaction. This procedure is called by the Reverse_Limits procedure below.
*************************************************************************/
PROCEDURE Update_Balance(p_new_trxn_amount IN NUMBER,
p_old_trxn_amount IN NUMBER,
p_limit_balance_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
Qp_Preq_Grp.engine_debug('***Begin Update_Balance***');
UPDATE qp_limit_balances
SET available_amount = available_amount - p_new_trxn_amount +
p_old_trxn_amount,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id
WHERE limit_balance_id = p_limit_balance_id;
Qp_Preq_Grp.engine_debug('***End Update_Balance***');
END Update_Balance;
SELECT limit_balance_id, list_header_id, list_line_id,
price_request_type_code, price_request_code,
pricing_phase_id, amount
FROM qp_limit_transactions
WHERE price_request_code = a_cons_price_request_code;
SELECT amount
INTO l_returned_amount
FROM qp_limit_transactions
WHERE price_request_code = p_ret_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
INSERT INTO qp_limit_transactions
(limit_balance_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
list_line_id,
price_request_date,
price_request_type_code,
price_request_code,
pricing_phase_id,
amount
)
VALUES
(l_cons_trans_rec.limit_balance_id,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
l_cons_trans_rec.list_header_id,
l_cons_trans_rec.list_line_id,
SYSDATE,
l_cons_trans_rec.price_request_type_code,
p_ret_price_request_code,
l_cons_trans_rec.pricing_phase_id,
l_proration * l_cons_trans_rec.amount
);
Update_Balance(p_new_trxn_amount =>
l_proration * l_cons_trans_rec.amount,
p_old_trxn_amount => 0,
p_limit_balance_id => l_cons_trans_rec.limit_balance_id
);
END; --Block around SELECT stmt to check if returned rec exists
UPDATE qp_limit_transactions
SET amount = (l_proration * l_cons_trans_rec.amount),
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id,
price_request_date = SYSDATE --bug#12715755
WHERE price_request_code = p_ret_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
Update_Balance(p_new_trxn_amount => l_proration * l_cons_trans_rec.amount,
p_old_trxn_amount => l_returned_amount,
p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
SELECT amount
INTO l_consumed_amount
FROM qp_limit_transactions
WHERE price_request_code = p_cons_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
select ql.BASIS into l_basis
from qp_limits ql, qp_limit_balances qlb
where ql.list_header_id = l_cons_trans_rec.list_header_id
and ql.list_line_id = l_cons_trans_rec.list_line_id
and ql.limit_id = qlb.limit_id
and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
UPDATE qp_limit_transactions
SET amount = amount_rounded, --bug 13521835
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id,
price_request_date = sysdate --bug#12715755
WHERE price_request_code = p_cons_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
Update_Balance(p_new_trxn_amount =>amount_rounded, --bug13521835
p_old_trxn_amount => l_consumed_amount,
p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
SELECT amount
INTO l_returned_amount
FROM qp_limit_transactions
WHERE price_request_code = p_ret_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
select ql.BASIS into l_basis
from qp_limits ql, qp_limit_balances qlb
where ql.list_header_id = l_cons_trans_rec.list_header_id
and ql.list_line_id = l_cons_trans_rec.list_line_id
and ql.limit_id = qlb.limit_id
and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
INSERT INTO qp_limit_transactions
(limit_balance_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
list_header_id,
list_line_id,
price_request_date,
price_request_type_code,
price_request_code,
pricing_phase_id,
amount
)
VALUES
(l_cons_trans_rec.limit_balance_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
l_cons_trans_rec.list_header_id,
l_cons_trans_rec.list_line_id,
sysdate,
l_cons_trans_rec.price_request_type_code,
p_ret_price_request_code,
l_cons_trans_rec.pricing_phase_id,
amount_rounded --bug13521835
);
Update_Balance(p_new_trxn_amount =>
amount_rounded, --bug13521835
p_old_trxn_amount => 0,
p_limit_balance_id => l_cons_trans_rec.limit_balance_id
);
END; --Block around SELECT stmt to check if split child rec exists
SELECT amount
INTO l_consumed_amount
FROM qp_limit_transactions
WHERE price_request_code = p_cons_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
select ql.BASIS into l_basis
from qp_limits ql, qp_limit_balances qlb
where ql.list_header_id = l_cons_trans_rec.list_header_id
and ql.list_line_id = l_cons_trans_rec.list_line_id
and ql.limit_id = qlb.limit_id
and qlb.limit_balance_id = l_cons_trans_rec.limit_balance_id;
UPDATE qp_limit_transactions
SET amount = amount_rounded, --bug13521835
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
price_request_date = sysdate --bug#12715755
WHERE price_request_code = p_cons_price_request_code
AND list_header_id = l_cons_trans_rec.list_header_id
AND list_line_id = l_cons_trans_rec.list_line_id
AND limit_balance_id = l_cons_trans_rec.limit_balance_id;
Update_Balance(p_new_trxn_amount => amount_rounded, --bug13521835
p_old_trxn_amount => l_consumed_amount,
p_limit_balance_id => l_cons_trans_rec.limit_balance_id);
SELECT 'X'
FROM qp_pricing_phases a , qp_event_phases b
WHERE a.pricing_phase_id = b.pricing_phase_id
AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y' OR a.rltd_exists = 'Y')
AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
FROM qp_event_phases
WHERE ROWNUM < 7)
AND ROWNUM = 1;
SELECT 'X'
FROM qp_pricing_phases a , qp_event_phases b
WHERE a.pricing_phase_id = b.pricing_phase_id
AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y')
AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
FROM qp_event_phases
WHERE ROWNUM < 7)
AND ROWNUM = 1;
SELECT /*+ leading(C) use_nl(c,b) */ 'X' --bug 12677276
FROM
(SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))) PRICING_EVENT_CD
FROM qp_event_phases
WHERE pricing_phase_id > 1
AND ROWNUM < 7) C,
QP_EVENT_PHASES B
WHERE B.PRICING_EVENT_CODE = C.PRICING_EVENT_CD
AND EXISTS (SELECT /*+ no_push_subq no_unnest index(a QP_LIST_LINES_N4) */ 'x' --bug 12677276 14842950
FROM qp_list_lines a
WHERE a.pricing_phase_id = b.pricing_phase_id
AND a.modifier_level_code=p_mod_level_code
AND ROWNUM=1)
AND ROWNUM=1;
SELECT 'X'
FROM qp_event_phases b
WHERE b.pricing_event_code IN (SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
FROM qp_event_phases
WHERE pricing_phase_id > 1
AND ROWNUM < 7)
AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
WHERE lhb.pricing_phase_id = b.pricing_phase_id
AND qph.list_header_id = lhb.list_header_id
AND qph.active_flag = 'Y'
AND qprs.request_type_code = p_request_type_code
AND qprs.source_system_code = qph.source_system_code
AND a.pricing_phase_id = b.pricing_phase_id
AND a.list_header_id = qph.list_header_id
AND a.modifier_level_code = p_mod_level_code
AND ROWNUM=1)
AND ROWNUM=1;
SELECT 'X'
FROM qp_event_phases
WHERE pricing_event_code = p_event_code
AND pricing_phase_id = 1;
SELECT 'X'
FROM qp_pricing_phases a , qp_event_phases b
WHERE a.pricing_phase_id = b.pricing_phase_id
AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y' OR a.rltd_exists = 'Y')
AND b.pricing_event_code in (SELECT decode(rownum
,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE rownum < 7)
AND ROWNUM = 1;
SELECT 'X'
FROM qp_pricing_phases a , qp_event_phases b
WHERE a.pricing_phase_id = b.pricing_phase_id
AND (a.oid_exists = 'Y' OR a.line_group_exists = 'Y')
AND b.pricing_event_code in (SELECT decode(rownum
,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE rownum < 7)
AND ROWNUM = 1;
SELECT 'X'
FROM qp_event_phases b
WHERE b.pricing_event_code in (SELECT decode(rownum
,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1)))
FROM qp_event_phases
WHERE pricing_phase_id > 1
AND rownum < 7)
AND EXISTS (SELECT 'x'
FROM qp_list_lines a
WHERE a.pricing_phase_id = b.pricing_phase_id
AND a.modifier_level_code=p_mod_level_code
AND ROWNUM=1)
AND ROWNUM=1;*/
SELECT /*+ leading(C) use_nl(c,b) */ 'X'
FROM
( SELECT decode(rownum
,1 ,substr(p_event_code,1,instr(p_event_code1,',',1,1)-1)
,2 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,3 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,4 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,5 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))
,6 ,substr(p_event_code , instr(p_event_code1,',',1,rownum-1) + 1,
instr(p_event_code1,',',1,rownum)-1 - instr(p_event_code1,',',1,rownum-1))) PRICING_EVENT_CD
FROM qp_event_phases
WHERE pricing_phase_id > 1
AND rownum < 7) C,
QP_EVENT_PHASES B
WHERE B.PRICING_EVENT_CODE = C.PRICING_EVENT_CD
AND EXISTS (SELECT /*+ no_push_subq no_unnest index(a QP_LIST_LINES_N4) */ 'x' -- 14842950
FROM qp_list_lines a
WHERE a.pricing_phase_id = b.pricing_phase_id
AND a.modifier_level_code=p_mod_level_code
AND ROWNUM=1)
AND ROWNUM=1;
SELECT 'X'
FROM qp_event_phases b
WHERE b.pricing_event_code IN (SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
FROM qp_event_phases
WHERE pricing_phase_id > 1
AND ROWNUM < 7)
AND EXISTS (SELECT /*+ ORDERED */ 'x' -- [julin/4261562] added active_flag and PTE/SS filters
FROM qp_list_header_phases lhb, qp_list_headers_b qph, qp_price_req_sources_v qprs, qp_list_lines a
WHERE lhb.pricing_phase_id = b.pricing_phase_id
AND qph.list_header_id = lhb.list_header_id
AND qph.active_flag = 'Y'
AND qprs.request_type_code = p_request_type_code
AND qprs.source_system_code = qph.source_system_code
AND a.pricing_phase_id = b.pricing_phase_id
AND a.list_header_id = qph.list_header_id
AND a.modifier_level_code=p_mod_level_code
AND ROWNUM=1)
AND ROWNUM=1;
SELECT 'X'
FROM qp_event_phases
WHERE pricing_event_code = p_event_code
AND pricing_phase_id = 1;
SELECT 'Y'
FROM qp_pricing_phases a , qp_event_phases b
WHERE a.pricing_phase_id = b.pricing_phase_id
AND a.line_group_exists = 'Y' -- no need to consider PRG/OID for manual mod call
AND a.manual_modifier_flag in ('M','B') -- phases tagged to have manual modifiers
AND b.pricing_event_code IN (SELECT DECODE(ROWNUM
,1 ,SUBSTR(p_event_code,1,INSTR(p_event_code1,',',1,1)-1)
,2 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,3 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,4 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,5 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1))
,6 ,SUBSTR(p_event_code , INSTR(p_event_code1,',',1,ROWNUM-1) + 1,
INSTR(p_event_code1,',',1,ROWNUM)-1 - INSTR(p_event_code1,',',1,ROWNUM-1)))
FROM qp_event_phases
WHERE ROWNUM < 7)
AND ROWNUM = 1;
l_select VARCHAR2(1);
SELECT 'X'
FROM qp_currency_details a
,qp_list_headers_b b
WHERE a.currency_header_id = b.currency_header_id
AND a.to_currency_code = l_currency_code
AND b.list_header_id = l_price_list_id
AND TRUNC(l_temp_date) >= TRUNC(NVL(a.start_date_active, l_temp_date))
AND TRUNC(l_temp_date) <= TRUNC(NVL(a.end_date_active, l_temp_date))
AND TRUNC(l_temp_date) >= TRUNC(NVL(b.start_date_active, l_temp_date))
AND TRUNC(l_temp_date) <= TRUNC(NVL(b.end_date_active, l_temp_date));
SELECT 'X'
FROM qp_list_headers_b
WHERE currency_code = l_currency_code
AND list_header_id = l_price_list_id
AND TRUNC(l_temp_date) >= TRUNC(NVL(start_date_active, l_temp_date))
AND TRUNC(l_temp_date) <= TRUNC(NVL(end_date_active, l_temp_date));
FETCH c_validate_plist_curr_multi INTO l_select;
FETCH c_validate_pl_curr_no_multi INTO l_select;
SELECT currency_code
,name currency_name
,PRECISION currency_precision
FROM fnd_currencies_vl
WHERE currency_flag = 'Y'
AND enabled_flag = 'Y'
AND TRUNC(NVL(start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(end_date_active, SYSDATE)) >= TRUNC(SYSDATE)
ORDER BY currency_code;
SELECT DISTINCT a.currency_code currency_code
,a.name currency_name
,a.PRECISION currency_precision
FROM fnd_currencies_vl a
,qp_currency_details b
,qp_list_headers_b c
WHERE c.list_header_id = l_price_list_id
AND b.currency_header_id = c.currency_header_id
AND a.currency_code = b.to_currency_code
AND c.list_type_code IN ('PRL', 'AGR')
AND a.currency_flag = 'Y'
AND a.enabled_flag = 'Y'
AND TRUNC(l_temp_date) >= TRUNC(NVL(b.start_date_active, l_temp_date))
AND TRUNC(l_temp_date) <= TRUNC(NVL(b.end_date_active, l_temp_date))
AND TRUNC(l_temp_date) >= TRUNC(NVL(c.start_date_active, l_temp_date))
AND TRUNC(l_temp_date) <= TRUNC(NVL(c.end_date_active, l_temp_date))
ORDER BY a.currency_code;
Bug 3018412 - added the condition to select the data for all source systems belonging to a pte_code
***********************************************************************/
PROCEDURE Get_Price_List
(
l_currency_code IN VARCHAR2
,l_pricing_effective_date IN DATE
,l_agreement_id IN NUMBER
,l_blanket_reference_id IN VARCHAR2 DEFAULT NULL
,l_price_list_tbl OUT NOCOPY price_list_tbl
,l_sold_to_org_id IN NUMBER DEFAULT NULL
)
IS
l_temp_date DATE;
SELECT DISTINCT qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM qp_list_headers_vl qlhv
,qp_currency_details qdt
WHERE qlhv.currency_header_id = qdt.currency_header_id
AND qdt.to_currency_code = l_currency_code
AND qlhv.active_flag = 'Y'
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND qlhv.list_type_code = 'PRL'
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
AND l_temp_date
BETWEEN
NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
NVL(TRUNC(qlhv.end_date_active), l_temp_date)
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
AND l_temp_date
BETWEEN
NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
NVL(TRUNC(qdt.end_date_active), l_temp_date)
--AND :parameter.lov_num_param1 IS NULL
AND l_agreement_id IS NULL
--AND qdt.to_currency_code = NVL(:order.transactional_curr_code, qdt.to_currency_code)
--AND qdt.to_currency_code = NVL(l_order_transac_curr_code, qdt.to_currency_code)
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
UNION
SELECT DISTINCT qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM qp_list_headers_vl qlhv
,oe_agreements oa
,qp_currency_details qdt
WHERE ( ( oa.price_list_id = qlhv.list_header_id
AND qlhv.list_type_code IN ('PRL', 'AGR') )
OR
qlhv.list_type_code = 'PRL'
)
AND qlhv.active_flag = 'Y'
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND qlhv.currency_header_id = qdt.currency_header_id
AND qdt.to_currency_code = l_currency_code
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
AND l_temp_date
BETWEEN
NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
NVL(TRUNC(qlhv.end_date_active), l_temp_date)
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
AND l_temp_date
BETWEEN
NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
NVL(TRUNC(qdt.end_date_active), l_temp_date)
--AND :parameter.lov_num_param1 = oa.agreement_id
AND l_agreement_id = oa.agreement_id
--AND :parameter.lov_num_param1 IS NOT NULL
AND l_agreement_id IS NOT NULL
--AND qdt.to_currency_code = NVL(:order.transactional_curr_code, qdt.to_currency_code)
--AND qdt.to_currency_code = NVL(l_order_transac_curr_code, qdt.to_currency_code)
AND qdt.to_currency_code = NVL(l_currency_code, qdt.to_currency_code)
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
UNION
SELECT DISTINCT qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM qp_list_headers_vl qlhv
,oe_agreements oa
,qp_currency_details qdt
WHERE ( ( oa.price_list_id = qlhv.list_header_id
AND qlhv.list_type_code IN ('PRL', 'AGR') )
OR
qlhv.list_type_code = 'PRL'
)
AND qlhv.active_flag = 'Y'
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND qlhv.currency_header_id = qdt.currency_header_id
AND qdt.to_currency_code = l_currency_code
AND l_temp_date
BETWEEN
NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
NVL(TRUNC(qlhv.end_date_active), l_temp_date)
AND l_temp_date
BETWEEN
NVL(TRUNC(qdt.start_date_active), l_temp_date) AND
NVL(TRUNC(qdt.end_date_active), l_temp_date)
AND l_agreement_id IS NULL
AND qdt.to_currency_code = NVL(l_currency_code, qdt.to_currency_code)
AND l_sold_to_org_id IS NOT NULL
AND( oa.sold_to_org_id = l_sold_to_org_id OR
oa.sold_to_org_id IS NULL OR
oa.sold_to_org_id = -1 OR
oa.sold_to_org_id IN (
SELECT r.cust_account_id FROM
hz_cust_acct_relate r
WHERE r.related_cust_account_id = l_sold_to_org_id AND
r.status = 'A' AND l_cust_relation_flag = 'Y'))
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
ORDER BY name;
SELECT qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM qp_list_headers_vl qlhv
WHERE list_type_code = 'PRL'
AND qlhv.active_flag = 'Y'
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate))
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
AND l_temp_date
BETWEEN
NVL(TRUNC(start_date_active), l_temp_date) AND
NVL(TRUNC(end_date_active), l_temp_date)
--AND :parameter.lov_num_param1 IS NULL
AND l_agreement_id IS NULL
--AND currency_code = NVL(l_order_transac_curr_code, currency_code)
AND currency_code = NVL(l_currency_code, currency_code)
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
UNION
SELECT
qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM
qp_list_headers_vl qlhv
,oe_agreements oa
WHERE ( (oa.price_list_id = qlhv.list_header_id AND
qlhv.list_type_code IN ('PRL', 'AGR'))
OR
qlhv.list_type_code = 'PRL'
)
--AND NVL(to_date(:parameter.lov_char_param1), TRUNC(sysdate)) BETWEEN
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND l_temp_date
BETWEEN
NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
NVL(TRUNC(qlhv.end_date_active), l_temp_date)
--AND :parameter.lov_num_param1 = oa.agreement_id
AND l_agreement_id = oa.agreement_id
--AND :parameter.lov_num_param1 IS NOT NULL
AND l_agreement_id IS NOT NULL
--AND currency_code = NVL(l_order_transac_curr_code, currency_code)
AND currency_code = NVL(l_currency_code, currency_code)
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
UNION
SELECT
qlhv.list_header_id price_list_id
,qlhv.name name
,qlhv.description description
,qlhv.start_date_active start_date_active
,qlhv.end_date_active end_date_active
FROM
qp_list_headers_vl qlhv
,oe_agreements oa
WHERE ( (oa.price_list_id = qlhv.list_header_id AND
qlhv.list_type_code IN ('PRL', 'AGR'))
OR
qlhv.list_type_code = 'PRL'
)
-- If there is a blanket reference show all standard pricelist and
-- all shareable BSO PL's and all PL's attached to the referenced blanket.
-- Otherwise show only standard PL's.
AND (
( l_blanket_reference_id IS NULL
AND NVL(qlhv.list_source_code,' ') <> 'BSO'
)
OR
( l_blanket_reference_id IS NOT NULL
AND
( ( NVL(qlhv.shareable_flag,'Y') = 'Y'
AND NVL(qlhv.list_source_code,' ') = 'BSO'
)
OR NVL(qlhv.orig_system_header_ref,-9999) = l_blanket_reference_id
OR NVL(qlhv.list_source_code,' ') <> 'BSO'
)
)
) -- Blanket Pricing
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
--AND (((nvl(qlhv.global_flag,'Y') = 'Y' or qlhv.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND l_temp_date
BETWEEN
NVL(TRUNC(qlhv.start_date_active), l_temp_date) AND
NVL(TRUNC(qlhv.end_date_active), l_temp_date)
AND l_agreement_id IS NULL
AND currency_code = NVL(l_currency_code, currency_code)
AND l_sold_to_org_id IS NOT NULL
AND( oa.sold_to_org_id = l_sold_to_org_id OR
oa.sold_to_org_id IS NULL OR
oa.sold_to_org_id = -1 OR
oa.sold_to_org_id IN (
SELECT r.cust_account_id FROM
hz_cust_acct_relate r
WHERE r.related_cust_account_id = l_sold_to_org_id AND
r.status = 'A' AND l_cust_relation_flag = 'Y'))
AND qlhv.source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
ORDER BY name;
Bug 3018412 - added the condition to select the data for all source systems belonging to a pte_code
*/
PROCEDURE Get_Price_Lists
(
p_currency_code IN VARCHAR2 DEFAULT NULL
,p_price_lists_tbl OUT NOCOPY price_lists_tbl
)
IS
l_temp_date DATE;
SELECT list_header_id price_list_id,
name name,
description description,
-rounding_factor rounding_factor,
start_date_active start_date_active,
end_date_active end_date_active
FROM qp_list_headers_vl
WHERE list_type_code IN ('PRL' ,'AGR') AND
NVL(active_flag,'N') ='Y'
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
-- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
-- and qp_security.security_on = 'Y') or qp_security.security_on = 'N')
AND source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
ORDER BY name;
SELECT list_header_id price_list_id,
name name,
description description,
-rounding_factor rounding_factor,
start_date_active start_date_active,
end_date_active end_date_active
FROM qp_list_headers_vl
WHERE currency_code = p_currency_code
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
-- (((nvl(global_flag,'Y') = 'Y' or orig_org_id = fnd_profile.Value('ORG_ID'))
-- and qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
AND TRUNC(l_temp_date) BETWEEN NVL(TRUNC(start_date_active), TRUNC(l_temp_date)) AND
NVL(TRUNC(end_date_active), TRUNC(l_temp_date)) AND
list_type_code = 'PRL'
AND source_system_code IN (SELECT qpss.application_short_name
FROM qp_pte_source_systems qpss
WHERE qpss.pte_code = Fnd_Profile.value('QP_PRICING_TRANSACTION_ENTITY'))
ORDER BY name;
SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
a.agreement_type, q.name price_list_name, p.party_name customer_name,
t.name payment_term_name, a.start_date_active, a.end_date_active
FROM oe_agreements_lov_v a, qp_list_headers_vl q,
hz_parties p, hz_cust_accounts c, ra_terms_tl t,
-- qp_list_headers_b l,
oe_transaction_types_all ot
WHERE a.sold_to_org_id IN (
SELECT TO_NUMBER(p_sold_to_org_id) FROM dual
UNION
SELECT -1 FROM dual
UNION
SELECT r.cust_account_id FROM hz_cust_acct_relate r
WHERE r.related_cust_account_id = p_sold_to_org_id AND
l_cust_relation_flag = 'Y' AND
r.status = 'A' ) AND
l_temp_date BETWEEN
TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
a.price_list_id = q.list_header_id
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
-- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
AND a.price_list_id = q.list_header_id AND
-- q.language = userenv('LANG') and
a.sold_to_org_id = c.cust_account_id(+) AND
c.party_id = p.party_id(+) AND
a.term_id = t.term_id(+) AND
ot.transaction_type_id = p_transaction_type_id AND
DECODE(ot.agreement_type_code,NULL,NVL(a.agreement_type, -9999),
ot.agreement_type_code) = NVL(a.agreement_type, -9999) AND
t.LANGUAGE(+) = USERENV('LANG')
ORDER BY 1;
select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
a.agreement_type, q.name price_list_name, null customer_name,
t.name payment_term_name, a.start_date_active, a.end_date_active
from oe_agreements_lov_v a, qp_list_headers_vl q, ra_terms_tl t,
oe_transaction_types_all ot
where l_temp_date between
trunc(nvl(a.start_date_active, add_months(sysdate, -10000))) and
trunc(nvl(a.end_date_active, add_months(sysdate, +10000))) and
a.price_list_id = q.list_header_id and
--(((nvl(l.global_flag,'Y') = 'Y' or l.orig_org_id = fnd_profile.Value('ORG_ID')) and
--qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
--a.price_list_id = q.list_header_id and
--q.language = userenv('LANG') and
a.term_id = t.term_id(+) and
ot.transaction_type_id = p_transaction_type_id and
decode(ot.agreement_type_code,null,nvl(a.agreement_type, -9999),
ot.agreement_type_code) = nvl(a.agreement_type, -9999) and
t.language(+) = userenv('LANG')
order by 1;
select substr(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
a.agreement_type, q.name price_list_name, p.party_name customer_name,
null payment_term_name, a.start_date_active, a.end_date_active
from oe_agreements_lov_v a, qp_list_headers_vl q,
hz_parties p, hz_cust_accounts c --, qp_list_headers_b l
where a.sold_to_org_id in (
select to_number(p_sold_to_org_id) from dual
union
select -1 from dual
union
select r.cust_account_id from hz_cust_acct_relate r
where r.related_cust_account_id = p_sold_to_org_id and
l_cust_relation_flag = 'Y' and
r.status = 'A' ) and
l_temp_date between
trunc(nvl(a.start_date_active, add_months(sysdate, -10000))) and
trunc(nvl(a.end_date_active, add_months(sysdate, +10000))) and
a.price_list_id = q.list_header_id and
--(((nvl(l.global_flag,'Y') = 'Y' or l.orig_org_id = fnd_profile.Value('ORG_ID')) and
--qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
--a.price_list_id = q.list_header_id and
--q.language = userenv('LANG') and
a.sold_to_org_id = c.cust_account_id(+) and
c.party_id = p.party_id(+)
order by 1;
SELECT SUBSTRB(a.agreement_name,1,300) agreement_name, a.agreement_id agreement_id,
a.agreement_type, q.name price_list_name, NULL customer_name,
NULL payment_term_name, a.start_date_active, a.end_date_active
FROM oe_agreements_lov_v a, qp_list_headers_vl q
--, qp_list_headers_b l
WHERE l_temp_date BETWEEN
TRUNC(NVL(a.start_date_active, ADD_MONTHS(SYSDATE, -10000))) AND
TRUNC(NVL(a.end_date_active, ADD_MONTHS(SYSDATE, +10000))) AND
a.price_list_id = q.list_header_id
--added for MOAC
--commented out below 2 lines for MOAC as the ORG_ID check is built into the view qp_list_headers_vl
-- (((nvl(q.global_flag,'Y') = 'Y' or q.orig_org_id = fnd_profile.Value('ORG_ID')) and
-- qp_security.security_on = 'Y') or qp_security.security_on = 'N') and
AND a.price_list_id = q.list_header_id
-- q.language = userenv('LANG')
ORDER BY 1;
SELECT qcdt.selling_rounding_factor
INTO l_rounding_factor
FROM qp_list_headers_b qb, qp_currency_details qcdt
WHERE qb.list_header_id = p_price_list_id
AND qcdt.currency_header_id = qb.currency_header_id
AND qcdt.to_currency_code = p_currency_code
AND l_pricing_effective_date BETWEEN
NVL(TRUNC(QCDT.START_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE)
AND NVL(TRUNC(QCDT.END_DATE_ACTIVE),l_PRICING_EFFECTIVE_DATE)
AND ROWNUM < 2;
SELECT qb.rounding_factor
INTO l_rounding_factor
FROM qp_list_headers_b qb
WHERE qb.list_header_id = p_price_list_id;
SELECT *
FROM qp_debug_req
WHERE request_id = p_request_id;
SELECT *
FROM qp_debug_req_lines
WHERE request_id = p_req_id;
SELECT *
FROM qp_debug_req_line_attrs
WHERE request_id = p_req_id AND
attribute_type = 'QUALIFIER' AND
line_detail_index IS NULL;
SELECT *
FROM qp_debug_req_line_attrs
WHERE request_id = p_req_id AND
attribute_type IN ('PRODUCT','PRICING') AND
line_detail_index IS NULL;
SELECT *
FROM qp_debug_req_ldets
WHERE request_id = p_req_id;
SELECT *
FROM qp_debug_req_rltd_lines
WHERE request_id = p_req_id AND
relationship_type_code = Qp_Preq_Grp.G_SERVICE_LINE;
p_control_rec.temp_table_insert_flag := l_dbg_req_rec.temp_table_insert_flag;
p_line_tbl(I).updated_adjusted_unit_price := cl.updated_adjusted_unit_price;
p_line_detail_tbl(i).UPDATED_FLAG := cl.UPDATED_FLAG;
l_no_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
FROM fnd_currencies_vl fnd, qp_list_headers_b qlh
WHERE fnd.currency_code = qlh.currency_code AND qlh.list_type_code in (''PRL'', ''AGR'')
AND fnd.currency_flag = ''Y'' AND fnd.enabled_flag = ''Y''';
l_multi_curr_sql VARCHAR2(2000) := 'SELECT distinct fnd.currency_code,fnd.name
FROM fnd_currencies_vl fnd, qp_currency_details qcd, qp_list_headers_b qlh
WHERE qcd.currency_header_id = qlh.currency_header_id AND fnd.currency_code = qcd.to_currency_code
AND qlh.list_type_code in (''PRL'', ''AGR'') and fnd.currency_flag = ''Y''
AND fnd.enabled_flag = ''Y''';
SELECT list_header_id FROM qp_list_headers_b
WHERE list_source_code = 'BSO'
AND orig_system_header_ref = p_blkt_header_id
AND list_type_code = 'PRL';
SELECT list_header_id FROM qp_list_headers_b
WHERE list_source_code = 'BSO'
AND orig_system_header_ref = p_blkt_header_id
AND list_type_code NOT IN ('PRL','AGR');
SELECT 'X'
FROM
QP_PRICING_ATTRIBUTES A,
QP_LIST_LINES L,
QP_LIST_HEADERS_B QLH
WHERE
A.LIST_HEADER_ID = P_Price_List_Id AND
A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
A.PRICING_PHASE_ID = 1 AND
A.QUALIFICATION_IND IN (4,6,20,22) AND
A.EXCLUDER_FLAG = 'N' AND
(EXISTS (SELECT NULL
FROM QP_PRICE_FORMULA_LINES FL
WHERE FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID)
OR
(A.PRICING_ATTRIBUTE_CONTEXT <> 'VOLUME')) AND
L.LIST_LINE_ID = A.LIST_LINE_ID AND
L.LIST_LINE_TYPE_CODE = 'PLL' AND
QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
QLH.LIST_TYPE_CODE = 'PRL' AND
NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
AND
NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
AND
ROWNUM < 2;
SELECT 'X'
FROM
QP_PRICING_ATTRIBUTES A,
QP_LIST_LINES L,
QP_LIST_HEADERS_B QLH
WHERE
A.LIST_HEADER_ID = P_Price_List_Id AND
A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
A.PRICING_PHASE_ID = 1 AND
A.QUALIFICATION_IND IN (4,6,20,22) AND
A.EXCLUDER_FLAG = 'N' AND
(EXISTS (SELECT NULL
FROM QP_PRICE_FORMULA_LINES FL
WHERE FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID)
OR
(A.PRICING_ATTRIBUTE_CONTEXT <> 'VOLUME')) AND
L.LIST_LINE_ID = A.LIST_LINE_ID AND
L.LIST_LINE_TYPE_CODE = 'PLL' AND
QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
QLH.LIST_TYPE_CODE = 'PRL' AND
NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
AND
NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
AND
ROWNUM < 2;
SELECT distinct pricing_attribute_context, pricing_attribute
FROM qp_pricing_attributes
WHERE product_attribute IN ('PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3')
AND product_attribute_context = 'ITEM'
AND product_attr_value IN (to_char(P_Inventory_Id),'ALL')
AND ((pricing_attribute_context IS NOT NULL AND pricing_attribute IS NOT NULL )
AND (pricing_attribute_context <> 'VOLUME'
AND pricing_attribute NOT IN ('PRICING_ATTRIBUTE10','PRICING_ATTRIBUTE12'
))
);
SELECT DISTINCT pricing_attribute_context, pricing_attribute
FROM qp_pricing_attributes
WHERE list_header_id = P_Price_List_Id
AND ( product_attribute IN ('PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3')
AND product_attribute_context = 'ITEM'
AND product_attr_value IN (TO_CHAR(P_Inventory_Id),'ALL')
AND ((pricing_attribute_context IS NOT NULL AND pricing_attribute IS NOT NULL )
AND (pricing_attribute_context <> 'VOLUME'
AND pricing_attribute NOT IN ('PRICING_ATTRIBUTE10','PRICING_ATTRIBUTE12'))) )
UNION ALL
SELECT DISTINCT pricing_attribute_context, pricing_attribute
FROM qp_pricing_attributes
WHERE list_header_id IN (
SELECT FL.price_modifier_list_id
FROM
QP_PRICING_ATTRIBUTES A,
QP_LIST_LINES L,
QP_LIST_HEADERS_B QLH,
QP_PRICE_FORMULA_LINES FL
WHERE
A.LIST_HEADER_ID = P_Price_List_Id AND
A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
A.PRICING_PHASE_ID = 1 AND
A.QUALIFICATION_IND IN (4,6,20,22) AND
A.EXCLUDER_FLAG = 'N' AND
FL.PRICE_FORMULA_LINE_TYPE_CODE IN ('PRA','ML') AND
FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID AND
L.LIST_LINE_ID = A.LIST_LINE_ID AND
L.LIST_LINE_TYPE_CODE = 'PLL' AND
QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
QLH.LIST_TYPE_CODE = 'PRL' AND
NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND
NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
)
UNION ALL
SELECT DISTINCT FL.pricing_attribute_context, FL.pricing_attribute
FROM
QP_PRICING_ATTRIBUTES A,
QP_LIST_LINES L,
QP_LIST_HEADERS_B QLH,
QP_PRICE_FORMULA_LINES FL
WHERE
A.LIST_HEADER_ID = P_Price_List_Id AND
A.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM' AND
A.PRODUCT_ATTRIBUTE IN ( 'PRICING_ATTRIBUTE1','PRICING_ATTRIBUTE3' ) AND
A.PRODUCT_ATTR_VALUE IN ( TO_CHAR(P_Inventory_Id),'ALL' ) AND
L.LIST_LINE_ID = A.LIST_LINE_ID AND
L.LIST_LINE_TYPE_CODE = 'PLL' AND
FL.PRICE_FORMULA_LINE_TYPE_CODE = 'PRA' AND --IN ('PRA','ML') AND, BUG No: 9155255
FL.PRICE_FORMULA_ID = L.PRICE_BY_FORMULA_ID AND
QLH.LIST_HEADER_ID = L.LIST_HEADER_ID AND
QLH.LIST_TYPE_CODE = 'PRL' AND
NVL(QLH.START_DATE_ACTIVE, SYSDATE) <= SYSDATE AND NVL(QLH.END_DATE_ACTIVE,SYSDATE) >= SYSDATE AND
NVL(L.START_DATE_ACTIVE,SYSDATE) <= SYSDATE AND NVL(L.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
;
SELECT 1
INTO l_found
FROM qp_prc_contexts_b con, qp_segments_b seg, qp_pte_segments pte
WHERE con.prc_context_code = v_pricing_attr_ctxt
AND seg.prc_context_id = con.prc_context_id
AND seg.segment_mapping_column = v_pricing_attr
AND seg.segment_id = pte.segment_id
AND pte.pte_code = 'ORDFUL' -- 4055210
AND ROWNUM < 2; -- 4055210
SELECT 1
INTO l_found
FROM qp_prc_contexts_b con, qp_segments_b seg, qp_attribute_sourcing src
WHERE con.prc_context_code = v_pricing_attr_ctxt
AND seg.prc_context_id = con.prc_context_id
AND seg.segment_mapping_column = v_pricing_attr
AND seg.segment_id = src.segment_id
AND src.request_type_code = 'ASO'-- 4055210
AND ROWNUM < 2; -- 4055210
OM needs API to update the lines_tmp table
this API will take care of updating i/f tables java engine is installed
and update temp tables when plsql engine is installed*/
PROCEDURE Update_Lines(p_update_type IN VARCHAR2, p_line_id IN NUMBER,
p_line_index IN NUMBER, p_priced_quantity IN NUMBER) IS
BEGIN
l_debug := Qp_Preq_Grp.G_DEBUG_ENGINE;
||' p_priced_quantity '||p_priced_quantity||' p_update_type '||p_update_type);
IF p_update_type = 'UPDATE_LINE_ID' THEN
IF p_line_index IS NOT NULL THEN
UPDATE qp_npreq_lines_tmp SET line_id = p_line_id
WHERE line_index = p_line_index;
ELSIF p_update_type = 'UPDATE_PRICED_QUANTITY' THEN
IF p_line_id IS NOT NULL THEN
UPDATE qp_npreq_lines_tmp SET priced_quantity = p_priced_quantity
WHERE line_id = p_line_id;
ELSIF p_update_type = 'MAKE_STATUS_INVALID' THEN
IF p_line_id IS NOT NULL THEN
UPDATE qp_npreq_lines_tmp SET process_status = 'NOT_VALID'
WHERE line_id = p_line_id;
||' p_priced_quantity '||p_priced_quantity||' p_update_type '||p_update_type);
IF p_update_type = 'UPDATE_LINE_ID' THEN
IF p_line_index IS NOT NULL THEN
UPDATE qp_int_lines SET line_id = p_line_id
WHERE line_index = p_line_index;
ELSIF p_update_type = 'UPDATE_PRICED_QUANTITY' THEN
IF p_line_id IS NOT NULL THEN
UPDATE qp_int_lines SET priced_quantity = p_priced_quantity
WHERE line_id = p_line_id;
ELSIF p_update_type = 'MAKE_STATUS_INVALID' THEN
IF p_line_id IS NOT NULL THEN
UPDATE qp_int_lines SET process_status = 'NOT_VALID'
WHERE line_id = p_line_id;
END Update_Lines;