How to Troubleshoot issue in P4021 (Supply & Demand)

Purpose of Document

Question 1. What are the related Business Functions running P4021?

Answer 1. There are 2 BSFNs which may affect the results when viewing the P4021 inquiry:

B3400310 - LoadSupplyAndDemandData (F4021W Calculate Supply and Demand)

1st Level business function (which called from P4021 directly through event rule)
Reads Transactional Tables and calling B3401750 which is to ADD and GET cache created

B3401750 - CacheProcessSupplyDemandData (Cache, Process Supply Demand Data)

2nd Level BSFN (called by B3400310) to CACHE_SD_ADD
1st Level BSFN (called from event rule) to CACHE_SD_GET
All the Supply and Demand is handled through cache

Question 2. What is the sequence of Cache Action?

Answer 2. In running application P4021 it handles Supply Demand Cache as below:

1. CACHE_SD_ADD (cCacheActionCode = 2)
2. CACHE_SD_GET_NEXT (cCacheActionCode = 5)
3. CACHE_SD_UPDATE (cCacheActionCode = 3)
4. CACHE_SD_TERMINATE (cCacheActionCode = 6)

NOTE:

Code for cache action is vary aBSFN by BSFN (for instance, most of BSFN define '6' as FetchNext etc.)
cCacheActionCode definition is different from other BSFN so refer B3401750.h

#define CACHE_SD_GET _J('1')
#define CACHE_SD_ADD _J('2')
#define CACHE_SD_UPDATE _J('3')
#define CACHE_SD_DELETE _J('4')
#define CACHE_SD_GET_NEXT _J('5')
#define CACHE_SD_TERMINATE _J('6')
#define CACHE_SD_CLOSE_CURSOR _J('7')
#define CACHE_SD_DEBUG _J('D')

Question 3. What is the sequence of computation in P4021?

Answer 3. Simplified sequence of the computation is:

1. EVENT: Dialog Is Initialized (Initialize Supply and Demand related cache)
2. EVENT: Find button clicked (CreateCache and UpdateCache)
3. EVENT: Get Custom Grid Row (Read/ write cache into Grid)
4. EVENT (Push Button) AfterCacheFetch

1. EVENT: Dialog Is Initialized (Initialize Supply and Demand related cache)

1. LoadSupplyAndDemandData (cMode = 1 to initialized it): All the flags will be determined at this level in conjunction with processing option parameters and data

2. EVENT: Find button clicked (CreateCache and UpdateCache)

1. LoadSupplyAndDemandData (cMode = 2 to create cache)

2. CacheProcessSupplyDemandData

Repeat this routine so long as data does exist from Supply and Demand tables
When LoadSupplyAndDemandData is calling CacheProcessSupplyDemandData it defines cCacheActionCode '2'
Since calling cMode of LoadSupplyAndDemandData is 2, read all records from the Location(F41021) and Lot Master(F4108) tables for the given Item and Branch.
Write the returned data to the Cache

1. F4021WCalculateQuantityAvailable

2. CacheProcessSupplyDemandData

Get Cache created from above and update it with On Hand quantity
cCacheActionCode 5 (GET_NEXT) then 3 (UPDATE)

3. EVENT: Get Custom Grid Row (Read/ write cache into Grid): Repeat this as many as Cache created from Find button

1. CacheProcessSupplyDemandData

So long as return idSDCacheCursor_GENLNG is not empty then Press Button(FC AfterCacheFetch)

4. EVENT (Push Button) AfterCacheFetch

Compute "Available-To-Promise (ATP)"
Insert Grid Buffer Row

The questions below are related with BSFN B3400310 - LoadSupplyAndDemandData (F4021W Calculate Supply and Demand).

Question 4. What flags are initialized when the B3400310 gets called with cMode = 1?

Answer 4. There are two different types of flags, one is from the parameters of processing option, and the other is computed based on transactional tables.

First, the Flags below are the IN parameters from Processing Options of P4021

