[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT ALL
WHEN (1 = 1) THEN
INTO OKS_INT_HEADER_STG_TEMP
(HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,CONTRACT_NUMBER
,CONTRACT_NUMBER_MODIFIER
,CATEGORY
,STATUS_CODE
,START_DATE
,END_DATE
,KNOWN_AS
,DESCRIPTION
,OPERATING_UNIT_ID
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_ID
,CONTRACT_CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,PRICE_LIST_ID
,PAYMENT_TERMS_ID
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,INVOICING_RULE_ID
,SERVICE_CHRG_PREPAY_REQ
,RENEWAL_PROCESS
,APPROVAL_REQUIRED
,RENEW_UP_TO
,DATE_APPROVED
,DATE_SIGNED
,DATE_CANCELED
,CANCELLATION_REASON
,QA_CHECKLIST
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,BILL_SERVICES
,BILLING_TRANSACTION_TYPE_ID
,ACCOUNTING_RULE_ID
,HOLD_CREDITS
,SUMMARY_PRINT
,SUMMARY_TRANSACTIONS
,SERVICE_CHARGES_PO_REQUIRED
,SERVICE_CHARGES_PO_NUMBER
,PRICING_METHOD
,RENEWAL_PRICE_LIST_ID
,RENEWAL_MARKUP
,RENEWAL_BILLING_PROFILE_ID
,RENEWAL_PO_NUMBER
,RENEWAL_PO_REQUIRED
,RENEWAL_GRACE_DURATION
,RENEWAL_GRACE_PERIOD
,RENEWAL_ESTIMATED_PERCENT
,RENEWAL_ESTIMATED_DURATION
,RENEWAL_ESTIMATED_PERIOD
,QUOTE_TO_PARTY_SITE
,QUOTE_TO_CONTACT
,QUOTE_TO_PHONE
,QUOTE_TO_FAX
,QUOTE_TO_EMAIL
,GRACE_DURATION
,GRACE_PERIOD
,ESTIMATION_PERCENT
,ESTIMATION_DATE
,FOLLOW_UP_DUE_DATE
,FOLLOW_UP_ACTION
,CUSTOMER_PARTY_ID
,THIRD_PARTY_ID
,CONTRACT_GROUP_ID
,APPROVAL_PROCESS_ID
,AGREEMENT_ID
,SALESPERSON_ID
,CUSTOMER_CONTRACT_ADMIN_ID
,INV_ORGANIZATION_ID
,EXEMPT_REASON_CODE
,SALESGROUP_ID
,FULLY_BILLED
,SOURCE
,DOCUMENT)
VALUES (HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,CONTRACT_NUMBER
,CONTRACT_NUMBER_MODIFIER
,CATEGORY
,STATUS_CODE
,START_DATE
,END_DATE
,KNOWN_AS
,DESCRIPTION
,OPERATING_UNIT_ID
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_ID
,CONTRACT_CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,PRICE_LIST_ID
,PAYMENT_TERMS_ID
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,INVOICING_RULE_ID
,SERVICE_CHRG_PREPAY_REQ
,RENEWAL_PROCESS
,APPROVAL_REQUIRED
,RENEW_UP_TO
,DATE_APPROVED
,DATE_SIGNED
,DATE_CANCELED
,CANCELLATION_REASON
,QA_CHECKLIST
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,BILL_SERVICES
,BILLING_TRANSACTION_TYPE_ID
,ACCOUNTING_RULE_ID
,HOLD_CREDITS
,SUMMARY_PRINT
,SUMMARY_TRANSACTIONS
,SERVICE_CHARGES_PO_REQUIRED
,SERVICE_CHARGES_PO_NUMBER
,PRICING_METHOD
,RENEWAL_PRICE_LIST_ID
,RENEWAL_MARKUP
,RENEWAL_BILLING_PROFILE_ID
,RENEWAL_PO_NUMBER
,RENEWAL_PO_REQUIRED
,RENEWAL_GRACE_DURATION
,RENEWAL_GRACE_PERIOD
,RENEWAL_ESTIMATED_PERCENT
,RENEWAL_ESTIMATED_DURATION
,RENEWAL_ESTIMATED_PERIOD
,QUOTE_TO_PARTY_SITE
,QUOTE_TO_CONTACT
,QUOTE_TO_PHONE
,QUOTE_TO_FAX
,QUOTE_TO_EMAIL
,GRACE_DURATION
,GRACE_PERIOD
,ESTIMATION_PERCENT
,ESTIMATION_DATE
,FOLLOW_UP_DUE_DATE
,FOLLOW_UP_ACTION
,CUSTOMER_PARTY_ID
,THIRD_PARTY_ID
,CONTRACT_GROUP_ID
,APPROVAL_PROCESS_ID
,AGREEMENT_ID
,SALESPERSON_ID
,CUSTOMER_CONTRACT_ADMIN_ID
,INV_ORGANIZATION_ID
,EXEMPT_REASON_CODE
,SALESGROUP_ID
,FULLY_BILLED
,SOURCE
,DOCUMENT)
WHEN (OPERATING_UNIT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_OU'
,NULL)
WHEN (BILL_TO_SITE_USAGE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_BTSU'
,NULL)
WHEN (SHIP_TO_SITE_USAGE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_STSU'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CUR_CODE'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
AND CURRENCY_CONVERSION_TYPE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CONV_TYPE'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
AND CURRENCY_CONVERSION_TYPE = 'User'
AND (CURRENCY_CONVERSION_RATE IS NULL OR CURRENCY_CONVERSION_RATE <= 0)) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CONV_RATE'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
AND CURRENCY_CONVERSION_DATE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CONV_DATE'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
AND CURRENCY_CONVERSION_TYPE <> 'User'
AND HDR_CURRENCY_CONVERSION_RATE IS NOT NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_CONV_RATE'
,NULL)
WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
AND CONTRACT_CURRENCY_CODE = OU_LEDGER_CUR_CODE
AND (CURRENCY_CONVERSION_TYPE IS NOT NULL
OR HDR_CURRENCY_CONVERSION_RATE IS NOT NULL
OR CURRENCY_CONVERSION_DATE IS NOT NULL)) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_CONV'
,NULL)
WHEN (PRICE_LIST_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PRICELIST'
,NULL)
WHEN (PAYMENT_TERMS_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PAYTERM'
,NULL)
WHEN (HDR_PAYMENT_INSTRUCTION IS NOT NULL AND PAYMENT_INSTRUCTION IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PAYINSTR'
,NULL)
WHEN (INVOICING_RULE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_INVRULE'
,NULL)
WHEN (HDR_PAYMENT_METHOD_CODE IS NOT NULL AND PAYMENT_METHOD_CODE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PMC'
,NULL)
WHEN ((PAYMENT_METHOD_CODE IS NOT NULL OR HDR_COMMITMENT_ID IS NOT NULL) AND COMMITMENT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_COMMIT'
,NULL)
WHEN (ACCOUNTING_RULE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_ACCRULE'
,NULL)
WHEN (PRICING_METHOD = 'LST' AND RENEWAL_PRICE_LIST_ID IS NULL)
OR (RENEWAL_PRICE_LIST_ID IS NOT NULL AND (PRICING_METHOD NOT IN ('LST', 'PCT') OR PRICING_METHOD IS NULL ) ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_RENPL_MANDATORY'
,NULL)
WHEN (REN_PL_PROVIDED = 'Y' AND RENEWAL_PRICE_LIST_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_RENPL'
,NULL)
WHEN (CUSTOMER_PARTY_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CTPARTY'
,NULL)
WHEN (TP_PROVIDED = 'Y' AND THIRD_PARTY_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_TPPARTY'
,NULL)
WHEN (CONTRACT_GROUP_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CG'
,NULL)
WHEN (AGREEMENT_PROVIDED = 'Y' AND AGREEMENT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PAGRMT'
,NULL)
WHEN (SALESPERSON_PROVIDED = 'Y' AND SALESPERSON_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SALEPERS'
,NULL)
WHEN (CONTRACT_ADMIN_PROVIDED = 'Y' AND CUSTOMER_CONTRACT_ADMIN_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CCADMIN'
,NULL)
WHEN DESCRIPTION IS NULL THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DESC'
,NULL)
WHEN FULLY_BILLED IS NULL THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_FULB'
,NULL)
WHEN FULLY_BILLED_INV ='Y' THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INV_FULB_STS'
,NULL)
SELECT OHI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OHI.ROWID HEADER_INTERFACE_ROWID
,rtrim(OHI.CONTRACT_NUMBER) CONTRACT_NUMBER
,rtrim(OHI.CONTRACT_NUMBER_MODIFIER) CONTRACT_NUMBER_MODIFIER
,OHI.CATEGORY CATEGORY
,OHI.STATUS_CODE STATUS_CODE
,OHI.START_DATE START_DATE
,OHI.END_DATE END_DATE
,rtrim(OHI.KNOWN_AS) KNOWN_AS
,rtrim(OHI.DESCRIPTION) DESCRIPTION
,(CASE WHEN (COUNT (DISTINCT HOU.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
AND COUNT (DISTINCT HOI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1)
THEN OHI.OPERATING_UNIT_ID
ELSE NULL
END) OPERATING_UNIT_ID
,(CASE WHEN COUNT(DISTINCT BTSUI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.BILL_TO_SITE_USAGE_ID
ELSE NULL
END) BILL_TO_SITE_USAGE_ID
,(CASE WHEN COUNT(DISTINCT STSUI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.SHIP_TO_SITE_USAGE_ID
ELSE NULL
END) SHIP_TO_SITE_USAGE_ID
,(CASE WHEN COUNT(DISTINCT FCUR.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.CONTRACT_CURRENCY_CODE
ELSE NULL
END) CONTRACT_CURRENCY_CODE
,(CASE WHEN COUNT(DISTINCT GLCT.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.CURRENCY_CONVERSION_TYPE
ELSE NULL
END) CURRENCY_CONVERSION_TYPE
,trunc(OHI.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
,(CASE WHEN OHI.CURRENCY_CONVERSION_TYPE = 'User' THEN OHI.CURRENCY_CONVERSION_RATE
ELSE GLDR.CONVERSION_RATE
END) CURRENCY_CONVERSION_RATE
,OHI.CURRENCY_CONVERSION_RATE HDR_CURRENCY_CONVERSION_RATE
,(CASE WHEN NAMEID_Q.QPI_HV = 'Y' AND NAMEID_Q.QPI_COUNT = 1 THEN NAMEID_Q.QPI_ID
WHEN NAMEID_Q.QPN_HV = 'Y' AND NAMEID_Q.QPN_COUNT = 1 THEN NAMEID_Q.QPN_ID
ELSE NULL
END) PRICE_LIST_ID /*SKUCHIMA 11880769 */
,(CASE WHEN NAMEID_Q.PTI_COUNT = 1 THEN NAMEID_Q.PTI_ID
WHEN NAMEID_Q.PTN_COUNT = 1 THEN NAMEID_Q.PTN_ID
ELSE NULL
END) PAYMENT_TERMS_ID
,(CASE WHEN COUNT(DISTINCT FL.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.PAYMENT_INSTRUCTION
ELSE NULL
END) PAYMENT_INSTRUCTION
,OHI.PAYMENT_INSTRUCTION HDR_PAYMENT_INSTRUCTION
,OHI.PO_REQUIRED PO_REQUIRED
,rtrim(OHI.PAYMENT_INSTRUCTION_DETAILS) PAYMENT_INSTRUCTION_DETAILS
,(CASE WHEN NAMEID_Q.IRI_COUNT = 1 THEN NAMEID_Q.IRI_ID
WHEN NAMEID_Q.IRN_COUNT = 1 THEN NAMEID_Q.IRN_ID
ELSE NULL
END) INVOICING_RULE_ID
,OHI.SERVICE_CHRG_PREPAY_REQ SERVICE_CHRG_PREPAY_REQ
,OHI.RENEWAL_PROCESS RENEWAL_PROCESS
,OHI.APPROVAL_REQUIRED APPROVAL_REQUIRED
,OHI.RENEW_UP_TO RENEW_UP_TO
,OHI.DATE_APPROVED DATE_APPROVED
,OHI.DATE_SIGNED DATE_SIGNED
,OHI.DATE_CANCELED DATE_CANCELED
,OHI.CANCELLATION_REASON CANCELLATION_REASON
,OHI.QA_CHECKLIST QA_CHECKLIST
,(CASE WHEN COUNT(DISTINCT PMC.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.PAYMENT_METHOD_CODE
ELSE NULL
END) PAYMENT_METHOD_CODE
,OHI.PAYMENT_METHOD_CODE HDR_PAYMENT_METHOD_CODE
,(CASE WHEN COUNT(DISTINCT CNI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
THEN OHI.COMMITMENT_ID
ELSE NULL
END) COMMITMENT_ID
,OHI.COMMITMENT_ID HDR_COMMITMENT_ID
,OHI.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
,OHI.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
,OHI.BILL_SERVICES BILL_SERVICES
,OHI.BILLING_TRANSACTION_TYPE_ID BILLING_TRANSACTION_TYPE_ID
,(CASE WHEN ARI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARI_COUNT = 1 THEN NAMEID_Q.ARI_ID
WHEN ARN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARN_COUNT = 1 THEN NAMEID_Q.ARN_ID
ELSE NULL
END) ACCOUNTING_RULE_ID
,OHI.HOLD_CREDITS HOLD_CREDITS
,OHI.SUMMARY_PRINT SUMMARY_PRINT
,OHI.SUMMARY_TRANSACTIONS SUMMARY_TRANSACTIONS
,OHI.SERVICE_CHARGES_PO_REQUIRED SERVICE_CHARGES_PO_REQUIRED
,rtrim(OHI.SERVICE_CHARGES_PO_NUMBER) SERVICE_CHARGES_PO_NUMBER
,OHI.PRICING_METHOD PRICING_METHOD
,(CASE WHEN NAMEID_Q.RNPLI_HV = 'Y' AND NAMEID_Q.RNPLI_COUNT = 1 THEN NAMEID_Q.RNPLI_ID
WHEN NAMEID_Q.RNPLN_HV = 'Y' AND NAMEID_Q.RNPLN_COUNT = 1 THEN NAMEID_Q.RNPLN_ID
ELSE NULL
END) RENEWAL_PRICE_LIST_ID --skuchima 11880769
,(CASE WHEN (OHI.RENEWAL_PRICE_LIST_ID IS NOT NULL OR OHI.RENEWAL_PRICE_LIST_NAME IS NOT NULL)
THEN 'Y'
ELSE 'N'
END) REN_PL_PROVIDED
,OHI.RENEWAL_MARKUP RENEWAL_MARKUP
,OHI.RENEWAL_BILLING_PROFILE_ID RENEWAL_BILLING_PROFILE_ID
,rtrim(OHI.RENEWAL_PO_NUMBER) RENEWAL_PO_NUMBER
,OHI.RENEWAL_PO_REQUIRED RENEWAL_PO_REQUIRED
,OHI.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
,OHI.RENEWAL_GRACE_PERIOD RENEWAL_GRACE_PERIOD
,OHI.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
,OHI.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
,OHI.RENEWAL_ESTIMATED_PERIOD RENEWAL_ESTIMATED_PERIOD
,OHI.QUOTE_TO_PARTY_SITE QUOTE_TO_PARTY_SITE
,OHI.QUOTE_TO_CONTACT QUOTE_TO_CONTACT
,OHI.QUOTE_TO_PHONE QUOTE_TO_PHONE
,OHI.QUOTE_TO_FAX QUOTE_TO_FAX
,OHI.QUOTE_TO_EMAIL QUOTE_TO_EMAIL
,OHI.GRACE_DURATION GRACE_DURATION
,OHI.GRACE_PERIOD GRACE_PERIOD
,OHI.ESTIMATION_PERCENT ESTIMATION_PERCENT
,OHI.ESTIMATION_DATE ESTIMATION_DATE
,OHI.FOLLOW_UP_DUE_DATE FOLLOW_UP_DUE_DATE
,OHI.FOLLOW_UP_ACTION FOLLOW_UP_ACTION
,(CASE WHEN NAMEID_Q.HPCUSTI_COUNT = 1 THEN NAMEID_Q.HPCUSTI_ID
WHEN NAMEID_Q.HPCUSTNUM_COUNT = 1 THEN NAMEID_Q.HPCUSTNUM_ID
WHEN NAMEID_Q.HPCUSTN_COUNT = 1 THEN NAMEID_Q.HPCUSTN_ID
ELSE NULL
END) CUSTOMER_PARTY_ID
,(CASE WHEN NAMEID_Q.HPTPI_COUNT = 1 THEN NAMEID_Q.HPTPI_ID
WHEN NAMEID_Q.HPTPNUM_COUNT = 1 THEN NAMEID_Q.HPTPNUM_ID
WHEN NAMEID_Q.HPTPN_COUNT = 1 THEN NAMEID_Q.HPTPN_ID
ELSE NULL
END) THIRD_PARTY_ID
,(CASE WHEN (OHI.THIRD_PARTY_ID IS NOT NULL OR OHI.THIRD_PARTY_NUMBER IS NOT NULL OR OHI.THIRD_PARTY_NAME IS NOT NULL)
THEN 'Y'
ELSE 'N'
END) TP_PROVIDED
,(CASE WHEN CGI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.CGI_COUNT = 1 THEN NAMEID_Q.CGI_ID
WHEN CGN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.CGN_COUNT = 1 THEN NAMEID_Q.CGN_ID
ELSE NULL
END) CONTRACT_GROUP_ID
,OHI.APPROVAL_PROCESS_ID APPROVAL_PROCESS_ID
,(CASE WHEN NAMEID_Q.AGI_COUNT = 1 THEN NAMEID_Q.AGI_ID
WHEN NAMEID_Q.AGN_COUNT = 1 THEN NAMEID_Q.AGN_ID
ELSE NULL
END) AGREEMENT_ID
,(CASE WHEN (OHI.AGREEMENT_ID IS NOT NULL OR OHI.AGREEMENT_NAME IS NOT NULL)
THEN 'Y'
ELSE 'N'
END) AGREEMENT_PROVIDED
,(CASE WHEN NAMEID_Q.SPI_COUNT = 1 THEN NAMEID_Q.SPI_ID
WHEN NAMEID_Q.SPN_COUNT = 1 THEN NAMEID_Q.SPN_ID
ELSE NULL
END) SALESPERSON_ID
,(CASE WHEN (OHI.SALESPERSON_ID IS NOT NULL OR OHI.SALESPERSON_NAME IS NOT NULL)
THEN 'Y'
ELSE 'N'
END) SALESPERSON_PROVIDED
,(CASE WHEN NAMEID_Q.CCAI_COUNT = 1 THEN NAMEID_Q.CCAI_ID
WHEN NAMEID_Q.CCAN_COUNT = 1 THEN NAMEID_Q.CCAN_ID
ELSE NULL
END) CUSTOMER_CONTRACT_ADMIN_ID
,(CASE WHEN (OHI.CUSTOMER_CONTRACT_ADMIN_ID IS NOT NULL OR OHI.CUSTOMER_CONTRACT_ADMIN_NAME IS NOT NULL)
THEN 'Y'
ELSE 'N'
END) CONTRACT_ADMIN_PROVIDED
,OHI.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
,OHI.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
,NAMEID_Q.OU_LEDGER_CUR_CODE OU_LEDGER_CUR_CODE
,OHI.SALESGROUP_ID SALESGROUP_ID
,(CASE WHEN OHI.FULLY_BILLED IN ('Y', 'N') THEN OHI.FULLY_BILLED ELSE NULL END) FULLY_BILLED
,(CASE WHEN OHI.FULLY_BILLED ='Y' AND OHI.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
ELSE 'N' END) FULLY_BILLED_INV
,OHI.SOURCE SOURCE
,rtrim(OHI.DOCUMENT) DOCUMENT
FROM OKS_HEADERS_INTERFACE OHI
,HR_ALL_ORGANIZATION_UNITS HOU
,HR_ORGANIZATION_INFORMATION HOI
,HZ_CUST_SITE_USES_ALL BTSUI
,HZ_CUST_SITE_USES_ALL STSUI
,FND_CURRENCIES FCUR
,GL_DAILY_CONVERSION_TYPES GLCT
,GL_DAILY_RATES GLDR
,FND_LOOKUPS FL
,RA_CUSTOMER_TRX_ALL CNI
,FND_LOOKUPS PMC
,(SELECT distinct(HDI.HEADER_INTERFACE_ID) -- to avoid duplicate joins with tables when some names match with multiple records
,COUNT(DISTINCT HPCUSTI.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTI_COUNT
,MAX(HDI.CUSTOMER_PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTI_ID -- CUSTOMER PARTY ID Based on Id
,COUNT(DISTINCT HPCUSTN.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTN_COUNT
,MAX(HPCUSTN.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTN_ID -- CUSTOMER PARTY ID Based on name
,COUNT(DISTINCT HPCUSTNUM.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTNUM_COUNT
,MAX(HPCUSTNUM.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTNUM_ID -- CUSTOMER PARTY ID Based on number
,COUNT(DISTINCT HPTPI.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPI_COUNT
,MAX(HDI.THIRD_PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPI_ID -- THIRD PARTY ID Based on Id
,COUNT(DISTINCT HPTPN.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPN_COUNT
,MAX(HPTPN.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPN_ID -- THIRD PARTY ID Based on name
,COUNT(DISTINCT HPTPNUM.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPNUM_COUNT
,MAX(HPTPNUM.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPNUM_ID -- THIRD PARTY ID Based on number
,COUNT(DISTINCT SPI.ROWID) OVER (PARTITION BY HDI.ROWID) SPI_COUNT
,MAX(HDI.SALESPERSON_ID) OVER (PARTITION BY HDI.ROWID) SPI_ID -- Sales Person Id Based on Id
,COUNT(DISTINCT SPN.ROW_ID) OVER (PARTITION BY HDI.ROWID) SPN_COUNT
,MAX(SPN.SALESREP_ID) OVER (PARTITION BY HDI.ROWID) SPN_ID -- Sales Person Id Based on Name
,COUNT(DISTINCT AGI.ROWID) OVER (PARTITION BY HDI.ROWID) AGI_COUNT
,MAX(HDI.AGREEMENT_ID) OVER (PARTITION BY HDI.ROWID) AGI_ID -- Agreement Id based on Id
,COUNT(DISTINCT AGTLN.ROWID) OVER (PARTITION BY HDI.ROWID) AGN_COUNT
,MAX(AGTLN.AGREEMENT_ID) OVER (PARTITION BY HDI.ROWID) AGN_ID -- Agreement Id based on Name
,COUNT(DISTINCT QPI.ROWID) OVER (PARTITION BY HDI.ROWID) QPI_COUNT
,MAX(HDI.PRICE_LIST_ID) OVER (PARTITION BY HDI.ROWID) QPI_ID -- PRICE LIST ID based on Id
,(CASE WHEN HDI.CONTRACT_CURRENCY_CODE = QPI.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) QPI_HV --SKUCHIMA 11880769
,COUNT(DISTINCT QPTLN.ROW_ID) OVER (PARTITION BY HDI.ROWID) QPN_COUNT
,MAX(QPTLN.LIST_HEADER_ID) OVER (PARTITION BY HDI.ROWID) QPN_ID -- PRICE LIST ID based on Name
,(CASE WHEN HDI.CONTRACT_CURRENCY_CODE = QPTLN.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) QPN_HV --SKUCHIMA 11880769
,COUNT(DISTINCT RNPLI.ROWID) OVER (PARTITION BY HDI.ROWID) RNPLI_COUNT
,MAX(HDI.RENEWAL_PRICE_LIST_ID) OVER (PARTITION BY HDI.ROWID) RNPLI_ID -- RENEWAL PRICE LIST ID based on Id
,(CASE WHEN HDI.CONTRACT_CURRENCY_CODE = RNPLI.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) RNPLI_HV --SKUCHIMA 11880769
,COUNT(DISTINCT RNPLN.ROW_ID) OVER (PARTITION BY HDI.ROWID) RNPLN_COUNT
,MAX(RNPLN.LIST_HEADER_ID) OVER (PARTITION BY HDI.ROWID) RNPLN_ID -- RENEWAL PRICE LIST ID based on Name
,(CASE WHEN HDI.CONTRACT_CURRENCY_CODE = RNPLN.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) RNPLN_HV --SKUCHIMA 11880769
,COUNT(DISTINCT ARI.ROWID) OVER (PARTITION BY HDI.ROWID) ARI_COUNT
,MAX(HDI.ACCOUNTING_RULE_ID) OVER (PARTITION BY HDI.ROWID) ARI_ID -- Accounting Rule ID based on Id
,(CASE WHEN ARI.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
THEN 'Y'
ELSE 'N'
END) ARI_ID_VAL_FLAG
,COUNT(DISTINCT ARN.ROWID) OVER (PARTITION BY HDI.ROWID) ARN_COUNT
,MAX(ARN.RULE_ID) OVER (PARTITION BY HDI.ROWID) ARN_ID -- Accounting Rule ID based on Name
,(CASE WHEN ARN.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
THEN 'Y'
ELSE 'N'
END) ARN_ID_VAL_FLAG
,COUNT(DISTINCT IRI.ROWID) OVER (PARTITION BY HDI.ROWID) IRI_COUNT
,MAX(HDI.INVOICING_RULE_ID) OVER (PARTITION BY HDI.ROWID) IRI_ID -- Invoicing Rule ID based on Id
,COUNT(DISTINCT IRN.ROWID) OVER (PARTITION BY HDI.ROWID) IRN_COUNT
,MAX(IRN.RULE_ID) OVER (PARTITION BY HDI.ROWID) IRN_ID -- Invoicing Rule ID based on Name
,COUNT(DISTINCT PTI.ROWID) OVER (PARTITION BY HDI.ROWID) PTI_COUNT
,MAX(HDI.PAYMENT_TERMS_ID) OVER (PARTITION BY HDI.ROWID) PTI_ID -- Payment Terms ID based on Id
,COUNT(DISTINCT PTTLN.ROWID) OVER (PARTITION BY HDI.ROWID) PTN_COUNT
,MAX(PTTLN.TERM_ID) OVER (PARTITION BY HDI.ROWID) PTN_ID -- Payment Terms ID based on Name
,COUNT(DISTINCT CGI.ROWID) OVER (PARTITION BY HDI.ROWID) CGI_COUNT
,MAX(HDI.CONTRACT_GROUP_ID) OVER (PARTITION BY HDI.ROWID) CGI_ID -- Contracts Group ID based on Id
,(CASE WHEN (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID)
THEN 'Y'
ELSE 'N'
END) CGI_ID_VAL_FLAG
,COUNT(DISTINCT CGBN.ROWID) OVER (PARTITION BY HDI.ROWID) CGN_COUNT
,MAX(CGBN.ID) OVER (PARTITION BY HDI.ROWID) CGN_ID -- Contracts Group ID based on Name /*modified for Bug:6839334*/
,(CASE WHEN ((HDI.CONTRACT_GROUP_ID IS NULL) AND (CGBN.PUBLIC_YN = 'Y' OR CGBN.USER_ID = FND_GLOBAL.USER_ID))THEN 'Y'
WHEN ((HDI.CONTRACT_GROUP_ID IS NOT NULL) AND (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID))THEN 'Y'
ELSE 'N'
END) CGN_ID_VAL_FLAG
,COUNT(DISTINCT CCAI.ROWID) OVER (PARTITION BY HDI.ROWID) CCAI_COUNT
,MAX(HDI.CUSTOMER_CONTRACT_ADMIN_ID) OVER (PARTITION BY HDI.ROWID) CCAI_ID -- Customer Contracts Admin ID based on Id
,COUNT(DISTINCT CCAN.ROWID) OVER (PARTITION BY HDI.ROWID) CCAN_COUNT
,MAX(CCAN.ID1) OVER (PARTITION BY HDI.ROWID) CCAN_ID -- Customer Contracts Admin ID based on Name
,GL.CURRENCY_CODE OU_LEDGER_CUR_CODE
,HDI.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
,trunc(HDI.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
,HDI.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE
FROM OKS_HEADERS_INTERFACE HDI
,HZ_PARTIES HPCUSTI
,HZ_PARTIES HPCUSTN
,HZ_PARTIES HPCUSTNUM
,HZ_PARTIES HPTPI
,HZ_PARTIES HPTPN
,HZ_PARTIES HPTPNUM
,JTF_RS_SALESREPS SPI
,(SELECT SP.ROWID ROW_ID, SPTL.RESOURCE_NAME, SP.ORG_ID, SP.SALESREP_ID
FROM JTF_RS_RESOURCE_EXTNS_TL SPTL, JTF_RS_SALESREPS SP
WHERE SP.RESOURCE_ID = SPTL.RESOURCE_ID
AND SPTL.LANGUAGE = USERENV('LANG')) SPN
,OE_AGREEMENTS_B AGI
,OE_AGREEMENTS_TL AGTLN
,QP_LIST_HEADERS_B QPI
,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
AND QPTL.LANGUAGE = USERENV('LANG')) QPTLN
,QP_LIST_HEADERS_B RNPLI
,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
AND QPTL.LANGUAGE = USERENV('LANG')) RNPLN
,RA_RULES ARI
,RA_RULES ARN
,RA_RULES IRI
,RA_RULES IRN
,RA_TERMS_B PTI
,RA_TERMS_TL PTTLN
,OKC_K_GROUPS_B CGI
,OKC_K_GROUPS_TL CGTLN
,OKC_K_GROUPS_B CGBN
,OKX_PARTY_CONTACTS_V CCAI
,OKX_PARTY_CONTACTS_V CCAN
,HR_ORGANIZATION_INFORMATION HOIPL
,GL_LEDGERS GL
WHERE HDI.CUSTOMER_PARTY_ID = HPCUSTI.PARTY_ID (+)
AND HDI.CUSTOMER_PARTY_NAME = HPCUSTN.PARTY_NAME (+)
AND HDI.CUSTOMER_PARTY_NUMBER = HPCUSTNUM.PARTY_NUMBER (+)
AND HDI.THIRD_PARTY_ID = HPTPI.PARTY_ID (+)
AND HDI.THIRD_PARTY_NAME = HPTPN.PARTY_NAME (+)
AND HDI.THIRD_PARTY_NUMBER = HPTPNUM.PARTY_NUMBER (+)
AND HDI.SALESPERSON_ID = SPI.SALESREP_ID (+)
AND HDI.OPERATING_UNIT_ID = SPI.ORG_ID (+)
AND HDI.SALESPERSON_NAME = SPN.RESOURCE_NAME (+)
AND HDI.OPERATING_UNIT_ID = SPN.ORG_ID (+)
AND HDI.AGREEMENT_ID = AGI.AGREEMENT_ID (+)
AND HDI.AGREEMENT_NAME = AGTLN.NAME (+)
AND AGTLN.LANGUAGE(+) = USERENV('LANG')
AND HDI.PRICE_LIST_ID = QPI.LIST_HEADER_ID (+)
/* AND HDI.CONTRACT_CURRENCY_CODE = QPI.CURRENCY_CODE (+) skuchima 11880769 */
AND HDI.PRICE_LIST_NAME = QPTLN.NAME (+)
/* AND HDI.CONTRACT_CURRENCY_CODE = QPTLN.CURRENCY_CODE (+) skuchima 11880769 */
--AND QPTLN.VERSION_NO (+) = 1
AND HDI.RENEWAL_PRICE_LIST_ID = RNPLI.LIST_HEADER_ID (+)
/* AND HDI.CONTRACT_CURRENCY_CODE = RNPLI.CURRENCY_CODE (+) skuchima 11880769 */
AND HDI.RENEWAL_PRICE_LIST_NAME = RNPLN.NAME (+)
/* AND HDI.CONTRACT_CURRENCY_CODE = RNPLN.CURRENCY_CODE (+) skuchima 11880769 */
--AND RNPLN.VERSION_NO (+) = 1
AND HDI.ACCOUNTING_RULE_ID = ARI.RULE_ID (+)
AND HDI.ACCOUNTING_RULE_NAME = ARN.NAME (+)
AND HDI.INVOICING_RULE_ID = IRI.RULE_ID (+)
AND IRI.TYPE(+) = 'I'
AND HDI.INVOICING_RULE_NAME = IRN.NAME (+)
AND IRN.TYPE(+) = 'I'
AND HDI.PAYMENT_TERMS_ID = PTI.TERM_ID (+)
AND HDI.PAYMENT_TERMS_NAME = PTTLN.NAME (+)
AND PTTLN.LANGUAGE(+) = USERENV('LANG')
AND HDI.CONTRACT_GROUP_ID = CGI.ID (+)
AND HDI.CONTRACT_GROUP_NAME = CGTLN.NAME (+)
AND CGTLN.LANGUAGE(+) = USERENV('LANG')
AND CGTLN.ID = CGBN.ID(+)
AND ((CGBN.PUBLIC_YN = 'Y' OR CGBN.USER_ID = FND_GLOBAL.USER_ID) OR (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID)) /*modified for bug:6839334*/
AND HDI.CUSTOMER_PARTY_ID = CCAI.PARTY_ID (+)
AND HDI.CUSTOMER_CONTRACT_ADMIN_ID = CCAI.ID1 (+)
AND HDI.CUSTOMER_PARTY_ID = CCAN.PARTY_ID (+)
AND HDI.CUSTOMER_CONTRACT_ADMIN_NAME = CCAN.NAME (+)
AND HDI.OPERATING_UNIT_ID = HOIPL.ORGANIZATION_ID (+)
AND HOIPL.ORG_INFORMATION_CONTEXT (+)= 'Operating Unit Information'
AND HOIPL.ORG_INFORMATION3 = GL.LEDGER_ID (+)
AND HDI.ROWID between P_rowid_from AND P_rowid_to
AND HDI.batch_id = P_batch_id
AND (HDI.INTERFACE_STATUS IS NULL OR HDI.INTERFACE_STATUS = 'R')) NAMEID_Q
WHERE OHI.HEADER_INTERFACE_ID = NAMEID_Q.HEADER_INTERFACE_ID
AND OHI.OPERATING_UNIT_ID = HOI.ORGANIZATION_ID (+)
AND OHI.OPERATING_UNIT_ID = HOU.ORGANIZATION_ID (+)
AND HOI.ORG_INFORMATION_CONTEXT (+)= 'CLASS'
AND HOI.ORG_INFORMATION1 (+)= 'OPERATING_UNIT'
AND HOI.ORG_INFORMATION2 (+)= 'Y'
AND OHI.BILL_TO_SITE_USAGE_ID = BTSUI.SITE_USE_ID (+)
AND BTSUI.SITE_USE_CODE (+)= 'BILL_TO'
AND OHI.OPERATING_UNIT_ID = BTSUI.ORG_ID (+)
AND OHI.SHIP_TO_SITE_USAGE_ID = STSUI.SITE_USE_ID (+)
AND STSUI.SITE_USE_CODE (+)= 'SHIP_TO'
AND OHI.OPERATING_UNIT_ID = STSUI.ORG_ID (+)
AND OHI.CONTRACT_CURRENCY_CODE = FCUR.CURRENCY_CODE (+)
AND OHI.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE (+)
AND NAMEID_Q.CURRENCY_CONVERSION_TYPE = GLDR.CONVERSION_TYPE (+)
AND NAMEID_Q.CURRENCY_CONVERSION_DATE = GLDR.CONVERSION_DATE (+)
AND NAMEID_Q.CONTRACT_CURRENCY_CODE = GLDR.FROM_CURRENCY (+)
AND NAMEID_Q.OU_LEDGER_CUR_CODE = GLDR.TO_CURRENCY (+)
AND OHI.PAYMENT_INSTRUCTION = FL.LOOKUP_CODE (+)
AND FL.LOOKUP_TYPE (+) = 'OKS_PAYMENT_INST_TYPE'
AND OHI.COMMITMENT_ID = CNI.CUSTOMER_TRX_ID (+)
AND OHI.PAYMENT_METHOD_CODE = PMC.LOOKUP_CODE (+)
AND PMC.LOOKUP_TYPE (+) = 'OKS_PAYMENT_METHODS'
AND PMC.LOOKUP_CODE (+) = 'COM'
AND OHI.ROWID between P_rowid_from AND P_rowid_to
AND OHI.batch_id = P_batch_id
AND (OHI.INTERFACE_STATUS IS NULL OR OHI.INTERFACE_STATUS = 'R');
SELECT count(1) INTO l_int_count FROM OKS_HEADERS_INTERFACE
WHERE ROWID between P_rowid_from AND P_rowid_to
AND batch_id = P_batch_id
AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R');
SELECT count(1) INTO l_stg_count FROM OKS_INT_HEADER_STG_TEMP;
'Number of records inserted into staging table = '|| l_stg_count);
INSERT ALL
WHEN (CONTRACT_NUMBER IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_NUM'
,NULL)
WHEN (NUMBER_COUNT_IN_HDR > 0) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NONUNIQ_NUM_HDR'
,NULL)
WHEN (NUMBER_NOTUNIQ_IN_INT = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NONUNIQ_NUM_INT'
,NULL)
WHEN (CAT_COUNT <> 1) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CAT'
,NULL)
WHEN (DATE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DATE'
,NULL)
WHEN (STATUS_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_STATUS'
,NULL)
WHEN (STATUS_X_DATE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_STATUS_X_DATE'
,NULL)
WHEN (DATE_APPROVED_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DATE_APRV'
,NULL)
WHEN (DATE_APPROVED_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_DATE_APRV'
,NULL)
WHEN (DATE_CANCELED_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DATE_CANC'
,NULL)
WHEN (DATE_CANCELED_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_DATE_CANC'
,NULL)
WHEN (CR_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_CANC_RSN'
,NULL)
WHEN (CR_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_CANC_RSN'
,NULL)
WHEN (DATE_SIGNED_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DATE_SGND'
,NULL)
WHEN (DATE_SIGNED_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_DATE_SGND'
,NULL)
WHEN (RENP_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_REN_PROC'
,NULL)
WHEN (RENUPTO_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_REN_UPTO'
,NULL)
WHEN (RENUPTO_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_REN_UPTO'
,NULL)
WHEN (APRV_REQ_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_APRV_REQ'
,NULL)
WHEN (APRV_REQ_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_APRV_REQ'
,NULL)
WHEN (PRM_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_PRC_METHOD'
,NULL)
WHEN (PRM_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_PRC_METHOD'
,NULL)
WHEN (MKP_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_MARKUP'
,NULL)
WHEN (MKP_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_MARKUP'
,NULL)
WHEN (BP_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_BILLPROF'
,NULL)
WHEN (RENPOREQ_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_RENPO_REQ'
,NULL)
WHEN (nvl(RENEWAL_GRACE_DURATION, 1) <= 0 OR floor(nvl(RENEWAL_GRACE_DURATION, 1)) <> nvl(RENEWAL_GRACE_DURATION, 1)) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_RENGR_DUR'
,NULL)
WHEN (RENGRPER_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_RENGR_PER'
,NULL)
WHEN (RENGRPER_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_RENGR_PER'
,NULL)
/* WHEN (nvl(RENEWAL_ESTIMATED_PERCENT, 1) < 0 OR nvl(RENEWAL_ESTIMATED_PERCENT, 1) > 100) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_EST_PERC'
,NULL) */
/* WHEN (nvl(RENEWAL_ESTIMATED_DURATION, 1) <= 0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_EST_DUR'
,NULL) */
WHEN (REN_EST_DURPER_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_DATE_EST'
,NULL)
WHEN (REN_EST_DURPER_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_REN_EST'
,NULL)
SELECT HST.CONTRACT_NUMBER CONTRACT_NUMBER
,HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,COUNT(DISTINCT HDR.ROWID) OVER (PARTITION BY HST.ROWID) NUMBER_COUNT_IN_HDR
,(CASE WHEN EXISTS (SELECT 1 FROM OKS_HEADERS_INTERFACE
WHERE HST.CONTRACT_NUMBER = CONTRACT_NUMBER
AND nvl(HST.CONTRACT_NUMBER_MODIFIER, 'XgArBaGe!@#') = nvl(CONTRACT_NUMBER_MODIFIER (+), 'XgArBaGe!@#')
AND HST.HEADER_INTERFACE_ID <> HEADER_INTERFACE_ID (+))
THEN 'Y' ELSE 'N'
END) NUMBER_NOTUNIQ_IN_INT
,COUNT(DISTINCT CAT.ROWID) OVER (PARTITION BY HST.ROWID) CAT_COUNT
,(CASE WHEN HST.START_DATE IS NULL OR HST.END_DATE IS NULL OR HST.START_DATE > HST.END_DATE
THEN 'N' ELSE 'Y' END) DATE_VALID
,(CASE WHEN STS.STE_CODE IN ('ENTERED', 'ACTIVE', 'CANCELLED', 'SIGNED', 'EXPIRED')
THEN 'Y' ELSE 'N' END) STATUS_VALID
,(CASE WHEN (HST.START_DATE > SYSDATE AND STS.STE_CODE IN ('ACTIVE', 'EXPIRED'))
OR (HST.END_DATE <= SYSDATE AND STS.STE_CODE = 'SIGNED')
OR (SYSDATE between HST.START_DATE and HST.END_DATE AND STS.STE_CODE IN ('SIGNED', 'EXPIRED'))
THEN 'N' ELSE 'Y' END) STATUS_X_DATE_VALID
,(CASE WHEN (HST.DATE_APPROVED IS NULL AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
THEN 'N' ELSE 'Y' END) DATE_APPROVED_VALID
,(CASE WHEN (HST.DATE_APPROVED IS NOT NULL AND STS.STE_CODE = 'ENTERED')
THEN 'Y' ELSE 'N' END) DATE_APPROVED_NULL_ERROR
,(CASE WHEN (HST.DATE_CANCELED IS NULL AND STS.STE_CODE = 'CANCELLED')
THEN 'N' ELSE 'Y' END) DATE_CANCELED_VALID
,(CASE WHEN (HST.DATE_CANCELED IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
THEN 'Y' ELSE 'N' END) DATE_CANCELED_NULL_ERROR
,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
)
THEN 'N' ELSE 'Y' END) CR_VALID
,(CASE WHEN (STS.STE_CODE <> 'CANCELLED' AND HST.CANCELLATION_REASON IS NOT NULL)
THEN 'Y' ELSE 'N' END) CR_NULL_ERROR
,(CASE WHEN (HST.DATE_SIGNED IS NULL AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
THEN 'N' ELSE 'Y' END) DATE_SIGNED_VALID
,(CASE WHEN (HST.DATE_SIGNED IS NOT NULL AND STS.STE_CODE = 'ENTERED')
THEN 'Y' ELSE 'N' END) DATE_SIGNED_NULL_ERROR
,(CASE WHEN (HST.RENEWAL_PROCESS IS NOT NULL
AND COUNT(DISTINCT RENP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
THEN 'N' ELSE 'Y' END) RENP_VALID
,(CASE WHEN (HST.RENEWAL_PROCESS = 'EVN' AND (HST.RENEW_UP_TO IS NULL /*OR HST.RENEW_UP_TO <= HST.END_DATE*/))
THEN 'N' ELSE 'Y' END) RENUPTO_VALID
,(CASE WHEN (HST.RENEW_UP_TO IS NOT NULL AND HST.RENEWAL_PROCESS <> 'EVN')
THEN 'Y' ELSE 'N' END) RENUPTO_NULL_ERROR
,(CASE WHEN (HST.RENEWAL_PROCESS IN ('EVN', 'NSR')
AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKS_REN_MANUAL_APPROVAL'
AND LOOKUP_CODE = HST.APPROVAL_REQUIRED))
OR (HST.RENEWAL_PROCESS = 'ERN'
AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKS_REN_ONLINE_APPROVAL'
AND LOOKUP_CODE = HST.APPROVAL_REQUIRED))
THEN 'N' ELSE 'Y' END) APRV_REQ_VALID
,(CASE WHEN (HST.APPROVAL_REQUIRED IS NOT NULL AND (HST.RENEWAL_PROCESS IS NULL OR HST.RENEWAL_PROCESS = 'DNR'))
THEN 'Y' ELSE 'N' END) APRV_REQ_NULL_ERROR
,(CASE WHEN (HST.PRICING_METHOD IS NOT NULL AND (HST.RENEWAL_PROCESS IN ('EVN', 'NSR', 'ERN') OR HST.RENEWAL_PROCESS IS NULL)
AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKC_RENEWAL_PRICING_TYPE'
AND LOOKUP_CODE = HST.PRICING_METHOD))
THEN 'N' ELSE 'Y' END) PRM_VALID
,(CASE WHEN (HST.PRICING_METHOD IS NOT NULL AND HST.RENEWAL_PROCESS = 'DNR')
THEN 'Y' ELSE 'N' END) PRM_NULL_ERROR
,(CASE WHEN (HST.PRICING_METHOD = 'PCT' AND (HST.RENEWAL_MARKUP IS NULL OR HST.RENEWAL_MARKUP < -100))
THEN 'N' ELSE 'Y' END) MKP_VALID
,(CASE WHEN (HST.RENEWAL_MARKUP IS NOT NULL AND HST.PRICING_METHOD <> 'PCT')
THEN 'Y' ELSE 'N' END) MKP_NULL_ERROR
,(CASE WHEN (HST.RENEWAL_BILLING_PROFILE_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM OKS_BILLING_PROFILES_B
WHERE ID = HST.RENEWAL_BILLING_PROFILE_ID
AND (OWNED_PARTY_ID1 IS NULL
OR (OWNED_PARTY_ID1 = HST.CUSTOMER_PARTY_ID AND
BILL_TO_ADDRESS_ID1 IS NULL))
UNION
SELECT 'X'
FROM OKS_BILLING_PROFILES_B A,
HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_CUST_ACCT_SITES_ALL CA
WHERE A.ID = HST.RENEWAL_BILLING_PROFILE_ID
AND A.OWNED_PARTY_ID1 = HST.CUSTOMER_PARTY_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_ID = A.OWNED_PARTY_ID1
AND CS.SITE_USE_CODE = 'BILL_TO'
AND CS.SITE_USE_ID = A.BILL_TO_ADDRESS_ID1
AND CS.ORG_ID = HST.OPERATING_UNIT_ID))
THEN 'N' ELSE 'Y' END) BP_VALID
,(CASE WHEN (HST.RENEWAL_PO_REQUIRED IS NOT NULL
AND COUNT(DISTINCT RENPOREQ.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
THEN 'N' ELSE 'Y' END) RENPOREQ_VALID
,HST.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
,(CASE WHEN (HST.RENEWAL_GRACE_DURATION IS NOT NULL
AND COUNT(DISTINCT RENGRPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
THEN 'N' ELSE 'Y' END) RENGRPER_VALID
,(CASE WHEN (HST.RENEWAL_GRACE_PERIOD IS NOT NULL AND HST.RENEWAL_GRACE_DURATION IS NULL)
THEN 'Y' ELSE 'N' END) RENGRPER_NULL_ERROR
,HST.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
,HST.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
,(CASE WHEN (HST.RENEWAL_ESTIMATED_PERCENT IS NOT NULL
AND (HST.RENEWAL_ESTIMATED_DURATION IS NULL
OR COUNT(DISTINCT RENESTPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1))
THEN 'N' ELSE 'Y' END) REN_EST_DURPER_VALID
,(CASE WHEN ((HST.RENEWAL_ESTIMATED_PERIOD IS NOT NULL OR HST.RENEWAL_ESTIMATED_DURATION IS NOT NULL)
AND HST.RENEWAL_ESTIMATED_PERCENT IS NULL)
THEN 'Y' ELSE 'N' END) REN_EST_DURPER_NULL_ERROR
FROM OKS_INT_HEADER_STG_TEMP HST
,OKC_SUBCLASSES_B CAT
,OKC_K_HEADERS_ALL_B HDR
,OKC_STATUSES_B STS
,FND_LOOKUPS RENP
,FND_LOOKUPS RENPOREQ
,OKC_TIME_CODE_UNITS_B RENGRPER
,OKC_TIME_CODE_UNITS_B RENESTPER
,FND_LOOKUPS CR1
,FND_LOOKUPS CR2
WHERE HST.CATEGORY = CAT.CODE (+)
AND CAT.CLS_CODE(+) = 'SERVICE'
AND HST.CONTRACT_NUMBER = HDR.CONTRACT_NUMBER (+)
AND nvl(HST.CONTRACT_NUMBER_MODIFIER , 'XgArBaGe!@#') = nvl(HDR.CONTRACT_NUMBER_MODIFIER (+), 'XgArBaGe!@#')
AND HST.STATUS_CODE = STS.CODE (+)
AND HST.RENEWAL_PROCESS = RENP.LOOKUP_CODE (+)
AND RENP.LOOKUP_TYPE (+) = 'OKS_RENEWAL_TYPE'
AND RENPOREQ.LOOKUP_TYPE (+) = 'OKS_Y_N'
AND HST.RENEWAL_PO_REQUIRED = RENPOREQ.LOOKUP_CODE (+)
AND HST.RENEWAL_GRACE_PERIOD = RENGRPER.UOM_CODE (+)
AND HST.RENEWAL_ESTIMATED_PERIOD = RENESTPER.UOM_CODE (+)
AND HST.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
AND HST.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON';
INSERT ALL
WHEN (SALESGROUP_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SALES_G'
,NULL)
WHEN (QUOTE_TO_PARTY_SITE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QPS'
,NULL)
WHEN (QUOTE_TO_CONTACT_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QTC'
,NULL)
WHEN (QUOTE_TO_PHONE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QTP'
,NULL)
WHEN (QUOTE_TO_FAX_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QTF'
,NULL)
WHEN (QUOTE_TO_EMAIL_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QTE'
,NULL)
WHEN (nvl(GRACE_DURATION, 1) <= 0 OR floor(nvl(GRACE_DURATION, 1)) <> nvl(GRACE_DURATION, 1)) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_GRD'
,NULL)
WHEN (GRPER_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_GRP'
,NULL)
WHEN (GRPER_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_GRP'
,NULL)
/* WHEN (nvl(ESTIMATION_PERCENT, 1) < 0 OR nvl(ESTIMATION_PERCENT, 1) > 100) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_ESTPERC'
,NULL) */
WHEN (ESTIMATION_PERCENT IS NOT NULL AND ESTIMATION_DATE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_ESTDATE'
,NULL)
WHEN (EST_DATE_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_ESTDATE'
,NULL)
WHEN (FA_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_FA'
,NULL)
WHEN (FA_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_FA'
,NULL)
WHEN (QAC_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_QAC'
,NULL)
WHEN (AP_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_AP'
,NULL)
WHEN (SALESGROUP_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_SALESG'
,NULL)
SELECT HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,(CASE WHEN HST.SALESGROUP_ID IS NOT NULL
AND HST.SALESGROUP_ID <> -1
AND NOT EXISTS (SELECT 'X'
FROM JTF_RS_GROUP_MEMBERS MEM
,JTF_RS_SALESREPS SRP
,JTF_RS_GROUP_USAGES USG
WHERE SRP.RESOURCE_ID = MEM.RESOURCE_ID
AND MEM.GROUP_ID = USG.GROUP_ID
AND USG.USAGE = 'SALES'
AND SRP.SALESREP_ID = HST.SALESPERSON_ID
AND SRP.ORG_ID = HST.OPERATING_UNIT_ID
AND MEM.GROUP_ID = HST.SALESGROUP_ID)
THEN 'N' ELSE 'Y' END) SALESGROUP_V
,(CASE WHEN HST.SALESPERSON_ID IS NOT NULL AND HST.SALESGROUP_ID IS NULL THEN 'Y' ELSE 'N' END) SALESGROUP_NULL_ERROR
,(CASE WHEN HST.QUOTE_TO_PARTY_SITE IS NOT NULL
AND COUNT(DISTINCT QPS.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QUOTE_TO_PARTY_SITE_V
,(CASE WHEN HST.QUOTE_TO_PARTY_SITE IS NOT NULL
AND COUNT(DISTINCT QTC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QUOTE_TO_CONTACT_V
,(CASE WHEN HST.QUOTE_TO_PHONE IS NOT NULL
AND COUNT(DISTINCT QTP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QUOTE_TO_PHONE_V
,(CASE WHEN HST.QUOTE_TO_FAX IS NOT NULL
AND COUNT(DISTINCT QTF.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QUOTE_TO_FAX_V
,(CASE WHEN HST.QUOTE_TO_EMAIL IS NOT NULL
AND COUNT(DISTINCT QTE.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QUOTE_TO_EMAIL_V
,HST.GRACE_DURATION GRACE_DURATION
,(CASE WHEN (HST.GRACE_DURATION IS NOT NULL
AND COUNT(DISTINCT GRPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
THEN 'N' ELSE 'Y' END) GRPER_V
,(CASE WHEN (HST.GRACE_PERIOD IS NOT NULL AND HST.GRACE_DURATION IS NULL)
THEN 'Y' ELSE 'N' END) GRPER_NULL_ERROR
,HST.ESTIMATION_PERCENT ESTIMATION_PERCENT
,HST.ESTIMATION_DATE ESTIMATION_DATE
,(CASE WHEN (HST.ESTIMATION_DATE IS NOT NULL AND HST.ESTIMATION_PERCENT IS NULL)
THEN 'Y' ELSE 'N' END) EST_DATE_NULL_ERROR
,(CASE WHEN (HST.FOLLOW_UP_DUE_DATE IS NOT NULL
AND COUNT(DISTINCT FOA.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
THEN 'N' ELSE 'Y' END) FA_V
,(CASE WHEN (HST.FOLLOW_UP_DUE_DATE IS NULL AND HST.FOLLOW_UP_ACTION IS NOT NULL)
THEN 'Y' ELSE 'N' END) FA_NULL_ERROR
,(CASE WHEN COUNT(DISTINCT QAC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) QAC_V
,(CASE WHEN COUNT(DISTINCT AP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) AP_V
FROM OKS_INT_HEADER_STG_TEMP HST
,HZ_CUST_ACCT_SITES_ALL QPS
,HZ_CUST_ACCOUNT_ROLES QTC
,HZ_CONTACT_POINTS QTP
,HZ_CONTACT_POINTS QTF
,HZ_CONTACT_POINTS QTE
,OKC_TIME_CODE_UNITS_B GRPER
,FND_LOOKUPS FOA
,OKC_QA_CHECK_LISTS_B QAC
,OKC_PROCESS_DEFS_B AP
WHERE HST.OPERATING_UNIT_ID = QPS.ORG_ID (+)
AND HST.QUOTE_TO_PARTY_SITE = QPS.CUST_ACCT_SITE_ID (+)
AND HST.QUOTE_TO_CONTACT = QTC.CUST_ACCOUNT_ROLE_ID (+)
AND QTC.ROLE_TYPE (+)= 'CONTACT'
AND QTP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HST.QUOTE_TO_PHONE = QTP.CONTACT_POINT_ID (+)
AND NVL(QTP.PHONE_LINE_TYPE (+) ,'GEN') = 'GEN'
AND QTF.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HST.QUOTE_TO_FAX = QTF.CONTACT_POINT_ID (+)
AND QTF.PHONE_LINE_TYPE (+) = 'FAX'
AND QTE.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND HST.QUOTE_TO_EMAIL = QTE.CONTACT_POINT_ID (+)
AND HST.GRACE_PERIOD = GRPER.UOM_CODE (+)
AND HST.FOLLOW_UP_ACTION = FOA.LOOKUP_CODE (+)
AND FOA.LOOKUP_TYPE (+) = 'OKS_FOLLOWUP_ACTION'
AND HST.QA_CHECKLIST = QAC.ID (+)
AND HST.APPROVAL_PROCESS_ID = AP.ID (+);
INSERT ALL
WHEN (TEC_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_TEC'
,NULL)
WHEN (TUCEF_OU_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_TUCEF_OU'
,NULL)
WHEN (TEN_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_TEN'
,NULL)
WHEN (TEN_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_TEN'
,NULL)
WHEN (POREQ_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_POREQ'
,NULL)
WHEN (POREQ_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_POREQ'
,NULL)
WHEN (BS_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_BS'
,NULL)
WHEN (BTXNTYPE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_BTXNTYPE'
,NULL)
WHEN (HC_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_HC'
,NULL)
WHEN (SP_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SP'
,NULL)
WHEN (ST_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_ST'
,NULL)
WHEN (SER_CHRG_PREP_REQ_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SCPREQ'
,NULL)
WHEN (SER_CHRG_PO_REQ_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SCPOREQ'
,NULL)
WHEN (SOURCE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_INVALID_SOURCE'
,NULL)
WHEN (HOI_COUNT = 0) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HST_INVALID_INVORG'
,NULL)
WHEN (ERC_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_HEADERS_INTERFACE'
,HEADER_INTERFACE_ID
,'OKS_IMP_HDR_NULL_ERC'
,NULL)
SELECT HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,(CASE WHEN HST.TAX_EXEMPTION_CONTROL IS NOT NULL
AND COUNT(DISTINCT TEC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) TEC_V
,(CASE WHEN HST.TAX_EXEMPTION_CONTROL = 'E'
AND NOT EXISTS (SELECT 'X' FROM ZX_PRODUCT_OPTIONS
WHERE ORG_ID = HST.OPERATING_UNIT_ID
AND TAX_METHOD_CODE <> 'LTE'
AND APPLICATION_ID = 222
AND TAX_USE_CUSTOMER_EXEMPT_FLAG = 'Y')
THEN 'N' ELSE 'Y' END) TUCEF_OU_V
,(CASE WHEN HST.TAX_EXEMPTION_CONTROL = 'E'
AND COUNT(DISTINCT TEN.ROW_ID) OVER (PARTITION BY HST.ROWID) = 0
THEN 'N' ELSE 'Y' END) TEN_V
,(CASE WHEN (HST.TAX_EXEMPTION_CONTROL <> 'E' OR HST.TAX_EXEMPTION_CONTROL IS NULL)
AND HST.TAX_EXEMPTION_NUMBER IS NOT NULL
THEN 'Y' ELSE 'N' END) TEN_NULL_ERROR
,(CASE WHEN (HST.TAX_EXEMPTION_CONTROL <> 'E' OR HST.TAX_EXEMPTION_CONTROL IS NULL)
AND HST.EXEMPT_REASON_CODE IS NOT NULL
THEN 'Y' ELSE 'N' END) ERC_NULL_ERROR
,(CASE WHEN HST.PAYMENT_INSTRUCTION = 'PON' AND (HST.PO_REQUIRED NOT IN ('Y', 'N') OR HST.PO_REQUIRED IS NULL) THEN 'N' ELSE 'Y' END) POREQ_V
,(CASE WHEN HST.PO_REQUIRED IS NOT NULL AND (HST.PAYMENT_INSTRUCTION <> 'PON' OR HST.PAYMENT_INSTRUCTION IS NULL)
THEN 'Y' ELSE 'N' END) POREQ_NULL_ERROR
,(CASE WHEN HST.BILL_SERVICES IS NOT NULL
AND COUNT(DISTINCT BS.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) BS_V
,(CASE WHEN HST.BILLING_TRANSACTION_TYPE_ID IS NOT NULL
AND COUNT(DISTINCT BTXNTYPE.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) BTXNTYPE_V
,(CASE WHEN HST.HOLD_CREDITS IS NOT NULL AND HST.HOLD_CREDITS NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) HC_V
,(CASE WHEN HST.SUMMARY_PRINT IS NOT NULL AND HST.SUMMARY_PRINT NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) SP_V
,(CASE WHEN HST.SUMMARY_TRANSACTIONS IS NOT NULL AND HST.SUMMARY_TRANSACTIONS NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) ST_V
,(CASE WHEN HST.SERVICE_CHRG_PREPAY_REQ IS NOT NULL AND HST.SERVICE_CHRG_PREPAY_REQ NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) SER_CHRG_PREP_REQ_V
,(CASE WHEN HST.SERVICE_CHARGES_PO_REQUIRED IS NOT NULL AND HST.SERVICE_CHARGES_PO_REQUIRED NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) SER_CHRG_PO_REQ_V
,(CASE WHEN HST.SOURCE IS NOT NULL
AND COUNT(DISTINCT SRC.ROWID) OVER (PARTITION BY HST.ROWID) = 0
THEN 'N' ELSE 'Y' END) SOURCE_V
,COUNT(DISTINCT HOI.ROWID) OVER (PARTITION BY HST.ROWID) HOI_COUNT
FROM OKS_INT_HEADER_STG_TEMP HST
,FND_LOOKUPS TEC
,(SELECT EX.ROWID ROW_ID, EX.EXEMPT_CERTIFICATE_NUMBER, EX.EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS EX, AR_LOOKUPS ERC
WHERE EX.EXEMPT_REASON_CODE = ERC.LOOKUP_CODE (+)
AND ERC.LOOKUP_TYPE(+) = 'TAX_REASON') TEN
,FND_LOOKUPS BS
,RA_CUST_TRX_TYPES_ALL BTXNTYPE
,FND_LOOKUPS SRC
,HR_ORGANIZATION_INFORMATION HOI
WHERE HST.TAX_EXEMPTION_CONTROL = TEC.LOOKUP_CODE (+)
AND TEC.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
AND HST.TAX_EXEMPTION_NUMBER = TEN.EXEMPT_CERTIFICATE_NUMBER (+)
AND HST.EXEMPT_REASON_CODE = TEN.EXEMPT_REASON_CODE (+)
AND HST.BILL_SERVICES = BS.LOOKUP_CODE (+)
AND BS.LOOKUP_TYPE (+) = 'OKS_AR_INTERFACE'
AND HST.OPERATING_UNIT_ID = BTXNTYPE.ORG_ID (+)
AND HST.BILLING_TRANSACTION_TYPE_ID = BTXNTYPE.CUST_TRX_TYPE_ID (+)
AND BTXNTYPE.TYPE (+) = 'INV'
AND SRC.LOOKUP_TYPE (+) = 'OKC_CONTRACT_SOURCES'
AND HST.SOURCE = SRC.LOOKUP_CODE (+)
AND HST.INV_ORGANIZATION_ID = HOI.ORGANIZATION_ID (+)
AND HOI.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
AND HOI.ORG_INFORMATION1 (+) = 'INV';
INSERT ALL
WHEN (1 = 1) THEN
INTO OKS_INT_LINE_STG_TEMP
(LINE_INTERFACE_ID
,LINE_NUMBER
,HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,LINE_INTERFACE_ROWID
,LSE_ID
,LINE_TYPE
,ITEM_ORGANIZATION_ID
,ITEM_ID
,REFERENCE_TEMPLATE_ID
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_ID
,CUSTOMER_BILLING_CONTACT_ID
,CUSTOMER_SHIPPING_CONTACT_ID
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,PRICE_LIST_ID
,INVOICE_TEXT
,PRINT_INVOICE
,SUBTOTAL
,TAX_AMOUNT
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,TAX_CLASSIFICATION_CODE
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,ACCOUNTING_RULE_ID
,INVOICING_RULE_ID
,RECUR_BILL_OCCURANCES
,BILLING_INTERVAL_DURATION
,BILLING_INTERVAL_PERIOD
,FIRST_BILL_UPTO_DATE
,LAST_BILL_FROM_DATE
,SUBSCRIPTION_QUANTITY
,QUANTITY_UOM
,PRICE_UOM
,UNIT_PRICE
,FIRST_BILLED_AMOUNT
,LAST_BILLED_AMOUNT
,USAGE_TYPE
,USAGE_PERIOD
,AVERAGING_INTERVAL
,SETTLEMENT_INTERVAL
,USAGE_TERMINATION_METHOD)
VALUES (LINE_INTERFACE_ID
,LINE_NUMBER
,HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,LINE_INTERFACE_ROWID
,LSE_ID
,LINE_TYPE
,ITEM_ORGANIZATION_ID
,ITEM_ID
,REFERENCE_TEMPLATE_ID
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_ID
,CUSTOMER_BILLING_CONTACT_ID
,CUSTOMER_SHIPPING_CONTACT_ID
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,PRICE_LIST_ID
,INVOICE_TEXT
,PRINT_INVOICE
,SUBTOTAL
,TAX_AMOUNT
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,TAX_CLASSIFICATION_CODE
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,ACCOUNTING_RULE_ID
,INVOICING_RULE_ID
,RECUR_BILL_OCCURANCES
,BILLING_INTERVAL_DURATION
,BILLING_INTERVAL_PERIOD
,FIRST_BILL_UPTO_DATE
,LAST_BILL_FROM_DATE
,SUBSCRIPTION_QUANTITY
,QUANTITY_UOM
,PRICE_UOM
,UNIT_PRICE
,FIRST_BILLED_AMOUNT
,LAST_BILLED_AMOUNT
,USAGE_TYPE
,USAGE_PERIOD
,AVERAGING_INTERVAL
,SETTLEMENT_INTERVAL
,USAGE_TERMINATION_METHOD)
WHEN (LSE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_LTYPE'
,NULL)
WHEN (ITEM_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_ITEM'
,NULL)
WHEN (BILL_TO_SITE_USAGE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_BTSU'
,NULL)
WHEN (SHIP_TO_SITE_USAGE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_STSU'
,NULL)
WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND INVOICE_TEXT IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_INVTXT'
,NULL)
/*Commented for bug:9128152*/
/* WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND CUSTOMER_BILLING_CONTACT_ID IS NULL)
OR (BILLING_CONTACT_PROVIDED = 'Y' AND CUSTOMER_BILLING_CONTACT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_BC'
,NULL)
WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND CUSTOMER_SHIPPING_CONTACT_ID IS NULL)
OR (SHIPPING_CONTACT_PROVIDED = 'Y' AND CUSTOMER_SHIPPING_CONTACT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_SC'
,NULL) */
WHEN (PL_PROVIDED = 'Y' AND PRICE_LIST_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_QP'
,NULL)
WHEN (LIN_PAYMENT_INSTRUCTION IS NOT NULL AND PAYMENT_INSTRUCTION IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_PAY_INST'
,NULL)
WHEN (ACCOUNTING_RULE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_AR'
,NULL)
WHEN (INVOICING_RULE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_IR'
,NULL)
WHEN (LIN_PAYMENT_METHOD_CODE IS NOT NULL AND PAYMENT_METHOD_CODE IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_PMC'
,NULL)
WHEN ((PAYMENT_METHOD_CODE IS NOT NULL OR LIN_COMMITMENT_ID IS NOT NULL) AND COMMITMENT_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_CN'
,NULL)
WHEN (LINE_TYPE = 'SUBSCRIPTION' AND (TAX_AMOUNT IS NULL OR TAX_AMOUNT < 0 )) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TAX'
,NULL)
WHEN (LINE_TYPE = 'SUBSCRIPTION' AND (SUBTOTAL IS NULL OR SUBTOTAL < 0 )) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TOT'
,NULL)
SELECT OLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OLI.ROWID LINE_INTERFACE_ROWID
,OLI.LINE_NUMBER LINE_NUMBER
,(CASE WHEN COUNT(DISTINCT LS.ROW_ID) OVER (PARTITION BY OLI.ROWID) = 1
AND ((HDR.CATEGORY = 'SERVICE' AND OLI.LINE_TYPE IN ('SERVICE', 'USAGE'))
OR (HDR.CATEGORY = 'WARRANTY' AND OLI.LINE_TYPE IN ('EXT_WARRANTY', 'WARRANTY'))
OR (HDR.CATEGORY = 'SUBSCRIPTION' AND OLI.LINE_TYPE IN ('SERVICE', 'SUBSCRIPTION', 'USAGE')))
THEN LS.ID
ELSE NULL
END) LSE_ID
,OLI.LINE_TYPE LINE_TYPE
,OLI.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID
,(CASE WHEN NAMEID_Q.MSI_COUNT = 1 AND NAMEID_Q.MSI_V = 'Y' THEN NAMEID_Q.MSI_ID
WHEN NAMEID_Q.MSN_COUNT = 1 AND NAMEID_Q.MSN_V = 'Y' THEN NAMEID_Q.MSN_ID
ELSE NULL
END) ITEM_ID
,(CASE WHEN OLI.REFERENCE_TEMPLATE_ID IS NOT NULL THEN OLI.REFERENCE_TEMPLATE_ID
WHEN NAMEID_Q.MSI_COUNT = 1 AND NAMEID_Q.MSI_V = 'Y' THEN NAMEID_Q.MSI_REF_TEMPLATE_ID
WHEN NAMEID_Q.MSN_COUNT = 1 AND NAMEID_Q.MSN_V = 'Y' THEN NAMEID_Q.MSN_REF_TEMPLATE_ID
ELSE NULL
END) REFERENCE_TEMPLATE_ID
,RTRIM(OLI.LINE_REFERENCE) LINE_REFERENCE
,OLI.STATUS_CODE STATUS_CODE
,OLI.START_DATE START_DATE
,OLI.END_DATE END_DATE
,(CASE WHEN NAMEID_Q.BTSUI_COUNT = 1 AND NAMEID_Q.BTSUI_V = 'Y' THEN NAMEID_Q.BTSUI_ID
WHEN NAMEID_Q.BTSUN_COUNT = 1 THEN NAMEID_Q.BTSUN_ID
ELSE NULL END) BILL_TO_SITE_USAGE_ID
,(CASE WHEN NAMEID_Q.STSUI_COUNT = 1 AND NAMEID_Q.STSUI_V = 'Y' THEN NAMEID_Q.STSUI_ID
WHEN NAMEID_Q.STSUN_COUNT = 1 THEN NAMEID_Q.STSUN_ID
ELSE NULL END) SHIP_TO_SITE_USAGE_ID
,(CASE WHEN NAMEID_Q.BCI_COUNT = 1 THEN NAMEID_Q.BCI_ID
/* WHEN NAMEID_Q.BCN_COUNT = 1 THEN NAMEID_Q.BCN_ID */
ELSE NULL
END) CUSTOMER_BILLING_CONTACT_ID
,(CASE WHEN OLI.BILLING_CONTACT_ID IS NOT NULL /*OR OLI.BILLING_CONTACT_NAME IS NOT NULL*/ THEN 'Y' ELSE 'N' END) BILLING_CONTACT_PROVIDED
,(CASE WHEN OLI.SHIPPING_CONTACT_ID IS NOT NULL /*OR OLI.SHIPPING_CONTACT_NAME IS NOT NULL*/ THEN 'Y' ELSE 'N' END) SHIPPING_CONTACT_PROVIDED
,(CASE WHEN NAMEID_Q.SCI_COUNT = 1 THEN NAMEID_Q.SCI_ID
/* WHEN NAMEID_Q.SCN_COUNT = 1 THEN NAMEID_Q.SCN_ID */
ELSE NULL
END) CUSTOMER_SHIPPING_CONTACT_ID
,OLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,OLI.CANCELLATION_DATE CANCELLATION_DATE
,OLI.CANCELLATION_REASON CANCELLATION_REASON
,(CASE WHEN NAMEID_Q.QPI_V = 'Y' AND NAMEID_Q.QPI_COUNT = 1 THEN NAMEID_Q.QPI_ID
WHEN NAMEID_Q.QPTLN_V = 'Y' AND NAMEID_Q.QPN_COUNT = 1 THEN NAMEID_Q.QPN_ID
ELSE NULL
END) PRICE_LIST_ID
,(CASE WHEN OLI.PRICE_LIST_ID IS NOT NULL OR OLI.PRICE_LIST_NAME IS NOT NULL THEN 'Y' ELSE 'N' END) PL_PROVIDED
,rtrim(OLI.INVOICE_TEXT) INVOICE_TEXT
,OLI.PRINT_INVOICE PRINT_INVOICE
,OLI.SUBTOTAL SUBTOTAL
,OLI.TAX_AMOUNT TAX_AMOUNT
,OLI.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
,OLI.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
,OLI.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
,(CASE WHEN COUNT(DISTINCT FL.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
THEN OLI.PAYMENT_INSTRUCTION
ELSE NULL
END) PAYMENT_INSTRUCTION
,OLI.PAYMENT_INSTRUCTION LIN_PAYMENT_INSTRUCTION
,OLI.PO_REQUIRED PO_REQUIRED
,rtrim(OLI.PAYMENT_INSTRUCTION_DETAILS) PAYMENT_INSTRUCTION_DETAILS
,(CASE WHEN COUNT(DISTINCT PMC.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
THEN OLI.PAYMENT_METHOD_CODE
ELSE NULL
END) PAYMENT_METHOD_CODE
,OLI.PAYMENT_METHOD_CODE LIN_PAYMENT_METHOD_CODE
,(CASE WHEN COUNT(DISTINCT CNI.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
THEN OLI.COMMITMENT_ID
ELSE NULL
END) COMMITMENT_ID
,OLI.COMMITMENT_ID LIN_COMMITMENT_ID
,(CASE WHEN ARI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARI_COUNT = 1 THEN NAMEID_Q.ARI_ID
WHEN ARN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARN_COUNT = 1 THEN NAMEID_Q.ARN_ID
ELSE NULL
END) ACCOUNTING_RULE_ID
,(CASE WHEN NAMEID_Q.IRI_COUNT = 1 THEN NAMEID_Q.IRI_ID
WHEN NAMEID_Q.IRN_COUNT = 1 THEN NAMEID_Q.IRN_ID
ELSE NULL
END) INVOICING_RULE_ID
,OLI.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
,OLI.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
,trunc(OLI.FIRST_BILL_UPTO_DATE) FIRST_BILL_UPTO_DATE
,OLI.SUBSCRIPTION_QUANTITY SUBSCRIPTION_QUANTITY
,OLI.QUANTITY_UOM QUANTITY_UOM
,OLI.PRICE_UOM PRICE_UOM
,OLI.UNIT_PRICE UNIT_PRICE
,OLI.FIRST_BILLED_AMOUNT FIRST_BILLED_AMOUNT
,OLI.LAST_BILLED_AMOUNT LAST_BILLED_AMOUNT
,OLI.USAGE_TYPE USAGE_TYPE
,OLI.USAGE_PERIOD USAGE_PERIOD
,OLI.AVERAGING_INTERVAL AVERAGING_INTERVAL
,OLI.SETTLEMENT_INTERVAL SETTLEMENT_INTERVAL
,OLI.USAGE_TERMINATION_METHOD USAGE_TERMINATION_METHOD
,OLI.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
,BIP.tce_code tce_code /*Added for bug:9019205*/
,BIP.quantity quantity
,(CASE WHEN OLI.RECUR_BILL_OCCURANCES > 0 AND OLI.BILLING_INTERVAL_DURATION > 0 /*Added for bug:9019205*/
THEN (CASE WHEN bip.tce_code ='YEAR' and bip.quantity =1
THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * 12 * OLI.BILLING_INTERVAL_DURATION)
WHEN bip.tce_code ='MONTH' and bip.quantity =3
THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * 3 * OLI.BILLING_INTERVAL_DURATION)
WHEN bip.tce_code ='MONTH' and bip.quantity =1
THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * OLI.BILLING_INTERVAL_DURATION)
WHEN bip.tce_code ='DAY' and bip.quantity =7
THEN NAMEID_Q.BILL_START_DATE + OLI.RECUR_BILL_OCCURANCES * 7 * OLI.BILLING_INTERVAL_DURATION
WHEN bip.tce_code ='DAY' and bip.quantity =1
THEN NAMEID_Q.BILL_START_DATE + OLI.RECUR_BILL_OCCURANCES * OLI.BILLING_INTERVAL_DURATION
ELSE NULL
END)
ELSE NULL
END) LAST_BILL_FROM_DATE
FROM OKS_LINES_INTERFACE OLI
,OKS_INT_HEADER_STG_TEMP HDR
,FND_LOOKUPS FL
,RA_CUSTOMER_TRX_ALL CNI
,FND_LOOKUPS PMC
,(SELECT LSE.ROWID ROW_ID, LSE.LTY_CODE, LSE.ID ID
FROM OKC_LINE_STYLES_B LSE, FND_APPLICATION FA
WHERE LSE.APPLICATION_ID = FA.APPLICATION_ID
AND FA.APPLICATION_SHORT_NAME = 'OKS') LS
,okc_time_code_units_b BIP
,okc_time_code_units_tl BIPTL /*Added for bug:9019205*/
,(SELECT distinct(LNI.LINE_INTERFACE_ID)
,COUNT(DISTINCT QPI.ROWID) OVER (PARTITION BY LNI.ROWID) QPI_COUNT
,MAX(LNI.PRICE_LIST_ID) OVER (PARTITION BY LNI.ROWID) QPI_ID -- PRICE LIST ID based on Id
,(CASE WHEN HST.CONTRACT_CURRENCY_CODE = QPI.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) QPI_V --skuchima bug 11880769
,COUNT(DISTINCT QPTLN.ROW_ID) OVER (PARTITION BY LNI.ROWID) QPN_COUNT
,MAX(QPTLN.LIST_HEADER_ID) OVER (PARTITION BY LNI.ROWID) QPN_ID -- PRICE LIST ID based on Name
,(CASE WHEN HST.CONTRACT_CURRENCY_CODE = QPTLN.CURRENCY_CODE THEN 'Y'
WHEN l_qp_mc='Y' THEN 'Y'
ELSE 'N' END) QPTLN_V --skuchima bug 11880769
,COUNT(DISTINCT ARI.ROWID) OVER (PARTITION BY LNI.ROWID) ARI_COUNT
,MAX(LNI.ACCOUNTING_RULE_ID) OVER (PARTITION BY LNI.ROWID) ARI_ID -- Accounting Rule ID based on Id
,(CASE WHEN ARI.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
THEN 'Y'
ELSE 'N'
END) ARI_ID_VAL_FLAG
,COUNT(DISTINCT ARN.ROWID) OVER (PARTITION BY LNI.ROWID) ARN_COUNT
,MAX(ARN.RULE_ID) OVER (PARTITION BY LNI.ROWID) ARN_ID -- Accounting Rule ID based on Name
,(CASE WHEN ARN.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
THEN 'Y'
ELSE 'N'
END) ARN_ID_VAL_FLAG
,COUNT(DISTINCT IRI.ROWID) OVER (PARTITION BY LNI.ROWID) IRI_COUNT
,MAX(LNI.INVOICING_RULE_ID) OVER (PARTITION BY LNI.ROWID) IRI_ID -- Invoicing Rule ID based on Id
,COUNT(DISTINCT IRN.ROWID) OVER (PARTITION BY LNI.ROWID) IRN_COUNT
,MAX(IRN.RULE_ID) OVER (PARTITION BY LNI.ROWID) IRN_ID -- Invoicing Rule ID based on Name
,COUNT(DISTINCT MSI.ROWID) OVER (PARTITION BY LNI.ROWID) MSI_COUNT
,MAX(LNI.ITEM_ID) OVER (PARTITION BY LNI.ROWID) MSI_ID -- Item id based on id
,(CASE WHEN (LNI.LINE_TYPE = 'SERVICE'
AND (MSI.VENDOR_WARRANTY_FLAG = 'Y' OR nvl(MSI.SERVICE_ITEM_FLAG, 'N') <> 'Y'))
OR (LNI.LINE_TYPE = 'WARRANTY' AND MSI.VENDOR_WARRANTY_FLAG = 'N')
OR (LNI.LINE_TYPE = 'USAGE' AND nvl(MSI.USAGE_ITEM_FLAG, 'N') <> 'Y')
OR (LNI.LINE_TYPE = 'SUBSCRIPTION'
AND (nvl(MSI.CONTRACT_ITEM_TYPE_CODE,'A') <> 'SUBSCRIPTION'
OR nvl(MSI.INVOICE_ENABLED_FLAG, 'N') <> 'Y'
OR (nvl(MSI.CUSTOMER_ORDER_ENABLED_FLAG, 'N') = 'N' AND nvl(MSI.INTERNAL_ORDER_ENABLED_FLAG, 'N') = 'N')))
THEN 'N' ELSE 'Y' END) MSI_V
,MAX(MSI.COVERAGE_SCHEDULE_ID) OVER (PARTITION BY LNI.ROWID) MSI_REF_TEMPLATE_ID
,COUNT(DISTINCT MSN.ROWID) OVER (PARTITION BY LNI.ROWID) MSN_COUNT
,MAX(MSN.INVENTORY_ITEM_ID) OVER (PARTITION BY LNI.ROWID) MSN_ID -- Item id based on Name
,(CASE WHEN (LNI.LINE_TYPE = 'SERVICE'
AND (MSN.VENDOR_WARRANTY_FLAG = 'Y' OR nvl(MSN.SERVICE_ITEM_FLAG, 'N') <> 'Y'))
OR (LNI.LINE_TYPE = 'WARRANTY' AND MSN.VENDOR_WARRANTY_FLAG = 'N')
OR (LNI.LINE_TYPE = 'USAGE' AND nvl(MSN.USAGE_ITEM_FLAG, 'N') <> 'Y')
OR (LNI.LINE_TYPE = 'SUBSCRIPTION'
AND (nvl(MSN.CONTRACT_ITEM_TYPE_CODE,'A') <> 'SUBSCRIPTION'
OR nvl(MSN.INVOICE_ENABLED_FLAG, 'N') <> 'Y'
OR (nvl(MSN.CUSTOMER_ORDER_ENABLED_FLAG, 'N') = 'N' AND nvl(MSN.INTERNAL_ORDER_ENABLED_FLAG, 'N') = 'N')))
THEN 'N' ELSE 'Y' END) MSN_V
,MAX(MSN.COVERAGE_SCHEDULE_ID) OVER (PARTITION BY LNI.ROWID) MSN_REF_TEMPLATE_ID
,COUNT(DISTINCT BTSUI.ROWID) OVER (PARTITION BY LNI.ROWID) BTSUI_COUNT
,MAX(LNI.BILL_TO_SITE_USAGE_ID) OVER (PARTITION BY LNI.ROWID) BTSUI_ID -- Bill To Site Usage id based on id
,(CASE WHEN HST.OPERATING_UNIT_ID = BTSUI.ORG_ID THEN 'Y' ELSE 'N' END) BTSUI_V
,COUNT(DISTINCT STSUI.ROWID) OVER (PARTITION BY LNI.ROWID) STSUI_COUNT
,MAX(LNI.SHIP_TO_SITE_USAGE_ID) OVER (PARTITION BY LNI.ROWID) STSUI_ID -- Ship To Site Usage id based on id
,(CASE WHEN HST.OPERATING_UNIT_ID = STSUI.ORG_ID THEN 'Y' ELSE 'N' END) STSUI_V
,decode(trunc(LNI.FIRST_BILL_UPTO_DATE), NULL, LNI.START_DATE, trunc(LNI.FIRST_BILL_UPTO_DATE) + 1) BILL_START_DATE
,COUNT(DISTINCT BCI.ROW_ID) OVER (PARTITION BY LNI.ROWID) BCI_COUNT
,MAX(LNI.BILLING_CONTACT_ID) OVER (PARTITION BY LNI.ROWID) BCI_ID -- Billing contact id based on id
,COUNT(DISTINCT SCI.ROW_ID) OVER (PARTITION BY LNI.ROWID) SCI_COUNT
,MAX(LNI.SHIPPING_CONTACT_ID) OVER (PARTITION BY LNI.ROWID) SCI_ID -- Shipping contact id based on id
,COUNT(DISTINCT BTSUN.ROW_ID) OVER (PARTITION BY LNI.ROWID) BTSUN_COUNT
,MAX(BTSUN.SITE_USE_ID) OVER (PARTITION BY LNI.ROWID) BTSUN_ID -- Bill To Site Usage id based on Name
,COUNT(DISTINCT STSUN.ROW_ID) OVER (PARTITION BY LNI.ROWID) STSUN_COUNT
,MAX(STSUN.SITE_USE_ID) OVER (PARTITION BY LNI.ROWID) STSUN_ID -- Ship To Site Usage id based on Name
/*,COUNT(DISTINCT BCN.ROW_ID) OVER (PARTITION BY LNI.ROWID) BCN_COUNT
,MAX(BCN.CONTACT_ID) OVER (PARTITION BY LNI.ROWID) BCN_ID -- Billing contact id based on Name
,COUNT(DISTINCT SCN.ROW_ID) OVER (PARTITION BY LNI.ROWID) SCN_COUNT
,MAX(SCN.CONTACT_ID) OVER (PARTITION BY LNI.ROWID) SCN_ID -- Shipping contact id based on Name */
FROM OKS_LINES_INTERFACE LNI
,OKS_INT_HEADER_STG_TEMP HST
,QP_LIST_HEADERS_B QPI
,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
AND QPTL.LANGUAGE = USERENV('LANG')) QPTLN
,RA_RULES ARI
,RA_RULES ARN
,RA_RULES IRI
,RA_RULES IRN
,MTL_SYSTEM_ITEMS_B MSI
,MTL_SYSTEM_ITEMS_B_KFV MSN
,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND R.PARTY_ID = CAR.PARTY_ID
AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND P.PARTY_ID = R.SUBJECT_ID
AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND R.DIRECTIONAL_FLAG = 'F') BCI
,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND R.PARTY_ID = CAR.PARTY_ID
AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND P.PARTY_ID = R.SUBJECT_ID
AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND R.DIRECTIONAL_FLAG = 'F') SCI
/*,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND R.PARTY_ID = CAR.PARTY_ID
AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND P.PARTY_ID = R.SUBJECT_ID
AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND R.DIRECTIONAL_FLAG = 'F') BCN
,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND R.PARTY_ID = CAR.PARTY_ID
AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND P.PARTY_ID = R.SUBJECT_ID
AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
AND R.DIRECTIONAL_FLAG = 'F') SCN */
,HZ_CUST_SITE_USES_ALL BTSUI
,HZ_CUST_SITE_USES_ALL STSUI
,(SELECT CSU.ROWID ROW_ID, CSU.SITE_USE_ID, CSU.LOCATION, HT.HEADER_INTERFACE_ID
FROM HZ_CUST_SITE_USES_ALL CSU, HZ_CUST_ACCT_SITES_ALL CAS, HZ_PARTY_SITES PS, OKS_INT_HEADER_STG_TEMP HT
WHERE CSU.SITE_USE_CODE = 'BILL_TO'
AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND PS.PARTY_ID = HT.CUSTOMER_PARTY_ID
AND CSU.ORG_ID = HT.OPERATING_UNIT_ID) BTSUN
,(SELECT CSU.ROWID ROW_ID, CSU.SITE_USE_ID, CSU.LOCATION, HT.HEADER_INTERFACE_ID
FROM HZ_CUST_SITE_USES_ALL CSU, HZ_CUST_ACCT_SITES_ALL CAS, HZ_PARTY_SITES PS, OKS_INT_HEADER_STG_TEMP HT
WHERE CSU.SITE_USE_CODE = 'SHIP_TO'
AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
AND PS.PARTY_ID = HT.CUSTOMER_PARTY_ID
AND CSU.ORG_ID = HT.OPERATING_UNIT_ID) STSUN
WHERE LNI.HEADER_INTERFACE_ID = HST.HEADER_INTERFACE_ID
AND LNI.PRICE_LIST_ID = QPI.LIST_HEADER_ID (+)
AND LNI.PRICE_LIST_NAME = QPTLN.NAME (+)
--AND QPTLN.VERSION_NO (+) = 1
AND LNI.ACCOUNTING_RULE_ID = ARI.RULE_ID (+)
AND LNI.ACCOUNTING_RULE_NAME = ARN.NAME (+)
AND LNI.INVOICING_RULE_ID = IRI.RULE_ID (+)
AND IRI.TYPE(+) = 'I'
AND LNI.INVOICING_RULE_NAME = IRN.NAME (+)
AND IRN.TYPE(+) = 'I'
AND LNI.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND LNI.ITEM_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND LNI.ITEM_NAME = MSN.CONCATENATED_SEGMENTS (+)
AND LNI.ITEM_ORGANIZATION_ID = MSN.ORGANIZATION_ID (+)
AND LNI.BILL_TO_SITE_USAGE_ID = BTSUI.SITE_USE_ID (+)
AND BTSUI.SITE_USE_CODE (+)= 'BILL_TO'
AND LNI.SHIP_TO_SITE_USAGE_ID = STSUI.SITE_USE_ID (+)
AND STSUI.SITE_USE_CODE (+)= 'SHIP_TO'
AND LNI.BILLING_CONTACT_ID = BCI.CONTACT_ID (+)
AND LNI.SHIPPING_CONTACT_ID = SCI.CONTACT_ID (+)
/*AND LNI.SHIPPING_CONTACT_NAME = SCN.CONTACT_NAME (+)
AND LNI.BILLING_CONTACT_NAME = BCN.CONTACT_NAME (+) */
AND LNI.BILL_TO_SITE_USAGE_CODE = BTSUN.LOCATION (+)
AND LNI.HEADER_INTERFACE_ID = BTSUN.HEADER_INTERFACE_ID (+)
AND LNI.SHIP_TO_SITE_USAGE_CODE = STSUN.LOCATION (+)
AND LNI.HEADER_INTERFACE_ID = STSUN.HEADER_INTERFACE_ID (+)) NAMEID_Q
WHERE OLI.LINE_INTERFACE_ID = NAMEID_Q.LINE_INTERFACE_ID
AND OLI.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND OLI.PAYMENT_INSTRUCTION = FL.LOOKUP_CODE (+)
AND FL.LOOKUP_TYPE (+) = 'OKS_PAYMENT_INST_TYPE'
AND OLI.COMMITMENT_ID = CNI.CUSTOMER_TRX_ID (+)
AND OLI.LINE_TYPE = LS.LTY_CODE (+)
AND OLI.PAYMENT_METHOD_CODE = PMC.LOOKUP_CODE (+)
AND PMC.LOOKUP_TYPE (+) = 'OKS_PAYMENT_METHODS'
AND PMC.LOOKUP_CODE (+) = 'COM'
AND OLI.billing_interval_period=BIP.uom_code(+)
AND BIP.uom_code =BIPTL.uom_code
AND BIP.tce_code =BIPTL.tce_code
AND BIPTL.language(+)=USERENV('LANG'); /* Added for Bug:9019205*/
SELECT count(1) INTO l_int_count FROM OKS_LINES_INTERFACE OLI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
WHERE HEADER_INTERFACE_ID = OLI.HEADER_INTERFACE_ID);
SELECT count(1) INTO l_stg_count FROM OKS_INT_LINE_STG_TEMP;
'Number of records inserted into staging table = '|| l_stg_count);
INSERT ALL
WHEN (LINE_NUMBER_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_LINENUM'
,NULL)
WHEN (HOI_COUNT = 0) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_ITMORG'
,NULL)
WHEN (COVTEM_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_COVTEM'
,NULL)
WHEN (STATUS_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_STS'
,NULL)
WHEN (STATUS_X_DATE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_STSXDATE'
,NULL)
WHEN (STS_X_HDRSTS = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_STSXHDR'
,NULL)
WHEN (DATE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_DATE'
,NULL)
WHEN (DATE_CANCELED_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_DATECAN'
,NULL)
WHEN (DATE_CANCELED_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_DATECAN'
,NULL)
WHEN (CR_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_CR'
,NULL)
WHEN (CR_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_CR'
,NULL)
WHEN (TEC_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TEC'
,NULL)
WHEN (TUCEF_OU_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TUCEF_OU'
,NULL)
WHEN (TEN_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TEN'
,NULL)
WHEN (TEN_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_TEN'
,NULL)
WHEN (ERC_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_ERC'
,NULL)
WHEN (TCC_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_TCC'
,NULL)
WHEN (REN_TYPE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_RENTYPE'
,NULL)
WHEN (PRINT_INVOICE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_PRNTINV'
,NULL)
WHEN (POREQ_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_POREQ'
,NULL)
WHEN (POREQ_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_POREQ'
,NULL)
WHEN (TANGIBLE_SUB = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_TAN_SUB'
,NULL)
SELECT LST.LINE_INTERFACE_ID LINE_INTERFACE_ID
,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,(CASE WHEN LST.LINE_NUMBER IS NULL
OR EXISTS (SELECT 1 FROM OKS_INT_LINE_STG_TEMP LUNIQ
WHERE LST.LINE_INTERFACE_ID <> LUNIQ.LINE_INTERFACE_ID
AND LST.LINE_NUMBER = LUNIQ.LINE_NUMBER
AND LST.HEADER_INTERFACE_ID = LUNIQ.HEADER_INTERFACE_ID)
OR LST.LINE_NUMBER <= 0
OR floor(LST.LINE_NUMBER) <> LST.LINE_NUMBER
THEN 'N' ELSE 'Y' END) LINE_NUMBER_V
,COUNT(DISTINCT HOI.ROWID) OVER (PARTITION BY LST.ROWID) HOI_COUNT
,(CASE WHEN LST.LINE_TYPE <> 'USAGE' AND COUNT(DISTINCT COVTEM.ID) OVER (PARTITION BY LST.ROWID) = 0
THEN 'N' ELSE 'Y' END) COVTEM_V
,(CASE WHEN STS.STE_CODE IN ('ENTERED', 'ACTIVE', 'CANCELLED', 'SIGNED', 'EXPIRED')
THEN 'Y' ELSE 'N' END) STATUS_VALID
,(CASE WHEN (LST.START_DATE > SYSDATE AND STS.STE_CODE IN ('ACTIVE', 'EXPIRED'))
OR (LST.END_DATE <= SYSDATE AND STS.STE_CODE = 'SIGNED')
OR (SYSDATE between LST.START_DATE and LST.END_DATE AND STS.STE_CODE IN ('SIGNED', 'EXPIRED'))
THEN 'N' ELSE 'Y' END) STATUS_X_DATE_VALID
,(CASE WHEN (HDRSTS.STE_CODE = 'ENTERED' AND STS.STE_CODE IN ('ENTERED', 'CANCELLED'))
OR (HDRSTS.STE_CODE = 'ACTIVE' AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
OR (HDRSTS.STE_CODE = 'CANCELLED' AND STS.STE_CODE IN ('CANCELLED'))
OR (HDRSTS.STE_CODE = 'SIGNED' AND STS.STE_CODE IN ('SIGNED'))
OR (HDRSTS.STE_CODE = 'EXPIRED' AND STS.STE_CODE IN ('EXPIRED'))
THEN 'Y' ELSE 'N' END) STS_X_HDRSTS
,(CASE WHEN LST.START_DATE IS NULL OR LST.END_DATE IS NULL OR LST.START_DATE > LST.END_DATE
OR LST.START_DATE < HDR.START_DATE OR LST.END_DATE > HDR.END_DATE
THEN 'N' ELSE 'Y' END) DATE_VALID
,(CASE WHEN (LST.CANCELLATION_DATE IS NULL AND STS.STE_CODE = 'CANCELLED')
THEN 'N' ELSE 'Y' END) DATE_CANCELED_VALID
,(CASE WHEN (LST.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
THEN 'Y' ELSE 'N' END) DATE_CANCELED_NULL_ERROR
,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY LST.ROWID) <> 1)
)
THEN 'N' ELSE 'Y' END) CR_VALID
,(CASE WHEN (STS.STE_CODE <> 'CANCELLED' AND LST.CANCELLATION_REASON IS NOT NULL)
THEN 'Y' ELSE 'N' END) CR_NULL_ERROR
,(CASE WHEN LST.TAX_EXEMPTION_CONTROL IS NOT NULL
AND COUNT(DISTINCT TEC.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) TEC_V
,(CASE WHEN LST.TAX_EXEMPTION_CONTROL = 'E'
AND NOT EXISTS (SELECT 'X' FROM ZX_PRODUCT_OPTIONS
WHERE ORG_ID = HDR.OPERATING_UNIT_ID
AND TAX_METHOD_CODE <> 'LTE'
AND APPLICATION_ID = 222
AND TAX_USE_CUSTOMER_EXEMPT_FLAG = 'Y')
THEN 'N' ELSE 'Y' END) TUCEF_OU_V
,(CASE WHEN LST.TAX_EXEMPTION_CONTROL = 'E'
AND COUNT(DISTINCT TEN.ROW_ID) OVER (PARTITION BY LST.ROWID) = 0
THEN 'N' ELSE 'Y' END) TEN_V
,(CASE WHEN (LST.TAX_EXEMPTION_CONTROL <> 'E' OR LST.TAX_EXEMPTION_CONTROL IS NULL)
AND LST.TAX_EXEMPTION_NUMBER IS NOT NULL
THEN 'Y' ELSE 'N' END) TEN_NULL_ERROR
,(CASE WHEN (LST.TAX_EXEMPTION_CONTROL <> 'E' OR LST.TAX_EXEMPTION_CONTROL IS NULL)
AND LST.EXEMPT_REASON_CODE IS NOT NULL
THEN 'Y' ELSE 'N' END) ERC_NULL_ERROR
,(CASE WHEN LST.TAX_CLASSIFICATION_CODE IS NOT NULL
AND NOT EXISTS ( SELECT 'X' FROM ZX_OUTPUT_CLASSIFICATIONS_V
WHERE LST.TAX_CLASSIFICATION_CODE = LOOKUP_CODE
AND ORG_ID IN (HDR.OPERATING_UNIT_ID, -99))
THEN 'N' ELSE 'Y' END) TCC_VALID
,(CASE WHEN LST.RENEWAL_TYPE_CODE IS NOT NULL
AND COUNT(DISTINCT RTC.ROWID) OVER (PARTITION BY LST.ROWID) = 0
THEN 'N' ELSE 'Y' END) REN_TYPE_VALID
,(CASE WHEN LST.PRINT_INVOICE IS NOT NULL AND LST.PRINT_INVOICE NOT IN ('Y', 'N')
THEN 'N' ELSE 'Y' END) PRINT_INVOICE_V
,(CASE WHEN LST.PAYMENT_INSTRUCTION = 'PON'
AND (LST.PO_REQUIRED NOT IN ('Y', 'N') OR LST.PO_REQUIRED IS NULL) THEN 'N' ELSE 'Y' END) POREQ_V
,(CASE WHEN LST.PO_REQUIRED IS NOT NULL AND (LST.PAYMENT_INSTRUCTION <> 'PON' OR LST.PAYMENT_INSTRUCTION IS NULL)
THEN 'Y' ELSE 'N' END) POREQ_NULL_ERROR
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
AND COUNT(DISTINCT TANGIBLE.ROWID) OVER (PARTITION BY LST.ROWID) = 1
THEN 'Y' ELSE 'N' END) TANGIBLE_SUB
FROM OKS_INT_LINE_STG_TEMP LST
,OKS_INT_HEADER_STG_TEMP HDR
,HR_ORGANIZATION_INFORMATION HOI
,OKS_COVERAGE_TEMPLTS_V COVTEM
,OKC_STATUSES_B STS
,OKC_STATUSES_B HDRSTS
,FND_LOOKUPS CR1
,FND_LOOKUPS CR2
,FND_LOOKUPS TEC
,(SELECT EX.ROWID ROW_ID, EX.EXEMPT_CERTIFICATE_NUMBER, EX.EXEMPT_REASON_CODE
FROM ZX_EXEMPTIONS EX, AR_LOOKUPS ERC
WHERE EX.EXEMPT_REASON_CODE = ERC.LOOKUP_CODE (+)
AND ERC.LOOKUP_TYPE(+) = 'TAX_REASON') TEN
,FND_LOOKUPS RTC
,OKS_SUBSCR_HEADER_B TANGIBLE
WHERE LST.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND LST.ITEM_ORGANIZATION_ID = HOI.ORGANIZATION_ID (+)
AND HOI.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
AND HOI.ORG_INFORMATION1 (+) = 'INV'
AND LST.REFERENCE_TEMPLATE_ID = COVTEM.ID (+)
AND DECODE(LST.LINE_TYPE,'EXT_WARRANTY' , 'SERVICE',LST.LINE_TYPE) = COVTEM.ITEM_TYPE (+)
AND LST.STATUS_CODE = STS.CODE (+)
AND HDR.STATUS_CODE = HDRSTS.CODE (+)
AND LST.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
AND LST.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
AND LST.TAX_EXEMPTION_CONTROL = TEC.LOOKUP_CODE (+)
AND TEC.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
AND LST.TAX_EXEMPTION_NUMBER = TEN.EXEMPT_CERTIFICATE_NUMBER (+)
AND LST.EXEMPT_REASON_CODE = TEN.EXEMPT_REASON_CODE (+)
AND LST.RENEWAL_TYPE_CODE = RTC.LOOKUP_CODE (+)
AND RTC.LOOKUP_TYPE (+) = 'OKC_LINE_RENEWAL_TYPE'
AND LST.REFERENCE_TEMPLATE_ID = TANGIBLE.ID (+)
AND TANGIBLE.FULFILLMENT_CHANNEL (+) = 'OM';
INSERT ALL
WHEN (BILL_INT_DUR_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_BID'
,NULL)
WHEN (REC_BILL_OCC_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_RBO'
,NULL)
WHEN (BILL_INT_DUR_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_BID'
,NULL)
WHEN (BILL_INT_PERIOD_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_BIP'
,NULL)
WHEN (FIRST_BILL_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_FBILL'
,NULL)
WHEN (SUBQTY_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_SQTY'
,NULL)
WHEN (SUOM_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_SUOM'
,NULL)
WHEN (SUB_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_SUB'
,NULL)
WHEN (PRICE_UOM_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_PUOM'
,NULL)
WHEN (USG_TYPE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_USGTYPE'
,NULL)
WHEN (USG_TYPE_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_USGTYPE'
,NULL)
WHEN (USG_PER_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_USGPER'
,NULL)
WHEN (USG_PER_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_USGPER'
,NULL)
WHEN (AVG_INT_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_AVGINT'
,NULL)
WHEN (AVG_INT_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_AVGINT'
,NULL)
WHEN (STM_INT_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_STMINT'
,NULL)
WHEN (STM_INT_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_STMINT'
,NULL)
WHEN (UTM_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_UTM'
,NULL)
WHEN (UTM_NULL_ERROR = 'Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_NULL_UTM'
,NULL)
WHEN (FIRST_BILLED_AMT_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_FBA'
,NULL)
WHEN (FIRST_BILLED_DATE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_FBD'
,NULL)
WHEN (LAST_BILLED_AMT_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_LBA'
,NULL)
WHEN (LAST_BILL_DATE_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_LINES_INTERFACE'
,LINE_INTERFACE_ID
,'OKS_IMP_LIN_INVALID_LBD'
,NULL)
SELECT LST.LINE_INTERFACE_ID LINE_INTERFACE_ID
,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,(CASE WHEN (LST.LINE_TYPE <> 'WARRANTY' AND LST.BILLING_INTERVAL_DURATION IS NULL)
OR LST.BILLING_INTERVAL_DURATION <= 0
OR floor(LST.BILLING_INTERVAL_DURATION) <> LST.BILLING_INTERVAL_DURATION
THEN 'N' ELSE 'Y' END) BILL_INT_DUR_V
,(CASE WHEN (LST.LINE_TYPE <> 'WARRANTY' AND LST.RECUR_BILL_OCCURANCES IS NULL)
OR LST.RECUR_BILL_OCCURANCES <= 0
OR floor(LST.RECUR_BILL_OCCURANCES) <> LST.RECUR_BILL_OCCURANCES
THEN 'N' ELSE 'Y' END) REC_BILL_OCC_V
,(CASE WHEN LST.LINE_TYPE <> 'WARRANTY'
AND (COUNT(DISTINCT BIP.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
OR BIP.tce_code not IN ('DAY','MONTH', 'YEAR')) /*Modified for bug:9019205*/
THEN 'N' ELSE 'Y' END) BILL_INT_PERIOD_V
,(CASE WHEN LST.LINE_TYPE = 'WARRANTY'
AND (LST.RECUR_BILL_OCCURANCES IS NOT NULL
AND (LST.BILLING_INTERVAL_DURATION IS NULL OR LST.BILLING_INTERVAL_PERIOD IS NULL))
THEN 'Y' ELSE 'N' END) BILL_INT_DUR_NULL_ERROR
,(CASE WHEN LST.FIRST_BILL_UPTO_DATE IS NOT NULL
AND (LST.FIRST_BILL_UPTO_DATE < LST.START_DATE OR LST.FIRST_BILL_UPTO_DATE > LST.END_DATE)
THEN 'N' ELSE 'Y' END) FIRST_BILL_V
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
AND (LST.SUBSCRIPTION_QUANTITY <= 0 OR LST.SUBSCRIPTION_QUANTITY IS NULL
OR floor(LST.SUBSCRIPTION_QUANTITY) <> LST.SUBSCRIPTION_QUANTITY)
THEN 'N' ELSE 'Y' END) SUBQTY_V
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND
COUNT(DISTINCT SUOM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) SUOM_VALID
,(CASE WHEN LST.LINE_TYPE <> 'SUBSCRIPTION'
AND (LST.SUBSCRIPTION_QUANTITY IS NOT NULL OR LST.QUANTITY_UOM IS NOT NULL)
THEN 'Y' ELSE 'N' END) SUB_NULL_ERROR
,(CASE WHEN LST.PRICE_UOM IS NOT NULL
AND COUNT(DISTINCT PUOM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) PRICE_UOM_V
,(CASE WHEN (LST.LINE_TYPE = 'USAGE' AND LST.USAGE_TYPE IN ('NPR', 'FRT', 'VRT', 'QTY'))
OR LST.LINE_TYPE <> 'USAGE'
THEN 'Y' ELSE 'N' END) USG_TYPE_V
,(CASE WHEN LST.LINE_TYPE <> 'USAGE' AND LST.USAGE_TYPE IS NOT NULL THEN 'Y' ELSE 'N' END) USG_TYPE_NULL_ERROR
,(CASE WHEN LST.USAGE_PERIOD IS NOT NULL
AND COUNT(DISTINCT USGP.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
THEN 'N' ELSE 'Y' END) USG_PER_V
,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE = 'NPR') AND LST.USAGE_PERIOD IS NOT NULL
THEN 'Y' ELSE 'N' END) USG_PER_NULL_ERROR
,(CASE WHEN LST.AVERAGING_INTERVAL IS NOT NULL AND LST.AVERAGING_INTERVAL < 0
THEN 'N' ELSE 'Y' END) AVG_INT_V
,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.AVERAGING_INTERVAL IS NOT NULL
THEN 'Y' ELSE 'N' END) AVG_INT_NULL_ERROR
,(CASE WHEN LST.SETTLEMENT_INTERVAL IS NOT NULL
AND COUNT(DISTINCT STMI.ROWID) OVER (PARTITION BY LST.ROWID) <> 1 THEN 'N' ELSE 'Y' END) STM_INT_V
,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.SETTLEMENT_INTERVAL IS NOT NULL
THEN 'Y' ELSE 'N' END) STM_INT_NULL_ERROR
,(CASE WHEN LST.USAGE_TERMINATION_METHOD IS NOT NULL
AND COUNT(DISTINCT UTM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1 THEN 'N' ELSE 'Y' END) UTM_V
,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.USAGE_TERMINATION_METHOD IS NOT NULL
THEN 'Y' ELSE 'N' END) UTM_NULL_ERROR
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
AND (LST.FIRST_BILLED_AMOUNT is not null and LST.FIRST_BILLED_AMOUNT < 0 )
-- OR (LST.FIRST_BILL_UPTO_DATE IS NOT NULL AND LST.FIRST_BILLED_AMOUNT IS NULL))
THEN 'N' ELSE 'Y' END) FIRST_BILLED_AMT_V
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
AND LST.FIRST_BILLED_AMOUNT IS NOT NULL AND LST.FIRST_BILL_UPTO_DATE IS NULL
THEN 'N' ELSE 'Y' END) FIRST_BILLED_DATE_V
,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND LST.LAST_BILLED_AMOUNT < 0
THEN 'N' ELSE 'Y' END) LAST_BILLED_AMT_V
,(CASE WHEN LST.LAST_BILL_FROM_DATE - 1 > LST.END_DATE THEN 'N'
WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND LST.LAST_BILLED_AMOUNT IS NOT NULL
AND LST.LAST_BILL_FROM_DATE > LST.END_DATE THEN 'N'
ELSE 'Y' END) LAST_BILL_DATE_V
FROM OKS_INT_LINE_STG_TEMP LST
,OKS_INT_HEADER_STG_TEMP HDR
,OKC_TIME_CODE_UNITS_B BIP
,MTL_UNITS_OF_MEASURE SUOM
,OKC_TIME_CODE_UNITS_B PUOM
,OKC_TIME_CODE_UNITS_B USGP
,FND_LOOKUPS STMI
,FND_LOOKUPS UTM
WHERE LST.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND LST.BILLING_INTERVAL_PERIOD = BIP.UOM_CODE (+)
AND LST.QUANTITY_UOM = SUOM.UOM_CODE (+)
AND LST.PRICE_UOM = PUOM.UOM_CODE (+)
AND LST.USAGE_PERIOD = USGP.UOM_CODE (+)
AND LST.SETTLEMENT_INTERVAL = STMI.LOOKUP_CODE (+)
AND STMI.LOOKUP_TYPE (+) = 'OKS_SETTLEMENT_INTERVAL'
AND LST.USAGE_TERMINATION_METHOD = UTM.LOOKUP_CODE (+)
AND UTM.LOOKUP_TYPE (+) = 'OKS_TRM_MTHD';
INSERT ALL
WHEN (COVERED_INSTANCE_ID IS NOT NULL ) THEN
INTO OKS_COVERED_INSTANCE_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_INSTANCE_ID
,COVERED_INSTANCE_NUMBER
,COVERED_SERIAL_NUMBER
,STATUS_CODE)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_INSTANCE_ID
,COVERED_INSTANCE_NUMBER
,COVERED_SERIAL_NUMBER
,STATUS_CODE)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.COVERED_SERIAL_NUMBER COVERED_SERIAL_NUMBER
,OCLI.COVERED_INSTANCE_NUMBER COVERED_INSTANCE_NUMBER
,(CASE WHEN NAMEID_Q.CITMI_COUNT = 1 THEN NAMEID_Q.CITMI_ID
WHEN NAMEID_Q.CITMN_COUNT = 1 THEN NAMEID_Q.CITMN_ID
WHEN NAMEID_Q.CITMSER_COUNT = 1 THEN NAMEID_Q.CITMSER_ID
ELSE NULL
END) COVERED_INSTANCE_ID
,OCLI.STATUS_CODE STATUS_CODE
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT CITMI.ROWID) OVER (PARTITION BY CLI.ROWID) CITMI_COUNT
,MAX(CLI.COVERED_INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMI_ID --INSTANCE ID Based on Id
,COUNT(DISTINCT CITMN.ROWID) OVER (PARTITION BY CLI.ROWID) CITMN_COUNT
,MAX(CITMN.INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMN_ID -- ITEM INSTANCE ID Based on Number
,COUNT(DISTINCT CITMSER.ROWID) OVER (PARTITION BY CLI.ROWID) CITMSER_COUNT
,MAX(CITMSER.INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMSER_ID -- ITEM INSTANCE ID Based on Serial number
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,CSI_ITEM_INSTANCES CITMI
,CSI_ITEM_INSTANCES CITMN
,CSI_ITEM_INSTANCES CITMSER
WHERE CLI.COVERED_INSTANCE_ID = CITMI.INSTANCE_ID (+)
AND CLI.COVERED_INSTANCE_NUMBER = CITMN.INSTANCE_NUMBER (+)
AND CLI.COVERED_SERIAL_NUMBER = CITMSER.SERIAL_NUMBER(+)) NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (COVERED_ITEM_ID IS NOT NULL) THEN
INTO OKS_COVERED_ITEM_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_ITEM_ID
,COVERED_ITEM_NAME
,COVERED_ITEM_ORG_ID)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_ITEM_ID
,COVERED_ITEM_NAME
,COVERED_ITEM_ORG_ID )
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.COVERED_ITEM_NAME COVERED_ITEM_NAME
,(CASE WHEN NAMEID_Q.CMSII_COUNT = 1 THEN NAMEID_Q.CMSII_ID
WHEN NAMEID_Q.CMSIN_COUNT = 1 THEN NAMEID_Q.CMSIN_ID
ELSE NULL
END) COVERED_ITEM_ID
,OCLI.COVERED_ITEM_ORG_ID COVERED_ITEM_ORG_ID
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT CMSII.ROWID) OVER (PARTITION BY CLI.ROWID ) CMSII_COUNT
,MAX(CLI.COVERED_ITEM_ID ) OVER (PARTITION BY CLI.ROWID ) CMSII_ID --COVERED ITEM ID Based on Id
,COUNT(DISTINCT CMSIN.ROWID) OVER (PARTITION BY CLI.ROWID) CMSIN_COUNT
,MAX(CMSIN.INVENTORY_ITEM_ID) OVER (PARTITION BY CLI.ROWID) CMSIN_ID --COVERED ITEM ID Based on Name
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,MTL_SYSTEM_ITEMS CMSII
,MTL_SYSTEM_ITEMS CMSIN
WHERE CLI.COVERED_ITEM_ID = CMSII.INVENTORY_ITEM_ID (+) ----Modified for bug 13518018
AND CLI.COVERED_ITEM_ORG_ID = CMSII.ORGANIZATION_ID(+)
AND CLI.COVERED_ITEM_NAME = CMSIN.SEGMENT1 (+)
AND CLI.COVERED_ITEM_ORG_ID = CMSIN.ORGANIZATION_ID (+)) NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (COVERED_PARTY_ID IS NOT NULL) THEN
INTO OKS_COVERED_PARTY_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_PARTY_ID
,COVERED_PARTY_NUMBER
,COVERED_PARTY_NAME)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_PARTY_ID
,COVERED_PARTY_NUMBER
,COVERED_PARTY_NAME)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.COVERED_PARTY_NAME COVERED_PARTY_NAME
,OCLI.COVERED_PARTY_NUMBER COVERED_PARTY_NUMBER
,(CASE WHEN NAMEID_Q.HPI_COUNT = 1 THEN NAMEID_Q.HPI_ID
WHEN NAMEID_Q.HPINUM_COUNT = 1 THEN NAMEID_Q.HPINUM_ID
WHEN NAMEID_Q.HPIN_COUNT = 1 THEN NAMEID_Q.HPIN_ID
ELSE NULL
END) COVERED_PARTY_ID
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT HPI.ROWID) OVER (PARTITION BY CLI.ROWID) HPI_COUNT
,MAX(CLI.COVERED_PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPI_ID --COVERED PARTY ID Based on Id
,COUNT(DISTINCT HPINUM.ROWID) OVER (PARTITION BY CLI.ROWID) HPINUM_COUNT
,MAX(HPINUM.PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPINUM_ID --COVERED PARTY ID Based on Number
,COUNT(DISTINCT HPIN.ROWID) OVER (PARTITION BY CLI.ROWID) HPIN_COUNT
,MAX(HPIN.PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPIN_ID --COVERED PARTY ID Based on Name
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,HZ_PARTIES HPI
,HZ_PARTIES HPINUM
,HZ_PARTIES HPIN
WHERE CLI.COVERED_PARTY_ID = HPI.PARTY_ID (+)
AND CLI.COVERED_PARTY_NAME = HPIN.PARTY_NAME (+)
AND CLI.COVERED_PARTY_NUMBER = HPINUM.PARTY_NUMBER (+))NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (COVERED_ACCOUNT_ID IS NOT NULL) THEN
INTO OKS_COVERED_ACCOUNT_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_ACCOUNT_ID
,COVERED_ACCOUNT_NUMBER)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_ACCOUNT_ID
,COVERED_ACCOUNT_NUMBER)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.COVERED_ACCOUNT_NUMBER COVERED_ACCOUNT_NUMBER
,(CASE WHEN NAMEID_Q.HCAI_COUNT = 1 THEN NAMEID_Q.HCAI_ID
WHEN NAMEID_Q.HCAN_COUNT = 1 THEN NAMEID_Q.HCAN_ID
ELSE NULL
END) COVERED_ACCOUNT_ID
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT HCAI.ROWID) OVER (PARTITION BY CLI.ROWID) HCAI_COUNT
,MAX(CLI.COVERED_ACCOUNT_ID) OVER (PARTITION BY CLI.ROWID) HCAI_ID --COVERED ACCOUNT ID Based on Id
,COUNT(DISTINCT HCAN.ROWID) OVER (PARTITION BY CLI.ROWID) HCAN_COUNT
,MAX(HCAN.CUST_ACCOUNT_ID) OVER (PARTITION BY CLI.ROWID) HCAN_ID -- COVERED ACCOUNT ID Based on Number
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,HZ_CUST_ACCOUNTS HCAI
,HZ_CUST_ACCOUNTS HCAN
WHERE CLI.COVERED_ACCOUNT_ID = HCAI.CUST_ACCOUNT_ID (+)
AND CLI.COVERED_ACCOUNT_NUMBER = HCAN.ACCOUNT_NUMBER (+)) NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (COVERED_SITE_ID IS NOT NULL) THEN
INTO OKS_COVERED_SITE_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_SITE_ID
,COVERED_SITE_NUMBER)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_SITE_ID
,COVERED_SITE_NUMBER)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.COVERED_SITE_NUMBER COVERED_SITE_NUMBER
,(CASE WHEN NAMEID_Q.CSITEID_COUNT = 1 THEN NAMEID_Q.CSITEID_ID
WHEN NAMEID_Q.CSITENUM_COUNT =1 THEN NAMEID_Q.CSITENUM_ID
ELSE NULL
END) COVERED_SITE_ID
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT CSITEID.ROWID) OVER (PARTITION BY CLI.ROWID) CSITEID_COUNT
,MAX(CLI.COVERED_SITE_ID) OVER (PARTITION BY CLI.ROWID) CSITEID_ID -- COVERED SITE ID Based on Id
,COUNT(DISTINCT CSITENUM.ROWID) OVER (PARTITION BY CLI.ROWID) CSITENUM_COUNT
,MAX(CSITENUM.PARTY_SITE_ID) OVER (PARTITION BY CLI.ROWID) CSITENUM_ID -- COVERED SITE ID Based on Number
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,HZ_PARTY_SITES CSITEID
,HZ_PARTY_SITES CSITENUM
WHERE CLI.COVERED_SITE_ID = CSITEID.PARTY_SITE_ID (+)
AND CLI.COVERED_SITE_NUMBER = CSITENUM.PARTY_SITE_NUMBER (+)) NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (COVERED_SYSTEM_ID IS NOT NULL) THEN
INTO OKS_COVERED_SYSTEM_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_SYSTEM_ID)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_SYSTEM_ID)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,(CASE WHEN NAMEID_Q.CSYSID_COUNT =1 THEN NAMEID_Q.CSYSID_ID
ELSE NULL
END) COVERED_SYSTEM_ID
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_INT_LINE_STG_TEMP OLSTG
,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
,COUNT(DISTINCT CSYSID.ROWID) OVER (PARTITION BY CLI.ROWID) CSYSID_COUNT
,MAX(CLI.COVERED_SYSTEM_ID) OVER (PARTITION BY CLI.ROWID) CSYSID_ID --COVERED SYSTEM ID Based on Id
FROM OKS_COVERED_LEVELS_INTERFACE CLI
,CSI_SYSTEMS_B CSYSID
WHERE CLI.COVERED_SYSTEM_ID = CSYSID.SYSTEM_ID (+)) NAMEID_Q
WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
INSERT ALL
WHEN (1=1) THEN
INTO OKS_INT_COVERED_LEVEL_STG_TEMP
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_INSTANCE_ID
,COVERED_ITEM_ID
,COVERED_ACCOUNT_ID
,COVERED_SITE_ID
,COVERED_PARTY_ID
,COVERED_SYSTEM_ID)
VALUES (COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,COVERED_INSTANCE_ID
,COVERED_ITEM_ID
,COVERED_ACCOUNT_ID
,COVERED_SITE_ID
,COVERED_PARTY_ID
,COVERED_SYSTEM_ID)
WHEN (COV_LINE_INTERFACE_ID IS NOT NULL AND LINE_INTERFACE_ID IS NULL ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_LINTID'
,NULL )
WHEN (LINE_NUMBER IS NULL OR DUP_LINE_NUMBER_COUNT >0 OR LINE_NUMBER <= 0 OR floor(LINE_NUMBER) <> LINE_NUMBER) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_LINNUM'
,NULL)
WHEN ( STATUS_CODE IS NULL OR STATUS_CLVL_VALID ='N' OR STATUS_LINLVL_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_STSCODE'
,NULL)
WHEN (DATE_CLVL_VALID ='N' OR DATE_LINLVL_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_DTRANGE'
,NULL)
WHEN (DATE_CLVL_STS_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_STS_DT'
,NULL)
WHEN (ST_DT_FST_BILL_YN ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_SDT_FBDT'
,NULL)
WHEN (RNWL_TYPE_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_RNMLCD'
,NULL)
WHEN (DATE_CANC_VALID ='N' OR DATE_CANC_NULL ='Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_CNCLDT'
,NULL)
WHEN (CR_VALID ='N' OR CR_NULL ='Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_CNCLRSN'
,NULL)
WHEN (INVOICE_TEXT IS NULL ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_INVTXT'
,NULL)
WHEN (PRINT_INVOICE IS NULL OR (PRINT_INVOICE IS NOT NULL AND PRINT_INVOICE <> 'Y' AND PRINT_INVOICE <>'N')) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_PRNTINV'
,NULL)
WHEN (QUANTITY_COVERED IS NULL OR QUANTITY_COVERED <= 0 OR FLOOR(QUANTITY_COVERED) <> QUANTITY_COVERED ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_QUNTCOV'
,NULL)
WHEN( QUOM_VALID = 'N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_QUOM'
,NULL)
/*WHEN ((COVERED_INSTANCE_ID IS NOT NULL OR COVERED_ITEM_ID IS NOT NULL)
AND (PRICE_UOM IS NULL OR PRUOM_VALID ='N')) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_PRUOM'
,NULL)*/ /*commented for bug:7804753*/
WHEN ((COVERED_INSTANCE_ID IS NULL AND COVERED_ITEM_ID IS NULL) AND PRICE_UOM IS NOT NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_PRUOMNUL'
,NULL)
WHEN (SUBTOTAL IS NULL OR SUBTOTAL <0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_SUBTOT'
,NULL)
WHEN( TAX_AMOUNT IS NULL OR TAX_AMOUNT < 0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_TAXAMT'
,NULL)
WHEN ( TOT_COV_LVL_ATTR <> 1 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_CLVLID'
,NULL)
/* WHEN (FIRST_BILL_AMT_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_FBILAMT'
,NULL)
WHEN (LAST_BILL_AMT_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_LBILAMT'
,NULL) */
WHEN ( Nvl(COVERED_ITEM_ID,1) = -99 ) THEN /*skuchima bug16468231 */
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_COVERED'
,NULL)
WHEN ( Nvl(COVERED_PARTY_ID,1) = -99 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_COVERED'
,NULL)
WHEN ( Nvl(COVERED_ACCOUNT_ID,1) = -99 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_COVERED'
,NULL)
WHEN ( Nvl(COVERED_SITE_ID,1) = -99 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_COVERED'
,NULL)
WHEN ( Nvl(COVERED_SYSTEM_ID,1) = -99 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_COVERED_LEVELS_INTERFACE'
,COVERED_LEVEL_INTERFACE_ID
,'OKS_IMP_CLVL_INVALID_COVERED'
,NULL)
SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OCLI.LINE_INTERFACE_ID COV_LINE_INTERFACE_ID
,(CASE WHEN COUNT(DISTINCT OLSTG.ROWID) OVER (PARTITION BY OCLI.ROWID) = 1
THEN OCLI.LINE_INTERFACE_ID
ELSE NULL END) LINE_INTERFACE_ID
,OLSTG.LSE_ID LINE_LSE_ID
,OKSINST_STG.COVERED_INSTANCE_ID
/*SKUCHIMA Bug#16468231 */
,( CASE WHEN OLSTG.lse_id IN (19,14) AND OKSITM_STG.COVERED_ITEM_ID IS NOT NULL THEN -99
ELSE OKSITM_STG.COVERED_ITEM_ID END ) COVERED_ITEM_ID
,( CASE WHEN OLSTG.lse_id IN (19,14) AND OKSPRTY_STG.COVERED_PARTY_ID IS NOT NULL THEN -99
ELSE OKSPRTY_STG.COVERED_PARTY_ID END ) COVERED_PARTY_ID
, ( CASE WHEN OLSTG.lse_id IN (19,14) AND OKSACC_STG.COVERED_ACCOUNT_ID IS NOT NULL THEN -99
ELSE OKSACC_STG.COVERED_ACCOUNT_ID END ) COVERED_ACCOUNT_ID
,( CASE WHEN OLSTG.lse_id IN (19,14) AND OKSSITE_STG.COVERED_SITE_ID IS NOT NULL THEN -99
ELSE OKSSITE_STG.COVERED_SITE_ID END ) COVERED_SITE_ID
,( CASE WHEN OLSTG.lse_id IN (19,14) AND OKSSYS_STG.COVERED_SYSTEM_ID IS NOT NULL THEN -99
ELSE OKSSYS_STG.COVERED_SYSTEM_ID END ) COVERED_SYSTEM_ID
,DECODE(NVL(OKSINST_STG.COVERED_INSTANCE_ID ,0),0,0,1) + DECODE(NVL(OKSITM_STG.COVERED_ITEM_ID,0),0,0,1)
+ DECODE(NVL(OKSPRTY_STG.COVERED_PARTY_ID,0),0,0,1) +DECODE(NVL(OKSACC_STG.COVERED_ACCOUNT_ID,0),0,0,1)
+DECODE(NVL(OKSSITE_STG.COVERED_SITE_ID,0),0,0,1) +DECODE(NVL(OKSSYS_STG.COVERED_SYSTEM_ID,0),0,0,1) TOT_COV_LVL_ATTR
,OCLI.LINE_NUMBER LINE_NUMBER
,COUNT(DISTINCT OCLIN.ROWID) OVER (PARTITION BY OCLI.ROWID) DUP_LINE_NUMBER_COUNT
,RTRIM(OCLI.LINE_REFERENCE) LINE_REFERENCE
,OCLI.STATUS_CODE STATUS_CODE
,(CASE WHEN STS.STE_CODE IN ('ENTERED','ACTIVE','CANCELLED','SIGNED','EXPIRED')
THEN 'Y'
ELSE 'N' END) STATUS_CLVL_VALID
,(CASE WHEN OLSTG.STATUS_CODE IN ('ENTERED') AND OCLI.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('ACTIVE') AND OCLI.STATUS_CODE IN ('SIGNED','ACTIVE','EXPIRED','CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('CANCELLED') AND OCLI.STATUS_CODE IN ('CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('SIGNED') AND OCLI.STATUS_CODE IN ('SIGNED' , 'CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('EXPIRED') AND OCLI.STATUS_CODE IN ('EXPIRED', 'CANCELLED') THEN 'Y'
ELSE 'N'
END) STATUS_LINLVL_VALID
,TRUNC(OCLI.START_DATE) START_DATE
,TRUNC(OLSTG.START_DATE) LINE_START_DATE
,TRUNC(OCLI.END_DATE) END_DATE
,TRUNC(OLSTG.END_DATE) LINE_END_DATE
,(CASE WHEN OCLI.START_DATE >=OLSTG.START_DATE AND OCLI.END_DATE <=OLSTG.END_DATE
THEN 'Y'
ELSE 'N' END ) DATE_LINLVL_VALID
,(CASE WHEN OCLI.START_DATE IS NULL OR OCLI.END_DATE IS NULL OR OCLI.START_DATE >OCLI.END_DATE
THEN 'N'
ELSE 'Y' END) DATE_CLVL_VALID
,(CASE WHEN ( TRUNC( OCLI.START_DATE) > SYSDATE) AND STS.STE_CODE IN ('ACTIVE','EXPIRED')
OR (TRUNC(OCLI.END_DATE) <=SYSDATE) AND STS.STE_CODE ='SIGNED'
OR (SYSDATE BETWEEN TRUNC(OCLI.START_DATE) AND TRUNC(OCLI.END_DATE))
AND (STS.STE_CODE IN ('SIGNED','EXPIRED'))
THEN 'N'
ELSE 'Y' END) DATE_CLVL_STS_VALID
,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND OLSTG.FIRST_BILL_UPTO_DATE < OCLI.START_DATE THEN 'N'
ELSE 'Y'
END) ST_DT_FST_BILL_YN
,OCLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,(CASE WHEN OCLI.RENEWAL_TYPE_CODE IS NOT NULL
AND (COUNT(DISTINCT RNWLC.ROWID) OVER (PARTITION BY OCLI.ROWID) <> 1)
THEN 'N' ELSE 'Y'
END) RNWL_TYPE_VALID
,OCLI.CANCELLATION_DATE CANCELLATION_DATE
,(CASE WHEN (OCLI.CANCELLATION_DATE IS NULL AND STS.STE_CODE ='CANCELLED')
THEN 'N'
ELSE 'Y' END) DATE_CANC_VALID
,(CASE WHEN (OCLI.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
THEN 'Y'
ELSE 'N' END) DATE_CANC_NULL
,OCLI.CANCELLATION_REASON CANCELLATION_REASON
,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY OCLI.ROWID) <>1
AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY OCLI.ROWID) <>1))
THEN 'N'
ELSE 'Y' END) CR_VALID
,(CASE WHEN(STS.STE_CODE <>'CANCELLED' AND OCLI.CANCELLATION_REASON IS NOT NULL )
THEN 'Y' ELSE 'N' END) CR_NULL
,RTRIM(OCLI.INVOICE_TEXT) INVOICE_TEXT
,OCLI.PRINT_INVOICE PRINT_INVOICE
,OCLI.QUANTITY_COVERED QUANTITY_COVERED
,OCLI.QUANTITY_UOM QUANTITY_UOM
,(CASE WHEN ((OCLI.QUANTITY_UOM IS NOT NULL) AND
(COUNT(DISTINCT QUOM.ROWID) OVER (PARTITION BY OCLI.ROWID)) <> 1)
THEN 'N'
ELSE 'Y' END) QUOM_VALID
,OCLI.PRICE_UOM PRICE_UOM
,(CASE WHEN ((OCLI.PRICE_UOM IS NOT NULL) AND
(COUNT(DISTINCT PRUOM.ROWID ) OVER (PARTITION BY OCLI.ROWID)) <>1)
THEN 'N'
ELSE 'Y' END) PRUOM_VALID
,OCLI.SUBTOTAL SUBTOTAL
,OCLI.TAX_AMOUNT TAX_AMOUNT
,OCLI.FIRST_BILL_AMOUNT
,OCLI.LAST_BILL_AMOUNT
/*
,(CASE WHEN OCLI.FIRST_BILL_AMOUNT IS NOT NULL AND OLSTG.FIRST_BILL_UPTO_DATE IS NULL THEN 'N'
ELSE 'Y'
END) FIRST_BILL_AMT_VALID
,(CASE WHEN OCLI.LAST_BILL_AMOUNT IS NULL AND OLSTG.LAST_BILL_FROM_DATE -1 > OCLI.END_DATE THEN 'N'
WHEN OCLI.LAST_BILL_AMOUNT IS NOT NULL AND OLSTG.LAST_BILL_FROM_DATE > OCLI.END_DATE THEN 'N'
ELSE 'Y'
END) LAST_BILL_AMT_VALID
*/
FROM OKS_COVERED_LEVELS_INTERFACE OCLI
,OKS_COVERED_LEVELS_INTERFACE OCLIN
,OKS_INT_LINE_STG_TEMP OLSTG
,OKC_STATUSES_B STS
,FND_LOOKUPS CR1
,FND_LOOKUPS CR2
,OKC_TIME_CODE_UNITS_V PRUOM
,OKX_UNITS_OF_MEASURE_V QUOM
,FND_LOOKUPS RNWLC
,OKS_COVERED_INSTANCE_STG_TEMP OKSINST_STG
,OKS_COVERED_ITEM_STG_TEMP OKSITM_STG
,OKS_COVERED_PARTY_STG_TEMP OKSPRTY_STG
,OKS_COVERED_ACCOUNT_STG_TEMP OKSACC_STG
,OKS_COVERED_SITE_STG_TEMP OKSSITE_STG
,OKS_COVERED_SYSTEM_STG_TEMP OKSSYS_STG
WHERE OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSINST_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSITM_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSPRTY_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSACC_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSSITE_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSSYS_STG.COVERED_LEVEL_INTERFACE_ID (+)
AND OCLI.STATUS_CODE = STS.CODE (+)
AND OCLI.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
AND OCLI.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
AND OCLI.PRICE_UOM = PRUOM.UOM_CODE(+)
AND PRUOM.ACTIVE_FLAG(+) ='Y'
AND OCLI.QUANTITY_UOM = QUOM.UOM_CODE (+)
AND TRUNC( NVL( QUOM.DISABLE_DATE (+), SYSDATE)) >= TRUNC(SYSDATE)
AND OCLI.RENEWAL_TYPE_CODE = RNWLC.LOOKUP_CODE (+)
AND RNWLC.LOOKUP_TYPE (+) = 'OKC_LINE_RENEWAL_TYPE'
AND OCLIN.LINE_NUMBER (+) =OCLI.LINE_NUMBER
AND OCLIN.LINE_INTERFACE_ID(+) = OCLI.LINE_INTERFACE_ID
AND OCLIN.COVERED_LEVEL_INTERFACE_ID(+) <> OCLI.COVERED_LEVEL_INTERFACE_ID ;
SELECT count(1) INTO l_int_count FROM OKS_COVERED_LEVELS_INTERFACE OCLI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
WHERE LINE_INTERFACE_ID = OCLI.LINE_INTERFACE_ID);
SELECT count(1) INTO l_stg_count FROM OKS_INT_COVERED_LEVEL_STG_TEMP;
'Number of records inserted into staging table = '|| l_stg_count);
INSERT ALL
WHEN (1=1) THEN
INTO OKS_INT_USAGE_COUNTER_STG_TEMP
(USAGE_COUNTER_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,LINE_INTERFACE_ROWID
,USAGE_COUNTER_INTERFACE_ROWID
,COUNTER_ID
,LINE_NUMBER
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,PRINT_INVOICE
,SUBTOTAL
,TAX_AMOUNT
,FIXED_USG_CTR
,MINIMUM_USG_CTR
,DEFAULT_USG_CTR
,FILL_YN
,ESTIMATION_METHOD
,ESTIMATION_START_DATE
,LEVEL_YN )
VALUES (USAGE_COUNTER_INTERFACE_ID
,LINE_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,LINE_INTERFACE_ROWID
,USAGE_COUNTER_INTERFACE_ROWID
,COUNTER_ID
,LINE_NUMBER
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,PRINT_INVOICE
,SUBTOTAL
,TAX_AMOUNT
,FIXED_USG_CNT
,MINIMUM_USG_CNT
,DEFAULT_USG_CNT
,FILL_YN
,ESTIMATION_METHOD
,ESTIMATION_START_DATE
,LEVEL_YN )
WHEN (LINE_INTERFACE_ID IS NOT NULL AND LINSTG_LINE_INTERFACE_ID IS NULL ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
, USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_LINTID'
,NULL )
WHEN (LINE_NUMBER IS NULL OR DUP_LINE_NUMBER_COUNT >0 OR LINE_NUMBER <= 0 OR floor(LINE_NUMBER) <> LINE_NUMBER) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_LINNUM'
,NULL)
WHEN (CNTR_VALID_YN = 'N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_CNTRID'
,NULL)
WHEN (START_DATE IS NULL OR START_DATE_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_STRDT'
,NULL)
WHEN (END_DATE IS NULL OR END_DATE_VALID='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_ENDDT'
,NULL)
WHEN ((STATUS_CODE IS NULL) OR STATUS_CNTLVL_VALID='N' OR STATUS_USGLINLVL_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_STSCODE'
,NULL)
WHEN (DATE_USGCNT_STS_VALID ='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_USGCNT_INVALID_STS_DT'
,NULL)
WHEN (RENEWAL_TYPE_CODE_VALID = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_RNWLCD'
,NULL)
WHEN (DATE_CANC_VALID ='N' OR DATE_CANC_NULL ='Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_CNCLDT'
,NULL)
WHEN (CR_VALID ='N' OR CR_NULL ='Y') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_CNCLRSN'
,NULL)
WHEN (PRINT_INVOICE IS NULL OR (PRINT_INVOICE IS NOT NULL AND PRINT_INVOICE <> 'Y' AND PRINT_INVOICE <> 'N')) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_PRINV'
,NULL)
WHEN ( SUBTOTAL_VALID = 'N' OR SUBTOTAL <0) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_STOT'
,NULL)
WHEN (TAX_VALID ='N' OR TAX_AMOUNT < 0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_TAXINV'
,NULL)
WHEN (FIXED_USG_CNT_VALID = 'N' OR FIXED_USG_CNT_VALID IS NULL OR FIXED_USG_CNT <0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_FIXEDUSG'
,NULL)
WHEN ( MINIMUM_USG_CNT_VALID ='N' OR MINIMUM_USG_CNT_VALID IS NULL OR MINIMUM_USG_CNT <0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_MINUSG'
,NULL)
WHEN ( DEFAULT_USG_CNT_VALID='N' OR DEFAULT_USG_CNT_VALID IS NULL OR DEFAULT_USG_CNT < 0 ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_DEFUSG'
,NULL)
WHEN (FILL_YN_VALID = 'N' OR ( Nvl(FILL_YN,'Y') <> 'Y' AND Nvl(FILL_YN,'N') <> 'N')) THEN --skuchima bug 12664469
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_FILLYN'
,NULL)
WHEN (ESTIMATION_METHOD_VALID='N' OR ESTMTHD_LKUP_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_ESTMTHD'
,NULL)
WHEN (EST_STRDT_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_ESTSRTDT'
,NULL)
WHEN (LEVEL_YN_VALID ='N' OR (LEVEL_YN is not NULL and LEVEL_YN <>'Y' AND LEVEL_YN <>'N')) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_USAGE_COUNTERS_INTERFACE'
,USAGE_COUNTER_INTERFACE_ID
,'OKS_IMP_UCTR_INVALID_LVLYN'
,NULL)
SELECT
OUSGCNTR.USAGE_COUNTER_INTERFACE_ID USAGE_COUNTER_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,OLSTG.LINE_INTERFACE_ROWID LINE_INTERFACE_ROWID
,OUSGCNTR.ROWID USAGE_COUNTER_INTERFACE_ROWID
,OUSGCNTR.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OLSTG.LINE_INTERFACE_ID LINSTG_LINE_INTERFACE_ID
,OUSGCNTR.COUNTER_ID COUNTER_ID -- validation to be done
,OUSGCNTR.LINE_NUMBER LINE_NUMBER
,COUNT(DISTINCT OUSGLIN.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) DUP_LINE_NUMBER_COUNT
,OLSTG.USAGE_TYPE USAGE_TYPE
,OUSGCNTR.FIXED_USG_CNT FIXED_USG_CNT
,(CASE WHEN OLSTG.USAGE_TYPE ='FRT' AND OUSGCNTR.FIXED_USG_CNT IS NOT NULL THEN 'Y'
WHEN OLSTG.USAGE_TYPE ='FRT' AND OUSGCNTR.FIXED_USG_CNT IS NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE <>'FRT' AND OUSGCNTR.FIXED_USG_CNT IS NOT NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE <>'FRT' AND OUSGCNTR.FIXED_USG_CNT IS NULL THEN 'Y'
END) FIXED_USG_CNT_VALID
,OUSGCNTR.MINIMUM_USG_CNT MINIMUM_USG_CNT
,(CASE WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND (OUSGCNTR.MINIMUM_USG_CNT IS NULL)
THEN 'Y'
WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND (OUSGCNTR.MINIMUM_USG_CNT IS NOT NULL)
THEN 'N'
WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND (OUSGCNTR.MINIMUM_USG_CNT IS NOT NULL
AND OUSGCNTR.MINIMUM_USG_CNT <= OUSGCNTR.DEFAULT_USG_CNT )
THEN 'Y'
WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND (OUSGCNTR.MINIMUM_USG_CNT IS NULL)
THEN 'Y'
ELSE 'N'
END) MINIMUM_USG_CNT_VALID
,OUSGCNTR.DEFAULT_USG_CNT DEFAULT_USG_CNT
,(CASE WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND OUSGCNTR.DEFAULT_USG_CNT IS NULL THEN 'Y'
WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR' ) AND OUSGCNTR.DEFAULT_USG_CNT IS NOT NULL THEN 'N'
WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND OUSGCNTR.DEFAULT_USG_CNT IS NULL THEN 'N'
WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND OUSGCNTR.DEFAULT_USG_CNT IS NOT NULL THEN 'Y'
END) DEFAULT_USG_CNT_VALID
,OUSGCNTR.FILL_YN FILL_YN
,(CASE WHEN OLSTG.USAGE_TYPE<>'VRT' AND OUSGCNTR.FILL_YN IS NOT NULL THEN 'N'
ELSE 'Y'
END) FILL_YN_VALID
,OUSGCNTR.ESTIMATION_METHOD ESTIMATION_METHOD
,(CASE WHEN OLSTG.USAGE_TYPE='VRT' AND OUSGCNTR.FILL_YN ='Y' AND OUSGCNTR.ESTIMATION_METHOD IS NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE<>'VRT' AND OUSGCNTR.ESTIMATION_METHOD IS NOT NULL THEN 'N'
ELSE 'Y'
END) ESTIMATION_METHOD_VALID
,(CASE WHEN ((OUSGCNTR.ESTIMATION_METHOD IS NOT NULL) AND
(COUNT(DISTINCT ESTMTHD.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID)) <> 1)
THEN 'N'
ELSE 'Y'
END) ESTMTHD_LKUP_VALID
,OUSGCNTR.ESTIMATION_START_DATE ESTIMATION_START_DATE
,(CASE WHEN OLSTG.USAGE_TYPE <> 'VRT' AND OUSGCNTR.ESTIMATION_START_DATE IS NOT NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE ='VRT' AND OUSGCNTR.ESTIMATION_METHOD ='CSR' AND
OUSGCNTR.ESTIMATION_START_DATE IS NULL
THEN 'N'
WHEN (OLSTG.USAGE_TYPE='VRT' AND OUSGCNTR.ESTIMATION_METHOD = 'AMCV') AND
OUSGCNTR.ESTIMATION_START_DATE IS NOT NULL THEN 'N'
ELSE 'Y'
END) EST_STRDT_VALID
,OUSGCNTR.LEVEL_YN LEVEL_YN
/*,(CASE WHEN OLSTG.USAGE_TYPE <>'VRT' AND (OUSGCNTR.LEVEL_YN IS NOT NULL AND OUSGCNTR.LEVEL_YN <>'N') THEN 'N'
WHEN OLSTG.USAGE_TYPE= 'VRT' AND OUSGCNTR.LEVEL_YN IS NULL THEN 'N'
ELSE 'Y'
END) LEVEL_YN_VALID -- NEED TO CHECK IN INSERT FOR VALID VALUES Y OR N*/
, (CASE WHEN (OLSTG.USAGE_TYPE ='FRT' OR OLSTG.USAGE_TYPE='NPR') AND OUSGCNTR.LEVEL_YN IS NOT NULL THEN 'N'
ELSE 'Y'
END) LEVEL_YN_VALID
,OUSGCNTR.STATUS_CODE STATUS_CODE
,(CASE WHEN STS.STE_CODE IN ('ENTERED','ACTIVE','CANCELLED','SIGNED','EXPIRED')
THEN 'Y'
ELSE 'N' END) STATUS_CNTLVL_VALID
,(CASE WHEN OLSTG.STATUS_CODE IN ('ENTERED') AND OUSGCNTR.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('ACTIVE') AND OUSGCNTR.STATUS_CODE IN ('SIGNED','ACTIVE','EXPIRED','CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('CANCELLED') AND OUSGCNTR.STATUS_CODE IN ('CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('SIGNED') AND OUSGCNTR.STATUS_CODE IN ('SIGNED' , 'CANCELLED') THEN 'Y'
WHEN OLSTG.STATUS_CODE IN ('EXPIRED') AND OUSGCNTR.STATUS_CODE IN ('EXPIRED', 'CANCELLED') THEN 'Y'
ELSE 'N'
END) STATUS_USGLINLVL_VALID
,(CASE WHEN ( TRUNC( OUSGCNTR.START_DATE) > SYSDATE) AND OUSGCNTR.STATUS_CODE IN ('ACTIVE','EXPIRED')
OR (TRUNC(OUSGCNTR.END_DATE) <=SYSDATE) AND OUSGCNTR.STATUS_CODE ='SIGNED'
OR (SYSDATE BETWEEN TRUNC(OUSGCNTR.START_DATE) AND TRUNC(OUSGCNTR.END_DATE))
AND (OUSGCNTR.STATUS_CODE IN ('SIGNED','EXPIRED'))
THEN 'N'
ELSE 'Y' END) DATE_USGCNT_STS_VALID
,OUSGCNTR.START_DATE START_DATE
,OLSTG.START_DATE ULIN_START_DATE
,OUSGCNTR.END_DATE END_DATE
,OLSTG.END_DATE ULIN_END_DATE
,(CASE WHEN OUSGCNTR.START_DATE = OLSTG.START_DATE THEN 'Y'
ELSE 'N'
END) START_DATE_VALID
,(CASE WHEN OUSGCNTR.END_DATE = OLSTG.END_DATE THEN 'Y'
ELSE 'N'
END) END_DATE_VALID
,OUSGCNTR.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,OLSTG.RENEWAL_TYPE_CODE USAGELINE_RENEWAL_CODE
,(CASE WHEN OUSGCNTR.RENEWAL_TYPE_CODE IS NULL THEN 'Y'
WHEN OUSGCNTR.RENEWAL_TYPE_CODE IS NOT NULL AND
OUSGCNTR.RENEWAL_TYPE_CODE=OLSTG.RENEWAL_TYPE_CODE THEN 'Y'
ELSE 'N'
END) RENEWAL_TYPE_CODE_VALID
,OUSGCNTR.CANCELLATION_DATE CANCELLATION_DATE
,(CASE WHEN (OUSGCNTR.CANCELLATION_DATE IS NULL AND STS.STE_CODE ='CANCELLED')
THEN 'N'
ELSE 'Y' END) DATE_CANC_VALID
,(CASE WHEN (OUSGCNTR.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
THEN 'Y'
ELSE 'N' END) DATE_CANC_NULL
,OUSGCNTR.CANCELLATION_REASON CANCELLATION_REASON
,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) <>1
AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) <>1))
THEN 'N'
ELSE 'Y' END) CR_VALID
,(CASE WHEN(STS.STE_CODE <>'CANCELLED' AND OUSGCNTR.CANCELLATION_REASON IS NOT NULL )
THEN 'Y' ELSE 'N' END) CR_NULL
,OUSGCNTR.PRINT_INVOICE PRINT_INVOICE
,RTRIM(OUSGCNTR.INVOICE_TEXT) INVOICE_TEXT
,OUSGCNTR.SUBTOTAL SUBTOTAL
,(CASE WHEN OLSTG.USAGE_TYPE <> 'NPR' AND OUSGCNTR.SUBTOTAL IS NOT NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE ='NPR' AND OUSGCNTR.SUBTOTAL IS NULL THEN 'N'
ELSE 'Y'
END) SUBTOTAL_VALID
,OUSGCNTR.TAX_AMOUNT
,(CASE WHEN OLSTG.USAGE_TYPE <> 'NPR' AND OUSGCNTR.TAX_AMOUNT IS NOT NULL THEN 'N'
WHEN OLSTG.USAGE_TYPE ='NPR' AND OUSGCNTR.TAX_AMOUNT IS NULL THEN 'N'
ELSE 'Y' END) TAX_VALID
,RTRIM(OUSGCNTR.LINE_REFERENCE) LINE_REFERENCE
,(CASE WHEN (COUNT(DISTINCT CSI_CNTR.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID)) <> 1 THEN 'N'
WHEN CSI_CNTR.USAGE_ITEM_ID <> OLSTG.ITEM_ID THEN 'N'
ELSE 'Y'
END ) CNTR_VALID_YN
FROM OKS_USAGE_COUNTERS_INTERFACE OUSGCNTR
,OKS_USAGE_COUNTERS_INTERFACE OUSGLIN
,OKS_INT_LINE_STG_TEMP OLSTG
,FND_LOOKUPS ESTMTHD
,OKC_STATUSES_B STS
,FND_LOOKUPS CR1
,FND_LOOKUPS CR2
,CSI_COUNTERS_B CSI_CNTR
WHERE OUSGCNTR.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
AND OUSGCNTR.ESTIMATION_METHOD = ESTMTHD.LOOKUP_CODE(+)
AND ESTMTHD.LOOKUP_TYPE(+) = 'OKS_CTR_EST_MTHD'
AND OUSGCNTR.STATUS_CODE = STS.CODE (+)
AND OUSGCNTR.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
AND OUSGCNTR.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
AND OUSGLIN.LINE_NUMBER (+) =OUSGCNTR.LINE_NUMBER
AND OUSGLIN.LINE_INTERFACE_ID(+) = OUSGCNTR.LINE_INTERFACE_ID
AND OUSGCNTR.COUNTER_ID = CSI_CNTR.COUNTER_ID (+)
AND OUSGLIN.USAGE_COUNTER_INTERFACE_ID(+) <> OUSGCNTR.USAGE_COUNTER_INTERFACE_ID;
SELECT count(1) INTO l_int_count FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUCI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
WHERE LINE_INTERFACE_ID = OUCI.LINE_INTERFACE_ID);
SELECT count(1) INTO l_stg_count FROM OKS_INT_USAGE_COUNTER_STG_TEMP;
'Number of records inserted into staging table = '|| l_stg_count);
INSERT ALL
WHEN (1=1) THEN
INTO OKS_INT_SALES_CREDIT_STG_TEMP
(SALES_CREDIT_INTERFACE_ID
,HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,SALESPERSON_ID
,SALESGROUP_ID
,SALES_CREDIT_TYPE_ID
,PERCENT)
VALUES
(SALES_CREDIT_INTERFACE_ID
,HEADER_INTERFACE_ID
,HEADER_INTERFACE_ROWID
,SALESPERSON_ID
,SALESGROUP_ID
,SALES_CREDIT_TYPE_ID
,PERCENT)
WHEN (SALESPERSON_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_SALEPERS'
,NULL)
WHEN (SALESGROUP_V = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_SALEGRP'
,NULL)
WHEN (SALES_CREDIT_TYPE_ID IS NULL) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_SCT'
,NULL)
WHEN (QUOTA_FLAG = 'Y' AND PERCENT_SUM <> 100) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_PERSUM'
,NULL)
/*Changes made for bug10295589*/
WHEN (QUOTA_FLAG = 'Y' AND (PERCENT IS NULL OR PERCENT < 0 OR PERCENT > 100)) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_PER'
,NULL)
/*bug 10295589 added new when condition to handle non-quota sales credit percentage
added a new message with name 'OKS_IMP_SC_INVALID_NQ_PER' and the message text to be
'Enter a percent value greater than 0 for 'Non Quota' type sales persons.'*/
WHEN (QUOTA_FLAG = 'N' AND (PERCENT IS NULL OR PERCENT < 0 )) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_INVALID_NQ_PER'
,NULL)
WHEN (UNIQ = 'N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_SALES_CREDITS_INTERFACE'
,SALES_CREDIT_INTERFACE_ID
,'OKS_IMP_SC_SP_SCT_NUNIQ'
,NULL)
SELECT OSC.SALES_CREDIT_INTERFACE_ID SALES_CREDIT_INTERFACE_ID
,OSC.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,NAMEID_Q.SALESPERSON_ID SALESPERSON_ID
,(CASE WHEN OSC.SALESGROUP_ID IS NULL OR
(OSC.SALESGROUP_ID <> -1
AND NOT EXISTS (SELECT 'X'
FROM JTF_RS_GROUP_MEMBERS MEM
,JTF_RS_SALESREPS SRP
,JTF_RS_GROUP_USAGES USG
WHERE SRP.RESOURCE_ID = MEM.RESOURCE_ID
AND MEM.GROUP_ID = USG.GROUP_ID
AND USG.USAGE = 'SALES'
AND SRP.SALESREP_ID = NAMEID_Q.SALESPERSON_ID
AND SRP.ORG_ID = HDR.OPERATING_UNIT_ID
AND MEM.GROUP_ID = OSC.SALESGROUP_ID))
THEN 'N' ELSE 'Y' END) SALESGROUP_V
,OSC.SALESGROUP_ID SALESGROUP_ID
,(CASE WHEN COUNT(DISTINCT SCT.ROWID) OVER (PARTITION BY OSC.ROWID) = 1
THEN MAX(SCT.SALES_CREDIT_TYPE_ID) OVER (PARTITION BY OSC.ROWID) ELSE NULL END) SALES_CREDIT_TYPE_ID
,(CASE WHEN COUNT(DISTINCT SCT.ROWID) OVER (PARTITION BY OSC.ROWID) = 1
THEN MAX(SCT.QUOTA_FLAG) OVER (PARTITION BY OSC.ROWID) ELSE NULL END) QUOTA_FLAG
, SUM(OSC.PERCENT) OVER (PARTITION BY OSC.HEADER_INTERFACE_ID, SCT.QUOTA_FLAG) PERCENT_SUM
, OSC.PERCENT PERCENT
,(CASE WHEN EXISTS (SELECT 'X' FROM OKS_SALES_CREDITS_INTERFACE
WHERE SALES_CREDIT_INTERFACE_ID <> OSC.SALES_CREDIT_INTERFACE_ID
AND HEADER_INTERFACE_ID = OSC.HEADER_INTERFACE_ID
AND (SALESPERSON_ID = OSC.SALESPERSON_ID OR SALESPERSON_NAME = OSC.SALESPERSON_NAME)
AND SALES_CREDIT_TYPE = OSC.SALES_CREDIT_TYPE)
THEN 'N' ELSE 'Y' END) UNIQ
FROM OKS_SALES_CREDITS_INTERFACE OSC
,OKS_INT_HEADER_STG_TEMP HDR
,OE_SALES_CREDIT_TYPES SCT
,(SELECT distinct(OSCI.SALES_CREDIT_INTERFACE_ID) -- to avoid duplicate joins with tables when some names match with multiple records
,(CASE WHEN COUNT(DISTINCT SPI.ROWID) OVER (PARTITION BY OSCI.ROW_ID) = 1
THEN MAX(OSCI.SALESPERSON_ID) OVER (PARTITION BY OSCI.ROW_ID) -- Sales Person Id Based on Id
WHEN COUNT(DISTINCT SPN.ROW_ID) OVER (PARTITION BY OSCI.ROW_ID) = 1
THEN MAX(SPN.SALESREP_ID) OVER (PARTITION BY OSCI.ROW_ID) -- Sales Person Id Based on Name
ELSE NULL
END) SALESPERSON_ID
FROM (SELECT SCI.ROWID ROW_ID, SCI.SALES_CREDIT_INTERFACE_ID, SCI.SALESPERSON_NAME, SCI.SALESPERSON_ID,
SCI.SALESGROUP_ID, SCI.SALES_CREDIT_TYPE, SCI.PERCENT, HST.OPERATING_UNIT_ID
FROM OKS_SALES_CREDITS_INTERFACE SCI, OKS_INT_HEADER_STG_TEMP HST
WHERE SCI.HEADER_INTERFACE_ID = HST.HEADER_INTERFACE_ID) OSCI
,JTF_RS_SALESREPS SPI
,(SELECT SP.ROWID ROW_ID, SPTL.RESOURCE_NAME, SP.ORG_ID, SP.SALESREP_ID
FROM JTF_RS_RESOURCE_EXTNS_TL SPTL, JTF_RS_SALESREPS SP
WHERE SP.RESOURCE_ID = SPTL.RESOURCE_ID
AND SPTL.LANGUAGE = USERENV('LANG')) SPN
WHERE OSCI.SALESPERSON_ID = SPI.SALESREP_ID (+)
AND OSCI.OPERATING_UNIT_ID = SPI.ORG_ID (+)
AND OSCI.SALESPERSON_NAME = SPN.RESOURCE_NAME (+)
AND OSCI.OPERATING_UNIT_ID = SPN.ORG_ID (+)) NAMEID_Q
WHERE OSC.SALES_CREDIT_INTERFACE_ID = NAMEID_Q.SALES_CREDIT_INTERFACE_ID
AND OSC.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
AND OSC.SALES_CREDIT_TYPE = SCT.NAME (+);
SELECT count(1) INTO l_int_count FROM OKS_SALES_CREDITS_INTERFACE OSCI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
WHERE HEADER_INTERFACE_ID = OSCI.HEADER_INTERFACE_ID);
SELECT count(1) INTO l_stg_count FROM OKS_INT_SALES_CREDIT_STG_TEMP;
'Number of records inserted into staging table = '|| l_stg_count);
INSERT ALL
WHEN (NOTE_STATUS_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NTSTAT'
,NULL)
WHEN (NOTE_TYPE_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NTTYPE'
,NULL)
WHEN (ENTERED_BY_VALID ='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_USR'
,NULL)
WHEN (ENTERED_DT_VALID ='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_ENTDT'
,NULL)
WHEN (NOTES IS NULL ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NOTES'
,NULL)
SELECT ONI.NOTES_INTERFACE_ID NOTES_INTERFACE_ID
,ONI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OHSTG.HEADER_INTERFACE_ROWID
,ONI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,RTRIM(ONI.NOTES) NOTES
,ONI.NOTES_DETAIL NOTES_DETAIL
,ONI.NOTE_STATUS NOTE_STATUS
,(CASE WHEN NOTE_STATUS IS NULL THEN 'N'
WHEN COUNT(DISTINCT NTSTATUS.ROWID) OVER (PARTITION BY ONI.ROWID) <> 1 THEN 'N'
ELSE 'Y'
END) NOTE_STATUS_VALID
,ONI.NOTE_TYPE NOTE_TYPE
,(CASE WHEN NOTE_TYPE IS NULL THEN 'N'
WHEN COUNT(DISTINCT NTTYPE.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
ELSE 'Y'
END) NOTE_TYPE_VALID
,ONI.ENTERED_BY ENTERED_BY
,(CASE WHEN COUNT(DISTINCT USR.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
ELSE 'Y'
END) ENTERED_BY_VALID
,ONI.ENTERED_DATE ENTERED_DATE
,(CASE WHEN ONI.ENTERED_DATE > SYSDATE THEN 'N'
ELSE 'Y'
END) ENTERED_DT_VALID
FROM OKS_NOTES_INTERFACE ONI
,OKS_INT_HEADER_STG_TEMP OHSTG
,FND_LOOKUPS NTSTATUS
,FND_LOOKUPS NTTYPE
,FND_USER USR
WHERE ONI.HEADER_INTERFACE_ID = OHSTG.HEADER_INTERFACE_ID
AND ONI.LINE_INTERFACE_ID IS NULL
AND ONI.NOTE_STATUS =NTSTATUS.LOOKUP_CODE (+)
AND NTSTATUS.LOOKUP_TYPE (+) ='JTF_NOTE_STATUS'
AND ONI.NOTE_TYPE = NTTYPE.LOOKUP_CODE (+)
AND NTTYPE.LOOKUP_TYPE (+) ='JTF_NOTE_TYPE'
AND ONI.ENTERED_BY = USR.USER_ID (+);
INSERT ALL
WHEN (NOTE_STATUS_VALID ='N' ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NTSTAT'
,NULL)
WHEN (NOTE_TYPE_VALID='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NTTYPE'
,NULL)
WHEN (ENTERED_BY_VALID ='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_USR'
,NULL)
WHEN (ENTERED_DT_VALID ='N') THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_ENTDT'
,NULL)
WHEN (NOTES IS NULL ) THEN
INTO OKS_INT_ERROR_STG_TEMP
(CONCURRENT_REQUEST_ID
,HEADER_INTERFACE_ROWID
,INTERFACE_SOURCE_TABLE
,INTERFACE_ID
,ERROR_MSG
,MSG_TOKENS)
VALUES (G_WORKER_REQ_ID
,HEADER_INTERFACE_ROWID
,'OKS_NOTES_INTERFACE'
,NOTES_INTERFACE_ID
,'OKS_IMP_NTINT_INVALID_NOTES'
,NULL)
SELECT ONI.NOTES_INTERFACE_ID NOTES_INTERFACE_ID
,ONI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
,ONI.LINE_INTERFACE_ID LINE_INTERFACE_ID
,RTRIM(ONI.NOTES) NOTES
,ONI.NOTES_DETAIL NOTES_DETAIL
,ONI.NOTE_STATUS NOTE_STATUS
,(CASE WHEN NOTE_STATUS IS NULL THEN 'N'
WHEN COUNT(DISTINCT NTSTATUS.ROWID) OVER (PARTITION BY ONI.ROWID) <> 1 THEN 'N'
ELSE 'Y'
END) NOTE_STATUS_VALID
,ONI.NOTE_TYPE NOTE_TYPE
,(CASE WHEN NOTE_TYPE IS NULL THEN 'N'
WHEN COUNT(DISTINCT NTTYPE.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
ELSE 'Y'
END) NOTE_TYPE_VALID
,ONI.ENTERED_BY ENTERED_BY
,(CASE WHEN COUNT(DISTINCT USR.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
ELSE 'Y'
END) ENTERED_BY_VALID
,ONI.ENTERED_DATE ENTERED_DATE
,(CASE WHEN ONI.ENTERED_DATE > SYSDATE THEN 'N'
ELSE 'Y'
END) ENTERED_DT_VALID
FROM OKS_NOTES_INTERFACE ONI
,OKS_INT_LINE_STG_TEMP OLSTG
,FND_LOOKUPS NTSTATUS
,FND_LOOKUPS NTTYPE
,FND_USER USR
WHERE ONI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
AND ONI.NOTE_STATUS =NTSTATUS.LOOKUP_CODE (+)
AND NTSTATUS.LOOKUP_TYPE (+) ='JTF_NOTE_STATUS'
AND ONI.NOTE_TYPE = NTTYPE.LOOKUP_CODE (+)
AND NTTYPE.LOOKUP_TYPE (+) ='JTF_NOTE_TYPE'
AND ONI.ENTERED_BY = USR.USER_ID (+);
SELECT count(1) INTO l_line_notes_count FROM OKS_NOTES_INTERFACE ONI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
WHERE LINE_INTERFACE_ID = ONI.LINE_INTERFACE_ID);
SELECT count(1) INTO l_header_notes_count FROM OKS_NOTES_INTERFACE ONI
WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
WHERE HEADER_INTERFACE_ID = ONI.HEADER_INTERFACE_ID)
AND ONI.LINE_INTERFACE_ID IS NULL;
/* delete from errors table all the records related to the contracts which are going to be validated by this worker.*/
DELETE FROM OKS_IMP_ERRORS
WHERE HEADER_INTERFACE_ID IN (SELECT HEADER_INTERFACE_ID
FROM OKS_HEADERS_INTERFACE
WHERE rowid between P_rowid_from and P_rowid_to
AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R'));