1. Tables and Views for SCM
  2. CJM_PROGRAMS_CHECKBOOK_VL

CJM_PROGRAMS_CHECKBOOK_VL

Details

  • Schema: FUSION

  • Object owner: CJM

  • Object type: VIEW

Columns

Name

UTILIZATION_ID

REQUESTED_GL_DATE

ITEM_NUMBER

ITEM_DESCRIPTION

TP_ITEM_NUMBER

TP_ITEM_DESC

PROGRAM_CURRENCY_CODE

TRANSACTION_CURRENCY_CODE

EARNED_AMOUNT

IN_PROGRESS_PAID_PROG_AMOUNT

AVAILABLE_PROGRAM_AMT

INVOICE_DATE

INVOICE_NUMBER

INVOICE_SOURCE_CODE

INVOICE_LINE_NUMBER

ORDER_NUMBER

SHIPMENT_DATE

SHIPMENT_NUMBER

WAYBILL_NUMBER

BILL_OF_LADING_NUMBER

BILL_TO_CUSTOMER

UTILIZATION_TYPE

ADJUSTMENT_REASON_NAME

ACCTD_EARNED_AMOUNT

IN_PROGRESS_PAID_ACCTD_AMOUNT

AVAILABLE_ACCTD_AMT

PROGRAM_HEADER_ID

ADJUSTMENT_TYPE

ADJUSTMENT_DATE

MANUAL_ADJUSTMENT_NUMBER

BILL_TO_CUSTOMER_ACC_NUMBER

GL_DATE

ORDER_DATE

ORDER_SOURCE

CREATION_DATE

FULFILLMENT_LINE

CREATED_BY

LAST_UPDATED_BY

LAST_UPDATE_DATE

UTILIZATION_TYPE_NAME

ADJUSTMENT_TYPE_NAME

ADJUSTMENT_COMMENTS

Query

SQL_Statement

SELECT UTIL.UTILIZATION_ID ,

UTIL.REQUESTED_GL_DATE,

EGPSYSTEMITEMVL.ITEM_NUMBER,

EGPSYSTEMITEMVL.DESCRIPTION ITEM_DESCRIPTION,

EGPTPITEMS.TP_ITEM_NUMBER,

EGPTPITEMS.TP_ITEM_DESC,

UTIL.PROGRAM_CURRENCY_CODE,

UTIL.TRANSACTION_CURRENCY_CODE,

UTIL.PROGRAM_AMOUNT EARNED_AMOUNT,

(UTIL.PROGRAM_AMOUNT - UTIL.PROGRAM_AMOUNT_REMAINING) IN_PROGRESS_PAID_PROG_AMOUNT,

UTIL.PROGRAM_AMOUNT_REMAINING AVAILABLE_PROGRAM_AMT,

UTIL.INVOICE_DATE,

UTIL.INVOICE_NUMBER,

UTIL.INVOICE_SOURCE_CODE,

UTIL.INVOICE_LINE_NUMBER INVOICE_LINE_NUMBER,

DOOHEADERSALL.ORDER_NUMBER,

UTIL.SHIPMENT_DATE,

UTIL.SHIPMENT_NUMBER,

UTIL.WAYBILL_NUMBER,

UTIL.BILL_OF_LADING_NUMBER,

HZPARTIES.PARTY_NAME BILL_TO_CUSTOMER,

UTIL.UTILIZATION_TYPE,

ADJREASONS.ADJUSTMENT_REASON_NAME,

UTIL.ACCTD_AMOUNT ACCTD_EARNED_AMOUNT,

(UTIL.ACCTD_AMOUNT - UTIL.ACCTD_AMOUNT_REMAINING) IN_PROGRESS_PAID_ACCTD_AMOUNT,

UTIL.ACCTD_AMOUNT_REMAINING AVAILABLE_ACCTD_AMT,

UTIL.PROGRAM_HEADER_ID,

UTIL.ADJUSTMENT_TYPE,

UTIL.ADJUSTMENT_DATE,

MANUALADJ.MANUAL_ADJUSTMENT_NUMBER,

HZCUSTACCOUNT.ACCOUNT_NUMBER BILL_TO_CUSTOMER_ACC_NUMBER,

UTIL.GL_DATE,

DOOHEADERSALL.ORDERED_DATE ORDER_DATE,

DOOHEADERSALL.SOURCE_ORDER_SYSTEM ORDER_SOURCE,

UTIL.CREATION_DATE,

DOOLINESALL.DISPLAY_LINE_NUMBER || '-' || DOOFULFILLLINESALL.FULFILL_LINE_NUMBER FULFILLMENT_LINE,

UTIL.CREATED_BY,

UTIL.LAST_UPDATED_BY ,

UTIL.LAST_UPDATE_DATE,

FndLookup1.MEANING AS UTILIZATION_TYPE_NAME,

CjmAdjustmentTypesVL.adjustment_type_name AS ADJUSTMENT_TYPE_NAME

,MANUALADJ.ADJUSTMENT_COMMENTS

FROM CJM_PROGRAMS_UTILIZED_ALL_B UTIL,

HZ_PARTIES HZPARTIES,

HZ_CUST_ACCOUNTS HZCUSTACCOUNT,

EGP_SYSTEM_ITEMS_VL EGPSYSTEMITEMVL,

EGP_TRADING_PARTNER_ITEMS EGPTPITEMS,

DOO_HEADERS_ALL DOOHEADERSALL,

DOO_LINES_ALL DOOLINESALL,

DOO_FULFILL_LINES_ALL DOOFULFILLLINESALL,

CJM_ADJ_TYPE_REASONS_VL ADJREASONS,

cjm_manual_adjustments_vl MANUALADJ,

fnd_lookup_values_vl FndLookUp1,

CJM_ADJUSTMENT_TYPES_VL CjmAdjustmentTypesVL

WHERE HZPARTIES.PARTY_ID = HZCUSTACCOUNT.PARTY_ID

AND UTIL.BILL_TO_CUSTOMER_ID = HZCUSTACCOUNT.CUST_ACCOUNT_ID

AND UTIL.ITEM_ID = EGPSYSTEMITEMVL.INVENTORY_ITEM_ID

AND UTIL.TP_ITEM_ID = EGPTPITEMS.TP_ITEM_ID(+)

AND EGPSYSTEMITEMVL.ORGANIZATION_ID = CJM_UTILITY_PKG.GET_INV_ORG_FROM_BU_ID(UTIL.BU_ID)

AND DOOLINESALL.LINE_ID = DOOFULFILLLINESALL.LINE_ID

AND DOOLINESALL.HEADER_ID = DOOFULFILLLINESALL.HEADER_ID

AND UTIL.OBJECT_ID = DOOHEADERSALL.HEADER_ID

AND UTIL.OBJECT_LINE_ID = DOOFULFILLLINESALL.FULFILL_LINE_ID

AND UTIL.ADJUSTMENT_REASON_ID = ADJREASONS.ADJUSTMENT_REASON_ID(+)

AND UTIL.MANUAL_ADJUSTMENT_ID = MANUALADJ.MANUAL_ADJUSTMENT_ID(+)

AND UTIL.ACCRUAL_TYPE = 'SUPPLIER'

AND UTIL.gl_posted_flag = 'Y'

AND FndLookUp1.lookup_type = 'ORA_CJM_TRANSACTION_TYPE'

AND FndLookUp1.lookup_code=UTIL.UTILIZATION_TYPE

AND CjmAdjustmentTypesVL.ADJUSTMENT_TYPE_ID(+) = UTIL.adjustment_type_id