The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_HZ_Parties(p_party_id IN NUMBER
, p_party_type IN VARCHAR2
, p_last_update_date IN DATE
, p_party_total IN NUMBER
, p_order_count IN NUMBER
, p_last_order_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE Update_Party_Totals(err_buff OUT NOCOPY VARCHAR2,
retcode out NOCOPY NUMBER)
IS
a_date DATE;
SELECT DISTINCT a.party_id,
a.party_type,
a.last_update_date
FROM hz_cust_accounts b,
hz_parties a
WHERE b.party_id = a.party_id;
SELECT a.header_id,
a.org_id,
a.order_number,
a.TRANSACTIONAL_CURR_CODE,
a.CONVERSION_RATE,
a.CONVERSION_TYPE_CODE,
a.sold_to_org_id,
a.CONVERSION_RATE_DATE,
a.ORDERED_DATE,
a.ORDER_CATEGORY_CODE
FROM hz_cust_accounts b,
oe_order_headers_all a
WHERE b.party_id = p_party_id
AND b.cust_account_id = a.sold_to_org_id
AND a.booked_flag = 'Y'
AND a.cancelled_flag = 'N';
SELECT SUM(DECODE(l.line_category_code,'RETURN',-1,1)*
l.unit_selling_price*l.ordered_quantity)
FROM oe_order_lines_all l
WHERE l.header_id = p_header_id
AND l.cancelled_flag <> 'Y'
AND l.charge_periodicity_code IS NULL; -- Added for Recurring Charges
SELECT SUM( DECODE(p.LINE_ID, NULL,
DECODE(p.CREDIT_OR_CHARGE_FLAG,'C',(-1) * p.OPERAND,p.OPERAND),
DECODE(p.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
(-1) * (P.OPERAND),
(-1) * (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
P.OPERAND,
(L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
)))
FROM oe_price_adjustments p,
oe_order_lines_all l
WHERE p.header_id = p_header_id
AND p.list_line_type_code = 'FREIGHT_CHARGE'
AND p.applied_flag = 'Y'
AND p.line_id = l.line_id(+)
AND DECODE(p.line_id,NULL,'P',l.cancelled_flag)=
DECODE(p.line_id,NULL,'P','N')
AND l.charge_periodicity_code IS NULL; -- Added for Recurring Charges
Update_HZ_Parties(p_party_id => C1.party_id
, p_party_type => C1.party_type
, p_last_update_date => C1.last_update_date
, p_party_total => l_party_total
, p_order_count => l_order_count
, p_last_order_date => l_last_order_date
, x_return_status => l_return_status
);
END Update_Party_Totals;
PROCEDURE Update_HZ_Parties(p_party_id IN NUMBER
, p_party_type IN VARCHAR2
, p_last_update_date IN DATE
, p_party_total IN NUMBER
, p_order_count IN NUMBER
, p_last_order_date IN DATE
, x_return_status OUT NOCOPY VARCHAR2
)
IS
v_errcode NUMBER := 0;
l_date DATE := p_last_update_date;
UPDATE hz_parties
SET total_num_of_orders = p_order_count,
total_ordered_amount = p_party_total,
last_ordered_date = p_last_order_date
WHERE party_id = p_party_id;
END Update_HZ_Parties;