[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_EVENT CONSTANT VARCHAR2(30) := 'UPDATE';
G_UPDATE_CALLED boolean := false; -- This variable is used in the update_claim procedure.
SELECT gs.currency_code
FROM gl_sets_of_books gs,
ozf_sys_parameters osp
WHERE gs.set_of_books_id = osp.set_of_books_id
AND osp.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE system_status_type = p_status_type
AND user_status_id = p_user_status_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = p_status_type
AND system_status_code = p_status_code
AND seeded_flag = 'Y';
SELECT claim_number
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
SELECT count(claim_id)
FROM ozf_claims_all
WHERE split_from_claim_id = p_split_from_claim_id;
SELECT TO_CHAR(ams_source_codes_gen_s.NEXTVAL)
FROM DUAL;
SELECT source_code_suffix
FROM ams_custom_setups_b
WHERE custom_setup_id = p_id;
SELECT count(claim_number)
FROM ozf_claims_all
WHERE upper(claim_number) = p_claim_number;
SELECT claim_number
FROM ozf_claims_all
WHERE split_from_claim_id IS NULL
CONNECT BY PRIOR split_from_claim_id = claim_id
START WITH claim_id = p_claim_number;
select a.cust_account_id
FROM HZ_CUST_ACCT_SITES a
, HZ_CUST_SITE_USES s
WHERE a.cust_acct_site_id = s.cust_acct_site_id
and s.site_use_id = p_site_use_id;
SELECT bill_to_site_use_id
FROM HZ_CUST_SITE_USES
WHERE site_use_id = p_shipto_id;
SELECT s.site_use_id -- b illto site
, s.contact_id -- party relation id (do not store this value- see SQL below)
FROM HZ_CUST_ACCT_SITES a
, HZ_CUST_SITE_USES s
WHERE a.cust_account_id = p_cust_acct_id
AND a.cust_acct_site_id = s.cust_acct_site_id
AND s.site_use_code = 'BILL_TO'
AND s.primary_flag = 'Y';
SELECT s.site_use_id --shipto site
FROM HZ_CUST_ACCT_SITES a
, HZ_CUST_SITE_USES s
WHERE a.cust_account_id = p_cust_acct_id
AND a.cust_acct_site_id = s.cust_acct_site_id
AND s.site_use_code = 'SHIP_TO'
AND s.primary_flag = 'Y';
SELECT p.party_id -- ,p.party_name
FROM hz_relationships r -- Bug 4654753
, hz_parties p
, hz_cust_accounts c
WHERE r.subject_id = p.party_id
AND r.relationship_id = p_party_relationship_id -- Bug 4654753
AND r.object_id = c.party_id
AND c.cust_account_id = p_cust_account_id;
SELECT s.primary_salesrep_id -- salesrep id, r.name, r.resource_id -- resource id
FROM HZ_CUST_SITE_USES s
WHERE s.site_use_id = p_site_use_id;
select primary_salesrep_id
from ra_customer_trx_all
where customer_trx_id = p_source_object_id;
select bill_to_contact_id
from ra_customer_trx
where customer_trx_id = p_source_object_id;
select p.party_id
from hz_parties p
, hz_cust_account_roles r
, hz_relationships rel
where r.cust_account_role_id = p_contact_id
and r.party_id = rel.party_id
and rel.subject_id = p.party_id
and SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F';
SELECT days_due
FROM ozf_cust_trd_prfls
WHERE cust_account_id = p_customer_account_id;
SELECT days_due
FROM ozf_sys_parameters
where org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT party_site_id
FROM hz_cust_acct_sites
WHERE cust_acct_site_id = p_site_id;
SELECT party_id, sales_channel_code
FROM hz_cust_accounts
WHERE CUST_ACCOUNT_ID = p_cust_account_id;
SELECT category_code
FROM hz_parties
WHERE party_id = p_party_id;
SELECT profile_class_id
FROM hz_customer_profiles
WHERE cust_account_id = p_account_id
AND site_use_id = p_site_use_id;
SELECT profile_class_id
FROM hz_customer_profiles
WHERE cust_account_id = p_account_id
AND site_use_id IS NULL;
SELECT site_use_code
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id;
SELECT ps.location_id
FROM hz_party_sites ps
, hz_cust_acct_sites ac
, hz_cust_site_uses su
WHERE ps.party_site_id = ac.party_site_id
AND ac.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_id = p_site_id;
SELECT city, county, country, postal_code, state, province
FROM hz_locations
WHERE location_id = p_location_id;
select b.category, b.resource_id
from jtf_rs_group_members a, ams_jtf_rs_emp_v b
where a.resource_id = b.resource_id
and a.group_id = p_id
and a.delete_flag = 'N';
select team_resource_id , c.lead_flag
from jtf_rs_team_members a, jtf_rs_defresroles_vl c
where a.team_member_id = c.role_resource_id(+)
and a.team_id = p_id
and a.delete_flag = 'N'
and c.delete_flag(+) = 'N'
and c.role_resource_type(+) = 'RS_TEAM_MEMBER';
SELECT task_status_id
FROM jtf_tasks_vl
WHERE task_id = p_task_id;
SELECT party_id
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = p_cust_account_id;
SELECT pos_write_off_threshold
, neg_write_off_threshold
FROM OZF_CUST_TRD_PRFLS
WHERE cust_account_id = p_cust_account_id;
SELECT pos_write_off_threshold
, neg_write_off_threshold
FROM OZF_CUST_TRD_PRFLS
WHERE party_id = p_party_id
AND (pos_write_off_threshold is not null
AND neg_write_off_threshold is not null)
AND rownum = 1;
SELECT pos_write_off_threshold
, neg_write_off_threshold
FROM OZF_SYS_PARAMETERS
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT exchange_rate_type
FROM OZF_SYS_PARAMETERS
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT h.party_id
FROM HZ_CUST_ACCOUNTS H
WHERE h.cust_account_id = p_cust_id;
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
, acctd_amount
FROM ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;
SELECT cust_account_id
FROM ozf_funds_utilized_all_b
WHERE plan_id = cv_plan_id
and rownum =1;
SELECT cust_account_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
OZF_CLAIM_ACCRUAL_PVT.Update_Group_Line_Util(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_summary_view => 'ACTIVITY'
,p_funds_util_flt => l_funds_util_flt
);
SELECT currency_code,
exchange_rate_date,
exchange_rate_type,
exchange_rate,
amount,
amount_adjusted,
amount_settled,
cust_account_id
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT count(claim_id)
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT count(claim_id)
FROM ozf_claims_all
WHERE claim_number = p_num;
SELECT count(claim_id)
FROM ozf_claims_all
WHERE claim_number = p_number
AND claim_id = p_claim_id;
select t.task_template_group_id
from ozf_reasons r,
jtf_task_temp_groups_vl t
where t.source_object_type_code = 'AMS_CLAM'
and r.active_flag = 'T'
and r.default_flag = 'T'
and t.task_template_group_id = r.task_template_group_id
and nvl(t.start_date_active, sysdate) <= sysdate
and nvl(t.end_date_active, sysdate) >= sysdate
and r.reason_code_id = p_id;
SELECT currency_code,
exchange_rate_date,
exchange_rate_type,
exchange_rate,
amount,
cust_account_id
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT * FROM ozf_claims_all
WHERE CLAIM_ID = cv_claim_id;
IF p_claim_rec.program_update_date = FND_API.G_MISS_DATE THEN
x_complete_rec.program_update_date := NULL;
IF p_claim_rec.program_update_date IS NULL THEN
x_complete_rec.program_update_date := l_claim_rec.program_update_date;
SELECT custom_setup_id
FROM ams_custom_setups_vl
WHERE object_type = G_CLAIM_OBJECT_TYPE
AND activity_type_code = p_claim_class;
SELECT set_of_books_id
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT gl_date_type
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT exchange_rate_type
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT default_owner_id
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT auto_assign_flag
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT ozf_claims_all_s.nextval FROM dual;
SELECT vendor_id, vendor_site_id
FROM ozf_cust_trd_prfls
WHERE cust_account_id = p_customer_account_id;
SELECT account_name
FROM hz_cust_accounts
WHERE cust_account_id = p_customer_account_id;
SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
FROM dual; */
SELECT count(claim_number)
FROM ozf_claims_all
WHERE upper(claim_number) = p_claim_number;
select count(*)
from ozf_claims
where claim_id =p_id;
select t.task_template_group_id
from ozf_reasons r,
jtf_task_temp_groups_vl t
where t.source_object_type_code = 'OZF_CLAM'
and r.active_flag = 'T'
and r.default_flag = 'T'
and t.task_template_group_id = r.task_template_group_id
and nvl(t.start_date_active, sysdate) <= sysdate
and nvl(t.end_date_active, sysdate) >= sysdate
and r.reason_code_id = p_id;
SELECT
-- Bug#9670818(+)
--orb.batch_type
orb.batch_type, orb.report_date
-- Bug#9670818(-)
FROM
ozf_resale_batches_all orb
WHERE
orb.resale_batch_id = p_resale_batch_id;
SELECT qp_list_header_id
FROM ozf_offers
WHERE offer_code = p_offer_code;
SELECT qp_list_header_id
FROM ozf_offers
WHERE UPPER(offer_code) = UPPER(p_offer_code)
AND ROWNUM = 1
ORDER BY creation_date;
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
, acctd_amount
FROM ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;
SELECT 1 FROM po_vendors pv, po_vendor_sites pvs
WHERE pv.vendor_id = pvs.vendor_id
AND (sysdate between nvl(pv.start_date_active, sysdate-1) and nvl(pv.end_date_active, sysdate+1))
AND (pvs.inactive_date is null or pvs.inactive_date > trunc(sysdate))
AND pv.vendor_id = cv_vendor_id
AND pvs.vendor_site_id = cv_vendor_site_id;
ozf_utility_PVT.debug_message('Before Insert : l_claim.offer_id:'||l_claim.offer_id);
OZF_claims_PKG.Insert_Row(
px_CLAIM_ID => l_claim_id,
px_OBJECT_VERSION_NUMBER => l_object_version_number,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => NVL(FND_GLOBAL.user_id,-1),
p_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.conc_login_id,-1),
p_REQUEST_ID => FND_GLOBAL.CONC_REQUEST_ID,
p_PROGRAM_APPLICATION_ID => FND_GLOBAL.PROG_APPL_ID,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_PROGRAM_ID => FND_GLOBAL.CONC_PROGRAM_ID,
p_CREATED_FROM => l_claim.CREATED_FROM,
p_BATCH_ID => l_claim.BATCH_ID,
p_CLAIM_NUMBER => l_claim.CLAIM_NUMBER,
p_CLAIM_TYPE_ID => l_claim.CLAIM_TYPE_ID,
p_CLAIM_CLASS => l_claim.CLAIM_CLASS,
p_CLAIM_DATE => trunc(l_claim.CLAIM_DATE), -- Added for Bug 7693000
p_DUE_DATE => trunc(l_claim.DUE_DATE), -- Added for Bug 7693000
p_OWNER_ID => l_claim.owner_id,
p_HISTORY_EVENT => G_NEW_EVENT,
p_HISTORY_EVENT_DATE => sysdate,
p_HISTORY_EVENT_DESCRIPTION => G_CREATION_EVENT_DESC,
p_SPLIT_FROM_CLAIM_ID => l_claim.SPLIT_FROM_CLAIM_ID,
p_duplicate_claim_id => l_claim.duplicate_claim_id,
p_SPLIT_DATE => l_claim.SPLIT_DATE,
p_ROOT_CLAIM_ID => l_claim.ROOT_CLAIM_ID,
p_AMOUNT => l_claim.AMOUNT,
p_AMOUNT_ADJUSTED => l_claim.AMOUNT_ADJUSTED,
p_AMOUNT_REMAINING => l_claim.AMOUNT_REMAINING,
p_AMOUNT_SETTLED => l_claim.AMOUNT_SETTLED,
p_ACCTD_AMOUNT => l_claim.ACCTD_AMOUNT,
p_acctd_amount_remaining => l_claim.acctd_amount_remaining,
p_acctd_AMOUNT_ADJUSTED => l_claim.acctd_AMOUNT_ADJUSTED,
p_acctd_AMOUNT_SETTLED => l_claim.acctd_AMOUNT_SETTLED,
p_tax_amount => l_claim.tax_amount,
p_tax_code => l_claim.tax_code,
p_tax_calculation_flag => l_claim.tax_calculation_flag,
p_CURRENCY_CODE => l_claim.CURRENCY_CODE,
p_EXCHANGE_RATE_TYPE => l_claim.EXCHANGE_RATE_TYPE,
p_EXCHANGE_RATE_DATE => l_claim.EXCHANGE_RATE_DATE,
p_EXCHANGE_RATE => l_claim.EXCHANGE_RATE,
p_SET_OF_BOOKS_ID => l_claim.SET_OF_BOOKS_ID,
p_ORIGINAL_CLAIM_DATE => l_claim.ORIGINAL_CLAIM_DATE,
p_SOURCE_OBJECT_ID => l_claim.SOURCE_OBJECT_ID,
p_SOURCE_OBJECT_CLASS => l_claim.SOURCE_OBJECT_CLASS,
p_SOURCE_OBJECT_TYPE_ID => l_claim.SOURCE_OBJECT_TYPE_ID,
p_SOURCE_OBJECT_NUMBER => l_claim.SOURCE_OBJECT_NUMBER,
p_CUST_ACCOUNT_ID => l_claim.CUST_ACCOUNT_ID,
p_CUST_BILLTO_ACCT_SITE_ID => l_claim.CUST_BILLTO_ACCT_SITE_ID,
P_CUST_SHIPTO_ACCT_SITE_ID => L_CLAIM.CUST_SHIPTO_ACCT_SITE_ID,
p_LOCATION_ID => l_claim.LOCATION_ID,
p_PAY_RELATED_ACCOUNT_FLAG => l_claim.PAY_RELATED_ACCOUNT_FLAG,
p_RELATED_CUST_ACCOUNT_ID => l_claim.related_cust_account_id,
p_RELATED_SITE_USE_ID => l_claim.RELATED_SITE_USE_ID,
p_RELATIONSHIP_TYPE => l_claim.RELATIONSHIP_TYPE,
p_VENDOR_ID => l_claim.VENDOR_ID,
p_VENDOR_SITE_ID => l_claim.VENDOR_SITE_ID,
p_REASON_TYPE => l_claim.REASON_TYPE,
p_REASON_CODE_ID => l_claim.REASON_CODE_ID,
p_TASK_TEMPLATE_GROUP_ID => l_claim.TASK_TEMPLATE_GROUP_ID,
p_STATUS_CODE => l_claim.STATUS_CODE,
p_USER_STATUS_ID => l_claim.USER_STATUS_ID,
p_SALES_REP_ID => l_claim.SALES_REP_ID,
p_COLLECTOR_ID => l_claim.COLLECTOR_ID,
p_CONTACT_ID => l_claim.CONTACT_ID,
p_BROKER_ID => l_claim.BROKER_ID,
p_TERRITORY_ID => l_claim.TERRITORY_ID,
p_CUSTOMER_REF_DATE => l_claim.CUSTOMER_REF_DATE,
p_CUSTOMER_REF_NUMBER => l_claim.CUSTOMER_REF_NUMBER,
p_CUSTOMER_REF_NORMALIZED => l_customer_ref_norm,
p_ASSIGNED_TO => l_claim.ASSIGNED_TO,
p_RECEIPT_ID => l_claim.RECEIPT_ID,
p_RECEIPT_NUMBER => l_claim.RECEIPT_NUMBER,
p_DOC_SEQUENCE_ID => l_claim.DOC_SEQUENCE_ID,
p_DOC_SEQUENCE_VALUE => l_claim.DOC_SEQUENCE_VALUE,
p_GL_DATE => trunc(l_claim.gl_date), -- Added for Bug 7693000
p_PAYMENT_METHOD => l_claim.PAYMENT_METHOD,
p_VOUCHER_ID => l_claim.VOUCHER_ID,
p_VOUCHER_NUMBER => l_claim.VOUCHER_NUMBER,
p_PAYMENT_REFERENCE_ID => l_claim.PAYMENT_REFERENCE_ID,
p_PAYMENT_REFERENCE_NUMBER => l_claim.PAYMENT_REFERENCE_NUMBER,
p_PAYMENT_REFERENCE_DATE => l_claim.PAYMENT_REFERENCE_DATE,
p_PAYMENT_STATUS => l_claim.PAYMENT_STATUS,
p_APPROVED_FLAG => l_claim.APPROVED_FLAG,
p_APPROVED_DATE => l_claim.APPROVED_DATE,
p_APPROVED_BY => l_claim.APPROVED_BY,
p_SETTLED_DATE => l_claim.SETTLED_DATE,
p_SETTLED_BY => l_claim.SETTLED_BY,
p_effective_date => l_claim.effective_date,
p_CUSTOM_SETUP_ID => l_claim.CUSTOM_SETUP_ID,
p_TASK_ID => l_claim.TASK_ID,
p_COUNTRY_ID => l_claim.COUNTRY_ID,
p_ORDER_TYPE_ID => l_claim.ORDER_TYPE_ID,
p_COMMENTS => l_claim.COMMENTS,
p_ATTRIBUTE_CATEGORY => l_claim.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_claim.ATTRIBUTE1,
p_ATTRIBUTE2 => l_claim.ATTRIBUTE2,
p_ATTRIBUTE3 => l_claim.ATTRIBUTE3,
p_ATTRIBUTE4 => l_claim.ATTRIBUTE4,
p_ATTRIBUTE5 => l_claim.ATTRIBUTE5,
p_ATTRIBUTE6 => l_claim.ATTRIBUTE6,
p_ATTRIBUTE7 => l_claim.ATTRIBUTE7,
p_ATTRIBUTE8 => l_claim.ATTRIBUTE8,
p_ATTRIBUTE9 => l_claim.ATTRIBUTE9,
p_ATTRIBUTE10 => l_claim.ATTRIBUTE10,
p_ATTRIBUTE11 => l_claim.ATTRIBUTE11,
p_ATTRIBUTE12 => l_claim.ATTRIBUTE12,
p_ATTRIBUTE13 => l_claim.ATTRIBUTE13,
p_ATTRIBUTE14 => l_claim.ATTRIBUTE14,
p_ATTRIBUTE15 => l_claim.ATTRIBUTE15,
p_DEDUCTION_ATTRIBUTE_CATEGORY => l_claim.DEDUCTION_ATTRIBUTE_CATEGORY,
p_DEDUCTION_ATTRIBUTE1 => l_claim.DEDUCTION_ATTRIBUTE1,
p_DEDUCTION_ATTRIBUTE2 => l_claim.DEDUCTION_ATTRIBUTE2,
p_DEDUCTION_ATTRIBUTE3 => l_claim.DEDUCTION_ATTRIBUTE3,
p_DEDUCTION_ATTRIBUTE4 => l_claim.DEDUCTION_ATTRIBUTE4,
p_DEDUCTION_ATTRIBUTE5 => l_claim.DEDUCTION_ATTRIBUTE5,
p_DEDUCTION_ATTRIBUTE6 => l_claim.DEDUCTION_ATTRIBUTE6,
p_DEDUCTION_ATTRIBUTE7 => l_claim.DEDUCTION_ATTRIBUTE7,
p_DEDUCTION_ATTRIBUTE8 => l_claim.DEDUCTION_ATTRIBUTE8,
p_DEDUCTION_ATTRIBUTE9 => l_claim.DEDUCTION_ATTRIBUTE9,
p_DEDUCTION_ATTRIBUTE10 => l_claim.DEDUCTION_ATTRIBUTE10,
p_DEDUCTION_ATTRIBUTE11 => l_claim.DEDUCTION_ATTRIBUTE11,
p_DEDUCTION_ATTRIBUTE12 => l_claim.DEDUCTION_ATTRIBUTE12,
p_DEDUCTION_ATTRIBUTE13 => l_claim.DEDUCTION_ATTRIBUTE13,
p_DEDUCTION_ATTRIBUTE14 => l_claim.DEDUCTION_ATTRIBUTE14,
p_DEDUCTION_ATTRIBUTE15 => l_claim.DEDUCTION_ATTRIBUTE15,
px_ORG_ID => l_org_id,
p_LEGAL_ENTITY_ID => l_claim.legal_entity_id,
p_WRITE_OFF_FLAG => l_claim.WRITE_OFF_FLAG,
p_WRITE_OFF_THRESHOLD_AMOUNT => l_claim.WRITE_OFF_THRESHOLD_AMOUNT,
p_UNDER_WRITE_OFF_THRESHOLD => l_claim.UNDER_WRITE_OFF_THRESHOLD,
p_CUSTOMER_REASON => l_claim.CUSTOMER_REASON,
p_SHIP_TO_CUST_ACCOUNT_ID => l_claim.SHIP_TO_CUST_ACCOUNT_ID,
p_AMOUNT_APPLIED => l_claim.AMOUNT_APPLIED, --BUG:2781186
p_APPLIED_RECEIPT_ID => l_claim.APPLIED_RECEIPT_ID, --BUG:2781186
p_APPLIED_RECEIPT_NUMBER => l_claim.APPLIED_RECEIPT_NUMBER, --BUG:2781186
p_WO_REC_TRX_ID => l_claim.WO_REC_TRX_ID, --Write-off Activity
p_GROUP_CLAIM_ID => l_claim.GROUP_CLAIM_ID,
p_APPR_WF_ITEM_KEY => l_claim.APPR_WF_ITEM_KEY,
p_CSTL_WF_ITEM_KEY => l_claim.CSTL_WF_ITEM_KEY,
p_BATCH_TYPE => l_claim.BATCH_TYPE,
p_OPEN_STATUS_ID => l_claim.open_status_id,
p_close_status_id => l_claim.close_status_id,
-- For Rule Based Settlement
p_pre_auth_ded_number => l_claim.pre_auth_deduction_number,
p_pre_auth_ded_normalized => l_pad_ref_norm,
p_offer_id => l_claim.offer_id,
p_settled_from => l_claim.settled_from,
p_approval_in_prog => l_claim.approval_in_prog
);
l_claim_data := DBMS_XMLGEN.getXml('select claim.claim_id ClaimID,
claim.claim_number ClaimNumber,
claim.owner_id ClaimOwner,
TO_CHAR(claim.claim_date, ''YYYY-MM-DD"T"HH24:MI:SS'')ClaimDate,
claim.claim_type_id ClaimTypeID,
claim.cust_billto_acct_site_id BillToSite,
TO_CHAR(claim.exchange_rate_date, ''YYYY-MM-DD"T"HH24:MI:SS'')ExchangeRateDate,
claim.customer_ref_number CustomerRefNum,
claim.amount ClaimAmount,
claim.currency_code ClaimCurrency,
cust.party_id PartyID,
(claim.cust_account_id || '':'' || cust.party_id) ClaimCustAcctID,
claim.customer_ref_number DebitMemoNumber,
claim.payment_reference_number CreditMemoNumber,
claim.status_code ClaimStatus,
claim.reason_code_id ClaimReasonCode,
claim.payment_method ClaimPayMethod,
claim.payment_status ClaimPayStatus,
TO_CHAR(claim.payment_reference_date, ''YYYY-MM-DD"T"HH24:MI:SS'')PaymentDate,
claim.claim_class ClaimClass,
claim.amount_remaining ClaimAmtRem,
claim.amount_settled ClaimAmtSet,
claim.root_claim_id ClaimParentID,
claim.receipt_id ClaimRecID,
claim.receipt_number ClaimRecNum,
claim.gl_date ClaimGLDate,
claim.attribute_category ClaimAttCat,
claim.attribute1 ClaimAttr1,
claim.attribute2 ClaimAttr2,
claim.attribute3 ClaimAttr3,
claim.attribute4 ClaimAttr4,
claim.attribute5 ClaimAttr5,
claim.attribute6 ClaimAttr6,
claim.attribute7 ClaimAttr7,
claim.attribute8 ClaimAttr8,
claim.attribute9 ClaimAttr9,
claim.attribute10 ClaimAttr10,
claim.attribute11 ClaimAttr11,
claim.attribute12 ClaimAttr12,
claim.attribute13 ClaimAttr13,
claim.attribute14 ClaimAttr14,
claim.attribute15 ClaimAttr15,
claim.deduction_attribute_category CalimDedCat,
claim.deduction_attribute1 ClaimDedAttr1,
claim.deduction_attribute2 ClaimDedAttr2,
claim.deduction_attribute1 ClaimDedAttr3,
claim.deduction_attribute1 ClaimDedAttr4,
claim.deduction_attribute1 ClaimDedAttr5,
claim.deduction_attribute1 ClaimDedAttr6,
claim.deduction_attribute1 ClaimDedAttr7,
claim.deduction_attribute1 ClaimDedAttr8,
claim.deduction_attribute1 ClaimDedAttr9,
claim.deduction_attribute1 ClaimDedAttr10,
claim.deduction_attribute1 ClaimDedAttr11,
claim.deduction_attribute1 ClaimDedAttr12,
claim.deduction_attribute1 ClaimDedAttr13,
claim.deduction_attribute1 ClaimDedAttr14,
claim.deduction_attribute1 ClaimDedAttr15,
type.name ClaimType,
reason.name ClaimReason,
claim.org_id
from ozf_claims_all claim, ozf_claim_types_all_vl type, ozf_reason_codes_all_vl reason,
hz_cust_accounts cust, ozf_sys_parameters_all ozfsys
where
type.claim_type_id = claim.claim_type_id AND
reason.reason_code_id = ozfsys.reason_code_id AND
claim.cust_account_id = cust.cust_account_id AND
claim.org_id = ozfsys.org_id AND
claim.claim_id ='|| l_claim_id);
ozf_utility_PVT.debug_message('After Insert : l_claim.offer_id:'||l_claim.offer_id);
ozf_utility_PVT.debug_message('After Insert : l_claim.claim_class:'||l_claim.claim_class);
OZF_claim_PVT.Update_claim(
P_Api_Version => l_api_version,
P_Init_Msg_List => FND_API.g_false,
P_Commit => FND_API.g_false,
P_Validation_Level => FND_API.g_valid_level_full,
X_Return_Status => l_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data,
P_claim => l_claim,
p_event => 'UPDATE',
p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
X_Object_Version_Number => l_object_version_number
);
PROCEDURE Validate_Delete_Claim (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_object_id IN NUMBER,
p_object_version_number IN NUMBER,
x_dependent_object_tbl OUT NOCOPY ams_utility_pvt.dependent_objects_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Delete_Claim';
SELECT object_version_number, owner_id, status_code
FROM ozf_claims_v
WHERE claim_id = p_id;
SELECT FULL_NAME
FROM AMS_JTF_RS_EMP_V
WHERE RESOURCE_ID = p_id;
SELECT line_number
FROM ozf_claim_lines
WHERE claim_id = p_id;
select count(*)
from ozf_claim_lines
where claim_id = p_id;
SELECT jta.task_number,
jtst.name task_status,
substrb(jtf_task_utl.get_owner(jta.owner_type_code, jta.owner_id),1,239) owner_name
FROM jtf_tasks_b jta, jtf_task_statuses_tl jtst
WHERE jta.source_object_type_code = G_CLAIM_TYPE
AND jta.source_object_id = p_id
AND jtst.language = userenv('lang')
AND jtst.task_status_id = jta.task_status_id;
SELECT jta.task_number,
jtst.name task_status,
substrb(jtf_task_utl.get_owner(jta.owner_type_code, jta.owner_id),1,239) owner_name
FROM jtf_tasks_b jta, jtf_task_statuses_tl jtst
WHERE jta.source_object_type_code = G_OBJECT_TYPE
AND jta.source_object_id = p_id
AND jtst.language = userenv('lang')
AND jtst.task_status_id = jta.task_status_id;
select count(task_id)
from jtf_tasks_b
WHERE source_object_type_code = G_CLAIM_TYPE
AND source_object_id = p_id;
select count(task_id)
from jtf_tasks_b
WHERE source_object_type_code = G_OBJECT_TYPE
AND source_object_id = p_id;
SAVEPOINT Val_Delete_Claim_PVT;
ROLLBACK TO Val_Delete_Claim_PVT;
ROLLBACK TO Val_Delete_Claim_PVT;
ROLLBACK TO Val_Delete_Claim_PVT;
End Validate_Delete_Claim;
PROCEDURE Delete_Claim (
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_object_id IN NUMBER
,p_object_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Claim';
SELECT object_version_number, status_code
FROM ozf_claims_all
WHERE claim_id = p_id;
select claim_line_id, object_version_number
from ozf_claim_lines_all
where claim_id = p_id;
SELECT task_id, object_version_number
FROM jtf_tasks_b
WHERE source_object_type_code = G_CLAIM_TYPE
AND source_object_id = p_id;
SELECT task_id, object_version_number
FROM jtf_tasks_b
WHERE source_object_type_code = G_OBJECT_TYPE
AND source_object_id = p_id;
select claim_history_id, object_version_number
from ozf_claims_history_all
where claim_id = p_id;
select claim_line_history_id, object_version_number
from OZF_CLAIM_LINES_HIST_ALL
where claim_id = p_id;
SAVEPOINT Delete_Claim_PVT;
JTF_TASKS_PUB.delete_task(
p_api_version => l_api_version
,p_object_version_number => l_tasks_id_tbl(i).object_version_number
,p_task_id => l_tasks_id_tbl(i).task_id
,p_delete_future_recurrences => FND_API.G_TRUE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
OZF_claims_history_PVT.Delete_claims_history(
P_Api_Version_Number => l_api_version
,P_Init_Msg_List => FND_API.g_false
,P_Commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,X_Return_Status => l_return_status
,X_Msg_Count => l_msg_count
,X_Msg_Data => l_msg_data
,P_CLAIM_HISTORY_ID => l_claim_history_id
,P_Object_Version_Number => l_object_version_number
);
OZF_Claim_Line_Hist_PVT.Delete_Claim_Line_Hist(
p_api_version_number => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_history_id => l_claim_line_hist_id
,p_object_version_number => l_object_version_number
);
OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_tbl => l_claim_line_tbl
,p_change_object_version => FND_API.g_false
,x_error_index => l_error_index
);
OZF_claims_PKG.Delete_Row(p_object_id);
ROLLBACK TO Delete_Claim_PVT;
ROLLBACK TO Delete_Claim_PVT;
ROLLBACK TO Delete_Claim_PVT;
END Delete_Claim;
SELECT reason_code_id, task_template_group_id
FROM ozf_claims_all
WHERE claim_id = p_id;
select user_status_id
from ozf_claims_all
where claim_id = p_id;
PROCEDURE Update_Claim (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim IN claim_rec_type
,p_event IN VARCHAR2
,p_mode IN VARCHAR2 := OZF_claim_Utility_pvt.G_AUTO_MODE
,x_object_version_number OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim';
select NVL(sum(claim_currency_amount), 0)
from ozf_claim_lines_all
where claim_id = p_id;
select object_version_number
from ozf_claims_all
where claim_id = p_id;
SELECT a.task_id, a.object_version_number
FROM jtf_tasks_vl a, jtf_task_statuses_vl b
WHERE b.completed_flag = p_completed_flag
AND a.deleted_flag = 'N'
AND a.task_status_id = b.task_status_id
AND a.source_object_type_code = G_OBJECT_TYPE
AND a.source_object_id = p_claim_id;
SELECT claim_history_id, object_version_number
FROM ozf_claims_history_all
WHERE task_source_object_id = p_claim_id
AND task_source_object_type_code = G_CLAIM_TYPE
AND claim_id = p_claim_id;
SELECT user_status_id,
status_code,
reason_code_id,
task_template_group_id,
cust_account_id,
owner_id,
customer_ref_number,
customer_ref_normalized,
write_off_flag,
pre_auth_deduction_number,
offer_id,
amount,
payment_status --ninarasi fix for bug 13395562
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT *
FROM ozf_claim_lines_all
WHERE claim_id = p_id;
SELECT primary_salesrep_id
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = p_customer_account_id;
SELECT user_status_id
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT status_code
FROM ozf_claims_all
WHERE claim_id = p_id;
SELECT auto_assign_flag
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT default_owner_id
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = p_user_id
AND category = 'EMPLOYEE';
SELECT tax_amount
FROM ozf_claims_all
WHERE claim_id = p_id;
select activity_access_id, object_version_number
FROM ams_act_access
WHERE arc_act_access_to_object = 'CLAM'
and act_access_to_object_id = p_id;
select activity_access_id, object_version_number
FROM ams_act_access
WHERE arc_act_access_to_object = 'CLAM'
and act_access_to_object_id = p_claim_id
and user_or_role_id = p_user_id
and arc_user_or_role_type = 'USER'
and rownum =1;
select a.cust_account_id
FROM HZ_CUST_ACCT_SITES a
, HZ_CUST_SITE_USES s
WHERE a.cust_acct_site_id = s.cust_acct_site_id
and s.site_use_id = p_site_use_id;
SELECT open_status_id,
close_status_id
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
SELECT
orb.batch_type
FROM
ozf_resale_batches_all orb
WHERE
orb.resale_batch_id = cv_resale_batch_id;
SELECT
exchange_rate,
exchange_rate_type
FROM
ozf_claims_all
WHERE
claim_id = cv_claim_id;
SELECT count(cln.claim_id)
FROM ozf_claims_all cla,
ozf_claim_lines_all cln
WHERE cla.claim_id = cln.claim_id
AND cla.claim_id = p_claim_id;
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
, acctd_amount
FROM ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;
SELECT claim_line_id, object_version_number
FROM ozf_claim_lines_all
WHERE claim_id = p_id;
SELECT source_object_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT count(*)
FROM ozf_claim_lines_all cln, ozf_claims_all cla
WHERE cla.claim_id = cln.claim_id
AND cla.source_object_id = cln.source_object_id
AND cla.claim_id = cv_claim_id
GROUP BY cln.source_object_id;
SELECT offer_code
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT activity_id
FROM ozf_claim_lines_all
WHERE claim_id = p_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
l_claim_update_data CLOB;
l_item_key_update VARCHAR2(50);
l_event_name_update VARCHAR2(80);
l_parameter_list_update wf_parameter_list_t;
SAVEPOINT Update_Claim_PVT;
FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_UPDATE_ERROR');
AMS_ACCESS_PVT.delete_access(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_validation_level => p_validation_level
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_commit => fnd_api.g_false
,p_access_id =>l_access_id
,p_object_version =>l_access_obj_ver
);
AMS_ACCESS_PVT.delete_access(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_validation_level => p_validation_level
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_commit => fnd_api.g_false
,p_access_id =>l_access_id
,p_object_version =>l_access_obj_ver
);
AMS_ACCESS_PVT.delete_access(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_validation_level => p_validation_level
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_commit => fnd_api.g_false
,p_access_id =>l_claim_access_list(i).activity_access_id
,p_object_version =>l_claim_access_list(i).object_version_number
);
FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_UPDATE_OWNER_ERR');
OZF_Utility_PVT.debug_message('Delete claim line');
OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_tbl => l_claim_line_tbl
,p_change_object_version => FND_API.g_false
,x_error_index => l_error_index
);
END IF; -- End of delete claim line
OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_tbl => l_claim_line_tbl
,p_change_object_version => FND_API.g_false
,x_error_index => l_error_index
);
END IF; -- End of delete claim line
FND_MESSAGE.Set_Name('OZF','OZF_DED_LINE_UPDATED');
update ozf_claims_all set status_code = 'OPEN'
where claim_id = l_claim.claim_id;
OZF_CLAIM_LINE_PVT.Update_Line_Fm_Claim(
p_api_version => l_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,p_new_claim_rec => l_claim
);
OZF_claims_PKG.Update_Row(
p_CLAIM_ID => l_claim.CLAIM_ID,
p_OBJECT_VERSION_NUMBER => l_object_version_number,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
p_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.conc_login_id,-1),
p_REQUEST_ID => l_claim.request_id,
p_PROGRAM_APPLICATION_ID => l_claim.program_application_id,
p_PROGRAM_UPDATE_DATE => l_claim.program_update_date,
p_PROGRAM_ID => l_claim.program_id,
p_CREATED_FROM => l_claim.CREATED_FROM,
p_BATCH_ID => l_claim.BATCH_ID,
p_CLAIM_NUMBER => l_claim.CLAIM_NUMBER,
p_CLAIM_TYPE_ID => l_claim.CLAIM_TYPE_ID,
p_CLAIM_CLASS => l_claim.CLAIM_CLASS,
p_CLAIM_DATE => trunc(l_claim.CLAIM_DATE), -- Added for Bug 7693000
p_DUE_DATE => trunc(l_claim.DUE_DATE), -- Added for Bug 7693000
p_OWNER_ID => l_claim.OWNER_ID,
p_HISTORY_EVENT => l_claim.hISTORY_EVENT,
-- For Bug#9217894 (+)
-- p_HISTORY_EVENT_DATE => l_claim.HISTORY_EVENT_DATE,
p_HISTORY_EVENT_DATE => SYSDATE,
-- For Bug#9217894 (-)
p_HISTORY_EVENT_DESCRIPTION => l_claim.HISTORY_EVENT_DESCRIPTION,
p_SPLIT_FROM_CLAIM_ID => l_claim.SPLIT_FROM_CLAIM_ID,
p_duplicate_claim_id => l_claim.duplicate_claim_id,
p_SPLIT_DATE => l_claim.SPLIT_DATE,
p_ROOT_CLAIM_ID => l_claim.ROOT_CLAIM_ID,
p_AMOUNT => l_claim.AMOUNT,
p_AMOUNT_ADJUSTED => l_claim.AMOUNT_ADJUSTED,
p_AMOUNT_REMAINING => l_claim.AMOUNT_REMAINING,
p_AMOUNT_SETTLED => l_claim.AMOUNT_SETTLED,
p_ACCTD_AMOUNT => l_claim.ACCTD_AMOUNT,
p_acctd_amount_remaining => l_claim.acctd_amount_remaining,
p_acctd_AMOUNT_ADJUSTED => l_claim.acctd_AMOUNT_ADJUSTED,
p_acctd_AMOUNT_SETTLED => l_claim.acctd_AMOUNT_SETTLED,
p_tax_amount => l_claim.tax_amount,
p_tax_code => l_claim.tax_code,
p_tax_calculation_flag => l_claim.tax_calculation_flag,
p_CURRENCY_CODE => l_claim.CURRENCY_CODE,
p_EXCHANGE_RATE_TYPE => l_claim.EXCHANGE_RATE_TYPE,
p_EXCHANGE_RATE_DATE => l_claim.EXCHANGE_RATE_DATE,
p_EXCHANGE_RATE => l_claim.EXCHANGE_RATE,
p_SET_OF_BOOKS_ID => l_claim.SET_OF_BOOKS_ID,
p_ORIGINAL_CLAIM_DATE => l_claim.ORIGINAL_CLAIM_DATE,
p_SOURCE_OBJECT_ID => l_claim.SOURCE_OBJECT_ID,
p_SOURCE_OBJECT_CLASS => l_claim.SOURCE_OBJECT_CLASS,
p_SOURCE_OBJECT_TYPE_ID => l_claim.SOURCE_OBJECT_TYPE_ID,
p_SOURCE_OBJECT_NUMBER => l_claim.SOURCE_OBJECT_NUMBER,
p_CUST_ACCOUNT_ID => l_claim.CUST_ACCOUNT_ID,
p_CUST_BILLTO_ACCT_SITE_ID => l_claim.CUST_BILLTO_ACCT_SITE_ID,
p_cust_shipto_acct_site_id => l_claim.cust_shipto_acct_site_id,
p_LOCATION_ID => l_claim.LOCATION_ID,
p_PAY_RELATED_ACCOUNT_FLAG => l_claim.PAY_RELATED_ACCOUNT_FLAG,
p_RELATED_CUST_ACCOUNT_ID => l_claim.related_cust_account_id,
p_RELATED_SITE_USE_ID => l_claim.RELATED_SITE_USE_ID,
p_RELATIONSHIP_TYPE => l_claim.RELATIONSHIP_TYPE,
p_VENDOR_ID => l_claim.VENDOR_ID,
p_VENDOR_SITE_ID => l_claim.VENDOR_SITE_ID,
p_REASON_TYPE => l_claim.REASON_TYPE,
p_REASON_CODE_ID => l_claim.REASON_CODE_ID,
p_TASK_TEMPLATE_GROUP_ID => l_claim.TASK_TEMPLATE_GROUP_ID,
p_STATUS_CODE => l_claim.STATUS_CODE,
p_USER_STATUS_ID => l_claim.USER_STATUS_ID,
p_SALES_REP_ID => l_claim.SALES_REP_ID,
p_COLLECTOR_ID => l_claim.COLLECTOR_ID,
p_CONTACT_ID => l_claim.CONTACT_ID,
p_BROKER_ID => l_claim.BROKER_ID,
p_TERRITORY_ID => l_claim.TERRITORY_ID,
p_CUSTOMER_REF_DATE => l_claim.CUSTOMER_REF_DATE,
p_CUSTOMER_REF_NUMBER => l_claim.CUSTOMER_REF_NUMBER,
p_CUSTOMER_REF_NORMALIZED => l_customer_ref_norm,
p_ASSIGNED_TO => l_claim.ASSIGNED_TO,
p_RECEIPT_ID => l_claim.RECEIPT_ID,
p_RECEIPT_NUMBER => l_claim.RECEIPT_NUMBER,
p_DOC_SEQUENCE_ID => l_claim.DOC_SEQUENCE_ID,
p_DOC_SEQUENCE_VALUE => l_claim.DOC_SEQUENCE_VALUE,
p_GL_DATE => trunc(l_claim.gl_date), -- Added for Bug 7693000
p_PAYMENT_METHOD => l_claim.PAYMENT_METHOD,
p_VOUCHER_ID => l_claim.VOUCHER_ID,
p_VOUCHER_NUMBER => l_claim.VOUCHER_NUMBER,
p_PAYMENT_REFERENCE_ID => l_claim.PAYMENT_REFERENCE_ID,
p_PAYMENT_REFERENCE_NUMBER => l_claim.PAYMENT_REFERENCE_NUMBER,
p_PAYMENT_REFERENCE_DATE => l_claim.PAYMENT_REFERENCE_DATE,
p_PAYMENT_STATUS => l_claim.PAYMENT_STATUS,
p_APPROVED_FLAG => l_claim.APPROVED_FLAG,
p_APPROVED_DATE => l_claim.APPROVED_DATE,
p_APPROVED_BY => l_claim.APPROVED_BY,
p_SETTLED_DATE => l_claim.SETTLED_DATE,
p_SETTLED_BY => l_claim.SETTLED_BY,
p_effective_date => l_claim.effective_date,
p_CUSTOM_SETUP_ID => l_claim.CUSTOM_SETUP_ID,
p_TASK_ID => l_claim.TASK_ID,
p_COUNTRY_ID => l_claim.COUNTRY_ID,
p_ORDER_TYPE_ID => l_claim.ORDER_TYPE_ID,
p_COMMENTS => l_claim.COMMENTS,
p_ATTRIBUTE_CATEGORY => l_claim.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => l_claim.ATTRIBUTE1,
p_ATTRIBUTE2 => l_claim.ATTRIBUTE2,
p_ATTRIBUTE3 => l_claim.ATTRIBUTE3,
p_ATTRIBUTE4 => l_claim.ATTRIBUTE4,
p_ATTRIBUTE5 => l_claim.ATTRIBUTE5,
p_ATTRIBUTE6 => l_claim.ATTRIBUTE6,
p_ATTRIBUTE7 => l_claim.ATTRIBUTE7,
p_ATTRIBUTE8 => l_claim.ATTRIBUTE8,
p_ATTRIBUTE9 => l_claim.ATTRIBUTE9,
p_ATTRIBUTE10 => l_claim.ATTRIBUTE10,
p_ATTRIBUTE11 => l_claim.ATTRIBUTE11,
p_ATTRIBUTE12 => l_claim.ATTRIBUTE12,
p_ATTRIBUTE13 => l_claim.ATTRIBUTE13,
p_ATTRIBUTE14 => l_claim.ATTRIBUTE14,
p_ATTRIBUTE15 => l_claim.ATTRIBUTE15,
p_DEDUCTION_ATTRIBUTE_CATEGORY => l_claim.DEDUCTION_ATTRIBUTE_CATEGORY,
p_DEDUCTION_ATTRIBUTE1 => l_claim.DEDUCTION_ATTRIBUTE1,
p_DEDUCTION_ATTRIBUTE2 => l_claim.DEDUCTION_ATTRIBUTE2,
p_DEDUCTION_ATTRIBUTE3 => l_claim.DEDUCTION_ATTRIBUTE3,
p_DEDUCTION_ATTRIBUTE4 => l_claim.DEDUCTION_ATTRIBUTE4,
p_DEDUCTION_ATTRIBUTE5 => l_claim.DEDUCTION_ATTRIBUTE5,
p_DEDUCTION_ATTRIBUTE6 => l_claim.DEDUCTION_ATTRIBUTE6,
p_DEDUCTION_ATTRIBUTE7 => l_claim.DEDUCTION_ATTRIBUTE7,
p_DEDUCTION_ATTRIBUTE8 => l_claim.DEDUCTION_ATTRIBUTE8,
p_DEDUCTION_ATTRIBUTE9 => l_claim.DEDUCTION_ATTRIBUTE9,
p_DEDUCTION_ATTRIBUTE10 => l_claim.DEDUCTION_ATTRIBUTE10,
p_DEDUCTION_ATTRIBUTE11 => l_claim.DEDUCTION_ATTRIBUTE11,
p_DEDUCTION_ATTRIBUTE12 => l_claim.DEDUCTION_ATTRIBUTE12,
p_DEDUCTION_ATTRIBUTE13 => l_claim.DEDUCTION_ATTRIBUTE13,
p_DEDUCTION_ATTRIBUTE14 => l_claim.DEDUCTION_ATTRIBUTE14,
p_DEDUCTION_ATTRIBUTE15 => l_claim.DEDUCTION_ATTRIBUTE15,
-- Bug 3313062 Fixing: ORG_ID cannot be set to null at any time.
p_ORG_ID => l_claim.org_id, -- R12 Enhancements
p_LEGAL_ENTITY_ID => l_claim.legal_entity_id, -- R12 Enhancements
p_WRITE_OFF_FLAG => l_claim.WRITE_OFF_FLAG,
p_WRITE_OFF_THRESHOLD_AMOUNT => l_claim.WRITE_OFF_THRESHOLD_AMOUNT,
p_UNDER_WRITE_OFF_THRESHOLD => l_claim.UNDER_WRITE_OFF_THRESHOLD,
p_CUSTOMER_REASON => l_claim.CUSTOMER_REASON,
p_SHIP_TO_CUST_ACCOUNT_ID => l_claim.SHIP_TO_CUST_ACCOUNT_ID,
p_AMOUNT_APPLIED => l_claim.AMOUNT_APPLIED, --BUG:2781186
p_APPLIED_RECEIPT_ID => l_claim.APPLIED_RECEIPT_ID, --BUG:2781186
p_APPLIED_RECEIPT_NUMBER => l_claim.APPLIED_RECEIPT_NUMBER, --BUG:2781186
p_WO_REC_TRX_ID => l_claim.WO_REC_TRX_ID, --Write-off Activity
p_GROUP_CLAIM_ID => l_claim.GROUP_CLAIM_ID,
p_APPR_WF_ITEM_KEY => l_claim.APPR_WF_ITEM_KEY,
p_CSTL_WF_ITEM_KEY => l_claim.CSTL_WF_ITEM_KEY,
p_BATCH_TYPE => l_claim.BATCH_TYPE,
p_close_status_id => l_claim.close_status_id,
p_open_status_id => l_claim.open_status_id,
--For Rule Based Settlement
p_pre_auth_ded_number => l_claim.pre_auth_deduction_number,
p_pre_auth_ded_normalized => l_claim.pre_auth_deduction_normalized,
p_offer_id => l_claim.offer_id,
p_settled_from => l_claim.settled_from,
p_approval_in_prog => l_claim.approval_in_prog
);
UPDATE ozf_claims_all
SET close_status_id = l_user_sel_status_code_id
WHERE claim_id = l_claim.claim_id;
UPDATE ozf_claims_all
SET user_status_id = close_status_id
WHERE claim_id = l_claim.claim_id
AND close_status_id IS NOT NULL;
JTF_TASKS_PUB.delete_task(
p_api_version => l_api_version
,p_object_version_number => l_uncompleted_tasks_tbl(i).object_version_number
,p_task_id => l_uncompleted_tasks_tbl(i).task_id
,p_delete_future_recurrences => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
JTF_TASKS_PUB.update_task(
p_api_version => l_api_version
,p_object_version_number => l_completed_tasks_tbl(i).object_version_number
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_task_id => l_completed_tasks_tbl(i).task_id
,p_source_object_type_code =>G_CLAIM_HISTORY_TYPE
,p_source_object_id => l_claim_history_id
);
update jtf_tasks_b
set source_object_id =l_claim_history_id,
source_object_type_code = G_CLAIM_HISTORY_TYPE,
source_object_name = l_claim.claim_number
where source_object_id = l_claim.claim_id
and source_object_type_code = G_OBJECT_TYPE;
update ozf_claims_history_all
set task_source_object_id = l_claim_history_id,
task_source_object_type_code = G_CLAIM_HISTORY_TYPE
where task_source_object_id = l_claim.claim_id
and task_source_object_id = G_OBJECT_TYPE
and claim_id = l_claim.claim_id;
OZF_claims_history_PVT.Update_claims_history(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
P_CLAIMS_HISTORY_Rec => l_claim_history_rec,
X_Object_Version_Number => l_hist_obj_ver_num
);
,p_event_name => 'oracle.apps.ozf.claim.updateStatus'
);
l_item_key_update := l_claim.claim_id || TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
l_parameter_list_update := WF_PARAMETER_LIST_T();
l_claim_update_data := dbms_xmlgen.getXml('select claim.claim_id ClaimID,
claim.amount ClaimAmount,
claim.status_code ClaimStatus,
(claim.cust_account_id || '':'' || cust.party_id)
ClaimCustAcctID,
claim.receipt_id ClaimRecID,
claim.receipt_number ClaimRecNum,
claim.gl_date ClaimGLDate,
claim.attribute_category ClaimAttCat,
claim.attribute1 ClaimAttr1,
claim.attribute2 ClaimAttr2,
claim.attribute3 ClaimAttr3,
claim.attribute4 ClaimAttr4,
claim.attribute5 ClaimAttr5,
claim.attribute6 ClaimAttr6,
claim.attribute7 ClaimAttr7,
claim.attribute8 ClaimAttr8,
claim.attribute9 ClaimAttr9,
claim.attribute10 ClaimAttr10,
claim.attribute11 ClaimAttr11,
claim.attribute12 ClaimAttr12,
claim.attribute13 ClaimAttr13,
claim.attribute14 ClaimAttr14,
claim.attribute15 ClaimAttr15,
claim.deduction_attribute_category CalimDedCat,
claim.deduction_attribute1 ClaimDedAttr1,
claim.deduction_attribute2 ClaimDedAttr2,
claim.deduction_attribute1 ClaimDedAttr3,
claim.deduction_attribute1 ClaimDedAttr4,
claim.deduction_attribute1 ClaimDedAttr5,
claim.deduction_attribute1 ClaimDedAttr6,
claim.deduction_attribute1 ClaimDedAttr7,
claim.deduction_attribute1 ClaimDedAttr8,
claim.deduction_attribute1 ClaimDedAttr9,
claim.deduction_attribute1 ClaimDedAttr10,
claim.deduction_attribute1 ClaimDedAttr11,
claim.deduction_attribute1 ClaimDedAttr12,
claim.deduction_attribute1 ClaimDedAttr13,
claim.deduction_attribute1 ClaimDedAttr14,
claim.deduction_attribute1 ClaimDedAttr15
from ozf_claims_all claim, hz_cust_accounts cust
Where
claim.cust_account_id = cust.cust_account_id AND
claim.claim_id = ' || l_claim.claim_id);
l_claim_update_data := dbms_xmlgen.getXml('select claim.claim_id ClaimID,
''0'' ClaimAmount,
claim.status_code ClaimStatus,
(claim.cust_account_id || '':'' || cust.party_id)
ClaimCustAcctID,
claim.receipt_id ClaimRecID,
claim.receipt_number ClaimRecNum,
claim.gl_date ClaimGLDate,
claim.attribute_category ClaimAttCat,
claim.attribute1 ClaimAttr1,
claim.attribute2 ClaimAttr2,
claim.attribute3 ClaimAttr3,
claim.attribute4 ClaimAttr4,
claim.attribute5 ClaimAttr5,
claim.attribute6 ClaimAttr6,
claim.attribute7 ClaimAttr7,
claim.attribute8 ClaimAttr8,
claim.attribute9 ClaimAttr9,
claim.attribute10 ClaimAttr10,
claim.attribute11 ClaimAttr11,
claim.attribute12 ClaimAttr12,
claim.attribute13 ClaimAttr13,
claim.attribute14 ClaimAttr14,
claim.attribute15 ClaimAttr15,
claim.deduction_attribute_category CalimDedCat,
claim.deduction_attribute1 ClaimDedAttr1,
claim.deduction_attribute2 ClaimDedAttr2,
claim.deduction_attribute1 ClaimDedAttr3,
claim.deduction_attribute1 ClaimDedAttr4,
claim.deduction_attribute1 ClaimDedAttr5,
claim.deduction_attribute1 ClaimDedAttr6,
claim.deduction_attribute1 ClaimDedAttr7,
claim.deduction_attribute1 ClaimDedAttr8,
claim.deduction_attribute1 ClaimDedAttr9,
claim.deduction_attribute1 ClaimDedAttr10,
claim.deduction_attribute1 ClaimDedAttr11,
claim.deduction_attribute1 ClaimDedAttr12,
claim.deduction_attribute1 ClaimDedAttr13,
claim.deduction_attribute1 ClaimDedAttr14,
claim.deduction_attribute1 ClaimDedAttr15,
claim.org_id
from ozf_claims_all claim, hz_cust_accounts cust
Where
claim.cust_account_id = cust.cust_account_id AND
claim.claim_id = ' || l_claim.claim_id);
l_event_name_update := 'oracle.apps.ozf.bpel.claim';
wf_event.raise(p_event_name => l_event_name_update,
p_event_key => l_item_key_update,
p_event_data => l_claim_update_data,
p_parameters => l_parameter_list_update,
p_send_date => sysdate);
l_claim_amount_data := dbms_xmlgen.getXml('select claim.claim_id ClaimID,
claim.amount ClaimAmount,
claim.status_code ClaimStatus,
(claim.cust_account_id || '':'' || cust.party_id)
ClaimCustAcctID,
claim.receipt_id ClaimRecID,
claim.receipt_number ClaimRecNum,
claim.gl_date ClaimGLDate,
claim.attribute_category ClaimAttCat,
claim.attribute1 ClaimAttr1,
claim.attribute2 ClaimAttr2,
claim.attribute3 ClaimAttr3,
claim.attribute4 ClaimAttr4,
claim.attribute5 ClaimAttr5,
claim.attribute6 ClaimAttr6,
claim.attribute7 ClaimAttr7,
claim.attribute8 ClaimAttr8,
claim.attribute9 ClaimAttr9,
claim.attribute10 ClaimAttr10,
claim.attribute11 ClaimAttr11,
claim.attribute12 ClaimAttr12,
claim.attribute13 ClaimAttr13,
claim.attribute14 ClaimAttr14,
claim.attribute15 ClaimAttr15,
claim.deduction_attribute_category CalimDedCat,
claim.deduction_attribute1 ClaimDedAttr1,
claim.deduction_attribute2 ClaimDedAttr2,
claim.deduction_attribute1 ClaimDedAttr3,
claim.deduction_attribute1 ClaimDedAttr4,
claim.deduction_attribute1 ClaimDedAttr5,
claim.deduction_attribute1 ClaimDedAttr6,
claim.deduction_attribute1 ClaimDedAttr7,
claim.deduction_attribute1 ClaimDedAttr8,
claim.deduction_attribute1 ClaimDedAttr9,
claim.deduction_attribute1 ClaimDedAttr10,
claim.deduction_attribute1 ClaimDedAttr11,
claim.deduction_attribute1 ClaimDedAttr12,
claim.deduction_attribute1 ClaimDedAttr13,
claim.deduction_attribute1 ClaimDedAttr14,
claim.deduction_attribute1 ClaimDedAttr15,
claim.org_id
from ozf_claims_all claim, hz_cust_accounts cust
Where
claim.cust_account_id = cust.cust_account_id AND
claim.claim_id = ' || l_claim.claim_id);
ROLLBACK TO Update_Claim_PVT;
ROLLBACK TO Update_Claim_PVT;
ROLLBACK TO Update_Claim_PVT;
END Update_Claim;
SELECT count(p.party_id)
FROM hz_parties p,
hz_relationships r,
hz_cust_accounts c
WHERE p.party_id = r.subject_id
AND r.relationship_code = p_relation_type
AND r.object_id = c.party_id
AND c.cust_account_id = p_cust_id
AND p.party_id = p_party_id;
SELECT count(hcsu.site_use_id)
FROM hz_cust_accounts hca,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu
WHERE hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.status = 'A'
AND hca.cust_account_id = p_cust_id
AND hcsu.site_use_code = p_site_use_code
AND hcsu.site_use_id = p_site_use_id;
SELECT count(t.task_template_group_id)
FROM jtf_task_temp_groups_vl t,
ozf_reasons r
WHERE t.source_object_type_code = 'OZF_CLAM'
AND r.active_flag = 'T'
AND t.task_template_group_id = r.task_template_group_id
AND NVL(t.start_date_active, SYSDATE) <= SYSDATE
AND NVL(t.end_date_active, SYSDATE) >= SYSDATE
AND r.reason_code_id = p_reason_code_id
AND r.task_template_group_id = p_task_template_id;
SELECT count(user_status_id)
FROM ams_user_statuses_vl
WHERE system_status_type = G_CLAIM_STATUS
AND system_status_code = p_status_code
AND user_status_id = p_user_status_id
AND enabled_flag = 'Y';
SELECT count(salesrep_id)
FROM jtf_rs_salesreps s,
jtf_rs_resource_extns r,
fnd_lookups l
WHERE s.start_date_active <= SYSDATE
AND NVL(s.end_date_active, SYSDATE) >= SYSDATE
AND s.salesrep_id = p_id
AND s.resource_id = r.resource_id
AND r.category = l.lookup_code
AND l.lookup_type ='JTF_RS_RESOURCE_CATEGORY';
SELECT count(lookup_code)
FROM ozf_lookups
WHERE lookup_type = 'OZF_PAYMENT_METHOD'
AND lookup_code = p_lookup_code;
SELECT count(transaction_type_id)
FROM oe_transaction_types_vl
WHERE transaction_type_code = 'ORDER'
AND order_category_code IN ('MIXED', 'RETURN')
AND default_inbound_line_type_id IS NOT NULL
AND transaction_type_id = p_id;
SELECT count(related_cust_account_id)
FROM hz_cust_acct_relate_all
WHERE cust_account_id = p_cust_id
AND related_cust_account_id = p_rel_cust_id;
SELECT org_id
FROM ozf_claim_types_all_b
WHERE claim_type_id = p_claim_type_id;
SELECT org_id
FROM ozf_reason_codes_all_b
WHERE reason_code_id = p_claim_reason_id;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
SELECT count(hca.cust_account_id)
FROM hz_cust_accounts hca
WHERE hca.cust_account_id = l_id;
PROCEDURE Update_Claim_tbl (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_claim_tbl IN claim_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_tbl';
SAVEPOINT Update_Claim_Tbl_PVT;
OZF_claim_PVT.Update_Claim (
p_api_version => l_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,p_claim => l_claim
,p_event => 'UPDATE'
,p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE
,x_object_version_number => l_object_version_number
);
ROLLBACK TO Update_Claim_Tbl_PVT;
ROLLBACK TO Update_Claim_Tbl_PVT;
ROLLBACK TO Update_Claim_Tbl_PVT;
END Update_Claim_tbl;