[Home] [Help]
1: PACKAGE BODY OKC_QP_UPGRADE_PUB AS
2: /* $Header: OKCPQPUB.pls 120.0 2005/05/25 22:36:14 appldev noship $ */
3: l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4: -- ----------------------------------------------------------------------------
5: -- | Private Global Definitions |
4: -- ----------------------------------------------------------------------------
5: -- | Private Global Definitions |
6: -- ----------------------------------------------------------------------------
7: --
8: g_package varchar2(33) := ' OKC_QP_UPGRADE_PUB.'; -- Global package name
9: --
10: g_error_exception exception;
11: g_category_code okc_subclasses_b.code%TYPE;
12: g_dflt_price_list_id number;
136:
137: -- check if profile value and abort if Y
138: IF NVL(check_qp_profile,'N') = 'N' THEN
139:
140: -- check if OKC_QP_UPGRADE modifier is defined
141: IF NVL(check_modifier,'N') = 'Y' THEN
142:
143: -- process params
144: /*
241: ----------------------------------------------------------------------------*/
242: PROCEDURE ins_summary_rec
243: IS
244: /*
245: This procedure will insert record in okc_qp_upgrade process with line_type
246: as SUMMARY.
247: There can be only one record in okc_qp_upgrade with line_type=SUMMARY.
248: If the record already exists then this procedure will not do any thing.
249: */
243: IS
244: /*
245: This procedure will insert record in okc_qp_upgrade process with line_type
246: as SUMMARY.
247: There can be only one record in okc_qp_upgrade with line_type=SUMMARY.
248: If the record already exists then this procedure will not do any thing.
249: */
250: -- local variables and cursors
251:
249: */
250: -- local variables and cursors
251:
252: l_proc varchar2(72) := g_package||'ins_summary_rec';
253: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
254:
255: CURSOR csr_summary_rec IS
256: SELECT *
257: FROM okc_qp_upgrade
253: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
254:
255: CURSOR csr_summary_rec IS
256: SELECT *
257: FROM okc_qp_upgrade
258: WHERE line_type='SUMMARY';
259:
260: BEGIN
261:
279: IF (l_debug = 'Y') THEN
280: okc_debug.Log('20: Summary Record Not Found, Inserting ... ',2);
281: END IF;
282: -- record does not exist, so insert
283: INSERT INTO okc_qp_upgrade
284: (
285: LINE_TYPE,
286: CREATION_DATE,
287: LAST_UPDATE_DATE,
352: p_category_code IN okc_subclasses_b.code%TYPE
353: )
354: IS
355: /*
356: This procedure will insert record into okc_qp_upgrade with line_type=CATEGORY
357: There will be only ONE ROW in okc_qp_upgrade for each category.
358: This proc will check if the if record for the category exists
359: ----------------
360: Record Not Found:
353: )
354: IS
355: /*
356: This procedure will insert record into okc_qp_upgrade with line_type=CATEGORY
357: There will be only ONE ROW in okc_qp_upgrade for each category.
358: This proc will check if the if record for the category exists
359: ----------------
360: Record Not Found:
361: ----------------
383:
384: -- local variables and cursors
385:
386: l_proc varchar2(72) := g_package||'ins_category_rec';
387: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
388:
389: CURSOR csr_category_rec IS
390: SELECT *
391: FROM okc_qp_upgrade
387: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
388:
389: CURSOR csr_category_rec IS
390: SELECT *
391: FROM okc_qp_upgrade
392: WHERE line_type = 'CATEGORY'
393: AND scs_code = p_category_code
394: FOR UPDATE OF completion_flag;
395:
421: IF (l_debug = 'Y') THEN
422: okc_debug.Log('20: Category Record Not Found, Inserting ... ',2);
423: END IF;
424: -- record does not exist, so insert
425: INSERT INTO okc_qp_upgrade
426: (
427: LINE_TYPE,
428: CREATION_DATE,
429: LAST_UPDATE_DATE,
509: Case 2 : completion_flag = 'N'
510: In this case there was an error when the conc. pgm was run for the category
511: */
512: -- 1. update completion_flag = P ,
513: UPDATE okc_qp_upgrade
514: SET completion_flag = 'P'
515: WHERE CURRENT OF csr_category_rec;
516:
517:
604: IS
605: /*
606: This procedure will call the upgrade script .
607: we will run upgrade for ALL contracts in p_category_code
608: which have NOT BEEN UPGRADED i.e no entry in okc_qp_upgrade table.
609:
610: */
611: -- local variables and cursors
612:
626: AND chr1.scs_code = p_category_code
627: AND chr1.id NOT IN
628: (
629: SELECT NVL(chr_id,0)
630: FROM okc_qp_upgrade
631: WHERE line_type = 'CONTRACT'
632: AND scs_code = p_category_code
633: )
634: ;
1025: (
1026: p_chr_id => chr_rec.id
1027: );
1028:
1029: -- Insert into okc_qp_upgrade the chr_id we processed
1030: ins_contract_rec
1031: (
1032: p_category_code => p_category_code,
1033: p_chr_id => chr_rec.id,
1092: p_contract_number_modifier IN okc_k_headers_b.contract_number_modifier%TYPE
1093: )
1094: IS
1095: /*
1096: This procedure will insert record into okc_qp_upgrade table with line_type=CONTRACT
1097: We will insert a row for each contract that we upgrade.
1098: For contracts that have used default price list id at any of the line level, we will
1099: also store the default price list id for those contracts
1100: */
1124: ' '|| p_contract_number_modifier||' '||p_chr_id);
1125: */
1126:
1127: -- insert
1128: INSERT INTO okc_qp_upgrade
1129: (
1130: LINE_TYPE,
1131: CREATION_DATE,
1132: LAST_UPDATE_DATE,
1199: l_proc varchar2(72) := g_package||'call_qp_upgrade';
1200:
1201: CURSOR csr_summary_rec IS
1202: SELECT *
1203: FROM okc_qp_upgrade
1204: WHERE line_type='SUMMARY'
1205: AND completion_flag = 'Y';
1206:
1207: -- category cursor for all/ specific categories in OKC and OKO
1214: AND scs.code = NVL(p_category_code,scs.code) ;
1215:
1216:
1217: l_code okc_subclasses_b.code%TYPE;
1218: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1219:
1220:
1221: BEGIN
1222:
1319: l_proc varchar2(72) := g_package||'upd_category_rec';
1320:
1321: CURSOR csr_category_rec IS
1322: SELECT *
1323: FROM okc_qp_upgrade
1324: WHERE line_type = 'CATEGORY'
1325: AND scs_code = p_category_code
1326: AND completion_flag = 'P'
1327: FOR UPDATE OF completion_flag;
1325: AND scs_code = p_category_code
1326: AND completion_flag = 'P'
1327: FOR UPDATE OF completion_flag;
1328:
1329: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1330:
1331: BEGIN
1332:
1333: IF (l_debug = 'Y') THEN
1351: IF (l_debug = 'Y') THEN
1352: okc_debug.set_trace_off;
1353: END IF;
1354: EXIT WHEN csr_category_rec%NOTFOUND;
1355: UPDATE okc_qp_upgrade
1356: SET completion_flag = p_status,
1357: last_update_date = sysdate
1358: WHERE CURRENT OF csr_category_rec;
1359: END LOOP;
1413: AND cs.application_id IN ( 510, 871 );
1414:
1415: CURSOR csr_categories_done IS
1416: SELECT COUNT(scs_code)
1417: FROM okc_qp_upgrade
1418: WHERE line_type='CATEGORY'
1419: and completion_flag = 'Y';
1420:
1421: CURSOR csr_summary_rec IS
1419: and completion_flag = 'Y';
1420:
1421: CURSOR csr_summary_rec IS
1422: SELECT *
1423: FROM okc_qp_upgrade
1424: WHERE line_type='SUMMARY'
1425: AND completion_flag= 'N'
1426: FOR UPDATE OF completion_flag;
1427:
1424: WHERE line_type='SUMMARY'
1425: AND completion_flag= 'N'
1426: FOR UPDATE OF completion_flag;
1427:
1428: l_qp_upgrade_rec okc_qp_upgrade%ROWTYPE;
1429: l_prof_val varchar2(10);
1430:
1431: BEGIN
1432:
1489: END IF;
1490: LOOP
1491: FETCH csr_summary_rec INTO l_qp_upgrade_rec;
1492: EXIT WHEN csr_summary_rec%NOTFOUND;
1493: UPDATE okc_qp_upgrade
1494: SET completion_flag ='Y',
1495: last_update_date = sysdate
1496: WHERE CURRENT OF csr_summary_rec;
1497: END LOOP;
1663: CURSOR category_compeleted_csr IS
1664: SELECT RPAD(scs.meaning,40),
1665: RPAD(qp.CREATION_DATE,13),
1666: qp.LAST_UPDATE_DATE
1667: FROM okc_subclasses_v scs, okc_qp_upgrade qp
1668: WHERE qp.scs_code = scs.code
1669: AND qp.line_type = 'CATEGORY'
1670: AND qp.completion_flag = 'Y'
1671: ORDER BY scs.meaning;
1671: ORDER BY scs.meaning;
1672:
1673: CURSOR category_incompeleted_csr IS
1674: SELECT scs.meaning
1675: FROM okc_subclasses_v scs, okc_qp_upgrade qp
1676: WHERE qp.scs_code = scs.code
1677: AND qp.line_type = 'CATEGORY'
1678: AND qp.completion_flag = 'N'
1679: ORDER BY scs.meaning;
1684: WHERE scs.cls_code = cs.code
1685: AND cs.application_id IN (510,871)
1686: AND scs.code NOT IN (
1687: SELECT scs_code
1688: FROM okc_qp_upgrade
1689: WHERE line_type='CATEGORY'
1690: )
1691: ORDER BY scs.meaning;
1692:
1968: /*
1969: This procedure will check if there is any difference between line_list_price and
1970: price_negotiated for priced lines and if yes it will create a manual adjustment
1971: transaction
1972: Assumption : There is a Modifier created with name OKC_QP_UPGRADE
1973: This Modifier has lines : Discount and Surcharge.
1974: We pick up the Surcharge Line if price_negotiated > line_list_price
1975: We pick up the Discount Line if price_negotiated < line_list_price
1976: */
1992: WHERE list_line_type_code = p_list_line_type_code
1993: AND list_header_id IN (
1994: SELECT list_header_id
1995: FROM qp_list_headers
1996: WHERE name = 'OKC_QP_UPGRADE'
1997: );
1998:
1999: l_k_lines_rec okc_k_lines_b%ROWTYPE;
2000: l_qp_list_lines_rec qp_list_lines%ROWTYPE;
2147: ----------------------------------------------------------------------------*/
2148: FUNCTION check_modifier RETURN varchar2
2149: IS
2150: /*
2151: This function will check if there is a Modifier created with name OKC_QP_UPGRADE
2152: Returns Y if Modifier defined
2153: Returns N if Modifier not defined
2154: Assumption : Pre req for upgrade that Modifier created with name OKC_QP_UPGRADE
2155: */
2150: /*
2151: This function will check if there is a Modifier created with name OKC_QP_UPGRADE
2152: Returns Y if Modifier defined
2153: Returns N if Modifier not defined
2154: Assumption : Pre req for upgrade that Modifier created with name OKC_QP_UPGRADE
2155: */
2156:
2157: -- local variables and cursors
2158:
2165: FROM qp_list_lines
2166: WHERE list_header_id IN (
2167: SELECT list_header_id
2168: FROM qp_list_headers
2169: WHERE name = 'OKC_QP_UPGRADE'
2170: );
2171:
2172: BEGIN
2173:
2176: okc_debug.Log('10: Entering ',2);
2177: END IF;
2178:
2179: OPEN csr_list_lines;
2180: -- only one line for the modifier OKC_QP_UPGRADE
2181: FETCH csr_list_lines INTO l_qp_list_lines_rec;
2182: -- check if the above Modifier is defined else give message
2183: IF csr_list_lines%NOTFOUND THEN
2184: IF (l_debug = 'Y') THEN
2181: FETCH csr_list_lines INTO l_qp_list_lines_rec;
2182: -- check if the above Modifier is defined else give message
2183: IF csr_list_lines%NOTFOUND THEN
2184: IF (l_debug = 'Y') THEN
2185: okc_debug.Log('100: Aborting as the Modifier OKC_QP_UPGRADE is not defined ',2);
2186: END IF;
2187: fnd_file.put_line(FND_FILE.LOG,' ');
2188: fnd_file.put_line(FND_FILE.LOG,' ');
2189: fnd_file.put_line(FND_FILE.LOG,' *********************************************** ');
2313:
2314:
2315:
2316:
2317: END OKC_QP_UPGRADE_PUB; -- Package Body OKC_QP_UPGRADE_PUB