VIEW: APPS.CN_ADJUSTMENTS_V

Source

View Text - Preformatted

SELECT CCH.commission_header_id, cch.org_id, RSD.employee_number direct_salesrep_number, RSD.name direct_salesrep_name, CCH.direct_salesrep_id, CCH.processed_period_id, CPSP.period_name processed_period, CCH.processed_date, CCH.rollup_date, CCH.transaction_amount, CCH.transaction_amount_orig, CCH.quantity, CCH.discount_percentage, CCH.margin_percentage, CCH.orig_currency_code, CCH.exchange_rate, CLS.meaning status_disp, CCH.status, CLT.meaning trx_type_disp, CCH.trx_type trx_type, CLR.meaning reason, CCH.reason_code reason_code, CCH.comments, CCH.trx_batch_id, CCH.created_by, CCH.creation_date, CCH.last_updated_by, CCH.last_update_login, CCH.last_update_date, CCH.attribute_category, CCH.attribute1, CCH.attribute2, CCH.attribute3, CCH.attribute4, CCH.attribute5, CCH.attribute6, CCH.attribute7, CCH.attribute8, CCH.attribute9, CCH.attribute10, CCH.attribute11, CCH.attribute12, CCH.attribute13, CCH.attribute14, CCH.attribute15, CCH.attribute16, CCH.attribute17, CCH.attribute18, CCH.attribute19, CCH.attribute20, CCH.attribute21, CCH.attribute22, CCH.attribute23, CCH.attribute24, CCH.attribute25, CCH.attribute26, CCH.attribute27, CCH.attribute28, CCH.attribute29, CCH.attribute30, CCH.attribute31, CCH.attribute32, CCH.attribute33, CCH.attribute34, CCH.attribute35, CCH.attribute36, CCH.attribute37, CCH.attribute38, CCH.attribute39, CCH.attribute40, CCH.attribute41, CCH.attribute42, CCH.attribute43, CCH.attribute44, CCH.attribute45, CCH.attribute46, CCH.attribute47, CCH.attribute48, CCH.attribute49, CCH.attribute50, CCH.attribute51, CCH.attribute52, CCH.attribute53, CCH.attribute54, CCH.attribute55, CCH.attribute56, CCH.attribute57, CCH.attribute58, CCH.attribute59, CCH.attribute60, CCH.attribute61, CCH.attribute62, CCH.attribute63, CCH.attribute64, CCH.attribute65, CCH.attribute66, CCH.attribute67, CCH.attribute68, CCH.attribute69, CCH.attribute70, CCH.attribute71, CCH.attribute72, CCH.attribute73, CCH.attribute74, CCH.attribute75, CCH.attribute76, CCH.attribute77, CCH.attribute78, CCH.attribute79, CCH.attribute80, CCH.attribute81, CCH.attribute82, CCH.attribute83, CCH.attribute84, CCH.attribute85, CCH.attribute86, CCH.attribute87, CCH.attribute88, CCH.attribute89, CCH.attribute90, CCH.attribute91, CCH.attribute92, CCH.attribute93, CCH.attribute94, CCH.attribute95, CCH.attribute96, CCH.attribute97, CCH.attribute98, CCH.attribute99, CCH.attribute100, CCH.quota_id, CQ.name quota_name, CCH.revenue_class_id, CNR.name revenue_class_name, CTB.trx_batch_name, CCH.source_trx_number, to_number('') trx_sales_line_id, to_number('') trx_line_id, to_number('') trx_id, CCH.comm_lines_api_id, CCH.source_doc_type, CCH.upside_amount, CCH.upside_quantity, CCH.uom_code, CCH.forecast_id, CCH.program_id, CCH.request_id, CCH.program_application_id, CCH.program_update_date, CCH.adj_comm_lines_api_id, CCH.invoice_number, CCH.invoice_date, CCH.order_number, CCH.booked_date order_date, CCH.line_number, CCH.customer_id, CCH.bill_to_address_id, CCH.ship_to_address_id, CCH.bill_to_contact_id, CCH.ship_to_contact_id, 'LOADED' load_status, CLRV.meaning revenue_type_disp, CCH.revenue_type, CCH.adjust_rollup_flag, CCH.adjust_date, CCH.adjusted_by, CLAD.meaning adjust_status_disp, CCH.adjust_status, CCH.adjust_comments, CCH.type, CCH.pre_processed_code, CCH.comp_group_id, CCH.srp_plan_assign_id, CCH.role_id, CCH.sales_channel, CCH.object_version_number FROM CN_PERIOD_STATUSES CPSP, CN_SALESREPS RSD, CN_COMMISSION_HEADERS CCH, CN_LOOKUPS CLT, CN_LOOKUPS CLR, CN_LOOKUPS CLS, CN_LOOKUPS CLRV, CN_LOOKUPS CLAD, CN_REVENUE_CLASSES CNR, CN_QUOTAS CQ, CN_TRX_BATCHES CTB WHERE CCH.direct_salesrep_id = RSD.salesrep_id AND CCH.org_id = CPSP.org_id AND CCH.processed_period_id = CPSP.period_id AND CCH.status = CLS.lookup_code(+) AND CLS.lookup_type (+)= 'TRX_STATUS' AND CCH.reason_code = CLR.lookup_code(+) AND CLR.lookup_type (+)= 'ADJUSTMENT_REASON' AND CCH.trx_type = CLT.lookup_code(+) AND CLT.lookup_type (+)= 'TRX TYPES' AND CCH.revenue_type = CLRV.lookup_code (+) AND CLRV.lookup_type (+) = 'REVENUE_TYPE' AND CCH.adjust_status = CLAD.lookup_code (+) AND CLAD.lookup_type (+) = 'ADJUST_STATUS' AND CCH.quota_id = CQ.quota_id(+) AND CCH.revenue_class_id = CNR.revenue_class_id(+) AND CCH.trx_batch_id = CTB.trx_batch_id(+) UNION ALL SELECT to_number('') commission_header_id, ccla.org_id, RSD.employee_number direct_salesrep_number, RSD.name direct_salesrep_name, CCLA.salesrep_id direct_salesrep_id, CCLA.processed_period_id, CPSP.period_name processed_period, CCLA.processed_date, CCLA.rollup_date, CCLA.acctd_transaction_amount, CCLA.transaction_amount, CCLA.quantity, CCLA.discount_percentage, CCLA.margin_percentage, CCLA.transaction_currency_code orig_currency_code, CCLA.exchange_rate, '' status_disp, '' status, CLT.meaning trx_type_disp, CLT.lookup_code trx_type, CLR.meaning reason, CLR.lookup_code reason_code, '' comments, to_number('') trx_batch_id, CCLA.created_by, CCLA.creation_date, CCLA.last_updated_by, CCLA.last_update_login, CCLA.last_update_date, '' attribute_category, CCLA.attribute1, CCLA.attribute2, CCLA.attribute3, CCLA.attribute4, CCLA.attribute5, CCLA.attribute6, CCLA.attribute7, CCLA.attribute8, CCLA.attribute9, CCLA.attribute10, CCLA.attribute11, CCLA.attribute12, CCLA.attribute13, CCLA.attribute14, CCLA.attribute15, CCLA.attribute16, CCLA.attribute17, CCLA.attribute18, CCLA.attribute19, CCLA.attribute20, CCLA.attribute21, CCLA.attribute22, CCLA.attribute23, CCLA.attribute24, CCLA.attribute25, CCLA.attribute26, CCLA.attribute27, CCLA.attribute28, CCLA.attribute29, CCLA.attribute30, CCLA.attribute31, CCLA.attribute32, CCLA.attribute33, CCLA.attribute34, CCLA.attribute35, CCLA.attribute36, CCLA.attribute37, CCLA.attribute38, CCLA.attribute39, CCLA.attribute40, CCLA.attribute41, CCLA.attribute42, CCLA.attribute43, CCLA.attribute44, CCLA.attribute45, CCLA.attribute46, CCLA.attribute47, CCLA.attribute48, CCLA.attribute49, CCLA.attribute50, CCLA.attribute51, CCLA.attribute52, CCLA.attribute53, CCLA.attribute54, CCLA.attribute55, CCLA.attribute56, CCLA.attribute57, CCLA.attribute58, CCLA.attribute59, CCLA.attribute60, CCLA.attribute61, CCLA.attribute62, CCLA.attribute63, CCLA.attribute64, CCLA.attribute65, CCLA.attribute66, CCLA.attribute67, CCLA.attribute68, CCLA.attribute69, CCLA.attribute70, CCLA.attribute71, CCLA.attribute72, CCLA.attribute73, CCLA.attribute74, CCLA.attribute75, CCLA.attribute76, CCLA.attribute77, CCLA.attribute78, CCLA.attribute79, CCLA.attribute80, CCLA.attribute81, CCLA.attribute82, CCLA.attribute83, CCLA.attribute84, CCLA.attribute85, CCLA.attribute86, CCLA.attribute87, CCLA.attribute88, CCLA.attribute89, CCLA.attribute90, CCLA.attribute91, CCLA.attribute92, CCLA.attribute93, CCLA.attribute94, CCLA.attribute95, CCLA.attribute96, CCLA.attribute97, CCLA.attribute98, CCLA.attribute99, CCLA.attribute100, to_number('') quota_id, '' quota_name, CCLA.revenue_class_id, CNR.name revenue_class_name, '' trx_batch_name, CCLA.source_trx_number, CCLA.trx_sales_line_id, CCLA.trx_line_id, CCLA.trx_id, CCLA.comm_lines_api_id, CCLA.source_doc_type, CCLA.upside_amount, CCLA.upside_quantity, CCLA.uom_code, CCLA.forecast_id, to_number('') program_id, to_number('') request_id, to_number('') program_application_id, to_date(null) program_update_date, CCLA.adj_comm_lines_api_id, CCLA.invoice_number, CCLA.invoice_date, CCLA.order_number, CCLA.booked_date order_date, CCLA.line_number, CCLA.customer_id, CCLA.bill_to_address_id, CCLA.ship_to_address_id, CCLA.bill_to_contact_id, CCLA.ship_to_contact_id, CCLA.load_status, CLRV.meaning revenue_type_disp, CCLA.revenue_type, CCLA.adjust_rollup_flag, CCLA.adjust_date, CCLA.adjusted_by, CLAD.meaning adjust_status_disp, CCLA.adjust_status, CCLA.adjust_comments, CCLA.type, CCLA.pre_processed_code, CCLA.comp_group_id, CCLA.srp_plan_assign_id, CCLA.role_id, CCLA.sales_channel, CCLA.object_version_number FROM CN_COMM_LINES_API CCLA, CN_PERIOD_STATUSES CPSP, CN_SALESREPS RSD, CN_REVENUE_CLASSES CNR, CN_LOOKUPS CLT, CN_LOOKUPS CLRV, CN_LOOKUPS CLAD, CN_LOOKUPS CLR WHERE RSD.salesrep_id = CCLA.salesrep_id AND CCLA.org_id = CPSP.org_id AND CCLA.processed_period_id = CPSP.period_id AND CCLA.revenue_class_id = CNR.revenue_class_id(+) AND CCLA.reason_code = CLR.lookup_code(+) AND CLR.lookup_type (+)= 'ADJUSTMENT_REASON' AND CCLA.trx_type = CLT.lookup_code AND CLT.lookup_type = 'TRX TYPES' AND CCLA.revenue_type = CLRV.lookup_code(+) AND CLRV.lookup_type (+)= 'REVENUE_TYPE' AND CCLA.adjust_status = CLAD.lookup_code(+) AND CLAD.lookup_type (+)= 'ADJUST_STATUS' AND nvl(CCLA.load_status,'X') <> 'LOADED'

