[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_access varchar2(1) :='N'; -- F : FULL: User can update sensitive metric data
l_access :=AMS_access_PVT.check_update_access(
p_object_id => P_object_id,
p_object_type => P_object_type,
p_user_or_role_id => p_user_id,
p_user_or_role_type => 'USER'
);
l_profile_value := NVL(fnd_profile.value('OZF_CLAIM_UPDATE_ACCESS'), 'VIEW');
IF l_profile_value = 'UPDATE' THEN
x_access := 'R';
OZF_Utility_PVT.debug_message('user_id='||p_user_id||' update_access is '||l_access);
| 19-Aug-2009 KPATRO Removed the Bulk insert for Bug 8809877
|
*=======================================================================*/
PROCEDURE Create_Log(
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_exact_match_tbl IN ozf_rule_match_tbl_type,
p_possible_match_tbl IN ozf_rule_match_tbl_type,
p_accrual_match_tbl IN ozf_accrual_match_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) := 'Create_Log';
INSERT INTO OZF_RULE_BASED_LOG
(
LOG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, CREATED_FROM
, CLAIM_ID
, QP_LIST_HEADER_ID
, CUSTOMER_TRX_ID
, PROCESSED_MATCH_TYPE
)
VALUES
(
OZF_RULE_BASED_LOG_S.nextval
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, NVL(FND_GLOBAL.conc_login_id,-1)
, NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
, NVL(FND_GLOBAL.PROG_APPL_ID,-1)
, 'RULEBASED'
--, l_exactmatchTbl(I).claim_id
, l_exact_match_rec_type.claim_id
, null
--, l_exactmatchTbl(I).customer_trx_id
, l_exact_match_rec_type.customer_trx_id
, 'C'
);
INSERT INTO OZF_RULE_BASED_LOG
(
LOG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, CREATED_FROM
, CLAIM_ID
, QP_LIST_HEADER_ID
, CUSTOMER_TRX_ID
, PROCESSED_MATCH_TYPE
)
VALUES
(
OZF_RULE_BASED_LOG_S.nextval
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, NVL(FND_GLOBAL.conc_login_id,-1)
, NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
, NVL(FND_GLOBAL.PROG_APPL_ID,-1)
, 'RULEBASED'
--, l_possiblematchTbl(J).claim_id
, l_poss_match_rec_type.claim_id
, null
--, l_possiblematchTbl(J).customer_trx_id
, l_poss_match_rec_type.customer_trx_id
, 'P'
);
INSERT INTO OZF_RULE_BASED_LOG
(
LOG_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, CREATED_FROM
, CLAIM_ID
, QP_LIST_HEADER_ID
, CUSTOMER_TRX_ID
, PROCESSED_MATCH_TYPE
)
VALUES
(
OZF_RULE_BASED_LOG_S.nextval
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, SYSDATE
, NVL(FND_GLOBAL.user_id,-1)
, NVL(FND_GLOBAL.conc_login_id,-1)
, NVL(FND_GLOBAL.CONC_REQUEST_ID,-1)
, NVL(FND_GLOBAL.PROG_APPL_ID,-1)
, 'RULEBASED'
, l_accrual_match_rec_type.claim_id
, l_accrual_match_rec_type.qp_list_header_id
, null
, 'A'
);
SELECT NVL(rule_based, 'F'),
NVL(cust_name_match_type,'EXCLUDE_REL_CUST'),
credit_matching_thold_type,
credit_tolerance_operand
FROM ozf_sys_parameters_all
WHERE org_id = l_org_id;
SELECT claim_id,
claim_number,
amount_remaining,
acctd_amount_remaining,
customer_ref_number,
customer_ref_normalized,
cust_account_id,
cust_billto_acct_site_id,
object_version_number,
currency_code,
pre_auth_deduction_number,
pre_auth_deduction_normalized,
cust_billto_acct_site_id,
offer_id
FROM ozf_claims_all
WHERE
status_code = 'OPEN'
AND claim_class = 'DEDUCTION'
AND (customer_ref_number IS NOT NULL
OR pre_auth_deduction_number IS NOT NULL)
AND org_id = l_org_id
AND cust_account_id = nvl(p_cust_account_id,cust_account_id)
AND trunc(creation_date) between nvl(p_start_date, trunc(creation_date)) AND nvl(p_end_date, trunc(creation_date))
ORDER BY creation_date ASC;
SELECT ps.customer_trx_id,
ps.trx_number,
ps.amount_due_remaining
FROM ar_payment_schedules_all ps,
ra_cust_trx_types_all ctt,
ra_customer_trx_all ct
WHERE
ps.class in ('CM') --class = Credit Memo
AND ps.status = 'OP' -- status = Open
AND ps.customer_id = p_cust_account_id
AND ps.invoice_currency_code = p_currency_code --deduction currency code
AND ps.customer_site_use_id = p_site_use_id --deduction site_use_id
AND ctt.type = 'CM'
AND ABS(ps.amount_due_remaining) = p_deduction_amount --deduction amount
AND ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
AND Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.org_id = l_org_id
AND rownum = 1 -- for 100% match it should be one
ORDER BY ct.creation_date ASC;
SELECT ps.customer_trx_id,
ps.trx_number,
ps.amount_due_remaining
FROM ar_payment_schedules_all ps,
ra_cust_trx_types_all ctt,
ra_customer_trx_all ct,
(
SELECT SITE.site_use_id site_use_id
FROM HZ_CUST_ACCT_RELATE_ALL REL, HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE
WHERE
REL.status = 'A'
AND REL.cust_account_id = p_cust_account_id --cust_account_id from deduction
AND (REL.relationship_type is NULL OR REL.relationship_type IN ('ALL','Reciprocal','Parent')) --For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
AND REL.related_cust_account_id = ACCT_SITE.cust_account_id
AND ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.status = 'A'
AND REL.org_id = l_org_id
UNION
SELECT SITE.site_use_id site_use_id
FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE
WHERE
ACCT_SITE.cust_account_id = p_cust_account_id
AND ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.status = 'A'
AND SITE.org_id = l_org_id
) site_use
WHERE
ps.class in ('CM') --class = Credit Memo
AND ps.status = 'OP' -- status = Open
AND ps.invoice_currency_code = p_currency_code --deduction currency code
AND ABS(ps.amount_due_remaining) = p_deduction_amount --deduction amount
AND ps.customer_site_use_id = site_use.site_use_id --deduction site_use_id / related customer site_use_id
AND ctt.type = 'CM'
AND ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
AND Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.org_id = l_org_id
AND rownum = 1 -- for 100% match it should be one.
ORDER BY ct.creation_date ASC;
SELECT ps.customer_trx_id,
ps.trx_number,
ps.amount_due_remaining
FROM
ar_payment_schedules_all ps,
ra_cust_trx_types_all ctt,
ra_customer_trx_all ct
WHERE
ps.class in ('CM') --class = Credit Memo
AND ps.status = 'OP' -- status = Open
AND ps.customer_id = p_cust_account_id
AND ps.invoice_currency_code = p_currency_code --deduction currency code
AND ps.customer_site_use_id = p_site_use_id --deduction site_use_id
AND ctt.type = 'CM'
AND ABS(ps.amount_due_remaining) between p_deduction_lower_amount AND p_deduction_upper_amount --deduction amount
AND ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
AND Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.org_id = l_org_id
ORDER BY ct.creation_date ASC;
SELECT ps.customer_trx_id,
ps.trx_number,
ps.amount_due_remaining
FROM
ar_payment_schedules_all ps,
ra_cust_trx_types_all ctt,
ra_customer_trx_all ct,
(
SELECT SITE.site_use_id site_use_id
FROM HZ_CUST_ACCT_RELATE_ALL REL,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE
WHERE REL.status = 'A'
AND REL.cust_account_id = p_cust_account_id --cust_account_id from deduction
AND (REL.relationship_type is NULL OR REL.relationship_type IN ('ALL','Reciprocal','Parent')) --For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
AND REL.related_cust_account_id = ACCT_SITE.cust_account_id
AND ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.status = 'A'
AND REL.org_id = l_org_id
UNION
SELECT SITE.site_use_id site_use_id
FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_CUST_SITE_USES_ALL SITE
WHERE ACCT_SITE.cust_account_id = p_cust_account_id
AND ACCT_SITE.cust_acct_site_id = SITE.cust_acct_site_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.status = 'A'
AND SITE.org_id = l_org_id
) site_use
WHERE
ps.class in ('CM') --class = Credit Memo
AND ps.status = 'OP' -- status = Open
AND ps.invoice_currency_code = p_currency_code --deduction currency code
AND ABS(ps.amount_due_remaining) BETWEEN p_deduction_lower_amount AND p_deduction_upper_amount --deduction amount
AND ps.customer_site_use_id = site_use.site_use_id --deduction site_use_id / related customer site_use_id
AND ctt.type = 'CM'
AND ctt.cust_trx_type_id = ps.cust_trx_type_id --transaction type = Credit Memo
AND Normalize_Credit_Reference(ct.customer_reference) = p_ref_number
AND ct.customer_trx_id = ps.customer_trx_id
AND ps.org_id = l_org_id
ORDER BY ct.creation_date ASC;
SELECT qp_list_header_id,offer_code
FROM ozf_offers
WHERE offer_code =p_ref_number;
SELECT claim_line_id,object_version_number
FROM ozf_claim_lines_all
WHERE claim_id = p_claim_id;
SELECT COUNT(*)
FROM ozf_claim_lines_util_all
WHERE claim_line_id = p_claim_line_id;
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_rec
,p_event => 'UPDATE'
,p_mode => 'AUTO'
,x_object_version_number => l_object_version_number
);
SAVEPOINT Update_Claim_From_Association;
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_Utility_PVT.write_conc_log('Claim Line Deleted');
ROLLBACK TO Update_Claim_From_Association;
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_rec
,p_event => 'UPDATE'
,p_mode => 'AUTO'
,x_object_version_number => l_object_version_number
);
ROLLBACK TO Update_Claim_From_Association;
ROLLBACK TO Update_Claim_From_Association;
ROLLBACK TO Update_Claim_From_Association;