[Home] [Help]
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;
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))
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;
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;
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:
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
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:
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
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;
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
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;
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
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
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:
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
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;
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:
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)
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)
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:
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: )
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;
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:
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:
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:
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:
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
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
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
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
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, *****************
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
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 *****************
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
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
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
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.
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
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
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
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: