[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT USERENV('sessionid')
FROM dual;
fnd_file.put_line(FND_FILE.LOG,'SELECT * FROM fnd_log_messages WHERE user_id = '||fnd_global.user_id||
' AND session_id = '||l_sessionid||' ORDER BY log_sequence; ');
This procedure will insert record in okc_qp_upgrade process with line_type
as SUMMARY.
There can be only one record in okc_qp_upgrade with line_type=SUMMARY.
If the record already exists then this procedure will not do any thing.
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'ins_summary_rec';
SELECT *
FROM okc_qp_upgrade
WHERE line_type='SUMMARY';
okc_debug.Log('20: Summary Record Not Found, Inserting ... ',2);
INSERT INTO okc_qp_upgrade
(
LINE_TYPE,
CREATION_DATE,
LAST_UPDATE_DATE,
COMPLETION_FLAG,
SCS_CODE,
CHR_ID,
REQUEST_ID,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
'SUMMARY',
sysdate,
sysdate,
'N',
NULL,
NULL,
fnd_global.conc_request_id,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.conc_login_id
);
okc_debug.Log('30: Inserted Summary Record ',2);
okc_debug.Log('40: Summary Record FOUND, skipping insert ',2);
This procedure will insert record into okc_qp_upgrade with line_type=CATEGORY
There will be only ONE ROW in okc_qp_upgrade for each category.
This proc will check if the if record for the category exists
----------------
Record Not Found:
----------------
1. Insert record for the category
2. Commit record
3. Call the upgrade of K for this category.
----------------
Record Found:
----------------
Case 1 : completion_flag = 'Y'
In this case this category was already upgraded
skip this category
Case 2 : completion_flag = 'N'
In this case there was an error when the conc. pgm was run for the category
1. update completion_flag = 'P' -- In Progress
2. Commit record
3. Call the upgrade of K for this category.
Case 3 : completion_flag = 'P'
In this case there is another concurrent pgm being run for this category.
So we skip this category as only ONE conc. pgm can run at any point of time for a
given category
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'ins_category_rec';
SELECT *
FROM okc_qp_upgrade
WHERE line_type = 'CATEGORY'
AND scs_code = p_category_code
FOR UPDATE OF completion_flag;
okc_debug.Log('20: Category Record Not Found, Inserting ... ',2);
INSERT INTO okc_qp_upgrade
(
LINE_TYPE,
CREATION_DATE,
LAST_UPDATE_DATE,
COMPLETION_FLAG,
SCS_CODE,
CHR_ID,
REQUEST_ID,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
'CATEGORY',
sysdate,
sysdate,
'P',
p_category_code,
NULL,
fnd_global.conc_request_id,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.conc_login_id
);
okc_debug.Log('30: Inserted Category Record for : '||p_category_code,2);
UPDATE okc_qp_upgrade
SET completion_flag = 'P'
WHERE CURRENT OF csr_category_rec;
okc_debug.Log('60: Updated completion_flag to P for : '||p_category_code,2);
SELECT chr1.rowid,
chr1.id id,
chr1.contract_number,
chr1.contract_number_modifier,
chr1.estimated_amount ,
chr1.object_version_number obj
FROM okc_k_headers_b chr1
WHERE chr1.application_id IN (510,871)
AND NVL(chr1.buy_or_sell,'X') = 'S'
AND chr1.scs_code = p_category_code
AND chr1.id NOT IN
(
SELECT NVL(chr_id,0)
FROM okc_qp_upgrade
WHERE line_type = 'CONTRACT'
AND scs_code = p_category_code
)
;
l_obj_code_tbl varchar_tbl_type; -- holds object version number for the line. needed in update
l_level_tbl.delete;
l_line_id_tbl.delete;
l_cle_id_tbl.delete;
l_line_list_price_tbl.delete;
l_price_unit_tbl.delete;
l_priced_flag_tbl.delete;
l_price_list_tbl.delete;
l_rul_line_id_tbl .delete;
l_rul_pricelist_tbl.delete;
l_rul_object_code_tbl.delete;
l_itm_to_price_tbl.delete;
l_lse_id_tbl.delete;
SELECT ROWID
,level
,id
,cle_id
,line_list_price
,price_unit
,price_level_ind
,price_list_id
,lse_id
BULK COLLECT INTO
l_line_rowid_tbl
,l_level_tbl
,l_line_id_tbl
,l_cle_id_tbl
,l_line_list_price_tbl
,l_price_unit_tbl
,l_priced_flag_tbl
,l_price_list_tbl
,l_lse_id_tbl
FROM okc_k_lines_b
CONNECT BY (prior id = cle_id AND dnz_chr_id=chr_rec.id )
START WITH chr_id = chr_rec.id;
SELECT rul.object1_id1
,rul.jtot_object1_code
,rgp.chr_id
,rgp.cle_id
BULK COLLECT INTO
l_rul_pricelist_tbl
,l_rul_object_code_tbl
,l_rul_header_id_tbl
,l_rul_line_id_tbl
FROM okc_rules_b rul,
okc_rule_groups_b rgp
WHERE rul.rgp_id = rgp.id
AND rul.rule_information_category = 'PRE'
AND rul.dnz_chr_id = chr_rec.id;
SELECT NVL(number_of_items,0)
INTO l_qty
FROM okc_k_items
WHERE cle_id=l_line_id_tbl(j);
okc_debug.Log('150: Quantity Selected : '||l_qty,2);
UPDATE okc_k_lines_b
SET object_version_number = object_version_number+1,
last_updated_by = -1901903 ,--bug number
last_update_date = sysdate ,
line_list_price = l_line_list_price_tbl(j),
price_list_id = l_price_list_tbl(j),
item_to_price_yn = l_itm_to_price_tbl(j),
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
pricing_date = sysdate
WHERE rowid = l_line_rowid_tbl(j);
UPDATE okc_k_headers_b
SET object_version_number = object_version_number+1,
last_updated_by = -1901903 ,--bug number
last_update_date = sysdate,
total_line_list_price = l_hdr_list_price,
price_list_id = l_hdr_price_list,
estimated_amount = NVL(l_estimated_amount,0),
user_estimated_amount = NVL(l_user_estimated_amount,0),
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate,
request_id = fnd_global.conc_request_id,
pricing_date = sysdate
WHERE rowid = chr_rec.rowid;
okc_debug.Log('400: Updated Header : '||chr_rec.contract_number,2);
This procedure will insert record into okc_qp_upgrade table with line_type=CONTRACT
We will insert a row for each contract that we upgrade.
For contracts that have used default price list id at any of the line level, we will
also store the default price list id for those contracts
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'ins_contract_rec';
okc_debug.Log('20: Contract Record Inserting ... ',2);
INSERT INTO okc_qp_upgrade
(
LINE_TYPE,
CREATION_DATE,
LAST_UPDATE_DATE,
COMPLETION_FLAG,
SCS_CODE,
CHR_ID,
DFLT_PRICE_LIST_ID,
REQUEST_ID,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
'CONTRACT',
sysdate,
sysdate,
'Y',
p_category_code,
p_chr_id,
g_k_price_list_id,
fnd_global.conc_request_id,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.conc_login_id
);
2. Insert the Summary Record
3. Call the start_category_upgrade with the p_category_code
4. Update the category record as complete after upgrade
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'call_qp_upgrade';
SELECT *
FROM okc_qp_upgrade
WHERE line_type='SUMMARY'
AND completion_flag = 'Y';
SELECT scs.code
FROM okc_subclasses_b scs,
okc_classes_b cs
WHERE scs.cls_code = cs.code
AND cs.application_id IN (510,871)
AND scs.code = NVL(p_category_code,scs.code) ;
This procedure will update the category record currently processed as complete
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'upd_category_rec';
SELECT *
FROM okc_qp_upgrade
WHERE line_type = 'CATEGORY'
AND scs_code = p_category_code
AND completion_flag = 'P'
FOR UPDATE OF completion_flag;
UPDATE okc_qp_upgrade
SET completion_flag = p_status,
last_update_date = sysdate
WHERE CURRENT OF csr_category_rec;
If Yes, it will update the SUMMARY record as done and enable the QP Profile
*/
-- local variables and cursors
l_proc varchar2(72) := g_package||'upd_summary_rec';
SELECT COUNT(scs.code)
FROM okc_subclasses_b scs, okc_classes_b cs
WHERE scs.cls_code = cs.code
AND cs.application_id IN ( 510, 871 );
SELECT COUNT(scs_code)
FROM okc_qp_upgrade
WHERE line_type='CATEGORY'
and completion_flag = 'Y';
SELECT *
FROM okc_qp_upgrade
WHERE line_type='SUMMARY'
AND completion_flag= 'N'
FOR UPDATE OF completion_flag;
UPDATE okc_qp_upgrade
SET completion_flag ='Y',
last_update_date = sysdate
WHERE CURRENT OF csr_summary_rec;
SELECT RPAD(scs.meaning,40),
RPAD(qp.CREATION_DATE,13),
qp.LAST_UPDATE_DATE
FROM okc_subclasses_v scs, okc_qp_upgrade qp
WHERE qp.scs_code = scs.code
AND qp.line_type = 'CATEGORY'
AND qp.completion_flag = 'Y'
ORDER BY scs.meaning;
SELECT scs.meaning
FROM okc_subclasses_v scs, okc_qp_upgrade qp
WHERE qp.scs_code = scs.code
AND qp.line_type = 'CATEGORY'
AND qp.completion_flag = 'N'
ORDER BY scs.meaning;
SELECT scs.meaning
FROM okc_subclasses_v scs, okc_classes_b cs
WHERE scs.cls_code = cs.code
AND cs.application_id IN (510,871)
AND scs.code NOT IN (
SELECT scs_code
FROM okc_qp_upgrade
WHERE line_type='CATEGORY'
)
ORDER BY scs.meaning;
SELECT SUM(NVL(price_negotiated,0))
FROM okc_k_lines_b
WHERE chr_id = p_chr_id;
SELECT *
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND NVL(price_level_ind,'N') = 'Y'
AND NVL(line_list_price,0) <> NVL(price_negotiated,0);
SELECT *
FROM qp_list_lines
WHERE list_line_type_code = p_list_line_type_code
AND list_header_id IN (
SELECT list_header_id
FROM qp_list_headers
WHERE name = 'OKC_QP_UPGRADE'
);
INSERT INTO okc_price_adjustments
(
ID,
CHR_ID,
CLE_ID,
ACCRUAL_CONVERSION_RATE,
ACCRUAL_FLAG,
ADJUSTED_AMOUNT,
APPLIED_FLAG,
ARITHMETIC_OPERATOR,
AUTOMATIC_FLAG,
BENEFIT_QTY,
BENEFIT_UOM_CODE,
CHARGE_SUBTYPE_CODE,
CHARGE_TYPE_CODE ,
EXPIRATION_DATE ,
INCLUDE_ON_RETURNS_FLAG ,
LIST_HEADER_ID ,
LIST_LINE_ID ,
LIST_LINE_NO ,
LIST_LINE_TYPE_CODE ,
MODIFIER_LEVEL_CODE ,
MODIFIER_MECHANISM_TYPE_CODE ,
OPERAND ,
PRICE_BREAK_TYPE_CODE ,
PRICING_GROUP_SEQUENCE ,
PRICING_PHASE_ID ,
PRORATION_TYPE_CODE ,
REBATE_TRANSACTION_TYPE_CODE ,
RANGE_BREAK_QUANTITY ,
SOURCE_SYSTEM_CODE ,
SUBSTITUTION_ATTRIBUTE ,
UPDATE_ALLOWED ,
UPDATED_FLAG ,
OBJECT_VERSION_NUMBER ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
VALUES
(
l_id , -- ID
p_chr_id, -- CHR_ID
l_k_lines_rec.id, -- CLE_ID
l_qp_list_lines_rec.accrual_conversion_rate, -- ACCRUAL_CONVERSION_RATE
l_qp_list_lines_rec.accrual_flag,-- ACCRUAL_FLAG
-1*(l_adj_amt),-- ADJUSTED_AMOUNT
'Y',-- APPLIED_FLAG
l_qp_list_lines_rec.arithmetic_operator, -- ARITHMETIC_OPERATOR
l_qp_list_lines_rec.automatic_flag,-- AUTOMATIC_FLAG
l_qp_list_lines_rec.benefit_qty , -- BENEFIT_QTY
l_qp_list_lines_rec.benefit_uom_code , -- BENEFIT_UOM_CODE
l_qp_list_lines_rec.charge_subtype_code , -- CHARGE_SUBTYPE_CODE
l_qp_list_lines_rec.charge_type_code , -- CHARGE_TYPE_CODE
l_qp_list_lines_rec.expiration_date , -- EXPIRATION_DATE
l_qp_list_lines_rec.include_on_returns_flag , -- INCLUDE_ON_RETURNS_FLAG
l_qp_list_lines_rec.list_header_id , -- LIST_HEADER_ID
l_qp_list_lines_rec.list_line_id , -- LIST_LINE_ID
l_qp_list_lines_rec.list_line_no , -- LIST_LINE_NO
l_qp_list_lines_rec.list_line_type_code , -- LIST_LINE_TYPE_CODE
l_qp_list_lines_rec.modifier_level_code , -- MODIFIER_LEVEL_CODE
'DLT' , -- MODIFIER_MECHANISM_TYPE_CODE
l_adj_amt , -- OPERAND this is reverse of ADJUSTED_AMOUNT
l_qp_list_lines_rec.price_break_type_code , -- PRICE_BREAK_TYPE_CODE
l_qp_list_lines_rec.pricing_group_sequence , -- PRICING_GROUP_SEQUENCE
l_qp_list_lines_rec.pricing_phase_id , -- PRICING_PHASE_ID
l_qp_list_lines_rec.proration_type_code , -- PRORATION_TYPE_CODE
l_qp_list_lines_rec.rebate_transaction_type_code , -- REBATE_TRANSACTION_TYPE_CODE
NULL , -- RANGE_BREAK_QUANTITY
NULL , -- SOURCE_SYSTEM_CODE
l_qp_list_lines_rec.substitution_attribute , -- SUBSTITUTION_ATTRIBUTE
l_qp_list_lines_rec.override_flag , -- UPDATE_ALLOWED
'Y' , -- UPDATED_FLAG
1, -- OBJECT_VERSION_NUMBER
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
);
SELECT *
FROM qp_list_lines
WHERE list_header_id IN (
SELECT list_header_id
FROM qp_list_headers
WHERE name = 'OKC_QP_UPGRADE'
);
SELECT NVL(item_to_price_yn,'N')
FROM okc_line_styles_b
WHERE id = p_lse_id;