VA frm_cPlanFlag_EV01 = PO cPOPlannedOrderFlag
VA frm_cLotExpireFlag = PO cPOLotExpFlag
PO cPOStdPotencyFlag -> BF cStandardPotencyFlag
PO cPOBulkStkItemFlag -> BF cBulkStockingType
PO cPOTransitFlag -> BF cTransitFlag
PO cPOInspectionFlag -> BF cInspectFlag
PO cPORecRtgSumFlag -> BF cReceiptRoutingSummary
PO cPOLocSummaryflag -> BF cLocationSummaryFlag
PO cPastDueRatesFlag -> BF cPastDueRatesFlag
PO cPOOther1Flag -> BF cOther1Flag
PO cPOOther2Flag -> BF cOther2Flag
PO cEnableMPM -> BF cEnableMPM (PO item #5. Enable Engineer to Order(ETO))

Next are the flags which are returned through B3400310 - LoadSupplyAndDemandData (with cMode = '1')

VA frm_cForecastFlag_EV09 <- BF cForecastFlag
VA frm_cPurchaseFlag_EV01 <- BF cPurchaseFlag
VA frm_cRecRtgFlag_EV01 <- BF cRecRtgFlag
VA frm_cMfgFlag_EV01 <- BF cMfgFlag
VA frm_cRateFlag_EV01 <- BF cRateFlag

Below flags are set internally (NOTE: each flag is self-explained)

a. If cPlannedOrderFlag (=PO cPOPlannedOrderFlag) is "1", then set "1" for below flags

. cTransFlag
. cPegOrderFlag
. cCoByPegFlag
b. If PO szForecastType1 through 5 is not Blank, then set "1" for

. cForecastFlag
c. If PO szScheduleType is not Blank, then set "1" for

. cRateFlag
d. Below are the other flags which determines which file to read:

. cPurchaseFlag
. cWorkOrderFlag
. cCrossDockingFlag
. cCoByOrderFlag
. cTransFlag
. cPartsFlag
. cRecRtgFlag
. cMfgFlag

: Based on the Flags defined/initialized here will determine which tables to access to fetch supply and demand data. For this the jdedebug.log will contain each flag with a value 0 (or Blank) and 1 (to process transactional data)

In mode 2 it reads Supply and Demand data and than creates cache.

cMode	Purpose
1	Attempt to open all files used for Supply and Demand Inquiry. Set global flags based on the success of the file opens.
2	Call the routines to read the supply and demand data from the various tables and write it to the Cache
3	Call the routines to close the data tables

Question 5. What now is the sequence of reading and tables related?

Answer 5. It calls the internal BSFN in B3400310 as explained below:

	Internal BSFN	Usage	Table(s)	Description
1	I3400310_GetAndSelectLotQuantity	All the data from F41021	F41021 and F4108	Location Master
2	I3400310_GetandSelectProjectInventory	when mpm is ON	F410211	Inventory Commitment Table
3	I3400310_GetAndSelectConfiguredComponentQuantity	Not for Cross Docking	F3215	Configurator Component Table
4	I3400310_GetAndSelectPurchaseOrders	cPurchaseFlag is ON	F4311	Purchase Order Detail File
5	I3400310_GetAndSelectWorkOrders	cMfgFlag is ON	F4801 and/or F4801T	Work Order Master File/Work Order Master Tag File
6	I3400310_GetAndSelectCoByOrders	cMfgFlag is ON	F3111	Work Order Parts List
7	I3400310_GetAndSelectPartsList	cMfgFlag is ON and no cCrossDockingFlag	F3111	Work Order Parts List
8	I3400310_GetAndSelectPeggingOrders	cPlannedOrderFlag is ON	F3412	MPS/MRP/DRP Lower Level Requirements Table or Pegging Records
9	I3400310_GetAndSelectCoByPegging	cPlannedOrderFlag is ON and F3002 contains data per (void *) &dsF3002Key8	F3412/F3002	MPS/MRP/DRP Pegging Records/Bill of Material Master File
10	I3400310_GetAndSelectCoByPegging	cPlannedOrderFlag is ON and F3002 does not contains data per (void *) &dsF3002Key8 then search F3002 again after setting CObY='C'	F3412	MPS/MRP/DRP Pegging Records Table
11	I3400310_GetAndSelectPlannedOrders	cPlannedOrderFlag is ON and cCrossDockingFlag is OFF	F3411	MPS/MRP/DRP Message File
12	I3400310_GetAndSelectForecast	cCrossDockingFlag is OFF and cForecastFlag is ON	F3460	Forecast File
13	I3400310_GetAndSelectSalesOrders		F4211	Sales Order Detail File
14	I3400310_GetAndSelectRates	cCrossDockingFlag is OFF and cRateFlag is ON	F4801	Work Order Master File

NOTE: Based on the processing options and data used, some of the Internal BSFN may not be called (based on business requirements)

Question 6. Is there example of a query?

Answer 6. Below are examples of each query when all the flags are ON,

	Query	Remark
1	SQL> SELECT * FROM PRODDTA.F41021 WHERE ( LIITM = 1234 AND LIMCU = ' M30' ) AND ( LILOTS IN ( ' ',' ',' ',' ',' ',' ' ) ) ORDER BY LIITM ASC,LIMCU ASC,LILOCN ASC,LILOTN ASC
2	SQL> SELECT * FROM PRODDTA.F410211 WHERE ( PCMCU = ' M30' AND PCITM = 1234 ) ORDER BY PCMCU ASC,PCITM ASC,PCLOCN ASC,PCLOTN ASC
3	SQL> SELECT * FROM PRODDTA.F3215 WHERE ( CCMCU = ' M30' AND CCITM = 1234 AND CCSY = '42' AND CCWADL = '0' AND CCCFGID > 0.000000 ) ORDER BY CCCFGID ASC,CCCFGCID ASC,CCKIT ASC,CCCFGSID ASC
4	SQL> SELECT * FROM PRODDTA.F4311 WHERE ( PDITM = 1234 AND PDMCU = ' M30' ) AND ( PDLTTR <> '999' OR PDNXTR <> ' ' ) ORDER BY PDITM ASC,PDMCU ASC,PDPDDJ ASC
5	SQL> SELECT * FROM PRODDTA.F4801 WHERE ( WAITM = 1234 AND WAMMCU = ' M30' ) AND ( WASRST NOT IN ( '95','91','92','96','97','98','99' ) ) ORDER BY WAITM ASC,WAMMCU ASC,WADRQJ ASC
6	SQL> SELECT * FROM PRODDTA.F3111 WHERE ( WMCPIT = 1234 AND WMCMCU = ' M30' AND WMCOBY IN ( 'B' , 'C')) ORDER BY WMCPIT ASC,WMCMCU ASC,WMDOCO ASC,WMUORG DESC
7	SQL> SELECT * FROM PRODDTA.F3111 T0,PRODDTA.F4801 T1,PRODDTA.F4801T T2 WHERE ( T0.WMCPIT = 1234 AND T0.WMCMCU = ' M30' AND T1.WASRST NOT IN ( '95','91','92','96','97','98','99' ) AND T0.WMCOBY = ' ' ) AND ( T0.WMDOCO=T1.WADOCO AND T0.WMDCTO=T1.WADCTO AND T0.WMDOCO=T2.WADOCO ) ORDER BY T0.WMUKID ASC
8	SQL> SELECT * FROM PRODDTA.F3412 WHERE ( MWITM = 1234 AND MWMCU = ' M30' ) ORDER BY MWITM ASC,MWMCU ASC,MWDRQJ ASC
9	SQL> SELECT * FROM PRODDTA.F3002 WHERE ( IXCOBY = 'B' AND IXKIT = 1234 AND IXMMCU = ' M30' )
10	SQL> SELECT * FROM PRODDTA.F3002 WHERE ( IXCOBY = 'C' AND IXKIT = 1234 AND IXMMCU = ' M30' )
11	SELECT * FROM PRODDTA.F3411 WHERE ( MMITM = 1234 AND MMMCU = ' M30' ) ORDER BY MMITM ASC,MMMCU ASC,MMDRQJ ASC SQL> SELECT * FROM PRODDTA.F3411 WHERE ( MMMMCU = ' M30' AND MMITM = 1234 ) ORDER BY MMMMCU ASC,MMITM ASC,MMDRQJ ASC
12	SQL> SELECT * FROM PRODDTA.F3460 WHERE ( MFITM = 1234 AND MFMCU = ' M30' ) ORDER BY MFITM ASC,MFMCU ASC,MFDRQJ ASC
13	SQL> SELECT * FROM PRODDTA.F4211 WHERE ( SDITM = 1234 AND SDMCU = ' M30' ) AND ( SDNXTR NOT IN ( '999','980' ) ) ORDER BY SDITM ASC,SDMCU ASC,SDPDDJ ASC,SDPDTT ASC
14	SQL> SELECT * FROM PRODDTA.F4801 WHERE ( WAITM = 1234 AND WAMMCU = ' M30' ) AND ( WADCTO = 'SC' ) ORDER BY WAITM ASC,WAMMCU ASC,WADRQJ ASC

Note:

Above query is just example of WHERE and ORDER BY
In real environment, it returns column selectively not ALL '*"
If above WHERE returns more than 2 records, the sequence of return value will be same with the sequence of grid rows in P4021

Question 7. How internally does it differentiate Supply data from Demand Data?

Answer 7. Internally it is through mnBubbleSequence as below,

1. 0 or 10 (F41021 and F4108)
. jdLotEffectiveDate >= jdTodayDate: 0
. jdLotEffectiveDate < jdTodayDate: 0
. jdExpiryDate is not NULL AND mnOutstandingQuantity !=0: 10
2. 0 (F410211)
3. 70 (F3215)
4. 20 (F4311)
5. 30 or 85 (4801/F4801T)
. F4801T.wamwdh is equal to Blank or NULL: 30
. cProjectDemand is 1(Supply on Project End Item is equal to Project Demand): 85
6. 40 (F3111)
7. 100 (F3111)
8. 110 (F3411)
9. 60 (F3412/F3002)
10. 60 (F3412)
11. 50 (F3411)
12. 80 (F3460)
13. 70 (F4211)
14. 31 (F4801)

NOTE:

If Bubble Sequence is less than 68 then it is Supply Line (else, Demand lines)
Added through Application P4021 in button clicked "AddForecastConnsumptionBuckets"

Read F3405 based on jdForeCastEndDateSave
If there is data meets this condition then add cache with mnBubbleSequence "81"
Then F3462 (where jdForecastBeginDateSave <= Date - Scheduled Pick)
If mnShippedQtyF3462 is greater than zero Add Cache with mnBubbleSequence "69"

And additional bubble sequence is defined as below (during runtime not through source of data)

68 (Obsolete) for Forecast Consumption Period) for 'FC' Type
81 (for Forecast Consumption Period) for 'FC' Type
69 (-SHIP for Forecast Cons period) for 'SH'Type

Question 8. How does the system compute the GC Promise Date?

Answer 8. By reading the Supply and Demand transactional files, it gets information as below:

1. I3400310_GetAndSelectLotQuantity

jdLotEffectiveDate >= jdTodayDate: NULLDate

jdLotEffectiveDate < jdTodayDate: F4108.EFTJ

jdExpiryDate is not NULL AND mnOutstandingQuantity !=0: F4108.EXDJ

2. I3400310_GetandSelectProjectInventory: No Implementation
3. I3400310_GetAndSelectConfiguredComponentQuantity: jdPromisedDate from B3401730 - MRPProcessConfiguredComponent (MRP, Process Configured Component)
4. I3400310_GetAndSelectPurchaseOrders: F4311.PDDJ
5. I3400310_GetAndSelectWorkOrders: F4801.DPL
6. I3400310_GetAndSelectCoByOrders: F3111.DLEJ
7. I3400310_GetAndSelectPartsList: V3111JC.DRQJ
8. I3400310_GetAndSelectPeggingOrders: F3412.DRQJ
9. I3400310_GetAndSelectCoByPegging: F3412.DRQJ
10. I3400310_GetAndSelectCoByPegging: F3412.DRQJ
11. I3400310_GetAndSelectPlannedOrders: F3411.OEDJ
12. I3400310_GetAndSelectForecast: F3460.DRQJ
13. I3400310_GetAndSelectSalesOrders: F4211.PDDJ
14. I3400310_GetAndSelectRates: F4801.DRQJ
: Promise Date will be computed based on system date if mnBubbleSequence is 0

