[Home] [Help]
9133: , oksTL.REACT_TIME_NAME AS oksTL_REACT_TIME_NAME
9134: , oksTL.SECURITY_GROUP_ID AS oksTL_SECURITY_GROUP_ID
9135: FROM
9136: OKS_K_LINES_TL oksTL
9137: ,OKS_K_LINES_B oksB
9138: WHERE oksTL.ID = oksB.ORIG_SYSTEM_ID1
9139: AND oksB.DNZ_CHR_ID = P_To_Chr_ID;
9140:
9141: CURSOR GetOksTLPartial IS
9151: , oksTL.REACT_TIME_NAME AS oksTL_REACT_TIME_NAME
9152: , oksTL.SECURITY_GROUP_ID AS oksTL_SECURITY_GROUP_ID
9153: FROM
9154: OKS_K_LINES_TL oksTL
9155: ,OKS_K_LINES_B oksB
9156: ,OKC_K_LINES_B_TEMP okcBT
9157: WHERE oksTL.ID = oksB.ORIG_SYSTEM_ID1
9158: AND oksB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
9159: AND oksB.CLE_ID = okcBT.ID
13431: WHERE a.ID = LineIDTab(i);
13432: End;
13433: Begin
13434: FORALL j IN LineIDTab.FIRST..LineIDTab.LAST
13435: UPDATE OKS_K_LINES_B a
13436: SET a.ACCT_RULE_ID = nvl(a.ACCT_RULE_ID,G_TrgHdrAcctRuleID)
13437: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
13438: and a.CLE_ID = LineIDTab(j);
13439: End;
13538: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13539: ) as INV_RULE_ID
13540: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13541: ,decode(oksB.BILLING_SCHEDULE_TYPE
13542: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13543: ,oksB.BILLING_SCHEDULE_TYPE
13544: ) -- its a subline
13545: ) as BILLING_SCHEDULE_TYPE
13546: ,okcB.START_DATE as START_DATE
13548: ,okcB_Source.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13549: ,NULL as GEN_SUBLINE_BSCH_YN
13550: from
13551: OKC_K_LINES_B okcB
13552: ,OKS_K_LINES_B oksB
13553: ,OKC_K_LINES_B okcB_Source
13554: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13555: and okcB.ID = oksB.CLE_ID
13556: and okcB_Source.ID = okcB.ORIG_SYSTEM_ID1
13587: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13588: ) as INV_RULE_ID
13589: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13590: ,decode(oksB.BILLING_SCHEDULE_TYPE
13591: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13592: ,oksB.BILLING_SCHEDULE_TYPE
13593: ) -- its a subline
13594: ) as BILLING_SCHEDULE_TYPE
13595: ,okcB.START_DATE as START_DATE
13597: ,okcBT.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13598: ,okcBT.GEN_SUBLINE_BSCH_YN as GEN_SUBLINE_BSCH_YN
13599: from
13600: OKC_K_LINES_B okcB
13601: ,OKS_K_LINES_B oksB
13602: ,OKC_K_LINES_B_TEMP okcBT
13603: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13604: and okcB.ID = oksB.CLE_ID
13605: and okcBT.ID = okcB.ID
13991: l_tce_code VARCHAR2(100);
13992: l_uom_quantity NUMBER;
13993: l_level_periods NUMBER;
13994:
13995: l_resultant_PriceUOM OKS_K_LINES_B.PRICE_UOM%TYPE;
13996:
13997: Begin
13998: IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
13999: FND_LOG.string(FND_LOG.level_procedure
14227: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14228: ) as INV_RULE_ID
14229: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14230: ,decode(oksB.BILLING_SCHEDULE_TYPE
14231: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14232: ,oksB.BILLING_SCHEDULE_TYPE
14233: ) -- its a subline
14234: ) as BILLING_SCHEDULE_TYPE
14235: ,okcB.START_DATE as START_DATE
14245: ,oksB.USAGE_TYPE as USAGE_TYPE
14246: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14247: from
14248: OKC_K_LINES_B okcB
14249: ,OKS_K_LINES_B oksB
14250: where okcB.CHR_ID = P_To_Chr_ID
14251: and okcB.ID = oksB.CLE_ID;
14252: -- End hkamdar
14253:
14264: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14265: ) as INV_RULE_ID
14266: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14267: ,decode(oksB.BILLING_SCHEDULE_TYPE
14268: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14269: ,oksB.BILLING_SCHEDULE_TYPE
14270: ) -- its a subline
14271: ) as BILLING_SCHEDULE_TYPE
14272: ,okcB.START_DATE as START_DATE
14282: ,oksB.USAGE_TYPE as USAGE_TYPE
14283: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14284: from
14285: OKC_K_LINES_B okcB
14286: ,OKS_K_LINES_B oksB
14287: ,OKC_K_LINES_B_TEMP okcBT
14288: where okcB.DNZ_CHR_ID = P_To_Chr_ID
14289: and okcB.ID = oksB.CLE_ID
14290: and okcBT.ID = okcB.ID
14365: l_SrcLnStreams_Exists_YN VARCHAR2(1);
14366: -- end hkamdar
14367: l_stage varchar2(100) := null;
14368:
14369: l_resultant_price_uom OKS_K_LINES_B.PRICE_UOM%TYPE;
14370: l_tce_code VARCHAR2(100);
14371: l_uom_quantity NUMBER;
14372: l_level_periods NUMBER;
14373:
15367: ,b.offset_duration
15368: ,b.offset_period
15369: from
15370: OKC_K_LINES_B_TEMP a
15371: ,OKS_K_LINES_B b
15372: where
15373: a.dnz_chr_id = P_To_Chr_ID
15374: and a.lse_id in (3,21)
15375: and a.orig_system_id1 = b.cle_id(+)
15377:
15378: l_BP_ID NUMBER;
15379: l_BP_Start_Date DATE;
15380: l_BP_End_Date DATE;
15381: l_BP_Offset_Duration OKS_K_LINES_B.OFFSET_DURATION%TYPE;
15382: l_BP_Offset_Period OKS_K_LINES_B.OFFSET_PERIOD%TYPE;
15383:
15384: l_Resultant_Start_Date DATE;
15385:
15378: l_BP_ID NUMBER;
15379: l_BP_Start_Date DATE;
15380: l_BP_End_Date DATE;
15381: l_BP_Offset_Duration OKS_K_LINES_B.OFFSET_DURATION%TYPE;
15382: l_BP_Offset_Period OKS_K_LINES_B.OFFSET_PERIOD%TYPE;
15383:
15384: l_Resultant_Start_Date DATE;
15385:
15386: CURSOR getBPLineandChildren IS
15588: AND OKCLINE.id = p_line_id
15589: AND OKCLINE.ID = OKCREL.CLE_ID
15590: AND OEL.LINE_ID = OKCREL.OBJECT1_ID1;
15591:
15592: l_toplvl_uom_code OKS_K_LINES_B.toplvl_uom_code%TYPE;
15593: -- 03-Nov-2006 hkamdar. Bug# 5623498.
15594:
15595: -- Bug# 5531198 hkamdar 21-Sep-2006
15596: ---------------------------------------------------------------------------
15607:
15608: /*if p_include_cancelled_lines 'Y' AND p_include_terminated_lines = 'Y' */
15609: /* Cursor to get rows to be copied from:
15610: OKC_K_LINES_B
15611: OKS_K_LINES_B
15612: OKC_K_ITEMS
15613: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15614: CURSOR GetOKCOKSLinesB_Copy(P_From_Chr_ID IN NUMBER
15615: ,P_To_Chr_ID IN NUMBER) IS
15720: ,NULL AS OkcB_CANCELLED_AMOUNT
15721: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
15722: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
15723: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
15724: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
15725: ,oksB.ID AS OksB_OldOksLineID
15726: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
15727: ,oksB.CLE_ID AS OksB_CLE_ID
15728: ,P_To_Chr_ID AS OksB_NewDnzChrID
15829: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
15830: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
15831: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
15832: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
15833: --------------------------------End of OKS_K_LINES_B columns-------------------------
15834: --------------------------------Start of OKC_K_ITEMS columns-------------------------
15835: ,okcI.ID AS OkcI_OldOkcItemID
15836: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
15837: ,okcI.CLE_ID AS OkcI_CLE_ID
15853: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
15854: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
15855: FROM
15856: OKC_K_LINES_B okcB
15857: ,OKS_K_LINES_B oksB
15858: ,OKC_K_ITEMS okcI
15859: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
15860: AND okcB.ID = oksB.CLE_ID(+)
15861: AND okcB.ID = okcI.CLE_ID(+)
15866:
15867: /*if p_include_cancelled_lines ='Y' and p_include_terminated_lines = 'N'*/
15868: /* Cursor to get rows to be copied from:
15869: OKC_K_LINES_B
15870: OKS_K_LINES_B
15871: OKC_K_ITEMS
15872: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15873: CURSOR GetOKCOKSLinesB_Copy_CANCEL(P_From_Chr_ID IN NUMBER
15874: ,P_To_Chr_ID IN NUMBER) IS
15979: ,NULL AS OkcB_CANCELLED_AMOUNT
15980: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
15981: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
15982: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
15983: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
15984: ,oksB.ID AS OksB_OldOksLineID
15985: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
15986: ,oksB.CLE_ID AS OksB_CLE_ID
15987: ,P_To_Chr_ID AS OksB_NewDnzChrID
16088: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16089: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16090: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16091: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16092: --------------------------------End of OKS_K_LINES_B columns-------------------------
16093: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16094: ,okcI.ID AS OkcI_OldOkcItemID
16095: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16096: ,okcI.CLE_ID AS OkcI_CLE_ID
16112: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16113: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16114: FROM
16115: OKC_K_LINES_B okcB
16116: ,OKS_K_LINES_B oksB
16117: ,OKC_K_ITEMS okcI
16118: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16119: AND okcB.ID = oksB.CLE_ID(+)
16120: AND okcB.ID = okcI.CLE_ID(+)
16126:
16127: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'Y' */
16128: /* Cursor to get rows to be copied from:
16129: OKC_K_LINES_B
16130: OKS_K_LINES_B
16131: OKC_K_ITEMS
16132: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16133: CURSOR GetOKCOKSLinesB_Copy_TERMN(P_From_Chr_ID IN NUMBER
16134: ,P_To_Chr_ID IN NUMBER) IS
16239: ,NULL AS OkcB_CANCELLED_AMOUNT
16240: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16241: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16242: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16243: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16244: ,oksB.ID AS OksB_OldOksLineID
16245: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16246: ,oksB.CLE_ID AS OksB_CLE_ID
16247: ,P_To_Chr_ID AS OksB_NewDnzChrID
16348: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16349: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16350: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16351: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16352: --------------------------------End of OKS_K_LINES_B columns-------------------------
16353: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16354: ,okcI.ID AS OkcI_OldOkcItemID
16355: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16356: ,okcI.CLE_ID AS OkcI_CLE_ID
16372: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16373: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16374: FROM
16375: OKC_K_LINES_B okcB
16376: ,OKS_K_LINES_B oksB
16377: ,OKC_K_ITEMS okcI
16378: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16379: AND okcB.ID = oksB.CLE_ID(+)
16380: AND okcB.ID = okcI.CLE_ID(+)
16386:
16387: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'N' */
16388: /* Cursor to get rows to be copied from:
16389: OKC_K_LINES_B
16390: OKS_K_LINES_B
16391: OKC_K_ITEMS
16392: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16393: CURSOR GetOKCOKSLinesB_Copy_exclude(P_From_Chr_ID IN NUMBER
16394: ,P_To_Chr_ID IN NUMBER) IS
16499: ,NULL AS OkcB_CANCELLED_AMOUNT
16500: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16501: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16502: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16503: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16504: ,oksB.ID AS OksB_OldOksLineID
16505: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16506: ,oksB.CLE_ID AS OksB_CLE_ID
16507: ,P_To_Chr_ID AS OksB_NewDnzChrID
16608: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16609: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16610: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16611: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16612: --------------------------------End of OKS_K_LINES_B columns-------------------------
16613: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16614: ,okcI.ID AS OkcI_OldOkcItemID
16615: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16616: ,okcI.CLE_ID AS OkcI_CLE_ID
16632: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16633: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16634: FROM
16635: OKC_K_LINES_B okcB
16636: ,OKS_K_LINES_B oksB
16637: ,OKC_K_ITEMS okcI
16638: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16639: AND okcB.ID = oksB.CLE_ID(+)
16640: AND okcB.ID = okcI.CLE_ID(+)
16647:
16648:
16649: /* Cursor to get rows to be copied from:
16650: OKC_K_LINES_B
16651: OKS_K_LINES_B
16652: OKC_K_ITEMS
16653: for a specified Source Contracti. This cursor is used when P_Renew_Ref_YN = 'Y'. It filters out
16654: the lines that have been renewed,cancelled or terminated or have been marked as 'DNR' (Do not renew) */
16655:
16751: ,NULL AS OkcB_CANCELLED_AMOUNT
16752: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16753: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16754: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16755: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16756: ,oksB.ID AS OksB_OldOksLineID
16757: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16758: ,oksB.CLE_ID AS OksB_CLE_ID
16759: ,P_To_Chr_ID AS OksB_NewDnzChrID
16860: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16861: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16862: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16863: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16864: --------------------------------End of OKS_K_LINES_B columns-------------------------
16865: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16866: ,okcI.ID AS OkcI_OldOkcItemID
16867: ,To_number(rawtohex(sys_guid()), HexFormatStr)AS OkcI_NewOkcItemID
16868: ,okcI.CLE_ID AS OkcI_CLE_ID
16884: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16885: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16886: FROM
16887: OKC_K_LINES_B okcB
16888: ,OKS_K_LINES_B oksB
16889: ,OKC_K_ITEMS okcI
16890: WHERE
16891: okcB.ID = oksB.CLE_ID(+)
16892: AND okcB.ID = okcI.CLE_ID(+)
17160: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17161: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17162: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17163: -----------------End of record members for OKC_K_LINES_B columns
17164: -----------------Start of record members for OKS_K_LINES_B columns
17165: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17166: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17167: ,OKCOKSLinesRecTab.OksB_CLE_ID
17168: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17256: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17257: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17258: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17259: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17260: -----------------End of record members for OKS_K_LINES_B columns
17261: -----------------Start of record members for OKC_K_ITEMS columns
17262: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17263: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17264: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17374: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17375: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17376: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17377: -----------------End of record members for OKC_K_LINES_B columns
17378: -----------------Start of record members for OKS_K_LINES_B columns
17379: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17380: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17381: ,OKCOKSLinesRecTab.OksB_CLE_ID
17382: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17470: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17471: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17472: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17473: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17474: -----------------End of record members for OKS_K_LINES_B columns
17475: -----------------Start of record members for OKC_K_ITEMS columns
17476: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17477: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17478: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17587: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17588: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17589: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17590: -----------------End of record members for OKC_K_LINES_B columns
17591: -----------------Start of record members for OKS_K_LINES_B columns
17592: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17593: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17594: ,OKCOKSLinesRecTab.OksB_CLE_ID
17595: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17683: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17684: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17685: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17686: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17687: -----------------End of record members for OKS_K_LINES_B columns
17688: -----------------Start of record members for OKC_K_ITEMS columns
17689: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17690: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17691: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17799: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17800: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17801: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17802: -----------------End of record members for OKC_K_LINES_B columns
17803: -----------------Start of record members for OKS_K_LINES_B columns
17804: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17805: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17806: ,OKCOKSLinesRecTab.OksB_CLE_ID
17807: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17895: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17896: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17897: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17898: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17899: -----------------End of record members for OKS_K_LINES_B columns
17900: -----------------Start of record members for OKC_K_ITEMS columns
17901: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17902: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17903: ,OKCOKSLinesRecTab.OkcI_CLE_ID
18019: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
18020: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG -- pseudo column
18021: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
18022: -----------------End of record members for OKC_K_LINES_B columns
18023: -----------------Start of record members for OKS_K_LINES_B columns
18024: ,OKCOKSLinesRecTab.OksB_OldOksLineID
18025: ,OKCOKSLinesRecTab.OksB_NewOksLineID
18026: ,OKCOKSLinesRecTab.OksB_CLE_ID
18027: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
18115: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
18116: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
18117: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
18118: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
18119: -----------------End of record members for OKS_K_LINES_B columns
18120: -----------------Start of record members for OKC_K_ITEMS columns
18121: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
18122: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
18123: ,OKCOKSLinesRecTab.OkcI_CLE_ID
18364:
18365: End;
18366:
18367: Begin
18368: --Bulk Insert into OKS_K_LINES_B
18369: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18370: INSERT INTO OKS_K_LINES_B(
18371: ID
18372: ,CLE_ID
18366:
18367: Begin
18368: --Bulk Insert into OKS_K_LINES_B
18369: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18370: INSERT INTO OKS_K_LINES_B(
18371: ID
18372: ,CLE_ID
18373: ,DNZ_CHR_ID
18374: ,DISCOUNT_LIST
18575:
18576: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
18577: FND_LOG.string(FND_LOG.level_statement
18578: ,l_module_name
18579: ,'AFTER INSERT into OKS_K_LINES_B '
18580: );
18581: END IF;
18582:
18583: EXCEPTION
18585: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
18586: IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
18587: FND_LOG.string(FND_LOG.level_unexpected
18588: ,l_module_name
18589: ,'EXCEPTION during insert into OKS_K_LINES_B '||SQLERRM
18590: );
18591: END IF;
18592:
18593: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
18917: RAISE G_EXCEPTION_HALT_VALIDATION;
18918: END IF;
18919: END IF;
18920:
18921: UPDATE OKS_K_LINES_B
18922: SET
18923: TAX_AMOUNT = lx_rail_rec.TAX_VALUE
18924: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
18925: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
18972: RAISE G_EXCEPTION_HALT_VALIDATION;
18973: END IF;
18974: END IF;
18975:
18976: UPDATE OKS_K_LINES_B
18977: SET
18978: LOCKED_PRICE_LIST_ID = l_locked_price_list_id
18979: ,LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id
18980: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
18983: If SQL%NOTFOUND then
18984: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
18985: FND_LOG.string(FND_LOG.level_statement
18986: ,l_module_name||'.QP_LOCK_PRICELIST_GRP.Lock_Price'
18987: ,'After call to QP_LOCK_PRICELIST_GRP.Lock_Price, Error in Update OKS_K_LINES_B, row not found for update'
18988: );
18989: END IF;
18990:
18991: l_return_status := FND_API.G_RET_STS_ERROR;
19041: RAISE G_EXCEPTION_HALT_VALIDATION;
19042: END IF;
19043: END IF;
19044:
19045: UPDATE OKS_K_LINES_B
19046: SET
19047: TRXN_EXTENSION_ID = l_trxn_extension_id
19048: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19049: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19051: If SQL%NOTFOUND then
19052: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19053: FND_LOG.string(FND_LOG.level_statement
19054: ,l_module_name||'.Create_Transaction_Extension'
19055: ,'After call to Create_Transaction_Extension, Error in Update OKS_K_LINES_B, row not found for update'
19056: );
19057: END IF;
19058:
19059: l_return_status := FND_API.G_RET_STS_ERROR;
19072: FETCH get_toplvl_uom INTO l_toplvl_uom_code;
19073: CLOSE get_toplvl_uom;
19074:
19075: IF l_toplvl_uom_code IS NOT NULL then
19076: UPDATE OKS_K_LINES_B
19077: SET toplvl_uom_code = l_toplvl_uom_code
19078: WHERE id = OKCOKSLinesRecTab.OksB_NewOksLineID(i);
19079: END IF;
19080:
19115: ELSIF (P_Renew_Ref_YN = 'Y') THEN
19116: EXIT WHEN GetOKCOKSLinesB_Renew%NOTFOUND;
19117: END IF;
19118:
19119: END LOOP; --End of Bulk fetch and insert into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS
19120:
19121: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19122: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19123: FND_LOG.string(FND_LOG.level_statement
19121: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19122: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19123: FND_LOG.string(FND_LOG.level_statement
19124: ,l_module_name
19125: ,'After BULK FETCH and BULK INSERT into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS'
19126: );
19127: END IF;
19128: END IF;
19129:
19144: -- Update of OKC_K_LINES_B
19145: -- i) CLE_ID on OKC_K_LINES_B for sublines
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19149: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19150: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19151:
19152: Begin
19145: -- i) CLE_ID on OKC_K_LINES_B for sublines
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19149: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19150: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19151:
19152: Begin
19153:
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19149: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19150: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19151:
19152: Begin
19153:
19154: UPDATE OKC_K_LINES_B a
19171: a.DNZ_CHR_ID = P_To_Chr_ID
19172: AND a.CLE_ID IS NULL
19173: AND a.LSE_ID <> 46; -- Subscription Lines do not have sublines. So they shouldnt be updated.
19174:
19175: UPDATE OKS_K_LINES_B a
19176: SET a.COVERAGE_ID = (select b.ID
19177: FROM OKC_K_LINES_B b
19178: WHERE
19179: b.CLE_ID = a.CLE_ID
19184: where c.DNZ_CHR_ID = P_To_Chr_ID
19185: and c.LSE_ID in (1,19))
19186: AND a.STANDARD_COV_YN = 'N';
19187:
19188: UPDATE OKS_K_LINES_B a
19189: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19190: FROM
19191: OKS_K_LINES_B b
19192: ,OKC_K_LINES_B c
19187:
19188: UPDATE OKS_K_LINES_B a
19189: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19190: FROM
19191: OKS_K_LINES_B b
19192: ,OKC_K_LINES_B c
19193: WHERE
19194: c.ID = b.CLE_ID
19195: AND c.cle_id = a.cle_id
19205:
19206: -- hkamdar 05-Oct-2006. Bug# 5361663.
19207: -- Updating price_uom to NULL for Covered levels - Customer, Party and Site.
19208:
19209: Update OKS_K_LINES_B
19210: Set PRICE_UOM = NULL
19211: WHERE CLE_ID IN (SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
19212: AND LSE_ID IN (8,10,35));
19213: -- End hkamdar 05-Oct-2006. Bug# 5361663.
19215: End;
19216: ---------------------------------------------------------------------------------
19217: --------------------------------------------------------------------------------
19218: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
19219: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
19220: --of all top lines respectively
19221: Begin
19222: UPDATE OKC_K_HEADERS_B a
19223: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
19229:
19230: UPDATE OKS_K_HEADERS_B a
19231: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19232: FROM
19233: OKS_K_LINES_B b
19234: ,OKC_K_LINES_B c
19235: where b.DNZ_CHR_ID = a.CHR_ID
19236: and b.CLE_ID = c.ID
19237: and c.CLE_ID IS NULL
20038: WHERE OKCLINE.LSE_ID IN (7,9,25)
20039: AND OKCLINE.id = p_line_id
20040: AND OKCLINE.ID = OKCREL.CLE_ID
20041: AND OEL.LINE_ID = OKCREL.OBJECT1_ID1;
20042: l_toplvl_uom_code OKS_K_LINES_B.toplvl_uom_code%TYPE;
20043: -- 03-Nov-2006 hkamdar. Bug# 5623498.
20044:
20045: -------------Cursor to get default status for line-------------------------
20046: CURSOR get_default_status IS
20076: /*if p_include_cancelled_lines = 'Y' AND P_INCLUDE_TERMINATED_LINES = 'Y' */
20077: ----------------------------------------------------------------------------
20078: -- Cursor to get rows to be copied from:
20079: -- OKC_K_LINES_B_TEMP
20080: -- OKS_K_LINES_B
20081: -- OKC_K_ITEMS
20082: -- for a specified Source Contract and for the lines chosen by the user for copy.
20083:
20084: CURSOR GetOKCOKSLinesB_Temp(P_From_Chr_ID IN NUMBER
20230: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20231: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20232: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20233: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20234: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20235: ,oksB.ID AS OksB_OldOksLineID
20236: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20237: ,oksB.CLE_ID AS OksB_CLE_ID
20238: ,P_To_Chr_ID AS OksB_NewDnzChrID
20356: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
20357: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
20358: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
20359: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
20360: --------------------------------End of OKS_K_LINES_B columns-------------------------
20361: --------------------------------Start of OKC_K_ITEMS columns-------------------------
20362: ,okcI.ID AS OkcI_OldOkcItemID
20363: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
20364: ,okcI.CLE_ID AS OkcI_CLE_ID
20380: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
20381: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20382: FROM
20383: OKC_K_LINES_B_TEMP okcBT
20384: ,OKS_K_LINES_B oksB
20385: ,OKC_K_ITEMS okcI
20386: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20387: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20388: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
20392: /*IF p_include_cancelled_lines = 'Y' AND p_include_terminated_lines = 'N' */
20393: ----------------------------------------------------------------------------
20394: -- Cursor to get rows to be copied from:
20395: -- OKC_K_LINES_B_TEMP
20396: -- OKS_K_LINES_B
20397: -- OKC_K_ITEMS
20398: -- for a specified Source Contract and for the lines chosen by the user for copy.
20399:
20400: CURSOR GetOKCOKSLinesB_Temp_CANCEL(P_From_Chr_ID IN NUMBER
20546: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20547: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20548: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20549: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20550: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20551: ,oksB.ID AS OksB_OldOksLineID
20552: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20553: ,oksB.CLE_ID AS OksB_CLE_ID
20554: ,P_To_Chr_ID AS OksB_NewDnzChrID
20672: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
20673: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
20674: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
20675: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
20676: --------------------------------End of OKS_K_LINES_B columns-------------------------
20677: --------------------------------Start of OKC_K_ITEMS columns-------------------------
20678: ,okcI.ID AS OkcI_OldOkcItemID
20679: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
20680: ,okcI.CLE_ID AS OkcI_CLE_ID
20696: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
20697: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20698: FROM
20699: OKC_K_LINES_B_TEMP okcBT
20700: ,OKS_K_LINES_B oksB
20701: ,OKC_K_ITEMS okcI
20702: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20703: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20704: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
20709: /*if p_include_cancelled_lines= 'N' AND p_include_terminated_lines = 'Y' */
20710: ----------------------------------------------------------------------------
20711: -- Cursor to get rows to be copied from:
20712: -- OKC_K_LINES_B_TEMP
20713: -- OKS_K_LINES_B
20714: -- OKC_K_ITEMS
20715: -- for a specified Source Contract and for the lines chosen by the user for copy.
20716:
20717: CURSOR GetOKCOKSLinesB_Temp_TERMN(P_From_Chr_ID IN NUMBER
20863: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20864: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20865: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20866: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20867: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20868: ,oksB.ID AS OksB_OldOksLineID
20869: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20870: ,oksB.CLE_ID AS OksB_CLE_ID
20871: ,P_To_Chr_ID AS OksB_NewDnzChrID
20989: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
20990: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
20991: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
20992: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
20993: --------------------------------End of OKS_K_LINES_B columns-------------------------
20994: --------------------------------Start of OKC_K_ITEMS columns-------------------------
20995: ,okcI.ID AS OkcI_OldOkcItemID
20996: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
20997: ,okcI.CLE_ID AS OkcI_CLE_ID
21013: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
21014: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21015: FROM
21016: OKC_K_LINES_B_TEMP okcBT
21017: ,OKS_K_LINES_B oksB
21018: ,OKC_K_ITEMS okcI
21019: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21020: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21021: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
21026: /*if p_include_cancelled_lines = 'N' AND p_include_terminated_lines = 'N'*/
21027: ----------------------------------------------------------------------------
21028: -- Cursor to get rows to be copied from:
21029: -- OKC_K_LINES_B_TEMP
21030: -- OKS_K_LINES_B
21031: -- OKC_K_ITEMS
21032: -- for a specified Source Contract and for the lines chosen by the user for copy.
21033:
21034: CURSOR GetOKCOKSLinesB_Temp_EXCLUDE(P_From_Chr_ID IN NUMBER
21180: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
21181: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
21182: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
21183: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
21184: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
21185: ,oksB.ID AS OksB_OldOksLineID
21186: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
21187: ,oksB.CLE_ID AS OksB_CLE_ID
21188: ,P_To_Chr_ID AS OksB_NewDnzChrID
21306: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
21307: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
21308: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
21309: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
21310: --------------------------------End of OKS_K_LINES_B columns-------------------------
21311: --------------------------------Start of OKC_K_ITEMS columns-------------------------
21312: ,okcI.ID AS OkcI_OldOkcItemID
21313: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
21314: ,okcI.CLE_ID AS OkcI_CLE_ID
21330: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
21331: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21332: FROM
21333: OKC_K_LINES_B_TEMP okcBT
21334: ,OKS_K_LINES_B oksB
21335: ,OKC_K_ITEMS okcI
21336: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21337: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21338: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
22060: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
22061: CONNECT BY PRIOR okcCL.ID = okcCL.CLE_ID
22062: START WITH okcCL.ID = (select a.COVERAGE_ID
22063: FROM
22064: OKS_K_LINES_B a
22065: where a.CLE_ID = ApiLinesRecTab.IDTab(i)
22066: );
22067:
22068: EXCEPTION
22684:
22685:
22686: -----------------------------------------------------------------------------
22687: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22688: --OKS_K_LINES_B and OKC_K_ITEMS
22689: ----------------------------------------------------------------------------
22690: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22691: ---for bulk insert into OKC_K_LINES_B, *****************
22692: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
22688: --OKS_K_LINES_B and OKC_K_ITEMS
22689: ----------------------------------------------------------------------------
22690: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22691: ---for bulk insert into OKC_K_LINES_B, *****************
22692: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
22693:
22694: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22695: FND_LOG.string(FND_LOG.level_statement
22696: ,l_module_name
22811: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
22812: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
22813: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
22814: -----------------End of record members for OKC_K_LINES_B columns
22815: -----------------Start of record members for OKS_K_LINES_B columns
22816: ,OKCOKSLinesRecTab.OksB_OldOksLineID
22817: ,OKCOKSLinesRecTab.OksB_NewOksLineID
22818: ,OKCOKSLinesRecTab.OksB_CLE_ID
22819: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
22907: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
22908: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
22909: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
22910: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
22911: -----------------End of record members for OKS_K_LINES_B columns
22912: -----------------Start of record members for OKC_K_ITEMS columns
22913: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
22914: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
22915: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23023: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23024: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23025: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23026: -----------------End of record members for OKC_K_LINES_B columns
23027: -----------------Start of record members for OKS_K_LINES_B columns
23028: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23029: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23030: ,OKCOKSLinesRecTab.OksB_CLE_ID
23031: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23119: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23120: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23121: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23122: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23123: -----------------End of record members for OKS_K_LINES_B columns
23124: -----------------Start of record members for OKC_K_ITEMS columns
23125: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23126: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23127: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23235: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23236: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23237: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23238: -----------------End of record members for OKC_K_LINES_B columns
23239: -----------------Start of record members for OKS_K_LINES_B columns
23240: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23241: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23242: ,OKCOKSLinesRecTab.OksB_CLE_ID
23243: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23331: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23332: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23333: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23334: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23335: -----------------End of record members for OKS_K_LINES_B columns
23336: -----------------Start of record members for OKC_K_ITEMS columns
23337: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23338: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23339: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23447: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23448: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23449: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23450: -----------------End of record members for OKC_K_LINES_B columns
23451: -----------------Start of record members for OKS_K_LINES_B columns
23452: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23453: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23454: ,OKCOKSLinesRecTab.OksB_CLE_ID
23455: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23543: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23544: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23545: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23546: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23547: -----------------End of record members for OKS_K_LINES_B columns
23548: -----------------Start of record members for OKC_K_ITEMS columns
23549: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23550: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23551: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23779: );
23780: End If;
23781:
23782:
23783: ---Bulk insert into OKS_K_LINES_B---
23784:
23785: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23786: INSERT INTO OKS_K_LINES_B(
23787: ID
23782:
23783: ---Bulk insert into OKS_K_LINES_B---
23784:
23785: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23786: INSERT INTO OKS_K_LINES_B(
23787: ID
23788: ,CLE_ID
23789: ,DNZ_CHR_ID
23790: ,DISCOUNT_LIST
23997:
23998: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
23999: FND_LOG.string(FND_LOG.level_statement
24000: ,l_module_name
24001: ,'After BULK INSERT into OKS_K_LINES_B'
24002: );
24003: End If;
24004:
24005: ---Bulk insert into OKC_K_ITEMS---
24113: RAISE G_EXCEPTION_HALT_VALIDATION;
24114: END IF;
24115: END IF;
24116:
24117: UPDATE OKS_K_LINES_B
24118: SET
24119: TAX_AMOUNT = lx_rail_rec.TAX_VALUE
24120: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24121: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24235: RAISE G_EXCEPTION_HALT_VALIDATION;
24236: END IF;
24237: END IF;
24238:
24239: UPDATE OKS_K_LINES_B
24240: SET
24241: LOCKED_PRICE_LIST_ID = l_locked_price_list_id
24242: ,LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id
24243: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24296: RAISE G_EXCEPTION_HALT_VALIDATION;
24297: END IF;
24298: END IF;
24299:
24300: UPDATE OKS_K_LINES_B
24301: SET
24302: TRXN_EXTENSION_ID = l_trxn_extension_id
24303: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24304: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24306: If SQL%NOTFOUND then
24307: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
24308: FND_LOG.string(FND_LOG.level_statement
24309: ,l_module_name||'.Create_Transaction_Extension'
24310: ,'After call to Create_Transaction_Extension, Error in Update OKS_K_LINES_B, row not found for update'
24311: );
24312: END IF;
24313:
24314: l_return_status := FND_API.G_RET_STS_ERROR;
24364: FETCH get_toplvl_uom INTO l_toplvl_uom_code;
24365: CLOSE get_toplvl_uom;
24366:
24367: IF l_toplvl_uom_code IS NOT NULL then
24368: UPDATE OKS_K_LINES_B
24369: SET toplvl_uom_code = l_toplvl_uom_code
24370: WHERE id = OKCOKSLinesRecTab.OksB_NewOksLineID(i);
24371: END IF;
24372:
24413: --------End of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
24414:
24415: END IF; ---End of Check of P_Lines_Tbl.COUNT > 0 *****************
24416: ---for bulk insert into OKC_K_LINES_B, *****************
24417: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24418:
24419:
24420: ----------------------------------------------------------------------------
24421: -- Update of OKC_K_LINES_B
24420: ----------------------------------------------------------------------------
24421: -- Update of OKC_K_LINES_B
24422: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24426: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24427: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24428: ----------------------------------------------------------------------------
24421: -- Update of OKC_K_LINES_B
24422: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24426: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24427: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24428: ----------------------------------------------------------------------------
24429: Begin
24422: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24426: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24427: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24428: ----------------------------------------------------------------------------
24429: Begin
24430:
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24426: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24427: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24428: ----------------------------------------------------------------------------
24429: Begin
24430:
24431: UPDATE OKC_K_LINES_B a
24439: AND a.CLE_ID IS NULL
24440: AND a.LSE_ID <> 46; -- Subscription Lines do not have sublines. So they shouldnt be updated.
24441:
24442:
24443: UPDATE OKS_K_LINES_B a
24444: SET a.COVERAGE_ID = (select b.ID
24445: FROM
24446: OKC_K_LINES_B b
24447: ,OKC_K_LINES_B_TEMP bt
24455: AND a.CLE_ID in (SELECT b.ID from OKC_K_LINES_B_TEMP b where b.LINE_EXISTS_YN = 'N'
24456: and b.LSE_ID in (1,19));
24457:
24458:
24459: UPDATE OKS_K_LINES_B a
24460: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24461: FROM
24462: OKS_K_LINES_B b
24463: ,OKC_K_LINES_B c
24458:
24459: UPDATE OKS_K_LINES_B a
24460: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24461: FROM
24462: OKS_K_LINES_B b
24463: ,OKC_K_LINES_B c
24464: WHERE
24465: c.ID = b.CLE_ID
24466: AND c.cle_id = a.cle_id
24484: IF (P_Target_Contract_New_YN = 'N') then
24485: IF (l_TrgContractPPSetupExists_YN = 'Y') then --Partial Period Setup exists in Target Contract Header
24486:
24487: --Update price_uom of copied top lines and their sublines
24488: UPDATE OKS_K_LINES_B a
24489: SET a.PRICE_UOM = l_Target_Hdr_Price_UOM
24490: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24491: where
24492: (bt.cle_id is null --Top Line
24502: )
24503: );
24504:
24505: --Update of PRICE_UOM of Sublines that were copied to existing Top lines.
24506: UPDATE OKS_K_LINES_B a
24507: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24508: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24509: where c.id = a.CLE_ID)
24510: )
24503: );
24504:
24505: --Update of PRICE_UOM of Sublines that were copied to existing Top lines.
24506: UPDATE OKS_K_LINES_B a
24507: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24508: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24509: where c.id = a.CLE_ID)
24510: )
24511: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24521:
24522: /****Not required: If PPSetup DOES NOT EXIST on Target Header, then the Price UOM on
24523: copied Top Lines and its sublines will be same as that of the Source Top Line/Sublines.
24524: --Update of PRICE_UOM on copied Top Lines and their Sublines
24525: UPDATE OKS_K_LINES_B a
24526: SET a.PRICE_UOM = nvl(l_Target_Hdr_Price_UOM,a.PRICE_UOM)
24527: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24528: where
24529: (bt.cle_id is null --Top Line
24536: );
24537: ******/
24538:
24539: --Update PRICE_UOM of Sub Lines that were copied to existing top lines
24540: UPDATE OKS_K_LINES_B a
24541: SET a.PRICE_UOM = nvl(
24542: (select b.price_uom from oks_k_lines_b b
24543: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24544: where c.id = a.CLE_ID)
24538:
24539: --Update PRICE_UOM of Sub Lines that were copied to existing top lines
24540: UPDATE OKS_K_LINES_B a
24541: SET a.PRICE_UOM = nvl(
24542: (select b.price_uom from oks_k_lines_b b
24543: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24544: where c.id = a.CLE_ID)
24545: )
24546: ,a.PRICE_UOM
24562: End;
24563: --------------------------------------------------------------------------------
24564: --------------------------------------------------------------------------------
24565: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
24566: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
24567: --of all top lines respectively
24568: Begin
24569: UPDATE OKC_K_HEADERS_B a
24570: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
24576:
24577: UPDATE OKS_K_HEADERS_B a
24578: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24579: FROM
24580: OKS_K_LINES_B b
24581: ,OKC_K_LINES_B c
24582: where b.DNZ_CHR_ID = a.CHR_ID
24583: and b.CLE_ID = c.ID
24584: and c.CLE_ID IS NULL
26066: --end npalepu
26067: WHERE DNZ_CHR_ID = P_To_Chr_ID
26068: AND CLE_ID IS NULL;
26069:
26070: --Update OKS_K_LINES_B--
26071: l_stage := 'Updating OKS_K_LINES_B';
26072: UPDATE OKS_K_LINES_B
26073: SET
26074: commitment_id = null
26067: WHERE DNZ_CHR_ID = P_To_Chr_ID
26068: AND CLE_ID IS NULL;
26069:
26070: --Update OKS_K_LINES_B--
26071: l_stage := 'Updating OKS_K_LINES_B';
26072: UPDATE OKS_K_LINES_B
26073: SET
26074: commitment_id = null
26075: ,cust_po_number = null
26068: AND CLE_ID IS NULL;
26069:
26070: --Update OKS_K_LINES_B--
26071: l_stage := 'Updating OKS_K_LINES_B';
26072: UPDATE OKS_K_LINES_B
26073: SET
26074: commitment_id = null
26075: ,cust_po_number = null
26076: ,cust_po_number_req_yn = null
26124: WHERE DNZ_CHR_ID = P_To_Chr_ID
26125: AND CLE_ID IS NULL
26126: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26127:
26128: --Update OKS_K_LINES_B--
26129: l_stage := 'Updating OKS_K_LINES_B';
26130: UPDATE OKS_K_LINES_B
26131: SET
26132: commitment_id = null
26125: AND CLE_ID IS NULL
26126: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26127:
26128: --Update OKS_K_LINES_B--
26129: l_stage := 'Updating OKS_K_LINES_B';
26130: UPDATE OKS_K_LINES_B
26131: SET
26132: commitment_id = null
26133: ,cust_po_number = null
26126: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26127:
26128: --Update OKS_K_LINES_B--
26129: l_stage := 'Updating OKS_K_LINES_B';
26130: UPDATE OKS_K_LINES_B
26131: SET
26132: commitment_id = null
26133: ,cust_po_number = null
26134: ,cust_po_number_req_yn = null