[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT line_id
FROM oe_order_lines_all
WHERE flow_status_code = 'RETURNED' AND
header_id = p_order_header_id AND
line_id = p_order_line_id;
SELECT referral_id, partner_id,
COUNT(*) OVER (PARTITION BY counter) outer_counter
FROM (SELECT referral_id, partner_id, 'x' counter
FROM pv_referrals_b
WHERE order_id = p_order_header_id);
SELECT referral_id, partner_id, referral_status, claim_id,
partner_cust_account_id
FROM pv_referrals_b
WHERE order_id = p_order_header_id AND
claim_id IS NULL
ORDER by creation_date ASC;
SELECT LINE.inventory_item_id
FROM pv_referred_products PROD,
pv_referrals_b REF,
oe_order_lines_all LINE,
mtl_item_categories MIC,
eni_prod_denorm_hrchy_v DENORM,
pv_ge_benefits_vl BENFT
WHERE REF.referral_id = pc_referral_id AND
REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
PROD.referral_id = REF.referral_id AND
LINE.header_id = p_order_header_id AND
LINE.line_id = p_order_line_id AND
LINE.inventory_item_id = MIC.inventory_item_id AND
MIC.category_set_id = DENORM.category_set_id AND
MIC.category_id = DENORM.child_id AND
PROD.product_category_set_id = DENORM.category_set_id AND
PROD.product_category_id = DENORM.parent_id AND
REF.benefit_id = BENFT.benefit_id AND
BENFT.additional_info_1 = p_offer_id;
SELECT *
FROM (
SELECT REF.referral_id, REF.partner_id, REF.partner_cust_account_id
FROM pv_referrals_b REF,
pv_referred_products PROD,
pv_ge_benefits_b BENFT,
oe_order_headers_all HEADER,
oe_order_lines_all LINE,
hz_cust_accounts ACCOUNT,
mtl_item_categories MIC,
eni_prod_denorm_hrchy_v DENORM
WHERE BENFT.additional_info_1 = p_offer_id AND
BENFT.benefit_id = REF.benefit_id AND
REF.referral_id = PROD.referral_id AND
REF.order_id IS NULL AND
REF.claim_id IS NULL AND
REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
HEADER.header_id = p_order_header_id AND
LINE.line_id = p_order_line_id AND
HEADER.header_id = LINE.header_id AND
LINE.flow_status_code <> 'CANCELLED' AND
LINE.inventory_item_id = MIC.inventory_item_id AND
MIC.category_set_id = DENORM.category_set_id AND
MIC.category_id = DENORM.child_id AND
PROD.product_category_set_id = DENORM.category_set_id AND
PROD.product_category_id = DENORM.parent_id AND
HEADER.sold_to_org_id = ACCOUNT.cust_account_id AND
ACCOUNT.party_id = REF.customer_party_id
ORDER BY REF.creation_date ASC
)
WHERE ROWNUM = 1;
SELECT referral_id, partner_cust_account_id, claim_id, 1 outer_counter
FROM pv_referrals_b
WHERE referral_id = 1;
'SELECT referral_id,
partner_cust_account_id,
claim_id,
COUNT(*) OVER (PARTITION BY counter) outer_counter
FROM (
SELECT REF.referral_id,
REF.partner_cust_account_id,
REF.claim_id,
1 counter
FROM pv_referrals_b REF,
pv_referred_products PROD,
pv_ge_benefits_b BENFT,
oe_order_headers_all HEADER,
oe_order_lines_all LINE,
hz_cust_accounts ACCOUNT,
mtl_item_categories MIC,
eni_prod_denorm_hrchy_v DENORM
WHERE BENFT.additional_info_1 = :p_offer_id AND
BENFT.benefit_id = REF.benefit_id AND
REF.referral_id = PROD.referral_id AND
REF.order_id IS NOT NULL AND
HEADER.header_id = :p_order_header_id AND
LINE.line_id = :p_order_line_id AND
HEADER.header_id = LINE.header_id AND
LINE.inventory_item_id = MIC.inventory_item_id AND
MIC.category_set_id = DENORM.category_set_id AND
MIC.category_id = DENORM.child_id AND
PROD.product_category_set_id = DENORM.category_set_id AND
PROD.product_category_id = DENORM.parent_id AND
HEADER.sold_to_org_id = ACCOUNT.cust_account_id AND
ACCOUNT.party_id = REF.customer_party_id AND
REF.creation_date < LINE.creation_date
)';
UPDATE pv_referrals_b
SET order_id = p_order_header_id
WHERE referral_id = x.referral_id AND
order_id IS NULL;
SELECT REF.referral_name,
REF.referral_id,
REF.referral_code,
REF.currency_code,
REF.partner_cust_account_id,
REF.org_id,
REF.partner_id,
BENFT.additional_info_1 offer_id,
BENFT.benefit_id
FROM pv_referrals_vl REF,
pv_ge_benefits_b BENFT
WHERE BENFT.benefit_id = REF.benefit_id AND
REF.order_id > 0 AND
REF.claim_id IS NULL;
SELECT DISTINCT x.order_id, x.line_id, UTL.utilization_id,
UTL.org_id, UTL.exchange_rate_type, UTL.exchange_rate_date,
UTL.exchange_rate, UTL.currency_code
FROM
(SELECT ACCRUAL.plan_type,
ACCRUAL.plan_id,
ACCRUAL.utilization_id,
ACCRUAL.utilization_type,
ACCRUAL.reference_type,
ACCRUAL.reference_id,
ACCRUAL.org_id,
ACCRUAL.cust_account_id,
ACCRUAL.object_id order_header_id,
ACCRUAL.order_line_id,
ACCRUAL.currency_code,
ACCRUAL.exchange_rate_type,
ACCRUAL.exchange_rate_date,
ACCRUAL.exchange_rate
FROM ozf_funds_utilized_all_b ACCRUAL
WHERE ACCRUAL.object_type = 'ORDER'
) UTL,
(SELECT PROD.product_category_id,
REF.order_id,
LINE.line_id,
MIC.inventory_item_id,
LINE.flow_status_code,
OFFER.qp_list_header_id,
REF.referral_id,
REF.partner_cust_account_id
FROM pv_referrals_b REF,
pv_referred_products PROD,
pv_ge_benefits_b BENFT,
oe_order_headers_all HEADER,
oe_order_lines_all LINE,
mtl_item_categories MIC,
eni_prod_denorm_hrchy_v DENORM,
ozf_offers OFFER
WHERE REF.referral_id = pc_referral_id AND
BENFT.benefit_id = REF.benefit_id AND
REF.referral_status IN ('APPROVED', 'MANUAL_EXTEND', 'CLOSED_OPPTY_WON') AND
REF.referral_id = PROD.referral_id AND
REF.order_id = HEADER.header_id AND
HEADER.header_id = LINE.header_id AND
LINE.flow_status_code <> 'CANCELLED' AND
LINE.inventory_item_id = MIC.inventory_item_id AND
MIC.category_set_id = DENORM.category_set_id AND
MIC.category_id = DENORM.child_id AND
PROD.product_category_set_id = DENORM.category_set_id AND
PROD.product_category_id = DENORM.parent_id AND
BENFT.additional_info_1 = OFFER.offer_id
) x
WHERE UTL.plan_type (+) = 'OFFR' AND
UTL.plan_id (+) = x.qp_list_header_id AND -- not offer_id!
UTL.utilization_type (+) = 'LEAD_ACCRUAL' AND
UTL.reference_type (+) = 'LEAD_REFERRAL' AND
UTL.reference_id (+) = x.referral_id AND
UTL.cust_account_id (+) = x.partner_cust_account_id AND
UTL.order_header_id (+) = x.order_id AND
UTL.order_line_id (+) = x.line_id;
FOR z IN (SELECT qp_list_header_id FROM ozf_offers WHERE offer_id = x.offer_id) LOOP
l_promotion_activity_rec.offer_id := z.qp_list_header_id;
UPDATE pv_referrals_b
SET claim_id = l_claim_id,
claim_number = l_claim_number,
actual_compensation_amt = l_claim_amount,
actual_currency_code = l_claim_rec.currency_code,
referral_status = 'COMP_INITIATED'
WHERE referral_id = x.referral_id;
FOR z IN (SELECT name
FROM hr_organization_units
WHERE organization_id = l_claim_rec.org_id)
LOOP
l_operating_unit := z.name;
PROCEDURE Update_Referral_Status (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_offer_id IN NUMBER,
p_pass_validation_flag IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_benefit_status_code VARCHAR2(50);
g_api_name := 'Update_Referral_Status';
UPDATE pv_ge_benefits_b
SET benefit_status_code = l_benefit_status_code
WHERE additional_info_1 = p_offer_id;
END Update_Referral_Status;
SELECT MIN(cust_account_id) cust_account_id
FROM pv_partner_profiles a,
hz_cust_accounts b
WHERE a.partner_id = p_partner_id AND
a.partner_party_id = b.party_id;