[Home] [Help]
102: IS
103:
104: CURSOR l_target_line_csr (l_target_line_id NUMBER) IS
105: SELECT start_date,end_date,orig_system_id1
106: FROM okc_k_lines_b
107: WHERE id = l_target_line_id;
108:
109: l_target_line_rec l_target_line_csr%ROWTYPE;
110:
109: l_target_line_rec l_target_line_csr%ROWTYPE;
110:
111: CURSOR l_source_line_csr (l_source_line_id NUMBER) IS
112: SELECT start_date,end_date
113: FROM okc_k_lines_b
114: WHERE id = l_source_line_id;
115:
116:
117: l_source_line_rec l_source_line_csr%ROWTYPE;
187:
188: Procedure printTemptable IS
189: cursor gettemptablelines is
190: select id,cle_id,lse_id,orig_system_id1
191: from okc_k_lines_b_temp;
192: l_id NUMBER;
193: l_cle_id NUMBER;
194: l_lse_id NUMBER;
195: l_orig_system_id1 NUMBER;
869: l_cle_id NUMBER := OKC_API.G_MISS_NUM;
870:
871: CURSOR c_dnz_chr_id(p_id IN NUMBER) IS
872: SELECT dnz_chr_id
873: FROM okc_k_lines_b
874: WHERE id = p_id;
875:
876: CURSOR c_ctcv IS
877: SELECT id
1422: l_parent_end_date DATE;
1423:
1424: CURSOR c_cle IS
1425: SELECT start_date,end_date
1426: FROM okc_k_lines_b
1427: WHERE id = p_to_cle_id;
1428:
1429: CURSOR c_chr IS
1430: SELECT start_date,end_date
3795: select
3796: id
3797: ,line_number
3798: ,cle_id
3799: from okc_k_lines_b
3800: where dnz_chr_id = P_To_Chr_ID
3801: and (
3802: (cle_id is NOT NULL and lse_id in (7,8,9,10,11,13,25,35))
3803: OR
3810: b.id
3811: ,b.line_number
3812: ,b.cle_id
3813: from
3814: okc_k_lines_b_temp bt
3815: ,okc_k_lines_b b
3816: where b.dnz_chr_id = P_To_Chr_ID
3817: and (
3818: (b.cle_id is NOT NULL and b.lse_id in (7,8,9,10,11,13,25,35))
3811: ,b.line_number
3812: ,b.cle_id
3813: from
3814: okc_k_lines_b_temp bt
3815: ,okc_k_lines_b b
3816: where b.dnz_chr_id = P_To_Chr_ID
3817: and (
3818: (b.cle_id is NOT NULL and b.lse_id in (7,8,9,10,11,13,25,35))
3819: OR
3890: SELECT nvl(max(to_number(line_number)),0)
3891: INTO
3892: l_topline_no
3893: FROM
3894: OKC_K_LINES_B
3895: WHERE CLE_ID IS NULL
3896: AND DNZ_CHR_ID = P_To_Chr_ID
3897: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3898: End;
3893: FROM
3894: OKC_K_LINES_B
3895: WHERE CLE_ID IS NULL
3896: AND DNZ_CHR_ID = P_To_Chr_ID
3897: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3898: End;
3899: l_topline_no := l_topline_no + 1;
3900: END IF;
3901: END IF;
3901: END IF;
3902: l_topline_num_initialized := 'Y';
3903: END IF;
3904:
3905: update okc_k_lines_b
3906: set line_number = l_topline_no
3907: where id = l_id;
3908:
3909: l_topline_no := l_topline_no + 1;
3923: SELECT nvl(max(to_number(line_number)),0)
3924: INTO
3925: l_subline_no
3926: FROM
3927: OKC_K_LINES_B
3928: WHERE CLE_ID = l_cle_id
3929: AND LSE_ID in (7,8,9,10,11,13,25,35)
3930: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3931: End;
3926: FROM
3927: OKC_K_LINES_B
3928: WHERE CLE_ID = l_cle_id
3929: AND LSE_ID in (7,8,9,10,11,13,25,35)
3930: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3931: End;
3932: l_subline_no := l_subline_no + 1;
3933: END IF;
3934: END IF;
3935: l_subline_num_initialized := 'Y';
3936: END IF;
3937: END IF;
3938:
3939: update okc_k_lines_b
3940: set line_number = l_subline_no
3941: where id = l_id;
3942:
3943: l_subline_no := l_subline_no + 1;
4006: ,OkcTL.OKE_BOE_DESCRIPTION AS OkcTL_OKE_BOE_DESCRIPTION
4007: ,OkcTL.COGNOMEN AS OkcTL_COGNOMEN
4008: FROM
4009: OKC_K_LINES_TL okcTL
4010: ,OKC_K_LINES_B okcB
4011: where
4012: okcB.ORIG_SYSTEM_ID1 = okcTL.ID
4013: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
4014:
4027: ,OkcTL.OKE_BOE_DESCRIPTION AS OkcTL_OKE_BOE_DESCRIPTION
4028: ,OkcTL.COGNOMEN AS OkcTL_COGNOMEN
4029: FROM
4030: OKC_K_LINES_TL okcTL
4031: ,OKC_K_LINES_B_TEMP okcBT
4032: where
4033: okcBT.ORIG_SYSTEM_ID1 = okcTL.ID
4034: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
4035:
4637: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
4638: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
4639: FROM
4640: OKC_PRICE_ATT_VALUES okcPAV
4641: ,OKC_K_LINES_B okcB
4642: WHERE okcPAV.CLE_ID = okcB.ORIG_SYSTEM_ID1
4643: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
4644:
4645: --------------------------------------------------------------------------
5083: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
5084: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
5085: FROM
5086: OKC_PRICE_ATT_VALUES okcPAV
5087: ,OKC_K_LINES_B_TEMP okcBT
5088: WHERE okcPAV.CLE_ID = okcBT.ORIG_SYSTEM_ID1
5089: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
5090:
5091:
5536: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
5537: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
5538: FROM
5539: OKC_PRICE_ATT_VALUES okcPAV
5540: ,OKC_K_LINES_B_TEMP okcBT
5541: WHERE okcPAV.CLE_ID = okcBT.ORIG_SYSTEM_ID1
5542: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
5543:
5544: Begin
7148: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7149: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7150: FROM
7151: OKC_PRICE_ADJUSTMENTS okcPA
7152: ,OKC_K_LINES_B okcB
7153: WHERE okcPA.CLE_ID = okcB.ORIG_SYSTEM_ID1
7154: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
7155:
7156: -----------------------------------------------------------------------
7297: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7298: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7299: FROM
7300: OKC_PRICE_ADJUSTMENTS okcPA
7301: ,OKC_K_LINES_B_TEMP okcBT
7302: WHERE okcPA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
7303: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
7304:
7305: ----------------------------------------------------------------------------------------
7453: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7454: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7455: FROM
7456: OKC_PRICE_ADJUSTMENTS okcPA
7457: ,OKC_K_LINES_B_TEMP okcBT
7458: WHERE okcPA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
7459: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
7460:
7461:
8102: ,'COPY' AS okcPRB_ORIG_SYSTEM_REFERENCE1
8103: ,NULL AS okcPRB_ORIG_SYSTEM_SOURCE_CODE
8104: FROM
8105: OKC_K_PARTY_ROLES_B okcPRB
8106: ,OKC_K_LINES_B okcB
8107: WHERE
8108: okcPRB.CLE_ID = okcB.ORIG_SYSTEM_ID1
8109: AND okcPRB.DNZ_CHR_ID = P_From_Chr_ID
8110: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
8154: ,'COPY' AS okcPRB_ORIG_SYSTEM_REFERENCE1
8155: ,NULL AS okcPRB_ORIG_SYSTEM_SOURCE_CODE
8156: FROM
8157: OKC_K_PARTY_ROLES_B okcPRB
8158: ,OKC_K_LINES_B_TEMP okcBT
8159: WHERE
8160: okcPRB.CLE_ID = okcBT.ORIG_SYSTEM_ID1
8161: AND okcPRB.CHR_ID IS NULL
8162: AND okcPRB.DNZ_CHR_ID = P_From_Chr_ID
8565: ,okcPRTL.SECURITY_GROUP_ID AS okcPRTL_SECURITY_GROUP_ID
8566: FROM
8567: OKC_K_PARTY_ROLES_TL okcPRTL
8568: ,OKC_K_PARTY_ROLES_B okcPRB
8569: ,OKC_K_LINES_B_TEMP okcBT
8570: where okcPRTL.ID = okcPRB.ORIG_SYSTEM_ID1
8571: AND okcPRB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
8572: AND okcPRB.CLE_ID = okcBT.ID
8573: AND okcPRB.DNZ_CHR_ID = P_To_Chr_ID;
8770: , okcC.SALES_GROUP_ID AS okcC_SALES_GROUP_ID
8771: FROM
8772: OKC_CONTACTS okcC
8773: ,OKC_K_PARTY_ROLES_B okcPRB
8774: ,OKC_K_LINES_B_TEMP okcBT
8775: WHERE okcC.CPL_ID = okcPRB.ORIG_SYSTEM_ID1
8776: AND okcPRB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
8777: AND okcPRB.CLE_ID = okcBT.ID
8778: AND okcPRB.DNZ_CHR_ID = P_To_Chr_ID
9012: ,NULL AS OkcOL_MESSAGE_CODE
9013: ,NULL AS OkcOL_PARENT_OLE_ID
9014: ,'Y' AS OkcOL_ACTIVE_YN
9015: FROM
9016: OKC_K_LINES_B okcB
9017: WHERE
9018: okcB.DNZ_CHR_ID = P_To_Chr_ID;
9019:
9020: Begin
9109: where
9110: ola.SUBJECT_CHR_ID = P_To_Chr_ID
9111: AND ola.SUBJECT_CLE_ID in (SELECT okcb.ID
9112: from
9113: OKC_K_LINES_B okcb
9114: WHERE
9115: okcb.DNZ_CHR_ID = P_To_Chr_ID
9116: AND okcb.CLE_ID IS NULL);
9117: ------------------------------------------------------------------------------------------
9119: UPDATE okc_operation_lines ola
9120: set ola.PARENT_OLE_ID = (select olb.ID
9121: from okc_operation_lines olb
9122: where olb.SUBJECT_CLE_ID = (select b.CLE_ID from
9123: okc_k_lines_b b
9124: where b.ID = ola.SUBJECT_CLE_ID
9125: )
9126: )
9127: where
9127: where
9128: ola.SUBJECT_CHR_ID = P_To_Chr_ID
9129: and ola.SUBJECT_CLE_ID in (select okcB.ID
9130: from
9131: OKC_K_LINES_B okcB
9132: where okcB.ID = ola.SUBJECT_CLE_ID
9133: and okcB.CLE_ID IS NOT NULL);
9134: ------------------------------------------------------------------------------------------
9135:
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
9205: AND oksB.DNZ_CHR_ID = P_To_Chr_ID;
9332: , 1 AS OksRD_OBJECT_VERSION_NUMBER
9333: , oksRD.SECURITY_GROUP_ID AS OksRD_SECURITY_GROUP_ID
9334: FROM
9335: OKS_REV_DISTRIBUTIONS oksRD
9336: ,OKC_K_LINES_B okcB
9337: WHERE oksRD.CLE_ID = okcB.ORIG_SYSTEM_ID1
9338: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9339:
9340: CURSOR GetOksRDPartial IS
9349: , 1 AS OksRD_OBJECT_VERSION_NUMBER
9350: , oksRD.SECURITY_GROUP_ID AS OksRD_SECURITY_GROUP_ID
9351: FROM
9352: OKS_REV_DISTRIBUTIONS oksRD
9353: ,OKC_K_LINES_B_TEMP okcBT
9354: WHERE oksRD.CLE_ID = okcBT.ORIG_SYSTEM_ID1
9355: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
9356:
9357: Begin
9512: , oksQ.QUAL_ATTR_VALUE_TO_NUMBER AS oksQ_QUAL_ATTRVALUE_TO_NUM
9513: , oksQ.SECURITY_GROUP_ID AS oksQ_SECURITY_GROUP_ID
9514: FROM
9515: OKS_QUALIFIERS oksQ
9516: ,OKC_K_LINES_B okcB
9517: WHERE
9518: oksQ.LIST_LINE_ID = okcB.ORIG_SYSTEM_ID1
9519: AND okcB.CLE_ID IS NULL --Qualifiers are only for Top Lines
9520: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9564: , oksQ.QUAL_ATTR_VALUE_TO_NUMBER AS oksQ_QUAL_ATTRVALUE_TO_NUM
9565: , oksQ.SECURITY_GROUP_ID AS oksQ_SECURITY_GROUP_ID
9566: FROM
9567: OKS_QUALIFIERS oksQ
9568: ,OKC_K_LINES_B_TEMP okcBT
9569: WHERE
9570: oksQ.LIST_LINE_ID = okcBT.ORIG_SYSTEM_ID1
9571: AND okcBT.CLE_ID IS NULL --Qualifiers are only for Top Lines
9572: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
9832: ,NULL AS oksCTZ_ORIG_SYSTEM_SOURCE_CODE --*
9833: ,'COPY' AS oksCTZ_ORIG_SYSTEM_REFERENCE1 --*
9834: FROM
9835: OKS_COVERAGE_TIMEZONES oksCTZ
9836: ,OKC_K_LINES_B okcB
9837: WHERE
9838: oksCTZ.CLE_ID = okcB.ORIG_SYSTEM_ID1
9839: AND oksCTZ.DNZ_CHR_ID = P_From_Chr_ID
9840: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9857: ,NULL AS oksCTZ_ORIG_SYSTEM_SOURCE_CODE --*
9858: ,'COPY' AS oksCTZ_ORIG_SYSTEM_REFERENCE1 --*
9859: FROM
9860: OKS_COVERAGE_TIMEZONES oksCTZ
9861: ,OKC_K_LINES_B_TEMP okcBT
9862: WHERE
9863: oksCTZ.CLE_ID = okcBT.ORIG_SYSTEM_ID1
9864: AND oksCTZ.DNZ_CHR_ID = P_From_Chr_ID
9865: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
10058: ,oksCT.REQUEST_ID AS oksCT_REQUEST_ID
10059: FROM
10060: OKS_COVERAGE_TIMES oksCT
10061: ,OKS_COVERAGE_TIMEZONES oksCTZ
10062: ,OKC_K_LINES_B_TEMP okcBT
10063: WHERE
10064: OksCT.COV_TZE_LINE_ID = oksCTZ.ORIG_SYSTEM_ID1
10065: AND oksCTZ.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10066: AND oksCTZ.CLE_ID = okcBT.ID
10250: ,NULL AS oksPMA_ORIG_SYSTEM_SOURCE_CODE --*
10251: ,'COPY' AS oksPMA_ORIG_SYSTEM_REFERENCE1 --*
10252: FROM
10253: OKS_PM_ACTIVITIES oksPMA
10254: ,OKC_K_LINES_B okcB
10255: WHERE oksPMA.CLE_ID = okcB.ORIG_SYSTEM_ID1
10256: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
10257: AND oksPMA.DNZ_CHR_ID = P_From_Chr_ID;
10258:
10276: ,NULL AS oksPMA_ORIG_SYSTEM_SOURCE_CODE --*
10277: ,'COPY' AS oksPMA_ORIG_SYSTEM_REFERENCE1 --*
10278: FROM
10279: OKS_PM_ACTIVITIES oksPMA
10280: ,OKC_K_LINES_B_TEMP okcBT
10281: WHERE oksPMA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
10282: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
10283: AND oksPMA.DNZ_CHR_ID = P_From_Chr_ID
10284: AND okcBT.DATES_CHANGED_YN = 'N'; --Added to handle Date Effectivity Fix. Do Bulk copy of oks_pm_schedules only for those lines whose dates
10488: ,NULL AS oksPMSL_ORIG_SYS_SOURCE_CODE --*
10489: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10490: FROM
10491: OKS_PM_STREAM_LEVELS oksPMSL
10492: ,OKC_K_LINES_B okcB
10493: WHERE
10494: oksPMSL.CLE_ID = okcB.ORIG_SYSTEM_ID1
10495: AND oksPMSL.ACTIVITY_LINE_ID IS NULL
10496: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
10523: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10524: FROM
10525: OKS_PM_STREAM_LEVELS oksPMSL
10526: ,OKS_PM_ACTIVITIES oksPMA
10527: ,OKC_K_LINES_B_TEMP okcBT
10528: WHERE
10529: oksPMSL.ACTIVITY_LINE_ID = oksPMA.ORIG_SYSTEM_ID1
10530: AND oksPMA.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10531: AND oksPMA.CLE_ID = okcBT.ID
10559: ,NULL AS oksPMSL_ORIG_SYS_SOURCE_CODE --*
10560: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10561: FROM
10562: OKS_PM_STREAM_LEVELS oksPMSL
10563: ,OKC_K_LINES_B_TEMP okcBT
10564: WHERE
10565: oksPMSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
10566: AND oksPMSL.ACTIVITY_LINE_ID IS NULL
10567: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
10792: ,oksPMSCH.REQUEST_ID AS oksPMSCH_REQUEST_ID
10793: FROM
10794: OKS_PM_SCHEDULES oksPMSCH
10795: ,OKS_PM_STREAM_LEVELS oksPMSL
10796: ,OKC_K_LINES_B_TEMP okcBT
10797: WHERE
10798: oksPMSCH.STREAM_LINE_ID = oksPMSL.ORIG_SYSTEM_ID1
10799: AND oksPMSL.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10800: AND oksPMSL.CLE_ID = okcBT.ID
10956: CURSOR GetLinesWithDatesChanged IS
10957: SELECT
10958: a.ID as LINE_ID
10959: from
10960: OKC_K_LINES_B_TEMP a
10961: where a.DNZ_CHR_ID = P_To_Chr_ID
10962: and a.lse_id in (1,19)
10963: and a.DATES_CHANGED_YN = 'Y';
10964:
11071: ,NULL AS oksATT_ORIG_SYSTEM_SOURCE_CODE--*
11072: ,'COPY' AS oksATT_ORIG_SYSTEM_REFERENCE1--*
11073: FROM
11074: OKS_ACTION_TIME_TYPES oksATT
11075: ,OKC_K_LINES_B okcB
11076: WHERE oksATT.CLE_ID = okcB.ORIG_SYSTEM_ID1
11077: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11078: AND oksATT.DNZ_CHR_ID = P_From_Chr_ID;
11079:
11094: ,NULL AS oksATT_ORIG_SYSTEM_SOURCE_CODE--*
11095: ,'COPY' AS oksATT_ORIG_SYSTEM_REFERENCE1--*
11096: FROM
11097: OKS_ACTION_TIME_TYPES oksATT
11098: ,OKC_K_LINES_B_TEMP okcBT
11099: WHERE oksATT.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11100: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11101: AND oksATT.DNZ_CHR_ID = P_From_Chr_ID;
11102:
11285: ,1 AS oksAT_OBJECT_VERSION_NUMBER
11286: FROM
11287: OKS_ACTION_TIMES oksAT
11288: ,OKS_ACTION_TIME_TYPES oksATT
11289: ,OKC_K_LINES_B_TEMP okcBT
11290: WHERE oksAT.COV_ACTION_TYPE_ID = oksATT.ORIG_SYSTEM_ID1
11291: AND oksATT.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
11292: AND oksATT.CLE_ID = okcBT.ID
11293: AND oksATT.DNZ_CHR_ID = P_To_Chr_ID
11497: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11498: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11499: FROM
11500: OKS_K_SALES_CREDITS oksSC
11501: ,OKC_K_LINES_B okcB
11502: WHERE oksSC.CLE_ID = okcB.ORIG_SYSTEM_ID1
11503: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11504: AND oksSC.CHR_ID = P_From_Chr_ID;
11505:
11538: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11539: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11540: FROM
11541: OKS_K_SALES_CREDITS oksSC
11542: ,OKC_K_LINES_B_TEMP okcBT
11543: WHERE
11544: oksSC.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11545: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11546: AND oksSC.CHR_ID = P_From_Chr_ID;
11579: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11580: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11581: FROM
11582: OKS_K_SALES_CREDITS oksSC
11583: ,OKC_K_LINES_B_TEMP okcBT
11584: WHERE
11585: oksSC.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11586: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11587: AND oksSC.CHR_ID = P_From_Chr_ID;
11793: ,oksBSCH.SECURITY_GROUP_ID AS oksBSCH_SECURITY_GROUP_ID
11794: ,1 AS oksBSCH_OBJECT_VERSION_NUMBER
11795: FROM
11796: OKS_BILLRATE_SCHEDULES oksBSCH
11797: ,OKC_K_LINES_B okcB
11798: WHERE oksBSCH.CLE_ID = okcB.ORIG_SYSTEM_ID1
11799: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11800: AND oksBSCH.DNZ_CHR_ID = P_From_Chr_ID;
11801:
11832: ,oksBSCH.SECURITY_GROUP_ID AS oksBSCH_SECURITY_GROUP_ID
11833: ,1 AS oksBSCH_OBJECT_VERSION_NUMBER
11834: FROM
11835: OKS_BILLRATE_SCHEDULES oksBSCH
11836: ,OKC_K_LINES_B_TEMP okcBT
11837: WHERE oksBSCH.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11838: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11839: AND oksBSCH.DNZ_CHR_ID = P_From_Chr_ID;
11840:
12121: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12122: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12123: FROM
12124: OKS_STREAM_LEVELS_B oksSL
12125: ,OKC_K_LINES_B okcBTarget
12126: ,OKC_K_LINES_B okcBSource
12127: WHERE
12128: oksSL.CLE_ID = okcBTarget.ORIG_SYSTEM_ID1
12129: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12122: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12123: FROM
12124: OKS_STREAM_LEVELS_B oksSL
12125: ,OKC_K_LINES_B okcBTarget
12126: ,OKC_K_LINES_B okcBSource
12127: WHERE
12128: oksSL.CLE_ID = okcBTarget.ORIG_SYSTEM_ID1
12129: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12130: AND okcBTarget.DNZ_CHR_ID = P_To_Chr_ID
12134: --AND okcB.DATE_TERMINATED IS NULL;
12135: AND (
12136: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12137: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12138: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Terminated
12139: where b.CLE_ID= okcBSource.ID
12140: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12141: and b.DATE_TERMINATED IS NOT NULL
12142: )
12143: )
12144: OR
12145: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12146: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12147: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Terminated
12148: where b.CLE_ID = okcBSource.CLE_ID
12149: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12150: and b.DATE_TERMINATED IS NOT NULL
12151: )
12154: --npalepu added on 31-may-2006 for bug # 5178264
12155: And (
12156: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12157: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12158: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Cancelled
12159: where b.CLE_ID= okcBSource.ID
12160: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12161: and b.DATE_CANCELLED IS NOT NULL
12162: )
12163: )
12164: OR
12165: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12166: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12167: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Cancelled
12168: where b.CLE_ID = okcBSource.CLE_ID
12169: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12170: and b.DATE_CANCELLED IS NOT NULL
12171: )
12240: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12241: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12242: FROM
12243: OKS_STREAM_LEVELS_B oksSL
12244: ,OKC_K_LINES_B_TEMP okcBT
12245: WHERE
12246: oksSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
12247: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
12248: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12252: AND (
12253: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12254: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12255: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12256: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12257: where b.CLE_ID = okcBT.ID
12258: and b.LINE_EXISTS_YN = 'N'
12259: and b.DATE_TERMINATED is not null)
12260: )
12262: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12263: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12264: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12265: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12266: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that TERMINATED
12267: where --e) Its Top Line being copied doesnt have its Dates Changed
12268: (a.CLE_ID = okcBT.CLE_ID
12269: and a.LINE_EXISTS_YN = 'N'
12270: and a.DATE_TERMINATED IS NOT NULL)
12284: AND (
12285: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12286: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12287: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
12288: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12289: where b.CLE_ID = okcBT.ID
12290: and b.LINE_EXISTS_YN = 'N'
12291: and b.DATE_CANCELLED is not null)
12292: )
12294: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12295: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12296: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12297: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12298: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that Cancelled
12299: where --e) Its Top Line being copied doesnt have its Dates Changed
12300: (a.CLE_ID = okcBT.CLE_ID
12301: and a.LINE_EXISTS_YN = 'N'
12302: and a.DATE_CANCELLED IS NOT NULL)
12386: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12387: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12388: FROM
12389: OKS_STREAM_LEVELS_B oksSL
12390: ,OKC_K_LINES_B_TEMP okcBT
12391: WHERE
12392: oksSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
12393: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12394: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
12396: AND (
12397: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12398: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12399: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12400: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12401: where b.CLE_ID = okcBT.ID
12402: and b.LINE_EXISTS_YN = 'N'
12403: and b.DATE_TERMINATED is not null)
12404: )
12406: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12407: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12408: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12409: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12410: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that TERMINATED
12411: where --e) Its Top Line being copied doesnt have its Dates Changed
12412: (a.CLE_ID = okcBT.CLE_ID
12413: and a.LINE_EXISTS_YN = 'N'
12414: and a.DATE_TERMINATED IS NOT NULL)
12428: AND (
12429: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12430: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12431: AND okcBT.DATE_CANCELLED IS NULL --b) Is not cancelled
12432: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12433: where b.CLE_ID = okcBT.ID
12434: and b.LINE_EXISTS_YN = 'N'
12435: and b.DATE_CANCELLED is not null)
12436: )
12438: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12439: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12440: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12441: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12442: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that Cancelled
12443: where --e) Its Top Line being copied doesnt have its Dates Changed
12444: (a.CLE_ID = okcBT.CLE_ID
12445: and a.LINE_EXISTS_YN = 'N'
12446: and a.DATE_CANCELLED IS NOT NULL)
12761: ,oksSL.CLE_ID AS oksLE_NewCleID --*
12762: ,oksSL.DNZ_CHR_ID AS oksLE_NewDnzChrID --*
12763: ,decode(oksSL.CLE_ID,NULL,NULL,(select
12764: decode(a.CLE_ID,NULL,a.ID,a.CLE_ID)
12765: from okc_k_lines_b a
12766: where
12767: a.ID = oksSL.CLE_ID
12768: and a.DNZ_CHR_ID = P_To_Chr_ID )) AS oksLE_NewParentCleID --*
12769: ,oksLE.DATE_END AS oksLE_DATE_END
12829: ,oksLE.DATE_END AS oksLE_DATE_END
12830: FROM
12831: OKS_LEVEL_ELEMENTS oksLE
12832: ,OKS_STREAM_LEVELS_B oksSL
12833: ,OKC_K_LINES_B okcBTarget
12834: ,OKC_K_LINES_B okcBSource
12835: WHERE
12836: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12837: AND oksSL.CHR_ID IS NULL
12830: FROM
12831: OKS_LEVEL_ELEMENTS oksLE
12832: ,OKS_STREAM_LEVELS_B oksSL
12833: ,OKC_K_LINES_B okcBTarget
12834: ,OKC_K_LINES_B okcBSource
12835: WHERE
12836: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12837: AND oksSL.CHR_ID IS NULL
12838: AND oksSL.CLE_ID IS NOT NULL
12842: --AND okcB.DATE_TERMINATED IS NULL;
12843: AND (
12844: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12845: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12846: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Terminated
12847: where b.CLE_ID= okcBSource.ID
12848: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12849: and b.DATE_TERMINATED IS NOT NULL
12850: )
12851: )
12852: OR
12853: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12854: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12855: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Terminated
12856: where b.CLE_ID = okcBSource.CLE_ID
12857: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12858: and b.DATE_TERMINATED IS NOT NULL
12859: )
12862: --npalepu added on 31-may-2006 for bug # 5178264
12863: AND (
12864: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12865: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12866: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Cancelled
12867: where b.CLE_ID= okcBSource.ID
12868: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12869: and b.DATE_CANCELLED IS NOT NULL
12870: )
12871: )
12872: OR
12873: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12874: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12875: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Cancelled
12876: where b.CLE_ID = okcBSource.CLE_ID
12877: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12878: and b.DATE_CANCELLED IS NOT NULL
12879: )
12939: ,oksLE.DATE_END AS oksLE_DATE_END
12940: FROM
12941: OKS_LEVEL_ELEMENTS oksLE
12942: ,OKS_STREAM_LEVELS_B oksSL
12943: ,OKC_K_LINES_B_TEMP okcBT
12944: WHERE
12945: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12946: AND oksSL.CHR_ID IS NULL
12947: AND oksSL.CLE_ID IS NOT NULL
12953: AND (
12954: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12955: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12956: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12957: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12958: where b.CLE_ID = okcBT.ID
12959: and b.LINE_EXISTS_YN = 'N'
12960: and b.DATE_TERMINATED is not null)
12961: )
12963: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12964: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12965: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
12966: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12967: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are TERMINATED
12968: where --e) Its Top Line being copied doesnt have its Dates Changed
12969: (a.CLE_ID = okcBT.CLE_ID
12970: and a.LINE_EXISTS_YN = 'N'
12971: and a.DATE_TERMINATED IS NOT NULL)
12985: AND (
12986: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12987: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12988: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
12989: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12990: where b.CLE_ID = okcBT.ID
12991: and b.LINE_EXISTS_YN = 'N'
12992: and b.DATE_CANCELLED is not null)
12993: )
12995: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12996: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12997: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
12998: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12999: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are Cancelled
13000: where --e) Its Top Line being copied doesnt have its Dates Changed
13001: (a.CLE_ID = okcBT.CLE_ID
13002: and a.LINE_EXISTS_YN = 'N'
13003: and a.DATE_CANCELLED IS NOT NULL)
13070: ,oksLE.DATE_END AS oksLE_DATE_END
13071: FROM
13072: OKS_LEVEL_ELEMENTS oksLE
13073: ,OKS_STREAM_LEVELS_B oksSL
13074: ,OKC_K_LINES_B_TEMP okcBT
13075: WHERE
13076: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
13077: AND oksSL.CHR_ID IS NULL
13078: AND oksSL.CLE_ID IS NOT NULL
13084: AND (
13085: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
13086: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
13087: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
13088: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
13089: where b.CLE_ID = okcBT.ID
13090: and b.LINE_EXISTS_YN = 'N'
13091: and b.DATE_TERMINATED is not null)
13092: )
13094: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
13095: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
13096: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
13097: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
13098: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are TERMINATED
13099: where --e) Its Top Line being copied doesnt have its Dates Changed
13100: (a.CLE_ID = okcBT.CLE_ID
13101: and a.LINE_EXISTS_YN = 'N'
13102: and a.DATE_TERMINATED IS NOT NULL)
13116: AND (
13117: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
13118: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
13119: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
13120: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
13121: where b.CLE_ID = okcBT.ID
13122: and b.LINE_EXISTS_YN = 'N'
13123: and b.DATE_CANCELLED is not null)
13124: )
13126: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
13127: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
13128: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
13129: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
13130: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are Cancelled
13131: where --e) Its Top Line being copied doesnt have its Dates Changed
13132: (a.CLE_ID = okcBT.CLE_ID
13133: and a.LINE_EXISTS_YN = 'N'
13134: and a.DATE_CANCELLED IS NOT NULL)
13425: CURSOR GetTargetLines_F IS
13426: Select
13427: a.ID
13428: FROM
13429: OKC_K_LINES_B a
13430: where a.DNZ_CHR_ID = P_To_Chr_ID
13431: and a.CLE_ID IS NULL;
13432:
13433: CURSOR GetTargetLines_P IS
13433: CURSOR GetTargetLines_P IS
13434: Select
13435: okcB.ID
13436: FROM
13437: OKC_K_LINES_B okcB
13438: ,OKC_K_LINES_B_TEMP okcBT
13439: where okcB.ID = okcBT.ID
13440: and okcB.DNZ_CHR_ID = P_To_Chr_ID
13441: and okcB.CLE_ID IS NULL;
13434: Select
13435: okcB.ID
13436: FROM
13437: OKC_K_LINES_B okcB
13438: ,OKC_K_LINES_B_TEMP okcBT
13439: where okcB.ID = okcBT.ID
13440: and okcB.DNZ_CHR_ID = P_To_Chr_ID
13441: and okcB.CLE_ID IS NULL;
13442:
13470: END IF;
13471:
13472: Begin
13473: FORALL i IN LineIDTab.FIRST..LineIDTab.LAST
13474: UPDATE OKC_K_LINES_B a
13475: SET a.INV_RULE_ID = nvl(a.INV_RULE_ID,G_TrgHdrInvRuleID)
13476: WHERE a.ID = LineIDTab(i);
13477: End;
13478: Begin
13579: okcB.ID as LINE_ID
13580: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
13581: ,okcB.CLE_ID CLE_ID
13582: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
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)
13592: ,okcB.END_DATE as END_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
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
13602: and okcB_Source.DNZ_CHR_ID = P_From_Chr_ID
13604: --npalepu modified on 31-may-2006 for bug # 5178264
13605: /* and (okcB_Source.DATE_TERMINATED IS NOT NULL
13606: OR
13607: (okcB_Source.DATE_TERMINATED IS NULL
13608: AND EXISTS (select b.ID from okc_k_lines_b b where b.CLE_ID= okcB_Source.ID and b.DATE_TERMINATED IS NOT NULL)
13609: )
13610: ); */
13611: and ((okcB_Source.DATE_TERMINATED IS NOT NULL
13612: OR
13610: ); */
13611: and ((okcB_Source.DATE_TERMINATED IS NOT NULL
13612: OR
13613: (okcB_Source.DATE_TERMINATED IS NULL
13614: AND EXISTS (select b.ID from okc_k_lines_b b where b.CLE_ID= okcB_Source.ID and b.DATE_TERMINATED IS NOT NULL)
13615: )
13616: ) OR
13617: (okcB_Source.DATE_CANCELLED IS NOT NULL
13618: OR
13616: ) OR
13617: (okcB_Source.DATE_CANCELLED IS NOT NULL
13618: OR
13619: (okcB_Source.DATE_CANCELLED IS NULL
13620: AND EXISTS (select b.ID from okc_k_lines_b b where b.CLE_ID= okcB_Source.ID and b.DATE_CANCELLED IS NOT NULL)
13621: )
13622: ));
13623: --end npalepu
13624:
13628: okcB.ID as LINE_ID
13629: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
13630: ,okcB.CLE_ID CLE_ID
13631: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
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)
13641: ,okcB.END_DATE as END_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
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
13651: --npalepu modified on 31-may-2006 for bug # 5178264
13652: /* and (
13653: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13654: OR okcBT.DATE_TERMINATED IS NOT NULL
13655: OR (
13656: okcBT.DATE_TERMINATED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13657: where b.CLE_ID= okcBT.ID
13658: and b.DATE_TERMINATED IS NOT NULL
13659: and b.LINE_EXISTS_YN = 'N')
13660: )
13665: and ((
13666: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13667: OR okcBT.DATE_TERMINATED IS NOT NULL
13668: OR (
13669: okcBT.DATE_TERMINATED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13670: where b.CLE_ID= okcBT.ID
13671: and b.DATE_TERMINATED IS NOT NULL
13672: and b.LINE_EXISTS_YN = 'N')
13673: )
13679: (
13680: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13681: OR okcBT.DATE_CANCELLED IS NOT NULL
13682: OR (
13683: okcBT.DATE_CANCELLED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13684: where b.CLE_ID= okcBT.ID
13685: and b.DATE_CANCELLED IS NOT NULL
13686: and b.LINE_EXISTS_YN = 'N')
13687: )
14268: ,okcB.LSE_ID as LSE_ID
14269: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
14270: ,okcB.CLE_ID CLE_ID
14271: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
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)
14289: -- hkamdar 04-Sep-2006 Added for bug# 5458842.
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
14305: ,okcB.LSE_ID as LSE_ID
14306: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
14307: ,okcB.CLE_ID CLE_ID
14308: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
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)
14326: -- hkamdar 04-Sep-2006 Added for bug# 5458842.
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
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
14336: and (okcBT.CLE_ID IS NULL --Get all Top lines that are being copied
15411: ,a.end_date
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)
15431: CURSOR getBPLineandChildren IS
15432: select
15433: a.ID
15434: from
15435: OKC_K_LINES_B_TEMP a
15436: where a.dnz_chr_id = P_To_Chr_ID
15437: start with a.id = l_BP_ID
15438: connect by prior a.id = a.cle_id;
15439:
15498: EXIT WHEN getBPLineandChildren%NOTFOUND;
15499:
15500:
15501:
15502: UPDATE OKC_K_LINES_B_TEMP SET START_DATE = l_Resultant_Start_Date
15503: WHERE DNZ_CHR_ID = P_To_Chr_ID
15504: AND ID = l_BP_Child_ID;
15505:
15506: END LOOP;
15626:
15627: -- 03-Nov-2006 hkamdar. Bug# 5623498.
15628: CURSOR get_toplvl_uom(p_line_id IN NUMBER) IS
15629: SELECT OEL.PRICING_QUANTITY_UOM
15630: FROM OKC_K_LINES_B OKCLINE,
15631: OKC_K_REL_OBJS OKCREL,
15632: OE_ORDER_LINES_ALL OEL
15633: WHERE OKCLINE.LSE_ID IN (7,9,25)
15634: AND OKCLINE.id = p_line_id
15652: -- End Bug# 5531198 hkamdar 21-Sep-2006
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
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
15662: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
15663: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
15664: okcB.ID AS OkcB_OldOKCLineID
15665: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
15666: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
15667: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
15769: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
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
15903: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
15916: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy-----------------
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
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
15926: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
15927: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
15928: okcB.ID AS OkcB_OldOKCLineID
15929: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
15930: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
15931: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16033: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
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
16167: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
16181: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_CANCEL-----------------
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
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
16191: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16192: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16193: okcB.ID AS OkcB_OldOKCLineID
16194: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16195: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16196: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16298: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
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
16432: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
16446: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_termn-----------------
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
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
16456: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16457: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16458: okcB.ID AS OkcB_OldOKCLineID
16459: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16460: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16461: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16563: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
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
16697: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
16712: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_EXCLUDE-----------------
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:
16722: CURSOR GetOKCOKSLinesB_Renew(P_From_Chr_ID IN NUMBER
16723: ,P_To_Chr_ID IN NUMBER) IS
16724: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16725: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16726: okcB.ID AS OkcB_OldOKCLineID
16727: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16728: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16729: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16816: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
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
16950: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
16971: OR
16972: (okcB.CLE_ID IS NOT NULL
16973: AND NOT EXISTS
16974: (SELECT 'X'
16975: FROM okc_k_lines_b lb2
16976: WHERE NVL(lb2.line_renewal_type_code,'X') = 'DNR'
16977: OR lb2.DATE_TERMINATED IS NOT NULL
16978: OR lb2.DATE_CANCELLED IS NOT NULL
16979: OR lb2.DATE_RENEWED IS NOT NULL
17022: /*cgopinee bugfix for bug8826746*/
17023: cursor terminatedLSLines IS
17024: select
17025: Count(id)
17026: from okc_k_lines_b
17027: where dnz_chr_id = P_From_Chr_ID
17028: and (
17029: (cle_id is NOT NULL and lse_id in (7,8,9,10,11,13,25,35))
17030: OR
17243: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
17458: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
17672: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
17885: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
18106: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
18236: END IF;
18237:
18238: END IF;
18239:
18240: --Bulk Insert into OKC_K_LINES_B
18241: Begin
18242:
18243: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18244: INSERT INTO OKC_K_LINES_B(
18240: --Bulk Insert into OKC_K_LINES_B
18241: Begin
18242:
18243: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18244: INSERT INTO OKC_K_LINES_B(
18245: ID
18246: , LINE_NUMBER
18247: , CHR_ID
18248: , CLE_ID
18433:
18434: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
18435: FND_LOG.string(FND_LOG.level_statement
18436: ,l_module_name
18437: ,'AFTER INSERT into OKC_K_LINES_B '
18438: );
18439: END IF;
18440:
18441: EXCEPTION
18443: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
18444: IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
18445: FND_LOG.string(FND_LOG.level_unexpected
18446: ,l_module_name
18447: ,'EXCEPTION during insert into OKC_K_LINES_B '||SQLERRM
18448: );
18449: END IF;
18450:
18451: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
18834: END IF;
18835: END IF;
18836:
18837: /*cgopinee bugfix for bug7580778*/
18838: UPDATE OKC_K_LINES_B
18839: SET
18840: PRICE_NEGOTIATED = l_converted_price_negotiated
18841: ,PRICE_UNIT = l_converted_price_unit
18842: ,CURRENCY_CODE= l_euro_currency
18954: );
18955: END IF;
18956:
18957: --skuchima 12357658
18958: UPDATE OKC_K_LINES_B a
18959: SET a.CLE_ID = (select b.ID
18960: FROM OKC_K_LINES_B b
18961: WHERE
18962: b.ORIG_SYSTEM_ID1 = a.CLE_ID
18956:
18957: --skuchima 12357658
18958: UPDATE OKC_K_LINES_B a
18959: SET a.CLE_ID = (select b.ID
18960: FROM OKC_K_LINES_B b
18961: WHERE
18962: b.ORIG_SYSTEM_ID1 = a.CLE_ID
18963: AND b.DNZ_CHR_ID = P_To_Chr_ID)
18964: WHERE
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:
19275: CLOSE GetOKCOKSLinesB_Renew;
19276: END IF;
19277:
19278: ----------------------------------------------------------------------------
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
19276: END IF;
19277:
19278: ----------------------------------------------------------------------------
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'
19277:
19278: ----------------------------------------------------------------------------
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
19285: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19286:
19287: Begin
19288:
19289: /* UPDATE OKC_K_LINES_B a
19290: SET a.CLE_ID = (select b.ID
19291: FROM OKC_K_LINES_B b
19292: WHERE
19293: b.ORIG_SYSTEM_ID1 = a.CLE_ID
19287: Begin
19288:
19289: /* UPDATE OKC_K_LINES_B a
19290: SET a.CLE_ID = (select b.ID
19291: FROM OKC_K_LINES_B b
19292: WHERE
19293: b.ORIG_SYSTEM_ID1 = a.CLE_ID
19294: AND b.DNZ_CHR_ID = P_To_Chr_ID)
19295: WHERE
19295: WHERE
19296: a.DNZ_CHR_ID = P_To_Chr_ID
19297: AND a.CLE_ID IS NOT NULL; */ --skuchima 12357658
19298:
19299: UPDATE OKC_K_LINES_B a
19300: SET a.PRICE_NEGOTIATED = nvl((select sum(b.PRICE_NEGOTIATED)
19301: from OKC_K_LINES_B b
19302: where b.CLE_ID = a.ID
19303: AND b.DNZ_CHR_ID = P_To_Chr_ID
19297: AND a.CLE_ID IS NOT NULL; */ --skuchima 12357658
19298:
19299: UPDATE OKC_K_LINES_B a
19300: SET a.PRICE_NEGOTIATED = nvl((select sum(b.PRICE_NEGOTIATED)
19301: from OKC_K_LINES_B b
19302: where b.CLE_ID = a.ID
19303: AND b.DNZ_CHR_ID = P_To_Chr_ID
19304: AND b.DATE_CANCELLED IS NULL),0) --Cancelled sublines shouldn't be included in sum. Nvl added for 12636704
19305: WHERE
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
19315: AND b.DNZ_CHR_ID = P_To_Chr_ID
19316: AND b.LSE_ID in (2,20))
19314: b.CLE_ID = a.CLE_ID
19315: AND b.DNZ_CHR_ID = P_To_Chr_ID
19316: AND b.LSE_ID in (2,20))
19317: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
19318: AND a.CLE_ID in (select c.ID from OKC_K_LINES_B c
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
19328: WHERE
19329: c.ID = b.CLE_ID
19330: AND c.cle_id = a.cle_id
19331: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldnt be included in sum
19331: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldnt be included in sum
19332: WHERE a.dnz_chr_id = P_To_Chr_ID
19333: and a.CLE_ID = (select b.id
19334: FROM
19335: OKC_K_LINES_B b
19336: WHERE b.cle_id is NULL
19337: AND b.id = a.cle_id
19338: AND b.LSE_ID <> 46); --Subscription Lines do not have sublines. So they shouldnt be updated.
19339:
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.
19349:
19350: End;
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)
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)
19359: FROM OKC_K_LINES_B b
19360: where b.DNZ_CHR_ID = a.ID
19361: and b.CLE_ID IS NULL
19362: AND b.DATE_CANCELLED IS NULL) --Cancelled toplines shouldn't be included in sum.
19363: WHERE a.ID = P_To_Chr_ID;
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
19373: and c.DATE_CANCELLED IS NULL) --Cancelled toplines shouldn't be included in sum
19375:
19376: End;
19377: --------------------------------------------------------------------------------
19378:
19379: -------------Generate new line numbers in OKC_K_LINES_B for newly created lines-----
19380: Begin
19381: /*cgopinee bugfix for bug8826746*/
19382: /* Check for existance of terminated lines-sublines in the contract which is being renewed.*/
19383: IF (P_Renew_Ref_YN ='Y') THEN
20178: lx_rail_rec OKS_TAX_UTIL_PVT.ra_rec_type;
20179: -- 03-Nov-2006 hkamdar. Bug# 5623498.
20180: CURSOR get_toplvl_uom(p_line_id IN NUMBER) IS
20181: SELECT OEL.PRICING_QUANTITY_UOM
20182: FROM OKC_K_LINES_B OKCLINE,
20183: OKC_K_REL_OBJS OKCREL,
20184: OE_ORDER_LINES_ALL OEL
20185: WHERE OKCLINE.LSE_ID IN (7,9,25)
20186: AND OKCLINE.id = p_line_id
20222:
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:
20230:
20231: CURSOR GetOKCOKSLinesB_Temp(P_From_Chr_ID IN NUMBER
20232: ,P_To_Chr_ID IN NUMBER) IS
20233: SELECT
20234: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20235: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20236: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20237: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20238: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
20376: ,okcBT.CANCELLED_AMOUNT)
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
20527: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
20539:
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:
20547:
20548: CURSOR GetOKCOKSLinesB_Temp_CANCEL(P_From_Chr_ID IN NUMBER
20549: ,P_To_Chr_ID IN NUMBER) IS
20550: SELECT
20551: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20552: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20553: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20554: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20555: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
20693: ,okcBT.CANCELLED_AMOUNT)
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
20844: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
20857:
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:
20865:
20866: CURSOR GetOKCOKSLinesB_Temp_TERMN(P_From_Chr_ID IN NUMBER
20867: ,P_To_Chr_ID IN NUMBER) IS
20868: SELECT
20869: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20870: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20871: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20872: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20873: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
21011: ,okcBT.CANCELLED_AMOUNT)
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
21162: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
21175:
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:
21183:
21184: CURSOR GetOKCOKSLinesB_Temp_EXCLUDE(P_From_Chr_ID IN NUMBER
21185: ,P_To_Chr_ID IN NUMBER) IS
21186: SELECT
21187: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
21188: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
21189: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
21190: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
21191: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
21329: ,okcBT.CANCELLED_AMOUNT)
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
21480: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
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(+)
21596:
21597: End If; -- End of check for P_Lines_Tbl.COUNT to be > 0
21598: --------------------------------------------------------------------------------------
21599:
21600: ------------------------Start of BULK INSERT INTO OKC_K_LINES_B_TEMP-----------------
21601: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21602: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21603:
21604: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21598: --------------------------------------------------------------------------------------
21599:
21600: ------------------------Start of BULK INSERT INTO OKC_K_LINES_B_TEMP-----------------
21601: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21602: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21603:
21604: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21605: ------OKC_K_LINES_B_TEMP global temporary table----------------------------------------
21606: -- a) Bulk insert for all lines in ApiLinesRecTab where TO_LINE is NULL and LINE_EXP_YN = 'Y'
21601: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21602: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21603:
21604: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21605: ------OKC_K_LINES_B_TEMP global temporary table----------------------------------------
21606: -- a) Bulk insert for all lines in ApiLinesRecTab where TO_LINE is NULL and LINE_EXP_YN = 'Y'
21607: -- (Indicates that line being copied is a top line and that this line and all its sublines
21608: -- need to be queried explicitly from the database and copied)
21609: --
21610:
21611: Begin
21612:
21613: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
21614: INSERT INTO OKC_K_LINES_B_TEMP(
21615: ID
21616: ,LINE_NUMBER
21617: ,CHR_ID
21618: ,CLE_ID
21829: END
21830: ) AS okcB_DATES_CHANGED_YN
21831: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
21832: FROM
21833: OKC_K_LINES_B okcB
21834: WHERE ApiLinesRecTab.ToLineTab(i) IS NULL
21835: AND ApiLinesRecTab.LineExpYNTab(i) = 'Y'
21836: AND okcB.DNZ_CHR_ID = P_From_Chr_ID
21837: CONNECT BY PRIOR okcB.ID = okcB.CLE_ID
21841: WHEN OTHERS THEN
21842: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
21843: FND_LOG.string(FND_LOG.level_unexpected
21844: ,l_module_name
21845: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : a) :'||SQLERRM);
21846: End If;
21847:
21848: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
21849: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
21858: --
21859:
21860: Begin
21861: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
21862: INSERT INTO OKC_K_LINES_B_TEMP(
21863: ID
21864: ,LINE_NUMBER
21865: ,CHR_ID
21866: ,CLE_ID
22077: END
22078: ) AS okcB_DATES_CHANGED_YN
22079: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
22080: FROM
22081: OKC_K_LINES_B okcB
22082: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
22083: AND ApiLinesRecTab.ToLineTab(i) IS NULL
22084: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
22085: AND okcB.DNZ_CHR_ID = P_From_Chr_ID
22203: END
22204: ) AS okcCL_DATES_CHANGED_YN
22205: ,'N' AS okcCL_GEN_SUBLINE_BSCH_YN
22206: FROM
22207: OKC_K_LINES_B okcCL
22208: WHERE
22209: okcCL.DNZ_CHR_ID = P_From_Chr_ID
22210: AND ApiLinesRecTab.ToLineTab(i) IS NULL
22211: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
22220: WHEN OTHERS THEN
22221: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22222: FND_LOG.string(FND_LOG.level_unexpected
22223: ,l_module_name
22224: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : b) :'||SQLERRM);
22225: End If;
22226:
22227:
22228: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
22240:
22241: Begin
22242:
22243: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
22244: INSERT INTO OKC_K_LINES_B_TEMP(
22245: ID
22246: ,LINE_NUMBER
22247: ,CHR_ID
22248: ,CLE_ID
22459: END
22460: ) AS okcB_DATES_CHANGED_YN
22461: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
22462: FROM
22463: OKC_K_LINES_B okcB
22464: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
22465: AND ApiLinesRecTab.ToLineTab(i) IS NOT NULL
22466: AND ApiLinesRecTab.LineExistsYNTab(i) = 'N'
22467: AND okcB.DNZ_CHR_ID = P_From_Chr_ID;
22471:
22472: /* IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
22473: FND_LOG.string(FND_LOG.level_unexpected
22474: ,l_module_name
22475: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : c) :'||SQLERRM);
22476: END IF; */
22477:
22478:
22479:
22490:
22491: Begin
22492:
22493: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
22494: INSERT INTO OKC_K_LINES_B_TEMP(
22495: ID
22496: ,LINE_NUMBER
22497: ,CHR_ID
22498: ,CLE_ID
22621: ,okcB.DATE_TERMINATED AS okcB_DATE_TERMINATED
22622: ,(CASE
22623: WHEN (
22624: (nvl(okcB.START_DATE,sysdate) BETWEEN
22625: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22626: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22627: )
22628: AND
22629: (nvl(okcB.END_DATE,sysdate) BETWEEN
22622: ,(CASE
22623: WHEN (
22624: (nvl(okcB.START_DATE,sysdate) BETWEEN
22625: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22626: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22627: )
22628: AND
22629: (nvl(okcB.END_DATE,sysdate) BETWEEN
22630: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22626: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22627: )
22628: AND
22629: (nvl(okcB.END_DATE,sysdate) BETWEEN
22630: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22631: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22632: )
22633: )
22634: THEN nvl(okcB.START_DATE,sysdate)
22627: )
22628: AND
22629: (nvl(okcB.END_DATE,sysdate) BETWEEN
22630: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22631: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22632: )
22633: )
22634: THEN nvl(okcB.START_DATE,sysdate)
22635: ELSE (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22631: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22632: )
22633: )
22634: THEN nvl(okcB.START_DATE,sysdate)
22635: ELSE (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22636: END
22637: ) AS okcB_START_DATE --okcB.START_DATE AS okcB_START_DATE
22638: ,(CASE
22639: WHEN (
22637: ) AS okcB_START_DATE --okcB.START_DATE AS okcB_START_DATE
22638: ,(CASE
22639: WHEN (
22640: (nvl(okcB.START_DATE,sysdate) BETWEEN
22641: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22642: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22643: )
22644: AND
22645: (nvl(okcB.END_DATE,sysdate) BETWEEN
22638: ,(CASE
22639: WHEN (
22640: (nvl(okcB.START_DATE,sysdate) BETWEEN
22641: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22642: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22643: )
22644: AND
22645: (nvl(okcB.END_DATE,sysdate) BETWEEN
22646: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22642: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22643: )
22644: AND
22645: (nvl(okcB.END_DATE,sysdate) BETWEEN
22646: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22647: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22648: )
22649: )
22650: THEN nvl(okcB.END_DATE,sysdate)
22643: )
22644: AND
22645: (nvl(okcB.END_DATE,sysdate) BETWEEN
22646: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22647: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22648: )
22649: )
22650: THEN nvl(okcB.END_DATE,sysdate)
22651: ELSE (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22647: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22648: )
22649: )
22650: THEN nvl(okcB.END_DATE,sysdate)
22651: ELSE (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22652: END
22653: ) AS okcB_END_DATE --okcB.END_DATE AS okcB_END_DATE
22654: ,okcB.ATTRIBUTE_CATEGORY AS okcB_ATTRIBUTE_CATEGORY
22655: ,okcB.ATTRIBUTE1 AS okcB_ATTRIBUTE1
22719: ,nvl(ApiLinesRecTab.LineExistsYNTab(i),'N')
22720: ,(CASE
22721: WHEN (
22722: (nvl(okcB.START_DATE,sysdate) BETWEEN
22723: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22724: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22725: AND
22726: (nvl(okcB.END_DATE,sysdate) BETWEEN
22727: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22720: ,(CASE
22721: WHEN (
22722: (nvl(okcB.START_DATE,sysdate) BETWEEN
22723: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22724: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22725: AND
22726: (nvl(okcB.END_DATE,sysdate) BETWEEN
22727: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22728: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22723: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22724: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22725: AND
22726: (nvl(okcB.END_DATE,sysdate) BETWEEN
22727: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22728: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22729: )
22730: THEN 'N'
22731: ELSE 'Y'
22724: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22725: AND
22726: (nvl(okcB.END_DATE,sysdate) BETWEEN
22727: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22728: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22729: )
22730: THEN 'N'
22731: ELSE 'Y'
22732: END
22734: ,(CASE
22735: WHEN (
22736: (
22737: (nvl(okcB.START_DATE,sysdate) BETWEEN
22738: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22739: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22740: )
22741: AND
22742: (nvl(okcB.END_DATE,sysdate) BETWEEN
22735: WHEN (
22736: (
22737: (nvl(okcB.START_DATE,sysdate) BETWEEN
22738: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22739: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22740: )
22741: AND
22742: (nvl(okcB.END_DATE,sysdate) BETWEEN
22743: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22739: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22740: )
22741: AND
22742: (nvl(okcB.END_DATE,sysdate) BETWEEN
22743: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22744: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22745: )
22746: )
22747: AND okcB.DATE_TERMINATED IS NULL
22740: )
22741: AND
22742: (nvl(okcB.END_DATE,sysdate) BETWEEN
22743: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22744: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22745: )
22746: )
22747: AND okcB.DATE_TERMINATED IS NULL
22748: )
22750: ELSE 'Y'
22751: END
22752: ) AS okcB_GEN_SUBLINE_BSCH_YN
22753: FROM
22754: OKC_K_LINES_B okcB
22755: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
22756: AND ApiLinesRecTab.ToLineTab(i) IS NOT NULL
22757: AND ApiLinesRecTab.LineExistsYNTab(i) = 'Y'
22758: AND okcB.DNZ_CHR_ID = P_From_Chr_ID;
22762: /*
22763: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22764: FND_LOG.string(FND_LOG.level_unexpected
22765: ,l_module_name
22766: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : d) :'||SQLERRM);
22767: End If;
22768: */
22769:
22770:
22775:
22776: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22777: FND_LOG.string(FND_LOG.level_statement
22778: ,l_module_name
22779: ,'After Bulk Insert into OKC_K_LINES_B_TEMP'
22780: );
22781: End If;
22782:
22783: -----------------------------------------------------------------------------
22780: );
22781: End If;
22782:
22783: -----------------------------------------------------------------------------
22784: -----Update of OKC_K_LINES_B_TEMP to set CLE_ID to point to the new IDs of
22785: -----Target lines to be created
22786: printTemptable;
22787:
22788: Begin
22785: -----Target lines to be created
22786: printTemptable;
22787:
22788: Begin
22789: UPDATE OKC_K_LINES_B_TEMP a
22790: SET a.CLE_ID = (select b.ID
22791: FROM OKC_K_LINES_B_TEMP b
22792: WHERE
22793: b.ORIG_SYSTEM_ID1 = a.CLE_ID
22787:
22788: Begin
22789: UPDATE OKC_K_LINES_B_TEMP a
22790: SET a.CLE_ID = (select b.ID
22791: FROM OKC_K_LINES_B_TEMP b
22792: WHERE
22793: b.ORIG_SYSTEM_ID1 = a.CLE_ID
22794: AND b.DNZ_CHR_ID = P_To_Chr_ID)
22795: WHERE
22800: WHEN OTHERS THEN
22801: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22802: FND_LOG.string(FND_LOG.level_unexpected
22803: ,l_module_name
22804: ,'During update of cle_id in OKC_K_LINES_B_TEMP :'
22805: ||SQLERRM
22806: );
22807: End If;
22808: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
22828: ------------------------------------------------------------------------------------------------------
22829: ------------------------------------------------------------------------------
22830:
22831: END IF; -----End of Check of P_Lines_Tbl.COUNT > 0+++++++++++++++++++++++
22832: -----for insert into OKC_K_LINES_B_TEMP +++++++++++++++++++++++++
22833:
22834: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
22835:
22836:
22830:
22831: END IF; -----End of Check of P_Lines_Tbl.COUNT > 0+++++++++++++++++++++++
22832: -----for insert into OKC_K_LINES_B_TEMP +++++++++++++++++++++++++
22833:
22834: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
22835:
22836:
22837: -----------------------------------------------------------------------------
22838: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22834: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
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, *****************
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 *****************
22844:
22845: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22846: FND_LOG.string(FND_LOG.level_statement
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
22848: ,'Start of BULK FETCH from OKC_K_LINES_B_TEMP'
22849: ||'Count of Lines in P_Lines_Tbl='||P_Lines_Tbl.COUNT
22850: );
22851: End If;
22852:
22850: );
22851: End If;
22852:
22853:
22854: --------Start of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
22855: Begin
22856:
22857:
22858: IF p_include_cancelled_lines = 'Y' AND
22961: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
23174: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
23387: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
23600: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
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
23722: ,OKCOKSLinesRecTab.OkcI_REQUEST_ID
23723: ,OKCOKSLinesRecTab.OksB_COUNTER_VALUE_ID LIMIT G_BULK_FETCH_LIMIT;
23724: END IF;
23725:
23726: ---Bulk insert into OKC_K_LINES_B---
23727:
23728: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23729: INSERT INTO OKC_K_LINES_B(
23730: ID
23725:
23726: ---Bulk insert into OKC_K_LINES_B---
23727:
23728: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23729: INSERT INTO OKC_K_LINES_B(
23730: ID
23731: , LINE_NUMBER
23732: , CHR_ID
23733: , CLE_ID
23929:
23930: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
23931: FND_LOG.string(FND_LOG.level_statement
23932: ,l_module_name
23933: ,'After BULK INSERT into OKC_K_LINES_B'
23934: );
23935: End If;
23936:
23937:
24604: CLOSE GetOKCOKSLinesB_Temp_exclude;
24605: END IF;
24606:
24607: End;
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 *****************
24607: End;
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: ----------------------------------------------------------------------------
24612: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24613:
24614:
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'
24613:
24614:
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: ----------------------------------------------------------------------------
24624: Begin
24625:
24626: UPDATE OKC_K_LINES_B a
24627: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
24628: from OKC_K_LINES_B b
24629: where b.CLE_ID = a.ID
24630: AND b.DNZ_CHR_ID = P_To_Chr_ID
24624: Begin
24625:
24626: UPDATE OKC_K_LINES_B a
24627: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
24628: from OKC_K_LINES_B b
24629: where b.CLE_ID = a.ID
24630: AND b.DNZ_CHR_ID = P_To_Chr_ID
24631: AND b.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24632: WHERE
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
24643: WHERE
24644: b.ID = bt.ID
24645: AND b.CLE_ID = a.CLE_ID
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
24643: WHERE
24644: b.ID = bt.ID
24645: AND b.CLE_ID = a.CLE_ID
24646: AND b.DNZ_CHR_ID = P_To_Chr_ID
24646: AND b.DNZ_CHR_ID = P_To_Chr_ID
24647: AND b.LSE_ID in (2,20))
24648: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
24649: AND a.STANDARD_COV_YN = 'N'
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
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
24662: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24662: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24663: WHERE a.dnz_chr_id = P_To_Chr_ID
24664: and a.CLE_ID = (select b.id
24665: FROM
24666: OKC_K_LINES_B b
24667: WHERE b.cle_id is NULL
24668: AND b.id = a.cle_id
24669: AND b.LSE_ID <> 46); --Subscription Lines do not have sublines. So they shouldnt be updated.
24670:
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
24688: OR
24689: (bt.cle_id is not null
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
24707: where bt.CLE_ID IS NOT NULL
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
24707: where bt.CLE_ID IS NOT NULL
24708: and bt.LINE_EXISTS_YN = 'Y'
24709: -- hkamdar 05-Oct-2006. Bug# 5361663. Removing Customer, Party and Site Covered levels
24710: -- as the PRICE_UOM should be NULL for them.
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
24725: OR
24726: (bt.cle_id is not null
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
24742: )
24739: where c.id = a.CLE_ID)
24740: )
24741: ,a.PRICE_UOM
24742: )
24743: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24744: where bt.CLE_ID IS NOT NULL
24745: and bt.LINE_EXISTS_YN = 'Y'
24746: -- hkamdar 05-Oct-2006. Bug# 5361663. Removing Customer, Party and Site Covered levels
24747: -- as the PRICE_UOM should be NULL for them.
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)
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)
24766: FROM OKC_K_LINES_B b
24767: where b.DNZ_CHR_ID = a.ID
24768: and b.CLE_ID IS NULL
24769: AND b.DATE_CANCELLED IS NULL),0) --Cancelled toplines shouldn't be included in sum.Nvl added for 12636704
24770: WHERE a.ID = P_To_Chr_ID;
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
24780: and c.DATE_CANCELLED IS NULL) -- Cancelled toplines shouldn't be included in sum
24782:
24783: End;
24784: --------------------------------------------------------------------------------
24785:
24786: -------------Generate new line numbers in OKC_K_LINES_B for newly created lines-----
24787: IF (P_Lines_Tbl.COUNT > 0) then
24788: Begin
24789: GenerateNewLineNumber(P_To_Chr_ID => P_To_Chr_ID
24790: ,P_Partial_Copy_YN => 'Y'
26251: ,renewal_po_used = NULL
26252: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26253: WHERE CHR_ID = P_To_Chr_ID;
26254:
26255: --Update OKC_K_LINES_B--
26256: l_stage := 'Updating OKC_K_LINES_B';
26257: UPDATE OKC_K_LINES_B
26258: SET
26259: cust_acct_id = NULL
26252: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26253: WHERE CHR_ID = P_To_Chr_ID;
26254:
26255: --Update OKC_K_LINES_B--
26256: l_stage := 'Updating OKC_K_LINES_B';
26257: UPDATE OKC_K_LINES_B
26258: SET
26259: cust_acct_id = NULL
26260: ,bill_to_site_use_id = NULL
26253: WHERE CHR_ID = P_To_Chr_ID;
26254:
26255: --Update OKC_K_LINES_B--
26256: l_stage := 'Updating OKC_K_LINES_B';
26257: UPDATE OKC_K_LINES_B
26258: SET
26259: cust_acct_id = NULL
26260: ,bill_to_site_use_id = NULL
26261: ,ship_to_site_use_id = NULL
26280: ,CC_AUTH_CODE = null
26281: ,TRXN_EXTENSION_ID = null
26282: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26283: WHERE DNZ_CHR_ID = P_To_Chr_ID
26284: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL);
26285:
26286: --Delete line level party contacts--
26287: l_stage := 'Deleting from OKC_CONTACTS';
26288: DELETE from okc_contacts
26310: CLOSE l_party_comp_csr;
26311:
26312: IF l_party_comp_count > 0 THEN
26313: Begin
26314: --Update OKC_K_LINES_B--
26315: l_stage := 'Updating OKC_K_LINES_B';
26316: UPDATE OKC_K_LINES_B
26317: SET
26318: cust_acct_id = NULL
26311:
26312: IF l_party_comp_count > 0 THEN
26313: Begin
26314: --Update OKC_K_LINES_B--
26315: l_stage := 'Updating OKC_K_LINES_B';
26316: UPDATE OKC_K_LINES_B
26317: SET
26318: cust_acct_id = NULL
26319: ,bill_to_site_use_id = NULL
26312: IF l_party_comp_count > 0 THEN
26313: Begin
26314: --Update OKC_K_LINES_B--
26315: l_stage := 'Updating OKC_K_LINES_B';
26316: UPDATE OKC_K_LINES_B
26317: SET
26318: cust_acct_id = NULL
26319: ,bill_to_site_use_id = NULL
26320: ,ship_to_site_use_id = NULL
26321: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26322: ,PAYMENT_INSTRUCTION_TYPE = 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
26338: ,CC_AUTH_CODE = null
26339: ,TRXN_EXTENSION_ID = null
26340: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26341: WHERE DNZ_CHR_ID = P_To_Chr_ID
26342: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26343: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26344:
26345: --Delete line level party contacts--
26346: l_stage := 'Deleting from OKC_CONTACTS';
26339: ,TRXN_EXTENSION_ID = null
26340: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26341: WHERE DNZ_CHR_ID = P_To_Chr_ID
26342: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26343: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26344:
26345: --Delete line level party contacts--
26346: l_stage := 'Deleting from OKC_CONTACTS';
26347: DELETE from okc_contacts
26346: l_stage := 'Deleting from OKC_CONTACTS';
26347: DELETE from okc_contacts
26348: where cpl_id in (SELECT ID from OKC_K_PARTY_ROLES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
26349: and CLE_ID is NOT NULL
26350: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26351: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP)));
26352:
26353: --Delete line level party roles--
26354: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26347: DELETE from okc_contacts
26348: where cpl_id in (SELECT ID from OKC_K_PARTY_ROLES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
26349: and CLE_ID is NOT NULL
26350: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26351: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP)));
26352:
26353: --Delete line level party roles--
26354: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26355: DELETE from okc_k_party_roles_b
26354: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26355: DELETE from okc_k_party_roles_b
26356: where dnz_chr_id = P_To_Chr_ID
26357: and cle_id is not null
26358: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26359: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26360: End;
26361: END IF;
26362: --end npalepu
26355: DELETE from okc_k_party_roles_b
26356: where dnz_chr_id = P_To_Chr_ID
26357: and cle_id is not null
26358: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26359: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26360: End;
26361: END IF;
26362: --end npalepu
26363: End If;
26375:
26376: END Party_Cleanup;
26377:
26378: --==============================================================================
26379: --Procedure PURGE_OKC_K_LINES_B_TEMP
26380: --==============================================================================
26381: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26382: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26383: l_schema_name VARCHAR2(3) := 'OKC';
26377:
26378: --==============================================================================
26379: --Procedure PURGE_OKC_K_LINES_B_TEMP
26380: --==============================================================================
26381: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26382: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26383: l_schema_name VARCHAR2(3) := 'OKC';
26384: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26385: ||l_schema_name
26378: --==============================================================================
26379: --Procedure PURGE_OKC_K_LINES_B_TEMP
26380: --==============================================================================
26381: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26382: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26383: l_schema_name VARCHAR2(3) := 'OKC';
26384: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26385: ||l_schema_name
26386: ||'.'
26383: l_schema_name VARCHAR2(3) := 'OKC';
26384: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26385: ||l_schema_name
26386: ||'.'
26387: ||'OKC_K_LINES_B_TEMP';
26388:
26389: Begin
26390: EXECUTE IMMEDIATE l_statement;
26391: EXCEPTION
26392: WHEN OTHERS THEN
26393: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, substr(SQLERRM,1,240));
26394: RAISE;
26395:
26396: End PURGE_OKC_K_LINES_B_TEMP;
26397:
26398: --=====================================================================================
26399: --Procedure COPY_COMPONENTS
26400: --====================================================================================
26480: SELECT
26481: ID as NEW_LINE_ID
26482: ,ORIG_SYSTEM_ID1 as OLD_LINE_ID
26483: FROM
26484: OKC_K_LINES_B_TEMP
26485: where
26486: CLE_ID IS NULL;
26487:
26488: l_old_line_id number;
27072: End;
27073:
27074: --Purge Global Temporary Table when called from Update Service
27075: --This is to support splitting of line into more than 2 lines
27076: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
27077: --multiple rows with same orig_system_id1
27078: Begin
27079: /*delete from okc_k_lines_b_temp;*/
27080: PURGE_OKC_K_LINES_B_TEMP;
27075: --This is to support splitting of line into more than 2 lines
27076: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
27077: --multiple rows with same orig_system_id1
27078: Begin
27079: /*delete from okc_k_lines_b_temp;*/
27080: PURGE_OKC_K_LINES_B_TEMP;
27081: End;
27082:
27083: END IF;
27076: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
27077: --multiple rows with same orig_system_id1
27078: Begin
27079: /*delete from okc_k_lines_b_temp;*/
27080: PURGE_OKC_K_LINES_B_TEMP;
27081: End;
27082:
27083: END IF;
27084: