[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_okc_header
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_okc_header';
INSERT
INTO OKC_K_HEADERS_ALL_B
(ID
,CONTRACT_NUMBER
,AUTHORING_ORG_ID
,CONTRACT_NUMBER_MODIFIER
,INV_ORGANIZATION_ID
,STS_CODE
,QCL_ID
,SCS_CODE
,TRN_CODE
,CURRENCY_CODE
,ARCHIVED_YN
,DELETED_YN
,TEMPLATE_YN
,CHR_TYPE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,CUST_PO_NUMBER_REQ_YN
,PRE_PAY_REQ_YN
,CUST_PO_NUMBER
,DATE_APPROVED
,DATETIME_CANCELLED
,DATE_SIGNED
,START_DATE
,END_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,APPLICATION_ID
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PRICE_LIST_ID
,CONVERSION_TYPE
,CONVERSION_RATE
,CONVERSION_RATE_DATE
,BILL_TO_SITE_USE_ID
,INV_RULE_ID
,RENEWAL_TYPE_CODE
,RENEWAL_END_DATE
,SHIP_TO_SITE_USE_ID
,PAYMENT_TERM_ID
,APPROVAL_TYPE
,PAYMENT_INSTRUCTION_TYPE
,ORG_ID
,CANCELLED_AMOUNT
,BILLED_AT_SOURCE
,BUY_OR_SELL
,ISSUE_OR_RECEIVE
,ESTIMATED_AMOUNT)
SELECT okc_k_headers_b_s.nextval HDRBID , ST.*
FROM
(SELECT HDRSTG.CONTRACT_NUMBER CONTRACT_NUMBER
,HDRSTG.OPERATING_UNIT_ID OPERATING_UNIT_ID
,HDRSTG.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
,HDRSTG.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
,HDRSTG.STATUS_CODE STATUS_CODE
,HDRSTG.QA_CHECKLIST QA_CHECKLIST
,HDRSTG.CATEGORY CATEGORY
,HDRSTG.CANCELLATION_REASON CANCELLATION_REASON
,HDRSTG.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE
,'N' ARCHIVED_YN
,'N' DELETED_YN
,'N' TEMPLATE_YN
,'CYA' CHR_TYPE
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,NVL(HDRSTG.PO_REQUIRED,'N') PO_REQUIRED
,HDRSTG.SERVICE_CHRG_PREPAY_REQ SERVICE_CHRG_PREPAY_REQ
,HDRSTG.PAYMENT_INSTRUCTION_DETAILS PAYMENT_INSTRUCTION_DETAILS
,HDRSTG.DATE_APPROVED DATE_APPROVED
,HDRSTG.DATE_CANCELED DATE_CANCELLED
,HDRSTG.DATE_SIGNED DATE_SIGNED
,HDRSTG.START_DATE START_DATE
,HDRSTG.END_DATE END_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,SYSDATE LAST_UPDATE_DATE
,515 APPLICATION_ID
,HDRSTG.SOURCE ORIG_SYSTEM_SOURCE_CODE
,null ORIG_SYSTEM_ID1
,HDRSTG.DOCUMENT ORIG_SYSTEM_REFERENCE1
,515 PROGRAM_APPLICATION_ID
,FND_GLOBAL.CONC_PROGRAM_ID PROGRAM_ID
,SYSDATE PROGRAM_UPDATE_DATE
,FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID
,HDRSTG.PRICE_LIST_ID PRICE_LIST_ID
,HDRSTG.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
,HDRSTG.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
,HDRSTG.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
,HDRSTG.BILL_TO_SITE_USAGE_ID BILL_TO_SITE_USAGE_ID
,HDRSTG.INVOICING_RULE_ID INVOICING_RULE_ID
,HDRSTG.RENEWAL_PROCESS RENEWAL_PROCESS
,HDRSTG.RENEW_UP_TO RENEW_UP_TO
,HDRSTG.SHIP_TO_SITE_USAGE_ID SHIP_TO_SITE_USAGE_ID
,HDRSTG.PAYMENT_TERMS_ID PAYMENT_TERMS_ID
,HDRSTG.APPROVAL_REQUIRED APPROVAL_REQUIRED
,HDRSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
,HDRSTG.OPERATING_UNIT_ID ORG_ID
,null CANCELLED_AMOUNT -- cancelled_amount, no corresponding field found
,HDRSTG.FULLY_BILLED BILLED_AT_SOURCE
,'S' BUY_OR_SELL
,'I' ISSUE_OR_RECEIVE
,NVL(INNER_2.TOTAL,0) ESTIMATED_AMOUNT
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,(SELECT INNER_1.HEADER_INTERFACE_ID
,SUM(ESTIMATED_AMOUNT) TOTAL
FROM ((SELECT HDR.HEADER_INTERFACE_ID
,NVL(SUM(INNER2_CVL.CVL_SUBTOTAL),0) + NVL(SUM(INNER2_USG.USG_SUBTOTAL),0) as ESTIMATED_AMOUNT
FROM OKS_INT_HEADER_STG_TEMP HDR
,OKS_INT_LINE_STG_TEMP LSTG
,(SELECT SUM(NVL(COV.SUBTOTAL,0)) AS CVL_SUBTOTAL
,COV.LINE_INTERFACE_ID
FROM OKS_COVERED_LEVELS_INTERFACE COV
,OKS_INT_COVERED_LEVEL_STG_TEMP CVL
WHERE CVL.COVERED_LEVEL_INTERFACE_ID= COV.COVERED_LEVEL_INTERFACE_ID
AND COV.STATUS_CODE <>'CANCELLED'
GROUP BY COV.LINE_INTERFACE_ID ) INNER2_CVL
,(SELECT SUM(NVL(USG.SUBTOTAL,0)) AS USG_SUBTOTAL
,USG.LINE_INTERFACE_ID
FROM OKS_INT_USAGE_COUNTER_STG_TEMP USG
WHERE USG.STATUS_CODE <>'CANCELLED'
GROUP BY USG.LINE_INTERFACE_ID) INNER2_USG
WHERE LSTG.LINE_INTERFACE_ID = INNER2_CVL.LINE_INTERFACE_ID(+)
AND LSTG.LINE_INTERFACE_ID = INNER2_USG.LINE_INTERFACE_ID(+)
AND LSTG.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND LSTG.STATUS_CODE <> 'CANCELLED'
GROUP BY HDR.HEADER_INTERFACE_ID)
UNION
(SELECT LST.HEADER_INTERFACE_ID
,SUM(LST.SUBTOTAL) ESTIMATED_AMOUNT
FROM OKS_INT_LINE_STG_TEMP LST
WHERE LST.LINE_TYPE ='SUBSCRIPTION'
AND LST.STATUS_CODE <>'CANCELLED'
GROUP BY LST.HEADER_INTERFACE_ID)) INNER_1
GROUP BY INNER_1.HEADER_INTERFACE_ID) INNER_2
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND INNER_2.HEADER_INTERFACE_ID (+) = HDRSTG.HEADER_INTERFACE_ID )ST ;
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_HEADERS_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,SHORT_DESCRIPTION
,COMMENTS
,DESCRIPTION
,COGNOMEN
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (HDRB_ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,SHORT_DESCRIPTION
,COMMENTS
,DESCRIPTION
,KNOWN_AS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
INTO OKC_K_PROCESSES
(ID
,PDF_ID
,CHR_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKCKPRID
,APPROVAL_PROCESS_ID
,HDRB_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
INTO OKC_K_VERS_NUMBERS
(CHR_ID
,MAJOR_VERSION
,MINOR_VERSION
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (HDRB_ID
,0
,1
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
WHEN(AGREEMENT_ID IS NOT NULL ) THEN
INTO OKC_GOVERNANCES
(ID
,DNZ_CHR_ID
,CHR_ID
,CLE_ID
,ISA_AGREEMENT_ID
,COPIED_ONLY_YN
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES ( OKCGID
,HDRB_ID
,HDRB_ID
,null
,AGREEMENT_ID
,'N'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT OKCHDRB.ID HDRB_ID
,okc_p_util.raw_to_number(sys_guid()) OKCGID
,okc_p_util.raw_to_number(sys_guid()) OKCKPRID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' SFWT_FLAG
,HDRSTG.DESCRIPTION SHORT_DESCRIPTION -- no matching column as short_description
,null COMMENTS
,HDRSTG.DESCRIPTION DESCRIPTION
,HDRSTG.KNOWN_AS KNOWN_AS
,HDRSTG.AGREEMENT_ID AGREEMENT_ID
,HDRSTG.APPROVAL_PROCESS_ID APPROVAL_PROCESS_ID
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,SYSDATE LAST_UPDATE_DATE
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_okc_header ;
PROCEDURE Insert_oks_header
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_oks_header';
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_HEADERS_B
(ID
,CHR_ID
,ACCT_RULE_ID
,PAYMENT_TYPE
,COMMITMENT_ID
,GRACE_DURATION
,GRACE_PERIOD
,EST_REV_PERCENT
,EST_REV_DATE
,TAX_STATUS
,BILLING_PROFILE_ID
,QUOTE_TO_CONTACT_ID
,QUOTE_TO_SITE_ID
,QUOTE_TO_EMAIL_ID
,QUOTE_TO_PHONE_ID
,QUOTE_TO_FAX_ID
,PAYMENT_TYPE
,RENEWAL_PO_REQUIRED
,RENEWAL_PO_NUMBER
,RENEWAL_PRICE_LIST
,RENEWAL_MARKUP_PERCENT
,RENEWAL_GRACE_DURATION
,RENEWAL_GRACE_PERIOD
,RENEWAL_EST_REV_PERCENT
,RENEWAL_EST_REV_DURATION
,RENEWAL_EST_REV_PERIOD
,RENEWAL_PRICING_TYPE
,INV_TRX_TYPE
,INV_PRINT_PROFILE
,AR_INTERFACE_YN
,HOLD_BILLING
,SUMMARY_TRX_YN
,SERVICE_PO_NUMBER
,SERVICE_PO_REQUIRED
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,FOLLOW_UP_ACTION
,FOLLOW_UP_DATE
,TRXN_EXTENSION_ID
,EXEMPT_CERTIFICATE_NUMBER
,EXEMPT_REASON_CODE
,TAX_AMOUNT
,BILLING_SCHEDULE_TYPE)
VALUES (OKSHDRB_ID
,CHR_ID
,ACCOUNTING_RULE_ID
,PAYMENT_INSTRUCTION
,COMMITMENT_NUMBER_ID
,GRACE_DURATION
,GRACE_PERIOD
,ESTIMATION_PERCENT
,ESTIMATION_DATE
,TAX_EXEMPTION_CONTROL
,RENEWAL_BILLING_PROFILE_ID
,QUOTE_TO_CONTACT
,QUOTE_TO_PARTY_SITE
,QUOTE_TO_EMAIL
,QUOTE_TO_PHONE
,QUOTE_TO_FAX
,PAYMENT_METHOD_CODE
,RENEWAL_PO_REQUIRED
,RENEWAL_PO_NUMBER
,RENEWAL_PRICE_LIST_ID
,RENEWAL_MARKUP
,RENEWAL_GRACE_DURATION
,RENEWAL_GRACE_PERIOD
,RENEWAL_ESTIMATED_PERCENT
,RENEWAL_ESTIMATED_DURATION
,RENEWAL_ESTIMATED_PERIOD
,RENEWAL_PRICING_TYPE
,BILLING_TRANSACTION_TYPE_ID
,SUMMARY_PRINT
,BILL_SERVICES
,HOLD_CREDITS
,SUMMARY_TRANSACTIONS
,SERVICE_CHARGES_PO_NUMBER
,SERVICE_CHARGES_PO_REQUIRED
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,FOLLOW_UP_ACTION
,FOLLOW_UP_DUE_DATE
,CREDIT_CARD_TRXN_EXTENSION_ID
,TAX_EXEMPTION_NUMBER
,EXEMPT_REASON_CODE
,TAX_AMOUNT
,BILLING_SCHEDULE_TYPE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKSHDRB_ID
,OKCHDRB.ID CHR_ID
,HDRSTG.ACCOUNTING_RULE_ID ACCOUNTING_RULE_ID
,HDRSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
,HDRSTG.COMMITMENT_ID COMMITMENT_NUMBER_ID
,HDRSTG.GRACE_DURATION GRACE_DURATION
,HDRSTG.GRACE_PERIOD GRACE_PERIOD
,HDRSTG.ESTIMATION_PERCENT ESTIMATION_PERCENT
,HDRSTG.ESTIMATION_DATE ESTIMATION_DATE
,HDRSTG.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
,HDRSTG.RENEWAL_BILLING_PROFILE_ID RENEWAL_BILLING_PROFILE_ID
,HDRSTG.QUOTE_TO_CONTACT QUOTE_TO_CONTACT
,HDRSTG.QUOTE_TO_PARTY_SITE QUOTE_TO_PARTY_SITE
,HDRSTG.QUOTE_TO_EMAIL QUOTE_TO_EMAIL
,HDRSTG.QUOTE_TO_PHONE QUOTE_TO_PHONE
,HDRSTG.QUOTE_TO_FAX QUOTE_TO_FAX
,HDRSTG.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
,HDRSTG.RENEWAL_PO_REQUIRED RENEWAL_PO_REQUIRED
,HDRSTG.RENEWAL_PO_NUMBER RENEWAL_PO_NUMBER
,HDRSTG.RENEWAL_PRICE_LIST_ID RENEWAL_PRICE_LIST_ID
,HDRSTG.RENEWAL_MARKUP RENEWAL_MARKUP
,HDRSTG.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
,HDRSTG.RENEWAL_GRACE_PERIOD RENEWAL_GRACE_PERIOD
,HDRSTG.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
,HDRSTG.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
,HDRSTG.RENEWAL_ESTIMATED_PERIOD RENEWAL_ESTIMATED_PERIOD
,HDRSTG.PRICING_METHOD RENEWAL_PRICING_TYPE
,HDRSTG.BILLING_TRANSACTION_TYPE_ID BILLING_TRANSACTION_TYPE_ID
,HDRSTG.SUMMARY_PRINT SUMMARY_PRINT
,HDRSTG.BILL_SERVICES BILL_SERVICES
,HDRSTG.HOLD_CREDITS HOLD_CREDITS
,HDRSTG.SUMMARY_TRANSACTIONS SUMMARY_TRANSACTIONS
,HDRSTG.SERVICE_CHARGES_PO_NUMBER SERVICE_CHARGES_PO_NUMBER
,HDRSTG.SERVICE_CHARGES_PO_REQUIRED SERVICE_CHARGES_PO_REQUIRED
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,HDRSTG.FOLLOW_UP_ACTION FOLLOW_UP_ACTION
,HDRSTG.FOLLOW_UP_DUE_DATE FOLLOW_UP_DUE_DATE
,null CREDIT_CARD_TRXN_EXTENSION_ID
,HDRSTG.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
,HDRSTG.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
,NVL(INNER_2.TAX_AMOUNT ,0) TAX_AMOUNT
,'T' BILLING_SCHEDULE_TYPE
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,(SELECT INNER_1.HEADER_INTERFACE_ID
,SUM(TAX_AMOUNT) TAX_AMOUNT
FROM
((SELECT HDR.HEADER_INTERFACE_ID
,NVL(SUM(INNER2_CVL.CVL_TAX_AMT),0) + NVL(SUM(INNER2_USG.USG_TAX_AMT),0) as TAX_AMOUNT
FROM OKS_INT_HEADER_STG_TEMP HDR
,OKS_INT_LINE_STG_TEMP LSTG
,(SELECT SUM(NVL(COV.TAX_AMOUNT,0)) AS CVL_TAX_AMT
,COV.LINE_INTERFACE_ID
FROM OKS_COVERED_LEVELS_INTERFACE COV
,OKS_INT_COVERED_LEVEL_STG_TEMP CVL
WHERE CVL.COVERED_LEVEL_INTERFACE_ID= COV.COVERED_LEVEL_INTERFACE_ID
AND COV.STATUS_CODE <>'CANCELLED'
GROUP BY COV.LINE_INTERFACE_ID ) INNER2_CVL
,(SELECT SUM(NVL(USG.TAX_AMOUNT,0)) AS USG_TAX_AMT
,USG.LINE_INTERFACE_ID
FROM OKS_INT_USAGE_COUNTER_STG_TEMP USG
WHERE USG.STATUS_CODE <>'CANCELLED'
GROUP BY USG.LINE_INTERFACE_ID) INNER2_USG
WHERE LSTG.LINE_INTERFACE_ID = INNER2_CVL.LINE_INTERFACE_ID(+)
AND LSTG.LINE_INTERFACE_ID = INNER2_USG.LINE_INTERFACE_ID(+)
AND LSTG.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND LSTG.STATUS_CODE <> 'CANCELLED'
GROUP BY HDR.HEADER_INTERFACE_ID)
UNION
(SELECT LST.HEADER_INTERFACE_ID
,SUM(LST.TAX_AMOUNT) TAX_AMOUNT
FROM OKS_INT_LINE_STG_TEMP LST
WHERE LST.LINE_TYPE ='SUBSCRIPTION'
AND LST.STATUS_CODE <>'CANCELLED'
GROUP BY LST.HEADER_INTERFACE_ID)) INNER_1
GROUP BY INNER_1.HEADER_INTERFACE_ID) INNER_2
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND INNER_2.HEADER_INTERFACE_ID (+) = HDRSTG.HEADER_INTERFACE_ID;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_oks_header;
PROCEDURE Insert_Contract_Groups
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Contract_Groups';
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_GRPINGS
(ID
,OBJECT_VERSION_NUMBER
,CGP_PARENT_ID
,INCLUDED_CHR_ID
,SCS_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (OKCGRP_ID
,OBJECT_VERSION_NUMBER
,CONTRACT_GROUP_ID
,INCLUDED_CHR_ID
,CATEGORY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
SELECT okc_p_util.raw_to_number(sys_guid()) OKCGRP_ID
,1 OBJECT_VERSION_NUMBER
,HDRSTG.CONTRACT_GROUP_ID CONTRACT_GROUP_ID
,OKCHDRB.ID INCLUDED_CHR_ID
,HDRSTG.CATEGORY CATEGORY
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_Contract_Groups;
PROCEDURE Insert_Party_Roles
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Party_Roles';
INSERT ALL
WHEN (OPERATING_UNIT_ID is not null ) THEN
INTO OKC_K_PARTY_ROLES_B
(ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES (okc_p_util.raw_to_number(sys_guid())
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE_OPUNIT
,OPERATING_UNIT_ID
,'#'
,'OKX_OPERUNIT'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
WHEN (CUSTOMER_PARTY_ID IS NOT NULL ) THEN
INTO OKC_K_PARTY_ROLES_B
(ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES (okc_p_util.raw_to_number(sys_guid())
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE_CUST
,CUSTOMER_PARTY_ID
,'#'
,'OKX_PARTY'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
WHEN (THIRD_PARTY_ID IS NOT NULL ) THEN
INTO OKC_K_PARTY_ROLES_B
(ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES (okc_p_util.raw_to_number(sys_guid())
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,'THIRD_PARTY'
,THIRD_PARTY_ID
,'#'
,'OKX_PARTY'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCKPTYRL_ID
,OKCHDRB.ID CHR_ID
,null CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,HDRSTG.OPERATING_UNIT_ID OPERATING_UNIT_ID
,HDRSTG.CUSTOMER_PARTY_ID CUSTOMER_PARTY_ID
,HDRSTG.THIRD_PARTY_ID THIRD_PARTY_ID
,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'VENDOR'
WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'MERCHANT'
END) RLE_CODE_OPUNIT
,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'CUSTOMER'
WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'SUBSCRIBER'
END) RLE_CODE_CUST
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_PARTY_ROLES_B
(ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKCKPTYRL_ID
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,RLE_CODE
,OBJECT1_ID1
,'#'
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCKPTYRL_ID
,null CHR_ID
,OKCLINB.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,HDRSTG.CUSTOMER_PARTY_ID OBJECT1_ID1
,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'CUSTOMER'
WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'SUBSCRIBER'
END) RLE_CODE
,1 OBJECT_VERSION_NUMBER
,'OKX_PARTY' JTOT_OBJECT1_CODE
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' SFWT_FLAG
,null COGNOMEN
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB
,OKS_LINES_INTERFACE LININT
,OKS_INT_LINE_STG_TEMP LINSTG
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND LININT.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND LINSTG.LINE_INTERFACE_ID= LININT.LINE_INTERFACE_ID
and OKCLINB.line_number= linstg.line_number
AND OKCLINB.CHR_ID =OKCHDRB.ID;
'Number of records successfully inserted = ' || l_int_count );
INSERT all
INTO OKC_K_PARTY_ROLES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,COGNOMEN
,LAST_UPDATE_LOGIN)
VALUES (OKCKPTYRL_ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,COGNOMEN
,LAST_UPDATE_LOGIN)
SELECT OKCPTY.ID OKCKPTYRL_ID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' SFWT_FLAG
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,null COGNOMEN
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKC_K_PARTY_ROLES_B OKCPTY
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKS_INT_HEADER_STG_TEMP HDRSTG
WHERE HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCPTY.DNZ_CHR_ID = OKCHDRB.ID;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_Party_Roles;
PROCEDURE Insert_Contacts
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Contacts';
INSERT ALL
WHEN (CUSTOMER_CONTRACT_ADMIN_ID IS NOT NULL ) THEN
INTO OKC_CONTACTS
(ID
,CPL_ID
,CRO_CODE
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,OBJECT_VERSION_NUMBER
,JTOT_OBJECT1_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES (OKCCONTACTS_ID
,CPL_ID
,'ADMIN'
,DNZ_CHR_ID
,CUSTOMER_CONTRACT_ADMIN_ID
,'#'
,OBJECT_VERSION_NUMBER
,'OKX_PCONTACT'
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCCONTACTS_ID
,OKCKPTYRL.ID CPL_ID -- ID of okc_k_party_roles_b table.
,HDRSTG.CUSTOMER_CONTRACT_ADMIN_ID CUSTOMER_CONTRACT_ADMIN_ID
,OKCHDRB.ID DNZ_CHR_ID
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_PARTY_ROLES_B OKCKPTYRL
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.INTERFACE_STATUS='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
AND OKCKPTYRL.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
AND OKCKPTYRL.CLE_ID IS null;
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (SALESPERSON_ID IS NOT NULL ) THEN
INTO OKC_CONTACTS
(ID
,CPL_ID
,CRO_CODE
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,OBJECT_VERSION_NUMBER
,JTOT_OBJECT1_CODE
,SALES_GROUP_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES (OKCCONTACTS_ID
,CPL_ID
,'SALESPERSON'
,DNZ_CHR_ID
,SALESPERSON_ID
,'#'
,OBJECT_VERSION_NUMBER
,'OKX_SALEPERS'
,SALESGROUP_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCCONTACTS_ID
,OKCKPTYRL.ID CPL_ID -- ID of okc_k_party_roles_b table.
,HDRSTG.SALESPERSON_ID SALESPERSON_ID
,OKCHDRB.ID DNZ_CHR_ID
,1 OBJECT_VERSION_NUMBER
,HDRSTG.SALESGROUP_ID SALESGROUP_ID
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
FROM OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_PARTY_ROLES_B OKCKPTYRL
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.INTERFACE_STATUS='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
AND OKCKPTYRL.RLE_CODE IN ('VENDOR','MERCHANT') ;
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (CUSTOMER_BILLING_CONTACT_ID is not null ) THEN
INTO OKC_CONTACTS
(ID
,CPL_ID
,CRO_CODE
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (okc_p_util.raw_to_number(sys_guid())
,CPL_ID
,'CUST_BILLING'
,DNZ_CHR_ID
,CUSTOMER_BILLING_CONTACT_ID
,'#'
,'OKX_CONTBILL'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
WHEN (CUSTOMER_SHIPPING_CONTACT_ID is not null ) THEN
INTO OKC_CONTACTS
(ID
,CPL_ID
,CRO_CODE
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (okc_p_util.raw_to_number(sys_guid())
,CPL_ID
,'CUST_SHIPPING'
,DNZ_CHR_ID
,CUSTOMER_SHIPPING_CONTACT_ID
,'#'
,'OKX_CONTSHIP'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
SELECT OKCKPTYRL.ID CPL_ID -- okc_party_roles ID column
,OKCHDRB.ID DNZ_CHR_ID
,LINSTG.CUSTOMER_BILLING_CONTACT_ID CUSTOMER_BILLING_CONTACT_ID
,LINSTG.CUSTOMER_SHIPPING_CONTACT_ID CUSTOMER_SHIPPING_CONTACT_ID
,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN 'CUST_BILLING'
WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN 'CUST_SHIPPING'
END ) CRO_CODE
,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN LINSTG.CUSTOMER_BILLING_CONTACT_ID
WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID
END) OBJECT1_ID1
,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN 'OKX_CONTBILL'
WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN 'OKX_CONTSHIP'
END ) JTOT_OBJECT1_CODE
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKS_INT_LINE_STG_TEMP LINSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_PARTY_ROLES_B OKCKPTYRL
,OKC_K_LINES_B OKCLINB
WHERE HDRSTG.INTERFACE_STATUS='S'
AND LINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.CHR_ID = OKCHDRB.ID
AND OKCLINB.LINE_NUMBER = LINSTG.LINE_NUMBER
AND OKCKPTYRL.CLE_ID = OKCLINB.ID ;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_Contacts;
PROCEDURE Insert_Sales_Credits
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Sales_Credits';
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_SALES_CREDITS
(ID
,PERCENT
,CHR_ID
,CLE_ID
,CTC_ID
,SALES_CREDIT_TYPE_ID1
,SALES_CREDIT_TYPE_ID2
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SALES_GROUP_ID)
VALUES (OKCSALESCR_ID
,PERCENT
,CHR_ID
,CLE_ID
,SALESPERSON_ID
,SALES_CREDIT_TYPE_ID
,SALES_CREDIT_TYPE_ID2
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SALESGROUP_ID)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCSALESCR_ID
,OKSCRSTG.PERCENT PERCENT
,OKCHDRB.ID CHR_ID
,null CLE_ID
,OKSCRSTG.SALESPERSON_ID SALESPERSON_ID
,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID
,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID2 -- need to confirm what could be the value
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,OKSCRSTG.SALESGROUP_ID SALESGROUP_ID
FROM OKS_INT_SALES_CREDIT_STG_TEMP OKSCRSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE HDRSTG.HEADER_INTERFACE_ID = OKSCRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND HDRSTG.INTERFACE_STATUS ='S';
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_SALES_CREDITS
(ID
,PERCENT
,CHR_ID
,CLE_ID
,CTC_ID
,SALES_CREDIT_TYPE_ID1
,SALES_CREDIT_TYPE_ID2
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SALES_GROUP_ID)
VALUES (OKCSALESCR_ID
,PERCENT
,CHR_ID
,CLE_ID
,SALESPERSON_ID
,SALES_CREDIT_TYPE_ID
,SALES_CREDIT_TYPE_ID2
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SALESGROUP_ID
)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCSALESCR_ID
,OKSCRSTG.PERCENT PERCENT
,OKCHDRB.ID CHR_ID
,OKCLINB.ID CLE_ID
,OKSCRSTG.SALESPERSON_ID SALESPERSON_ID
,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID
,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID2 -- need to confirm what could be the value
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,OKSCRSTG.SALESGROUP_ID SALESGROUP_ID
FROM OKS_INT_SALES_CREDIT_STG_TEMP OKSCRSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB
WHERE HDRSTG.HEADER_INTERFACE_ID = OKSCRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.CHR_ID = OKCHDRB.ID;
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_Sales_Credits;
PROCEDURE Insert_okc_toplines
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_okc_toplines ';
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_LINES_B
(ID
,LINE_NUMBER
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,STS_CODE
,TRN_CODE
,LSE_ID
,PRICE_LEVEL_IND
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CURRENCY_CODE
,LAST_UPDATE_LOGIN
,START_DATE
,END_DATE
,ATTRIBUTE_CATEGORY
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,PRICE_LIST_ID
,CUST_ACCT_ID
,BILL_TO_SITE_USE_ID
,INV_RULE_ID
,LINE_RENEWAL_TYPE_CODE
,SHIP_TO_SITE_USE_ID
,DATE_CANCELLED
,PAYMENT_INSTRUCTION_TYPE
,CANCELLED_AMOUNT
,DISPLAY_SEQUENCE
,EXCEPTION_YN
,ANNUALIZED_FACTOR
,PRICE_NEGOTIATED
,PRICE_UNIT
,CUST_ACCT_ID)
VALUES (OKCLINB_ID
,LINE_NUMBER
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,STATUS_CODE
,CANCELLATION_REASON
,LSE_ID
,'Y'
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,CURRENCY_CODE
,LAST_UPDATE_LOGIN
,START_DATE
,END_DATE
,CATEGORY
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,PRICE_LIST_ID
,BILL_TO_ACCOUNT_ID
,BILL_TO_SITE_USAGE_ID
,INVOICING_RULE_ID
,RENEWAL_TYPE_CODE
,SHIP_TO_SITE_USAGE_ID
,CANCELLATION_DATE
,PAYMENT_INSTRUCTION
,CANCELLED_AMOUNT
,1
,EXCEPTION_YN
,ANNUALIZED_FACTOR
,PRICE_NEGOTIATED
,PRICE_UNIT
,CUST_ACCT_ID)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
,OLSTG.LINE_NUMBER LINE_NUMBER
,OKCHDRB.ID CHR_ID
, null CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,OLSTG.STATUS_CODE STATUS_CODE
,OLSTG.CANCELLATION_REASON CANCELLATION_REASON
,OLSTG.LSE_ID LSE_ID
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,OHSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,OLSTG.START_DATE START_DATE
,OLSTG.END_DATE END_DATE
,OHSTG.CATEGORY CATEGORY
,null ORIG_SYSTEM_SOURCE_CODE
,null ORIG_SYSTEM_ID1
,null ORIG_SYSTEM_REFERENCE1
,OLSTG.PRICE_LIST_ID PRICE_LIST_ID
,null BILL_TO_ACCOUNT_ID
,OLSTG.BILL_TO_SITE_USAGE_ID BILL_TO_SITE_USAGE_ID
,OLSTG.INVOICING_RULE_ID INVOICING_RULE_ID
,OLSTG.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,OLSTG.SHIP_TO_SITE_USAGE_ID SHIP_TO_SITE_USAGE_ID
,OLSTG.CANCELLATION_DATE CANCELLATION_DATE
,OLSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
,null CANCELLED_AMOUNT -- no matching filed for this in stg table
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,OLSTG.LINE_REFERENCE COGNOMEN
,'N' EXCEPTION_YN
,OLSTG.UNIT_PRICE PRICE_UNIT
,CSTACCT.CUST_ACCOUNT_ID CUST_ACCT_ID
,(CASE WHEN OLSTG.LINE_TYPE IN ('SERVICE','WARRANTY','EXT_WARRANTY') THEN INNER_CLVL.SUBTOTAL
WHEN OLSTG.LINE_TYPE ='USAGE' THEN INNER_USG.SUBTOTAL
ELSE OLSTG.SUBTOTAL
END ) PRICE_NEGOTIATED
,(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OLSTG.START_DATE -
DECODE(ADD_MONTHS(OLSTG.END_DATE, -12),( OLSTG.END_DATE-366), 0,
DECODE(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
- ADD_MONTHS(OLSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
/ (INNER_Q.NYEARS+1) /(OLSTG.END_DATE-OLSTG.START_DATE+ 1) ANNUALIZED_FACTOR
FROM OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP OHSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,HZ_CUST_SITE_USES_ALL CSTSITE
,HZ_CUST_ACCT_SITES_ALL CSTACCT
,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, LINE_INTERFACE_ID
FROM OKS_INT_LINE_STG_TEMP LINSTG
WHERE LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)) INNER_Q
,(SELECT SUM(CVLINT.SUBTOTAL) AS SUBTOTAL
,CVLSTG.LINE_INTERFACE_ID
FROM OKS_COVERED_LEVELS_INTERFACE CVLINT
,OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
WHERE CVLINT.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID
AND cvlint.status_code <>'CANCELLED'
GROUP BY CVLSTG.LINE_INTERFACE_ID) INNER_CLVL
,(SELECT SUM(USGSTG.SUBTOTAL ) AS SUBTOTAL
,USGSTG.LINE_INTERFACE_ID
FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
WHERE USGSTG.STATUS_CODE <> 'CANCELLED'
GROUP BY LINE_INTERFACE_ID )INNER_USG
WHERE OHSTG.HEADER_INTERFACE_ID=OLSTG.HEADER_INTERFACE_ID
AND OLSTG.LINE_INTERFACE_ID =INNER_Q.LINE_INTERFACE_ID
AND INNER_CLVL.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
AND INNER_USG.LINE_INTERFACE_ID (+) = OLSTG.LINE_INTERFACE_ID
AND OHSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(CSTSITE.BILL_TO_SITE_USE_ID,CSTSITE.SITE_USE_ID) = OLSTG.BILL_TO_SITE_USAGE_ID
AND CSTSITE.SITE_USE_CODE = 'BILL_TO'
AND CSTSITE.CUST_ACCT_SITE_ID = CSTACCT.CUST_ACCT_SITE_ID
AND NVL(OHSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')= NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
AND OHSTG.INTERFACE_STATUS ='S';
'Number of records successfully inserted = ' || l_int_count );*/
INSERT ALL
WHEN(1=1) THEN
INTO OKC_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COGNOMEN
,SFWT_FLAG )
VALUES (OKCLINB_ID
,LANGUAGE
,SOURCE_LANG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COGNOMEN
,'N')
WHEN (1=1) then
INTO OKC_K_ITEMS
(ID
,CLE_ID
,CHR_ID
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,UOM_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,PRICED_ITEM_YN
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES ( OKCKITEM_ID
,CLE_ID
,null
,DNZ_CHR_ID
,ITEM_ID
,ITEM_ORGANIZATION_ID
,JTOT_OBJECT1_CODE
,UOM_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,PRICED_ITEM_YN
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT OKCLINB.ID OKCLINB_ID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,OLSTG.LINE_REFERENCE COGNOMEN
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,okc_p_util.raw_to_number(sys_guid()) OKCKITEM_ID
,OKCLINB.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,null CHR_ID
,OLSTG.ITEM_ID ITEM_ID
,OLSTG.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID
,(CASE WHEN OLSTG.LINE_TYPE IN ('SERVICE','EXT_WARRANTY') THEN 'OKX_SERVICE'
WHEN OLSTG.LINE_TYPE IN ('WARRANTY') THEN 'OKX_WARRANTY'
WHEN OLSTG.LINE_TYPE IN ('USAGE') THEN 'OKX_USAGE'
WHEN OLSTG.LINE_TYPE IN ('SUBSCRIPTION') THEN 'OKS_SUBSCRIPTION'
END) JTOT_OBJECT1_CODE
,'N' EXCEPTION_YN
,OLSTG.billing_interval_period UOM_CODE -- need to confirm wat values can go
,(CASE WHEN OLSTG.LINE_TYPE IN ('SUBSCRIPTION') THEN OLSTG.SUBSCRIPTION_QUANTITY
WHEN OLSTG.LINE_TYPE IN ('SERVICE','EXT_WARRANTY','WARRANTY') THEN TO_NUMBER(olstg.quantity_uom)
END) NUMBER_OF_ITEMS
,(CASE WHEN OLSTG.LSE_ID IN (1,14,19)
THEN 'N'
ELSE 'Y'
END ) PRICED_ITEM_YN
,1 OBJECT_VERSION_NUMBER
FROM OKC_K_LINES_B OKCLINB
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER= OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.LINE_NUMBER= OLSTG.LINE_NUMBER
AND OKCLINB.CLE_ID IS NULL
AND HDRSTG.INTERFACE_STATUS ='S' ;
'Number of records Inserted = '|| l_int_count);
END Insert_okc_toplines;
PROCEDURE Insert_oks_toplines
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_oks_toplines';
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_LINES_B
(ID
,CLE_ID
,DNZ_CHR_ID
,ACCT_RULE_ID
,PAYMENT_TYPE
,COMMITMENT_ID
,CUST_PO_NUMBER_REQ_YN
,CUST_PO_NUMBER
,INV_PRINT_FLAG
,TAX_STATUS
,PRICE_UOM
,USAGE_PERIOD
,USAGE_TYPE
,uom_quantified
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TAX_STATUS
,TAX_AMOUNT
,TAX_CLASSIFICATION_CODE
,TAX_EXEMPTION_ID
,EXEMPT_REASON_CODE
,COVERAGE_ID
,STANDARD_COV_YN
,BILLING_SCHEDULE_TYPE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,ORIG_SYSTEM_SOURCE_CODE
,AVERAGING_INTERVAL
,SETTLEMENT_INTERVAL
,TERMN_METHOD
,OBJECT_VERSION_NUMBER
,pm_program_id
,pm_sch_exists_yn
,pm_conf_req_yn)
VALUES (OKSLINB_ID
,cle_id
,DNZ_CHR_ID
,ACCOUNTING_RULE_ID
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,PRINT_INVOICE
,TAX_EXEMPTION_CONTROL
,PRICE_UOM
,USAGE_PERIOD
,USAGE_TYPE
,uom_quantified
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,TAX_EXEMPTION_CONTROL
,TAX_AMOUNT
,TAX_CLASSIFICATION_CODE
,TAX_EXEMPTION_NUMBER
,EXEMPT_REASON_CODE
,COVERAGE_ID
,STANDARD_COV_YN
,BILLING_SCHEDULE_TYPE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,ORIG_SYSTEM_SOURCE_CODE
,AVERAGING_INTERVAL
,SETTLEMENT_INTERVAL
,TERMN_METHOD
,1
,pm_program_id
,pm_sch_exists_yn
,pm_conf_req_yn)
SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
,OKCLINB.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,OLSTG.ACCOUNTING_RULE_ID ACCOUNTING_RULE_ID
,OLSTG.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
,OLSTG.COMMITMENT_ID COMMITMENT_ID
,OLSTG.PO_REQUIRED PO_REQUIRED
,OLSTG.PAYMENT_INSTRUCTION_DETAILS PAYMENT_INSTRUCTION_DETAILS
,OLSTG.PRINT_INVOICE PRINT_INVOICE
,OLSTG.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,OLSTG.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
,OLSTG.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
,OLSTG.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
,OLSTG.REFERENCE_TEMPLATE_ID COVERAGE_ID
,null ORIG_SYSTEM_ID1
,null ORIG_SYSTEM_REFERENCE1
,null ORIG_SYSTEM_SOURCE_CODE
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,OLSTG.INVOICE_TEXT INVOICE_TEXT
,OLSTG.PRICE_UOM PRICE_UOM
,OLSTG.USAGE_PERIOD USAGE_PERIOD
,OLSTG.USAGE_TYPE USAGE_TYPE
,olstg.quantity_uom uom_quantified
,'Y' STANDARD_COV_YN
,(CASE WHEN OLSTG.LINE_TYPE='SUBSCRIPTION' THEN 'E'
ELSE 'T'
END ) BILLING_SCHEDULE_TYPE
,OLSTG.AVERAGING_INTERVAL AVERAGING_INTERVAL
,OLSTG.SETTLEMENT_INTERVAL SETTLEMENT_INTERVAL
,OLSTG.USAGE_TERMINATION_METHOD TERMN_METHOD
,(CASE WHEN OLSTG.LINE_TYPE IN('SERVICE','WARRANTY','EXT_WARRANTY') THEN INNER_CLVL.TAX_AMOUNT
WHEN OLSTG.LINE_TYPE ='USAGE' THEN INNER_USG.TAX_AMOUNT
ELSE OLSTG.TAX_AMOUNT
END ) TAX_AMOUNT
,COV_IN_QUERY.PM_PROGRAM_ID PM_PROGRAM_ID
,COV_IN_QUERY.PM_SCH_EXISTS_YN PM_SCH_EXISTS_YN
,COV_IN_QUERY.PM_CONF_REQ_YN PM_CONF_REQ_YN
FROM OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB
,(SELECT SUM(CVLINT.TAX_AMOUNT) AS TAX_AMOUNT
,CVLSTG.LINE_INTERFACE_ID
FROM OKS_COVERED_LEVELS_INTERFACE CVLINT , OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
WHERE CVLINT.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID
AND CVLINT.STATUS_CODE <>'CANCELLED'
GROUP BY CVLSTG.LINE_INTERFACE_ID) INNER_CLVL
,(SELECT SUM(USGSTG.TAX_AMOUNT) AS TAX_AMOUNT
,USGSTG.LINE_INTERFACE_ID
FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
WHERE USGSTG.STATUS_CODE <> 'CANCELLED'
GROUP BY LINE_INTERFACE_ID )INNER_USG
,(SELECT OKSLB.PM_PROGRAM_ID AS PM_PROGRAM_ID
,OKSLB.PM_SCH_EXISTS_YN AS PM_SCH_EXISTS_YN
,OKSLB.PM_CONF_REQ_YN AS PM_CONF_REQ_YN
,LIN.LINE_INTERFACE_ID
,LIN.LINE_NUMBER
FROM OKS_K_LINES_B OKSLB
, OKS_INT_LINE_STG_TEMP LIN
WHERE OKSLB.CLE_ID = LIN.REFERENCE_TEMPLATE_ID) COV_IN_QUERY
WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND COV_IN_QUERY.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
AND OLSTG.LINE_NUMBER = COV_IN_QUERY.LINE_NUMBER(+)
AND INNER_CLVL.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
AND INNER_USG.LINE_INTERFACE_ID (+) = OLSTG.LINE_INTERFACE_ID
AND OKCLINB.LINE_NUMBER = OLSTG.LINE_NUMBER
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKCLINB.CLE_ID IS NULL;
'Number of records successfully inserted = ' || l_int_count ); */
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,SFWT_FLAG)
VALUES (OKSLINB_ID
,LANGUAGE
,SOURCE_LANG
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,'N')
SELECT OKSLINB.ID OKSLINB_ID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,OLSTG.INVOICE_TEXT INVOICE_TEXT
FROM OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKS_K_LINES_B OKSLINB
,OKC_K_LINES_B OKCLINB
WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
AND OKSLINB.CLE_ID=OKCLINB.ID
AND OKCLINB.CLE_ID IS NULL;
INSERT ALL
WHEN (1=1) THEN
INTO OKS_SUBSCR_HEADER_B
(ID
,CLE_ID
,DNZ_CHR_ID
,INSTANCE_ID
,SUBSCRIPTION_TYPE
,ITEM_TYPE
,MEDIA_TYPE
,STATUS
,FREQUENCY
,FULFILLMENT_CHANNEL
,OFFSET
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES (SUB_HDR_ID
,CLE_ID
,DNZ_CHR_ID
,INSTANCE_ID
,SUBSCRIPTION_TYPE
,ITEM_TYPE
,MEDIA_TYPE
,STATUS
,FREQUENCY
,FULFILLMENT_CHANNEL
,OFFSET
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT okc_p_util.raw_to_number(sys_guid()) SUB_HDR_ID
,OKCLINB.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,SUB_HDR.INSTANCE_ID INSTANCE_ID
,SUB_HDR.SUBSCRIPTION_TYPE SUBSCRIPTION_TYPE
,SUB_HDR.ITEM_TYPE ITEM_TYPE
,SUB_HDR.MEDIA_TYPE MEDIA_TYPE
,SUB_HDR.STATUS STATUS
,SUB_HDR.FREQUENCY FREQUENCY
,SUB_HDR.FULFILLMENT_CHANNEL FULFILLMENT_CHANNEL
,SUB_HDR.OFFSET OFFSET
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKC_K_LINES_B OKCLINB
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKS_SUBSCR_HEADER_B SUB_HDR
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
AND OKCLINB.CLE_ID IS NULL
AND OLSTG.LINE_TYPE ='SUBSCRIPTION'
AND SUB_HDR.ID = OLSTG.REFERENCE_TEMPLATE_ID;
INSERT ALL
WHEN (1=1) THEN
INTO OKS_SUBSCR_HEADER_TL
(ID
,NAME
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,COMMENTS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN )
VALUES ( TL_ID
,NAME
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,COMMENTS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT SUB_HDR_2.ID TL_ID
,SUB_HDR_TL.NAME NAME
,SUB_HDR_TL.DESCRIPTION DESCRIPTION
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' SFWT_FLAG
,SUB_HDR_TL.COMMENTS COMMENTS
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKC_K_LINES_B OKCLINB
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKS_SUBSCR_HEADER_B SUB_HDR_1
,OKS_SUBSCR_HEADER_B SUB_HDR_2
,OKS_SUBSCR_HEADER_TL SUB_HDR_TL
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.INTERFACE_STATUS ='S'
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
AND OKCLINB.CLE_ID IS NULL
AND OLSTG.LINE_TYPE ='SUBSCRIPTION'
AND SUB_HDR_1.ID = OLSTG.REFERENCE_TEMPLATE_ID
AND SUB_HDR_1.ID = SUB_HDR_TL.ID
AND SUB_HDR_2.DNZ_CHR_ID = OKCHDRB.ID
AND SUB_HDR_2.CLE_ID = OKCLINB.ID
AND SUB_HDR_TL.LANGUAGE = USERENV('LANG');
'Number of records successfully inserted = ' || l_int_count );
'Number of records Inserted = '|| l_int_count);
END Insert_oks_toplines;
PROCEDURE Insert_covlevel_and_usgcounter
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_covlevel_and_usgcounter';
/* Insert query for Sublines - Covered levels */
l_stmt_num := 10;
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_LINES_B
(ID
,CLE_ID
,LINE_NUMBER
,DNZ_CHR_ID
,STS_CODE
,LSE_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,PRICE_NEGOTIATED
,PRICE_UNIT
,CURRENCY_CODE
,START_DATE
,END_DATE
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,LINE_RENEWAL_TYPE_CODE
,DATE_CANCELLED
,TRN_CODE
,DISPLAY_SEQUENCE
,EXCEPTION_YN
,ANNUALIZED_FACTOR
,PRICE_LEVEL_IND)
VALUES (OKCLINB_ID
,CLE_ID
,LINE_NUMBER
,DNZ_CHR_ID
,STATUS_CODE
,LSE_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SUBTOTAL
,PRICE_UNIT
,CURRENCY_CODE
,START_DATE
,END_DATE
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,2
,'N'
,ANNUALIZED_FACTOR
,PRICE_LEVEL_IND)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
,OKCLINB.ID CLE_ID
,OCLI.LINE_NUMBER LINE_NUMBER
,OKCHDRB.ID DNZ_CHR_ID
,null CHR_ID
,OCLI.STATUS_CODE STATUS_CODE
,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL THEN 9
WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN 35
WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN 7
WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN 8
WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN 10
WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN 11
END) LSE_ID
,HDRSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,OCLI.SUBTOTAL SUBTOTAL
,(CASE WHEN OCLI.SUBTOTAL IS NOT NULL AND OCLI.SUBTOTAL > 0
AND OCLI.QUANTITY_COVERED IS NOT NULL AND OCLI.QUANTITY_COVERED > 0
THEN OCLI.SUBTOTAL/OCLI.QUANTITY_COVERED
ELSE NULL
END) PRICE_UNIT --Imports program doesn't derive any value, for any column, so commented this part of the code.
,OCLI.START_DATE START_DATE
,OCLI.END_DATE END_DATE
,null ORIG_SYSTEM_SOURCE_CODE
,null ORIG_SYSTEM_ID1
,null ORIG_SYSTEM_REFERENCE1
,OCLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,OCLI.CANCELLATION_DATE CANCELLATION_DATE
,OCLI.CANCELLATION_REASON CANCELLATION_REASON
,(ADD_MONTHS(OKSLINSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OKSLINSTG.START_DATE -
DECODE(ADD_MONTHS(OKSLINSTG.END_DATE, -12),(OKSLINSTG.END_DATE-366), 0,
DECODE(ADD_MONTHS(OKSLINSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
- ADD_MONTHS(OKSLINSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
/ (INNER_Q.NYEARS+1) /(OKSLINSTG.END_DATE - OKSLINSTG.START_DATE+ 1) ANNUALIZED_FACTOR
,'Y' PRICE_LEVEL_IND -- defaulted
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKS_INT_LINE_STG_TEMP OKSLINSTG
,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, COVERED_LEVEL_INTERFACE_ID
FROM OKS_INT_COVERED_LEVEL_STG_TEMP COVSTG
,OKS_INT_LINE_STG_TEMP LINSTG
WHERE COVSTG.LINE_INTERFACE_ID = LINSTG.LINE_INTERFACE_ID
AND LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)) INNER_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
AND OCLVLSTG.COVERED_LEVEL_INTERFACE_ID = INNER_Q.COVERED_LEVEL_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OCLI.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND OKCLINB.CHR_ID = OKCHDRB.ID
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKCLINB.LINE_NUMBER = OKSLINSTG.LINE_NUMBER;
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_LINES_B
(ID
,CLE_ID
,DNZ_CHR_ID
,INV_PRINT_FLAG
,PRICE_UOM
,TAX_AMOUNT
,BILLING_SCHEDULE_TYPE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKSLINB_ID
,CLE_ID
,DNZ_CHR_ID
,PRINT_INVOICE
,PRICE_UOM
,TAX_AMOUNT
,BILLING_SCHEDULE_TYPE
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
,OKCLINB_subline.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,null CHR_ID
,OCLI.PRINT_INVOICE PRINT_INVOICE
,OCLI.PRICE_UOM PRICE_UOM
,OCLI.TAX_AMOUNT TAX_AMOUNT
,'T' BILLING_SCHEDULE_TYPE
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKS_INT_LINE_STG_TEMP OKSLINSTG
,OKC_K_LINES_B OKCLINB_SUBLINE
,OKC_K_LINES_B OKCLINB_LINE
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
AND OCLI.LINE_NUMBER = OKCLINB_SUBLINE.LINE_NUMBER
AND OKCLINB_LINE.LINE_NUMBER= OKSLINSTG.LINE_NUMBER
AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OCLI.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.INTERFACE_STATUS ='S';
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (1=1) THEN
INTO OKS_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKSLINB_ID
,LANGUAGE
,SOURCE_LANG
,OKS_SFWT_FLAG
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT OKSLINB.ID OKSLINB_ID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'S' OKS_SFWT_FLAG
,OCLI.INVOICE_TEXT INVOICE_TEXT
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
,OKC_K_HEADERS_aLL_B OKCHDRB
,OKC_K_LINES_B OKCLINB_LINE
,OKC_K_LINES_B OKCLINB_SUBLINE
,OKS_K_LINES_B OKSLINB
,OKS_INT_LINE_STG_TEMP OKSLINSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
AND OCLVLSTG.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.CHR_ID =OKCHDRB.ID
AND OKCLINB_LINE.LINE_NUMBER = OKSLINSTG.LINE_NUMBER
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
AND OKCLINB_SUBLINE.LINE_NUMBER=OCLI.LINE_NUMBER
AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID;
'Number of records successfully inserted = ' || l_int_count );
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_ITEMS
(ID
,CLE_ID
,CHR_ID
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,UOM_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,created_by
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
VALUES ( OKCKITEMS_ID
,CLE_ID
,null
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,UOM_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,OBJECT_VERSION_NUMBER
,CREATION_DATE
,created_by
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
INTO OKC_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COGNOMEN)
VALUES (OKCLINB_ID
,LANGUAGE
,SOURCE_LANG
,OKC_SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LINE_REFERENCE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCKITEMS_ID
,OKCLINB_SUBLINE.ID CLE_ID
,OKCLINB_SUBLINE.ID OKCLINB_ID
,OKCHDRB.ID DNZ_CHR_ID
,null CHR_ID
,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL THEN OCLVLSTG.COVERED_INSTANCE_ID
WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN OCLVLSTG.COVERED_ACCOUNT_ID
WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN OCLVLSTG.COVERED_ITEM_ID
WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN OCLVLSTG.COVERED_PARTY_ID
WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN OCLVLSTG.COVERED_SITE_ID
WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN OCLVLSTG.COVERED_SYSTEM_ID
END) OBJECT1_ID1
,(CASE WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN TO_CHAR(OCLI.COVERED_ITEM_ORG_ID)
ELSE '#'
END) OBJECT1_ID2
,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL THEN 'OKX_CUSTPROD'
WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN 'OKX_CUSTACCT'
WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN 'OKX_COVITEM'
WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN 'OKX_PARTY'
WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN 'OKX_PARTYSITE'
WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN 'OKX_COVSYST'
END) JTOT_OBJECT1_CODE
,OCLI.QUANTITY_UOM UOM_CODE
,'N' EXCEPTION_YN
,OCLI.QUANTITY_COVERED NUMBER_OF_ITEMS
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' OKC_SFWT_FLAG
,OCLI.LINE_REFERENCE LINE_REFERENCE
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
,OKC_K_HEADERS_aLL_B OKCHDRB
,OKC_K_LINES_B OKCLINB_LINE
,OKC_K_LINES_B OKCLINB_SUBLINE
,OKS_K_LINES_B OKSLINB
,OKS_INT_LINE_STG_TEMP OKSLINSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
AND OCLVLSTG.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
AND HDRSTG.INTERFACE_STATUS ='S'
AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.CHR_ID =OKCHDRB.ID
AND OKCLINB_LINE.LINE_NUMBER = OKSLINSTG.LINE_NUMBER
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
AND OKCLINB_SUBLINE.LINE_NUMBER=OCLI.LINE_NUMBER
AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID;
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_LINES_B
(ID
,LINE_NUMBER
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,STS_CODE
,TRN_CODE
,LSE_ID
,EXCEPTION_YN
,OBJECT_VERSION_NUMBER
,display_sequence
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,PRICE_NEGOTIATED
,PRICE_LEVEL_IND
,CURRENCY_CODE
,START_DATE
,END_DATE
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,DATE_CANCELLED
,ANNUALIZED_FACTOR
,CANCELLED_AMOUNT
,LINE_RENEWAL_TYPE_CODE)
VALUES (OKCLINB_ID
,LINE_NUMBER
,CHR_ID
,CLE_ID
,DNZ_CHR_ID
,STATUS_CODE
,CANCELLATION_REASON
,LSE_ID
,EXCEPTION_YN
,OBJECT_VERSION_NUMBER
,2
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,SUBTOTAL
,PRICED_YN
,CURRENCY_CODE
,START_DATE
,END_DATE
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,CANCELLATION_DATE
,ANNUALIZED_FACTOR
,CANCELLED_AMOUNT
,RENEWAL_TYPE_CODE)
SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
,OUSGCNTSTG.LINE_NUMBER LINE_NUMBER
,null CHR_ID
,OKCLINB_LINE.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,OUSGCNTSTG.STATUS_CODE STATUS_CODE
,OUSGCNTSTG.CANCELLATION_REASON CANCELLATION_REASON
,13 LSE_ID
,'N' EXCEPTION_YN
,1 OBJECT_VERSION_NUMBER
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,OUSGCNTSTG.SUBTOTAL SUBTOTAL
,'Y' PRICED_YN
,HDRSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
,OUSGCNTSTG.START_DATE START_DATE
,OUSGCNTSTG.END_DATE END_DATE
,null ORIG_SYSTEM_SOURCE_CODE
,null ORIG_SYSTEM_ID1
,null ORIG_SYSTEM_REFERENCE1
,OUSGCNTSTG.CANCELLATION_DATE CANCELLATION_DATE
,OUSGCNTSTG.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
, (ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OLSTG.START_DATE -
DECODE(ADD_MONTHS(OLSTG.END_DATE, -12),( OLSTG.END_DATE-366), 0,
DECODE(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
- ADD_MONTHS(OLSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
/ (INNER_Q.NYEARS+1) /(OLSTG.END_DATE-OLSTG.START_DATE+ 1) ANNUALIZED_FACTOR
,null CANCELLED_AMOUNT
FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB_LINE
,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, USAGE_COUNTER_INTERFACE_ID
FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
,OKS_INT_LINE_STG_TEMP LINSTG
WHERE
USGSTG.LINE_INTERFACE_ID = LINSTG.LINE_INTERFACE_ID
AND LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)
) INNER_Q
WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID =OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
AND OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID = INNER_Q.USAGE_COUNTER_INTERFACE_ID
AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
AND HDRSTG.INTERFACE_STATUS = 'S';
INSERT ALL
WHEN (1=1 ) THEN
INTO OKS_K_LINES_B
(ID
,CLE_ID
,DNZ_CHR_ID
,USAGE_EST_YN
,USAGE_EST_METHOD
,USAGE_EST_START_DATE
,BILLING_SCHEDULE_TYPE
,TAX_AMOUNT
,MINIMUM_QUANTITY
,DEFAULT_QUANTITY
,FIXED_QUANTITY
,LEVEL_YN
,PRICE_UOM
,USAGE_PERIOD
,USAGE_TYPE
,UOM_QUANTIFIED
,INV_PRINT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,ORIG_SYSTEM_SOURCE_CODE
,OBJECT_VERSION_NUMBER)
VALUES ( OKSLINB_ID
,CLE_ID
,DNZ_CHR_ID
,FILL_YN
,ESTIMATION_METHOD
,ESTIMATION_START_DATE
,BILLING_SCHEDULE_TYPE
,TAX_AMOUNT
,MINIMUM_USG_CTR
,DEFAULT_USG_CTR
,FIXED_USG_CTR
,LEVEL_YN
,PRICE_UOM
,USAGE_PERIOD
,USAGE_TYPE
,UOM_QUANTIFIED
,INV_PRINT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,ORIG_SYSTEM_SOURCE_CODE
,1)
SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
,OKCLINB_SUBLINE.ID CLE_ID
,OKCHDRB.ID DNZ_CHR_ID
,OUSGCNTSTG.FILL_YN FILL_YN
,OUSGCNTSTG.ESTIMATION_METHOD ESTIMATION_METHOD
,OUSGCNTSTG.ESTIMATION_START_DATE ESTIMATION_START_DATE
,OUSGCNTSTG.TAX_AMOUNT TAX_AMOUNT
,OUSGCNTSTG.MINIMUM_USG_CTR MINIMUM_USG_CTR
,OUSGCNTSTG.DEFAULT_USG_CTR DEFAULT_USG_CTR
,OUSGCNTSTG.FIXED_USG_CTR FIXED_USG_CTR
,OUSGCNTSTG.LEVEL_YN LEVEL_YN
,OLSTG.PRICE_UOM PRICE_UOM -- need to confirm
,NULL USAGE_PERIOD
,NULL USAGE_TYPE
,NULL UOM_QUANTIFIED
,OUSGCNTSTG.PRINT_INVOICE INV_PRINT_FLAG
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,null ORIG_SYSTEM_SOURCE_CODE
,null ORIG_SYSTEM_ID1
,null ORIG_SYSTEM_REFERENCE1
,'T' BILLING_SCHEDULE_TYPE
FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB_SUBLINE
,OKC_K_LINES_B OKCLINB_LINE
WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID = OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
and OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
and OUSGCNTSTG.LINE_NUMBER = OKCLINB_SUBLINE.LINE_NUMBER
and OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.INTERFACE_STATUS ='S';
INSERT ALL
WHEN (1=1) THEN
INTO OKC_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COGNOMEN)
VALUES ( OKCLINB_ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COGNOMEN)
WHEN (1=1 ) THEN
INTO OKS_K_LINES_TL
(ID
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKSLINB_ID
,LANGUAGE
,SOURCE_LANG
,'S'
,INVOICE_TEXT
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
WHEN (1=1) THEN
INTO OKC_K_ITEMS
(ID
,CLE_ID
,CHR_ID
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,PRICED_ITEM_YN
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
VALUES (OKCKITM_ID
,CLE_ID
,null
,DNZ_CHR_ID
,OBJECT1_ID1
,OBJECT1_ID2
,JTOT_OBJECT1_CODE
,EXCEPTION_YN
,NUMBER_OF_ITEMS
,PRICED_ITEM_YN
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT OKCLINB_SUBLINE.ID OKCLINB_ID
,USERENV('LANG') LANGUAGE
,USERENV('LANG') SOURCE_LANG
,'N' SFWT_FLAG
,FND_GLOBAL.USER_ID CREATED_BY
,SYSDATE CREATION_DATE
,FND_GLOBAL.USER_ID LAST_UPDATED_BY
,SYSDATE LAST_UPDATE_DATE
,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
,OUSGCNTSTG.LINE_REFERENCE COGNOMEN
,OKSLINB.ID OKSLINB_ID
,OUSGCNTINT.INVOICE_TEXT INVOICE_TEXT
,okc_p_util.raw_to_number(sys_guid()) OKCKITM_ID
,OKCLINB_SUBLINE.ID CLE_ID
,null CHR_ID
,OKCHDRB.ID DNZ_CHR_ID
,OUSGCNTINT.COUNTER_ID OBJECT1_ID1
,'#' OBJECT1_ID2
,'OKX_COUNTER' JTOT_OBJECT1_CODE -- default value for usagel lines
,'N' EXCEPTION_YN
,null NUMBER_OF_ITEMS
,'Y' PRICED_ITEM_YN
,1 OBJECT_VERSION_NUMBER
FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
,OKS_INT_LINE_STG_TEMP OLSTG
,OKS_INT_HEADER_STG_TEMP HDRSTG
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB_SUBLINE
,OKC_K_LINES_B OKCLINB_LINE
,OKS_K_LINES_B OKSLINB
WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID = OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_SUBLINE.CLE_ID =OKCLINB_LINE.ID
AND OUSGCNTSTG.LINE_NUMBER=OKCLINB_SUBLINE.LINE_NUMBER
AND OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.DNZ_CHR_ID =OKCHDRB.ID
AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
AND HDRSTG.INTERFACE_STATUS = 'S';
'Number of records successfully inserted = ' || l_int_count );*/
END Insert_covlevel_and_usgcounter;
PROCEDURE Insert_Contracts IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Contracts';
Insert_okc_header;
Insert_oks_header;
Insert_Contract_groups;
Insert_okc_toplines;
Insert_oks_toplines;
Insert_Party_Roles;
Insert_Contacts;
Insert_Sales_Credits ;
Insert_covlevel_and_usgcounter;
END Insert_Contracts;