[Home] [Help]
9178: , oksTL.REACT_TIME_NAME AS oksTL_REACT_TIME_NAME
9179: , oksTL.SECURITY_GROUP_ID AS oksTL_SECURITY_GROUP_ID
9180: FROM
9181: OKS_K_LINES_TL oksTL
9182: ,OKS_K_LINES_B oksB
9183: WHERE oksTL.ID = oksB.ORIG_SYSTEM_ID1
9184: AND oksB.DNZ_CHR_ID = P_To_Chr_ID;
9185:
9186: CURSOR GetOksTLPartial IS
9196: , oksTL.REACT_TIME_NAME AS oksTL_REACT_TIME_NAME
9197: , oksTL.SECURITY_GROUP_ID AS oksTL_SECURITY_GROUP_ID
9198: FROM
9199: OKS_K_LINES_TL oksTL
9200: ,OKS_K_LINES_B oksB
9201: ,OKC_K_LINES_B_TEMP okcBT
9202: WHERE oksTL.ID = oksB.ORIG_SYSTEM_ID1
9203: AND oksB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
9204: AND oksB.CLE_ID = okcBT.ID
13476: WHERE a.ID = LineIDTab(i);
13477: End;
13478: Begin
13479: FORALL j IN LineIDTab.FIRST..LineIDTab.LAST
13480: UPDATE OKS_K_LINES_B a
13481: SET a.ACCT_RULE_ID = nvl(a.ACCT_RULE_ID,G_TrgHdrAcctRuleID)
13482: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
13483: and a.CLE_ID = LineIDTab(j);
13484: End;
13583: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13584: ) as INV_RULE_ID
13585: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13586: ,decode(oksB.BILLING_SCHEDULE_TYPE
13587: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13588: ,oksB.BILLING_SCHEDULE_TYPE
13589: ) -- its a subline
13590: ) as BILLING_SCHEDULE_TYPE
13591: ,okcB.START_DATE as START_DATE
13593: ,okcB_Source.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13594: ,NULL as GEN_SUBLINE_BSCH_YN
13595: from
13596: OKC_K_LINES_B okcB
13597: ,OKS_K_LINES_B oksB
13598: ,OKC_K_LINES_B okcB_Source
13599: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13600: and okcB.ID = oksB.CLE_ID
13601: and okcB_Source.ID = okcB.ORIG_SYSTEM_ID1
13632: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13633: ) as INV_RULE_ID
13634: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13635: ,decode(oksB.BILLING_SCHEDULE_TYPE
13636: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13637: ,oksB.BILLING_SCHEDULE_TYPE
13638: ) -- its a subline
13639: ) as BILLING_SCHEDULE_TYPE
13640: ,okcB.START_DATE as START_DATE
13642: ,okcBT.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13643: ,okcBT.GEN_SUBLINE_BSCH_YN as GEN_SUBLINE_BSCH_YN
13644: from
13645: OKC_K_LINES_B okcB
13646: ,OKS_K_LINES_B oksB
13647: ,OKC_K_LINES_B_TEMP okcBT
13648: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13649: and okcB.ID = oksB.CLE_ID
13650: and okcBT.ID = okcB.ID
14036: l_tce_code VARCHAR2(100);
14037: l_uom_quantity NUMBER;
14038: l_level_periods NUMBER;
14039:
14040: l_resultant_PriceUOM OKS_K_LINES_B.PRICE_UOM%TYPE;
14041:
14042: Begin
14043: IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
14044: FND_LOG.string(FND_LOG.level_procedure
14272: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14273: ) as INV_RULE_ID
14274: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14275: ,decode(oksB.BILLING_SCHEDULE_TYPE
14276: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14277: ,oksB.BILLING_SCHEDULE_TYPE
14278: ) -- its a subline
14279: ) as BILLING_SCHEDULE_TYPE
14280: ,okcB.START_DATE as START_DATE
14290: ,oksB.USAGE_TYPE as USAGE_TYPE
14291: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14292: from
14293: OKC_K_LINES_B okcB
14294: ,OKS_K_LINES_B oksB
14295: where okcB.CHR_ID = P_To_Chr_ID
14296: and okcB.ID = oksB.CLE_ID;
14297: -- End hkamdar
14298:
14309: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14310: ) as INV_RULE_ID
14311: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14312: ,decode(oksB.BILLING_SCHEDULE_TYPE
14313: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14314: ,oksB.BILLING_SCHEDULE_TYPE
14315: ) -- its a subline
14316: ) as BILLING_SCHEDULE_TYPE
14317: ,okcB.START_DATE as START_DATE
14327: ,oksB.USAGE_TYPE as USAGE_TYPE
14328: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14329: from
14330: OKC_K_LINES_B okcB
14331: ,OKS_K_LINES_B oksB
14332: ,OKC_K_LINES_B_TEMP okcBT
14333: where okcB.DNZ_CHR_ID = P_To_Chr_ID
14334: and okcB.ID = oksB.CLE_ID
14335: and okcBT.ID = okcB.ID
14410: l_SrcLnStreams_Exists_YN VARCHAR2(1);
14411: -- end hkamdar
14412: l_stage varchar2(100) := null;
14413:
14414: l_resultant_price_uom OKS_K_LINES_B.PRICE_UOM%TYPE;
14415: l_tce_code VARCHAR2(100);
14416: l_uom_quantity NUMBER;
14417: l_level_periods NUMBER;
14418:
15412: ,b.offset_duration
15413: ,b.offset_period
15414: from
15415: OKC_K_LINES_B_TEMP a
15416: ,OKS_K_LINES_B b
15417: where
15418: a.dnz_chr_id = P_To_Chr_ID
15419: and a.lse_id in (3,21)
15420: and a.orig_system_id1 = b.cle_id(+)
15422:
15423: l_BP_ID NUMBER;
15424: l_BP_Start_Date DATE;
15425: l_BP_End_Date DATE;
15426: l_BP_Offset_Duration OKS_K_LINES_B.OFFSET_DURATION%TYPE;
15427: l_BP_Offset_Period OKS_K_LINES_B.OFFSET_PERIOD%TYPE;
15428:
15429: l_Resultant_Start_Date DATE;
15430:
15423: l_BP_ID NUMBER;
15424: l_BP_Start_Date DATE;
15425: l_BP_End_Date DATE;
15426: l_BP_Offset_Duration OKS_K_LINES_B.OFFSET_DURATION%TYPE;
15427: l_BP_Offset_Period OKS_K_LINES_B.OFFSET_PERIOD%TYPE;
15428:
15429: l_Resultant_Start_Date DATE;
15430:
15431: CURSOR getBPLineandChildren IS
15634: AND OKCLINE.id = p_line_id
15635: AND OKCLINE.ID = OKCREL.CLE_ID
15636: AND OEL.LINE_ID = OKCREL.OBJECT1_ID1;
15637:
15638: l_toplvl_uom_code OKS_K_LINES_B.toplvl_uom_code%TYPE;
15639: -- 03-Nov-2006 hkamdar. Bug# 5623498.
15640:
15641: -- Bug# 5531198 hkamdar 21-Sep-2006
15642: ---------------------------------------------------------------------------
15653:
15654: /*if p_include_cancelled_lines 'Y' AND p_include_terminated_lines = 'Y' */
15655: /* Cursor to get rows to be copied from:
15656: OKC_K_LINES_B
15657: OKS_K_LINES_B
15658: OKC_K_ITEMS
15659: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15660: CURSOR GetOKCOKSLinesB_Copy(P_From_Chr_ID IN NUMBER
15661: ,P_To_Chr_ID IN NUMBER) IS
15770: ,NULL AS OkcB_CANCELLED_AMOUNT
15771: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
15772: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
15773: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
15774: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
15775: ,oksB.ID AS OksB_OldOksLineID
15776: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
15777: ,oksB.CLE_ID AS OksB_CLE_ID
15778: ,P_To_Chr_ID AS OksB_NewDnzChrID
15879: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
15880: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
15881: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
15882: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
15883: --------------------------------End of OKS_K_LINES_B columns-------------------------
15884: --------------------------------Start of OKC_K_ITEMS columns-------------------------
15885: ,okcI.ID AS OkcI_OldOkcItemID
15886: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
15887: ,okcI.CLE_ID AS OkcI_CLE_ID
15904: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
15905: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
15906: FROM
15907: OKC_K_LINES_B okcB
15908: ,OKS_K_LINES_B oksB
15909: ,OKC_K_ITEMS okcI
15910: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
15911: AND okcB.ID = oksB.CLE_ID(+)
15912: AND okcB.ID = okcI.CLE_ID(+)
15917:
15918: /*if p_include_cancelled_lines ='Y' and p_include_terminated_lines = 'N'*/
15919: /* Cursor to get rows to be copied from:
15920: OKC_K_LINES_B
15921: OKS_K_LINES_B
15922: OKC_K_ITEMS
15923: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15924: CURSOR GetOKCOKSLinesB_Copy_CANCEL(P_From_Chr_ID IN NUMBER
15925: ,P_To_Chr_ID IN NUMBER) IS
16034: ,NULL AS OkcB_CANCELLED_AMOUNT
16035: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16036: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16037: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16038: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16039: ,oksB.ID AS OksB_OldOksLineID
16040: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16041: ,oksB.CLE_ID AS OksB_CLE_ID
16042: ,P_To_Chr_ID AS OksB_NewDnzChrID
16143: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16144: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16145: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16146: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16147: --------------------------------End of OKS_K_LINES_B columns-------------------------
16148: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16149: ,okcI.ID AS OkcI_OldOkcItemID
16150: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16151: ,okcI.CLE_ID AS OkcI_CLE_ID
16168: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16169: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
16170: FROM
16171: OKC_K_LINES_B okcB
16172: ,OKS_K_LINES_B oksB
16173: ,OKC_K_ITEMS okcI
16174: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16175: AND okcB.ID = oksB.CLE_ID(+)
16176: AND okcB.ID = okcI.CLE_ID(+)
16182:
16183: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'Y' */
16184: /* Cursor to get rows to be copied from:
16185: OKC_K_LINES_B
16186: OKS_K_LINES_B
16187: OKC_K_ITEMS
16188: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16189: CURSOR GetOKCOKSLinesB_Copy_TERMN(P_From_Chr_ID IN NUMBER
16190: ,P_To_Chr_ID IN NUMBER) IS
16299: ,NULL AS OkcB_CANCELLED_AMOUNT
16300: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16301: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16302: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16303: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16304: ,oksB.ID AS OksB_OldOksLineID
16305: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16306: ,oksB.CLE_ID AS OksB_CLE_ID
16307: ,P_To_Chr_ID AS OksB_NewDnzChrID
16408: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16409: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16410: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16411: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16412: --------------------------------End of OKS_K_LINES_B columns-------------------------
16413: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16414: ,okcI.ID AS OkcI_OldOkcItemID
16415: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16416: ,okcI.CLE_ID AS OkcI_CLE_ID
16433: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16434: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
16435: FROM
16436: OKC_K_LINES_B okcB
16437: ,OKS_K_LINES_B oksB
16438: ,OKC_K_ITEMS okcI
16439: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16440: AND okcB.ID = oksB.CLE_ID(+)
16441: AND okcB.ID = okcI.CLE_ID(+)
16447:
16448: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'N' */
16449: /* Cursor to get rows to be copied from:
16450: OKC_K_LINES_B
16451: OKS_K_LINES_B
16452: OKC_K_ITEMS
16453: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16454: CURSOR GetOKCOKSLinesB_Copy_exclude(P_From_Chr_ID IN NUMBER
16455: ,P_To_Chr_ID IN NUMBER) IS
16564: ,NULL AS OkcB_CANCELLED_AMOUNT
16565: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16566: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16567: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16568: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16569: ,oksB.ID AS OksB_OldOksLineID
16570: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16571: ,oksB.CLE_ID AS OksB_CLE_ID
16572: ,P_To_Chr_ID AS OksB_NewDnzChrID
16673: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16674: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16675: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16676: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16677: --------------------------------End of OKS_K_LINES_B columns-------------------------
16678: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16679: ,okcI.ID AS OkcI_OldOkcItemID
16680: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
16681: ,okcI.CLE_ID AS OkcI_CLE_ID
16698: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16699: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
16700: FROM
16701: OKC_K_LINES_B okcB
16702: ,OKS_K_LINES_B oksB
16703: ,OKC_K_ITEMS okcI
16704: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16705: AND okcB.ID = oksB.CLE_ID(+)
16706: AND okcB.ID = okcI.CLE_ID(+)
16713:
16714:
16715: /* Cursor to get rows to be copied from:
16716: OKC_K_LINES_B
16717: OKS_K_LINES_B
16718: OKC_K_ITEMS
16719: for a specified Source Contracti. This cursor is used when P_Renew_Ref_YN = 'Y'. It filters out
16720: the lines that have been renewed,cancelled or terminated or have been marked as 'DNR' (Do not renew) */
16721:
16817: ,NULL AS OkcB_CANCELLED_AMOUNT
16818: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16819: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16820: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16821: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16822: ,oksB.ID AS OksB_OldOksLineID
16823: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16824: ,oksB.CLE_ID AS OksB_CLE_ID
16825: ,P_To_Chr_ID AS OksB_NewDnzChrID
16926: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
16927: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
16928: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
16929: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
16930: --------------------------------End of OKS_K_LINES_B columns-------------------------
16931: --------------------------------Start of OKC_K_ITEMS columns-------------------------
16932: ,okcI.ID AS OkcI_OldOkcItemID
16933: ,To_number(rawtohex(sys_guid()), HexFormatStr)AS OkcI_NewOkcItemID
16934: ,okcI.CLE_ID AS OkcI_CLE_ID
16951: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16952: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
16953: FROM
16954: OKC_K_LINES_B okcB
16955: ,OKS_K_LINES_B oksB
16956: ,OKC_K_ITEMS okcI
16957: WHERE
16958: okcB.ID = oksB.CLE_ID(+)
16959: AND okcB.ID = okcI.CLE_ID(+)
17244: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17245: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17246: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17247: -----------------End of record members for OKC_K_LINES_B columns
17248: -----------------Start of record members for OKS_K_LINES_B columns
17249: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17250: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17251: ,OKCOKSLinesRecTab.OksB_CLE_ID
17252: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17340: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17341: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17342: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17343: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17344: -----------------End of record members for OKS_K_LINES_B columns
17345: -----------------Start of record members for OKC_K_ITEMS columns
17346: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17347: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17348: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17459: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17460: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17461: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17462: -----------------End of record members for OKC_K_LINES_B columns
17463: -----------------Start of record members for OKS_K_LINES_B columns
17464: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17465: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17466: ,OKCOKSLinesRecTab.OksB_CLE_ID
17467: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17555: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17556: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17557: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17558: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17559: -----------------End of record members for OKS_K_LINES_B columns
17560: -----------------Start of record members for OKC_K_ITEMS columns
17561: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17562: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17563: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17673: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17674: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17675: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17676: -----------------End of record members for OKC_K_LINES_B columns
17677: -----------------Start of record members for OKS_K_LINES_B columns
17678: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17679: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17680: ,OKCOKSLinesRecTab.OksB_CLE_ID
17681: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17769: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17770: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17771: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17772: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17773: -----------------End of record members for OKS_K_LINES_B columns
17774: -----------------Start of record members for OKC_K_ITEMS columns
17775: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17776: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17777: ,OKCOKSLinesRecTab.OkcI_CLE_ID
17886: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17887: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17888: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17889: -----------------End of record members for OKC_K_LINES_B columns
17890: -----------------Start of record members for OKS_K_LINES_B columns
17891: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17892: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17893: ,OKCOKSLinesRecTab.OksB_CLE_ID
17894: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
17982: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
17983: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
17984: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
17985: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
17986: -----------------End of record members for OKS_K_LINES_B columns
17987: -----------------Start of record members for OKC_K_ITEMS columns
17988: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
17989: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
17990: ,OKCOKSLinesRecTab.OkcI_CLE_ID
18107: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
18108: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG -- pseudo column
18109: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
18110: -----------------End of record members for OKC_K_LINES_B columns
18111: -----------------Start of record members for OKS_K_LINES_B columns
18112: ,OKCOKSLinesRecTab.OksB_OldOksLineID
18113: ,OKCOKSLinesRecTab.OksB_NewOksLineID
18114: ,OKCOKSLinesRecTab.OksB_CLE_ID
18115: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
18203: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
18204: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
18205: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
18206: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
18207: -----------------End of record members for OKS_K_LINES_B columns
18208: -----------------Start of record members for OKC_K_ITEMS columns
18209: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
18210: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
18211: ,OKCOKSLinesRecTab.OkcI_CLE_ID
18453:
18454: End;
18455:
18456: Begin
18457: --Bulk Insert into OKS_K_LINES_B
18458: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18459: INSERT INTO OKS_K_LINES_B(
18460: ID
18461: ,CLE_ID
18455:
18456: Begin
18457: --Bulk Insert into OKS_K_LINES_B
18458: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18459: INSERT INTO OKS_K_LINES_B(
18460: ID
18461: ,CLE_ID
18462: ,DNZ_CHR_ID
18463: ,DISCOUNT_LIST
18666:
18667: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
18668: FND_LOG.string(FND_LOG.level_statement
18669: ,l_module_name
18670: ,'AFTER INSERT into OKS_K_LINES_B '
18671: );
18672: END IF;
18673:
18674: EXCEPTION
18676: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
18677: IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
18678: FND_LOG.string(FND_LOG.level_unexpected
18679: ,l_module_name
18680: ,'EXCEPTION during insert into OKS_K_LINES_B '||SQLERRM
18681: );
18682: END IF;
18683:
18684: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
19024: RAISE G_EXCEPTION_HALT_VALIDATION;
19025: END IF;
19026: END IF;
19027:
19028: UPDATE OKS_K_LINES_B
19029: SET
19030: TAX_AMOUNT = lx_rail_rec.TAX_VALUE
19031: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19032: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19037: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
19038:
19039:
19040: IF (l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
19041: UPDATE OKS_K_LINES_B
19042: SET TAX_INCLUSIVE_YN = 'N'
19043: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19044: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19045: ELSE
19043: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19044: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19045: ELSE
19046:
19047: UPDATE OKS_K_LINES_B
19048: SET TAX_AMOUNT = lx_rail_rec.TAX_VALUE
19049: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19050: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19051:
19051:
19052: END IF;
19053: ELSE
19054:
19055: UPDATE OKS_K_LINES_B
19056: SET TAX_AMOUNT = lx_rail_rec.TAX_VALUE
19057: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19058: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19059:
19107: RAISE G_EXCEPTION_HALT_VALIDATION;
19108: END IF;
19109: END IF;
19110:
19111: UPDATE OKS_K_LINES_B
19112: SET
19113: LOCKED_PRICE_LIST_ID = l_locked_price_list_id
19114: ,LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id
19115: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19118: If SQL%NOTFOUND then
19119: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19120: FND_LOG.string(FND_LOG.level_statement
19121: ,l_module_name||'.QP_LOCK_PRICELIST_GRP.Lock_Price'
19122: ,'After call to QP_LOCK_PRICELIST_GRP.Lock_Price, Error in Update OKS_K_LINES_B, row not found for update'
19123: );
19124: END IF;
19125:
19126: l_return_status := FND_API.G_RET_STS_ERROR;
19176: RAISE G_EXCEPTION_HALT_VALIDATION;
19177: END IF;
19178: END IF;
19179:
19180: UPDATE OKS_K_LINES_B
19181: SET
19182: TRXN_EXTENSION_ID = l_trxn_extension_id
19183: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
19184: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
19186: If SQL%NOTFOUND then
19187: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19188: FND_LOG.string(FND_LOG.level_statement
19189: ,l_module_name||'.Create_Transaction_Extension'
19190: ,'After call to Create_Transaction_Extension, Error in Update OKS_K_LINES_B, row not found for update'
19191: );
19192: END IF;
19193:
19194: l_return_status := FND_API.G_RET_STS_ERROR;
19207: FETCH get_toplvl_uom INTO l_toplvl_uom_code;
19208: CLOSE get_toplvl_uom;
19209:
19210: IF l_toplvl_uom_code IS NOT NULL then
19211: UPDATE OKS_K_LINES_B
19212: SET toplvl_uom_code = l_toplvl_uom_code
19213: WHERE id = OKCOKSLinesRecTab.OksB_NewOksLineID(i);
19214: END IF;
19215:
19250: ELSIF (P_Renew_Ref_YN = 'Y') THEN
19251: EXIT WHEN GetOKCOKSLinesB_Renew%NOTFOUND;
19252: END IF;
19253:
19254: END LOOP; --End of Bulk fetch and insert into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS
19255:
19256: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19257: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19258: FND_LOG.string(FND_LOG.level_statement
19256: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19257: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19258: FND_LOG.string(FND_LOG.level_statement
19259: ,l_module_name
19260: ,'After BULK FETCH and BULK INSERT into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS'
19261: );
19262: END IF;
19263: END IF;
19264:
19279: -- Update of OKC_K_LINES_B
19280: -- i) CLE_ID on OKC_K_LINES_B for sublines
19281: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19282: --
19283: -- Update of OKS_K_LINES_B
19284: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19285: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19286:
19287: Begin
19280: -- i) CLE_ID on OKC_K_LINES_B for sublines
19281: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19282: --
19283: -- Update of OKS_K_LINES_B
19284: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19285: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19286:
19287: Begin
19288:
19281: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19282: --
19283: -- Update of OKS_K_LINES_B
19284: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19285: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19286:
19287: Begin
19288:
19289: /* UPDATE OKC_K_LINES_B a
19306: a.DNZ_CHR_ID = P_To_Chr_ID
19307: AND a.CLE_ID IS NULL
19308: AND a.LSE_ID <> 46; -- Subscription Lines do not have sublines. So they shouldnt be updated.
19309:
19310: UPDATE OKS_K_LINES_B a
19311: SET a.COVERAGE_ID = (select b.ID
19312: FROM OKC_K_LINES_B b
19313: WHERE
19314: b.CLE_ID = a.CLE_ID
19319: where c.DNZ_CHR_ID = P_To_Chr_ID
19320: and c.LSE_ID in (1,19))
19321: AND a.STANDARD_COV_YN = 'N';
19322:
19323: UPDATE OKS_K_LINES_B a
19324: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19325: FROM
19326: OKS_K_LINES_B b
19327: ,OKC_K_LINES_B c
19322:
19323: UPDATE OKS_K_LINES_B a
19324: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19325: FROM
19326: OKS_K_LINES_B b
19327: ,OKC_K_LINES_B c
19328: WHERE
19329: c.ID = b.CLE_ID
19330: AND c.cle_id = a.cle_id
19340:
19341: -- hkamdar 05-Oct-2006. Bug# 5361663.
19342: -- Updating price_uom to NULL for Covered levels - Customer, Party and Site.
19343:
19344: Update OKS_K_LINES_B
19345: Set PRICE_UOM = NULL
19346: WHERE CLE_ID IN (SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
19347: AND LSE_ID IN (8,10,35));
19348: -- End hkamdar 05-Oct-2006. Bug# 5361663.
19350: End;
19351: ---------------------------------------------------------------------------------
19352: --------------------------------------------------------------------------------
19353: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
19354: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
19355: --of all top lines respectively
19356: Begin
19357: UPDATE OKC_K_HEADERS_B a
19358: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
19364:
19365: UPDATE OKS_K_HEADERS_B a
19366: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19367: FROM
19368: OKS_K_LINES_B b
19369: ,OKC_K_LINES_B c
19370: where b.DNZ_CHR_ID = a.CHR_ID
19371: and b.CLE_ID = c.ID
19372: and c.CLE_ID IS NULL
20185: WHERE OKCLINE.LSE_ID IN (7,9,25)
20186: AND OKCLINE.id = p_line_id
20187: AND OKCLINE.ID = OKCREL.CLE_ID
20188: AND OEL.LINE_ID = OKCREL.OBJECT1_ID1;
20189: l_toplvl_uom_code OKS_K_LINES_B.toplvl_uom_code%TYPE;
20190: -- 03-Nov-2006 hkamdar. Bug# 5623498.
20191:
20192: -------------Cursor to get default status for line-------------------------
20193: CURSOR get_default_status IS
20223: /*if p_include_cancelled_lines = 'Y' AND P_INCLUDE_TERMINATED_LINES = 'Y' */
20224: ----------------------------------------------------------------------------
20225: -- Cursor to get rows to be copied from:
20226: -- OKC_K_LINES_B_TEMP
20227: -- OKS_K_LINES_B
20228: -- OKC_K_ITEMS
20229: -- for a specified Source Contract and for the lines chosen by the user for copy.
20230:
20231: CURSOR GetOKCOKSLinesB_Temp(P_From_Chr_ID IN NUMBER
20377: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20378: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20379: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20380: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20381: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20382: ,oksB.ID AS OksB_OldOksLineID
20383: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20384: ,oksB.CLE_ID AS OksB_CLE_ID
20385: ,P_To_Chr_ID AS OksB_NewDnzChrID
20503: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
20504: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
20505: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
20506: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
20507: --------------------------------End of OKS_K_LINES_B columns-------------------------
20508: --------------------------------Start of OKC_K_ITEMS columns-------------------------
20509: ,okcI.ID AS OkcI_OldOkcItemID
20510: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
20511: ,okcI.CLE_ID AS OkcI_CLE_ID
20528: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20529: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
20530: FROM
20531: OKC_K_LINES_B_TEMP okcBT
20532: ,OKS_K_LINES_B oksB
20533: ,OKC_K_ITEMS okcI
20534: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20535: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20536: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
20540: /*IF p_include_cancelled_lines = 'Y' AND p_include_terminated_lines = 'N' */
20541: ----------------------------------------------------------------------------
20542: -- Cursor to get rows to be copied from:
20543: -- OKC_K_LINES_B_TEMP
20544: -- OKS_K_LINES_B
20545: -- OKC_K_ITEMS
20546: -- for a specified Source Contract and for the lines chosen by the user for copy.
20547:
20548: CURSOR GetOKCOKSLinesB_Temp_CANCEL(P_From_Chr_ID IN NUMBER
20694: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20695: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20696: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20697: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20698: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20699: ,oksB.ID AS OksB_OldOksLineID
20700: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20701: ,oksB.CLE_ID AS OksB_CLE_ID
20702: ,P_To_Chr_ID AS OksB_NewDnzChrID
20820: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
20821: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
20822: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
20823: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
20824: --------------------------------End of OKS_K_LINES_B columns-------------------------
20825: --------------------------------Start of OKC_K_ITEMS columns-------------------------
20826: ,okcI.ID AS OkcI_OldOkcItemID
20827: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
20828: ,okcI.CLE_ID AS OkcI_CLE_ID
20845: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20846: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
20847: FROM
20848: OKC_K_LINES_B_TEMP okcBT
20849: ,OKS_K_LINES_B oksB
20850: ,OKC_K_ITEMS okcI
20851: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20852: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20853: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
20858: /*if p_include_cancelled_lines= 'N' AND p_include_terminated_lines = 'Y' */
20859: ----------------------------------------------------------------------------
20860: -- Cursor to get rows to be copied from:
20861: -- OKC_K_LINES_B_TEMP
20862: -- OKS_K_LINES_B
20863: -- OKC_K_ITEMS
20864: -- for a specified Source Contract and for the lines chosen by the user for copy.
20865:
20866: CURSOR GetOKCOKSLinesB_Temp_TERMN(P_From_Chr_ID IN NUMBER
21012: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
21013: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
21014: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
21015: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
21016: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
21017: ,oksB.ID AS OksB_OldOksLineID
21018: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
21019: ,oksB.CLE_ID AS OksB_CLE_ID
21020: ,P_To_Chr_ID AS OksB_NewDnzChrID
21138: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
21139: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
21140: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
21141: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
21142: --------------------------------End of OKS_K_LINES_B columns-------------------------
21143: --------------------------------Start of OKC_K_ITEMS columns-------------------------
21144: ,okcI.ID AS OkcI_OldOkcItemID
21145: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
21146: ,okcI.CLE_ID AS OkcI_CLE_ID
21163: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21164: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
21165: FROM
21166: OKC_K_LINES_B_TEMP okcBT
21167: ,OKS_K_LINES_B oksB
21168: ,OKC_K_ITEMS okcI
21169: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21170: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21171: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
21176: /*if p_include_cancelled_lines = 'N' AND p_include_terminated_lines = 'N'*/
21177: ----------------------------------------------------------------------------
21178: -- Cursor to get rows to be copied from:
21179: -- OKC_K_LINES_B_TEMP
21180: -- OKS_K_LINES_B
21181: -- OKC_K_ITEMS
21182: -- for a specified Source Contract and for the lines chosen by the user for copy.
21183:
21184: CURSOR GetOKCOKSLinesB_Temp_EXCLUDE(P_From_Chr_ID IN NUMBER
21330: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
21331: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
21332: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
21333: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
21334: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
21335: ,oksB.ID AS OksB_OldOksLineID
21336: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
21337: ,oksB.CLE_ID AS OksB_CLE_ID
21338: ,P_To_Chr_ID AS OksB_NewDnzChrID
21456: ,oksB.EXEMPT_CERTIFICATE_NUMBER AS OksB_EXEMPT_CERTIFICATE_NUMBER
21457: ,oksB.EXEMPT_REASON_CODE AS OksB_EXEMPT_REASON_CODE
21458: ,oksB.COVERAGE_ID AS OksB_COVERAGE_ID
21459: ,oksB.STANDARD_COV_YN AS OksB_STANDARD_COV_YN
21460: --------------------------------End of OKS_K_LINES_B columns-------------------------
21461: --------------------------------Start of OKC_K_ITEMS columns-------------------------
21462: ,okcI.ID AS OkcI_OldOkcItemID
21463: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcI_NewOkcItemID
21464: ,okcI.CLE_ID AS OkcI_CLE_ID
21481: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21482: ,oksB.COUNTER_VALUE_ID AS OksB_COUNTER_VALUE_ID
21483: FROM
21484: OKC_K_LINES_B_TEMP okcBT
21485: ,OKS_K_LINES_B oksB
21486: ,OKC_K_ITEMS okcI
21487: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21488: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21489: AND okcBT.ORIG_SYSTEM_ID1 = okcI.CLE_ID(+)
22211: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
22212: CONNECT BY PRIOR okcCL.ID = okcCL.CLE_ID
22213: START WITH okcCL.ID = (select a.COVERAGE_ID
22214: FROM
22215: OKS_K_LINES_B a
22216: where a.CLE_ID = ApiLinesRecTab.IDTab(i)
22217: );
22218:
22219: EXCEPTION
22835:
22836:
22837: -----------------------------------------------------------------------------
22838: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22839: --OKS_K_LINES_B and OKC_K_ITEMS
22840: ----------------------------------------------------------------------------
22841: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22842: ---for bulk insert into OKC_K_LINES_B, *****************
22843: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
22839: --OKS_K_LINES_B and OKC_K_ITEMS
22840: ----------------------------------------------------------------------------
22841: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22842: ---for bulk insert into OKC_K_LINES_B, *****************
22843: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
22844:
22845: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22846: FND_LOG.string(FND_LOG.level_statement
22847: ,l_module_name
22962: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
22963: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
22964: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
22965: -----------------End of record members for OKC_K_LINES_B columns
22966: -----------------Start of record members for OKS_K_LINES_B columns
22967: ,OKCOKSLinesRecTab.OksB_OldOksLineID
22968: ,OKCOKSLinesRecTab.OksB_NewOksLineID
22969: ,OKCOKSLinesRecTab.OksB_CLE_ID
22970: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23058: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23059: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23060: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23061: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23062: -----------------End of record members for OKS_K_LINES_B columns
23063: -----------------Start of record members for OKC_K_ITEMS columns
23064: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23065: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23066: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23175: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23176: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23177: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23178: -----------------End of record members for OKC_K_LINES_B columns
23179: -----------------Start of record members for OKS_K_LINES_B columns
23180: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23181: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23182: ,OKCOKSLinesRecTab.OksB_CLE_ID
23183: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23271: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23272: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23273: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23274: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23275: -----------------End of record members for OKS_K_LINES_B columns
23276: -----------------Start of record members for OKC_K_ITEMS columns
23277: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23278: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23279: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23388: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23389: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23390: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23391: -----------------End of record members for OKC_K_LINES_B columns
23392: -----------------Start of record members for OKS_K_LINES_B columns
23393: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23394: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23395: ,OKCOKSLinesRecTab.OksB_CLE_ID
23396: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23484: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23485: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23486: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23487: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23488: -----------------End of record members for OKS_K_LINES_B columns
23489: -----------------Start of record members for OKC_K_ITEMS columns
23490: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23491: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23492: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23601: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23602: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23603: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23604: -----------------End of record members for OKC_K_LINES_B columns
23605: -----------------Start of record members for OKS_K_LINES_B columns
23606: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23607: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23608: ,OKCOKSLinesRecTab.OksB_CLE_ID
23609: ,OKCOKSLinesRecTab.OksB_NewDnzChrID
23697: ,OKCOKSLinesRecTab.OksB_EXEMPT_CERTIFICATE_NUMBER
23698: ,OKCOKSLinesRecTab.OksB_EXEMPT_REASON_CODE
23699: ,OKCOKSLinesRecTab.OksB_COVERAGE_ID
23700: ,OKCOKSLinesRecTab.OksB_STANDARD_COV_YN
23701: -----------------End of record members for OKS_K_LINES_B columns
23702: -----------------Start of record members for OKC_K_ITEMS columns
23703: ,OKCOKSLinesRecTab.OkcI_OldOkcItemID
23704: ,OKCOKSLinesRecTab.OkcI_NewOkcItemID
23705: ,OKCOKSLinesRecTab.OkcI_CLE_ID
23934: );
23935: End If;
23936:
23937:
23938: ---Bulk insert into OKS_K_LINES_B---
23939:
23940: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23941: INSERT INTO OKS_K_LINES_B(
23942: ID
23937:
23938: ---Bulk insert into OKS_K_LINES_B---
23939:
23940: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23941: INSERT INTO OKS_K_LINES_B(
23942: ID
23943: ,CLE_ID
23944: ,DNZ_CHR_ID
23945: ,DISCOUNT_LIST
24154:
24155: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
24156: FND_LOG.string(FND_LOG.level_statement
24157: ,l_module_name
24158: ,'After BULK INSERT into OKS_K_LINES_B'
24159: );
24160: End If;
24161:
24162: ---Bulk insert into OKC_K_ITEMS---
24270: RAISE G_EXCEPTION_HALT_VALIDATION;
24271: END IF;
24272: END IF;
24273:
24274: UPDATE OKS_K_LINES_B
24275: SET
24276: TAX_AMOUNT = lx_rail_rec.TAX_VALUE
24277: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24278: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i); */
24290: );
24291: END IF;
24292: END IF;
24293: IF (l_return_status = FND_API.G_RET_STS_ERROR OR l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
24294: UPDATE OKS_K_LINES_B
24295: SET TAX_INCLUSIVE_YN = 'N'
24296: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24297: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24298: ELSE
24296: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24297: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24298: ELSE
24299:
24300: UPDATE OKS_K_LINES_B
24301: SET TAX_AMOUNT = lx_rail_rec.TAX_VALUE
24302: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24303: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24304:
24304:
24305: END IF;
24306: ELSE
24307:
24308: UPDATE OKS_K_LINES_B
24309: SET TAX_AMOUNT = lx_rail_rec.TAX_VALUE
24310: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24311: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24312:
24430: RAISE G_EXCEPTION_HALT_VALIDATION;
24431: END IF;
24432: END IF;
24433:
24434: UPDATE OKS_K_LINES_B
24435: SET
24436: LOCKED_PRICE_LIST_ID = l_locked_price_list_id
24437: ,LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id
24438: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24491: RAISE G_EXCEPTION_HALT_VALIDATION;
24492: END IF;
24493: END IF;
24494:
24495: UPDATE OKS_K_LINES_B
24496: SET
24497: TRXN_EXTENSION_ID = l_trxn_extension_id
24498: WHERE ID = OKCOKSLinesRecTab.OksB_NewOksLineID(i)
24499: AND ORIG_SYSTEM_ID1 = OKCOKSLinesRecTab.OksB_OldOksLineID(i);
24501: If SQL%NOTFOUND then
24502: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
24503: FND_LOG.string(FND_LOG.level_statement
24504: ,l_module_name||'.Create_Transaction_Extension'
24505: ,'After call to Create_Transaction_Extension, Error in Update OKS_K_LINES_B, row not found for update'
24506: );
24507: END IF;
24508:
24509: l_return_status := FND_API.G_RET_STS_ERROR;
24559: FETCH get_toplvl_uom INTO l_toplvl_uom_code;
24560: CLOSE get_toplvl_uom;
24561:
24562: IF l_toplvl_uom_code IS NOT NULL then
24563: UPDATE OKS_K_LINES_B
24564: SET toplvl_uom_code = l_toplvl_uom_code
24565: WHERE id = OKCOKSLinesRecTab.OksB_NewOksLineID(i);
24566: END IF;
24567:
24608: --------End of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
24609:
24610: END IF; ---End of Check of P_Lines_Tbl.COUNT > 0 *****************
24611: ---for bulk insert into OKC_K_LINES_B, *****************
24612: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24613:
24614:
24615: ----------------------------------------------------------------------------
24616: -- Update of OKC_K_LINES_B
24615: ----------------------------------------------------------------------------
24616: -- Update of OKC_K_LINES_B
24617: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24618: --
24619: -- Update of OKS_K_LINES_B
24620: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24621: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24622: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24623: ----------------------------------------------------------------------------
24616: -- Update of OKC_K_LINES_B
24617: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24618: --
24619: -- Update of OKS_K_LINES_B
24620: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24621: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24622: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24623: ----------------------------------------------------------------------------
24624: Begin
24617: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24618: --
24619: -- Update of OKS_K_LINES_B
24620: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24621: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24622: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24623: ----------------------------------------------------------------------------
24624: Begin
24625:
24618: --
24619: -- Update of OKS_K_LINES_B
24620: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24621: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24622: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24623: ----------------------------------------------------------------------------
24624: Begin
24625:
24626: UPDATE OKC_K_LINES_B a
24634: AND a.CLE_ID IS NULL
24635: AND a.LSE_ID <> 46; -- Subscription Lines do not have sublines. So they shouldnt be updated.
24636:
24637:
24638: UPDATE OKS_K_LINES_B a
24639: SET a.COVERAGE_ID = (select b.ID
24640: FROM
24641: OKC_K_LINES_B b
24642: ,OKC_K_LINES_B_TEMP bt
24650: AND a.CLE_ID in (SELECT b.ID from OKC_K_LINES_B_TEMP b where b.LINE_EXISTS_YN = 'N'
24651: and b.LSE_ID in (1,19));
24652:
24653:
24654: UPDATE OKS_K_LINES_B a
24655: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24656: FROM
24657: OKS_K_LINES_B b
24658: ,OKC_K_LINES_B c
24653:
24654: UPDATE OKS_K_LINES_B a
24655: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24656: FROM
24657: OKS_K_LINES_B b
24658: ,OKC_K_LINES_B c
24659: WHERE
24660: c.ID = b.CLE_ID
24661: AND c.cle_id = a.cle_id
24679: IF (P_Target_Contract_New_YN = 'N') then
24680: IF (l_TrgContractPPSetupExists_YN = 'Y') then --Partial Period Setup exists in Target Contract Header
24681:
24682: --Update price_uom of copied top lines and their sublines
24683: UPDATE OKS_K_LINES_B a
24684: SET a.PRICE_UOM = l_Target_Hdr_Price_UOM
24685: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24686: where
24687: (bt.cle_id is null --Top Line
24697: )
24698: );
24699:
24700: --Update of PRICE_UOM of Sublines that were copied to existing Top lines.
24701: UPDATE OKS_K_LINES_B a
24702: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24703: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24704: where c.id = a.CLE_ID)
24705: )
24698: );
24699:
24700: --Update of PRICE_UOM of Sublines that were copied to existing Top lines.
24701: UPDATE OKS_K_LINES_B a
24702: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24703: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24704: where c.id = a.CLE_ID)
24705: )
24706: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24716:
24717: /****Not required: If PPSetup DOES NOT EXIST on Target Header, then the Price UOM on
24718: copied Top Lines and its sublines will be same as that of the Source Top Line/Sublines.
24719: --Update of PRICE_UOM on copied Top Lines and their Sublines
24720: UPDATE OKS_K_LINES_B a
24721: SET a.PRICE_UOM = nvl(l_Target_Hdr_Price_UOM,a.PRICE_UOM)
24722: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24723: where
24724: (bt.cle_id is null --Top Line
24731: );
24732: ******/
24733:
24734: --Update PRICE_UOM of Sub Lines that were copied to existing top lines
24735: UPDATE OKS_K_LINES_B a
24736: SET a.PRICE_UOM = nvl(
24737: (select b.price_uom from oks_k_lines_b b
24738: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24739: where c.id = a.CLE_ID)
24733:
24734: --Update PRICE_UOM of Sub Lines that were copied to existing top lines
24735: UPDATE OKS_K_LINES_B a
24736: SET a.PRICE_UOM = nvl(
24737: (select b.price_uom from oks_k_lines_b b
24738: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24739: where c.id = a.CLE_ID)
24740: )
24741: ,a.PRICE_UOM
24757: End;
24758: --------------------------------------------------------------------------------
24759: --------------------------------------------------------------------------------
24760: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
24761: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
24762: --of all top lines respectively
24763: Begin
24764: UPDATE OKC_K_HEADERS_B a
24765: SET a.ESTIMATED_AMOUNT = nvl((select sum(b.PRICE_NEGOTIATED)
24771:
24772: UPDATE OKS_K_HEADERS_B a
24773: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24774: FROM
24775: OKS_K_LINES_B b
24776: ,OKC_K_LINES_B c
24777: where b.DNZ_CHR_ID = a.CHR_ID
24778: and b.CLE_ID = c.ID
24779: and c.CLE_ID IS NULL
26265: --end npalepu
26266: WHERE DNZ_CHR_ID = P_To_Chr_ID
26267: AND CLE_ID IS NULL;
26268:
26269: --Update OKS_K_LINES_B--
26270: l_stage := 'Updating OKS_K_LINES_B';
26271: UPDATE OKS_K_LINES_B
26272: SET
26273: commitment_id = null
26266: WHERE DNZ_CHR_ID = P_To_Chr_ID
26267: AND CLE_ID IS NULL;
26268:
26269: --Update OKS_K_LINES_B--
26270: l_stage := 'Updating OKS_K_LINES_B';
26271: UPDATE OKS_K_LINES_B
26272: SET
26273: commitment_id = null
26274: ,cust_po_number = null
26267: AND CLE_ID IS NULL;
26268:
26269: --Update OKS_K_LINES_B--
26270: l_stage := 'Updating OKS_K_LINES_B';
26271: UPDATE OKS_K_LINES_B
26272: SET
26273: commitment_id = null
26274: ,cust_po_number = null
26275: ,cust_po_number_req_yn = null
26323: WHERE DNZ_CHR_ID = P_To_Chr_ID
26324: AND CLE_ID IS NULL
26325: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26326:
26327: --Update OKS_K_LINES_B--
26328: l_stage := 'Updating OKS_K_LINES_B';
26329: UPDATE OKS_K_LINES_B
26330: SET
26331: commitment_id = null
26324: AND CLE_ID IS NULL
26325: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26326:
26327: --Update OKS_K_LINES_B--
26328: l_stage := 'Updating OKS_K_LINES_B';
26329: UPDATE OKS_K_LINES_B
26330: SET
26331: commitment_id = null
26332: ,cust_po_number = null
26325: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26326:
26327: --Update OKS_K_LINES_B--
26328: l_stage := 'Updating OKS_K_LINES_B';
26329: UPDATE OKS_K_LINES_B
26330: SET
26331: commitment_id = null
26332: ,cust_po_number = null
26333: ,cust_po_number_req_yn = null