View Text - HTML Formatted

SELECT CCH.COMMISSION_HEADER_ID
, CCH.ORG_ID
, RSD.EMPLOYEE_NUMBER DIRECT_SALESREP_NUMBER
, RSD.NAME DIRECT_SALESREP_NAME
, CCH.DIRECT_SALESREP_ID
, CCH.PROCESSED_PERIOD_ID
, CPSP.PERIOD_NAME PROCESSED_PERIOD
, CCH.PROCESSED_DATE
, CCH.ROLLUP_DATE
, CCH.TRANSACTION_AMOUNT
, CCH.TRANSACTION_AMOUNT_ORIG
, CCH.QUANTITY
, CCH.DISCOUNT_PERCENTAGE
, CCH.MARGIN_PERCENTAGE
, CCH.ORIG_CURRENCY_CODE
, CCH.EXCHANGE_RATE
, CLS.MEANING STATUS_DISP
, CCH.STATUS
, CLT.MEANING TRX_TYPE_DISP
, CCH.TRX_TYPE TRX_TYPE
, CLR.MEANING REASON
, CCH.REASON_CODE REASON_CODE
, CCH.COMMENTS
, CCH.TRX_BATCH_ID
, CCH.CREATED_BY
, CCH.CREATION_DATE
, CCH.LAST_UPDATED_BY
, CCH.LAST_UPDATE_LOGIN
, CCH.LAST_UPDATE_DATE
, CCH.ATTRIBUTE_CATEGORY
, CCH.ATTRIBUTE1
, CCH.ATTRIBUTE2
, CCH.ATTRIBUTE3
, CCH.ATTRIBUTE4
, CCH.ATTRIBUTE5
, CCH.ATTRIBUTE6
, CCH.ATTRIBUTE7
, CCH.ATTRIBUTE8
, CCH.ATTRIBUTE9
, CCH.ATTRIBUTE10
, CCH.ATTRIBUTE11
, CCH.ATTRIBUTE12
, CCH.ATTRIBUTE13
, CCH.ATTRIBUTE14
, CCH.ATTRIBUTE15
, CCH.ATTRIBUTE16
, CCH.ATTRIBUTE17
, CCH.ATTRIBUTE18
, CCH.ATTRIBUTE19
, CCH.ATTRIBUTE20
, CCH.ATTRIBUTE21
, CCH.ATTRIBUTE22
, CCH.ATTRIBUTE23
, CCH.ATTRIBUTE24
, CCH.ATTRIBUTE25
, CCH.ATTRIBUTE26
, CCH.ATTRIBUTE27
, CCH.ATTRIBUTE28
, CCH.ATTRIBUTE29
, CCH.ATTRIBUTE30
, CCH.ATTRIBUTE31
, CCH.ATTRIBUTE32
, CCH.ATTRIBUTE33
, CCH.ATTRIBUTE34
, CCH.ATTRIBUTE35
, CCH.ATTRIBUTE36
, CCH.ATTRIBUTE37
, CCH.ATTRIBUTE38
, CCH.ATTRIBUTE39
, CCH.ATTRIBUTE40
, CCH.ATTRIBUTE41
, CCH.ATTRIBUTE42
, CCH.ATTRIBUTE43
, CCH.ATTRIBUTE44
, CCH.ATTRIBUTE45
, CCH.ATTRIBUTE46
, CCH.ATTRIBUTE47
, CCH.ATTRIBUTE48
, CCH.ATTRIBUTE49
, CCH.ATTRIBUTE50
, CCH.ATTRIBUTE51
, CCH.ATTRIBUTE52
, CCH.ATTRIBUTE53
, CCH.ATTRIBUTE54
, CCH.ATTRIBUTE55
, CCH.ATTRIBUTE56
, CCH.ATTRIBUTE57
, CCH.ATTRIBUTE58
, CCH.ATTRIBUTE59
, CCH.ATTRIBUTE60
, CCH.ATTRIBUTE61
, CCH.ATTRIBUTE62
, CCH.ATTRIBUTE63
, CCH.ATTRIBUTE64
, CCH.ATTRIBUTE65
, CCH.ATTRIBUTE66
, CCH.ATTRIBUTE67
, CCH.ATTRIBUTE68
, CCH.ATTRIBUTE69
, CCH.ATTRIBUTE70
, CCH.ATTRIBUTE71
, CCH.ATTRIBUTE72
, CCH.ATTRIBUTE73
, CCH.ATTRIBUTE74
, CCH.ATTRIBUTE75
, CCH.ATTRIBUTE76
, CCH.ATTRIBUTE77
, CCH.ATTRIBUTE78
, CCH.ATTRIBUTE79
, CCH.ATTRIBUTE80
, CCH.ATTRIBUTE81
, CCH.ATTRIBUTE82
, CCH.ATTRIBUTE83
, CCH.ATTRIBUTE84
, CCH.ATTRIBUTE85
, CCH.ATTRIBUTE86
, CCH.ATTRIBUTE87
, CCH.ATTRIBUTE88
, CCH.ATTRIBUTE89
, CCH.ATTRIBUTE90
, CCH.ATTRIBUTE91
, CCH.ATTRIBUTE92
, CCH.ATTRIBUTE93
, CCH.ATTRIBUTE94
, CCH.ATTRIBUTE95
, CCH.ATTRIBUTE96
, CCH.ATTRIBUTE97
, CCH.ATTRIBUTE98
, CCH.ATTRIBUTE99
, CCH.ATTRIBUTE100
, CCH.QUOTA_ID
, CQ.NAME QUOTA_NAME
, CCH.REVENUE_CLASS_ID
, CNR.NAME REVENUE_CLASS_NAME
, CTB.TRX_BATCH_NAME
, CCH.SOURCE_TRX_NUMBER
, TO_NUMBER('') TRX_SALES_LINE_ID
, TO_NUMBER('') TRX_LINE_ID
, TO_NUMBER('') TRX_ID
, CCH.COMM_LINES_API_ID
, CCH.SOURCE_DOC_TYPE
, CCH.UPSIDE_AMOUNT
, CCH.UPSIDE_QUANTITY
, CCH.UOM_CODE
, CCH.FORECAST_ID
, CCH.PROGRAM_ID
, CCH.REQUEST_ID
, CCH.PROGRAM_APPLICATION_ID
, CCH.PROGRAM_UPDATE_DATE
, CCH.ADJ_COMM_LINES_API_ID
, CCH.INVOICE_NUMBER
, CCH.INVOICE_DATE
, CCH.ORDER_NUMBER
, CCH.BOOKED_DATE ORDER_DATE
, CCH.LINE_NUMBER
, CCH.CUSTOMER_ID
, CCH.BILL_TO_ADDRESS_ID
, CCH.SHIP_TO_ADDRESS_ID
, CCH.BILL_TO_CONTACT_ID
, CCH.SHIP_TO_CONTACT_ID
, 'LOADED' LOAD_STATUS
, CLRV.MEANING REVENUE_TYPE_DISP
, CCH.REVENUE_TYPE
, CCH.ADJUST_ROLLUP_FLAG
, CCH.ADJUST_DATE
, CCH.ADJUSTED_BY
, CLAD.MEANING ADJUST_STATUS_DISP
, CCH.ADJUST_STATUS
, CCH.ADJUST_COMMENTS
, CCH.TYPE
, CCH.PRE_PROCESSED_CODE
, CCH.COMP_GROUP_ID
, CCH.SRP_PLAN_ASSIGN_ID
, CCH.ROLE_ID
, CCH.SALES_CHANNEL
, CCH.OBJECT_VERSION_NUMBER
FROM CN_PERIOD_STATUSES CPSP
, CN_SALESREPS RSD
, CN_COMMISSION_HEADERS CCH
, CN_LOOKUPS CLT
, CN_LOOKUPS CLR
, CN_LOOKUPS CLS
, CN_LOOKUPS CLRV
, CN_LOOKUPS CLAD
, CN_REVENUE_CLASSES CNR
, CN_QUOTAS CQ
, CN_TRX_BATCHES CTB
WHERE CCH.DIRECT_SALESREP_ID = RSD.SALESREP_ID
AND CCH.ORG_ID = CPSP.ORG_ID
AND CCH.PROCESSED_PERIOD_ID = CPSP.PERIOD_ID
AND CCH.STATUS = CLS.LOOKUP_CODE(+)
AND CLS.LOOKUP_TYPE (+)= 'TRX_STATUS'
AND CCH.REASON_CODE = CLR.LOOKUP_CODE(+)
AND CLR.LOOKUP_TYPE (+)= 'ADJUSTMENT_REASON'
AND CCH.TRX_TYPE = CLT.LOOKUP_CODE(+)
AND CLT.LOOKUP_TYPE (+)= 'TRX TYPES'
AND CCH.REVENUE_TYPE = CLRV.LOOKUP_CODE (+)
AND CLRV.LOOKUP_TYPE (+) = 'REVENUE_TYPE'
AND CCH.ADJUST_STATUS = CLAD.LOOKUP_CODE (+)
AND CLAD.LOOKUP_TYPE (+) = 'ADJUST_STATUS'
AND CCH.QUOTA_ID = CQ.QUOTA_ID(+)
AND CCH.REVENUE_CLASS_ID = CNR.REVENUE_CLASS_ID(+)
AND CCH.TRX_BATCH_ID = CTB.TRX_BATCH_ID(+) UNION ALL SELECT TO_NUMBER('') COMMISSION_HEADER_ID
, CCLA.ORG_ID
, RSD.EMPLOYEE_NUMBER DIRECT_SALESREP_NUMBER
, RSD.NAME DIRECT_SALESREP_NAME
, CCLA.SALESREP_ID DIRECT_SALESREP_ID
, CCLA.PROCESSED_PERIOD_ID
, CPSP.PERIOD_NAME PROCESSED_PERIOD
, CCLA.PROCESSED_DATE
, CCLA.ROLLUP_DATE
, CCLA.ACCTD_TRANSACTION_AMOUNT
, CCLA.TRANSACTION_AMOUNT
, CCLA.QUANTITY
, CCLA.DISCOUNT_PERCENTAGE
, CCLA.MARGIN_PERCENTAGE
, CCLA.TRANSACTION_CURRENCY_CODE ORIG_CURRENCY_CODE
, CCLA.EXCHANGE_RATE
, '' STATUS_DISP
, '' STATUS
, CLT.MEANING TRX_TYPE_DISP
, CLT.LOOKUP_CODE TRX_TYPE
, CLR.MEANING REASON
, CLR.LOOKUP_CODE REASON_CODE
, '' COMMENTS
, TO_NUMBER('') TRX_BATCH_ID
, CCLA.CREATED_BY
, CCLA.CREATION_DATE
, CCLA.LAST_UPDATED_BY
, CCLA.LAST_UPDATE_LOGIN
, CCLA.LAST_UPDATE_DATE
, '' ATTRIBUTE_CATEGORY
, CCLA.ATTRIBUTE1
, CCLA.ATTRIBUTE2
, CCLA.ATTRIBUTE3
, CCLA.ATTRIBUTE4
, CCLA.ATTRIBUTE5
, CCLA.ATTRIBUTE6
, CCLA.ATTRIBUTE7
, CCLA.ATTRIBUTE8
, CCLA.ATTRIBUTE9
, CCLA.ATTRIBUTE10
, CCLA.ATTRIBUTE11
, CCLA.ATTRIBUTE12
, CCLA.ATTRIBUTE13
, CCLA.ATTRIBUTE14
, CCLA.ATTRIBUTE15
, CCLA.ATTRIBUTE16
, CCLA.ATTRIBUTE17
, CCLA.ATTRIBUTE18
, CCLA.ATTRIBUTE19
, CCLA.ATTRIBUTE20
, CCLA.ATTRIBUTE21
, CCLA.ATTRIBUTE22
, CCLA.ATTRIBUTE23
, CCLA.ATTRIBUTE24
, CCLA.ATTRIBUTE25
, CCLA.ATTRIBUTE26
, CCLA.ATTRIBUTE27
, CCLA.ATTRIBUTE28
, CCLA.ATTRIBUTE29
, CCLA.ATTRIBUTE30
, CCLA.ATTRIBUTE31
, CCLA.ATTRIBUTE32
, CCLA.ATTRIBUTE33
, CCLA.ATTRIBUTE34
, CCLA.ATTRIBUTE35
, CCLA.ATTRIBUTE36
, CCLA.ATTRIBUTE37
, CCLA.ATTRIBUTE38
, CCLA.ATTRIBUTE39
, CCLA.ATTRIBUTE40
, CCLA.ATTRIBUTE41
, CCLA.ATTRIBUTE42
, CCLA.ATTRIBUTE43
, CCLA.ATTRIBUTE44
, CCLA.ATTRIBUTE45
, CCLA.ATTRIBUTE46
, CCLA.ATTRIBUTE47
, CCLA.ATTRIBUTE48
, CCLA.ATTRIBUTE49
, CCLA.ATTRIBUTE50
, CCLA.ATTRIBUTE51
, CCLA.ATTRIBUTE52
, CCLA.ATTRIBUTE53
, CCLA.ATTRIBUTE54
, CCLA.ATTRIBUTE55
, CCLA.ATTRIBUTE56
, CCLA.ATTRIBUTE57
, CCLA.ATTRIBUTE58
, CCLA.ATTRIBUTE59
, CCLA.ATTRIBUTE60
, CCLA.ATTRIBUTE61
, CCLA.ATTRIBUTE62
, CCLA.ATTRIBUTE63
, CCLA.ATTRIBUTE64
, CCLA.ATTRIBUTE65
, CCLA.ATTRIBUTE66
, CCLA.ATTRIBUTE67
, CCLA.ATTRIBUTE68
, CCLA.ATTRIBUTE69
, CCLA.ATTRIBUTE70
, CCLA.ATTRIBUTE71
, CCLA.ATTRIBUTE72
, CCLA.ATTRIBUTE73
, CCLA.ATTRIBUTE74
, CCLA.ATTRIBUTE75
, CCLA.ATTRIBUTE76
, CCLA.ATTRIBUTE77
, CCLA.ATTRIBUTE78
, CCLA.ATTRIBUTE79
, CCLA.ATTRIBUTE80
, CCLA.ATTRIBUTE81
, CCLA.ATTRIBUTE82
, CCLA.ATTRIBUTE83
, CCLA.ATTRIBUTE84
, CCLA.ATTRIBUTE85
, CCLA.ATTRIBUTE86
, CCLA.ATTRIBUTE87
, CCLA.ATTRIBUTE88
, CCLA.ATTRIBUTE89
, CCLA.ATTRIBUTE90
, CCLA.ATTRIBUTE91
, CCLA.ATTRIBUTE92
, CCLA.ATTRIBUTE93
, CCLA.ATTRIBUTE94
, CCLA.ATTRIBUTE95
, CCLA.ATTRIBUTE96
, CCLA.ATTRIBUTE97
, CCLA.ATTRIBUTE98
, CCLA.ATTRIBUTE99
, CCLA.ATTRIBUTE100
, TO_NUMBER('') QUOTA_ID
, '' QUOTA_NAME
, CCLA.REVENUE_CLASS_ID
, CNR.NAME REVENUE_CLASS_NAME
, '' TRX_BATCH_NAME
, CCLA.SOURCE_TRX_NUMBER
, CCLA.TRX_SALES_LINE_ID
, CCLA.TRX_LINE_ID
, CCLA.TRX_ID
, CCLA.COMM_LINES_API_ID
, CCLA.SOURCE_DOC_TYPE
, CCLA.UPSIDE_AMOUNT
, CCLA.UPSIDE_QUANTITY
, CCLA.UOM_CODE
, CCLA.FORECAST_ID
, TO_NUMBER('') PROGRAM_ID
, TO_NUMBER('') REQUEST_ID
, TO_NUMBER('') PROGRAM_APPLICATION_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, CCLA.ADJ_COMM_LINES_API_ID
, CCLA.INVOICE_NUMBER
, CCLA.INVOICE_DATE
, CCLA.ORDER_NUMBER
, CCLA.BOOKED_DATE ORDER_DATE
, CCLA.LINE_NUMBER
, CCLA.CUSTOMER_ID
, CCLA.BILL_TO_ADDRESS_ID
, CCLA.SHIP_TO_ADDRESS_ID
, CCLA.BILL_TO_CONTACT_ID
, CCLA.SHIP_TO_CONTACT_ID
, CCLA.LOAD_STATUS
, CLRV.MEANING REVENUE_TYPE_DISP
, CCLA.REVENUE_TYPE
, CCLA.ADJUST_ROLLUP_FLAG
, CCLA.ADJUST_DATE
, CCLA.ADJUSTED_BY
, CLAD.MEANING ADJUST_STATUS_DISP
, CCLA.ADJUST_STATUS
, CCLA.ADJUST_COMMENTS
, CCLA.TYPE
, CCLA.PRE_PROCESSED_CODE
, CCLA.COMP_GROUP_ID
, CCLA.SRP_PLAN_ASSIGN_ID
, CCLA.ROLE_ID
, CCLA.SALES_CHANNEL
, CCLA.OBJECT_VERSION_NUMBER
FROM CN_COMM_LINES_API CCLA
, CN_PERIOD_STATUSES CPSP
, CN_SALESREPS RSD
, CN_REVENUE_CLASSES CNR
, CN_LOOKUPS CLT
, CN_LOOKUPS CLRV
, CN_LOOKUPS CLAD
, CN_LOOKUPS CLR
WHERE RSD.SALESREP_ID = CCLA.SALESREP_ID
AND CCLA.ORG_ID = CPSP.ORG_ID
AND CCLA.PROCESSED_PERIOD_ID = CPSP.PERIOD_ID
AND CCLA.REVENUE_CLASS_ID = CNR.REVENUE_CLASS_ID(+)
AND CCLA.REASON_CODE = CLR.LOOKUP_CODE(+)
AND CLR.LOOKUP_TYPE (+)= 'ADJUSTMENT_REASON'
AND CCLA.TRX_TYPE = CLT.LOOKUP_CODE
AND CLT.LOOKUP_TYPE = 'TRX TYPES'
AND CCLA.REVENUE_TYPE = CLRV.LOOKUP_CODE(+)
AND CLRV.LOOKUP_TYPE (+)= 'REVENUE_TYPE'
AND CCLA.ADJUST_STATUS = CLAD.LOOKUP_CODE(+)
AND CLAD.LOOKUP_TYPE (+)= 'ADJUST_STATUS'
AND NVL(CCLA.LOAD_STATUS
, 'X') <> 'LOADED'