Advance Supply Chaining Scripts - ASCP Full Pegging Concept Script

-
ASCP Pegging Script

SELECT DISTINCT msc.organization_code m_organization_code,
                      msc.category_name m_category_name,
                      item_segments m_item_segments,
                      msc.description m_description,
                      order_type_text m_order_type_text,
                      TO_CHAR (new_due_date) m_new_due_date,
                      TO_CHAR (quantity_rate) m_quantity_rate,
                     
                      --                TO_CHAR (msc.disposition_id),
--                TO_CHAR (msc.transaction_id) m_transaction_id,
                      b.item_name s1_item, b.description s1_desc,
                      d.meaning s1_order_type,
                      c.order_number s1_order_number,
                      TO_CHAR (a.demand_quantity) s1_demand_quantity,
                      (a.allocated_quantity) s1_allocated_quantity,
                      TO_CHAR (a.end_item_usage) s1_end_item_usage,
                      TO_CHAR (a1.end_item_usage) s2_end_item_usage,
                      a.demand_date s1_supply_date,
              --                TO_CHAR (a.transaction_id) s1_transaction_id,
--                TO_CHAR (a.demand_id),
                                                   msc1.item_name s2_item,
                      msc1.description s2_desc,
                      c1.order_number s2_order_number,
                      TO_CHAR (a1.demand_quantity) s2_demand_quantity,
                      TO_CHAR (a1.allocated_quantity) s2_allocated_quantity,
                      TO_CHAR (a1.demand_date) s2_supply_date,
                      TO_CHAR (c.new_order_quantity) c_qty,
                      TO_CHAR (c1.new_order_quantity) c1_qty,
                      d1.meaning s2_order_type
                 FROM msc_full_pegging a,
                      msc_system_items b,
                      msc_supplies c,
                      mfg_lookups d,
                      msc_orders_v msc,
                      msc_full_pegging a1,
                      msc_system_items msc1,
                      msc_supplies c1,
                      mfg_lookups d1
                WHERE 1 = 1
                  AND a.inventory_item_id = b.inventory_item_id
                  AND a.organization_id = b.organization_id
                  AND a.plan_id = b.plan_id
                  AND a.plan_id = c.plan_id
                  AND a.transaction_id(+) = c.transaction_id
                  AND d.lookup_type = 'MRP_ORDER_TYPE'
                  AND d.lookup_code = c.order_type
                  AND msc.transaction_id = a.transaction_id
--                  AND item_segments IN ('SK4-D16X2000')
                  AND msc.category_set_id = 23
                  AND msc.order_type_text NOT IN
                         ('Work order demand', 'Sales Orders',
                          'Planned order demand', 'Non-standard job demand')
                  AND msc.category_id = p_category
--:p_category                           --4220
                  AND msc.plan_id = p_plan
--:p_plan                                  -- 2021
                  AND msc.organization_id = p_org                     --:p_org
                  AND a.prev_pegging_id = a1.pegging_id(+)
                  AND a1.inventory_item_id = msc1.inventory_item_id(+)
                  AND a1.organization_id = msc1.organization_id(+)
                  AND a1.plan_id = msc1.plan_id(+)
                  AND a1.transaction_id = c1.transaction_id(+)
                  AND c1.order_type = d1.lookup_code
                  AND d1.lookup_type = 'MRP_ORDER_TYPE'
                  AND msc.plan_id = a.plan_id
                  AND msc1.plan_id = a1.plan_id

Comments

  1. UnknownJanuary 27, 2015 at 4:50 AM

    Its impressive to know something about your note on Oracle apps Course. Please do share your articles like this your articles for our awareness. Mostly we do also provide Online Training on Cub training oracle apps course.

    ReplyDelete
  2. dhoomJanuary 30, 2016 at 8:26 AM

    Can we have sql/plsql script to get the supply&demands horizontal plan as we see on the screen?

    Thanks in advence.

    ReplyDelete
  3. Click4learningJuly 19, 2021 at 4:00 AM

    Nice information. Thanks for sharing such an amazing article. For online training and Self Paced courses visit our site click4learning.com/product/oracle-ascp/

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Shipping Network SQL Query

-
Useful Script for Oracle Shipping Network , I wrote custom function for GL Code , you may find below SELECT a.from_organization_code, a.from_organization_name,        a.to_organization_code, a.to_organization_name,  DECODE (a.intransit_type,                  2, 'Intransit',                  1, 'Direct'                 ) "Transfer Type",        (SELECT meaning           FROM apps.mfg_lookups          WHERE lookup_type = 'MTL_FOB_POINT'            AND lookup_code = a.fob_point) fob_point,        a.elemental_visibility_enabled, DECODE (a.routing_header_id,                  1, 'Standard',                  2, 'Insp...
Click for more

Oracle Ar Invoice Numbering Sequence alter to avoid missing in between by cache

-
1) Query up your invoice source , which user have reported Jump in number. Navigate>Setup>Invoice>Source 2) Execute the following script using your source as in step 1.   SELECT batch_source_id, org_id, name FROM ra_batch_sources_all WHERE name LIKE '<source>' ;   or use this query if you have access from database.   SELECT 'alter sequence AR.' ||seq.sequence_name|| ' nocache;' FROM ( SELECT name bsname, org_id org, 'RA_TRX_NUMBER_' ||BATCH_SOURCE_ID|| '_' ||org_id|| '_S' seqname FROM RA_batch_sources_all ) src, dba_sequences seq WHERE src.seqname=seq.sequence_name AND seq.cache_size; ORDER BY org /     3) Using the batch_source_id from the above query , execute the followingscript:   SELECT sequence_owner, sequence_name, cache_size FROM all_sequences WHERE sequence_name LIKE 'RA_TRX_NUMBER%1146%' ;     4) Execute the following:   ALTER sequence SEQ...
Click for more