Question 9. What are columns, index for query?

Answer 9.

Internal function	Table	Index	Columns	Note
I3400310_VerifySupplyDemandRuleExists()	F34004	Key1 (ID_F34004_VERSION_ORDERTYPE__A)	MVER,DCTO,LNTY,TRTY
I3400310_GetAndSelectPOSequentially()	F4311	Key4 (ID_F4311_ITEM_NUMBER__BRANCH____A)	KCOO, DOCO, DCTO, SFXO,LNID, MCU, RORN, AN8,PDDJ, ITM, LOCN, LOTN,LNTY, NXTR, LTTR,UOM,UOPN, RTGC, PRJM, DLEJ
I3400310_GetSelectWOSequentially()	F4801	Key4 (ID_F4801_ITEM_BRANCH)	DCTO, DOCO, CO, SFXO, PARS, DL01, MMCU, LOCN, SRST, AN8, DRQJ, ITM, UORG, SOCN, SOQS, UOM, RTG, LOTN, DPL, RORN
I3400310_GetSelectPlannedOrdersSeq	F3411	Key5 (ID_F3411_SHORT_ITEM__BRANCH___B)	ITM, MCU, MMCU, MSGT, MSGA, HCLD, DOCO, DCTO, TRQT, VEND, DRQJ, OEDJ, PRJM
I3400310_GetAndSelectCoByOrdersSeq	F3111	Key2 (ID_F3111_COMPITEMNO_COMPBRANCH__A)	DOCO, DCTO, SFXO, COBY, BSEQ, CPIT, CMCU, LOCN, LOTN, AN8, DRQJ, UORG, TRQT, SOCN, UM, RTG, PARS, DLEJ
I3400310_GetAndSelectCoByPeggingSeq	F3412	Key3 (ID_F3412_KIT_ITEM___KIT_BRANCH)	KIT, MMCU, ITM, MCU, DRQJ, DOCO, DCTO, UORG
I3400310_GetOnHandQuantityInSequence	F41021	Key1 (ID_F41021_ITEM__BRANCH___A)	ITM, MCU, LOCN, LOTN, PBIN, LOTS, PQOH, QTTR, QTIN, QTO1, QTO2, SQOH
I3400310_GetSelectSalesOrdersSequentially	F4211	Key6 (ID_F4211_SHORT_ITEM__BRANCH)	KCOO, DOCO, DCTO, LNID, MCU, AN8, SHAN, PDDJ, ITM, LOCN, LOTN, LNTY, NXTR, UOM, UORG, SOCN, QTYT, XDCK, XPTY, PRJM, XKCO, XORN, XCTO, XLLN, XSFX,LTTR
I3400310_GetSelectTransferOrdersSeq	F3411	Key7 (ID_F3411_SUPP_BRANCH__SHORT_ITEM___A)	ITM, MCU, MMCU, MSGT, MSGA, HCLD, DOCO, DCTO, TRQT, VEND, DRQJ, STRT
I3400310_GetSelectPartsListSequentially	F3111, F4801, F4801T		V3111JC - JointF3111_F4801_F4801T F3111.DOCO, F3111.DCTO, F3111.COBY, F3111.CPIT, F3111.CMCU, F3111.DSC1, F3111.LOCN, F3111.LOTN, F3111.AN8, F3111.DRQJ, F3111.UORG, F3111.UM, F3111.TRQT, F3111.QNTA, F3111.COMM, F4801.SRST, F4801.ITM, F4801.MMCU, F4801T.PRJM	Business View
I3400310_GetAndSelectPeggingSequentially	F3412	Key1 (ID_F3412_SHORT_ITEM__BRANCH___A)	ITM, MCU, DRQJ, DOCO, DCTO, UORG, KIT, MMCU, PRJM, SRDM
I3400310_GetAndSelectForecastSequentially	F3460	Key 2 (ID_F3460_ITEM_NUMBER__BRANCH___A)	ITM, MCU, DRQJ, AN8, FQT, TYPF, DCTO
GetReceiptRoutingData (B3400300)	F43092	Key 3 (ID_F43092_ACTIVE_OP__ORDERNUMBER__B)	KCOO, DOCO, DCTO, SFXO, LNID, MCU, OPRC, UPIB, ACTO, UOM, QTYO	Not internal function
I3400310_GetAndSelectRatesSequentially	F4801	Key4 (ID_F4801_ITEM_BRANCH)	DCTO, DOCO, SFXO, DL01, MMCU, SRST, STRT, DRQJ, ITM, UORG, SOCN, SOQS, UOM
I3400310_GetConfCompQuantityInSequence	F3215, F4211	Key1 (ID_F3215_CONFIGID__COMPONENTID___A), Key1 (ID_F4211_ORDER_NUMBER__ORDER_TYPE___A)	F3215 (CFGID, CFGCID, ITM, MCU, QNTY, UOM, KIT, CFGSID, SY, WADL, PDDJ), F4211 (KCOO, DOCO, DCTO, LNID, LNTY,NXTR)
I3400310_GetandSelectProjectInventory	F410211	Key2 (ID_F410211_MCU_ITM_LOCN_LOTN)	MCU, PRJM, DOCO, ITM, LOCN, LOTN, PJCM, PJDM

Note:

Refer question 6 to get WHERE clause
Example of query in the log: SQL> SELECT DCTO, DOCO, CO, SFXO, PARS, DL01, MMCU, LOCN, SRST, AN8, DRQJ, ITM, UORG, SOCN, SOQS, UOM, RTG, LOTN, DPL, RORN FROM PRODDTA.F4801 WHERE ( WAITM = 1234 AND WAMMCU = ' M30' ) AND ( WASRST NOT IN ( '95','91','92','96','97','98','99' ) ) ORDER BY WAITM ASC,WAMMCU ASC,WADRQJ ASC
So based on query written in log, you can determine which routine it took
Columns in view V3111JC - JointF3111_F4801_F4801T can be represented as below,
- SQL> T0.DOCO, T0.DCTO, T0.COBY, T0.CPIT, T0.CMCU, T0.DSC1, T0.LOCN, T0.LOTN, T0.AN8, T0.DRQJ, T0.UORG, T0.UM, T0.TRQT, T0.QNTA, T0.COMM, T1.SRST, T1.ITM, F4801.T2, F4801T.PRJM FROM PRODDTA.F3111 T0, PRODDTA.F4801 T1, PRODTA.F4801T T2 WHERE ( T0.WMCPIT = 1234 AND T0.WMCMCU = ' M30' AND T1.WASRST NOT IN ( '95','91','92','96','97','98','99' ) AND T0.WMCOBY = ' ' ) AND ( T0.WMDOCO=T1.WADOCO AND T0.WMDCTO=T1.WADCTO AND T0.WMDOCO=T2.WADOCO ) ORDER BY T0.WMUKID ASC

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the E1 Manufacturing Community.

To look at upcoming or archived Advisor Webcasts please see Advisor Webcast Details Document 1450327.1. If your topic is not currently scheduled please suggest it.