CM_CST_IBUBH_VW

(SQL View)
 Index Back

Costed IBU Depl Gain/Loss View

07/21/99 ebn CN#CM800-9.0 Used to process variances created through interunit shipments. 03/08/01 tw CN#R-ORICHA-HA99V added Lot ID and Serial ID. 07/30/01 tw CN#R-BKELLY-CQ58J added trans group '036'.

SELECT B.BUSINESS_UNIT , B.INV_ITEM_ID , B.CM_BOOK , B.DT_TIMESTAMP , B.SEQ_NBR , B.CM_DT_TIMESTAMP , B.CM_SEQ_NBR , B.CM_DT_TIMESTAMP_A , B.CM_SEQ_NBR_A , B.CM_SEQ_COST , B.COST_ELEMENT , C.STORAGE_AREA , C.SOURCE_BUS_UNIT , C.DESTIN_BU , D.QTY_BASE , %Round((B.CM_UNIT_COST - B.TRANSFER_COST - B.CM_UNIT_COST_VSUM), 4) , D.UNIT_MEASURE_STD , A.DISTRIB_TYPE , A.BUDGET_DT , C.TRANSACTION_GROUP , B.POSTED_FLAG , B.PROCESS_INSTANCE , C.OWNERSHIP_BU , C.IU_OWNERSHIP , E.CM_BU_DESTIN_GL , '302' , G.BUSINESS_UNIT_GL , G.BASE_CURRENCY , G.BASE_CURRENCY , G.APPL_JRNL_ID , A.TRANSACTION_DATE , E.CM_BU_SOURCE_GL , E.CM_BU_DESTIN_GL , A.CONSIGNED_FLAG , A.FROM_PRDN_ID , A.TO_PRDN_ID , A.ACCOUNT , A.ALTACCT , A.DEPTID , %subrec(CF16_AN_SBR, A) , %subrec(PC_CF1_N_SBR, A) , %subrec(CM_TRANS_IN_SBR, A) , A.SHIP_ID FROM PS_TRANSACTION_INV A , PS_CM_TRANVAR_COST B , PS_CM_INTRANSIT C , PS_CM_TRANVAR D , PS_CM_SHIPONBEHALF E , PS_BUS_UNIT_TBL_IN G WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.DT_TIMESTAMP = B.DT_TIMESTAMP AND A.SEQ_NBR = B.SEQ_NBR AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.INV_ITEM_ID = C.INV_ITEM_ID AND B.DT_TIMESTAMP = C.DT_TIMESTAMP AND B.SEQ_NBR = C.SEQ_NBR AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.INV_ITEM_ID = D.INV_ITEM_ID AND B.CM_BOOK = D.CM_BOOK AND B.DT_TIMESTAMP = D.DT_TIMESTAMP AND B.SEQ_NBR = D.SEQ_NBR AND B.CM_DT_TIMESTAMP = D.CM_DT_TIMESTAMP AND B.CM_SEQ_NBR = D.CM_SEQ_NBR AND B.CM_DT_TIMESTAMP_A = D.CM_DT_TIMESTAMP_A AND B.CM_SEQ_NBR_A = D.CM_SEQ_NBR_A AND C.BUSINESS_UNIT = E.BUSINESS_UNIT AND C.INV_ITEM_ID = E.INV_ITEM_ID AND C.DT_TIMESTAMP = E.DT_TIMESTAMP AND C.SEQ_NBR = E.SEQ_NBR AND A.BUSINESS_UNIT = G.BUSINESS_UNIT AND B.POSTED_FLAG = 'N' AND B.TRANSACTION_GROUP = '302' AND B.CM_COST_MODE = 'L' AND C.TRANSACTION_GROUP IN ('034')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: MST_ITM_PRPT_VW

3 CM_BOOK Character(10) VARCHAR2(10) NOT NULL Cost Book
4 DT_TIMESTAMP DateTime(26) TIMESTAMP NOT NULL Date Timestamp
5 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
6 CM_DT_TIMESTAMP DateTime(26) TIMESTAMP NOT NULL Receipt dttm.
7 CM_SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
8 CM_DT_TIMESTAMP_A DateTime(26) TIMESTAMP NOT NULL 07/99 GMG CN800-9.0: Original putaway dttm when bin-to-bin'd.
9 CM_SEQ_NBR_A Number(15,0) DECIMAL(15) NOT NULL 07/99 GMG CN800-9.0:
10 CM_SEQ_COST Number(15,0) DECIMAL(15) NOT NULL Cost Sequence Number. Incremented each time the actual cost is updated.
11 COST_ELEMENT Character(4) VARCHAR2(4) NOT NULL Cost Element
12 STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
13 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
14 DESTIN_BU Character(5) VARCHAR2(5) NOT NULL Destination Unit
15 QTY_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Base Quantity
16 CE_EXTENDED_COST Signed Number(32,15) DECIMAL(30,15) NOT NULL Extended Cost
17 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
18 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type

