[Home] [Help]
100: IS
101:
102: CURSOR l_target_line_csr (l_target_line_id NUMBER) IS
103: SELECT start_date,end_date,orig_system_id1
104: FROM okc_k_lines_b
105: WHERE id = l_target_line_id;
106:
107: l_target_line_rec l_target_line_csr%ROWTYPE;
108:
107: l_target_line_rec l_target_line_csr%ROWTYPE;
108:
109: CURSOR l_source_line_csr (l_source_line_id NUMBER) IS
110: SELECT start_date,end_date
111: FROM okc_k_lines_b
112: WHERE id = l_source_line_id;
113:
114:
115: l_source_line_rec l_source_line_csr%ROWTYPE;
185:
186: Procedure printTemptable IS
187: cursor gettemptablelines is
188: select id,cle_id,lse_id,orig_system_id1
189: from okc_k_lines_b_temp;
190: l_id NUMBER;
191: l_cle_id NUMBER;
192: l_lse_id NUMBER;
193: l_orig_system_id1 NUMBER;
867: l_cle_id NUMBER := OKC_API.G_MISS_NUM;
868:
869: CURSOR c_dnz_chr_id(p_id IN NUMBER) IS
870: SELECT dnz_chr_id
871: FROM okc_k_lines_b
872: WHERE id = p_id;
873:
874: CURSOR c_ctcv IS
875: SELECT id
1420: l_parent_end_date DATE;
1421:
1422: CURSOR c_cle IS
1423: SELECT start_date,end_date
1424: FROM okc_k_lines_b
1425: WHERE id = p_to_cle_id;
1426:
1427: CURSOR c_chr IS
1428: SELECT start_date,end_date
3758: select
3759: id
3760: ,line_number
3761: ,cle_id
3762: from okc_k_lines_b
3763: where dnz_chr_id = P_To_Chr_ID
3764: and (
3765: (cle_id is NOT NULL and lse_id in (7,8,9,10,11,13,25,35))
3766: OR
3773: b.id
3774: ,b.line_number
3775: ,b.cle_id
3776: from
3777: okc_k_lines_b_temp bt
3778: ,okc_k_lines_b b
3779: where b.dnz_chr_id = P_To_Chr_ID
3780: and (
3781: (b.cle_id is NOT NULL and b.lse_id in (7,8,9,10,11,13,25,35))
3774: ,b.line_number
3775: ,b.cle_id
3776: from
3777: okc_k_lines_b_temp bt
3778: ,okc_k_lines_b b
3779: where b.dnz_chr_id = P_To_Chr_ID
3780: and (
3781: (b.cle_id is NOT NULL and b.lse_id in (7,8,9,10,11,13,25,35))
3782: OR
3853: SELECT nvl(max(to_number(line_number)),0)
3854: INTO
3855: l_topline_no
3856: FROM
3857: OKC_K_LINES_B
3858: WHERE CLE_ID IS NULL
3859: AND DNZ_CHR_ID = P_To_Chr_ID
3860: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3861: End;
3856: FROM
3857: OKC_K_LINES_B
3858: WHERE CLE_ID IS NULL
3859: AND DNZ_CHR_ID = P_To_Chr_ID
3860: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3861: End;
3862: l_topline_no := l_topline_no + 1;
3863: END IF;
3864: END IF;
3864: END IF;
3865: l_topline_num_initialized := 'Y';
3866: END IF;
3867:
3868: update okc_k_lines_b
3869: set line_number = l_topline_no
3870: where id = l_id;
3871:
3872: l_topline_no := l_topline_no + 1;
3886: SELECT nvl(max(to_number(line_number)),0)
3887: INTO
3888: l_subline_no
3889: FROM
3890: OKC_K_LINES_B
3891: WHERE CLE_ID = l_cle_id
3892: AND LSE_ID in (7,8,9,10,11,13,25,35)
3893: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3894: End;
3889: FROM
3890: OKC_K_LINES_B
3891: WHERE CLE_ID = l_cle_id
3892: AND LSE_ID in (7,8,9,10,11,13,25,35)
3893: AND ID NOT IN (Select a.id from OKC_K_LINES_B_TEMP a);
3894: End;
3895: l_subline_no := l_subline_no + 1;
3896: END IF;
3897: END IF;
3898: l_subline_num_initialized := 'Y';
3899: END IF;
3900: END IF;
3901:
3902: update okc_k_lines_b
3903: set line_number = l_subline_no
3904: where id = l_id;
3905:
3906: l_subline_no := l_subline_no + 1;
3969: ,OkcTL.OKE_BOE_DESCRIPTION AS OkcTL_OKE_BOE_DESCRIPTION
3970: ,OkcTL.COGNOMEN AS OkcTL_COGNOMEN
3971: FROM
3972: OKC_K_LINES_TL okcTL
3973: ,OKC_K_LINES_B okcB
3974: where
3975: okcB.ORIG_SYSTEM_ID1 = okcTL.ID
3976: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
3977:
3990: ,OkcTL.OKE_BOE_DESCRIPTION AS OkcTL_OKE_BOE_DESCRIPTION
3991: ,OkcTL.COGNOMEN AS OkcTL_COGNOMEN
3992: FROM
3993: OKC_K_LINES_TL okcTL
3994: ,OKC_K_LINES_B_TEMP okcBT
3995: where
3996: okcBT.ORIG_SYSTEM_ID1 = okcTL.ID
3997: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
3998:
4600: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
4601: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
4602: FROM
4603: OKC_PRICE_ATT_VALUES okcPAV
4604: ,OKC_K_LINES_B okcB
4605: WHERE okcPAV.CLE_ID = okcB.ORIG_SYSTEM_ID1
4606: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
4607:
4608: --------------------------------------------------------------------------
5046: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
5047: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
5048: FROM
5049: OKC_PRICE_ATT_VALUES okcPAV
5050: ,OKC_K_LINES_B_TEMP okcBT
5051: WHERE okcPAV.CLE_ID = okcBT.ORIG_SYSTEM_ID1
5052: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
5053:
5054:
5499: ,okcPAV.REQUEST_ID AS okcPAV_REQUEST_ID
5500: ,1 AS okcPAV_OBJECT_VERSION_NUMBER
5501: FROM
5502: OKC_PRICE_ATT_VALUES okcPAV
5503: ,OKC_K_LINES_B_TEMP okcBT
5504: WHERE okcPAV.CLE_ID = okcBT.ORIG_SYSTEM_ID1
5505: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
5506:
5507: Begin
7111: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7112: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7113: FROM
7114: OKC_PRICE_ADJUSTMENTS okcPA
7115: ,OKC_K_LINES_B okcB
7116: WHERE okcPA.CLE_ID = okcB.ORIG_SYSTEM_ID1
7117: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
7118:
7119: -----------------------------------------------------------------------
7260: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7261: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7262: FROM
7263: OKC_PRICE_ADJUSTMENTS okcPA
7264: ,OKC_K_LINES_B_TEMP okcBT
7265: WHERE okcPA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
7266: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
7267:
7268: ----------------------------------------------------------------------------------------
7416: ,okcPA.INCLUDE_ON_RETURNS_FLAG AS okcPA_INCLUDE_ON_RETURNS_FLAG
7417: ,okcPA.REBATE_TRANSACTION_TYPE_CODE AS okcPA_REBATE_TRXN_TYPE_CODE
7418: FROM
7419: OKC_PRICE_ADJUSTMENTS okcPA
7420: ,OKC_K_LINES_B_TEMP okcBT
7421: WHERE okcPA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
7422: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
7423:
7424:
8065: ,'COPY' AS okcPRB_ORIG_SYSTEM_REFERENCE1
8066: ,NULL AS okcPRB_ORIG_SYSTEM_SOURCE_CODE
8067: FROM
8068: OKC_K_PARTY_ROLES_B okcPRB
8069: ,OKC_K_LINES_B okcB
8070: WHERE
8071: okcPRB.CLE_ID = okcB.ORIG_SYSTEM_ID1
8072: AND okcPRB.DNZ_CHR_ID = P_From_Chr_ID
8073: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
8117: ,'COPY' AS okcPRB_ORIG_SYSTEM_REFERENCE1
8118: ,NULL AS okcPRB_ORIG_SYSTEM_SOURCE_CODE
8119: FROM
8120: OKC_K_PARTY_ROLES_B okcPRB
8121: ,OKC_K_LINES_B_TEMP okcBT
8122: WHERE
8123: okcPRB.CLE_ID = okcBT.ORIG_SYSTEM_ID1
8124: AND okcPRB.CHR_ID IS NULL
8125: AND okcPRB.DNZ_CHR_ID = P_From_Chr_ID
8528: ,okcPRTL.SECURITY_GROUP_ID AS okcPRTL_SECURITY_GROUP_ID
8529: FROM
8530: OKC_K_PARTY_ROLES_TL okcPRTL
8531: ,OKC_K_PARTY_ROLES_B okcPRB
8532: ,OKC_K_LINES_B_TEMP okcBT
8533: where okcPRTL.ID = okcPRB.ORIG_SYSTEM_ID1
8534: AND okcPRB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
8535: AND okcPRB.CLE_ID = okcBT.ID
8536: AND okcPRB.DNZ_CHR_ID = P_To_Chr_ID;
8733: , okcC.SALES_GROUP_ID AS okcC_SALES_GROUP_ID
8734: FROM
8735: OKC_CONTACTS okcC
8736: ,OKC_K_PARTY_ROLES_B okcPRB
8737: ,OKC_K_LINES_B_TEMP okcBT
8738: WHERE okcC.CPL_ID = okcPRB.ORIG_SYSTEM_ID1
8739: AND okcPRB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
8740: AND okcPRB.CLE_ID = okcBT.ID
8741: AND okcPRB.DNZ_CHR_ID = P_To_Chr_ID
8967: ,NULL AS OkcOL_MESSAGE_CODE
8968: ,NULL AS OkcOL_PARENT_OLE_ID
8969: ,'Y' AS OkcOL_ACTIVE_YN
8970: FROM
8971: OKC_K_LINES_B okcB
8972: WHERE
8973: okcB.DNZ_CHR_ID = P_To_Chr_ID;
8974:
8975: Begin
9064: where
9065: ola.SUBJECT_CHR_ID = P_To_Chr_ID
9066: AND ola.SUBJECT_CLE_ID in (SELECT okcb.ID
9067: from
9068: OKC_K_LINES_B okcb
9069: WHERE
9070: okcb.DNZ_CHR_ID = P_To_Chr_ID
9071: AND okcb.CLE_ID IS NULL);
9072: ------------------------------------------------------------------------------------------
9074: UPDATE okc_operation_lines ola
9075: set ola.PARENT_OLE_ID = (select olb.ID
9076: from okc_operation_lines olb
9077: where olb.SUBJECT_CLE_ID = (select b.CLE_ID from
9078: okc_k_lines_b b
9079: where b.ID = ola.SUBJECT_CLE_ID
9080: )
9081: )
9082: where
9082: where
9083: ola.SUBJECT_CHR_ID = P_To_Chr_ID
9084: and ola.SUBJECT_CLE_ID in (select okcB.ID
9085: from
9086: OKC_K_LINES_B okcB
9087: where okcB.ID = ola.SUBJECT_CLE_ID
9088: and okcB.CLE_ID IS NOT NULL);
9089: ------------------------------------------------------------------------------------------
9090:
9152: , oksTL.SECURITY_GROUP_ID AS oksTL_SECURITY_GROUP_ID
9153: FROM
9154: OKS_K_LINES_TL oksTL
9155: ,OKS_K_LINES_B oksB
9156: ,OKC_K_LINES_B_TEMP okcBT
9157: WHERE oksTL.ID = oksB.ORIG_SYSTEM_ID1
9158: AND oksB.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
9159: AND oksB.CLE_ID = okcBT.ID
9160: AND oksB.DNZ_CHR_ID = P_To_Chr_ID;
9287: , 1 AS OksRD_OBJECT_VERSION_NUMBER
9288: , oksRD.SECURITY_GROUP_ID AS OksRD_SECURITY_GROUP_ID
9289: FROM
9290: OKS_REV_DISTRIBUTIONS oksRD
9291: ,OKC_K_LINES_B okcB
9292: WHERE oksRD.CLE_ID = okcB.ORIG_SYSTEM_ID1
9293: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9294:
9295: CURSOR GetOksRDPartial IS
9304: , 1 AS OksRD_OBJECT_VERSION_NUMBER
9305: , oksRD.SECURITY_GROUP_ID AS OksRD_SECURITY_GROUP_ID
9306: FROM
9307: OKS_REV_DISTRIBUTIONS oksRD
9308: ,OKC_K_LINES_B_TEMP okcBT
9309: WHERE oksRD.CLE_ID = okcBT.ORIG_SYSTEM_ID1
9310: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
9311:
9312: Begin
9467: , oksQ.QUAL_ATTR_VALUE_TO_NUMBER AS oksQ_QUAL_ATTRVALUE_TO_NUM
9468: , oksQ.SECURITY_GROUP_ID AS oksQ_SECURITY_GROUP_ID
9469: FROM
9470: OKS_QUALIFIERS oksQ
9471: ,OKC_K_LINES_B okcB
9472: WHERE
9473: oksQ.LIST_LINE_ID = okcB.ORIG_SYSTEM_ID1
9474: AND okcB.CLE_ID IS NULL --Qualifiers are only for Top Lines
9475: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9519: , oksQ.QUAL_ATTR_VALUE_TO_NUMBER AS oksQ_QUAL_ATTRVALUE_TO_NUM
9520: , oksQ.SECURITY_GROUP_ID AS oksQ_SECURITY_GROUP_ID
9521: FROM
9522: OKS_QUALIFIERS oksQ
9523: ,OKC_K_LINES_B_TEMP okcBT
9524: WHERE
9525: oksQ.LIST_LINE_ID = okcBT.ORIG_SYSTEM_ID1
9526: AND okcBT.CLE_ID IS NULL --Qualifiers are only for Top Lines
9527: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
9787: ,NULL AS oksCTZ_ORIG_SYSTEM_SOURCE_CODE --*
9788: ,'COPY' AS oksCTZ_ORIG_SYSTEM_REFERENCE1 --*
9789: FROM
9790: OKS_COVERAGE_TIMEZONES oksCTZ
9791: ,OKC_K_LINES_B okcB
9792: WHERE
9793: oksCTZ.CLE_ID = okcB.ORIG_SYSTEM_ID1
9794: AND oksCTZ.DNZ_CHR_ID = P_From_Chr_ID
9795: AND okcB.DNZ_CHR_ID = P_To_Chr_ID;
9812: ,NULL AS oksCTZ_ORIG_SYSTEM_SOURCE_CODE --*
9813: ,'COPY' AS oksCTZ_ORIG_SYSTEM_REFERENCE1 --*
9814: FROM
9815: OKS_COVERAGE_TIMEZONES oksCTZ
9816: ,OKC_K_LINES_B_TEMP okcBT
9817: WHERE
9818: oksCTZ.CLE_ID = okcBT.ORIG_SYSTEM_ID1
9819: AND oksCTZ.DNZ_CHR_ID = P_From_Chr_ID
9820: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID;
10013: ,oksCT.REQUEST_ID AS oksCT_REQUEST_ID
10014: FROM
10015: OKS_COVERAGE_TIMES oksCT
10016: ,OKS_COVERAGE_TIMEZONES oksCTZ
10017: ,OKC_K_LINES_B_TEMP okcBT
10018: WHERE
10019: OksCT.COV_TZE_LINE_ID = oksCTZ.ORIG_SYSTEM_ID1
10020: AND oksCTZ.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10021: AND oksCTZ.CLE_ID = okcBT.ID
10205: ,NULL AS oksPMA_ORIG_SYSTEM_SOURCE_CODE --*
10206: ,'COPY' AS oksPMA_ORIG_SYSTEM_REFERENCE1 --*
10207: FROM
10208: OKS_PM_ACTIVITIES oksPMA
10209: ,OKC_K_LINES_B okcB
10210: WHERE oksPMA.CLE_ID = okcB.ORIG_SYSTEM_ID1
10211: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
10212: AND oksPMA.DNZ_CHR_ID = P_From_Chr_ID;
10213:
10231: ,NULL AS oksPMA_ORIG_SYSTEM_SOURCE_CODE --*
10232: ,'COPY' AS oksPMA_ORIG_SYSTEM_REFERENCE1 --*
10233: FROM
10234: OKS_PM_ACTIVITIES oksPMA
10235: ,OKC_K_LINES_B_TEMP okcBT
10236: WHERE oksPMA.CLE_ID = okcBT.ORIG_SYSTEM_ID1
10237: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
10238: AND oksPMA.DNZ_CHR_ID = P_From_Chr_ID
10239: 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
10443: ,NULL AS oksPMSL_ORIG_SYS_SOURCE_CODE --*
10444: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10445: FROM
10446: OKS_PM_STREAM_LEVELS oksPMSL
10447: ,OKC_K_LINES_B okcB
10448: WHERE
10449: oksPMSL.CLE_ID = okcB.ORIG_SYSTEM_ID1
10450: AND oksPMSL.ACTIVITY_LINE_ID IS NULL
10451: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
10478: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10479: FROM
10480: OKS_PM_STREAM_LEVELS oksPMSL
10481: ,OKS_PM_ACTIVITIES oksPMA
10482: ,OKC_K_LINES_B_TEMP okcBT
10483: WHERE
10484: oksPMSL.ACTIVITY_LINE_ID = oksPMA.ORIG_SYSTEM_ID1
10485: AND oksPMA.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10486: AND oksPMA.CLE_ID = okcBT.ID
10514: ,NULL AS oksPMSL_ORIG_SYS_SOURCE_CODE --*
10515: ,'COPY' AS oksPMSL_ORIG_SYS_REFERENCE1 --*
10516: FROM
10517: OKS_PM_STREAM_LEVELS oksPMSL
10518: ,OKC_K_LINES_B_TEMP okcBT
10519: WHERE
10520: oksPMSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
10521: AND oksPMSL.ACTIVITY_LINE_ID IS NULL
10522: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
10747: ,oksPMSCH.REQUEST_ID AS oksPMSCH_REQUEST_ID
10748: FROM
10749: OKS_PM_SCHEDULES oksPMSCH
10750: ,OKS_PM_STREAM_LEVELS oksPMSL
10751: ,OKC_K_LINES_B_TEMP okcBT
10752: WHERE
10753: oksPMSCH.STREAM_LINE_ID = oksPMSL.ORIG_SYSTEM_ID1
10754: AND oksPMSL.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
10755: AND oksPMSL.CLE_ID = okcBT.ID
10911: CURSOR GetLinesWithDatesChanged IS
10912: SELECT
10913: a.ID as LINE_ID
10914: from
10915: OKC_K_LINES_B_TEMP a
10916: where a.DNZ_CHR_ID = P_To_Chr_ID
10917: and a.lse_id in (1,19)
10918: and a.DATES_CHANGED_YN = 'Y';
10919:
11026: ,NULL AS oksATT_ORIG_SYSTEM_SOURCE_CODE--*
11027: ,'COPY' AS oksATT_ORIG_SYSTEM_REFERENCE1--*
11028: FROM
11029: OKS_ACTION_TIME_TYPES oksATT
11030: ,OKC_K_LINES_B okcB
11031: WHERE oksATT.CLE_ID = okcB.ORIG_SYSTEM_ID1
11032: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11033: AND oksATT.DNZ_CHR_ID = P_From_Chr_ID;
11034:
11049: ,NULL AS oksATT_ORIG_SYSTEM_SOURCE_CODE--*
11050: ,'COPY' AS oksATT_ORIG_SYSTEM_REFERENCE1--*
11051: FROM
11052: OKS_ACTION_TIME_TYPES oksATT
11053: ,OKC_K_LINES_B_TEMP okcBT
11054: WHERE oksATT.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11055: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11056: AND oksATT.DNZ_CHR_ID = P_From_Chr_ID;
11057:
11240: ,1 AS oksAT_OBJECT_VERSION_NUMBER
11241: FROM
11242: OKS_ACTION_TIMES oksAT
11243: ,OKS_ACTION_TIME_TYPES oksATT
11244: ,OKC_K_LINES_B_TEMP okcBT
11245: WHERE oksAT.COV_ACTION_TYPE_ID = oksATT.ORIG_SYSTEM_ID1
11246: AND oksATT.DNZ_CHR_ID = okcBT.DNZ_CHR_ID
11247: AND oksATT.CLE_ID = okcBT.ID
11248: AND oksATT.DNZ_CHR_ID = P_To_Chr_ID
11452: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11453: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11454: FROM
11455: OKS_K_SALES_CREDITS oksSC
11456: ,OKC_K_LINES_B okcB
11457: WHERE oksSC.CLE_ID = okcB.ORIG_SYSTEM_ID1
11458: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11459: AND oksSC.CHR_ID = P_From_Chr_ID;
11460:
11493: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11494: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11495: FROM
11496: OKS_K_SALES_CREDITS oksSC
11497: ,OKC_K_LINES_B_TEMP okcBT
11498: WHERE
11499: oksSC.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11500: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11501: AND oksSC.CHR_ID = P_From_Chr_ID;
11534: ,oksSC.SECURITY_GROUP_ID AS oksSC_SECURITY_GROUP_ID
11535: ,oksSC.SALES_GROUP_ID AS oksSC_SALES_GROUP_ID
11536: FROM
11537: OKS_K_SALES_CREDITS oksSC
11538: ,OKC_K_LINES_B_TEMP okcBT
11539: WHERE
11540: oksSC.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11541: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11542: AND oksSC.CHR_ID = P_From_Chr_ID;
11748: ,oksBSCH.SECURITY_GROUP_ID AS oksBSCH_SECURITY_GROUP_ID
11749: ,1 AS oksBSCH_OBJECT_VERSION_NUMBER
11750: FROM
11751: OKS_BILLRATE_SCHEDULES oksBSCH
11752: ,OKC_K_LINES_B okcB
11753: WHERE oksBSCH.CLE_ID = okcB.ORIG_SYSTEM_ID1
11754: AND okcB.DNZ_CHR_ID = P_To_Chr_ID
11755: AND oksBSCH.DNZ_CHR_ID = P_From_Chr_ID;
11756:
11787: ,oksBSCH.SECURITY_GROUP_ID AS oksBSCH_SECURITY_GROUP_ID
11788: ,1 AS oksBSCH_OBJECT_VERSION_NUMBER
11789: FROM
11790: OKS_BILLRATE_SCHEDULES oksBSCH
11791: ,OKC_K_LINES_B_TEMP okcBT
11792: WHERE oksBSCH.CLE_ID = okcBT.ORIG_SYSTEM_ID1
11793: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
11794: AND oksBSCH.DNZ_CHR_ID = P_From_Chr_ID;
11795:
12076: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12077: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12078: FROM
12079: OKS_STREAM_LEVELS_B oksSL
12080: ,OKC_K_LINES_B okcBTarget
12081: ,OKC_K_LINES_B okcBSource
12082: WHERE
12083: oksSL.CLE_ID = okcBTarget.ORIG_SYSTEM_ID1
12084: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12077: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12078: FROM
12079: OKS_STREAM_LEVELS_B oksSL
12080: ,OKC_K_LINES_B okcBTarget
12081: ,OKC_K_LINES_B okcBSource
12082: WHERE
12083: oksSL.CLE_ID = okcBTarget.ORIG_SYSTEM_ID1
12084: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12085: AND okcBTarget.DNZ_CHR_ID = P_To_Chr_ID
12089: --AND okcB.DATE_TERMINATED IS NULL;
12090: AND (
12091: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12092: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12093: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Terminated
12094: where b.CLE_ID= okcBSource.ID
12095: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12096: and b.DATE_TERMINATED IS NOT NULL
12097: )
12098: )
12099: OR
12100: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12101: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12102: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Terminated
12103: where b.CLE_ID = okcBSource.CLE_ID
12104: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12105: and b.DATE_TERMINATED IS NOT NULL
12106: )
12109: --npalepu added on 31-may-2006 for bug # 5178264
12110: And (
12111: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12112: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12113: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Cancelled
12114: where b.CLE_ID= okcBSource.ID
12115: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12116: and b.DATE_CANCELLED IS NOT NULL
12117: )
12118: )
12119: OR
12120: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12121: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12122: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Cancelled
12123: where b.CLE_ID = okcBSource.CLE_ID
12124: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12125: and b.DATE_CANCELLED IS NOT NULL
12126: )
12195: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12196: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12197: FROM
12198: OKS_STREAM_LEVELS_B oksSL
12199: ,OKC_K_LINES_B_TEMP okcBT
12200: WHERE
12201: oksSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
12202: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
12203: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12207: AND (
12208: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12209: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12210: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12211: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12212: where b.CLE_ID = okcBT.ID
12213: and b.LINE_EXISTS_YN = 'N'
12214: and b.DATE_TERMINATED is not null)
12215: )
12217: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12218: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12219: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12220: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12221: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that TERMINATED
12222: where --e) Its Top Line being copied doesnt have its Dates Changed
12223: (a.CLE_ID = okcBT.CLE_ID
12224: and a.LINE_EXISTS_YN = 'N'
12225: and a.DATE_TERMINATED IS NOT NULL)
12239: AND (
12240: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12241: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12242: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
12243: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12244: where b.CLE_ID = okcBT.ID
12245: and b.LINE_EXISTS_YN = 'N'
12246: and b.DATE_CANCELLED is not null)
12247: )
12249: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12250: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12251: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12252: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12253: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that Cancelled
12254: where --e) Its Top Line being copied doesnt have its Dates Changed
12255: (a.CLE_ID = okcBT.CLE_ID
12256: and a.LINE_EXISTS_YN = 'N'
12257: and a.DATE_CANCELLED IS NOT NULL)
12341: ,NULL AS oksSL_ORIG_SYSTEM_SOURCE_CODE
12342: ,'COPY' AS oksSL_ORIG_SYSTEM_REFERENCE1
12343: FROM
12344: OKS_STREAM_LEVELS_B oksSL
12345: ,OKC_K_LINES_B_TEMP okcBT
12346: WHERE
12347: oksSL.CLE_ID = okcBT.ORIG_SYSTEM_ID1
12348: AND oksSL.DNZ_CHR_ID = P_From_Chr_ID
12349: AND okcBT.DNZ_CHR_ID = P_To_Chr_ID
12351: AND (
12352: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12353: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12354: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12355: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12356: where b.CLE_ID = okcBT.ID
12357: and b.LINE_EXISTS_YN = 'N'
12358: and b.DATE_TERMINATED is not null)
12359: )
12361: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12362: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12363: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12364: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12365: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that TERMINATED
12366: where --e) Its Top Line being copied doesnt have its Dates Changed
12367: (a.CLE_ID = okcBT.CLE_ID
12368: and a.LINE_EXISTS_YN = 'N'
12369: and a.DATE_TERMINATED IS NOT NULL)
12383: AND (
12384: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12385: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12386: AND okcBT.DATE_CANCELLED IS NULL --b) Is not cancelled
12387: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12388: where b.CLE_ID = okcBT.ID
12389: and b.LINE_EXISTS_YN = 'N'
12390: and b.DATE_CANCELLED is not null)
12391: )
12393: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12394: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12395: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's dates havent changed
12396: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12397: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that Cancelled
12398: where --e) Its Top Line being copied doesnt have its Dates Changed
12399: (a.CLE_ID = okcBT.CLE_ID
12400: and a.LINE_EXISTS_YN = 'N'
12401: and a.DATE_CANCELLED IS NOT NULL)
12716: ,oksSL.CLE_ID AS oksLE_NewCleID --*
12717: ,oksSL.DNZ_CHR_ID AS oksLE_NewDnzChrID --*
12718: ,decode(oksSL.CLE_ID,NULL,NULL,(select
12719: decode(a.CLE_ID,NULL,a.ID,a.CLE_ID)
12720: from okc_k_lines_b a
12721: where
12722: a.ID = oksSL.CLE_ID
12723: and a.DNZ_CHR_ID = P_To_Chr_ID )) AS oksLE_NewParentCleID --*
12724: ,oksLE.DATE_END AS oksLE_DATE_END
12784: ,oksLE.DATE_END AS oksLE_DATE_END
12785: FROM
12786: OKS_LEVEL_ELEMENTS oksLE
12787: ,OKS_STREAM_LEVELS_B oksSL
12788: ,OKC_K_LINES_B okcBTarget
12789: ,OKC_K_LINES_B okcBSource
12790: WHERE
12791: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12792: AND oksSL.CHR_ID IS NULL
12785: FROM
12786: OKS_LEVEL_ELEMENTS oksLE
12787: ,OKS_STREAM_LEVELS_B oksSL
12788: ,OKC_K_LINES_B okcBTarget
12789: ,OKC_K_LINES_B okcBSource
12790: WHERE
12791: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12792: AND oksSL.CHR_ID IS NULL
12793: AND oksSL.CLE_ID IS NOT NULL
12797: --AND okcB.DATE_TERMINATED IS NULL;
12798: AND (
12799: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12800: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12801: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Terminated
12802: where b.CLE_ID= okcBSource.ID
12803: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12804: and b.DATE_TERMINATED IS NOT NULL
12805: )
12806: )
12807: OR
12808: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12809: AND okcBSource.DATE_TERMINATED IS NULL --a) Not Terminated
12810: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Terminated
12811: where b.CLE_ID = okcBSource.CLE_ID
12812: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12813: and b.DATE_TERMINATED IS NOT NULL
12814: )
12817: --npalepu added on 31-may-2006 for bug # 5178264
12818: AND (
12819: (okcBSource.CLE_ID IS NULL --Line being copied is a Topline that is:
12820: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12821: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no sublines that are Cancelled
12822: where b.CLE_ID= okcBSource.ID
12823: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12824: and b.DATE_CANCELLED IS NOT NULL
12825: )
12826: )
12827: OR
12828: (okcBSource.CLE_ID IS NOT NULL --Line being copied is a Subline that is:
12829: AND okcBSource.DATE_CANCELLED IS NULL --a) Not Cancelled
12830: AND NOT EXISTS (select b.ID from okc_k_lines_b b --b) Has no peer sublines that are Cancelled
12831: where b.CLE_ID = okcBSource.CLE_ID
12832: and b.DNZ_CHR_ID = okcBSource.DNZ_CHR_ID
12833: and b.DATE_CANCELLED IS NOT NULL
12834: )
12894: ,oksLE.DATE_END AS oksLE_DATE_END
12895: FROM
12896: OKS_LEVEL_ELEMENTS oksLE
12897: ,OKS_STREAM_LEVELS_B oksSL
12898: ,OKC_K_LINES_B_TEMP okcBT
12899: WHERE
12900: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
12901: AND oksSL.CHR_ID IS NULL
12902: AND oksSL.CLE_ID IS NOT NULL
12908: AND (
12909: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12910: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12911: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
12912: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
12913: where b.CLE_ID = okcBT.ID
12914: and b.LINE_EXISTS_YN = 'N'
12915: and b.DATE_TERMINATED is not null)
12916: )
12918: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12919: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12920: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
12921: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
12922: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are TERMINATED
12923: where --e) Its Top Line being copied doesnt have its Dates Changed
12924: (a.CLE_ID = okcBT.CLE_ID
12925: and a.LINE_EXISTS_YN = 'N'
12926: and a.DATE_TERMINATED IS NOT NULL)
12940: AND (
12941: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
12942: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
12943: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
12944: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
12945: where b.CLE_ID = okcBT.ID
12946: and b.LINE_EXISTS_YN = 'N'
12947: and b.DATE_CANCELLED is not null)
12948: )
12950: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
12951: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
12952: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
12953: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
12954: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are Cancelled
12955: where --e) Its Top Line being copied doesnt have its Dates Changed
12956: (a.CLE_ID = okcBT.CLE_ID
12957: and a.LINE_EXISTS_YN = 'N'
12958: and a.DATE_CANCELLED IS NOT NULL)
13025: ,oksLE.DATE_END AS oksLE_DATE_END
13026: FROM
13027: OKS_LEVEL_ELEMENTS oksLE
13028: ,OKS_STREAM_LEVELS_B oksSL
13029: ,OKC_K_LINES_B_TEMP okcBT
13030: WHERE
13031: oksLE.RUL_ID = oksSL.ORIG_SYSTEM_ID1
13032: AND oksSL.CHR_ID IS NULL
13033: AND oksSL.CLE_ID IS NOT NULL
13039: AND (
13040: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
13041: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
13042: AND okcBT.DATE_TERMINATED IS NULL --b) Is not TERMINATED
13043: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are TERMINATED
13044: where b.CLE_ID = okcBT.ID
13045: and b.LINE_EXISTS_YN = 'N'
13046: and b.DATE_TERMINATED is not null)
13047: )
13049: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
13050: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
13051: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
13052: AND okcBT.DATE_TERMINATED IS NULL --c) Subline is not TERMINATED
13053: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are TERMINATED
13054: where --e) Its Top Line being copied doesnt have its Dates Changed
13055: (a.CLE_ID = okcBT.CLE_ID
13056: and a.LINE_EXISTS_YN = 'N'
13057: and a.DATE_TERMINATED IS NOT NULL)
13071: AND (
13072: (okcBT.CLE_ID IS NULL --Line being copied is a Topline with
13073: AND okcBT.DATES_CHANGED_YN = 'N' --a) Dates not changed to honor target
13074: AND okcBT.DATE_CANCELLED IS NULL --b) Is not Cancelled
13075: AND NOT EXISTS (select b.id from okc_k_lines_b_temp b --c) Has no sublines being copied that are Cancelled
13076: where b.CLE_ID = okcBT.ID
13077: and b.LINE_EXISTS_YN = 'N'
13078: and b.DATE_CANCELLED is not null)
13079: )
13081: (okcBT.CLE_ID IS NOT NULL --Line being copied is a subline with
13082: AND okcBT.LINE_EXISTS_YN = 'N' --a) Top line being copied
13083: AND okcBT.DATES_CHANGED_YN = 'N' --b) Subline's Dates havent changed with respect to Target
13084: AND okcBT.DATE_CANCELLED IS NULL --c) Subline is not Cancelled
13085: AND NOT EXISTS (select a.ID from okc_k_lines_b_temp a --d) Has no peer sublines that are Cancelled
13086: where --e) Its Top Line being copied doesnt have its Dates Changed
13087: (a.CLE_ID = okcBT.CLE_ID
13088: and a.LINE_EXISTS_YN = 'N'
13089: and a.DATE_CANCELLED IS NOT NULL)
13380: CURSOR GetTargetLines_F IS
13381: Select
13382: a.ID
13383: FROM
13384: OKC_K_LINES_B a
13385: where a.DNZ_CHR_ID = P_To_Chr_ID
13386: and a.CLE_ID IS NULL;
13387:
13388: CURSOR GetTargetLines_P IS
13388: CURSOR GetTargetLines_P IS
13389: Select
13390: okcB.ID
13391: FROM
13392: OKC_K_LINES_B okcB
13393: ,OKC_K_LINES_B_TEMP okcBT
13394: where okcB.ID = okcBT.ID
13395: and okcB.DNZ_CHR_ID = P_To_Chr_ID
13396: and okcB.CLE_ID IS NULL;
13389: Select
13390: okcB.ID
13391: FROM
13392: OKC_K_LINES_B okcB
13393: ,OKC_K_LINES_B_TEMP okcBT
13394: where okcB.ID = okcBT.ID
13395: and okcB.DNZ_CHR_ID = P_To_Chr_ID
13396: and okcB.CLE_ID IS NULL;
13397:
13425: END IF;
13426:
13427: Begin
13428: FORALL i IN LineIDTab.FIRST..LineIDTab.LAST
13429: UPDATE OKC_K_LINES_B a
13430: SET a.INV_RULE_ID = nvl(a.INV_RULE_ID,G_TrgHdrInvRuleID)
13431: WHERE a.ID = LineIDTab(i);
13432: End;
13433: Begin
13534: okcB.ID as LINE_ID
13535: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
13536: ,okcB.CLE_ID CLE_ID
13537: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
13538: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13539: ) as INV_RULE_ID
13540: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13541: ,decode(oksB.BILLING_SCHEDULE_TYPE
13542: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13547: ,okcB.END_DATE as END_DATE
13548: ,okcB_Source.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13549: ,NULL as GEN_SUBLINE_BSCH_YN
13550: from
13551: OKC_K_LINES_B okcB
13552: ,OKS_K_LINES_B oksB
13553: ,OKC_K_LINES_B okcB_Source
13554: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13555: and okcB.ID = oksB.CLE_ID
13549: ,NULL as GEN_SUBLINE_BSCH_YN
13550: from
13551: OKC_K_LINES_B okcB
13552: ,OKS_K_LINES_B oksB
13553: ,OKC_K_LINES_B okcB_Source
13554: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13555: and okcB.ID = oksB.CLE_ID
13556: and okcB_Source.ID = okcB.ORIG_SYSTEM_ID1
13557: and okcB_Source.DNZ_CHR_ID = P_From_Chr_ID
13559: --npalepu modified on 31-may-2006 for bug # 5178264
13560: /* and (okcB_Source.DATE_TERMINATED IS NOT NULL
13561: OR
13562: (okcB_Source.DATE_TERMINATED IS NULL
13563: 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)
13564: )
13565: ); */
13566: and ((okcB_Source.DATE_TERMINATED IS NOT NULL
13567: OR
13565: ); */
13566: and ((okcB_Source.DATE_TERMINATED IS NOT NULL
13567: OR
13568: (okcB_Source.DATE_TERMINATED IS NULL
13569: 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)
13570: )
13571: ) OR
13572: (okcB_Source.DATE_CANCELLED IS NOT NULL
13573: OR
13571: ) OR
13572: (okcB_Source.DATE_CANCELLED IS NOT NULL
13573: OR
13574: (okcB_Source.DATE_CANCELLED IS NULL
13575: 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)
13576: )
13577: ));
13578: --end npalepu
13579:
13583: okcB.ID as LINE_ID
13584: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
13585: ,okcB.CLE_ID CLE_ID
13586: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
13587: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
13588: ) as INV_RULE_ID
13589: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
13590: ,decode(oksB.BILLING_SCHEDULE_TYPE
13591: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
13596: ,okcB.END_DATE as END_DATE
13597: ,okcBT.DATE_TERMINATED as SOURCE_LINE_DATE_TERMINATED
13598: ,okcBT.GEN_SUBLINE_BSCH_YN as GEN_SUBLINE_BSCH_YN
13599: from
13600: OKC_K_LINES_B okcB
13601: ,OKS_K_LINES_B oksB
13602: ,OKC_K_LINES_B_TEMP okcBT
13603: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13604: and okcB.ID = oksB.CLE_ID
13598: ,okcBT.GEN_SUBLINE_BSCH_YN as GEN_SUBLINE_BSCH_YN
13599: from
13600: OKC_K_LINES_B okcB
13601: ,OKS_K_LINES_B oksB
13602: ,OKC_K_LINES_B_TEMP okcBT
13603: where okcB.DNZ_CHR_ID = P_To_Chr_ID
13604: and okcB.ID = oksB.CLE_ID
13605: and okcBT.ID = okcB.ID
13606: --npalepu modified on 31-may-2006 for bug # 5178264
13607: /* and (
13608: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13609: OR okcBT.DATE_TERMINATED IS NOT NULL
13610: OR (
13611: okcBT.DATE_TERMINATED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13612: where b.CLE_ID= okcBT.ID
13613: and b.DATE_TERMINATED IS NOT NULL
13614: and b.LINE_EXISTS_YN = 'N')
13615: )
13620: and ((
13621: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13622: OR okcBT.DATE_TERMINATED IS NOT NULL
13623: OR (
13624: okcBT.DATE_TERMINATED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13625: where b.CLE_ID= okcBT.ID
13626: and b.DATE_TERMINATED IS NOT NULL
13627: and b.LINE_EXISTS_YN = 'N')
13628: )
13634: (
13635: (okcBT.CLE_ID IS NULL AND (okcBT.DATES_CHANGED_YN = 'Y'
13636: OR okcBT.DATE_CANCELLED IS NOT NULL
13637: OR (
13638: okcBT.DATE_CANCELLED IS NULL and EXISTS (select b.ID from okc_k_lines_b_temp b
13639: where b.CLE_ID= okcBT.ID
13640: and b.DATE_CANCELLED IS NOT NULL
13641: and b.LINE_EXISTS_YN = 'N')
13642: )
14223: ,okcB.LSE_ID as LSE_ID
14224: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
14225: ,okcB.CLE_ID CLE_ID
14226: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
14227: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14228: ) as INV_RULE_ID
14229: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14230: ,decode(oksB.BILLING_SCHEDULE_TYPE
14231: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14244: -- hkamdar 04-Sep-2006 Added for bug# 5458842.
14245: ,oksB.USAGE_TYPE as USAGE_TYPE
14246: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14247: from
14248: OKC_K_LINES_B okcB
14249: ,OKS_K_LINES_B oksB
14250: where okcB.CHR_ID = P_To_Chr_ID
14251: and okcB.ID = oksB.CLE_ID;
14252: -- End hkamdar
14260: ,okcB.LSE_ID as LSE_ID
14261: ,okcB.ORIG_SYSTEM_ID1 as ORIG_SYSTEM_ID1
14262: ,okcB.CLE_ID CLE_ID
14263: ,decode(okcB.CLE_ID,NULL,okcB.INV_RULE_ID --its a topline
14264: ,(select a.INV_RULE_ID from okc_k_lines_b a where id = okcB.CLE_ID) --its a subline
14265: ) as INV_RULE_ID
14266: ,decode(okcB.CLE_ID,NULL,oksB.BILLING_SCHEDULE_TYPE --its a topline
14267: ,decode(oksB.BILLING_SCHEDULE_TYPE
14268: ,NULL,(select a.BILLING_SCHEDULE_TYPE from oks_k_lines_b a where a.CLE_ID = okcB.CLE_ID)
14281: -- hkamdar 04-Sep-2006 Added for bug# 5458842.
14282: ,oksB.USAGE_TYPE as USAGE_TYPE
14283: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14284: from
14285: OKC_K_LINES_B okcB
14286: ,OKS_K_LINES_B oksB
14287: ,OKC_K_LINES_B_TEMP okcBT
14288: where okcB.DNZ_CHR_ID = P_To_Chr_ID
14289: and okcB.ID = oksB.CLE_ID
14283: ,oksB.USAGE_PERIOD as USAGE_PERIOD
14284: from
14285: OKC_K_LINES_B okcB
14286: ,OKS_K_LINES_B oksB
14287: ,OKC_K_LINES_B_TEMP okcBT
14288: where okcB.DNZ_CHR_ID = P_To_Chr_ID
14289: and okcB.ID = oksB.CLE_ID
14290: and okcBT.ID = okcB.ID
14291: and (okcBT.CLE_ID IS NULL --Get all Top lines that are being copied
15366: ,a.end_date
15367: ,b.offset_duration
15368: ,b.offset_period
15369: from
15370: OKC_K_LINES_B_TEMP a
15371: ,OKS_K_LINES_B b
15372: where
15373: a.dnz_chr_id = P_To_Chr_ID
15374: and a.lse_id in (3,21)
15386: CURSOR getBPLineandChildren IS
15387: select
15388: a.ID
15389: from
15390: OKC_K_LINES_B_TEMP a
15391: where a.dnz_chr_id = P_To_Chr_ID
15392: start with a.id = l_BP_ID
15393: connect by prior a.id = a.cle_id;
15394:
15453: EXIT WHEN getBPLineandChildren%NOTFOUND;
15454:
15455:
15456:
15457: UPDATE OKC_K_LINES_B_TEMP SET START_DATE = l_Resultant_Start_Date
15458: WHERE DNZ_CHR_ID = P_To_Chr_ID
15459: AND ID = l_BP_Child_ID;
15460:
15461: END LOOP;
15580:
15581: -- 03-Nov-2006 hkamdar. Bug# 5623498.
15582: CURSOR get_toplvl_uom(p_line_id IN NUMBER) IS
15583: SELECT OEL.PRICING_QUANTITY_UOM
15584: FROM OKC_K_LINES_B OKCLINE,
15585: OKC_K_REL_OBJS OKCREL,
15586: OE_ORDER_LINES_ALL OEL
15587: WHERE OKCLINE.LSE_ID IN (7,9,25)
15588: AND OKCLINE.id = p_line_id
15606: -- End Bug# 5531198 hkamdar 21-Sep-2006
15607:
15608: /*if p_include_cancelled_lines 'Y' AND p_include_terminated_lines = 'Y' */
15609: /* Cursor to get rows to be copied from:
15610: OKC_K_LINES_B
15611: OKS_K_LINES_B
15612: OKC_K_ITEMS
15613: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15614: CURSOR GetOKCOKSLinesB_Copy(P_From_Chr_ID IN NUMBER
15613: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15614: CURSOR GetOKCOKSLinesB_Copy(P_From_Chr_ID IN NUMBER
15615: ,P_To_Chr_ID IN NUMBER) IS
15616: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
15617: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
15618: okcB.ID AS OkcB_OldOKCLineID
15619: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
15620: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
15621: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
15719: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
15720: ,NULL AS OkcB_CANCELLED_AMOUNT
15721: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
15722: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
15723: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
15724: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
15725: ,oksB.ID AS OksB_OldOksLineID
15726: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
15727: ,oksB.CLE_ID AS OksB_CLE_ID
15852: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
15853: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
15854: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
15855: FROM
15856: OKC_K_LINES_B okcB
15857: ,OKS_K_LINES_B oksB
15858: ,OKC_K_ITEMS okcI
15859: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
15860: AND okcB.ID = oksB.CLE_ID(+)
15865: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy-----------------
15866:
15867: /*if p_include_cancelled_lines ='Y' and p_include_terminated_lines = 'N'*/
15868: /* Cursor to get rows to be copied from:
15869: OKC_K_LINES_B
15870: OKS_K_LINES_B
15871: OKC_K_ITEMS
15872: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15873: CURSOR GetOKCOKSLinesB_Copy_CANCEL(P_From_Chr_ID IN NUMBER
15872: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
15873: CURSOR GetOKCOKSLinesB_Copy_CANCEL(P_From_Chr_ID IN NUMBER
15874: ,P_To_Chr_ID IN NUMBER) IS
15875: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
15876: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
15877: okcB.ID AS OkcB_OldOKCLineID
15878: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
15879: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
15880: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
15978: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
15979: ,NULL AS OkcB_CANCELLED_AMOUNT
15980: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
15981: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
15982: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
15983: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
15984: ,oksB.ID AS OksB_OldOksLineID
15985: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
15986: ,oksB.CLE_ID AS OksB_CLE_ID
16111: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
16112: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16113: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16114: FROM
16115: OKC_K_LINES_B okcB
16116: ,OKS_K_LINES_B oksB
16117: ,OKC_K_ITEMS okcI
16118: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16119: AND okcB.ID = oksB.CLE_ID(+)
16125: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_CANCEL-----------------
16126:
16127: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'Y' */
16128: /* Cursor to get rows to be copied from:
16129: OKC_K_LINES_B
16130: OKS_K_LINES_B
16131: OKC_K_ITEMS
16132: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16133: CURSOR GetOKCOKSLinesB_Copy_TERMN(P_From_Chr_ID IN NUMBER
16132: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16133: CURSOR GetOKCOKSLinesB_Copy_TERMN(P_From_Chr_ID IN NUMBER
16134: ,P_To_Chr_ID IN NUMBER) IS
16135: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16136: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16137: okcB.ID AS OkcB_OldOKCLineID
16138: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16139: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16140: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16238: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
16239: ,NULL AS OkcB_CANCELLED_AMOUNT
16240: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16241: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16242: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16243: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16244: ,oksB.ID AS OksB_OldOksLineID
16245: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16246: ,oksB.CLE_ID AS OksB_CLE_ID
16371: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
16372: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16373: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16374: FROM
16375: OKC_K_LINES_B okcB
16376: ,OKS_K_LINES_B oksB
16377: ,OKC_K_ITEMS okcI
16378: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16379: AND okcB.ID = oksB.CLE_ID(+)
16385: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_termn-----------------
16386:
16387: /*if p_include_cancelled_lines = 'N' and p_include_terminated_lines = 'N' */
16388: /* Cursor to get rows to be copied from:
16389: OKC_K_LINES_B
16390: OKS_K_LINES_B
16391: OKC_K_ITEMS
16392: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16393: CURSOR GetOKCOKSLinesB_Copy_exclude(P_From_Chr_ID IN NUMBER
16392: for a specified Source Contract. This cursor is used when P_Renew_Ref_YN = 'N' */
16393: CURSOR GetOKCOKSLinesB_Copy_exclude(P_From_Chr_ID IN NUMBER
16394: ,P_To_Chr_ID IN NUMBER) IS
16395: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16396: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16397: okcB.ID AS OkcB_OldOKCLineID
16398: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16399: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16400: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16498: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
16499: ,NULL AS OkcB_CANCELLED_AMOUNT
16500: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16501: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16502: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16503: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16504: ,oksB.ID AS OksB_OldOksLineID
16505: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16506: ,oksB.CLE_ID AS OksB_CLE_ID
16631: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
16632: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16633: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16634: FROM
16635: OKC_K_LINES_B okcB
16636: ,OKS_K_LINES_B oksB
16637: ,OKC_K_ITEMS okcI
16638: Where okcB.DNZ_CHR_ID = P_From_Chr_ID
16639: AND okcB.ID = oksB.CLE_ID(+)
16646: ---------------End of Cursor Definition for GetOKCOKSLinesB_Copy_EXCLUDE-----------------
16647:
16648:
16649: /* Cursor to get rows to be copied from:
16650: OKC_K_LINES_B
16651: OKS_K_LINES_B
16652: OKC_K_ITEMS
16653: for a specified Source Contracti. This cursor is used when P_Renew_Ref_YN = 'Y'. It filters out
16654: the lines that have been renewed,cancelled or terminated or have been marked as 'DNR' (Do not renew) */
16655:
16656: CURSOR GetOKCOKSLinesB_Renew(P_From_Chr_ID IN NUMBER
16657: ,P_To_Chr_ID IN NUMBER) IS
16658: SELECT /*+ leading(okcB) use_nl(oksB,okcI) */
16659: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
16660: okcB.ID AS OkcB_OldOKCLineID
16661: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OkcB_NewOKCLineID --Target Value
16662: ,okcB.LINE_NUMBER AS OkcB_LINE_NUMBER
16663: ,decode(okcB.CHR_ID,NULL,NULL,P_To_Chr_ID) AS OkcB_NewChrID--Target Value
16750: ,okcB.PAYMENT_INSTRUCTION_TYPE AS OkcB_PAYMENT_INSTRUCTION_TYPE
16751: ,NULL AS OkcB_CANCELLED_AMOUNT
16752: ,decode(okcB.DATE_CANCELLED,NULL,'N','Y') AS OkcB_LINE_CANCELLED_FLAG
16753: ,decode(okcB.DATE_TERMINATED,NULL,'N','Y') AS OkcB_LINE_TERMINATED_FLAG
16754: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
16755: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
16756: ,oksB.ID AS OksB_OldOksLineID
16757: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
16758: ,oksB.CLE_ID AS OksB_CLE_ID
16883: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
16884: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
16885: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
16886: FROM
16887: OKC_K_LINES_B okcB
16888: ,OKS_K_LINES_B oksB
16889: ,OKC_K_ITEMS okcI
16890: WHERE
16891: okcB.ID = oksB.CLE_ID(+)
16904: OR
16905: (okcB.CLE_ID IS NOT NULL
16906: AND NOT EXISTS
16907: (SELECT 'X'
16908: FROM okc_k_lines_b lb2
16909: WHERE NVL(lb2.line_renewal_type_code,'X') = 'DNR'
16910: OR lb2.DATE_TERMINATED IS NOT NULL
16911: OR lb2.DATE_CANCELLED IS NOT NULL
16912: OR lb2.DATE_RENEWED IS NOT NULL
17159: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
17160: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17161: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17162: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17163: -----------------End of record members for OKC_K_LINES_B columns
17164: -----------------Start of record members for OKS_K_LINES_B columns
17165: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17166: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17167: ,OKCOKSLinesRecTab.OksB_CLE_ID
17373: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
17374: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17375: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17376: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17377: -----------------End of record members for OKC_K_LINES_B columns
17378: -----------------Start of record members for OKS_K_LINES_B columns
17379: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17380: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17381: ,OKCOKSLinesRecTab.OksB_CLE_ID
17586: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
17587: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17588: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17589: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17590: -----------------End of record members for OKC_K_LINES_B columns
17591: -----------------Start of record members for OKS_K_LINES_B columns
17592: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17593: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17594: ,OKCOKSLinesRecTab.OksB_CLE_ID
17798: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
17799: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
17800: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
17801: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
17802: -----------------End of record members for OKC_K_LINES_B columns
17803: -----------------Start of record members for OKS_K_LINES_B columns
17804: ,OKCOKSLinesRecTab.OksB_OldOksLineID
17805: ,OKCOKSLinesRecTab.OksB_NewOksLineID
17806: ,OKCOKSLinesRecTab.OksB_CLE_ID
18018: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
18019: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
18020: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG -- pseudo column
18021: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
18022: -----------------End of record members for OKC_K_LINES_B columns
18023: -----------------Start of record members for OKS_K_LINES_B columns
18024: ,OKCOKSLinesRecTab.OksB_OldOksLineID
18025: ,OKCOKSLinesRecTab.OksB_NewOksLineID
18026: ,OKCOKSLinesRecTab.OksB_CLE_ID
18147: END IF;
18148:
18149: END IF;
18150:
18151: --Bulk Insert into OKC_K_LINES_B
18152: Begin
18153:
18154: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18155: INSERT INTO OKC_K_LINES_B(
18151: --Bulk Insert into OKC_K_LINES_B
18152: Begin
18153:
18154: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
18155: INSERT INTO OKC_K_LINES_B(
18156: ID
18157: , LINE_NUMBER
18158: , CHR_ID
18159: , CLE_ID
18344:
18345: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
18346: FND_LOG.string(FND_LOG.level_statement
18347: ,l_module_name
18348: ,'AFTER INSERT into OKC_K_LINES_B '
18349: );
18350: END IF;
18351:
18352: EXCEPTION
18354: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
18355: IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
18356: FND_LOG.string(FND_LOG.level_unexpected
18357: ,l_module_name
18358: ,'EXCEPTION during insert into OKC_K_LINES_B '||SQLERRM
18359: );
18360: END IF;
18361:
18362: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
18743: END IF;
18744: END IF;
18745:
18746:
18747: UPDATE OKC_K_LINES_B
18748: SET
18749: PRICE_NEGOTIATED = l_converted_price_negotiated
18750: ,PRICE_UNIT = l_converted_price_unit
18751: WHERE ID = OKCOKSLinesRecTab.OkcB_NewOkcLineID(i)
19115: ELSIF (P_Renew_Ref_YN = 'Y') THEN
19116: EXIT WHEN GetOKCOKSLinesB_Renew%NOTFOUND;
19117: END IF;
19118:
19119: END LOOP; --End of Bulk fetch and insert into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS
19120:
19121: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19122: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19123: FND_LOG.string(FND_LOG.level_statement
19121: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
19122: IF (FND_LOG.test(FND_LOG.level_statement, l_module_name)) THEN
19123: FND_LOG.string(FND_LOG.level_statement
19124: ,l_module_name
19125: ,'After BULK FETCH and BULK INSERT into OKC_K_LINES_B,OKS_K_LINES_B,OKC_K_ITEMS'
19126: );
19127: END IF;
19128: END IF;
19129:
19140: CLOSE GetOKCOKSLinesB_Renew;
19141: END IF;
19142:
19143: ----------------------------------------------------------------------------
19144: -- Update of OKC_K_LINES_B
19145: -- i) CLE_ID on OKC_K_LINES_B for sublines
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19141: END IF;
19142:
19143: ----------------------------------------------------------------------------
19144: -- Update of OKC_K_LINES_B
19145: -- i) CLE_ID on OKC_K_LINES_B for sublines
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19149: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19142:
19143: ----------------------------------------------------------------------------
19144: -- Update of OKC_K_LINES_B
19145: -- i) CLE_ID on OKC_K_LINES_B for sublines
19146: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
19147: --
19148: -- Update of OKS_K_LINES_B
19149: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
19150: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19150: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
19151:
19152: Begin
19153:
19154: UPDATE OKC_K_LINES_B a
19155: SET a.CLE_ID = (select b.ID
19156: FROM OKC_K_LINES_B b
19157: WHERE
19158: b.ORIG_SYSTEM_ID1 = a.CLE_ID
19152: Begin
19153:
19154: UPDATE OKC_K_LINES_B a
19155: SET a.CLE_ID = (select b.ID
19156: FROM OKC_K_LINES_B b
19157: WHERE
19158: b.ORIG_SYSTEM_ID1 = a.CLE_ID
19159: AND b.DNZ_CHR_ID = P_To_Chr_ID)
19160: WHERE
19160: WHERE
19161: a.DNZ_CHR_ID = P_To_Chr_ID
19162: AND a.CLE_ID IS NOT NULL;
19163:
19164: UPDATE OKC_K_LINES_B a
19165: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
19166: from OKC_K_LINES_B b
19167: where b.CLE_ID = a.ID
19168: AND b.DNZ_CHR_ID = P_To_Chr_ID
19162: AND a.CLE_ID IS NOT NULL;
19163:
19164: UPDATE OKC_K_LINES_B a
19165: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
19166: from OKC_K_LINES_B b
19167: where b.CLE_ID = a.ID
19168: AND b.DNZ_CHR_ID = P_To_Chr_ID
19169: AND b.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum.
19170: WHERE
19173: AND a.LSE_ID <> 46; -- Subscription Lines do not have sublines. So they shouldnt be updated.
19174:
19175: UPDATE OKS_K_LINES_B a
19176: SET a.COVERAGE_ID = (select b.ID
19177: FROM OKC_K_LINES_B b
19178: WHERE
19179: b.CLE_ID = a.CLE_ID
19180: AND b.DNZ_CHR_ID = P_To_Chr_ID
19181: AND b.LSE_ID in (2,20))
19179: b.CLE_ID = a.CLE_ID
19180: AND b.DNZ_CHR_ID = P_To_Chr_ID
19181: AND b.LSE_ID in (2,20))
19182: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
19183: AND a.CLE_ID in (select c.ID from OKC_K_LINES_B c
19184: where c.DNZ_CHR_ID = P_To_Chr_ID
19185: and c.LSE_ID in (1,19))
19186: AND a.STANDARD_COV_YN = 'N';
19187:
19188: UPDATE OKS_K_LINES_B a
19189: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19190: FROM
19191: OKS_K_LINES_B b
19192: ,OKC_K_LINES_B c
19193: WHERE
19194: c.ID = b.CLE_ID
19195: AND c.cle_id = a.cle_id
19196: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldnt be included in sum
19196: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldnt be included in sum
19197: WHERE a.dnz_chr_id = P_To_Chr_ID
19198: and a.CLE_ID = (select b.id
19199: FROM
19200: OKC_K_LINES_B b
19201: WHERE b.cle_id is NULL
19202: AND b.id = a.cle_id
19203: AND b.LSE_ID <> 46); --Subscription Lines do not have sublines. So they shouldnt be updated.
19204:
19207: -- Updating price_uom to NULL for Covered levels - Customer, Party and Site.
19208:
19209: Update OKS_K_LINES_B
19210: Set PRICE_UOM = NULL
19211: WHERE CLE_ID IN (SELECT ID FROM OKC_K_LINES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
19212: AND LSE_ID IN (8,10,35));
19213: -- End hkamdar 05-Oct-2006. Bug# 5361663.
19214:
19215: End;
19215: End;
19216: ---------------------------------------------------------------------------------
19217: --------------------------------------------------------------------------------
19218: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
19219: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
19220: --of all top lines respectively
19221: Begin
19222: UPDATE OKC_K_HEADERS_B a
19223: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
19220: --of all top lines respectively
19221: Begin
19222: UPDATE OKC_K_HEADERS_B a
19223: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
19224: FROM OKC_K_LINES_B b
19225: where b.DNZ_CHR_ID = a.ID
19226: and b.CLE_ID IS NULL
19227: AND b.DATE_CANCELLED IS NULL) --Cancelled toplines shouldn't be included in sum.
19228: WHERE a.ID = P_To_Chr_ID;
19230: UPDATE OKS_K_HEADERS_B a
19231: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
19232: FROM
19233: OKS_K_LINES_B b
19234: ,OKC_K_LINES_B c
19235: where b.DNZ_CHR_ID = a.CHR_ID
19236: and b.CLE_ID = c.ID
19237: and c.CLE_ID IS NULL
19238: and c.DATE_CANCELLED IS NULL) --Cancelled toplines shouldn't be included in sum
19240:
19241: End;
19242: --------------------------------------------------------------------------------
19243:
19244: -------------Generate new line numbers in OKC_K_LINES_B for newly created lines-----
19245: Begin
19246: GenerateNewLineNumber(P_To_Chr_ID => P_To_Chr_ID
19247: ,P_Partial_Copy_YN => 'N'
19248: ,x_return_status => l_return_Status);
20031: lx_rail_rec OKS_TAX_UTIL_PVT.ra_rec_type;
20032: -- 03-Nov-2006 hkamdar. Bug# 5623498.
20033: CURSOR get_toplvl_uom(p_line_id IN NUMBER) IS
20034: SELECT OEL.PRICING_QUANTITY_UOM
20035: FROM OKC_K_LINES_B OKCLINE,
20036: OKC_K_REL_OBJS OKCREL,
20037: OE_ORDER_LINES_ALL OEL
20038: WHERE OKCLINE.LSE_ID IN (7,9,25)
20039: AND OKCLINE.id = p_line_id
20075:
20076: /*if p_include_cancelled_lines = 'Y' AND P_INCLUDE_TERMINATED_LINES = 'Y' */
20077: ----------------------------------------------------------------------------
20078: -- Cursor to get rows to be copied from:
20079: -- OKC_K_LINES_B_TEMP
20080: -- OKS_K_LINES_B
20081: -- OKC_K_ITEMS
20082: -- for a specified Source Contract and for the lines chosen by the user for copy.
20083:
20083:
20084: CURSOR GetOKCOKSLinesB_Temp(P_From_Chr_ID IN NUMBER
20085: ,P_To_Chr_ID IN NUMBER) IS
20086: SELECT
20087: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20088: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20089: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20090: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20091: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
20229: ,okcBT.CANCELLED_AMOUNT)
20230: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20231: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20232: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20233: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20234: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20235: ,oksB.ID AS OksB_OldOksLineID
20236: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20237: ,oksB.CLE_ID AS OksB_CLE_ID
20379: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
20380: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
20381: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20382: FROM
20383: OKC_K_LINES_B_TEMP okcBT
20384: ,OKS_K_LINES_B oksB
20385: ,OKC_K_ITEMS okcI
20386: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20387: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20391:
20392: /*IF p_include_cancelled_lines = 'Y' AND p_include_terminated_lines = 'N' */
20393: ----------------------------------------------------------------------------
20394: -- Cursor to get rows to be copied from:
20395: -- OKC_K_LINES_B_TEMP
20396: -- OKS_K_LINES_B
20397: -- OKC_K_ITEMS
20398: -- for a specified Source Contract and for the lines chosen by the user for copy.
20399:
20399:
20400: CURSOR GetOKCOKSLinesB_Temp_CANCEL(P_From_Chr_ID IN NUMBER
20401: ,P_To_Chr_ID IN NUMBER) IS
20402: SELECT
20403: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20404: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20405: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20406: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20407: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
20545: ,okcBT.CANCELLED_AMOUNT)
20546: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20547: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20548: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20549: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20550: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20551: ,oksB.ID AS OksB_OldOksLineID
20552: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20553: ,oksB.CLE_ID AS OksB_CLE_ID
20695: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
20696: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
20697: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
20698: FROM
20699: OKC_K_LINES_B_TEMP okcBT
20700: ,OKS_K_LINES_B oksB
20701: ,OKC_K_ITEMS okcI
20702: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
20703: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
20708:
20709: /*if p_include_cancelled_lines= 'N' AND p_include_terminated_lines = 'Y' */
20710: ----------------------------------------------------------------------------
20711: -- Cursor to get rows to be copied from:
20712: -- OKC_K_LINES_B_TEMP
20713: -- OKS_K_LINES_B
20714: -- OKC_K_ITEMS
20715: -- for a specified Source Contract and for the lines chosen by the user for copy.
20716:
20716:
20717: CURSOR GetOKCOKSLinesB_Temp_TERMN(P_From_Chr_ID IN NUMBER
20718: ,P_To_Chr_ID IN NUMBER) IS
20719: SELECT
20720: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
20721: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
20722: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
20723: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
20724: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
20862: ,okcBT.CANCELLED_AMOUNT)
20863: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
20864: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
20865: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
20866: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
20867: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
20868: ,oksB.ID AS OksB_OldOksLineID
20869: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
20870: ,oksB.CLE_ID AS OksB_CLE_ID
21012: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
21013: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
21014: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21015: FROM
21016: OKC_K_LINES_B_TEMP okcBT
21017: ,OKS_K_LINES_B oksB
21018: ,OKC_K_ITEMS okcI
21019: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21020: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21025:
21026: /*if p_include_cancelled_lines = 'N' AND p_include_terminated_lines = 'N'*/
21027: ----------------------------------------------------------------------------
21028: -- Cursor to get rows to be copied from:
21029: -- OKC_K_LINES_B_TEMP
21030: -- OKS_K_LINES_B
21031: -- OKC_K_ITEMS
21032: -- for a specified Source Contract and for the lines chosen by the user for copy.
21033:
21033:
21034: CURSOR GetOKCOKSLinesB_Temp_EXCLUDE(P_From_Chr_ID IN NUMBER
21035: ,P_To_Chr_ID IN NUMBER) IS
21036: SELECT
21037: ------------------------------------------Begin of OKC_K_LINES_B columns---------------------
21038: okcBT.ORIG_SYSTEM_ID1 AS OkcBT_OldOKCLineID
21039: ,okcBT.ID AS OkcBT_NewOKCLineID --Target Value
21040: ,okcBT.LINE_NUMBER AS OkcBT_LINE_NUMBER
21041: ,okcBT.CHR_ID AS OkcBT_NewChrID--Target Value
21179: ,okcBT.CANCELLED_AMOUNT)
21180: ) AS OkcBT_CANCELLED_AMOUNT -- As part of fixing 4641561,Added decode for 4747648
21181: ,decode(okcBT.DATE_CANCELLED,NULL,'N','Y') AS OkcBT_LINE_CANCELLED_FLAG --pseudo column
21182: ,decode(okcBT.DATE_TERMINATED,NULL,'N','Y') AS OkcBT_LINE_TERMINATED_FLAG --pseudo column
21183: ---------------------------------------End of OKC_K_LINES_B columns------------------------------------
21184: ---------------------------------------Begin of OKS_K_LINES_B columns----------------------------------
21185: ,oksB.ID AS OksB_OldOksLineID
21186: ,To_number(rawtohex(sys_guid()), HexFormatStr) AS OksB_NewOksLineID
21187: ,oksB.CLE_ID AS OksB_CLE_ID
21329: ,okcI.PROGRAM_ID AS OkcI_PROGRAM_ID
21330: ,okcI.PROGRAM_UPDATE_DATE AS OkcI_PROGRAM_UPDATE_DATE
21331: ,okcI.REQUEST_ID AS OkcI_REQUEST_ID
21332: FROM
21333: OKC_K_LINES_B_TEMP okcBT
21334: ,OKS_K_LINES_B oksB
21335: ,OKC_K_ITEMS okcI
21336: Where okcBT.DNZ_CHR_ID = P_To_Chr_ID
21337: AND okcBT.ORIG_SYSTEM_ID1 = oksB.CLE_ID(+)
21445:
21446: End If; -- End of check for P_Lines_Tbl.COUNT to be > 0
21447: --------------------------------------------------------------------------------------
21448:
21449: ------------------------Start of BULK INSERT INTO OKC_K_LINES_B_TEMP-----------------
21450: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21451: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21452:
21453: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21447: --------------------------------------------------------------------------------------
21448:
21449: ------------------------Start of BULK INSERT INTO OKC_K_LINES_B_TEMP-----------------
21450: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21451: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21452:
21453: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21454: ------OKC_K_LINES_B_TEMP global temporary table----------------------------------------
21455: -- a) Bulk insert for all lines in ApiLinesRecTab where TO_LINE is NULL and LINE_EXP_YN = 'Y'
21450: IF (P_Lines_Tbl.COUNT > 0) then ---++++++++++++Start of Check of P_Lines_Tbl.COUNT for
21451: ---++++++++++++Bulk insert into OKC_K_LINES_B_TEMP
21452:
21453: ------Loop through PLSQL Record of tables of the lines to do bulk inserts into--------
21454: ------OKC_K_LINES_B_TEMP global temporary table----------------------------------------
21455: -- a) Bulk insert for all lines in ApiLinesRecTab where TO_LINE is NULL and LINE_EXP_YN = 'Y'
21456: -- (Indicates that line being copied is a top line and that this line and all its sublines
21457: -- need to be queried explicitly from the database and copied)
21458: --
21459:
21460: Begin
21461:
21462: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
21463: INSERT INTO OKC_K_LINES_B_TEMP(
21464: ID
21465: ,LINE_NUMBER
21466: ,CHR_ID
21467: ,CLE_ID
21678: END
21679: ) AS okcB_DATES_CHANGED_YN
21680: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
21681: FROM
21682: OKC_K_LINES_B okcB
21683: WHERE ApiLinesRecTab.ToLineTab(i) IS NULL
21684: AND ApiLinesRecTab.LineExpYNTab(i) = 'Y'
21685: AND okcB.DNZ_CHR_ID = P_From_Chr_ID
21686: CONNECT BY PRIOR okcB.ID = okcB.CLE_ID
21690: WHEN OTHERS THEN
21691: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
21692: FND_LOG.string(FND_LOG.level_unexpected
21693: ,l_module_name
21694: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : a) :'||SQLERRM);
21695: End If;
21696:
21697: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
21698: l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
21707: --
21708:
21709: Begin
21710: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
21711: INSERT INTO OKC_K_LINES_B_TEMP(
21712: ID
21713: ,LINE_NUMBER
21714: ,CHR_ID
21715: ,CLE_ID
21926: END
21927: ) AS okcB_DATES_CHANGED_YN
21928: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
21929: FROM
21930: OKC_K_LINES_B okcB
21931: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
21932: AND ApiLinesRecTab.ToLineTab(i) IS NULL
21933: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
21934: AND okcB.DNZ_CHR_ID = P_From_Chr_ID
22052: END
22053: ) AS okcCL_DATES_CHANGED_YN
22054: ,'N' AS okcCL_GEN_SUBLINE_BSCH_YN
22055: FROM
22056: OKC_K_LINES_B okcCL
22057: WHERE
22058: okcCL.DNZ_CHR_ID = P_From_Chr_ID
22059: AND ApiLinesRecTab.ToLineTab(i) IS NULL
22060: AND ApiLinesRecTab.LineExpYNTab(i) = 'N'
22069: WHEN OTHERS THEN
22070: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22071: FND_LOG.string(FND_LOG.level_unexpected
22072: ,l_module_name
22073: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : b) :'||SQLERRM);
22074: End If;
22075:
22076:
22077: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
22089:
22090: Begin
22091:
22092: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
22093: INSERT INTO OKC_K_LINES_B_TEMP(
22094: ID
22095: ,LINE_NUMBER
22096: ,CHR_ID
22097: ,CLE_ID
22308: END
22309: ) AS okcB_DATES_CHANGED_YN
22310: ,'N' AS okcB_GEN_SUBLINE_BSCH_YN
22311: FROM
22312: OKC_K_LINES_B okcB
22313: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
22314: AND ApiLinesRecTab.ToLineTab(i) IS NOT NULL
22315: AND ApiLinesRecTab.LineExistsYNTab(i) = 'N'
22316: AND okcB.DNZ_CHR_ID = P_From_Chr_ID;
22320:
22321: /* IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
22322: FND_LOG.string(FND_LOG.level_unexpected
22323: ,l_module_name
22324: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : c) :'||SQLERRM);
22325: END IF; */
22326:
22327:
22328:
22339:
22340: Begin
22341:
22342: FORALL i in ApiLinesRecTab.IDTab.FIRST .. ApiLinesRecTab.IDTab.LAST
22343: INSERT INTO OKC_K_LINES_B_TEMP(
22344: ID
22345: ,LINE_NUMBER
22346: ,CHR_ID
22347: ,CLE_ID
22470: ,okcB.DATE_TERMINATED AS okcB_DATE_TERMINATED
22471: ,(CASE
22472: WHEN (
22473: (nvl(okcB.START_DATE,sysdate) BETWEEN
22474: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22475: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22476: )
22477: AND
22478: (nvl(okcB.END_DATE,sysdate) BETWEEN
22471: ,(CASE
22472: WHEN (
22473: (nvl(okcB.START_DATE,sysdate) BETWEEN
22474: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22475: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22476: )
22477: AND
22478: (nvl(okcB.END_DATE,sysdate) BETWEEN
22479: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22475: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22476: )
22477: AND
22478: (nvl(okcB.END_DATE,sysdate) BETWEEN
22479: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22480: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22481: )
22482: )
22483: THEN nvl(okcB.START_DATE,sysdate)
22476: )
22477: AND
22478: (nvl(okcB.END_DATE,sysdate) BETWEEN
22479: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22480: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22481: )
22482: )
22483: THEN nvl(okcB.START_DATE,sysdate)
22484: ELSE (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22480: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22481: )
22482: )
22483: THEN nvl(okcB.START_DATE,sysdate)
22484: ELSE (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22485: END
22486: ) AS okcB_START_DATE --okcB.START_DATE AS okcB_START_DATE
22487: ,(CASE
22488: WHEN (
22486: ) AS okcB_START_DATE --okcB.START_DATE AS okcB_START_DATE
22487: ,(CASE
22488: WHEN (
22489: (nvl(okcB.START_DATE,sysdate) BETWEEN
22490: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22491: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22492: )
22493: AND
22494: (nvl(okcB.END_DATE,sysdate) BETWEEN
22487: ,(CASE
22488: WHEN (
22489: (nvl(okcB.START_DATE,sysdate) BETWEEN
22490: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22491: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22492: )
22493: AND
22494: (nvl(okcB.END_DATE,sysdate) BETWEEN
22495: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22491: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22492: )
22493: AND
22494: (nvl(okcB.END_DATE,sysdate) BETWEEN
22495: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22496: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22497: )
22498: )
22499: THEN nvl(okcB.END_DATE,sysdate)
22492: )
22493: AND
22494: (nvl(okcB.END_DATE,sysdate) BETWEEN
22495: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22496: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22497: )
22498: )
22499: THEN nvl(okcB.END_DATE,sysdate)
22500: ELSE (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22496: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22497: )
22498: )
22499: THEN nvl(okcB.END_DATE,sysdate)
22500: ELSE (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22501: END
22502: ) AS okcB_END_DATE --okcB.END_DATE AS okcB_END_DATE
22503: ,okcB.ATTRIBUTE_CATEGORY AS okcB_ATTRIBUTE_CATEGORY
22504: ,okcB.ATTRIBUTE1 AS okcB_ATTRIBUTE1
22568: ,nvl(ApiLinesRecTab.LineExistsYNTab(i),'N')
22569: ,(CASE
22570: WHEN (
22571: (nvl(okcB.START_DATE,sysdate) BETWEEN
22572: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22573: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22574: AND
22575: (nvl(okcB.END_DATE,sysdate) BETWEEN
22576: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22569: ,(CASE
22570: WHEN (
22571: (nvl(okcB.START_DATE,sysdate) BETWEEN
22572: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22573: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22574: AND
22575: (nvl(okcB.END_DATE,sysdate) BETWEEN
22576: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22577: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22572: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22573: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22574: AND
22575: (nvl(okcB.END_DATE,sysdate) BETWEEN
22576: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22577: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22578: )
22579: THEN 'N'
22580: ELSE 'Y'
22573: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22574: AND
22575: (nvl(okcB.END_DATE,sysdate) BETWEEN
22576: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22577: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i)))
22578: )
22579: THEN 'N'
22580: ELSE 'Y'
22581: END
22583: ,(CASE
22584: WHEN (
22585: (
22586: (nvl(okcB.START_DATE,sysdate) BETWEEN
22587: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22588: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22589: )
22590: AND
22591: (nvl(okcB.END_DATE,sysdate) BETWEEN
22584: WHEN (
22585: (
22586: (nvl(okcB.START_DATE,sysdate) BETWEEN
22587: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22588: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22589: )
22590: AND
22591: (nvl(okcB.END_DATE,sysdate) BETWEEN
22592: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22588: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22589: )
22590: AND
22591: (nvl(okcB.END_DATE,sysdate) BETWEEN
22592: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22593: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22594: )
22595: )
22596: AND okcB.DATE_TERMINATED IS NULL
22589: )
22590: AND
22591: (nvl(okcB.END_DATE,sysdate) BETWEEN
22592: (select nvl(b.START_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22593: AND (select nvl(b.END_DATE,sysdate) from OKC_K_LINES_B b where b.ID = ApiLinesRecTab.ToLineTab(i))
22594: )
22595: )
22596: AND okcB.DATE_TERMINATED IS NULL
22597: )
22599: ELSE 'Y'
22600: END
22601: ) AS okcB_GEN_SUBLINE_BSCH_YN
22602: FROM
22603: OKC_K_LINES_B okcB
22604: WHERE okcB.ID = ApiLinesRecTab.IDTab(i)
22605: AND ApiLinesRecTab.ToLineTab(i) IS NOT NULL
22606: AND ApiLinesRecTab.LineExistsYNTab(i) = 'Y'
22607: AND okcB.DNZ_CHR_ID = P_From_Chr_ID;
22611: /*
22612: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22613: FND_LOG.string(FND_LOG.level_unexpected
22614: ,l_module_name
22615: ,'During Bulk Insert into OKC_K_LINES_B_TEMP : d) :'||SQLERRM);
22616: End If;
22617: */
22618:
22619:
22624:
22625: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22626: FND_LOG.string(FND_LOG.level_statement
22627: ,l_module_name
22628: ,'After Bulk Insert into OKC_K_LINES_B_TEMP'
22629: );
22630: End If;
22631:
22632: -----------------------------------------------------------------------------
22629: );
22630: End If;
22631:
22632: -----------------------------------------------------------------------------
22633: -----Update of OKC_K_LINES_B_TEMP to set CLE_ID to point to the new IDs of
22634: -----Target lines to be created
22635: printTemptable;
22636:
22637: Begin
22634: -----Target lines to be created
22635: printTemptable;
22636:
22637: Begin
22638: UPDATE OKC_K_LINES_B_TEMP a
22639: SET a.CLE_ID = (select b.ID
22640: FROM OKC_K_LINES_B_TEMP b
22641: WHERE
22642: b.ORIG_SYSTEM_ID1 = a.CLE_ID
22636:
22637: Begin
22638: UPDATE OKC_K_LINES_B_TEMP a
22639: SET a.CLE_ID = (select b.ID
22640: FROM OKC_K_LINES_B_TEMP b
22641: WHERE
22642: b.ORIG_SYSTEM_ID1 = a.CLE_ID
22643: AND b.DNZ_CHR_ID = P_To_Chr_ID)
22644: WHERE
22649: WHEN OTHERS THEN
22650: If (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) then
22651: FND_LOG.string(FND_LOG.level_unexpected
22652: ,l_module_name
22653: ,'During update of cle_id in OKC_K_LINES_B_TEMP :'
22654: ||SQLERRM
22655: );
22656: End If;
22657: OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
22677: ------------------------------------------------------------------------------------------------------
22678: ------------------------------------------------------------------------------
22679:
22680: END IF; -----End of Check of P_Lines_Tbl.COUNT > 0+++++++++++++++++++++++
22681: -----for insert into OKC_K_LINES_B_TEMP +++++++++++++++++++++++++
22682:
22683: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
22684:
22685:
22679:
22680: END IF; -----End of Check of P_Lines_Tbl.COUNT > 0+++++++++++++++++++++++
22681: -----for insert into OKC_K_LINES_B_TEMP +++++++++++++++++++++++++
22682:
22683: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
22684:
22685:
22686: -----------------------------------------------------------------------------
22687: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22683: ---=============================-End of BULK INSERT INTO OKC_K_LINES_B_TEMP===================
22684:
22685:
22686: -----------------------------------------------------------------------------
22687: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22688: --OKS_K_LINES_B and OKC_K_ITEMS
22689: ----------------------------------------------------------------------------
22690: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22691: ---for bulk insert into OKC_K_LINES_B, *****************
22687: --Process rows in OKC_K_LINES_B_TEMP and do bulk inserts into OKC_K_LINES_B
22688: --OKS_K_LINES_B and OKC_K_ITEMS
22689: ----------------------------------------------------------------------------
22690: IF (P_Lines_Tbl.COUNT > 0) then ---Start of Check of P_Lines_Tbl.COUNT > 0 *****************
22691: ---for bulk insert into OKC_K_LINES_B, *****************
22692: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
22693:
22694: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22695: FND_LOG.string(FND_LOG.level_statement
22693:
22694: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
22695: FND_LOG.string(FND_LOG.level_statement
22696: ,l_module_name
22697: ,'Start of BULK FETCH from OKC_K_LINES_B_TEMP'
22698: ||'Count of Lines in P_Lines_Tbl='||P_Lines_Tbl.COUNT
22699: );
22700: End If;
22701:
22699: );
22700: End If;
22701:
22702:
22703: --------Start of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
22704: Begin
22705:
22706:
22707: IF p_include_cancelled_lines = 'Y' AND
22810: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
22811: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
22812: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
22813: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
22814: -----------------End of record members for OKC_K_LINES_B columns
22815: -----------------Start of record members for OKS_K_LINES_B columns
22816: ,OKCOKSLinesRecTab.OksB_OldOksLineID
22817: ,OKCOKSLinesRecTab.OksB_NewOksLineID
22818: ,OKCOKSLinesRecTab.OksB_CLE_ID
23022: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
23023: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23024: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23025: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23026: -----------------End of record members for OKC_K_LINES_B columns
23027: -----------------Start of record members for OKS_K_LINES_B columns
23028: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23029: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23030: ,OKCOKSLinesRecTab.OksB_CLE_ID
23234: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
23235: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23236: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23237: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23238: -----------------End of record members for OKC_K_LINES_B columns
23239: -----------------Start of record members for OKS_K_LINES_B columns
23240: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23241: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23242: ,OKCOKSLinesRecTab.OksB_CLE_ID
23446: ,OKCOKSLinesRecTab.OkcB_PAYMENT_INSTRUCTION_TYPE
23447: ,OKCOKSLinesRecTab.OkcB_CANCELLED_AMOUNT
23448: ,OKCOKSLinesRecTab.OkcB_LINE_CANCELLED_FLAG --pseudo column
23449: ,OKCOKSLinesRecTab.OkcB_LINE_TERMINATED_FLAG --pseudo column
23450: -----------------End of record members for OKC_K_LINES_B columns
23451: -----------------Start of record members for OKS_K_LINES_B columns
23452: ,OKCOKSLinesRecTab.OksB_OldOksLineID
23453: ,OKCOKSLinesRecTab.OksB_NewOksLineID
23454: ,OKCOKSLinesRecTab.OksB_CLE_ID
23567: ,OKCOKSLinesRecTab.OkcI_PROGRAM_UPDATE_DATE
23568: ,OKCOKSLinesRecTab.OkcI_REQUEST_ID LIMIT G_BULK_FETCH_LIMIT;
23569: END IF;
23570:
23571: ---Bulk insert into OKC_K_LINES_B---
23572:
23573: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23574: INSERT INTO OKC_K_LINES_B(
23575: ID
23570:
23571: ---Bulk insert into OKC_K_LINES_B---
23572:
23573: FORALL i IN OKCOKSLinesRecTab.OkcB_NewOKCLineID.FIRST .. OKCOKSLinesRecTab.OkcB_NewOKCLineID.LAST
23574: INSERT INTO OKC_K_LINES_B(
23575: ID
23576: , LINE_NUMBER
23577: , CHR_ID
23578: , CLE_ID
23774:
23775: If (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) then
23776: FND_LOG.string(FND_LOG.level_statement
23777: ,l_module_name
23778: ,'After BULK INSERT into OKC_K_LINES_B'
23779: );
23780: End If;
23781:
23782:
24409: CLOSE GetOKCOKSLinesB_Temp_exclude;
24410: END IF;
24411:
24412: End;
24413: --------End of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
24414:
24415: END IF; ---End of Check of P_Lines_Tbl.COUNT > 0 *****************
24416: ---for bulk insert into OKC_K_LINES_B, *****************
24417: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24412: End;
24413: --------End of BULK FETCH FROM OKC_K_LINES_B_TEMP---------------
24414:
24415: END IF; ---End of Check of P_Lines_Tbl.COUNT > 0 *****************
24416: ---for bulk insert into OKC_K_LINES_B, *****************
24417: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24418:
24419:
24420: ----------------------------------------------------------------------------
24417: ---OKS_K_LINES_B,OKC_K_ITEMS *****************
24418:
24419:
24420: ----------------------------------------------------------------------------
24421: -- Update of OKC_K_LINES_B
24422: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24418:
24419:
24420: ----------------------------------------------------------------------------
24421: -- Update of OKC_K_LINES_B
24422: -- ii) PRICE_NEGOTIATED on OKC_K_LINES_B for toplines
24423: --
24424: -- Update of OKS_K_LINES_B
24425: -- iii) COVERAGE_ID on OKS_K_LINES_B for toplines with STANDARD_COV_YN = 'N'
24426: -- iv) TAX_AMOUNT on OKS_K_LINES_B for all toplines
24427: -- v) PRICE_UOM on OKS_K_LINES_B as per Partial Period uptake rules, when copying to an existing Target Contract
24428: ----------------------------------------------------------------------------
24429: Begin
24430:
24431: UPDATE OKC_K_LINES_B a
24432: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
24433: from OKC_K_LINES_B b
24434: where b.CLE_ID = a.ID
24435: AND b.DNZ_CHR_ID = P_To_Chr_ID
24429: Begin
24430:
24431: UPDATE OKC_K_LINES_B a
24432: SET a.PRICE_NEGOTIATED = (select sum(b.PRICE_NEGOTIATED)
24433: from OKC_K_LINES_B b
24434: where b.CLE_ID = a.ID
24435: AND b.DNZ_CHR_ID = P_To_Chr_ID
24436: AND b.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24437: WHERE
24442:
24443: UPDATE OKS_K_LINES_B a
24444: SET a.COVERAGE_ID = (select b.ID
24445: FROM
24446: OKC_K_LINES_B b
24447: ,OKC_K_LINES_B_TEMP bt
24448: WHERE
24449: b.ID = bt.ID
24450: AND b.CLE_ID = a.CLE_ID
24443: UPDATE OKS_K_LINES_B a
24444: SET a.COVERAGE_ID = (select b.ID
24445: FROM
24446: OKC_K_LINES_B b
24447: ,OKC_K_LINES_B_TEMP bt
24448: WHERE
24449: b.ID = bt.ID
24450: AND b.CLE_ID = a.CLE_ID
24451: AND b.DNZ_CHR_ID = P_To_Chr_ID
24451: AND b.DNZ_CHR_ID = P_To_Chr_ID
24452: AND b.LSE_ID in (2,20))
24453: WHERE a.DNZ_CHR_ID = P_To_Chr_ID
24454: AND a.STANDARD_COV_YN = 'N'
24455: AND a.CLE_ID in (SELECT b.ID from OKC_K_LINES_B_TEMP b where b.LINE_EXISTS_YN = 'N'
24456: and b.LSE_ID in (1,19));
24457:
24458:
24459: UPDATE OKS_K_LINES_B a
24459: UPDATE OKS_K_LINES_B a
24460: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24461: FROM
24462: OKS_K_LINES_B b
24463: ,OKC_K_LINES_B c
24464: WHERE
24465: c.ID = b.CLE_ID
24466: AND c.cle_id = a.cle_id
24467: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24467: AND c.DATE_CANCELLED IS NULL) --Cancelled sublines shouldn't be included in sum
24468: WHERE a.dnz_chr_id = P_To_Chr_ID
24469: and a.CLE_ID = (select b.id
24470: FROM
24471: OKC_K_LINES_B b
24472: WHERE b.cle_id is NULL
24473: AND b.id = a.cle_id
24474: AND b.LSE_ID <> 46); --Subscription Lines do not have sublines. So they shouldnt be updated.
24475:
24486:
24487: --Update price_uom of copied top lines and their sublines
24488: UPDATE OKS_K_LINES_B a
24489: SET a.PRICE_UOM = l_Target_Hdr_Price_UOM
24490: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24491: where
24492: (bt.cle_id is null --Top Line
24493: OR
24494: (bt.cle_id is not null
24504:
24505: --Update of PRICE_UOM of Sublines that were copied to existing Top lines.
24506: UPDATE OKS_K_LINES_B a
24507: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24508: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24509: where c.id = a.CLE_ID)
24510: )
24511: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24512: where bt.CLE_ID IS NOT NULL
24507: SET a.PRICE_UOM = (select b.price_uom from oks_k_lines_b b
24508: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24509: where c.id = a.CLE_ID)
24510: )
24511: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24512: where bt.CLE_ID IS NOT NULL
24513: and bt.LINE_EXISTS_YN = 'Y'
24514: -- hkamdar 05-Oct-2006. Bug# 5361663. Removing Customer, Party and Site Covered levels
24515: -- as the PRICE_UOM should be NULL for them.
24523: copied Top Lines and its sublines will be same as that of the Source Top Line/Sublines.
24524: --Update of PRICE_UOM on copied Top Lines and their Sublines
24525: UPDATE OKS_K_LINES_B a
24526: SET a.PRICE_UOM = nvl(l_Target_Hdr_Price_UOM,a.PRICE_UOM)
24527: WHERE a.CLE_ID IN (select bt.id from okc_k_lines_b_temp bt
24528: where
24529: (bt.cle_id is null --Top Line
24530: OR
24531: (bt.cle_id is not null
24539: --Update PRICE_UOM of Sub Lines that were copied to existing top lines
24540: UPDATE OKS_K_LINES_B a
24541: SET a.PRICE_UOM = nvl(
24542: (select b.price_uom from oks_k_lines_b b
24543: where b.cle_id = (select c.cle_id from okc_k_lines_b c
24544: where c.id = a.CLE_ID)
24545: )
24546: ,a.PRICE_UOM
24547: )
24544: where c.id = a.CLE_ID)
24545: )
24546: ,a.PRICE_UOM
24547: )
24548: WHERE a.CLE_ID IN (select bt.ID from okc_k_lines_b_temp bt
24549: where bt.CLE_ID IS NOT NULL
24550: and bt.LINE_EXISTS_YN = 'Y'
24551: -- hkamdar 05-Oct-2006. Bug# 5361663. Removing Customer, Party and Site Covered levels
24552: -- as the PRICE_UOM should be NULL for them.
24562: End;
24563: --------------------------------------------------------------------------------
24564: --------------------------------------------------------------------------------
24565: --Update of OKC_K_HEADERS_B.ESTIMATED_AMOUNT and OKS_K_HEADERS_B.TAX_AMOUNT to equal
24566: --sum of OKC_K_LINES_B.PRICE_NEGOTIATED of all top lines and OKS_K_LINES_B.TAX_AMOUNT
24567: --of all top lines respectively
24568: Begin
24569: UPDATE OKC_K_HEADERS_B a
24570: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
24567: --of all top lines respectively
24568: Begin
24569: UPDATE OKC_K_HEADERS_B a
24570: SET a.ESTIMATED_AMOUNT = (select sum(b.PRICE_NEGOTIATED)
24571: FROM OKC_K_LINES_B b
24572: where b.DNZ_CHR_ID = a.ID
24573: and b.CLE_ID IS NULL
24574: AND b.DATE_CANCELLED IS NULL) --Cancelled toplines shouldn't be included in sum
24575: WHERE a.ID = P_To_Chr_ID;
24577: UPDATE OKS_K_HEADERS_B a
24578: SET a.TAX_AMOUNT = (select sum(b.TAX_AMOUNT)
24579: FROM
24580: OKS_K_LINES_B b
24581: ,OKC_K_LINES_B c
24582: where b.DNZ_CHR_ID = a.CHR_ID
24583: and b.CLE_ID = c.ID
24584: and c.CLE_ID IS NULL
24585: and c.DATE_CANCELLED IS NULL) -- Cancelled toplines shouldn't be included in sum
24587:
24588: End;
24589: --------------------------------------------------------------------------------
24590:
24591: -------------Generate new line numbers in OKC_K_LINES_B for newly created lines-----
24592: IF (P_Lines_Tbl.COUNT > 0) then
24593: Begin
24594: GenerateNewLineNumber(P_To_Chr_ID => P_To_Chr_ID
24595: ,P_Partial_Copy_YN => 'Y'
26052: ,renewal_po_used = NULL
26053: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26054: WHERE CHR_ID = P_To_Chr_ID;
26055:
26056: --Update OKC_K_LINES_B--
26057: l_stage := 'Updating OKC_K_LINES_B';
26058: UPDATE OKC_K_LINES_B
26059: SET
26060: cust_acct_id = NULL
26053: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26054: WHERE CHR_ID = P_To_Chr_ID;
26055:
26056: --Update OKC_K_LINES_B--
26057: l_stage := 'Updating OKC_K_LINES_B';
26058: UPDATE OKC_K_LINES_B
26059: SET
26060: cust_acct_id = NULL
26061: ,bill_to_site_use_id = NULL
26054: WHERE CHR_ID = P_To_Chr_ID;
26055:
26056: --Update OKC_K_LINES_B--
26057: l_stage := 'Updating OKC_K_LINES_B';
26058: UPDATE OKC_K_LINES_B
26059: SET
26060: cust_acct_id = NULL
26061: ,bill_to_site_use_id = NULL
26062: ,ship_to_site_use_id = NULL
26081: ,CC_AUTH_CODE = null
26082: ,TRXN_EXTENSION_ID = null
26083: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26084: WHERE DNZ_CHR_ID = P_To_Chr_ID
26085: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL);
26086:
26087: --Delete line level party contacts--
26088: l_stage := 'Deleting from OKC_CONTACTS';
26089: DELETE from okc_contacts
26111: CLOSE l_party_comp_csr;
26112:
26113: IF l_party_comp_count > 0 THEN
26114: Begin
26115: --Update OKC_K_LINES_B--
26116: l_stage := 'Updating OKC_K_LINES_B';
26117: UPDATE OKC_K_LINES_B
26118: SET
26119: cust_acct_id = NULL
26112:
26113: IF l_party_comp_count > 0 THEN
26114: Begin
26115: --Update OKC_K_LINES_B--
26116: l_stage := 'Updating OKC_K_LINES_B';
26117: UPDATE OKC_K_LINES_B
26118: SET
26119: cust_acct_id = NULL
26120: ,bill_to_site_use_id = NULL
26113: IF l_party_comp_count > 0 THEN
26114: Begin
26115: --Update OKC_K_LINES_B--
26116: l_stage := 'Updating OKC_K_LINES_B';
26117: UPDATE OKC_K_LINES_B
26118: SET
26119: cust_acct_id = NULL
26120: ,bill_to_site_use_id = NULL
26121: ,ship_to_site_use_id = NULL
26122: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26123: ,PAYMENT_INSTRUCTION_TYPE = NULL
26124: WHERE DNZ_CHR_ID = P_To_Chr_ID
26125: AND CLE_ID IS NULL
26126: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP);
26127:
26128: --Update OKS_K_LINES_B--
26129: l_stage := 'Updating OKS_K_LINES_B';
26130: UPDATE OKS_K_LINES_B
26139: ,CC_AUTH_CODE = null
26140: ,TRXN_EXTENSION_ID = null
26141: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26142: WHERE DNZ_CHR_ID = P_To_Chr_ID
26143: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26144: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26145:
26146: --Delete line level party contacts--
26147: l_stage := 'Deleting from OKC_CONTACTS';
26140: ,TRXN_EXTENSION_ID = null
26141: ,object_version_number = decode(P_Target_Contract_New_YN,'Y',object_version_number,object_version_number+1)
26142: WHERE DNZ_CHR_ID = P_To_Chr_ID
26143: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26144: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26145:
26146: --Delete line level party contacts--
26147: l_stage := 'Deleting from OKC_CONTACTS';
26148: DELETE from okc_contacts
26147: l_stage := 'Deleting from OKC_CONTACTS';
26148: DELETE from okc_contacts
26149: where cpl_id in (SELECT ID from OKC_K_PARTY_ROLES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
26150: and CLE_ID is NOT NULL
26151: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26152: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP)));
26153:
26154: --Delete line level party roles--
26155: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26148: DELETE from okc_contacts
26149: where cpl_id in (SELECT ID from OKC_K_PARTY_ROLES_B WHERE DNZ_CHR_ID = P_To_Chr_ID
26150: and CLE_ID is NOT NULL
26151: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26152: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP)));
26153:
26154: --Delete line level party roles--
26155: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26156: DELETE from okc_k_party_roles_b
26155: l_stage := 'Deleting from OKC_K_PARTY_ROLES_B';
26156: DELETE from okc_k_party_roles_b
26157: where dnz_chr_id = P_To_Chr_ID
26158: and cle_id is not null
26159: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26160: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26161: End;
26162: END IF;
26163: --end npalepu
26156: DELETE from okc_k_party_roles_b
26157: where dnz_chr_id = P_To_Chr_ID
26158: and cle_id is not null
26159: AND CLE_ID in (select ID from okc_k_lines_b where DNZ_CHR_ID = P_To_Chr_ID and CLE_ID IS NULL
26160: AND id in (SELECT ID FROM OKC_K_LINES_B_TEMP));
26161: End;
26162: END IF;
26163: --end npalepu
26164: End If;
26176:
26177: END Party_Cleanup;
26178:
26179: --==============================================================================
26180: --Procedure PURGE_OKC_K_LINES_B_TEMP
26181: --==============================================================================
26182: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26183: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26184: l_schema_name VARCHAR2(3) := 'OKC';
26178:
26179: --==============================================================================
26180: --Procedure PURGE_OKC_K_LINES_B_TEMP
26181: --==============================================================================
26182: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26183: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26184: l_schema_name VARCHAR2(3) := 'OKC';
26185: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26186: ||l_schema_name
26179: --==============================================================================
26180: --Procedure PURGE_OKC_K_LINES_B_TEMP
26181: --==============================================================================
26182: Procedure PURGE_OKC_K_LINES_B_TEMP IS PRAGMA AUTONOMOUS_TRANSACTION;
26183: l_api_name VARCHAR2(30) := 'PURGE_OKC_K_LINES_B_TEMP';
26184: l_schema_name VARCHAR2(3) := 'OKC';
26185: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26186: ||l_schema_name
26187: ||'.'
26184: l_schema_name VARCHAR2(3) := 'OKC';
26185: l_statement VARCHAR2(200) := 'TRUNCATE TABLE '
26186: ||l_schema_name
26187: ||'.'
26188: ||'OKC_K_LINES_B_TEMP';
26189:
26190: Begin
26191: EXECUTE IMMEDIATE l_statement;
26192: EXCEPTION
26193: WHEN OTHERS THEN
26194: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, substr(SQLERRM,1,240));
26195: RAISE;
26196:
26197: End PURGE_OKC_K_LINES_B_TEMP;
26198:
26199: --=====================================================================================
26200: --Procedure COPY_COMPONENTS
26201: --====================================================================================
26281: SELECT
26282: ID as NEW_LINE_ID
26283: ,ORIG_SYSTEM_ID1 as OLD_LINE_ID
26284: FROM
26285: OKC_K_LINES_B_TEMP
26286: where
26287: CLE_ID IS NULL;
26288:
26289: l_old_line_id number;
26863: End;
26864:
26865: --Purge Global Temporary Table when called from Update Service
26866: --This is to support splitting of line into more than 2 lines
26867: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
26868: --multiple rows with same orig_system_id1
26869: Begin
26870: /*delete from okc_k_lines_b_temp;*/
26871: PURGE_OKC_K_LINES_B_TEMP;
26866: --This is to support splitting of line into more than 2 lines
26867: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
26868: --multiple rows with same orig_system_id1
26869: Begin
26870: /*delete from okc_k_lines_b_temp;*/
26871: PURGE_OKC_K_LINES_B_TEMP;
26872: End;
26873:
26874: END IF;
26867: --so that Update of CLE_ID in OKC_K_LINES_B_TEMP doesn't run into
26868: --multiple rows with same orig_system_id1
26869: Begin
26870: /*delete from okc_k_lines_b_temp;*/
26871: PURGE_OKC_K_LINES_B_TEMP;
26872: End;
26873:
26874: END IF;
26875: