[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Interface_Records (X_errbuf OUT NOCOPY VARCHAR2,
X_retcode OUT NOCOPY VARCHAR2,
p_contract_number IN VARCHAR2,
p_contract_modifier IN VARCHAR2,
p_target_contract IN VARCHAR2,
p_num_scenarios IN NUMBER)
IS
l_stmt_num NUMBER := 0;
l_routine CONSTANT VARCHAR2(30) := 'Insert_Interface_Records';
SELECT OKS_HEADERS_INT_BATCH_S.nextval into l_batch_id from dual;
INSERT INTO OKS_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID
,CONTRACT_NUMBER
,CONTRACT_NUMBER_MODIFIER
,CATEGORY
,START_DATE
,END_DATE
,STATUS_CODE
,KNOWN_AS
,DESCRIPTION
,OPERATING_UNIT_ID
,CUSTOMER_PARTY_NAME
,CUSTOMER_PARTY_NUMBER
,CUSTOMER_PARTY_ID
,THIRD_PARTY_NAME
,THIRD_PARTY_NUMBER
,THIRD_PARTY_ID
,BILL_TO_SITE_USAGE_CODE
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_CODE
,SHIP_TO_SITE_USAGE_ID
,SALESPERSON_NAME
,SALESPERSON_ID
,SALESGROUP_ID
,CUSTOMER_CONTRACT_ADMIN_NAME
,CUSTOMER_CONTRACT_ADMIN_ID
,CONTRACT_CURRENCY_CODE
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,AGREEMENT_NAME
,AGREEMENT_ID
,PRICE_LIST_NAME
,PRICE_LIST_ID
,PAYMENT_TERMS_NAME
,PAYMENT_TERMS_ID
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,EXEMPT_REASON_CODE
,BILL_SERVICES
,BILLING_TRANSACTION_TYPE_ID
,ACCOUNTING_RULE_NAME
,ACCOUNTING_RULE_ID
,INVOICING_RULE_NAME
,INVOICING_RULE_ID
,HOLD_CREDITS
,SUMMARY_PRINT
,SUMMARY_TRANSACTIONS
,SERVICE_CHRG_PREPAY_REQ
,SERVICE_CHARGES_PO_REQUIRED
,SERVICE_CHARGES_PO_NUMBER
,RENEWAL_PROCESS
,APPROVAL_REQUIRED
,RENEW_UP_TO
,PRICING_METHOD
,RENEWAL_PRICE_LIST_NAME
,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
,DATE_APPROVED
,DATE_SIGNED
,DATE_CANCELED
,CANCELLATION_REASON
,GRACE_DURATION
,GRACE_PERIOD
,ESTIMATION_PERCENT
,ESTIMATION_DATE
,FOLLOW_UP_DUE_DATE
,FOLLOW_UP_ACTION
,QA_CHECKLIST
,CONTRACT_GROUP_NAME
,CONTRACT_GROUP_ID
,APPROVAL_PROCESS_ID
,FULLY_BILLED
,INTERFACE_STATUS
,INV_ORGANIZATION_ID
,BATCH_ID
,SOURCE
,DOCUMENT
)
SELECT OKS_HEADERS_INT_ID_S.nextval AS HEADER_INTERFACE_ID
,p_target_contract AS CONTRACT_NUMBER
,NUM_VIEW.CONTRACT_NUMBER_MODIFIER AS CONTRACT_NUMBER_MODIFIER
,H.SCS_CODE AS CATEGORY
,H.START_DATE AS START_DATE
,H.END_DATE AS END_DATE
,H.STS_CODE AS STATUS_CODE
,HL.COGNOMEN AS KNOWN_AS
,HL.SHORT_DESCRIPTION AS DESCRIPTION
,H.ORG_ID AS OPERATING_UNIT_ID
,HZP.PARTY_NAME AS CUSTOMER_PARTY_NAME
,HZP.PARTY_NUMBER AS CUSTOMER_PARTY_NUMBER
,HZP.PARTY_ID AS CUSTOMER_PARTY_ID
,HZP3.PARTY_NAME AS THIRD_PARTY_NAME
,HZP3.PARTY_NUMBER AS THIRD_PARTY_NUMBER
,HZP3.PARTY_ID AS THIRD_PARTY_ID
,BSU.LOCATION AS BILL_TO_SITE_USAGE_CODE
,H.BILL_TO_SITE_USE_ID AS BILL_TO_SITE_USAGE_ID
,SSU.LOCATION AS SHIP_TO_SITE_USAGE_CODE
,H.SHIP_TO_SITE_USE_ID AS SHIP_TO_SITE_USAGE_ID
,SLSREPNAME.RESOURCE_NAME AS SALESPERSON_NAME
,SLSREPID.SALESREP_ID AS SALESPERSON_ID
,SLSREP.SALES_GROUP_ID AS SALESGROUP_ID
,CONADMNNAME.NAME AS CUSTOMER_CONTRACT_ADMIN_NAME
,CONADM.OBJECT1_ID1 AS CUSTOMER_CONTRACT_ADMIN_ID
,H.CURRENCY_CODE AS CONTRACT_CURRENCY_CODE
,H.CONVERSION_TYPE AS CURRENCY_CONVERSION_TYPE
,H.CONVERSION_RATE AS CURRENCY_CONVERSION_RATE
,H.CONVERSION_RATE_DATE AS CURRENCY_CONVERSION_DATE
,AGRNAME.NAME AS AGREEMENT_NAME
,AGRID.ISA_AGREEMENT_ID AS AGREEMENT_ID
,PRLNAME.NAME AS PRICE_LIST_NAME
,H.PRICE_LIST_ID AS PRICE_LIST_ID
,PAYTERMNAME.NAME AS PAYMENT_TERMS_NAME
,H.PAYMENT_TERM_ID AS PAYMENT_TERMS_ID
,H.PAYMENT_INSTRUCTION_TYPE AS PAYMENT_INSTRUCTION
,H.CUST_PO_NUMBER_REQ_YN AS PO_REQUIRED
,H.CUST_PO_NUMBER AS PAYMENT_INSTRUCTION_DETAILS
,HS.PAYMENT_TYPE AS PAYMENT_METHOD_CODE
,HS.COMMITMENT_ID AS COMMITMENT_ID
,HS.TAX_STATUS AS TAX_EXEMPTION_CONTROL
,HS.EXEMPT_CERTIFICATE_NUMBER AS TAX_EXEMPTION_NUMBER
,HS.EXEMPT_REASON_CODE AS EXEMPT_REASON_CODE
,HS.AR_INTERFACE_YN AS BILL_SERVICES
,HS.INV_TRX_TYPE AS BILLING_TRANSACTION_TYPE_ID
,nvl(ACCTRULE.NAME, 'Immediate') AS ACCOUNTING_RULE_NAME
,ACCTRULE.ID1 AS ACCOUNTING_RULE_ID
,nvl(INVRULE.NAME, 'Advance Invoice') AS INVOICING_RULE_NAME
,INVRULE.ID1 AS INVOICING_RULE_ID
,HS.HOLD_BILLING AS HOLD_CREDITS
,HS.INV_PRINT_PROFILE AS SUMMARY_PRINT
,HS.SUMMARY_TRX_YN AS SUMMARY_TRANSACTIONS
,H.PRE_PAY_REQ_YN AS SERVICE_CHRG_PREPAY_REQ
,HS.SERVICE_PO_REQUIRED AS SERVICE_CHARGES_PO_REQUIRED
,HS.SERVICE_PO_NUMBER AS SERVICE_CHARGES_PO_NUMBER
,H.RENEWAL_TYPE_CODE AS RENEWAL_PROCESS
,H.APPROVAL_TYPE AS APPROVAL_REQUIRED
,H.RENEWAL_END_DATE AS RENEW_UP_TO
,HS.RENEWAL_PRICING_TYPE AS RENEWAL_PRICING_METHOD
,RENPRLLST.NAME AS RENEWAL_PRICE_LIST_NAME
,HS.RENEWAL_PRICE_LIST AS RENEWAL_PRICE_LIST_ID
,HS.RENEWAL_MARKUP_PERCENT AS RENEWAL_MARKUP
,HS.BILLING_PROFILE_ID AS RENEWAL_BILLING_PROFILE_ID
,HS.RENEWAL_PO_NUMBER AS RENEWAL_PO_NUMBER
,HS.RENEWAL_PO_REQUIRED AS RENEWAL_PO_REQUIRED
,HS.RENEWAL_GRACE_DURATION AS RENEWAL_GRACE_DURATION
,HS.RENEWAL_GRACE_PERIOD AS RENEWAL_GRACE_PERIOD
,HS.RENEWAL_EST_REV_PERCENT AS RENEWAL_ESTIMATED_PERCENT
,HS.RENEWAL_EST_REV_DURATION AS RENEWAL_ESTIMATED_DURATION
,HS.RENEWAL_EST_REV_PERIOD AS RENEWAL_ESTIMATED_PERIOD
,HS.QUOTE_TO_SITE_ID AS QUOTE_TO_PARTY_SITE
,HS.QUOTE_TO_CONTACT_ID AS QUOTE_TO_CONTACT
,HS.QUOTE_TO_PHONE_ID AS QUOTE_TO_PHONE
,HS.QUOTE_TO_FAX_ID AS QUOTE_TO_FAX
,HS.QUOTE_TO_EMAIL_ID AS QUOTE_TO_EMAIL
,H.DATE_APPROVED AS DATE_APPROVED
,H.DATE_SIGNED AS DATE_SIGNED
,H.DATETIME_CANCELLED AS DATE_CANCELED
,H.TRN_CODE AS CANCELLATION_REASON
,HS.GRACE_DURATION AS GRACE_DURATION
,HS.GRACE_PERIOD AS GRACE_PERIOD
,HS.EST_REV_PERCENT AS ESTIMATION_PERCENT
,HS.EST_REV_DATE AS ESTIMATION_DATE
,HS.FOLLOW_UP_DATE AS FOLLOW_UP_DUE_DATE
,HS.FOLLOW_UP_ACTION AS FOLLOW_UP_ACTION
,H.QCL_ID AS QA_CHECKLIST
,GRTL.NAME AS CONTRACT_GROUP_NAME
,GRTL.ID AS CONTRACT_GROUP_ID
,KPROCS.PDF_ID AS APPROVAL_PROCESS_ID
,'N' AS FULLY_BILLED
,NULL AS INTERFACE_STATUS
,H.INV_ORGANIZATION_ID AS INV_ORGANIZATION_ID
,l_batch_id
,'OKS_IMPORT'
,'Doc'||l_batch_id
FROM
OKC_K_HEADERS_ALL_B H
,OKS_K_HEADERS_B HS
,OKC_K_HEADERS_TL HL
,OKC_K_PARTY_ROLES_B PTY
,HZ_PARTIES HZP
,OKC_K_PARTY_ROLES_B PTY3
,HZ_PARTIES HZP3
,HZ_CUST_SITE_USES_ALL BSU
,HZ_CUST_SITE_USES_ALL SSU
,OKC_K_PARTY_ROLES_B VNDRPTY
,OKC_CONTACTS SLSREP
,JTF_RS_SALESREPS SLSREPID
,JTF_RS_RESOURCE_EXTNS_VL SLSREPNAME
,OKC_CONTACTS CONADM
,OKX_PARTY_CONTACTS_V CONADMNNAME
,OE_AGREEMENTS_VL AGRNAME
,OKC_GOVERNANCES AGRID
,QP_LIST_HEADERS_VL PRLNAME
,RA_TERMS_TL PAYTERMNAME
,OKX_RULES_V ACCTRULE
,OKX_RULES_V INVRULE
,QP_LIST_HEADERS_VL RENPRLLST
,OKC_K_GROUPS_TL GRTL
,OKC_K_GRPINGS GRPNG
,OKC_K_PROCESSES KPROCS
,(SELECT ROWNUM AS CONTRACT_NUMBER_MODIFIER FROM DUAL CONNECT BY LEVEL <=p_num_scenarios) NUM_VIEW
WHERE
H.ID = HS.CHR_ID
AND H.ID = HL.ID
AND HL.LANGUAGE = USERENV('LANG')
AND H.ID = PTY.CHR_ID
AND PTY.OBJECT1_ID1 = HZP.PARTY_ID
AND PTY.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND PTY.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
AND H.ID = PTY3.CHR_ID(+)
AND PTY3.OBJECT1_ID1 = HZP3.PARTY_ID(+)
AND PTY3.JTOT_OBJECT1_CODE(+) = 'OKX_PARTY'
AND PTY3.RLE_CODE(+) = 'THIRD_PARTY'
AND H.BILL_TO_SITE_USE_ID = BSU.SITE_USE_ID
AND H.SHIP_TO_SITE_USE_ID = SSU.SITE_USE_ID
AND H.ID = VNDRPTY.CHR_ID
AND VNDRPTY.RLE_CODE IN ('VENDOR','MERCHANT')
AND VNDRPTY.ID = SLSREP.CPL_ID
AND SLSREP.CRO_CODE = 'SALESPERSON'
AND SLSREP.OBJECT1_ID1 = SLSREPID.SALESREP_ID
AND SLSREPID.ORG_ID = H.AUTHORING_ORG_ID
AND SLSREPID.RESOURCE_ID = SLSREPNAME.RESOURCE_ID
AND PTY.ID = CONADM.CPL_ID(+)
AND CONADM.CRO_CODE(+) = 'ADMIN'
AND CONADM.OBJECT1_ID1 = CONADMNNAME.ID1(+)
AND CONADM.JTOT_OBJECT1_CODE(+) = 'OKX_PCONTACT'
AND H.ID = AGRID.CHR_ID(+)
AND AGRID.ISA_AGREEMENT_ID = AGRNAME.AGREEMENT_ID(+)
AND H.PRICE_LIST_ID = PRLNAME.LIST_HEADER_ID(+)
AND H.PAYMENT_TERM_ID = PAYTERMNAME.TERM_ID(+)
AND PAYTERMNAME.LANGUAGE (+) = USERENV('LANG')
AND HS.ACCT_RULE_ID = ACCTRULE.ID1 (+)
AND ACCTRULE.TYPE (+) = 'A'
AND H.INV_RULE_ID = INVRULE.ID1 (+)
AND INVRULE.TYPE (+) = 'I'
AND HS.RENEWAL_PRICE_LIST = RENPRLLST.LIST_HEADER_ID(+)
AND H.ID = GRPNG.INCLUDED_CHR_ID(+)
AND GRPNG.CGP_PARENT_ID = GRTL.ID(+)
AND GRTL.LANGUAGE(+) = USERENV('LANG')
AND H.ID = KPROCS.CHR_ID(+)
AND H.CONTRACT_NUMBER = p_contract_number
AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1');
FND_FILE.put_line(FND_FILE.OUTPUT, 'Batch Id '|| l_batch_id ||' inserted into interface tables');
INSERT INTO OKS_LINES_INTERFACE
(
LINE_INTERFACE_ID
,HEADER_INTERFACE_ID
,LINE_NUMBER
,LINE_TYPE
,ITEM_ORGANIZATION_ID
,ITEM_NAME
,ITEM_ID
,REFERENCE_TEMPLATE_ID
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,BILL_TO_SITE_USAGE_CODE
,BILL_TO_SITE_USAGE_ID
,SHIP_TO_SITE_USAGE_CODE
,SHIP_TO_SITE_USAGE_ID
,BILLING_CONTACT_NAME
,BILLING_CONTACT_ID
,SHIPPING_CONTACT_NAME
,SHIPPING_CONTACT_ID
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,PRICE_LIST_NAME
,PRICE_LIST_ID
,INVOICE_TEXT
,PRINT_INVOICE
,SUBTOTAL
,TAX_AMOUNT
,TAX_EXEMPTION_CONTROL
,TAX_EXEMPTION_NUMBER
,EXEMPT_REASON_CODE
,TAX_CLASSIFICATION_CODE
,PAYMENT_INSTRUCTION
,PO_REQUIRED
,PAYMENT_INSTRUCTION_DETAILS
,PAYMENT_METHOD_CODE
,COMMITMENT_ID
,ACCOUNTING_RULE_NAME
,ACCOUNTING_RULE_ID
,INVOICING_RULE_NAME
,INVOICING_RULE_ID
,RECUR_BILL_OCCURANCES
,BILLING_INTERVAL_DURATION
,BILLING_INTERVAL_PERIOD
,FIRST_BILL_UPTO_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
)
SELECT
OKS_LINES_INT_ID_S.nextval AS LINE_INTERFACE_ID
,OHI.HEADER_INTERFACE_ID AS HEADER_INTERFACE_ID
,LINE.LINE_NUMBER AS LINE_NUMBER
,LTYST.LTY_CODE AS LINE_TYPE
,MTL.ORGANIZATION_ID AS ITEM_ORGANIZATION_ID
,MTL.CONCATENATED_SEGMENTS AS ITEM_NAME
,MTL.INVENTORY_ITEM_ID AS ITEM_ID
,MTL.COVERAGE_SCHEDULE_ID AS REFERENCE_TEMPLATE_ID
,LINE.COGNOMEN AS LINE_REFERENCE
,LINE.STS_CODE AS STATUS_CODE
,LINE.START_DATE AS START_DATE
,LINE.END_DATE AS END_DATE
,BSU.LOCATION AS BILL_TO_SITE_USAGE_CODE
,LINE.BILL_TO_SITE_USE_ID AS BILL_TO_SITE_USAGE_ID
,SSU.LOCATION AS SHIP_TO_SITE_USAGE_CODE
,LINE.SHIP_TO_SITE_USE_ID AS SHIP_TO_SITE_USAGE_ID
,BILLTOCONTNAME.CONTACT_NAME AS BILLING_CONTACT_NAME
,BILLTOCONT.OBJECT1_ID1 AS BILLING_CONTACT_ID
,SHIPTOCONTNAME.CONTACT_NAME AS SHIPPING_CONTACT_NAME
,SHIPTOCONT.OBJECT1_ID1 AS SHIPPING_CONTACT_ID
,LINE.LINE_RENEWAL_TYPE_CODE AS RENEWAL_TYPE_CODE
,LINE.DATE_CANCELLED AS CANCELLATION_DATE
,LINE.TRN_CODE AS CANCELLATION_REASON
,PRLNAME.NAME AS PRICE_LIST_NAME
,LINE.PRICE_LIST_ID AS PRICE_LIST_ID
,LS.INVOICE_TEXT AS INVOICE_TEXT
,LS.INV_PRINT_FLAG AS PRINT_INVOICE
,LINE.PRICE_NEGOTIATED AS SUBTOTAL
,LS.TAX_AMOUNT AS TAX_AMOUNT
,LS.TAX_STATUS AS TAX_EXEMPTION_CONTROL
,LS.EXEMPT_CERTIFICATE_NUMBER AS TAX_EXEMPTION_NUMBER
,LS.EXEMPT_REASON_CODE AS EXEMPT_REASON_CODE
,LS.TAX_CLASSIFICATION_CODE AS TAX_CLASSIFICATION_CODE
,LINE.PAYMENT_INSTRUCTION_TYPE AS PAYMENT_INSTRUCTION
,LS.CUST_PO_NUMBER_REQ_YN AS PO_REQUIRED
,LS.CUST_PO_NUMBER AS PAYMENT_INSTRUCTION_DETAILS
,LS.PAYMENT_TYPE AS PAYMENT_METHOD_CODE
,LS.COMMITMENT_ID AS COMMITMENT_ID
,nvl(ACCTRULE.NAME, 'Immediate') AS ACCOUNTING_RULE_NAME
,ACCTRULE.ID1 AS ACCOUNTING_RULE_ID
,nvl(INVRULE.NAME, 'Advance Invoice') AS INVOICING_RULE_NAME
,INVRULE.ID1 AS INVOICING_RULE_ID
,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,12) AS RECUR_BILL_OCCURANCES
,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,12) AS BILLING_INTERVAL_DURATION
,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,'MTH') AS BILLING_INTERVAL_PERIOD
,(LINE.START_DATE + 1) AS FIRST_BILL_UPTO_DATE
,DECODE(LINE.LSE_ID,46,KITEMS.NUMBER_OF_ITEMS,NULL) AS SUBSCRIPTION_QUANTITY
,DECODE(LINE.LSE_ID,46,KITEMS.UOM_CODE,NULL) AS QUANTITY_UOM
,LS.PRICE_UOM AS PRICE_UOM
,LINE.PRICE_UNIT AS UNIT_PRICE
,20 AS FIRST_BILLED_AMOUNT
,10 AS LAST_BILLED_AMOUNT
,LS.USAGE_TYPE AS USAGE_TYPE
,LS.USAGE_PERIOD AS USAGE_PERIOD
,LS.AVERAGING_INTERVAL AS AVERAGING_INTERVAL
,LS.SETTLEMENT_INTERVAL AS SETTLEMENT_INTERVAL
,LS.TERMN_METHOD AS USAGE_TERMINATION_METHOD
FROM
OKC_K_HEADERS_ALL_B H
,OKC_K_LINES_V LINE
,OKS_K_LINES_V LS
,OKC_LINE_STYLES_V LTYST
,OKC_K_ITEMS KITEMS
,MTL_SYSTEM_ITEMS_B_KFV MTL
,HZ_CUST_SITE_USES_ALL BSU
,HZ_CUST_SITE_USES_ALL SSU
,OKC_K_PARTY_ROLES_B PTY
,OKC_CONTACTS BILLTOCONT
,OKC_CONTACTS SHIPTOCONT
,(SELECT 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') BILLTOCONTNAME
,(SELECT 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') SHIPTOCONTNAME
,QP_LIST_HEADERS_VL PRLNAME
,OKX_RULES_V ACCTRULE
,OKX_RULES_V INVRULE
,OKS_HEADERS_INTERFACE OHI
WHERE
H.ID = LINE.CHR_ID
AND LINE.LSE_ID IN (1,12,14,19,46)
AND LINE.ID = LS.CLE_ID
AND LINE.LSE_ID = LTYST.ID
AND KITEMS.CLE_ID = LINE.ID
AND MTL.INVENTORY_ITEM_ID = KITEMS.OBJECT1_ID1
AND MTL.ORGANIZATION_ID = KITEMS.OBJECT1_ID2
AND KITEMS.JTOT_OBJECT1_CODE IN ('OKX_WARRANTY','OKX_SERVICE','OKS_SUBSCRIPTION','OKX_USAGE')
AND LINE.BILL_TO_SITE_USE_ID = BSU.SITE_USE_ID
AND LINE.SHIP_TO_SITE_USE_ID = SSU.SITE_USE_ID
AND LINE.ID = PTY.CLE_ID
--AND PTY.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
AND PTY.ID = BILLTOCONT.CPL_ID
AND BILLTOCONT.CRO_CODE = 'CUST_BILLING'
AND PTY.ID = SHIPTOCONT.CPL_ID
AND SHIPTOCONT.CRO_CODE = 'CUST_SHIPPING'
AND BILLTOCONT.OBJECT1_ID1 = BILLTOCONTNAME.CONTACT_ID
AND BILLTOCONT.JTOT_OBJECT1_CODE = 'OKX_CONTBILL'
AND SHIPTOCONT.OBJECT1_ID1 = SHIPTOCONTNAME.CONTACT_ID
AND SHIPTOCONT.JTOT_OBJECT1_CODE = 'OKX_CONTSHIP'
AND LINE.PRICE_LIST_ID = PRLNAME.LIST_HEADER_ID(+)
AND LS.ACCT_RULE_ID = ACCTRULE.ID1 (+)
AND ACCTRULE.TYPE (+) = 'A'
AND LINE.INV_RULE_ID = INVRULE.ID1 (+)
AND INVRULE.TYPE (+) = 'I'
AND H.CONTRACT_NUMBER = p_contract_number
AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(OHI.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND OHI.BATCH_ID = l_batch_id;
INSERT INTO OKS_SALES_CREDITS_INTERFACE
(
SALES_CREDIT_INTERFACE_ID
,HEADER_INTERFACE_ID
,SALESPERSON_NAME
,SALESPERSON_ID
,SALESGROUP_ID
,SALES_CREDIT_TYPE
,PERCENT
)
SELECT OKS_SALES_CREDITS_INT_ID_S.nextval AS SALES_CREDIT_INTERFACE_ID
,OHI.HEADER_INTERFACE_ID AS HEADER_INTERFACE_ID
,SLSREPNAME.RESOURCE_NAME AS SALESPERSON_NAME
,SLSREPID.SALESREP_ID AS SALESPERSON_ID
,OSC.SALES_GROUP_ID AS SALESGROUP_ID
,SCT.NAME AS SALES_CREDIT_TYPE
,PERCENT
FROM OKS_K_SALES_CREDITS OSC
,OKC_K_HEADERS_ALL_B H
,JTF_RS_SALESREPS SLSREPID
,JTF_RS_RESOURCE_EXTNS_VL SLSREPNAME
,OE_SALES_CREDIT_TYPES SCT
,OKS_HEADERS_INTERFACE OHI
WHERE H.ID = OSC.CHR_ID
AND OSC.CLE_ID IS NULL
AND OSC.CTC_ID = SLSREPID.SALESREP_ID
AND SLSREPID.ORG_ID = H.AUTHORING_ORG_ID
AND SLSREPID.RESOURCE_ID = SLSREPNAME.RESOURCE_ID
AND OSC.SALES_CREDIT_TYPE_ID1 = SCT.SALES_CREDIT_TYPE_ID
AND H.CONTRACT_NUMBER = p_contract_number
AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(OHI.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND OHI.BATCH_ID = l_batch_id;
INSERT INTO OKS_COVERED_LEVELS_INTERFACE
(COVERED_LEVEL_INTERFACE_ID
,LINE_INTERFACE_ID
,LINE_NUMBER
,COVERED_SERIAL_NUMBER
,COVERED_INSTANCE_NUMBER
,COVERED_INSTANCE_ID
,COVERED_ITEM_NAME
,COVERED_ITEM_ID
,COVERED_ITEM_ORG_ID
,COVERED_SYSTEM_ID
,COVERED_ACCOUNT_NUMBER
,COVERED_ACCOUNT_ID
,COVERED_SITE_NUMBER
,COVERED_SITE_ID
,COVERED_PARTY_NAME
,COVERED_PARTY_NUMBER
,COVERED_PARTY_ID
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,INVOICE_TEXT
,PRINT_INVOICE
,QUANTITY_COVERED
,QUANTITY_UOM
,PRICE_UOM
,SUBTOTAL
,TAX_AMOUNT
)
SELECT OKS_CLVL_INT_ID_S.NEXTVAL COVERED_LEVEL_INTERFACE_ID
,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OKCLINB_subline.LINE_NUMBER LINE_NUMBER
,null COVERED_SERIAL_NUMBER
,(CASE WHEN OKCLINB_subline.LSE_ID IN (9,18,25) THEN CSIITMINST.INSTANCE_NUMBER
ELSE NULL
END) COVERED_INSTANCE_NUMBER
,(CASE WHEN OKCLINB_subline.LSE_ID IN (9,18,25) THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_INSTANCE_ID
,(CASE WHEN OKCLINB_subline.LSE_ID = 7 THEN MTLSYSITM.concatenated_segments
else null
end) COVERED_ITEM_NAME
,(CASE WHEN OKCLINB_subline.LSE_ID = 7 THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_ITEM_ID
,(CASE when OKCITM.OBJECT1_ID2 <>'#' then OKCITM.OBJECT1_ID2
else null
end)COVERED_ITEM_ORG_ID
,(CASE WHEN OKCLINB_subline.LSE_ID = 11 THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_SYSTEM_ID
,(case when OKCLINB_subline.LSE_ID = 35 then CUSTACC.ACCOUNT_NUMBER
else null
end) COVERED_ACCOUNT_NUMBER
, (CASE WHEN OKCLINB_subline.LSE_ID = 35 THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_ACCOUNT_ID
,(CASE WHEN OKCLINB_subline.LSE_ID = 10 then HZPRTSIT.PARTY_SITE_NUMBER
else null
end) COVERED_SITE_NUMBER
,(CASE WHEN OKCLINB_subline.LSE_ID = 10 THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_SITE_ID
,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN HZPRT.PARTY_NAME
else null
end) COVERED_PARTY_NAME
,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN HZPRT.PARTY_NUMBER
else null
end) COVERED_PARTY_NUMBER
,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN OKCITM.OBJECT1_ID1
ELSE NULL
END) COVERED_PARTY_ID
, OKCLINTL.COGNOMEN LINE_REFERENCE
, OKCLINB_subline.STS_CODE STATUS_CODE
, OKCLINB_subline.START_DATE START_DATE
, OKCLINB_subline.END_DATE END_DATE
, OKCLINB_subline.LINE_RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
, OKCLINB_subline.DATE_CANCELLED CANCELLATION_DATE
, OKCLINB_subline.TRN_CODE CANCELLATION_REASON
, OKSLINTL.INVOICE_TEXT INVOICE_TEXT
, OKSLINB.INV_PRINT_FLAG PRINT_INVOICE
, OKCITM.NUMBER_OF_ITEMS QUANTITY_COVERED
, OKCITM.UOM_CODE QUANTITY_UOM
, OKSLINB.PRICE_UOM PRICE_UOM
, OKCLINB_subline.PRICE_NEGOTIATED SUBTOTAL
, OKSLINB.TAX_AMOUNT TAX_AMOUNT
FROM OKS_LINES_INTERFACE LININT
,OKS_HEADERS_INTERFACE HDRINT
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_ITEMS OKCITM
,CSI_ITEM_INSTANCES CSIITMINST
,HZ_CUST_ACCOUNTS CUSTACC
,MTL_SYSTEM_ITEMS_B_kfv MTLSYSITM
,HZ_PARTIES HZPRT
,HZ_PARTY_SITES HZPRTSIT
,CSI_SYSTEMS_B CSISYS
,OKC_K_LINES_TL OKCLINTL
,OKS_K_LINES_B OKSLINB
,OKS_K_LINES_TL OKSLINTL
,okc_k_lines_b okclinb_line
,OKC_K_LINES_B OKCLINB_subline
WHERE LININT.HEADER_INTERFACE_ID = HDRINT.HEADER_INTERFACE_ID
and OKCLINB_subline.dnz_chr_id = okchdrb.id
and OKCLINB_subline.cle_id =okclinb_line.id
and okclinb_line.line_number = linint.line_number
and okclinb_line.chr_id = okchdrb.id
and okclinb_line.dnz_chr_id =okchdrb.id
AND OKCITM.CLE_ID= OKCLINB_subline.id
and OKCITM.DNZ_CHR_ID = OKCLINB_subline.DNZ_CHR_ID
AND OKCITM.OBJECT1_ID1 = CSIITMINST.INSTANCE_ID (+)
AND OKCITM.OBJECT1_ID1 = CUSTACC.CUST_ACCOUNT_ID (+)
AND OKCITM.OBJECT1_ID1 = MTLSYSITM.INVENTORY_ITEM_ID (+)
AND OKCITM.OBJECT1_ID2 = to_char(MTLSYSITM.ORGANIZATION_ID (+))
AND OKCITM.OBJECT1_ID1 = HZPRT.PARTY_ID (+)
AND OKCITM.OBJECT1_ID1 = CSISYS.SYSTEM_ID(+)
and OKCITM.OBJECT1_ID1 = HZPRTSIT.party_site_id (+)
and OKCLINTL.ID = OKCLINB_subline.id
AND OKSLINB.CLE_ID =OKCLINB_subline.id
AND OKSLINB.DNZ_CHR_ID=OKCLINB_subline.dnz_chr_id
AND OKSLINB.ID = OKSLINTL.ID
and OKCLINB_subline.lse_id in (7,8,9,10,11,18,25,35)
AND OKSLINTL.LANGUAGE = USERENV('LANG')
AND OKCLINTL.LANGUAGE = USERENV('LANG')
AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
AND nvl(OKCHDRB.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND HDRINT.BATCH_ID = l_batch_id;
INSERT INTO OKS_USAGE_COUNTERS_INTERFACE
(USAGE_COUNTER_INTERFACE_ID
,LINE_INTERFACE_ID
,LINE_NUMBER
,COUNTER_ID
,LINE_REFERENCE
,STATUS_CODE
,START_DATE
,END_DATE
,RENEWAL_TYPE_CODE
,CANCELLATION_DATE
,CANCELLATION_REASON
,INVOICE_TEXT
,PRINT_INVOICE
,SUBTOTAL
,FIXED_USG_CNT
,MINIMUM_USG_CNT
,DEFAULT_USG_CNT
,FILL_YN
,ESTIMATION_METHOD
,ESTIMATION_START_DATE
,LEVEL_YN
,TAX_AMOUNT)
SELECT OKS_USGCNTR_INT_ID_S.NEXTVAL USAGE_COUNTER_INTERFACE_ID
,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
,OKCLINB_subline.LINE_NUMBER LINE_NUMBER
,OKCITM.OBJECT1_ID1 COUNTER_ID -- open issue, need clarification
,OKCLINTL.COGNOMEN LINE_REFERENCE
,OKCLINB_subline.STS_CODE STATUS_CODE
,OKCLINB_subline.START_DATE START_DATE
,OKCLINB_subline.END_DATE END_DATE
,OKCLINB_subline.LINE_RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
,OKCLINB_subline.DATE_CANCELLED CANCELLATION_DATE
,OKCLINB_subline.TRN_CODE CANCELLATION_REASON
,OKSLINTL.INVOICE_TEXT INVOICE_TEXT
,OKSLINB.INV_PRINT_FLAG PRINT_INVOICE
,OKCLINB_subline.PRICE_NEGOTIATED SUBTOTAL
,OKSLINB.FIXED_QUANTITY FIXED_USG_CNT
,OKSLINB.MINIMUM_QUANTITY MINIMUM_USG_CNT
,OKSLINB.DEFAULT_QUANTITY DEFAULT_USG_CNT
,OKSLINB.USAGE_EST_YN FILL_YN
,OKSLINB.USAGE_EST_METHOD ESTIMATION_METHOD
,OKSLINB.USAGE_EST_START_DATE ESTIMATION_START_DATE
,OKSLINB.LEVEL_YN LEVEL_YN
,OKSLINB.TAX_AMOUNT TAX_AMOUNT
FROM OKS_LINES_INTERFACE LININT
,OKS_K_LINES_B OKSLINB
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKS_HEADERS_INTERFACE HDRINT
,OKC_K_LINES_TL OKCLINTL
,OKS_K_LINES_TL OKSLINTL
,OKC_K_LINES_B OKCLINB_LINE
,OKC_K_LINES_B OKCLINB_SUBLINE
,okc_k_items okcitm
WHERE HDRINT.HEADER_INTERFACE_ID = LININT.HEADER_INTERFACE_ID
AND LININT.LINE_TYPE = 'USAGE'
AND LININT.LINE_NUMBER= OKCLINB_LINE.LINE_NUMBER
AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
AND OKCLINB_LINE.LSE_ID = 12 -- defaulted to 13 for usage counters
AND OKCLINB_SUBLINE.LSE_ID = 13
AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
AND OKCLINTL.ID = OKCLINB_SUBLINE.ID
AND OKCLINTL.LANGUAGE = USERENV('LANG')
AND OKSLINTL.ID = OKSLINB.ID
AND OKSLINTL.LANGUAGE = USERENV('LANG')
and OKCITM.dnz_chr_id = okchdrb.id
and okcitm.cle_id = okclinb_subline.id
AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
AND nvl(OKCHDRB.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND HDRINT.BATCH_ID = l_batch_id;
INSERT INTO OKS_NOTES_INTERFACE
(NOTES_INTERFACE_ID
,HEADER_INTERFACE_ID
,LINE_INTERFACE_ID
,NOTES
,NOTES_DETAIL
,NOTE_STATUS
,NOTE_TYPE
,ENTERED_BY
,ENTERED_DATE)
SELECT oks_notes_int_id_s.NEXTVAL NOTES_INTERFACE_ID
,HDRINT.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
,null LINE_INTERFACE_ID
,JTFNTL.NOTES NOTES
,JTFNTL.NOTES_DETAIL NOTES_DETAIL
,JTFNTB.NOTE_STATUS NOTE_STATUS
,JTFNTB.NOTE_TYPE NOTE_TYPE
,JTFNTB.ENTERED_BY ENTERED_BY
,JTFNTB.ENTERED_DATE ENTERED_DATE
FROM JTF_NOTES_B JTFNTB
,JTF_NOTES_TL JTFNTL
,OKS_HEADERS_INTERFACE HDRINT
,OKC_K_HEADERS_ALL_B OKCHDRB
WHERE JTFNTB.SOURCE_OBJECT_CODE = 'OKS_HDR_NOTE'
AND JTFNTB.JTF_NOTE_ID = JTFNTL.JTF_NOTE_ID
AND JTFNTL.LANGUAGE = USERENV('LANG')
AND JTFNTL.SOURCE_LANG= USERENV('LANG')
AND JTFNTB.SOURCE_OBJECT_ID =OKCHDRB.ID
AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
AND nvl(okchdrb.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND HDRINT.BATCH_ID = l_batch_id ;
INSERT INTO OKS_NOTES_INTERFACE
(NOTES_INTERFACE_ID
,HEADER_INTERFACE_ID
,LINE_INTERFACE_ID
,NOTES
,NOTES_DETAIL
,NOTE_STATUS
,NOTE_TYPE
,ENTERED_BY
,ENTERED_DATE)
SELECT oks_notes_int_id_s.NEXTVAL NOTES_INTERFACE_ID
,NULL HEADER_INTERFACE_ID
,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
,JTFNTL.NOTES NOTES
,JTFNTL.NOTES_DETAIL NOTES_DETAIL
,JTFNTB.NOTE_STATUS NOTE_STATUS
,JTFNTB.NOTE_TYPE NOTE_TYPE
,JTFNTB.ENTERED_BY ENTERED_BY
,JTFNTB.ENTERED_DATE ENTERED_DATE
FROM JTF_NOTES_B JTFNTB
,JTF_NOTES_TL JTFNTL
,OKS_HEADERS_INTERFACE HDRINT
,OKC_K_HEADERS_ALL_B OKCHDRB
,OKC_K_LINES_B OKCLINB
,OKS_LINES_INTERFACE LININT
WHERE LININT.HEADER_INTERFACE_ID = HDRINT.HEADER_INTERFACE_ID
AND JTFNTB.SOURCE_OBJECT_CODE = 'OKS_COV_NOTE'
AND JTFNTB.JTF_NOTE_ID = JTFNTL.JTF_NOTE_ID
AND JTFNTL.LANGUAGE = USERENV('LANG')
AND JTFNTL.SOURCE_LANG= USERENV('LANG')
AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
AND JTFNTB.SOURCE_OBJECT_ID = OKCLINB.ID
AND LININT.LINE_NUMBER = OKCLINB.LINE_NUMBER
AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
AND nvl(okchdrb.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
AND HDRINT.BATCH_ID = l_batch_id ;
END Insert_Interface_Records;