Prompt Table: CM_DISTR_TYPE

19 BUDGET_DT Date(10) DATE Budget Date
20 IU_TRANS_GROUP Character(3) VARCHAR2(3) NOT NULL InterUnit Transaction Group
21 POSTED_FLAG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a Receivables item has been posted. This field is automatically updated by the Receivables Update program.
N=Not Posted
X=Don't Post
Y=Posted
22 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
23 OWNERSHIP_BU Character(5) VARCHAR2(5) NOT NULL Business Unit Owning Inventory
24 IU_OWNERSHIP Character(1) VARCHAR2(1) NOT NULL Ownership Unit
D=Destination
S=Source
25 BUS_UNIT_GL_IBU Character(5) VARCHAR2(5) NOT NULL GL BU for other BU for IBU xfr
26 TRANSACTION_GROUP Character(3) VARCHAR2(3) NOT NULL 11/7/02 LLR - Added the new (600's) transactions groups for 8.8.
27 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
29 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
30 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template
31 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
32 SOURCE_GL_BU Character(5) VARCHAR2(5) NOT NULL Source GL Business Unit
33 DESTIN_GL_BU Character(5) VARCHAR2(5) NOT NULL Destination GL Business Unit
34 CONSIGNED_FLAG Character(1) VARCHAR2(1) NOT NULL Consigned Flag
35 FROM_PRDN_ID Character(10) VARCHAR2(10) NOT NULL From Production ID
36 TO_PRDN_ID Character(10) VARCHAR2(10) NOT NULL To Production ID
37 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
38 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
39 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPTID_BUGL_VW

40 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

41 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_BUGL_VW

42 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_BUGL_VW

43 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASSCF_BUGL_VW

44 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_BUGL_VW

45 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_BUGL_VW

46 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

47 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

48 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

49 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CF1_BUGL_VW

50 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CF2_BUGL_VW

51 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CF3_BUGL_VW

52 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

53 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: %EDIT_PROJECT

54 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: %EDIT_ACTIVITY

55 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

56 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

57 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

58 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

59 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID
60 COUNTING_EVENT_ID Number(15,0) DECIMAL(15) NOT NULL Counting Event ID
61 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No
62 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
63 FROM_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL From Production Area
64 FROM_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL From Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
65 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID
66 OP_SEQUENCE Number(4,0) SMALLINT NOT NULL Operation Sequence
67 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
68 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
69 PRDN_AREA_CODE Character(10) VARCHAR2(10) NOT NULL Production Area
70 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
71 PRODUCTION_TYPE Character(2) VARCHAR2(2) NOT NULL JNW 11/1/98 (CN#SF800-13) activated teardown xlat
PR=Production
RW=Rework
SV=Service
TD=Teardown
72 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
73 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
74 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
75 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
76 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
77 STAGED_DATE Date(10) DATE Staged Date
78 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
79 STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
80 STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
81 STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
82 STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
83 TO_PRDN_AREA Character(10) VARCHAR2(10) NOT NULL To Production Area
84 TO_PRDN_TYPE Character(2) VARCHAR2(2) NOT NULL To Production Type
PR=Production
RW=Rework
SV=Service
TD=Teardown
85 TO_STORAGE_AREA Character(5) VARCHAR2(5) NOT NULL Storage Area
86 TO_STOR_LEVEL_1 Character(4) VARCHAR2(4) NOT NULL Storage Level 1
87 TO_STOR_LEVEL_2 Character(4) VARCHAR2(4) NOT NULL Storage Level 2
88 TO_STOR_LEVEL_3 Character(4) VARCHAR2(4) NOT NULL Storage Level 3
89 TO_STOR_LEVEL_4 Character(4) VARCHAR2(4) NOT NULL Storage Level 4
90 TRANSACTION_SOURCE Character(2) VARCHAR2(2) NOT NULL Transaction Source
91 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
92 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID