Translate

Monday, July 11, 2016

Pegging Details in ASCP


SELECT mso.sales_order_number, md1.*,
MSI1.ITEM_NAME||' ('||MSI1.INVENTORY_ITEM_ID||')' "COMPONENT (ID)"
, substr( md1.order_number,1,22) order_num
, MS1.NEW_ORDER_QUANTITY COMP_QTY -- this is the pegged or previously pegged column
, decode (ms1.order_type,'1','Purchase order'
,'11','Intransit shipment'
,'12','Intransit receipt'
,'13','Suggested repetitive schedule'
,'14','Discrete job co-product/by-product'
,'15','Nonstandard job by-product'
,'16','Repetitive schedule by-product'
,'17','Planned order co-product/by-product'
,'18','On Hand'
,'2','Purchase requisition'
,'27','Flow schedule'
,'28','Flow schedule by-product'
,'29','Payback Supply'
,'3','Discrete job'
,'30','Current repetitive schedule'
,'32','Returns'
,'4','Suggested aggregate repetitive schedule'
,'41','User Supply'
,'45','Demand Class Consumption'
,'46','Supply Due To Stealing'
,'47','Demand Due To Stealing'
,'48','Supply Adjustment'
,'49','PO Acknowledgment'
,'5','Planned order'
,'50','ATP Aggregate Supply'
,'51','Planned inbound shipment'
,'52','Requested inbound shipment'
,'53','Internal requisition'
,'60','Order Rescheduling Adjustment'
,'7','Non-standard job'
,'70','Maintenance Work Order'
,'8','PO in receiving') Order_type
-- , decode (MFP1.supply_type, 1, 'Purchase Order', 2, 'Purchase Requisition', 3, 'Work Order', 11, 'Intransit Shipment', 12, 'Intransit receipt') Supply_type
-- ,decode (MFP2.supply_type, 1, 'Purchase Order', 2, 'Purchase Requisition', 3, 'Work Order', 11, 'Intransit Shipment', 12, 'Intransit receipt') Supply_type2
, MFP1.demand_quantity
--, MFP1.supply_quantity
, MFP1.demand_date
, MFP1.supply_date
, ROUND(MFP1.ALLOCATED_QUANTITY,1) COMP_ALLOC
, decode(MD1.origination_type, '29','Peg_Fcast','30','Peg_Sales') Pegged_To
, decode (MFP1.DEMAND_ID, -1,'peg_Excess',-2,'Peg_SS') Pegged_To_1
, MFP2.ORGANIZATION_ID
,MFP1.ORGANIZATION_ID
, decode(MD2.origination_type, '29','Peg_Fcast','30','Peg_Sales') Pegged_To_2
, TRUNC(MS1.NEW_SCHEDULE_DATE) COMP_SUP_DUE
, 'X'
, DECODE(MD2.ORDER_NUMBER,NULL
,TO_CHAR(MD2.DEMAND_ID)
,(SUBSTR(MD2.ORDER_NUMBER,1,14)||SUBSTR(MD2.ORDER_NUMBER,42,5))) SO_NUM
, MSI2.ITEM_NAME||' ('||MSI2.INVENTORY_ITEM_ID||')' "ASSEMBLY (ID)"
-- , MSI2.DESCRIPTION
, TO_CHAR(MD2.REQUEST_SHIP_DATE,'DD/MM/YYYY') RDATE
, TRUNC(MD2.USING_ASSEMBLY_DEMAND_DATE) SSDATE
, MD1.Creation_date
, ROUND((MD2.DMD_SATISFIED_DATE-MD2.USING_ASSEMBLY_DEMAND_DATE),1) LATE
, MD2.DEMAND_PRIORITY PRIORITY
, ROUND(MFP2.DEMAND_QUANTITY,1) so_QTY
, ROUND(MFP2.ALLOCATED_QUANTITY,1) WIP_QTY
, TRUNC(MD1.OLD_DEMAND_DATE) ULSD
, TRUNC(MD1.USING_ASSEMBLY_DEMAND_DATE) COMP_DEM_DUE
FROM APPS.MSC_SUPPLIES MS1 -- COMP
, APPS.MSC_SYSTEM_ITEMS MSI1 -- COMP
, APPS.MSC_FULL_PEGGING MFP1 -- COMP
, apps.msc_sales_orders mso
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD1 -- COMP
, (SELECT *
FROM APPS.MSC_FULL_PEGGING
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MFP2 -- FG
, (SELECT *
FROM APPS.MSC_DEMANDS
WHERE SR_INSTANCE_ID =&1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MD2 -- FG
, (SELECT *
FROM APPS.MSC_SYSTEM_ITEMS
WHERE SR_INSTANCE_ID = &1
AND ORGANIZATION_ID =&2
AND PLAN_ID =&3) MSI2 -- FG LEVEL
WHERE 1=1
AND MS1.SR_INSTANCE_ID =&1
AND MS1.ORGANIZATION_ID =&2
AND MS1.PLAN_ID =&3
and mso.demand_id=md1.demand_id
AND MSI1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MSI1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MSI1.PLAN_ID = MS1.PLAN_ID
AND MSI1.INVENTORY_ITEM_ID = MS1.INVENTORY_ITEM_ID
AND MFP1.SR_INSTANCE_ID = MS1.SR_INSTANCE_ID
AND MFP1.ORGANIZATION_ID = MS1.ORGANIZATION_ID
AND MFP1.PLAN_ID = MS1.PLAN_ID
AND MFP1.TRANSACTION_ID = MS1.TRANSACTION_ID
AND MD1.DEMAND_ID (+) = MFP1.DEMAND_ID
AND MFP2.PEGGING_ID (+) = MFP1.PREV_PEGGING_ID
AND MD2.DEMAND_ID (+) = MFP2.DEMAND_ID
AND MSI2.INVENTORY_ITEM_ID (+) = MFP2.INVENTORY_ITEM_ID
AND MSI1.ITEM_NAME = 'item'
and md1.order_number='ord'
ORDER BY MS1.OLD_SCHEDULE_DATE, MD2.REQUEST_SHIP_DATE

Thursday, January 21, 2016

ERR_GET_INSTANCES (ERROR_MSG=No Free partitions available. Contact DBA to create partitions.)



ERR_GET_INSTANCES (ERROR_MSG=No Free partitions available. Contact DBA to create partitions.)

Through System Administrator responsibility > Security > Responsibility Request >Select the group, All MSC Reports
Place cursor in the name column > Click the Green X on the toolbar > In the name  column select, CREATE APS PARTITIONS  (Make sure to select the  correct one)
Save.

Go the Advanced supply chain planner responsibility > other > request > submit a new request > single request
select CREATE APS PARTITIONS
(Make sure to select the correct one NOT Create ATP Partitions)

Use parameters
plan_partition_count = 0
inst_partition_count = 1

This will create the instance partition and allow continued setup.

Concurrent Request/Program tables in Oracle

Tables Used by Concurrent Request Concurrent Program

FND_CONCURRENT_REQUESTS

Contains a complete history of all concurrent requests (both past history and those scheduled to run in the future).

FND_RUN_REQUESTS

Stores information about the reports in a report set that a user submits including the report set’s parameter values.

FND_CONC_REQUEST_ARGUMENTS

Records all arguments passed by Concurrent Managers to concurrent requests as those requests are running.

FND_DUAL

Records when a request does not update any database tables.

FND_CONCURRENT_PROCESSES

Records information about Oracle Applications processes and OS processes.

FND_CONC_STAT_LIST

Collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

Contains Concurrent Program performance statistics generated by the Purge Concurrent Request program or the manager data program. These programs use the data in FND_CONC_STAT_LIST to compute these statistics।